The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT instance_code INTO l_source
FROM edw_local_instance;
SELECT instance_code INTO l_target
FROM edw_local_instance@edw_apps_to_wh;
SELECT INV_DAILY_STATUS_PK,TRX_DATE_FK,BASE_CURRENCY_FK
FROM
OPI_EDW_INV_DAILY_STAT_FSTG
WHERE
COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND INV_DAILY_STATUS_PK like '%OPM';
/* Delete the incremental table before inserting new data */
DELETE OPI_EDW_OPMINV_DAILY_STAT_INC WHERE SEQ_ID IS NOT NULL;
SELECT count(*) into l_count from opi_edw_inv_daily_stat_fstg where
COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND INV_DAILY_STATUS_PK like '%OPM';
/* insert into Incremental table all line_id where Currency is missing */
INSERT /*+ parallel(OPI_EDW_OPMINV_DAILY_STAT_INC) */
into OPI_EDW_OPMINV_DAILY_STAT_INC
( PRIMARY_KEY,
PRIMARY_KEY1,
PRIMARY_KEY2,
PRIMARY_KEY3,
PRIMARY_KEY4,
PRIMARY_KEY5,
PRIMARY_KEY6,
VIEW_ID,
SEQ_ID)
SELECT
CO_CODE,
ORGN_CODE,
WHSE_CODE,
LOCATION,
ITEM_ID,
LOT_ID,
TRX_DATE,
1,
NULL
FROM
OPI_EDW_OPMINV_DAILY_STAT_FCV
WHERE
INV_DAILY_STATUS_PK in ( SELECT INV_DAILY_STATUS_PK
FROM opi_edw_inv_daily_stat_fstg
WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND INV_DAILY_STATUS_PK like '%OPM');
/*Delete all missing rows from FSTG table if source and target are on same instance*/
IF (LOCAL_SAME_AS_REMOTE) THEN
DELETE opi_edw_inv_daily_stat_fstg
WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND INV_DAILY_STATUS_PK like '%OPM';
edw_log.Put_line(to_char(sql%rowcount) ||' missing Currency Rate Conversion rows deleted from Staging table');
select OPI_EDW_OPMINV_DAILY_INC_S.nextval into l_seq_id from dual;
/* insert into Incremental table all line_id but not part of missing currency convenrsion rows */
INSERT /*+ parallel(OPI_EDW_OPMINV_DAILY_STAT_INC) */
into OPI_EDW_OPMINV_DAILY_STAT_INC
( PRIMARY_KEY,
PRIMARY_KEY1,
PRIMARY_KEY2,
PRIMARY_KEY3,
PRIMARY_KEY4,
PRIMARY_KEY5,
PRIMARY_KEY6,
VIEW_ID,
SEQ_ID)
SELECT
CO_CODE,
ORGN_CODE,
WHSE_CODE,
LOCATION,
ITEM_ID,
LOT_ID,
TRX_DATE,
1,
L_SEQ_ID
FROM
OPI_PMI_INV_DAILY_STAT_SUM OPM
WHERE OPM.LAST_UPDATE_DATE BETWEEN g_push_from_date and g_push_to_date
AND CO_CODE||ORGN_CODE||WHSE_CODE||LOCATION||ITEM_ID||LOT_ID||TRX_DATE
not in
(SELECT PRIMARY_KEY||PRIMARY_KEY1||PRIMARY_KEY2||PRIMARY_KEY3||PRIMARY_KEY4
||PRIMARY_KEY5||PRIMARY_KEY6
from OPI_EDW_OPMINV_DAILY_STAT_INC
WHERE SEQ_ID is NULL);
/* Update the Missing Currency convenrsion rows with new Sequence */
Update OPI_EDW_OPMINV_DAILY_STAT_INC set view_id=1,seq_id=l_seq_id
WHERE seq_id is NULL;
INSERT INTO opi_edw_inv_daily_stat_fstg(AVG_INT_QTY
,AVG_INT_VAL_B
,AVG_INT_VAL_G
,AVG_ONH_QTY
,AVG_ONH_VAL_B
,AVG_ONH_VAL_G
,AVG_WIP_QTY
,AVG_WIP_VAL_B
,AVG_WIP_VAL_G
,BASE_CURRENCY_FK
,BASE_UOM_FK
,BEG_INT_QTY
,BEG_INT_VAL_B
,BEG_INT_VAL_G
,BEG_ONH_QTY
,BEG_ONH_VAL_B
,BEG_ONH_VAL_G
,BEG_WIP_QTY
,BEG_WIP_VAL_B
,BEG_WIP_VAL_G
,COMMODITY_CODE
,COST_GROUP
,CREATION_DATE
,END_INT_QTY
,END_INT_VAL_B
,END_INT_VAL_G
,END_ONH_QTY
,END_ONH_VAL_B
,END_ONH_VAL_G
,END_WIP_QTY
,END_WIP_VAL_B
,END_WIP_VAL_G
,FROM_ORG_QTY
,FROM_ORG_VAL_B
,FROM_ORG_VAL_G
,INSTANCE_FK
,INV_ADJ_QTY
,INV_ADJ_VAL_B
,INV_ADJ_VAL_G
,INV_DAILY_STATUS_PK
,INV_ORG_FK
,ITEM_ORG_FK
,ITEM_STATUS
,ITEM_TYPE
,LAST_UPDATE_DATE
,LOCATOR_FK
,LOT_FK
,NETTABLE_FLAG
,PO_DEL_QTY
,PO_DEL_VAL_B
,PO_DEL_VAL_G
,PRD_DATE_FK
,TOTAL_REC_QTY
,TOTAL_REC_VAL_B
,TOTAL_REC_VAL_G
,TOT_CUST_SHIP_QTY
,TOT_CUST_SHIP_VAL_B
,TOT_CUST_SHIP_VAL_G
,TOT_ISSUES_QTY
,TOT_ISSUES_VAL_B
,TOT_ISSUES_VAL_G
,TO_ORG_QTY
,TO_ORG_VAL_B
,TO_ORG_VAL_G
,TRX_DATE_FK
,USER_ATTRIBUTE1
,USER_ATTRIBUTE10
,USER_ATTRIBUTE11
,USER_ATTRIBUTE12
,USER_ATTRIBUTE13
,USER_ATTRIBUTE14
,USER_ATTRIBUTE15
,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
,WIP_ASSY_QTY
,WIP_ASSY_VAL_B
,WIP_ASSY_VAL_G
,WIP_COMP_QTY
,WIP_COMP_VAL_B
,WIP_COMP_VAL_G
,WIP_ISSUE_QTY
,WIP_ISSUE_VAL_B
,WIP_ISSUE_VAL_G
,TRX_DATE
,PERIOD_FLAG
,OPERATION_CODE
,COLLECTION_STATUS)
SELECT /*+ ALL_ROWS */
AVG_INT_QTY
,AVG_INT_VAL_B
,AVG_INT_VAL_G
,AVG_ONH_QTY
,AVG_ONH_VAL_B
,AVG_ONH_VAL_G
,AVG_WIP_QTY
,AVG_WIP_VAL_B
,AVG_WIP_VAL_G
,BASE_CURRENCY_FK
,BASE_UOM_FK
,BEG_INT_QTY
,BEG_INT_VAL_B
,BEG_INT_VAL_G
,BEG_ONH_QTY
,BEG_ONH_VAL_B
,BEG_ONH_VAL_G
,BEG_WIP_QTY
,BEG_WIP_VAL_B
,BEG_WIP_VAL_G
,COMMODITY_CODE
,COST_GROUP
,CREATION_DATE
,END_INT_QTY
,END_INT_VAL_B
,END_INT_VAL_G
,END_ONH_QTY
,END_ONH_VAL_B
,END_ONH_VAL_G
,END_WIP_QTY
,END_WIP_VAL_B
,END_WIP_VAL_G
,FROM_ORG_QTY
,FROM_ORG_VAL_B
,FROM_ORG_VAL_G
,INSTANCE_FK
,INV_ADJ_QTY
,INV_ADJ_VAL_B
,INV_ADJ_VAL_G
,INV_DAILY_STATUS_PK
,INV_ORG_FK
,ITEM_ORG_FK
,ITEM_STATUS
,ITEM_TYPE
,LAST_UPDATE_DATE
,LOCATOR_FK
,LOT_FK
,NETTABLE_FLAG
,PO_DEL_QTY
,PO_DEL_VAL_B
,PO_DEL_VAL_G
,PRD_DATE_FK
,TOTAL_REC_QTY
,TOTAL_REC_VAL_B
,TOTAL_REC_VAL_G
,TOT_CUST_SHIP_QTY
,TOT_CUST_SHIP_VAL_B
,TOT_CUST_SHIP_VAL_G
,TOT_ISSUES_QTY
,TOT_ISSUES_VAL_B
,TOT_ISSUES_VAL_G
,TO_ORG_QTY
,TO_ORG_VAL_B
,TO_ORG_VAL_G
,TRX_DATE_FK
,USER_ATTRIBUTE1
,USER_ATTRIBUTE10
,USER_ATTRIBUTE11
,USER_ATTRIBUTE12
,USER_ATTRIBUTE13
,USER_ATTRIBUTE14
,USER_ATTRIBUTE15
,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
,WIP_ASSY_QTY
,WIP_ASSY_VAL_B
,WIP_ASSY_VAL_G
,WIP_COMP_QTY
,WIP_COMP_VAL_B
,WIP_COMP_VAL_G
,WIP_ISSUE_QTY
,WIP_ISSUE_VAL_B
,WIP_ISSUE_VAL_G
,TRX_DATE
,PERIOD_FLAG
,NULL
,DECODE(END_ONH_VAL_G,-1,'RATE NOT AVAILABLE',-2,'INVALID CURRENCY','LOCAL READY')
FROM opi_edw_opminv_daily_stat_fcv
WHERE view_id=p_view_id
AND seq_id = p_seq_id;
edw_log.put_line('Inserting into local staging table ');
edw_log.put_line('Inserted '||nvl(l_row_count1,0)||
' rows into the local staging table ');
edw_log.put_line('For all views types, inserted '||nvl(g_row_count,0)||
' rows into local staging table ');
edw_log.put_line('Inserted '||nvl(g_row_count,0)||
' rows into the staging table');
/* Update Data Which has been pushed */
UPDATE OPI_PMI_INV_DAILY_STAT_SUM
SET DATA_PUSHED_IND = 1
WHERE LAST_UPDATE_DATE BETWEEN g_push_from_date AND g_push_to_date AND
CO_CODE||ORGN_CODE||WHSE_CODE||LOCATION||ITEM_ID||LOT_ID||TRX_DATE
not in
(SELECT PRIMARY_KEY||PRIMARY_KEY1||PRIMARY_KEY2||PRIMARY_KEY3||PRIMARY_KEY4
||PRIMARY_KEY5||PRIMARY_KEY6
from OPI_EDW_OPMINV_DAILY_STAT_INC
WHERE SEQ_ID is NULL);
rollback; -- Rollback insert into local staging
edw_log.put_line('Inserting into local staging have failed');