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 COGS_PK,COGS_DATE,BASE_CURRENCY_FK
FROM
OPI_EDW_COGS_FSTG
WHERE
COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND COGS_PK like '%OPM%';
/* Delete the incremental table before inserting new data */
DELETE OPI_EDW_OPMCOGS_INC WHERE SEQ_ID IS NOT NULL;
SELECT count(*) into l_count from OPI_EDW_COGS_FSTG
WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND COGS_PK like '%OPM%';
/* insert into Incremental table all line_id where Currency is missing */
INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
SELECT
SUBSTRB(ORDER_LINE_ID,1,instrB(ORDER_LINE_ID,'-',1,1)-1),
1,
NULL
FROM
OPI_EDW_OPMCOGS_FCV
WHERE
VIEW_ID=1 AND
COGS_PK in ( SELECT COGS_PK
FROM OPI_EDW_COGS_FSTG
WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND COGS_PK like '%OPM%');
INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
SELECT
SUBSTRB(ORDER_LINE_ID,1,instrB(ORDER_LINE_ID,'-',1,1)-1),
2,
NULL
FROM
OPI_EDW_OPMCOGS_FCV
WHERE
VIEW_ID=2 AND
COGS_PK in ( SELECT COGS_PK
FROM OPI_EDW_COGS_FSTG
WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND COGS_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_COGS_FSTG
WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND COGS_PK like '%OPM%';
edw_log.Put_line(to_char(sql%rowcount) ||' missing Currency Rate Conversion rows deleted from Staging table');
edw_log.Put_line('Inserting Rows into Staging Table');
Insert Into OPI_EDW_COGS_FSTG
(COGS_PK
,COGS_DATE
,COGS_DATE_FK
,ORDER_LINE_ID
,SHIP_INV_LOCATOR_FK
,INSTANCE_FK
,TOP_MODEL_ITEM_FK
,ITEM_ORG_FK
,OPERATING_UNIT_FK
,INV_ORG_FK
,CUSTOMER_FK
,SALES_CHANNEL_FK
,PRIM_SALES_REP_FK
,PRIM_SALESRESOURCE_FK
,BILL_TO_LOC_FK
,SHIP_TO_LOC_FK
,PROJECT_FK
,TASK_FK
,ORDER_DATE
,BASE_UOM_FK
,TRX_CURRENCY_FK
,BASE_CURRENCY_FK
,ORDER_CATEGORY_FK
,ORDER_TYPE_FK
,BILL_TO_SITE_FK
,SHIP_TO_SITE_FK
,MONTH_BOOKED_FK
,DATE_BOOKED_FK
,DATE_PROMISED_FK
,DATE_REQUESTED_FK
,DATE_SCHEDULED_FK
,DATE_SHIPPED_FK
,LOCATOR_FK
,ORDER_SOURCE_FK
,SET_OF_BOOKS_FK
,CAMPAIGN_INIT_FK
,CAMPAIGN_ACTL_FK
,CAMPAIGN_STATUS_ACTL_FK
,CAMPAIGN_STATUS_INIT_FK
,MEDCHN_INIT_FK
,MEDCHN_ACTL_FK
,OFFER_HDR_FK
,OFFER_LINE_FK
,MARKET_SEGMENT_FK
,TARGET_SEGMENT_INIT_FK
,TARGET_SEGMENT_ACTL_FK
,PROM_EARLY_COUNT
,PROM_LATE_COUNT
,REQ_EARLY_COUNT
,REQ_LATE_COUNT
,PROM_EARLY_VAL_G
,PROM_LATE_VAL_G
,REQ_EARLY_VAL_G
,REQ_LATE_VAL_G
,REQUEST_LEAD_TIME
,PROMISE_LEAD_TIME
,ORDER_LEAD_TIME
,SHIPPED_QTY_B
,RMA_QTY_B
,ICAP_QTY_B
,COGS_T
,COGS_B
,COGS_G
,RMA_VAL_T
,RMA_VAL_G
,LAST_UPDATE_DATE
,COST_ELEMENT
,ACCOUNT
,ORDER_NUMBER
,WAYBILL_NUMBER
,LOT
,REVISION
,SERIAL_NUMBER
,USER_ATTRIBUTE1
,USER_ATTRIBUTE2
,USER_ATTRIBUTE3
,USER_ATTRIBUTE4
,USER_ATTRIBUTE5
,USER_ATTRIBUTE6
,USER_ATTRIBUTE7
,USER_ATTRIBUTE8
,USER_ATTRIBUTE9
,USER_ATTRIBUTE10
,USER_ATTRIBUTE11
,USER_ATTRIBUTE12
,USER_ATTRIBUTE13
,USER_ATTRIBUTE14
,USER_ATTRIBUTE15
,USER_MEASURE1
,USER_MEASURE2
,USER_MEASURE3
,USER_MEASURE4
,USER_MEASURE5
,USER_FK1
,USER_FK2
,USER_FK3
,USER_FK4
,USER_FK5
,OPERATION_CODE
,COLLECTION_STATUS
,CREATION_DATE)
SELECT /*+ ALL_ROWS */
COGS_PK
,COGS_DATE
,COGS_DATE_FK
,ORDER_LINE_ID
,SHIP_INV_LOCATOR_FK
,INSTANCE_FK
,TOP_MODEL_ITEM_FK
,ITEM_ORG_FK
,OPERATING_UNIT_FK
,INV_ORG_FK
,CUSTOMER_FK
,SALES_CHANNEL_FK
,PRIM_SALES_REP_FK
,PRIM_SALESRESOURCE_FK
,BILL_TO_LOC_FK
,SHIP_TO_LOC_FK
,PROJECT_FK
,TASK_FK
,ORDER_DATE
,BASE_UOM_FK
,TRX_CURRENCY_FK
,BASE_CURRENCY_FK
,ORDER_CATEGORY_FK
,ORDER_TYPE_FK
,BILL_TO_SITE_FK
,SHIP_TO_SITE_FK
,MONTH_BOOKED_FK
,DATE_BOOKED_FK
,DATE_PROMISED_FK
,DATE_REQUESTED_FK
,DATE_SCHEDULED_FK
,DATE_SHIPPED_FK
,LOCATOR_FK
,ORDER_SOURCE_FK
,SET_OF_BOOKS_FK
,CAMPAIGN_INIT_FK
,CAMPAIGN_ACTL_FK
,CAMPAIGN_STATUS_ACTL_FK
,CAMPAIGN_STATUS_INIT_FK
,MEDCHN_INIT_FK
,MEDCHN_ACTL_FK
,OFFER_HDR_FK
,OFFER_LINE_FK
,MARKET_SEGMENT_FK
,TARGET_SEGMENT_INIT_FK
,TARGET_SEGMENT_ACTL_FK
,PROM_EARLY_COUNT
,PROM_LATE_COUNT
,REQ_EARLY_COUNT
,REQ_LATE_COUNT
,PROM_EARLY_VAL_G
,PROM_LATE_VAL_G
,REQ_EARLY_VAL_G
,REQ_LATE_VAL_G
,REQUEST_LEAD_TIME
,PROMISE_LEAD_TIME
,ORDER_LEAD_TIME
,SHIPPED_QTY_B
,RMA_QTY_B
,ICAP_QTY_B
,COGS_T
,COGS_B
,COGS_G
,RMA_VAL_T
,RMA_VAL_G
,LAST_UPDATE_DATE
,COST_ELEMENT
,ACCOUNT
,ORDER_NUMBER
,WAYBILL_NUMBER
,LOT
,REVISION
,SERIAL_NUMBER
,USER_ATTRIBUTE1
,USER_ATTRIBUTE2
,USER_ATTRIBUTE3
,USER_ATTRIBUTE4
,USER_ATTRIBUTE5
,USER_ATTRIBUTE6
,USER_ATTRIBUTE7
,USER_ATTRIBUTE8
,USER_ATTRIBUTE9
,USER_ATTRIBUTE10
,USER_ATTRIBUTE11
,USER_ATTRIBUTE12
,USER_ATTRIBUTE13
,USER_ATTRIBUTE14
,USER_ATTRIBUTE15
,USER_MEASURE1
,USER_MEASURE2
,USER_MEASURE3
,USER_MEASURE4
,USER_MEASURE5
,USER_FK1
,USER_FK2
,USER_FK3
,USER_FK4
,USER_FK5
,NULL -- OPERATION_CODE
,DECODE(COGS_G,-1,'RATE NOT AVAILABLE',-2,'INVALID CURRENCY','LOCAL READY')
,LAST_UPDATE_DATE
from OPI_EDW_OPMCOGS_FCV
WHERE view_id = p_view_id
AND seq_id = p_seq_id;
edw_log.Put_line('Insert completed in Staging tables');
select OPI_EDW_OPMCOGS_INC_S.nextval into l_seq_id from dual;
INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
SELECT
SD.LINE_ID,
1,
l_seq_id
FROM
OP_ORDR_HDR SH,
OP_ORDR_DTL SD,
SY_ORGN_MST OM,
GL_PLCY_MST PM
WHERE SH.order_id = sd.order_id
AND SH.orgn_code = OM.orgn_code
AND OM.co_CODE = PM.co_code
AND SD.LINE_STATUS >= 20
AND GREATEST(SH.LAST_UPDATE_DATE, SD.LAST_UPDATE_DATE,PM.LAST_UPDATE_DATE)
between g_push_from_date and g_push_to_date and
sd.line_id not in
(select LINE_ID from OPI_EDW_OPMCOGS_INC
WHERE VIEW_ID = 1 AND SEQ_ID is NULL);
UPDATE OPI_EDW_OPMCOGS_INC set SEQ_Id=l_SEQ_ID
WHERE VIEW_ID=1 AND SEQ_ID is NULL;
INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
SELECT
IT.LINE_ID,
2,
l_seq_id
FROM
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
IC_TRAN_VW1 IT
WHERE OOH.HEADER_ID = OOL.HEADER_ID AND
OOH.ORG_ID = OOL.ORG_ID AND
OOL.LINE_CATEGORY_CODE = 'ORDER' AND
IT.DOC_ID = OOH.HEADER_ID AND
IT.LINE_ID = OOL.LINE_ID AND
IT.DOC_TYPE = 'OMSO' AND
GREATEST(OOL.LAST_UPDATE_DATE, OOH.LAST_UPDATE_DATE)
between g_push_from_date and g_push_to_date AND
IT.LINE_ID not in
(select LINE_ID from OPI_EDW_OPMCOGS_INC
WHERE VIEW_ID = 2 AND SEQ_ID is NULL);
UPDATE OPI_EDW_OPMCOGS_INC set SEQ_Id=l_SEQ_ID
WHERE VIEW_ID=2 AND SEQ_ID is NULL;
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('Inserting into local staging table ');
edw_log.put_line('Inserted '||nvl(l_row_count2,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 ');
UPDATE OPI_EDW_COGS_FSTG cogs
SET PRIM_SALESRESOURCE_FK =
(select sr.salesrep_id||'-'||sr.org_id||'-'||cogs.instance_fk||'-SALESREP-PERS'
FROM RA_SALESREPS_ALL sr
WHERE sr.SALESREP_NUMBER = substrb(PRIM_SALESRESOURCE_FK,1,instrb(PRIM_SALESRESOURCE_FK,'-',-1,1)-1)
AND sr.org_id = substrb(PRIM_SALESRESOURCE_FK,instrb(PRIM_SALESRESOURCE_FK,'-',-1,1)+1))
WHERE PRIM_SALESRESOURCE_FK <> 'NA_EDW'
AND COLLECTION_STATUS = 'LOCAL READY';
delete OPI_EDW_OPMCOGS_INC;
edw_log.put_line('Inserted '||nvl(g_row_count,0)||
' rows into the staging table');
rollback; -- Rollback insert into local staging
edw_log.put_line('Inserting into local staging have failed');
delete OPI_EDW_OPMCOGS_INC;
delete OPI_EDW_OPMCOGS_INC;
delete OPI_EDW_OPMCOGS_INC;