eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FII_PA_COST_F_C SQL Statements

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

Line: 67


 PROCEDURE DELETE_STG
 IS

 BEGIN
   DELETE FII_PA_COST_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: 82


PROCEDURE INSERT_MISSING_RATES_IN_TMP
IS
 rows2                   Number:=0;

Line: 87


   INSERT INTO fii_pa_cost_pk(
               Primary_Key1,
               Primary_Key2  )
   SELECT
              TO_NUMBER(SUBSTR (COST_PK, 1, INSTR(COST_PK, '-' )-1)),
              TO_NUMBER(SUBSTR (COST_PK, INSTR(COST_PK, '-')+1,INSTR(COST_PK,'-',1,2)-(INSTR(COST_PK,'-')+1)))

   FROM  FII_PA_COST_FSTG fcf

   WHERE

               fcf.COLLECTION_STATUS = 'RATE NOT AVAILABLE'
   OR
               fcf.COLLECTION_STATUS = 'INVALID CURRENCY';

Line: 107


      select count(*) into rows2 from FII_PA_COST_FSTG;

Line: 112


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

Line: 133


   SELECT instance_code
   INTO   l_instance1
   FROM   edw_local_instance;

Line: 137


   SELECT instance_code
   INTO   l_instance2
   FROM   edw_local_instance@edw_apps_to_wh;

Line: 164


   UPDATE FII_PA_COST_FSTG
   SET    COLLECTION_STATUS = 'READY'
   WHERE  COLLECTION_STATUS = 'LOCAL READY'
   AND    INSTANCE = (SELECT INSTANCE_CODE
                     FROM   EDW_LOCAL_INSTANCE);

Line: 203


  Insert into FII_PA_COST_FSTG (
     	COST_PK,
 	INSTANCE_FK,
 	SET_OF_BOOKS_FK,
 	PROJECT_FK,
 	PROJECT_ORG_FK,
 	EXPENDITURE_ORG_FK,
 	EXPENDITURE_TYPE_FK,
 	CURRENCY_GL_FK,
 	CURRENCY_TRX_FK,
 	EMPLOYEE_FK,
 	GL_DATE_FK,
 	PA_DATE_FK,
 	TRANSACTION_DATE_FK,
 	UNIT_OF_MEASURE_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_ACCT10_FK,
 	RAW_COST_G,
 	RAW_COST_B,
 	RAW_COST_T,
 	BURDENED_COST_G,
 	BURDENED_COST_B,
 	BURDENED_COST_T,
 	QUANTITY,
 	BILLABLE_FLAG,
 	USER_MEASURE1,
 	USER_MEASURE2,
 	USER_MEASURE3,
 	USER_MEASURE4,
 	USER_MEASURE5,
 	USER_ATTRIBUTE1,
 	USER_ATTRIBUTE2,
 	USER_ATTRIBUTE3,
 	USER_ATTRIBUTE4,
 	USER_ATTRIBUTE5,
 	USER_ATTRIBUTE6,
 	USER_ATTRIBUTE7,
 	USER_ATTRIBUTE8,
 	USER_ATTRIBUTE9,
 	USER_ATTRIBUTE10,
 	USER_FK1,
 	USER_FK2,
 	USER_FK3,
 	USER_FK4,
 	USER_FK5,
 	CREATION_DATE,
 	LAST_UPDATE_DATE,
 	INSTANCE,
 	OPERATION_CODE,
 	COLLECTION_STATUS,
 	EDW_RECORD_TYPE,
        FROM_CURRENCY,
	TRANSACTION_DATE )
  select
	COST_PK,
 	NVL(INSTANCE_FK,'NA_EDW'),
 	NVL(SET_OF_BOOKS_FK,'NA_EDW'),
 	NVL(PROJECT_FK,'NA_EDW'),
 	'NA_EDW'   PROJECT_ORG_FK,
 	NVL(EXPENDITURE_ORG_FK,'NA_EDW'),
 	NVL(EXPENDITURE_TYPE_FK,'NA_EDW'),
 	NVL(CURRENCY_GL_FK,'NA_EDW'),
 	NVL(CURRENCY_TRX_FK,'NA_EDW'),
 	NVL(EMPLOYEE_FK,'NA_EDW'),
 	NVL(GL_DATE_FK,'NA_EDW'),
 	NVL(PA_DATE_FK,'NA_EDW'),
 	NVL(TRANSACTION_DATE_FK,'NA_EDW'),
 	NVL(UNIT_OF_MEASURE_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_ACCT10_FK,'NA_EDW'),
        round( ( RAW_COST_B * GLOBAL_CURRENCY_RATE ) / l_mau ) * l_mau,
 	RAW_COST_B,
 	RAW_COST_T,
        round( ( BURDENED_COST_B * GLOBAL_CURRENCY_RATE ) / l_mau ) * l_mau,
 	BURDENED_COST_B,
 	BURDENED_COST_T,
 	QUANTITY,
 	BILLABLE_FLAG,
 	USER_MEASURE1,
 	USER_MEASURE2,
 	USER_MEASURE3,
 	USER_MEASURE4,
 	USER_MEASURE5,
 	USER_ATTRIBUTE1,
 	USER_ATTRIBUTE2,
 	USER_ATTRIBUTE3,
 	USER_ATTRIBUTE4,
 	USER_ATTRIBUTE5,
 	USER_ATTRIBUTE6,
 	USER_ATTRIBUTE7,
 	USER_ATTRIBUTE8,
 	USER_ATTRIBUTE9,
 	USER_ATTRIBUTE10,
 	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'),
     	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_CURRENCY,
        TRANSACTION_DATE
  from FII_PA_COST_F_FCV;

Line: 359


   Insert Into FII_PA_COST_FSTG@EDW_APPS_TO_WH
   (
	COST_PK,
 	INSTANCE_FK,
 	SET_OF_BOOKS_FK,
 	PROJECT_FK,
 	PROJECT_ORG_FK,
 	EXPENDITURE_ORG_FK,
 	EXPENDITURE_TYPE_FK,
 	CURRENCY_GL_FK,
 	CURRENCY_TRX_FK,
 	EMPLOYEE_FK,
 	GL_DATE_FK,
 	PA_DATE_FK,
 	TRANSACTION_DATE_FK,
 	UNIT_OF_MEASURE_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_ACCT10_FK,
 	RAW_COST_G,
 	RAW_COST_B,
 	RAW_COST_T,
 	BURDENED_COST_G,
 	BURDENED_COST_B,
 	BURDENED_COST_T,
 	QUANTITY,
 	BILLABLE_FLAG,
 	USER_MEASURE1,
 	USER_MEASURE2,
 	USER_MEASURE3,
 	USER_MEASURE4,
 	USER_MEASURE5,
 	USER_ATTRIBUTE1,
 	USER_ATTRIBUTE2,
 	USER_ATTRIBUTE3,
 	USER_ATTRIBUTE4,
 	USER_ATTRIBUTE5,
 	USER_ATTRIBUTE6,
 	USER_ATTRIBUTE7,
 	USER_ATTRIBUTE8,
 	USER_ATTRIBUTE9,
 	USER_ATTRIBUTE10,
 	USER_FK1,
 	USER_FK2,
 	USER_FK3,
 	USER_FK4,
 	USER_FK5,
 	CREATION_DATE,
 	LAST_UPDATE_DATE,
 	INSTANCE,
 	OPERATION_CODE,
 	COLLECTION_STATUS,
        EDW_RECORD_TYPE )
   select
   	COST_PK,
 	INSTANCE_FK,
 	SET_OF_BOOKS_FK,
 	PROJECT_FK,
 	PROJECT_ORG_FK,
 	EXPENDITURE_ORG_FK,
 	EXPENDITURE_TYPE_FK,
 	CURRENCY_GL_FK,
 	CURRENCY_TRX_FK,
 	EMPLOYEE_FK,
 	GL_DATE_FK,
 	PA_DATE_FK,
 	TRANSACTION_DATE_FK,
 	UNIT_OF_MEASURE_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_ACCT10_FK,
 	RAW_COST_G,
 	RAW_COST_B,
 	RAW_COST_T,
 	BURDENED_COST_G,
 	BURDENED_COST_B,
 	BURDENED_COST_T,
 	QUANTITY,
 	BILLABLE_FLAG,
 	USER_MEASURE1,
 	USER_MEASURE2,
 	USER_MEASURE3,
 	USER_MEASURE4,
 	USER_MEASURE5,
 	USER_ATTRIBUTE1,
 	USER_ATTRIBUTE2,
 	USER_ATTRIBUTE3,
 	USER_ATTRIBUTE4,
 	USER_ATTRIBUTE5,
 	USER_ATTRIBUTE6,
 	USER_ATTRIBUTE7,
 	USER_ATTRIBUTE8,
 	USER_ATTRIBUTE9,
 	USER_ATTRIBUTE10,
 	USER_FK1,
 	USER_FK2,
 	USER_FK3,
 	USER_FK4,
 	USER_FK5,
 	SYSDATE,
 	SYSDATE,
 	INSTANCE,
 	null,
	'READY',
 	EDW_RECORD_TYPE
   from FII_PA_COST_FSTG
    WHERE collection_status = 'LOCAL READY';

Line: 505


   INSERT INTO fii_pa_cost_pk
   (
     Primary_Key1,
     Primary_Key2
   )
   SELECT
     expenditure_item_id,
     line_num
   FROM
     pa_cost_distribution_lines_all
   WHERE
         line_type = 'R'
     AND program_update_date BETWEEN g_push_date_range1 and g_push_date_range2;

Line: 521


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

Line: 577


   CURSOR c1 IS SELECT DISTINCT FROM_CURRENCY, substr(TRANSACTION_DATE,1,10 ) inv_dt, COLLECTION_STATUS
   FROM FII_PA_COST_FSTG
   WHERE COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR COLLECTION_STATUS = 'INVALID CURRENCY';

Line: 623


    DELETE_STG;

Line: 625


     select count(*) into rows1 from FII_PA_COST_FSTG;

Line: 647


    select count(*) into rows1 from FII_PA_COST_pk;

Line: 677


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

Line: 710


   INSERT_MISSING_RATES_IN_TMP;

Line: 792


           DELETE_STG;

Line: 827


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

Line: 861


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