The following lines contain the word 'select', 'insert', 'update' or 'delete':
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);
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');
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');
edw_log.put_line('INSERTING ' || to_char(sql%rowcount) || ' rows from staging table');
SELECT instance_code
INTO l_instance1
FROM edw_local_instance;
SELECT instance_code
INTO l_instance2
FROM edw_local_instance@edw_apps_to_wh;
UPDATE FII_PA_REVENUE_FSTG
SET COLLECTION_STATUS = 'READY'
WHERE COLLECTION_STATUS = 'LOCAL READY'
AND INSTANCE = (SELECT INSTANCE_CODE
FROM EDW_LOCAL_INSTANCE);
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;
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';
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');
edw_log.put_line('Inserted ' || nvl(SQL%ROWCOUNT,0) || ' records into labor primary key table' );
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;
edw_log.put_line('Inserted ' || nvl(SQL%ROWCOUNT,0) || ' records into event primary key table' );
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';
DELETE_STG;
edw_log.put_line('Inserted '||nvl(g_row_count,0)|| ' rows into the local staging table');
INSERT_MISSING_RATES_IN_TMP;
DELETE_STG;
edw_log.put_line('Inserted '||nvl(g_row_count,0)|| ' rows into the staging table');
edw_log.put_line('ERROR: Inserting into local staging have failed');