INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

using dynamic SQL and CURSUR effectively

using dynamic SQL and CURSUR effectively

(OP)
Im updating the table GLRS.TBL_GLRS_GT_PRC_FEED with the enriched data based on the OPICS_TYPE value usng two approaches

The other mapping tables are GLRS.TBL_GLRS_OPICS_RULES and GLRS.TBL_GLRS_OPICS_TYPE

CODE


CREATE TABLE GLRS.TBL_GLRS_OPICS_RULES
(
  TBL_GLRS_OPICS_RULES_ID        NUMBER(38,0) not null,
  TBL_GLRS_OPICS_TYPE_ID        NUMBER(38,0) not null,
  TBL_GLRS_OPICS_PROD_ID        NUMBER(38,0) not null,  
  TBL_AGG_PRODUCT_LINE_ID        NUMBER(38,0) not null,
  TBL_GLRS_MAR_CONF_ID            NUMBER(38,0) not null,
  TBL_GLRS_PROD_LINE_TYPE_ID     NUMBER(38,0) not null,
  TBL_GLRS_PROD_LINE_SUB_TYPE_ID NUMBER(38,0) not null,
  CREATED_BY                     VARCHAR2(20) not null,
  CREATED_DATETIME               TIMESTAMP(6) not null,
  UPDATED_BY                     VARCHAR2(20) not null,
  UPDATED_DATETIME               TIMESTAMP(6) not null,
  CONSTRAINT PKY_TBL_GLRS_OPICS_RULES_ID PRIMARY KEY(TBL_GLRS_OPICS_RULES_ID)
);
 
CREATE TABLE GLRS.TBL_GLRS_OPICS_TYPE
(
  TBL_GLRS_OPICS_TYPE_ID        NUMBER(38,0) not null,
  OPICS_TYPE                          VARCHAR2(2) not null,
  TYPE_DESCRIPTION              VARCHAR2(15) not null,
  CREATED_BY                     VARCHAR2(20) not null,
  CREATED_DATETIME               TIMESTAMP(6) not null,
  UPDATED_BY                     VARCHAR2(20) not null,
  UPDATED_DATETIME               TIMESTAMP(6) not null,
  CONSTRAINT PKY_TBL_GLRS_OPICS_TYPE_ID PRIMARY KEY(TBL_GLRS_OPICS_TYPE_ID)
  
);

------------------------------------------------
The two approaches are as below
-- Approach 1

CODE

/*Qry to select MAR based on rules. This will be included as inner select qry in the below base qry.*/
 V_MAR_QRY := ' (SELECT CF.TBL_GLRS_MU_ID FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF WHERE CF.TBL_GLRS_MAR_CONF_ID = PR.TBL_GLRS_MAR_CONF_ID) TBL_GLRS_MU_ID ';
     
          /*Base query for selecting rules from the OPICS rules table.*/
       V_QUERY := ' SELECT TBL_AGG_PRODUCT_LINE_ID,TBL_GLRS_PROD_LINE_TYPE_ID,TBL_GLRS_PROD_LINE_SUB_TYPE_ID,' ||
             V_MAR_QRY ||
             ' FROM GLRS.TBL_GLRS_OPICS_RULES PR WHERE PR.TBL_GLRS_OPICS_TYPE_ID = :1 ';
      
     FOR V_DATA IN (SELECT T.OPICS_TYPE FROM GLRS.TBL_GLRS_GT_OPICS_FEED T)
     LOOP
     BEGIN
     
     /*Retrieve opics_type id */
     
      SELECT C.TBL_GLRS_OPICS_TYPE_ID
        INTO V_OPICS_TYPE_ID
        FROM GLRS.TBL_GLRS_OPICS_TYPE C
       WHERE C.OPICS_TYPE = V_DATA.OPICS_TYPE;
       
        IF (V_OPICS_TYPE_ID IS NOT NULL) THEN
        /*Retreive MAR and Product line id on the basis of Pool Code*/
        EXECUTE IMMEDIATE V_QUERY
          INTO V_PROD_LINE_ID, V_PROD_LINE_TYPE_ID, V_PROD_LINE_SUB_TYPE_ID, V_MU_ID
          USING V_OPICS_TYPE_ID;
      
        /*Update MAR and Product Line ID against each record.*/
        UPDATE GLRS.TBL_GLRS_GT_PRC_FEED F
           SET F.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
               F.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
               F.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
               F.TBL_GLRS_MU_ID                 = V_MU_ID
         WHERE F.OPICS_TYPE = V_DATA.OPICS_TYPE;
       END IF;
      
       EXCEPTION
      WHEN OTHERS THEN
        NULL;  
     
     END ;
     
     END LOOP;
    
    ---------------------
 -- Approach 2   
 

CODE

  
        CURSOR c_get_opics_prodtype
            IS
               SELECT b.opics_type, b.prod FROM GLRS.TBL_GLRS_GT_OPICS_FEED b;
                      
     TYPE c_get_opics_prodtype_type IS TABLE OF c_get_opics_prodtype%rowtype;
     opics_prodtype c_get_opics_prodtype_type;
     
     
     OPEN c_get_opics_prodtype;
   LOOP
           FETCH c_get_opics_prodtype BULK COLLECT INTO opics_prodtype limit 200;
        FOR i in 1..opics_prodtype.count
        
         /*Retreive MAR and Product line id on the basis of Pool Code*/
        
         SELECT PR.TBL_AGG_PRODUCT_LINE_ID,
                 PR.TBL_GLRS_PROD_LINE_TYPE_ID,
                 PR.TBL_GLRS_PROD_LINE_SUB_TYPE_ID,
                 (SELECT CF.TBL_GLRS_MU_ID
                FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF
                   WHERE CF.TBL_GLRS_MAR_CONF_ID = PR.TBL_GLRS_MAR_CONF_ID) TBL_GLRS_MU_ID
                 INTO V_PROD_LINE_ID,
                      V_PROD_LINE_TYPE_ID,
                       V_PROD_LINE_SUB_TYPE_ID,
                       V_MU_ID,
                 FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF , GLRS.TBL_GLRS_OPICS_RULES PR
                 WHERE PR.TBL_GLRS_MAR_CONF_ID = CF.TBL_GLRS_MAR_CONF_ID AND
                 PR.TBL_GLRS_OPICS_TYPE_ID IN (SELECT C.TBL_GLRS_OPICS_TYPE_ID
                                                    FROM GLRS.TBL_GLRS_OPICS_TYPE C
                                                       WHERE C.OPICS_TYPE IN ('BY','RG','FI') ;
        
        LOOP
            IF opics_prodtype(i).OPICS_TYPE = 'BY' THEN
            
               UPDATE GLRS.TBL_GLRS_GT_OPICS_FEED G
                     SET G.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
                         G.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
                         G.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
                         G.TBL_GLRS_MU_ID                 = V_MU_ID  
                         
            
            ELSIF ((opics_prodtype(i).OPICS_TYPE = 'RG'  AND TRIM(opics_prodtype(i).PROD) = "DEPO" )OR (opics_prodtype(i).OPICS_TYPE = 'FI' AND TRIM(opics_prodtype(i).PROD) = "DEPO") AND (M_DATE = Null or M_DATE = V_AS_OF_DATE + 1) )THEN
            
                 UPDATE GLRS.TBL_GLRS_GT_OPICS_FEED G
                     SET G.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
                         G.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
                         G.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
                         G.TBL_GLRS_MU_ID                 = V_MU_ID   
            
            END IF;
            
        END LOOP;
        EXIT WHEN c_get_opics_prodtype%NOTFOUND;
   
   END LOOP;
   CLOSE c_get_opics_prodtype;
  
   
   Please let me know if approach  

RE: using dynamic SQL and CURSUR effectively

It depends on what your question is, which didn't seem to be expressed fully.  But assuming you're talking about performance, my rules are:

a)  if possible, use a block update/insert.   This will nearly always be more efficient than anything based on cursors.

b) if cursors have to be used, use block selects, inserts and updates.   In example b, you go half-way towards this by doing a block select but you don't use the forall functionality to do the update.

c) Avoid repeated cursors like the select to get V_OPICS_TYPE_ID you have in your first example.   This select could easily be incorporated as a join in cursor which gets V_DATA.

PS Why have you get "DEPO" in double quotes rather than single-quotes?   This will mean it's interpreted as a variable name rather than a string.  If it is a variable, you don't need to put quotes around it.

RE: using dynamic SQL and CURSUR effectively

(OP)
Thanks
Please elaborate on points mentioned
b) if cursors have to be used, use block selects, inserts and updates.   In example b, you go half-way towards this by doing a block select but you don't use the forall functionality to do the update.

c) Avoid repeated cursors like the select to get V_OPICS_TYPE_ID you have in your first example.   This select could easily be incorporated as a join in cursor which gets V_DATA.
 

RE: using dynamic SQL and CURSUR effectively

b) Do an internet search for "Oracle forall" and you will see what I'm talking about.   

I'm not sure why you have two different updates in your second example when they appear to be identical.   Also you don't seem to have any where clause for the update.   Since your code is obviously incomplete, it is difficult to suggest exactly how you would use a FORALL.

c) I mean it would be more efficient to incorporate getting the Id into the original cursor rather than having another select for it.  Otherwise, you are opening, fetching and closing a cursor for each iteration of the loop, which is inefficient.

CODE

FOR V_DATA IN (SELECT T.OPICS_TYPE,C.TBL_GLRS_OPICS_TYPE_ID
                 FROM GLRS.TBL_GLRS_GT_OPICS_FEED T,
                      GLRS.TBL_GLRS_OPICS_TYPE C
                WHERE C.OPICS_TYPE = T.OPICS_TYPE) LOOP

RE: using dynamic SQL and CURSUR effectively

(OP)
for opics_prodtype(i).OPICS_TYPE = 'BY' then values populated for V_PROD_LINE_ID,V_PROD_LINE_TYPE_ID,V_PROD_LINE_SUB_TYPE_ID and V_MU_ID
are different than that populated for opics_prodtype(i).OPICS_TYPE = 'RG' thats why two updates
and here is my modified code using FORALL

CODE

 CURSOR c_get_opics_prodtype
            IS
               SELECT b.opics_type, b.prod FROM GLRS.TBL_GLRS_GT_OPICS_FEED b;
                      
     TYPE c_get_opics_prodtype_type IS TABLE OF c_get_opics_prodtype%rowtype;
     opics_prodtype c_get_opics_prodtype_type;
     
      /*Retreive MAR and Product line id on the basis of TYPE*/
        
         SELECT PR.TBL_AGG_PRODUCT_LINE_ID,
                 PR.TBL_GLRS_PROD_LINE_TYPE_ID,
                 PR.TBL_GLRS_PROD_LINE_SUB_TYPE_ID,
                 (SELECT CF.TBL_GLRS_MU_ID
                FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF
                   WHERE CF.TBL_GLRS_MAR_CONF_ID = PR.TBL_GLRS_MAR_CONF_ID) TBL_GLRS_MU_ID
                 INTO V_PROD_LINE_ID,
                      V_PROD_LINE_TYPE_ID,
                       V_PROD_LINE_SUB_TYPE_ID,
                       V_MU_ID,
                 FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF , GLRS.TBL_GLRS_OPICS_RULES PR
                 WHERE PR.TBL_GLRS_MAR_CONF_ID = CF.TBL_GLRS_MAR_CONF_ID  
                          ;
                 
     
     
     OPEN c_get_opics_prodtype;
   LOOP
           FETCH c_get_opics_prodtype BULK COLLECT INTO opics_prodtype limit 200;
        FORALL indx IN opics_prodtype.FIRST..opics_prodtype.LAST
        
        LOOP
            IF opics_prodtype(i).OPICS_TYPE = 'BY' THEN
            
               UPDATE GLRS.TBL_GLRS_GT_OPICS_FEED G
                     SET G.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
                         G.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
                         G.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
                         G.TBL_GLRS_MU_ID                 = V_MU_ID  
                         
            
            ELSIF ((opics_prodtype(i).OPICS_TYPE = 'RG'  AND TRIM(opics_prodtype(i).PROD) = 'DEPO' )OR (opics_prodtype(i).OPICS_TYPE = 'FI' AND TRIM(opics_prodtype(i).PROD) = 'DEPO') AND (M_DATE = Null or M_DATE = V_AS_OF_DATE + 1) )THEN
            
                 UPDATE GLRS.TBL_GLRS_GT_OPICS_FEED G
                     SET G.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
                         G.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
                         G.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
                         G.TBL_GLRS_MU_ID                 = V_MU_ID   
            
            END IF;
            
        END LOOP;
        EXIT WHEN c_get_opics_prodtype%NOTFOUND;
   
   END LOOP;
   CLOSE c_get_opics_prodtype;

RE: using dynamic SQL and CURSUR effectively

You haven't understood FORALL correctly.  It is not a looping mechanism, so you never have the keyword LOOP after it.  It always appears directly before an SQL statement e.g.

FORALL i in myarray.first..myarray.last
  insert into table (col) values (myarray(i));

RE: using dynamic SQL and CURSUR effectively

Quote:


for opics_prodtype(i).OPICS_TYPE = 'BY' then values populated for V_PROD_LINE_ID,V_PROD_LINE_TYPE_ID,V_PROD_LINE_SUB_TYPE_ID and V_MU_ID
are different than that populated for opics_prodtype(i).OPICS_TYPE = 'RG'

Yes, but the actual update statements are identical and also have no where clause.

RE: using dynamic SQL and CURSUR effectively

(OP)
I have decoded to use the approach 1 the code is as below

CODE

V_MAR_QRY := ' (SELECT CF.TBL_GLRS_MU_ID FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF WHERE CF.TBL_GLRS_MAR_CONF_ID = PR.TBL_GLRS_MAR_CONF_ID) TBL_GLRS_MU_ID ';
     
          /*Base query for selecting rules from the OPICS rules table.*/
       V_QUERY := ' SELECT TBL_AGG_PRODUCT_LINE_ID,TBL_GLRS_PROD_LINE_TYPE_ID,TBL_GLRS_PROD_LINE_SUB_TYPE_ID,' ||
             V_MAR_QRY ||
             ' FROM GLRS.TBL_GLRS_OPICS_RULES PR WHERE PR.TBL_GLRS_OPICS_TYPE_ID = :1 ';
      
     FOR V_DATA IN (SELECT T.OPICS_TYPE,C.TBL_GLRS_OPICS_TYPE_ID
                     FROM GLRS.TBL_GLRS_GT_OPICS_FEED T,
                          GLRS.TBL_GLRS_OPICS_TYPE C
                        WHERE C.OPICS_TYPE = T.OPICS_TYPE)
     LOOP
     BEGIN
     
      IF (V_DATA.TBL_GLRS_OPICS_TYPE_ID IS NOT NULL) THEN
        /*Retreive MAR and Product line id on the basis of opics type*/
        EXECUTE IMMEDIATE V_QUERY
          INTO V_PROD_LINE_ID, V_PROD_LINE_TYPE_ID, V_PROD_LINE_SUB_TYPE_ID,V_MU_ID
          USING V_DATA.TBL_GLRS_OPICS_TYPE_ID;
      
        /*Update MAR and Product Line ID against each record.*/
        UPDATE GLRS.TBL_GLRS_GT_PRC_FEED F
           SET F.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
               F.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
               F.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
               F.TBL_GLRS_MU_ID                 = V_MU_ID
         WHERE F.OPICS_TYPE = V_DATA.OPICS_TYPE;
       END IF;
      
       EXCEPTION
      WHEN OTHERS THEN
        NULL;  
     
     END ;
     
     END LOOP;

Now my question is for opics_prodtype(i).OPICS_TYPE = 'BY' then values populated for V_PROD_LINE_ID,V_PROD_LINE_TYPE_ID,V_PROD_LINE_SUB_TYPE_ID and V_MU_ID
are different than that populated for opics_prodtype(i).OPICS_TYPE = 'RG'
in case case I have a condition i.e

CODE

((opics_prodtype(i).OPICS_TYPE = 'RG'  AND TRIM(opics_prodtype(i).PROD) = 'DEPO' )OR (opics_prodtype(i).OPICS_TYPE = 'FI' AND TRIM(opics_prodtype(i).PROD) = 'DEPO') AND (M_DATE = Null or M_DATE = V_AS_OF_DATE + 1) )THEN
When I do an update how to make sure that above condition gets satisfied ?
 

RE: using dynamic SQL and CURSUR effectively

It's a bit difficult to answer when you have variables such as M_DATE and V_AS_OF_DATE which you never declare and are never set anywhere.   Perhaps if you gave us code that actually worked rather than badly written fragments, we might be able to make progress better.

But in general I don't see much problem with it.  In your second example, you are not changing the values that you are updating - as I have pointed out, the (incomplete) update statements are identical.  All you are actually achieving is that you are stopping the update from happening in some situations.   You should just be able to use the equivalent variables that you have in Example 2 to restrict when the update takes place e.g. instead of:

IF opics_prodtype(i).OPICS_TYPE = 'BY' then
  Update ...

you'd have:

if V_DATA.OPICS_TYPE = 'BY' then
  Update ...

As I've said, without seeing proper functioning code (which your examples most certainly aren't), it's difficult to give decent advice here.
 

RE: using dynamic SQL and CURSUR effectively

(OP)
here is my full procedure code

CODE

CREATE OR REPLACE PROCEDURE GLRS.PRC_GLRS_OPICS_ENRICH_DATA(PO_ERROR      OUT VARCHAR2,
                                                     PO_ERROR_DESC OUT NOCOPY VARCHAR2) AS

          
  
             CURSOR get_opics
             IS
            SELECT T.OPICS_TYPE,C.TBL_GLRS_OPICS_TYPE_ID
                   FROM GLRS.TBL_GLRS_GT_OPICS_FEED T,
                        GLRS.TBL_GLRS_OPICS_TYPE C
                    WHERE C.OPICS_TYPE = T.OPICS_TYPE
                    AND T.TBL_AGG_PRODUCT_LINE_ID IS NULL
                    AND T.TBL_GLRS_MU_ID IS NULL ;
                        
          V_PROC_NAME                       VARCHAR2(40);
          V_CURR_AGG_BUS_DT                 DATE;
          V_QUERY                           VARCHAR2(4000);
          V_OPICS_INTERIM_SRC_SYSTEM_ID     GLRS_AGG.tbl_agg_interim_source_system.tbl_agg_interim_source_sys_id%TYPE;
             V_OPICS_PRODUCT_LINE_ID           GLRS_AGG.tbl_agg_product_line.tbl_agg_product_line_id%TYPE;
          V_OPICS_MU_ID                     GLRS.TBL_GLRS_MU.tbl_glrs_mu_id%TYPE;
             V_MAR_OPICS_QRY                   VARCHAR2(4000);
            V_PROD_LINE_ID                    GLRS.TBL_GLRS_DRAMS_RULES.TBL_AGG_PRODUCT_LINE_ID%TYPE;
             V_MU_ID                           GLRS.TBL_GLRS_INTERIM_MAR_CONF.TBL_GLRS_MU_ID%TYPE;
             V_AS_OF_DATE                      GLRS_AGG.TBL_AGG_SYSTEM_PARAM.PARAM_VALUE%TYPE;
             V_CURRENCY_ID                     GLRS.CURRENCY.CURRENCY_ID%TYPE;
            opics_details                        get_opics%ROWTYPE;
                        
                        
             
      
BEGIN

  PO_ERROR     := null;
  V_PROC_NAME := 'prc_glrs_opics_enrich_data';
  PO_ERROR_DESC := '';
  
 
  SELECT to_date(d.param_value, 'DD-Mon-YYYY')
    INTO V_AS_OF_DATE
    FROM glrs_agg.tbl_agg_system_param d
   WHERE d.param_name = 'CURR_UP_BU_DATE';
   
     SELECT TO_DATE(T.PARAM_VALUE, 'mm-dd-yyyy')
    INTO V_SIM_AS_OF_DATE
    FROM GLRS.SYSTEM_PARAM T
   WHERE T.PARAM_NAME = 'BUSINESS_DATE';

   
      
   SELECT S.TBL_AGG_INTERIM_SOURCE_SYS_ID
    INTO V_OPICS_INTERIM_SRC_SYSTEM_ID
    FROM GLRS_AGG.TBL_AGG_INTERIM_SOURCE_SYSTEM S
   WHERE S.SOURCE_CODE = 'OPICS_FEED';
   
    DELETE FROM GLRS.TBL_ODS_OPICS_MASTER M
   WHERE M.AS_OF_DATE = V_SIM_AS_OF_DATE;  
   
    SELECT c.CURRENCY_ID
    INTO v_currency_id
    FROM GLRS.CURRENCY c
   WHERE c.CURRENCY_CODE = 'USD';
   
   

  
        INSERT INTO GLRS.TBL_ODS_OPICS_MASTER(
                           TBL_ODS_OPICS_MASTER_ID         ,
                           OPICS_BR,
                            DEAL_NO,
                            V_DATE,
                          M_DATE,
                          REV_DATE,
                          RATE_REVDATE,
                          CUSTOMER,
                          ENTITY ,
                          COUNTRY_CODE,    
                          ACCT_TYPE,    
                          PORT,    
                          PROD,    
                          OPICS_TYPE,
                          COST_CENTER,    
                          CCY,
                          BASIS,    
                          EXCHANGE_RATE    ,    
                          PRINT_CCY_AMT    ,    
                          INT_RATE,    
                          PRIN_AVE,    
                          OPICS_GL_ACCT,    
                          PRIN_AMT,    
                          PRIN_GL_ACCT,    
                          INT_THIS_MONTH,    
                          OPICS_GL_INT,    
                          IENC_TO_DATE,    
                          BROK,    
                          OPICS_AL,    
                          RATE_CODE,    
                          TRADER,    
                          TENOR,
                          DATE_COUNT,    
                          CUST_TYPE,    
                          GUARANTOR    ,    
                          CUST_NO,    
                          GFCID    ,
                          OPICS_AS_OF_DATE,
                          CREATED_BY,
                          CREATED_DATETIME,
                          UPDATED_BY,
                          UPDATED_DATETIME                   
)
  SELECT             GLRS.SEQ_ODS_OPICS_MASTER.NEXTVAL,     
                    OT.OPICS_BR                           ,
                    OT.DEAL_NO                        ,    
                    to_date(OT.V_DATE,'mm/dd/yyyy'),
                    to_date(OT.M_DATE,'mm/dd/yyyy')                    ,    
                    to_date(OT.REV_DATE,'mm/dd/yyyy'),    
                    to_date(OT.RATE_REVDATE,'mm/dd/yyyy'),    
                    OT.CUSTOMER                   ,    
                    OT.ENTITY        ,
                    OT.COUNTRY_CODE    ,    
                    OT.ACCT_TYPE ,    
                    OT.PORT ,    
                    OT.PROD    ,    
                    OT.OPICS_TYPE ,
                    OT.COST_CENTER,    
                    OT.CCY ,
                    OT.BASIS ,    
                    OT.EXCHANGE_RATE ,    
                    OT.PRINT_CCY_AMT ,    
                    OT.INT_RATE    ,    
                    OT.PRIN_AVE    ,    
                    OT.OPICS_GL_ACCT ,    
                    OT.PRIN_AMT    ,    
                    OT.PRIN_GL_ACCT ,    
                    OT.INT_THIS_MONTH ,    
                    OT.OPICS_GL_INT    ,    
                    OT.IENC_TO_DATE    ,    
                    OT.BROK    ,    
                    OT.OPICS_AL ,    
                    OT.RATE_CODE,    
                    OT.TRADER ,    
                    OT.TENOR ,
                    OT.DATE_COUNT ,    
                    OT.CUST_TYPE ,    
                    OT.GUARANTOR ,    
                    OT.CUST_NO ,    
                    OT.GFCID ,
                    V_SIM_AS_OF_DATE,
                    'SYSTEM',
                     SYSTIMESTAMP,
                    'SYSTEM',
                    SYSTIMESTAMP        
        FROM GLRS.TBL_ODS_TRANS_OPICS OT ;
        
        
        DELETE FROM GLRS.TBL_GLRS_GT_PRC_FEED;
        v_next_bu_date := V_AS_OF_DATE + 1;
        
        
        /*For Enrichment Insert data into TBL_GT_PRC_FEED table where  
   As of date will be simulation current business date.*/

  INSERT INTO GLRS.TBL_GLRS_GT_OPICS_FEED
               (OPICS_BR,
                DEAL_NO,
                V_DATE,
                M_DATE,
                REV_DATE,
                RATE_REVDATE,
                CUSTOMER,
                ENTITY,
                COUNTRY_CODE,
                ACCT_TYPE,
                PORT,
                PROD,
                OPICS_TYPE,
                COST_CENTER,
                CCY,
                BASIS,
                EXCHANGE_RATE,
                PRINT_CCY_AMT,
                INT_RATE,
                PRIN_AVE,
                OPICS_GL_ACCT,
                PRIN_AMT,
                PRIN_GL_ACCT,
                INT_THIS_MONTH,
                OPICS_GL_INT,
                IENC_TO_DATE,
                BROK,
                OPICS_AL,
                RATE_CODE,
                TRADER,
                TENOR,
                DATE_COUNT,
                CUST_TYPE,
                GUARANTOR,
                CUST_NO,
                GFCID,
                CASHFLOW_DATE,
                AS_OF_DATE,
                TBL_AGG_INTERIM_SOURCE_SYS_ID,
                CURRENCY_ID)
    SELECT         OPICS_BR,
                     DEAL_NO,
                  V_DATE,
                M_DATE,
                REV_DATE,
                RATE_REVDATE,
                CUSTOMER,
                ENTITY,
                COUNTRY_CODE,    
                ACCT_TYPE,    
                PORT,    
                PROD,    
                OPICS_TYPE,
                COST_CENTER    ,    
                CCY    ,
                BASIS,    
                EXCHANGE_RATE,    
                PRINT_CCY_AMT,    
                INT_RATE,    
                PRIN_AVE,    
                OPICS_GL_ACCT,    
                PRIN_AMT,    
                PRIN_GL_ACCT,    
                INT_THIS_MONTH,    
                OPICS_GL_INT,    
                IENC_TO_DATE,    
                BROK,    
                OPICS_AL,    
                RATE_CODE,    
                TRADER,    
                TENOR,
                DATE_COUNT,    
                CUST_TYPE,    
                GUARANTOR,    
                CUST_NO    ,    
                GFCID,
                DECODE(M_DATE,
                  NULL,
                  v_next_bu_date,
                  M_DATE),
                  V_OPICS_INTERIM_SRC_SYSTEM_ID,
                   (SELECT C.CURRENCY_ID
              FROM GLRS.CURRENCY C
             WHERE C.CURRENCY_CODE = M.CURRLOANVALUE)
      FROM GLRS.TBL_ODS_OPICS_MASTER M
     WHERE M.AS_OF_DATE = V_SIM_AS_OF_DATE;
  
    V_MAR_QRY := ' (SELECT CF.TBL_GLRS_MU_ID FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF WHERE CF.TBL_GLRS_MAR_CONF_ID = PR.TBL_GLRS_MAR_CONF_ID) TBL_GLRS_MU_ID ';
  
      /*Base query for selecting rules from the OPICS rules table.*/
       V_QUERY := ' SELECT TBL_AGG_PRODUCT_LINE_ID,TBL_GLRS_PROD_LINE_TYPE_ID,TBL_GLRS_PROD_LINE_SUB_TYPE_ID,' ||
             V_MAR_QRY ||
             ' FROM GLRS.TBL_GLRS_OPICS_RULES PR WHERE PR.TBL_GLRS_OPICS_TYPE_ID = :1 ';
      
  FOR opics_details IN  get_opics  LOOP
  
   BEGIN
     
      IF (opics_details.TBL_GLRS_OPICS_TYPE_ID IS NOT NULL) THEN
       
        EXECUTE IMMEDIATE V_QUERY
          INTO V_PROD_LINE_ID, V_PROD_LINE_TYPE_ID, V_PROD_LINE_SUB_TYPE_ID,V_MU_ID
          USING V_DATA.TBL_GLRS_OPICS_TYPE_ID;
      
        /*Update MAR and Product Line ID against each record.*/
        UPDATE GLRS.TBL_GLRS_GT_OPICS_FEED F
           SET F.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
               F.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
               F.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
               F.TBL_GLRS_MU_ID                 = V_MU_ID
         WHERE F.OPICS_TYPE = opics_details.OPICS_TYPE;
           AND F.TBL_AGG_PRODUCT_LINE_ID IS NULL
           AND F.TBL_GLRS_MU_ID IS NULL
           AND F.AS_OF_DATE = V_SIM_AS_OF_DATE;
       END IF;
      
       EXCEPTION
      WHEN OTHERS THEN
        NULL;  
     
     END ;
     
     END LOOP;
   
   
   /*Check if data is present after filtering the records.*/
  SELECT COUNT(*)
    INTO V_REC_COUNT
    FROM GLRS.TBL_GLRS_GT_OPICS_FEED PC
   WHERE PC.AS_OF_DATE = V_SIM_AS_OF_DATE;

  IF (V_REC_COUNT = 0) THEN
    PO_ERROR      := 0;
    PO_ERROR_DESC := 'No data found for opics processing ';
    DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
    RETURN;
  END IF;
    
    /*Delete data from TBL_ODS_OPICS_FEED for Simulation As of date.*/
  DELETE FROM GLRS.TBL_ODS_OPICS_FEED FE
   WHERE FE.AS_OF_DATE = V_SIM_AS_OF_DATE;  
    
     /*Insert enriched data into TBL_ODS_OP_FEED from GTT table with as
  of date equal to Simulation As of date.*/
  INSERT INTO GLRS.TBL_ODS_OPICS_ENRICHED
    (tbl_ods_opics_enriched_id
                    opics_br,
                    deal_no,
                    v_date,
                    m_date,
                    rev_date,
                    rate_revdate,
                    customer,
                    entity,
                    country_code,
                    acct_type,
                    port,
                    prod,
                    opics_type,
                    cost_center,
                    ccy,
                    basis,
                    exchange_rate,
                    print_ccy_amt,
                    int_rate,
                    prin_ave,
                    opics_gl_acct,
                    prin_amt,
                    prin_gl_acct,
                    int_this_month,
                    opics_gl_int,
                    ienc_to_date,
                    brok,
                    opics_al,
                    rate_code,
                    trader,
                    tenor,
                    date_count,
                    cust_type,
                    guarantor,
                    cust_no,
                    gfcid,
                    cashflow_date,
                    tbl_agg_product_line_id,
                    tbl_glrs_mu_id,
                    as_of_date,
                    tbl_agg_interim_source_sys_id,
                    currency_id,
                    tbl_glrs_prod_line_type_id,
                    tbl_glrs_prod_line_sub_type_id,
                    created_by,
                    created_datetime,
                    updated_by,
                    updated_datetime
                    )
    SELECT             GLRS.SEQ_PRC_FEED.NEXTVAL,
                        OPICS_BR,                  
                      DEAL_NO,
                      V_DATE,
                      M_DATE,
                      REV_DATE,
                      RATE_REVDATE,
                      CUSTOMER,
                      ENTITY,
                      COUNTRY_CODE,
                      ACCT_TYPE,
                      PORT,
                      PROD,
                      OPICS_TYPE,
                      COST_CENTER,
                      CCY,
                      BASIS,
                      EXCHANGE_RATE,
                      PRINT_CCY_AMT,
                      INT_RATE,
                      PRIN_AVE,
                      OPICS_GL_ACCT,
                      PRIN_AMT,
                      PRIN_GL_ACCT,
                      INT_THIS_MONTH,
                      OPICS_GL_INT,
                      IENC_TO_DATE,
                      BROK,
                      OPICS_AL,
                      RATE_CODE,
                      TRADER,
                      TENOR,
                      DATE_COUNT,
                      CUST_TYPE,
                      GUARANTOR,
                      CUST_NO,
                      GFCID,
                      CASHFLOW_DATE,                 
                      TBL_AGG_PRODUCT_LINE_ID,       
                      TBL_GLRS_MU_ID          ,      
                      AS_OF_DATE,                    
                      TBL_AGG_INTERIM_SOURCE_SYS_ID,
                      CURRENCY_ID,                   
                      TBL_GLRS_PROD_LINE_TYPE_ID,     
                      TBL_GLRS_PROD_LINE_SUB_TYPE_ID,

           'SYSTEM',
           SYSTIMESTAMP,
           'SYSTEM',
           SYSTIMESTAMP
      FROM GLRS.TBL_GLRS_GT_OPICS_FEED PC
     WHERE PC.TBL_AGG_PRODUCT_LINE_ID IS NOT NULL
       AND PC.TBL_GLRS_MU_ID IS NOT NULL
       AND PC.AS_OF_DATE = V_SIM_AS_OF_DATE;
EXCEPTION
  WHEN OTHERS THEN
    p_error      := -1;
    p_error_desc := 'proc nam : ' || v_proc_name || ', err_num :' ||
                    SQLCODE || ' | , err_msg :' || SQLERRM;
    ROLLBACK;
                    
    DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
END PRC_GLRS_OPICS_ENRICH_DATA;
/

The required part where modification is required is

CODE

   V_MAR_QRY := ' (SELECT CF.TBL_GLRS_MU_ID FROM GLRS.TBL_GLRS_INTERIM_MAR_CONF CF WHERE CF.TBL_GLRS_MAR_CONF_ID = PR.TBL_GLRS_MAR_CONF_ID) TBL_GLRS_MU_ID ';
  
      /*Base query for selecting rules from the OPICS rules table.*/
       V_QUERY := ' SELECT TBL_AGG_PRODUCT_LINE_ID,TBL_GLRS_PROD_LINE_TYPE_ID,TBL_GLRS_PROD_LINE_SUB_TYPE_ID,' ||
             V_MAR_QRY ||
             ' FROM GLRS.TBL_GLRS_OPICS_RULES PR WHERE PR.TBL_GLRS_OPICS_TYPE_ID = :1 ';
      
  FOR opics_details IN  get_opics  LOOP
  
   BEGIN
     
      IF (opics_details.TBL_GLRS_OPICS_TYPE_ID IS NOT NULL) THEN
       
        EXECUTE IMMEDIATE V_QUERY
          INTO V_PROD_LINE_ID, V_PROD_LINE_TYPE_ID, V_PROD_LINE_SUB_TYPE_ID,V_MU_ID
          USING V_DATA.TBL_GLRS_OPICS_TYPE_ID;
      
        /*Update MAR and Product Line ID against each record.*/
        UPDATE GLRS.TBL_GLRS_GT_OPICS_FEED F
           SET F.TBL_AGG_PRODUCT_LINE_ID        = V_PROD_LINE_ID,
               F.TBL_GLRS_PROD_LINE_TYPE_ID     = V_PROD_LINE_TYPE_ID,
               F.TBL_GLRS_PROD_LINE_SUB_TYPE_ID = V_PROD_LINE_SUB_TYPE_ID,
               F.TBL_GLRS_MU_ID                 = V_MU_ID
         WHERE F.OPICS_TYPE = opics_details.OPICS_TYPE;
           AND F.TBL_AGG_PRODUCT_LINE_ID IS NULL
           AND F.TBL_GLRS_MU_ID IS NULL
           AND F.AS_OF_DATE = V_SIM_AS_OF_DATE;
       END IF;
      
       EXCEPTION
      WHEN OTHERS THEN
        NULL;  
     
     END ;
     
     END LOOP;
   

But while updating UPDATE GLRS.TBL_GLRS_GT_OPICS_FEED
I have two conditions
1.    If Type = "BY"
2.    If ((Type = "RG" and TRIM (Prod) = "DEPO") OR (Type = "FI" and TRIM (Prod) = "DEPO")) AND (M_DATE = Null or M_DATE = As of Date + 1 Business Day)

Does above code work for 2 condition ?

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close