eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ARP_BF_BILL dependencies on AR_CONS_INV_TRX

Line 286: ar_cons_inv_trx IT,

282: printing_count - 1)
283: WHERE customer_trx_id IN
284: (SELECT PS.customer_trx_id
285: FROM ar_payment_schedules PS,
286: ar_cons_inv_trx IT,
287: ar_cons_inv CI
288: WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO')
289: AND CI.cons_inv_id = IT.cons_inv_id
290: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

Line 299: ar_cons_inv_trx IT

295: SET cons_inv_id = NULL
296: WHERE payment_schedule_id IN
297: (SELECT IT.adj_ps_id
298: FROM ar_cons_inv CI,
299: ar_cons_inv_trx IT
300: WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO',
301: 'RECEIPT')
302: AND CI.cons_inv_id = IT.cons_inv_id
303: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

Line 311: ar_cons_inv_trx IT

307: SET cons_inv_id_rev = NULL
308: WHERE payment_schedule_id IN
309: (SELECT IT.adj_ps_id
310: FROM ar_cons_inv CI,
311: ar_cons_inv_trx IT
312: WHERE IT.transaction_type = 'RECEIPT REV'
313: AND CI.cons_inv_id = IT.cons_inv_id
314: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
315: where i.status = 'PRE_REJECTED'));

Line 322: ar_cons_inv_trx IT

318: SET cons_inv_id = NULL
319: WHERE receivable_application_id IN
320: (SELECT IT.adj_ps_id
321: FROM ar_cons_inv CI,
322: ar_cons_inv_trx IT
323: WHERE IT.transaction_type IN ('XSITE RECREV', 'XSITE_CMREV',
324: 'XCURR RECREV', 'XSITE XCURR RECREV',
325: 'EXCLUDE RECREV', 'EXCLUDE_CMREV')
326: AND CI.cons_inv_id = IT.cons_inv_id

Line 335: ar_cons_inv_trx IT

331: SET cons_inv_id_to = NULL
332: WHERE receivable_application_id IN
333: (SELECT IT.adj_ps_id
334: FROM ar_cons_inv CI,
335: ar_cons_inv_trx IT
336: WHERE IT.transaction_type IN ('XSITE RECAPP','XSITE_CMAPP',
337: 'XCURR RECAPP', 'XSITE XCURR RECAPP' ,
338: 'EXCLUDE RECAPP', 'EXCLUDE_CMAPP',
339: 'DELAY_CMAPP')

Line 350: ar_cons_inv_trx IT

346: SET cons_inv_id = NULL
347: WHERE adjustment_id IN
348: (SELECT IT.adj_ps_id
349: FROM ar_cons_inv CI,
350: ar_cons_inv_trx IT
351: WHERE IT.transaction_type = 'ADJUSTMENT'
352: AND CI.cons_inv_id = IT.cons_inv_id
353: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
354: where i.status = 'PRE_REJECTED'));

Line 357: DELETE FROM ar_cons_inv_trx_lines

353: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
354: where i.status = 'PRE_REJECTED'));
355:
356:
357: DELETE FROM ar_cons_inv_trx_lines
358: WHERE cons_inv_id IN
359: (SELECT CI.cons_inv_id
360: FROM ar_cons_inv CI
361: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

Line 364: DELETE FROM ar_cons_inv_trx

360: FROM ar_cons_inv CI
361: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
362: where i.status = 'PRE_REJECTED'));
363:
364: DELETE FROM ar_cons_inv_trx
365: WHERE cons_inv_id IN
366: (SELECT CI.cons_inv_id
367: FROM ar_cons_inv CI
368: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

Line 1397: -- Prior to inserting into ar_cons_inv_trx, need to perform validation and override if necessary

1393: -- after process_override whether an update has taken place by comparing
1394: -- these two variables. bug 6488683
1395: l_check_override := l_tab_idx;
1396:
1397: -- Prior to inserting into ar_cons_inv_trx, need to perform validation and override if necessary
1398: IF nvl(L_inv_trx.term_id,'-1') <> L_terms.term_id THEN
1399:
1400: IF L_inv_trx.class = 'CM' THEN
1401: -- bypass additional validation, since CM's have no term id / billing date

Line 1492: write_debug_and_log('insert to ar_cons_inv_trx for ' || L_inv_trx.trx_id);

1488: l_new_schedule_id := L_inv_trx.schedule_id;
1489: END IF;
1490:
1491: write_debug_and_log('new payment schedule id ' || l_new_schedule_id );
1492: write_debug_and_log('insert to ar_cons_inv_trx for ' || L_inv_trx.trx_id);
1493:
1494: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1495: transaction_type,
1496: trx_number,

Line 1494: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1490:
1491: write_debug_and_log('new payment schedule id ' || l_new_schedule_id );
1492: write_debug_and_log('insert to ar_cons_inv_trx for ' || L_inv_trx.trx_id);
1493:
1494: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1495: transaction_type,
1496: trx_number,
1497: transaction_date,
1498: amount_original,

Line 1527: write_debug_and_log('insert to ar_cons_inv_trx_lines for ' || L_inv_trx.trx_id);

1523: /** For audit purposes, insert detail line information even if
1524: reporting in summary. Also note that cons_inv_line_number
1525: is one value for detail lines for a specific invoice. **/
1526:
1527: write_debug_and_log('insert to ar_cons_inv_trx_lines for ' || L_inv_trx.trx_id);
1528: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1529: cons_inv_line_number,
1530: customer_trx_id,
1531: customer_trx_line_id,

Line 1528: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,

1524: reporting in summary. Also note that cons_inv_line_number
1525: is one value for detail lines for a specific invoice. **/
1526:
1527: write_debug_and_log('insert to ar_cons_inv_trx_lines for ' || L_inv_trx.trx_id);
1528: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1529: cons_inv_line_number,
1530: customer_trx_id,
1531: customer_trx_line_id,
1532: line_number,

Line 1560: write_debug_and_log('update ar_cons_inv_trx_lines for TAX');

1556: AND line_type NOT IN ('TAX', 'FREIGHT');
1557:
1558: /** now update lines with associated tax line **/
1559:
1560: write_debug_and_log('update ar_cons_inv_trx_lines for TAX');
1561: OPEN c_tax(L_inv_trx.trx_id);
1562: LOOP
1563: FETCH c_tax BULK COLLECT INTO
1564: l_line_id , l_tax_sum, l_include_tax_sum LIMIT l_bulk_fetch_rows;

Line 1569: UPDATE ar_cons_inv_trx_lines

1565:
1566: -- 1. Update tax_amount
1567: -- 2. Exclude inclusive tax amount total from extended_amount
1568: FORALL i IN 1..l_line_id.count
1569: UPDATE ar_cons_inv_trx_lines
1570: SET tax_amount = l_tax_sum(i),
1571: extended_amount = extended_amount - l_include_tax_sum(i)
1572: WHERE customer_trx_id = L_inv_trx.trx_id
1573: AND customer_trx_line_id = l_line_id(i) ;

Line 1580: write_debug_and_log('insert to ar_cons_inv_trx_lines for FREIGHT');

1576: END LOOP;
1577: CLOSE c_tax;
1578:
1579: /** now create 1 summary row for freight **/
1580: write_debug_and_log('insert to ar_cons_inv_trx_lines for FREIGHT');
1581: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1582: cons_inv_line_number,
1583: customer_trx_id,
1584: customer_trx_line_id,

Line 1581: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,

1577: CLOSE c_tax;
1578:
1579: /** now create 1 summary row for freight **/
1580: write_debug_and_log('insert to ar_cons_inv_trx_lines for FREIGHT');
1581: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1582: cons_inv_line_number,
1583: customer_trx_id,
1584: customer_trx_line_id,
1585: line_number,

Line 1636: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1632: (fin charge is in next select ACTIVITY 1A) */
1633:
1634: write_debug_and_log('.........ACTIVITY 1');
1635:
1636: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1637: transaction_type,
1638: trx_number,
1639: transaction_date,
1640: amount_original,

Line 1676: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1672: pick up all adjustments (only finance charges) generated against this BFB site */
1673:
1674: write_debug_and_log('.........ACTIVITY 1A');
1675:
1676: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1677: transaction_type,
1678: trx_number,
1679: transaction_date,
1680: amount_original,

Line 1716: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1712: site (in ACTIVITY 4 : we back out amounts applied to diff site) */
1713:
1714: write_debug_and_log('.........ACTIVITY 2');
1715:
1716: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1717: transaction_type,
1718: trx_number,
1719: transaction_date,
1720: amount_original,

Line 1753: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1749: receipts created against this BFB site */
1750:
1751: write_debug_and_log('.........ACTIVITY 3');
1752:
1753: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1754: transaction_type,
1755: trx_number,
1756: transaction_date,
1757: amount_original,

Line 1790: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1786: a different site */
1787:
1788: write_debug_and_log('.........ACTIVITY 4');
1789:
1790: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1791: transaction_type,
1792: trx_number,
1793: transaction_date,
1794: amount_original,

Line 1837: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1833: different (or null) site, but applied to TRX with this BFB Site */
1834:
1835: write_debug_and_log('.........ACTIVITY 5');
1836:
1837: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1838: transaction_type,
1839: trx_number,
1840: transaction_date,
1841: amount_original,

Line 1890: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1886: in ACTIVITY 5, otherwise the receipt application is recorded twice */
1887:
1888: write_debug_and_log('.........ACTIVITY 6');
1889:
1890: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1891: transaction_type,
1892: trx_number,
1893: transaction_date,
1894: amount_original,

Line 1911: ar_cons_inv_trx inv_trx,

1907: RA.receivable_application_id,
1908: NULL,
1909: ps_cash.org_id
1910: FROM
1911: ar_cons_inv_trx inv_trx,
1912: ar_receivable_applications ra,
1913: ar_payment_schedules ps_cash,
1914: ar_payment_schedules ps_inv
1915: WHERE ra.cons_inv_id_to is not null

Line 1936: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1932: we need to exclude it. */
1933:
1934: write_debug_and_log('.........ACTIVITY 7');
1935:
1936: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1937: transaction_type,
1938: trx_number,
1939: transaction_date,
1940: amount_original,

Line 1957: ar_cons_inv_trx inv_trx,

1953: RA.receivable_application_id,
1954: NULL,
1955: ps_cash.org_id
1956: FROM
1957: ar_cons_inv_trx inv_trx,
1958: ar_receivable_applications ra,
1959: ar_payment_schedules ps_cash,
1960: ar_payment_schedules ps_inv
1961: WHERE ra.cons_inv_id_to is null

Line 1979: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1975: applied to an invoice having a different site */
1976:
1977: write_debug_and_log('.........ACTIVITY 8');
1978:
1979: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1980: transaction_type,
1981: trx_number,
1982: transaction_date,
1983: amount_original,

Line 2020: INSERT INTO ar_cons_inv_trx (cons_inv_id,

2016: INV is not pulled into BFB yet, need to exclude */
2017:
2018: write_debug_and_log('.........ACTIVITY 8A');
2019:
2020: INSERT INTO ar_cons_inv_trx (cons_inv_id,
2021: transaction_type,
2022: trx_number,
2023: transaction_date,
2024: amount_original,

Line 2057: ar_cons_inv_trx ctrx

2053: AND PS_INV.customer_site_use_id = L_sites.site_id
2054: AND NOT EXISTS
2055: (SELECT '*'
2056: FROM ar_cons_inv c,
2057: ar_cons_inv_trx ctrx
2058: WHERE PS_INV.customer_trx_id = ctrx.customer_trx_id
2059: AND c.cons_inv_id = ctrx.cons_inv_id
2060: AND c.status <> 'REJECTED');
2061:

Line 2069: INSERT INTO ar_cons_inv_trx (cons_inv_id,

2065: part of an BFB yet */
2066:
2067: write_debug_and_log('.........ACTIVITY 9');
2068:
2069: INSERT INTO ar_cons_inv_trx (cons_inv_id,
2070: transaction_type,
2071: trx_number,
2072: transaction_date,
2073: amount_original,

Line 2106: ar_cons_inv_trx ctrx

2102: AND ( PS_CM.customer_site_use_id <> PS_INV.customer_site_use_id
2103: or nvl(PS_CM.exclude_from_cons_bill_flag, 'N') = 'Y')
2104: AND EXISTS (SELECT '*'
2105: FROM ar_cons_inv c,
2106: ar_cons_inv_trx ctrx
2107: WHERE PS_INV.customer_trx_id = ctrx.customer_trx_id
2108: AND c.cons_inv_id = ctrx.cons_inv_id
2109: AND c.status <> 'REJECTED');
2110:

Line 2120: INSERT INTO ar_cons_inv_trx (cons_inv_id,

2116: l_customer_id := L_sites.customer_id;
2117:
2118: write_debug_and_log('.........ACTIVITY 10 : Receipts with No Location');
2119:
2120: INSERT INTO ar_cons_inv_trx (cons_inv_id,
2121: transaction_type,
2122: trx_number,
2123: transaction_date,
2124: amount_original,

Line 2158: INSERT INTO ar_cons_inv_trx (cons_inv_id,

2154: receipts created without site */
2155:
2156: write_debug_and_log('.........ACTIVITY 11: Reversal of receipts with no location');
2157:
2158: INSERT INTO ar_cons_inv_trx (cons_inv_id,
2159: transaction_type,
2160: trx_number,
2161: transaction_date,
2162: amount_original,

Line 2199: FROM ar_cons_inv_trx

2195:
2196: /** For Site: calculate totals **/
2197: SELECT nvl(sum(amount_original),0)
2198: INTO l_period_trx
2199: FROM ar_cons_inv_trx
2200: WHERE cons_inv_id = l_consinv_id
2201: AND transaction_type IN ('INVOICE');
2202:
2203: SELECT nvl(sum(amount_original),0)

Line 2205: FROM ar_cons_inv_trx

2201: AND transaction_type IN ('INVOICE');
2202:
2203: SELECT nvl(sum(amount_original),0)
2204: INTO l_period_receipts
2205: FROM ar_cons_inv_trx
2206: WHERE cons_inv_id = l_consinv_id
2207: AND transaction_type IN ('RECEIPT','RECEIPT REV','XSITE RECREV',
2208: 'XSITE RECAPP',
2209: 'XSITE XCURR RECAPP','XSITE XCURR RECREV',

Line 2214: FROM ar_cons_inv_trx

2210: 'EXCLUDE RECREV', 'EXCLUDE RECAPP');
2211:
2212: SELECT nvl(sum(amount_original),0)
2213: INTO l_period_finchrg
2214: FROM ar_cons_inv_trx
2215: WHERE cons_inv_id = l_consinv_id
2216: AND transaction_type IN ('FINANCE CHARGE');
2217:
2218: SELECT nvl(sum(amount_original),0)

Line 2220: FROM ar_cons_inv_trx

2216: AND transaction_type IN ('FINANCE CHARGE');
2217:
2218: SELECT nvl(sum(amount_original),0)
2219: INTO l_period_adj
2220: FROM ar_cons_inv_trx
2221: WHERE cons_inv_id = l_consinv_id
2222: AND transaction_type = 'ADJUSTMENT';
2223:
2224: SELECT nvl(sum(amount_original),0)

Line 2226: FROM ar_cons_inv_trx

2222: AND transaction_type = 'ADJUSTMENT';
2223:
2224: SELECT nvl(sum(amount_original),0)
2225: INTO l_period_credits
2226: FROM ar_cons_inv_trx
2227: WHERE cons_inv_id = l_consinv_id
2228: AND transaction_type IN ('CREDIT_MEMO',
2229: 'XSITE_CMREV','XSITE_CMAPP',
2230: 'EXCLUDE_CMREV', 'EXCLUDE_CMAPP',

Line 2235: FROM ar_cons_inv_trx_lines

2231: 'DELAY_CMAPP');
2232:
2233: SELECT nvl(sum(tax_amount),0)
2234: INTO l_period_tax
2235: FROM ar_cons_inv_trx_lines
2236: WHERE cons_inv_id = l_consinv_id;
2237:
2238:
2239: UPDATE ar_cons_inv

Line 2259: FROM ar_cons_inv_trx IT

2255: UPDATE ar_payment_schedules PS
2256: SET PS.cons_inv_id = l_consinv_id
2257: WHERE PS.payment_schedule_id IN
2258: (SELECT IT.adj_ps_id
2259: FROM ar_cons_inv_trx IT
2260: WHERE IT.cons_inv_id = l_consinv_id
2261: AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO',
2262: 'RECEIPT'));
2263:

Line 2268: FROM ar_cons_inv_trx IT

2264: UPDATE ar_payment_schedules PS
2265: SET PS.cons_inv_id_rev = l_consinv_id
2266: WHERE PS.payment_schedule_id IN
2267: (SELECT IT.adj_ps_id
2268: FROM ar_cons_inv_trx IT
2269: WHERE IT.cons_inv_id = l_consinv_id
2270: AND IT.transaction_type = 'RECEIPT REV');
2271:
2272: write_debug_and_log('Updating AR_RECEIVABLE_APPLICATIONS');

Line 2278: FROM ar_cons_inv_trx IT

2274: UPDATE ar_receivable_applications RA
2275: SET RA.cons_inv_id = l_consinv_id
2276: WHERE RA.receivable_application_id IN
2277: (SELECT IT.adj_ps_id
2278: FROM ar_cons_inv_trx IT
2279: WHERE IT.cons_inv_id = l_consinv_id
2280: AND IT.transaction_type IN ('XSITE RECREV',
2281: 'XSITE_CMREV',
2282: 'XCURR RECREV',

Line 2291: FROM ar_cons_inv_trx IT

2287: UPDATE ar_receivable_applications RA
2288: SET RA.cons_inv_id_to = l_consinv_id
2289: WHERE RA.receivable_application_id IN
2290: (SELECT IT.adj_ps_id
2291: FROM ar_cons_inv_trx IT
2292: WHERE IT.cons_inv_id = l_consinv_id
2293: AND IT.transaction_type IN ('XSITE RECAPP',
2294: 'XSITE_CMAPP',
2295: 'XCURR RECAPP',

Line 2305: (SELECT /+ index (IT AR_CONS_INV_TRX_N1) /

2301:
2302: UPDATE ar_adjustments RA
2303: SET RA.cons_inv_id = l_consinv_id
2304: WHERE RA.adjustment_id IN
2305: (SELECT /*+ index (IT AR_CONS_INV_TRX_N1) */
2306: IT.adj_ps_id
2307: FROM ar_cons_inv_trx IT
2308: WHERE IT.cons_inv_id = l_consinv_id
2309: AND IT.transaction_type = 'ADJUSTMENT');

Line 2307: FROM ar_cons_inv_trx IT

2303: SET RA.cons_inv_id = l_consinv_id
2304: WHERE RA.adjustment_id IN
2305: (SELECT /*+ index (IT AR_CONS_INV_TRX_N1) */
2306: IT.adj_ps_id
2307: FROM ar_cons_inv_trx IT
2308: WHERE IT.cons_inv_id = l_consinv_id
2309: AND IT.transaction_type = 'ADJUSTMENT');
2310:
2311: write_debug_and_log('Updating AR_CONS_INV');

Line 2320: FROM ar_cons_inv_trx IT

2316: SET printing_original_date = nvl(printing_original_date, SYSDATE),
2317: printing_last_printed = nvl(printing_last_printed, SYSDATE)
2318: WHERE trx.trx_number IN
2319: (SELECT trx_number
2320: FROM ar_cons_inv_trx IT
2321: WHERE IT.cons_inv_id = l_consinv_id );
2322:
2323: --Get the Aging information and update the
2324: -- aging buckets on the Bill

Line 2379: -- into ar_cons_inv_trx.

2375: -- commmented out the following code for bug 6488683
2376: -- This is moved to the process_override procedure and the update is done for
2377: -- individual transactions instead of doing it as a bulk here.
2378: -- This is in order to get the new payment schedule id while inserting
2379: -- into ar_cons_inv_trx.
2380: /*
2381: -- Following is the update for all data overridden in TRX table
2382: IF l_tab_idx > 0 THEN
2383: write_debug_and_log('Override : Updating RA_CUSTOMER_TRX');

Line 2472: ar_cons_inv_trx IT,

2468:
2469: CURSOR c_pending_trx IS
2470: SELECT PS.customer_trx_id
2471: FROM ar_payment_schedules PS,
2472: ar_cons_inv_trx IT,
2473: ar_cons_inv CI
2474: WHERE
2475: CI.print_status = 'PENDING'
2476: AND IT.cons_inv_id = CI.cons_inv_id

Line 2497: ar_cons_inv_trx IT,

2493: 1)
2494: WHERE CT.customer_trx_id IN
2495: (SELECT PS.customer_trx_id
2496: FROM ar_payment_schedules PS,
2497: ar_cons_inv_trx IT,
2498: ar_cons_inv CI
2499: WHERE (
2500: (P_print_option = 'REPRINT'
2501: AND CI.cons_inv_id=nvl(P_consinv_id,CI.cons_inv_id)