[Home] [Help]
1210: ),38) trans_to_receipt_rate,
1211: NULL amount_applied_from, -- will be calculated later for xcurr app.
1212: ps.payment_schedule_id payment_schedule_id,
1213: NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
1214: UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ps.trx_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
1215: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
1216: ps.org_id org_id,
1217: ps.term_id term_id,
1218: am.automatch_id automatch_id,
1244: AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
1245: AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
1246: AND ps.trx_number IS NOT NULL
1247: AND ps.selected_for_receipt_batch_id IS NULL
1248: AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ps.trx_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
1249: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
1250: AND ps.class NOT IN ('PMT', 'GUAR')
1251: AND ps.payment_schedule_id > 0
1252: AND ps.status = 'OP'
1457: ),38) trans_to_receipt_rate,
1458: NULL amount_applied_from, -- will be calculated later for xcurr app.
1459: ps.payment_schedule_id,
1460: NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
1461: UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.purchase_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1462: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
1463: ps.org_id,
1464: ps.term_id term_id,
1465: am.automatch_id,
1490: AND cr.cash_receipt_id = ref.cash_receipt_id
1491: AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
1492: AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
1493: AND trx.purchase_order IS NOT NULL
1494: AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.purchase_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1495: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
1496: AND ps.customer_trx_id = trx.customer_trx_id
1497: AND ps.selected_for_receipt_batch_id IS NULL
1498: AND ps.class NOT IN ('PMT', 'GUAR')
1698: ),38) trans_to_receipt_rate,
1699: NULL amount_applied_from, -- will be calculated later for xcurr app.
1700: ps.payment_schedule_id payment_schedule_id,
1701: NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
1702: UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1703: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
1704: ps.org_id,
1705: ps.term_id term_id,
1706: am.automatch_id automatch_id,
1733: AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
1734: AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
1735: AND lin.interface_line_context <> 'OKS CONTRACTS'
1736: AND lin.sales_order IS NOT NULL
1737: AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1738: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
1739: AND ps.customer_trx_id = lin.customer_trx_id
1740: AND trx.customer_trx_id = ps.customer_trx_id
1741: /* Added to fetch values from Header Attributes */
1942: ),38) trans_to_receipt_rate,
1943: NULL amount_applied_from, -- will be calculated later for xcurr app.
1944: ps.payment_schedule_id payment_schedule_id,
1945: NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
1946: UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1947: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
1948: ps.org_id,
1949: ps.term_id term_id,
1950: am.automatch_id automatch_id,
1977: AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
1978: AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
1979: AND lin.interface_line_context = 'OKS CONTRACTS'
1980: AND lin.sales_order IS NOT NULL
1981: AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1982: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
1983: AND ps.customer_trx_id = lin.customer_trx_id
1984: AND trx.customer_trx_id = ps.customer_trx_id
1985: /* Added to fetch the date from Header Attribute Columns */
2189: ),38) trans_to_receipt_rate,
2190: NULL amount_applied_from, -- will be calculated later for xcurr app.
2191: ps.payment_schedule_id payment_schedule_id,
2192: NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
2193: UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.interface_header_attribute'|| p_attribute_number ||', :b_trans_format_str, ''\2''), :b_trans_float_str, ''''),
2194: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, :b_rem_format_str, ''\2''), :b_rem_float_str, '''')) match_score_value,
2195: ps.org_id org_id,
2196: ps.term_id term_id,
2197: am.automatch_id automatch_id,
2222: AND cr.cash_receipt_id = ref.cash_receipt_id
2223: AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
2224: AND NVL(am.end_date, to_date(''31/12/4712'',''DD/MM/YYYY''))
2225: AND trx.interface_header_attribute'|| p_attribute_number || ' IS NOT NULL
2226: AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.interface_header_attribute'|| p_attribute_number ||', :b_trans_format_str, ''\2''), :b_trans_float_str, ''''),
2227: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, :b_rem_format_str, ''\2''), :b_rem_float_str, '''')) >= am.sugg_match_threshold
2228: AND ps.customer_trx_id = trx.customer_trx_id
2229: AND ps.selected_for_receipt_batch_id IS NULL
2230: AND ps.class NOT IN (''PMT'', ''GUAR'')
2450: ),38) trans_to_receipt_rate,
2451: NULL amount_applied_from, -- will be calculated later for xcurr app.
2452: ps.payment_schedule_id payment_schedule_id,
2453: NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
2454: UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.waybill_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
2455: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
2456: ps.org_id,
2457: ps.term_id term_id,
2458: am.automatch_id,
2483: AND cr.cash_receipt_id = ref.cash_receipt_id
2484: AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
2485: AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
2486: AND trx.waybill_number IS NOT NULL
2487: AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.waybill_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
2488: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
2489: AND ps.customer_trx_id = trx.customer_trx_id
2490: AND ps.selected_for_receipt_batch_id IS NULL
2491: AND ps.class NOT IN ('PMT', 'GUAR')
2672: ),38) trans_to_receipt_rate,
2673: NULL amount_applied_from, -- will be calculated later for xcurr app.
2674: ps.payment_schedule_id payment_schedule_id,
2675: ci.cons_inv_id cons_inv_id,
2676: UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ci.cons_billing_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
2677: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
2678: ci.org_id,
2679: ps.term_id term_id,
2680: am.automatch_id automatch_id,
2704: AND ref.receipt_reference_status = 'AR_AM_NEW'
2705: AND cr.cash_receipt_id = ref.cash_receipt_id
2706: AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
2707: AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
2708: AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ci.cons_billing_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
2709: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
2710: AND ps.cons_inv_id = ci.cons_inv_id
2711: AND ps.selected_for_receipt_batch_id IS NULL
2712: AND ps.class NOT IN ('PMT', 'GUAR')
2917: ),38) trans_to_receipt_rate,
2918: NULL amount_applied_from, -- will be calculated later for xcurr app.
2919: ps.payment_schedule_id payment_schedule_id,
2920: NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
2921: UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.ct_reference, p_trans_format_str, '\2'), p_trans_float_str, ''),
2922: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
2923: ps.org_id,
2924: ps.term_id term_id,
2925: am.automatch_id automatch_id,
2950: AND cr.cash_receipt_id = ref.cash_receipt_id
2951: AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
2952: AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
2953: AND trx.ct_reference IS NOT NULL
2954: AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.ct_reference, p_trans_format_str, '\2'), p_trans_float_str, ''),
2955: REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
2956: AND ps.customer_trx_id = trx.customer_trx_id
2957: AND ps.selected_for_receipt_batch_id IS NULL
2958: AND ps.class NOT IN ('PMT', 'GUAR')