[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT decode(PTYPE.Project_Type_Class_Code,'CAPITAL','P','X')
INTO G_Assets_Addition_flag
FROM pa_project_types_all PTYPE,
pa_projects_all PROJ
WHERE PTYPE.Project_Type = PROJ.Project_Type
AND (PTYPE.org_id = PROJ.org_id OR
PROJ.org_id is null)
AND PROJ.Project_Id = G_PROJECT_ID;
SELECT ap.set_of_books_id,
pa.set_of_books_id
INTO G_AP_SOB,
G_PA_SOB
FROM ap_system_parameters ap,
pa_implementations pa;
SELECT po.set_of_books_id
INTO G_PO_SOB
FROM financials_system_parameters po;
variables in 'insert_into_trans_intf' API */
G_INVOICE_TYPE := p_invoice_type;
/* SELECT NVL(org_id, -99) commented for bug#2488576,removed nvl */
SELECT org_id
INTO G_ORG_ID
FROM pa_implementations;
SELECT pa_interface_id_s.nextval
into v_interface_id
FROM dual;
SELECT 'Y'
FROM fnd_concurrent_requests req,
fnd_concurrent_programs prog,
fnd_executables exe
WHERE req.program_application_id = prog.application_id
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.phase_code = 'R'
AND req.request_id <> G_REQUEST_ID
AND prog.executable_application_id = exe.application_id
AND prog.executable_id = exe.executable_id
AND exe.executable_name = 'PAAPIMP';
UPDATE ap_invoice_distributions_all DIST
SET DIST.pa_addition_flag = 'N'
, DIST.request_id = G_REQUEST_ID
WHERE DIST.pa_addition_flag IN ('O','W')
AND DIST.posted_flag||''= 'Y'
AND DIST.project_id >0
AND NOT EXISTS ( SELECT 'X'
FROM pa_expenditure_items_all ei
WHERE ei.document_header_id = dist.invoice_id /*Added for bug 6327185 */
AND ei.document_distribution_id = dist.invoice_distribution_id
AND ei.transaction_source in ('AP INVOICE','AP VARIANCE','AP NRTAX','AP EXPENSE')) ;
write_log(LOG, 'Number of invoice distributions updated = ' || to_char(SQL%ROWCOUNT));
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'N'
, DIST.request_id = G_REQUEST_ID
WHERE DIST.pa_addition_flag = 'O'
AND dist.pay_dist_lookup_code = 'DISCOUNT'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND NOT EXISTS ( SELECT 'X'
FROM pa_expenditure_items_all ei
WHERE ei.document_distribution_id = dist.invoice_distribution_id
AND ei.document_payment_id = dist.invoice_payment_id
AND ei.transaction_source = 'AP DISCOUNTS') ;
write_log(LOG, 'Number of discount distributions updated = ' || to_char(SQL%ROWCOUNT));
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_addition_flag = 'N'
,rcv_sub.request_id = G_REQUEST_ID
WHERE rcv_sub.pa_addition_flag = 'O'
AND NOT EXISTS (SELECT 'X'
FROM pa_expenditure_items_all ei
WHERE ei.document_distribution_id = rcv_sub.rcv_transaction_id);
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_addition_flag = 'I'
WHERE rcv_sub.pa_addition_flag = 'J';
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'N'
, DIST.request_id = G_REQUEST_ID
WHERE DIST.pa_addition_flag = 'O'
AND dist.pay_dist_lookup_code = 'CASH'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND NOT EXISTS ( SELECT 'X'
FROM pa_expenditure_items_all ei
WHERE ei.document_distribution_id = dist.invoice_distribution_id
AND ei.document_payment_id = dist.invoice_payment_id
AND ei.transaction_source = 'AP INVOICE') ;
write_log(LOG, 'Number of payment distributions updated = ' || to_char(SQL%ROWCOUNT));
UPDATE ap_prepay_app_dists dist
SET dist.pa_addition_flag = 'N',
request_id = G_REQUEST_ID
WHERE dist.pa_addition_flag = 'O'
AND NOT EXISTS ( SELECT 'X'
FROM pa_expenditure_items_all ei
WHERE ei.document_distribution_id = dist.invoice_distribution_id
AND ei.document_payment_id = dist.prepay_app_dist_id
AND ei.transaction_source in ('AP INVOICE','AP VARIANCE','AP NRTAX','AP EXPENSE')) ;
write_log(LOG, 'Number of prepayment appl distributions updated = ' || to_char(SQL%ROWCOUNT));
SELECT ledger_id, currency_code
FROM gl_alc_ledger_rships_v
WHERE source_ledger_id = p_set_of_books_id
AND application_id = 275
AND org_id = p_org_id
AND relationship_enabled_flag = 'Y';
SELECT NVL(org_id,-99)
INTO l_org_id
FROM pa_implementations;
savepoint import; /*savepoint added for bug 2138340. The data inserted into
UPDATE pa_transaction_interface
SET transaction_status_code = 'R',
transaction_rejection_code = 'TRX_IMPORT_ABORTED'
WHERE interface_id = p_interface_id
AND transaction_status_code = 'P'
AND transaction_source = p_transaction_source
AND batch_name = p_batch_name;
l_num_dists_updated NUMBER;
/* Modified the hint on following update statement for bug 6920705 */
UPDATE /*+ index(dist AP_INVOICE_DISTRIBUTIONS_N14) */ ap_invoice_distributions dist
SET dist.pa_addition_flag = 'Z',
request_id = G_REQUEST_ID,
last_update_date = SYSDATE, --bug 3905111
last_updated_by = G_USER_ID,
last_update_login = G_USER_ID,
program_id = G_PROG_ID,
program_application_id = G_PROG_APPL_ID,
program_update_date = SYSDATE, --bug 3905111
dist.assets_addition_flag = decode(G_Assets_Addition_flag,'P','P',
dist.assets_addition_flag)
WHERE dist.pa_addition_flag IN ('N', 'S', 'A', 'B', 'C', 'D', 'I', 'J', 'K', 'M', 'P', 'V', 'X', 'W')
AND dist.posted_flag||'' = 'Y'
AND dist.project_id = G_PROJECT_ID
AND 'N' = (select cost_rate_flag
from pa_expenditure_types
where expenditure_type = dist.expenditure_type)
AND EXISTS (SELECT invoice_id
FROM AP_INVOICES inv
WHERE inv.invoice_id = DIST.invoice_id
AND inv.invoice_type_lookup_code = 'EXPENSE REPORT')
AND ( nvl(dist.encumbered_flag,'N') = 'R' )
AND NOT EXISTS (SELECT NULL
FROM ap_invoice_distributions dist1
WHERE dist.parent_reversal_id is not null
AND dist.parent_reversal_id = dist1.invoice_distribution_id
AND dist1.pa_addition_flag = 'T'
AND dist1.encumbered_flag = 'R')
--Update historical data for Cash Based Acctng
AND (G_ACCTNG_METHOD = 'A' OR (G_ACCTNG_METHOD = 'C' AND dist.historical_flag = 'Y'));
l_num_dists_updated := SQL%ROWCOUNT ;
write_log(LOG, 'Updated '||to_char(l_num_dists_updated)|| 'invoice distributions to Z for Encumbrance');
UPDATE /*+ index(dist AP_INVOICE_DISTRIBUTIONS_N14)*/ ap_invoice_distributions dist /*Added for bug 6327185*/
SET dist.pa_addition_flag = 'Z',
request_id = G_REQUEST_ID,
last_update_date = SYSDATE, --bug 3905111
last_updated_by = G_USER_ID,
last_update_login = G_USER_ID,
program_id = G_PROG_ID,
program_application_id = G_PROG_APPL_ID,
program_update_date = SYSDATE, --bug 3905111
dist.assets_addition_flag = (SELECT decode(ptype.project_type_class_code,
'CAPITAL','P', dist.assets_addition_flag)
FROM pa_project_types_all ptype, pa_projects_all proj
WHERE ptype.project_type = proj.project_type
AND (ptype.org_id = proj.org_id
OR proj.org_id is null)
AND proj.project_id = dist.project_id)
WHERE dist.pa_addition_flag IN ('N', 'S', 'A', 'B', 'C', 'D', 'I', 'J', 'K', 'M', 'P', 'V', 'X', 'W')
AND dist.posted_flag||'' = 'Y'
AND dist.project_id > 0
AND 'N' = (select cost_rate_flag
from pa_expenditure_types
where expenditure_type = dist.expenditure_type)
AND EXISTS (SELECT invoice_id
FROM AP_INVOICES inv
WHERE inv.invoice_id = DIST.invoice_id
AND inv.invoice_type_lookup_code = 'EXPENSE REPORT')
AND ( nvl(dist.encumbered_flag,'N') = 'R' )
AND NOT EXISTS (SELECT NULL
FROM ap_invoice_distributions dist1
WHERE dist.parent_reversal_id is not null
AND dist.parent_reversal_id = dist1.invoice_distribution_id
AND dist1.pa_addition_flag = 'T'
AND dist1.encumbered_flag = 'R')
--Update historical data for Cash Based Acctng
AND (G_ACCTNG_METHOD = 'A' OR (G_ACCTNG_METHOD = 'C' AND dist.historical_flag = 'Y'));
l_num_dists_updated := SQL%ROWCOUNT ;
write_log(LOG, 'Updated '||to_char(l_num_dists_updated)|| 'invoice distributions to Z for Encumbrance');
/* Added the following update for bug 3569296 */
-- The program should update the pa_addition_flag for all encumbered lines marked as R to netzero adj flag.
-- R indicates a line to be ignored by encumbrance and validation code because neither the original nor the
-- reversal distributions were looked at and they offset each other so, they can be ignored and marked as Z.
-- (This is set only if the parent one is not validated as well. Otherwise the reversal one will also be encumbered).
-- Since these lines have been not encumbered, there exist no Budgetery control commitment records for these in PA
If g_body_debug_mode = 'Y' Then
G_err_stage := 'Updating invoice distributions to Z for project';
/* Modified the hint on following update statement for bug 6920705 */
UPDATE /*+ index(dist AP_INVOICE_DISTRIBUTIONS_N14) */ ap_invoice_distributions dist
SET dist.pa_addition_flag = 'Z',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE, --bug 3905111
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE, --bug 3905111
dist.assets_addition_flag = decode(G_Assets_Addition_flag,'P','P',
dist.assets_addition_flag)
WHERE dist.pa_addition_flag IN ('N', 'S', 'A', 'B', 'C', 'D','H', 'I', 'J', 'K', 'L', 'M', 'P','V', 'X', 'W')
AND dist.posted_flag||'' = 'Y'
AND dist.project_id = G_PROJECT_ID
AND ( nvl(dist.encumbered_flag,'N') = 'R' )
--Update historical data for Cash Based Acctng
AND (G_ACCTNG_METHOD = 'A' OR (G_ACCTNG_METHOD = 'C' AND dist.historical_flag = 'Y'))
AND NOT EXISTS (SELECT NULL
FROM ap_invoice_distributions dist1
WHERE dist.parent_reversal_id is not null
AND dist.parent_reversal_id = dist1.invoice_distribution_id
AND dist1.pa_addition_flag = 'T'
AND dist1.encumbered_flag = 'R')
AND EXISTS (SELECT invoice_id
FROM AP_INVOICES inv
WHERE inv.invoice_id = DIST.invoice_id
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT');
write_log(LOG, 'Updated '||to_char(SQL%ROWCOUNT)|| 'invoice distributions to Z for Encumbrance');
UPDATE /*+ index(dist AP_INVOICE_DISTRIBUTIONS_N14)*/ ap_invoice_distributions dist /*Added for bug 6327185*/
SET dist.pa_addition_flag = 'Z',
request_id = G_REQUEST_ID,
last_update_date = SYSDATE, --bug 3905111
last_updated_by = G_USER_ID,
last_update_login = G_USER_ID,
program_id = G_PROG_ID,
program_application_id = G_PROG_APPL_ID,
program_update_date = SYSDATE, --bug 3905111
dist.assets_addition_flag = (SELECT decode(ptype.project_type_class_code,
'CAPITAL','P', dist.assets_addition_flag)
FROM pa_project_types_all ptype, pa_projects_all proj
WHERE ptype.project_type = proj.project_type
AND (ptype.org_id = proj.org_id OR
proj.org_id is null)
AND proj.project_id = dist.project_id)
WHERE dist.pa_addition_flag IN ('N', 'S', 'A', 'B', 'C', 'D','H', 'I', 'J', 'K', 'L', 'M', 'P','V', 'X', 'W')
AND dist.posted_flag||'' = 'Y'
AND dist.project_id > 0
AND (nvl(dist.encumbered_flag,'N') = 'R' )
--Update historical data for Cash Based Acctng
AND (G_ACCTNG_METHOD = 'A' OR (G_ACCTNG_METHOD = 'C' AND dist.historical_flag = 'Y'))
AND NOT EXISTS (SELECT NULL
FROM ap_invoice_distributions dist1
WHERE dist.parent_reversal_id is not null
AND dist.parent_reversal_id = dist1.invoice_distribution_id
AND dist1.pa_addition_flag = 'T'
AND dist1.encumbered_flag = 'R')
AND EXISTS (SELECT invoice_id
FROM AP_INVOICES inv
WHERE inv.invoice_id = DIST.invoice_id
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT');
write_log(LOG, 'Updated '||to_char(SQL%ROWCOUNT)|| 'invoice distributions to Z for Encumbrance');
SELECT prepay_amount_remaining
INTO l_prepay_rem_amt
FROM ap_invoice_distributions_All
WHERE invoice_distribution_id = p_prepay_dist_id;
UPDATE AP_Invoice_distributions DIST
SET DIST.Pa_Addition_Flag ='O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by=G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE DIST.Posted_Flag||'' = 'Y'
AND DIST.Pa_Addition_Flag IN
('S', 'A', 'B', 'C', 'D', 'I', 'N', 'J', 'K', 'M', 'P','Q', 'V', 'X', 'W')
AND DIST.project_id > 0
AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
AND ((
exists (SELECT NULL
FROM AP_INVOICES inv,
AP_Invoice_distributions DIST1,
PO_VENDORS vend
WHERE inv.invoice_id = DIST1.invoice_id
AND DIST1.invoice_distribution_id = DIST.invoice_distribution_id
AND INV.payment_status_flag = 'Y' -- Flag indicated FULLY paid inv
AND DIST1.historical_flag = 'Y' -- process historical dist as invoices in Cash Based Acctng
AND inv.vendor_id = vend.vendor_id
AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2, G_INVOICE_SOURCE3)
AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0))
OR
(inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED')
AND inv.source in ('CREDIT CARD','Both Pay')
AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0))))
OR
(EXISTS ( SELECT NULL
FROM PO_VENDORS vend1,
ap_invoices inv1,
ap_invoice_distributions dist2
WHERE inv1.invoice_id = dist2.invoice_id
AND inv1.invoice_id = dist.invoice_id
AND (dist.reversal_flag = 'Y' or dist.cancellation_flag = 'Y' )
AND dist2.invoice_distribution_id = dist.parent_reversal_id --Process Historical data reversals as Invoices in Cash based
AND dist2.pa_addition_flag = 'Y'
AND inv1.vendor_id = vend1.vendor_id
AND ((inv1.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv1.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2, G_INVOICE_SOURCE3)
AND (vend1.employee_id IS NOT NULL or nvl(inv1.paid_on_behalf_employee_id,0) > 0))
OR
(inv1.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED')
AND inv1.source in ('CREDIT CARD','Both Pay')
AND nvl(inv1.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0))))
)
AND DIST.project_id = G_PROJECT_ID
AND trunc(DIST.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,DIST.expenditure_item_date))
AND trunc(DIST.accounting_date) <= trunc(nvl(G_GL_DATE,DIST.accounting_date));
UPDATE ap_invoice_distributions_all dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND DIST.project_id > 0
AND dist.posted_flag = 'Y'
AND dist.project_id = G_PROJECT_ID
--AND dist.line_type_lookup_code = 'PREPAY'
AND dist.line_type_lookup_code <> 'REC_TAX'
AND dist.prepay_distribution_id is not null
AND trunc(dist.Accounting_Date) <= trunc(nvl(G_GL_DATE,dist.Accounting_Date))
AND trunc(dist.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,dist.expenditure_item_date))
AND exists (SELECT NULL
FROM AP_INVOICES inv,
PO_VENDORS vend
WHERE inv.invoice_id = DIST.invoice_id
AND inv.vendor_id = vend.vendor_id
AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2, G_INVOICE_SOURCE3)
AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0))
OR
(inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') /*Bug# 3373933*/ /*Bug 4099522*/
AND inv.source in ('CREDIT CARD','Both Pay')
AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0)))
AND exists(SELECT inv.invoice_id
FROM AP_INVOICES inv,
AP_Invoice_Distributions_all aid
WHERE aid.invoice_id = inv.invoice_id
AND inv.invoice_type_lookup_code = 'PREPAYMENT'
AND aid.historical_flag = 'Y'
AND aid.pa_addition_flag = 'Y'
AND aid.invoice_distribution_id = dist.prepay_distribution_id --Prepayment dist id
);
UPDATE AP_Invoice_distributions DIST
SET DIST.Pa_Addition_Flag ='O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by=G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE DIST.Posted_Flag||'' = 'Y'
AND DIST.Pa_Addition_Flag IN
('S', 'A', 'B', 'C', 'D', 'I', 'N', 'J', 'K', 'M', 'P','Q', 'V', 'X', 'W')
AND DIST.project_id > 0
AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
AND exists (SELECT NULL
FROM AP_INVOICES inv,
PO_VENDORS vend
WHERE inv.invoice_id = DIST.invoice_id
AND inv.vendor_id = vend.vendor_id
AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2, G_INVOICE_SOURCE3)
AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0))
OR
(inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') /*Bug# 3373933*/ /*Bug 4099522*/
AND inv.source in ('CREDIT CARD','Both Pay')
AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0)))
AND DIST.project_id = G_PROJECT_ID
AND trunc(DIST.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,DIST.expenditure_item_date)) /*GSCC*//* added trunc for the bug 6623163 */
AND trunc(DIST.accounting_date) <= trunc(nvl(G_GL_DATE,DIST.accounting_date)); /*GSCC*//* added trunc for the bug 6623163 */
UPDATE AP_Invoice_distributions DIST
SET DIST.Pa_Addition_Flag ='O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by=G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE DIST.Posted_Flag||'' = 'Y'
AND DIST.Pa_Addition_Flag IN
('S', 'A', 'B', 'C', 'D', 'I', 'N', 'J', 'K', 'M', 'P','Q', 'V', 'X', 'W')
AND DIST.project_id > 0
AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
AND (
(exists (SELECT NULL
FROM AP_INVOICES inv,
AP_Invoice_distributions DIST1,
PO_VENDORS vend
WHERE inv.invoice_id = DIST1.invoice_id
AND DIST1.invoice_distribution_id = DIST.invoice_distribution_id
AND INV.payment_status_flag = 'Y' -- Flag indicated FULLY paid inv
AND DIST1.historical_flag = 'Y' --process historical dist as invoices in Cash Based Acctng
AND inv.vendor_id = vend.vendor_id
AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2, G_INVOICE_SOURCE3)
AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0))
OR
(inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') /*Bug# 3373933*/ /*Bug 4099522*/
AND inv.source in ('CREDIT CARD','Both Pay')
AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0))))
OR
(EXISTS ( SELECT NULL
FROM PO_VENDORS vend1,
ap_invoices inv1, ap_invoice_distributions dist2
WHERE inv1.invoice_id = dist2.invoice_id
AND inv1.invoice_id = dist.invoice_id
AND (dist.reversal_flag = 'Y' or dist.cancellation_flag = 'Y' )
AND dist2.invoice_distribution_id = dist.parent_reversal_id --Process Historical data reversals as Invoices in Cash based
AND dist2.pa_addition_flag = 'Y'
AND inv1.vendor_id = vend1.vendor_id
AND ((inv1.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv1.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2, G_INVOICE_SOURCE3)
AND (vend1.employee_id IS NOT NULL or nvl(inv1.paid_on_behalf_employee_id,0) > 0))
OR
(inv1.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') /*Bug# 3373933*/ /*Bug 4099522*/
AND inv1.source in ('CREDIT CARD','Both Pay')
AND nvl(inv1.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0))))
)
AND trunc(DIST.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,DIST.expenditure_item_date)) /*GSCC*/
AND trunc(DIST.accounting_date) <= trunc(nvl(G_GL_DATE,DIST.accounting_date)); /*GSCC*/ /*Bug 7342936. Right parenthesis were missing on left side of expression*/
UPDATE ap_invoice_distributions dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND DIST.project_id > 0
AND dist.posted_flag = 'Y'
--AND dist.line_type_lookup_code = 'PREPAY'
AND dist.line_type_lookup_code <> 'REC_TAX'
AND dist.prepay_distribution_id is not null
AND trunc(dist.Accounting_Date) <= trunc(nvl(G_GL_DATE,dist.Accounting_Date))
AND trunc(dist.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,dist.expenditure_item_date))
AND exists (SELECT NULL
FROM AP_INVOICES_ALL inv,
PO_VENDORS vend
WHERE inv.invoice_id = DIST.invoice_id
AND inv.vendor_id = vend.vendor_id
AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2, G_INVOICE_SOURCE3)
AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0))
OR
(inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') /*Bug# 3373933*/ /*Bug 4099522*/
AND inv.source in ('CREDIT CARD','Both Pay')
AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0)))
AND exists(SELECT inv.invoice_id
FROM AP_INVOICES_ALL inv,
AP_Invoice_Distributions_all aid
WHERE aid.invoice_id = inv.invoice_id
AND inv.invoice_type_lookup_code = 'PREPAYMENT'
AND aid.historical_flag = 'Y'
AND aid.pa_addition_flag = 'Y'
AND aid.invoice_distribution_id = dist.prepay_distribution_id --Prepayment dist id
);
UPDATE /*+ index(DIST AP_INVOICE_DISTRIBUTIONS_N14)*/ AP_Invoice_distributions DIST /*Added for bug 6327185*/
SET DIST.Pa_Addition_Flag ='O', /*Bug#2168903*/
request_id = G_REQUEST_ID,
last_update_date=SYSDATE, --bug 3905111
last_updated_by=G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE --bug 3905111
WHERE DIST.Posted_Flag||'' = 'Y'
AND DIST.Pa_Addition_Flag IN /*Bug#1727504*/
('S', 'A', 'B', 'C', 'D', 'I', 'N', 'J', 'K', 'M', 'P','Q', 'V', 'X', 'W')
AND DIST.project_id > 0
AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
AND exists (SELECT invoice_id
FROM AP_INVOICES_ALL inv,
PO_VENDORS vend
WHERE inv.invoice_id = DIST.invoice_id
AND inv.vendor_id = vend.vendor_id
AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2, G_INVOICE_SOURCE3)
AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0))
OR
(inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') /*Bug# 3373933*//*Bug 4099522*/
AND inv.source in ('CREDIT CARD','Both Pay')
AND nvl(inv.paid_on_behalf_employee_id,0) > 0)))
AND trunc(DIST.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,DIST.expenditure_item_date)) /*GSCC*//* added trunc for the bug 6623163 */
AND trunc(DIST.accounting_date) <= trunc(nvl(G_GL_DATE,DIST.accounting_date)); /*GSCC*/
UPDATE AP_Invoice_Distributions DIST
SET DIST.Pa_Addition_Flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by=G_USER_ID,
last_update_login=G_USER_ID,
program_id=G_PROG_ID,
program_application_id=G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE DIST.Posted_Flag = 'Y'
AND DIST.Pa_Addition_Flag IN
('S', 'A', 'B', 'C', 'D','H', 'I', 'J', 'K', 'L', 'M', 'N', 'P','Q', 'V', 'X')
AND DIST.project_id > 0
AND trunc(DIST.Accounting_Date) <= trunc(nvl(G_GL_DATE,DIST.Accounting_Date)) /*GSCC*//* added trunc for the bug 6623163 */
AND trunc(DIST.Expenditure_Item_Date) <= trunc(NVL(G_TRANSACTION_DATE,DIST.Expenditure_Item_Date)) /*GSCC*/ /* added trunc for the bug 6623163 */
AND DIST.project_id = G_PROJECT_ID
AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
AND (
EXISTS (
SELECT NULL
FROM ap_invoices_all inv,
po_distributions_all PO,
ap_invoice_distributions_all dist2
WHERE inv.invoice_id = dist2.invoice_id
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
AND dist2.invoice_id = DIST.invoice_id
AND dist2.invoice_distribution_id = DIST.invoice_distribution_id
AND inv.payment_status_flag = 'Y' -- Flag indicates that Invoice has been FULLY paid
AND dist2.historical_flag = 'Y' --Process Historical data as Invoices in Cash based
AND dist2.po_distribution_id = PO.po_distribution_id(+)
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND inv.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND ( nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting',
'PA_IC_INVOICES','PA_COST_ADJUSTMENTS')
or dist2.line_type_lookup_code = 'NONREC_TAX'
)
)
OR
EXISTS (
SELECT NULL
FROM ap_invoices_all inv1,
ap_invoice_distributions_all dist3
WHERE inv1.invoice_id = dist3.invoice_id
AND inv1.invoice_id = dist.invoice_id
AND inv1.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND (dist.reversal_flag = 'Y' or dist.cancellation_flag = 'Y' )
AND dist3.invoice_distribution_id = dist.parent_reversal_id --Process Historical data reversals as Invoices in Cash based
AND dist3.pa_addition_flag = 'Y')
)
/* Bug 6353803: Added the following for this bug. */
AND (pa_nl_installed.is_nl_installed = 'N'
OR ( pa_nl_installed.is_nl_installed = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
WHERE pod.po_distribution_id = dist.po_distribution_id
AND pod.po_line_id = pol.po_line_id
AND si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
AND NOT EXISTS (SELECT 'X'
FROM
ap_invoice_distributions apdist,
po_distributions pod,
mtl_system_items si,
po_lines_all pol
where DIST.CHARGE_APPLICABLE_TO_DIST_ID
= apdist.INVOICE_DISTRIBUTION_ID
and apdist.po_distribution_id = pod.po_distribution_id
and pod.po_line_id = pol.po_line_id
and si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
)
);
UPDATE ap_invoice_distributions_all dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND DIST.project_id > 0
AND dist.posted_flag = 'Y'
AND dist.project_id = G_PROJECT_ID
--AND dist.line_type_lookup_code = 'PREPAY'
AND dist.line_type_lookup_code <> 'REC_TAX'
AND dist.prepay_distribution_id is not null
AND trunc(dist.Accounting_Date) <= trunc(nvl(G_GL_DATE,dist.Accounting_Date))
AND trunc(dist.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,dist.expenditure_item_date))
AND EXISTS (
SELECT NULL
FROM ap_invoices inv
WHERE inv.invoice_id = dist.invoice_id
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND inv.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting',
'PA_IC_INVOICES','PA_COST_ADJUSTMENTS') )
AND exists(SELECT inv.invoice_id
FROM AP_INVOICES inv,
AP_Invoice_Distributions_all aid
WHERE aid.invoice_id = inv.invoice_id
AND inv.invoice_type_lookup_code = 'PREPAYMENT'
AND aid.historical_flag = 'Y'
AND aid.pa_addition_flag = 'Y'
AND aid.invoice_distribution_id = dist.prepay_distribution_id --Prepayment dist id
AND aid.project_id = G_PROJECT_ID)
/* Bug 6353803: Added the following for this bug. */
AND (pa_nl_installed.is_nl_installed = 'N'
OR ( pa_nl_installed.is_nl_installed = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
WHERE pod.po_distribution_id = dist.po_distribution_id
AND pod.po_line_id = pol.po_line_id
AND si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
AND NOT EXISTS (SELECT 'X'
FROM
ap_invoice_distributions apdist,
po_distributions pod,
mtl_system_items si,
po_lines_all pol
where DIST.CHARGE_APPLICABLE_TO_DIST_ID
= apdist.INVOICE_DISTRIBUTION_ID
and apdist.po_distribution_id = pod.po_distribution_id
and pod.po_line_id = pol.po_line_id
and si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
)
);
UPDATE AP_Invoice_Distributions DIST
SET DIST.Pa_Addition_Flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by=G_USER_ID,
last_update_login=G_USER_ID,
program_id=G_PROG_ID,
program_application_id=G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE DIST.Posted_Flag = 'Y'
AND DIST.Pa_Addition_Flag IN
('S', 'A', 'B', 'C', 'D','H', 'I', 'J', 'K', 'L', 'M', 'N', 'P','Q', 'V', 'X')
AND DIST.project_id > 0
AND trunc(DIST.Accounting_Date) <= trunc(nvl(G_GL_DATE,DIST.Accounting_Date)) /*GSCC*/
AND trunc(DIST.Expenditure_Item_Date) <=
trunc(NVL(G_TRANSACTION_DATE,DIST.Expenditure_Item_Date)) /*GSCC*/
AND DIST.project_id = G_PROJECT_ID
AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
AND EXISTS (
SELECT NULL
FROM ap_invoices_all inv,
po_distributions_all PO,
ap_invoice_distributions_all dist2
WHERE inv.invoice_id = dist2.invoice_id
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
AND dist2.invoice_id = DIST.invoice_id
/*credit card txn enhancement, make sure this update doesn't pick tehm */
AND inv.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND dist2.invoice_distribution_id = DIST.invoice_distribution_id
AND dist2.po_distribution_id = PO.po_distribution_id(+)
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND (
nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting', 'PA_IC_INVOICES','PA_COST_ADJUSTMENTS')
or
dist2.line_type_lookup_code = 'NONREC_TAX'
)
)
/* Bug 6353803: Added the following for this bug. */
AND (pa_nl_installed.is_nl_installed = 'N'
OR ( pa_nl_installed.is_nl_installed = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
WHERE pod.po_distribution_id = dist.po_distribution_id
AND pod.po_line_id = pol.po_line_id
AND si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
AND NOT EXISTS (SELECT 'X'
FROM
ap_invoice_distributions apdist,
po_distributions pod,
mtl_system_items si,
po_lines_all pol
where DIST.CHARGE_APPLICABLE_TO_DIST_ID
= apdist.INVOICE_DISTRIBUTION_ID
and apdist.po_distribution_id = pod.po_distribution_id
and pod.po_line_id = pol.po_line_id
and si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
)
);
UPDATE AP_Invoice_Distributions DIST
SET DIST.Pa_Addition_Flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by=G_USER_ID,
last_update_login=G_USER_ID,
program_id=G_PROG_ID,
program_application_id=G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE DIST.Posted_Flag = 'Y'
AND DIST.Pa_Addition_Flag IN
('S', 'A', 'B', 'C', 'D','H', 'I', 'J', 'K', 'L', 'M', 'N', 'P','Q', 'V', 'X')
AND DIST.project_id > 0
AND trunc(DIST.Accounting_Date) <= trunc(nvl(G_GL_DATE,DIST.Accounting_Date)) /*GSCC*/ /*Added trunc for the bug 6623163 */
AND trunc(DIST.Expenditure_Item_Date) <=
trunc(NVL(G_TRANSACTION_DATE,DIST.Expenditure_Item_Date)) /*GSCC*/ /*Added trunc for the bug 6623163 */
AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
AND (
EXISTS (
SELECT NULL
FROM ap_invoices_all inv,
po_distributions_all PO,
ap_invoice_distributions_all dist2
WHERE inv.invoice_id = dist2.invoice_id
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
AND dist2.invoice_id = DIST.invoice_id
AND dist2.invoice_distribution_id = DIST.invoice_distribution_id
AND inv.payment_status_flag = 'Y' -- Flag indicates that Invoice has been FULLY paid
AND dist2.historical_flag = 'Y' --Process Historical data as Invoices in Cash based
AND dist2.po_distribution_id = PO.po_distribution_id(+)
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND inv.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND ( nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting',
'PA_IC_INVOICES','PA_COST_ADJUSTMENTS')
or dist2.line_type_lookup_code = 'NONREC_TAX')
)
OR
EXISTS (
SELECT NULL
FROM ap_invoices inv1,
ap_invoice_distributions dist3
WHERE inv1.invoice_id = dist3.invoice_id
AND inv1.invoice_id = dist.invoice_id
AND inv1.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND (dist.reversal_flag = 'Y' or dist.cancellation_flag = 'Y' )
AND dist3.invoice_distribution_id = dist.parent_reversal_id --Process Historical data reversals as Invoices in Cash based
AND dist3.pa_addition_flag = 'Y')
)
/* Bug 6353803: Added the following for this bug. */
AND (pa_nl_installed.is_nl_installed = 'N'
OR ( pa_nl_installed.is_nl_installed = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
WHERE pod.po_distribution_id = dist.po_distribution_id
AND pod.po_line_id = pol.po_line_id
AND si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
AND NOT EXISTS (SELECT 'X'
FROM
ap_invoice_distributions apdist,
po_distributions pod,
mtl_system_items si,
po_lines_all pol
where DIST.CHARGE_APPLICABLE_TO_DIST_ID
= apdist.INVOICE_DISTRIBUTION_ID
and apdist.po_distribution_id = pod.po_distribution_id
and pod.po_line_id = pol.po_line_id
and si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
)
);
UPDATE ap_invoice_distributions_all dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.posted_flag ='Y'
AND dist.project_id > 0
--AND dist.line_type_lookup_code = 'PREPAY'
AND dist.line_type_lookup_code <> 'REC_TAX'
AND dist.prepay_distribution_id is not null
AND trunc(dist.Accounting_Date) <= trunc(nvl(G_GL_DATE,dist.Accounting_Date))
AND trunc(dist.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,dist.expenditure_item_date))
AND EXISTS (
SELECT NULL
FROM ap_invoices inv
WHERE inv.invoice_id = dist.invoice_id
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND inv.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting',
'PA_IC_INVOICES','PA_COST_ADJUSTMENTS') )
AND exists(SELECT inv.invoice_id
FROM AP_INVOICES_all inv,
AP_Invoice_Distributions_all aid
WHERE aid.invoice_id = inv.invoice_id
AND inv.invoice_type_lookup_code = 'PREPAYMENT'
AND aid.historical_flag = 'Y'
AND aid.pa_addition_flag = 'Y'
AND aid.invoice_distribution_id = dist.prepay_distribution_id --Prepayment dist id
AND aid.project_id > 0 )
/* Bug 6353803: Added the following for this bug. */
AND (pa_nl_installed.is_nl_installed = 'N'
OR ( pa_nl_installed.is_nl_installed = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
WHERE pod.po_distribution_id = dist.po_distribution_id
AND pod.po_line_id = pol.po_line_id
AND si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
AND NOT EXISTS (SELECT 'X'
FROM
ap_invoice_distributions apdist,
po_distributions pod,
mtl_system_items si,
po_lines_all pol
where DIST.CHARGE_APPLICABLE_TO_DIST_ID
= apdist.INVOICE_DISTRIBUTION_ID
and apdist.po_distribution_id = pod.po_distribution_id
and pod.po_line_id = pol.po_line_id
and si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
)
);
UPDATE AP_Invoice_Distributions DIST
SET DIST.Pa_Addition_Flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by=G_USER_ID,
last_update_login=G_USER_ID,
program_id=G_PROG_ID,
program_application_id=G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE DIST.Posted_Flag||'' = 'Y'
AND DIST.Pa_Addition_Flag IN
('S', 'A', 'B', 'C', 'D','H', 'I', 'J', 'K', 'L', 'M', 'N', 'P','Q', 'V', 'X')
AND DIST.project_id > 0
AND trunc(DIST.Accounting_Date )<= trunc(nvl(G_GL_DATE,DIST.Accounting_Date) ) /*GSCC*/
AND trunc(DIST.Expenditure_Item_Date) <=
trunc(NVL(G_TRANSACTION_DATE, DIST.Expenditure_Item_Date)) /*GSCC*/
AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
AND EXISTS (
SELECT NULL
FROM ap_invoices_all inv,
po_distributions_all PO,
ap_invoice_distributions_all dist2
WHERE inv.invoice_id = dist2.invoice_id
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
AND dist2.invoice_id = DIST.invoice_id
/* credit card txn enhancement, make sure this update doesn't pick tehm */
AND inv.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND dist2.invoice_distribution_id = DIST.invoice_distribution_id
AND dist2.po_distribution_id = PO.po_distribution_id(+)
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
-- IC Upgrade: Do not get Inter-company invoices
AND ( nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting',
'PA_IC_INVOICES','PA_COST_ADJUSTMENTS')
or
dist2.line_type_lookup_code = 'NONREC_TAX'
)
)
/* Bug 6353803: Added the following for this bug. */
AND (pa_nl_installed.is_nl_installed = 'N'
OR ( pa_nl_installed.is_nl_installed = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
WHERE pod.po_distribution_id = dist.po_distribution_id
AND pod.po_line_id = pol.po_line_id
AND si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
AND NOT EXISTS (SELECT 'X'
FROM
ap_invoice_distributions apdist,
po_distributions pod,
mtl_system_items si,
po_lines_all pol
where DIST.CHARGE_APPLICABLE_TO_DIST_ID
= apdist.INVOICE_DISTRIBUTION_ID
and apdist.po_distribution_id = pod.po_distribution_id
and pod.po_line_id = pol.po_line_id
and si.inventory_item_id = pol.item_id
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = pod.org_id
)
)
);
SELECT count(*)
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND pa_addition_flag = 'O';
l_invoice_id_tbl.delete;
l_created_by_tbl.delete;
l_invoice_dist_id_tbl.delete; --NEW
l_project_id_tbl.delete;
l_task_id_tbl.delete;
l_ln_type_lookup_tbl.delete;
l_exp_type_tbl.delete;
l_ei_date_tbl.delete;
l_amount_tbl.delete;
l_description_tbl.delete;
l_justification_tbl.delete;
l_dist_cc_id_tbl.delete;
l_exp_org_id_tbl.delete;
l_quantity_tbl.delete;
l_acct_pay_cc_id_tbl.delete;
l_gl_date_tbl.delete;
l_attribute_cat_tbl.delete;
l_attribute1_tbl.delete;
l_attribute2_tbl.delete;
l_attribute3_tbl.delete;
l_attribute4_tbl.delete;
l_attribute5_tbl.delete;
l_attribute6_tbl.delete;
l_attribute7_tbl.delete;
l_attribute8_tbl.delete;
l_attribute9_tbl.delete;
l_attribute10_tbl.delete;
l_rec_cur_amt_tbl.delete;
l_rec_cur_code_tbl.delete;
l_rec_conv_rate_tbl.delete;
l_denom_raw_cost_tbl.delete;
l_denom_cur_code_tbl.delete;
l_acct_rate_date_tbl.delete;
l_acct_rate_type_tbl.delete;
l_acct_exch_rate_tbl.delete;
l_job_id_tbl.delete;
l_employee_id_tbl.delete;
l_vendor_id_tbl.delete;
l_inv_type_code_tbl.delete;
l_source_tbl.delete;
l_org_id_tbl.delete;
l_invoice_num_tbl.delete;
l_cdl_sys_ref4_tbl.delete;
l_po_dist_id_tbl.delete;
l_txn_src_tbl.delete;
l_user_txn_src_tbl.delete;
l_batch_name_tbl.delete;
l_interface_id_tbl.delete;
l_exp_end_date_tbl.delete;
l_txn_status_code_tbl.delete;
l_txn_rej_code_tbl.delete;
l_po_dist_id_tbl.delete;
l_bus_grp_id_tbl.delete;
l_paid_emp_id_tbl.delete;
l_sort_var_tbl.delete;
l_reversal_flag_tbl.delete; --NEW
l_cancel_flag_tbl.delete; --NEW
l_parent_rev_id_tbl.delete; --NEW
l_net_zero_flag_tbl.delete; --NEW
l_sc_xfer_code_tbl.delete; --NEW
l_adj_exp_item_id_tbl.delete; --NEW
l_fc_enabled_tbl.delete; --NEW
l_fc_document_type_tbl.delete; --NEW
l_insert_flag_tbl.delete;
l_rev_parent_dist_id_tbl.delete;
l_rev_child_dist_id_tbl.delete;
l_rev_parent_dist_ind_tbl.delete;
l_si_assts_add_flg_tbl.delete;
l_prepay_dist_id_tbl.delete;
l_hist_flag_tbl.delete;
/* the following sub-procedure is declared here to save lines of code since bulk insert
will be done multiple times within the procedure transfer_inv_to_pa */
PROCEDURE bulk_update_trx_intf IS
BEGIN
/* The records with INSERT_FLAG = F indicate that they are fully applied prepayments and the pa-addition-flag
for such records will be updated to G to relieve commitments*/
/* The records with INSERT_FLAG = P indicate that they are partially applied prepayments and the pa-addition-flag
for such records will be updated to N */
write_log(LOG,'Before bulk update of prepayment invoices');
UPDATE ap_invoice_distributions_all dist
SET dist.pa_addition_flag = decode(l_insert_flag_tbl(i),'F','G','P','N')
WHERE dist.invoice_id = l_invoice_id_tbl(i)
AND dist.invoice_distribution_id = l_invoice_dist_id_tbl(i)
AND dist.pa_addition_flag = 'O'
AND l_insert_flag_tbl(i) in ('P','F');
write_log(LOG,'Failed during bulk update for prepayment processing');
END bulk_update_trx_intf;
PROCEDURE bulk_insert_trx_intf IS
l_status2 VARCHAR2(30);
write_log(LOG,'Before bulk insert of supplier invoices');
write_log(LOG, '58:'||l_insert_flag_tbl(i));
INSERT INTO pa_transaction_interface_all(
transaction_source
, user_transaction_source
, system_linkage
, batch_name
, expenditure_ending_date
, expenditure_item_date
, expenditure_type
, quantity
, raw_cost_rate
, expenditure_comment
, transaction_status_code
, transaction_rejection_code
, orig_transaction_reference
, interface_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5 --NEW
, gl_date
, org_id
, unmatched_negative_txn_flag
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_raw_cost
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, last_update_date
, last_updated_by
, creation_date
, created_by
, person_id
, organization_id
, project_id
, task_id
, Vendor_id
, override_to_organization_id
, person_business_group_id
, adjusted_expenditure_item_id --NEW
, fc_document_type -- NEW
, document_type
, document_distribution_type
, sc_xfer_code
, si_assets_addition_flag
, net_zero_adjustment_flag
)
SELECT
l_txn_src_tbl(i)
,l_user_txn_src_tbl(i)
,G_SYSTEM_LINKAGE
,l_batch_name_tbl(i)
,l_exp_end_date_tbl(i)
,l_ei_date_tbl(i)
,l_exp_type_tbl(i)
,l_quantity_tbl(i)
,l_amount_tbl(i)/decode(nvl(l_quantity_tbl(i),0),0,1,l_quantity_tbl(i))
,l_description_tbl(i)
,l_txn_status_code_tbl(i)
,l_txn_rej_code_tbl(i)
,G_REQUEST_ID
,l_interface_id_tbl(i)
,l_dist_cc_id_tbl(i)
,l_acct_pay_cc_id_tbl(i)
,l_vendor_id_tbl(i) /*sysref1*/
,l_invoice_id_tbl(i) /*sysref2*/
,l_cdl_sys_ref3_tbl(i) --NULL /*sysref3*/
,l_cdl_sys_ref4_tbl(i)
,l_invoice_dist_id_tbl(i) /*sysref5*/ --NEW
,l_gl_date_tbl(i)
,G_ORG_ID
,'Y'
,l_rec_cur_amt_tbl(i)
,l_rec_cur_code_tbl(i)
,l_rec_conv_rate_tbl(i)
,l_denom_raw_cost_tbl(i)
,l_denom_cur_code_tbl(i)
,l_acct_rate_date_tbl(i)
,l_acct_rate_type_tbl(i)
,l_acct_exch_rate_tbl(i)
,l_amount_tbl(i)
,1
,l_attribute_cat_tbl(i)
,l_attribute1_tbl(i)
,l_attribute2_tbl(i)
,l_attribute3_tbl(i)
,l_attribute4_tbl(i)
,l_attribute5_tbl(i)
,l_attribute6_tbl(i)
,l_attribute7_tbl(i)
,l_attribute8_tbl(i)
,l_attribute9_tbl(i)
,l_attribute10_tbl(i)
,l_invoice_id_tbl(i) /*orig_exp_txn_reference1*/
,l_invoice_num_tbl(i) /*user_exp_txn_reference*/
/* bug 2835757*/
,DECODE(G_TRANS_DFF_AP,'N',NULL,l_invoice_id_tbl(i)) /*orig_exp_txn_reference2*/
,NULL /*orig_exp_txn_reference3*/
,SYSDATE
,-1
,SYSDATE
,-1
,l_employee_id_tbl(i)
,l_org_id_tbl(i)
,l_project_id_tbl(i)
,l_task_id_tbl(i)
,l_vendor_id_tbl(i)
,l_exp_org_id_tbl(i)
,l_bus_grp_id_tbl(i)
,l_adj_exp_item_id_tbl(i) --NEW for reversals
,l_fc_document_type_tbl(i) --NEW for funds check
,l_inv_type_code_tbl(i)
,l_ln_type_lookup_tbl(i)
,l_sc_xfer_code_tbl(i)
,l_si_assts_add_flg_tbl(i)
,l_net_zero_flag_tbl(i)
FROM dual
WHERE l_insert_flag_tbl(i) not in ('F', 'P');
write_log(LOG, 'Inserting adjustment records..');
INSERT INTO pa_transaction_interface_all(
transaction_source
, user_transaction_source
, system_linkage
, batch_name
, expenditure_ending_date
, expenditure_item_date
, expenditure_type
, quantity
, raw_cost_rate
, expenditure_comment
, transaction_status_code
, transaction_rejection_code
, orig_transaction_reference
, interface_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5 --NEW
, gl_date
, org_id
, unmatched_negative_txn_flag
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_raw_cost
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, last_update_date
, last_updated_by
, creation_date
, created_by
, person_id
, organization_id
, project_id
, task_id
, Vendor_id
, override_to_organization_id
, person_business_group_id
, adjusted_expenditure_item_id --NEW
, fc_document_type -- NEW
, document_type
, document_distribution_type
, adjusted_txn_interface_id
, sc_xfer_code
, si_assets_addition_flag
, net_zero_adjustment_flag
)
SELECT
l_txn_src_tbl(i)
,l_user_txn_src_tbl(i)
,G_SYSTEM_LINKAGE
,l_batch_name_tbl(i)
,l_exp_end_date_tbl(i)
,l_ei_date_tbl(i)
,l_exp_type_tbl(i)
,-l_quantity_tbl(i)
,l_amount_tbl(i)/decode(nvl(l_quantity_tbl(i),0),0,1,l_quantity_tbl(i))
,l_description_tbl(i)
,l_txn_status_code_tbl(i)
,l_txn_rej_code_tbl(i)
,G_REQUEST_ID
,l_interface_id_tbl(i)
,l_dist_cc_id_tbl(i)
,l_acct_pay_cc_id_tbl(i)
,l_vendor_id_tbl(i) /*sysref1*/
,l_invoice_id_tbl(i) /*sysref2*/
,l_cdl_sys_ref3_tbl(i) --NULL /*sysref3*/
,l_cdl_sys_ref4_tbl(i)
,l_invoice_dist_id_tbl(i) /*sysref5*/ --NEW
,l_gl_date_tbl(i)
,G_ORG_ID
,'Y'
,-l_rec_cur_amt_tbl(i)
,l_rec_cur_code_tbl(i)
,l_rec_conv_rate_tbl(i)
,-l_denom_raw_cost_tbl(i)
,l_denom_cur_code_tbl(i)
,l_acct_rate_date_tbl(i)
,l_acct_rate_type_tbl(i)
,l_acct_exch_rate_tbl(i)
,-l_amount_tbl(i)
,1
,l_attribute_cat_tbl(i)
,l_attribute1_tbl(i)
,l_attribute2_tbl(i)
,l_attribute3_tbl(i)
,l_attribute4_tbl(i)
,l_attribute5_tbl(i)
,l_attribute6_tbl(i)
,l_attribute7_tbl(i)
,l_attribute8_tbl(i)
,l_attribute9_tbl(i)
,l_attribute10_tbl(i)
,l_invoice_id_tbl(i) /*orig_exp_txn_reference1*/
,l_invoice_num_tbl(i) /*user_exp_txn_reference*/
/* bug 2835757*/
,DECODE(G_TRANS_DFF_AP,'N',NULL,l_invoice_id_tbl(i)) /*orig_exp_txn_reference2*/
,NULL /*orig_exp_txn_reference3*/
,SYSDATE
,-1
,SYSDATE
,-1
,l_employee_id_tbl(i)
,l_org_id_tbl(i)
,l_project_id_tbl(i)
,l_task_id_tbl(i)
,l_vendor_id_tbl(i)
,l_exp_org_id_tbl(i)
,l_bus_grp_id_tbl(i)
,l_adj_exp_item_id_tbl(i) --NEW for reversals
,l_fc_document_type_tbl(i) --NEW for funds check
,l_inv_type_code_tbl(i)
,l_ln_type_lookup_tbl(i)
,(select xface.txn_interface_id
from pa_transaction_interface xface
where xface.interface_id = l_interface_id_tbl(i)
and xface.cdl_system_reference2 = l_invoice_id_tbl(i)
and xface.cdl_system_reference5 = l_invoice_dist_id_tbl(i)
and NVL(xface.adjusted_expenditure_item_id,0) = 0 ) -- R12 funds management Uptake
,'P' -- sc_xfer_code
,'T' -- l_si_assts_add_flg_tbl(i)
,l_net_zero_flag_tbl(i)
FROM dual
WHERE l_insert_flag_tbl(i)= 'A';
UPDATE pa_transaction_interface_all xface
SET xface.net_zero_adjustment_flag ='Y',
xface.adjusted_txn_interface_id =
(select xface1.txn_interface_id
from pa_transaction_interface xface1
where xface1.interface_id = l_interface_id_tbl(l_rev_parent_dist_ind_tbl(i))
and xface1.cdl_system_reference2 = l_invoice_id_tbl(l_rev_parent_dist_ind_tbl(i))
and xface1.cdl_system_reference5 = l_invoice_dist_id_tbl(l_rev_parent_dist_ind_tbl(i))
)
WHERE xface.interface_id = l_interface_id_tbl(l_rev_parent_dist_ind_tbl(i))
AND xface.cdl_system_reference2 = l_invoice_id_tbl(l_rev_parent_dist_ind_tbl(i))
AND xface.cdl_system_reference5 = l_rev_child_dist_id_tbl(i);
write_log(LOG,'Failed during bulk insert for invoice processing');
END bulk_insert_trx_intf;
/* Update the previous invoice id and vendor id*/
v_prev_invoice_id := l_invoice_id_tbl(i);
/* First update the v_prev_invoice_source */
G_err_stage := 'New source encountered';
SELECT pa_utils.getweekending(MAX(expenditure_item_date))
INTO G_EXPENDITURE_ENDING_DATE
FROM ap_invoice_distributions
WHERE invoice_id = l_invoice_id_tbl(i);
SELECT emp.business_group_id
INTO G_PER_BUS_GRP_ID
FROM per_all_people_f emp
WHERE emp.person_id = l_employee_id_tbl(i)
AND l_ei_date_tbl(i) between trunc(emp.effective_start_date) and trunc(emp.effective_end_date);
select org2.business_group_id
into G_PER_BUS_GRP_ID
from hr_organization_units org1,
hr_organization_units org2
Where org1.organization_id = l_exp_org_id_tbl(i)
and org1.business_group_id = org2.organization_id ;
/*Update counter of how many distributions of the last invoice of the batch has been processed*/
IF l_invoice_id_tbl(i) = l_invoice_id_tbl(v_last_inv_index) THEN
v_num_last_invoice_processed := v_num_last_invoice_processed +1;
/* Update counter for number of tax lines fetched */
v_num_tax_lines_fetched := v_num_tax_lines_fetched +1;
/* Update counter for number of variance lines fetched */
v_num_inv_variance_fetched := v_num_inv_variance_fetched +1;
/* Update counter for number of variance lines fetched */
v_num_inv_erv_fetched := v_num_inv_erv_fetched +1;
/* Update counter for number of frt and misc lines fetched */
v_num_inv_frt_fetched := v_num_inv_frt_fetched +1;
/* The records with INSERT_FLAG = F indicate that they are fully applied prepayments and the pa-addition-flag
for such records will be updated to G to relieve commitments*/
/* The records with INSERT_FLAG = P indicate that they are partially applied prepayments and the pa-addition-flag
for such records will be updated to N */
l_prepay_hist_flag := 'X'; --initialize
SELECT nvl(historical_flag,'N')
INTO l_prepay_hist_flag
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = l_prepay_dist_id_tbl(i);
SELECT nvl(historical_flag,'N')
INTO l_prepay_hist_flag
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = l_parent_rev_id_tbl(i);
l_insert_flag_tbl(i) := 'F';
l_insert_flag_tbl(i) := 'P';
l_insert_flag_tbl(i) := 'F';
l_insert_flag_tbl(i) := 'P';
SELECT nvl(historical_flag,'N')
INTO l_historical_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id_tbl(i)
AND invoice_distribution_id = l_parent_rev_id_tbl(i); --check the index on this table
IF l_insert_flag_tbl(i) in ('A','U') THEN
l_create_adj_recs := 'Y';
SELECT pa_interface_id_s.nextval
INTO G_INTERFACE_ID
FROM dual;
SELECT pa_interface_id_s.nextval
into G_NRT_INTERFACE_ID
FROM dual;
SELECT pa_interface_id_s.nextval
into G_AP_FRT_INTERFACE_ID
FROM dual;
SELECT pa_interface_id_s.nextval
into G_AP_VAR_INTERFACE_ID
FROM dual;
SELECT pa_interface_id_s.nextval
into G_AP_ERV_INTERFACE_ID
FROM dual;
,l_insert_flag_tbl
,l_hist_flag_tbl
,l_prepay_dist_id_tbl
LIMIT v_max_size;
G_err_stage := 'calling bulk_update_trx_intf within transfer_inv_to_pa';
bulk_update_trx_intf;
G_err_stage := 'calling bulk_insert_trx_intf within transfer_inv_to_pa';
bulk_insert_trx_intf;
G_err_stage := 'After calling bulk_insert_trx_intf within transfer_inv_to_pa';
,l_insert_flag_tbl
,l_hist_flag_tbl
,l_prepay_dist_id_tbl
LIMIT v_num_dist_remain;
G_err_stage := 'calling bulk_update_trx_intf within transfer_inv_to_pa';
bulk_update_trx_intf;
G_err_stage := 'Before 2nd call of bulk_insert_trx_intf within transfer_inv_to_pa';
bulk_insert_trx_intf;
SELECT cdl_system_reference1
,cdl_system_reference2
-- ,cdl_system_reference3 --NEW
-- ,cdl_system_reference4
,cdl_system_reference5 --NEW
,transaction_source
,batch_name
,interface_id
,transaction_status_code
,project_id
,l_pa_addflag
,l_assets_addflag
FROM pa_transaction_interface_all txnintf
WHERE txnintf.transaction_source = p_txn_src
AND txnintf.batch_name = p_batch_name
AND txnintf.interface_id = p_interface_id;
l_sys_ref1_tbl.delete;
l_sys_ref2_tbl.delete;
l_sys_ref5_tbl.delete; --NEW
l_txn_src_tbl.delete;
l_batch_name_tbl.delete;
l_interface_id_tbl.delete;
l_txn_status_code_tbl.delete;
l_project_id_tbl.delete;
l_pa_addflag_tbl.delete;
l_assets_addflag_tbl.delete;
update pa_addition_flag of invoice distribution to 'Y'.
If transaction import leaves the record to be 'P' then
update pa_addition_flag of invoice distribution to 'N'.
If transaction import stamps the record to be 'R' then
update pa_addition_flag of invoice distribution to 'N'.*/
write_log(LOG,'Tying invoice_id: '||l_sys_ref2_tbl(i)||
--'dist num: '||l_sys_ref3_tbl(i)|| --NEW
'dist id: '||l_sys_ref5_tbl(i)|| --NEW
'trc src: '||l_txn_src_tbl(i));
G_err_stage:='Selecting assets addition flag within invoice tieback';
SELECT decode(PTYPE.Project_Type_Class_Code,'CAPITAL','P','X')
INTO l_assets_addflag_tbl(i)
FROM pa_project_types_all PTYPE,
pa_projects_all PROJ
WHERE PTYPE.Project_Type = PROJ.Project_Type
AND (PTYPE.org_id = PROJ.org_id OR
PROJ.org_id is null)
AND PROJ.Project_Id = l_project_id_tbl(i);
PROCEDURE bulk_update_txn_intf IS
v_status VARCHAR2(15);
G_err_stage:=('Within bulk update of invoice tieback');
UPDATE ap_invoice_distributions_all dist
SET dist.pa_addition_flag = l_pa_addflag_tbl(i)
,dist.assets_addition_flag = decode(l_assets_addflag_tbl(i),'P','P',dist.assets_addition_flag)
WHERE dist.invoice_id = l_sys_ref2_tbl(i)
AND dist.invoice_distribution_id = l_sys_ref5_tbl(i)
AND dist.pa_addition_flag = 'O';
/* Bug 5440548 fix to update expenditure data with historical flag for historical AP data */
FORALL i IN l_sys_ref1_tbl.FIRST..l_sys_ref1_tbl.LAST
UPDATE pa_expenditure_items_all exp1
SET historical_flag = 'Y'
WHERE document_header_id = l_sys_ref2_tbl(i)
AND document_distribution_id = l_sys_ref5_tbl(i)
AND exists (select 'exist'
from ap_invoice_distributions_all dist
where dist.invoice_id =l_sys_ref2_tbl(i)
and dist.invoice_distribution_id = l_sys_ref5_tbl(i)
and dist.pa_addition_flag = 'Y'
and dist.historical_flag = 'Y');
/* If the accounting method is CASH BASIS then the payment lines associated with historical invoices should be updated to G
since such invoice distributions will be not be interfaced as PAYMENTS but as INVOICES */
IF G_ACCTNG_METHOD = 'C' THEN
FORALL i IN l_sys_ref1_tbl.FIRST..l_sys_ref1_tbl.LAST
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'G',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.pay_dist_lookup_code = 'CASH'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND exists(SELECT /*+ no_unnest */ inv.invoice_id
FROM AP_INVOICES_ALL inv,
AP_Invoice_Distributions_all aid,
ap_invoice_payments_all aip
WHERE inv.invoice_id = aip.invoice_id
AND aid.invoice_id = inv.invoice_id
AND aip.invoice_payment_id = dist.invoice_payment_id
AND aid.invoice_distribution_id = dist.invoice_distribution_id
AND inv.org_id = G_ORG_ID
AND aip.invoice_id = l_sys_ref2_tbl(i)
AND aid.invoice_distribution_id = l_sys_ref5_tbl(i));
G_err_stage:= 'Failed during bulk update of invoice tieback';
END bulk_update_txn_intf;
bulk_update_txn_intf;
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_addition_flag = 'L'
WHERE rcv_sub.pa_addition_flag = 'N'
and reference3 = TO_CHAR(p_po_distribution_id) and exists (
select 1 from po_distributions_all pod
where po_distribution_id = TO_NUMBER(rcv_sub.reference3)
and po_distribution_id = p_po_distribution_id
and code_combination_id = rcv_sub.code_combination_id
and accrue_on_receipt_flag = 'Y');
/* UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_addition_flag = 'L'
WHERE rcv_sub.pa_addition_flag = 'N'
AND EXISTS (SELECT transaction_id
FROM rcv_transactions rcv_txn
WHERE rcv_txn.transaction_id = rcv_sub.rcv_transaction_id
AND rcv_txn.po_distribution_id = p_po_distribution_id ); commented for bug 6825742*/
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_addition_flag = 'G'
WHERE rcv_sub.pa_addition_flag = 'L';
G_err_stage := 'Selecting Adjustment account: get_cdl_ccid';
SELECT cr_code_combination_id, dr_code_combination_id
INTO l_cr_ccid, l_dr_ccid
FROm pa_cost_distribution_lines_all cdl
WHERE cdl.expenditure_item_id = p_expenditure_item_id
AND cdl.line_num in (select max(line_num)
from pa_cost_distribution_lines_all cdl2
where cdl2.expenditure_item_id = cdl.expenditure_item_id
and line_type ='R');
select nvl(fnd_profile.value_specific('PA_DISC_PULL_START_DATE'),'2051/01/01') --bug4474213.
INTO v_discount_start_date
from DUAL;
SELECT discount_distribution_method
INTO v_method
FROM AP_SYSTEM_PARAMETERS;
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'G',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.pay_dist_lookup_code = 'DISCOUNT'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND exists(SELECT /*+ no_unnest */ inv.invoice_id
FROM AP_INVOICES_ALL inv,
AP_Invoice_Distributions_all aid,
ap_invoice_payments_all aip
WHERE inv.invoice_id = aip.invoice_id
AND aid.invoice_id = inv.invoice_id
AND aip.invoice_payment_id = dist.invoice_payment_id
AND aid.invoice_distribution_id = dist.invoice_distribution_id
AND inv.invoice_type_lookup_code = 'PREPAYMENT' --Prevent prepayment payments from being transferred to Projects
AND aid.project_id = G_PROJECT_ID
AND aid.invoice_id = aip.invoice_id
AND inv.org_id = G_ORG_ID
AND trunc(aip.Accounting_Date) <= trunc(nvl(G_GL_DATE,aip.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date)));
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.pay_dist_lookup_code = 'DISCOUNT'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND exists(SELECT/*+ no_unnest */ inv.invoice_id
FROM AP_INVOICES_ALL inv,
PO_Distributions_all PO,
AP_Invoice_Distributions_all aid,
ap_invoice_payments_all aip
WHERE inv.invoice_id = aip.invoice_id
AND aid.invoice_id = inv.invoice_id
AND aip.invoice_payment_id = dist.invoice_payment_id
AND aid.invoice_distribution_id = dist.invoice_distribution_id
AND aid.po_distribution_id = PO.po_distribution_id (+)
AND aid.line_type_lookup_code not in ('TERV', 'REC_TAX') -- Bug#5441030 to avoid zero dollar lines for TERV
AND inv.org_id = G_ORG_ID
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
AND inv.paid_on_behalf_employee_id IS NULL
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting', 'PA_IC_INVOICES')
AND aid.project_id = G_PROJECT_ID
AND trunc(aip.Accounting_Date) <= trunc(nvl(G_GL_DATE,aip.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date))
AND ( (aid.expenditure_item_date >=G_Profile_Discount_Start_date
AND G_ACCTNG_METHOD = 'A'
AND G_discount_Method IN ('TAX','EXPENSE'))
OR --CAsh basis
(( (G_discount_Method IN ('TAX','EXPENSE') AND aid.expenditure_item_date < G_Profile_Discount_Start_date
AND nvl(aid.pa_addition_flag,'N') <> 'Y') ---bug 5516855
OR (G_discount_Method IN ('TAX','EXPENSE') AND aid.expenditure_item_date >= G_Profile_Discount_Start_date
AND aid.pa_addition_flag = 'Y') --bug 5516855 Added to allow disounts interface for historical data
OR (G_discount_Method = 'TAX' AND AID.line_type_lookup_code <> 'NONREC_TAX'
AND aid.expenditure_item_date >=G_Profile_Discount_Start_date ) --Bug#5189187
OR G_Discount_Method = 'SYSTEM') AND G_ACCTNG_METHOD = 'C')
) --See bug#4941454 for logic
);
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'G',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.pay_dist_lookup_code = 'DISCOUNT'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND exists(SELECT /*+ no_unnest */ inv.invoice_id
FROM AP_INVOICES_ALL inv,
AP_Invoice_Distributions_all aid,
ap_invoice_payments_all aip
WHERE inv.invoice_id = aip.invoice_id
AND aid.invoice_id = inv.invoice_id
AND aip.invoice_payment_id = dist.invoice_payment_id
AND aid.invoice_distribution_id = dist.invoice_distribution_id
AND inv.org_id = G_ORG_ID
AND inv.invoice_type_lookup_code = 'PREPAYMENT' --Prevent prepayment payments from being transferred to Projects
AND aid.project_id > 0
AND aid.invoice_id = aip.invoice_id
AND trunc(aip.Accounting_Date) <= trunc(nvl(G_GL_DATE,aip.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date)));
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.pay_dist_lookup_code = 'DISCOUNT'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND exists(SELECT /*+ no_unnest */ inv.invoice_id
FROM AP_INVOICES_ALL inv,
PO_Distributions_all PO,
AP_Invoice_Distributions_all aid,
ap_invoice_payments_all aip
WHERE inv.invoice_id = aip.invoice_id
AND aid.invoice_id = inv.invoice_id
AND aip.invoice_payment_id = dist.invoice_payment_id
AND aid.invoice_distribution_id = dist.invoice_distribution_id
AND inv.org_id = G_ORG_ID
AND aid.po_distribution_id = PO.po_distribution_id (+)
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
AND inv.paid_on_behalf_employee_id IS NULL
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting', 'PA_IC_INVOICES')
AND aid.project_id > 0
AND aid.line_type_lookup_code not in ('TERV', 'REC_TAX') -- Bug#5441030 to avoid zero dollar lines for TERV
AND trunc(aip.Accounting_Date) <= trunc(nvl(G_GL_DATE,aip.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date))
AND ( (aid.expenditure_item_date >=G_Profile_Discount_Start_date
AND G_ACCTNG_METHOD = 'A'
AND G_discount_Method IN ('TAX','EXPENSE'))
OR --CAsh basis
(( (G_discount_Method IN ('TAX','EXPENSE') AND aid.expenditure_item_date < G_Profile_Discount_Start_date
AND nvl(aid.pa_addition_flag,'N') <> 'Y') ---bug 5516855
OR (G_discount_Method IN ('TAX','EXPENSE') AND aid.expenditure_item_date >= G_Profile_Discount_Start_date
AND aid.pa_addition_flag = 'Y') --bug 5516855 Added to allow disounts interface for historical data
OR (G_discount_Method = 'TAX' AND AID.line_type_lookup_code <> 'NONREC_TAX'
AND aid.expenditure_item_date >=G_Profile_Discount_Start_date ) --Bug#5189187
OR G_Discount_Method = 'SYSTEM') AND G_ACCTNG_METHOD = 'C')
) --See bug#4941454 for logic
);
l_inv_pay_id_tbl.delete;
l_invoice_id_tbl.delete;
l_invoice_dist_id_tbl.delete;
l_project_id_tbl.delete;
l_task_id_tbl.delete;
l_employee_id_tbl.delete;
l_exp_type_tbl.delete;
l_ei_date_tbl.delete;
l_vendor_id_tbl.delete;
l_created_by_tbl.delete;
l_exp_org_id_tbl.delete;
l_quantity_tbl.delete;
l_job_id_tbl.delete;
l_description_tbl.delete;
l_dist_cc_id_tbl.delete;
l_acct_pay_cc_id_tbl.delete;
l_gl_date_tbl.delete;
l_attribute_cat_tbl.delete;
l_attribute1_tbl.delete;
l_attribute2_tbl.delete;
l_attribute3_tbl.delete;
l_attribute4_tbl.delete;
l_attribute5_tbl.delete;
l_attribute6_tbl.delete;
l_attribute7_tbl.delete;
l_attribute8_tbl.delete;
l_attribute9_tbl.delete;
l_attribute10_tbl.delete;
l_inv_type_code_tbl.delete;
l_org_id_tbl.delete;
l_invoice_num_tbl.delete;
l_ln_type_lookup_tbl.delete;
l_source_tbl.delete;
l_denom_raw_cost_tbl.delete;
l_amount_tbl.delete;
l_denom_cur_code_tbl.delete;
l_acct_rate_date_tbl.delete;
l_acct_rate_type_tbl.delete;
l_acct_exch_rate_tbl.delete;
l_cdl_sys_ref4_tbl.delete;
l_cdl_sys_ref3_tbl.delete;
l_txn_src_tbl.delete;
l_user_txn_src_tbl.delete;
l_batch_name_tbl.delete;
l_interface_id_tbl.delete;
l_exp_end_date_tbl.delete;
l_txn_status_code_tbl.delete;
l_txn_rej_code_tbl.delete;
l_bus_grp_id_tbl.delete;
l_reversal_flag_tbl.delete; --NEW
l_net_zero_flag_tbl.delete; --NEW
l_sc_xfer_code_tbl.delete; --NEW
l_parent_pmt_id_tbl.delete; --NEW
l_fc_enabled_tbl.delete; --NEW
l_rev_parent_dist_id_tbl.delete;
l_rev_child_dist_id_tbl.delete;
l_rev_parent_dist_ind_tbl.delete;
l_si_assts_add_flg_tbl.delete;
l_pay_hist_id_tbl.delete;
l_pa_add_flag_tbl.delete;
PROCEDURE bulk_insert_trx_intf IS
l_status2 VARCHAR2(30);
INSERT INTO pa_transaction_interface_all(
transaction_source
, user_transaction_source
, system_linkage
, batch_name
, expenditure_ending_date
, expenditure_item_date
, expenditure_type
, quantity
, raw_cost_rate
, expenditure_comment
, transaction_status_code
, transaction_rejection_code
, orig_transaction_reference
, interface_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5 --NEW
, gl_date
, org_id
, unmatched_negative_txn_flag
, denom_raw_cost
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, last_update_date
, last_updated_by
, creation_date
, created_by
, person_id
, organization_id
, project_id
, task_id
, Vendor_id
, override_to_organization_id
, person_business_group_id
, adjusted_expenditure_item_id
, fc_document_type
, document_type
, document_distribution_type
, si_assets_addition_flag
, sc_xfer_code
,net_zero_adjustment_flag
)
SELECT l_txn_src_tbl(i)
,l_user_txn_src_tbl(i)
,G_SYSTEM_LINKAGE
,l_batch_name_tbl(i)
,l_exp_end_date_tbl(i)
,l_ei_date_tbl(i)
,l_exp_type_tbl(i)
,l_quantity_tbl(i)
,l_amount_tbl(i)/decode(nvl(l_quantity_tbl(i),0),0,1,l_quantity_tbl(i))
,l_description_tbl(i)
,l_txn_status_code_tbl(i)
,l_txn_rej_code_tbl(i)
,G_REQUEST_ID
,l_interface_id_tbl(i)
,l_dist_cc_id_tbl(i)
,l_acct_pay_cc_id_tbl(i)
,l_pay_hist_id_tbl(i) --cdl_ref1
,l_invoice_id_tbl(i)
,l_cdl_sys_ref3_tbl(i)
,l_inv_pay_id_tbl(i)
,l_invoice_dist_id_tbl(i) --NEW
,l_gl_date_tbl(i)
,G_ORG_ID
,'Y'
,l_denom_raw_cost_tbl(i)
,l_denom_cur_code_tbl(i)
,l_acct_rate_date_tbl(i)
,l_acct_rate_type_tbl(i)
,l_acct_exch_rate_tbl(i)
,l_amount_tbl(i)
,1
,l_attribute_cat_tbl(i)
,l_attribute1_tbl(i)
,l_attribute2_tbl(i)
,l_attribute3_tbl(i)
,l_attribute4_tbl(i)
,l_attribute5_tbl(i)
,l_attribute6_tbl(i)
,l_attribute7_tbl(i)
,l_attribute8_tbl(i)
,l_attribute9_tbl(i)
,l_attribute10_tbl(i)
,l_invoice_id_tbl(i) /*orig_exp_txn_reference1*/
,l_invoice_num_tbl(i) /*user_exp_txn_reference*/
,NULL /*orig_exp_txn_reference2*/
,NULL /*orig_exp_txn_reference3*/
,SYSDATE
,-1
,SYSDATE
,-1
,l_employee_id_tbl(i)
,l_org_id_tbl(i)
,l_project_id_tbl(i)
,l_task_id_tbl(i)
,l_vendor_id_tbl(i)
,l_exp_org_id_tbl(i)
,l_bus_grp_id_tbl(i)
,l_adj_exp_item_id_tbl(i)
,l_fc_document_type_tbl(i)
,l_inv_type_code_tbl(i)
,l_ln_type_lookup_tbl(i)
,l_si_assts_add_flg_tbl(i)
,l_sc_xfer_code_tbl(i)
,l_net_zero_flag_tbl(i)
FROM dual;
write_log(LOG, 'Inserting adjustment records..');
INSERT INTO pa_transaction_interface_all(
transaction_source
, user_transaction_source
, system_linkage
, batch_name
, expenditure_ending_date
, expenditure_item_date
, expenditure_type
, quantity
, raw_cost_rate
, expenditure_comment
, transaction_status_code
, transaction_rejection_code
, orig_transaction_reference
, interface_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5 --NEW
, gl_date
, org_id
, unmatched_negative_txn_flag
, denom_raw_cost
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, last_update_date
, last_updated_by
, creation_date
, created_by
, person_id
, organization_id
, project_id
, task_id
, Vendor_id
, override_to_organization_id
, person_business_group_id
, adjusted_expenditure_item_id
, fc_document_type
, document_type
, document_distribution_type
, si_assets_addition_flag
, adjusted_txn_interface_id
, sc_xfer_code
,net_zero_adjustment_flag
)
SELECT l_txn_src_tbl(i)
,l_user_txn_src_tbl(i)
,G_SYSTEM_LINKAGE
,l_batch_name_tbl(i)
,l_exp_end_date_tbl(i)
,l_ei_date_tbl(i)
,l_exp_type_tbl(i)
,-l_quantity_tbl(i)
,l_amount_tbl(i)/decode(nvl(l_quantity_tbl(i),0),0,1,l_quantity_tbl(i))
,l_description_tbl(i)
,l_txn_status_code_tbl(i)
,l_txn_rej_code_tbl(i)
,G_REQUEST_ID
,l_interface_id_tbl(i)
,l_dist_cc_id_tbl(i)
,l_acct_pay_cc_id_tbl(i)
,l_pay_hist_id_tbl(i) --cdl_ref1
,l_invoice_id_tbl(i)
,l_cdl_sys_ref3_tbl(i)
,l_inv_pay_id_tbl(i)
,l_invoice_dist_id_tbl(i)
,l_gl_date_tbl(i)
,G_ORG_ID
,'Y'
,-l_denom_raw_cost_tbl(i)
,l_denom_cur_code_tbl(i)
,l_acct_rate_date_tbl(i)
,l_acct_rate_type_tbl(i)
,l_acct_exch_rate_tbl(i)
,-l_amount_tbl(i)
,1
,l_attribute_cat_tbl(i)
,l_attribute1_tbl(i)
,l_attribute2_tbl(i)
,l_attribute3_tbl(i)
,l_attribute4_tbl(i)
,l_attribute5_tbl(i)
,l_attribute6_tbl(i)
,l_attribute7_tbl(i)
,l_attribute8_tbl(i)
,l_attribute9_tbl(i)
,l_attribute10_tbl(i)
,l_invoice_id_tbl(i) /*orig_exp_txn_reference1*/
,l_invoice_num_tbl(i) /*user_exp_txn_reference*/
,NULL /*orig_exp_txn_reference2*/
,NULL /*orig_exp_txn_reference3*/
,SYSDATE
,-1
,SYSDATE
,-1
,l_employee_id_tbl(i)
,l_org_id_tbl(i)
,l_project_id_tbl(i)
,l_task_id_tbl(i)
,l_vendor_id_tbl(i)
,l_exp_org_id_tbl(i)
,l_bus_grp_id_tbl(i)
,l_adj_exp_item_id_tbl(i)
,l_fc_document_type_tbl(i)
,l_inv_type_code_tbl(i)
,l_ln_type_lookup_tbl(i)
, 'T' --l_si_assts_add_flg_tbl(i)
,(select xface.txn_interface_id
from pa_transaction_interface xface
where xface.interface_id = l_interface_id_tbl(i)
and xface.cdl_system_reference2 = l_invoice_id_tbl(i)
and xface.cdl_system_reference4 = to_char(l_inv_pay_id_tbl(i))
and xface.cdl_system_reference5 = l_invoice_dist_id_tbl(i)
and NVL(xface.adjusted_expenditure_item_id,0) = 0 ) -- R12 funds management Uptake
,'P' -- sc_xfer_code
,l_net_zero_flag_tbl(i)
FROM dual
WHERE l_insert_flag_tbl(i)= 'A';
UPDATE pa_transaction_interface_all xface
SET xface.net_zero_adjustment_flag ='Y',
xface.adjusted_txn_interface_id =
(select xface1.txn_interface_id
from pa_transaction_interface xface1
where xface1.interface_id = l_interface_id_tbl(l_rev_parent_dist_ind_tbl(i))
and xface1.cdl_system_reference2 = l_invoice_id_tbl(l_rev_parent_dist_ind_tbl(i))
and xface1.cdl_system_reference4 = to_char(l_inv_pay_id_tbl(l_rev_parent_dist_ind_tbl(i)))
and xface1.cdl_system_reference5 = l_invoice_dist_id_tbl(l_rev_parent_dist_ind_tbl(i))
)
WHERE xface.interface_id = l_interface_id_tbl(l_rev_parent_dist_ind_tbl(i))
AND xface.cdl_system_reference2 = l_invoice_id_tbl(l_rev_parent_dist_ind_tbl(i))
--AND xface.cdl_system_reference4 = to_char(l_rev_child_dist_id_tbl(i))
-- AND xface.cdl_system_reference5 = l_invoice_dist_id_tbl(l_rev_parent_dist_ind_tbl(i));
write_log(LOG,'Failed during bulk insert for discount processing');
END bulk_insert_trx_intf;
/* Update the previous invoice payment id*/
v_prev_inv_pay_id := l_inv_pay_id_tbl(i);
SELECT pa_utils.getweekending(MAX(l_ei_date_tbl(i)))
INTO G_EXPENDITURE_ENDING_DATE
FROM ap_invoice_distributions
WHERE invoice_id = l_invoice_id_tbl(i);
SELECT emp.business_group_id
INTO G_PER_BUS_GRP_ID
FROM per_all_people_f emp
WHERE emp.person_id = l_employee_id_tbl(i)
AND l_ei_date_tbl(i) between trunc(emp.effective_start_date) and
trunc(emp.effective_end_date);
select org2.business_group_id
into G_PER_BUS_GRP_ID
from hr_organization_units org1,
hr_organization_units org2
Where org1.organization_id = l_exp_org_id_tbl(i)
and org1.business_group_id = org2.organization_id;
IF l_insert_flag_tbl(i) in ('A','U') THEN
l_create_adj_recs := 'Y';
SELECT pa_interface_id_s.nextval
INTO G_DISC_INTERFACE_ID
FROM dual;
,l_insert_flag_tbl
,l_pay_hist_id_tbl
,l_pa_add_flag_tbl
LIMIT v_max_size;
G_err_stage := 'Calling Bulk Insert into trx intf for discounts';
bulk_insert_trx_intf;
/* Modified the below update statement for better performence bug#7526677
Not commenting and doing the changes because it has already become a mess with these updates
*/
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_Addition_Flag = decode(rcv_sub.pa_addition_flag,'N','O','I','J'),
rcv_sub.request_id = G_REQUEST_ID,
rcv_sub.last_update_date = SYSDATE,
rcv_sub.last_updated_by = G_USER_ID,
rcv_sub.last_update_login = G_USER_ID,
rcv_sub.program_id = G_PROG_ID,
rcv_sub.program_application_id = G_PROG_APPL_ID,
rcv_sub.program_update_date = SYSDATE
WHERE exists
(SELECT 1 --rcv_sub1.ROWID --Removed /*+ leading(po_dist) */ for Bug5262594
FROM Rcv_Transactions rcv_txn,
PO_Distributions po_dist
WHERE ((rcv_txn.destination_type_code ='EXPENSE' )
OR (rcv_txn.destination_type_code = 'RECEIVING'
AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING'))))
AND trunc(rcv_txn.transaction_date) <= trunc(nvl(G_GL_DATE,rcv_txn.transaction_date)) /*Added trunc for the bug 6623163 */
AND rcv_txn.PO_DISTRIBUTION_ID = po_dist.po_distribution_id
AND rcv_sub.code_combination_id = po_dist.code_combination_id
AND nvl(po_dist.distribution_type,'XXX') <> 'PREPAYMENT' --bug 7192304, added nvl
AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
AND trunc(po_dist.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,po_dist.expenditure_item_date)) /*Added trunc for the bug 6623163 */
AND po_dist.project_ID > 0
AND po_dist.accrue_on_receipt_flag= 'Y'
AND nvl(rcv_txn.project_id , po_dist.project_id) = G_PROJECT_ID /*Added for bug:7046666*/
/* Start added for bug#6015451 */
AND ( (rcv_txn.destination_type_code = 'EXPENSE' AND
rcv_txn.transaction_type <> 'RETURN TO RECEIVING' AND rcv_sub.entered_dr is NOT NULL
) OR
((rcv_txn.destination_type_code = 'RECEIVING' OR
rcv_txn.transaction_type = 'RETURN TO RECEIVING') AND rcv_sub.entered_cr is NOT NULL
)
)
/* Ends added for bug#6015451 */
)
AND rcv_sub.pa_addition_flag IN ('N','I')
AND rcv_sub.actual_flag = 'A';
/* Modified the below update statement for better performence bug#7526677
Not commenting and doing the changes because it has already become a mess with these updates
*/
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_Addition_Flag = decode(rcv_sub.pa_addition_flag,'N','O','I','J'),
rcv_sub.request_id = G_REQUEST_ID,
rcv_sub.last_update_date = SYSDATE,
rcv_sub.last_updated_by = G_USER_ID,
rcv_sub.last_update_login = G_USER_ID,
rcv_sub.program_id = G_PROG_ID,
rcv_sub.program_application_id = G_PROG_APPL_ID,
rcv_sub.program_update_date = SYSDATE
WHERE exists
(SELECT 1 --rcv_sub1.ROWID --Removed /*+ leading(po_dist) */ for Bug5262594
FROM Rcv_Transactions rcv_txn,
PO_Distributions po_dist
WHERE ((rcv_txn.destination_type_code ='EXPENSE' )
OR (rcv_txn.destination_type_code = 'RECEIVING'
AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING'))))
AND trunc(rcv_txn.transaction_date) <= trunc(nvl(G_GL_DATE,rcv_txn.transaction_date)) /*Added trunc for the bug 6623163 */
AND rcv_txn.PO_DISTRIBUTION_ID = po_dist.po_distribution_id
AND rcv_sub.code_combination_id = po_dist.code_combination_id
AND nvl(po_dist.distribution_type,'XXX') <> 'PREPAYMENT' -- bug 7192304, added nvl
AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
AND trunc(po_dist.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,po_dist.expenditure_item_date)) /*Added trunc for the bug 6623163 */
AND po_dist.project_ID > 0
AND po_dist.accrue_on_receipt_flag= 'Y'
/* Starts added for bug#6015451 */
AND ( (rcv_txn.destination_type_code = 'EXPENSE' AND
rcv_txn.transaction_type <> 'RETURN TO RECEIVING' AND rcv_sub.entered_dr is NOT NULL
) OR
((rcv_txn.destination_type_code = 'RECEIVING' OR
rcv_txn.transaction_type = 'RETURN TO RECEIVING') AND rcv_sub.entered_cr is NOT NULL
)
)
/* Ends added for bug#6015451 */
)
AND rcv_sub.pa_addition_flag IN ('N','I')
AND rcv_sub.actual_flag = 'A';
l_rcv_txn_id_tbl.delete;
l_po_dist_id_tbl.delete;
l_po_head_id_tbl.delete;
l_po_num_tbl.delete;
l_quantity_tbl.delete;
l_entered_dr_tbl.delete;
l_entered_cr_tbl.delete;
l_accounted_dr_tbl.delete;
l_accounted_cr_tbl.delete;
l_entered_nr_tax_tbl.delete;
l_accounted_nr_tax_tbl.delete;
l_denom_raw_cost_tbl.delete;
l_acct_raw_cost_tbl.delete;
l_record_type_tbl.delete;
l_dist_cc_id_tbl.delete;
l_denom_cur_code_tbl.delete;
l_acct_rate_date_tbl.delete;
l_acct_rate_type_tbl.delete;
l_acct_exch_rate_tbl.delete;
l_gl_date_tbl.delete;
l_dest_typ_code_tbl.delete;
l_pa_add_flag_tbl.delete;
l_trx_type_tbl.delete;
l_project_id_tbl.delete;
l_task_id_tbl.delete;
l_employee_id_tbl.delete;
l_exp_type_tbl.delete;
l_ei_date_tbl.delete;
l_vendor_id_tbl.delete;
l_exp_org_id_tbl.delete;
l_job_id_tbl.delete;
l_description_tbl.delete;
l_attribute_cat_tbl.delete;
l_attribute1_tbl.delete;
l_attribute2_tbl.delete;
l_attribute3_tbl.delete;
l_attribute4_tbl.delete;
l_attribute5_tbl.delete;
l_attribute6_tbl.delete;
l_attribute7_tbl.delete;
l_attribute8_tbl.delete;
l_attribute9_tbl.delete;
l_attribute10_tbl.delete;
l_org_id_tbl.delete;
l_cdl_sys_ref4_tbl.delete;
l_txn_src_tbl.delete;
l_user_txn_src_tbl.delete;
l_batch_name_tbl.delete;
l_interface_id_tbl.delete;
l_exp_end_date_tbl.delete;
l_txn_status_code_tbl.delete;
l_txn_rej_code_tbl.delete;
l_bus_grp_id_tbl.delete;
l_insert_flag_tbl.delete;
l_rcv_acct_evt_id_tbl.delete; -- pricing changes
l_rcv_acct_evt_typ_tbl.delete; -- pricing changes
l_rcv_acct_rec_tax_tbl.delete; -- pricing changes
l_rcv_ent_rec_tax_tbl.delete; -- pricing changes
l_parent_rcv_id_tbl.delete; -- NEW --added for full return reversal logic
l_net_zero_flag_tbl.delete;
l_sc_xfer_code_tbl.delete; --NEW
l_adj_exp_item_id_tbl.delete; --NEW
l_fc_enabled_tbl.delete; --NEW
l_fc_document_type_tbl.delete; --NEW
l_rcv_sub_ledger_id_tbl.delete;
l_si_assts_add_flg_tbl.delete;
l_exp_cst_rt_flg_tbl.delete; --NEW
l_po_tax_qty_tbl.delete;
PROCEDURE bulk_insert_trx_intf IS
l_status2 VARCHAR2(30);
INSERT INTO pa_transaction_interface_all(
transaction_source
, user_transaction_source
, system_linkage
, batch_name
, expenditure_ending_date
, expenditure_item_date
, expenditure_type
, quantity
, raw_cost_rate
, expenditure_comment
, transaction_status_code
, transaction_rejection_code
, orig_transaction_reference
, interface_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5
, gl_date
, org_id
, unmatched_negative_txn_flag
, denom_raw_cost
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, last_update_date
, last_updated_by
, creation_date
, created_by
, person_id
, organization_id
, project_id
, task_id
, Vendor_id
, override_to_organization_id
, person_business_group_id
, adjusted_expenditure_item_id --NEW
, fc_document_type -- NEW
, document_type
, document_distribution_type
, sc_xfer_code
, si_assets_addition_flag
, net_zero_adjustment_flag
)
SELECT l_txn_src_tbl(i)
,l_user_txn_src_tbl(i)
,G_SYSTEM_LINKAGE
,l_batch_name_tbl(i)
,l_exp_end_date_tbl(i)
,l_ei_date_tbl(i)
,l_exp_type_tbl(i)
,l_quantity_tbl(i)
,l_acct_raw_cost_tbl(i)/decode(nvl(l_quantity_tbl(i),0),0,1,l_quantity_tbl(i))
,l_description_tbl(i)
,l_txn_status_code_tbl(i)
,l_txn_rej_code_tbl(i)
,G_REQUEST_ID
,l_interface_id_tbl(i)
,l_dist_cc_id_tbl(i)
,NULL
,l_vendor_id_tbl(i)
,l_po_head_id_tbl(i)
,l_po_dist_id_tbl(i)
,l_rcv_txn_id_tbl(i)
,l_rcv_sub_ledger_id_tbl(i)
,l_gl_date_tbl(i)
,G_ORG_ID
,'Y'
,l_denom_raw_cost_tbl(i)
,l_denom_cur_code_tbl(i)
,l_acct_rate_date_tbl(i)
,l_acct_rate_type_tbl(i)
,l_acct_exch_rate_tbl(i)
,l_acct_raw_cost_tbl(i)
,1
,l_attribute_cat_tbl(i)
,l_attribute1_tbl(i)
,l_attribute2_tbl(i)
,l_attribute3_tbl(i)
,l_attribute4_tbl(i)
,l_attribute5_tbl(i)
,l_attribute6_tbl(i)
,l_attribute7_tbl(i)
,l_attribute8_tbl(i)
,l_attribute9_tbl(i)
,l_attribute10_tbl(i)
,l_po_dist_id_tbl(i) /*orig_exp_txn_reference1*/
,l_rcv_txn_id_tbl(i) /*user_exp_txn_reference*/
,l_rcv_acct_evt_id_tbl(i) /*orig_exp_txn_reference2*/
,NULL /*orig_exp_txn_reference3*/
,SYSDATE
,-1
,SYSDATE
,-1
,l_employee_id_tbl(i)
,l_org_id_tbl(i)
,l_project_id_tbl(i)
,l_task_id_tbl(i)
,l_vendor_id_tbl(i)
,l_exp_org_id_tbl(i)
,l_bus_grp_id_tbl(i)
,l_adj_exp_item_id_tbl(i) --NEW for reversals
,l_fc_document_type_tbl(i) --NEW for funds check
,l_dest_typ_code_tbl(i)
,l_trx_type_tbl(i)
,l_sc_xfer_code_tbl(i)
,l_si_assts_add_flg_tbl(i)
,l_net_zero_flag_tbl(i)
FROM dual
WHERE l_insert_flag_tbl(i) in ('Y','A');
write_log(LOG, 'Inserting adjustment records..');
INSERT INTO pa_transaction_interface_all(
transaction_source
, user_transaction_source
, system_linkage
, batch_name
, expenditure_ending_date
, expenditure_item_date
, expenditure_type
, quantity
, raw_cost_rate
, expenditure_comment
, transaction_status_code
, transaction_rejection_code
, orig_transaction_reference
, interface_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5
, gl_date
, org_id
, unmatched_negative_txn_flag
, denom_raw_cost
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, last_update_date
, last_updated_by
, creation_date
, created_by
, person_id
, organization_id
, project_id
, task_id
, Vendor_id
, override_to_organization_id
, person_business_group_id
, adjusted_expenditure_item_id --NEW
, fc_document_type -- NEW
, document_type
, document_distribution_type
, adjusted_txn_interface_id
, sc_xfer_code
, si_assets_addition_flag
, net_zero_adjustment_flag
)
SELECT l_txn_src_tbl(i)
,l_user_txn_src_tbl(i)
,G_SYSTEM_LINKAGE
,l_batch_name_tbl(i)
,l_exp_end_date_tbl(i)
,l_ei_date_tbl(i)
,l_exp_type_tbl(i)
,-l_quantity_tbl(i)
,l_acct_raw_cost_tbl(i)/decode(nvl(l_quantity_tbl(i),0),0,1,l_quantity_tbl(i))
,l_description_tbl(i)
,l_txn_status_code_tbl(i)
,l_txn_rej_code_tbl(i)
,G_REQUEST_ID
,l_interface_id_tbl(i)
,l_dist_cc_id_tbl(i)
,NULL
,l_vendor_id_tbl(i)
,l_po_head_id_tbl(i)
,l_po_dist_id_tbl(i)
,l_rcv_txn_id_tbl(i)
,l_rcv_sub_ledger_id_tbl(i)
,l_gl_date_tbl(i)
,G_ORG_ID
,'Y'
,-l_denom_raw_cost_tbl(i)
,l_denom_cur_code_tbl(i)
,l_acct_rate_date_tbl(i)
,l_acct_rate_type_tbl(i)
,l_acct_exch_rate_tbl(i)
,-l_acct_raw_cost_tbl(i)
,1
,l_attribute_cat_tbl(i)
,l_attribute1_tbl(i)
,l_attribute2_tbl(i)
,l_attribute3_tbl(i)
,l_attribute4_tbl(i)
,l_attribute5_tbl(i)
,l_attribute6_tbl(i)
,l_attribute7_tbl(i)
,l_attribute8_tbl(i)
,l_attribute9_tbl(i)
,l_attribute10_tbl(i)
,l_po_dist_id_tbl(i) /*orig_exp_txn_reference1*/
,l_rcv_txn_id_tbl(i) /*user_exp_txn_reference*/
,l_rcv_acct_evt_id_tbl(i) /*orig_exp_txn_reference2*/
,NULL /*orig_exp_txn_reference3*/
,SYSDATE
,-1
,SYSDATE
,-1
,l_employee_id_tbl(i)
,l_org_id_tbl(i)
,l_project_id_tbl(i)
,l_task_id_tbl(i)
,l_vendor_id_tbl(i)
,l_exp_org_id_tbl(i)
,l_bus_grp_id_tbl(i)
,l_adj_exp_item_id_tbl(i) --NEW for reversals
,l_fc_document_type_tbl(i) --NEW for funds check
,l_dest_typ_code_tbl(i)
,l_trx_type_tbl(i)
,(select xface.txn_interface_id
from pa_transaction_interface xface
where xface.interface_id = l_interface_id_tbl(i)
and xface.transaction_source = l_txn_src_tbl(i)
and xface.cdl_system_reference2 = l_po_head_id_tbl(i)
and xface.cdl_system_reference3 = l_po_dist_id_tbl(i)
and xface.cdl_system_reference4 = to_char(l_rcv_txn_id_tbl(i))
and nVL(xface.adjusted_expenditure_item_id,0) = 0 ) -- R12 funds management Uptake
,'P' -- sc_xfer_code
,'T' -- Si assets flag
,l_net_zero_flag_tbl(i)
FROM dual
WHERE l_insert_flag_tbl(i) = 'A';
write_log(LOG,'Failed during bulk insert for receipt processing');
END bulk_insert_trx_intf;
SELECT emp.business_group_id
INTO G_PER_BUS_GRP_ID
FROM per_all_people_f emp
WHERE emp.person_id = l_employee_id_tbl(i)
AND l_ei_date_tbl(i) between trunc(emp.effective_start_date) and
trunc(emp.effective_end_date);
SELECT pa_utils.getweekending(MAX(l_ei_date_tbl(i)))
INTO G_EXPENDITURE_ENDING_DATE
FROM po_distributions
WHERE po_distribution_id = l_po_dist_id_tbl(i);
SELECT event_type_name
INTO l_evt_typ_name
FROM rcv_accounting_event_types rcv_acct_evt_typ, rcv_accounting_events rcv_acct_evts
WHERE rcv_acct_evt_typ.event_type_id = rcv_acct_evts.event_type_id
AND rcv_acct_evts.accounting_event_id = l_rcv_acct_evt_id_tbl(i);
/* If the pa_addition_flag is updated to 'O', then it means we would need
to break down the amount of the receipt into two portions. One is the
total minus tax and the other is just the NR Tax portion. This Loop
will process the total minus tax portion. After inserting this amount
, the next loop will insert the tax portion of the receipt into the
tranasction_interface table */
v_num_receipts_processed := v_num_receipts_processed + 1;
/* for the amount that we are selecting, we need to see whether the transaction is an
EXPENSE or RECEIVING transactions. EXPENSE means we take the positive value of dr column
minus the tax amount while RECEIVNG transaction means it is a return, so we take the
negative of the cr column plus the tax amount. If the trasnaction is a RETURN,
then we want the quantity to be a negative quantity */
-- call the function to check if the txn got interfaced through AP. If Yes, then set the pa_add_flag to G.
IF ReceiptPaAdditionFlag(p_Pa_Addition_Flag => l_pa_add_flag_tbl(i),
p_Po_Distribution_Id => l_po_dist_id_tbl(i)) = 'G' THEN
write_log(LOG,'PA_addition_flag for this RCV transaction should be marked to G');
l_insert_flag_tbl(i) := 'N';
l_insert_flag_tbl(i) := 'N';
SELECT primary_quantity, amount
INTO l_primary_quantity, l_amount
FROM rcv_transactions
WHERE transaction_id = l_parent_rcv_id_tbl(i) ;
SELECT 'Y'
INTO l_exists
FROM dual
WHERE EXISTS (
SELECT pa_addition_flag
FROM rcv_receiving_sub_ledger
WHERE rcv_transaction_id = l_parent_rcv_id_tbl(i)
AND pa_addition_flag in ('Y','I')) ;
IF l_insert_flag_tbl(i)= 'A' THEN
l_create_adj_recs := 'Y';
l_insert_flag_tbl(i) := 'N';
SELECT primary_quantity, amount
INTO l_primary_quantity, l_amount
FROM rcv_transactions
WHERE transaction_id = l_parent_rcv_id_tbl(i) ;
SELECT 'Y'
INTO l_exists
FROM dual
WHERE EXISTS (
SELECT pa_addition_flag
FROM rcv_receiving_sub_ledger
WHERE rcv_transaction_id = l_parent_rcv_id_tbl(i)
AND pa_addition_flag in ('Y','I')) ;
IF l_insert_flag_tbl(i) = 'A' THEN
l_create_adj_recs := 'Y';
IF l_insert_flag_tbl(i) IS NULL THEN
l_insert_flag_tbl(i) := 'Y';
write_log(LOG, 'The value for Insert Flag : '||l_insert_flag_tbl(i) );
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_addition_flag = decode(l_pa_add_flag_tbl(i),'G','G',pa_addition_flag)
WHERE rcv_sub.rcv_transaction_id = l_rcv_txn_id_tbl(i)
AND rcv_sub.pa_addition_flag = 'O'
AND l_insert_flag_tbl(i) = 'N' ;
processed here. The reason for this is because when pa_addition_flag is updated
to 'O', it means we need to split the receipt into the receipt amount and the tax
amount. Since we call a bulk insert after calling process_receipt_logic then
it means the receipt without tax portion should have been inserted into the
txn interface table. The only part that we should now process are the tax portion
of these records. Thus, we will only process these records and nothing else */
/* Additional Note: Most of the values already stored in the plsql table for these records
can be reused except for the amount columns. Other columns like bus_group_id and DFF
fields are the same for either the receipt amount or just the tax amount */
G_err_stage:= 'Within process_receipt_logic';
l_insert_flag_tbl(i) := 'N';
l_insert_flag_tbl(i) := 'N';
l_insert_flag_tbl(i) := 'Y';
SELECT primary_quantity, nvl(amount,0)
INTO l_primary_quantity, l_amount
FROM rcv_transactions
WHERE transaction_id = l_parent_rcv_id_tbl(i) ;
SELECT 'Y'
INTO l_exists
FROM dual
WHERE EXISTS (
SELECT pa_addition_flag
FROM rcv_receiving_sub_ledger
WHERE rcv_transaction_id = l_parent_rcv_id_tbl(i)
AND pa_addition_flag in ('Y','I')) ;
IF l_insert_flag_tbl(i) = 'A' THEN
l_create_adj_recs := 'Y';
SELECT event_type_name
INTO l_evt_typ_name
FROM rcv_accounting_event_types rcv_acct_evt_typ, rcv_accounting_events rcv_acct_evts
WHERE rcv_acct_evt_typ.event_type_id = rcv_acct_evts.event_type_id
AND rcv_acct_evts.accounting_event_id = l_rcv_acct_evt_id_tbl(i);
SELECT pa_interface_id_s.nextval
INTO G_RCV_INTERFACE_ID FROM dual;
SELECT pa_interface_id_s.nextval
INTO G_RCVNRT_INTERFACE_ID FROM dual;
l_insert_flag_tbl,
l_rcv_acct_evt_id_tbl, -- pricing changes
l_rcv_acct_rec_tax_tbl,
l_rcv_ent_rec_tax_tbl,
l_parent_rcv_id_tbl, --NEW
l_net_zero_flag_tbl, -- NEW
l_sc_xfer_code_tbl,
l_amount_tbl,
l_adj_exp_item_id_tbl,
l_fc_enabled_tbl,
l_mrc_exchange_date_tbl,
l_fc_document_type_tbl,
l_si_assts_add_flg_tbl,
l_insert_flag_tbl,
l_rcv_sub_ledger_id_tbl,
l_exp_cst_rt_flg_tbl
LIMIT v_max_size;
The Receipt Cursor picks up both records that have pa_addition_flag updated
to either 'O' or 'J'. for the ones updated 'O', it means we need to split up
the record into receipt minus tax portion and just the tax portion. The call
to process_receipt_logic will insert only the receipt total minus tax portion.
For records updated to 'J', we will only pull the nr_tax portion and insert
into transaction_interface_all table for processing */
G_err_stage:='Begin processing just the receipt records';
bulk_insert_trx_intf;
/* The process_receipt_tax_logic applies only to those records that have been updated
to 'O'. After having the total receipt amount minus tax being inserted into txn
interface table, we need to insert the tax portion of these records by calling
process_receipt_tax_logic. The records that have been updated to 'J' would not be
processed here because the tax amount was taken cared of in proceess_receipt_logic
above.
*/
G_err_stage:='Begin processing receipt tax records';
bulk_insert_trx_intf;
SELECT cdl_system_reference1
,cdl_system_reference2
,cdl_system_reference3
,cdl_system_reference4
,transaction_source
,batch_name
,interface_id
,transaction_status_code
,project_id
,l_pa_addflag
,orig_exp_txn_reference2 -- pricing changes
,dr_code_combination_id
FROM pa_transaction_interface_all txnintf
WHERE txnintf.transaction_source = p_txn_src
AND txnintf.batch_name = p_batch_name
AND txnintf.interface_id = p_interface_id;
l_sys_ref1_tbl.delete;
l_sys_ref2_tbl.delete;
l_sys_ref3_tbl.delete;
l_sys_ref4_tbl.delete;
l_txn_src_tbl.delete;
l_batch_name_tbl.delete;
l_interface_id_tbl.delete;
l_txn_status_code_tbl.delete;
l_project_id_tbl.delete;
l_pa_addflag_tbl.delete;
l_rcv_acct_evt_id_tbl.delete; -- pricing changes
l_dr_ccid_tbl.delete;
update pa_addition_flag of RCV transactions to 'Y'.
If transaction import leaves the record to be 'P' then
update pa_addition_flag of RCV transactions to 'N' or 'I'.
If transaction import stamps the record to be 'R' then
update pa_addition_flag of RCV transactions to 'N' or 'I'.*/
write_log(LOG,'Tying back transaction ID: '||l_sys_ref4_tbl(i)||
'trc src: '||l_txn_src_tbl(i));
updated to N. If the status code is R then it should be
updated to I such that the program can pick it up in the
next run and interface ONLY the tax portion */
IF l_txn_status_code_tbl(i) = 'A' THEN
l_pa_addflag_tbl(i) := 'Y';
PROCEDURE bulk_update_txn_intf IS
v_status VARCHAR2(15);
G_err_stage:=('Within bulk update of RCV transactions tieback');
/* Code combination Id join is required so that the record whichw as earlier updated to X does nto get updated to Y */
UPDATE rcv_receiving_sub_ledger rcv_sub -- pricing changes
SET rcv_sub.pa_addition_flag = l_pa_addflag_tbl(i)
WHERE rcv_sub.rcv_transaction_id = l_sys_ref4_tbl(i)
AND (rcv_sub.accounting_event_id = l_rcv_acct_evt_id_tbl(i) OR rcv_sub.accounting_event_id IS NULL)
AND rcv_sub.code_combination_id = l_dr_ccid_tbl(i)
/* Start added for bug#6015451 */
AND EXISTS
(SELECT 1 from rcv_transactions rcv_txn WHERE
rcv_txn.transaction_id = l_sys_ref4_tbl(i)
AND ( (rcv_txn.destination_type_code = 'EXPENSE' AND
rcv_txn.transaction_type <> 'RETURN TO RECEIVING' AND rcv_sub.entered_dr is NOT NULL
) OR
((rcv_txn.destination_type_code = 'RECEIVING' OR
rcv_txn.transaction_type = 'RETURN TO RECEIVING') AND rcv_sub.entered_cr is NOT NULL
)
)
);
G_err_stage:= 'Failed during bulk update of RCV transactions tieback';
END bulk_update_txn_intf;
bulk_update_txn_intf;
SELECT cdl_system_reference2
,cdl_system_reference3
,cdl_system_reference4
,orig_exp_txn_reference2 -- pricing changes
FROM pa_transaction_interface
WHERE transaction_status_code = 'R'
AND transaction_source IN ('PO RECEIPT', 'PO RECEIPT PRICE ADJ')
AND batch_name = p_batch_name
AND interface_id = p_interface_id;
UPDATE pa_transaction_interface_all
SET transaction_status_code = 'T'
WHERE batch_name = G_RCVTAX_BATCH_NAME /* p_batch_name changed for #2912545 */
AND interface_id = G_RCVNRT_INTERFACE_ID /* p_interface_id changed for #2912545 */
AND transaction_source = 'PO RECEIPT NRTAX' /* PO RECEIPT TAX. changed for #2912545 */
AND cdl_system_reference2 = v_po_head_id
AND cdl_system_reference3 = v_po_dist_id
AND cdl_system_reference4 = v_txn_id;
UPDATE pa_transaction_interface_all
SET transaction_status_code = 'T'
WHERE batch_name = G_RCVTAX_BATCH_NAME /* p_batch_name changed for #2912545 */
AND interface_id = G_RCVNRT_INTERFACE_ID /* p_interface_id changed for #2912545 */
AND transaction_source = 'PO RECEIPT NRTAX PRICE ADJ' /* PO RECEIPT TAX. changed for #2912545 */
AND cdl_system_reference2 = v_po_head_id
AND cdl_system_reference3 = v_po_dist_id
AND cdl_system_reference4 = v_txn_id
AND orig_exp_txn_reference2 = v_acct_evt_id;
UPDATE ap_invoice_distributions_all dist
SET dist.pa_addition_flag = 'L'
WHERE dist.po_distribution_id = p_po_distribution_id
AND dist.pa_addition_flag = 'N';
UPDATE ap_invoice_distributions_all dist
SET dist.pa_addition_flag = 'N'
WHERE dist.pa_addition_flag = 'L';
UPDATE ap_invoice_distributions_all apdist
SET apdist.pa_addition_flag = DECODE(NVL(apdist.amount_variance,0),0,'G',increment_W_count('W')),
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by=G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE rowid IN (
SELECT dist.rowid
FROM ap_invoices inv, ap_invoice_distributions_all dist
WHERE inv.invoice_id = dist.invoice_id
AND (dist.line_type_lookup_code in ('ITEM','ACCRUAL','NONREC_TAX')
OR ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
AND EXISTS (SELECT 'X'
FROM mtl_system_items si,
po_lines_all pol,
po_distributions_all po_dist1
WHERE po_dist1.po_line_id = pol.po_line_id
AND po_dist1.po_distribution_id = dist.po_distribution_id
AND si.inventory_item_id = pol.item_id
AND po_dist1.project_id IS NOT NULL
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = po_dist1.org_id)
)
) --Bug#5399352. Added this here to take care of IPV/TIPV records matched to EIB item PO.
AND NOT EXISTS (SELECT NULL
FROM ap_invoice_distributions_all apdist2
WHERE apdist2.pa_addition_flag = 'Y'
AND apdist2.po_distribution_id = dist.po_distribution_id
AND apdist2.line_type_lookup_code = dist.line_type_lookup_code
AND apdist2.line_type_lookup_code in ('ITEM','ACCRUAL','NONREC_TAX')
)
AND ( EXISTS (SELECT NULL
FROM ap_invoice_distributions_all apdist1
WHERE apdist1.pa_addition_flag IN ('F', 'G')
AND apdist1.po_distribution_id = dist.po_distribution_id
)
OR EXISTS ( SELECT rcv_txn.po_distribution_id
FROM rcv_transactions rcv_txn
, rcv_receiving_sub_ledger rcv_sub
WHERE rcv_txn.po_distribution_id = dist.po_distribution_id
AND rcv_sub.pa_addition_flag || '' IN ('Y','I')
AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
)
OR EXISTS ( SELECT PO.po_distribution_id
FROM po_distributions PO
WHERE PO.po_distribution_id = dist.po_distribution_id
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT' -- bug 7192304, added nvl
AND PO.project_id > 0
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND PO.accrue_on_receipt_flag= 'Y'
)
)
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND nvl(INV.source, 'xx' ) NOT IN
('Oracle Project Accounting','PA_IC_INVOICES')
AND dist.pa_addition_flag = 'N'
AND dist.posted_flag = 'Y'
AND trunc(dist.accounting_date) <= trunc(nvl(G_GL_DATE,dist.Accounting_Date))
AND trunc(dist.Expenditure_Item_Date) <= trunc(NVL(G_TRANSACTION_DATE,dist.Expenditure_Item_Date))
AND dist.project_id = G_PROJECT_ID
-- Process historical distributions for CAsh BAsed Accounting
AND (G_ACCTNG_METHOD = 'A' OR (G_ACCTNG_METHOD = 'C' AND dist.historical_flag = 'Y'))
AND NVL(dist.po_distribution_id,0) > 0 );
pulled from Payables. The pa_addition_flag for such invoice ITEM distribution lines is updated to G to prevent
further processing into Projects.
However, if there is an amount variance on such invoice, it needs to be processed as W to releive variance commitment */
UPDATE ap_invoice_distributions_all apdist
SET apdist.pa_addition_flag = DECODE(NVL(apdist.amount_variance,0),0,'G',increment_W_count('W')),
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by=G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE rowid in (
SELECT dist.rowid
FROM ap_invoices inv,
po_distributions po,
ap_invoice_distributions_all dist
WHERE inv.invoice_id = dist.invoice_id
AND po.po_distribution_id = dist.po_distribution_id
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT' -- bug 7192304, added nvl
AND NVL(po.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND PA_PJC_CWK_UTILS.Is_cwk_tc_xface_allowed(nvl(dist.project_ID, 0))= 'Y'
AND PA_PJC_CWK_UTILS.Is_rate_based_line(null,nvl(dist.po_distribution_id,0))= 'Y'
AND dist.line_type_lookup_code in ( 'ITEM','ACCRUAL') --added accrual for historical data
AND dist.pa_addition_flag = 'N'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND nvl(INV.source, 'xx' ) NOT IN
('Oracle Project Accounting','PA_IC_INVOICES','PA_COST_ADJUSTMENTS')
AND dist.pa_addition_flag = 'N'
AND dist.project_id > 0
AND dist.posted_flag = 'Y'
AND trunc(dist.accounting_date) <= trunc(nvl(G_GL_DATE,dist.Accounting_Date)) /* Added trunc for the bug 6623163 */
AND trunc(dist.Expenditure_Item_Date) <= trunc(NVL(G_TRANSACTION_DATE,dist.Expenditure_Item_Date))/* Added trunc for the bug 6623163 */
AND dist.project_id = G_PROJECT_ID
-- Process historical distributions for CAsh BAsed Accounting
AND (G_ACCTNG_METHOD = 'A' OR (G_ACCTNG_METHOD = 'C' AND dist.historical_flag = 'Y'))
AND inv.paid_on_behalf_employee_id is NULL );
UPDATE ap_invoice_distributions_all apdist
SET apdist.pa_addition_flag = DECODE(NVL(apdist.amount_variance,0),0,'G',increment_W_count('W')),
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by=G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE rowid IN (
SELECT dist.rowid
FROM ap_invoices inv, ap_invoice_distributions_all dist
WHERE inv.invoice_id = dist.invoice_id
AND ( dist.line_type_lookup_code in ('ITEM','ACCRUAL','NONREC_TAX')
OR ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
AND EXISTS (SELECT 'X'
FROM mtl_system_items si,
po_lines_all pol,
po_distributions_all po_dist1
WHERE po_dist1.po_line_id = pol.po_line_id
AND po_dist1.po_distribution_id = dist.po_distribution_id
AND si.inventory_item_id = pol.item_id
AND po_dist1.project_id IS NOT NULL
AND si.comms_nl_trackable_flag = 'Y'
AND si.organization_id = po_dist1.org_id)
)
) --Bug#5399352. Added this here to take care of IPV/TIPV records matched to EIB item PO.
AND NOT EXISTS (SELECT NULL
FROM ap_invoice_distributions_all apdist2
WHERE apdist2.pa_addition_flag = 'Y'
AND apdist2.po_distribution_id = dist.po_distribution_id
AND apdist2.line_type_lookup_code in ('ITEM','ACCRUAL','NONREC_TAX')
)
AND ( EXISTS (SELECT NULL
FROM ap_invoice_distributions_all apdist1
WHERE apdist1.pa_addition_flag IN ('F', 'G')
AND apdist1.po_distribution_id = dist.po_distribution_id
)
OR EXISTS ( SELECT rcv_txn.po_distribution_id
FROM rcv_transactions rcv_txn
, rcv_receiving_sub_ledger rcv_sub
WHERE rcv_txn.po_distribution_id = dist.po_distribution_id
AND rcv_sub.pa_addition_flag || '' IN ('Y','I')
AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
)
OR EXISTS ( SELECT PO.po_distribution_id
FROM po_distributions PO
WHERE PO.po_distribution_id = dist.po_distribution_id
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT' -- bug 7192304, added nvl
AND PO.project_id > 0
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND PO.accrue_on_receipt_flag= 'Y'
)
)
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND nvl(INV.source, 'xx' ) NOT IN
('Oracle Project Accounting','PA_IC_INVOICES','PA_COST_ADJUSTMENTS')
AND dist.pa_addition_flag = 'N'
AND dist.posted_flag = 'Y'
AND trunc(dist.accounting_date) <= trunc(nvl(G_GL_DATE,dist.Accounting_Date)) /* Added trunc for the bug 6623163 */
AND trunc(dist.Expenditure_Item_Date) <= trunc(NVL(G_TRANSACTION_DATE,dist.Expenditure_Item_Date)) /* Added trunc for the bug 6623163 */
AND dist.project_id > 0
-- Process historical distributions for CAsh BAsed Accounting
AND (G_ACCTNG_METHOD = 'A' OR (G_ACCTNG_METHOD = 'C' AND dist.historical_flag = 'Y'))
AND NVL(dist.po_distribution_id,0) > 0 );
pulled from Payables. The pa_addition_flag for such invoice ITEM distribution lines is updated to G to prevent
further processing into Projects.
However, if there is an amount variance on such invoice, it needs to be processed as W to releive variance commitment */
UPDATE ap_invoice_distributions_all apdist
SET apdist.pa_addition_flag = DECODE(NVL(apdist.amount_variance,0),0,'G',increment_W_count('W')),
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by=G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE rowid in (
SELECT dist.rowid
FROM ap_invoices inv,
po_distributions po,
ap_invoice_distributions_all dist
WHERE inv.invoice_id = dist.invoice_id
AND po.po_distribution_id = dist.po_distribution_id
AND NVL(po.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT' -- bug 7192304, added nvl
AND PA_PJC_CWK_UTILS.Is_cwk_tc_xface_allowed(nvl(dist.project_ID, 0))= 'Y'
AND PA_PJC_CWK_UTILS.Is_rate_based_line(null,nvl(dist.po_distribution_id,0))= 'Y'
AND dist.line_type_lookup_code in ( 'ITEM','ACCRUAL') --added accrual for historical data
AND dist.pa_addition_flag = 'N'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND nvl(INV.source, 'xx' ) NOT IN
('Oracle Project Accounting','PA_IC_INVOICES','PA_COST_ADJUSTMENTS')
AND dist.pa_addition_flag = 'N'
AND dist.posted_flag = 'Y'
AND trunc(dist.accounting_date) <= trunc(nvl(G_GL_DATE,dist.Accounting_Date)) /* Added trunc for the bug 6623163 */
AND trunc(dist.Expenditure_Item_Date) <= trunc(NVL(G_TRANSACTION_DATE,dist.Expenditure_Item_Date)) /* Added trunc for the bug 6623163 */
AND dist.project_id > 0
-- Process historical distributions for CAsh BAsed Accounting
AND (G_ACCTNG_METHOD = 'A' OR (G_ACCTNG_METHOD = 'C' AND dist.historical_flag = 'Y'))
AND inv.paid_on_behalf_employee_id is NULL );
l_invoice_id_tbl.delete;
l_invoice_dist_id_tbl.delete;
l_project_id_tbl.delete;
l_task_id_tbl.delete;
l_ln_type_lookup_tbl.delete;
l_exp_type_tbl.delete;
l_ei_date_tbl.delete;
l_amount_tbl.delete;
l_description_tbl.delete;
l_dist_cc_id_tbl.delete;
l_exp_org_id_tbl.delete;
l_quantity_tbl.delete;
l_gl_date_tbl.delete;
l_attribute_cat_tbl.delete;
l_attribute1_tbl.delete;
l_attribute2_tbl.delete;
l_attribute3_tbl.delete;
l_attribute4_tbl.delete;
l_attribute5_tbl.delete;
l_attribute6_tbl.delete;
l_attribute7_tbl.delete;
l_attribute8_tbl.delete;
l_attribute9_tbl.delete;
l_attribute10_tbl.delete;
l_denom_raw_cost_tbl.delete;
l_denom_cur_code_tbl.delete;
l_acct_rate_date_tbl.delete;
l_acct_rate_type_tbl.delete;
l_acct_exch_rate_tbl.delete;
l_job_id_tbl.delete;
l_employee_id_tbl.delete;
l_vendor_id_tbl.delete;
l_inv_type_code_tbl.delete;
l_source_tbl.delete;
l_org_id_tbl.delete;
l_invoice_num_tbl.delete;
l_cdl_sys_ref3_tbl.delete;
l_cdl_sys_ref4_tbl.delete;
l_txn_src_tbl.delete;
l_user_txn_src_tbl.delete;
l_batch_name_tbl.delete;
l_interface_id_tbl.delete;
l_exp_end_date_tbl.delete;
l_txn_status_code_tbl.delete;
l_txn_rej_code_tbl.delete;
l_bus_grp_id_tbl.delete;
l_reversal_flag_tbl.delete; --NEW
l_net_zero_flag_tbl.delete; --NEW
l_sc_xfer_code_tbl.delete; --NEW
l_cancel_flag_tbl.delete; --NEW
l_parent_rev_id_tbl.delete; --NEW
l_adj_exp_item_id_tbl.delete; --NEW
l_fc_enabled_tbl.delete;
l_fc_document_type_tbl.delete;
PROCEDURE bulk_insert_trx_intf IS
l_status2 VARCHAR2(30);
INSERT INTO pa_transaction_interface_all(
transaction_source
, user_transaction_source
, system_linkage
, batch_name
, expenditure_ending_date
, expenditure_item_date
, expenditure_type
, quantity
, raw_cost_rate
, expenditure_comment
, transaction_status_code
, transaction_rejection_code
, orig_transaction_reference
, interface_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5 --NEW
, gl_date
, org_id
, unmatched_negative_txn_flag
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_raw_cost
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, last_update_date
, last_updated_by
, creation_date
, created_by
, person_id
, organization_id
, project_id
, task_id
, Vendor_id
, override_to_organization_id
, person_business_group_id
, adjusted_expenditure_item_id --NEW
, fc_document_type -- NEW
, sc_xfer_code
, si_assets_addition_flag
, net_zero_adjustment_flag
, expenditure_item_id
)
SELECT l_txn_src_tbl(i)
,l_user_txn_src_tbl(i)
,G_SYSTEM_LINKAGE
,l_batch_name_tbl(i)
,l_exp_end_date_tbl(i)
,l_ei_date_tbl(i)
,l_exp_type_tbl(i)
,l_quantity_tbl(i)
,l_amount_tbl(i)/decode(nvl(l_quantity_tbl(i),0),0,1,l_quantity_tbl(i))
,l_description_tbl(i)
,l_txn_status_code_tbl(i)
,l_txn_rej_code_tbl(i)
,G_REQUEST_ID
,l_interface_id_tbl(i)
,l_dist_cc_id_tbl(i)
,NULL
,l_vendor_id_tbl(i)
,l_invoice_id_tbl(i)
,l_cdl_sys_ref3_tbl(i)
,l_cdl_sys_ref4_tbl(i)
,l_invoice_dist_id_tbl(i) --NEW
,l_gl_date_tbl(i)
,G_ORG_ID
,'Y'
,NULL
,NULL
,NULL
,l_denom_raw_cost_tbl(i)
,l_denom_cur_code_tbl(i)
,l_acct_rate_date_tbl(i)
,l_acct_rate_type_tbl(i)
,l_acct_exch_rate_tbl(i)
,l_amount_tbl(i)
,1
,l_attribute_cat_tbl(i)
,l_attribute1_tbl(i)
,l_attribute2_tbl(i)
,l_attribute3_tbl(i)
,l_attribute4_tbl(i)
,l_attribute5_tbl(i)
,l_attribute6_tbl(i)
,l_attribute7_tbl(i)
,l_attribute8_tbl(i)
,l_attribute9_tbl(i)
,l_attribute10_tbl(i)
,l_invoice_id_tbl(i) /*orig_exp_txn_reference1*/
,l_invoice_num_tbl(i) /*user_exp_txn_reference*/
,NULL /*orig_exp_txn_reference2*/
,NULL /*orig_exp_txn_reference3*/
,SYSDATE
,-1
,SYSDATE
,-1
,l_employee_id_tbl(i)
,l_org_id_tbl(i)
,l_project_id_tbl(i)
,l_task_id_tbl(i)
,l_vendor_id_tbl(i)
,l_exp_org_id_tbl(i)
,l_bus_grp_id_tbl(i)
,l_adj_exp_item_id_tbl(i) --NEW for reversals
,l_fc_document_type_tbl(i) --NEW for funds checking
,l_sc_xfer_code_tbl(i)
,l_si_assts_add_flg_tbl(i)
,l_net_zero_flag_tbl(i)
,0 -- To relieve the cmt. It will not be interfaced from xface in trx imp.
FROM dual;
write_log(LOG, 'Inserting adjustment records..');
INSERT INTO pa_transaction_interface_all(
transaction_source
, user_transaction_source
, system_linkage
, batch_name
, expenditure_ending_date
, expenditure_item_date
, expenditure_type
, quantity
, raw_cost_rate
, expenditure_comment
, transaction_status_code
, transaction_rejection_code
, orig_transaction_reference
, interface_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5 --NEW
, gl_date
, org_id
, unmatched_negative_txn_flag
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_raw_cost
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, last_update_date
, last_updated_by
, creation_date
, created_by
, person_id
, organization_id
, project_id
, task_id
, Vendor_id
, override_to_organization_id
, person_business_group_id
, adjusted_expenditure_item_id --NEW
, fc_document_type -- NEW
, adjusted_txn_interface_id --NEW
, sc_xfer_code
, si_assets_addition_flag
, net_zero_adjustment_flag
)
SELECT l_txn_src_tbl(i)
,l_user_txn_src_tbl(i)
,G_SYSTEM_LINKAGE
,l_batch_name_tbl(i)
,l_exp_end_date_tbl(i)
,l_ei_date_tbl(i)
,l_exp_type_tbl(i)
,-l_quantity_tbl(i)
,l_amount_tbl(i)/decode(nvl(l_quantity_tbl(i),0),0,1,l_quantity_tbl(i))
,l_description_tbl(i)
,l_txn_status_code_tbl(i)
,l_txn_rej_code_tbl(i)
,G_REQUEST_ID
,l_interface_id_tbl(i)
,l_dist_cc_id_tbl(i)
,NULL
,l_vendor_id_tbl(i)
,l_invoice_id_tbl(i)
,l_cdl_sys_ref3_tbl(i)
,l_cdl_sys_ref4_tbl(i)
,l_invoice_dist_id_tbl(i) --NEW
,l_gl_date_tbl(i)
,G_ORG_ID
,'Y'
,NULL
,NULL
,NULL
,-l_denom_raw_cost_tbl(i)
,l_denom_cur_code_tbl(i)
,l_acct_rate_date_tbl(i)
,l_acct_rate_type_tbl(i)
,l_acct_exch_rate_tbl(i)
,-l_amount_tbl(i)
,1
,l_attribute_cat_tbl(i)
,l_attribute1_tbl(i)
,l_attribute2_tbl(i)
,l_attribute3_tbl(i)
,l_attribute4_tbl(i)
,l_attribute5_tbl(i)
,l_attribute6_tbl(i)
,l_attribute7_tbl(i)
,l_attribute8_tbl(i)
,l_attribute9_tbl(i)
,l_attribute10_tbl(i)
,l_invoice_id_tbl(i) /*orig_exp_txn_reference1*/
,l_invoice_num_tbl(i) /*user_exp_txn_reference*/
,NULL /*orig_exp_txn_reference2*/
,NULL /*orig_exp_txn_reference3*/
,SYSDATE
,-1
,SYSDATE
,-1
,l_employee_id_tbl(i)
,l_org_id_tbl(i)
,l_project_id_tbl(i)
,l_task_id_tbl(i)
,l_vendor_id_tbl(i)
,l_exp_org_id_tbl(i)
,l_bus_grp_id_tbl(i)
,l_adj_exp_item_id_tbl(i) --NEW for reversals
,l_fc_document_type_tbl(i) --NEW for funds checking
,(select xface.txn_interface_id
from pa_transaction_interface xface
where xface.interface_id = l_interface_id_tbl(i)
and xface.cdl_system_reference2 = l_invoice_id_tbl(i)
and xface.cdl_system_reference5 = l_invoice_dist_id_tbl(i)
and NVL(xface.adjusted_expenditure_item_id,0) =0 ) -- R12 funds management Uptake
,'P' -- sc_xfer_code
,l_si_assts_add_flg_tbl(i)
,l_net_zero_flag_tbl(i)
FROM dual
WHERE l_insert_flag_tbl(i)= 'A';
write_log(LOG,'Failed during bulk insert of inv var processing');
END bulk_insert_trx_intf;
/* Update the previous invoice id and vendor id*/
v_prev_invoice_id := l_invoice_id_tbl(i);
SELECT pa_utils.getweekending(MAX(expenditure_item_date))
INTO G_EXPENDITURE_ENDING_DATE
FROM ap_invoice_distributions
WHERE invoice_id = l_invoice_id_tbl(i);
SELECT emp.business_group_id
INTO G_PER_BUS_GRP_ID
FROM per_all_people_f emp
WHERE emp.person_id = l_employee_id_tbl(i)
AND l_ei_date_tbl(i) between trunc(emp.effective_start_date) and
trunc(emp.effective_end_date);
select org2.business_group_id
into G_PER_BUS_GRP_ID
from hr_organization_units org1,
hr_organization_units org2
Where org1.organization_id = l_exp_org_id_tbl(i)
and org1.business_group_id = org2.organization_id;
SELECT nvl(historical_flag,'N') --check if this flag can be used
INTO l_historical_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id_tbl(i)
AND invoice_distribution_id = l_parent_rev_id_tbl(i); --check the index on this table
IF l_insert_flag_tbl(i) in ('A','U') THEN
l_create_adj_recs := 'Y';
SELECT pa_interface_id_s.nextval
INTO G_AP_VAR_INTERFACE_ID
FROM dual;
,l_insert_flag_tbl
LIMIT v_max_size;
G_err_stage := 'calling bulk_insert_trx_intf within transfer_inv_var_to_pa';
bulk_insert_trx_intf;
SELECT cdl_system_reference1
,cdl_system_reference2
,cdl_system_reference5
,transaction_source
,batch_name
,interface_id
,transaction_status_code
,project_id
,l_pa_addflag
,l_assets_addflag
FROM pa_transaction_interface_all txnintf
WHERE txnintf.transaction_source = p_txn_src
AND txnintf.batch_name = p_batch_name
AND txnintf.interface_id = p_interface_id;
l_sys_ref1_tbl.delete;
l_sys_ref2_tbl.delete;
l_sys_ref5_tbl.delete;
l_txn_src_tbl.delete;
l_batch_name_tbl.delete;
l_interface_id_tbl.delete;
l_txn_status_code_tbl.delete;
l_project_id_tbl.delete;
l_pa_addflag_tbl.delete;
l_assets_addflag_tbl.delete;
update pa_addition_flag of invoice distribution to 'F'.
If transaction import leaves the record to be 'P' then
update pa_addition_flag of invoice distribution to 'N'.
If transaction import stamps the record to be 'R' then
update pa_addition_flag of invoice distribution to 'N'.*/
write_log(LOG,'Tying invoice_id: '||l_sys_ref2_tbl(i)||
-- 'dist num: '||l_sys_ref3_tbl(i)||
-- 'sys ref 4: '||l_sys_ref4_tbl(i)||
'trc src: '||l_txn_src_tbl(i));
G_err_stage:='Selecting assets addition flag within invoice variance tieback';
SELECT decode(PTYPE.Project_Type_Class_Code,'CAPITAL','P','X')
INTO l_assets_addflag_tbl(i)
FROM pa_project_types_all PTYPE,
pa_projects_all PROJ
WHERE PTYPE.Project_Type = PROJ.Project_Type
AND (PTYPE.org_id = PROJ.org_id OR
PROJ.org_id is null)
AND PROJ.Project_Id = l_project_id_tbl(i);
PROCEDURE bulk_update_txn_intf IS
v_status VARCHAR2(15);
G_err_stage:=('Within bulk update of invoice variance tieback');
UPDATE ap_invoice_distributions_all dist
SET dist.pa_addition_flag = l_pa_addflag_tbl(i)
,dist.assets_addition_flag = decode(l_assets_addflag_tbl(i),'P','P',dist.assets_addition_flag)
WHERE dist.invoice_id = l_sys_ref2_tbl(i)
AND dist.invoice_distribution_id = l_sys_ref5_tbl(i)
AND dist.pa_addition_flag = 'W';
G_err_stage:= 'Failed during bulk update of invoice variance tieback';
END bulk_update_txn_intf;
bulk_update_txn_intf;
l_num_dists_updated NUMBER;
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'Z',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND EXISTS (SELECT invoice_id
FROM AP_invoice_distributions aid
WHERE aid.invoice_distribution_id = dist.invoice_distribution_id
AND aid.project_id = G_PROJECT_ID
AND ( nvl(aid.encumbered_flag,'N') = 'R' )) ;
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'Z',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND EXISTS (SELECT aid.invoice_id
FROM AP_invoice_distributions aid
WHERE aid.invoice_distribution_id = dist.invoice_distribution_id
AND aid.project_id > 0
AND nvl(aid.encumbered_flag,'N') = 'R' ) ;
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'Z',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND EXISTS (SELECT aid.invoice_id
FROM AP_invoice_distributions aid
WHERE aid.invoice_distribution_id = dist.invoice_distribution_id
AND aid.project_id = G_PROJECT_ID
AND ( nvl(aid.encumbered_flag,'N') = 'R' )) ;
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'Z',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND EXISTS (SELECT aid.invoice_id
FROM AP_invoice_distributions aid
WHERE aid.invoice_distribution_id = dist.invoice_distribution_id
AND aid.project_id > 0
AND ( nvl(aid.encumbered_flag,'N') = 'R' )) ;
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.pay_dist_lookup_code = 'CASH'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND exists (SELECT inv.invoice_id
FROM AP_INVOICES inv,
PO_VENDORS vend,
AP_Invoice_Distributions_all aid,
ap_invoice_payments_all aip
WHERE inv.invoice_id = aid.invoice_id
AND inv.vendor_id = vend.vendor_id
AND aip.invoice_payment_id = dist.invoice_payment_id
AND aid.invoice_distribution_id = dist.invoice_distribution_id
AND aid.pa_addition_flag = 'N' --to avoid any historical data to be processed as Payments
AND nvl(aid.historical_flag,'N') = 'N'
AND aid.invoice_id = aip.invoice_id
AND aid.project_id = G_PROJECT_ID
AND trunc(aip.Accounting_Date) <= trunc(nvl(G_GL_DATE,aip.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date)) /*GSCC*/
AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2,
G_INVOICE_SOURCE3)
AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0))
OR
(inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') /*Bug# 3373933*/ /*Bug 4099522*/
AND inv.source in ('CREDIT CARD','Both Pay')
AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0)));
UPDATE ap_prepay_app_dists dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.amount <>0
AND exists(SELECT /*+ no_unnest */ inv.invoice_id
FROM AP_INVOICES_ALL inv,
PO_VENDORS vend,
PO_Distributions_all PO,
AP_Invoice_Distributions_all aid, --STD INV DIST LINE
AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
WHERE aid.invoice_id = inv.invoice_id
AND inv.vendor_id = vend.vendor_id
AND inv.org_id = G_ORG_ID
AND aid.invoice_distribution_id = dist.invoice_distribution_id -- Std inv line
AND aid2.invoice_id = aid.invoice_id
AND aid2.invoice_distribution_id = dist.prepay_app_distribution_id --Prepay appl line
--AND aid2.line_type_lookup_code in ( 'PREPAY', 'NONREC_TAX') -- bug#5514129
AND aid.line_type_lookup_code <> 'REC_TAX' -- bug#5514129
and aid2.prepay_distribution_id is not null
AND aid2.pa_addition_flag in ( 'N','E') --to avoid any historical data to be processed as Payments
-- pa-addition-flag E to pull in rec tax across which prepay appl is prorated
AND aid2.posted_flag = 'Y'
AND aid.project_id = G_PROJECT_ID
AND trunc(aid2.Accounting_Date) <= trunc(nvl(G_GL_DATE,aid2.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date))
AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2, G_INVOICE_SOURCE3)
AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0))
OR
(inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') /*Bug# 3373933*/ /*Bug 4099522*/
AND inv.source in ('CREDIT CARD','Both Pay')
AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0)));
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.pay_dist_lookup_code = 'CASH'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND exists (SELECT inv.invoice_id
FROM AP_INVOICES_ALL inv,
PO_VENDORS vend,
AP_Invoice_Distributions_all aid,
ap_invoice_payments_all aip
WHERE inv.vendor_id = vend.vendor_id
AND aid.invoice_id = inv.invoice_id
AND inv.org_id = G_ORG_ID
AND aip.invoice_payment_id = dist.invoice_payment_id
AND aid.invoice_distribution_id = dist.invoice_distribution_id
AND aid.invoice_id = aip.invoice_id
AND aid.project_id > 0
AND aid.pa_addition_flag = 'N' --to avoid any historical data to be processed as Payments
AND nvl(aid.historical_flag,'N') = 'N'
AND trunc(aip.Accounting_Date) <= trunc(nvl(G_GL_DATE,aip.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date)) /*GSCC*/
AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2,
G_INVOICE_SOURCE3)
AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0))
OR
(inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') /*Bug# 3373933*/ /*Bug 4099522*/
AND inv.source in ('CREDIT CARD','Both Pay')
AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0)));
UPDATE ap_prepay_app_dists dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.amount <>0
AND exists(SELECT /*+ no_unnest */ inv.invoice_id
FROM AP_INVOICES_ALL inv,
PO_VENDORS vend,
PO_Distributions_all PO,
AP_Invoice_Distributions_all aid, --STD INV DIST LINE
AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
WHERE aid.invoice_id = inv.invoice_id
AND inv.vendor_id = vend.vendor_id
AND inv.org_id = G_ORG_ID
AND aid.invoice_distribution_id = dist.invoice_distribution_id -- Std inv line
AND aid2.invoice_id = aid.invoice_id
AND aid2.invoice_distribution_id = dist.prepay_app_distribution_id --Prepay appl line
--AND aid2.line_type_lookup_code in ( 'PREPAY', 'NONREC_TAX') -- bug#5514129
AND aid.line_type_lookup_code <> 'REC_TAX' -- bug#5514129
AND aid2.pa_addition_flag in ( 'N','E') --to avoid any historical data to be processed as Payments
-- pa-addition-flag E to pull in rec tax across which prepay appl is prorated
and aid2.prepay_distribution_id is not null
AND aid2.posted_flag = 'Y'
AND aid.project_id > 0
AND trunc(aid2.Accounting_Date) <= trunc(nvl(G_GL_DATE,aid2.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date))
AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
AND inv.source IN (G_INVOICE_SOURCE1, G_INVOICE_SOURCE2, G_INVOICE_SOURCE3)
AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0))
OR
(inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') /*Bug# 3373933*/ /*Bug 4099522*/
AND inv.source in ('CREDIT CARD','Both Pay')
AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0)));
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.pay_dist_lookup_code = 'CASH'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND exists(SELECT /*+ no_unnest */ inv.invoice_id
FROM AP_INVOICES_ALL inv,
PO_Distributions_all PO,
AP_Invoice_Distributions_all aid,
ap_invoice_payments_all aip
WHERE inv.invoice_id = aip.invoice_id
AND aid.invoice_id = inv.invoice_id
AND inv.org_id = G_ORG_ID
AND aip.invoice_payment_id = dist.invoice_payment_id
AND aid.invoice_distribution_id = dist.invoice_distribution_id
AND aid.line_type_lookup_code <> 'TERV' -- Bug#5441030 to avoid zero dollar lines for TERV
AND aid.invoice_id = aip.invoice_id
AND aid.pa_addition_flag = 'N' --to avoid any historical data to be processed as Payments
AND nvl(aid.historical_flag,'N') = 'N'
AND aid.po_distribution_id = PO.po_distribution_id (+)
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
AND inv.paid_on_behalf_employee_id IS NULL
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting', 'PA_IC_INVOICES')
AND aid.project_id = G_PROJECT_ID
AND aid.line_type_lookup_code <> 'REC_TAX'
AND (((
PA_PJC_CWK_UTILS.Is_cwk_tc_xface_allowed(nvl(aid.project_ID, 0))= 'N'
OR
PA_PJC_CWK_UTILS.Is_rate_based_line(null,nvl(aid.po_distribution_id,0))= 'N' )
AND aid.line_type_lookup_code IN ('ITEM','ACCRUAL')) OR
(aid.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')))
AND trunc(aip.Accounting_Date) <= trunc(nvl(G_GL_DATE,aip.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date)));
UPDATE ap_prepay_app_dists dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.amount <> 0
AND exists(SELECT /*+ no_unnest */ inv.invoice_id
FROM AP_INVOICES_ALL inv,
PO_Distributions_all PO,
AP_Invoice_Distributions_all aid, --STD INV DIST LINE
AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
WHERE aid.invoice_id = inv.invoice_id
AND aid.invoice_distribution_id = dist.invoice_distribution_id -- Std inv line
AND inv.org_id = G_ORG_ID
AND aid2.invoice_id = aid.invoice_id
AND aid2.invoice_distribution_id = dist.prepay_app_distribution_id --Prepay appl line
--AND aid2.line_type_lookup_code in ( 'PREPAY', 'NONREC_TAX') -- bug#5514129
AND aid.line_type_lookup_code <> 'REC_TAX' -- bug#5514129
AND aid2.pa_addition_flag in ( 'N','E') --to avoid any historical data to be processed as Payments
-- pa-addition-flag E to pull in rec tax across which prepay appl is prorated
and aid2.prepay_distribution_id is not null
AND aid2.posted_flag = 'Y'
AND aid.po_distribution_id = PO.po_distribution_id (+)
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
AND inv.paid_on_behalf_employee_id IS NULL
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting', 'PA_IC_INVOICES')
AND aid.project_id = G_PROJECT_ID
AND (((
PA_PJC_CWK_UTILS.Is_cwk_tc_xface_allowed(nvl(aid.project_ID, 0))= 'N'
OR
PA_PJC_CWK_UTILS.Is_rate_based_line(null,nvl(aid.po_distribution_id,0))= 'N' )
AND aid.line_type_lookup_code IN ('ITEM','ACCRUAL')) OR
(aid.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')))
AND trunc(aid2.Accounting_Date) <= trunc(nvl(G_GL_DATE,aid2.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date)));
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.pay_dist_lookup_code = 'CASH'
AND EXISTS (SELECT NULL
FROM ap_payment_history_all hist
WHERE hist.payment_history_id = dist.payment_history_id
AND hist.posted_flag = 'Y')
AND exists(SELECT /*+ no_unnest */ inv.invoice_id
FROM AP_INVOICES_ALL inv,
PO_Distributions_all PO,
AP_Invoice_Distributions_all aid,
ap_invoice_payments_all aip
WHERE inv.invoice_id = aip.invoice_id
AND aid.invoice_id = inv.invoice_id
AND inv.org_id = G_ORG_ID
AND aip.invoice_payment_id = dist.invoice_payment_id
AND aid.invoice_distribution_id = dist.invoice_distribution_id
AND aid.line_type_lookup_code <> 'TERV' -- Bug#5441030 to avoid zero dollar lines for TERV
AND aid.invoice_id = aip.invoice_id
AND aid.pa_addition_flag = 'N' --to avoid any historical data to be processed as Payments
AND nvl(aid.historical_flag,'N') = 'N'
AND aid.po_distribution_id = PO.po_distribution_id (+)
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
AND inv.paid_on_behalf_employee_id IS NULL
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting', 'PA_IC_INVOICES')
AND aid.project_id > 0
AND aid.line_type_lookup_code <> 'REC_TAX'
AND (((
PA_PJC_CWK_UTILS.Is_cwk_tc_xface_allowed(nvl(aid.project_ID, 0))= 'N'
OR
PA_PJC_CWK_UTILS.Is_rate_based_line(null,nvl(aid.po_distribution_id,0))= 'N' )
AND aid.line_type_lookup_code IN ('ITEM','ACCRUAL')) OR
(aid.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')))
AND trunc(aip.Accounting_Date) <= trunc(nvl(G_GL_DATE,aip.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date)));
UPDATE ap_prepay_app_dists dist
SET dist.pa_addition_flag = 'O',
request_id = G_REQUEST_ID,
last_update_date=SYSDATE,
last_updated_by= G_USER_ID,
last_update_login= G_USER_ID,
program_id= G_PROG_ID,
program_application_id= G_PROG_APPL_ID,
program_update_date=SYSDATE
WHERE nvl(dist.pa_addition_flag,'N') = 'N'
AND dist.amount <> 0
AND exists(SELECT /*+ no_unnest */ inv.invoice_id
FROM AP_INVOICES_ALL inv,
PO_Distributions_all PO,
AP_Invoice_Distributions_all aid, --STD INV DIST LINE
AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
WHERE aid.invoice_id = inv.invoice_id
AND inv.org_id = G_ORG_ID
AND aid.invoice_distribution_id = dist.invoice_distribution_id -- Std inv line
AND aid2.invoice_id = aid.invoice_id
AND aid2.invoice_distribution_id = dist.prepay_app_distribution_id --Prepay appl line
--AND aid2.line_type_lookup_code in ( 'PREPAY', 'NONREC_TAX') -- bug#5514129
AND aid.line_type_lookup_code <> 'REC_TAX' -- bug#5514129
AND aid2.pa_addition_flag in ( 'N','E') --to avoid any historical data to be processed as Payments
-- pa-addition-flag E to pull in rec tax across which prepay appl is prorated
and aid2.prepay_distribution_id is not null
AND aid2.posted_flag = 'Y'
AND aid.po_distribution_id = PO.po_distribution_id (+)
AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
AND inv.paid_on_behalf_employee_id IS NULL
AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
AND inv.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting', 'PA_IC_INVOICES')
AND aid.project_id> 0
AND (((
PA_PJC_CWK_UTILS.Is_cwk_tc_xface_allowed(nvl(aid.project_ID, 0))= 'N'
OR
PA_PJC_CWK_UTILS.Is_rate_based_line(null,nvl(aid.po_distribution_id,0))= 'N' )
AND aid.line_type_lookup_code IN ('ITEM','ACCRUAL')) OR
(aid.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')))
AND trunc(aid2.Accounting_Date) <= trunc(nvl(G_GL_DATE,aid2.Accounting_Date))
AND trunc(aid.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,aid.expenditure_item_date)));
l_invoice_id_tbl.delete;
l_created_by_tbl.delete;
l_invoice_dist_id_tbl.delete; --NEW
l_project_id_tbl.delete;
l_task_id_tbl.delete;
l_ln_type_lookup_tbl.delete;
l_exp_type_tbl.delete;
l_ei_date_tbl.delete;
l_amount_tbl.delete;
l_description_tbl.delete;
l_justification_tbl.delete;
l_dist_cc_id_tbl.delete;
l_exp_org_id_tbl.delete;
l_quantity_tbl.delete;
l_acct_pay_cc_id_tbl.delete;
l_gl_date_tbl.delete;
l_attribute_cat_tbl.delete;
l_attribute1_tbl.delete;
l_attribute2_tbl.delete;
l_attribute3_tbl.delete;
l_attribute4_tbl.delete;
l_attribute5_tbl.delete;
l_attribute6_tbl.delete;
l_attribute7_tbl.delete;
l_attribute8_tbl.delete;
l_attribute9_tbl.delete;
l_attribute10_tbl.delete;
l_rec_cur_amt_tbl.delete;
l_rec_cur_code_tbl.delete;
l_rec_conv_rate_tbl.delete;
l_denom_raw_cost_tbl.delete;
l_denom_cur_code_tbl.delete;
l_acct_rate_date_tbl.delete;
l_acct_rate_type_tbl.delete;
l_acct_exch_rate_tbl.delete;
l_job_id_tbl.delete;
l_employee_id_tbl.delete;
l_vendor_id_tbl.delete;
l_inv_type_code_tbl.delete;
l_source_tbl.delete;
l_org_id_tbl.delete;
l_invoice_num_tbl.delete;
l_cdl_sys_ref3_tbl.delete;
l_cdl_sys_ref4_tbl.delete;
l_po_dist_id_tbl.delete;
l_txn_src_tbl.delete;
l_user_txn_src_tbl.delete;
l_batch_name_tbl.delete;
l_interface_id_tbl.delete;
l_exp_end_date_tbl.delete;
l_txn_status_code_tbl.delete;
l_txn_rej_code_tbl.delete;
l_po_dist_id_tbl.delete;
l_bus_grp_id_tbl.delete;
l_paid_emp_id_tbl.delete;
l_sort_var_tbl.delete;
l_reversal_flag_tbl.delete;
l_cancel_flag_tbl.delete;
l_parent_rev_id_tbl.delete;
l_net_zero_flag_tbl.delete;
l_sc_xfer_code_tbl.delete;
l_adj_exp_item_id_tbl.delete;
l_fc_enabled_tbl.delete;
l_fc_document_type_tbl.delete;
l_rev_parent_dist_id_tbl.delete;
l_rev_child_dist_id_tbl.delete;
l_rev_parent_dist_ind_tbl.delete;
l_si_assts_add_flg_tbl.delete;
l_pay_hist_id_tbl.delete;
l_prepay_dist_id_tbl.delete;
/* the following sub-procedure is declared here to save lines of code since bulk insert
will be done multiple times within the procedure transfer_pay_to_pa */
PROCEDURE bulk_update_trx_intf IS
BEGIN
/* The records with INSERT_FLAG = F indicate that they are fully applied prepayments and the pa-addition-flag
for such records will be updated to G to relieve commitments*/
/* The records with INSERT_FLAG = P indicate that they are partially applied prepayments and the pa-addition-flag
for such records will be updated to N */
write_log(LOG,'Before bulk update of prepayment payments');
UPDATE ap_payment_hist_dists dist
SET dist.pa_addition_flag = decode(l_insert_flag_tbl(i),'F','G','P','N')
WHERE dist.invoice_payment_id = l_inv_pay_id_tbl(i)
AND dist.pay_dist_lookup_code = 'CASH'
AND dist.invoice_distribution_id = l_invoice_dist_id_tbl(i)
AND dist.pa_addition_flag = 'O'
AND l_insert_flag_tbl(i) in ('P','F');
write_log(LOG,'Failed during bulk update for prepayment processing');
END bulk_update_trx_intf;
PROCEDURE bulk_insert_trx_intf IS
l_status2 VARCHAR2(30);
INSERT INTO pa_transaction_interface_all(
transaction_source
, user_transaction_source
, system_linkage
, batch_name
, expenditure_ending_date
, expenditure_item_date
, expenditure_type
, quantity
, raw_cost_rate
, expenditure_comment
, transaction_status_code
, transaction_rejection_code
, orig_transaction_reference
, interface_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5 --NEW
, gl_date
, org_id
, unmatched_negative_txn_flag
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_raw_cost
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, last_update_date
, last_updated_by
, creation_date
, created_by
, person_id
, organization_id
, project_id
, task_id
, Vendor_id
, override_to_organization_id
, person_business_group_id
, adjusted_expenditure_item_id --NEW
, fc_document_type -- NEW
, document_type
, document_distribution_type
, sc_xfer_code
, si_assets_addition_flag
, net_zero_adjustment_flag
)
SELECT l_txn_src_tbl(i)
,l_user_txn_src_tbl(i)
,G_SYSTEM_LINKAGE
,l_batch_name_tbl(i)
,l_exp_end_date_tbl(i)
,l_ei_date_tbl(i)
,l_exp_type_tbl(i)
,l_quantity_tbl(i)
,l_amount_tbl(i)/decode(nvl(l_quantity_tbl(i),0),0,1,l_quantity_tbl(i))
,l_description_tbl(i)
,l_txn_status_code_tbl(i)
,l_txn_rej_code_tbl(i)
,G_REQUEST_ID
,l_interface_id_tbl(i)
,l_dist_cc_id_tbl(i)
,l_acct_pay_cc_id_tbl(i)
,decode(l_ln_type_lookup_tbl(i),'PREPAY',l_vendor_id_tbl(i),l_pay_hist_id_tbl(i)) /*sysref1*/
,l_invoice_id_tbl(i) /*sysref2*/
,l_cdl_sys_ref3_tbl(i) /*sysref3*/
,l_inv_pay_id_tbl(i) /*sysref4*/
,l_invoice_dist_id_tbl(i) /*sysref5*/
,l_gl_date_tbl(i)
,G_ORG_ID
,'Y'
,l_rec_cur_amt_tbl(i)
,l_rec_cur_code_tbl(i)
,l_rec_conv_rate_tbl(i)
,l_denom_raw_cost_tbl(i)
,l_denom_cur_code_tbl(i)
,l_acct_rate_date_tbl(i)
,l_acct_rate_type_tbl(i)
,l_acct_exch_rate_tbl(i)
,l_amount_tbl(i)
,1
,l_attribute_cat_tbl(i)
,l_attribute1_tbl(i)
,l_attribute2_tbl(i)
,l_attribute3_tbl(i)
,l_attribute4_tbl(i)
,l_attribute5_tbl(i)
,l_attribute6_tbl(i)
,l_attribute7_tbl(i)
,l_attribute8_tbl(i)
,l_attribute9_tbl(i)
,l_attribute10_tbl(i)
,l_invoice_id_tbl(i) /*orig_exp_txn_reference1*/
,l_invoice_num_tbl(i) /*user_exp_txn_reference*/
,DECODE(G_TRANS_DFF_AP,'N',NULL,l_invoice_id_tbl(i)) /*orig_exp_txn_reference2*/
,NULL /*orig_exp_txn_reference3*/
,SYSDATE
,-1
,SYSDATE
,-1
,l_employee_id_tbl(i)
,l_org_id_tbl(i)
,l_project_id_tbl(i)
,l_task_id_tbl(i)
,l_vendor_id_tbl(i)
,l_exp_org_id_tbl(i)
,l_bus_grp_id_tbl(i)
,l_adj_exp_item_id_tbl(i) --NEW for reversals
,l_fc_document_type_tbl(i) --NEW for funds check
,l_inv_type_code_tbl(i)
,l_ln_type_lookup_tbl(i)
,l_sc_xfer_code_tbl(i)
,l_si_assts_add_flg_tbl(i)
,l_net_zero_flag_tbl(i)
FROM DUAL
WHERE l_insert_flag_tbl(i) not in ('P','F');
write_log(LOG, 'Inserting adjustment records..');
INSERT INTO pa_transaction_interface_all(
transaction_source
, user_transaction_source
, system_linkage
, batch_name
, expenditure_ending_date
, expenditure_item_date
, expenditure_type
, quantity
, raw_cost_rate
, expenditure_comment
, transaction_status_code
, transaction_rejection_code
, orig_transaction_reference
, interface_id
, dr_code_combination_id
, cr_code_combination_id
, cdl_system_reference1
, cdl_system_reference2
, cdl_system_reference3
, cdl_system_reference4
, cdl_system_reference5 --NEW
, gl_date
, org_id
, unmatched_negative_txn_flag
, receipt_currency_amount
, receipt_currency_code
, receipt_exchange_rate
, denom_raw_cost
, denom_currency_code
, acct_rate_date
, acct_rate_type
, acct_exchange_rate
, acct_raw_cost
, acct_exchange_rounding_limit
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, orig_exp_txn_reference1
, orig_user_exp_txn_reference
, orig_exp_txn_reference2
, orig_exp_txn_reference3
, last_update_date
, last_updated_by
, creation_date
, created_by
, person_id
, organization_id
, project_id
, task_id
, Vendor_id
, override_to_organization_id
, person_business_group_id
, adjusted_expenditure_item_id --NEW
, fc_document_type -- NEW
, document_type
, document_distribution_type
, adjusted_txn_interface_id --NEW
, sc_xfer_code
, si_assets_addition_flag
, net_zero_adjustment_flag
)
SELECT
l_txn_src_tbl(i)
,l_user_txn_src_tbl(i)
,G_SYSTEM_LINKAGE
,l_batch_name_tbl(i)
,l_exp_end_date_tbl(i)
,l_ei_date_tbl(i)
,l_exp_type_tbl(i)
,-l_quantity_tbl(i)
,l_amount_tbl(i)/decode(nvl(l_quantity_tbl(i),0),0,1,l_quantity_tbl(i))
,l_description_tbl(i)
,l_txn_status_code_tbl(i)
,l_txn_rej_code_tbl(i)
,G_REQUEST_ID
,l_interface_id_tbl(i)
,l_dist_cc_id_tbl(i)
,l_acct_pay_cc_id_tbl(i)
--,l_vendor_id_tbl(i) /*sysref1*/
,decode(l_ln_type_lookup_tbl(i),'PREPAY',l_vendor_id_tbl(i),l_pay_hist_id_tbl(i)) /*sysref1*/
,l_invoice_id_tbl(i) /*sysref2*/
,l_cdl_sys_ref3_tbl(i)/*sysref3*/
,l_inv_pay_id_tbl(i) /*sysref4*/
,l_invoice_dist_id_tbl(i) /*sysref5*/ --NEW
,l_gl_date_tbl(i)
,G_ORG_ID
,'Y'
,-l_rec_cur_amt_tbl(i)
,l_rec_cur_code_tbl(i)
,l_rec_conv_rate_tbl(i)
,-l_denom_raw_cost_tbl(i)
,l_denom_cur_code_tbl(i)
,l_acct_rate_date_tbl(i)
,l_acct_rate_type_tbl(i)
,l_acct_exch_rate_tbl(i)
,-l_amount_tbl(i)
,1
,l_attribute_cat_tbl(i)
,l_attribute1_tbl(i)
,l_attribute2_tbl(i)
,l_attribute3_tbl(i)
,l_attribute4_tbl(i)
,l_attribute5_tbl(i)
,l_attribute6_tbl(i)
,l_attribute7_tbl(i)
,l_attribute8_tbl(i)
,l_attribute9_tbl(i)
,l_attribute10_tbl(i)
,l_invoice_id_tbl(i) /*orig_exp_txn_reference1*/
,l_invoice_num_tbl(i) /*user_exp_txn_reference*/
/* bug 2835757*/
,DECODE(G_TRANS_DFF_AP,'N',NULL,l_invoice_id_tbl(i)) /*orig_exp_txn_reference2*/
,NULL /*orig_exp_txn_reference3*/
,SYSDATE
,-1
,SYSDATE
,-1
,l_employee_id_tbl(i)
,l_org_id_tbl(i)
,l_project_id_tbl(i)
,l_task_id_tbl(i)
,l_vendor_id_tbl(i)
,l_exp_org_id_tbl(i)
,l_bus_grp_id_tbl(i)
,l_adj_exp_item_id_tbl(i) --NEW for reversals
,l_fc_document_type_tbl(i) --NEW for funds check
,l_inv_type_code_tbl(i)
,l_ln_type_lookup_tbl(i)
,(select xface.txn_interface_id
from pa_transaction_interface xface
where xface.interface_id = l_interface_id_tbl(i)
and xface.cdl_system_reference2 = l_invoice_id_tbl(i)
and xface.cdl_system_reference4 = to_char(l_inv_pay_id_tbl(i))
and xface.cdl_system_reference5 = l_invoice_dist_id_tbl(i)
and NVL(xface.adjusted_expenditure_item_id,0) = 0 ) -- R12 funds management Uptake
,'P'
,'T' -- l_si_assts_add_flg_tbl(i)
,l_net_zero_flag_tbl(i)
FROM dual
WHERE l_insert_flag_tbl(i)= 'A';
UPDATE pa_transaction_interface_all xface
SET xface.net_zero_adjustment_flag ='Y',
xface.adjusted_txn_interface_id =
(select xface1.txn_interface_id
from pa_transaction_interface xface1
where xface1.interface_id = l_interface_id_tbl(l_rev_parent_dist_ind_tbl(i))
and xface1.cdl_system_reference2 = l_invoice_id_tbl(l_rev_parent_dist_ind_tbl(i))
and xface1.cdl_system_reference4 = to_char(l_inv_pay_id_tbl(l_rev_parent_dist_ind_tbl(i)))
and xface1.cdl_system_reference5 = l_invoice_dist_id_tbl(l_rev_parent_dist_ind_tbl(i))
)
WHERE xface.interface_id = l_interface_id_tbl(l_rev_parent_dist_ind_tbl(i))
AND xface.cdl_system_reference2 = l_invoice_id_tbl(l_rev_parent_dist_ind_tbl(i))
AND -- For voided payments l_rev_child_dist_id_tbl stores the reversed payment id Bug# 5408748
-- Here the reversal pair will have same inv dist id and diff payment id's
((
xface.cdl_system_reference4 = To_char(l_rev_child_dist_id_tbl(i))
AND xface.cdl_system_reference5 = l_invoice_dist_id_tbl(l_rev_parent_dist_ind_tbl(i))
)
OR -- For invoice reversal l_rev_child_dist_id_tbl stores the reversed invoice dist id Bug# 5408748
-- Here the reversal pair will have same payment id and diff inv dist id's
(
xface.cdl_system_reference4 = to_char(l_inv_pay_id_tbl(l_rev_parent_dist_ind_tbl(i)))
AND xface.cdl_system_reference5 = To_char(l_rev_child_dist_id_tbl(i))
)) ;
write_log(LOG,'Failed during bulk insert for payment processing');
END bulk_insert_trx_intf;
/* Update the previous invoice id and vendor id*/
v_prev_invoice_id := l_invoice_id_tbl(i);
/* First update the v_prev_invoice_source */
G_err_stage := 'New source encountered';
SELECT pa_utils.getweekending(MAX(l_ei_date_tbl(i)))
INTO G_EXPENDITURE_ENDING_DATE
FROM ap_invoice_distributions
WHERE invoice_id = l_invoice_id_tbl(i);
SELECT emp.business_group_id
INTO G_PER_BUS_GRP_ID
FROM per_all_people_f emp
WHERE emp.person_id = l_employee_id_tbl(i)
AND l_ei_date_tbl(i) between trunc(emp.effective_start_date) and trunc(emp.effective_end_date);
select org2.business_group_id
into G_PER_BUS_GRP_ID
from hr_organization_units org1,
hr_organization_units org2
Where org1.organization_id = l_exp_org_id_tbl(i)
and org1.business_group_id = org2.organization_id ;
/*Update counter of how many distributions of the last invoice of the batch has been processed*/
IF l_invoice_id_tbl(i) = l_invoice_id_tbl(v_last_inv_index) THEN
v_num_last_invoice_processed := v_num_last_invoice_processed +1;
/* Update counter for number of tax lines fetched */
v_num_tax_lines_fetched := v_num_tax_lines_fetched +1;
/* Update counter for number of variance lines fetched */
v_num_inv_variance_fetched := v_num_inv_variance_fetched +1;
/* Update counter for number of variance lines fetched */
v_num_inv_erv_fetched := v_num_inv_erv_fetched +1;
/* Update counter for number of frt and misc lines fetched */
v_num_inv_frt_fetched := v_num_inv_frt_fetched +1;
/* The records with INSERT_FLAG = F indicate that they are fully applied prepayments and the pa-addition-flag
for such records will be updated to G to relieve commitments*/
/* The records with INSERT_FLAG = P indicate that they are partially applied prepayments and the pa-addition-flag
for such records will be updated to N */
IF (l_inv_type_code_tbl(i) = 'PREPAYMENT' ) THEN
IF check_prepay_fully_applied(l_invoice_dist_id_tbl(i)) = 'Y' THEN
l_insert_flag_tbl(i) := 'F';
l_insert_flag_tbl(i) := 'P';
IF l_insert_flag_tbl(i) in ('A','U') THEN
l_create_adj_recs := 'Y';
SELECT pa_interface_id_s.nextval
INTO G_INTERFACE_ID
FROM dual;
SELECT pa_interface_id_s.nextval
into G_NRT_INTERFACE_ID
FROM dual;
SELECT pa_interface_id_s.nextval
into G_AP_FRT_INTERFACE_ID
FROM dual;
SELECT pa_interface_id_s.nextval
into G_AP_VAR_INTERFACE_ID
FROM dual;
SELECT pa_interface_id_s.nextval
into G_AP_ERV_INTERFACE_ID
FROM dual;
,l_insert_flag_tbl
,l_pay_hist_id_tbl
,l_prepay_dist_id_tbl --bug#5514129
LIMIT v_max_size;
G_err_stage := 'calling bulk_update_trx_intf within transfer_pay_to_pa';
bulk_update_trx_intf; --Update Prepayment trx
G_err_stage := 'calling bulk_insert_trx_intf within transfer_pay_to_pa';
bulk_insert_trx_intf;
G_err_stage := 'After calling bulk_insert_trx_intf within transfer_pay_to_pa';
SELECT pa_interface_id_s.nextval
into G_PREPAY_INTERFACE_ID
FROM dual;
,l_insert_flag_tbl
,l_pay_hist_id_tbl
,l_prepay_dist_id_tbl --bug#5514129
LIMIT v_max_size;
G_err_stage := 'calling bulk_insert_trx_intf within transfer_pay_to_pa';
bulk_insert_trx_intf;
G_err_stage := 'After calling bulk_insert_trx_intf within transfer_pay_to_pa';
SELECT cdl_system_reference1
,cdl_system_reference2
,cdl_system_reference4
,cdl_system_reference5
,transaction_source
,batch_name
,interface_id
,transaction_status_code
,project_id
,l_pa_addflag
,l_assets_addflag
FROM pa_transaction_interface_all txnintf
WHERE txnintf.transaction_source = p_txn_src
AND txnintf.batch_name = p_batch_name
AND txnintf.interface_id = p_interface_id;
l_sys_ref1_tbl.delete;
l_sys_ref2_tbl.delete;
l_sys_ref4_tbl.delete;
l_sys_ref5_tbl.delete;
l_txn_src_tbl.delete;
l_batch_name_tbl.delete;
l_interface_id_tbl.delete;
l_txn_status_code_tbl.delete;
l_project_id_tbl.delete;
l_pa_addflag_tbl.delete;
l_assets_addflag_tbl.delete;
update pa_addition_flag of invoice distribution to 'Y'.
If transaction import leaves the record to be 'P' then
update pa_addition_flag of invoice distribution to 'N'.
If transaction import stamps the record to be 'R' then
update pa_addition_flag of invoice distribution to 'N'.*/
write_log(LOG,'Tying invoice_id: '||l_sys_ref2_tbl(i)||
'Payment Id: '||l_sys_ref4_tbl(i)||
'dist id: '||l_sys_ref5_tbl(i)||
'trc src: '||l_txn_src_tbl(i));
G_err_stage:='Selecting assets addition flag within payment tieback';
SELECT decode(PTYPE.Project_Type_Class_Code,'CAPITAL','P','X')
INTO l_assets_addflag_tbl(i)
FROM pa_project_types_all PTYPE,
pa_projects_all PROJ
WHERE PTYPE.Project_Type = PROJ.Project_Type
AND (PTYPE.org_id = PROJ.org_id OR
PROJ.org_id is null)
AND PROJ.Project_Id = l_project_id_tbl(i);
PROCEDURE bulk_update_txn_intf(l_batch in VARCHAR2) IS
v_status VARCHAR2(15);
G_err_stage:=('Within bulk update of payment tieback');
UPDATE ap_prepay_app_dists dist
SET dist.pa_addition_flag = l_pa_addflag_tbl(i)
WHERE dist.prepay_app_dist_id = l_sys_ref4_tbl(i)
AND dist.invoice_distribution_id = l_sys_ref5_tbl(i)
AND dist.pa_addition_flag = 'O';
UPDATE ap_payment_hist_dists paydist
SET paydist.pa_addition_flag = l_pa_addflag_tbl(i)
WHERE paydist.invoice_payment_id = l_sys_ref4_tbl(i)
AND paydist.invoice_distribution_id = l_sys_ref5_tbl(i)
AND paydist.pa_addition_flag = 'O';
UPDATE ap_invoice_distributions_all dist
SET dist.assets_addition_flag = decode(l_assets_addflag_tbl(i),'P','P',dist.assets_addition_flag)
WHERE dist.invoice_distribution_id = l_sys_ref5_tbl(i) ;
G_err_stage:= 'Failed during bulk update of payment tieback';
END bulk_update_txn_intf;
bulk_update_txn_intf(p_batch_type);
SELECT ei.expenditure_item_id
, ei.project_id project_id
, ei.task_id task_id
, ei.expenditure_item_date expenditure_item_date
, ei.expenditure_type expenditure_type
, ei.quantity quantity
, ei.raw_cost raw_cost
, nvl(ei.cost_distributed_flag,'N') cost_distributed_flag
, ei.organization_id organization_id
, ei.override_to_organization_id override_to_organization_id
, ei.receipt_currency_amount receipt_currency_amount
, ei.receipt_currency_code receipt_currency_code
, ei.receipt_exchange_rate receipt_exchange_rate
, ei.denom_raw_cost denom_raw_cost
, ei.denom_currency_code denom_currency_code
, ei.acct_rate_date acct_rate_date
, ei.acct_rate_type acct_rate_type
, ei.acct_exchange_rate acct_exchange_rate
, ei.acct_raw_cost acct_raw_cost
, ei.acct_exchange_rounding_limit acct_exchange_rounding_limit
, ei.attribute_category
, ei.attribute1
, ei.attribute2
, ei.attribute3
, ei.attribute4
, ei.attribute5
, ei.attribute6
, ei.attribute7
, ei.attribute8
, ei.attribute9
, ei.attribute10
, ei.org_id org_id
, get_cdl_ccid(ei.expenditure_item_id,'D') dr_code_combination_id
, get_cdl_ccid(ei.expenditure_item_id,'C') cr_code_combination_id
, Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(ei.project_id,'STD') orig_fc_enabled
, nvl(cdl.transfer_status_code,'P') transfer_status_code
,ei.document_type
,ei.document_distribution_type
,ei.document_header_id
,ei.document_distribution_id
,ei.document_payment_id
,ei.document_line_number
,cdl.system_reference5 cdl_sys_ref5 --to get the rcv_sub_leger_id of parent Rcv txn
FROM pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei
WHERE cdl.expenditure_item_id (+) = ei.expenditure_item_id
AND nvl(cdl.reversed_flag, 'N') = 'N'
AND ei.document_distribution_id = p_document_distribution_id /*Added this for 6945767 */
AND ei.system_linkage_function in ('VI','ER')
AND ei.document_header_id = p_document_header_id /*Added this for 6945767 */
AND nvl(cdl.line_type,'R') = 'R' --Bug 5373272 : 'C' and 'D' lines are incorrectly getting processed
AND cdl.line_num_reversed is null
AND (( p_record_type = 'AP_INVOICE'
AND ei.transaction_source in ('AP INVOICE','AP VARIANCE','AP EXPENSE','AP NRTAX'))
OR ( p_record_type = 'PO_RECEIPT'
AND ei.transaction_source IN ('PO RECEIPT', 'PO RECEIPT PRICE ADJ'))
OR ( p_record_type = 'PO_RECEIPT_TAX'
AND ei.transaction_source in ('PO RECEIPT NRTAX'))
OR ( p_record_type = 'AP_DISCOUNTS'
AND ei.document_payment_id = p_document_payment_id
AND ei.transaction_source in ('AP DISCOUNTS','AP INVOICE','AP NRTAX'))
OR ( p_record_type = 'AP_PAYMENT'
AND ei.document_payment_id = p_document_payment_id
AND ei.transaction_source in ('AP INVOICE','AP EXPENSE','AP NRTAX','AP VARIANCE')))
AND nvl(ei.net_zero_adjustment_flag,'N') <> 'Y'
ORDER BY ei.cost_distributed_flag, ei.expenditure_item_id;
l_insert_flag_tbl(p_current_index) := 'A';
l_insert_flag_tbl(j) := 'Y';
l_insert_flag_tbl(p_current_index) := 'U'; -- update adjustment record
IF l_insert_flag_tbl(p_current_index) <> 'U' THEN
write_log(LOG, 'Selecting the count of adjusted expenditures');
SELECT sum(decode(ei.net_zero_adjustment_flag,'N',1,0)),count(*)
INTO l_not_reversed_cnt,l_all_reversed_cnt
FROM pa_expenditure_items_all ei
/*Added this for 6945767 */
WHERE ei.document_distribution_id = p_document_distribution_id
AND ei.document_header_id = p_document_header_id /*Added this for 6945767 */
AND (( p_record_type = 'AP_INVOICE'
-- AND ei.document_distribution_id = p_document_distribution_id
AND ei.transaction_source in ('AP INVOICE','AP VARIANCE','AP EXPENSE','AP NRTAX'))
OR ( p_record_type = 'PO_RECEIPT'
--AND ei.document_distribution_id = p_document_distribution_id
AND ei.transaction_source IN ('PO RECEIPT', 'PO RECEIPT PRICE ADJ'))
OR ( p_record_type = 'PO_RECEIPT_TAX'
--AND ei.document_distribution_id = p_document_distribution_id
AND ei.transaction_source in ('PO RECEIPT NRTAX'))
OR ( p_record_type = 'AP_DISCOUNTS'
--AND ei.document_distribution_id = p_document_distribution_id
AND ei.document_payment_id = p_document_payment_id
AND ei.transaction_source in ('AP DISCOUNTS','AP INVOICE','AP NRTAX'))
OR ( p_record_type = 'AP_PAYMENT'
--AND ei.document_distribution_id = p_document_distribution_id
AND ei.document_payment_id = p_document_payment_id
AND ei.transaction_source in ('AP INVOICE','AP EXPENSE','AP VARIANCE','AP NRTAX'))) ;
l_insert_flag_tbl(p_current_index) := 'A';
SELECT 'X'
INTO l_Dummy
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM rcv_transactions rcv_txn2
,rcv_receiving_sub_ledger rcv_sub2
WHERE rcv_sub2.rcv_transaction_id = rcv_txn2.transaction_id
AND rcv_txn2.po_distribution_id = P_Po_Distribution_Id
AND rcv_sub2.pa_addition_flag = 'G'
UNION ALL
SELECT null
FROM ap_invoice_distributions apdist
WHERE apdist.po_distribution_id = P_Po_Distribution_Id
AND apdist.line_type_lookup_code in ('ITEM','ACCRUAL','NONREC_TAX')
AND apdist.pa_addition_flag = 'Y');