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

Line 69: UPDATE ar_cons_inv

65: *----------------------------------------------------------------------------*/
66: PROCEDURE reprint (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
67:
68: BEGIN
69: UPDATE ar_cons_inv
70: SET print_status = 'PENDING',
71: last_update_date = arp_global.last_update_date,
72: last_updated_by = arp_global.last_updated_by,
73: last_update_login = arp_global.last_update_login

Line 136: UPDATE ar_cons_inv

132: write_debug_and_log ( ' P_consinv_num_high: ' || P_consinv_num_high);
133: write_debug_and_log ( ' P_request_id : ' || P_request_id);
134:
135: --Bug 10023214.
136: UPDATE ar_cons_inv
137: SET status = 'ACCEPTED',
138: last_update_date = arp_global.last_update_date,
139: last_updated_by = arp_global.last_updated_by,
140: last_update_login = arp_global.last_update_login

Line 230: UPDATE ar_cons_inv

226: write_debug_and_log ( ' P_consinv_num_high: ' || P_consinv_num_high);
227: write_debug_and_log ( ' P_request_id : ' || P_request_id);
228:
229: --Bug 10023214
230: UPDATE ar_cons_inv
231: SET status = 'PRE_REJECTED',
232: print_status = 'PRINTED',
233: last_update_date = arp_global.last_update_date,
234: last_updated_by = arp_global.last_updated_by,

Line 266: ar_cons_inv_trx IT,

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

Line 267: ar_cons_inv CI

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')

Line 270: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

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

Line 278: FROM ar_cons_inv CI,

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,
279: ar_cons_inv_trx IT
280: WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO', 'DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK',
281: 'RECEIPT')
282: AND CI.cons_inv_id = IT.cons_inv_id

Line 279: ar_cons_inv_trx IT

275: SET cons_inv_id = NULL
276: WHERE payment_schedule_id IN
277: (SELECT IT.adj_ps_id
278: FROM ar_cons_inv CI,
279: ar_cons_inv_trx IT
280: WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO', 'DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK',
281: 'RECEIPT')
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

Line 283: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

279: ar_cons_inv_trx IT
280: WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO', 'DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK',
281: 'RECEIPT')
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

Line 290: FROM ar_cons_inv CI,

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,
291: ar_cons_inv_trx IT
292: WHERE IT.transaction_type = 'RECEIPT REV'
293: AND CI.cons_inv_id = IT.cons_inv_id
294: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

Line 291: ar_cons_inv_trx IT

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,
291: ar_cons_inv_trx IT
292: WHERE IT.transaction_type = 'RECEIPT REV'
293: AND CI.cons_inv_id = IT.cons_inv_id
294: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
295: where i.status = 'PRE_REJECTED'));

Line 294: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

290: FROM ar_cons_inv CI,
291: ar_cons_inv_trx IT
292: WHERE IT.transaction_type = 'RECEIPT REV'
293: AND CI.cons_inv_id = IT.cons_inv_id
294: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
295: where i.status = 'PRE_REJECTED'));
296:
297: UPDATE ar_receivable_applications
298: SET cons_inv_id = NULL

Line 301: FROM ar_cons_inv CI,

297: UPDATE ar_receivable_applications
298: SET cons_inv_id = NULL
299: WHERE receivable_application_id IN
300: (SELECT IT.adj_ps_id
301: FROM ar_cons_inv CI,
302: ar_cons_inv_trx IT
303: WHERE IT.transaction_type IN ('XSITE RECREV', 'XSITE_CMREV',
304: 'XCURR RECREV', 'XSITE XCURR RECREV',
305: 'EXCLUDE RECREV', 'EXCLUDE_CMREV','RECEIPT ADJUST') /*Bug 9189970 */

Line 302: ar_cons_inv_trx IT

298: SET cons_inv_id = NULL
299: WHERE receivable_application_id IN
300: (SELECT IT.adj_ps_id
301: FROM ar_cons_inv CI,
302: ar_cons_inv_trx IT
303: WHERE IT.transaction_type IN ('XSITE RECREV', 'XSITE_CMREV',
304: 'XCURR RECREV', 'XSITE XCURR RECREV',
305: 'EXCLUDE RECREV', 'EXCLUDE_CMREV','RECEIPT ADJUST') /*Bug 9189970 */
306: AND CI.cons_inv_id = IT.cons_inv_id

Line 307: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

303: WHERE IT.transaction_type IN ('XSITE RECREV', 'XSITE_CMREV',
304: 'XCURR RECREV', 'XSITE XCURR RECREV',
305: 'EXCLUDE RECREV', 'EXCLUDE_CMREV','RECEIPT ADJUST') /*Bug 9189970 */
306: AND CI.cons_inv_id = IT.cons_inv_id
307: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
308: where i.status = 'PRE_REJECTED'));
309:
310: UPDATE ar_receivable_applications
311: SET cons_inv_id_to = NULL

Line 314: FROM ar_cons_inv CI,

310: UPDATE ar_receivable_applications
311: SET cons_inv_id_to = NULL
312: WHERE receivable_application_id IN
313: (SELECT IT.adj_ps_id
314: FROM ar_cons_inv CI,
315: ar_cons_inv_trx IT
316: WHERE IT.transaction_type IN ('XSITE RECAPP','XSITE_CMAPP',
317: 'XCURR RECAPP', 'XSITE XCURR RECAPP' ,
318: 'EXCLUDE RECAPP', 'EXCLUDE_CMAPP',

Line 315: ar_cons_inv_trx IT

311: SET cons_inv_id_to = NULL
312: WHERE receivable_application_id IN
313: (SELECT IT.adj_ps_id
314: FROM ar_cons_inv CI,
315: ar_cons_inv_trx IT
316: WHERE IT.transaction_type IN ('XSITE RECAPP','XSITE_CMAPP',
317: 'XCURR RECAPP', 'XSITE XCURR RECAPP' ,
318: 'EXCLUDE RECAPP', 'EXCLUDE_CMAPP',
319: 'DELAY_CMAPP')

Line 321: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

317: 'XCURR RECAPP', 'XSITE XCURR RECAPP' ,
318: 'EXCLUDE RECAPP', 'EXCLUDE_CMAPP',
319: 'DELAY_CMAPP')
320: AND CI.cons_inv_id = IT.cons_inv_id
321: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
322: where i.status = 'PRE_REJECTED'));
323:
324:
325: UPDATE ar_adjustments

Line 329: FROM ar_cons_inv CI,

325: UPDATE ar_adjustments
326: SET cons_inv_id = NULL
327: WHERE adjustment_id IN
328: (SELECT IT.adj_ps_id
329: FROM ar_cons_inv CI,
330: ar_cons_inv_trx IT
331: WHERE IT.transaction_type = 'ADJUSTMENT'
332: AND CI.cons_inv_id = IT.cons_inv_id
333: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

Line 330: ar_cons_inv_trx IT

326: SET cons_inv_id = NULL
327: WHERE adjustment_id IN
328: (SELECT IT.adj_ps_id
329: FROM ar_cons_inv CI,
330: ar_cons_inv_trx IT
331: WHERE IT.transaction_type = 'ADJUSTMENT'
332: AND CI.cons_inv_id = IT.cons_inv_id
333: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
334: where i.status = 'PRE_REJECTED'));

Line 333: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

329: FROM ar_cons_inv CI,
330: ar_cons_inv_trx IT
331: WHERE IT.transaction_type = 'ADJUSTMENT'
332: AND CI.cons_inv_id = IT.cons_inv_id
333: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
334: where i.status = 'PRE_REJECTED'));
335:
336:
337: DELETE FROM ar_cons_inv_trx_lines

Line 337: DELETE FROM ar_cons_inv_trx_lines

333: AND CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
334: where i.status = 'PRE_REJECTED'));
335:
336:
337: DELETE FROM ar_cons_inv_trx_lines
338: WHERE cons_inv_id IN
339: (SELECT CI.cons_inv_id
340: FROM ar_cons_inv CI
341: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

Line 340: FROM ar_cons_inv CI

336:
337: DELETE FROM ar_cons_inv_trx_lines
338: WHERE cons_inv_id IN
339: (SELECT CI.cons_inv_id
340: FROM ar_cons_inv CI
341: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
342: where i.status = 'PRE_REJECTED'));
343:
344: DELETE FROM ar_cons_inv_trx

Line 341: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

337: DELETE FROM ar_cons_inv_trx_lines
338: WHERE cons_inv_id IN
339: (SELECT CI.cons_inv_id
340: FROM ar_cons_inv CI
341: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
342: where i.status = 'PRE_REJECTED'));
343:
344: DELETE FROM ar_cons_inv_trx
345: WHERE cons_inv_id IN

Line 344: DELETE FROM ar_cons_inv_trx

340: FROM ar_cons_inv CI
341: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
342: where i.status = 'PRE_REJECTED'));
343:
344: DELETE FROM ar_cons_inv_trx
345: WHERE cons_inv_id IN
346: (SELECT CI.cons_inv_id
347: FROM ar_cons_inv CI
348: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

Line 347: FROM ar_cons_inv CI

343:
344: DELETE FROM ar_cons_inv_trx
345: WHERE cons_inv_id IN
346: (SELECT CI.cons_inv_id
347: FROM ar_cons_inv CI
348: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
349: where i.status = 'PRE_REJECTED'));
350:
351: UPDATE ar_cons_inv CI

Line 348: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

344: DELETE FROM ar_cons_inv_trx
345: WHERE cons_inv_id IN
346: (SELECT CI.cons_inv_id
347: FROM ar_cons_inv CI
348: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
349: where i.status = 'PRE_REJECTED'));
350:
351: UPDATE ar_cons_inv CI
352: SET status = 'REJECTED'

Line 351: UPDATE ar_cons_inv CI

347: FROM ar_cons_inv CI
348: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
349: where i.status = 'PRE_REJECTED'));
350:
351: UPDATE ar_cons_inv CI
352: SET status = 'REJECTED'
353: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
354: where i.status = 'PRE_REJECTED');
355:

Line 353: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i

349: where i.status = 'PRE_REJECTED'));
350:
351: UPDATE ar_cons_inv CI
352: SET status = 'REJECTED'
353: WHERE CI.cons_inv_id in (select cons_inv_id from ar_cons_inv i
354: where i.status = 'PRE_REJECTED');
355:
356: commit;
357:

Line 948: ' FROM ar_cons_inv CI ' ||

944: ' ''A'', CP.cons_inv_flag, ' ||
945: ' ''S'', SP.cons_inv_flag) = ''Y'' ' ||
946: 'AND NOT EXISTS ' ||
947: ' (SELECT NULL ' ||
948: ' FROM ar_cons_inv CI ' ||
949: ' WHERE CI.site_use_id = site_uses.site_use_id ' ||
950: ' AND CI.billing_date >= :billing_date ' ||
951: ' AND CI.currency_code = :currency ' ||
952: ' AND CI.status <> ''REJECTED'') ' ||

Line 955: ' FROM ar_cons_inv CI2 ' ||

951: ' AND CI.currency_code = :currency ' ||
952: ' AND CI.status <> ''REJECTED'') ' ||
953: 'AND NOT EXISTS ' ||
954: ' (SELECT NULL ' ||
955: ' FROM ar_cons_inv CI2 ' ||
956: ' WHERE CI2.site_use_id = site_uses.site_use_id ' ||
957: ' AND CI2.currency_code = :currency ' ||
958: ' AND CI2.status = ''DRAFT'') ';
959:

Line 1171: SELECT ar_cons_inv_s.NEXTVAL

1167: END IF;
1168:
1169: /** get next billing invoice id, create header with zero totals. **/
1170:
1171: SELECT ar_cons_inv_s.NEXTVAL
1172: INTO l_consinv_id
1173: FROM dual;
1174:
1175:

Line 1204: FROM ar_cons_inv CI1

1200: BEGIN
1201:
1202: SELECT sum(ending_balance), max(nvl(billing_date,cut_off_date))
1203: INTO l_beginning_balance, l_last_bill_date
1204: FROM ar_cons_inv CI1
1205: WHERE CI1.site_use_id = L_sites.site_id
1206: AND CI1.currency_code = P_currency
1207: AND (CI1.status IN ('ACCEPTED', 'FINAL')
1208: AND nvl(CI1.billing_date,CI1.cut_off_date) =

Line 1210: FROM ar_cons_inv CI2

1206: AND CI1.currency_code = P_currency
1207: AND (CI1.status IN ('ACCEPTED', 'FINAL')
1208: AND nvl(CI1.billing_date,CI1.cut_off_date) =
1209: (SELECT max(nvl(CI2.billing_date,CI2.cut_off_date))
1210: FROM ar_cons_inv CI2
1211: WHERE CI2.site_use_id = L_sites.site_id
1212: AND CI2.currency_code = P_currency
1213: AND CI2.status IN ('ACCEPTED', 'FINAL')));
1214:

Line 1309: write_debug_and_log('......insert to ar_cons_inv, ID: ' || TO_CHAR(l_consinv_id) ||

1305: note it is possible that only the header will created if no
1306: transactions are found.
1307: **/
1308:
1309: write_debug_and_log('......insert to ar_cons_inv, ID: ' || TO_CHAR(l_consinv_id) ||
1310: ' number: ' || l_cons_billno);
1311:
1312:
1313: INSERT INTO ar_cons_inv (cons_inv_id,

Line 1313: INSERT INTO ar_cons_inv (cons_inv_id,

1309: write_debug_and_log('......insert to ar_cons_inv, ID: ' || TO_CHAR(l_consinv_id) ||
1310: ' number: ' || l_cons_billno);
1311:
1312:
1313: INSERT INTO ar_cons_inv (cons_inv_id,
1314: cons_billing_number,
1315: customer_id,
1316: site_use_id,
1317: concurrent_request_id,

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

1385: -- after process_override whether an update has taken place by comparing
1386: -- these two variables. bug 6488683
1387: l_check_override := l_tab_idx;
1388:
1389: -- Prior to inserting into ar_cons_inv_trx, need to perform validation and override if necessary
1390: IF nvl(L_inv_trx.term_id,'-1') <> L_terms.term_id THEN
1391:
1392: IF L_inv_trx.class = 'CM' THEN
1393: /* Bug 9392028 */

Line 1423: FROM ar_cons_inv c,

1419: -- AND ar_bfb_utils_pvt.is_payment_term_bfb(ct.term_id) = 'Y' -- commented on 08mar2012
1420: AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1421: ---Added below condition to verify if associated INV is there in prior BFB
1422: AND EXISTS (SELECT '*'
1423: FROM ar_cons_inv c,
1424: ar_cons_inv_trx ctrx
1425: WHERE ct.customer_trx_id = ctrx.customer_trx_id
1426: AND c.cons_inv_id = ctrx.cons_inv_id
1427: AND c.status <> 'REJECTED');

Line 1424: ar_cons_inv_trx ctrx

1420: AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1421: ---Added below condition to verify if associated INV is there in prior BFB
1422: AND EXISTS (SELECT '*'
1423: FROM ar_cons_inv c,
1424: ar_cons_inv_trx ctrx
1425: WHERE ct.customer_trx_id = ctrx.customer_trx_id
1426: AND c.cons_inv_id = ctrx.cons_inv_id
1427: AND c.status <> 'REJECTED');
1428:

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

1523: l_new_schedule_id := L_inv_trx.schedule_id;
1524: END IF;
1525:
1526: write_debug_and_log('new payment schedule id ' || l_new_schedule_id );
1527: write_debug_and_log('insert to ar_cons_inv_trx for ' || L_inv_trx.trx_id);
1528:
1529: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1530: transaction_type,
1531: trx_number,

Line 1529: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1525:
1526: write_debug_and_log('new payment schedule id ' || l_new_schedule_id );
1527: write_debug_and_log('insert to ar_cons_inv_trx for ' || L_inv_trx.trx_id);
1528:
1529: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1530: transaction_type,
1531: trx_number,
1532: transaction_date,
1533: amount_original,

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

1561: /** For audit purposes, insert detail line information even if
1562: reporting in summary. Also note that cons_inv_line_number
1563: is one value for detail lines for a specific invoice. **/
1564:
1565: write_debug_and_log('insert to ar_cons_inv_trx_lines for ' || L_inv_trx.trx_id);
1566: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1567: cons_inv_line_number,
1568: customer_trx_id,
1569: customer_trx_line_id,

Line 1566: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,

1562: reporting in summary. Also note that cons_inv_line_number
1563: is one value for detail lines for a specific invoice. **/
1564:
1565: write_debug_and_log('insert to ar_cons_inv_trx_lines for ' || L_inv_trx.trx_id);
1566: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1567: cons_inv_line_number,
1568: customer_trx_id,
1569: customer_trx_line_id,
1570: line_number,

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

1594: AND line_type NOT IN ('TAX', 'FREIGHT');
1595:
1596: /** now update lines with associated tax line **/
1597:
1598: write_debug_and_log('update ar_cons_inv_trx_lines for TAX');
1599: OPEN c_tax(L_inv_trx.trx_id);
1600: LOOP
1601: FETCH c_tax BULK COLLECT INTO
1602: l_line_id , l_tax_sum, l_include_tax_sum LIMIT l_bulk_fetch_rows;

Line 1607: UPDATE ar_cons_inv_trx_lines

1603:
1604: -- 1. Update tax_amount
1605: -- 2. Exclude inclusive tax amount total from extended_amount
1606: FORALL i IN 1..l_line_id.count
1607: UPDATE ar_cons_inv_trx_lines
1608: SET tax_amount = l_tax_sum(i),
1609: extended_amount = extended_amount - l_include_tax_sum(i)
1610: WHERE customer_trx_id = L_inv_trx.trx_id
1611: AND customer_trx_line_id = l_line_id(i) ;

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

1614: END LOOP;
1615: CLOSE c_tax;
1616:
1617: /** now create 1 summary row for freight **/
1618: write_debug_and_log('insert to ar_cons_inv_trx_lines for FREIGHT');
1619: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1620: cons_inv_line_number,
1621: customer_trx_id,
1622: customer_trx_line_id,

Line 1619: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,

1615: CLOSE c_tax;
1616:
1617: /** now create 1 summary row for freight **/
1618: write_debug_and_log('insert to ar_cons_inv_trx_lines for FREIGHT');
1619: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1620: cons_inv_line_number,
1621: customer_trx_id,
1622: customer_trx_line_id,
1623: line_number,

Line 1675: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1671: (fin charge is in next select ACTIVITY 1A) */
1672:
1673: write_debug_and_log('.........ACTIVITY 1');
1674:
1675: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1676: transaction_type,
1677: trx_number,
1678: transaction_date,
1679: amount_original,

Line 1709: FROM ar_cons_inv c,

1705: AND PS.invoice_currency_code = P_currency
1706: AND PS.class||'' <> 'GUAR'
1707: AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1708: AND EXISTS (SELECT '*'
1709: FROM ar_cons_inv c,
1710: ar_cons_inv_trx ctrx
1711: WHERE adj.customer_trx_id = ctrx.customer_trx_id
1712: AND c.cons_inv_id = ctrx.cons_inv_id
1713: AND c.status <> 'REJECTED');

Line 1710: ar_cons_inv_trx ctrx

1706: AND PS.class||'' <> 'GUAR'
1707: AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1708: AND EXISTS (SELECT '*'
1709: FROM ar_cons_inv c,
1710: ar_cons_inv_trx ctrx
1711: WHERE adj.customer_trx_id = ctrx.customer_trx_id
1712: AND c.cons_inv_id = ctrx.cons_inv_id
1713: AND c.status <> 'REJECTED');
1714:

Line 1721: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1717: pick up all adjustments (only finance charges) generated against this BFB site */
1718:
1719: write_debug_and_log('.........ACTIVITY 1A');
1720:
1721: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1722: transaction_type,
1723: trx_number,
1724: transaction_date,
1725: amount_original,

Line 1755: FROM ar_cons_inv c,

1751: AND PS.invoice_currency_code = P_currency
1752: AND PS.class||'' <> 'GUAR'
1753: AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1754: AND EXISTS (SELECT '*'
1755: FROM ar_cons_inv c,
1756: ar_cons_inv_trx ctrx
1757: WHERE adj.customer_trx_id = ctrx.customer_trx_id
1758: AND c.cons_inv_id = ctrx.cons_inv_id
1759: AND c.status <> 'REJECTED');

Line 1756: ar_cons_inv_trx ctrx

1752: AND PS.class||'' <> 'GUAR'
1753: AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1754: AND EXISTS (SELECT '*'
1755: FROM ar_cons_inv c,
1756: ar_cons_inv_trx ctrx
1757: WHERE adj.customer_trx_id = ctrx.customer_trx_id
1758: AND c.cons_inv_id = ctrx.cons_inv_id
1759: AND c.status <> 'REJECTED');
1760:

Line 1767: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1763: site (in ACTIVITY 4 : we back out amounts applied to diff site) */
1764:
1765: write_debug_and_log('.........ACTIVITY 2');
1766:
1767: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1768: transaction_type,
1769: trx_number,
1770: transaction_date,
1771: amount_original,

Line 1818: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1814: receipts created against this BFB site */
1815:
1816: write_debug_and_log('.........ACTIVITY 3');
1817:
1818: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1819: transaction_type,
1820: trx_number,
1821: transaction_date,
1822: amount_original,

Line 1850: FROM ar_cons_inv c,

1846: AND CR.reversal_date <= l_billing_date
1847: AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1848: AND EXISTS
1849: (SELECT '*'
1850: FROM ar_cons_inv c,
1851: ar_cons_inv_trx ctrx
1852: WHERE PS.payment_schedule_id = ctrx.adj_ps_id
1853: AND c.cons_inv_id = ctrx.cons_inv_id
1854: AND c.status <> 'REJECTED');

Line 1851: ar_cons_inv_trx ctrx

1847: AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
1848: AND EXISTS
1849: (SELECT '*'
1850: FROM ar_cons_inv c,
1851: ar_cons_inv_trx ctrx
1852: WHERE PS.payment_schedule_id = ctrx.adj_ps_id
1853: AND c.cons_inv_id = ctrx.cons_inv_id
1854: AND c.status <> 'REJECTED');
1855:

Line 1862: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1858: a different site */
1859:
1860: write_debug_and_log('.........ACTIVITY 4');
1861:
1862: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1863: transaction_type,
1864: trx_number,
1865: transaction_date,
1866: amount_original,

Line 1905: FROM ar_cons_inv c,

1901: OR RA.amount_applied_from IS NOT NULL) --Bug 8208763
1902: -- OR nvl(ps_inv.exclude_from_cons_bill_flag, 'N') = 'Y')
1903: AND EXISTS
1904: (SELECT '*'
1905: FROM ar_cons_inv c,
1906: ar_cons_inv_trx ctrx
1907: WHERE RA.payment_schedule_id = ctrx.adj_ps_id
1908: AND c.cons_inv_id = ctrx.cons_inv_id
1909: AND c.status <> 'REJECTED')

Line 1906: ar_cons_inv_trx ctrx

1902: -- OR nvl(ps_inv.exclude_from_cons_bill_flag, 'N') = 'Y')
1903: AND EXISTS
1904: (SELECT '*'
1905: FROM ar_cons_inv c,
1906: ar_cons_inv_trx ctrx
1907: WHERE RA.payment_schedule_id = ctrx.adj_ps_id
1908: AND c.cons_inv_id = ctrx.cons_inv_id
1909: AND c.status <> 'REJECTED')
1910: AND EXISTS --bug 12349325

Line 1912: FROM ar_cons_inv c,

1908: AND c.cons_inv_id = ctrx.cons_inv_id
1909: AND c.status <> 'REJECTED')
1910: AND EXISTS --bug 12349325
1911: (SELECT '*'
1912: FROM ar_cons_inv c,
1913: ar_cons_inv_trx ctrx
1914: WHERE ps_inv.payment_schedule_id = ctrx.adj_ps_id
1915: AND c.cons_inv_id = ctrx.cons_inv_id
1916: AND c.status <> 'REJECTED');

Line 1913: ar_cons_inv_trx ctrx

1909: AND c.status <> 'REJECTED')
1910: AND EXISTS --bug 12349325
1911: (SELECT '*'
1912: FROM ar_cons_inv c,
1913: ar_cons_inv_trx ctrx
1914: WHERE ps_inv.payment_schedule_id = ctrx.adj_ps_id
1915: AND c.cons_inv_id = ctrx.cons_inv_id
1916: AND c.status <> 'REJECTED');
1917:

Line 1923: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1919: different (or null) site, but applied to TRX with this BFB Site */
1920:
1921: write_debug_and_log('.........ACTIVITY 5');
1922:
1923: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1924: transaction_type,
1925: trx_number,
1926: transaction_date,
1927: amount_original,

Line 1966: FROM ar_cons_inv c,

1962: OR ra.amount_applied_from IS NOT NULL
1963: OR nvl(ps_cash.exclude_from_cons_bill_flag, 'N') = 'Y')
1964: AND EXISTS
1965: (SELECT '*'
1966: FROM ar_cons_inv c,
1967: ar_cons_inv_trx ctrx
1968: WHERE ps_inv.customer_trx_id = ctrx.customer_trx_id
1969: AND c.cons_inv_id = ctrx.cons_inv_id
1970: AND c.status <> 'REJECTED');

Line 1967: ar_cons_inv_trx ctrx

1963: OR nvl(ps_cash.exclude_from_cons_bill_flag, 'N') = 'Y')
1964: AND EXISTS
1965: (SELECT '*'
1966: FROM ar_cons_inv c,
1967: ar_cons_inv_trx ctrx
1968: WHERE ps_inv.customer_trx_id = ctrx.customer_trx_id
1969: AND c.cons_inv_id = ctrx.cons_inv_id
1970: AND c.status <> 'REJECTED');
1971:

Line 1983: INSERT INTO ar_cons_inv_trx (cons_inv_id,

1979: in ACTIVITY 5, otherwise the receipt application is recorded twice */
1980:
1981: write_debug_and_log('.........ACTIVITY 6');
1982:
1983: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1984: transaction_type,
1985: trx_number,
1986: transaction_date,
1987: amount_original,

Line 2004: ar_cons_inv_trx inv_trx,

2000: RA.receivable_application_id,
2001: NULL,
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

Line 2029: INSERT INTO ar_cons_inv_trx (cons_inv_id,

2025: we need to exclude it. */
2026:
2027: write_debug_and_log('.........ACTIVITY 7');
2028:
2029: INSERT INTO ar_cons_inv_trx (cons_inv_id,
2030: transaction_type,
2031: trx_number,
2032: transaction_date,
2033: amount_original,

Line 2050: ar_cons_inv_trx inv_trx,

2046: RA.receivable_application_id,
2047: NULL,
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

Line 2072: INSERT INTO ar_cons_inv_trx (cons_inv_id,

2068: applied to an invoice having a different site */
2069:
2070: write_debug_and_log('.........ACTIVITY 8');
2071:
2072: INSERT INTO ar_cons_inv_trx (cons_inv_id,
2073: transaction_type,
2074: trx_number,
2075: transaction_date,
2076: amount_original,

Line 2097: AR_CONS_INV_TRX CTRX,

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
2101: AND RA.status = 'APP'

Line 2098: AR_CONS_INV C

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
2101: AND RA.status = 'APP'
2102: AND RA.application_type = 'CM'

Line 2120: INSERT INTO ar_cons_inv_trx (cons_inv_id,

2116: INV is not pulled into BFB yet, need to exclude */
2117:
2118: write_debug_and_log('.........ACTIVITY 8A');
2119:
2120: INSERT INTO ar_cons_inv_trx (cons_inv_id,
2121: transaction_type,
2122: trx_number,
2123: transaction_date,
2124: amount_original,

Line 2156: FROM ar_cons_inv c,

2152: AND PS_INV.payment_schedule_id = RA.applied_payment_schedule_id
2153: AND PS_INV.customer_site_use_id = L_sites.site_id
2154: AND NOT EXISTS
2155: (SELECT /*+ no_unnest */ '*'
2156: FROM ar_cons_inv c,
2157: ar_cons_inv_trx ctrx
2158: WHERE PS_INV.customer_trx_id = ctrx.customer_trx_id
2159: AND c.cons_inv_id = ctrx.cons_inv_id
2160: AND c.status <> 'REJECTED')

Line 2157: ar_cons_inv_trx ctrx

2153: AND PS_INV.customer_site_use_id = L_sites.site_id
2154: AND NOT EXISTS
2155: (SELECT /*+ no_unnest */ '*'
2156: FROM ar_cons_inv c,
2157: ar_cons_inv_trx ctrx
2158: WHERE PS_INV.customer_trx_id = ctrx.customer_trx_id
2159: AND c.cons_inv_id = ctrx.cons_inv_id
2160: AND c.status <> 'REJECTED')
2161: AND EXISTS

Line 2163: FROM ar_cons_inv c,

2159: AND c.cons_inv_id = ctrx.cons_inv_id
2160: AND c.status <> 'REJECTED')
2161: AND EXISTS
2162: (SELECT /*+ no_unnest */ '*'
2163: FROM ar_cons_inv c,
2164: ar_cons_inv_trx ctrx
2165: WHERE PS_CM.customer_trx_id = ctrx.customer_trx_id
2166: AND c.cons_inv_id = ctrx.cons_inv_id
2167: AND c.status <> 'REJECTED');

Line 2164: ar_cons_inv_trx ctrx

2160: AND c.status <> 'REJECTED')
2161: AND EXISTS
2162: (SELECT /*+ no_unnest */ '*'
2163: FROM ar_cons_inv c,
2164: ar_cons_inv_trx ctrx
2165: WHERE PS_CM.customer_trx_id = ctrx.customer_trx_id
2166: AND c.cons_inv_id = ctrx.cons_inv_id
2167: AND c.status <> 'REJECTED');
2168:

Line 2176: INSERT INTO ar_cons_inv_trx (cons_inv_id,

2172: part of an BFB yet */
2173:
2174: write_debug_and_log('.........ACTIVITY 9');
2175:
2176: INSERT INTO ar_cons_inv_trx (cons_inv_id,
2177: transaction_type,
2178: trx_number,
2179: transaction_date,
2180: amount_original,

Line 2212: FROM ar_cons_inv c,

2208: AND PS_CM.payment_schedule_id = RA.payment_schedule_id
2209: AND ( PS_CM.customer_site_use_id <> PS_INV.customer_site_use_id
2210: or nvl(PS_CM.exclude_from_cons_bill_flag, 'N') = 'Y')
2211: AND EXISTS (SELECT '*'
2212: FROM ar_cons_inv c,
2213: ar_cons_inv_trx ctrx
2214: WHERE PS_INV.customer_trx_id = ctrx.customer_trx_id
2215: AND c.cons_inv_id = ctrx.cons_inv_id
2216: AND c.status <> 'REJECTED');

Line 2213: ar_cons_inv_trx ctrx

2209: AND ( PS_CM.customer_site_use_id <> PS_INV.customer_site_use_id
2210: or nvl(PS_CM.exclude_from_cons_bill_flag, 'N') = 'Y')
2211: AND EXISTS (SELECT '*'
2212: FROM ar_cons_inv c,
2213: ar_cons_inv_trx ctrx
2214: WHERE PS_INV.customer_trx_id = ctrx.customer_trx_id
2215: AND c.cons_inv_id = ctrx.cons_inv_id
2216: AND c.status <> 'REJECTED');
2217:

Line 2227: INSERT INTO ar_cons_inv_trx (cons_inv_id,

2223: l_customer_id := L_sites.customer_id;
2224:
2225: write_debug_and_log('.........ACTIVITY 10 : Receipts with No Location');
2226:
2227: INSERT INTO ar_cons_inv_trx (cons_inv_id,
2228: transaction_type,
2229: trx_number,
2230: transaction_date,
2231: amount_original,

Line 2279: INSERT INTO ar_cons_inv_trx (cons_inv_id,

2275: receipts created without site */
2276:
2277: write_debug_and_log('.........ACTIVITY 11: Reversal of receipts with no location');
2278:
2279: INSERT INTO ar_cons_inv_trx (cons_inv_id,
2280: transaction_type,
2281: trx_number,
2282: transaction_date,
2283: amount_original,

Line 2312: FROM ar_cons_inv c,

2308: AND CR.reversal_date <= l_billing_date
2309: AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
2310: AND EXISTS
2311: (SELECT '*'
2312: FROM ar_cons_inv c,
2313: ar_cons_inv_trx ctrx
2314: WHERE PS.payment_schedule_id = ctrx.adj_ps_id
2315: AND c.cons_inv_id = ctrx.cons_inv_id
2316: AND c.status <> 'REJECTED');

Line 2313: ar_cons_inv_trx ctrx

2309: AND nvl(PS.exclude_from_cons_bill_flag, 'N') <> 'Y'
2310: AND EXISTS
2311: (SELECT '*'
2312: FROM ar_cons_inv c,
2313: ar_cons_inv_trx ctrx
2314: WHERE PS.payment_schedule_id = ctrx.adj_ps_id
2315: AND c.cons_inv_id = ctrx.cons_inv_id
2316: AND c.status <> 'REJECTED');
2317: END IF;

Line 2350: INSERT INTO ar_cons_inv_trx (cons_inv_id,

2346: */
2347:
2348: write_debug_and_log('.........ACTIVITY 12 : RECEIPT ADJUSTMENT');
2349:
2350: INSERT INTO ar_cons_inv_trx (cons_inv_id,
2351: transaction_type,
2352: trx_number,
2353: transaction_date,
2354: amount_original,

Line 2387: FROM ar_cons_inv c,

2383: AND inv_trx.CUSTOMER_TRX_ID = RA.APPLIED_CUSTOMER_TRX_ID
2384: AND ra.applied_payment_schedule_id = ps_app.payment_schedule_id
2385: AND EXISTS
2386: (SELECT '*'
2387: FROM ar_cons_inv c,
2388: ar_cons_inv_trx ctrx
2389: WHERE ps_cash.payment_schedule_id = ctrx.adj_ps_id
2390: AND ctrx.transaction_type = 'RECEIPT'
2391: AND c.cons_inv_id = ctrx.cons_inv_id

Line 2388: ar_cons_inv_trx ctrx

2384: AND ra.applied_payment_schedule_id = ps_app.payment_schedule_id
2385: AND EXISTS
2386: (SELECT '*'
2387: FROM ar_cons_inv c,
2388: ar_cons_inv_trx ctrx
2389: WHERE ps_cash.payment_schedule_id = ctrx.adj_ps_id
2390: AND ctrx.transaction_type = 'RECEIPT'
2391: AND c.cons_inv_id = ctrx.cons_inv_id
2392: AND c.status <> 'REJECTED')

Line 2394: FROM ar_cons_inv c,

2390: AND ctrx.transaction_type = 'RECEIPT'
2391: AND c.cons_inv_id = ctrx.cons_inv_id
2392: AND c.status <> 'REJECTED')
2393: AND NOT EXISTS (SELECT '*'
2394: FROM ar_cons_inv c,
2395: ar_cons_inv_trx ctrx
2396: WHERE inv_trx.customer_trx_id = ctrx.customer_trx_id
2397: AND c.cons_inv_id = ctrx.cons_inv_id
2398: AND c.status <> 'REJECTED'

Line 2395: ar_cons_inv_trx ctrx

2391: AND c.cons_inv_id = ctrx.cons_inv_id
2392: AND c.status <> 'REJECTED')
2393: AND NOT EXISTS (SELECT '*'
2394: FROM ar_cons_inv c,
2395: ar_cons_inv_trx ctrx
2396: WHERE inv_trx.customer_trx_id = ctrx.customer_trx_id
2397: AND c.cons_inv_id = ctrx.cons_inv_id
2398: AND c.status <> 'REJECTED'
2399: UNION ALL

Line 2401: FROM ar_cons_inv c,

2397: AND c.cons_inv_id = ctrx.cons_inv_id
2398: AND c.status <> 'REJECTED'
2399: UNION ALL
2400: select '*'
2401: FROM ar_cons_inv c,
2402: ar_cons_inv_trx ctrx
2403: where c.cons_inv_id=ps_app.cons_inv_id
2404: AND c.status <> 'REJECTED'
2405: AND ctrx.cons_inv_id=c.cons_inv_id

Line 2402: ar_cons_inv_trx ctrx

2398: AND c.status <> 'REJECTED'
2399: UNION ALL
2400: select '*'
2401: FROM ar_cons_inv c,
2402: ar_cons_inv_trx ctrx
2403: where c.cons_inv_id=ps_app.cons_inv_id
2404: AND c.status <> 'REJECTED'
2405: AND ctrx.cons_inv_id=c.cons_inv_id
2406: AND ctrx.customer_trx_id is null

Line 2434: FROM ar_cons_inv c,

2430: AND ps_cash.invoice_currency_code = P_currency
2431: AND nvl(ps_cash.exclude_from_cons_bill_flag, 'N') <> 'Y'
2432: AND EXISTS
2433: (SELECT '*'
2434: FROM ar_cons_inv c,
2435: ar_cons_inv_trx ctrx
2436: WHERE ps_cash.payment_schedule_id = ctrx.adj_ps_id
2437: AND c.cons_inv_id = ctrx.cons_inv_id
2438: AND ctrx.transaction_type = 'RECEIPT'

Line 2435: ar_cons_inv_trx ctrx

2431: AND nvl(ps_cash.exclude_from_cons_bill_flag, 'N') <> 'Y'
2432: AND EXISTS
2433: (SELECT '*'
2434: FROM ar_cons_inv c,
2435: ar_cons_inv_trx ctrx
2436: WHERE ps_cash.payment_schedule_id = ctrx.adj_ps_id
2437: AND c.cons_inv_id = ctrx.cons_inv_id
2438: AND ctrx.transaction_type = 'RECEIPT'
2439: AND c.status <> 'REJECTED');

Line 2447: FROM ar_cons_inv_trx

2443:
2444: /** For Site: calculate totals **/
2445: SELECT nvl(sum(amount_original),0)
2446: INTO l_period_trx
2447: FROM ar_cons_inv_trx
2448: WHERE cons_inv_id = l_consinv_id
2449: AND transaction_type IN ('INVOICE', 'DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK');
2450:
2451: SELECT nvl(sum(amount_original),0)

Line 2453: FROM ar_cons_inv_trx

2449: AND transaction_type IN ('INVOICE', 'DEBIT_MEMO', 'DEPOSIT', 'CHARGEBACK');
2450:
2451: SELECT nvl(sum(amount_original),0)
2452: INTO l_period_receipts
2453: FROM ar_cons_inv_trx
2454: WHERE cons_inv_id = l_consinv_id
2455: AND transaction_type IN ('RECEIPT','RECEIPT REV','XSITE RECREV',
2456: 'XSITE RECAPP',
2457: 'XCURR RECAPP', 'XCURR RECREV',

Line 2463: FROM ar_cons_inv_trx

2459: 'EXCLUDE RECREV', 'EXCLUDE RECAPP','RECEIPT ADJUST');
2460:
2461: SELECT nvl(sum(amount_original),0)
2462: INTO l_period_finchrg
2463: FROM ar_cons_inv_trx
2464: WHERE cons_inv_id = l_consinv_id
2465: AND transaction_type IN ('FINANCE CHARGE');
2466:
2467: SELECT nvl(sum(amount_original),0)

Line 2469: FROM ar_cons_inv_trx

2465: AND transaction_type IN ('FINANCE CHARGE');
2466:
2467: SELECT nvl(sum(amount_original),0)
2468: INTO l_period_adj
2469: FROM ar_cons_inv_trx
2470: WHERE cons_inv_id = l_consinv_id
2471: AND transaction_type = 'ADJUSTMENT';
2472:
2473: SELECT nvl(sum(amount_original),0)

Line 2475: FROM ar_cons_inv_trx

2471: AND transaction_type = 'ADJUSTMENT';
2472:
2473: SELECT nvl(sum(amount_original),0)
2474: INTO l_period_credits
2475: FROM ar_cons_inv_trx
2476: WHERE cons_inv_id = l_consinv_id
2477: AND transaction_type IN ('CREDIT_MEMO',
2478: 'XSITE_CMREV','XSITE_CMAPP',
2479: 'EXCLUDE_CMREV', 'EXCLUDE_CMAPP',

Line 2484: FROM ar_cons_inv_trx_lines

2480: 'DELAY_CMAPP');
2481:
2482: SELECT nvl(sum(tax_amount),0)
2483: INTO l_period_tax
2484: FROM ar_cons_inv_trx_lines
2485: WHERE cons_inv_id = l_consinv_id;
2486:
2487:
2488: UPDATE ar_cons_inv

Line 2488: UPDATE ar_cons_inv

2484: FROM ar_cons_inv_trx_lines
2485: WHERE cons_inv_id = l_consinv_id;
2486:
2487:
2488: UPDATE ar_cons_inv
2489: SET total_receipts_amt = l_period_receipts,
2490: total_adjustments_amt = l_period_adj,
2491: total_credits_amt = l_period_credits,
2492: total_finance_charges_amt = l_period_finchrg,

Line 2508: FROM ar_cons_inv_trx IT

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:

Line 2517: FROM ar_cons_inv_trx IT

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
2518: WHERE IT.cons_inv_id = l_consinv_id
2519: AND IT.transaction_type = 'RECEIPT REV');
2520:
2521: write_debug_and_log('Updating AR_RECEIVABLE_APPLICATIONS');

Line 2527: FROM ar_cons_inv_trx IT

2523: UPDATE ar_receivable_applications RA
2524: SET RA.cons_inv_id = l_consinv_id
2525: WHERE RA.receivable_application_id IN
2526: (SELECT IT.adj_ps_id
2527: FROM ar_cons_inv_trx IT
2528: WHERE IT.cons_inv_id = l_consinv_id
2529: AND IT.transaction_type IN ('XSITE RECREV',
2530: 'XSITE_CMREV',
2531: 'XCURR RECREV',

Line 2542: FROM ar_cons_inv_trx IT

2538: UPDATE ar_receivable_applications RA
2539: SET RA.cons_inv_id_to = l_consinv_id
2540: WHERE RA.receivable_application_id IN
2541: (SELECT IT.adj_ps_id
2542: FROM ar_cons_inv_trx IT
2543: WHERE IT.cons_inv_id = l_consinv_id
2544: AND IT.transaction_type IN ('XSITE RECAPP',
2545: 'XSITE_CMAPP',
2546: 'XCURR RECAPP',

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

2552:
2553: UPDATE ar_adjustments RA
2554: SET RA.cons_inv_id = l_consinv_id
2555: WHERE RA.adjustment_id IN
2556: (SELECT /*+ index (IT AR_CONS_INV_TRX_N1) */
2557: IT.adj_ps_id
2558: FROM ar_cons_inv_trx IT
2559: WHERE IT.cons_inv_id = l_consinv_id
2560: AND IT.transaction_type = 'ADJUSTMENT');

Line 2558: FROM ar_cons_inv_trx IT

2554: SET RA.cons_inv_id = l_consinv_id
2555: WHERE RA.adjustment_id IN
2556: (SELECT /*+ index (IT AR_CONS_INV_TRX_N1) */
2557: IT.adj_ps_id
2558: FROM ar_cons_inv_trx IT
2559: WHERE IT.cons_inv_id = l_consinv_id
2560: AND IT.transaction_type = 'ADJUSTMENT');
2561:
2562: write_debug_and_log('Updating AR_CONS_INV');

Line 2562: write_debug_and_log('Updating AR_CONS_INV');

2558: FROM ar_cons_inv_trx IT
2559: WHERE IT.cons_inv_id = l_consinv_id
2560: AND IT.transaction_type = 'ADJUSTMENT');
2561:
2562: write_debug_and_log('Updating AR_CONS_INV');
2563:
2564: -- 6955957
2565: -- update ra_customer_trx_all with the printing dates for all the transactions included.
2566: UPDATE ra_customer_trx trx

Line 2571: FROM ar_cons_inv_trx IT

2567: SET printing_original_date = nvl(printing_original_date, SYSDATE),
2568: printing_last_printed = nvl(printing_last_printed, SYSDATE)
2569: WHERE trx.trx_number IN
2570: (SELECT trx_number
2571: FROM ar_cons_inv_trx IT
2572: WHERE IT.cons_inv_id = l_consinv_id );
2573:
2574: --Get the Aging information and update the
2575: -- aging buckets on the Bill

Line 2610: UPDATE ar_cons_inv

2606: l_bucket_titletop_6,
2607: l_bucket_titlebottom_6,
2608: l_bucket_amount_6);
2609:
2610: UPDATE ar_cons_inv
2611: SET aging_bucket1_amt = l_bucket_amount_0,
2612: aging_bucket2_amt = l_bucket_amount_1,
2613: aging_bucket3_amt = l_bucket_amount_2,
2614: aging_bucket4_amt = l_bucket_amount_3,

Line 2631: -- into ar_cons_inv_trx.

2627: -- commmented out the following code for bug 6488683
2628: -- This is moved to the process_override procedure and the update is done for
2629: -- individual transactions instead of doing it as a bulk here.
2630: -- This is in order to get the new payment schedule id while inserting
2631: -- into ar_cons_inv_trx.
2632: /*
2633: -- Following is the update for all data overridden in TRX table
2634: IF l_tab_idx > 0 THEN
2635: write_debug_and_log('Override : Updating RA_CUSTOMER_TRX');

Line 2724: ar_cons_inv_trx IT,

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'
2728: AND IT.cons_inv_id = CI.cons_inv_id

Line 2725: ar_cons_inv CI

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'
2728: AND IT.cons_inv_id = CI.cons_inv_id
2729: AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO',

Line 2737: ar_cons_inv.print_status was changed.

2733: BEGIN
2734:
2735: /* bug3604391 Changed the sequence of following update stmts.
2736: Because ra_customer_trx was not updated after
2737: ar_cons_inv.print_status was changed.
2738: */
2739: UPDATE ra_customer_trx CT
2740: SET CT.printing_original_date =
2741: nvl(CT.printing_original_date,sysdate),

Line 2750: ar_cons_inv_trx IT,

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'
2754: AND CI.cons_inv_id=nvl(P_consinv_id,CI.cons_inv_id)

Line 2751: ar_cons_inv CI

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'
2754: AND CI.cons_inv_id=nvl(P_consinv_id,CI.cons_inv_id)
2755: AND CI.concurrent_request_id =

Line 2775: UPDATE ar_cons_inv

2771: 'PRINT');
2772: END LOOP;
2773: END IF;
2774:
2775: UPDATE ar_cons_inv
2776: SET print_status = 'PRINTED',
2777: last_update_date = arp_global.last_update_date,
2778: last_updated_by = arp_global.last_updated_by,
2779: last_update_login = arp_global.last_update_login