[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_selected VARCHAR2(1);
x_selected := 'Y';
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
apps.invoice_id = apinv.invoice_id ) AND
( apps.due_date = jlbl.due_date )) AND
( apinv.invoice_type_lookup_code = 'STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.vendor_site_id = jlbl.vendor_site_id AND
apps.invoice_id = apinv.invoice_id ) AND
/* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code = 'STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.vendor_site_id = jlbl.vendor_site_id AND
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code = 'STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected := 'N';
l_log_msg := 'Exception, x_selected set to N';
IF x_selected = 'N' THEN
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
/* (( povs.vendor_site_id = jlbl.vendor_site_id AND */
((apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code = 'STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected:='Y';
l_log_msg := 'Next select executed and x_selected is set to Y';
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
apps.invoice_id = apinv.invoice_id ) AND
( apps.due_date = jlbl.due_date ) AND
( apinv.vendor_site_id = jlbl.vendor_site_id AND
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code = 'STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
apps.invoice_id = apinv.invoice_id ) AND
( apps.due_date = jlbl.due_date ) AND
/* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code = 'STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
apps.invoice_id = apinv.invoice_id ) AND
( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
apinv.invoice_id = apps.invoice_id AND
apinv.payment_currency_code = jlbl.currency_code ) AND
( apps.due_date = jlbl.due_date ) ) AND
( apinv.invoice_type_lookup_code = 'STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
apps.invoice_id = apinv.invoice_id ) AND
( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
apinv.invoice_id = apps.invoice_id AND
apinv.payment_currency_code = jlbl.currency_code ) AND
( apps.due_date = jlbl.due_date ) AND
( apinv.vendor_site_id = jlbl.vendor_site_id AND
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code = 'STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
apps.invoice_id = apinv.invoice_id ) AND
( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
apinv.invoice_id = apps.invoice_id AND
apinv.payment_currency_code = jlbl.currency_code ) AND
( apps.due_date = jlbl.due_date ) AND
/* ( povs.vendor_site_id = jlbl.vendor_site_id AND*/
( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code ='STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
apps.invoice_id = apinv.invoice_id ) AND
( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
apinv.invoice_id = apps.invoice_id AND
apinv.payment_currency_code = jlbl.currency_code ) AND
( apps.due_date = jlbl.due_date ) AND
( apinv.vendor_site_id = jlbl.vendor_site_id AND
apps.invoice_id = apinv.invoice_id ) AND
/* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND */
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code ='STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
apps.invoice_id = apinv.invoice_id ) AND
( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
apinv.invoice_id = apps.invoice_id AND
apinv.payment_currency_code = jlbl.currency_code ) AND
( apps.due_date = jlbl.due_date ) ) AND
( apinv.invoice_type_lookup_code ='STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected := 'N';
l_log_msg := 'Exception, x_selected set to N';
IF x_selected = 'N' THEN
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.vendor_site_id = jlbl.vendor_site_id AND
apps.invoice_id = apinv.invoice_id ) AND
/* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND */
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code ='STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected:= 'Y';
l_log_msg := 'Next select executed and x_selected is set to Y';
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code ='STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected := 'N';
l_log_msg := 'Exception, x_selected set to N';
IF x_selected = 'N' THEN
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
apinv.invoice_id = apps.invoice_id AND
apinv.payment_currency_code = jlbl.currency_code ) AND
( apps.due_date = jlbl.due_date ) AND
( apinv.vendor_site_id = jlbl.vendor_site_id AND
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code ='STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected:= 'Y';
l_log_msg := 'Next select executed and x_selected is set to Y';
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
apinv.invoice_id = apps.invoice_id AND
apinv.payment_currency_code = jlbl.currency_code ) AND
( apps.due_date = jlbl.due_date ) AND
( apinv.vendor_site_id = jlbl.vendor_site_id AND
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code ='STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT apps.invoice_id, apps.payment_num
INTO invoice_id_s, payment_num_s
FROM ap_payment_schedules_ALL apps,
jl_br_ap_collection_docs jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE jlbl.bank_collection_id = bank_collection_id_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
apinv.invoice_id = apps.invoice_id AND
apinv.payment_currency_code = jlbl.currency_code ) AND
( apps.due_date = jlbl.due_date ) AND
/* ( povs.vendor_site_id = jlbl.vendor_site_id AND*/
( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
apps.invoice_id = apinv.invoice_id ) ) AND
( apinv.invoice_type_lookup_code ='STANDARD') AND
( apps.global_attribute11 IS NULL ) AND
( apinv.cancelled_date IS NULL ) AND
(nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected := 'N';
l_log_msg := 'Exception NO_DATA_FOUND, x_selected set to N';
x_selected := 'N';
l_log_msg := 'Exception TOO_MANY_ROWS, x_selected set to N';
IF x_selected = 'Y' THEN /* Create the link with two tables */
UPDATE jl_br_ap_collection_docs
SET invoice_id = invoice_id_s,
payment_num = payment_num_s
WHERE bank_collection_id = bank_collection_id_e;
l_log_msg := 'Updated jl_br_ap_collection_docs invoice_id ' ||
invoice_id_s || ' payment_num' || payment_num_s;
UPDATE ap_payment_schedules
SET global_attribute11 = bank_collection_id_e,
global_attribute8 = 'Y'
WHERE invoice_id = invoice_id_s
AND payment_num = payment_num_s;
l_log_msg := 'Updated ap_payment_schedules global_attribute11 ' || bank_collection_id_e;
SELECT global_attribute1,
global_attribute2
INTO x_enable_bank_coll,
x_enable_association
FROM ap_system_parameters;
UPDATE ap_payment_schedules
SET hold_flag = 'N'
WHERE invoice_id = invoice_id_s
AND payment_num = payment_num_s;
l_log_msg := 'updated hold flag to N in ap_payment_schedules';
UPDATE jl_br_ap_collection_docs
SET hold_flag = 'N'
WHERE bank_collection_id = bank_collection_id_e;
l_log_msg := 'updated hold flag to N in jl_br_ap_collection_docs';
UPDATE jl_br_ap_collection_docs
SET hold_flag = 'Y'
WHERE bank_collection_id = bank_collection_id_e;
l_log_msg := 'associate_flag_s is N updated hold_flag to Y in jl_br_ap_collection_docs';
UPDATE jl_br_ap_collection_docs
SET hold_flag = 'Y'
WHERE bank_collection_id = bank_collection_id_e;
l_log_msg := 'x_enable_association is N update hold flag to Y in jl_br_ap_collection_docs';
UPDATE jl_br_ap_collection_docs
SET hold_flag = 'N'
WHERE bank_collection_id = bank_collection_id_e;
l_log_msg := 'x_enable_bank_coll is N update hold flag to N in jl_br_ap_collection_docs';
x_selected VARCHAR2(1);
x_selected := 'Y';
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.document_number = substr( apinv.invoice_num,1,15 )) AND
( jlbl.due_date = apps.due_date ) ) AND
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.vendor_site_id = apinv.vendor_site_id ) AND
( apinv.invoice_id = apps.invoice_id AND
/* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
/* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.vendor_site_id = apinv.vendor_site_id )) AND
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected := 'N';
l_log_msg := 'Exception, x_selected set to N';
IF x_selected = 'N' THEN
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
/* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
/* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected:= 'Y';
l_log_msg := 'Next select executed and x_selected is set to Y';
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
( jlbl.due_date = apps.due_date ) AND
( apinv.invoice_id = apps.invoice_id AND
jlbl.vendor_site_id = apinv.vendor_site_id ) ) AND
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
( jlbl.due_date = apps.due_date ) AND
( apinv.invoice_id = apps.invoice_id AND
/* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
/* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
apinv.invoice_id = apps.invoice_id AND
jlbl.currency_code = apinv.payment_currency_code ) AND
( jlbl.due_date = apps.due_date ) ) AND
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
apinv.invoice_id = apps.invoice_id AND
jlbl.currency_code = apinv.payment_currency_code ) AND
( jlbl.due_date = apps.due_date ) AND
( apinv.invoice_id = apps.invoice_id AND
jlbl.vendor_site_id = apinv.vendor_site_id ) ) AND
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
(jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
apinv.invoice_id = apps.invoice_id AND
jlbl.currency_code = apinv.payment_currency_code ) AND
( jlbl.due_date = apps.due_date ) AND
( apinv.invoice_id = apps.invoice_id AND
/* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
/* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
apinv.invoice_id = apps.invoice_id AND
jlbl.currency_code = apinv.payment_currency_code ) AND
( jlbl.due_date = apps.due_date ) AND
( apinv.invoice_id = apps.invoice_id AND
jlbl.vendor_site_id = apinv.vendor_site_id ) AND
( apinv.invoice_id = apps.invoice_id AND
/* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
/* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
apinv.invoice_id = apps.invoice_id AND
jlbl.currency_code = apinv.payment_currency_code ) AND
( jlbl.due_date = apps.due_date ) ) AND
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected := 'N';
l_log_msg := 'Exception, x_selected set to N';
IF x_selected = 'N' THEN
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.vendor_site_id = apinv.vendor_site_id ) AND
( apinv.invoice_id = apps.invoice_id AND
/* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
/* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected:= 'Y';
l_log_msg := 'Next select executed and x_selected is set to Y';
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( apinv.invoice_id = apps.invoice_id AND
jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) ) AND
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected := 'N';
l_log_msg := 'Exception, x_selected set to N';
IF x_selected = 'N' THEN
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
apinv.invoice_id = apps.invoice_id AND
jlbl.currency_code = apinv.payment_currency_code ) AND
( jlbl.due_date = apps.due_date ) AND
( apinv.invoice_id = apps.invoice_id AND
jlbl.vendor_site_id = apinv.vendor_site_id ) ) AND
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected:= 'Y';
l_log_msg := 'Next select executed and x_selected is set to Y';
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
apinv.invoice_id = apps.invoice_id AND
jlbl.currency_code = apinv.payment_currency_code ) AND
( jlbl.due_date = apps.due_date ) AND
( apinv.invoice_id = apps.invoice_id AND
jlbl.vendor_site_id = apinv.vendor_site_id ) ) AND
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
SELECT bank_collection_id
INTO bank_collection_id_s
FROM ap_payment_schedules apps,
jl_br_ap_collection_docs_ALL jlbl,
ap_invoices_ALL apinv
/* Bug # 635847 / 659227
,
po_vendor_sites povs
*/
WHERE apps.invoice_id = invoice_id_e AND
apps.payment_num = payment_num_e AND
apps.org_id = jlbl.org_id AND --bug 14615620
apps.org_id = apinv.org_id AND --bug 14615620
(( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
apinv.invoice_id = apps.invoice_id AND
jlbl.currency_code = apinv.payment_currency_code ) AND
( jlbl.due_date = apps.due_date ) AND
( apinv.invoice_id = apps.invoice_id AND
/* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
/* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
( jlbl.invoice_id IS NULL AND
jlbl.payment_num IS NULL ) AND
( jlbl.status_lookup_code = 'ACTIVE')
AND apinv.invoice_currency_code = jlbl.currency_code;
x_selected := 'N';
l_log_msg := 'Exception, x_selected set to N';
IF x_selected = 'Y' THEN /* Create the link with two tables */
-- Bug Number 659227 / R11 Patch / May 98 (Copying Y to GA8)
UPDATE ap_payment_schedules
SET global_attribute11 = bank_collection_id_s,
global_attribute8 = 'Y'
WHERE invoice_id = invoice_id_e AND
payment_num = payment_num_e;
l_log_msg := 'Updated ap_payment_schedules global_attribute11 ' || bank_collection_id_s;
UPDATE jl_br_ap_collection_docs
SET invoice_id = invoice_id_e,
payment_num = payment_num_e
WHERE bank_collection_id = bank_collection_id_s;
l_log_msg := 'Updated jl_br_ap~_collection_docs invoice_id ' || invoice_id_e;
l_log_msg := 'associate_flag_s set to Y updated values';
SELECT global_attribute1
INTO x_enable_bank_coll
FROM ap_system_parameters;
SELECT global_attribute1
INTO x_enable_association
FROM ap_invoices
WHERE invoice_id = invoice_id_e;
UPDATE ap_payment_schedules
SET hold_flag = 'N'
WHERE invoice_id = invoice_id_e
AND payment_num = payment_num_e;
UPDATE jl_br_ap_collection_docs
SET hold_flag = 'N'
WHERE bank_collection_id = bank_collection_id_s;
l_log_msg := 'Updated hold to N in ar_payment_schedules, jl_br_ap_collection_docs';
SELECT payment_status_flag
INTO x_payment_status_flag
FROM ap_payment_schedules
WHERE invoice_id = invoice_id_e
AND payment_num = payment_num_e;
UPDATE ap_payment_schedules
SET hold_flag = 'Y'
WHERE invoice_id = invoice_id_e
AND payment_num = payment_num_e;
l_log_msg := 'x_payment_status_flag N, Updated hold_flag_s Y in ap_payment_schedules';
UPDATE ap_payment_schedules
SET hold_flag = 'Y'
WHERE invoice_id = invoice_id_e
AND payment_num = payment_num_e;
l_log_msg := 'x_enable_association is N updated ap_payment_schedules hold flag to Y';
UPDATE ap_payment_schedules
SET hold_flag = 'N'
WHERE invoice_id = invoice_id_e
AND payment_num = payment_num_e;
l_log_msg := 'x_enable_bank_coll is N, updated ap_payment_schedules to N';