Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

using dynamic SQL and CURSUR effectively

Status
Not open for further replies.
ronanBas

ronanBas

IS-IT--Management
Nov 30, 2011
15
IN
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
 
Sort by date Sort by votes
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.

 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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;
 
Upvote 0 Downvote
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));

 
Upvote 0 Downvote
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'
Click to expand...

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

 
Upvote 0 Downvote
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 ?
 
Upvote 0 Downvote
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.


 
Upvote 0 Downvote
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 ?

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Get part of a string with SQL 1
Replies
2
Views
381
Andrzejek
Andrzejek
Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
2K
carp
carp
annie1975
  • Locked
  • Question
sql script in an cron job script failing
Replies
3
Views
170
carp
carp
Livia8
  • Locked
  • Question
SQL script to find number of characters allowed in "Description" column of "AP_INVOIC 2
Replies
5
Views
195
Livia8
Livia8
Olutee360
  • Locked
  • Question
Group by using values in a range 1
Replies
4
Views
130
Olutee360
Olutee360

Part and Inventory Search

Sponsor

Back
Top