[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,
P_Program_Id IN NUMBER,
P_Request_Id IN NUMBER,
P_Calling_Sequence IN VARCHAR2)
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
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))
ORDER BY AATD.group_id,
AATD.tax_name,
AATD.tax_rate_id
FOR UPDATE of AATD.invoice_id;
SELECT AI.set_of_books_id
, 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 = AI.vendor_id
AND AI.invoice_id = InvId
FOR UPDATE of AI.invoice_id;
SELECT MAX(line_number) curr_inv_line_number
FROM ap_invoice_lines_all
WHERE (invoice_id = InvId);
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 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';
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,
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,
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_dists.accounting_date,
rec_temp_dists.period_name,
'N',
rec_invoice.set_of_books_id,
ap_utilities_pkg.ap_round_currency(
-rec_temp_dists.withholding_amount/rec_invoice.exchange_rate,
p_currency_code),
ap_utilities_pkg.ap_round_currency(
-rec_temp_dists.base_withholding_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_dists.invoice_payment_id,NULL, rec_temp_dists.group_id,NULL), --7230158
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,
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_temp_dists.org_id, --7230158
decode (rec_temp_dists.invoice_payment_id,NULL,NULL,rec_temp_dists.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 --bug6749513 Removed for bug7719929
)
VALUES
(
rec_temp_dists.accounting_date
,'N'
,'N'
,'N'
,'N'
,1 -- 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(
-rec_temp_dists.withholding_amount/rec_invoice.exchange_rate,
p_currency_code)
,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/rec_invoice.exchange_rate,
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'
--,'CANDIDATE' --bug6749513 Removed for bug7719929
);
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);
<>
DECLARE
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',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)
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, --Bug6660355
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
FROM ap_invoice_distributions_all APID,
ap_tax_codes_all ATC,
ap_invoices_all AI
WHERE (APID.invoice_id = InvId)
AND (APID.invoice_line_number = NVL(P_Inv_Line_No,invoice_line_number))
AND (APID.distribution_line_number = NVL(P_dist_Line_No,distribution_line_number))
AND (APID.line_type_lookup_code = 'AWT')
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.amount <> decode (NVL(ATC.suppress_zero_amount_flag,
'N'), 'Y', 0 , APID.amount +1)
AND NVL(APID.reversal_flag, 'N') <> 'Y'
AND APID.AWT_ORIGIN_GROUP_ID = DECODE(P_calling_module,'AUTOAPPROVAL',AI.awt_group_id,
'CANCEL INVOICE',AI.awt_group_id,'CONFIRM',DECODE(P_create_dists,'APPROVAL',
AI.awt_group_id, AI.pay_awt_group_id),
'QUICKCHECK', DECODE(P_create_dists,'APPROVAL',
AI.awt_group_id,AI.pay_awt_group_id), AI.pay_awt_group_id) --6660355
--Bug 7685907 Added Decode for Confirm and Quickcheck
FOR UPDATE of awt_invoice_id;
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
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;
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_lines.invoice_line_number)
||' - '|| to_char(rec_awt_lines.distribution_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_lines.invoice_line_number)
||' - '||to_char(rec_awt_lines.distribution_line_number)
)
,rec_awt_lines.set_of_books_id
,base_currency
,ta_payment_currency_code
,c_payment_cross_rate
,-NVL(rec_awt_lines.base_amount, rec_awt_lines.amount)
,gl_currency_api.convert_amount(
base_currency,
ta_payment_currency_code,
rec_awt_lines.accounting_date,
c_payment_cross_rate_type,
-NVL(rec_awt_lines.base_amount, rec_awt_lines.amount))
,c_payment_cross_rate_type
,rec_awt_lines.accounting_date
,tax_authority_site_id
,0
,0
,NVL(P_Payment_Date,rec_awt_lines.accounting_date)
,substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION', 'AWT'),1,25)
,'AWT' -- It was: decode(sign(rec_awt_lines.amount),1,'CREDIT','STANDARD')
,new_invoice_base_descr
,rec_awt_lines.batch_id
,decode(sign(-NVL(rec_awt_lines.base_amount, rec_awt_lines.amount)),
-1, 0, -NVL(rec_awt_lines.base_amount, rec_awt_lines.amount))
,ta_terms_id
,decode(ta_terms_date_basIS
,'Current', SYSDATE
,'Invoice', NVL(p_payment_date,
rec_awt_lines.accounting_date)
,'Goods Received', NVL(goods_received_date,
rec_awt_lines.accounting_date)
,'Invoice Received', NVL(invoice_received_date,
rec_awt_lines.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_lines.accounting_date)
,'Y'
,'NOT REQUIRED'
,rec_awt_lines.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_lines.invoice_line_number),
'AUTO INVOICE CREATION',
'D',
'NOT MATCHED',
'N',
NVL(P_Payment_Date,rec_awt_lines.accounting_date),
NVL(ap_utilities_pkg.get_current_gl_date(P_Payment_Date, rec_awt_lines.org_id),
rec_awt_lines.period_name),
'N',
rec_awt_lines.set_of_books_id,
-NVL(rec_awt_lines.base_amount, rec_awt_lines.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,
rec_awt_lines.org_id);
debug_info := 'Insert INTO ap_invoice_distributions';
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'
,1 -- distribution_line_number
,rec_awt_lines.dist_code_combination_id
,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'
,'CANDIDATE'); --bug7719929
,P_Last_Updated_By
,P_Last_Updated_By
,ta_payment_priority
,rec_awt_lines.batch_id
,inv_terms_date
,-NVL(rec_awt_lines.base_amount
,rec_awt_lines.amount
)
,gl_currency_api.convert_amount(
base_currency,
ta_payment_currency_code,
rec_awt_lines.accounting_date,
c_payment_cross_rate_type,
-NVL(rec_awt_lines.base_amount, rec_awt_lines.amount))
,c_payment_cross_rate
,NULL
,nvl(l_PAYMENT_METHOD_CODE,'CHECK')
,base_currency
,ta_payment_currency_code
,'ap_do_withholding');
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_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';
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
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.awt_group_id IS NOT NULL)
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
INTO l_invoice_amount, l_amount_remaining
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(aid.base_amount,aid.amount))
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(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
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 = asi.checkrun_id
AND ibydocs.calling_app_doc_unique_ref2 = asi.invoice_id
AND ibydocs.calling_app_doc_unique_ref3 = asi.payment_num
and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id
and ibydocs.org_id = p_org_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 => 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';
,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', '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'))
FOR UPDATE;
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.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)
, 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;
IF (P_Calling_Module = 'AUTOSELECT') THEN
DECLARE
CURSOR c_curr_code (l_checkrun_name IN VARCHAR2,
l_invoice_id IN NUMBER,
l_payment_num IN NUMBER,
l_checkrun_id in number) IS
SELECT ASI.payment_currency_code,
ASI.invoice_exchange_rate,
ASI.payment_cross_rate
FROM ap_SELECTed_invoices_all ASI
WHERE ASI.checkrun_name = l_checkrun_name
AND ASI.invoice_id = l_invoice_id
AND ASI.payment_num = l_payment_num
and asi.checkrun_id = l_checkrun_id;
debug_info := 'Update ap SELECTed invoices';
UPDATE ap_SELECTed_invoices_all
SET payment_amount = payment_amount +
NVL(l_withholding_amount,0),
proposed_payment_amount = proposed_payment_amount +
NVL(l_withholding_amount,0),
amount_remaining = amount_remaining +
NVL(l_withholding_amount,0),
withholding_amount = 0
WHERE checkrun_name = rec_temp.checkrun_name
AND invoice_id = rec_temp.invoice_id
AND payment_num = rec_temp.payment_num
and checkrun_id = rec_temp.checkrun_id;
debug_info := 'Delete the AWT temp distribution';
DELETE ap_awt_temp_distributions_all
WHERE invoice_id = rec_temp.invoice_id
AND group_id = rec_temp.group_id
AND tax_name = rec_temp.tax_name
AND ( ( (checkrun_name = NVL(rec_temp.checkrun_name, checkrun_name))
AND (payment_num = NVL(rec_temp.payment_num, payment_num))
and (checkrun_id = nvl(rec_temp.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
FROM ap_invoice_distributions AID,
ap_tax_codes TC,
ap_invoices AI
WHERE AID.invoice_id = ParentId
AND TC.tax_id (+) = AID.withholding_tax_code_id /* Bug 5382525 */
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
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';
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
, 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
)
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'
);
<>
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))
WHERE CURRENT of c_payment_sched;
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;
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;
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;
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);