[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT instance_code
INTO l_instance1
FROM edw_local_instance;
SELECT instance_code
INTO l_instance2
FROM edw_local_instance@edw_apps_to_wh;
SELECT opi_edw_cogs_inc_s.NEXTVAL INTO l_seq_id FROM dual;
INSERT
INTO opi_edw_cogs_inc(primary_key1, seq_id, view_id)
SELECT /*+ parallel(mmt) */
DISTINCT mmt.transaction_id, l_seq_id, 1
FROM
oe_order_headers_all h,
oe_order_lines_all pl,
oe_order_lines_all l,
mtl_transaction_accounts mta,
mtl_material_transactions mmt
where ( (mmt.transaction_source_type_id = 2
and mta.transaction_source_type_id = 2)
or
(mmt.transaction_source_type_id = 13
and mmt.transaction_action_id = 9
and mta.transaction_source_type_id = 13)
)
and mmt.transaction_id = mta.transaction_id
and mta.accounting_line_type in (2, 35)
and pl.org_id = l.org_id
and h.org_id = l.org_id
and l.line_id = mmt.trx_source_line_id
and l.line_category_code = 'ORDER'
and pl.line_category_code = 'ORDER'
and pl.line_id = nvl(l.top_model_line_id, l.line_id)
and h.header_id = l.header_id
and h.header_id = pl.header_id
AND greatest(
nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(mta.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(mmt.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
BETWEEN g_push_from_date and g_push_to_date
UNION
SELECT primary_key1, l_seq_id, 1
FROM opi_edw_cogs_inc
WHERE view_id =1;
INSERT
INTO opi_edw_cogs_inc(primary_key1, seq_id, view_id)
SELECT /*+ parallel(mmt) */
DISTINCT mmt.transaction_id, l_seq_id, 2
FROM
oe_order_headers_all h,
oe_order_lines_all pl,
oe_order_lines_all cl,
oe_order_lines_all l,
mtl_transaction_accounts mta,
mtl_material_transactions mmt
where ( (mmt.transaction_source_type_id = 12
and mta.transaction_source_type_id = 12)
or
(mmt.transaction_source_type_id = 13
and mmt.transaction_action_id = 14
and mta.transaction_source_type_id = 13)
)
and mmt.transaction_id = mta.transaction_id
and mta.accounting_line_type in (2, 35)
and h.org_id = l.org_id
and l.line_id = mmt.trx_source_line_id
and l.line_category_code = 'RETURN'
and cl.line_id (+) = l.link_to_line_id
and pl.line_id (+) = nvl(cl.top_model_line_id, cl.line_id)
and h.header_id = l.header_id
AND greatest(
nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(mta.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(mmt.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
BETWEEN g_push_from_date and g_push_to_date
UNION
SELECT primary_key1, l_seq_id, 2
FROM opi_edw_cogs_inc
WHERE view_id =2;
INSERT
INTO opi_edw_cogs_inc(primary_key1, primary_key2, seq_id, view_id)
SELECT /*+ parallel(aid) */
DISTINCT aid.invoice_id,
aid.distribution_line_number,
l_seq_id, 3
FROM
oe_order_headers_all h,
oe_order_lines_all pl, /* parent line */
oe_order_lines_all l, /* child line */
ra_customer_trx_lines_all rcl,
ap_invoice_distributions_all aid,
ap_invoices_all ai,
mtl_material_transactions mmt,
mtl_parameters mp
WHERE ai.source = 'Intercompany'
AND aid.invoice_id = ai.invoice_id
and translate( lower(aid.REFERENCE_1), 'abcdefghijklmnopqrstuvwxyz_ -+0123456789',
'abcdefghijklmnopqrstuvwxyz_ -+') is null
and aid.org_id = ai.org_id
and rcl.CUSTOMER_TRX_LINE_ID = to_number(aid.REFERENCE_1)
and aid.line_type_lookup_code = 'ITEM'
and rcl.interface_line_attribute6 = l.line_id
and pl.line_id = nvl(l.top_model_line_id, l.line_id)
and pl.org_id = l.org_id
and h.org_id = l.org_id
and h.header_id = l.header_id
and h.header_id = pl.header_id
and l.line_category_code = 'ORDER'
and pl.line_category_code = 'ORDER'
and rcl.interface_line_attribute7 = mmt.transaction_id
and nvl(mmt.logical_transaction,0) <> 1
and mmt.organization_id = mp.organization_id
and mp.process_enabled_flag <> 'Y'
AND greatest(
nvl(aid.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(ai.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(l.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(pl.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
nvl(h.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')))
BETWEEN g_push_from_date and g_push_to_date
UNION
SELECT primary_key1, primary_key2, l_seq_id, 3
FROM opi_edw_cogs_inc
WHERE view_id =3;
DELETE opi_edw_cogs_inc WHERE view_id = p_view_id AND seq_id <> l_seq_id;
Insert Into opi_edw_cogs_fstg
(
ACCOUNT,
BASE_CURRENCY_FK,
BASE_UOM_FK,
BILL_TO_LOC_FK,
BILL_TO_SITE_FK,
CAMPAIGN_ACTL_FK,
CAMPAIGN_INIT_FK,
campaign_status_actl_fk,
campaign_status_init_fk,
COGS_B,
cogs_date,
cogs_date_fk,
COGS_G,
COGS_PK,
COGS_T,
COST_ELEMENT,
CUSTOMER_FK,
DATE_BOOKED_FK,
DATE_PROMISED_FK,
DATE_REQUESTED_FK,
DATE_SCHEDULED_FK,
DATE_SHIPPED_FK,
ICAP_QTY_B,
INSTANCE_FK,
INV_ORG_FK,
ITEM_ORG_FK,
LOCATOR_FK,
LOT,
MARKET_SEGMENT_FK,
MEDCHN_ACTL_FK,
MEDCHN_INIT_FK,
MONTH_BOOKED_FK,
OFFER_HDR_FK,
OFFER_LINE_FK,
OPERATING_UNIT_FK,
ORDER_CATEGORY_FK,
order_date,
ORDER_LEAD_TIME,
order_line_id,
ORDER_NUMBER,
ORDER_SOURCE_FK,
ORDER_TYPE_FK,
PRIM_SALES_REP_FK,
prim_salesresource_fk,
PROJECT_FK,
PROMISE_LEAD_TIME,
PROM_EARLY_COUNT,
PROM_EARLY_VAL_G,
PROM_LATE_COUNT,
PROM_LATE_VAL_G,
REQUEST_LEAD_TIME,
REQ_EARLY_COUNT,
REQ_EARLY_VAL_G,
REQ_LATE_COUNT,
REQ_LATE_VAL_G,
REVISION,
RMA_QTY_B,
RMA_VAL_G,
RMA_VAL_T,
SALES_CHANNEL_FK,
SERIAL_NUMBER,
SET_OF_BOOKS_FK,
ship_inv_locator_fk,
SHIPPED_QTY_B,
SHIP_TO_LOC_FK,
SHIP_TO_SITE_FK,
TARGET_SEGMENT_ACTL_FK,
TARGET_SEGMENT_INIT_FK,
TASK_FK,
TOP_MODEL_ITEM_FK,
TRX_CURRENCY_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,
WAYBILL_NUMBER,
OPERATION_CODE,
collection_status,
creation_date,
last_update_date
,delivery_id )
SELECT /*+ ALL_ROWS */
ACCOUNT,
NVL(BASE_CURRENCY_FK,'NA_EDW'),
NVL(BASE_UOM_FK,'NA_EDW'),
NVL(BILL_TO_LOC_FK,'NA_EDW'),
NVL(BILL_TO_SITE_FK,'NA_EDW'),
NVL(CAMPAIGN_ACTL_FK,'NA_EDW'),
NVL(CAMPAIGN_INIT_FK,'NA_EDW'),
Nvl(campaign_status_actl_fk, 'NA_EDW'),
Nvl(campaign_status_init_fk, 'NA_EDW'),
COGS_B,
cogs_date,
cogs_date_fk,
global_currency_rate* cogs_b cogs_g,
COGS_PK,
COGS_T,
COST_ELEMENT,
NVL(CUSTOMER_FK,'NA_EDW'),
DATE_BOOKED_FK,
DATE_PROMISED_FK,
DATE_REQUESTED_FK,
DATE_SCHEDULED_FK,
DATE_SHIPPED_FK,
ICAP_QTY_B,
NVL(INSTANCE_FK,'NA_EDW'),
NVL(INV_ORG_FK,'NA_EDW'),
NVL(ITEM_ORG_FK,'NA_EDW'),
NVL(LOCATOR_FK,'NA_EDW'),
LOT,
NVL(MARKET_SEGMENT_FK,'NA_EDW'),
NVL(MEDCHN_ACTL_FK,'NA_EDW'),
NVL(MEDCHN_INIT_FK,'NA_EDW'),
MONTH_BOOKED_FK,
NVL(OFFER_HDR_FK,'NA_EDW'),
NVL(OFFER_LINE_FK,'NA_EDW'),
NVL(OPERATING_UNIT_FK,'NA_EDW'),
NVL(ORDER_CATEGORY_FK,'NA_EDW'),
order_date,
ORDER_LEAD_TIME,
order_line_id,
ORDER_NUMBER,
NVL(ORDER_SOURCE_FK,'NA_EDW'),
NVL(ORDER_TYPE_FK,'NA_EDW'),
NVL(PRIM_SALES_REP_FK,'NA_EDW'),
Nvl(prim_salesresource_fk, 'NA_EDW'),
NVL(PROJECT_FK,'NA_EDW'),
PROMISE_LEAD_TIME,
PROM_EARLY_COUNT,
prom_early_val_g * global_currency_rate,
PROM_LATE_COUNT,
prom_late_val_g * global_currency_rate,
REQUEST_LEAD_TIME,
REQ_EARLY_COUNT,
req_early_val_g * global_currency_rate,
REQ_LATE_COUNT,
req_late_val_g * global_currency_rate,
REVISION,
RMA_QTY_B,
rma_val_t * global_currency_rate rma_val_g,
RMA_VAL_T,
NVL(SALES_CHANNEL_FK,'NA_EDW'),
SERIAL_NUMBER,
NVL(SET_OF_BOOKS_FK,'NA_EDW'),
Nvl(ship_inv_locator_fk,'NA_EDW'),
SHIPPED_QTY_B,
NVL(SHIP_TO_LOC_FK,'NA_EDW'),
NVL(SHIP_TO_SITE_FK,'NA_EDW'),
NVL(TARGET_SEGMENT_ACTL_FK,'NA_EDW'),
NVL(TARGET_SEGMENT_INIT_FK,'NA_EDW'),
NVL(TASK_FK,'NA_EDW'),
NVL(TOP_MODEL_ITEM_FK,'NA_EDW'),
NVL(TRX_CURRENCY_FK,'NA_EDW'),
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,
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,
WAYBILL_NUMBER,
NULL, -- OPERATION_CODE
Decode( global_currency_rate,
-1, 'RATE NOT AVAILABLE',
-2, 'INVALID CURRENCY',
'LOCAL READY'),
Sysdate,
Sysdate
,delivery_id
FROM opi_edw_cogs_fcv
WHERE view_id = p_view_id
AND seq_id = p_seq_id;
SELECT DISTINCT
base_currency_fk from_currency,
Substr(cogs_date_fk, 1,10) c_date,
collection_status
FROM opi_edw_cogs_fstg
WHERE collection_status IN ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND Substr(cogs_pk,0,3) <> 'OPM'
ORDER BY from_currency, c_date;
l_rows_inserted Number:=0;
edw_log.put_line('Inserting into local staging table for view type 1');
edw_log.put_line('Inserted ' || Nvl(l_row_count_view1,0) ||
' rows into local staging table for view type 1');
edw_log.put_line('Inserting into local staging table for view type 2');
edw_log.put_line('Inserted ' || Nvl(l_row_count_view2,0) ||
' rows into local staging table for view type 2');
edw_log.put_line('Inserting into local staging table for view type 3');
edw_log.put_line('Inserted ' || Nvl(l_row_count_view3,0) ||
' rows into local staging table for view type 3');
edw_log.put_line('For all view types, inserted ' || Nvl(g_row_count,0)
|| ' rows into local staging table.');
INSERT INTO opi_edw_cogs_inc(view_id, primary_key1, primary_key2 )
SELECT Decode(Substr(cogs_pk,0,3), 'INV', 1, 'RMA', 2, 'ICI', 3 ) view_id,
Decode(Substr(cogs_pk,0,3),
'INV', Substr(cogs_pk,5,Instr(cogs_pk,'-',1,2) -5),
'RMA', Substr(cogs_pk,5,Instr(cogs_pk,'-',1,2) -5),
'ICI', Substr(cogs_pk,Instr(cogs_pk, '-',1,2)+1,
Instr(cogs_pk,'-', 1, 3)- Instr(cogs_pk, '-',1,2)-1)
) primary_key1,
Decode(Substr(cogs_pk,0,3), 'INV', NULL, 'RMA', NULL,
'ICI', Substr(cogs_pk,5,Instr(cogs_pk,'-',1,2) -5)) primary_key2
FROM opi_edw_cogs_fstg
WHERE collection_status IN ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND Substr(cogs_pk,0,3) <> 'OPM'
;
DELETE opi_edw_cogs_fstg
WHERE collection_status IN ('RATE NOT AVAILABLE','INVALID CURRENCY')
AND Substr(cogs_pk,0,3) <> 'OPM';
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');