[Home] [Help]
8:
9: G_ERROR varchar2(1) := 'N';
10: TOTAL_WORKERS NUMBER := 0;
11: WORKER_NUMBER NUMBER := 0;
12: pg_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
13: pg_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
14: pg_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
15:
16: /*========================================================================+
9: G_ERROR varchar2(1) := 'N';
10: TOTAL_WORKERS NUMBER := 0;
11: WORKER_NUMBER NUMBER := 0;
12: pg_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
13: pg_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
14: pg_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
15:
16: /*========================================================================+
17: | PUBLIC PROCEDURE GET_PARAMETERS |
10: TOTAL_WORKERS NUMBER := 0;
11: WORKER_NUMBER NUMBER := 0;
12: pg_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
13: pg_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
14: pg_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
15:
16: /*========================================================================+
17: | PUBLIC PROCEDURE GET_PARAMETERS |
18: | |
86: p_worker_number IN NUMBER DEFAULT 0,
87: p_total_workers IN NUMBER DEFAULT 0
88: ) IS
89:
90: l_request_id ar_cash_receipts.request_id%TYPE;
91: l_batch_date ar_cash_receipts.receipt_date%TYPE ;
92: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
93: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
94: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
87: p_total_workers IN NUMBER DEFAULT 0
88: ) IS
89:
90: l_request_id ar_cash_receipts.request_id%TYPE;
91: l_batch_date ar_cash_receipts.receipt_date%TYPE ;
92: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
93: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
94: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
95: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
89:
90: l_request_id ar_cash_receipts.request_id%TYPE;
91: l_batch_date ar_cash_receipts.receipt_date%TYPE ;
92: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
93: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
94: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
95: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
96: l_currency_code ar_cash_receipts.currency_code%TYPE;
97: l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
90: l_request_id ar_cash_receipts.request_id%TYPE;
91: l_batch_date ar_cash_receipts.receipt_date%TYPE ;
92: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
93: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
94: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
95: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
96: l_currency_code ar_cash_receipts.currency_code%TYPE;
97: l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
98: l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
91: l_batch_date ar_cash_receipts.receipt_date%TYPE ;
92: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
93: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
94: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
95: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
96: l_currency_code ar_cash_receipts.currency_code%TYPE;
97: l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
98: l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
99: l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE ;
92: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
93: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
94: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
95: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
96: l_currency_code ar_cash_receipts.currency_code%TYPE;
97: l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
98: l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
99: l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE ;
100: l_remittance_bank_branch_id ap_bank_accounts.bank_branch_id%TYPE DEFAULT NULL;
95: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
96: l_currency_code ar_cash_receipts.currency_code%TYPE;
97: l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
98: l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
99: l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE ;
100: l_remittance_bank_branch_id ap_bank_accounts.bank_branch_id%TYPE DEFAULT NULL;
101: l_remittance_bank_account_id ar_receipt_method_accounts.REMIT_BANK_ACCT_USE_ID%TYPE DEFAULT NULL;
102: o_batch_id NUMBER;
103:
270: ELSE
271: BEGIN
272: select count(*)
273: INTO l_count
274: from AR_CASH_RECEIPTS
275: where selected_remittance_batch_id = p_batch_id;
276:
277: EXCEPTION
278: when no_data_found THEN
697: control_check ( p_batch_id =>o_batch_id);
698:
699: fnd_file.put_line(FND_FILE.LOG,'reset selected_remittance_batch_id for all receipts after approval');
700:
701: update ar_cash_receipts
702: set selected_remittance_batch_id = null
703: where selected_remittance_batch_id = o_batch_id
704: and cash_receipt_id in (select cash_receipt_id from
705: ar_cash_receipt_history
738: nvl(sum(cr.amount),0),
739: count(*) into
740: l_control_amount,
741: l_control_count
742: FROM ar_cash_receipts cr
743: WHERE cr.selected_remittance_batch_id = o_batch_id;
744:
745: update ar_batches
746: SET batch_applied_status = l_batch_applied_status,
930:
931: l_worker_number NUMBER ;
932: l_complete BOOLEAN := FALSE;
933: l_count NUMBER;
934: l_request_id ar_cash_receipts.request_id%TYPE;
935: l_batch_date ar_cash_receipts.receipt_date%TYPE ;
936: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
937: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
938: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
931: l_worker_number NUMBER ;
932: l_complete BOOLEAN := FALSE;
933: l_count NUMBER;
934: l_request_id ar_cash_receipts.request_id%TYPE;
935: l_batch_date ar_cash_receipts.receipt_date%TYPE ;
936: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
937: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
938: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
939: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
933: l_count NUMBER;
934: l_request_id ar_cash_receipts.request_id%TYPE;
935: l_batch_date ar_cash_receipts.receipt_date%TYPE ;
936: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
937: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
938: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
939: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
940: l_currency_code ar_cash_receipts.currency_code%TYPE;
941: l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
934: l_request_id ar_cash_receipts.request_id%TYPE;
935: l_batch_date ar_cash_receipts.receipt_date%TYPE ;
936: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
937: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
938: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
939: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
940: l_currency_code ar_cash_receipts.currency_code%TYPE;
941: l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
942: l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
935: l_batch_date ar_cash_receipts.receipt_date%TYPE ;
936: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
937: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
938: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
939: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
940: l_currency_code ar_cash_receipts.currency_code%TYPE;
941: l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
942: l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
943: l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE ;
936: l_gl_date ar_cash_receipt_history.gl_date%TYPE;
937: l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
938: l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
939: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
940: l_currency_code ar_cash_receipts.currency_code%TYPE;
941: l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
942: l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
943: l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE ;
944: l_remittance_bank_branch_id ap_bank_accounts.bank_branch_id%TYPE DEFAULT NULL;
939: l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
940: l_currency_code ar_cash_receipts.currency_code%TYPE;
941: l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
942: l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
943: l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE ;
944: l_remittance_bank_branch_id ap_bank_accounts.bank_branch_id%TYPE DEFAULT NULL;
945: l_remittance_bank_account_id ar_receipt_method_accounts.REMIT_BANK_ACCT_USE_ID%TYPE DEFAULT NULL;
946: l_batch_applied_status ar_batches.batch_applied_status%TYPE;
947: l_return_status VARCHAR2(1);
1175: BEGIN
1176: l_count := 0;
1177: select count(*)
1178: INTO l_count
1179: from AR_CASH_RECEIPTS
1180: where selected_remittance_batch_id = o_batch_id;
1181: EXCEPTION
1182: when no_data_found THEN
1183: l_count :=0;
1263: --MOD(cr.cash_receipt_id, p_total_workers) + 1
1264: --MOD(CEIL(cr.cash_receipt_id/10000), p_total_workers) + 1
1265: MOD(CEIL((DENSE_RANK() over(order by crh.cash_receipt_id))/5000), :b_total_workers) + 1
1266: FROM ar_rem_cr_id_gtt gtt,
1267: ar_cash_receipts cr,
1268: ar_receipt_methods rm,
1269: ar_receipt_classes rc,
1270: hz_cust_accounts hca,
1271: hz_parties party,
1356: nvl(sum(cr.amount),0),
1357: count(*) into
1358: l_control_amount,
1359: l_control_count
1360: FROM ar_cash_receipts cr
1361: WHERE cr.selected_remittance_batch_id = o_batch_id;
1362:
1363: UPDATE ar_batches
1364: SET batch_applied_status = l_batch_applied_status,
1435: | 16-JUL-2005 bichatte Created |
1436: *=========================================================================*/
1437:
1438: PROCEDURE insert_batch(
1439: p_batch_date IN ar_cash_receipts.receipt_date%TYPE DEFAULT NULL,
1440: p_batch_gl_date IN ar_cash_receipt_history.gl_date%TYPE DEFAULT NULL,
1441: p_approve_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
1442: p_format_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
1443: p_currency_code IN ar_batches.currency_code%TYPE,
1437:
1438: PROCEDURE insert_batch(
1439: p_batch_date IN ar_cash_receipts.receipt_date%TYPE DEFAULT NULL,
1440: p_batch_gl_date IN ar_cash_receipt_history.gl_date%TYPE DEFAULT NULL,
1441: p_approve_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
1442: p_format_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
1443: p_currency_code IN ar_batches.currency_code%TYPE,
1444: p_remmitance_method IN ar_batches.remit_method_code%TYPE,
1445: p_receipt_class_id IN ar_receipt_classes.receipt_class_id%TYPE,
1438: PROCEDURE insert_batch(
1439: p_batch_date IN ar_cash_receipts.receipt_date%TYPE DEFAULT NULL,
1440: p_batch_gl_date IN ar_cash_receipt_history.gl_date%TYPE DEFAULT NULL,
1441: p_approve_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
1442: p_format_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
1443: p_currency_code IN ar_batches.currency_code%TYPE,
1444: p_remmitance_method IN ar_batches.remit_method_code%TYPE,
1445: p_receipt_class_id IN ar_receipt_classes.receipt_class_id%TYPE,
1446: p_payment_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
1745: l_sel_stmt long;
1746: sel_receipts INTEGER;
1747: /* declare the cursor variables */
1748:
1749: p_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
1750: p_payment_type_code ar_receipt_methods.payment_type_code%TYPE;
1751: p_merchant_ref ar_receipt_methods.merchant_ref%TYPE;
1752: p_currency_code ar_cash_receipts.currency_code%TYPE;
1753: p_pay_from_customer ar_cash_receipts.pay_from_customer%TYPE;
1748:
1749: p_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
1750: p_payment_type_code ar_receipt_methods.payment_type_code%TYPE;
1751: p_merchant_ref ar_receipt_methods.merchant_ref%TYPE;
1752: p_currency_code ar_cash_receipts.currency_code%TYPE;
1753: p_pay_from_customer ar_cash_receipts.pay_from_customer%TYPE;
1754: p_customer_site_use_id ar_cash_receipts.customer_site_use_id%TYPE;
1755: p_prv_cash_receipt_hist_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1756: p_exchange_date ar_cash_receipt_history.exchange_date%TYPE;
1749: p_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
1750: p_payment_type_code ar_receipt_methods.payment_type_code%TYPE;
1751: p_merchant_ref ar_receipt_methods.merchant_ref%TYPE;
1752: p_currency_code ar_cash_receipts.currency_code%TYPE;
1753: p_pay_from_customer ar_cash_receipts.pay_from_customer%TYPE;
1754: p_customer_site_use_id ar_cash_receipts.customer_site_use_id%TYPE;
1755: p_prv_cash_receipt_hist_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1756: p_exchange_date ar_cash_receipt_history.exchange_date%TYPE;
1757: p_exchange_rate ar_cash_receipt_history.exchange_rate%TYPE;
1750: p_payment_type_code ar_receipt_methods.payment_type_code%TYPE;
1751: p_merchant_ref ar_receipt_methods.merchant_ref%TYPE;
1752: p_currency_code ar_cash_receipts.currency_code%TYPE;
1753: p_pay_from_customer ar_cash_receipts.pay_from_customer%TYPE;
1754: p_customer_site_use_id ar_cash_receipts.customer_site_use_id%TYPE;
1755: p_prv_cash_receipt_hist_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1756: p_exchange_date ar_cash_receipt_history.exchange_date%TYPE;
1757: p_exchange_rate ar_cash_receipt_history.exchange_rate%TYPE;
1758: p_exchange_type ar_cash_receipt_history.exchange_rate_type%TYPE;
1755: p_prv_cash_receipt_hist_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1756: p_exchange_date ar_cash_receipt_history.exchange_date%TYPE;
1757: p_exchange_rate ar_cash_receipt_history.exchange_rate%TYPE;
1758: p_exchange_type ar_cash_receipt_history.exchange_rate_type%TYPE;
1759: p_cr_amount ar_cash_receipts.amount%TYPE;
1760: p_cr_acctd_amount ar_cash_receipt_history.acctd_amount%TYPE;
1761: p_cr_factor_discount_amount ar_cash_receipts.factor_discount_amount%TYPE;
1762: p_remmitance_ccid ar_receipt_method_accounts.remittance_ccid%TYPE;
1763: p_bank_charges_ccid ar_receipt_method_accounts.bank_charges_ccid%TYPE;
1757: p_exchange_rate ar_cash_receipt_history.exchange_rate%TYPE;
1758: p_exchange_type ar_cash_receipt_history.exchange_rate_type%TYPE;
1759: p_cr_amount ar_cash_receipts.amount%TYPE;
1760: p_cr_acctd_amount ar_cash_receipt_history.acctd_amount%TYPE;
1761: p_cr_factor_discount_amount ar_cash_receipts.factor_discount_amount%TYPE;
1762: p_remmitance_ccid ar_receipt_method_accounts.remittance_ccid%TYPE;
1763: p_bank_charges_ccid ar_receipt_method_accounts.bank_charges_ccid%TYPE;
1764: p_code_combination_id ar_distributions.code_combination_id%TYPE;
1765: p_cash_receipt_history_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1764: p_code_combination_id ar_distributions.code_combination_id%TYPE;
1765: p_cash_receipt_history_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1766: p_crh_gl_date ar_cash_receipt_history.gl_date%TYPE;
1767: p_crh_trx_date ar_cash_receipt_history.trx_date%TYPE;
1768: p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
1769: p_approval_code ar_cash_receipts.approval_code%TYPE;
1770: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
1771: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
1772: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
1765: p_cash_receipt_history_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1766: p_crh_gl_date ar_cash_receipt_history.gl_date%TYPE;
1767: p_crh_trx_date ar_cash_receipt_history.trx_date%TYPE;
1768: p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
1769: p_approval_code ar_cash_receipts.approval_code%TYPE;
1770: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
1771: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
1772: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
1773: p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
1766: p_crh_gl_date ar_cash_receipt_history.gl_date%TYPE;
1767: p_crh_trx_date ar_cash_receipt_history.trx_date%TYPE;
1768: p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
1769: p_approval_code ar_cash_receipts.approval_code%TYPE;
1770: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
1771: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
1772: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
1773: p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
1774:
1767: p_crh_trx_date ar_cash_receipt_history.trx_date%TYPE;
1768: p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
1769: p_approval_code ar_cash_receipts.approval_code%TYPE;
1770: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
1771: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
1772: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
1773: p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
1774:
1775: /* end declare */
1768: p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
1769: p_approval_code ar_cash_receipts.approval_code%TYPE;
1770: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
1771: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
1772: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
1773: p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
1774:
1775: /* end declare */
1776:
1769: p_approval_code ar_cash_receipts.approval_code%TYPE;
1770: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
1771: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
1772: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
1773: p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
1774:
1775: /* end declare */
1776:
1777: /* declare the insert array elements */
1894: NULL,SYS_GUID(),
1895: cr.unique_reference ),
1896: cr.customer_bank_account_id,
1897: cr.payment_trxn_extension_id
1898: FROM ar_cash_receipts cr, ar_cash_receipt_history crh,
1899: ar_receipt_methods rm,
1900: ar_batches bat,
1901: ar_receipt_method_accounts rma,
1902: ar_payment_schedules ps,
2526: l_sel_stmt long;
2527: sel_receipts INTEGER;
2528: /* declare the cursor variables */
2529:
2530: p_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
2531: p_payment_type_code ar_receipt_methods.payment_type_code%TYPE;
2532: p_merchant_ref ar_receipt_methods.merchant_ref%TYPE;
2533: p_currency_code ar_cash_receipts.currency_code%TYPE;
2534: p_pay_from_customer ar_cash_receipts.pay_from_customer%TYPE;
2529:
2530: p_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
2531: p_payment_type_code ar_receipt_methods.payment_type_code%TYPE;
2532: p_merchant_ref ar_receipt_methods.merchant_ref%TYPE;
2533: p_currency_code ar_cash_receipts.currency_code%TYPE;
2534: p_pay_from_customer ar_cash_receipts.pay_from_customer%TYPE;
2535: p_customer_site_use_id ar_cash_receipts.customer_site_use_id%TYPE;
2536: p_prv_cash_receipt_hist_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
2537: p_exchange_date ar_cash_receipt_history.exchange_date%TYPE;
2530: p_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
2531: p_payment_type_code ar_receipt_methods.payment_type_code%TYPE;
2532: p_merchant_ref ar_receipt_methods.merchant_ref%TYPE;
2533: p_currency_code ar_cash_receipts.currency_code%TYPE;
2534: p_pay_from_customer ar_cash_receipts.pay_from_customer%TYPE;
2535: p_customer_site_use_id ar_cash_receipts.customer_site_use_id%TYPE;
2536: p_prv_cash_receipt_hist_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
2537: p_exchange_date ar_cash_receipt_history.exchange_date%TYPE;
2538: p_exchange_rate ar_cash_receipt_history.exchange_rate%TYPE;
2531: p_payment_type_code ar_receipt_methods.payment_type_code%TYPE;
2532: p_merchant_ref ar_receipt_methods.merchant_ref%TYPE;
2533: p_currency_code ar_cash_receipts.currency_code%TYPE;
2534: p_pay_from_customer ar_cash_receipts.pay_from_customer%TYPE;
2535: p_customer_site_use_id ar_cash_receipts.customer_site_use_id%TYPE;
2536: p_prv_cash_receipt_hist_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
2537: p_exchange_date ar_cash_receipt_history.exchange_date%TYPE;
2538: p_exchange_rate ar_cash_receipt_history.exchange_rate%TYPE;
2539: p_exchange_type ar_cash_receipt_history.exchange_rate_type%TYPE;
2536: p_prv_cash_receipt_hist_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
2537: p_exchange_date ar_cash_receipt_history.exchange_date%TYPE;
2538: p_exchange_rate ar_cash_receipt_history.exchange_rate%TYPE;
2539: p_exchange_type ar_cash_receipt_history.exchange_rate_type%TYPE;
2540: p_cr_amount ar_cash_receipts.amount%TYPE;
2541: p_cr_acctd_amount ar_cash_receipt_history.acctd_amount%TYPE;
2542: p_cr_factor_discount_amount ar_cash_receipts.factor_discount_amount%TYPE;
2543: p_remmitance_ccid ar_receipt_method_accounts.remittance_ccid%TYPE;
2544: p_bank_charges_ccid ar_receipt_method_accounts.bank_charges_ccid%TYPE;
2538: p_exchange_rate ar_cash_receipt_history.exchange_rate%TYPE;
2539: p_exchange_type ar_cash_receipt_history.exchange_rate_type%TYPE;
2540: p_cr_amount ar_cash_receipts.amount%TYPE;
2541: p_cr_acctd_amount ar_cash_receipt_history.acctd_amount%TYPE;
2542: p_cr_factor_discount_amount ar_cash_receipts.factor_discount_amount%TYPE;
2543: p_remmitance_ccid ar_receipt_method_accounts.remittance_ccid%TYPE;
2544: p_bank_charges_ccid ar_receipt_method_accounts.bank_charges_ccid%TYPE;
2545: p_code_combination_id ar_distributions.code_combination_id%TYPE;
2546: p_cash_receipt_history_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
2545: p_code_combination_id ar_distributions.code_combination_id%TYPE;
2546: p_cash_receipt_history_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
2547: p_crh_gl_date ar_cash_receipt_history.gl_date%TYPE;
2548: p_crh_trx_date ar_cash_receipt_history.trx_date%TYPE;
2549: p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
2550: p_approval_code ar_cash_receipts.approval_code%TYPE;
2551: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
2552: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
2553: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
2546: p_cash_receipt_history_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
2547: p_crh_gl_date ar_cash_receipt_history.gl_date%TYPE;
2548: p_crh_trx_date ar_cash_receipt_history.trx_date%TYPE;
2549: p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
2550: p_approval_code ar_cash_receipts.approval_code%TYPE;
2551: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
2552: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
2553: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
2554: p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
2547: p_crh_gl_date ar_cash_receipt_history.gl_date%TYPE;
2548: p_crh_trx_date ar_cash_receipt_history.trx_date%TYPE;
2549: p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
2550: p_approval_code ar_cash_receipts.approval_code%TYPE;
2551: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
2552: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
2553: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
2554: p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
2555:
2548: p_crh_trx_date ar_cash_receipt_history.trx_date%TYPE;
2549: p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
2550: p_approval_code ar_cash_receipts.approval_code%TYPE;
2551: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
2552: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
2553: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
2554: p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
2555:
2556: /* end declare */
2549: p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
2550: p_approval_code ar_cash_receipts.approval_code%TYPE;
2551: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
2552: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
2553: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
2554: p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
2555:
2556: /* end declare */
2557:
2550: p_approval_code ar_cash_receipts.approval_code%TYPE;
2551: p_receipt_number ar_cash_receipts.receipt_number%TYPE;
2552: p_unique_ref ar_cash_receipts.unique_reference%TYPE;
2553: p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
2554: p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
2555:
2556: /* end declare */
2557:
2558: /* declare the insert array elements */
3284: p_due_date_l IN ar_payment_schedules.due_date%TYPE,
3285: p_due_date_h IN ar_payment_schedules.due_date%TYPE,
3286: p_trx_num_l IN ar_payment_schedules.trx_number%TYPE,
3287: p_trx_num_h IN ar_payment_schedules.trx_number%TYPE,
3288: p_remittance_total_to IN ar_cash_receipts.amount%TYPE,
3289: p_remittance_total_from IN ar_cash_receipts.amount%TYPE,
3290: p_batch_id IN ar_batches.batch_id%TYPE,
3291: p_receipt_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
3292: p_currency_code IN ar_cash_receipts.currency_code%TYPE,
3285: p_due_date_h IN ar_payment_schedules.due_date%TYPE,
3286: p_trx_num_l IN ar_payment_schedules.trx_number%TYPE,
3287: p_trx_num_h IN ar_payment_schedules.trx_number%TYPE,
3288: p_remittance_total_to IN ar_cash_receipts.amount%TYPE,
3289: p_remittance_total_from IN ar_cash_receipts.amount%TYPE,
3290: p_batch_id IN ar_batches.batch_id%TYPE,
3291: p_receipt_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
3292: p_currency_code IN ar_cash_receipts.currency_code%TYPE,
3293: p_payment_type_code IN ar_receipt_methods.payment_type_code%TYPE,
3288: p_remittance_total_to IN ar_cash_receipts.amount%TYPE,
3289: p_remittance_total_from IN ar_cash_receipts.amount%TYPE,
3290: p_batch_id IN ar_batches.batch_id%TYPE,
3291: p_receipt_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
3292: p_currency_code IN ar_cash_receipts.currency_code%TYPE,
3293: p_payment_type_code IN ar_receipt_methods.payment_type_code%TYPE,
3294: p_sob_id IN ar_cash_receipts.set_of_books_id%TYPE,
3295: p_remit_method_code IN ar_receipt_classes.remit_method_code%TYPE,
3296: p_remit_bank_account_id IN ar_cash_receipts.remittance_bank_account_id%TYPE,
3290: p_batch_id IN ar_batches.batch_id%TYPE,
3291: p_receipt_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
3292: p_currency_code IN ar_cash_receipts.currency_code%TYPE,
3293: p_payment_type_code IN ar_receipt_methods.payment_type_code%TYPE,
3294: p_sob_id IN ar_cash_receipts.set_of_books_id%TYPE,
3295: p_remit_method_code IN ar_receipt_classes.remit_method_code%TYPE,
3296: p_remit_bank_account_id IN ar_cash_receipts.remittance_bank_account_id%TYPE,
3297: p_return_status OUT NOCOPY VARCHAR2
3298: ) IS
3292: p_currency_code IN ar_cash_receipts.currency_code%TYPE,
3293: p_payment_type_code IN ar_receipt_methods.payment_type_code%TYPE,
3294: p_sob_id IN ar_cash_receipts.set_of_books_id%TYPE,
3295: p_remit_method_code IN ar_receipt_classes.remit_method_code%TYPE,
3296: p_remit_bank_account_id IN ar_cash_receipts.remittance_bank_account_id%TYPE,
3297: p_return_status OUT NOCOPY VARCHAR2
3298: ) IS
3299:
3300: remit_recs INTEGER;
3301: l_sel_stmt long;
3302: l_rows_processed INTEGER;
3303: l_rows_fetched INTEGER;
3304: p_cash_receipt_id NUMBER(15);
3305: p_amount ar_cash_receipts.amount%TYPE;
3306: p_payment_method_id ar_receipt_methods.receipt_method_id%TYPE;
3307: cash_receipt_id_array dbms_sql.Number_Table;
3308: cr_id_array dbms_sql.Number_Table;
3309: amt_array dbms_sql.Number_Table;
3380: END IF;
3381: l_sel_stmt := l_sel_stmt || ' cr.cash_receipt_id
3382: FROM ar_rem_cr_id_gtt crh,
3383: ar_cash_receipt_history crh1,
3384: ar_cash_receipts cr,
3385: ar_payment_schedules ps,
3386: ar_receipt_classes rclass,
3387: ar_receipt_methods rm,
3388: ar_receipt_method_accounts rma1,
3481: IF p_remittance_total_from IS NOT NULL THEN
3482: l_sel_stmt := l_sel_stmt || ' AND cr.amount <= to_number(:remittance_total_from)';
3483: END IF;
3484: IF PG_PARALLEL IN ('Y', 'C') THEN
3485: upd_stmt2 := ' UPDATE /*+ parallel(R) index(R) */ ar_cash_receipts R';
3486: ELSE
3487: upd_stmt2 := ' UPDATE /*+ index(R) */ ar_cash_receipts R';
3488: END IF;
3489: upd_stmt2 := upd_stmt2 ||' SET selected_remittance_batch_id = :u_batch_id,
3483: END IF;
3484: IF PG_PARALLEL IN ('Y', 'C') THEN
3485: upd_stmt2 := ' UPDATE /*+ parallel(R) index(R) */ ar_cash_receipts R';
3486: ELSE
3487: upd_stmt2 := ' UPDATE /*+ index(R) */ ar_cash_receipts R';
3488: END IF;
3489: upd_stmt2 := upd_stmt2 ||' SET selected_remittance_batch_id = :u_batch_id,
3490: REMIT_BANK_ACCT_USE_ID = :u_remit_bank_account_id,
3491: last_update_date = sysdate,
3638: cr.customer_site_use_id,
3639: cr.payment_trxn_extension_id,
3640: cr.selected_remittance_batch_id,
3641: cr.receipt_date
3642: FROM ar_cash_receipts cr,
3643: ar_receipt_methods rm,
3644: ar_receipt_classes rc,
3645: hz_cust_accounts hca,
3646: hz_parties party,
3652: AND cr.receipt_method_id = rm.receipt_method_id
3653: AND rm.receipt_class_id = rc.receipt_class_id;
3654:
3655: -- rct_info rct_info_cur%ROWTYPE;
3656: l_cr_rec ar_cash_receipts_all%ROWTYPE;
3657: l_org_type HR_ALL_ORGANIZATION_UNITS.TYPE%TYPE;
3658: l_action VARCHAR2(80);
3659: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3660: l_msg_count NUMBER;
3688: ls_msg_count NUMBER;
3689: ls_msg_data VARCHAR2(2000);
3690: ls_response_rec_tab IBY_FNDCPT_TRXN_PUB.SettlementResult_tbl_type;
3691: ls_iby_msg_data VARCHAR2(2000);
3692: l_cr_id ar_cash_receipts.cash_receipt_id%type;
3693: l_paying_customer_id ar_cash_receipts.pay_from_customer%type;
3694: l_cust_site_id ar_cash_receipts.customer_site_use_id%type;
3695: l_count1 NUMBER;
3696:
3689: ls_msg_data VARCHAR2(2000);
3690: ls_response_rec_tab IBY_FNDCPT_TRXN_PUB.SettlementResult_tbl_type;
3691: ls_iby_msg_data VARCHAR2(2000);
3692: l_cr_id ar_cash_receipts.cash_receipt_id%type;
3693: l_paying_customer_id ar_cash_receipts.pay_from_customer%type;
3694: l_cust_site_id ar_cash_receipts.customer_site_use_id%type;
3695: l_count1 NUMBER;
3696:
3697: /* end declare varaibles for settlement */
3690: ls_response_rec_tab IBY_FNDCPT_TRXN_PUB.SettlementResult_tbl_type;
3691: ls_iby_msg_data VARCHAR2(2000);
3692: l_cr_id ar_cash_receipts.cash_receipt_id%type;
3693: l_paying_customer_id ar_cash_receipts.pay_from_customer%type;
3694: l_cust_site_id ar_cash_receipts.customer_site_use_id%type;
3695: l_count1 NUMBER;
3696:
3697: /* end declare varaibles for settlement */
3698:
3930: l_response_rec.Result_Message , 1, 240);
3931:
3932: fnd_file.put_line(FND_FILE.LOG, 'l_iby_msg_data: ' || l_iby_msg_data);
3933:
3934: UPDATE ar_cash_receipts
3935: SET cc_error_flag = 'Y',
3936: cc_error_code = l_response_rec.Result_Code,
3937: cc_error_text = l_response_rec.Result_Message,
3938: last_updated_by = l_last_updated_by,
3959:
3960: FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
3961: FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_vend_msg_data);
3962:
3963: UPDATE ar_cash_receipts
3964: SET cc_error_flag = 'Y',
3965: cc_error_code = l_authresult_rec.PaymentSys_Code,
3966: cc_error_text = l_authresult_rec.PaymentSys_Msg,
3967: last_updated_by = l_last_updated_by,
4004: p_exception_code => 'AR_CC_AUTH_FAILED',
4005: p_additional_message => l_iby_msg_data
4006: );
4007:
4008: UPDATE ar_cash_receipts
4009: SET cc_error_flag = 'Y',
4010: last_updated_by = l_last_updated_by,
4011: last_update_date = sysdate,
4012: last_update_login = l_last_update_login,
4132: p_exception_code => 'AR_CC_AUTH_FAILED',
4133: p_additional_message => lc_iby_msg_data
4134: );
4135:
4136: UPDATE ar_cash_receipts
4137: SET cc_error_flag = 'Y',
4138: cc_error_code = lc_response_rec.Result_Code,
4139: cc_error_text = lc_response_rec.Result_Message,
4140: last_updated_by = l_last_updated_by,
4168: p_exception_code => 'AR_CC_AUTH_FAILED',
4169: p_additional_message => l_iby_msg_data
4170: );
4171:
4172: UPDATE ar_cash_receipts
4173: SET cc_error_flag = 'Y',
4174: last_updated_by = l_last_updated_by,
4175: last_update_date = sysdate,
4176: last_update_login = l_last_update_login,
4286:
4287: Begin
4288: select cash_receipt_id,pay_from_customer,customer_site_use_id
4289: into l_cr_id,l_paying_customer_id,l_cust_site_id
4290: from ar_cash_receipts
4291: where selected_remittance_batch_id = p_batch_id and
4292: payment_trxn_extension_id = ls_response_rec_tab(i).Trxn_Extension_Id;
4293:
4294:
4303: p_additional_message => ls_iby_msg_data
4304: );
4305:
4306:
4307: UPDATE ar_cash_receipts
4308: SET cc_error_flag = 'Y',
4309: cc_error_code = ls_response_rec_tab(i).Result.Result_Code,
4310: cc_error_text = ls_response_rec_tab(i).Result.Result_Message,
4311: last_updated_by = l_last_updated_by,
4397: AND a.current_worker = WORKER_NUMBER;
4398:
4399:
4400: /*Bug 7666285*/
4401: CURSOR rct_date_cur (c_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE)
4402: IS
4403: SELECT receipt_date
4404: FROM ar_cash_receipts
4405: WHERE cash_receipt_id = c_cash_receipt_id;
4400: /*Bug 7666285*/
4401: CURSOR rct_date_cur (c_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE)
4402: IS
4403: SELECT receipt_date
4404: FROM ar_cash_receipts
4405: WHERE cash_receipt_id = c_cash_receipt_id;
4406:
4407:
4408:
4406:
4407:
4408:
4409: -- rct_info rct_info_cur%ROWTYPE;
4410: l_cr_rec ar_cash_receipts_all%ROWTYPE;
4411: l_org_type HR_ALL_ORGANIZATION_UNITS.TYPE%TYPE;
4412: l_action VARCHAR2(80);
4413: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4414: l_msg_count NUMBER;
4421: l_amount_rec IBY_FNDCPT_TRXN_PUB.Amount_rec_type;
4422: l_payer_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
4423: l_response_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type; /* OUT RESPONSE STRUCTURE */
4424:
4425: l_receipt_date ar_cash_receipts_all.receipt_date%TYPE; /*Bug7666285*/
4426:
4427: /* DECLARE the variables required for the payment engine (AUTH) all the REC TYPES */
4428:
4429: l_payee_rec IBY_FNDCPT_TRXN_PUB.PayeeContext_rec_type;
4443: ls_msg_count NUMBER;
4444: ls_msg_data VARCHAR2(2000);
4445: ls_response_rec_tab IBY_FNDCPT_TRXN_PUB.SettlementResult_tbl_type;
4446: ls_iby_msg_data VARCHAR2(2000);
4447: l_cr_id ar_cash_receipts.cash_receipt_id%type;
4448: l_paying_customer_id ar_cash_receipts.pay_from_customer%type;
4449: l_cust_site_id ar_cash_receipts.customer_site_use_id%type;
4450: l_count1 NUMBER;
4451:
4444: ls_msg_data VARCHAR2(2000);
4445: ls_response_rec_tab IBY_FNDCPT_TRXN_PUB.SettlementResult_tbl_type;
4446: ls_iby_msg_data VARCHAR2(2000);
4447: l_cr_id ar_cash_receipts.cash_receipt_id%type;
4448: l_paying_customer_id ar_cash_receipts.pay_from_customer%type;
4449: l_cust_site_id ar_cash_receipts.customer_site_use_id%type;
4450: l_count1 NUMBER;
4451:
4452: /* end declare varaibles for settlement */
4445: ls_response_rec_tab IBY_FNDCPT_TRXN_PUB.SettlementResult_tbl_type;
4446: ls_iby_msg_data VARCHAR2(2000);
4447: l_cr_id ar_cash_receipts.cash_receipt_id%type;
4448: l_paying_customer_id ar_cash_receipts.pay_from_customer%type;
4449: l_cust_site_id ar_cash_receipts.customer_site_use_id%type;
4450: l_count1 NUMBER;
4451:
4452: /* end declare varaibles for settlement */
4453:
4680: l_response_rec.Result_Message , 1, 240);
4681:
4682: fnd_file.put_line(FND_FILE.LOG, 'l_iby_msg_data: ' || l_iby_msg_data);
4683:
4684: UPDATE ar_cash_receipts
4685: SET cc_error_flag = 'Y',
4686: cc_error_code = l_response_rec.Result_Code,
4687: cc_error_text = l_response_rec.Result_Message,
4688: last_updated_by = l_last_updated_by,
4707:
4708: FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
4709: FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_vend_msg_data);
4710:
4711: UPDATE ar_cash_receipts
4712: SET cc_error_flag = 'Y',
4713: cc_error_code = l_authresult_rec.PaymentSys_Code,
4714: cc_error_text = l_authresult_rec.PaymentSys_Msg,
4715: last_updated_by = l_last_updated_by,
4752: p_exception_code => 'AR_CC_AUTH_FAILED',
4753: p_additional_message => l_iby_msg_data
4754: );
4755:
4756: UPDATE ar_cash_receipts
4757: SET cc_error_flag = 'Y',
4758: last_updated_by = l_last_updated_by,
4759: last_update_date = sysdate,
4760: last_update_login = l_last_update_login,
4904: p_exception_code => 'AR_CC_AUTH_FAILED',
4905: p_additional_message => lc_iby_msg_data
4906: );
4907:
4908: UPDATE ar_cash_receipts
4909: SET cc_error_flag = 'Y',
4910: cc_error_code = lc_response_rec.Result_Code,
4911: cc_error_text = lc_response_rec.Result_Message,
4912: last_updated_by = l_last_updated_by,
4940: p_exception_code => 'AR_CC_AUTH_FAILED',
4941: p_additional_message => l_iby_msg_data
4942: );
4943:
4944: UPDATE ar_cash_receipts
4945: SET cc_error_flag = 'Y',
4946: last_updated_by = l_last_updated_by,
4947: last_update_date = sysdate,
4948: last_update_login = l_last_update_login,
5066:
5067: Begin
5068: select cash_receipt_id,pay_from_customer,customer_site_use_id
5069: into l_cr_id,l_paying_customer_id,l_cust_site_id
5070: from ar_cash_receipts
5071: where selected_remittance_batch_id = p_batch_id and
5072: payment_trxn_extension_id = ls_response_rec_tab(i).Trxn_Extension_Id;
5073:
5074:
5083: p_additional_message => ls_iby_msg_data
5084: );
5085:
5086:
5087: UPDATE ar_cash_receipts
5088: SET cc_error_flag = 'Y',
5089: cc_error_code = ls_response_rec_tab(i).Result.Result_Code,
5090: cc_error_text = ls_response_rec_tab(i).Result.Result_Message,
5091: last_updated_by = l_last_updated_by,
5154: | 16-JUL-2005 bichatte Created |
5155: *=========================================================================*/
5156: PROCEDURE insert_exceptions(
5157: p_batch_id IN ar_batches.batch_id%TYPE DEFAULT NULL,
5158: p_request_id IN ar_cash_receipts.request_id%TYPE DEFAULT NULL,
5159: p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL,
5160: p_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL,
5161: p_paying_customer_id IN ar_cash_receipts.pay_from_customer%TYPE DEFAULT NULL,
5162: p_paying_site_use_id IN ar_cash_receipts.customer_site_use_id%TYPE DEFAULT NULL,
5155: *=========================================================================*/
5156: PROCEDURE insert_exceptions(
5157: p_batch_id IN ar_batches.batch_id%TYPE DEFAULT NULL,
5158: p_request_id IN ar_cash_receipts.request_id%TYPE DEFAULT NULL,
5159: p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL,
5160: p_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL,
5161: p_paying_customer_id IN ar_cash_receipts.pay_from_customer%TYPE DEFAULT NULL,
5162: p_paying_site_use_id IN ar_cash_receipts.customer_site_use_id%TYPE DEFAULT NULL,
5163: p_due_date IN ar_payment_schedules.due_date%TYPE DEFAULT NULL,
5157: p_batch_id IN ar_batches.batch_id%TYPE DEFAULT NULL,
5158: p_request_id IN ar_cash_receipts.request_id%TYPE DEFAULT NULL,
5159: p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL,
5160: p_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL,
5161: p_paying_customer_id IN ar_cash_receipts.pay_from_customer%TYPE DEFAULT NULL,
5162: p_paying_site_use_id IN ar_cash_receipts.customer_site_use_id%TYPE DEFAULT NULL,
5163: p_due_date IN ar_payment_schedules.due_date%TYPE DEFAULT NULL,
5164: p_cust_min_rec_amount IN NUMBER DEFAULT NULL,
5165: p_bank_min_rec_amount IN NUMBER DEFAULT NULL,
5158: p_request_id IN ar_cash_receipts.request_id%TYPE DEFAULT NULL,
5159: p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL,
5160: p_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL,
5161: p_paying_customer_id IN ar_cash_receipts.pay_from_customer%TYPE DEFAULT NULL,
5162: p_paying_site_use_id IN ar_cash_receipts.customer_site_use_id%TYPE DEFAULT NULL,
5163: p_due_date IN ar_payment_schedules.due_date%TYPE DEFAULT NULL,
5164: p_cust_min_rec_amount IN NUMBER DEFAULT NULL,
5165: p_bank_min_rec_amount IN NUMBER DEFAULT NULL,
5166: p_exception_code IN VARCHAR2,
5304: *=========================================================================*/
5305:
5306: PROCEDURE SUBMIT_REPORT (
5307: p_batch_id ar_batches.batch_id%TYPE,
5308: p_request_id ar_cash_receipts.request_id%TYPE
5309: ) IS
5310:
5311: l_reqid NUMBER(15);
5312: l_org_id NUMBEr;
5446: l_last_update_login := arp_standard.profile.last_update_login ;
5447: l_program_application_id := arp_standard.application_id ;
5448: l_program_id := arp_standard.profile.program_id;
5449:
5450: UPDATE ar_cash_receipts
5451: SET cc_error_flag = 'Y',
5452: last_updated_by = l_last_updated_by,
5453: last_update_date = sysdate,
5454: last_update_login = l_last_update_login,
5457: program_id = l_program_id,
5458: program_update_date = sysdate
5459: WHERE cash_receipt_id in (
5460: SELECT cr.cash_receipt_id
5461: FROM ar_cash_receipts cr,
5462: ar_cash_receipt_history crh,
5463: iby_trxn_extensions_v trxn_ext
5464: WHERE crh.request_id = l_request_id
5465: AND crh.status = 'REMITTED'
5509: l_last_update_login,
5510: l_program_application_id,
5511: l_program_id,
5512: sysdate
5513: FROM ar_cash_receipts cr,
5514: ar_cash_receipt_history crh,
5515: iby_trxn_extensions_v trxn_ext
5516: WHERE crh.request_id = l_request_id
5517: AND crh.status = 'REMITTED'
5527: AND summ.reqtype in ('ORAPMTCAPTURE','ORAPMTBATCHREQ')
5528: AND summ.status IN(0, 11, 100));
5529: end if;
5530:
5531: UPDATE ar_cash_receipts
5532: SET cc_error_flag = 'Y',
5533: last_updated_by = l_last_updated_by,
5534: last_update_date = sysdate,
5535: last_update_login = l_last_update_login,
5538: program_id = l_program_id,
5539: program_update_date = sysdate
5540: WHERE cash_receipt_id in (
5541: SELECT cr.cash_receipt_id
5542: FROM ar_cash_receipts cr,
5543: ar_cash_receipt_history crh,
5544: iby_trxn_extensions_v trxn_ext
5545: WHERE crh.request_id = l_request_id
5546: AND crh.status = 'REMITTED'
5590: l_last_update_login,
5591: l_program_application_id,
5592: l_program_id,
5593: sysdate
5594: FROM ar_cash_receipts cr,
5595: ar_cash_receipt_history crh,
5596: iby_trxn_extensions_v trxn_ext
5597: WHERE crh.request_id = l_request_id
5598: AND crh.status = 'REMITTED'
5633: END IF;
5634:
5635: IF G_ERROR = 'Y' THEN
5636:
5637: UPDATE ar_cash_receipts
5638: SET cc_error_flag = null
5639: WHERE cash_receipt_id in (
5640: SELECT cr.cash_receipt_id
5641: FROM ar_cash_receipts cr,
5637: UPDATE ar_cash_receipts
5638: SET cc_error_flag = null
5639: WHERE cash_receipt_id in (
5640: SELECT cr.cash_receipt_id
5641: FROM ar_cash_receipts cr,
5642: ar_cash_receipt_history crh,
5643: iby_trxn_extensions_v trxn_ext
5644: WHERE cr.request_id = p_request_id
5645: AND cr.cc_error_flag = 'Y'
5660:
5661: fnd_file.put_line(FND_FILE.LOG,'CASH receipt rows updated : '||sql%rowcount);
5662:
5663:
5664: UPDATE ar_cash_receipts
5665: SET cc_error_flag = null
5666: WHERE cash_receipt_id in (
5667: SELECT cr.cash_receipt_id
5668: FROM ar_cash_receipts cr,
5664: UPDATE ar_cash_receipts
5665: SET cc_error_flag = null
5666: WHERE cash_receipt_id in (
5667: SELECT cr.cash_receipt_id
5668: FROM ar_cash_receipts cr,
5669: ar_cash_receipt_history crh,
5670: iby_trxn_extensions_v trxn_ext
5671: WHERE cr.request_id = p_request_id
5672: AND cr.cc_error_flag = 'Y'
5688: fnd_file.put_line(FND_FILE.LOG,'MISC receipt rows updated : '||sql%rowcount);
5689:
5690: fnd_file.put_line(FND_FILE.LOG,'delete the bad receipts');
5691:
5692: update ar_cash_receipts
5693: set selected_remittance_batch_id = null
5694: where request_id = p_request_id
5695: and cc_error_flag = 'Y';
5696:
5706: current_record_flag = 'Y'
5707: where request_id = p_request_id
5708: and status = 'CONFIRMED'
5709: and cash_receipt_id in ( select cash_receipt_id
5710: from ar_cash_receipts
5711: where request_id = p_request_id
5712: and cc_error_flag = 'Y');
5713:
5714: IF PG_DEBUG in ('Y','C') THEN
5718: delete from ar_distributions
5719: where source_table = 'CRH'
5720: and source_id in ( select crh.cash_receipt_history_id
5721: from ar_cash_receipt_history crh,
5722: ar_cash_receipts cr
5723: where crh.STATUS = 'REMITTED'
5724: and crh.request_id = p_request_id
5725: and cr.cash_receipt_id = crh.cash_receipt_id
5726: and cr.request_id = p_request_id
5733: delete from ar_cash_receipt_history
5734: where STATUS = 'REMITTED'
5735: and request_id = p_request_id
5736: and cash_receipt_id in ( select cash_receipt_id
5737: from ar_cash_receipts
5738: where request_id = p_request_id
5739: and cc_error_flag = 'Y');
5740:
5741: