[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_MISSING_RATES
IS
BEGIN
INSERT INTO poa_edw_alines_inc(primary_key)
SELECT po_line_id
FROM POA_EDW_ALINES_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_alines_inc table');
PROCEDURE DELETE_STG_MISSING_RATES
IS
BEGIN
DELETE FROM POA_EDW_ALINES_FSTG
WHERE COLLECTION_STATUS = 'RATE NOT AVAILABLE'
OR COLLECTION_STATUS = 'INVALID CURRENCY'
AND INSTANCE_FK = (SELECT INSTANCE_CODE
FROM EDW_LOCAL_INSTANCE);
l_rows_inserted Number:=0;
Insert Into POA_EDW_ALINES_FSTG (
DUNS_FK,
UNSPSC_FK,
SIC_CODE_FK,
ACCPT_DUE_DATE_FK,
ACCPT_REQUIRED_FK,
AGREE_LN_INST_PK,
AMT_AGREED_G,
AMT_AGREED_T,
AMT_MIN_RELEASE_G,
AMT_MIN_RELEASE_T,
AMT_RELEASED_G,
AMT_RELEASED_T,
APPROVED_DATE_FK,
APPROVED_FK,
APPROVER_FK,
AP_TERMS_FK,
BASE_UOM_FK,
BILL_LOCATION_FK,
BUYER_FK,
CANCELLED_FK,
CANCEL_REASON,
COMMENTS,
CONFIRM_ORDER_FK,
CONTRACT_EFFECTIVE_FK,
EDI_PROCESSED_FK,
END_DATE_FK,
FOB_FK,
FREIGHT_TERMS_FK,
FROZEN_FK,
INSTANCE_FK,
ITEM_DESCRIPTION,
ITEM_ID,
ITEM_REVISION_FK,
LIST_PRICE_G,
LIST_PRICE_T,
LNE_CLOSED_FK,
LNE_CREAT_DATE_FK,
MARKET_PRICE_G,
MARKET_PRICE_T,
NEG_BY_PREPARE_FK,
OPERATING_UNIT_FK,
PO_CLOSED_FK,
PO_CREATE_DATE_FK,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_TYPE_FK,
PO_PRINT_DATE_FK,
PO_SUPPLIER_NOTE,
PO_TYPE_FK,
PRICE_BREAK_FK,
PRICE_LIMIT_G,
PRICE_LIMIT_T,
PRICE_TYPE_FK,
QTY_AGREED_T,
QTY_MAX_ORDER_T,
QTY_MIN_ORDER_T,
QTY_ORDERED_T,
QTY_RELEASED_T,
RECEIVER_NOTE,
REVISED_DATE_FK,
SHIP_LOCATION_FK,
SHIP_VIA_FK,
START_DATE_FK,
SUPPLIER_ITEM_NUM_FK,
SUPPLIER_SITE_FK,
SUPPLY_AGREE_FK,
SUP_SITE_GEOG_FK,
TXN_CUR_CODE_FK,
TXN_CUR_DATE_FK,
TXN_CUR_RATE_TYPE,
TXN_REASON_FK,
TXN_UOM_FK,
UNIT_PRICE_G,
UNIT_PRICE_T,
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_HOLD_FK,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
OPERATION_CODE,
COLLECTION_STATUS)
select
NVL(DUNS_FK, 'NA_EDW'),
NVL(UNSPSC_FK, 'NA_EDW'),
NVL(SIC_CODE_FK, 'NA_EDW'),
NVL(ACCPT_DUE_DATE_FK,'NA_EDW'),
NVL(ACCPT_REQUIRED_FK,'NA_EDW'),
AGREE_LN_INST_PK,
AMT_AGREED_G,
AMT_AGREED_T,
AMT_MIN_RELEASE_G,
AMT_MIN_RELEASE_T,
AMT_RELEASED_G,
AMT_RELEASED_T,
NVL(APPROVED_DATE_FK,'NA_EDW'),
NVL(APPROVED_FK,'NA_EDW'),
NVL(APPROVER_FK,'NA_EDW'),
NVL(AP_TERMS_FK,'NA_EDW'),
NVL(BASE_UOM_FK,'NA_EDW'),
NVL(BILL_LOCATION_FK,'NA_EDW'),
NVL(BUYER_FK,'NA_EDW'),
NVL(CANCELLED_FK,'NA_EDW'),
CANCEL_REASON,
COMMENTS,
NVL(CONFIRM_ORDER_FK,'NA_EDW'),
NVL(CONTRACT_EFFECTIVE_FK,'NA_EDW'),
NVL(EDI_PROCESSED_FK,'NA_EDW'),
NVL(END_DATE_FK,'NA_EDW'),
NVL(FOB_FK,'NA_EDW'),
NVL(FREIGHT_TERMS_FK,'NA_EDW'),
NVL(FROZEN_FK,'NA_EDW'),
NVL(INSTANCE_FK,'NA_EDW'),
ITEM_DESCRIPTION,
ITEM_ID,
NVL(ITEM_REVISION_FK,'NA_EDW'),
LIST_PRICE_G,
LIST_PRICE_T,
NVL(LNE_CLOSED_FK,'NA_EDW'),
NVL(LNE_CREAT_DATE_FK,'NA_EDW'),
MARKET_PRICE_G,
MARKET_PRICE_T,
NVL(NEG_BY_PREPARE_FK,'NA_EDW'),
NVL(OPERATING_UNIT_FK,'NA_EDW'),
NVL(PO_CLOSED_FK,'NA_EDW'),
NVL(PO_CREATE_DATE_FK,'NA_EDW'),
PO_HEADER_ID,
PO_LINE_ID,
NVL(PO_LINE_TYPE_FK,'NA_EDW'),
NVL(PO_PRINT_DATE_FK,'NA_EDW'),
PO_SUPPLIER_NOTE,
NVL(PO_TYPE_FK,'NA_EDW'),
NVL(PRICE_BREAK_FK,'NA_EDW'),
PRICE_LIMIT_G,
PRICE_LIMIT_T,
NVL(PRICE_TYPE_FK,'NA_EDW'),
QTY_AGREED_T,
QTY_MAX_ORDER_T,
QTY_MIN_ORDER_T,
QTY_ORDERED_T,
QTY_RELEASED_T,
RECEIVER_NOTE,
NVL(REVISED_DATE_FK,'NA_EDW'),
NVL(SHIP_LOCATION_FK,'NA_EDW'),
NVL(SHIP_VIA_FK,'NA_EDW'),
NVL(START_DATE_FK,'NA_EDW'),
NVL(SUPPLIER_ITEM_NUM_FK,'NA_EDW'),
NVL(SUPPLIER_SITE_FK,'NA_EDW'),
NVL(SUPPLY_AGREE_FK,'NA_EDW'),
NVL(SUP_SITE_GEOG_FK,'NA_EDW'),
NVL(TXN_CUR_CODE_FK,'NA_EDW'),
NVL(TXN_CUR_DATE_FK,'NA_EDW'),
TXN_CUR_RATE_TYPE,
NVL(TXN_REASON_FK,'NA_EDW'),
NVL(TXN_UOM_FK,'NA_EDW'),
UNIT_PRICE_G,
UNIT_PRICE_T,
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'),
NVL(USER_HOLD_FK,'NA_EDW'),
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
NULL, -- OPERATION_CODE
collection_status
from POA_EDW_AGREEMENT_LINES_FCV
WHERE view_id = p_view_id
AND seq_id = p_seq_id;
l_rows_inserted := sql%rowcount;
edw_log.put_line('...Inserted ' || to_char(nvl(l_rows_inserted,0))||
' rows into the local staging table');
RETURN (l_rows_inserted);
PROCEDURE DELETE_DUPLICATES IS
-- Cursor to delete duplicates
CURSOR Dup_Rec IS
SELECT primary_key
FROM poa_edw_alines_inc
ORDER BY primary_key
FOR UPDATE;
DELETE FROM poa_edw_alines_inc
WHERE CURRENT OF Dup_Rec;
select poa_edw_alines_inc_s.nextval into l_seq_id from dual;
UPDATE poa_edw_alines_inc
SET seq_id = l_seq_id
WHERE seq_id IS NULL;
edw_log.put_line( 'Updated ' || p_count || ' records');
INSERT INTO poa_edw_alines_inc(primary_key, seq_id)
SELECT pol.po_line_id, l_seq_id
FROM po_headers_all poh,
po_lines_all pol
WHERE poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND poh.po_header_id = pol.po_header_id
AND greatest(poh.last_update_date, pol.last_update_date)
between g_push_date_range1 and g_push_date_range2;
SELECT DISTINCT NVL(poh.rate_date, pol.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_ALINES_FSTG fstg,
PO_HEADERS_ALL POH,
po_lines_all pol,
GL_SETS_OF_BOOKS GSOB,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
where (fstg.COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
fstg.COLLECTION_STATUS = 'INVALID CURRENCY')
AND fstg.PO_HEADER_ID = POH.PO_HEADER_ID
AND fstg.po_line_id = pol.po_line_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');