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_BUDGET_F_C SQL Statements

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

Line: 65


 PROCEDURE DELETE_STG
 IS

 BEGIN
   DELETE FII_PA_BUDGET_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: 80


PROCEDURE INSERT_MISSING_RATES_IN_TMP (p_conversion_date in date)
IS

 BEGIN
   INSERT INTO FII_PA_BUDGET_PK(
               Primary_Key1,
               PRIMARY_KEY_DATE1,
               conversion_date  )
   SELECT
              TO_NUMBER(SUBSTR (BUDGET_LINE_PK, 1, INSTR(BUDGET_LINE_PK, '-' )-1)),
            TO_DATE((SUBSTR (BUDGET_LINE_PK, INSTR(BUDGET_LINE_PK,
'-')+1,INSTR(BUDGET_LINE_PK,'-',1,2)-(INSTR(BUDGET_LINE_PK,'-')+1))), 'YYYY/MM/DD'),
              p_conversion_date

   FROM  FII_PA_BUDGET_FSTG fsi

   WHERE

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

Line: 110


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

Line: 131


   SELECT instance_code
   INTO   l_instance1
   FROM   edw_local_instance;

Line: 135


   SELECT instance_code
   INTO   l_instance2
   FROM   edw_local_instance@edw_apps_to_wh;

Line: 161


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

Line: 190


Insert Into FII_PA_BUDGET_FSTG
(
 BUDGET_LINE_PK
,INSTANCE_FK
,SET_OF_BOOKS_FK
,PROJECT_FK
,PROJECT_ORG_FK
,BUDGET_FK
,EXPENDITURE_TYPE_FK
,CURRENCY_GL_FK
,GL_PERIOD_FK
,PA_PERIOD_FK
,UNIT_OF_MEASURE_FK
,RANGE_FROM_DATE
,RANGE_TO_DATE
,DESCRIPTION
,RAW_COST_G
,RAW_COST_B
,BURDENED_COST_G
,BURDENED_COST_B
,QUANTITY
,REVENUE_G
,REVENUE_B
,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
,TRANSACTION_DATE)
select
 BUDGET_LINE_PK
,INSTANCE_FK
,SET_OF_BOOKS_FK
,PROJECT_FK
,'NA_EDW'         PROJECT_ORG_FK
,BUDGET_FK
,EXPENDITURE_TYPE_FK
,CURRENCY_GL_FK
,GL_PERIOD_FK
,PA_PERIOD_FK
,UNIT_OF_MEASURE_FK
,RANGE_FROM_DATE
,RANGE_TO_DATE
,DESCRIPTION
,round(( RAW_COST_B * GLOBAL_CURRENCY_RATE)/l_mau) * l_mau
,RAW_COST_B
,round(( BURDENED_COST_B * GLOBAL_CURRENCY_RATE)/l_mau) * l_mau
,BURDENED_COST_B
,QUANTITY
,round(( REVENUE_B * GLOBAL_CURRENCY_RATE)/l_mau) * l_mau
,REVENUE_B
,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			-- CREATION_DATE
,sysdate			-- LAST_UPDATE_DATE
,INSTANCE_FK			-- INSTANCE
,null				-- OPERATION_CODE
,decode(GLOBAL_CURRENCY_RATE,
	NULL, 'RATE NOT AVAILABLE',
	-1, 'RATE NOT AVAILABLE',
	-2, 'RATE NOT AVAILABLE',
	'LOCAL READY'
)			        -- COLLECTION_STATUS
,'ORACLE'                       -- EDW_RECORD_TYPE
,TRANSACTION_DATE
from FII_PA_BUDGET_F_FCV;

Line: 320


Insert Into FII_PA_BUDGET_FSTG@EDW_APPS_TO_WH
(
 BUDGET_LINE_PK
,INSTANCE_FK
,SET_OF_BOOKS_FK
,PROJECT_FK
,PROJECT_ORG_FK
,BUDGET_FK
,EXPENDITURE_TYPE_FK
,CURRENCY_GL_FK
,GL_PERIOD_FK
,PA_PERIOD_FK
,UNIT_OF_MEASURE_FK
,RANGE_FROM_DATE
,RANGE_TO_DATE
,DESCRIPTION
,RAW_COST_G
,RAW_COST_B
,BURDENED_COST_G
,BURDENED_COST_B
,QUANTITY
,REVENUE_G
,REVENUE_B
,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
 BUDGET_LINE_PK
,INSTANCE_FK
,SET_OF_BOOKS_FK
,PROJECT_FK
,PROJECT_ORG_FK
,BUDGET_FK
,EXPENDITURE_TYPE_FK
,CURRENCY_GL_FK
,GL_PERIOD_FK
,PA_PERIOD_FK
,UNIT_OF_MEASURE_FK
,RANGE_FROM_DATE
,RANGE_TO_DATE
,DESCRIPTION
,RAW_COST_G
,RAW_COST_B
,BURDENED_COST_G
,BURDENED_COST_B
,QUANTITY
,REVENUE_G
,REVENUE_B
,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
,'READY'
,EDW_RECORD_TYPE
from FII_PA_BUDGET_FSTG
WHERE collection_status = 'LOCAL READY';

Line: 450


   INSERT INTO fii_pa_budget_pk
   (
     Primary_Key1,
     Primary_Key_Date1,
     conversion_date
   )
   SELECT
     RESOURCE_ASSIGNMENT_ID,
   	 START_DATE,
   	 p_conversion_date
   FROM
     pa_budget_lines
   WHERE
     last_update_date BETWEEN g_push_date_range1 and g_push_date_range2;

Line: 467


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

Line: 527


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

Line: 577


         DELETE_STG;

Line: 625


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

Line: 650


   INSERT_MISSING_RATES_IN_TMP (l_conversion_date);

Line: 729


           DELETE_STG;

Line: 735


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

Line: 793


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