[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_INC
IS
BEGIN
DELETE from poa_edw_po_dist_inc
WHERE batch_id <> 0;
PROCEDURE INSERT_MISSING_RATES
IS
BEGIN
INSERT INTO poa_edw_po_dist_inc(primary_key,batch_id)
SELECT DESTRIBUTION_ID,0
FROM POA_EDW_PO_DIST_FSTG fstg
WHERE fstg.COLLECTION_STATUS = 'RATE NOT AVAILABLE'
OR fstg.COLLECTION_STATUS = 'INVALID CURRENCY';
edw_log.put_line('INSERTING ' || to_char(sql%rowcount) ||
' rows into poa_edw_po_dist_inc table');
PROCEDURE DELETE_STG_MISSING_RATES
IS
BEGIN
-- DELETE FROM POA_EDW_PO_DIST_FSTG
-- WHERE COLLECTION_STATUS = 'RATE NOT AVAILABLE'
-- OR COLLECTION_STATUS = 'INVALID CURRENCY'
-- AND INSTANCE_FK = (SELECT INSTANCE_CODE
-- FROM EDW_LOCAL_INSTANCE);
edw_log.put_line('Start inserting to local staging table... ');
UPDATE poa_edw_po_dist_inc
SET (check_cut_date, invoice_received_date) =
(SELECT min(ack.check_date), min(ain.invoice_received_date)
FROM ap_invoice_distributions_all aid,
ap_invoice_payments_all aip,
ap_checks_all ack,
ap_invoices_all ain
WHERE aid.po_distribution_id = primary_key
AND aid.invoice_id = aip.invoice_id (+)
AND aip.check_id = ack.check_id (+)
AND aid.invoice_id = ain.invoice_id);
Insert Into POA_EDW_PO_DIST_FSTG(
CHECK_CUT_DATE_FK,
INV_RECEIVED_DATE_FK,
INV_CREATION_DATE_FK,
GOODS_RECEIVED_DATE_FK,
DUNS_FK,
UNSPSC_FK,
SIC_CODE_FK,
APPRV_SUPPLIER_FK,
TASK_FK,
PO_CREATION_CYCLE_TIME,
ORDER_TO_PAY_CYCLE_TIME,
RECEIVE_TO_PAY_CYCL_TIME,
INV_CREATION_CYCLE_TIME,
INV_TO_PAY_CYCLE_TIME,
IPV_T,
IPV_G,
QTY_BILLED_B,
QTY_CANCELLED_B,
QTY_DELIVERED_B,
QTY_ORDERED_B,
ACCPT_DUE_DATE_FK,
ACCPT_REQUIRED_FK,
ACCRUED_FK,
AMT_BILLED_G,
AMT_BILLED_T,
AMT_CONTRACT_G,
AMT_CONTRACT_T,
AMT_LEAKAGE_G,
AMT_LEAKAGE_T,
AMT_NONCONTRACT_G,
AMT_NONCONTRACT_T,
AMT_PURCHASED_G,
AMT_PURCHASED_T,
APPROVER_FK,
AP_TERMS_FK,
BILL_LOCATION_FK,
BUYER_FK,
CONFIRM_ORDER_FK,
CONTRACT_NUM,
CONTRACT_TYPE_FK,
DELIVER_TO_FK,
DELIV_LOCATION_FK,
DESTIN_ORG_FK,
DESTIN_TYPE_FK,
DESTRIBUTION_ID,
DST_CREAT_DATE_FK,
DST_ENCUMB_FK,
EDI_PROCESSED_FK,
FOB_FK,
FREIGHT_TERMS_FK,
FROZEN_FK,
INSPECTION_REQ_FK,
INSTANCE_FK,
ITEM_DESCRIPTION,
ITEM_ID,
ITEM_FK,
LINE_LOCATION_ID,
LIST_PRC_UNIT_T,
LIST_PRC_UNIT_G,
LNE_CREAT_DATE_FK,
LNE_SUPPLIER_NOTE,
LST_ACCPT_DATE_FK,
MARKET_PRICE_T,
MARKET_PRICE_G,
NEED_BY_DATE_FK,
NEG_BY_PREPARE_FK,
ONLINE_REQ_FK,
PCARD_PROCESS_FK,
POTENTIAL_SVG_G,
POTENTIAL_SVG_T,
PO_ACCEPT_DATE_FK,
PO_APP_DATE_FK,
PO_COMMENTS,
PO_CREATE_DATE_FK,
PO_DIST_INST_PK,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_TYPE_FK,
PO_NUMBER,
PO_RECEIVER_NOTE,
PO_RELEASE_ID,
PRICE_BREAK_FK,
PRICE_T,
PRICE_G,
PRICE_LIMIT_T,
PRICE_LIMIT_G,
PRICE_TYPE_FK,
PRINTED_DATE_FK,
PROMISED_DATE_FK,
PURCH_CLASS_FK,
RCV_ROUTING_FK,
RECEIPT_REQ_FK,
RELEASE_DATE_FK,
RELEASE_HOLD_FK,
RELEASE_NUM,
REQ_APPRV_DATE_FK,
REQ_CREAT_DATE_FK,
REVISED_DATE_FK,
REVISION_NUM,
SHIPMENT_TYPE_FK,
SHIP_LOCATION_FK,
SHIP_TO_ORG_FK,
SHIP_VIA_FK,
SHP_APPROVED_FK,
SHP_APP_DATE_FK,
SHP_CANCELLED_FK,
SHP_CANCEL_REASON,
SHP_CLOSED_FK,
SHP_CLOSED_REASON,
SHP_CREAT_DATE_FK,
SHP_SRC_SHIP_ID,
SHP_TAXABLE_FK,
SOB_FK,
SOURCE_DIST_ID,
SUB_RECEIPT_FK,
SUPPLIER_ITEM_FK,
SUPPLIER_NOTE,
SUPPLIER_SITE_FK,
SUP_SITE_GEOG_FK,
TXN_CUR_CODE_FK,
TXN_CUR_DATE_FK,
TXN_REASON_FK,
EDW_UOM_FK,
EDW_BASE_UOM_FK,
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_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
OPERATION_CODE,
COLLECTION_STATUS)
select
NVL(CHECK_CUT_DATE_FK,'NA_EDW'),
NVL(INV_RECEIVED_DATE_FK,'NA_EDW'),
NVL(INV_CREATION_DATE_FK,'NA_EDW'),
NVL(GOODS_RECEIVED_DATE_FK,'NA_EDW'),
NVL(DUNS_FK,'NA_EDW'),
NVL(UNSPSC_FK,'NA_EDW'),
NVL(SIC_CODE_FK,'NA_EDW'),
NVL(APPRV_SUPPLIER_FK,'NA_EDW'),
NVL(TASK_FK,'NA_EDW'),
PO_CREATION_CYCLE_TIME,
ORDER_TO_PAY_CYCLE_TIME,
RECEIVE_TO_PAY_CYCL_TIME,
INV_CREATION_CYCLE_TIME,
INV_TO_PAY_CYCLE_TIME,
IPV_T,
round(IPV_G / l_mau) * l_mau,
QTY_BILLED_B,
QTY_CANCELLED_B,
QTY_DELIVERED_B,
QTY_ORDERED_B,
NVL(ACCPT_DUE_DATE_FK,'NA_EDW'),
NVL(ACCPT_REQUIRED_FK,'NA_EDW'),
NVL(ACCRUED_FK,'NA_EDW'),
round(AMT_BILLED_G / l_mau) * l_mau,
AMT_BILLED_T,
round(AMT_CONTRACT_G / l_mau) * l_mau,
AMT_CONTRACT_T,
round(AMT_LEAKAGE_G / l_mau) * l_mau,
AMT_LEAKAGE_T,
round(AMT_NONCONTRACT_G / l_mau) * l_mau,
AMT_NONCONTRACT_T,
round(AMT_PURCHASED_G / l_mau) * l_mau,
AMT_PURCHASED_T,
NVL(APPROVER_FK,'NA_EDW'),
NVL(AP_TERMS_FK,'NA_EDW'),
NVL(BILL_LOCATION_FK,'NA_EDW'),
NVL(BUYER_FK,'NA_EDW'),
NVL(CONFIRM_ORDER_FK,'NA_EDW'),
CONTRACT_NUM,
NVL(CONTRACT_TYPE_FK,'NA_EDW'),
NVL(DELIVER_TO_FK,'NA_EDW'),
NVL(DELIV_LOCATION_FK,'NA_EDW'),
NVL(DESTIN_ORG_FK,'NA_EDW'),
NVL(DESTIN_TYPE_FK,'NA_EDW'),
DISTRIBUTION_ID,
NVL(DST_CREAT_DATE_FK,'NA_EDW'),
NVL(DST_ENCUMB_FK,'NA_EDW'),
NVL(EDI_PROCESSED_FK,'NA_EDW'),
NVL(FOB_FK,'NA_EDW'),
NVL(FREIGHT_TERMS_FK,'NA_EDW'),
NVL(FROZEN_FK,'NA_EDW'),
NVL(INSPECTION_REQ_FK,'NA_EDW'),
NVL(INSTANCE_FK,'NA_EDW'),
ITEM_DESCRIPTION,
ITEM_ID,
NVL(ITEM_FK,'NA_EDW'),
LINE_LOCATION_ID,
LIST_PRC_UNIT_T,
round(LIST_PRC_UNIT_G / l_mau) * l_mau,
NVL(LNE_CREAT_DATE_FK,'NA_EDW'),
LNE_SUPPLIER_NOTE,
NVL(LST_ACCPT_DATE_FK,'NA_EDW'),
MARKET_PRICE_T,
round(MARKET_PRICE_G / l_mau) * l_mau,
NVL(NEED_BY_DATE_FK,'NA_EDW'),
NVL(NEG_BY_PREPARE_FK,'NA_EDW'),
NVL(ONLINE_REQ_FK,'NA_EDW'),
NVL(PCARD_PROCESS_FK,'NA_EDW'),
round(POTENTIAL_SVG_G / l_mau) * l_mau,
POTENTIAL_SVG_T,
NVL(PO_ACCEPT_DATE_FK,'NA_EDW'),
NVL(PO_APP_DATE_FK,'NA_EDW'),
PO_COMMENTS,
NVL(PO_CREATE_DATE_FK,'NA_EDW'),
PO_DIST_INST_PK,
PO_HEADER_ID,
PO_LINE_ID,
NVL(PO_LINE_TYPE_FK,'NA_EDW'),
PO_NUMBER,
PO_RECEIVER_NOTE,
PO_RELEASE_ID,
NVL(PRICE_BREAK_FK,'NA_EDW'),
PRICE_T,
round(PRICE_G / l_mau) * l_mau,
PRICE_LIMIT_T,
round(PRICE_LIMIT_G / l_mau) * l_mau,
NVL(PRICE_TYPE_FK,'NA_EDW'),
NVL(PRINTED_DATE_FK,'NA_EDW'),
NVL(PROMISED_DATE_FK,'NA_EDW'),
NVL(PURCH_CLASS_FK, 'NA_EDW'),
NVL(RCV_ROUTING_FK,'NA_EDW'),
NVL(RECEIPT_REQ_FK,'NA_EDW'),
NVL(RELEASE_DATE_FK,'NA_EDW'),
NVL(RELEASE_HOLD_FK,'NA_EDW'),
RELEASE_NUM,
NVL(REQ_APPRV_DATE_FK,'NA_EDW'),
NVL(REQ_CREAT_DATE_FK,'NA_EDW'),
NVL(REVISED_DATE_FK,'NA_EDW'),
REVISION_NUM,
NVL(SHIPMENT_TYPE_FK,'NA_EDW'),
NVL(SHIP_LOCATION_FK,'NA_EDW'),
NVL(SHIP_TO_ORG_FK,'NA_EDW'),
NVL(SHIP_VIA_FK,'NA_EDW'),
NVL(SHP_APPROVED_FK,'NA_EDW'),
NVL(SHP_APP_DATE_FK,'NA_EDW'),
NVL(SHP_CANCELLED_FK,'NA_EDW'),
SHP_CANCEL_REASON,
NVL(SHP_CLOSED_FK,'NA_EDW'),
SHP_CLOSED_REASON,
NVL(SHP_CREAT_DATE_FK,'NA_EDW'),
SHP_SRC_SHIP_ID,
NVL(SHP_TAXABLE_FK,'NA_EDW'),
NVL(SOB_FK,'NA_EDW'),
SOURCE_DIST_ID,
NVL(SUB_RECEIPT_FK,'NA_EDW'),
NVL(SUPPLIER_ITEM_FK,'NA_EDW'),
SUPPLIER_NOTE,
NVL(SUPPLIER_SITE_FK,'NA_EDW'),
NVL(SUP_SITE_GEOG_FK,'NA_EDW'),
NVL(TXN_CUR_CODE_FK,'NA_EDW'),
NVL(TXN_CUR_DATE_FK,'NA_EDW'),
NVL(TXN_REASON_FK,'NA_EDW'),
NVL(EDW_UOM_FK,'NA_EDW'),
NVL(EDW_BASE_UOM_FK,'NA_EDW'),
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,
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,
NULL, -- OPERATION_CODE
collection_status
from POA_EDW_PO_DISTRIBUTIONS_FCV
WHERE view_id = p_view_id
AND seq_id = p_seq_id;
edw_log.put_line('Process Time (inserting to local staging table): '
|| edw_log.duration(l_duration) || ', Current system time: ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
PROCEDURE DELETE_DUPLICATES IS
-- Cursor to delete duplicates
CURSOR Dup_Rec IS
SELECT primary_key
FROM poa_edw_po_dist_inc
ORDER BY primary_key
FOR UPDATE;
DELETE FROM poa_edw_po_dist_inc
WHERE CURRENT OF Dup_Rec;
SELECT po_distribution_id, ceil(rownum/p_batch_size)
FROM
(SELECT pod.PO_DISTRIBUTION_ID, pol.item_id, pod.creation_date
FROM po_lines_all pol,
po_line_locations_all pll,
po_headers_all poh,
po_distributions_all pod
WHERE pod.line_location_id = pll.line_location_id
and pod.po_line_id = pol.po_line_id
and pod.po_header_id = poh.po_header_id
and pll.shipment_type = 'STANDARD'
and pll.approved_flag = 'Y'
and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
and greatest(pol.last_update_date, pll.last_update_date,
poh.last_update_date, pod.last_update_date, nvl(pod.program_update_date, pod.last_update_date))
between g_push_from_date and g_push_to_date
UNION ALL
SELECT pod.PO_DISTRIBUTION_ID, pol.item_id, pod.creation_date
FROM po_lines_all pol,
po_line_locations_all pll,
po_headers_all poh,
po_releases_all por,
po_distributions_all pod
WHERE pod.line_location_id = pll.line_location_id
and pod.po_release_id = por.po_release_id
and pod.po_line_id = pol.po_line_id
and pod.po_header_id = poh.po_header_id
and pll.shipment_type in ('BLANKET', 'SCHEDULED')
and pll.approved_flag = 'Y'
and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
and greatest(pol.last_update_date,pll.last_update_date,
poh.last_update_date,por.last_update_date,pod.last_update_date, nvl(pod.program_update_date, pod.last_update_date))
between g_push_from_date and g_push_to_date)
order by item_id, creation_date;
select poa_edw_po_dist_inc_s.nextval into l_seq_id from dual;
/** Update the seq_id for records that had missing currency rates in
the earlier PUSH. We need to repush these records again
**/
UPDATE poa_edw_po_dist_inc
SET seq_id = l_seq_id
WHERE seq_id IS NULL;
edw_log.put_line( 'Updated ' || p_count || ' records');
INSERT into poa_edw_po_dist_inc(primary_key, seq_id, batch_id)
values(l_primary_key(i), l_seq_id, l_batch_id(i));
SELECT DISTINCT NVL(pod.rate_date, pod.creation_date) Rate_Date,
decode(poh.rate_type,
'User',gsob.currency_code,
NVL(poh.currency_code,
gsob.currency_code)) From_Currency,
fstg.Collection_Status
FROM POA_EDW_PO_DIST_FSTG fstg,
po_distributions_all pod,
po_headers_all poh,
gl_sets_of_books gsob
WHERE (fstg.COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
fstg.COLLECTION_STATUS = 'INVALID CURRENCY')
AND fstg.DESTRIBUTION_ID = pod.po_distribution_id
AND fstg.PO_HEADER_ID = poh.po_header_id
AND pod.set_of_books_id = gsob.set_of_books_id
AND nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
DELETE_INC;
edw_log.put_line('Incremental table deleted');
edw_log.put_line('System time at start of delete duplicates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
DELETE_DUPLICATES;
edw_log.put_line('System time at end of delete duplicates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
edw_log.put_line('Duplicate records deleted in Inc Table');
edw_log.put_line('Inserting into local staging table for view type 1');
edw_log.put_line('Inserted ' || nvl(l_row_count1,0) ||
' rows into the local staging table for view type 1');
edw_log.put_line('Inserting missing rates...');
edw_log.put_line('System time at start of insert missing rates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
INSERT_MISSING_RATES;
edw_log.put_line('System time at end of insert missing rates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
edw_log.put_line('System time at start of delete stg missing rates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
DELETE_STG_MISSING_RATES;
edw_log.put_line('System time at end of delete stg missing rates ' || to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS'));
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');