[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_STG
IS
BEGIN
DELETE FII_AP_INV_LINES_FSTG
WHERE COLLECTION_STATUS = 'LOCAL READY'OR ( COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR COLLECTION_STATUS = 'INVALID CURRENCY')
AND INSTANCE = (SELECT INSTANCE_CODE
FROM EDW_LOCAL_INSTANCE);
PROCEDURE INSERT_MISSING_RATES_IN_TMP
IS
BEGIN
-- --------------------------------------------------------------------------------------------------
-- The variable g_acct_or_inv_date is added in the below mentioned select statement.
-- The profile option stored in the global variable g_acct_or_inv_date
-- will be stored in the column Primary_Key5 . Modified for Currency Conversion Date Enhancement,25-APR-03
-----------------------------------------------------------------------------------------------------
-- Need to store invoice_line_number in the temp table. Change done for Inv Lines Uptake
-- See design doc on fol for more details
------------------------------------------------------------------------------------------------------
INSERT INTO fii_ap_tmp_line_pk(
Primary_Key1,
Primary_Key2,
Primary_key4,
Primary_Key5 )
SELECT
TO_NUMBER(SUBSTR (INV_LINE_PK, 1, INSTR(INV_LINE_PK, '-' )-1)),
TO_NUMBER(SUBSTR (INV_LINE_PK, INSTR(INV_LINE_PK, '-')+1,INSTR(INV_LINE_PK, '-',1,2) -
(INSTR(INV_LINE_PK,'-')+1))) ,
TO_NUMBER(SUBSTR(INV_LINE_PK,INSTR('INV_LINE_PK','-',1,2)+1,INSTR(INV_LINE_PK,'-',1,3)-
(INSTR(INV_LINE_PK,'-',1,2)+1))),
g_acct_or_inv_date
FROM FII_AP_INV_LINES_FSTG fil
WHERE
fil.COLLECTION_STATUS = 'RATE NOT AVAILABLE'
OR
fil.COLLECTION_STATUS = 'INVALID CURRENCY';
edw_log.put_line('INSERTING ' || to_char(sql%rowcount) || ' rows from staging table');
SELECT instance_code
INTO l_instance1
FROM edw_local_instance;
SELECT instance_code
INTO l_instance2
FROM edw_local_instance@edw_apps_to_wh;
UPDATE FII_AP_INV_LINES_FSTG
SET COLLECTION_STATUS = 'READY'
WHERE COLLECTION_STATUS = 'LOCAL READY'
AND INSTANCE = (SELECT INSTANCE_CODE
FROM EDW_LOCAL_INSTANCE);
Insert Into FII_AP_INV_LINES_FSTG(
APPROVAL_STATUS,
ACCOUNTING_DATE,
ACCOUNTING_DATE_FK,
ACCRUAL_POSTED_FLAG,
AMT_INCLUDES_TAX_FLAG,
ASSETS_TRACKING_FLAG,
AWT_FLAG,
AWT_GROUP_ID,
BASE_CURRENCY_CODE,
BATCH_ID,
CASH_JE_BATCH_ID,
CASH_POSTED_FLAG,
CATEGORY_ID,
CCID,
CREATION_DATE,
DUNS_FK,
EMPLOYEE_FK,
ENCUMBERED_FLAG,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE_VAR,
GEOGRAPHY_FK,
GL_ACCT10_FK,
GL_ACCT1_FK,
GL_ACCT2_FK,
GL_ACCT3_FK,
GL_ACCT4_FK,
GL_ACCT5_FK,
GL_ACCT6_FK,
GL_ACCT7_FK,
GL_ACCT8_FK,
GL_ACCT9_FK,
INCOME_TAX_REGION,
INSTANCE,
INSTANCE_FK,
INV_CURRENCY_FK,
INV_DATE,
INV_FK,
INV_LINE_AMT_B,
INV_LINE_AMT_G,
INV_LINE_AMT_T,
INV_LINE_COUNT,
INV_LINE_DESCRIPTION,
INV_LINE_PK,
INV_LINE_TYPE_FK,
INV_NUM,
INV_PRICE_VAR_AMT_B,
INV_PRICE_VAR_AMT_G,
INV_PRICE_VAR_AMT_T,
INV_SOURCE_FK,
INV_TYPE,
INV_UNIT_PRICE_B,
INV_UNIT_PRICE_G,
INV_UNIT_PRICE_T,
ITEM_DESCRIPTION,
ITEM_FK,
ITEM_ID,
LAST_UPDATE_DATE,
MATCH_LINE_AMT_B,
MATCH_LINE_AMT_G,
MATCH_LINE_AMT_T,
MATCH_LINE_COUNT,
MATCH_STATUS_FLAG,
ORG_FK,
PAYMENT_TERM_FK,
POSTED_AMT_B,
POSTED_AMT_G,
POSTED_AMT_T,
POSTED_FLAG,
PO_AMT_B,
PO_AMT_G,
PO_AMT_T,
PO_DISTRIBUTION_ID,
PO_NUMBER,
PO_UNIT_PRICE_B,
PO_UNIT_PRICE_G,
PO_UNIT_PRICE_T,
PROJECT_ID,
QTY_VAR_AMT_B,
QTY_VAR_AMT_G,
QTY_VAR_AMT_T,
QUANTITY_INVOICED_G,
QUANTITY_INVOICED_T,
SIC_CODE_FK,
SOB_FK,
SUPPLIER_FK,
SUPPLIER_SITE_ID,
TOTAL_VAR_AMT_B,
TOTAL_VAR_AMT_G,
TOTAL_VAR_AMT_T,
TYPE_1099,
UNMATCH_LINE_AMT_B,
UNMATCH_LINE_AMT_G,
UNMATCH_LINE_AMT_T,
UNMATCH_LINE_COUNT,
UNSPSC_FK,
UOM_G_FK,
UOM_T_FK,
USER_ATTRIBUTE1,
USER_ATTRIBUTE10,
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,
VAT_CODE,
OPERATION_CODE,
COLLECTION_STATUS,
DISCOUNT_AMT_T, --Added the following three columns for New Information Enhancement, nov/12/2002
DISCOUNT_AMT_B,
DISCOUNT_AMT_G,
PROJECT_FK, --Added the following four columns for New Information Enhancement, nov/26/2002
EXPENDITURE_TYPE,
VOUCHER_NUMBER,
DOC_SEQUENCE_VALUE,
TASK_ID, -- Addded for bug#2926033
RCV_TRANSACTION_ID) -- Added for bug#3116554
select
APPROVAL_STATUS,
ACCOUNTING_DATE,
NVL(ACCOUNTING_DATE_FK,'NA_EDW'),
ACCRUAL_POSTED_FLAG,
AMT_INCLUDES_TAX_FLAG,
ASSETS_TRACKING_FLAG,
AWT_FLAG,
AWT_GROUP_ID,
BASE_CURRENCY_CODE,
BATCH_ID,
CASH_JE_BATCH_ID,
CASH_POSTED_FLAG,
CATEGORY_ID,
CCID,
CREATION_DATE,
NVL(DUNS_FK,'NA_EDW'),
NVL(EMPLOYEE_FK,'NA_EDW'),
ENCUMBERED_FLAG,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE_VAR,
NVL(GEOGRAPHY_FK,'NA_EDW'),
NVL(GL_ACCT10_FK,'NA_EDW'),
NVL(GL_ACCT1_FK,'NA_EDW'),
NVL(GL_ACCT2_FK,'NA_EDW'),
NVL(GL_ACCT3_FK,'NA_EDW'),
NVL(GL_ACCT4_FK,'NA_EDW'),
NVL(GL_ACCT5_FK,'NA_EDW'),
NVL(GL_ACCT6_FK,'NA_EDW'),
NVL(GL_ACCT7_FK,'NA_EDW'),
NVL(GL_ACCT8_FK,'NA_EDW'),
NVL(GL_ACCT9_FK,'NA_EDW'),
INCOME_TAX_REGION,
INSTANCE,
NVL(INSTANCE_FK,'NA_EDW'),
NVL(INV_CURRENCY_FK,'NA_EDW'),
INV_DATE,
NVL(INV_FK,'NA_EDW'),
INV_LINE_AMT_B,
round((INV_LINE_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
INV_LINE_AMT_T,
INV_LINE_COUNT,
INV_LINE_DESCRIPTION,
INV_LINE_PK,
NVL(INV_LINE_TYPE_FK,'NA_EDW'),
INV_NUM,
INV_PRICE_VAR_AMT_B,
round((INV_PRICE_VAR_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
INV_PRICE_VAR_AMT_T,
NVL(INV_SOURCE_FK,'NA_EDW'),
INV_TYPE,
INV_UNIT_PRICE_B,
round((INV_UNIT_PRICE_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
INV_UNIT_PRICE_T,
ITEM_DESCRIPTION,
NVL(ITEM_FK,'NA_EDW'),
ITEM_ID,
LAST_UPDATE_DATE,
MATCH_LINE_AMT_B,
round((MATCH_LINE_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
MATCH_LINE_AMT_T,
MATCH_LINE_COUNT,
MATCH_STATUS_FLAG,
NVL(ORG_FK,'NA_EDW'),
NVL(PAYMENT_TERM_FK,'NA_EDW'),
POSTED_AMT_B,
round((POSTED_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
POSTED_AMT_T,
POSTED_FLAG,
PO_AMT_B,
round((PO_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
PO_AMT_T,
PO_DISTRIBUTION_ID,
PO_NUMBER,
PO_UNIT_PRICE_B,
round((PO_UNIT_PRICE_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
PO_UNIT_PRICE_T,
PROJECT_ID,
QTY_VAR_AMT_B,
round((QTY_VAR_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
QTY_VAR_AMT_T,
QUANTITY_INVOICED_G,
QUANTITY_INVOICED_T,
NVL(SIC_CODE_FK,'NA_EDW'),
NVL(SOB_FK,'NA_EDW'),
NVL(SUPPLIER_FK,'NA_EDW'),
SUPPLIER_SITE_ID,
TOTAL_VAR_AMT_B,
round((TOTAL_VAR_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
TOTAL_VAR_AMT_T,
TYPE_1099,
UNMATCH_LINE_AMT_B,
round((UNMATCH_LINE_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
UNMATCH_LINE_AMT_T,
UNMATCH_LINE_COUNT,
NVL(UNSPSC_FK,'NA_EDW'),
NVL(UOM_G_FK,'NA_EDW'),
NVL(UOM_T_FK,'NA_EDW'),
USER_ATTRIBUTE1,
USER_ATTRIBUTE10,
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,
invoice_distribution_id,
--USER_MEASURE2,
set_of_books_id,
-- USER_MEASURE3,
old_dist_line_number,
USER_MEASURE4,
USER_MEASURE5,
VAT_CODE,
NULL, -- OPERATION_CODE
decode(GLOBAL_CURRENCY_RATE,
NULL, 'RATE NOT AVAILABLE',
-1, 'RATE NOT AVAILABLE',
-2, 'RATE NOT AVAILABLE',
'LOCAL READY'),
0, --added the following three columns for New Information Enhancement, Nov/12/2002
0,
GLOBAL_CURRENCY_RATE,
PROJECT_FK, --Added the following four columns for New Information Enhancement, nov/26/2002
EXPENDITURE_TYPE,
VOUCHER_NUMBER,
DOC_SEQUENCE_VALUE,
TASK_ID, -- Added for bug#2926033
RCV_TRANSACTION_ID -- Added for bug#3116554
from FII_AP_INV_LINES_FCV;
PROCEDURE UPDATE_DISCOUNT_AMT IS
l_mau NUMBER;
select apk.PRIMARY_KEY_CHAR1 pk,
apk.PRIMARY_KEY4 amt_t,
apk.PRIMARY_KEY5 amt_b
from fii_ap_tmp_line_pk apk
where apk.SEQ_ID = -878;
insert into fii_ap_tmp_line_pk (
SEQ_ID,
PRIMARY_KEY1, --invoice_id
PRIMARY_KEY2, --old_dist_line_number
/* PRIMARY_KEY_CHAR2, ap_ae_lines_all.reference8 */
PRIMARY_KEY3, --invoice_distribution_id,
PRIMARY_KEY4, --set of books id
PRIMARY_KEY_CHAR1) --inv_line_pk
select
-919,
to_number(substr(fstg.inv_line_pk, 1, instr(fstg.inv_line_pk, '-') - 1)),
/* substr(fstg.inv_line_pk, instr(fstg.inv_line_pk, '-', 1, 1) + 1,
instr(fstg.inv_line_pk, '-', 1, 2) -
instr(fstg.inv_line_pk, '-', 1, 1) - 1), */
fstg.user_measure3, --old_dist_line_number
fstg.user_measure1, --invoice_distribution_id
fstg.user_measure2,
fstg.inv_line_pk
from fii_ap_inv_lines_fstg fstg
where fstg.collection_status = 'LOCAL READY';
edw_log.put_line ('Process Time for Insert into TMP: '||edw_log.duration(l_duration));
insert into fii_ap_tmp_line_pk (
SEQ_ID,
PRIMARY_KEY_CHAR1, --inv_line_pk
PRIMARY_KEY4, --discount_amt_t
PRIMARY_KEY5) --discount_amt_b
SELECT -878,
apk.PRIMARY_KEY_CHAR1,
sum (nvl (aphd.amount,0)),
sum(nvl(aphd.paid_base_amount,0))
FROM fii_ap_tmp_line_pk apk,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
ap_payment_history_all aph
WHERE apk.PRIMARY_KEY1 = aip.invoice_id
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
AND aphd.invoice_distribution_id = apk.PRIMARY_KEY3
AND nvl(aph.historical_flag, 'N') = 'N'
AND APH.check_id = aip.check_id
AND aph.payment_history_id=aphd.payment_history_id
AND aphd.bank_curr_amount is null
AND aphd.cleared_base_amount is null
group by apk.primary_key_char1
UNION
SELECT -878,
apk.PRIMARY_KEY_CHAR1,
NVL(sum(xal.entered_cr), 0) - NVL(sum(xal.entered_dr), 0),
NVL(sum(NVL(xal.accounted_cr, xal.entered_cr)), 0) -
NVL(sum(NVL(xal.accounted_dr, xal.entered_dr)), 0)
FROM fii_ap_tmp_line_pk apk,
ap_invoice_payments_all aip,
-- ap_payment_history_all aph,
xla_ae_lines xal,
xla_ae_headers xah
WHERE apk.PRIMARY_KEY1 = aip.invoice_id
AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
AND apk.PRIMARY_KEY2 = xal.Upg_Tax_Reference_ID2
AND xal.accounting_class_code = 'DISCOUNT'
-- AND APH.check_id = aip.check_id
-- AND nvl(aph.historical_flag, 'N') = 'Y'
AND xal.application_id=200
AND xah.ae_header_id=xal.ae_header_id
AND xah.ledger_id = apk.primary_key4
group by apk.primary_key_char1;
edw_log.put_line ('Process Time for Insert into TMP w/ Discount: ' ||
edw_log.duration(l_duration));
UPDATE /*+ ORDERED USE_NL (FSTG) */
fii_ap_inv_lines_fstg fstg
SET discount_amt_t = v_tmp.amt_t,
discount_amt_b = v_tmp.amt_b,
discount_amt_g = ROUND(v_tmp.amt_b * discount_amt_g /l_mau)*l_mau
WHERE fstg.inv_line_pk = v_tmp.pk;
update FII_AP_INV_LINES_FSTG
set DISCOUNT_AMT_G = 0
where DISCOUNT_AMT_B = 0;
edw_log.put_line ('Process Time for Update: ' || edw_log.duration(l_duration));
edw_log.put_line ('# of Updated Records: ' || l_count);
Insert Into FII_AP_INV_LINES_FSTG@EDW_APPS_TO_WH(
APPROVAL_STATUS,
ACCOUNTING_DATE,
ACCOUNTING_DATE_FK,
ACCRUAL_POSTED_FLAG,
AMT_INCLUDES_TAX_FLAG,
ASSETS_TRACKING_FLAG,
AWT_FLAG,
AWT_GROUP_ID,
BASE_CURRENCY_CODE,
BATCH_ID,
CASH_JE_BATCH_ID,
CASH_POSTED_FLAG,
CATEGORY_ID,
CCID,
CREATION_DATE,
DUNS_FK,
EMPLOYEE_FK,
ENCUMBERED_FLAG,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE_VAR,
GEOGRAPHY_FK,
GL_ACCT10_FK,
GL_ACCT1_FK,
GL_ACCT2_FK,
GL_ACCT3_FK,
GL_ACCT4_FK,
GL_ACCT5_FK,
GL_ACCT6_FK,
GL_ACCT7_FK,
GL_ACCT8_FK,
GL_ACCT9_FK,
INCOME_TAX_REGION,
INSTANCE,
INSTANCE_FK,
INV_CURRENCY_FK,
INV_DATE,
INV_FK,
INV_LINE_AMT_B,
INV_LINE_AMT_G,
INV_LINE_AMT_T,
INV_LINE_COUNT,
INV_LINE_DESCRIPTION,
INV_LINE_PK,
INV_LINE_TYPE_FK,
INV_NUM,
INV_PRICE_VAR_AMT_B,
INV_PRICE_VAR_AMT_G,
INV_PRICE_VAR_AMT_T,
INV_SOURCE_FK,
INV_TYPE,
INV_UNIT_PRICE_B,
INV_UNIT_PRICE_G,
INV_UNIT_PRICE_T,
ITEM_DESCRIPTION,
ITEM_FK,
ITEM_ID,
LAST_UPDATE_DATE,
MATCH_LINE_AMT_B,
MATCH_LINE_AMT_G,
MATCH_LINE_AMT_T,
MATCH_LINE_COUNT,
MATCH_STATUS_FLAG,
ORG_FK,
PAYMENT_TERM_FK,
POSTED_AMT_B,
POSTED_AMT_G,
POSTED_AMT_T,
POSTED_FLAG,
PO_AMT_B,
PO_AMT_G,
PO_AMT_T,
PO_DISTRIBUTION_ID,
PO_NUMBER,
PO_UNIT_PRICE_B,
PO_UNIT_PRICE_G,
PO_UNIT_PRICE_T,
PROJECT_ID,
QTY_VAR_AMT_B,
QTY_VAR_AMT_G,
QTY_VAR_AMT_T,
QUANTITY_INVOICED_G,
QUANTITY_INVOICED_T,
SIC_CODE_FK,
SOB_FK,
SUPPLIER_FK,
SUPPLIER_SITE_ID,
TOTAL_VAR_AMT_B,
TOTAL_VAR_AMT_G,
TOTAL_VAR_AMT_T,
TYPE_1099,
UNMATCH_LINE_AMT_B,
UNMATCH_LINE_AMT_G,
UNMATCH_LINE_AMT_T,
UNMATCH_LINE_COUNT,
UNSPSC_FK,
UOM_G_FK,
UOM_T_FK,
USER_ATTRIBUTE1,
USER_ATTRIBUTE10,
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,
VAT_CODE,
OPERATION_CODE,
COLLECTION_STATUS,
DISCOUNT_AMT_T, --Added the following three columns for New Information Enhancement, nov/12/2002
DISCOUNT_AMT_B,
DISCOUNT_AMT_G,
PROJECT_FK, --Added the following four columns for New Information Enhancement, nov/26/2002
EXPENDITURE_TYPE,
VOUCHER_NUMBER,
DOC_SEQUENCE_VALUE,
TASK_ID, -- Added for bug#2926033
RCV_TRANSACTION_ID) -- Added for bug#3116554
select
substrb(APPROVAL_STATUS,1,25),
ACCOUNTING_DATE,
NVL(ACCOUNTING_DATE_FK,'NA_EDW'),
substrb(ACCRUAL_POSTED_FLAG, 1, 1),
substrb(AMT_INCLUDES_TAX_FLAG, 1, 1),
substrb(ASSETS_TRACKING_FLAG, 1, 1),
substrb(AWT_FLAG, 1, 1),
AWT_GROUP_ID,
substrb(BASE_CURRENCY_CODE,1,15),
BATCH_ID,
CASH_JE_BATCH_ID,
substrb(CASH_POSTED_FLAG,1,1),
CATEGORY_ID,
CCID,
CREATION_DATE,
NVL(DUNS_FK,'NA_EDW'),
NVL(EMPLOYEE_FK,'NA_EDW'),
substrb(ENCUMBERED_FLAG,1,1),
EXCHANGE_DATE,
EXCHANGE_RATE,
substrb(EXCHANGE_RATE_TYPE,1,30),
EXCHANGE_RATE_VAR,
NVL(GEOGRAPHY_FK,'NA_EDW'),
NVL(GL_ACCT10_FK,'NA_EDW'),
NVL(GL_ACCT1_FK,'NA_EDW'),
NVL(GL_ACCT2_FK,'NA_EDW'),
NVL(GL_ACCT3_FK,'NA_EDW'),
NVL(GL_ACCT4_FK,'NA_EDW'),
NVL(GL_ACCT5_FK,'NA_EDW'),
NVL(GL_ACCT6_FK,'NA_EDW'),
NVL(GL_ACCT7_FK,'NA_EDW'),
NVL(GL_ACCT8_FK,'NA_EDW'),
NVL(GL_ACCT9_FK,'NA_EDW'),
substrb(INCOME_TAX_REGION,1,10),
substrb(INSTANCE,1,40),
NVL(INSTANCE_FK,'NA_EDW'),
NVL(INV_CURRENCY_FK,'NA_EDW'),
INV_DATE,
NVL(INV_FK,'NA_EDW'),
INV_LINE_AMT_B,
INV_LINE_AMT_G,
INV_LINE_AMT_T,
INV_LINE_COUNT,
substrb(INV_LINE_DESCRIPTION,1,240),
substrb(INV_LINE_PK,1,120),
NVL(INV_LINE_TYPE_FK,'NA_EDW'),
substrb(INV_NUM, 1, 50),
INV_PRICE_VAR_AMT_B,
INV_PRICE_VAR_AMT_G,
INV_PRICE_VAR_AMT_T,
NVL(INV_SOURCE_FK,'NA_EDW'),
substrb(INV_TYPE,1,25),
INV_UNIT_PRICE_B,
INV_UNIT_PRICE_G,
INV_UNIT_PRICE_T,
substrb(ITEM_DESCRIPTION,1,240),
NVL(ITEM_FK,'NA_EDW'),
ITEM_ID,
LAST_UPDATE_DATE,
MATCH_LINE_AMT_B,
MATCH_LINE_AMT_G,
MATCH_LINE_AMT_T,
MATCH_LINE_COUNT,
substrb(MATCH_STATUS_FLAG,1,1),
NVL(ORG_FK,'NA_EDW'),
NVL(PAYMENT_TERM_FK,'NA_EDW'),
POSTED_AMT_B,
POSTED_AMT_G,
POSTED_AMT_T,
substrb(POSTED_FLAG,1,1),
PO_AMT_B,
PO_AMT_G,
PO_AMT_T,
PO_DISTRIBUTION_ID,
substrb(PO_NUMBER,1,20),
PO_UNIT_PRICE_B,
PO_UNIT_PRICE_G,
PO_UNIT_PRICE_T,
PROJECT_ID,
QTY_VAR_AMT_B,
QTY_VAR_AMT_G,
QTY_VAR_AMT_T,
QUANTITY_INVOICED_G,
QUANTITY_INVOICED_T,
NVL(SIC_CODE_FK,'NA_EDW'),
NVL(SOB_FK,'NA_EDW'),
NVL(SUPPLIER_FK,'NA_EDW'),
SUPPLIER_SITE_ID,
TOTAL_VAR_AMT_B,
TOTAL_VAR_AMT_G,
TOTAL_VAR_AMT_T,
substrb(TYPE_1099,1,10),
UNMATCH_LINE_AMT_B,
UNMATCH_LINE_AMT_G,
UNMATCH_LINE_AMT_T,
UNMATCH_LINE_COUNT,
NVL(UNSPSC_FK,'NA_EDW'),
NVL(UOM_G_FK,'NA_EDW'),
NVL(UOM_T_FK,'NA_EDW'),
USER_ATTRIBUTE1,
USER_ATTRIBUTE10,
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,
substrb(VAT_CODE,1,15),
NULL, -- OPERATION_CODE
'READY',
NVL(DISCOUNT_AMT_T, 0), --added the following three columns for New Information Enhancement, nov/12/2002
NVL(DISCOUNT_AMT_B, 0),
NVL(DISCOUNT_AMT_G, 0),
PROJECT_FK, --Added the following four columns for New Information Enhancement, nov/26/2002
substrb(EXPENDITURE_TYPE,1,30),
substrb(VOUCHER_NUMBER,1,50),
DOC_SEQUENCE_VALUE,
TASK_ID, -- Addded for bug#2926033
RCV_TRANSACTION_ID -- Added for bug#3116554
from FII_AP_INV_LINES_FSTG
WHERE collection_status = 'LOCAL READY';
l_stmt := ' INSERT INTO fii_ap_tmp_line_pk(
Primary_Key1,
Primary_Key2,
Primary_Key4,
Primary_Key5)
SELECT
aid.invoice_id,
aid.distribution_line_number,
aid.invoice_line_number,
:g_acct_or_inv_date
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai,
ap_invoice_lines_all ail
WHERE aid.invoice_id = ai.invoice_id
AND aid.invoice_line_number=ail.line_number
AND ail.invoice_id=ai.invoice_id
AND aid.posted_flag=''Y''
-- for bug 2601797: AND ai.cancelled_date IS NULL
AND (aid.last_update_date between :g_push_date_range1 and :g_push_date_range2
OR ai.last_update_date between :g_push_date_range1 and :g_push_date_range2 )'||l_er_stmt||'
UNION
SELECT
aid.invoice_id,
aid.distribution_line_number,
aid.invoice_line_number,
:g_acct_or_inv_date
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai,
ap_invoice_lines_all ail,
po_distributions_all pd,
po_lines_all pl,
po_headers_all ph,
po_line_locations_all pll
WHERE ( pl.last_update_date between :g_push_date_range1 and :g_push_date_range2
or ph.last_update_date between :g_push_date_range1 and :g_push_date_range2
or pll.last_update_date between :g_push_date_range1 and :g_push_date_range2)
AND pl.po_line_id = pd.po_line_id
AND pd.po_distribution_id = aid.po_distribution_id
AND aid.invoice_id = ai.invoice_id
AND aid.invoice_line_number=ail.line_number
AND ail.invoice_id=ai.invoice_id
AND aid.posted_flag=''Y''
AND ph.po_header_id = pl.po_header_id
AND pll.line_location_id = pd.line_location_id '||l_er_stmt||'
UNION
SELECT aid.invoice_id,
aid.distribution_line_number,
aid.invoice_line_number,
:g_acct_or_inv_date
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
ap_payment_history_all aph
WHERE aid.invoice_id = ai.invoice_id
AND aid.invoice_id = aip.invoice_id
AND aid.posted_flag = ''Y''
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND aphd.PAY_DIST_LOOKUP_CODE = ''DISCOUNT''
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND nvl(aph.historical_flag, ''N'') = ''N''
AND APH.check_id = aip.check_id
AND aph.payment_history_id=aphd.payment_history_id
AND aphd.bank_curr_amount is null
AND aphd.cleared_base_amount is null
AND aphd.last_update_date between :g_push_date_range1 and :g_push_date_range2 '||l_er_stmt||'
UNION
SELECT aid.invoice_id,
aid.distribution_line_number,
aid.invoice_line_number,
:g_acct_or_inv_date
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai,
ap_invoice_payments_all aip,
-- ap_payment_history_all aph,
xla_ae_lines xal,
xla_ae_headers xah
WHERE aid.invoice_id = ai.invoice_id
AND aid.invoice_id = aip.invoice_id
AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
AND xal.accounting_class_code = ''DISCOUNT''
-- AND APH.check_id = aip.check_id
--AND nvl(aph.historical_flag, ''N'') = ''Y''
AND xal.last_update_date between :g_push_date_range1 and :g_push_date_range2
AND xal.application_id=200
AND xah.ae_header_id=xal.ae_header_id
AND xah.ledger_id=aid.set_of_books_id '||l_er_stmt;
PROCEDURE UPDATE_DIST_CCID IS
cursor ccid_cursor is
select distinct primary_key1 ccid, primary_key_char1 inv_line_pk
from fii_ap_tmp_line_pk;
insert into fii_ap_tmp_line_pk(Primary_key1,
Primary_key_Char1)
with accounting_class AS (SELECT distinct xaca.accounting_class_Code
FROM xla_assignment_defns_B xad,
xla_acct_class_assgns xaca
WHERE XAD.Program_Code = 'PAYABLES EDW EXPENSES'
AND XAD.Enabled_Flag = 'Y'
AND XAD.Program_Code = XACA.Program_Code
AND XAD.Assignment_Code = XACA.Assignment_Code)
select /*+ parallel(fstg) parallel(xah) parallel(xte) parallel(xal) parallel(xdl) */ xal.code_combination_id,
inv_line_pk
from xla_ae_headers xah,
xla_transaction_entities xte,
xla_distribution_links xdl,
xla_ae_lines xal,
fii_ap_inv_lines_fstg fstg,
accounting_class ac
where xte.entity_code='AP_INVOICES'
and xah.entity_id=xte.entity_id
and xah.ae_header_id=xal.ae_header_id
and xal.ae_header_id=xdl.ae_header_id
and xal.ae_line_num=xdl.ae_line_num
and xdl.source_distribution_id_num_1=fstg.user_measure1
and xdl.Source_Distribution_Type IN ('AP_INV_DIST', 'AP_PMT_DIST', 'AP_PREPAY')
and xal.application_id=200
and xah.application_id=200
and xte.application_id=200
and xdl.application_id=200
and xah.accounting_entry_status_code='F'
and xal.accounting_class_code = ac.accounting_class_code
and xah.ledger_id=xte.ledger_id
and fstg.user_measure2=xah.ledger_id
and xah.balance_type_code='A';
update fii_ap_inv_lines_fstg fstg
set fstg.CCID = l_ccid.ccid
where fstg.inv_line_pk=l_ccid.inv_line_pk;
cursor c1 is select DISTINCT BASE_CURRENCY_CODE from_currency,
Decode(g_acct_or_inv_date,
1, ACCOUNTING_DATE,
INV_DATE) CONVERSION_DATE,
COLLECTION_STATUS
From FII_AP_INV_LINES_FSTG
where (COLLECTION_STATUS='RATE NOT AVAILABLE'
OR COLLECTION_STATUS = 'INVALID CURRENCY')
AND trunc(Decode(g_acct_or_inv_date,
1, ACCOUNTING_DATE,
INV_DATE)) <= trunc(sysdate);
cursor c2 is select DISTINCT BASE_CURRENCY_CODE FROM_CURRENCY,
Decode(g_acct_or_inv_date,
1, ACCOUNTING_DATE,
INV_DATE) CONVERSION_DATE,
COLLECTION_STATUS
From FII_AP_INV_LINES_FSTG
where (COLLECTION_STATUS='RATE NOT AVAILABLE'
OR COLLECTION_STATUS = 'INVALID CURRENCY')
AND trunc(Decode(g_acct_or_inv_date,
1, ACCOUNTING_DATE,
INV_DATE)) > trunc(sysdate);
execute immediate 'select count(*) from FII_AP_LINE_MSNG_RATES' into l_count;
insert into fii_ap_line_msng_rates(Primary_Key1,
Primary_key2,
Primary_key3,
Primary_key4) /* Inv line Uptake */
select Primary_key1,
Primary_key2,
Primary_Key5,
Primary_Key4
from fii_ap_tmp_line_pk;
Insert into fii_ap_tmp_line_pk(Primary_Key1,
Primary_Key2,
Primary_Key5,
Primary_Key4) /* Inv Line Uptake */
select Primary_Key1,
Primary_Key2,
Primary_Key3,
Primary_Key4
from fii_ap_line_msng_rates;
DELETE_STG;
edw_log.put_line('Inserted '||nvl(g_row_count,0)||
' rows into the local staging table');
UPDATE_DIST_CCID; -- added for SLA-AP Uptake
UPDATE_DISCOUNT_AMT;
INSERT_MISSING_RATES_IN_TMP;
select /*+ FULL(SP) CACHE(SP) */
warehouse_currency_code into l_to_currency
from edw_local_system_parameters SP;
DELETE_STG;
delete from fii_ap_line_msng_rates;
insert into fii_ap_line_msng_rates(Primary_Key1,
Primary_Key2,
Primary_Key3,
Primary_Key4) /* Inv Lines Uptake */
select Primary_Key1,
Primary_Key2,
Primary_Key5,
Primary_Key4
from fii_ap_tmp_line_pk;
edw_log.put_line('Inserted '||nvl(g_row_count,0)||
' rows into the staging table');
edw_log.put_line('Inserted '||nvl(g_row_count,0)||
' rows into the staging table');
edw_log.put_line('Inserting into local staging have failed');