[Home] [Help]
151: , p_terms_sequence_number IN AR_PAYMENT_SCHEDULES.terms_sequence_number%TYPE
152: , p_trx_date IN AR_PAYMENT_SCHEDULES.trx_date%TYPE
153: , p_allow_overapp_flag IN RA_CUST_TRX_TYPES.allow_overapplication_flag%TYPE
154: , p_partial_discount_flag IN RA_TERMS.partial_discount_flag%TYPE
155: , p_input_amount IN AR_CASH_REMIT_REFS.amount_applied%TYPE
156: , p_amount_due_original IN AR_PAYMENT_SCHEDULES.amount_due_original%TYPE
157: , p_amount_due_remaining IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE
158: , p_discount_taken_earned IN AR_PAYMENT_SCHEDULES.discount_taken_earned%TYPE
159: , p_discount_taken_unearned IN AR_PAYMENT_SCHEDULES.discount_taken_unearned%TYPE
418: * P_RETCODE Return Code *
419: * *
420: * RETURNS NONE *
421: * ALGORITHM *
422: * 1. Delete data from ar_cash_remit_refs_interim, if any data is present. *
423: * The table is truncated at the end of each run. However if any data *
424: * exists inside the interim event necause of any unhandled exception in*
425: * the previous run, just a precautionary measure to retrunc the table *
426: * 2. Populate ar_cash_Remit_refs_interim with data from ar_cash_remit_refs*
422: * 1. Delete data from ar_cash_remit_refs_interim, if any data is present. *
423: * The table is truncated at the end of each run. However if any data *
424: * exists inside the interim event necause of any unhandled exception in*
425: * the previous run, just a precautionary measure to retrunc the table *
426: * 2. Populate ar_cash_Remit_refs_interim with data from ar_cash_remit_refs*
427: * based on the parameters provided to the concurrent program. *
428: * 3. Update the references with status 'AR_AA_RULE_SET_INACTIVE' which *
429: * are associated to a rule set that is inactive. *
430: * 4. Spawn the child process or directly call auto_apply_child() process *
568: G_PROGRAM_APPLICATION_ID := arp_standard.application_id;
569: G_CREATED_BY := arp_standard.profile.user_id;
570: G_LAST_UPDATED_BY := arp_standard.profile.user_id;
571: G_LAST_UPDATE_LOGIN := arp_standard.profile.last_update_login;
572: /* Fetch the data from ar_cash_remit_refs table for the current run
573: based on the parameters */
574: l_insert_stmt := ' INSERT INTO AR_CASH_REMIT_REFS_INTERIM
575: ( REMIT_REFERENCE_ID,
576: RECEIPT_REFERENCE_STATUS,
570: G_LAST_UPDATED_BY := arp_standard.profile.user_id;
571: G_LAST_UPDATE_LOGIN := arp_standard.profile.last_update_login;
572: /* Fetch the data from ar_cash_remit_refs table for the current run
573: based on the parameters */
574: l_insert_stmt := ' INSERT INTO AR_CASH_REMIT_REFS_INTERIM
575: ( REMIT_REFERENCE_ID,
576: RECEIPT_REFERENCE_STATUS,
577: AUTOMATCH_SET_ID,
578: CASH_RECEIPT_ID,
610: ref.TRANSMISSION_ID,
611: ref.BATCH_ID,
612: MOD( ref.CASH_RECEIPT_ID, :b_total_workers) + 1';
613:
614: l_from_clause := ' FROM AR_CASH_REMIT_REFS ref
615: , AR_CASH_RECEIPTS cr ';
616:
617: l_where_clause := ' WHERE ref.auto_applied = ''N''
618: AND ref.manually_applied = ''N''
770: * rule set provided in the receipt is not active as per the *
771: * receipt date. These references will not be processed further *
772: * by the Automatic Cash Application Program * */
773:
774: UPDATE ar_cash_remit_refs_interim cri
775: SET cri.receipt_reference_status = 'AR_AA_RULE_SET_INACTIVE'
776: WHERE cri.cash_receipt_id IN (
777: SELECT distinct cr.cash_receipt_id
778: FROM ar_cash_remit_refs_interim cri1,
774: UPDATE ar_cash_remit_refs_interim cri
775: SET cri.receipt_reference_status = 'AR_AA_RULE_SET_INACTIVE'
776: WHERE cri.cash_receipt_id IN (
777: SELECT distinct cr.cash_receipt_id
778: FROM ar_cash_remit_refs_interim cri1,
779: ar_cash_auto_rule_sets aca,
780: ar_cash_receipts cr
781: WHERE cr.cash_receipt_id = cri1.cash_receipt_id
782: AND cr.automatch_set_id = aca.automatch_set_id
787: AND cri.receipt_reference_status = 'AR_AM_NEW';
788:
789: /* * Mark references with status 'AR_AA_AUTOAPPLY_NOT_SET' if auto *
790: * match set id is not present in both receipt and reference info * */
791: UPDATE ar_cash_remit_refs_interim
792: SET receipt_reference_status = 'AR_AA_RULE_SET_NOT_PASSED'
793: WHERE automatch_set_id IS NULL
794: AND receipt_reference_status = 'AR_AM_NEW';
795:
792: SET receipt_reference_status = 'AR_AA_RULE_SET_NOT_PASSED'
793: WHERE automatch_set_id IS NULL
794: AND receipt_reference_status = 'AR_AM_NEW';
795:
796: UPDATE ar_cash_remit_refs_interim
797: SET receipt_reference_status = 'AR_AA_AMT_NOT_PASSED'
798: WHERE amount_applied IS NULL
799: AND amount_applied_from IS NULL
800: AND receipt_reference_status = 'AR_AM_NEW';
806:
807: DELETE FROM ar_cash_reco_lines lines
808: WHERE EXISTS (
809: SELECT 'Suggestion Exists'
810: FROM ar_cash_recos rec, ar_cash_remit_refs_interim ref
811: WHERE rec.recommendation_id = lines.recommendation_id
812: AND rec.remit_reference_id = ref.remit_reference_id
813: AND ref.receipt_reference_status = 'AR_AM_NEW'
814: );
815:
816: DELETE FROM ar_cash_recos rec
817: WHERE EXISTS(
818: SELECT 'Suggestion Exists'
819: FROM ar_cash_remit_refs_interim ref
820: WHERE rec.remit_reference_id = ref.remit_reference_id
821: AND ref.receipt_reference_status = 'AR_AM_NEW'
822: );
823:
872: END IF;
873: /* * AutoCash Application Process Completed. Now update the receipt_ *
874: * reference_status for the unapplied references with either No Match*
875: * Found or Suggestions found based on recommendations generated * */
876: UPDATE ar_cash_remit_refs_interim cri
877: SET cri.receipt_reference_status = DECODE(
878: ( SELECT 'MATCH_FOUND'
879: FROM ar_cash_recos
880: WHERE remit_reference_id = cri.remit_reference_id
880: WHERE remit_reference_id = cri.remit_reference_id
881: AND rownum = 1 ),'MATCH_FOUND','AR_AA_SUGG_FOUND','AR_AA_NO_MATCH')
882: WHERE cri.receipt_reference_status = 'AR_AM_NEW';
883:
884: UPDATE ar_cash_remit_refs crr
885: SET crr.receipt_reference_status = (SELECT cri.receipt_reference_status
886: FROM ar_cash_remit_refs_interim cri
887: WHERE crr.remit_reference_id = cri.remit_reference_id
888: AND cri.receipt_reference_status IN ('AR_AA_SUGG_FOUND', 'AR_AA_NO_MATCH', 'AR_AA_RULE_SET_INACTIVE', 'AR_AA_RULE_SET_NOT_PASSED', 'AR_AA_AMT_NOT_PASSED'))
882: WHERE cri.receipt_reference_status = 'AR_AM_NEW';
883:
884: UPDATE ar_cash_remit_refs crr
885: SET crr.receipt_reference_status = (SELECT cri.receipt_reference_status
886: FROM ar_cash_remit_refs_interim cri
887: WHERE crr.remit_reference_id = cri.remit_reference_id
888: AND cri.receipt_reference_status IN ('AR_AA_SUGG_FOUND', 'AR_AA_NO_MATCH', 'AR_AA_RULE_SET_INACTIVE', 'AR_AA_RULE_SET_NOT_PASSED', 'AR_AA_AMT_NOT_PASSED'))
889: WHERE crr.remit_reference_id IN (SELECT cri.remit_reference_id
890: FROM ar_cash_remit_refs_interim cri
886: FROM ar_cash_remit_refs_interim cri
887: WHERE crr.remit_reference_id = cri.remit_reference_id
888: AND cri.receipt_reference_status IN ('AR_AA_SUGG_FOUND', 'AR_AA_NO_MATCH', 'AR_AA_RULE_SET_INACTIVE', 'AR_AA_RULE_SET_NOT_PASSED', 'AR_AA_AMT_NOT_PASSED'))
889: WHERE crr.remit_reference_id IN (SELECT cri.remit_reference_id
890: FROM ar_cash_remit_refs_interim cri
891: WHERE crr.remit_reference_id = cri.remit_reference_id
892: AND cri.receipt_reference_status IN ('AR_AA_SUGG_FOUND', 'AR_AA_NO_MATCH', 'AR_AA_RULE_SET_INACTIVE', 'AR_AA_RULE_SET_NOT_PASSED', 'AR_AA_AMT_NOT_PASSED'))
893: AND crr.receipt_reference_status <> 'AR_AA_INV_APPLIED';
894:
901: FROM ar_cash_auto_rule_sets
902: WHERE automatch_set_id = cr.automatch_set_id)
903: WHERE cash_receipt_id IN
904: (SELECT distinct cash_receipt_id
905: FROM ar_cash_remit_refs_interim cri
906: WHERE receipt_reference_status IN ('AR_AA_NO_MATCH','AR_AA_SUGG_FOUND')
907: );
908:
909: COMMIT;
962: , P_RETCODE OUT NOCOPY NUMBER
963: , p_worker_number IN NUMBER) IS
964: CURSOR auto_rule_set_cur(p_worker_number IN NUMBER) IS
965: SELECT distinct automatch_set_id
966: FROM AR_CASH_REMIT_REFS_INTERIM
967: WHERE worker_number = p_worker_number
968: AND receipt_reference_status = 'AR_AM_NEW';
969:
970: CURSOR auto_rule_cursor(p_automatch_set_id IN NUMBER) IS
1121: * RETURNS NONE *
1122: * ALGORITHM *
1123: * 1. For all open transactions satisfying all the setup conditions calculate
1124: * the matching score of transaction number with the reference number *
1125: * given in the remittance lines (ar_cash_remit_refs_all) *
1126: * 2. If match_score > suggested threshold value specified at the AutoMatch*
1127: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1128: * -tion. *
1129: * NOTES - *
1228: ref.remit_reference_id, ps.customer_trx_id
1229: ORDER BY ps.payment_schedule_id) AS reco_num
1230: FROM ar_cash_automatches am,
1231: ar_cash_automatch_rule_map amp,
1232: ar_cash_remit_refs_interim ref,
1233: ar_cash_receipts cr,
1234: ar_payment_schedules ps,
1235: ra_customer_trx trx,
1236: ra_cust_trx_types ctt,
1369: * RETURNS NONE *
1370: * ALGORITHM *
1371: * 1. For all open POs satisfying all the setup conditions calculate *
1372: * the matching score of purchase order number with the reference number*
1373: * given in the remittance lines (ar_cash_remit_refs_all) *
1374: * 2. If match_score > suggested threshold value specified at the AutoMatch*
1375: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1376: * -tion. *
1377: * NOTES - *
1475: ref.remit_reference_id, ps.customer_trx_id
1476: ORDER BY ps.payment_schedule_id) AS reco_num
1477: FROM ar_cash_automatches am,
1478: ar_cash_automatch_rule_map amp,
1479: ar_cash_remit_refs_interim ref,
1480: ar_cash_receipts cr,
1481: ra_customer_trx trx,
1482: ar_payment_schedules ps,
1483: ra_cust_trx_types ctt,
1609: * RETURNS NONE *
1610: * ALGORITHM *
1611: * 1. For all open SOs satisfying all the setup conditions calculate *
1612: * the matching score of sales order number with the reference number *
1613: * given in the remittance lines (ar_cash_remit_refs_all) *
1614: * 2. If match_score > suggested threshold value specified at the AutoMatch*
1615: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1616: * -tion. *
1617: * NOTES - *
1716: ref.remit_reference_id, ps.customer_trx_id
1717: ORDER BY ps.payment_schedule_id) AS reco_num
1718: FROM ar_cash_automatches am,
1719: ar_cash_automatch_rule_map amp,
1720: ar_cash_remit_refs_interim ref,
1721: ar_cash_receipts cr,
1722: ra_customer_trx_lines lin,
1723: ar_payment_schedules ps,
1724: ra_customer_trx trx,
1853: * RETURNS NONE *
1854: * ALGORITHM *
1855: * 1. For all open contracts satisfying all the setup conditions calculate *
1856: * the matching score of contract number with the reference number *
1857: * given in the remittance lines (ar_cash_remit_refs_all) *
1858: * 2. If match_score > suggested threshold value specified at the AutoMatch*
1859: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1860: * -tion. *
1861: * NOTES - *
1960: ref.remit_reference_id, ps.customer_trx_id
1961: ORDER BY ps.payment_schedule_id) AS reco_num
1962: FROM ar_cash_automatches am,
1963: ar_cash_automatch_rule_map amp,
1964: ar_cash_remit_refs_interim ref,
1965: ar_cash_receipts cr,
1966: ra_customer_trx_lines lin,
1967: ar_payment_schedules ps,
1968: ra_customer_trx trx,
2100: * RETURNS NONE *
2101: * ALGORITHM *
2102: * 1. For all open transactions satisfying all the setup conditions calculate
2103: * the matching score of header attribute value with the reference number
2104: * given in the remittance lines (ar_cash_remit_refs_all) *
2105: * 2. If match_score > suggested threshold value specified at the AutoMatch*
2106: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2107: * -tion. *
2108: * NOTES - *
2207: ref.remit_reference_id, ps.customer_trx_id
2208: ORDER BY ps.payment_schedule_id) AS reco_num
2209: FROM ar_cash_automatches am,
2210: ar_cash_automatch_rule_map amp,
2211: ar_cash_remit_refs_interim ref,
2212: ar_cash_receipts cr,
2213: ra_customer_trx trx,
2214: ar_payment_schedules ps,
2215: ra_cust_trx_types ctt,
2364: * RETURNS NONE *
2365: * ALGORITHM *
2366: * 1. For all open way bills satisfying all the setup conditions calculate *
2367: * the matching score of way bill number with the reference number *
2368: * given in the remittance lines (ar_cash_remit_refs_all) *
2369: * 2. If match_score > suggested threshold value specified at the AutoMatch*
2370: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2371: * -tion. *
2372: * NOTES - *
2468: ref.remit_reference_id, ps.customer_trx_id
2469: ORDER BY ps.payment_schedule_id) AS reco_num
2470: FROM ar_cash_automatches am,
2471: ar_cash_automatch_rule_map amp,
2472: ar_cash_remit_refs_interim ref,
2473: ar_cash_receipts cr,
2474: ra_customer_trx trx,
2475: ar_payment_schedules ps,
2476: ra_cust_trx_types ctt,
2601: * RETURNS NONE *
2602: * ALGORITHM *
2603: * 1. For all open bfbs satisfying all the setup conditions calculate *
2604: * the matching score of bfb number with the reference number *
2605: * given in the remittance lines (ar_cash_remit_refs_all) *
2606: * 2. If match_score > suggested threshold value specified at the AutoMatch*
2607: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2608: * -tion. *
2609: * NOTES - *
2689: RANK() OVER (PARTITION BY ci.cons_billing_number, ci.site_use_id, ref.remit_reference_id
2690: ORDER BY ps.due_date, ps.payment_schedule_id) AS reco_num
2691: FROM ar_cash_automatches am,
2692: ar_cash_automatch_rule_map amp,
2693: ar_cash_remit_refs_interim ref,
2694: ar_cash_receipts cr,
2695: ar_cons_inv ci,
2696: ar_payment_schedules ps,
2697: ra_customer_trx trx,
2828: * RETURNS NONE *
2829: * ALGORITHM *
2830: * 1. For all open transactions satisfying all the setup conditions calculate
2831: * the matching score of trx reference number with the reference number *
2832: * given in the remittance lines (ar_cash_remit_refs_all) *
2833: * 2. If match_score > suggested threshold value specified at the AutoMatch*
2834: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2835: * -tion. *
2836: * NOTES - *
2935: ref.remit_reference_id, ps.customer_trx_id
2936: ORDER BY ps.payment_schedule_id) AS reco_num
2937: FROM ar_cash_automatches am,
2938: ar_cash_automatch_rule_map amp,
2939: ar_cash_remit_refs_interim ref,
2940: ar_cash_receipts cr,
2941: ar_payment_schedules ps,
2942: ra_customer_trx trx,
2943: ra_cust_trx_types ctt,
3129: WHERE rec.request_id = p_req_id
3130: AND match_reason_code = 'AR_AM_INV_THRESHOLD'
3131: AND EXISTS ( SELECT 'Date Not Matching'
3132: FROM ar_cash_automatches am,
3133: ar_cash_remit_refs_interim ref
3134: WHERE am.automatch_id = rec.automatch_id
3135: AND ref.remit_reference_id = rec.remit_reference_id
3136: AND am.use_matching_date = 'ALWAYS'
3137: AND trunc(rec.resolved_matching_date) <> NVL(ref.matching_reference_date, to_date('31/12/4712','DD/MM/YYYY'))
3160: AND match_reason_code = 'AR_AM_INV_THRESHOLD'
3161: AND EXISTS ( SELECT ref.remit_reference_id
3162: FROM ar_cash_reco_lines lin,
3163: ar_cash_automatches am,
3164: ar_cash_remit_refs_interim ref
3165: WHERE lin.request_id = p_req_id
3166: AND am.automatch_id = rec.automatch_id
3167: AND am.use_matching_amount = 'ALWAYS'
3168: AND rec.recommendation_id = lin.recommendation_id
3191: UPDATE ar_cash_reco_lines l
3192: SET recommendation_reason = 'AR_AA_CURR_NO_MATCH'
3193: WHERE EXISTS (SELECT 'Inconsistent Currency'
3194: FROM ar_cash_recos rec,
3195: ar_cash_remit_refs_interim ref,
3196: ar_payment_schedules ps
3197: WHERE rec.recommendation_id = l.recommendation_id
3198: AND ref.remit_reference_id = rec.remit_reference_id
3199: AND ref.worker_number = p_worker_number
3269: UPDATE ar_cash_reco_lines l
3270: SET recommendation_reason = 'AR_AA_MUL_APP_TRX'
3271: WHERE ( EXISTS (SELECT 'PS already Applied'
3272: FROM ar_cash_recos rec,
3273: ar_cash_remit_refs_interim ref,
3274: ar_receivable_applications ra
3275: WHERE rec.recommendation_id = l.recommendation_id
3276: AND ref.remit_reference_id = rec.remit_reference_id
3277: AND ra.cash_receipt_id = ref.cash_receipt_id
3343: )
3344: AND trunc(rec1.resolved_matching_date) = (SELECT decode(am.use_matching_date,
3345: 'DUPLICATE', nvl(ref.matching_reference_date, rec1.resolved_matching_date),
3346: rec1.resolved_matching_date)
3347: FROM ar_cash_remit_refs_interim ref,
3348: ar_cash_automatches am
3349: WHERE ref.worker_number = p_worker_number
3350: AND ref.remit_reference_id = rec1.remit_reference_id
3351: AND am.automatch_id = rec1.automatch_id)
3351: AND am.automatch_id = rec1.automatch_id)
3352: AND EXISTS ( SELECT ref.remit_reference_id
3353: FROM ar_cash_reco_lines lin,
3354: ar_cash_automatches am,
3355: ar_cash_remit_refs_interim ref
3356: WHERE lin.request_id = p_req_id
3357: AND am.automatch_id = rec.automatch_id
3358: AND am.use_matching_amount = 'DUPLICATE'
3359: AND rec.recommendation_id = lin.recommendation_id
3406: am.use_matching_date,
3407: am.use_matching_amount
3408: FROM ar_cash_recos rec,
3409: ar_cash_reco_lines lin,
3410: ar_cash_remit_refs_interim ref,
3411: ar_cash_automatches am,
3412: ar_payment_schedules ps
3413: WHERE rec.request_id = p_req_id
3414: AND rec.match_reason_code = 'AR_AA_DUPLICATE_RECOS'
3499: --Check if customer can be uniquely identified if not yet identified
3500: UPDATE ar_cash_recos rec
3501: SET match_reason_code = 'AR_AA_CUST_NOT_UNIQUE'
3502: WHERE remit_reference_id IN (SELECT remit_reference_id
3503: FROM ar_cash_remit_refs_interim ref1
3504: WHERE cash_receipt_id IN (
3505: SELECT cr.cash_receipt_id
3506: FROM ar_cash_receipts cr,
3507: ar_cash_remit_refs_interim ref,
3503: FROM ar_cash_remit_refs_interim ref1
3504: WHERE cash_receipt_id IN (
3505: SELECT cr.cash_receipt_id
3506: FROM ar_cash_receipts cr,
3507: ar_cash_remit_refs_interim ref,
3508: ar_cash_recos rec,
3509: ar_cash_reco_lines recl
3510: WHERE cr.autoapply_flag = 'Y'
3511: AND cr.pay_from_customer IS NULL
3514: AND ref.worker_number = p_worker_number
3515: AND recl.recommendation_id = rec.recommendation_id
3516: AND recl.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3517: AND EXISTS (SELECT 'Reco of Different Customer'
3518: FROM ar_cash_remit_refs_interim ref2,
3519: ar_cash_recos rec1,
3520: ar_cash_reco_lines recl1
3521: WHERE ref2.cash_receipt_id = ref.cash_receipt_id
3522: AND rec1.remit_reference_id = ref2.remit_reference_id
3588: UPDATE ar_cash_recos rec
3589: SET match_reason_code = 'AR_AA_REMIT_EXCEEDED'
3590: WHERE remit_reference_id IN
3591: (SELECT remit_reference_id
3592: FROM ar_cash_remit_refs_interim
3593: WHERE cash_receipt_id IN (
3594: SELECT ps.cash_receipt_id
3595: FROM ar_payment_schedules ps,
3596: ar_cash_receipts cr,
3593: WHERE cash_receipt_id IN (
3594: SELECT ps.cash_receipt_id
3595: FROM ar_payment_schedules ps,
3596: ar_cash_receipts cr,
3597: ar_cash_remit_refs_interim ref,
3598: ar_cash_recos rec,
3599: ar_cash_reco_lines recl
3600: WHERE ps.cash_receipt_id = cr.cash_receipt_id
3601: AND ps.cash_receipt_id = ref.cash_receipt_id
3630:
3631: DECLARE
3632: CURSOR unid_receipts IS
3633: SELECT distinct cash_receipt_id
3634: FROM ar_cash_remit_refs_interim
3635: WHERE worker_number = p_worker_number
3636: AND customer_id IS NULL;
3637:
3638: l_cash_receipt_id AR_CASH_REMIT_REFS_INTERIM.cash_receipt_id%TYPE;
3634: FROM ar_cash_remit_refs_interim
3635: WHERE worker_number = p_worker_number
3636: AND customer_id IS NULL;
3637:
3638: l_cash_receipt_id AR_CASH_REMIT_REFS_INTERIM.cash_receipt_id%TYPE;
3639: l_customer_id AR_CASH_RECOS.pay_from_customer%TYPE;
3640: v_msg_count NUMBER(4);
3641: v_msg_data VARCHAR2(1000);
3642: v_return_status VARCHAR2(5);
3649: 1, max(rec.pay_from_customer),
3650: NULL)
3651: INTO l_customer_id
3652: FROM ar_cash_recos rec,
3653: ar_cash_remit_refs_interim ref
3654: WHERE rec.request_id = p_req_id
3655: AND ref.cash_receipt_id = l_cash_receipt_id
3656: AND rec.remit_reference_id = ref.remit_reference_id
3657: AND ref.worker_number = p_worker_number
3675: IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3676: UPDATE ar_cash_recos
3677: SET match_reason_code = 'AR_AA_CUST_UNID'
3678: WHERE remit_reference_id IN (SELECT ref1.remit_reference_id
3679: FROM ar_cash_remit_refs_interim ref1
3680: WHERE ref1.cash_receipt_id = l_cash_receipt_id)
3681: AND match_reason_code = 'AR_AM_INV_THRESHOLD'
3682: AND request_id = p_req_id;
3683: END IF;
3785: lin.recommendation_id,
3786: lin.line_number,
3787: lin.receipt_currency_code,
3788: rec.resolved_match_currency
3789: FROM ar_cash_remit_refs_interim ref,
3790: ar_cash_recos rec,
3791: ar_cash_reco_lines lin
3792: WHERE rec.remit_reference_id = p_remit_reference_id
3793: AND ref.remit_reference_id = rec.remit_reference_id
3806: ref.amount_applied_from,
3807: ref.cash_receipt_id,
3808: cr.currency_code
3809: FROM ar_cash_recos rec,
3810: ar_cash_remit_refs_interim ref,
3811: ar_cash_receipts cr
3812: WHERE rec.request_id = p_req_id
3813: AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
3814: AND rec.match_resolved_using = 'BALANCE FORWARD BILL'
3980: END IF;
3981: END LOOP;
3982: END LOOP;
3983:
3984: UPDATE ar_cash_remit_refs ref
3985: SET (receipt_reference_status,
3986: resolved_matching_number,
3987: auto_applied,
3988: match_score_value,
4016: END IF;
4017:
4018: DELETE FROM ar_cash_reco_lines lin
4019: WHERE EXISTS (SELECT 'Delete Recos'
4020: FROM ar_cash_remit_refs ref,
4021: ar_cash_recos rec
4022: WHERE ref.receipt_reference_status = 'AR_AA_INV_APPLIED'
4023: AND lin.recommendation_id = rec.recommendation_id
4024: AND rec.remit_reference_id = ref.remit_reference_id)
4025: AND request_id = p_req_id;
4026:
4027: DELETE FROM ar_cash_recos rec
4028: WHERE EXISTS (SELECT 'Delete Recos'
4029: FROM ar_cash_remit_refs ref
4030: WHERE ref.receipt_reference_status = 'AR_AA_INV_APPLIED'
4031: AND rec.remit_reference_id = ref.remit_reference_id)
4032: AND request_id = p_req_id
4033: AND recommendation_type = 'TRX';
4106: l_recommendation_id NUMBER;
4107: l_recommendation_reason VARCHAR2(30);
4108: l_use_matching_date AR_CASH_AUTOMATCHES.use_matching_date%TYPE;
4109: l_use_matching_amount AR_CASH_AUTOMATCHES.use_matching_amount%TYPE;
4110: l_ref_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE;
4111: l_ref_amount_applied_from AR_CASH_REMIT_REFS.amount_applied_from%TYPE;
4112: l_ref_orig_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4113: l_ref_rem_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4114: l_trans_to_receipt_rate AR_CASH_REMIT_REFS.trans_to_receipt_rate%TYPE;
4107: l_recommendation_reason VARCHAR2(30);
4108: l_use_matching_date AR_CASH_AUTOMATCHES.use_matching_date%TYPE;
4109: l_use_matching_amount AR_CASH_AUTOMATCHES.use_matching_amount%TYPE;
4110: l_ref_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE;
4111: l_ref_amount_applied_from AR_CASH_REMIT_REFS.amount_applied_from%TYPE;
4112: l_ref_orig_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4113: l_ref_rem_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4114: l_trans_to_receipt_rate AR_CASH_REMIT_REFS.trans_to_receipt_rate%TYPE;
4115: l_res_matching_date AR_PAYMENT_SCHEDULES.trx_date%TYPE;
4108: l_use_matching_date AR_CASH_AUTOMATCHES.use_matching_date%TYPE;
4109: l_use_matching_amount AR_CASH_AUTOMATCHES.use_matching_amount%TYPE;
4110: l_ref_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE;
4111: l_ref_amount_applied_from AR_CASH_REMIT_REFS.amount_applied_from%TYPE;
4112: l_ref_orig_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4113: l_ref_rem_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4114: l_trans_to_receipt_rate AR_CASH_REMIT_REFS.trans_to_receipt_rate%TYPE;
4115: l_res_matching_date AR_PAYMENT_SCHEDULES.trx_date%TYPE;
4116: l_match_score_value AR_CASH_RECOS.match_score_value%TYPE;
4109: l_use_matching_amount AR_CASH_AUTOMATCHES.use_matching_amount%TYPE;
4110: l_ref_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE;
4111: l_ref_amount_applied_from AR_CASH_REMIT_REFS.amount_applied_from%TYPE;
4112: l_ref_orig_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4113: l_ref_rem_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4114: l_trans_to_receipt_rate AR_CASH_REMIT_REFS.trans_to_receipt_rate%TYPE;
4115: l_res_matching_date AR_PAYMENT_SCHEDULES.trx_date%TYPE;
4116: l_match_score_value AR_CASH_RECOS.match_score_value%TYPE;
4117: l_receipt_currency_code AR_CASH_RECEIPTS.currency_code%TYPE;
4110: l_ref_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE;
4111: l_ref_amount_applied_from AR_CASH_REMIT_REFS.amount_applied_from%TYPE;
4112: l_ref_orig_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4113: l_ref_rem_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4114: l_trans_to_receipt_rate AR_CASH_REMIT_REFS.trans_to_receipt_rate%TYPE;
4115: l_res_matching_date AR_PAYMENT_SCHEDULES.trx_date%TYPE;
4116: l_match_score_value AR_CASH_RECOS.match_score_value%TYPE;
4117: l_receipt_currency_code AR_CASH_RECEIPTS.currency_code%TYPE;
4118: l_resolved_match_currency AR_PAYMENT_SCHEDULES.invoice_currency_code%TYPE;
4115: l_res_matching_date AR_PAYMENT_SCHEDULES.trx_date%TYPE;
4116: l_match_score_value AR_CASH_RECOS.match_score_value%TYPE;
4117: l_receipt_currency_code AR_CASH_RECEIPTS.currency_code%TYPE;
4118: l_resolved_match_currency AR_PAYMENT_SCHEDULES.invoice_currency_code%TYPE;
4119: l_trx_amt_due_rem AR_CASH_REMIT_REFS.amount_applied%TYPE := 0;
4120: l_discount_taken AR_CASH_RECO_LINES.discount_taken_earned%TYPE := 0;
4121: l_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE;
4122: l_out_amount_to_apply AR_CASH_REMIT_REFS.amount_applied%TYPE;
4123: l_out_discount_to_take AR_CASH_REMIT_REFS.amount_applied%TYPE;
4117: l_receipt_currency_code AR_CASH_RECEIPTS.currency_code%TYPE;
4118: l_resolved_match_currency AR_PAYMENT_SCHEDULES.invoice_currency_code%TYPE;
4119: l_trx_amt_due_rem AR_CASH_REMIT_REFS.amount_applied%TYPE := 0;
4120: l_discount_taken AR_CASH_RECO_LINES.discount_taken_earned%TYPE := 0;
4121: l_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE;
4122: l_out_amount_to_apply AR_CASH_REMIT_REFS.amount_applied%TYPE;
4123: l_out_discount_to_take AR_CASH_REMIT_REFS.amount_applied%TYPE;
4124: l_valid VARCHAR2(1);
4125: BEGIN
4118: l_resolved_match_currency AR_PAYMENT_SCHEDULES.invoice_currency_code%TYPE;
4119: l_trx_amt_due_rem AR_CASH_REMIT_REFS.amount_applied%TYPE := 0;
4120: l_discount_taken AR_CASH_RECO_LINES.discount_taken_earned%TYPE := 0;
4121: l_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE;
4122: l_out_amount_to_apply AR_CASH_REMIT_REFS.amount_applied%TYPE;
4123: l_out_discount_to_take AR_CASH_REMIT_REFS.amount_applied%TYPE;
4124: l_valid VARCHAR2(1);
4125: BEGIN
4126: IF (PG_DEBUG IN ('Y', 'C')) THEN
4119: l_trx_amt_due_rem AR_CASH_REMIT_REFS.amount_applied%TYPE := 0;
4120: l_discount_taken AR_CASH_RECO_LINES.discount_taken_earned%TYPE := 0;
4121: l_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE;
4122: l_out_amount_to_apply AR_CASH_REMIT_REFS.amount_applied%TYPE;
4123: l_out_discount_to_take AR_CASH_REMIT_REFS.amount_applied%TYPE;
4124: l_valid VARCHAR2(1);
4125: BEGIN
4126: IF (PG_DEBUG IN ('Y', 'C')) THEN
4127: log('process_single_reco()+');
4482: log(SQLERRM);
4483: RAISE;
4484: END insert_recos;
4485:
4486: FUNCTION get_cross_curr_rate(p_amount_applied IN ar_cash_remit_refs.amount_applied%TYPE
4487: , p_amount_applied_from IN ar_cash_remit_refs.amount_applied_from%TYPE
4488: , p_inv_curr_code IN ar_payment_schedules.invoice_currency_code%TYPE
4489: , p_rec_curr_code IN ar_cash_receipts.currency_code%TYPE)
4490: RETURN NUMBER IS
4483: RAISE;
4484: END insert_recos;
4485:
4486: FUNCTION get_cross_curr_rate(p_amount_applied IN ar_cash_remit_refs.amount_applied%TYPE
4487: , p_amount_applied_from IN ar_cash_remit_refs.amount_applied_from%TYPE
4488: , p_inv_curr_code IN ar_payment_schedules.invoice_currency_code%TYPE
4489: , p_rec_curr_code IN ar_cash_receipts.currency_code%TYPE)
4490: RETURN NUMBER IS
4491: l_cross_curr_rate NUMBER;
4488: , p_inv_curr_code IN ar_payment_schedules.invoice_currency_code%TYPE
4489: , p_rec_curr_code IN ar_cash_receipts.currency_code%TYPE)
4490: RETURN NUMBER IS
4491: l_cross_curr_rate NUMBER;
4492: l_amount_applied ar_cash_remit_refs.amount_applied%TYPE;
4493: l_amount_applied_from ar_cash_remit_refs.amount_applied_from%TYPE;
4494: l_inv_curr_code ar_payment_schedules.invoice_currency_code%TYPE;
4495: l_rec_curr_code ar_cash_receipts.currency_code%TYPE;
4496: BEGIN
4489: , p_rec_curr_code IN ar_cash_receipts.currency_code%TYPE)
4490: RETURN NUMBER IS
4491: l_cross_curr_rate NUMBER;
4492: l_amount_applied ar_cash_remit_refs.amount_applied%TYPE;
4493: l_amount_applied_from ar_cash_remit_refs.amount_applied_from%TYPE;
4494: l_inv_curr_code ar_payment_schedules.invoice_currency_code%TYPE;
4495: l_rec_curr_code ar_cash_receipts.currency_code%TYPE;
4496: BEGIN
4497: l_amount_applied := NVL(p_amount_applied, 0);
4518: , p_terms_sequence_number IN AR_PAYMENT_SCHEDULES.terms_sequence_number%TYPE
4519: , p_trx_date IN AR_PAYMENT_SCHEDULES.trx_date%TYPE
4520: , p_allow_overapp_flag IN RA_CUST_TRX_TYPES.allow_overapplication_flag%TYPE
4521: , p_partial_discount_flag IN RA_TERMS.partial_discount_flag%TYPE
4522: , p_input_amount IN AR_CASH_REMIT_REFS.amount_applied%TYPE
4523: , p_amount_due_original IN AR_PAYMENT_SCHEDULES.amount_due_original%TYPE
4524: , p_amount_due_remaining IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE
4525: , p_discount_taken_earned IN AR_PAYMENT_SCHEDULES.discount_taken_earned%TYPE
4526: , p_discount_taken_unearned IN AR_PAYMENT_SCHEDULES.discount_taken_unearned%TYPE
4535: l_terms_sequence_number AR_PAYMENT_SCHEDULES.terms_sequence_number%TYPE;
4536: l_trx_date AR_PAYMENT_SCHEDULES.trx_date%TYPE;
4537: l_allow_overapp_flag RA_CUST_TRX_TYPES.allow_overapplication_flag%TYPE;
4538: l_partial_discount_flag RA_TERMS.partial_discount_flag%TYPE;
4539: l_input_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4540: l_amount_due_original AR_PAYMENT_SCHEDULES.amount_due_original%TYPE;
4541: l_amount_due_remaining AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
4542: l_discount_taken_earned AR_PAYMENT_SCHEDULES.discount_taken_earned%TYPE;
4543: l_discount_taken_unearned AR_PAYMENT_SCHEDULES.discount_taken_unearned%TYPE;
4729: /*===========================================================================+
4730: * PROCEDURE *
4731: * DELETE_INTERIM_RECORDS() *
4732: * DESCRIPTION *
4733: * Delete records from ar_cash_remit_refs_interim. *
4734: * SCOPE - LOCAL *
4735: * ARGUMENTS *
4736: * IN : None *
4737: * OUT : None *
4737: * OUT : None *
4738: * *
4739: * RETURNS NONE *
4740: * ALGORITHM *
4741: * 1. Delete records from ar_cash_remit_refs_interim. *
4742: * NOTES - *
4743: * 1. This is called from the XML report *
4744: * *
4745: * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
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 );
4756: END IF;
4757: IF (PG_DEBUG IN ('Y', 'C')) THEN