[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 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,
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,
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)
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)
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
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
) IS
cursor unselected_invoices is
SELECT /*+NO_EXPAND */ inv.invoice_id,
ps.payment_num,
ps.hold_flag,
sites.hold_all_payments_flag,
ap_utilities_pkg.get_invoice_status(inv.invoice_id, null),
inv.wfapproval_status,
inv.org_id,
ps.due_date,
ps.discount_amount_available,
ps.discount_date
FROM ap_supplier_sites_all sites,
ap_invoices inv, --Bug6040657. Changed from ap_invoices_all to ap_invoices
ap_payment_schedules ps,
ap_suppliers suppliers,
hz_parties hz
where inv.invoice_id = ps.invoice_id
AND sites.vendor_site_id(+) = inv.vendor_site_id
AND suppliers.vendor_id(+) = inv.vendor_id
AND inv.party_id = hz.party_id
AND ps.payment_status_flag BETWEEN 'N' AND 'P'
AND inv.payment_status_flag BETWEEN 'N' AND 'P'
AND NVL(ps.payment_priority, 99) BETWEEN p_hi_payment_priority
AND p_low_payment_priority
AND inv.cancelled_date is null
-- Bug 5649608
--AND nvl(inv.batch_id,-99) = nvl(p_invoice_batch_id,-99)
AND (p_invoice_batch_id IS NULL
OR(p_invoice_batch_id IS NOT NULL AND
inv.batch_id = p_invoice_batch_id))
AND inv.vendor_id = nvl(p_inv_vendor_id,inv.vendor_id)
AND inv.party_id = nvl(p_party_id, inv.party_id)
-- Bug 5507013 hkaniven start --
AND (( p_inv_exchange_rate_type = 'IS_USER' AND NVL(inv.exchange_rate_type,'NOT USER') = 'User' )
OR (p_inv_exchange_rate_type = 'IS_NOT_USER' AND NVL(inv.exchange_rate_type,'NOT USER') <> 'User')
OR (p_inv_exchange_rate_type IS NULL))
-- Bug 5507013 hkaniven end --
AND ps.payment_method_code = nvl(p_payment_method, ps.payment_method_code)
AND nvl(suppliers.vendor_type_lookup_code,-99) =
nvl(p_supplier_type, nvl(suppliers.vendor_type_lookup_code,-99))
AND (inv.legal_entity_id in (select legal_entity_id
from ap_le_group
where checkrun_id = p_checkrun_id)
or p_le_group_option = 'ALL')
AND (inv.org_id in (select org_id
from AP_OU_GROUP
where checkrun_id = p_checkrun_id)
or p_ou_group_option = 'ALL')
AND (inv.payment_currency_code in (select currency_code
from AP_CURRENCY_GROUP
where checkrun_id = p_checkrun_id)
or p_currency_group_option = 'ALL')
AND (inv.pay_group_lookup_code in (select vendor_pay_group
from AP_PAY_GROUP
where checkrun_id = p_checkrun_id)
or p_pay_group_option = 'ALL')
AND ((p_zero_invoices_allowed = 'N' AND ps.amount_remaining <> 0) OR
p_zero_invoices_allowed = 'Y')
and ps.checkrun_id is null;
l_current_calling_sequence := p_current_calling_sequence||'<- insert unselected';
l_debug_info := 'open unselected_invoices';
open unselected_invoices;
fetch unselected_invoices into l_invoice_id,
l_payment_num,
l_ps_hold_flag,
l_hold_all_payments_flag,
l_invoice_status,
l_approval_status,
l_org_id,
l_due_date,
l_discount_amount_available,
l_discount_date;
exit when unselected_invoices%notfound;
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';
close unselected_invoices;
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 asi.exclusive_payment_flag,
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,
fv.beneficiary_party_id,
SIGN(asi.invoice_amount) DESC, --this will make credit memos last per group
asi.due_date, -- Bug 5479979
/* DECODE(SIGN(asi.invoice_amount),
-1, TO_CHAR(asi.due_date,'YYYYMMDD'),
asi.invoice_num), */
asi.payment_num;
select nvl(zero_amounts_allowed,'N')
into l_maximize_credits_flag
from ap_inv_selection_criteria_all
where checkrun_id = p_checkrun_id;
l_debug_info := 'update grouping numbers, ok to pay flags, and amounts';
update 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;
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';
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 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; */
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, --deepak is unsure of this one in his dld
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
)
select nvl(p_payment_date,sysdate), --4681989
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,
ZERO_INV_ALLOWED_FLAG,
vendor_id,
p_checkrun_id,
nvl(p_pay_from_date, sysdate - ADDL_PAY_FROM_DAYS), --4681989
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
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;
,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);
SELECT /*+ NO_EXPAND */
p_checkrun_name checkrun_name
,p_checkrun_id checkrun_id
,ps.invoice_id invoice_id
,payment_num payment_num
,SYSDATE last_update_date
-- Bug 7383484 (Base 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 7383484 (Base bug 7296715)
-- ,5 created_by
,FND_GLOBAL.USER_ID created_by
,NULL last_update_login
,inv.vendor_id vendor_id
,inv.vendor_site_id vendor_site_id
,suppliers.segment1 vendor_num
/* Bug 5620285, Added the following decode */
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzp.party_name, suppliers.vendor_name) vendor_name
,sites.vendor_site_code vendor_site_code
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.address1, sites.address_line1) address_line1
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.address2, sites.address_line2) address_line2
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.address3, sites.address_line3) address_line3
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.address4, sites.address_line4) address_line4
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.city, sites.city) city
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.state, sites.state) state
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.postal_code, sites.zip) zip
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.province, sites.province) province
,decode(inv.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
,inv.invoice_num invoice_num
,inv.invoice_date invoice_date
,DECODE(inv.doc_sequence_id,
'', inv.voucher_num,
inv.doc_sequence_value) voucher_num
,inv.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(p_check_date
- NVL(ps.discount_date,
p_check_date+1)-1),
-1, ps.discount_date,
DECODE(SIGN(p_check_date
-NVL(ps.second_discount_date,
p_check_date+1)-1),
-1, ps.second_discount_date,
DECODE(SIGN(p_check_date
-NVL(ps.third_discount_date,
p_check_date+1)-1),
-1, ps.third_discount_date,
TRUNC(ps.due_date))))) discount_date
,SUBSTRB(inv.description,1,50) 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
,inv.invoice_amount invoice_amount
,inv.payment_cross_rate payment_cross_rate
,DECODE(inv.exchange_rate,
NULL, DECODE(inv.invoice_currency_code,
asp.base_currency_code, 1,
NULL),
inv.exchange_rate) invoice_exchange_rate
,inv.set_of_books_id set_of_books_id
,sites.customer_num customer_num
,ps.future_pay_due_date future_pay_due_date
,inv.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
,inv.org_id org_id
,inv.payment_currency_code payment_currency_code
,ps.external_bank_account_id external_bank_account_id
,inv.legal_entity_id legal_entity_id
/* Bug 5192018 we will insert global attribute values from ap_invoices table */
,inv.global_attribute1 global_attribute1
,inv.global_attribute2 global_attribute2
,inv.global_attribute3 global_attribute3
,inv.global_attribute4 global_attribute4
,inv.global_attribute5 global_attribute5
,inv.global_attribute6 global_attribute6
,inv.global_attribute7 global_attribute7
,inv.global_attribute8 global_attribute8
,inv.global_attribute9 global_attribute9
,inv.global_attribute10 global_attribute10
,inv.global_attribute11 global_attribute11
,inv.global_attribute12 global_attribute12
,inv.global_attribute13 global_attribute13
,inv.global_attribute14 global_attribute14
,inv.global_attribute15 global_attribute15
,inv.global_attribute16 global_attribute16
,inv.global_attribute17 global_attribute17
,inv.global_attribute18 global_attribute18
,inv.global_attribute19 global_attribute19
,inv.global_attribute20 global_attribute20
,inv.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
FROM ap_supplier_sites_all sites,
ap_suppliers suppliers,
ap_invoices inv, --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 <= p_pay_thru_date +0/24+1 and
due_date >= nvl(p_pay_from_date + 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(p_check_date
-NVL(ps.discount_date,
p_check_date+1)-1),
-1, ps.discount_date,
DECODE(SIGN(p_check_date
-NVL(ps.second_discount_date,
p_check_date+1)-1),
-1, ps.second_discount_date,
DECODE(SIGN(p_check_date
-NVL(ps.third_discount_date,
p_check_date+1)-1),
-1, ps.third_discount_date,
TRUNC(ps.due_date))))),
TRUNC(due_date))
BETWEEN DECODE(sites.always_take_disc_flag,'Y',
nvl(p_pay_from_date, TO_DATE('1901','YYYY')),
p_check_date)
AND p_disc_pay_thru_date)
AND ps.payment_status_flag BETWEEN 'N' AND 'P'
AND inv.payment_status_flag BETWEEN 'N' AND 'P'
AND nvl(inv.force_revalidation_flag, 'N') = 'N' --bug7244642
AND NVL(ps.payment_priority, 99) BETWEEN p_hi_payment_priority
AND p_lo_payment_priority
AND inv.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(inv.invoice_type_lookup_code,
'PAYMENT REQUEST', inv.party_id
, -99)
AND NVL(ps.hold_flag, 'N') = 'N'
AND NVL(sites.hold_all_payments_flag, 'N') = 'N'
AND inv.invoice_id = ps.invoice_id
AND sites.vendor_id(+) = inv.vendor_id
AND sites.vendor_site_id(+) = inv.vendor_site_id
AND suppliers.vendor_id(+) = inv.vendor_id
AND asp.org_id = inv.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(inv.invoice_type_lookup_code, 'PAYMENT REQUEST', INV.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, inv.invoice_id,
p_check_date, due_date, ps.discount_amount_available, ps.discount_date) = 'Y'
AND AP_INVOICES_PKG.get_wfapproval_status(inv.invoice_id, inv.org_id) in
('NOT REQUIRED','WFAPPROVED','MANUALLY APPROVED')
AND (p_inv_batch_id IS NULL OR
(p_inv_batch_id IS NOT NULL AND inv.batch_id = p_inv_batch_id))
AND inv.vendor_id = nvl(p_inv_vendor_id,inv.vendor_id)
AND inv.party_id = nvl(p_party_id, inv.party_id)
-- Bug 5507013 hkaniven start --
AND (( p_inv_exc_rate_type = 'IS_USER' AND NVL(inv.exchange_rate_type,'NOT USER') = 'User' )
OR (p_inv_exc_rate_type = 'IS_NOT_USER' AND NVL(inv.exchange_rate_type,'NOT USER') <> 'User')
OR (p_inv_exc_rate_type IS NULL))
-- Bug 5507013 hkaniven end --
AND ps.payment_method_code = nvl(p_payment_method, ps.payment_method_code)
AND nvl(suppliers.vendor_type_lookup_code,-99) =
nvl(p_supplier_type, nvl(suppliers.vendor_type_lookup_code,-99))
AND (inv.legal_entity_id in (select legal_entity_id
from ap_le_group
where checkrun_id = p_checkrun_id)
or p_le_group_option = 'ALL')
AND (inv.org_id in (select org_id
from AP_OU_GROUP
where checkrun_id = p_checkrun_id)
or p_ou_group_option = 'ALL')
AND (inv.payment_currency_code in (select currency_code
from AP_CURRENCY_GROUP
where checkrun_id = p_checkrun_id)
or p_curr_group_option = 'ALL')
AND (inv.pay_group_lookup_code in (select vendor_pay_group
from AP_PAY_GROUP
where checkrun_id = p_checkrun_id)
or p_pay_group_option = 'ALL')
AND ((p_zero_inv_allowed = 'N' AND ps.amount_remaining <> 0) OR
p_zero_inv_allowed = 'Y')
--Bug 6342390 Added the clause below.
--Commented the fix for the bug6342390, bug6365720
/* 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
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds H
WHERE H.invoice_id = inv.invoice_id
AND H.release_lookup_code is null)
AND NOT EXISTS (SELECT 'Invoice is not fully approved'
FROM ap_invoices_derived_v AIDV
WHERE AIDV.invoice_id = inv.invoice_id
AND AIDV.approval_status_lookup_code IN
('NEVER APPROVED', 'NEEDS REAPPROVAL', 'UNAPPROVED'))
AND EXISTS (SELECT 'Distributions exist'
FROM ap_invoice_distributions D4
WHERE D4.invoice_id = inv.invoice_id)
-- bug 6456537
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');
SELECT /*+ NO_EXPAND */
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 7383484 (Base 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 7383484 (Base bug 7296715)
-- ,5 created_by
,FND_GLOBAL.USER_ID created_by
,NULL last_update_login
,inv.vendor_id vendor_id
,inv.vendor_site_id vendor_site_id
,suppliers.segment1 vendor_num
/* Bug 5620285, Added the following decode */
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzp.party_name, suppliers.vendor_name) vendor_name
,sites.vendor_site_code vendor_site_code
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.address1, sites.address_line1) address_line1
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.address2, sites.address_line2) address_line2
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.address3, sites.address_line3) address_line3
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.address4, sites.address_line4) address_line4
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.city, sites.city) city
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.state, sites.state) state
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.postal_code, sites.zip) zip
,decode(inv.invoice_type_lookup_code, 'PAYMENT REQUEST',
hzl.province, sites.province) province
,decode(inv.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
,inv.invoice_num invoice_num
,inv.invoice_date invoice_date
,DECODE(inv.doc_sequence_id,
'', inv.voucher_num,
inv.doc_sequence_value) voucher_num
,inv.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(p_check_date
- NVL(ps.discount_date,
p_check_date+1)-1),
-1, ps.discount_date,
DECODE(SIGN(p_check_date
-NVL(ps.second_discount_date,
p_check_date+1)-1),
-1, ps.second_discount_date,
DECODE(SIGN(p_check_date
-NVL(ps.third_discount_date,
p_check_date+1)-1),
-1, ps.third_discount_date,
TRUNC(ps.due_date))))) discount_date
,SUBSTRB(inv.description,1,50) 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
,inv.invoice_amount invoice_amount
,inv.payment_cross_rate payment_cross_rate
,DECODE(inv.exchange_rate,
NULL, DECODE(inv.invoice_currency_code,
asp.base_currency_code, 1,
NULL),
inv.exchange_rate) invoice_exchange_rate
,inv.set_of_books_id set_of_books_id
,sites.customer_num customer_num
,ps.future_pay_due_date future_pay_due_date
,inv.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
,inv.org_id org_id
,inv.payment_currency_code payment_currency_code
,ps.external_bank_account_id external_bank_account_id
,inv.legal_entity_id legal_entity_id
/* Bug 5192018 we will insert global attribute values from ap_invoices table */
,inv.global_attribute1 global_attribute1
,inv.global_attribute2 global_attribute2
,inv.global_attribute3 global_attribute3
,inv.global_attribute4 global_attribute4
,inv.global_attribute5 global_attribute5
,inv.global_attribute6 global_attribute6
,inv.global_attribute7 global_attribute7
,inv.global_attribute8 global_attribute8
,inv.global_attribute9 global_attribute9
,inv.global_attribute10 global_attribute10
,inv.global_attribute11 global_attribute11
,inv.global_attribute12 global_attribute12
,inv.global_attribute13 global_attribute13
,inv.global_attribute14 global_attribute14
,inv.global_attribute15 global_attribute15
,inv.global_attribute16 global_attribute16
,inv.global_attribute17 global_attribute17
,inv.global_attribute18 global_attribute18
,inv.global_attribute19 global_attribute19
,inv.global_attribute20 global_attribute20
,inv.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
FROM ap_supplier_sites_all sites,
ap_suppliers suppliers,
ap_invoices inv, --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 <= p_pay_thru_date +0/24+1 and
due_date >= nvl(p_pay_from_date + 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(p_check_date
-NVL(ps.discount_date,
p_check_date+1)-1),
-1, ps.discount_date,
DECODE(SIGN(p_check_date
-NVL(ps.second_discount_date,
p_check_date+1)-1),
-1, ps.second_discount_date,
DECODE(SIGN(p_check_date
-NVL(ps.third_discount_date,
p_check_date+1)-1),
-1, ps.third_discount_date,
TRUNC(ps.due_date))))),
TRUNC(due_date))
BETWEEN DECODE(sites.always_take_disc_flag,'Y',
nvl(p_pay_from_date, TO_DATE('1901','YYYY')),
p_check_date)
AND p_disc_pay_thru_date)
AND ps.payment_status_flag BETWEEN 'N' AND 'P'
AND nvl(inv.force_revalidation_flag, 'N') = 'N' --bug7244642
AND inv.payment_status_flag BETWEEN 'N' AND 'P'
AND NVL(ps.payment_priority, 99) BETWEEN p_hi_payment_priority
AND p_lo_payment_priority
AND inv.cancelled_date is null
-- Bug 7167192 Added decode
-- hzp and hzps data is required only for Payment Requests.
AND hzp.party_id(+) = decode(inv.invoice_type_lookup_code,
'PAYMENT REQUEST', inv.party_id
, -99)
AND NVL(ps.hold_flag, 'N') = 'N'
AND NVL(sites.hold_all_payments_flag, 'N') = 'N'
AND inv.invoice_id = ps.invoice_id
AND sites.vendor_id(+) = inv.vendor_id
AND sites.vendor_site_id(+) = inv.vendor_site_id
AND suppliers.vendor_id(+) = inv.vendor_id
AND asp.org_id = inv.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(inv.invoice_type_lookup_code, 'PAYMENT REQUEST', INV.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, inv.invoice_id,
p_check_date, due_date, ps.discount_amount_available, ps.discount_date) = 'Y'
AND AP_INVOICES_PKG.get_wfapproval_status(inv.invoice_id, inv.org_id) in
('NOT REQUIRED','WFAPPROVED','MANUALLY APPROVED')
AND (p_inv_batch_id IS NULL OR
(p_inv_batch_id IS NOT NULL AND inv.batch_id = p_inv_batch_id))
AND inv.vendor_id = nvl(p_inv_vendor_id,inv.vendor_id)
AND inv.party_id = nvl(p_party_id, inv.party_id)
-- Bug 5507013 hkaniven start --
AND (( p_inv_exc_rate_type = 'IS_USER' AND NVL(inv.exchange_rate_type,'NOT USER') = 'User' )
OR (p_inv_exc_rate_type = 'IS_NOT_USER' AND NVL(inv.exchange_rate_type,'NOT USER') <> 'User')
OR (p_inv_exc_rate_type IS NULL))
-- Bug 5507013 hkaniven end --
AND ps.payment_method_code = nvl(p_payment_method, ps.payment_method_code)
AND nvl(suppliers.vendor_type_lookup_code,-99) =
nvl(p_supplier_type, nvl(suppliers.vendor_type_lookup_code,-99))
AND (inv.legal_entity_id in (select legal_entity_id
from ap_le_group
where checkrun_id = p_checkrun_id)
or p_le_group_option = 'ALL')
AND (inv.org_id in (select org_id
from AP_OU_GROUP
where checkrun_id = p_checkrun_id)
or p_ou_group_option = 'ALL')
AND (inv.payment_currency_code in (select currency_code
from AP_CURRENCY_GROUP
where checkrun_id = p_checkrun_id)
or p_curr_group_option = 'ALL')
AND (inv.pay_group_lookup_code in (select vendor_pay_group
from AP_PAY_GROUP
where checkrun_id = p_checkrun_id)
or p_pay_group_option = 'ALL')
AND ((p_zero_inv_allowed = 'N' AND ps.amount_remaining <> 0) OR
p_zero_inv_allowed = 'Y')
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all H
WHERE H.invoice_id = inv.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 = inv.invoice_id
AND NVL(D2.match_status_flag, 'N') in ('N', 'S'))
AND EXISTS (SELECT 'Distributions exist'
FROM ap_invoice_distributions_all D4
WHERE D4.invoice_id = inv.invoice_id)
-- bug 6456537
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');
l_current_calling_sequence := 'select invoices';
l_debug_info := 'Select data from ap_invoice_selection_criteria';
SELECT
trunc(check_date),
trunc(pay_thru_date),
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(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),
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')),
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_pay_thru_date,
l_hi_payment_priority,
l_low_payment_priority,
l_disc_pay_thru_date,
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_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',
-- 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;
,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
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)
--bug 6788730 Changed this to SELECT
--VALUES
(
SELECT
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)
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
);
,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
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)
--bug 6788730 Changed this to SELECT
--
-- VALUES
(
SELECT
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)
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 IN ('O', 'F')
AND GLPS.APPLICATION_ID = 200
AND GLPS.SET_OF_BOOKS_ID = ASI2.SET_OF_BOOKS_ID));
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 */ 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'
AND trunc(l_check_date) < trunc(sysdate));
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_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,
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,
PS.GROSS_AMOUNT))))),
decode(ai.invoice_type_lookup_code,'PAYMENT REQUEST',
ps.amount_remaining,
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,
PS.GROSS_AMOUNT))))),
decode(ai.invoice_type_lookup_code,'PAYMENT REQUEST',
0,
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,
PS.GROSS_AMOUNT))))
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;
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);
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 (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 = 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);
l_debug_info := 'Grouping selected invoices';
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 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';
END SELECT_INVOICES;
l_checkrun_name ap_inv_selection_criteria_all.checkrun_name%type;-- bug# 6643035
SELECT invoice_id
, vendor_id
, payment_num
FROM ap_SELECTed_invoices_all ASI,
ap_system_parameters_all asp
WHERE checkrun_name = l_checkrun_name
AND original_invoice_id IS NULL
AND asp.org_id = asi.org_id
and checkrun_id = 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';
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')),
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';
,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;
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 decode(nvl(inv_awt_exists_flag, 'N'), 'Y', 'REQUEST', document_rejection_level_code) document_rejection_level_code,
decode(nvl(inv_awt_exists_flag, 'N'), 'Y', 'REQUEST', payment_rejection_level_code) payment_rejection_level_code
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
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;
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;