[Home] [Help]
803: * the current run. This is to avoid duplicate recommendations *
804: * getting generated and to handle the cases where a refernce no is*
805: * changed after the previous run. Refer bug 8396831 * */
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
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. *
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: * *
1143: * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
1144: * *
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. *
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: * *
1391: * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
1392: * *
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. *
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: * *
1631: * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
1632: * *
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. *
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: * *
1875: * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
1876: * *
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. *
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: * *
2122: * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
2123: * *
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. *
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 *
2384: * *
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. *
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: * *
2623: * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
2624: * *
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. *
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: * *
2850: * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
2851: * *
3139: IF (PG_DEBUG IN ('Y', 'C')) THEN
3140: log('No. of recos updated to Date Mismatch: ' || SQL%ROWCOUNT );
3141: END IF;
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'
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
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
3174: IF (PG_DEBUG IN ('Y', 'C')) THEN
3175: log('No. of recos updated to Amount Mismatch: ' || SQL%ROWCOUNT );
3176: END IF;
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'
3187: log('No. of reco lines updated to Amount Mismatch: ' || SQL%ROWCOUNT );
3188: END IF;*/
3189:
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,
3208: END IF;
3209:
3210: --Do not autoapply a PS if any 2 of amount applied, amount applied from and trans to receipt rate
3211: --is not provided in the reference
3212: UPDATE ar_cash_reco_lines l
3213: SET recommendation_reason = 'AR_AA_NO_XCURR_RATE'
3214: WHERE EXISTS (SELECT 'No X Rate Info'
3215: FROM ar_payment_schedules ps
3216: WHERE l.payment_schedule_id = ps.payment_schedule_id
3224: log('No. of recos updated to No Exchange Rate: ' || SQL%ROWCOUNT );
3225: END IF;
3226:
3227: --Invalid rate provided for fixed rate currencies
3228: UPDATE ar_cash_reco_lines l
3229: SET recommendation_reason = 'AR_AA_INVALID_RATE'
3230: WHERE (EXISTS (SELECT 'Same Currency'
3231: FROM ar_payment_schedules ps
3232: WHERE l.payment_schedule_id = ps.payment_schedule_id
3250: log('No. of recos updated to Invalid Rate: ' || SQL%ROWCOUNT );
3251: END IF;
3252:
3253: --Update the status if the application is not a natural application or an over application
3254: UPDATE ar_cash_reco_lines l
3255: SET recommendation_reason = (SELECT CASE WHEN SIGN(l.amount_applied*ps.amount_due_remaining) = -1
3256: THEN 'AR_AA_NAT_APP_VIO'
3257: ELSE recommendation_reason
3258: END
3265: log('No. of recos updated to Nat Appn Vio/Over Appn: ' || SQL%ROWCOUNT );
3266: END IF;
3267:
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,
3288:
3289: /* Prevent application if the same PS is selected for any other reco
3290: Since we are validating per worker, at the end of validation there is
3291: a chance that same PS is selected by two receipts from diff workers */
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,
3291: a chance that same PS is selected by two receipts from diff workers */
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
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)
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
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
3413: WHERE rec.request_id = p_req_id
3483: log('No. of recos Corrected: ' || SQL%ROWCOUNT );
3484: END IF;
3485: END;
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'
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
3513: AND ref.remit_reference_id = rec.remit_reference_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
3524: AND recl1.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3532: IF (PG_DEBUG IN ('Y', 'C')) THEN
3533: log('No. of recos updated to Non Unique Customer: ' || SQL%ROWCOUNT );
3534: END IF;
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'
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
3554: AND lin.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3559: END
3560: END) = 'T'
3561: AND lin.request_id = p_req_id)
3562: AND EXISTS (SELECT 'Applicable Reco Exist'
3563: FROM ar_cash_reco_lines lin
3564: WHERE lin.recommendation_id = rec.recommendation_id
3565: AND lin.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3566: AND lin.request_id = p_req_id)
3567: AND request_id = p_req_id
3570: IF (PG_DEBUG IN ('Y', 'C')) THEN
3571: log('No. of recos updated to Multiple Recos: ' || SQL%ROWCOUNT );
3572: END IF;
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
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
3603: AND ref.worker_number = p_worker_number
3612: IF (PG_DEBUG IN ('Y', 'C')) THEN
3613: log('No. of recos updated to Remittance amount exceeded: ' || SQL%ROWCOUNT );
3614: END IF;
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'
3682: AND request_id = p_req_id;
3683: END IF;
3684: END IF;
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'
3695:
3696: SELECT ps.payment_schedule_id
3697: BULK COLLECT INTO locked_ps_records
3698: FROM ar_payment_schedules ps,
3699: ar_cash_reco_lines lines
3700: WHERE lines.request_id = p_req_id
3701: AND lines.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3702: AND ps.payment_schedule_id = lines.payment_schedule_id
3703: FOR UPDATE OF ps.amount_due_remaining SKIP LOCKED;
3702: AND ps.payment_schedule_id = lines.payment_schedule_id
3703: FOR UPDATE OF ps.amount_due_remaining SKIP LOCKED;
3704:
3705: FORALL i IN 1..NVL(locked_ps_records.LAST, 0)
3706: UPDATE ar_cash_reco_lines
3707: SET recommendation_reason = 'AR_AA_INV_LOCKED'
3708: WHERE request_id = p_req_id
3709: AND recommendation_reason = 'AR_AM_INV_THRESHOLD'
3710: AND payment_schedule_id = locked_ps_records(i);
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
3795: AND ref.worker_number = p_worker_number
3824: lin.amount_applied,
3825: lin.amount_applied_from,
3826: lin.trans_to_receipt_rate,
3827: ps.invoice_currency_code
3828: FROM ar_cash_reco_lines lin,
3829: ar_payment_schedules ps
3830: WHERE lin.recommendation_id = p_reco_id
3831: AND lin.request_id = p_req_id
3832: AND lin.recommendation_reason = 'AR_AA_INV_LOCKED'
3880: log('Apply API Status :'|| l_return_status);
3881: END IF;
3882:
3883: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3884: UPDATE ar_cash_reco_lines
3885: SET recommendation_reason = 'AR_AA_REC_APP_IN_ERROR'
3886: WHERE recommendation_id = app_line.recommendation_id
3887: AND line_number = app_line.line_number;
3888:
3957: log('Apply API Status :'|| l_return_status);
3958: END IF;
3959:
3960: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3961: UPDATE ar_cash_reco_lines
3962: SET recommendation_reason = 'AR_AA_REC_APP_IN_ERROR'
3963: WHERE recommendation_id = bfb_line.recommendation_id
3964: AND line_number = bfb_line.line_number;
3965: FOR loop_index in 1..l_msg_count LOOP
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
4012: AND recommendation_type = 'TRX'
4014: IF (PG_DEBUG IN ('Y', 'C')) THEN
4015: log('No. of References updated with Resolved Matching Number: ' || SQL%ROWCOUNT );
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'
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;
4124: l_valid VARCHAR2(1);
4428: END IF;
4429: END LOOP;
4430:
4431: FORALL l_reco_line_index IN 1 .. NVL(reco_id_arr.LAST, 0)
4432: INSERT INTO ar_cash_reco_lines_all (
4433: recommendation_id,
4434: line_number,
4435: customer_trx_id,
4436: payment_schedule_id,