[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER DEFAULT NULL,
P_Program_Id IN NUMBER DEFAULT NULL,
P_Request_Id IN NUMBER DEFAULT NULL,
P_Awt_Success OUT NOCOPY VARCHAR2,
P_checkrun_id in number default null);
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER,
P_Program_Id IN NUMBER,
P_Request_Id IN NUMBER,
P_Calling_Sequence IN VARCHAR2,
P_Check_Id IN NUMBER DEFAULT NULL) -- 8590059
IS
withholding_total NUMBER := 0;
SELECT AATD.invoice_id
, AATD.payment_num
, AATD.group_id
, AATD.tax_name
, AATD.tax_code_combination_id
, AATD.gross_amount
, AATD.withholding_amount
, AATD.base_withholding_amount
, AATD.accounting_date
, AATD.period_name
, AATD.checkrun_name
, AATD.tax_rate_id
, AATD.invoice_payment_id
, TC.tax_id tax_code_id
, AATD.GLOBAL_ATTRIBUTE_CATEGORY
, AATD.GLOBAL_ATTRIBUTE1
, AATD.GLOBAL_ATTRIBUTE2
, AATD.GLOBAL_ATTRIBUTE3
, AATD.GLOBAL_ATTRIBUTE4
, AATD.GLOBAL_ATTRIBUTE5
, AATD.GLOBAL_ATTRIBUTE6
, AATD.GLOBAL_ATTRIBUTE7
, AATD.GLOBAL_ATTRIBUTE8
, AATD.GLOBAL_ATTRIBUTE9
, AATD.GLOBAL_ATTRIBUTE10
, AATD.GLOBAL_ATTRIBUTE11
, AATD.GLOBAL_ATTRIBUTE12
, AATD.GLOBAL_ATTRIBUTE13
, AATD.GLOBAL_ATTRIBUTE14
, AATD.GLOBAL_ATTRIBUTE15
, AATD.GLOBAL_ATTRIBUTE16
, AATD.GLOBAL_ATTRIBUTE17
, AATD.GLOBAL_ATTRIBUTE18
, AATD.GLOBAL_ATTRIBUTE19
, AATD.GLOBAL_ATTRIBUTE20
, AI.org_id
, AATD.awt_related_id
, aatd.checkrun_id
, TC.description --Bug5502917
FROM ap_awt_temp_distributions_all AATD,
ap_invoices_all AI,
ap_tax_codes_all TC,
ap_invoice_distributions_all AID --bug 7930936
WHERE AATD.invoice_id = InvId
AND AATD.group_id = GRP_ID
AND AATD.invoice_id = AI.invoice_id
AND TC.TAX_ID = TAXID
AND AATD.tax_name = TC.name(+)
AND TC.org_id = AI.org_id -- Bug5902006
AND TC.tax_type = 'AWT' -- Bug3665866
AND NVL(TC.enabled_flag,'Y') = 'Y'
AND ( P_Payment_Num IS NULL
OR AATD.payment_num = P_Payment_Num)
AND NVL(AI.invoice_date,SYSDATE) BETWEEN
NVL(TC.start_date,NVL(AI.invoice_date,SYSDATE)) AND
NVL(TC.inactive_date,NVL(AI.invoice_date,SYSDATE))
AND AATD.invoice_id = AID.invoice_id --bug 7930936
AND AATD.awt_related_id = AID.invoice_distribution_id --bug 7930936
AND AID.prepay_distribution_id is NULL --bug 7930936
ORDER BY AATD.tax_name,
AATD.tax_rate_id
FOR UPDATE of AATD.invoice_id;
SELECT AI.set_of_books_id
, AI.org_id --bug 8266021
, AI.accts_pay_code_combination_id
, AI.batch_id
, AI.description
, AI.invoice_amount
, NVL(AI.payment_cross_rate,1) payment_cross_rate
, AI.payment_currency_code
, AI.exchange_date
, NVL(AI.exchange_rate, 1) exchange_rate
, AI.exchange_rate_type
--, AI.ussgl_transaction_code - Bug 4277744
--, AI.ussgl_trx_code_context - Bug 4277744
, AI.vat_code
, NVL(PV.federal_reportable_flag, 'N') federal_reportable_flag
, AI.vendor_site_id vendor_site_id
, AI.amount_applicable_to_discount
FROM ap_invoices_all AI,
po_vendors PV
WHERE PV.vendor_id(+) = DECODE(AI.invoice_type_lookup_code,'PAYMENT REQUEST', NULL, AI.vendor_id) --bug8272564
AND AI.invoice_id = InvId
FOR UPDATE of AI.invoice_id;
SELECT AATD.group_id
, AATD.invoice_payment_id
, TC.TAX_ID
, SUM(AATD.withholding_amount) AMOUNT
, SUM(AATD.base_withholding_amount) BASE_AMOUNT
, MIN(AATD.accounting_date) ACCOUNTING_DATE
FROM ap_awt_temp_distributions_all AATD,
ap_invoices_all AI,
ap_tax_codes_all TC,
ap_invoice_distributions_all AID --bug 7930936
WHERE AATD.invoice_id = InvId
AND AATD.invoice_id = AI.invoice_id
AND AATD.tax_name = TC.name(+)
AND TC.org_id = AI.org_id -- Bug5902006
AND TC.tax_type = 'AWT' -- Bug3665866
AND NVL(TC.enabled_flag,'Y') = 'Y'
AND ( P_Payment_Num IS NULL
OR AATD.payment_num = P_Payment_Num)
AND NVL(AI.invoice_date,SYSDATE) BETWEEN
NVL(TC.start_date,NVL(AI.invoice_date,SYSDATE)) AND
NVL(TC.inactive_date,NVL(AI.invoice_date,SYSDATE))
AND AATD.invoice_id = AID.invoice_id --bug 7930936
AND AATD.awt_related_id = AID.invoice_distribution_id --bug 7930936
AND AID.prepay_distribution_id is NULL --bug 7930936
GROUP BY AATD.group_id,AATD.invoice_payment_id,TC.tax_id;
SELECT MAX(line_number) curr_inv_line_number
FROM ap_invoice_lines_all
WHERE (invoice_id = InvId);
SELECT AATD.*
FROM ap_awt_temp_distributions_all AATD,
ap_invoice_distributions_all AID
WHERE AATD.invoice_id = InvId
AND AATD.invoice_id = AID.invoice_id
AND AATD.awt_related_id = AID.invoice_distribution_id
AND AID.prepay_distribution_id is NULL;
/* bug 7930936 added the above cursor to include to select all non prepay awt distributions
against which the prepay awt amount should be prorated*/
l_prepay_awt_amount NUMBER; -- bug7930936
SELECT NVL(enable_1099_on_awt_flag, 'N'),
combined_filing_flag,
income_tax_region_flag,
income_tax_region,
base_currency_code
INTO l_enable_1099_on_awt_flag,
l_combined_filing_flag,
l_income_tax_region_flag,
l_income_tax_region_asp,
l_basecur
FROM ap_system_parameters_all asp,
ap_invoices_all ai
WHERE ai.org_id = asp.org_id
and ai.invoice_id = p_invoice_id;
SELECT nvl(sum(AATD.withholding_amount),0),nvl(sum(AATD.base_withholding_amount),0)
INTO l_prepay_awt_amount,l_prepay_awt_base_amount
FROM ap_awt_temp_distributions_all AATD,
ap_invoice_distributions_all AID
WHERE AATD.invoice_id = P_Invoice_Id
AND AATD.invoice_id = AID.invoice_id
AND AATD.awt_related_id = AID.invoice_distribution_id
AND AID.prepay_distribution_id is not NULL;
/* bug 7930936 The above query will select the total prepay awt amount from
ap_awt_temp_distributions table and this will be prorated against other
non prepay awt distributions */
SELECT sum(AATD.withholding_amount),sum(AATD.base_withholding_amount)
INTO l_non_prepay_awt_amount,l_non_prepay_awt_base_amount
FROM ap_awt_temp_distributions_all AATD,
ap_invoice_distributions_all AID
WHERE AATD.invoice_id = P_Invoice_Id
AND AATD.invoice_id = AID.invoice_id
AND AATD.awt_related_id = AID.invoice_distribution_id
AND AID.prepay_distribution_id is NULL;
/* bug 7930936 The above query will select the total non prepay awt amount from
ap_awt_temp_distributions table and this will be used in the proration formula */
debug_info := 'l_prepay_awt_amount -- '||l_prepay_awt_amount;
update ap_awt_temp_distributions_all
set withholding_amount = withholding_amount + l_pro_prepay_awt_amt,
base_withholding_amount = base_withholding_amount + l_pro_prepay_awt_base_amt
where invoice_id = rec_nonprepay_awt.invoice_id
and awt_related_id = rec_nonprepay_awt.awt_related_id
and tax_rate_id = rec_nonprepay_awt.tax_rate_id;
update ap_awt_temp_distributions_all
set withholding_amount = withholding_amount + l_amt_diff,
base_withholding_amount = base_withholding_amount + l_base_amt_diff
where invoice_id = P_INVOICE_ID
and awt_related_id = l_awt_related_id
and tax_rate_id = l_tax_rate_id;
SELECT SUBSTR(state, 1, 10)
INTO l_income_tax_region
FROM po_vendor_sites_all
WHERE vendor_site_id = rec_invoice.vendor_site_id
AND NVL(tax_reporting_site_flag, 'N') = 'Y';
SELECT DISTINCT gps.Period_Name
INTO l_period_name
FROM gl_Period_Statuses gps,
ap_System_Parameters_All Asp
WHERE gps.Application_Id = 200
AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
AND rec_temp_lines.accounting_date BETWEEN Trunc(gps.Start_Date)
AND Trunc(gps.End_Date)
AND Nvl(Asp.Org_Id,- 99) = Nvl(rec_invoice.org_id,- 99)
AND gps.closing_Status in ('O', 'F');
SELECT exchange_rate
INTO l_exchange_rate
FROM ap_checks_all
WHERE check_id = P_Check_Id;
SELECT DISTINCT payment_exchange_rate /* Bug 9666111 added distinct */
INTO l_exchange_rate
FROM ap_selected_invoices_all
WHERE invoice_id = P_Invoice_Id;
debug_info := 'Insert INTO ap_invoice_lines_all';
INSERT INTO AP_INVOICE_LINES_all (
invoice_id,
line_number,
line_type_lookup_code,
description,
line_source,
generate_dists,
match_type,
prorate_across_all_items,
accounting_date,
period_name,
deferred_acctg_flag,
set_of_books_id,
amount,
base_amount,
rounding_amt,
wfapproval_status,
-- ussgl_transaction_code, - Bug 4277744
discarded_flag,
cancelled_flag,
income_tax_region,
type_1099,
final_match_flag,
assets_tracking_flag,
awt_group_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_UPDATE_date,
request_id,
org_id, --7230158
pay_awt_group_id) --7230158
VALUES
( P_Invoice_ID,
curr_inv_line_number,
'AWT',
rec_invoice.description,
'AUTO WITHHOLDING',
'D',
'NOT_MATCHED',
'N',
rec_temp_lines.accounting_date,
l_period_name,
'N',
rec_invoice.set_of_books_id,
ap_utilities_pkg.ap_round_currency(
l_withhold_amount, -- bug 8726501
p_currency_code), -- bug 8590059
ap_utilities_pkg.ap_round_currency(
-rec_temp_lines.base_amount,
l_basecur),
0,
'NOT REQUIRED', /*bug 4994642, was 'NOT_REQUIRED' */
-- rec_invoice.ussgl_transaction_code, - Bug 4277744
'N',
'N',
l_income_tax_region,
l_type_1099,
'N',
'N',
decode (rec_temp_lines.invoice_payment_id,NULL, rec_temp_lines.group_id,NULL), --7230158,
SYSDATE,
P_Last_Updated_By,
SYSDATE,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_ID,
P_Program_ID,
SYSDATE,
P_request_ID,
rec_invoice.org_id, --7230158
decode (rec_temp_lines.invoice_payment_id,NULL,NULL,rec_temp_lines.group_id)); --7230158
debug_info := 'Insert INTO ap_invoice_distributions';
INSERT INTO ap_invoice_distributions_all (
accounting_date
,accrual_posted_flag
,assets_addition_flag
,assets_tracking_flag
,cash_posted_flag
,distribution_line_number
,dist_code_combination_id
,invoice_id
,invoice_line_number
,last_updated_by
,last_update_date
,line_type_lookup_code
,period_name
,set_of_books_id
,amount
,base_amount
,batch_id
,created_by
,creation_date
,description
,last_update_login
,match_status_flag
,posted_flag
,program_application_id
,program_id
,program_UPDATE_date
,request_id
,withholding_tax_code_id /* Bug 5382525 */
,encumbered_flag
,pa_addition_flag
,posted_amount
,posted_base_amount
-- ,ussgl_transaction_code - Bug 4277744
-- ,ussgl_trx_code_context - Bug 4277744
,awt_flag
,awt_tax_rate_id
,awt_gross_amount
,awt_origin_group_id
,awt_invoice_payment_id
,invoice_distribution_id
,GLOBAL_ATTRIBUTE_CATEGORY
,GLOBAL_ATTRIBUTE1
,GLOBAL_ATTRIBUTE2
,GLOBAL_ATTRIBUTE3
,GLOBAL_ATTRIBUTE4
,GLOBAL_ATTRIBUTE5
,GLOBAL_ATTRIBUTE6
,GLOBAL_ATTRIBUTE7
,GLOBAL_ATTRIBUTE8
,GLOBAL_ATTRIBUTE9
,GLOBAL_ATTRIBUTE10
,GLOBAL_ATTRIBUTE11
,GLOBAL_ATTRIBUTE12
,GLOBAL_ATTRIBUTE13
,GLOBAL_ATTRIBUTE14
,GLOBAL_ATTRIBUTE15
,GLOBAL_ATTRIBUTE16
,GLOBAL_ATTRIBUTE17
,GLOBAL_ATTRIBUTE18
,GLOBAL_ATTRIBUTE19
,GLOBAL_ATTRIBUTE20
,type_1099
,income_tax_region
,org_id
,awt_related_id
--Freight and Special Charges
,rcv_charge_addition_flag
,distribution_class -- bug 8620272
)
VALUES
(
rec_temp_dists.accounting_date
,'N'
,'N'
,'N'
,'N'
,curr_inv_dist_line_number -- distribution_line_number
,rec_temp_dists.tax_code_combination_id
,P_Invoice_Id
,curr_inv_line_number -- invoice_line_number
,P_Last_Updated_By
,SYSDATE
,'AWT'
,rec_temp_dists.period_name
,rec_invoice.set_of_books_id
,ap_utilities_pkg.ap_round_currency(
l_withhold_amount, -- bug 8726501
p_currency_code) -- bug 8590059
,ap_utilities_pkg.ap_round_currency(-rec_temp_dists.base_withholding_amount,
l_basecur)
,rec_invoice.batch_id
,P_Last_Updated_By
,SYSDATE
,rec_temp_dists.description --Bug5502917 Replaced rec_invoice.description
,P_Last_Update_Login
,decode (P_Calling_Module, 'INVOICE ENTRY','N',
'INVOICE INQUIRY','N',
'A')
,'N'
,P_Program_Application_Id
,P_Program_Id
,decode (P_Program_Id,NULL,NULL,SYSDATE)
,P_Request_Id
,rec_temp_dists.tax_code_id
,'T'
,'E'
,0
,0
-- ,rec_invoice.ussgl_transaction_code - Bug 4277744
-- ,rec_invoice.ussgl_trx_code_context - Bug 4277744
,decode (P_Calling_Module, 'AWT REPORT', 'P',
'A')
,rec_temp_dists.tax_rate_id
,ap_utilities_pkg.ap_round_currency(
rec_temp_dists.gross_amount/nvl(l_exchange_rate,1), --bug 8590059
P_currency_code)
,rec_temp_dists.group_id
,rec_temp_dists.invoice_payment_id
,ap_invoice_distributions_s.nextval
,rec_temp_dists.GLOBAL_ATTRIBUTE_CATEGORY
,rec_temp_dists.GLOBAL_ATTRIBUTE1
,rec_temp_dists.GLOBAL_ATTRIBUTE2
,rec_temp_dists.GLOBAL_ATTRIBUTE3
,rec_temp_dists.GLOBAL_ATTRIBUTE4
,rec_temp_dists.GLOBAL_ATTRIBUTE5
,rec_temp_dists.GLOBAL_ATTRIBUTE6
,rec_temp_dists.GLOBAL_ATTRIBUTE7
,rec_temp_dists.GLOBAL_ATTRIBUTE8
,rec_temp_dists.GLOBAL_ATTRIBUTE9
,rec_temp_dists.GLOBAL_ATTRIBUTE10
,rec_temp_dists.GLOBAL_ATTRIBUTE11
,rec_temp_dists.GLOBAL_ATTRIBUTE12
,rec_temp_dists.GLOBAL_ATTRIBUTE13
,rec_temp_dists.GLOBAL_ATTRIBUTE14
,rec_temp_dists.GLOBAL_ATTRIBUTE15
,rec_temp_dists.GLOBAL_ATTRIBUTE16
,rec_temp_dists.GLOBAL_ATTRIBUTE17
,rec_temp_dists.GLOBAL_ATTRIBUTE18
,rec_temp_dists.GLOBAL_ATTRIBUTE19
,rec_temp_dists.GLOBAL_ATTRIBUTE20
,l_type_1099
,l_income_tax_region
,rec_temp_dists.org_id
,rec_temp_dists.awt_related_id
,'N'
,'PERMANENT' -- distribution_class bug 8620272
);
SELECT max(aid.invoice_distribution_id)
INTO l_dist_id_to_round
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = P_Invoice_Id
AND aid.line_type_lookup_code = 'AWT'
AND abs(aid.amount) =
(SELECT max(abs(aid1.amount))
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = P_Invoice_Id
AND aid1.invoice_line_number = curr_inv_line_number
AND aid1.line_type_lookup_code = 'AWT');
UPDATE ap_invoice_distributions_all aid
SET aid.amount = aid.amount + l_round_amt,
aid.base_amount = aid.base_amount + l_round_base_amt
WHERE aid.invoice_id = P_Invoice_Id
AND aid.invoice_distribution_id = l_dist_id_to_round;
debug_info := 'Delete From ap_awt_temp_distributions';
DELETE ap_awt_temp_distributions_all
WHERE invoice_id = p_invoice_id
AND (P_Payment_Num IS NULL OR payment_num = P_Payment_Num);
SELECT AI.Exclude_Freight_From_Discount
INTO l_exclude_freight_from_disc
FROM AP_Invoices_All AI
WHERE AI.Invoice_ID = P_Invoice_Id;
SELECT NVL(SUM(AID_AWT.Amount),0)
INTO l_sub_withhold_amt
FROM AP_Invoice_Distributions_All AID_FRE,
AP_Invoice_Distributions_All AID_AWT
WHERE AID_FRE.Invoice_ID = P_Invoice_Id
AND AID_FRE.Invoice_ID = AID_AWT.Invoice_ID
AND AID_FRE.Line_Type_Lookup_Code = 'FREIGHT'
AND AID_AWT.Line_Type_Lookup_code = 'AWT'
AND AID_FRE.Invoice_Distribution_ID = AID_AWT.Awt_Related_ID;
<>
DECLARE
--Bug7707630:Cursor c_payment_sched: Added decode for BOTH
CURSOR c_payment_sched --bug6660355
(Createdists IN VARCHAR2
,PaymNum IN NUMBER
,InvId IN NUMBER
) IS
SELECT gross_amount
, amount_remaining
, NVL(inv_curr_gross_amount, gross_Amount) inv_curr_gross_amount
FROM ap_payment_schedules_all
WHERE (invoice_id = InvId)
AND (payment_num = decode(Createdists
,'APPROVAL',payment_num, 'BOTH',
decode(P_Calling_Module,'CONFIRM',PaymNum,'QUICKCHECK',PaymNum,payment_num)
,PaymNum
))
FOR UPDATE of amount_remaining;
DBG_Loc VARCHAR2(30) := 'Update_Payment_Schedules';
SELECT count(*) payments
FROM ap_payment_schedules_all
WHERE invoice_id = InvId;
debug_info := 'Update current payment schedule';
UPDATE ap_payment_schedules_all
SET amount_remaining = amount_remaining -
pay_curr_amount_to_subtract,
-- iyas: Following code IS in DLD but was not found originally in file:
discount_amount_available = discount_amount_available -
ap_utilities_pkg.ap_round_currency(
discount_amount_available * l_disc_amt_factor,
rec_invoice.payment_currency_code),
second_disc_amt_available = second_disc_amt_available -
ap_utilities_pkg.ap_round_currency(
second_disc_amt_available * l_disc_amt_factor,
rec_invoice.payment_currency_code) ,
third_disc_amt_available = third_disc_amt_available -
ap_utilities_pkg.ap_round_currency(
third_disc_amt_available * l_disc_amt_factor,
rec_invoice.payment_currency_code)
WHERE CURRENT of c_payment_sched;
debug_info := 'Update current payment schedule';
UPDATE ap_payment_schedules_all
SET amount_remaining = (amount_remaining -
ap_utilities_pkg.ap_round_currency(
withholding_total * rec_invoice.payment_cross_rate,
rec_invoice.payment_currency_code))
WHERE current of c_payment_sched;
END Update_Payment_Schedules;
<>
debug_info := 'Update ap_invoices';
UPDATE ap_invoices_all
SET awt_flag = DECODE(P_Create_dists, 'APPROVAL', 'Y','BOTH','Y', NULL), --Bug6660355
amount_applicable_to_discount = decode (sign(invoice_amount),
-1, amount_applicable_to_discount,
amount_applicable_to_discount
- withholding_total - l_sub_withhold_amt) -- 10183587
WHERE CURRENT OF c_invoice;
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER,
P_Program_Id IN NUMBER,
P_Request_Id IN NUMBER,
P_Calling_Sequence IN VARCHAR2,
P_Calling_Module IN VARCHAR2 DEFAULT NULL, --Bug6660355 -- bug 8266021
P_Inv_Line_No IN NUMBER DEFAULT NULL,
P_Dist_Line_No IN NUMBER DEFAULT NULL,
P_New_Invoice_Id IN NUMBER DEFAULT NULL,
P_create_dists IN VARCHAR2 DEFAULT NULL) --Bug7685907 bug8207324 bug8236169
IS
new_invoice_id ap_invoices.invoice_id%TYPE;
SELECT APID.accounting_date accounting_date
, APID.invoice_line_number invoice_line_number
, APID.distribution_line_number distribution_line_number
, APID.set_of_books_id set_of_books_id
, APID.dist_code_combination_id dist_code_combination_id
, APID.period_name period_name
, APID.withholding_tax_code_id tax_code_id /* Bug 5382525 */
, APID.amount amount
, APID.base_amount base_amount
, APID.batch_id batch_id
--, APID.ussgl_transaction_code ussgl_transaction_code - Bug 4277744
--, APID.ussgl_trx_code_context ussgl_trx_code_context - Bug 4277744
, APID.org_id
, APID.awt_related_id awt_related_id -- bug 9913164
FROM ap_invoice_distributions_all APID,
ap_invoice_distributions_all APID1,
ap_tax_codes_all ATC,
ap_invoices_all AI
WHERE (APID.invoice_id = InvId)
AND (APID.invoice_line_number = NVL(P_Inv_Line_No,line_num))
AND (APID.distribution_line_number = NVL(P_dist_Line_No,APID.distribution_line_number))
AND (APID.line_type_lookup_code = 'AWT')
AND APID.invoice_id = APID1.invoice_id
AND APID.awt_related_id = APID1.invoice_distribution_id
AND ((APID.awt_invoice_id IS NULL)
OR (APID.awt_invoice_id = P_New_Invoice_Id))
AND (NVL(APID.awt_flag , 'M' ) = 'A' )
AND APID.invoice_id = AI.invoice_id
AND APID.WITHHOLDING_TAX_CODE_ID = ATC.tax_id /* Bug 5382525 */
AND APID.base_amount <> decode (NVL(ATC.suppress_zero_amount_flag,
'N'), 'Y', 0 , APID.base_amount +1) /*Bug 14491356*/
AND NVL(APID.reversal_flag, 'N') <> 'Y'
AND
(
APID.AWT_ORIGIN_GROUP_ID = nvl(DECODE(P_calling_module,'AUTOAPPROVAL',APID1.awt_group_id,
'CANCEL INVOICE',APID1.awt_group_id,'REVERSE DIST',APID1.awt_group_id,
'CONFIRM',DECODE(P_create_dists,'APPROVAL',
APID1.awt_group_id, APID1.pay_awt_group_id),
'QUICKCHECK', DECODE(P_create_dists,'APPROVAL',
APID1.awt_group_id,APID1.pay_awt_group_id), APID1.pay_awt_group_id),-1) --6660355 --9093973
--Bug 7685907 Added Decode for Confirm and Quickcheck
or
APID.AWT_ORIGIN_GROUP_ID = - 1 --bug13999969
)
FOR UPDATE of APID.awt_invoice_id;
SELECT APID.accounting_date accounting_date
, APID.invoice_line_number invoice_line_number
, APID.distribution_line_number distribution_line_number
, APID.set_of_books_id set_of_books_id
, APID.dist_code_combination_id dist_code_combination_id
, APID.period_name period_name
, APID.withholding_tax_code_id tax_code_id /* Bug 5382525 */
, APID.amount amount
, APID.base_amount base_amount
, APID.batch_id batch_id
--, APID.ussgl_transaction_code ussgl_transaction_code - Bug 4277744
--, APID.ussgl_trx_code_context ussgl_trx_code_context - Bug 4277744
, APID.org_id
, APID.awt_related_id awt_related_id -- bug 9913164
FROM ap_invoice_distributions_all APID,
ap_invoice_distributions_all APID1,
ap_tax_codes_all ATC,
ap_invoices_all AI
WHERE (APID.invoice_id = InvId)
AND (APID.invoice_line_number = NVL(P_Inv_Line_No,line_num))
AND (APID.distribution_line_number = NVL(P_dist_Line_No,APID.distribution_line_number))
AND (APID.line_type_lookup_code = 'AWT')
AND APID.invoice_id = APID1.invoice_id
AND APID.awt_related_id = APID1.invoice_distribution_id
AND ((APID.awt_invoice_id IS NULL)
OR (APID.awt_invoice_id = P_New_Invoice_Id)
)
AND (NVL(APID.awt_flag , 'M' ) = 'A' )
AND APID.invoice_id = AI.invoice_id
AND APID.WITHHOLDING_TAX_CODE_ID = ATC.tax_id /* Bug 5382525 */
AND APID.base_amount <> decode (NVL(ATC.suppress_zero_amount_flag,
'N'), 'Y', 0 , APID.base_amount +1) /*Bug 14491356*/
AND nvl(APID.parent_reversal_id,-99) <> -99
AND NVL(APID.reversal_flag, 'N') = 'Y'
AND
(
APID.AWT_ORIGIN_GROUP_ID = nvl(DECODE(P_calling_module,'AUTOAPPROVAL',APID1.awt_group_id,
'CANCEL INVOICE',APID1.awt_group_id,'REVERSE DIST',APID1.awt_group_id,
'CONFIRM',DECODE(P_create_dists,'APPROVAL',
APID1.awt_group_id, APID1.pay_awt_group_id),
'QUICKCHECK', DECODE(P_create_dists,'APPROVAL',
APID1.awt_group_id,APID1.pay_awt_group_id), APID1.pay_awt_group_id),-1) --6660355 --9093973
--Bug 7685907 Added Decode for Confirm and Quickcheck
or
APID.AWT_ORIGIN_GROUP_ID = - 1 --bug13999969
)
FOR UPDATE of APID.awt_invoice_id;
SELECT min(APID.accounting_date) accounting_date
, APID.withholding_tax_code_id tax_code_id
, sum(-1 * NVL(APID.base_amount,APID.amount)) invoice_amount --bug 8597105
, APID.invoice_line_number
FROM ap_invoice_distributions_all APID,
ap_invoice_distributions_all APID1,
ap_tax_codes_all ATC,
AP_INVOICES_ALL AI
WHERE (APID.invoice_id = InvId)
AND (APID.line_type_lookup_code = 'AWT')
AND (NVL(APID.awt_flag , 'M' ) = 'A' )
AND APID.WITHHOLDING_TAX_CODE_ID = ATC.tax_id
AND APID.invoice_id = APID1.invoice_id
--AND (APID.invoice_line_number = NVL(P_Inv_Line_No,APID.invoice_line_number))
AND APID.awt_related_id = APID1.invoice_distribution_id
AND APID.base_amount <> decode (NVL(ATC.suppress_zero_amount_flag,
'N'), 'Y', 0 , APID.base_amount +1) /*Bug 14491356*/
AND NVL(APID.reversal_flag, 'N') <> 'Y'
AND APID.invoice_id = AI.invoice_id
AND ((APID.awt_invoice_id IS NULL)
OR (APID.awt_invoice_id = P_New_Invoice_Id)
) --bug 8659829
AND
(
APID.AWT_ORIGIN_GROUP_ID = nvl(DECODE(P_calling_module,'AUTOAPPROVAL',APID1.awt_group_id,
'CANCEL INVOICE',APID1.awt_group_id,'REVERSE DIST',APID1.awt_group_id,
'CONFIRM',DECODE(P_create_dists,'APPROVAL',
APID1.awt_group_id, APID1.pay_awt_group_id),
'QUICKCHECK', DECODE(P_create_dists,'APPROVAL',
APID1.awt_group_id,APID1.pay_awt_group_id), APID1.pay_awt_group_id),-1) --9093973
or
APID.AWT_ORIGIN_GROUP_ID = - 1 --bug13999969
)
GROUP By APID.withholding_tax_code_id
,APID.invoice_line_number;
SELECT min(APID.accounting_date) accounting_date
, APID.withholding_tax_code_id tax_code_id
, sum(-1 * NVL(APID.base_amount,APID.amount)) invoice_amount --bug 8597105
, APID.invoice_line_number
FROM ap_invoice_distributions_all APID,
ap_invoice_distributions_all APID1,
ap_tax_codes_all ATC,
AP_INVOICES_ALL AI
WHERE (APID.invoice_id = InvId)
AND (APID.line_type_lookup_code = 'AWT')
AND (NVL(APID.awt_flag , 'M' ) = 'A' )
AND APID.WITHHOLDING_TAX_CODE_ID = ATC.tax_id
AND APID.invoice_id = APID1.invoice_id
--AND (APID.invoice_line_number = NVL(P_Inv_Line_No,APID.invoice_line_number))
AND APID.awt_related_id = APID1.invoice_distribution_id
AND APID.base_amount <> decode (NVL(ATC.suppress_zero_amount_flag,
'N'), 'Y', 0 , APID.base_amount +1) /*Bug 14491356*/
AND NVL(APID.reversal_flag, 'N') = 'Y'
AND nvl(APID.parent_reversal_id,-99) <> -99
AND APID.invoice_id = AI.invoice_id
AND ((APID.awt_invoice_id IS NULL)
OR (APID.awt_invoice_id = P_New_Invoice_Id)
) --bug 8659829
AND
(
APID.AWT_ORIGIN_GROUP_ID = nvl(DECODE(P_calling_module,'AUTOAPPROVAL',APID1.awt_group_id,
'CANCEL INVOICE',APID1.awt_group_id,'REVERSE DIST',APID1.awt_group_id,
'CONFIRM',DECODE(P_create_dists,'APPROVAL',
APID1.awt_group_id, APID1.pay_awt_group_id),
'QUICKCHECK', DECODE(P_create_dists,'APPROVAL',
APID1.awt_group_id,APID1.pay_awt_group_id), APID1.pay_awt_group_id),-1) --9093973
or
APID.AWT_ORIGIN_GROUP_ID = - 1 --bug13999969
)
GROUP By APID.withholding_tax_code_id
,APID.invoice_line_number;
SELECT substrb(
substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION' , 'AWT'),1,25)||
' - '||
v.vendor_name||
' - '||
i.invoice_num||
' /' --4940604
,1
, 234
) description,
i.legal_entity_id,
i.set_of_books_id,
i.batch_id,
i.org_id
FROM po_vendors v
, ap_invoices_all i
WHERE (v.vendor_id = i.vendor_id)
AND (i.invoice_id = InvId);
SELECT t.awt_vendor_id,
t.awt_vendor_site_id,
NVL(s.payment_currency_code, s.invoice_currency_code),
NVL(P_New_Invoice_Id, ap_invoices_s.nextval),
p.base_currency_code,
s.terms_id,
s.payment_priority,
s.terms_date_basis,
s.pay_group_lookup_code,
s.accts_pay_code_combination_id,
s.party_site_id,
pv.party_id
FROM ap_tax_codes_all t,
ap_system_parameters_all p,
po_vendor_sites_all s,
po_vendors pv
WHERE t.tax_id = TaxId
AND pv.vendor_id = s.vendor_id /* Bug 4724120 */
AND s.vendor_id = t.awt_vendor_id
AND s.vendor_site_id = t.awt_vendor_site_id
AND p.org_id = t.org_id;
select vendor_id, vendor_name into l_remit_to_supplier_id, l_remit_to_supplier_name
from ap_suppliers where party_id = l_remit_party_id and rownum<2;
select party_site_id, vendor_site_code into l_remit_to_party_site_id,
l_remit_to_supplier_site from ap_supplier_sites where vendor_site_id = l_remit_to_supplier_site_id
and rownum<2;
SELECT invoice_received_date,
goods_received_date
INTO invoice_received_date,
goods_received_date
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
SELECT DISTINCT gps.Period_Name,
automatic_offsets_flag
INTO l_period_name,
l_automatic_offsets
FROM gl_Period_Statuses gps,
ap_System_Parameters_All Asp
WHERE gps.Application_Id = 200
AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
AND rec_awt_invs.accounting_date BETWEEN Trunc(gps.Start_Date)
AND Trunc(gps.End_Date)
AND Nvl(Asp.Org_Id,- 99) = Nvl(l_org_id,- 99);
debug_info := 'Insert Into ap_invoices';
INSERT INTO ap_invoices_all
(invoice_id
,last_UPDATE_date
,last_UPDATEd_by
,vendor_id
,invoice_num
,set_of_books_id
,invoice_currency_code
,payment_currency_code
,payment_cross_rate
,invoice_amount
,pay_curr_invoice_amount
,payment_cross_rate_type
,payment_cross_rate_date
,vendor_site_id
,amount_paid
,discount_amount_taken
,invoice_date
,source
,invoice_type_lookup_code
,description
,batch_id
,amount_applicable_to_discount
,terms_id
,terms_date
,pay_group_lookup_code
,accts_pay_code_combination_id
,payment_status_flag
,creation_date
,created_by
,last_UPDATE_login
,doc_sequence_id
,doc_sequence_value
,doc_category_code
,posting_status
-- ,ussgl_transaction_code - Bug 4277744
-- ,ussgl_trx_code_context - Bug 4277744
,payment_amount_total
,gl_date
,approval_ready_flag
,wfapproval_status
,org_id
,legal_entity_id
,auto_tax_calc_flag -- BUG 3007085
,PAYMENT_METHOD_CODE
,PAYMENT_REASON_CODE
,BANK_CHARGE_BEARER
,DELIVERY_CHANNEL_CODE
,SETTLEMENT_PRIORITY
,exclusive_payment_flag
,external_bank_account_id
,party_id
,party_site_id
,payment_reason_comments
--bug 7699166 changes for Third Party Payments
,remit_to_supplier_name
,remit_to_supplier_id
,remit_to_supplier_site
,remit_to_supplier_site_id
,relationship_id
--bug 7699166
)
VALUES
(new_invoice_id
,SYSDATE
,5
,tax_authority_id
,DECODE( p_calling_sequence, 'AP_WITHHOLDING_PKG.AP_Undo_Withholding',
substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION', 'AWT'),1,25)
||' - '||to_char(P_invoice_id)||' - ' || to_char(rec_awt_invs.invoice_line_number)
|| ' - ' || Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION','CANCELLED'),
substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION', 'AWT'),1,25)||
' - '||to_char(P_invoice_id)||' - ' || to_char(rec_awt_invs.invoice_line_number)
)
,l_set_of_books_id
,base_currency
,ta_payment_currency_code
,c_payment_cross_rate
,rec_awt_invs.invoice_amount
,gl_currency_api.convert_amount(
base_currency,
ta_payment_currency_code,
rec_awt_invs.accounting_date,
c_payment_cross_rate_type,
rec_awt_invs.invoice_amount)
,c_payment_cross_rate_type
,rec_awt_invs.accounting_date
,tax_authority_site_id
,0
,0
,NVL(P_Payment_Date,rec_awt_invs.accounting_date)
,substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION', 'AWT'),1,25)
,'AWT'
,new_invoice_base_descr
,l_batch_id
,decode(sign(rec_awt_invs.invoice_amount),
-1, 0, rec_awt_invs.invoice_amount)
,ta_terms_id
,decode(ta_terms_date_basIS
,'Current', SYSDATE
,'Invoice', NVL(p_payment_date,
rec_awt_invs.accounting_date)
,'Goods Received', NVL(goods_received_date,
rec_awt_invs.accounting_date)
,'Invoice Received', NVL(invoice_received_date,
rec_awt_invs.accounting_date)
,NULL)
,ta_pay_group_lookup_code
,ta_accts_pay_code_comb_id
,'N'
,SYSDATE
,5
,P_Last_Update_Login
,NULL
,NULL
,NULL
,'N'
-- ,rec_awt_lines.ussgl_transaction_code - Bug 4277744
-- ,rec_awt_lines.ussgl_trx_code_context - Bug 4277744
,NULL
,NVL(P_Payment_Date,rec_awt_invs.accounting_date)
,'Y'
,'NOT REQUIRED'
,l_org_id
,l_legal_entity_id
,'N' -- BUG 3007085
,nvl(l_payment_method_code,'CHECK')
,l_payment_reason_code
,l_bank_charge_bearer
,l_delivery_channel_code
,l_settlement_priority
,l_exclusive_payment_flag
,l_external_bank_account_id
,l_party_id
,l_party_site_id
,l_payment_reason_comments --4874927
--bug 7699166 changes for Third Party Payments
,l_remit_to_supplier_name
,l_remit_to_supplier_id
,l_remit_to_supplier_site
,l_remit_to_supplier_site_id
,l_relationship_id
--bug 7699166
);
debug_info := 'Insert INTO ap_invoice_lines_all';
INSERT INTO AP_INVOICE_LINES_all (
invoice_id,
line_number,
line_type_lookup_code,
description,
line_source,
generate_dists,
match_type,
prorate_across_all_items,
accounting_date,
period_name,
deferred_acctg_flag,
set_of_books_id,
amount,
base_amount,
rounding_amt,
wfapproval_status,
-- ussgl_transaction_code, - Bug 4277744
discarded_flag,
cancelled_flag,
final_match_flag,
assets_tracking_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
org_id
)
VALUES
(
new_invoice_id,
1,
'ITEM'
,new_invoice_base_descr||to_char(rec_awt_invs.invoice_line_number),
'AUTO INVOICE CREATION',
'D',
'NOT MATCHED',
'N',
NVL(P_Payment_Date,rec_awt_invs.accounting_date),
NVL(ap_utilities_pkg.get_current_gl_date(P_Payment_Date, l_org_id),
l_period_name),
'N',
l_set_of_books_id,
rec_awt_invs.invoice_amount,
null, -- bug 5190989
0,
'NOT REQUIRED',
-- rec_awt_lines.ussgl_transaction_code, - Bug 4277744
'N',
'N',
'N',
'N',
SYSDATE,
P_Last_Updated_By,
SYSDATE,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_ID,
P_Program_ID,
SYSDATE,
P_request_ID,
l_org_id);
debug_info := 'Insert INTO ap_invoice_distributions';
SELECT dist_code_combination_id
INTO l_dist_code_ccid
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = rec_awt_lines.awt_related_id;
SELECT ap_invoice_distributions_s.nextval
INTO l_invoice_distribution_id
FROM DUAL;
INSERT INTO ap_invoice_distributions_all (
accounting_date
,accrual_posted_flag
,assets_addition_flag
,assets_tracking_flag
,cash_posted_flag
,distribution_line_number
,dist_code_combination_id
,invoice_id
,invoice_line_number
,last_updated_by
,last_update_date
,line_type_lookup_code
,period_name
,set_of_books_id
,amount
,base_amount
,batch_id
,created_by
,creation_date
,description
,last_update_login
,match_status_flag
,posted_flag
,program_application_id
,program_id
,program_UPDATE_date
,request_id
,tax_code_id
,encumbered_flag
,pa_addition_flag
,posted_amount
,posted_base_amount
,awt_flag
,awt_tax_rate_id
,awt_gross_amount
,awt_origin_group_id
,awt_invoice_payment_id
,invoice_distribution_id
,GLOBAL_ATTRIBUTE_CATEGORY
,GLOBAL_ATTRIBUTE1
,GLOBAL_ATTRIBUTE2
,GLOBAL_ATTRIBUTE3
,GLOBAL_ATTRIBUTE4
,GLOBAL_ATTRIBUTE5
,GLOBAL_ATTRIBUTE6
,GLOBAL_ATTRIBUTE7
,GLOBAL_ATTRIBUTE8
,GLOBAL_ATTRIBUTE9
,GLOBAL_ATTRIBUTE10
,GLOBAL_ATTRIBUTE11
,GLOBAL_ATTRIBUTE12
,GLOBAL_ATTRIBUTE13
,GLOBAL_ATTRIBUTE14
,GLOBAL_ATTRIBUTE15
,GLOBAL_ATTRIBUTE16
,GLOBAL_ATTRIBUTE17
,GLOBAL_ATTRIBUTE18
,GLOBAL_ATTRIBUTE19
,GLOBAL_ATTRIBUTE20
,type_1099
,income_tax_region
,org_id
,awt_related_id
--Freight and Special Charges
,rcv_charge_addition_flag
,distribution_class) --bug7719929
VALUES
(
NVL(P_Payment_Date,rec_awt_lines.accounting_date)
,'N'
,'N'
,'N'
,'N'
,l_dist_number -- distribution_line_number
/* ,rec_awt_lines.dist_code_combination_id */ --bug 9913164
,nvl(l_overlayed_awt_ccid,rec_awt_lines.dist_code_combination_id) --bug 9913164 --bug 10050107
,new_Invoice_Id
,1 -- invoice_line_number
,P_Last_Updated_By
,SYSDATE
,'ITEM'
, NVL(ap_utilities_pkg.get_current_gl_date(P_Payment_Date, rec_awt_lines.org_id),
rec_awt_lines.period_name)
,rec_awt_lines.set_of_books_id
,-NVL(rec_awt_lines.base_amount, rec_awt_lines.amount)
,NULL -- base amount bug 5190989
,NULL -- batch_id
,P_Last_Updated_By
,SYSDATE
,new_invoice_base_descr||to_char(rec_awt_lines.distribution_line_number)
,P_Last_Update_Login
,NULL -- match_status_flag
,'N' -- posted_flag
,P_Program_Application_Id
,P_Program_Id
,decode (P_Program_Id,NULL,NULL,SYSDATE)
,P_Request_Id
,NULL -- tax_code_id
,'T' -- encumbered_flag
,'E' -- pa_addition_flag
,0
,0
,NULL -- awt_flag
,NULL -- awt_tax_rate_id
,NULL -- awt_gross_amount
,NULL -- awt_origin_group_id
,NULL -- awt_invoice_payment_id
,l_invoice_distribution_id
,NULL -- Global Attribute Category
,NULL -- Global Attribute1
,NULL
,NULL
,NULL
,NULL -- Global Attribute5
,NULL
,NULL
,NULL
,NULL
,NULL -- Global Attribute10
,NULL
,NULL
,NULL
,NULL
,NULL -- Global Attribute15
,NULL
,NULL
,NULL
,NULL
,NULL -- Global Attribute20
,NULL -- type_1099
,NULL -- income_tax_region
,rec_awt_lines.org_id
,NULL -- awt_related_id
,'N'
,'PERMANENT'); -- bug 8304036: modify
debug_info := 'Update ap_invoice_distributions';
UPDATE ap_invoice_distributions_all
SET awt_invoice_id = new_invoice_id
WHERE current of c_awt_lines_rev;
UPDATE ap_invoice_distributions_all
SET awt_invoice_id = new_invoice_id
WHERE current of c_awt_lines;
,P_Last_Updated_By
,P_Last_Updated_By
,ta_payment_priority
,l_batch_id --bug 8266021
,inv_terms_date
,rec_awt_invs.invoice_amount --bug 8266021
,gl_currency_api.convert_amount(
base_currency,
ta_payment_currency_code,
rec_awt_invs.accounting_date, --bug 8266021
c_payment_cross_rate_type,
rec_awt_invs.invoice_amount) --bug 8266021
,c_payment_cross_rate
,NULL
,nvl(l_PAYMENT_METHOD_CODE,'CHECK')
,base_currency
,ta_payment_currency_code
,'ap_do_withholding');
/* commented in bug 8266021 ,this update has been moved up
debug_info := 'Update ap_invoice_distributions';
UPDATE ap_invoice_distributions_all
SET awt_invoice_id = new_invoice_id
WHERE current of c_awt_lines;
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER DEFAULT NULL,
P_Program_Id IN NUMBER DEFAULT NULL,
P_Request_Id IN NUMBER DEFAULT NULL,
P_Awt_Success OUT NOCOPY VARCHAR2,
P_Invoice_Payment_Id IN NUMBER DEFAULT NULL,
P_Check_Id IN NUMBER DEFAULT NULL,
p_checkrun_id in number default null)
IS
l_awt_flag ap_invoices.awt_flag%TYPE;
- AutoSELECT / Build Payments
- Confirm Payment Batch
- Invoice Entry / Inquiry
- QuickCheck
Three dIFferent processing units ("Create Temporary AWT distributions",
"Create AWT distributions" AND "Create AWT Invoices") are conditionally
executed depENDing on the originating event triggering the Ap_Do_Withholding
PROCEDURE, as represented in the following flow diagrams:
+=========================+
| |
| AutoApproval |
| |
+=========================+
|
|
^
/ \
/ \
/ \
/ create_ \ +------------------------------------+
/ dists = \_______| |
\ APPROVAL / Yes | Create Temporary AWT distributions |
\/BOTH / | |
\ ? / +------------------+-----------------+
\ / |
\ / |
v |
No | |
| +------------------+-----------------+
| | |
| | Create AWT distributions |
| | |
| +------------------+-----------------+
| |
+--------------------------------+
|
^
/ \
/ \
/ \
/ create_ \ +------------------------------------+
/ invoices \_______| |
\= APPROVAL / Yes | Create AWT Invoices |
\ /BOTH / | |
\ ? / +------------------+-----------------+
\ / |
\ / |
v |
No | |
| |
+--------------------------------+
|
+----+----+
| DONE |
+---------+
+===========================+
| |
| AutoSelect/Build Payments |
| |
+===========================+
|
|
^
/ \
/ \
/ \
/ create_ \ +------------------------------------+
/ dists = \_______| |
\ PAYMENT / Yes | Create Temporary AWT distributions |
\ /BOTH / | |
\ ? / +------------------+-----------------+
\ / |
\ / |
v |
No | |
+--------------------------------+
|
| +------------------------------------+
| | |
| | Create AWT distributions |
| | |
| +------------------------------------+
|
| +------------------------------------+
| | |
| | Create AWT Invoices |
| | |
| +------------------------------------+
|
+----+----+
| DONE |
+---------+
+=========================+
| |
| Confirm Payment Batch |
| |
+=========================+
|
| +------------------------------------+
| | |
| | Create Temporary AWT distributions |
| | |
| +------------------------------------+
^
/ \
/ \
/ \
/ create_ \ +------------------------------------+
/ dists = \_______| |
\ PAYMENT / Yes | Create AWT distributions |
\ /BOTH / | |
\ ? / +------------------+-----------------+
\ / |
\ / |
v |
No | |
+--------------------------------+
|
^
/ \
/ \
/ \
/ create_ \ +------------------------------------+
/ invoices \_______| |
\ = PAYMENT / Yes | Create AWT Invoices |
\ /BOTH / | |
\ ? / +------------------+-----------------+
\ / |
\ / |
v |
No | |
+--------------------------------+
|
+----+----+
| DONE |
+---------+
+=========================+
| |
| Invoice Entry/Inquiry |
| |
+=========================+
| +------------------------------------+
|_____________| |
| Create Temporary AWT distributions |
| |
+------------------+-----------------+
|
+--------------------------------+
|
| +------------------------------------+
| | |
| | Create AWT distributions |
| | |
| +------------------------------------+
|
| +------------------------------------+
| | |
| | Create AWT Invoices |
| | |
| +------------------------------------+
+----+----+
| DONE |
+---------+
+=========================+
| |
| QuickCheck |
| |
+=========================+
|
|
^
/ \
/ \
/ \
/ create_ \ +------------------------------------+
/ dists = \_______| |
\ PAYMENT / Yes | Create Temporary AWT distributions |
\ /BOTH / | |
\ ? / +------------------+-----------------+
\ / |
\ / |
v |
No | |
| +------------------+-----------------+
| | |
| | Create AWT distributions |
| | |
| +------------------+-----------------+
| |
+--------------------------------+
|
^
/ \
/ \
/ \
/ create_ \ +------------------------------------+
/ invoices \_______| |
\ = PAYMENT / Yes | Create AWT Invoices |
\ /BOTH / | |
\ ? / +------------------+-----------------+
\ / |
\ / |
v |
No | |
+--------------------------------+
|
+----+----+
| DONE |
+---------+
<< End of Ap_Do_Withholding program documentation >>
*/
BEGIN
current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Do_Withholding';
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Awt_Success,
P_Invoice_Payment_Id,
P_Check_Id,
p_checkrun_id);
SELECT create_awt_dists_type,
create_awt_invoices_type,
NVL(ai.awt_flag, 'N') awt_flag,
ai.invoice_currency_code,
ai.org_id --4742265
INTO l_create_dists,
l_create_invoices,
l_awt_flag,
l_inv_curr_code,
l_org_id --4742265
FROM ap_system_parameters_all asp,
ap_invoices_all ai
WHERE ai.org_id = asp.org_id
and ai.invoice_id = p_invoice_id;
(P_Calling_Module in ('AUTOSELECT', 'QUICKCHECK') ))
OR
( P_Calling_Module in ('INVOICE ENTRY', 'INVOICE INQUIRY', 'AWT REPORT'))
) THEN
savepoint BEFORE_TEMPORARY_CALCULATIONS;
,P_Last_Updated_By
,P_Last_Update_Login
,P_Program_Application_Id
,P_Program_Id
,P_Request_Id
,l_AWT_success
,current_calling_sequence
,P_Invoice_Payment_Id
,p_checkrun_id
,l_org_id); --4742265
,P_Last_Updated_By
,P_Last_Update_Login
,P_Program_Application_Id
,P_Program_Id
,P_Request_Id
,current_calling_sequence
,P_Check_Id); --bug 8590059
P_Last_Updated_By => P_Last_Updated_By,
P_Last_Update_Login => P_Last_Update_Login,
P_Program_Application_Id => P_Program_Application_Id,
P_Program_Id => P_Program_Id,
P_Request_Id => P_Request_Id,
P_Calling_Sequence => current_calling_sequence,
P_Calling_Module => p_calling_module, --Bug6660355
P_Inv_Line_No => NULL,
P_Dist_Line_No => NULL,
P_New_Invoice_Id => NULL,
P_create_dists => l_create_dists); --Bug7685907
PROCEDURE Ap_Withhold_AutoSelect (
P_Checkrun_Name IN VARCHAR2,
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER,
P_Program_Id IN NUMBER,
P_Request_Id IN NUMBER,
p_checkrun_id in number)
IS
DBG_Loc VARCHAR2(30) := 'Ap_Withhold_AutoSelect';
current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Withhold_AutoSelect';
debug_info := 'AP_WITHHOLDING_PKG.AP_Withhold_AutoSelect';
SELECT invoice_id
, vendor_id
, payment_num
FROM ap_SELECTed_invoices_all ASI,
ap_system_parameters_all asp
WHERE checkrun_name = l_checkrun_name
AND original_invoice_id IS NULL
AND asp.org_id = asi.org_id
and checkrun_id = l_checkrun_id
--Bug6660355
AND decode(nvl(ASP.allow_awt_flag, 'N'), 'Y',
decode(ASP.create_awt_dists_type, 'PAYMENT',
'Y','BOTH','Y',decode(ASP.create_awt_invoices_type, 'PAYMENT',
'Y','BOTH','Y','N'),
'N'),
'N') = 'Y';
debug_info := 'OPEN CURSOR for all SELECTed invoices';
debug_info := 'Fetch CURSOR for all SELECTed invoices';
,P_Calling_Module => 'AUTOSELECT'
,P_Last_Updated_By => P_Last_Updated_By
,P_Last_Update_Login => P_Last_Update_Login
,P_Program_Application_Id => P_Program_Application_Id
,P_Program_Id => P_Program_Id
,P_Request_Id => P_Request_Id
,P_Awt_Success => undo_output
,P_checkrun_id => p_checkrun_id );
debug_info := 'CLOSE CURSOR for all SELECTed invoices';
UPDATE ap_SELECTed_invoices_all
SET ok_to_pay_flag = 'Y',
proposed_payment_amount = invoice_amount * payment_cross_rate,
-- We cannot round the proposed_payment_amount here since we don't
-- have payment_currency_code. We will round it later.
dont_pay_reason_code = NULL,
dont_pay_description = NULL
WHERE checkrun_name = P_Checkrun_Name AND
ok_to_pay_flag = 'N' AND
checkrun_id = p_checkrun_id and
dont_pay_reason_code = 'AWT ERROR';
SELECT ASI.invoice_id
, ASI.payment_num
, ASI.payment_amount
, ASI.discount_amount
, NVL(ASI.invoice_exchange_rate, 1) invoice_exchange_rate
, NVL(ASI.payment_cross_rate,1) payment_cross_rate
, AI.payment_currency_code
, NVL(asp.awt_include_discount_amt, 'N') include_discount_amt
, asp.base_currency_code
, NVL(ASI.payment_exchange_rate,1) payment_exchange_rate --bug 8590059
FROM ap_SELECTed_invoices_all ASI,
ap_invoices_all AI,
ap_system_parameters_all asp
WHERE ASI.checkrun_name = l_checkrun_name
AND asi.checkrun_id = l_checkrun_id
AND AI.invoice_id = ASI.invoice_id
AND AI.org_id = asp.org_id
AND NVL(ASI.ok_to_pay_flag,'Y') IN ( 'Y','F')
AND NOT EXISTS (SELECT 'Manual AWT dists exist'
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = ASI.invoice_id
AND AID.line_type_lookup_code = 'AWT'
AND AID.awt_flag = 'M')
AND ((ASP.create_awt_dists_type ='PAYMENT' --Bug6660355
AND NOT EXISTS (SELECT 'Invoice already withheld by AutoApproval'
FROM ap_invoices AI
WHERE AI.invoice_id = ASI.invoice_id
AND NVL(AI.awt_flag, 'N') = 'Y'))
OR
ASP.create_awt_dists_type ='BOTH')
AND EXISTS (SELECT 'At least one dist exists with AWT_GROUP_ID'
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = ASI.invoice_id
AND AID.pay_awt_group_id IS NOT NULL) --Bug8631142
AND ASI.original_invoice_id IS NULL --Bug6660355
AND decode(nvl(ASP.allow_awt_flag, 'N'), 'Y',
decode(ASP.create_awt_dists_type, 'PAYMENT',
'Y','BOTH','Y', decode(ASP.create_awt_invoices_type, 'PAYMENT',
'Y','BOTH','Y','N'),
'N'),
'N') = 'Y'
FOR UPDATE OF
ASI.proposed_payment_amount
, ASI.payment_amount
, ASI.withholding_amount
, ASI.ok_to_pay_flag
, ASI.dont_pay_reason_code
, ASI.dont_pay_description;
l_update_indicator number:=0;
debug_info := 'Select check_date for thIS checkrun';
SELECT AISC.check_date
INTO l_awt_date
FROM ap_inv_SELECTion_criteria_all AISC
WHERE AISC.checkrun_name = P_Checkrun_Name
and aisc.checkrun_id = p_checkrun_id;
if l_update_indicator = 0 then
--if we are here the cursor got data, so we need to set the
--batches rejection levels to request
-- Bug 7492768 We need to set the inv_awt_exists_flag which indicates if the
-- check run contains invoice that has awt. If the flag is set we would
-- pass the rejection_level_code as 'REQUEST' to IBY.
-- We will not update the rejection levels directly so that we can retrieve
-- the initial values for these if the user removes awt invoices during
-- the review stage from the selected invoices.
update ap_inv_selection_criteria_all
set /*document_rejection_level_code = 'REQUEST',
payment_rejection_level_code = 'REQUEST'*/
inv_awt_exists_flag = 'Y'
where checkrun_id = p_checkrun_id;
l_update_indicator := 1;
SELECT /*invoice_amount,*/ amount_remaining /*Bug 14530960*/
INTO /*l_invoice_amount,*/ l_amount_remaining /*Bug 14530960*/
FROM ap_selected_invoices_all
WHERE invoice_id = rec_ok_sel_invs.invoice_id
AND checkrun_name = p_checkrun_name
and checkrun_id = p_checkrun_id
AND payment_num = rec_ok_sel_invs.payment_num;
SELECT sum(nvl(base_amount,amount))
INTO l_invoice_amount
FROM ap_invoice_distributions
WHERE invoice_id = rec_ok_sel_invs.invoice_id
AND line_type_lookup_code <> 'AWT'; /*End of Bug 14530960*/
/*SELECT sum(nvl(aid.base_amount,aid.amount)) --query is commented for Bug:14696775
INTO l_total_awt_amount
FROM ap_invoice_distributions aid,ap_invoices ai
WHERE aid.invoice_id = ai.invoice_id
AND aid.invoice_id =rec_ok_sel_invs.invoice_id
AND aid.line_type_lookup_code in ('AWT')
AND aid.awt_origin_group_id = ai.awt_group_id;*/
SELECT sum(nvl(aid.base_amount,aid.amount))
INTO l_total_awt_amount
FROM ap_invoice_distributions aid
WHERE aid.invoice_id =rec_ok_sel_invs.invoice_id
AND aid.line_type_lookup_code in ('AWT')
AND aid.awt_invoice_payment_id is null;
SELECT SUM(NVL(payment_amount,0)) +
SUM((-1) * NVL(withholding_amount,0))
INTO l_total_amount
FROM ap_SELECTed_invoices_all
WHERE checkrun_name = p_checkrun_name
and checkrun_id = p_checkrun_id
AND NVL(ok_to_pay_flag,'Y') in ( 'Y','F');
Select COUNT(*)
INTO l_count
FROM ap_selected_invoices_all
WHERE checkrun_name = p_checkrun_name
and checkrun_id = p_checkrun_id
AND NVL(ok_to_pay_flag,'Y') IN ( 'Y','F')
AND invoice_amount < 0;
SELECT (-1) * (SUM(NVL(payment_amount,0) +
NVL(ABS(withholding_amount),0)))
INTO l_subject_amount
FROM ap_selected_invoices_all
WHERE payment_amount > 0
AND NVL(ok_to_pay_flag,'Y') in ( 'Y','F')
AND checkrun_name = p_checkrun_name
and checkrun_id = p_checkrun_id;
SELECT (-1) * (SUM(NVL(ABS(payment_amount),0) +
NVL(withholding_amount,0)))
INTO l_amountapplied
FROM ap_selected_invoices_all
WHERE NVL(withholding_amount,0) > 0
AND NVL(ok_to_pay_flag,'Y') in ( 'Y','F')
AND checkrun_name = p_checkrun_name
and checkrun_id = p_checkrun_id;
,P_Calling_Module => 'AUTOSELECT'
,P_Amount => l_subject_amount
,P_Payment_Num => rec_ok_sel_invs.payment_num
,P_Checkrun_Name => P_Checkrun_Name
,P_Last_Updated_By => P_Last_Updated_By
,P_Last_Update_Login => P_Last_Update_Login
,P_Program_Application_Id => P_Program_Application_Id
,P_Program_Id => P_Program_Id
,P_Request_Id => P_Request_Id
,P_Awt_Success => l_awt_success
,P_checkrun_id => p_checkrun_id
);
debug_info := 'Select sum of withholding amount for thIS invoice';
SELECT NVL(SUM(AATD.withholding_amount), 0)
INTO l_withholding_amount
FROM ap_awt_temp_distributions_all AATD
WHERE AATD.checkrun_name = P_Checkrun_Name
AND AATD.invoice_id = rec_ok_sel_invs.invoice_id
AND AATD.payment_num = rec_ok_sel_invs.payment_num
and aatd.checkrun_id = p_checkrun_id;
debug_info := 'Update proposed payment in ap_selected_invoices';
UPDATE ap_selected_invoices_all ASI
SET /*ASI.proposed_payment_amount =
ap_utilities_pkg.ap_round_currency(
ASI.proposed_payment_amount,rec_ok_sel_invs.payment_currency_code) -
l_withholding_amount
-- We round proposed_payment_amount here because we couldn't round it earlier.
,ASI.payment_amount =
ASI.payment_amount - l_withholding_amount
,ASI.amount_remaining =
ASI.amount_remaining - l_withholding_amount
,ASI.withholding_amount = l_withholding_amount */
--Bug#8281225 Wrong Amount Remaining in Case of Inv Payment Through PPR
ASI.proposed_payment_amount = ap_utilities_pkg.ap_round_currency(ASI.proposed_payment_amount,rec_ok_sel_invs.payment_currency_code)
- nvl(l_withholding_amount, 0)
,ASI.payment_amount = ap_utilities_pkg.ap_round_currency(ASI.proposed_payment_amount,rec_ok_sel_invs.payment_currency_code)
- nvl(l_withholding_amount, 0)
,ASI.withholding_amount = l_withholding_amount
WHERE current of c_ok_sel_invs;
debug_info := 'Update AWT error in ap_selected_invoices';
UPDATE ap_SELECTed_invoices_all ASI
SET ASI.ok_to_pay_flag = 'N',
ASI.dont_pay_reason_code = 'AWT ERROR',
ASI.dont_pay_description = substr(l_awt_success, 1, 255)
WHERE current of c_ok_sel_invs;
update ap_inv_selection_criteria_all
set /*document_rejection_level_code = 'REQUEST',
payment_rejection_level_code = 'REQUEST'*/
inv_awt_exists_flag = 'Y'
where checkrun_id = p_checkrun_id;
END Ap_Withhold_AutoSelect;
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER,
P_Program_Id IN NUMBER,
P_Request_Id IN NUMBER,
p_checkrun_id in number,
p_completed_pmts_group_id in number,
p_org_id in number,
p_check_date in date
)
IS
-- DO Withholding for all OK to pay selected invoices in this checkrun
CURSOR c_ok_sel_invs IS
SELECT ASI.invoice_id,
ASI.payment_num,
p_check_date payment_date
FROM ap_selected_invoices_all ASI,
iby_fd_docs_payable_v ibydocs
WHERE ASI.checkrun_name = p_checkrun_name
AND ASI.original_invoice_id IS NULL
and asi.checkrun_id = p_checkrun_id
and ibydocs.calling_app_doc_unique_ref1 = to_char(asi.checkrun_id) /* Added to_char for bug#8462020 */
AND ibydocs.calling_app_doc_unique_ref2 = to_char(asi.invoice_id) /* Added to_char for bug#8462020 */
AND ibydocs.calling_app_doc_unique_ref3 = to_char(asi.payment_num) /* Added to_char for bug#8462020 */
and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id
and ibydocs.org_id = p_org_id
and ibydocs.calling_app_id = 200; /* Added calling_app_id condition for bug#8462020 */
,P_Last_Updated_By => P_Last_Updated_By
,P_Last_Update_Login => P_Last_Update_Login
,P_Program_Application_Id => P_Program_Application_Id
,P_Program_Id => P_Program_Id
,P_Request_Id => P_Request_Id
,P_Awt_Success => l_awt_success
,p_checkrun_id => p_checkrun_id
);
,P_Last_Updated_By => P_Last_Updated_By
,P_Last_Update_Login => P_Last_Update_Login
,P_Program_Application_Id => P_Program_Application_Id
,P_Program_Id => P_Program_Id
,P_Request_Id => P_Request_Id
,P_Awt_Success => l_awt_success
,p_checkrun_id => p_checkrun_id
);
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER,
P_Program_Id IN NUMBER,
P_Request_Id IN NUMBER,
p_checkrun_id in number,
p_completed_pmts_group_id in number default null,
p_org_id in number default null)
IS
-- UNDO Withholding for all selected invoices in thIS checkrun
CURSOR c_all_sel_invs (l_checkrun_name IN VARCHAR2, l_checkrun_id in number)
IS
SELECT ASI.invoice_id
, ASI.payment_num
, AI.vendor_id
FROM ap_SELECTed_invoices_all ASI
, ap_invoices_all AI
WHERE ASI.checkrun_name = l_checkrun_name
AND AI.invoice_id = ASI.invoice_id
and asi.checkrun_id = l_checkrun_id;
SELECT ASI.invoice_id
, ASI.payment_num
, AI.vendor_id
FROM ap_SELECTed_invoices_all ASI
, ap_invoices_all AI
, iby_fd_docs_payable_v ibydocs
WHERE ASI.checkrun_name = p_checkrun_name
AND AI.invoice_id = ASI.invoice_id
and asi.checkrun_id = p_checkrun_id
and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id
and ibydocs.org_id = p_org_id
and ibydocs.calling_app_doc_unique_ref1 = asi.checkrun_id
AND ibydocs.calling_app_doc_unique_ref2 = asi.invoice_id
AND ibydocs.calling_app_doc_unique_ref3 = asi.payment_num;
debug_info := 'Open Cursor for all selected invoices';
debug_info := 'Fetch CURSOR for all SELECTed invoices -- invoice_id = '||to_char(rec_all_sel_invs.invoice_id);
,P_Last_Updated_By => P_Last_Updated_By
,P_Last_Update_Login => P_Last_Update_Login
,P_Program_Application_Id => P_Program_Application_Id
,P_Program_Id => P_Program_Id
,P_Request_Id => P_Request_Id
,P_Awt_Success => l_awt_success
,P_checkrun_id => p_checkrun_id);
debug_info := 'CLOSE CURSOR for all SELECTed invoices';
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER DEFAULT NULL,
P_Program_Id IN NUMBER DEFAULT NULL,
P_Request_Id IN NUMBER DEFAULT NULL,
P_Awt_Success OUT NOCOPY VARCHAR2,
P_checkrun_id in number default null)
IS
DBG_Loc VARCHAR2(30) := 'Ap_Undo_Temp_Withholding';
IF (P_Calling_Module in ('AUTOSELECT', 'QUICKCHECK')) THEN
Ap_Undo_Orphan_Distributions
(P_Invoice_Id => P_Invoice_Id
,P_VENDor_Id => P_Vendor_Id
,P_Payment_Num => P_Payment_Num
,P_Checkrun_Name => P_Checkrun_Name
,P_Undo_Awt_Date => P_Undo_Awt_Date
,P_Calling_Module => P_Calling_Module
,P_Last_Updated_By => P_Last_Updated_By
,P_Last_Update_Login => P_Last_Update_Login
,P_Program_Application_Id => P_Program_Application_Id
,P_Program_Id => P_Program_Id
,P_Request_Id => P_Request_Id
,P_Awt_Success => P_Awt_Success
,P_checkrun_id => P_checkrun_id);
IF (P_Calling_Module in ('AUTOSELECT', 'CANCEL', 'PROJECTED')) THEN
<>
DECLARE
CURSOR c_temp (InvId IN NUMBER
,PaymNum IN NUMBER
,CheckrunName in VARCHAR2
,Calling_Module in VARCHAR2
,checkrun_id in number) IS
SELECT AATD.invoice_id
, AATD.payment_num
, AATD.group_id
, AATD.tax_name
, AATD.tax_code_combination_id
, AATD.gross_amount
, AATD.withholding_amount
, AATD.base_withholding_amount
, AATD.accounting_date
, AATD.period_name
, AATD.checkrun_name
, AATD.tax_rate_id
, TC.tax_id tax_code_id
, aatd.checkrun_id
FROM ap_awt_temp_distributions_all AATD,
ap_invoices_all AI,
ap_tax_codes_all TC
WHERE AATD.invoice_id = InvId
AND AATD.invoice_id = AI.invoice_id
AND TC.name(+) = AATD.tax_name
AND TC.tax_type = 'AWT' -- BUG 3665866
AND NVL(TC.enabled_flag,'Y') = 'Y'
AND NVL(AI.invoice_date,SYSDATE) BETWEEN
NVL(TC.start_date, NVL(AI.invoice_date,SYSDATE)) AND
NVL(TC.inactive_date, NVL(AI.invoice_date,SYSDATE))
AND (((AATD.checkrun_name = NVL(CheckrunName, AATD.checkrun_name))
AND (AATD.payment_num = NVL(PaymNum, AATD.payment_num))
and (aatd.checkrun_id = nvl(checkrun_id, aatd.checkrun_id)))
OR
(AATD.checkrun_name IS NULL
AND AATD.payment_num IS NULL
and aatd.checkrun_id is null
AND calling_module = 'PROJECTED'))
AND TC.org_id = AI.org_id -- Bug 8772252
FOR UPDATE of aatd.withholding_amount, aatd.base_withholding_amount; --tapan, added two columns to prevent deadlock on TC;
SELECT distinct AATD.invoice_id
, AATD.payment_num
, AATD.group_id
, AATD.tax_name
, AATD.gross_amount
, TC.tax_id tax_code_id
, aatd.checkrun_id
, AATD.accounting_date
FROM ap_awt_temp_distributions_all AATD,
ap_invoices_all AI,
ap_tax_codes_all TC
WHERE AATD.invoice_id = InvId
AND AATD.invoice_id = AI.invoice_id
AND TC.name(+) = AATD.tax_name
AND TC.tax_type = 'AWT'
AND NVL(TC.enabled_flag,'Y') = 'Y'
AND NVL(AI.invoice_date,SYSDATE) BETWEEN
NVL(TC.start_date, NVL(AI.invoice_date,SYSDATE)) AND
NVL(TC.inactive_date, NVL(AI.invoice_date,SYSDATE))
AND (((AATD.checkrun_name = NVL(CheckrunName, AATD.checkrun_name))
AND (AATD.payment_num = NVL(PaymNum, AATD.payment_num))
and (aatd.checkrun_id = nvl(checkrun_id, aatd.checkrun_id)))
OR
(AATD.checkrun_name IS NULL
AND AATD.payment_num IS NULL
and aatd.checkrun_id is null
AND calling_module = 'PROJECTED'))
AND TC.org_id = AI.org_id ;
SELECT 'Limit ExISts'
FROM ap_tax_codes_all
WHERE tax_id = TaxId
AND awt_period_type IS not NULL;
IF (P_Calling_Module in ('AUTOSELECT', 'CANCEL') AND
Period_Limit_ExISt_For_Tax(rec_temp_gross.tax_code_id
,current_calling_sequence)) THEN
DECLARE
CURSOR c_get_awt_period IS
SELECT p.period_name
FROM ap_other_periods P,
ap_tax_codes_all C
WHERE (rec_temp_gross.accounting_date BETWEEN
p.start_date AND p.end_date)
AND p.period_type = c.awt_period_type
AND c.name = rec_temp_gross.tax_name
AND p.module = 'AWT';
UPDATE ap_awt_buckets_all
SET gross_amount_to_date = gross_amount_to_date -
NVL(rec_temp_gross.gross_amount,0)
, last_UPDATE_date = SYSDATE
, last_UPDATEd_by = P_Last_Updated_By
, last_UPDATE_login = P_Last_Update_Login
, program_UPDATE_date = SYSDATE
, program_application_id = P_Program_Application_Id
, program_id = P_Program_Id
, request_id = P_Request_Id
WHERE period_name = awt_period
AND tax_name = rec_temp_gross.tax_name
AND vendor_id = P_vendor_Id;
IF (P_Calling_Module in ('AUTOSELECT', 'CANCEL') AND
Period_Limit_ExISt_For_Tax(rec_temp.tax_code_id
,current_calling_sequence)) THEN
DECLARE
CURSOR c_get_awt_period IS
SELECT p.period_name
FROM ap_other_periods P,
ap_tax_codes_all C
WHERE (rec_temp.accounting_date BETWEEN
p.start_date AND p.end_date)
AND p.period_type = c.awt_period_type
AND c.name = rec_temp.tax_name
AND p.module = 'AWT';
debug_info := 'Update ap_awt_buckets';
UPDATE ap_awt_buckets_all
/* SET gross_amount_to_date = gross_amount_to_date -
NVL(rec_temp.gross_amount,0) */
SET withheld_amount_to_date = withheld_amount_to_date -
NVL(rec_temp.withholding_amount,0)
, last_UPDATE_date = SYSDATE
, last_UPDATEd_by = P_Last_Updated_By
, last_UPDATE_login = P_Last_Update_Login
, program_UPDATE_date = SYSDATE
, program_application_id = P_Program_Application_Id
, program_id = P_Program_Id
, request_id = P_Request_Id
WHERE period_name = awt_period
AND tax_name = rec_temp.tax_name
AND vendor_id = P_vendor_Id;
/* Bug 12433018 Moved updates to ap_selected_invoices_all and the delete
outside the loop */
IF (P_Calling_Module = 'AUTOSELECT') THEN
debug_info := 'Update ap selected invoices';
/* Note the withholding_amount in ap_selected_invoices_all is already in the
payment currency. See ap_withhold_autoslect where it is updated.
ap_undo_temp_withholding is also called at the beginning of the
ap_withhold_autoselect procedure so it also has to properly handle the case
where no withholding has been calculated yet. */
UPDATE ap_selected_invoices_all si
SET proposed_payment_amount = nvl(proposed_payment_amount,0) + NVL(withholding_amount,0)
, payment_amount = nvl(proposed_payment_amount,0) + NVL(withholding_amount,0)
, withholding_amount = 0
WHERE checkrun_name = p_checkrun_name
AND invoice_id = p_invoice_id
AND payment_num = p_payment_num
and checkrun_id = p_checkrun_id
and exists
(SELECT 1
FROM ap_awt_temp_distributions_all atd
WHERE atd.invoice_id = si.invoice_id
AND atd.payment_num = si.payment_num
AND atd.checkrun_id = si.checkrun_id);
END IF; /* calling mode equals AUTOSELECT */
debug_info := 'Delete the AWT temp distribution';
DELETE ap_awt_temp_distributions_all
WHERE invoice_id = p_invoice_id
AND ( ( (checkrun_name = NVL(p_checkrun_name, checkrun_name))
AND (payment_num = NVL(p_payment_num, payment_num))
and (checkrun_id = nvl(p_checkrun_id,checkrun_id)))
OR
( checkrun_name IS NULL
and checkrun_id is NULL
AND payment_num IS NULL
AND P_calling_module = 'PROJECTED'));
END Undo_During_AutoSELECT;
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Awt_Success,
p_checkrun_id);
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER DEFAULT NULL,
P_Program_Id IN NUMBER DEFAULT NULL,
P_Request_Id IN NUMBER DEFAULT NULL,
P_Awt_Success OUT NOCOPY VARCHAR2,
P_Inv_Line_No IN NUMBER DEFAULT NULL,
P_dist_Line_No IN NUMBER DEFAULT NULL,
P_New_Invoice_Id IN NUMBER DEFAULT NULL,
P_New_dist_Line_No IN NUMBER DEFAULT NULL)
IS
/*
Copyright (c) 1995 by Oracle Corporation
NAME
Ap_Undo_Withholding
DESCRIPTION
Reverses AWT distribution lines, buckets, tax authority invoices
for a full invoice or for a payment depENDing upon the calling module
NOTES
ThIS PROCEDURE IS part of the AP_WITHHOLDING_PKG PL/SQL package
HISTORY (YY/MM/DD)
atassoni.it 95/07/14 Creation
mhtaylor 95/08/21 Adapted for Adjust distributions
<< Beginning of Undo_Awt_By_Invoice_Payment program documentation >>
Flow of thIS PROCEDURE:
*---------------------------*
| BEGIN Ap_Undo_Withholding |
*---------------------------*
|
v
*---------------------------------------------------*
| Get one AWT distribution line for current invoice | <------------------+
| or invoice payment | |
*---------------------------------------------------* |
| |
v |
*------------------------------------------------------* |
| Get line accounting DATE AND corresponding WT period | |
*------------------------------------------------------* |
| |
v |
*-----------------------------------* |
| Reverse the AWT distribution line | |
*-----------------------------------* |
| |
v |
*--------------------------------------------* |
| Adjust invoice amount AND payment schedule | |
*--------------------------------------------* |
| |
*--------------------------------------------* |
| Decrease corresponding bucket, IF exISting | |
*--------------------------------------------* |
| |
+--> An invoice to a tax authority exISts? |
,'`. |
*-----------------------------* Yes ,' `. |
| Reverse that invoice: | <---------- |
| ~~~~~~~~~~~~~~~~~~~~ | `. ,' |
| - Reverse invoice line | `.,' |
| - Reverse distribution line | No | |
| - Reverse payment schedules | | |
*-----------------------------* | |
| | |
+<--------------------------+ |
| |
v |
,'`. |
,' `. No |
---------------------------------------------+
`. ,'
`.,'
Yes |
v
*-------------------------*
| END Ap_Undo_Withholding |
*-------------------------*
<< End of Ap_Undo_Withholding program documentation >>
*/
-- PL/SQL Main Block Constants AND Variables:
awt_period ap_other_periods.period_name%TYPE;
SELECT AID.accounting_date
, AID.accrual_posted_flag
, AID.assets_addition_flag
, AID.assets_tracking_flag
, AID.cash_posted_flag
, AID.invoice_line_number
, AID.distribution_line_number
, AID.dist_code_combination_id
, AID.invoice_id
, AID.last_UPDATEd_by
, AID.last_UPDATE_date
, AID.line_type_lookup_code
, AID.period_name
, AID.set_of_books_id
, AID.accts_pay_code_combination_id
, AID.amount
, AID.base_amount
, AID.base_invoice_price_variance
, AID.batch_id
, AID.created_by
, AID.creation_date
, AID.description
, AID.exchange_rate_variance
, AID.final_match_flag
, AID.income_tax_region
, AID.invoice_price_variance
, AID.last_UPDATE_login
, AID.match_status_flag
, AID.posted_flag
, AID.po_distribution_id
, AID.program_application_id
, AID.program_id
, AID.program_UPDATE_date
, AID.quantity_invoiced
, AID.rate_var_code_combination_id
, AID.request_id
, AID.reversal_flag
, AID.type_1099
, AID.unit_price
, AID.withholding_tax_code_id /* Bug 5382525 */
, TC.name vat_code
, AID.amount_encumbered
, AID.base_amount_encumbered
, AID.encumbered_flag
, AID.price_adjustment_flag
, AID.price_var_code_combination_id
, AID.quantity_unencumbered
, AID.stat_amount
, AID.amount_to_post
, AID.attribute1
, AID.attribute10
, AID.attribute11
, AID.attribute12
, AID.attribute13
, AID.attribute14
, AID.attribute15
, AID.attribute2
, AID.attribute3
, AID.attribute4
, AID.attribute5
, AID.attribute6
, AID.attribute7
, AID.attribute8
, AID.attribute9
, AID.attribute_category
, AID.base_amount_to_post
, AID.cash_je_batch_id
, AID.expenditure_item_date
, AID.expenditure_organization_Id
, AID.expenditure_type
, AID.je_batch_id
, AID.parent_invoice_id
, AID.pa_addition_flag
, AID.pa_quantity
, AID.posted_amount
, AID.posted_base_amount
, AID.prepay_amount_remaining
, AID.project_accounting_context
, AID.project_id
, AID.task_id
--, AID.ussgl_transaction_code - Bug 4277744
--, AID.ussgl_trx_code_context - Bug 4277744
, AID.earliest_settlement_date
, AID.req_distribution_id
, AID.quantity_variance
, AID.base_quantity_variance
, AID.packet_id
, AID.awt_flag
, AID.awt_group_id
, AID.awt_tax_rate_id
, AID.awt_gross_amount
, AID.awt_invoice_id
, AID.awt_origin_group_id
, AID.reference_1
, AID.reference_2
, AID.org_id
, AID.other_invoice_id
, AID.awt_invoice_payment_id
, AID.invoice_distribution_id
, AID.awt_related_id
/* Start of fix for bug#8462050*/
, AID.global_attribute_category
, AID.global_attribute1
, AID.global_attribute2
, AID.global_attribute3
, AID.global_attribute4
, AID.global_attribute5
, AID.global_attribute6
, AID.global_attribute7
, AID.global_attribute8
, AID.global_attribute9
, AID.global_attribute10
, AID.global_attribute11
, AID.global_attribute12
, AID.global_attribute13
, AID.global_attribute14
, AID.global_attribute15
, AID.global_attribute16
, AID.global_attribute17
, AID.global_attribute18
, AID.global_attribute19
, AID.global_attribute20
/* End of fix for bug#8462050*/
FROM ap_invoice_distributions AID,
ap_tax_codes TC
--,ap_invoices AI --Bug8547506
WHERE AID.invoice_id = ParentId
AND TC.tax_id (+) = AID.withholding_tax_code_id /* Bug 5382525 */
--Bug8547506 Undoing changes done for bug6660355
--AND AID.invoice_id = AI.invoice_id --6660355
--AND AID.awt_origin_group_id = AI.awt_group_id
AND AID.invoice_line_number = NVL(P_Inv_Line_No,
AID.invoice_line_number)
AND AID.distribution_line_number = NVL(P_dist_Line_No,
AID.distribution_line_number)
AND NVL(AID.reversal_flag, 'N') <> 'Y' -- bug 7606072
AND NVL(AID.awt_flag, 'M') = 'A';
SELECT AID.accounting_date
, AID.accrual_posted_flag
, AID.assets_addition_flag
, AID.assets_tracking_flag
, AID.cash_posted_flag
, AID.invoice_line_number
, AID.distribution_line_number
, AID.dist_code_combination_id
, AID.invoice_id
, AID.last_UPDATEd_by
, AID.last_UPDATE_date
, AID.line_type_lookup_code
, AID.period_name
, AID.set_of_books_id
, AID.accts_pay_code_combination_id
, AID.amount
, AID.base_amount
, AID.base_invoice_price_variance
, AID.batch_id
, AID.created_by
, AID.creation_date
, AID.description
, AID.exchange_rate_variance
, AID.final_match_flag
, AID.income_tax_region
, AID.invoice_price_variance
, AID.last_UPDATE_login
, AID.match_status_flag
, AID.posted_flag
, AID.po_distribution_id
, AID.program_application_id
, AID.program_id
, AID.program_UPDATE_date
, AID.quantity_invoiced
, AID.rate_var_code_combination_id
, AID.request_id
, AID.reversal_flag
, AID.type_1099
, AID.unit_price
, AID.withholding_tax_code_id /* Bug 5382525 */
, TC.name vat_code
, AID.amount_encumbered
, AID.base_amount_encumbered
, AID.encumbered_flag
, AID.price_adjustment_flag
, AID.price_var_code_combination_id
, AID.quantity_unencumbered
, AID.stat_amount
, AID.amount_to_post
, AID.attribute1
, AID.attribute10
, AID.attribute11
, AID.attribute12
, AID.attribute13
, AID.attribute14
, AID.attribute15
, AID.attribute2
, AID.attribute3
, AID.attribute4
, AID.attribute5
, AID.attribute6
, AID.attribute7
, AID.attribute8
, AID.attribute9
, AID.attribute_category
, AID.base_amount_to_post
, AID.cash_je_batch_id
, AID.expenditure_item_date
, AID.expenditure_organization_Id
, AID.expenditure_type
, AID.je_batch_id
, AID.parent_invoice_id
, AID.pa_addition_flag
, AID.pa_quantity
, AID.posted_amount
, AID.posted_base_amount
, AID.prepay_amount_remaining
, AID.project_accounting_context
, AID.project_id
, AID.task_id
--, AID.ussgl_transaction_code - Bug 4277744
--, AID.ussgl_trx_code_context - Bug 4277744
, AID.earliest_settlement_date
, AID.req_distribution_id
, AID.quantity_variance
, AID.base_quantity_variance
, AID.packet_id
, AID.awt_flag
, AID.awt_group_id
, AID.awt_tax_rate_id
, AID.awt_gross_amount
, AID.awt_invoice_id
, AID.awt_origin_group_id
, AID.reference_1
, AID.reference_2
, AID.org_id
, AID.other_invoice_id
, AID.awt_invoice_payment_id
, AID.invoice_distribution_id
, awt_related_id
/* Start of fix for bug#8462050*/
, AID.global_attribute_category
, AID.global_attribute1
, AID.global_attribute2
, AID.global_attribute3
, AID.global_attribute4
, AID.global_attribute5
, AID.global_attribute6
, AID.global_attribute7
, AID.global_attribute8
, AID.global_attribute9
, AID.global_attribute10
, AID.global_attribute11
, AID.global_attribute12
, AID.global_attribute13
, AID.global_attribute14
, AID.global_attribute15
, AID.global_attribute16
, AID.global_attribute17
, AID.global_attribute18
, AID.global_attribute19
, AID.global_attribute20
/* End of fix for bug#8462050*/
FROM ap_invoice_distributions AID,
ap_tax_codes TC
WHERE AID.awt_invoice_payment_id = ParentId
AND TC.tax_id(+) = AID.withholding_tax_code_id /* 5382525 */
AND AID.invoice_line_number = NVL(P_Inv_Line_No,
AID.invoice_line_number)
AND AID.distribution_line_number = NVL(P_dist_Line_No,
AID.distribution_line_number)
AND NVL(AID.awt_flag, 'M') = 'A';
SELECT DISTINCT
AID.accounting_date
, AID.invoice_line_number
, AID.invoice_id
, AID.period_name
, AID.set_of_books_id
, AID.withholding_tax_code_id /* Bug 5382525 */
, TC.name vat_code
, AID.awt_group_id
, AID.awt_tax_rate_id
, AID.awt_invoice_payment_id
, AID.awt_gross_amount
/* End of fix for bug#8462050*/
FROM ap_invoice_distributions AID,
ap_tax_codes TC
WHERE AID.awt_invoice_payment_id = ParentId
AND TC.tax_id(+) = AID.withholding_tax_code_id /* 5382525 */
AND NVL(AID.awt_flag, 'M') = 'A';
SELECT DISTINCT
AID.accounting_date
, AID.invoice_line_number
, AID.invoice_id
, AID.period_name
, AID.set_of_books_id
, AID.withholding_tax_code_id /* Bug 5382525 */
, TC.name vat_code
, AID.awt_group_id
, AID.awt_tax_rate_id
, AID.awt_invoice_payment_id
, AID.awt_gross_amount
/* End of fix for bug#8462050*/
FROM ap_invoice_distributions AID,
ap_tax_codes TC
--,ap_invoices AI --Bug8547506
WHERE AID.invoice_id = ParentId
AND TC.tax_id (+) = AID.withholding_tax_code_id
AND AID.invoice_line_number = NVL(P_Inv_Line_No,
AID.invoice_line_number)
AND NVL(AID.reversal_flag, 'N') <> 'Y' -- bug 7606072
AND NVL(AID.awt_flag, 'M') = 'A';
debug_info := 'Select Org Id';
SELECT AI.org_id
INTO l_org_id
FROM AP_INVOICES_ALL AI
WHERE invoice_id = P_Parent_Id;
SELECT AIP.org_id
INTO l_org_id
FROM AP_INVOICE_PAYMENTS_ALL AIP
WHERE AIP.invoice_payment_id = P_Parent_Id;
debug_info := 'Select GL Period Name';
SELECT GPS.period_name,
P_Awt_Date
INTO gl_period_name,
gl_awt_date
FROM gl_period_statuses GPS,
ap_system_parameters_all ASP
WHERE GPS.application_id = 200
AND GPS.set_of_books_id = ASP.set_of_books_id
AND P_Awt_Date BETWEEN GPS.start_date AND GPS.END_date
AND GPS.closing_status IN ('O', 'F')
AND NVL(gps.ADJUSTMENT_PERIOD_FLAG,'N') = 'N'
AND ASP.org_id = l_org_id; /* Bug 4759178, added org_id condition*/
SELECT period_name
FROM ap_other_periods P,
ap_tax_codes T
WHERE t.tax_id = TaxId
AND p.period_type = t.awt_period_type
AND p.application_id = 200
AND p.module = 'AWT'
AND p.start_date <= TRUNC(distDate)
AND p.end_date >= TRUNC(distDate);
SELECT vendor_id
, set_of_books_id
, exchange_date
, exchange_rate
FROM ap_invoices
WHERE invoice_id = InvId
FOR UPDATE;
<>
DECLARE
CURSOR c_awt_bucket (VendorId IN NUMBER,
Period IN VARCHAR2,
TaxCode IN VARCHAR2) IS
SELECT gross_amount_to_date,
withheld_amount_to_date
FROM ap_awt_buckets
WHERE vendor_id = VendorId
AND period_name = Period
AND tax_name = TaxCode
FOR UPDATE;
DBG_Loc VARCHAR2(30) := 'Update_Bucket';
SELECT base_currency_code
INTO l_func_currency_code
FROM ap_system_parameters
WHERE org_id = l_org_id;
SELECT ai.exchange_rate
INTO l_invoice_exchange_rate
FROM ap_invoices ai, ap_invoice_payments aip
WHERE ai.invoice_id = aip.invoice_id
AND aip.invoice_payment_id =rec_awt_gross_dists.awt_invoice_payment_id;
debug_info := 'Update the AWT bucket';
UPDATE ap_awt_buckets
SET gross_amount_to_date = (gross_amt_to_date -
ap_utilities_pkg.ap_round_currency(
rec_awt_gross_dists.awt_gross_amount*
NVL(l_invoice_exchange_rate,1),
l_func_currency_code ))
WHERE CURRENT OF c_awt_bucket;
END Update_Bucket;
SELECT period_name
FROM ap_other_periods P,
ap_tax_codes T
WHERE t.tax_id = TaxId
AND p.period_type = t.awt_period_type
AND p.application_id = 200
AND p.module = 'AWT'
AND p.start_date <= TRUNC(distDate)
AND p.end_date >= TRUNC(distDate);
SELECT vendor_id
, set_of_books_id
, accts_pay_code_combination_id
, batch_id
, description
, invoice_amount
, invoice_currency_code
, exchange_date
, exchange_rate
, exchange_rate_type
-- , ussgl_transaction_code - Bug 4277744
-- , ussgl_trx_code_context - Bug 4277744
, vat_code
FROM ap_invoices
WHERE invoice_id = InvId
FOR UPDATE;
SELECT MAX(distribution_line_number)+1 curr_line_number
FROM ap_invoice_distributions
WHERE invoice_id = InvId
AND invoice_line_number = InvLineNum;
UPDATE ap_invoice_lines_all
SET discarded_flag = DECODE(p_calling_module,'CANCEL INVOICE','N','Y'),
/* Bug 5299720. Comment out the following line */
-- Cancelled_flag = DECODE(p_calling_module,'CANCEL INVOICE','Y','N'),
Original_amount = amount,
Original_base_amount = base_amount,
Original_rounding_amt = rounding_amt,
Amount = 0,
Base_amount = 0,
Rounding_amt = 0,
Last_update_date = SYSDATE,
Last_Updated_By = P_Last_Updated_By,
Last_Update_Login = P_Last_Update_Login,
Program_application_id = P_Program_application_id,
Program_id = P_Program_id,
Program_update_date = DECODE(p_program_id,NULL,NULL,SYSDATE),
Request_id = P_Request_id
WHERE invoice_id = rec_awt_dists.invoice_id
AND line_number = rec_awt_dists.invoice_line_number;
debug_info := 'Insert reverse AWT line INTO ap_invoice_distributions';
INSERT INTO ap_invoice_distributions
(
accounting_date
,accrual_posted_flag
,assets_addition_flag
,assets_tracking_flag
,cash_posted_flag
,distribution_line_number
,invoice_line_number
,dist_code_combination_id
,invoice_id
,last_UPDATEd_by
,last_UPDATE_date
,line_type_lookup_code
,period_name
,set_of_books_id
,amount
,base_amount
,batch_id
,created_by
,creation_date
,description
,last_UPDATE_login
,match_status_flag
,posted_flag
,program_application_id
,program_id
,program_update_date
,request_id
,withholding_tax_code_id /* Bug 5382525 */
,encumbered_flag
,pa_addition_flag
,posted_amount
,posted_base_amount
-- ,ussgl_transaction_code - Bug 4277744
-- ,ussgl_trx_code_context - Bug 4277744
,awt_flag
,awt_tax_rate_id
,awt_gross_amount
,awt_origin_group_id
,awt_invoice_payment_id
,tax_code_override_flag
,tax_recovery_rate
,tax_recovery_override_flag
,tax_recoverable_flag
,invoice_distribution_id
,reversal_flag
,parent_reversal_id
,type_1099
,income_tax_region
,org_id
,awt_related_id
--Freight and Special Charges
,rcv_charge_addition_flag
/* Start of fix for bug#8462050*/
,global_attribute_category
,global_attribute1
,global_attribute2
,global_attribute3
,global_attribute4
,global_attribute5
,global_attribute6
,global_attribute7
,global_attribute8
,global_attribute9
,global_attribute10
,global_attribute11
,global_attribute12
,global_attribute13
,global_attribute14
,global_attribute15
,global_attribute16
,global_attribute17
,global_attribute18
,global_attribute19
,global_attribute20
/* End of fix for bug#8462050*/
,cancellation_flag --bug 9781126
)
values
(
gl_awt_date
,'N'
,'N'
,'N'
,'N'
,curr_line_number /*bug 5202248. invoice_line_number was inserted before */
,rec_awt_dists.invoice_line_number
,rec_awt_dists.dISt_code_combination_id
,rec_awt_dists.invoice_id
,P_Last_Updated_By
,SYSDATE
,'AWT'
,gl_period_name
,rec_invoice.set_of_books_id
,-rec_awt_dists.amount
,-rec_awt_dists.base_amount
,rec_invoice.batch_id
,P_Last_Updated_By
,SYSDATE
,rec_awt_dists.description
,P_Last_Update_Login
,decode(p_calling_module,'REVERSE DIST','N','A') -- BUG 6720284
,'N'
,P_Program_Application_Id
,P_Program_Id
,decode (P_Program_Id,NULL,NULL,SYSDATE)
,P_Request_Id
,rec_awt_dists.withholding_tax_code_id
,'T'
,'E'
,0
,0
-- ,rec_invoice.ussgl_transaction_code - Bug 4277744
-- ,rec_invoice.ussgl_trx_code_context - Bug 4277744
,'A'
,rec_awt_dists.awt_tax_rate_id
,rec_awt_dists.awt_gross_amount * -1
,rec_awt_dists.awt_origin_group_id
,P_New_Invoice_Payment_Id
,'N'
,''
,'N'
,'N'
,ap_invoice_distributions_s.nextval
,'N'
,rec_awt_dists.invoice_distribution_id
,rec_awt_dists.type_1099
,rec_awt_dists.income_tax_region
,rec_awt_dists.org_id
,rec_awt_dists.awt_related_id
,'N'
/* Start of fix for bug#8462050*/
,rec_awt_dists.global_attribute_category
,rec_awt_dists.global_attribute1
,rec_awt_dists.global_attribute2
,rec_awt_dists.global_attribute3
,rec_awt_dists.global_attribute4
,rec_awt_dists.global_attribute5
,rec_awt_dists.global_attribute6
,rec_awt_dists.global_attribute7
,rec_awt_dists.global_attribute8
,rec_awt_dists.global_attribute9
,rec_awt_dists.global_attribute10
,rec_awt_dists.global_attribute11
,rec_awt_dists.global_attribute12
,rec_awt_dists.global_attribute13
,rec_awt_dists.global_attribute14
,rec_awt_dists.global_attribute15
,rec_awt_dists.global_attribute16
,rec_awt_dists.global_attribute17
,rec_awt_dists.global_attribute18
,rec_awt_dists.global_attribute19
,rec_awt_dists.global_attribute20
/* End of fix for bug#8462050*/
,DECODE(P_Calling_Module, 'CANCEL INVOICE',
DECODE(rec_awt_dists.awt_invoice_payment_id, NULL, 'Y', NULL), NULL) --bug 9781126
);
<>
DECLARE
reversed_withholding NUMBER := -rec_awt_dists.amount;
SELECT payment_num
FROM ap_invoice_payments
WHERE invoice_payment_id = InvPaymId;
SELECT APS.gross_amount
, NVL(APS.inv_curr_gross_amount, APS.gross_Amount) inv_curr_gross_amount
, APS.amount_remaining
, AI.payment_currency_code
FROM ap_payment_schedules APS,
ap_invoices AI
WHERE AI.invoice_id = InvId
AND AI.invoice_id = APS.invoice_id
AND APS.payment_num = NVL(PaymNum, APS.payment_num) /* Bug 5300858 */
FOR UPDATE of APS.gross_amount, APS.inv_curr_gross_amount, APS.amount_remaining;
DBG_Loc VARCHAR2(30) := 'Update_Payment_Schedule';
debug_info := 'Update the payment schedule';
UPDATE ap_payment_schedules
SET amount_remaining = (amount_remaining +
ap_utilities_pkg.ap_round_currency(
reversed_withholding *
payment_cross_rate,
rec_payment_sched.payment_currency_code))
, payment_status_flag = decode(amount_remaining + -- Bug 8300099/4959558
ap_utilities_pkg.ap_round_currency(reversed_withholding * payment_cross_rate,
rec_payment_sched.payment_currency_code),gross_amount,'N','P')
WHERE CURRENT of c_payment_sched;
-- Bug 8300099/7518063 : Added below update statement
UPDATE ap_invoices
SET payment_status_flag = AP_INVOICES_UTILITY_PKG.get_payment_status( rec_awt_dists.invoice_id )
WHERE invoice_id = rec_awt_dists.invoice_id ;
END Update_Payment_Schedule;
<>
DECLARE
CURSOR c_awt_bucket (VendorId IN NUMBER,
Period IN VARCHAR2,
TaxCode IN VARCHAR2) IS
SELECT gross_amount_to_date,
withheld_amount_to_date
FROM ap_awt_buckets
WHERE vendor_id = VendorId
AND period_name = Period
AND tax_name = TaxCode
FOR UPDATE;
DBG_Loc VARCHAR2(30) := 'Update_Bucket';
SELECT base_currency_code
INTO l_func_currency_code
FROM ap_system_parameters
WHERE org_id = l_org_id;
SELECT ai.exchange_rate
INTO l_invoice_exchange_rate
FROM ap_invoices ai, ap_invoice_payments aip
WHERE ai.invoice_id = aip.invoice_id
AND aip.invoice_payment_id = rec_awt_dists.awt_invoice_payment_id;
debug_info := 'Update the AWT bucket';
UPDATE ap_awt_buckets
SET
/* gross_amount_to_date = (gross_amt_to_date -
ap_utilities_pkg.ap_round_currency(
rec_awt_dists.awt_gross_amount*
NVL(l_invoice_exchange_rate,1),
l_func_currency_code )), */
withheld_amount_to_date = (withheld_amt_to_date+
ap_utilities_pkg.ap_round_currency(
rec_awt_dists.amount*NVL(l_invoice_exchange_rate,1),
l_func_currency_code ))
WHERE CURRENT OF c_awt_bucket;
END Update_Bucket;
SELECT create_awt_invoices_type,create_awt_dists_type --bug7685907
FROM ap_system_parameters_all
where org_id = p_org_id; --bug14404025
P_Last_Updated_By => P_Last_Updated_By,
P_Last_Update_Login => P_Last_Update_Login,
P_Program_Application_Id => P_Program_Application_Id,
P_Program_Id => P_Program_Id,
P_Request_Id => P_Request_Id,
P_Calling_Sequence => current_calling_sequence,
P_Calling_Module => p_calling_module,
P_Inv_Line_No => rec_awt_dists.invoice_line_number,
P_Dist_Line_No => curr_line_number,
P_New_Invoice_Id => P_New_Invoice_Id,
P_create_dists => l_create_dists); --bug7685907
P_Last_Updated_By => P_Last_Updated_By,
P_Last_Update_Login => P_Last_Update_Login,
P_Program_Application_Id => P_Program_Application_Id,
P_Program_Id => P_Program_Id,
P_Request_Id => P_Request_Id,
P_Calling_Sequence => current_calling_sequence,
P_Calling_Module => p_calling_module,
P_Inv_Line_No => rec_awt_dists.invoice_line_number,
P_Dist_Line_No => NVL(P_New_dist_Line_No, P_dist_Line_No),
P_New_Invoice_Id => P_New_Invoice_Id,
P_create_dists => l_create_dists); --bug7685907
UPDATE ap_invoice_distributions
SET reversal_flag='Y'
WHERE invoice_distribution_id = rec_awt_dists.invoice_distribution_id
OR parent_reversal_id=rec_awt_dists.invoice_distribution_id;
SELECT count(*)
INTO l_pay_awt_invs_count
FROM ap_invoice_distributions_all
WHERE invoice_id = DECODE(p_calling_module,'VOID PAYMENT',rec_awt_dists.invoice_id,
P_Parent_Id)
AND line_type_lookup_code = 'AWT'
AND awt_flag = 'A'
AND awt_invoice_id is not null;
P_Last_Updated_By => P_Last_Updated_By,
P_Last_Update_Login => P_Last_Update_Login,
P_Program_Application_Id => P_Program_Application_Id,
P_Program_Id => P_Program_Id,
P_Request_Id => P_Request_Id,
P_Calling_Sequence => current_calling_sequence,
P_Calling_Module => p_calling_module,
P_Inv_Line_No => NULL,
P_Dist_Line_No => NULL,
P_New_Invoice_Id => P_New_Invoice_Id, --bug 8266021
P_create_dists => l_create_dists); --bug7685907
P_Last_Updated_By => P_Last_Updated_By,
P_Last_Update_Login => P_Last_Update_Login,
P_Program_Application_Id => P_Program_Application_Id,
P_Program_Id => P_Program_Id,
P_Request_Id => P_Request_Id,
P_Calling_Sequence => current_calling_sequence,
P_Calling_Module => p_calling_module,
P_Inv_Line_No => NULL,
P_Dist_Line_No => NULL,
P_New_Invoice_Id => P_New_Invoice_Id,
P_create_dists => l_create_dists); --bug7685907
UPDATE ap_invoice_distributions
SET awt_withheld_amt = NULL
WHERE invoice_id = P_parent_id
AND NVL(awt_withheld_amt,0) <> 0;
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Awt_Success,
P_dist_Line_No,
P_New_Invoice_Id,
P_New_dist_Line_No);
/*bug13606808, Added the procedure Ap_Undo_Orphan_Distributions to delete the orphan AWT distributions
from ap_awt_temp_distributions_all table and hanlde awt buckets that are effected by them
*/
PROCEDURE Ap_Undo_Orphan_Distributions (
P_Invoice_Id IN NUMBER,
P_Vendor_Id IN NUMBER DEFAULT NULL,
P_Payment_Num IN NUMBER,
P_Checkrun_Name IN VARCHAR2,
P_Undo_Awt_Date IN DATE,
P_Calling_Module IN VARCHAR2,
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER DEFAULT NULL,
P_Program_Id IN NUMBER DEFAULT NULL,
P_Request_Id IN NUMBER DEFAULT NULL,
P_Awt_Success OUT NOCOPY VARCHAR2,
P_checkrun_id in NUMBER DEFAULT NULL)
IS
DBG_Loc VARCHAR2(30) := 'Ap_Undo_Temp_Withholding';
select ai.vendor_id, aatd.accounting_date, aatd.tax_name, aatd.withholding_amount
from ap_awt_temp_distributions_all AATD,
ap_invoices_all AI
where aatd.invoice_id = InvId
and AATD.invoice_id = AI.invoice_id
AND ( PaymNum IS NULL
OR AATD.payment_num = PaymNum)
and P_Calling_Module <> 'AUTOAPPROVAL'
AND (P_Calling_Module = 'QUICKCHECK'
OR (P_Calling_Module = 'AUTOSELECT' and AATD.checkrun_id <> P_checkrun_id))
and AATD.invoice_payment_id is null;
select ai.vendor_id, aatd.accounting_date, aatd.tax_name, aatd.gross_amount
from ap_awt_temp_distributions_all AATD,
ap_invoices_all AI
where aatd.invoice_id = InvId
and AATD.invoice_id = AI.invoice_id
AND ( PaymNum IS NULL
OR AATD.payment_num = PaymNum)
and P_Calling_Module <> 'AUTOAPPROVAL'
AND (P_Calling_Module = 'QUICKCHECK'
OR (P_Calling_Module = 'AUTOSELECT' and AATD.checkrun_id <> P_checkrun_id))
and aatd.awt_related_id =
(
select min(aatdx.awt_related_id) from ap_awt_temp_distributions_all aatdx
where aatdx.invoice_id = InvId
and aatdx.checkrun_name = aatd.checkrun_name
and aatdx.payment_num = aatd.payment_num
and aatdx.checkrun_id = aatd.checkrun_id
and aatdx.group_id = aatd.group_id
)
and AATD.invoice_payment_id is null;
debug_info := 'DELETE orphan AWT temp distributions';
SELECT p.period_name
FROM ap_other_periods P,
ap_tax_codes_all C
WHERE (rec_orphan_awt_buckets_w.accounting_date BETWEEN
p.start_date AND p.end_date)
AND p.period_type = c.awt_period_type
AND c.name = rec_orphan_awt_buckets_w.tax_name
AND p.module = 'AWT';
UPDATE ap_awt_buckets_all
SET
withheld_amount_to_date = withheld_amount_to_date -
NVL(rec_orphan_awt_buckets_w.withholding_amount, 0),
last_update_date = SYSDATE,
last_updated_by = P_Last_Updated_By,
last_update_login = P_Last_Update_Login,
program_update_date = SYSDATE,
program_application_id = P_Program_Application_Id,
program_id = P_Program_Id,
request_id = P_Request_Id
WHERE vendor_id = rec_orphan_awt_buckets_w.vendor_id
AND tax_name = rec_orphan_awt_buckets_w.tax_name
AND period_name = awt_period;
SELECT p.period_name
FROM ap_other_periods P,
ap_tax_codes_all C
WHERE (rec_orphan_awt_buckets_g.accounting_date BETWEEN
p.start_date AND p.end_date)
AND p.period_type = c.awt_period_type
AND c.name = rec_orphan_awt_buckets_g.tax_name
AND p.module = 'AWT';
UPDATE ap_awt_buckets_all
SET
gross_amount_to_date = gross_amount_to_date -
NVL(rec_orphan_awt_buckets_g.gross_amount, 0),
last_update_date = SYSDATE,
last_updated_by = P_Last_Updated_By,
last_update_login = P_Last_Update_Login,
program_update_date = SYSDATE,
program_application_id = P_Program_Application_Id,
program_id = P_Program_Id,
request_id = P_Request_Id
WHERE vendor_id = rec_orphan_awt_buckets_g.vendor_id
AND tax_name = rec_orphan_awt_buckets_g.tax_name
AND period_name = awt_period;
DELETE
FROM ap_awt_temp_distributions_all AATD
where aatd.invoice_id = P_Invoice_Id
AND (P_Payment_Num IS NULL
OR AATD.payment_num = P_Payment_Num)
AND P_Calling_Module <> 'AUTOAPPROVAL'
AND (P_Calling_Module = 'QUICKCHECK'
OR (P_Calling_Module = 'AUTOSELECT' and AATD.checkrun_id <> P_checkrun_id))
AND AATD.invoice_payment_id is null;