[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_MODULE_NAME CONSTANT VARCHAR2(80) := 'AP.PLSQL.AP_AUTOSELECT_PKG';
SELECT nvl(SUM(amount),0)
INTO l_awt_amount
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND awt_invoice_payment_id IS NULL
AND line_type_lookup_code = 'AWT';
SELECT nvl(SUM(gross_amount),1)
INTO l_schedules_sum
FROM ap_payment_schedules_all
WHERE invoice_id = p_invoice_id;
SELECT gross_amount
INTO l_gross_amount
FROM ap_payment_schedules_all
WHERE invoice_id = p_invoice_id
AND payment_num = p_payment_num;
CURSOR selected_inv(p_checkrun_id varchar2) IS
SELECT asi.invoice_id
,asi.vendor_id
,asi.vendor_site_id
,asi.remit_to_supplier_site_id
FROM ap_selected_invoices_all asi,ap_invoices_all ai
WHERE ai.invoice_id=asi.invoice_id
AND ai.invoice_type_lookup_code in ('STANDARD','PREPAYMENT')
AND AP_UTILITIES_PKG.GET_CCR_STATUS(asi.vendor_id,'S') <> 'F'
AND asi.checkrun_id=p_checkrun_id;
OPEN selected_inv(p_checkrun_id);
FETCH selected_inv
BULK COLLECT INTO ccr_rec_info.invoice_id_tab
,ccr_rec_info.vendor_id_tab
,ccr_rec_info.vendor_site_id_tab
,ccr_rec_info.remit_to_supplier_site_id_tab
LIMIT 1000;
UPDATE Ap_Selected_Invoices_All ASI
SET ASI.ok_to_pay_flag = 'N',
ASI.dont_pay_reason_code = 'CCR_REG_EXPIRED'
WHERE ASI.checkrun_id = p_checkrun_id
AND ASI.invoice_id=ccr_rec_list.invoice_id_tab(i);
EXIT WHEN selected_inv%NOTFOUND;
CLOSE selected_inv;
SELECT ASI.invoice_id, ASI.payment_num, ASI.vendor_id,
ASI.vendor_site_id, ASI.vendor_num, ASI.vendor_name,
ASI.vendor_site_code, ASI.address_line1, ASI.address_line2,
ASI.address_line3, ASI.address_line4, ASI.city, ASI.state, ASI.zip,
ASI.invoice_num, ASI.voucher_num,
-- ASI.payment_priority, -- Bug 5139574
nvl(ASI.payment_priority, 99), ASI.province,
ASI.country, ASI.withholding_status_lookup_code,
ASI.attention_ar_flag, ASI.set_of_books_id,
ASI.invoice_exchange_rate, ASI.payment_cross_rate,
ASI.customer_num, asi.external_bank_account_id, ASI.ok_to_pay_flag,
round(LEAST(TRUNC(P_check_date),ADD_MONTHS(TRUNC(due_date),12))
- TRUNC(due_date)), /*Bug 5124784 */
/* annual_interest_rate, Bug#12835170 */
/* Added for Bug#12835170 Start */
(
SELECT annual_interest_rate
FROM ap_interest_periods aip
WHERE trunc(due_date+1) BETWEEN trunc(start_date) AND trunc(end_date)
) annual_interest_rate,
/* Added for Bug#12835170 End */
AI.invoice_currency_code,
ASI.payment_currency_code,
/* bug 5233279. For Federal Installation Exclusive payment Flag is required */
decode(Ap_Payment_Util_Pkg.is_federal_installed(AI.org_id),
'Y', AI.exclusive_payment_flag, 'N'),
asp.interest_accts_pay_ccid,
ai.org_id
FROM /* ap_interest_periods, Bug#12835170 */
ap_invoices AI, --Bug6040657. Changed from ap_invoices_all to ap_invoices
ap_selected_invoices_all ASI,
po_vendors pov,
ap_system_parameters_all asp
WHERE ASI.checkrun_id = P_checkrun_id
AND ASP.auto_calculate_interest_flag = 'Y'
AND ASP.org_id = asi.org_id
AND TRUNC(P_check_date) > TRUNC(due_date)
/* AND (trunc(due_date)+1) BETWEEN trunc(start_date) AND trunc(end_date)
AND (NVL(payment_amount,0) *
POWER(1 + (annual_interest_rate/(12 * 100)),
TRUNC((LEAST(P_check_date,
ADD_MONTHS(due_date,12))
- due_date) / 30))
*
(1 + ((annual_interest_rate/(360 * 100)) *
MOD((LEAST(P_check_date,
ADD_MONTHS(due_date,12))
- due_date)
, 30))))
- NVL(payment_amount,0) >= NVL(asp.interest_tolerance_amount,0) Bug#12835170 */
AND ASI.vendor_id = pov.vendor_id
AND pov.auto_calculate_interest_flag = 'Y'
AND AI.invoice_id = ASI.invoice_id
AND AI.invoice_type_lookup_code <> 'PAYMENT REQUEST';
l_city ap_selected_invoices_all.city%type; --6708281
l_country ap_selected_invoices_all.country%type; --6708281
l_zip ap_selected_invoices_all.zip%type; --6708281
SELECT substrb(l1.displayed_field, 1, 25),
substrb(l2.displayed_field, 1, 10),
substrb(l3.displayed_field, 1, 5),
substrb(l4.displayed_field, 1, 25)
INTO l_nls_interest,
l_nls_days,
l_nls_percent,
l_nls_int
FROM ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4
WHERE l1.lookup_type = 'NLS TRANSLATION'
AND l1.lookup_code = 'INTEREST'
AND l2.lookup_type = 'NLS TRANSLATION'
AND l2.lookup_code = 'DAYS'
AND l3.lookup_type = 'NLS TRANSLATION'
AND l3.lookup_code = 'PERCENT'
AND l4.lookup_type = 'NLS TRANSLATION'
AND l4.lookup_code = 'INT';
SELECT nvl(payment_amount,0), nvl(amount_remaining,0),
nvl(discount_amount,0),nvl(discount_amount_remaining,0)
INTO l_payment_amount, l_amount_remaining,
l_discount_taken, l_discount_available
FROM ap_selected_invoices_all asi
WHERE asi.invoice_id = l_invoice_id
AND asi.payment_num = l_payment_num
and asi.checkrun_id = p_checkrun_id;
SELECT count(*)
INTO l_existing_interest_count
FROM ap_invoice_relationships
WHERE original_invoice_id = l_invoice_id;
SELECT count(*)
INTO l_proposed_interest_count
FROM ap_selected_invoices
WHERE original_invoice_id = to_char(l_invoice_id); --4388916
INSERT INTO ap_selected_invoices_all
(checkrun_name,
invoice_id,
vendor_id,
vendor_site_id,
vendor_num,
vendor_name,
vendor_site_code,
address_line1,
address_line2,
address_line3,
address_line4,
city,
state,
zip,
invoice_num,
voucher_num,
ap_ccid,
payment_priority,
province,
country,
withholding_status_lookup_code,
attention_ar_flag,
set_of_books_id,
invoice_exchange_rate,
payment_cross_rate,
customer_num,
payment_num,
last_update_date,
last_updated_by,
invoice_date,
invoice_amount,
amount_remaining,
amount_paid,
discount_amount_taken,
due_date,
invoice_description,
discount_amount_remaining,
payment_amount,
proposed_payment_amount,
discount_amount,
ok_to_pay_flag,
always_take_discount_flag,
amount_modified_flag,
original_invoice_id,
original_payment_num,
creation_date,
created_by,
exclusive_payment_flag,
org_id,
external_bank_account_id,
checkrun_id,
payment_currency_code,
affects_rejection_level)
SELECT
P_checkrun_name,
ap_invoices_s.NEXTVAL,
l_vendor_id,
l_site_id,
l_int_vendor_num,
l_int_vendor_name,
l_site_code,
l_address_line1,
l_address_line2,
l_address_line3,
l_address_line4,
l_city,
l_state,
l_zip,
SUBSTRB(SUBSTRB(l_int_invoice_num,
1,(50 - LENGTHB('-' || l_nls_int ||
TO_CHAR(l_existing_interest_count +
l_proposed_interest_count + 1))))
|| '-' || l_nls_int || TO_CHAR(l_existing_interest_count +
l_proposed_interest_count + 1),1,50),
l_voucher_num,
l_interest_ap_ccid,
l_payment_priority,
l_province,
l_country,
l_awt_status_lookup_code,
l_attention_ar_flag,
l_set_of_books_id,
l_invoice_exchange_rate,
l_payment_cross_rate,
l_customer_num,
1,
SYSDATE,
-- Bug 7383484 (Base bug 7296715)
-- The User Id is hardcoded to 5 (APPSMGR). It is changed to populate correct value.
-- '5',
FND_GLOBAL.USER_ID,
p_check_date,
l_inv_curr_int_amt,
l_int_invoice_amt,
0,
0,
p_check_date,
SUBSTRB(l_nls_interest|| ' ' || to_char(l_int_invoice_days)
|| ' ' || l_nls_days || to_char(l_int_invoice_rate)
|| l_nls_percent,1,50),
0,
l_int_invoice_amt,
l_int_invoice_amt,
0,
l_ok_to_pay_flag,
'N',
'N',
l_invoice_id,
l_payment_num,
SYSDATE,
-- Bug 7383484 (Base bug 7296715)
--'5',
FND_GLOBAL.USER_ID,
l_exclusive_payment_flag,
l_org_id,
l_external_bank_account_id,
p_checkrun_id,
l_pay_currency_code,
'N'
--bug12349626
FROM sys.dual;
l_debug_info := 'delete unselected invoices';
delete from ap_unselected_invoices_all
where checkrun_id = p_checkrun_id;
l_debug_info := 'deleted selected invoices';
delete from ap_selected_invoices_all
where checkrun_id = p_checkrun_id;
l_debug_info := 'update payment schedules';
update ap_payment_schedules_all
set checkrun_id = null
where checkrun_id = p_checkrun_id;
select invoice_id, payment_num, dont_pay_reason_code, org_id
from ap_selected_invoices_all
where checkrun_id = p_checkrun_id
and ok_to_pay_flag = 'N';
insert into ap_unselected_invoices_all(
checkrun_id,
invoice_id,
payment_num,
dont_pay_reason_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
org_id)
values(
p_checkrun_id,
l_remove_invoices(i).invoice_id,
l_remove_invoices(i).payment_num ,
l_remove_invoices(i).dont_pay_reason_code,
sysdate,
5,
5,
sysdate,
l_remove_invoices(i).org_id);
update ap_payment_schedules_all
set checkrun_id = null
where invoice_id = l_remove_invoices(i).invoice_id
and payment_num = l_remove_invoices(i).payment_num
and checkrun_id = p_checkrun_id;
delete from ap_selected_invoices_all
where invoice_id = l_remove_invoices(i).invoice_id
and payment_num = l_remove_invoices(i).payment_num
and checkrun_id = p_checkrun_id;
PROCEDURE insert_unselected(p_payment_process_request_name in VARCHAR2,
p_hi_payment_priority in number,
p_low_payment_priority in number,
p_invoice_batch_id in number,
p_inv_vendor_id in number,
p_inv_exchange_rate_type in varchar2,
p_payment_method in varchar2,
p_supplier_type in varchar2,
p_le_group_option in varchar2,
p_ou_group_option in varchar2,
p_currency_group_option in varchar2,
p_pay_group_option in varchar2,
p_zero_invoices_allowed in varchar2,
p_check_date in date,
p_checkrun_id in number,
p_current_calling_sequence in varchar2,
p_party_id in number,
p_pay_thru_date_char in varchar2,
p_pay_from_date_char in varchar2,
p_check_date_char in varchar2,
p_disc_pay_thru_char in varchar2
) IS
l_invoice_id number;
l_current_calling_sequence := p_current_calling_sequence||'<- insert unselected';
l_debug_info := 'open unselected_invoices';
/* The query for unselected invoices has been made dynamic for */
/* performance improvements, using native dynamic sqls and ref */
/* cursors. */
/* */
/* Since use of native sql requires a knowledge of the using */
/* clause and hence the number of binds before hand, please make */
/* sure that any modifications to the sql string, ensures that */
/* the number of binds remain constant for all cases of input */
/* parameters (selection criteria) */
/* */
/* Currently this has been achieved using: */
/* nvl(bind, -9999) = -9999 */
/* */
/* If for some reason it is not feasible to achieve a constant number */
/* for a later change please connsider */
/* a. Eliminating binds, by joing to ap_invoice_selection_criteria_all */
/* b. Using DBMS_SQL */
/**************************************************************************/
l_sql_stmt :=
' SELECT '||l_hint||' '||
' ai.invoice_id, '||
' ps.payment_num, '||
' ps.hold_flag, '||
' sites.hold_all_payments_flag, '||
' ap_utilities_pkg.get_invoice_status(ai.invoice_id, null), '||
' ai.wfapproval_status, '||
' ai.org_id, '||
' ps.due_date, '||
' ps.discount_amount_available, '||
' ps.discount_date '||
' FROM ap_supplier_sites_all sites, '||
' ap_invoices ai, '|| /* inv, '|| Commented for bug#9182499 GSCC Error File.Sql.6 Bug6040657. Changed from ap_invoices_all to ap_invoices */
' ap_payment_schedules ps, '||
' ap_suppliers suppliers, '||
' hz_parties hz '||
' where ai.invoice_id = ps.invoice_id '||
' AND sites.vendor_site_id(+) = ai.vendor_site_id '||
' AND suppliers.vendor_id(+) = ai.vendor_id '||
' AND ai.party_id = hz.party_id '||
/* ' AND ps.payment_status_flag BETWEEN ''N'' AND ''P'' '||
' AND ai.payment_status_flag BETWEEN ''N'' AND ''P'' '|| Commented for bug#11848050 */
' AND ps.payment_status_flag IN (''N'', ''P'') '|| /* Added for bug#11848050 */
' AND ai.payment_status_flag IN (''N'', ''P'') '|| /* Added for bug#11848050 */
' AND NVL(ps.payment_priority, 99) BETWEEN :p_hi_payment_priority '||
' AND :p_low_payment_priority '||
' AND ai.cancelled_date is null ';
' AND (ai.legal_entity_id in (select /*+ push_subq * / legal_entity_id '||
' from ap_le_group '||
' where checkrun_id = :p_checkrun_id) '||
' or :p_le_group_option = ''ALL'') '||
' AND (ai.org_id in (select /*+ push_subq * / org_id '||
' from AP_OU_GROUP '||
' where checkrun_id = :p_checkrun_id) '||
' or :p_ou_group_option = ''ALL'') '||
' AND (ai.payment_currency_code in (select /*+ push_subq * / currency_code '||
' from AP_CURRENCY_GROUP '||
' where checkrun_id = :p_checkrun_id) '||
' or :p_currency_group_option = ''ALL'') 'Commented for Bug#11848050 */ ;
' AND inv.pay_group_lookup_code in (select / *+ leading(apg) cardinality(apg 1) * / vendor_pay_group '||
' from AP_PAY_GROUP apg'|| --bug9087739, added alias for AP_PAY_GROUP
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
' ( select '|| /* Added for bug#12773508 */
/* ' ( select / *+ leading(apg) cardinality(apg 1) * / '|| Commented for bug#12773508 */
' apg.vendor_pay_group, mo.ORGANIZATION_ID '||
' from AP_PAY_GROUP apg, MO_GLOB_ORG_ACCESS_TMP mo '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id '||
' AND ai.org_id = mo.organization_id) '; /* Added for bug#11848050 */
' (select /*+ push_subq */ legal_entity_id '||
' from ap_le_group '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
' (select /*+ push_subq */ org_id '||
' from AP_OU_GROUP '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
' (select /*+ push_subq */ currency_code '||
' from AP_CURRENCY_GROUP '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
insert into ap_unselected_invoices_all(
checkrun_id,
invoice_id,
payment_num,
dont_pay_reason_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
org_id)
values(
p_checkrun_id,
l_invoice_id,
l_payment_num ,
'NEEDS_INVOICE_VALIDATION',
sysdate,
5,
5,
sysdate,
l_org_id);
insert into ap_unselected_invoices(
checkrun_id,
invoice_id,
payment_num,
dont_pay_reason_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
org_id)
values(
p_checkrun_id,
l_invoice_id,
l_payment_num ,
'FAILED_INVOICE_VALIDATION',
sysdate,
5,
5,
sysdate,
l_org_id);
insert into ap_unselected_invoices(
checkrun_id,
invoice_id,
payment_num,
dont_pay_reason_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
org_id)
values(
p_checkrun_id,
l_invoice_id,
l_payment_num ,
'NEEDS_APPROVAL',
sysdate,
5,
5,
sysdate,
l_org_id);
insert into ap_unselected_invoices(
checkrun_id,
invoice_id,
payment_num,
dont_pay_reason_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
org_id)
values(
p_checkrun_id,
l_invoice_id,
l_payment_num ,
'APPROVER_REJECTED',
sysdate,
5,
5,
sysdate,
l_org_id);
insert into ap_unselected_invoices(
checkrun_id,
invoice_id,
payment_num,
dont_pay_reason_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
org_id)
values(
p_checkrun_id,
l_invoice_id,
l_payment_num ,
'SCHEDULED_PAYMENT_HOLD',
sysdate,
5,
5,
sysdate,
l_org_id);
insert into ap_unselected_invoices(
checkrun_id,
invoice_id,
payment_num,
dont_pay_reason_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
org_id)
values(
p_checkrun_id,
l_invoice_id,
l_payment_num ,
'SUPPLIER_SITE_HOLD',
sysdate,
5,
5,
sysdate,
l_org_id);
insert into ap_unselected_invoices(
checkrun_id,
invoice_id,
payment_num,
dont_pay_reason_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
org_id)
select
p_checkrun_id,
l_invoice_id,
l_payment_num ,
'DISCOUNT_RATE_TOO_LOW',
sysdate,
5,
5,
sysdate,
l_org_id
from dual
where fv_econ_benf_disc.ebd_check(p_payment_process_request_name, l_invoice_id,
p_check_date, l_due_date, l_discount_amount_available, l_discount_date) = 'N';
END INSERT_UNSELECTED;
select nvl(asi.exclusive_payment_flag,'N')exclusive_payment_flag,
asi.org_id,
asi.payment_amount,
asi.vendor_site_id,
ai.party_id,
ai.party_site_id,
asi.payment_currency_code,
aps.payment_method_code,
nvl(aps.external_bank_account_id,-99) external_bank_account_id,
-- As per the discussion with Omar/Jayanta, we will only
-- have payables payment function and no more employee expenses
-- payment function.
nvl(ai.payment_function, 'PAYABLES_DISB') payment_function,
nvl(ai.pay_proc_trxn_type_code, decode(ai.invoice_type_lookup_code,'EXPENSE REPORT',
'EMPLOYEE_EXP','PAYABLES_DOC')) pay_proc_trxn_type_code,
asi.invoice_id,
asi.payment_num,
asi.payment_grouping_number,
NVL(asi.ok_to_pay_flag,'Y') ok_to_pay_flag,
asi.proposed_payment_amount,
fv.beneficiary_party_id, --5017076
ipm.support_bills_payable_flag, -- Bug 5357689, 5479979
(trunc(aps.due_date) + nvl(ipm.maturity_date_offset_days,0)) due_date -- Bug 5357689
--Bug 543942 added NVL in the above scenario
from ap_selected_invoices_all asi,
ap_invoices ai, --Bug6040657. Changed from ap_invoices_all to ap_invoices
ap_inv_selection_criteria_all aisc,
ap_payment_schedules_all aps,
fv_tpp_assignments_v fv, --5017076
iby_payment_methods_vl ipm -- Bug 5357689
where asi.invoice_id = ai.invoice_id
and aps.invoice_id = asi.invoice_id
and aps.payment_num = asi.payment_num
and asi.checkrun_name = aisc.checkrun_name
and nvl(asi.ok_to_pay_flag,'Y') = 'Y'
and aisc.checkrun_id= p_checkrun_id
and asi.original_invoice_id is null
and fv.beneficiary_supplier_id(+) = ai.vendor_id
and fv.beneficiary_supplier_site_id(+) = ai.vendor_site_id
and ipm.payment_method_code = aps.payment_method_code -- Bug 5357689
order by nvl(asi.exclusive_payment_flag,'N'), --bug8440703
asi.org_id,
asi.vendor_site_id,
ai.party_id,
ai.party_site_id,
asi.payment_currency_code,
aps.payment_method_code,
aps.external_bank_account_id,
payment_function,
/* pay_proc_trxn_type_code, Bug 13442182 */
fv.beneficiary_party_id,
SIGN(asi.invoice_amount) asc, --this will make credit memos first(Bug 16340312) per group
asi.due_date, -- Bug 5479979, Bug 12740398, Bug 16340312
/* DECODE(SIGN(asi.invoice_amount),
-1, TO_CHAR(asi.due_date,'YYYYMMDD'),
asi.invoice_num), */
asi.payment_num;
select nvl(zero_amounts_allowed,'N'), payment_profile_id
into l_maximize_credits_flag, l_payment_profile_id -- Added for bug 9089243
from ap_inv_selection_criteria_all
where checkrun_id = p_checkrun_id;
select nvl(ibcr.group_by_due_date_flag,'N')
into l_group_by_due_date_flag
from iby_acct_pmt_profiles_b ibpp, IBY_PMT_CREATION_RULES ibcr
where ibpp.system_profile_code = ibcr.system_profile_code
and ibpp.payment_profile_id = l_payment_profile_id;
Based on sort order of documents selection, after grouping all the credit memos
code will reach here for grouping standard invocies
*/
l_tmp_cm_total := l_tmp_cm_total + l_documents(i).payment_amount;
l_debug_info := 'update grouping numbers, ok to pay flags, and amounts';
/* Added the hint INDEX(AP_SELECTED_INVOICES_ALL,AP_SELECTED_INVOICES_N1) for bug#8368922 */
update /*+ INDEX(AP_SELECTED_INVOICES_ALL,AP_SELECTED_INVOICES_N1) */ ap_selected_invoices_all
set payment_grouping_number = l_documents(i).payment_grouping_number,
ok_to_pay_flag = l_documents(i).ok_to_pay_flag,
-- proposed_payment_amount = l_documents(i).proposed_payment_amount, 7371792
payment_amount = l_documents(i).proposed_payment_amount,
dont_pay_reason_code = decode(l_documents(i).ok_to_pay_flag,'N',
'CREDIT TOO LOW',null),
last_update_date = sysdate,
-- Bug 7383484 (Base bug 7296715)
-- last_updated_by = 5
last_updated_by = FND_GLOBAL.USER_ID,
due_date = l_documents(i).due_date -- Bug 5357689
where invoice_id = l_documents(i).invoice_id
and payment_num = l_documents(i).payment_num
and checkrun_id= p_checkrun_id;
* Note in a federal instance the selected invoice row for the
* interest takes its exclusive payment flag from the original invoice.
*/
update ap_selected_invoices_all asi
set payment_grouping_number =
(select asi2.payment_grouping_number
from ap_selected_invoices_all asi2
where asi2.invoice_id = asi.original_invoice_id
and asi2.payment_num = asi.original_payment_num
and asi2.checkrun_id = p_checkrun_id)
where asi.checkrun_id = p_checkrun_id
and asi.original_invoice_id is not null
and (Ap_Payment_Util_Pkg.is_federal_installed(asi.org_id) = 'N'
/* following 2 lines added for bug 10318301 */
or (Ap_Payment_Util_Pkg.is_federal_installed(asi.org_id) = 'Y'
and asi.exclusive_payment_flag = 'N'));
update ap_selected_invoices_all asi
set ok_to_pay_flag = 'N',
dont_pay_reason_code = 'CREDIT TOO LOW',
last_update_date = sysdate,
-- Bug 7383484 (Base bug 7296715)
-- last_updated_by = 5
last_updated_by = FND_GLOBAL.USER_ID
where checkrun_id = p_checkrun_id
and payment_grouping_number in
(select asi2.payment_grouping_number
from ap_selected_invoices_all asi2
where asi2.checkrun_id = p_checkrun_id
group by asi2.payment_grouping_number
having sum(asi2.payment_amount) < 0);
UPDATE Ap_Selected_Invoices_All ASI
SET payment_grouping_number = null
WHERE payment_grouping_number NOT IN (
SELECT /*+ HASH_AJ */ payment_grouping_number
FROM Ap_Selected_Invoices_All ASI2
WHERE (ASI2.original_invoice_id is not null or
ASI2.payment_amount < 0)
AND ASI2.ok_to_pay_flag = 'Y'
AND ASI2.checkrun_id = p_checkrun_id
AND ASI2.payment_grouping_number IS NOT NULL)
AND ASI.checkrun_id = p_checkrun_id
AND ASI.payment_grouping_number IS NOT NULL;
/*update ap_selected_invoices_all asi
set payment_grouping_number = null
where payment_grouping_number not in (
select payment_grouping_number
from ap_selected_invoices_all asi2
where (asi2.original_invoice_id is not null or
asi2.payment_amount < 0)
and asi2.ok_to_pay_flag = 'Y'
and checkrun_id = p_checkrun_id)
and checkrun_id = p_checkrun_id; */
* When in place, the update incorrect ungruops credit memos too.
* Thus the following is commented out.
update ap_selected_invoices_all asi
set payment_grouping_number = null
where asi.checkrun_id = p_checkrun_id
and asi.payment_grouping_number is not null
and Ap_Payment_Util_Pkg.is_federal_installed(asi.org_id) = 'Y'
and exists (
select*/ /*+NO_UNNEST *//* NULL
from ap_selected_invoices_all asi2
where asi2.original_invoice_id is not null
and asi2.original_invoice_id = asi.invoice_id
and asi2.ok_to_pay_flag = 'Y'
and asi2.checkrun_id = p_checkrun_id);
update ap_selected_invoices_all asi
set exclusive_payment_flag = 'N'
where asi.checkrun_id = p_checkrun_id
and Ap_Payment_Util_Pkg.is_federal_installed(asi.org_id) = 'N'
and exists (
select NULL
from ap_selected_invoices_all asi2
where asi2.original_invoice_id is not null
and asi2.original_invoice_id = asi.invoice_id
and asi2.ok_to_pay_flag = 'Y'
and asi2.checkrun_id = p_checkrun_id);
l_debug_info := 'select checkrun_id';
select ap_inv_selection_criteria_s.nextval
into p_checkrun_id
from dual;
l_debug_info := 'insert into ap_inv_selection_criteria_all';
insert into ap_inv_selection_criteria_all(
check_date,
pay_thru_date,
hi_payment_priority,
low_payment_priority,
pay_only_when_due_flag,
status,
zero_amounts_allowed, --uncommented for Bug 8899870
zero_invoices_allowed,
vendor_id,
checkrun_id,
pay_from_date,
inv_exchange_rate_type,
exchange_rate_type, --Bug6829191
payment_method_code,
vendor_type_lookup_code,
CREATE_INSTRS_FLAG,
PAYMENT_PROFILE_ID,
bank_account_id,
checkrun_name,
ou_group_option,
le_group_option,
currency_group_option,
pay_group_option,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
template_flag,
template_id,
payables_review_settings,
payments_review_settings,
document_rejection_level_code,
payment_rejection_level_code,
party_id,
request_id, --4737467
payment_document_id, --7315136
transfer_priority, --7315136
settlement_priority, --14726060
ATTRIBUTE_CATEGORY, -- begin 8935712
ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15 -- end 8935712
)
select nvl(p_payment_date,sysdate)+ nvl(addl_payment_days,0) , --4681989 /*Bug 9739226 */
nvl(p_pay_thru_date, sysdate + ADDL_PAY_THRU_DAYS),--4681989
hi_payment_priority,
low_payment_priority,
pay_only_when_due_flag,
'UNSTARTED',
zero_amounts_allowed, --uncommented for Bug 8899870
ZERO_INV_ALLOWED_FLAG,
vendor_id,
p_checkrun_id,
-- start of bug12403039
--nvl(p_pay_from_date, sysdate - ADDL_PAY_FROM_DAYS), --4681989
nvl(p_pay_from_date, sysdate + ADDL_PAY_FROM_DAYS),
-- end of bug12403039
inv_exchange_rate_type,
payment_exchange_rate_type, --Bug6829191
payment_method_code,
vendor_type_lookup_code,
CREATE_INSTRS_FLAG,
PAYMENT_PROFILE_ID,
BANK_ACCOUNT_ID,
template_name ||'-'||to_char(sysdate, 'DD-MON-RRRR HH24:MI:SS'),
ou_group_option,
le_group_option,
currency_group_option,
pay_group_option,
sysdate,
last_updated_by,
last_update_login,
sysdate,
created_by,
'Y',
p_template_id,
payables_review_settings,
payments_review_settings,
document_rejection_level_code,
payment_rejection_level_code,
party_id,
fnd_global.conc_request_id, --4737467
payment_document_id, --7315136
transfer_priority, --7315136
settlement_priority, --14726060
ATTRIBUTE_CATEGORY, -- begin 8885918
ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15 -- end 8885918
from AP_PAYMENT_TEMPLATES
where template_id = p_template_id;
l_debug_info := 'insert into ap_le_group';
insert into ap_le_group (
legal_entity_id,
checkrun_id,
LE_GROUP_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select legal_entity_id,
p_checkrun_id,
AP_LE_GROUP_S.nextval,
sysdate,
alg.created_by,
sysdate,
alg.last_updated_by
from ap_le_group alg,
ap_payment_templates appt
where alg.template_id = p_template_id
and alg.template_id = appt.template_id
and appt.le_group_option = 'SPECIFY';
l_debug_info := 'insert into AP_OU_GROUP';
insert into AP_OU_GROUP (
org_id,
checkrun_id,
OU_GROUP_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select aog.org_id,
p_checkrun_id,
AP_OU_GROUP_S.nextval,
sysdate,
aog.created_by,
sysdate,
aog.last_updated_by
from ap_ou_group aog,
ap_payment_templates appt
where aog.template_id = p_template_id
and aog.template_id = appt.template_id
and appt.ou_group_option = 'SPECIFY';
l_debug_info := 'insert into AP_CURRENCY_GROUP';
insert into AP_CURRENCY_GROUP (
currency_code,
checkrun_id,
CURRENCY_GROUP_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select currency_code,
p_checkrun_id,
AP_CURRENCY_GROUP_S.nextval,
sysdate,
acg.created_by,
sysdate,
acg.last_updated_by
from AP_CURRENCY_GROUP acg,
ap_payment_templates appt
where acg.template_id = p_template_id
and acg.template_id = appt.template_id
and appt.currency_group_option = 'SPECIFY';--Bug6926344
l_debug_info := 'insert into AP_PAY_GROUP';
insert into AP_PAY_GROUP (
vendor_pay_group,
checkrun_id,
PAY_GROUP_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select vendor_pay_group,
p_checkrun_id,
AP_PAY_GROUP_S.nextval,
sysdate,
apg.created_by,
sysdate,
apg.last_updated_by
from AP_PAY_GROUP apg,
ap_payment_templates appt
where apg.template_id = p_template_id
and apg.template_id = appt.template_id
and appt.pay_group_option = 'SPECIFY'; --Bug6926344
PROCEDURE select_invoices (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_checkrun_id in varchar2,
P_template_id in varchar2,
p_payment_date in varchar2,
p_pay_thru_date in varchar2,
p_pay_from_date in varchar2) IS
l_abort varchar2(1);
l_api_name CONSTANT VARCHAR2(100) := 'SELECT_INVOICES';
l_count_inv_selected number;
SELECTION_FAILURE EXCEPTION;
TYPE checkrun_name_t IS TABLE OF AP_SELECTED_INVOICES_ALL.checkrun_name%TYPE INDEX BY BINARY_INTEGER;
TYPE checkrun_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.checkrun_id%TYPE INDEX BY BINARY_INTEGER;
TYPE invoice_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_id%TYPE INDEX BY BINARY_INTEGER;
TYPE payment_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_num%TYPE INDEX BY BINARY_INTEGER;
TYPE last_update_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.last_update_date%TYPE INDEX BY BINARY_INTEGER;
TYPE last_updated_by_t IS TABLE OF AP_SELECTED_INVOICES_ALL.last_updated_by%TYPE INDEX BY BINARY_INTEGER;
TYPE creation_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.creation_date%TYPE INDEX BY BINARY_INTEGER;
TYPE created_by_t IS TABLE OF AP_SELECTED_INVOICES_ALL.created_by%TYPE INDEX BY BINARY_INTEGER;
TYPE last_update_login_t IS TABLE OF AP_SELECTED_INVOICES_ALL.last_update_login%TYPE INDEX BY BINARY_INTEGER;
TYPE vendor_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.vendor_id%TYPE INDEX BY BINARY_INTEGER;
TYPE vendor_site_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.vendor_site_id%TYPE INDEX BY BINARY_INTEGER;
TYPE vendor_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.vendor_num%TYPE INDEX BY BINARY_INTEGER;
TYPE vendor_name_t IS TABLE OF AP_SELECTED_INVOICES_ALL.vendor_name%TYPE INDEX BY BINARY_INTEGER;
TYPE vendor_site_code_t IS TABLE OF AP_SELECTED_INVOICES_ALL.vendor_site_code%TYPE INDEX BY BINARY_INTEGER;
TYPE address_line1_t IS TABLE OF AP_SELECTED_INVOICES_ALL.address_line1%TYPE INDEX BY BINARY_INTEGER;
TYPE city_t IS TABLE OF AP_SELECTED_INVOICES_ALL.city%TYPE INDEX BY BINARY_INTEGER;
TYPE state_t IS TABLE OF AP_SELECTED_INVOICES_ALL.state%TYPE INDEX BY BINARY_INTEGER;
TYPE zip_t IS TABLE OF AP_SELECTED_INVOICES_ALL.zip%TYPE INDEX BY BINARY_INTEGER;
TYPE province_t IS TABLE OF AP_SELECTED_INVOICES_ALL.province%TYPE INDEX BY BINARY_INTEGER;
TYPE country_t IS TABLE OF AP_SELECTED_INVOICES_ALL.country%TYPE INDEX BY BINARY_INTEGER;
TYPE attention_ar_flag_t IS TABLE OF AP_SELECTED_INVOICES_ALL.attention_ar_flag%TYPE INDEX BY BINARY_INTEGER;
TYPE withholding_status_lookup_t IS TABLE OF AP_SELECTED_INVOICES_ALL.withholding_status_lookup_code%TYPE INDEX BY BINARY_INTEGER;
TYPE invoice_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_num%TYPE INDEX BY BINARY_INTEGER;
TYPE invoice_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_date%TYPE INDEX BY BINARY_INTEGER;
TYPE voucher_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.voucher_num%TYPE INDEX BY BINARY_INTEGER;
TYPE ap_ccid_t IS TABLE OF AP_SELECTED_INVOICES_ALL.ap_ccid%TYPE INDEX BY BINARY_INTEGER;
TYPE due_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.due_date%TYPE INDEX BY BINARY_INTEGER;
TYPE discount_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.discount_date%TYPE INDEX BY BINARY_INTEGER;
TYPE invoice_description_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_description%TYPE INDEX BY BINARY_INTEGER;
TYPE payment_priority_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_priority%TYPE INDEX BY BINARY_INTEGER;
TYPE ok_to_pay_flag_t IS TABLE OF AP_SELECTED_INVOICES_ALL.ok_to_pay_flag%TYPE INDEX BY BINARY_INTEGER;
TYPE always_take_disc_flag_t IS TABLE OF AP_SELECTED_INVOICES_ALL.always_take_discount_flag%TYPE INDEX BY BINARY_INTEGER;
TYPE amount_modified_flag_t IS TABLE OF AP_SELECTED_INVOICES_ALL.amount_modified_flag%TYPE INDEX BY BINARY_INTEGER;
TYPE invoice_amount_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_amount%TYPE INDEX BY BINARY_INTEGER;
TYPE payment_cross_rate_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_cross_rate%TYPE INDEX BY BINARY_INTEGER;
TYPE invoice_exchange_rate_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_exchange_rate%TYPE INDEX BY BINARY_INTEGER;
TYPE set_of_books_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.set_of_books_id%TYPE INDEX BY BINARY_INTEGER;
TYPE customer_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.customer_num%TYPE INDEX BY BINARY_INTEGER;
TYPE future_pay_due_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.future_pay_due_date%TYPE INDEX BY BINARY_INTEGER;
TYPE exclusive_payment_flag_t IS TABLE OF AP_SELECTED_INVOICES_ALL.exclusive_payment_flag%TYPE INDEX BY BINARY_INTEGER;
TYPE attribute1_t IS TABLE OF AP_SELECTED_INVOICES_ALL.attribute1%TYPE INDEX BY BINARY_INTEGER;
TYPE attribute_category_t IS TABLE OF AP_SELECTED_INVOICES_ALL.attribute_category%TYPE INDEX BY BINARY_INTEGER;
TYPE org_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.org_id%TYPE INDEX BY BINARY_INTEGER;
TYPE payment_currency_code_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_currency_code%TYPE INDEX BY BINARY_INTEGER;
TYPE external_bank_account_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.external_bank_account_id%TYPE INDEX BY BINARY_INTEGER;
TYPE legal_entity_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.legal_entity_id%TYPE INDEX BY BINARY_INTEGER;
TYPE global_attribute1_t IS TABLE OF AP_SELECTED_INVOICES_ALL.global_attribute1%TYPE INDEX BY BINARY_INTEGER;
TYPE global_attribute_category_t IS TABLE OF AP_SELECTED_INVOICES_ALL.global_attribute_category%TYPE INDEX BY BINARY_INTEGER;
TYPE amount_paid_t IS TABLE OF AP_SELECTED_INVOICES_ALL.amount_paid%TYPE INDEX BY BINARY_INTEGER;
TYPE discount_amount_taken_t IS TABLE OF AP_SELECTED_INVOICES_ALL.discount_amount_taken%TYPE INDEX BY BINARY_INTEGER;
TYPE amount_remaining_t IS TABLE OF AP_SELECTED_INVOICES_ALL.amount_remaining%TYPE INDEX BY BINARY_INTEGER;
TYPE discount_amount_remaining_t IS TABLE OF AP_SELECTED_INVOICES_ALL.discount_amount_remaining%TYPE INDEX BY BINARY_INTEGER;
TYPE payment_amount_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_amount%TYPE INDEX BY BINARY_INTEGER;
TYPE discount_amount_t IS TABLE OF AP_SELECTED_INVOICES_ALL.discount_amount%TYPE INDEX BY BINARY_INTEGER;
TYPE sequence_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.sequence_num%TYPE INDEX BY BINARY_INTEGER;
TYPE dont_pay_reason_code_t IS TABLE OF AP_SELECTED_INVOICES_ALL.dont_pay_reason_code%TYPE INDEX BY BINARY_INTEGER;
TYPE check_number_t IS TABLE OF AP_SELECTED_INVOICES_ALL.check_number%TYPE INDEX BY BINARY_INTEGER;
TYPE bank_account_type_t IS TABLE OF AP_SELECTED_INVOICES_ALL.bank_account_type%TYPE INDEX BY BINARY_INTEGER;
TYPE original_invoice_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.original_invoice_id%TYPE INDEX BY BINARY_INTEGER;
TYPE original_payment_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.original_payment_num%TYPE INDEX BY BINARY_INTEGER;
TYPE bank_account_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.bank_account_num%TYPE INDEX BY BINARY_INTEGER;
TYPE bank_num_t IS TABLE OF AP_SELECTED_INVOICES_ALL.bank_num%TYPE INDEX BY BINARY_INTEGER;
TYPE proposed_payment_amount_t IS TABLE OF AP_SELECTED_INVOICES_ALL.proposed_payment_amount%TYPE INDEX BY BINARY_INTEGER;
TYPE pay_selected_check_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.pay_selected_check_id%TYPE INDEX BY BINARY_INTEGER;
TYPE print_selected_check_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.print_selected_check_id%TYPE INDEX BY BINARY_INTEGER;
TYPE withholding_amount_t IS TABLE OF AP_SELECTED_INVOICES_ALL.withholding_amount%TYPE INDEX BY BINARY_INTEGER;
TYPE invoice_payment_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.invoice_payment_id%TYPE INDEX BY BINARY_INTEGER;
TYPE dont_pay_description_t IS TABLE OF AP_SELECTED_INVOICES_ALL.dont_pay_description%TYPE INDEX BY BINARY_INTEGER;
TYPE transfer_priority_t IS TABLE OF AP_SELECTED_INVOICES_ALL.transfer_priority%TYPE INDEX BY BINARY_INTEGER;
TYPE iban_number_t IS TABLE OF AP_SELECTED_INVOICES_ALL.iban_number%TYPE INDEX BY BINARY_INTEGER;
TYPE payment_grouping_number_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_grouping_number%TYPE INDEX BY BINARY_INTEGER;
TYPE payment_exchange_rate_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_exchange_rate%TYPE INDEX BY BINARY_INTEGER;
TYPE payment_exchange_rate_type_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_exchange_rate_type%TYPE INDEX BY BINARY_INTEGER;
TYPE payment_exchange_date_t IS TABLE OF AP_SELECTED_INVOICES_ALL.payment_exchange_date%TYPE INDEX BY BINARY_INTEGER;
TYPE remit_to_supplier_name_t IS TABLE OF AP_SELECTED_INVOICES_ALL.remit_to_supplier_name%TYPE INDEX BY BINARY_INTEGER;
TYPE remit_to_supplier_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.remit_to_supplier_id%TYPE INDEX BY BINARY_INTEGER;
TYPE remit_to_supplier_site_t IS TABLE OF AP_SELECTED_INVOICES_ALL.remit_to_supplier_site%TYPE INDEX BY BINARY_INTEGER;
TYPE remit_to_supplier_site_id_t IS TABLE OF AP_SELECTED_INVOICES_ALL.remit_to_supplier_site_id%TYPE INDEX BY BINARY_INTEGER;
,last_update_date_l last_update_date_t
,last_updated_by_l last_updated_by_t
,creation_date_l creation_date_t
,created_by_l created_by_t
,last_update_login_l last_update_login_t
,vendor_id_l vendor_id_t
,vendor_site_id_l vendor_site_id_t
,vendor_num_l vendor_num_t
,vendor_name_l vendor_name_t
,vendor_site_code_l vendor_site_code_t
,address_line1_l address_line1_t
,address_line2_l address_line1_t
,address_line3_l address_line1_t
,address_line4_l address_line1_t
,city_l city_t
,state_l state_t
,zip_l zip_t
,province_l province_t
,country_l country_t
,attention_ar_flag_l attention_ar_flag_t
,withholding_status_lookup_l withholding_status_lookup_t
,invoice_num_l invoice_num_t
,invoice_date_l invoice_date_t
,voucher_num_l voucher_num_t
,ap_ccid_l ap_ccid_t
,due_date_l due_date_t
,discount_date_l discount_date_t
,invoice_description_l invoice_description_t
,payment_priority_l payment_priority_t
,ok_to_pay_flag_l ok_to_pay_flag_t
,always_take_disc_flag_l always_take_disc_flag_t
,amount_modified_flag_l amount_modified_flag_t
,invoice_amount_l invoice_amount_t
,payment_cross_rate_l payment_cross_rate_t
,invoice_exchange_rate_l invoice_exchange_rate_t
,set_of_books_id_l set_of_books_id_t
,customer_num_l customer_num_t
,future_pay_due_date_l future_pay_due_date_t
,exclusive_payment_flag_l exclusive_payment_flag_t
,attribute1_l attribute1_t
,attribute2_l attribute1_t
,attribute3_l attribute1_t
,attribute4_l attribute1_t
,attribute5_l attribute1_t
,attribute6_l attribute1_t
,attribute7_l attribute1_t
,attribute8_l attribute1_t
,attribute9_l attribute1_t
,attribute10_l attribute1_t
,attribute11_l attribute1_t
,attribute12_l attribute1_t
,attribute13_l attribute1_t
,attribute14_l attribute1_t
,attribute15_l attribute1_t
,attribute_category_l attribute_category_t
,org_id_l org_id_t
,payment_currency_code_l payment_currency_code_t
,external_bank_account_id_l external_bank_account_id_t
,legal_entity_id_l legal_entity_id_t
,global_attribute1_l global_attribute1_t
,global_attribute2_l global_attribute1_t
,global_attribute3_l global_attribute1_t
,global_attribute4_l global_attribute1_t
,global_attribute5_l global_attribute1_t
,global_attribute6_l global_attribute1_t
,global_attribute7_l global_attribute1_t
,global_attribute8_l global_attribute1_t
,global_attribute9_l global_attribute1_t
,global_attribute10_l global_attribute1_t
,global_attribute11_l global_attribute1_t
,global_attribute12_l global_attribute1_t
,global_attribute13_l global_attribute1_t
,global_attribute14_l global_attribute1_t
,global_attribute15_l global_attribute1_t
,global_attribute16_l global_attribute1_t
,global_attribute17_l global_attribute1_t
,global_attribute18_l global_attribute1_t
,global_attribute19_l global_attribute1_t
,global_attribute20_l global_attribute1_t
,global_attribute_category_l global_attribute_category_t
,amount_paid_l amount_paid_t
,discount_amount_taken_l discount_amount_taken_t
,amount_remaining_l amount_remaining_t
,discount_amount_remaining_l discount_amount_remaining_t
,payment_amount_l payment_amount_t
,discount_amount_l discount_amount_t
,sequence_num_l sequence_num_t
,dont_pay_reason_code_l dont_pay_reason_code_t
,check_number_l check_number_t
,bank_account_type_l bank_account_type_t
,original_invoice_id_l original_invoice_id_t
,original_payment_num_l original_payment_num_t
,bank_account_num_l bank_account_num_t
,bank_num_l bank_num_t
,proposed_payment_amount_l proposed_payment_amount_t
,pay_selected_check_id_l pay_selected_check_id_t
,print_selected_check_id_l print_selected_check_id_t
,withhloding_amount_l withholding_amount_t
,invoice_payment_id_l invoice_payment_id_t
,dont_pay_description_l dont_pay_description_t
,transfer_priority_l transfer_priority_t
,iban_number_l iban_number_t
,payment_grouping_number_l payment_grouping_number_t
,payment_exchange_rate_l payment_exchange_rate_t
,payment_exchange_rate_type_l payment_exchange_rate_type_t
,payment_exchange_date_l payment_exchange_date_t
--Start 8217641
,remit_to_supplier_name_l remit_to_supplier_name_t
,remit_to_supplier_id_l remit_to_supplier_id_t
,remit_to_supplier_site_l remit_to_supplier_site_t
,remit_to_supplier_site_id_l remit_to_supplier_site_id_t
--End 8217641
);
l_current_calling_sequence := 'select invoices';
l_debug_info := 'Select data from ap_invoice_selection_criteria';
SELECT
trunc(check_date),
to_char(check_date, 'DD-MM-YYYY'),
trunc(pay_thru_date),
to_char(pay_thru_date, 'DD-MM-YYYY'),
NVL(hi_payment_priority,1),
NVL(low_payment_priority,99),
DECODE(pay_only_when_due_flag,'Y',
to_date('01/01/80','MM/DD/RR'),
trunc(pay_thru_date)),
DECODE(pay_only_when_due_flag,'Y',
'01-01-1980',
to_char(pay_thru_date, 'DD-MM-YYYY')),
DECODE(status,'SELECTING','N','Y'),
nvl(zero_amounts_allowed,'N'),
nvl(zero_invoices_allowed,'N'),
invoice_batch_id,
vendor_id,
checkrun_name,
trunc(pay_from_date),
to_char(pay_from_date, 'DD-MM-YYYY'),
inv_exchange_rate_type,
payment_method_code,
vendor_type_lookup_code,
ou_group_option,
le_group_option,
currency_group_option,
pay_group_option,
exchange_rate_type,
payables_review_settings,
bank_account_id, --4710933
payment_profile_id,
max_payment_amount,
min_check_amount,
payments_review_settings,
--decode(payment_profile_id,null,'N',nvl(create_instrs_flag,'N')),
nvl(create_instrs_flag,'N'), -- Commented and added for bug 8925444
party_id,
payment_document_id,
/*bug 7519277*/
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
/*bug 7519277*/
INTO
l_check_date,
l_check_date_char,
l_pay_thru_date,
l_pay_thru_date_char,
l_hi_payment_priority,
l_low_payment_priority,
l_disc_pay_thru_date,
l_disc_pay_thru_char,
l_abort,
l_zero_amounts_allowed,
l_zero_invoices_allowed,
l_invoice_batch_id,
l_inv_vendor_id,
l_payment_process_request_name,
l_pay_from_date,
l_pay_from_date_char,
l_inv_exchange_rate_type,
l_payment_method,
l_supplier_type,
l_ou_group_option,
l_le_group_option,
l_currency_group_option,
l_pay_group_option,
l_batch_exchange_rate_type,
l_payables_review_settings,
l_bank_account_id ,
l_payment_profile_id,
l_max_payment_amount,
l_min_check_amount,
l_pay_review_settings_flag,
l_create_instrs_flag,
l_party_id,
l_payment_document_id,
/* bug 7519277*/
l_ATTRIBUTE_CATEGORY,
l_ATTRIBUTE1,
l_ATTRIBUTE2,
l_ATTRIBUTE3,
l_ATTRIBUTE4,
l_ATTRIBUTE5,
l_ATTRIBUTE6,
l_ATTRIBUTE7,
l_ATTRIBUTE8,
l_ATTRIBUTE9,
l_ATTRIBUTE10,
l_ATTRIBUTE11,
l_ATTRIBUTE12,
l_ATTRIBUTE13,
l_ATTRIBUTE14,
l_ATTRIBUTE15
FROM ap_inv_selection_criteria_all
WHERE checkrun_id = l_checkrun_id
AND status = 'UNSTARTED';
raise SELECTION_FAILURE;
UPDATE ap_inv_selection_criteria_all
set status = 'SELECTING',
check_date = TRUNC(check_date),
pay_thru_date = TRUNC(pay_thru_date),
-- Bug 7492768 We need to reset the inv_awt_exists_flag which indicates if the
-- check run contains invoice that has awt.
inv_awt_exists_flag = 'N'
where checkrun_id = l_checkrun_id;
SELECT count(*)
INTO l_encumbrance_flag
FROM financials_system_parameters
WHERE nvl(purch_encumbrance_flag,'N') = 'Y'
AND (org_id in (select org_id
from AP_OU_GROUP
where checkrun_id = l_checkrun_id)
or l_ou_group_option = 'ALL')
AND rownum=1;
/* The selection queries for Autoselect have been made dynamic for */
/* performance improvements, using native dynamic sqls and ref */
/* cursors. */
/* */
/* Since use of native sql requires a knowledge of the using */
/* clause and hence the number of binds before hand, please make */
/* sure that any modifications to the sql string, ensures that */
/* the number of binds remain constant for all cases of input */
/* parameters (selection criteria) */
/* */
/* Currently this has been achieved using: */
/* nvl(bind, -9999) = -9999 */
/* */
/* If for some reason it is not feasible to achieve a constant number */
/* for a later change please consider */
/* a. Eliminating binds, by joing to ap_invoice_selection_criteria_all */
/* b. Using DBMS_SQL */
/**************************************************************************/
if l_encumbrance_flag = 1 then
l_debug_info := 'Open payment schedules cursor - encumbrances are on';
' SELECT '||l_hint||
' :p_checkrun_name checkrun_name '||
' ,:p_checkrun_id checkrun_id '||
' ,ps.invoice_id invoice_id '||
' ,payment_num payment_num '||
' ,SYSDATE last_update_date '||
-- Bug 7296715
-- The User Id is hardcoded to 5 (APPSMGR). It is changed to populate correct value.
-- ,5 last_updated_by
' ,FND_GLOBAL.USER_ID last_updated_by '||
' ,SYSDATE creation_date '||
-- Bug 7296715
-- ,5 created_by
' ,FND_GLOBAL.USER_ID created_by '||
' ,NULL last_update_login '||
' ,ai.vendor_id vendor_id '||
' ,ai.vendor_site_id vendor_site_id '||
' ,suppliers.segment1 vendor_num '||
/* Bug 5620285, Added the following decode */
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzp.party_name, suppliers.vendor_name) vendor_name '||
' ,sites.vendor_site_code vendor_site_code '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.address1, sites.address_line1) address_line1 '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.address2, sites.address_line2) address_line2 '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.address3, sites.address_line3) address_line3 '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.address4, sites.address_line4) address_line4 '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.city, sites.city) city '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.state, sites.state) state '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.postal_code, sites.zip) zip '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.province, sites.province) province '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.country, sites.country) country '||
' ,sites.attention_ar_flag attention_ar_flag '||
' ,suppliers.withholding_status_lookup_code withholding_status_lookup_code '||
' ,ai.invoice_num invoice_num '||
' ,ai.invoice_date invoice_date '||
' ,DECODE(ai.doc_sequence_id, '||
' '''', ai.voucher_num, '||
' ai.doc_sequence_value) voucher_num '||
' ,ai.accts_pay_code_combination_id ap_ccid '||
' ,TRUNC(ps.due_date) due_date '||
' ,DECODE(sites.always_take_disc_flag, '||
' ''Y'', TRUNC(ps.due_date), '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' - NVL(ps.discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.discount_date, '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' -NVL(ps.second_discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.second_discount_date, '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' -NVL(ps.third_discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.third_discount_date, '||
' TRUNC(ps.due_date))))) discount_date '||
/* Commented and added for bug 12409940 */
/*' ,SUBSTRB(ai.description,1,50) invoice_description '|| */
' ,ai.description invoice_description '||
' ,nvl(ps.payment_priority, 99) payment_priority '||
' ,''Y'' ok_to_pay_flag '||
' ,sites.always_take_disc_flag always_take_discount_flag '||
' ,''N'' amount_modified_flag '||
' ,ai.invoice_amount invoice_amount '||
' ,ai.payment_cross_rate payment_cross_rate '||
' ,DECODE(ai.exchange_rate, '||
' NULL, DECODE(ai.invoice_currency_code, '||
' asp.base_currency_code, 1, '||
' NULL), '||
' ai.exchange_rate) invoice_exchange_rate '||
' ,ai.set_of_books_id set_of_books_id '||
' ,sites.customer_num customer_num '||
' ,ps.future_pay_due_date future_pay_due_date '||
' ,ai.exclusive_payment_flag exclusive_payment_flag '||
' ,ps.attribute1 attribute1 '||
' ,ps.attribute2 attribute2 '||
' ,ps.attribute3 attribute3 '||
' ,ps.attribute4 attribute4 '||
' ,ps.attribute5 attribute5 '||
' ,ps.attribute6 attribute6 '||
' ,ps.attribute7 attribute7 '||
' ,ps.attribute8 attribute8 '||
' ,ps.attribute9 attribute9 '||
' ,ps.attribute10 attribute10 '||
' ,ps.attribute11 attribute11 '||
' ,ps.attribute12 attribute12 '||
' ,ps.attribute13 attribure13 '||
' ,ps.attribute14 attribute14 '||
' ,ps.attribute15 attribute15 '||
' ,ps.attribute_category attribute_category '||
' ,ai.org_id org_id '||
' ,ai.payment_currency_code payment_currency_code '||
' ,ps.external_bank_account_id external_bank_account_id '||
' ,ai.legal_entity_id legal_entity_id '||
/* Bug 5192018 we will insert global attribute values from ap_invoices table */
' ,ai.global_attribute1 global_attribute1 '||
' ,ai.global_attribute2 global_attribute2 '||
' ,ai.global_attribute3 global_attribute3 '||
' ,ai.global_attribute4 global_attribute4 '||
' ,ai.global_attribute5 global_attribute5 '||
' ,ai.global_attribute6 global_attribute6 '||
' ,ai.global_attribute7 global_attribute7 '||
' ,ai.global_attribute8 global_attribute8 '||
' ,ai.global_attribute9 global_attribute9 '||
' ,ai.global_attribute10 global_attribute10 '||
' ,ai.global_attribute11 global_attribute11 '||
' ,ai.global_attribute12 global_attribute12 '||
' ,ai.global_attribute13 global_attribute13 '||
' ,ai.global_attribute14 global_attribute14 '||
' ,ai.global_attribute15 global_attribute15 '||
' ,ai.global_attribute16 global_attribute16 '||
' ,ai.global_attribute17 global_attribute17 '||
' ,ai.global_attribute18 global_attribute18 '||
' ,ai.global_attribute19 global_attribute19 '||
' ,ai.global_attribute20 global_attribute20 '||
' ,ai.global_attribute_category global_attribute_category '||-- end of bug 5192018
' ,Null amount_paid '||
' ,Null discount_amount_taken '||
' ,Null amount_remaining '||
' ,Null discount_amount_remaining '||
' ,Null payment_amount '||
' ,Null discount_amount '||
' ,Null sequence_num '||
' ,Null done_pay_reason_code '||
' ,Null check_number '||
' ,Null bank_account_type '||
' ,Null original_invoice_id '||
' ,Null original_payment_num '||
' ,Null bank_account_num '||
' ,Null bank_num '||
' ,Null proposed_payment_amount '||
' ,Null pay_selected_check_id '||
' ,Null print_selected_check_id '||
' ,Null withholding_amount '||
' ,Null invoice_payment_id '||
' ,Null dont_pay_description '||
' ,Null transfer_priority '||
' ,Null iban_number '||
' ,Null payment_grouping_number '||
' ,Null payment_exchange_rate '||
' ,Null payment_exchange_rate_type '||
' ,Null payment_exchange_date '||
-- Start of 8217641
' ,ps.remit_to_supplier_name remit_to_supplier_name '||
' ,ps.remit_to_supplier_id remit_to_supplier_id '||
' ,ps.remit_to_supplier_site remit_to_supplier_site '||
' ,ps.remit_to_supplier_site_id remit_to_supplier_site_id '||
--End 8217641
' FROM ap_supplier_sites_all sites, '||
' ap_suppliers suppliers, '||
' ap_invoices ai, '|| /* inv, '||Commented for bug#9182499 --Bug6040657. Changed from ap_invoices_all to ap_invoices */
' ap_payment_schedules_all ps, '||
' ap_system_parameters_all asp, '||
' hz_parties hzp, '||
' hz_party_sites hzps, '||-- Bug 5620285
' hz_locations hzl '||-- Bug 5620285
' WHERE ps.checkrun_id is null '||-- Bug 5705276. Regression
' AND ((due_date <= to_date(:p_pay_thru_date, ''DD-MM-YYYY'') +0/24 and '||--Bug 8708165
' due_date >= nvl(to_date(:p_pay_from_date, ''DD-MM-YYYY'') + 0/24,due_date)) '||
' OR '||
' DECODE(NVL(sites.pay_date_basis_lookup_code,''DISCOUNT''), '||
' ''DISCOUNT'', '||
' DECODE(sites.always_take_disc_flag, '||
' ''Y'', ps.discount_date, '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' -NVL(ps.discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.discount_date, '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' -NVL(ps.second_discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.second_discount_date, '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' -NVL(ps.third_discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.third_discount_date, '||
' TRUNC(ps.due_date))))), '||
' TRUNC(due_date)) '||
' BETWEEN DECODE(sites.always_take_disc_flag,''Y'', '||
' nvl(to_date(:p_pay_from_date, ''DD-MM-YYYY''), TO_DATE(''1901'',''YYYY'')), '||
' to_date(:p_check_date, ''DD-MM-YYYY'')) '||
' AND to_date(:p_disc_pay_thru_date, ''DD-MM-YYYY'')) '||
/*' AND ps.payment_status_flag BETWEEN ''N'' AND ''P'' '||
' AND ai.payment_status_flag BETWEEN ''N'' AND ''P'' '|| Commented for bug#11848050 */
' AND ps.payment_status_flag IN (''N'', ''P'') '|| /* Added for bug#11848050 */
' AND ai.payment_status_flag IN (''N'', ''P'') '|| /* Added for bug#11848050 */
' AND nvl(ai.force_revalidation_flag, ''N'') = ''N'' '||--bug7244642
' AND NVL(ps.payment_priority, 99) BETWEEN :p_hi_payment_priority '||
' AND :p_lo_payment_priority '||
' AND ai.cancelled_date is null '||
-- Bug 7167192 Added decode and outer join
-- hzp and hzps data is required only for Payment Requests.
' AND hzp.party_id(+) = decode(ai.invoice_type_lookup_code, '||
' ''PAYMENT REQUEST'', ai.party_id '||
' , -99) '||
' AND NVL(ps.hold_flag, ''N'') = ''N'' '||
' AND NVL(sites.hold_all_payments_flag, ''N'') = ''N'' '||
' AND ai.invoice_id = ps.invoice_id '||
' AND sites.vendor_id(+) = ai.vendor_id '||
' AND sites.vendor_site_id(+) = ai.vendor_site_id '||
' AND suppliers.vendor_id(+) = ai.vendor_id '||
' AND asp.org_id = ai.org_id '||
' AND hzp.party_id = hzps.party_id (+) '||-- Bug 5620285
--Bug 5929034: An employee does not have a hz_party_site, modifying query to reflect the same
-- AND nvl(hzps.party_site_id,-99) = decode(suppliers.vendor_type_lookup_code,''EMPLOYEE'',-99,nvl(inv.party_site_id, hzps.party_site_id)) -- Bug 5620285
-- Bug 6662382
-- Bug 7167192 - Query condition is now based on whether the Invoice
-- is a Payment Request. Supplier type does not matter.
--AND NVL(hzps.party_site_id,-99) = DECODE(suppliers.vendor_type_lookup_code,''EMPLOYEE'', COALESCE(inv.party_site_id, hzps.party_site_id,-99),
-- NVL(inv.party_site_id, hzps.party_site_id))
' AND NVL(hzps.party_site_id,-99) = NVL(decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', ai.party_site_id, -99), NVL(hzps.party_site_id,-99)) '||
-- Bug 7167192
' AND nvl(hzps.location_id,-99) = hzl.location_id (+) '||-- Bug 5620285
--End of 5929034
' AND fv_econ_benf_disc.ebd_check(:p_checkrun_name, ai.invoice_id, '||
' to_date(:p_check_date, ''DD-MM-YYYY''), due_date, ps.discount_amount_available, ps.discount_date) = ''Y'' ';
' AND (ai.legal_entity_id in (select /*+ push_subq * / legal_entity_id '||
' from ap_le_group '||
' where checkrun_id = :p_checkrun_id) '||
' or :p_le_group_option = ''ALL'') '||
' AND (ai.org_id in (select /*+ push_subq * / org_id '||
' from AP_OU_GROUP '||
' where checkrun_id = :p_checkrun_id) '||
' or :p_ou_group_option = ''ALL'') '||
' AND (ai.payment_currency_code in (select /*+ push_subq * / currency_code '||
' from AP_CURRENCY_GROUP '||
' where checkrun_id = :p_checkrun_id) '||
' or :p_curr_group_option = ''ALL'') ' Commented for bug#11848050 */;
' AND inv.pay_group_lookup_code in (select / *+ leading(apg) cardinality(apg 1) * / vendor_pay_group '||
' from AP_PAY_GROUP apg'|| --bug9087739, added alias for AP_PAY_GROUP
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
' ( select '|| /* Added for bug#12773508 */
/* ' ( select / *+ leading(apg) cardinality(apg 1) * / '|| Commented for bug#12773508 */
' apg.vendor_pay_group, mo.ORGANIZATION_ID '||
' from AP_PAY_GROUP apg, MO_GLOB_ORG_ACCESS_TMP mo '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id '||
' AND ai.org_id = mo.organization_id ) '; /* Added for bug#11848050 */
' (select /*+ push_subq */ legal_entity_id '||
' from ap_le_group '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
' (select /*+ no_push_subq no_unnest*/ org_id '||
' from AP_OU_GROUP '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
' (select /*+ no_unnest */ currency_code '||
' from AP_CURRENCY_GROUP '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
/* AND NOT EXISTS (SELECT ''Invoice is not fully approved''
FROM ap_invoice_distributions_all D2
WHERE D2.invoice_id = inv.invoice_id
AND NVL(D2.match_status_flag, ''N'') in (''N'', ''S''))*/
--bug6365720
-- Bug 7265013 starts
/* Bug 13901772
' AND EXISTS ( '||
' SELECT 1 '||
' FROM sys.dual '||
' WHERE AP_INVOICES_PKG.get_wfapproval_status(ai.invoice_id, ai.org_id) in '||
' (''NOT REQUIRED'',''WFAPPROVED'',''MANUALLY APPROVED'') '||
' ) '||
*/
' AND ai.wfapproval_status in (''NOT REQUIRED'',''WFAPPROVED'',''MANUALLY APPROVED'') '||
' AND NOT EXISTS (select 1 from ap_invoice_lines_all ail '||
' where ai.invoice_id = ail.invoice_id '||
' and ail.wfapproval_status in (''NEEDS WFREAPPROVAL'',''REJECTED'',''INITIATED'')) '||
-- Bug 7265013 ends
-- Bug 11816573. Removed the push predicates.
' AND NOT EXISTS (SELECT /*+ no_unnest */ ''Unreleased holds exist'' '|| /* Added hint for bug#12773508 */
' FROM ap_holds H '||
' WHERE H.invoice_id = ai.invoice_id '||
' AND H.release_lookup_code is null) '||
/* Added for bug 13901772 */
' AND NOT EXISTS (SELECT ''Invoice is not fully approved'' '||
' FROM ap_invoice_distributions_all D2 '||
' WHERE D2.invoice_id = ai.invoice_id '||
' AND NVL(D2.match_status_flag, ''N'') in (''N'', ''S'', ''T'')) '||
/* Bug 12799362. Start. */
' AND NOT EXISTS (SELECT ''Line without distribution'' '||
' FROM ap_invoice_lines_all ail '||
' WHERE ail.invoice_id = ai.invoice_id '||
' AND ail.amount <> 0 ' ||
' AND NOT EXISTS (SELECT ''No Distributions'' '||
' FROM ap_invoice_distributions_all aid '||
' WHERE ail.invoice_id = aid.invoice_id '||
' AND ail.line_number = aid.invoice_line_number '||
' ) '||
' ) '||
/* Commented for bug 13901772 */
/*' AND NOT EXISTS (SELECT /*+ no_unnest */ /*''Invoice is not fully approved'' '|| /* Added hint for bug#12773508 */
/*' FROM ap_invoices_derived_v AIDV '||
' WHERE AIDV.invoice_id = ai.invoice_id '||
' AND AIDV.approval_status_lookup_code IN '||
' (''NEVER APPROVED'', ''NEEDS REAPPROVAL'', ''UNAPPROVED'')) '||
*/
-- Bug 11816573. Changed hint from push_subq to no_unnest.
' AND EXISTS (SELECT /*+ no_unnest */ ''Distributions exist'' '||
' FROM ap_invoice_distributions D4 '||
/* Added FOR UPDATE for bug 13321621 */
/* Added SKIP LOCKED for bug 14277979 */
' WHERE D4.invoice_id = ai.invoice_id) FOR UPDATE OF ps.checkrun_id SKIP LOCKED';
/* AND NOT EXISTS (SELECT ''CCR EXPIRED''
FROM FV_TPP_ASSIGNMENTS_V TPP
WHERE TPP.beneficiary_party_id = inv.party_id
AND TPP.beneficiary_party_site_id = inv.party_site_id
AND NVL(TPP.fv_tpp_pay_flag, ''Y'') = ''N'') ; bug8691645 */
,sel_inv_list.last_update_date_l
,sel_inv_list.last_updated_by_l
,sel_inv_list.creation_date_l
,sel_inv_list.created_by_l
,sel_inv_list.last_update_login_l
,sel_inv_list.vendor_id_l
,sel_inv_list.vendor_site_id_l
,sel_inv_list.vendor_num_l
,sel_inv_list.vendor_name_l
,sel_inv_list.vendor_site_code_l
,sel_inv_list.address_line1_l
,sel_inv_list.address_line2_l
,sel_inv_list.address_line3_l
,sel_inv_list.address_line4_l
,sel_inv_list.city_l
,sel_inv_list.state_l
,sel_inv_list.zip_l
,sel_inv_list.province_l
,sel_inv_list.country_l
,sel_inv_list.attention_ar_flag_l
,sel_inv_list.withholding_status_lookup_l
,sel_inv_list.invoice_num_l
,sel_inv_list.invoice_date_l
,sel_inv_list.voucher_num_l
,sel_inv_list.ap_ccid_l
,sel_inv_list.due_date_l
,sel_inv_list.discount_date_l
,sel_inv_list.invoice_description_l
,sel_inv_list.payment_priority_l
,sel_inv_list.ok_to_pay_flag_l
,sel_inv_list.always_take_disc_flag_l
,sel_inv_list.amount_modified_flag_l
,sel_inv_list.invoice_amount_l
,sel_inv_list.payment_cross_rate_l
,sel_inv_list.invoice_exchange_rate_l
,sel_inv_list.set_of_books_id_l
,sel_inv_list.customer_num_l
,sel_inv_list.future_pay_due_date_l
,sel_inv_list.exclusive_payment_flag_l
,sel_inv_list.attribute1_l
,sel_inv_list.attribute2_l
,sel_inv_list.attribute3_l
,sel_inv_list.attribute4_l
,sel_inv_list.attribute5_l
,sel_inv_list.attribute6_l
,sel_inv_list.attribute7_l
,sel_inv_list.attribute8_l
,sel_inv_list.attribute9_l
,sel_inv_list.attribute10_l
,sel_inv_list.attribute11_l
,sel_inv_list.attribute12_l
,sel_inv_list.attribute13_l
,sel_inv_list.attribute14_l
,sel_inv_list.attribute15_l
,sel_inv_list.attribute_category_l
,sel_inv_list.org_id_l
,sel_inv_list.payment_currency_code_l
,sel_inv_list.external_bank_account_id_l
,sel_inv_list.legal_entity_id_l
,sel_inv_list.global_attribute1_l
,sel_inv_list.global_attribute2_l
,sel_inv_list.global_attribute3_l
,sel_inv_list.global_attribute4_l
,sel_inv_list.global_attribute5_l
,sel_inv_list.global_attribute6_l
,sel_inv_list.global_attribute7_l
,sel_inv_list.global_attribute8_l
,sel_inv_list.global_attribute9_l
,sel_inv_list.global_attribute10_l
,sel_inv_list.global_attribute11_l
,sel_inv_list.global_attribute12_l
,sel_inv_list.global_attribute13_l
,sel_inv_list.global_attribute14_l
,sel_inv_list.global_attribute15_l
,sel_inv_list.global_attribute16_l
,sel_inv_list.global_attribute17_l
,sel_inv_list.global_attribute18_l
,sel_inv_list.global_attribute19_l
,sel_inv_list.global_attribute20_l
,sel_inv_list.global_attribute_category_l
,sel_inv_list.amount_paid_l
,sel_inv_list.discount_amount_taken_l
,sel_inv_list.amount_remaining_l
,sel_inv_list.discount_amount_remaining_l
,sel_inv_list.payment_amount_l
,sel_inv_list.discount_amount_l
,sel_inv_list.sequence_num_l
,sel_inv_list.dont_pay_reason_code_l
,sel_inv_list.check_number_l
,sel_inv_list.bank_account_type_l
,sel_inv_list.original_invoice_id_l
,sel_inv_list.original_payment_num_l
,sel_inv_list.bank_account_num_l
,sel_inv_list.bank_num_l
,sel_inv_list.proposed_payment_amount_l
,sel_inv_list.pay_selected_check_id_l
,sel_inv_list.print_selected_check_id_l
,sel_inv_list.withhloding_amount_l
,sel_inv_list.invoice_payment_id_l
,sel_inv_list.dont_pay_description_l
,sel_inv_list.transfer_priority_l
,sel_inv_list.iban_number_l
,sel_inv_list.payment_grouping_number_l
,sel_inv_list.payment_exchange_rate_l
,sel_inv_list.payment_exchange_rate_type_l
,sel_inv_list.payment_exchange_date_l
--Start of 8217641
,sel_inv_list.remit_to_supplier_name_l
,sel_inv_list.remit_to_supplier_id_l
,sel_inv_list.remit_to_supplier_site_l
,sel_inv_list.remit_to_supplier_site_id_l
--End 8217641
LIMIT 1000;
l_debug_info := 'Update ap_payment_schedules_all: encumbrances are on';
UPDATE Ap_Payment_Schedules_All
SET checkrun_id = sel_inv_list.checkrun_id_l(i)
WHERE invoice_id = sel_inv_list.invoice_id_l(i)
AND payment_num = sel_inv_list.payment_num_l(i)
AND checkrun_id IS NULL --bug 6788730
;
l_debug_info := 'Insert into ap_selected_invoices_all: encumbrances are on';
INSERT INTO ap_selected_invoices_all
(checkrun_name
,checkrun_id
,invoice_id
,payment_num
,last_update_date
,last_updated_by
,creation_date
,created_by
,vendor_id
,vendor_site_id
,vendor_num
,vendor_name
,vendor_site_code
,address_line1
,address_line2
,address_line3
,address_line4
,city
,state
,zip
,province
,country
,attention_ar_flag
,withholding_status_lookup_code
,invoice_num
,invoice_date
,voucher_num
,ap_ccid
,due_date
,discount_date
,invoice_description
,payment_priority
,ok_to_pay_flag
,always_take_discount_flag
,amount_modified_flag
,invoice_amount
,payment_cross_rate
,invoice_exchange_rate
,set_of_books_id
,customer_num
,future_pay_due_date
,exclusive_payment_flag
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category
,org_id
,payment_currency_code
,external_bank_account_id
,legal_entity_id
,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
,global_attribute_category
--Start of 8217641
,remit_to_supplier_name
,remit_to_supplier_id
,remit_to_supplier_site
,remit_to_supplier_site_id
--End 8217641
,affects_rejection_level
--bug12349626
)
--bug 6788730 Changed this to SELECT
VALUES /* Bug 13321621 Changed back to insert by values */
(
/* Commented for bug 13321621 */
--SELECT /*+ INDEX(AP_PAYMENT_SCHEDULES_ALL,AP_PAYMENT_SCHEDULES_U1) */
sel_inv_list.checkrun_name_l(i)
,sel_inv_list.checkrun_id_l(i)
,sel_inv_list.invoice_id_l(i)
,sel_inv_list.payment_num_l(i)
,sel_inv_list.last_update_date_l(i)
,sel_inv_list.last_updated_by_l(i)
,sel_inv_list.creation_date_l(i)
,sel_inv_list.created_by_l(i)
,sel_inv_list.vendor_id_l(i)
,sel_inv_list.vendor_site_id_l(i)
,sel_inv_list.vendor_num_l(i)
,sel_inv_list.vendor_name_l(i)
,sel_inv_list.vendor_site_code_l(i)
,sel_inv_list.address_line1_l(i)
,sel_inv_list.address_line2_l(i)
,sel_inv_list.address_line3_l(i)
,sel_inv_list.address_line4_l(i)
,sel_inv_list.city_l(i)
,sel_inv_list.state_l(i)
,sel_inv_list.zip_l(i)
,sel_inv_list.province_l(i)
,sel_inv_list.country_l(i)
,sel_inv_list.attention_ar_flag_l(i)
,sel_inv_list.withholding_status_lookup_l(i)
,sel_inv_list.invoice_num_l(i)
,sel_inv_list.invoice_date_l(i)
,sel_inv_list.voucher_num_l(i)
,sel_inv_list.ap_ccid_l(i)
,sel_inv_list.due_date_l(i)
,sel_inv_list.discount_date_l(i)
,sel_inv_list.invoice_description_l(i)
,sel_inv_list.payment_priority_l(i)
,sel_inv_list.ok_to_pay_flag_l(i)
,sel_inv_list.always_take_disc_flag_l(i)
,sel_inv_list.amount_modified_flag_l(i)
,sel_inv_list.invoice_amount_l(i)
,sel_inv_list.payment_cross_rate_l(i)
,sel_inv_list.invoice_exchange_rate_l(i)
,sel_inv_list.set_of_books_id_l(i)
,sel_inv_list.customer_num_l(i)
,sel_inv_list.future_pay_due_date_l(i)
,sel_inv_list.exclusive_payment_flag_l(i)
,sel_inv_list.attribute1_l(i)
,sel_inv_list.attribute2_l(i)
,sel_inv_list.attribute3_l(i)
,sel_inv_list.attribute4_l(i)
,sel_inv_list.attribute5_l(i)
,sel_inv_list.attribute6_l(i)
,sel_inv_list.attribute7_l(i)
,sel_inv_list.attribute8_l(i)
,sel_inv_list.attribute9_l(i)
,sel_inv_list.attribute10_l(i)
,sel_inv_list.attribute11_l(i)
,sel_inv_list.attribute12_l(i)
,sel_inv_list.attribute13_l(i)
,sel_inv_list.attribute14_l(i)
,sel_inv_list.attribute15_l(i)
,sel_inv_list.attribute_category_l(i)
,sel_inv_list.org_id_l(i)
,sel_inv_list.payment_currency_code_l(i)
,sel_inv_list.external_bank_account_id_l(i)
,sel_inv_list.legal_entity_id_l(i)
,sel_inv_list.global_attribute1_l(i)
,sel_inv_list.global_attribute2_l(i)
,sel_inv_list.global_attribute3_l(i)
,sel_inv_list.global_attribute4_l(i)
,sel_inv_list.global_attribute5_l(i)
,sel_inv_list.global_attribute6_l(i)
,sel_inv_list.global_attribute7_l(i)
,sel_inv_list.global_attribute8_l(i)
,sel_inv_list.global_attribute9_l(i)
,sel_inv_list.global_attribute10_l(i)
,sel_inv_list.global_attribute11_l(i)
,sel_inv_list.global_attribute12_l(i)
,sel_inv_list.global_attribute13_l(i)
,sel_inv_list.global_attribute14_l(i)
,sel_inv_list.global_attribute15_l(i)
,sel_inv_list.global_attribute16_l(i)
,sel_inv_list.global_attribute17_l(i)
,sel_inv_list.global_attribute18_l(i)
,sel_inv_list.global_attribute19_l(i)
,sel_inv_list.global_attribute20_l(i)
,sel_inv_list.global_attribute_category_l(i)
--Start of 8217641
,sel_inv_list.remit_to_supplier_name_l(i)
,sel_inv_list.remit_to_supplier_id_l(i)
,sel_inv_list.remit_to_supplier_site_l(i)
,sel_inv_list.remit_to_supplier_site_id_l(i)
--End 8217641
,'N'
--bug12349626
/* Commented for bug 13321621
FROM Ap_Payment_Schedules_All
WHERE invoice_id = sel_inv_list.invoice_id_l(i)
AND payment_num = sel_inv_list.payment_num_l(i)
AND checkrun_id = sel_inv_list.checkrun_id_l(i)
*/
--bug 6788730
);
' SELECT '||l_hint||
' :p_checkrun_name checkrun_name '||
' ,:p_checkrun_id checkrun_id '||
' ,ps.invoice_id invoice_id '||
' ,ps.payment_num payment_num '||
' ,SYSDATE last_update_date '||
-- Bug 7296715
-- The User Id is hardcoded to 5 (APPSMGR). It is changed to populate correct value.
-- ,5 last_updated_by
' ,FND_GLOBAL.USER_ID last_updated_by '||
' ,SYSDATE creation_date '||
-- Bug 7296715
-- ,5 created_by
' ,FND_GLOBAL.USER_ID created_by '||
' ,NULL last_update_login '||
' ,ai.vendor_id vendor_id '||
' ,ai.vendor_site_id vendor_site_id '||
' ,suppliers.segment1 vendor_num '||
-- Bug 5620285, Added the following decode */
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzp.party_name, suppliers.vendor_name) vendor_name '||
' ,sites.vendor_site_code vendor_site_code '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.address1, sites.address_line1) address_line1 '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.address2, sites.address_line2) address_line2 '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.address3, sites.address_line3) address_line3 '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.address4, sites.address_line4) address_line4 '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.city, sites.city) city '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.state, sites.state) state '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.postal_code, sites.zip) zip '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.province, sites.province) province '||
' ,decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', '||
' hzl.country, sites.country) country '||
' ,sites.attention_ar_flag attention_ar_flag '||
' ,suppliers.withholding_status_lookup_code withholding_status_lookup_code '||
' ,ai.invoice_num invoice_num '||
' ,ai.invoice_date invoice_date '||
' ,DECODE(ai.doc_sequence_id, '||
' '''', ai.voucher_num, '||
' ai.doc_sequence_value) voucher_num '||
' ,ai.accts_pay_code_combination_id ap_ccid '||
' ,TRUNC(ps.due_date) due_date '||
' ,DECODE(sites.always_take_disc_flag, '||
' ''Y'', TRUNC(ps.due_date), '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' - NVL(ps.discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.discount_date, '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' -NVL(ps.second_discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.second_discount_date, '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' -NVL(ps.third_discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.third_discount_date, '||
' TRUNC(ps.due_date))))) discount_date '||
/* Commented and added for bug 12409940 */
/*' ,SUBSTRB(ai.description,1,50) invoice_description '|| */
' ,ai.description invoice_description '||
' ,nvl(ps.payment_priority, 99) payment_priority '||
' ,''Y'' ok_to_pay_flag '||
' ,sites.always_take_disc_flag always_take_discount_flag '||
' ,''N'' amount_modified_flag '||
' ,ai.invoice_amount invoice_amount '||
' ,ai.payment_cross_rate payment_cross_rate '||
' ,DECODE(ai.exchange_rate, '||
' NULL, DECODE(ai.invoice_currency_code, '||
' asp.base_currency_code, 1, '||
' NULL), '||
' ai.exchange_rate) invoice_exchange_rate '||
' ,ai.set_of_books_id set_of_books_id '||
' ,sites.customer_num customer_num '||
' ,ps.future_pay_due_date future_pay_due_date '||
' ,ai.exclusive_payment_flag exclusive_payment_flag '||
' ,ps.attribute1 attribute1 '||
' ,ps.attribute2 attribute2 '||
' ,ps.attribute3 attribute3 '||
' ,ps.attribute4 attribute4 '||
' ,ps.attribute5 attribute5 '||
' ,ps.attribute6 attribute6 '||
' ,ps.attribute7 attribute7 '||
' ,ps.attribute8 attribute8 '||
' ,ps.attribute9 attribute9 '||
' ,ps.attribute10 attribute10 '||
' ,ps.attribute11 attribute11 '||
' ,ps.attribute12 attribute12 '||
' ,ps.attribute13 attribure13 '||
' ,ps.attribute14 attribute14 '||
' ,ps.attribute15 attribute15 '||
' ,ps.attribute_category attribute_category '||
' ,ai.org_id org_id '||
' ,ai.payment_currency_code payment_currency_code '||
' ,ps.external_bank_account_id external_bank_account_id '||
' ,ai.legal_entity_id legal_entity_id '||
-- Bug 5192018 we will insert global attribute values from ap_invoices table */
' ,ai.global_attribute1 global_attribute1 '||
' ,ai.global_attribute2 global_attribute2 '||
' ,ai.global_attribute3 global_attribute3 '||
' ,ai.global_attribute4 global_attribute4 '||
' ,ai.global_attribute5 global_attribute5 '||
' ,ai.global_attribute6 global_attribute6 '||
' ,ai.global_attribute7 global_attribute7 '||
' ,ai.global_attribute8 global_attribute8 '||
' ,ai.global_attribute9 global_attribute9 '||
' ,ai.global_attribute10 global_attribute10 '||
' ,ai.global_attribute11 global_attribute11 '||
' ,ai.global_attribute12 global_attribute12 '||
' ,ai.global_attribute13 global_attribute13 '||
' ,ai.global_attribute14 global_attribute14 '||
' ,ai.global_attribute15 global_attribute15 '||
' ,ai.global_attribute16 global_attribute16 '||
' ,ai.global_attribute17 global_attribute17 '||
' ,ai.global_attribute18 global_attribute18 '||
' ,ai.global_attribute19 global_attribute19 '||
' ,ai.global_attribute20 global_attribute20 '||
' ,ai.global_attribute_category global_attribute_category '|| -- end of bug 5192018
' ,Null amount_paid '||
' ,Null discount_amount_taken '||
' ,Null amount_remaining '||
' ,Null discount_amount_remaining '||
' ,Null payment_amount '||
' ,Null discount_amount '||
' ,Null sequence_num '||
' ,Null done_pay_reason_code '||
' ,Null check_number '||
' ,Null bank_account_type '||
' ,Null original_invoice_id '||
' ,Null original_payment_num '||
' ,Null bank_account_num '||
' ,Null bank_num '||
' ,Null proposed_payment_amount '||
' ,Null pay_selected_check_id '||
' ,Null print_selected_check_id '||
' ,Null withholding_amount '||
' ,Null invoice_payment_id '||
' ,Null dont_pay_description '||
' ,Null transfer_priority '||
' ,Null iban_number '||
' ,Null payment_grouping_number '||
' ,Null payment_exchange_rate '||
' ,Null payment_exchange_rate_type '||
' ,Null payment_exchange_date '||
--Start of 8217641
' ,ps.remit_to_supplier_name remit_to_supplier_name '||
' ,ps.remit_to_supplier_id remit_to_supplier_id '||
' ,ps.remit_to_supplier_site remit_to_supplier_site '||
' ,ps.remit_to_supplier_site_id remit_to_supplier_site_id '||
--End 8217641
' FROM ap_supplier_sites_all sites, '||
' ap_suppliers suppliers, '||
' ap_invoices ai, '|| /* inv, '|| Commented for bug#9182499 GSCC Error File.Sql.6 --Bug6040657. Changed from ap_invoices_all to ap_invoices */
' ap_payment_schedules_all ps, '||
' ap_system_parameters_all asp, '||
' hz_parties hzp, '||
' hz_party_sites hzps, '|| -- Bug 5620285
' hz_locations hzl '|| -- Bug 5620285
' WHERE ps.checkrun_id is null '|| -- Bug 5705276. Regression
' AND ((due_date <= to_date(:p_pay_thru_date, ''DD-MM-YYYY'') +0/24 and '|| --Bug 8708165
' due_date >= nvl(to_date(:p_pay_from_date, ''DD-MM-YYYY'') + 0/24,due_date)) '||
' OR '||
' DECODE(NVL(sites.pay_date_basis_lookup_code,''DISCOUNT''), '||
' ''DISCOUNT'', '||
' DECODE(sites.always_take_disc_flag, '||
' ''Y'', ps.discount_date, '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' -NVL(ps.discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.discount_date, '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' -NVL(ps.second_discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.second_discount_date, '||
' DECODE(SIGN(to_date(:p_check_date, ''DD-MM-YYYY'') '||
' -NVL(ps.third_discount_date, '||
' to_date(:p_check_date, ''DD-MM-YYYY'')+1)-1), '||
' -1, ps.third_discount_date, '||
' TRUNC(ps.due_date))))), '||
' TRUNC(due_date)) '||
' BETWEEN DECODE(sites.always_take_disc_flag,''Y'', '||
' nvl(to_date(:p_pay_from_date, ''DD-MM-YYYY''), TO_DATE(''1901'',''YYYY'')), '||
' to_date(:p_check_date, ''DD-MM-YYYY'') ) '||
' AND to_date(:p_disc_pay_thru_date, ''DD-MM-YYYY'') ) '||
/* ' AND ps.payment_status_flag BETWEEN ''N'' AND ''P'' '|| Commented for bug#11848050 */
' AND nvl(ai.force_revalidation_flag, ''N'') = ''N'' '|| --bug7244642
/* ' AND ai.payment_status_flag BETWEEN ''N'' AND ''P'' '|| Commented for bug#11848050 */
' AND ps.payment_status_flag IN (''N'', ''P'') '|| /* Added for bug#11848050 */
' AND ai.payment_status_flag IN (''N'', ''P'') '|| /* Added for bug#11848050 */
' AND NVL(ps.payment_priority, 99) BETWEEN :p_hi_payment_priority '||
' AND :p_lo_payment_priority '||
' AND ai.cancelled_date is null '||
-- Bug 7167192 Added decode
-- hzp and hzps data is required only for Payment Requests.
' AND hzp.party_id(+) = decode(ai.invoice_type_lookup_code, '||
' ''PAYMENT REQUEST'', ai.party_id '||
' , -99) '||
' AND NVL(ps.hold_flag, ''N'') = ''N'' '||
' AND NVL(sites.hold_all_payments_flag, ''N'') = ''N'' '||
' AND ai.invoice_id = ps.invoice_id '||
' AND sites.vendor_id(+) = ai.vendor_id '||
' AND sites.vendor_site_id(+) = ai.vendor_site_id '||
' AND suppliers.vendor_id(+) = ai.vendor_id '||
' AND asp.org_id = ai.org_id '||
' AND hzp.party_id = hzps.party_id (+) '|| -- Bug 5620285
-- Bug 5929034: An employee does not have a hz_party_site changing query to reflect the same
-- AND nvl(hzps.party_site_id,-99) = decode(suppliers.vendor_type_lookup_code,'EMPLOYEE',-99,nvl(inv.party_site_id, hzps.party_site_id)) -- Bug 5620285
-- Bug 6662382
-- Bug 7167192 - Query condition is now based on whether the Invoice
-- is a Payment Request. Supplier type does not matter.
-- AND NVL(hzps.party_site_id,-99) = DECODE(suppliers.vendor_type_lookup_code,'EMPLOYEE', COALESCE(inv.party_site_id, hzps.party_site_id,-99),
-- NVL(inv.party_site_id, hzps.party_site_id))
' AND NVL(hzps.party_site_id,-99) = NVL(decode(ai.invoice_type_lookup_code, ''PAYMENT REQUEST'', ai.party_site_id, -99), hzps.party_site_id) '||
-- Bug 7167192
' AND nvl(hzps.location_id,-99) = hzl.location_id(+) '|| -- Bug 5620285
--End Bug 5929034
' AND fv_econ_benf_disc.ebd_check(:p_checkrun_name, ai.invoice_id, '||
' to_date(:p_check_date, ''DD-MM-YYYY''), due_date, ps.discount_amount_available, ps.discount_date) = ''Y'' ';
' AND (ai.legal_entity_id in (select /*+ push_subq * / legal_entity_id '||
' from ap_le_group '||
' where checkrun_id = :p_checkrun_id) '||
' or :p_le_group_option = ''ALL'') '||
' AND (ai.org_id in (select /*+ push_subq * / org_id '||
' from AP_OU_GROUP '||
' where checkrun_id = :p_checkrun_id) '||
' or :p_ou_group_option = ''ALL'') '||
' AND (ai.payment_currency_code in (select /*+ push_subq * / currency_code '||
' from AP_CURRENCY_GROUP '||
' where checkrun_id = :p_checkrun_id) '||
' or :p_curr_group_option = ''ALL'') ' Commented for bug#11848050 */;
' AND inv.pay_group_lookup_code in (select / *+ leading(apg) cardinality(apg 1) * / vendor_pay_group '||
' from AP_PAY_GROUP apg'|| --bug9087739, added alias for AP_PAY_GROUP
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
' ( select '|| /* Added for bug#12773508 */
/* ' ( select / *+ leading(apg) cardinality(apg 1) * / '|| Commented for bug#12773508 */
' apg.vendor_pay_group, mo.ORGANIZATION_ID '||
' from AP_PAY_GROUP apg, MO_GLOB_ORG_ACCESS_TMP mo '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id '||
' AND ai.org_id = mo.organization_id ) '; /* Added for bug#11848050 */
' (select /*+ push_subq */ legal_entity_id '||
' from ap_le_group '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
' (select /*+ no_push_subq no_unnest*/ org_id '||
' from AP_OU_GROUP '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
' (select /*+ no_unnest */ currency_code '||
' from AP_CURRENCY_GROUP '||
' where checkrun_id BETWEEN :p_checkrun_id AND :p_checkrun_id) ';
' SELECT 1 '||
' FROM sys.dual '||
' WHERE AP_INVOICES_PKG.get_wfapproval_status(ai.invoice_id, ai.org_id) in '||
' (''NOT REQUIRED'',''WFAPPROVED'',''MANUALLY APPROVED'') '||
' ) '|| */
/* Bug 13901772 */
' AND ai.wfapproval_status in (''NOT REQUIRED'',''WFAPPROVED'',''MANUALLY APPROVED'') '||
' AND NOT EXISTS (select 1 from ap_invoice_lines_all ail '||
' where ai.invoice_id = ail.invoice_id '||
' and ail.wfapproval_status in (''NEEDS WFREAPPROVAL'',''REJECTED'',''INITIATED'')) '||
-- Bug 7265013 ends
-- Bug 11816573. Removed the push predicates.
' AND NOT EXISTS (SELECT /*+ no_unnest */ ''Unreleased holds exist'' '|| /* Added hint for bug#12773508 */
' FROM ap_holds_all H '||
' WHERE H.invoice_id = ai.invoice_id '||
' AND H.release_lookup_code is null) '||
' AND NOT EXISTS (SELECT ''Invoice is not fully approved'' '||
' FROM ap_invoice_distributions_all D2 '||
' WHERE D2.invoice_id = ai.invoice_id '||
' AND NVL(D2.match_status_flag, ''N'') in (''N'', ''S'')) '||
/* Bug 12799362. Start. */
' AND NOT EXISTS (SELECT ''Line without distribution'' '||
' FROM ap_invoice_lines_all ail '||
' WHERE ail.invoice_id = ai.invoice_id '||
' AND ail.amount <> 0 ' ||
' AND NOT EXISTS (SELECT ''No Distributions'' '||
' FROM ap_invoice_distributions_all aid '||
' WHERE ail.invoice_id = aid.invoice_id '||
' AND ail.line_number = aid.invoice_line_number '||
' ) '||
' ) '||
/* Bug 12799362. End. */
/*
-- Commented for Bug 12727539.
-- ' AND NOT EXISTS (SELECT ''Invoice is not fully approved'' '||
-- ' FROM ap_invoices_derived_v AIDV '||
-- ' WHERE AIDV.invoice_id = ai.invoice_id '||
-- ' AND AIDV.approval_status_lookup_code IN '||
-- ' (''NEVER APPROVED'', ''NEEDS REAPPROVAL'', ''UNAPPROVED'')) '||
*/
-- Bug 11816573. Changed hint from push_subq to no_unnest.
' AND EXISTS (SELECT /*+ no_unnest */ ''Distributions exist'' '||
' FROM ap_invoice_distributions_all D4 '||
/* Added FOR UPDATE for bug 13321621 */
/* Added SKIP LOCKED for bug 14277979 */
' WHERE D4.invoice_id = ai.invoice_id) FOR UPDATE OF ps.checkrun_id SKIP LOCKED';
/* AND NOT EXISTS (SELECT ''CCR EXPIRED''
FROM FV_TPP_ASSIGNMENTS_V TPP
WHERE TPP.beneficiary_party_id = inv.party_id
AND TPP.beneficiary_party_site_id = inv.party_site_id
AND NVL(TPP.fv_tpp_pay_flag, ''Y'') = ''N'') bug8691645 */
-- 6456537 Checking the validity of CCR of the Third Party for
-- supplier. If the CCR is Invalid then the invoice document
-- is not consider for the Payment(Auto Select)
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
,sel_inv_list.last_update_date_l
,sel_inv_list.last_updated_by_l
,sel_inv_list.creation_date_l
,sel_inv_list.created_by_l
,sel_inv_list.last_update_login_l
,sel_inv_list.vendor_id_l
,sel_inv_list.vendor_site_id_l
,sel_inv_list.vendor_num_l
,sel_inv_list.vendor_name_l
,sel_inv_list.vendor_site_code_l
,sel_inv_list.address_line1_l
,sel_inv_list.address_line2_l
,sel_inv_list.address_line3_l
,sel_inv_list.address_line4_l
,sel_inv_list.city_l
,sel_inv_list.state_l
,sel_inv_list.zip_l
,sel_inv_list.province_l
,sel_inv_list.country_l
,sel_inv_list.attention_ar_flag_l
,sel_inv_list.withholding_status_lookup_l
,sel_inv_list.invoice_num_l
,sel_inv_list.invoice_date_l
,sel_inv_list.voucher_num_l
,sel_inv_list.ap_ccid_l
,sel_inv_list.due_date_l
,sel_inv_list.discount_date_l
,sel_inv_list.invoice_description_l
,sel_inv_list.payment_priority_l
,sel_inv_list.ok_to_pay_flag_l
,sel_inv_list.always_take_disc_flag_l
,sel_inv_list.amount_modified_flag_l
,sel_inv_list.invoice_amount_l
,sel_inv_list.payment_cross_rate_l
,sel_inv_list.invoice_exchange_rate_l
,sel_inv_list.set_of_books_id_l
,sel_inv_list.customer_num_l
,sel_inv_list.future_pay_due_date_l
,sel_inv_list.exclusive_payment_flag_l
,sel_inv_list.attribute1_l
,sel_inv_list.attribute2_l
,sel_inv_list.attribute3_l
,sel_inv_list.attribute4_l
,sel_inv_list.attribute5_l
,sel_inv_list.attribute6_l
,sel_inv_list.attribute7_l
,sel_inv_list.attribute8_l
,sel_inv_list.attribute9_l
,sel_inv_list.attribute10_l
,sel_inv_list.attribute11_l
,sel_inv_list.attribute12_l
,sel_inv_list.attribute13_l
,sel_inv_list.attribute14_l
,sel_inv_list.attribute15_l
,sel_inv_list.attribute_category_l
,sel_inv_list.org_id_l
,sel_inv_list.payment_currency_code_l
,sel_inv_list.external_bank_account_id_l
,sel_inv_list.legal_entity_id_l
,sel_inv_list.global_attribute1_l
,sel_inv_list.global_attribute2_l
,sel_inv_list.global_attribute3_l
,sel_inv_list.global_attribute4_l
,sel_inv_list.global_attribute5_l
,sel_inv_list.global_attribute6_l
,sel_inv_list.global_attribute7_l
,sel_inv_list.global_attribute8_l
,sel_inv_list.global_attribute9_l
,sel_inv_list.global_attribute10_l
,sel_inv_list.global_attribute11_l
,sel_inv_list.global_attribute12_l
,sel_inv_list.global_attribute13_l
,sel_inv_list.global_attribute14_l
,sel_inv_list.global_attribute15_l
,sel_inv_list.global_attribute16_l
,sel_inv_list.global_attribute17_l
,sel_inv_list.global_attribute18_l
,sel_inv_list.global_attribute19_l
,sel_inv_list.global_attribute20_l
,sel_inv_list.global_attribute_category_l
,sel_inv_list.amount_paid_l
,sel_inv_list.discount_amount_taken_l
,sel_inv_list.amount_remaining_l
,sel_inv_list.discount_amount_remaining_l
,sel_inv_list.payment_amount_l
,sel_inv_list.discount_amount_l
,sel_inv_list.sequence_num_l
,sel_inv_list.dont_pay_reason_code_l
,sel_inv_list.check_number_l
,sel_inv_list.bank_account_type_l
,sel_inv_list.original_invoice_id_l
,sel_inv_list.original_payment_num_l
,sel_inv_list.bank_account_num_l
,sel_inv_list.bank_num_l
,sel_inv_list.proposed_payment_amount_l
,sel_inv_list.pay_selected_check_id_l
,sel_inv_list.print_selected_check_id_l
,sel_inv_list.withhloding_amount_l
,sel_inv_list.invoice_payment_id_l
,sel_inv_list.dont_pay_description_l
,sel_inv_list.transfer_priority_l
,sel_inv_list.iban_number_l
,sel_inv_list.payment_grouping_number_l
,sel_inv_list.payment_exchange_rate_l
,sel_inv_list.payment_exchange_rate_type_l
,sel_inv_list.payment_exchange_date_l
--Start of 8217641
,sel_inv_list.remit_to_supplier_name_l
,sel_inv_list.remit_to_supplier_id_l
,sel_inv_list.remit_to_supplier_site_l
,sel_inv_list.remit_to_supplier_site_id_l
--End 8217641
LIMIT 1000;
l_debug_info := 'Update ap_payment_schedules_all: encumbrances are off';
UPDATE Ap_Payment_Schedules_All
SET checkrun_id = sel_inv_list.checkrun_id_l(i)
WHERE invoice_id = sel_inv_list.invoice_id_l(i)
AND payment_num = sel_inv_list.payment_num_l(i)
AND checkrun_id IS NULL --bug 6788730
;
l_debug_info := 'Insert into ap_selected_invoices_all: encumbrances are off';
INSERT INTO ap_selected_invoices_all
(checkrun_name
,checkrun_id
,invoice_id
,payment_num
,last_update_date
,last_updated_by
,creation_date
,created_by
,vendor_id
,vendor_site_id
,vendor_num
,vendor_name
,vendor_site_code
,address_line1
,address_line2
,address_line3
,address_line4
,city
,state
,zip
,province
,country
,attention_ar_flag
,withholding_status_lookup_code
,invoice_num
,invoice_date
,voucher_num
,ap_ccid
,due_date
,discount_date
,invoice_description
,payment_priority
,ok_to_pay_flag
,always_take_discount_flag
,amount_modified_flag
,invoice_amount
,payment_cross_rate
,invoice_exchange_rate
,set_of_books_id
,customer_num
,future_pay_due_date
,exclusive_payment_flag
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category
,org_id
,payment_currency_code
,external_bank_account_id
,legal_entity_id
,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
,global_attribute_category
--Start of 8217641
,remit_to_supplier_name
,remit_to_supplier_id
,remit_to_supplier_site
,remit_to_supplier_site_id
--End 8217641
,affects_rejection_level
--bug12349626
)
--bug 6788730 Changed this to SELECT
--
VALUES /* Bug 13321621 Changed back to insert by values */
(
/* Commented for bug 13321621 */
--SELECT /*+ INDEX(AP_PAYMENT_SCHEDULES_ALL,AP_PAYMENT_SCHEDULES_U1) */
sel_inv_list.checkrun_name_l(i)
,sel_inv_list.checkrun_id_l(i)
,sel_inv_list.invoice_id_l(i)
,sel_inv_list.payment_num_l(i)
,sel_inv_list.last_update_date_l(i)
,sel_inv_list.last_updated_by_l(i)
,sel_inv_list.creation_date_l(i)
,sel_inv_list.created_by_l(i)
,sel_inv_list.vendor_id_l(i)
,sel_inv_list.vendor_site_id_l(i)
,sel_inv_list.vendor_num_l(i)
,sel_inv_list.vendor_name_l(i)
,sel_inv_list.vendor_site_code_l(i)
,sel_inv_list.address_line1_l(i)
,sel_inv_list.address_line2_l(i)
,sel_inv_list.address_line3_l(i)
,sel_inv_list.address_line4_l(i)
,sel_inv_list.city_l(i)
,sel_inv_list.state_l(i)
,sel_inv_list.zip_l(i)
,sel_inv_list.province_l(i)
,sel_inv_list.country_l(i)
,sel_inv_list.attention_ar_flag_l(i)
,sel_inv_list.withholding_status_lookup_l(i)
,sel_inv_list.invoice_num_l(i)
,sel_inv_list.invoice_date_l(i)
,sel_inv_list.voucher_num_l(i)
,sel_inv_list.ap_ccid_l(i)
,sel_inv_list.due_date_l(i)
,sel_inv_list.discount_date_l(i)
,sel_inv_list.invoice_description_l(i)
,sel_inv_list.payment_priority_l(i)
,sel_inv_list.ok_to_pay_flag_l(i)
,sel_inv_list.always_take_disc_flag_l(i)
,sel_inv_list.amount_modified_flag_l(i)
,sel_inv_list.invoice_amount_l(i)
,sel_inv_list.payment_cross_rate_l(i)
,sel_inv_list.invoice_exchange_rate_l(i)
,sel_inv_list.set_of_books_id_l(i)
,sel_inv_list.customer_num_l(i)
,sel_inv_list.future_pay_due_date_l(i)
,sel_inv_list.exclusive_payment_flag_l(i)
,sel_inv_list.attribute1_l(i)
,sel_inv_list.attribute2_l(i)
,sel_inv_list.attribute3_l(i)
,sel_inv_list.attribute4_l(i)
,sel_inv_list.attribute5_l(i)
,sel_inv_list.attribute6_l(i)
,sel_inv_list.attribute7_l(i)
,sel_inv_list.attribute8_l(i)
,sel_inv_list.attribute9_l(i)
,sel_inv_list.attribute10_l(i)
,sel_inv_list.attribute11_l(i)
,sel_inv_list.attribute12_l(i)
,sel_inv_list.attribute13_l(i)
,sel_inv_list.attribute14_l(i)
,sel_inv_list.attribute15_l(i)
,sel_inv_list.attribute_category_l(i)
,sel_inv_list.org_id_l(i)
,sel_inv_list.payment_currency_code_l(i)
,sel_inv_list.external_bank_account_id_l(i)
,sel_inv_list.legal_entity_id_l(i)
,sel_inv_list.global_attribute1_l(i)
,sel_inv_list.global_attribute2_l(i)
,sel_inv_list.global_attribute3_l(i)
,sel_inv_list.global_attribute4_l(i)
,sel_inv_list.global_attribute5_l(i)
,sel_inv_list.global_attribute6_l(i)
,sel_inv_list.global_attribute7_l(i)
,sel_inv_list.global_attribute8_l(i)
,sel_inv_list.global_attribute9_l(i)
,sel_inv_list.global_attribute10_l(i)
,sel_inv_list.global_attribute11_l(i)
,sel_inv_list.global_attribute12_l(i)
,sel_inv_list.global_attribute13_l(i)
,sel_inv_list.global_attribute14_l(i)
,sel_inv_list.global_attribute15_l(i)
,sel_inv_list.global_attribute16_l(i)
,sel_inv_list.global_attribute17_l(i)
,sel_inv_list.global_attribute18_l(i)
,sel_inv_list.global_attribute19_l(i)
,sel_inv_list.global_attribute20_l(i)
,sel_inv_list.global_attribute_category_l(i)
--Start of 8217641
,sel_inv_list.remit_to_supplier_name_l(i)
,sel_inv_list.remit_to_supplier_id_l(i)
,sel_inv_list.remit_to_supplier_site_l(i)
,sel_inv_list.remit_to_supplier_site_id_l(i)
--End 8217641
,'N'
--bug12349626
/* Commented for bug 13321621
FROM Ap_Payment_Schedules_All
WHERE invoice_id = sel_inv_list.invoice_id_l(i)
AND payment_num = sel_inv_list.payment_num_l(i)
AND checkrun_id = sel_inv_list.checkrun_id_l(i)
*/
--bug 6788730
);
l_debug_info := 'Done Inserting Into Ap_Selected_Invoices_AlL';
UPDATE Ap_Payment_Schedules_All aps
SET checkrun_id = null
WHERE checkrun_id = l_checkrun_id
AND NOT EXISTS (SELECT /*+HASH_AJ */ 'no row in asi'
FROM ap_selected_invoices_all asi
WHERE asi.invoice_id = aps.invoice_id
AND asi.payment_num = aps.payment_num
AND asi.checkrun_id = l_checkrun_id);
UPDATE AP_SELECTED_INVOICES_ALL ASI
SET OK_TO_PAY_FLAG = 'N',
DONT_PAY_REASON_CODE = 'PERIOD CLOSED'
WHERE CHECKRUN_ID = l_checkrun_id
AND EXISTS
(SELECT NULL
FROM AP_SELECTED_INVOICES_ALL ASI2
WHERE ASI.INVOICE_ID =ASI2.INVOICE_ID
AND ASI.PAYMENT_NUM = ASI2.PAYMENT_NUM
AND ASI2.CHECKRUN_ID = l_checkrun_id
AND NOT EXISTS
(SELECT NULL
FROM GL_PERIOD_STATUSES GLPS
WHERE TRUNC(l_check_date) BETWEEN GLPS.START_DATE AND GLPS.END_DATE
AND GLPS.CLOSING_STATUS = 'O' --For Payment Only Open Periods are allowed
AND GLPS.APPLICATION_ID = 200
AND GLPS.SET_OF_BOOKS_ID = ASI2.SET_OF_BOOKS_ID));
/* Added INDEX(ASI2 AP_SELECTED_INVOICES_N1) And if condition for bug#12725493 */
IF trunc(l_check_date) < trunc(sysdate)
THEN
UPDATE Ap_Selected_Invoices_All ASI
SET ok_to_pay_flag = 'N',
dont_pay_reason_code = 'PRE DATE NOT ALLOWED'
WHERE checkrun_id = l_checkrun_id
AND Exists (SELECT /*+NO_UNNEST INDEX(ASI2 AP_SELECTED_INVOICES_N1)*/ NULL
FROM Ap_Selected_Invoices_All ASI2,
Ap_System_Parameters_All ASP
WHERE ASI.invoice_id = ASI2.invoice_id
AND ASI.payment_num = ASI2.payment_num
AND ASI2.checkrun_id = l_checkrun_id
AND ASI2.org_id = ASP.org_id
AND ASI2.set_of_books_id = ASP.set_of_books_id
AND NVL(ASP.post_dated_payments_flag, 'N') = 'N'
);
UPDATE AP_SELECTED_INVOICES_ALL ASI
SET OK_TO_PAY_FLAG = 'N',
DONT_PAY_REASON_CODE = 'INVALID REMIT SUPPLIER'
WHERE CHECKRUN_ID = l_checkrun_id
AND EXISTS
(SELECT NULL
FROM AP_SELECTED_INVOICES_ALL ASI2
WHERE ASI.INVOICE_ID =ASI2.INVOICE_ID
AND ASI.PAYMENT_NUM = ASI2.PAYMENT_NUM
AND ASI2.CHECKRUN_ID = l_checkrun_id
--introduced for 8403042/8404650
AND EXISTS
(SELECT 1
FROM AP_INVOICES_ALL AI
WHERE AI.INVOICE_ID = ASI2.INVOICE_ID
AND NVL(AI.RELATIONSHIP_ID,-1) <> -1)
--end of 8403042/8404650
AND NOT EXISTS
(SELECT NULL
FROM iby_ext_payee_relationships irel
WHERE irel.party_id = (select party_id
from ap_suppliers
where vendor_id = ASI2.vendor_id)
AND irel.supplier_site_id =ASI2.vendor_site_id
AND irel.remit_party_id = (select party_id
from ap_suppliers
where vendor_id = ASI2.remit_to_supplier_id)
AND irel.remit_supplier_site_id = ASI2.remit_to_supplier_site_id
AND irel.active = 'Y'
AND to_char(l_check_date,'YYYY-MM-DD HH24:MI:SS')
BETWEEN(to_char(irel.from_date, 'YYYY-MM-DD')||' 00:00:00')
AND(to_char(nvl(irel.to_date,l_check_date),'YYYY-MM-DD') || ' 23:59:59')));
l_debug_info := 'Calling Insert_UnselectedL';
insert_unselected( l_payment_process_request_name,
l_hi_payment_priority,
l_low_payment_priority,
l_invoice_batch_id,
l_inv_vendor_id,
l_inv_exchange_rate_type,
l_payment_method,
l_supplier_type,
l_le_group_option,
l_ou_group_option,
l_currency_group_option,
l_pay_group_option,
l_zero_invoices_allowed,
l_check_date,
l_checkrun_id,
l_current_calling_sequence,
l_party_id,
l_pay_thru_date_char,
l_pay_from_date_char,
l_check_date_char,
l_disc_pay_thru_char);
l_debug_info := 'Update amounts in ap_selected_invoices';
UPDATE ap_selected_invoices_all asi
SET (amount_remaining,
discount_amount_remaining,
payment_amount,
proposed_payment_amount,
discount_amount)
=
(SELECT
PS.amount_remaining,
0,
decode(ai.invoice_type_lookup_code,'PAYMENT REQUEST',ps.amount_remaining,
DECODE(ps.amount_remaining, 0, 0, /* Added DECODE statement for bug 10322208 */
PS.amount_remaining
- (DECODE(PS.GROSS_AMOUNT,
0, 0,
DECODE(asi.ALWAYS_TAKE_DISCOUNT_FLAG,
'Y', NVL(PS.DISCOUNT_AMOUNT_AVAILABLE,0),
GREATEST(DECODE(SIGN(l_check_date
- NVL(PS.DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.DISCOUNT_AMOUNT_AVAILABLE),0)),
DECODE(SIGN(l_check_date
- NVL(PS.SECOND_DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.SECOND_DISC_AMT_AVAILABLE),0)),
DECODE(SIGN(l_check_date
- NVL(PS.THIRD_DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.THIRD_DISC_AMT_AVAILABLE),0)),
0) * DECODE(SIGN(ps.gross_amount),-1,-1,1))
* (PS.AMOUNT_REMAINING / DECODE(PS.GROSS_AMOUNT,
0, 1,
prorated_awt_gross_amt(ps.invoice_id, ps.payment_num) )))))),
decode(ai.invoice_type_lookup_code,'PAYMENT REQUEST', ps.amount_remaining,
DECODE(ps.amount_remaining, 0, 0, /* Added DECODE statement for bug 10322208 */
PS.amount_remaining
- (DECODE(PS.GROSS_AMOUNT,
0, 0,
DECODE(asi.ALWAYS_TAKE_DISCOUNT_FLAG,
'Y', NVL(PS.DISCOUNT_AMOUNT_AVAILABLE,0),
GREATEST(DECODE(SIGN(l_check_date
- NVL(PS.DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.DISCOUNT_AMOUNT_AVAILABLE),0)),
DECODE(SIGN(l_check_date
- NVL(PS.SECOND_DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.SECOND_DISC_AMT_AVAILABLE),0)),
DECODE(SIGN(l_check_date
- NVL(PS.THIRD_DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.THIRD_DISC_AMT_AVAILABLE),0)),
0) * DECODE(SIGN(ps.gross_amount),-1,-1,1))
* (PS.AMOUNT_REMAINING / DECODE(PS.GROSS_AMOUNT,
0, 1,
prorated_awt_gross_amt(ps.invoice_id, ps.payment_num) )))))),
decode(ai.invoice_type_lookup_code,'PAYMENT REQUEST', 0,
DECODE(ps.amount_remaining, 0, 0, /* Added DECODE statement for bug 10322208 */
DECODE(PS.GROSS_AMOUNT,
0, 0,
DECODE(asi.ALWAYS_TAKE_DISCOUNT_FLAG,
'Y', NVL(PS.DISCOUNT_AMOUNT_AVAILABLE,0),
GREATEST(DECODE(SIGN(l_check_date
- NVL(PS.DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.DISCOUNT_AMOUNT_AVAILABLE),0)),
DECODE(SIGN(l_check_date
- NVL(PS.SECOND_DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.SECOND_DISC_AMT_AVAILABLE),0)),
DECODE(SIGN(l_check_date
- NVL(PS.THIRD_DISCOUNT_DATE,
TO_DATE('01/01/1901',
'MM/DD/YYYY'))),
1, 0,
NVL(ABS(PS.THIRD_DISC_AMT_AVAILABLE),0)),
0) * DECODE(SIGN(ps.gross_amount),-1,-1,1))
* (PS.AMOUNT_REMAINING / DECODE(PS.GROSS_AMOUNT,
0, 1,
prorated_awt_gross_amt(ps.invoice_id, ps.payment_num) )))))
FROM ap_payment_schedules_all PS,
ap_invoices ai --Bug6040657. Changed from ap_invoices_all to ap_invoices
WHERE PS.invoice_id = asi.invoice_id
AND PS.payment_num = asi.payment_num
and ai.invoice_id = ps.invoice_id)
WHERE checkrun_id = l_checkrun_id;
l_debug_info := 'Round amounts in ap_selected_invoices';
UPDATE ap_selected_invoices_all ASI
SET payment_amount = ap_utilities_pkg.ap_round_currency
(payment_amount,payment_currency_code),
proposed_payment_amount = ap_utilities_pkg.ap_round_currency
(proposed_payment_amount,payment_currency_code) ,
discount_amount = ap_utilities_pkg.ap_round_currency
(discount_amount,payment_currency_code)
WHERE checkrun_id= l_checkrun_id;
update ap_selected_invoices_all
set ok_to_pay_flag = 'N',
dont_pay_reason_code = 'ZERO INVOICE'
WHERE checkrun_id = l_checkrun_id
AND l_zero_invoices_allowed = 'N'
AND amount_remaining = 0;
update ap_selected_invoices_all asi
set (payment_exchange_rate_type, payment_exchange_rate) =
(select 'User', /* exchange_rate Commented for bug#12660492 */
/* Added for bug#12660492 Start */
decode( nvl( fnd_profile.value('DISPLAY_INVERSE_RATE'),'N'),
'N',exchange_rate,
'Y', 1/exchange_rate
)
/* Added for bug#12660492 End */
from ap_user_exchange_rates auer,
ap_system_parameters_all asp
where asp.org_id = asi.org_id
and asi.payment_currency_code = auer.payment_currency_code
and asp.base_currency_code = auer.ledger_currency_code
and asp.base_currency_code <> asi.payment_currency_code
and auer.checkrun_id = l_checkrun_id)
where checkrun_id = l_checkrun_id
and (invoice_id, payment_num) in
(select invoice_id, payment_num
from ap_selected_invoices_all asi2,
ap_system_parameters_all asp2
where asp2.org_id = asi2.org_id
and asp2.base_currency_code <> asi2.payment_currency_code
and asi2.checkrun_id = l_checkrun_id);
update ap_selected_invoices asi
set (payment_exchange_rate_type, payment_exchange_rate) =
(select l_batch_exchange_rate_type,
ap_utilities_pkg.get_exchange_rate(
asi.payment_currency_code,
asp.base_currency_code,
l_batch_exchange_rate_type,
l_check_date,
'AUTOSELECT')
from ap_system_parameters_all asp
where asp.org_id = asi.org_id
and asp.base_currency_code <> asi.payment_currency_code)
where checkrun_id = l_checkrun_id
and (invoice_id, payment_num) in
(select invoice_id, payment_num
from ap_selected_invoices_all asi2,
ap_system_parameters_all asp2
where asp2.org_id = asi2.org_id
and asp2.base_currency_code <> asi2.payment_currency_code
and asi2.checkrun_id = l_checkrun_id); --Bug 5123855
select count(*)
into l_missing_rates_count
from ap_selected_invoices_all asi,
ap_system_parameters_all asp
where asi.org_id = asp.org_id
and asi.checkrun_id = l_checkrun_id
and asi.payment_currency_code <> asp.base_currency_code
and asi.payment_exchange_rate is null
and ((l_batch_exchange_rate_type <> 'User'
and asp.make_rate_mandatory_flag = 'Y') OR
l_batch_exchange_rate_type = 'User')
and rownum = 1;
update ap_inv_selection_criteria_all
set status = 'MISSING RATES'
where checkrun_id = l_checkrun_id;
insert into ap_user_exchange_rates auer
(checkrun_id,
payment_currency_code,
ledger_currency_code,
creation_date,
created_by, --Bug 5123855
last_update_date,
last_updated_by,
last_update_login)
(select l_checkrun_id,
asi.payment_currency_code,
asp.base_currency_code,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id
from ap_selected_invoices_all asi,
ap_system_parameters_all asp
where asi.payment_exchange_rate is null
and asp.org_id = asi.org_id
and asp.base_currency_code <> asi.payment_currency_code
and asi.checkrun_id = l_checkrun_id
group by asi.payment_currency_code, /* bug 5447896 */
asp.base_currency_code);
been finally selected for the PPR, and not for Invoices which
have been de-selected from the PPR. */
/* BUG 14498596 Start - Moved Withholding Tax logic before grouping of invoices */
l_debug_info := 'Calling ap_withholding_pkg';
AP_WITHHOLDING_PKG.AP_WITHHOLD_AUTOSELECT(
l_payment_process_request_name,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
FND_GLOBAL.CONC_REQUEST_ID,
l_checkrun_id);
l_debug_info := 'Grouping selected invoices';
/* Need to gather table stat for ap_selected_invoices_all Since all insert/ update has
already been done on the table */
IF ( FND_INSTALLATION.GET_APP_INFO('SQLAP', l_status, l_industry, l_schema)
AND nvl( fnd_profile.value('AP_GATHER_PPR_TABLE_STATS'), 'N') = 'Y'
)
THEN
IF l_schema IS NOT NULL THEN
FND_STATS.GATHER_TABLE_STATS(l_schema, 'AP_SELECTED_INVOICES_ALL');
FND_STATS.GATHER_TABLE_STATS(l_schema, 'AP_UNSELECTED_INVOICES_ALL'); /* Added for bug#10053374 */
select invoice_id,
vendor_id,
payment_num,
checkrun_id,
withholding_amount,
checkrun_name
from ap_selected_invoices_all
where checkrun_id = p_checkrun_id
and ok_to_pay_flag = 'N';
l_debug_info := 'Fetch CURSOR for all UN-SELECTed invoices';
/* Added below update for bug 8888311 */
UPDATE ap_awt_temp_distributions_all aatd
SET withholding_amount = rec_dont_pay_invoices.withholding_amount
WHERE invoice_id = rec_dont_pay_invoices.invoice_id
AND payment_num =rec_dont_pay_invoices.payment_num
AND checkrun_id = rec_dont_pay_invoices.checkrun_id
AND nvl(rec_dont_pay_invoices.withholding_amount,0) = 0;
,P_Calling_Module => 'AUTOSELECT'
,P_Last_Updated_By => FND_GLOBAL.USER_ID
,P_Last_Update_Login => FND_GLOBAL.LOGIN_ID
,P_Program_Application_Id => FND_GLOBAL.PROG_APPL_ID
,P_Program_Id => FND_GLOBAL.CONC_PROGRAM_ID
,P_Request_Id => FND_GLOBAL.CONC_REQUEST_ID
,P_Awt_Success => undo_output
,P_checkrun_id => p_checkrun_id );
l_debug_info := 'CLOSE CURSOR for all UN-SELECTed invoices';
select document_rejection_level_code,
payment_rejection_level_code,
inv_awt_exists_flag
into l_doc_rejection_level_code,
l_pay_rejection_level_code,
l_inv_awt_exists_flag
from ap_inv_selection_criteria_all
where checkrun_id = l_checkrun_id;
update ap_selected_invoices_All asi
set asi.AFFECTS_REJECTION_LEVEL= 'Y'
where asi.checkrun_id = l_checkrun_id
and exists ( select 'ATLEAST ONE RANGE BASED PAY TIME AWT'
from ap_invoice_distributions_all aid,
AP_AWT_GROUP_TAXES_all awtt,
ap_tax_codes_all awtc
where aid.invoice_id = asi.invoice_id
and aid.pay_awt_group_id is not null
and awtt.group_id = aid.pay_awt_group_id
and awtt.tax_name = awtc.name
and awtc.tax_type = 'AWT'
and awtc.awt_rate_type <> 'F'
and awtc.enabled_flag = 'Y'
and NVL(asi.invoice_date,SYSDATE) BETWEEN
NVL(awtc.start_date,NVL(asi.invoice_date,SYSDATE)) AND
NVL(awtc.inactive_date,NVL(asi.invoice_date,SYSDATE))
);
select count(*)
into l_count_inv_selected
from ap_selected_invoices_all
where checkrun_id = l_checkrun_id
and rownum = 1;
if l_count_inv_selected = 0 then
update ap_inv_selection_criteria_all
set status = 'CANCELLED NO PAYMENTS'
where checkrun_id = l_checkrun_id;
fnd_file.put_line(FND_FILE.LOG, 'No scheduled payments matched the invoice selection criteria');
l_debug_info := 'No scheduled payments matched the invoice selection criteria';
update ap_inv_selection_criteria_all
set status = decode(l_payables_review_settings,'Y','REVIEW','SELECTED')
where status = 'SELECTING'
and checkrun_id = l_checkrun_id;
SELECT lower(iso_language),iso_territory
INTO l_iso_language,l_iso_territory
FROM FND_LANGUAGES
WHERE language_code = USERENV('LANG');
SELECT nvl(template_code, 'APINVSEL' )
INTO l_template_code
FROM Fnd_Concurrent_Programs
WHERE concurrent_program_name = 'APINVSEL'; --Bug 6969710
SELECT nvl(template_code, 'APINVSEL' )
, (SELECT Nvl(DEFAULT_OUTPUT_TYPE,'PDF')
FROM XDO_TEMPLATES_B xtb
WHERE xtb.TEMPLATE_CODE = nvl(fcp.template_code, 'APINVSEL' )
AND APPLICATION_ID = 200
AND rownum = 1
)
INTO l_template_code
, l_output_format
FROM Fnd_Concurrent_Programs fcp
WHERE concurrent_program_name = 'APINVSEL';
select status
into l_batch_status
from ap_inv_selection_criteria_all
where checkrun_id = l_checkrun_id;
if l_batch_status = 'SELECTED' and l_payables_review_settings <> 'Y' then
l_debug_info := 'Submitting Oracle Payments Build';
/* Bug 11063950 : Populated Request_id in ap_inv_selection_criteria_all */
update ap_inv_selection_criteria_all
set request_id = l_req_id
where checkrun_id = l_checkrun_id;
END SELECT_INVOICES;
l_checkrun_name ap_inv_selection_criteria_all.checkrun_name%type;-- bug# 6643035
SELECT invoice_id
, vendor_id
, payment_num
, checkrun_id --bug 8888311
, withholding_amount --bug 8888311
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 = p_checkrun_id
AND decode(nvl(ASP.allow_awt_flag, 'N'), 'Y',
decode(ASP.create_awt_dists_type,'BOTH','Y','PAYMENT',
'Y', decode(ASP.create_awt_invoices_type,'BOTH','Y','PAYMENT',
'Y', 'N'),
'N'),
'N') = 'Y'; --Bug6660355
l_current_calling_sequence := 'ap_autoselect_pkg.recalculate';
update ap_selected_invoices_all asi
set exclusive_payment_flag = (select nvl(exclusive_payment_flag, 'N')
from ap_invoices_all
where invoice_id = asi.invoice_id)
where checkrun_id = p_checkrun_id
and exists
( select 1
from ap_selected_invoices_all asi2
where asi2.original_invoice_id is not null
and asi2.original_invoice_id = asi.invoice_id
and asi2.ok_to_pay_flag = 'Y'
and asi2.checkrun_id = p_checkrun_id
);
l_debug_info:= 'delete interest invoices';
delete from ap_selected_invoices_all
where checkrun_id = p_checkrun_id
and original_invoice_id is not null;
SELECT
checkrun_name,
check_date,
nvl(zero_amounts_allowed,'N'),
nvl(zero_invoices_allowed,'N'), -- Bug 6523501
bank_account_id, --4710933
payment_profile_id,
max_payment_amount,
min_check_amount,
payments_review_settings,
--decode(payment_profile_id,null,'N',nvl(create_instrs_flag,'N')),
nvl(create_instrs_flag,'N'), -- Commented and added for bug 8925444
document_rejection_level_code,
payment_rejection_level_code,
exchange_rate_type,
payment_document_id,
/*bug 7519277*/
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
/*bug 7519277*/
INTO l_checkrun_name,
l_check_date,
l_zero_amounts_allowed,
l_zero_invoices_allowed, -- Bug 6523501
l_bank_account_id,
l_payment_profile_id,
l_max_payment_amount,
l_min_check_amount,
l_pay_review_settings_flag,
l_create_instrs_flag,
l_doc_rejection_level_code,
l_pay_rejection_level_code,
l_batch_exchange_rate_type,
l_payment_document_id,
/* bug 7519277*/
l_ATTRIBUTE_CATEGORY,
l_ATTRIBUTE1,
l_ATTRIBUTE2,
l_ATTRIBUTE3,
l_ATTRIBUTE4,
l_ATTRIBUTE5,
l_ATTRIBUTE6,
l_ATTRIBUTE7,
l_ATTRIBUTE8,
l_ATTRIBUTE9,
l_ATTRIBUTE10,
l_ATTRIBUTE11,
l_ATTRIBUTE12,
l_ATTRIBUTE13,
l_ATTRIBUTE14,
l_ATTRIBUTE15
/*bug 7519277*/
FROM ap_inv_selection_criteria_all
WHERE checkrun_id = p_checkrun_id;
l_debug_info := 'Fetch CURSOR for all SELECTed invoices';
/* Added below update for bug 8888311 */
UPDATE ap_awt_temp_distributions_all aatd
SET withholding_amount = rec_all_sel_invs.withholding_amount
WHERE invoice_id = rec_all_sel_invs.invoice_id
AND payment_num = rec_all_sel_invs.payment_num
AND checkrun_id = rec_all_sel_invs.checkrun_id
AND nvl(rec_all_sel_invs.withholding_amount,0) = 0;
,P_Calling_Module => 'AUTOSELECT'
,P_Last_Updated_By => FND_GLOBAL.USER_ID
,P_Last_Update_Login => FND_GLOBAL.LOGIN_ID
,P_Program_Application_Id => FND_GLOBAL.PROG_APPL_ID
,P_Program_Id => FND_GLOBAL.CONC_PROGRAM_ID
,P_Request_Id => FND_GLOBAL.CONC_REQUEST_ID
,P_Awt_Success => undo_output
,P_checkrun_id => p_checkrun_id );
l_debug_info := 'CLOSE CURSOR for all SELECTed invoices';
update ap_selected_invoices_all
set payment_grouping_number = null
where checkrun_id = p_checkrun_id;
update ap_inv_selection_criteria_all
set inv_awt_exists_flag = 'N'
where checkrun_id = p_checkrun_id;
update ap_selected_invoices_all asi
set (payment_exchange_rate_type, payment_exchange_rate) =
(select 'User', /* exchange_rate Commented for bug#12660492 */
/* Added for bug#12660492 Start */
decode( nvl( fnd_profile.value('DISPLAY_INVERSE_RATE'),'N'),
'N',exchange_rate,
'Y', 1/exchange_rate
)
/* Added for bug#12660492 End */
from ap_user_exchange_rates auer,
ap_system_parameters_all asp
where asp.org_id = asi.org_id
and asi.payment_currency_code = auer.payment_currency_code
and asp.base_currency_code = auer.ledger_currency_code
and asp.base_currency_code <> asi.payment_currency_code
and auer.checkrun_id = p_checkrun_id) --Bug 5123855
where checkrun_id = p_checkrun_id
and (invoice_id, payment_num) in
(select invoice_id, payment_num
from ap_selected_invoices_all asi2,
ap_system_parameters_all asp2
where asp2.org_id = asi2.org_id
and asp2.base_currency_code <> asi2.payment_currency_code
and asi2.checkrun_id = p_checkrun_id);
update ap_selected_invoices asi
set (payment_exchange_rate_type, payment_exchange_rate) =
(select l_batch_exchange_rate_type,
ap_utilities_pkg.get_exchange_rate(
asi.payment_currency_code,
asp.base_currency_code,
l_batch_exchange_rate_type,
l_check_date,
'AUTOSELECT')
from ap_system_parameters_all asp
where asp.org_id = asi.org_id
and asp.base_currency_code <> asi.payment_currency_code)
where checkrun_id = p_checkrun_id
and (invoice_id, payment_num) in
(select invoice_id, payment_num
from ap_selected_invoices_all asi2,
ap_system_parameters_all asp2
where asp2.org_id = asi2.org_id
and asp2.base_currency_code <> asi2.payment_currency_code
and asi2.checkrun_id = p_checkrun_id);
select count(*)
into l_missing_rates_count
from ap_selected_invoices_all asi,
ap_system_parameters_all asp
where asi.org_id = asp.org_id
and asi.checkrun_id = p_checkrun_id
and asi.payment_currency_code <> asp.base_currency_code
and asi.payment_exchange_rate is null
and ((l_batch_exchange_rate_type <> 'User'
and asp.make_rate_mandatory_flag = 'Y') OR
l_batch_exchange_rate_type = 'User')
and rownum = 1;
update ap_inv_selection_criteria_all
set status = 'MISSING RATES'
where checkrun_id = p_checkrun_id;
insert into ap_user_exchange_rates auer
(checkrun_id,
payment_currency_code,
ledger_currency_code,
creation_date, --Bug 5123855
created_by,
last_update_date,
last_updated_by,
last_update_login)
(select p_checkrun_id,
asi.payment_currency_code,
asp.base_currency_code,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id
from ap_selected_invoices_all asi,
ap_system_parameters_all asp
where asi.payment_exchange_rate is null
and asp.org_id = asi.org_id
and asp.base_currency_code <> asi.payment_currency_code
and asi.checkrun_id = p_checkrun_id
and not exists (select 'row already in auer'
from ap_user_exchange_rates auer2
where auer2.checkrun_id = asi.checkrun_id
and auer2.payment_currency_code = asi.payment_currency_code
and auer2.ledger_currency_code = asp.base_currency_code));
AP_WITHHOLDING_PKG.AP_WITHHOLD_AUTOSELECT(
l_checkrun_name,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
FND_GLOBAL.CONC_REQUEST_ID,
p_checkrun_id);
select invoice_id,
vendor_id,
payment_num,
checkrun_id,
withholding_amount,
checkrun_name
from ap_selected_invoices_all
where checkrun_id = p_checkrun_id
and ok_to_pay_flag = 'N';
l_debug_info := 'Fetch CURSOR for all UN-SELECTed invoices';
/* Added below update for bug 8888311 */
UPDATE ap_awt_temp_distributions_all aatd
SET withholding_amount = rec_dont_pay_invoices.withholding_amount
WHERE invoice_id = rec_dont_pay_invoices.invoice_id
AND payment_num =rec_dont_pay_invoices.payment_num
AND checkrun_id = rec_dont_pay_invoices.checkrun_id
AND nvl(rec_dont_pay_invoices.withholding_amount,0) = 0;
,P_Calling_Module => 'AUTOSELECT'
,P_Last_Updated_By => FND_GLOBAL.USER_ID
,P_Last_Update_Login => FND_GLOBAL.LOGIN_ID
,P_Program_Application_Id => FND_GLOBAL.PROG_APPL_ID
,P_Program_Id => FND_GLOBAL.CONC_PROGRAM_ID
,P_Request_Id => FND_GLOBAL.CONC_REQUEST_ID
,P_Awt_Success => undo_output
,P_checkrun_id => p_checkrun_id );
l_debug_info := 'CLOSE CURSOR for all UN-SELECTed invoices';
check if inv_awt_Exists_flag is Y,if yes, then update asi
this avoids running the update on asi unless necessary */
Select inv_awt_exists_flag
into l_inv_awt_exists_flag
from ap_inv_selection_criteria_all
WHERE checkrun_id = p_checkrun_id;
update ap_selected_invoices_All asi
set asi.AFFECTS_REJECTION_LEVEL= 'Y'
where asi.checkrun_id = p_checkrun_id
and exists ( select 'ATLEAST ONE RANGE BASED PAY TIME AWT'
from ap_invoice_distributions_all aid,
AP_AWT_GROUP_TAXES_all awtt,
ap_tax_codes_all awtc
where aid.invoice_id = asi.invoice_id
and aid.pay_awt_group_id is not null
and awtt.group_id = aid.pay_awt_group_id
and awtt.tax_name = awtc.name
and awtc.tax_type = 'AWT'
and awtc.awt_rate_type <> 'F'
and awtc.enabled_flag = 'Y'
and NVL(asi.invoice_date,SYSDATE) BETWEEN
NVL(awtc.start_date,NVL(asi.invoice_date,SYSDATE)) AND
NVL(awtc.inactive_date,NVL(asi.invoice_date,SYSDATE))
);
select decode(nvl(inv_awt_exists_flag, 'N'), 'Y', decode(document_rejection_level_code,'REQUEST',document_rejection_level_code,'PAYEE'), document_rejection_level_code) document_rejection_level_code,
decode(nvl(inv_awt_exists_flag, 'N'), 'Y', decode(payment_rejection_level_code,'REQUEST',payment_rejection_level_code,'PAYEE'), payment_rejection_level_code) payment_rejection_level_code
--Bug 8746215 End
into l_doc_rejection_level_code,
l_pay_rejection_level_code
FROM ap_inv_selection_criteria_all
WHERE checkrun_id = p_checkrun_id;
update ap_selected_invoices_all asi
set (payment_exchange_rate_type, payment_exchange_rate) =
(select 'User', exchange_rate
from ap_user_exchange_rates auer,
ap_system_parameters_all asp
where asp.org_id = asi.org_id
and asi.payment_currency_code = auer.payment_currency_code
and asp.base_currency_code = auer.ledger_currency_code
and asp.base_currency_code <> asi.payment_currency_code
and auer.checkrun_id = p_checkrun_id) --Bug 5123855
where checkrun_id = p_checkrun_id
and (invoice_id, payment_num) in
(select invoice_id, payment_num
from ap_selected_invoices_all asi2,
ap_system_parameters_all asp2
where asp2.org_id = asi2.org_id
and asp2.base_currency_code <> asi2.payment_currency_code
and asi2.checkrun_id = p_checkrun_id);
update ap_selected_invoices asi
set (payment_exchange_rate_type, payment_exchange_rate) =
(select l_batch_exchange_rate_type,
ap_utilities_pkg.get_exchange_rate(
asi.payment_currency_code,
asp.base_currency_code,
l_batch_exchange_rate_type,
l_check_date,
'AUTOSELECT')
from ap_system_parameters_all asp
where asp.org_id = asi.org_id
and asp.base_currency_code <> asi.payment_currency_code)
where checkrun_id = p_checkrun_id
and (invoice_id, payment_num) in
(select invoice_id, payment_num
from ap_selected_invoices_all asi2,
ap_system_parameters_all asp2
where asp2.org_id = asi2.org_id
and asp2.base_currency_code <> asi2.payment_currency_code
and asi2.checkrun_id = p_checkrun_id);
select count(*)
into l_missing_rates_count
from ap_selected_invoices_all asi,
ap_system_parameters_all asp
where asi.org_id = asp.org_id
and asi.checkrun_id = p_checkrun_id
and asi.payment_currency_code <> asp.base_currency_code
and asi.payment_exchange_rate is null
and ((l_batch_exchange_rate_type <> 'User'
and asp.make_rate_mandatory_flag = 'Y') OR
l_batch_exchange_rate_type = 'User')
and rownum = 1;
update ap_inv_selection_criteria_all
set status = 'MISSING RATES'
where checkrun_id = p_checkrun_id;
insert into ap_user_exchange_rates auer
(checkrun_id,
payment_currency_code,
ledger_currency_code,
creation_date, --Bug 5123855
created_by,
last_update_date,
last_updated_by,
last_update_login)
(select p_checkrun_id,
asi.payment_currency_code,
asp.base_currency_code,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id
from ap_selected_invoices_all asi,
ap_system_parameters_all asp
where asi.payment_exchange_rate is null
and asp.org_id = asi.org_id
and asp.base_currency_code <> asi.payment_currency_code
and asi.checkrun_id = p_checkrun_id
and not exists (select 'row already in auer'
from ap_user_exchange_rates auer2
where auer2.checkrun_id = asi.checkrun_id
and auer2.payment_currency_code = asi.payment_currency_code
and auer2.ledger_currency_code = asp.base_currency_code));
select status
into l_batch_status
from ap_inv_selection_criteria_all
where checkrun_id = p_checkrun_id;
update ap_inv_selection_criteria_all
set status = 'SELECTED'
where checkrun_id = p_checkrun_id;
SELECT lower(iso_language),iso_territory
INTO l_iso_language,l_iso_territory
FROM FND_LANGUAGES
WHERE language_code = USERENV('LANG');
SELECT nvl(template_code, 'APINVSEL' )
INTO l_template_code
FROM Fnd_Concurrent_Programs
WHERE concurrent_program_name = 'APINVSEL'; --Bug 6969710
SELECT nvl(template_code, 'APINVSEL' )
, (SELECT Nvl(DEFAULT_OUTPUT_TYPE,'PDF')
FROM XDO_TEMPLATES_B xtb
WHERE xtb.TEMPLATE_CODE = nvl(fcp.template_code, 'APINVSEL' )
AND APPLICATION_ID = 200
AND rownum = 1
)
INTO l_template_code
, l_output_format
FROM Fnd_Concurrent_Programs fcp
WHERE concurrent_program_name = 'APINVSEL';
update ap_inv_selection_criteria_all
set status = 'REVIEW'
where checkrun_id = p_checkrun_id;
l_checkrun_name ap_inv_selection_criteria_all.checkrun_name%type;-- bug# 6643035
l_current_calling_sequence := 'ap_autoselect_pkg.cancel_batch';
select checkrun_name
into l_checkrun_name
from ap_inv_selection_criteria_all
where checkrun_id = p_checkrun_id;
select PAYMENT_SERVICE_REQUEST_ID
into l_psr_id
from iby_pay_service_requests
where calling_app_id = 200
and CALL_APP_PAY_SERVICE_REQ_CODE = l_checkrun_name;
Select count(1) --Bug 14336393
into l_fd_count
from iby_fd_docs_payable_v
where calling_app_doc_unique_ref1 = p_checkrun_id
and calling_app_id = 200;
update ap_inv_selection_criteria_all
set status = 'CANCELING'
where checkrun_id = p_checkrun_id;
l_debug_info := 'delete unselected invoices';
delete from ap_unselected_invoices_all
where checkrun_id = p_checkrun_id;
l_debug_info := 'delete selected invoices';
delete from ap_selected_invoices_all
where checkrun_id = p_checkrun_id;
l_debug_info := 'update payment schedules';
update ap_payment_schedules_all
set checkrun_id = null
where checkrun_id = p_checkrun_id;
update ap_inv_selection_criteria_all
set status = 'CANCELED' --seeded with one L
where checkrun_id = p_checkrun_id;
PROCEDURE selection_criteria_report(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_checkrun_id in varchar2)is
l_qryCtx DBMS_XMLGEN.ctxHandle;
l_current_calling_sequence := 'ap_autoselect_pkg.selection_criteria_report';
l_debug_info:= 'select from ap_inv_selection_criteria_all';
fnd_file.put_line(fnd_file.output, '');
'select aisc.checkrun_name, aisc.pay_from_date, aisc.pay_thru_date,
aisc.hi_payment_priority, aisc.low_payment_priority,
aisc.pay_only_when_due_flag, aisc.zero_amounts_allowed,
aisc.zero_invoices_allowed, ab.batch_name,
vndr.meaning supplier_type, hz.party_name,
iby.payment_method_name, rate.displayed_field document_exchange_rate_type,
apt.template_name
from ap_inv_selection_criteria_all aisc,
ap_batches_all ab,
iby_payment_methods_vl iby,
fnd_lookups vndr,
hz_parties hz,
ap_lookup_codes rate,
ap_payment_templates apt
where checkrun_id ='|| p_checkrun_id ||'
and apt.template_id(+) = aisc.template_id
and aisc.invoice_batch_id = ab.batch_id(+)
and aisc.payment_method_code = iby.payment_method_code(+)
and aisc.vendor_type_lookup_code = vndr.lookup_code(+)
and vndr.lookup_type(+) = ''VENDOR TYPE''
and aisc.party_id = hz.party_id(+)
and rate.lookup_type(+) = ''INVOICE_EXCHANGE_RATE_TYPE''
and aisc.inv_exchange_rate_type = rate.lookup_code(+)');
DBMS_XMLGEN.setRowTag(l_qryCtx, 'SELECTION_CRITERIA');
l_debug_info := 'select pay group';
l_qryCtx := DBMS_XMLGEN.newContext('SELECT vendor_pay_group '||
'FROM ap_pay_group '||
'WHERE checkrun_id = '||to_char(p_checkrun_id));
l_debug_info := 'select currency group';
l_qryCtx := DBMS_XMLGEN.newContext('SELECT currency_code '||
'FROM AP_CURRENCY_GROUP '||
'WHERE checkrun_id = '||to_char(p_checkrun_id));
l_debug_info:= 'select le group';
l_qryCtx := DBMS_XMLGEN.newContext('SELECT name legal_entity_name '||
'FROM ap_le_group aleg, xle_entity_profiles xle '||
'WHERE aleg.legal_entity_id = xle.legal_entity_id '||
'AND checkrun_id = '||to_char(p_checkrun_id));
l_debug_info := 'select ou group';
l_qryCtx := DBMS_XMLGEN.newContext('SELECT name organization_name '||
'FROM AP_OU_GROUP AOG, HR_OPERATING_UNITS HR '||
'WHERE hr.organization_id = aog.org_id '||
'AND checkrun_id = '||to_char(p_checkrun_id));
fnd_file.put_line(fnd_file.output, '');
END selection_criteria_report;
SELECT ((0 - SUM(NVL(ail.amount,0)))*ai.payment_cross_rate)
INTO l_prepay_with_tax
FROM ap_invoice_lines_all ail,ap_invoices_all ai
WHERE ail.invoice_id = p_invoice_id
AND ail.invoice_id=ai.invoice_id
AND ail.prepay_invoice_id is not null
AND ail.line_type_lookup_code in ('TAX', 'PREPAY')
AND NVL(ail.invoice_includes_prepay_flag, 'N') = 'N'
GROUP BY ai.payment_cross_rate;
overpaid by the selected invoices records created.
If any are found it will mark the selected invoices
ok_to_pay_flag as N and remove any related awt
temp distributions.
This should be called after all processing that would
change a payment amount is complete.
The remove_invoices procedure should be called after
this procedure
*/
PROCEDURE mark_overpayments ( p_checkrun_id in number,
p_checkrun_name in varchar2,
p_calling_sequence in varchar2) IS
l_debug_info varchar2(2000);
select si.invoice_id
, si.vendor_id
, si.payment_num
from ap_selected_invoices_all si
, ap_awt_temp_distributions_all atd
where si.ok_to_pay_flag = 'N'
and dont_pay_reason_code = 'OVERPAYMENT'
and si.invoice_id = atd.invoice_id
and si.payment_num = atd.payment_num
and si.checkrun_id = p_checkrun_id
and si.checkrun_name = atd.checkrun_name;
update ap_selected_invoices_all si
set ok_to_pay_flag = 'N',
dont_pay_reason_code = 'OVERPAYMENT'
WHERE si.checkrun_id = p_checkrun_id
AND si.invoice_id in
(select /*+ use_nl_index(I AP_INVOICES_U1) */ si2.invoice_id
from ap_selected_invoices_all si2
, ap_invoices_all i
where si2.checkrun_id = p_checkrun_id
and si2.invoice_id = i.invoice_id
and si2.original_invoice_id is null
group by si2.invoice_id,i.invoice_amount, decode(i.net_of_retainage_flag, 'Y', 0,
nvl(AP_INVOICES_UTILITY_PKG.GET_RETAINED_TOTAL(si2.invoice_id, si2.org_id),0)),i.payment_cross_rate
having abs((nvl(i.invoice_amount,0) - nvl(sum(si2.withholding_amount),0)
- nvl(AP_INVOICES_UTILITY_PKG.get_amount_withheld(si2.invoice_id),0)
+ decode(i.net_of_retainage_flag, 'Y', 0,
nvl(AP_INVOICES_UTILITY_PKG.GET_RETAINED_TOTAL(si2.invoice_id, si2.org_id),0)))*i.payment_cross_rate
/* retainage is returned as a negative */
)
< abs( nvl(sum(si2.discount_amount),0) + nvl(sum(si2.payment_amount),0)
+ nvl(get_prepay_with_tax(si2.invoice_id),0)
/* Bug 9570635 */
+ nvl((select sum(nvl(p.amount,0)) + sum(nvl(p.discount_taken,0))
from ap_invoice_payments_all p
where p.invoice_id = si2.invoice_id),0)));
l_debug_info := 'Fetch CURSOR for selected invoices marked for removal that have awt';
,P_Calling_Module => 'AUTOSELECT'
,P_Last_Updated_By => FND_GLOBAL.USER_ID
,P_Last_Update_Login => FND_GLOBAL.LOGIN_ID
,P_Program_Application_Id => FND_GLOBAL.PROG_APPL_ID
,P_Program_Id => FND_GLOBAL.CONC_PROGRAM_ID
,P_Request_Id => FND_GLOBAL.CONC_REQUEST_ID
,P_Awt_Success => undo_output
,P_checkrun_id => p_checkrun_id );
SELECT asi.invoice_id
, asi.vendor_id
, asi.payment_num
, asi.original_invoice_id
, asi.original_payment_num
, asi.payment_grouping_number
FROM ap_selected_invoices_all ASI,
iby_fd_docs_payable_v ibydocs
WHERE checkrun_id = l_checkrun_id
AND ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(ASI.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(ASI.invoice_id)
AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(ASI.payment_num)
AND ibydocs.rejected_docs_group_id = p_rejected_docs_group_id
AND ibydocs.calling_app_id = 200 ;
select calling_app_doc_unique_ref1
into l_checkrun_id
from iby_fd_docs_payable_v
where rejected_docs_group_id = p_rejected_docs_group_id
and rownum = 1;
l_debug_info := 'fetch c_sel_invs for selected schedules.';
SELECT count(*) INTO l_count
FROM ap_selected_invoices_all ASI,
iby_fd_docs_payable_v ibydocs
WHERE ASI.checkrun_id = l_checkrun_id
AND ASI.invoice_id <> rec_sel_invs.invoice_id
AND ( ( rec_sel_invs.original_invoice_id IS NULL -- standard schedule
AND ASI.original_invoice_id = rec_sel_invs.invoice_id
AND ASI.original_payment_num = rec_sel_invs.payment_num
) OR
( rec_sel_invs.original_invoice_id IS NOT NULL -- interest schedule
AND ASI.invoice_id = rec_sel_invs.original_invoice_id
AND ASI.payment_num = rec_sel_invs.original_payment_num
)
)
AND ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(ASI.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(ASI.invoice_id)
AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(ASI.payment_num)
AND ibydocs.rejected_docs_group_id = p_rejected_docs_group_id
AND ibydocs.calling_app_id = 200 ;
SELECT count(*) into l_count
FROM ap_selected_invoices_all ASI
WHERE ASI.checkrun_id = l_checkrun_id
AND ASI.invoice_id <> rec_sel_invs.invoice_id
AND ( ( rec_sel_invs.original_invoice_id IS NULL -- selected row is standard schedule
AND ASI.original_invoice_id = rec_sel_invs.invoice_id
AND ASI.original_payment_num = rec_sel_invs.payment_num
) OR
( rec_sel_invs.original_invoice_id IS NOT NULL -- selected row is interest schedule
AND ASI.invoice_id = rec_sel_invs.original_invoice_id
AND ASI.payment_num = rec_sel_invs.original_payment_num
)
)
AND ASI.payment_grouping_number = rec_sel_invs.payment_grouping_number
AND ASI.ok_to_pay_flag = 'Y'
AND (Ap_Payment_Util_Pkg.is_federal_installed(ASI.org_id) = 'N'
OR (Ap_Payment_Util_Pkg.is_federal_installed(asi.org_id) = 'Y'
AND asi.exclusive_payment_flag = 'N')
) ;