[Home] [Help]
199: /*===========================================================================+
200: * FUNCTION *
201: * GET_NEXT_RECO_ID() *
202: * DESCRIPTION *
203: * Generates the recommendation id from sequence ar_cash_recos_s *
204: * SCOPE - LOCAL *
205: * ARGUMENTS *
206: * IN : p_reco_num - Recommendation Number *
207: * RETURNS NUMBER *
218: RETURN NUMBER IS
219: l_reco_id NUMBER;
220: BEGIN
221: IF p_reco_num = 1 AND NVL(g_prev_reco_num, -1) <> 1 THEN
222: SELECT ar_cash_recos_s.nextval
223: INTO l_reco_id
224: FROM DUAL;
225: g_next_reco_id := l_reco_id;
226: g_prev_reco_num := p_reco_num;
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: );
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
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
881: AND rownum = 1 ),'MATCH_FOUND','AR_AA_SUGG_FOUND','AR_AA_NO_MATCH')
882: WHERE cri.receipt_reference_status = 'AR_AM_NEW';
883:
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 - *
1130: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1131: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1127: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1128: * -tion. *
1129: * NOTES - *
1130: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1131: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1132: * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
1133: * unidentified then transactions for all the customers are considered. *
1134: * Otherwise only the transactions related to the paying customer of the*
1135: * receipt are considered. *
1134: * Otherwise only the transactions related to the paying customer of the*
1135: * receipt are considered. *
1136: * 3. An invoice can have multiple installments; which means there is a *
1137: * possibility that the receipt is applied against multiple payment *
1138: * schedules for the same transaction. ar_cash_recos contains header *
1139: * level information like resolved number(trx number), trx date etc., *
1140: * where as ar_cash_reco_lines contains the sepecific ps information for*
1141: * the resolved transaction. *
1142: * *
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 - *
1378: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1379: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1375: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1376: * -tion. *
1377: * NOTES - *
1378: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1379: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1380: * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
1381: * unidentified then transactions for all the customers are considered. *
1382: * Otherwise only the transactions related to the paying customer of the*
1383: * receipt are considered. *
1382: * Otherwise only the transactions related to the paying customer of the*
1383: * receipt are considered. *
1384: * 3. A PO can have multiple invoices; which means there is a possibility *
1385: * that the receipt is applied against multiple payment schedules for *
1386: * the same transaction. ar_cash_recos contains header level information*
1387: * level information like resolved number(purchase order)etc., *
1388: * where as ar_cash_reco_lines contains the sepecific ps information for*
1389: * the resolved transaction. *
1390: * *
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 - *
1618: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1619: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1615: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1616: * -tion. *
1617: * NOTES - *
1618: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1619: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1620: * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
1621: * unidentified then transactions for all the customers are considered. *
1622: * Otherwise only the transactions related to the paying customer of the*
1623: * receipt are considered. *
1622: * Otherwise only the transactions related to the paying customer of the*
1623: * receipt are considered. *
1624: * 3. A SO can have multiple invoices; which means there is a possibility *
1625: * that the receipt is applied against multiple payment schedules for *
1626: * the same transaction. ar_cash_recos contains header level information*
1627: * level information like resolved number(sales order number)etc., *
1628: * where as ar_cash_reco_lines contains the sepecific ps information for*
1629: * the resolved transaction. *
1630: * *
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 - *
1862: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1863: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1859: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1860: * -tion. *
1861: * NOTES - *
1862: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1863: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1864: * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
1865: * unidentified then transactions for all the customers are considered. *
1866: * Otherwise only the transactions related to the paying customer of the*
1867: * receipt are considered. *
1866: * Otherwise only the transactions related to the paying customer of the*
1867: * receipt are considered. *
1868: * 3. A Contract can have multiple invoices;which means there is a possibility
1869: * that the receipt is applied against multiple payment schedules for *
1870: * the same transaction. ar_cash_recos contains header level information*
1871: * level information like resolved number(contract number)etc., *
1872: * where as ar_cash_reco_lines contains the sepecific ps information for*
1873: * the resolved transaction. *
1874: * *
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 - *
2109: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2110: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2106: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2107: * -tion. *
2108: * NOTES - *
2109: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2110: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2111: * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
2112: * unidentified then transactions for all the customers are considered. *
2113: * Otherwise only the transactions related to the paying customer of the*
2114: * receipt are considered. *
2113: * Otherwise only the transactions related to the paying customer of the*
2114: * receipt are considered. *
2115: * 3. An invoice can have multiple installments; which means there is a *
2116: * possibility that the receipt is applied against multiple payment *
2117: * schedules for the same transaction. ar_cash_recos contains header *
2118: * level information like resolved number(trx number), trx date etc., *
2119: * where as ar_cash_reco_lines contains the sepecific ps information for*
2120: * the resolved transaction. *
2121: * *
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 - *
2373: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2374: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2370: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2371: * -tion. *
2372: * NOTES - *
2373: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2374: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2375: * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
2376: * unidentified then transactions for all the customers are considered. *
2377: * Otherwise only the transactions related to the paying customer of the*
2378: * receipt are considered. *
2375: * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
2376: * unidentified then transactions for all the customers are considered. *
2377: * Otherwise only the transactions related to the paying customer of the*
2378: * receipt are considered. *
2379: * 3. ar_cash_recos contains header level information like resolved *
2380: * number(way bill number)etc., where as ar_cash_reco_lines contains the*
2381: * sepecific ps information for the resolved transaction. *
2382: * *
2383: * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
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 - *
2610: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2611: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2607: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2608: * -tion. *
2609: * NOTES - *
2610: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2611: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2612: * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
2613: * unidentified then transactions for all the customers are considered. *
2614: * Otherwise only the transactions related to the paying customer of the*
2615: * receipt are considered. *
2614: * Otherwise only the transactions related to the paying customer of the*
2615: * receipt are considered. *
2616: * 3. A bfb can have multiple invoices; which means there is a possibility *
2617: * that the receipt is applied against multiple payment schedules for *
2618: * the same transaction. ar_cash_recos contains header level information*
2619: * level information like resolved number(bfb number)etc., *
2620: * where as ar_cash_reco_lines contains the sepecific ps information for*
2621: * the resolved transaction. *
2622: * *
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 - *
2837: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2838: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2834: * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2835: * -tion. *
2836: * NOTES - *
2837: * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2838: * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2839: * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
2840: * unidentified then transactions for all the customers are considered. *
2841: * Otherwise only the transactions related to the paying customer of the*
2842: * receipt are considered. *
2841: * Otherwise only the transactions related to the paying customer of the*
2842: * receipt are considered. *
2843: * 3. An invoice can have multiple installments; which means there is a *
2844: * possibility that the receipt is applied against multiple payment *
2845: * schedules for the same transaction. ar_cash_recos contains header *
2846: * level information like resolved number(trx number), trx date etc., *
2847: * where as ar_cash_reco_lines contains the sepecific ps information for*
2848: * the resolved transaction. *
2849: * *
3123: * reference matching date provided by the user. If the date is not*
3124: * provided in 'Remittance Lines', then it will be treated as a mis*
3125: * match. * */
3126:
3127: UPDATE ar_cash_recos rec
3128: SET rec.match_reason_code = 'AR_AA_DATE_MISMATCH'
3129: WHERE rec.request_id = p_req_id
3130: AND match_reason_code = 'AR_AM_INV_THRESHOLD'
3131: AND EXISTS ( SELECT 'Date Not Matching'
3142:
3143: UPDATE ar_cash_reco_lines l
3144: SET recommendation_reason = 'AR_AA_DATE_MISMATCH'
3145: WHERE recommendation_id IN (SELECT recommendation_id
3146: FROM ar_cash_recos r
3147: WHERE match_reason_code = 'AR_AA_DATE_MISMATCH'
3148: AND request_id = l.request_id)
3149: AND request_id = p_req_id;
3150:
3153: END IF;
3154:
3155: /* * If Use_Matching_Amount is set to 'Yes', then the transaction *
3156: * balance must equal the amount applied of the remittance line * */
3157: /*UPDATE ar_cash_recos rec
3158: SET rec.match_reason_code = 'AR_AA_AMOUNT_MISMATCH'
3159: WHERE rec.request_id = p_req_id
3160: AND match_reason_code = 'AR_AM_INV_THRESHOLD'
3161: AND EXISTS ( SELECT ref.remit_reference_id
3177:
3178: UPDATE ar_cash_reco_lines l
3179: SET recommendation_reason = 'AR_AA_AMOUNT_MISMATCH'
3180: WHERE recommendation_id IN (SELECT recommendation_id
3181: FROM ar_cash_recos r
3182: WHERE match_reason_code = 'AR_AA_AMOUNT_MISMATCH'
3183: AND request_id = l.request_id)
3184: AND request_id = p_req_id;
3185:
3190: --Do not apply the transaction if the currency provided is different to that of the transaction
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
3268: --Prevent same PS applied twice to the same receipt
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
3292: /* UPDATE ar_cash_reco_lines l
3293: SET recommendation_reason = 'AR_AA_MUL_RECO_TRX'
3294: WHERE EXISTS (SELECT 'PS eligible for more than one reference'
3295: FROM ar_cash_reco_lines l1,
3296: ar_cash_recos rec,
3297: ar_cash_recos rec1
3298: WHERE l.payment_schedule_id = l1.payment_schedule_id
3299: AND l.recommendation_id <> l1.recommendation_id
3300: AND rec.recommendation_id = l.recommendation_id
3293: SET recommendation_reason = 'AR_AA_MUL_RECO_TRX'
3294: WHERE EXISTS (SELECT 'PS eligible for more than one reference'
3295: FROM ar_cash_reco_lines l1,
3296: ar_cash_recos rec,
3297: ar_cash_recos rec1
3298: WHERE l.payment_schedule_id = l1.payment_schedule_id
3299: AND l.recommendation_id <> l1.recommendation_id
3300: AND rec.recommendation_id = l.recommendation_id
3301: AND rec1.recommendation_id = l1.recommendation_id
3308: IF (PG_DEBUG IN ('Y', 'C')) THEN
3309: log('No. of recos updated to Same Trx for multiple Recos: ' || SQL%ROWCOUNT );
3310: END IF; */
3311: /*
3312: UPDATE ar_cash_recos rec
3313: SET rec.match_reason_code = 'AR_AA_DUPLICATE_RECOS'
3314: WHERE rec.recommendation_id IN
3315: (SELECT recommendation_id
3316: FROM ar_cash_recos
3312: UPDATE ar_cash_recos rec
3313: SET rec.match_reason_code = 'AR_AA_DUPLICATE_RECOS'
3314: WHERE rec.recommendation_id IN
3315: (SELECT recommendation_id
3316: FROM ar_cash_recos
3317: WHERE request_id = p_req_id
3318: AND (resolved_matching_number, match_resolved_using, remit_reference_id) IN
3319: (
3320: SELECT resolved_matching_number, match_resolved_using, remit_reference_id
3317: WHERE request_id = p_req_id
3318: AND (resolved_matching_number, match_resolved_using, remit_reference_id) IN
3319: (
3320: SELECT resolved_matching_number, match_resolved_using, remit_reference_id
3321: FROM ar_cash_recos rec
3322: WHERE rec.request_id = p_req_id
3323: AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
3324: GROUP BY resolved_matching_number, match_resolved_using, remit_reference_id
3325: HAVING COUNT(*) > 1
3325: HAVING COUNT(*) > 1
3326: )
3327: MINUS
3328: SELECT recommendation_id
3329: FROM ar_cash_recos rec
3330: WHERE request_id = p_req_id
3331: AND (resolved_matching_number, match_resolved_using, remit_reference_id, resolved_matching_date) IN
3332: (
3333: SELECT resolved_matching_number, match_resolved_using, remit_reference_id, resolved_matching_date
3330: WHERE request_id = p_req_id
3331: AND (resolved_matching_number, match_resolved_using, remit_reference_id, resolved_matching_date) IN
3332: (
3333: SELECT resolved_matching_number, match_resolved_using, remit_reference_id, resolved_matching_date
3334: FROM ar_cash_recos rec1
3335: WHERE request_id = p_req_id
3336: AND (resolved_matching_number, match_resolved_using, remit_reference_id) IN
3337: (
3338: SELECT resolved_matching_number, match_resolved_using, remit_reference_id
3335: WHERE request_id = p_req_id
3336: AND (resolved_matching_number, match_resolved_using, remit_reference_id) IN
3337: (
3338: SELECT resolved_matching_number, match_resolved_using, remit_reference_id
3339: FROM ar_cash_recos rec
3340: WHERE rec.request_id = p_req_id
3341: GROUP BY resolved_matching_number, match_resolved_using, remit_reference_id
3342: HAVING COUNT(*) > 1
3343: )
3372: IF (PG_DEBUG IN ('Y', 'C')) THEN
3373: log('No. of recos updated to Duplicate Recos: ' || SQL%ROWCOUNT );
3374: END IF; */
3375:
3376: UPDATE ar_cash_recos rec
3377: SET rec.match_reason_code = 'AR_AA_DUPLICATE_RECOS'
3378: WHERE request_id = p_req_id
3379: AND (resolved_matching_number, match_resolved_using, remit_reference_id )
3380: IN ( SELECT resolved_matching_number,
3379: AND (resolved_matching_number, match_resolved_using, remit_reference_id )
3380: IN ( SELECT resolved_matching_number,
3381: match_resolved_using ,
3382: remit_reference_id
3383: FROM ar_cash_recos rec
3384: WHERE rec.request_id = p_req_id
3385: AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
3386: GROUP BY resolved_matching_number,
3387: match_resolved_using ,
3404: lin.customer_trx_id,
3405: lin.receipt_date,
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
3432: l_old_remit_reference_id NUMBER := -1;
3433: l_rec_count NUMBER;
3434: l_passed_amount BOOLEAN;
3435: l_passed_date BOOLEAN;
3436: TYPE l_rm_frm_dup_rec_tbl IS TABLE OF ar_cash_recos.recommendation_id%TYPE INDEX BY BINARY_INTEGER;
3437: l_rm_frm_dup_rec l_rm_frm_dup_rec_tbl;
3438: i NUMBER;
3439: l_discount NUMBER;
3440: BEGIN
3472: END IF;
3473: END LOOP;
3474:
3475: FORALL i IN 1..NVL(l_rm_frm_dup_rec.LAST, 0)
3476: UPDATE ar_cash_recos
3477: SET match_reason_code = 'AR_AM_INV_THRESHOLD'
3478: WHERE request_id = p_req_id
3479: AND match_reason_code = 'AR_AA_DUPLICATE_RECOS'
3480: AND recommendation_id = l_rm_frm_dup_rec(i);
3486:
3487: UPDATE ar_cash_reco_lines l
3488: SET recommendation_reason = 'AR_AA_DUPLICATE_RECOS'
3489: WHERE recommendation_id IN (SELECT recommendation_id
3490: FROM ar_cash_recos r
3491: WHERE match_reason_code = 'AR_AA_DUPLICATE_RECOS'
3492: AND request_id = l.request_id)
3493: AND request_id = p_req_id;
3494:
3496: log('No. of reco lines updated to Duplicate Recos: ' || SQL%ROWCOUNT );
3497: END IF;
3498:
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 (
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
3512: AND cr.cash_receipt_id = ref.cash_receipt_id
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
3523: AND recl1.recommendation_id = rec1.recommendation_id
3535:
3536: UPDATE ar_cash_reco_lines l
3537: SET recommendation_reason = 'AR_AA_CUST_NOT_UNIQUE'
3538: WHERE recommendation_id IN (SELECT recommendation_id
3539: FROM ar_cash_recos
3540: WHERE match_reason_code = 'AR_AA_CUST_NOT_UNIQUE'
3541: AND request_id = p_req_id)
3542: AND request_id = p_req_id
3543: AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3542: AND request_id = p_req_id
3543: AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3544:
3545: --Multiple recos for same reference
3546: UPDATE ar_cash_recos rec
3547: SET match_reason_code = 'AR_AA_MULT_RECOS'
3548: WHERE EXISTS (SELECT 'Multiple Recos'
3549: FROM ar_cash_recos rec1,
3550: ar_cash_reco_lines lin
3545: --Multiple recos for same reference
3546: UPDATE ar_cash_recos rec
3547: SET match_reason_code = 'AR_AA_MULT_RECOS'
3548: WHERE EXISTS (SELECT 'Multiple Recos'
3549: FROM ar_cash_recos rec1,
3550: ar_cash_reco_lines lin
3551: WHERE rec1.remit_reference_id = rec.remit_reference_id
3552: AND rec1.recommendation_id <> rec.recommendation_id
3553: AND lin.recommendation_id = rec1.recommendation_id
3573:
3574: UPDATE ar_cash_reco_lines l
3575: SET recommendation_reason = 'AR_AA_MULT_RECOS'
3576: WHERE EXISTS (SELECT 'Many Types of Recos'
3577: FROM ar_cash_recos rec
3578: WHERE l.recommendation_id = rec.recommendation_id
3579: AND rec.match_reason_code = 'AR_AA_MULT_RECOS'
3580: AND rec.request_id = p_req_id)
3581: AND recommendation_reason = 'AR_AM_INV_THRESHOLD'
3584: log('No. of reco lines updated to Multiple Recos: ' || SQL%ROWCOUNT );
3585: END IF;
3586:
3587: --Check if the allocated receipt amounts of all the valid recos exceed the receipt amount/balance
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
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
3602: AND ref.remit_reference_id = rec.remit_reference_id
3615:
3616: UPDATE ar_cash_reco_lines l
3617: SET recommendation_reason = 'AR_AA_REMIT_EXCEEDED'
3618: WHERE recommendation_id IN (SELECT recommendation_id
3619: FROM ar_cash_recos
3620: WHERE match_reason_code = 'AR_AA_REMIT_EXCEEDED'
3621: AND request_id = p_req_id)
3622: AND request_id = p_req_id
3623: AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
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);
3643: v_status VARCHAR2(100);
3648: SELECT decode(count(distinct rec.pay_from_customer),
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
3672: );
3673: log('Return Status '||v_return_status);
3674:
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)
3685: END LOOP;
3686: UPDATE ar_cash_reco_lines l
3687: SET recommendation_reason = 'AR_AA_CUST_UNID'
3688: WHERE recommendation_id IN (SELECT recommendation_id
3689: FROM ar_cash_recos
3690: WHERE match_reason_code = 'AR_AA_CUST_UNID'
3691: AND request_id = p_req_id)
3692: AND request_id = p_req_id
3693: AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3768: l_amount_applied_from BOOLEAN;
3769:
3770: CURSOR app_reco_cur IS
3771: SELECT distinct rec.remit_reference_id
3772: FROM ar_cash_recos rec
3773: WHERE rec.request_id = p_req_id
3774: AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
3775: AND rec.match_resolved_using <> 'BALANCE FORWARD BILL';
3776:
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
3794: AND rec.recommendation_id = lin.recommendation_id
3805: ref.amount_applied,
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'
3994: rec.match_score_value,
3995: rec.resolved_matching_date,
3996: rec.resolved_match_currency,
3997: rec.automatch_id
3998: FROM ar_cash_recos rec,
3999: ar_cash_reco_lines lin
4000: WHERE ref.remit_reference_id = rec.remit_reference_id
4001: AND lin.recommendation_id = rec.recommendation_id
4002: AND rec.request_id = p_req_id
4003: AND recommendation_type = 'TRX'
4004: AND lin.recommendation_reason = 'AR_AA_INV_LOCKED'
4005: AND rownum =1)
4006: WHERE EXISTS (SELECT 'Found Match'
4007: FROM ar_cash_recos rec,
4008: ar_cash_reco_lines lin
4009: WHERE ref.remit_reference_id = rec.remit_reference_id
4010: AND lin.recommendation_id = rec.recommendation_id
4011: AND lin.request_id = p_req_id
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;
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)
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;
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;
4125: BEGIN
4126: IF (PG_DEBUG IN ('Y', 'C')) THEN
4127: log('process_single_reco()+');
4128: END IF;
4129: SELECT ar_cash_recos_s.nextval
4130: INTO l_recommendation_id
4131: FROM dual;
4132: l_recommendation_reason := 'AR_AM_INV_THRESHOLD';
4133: l_use_matching_date := p_current_reco(1).use_matching_date;
4367:
4368: FOR l_reco_index IN 1 .. NVL(reco_num_arr.LAST, 0) LOOP
4369: IF reco_num_arr(l_reco_index) = 1 THEN
4370: INSERT
4371: INTO ar_cash_recos_all (
4372: recommendation_id,
4373: recommendation_type,
4374: recommendation_source,
4375: remit_reference_id,