[Home] [Help]
261: 1, NULL,
262: printing_count - 1)
263: WHERE customer_trx_id IN
264: (SELECT PS.customer_trx_id
265: FROM ar_payment_schedules PS,
266: ar_cons_inv_trx IT,
267: ar_cons_inv CI
268: WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK')
269: AND CI.cons_inv_id = IT.cons_inv_id
270: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
271: where i.status = 'PRE_REJECTED')
272: AND PS.payment_schedule_id = IT.adj_ps_id);
273:
274: UPDATE ar_payment_schedules
275: SET cons_inv_id = NULL
276: WHERE payment_schedule_id IN
277: (SELECT IT.adj_ps_id
278: FROM ar_cons_inv CI,
282: AND CI.cons_inv_id = IT.cons_inv_id
283: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
284: where i.status = 'PRE_REJECTED'));
285:
286: UPDATE ar_payment_schedules
287: SET cons_inv_id_rev = NULL
288: WHERE payment_schedule_id IN
289: (SELECT IT.adj_ps_id
290: FROM ar_cons_inv CI,
479: billing_date = new_bill_date,
480: term_due_date = new_due_date
481: WHERE customer_trx_id = P_trx_id;
482:
483: -- update AR_PAYMENT_SCHEDULES table with the new values
484: arp_process_header.post_commit( 'ARPBFBIB',
485: 120.0,
486: P_trx_id, -- customer_trx_id
487: NULL, -- cm trx_id
493: NULL, -- natural_app_only_flag,
494: NULL -- cash_receipt_id
495: );
496:
497: -- update AR_PAYMENT_SCHEDULES with the due date.
498: UPDATE AR_PAYMENT_SCHEDULES
499: SET due_date = new_due_date
500: WHERE customer_trx_id = P_trx_id;
501:
494: NULL -- cash_receipt_id
495: );
496:
497: -- update AR_PAYMENT_SCHEDULES with the due date.
498: UPDATE AR_PAYMENT_SCHEDULES
499: SET due_date = new_due_date
500: WHERE customer_trx_id = P_trx_id;
501:
502: ELSE
648: CT.interface_header_attribute1 trx_desc,
649: CT.ship_to_site_use_id ship_id,
650: CT.term_due_date due_date
651: FROM ra_customer_trx CT,
652: ar_payment_schedules PS
653: WHERE PS.customer_site_use_id = C_site_use_id
654: AND PS.cons_inv_id IS NULL
655: AND PS.invoice_currency_code = nvl(C_use_currency, PS.invoice_currency_code)
656: AND CT.customer_trx_id = PS.customer_trx_id
666: * bug 6488683
667: */
668: CURSOR ps_cur( C_trx_id number) IS
669: SELECT payment_schedule_id
670: FROM ar_payment_schedules
671: WHERE customer_trx_id = C_trx_id
672: ORDER BY creation_date DESC;
673:
674: TYPE c_sites_type IS REF CURSOR ;
1392: IF L_inv_trx.class = 'CM' THEN
1393: /* Bug 9392028 */
1394: SELECT count(*)
1395: INTO l_cm_flag
1396: FROM ar_payment_schedules ps,
1397: ra_customer_trx ct
1398: WHERE ps.customer_trx_id = l_inv_trx.trx_id
1399: AND ct.customer_trx_id = ps.customer_trx_id
1400: AND ps.status = 'OP'
1405:
1406: SELECT count(*)
1407: INTO l_cm_flag
1408: FROM RA_CUSTOMER_TRX ct,
1409: AR_PAYMENT_SCHEDULES ps
1410: WHERE ct.customer_trx_id IN (Select ra.APPLIED_CUSTOMER_TRX_ID
1411: FROM ar_receivable_applications ra
1412: WHERE ra.customer_trx_id = l_inv_trx.trx_id
1413: AND ra.status = 'APP'
1680: tax_original,
1681: adj_ps_id,
1682: cons_inv_line_number,
1683: org_id)
1684: SELECT /*+ index (PS AR_PAYMENT_SCHEDULES_N5) */
1685: l_consinv_id,
1686: 'ADJUSTMENT',
1687: PS.trx_number,
1688: ADJ.apply_date,
1692: NULL,
1693: ps.org_id
1694: FROM
1695: ar_adjustments ADJ,
1696: ar_payment_schedules PS
1697: WHERE
1698: ADJ.cons_inv_id is NULL
1699: AND ADJ.apply_date <= l_billing_date
1700: AND ADJ.type in ('CHARGES','FREIGHT','INVOICE','LINE','TAX')
1726: tax_original,
1727: adj_ps_id,
1728: cons_inv_line_number,
1729: org_id)
1730: SELECT /*+ index (PS AR_PAYMENT_SCHEDULES_N5) */
1731: l_consinv_id,
1732: 'FINANCE CHARGE',
1733: PS.trx_number,
1734: ADJ.apply_date,
1738: NULL,
1739: ps.org_id
1740: FROM
1741: ar_adjustments ADJ,
1742: ar_payment_schedules PS
1743: WHERE
1744: ADJ.cons_inv_id is NULL
1745: AND ADJ.apply_date <= l_billing_date
1746: AND ADJ.type = 'CHARGES'
1783: PS.payment_schedule_id,
1784: NULL,
1785: PS.org_id
1786: FROM
1787: ar_payment_schedules PS,
1788: ar_cash_receipts CR
1789: WHERE
1790: PS.customer_site_use_id = L_sites.site_id
1791: AND PS.cons_inv_id IS NULL
1797: AND (PS.status = 'OP'
1798: OR (ps.amount_due_original +
1799: (SELECT nvl(SUM(nvl(ra.amount_applied_from, ra.amount_applied)), 0)
1800: FROM ar_receivable_applications ra,
1801: ar_payment_schedules ps_inv,
1802: ra_customer_trx inv_trx
1803: WHERE ra.cash_receipt_id = cr.cash_receipt_id
1804: AND inv_trx.customer_trx_id = ra.applied_customer_trx_id
1805: AND RA.status = 'APP'
1834: PS.payment_schedule_id,
1835: NULL,
1836: CR.org_id
1837: FROM
1838: ar_payment_schedules PS,
1839: ar_cash_receipts CR
1840: WHERE
1841: PS.customer_site_use_id = L_sites.site_id
1842: AND PS.cons_inv_id_rev IS NULL
1884: NULL,
1885: ps_cash.org_id
1886: FROM
1887: ar_receivable_applications RA,
1888: ar_payment_schedules ps_cash,
1889: ar_payment_schedules ps_inv
1890: WHERE
1891: RA.cons_inv_id IS NULL
1892: AND RA.status = 'APP'
1885: ps_cash.org_id
1886: FROM
1887: ar_receivable_applications RA,
1888: ar_payment_schedules ps_cash,
1889: ar_payment_schedules ps_inv
1890: WHERE
1891: RA.cons_inv_id IS NULL
1892: AND RA.status = 'APP'
1893: AND RA.application_type = 'CASH'
1945: NULL,
1946: ps_cash.org_id
1947: FROM
1948: ar_receivable_applications RA,
1949: ar_payment_schedules ps_cash,
1950: ar_payment_schedules ps_inv
1951: WHERE
1952: RA.cons_inv_id_to IS NULL
1953: AND RA.status = 'APP'
1946: ps_cash.org_id
1947: FROM
1948: ar_receivable_applications RA,
1949: ar_payment_schedules ps_cash,
1950: ar_payment_schedules ps_inv
1951: WHERE
1952: RA.cons_inv_id_to IS NULL
1953: AND RA.status = 'APP'
1954: AND RA.application_type = 'CASH'
2002: ps_cash.org_id
2003: FROM
2004: ar_cons_inv_trx inv_trx,
2005: ar_receivable_applications ra,
2006: ar_payment_schedules ps_cash,
2007: ar_payment_schedules ps_inv
2008: WHERE ra.cons_inv_id_to is not null
2009: AND ra.cons_inv_id is null
2010: AND ra.status = 'APP'
2003: FROM
2004: ar_cons_inv_trx inv_trx,
2005: ar_receivable_applications ra,
2006: ar_payment_schedules ps_cash,
2007: ar_payment_schedules ps_inv
2008: WHERE ra.cons_inv_id_to is not null
2009: AND ra.cons_inv_id is null
2010: AND ra.status = 'APP'
2011: AND ra.application_type = 'CASH'
2048: ps_cash.org_id
2049: FROM
2050: ar_cons_inv_trx inv_trx,
2051: ar_receivable_applications ra,
2052: ar_payment_schedules ps_cash,
2053: ar_payment_schedules ps_inv
2054: WHERE ra.cons_inv_id_to is null
2055: AND ra.cons_inv_id is not null
2056: AND ra.status = 'APP'
2049: FROM
2050: ar_cons_inv_trx inv_trx,
2051: ar_receivable_applications ra,
2052: ar_payment_schedules ps_cash,
2053: ar_payment_schedules ps_inv
2054: WHERE ra.cons_inv_id_to is null
2055: AND ra.cons_inv_id is not null
2056: AND ra.status = 'APP'
2057: AND ra.application_type = 'CASH'
2090: RA.receivable_application_id,
2091: NULL,
2092: PS_CM.org_id
2093: FROM
2094: AR_PAYMENT_SCHEDULES PS_CM ,
2095: AR_RECEIVABLE_APPLICATIONS RA ,
2096: AR_PAYMENT_SCHEDULES PS_INV,
2097: AR_CONS_INV_TRX CTRX,
2098: AR_CONS_INV C
2092: PS_CM.org_id
2093: FROM
2094: AR_PAYMENT_SCHEDULES PS_CM ,
2095: AR_RECEIVABLE_APPLICATIONS RA ,
2096: AR_PAYMENT_SCHEDULES PS_INV,
2097: AR_CONS_INV_TRX CTRX,
2098: AR_CONS_INV C
2099: WHERE
2100: RA.cons_inv_id IS NULL
2137: NULL,
2138: PS_CM.org_id
2139: FROM
2140: ar_receivable_applications RA,
2141: ar_payment_schedules PS_CM,
2142: ar_payment_schedules PS_INV
2143: WHERE
2144: RA.cons_inv_id_to IS NULL
2145: AND RA.status = 'APP'
2138: PS_CM.org_id
2139: FROM
2140: ar_receivable_applications RA,
2141: ar_payment_schedules PS_CM,
2142: ar_payment_schedules PS_INV
2143: WHERE
2144: RA.cons_inv_id_to IS NULL
2145: AND RA.status = 'APP'
2146: AND RA.application_type = 'CM'
2193: NULL,
2194: PS_INV.org_id
2195: FROM
2196: ar_receivable_applications RA,
2197: ar_payment_schedules PS_INV, -- INV
2198: ar_payment_schedules PS_CM -- CM
2199: WHERE
2200: RA.cons_inv_id_to IS NULL
2201: AND RA.status = 'APP'
2194: PS_INV.org_id
2195: FROM
2196: ar_receivable_applications RA,
2197: ar_payment_schedules PS_INV, -- INV
2198: ar_payment_schedules PS_CM -- CM
2199: WHERE
2200: RA.cons_inv_id_to IS NULL
2201: AND RA.status = 'APP'
2202: AND RA.application_type = 'CM'
2243: PS.payment_schedule_id,
2244: NULL,
2245: PS.org_id
2246: FROM
2247: ar_payment_schedules PS,
2248: ar_cash_receipts CR
2249: WHERE
2250: PS.customer_id = L_sites.customer_id
2251: AND PS.customer_site_use_id IS NULL
2258: AND (PS.status = 'OP'
2259: OR (ps.amount_due_original +
2260: (SELECT nvl(SUM(nvl(ra.amount_applied_from, ra.amount_applied)), 0)
2261: FROM ar_receivable_applications ra,
2262: ar_payment_schedules ps_inv,
2263: ra_customer_trx inv_trx
2264: WHERE ra.cash_receipt_id = cr.cash_receipt_id
2265: AND inv_trx.customer_trx_id = ra.applied_customer_trx_id
2266: AND RA.status = 'APP'
2295: PS.payment_schedule_id,
2296: NULL,
2297: CR.org_id
2298: FROM
2299: ar_payment_schedules PS,
2300: ar_cash_receipts CR
2301: WHERE
2302: PS.customer_id =L_sites.customer_id
2303: AND PS.customer_site_use_id IS NULL
2367: NULL,
2368: ps_cash.org_id
2369: FROM
2370: ar_receivable_applications RA,
2371: ar_payment_schedules ps_cash,
2372: ra_customer_trx inv_trx,
2373: ar_payment_schedules ps_app
2374: WHERE
2375: RA.cons_inv_id IS NULL
2369: FROM
2370: ar_receivable_applications RA,
2371: ar_payment_schedules ps_cash,
2372: ra_customer_trx inv_trx,
2373: ar_payment_schedules ps_app
2374: WHERE
2375: RA.cons_inv_id IS NULL
2376: AND RA.status = 'APP'
2377: AND RA.application_type = 'CASH'
2418: NULL,
2419: ps_cash.org_id
2420: FROM
2421: ar_receivable_applications RA,
2422: ar_payment_schedules ps_cash
2423: WHERE
2424: RA.cons_inv_id IS NULL
2425: AND RA.applied_payment_schedule_id = -3
2426: AND RA.application_type = 'CASH'
2495: ending_balance = beginning_balance + l_period_trx + l_period_receipts +
2496: l_period_adj + l_period_credits + l_period_finchrg
2497: WHERE cons_inv_id = l_consinv_id;
2498:
2499: /** For Site: update ar_payment_schedules, ar_receivable_applications
2500: and ar_adjustments **/
2501:
2502: write_debug_and_log('Updating AR_PAYMENT_SCHEDULES');
2503:
2498:
2499: /** For Site: update ar_payment_schedules, ar_receivable_applications
2500: and ar_adjustments **/
2501:
2502: write_debug_and_log('Updating AR_PAYMENT_SCHEDULES');
2503:
2504: UPDATE ar_payment_schedules PS
2505: SET PS.cons_inv_id = l_consinv_id
2506: WHERE PS.payment_schedule_id IN
2500: and ar_adjustments **/
2501:
2502: write_debug_and_log('Updating AR_PAYMENT_SCHEDULES');
2503:
2504: UPDATE ar_payment_schedules PS
2505: SET PS.cons_inv_id = l_consinv_id
2506: WHERE PS.payment_schedule_id IN
2507: (SELECT IT.adj_ps_id
2508: FROM ar_cons_inv_trx IT
2509: WHERE IT.cons_inv_id = l_consinv_id
2510: AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO', 'DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK',
2511: 'RECEIPT'));
2512:
2513: UPDATE ar_payment_schedules PS
2514: SET PS.cons_inv_id_rev = l_consinv_id
2515: WHERE PS.payment_schedule_id IN
2516: (SELECT IT.adj_ps_id
2517: FROM ar_cons_inv_trx IT
2639: billing_date = l_tab_billing_date(i),
2640: term_due_date = l_tab_due_date(i)
2641: WHERE customer_trx_id = l_tab_trx_id(i);
2642:
2643: write_debug_and_log('Override : Updating AR_PAYMENT_SCHEDULES');
2644: FOR i IN 1..l_tab_idx LOOP
2645: arp_process_header.post_commit( 'ARPBFBIB',
2646: 120.0,
2647: l_tab_trx_id(i), -- customer_trx_id
2656: );
2657: END LOOP;
2658:
2659: FORALL i IN 1..l_tab_idx
2660: UPDATE AR_PAYMENT_SCHEDULES
2661: SET due_date = l_tab_due_date(i)
2662: WHERE customer_trx_id = l_tab_trx_id(i);
2663:
2664: END IF;
2719: P_request_id IN NUMBER) IS
2720:
2721: CURSOR c_pending_trx IS
2722: SELECT PS.customer_trx_id
2723: FROM ar_payment_schedules PS,
2724: ar_cons_inv_trx IT,
2725: ar_cons_inv CI
2726: WHERE
2727: CI.print_status = 'PENDING'
2745: 'REPRINT', 0,
2746: 1)
2747: WHERE CT.customer_trx_id IN
2748: (SELECT PS.customer_trx_id
2749: FROM ar_payment_schedules PS,
2750: ar_cons_inv_trx IT,
2751: ar_cons_inv CI
2752: WHERE (
2753: (P_print_option = 'REPRINT'