[Home] [Help]
1: PACKAGE BODY ARP_AUTOAPPLY_API AS
2: /*$Header: ARATAPPB.pls 120.0.12010000.11 2009/05/12 07:46:11 aghoraka noship $*/
3: PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4: G_PROGRAM_ID NUMBER := 111;
5: G_PROGRAM_APPLICATION_ID NUMBER := 222;
259: l_table_size NUMBER;
260: i NUMBER;
261: BEGIN
262: IF (PG_DEBUG IN ('Y', 'C')) THEN
263: log('arp_autoapply_api.is_rule_processed(+)');
264: log('Rule Id: '||p_rule_id);
265: END IF;
266: l_table_size := NVL(l_processed_rules.last, 0);
267:
268: FOR i IN 1..l_table_size LOOP
269: IF l_processed_rules(i) = p_rule_id THEN
270: IF (PG_DEBUG IN ('Y', 'C')) THEN
271: log('Rule already processed.');
272: log('arp_autoapply_api.is_rule_processed(-)');
273: END IF;
274: RETURN TRUE;
275: END IF;
276: END LOOP;
277:
278: l_processed_rules(l_table_size + 1) := p_rule_id;
279: IF (PG_DEBUG IN ('Y', 'C')) THEN
280: log('New Rule.');
281: log('arp_autoapply_api.is_rule_processed(-)');
282: END IF;
283: RETURN FALSE;
284: END is_rule_processed;
285:
346: l_rmt_fl_exp VARCHAR2(1000);
347:
348: BEGIN
349: IF (PG_DEBUG IN ('Y', 'C')) THEN
350: log('arp_autoapply_api.gen_regexp(+)');
351: log('Rule Id: '||p_rule_id);
352: END IF;
353: FOR r_rule_dtls IN rule_dtls(p_rule_id) LOOP
354: IF (r_rule_dtls.type = 'DOCUMENT') THEN /* Document Number */
514: p_org_id IN NUMBER) IS
515: l_request_id NUMBER(15);
516: BEGIN
517: IF (PG_DEBUG IN ('Y', 'C')) THEN
518: log('arp_autoapply_api.submit_subrequest(+)');
519: log('Worker No : '|| p_worker_number);
520: END IF;
521:
522: FND_REQUEST.SET_ORG_ID(p_org_id);
538: END IF;
539:
540: l_req_status_tab(p_worker_number).request_id := l_request_id;
541: IF (PG_DEBUG IN ('Y', 'C')) THEN
542: log('arp_autoapply_api.submit_subrequest(-)');
543: END IF;
544: EXCEPTION
545: WHEN OTHERS THEN
546: log('Exception from arp_autoapply_api.submit_subrequest');
542: log('arp_autoapply_api.submit_subrequest(-)');
543: END IF;
544: EXCEPTION
545: WHEN OTHERS THEN
546: log('Exception from arp_autoapply_api.submit_subrequest');
547: log(SQLERRM);
548: RAISE;
549: END submit_subrequest;
550:
549: END submit_subrequest;
550:
551: BEGIN
552: IF (PG_DEBUG IN ('Y', 'C')) THEN
553: log('arp_autoapply_api.auto_apply_master(+)');
554: log('Org Id : ' || p_org_id);
555: log('Receipt Number From ' || p_receipt_no_l || ' To ' || p_receipt_no_h);
556: log('Batch Name From ' || p_batch_name_l || ' To ' || p_batch_name_h);
557: log('Minimun Unapplied Amount : ' || p_min_unapp_amt);
908:
909: COMMIT;
910:
911: IF (PG_DEBUG IN ('Y', 'C')) THEN
912: log('arp_autoapply_api.auto_apply_master(-)');
913: END IF;
914: EXCEPTION
915: WHEN OTHERS THEN
916: log('Exception from arp_autoapply_api.auto_apply_master');
912: log('arp_autoapply_api.auto_apply_master(-)');
913: END IF;
914: EXCEPTION
915: WHEN OTHERS THEN
916: log('Exception from arp_autoapply_api.auto_apply_master');
917: log(SQLERRM);
918: RAISE;
919:
920: END auto_apply_master;
989: l_rem_float_str VARCHAR2(1000);
990: p_request_id NUMBER := -1;
991: BEGIN
992: IF (PG_DEBUG IN ('Y', 'C')) THEN
993: log('arp_autoapply_api.auto_apply_child(+)');
994: log('Worker Number : ' || p_worker_number);
995: END IF;
996: l_worker_number := p_worker_number;
997: p_request_id := arp_standard.profile.request_id;
1092:
1093: apply_trx_recos(p_request_id, p_worker_number);
1094:
1095: IF (PG_DEBUG IN ('Y', 'C')) THEN
1096: log('arp_autoapply_api.auto_apply_child(-)');
1097: END IF;
1098: EXCEPTION
1099: WHEN OTHERS THEN
1100: log('Exception from arp_autoapply_api.auto_apply_main');
1096: log('arp_autoapply_api.auto_apply_child(-)');
1097: END IF;
1098: EXCEPTION
1099: WHEN OTHERS THEN
1100: log('Exception from arp_autoapply_api.auto_apply_main');
1101: log(SQLERRM);
1102: RAISE;
1103: END auto_apply_child;
1104:
1193: ps.discount_taken_unearned discount_taken_unearned,
1194: ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
1195: ROUND(NVL(ref.trans_to_receipt_rate,
1196: DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
1197: NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
1198: ref.amount_applied,
1199: ref.amount_applied_from,
1200: ps.invoice_currency_code,
1201: cr.currency_code
1281: l_current_reco_line NUMBER;
1282: got_current_block BOOLEAN;
1283: BEGIN
1284: IF (PG_DEBUG IN ('Y', 'C')) THEN
1285: log('arp_autoapply_api.insert_invoice_recos(+)');
1286: log('Auto Match ID :'||p_automatch_id);
1287: log('Worker Number :'||p_worker_number);
1288: END IF;
1289: OPEN select_recos;
1340: END LOOP;
1341: END LOOP;
1342:
1343: IF (PG_DEBUG IN ('Y', 'C')) THEN
1344: log('arp_autoapply_api.insert_invoice_recos(-)');
1345: END IF;
1346: EXCEPTION
1347: WHEN OTHERS THEN
1348: log('Exception from arp_autoapply_api.insert_invoice_recos');
1344: log('arp_autoapply_api.insert_invoice_recos(-)');
1345: END IF;
1346: EXCEPTION
1347: WHEN OTHERS THEN
1348: log('Exception from arp_autoapply_api.insert_invoice_recos');
1349: log(SQLERRM);
1350: RAISE;
1351: END insert_invoice_recos;
1352:
1440: ps.discount_taken_unearned discount_taken_unearned,
1441: ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
1442: ROUND(NVL(ref.trans_to_receipt_rate,
1443: DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
1444: NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
1445: ref.amount_applied,
1446: ref.amount_applied_from,
1447: ps.invoice_currency_code,
1448: cr.currency_code
1526: l_current_reco_line NUMBER;
1527: got_current_block BOOLEAN;
1528: BEGIN
1529: IF (PG_DEBUG IN ('Y', 'C')) THEN
1530: log('arp_autoapply_api.insert_po_recos(+)');
1531: END IF;
1532: OPEN select_recos;
1533: LOOP
1534: FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
1580: l_current_reco.DELETE;
1581: END LOOP;
1582: END LOOP;
1583: IF (PG_DEBUG IN ('Y', 'C')) THEN
1584: log('arp_autoapply_api.insert_po_recos(-)');
1585: END IF;
1586: EXCEPTION
1587: WHEN OTHERS THEN
1588: log('Exception from arp_autoapply_api.insert_po_recos');
1584: log('arp_autoapply_api.insert_po_recos(-)');
1585: END IF;
1586: EXCEPTION
1587: WHEN OTHERS THEN
1588: log('Exception from arp_autoapply_api.insert_po_recos');
1589: log(SQLERRM);
1590: RAISE;
1591: END insert_po_recos;
1592:
1681: ps.discount_taken_unearned discount_taken_unearned,
1682: ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
1683: ROUND(NVL(ref.trans_to_receipt_rate,
1684: DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
1685: NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
1686: ref.amount_applied,
1687: ref.amount_applied_from,
1688: ps.invoice_currency_code,
1689: cr.currency_code
1771: l_current_reco_line NUMBER;
1772: got_current_block BOOLEAN;
1773: BEGIN
1774: IF (PG_DEBUG IN ('Y', 'C')) THEN
1775: log('arp_autoapply_api.insert_so_recos(+)');
1776: END IF;
1777: OPEN select_recos;
1778: LOOP
1779: FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
1824: l_current_reco.DELETE;
1825: END LOOP;
1826: END LOOP;
1827: IF (PG_DEBUG IN ('Y', 'C')) THEN
1828: log('arp_autoapply_api.insert_so_recos(-)');
1829: END IF;
1830: EXCEPTION
1831: WHEN OTHERS THEN
1832: log('Exception from arp_autoapply_api.insert_so_recos');
1828: log('arp_autoapply_api.insert_so_recos(-)');
1829: END IF;
1830: EXCEPTION
1831: WHEN OTHERS THEN
1832: log('Exception from arp_autoapply_api.insert_so_recos');
1833: log(SQLERRM);
1834: RAISE;
1835: END insert_so_recos;
1836:
1925: ps.discount_taken_unearned discount_taken_unearned,
1926: ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
1927: ROUND(NVL(ref.trans_to_receipt_rate,
1928: DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
1929: NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
1930: ref.amount_applied,
1931: ref.amount_applied_from,
1932: ps.invoice_currency_code,
1933: cr.currency_code
2015: l_current_reco_line NUMBER;
2016: got_current_block BOOLEAN;
2017: BEGIN
2018: IF (PG_DEBUG IN ('Y', 'C')) THEN
2019: log('arp_autoapply_api.insert_contract_recos(+)');
2020: END IF;
2021: OPEN select_recos;
2022: LOOP
2023: FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
2068: l_current_reco.DELETE;
2069: END LOOP;
2070: END LOOP;
2071: IF (PG_DEBUG IN ('Y', 'C')) THEN
2072: log('arp_autoapply_api.insert_contract_recos(-)');
2073: END IF;
2074: EXCEPTION
2075: WHEN OTHERS THEN
2076: log('Exception from arp_autoapply_api.insert_contract_recos');
2072: log('arp_autoapply_api.insert_contract_recos(-)');
2073: END IF;
2074: EXCEPTION
2075: WHEN OTHERS THEN
2076: log('Exception from arp_autoapply_api.insert_contract_recos');
2077: log(SQLERRM);
2078: RAISE;
2079: END insert_contract_recos;
2080:
2172: ps.discount_taken_unearned discount_taken_unearned,
2173: ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
2174: ROUND(NVL(ref.trans_to_receipt_rate,
2175: DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
2176: NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
2177: ref.amount_applied,
2178: ref.amount_applied_from,
2179: ps.invoice_currency_code,
2180: cr.currency_code
2264: l_current_reco_line NUMBER;
2265: got_current_block BOOLEAN;
2266: BEGIN
2267: IF (PG_DEBUG IN ('Y', 'C')) THEN
2268: log('arp_autoapply_api.insert_attribute_recos(+)');
2269: log('SQL : '||l_sel_stmt);
2270: END IF;
2271:
2272: OPEN select_recos FOR l_sel_stmt USING p_trans_format_str,
2335: END LOOP;
2336: END LOOP;
2337:
2338: IF (PG_DEBUG IN ('Y', 'C')) THEN
2339: log('arp_autoapply_api.insert_attribute_recos(-)');
2340: END IF;
2341: EXCEPTION
2342: WHEN OTHERS THEN
2343: log('Exception from arp_autoapply_api.insert_attribute_recos');
2339: log('arp_autoapply_api.insert_attribute_recos(-)');
2340: END IF;
2341: EXCEPTION
2342: WHEN OTHERS THEN
2343: log('Exception from arp_autoapply_api.insert_attribute_recos');
2344: log(SQLERRM);
2345: RAISE;
2346: END insert_attribute_recos;
2347:
2433: ps.discount_taken_unearned discount_taken_unearned,
2434: ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
2435: ROUND(NVL(ref.trans_to_receipt_rate,
2436: DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
2437: NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
2438: ref.amount_applied,
2439: ref.amount_applied_from,
2440: ps.invoice_currency_code,
2441: cr.currency_code
2519: l_current_reco_line NUMBER;
2520: got_current_block BOOLEAN;
2521: BEGIN
2522: IF (PG_DEBUG IN ('Y', 'C')) THEN
2523: log('arp_autoapply_api.insert_waybill_recos(+)');
2524: END IF;
2525: OPEN select_recos;
2526: LOOP
2527: FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
2572: l_current_reco.DELETE;
2573: END LOOP;
2574: END LOOP;
2575: IF (PG_DEBUG IN ('Y', 'C')) THEN
2576: log('arp_autoapply_api.insert_waybill_recos(-)');
2577: END IF;
2578: EXCEPTION
2579: WHEN OTHERS THEN
2580: log('Exception from arp_autoapply_api.insert_waybill_recos');
2576: log('arp_autoapply_api.insert_waybill_recos(-)');
2577: END IF;
2578: EXCEPTION
2579: WHEN OTHERS THEN
2580: log('Exception from arp_autoapply_api.insert_waybill_recos');
2581: log(SQLERRM);
2582: RAISE;
2583: END insert_waybill_recos;
2584:
2655: ps.discount_taken_unearned discount_taken_unearned,
2656: ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
2657: ROUND(NVL(ref.trans_to_receipt_rate,
2658: DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
2659: NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
2660: ref.amount_applied,
2661: ref.amount_applied_from,
2662: ps.invoice_currency_code,
2663: cr.currency_code
2741: l_current_reco_line NUMBER;
2742: got_current_block BOOLEAN;
2743: BEGIN
2744: IF (PG_DEBUG IN ('Y', 'C')) THEN
2745: log('arp_autoapply_api.insert_bfb_recos(+)');
2746: END IF;
2747:
2748: OPEN select_recos;
2749: LOOP
2799: END LOOP;
2800: END LOOP;
2801:
2802: IF (PG_DEBUG IN ('Y', 'C')) THEN
2803: log('arp_autoapply_api.insert_bfb_recos(-)');
2804: END IF;
2805: EXCEPTION
2806: WHEN OTHERS THEN
2807: log('Exception from arp_autoapply_api.insert_bfb_recos');
2803: log('arp_autoapply_api.insert_bfb_recos(-)');
2804: END IF;
2805: EXCEPTION
2806: WHEN OTHERS THEN
2807: log('Exception from arp_autoapply_api.insert_bfb_recos');
2808: log(SQLERRM);
2809: RAISE;
2810: END insert_bfb_recos;
2811: /*===========================================================================+
2900: ps.discount_taken_unearned discount_taken_unearned,
2901: ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
2902: ROUND(NVL(ref.trans_to_receipt_rate,
2903: DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
2904: NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
2905: ref.amount_applied,
2906: ref.amount_applied_from,
2907: ps.invoice_currency_code,
2908: cr.currency_code
2988: l_current_reco_line NUMBER;
2989: got_current_block BOOLEAN;
2990: BEGIN
2991: IF (PG_DEBUG IN ('Y', 'C')) THEN
2992: log('arp_autoapply_api.insert_reference_recos(+)');
2993: END IF;
2994: OPEN select_recos;
2995: LOOP
2996: FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
3041: l_current_reco.DELETE;
3042: END LOOP;
3043: END LOOP;
3044: IF (PG_DEBUG IN ('Y', 'C')) THEN
3045: log('arp_autoapply_api.insert_reference_recos(-)');
3046: END IF;
3047: EXCEPTION
3048: WHEN OTHERS THEN
3049: log('Exception from arp_autoapply_api.insert_reference_recos');
3045: log('arp_autoapply_api.insert_reference_recos(-)');
3046: END IF;
3047: EXCEPTION
3048: WHEN OTHERS THEN
3049: log('Exception from arp_autoapply_api.insert_reference_recos');
3050: log(SQLERRM);
3051: RAISE;
3052: END insert_reference_recos;
3053:
3114: TYPE psid_tab IS TABLE OF ar_payment_schedules.payment_schedule_id%TYPE INDEX BY PLS_INTEGER;
3115: locked_ps_records psid_tab;
3116: BEGIN
3117: IF (PG_DEBUG IN ('Y', 'C')) THEN
3118: log('arp_autoapply_api.validate_trx_recos(+)');
3119: END IF;
3120:
3121: /* * If Use_Matching_Date is set to 'ALWAYS' in Automatch Rule Setup *
3122: * resolved_matching_date of the recommendations must equal the *
3710: AND payment_schedule_id = locked_ps_records(i);
3711:
3712:
3713: IF (PG_DEBUG IN ('Y', 'C')) THEN
3714: log('arp_autoapply_api.validate_trx_recos(-)');
3715: END IF;
3716: EXCEPTION
3717: WHEN OTHERS THEN
3718: log('Exception from arp_autoapply_api.validate_trx_recos');
3714: log('arp_autoapply_api.validate_trx_recos(-)');
3715: END IF;
3716: EXCEPTION
3717: WHEN OTHERS THEN
3718: log('Exception from arp_autoapply_api.validate_trx_recos');
3719: log(SQLERRM);
3720: RAISE;
3721: END validate_trx_recos;
3722:
3834: ORDER BY lin.recommendation_id, lin.line_number;
3835:
3836: BEGIN
3837: IF (PG_DEBUG IN ('Y', 'C')) THEN
3838: log('arp_autoapply_api.apply_trx_recos(+)');
3839: log('p_req_id: ' || p_req_id);
3840: log('p_worker_number: ' || p_worker_number);
3841: END IF;
3842: FOR app_reco IN app_reco_cur LOOP
4035: IF (PG_DEBUG IN ('Y', 'C')) THEN
4036: log('No. of Recos deleted: ' || SQL%ROWCOUNT );
4037: END IF;
4038: IF (PG_DEBUG IN ('Y', 'C')) THEN
4039: log('arp_autoapply_api.apply_trx_recos(-)' );
4040: END IF;
4041: EXCEPTION
4042: WHEN OTHERS THEN
4043: log('Exception from arp_autoapply_api.apply_trx_recos');
4039: log('arp_autoapply_api.apply_trx_recos(-)' );
4040: END IF;
4041: EXCEPTION
4042: WHEN OTHERS THEN
4043: log('Exception from arp_autoapply_api.apply_trx_recos');
4044: log(SQLERRM);
4045: RAISE;
4046: END apply_trx_recos;
4047:
4253: log('process_single_reco()-');
4254: END IF;
4255: EXCEPTION
4256: WHEN OTHERS THEN
4257: log('Exception from arp_autoapply_api.process_single_reco');
4258: log(SQLERRM);
4259: RAISE;
4260: END process_single_reco;
4261:
4307: log('populate_reco_line_struct()-');
4308: END IF;
4309: EXCEPTION
4310: WHEN OTHERS THEN
4311: log('Exception from arp_autoapply_api.populate_reco_line_struct');
4312: log(SQLERRM);
4313: RAISE;
4314: END populate_reco_line_struct;
4315:
4348: log('clear_Reco_lines_struct()-');
4349: END IF;
4350: EXCEPTION
4351: WHEN OTHERS THEN
4352: log('Exception from arp_autoapply_api.clear_Reco_lines_struct');
4353: log(SQLERRM);
4354: RAISE;
4355: END clear_Reco_lines_struct;
4356:
4477: log('insert_recos()+');
4478: END IF;
4479: EXCEPTION
4480: WHEN OTHERS THEN
4481: log('Exception from arp_autoapply_api.insert_recos');
4482: log(SQLERRM);
4483: RAISE;
4484: END insert_recos;
4485:
4552: l_allow_discount VARCHAR2(1);
4553: l_grace_days NUMBER;
4554: BEGIN
4555: IF (PG_DEBUG IN ('Y', 'C')) THEN
4556: log('arp_autoapply_api.calc_amount_app_and_disc(+)');
4557: END IF;
4558: l_customer_id := p_customer_id;
4559: l_bill_to_site_use_id := p_bill_to_site_use_id;
4560: l_invoice_currency_code := p_invoice_currency_code;
4628: , p_cash_receipt_id => l_cash_receipt_id
4629: , p_allow_discount => l_allow_discount
4630: );
4631: IF (PG_DEBUG IN ('Y', 'C')) THEN
4632: log('arp_autoapply_api.calc_amount_app_and_disc(-)');
4633: END IF;
4634: EXCEPTION
4635: WHEN OTHERS THEN
4636: log('Exception from arp_autoapply_api.calc_amount_app_and_disc()');
4632: log('arp_autoapply_api.calc_amount_app_and_disc(-)');
4633: END IF;
4634: EXCEPTION
4635: WHEN OTHERS THEN
4636: log('Exception from arp_autoapply_api.calc_amount_app_and_disc()');
4637: log(SQLERRM);
4638: RAISE;
4639: END;
4640:
4747: +===========================================================================*/
4748: PROCEDURE delete_interim_records IS
4749: BEGIN
4750: IF (PG_DEBUG IN ('Y', 'C')) THEN
4751: log('arp_autoapply_api.delete_interim_records()+' );
4752: END IF;
4753: DELETE FROM ar_cash_remit_refs_interim;
4754: IF (PG_DEBUG IN ('Y', 'C')) THEN
4755: log('No. of records deleted: ' || SQL%ROWCOUNT );
4754: IF (PG_DEBUG IN ('Y', 'C')) THEN
4755: log('No. of records deleted: ' || SQL%ROWCOUNT );
4756: END IF;
4757: IF (PG_DEBUG IN ('Y', 'C')) THEN
4758: log('arp_autoapply_api.delete_interim_records(-)' );
4759: END IF;
4760: EXCEPTION
4761: WHEN OTHERS THEN
4762: log('Exception from arp_autoapply_api.delete_interim_records');
4758: log('arp_autoapply_api.delete_interim_records(-)' );
4759: END IF;
4760: EXCEPTION
4761: WHEN OTHERS THEN
4762: log('Exception from arp_autoapply_api.delete_interim_records');
4763: log(SQLERRM);
4764: RAISE;
4765: END delete_interim_records;
4766:
4763: log(SQLERRM);
4764: RAISE;
4765: END delete_interim_records;
4766:
4767: END ARP_AUTOAPPLY_API;