[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_MISSING_RATES
IS
BEGIN
INSERT INTO poa_edw_rcv_txns_inc(primary_key)
SELECT TO_NUMBER(SUBSTR(RCV_TXN_PK, 1, INSTR(RCV_TXN_PK, '-' )-1))
FROM POA_EDW_RCV_TXNS_FSTG
where COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
COLLECTION_STATUS = 'INVALID CURRENCY';
edw_log.put_line('INSERTING ' || to_char(sql%rowcount) ||
' rows into poa_edw_rcv_txns_inc table');
PROCEDURE DELETE_STG_MISSING_RATES
IS
BEGIN
DELETE FROM POA_EDW_RCV_TXNS_FSTG
WHERE COLLECTION_STATUS = 'RATE NOT AVAILABLE'
OR COLLECTION_STATUS = 'INVALID CURRENCY'
AND INSTANCE_FK = (SELECT INSTANCE_CODE
FROM EDW_LOCAL_INSTANCE);
Insert Into POA_EDW_RCV_TXNS_FSTG(
DUNS_FK,
UNSPSC_FK,
SIC_CODE_FK,
AP_TERMS_FK,
BILL_OF_LADING,
BUYER_FK,
DELIVER_TO_FK,
DELIV_LOCATION_FK,
DESTIN_TYPE_FK,
EDW_BASE_UOM_FK,
EDW_UOM_FK,
EXPCT_RCV_DATE_FK,
FREIGHT_TERMS_FK,
INSPECT_QUAL_FK,
INSPECT_STATUS_FK,
INSTANCE_FK,
INVOICE_NUM,
ITEM_REVISION_FK,
LOCATOR_FK,
LST_ACCPT_DATE_FK,
NEED_BY_DATE_FK,
NUM_DAYS_TO_FULL_DEL,
PACKING_SLIP,
PARNT_TXN_DATE_FK,
PARNT_TXN_TYPE_FK,
PO_LINE_TYPE_FK,
PRICE_G,
PRICE_T,
PROMISED_DATE_FK,
PURCHASE_CLASS_CODE_FK,
QTY_ACCEPT,
QTY_DELIVER,
QTY_RECEIVED,
QTY_REJECT,
QTY_RETURN_TO_RECEIVING,
QTY_RETURN_TO_VENDOR,
QTY_TRANSFER,
QTY_TXN,
QTY_TXN_NET,
RCV_DEL_TO_ORG_FK,
RCV_LOCATION_FK,
RCV_ROUTING_FK,
RCV_TXN_PK,
RECEIPT_NUM_INST,
RECEIPT_SOURCE_FK,
RECEIVE_EXCEP_FK,
RMA_REFERENCE,
SHIPMENT_NUM,
SHIPPED_TO_DATE_FK,
SHIP_HDR_COMMENTS,
SOURCE_TXN_NUMBER,
SRC_CREAT_DATE_FK,
SUBST_UNORD_FK,
SUPPLIER_ITEM_NUM_FK,
SUPPLIER_SITE_FK,
SUP_SITE_GEOG_FK,
TXN_COMMENTS,
TXN_CREAT_FK,
TXN_CUR_CODE_FK,
TXN_DATE_FK,
TXN_REASON_FK,
TXN_TYPE_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_ENTERED_FK,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
VENDOR_LOT_NUM,
WAY_AIRBILL_NUM,
po_distribution_id,
OPERATION_CODE,
COLLECTION_STATUS)
select
NVL(DUNS_FK, 'NA_EDW'),
NVL(UNSPSC_FK, 'NA_EDW'),
NVL(SIC_CODE_FK, 'NA_EDW'),
NVL(AP_TERMS_FK,'NA_EDW'),
BILL_OF_LADING,
NVL(BUYER_FK,'NA_EDW'),
NVL(DELIVER_TO_FK,'NA_EDW'),
NVL(DELIV_LOCATION_FK,'NA_EDW'),
NVL(DESTIN_TYPE_FK,'NA_EDW'),
NVL(EDW_BASE_UOM_FK,'NA_EDW'),
NVL(EDW_UOM_FK,'NA_EDW'),
NVL(EXPCT_RCV_DATE_FK,'NA_EDW'),
NVL(FREIGHT_TERMS_FK,'NA_EDW'),
NVL(INSPECT_QUAL_FK,'NA_EDW'),
NVL(INSPECT_STATUS_FK,'NA_EDW'),
NVL(INSTANCE_FK,'NA_EDW'),
INVOICE_NUM,
NVL(ITEM_REVISION_FK,'NA_EDW'),
NVL(LOCATOR_FK,'NA_EDW'),
NVL(LST_ACCPT_DATE_FK,'NA_EDW'),
NVL(NEED_BY_DATE_FK,'NA_EDW'),
NUM_DAYS_TO_FULL_DEL,
PACKING_SLIP,
NVL(PARNT_TXN_DATE_FK,'NA_EDW'),
NVL(PARNT_TXN_TYPE_FK,'NA_EDW'),
NVL(PO_LINE_TYPE_FK,'NA_EDW'),
PRICE_G,
PRICE_T,
NVL(PROMISED_DATE_FK,'NA_EDW'),
NVL(PURCHASE_CLASS_CODE_FK,'NA_EDW'),
QTY_ACCEPT,
QTY_DELIVER,
QTY_RECEIVED,
QTY_REJECT,
QTY_RETURN_TO_RECEIVING,
QTY_RETURN_TO_VENDOR,
QTY_TRANSFER,
QTY_TXN,
QTY_TXN_NET,
NVL(RCV_DEL_TO_ORG_FK,'NA_EDW'),
NVL(RCV_LOCATION_FK,'NA_EDW'),
NVL(RCV_ROUTING_FK,'NA_EDW'),
RCV_TXN_PK,
RECEIPT_NUM_INST,
NVL(RECEIPT_SOURCE_FK,'NA_EDW'),
NVL(RECEIVE_EXCEP_FK,'NA_EDW'),
RMA_REFERENCE,
SHIPMENT_NUM,
NVL(SHIPPED_TO_DATE_FK,'NA_EDW'),
SHIP_HDR_COMMENTS,
SOURCE_TXN_NUMBER,
NVL(SRC_CREAT_DATE_FK,'NA_EDW'),
NVL(SUBST_UNORD_FK,'NA_EDW'),
NVL(SUPPLIER_ITEM_NUM_FK,'NA_EDW'),
NVL(SUPPLIER_SITE_FK,'NA_EDW'),
NVL(SUP_SITE_GEOG_FK,'NA_EDW'),
TXN_COMMENTS,
NVL(TXN_CREAT_FK,'NA_EDW'),
NVL(TXN_CUR_CODE_FK,'NA_EDW'),
NVL(TXN_DATE_FK,'NA_EDW'),
NVL(TXN_REASON_FK,'NA_EDW'),
NVL(TXN_TYPE_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_ENTERED_FK,'NA_EDW'),
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,
VENDOR_LOT_NUM,
WAY_AIRBILL_NUM,
po_distribution_id,
NULL, -- OPERATION_CODE
decode(PRICE_G,
-1, 'RATE NOT AVAILABLE',
-2, 'INVALID CURRENCY', 'LOCAL READY')
from POA_EDW_RECEIVING_TXN_FCV
WHERE view_id = p_view_id
AND seq_id = p_seq_id;
PROCEDURE DELETE_DUPLICATES IS
-- Cursor to delete duplicates
CURSOR Dup_Rec IS
SELECT primary_key
FROM poa_edw_rcv_txns_inc
ORDER BY primary_key
FOR UPDATE;
DELETE FROM poa_edw_rcv_txns_inc
WHERE CURRENT OF Dup_Rec;
select poa_edw_rcv_txns_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_rcv_txns_inc
SET seq_id = l_seq_id
WHERE seq_id IS NULL;
edw_log.put_line( 'Updated ' || p_count || ' records');
/* Currently, 2 tables are considered for last_update_date; we may
INSERT INTO poa_edw_rcv_txns_inc(primary_key, seq_id)
SELECT rcv.transaction_id, l_seq_id
FROM RCV_SHIPMENT_LINES rsl,
RCV_TRANSACTIONS rcv
WHERE rcv.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID
AND greatest(rcv.last_update_date,
rsl.last_update_date)
between g_push_from_date and g_push_to_date;
SELECT DISTINCT NVL(poh.rate_date, pll.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 (select TO_NUMBER(SUBSTR(RCV_TXN_PK, 1,
INSTR(RCV_TXN_PK, '-' )-1))
TRANSACTION_ID,
Collection_Status
from POA_EDW_RCV_TXNS_FSTG
where COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
COLLECTION_STATUS = 'INVALID CURRENCY') fstg,
RCV_TRANSACTIONS RCV,
PO_LINE_LOCATIONS_ALL PLL,
PO_HEADERS_ALL POH,
GL_SETS_OF_BOOKS GSOB,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE fstg.TRANSACTION_ID = RCV.TRANSACTION_ID
AND RCV.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID
AND nvl(POH.ORG_ID, -999) = nvl(FSP.ORG_ID, -999)
AND FSP.set_of_books_id = GSOB.set_of_books_id;
DELETE_DUPLICATES;
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');
INSERT_MISSING_RATES;
DELETE_STG_MISSING_RATES;
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');