DBA Data[Home] [Help]

APPS.FII_PA_REVENUE_F_C SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 72

 PROCEDURE DELETE_STG
 IS

 BEGIN
   DELETE FII_PA_REVENUE_FSTG
   WHERE  COLLECTION_STATUS = 'LOCAL READY' OR ( COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR COLLECTION_STATUS = 'INVALID CURRENCY')
   AND    INSTANCE = (SELECT INSTANCE_CODE
                     FROM   EDW_LOCAL_INSTANCE);
Line: 87

PROCEDURE INSERT_MISSING_RATES_IN_TMP
IS

 BEGIN
   INSERT INTO fii_pa_revenue_exp_pk(
               Primary_Key1,
               Primary_Key2  )
   SELECT
              TO_NUMBER(SUBSTR (revenue_PK, 1, INSTR(REVENUE_PK, '-' )-1)),
            TO_NUMBER(SUBSTR (REVENUE_PK, INSTR(REVENUE_PK,'-')+1,INSTR(REVENUE_PK,'-',1,2)-(INSTR(REVENUE_PK,'-')+1)))

   FROM  FII_PA_REVENUE_FSTG FPF

   WHERE
               FPF.REVENUE_PK LIKE '%-EXP-%'
  AND (FPF.COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR FPF.COLLECTION_STATUS = 'INVALID CURRENCY');
Line: 110

   INSERT INTO fii_pa_revenue_evt_pk(
               PRIMARY_KEY1,
               PRIMARY_KEY2,
	       PRIMARY_KEY3,
	       PRIMARY_KEY4)
   SELECT
            TO_NUMBER(SUBSTR (REVENUE_PK, INSTR(REVENUE_PK,'-',1,3)+1,INSTR(REVENUE_PK,'-',1,4)-(INSTR(REVENUE_PK,'-',1,3)+1))),
            TO_NUMBER(SUBSTR (REVENUE_PK, 1, INSTR(REVENUE_PK, '-' )-1)),
            TO_NUMBER(SUBSTR (REVENUE_PK, INSTR(REVENUE_PK,'-')+1,INSTR(REVENUE_PK,'-',1,2)-(INSTR(REVENUE_PK,'-')+1))),
            TO_NUMBER(DECODE((SUBSTR (REVENUE_PK, INSTR(REVENUE_PK,'-',1,2)+1,INSTR(REVENUE_PK,'-',1,3)-(INSTR(REVENUE_PK,'-',1,2)+1)))
, 'NA', null, (SUBSTR (REVENUE_PK, INSTR(REVENUE_PK,'-',1,2)+1,INSTR(REVENUE_PK,'-',1,3)-(INSTR(REVENUE_PK,'-',1,2)+1)))))
   FROM  FII_PA_REVENUE_FSTG FPF

   WHERE
               FPF.REVENUE_PK LIKE '%-EVN-%'
  AND (FPF.COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR FPF.COLLECTION_STATUS = 'INVALID CURRENCY');
Line: 141

        edw_log.put_line('INSERTING ' || to_char(sql%rowcount) || ' rows from staging table');
Line: 161

   SELECT instance_code
   INTO   l_instance1
   FROM   edw_local_instance;
Line: 165

   SELECT instance_code
   INTO   l_instance2
   FROM   edw_local_instance@edw_apps_to_wh;
Line: 192

   UPDATE FII_PA_REVENUE_FSTG
   SET    COLLECTION_STATUS = 'READY'
   WHERE  COLLECTION_STATUS = 'LOCAL READY'
   AND    INSTANCE = (SELECT INSTANCE_CODE
                     FROM   EDW_LOCAL_INSTANCE);
Line: 222

  Insert Into FII_PA_REVENUE_FSTG
  (
     CURRENCY_GL_FK,
     CUSTOMER_FK,
     GL_ACCT10_FK,
     GL_ACCT1_FK,
     GL_ACCT2_FK,
     GL_ACCT3_FK,
     GL_ACCT4_FK,
     GL_ACCT5_FK,
     GL_ACCT6_FK,
     GL_ACCT7_FK,
     GL_ACCT8_FK,
     GL_ACCT9_FK,
     GL_DATE_FK,
     INSTANCE_FK,
     PA_DATE_FK,
     PROJECT_FK,
     PROJECT_ORG_FK,
     REVENUE_B,
     REVENUE_G,
     REVENUE_PK,
     SET_OF_BOOKS_FK,
     TRANSACTION_DATE_FK,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE10,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     USER_ATTRIBUTE6,
     USER_ATTRIBUTE7,
     USER_ATTRIBUTE8,
     USER_ATTRIBUTE9,
     USER_FK1,
     USER_FK2,
     USER_FK3,
     USER_FK4,
     USER_FK5,
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     CREATION_DATE,
     LAST_UPDATE_DATE,
     INSTANCE,
     OPERATION_CODE,
     COLLECTION_STATUS,
     EDW_RECORD_TYPE)
   select
     NVL(CURRENCY_GL_FK,'NA_EDW'),
     NVL(CUSTOMER_FK,'NA_EDW'),
     NVL(GL_ACCT10_FK,'NA_EDW'),
     NVL(GL_ACCT1_FK,'NA_EDW'),
     NVL(GL_ACCT2_FK,'NA_EDW'),
     NVL(GL_ACCT3_FK,'NA_EDW'),
     NVL(GL_ACCT4_FK,'NA_EDW'),
     NVL(GL_ACCT5_FK,'NA_EDW'),
     NVL(GL_ACCT6_FK,'NA_EDW'),
     NVL(GL_ACCT7_FK,'NA_EDW'),
     NVL(GL_ACCT8_FK,'NA_EDW'),
     NVL(GL_ACCT9_FK,'NA_EDW'),
     NVL(GL_DATE_FK,'NA_EDW'),
     NVL(INSTANCE_FK,'NA_EDW'),
     NVL(PA_DATE_FK,'NA_EDW'),
     NVL(PROJECT_FK,'NA_EDW'),
     'NA_EDW'         PROJECT_ORG_FK,
     REVENUE_B,
     round(( REVENUE_B * GLOBAL_CURRENCY_RATE)/l_mau) * l_mau,
     REVENUE_PK,
     NVL(SET_OF_BOOKS_FK,'NA_EDW'),
     NVL(TRANSACTION_DATE_FK,'NA_EDW'),
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE10,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     USER_ATTRIBUTE6,
     USER_ATTRIBUTE7,
     USER_ATTRIBUTE8,
     USER_ATTRIBUTE9,
     NVL(USER_FK1,'NA_EDW'),
     NVL(USER_FK2,'NA_EDW'),
     NVL(USER_FK3,'NA_EDW'),
     NVL(USER_FK4,'NA_EDW'),
     NVL(USER_FK5,'NA_EDW'),
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     SYSDATE,
     SYSDATE,
     INSTANCE_FK,
     NULL,
     decode(GLOBAL_CURRENCY_RATE,
	NULL, 'RATE NOT AVAILABLE',
	-1,   'RATE NOT AVAILABLE',
	-2,   'RATE NOT AVAILABLE',
	'LOCAL READY'
     ),
     'ORACLE'
   from FII_PA_REVENUE_F_FCV;
Line: 352

   Insert Into FII_PA_REVENUE_FSTG@EDW_APPS_TO_WH
   (
     CURRENCY_GL_FK,
     CUSTOMER_FK,
     GL_ACCT10_FK,
     GL_ACCT1_FK,
     GL_ACCT2_FK,
     GL_ACCT3_FK,
     GL_ACCT4_FK,
     GL_ACCT5_FK,
     GL_ACCT6_FK,
     GL_ACCT7_FK,
     GL_ACCT8_FK,
     GL_ACCT9_FK,
     GL_DATE_FK,
     INSTANCE_FK,
     PA_DATE_FK,
     PROJECT_FK,
     PROJECT_ORG_FK,
     REVENUE_B,
     REVENUE_G,
     REVENUE_PK,
     SET_OF_BOOKS_FK,
     TRANSACTION_DATE_FK,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE10,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     USER_ATTRIBUTE6,
     USER_ATTRIBUTE7,
     USER_ATTRIBUTE8,
     USER_ATTRIBUTE9,
     USER_FK1,
     USER_FK2,
     USER_FK3,
     USER_FK4,
     USER_FK5,
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     CREATION_DATE,
     LAST_UPDATE_DATE,
     INSTANCE,
     OPERATION_CODE,
     COLLECTION_STATUS,
     EDW_RECORD_TYPE)
   select
     CURRENCY_GL_FK,
     CUSTOMER_FK,
     GL_ACCT10_FK,
     GL_ACCT1_FK,
     GL_ACCT2_FK,
     GL_ACCT3_FK,
     GL_ACCT4_FK,
     GL_ACCT5_FK,
     GL_ACCT6_FK,
     GL_ACCT7_FK,
     GL_ACCT8_FK,
     GL_ACCT9_FK,
     GL_DATE_FK,
     INSTANCE_FK,
     PA_DATE_FK,
     PROJECT_FK,
     PROJECT_ORG_FK,
     REVENUE_B,
     REVENUE_G,
     REVENUE_PK,
     SET_OF_BOOKS_FK,
     TRANSACTION_DATE_FK,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE10,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     USER_ATTRIBUTE6,
     USER_ATTRIBUTE7,
     USER_ATTRIBUTE8,
     USER_ATTRIBUTE9,
     USER_FK1,
     USER_FK2,
     USER_FK3,
     USER_FK4,
     USER_FK5,
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     SYSDATE,
     SYSDATE,
     INSTANCE_FK,
     NULL,
     'READY',
     EDW_RECORD_TYPE
   from FII_PA_REVENUE_FSTG
WHERE collection_status = 'LOCAL READY';
Line: 479

    INSERT INTO fii_pa_revenue_exp_pk
    (
      Primary_Key1,
      Primary_Key2
    )
   	SELECT
      expenditure_item_id,
      line_num
   	FROM pa_cust_rev_dist_lines_all
   	WHERE program_update_date BETWEEN g_push_date_range1 and g_push_date_range2
   	  and function_code NOT IN ('LRL','LRB','URL','URB');
Line: 493

      edw_log.put_line('Inserted ' || nvl(SQL%ROWCOUNT,0) || ' records into labor primary key table' );
Line: 497

   	INSERT INTO fii_pa_revenue_evt_pk
    (
      Primary_Key1,
      Primary_Key2,
      Primary_Key3,
      Primary_Key4
    )
   	SELECT
   	  project_id,
   	  event_num,
   	  line_num,
   	  task_id
   	FROM pa_cust_event_rdl_all
   	WHERE program_update_date BETWEEN g_push_date_range1 and g_push_date_range2;
Line: 514

      edw_log.put_line('Inserted ' || nvl(SQL%ROWCOUNT,0) || ' records into event primary key table' );
Line: 577

   CURSOR c1 IS SELECT DISTINCT CURRENCY_GL_FK frm_currency,
SUBSTR(PA_DATE_FK,1,(INSTR(PA_DATE_FK, '-',1,3)-1)) inv_dt, COLLECTION_STATUS
   FROM FII_PA_REVENUE_FSTG
   WHERE COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR COLLECTION_STATUS = 'INVALID CURRENCY';
Line: 623

         DELETE_STG;
Line: 666

     edw_log.put_line('Inserted '||nvl(g_row_count,0)|| ' rows into the local staging table');
Line: 695

   INSERT_MISSING_RATES_IN_TMP;
Line: 774

           DELETE_STG;
Line: 784

       edw_log.put_line('Inserted '||nvl(g_row_count,0)|| ' rows into the staging table');
Line: 833

        edw_log.put_line('ERROR: Inserting into local staging have failed');