[Home] [Help]
1: PACKAGE BODY arp_consinv AS
2: /* $Header: ARPCBIB.pls 120.27 2005/06/14 18:53:10 vcrisost ship $ */
3:
4: /* bug2778646 : Added 'MERGE_PENDING','DRAFT_MERGE' and 'MERGED' status to
5: correspond to customer merge as status of AR_CONS_INV_ALL table.
6:
7: MERGE_PENDING -- Not merged yet. Next CBI has to pick up.
8: DRAFT_MERGE -- Merged to new draft CBI. Other CBI cannot get it untill
9: the new draft CBI is rejected.
9: the new draft CBI is rejected.
10: MERGED -- Merged to new accept CBI. Other CBI cannot get it.
11:
12: In merge process , ARCMCONB.pls updates customer_id , site_id and status of
13: ar_cons_inv table. Update status to 'MERGED' except latest CBI.
14: The ending_balance of latest CBI should be added to new customer site's CBI.
15: The status of latest CBI is 'MERGE_PENDING'.
16:
17: In generic procedure, added up 'MERGE_PENDING' and latest 'ACCEPTED' CBI for
50: *----------------------------------------------------------------------------*/
51: PROCEDURE reprint (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
52:
53: BEGIN
54: UPDATE ar_cons_inv
55: SET print_status = 'PENDING',
56: last_update_date = arp_global.last_update_date,
57: last_updated_by = arp_global.last_updated_by,
58: last_update_login = arp_global.last_update_login
95: | |
96: *----------------------------------------------------------------------------*/
97: PROCEDURE accept (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
98: -- bug2778646 start
99: TYPE tab_site_use_id IS TABLE OF ar_cons_inv_all.site_use_id%TYPE;
100: TYPE tab_currency_code IS TABLE OF ar_cons_inv_all.currency_code%TYPE;
101: TYPE tab_cut_off_date IS TABLE OF ar_cons_inv_all.cut_off_date%TYPE;
102:
103: l_site_use_id tab_site_use_id ;
96: *----------------------------------------------------------------------------*/
97: PROCEDURE accept (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
98: -- bug2778646 start
99: TYPE tab_site_use_id IS TABLE OF ar_cons_inv_all.site_use_id%TYPE;
100: TYPE tab_currency_code IS TABLE OF ar_cons_inv_all.currency_code%TYPE;
101: TYPE tab_cut_off_date IS TABLE OF ar_cons_inv_all.cut_off_date%TYPE;
102:
103: l_site_use_id tab_site_use_id ;
104: l_currency_code tab_currency_code;
97: PROCEDURE accept (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
98: -- bug2778646 start
99: TYPE tab_site_use_id IS TABLE OF ar_cons_inv_all.site_use_id%TYPE;
100: TYPE tab_currency_code IS TABLE OF ar_cons_inv_all.currency_code%TYPE;
101: TYPE tab_cut_off_date IS TABLE OF ar_cons_inv_all.cut_off_date%TYPE;
102:
103: l_site_use_id tab_site_use_id ;
104: l_currency_code tab_currency_code;
105: l_cut_off_date tab_cut_off_date ;
107: CURSOR c_cons_inv IS
108: SELECT site_use_id,
109: currency_code,
110: cut_off_date
111: FROM ar_cons_inv
112: WHERE cons_inv_id = nvl(P_consinv_id, cons_inv_id)
113: AND concurrent_request_id = DECODE(P_consinv_id,
114: NULL, P_request_id,
115: concurrent_request_id)
127: l_currency_code,
128: l_cut_off_date ;
129:
130: FORALL i IN 1..l_site_use_id.count
131: UPDATE ar_cons_inv
132: SET status = 'MERGED',
133: last_update_date = arp_global.last_update_date,
134: last_updated_by = arp_global.last_updated_by,
135: last_update_login = arp_global.last_update_login
138: AND currency_code = l_currency_code(i)
139: AND cut_off_date <= l_cut_off_date(i) ;
140: -- bug2778646 end
141:
142: UPDATE ar_cons_inv
143: SET status = 'ACCEPTED',
144: last_update_date = arp_global.last_update_date,
145: last_updated_by = arp_global.last_updated_by,
146: last_update_login = arp_global.last_update_login
191: *----------------------------------------------------------------------------*/
192: PROCEDURE reject (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
193:
194: -- bug2778646 start
195: TYPE tab_site_use_id IS TABLE OF ar_cons_inv_all.site_use_id%TYPE;
196: TYPE tab_currency_code IS TABLE OF ar_cons_inv_all.currency_code%TYPE;
197: TYPE tab_cut_off_date IS TABLE OF ar_cons_inv_all.cut_off_date%TYPE;
198:
199: l_site_use_id tab_site_use_id ;
192: PROCEDURE reject (P_consinv_id IN NUMBER, P_request_id IN NUMBER) IS
193:
194: -- bug2778646 start
195: TYPE tab_site_use_id IS TABLE OF ar_cons_inv_all.site_use_id%TYPE;
196: TYPE tab_currency_code IS TABLE OF ar_cons_inv_all.currency_code%TYPE;
197: TYPE tab_cut_off_date IS TABLE OF ar_cons_inv_all.cut_off_date%TYPE;
198:
199: l_site_use_id tab_site_use_id ;
200: l_currency_code tab_currency_code;
193:
194: -- bug2778646 start
195: TYPE tab_site_use_id IS TABLE OF ar_cons_inv_all.site_use_id%TYPE;
196: TYPE tab_currency_code IS TABLE OF ar_cons_inv_all.currency_code%TYPE;
197: TYPE tab_cut_off_date IS TABLE OF ar_cons_inv_all.cut_off_date%TYPE;
198:
199: l_site_use_id tab_site_use_id ;
200: l_currency_code tab_currency_code;
201: l_cut_off_date tab_cut_off_date ;
203: CURSOR c_cons_inv IS
204: SELECT site_use_id,
205: currency_code,
206: cut_off_date
207: FROM ar_cons_inv
208: WHERE cons_inv_id = nvl(P_consinv_id, cons_inv_id)
209: AND concurrent_request_id = DECODE(P_consinv_id,
210: NULL, P_request_id,
211: concurrent_request_id)
227: printing_count - 1)
228: WHERE customer_trx_id IN
229: (SELECT PS.customer_trx_id
230: FROM ar_payment_schedules PS,
231: ar_cons_inv_trx IT,
232: ar_cons_inv CI
233: WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO')
234: AND CI.cons_inv_id = IT.cons_inv_id
235: AND CI.cons_inv_id = nvl(P_consinv_id,CI.cons_inv_id)
228: WHERE customer_trx_id IN
229: (SELECT PS.customer_trx_id
230: FROM ar_payment_schedules PS,
231: ar_cons_inv_trx IT,
232: ar_cons_inv CI
233: WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO')
234: AND CI.cons_inv_id = IT.cons_inv_id
235: AND CI.cons_inv_id = nvl(P_consinv_id,CI.cons_inv_id)
236: AND CI.concurrent_request_id = DECODE (P_consinv_id,
242: UPDATE ar_payment_schedules
243: SET cons_inv_id = NULL
244: WHERE payment_schedule_id IN
245: (SELECT IT.adj_ps_id
246: FROM ar_cons_inv CI,
247: ar_cons_inv_trx IT
248: WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO',
249: 'RECEIPT')
250: AND CI.cons_inv_id = IT.cons_inv_id
243: SET cons_inv_id = NULL
244: WHERE payment_schedule_id IN
245: (SELECT IT.adj_ps_id
246: FROM ar_cons_inv CI,
247: ar_cons_inv_trx IT
248: WHERE IT.transaction_type IN ('INVOICE','CREDIT_MEMO',
249: 'RECEIPT')
250: AND CI.cons_inv_id = IT.cons_inv_id
251: AND CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
257: UPDATE ar_payment_schedules
258: SET cons_inv_id_rev = NULL
259: WHERE payment_schedule_id IN
260: (SELECT IT.adj_ps_id
261: FROM ar_cons_inv CI,
262: ar_cons_inv_trx IT
263: WHERE IT.transaction_type = 'RECEIPT REV'
264: AND CI.cons_inv_id = IT.cons_inv_id
265: AND CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
258: SET cons_inv_id_rev = NULL
259: WHERE payment_schedule_id IN
260: (SELECT IT.adj_ps_id
261: FROM ar_cons_inv CI,
262: ar_cons_inv_trx IT
263: WHERE IT.transaction_type = 'RECEIPT REV'
264: AND CI.cons_inv_id = IT.cons_inv_id
265: AND CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
266: AND CI.concurrent_request_id = DECODE(P_consinv_id,
273: UPDATE ar_receivable_applications
274: SET cons_inv_id = NULL
275: WHERE receivable_application_id IN
276: (SELECT IT.adj_ps_id
277: FROM ar_cons_inv CI,
278: ar_cons_inv_trx IT
279: WHERE IT.transaction_type IN ('XSITE RECREV', 'XSITE_CMREV',
280: 'XCURR RECREV', 'XSITE XCURR RECREV',
281: 'EXCLUDE RECREV', 'EXCLUDE_CMREV')
274: SET cons_inv_id = NULL
275: WHERE receivable_application_id IN
276: (SELECT IT.adj_ps_id
277: FROM ar_cons_inv CI,
278: ar_cons_inv_trx IT
279: WHERE IT.transaction_type IN ('XSITE RECREV', 'XSITE_CMREV',
280: 'XCURR RECREV', 'XSITE XCURR RECREV',
281: 'EXCLUDE RECREV', 'EXCLUDE_CMREV')
282: AND CI.cons_inv_id = IT.cons_inv_id
290: UPDATE ar_receivable_applications
291: SET cons_inv_id_to = NULL
292: WHERE receivable_application_id IN
293: (SELECT IT.adj_ps_id
294: FROM ar_cons_inv CI,
295: ar_cons_inv_trx IT
296: WHERE IT.transaction_type IN ('XSITE RECAPP','XSITE_CMAPP',
297: 'XCURR RECAPP', 'XSITE XCURR RECAPP' ,
298: 'EXCLUDE RECAPP', 'EXCLUDE_CMAPP')
291: SET cons_inv_id_to = NULL
292: WHERE receivable_application_id IN
293: (SELECT IT.adj_ps_id
294: FROM ar_cons_inv CI,
295: ar_cons_inv_trx IT
296: WHERE IT.transaction_type IN ('XSITE RECAPP','XSITE_CMAPP',
297: 'XCURR RECAPP', 'XSITE XCURR RECAPP' ,
298: 'EXCLUDE RECAPP', 'EXCLUDE_CMAPP')
299: AND CI.cons_inv_id = IT.cons_inv_id
306: UPDATE ar_adjustments
307: SET cons_inv_id = NULL
308: WHERE adjustment_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 = 'ADJUSTMENT'
313: AND CI.cons_inv_id = IT.cons_inv_id
314: AND CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
307: SET cons_inv_id = NULL
308: WHERE adjustment_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 = 'ADJUSTMENT'
313: AND CI.cons_inv_id = IT.cons_inv_id
314: AND CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
315: AND CI.concurrent_request_id = DECODE (P_consinv_id,
326: l_currency_code,
327: l_cut_off_date ;
328:
329: FORALL i IN 1..l_site_use_id.count
330: UPDATE ar_cons_inv
331: SET status = 'MERGE_PENDING',
332: last_update_date = arp_global.last_update_date,
333: last_updated_by = arp_global.last_updated_by,
334: last_update_login = arp_global.last_update_login
337: AND currency_code = l_currency_code(i)
338: AND cut_off_date <= l_cut_off_date(i) ;
339: -- bug2778646 end
340:
341: DELETE FROM ar_cons_inv_trx_lines
342: WHERE cons_inv_id IN
343: (SELECT CI.cons_inv_id
344: FROM ar_cons_inv CI
345: WHERE CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
340:
341: DELETE FROM ar_cons_inv_trx_lines
342: WHERE cons_inv_id IN
343: (SELECT CI.cons_inv_id
344: FROM ar_cons_inv CI
345: WHERE CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
346: AND CI.concurrent_request_id = DECODE (P_consinv_id,
347: NULL, P_request_id,
348: CI.concurrent_request_id)
347: NULL, P_request_id,
348: CI.concurrent_request_id)
349: AND CI.status = 'DRAFT');
350:
351: DELETE FROM ar_cons_inv_trx
352: WHERE cons_inv_id IN
353: (SELECT CI.cons_inv_id
354: FROM ar_cons_inv CI
355: WHERE CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
350:
351: DELETE FROM ar_cons_inv_trx
352: WHERE cons_inv_id IN
353: (SELECT CI.cons_inv_id
354: FROM ar_cons_inv CI
355: WHERE CI.cons_inv_id = nvl(P_consinv_id, CI.cons_inv_id)
356: AND CI.concurrent_request_id = DECODE (P_consinv_id,
357: NULL, P_request_id,
358: CI.concurrent_request_id)
357: NULL, P_request_id,
358: CI.concurrent_request_id)
359: AND CI.status = 'DRAFT');
360:
361: UPDATE ar_cons_inv
362: SET status = 'REJECTED',
363: print_status = 'PRINTED'
364: WHERE cons_inv_id = nvl(P_consinv_id, cons_inv_id)
365: AND concurrent_request_id = DECODE(P_consinv_id,
404: | NOTES |
405: | |
406: | MODIFICATION HISTORY |
407: | 05-AUG-97 Jack Martinez bug 499781: |
408: | insert into ar_cons_inv_trx for type |
409: | 'XSITE RECAPP' should not negate amount|
410: | 06-AUG-97 Jack Martinez bug 522890: |
411: | ignore guarantees when collecting |
412: | adjustments. When an invoice is |
546: nvl(CP.cons_inv_type,
547: 'SUMMARY')) = C_detail_option
548: AND NOT EXISTS
549: (SELECT NULL
550: FROM ar_cons_inv CI
551: WHERE CI.site_use_id = site_uses.site_use_id
552: AND CI.cut_off_date = to_date(C_cutoff_date)
553: AND CI.currency_code = P_currency
554: AND CI.status <> 'REJECTED')
553: AND CI.currency_code = P_currency
554: AND CI.status <> 'REJECTED')
555: AND NOT EXISTS
556: (SELECT NULL
557: FROM ar_cons_inv CI2
558: WHERE CI2.site_use_id = site_uses.site_use_id
559: AND CI2.currency_code = P_currency
560: AND CI2.status = 'DRAFT') ;
561: */
716: AND nvl(nvl(SP.cons_inv_type,CP.cons_inv_type),'SUMMARY')
717: = C_detail_option
718: AND NOT EXISTS
719: (SELECT NULL
720: FROM ar_cons_inv CI
721: WHERE CI.site_use_id = site_uses.site_use_id
722: -- bug3129948 added '>'
723: AND CI.cut_off_date >=P_cutoff_date
724: AND CI.currency_code = P_currency
724: AND CI.currency_code = P_currency
725: AND CI.status <> 'REJECTED')
726: AND NOT EXISTS
727: (SELECT NULL
728: FROM ar_cons_inv CI2
729: WHERE CI2.site_use_id = site_uses.site_use_id
730: AND CI2.currency_code = P_currency
731: AND CI2.status = 'DRAFT') ;
732: ELSE
754: AND nvl(nvl(SP.cons_inv_type,CP.cons_inv_type),'SUMMARY')
755: = C_detail_option
756: AND NOT EXISTS
757: (SELECT NULL
758: FROM ar_cons_inv CI
759: WHERE CI.site_use_id = site_uses.site_use_id
760: -- bug3129948 added '>'
761: AND CI.cut_off_date >= P_cutoff_date
762: AND CI.currency_code = P_currency
762: AND CI.currency_code = P_currency
763: AND CI.status <> 'REJECTED')
764: AND NOT EXISTS
765: (SELECT NULL
766: FROM ar_cons_inv CI2
767: WHERE CI2.site_use_id = site_uses.site_use_id
768: AND CI2.currency_code = P_currency
769: AND CI2.status = 'DRAFT') ;
770: END IF ;
793: AND nvl(nvl(SP.cons_inv_type,CP.cons_inv_type),'SUMMARY')
794: = C_detail_option
795: AND NOT EXISTS
796: (SELECT NULL
797: FROM ar_cons_inv CI
798: WHERE CI.site_use_id = site_uses.site_use_id
799: -- bug3129948 added '>'
800: AND CI.cut_off_date >= P_cutoff_date
801: AND CI.currency_code = P_currency
801: AND CI.currency_code = P_currency
802: AND CI.status <> 'REJECTED')
803: AND NOT EXISTS
804: (SELECT NULL
805: FROM ar_cons_inv CI2
806: WHERE CI2.site_use_id = site_uses.site_use_id
807: AND CI2.currency_code = P_currency
808: AND CI2.status = 'DRAFT') ;
809: END IF;
830: l_consinv_lineno := 1;
831:
832: /** For Site: get next billing invoice id, create header with zero totals.**/
833:
834: SELECT ar_cons_inv_s.NEXTVAL INTO l_consinv_id FROM dual;
835: l_cons_billno := to_char(l_consinv_id);
836:
837: /** calculate due date **/
838:
858:
859: /* bug2778646 Modified this select stmt to get balance of merged cbi.
860: SELECT sum(ending_balance)
861: INTO l_beginning_balance
862: FROM ar_cons_inv CI1
863: WHERE CI1.site_use_id = L_sites.site_id
864: AND CI1.currency_code = P_currency
865: AND CI1.status <> 'REJECTED'
866: AND CI1.cut_off_date =
864: AND CI1.currency_code = P_currency
865: AND CI1.status <> 'REJECTED'
866: AND CI1.cut_off_date =
867: (SELECT max(CI2.cut_off_date)
868: FROM ar_cons_inv CI2
869: WHERE CI2.site_use_id = L_sites.site_id
870: AND CI2.currency_code = P_currency
871: AND CI2.cut_off_date < P_cutoff_date
872: AND CI2.status <> 'REJECTED');
873: */
874:
875: SELECT sum(ending_balance)
876: INTO l_beginning_balance
877: FROM ar_cons_inv CI1
878: WHERE CI1.site_use_id = L_sites.site_id
879: AND CI1.currency_code = P_currency
880: AND ((CI1.status = 'ACCEPTED'
881: AND CI1.cut_off_date =
879: AND CI1.currency_code = P_currency
880: AND ((CI1.status = 'ACCEPTED'
881: AND CI1.cut_off_date =
882: (SELECT max(CI2.cut_off_date)
883: FROM ar_cons_inv CI2
884: WHERE CI2.site_use_id = L_sites.site_id
885: AND CI2.currency_code = P_currency
886: AND CI2.cut_off_date < P_cutoff_date
887: AND CI2.status = 'ACCEPTED'))
895:
896: /** For Site: create header. **/
897: /** note it is possible that only the header will created if no **/
898: /** transactions are found. **/
899: INSERT INTO ar_cons_inv (cons_inv_id,
900: cons_billing_number,
901: customer_id,
902: site_use_id,
903: concurrent_request_id,
959: dbms_output.put_line('trx_id :'||TO_CHAR(l_inv_trx.trx_id));
960: dbms_output.put_line('trx_date :'||TO_CHAR(l_inv_trx.trx_date));
961: dbms_output.put_line('trx_number :'||l_inv_trx.trx_number);
962: */
963: INSERT INTO ar_cons_inv_trx (cons_inv_id,
964: transaction_type,
965: trx_number,
966: transaction_date,
967: amount_original,
988:
989: /* Bug 586099: For credit memo, quantity is stored in
990: quantity_credited rather than quantity_invoiced. */
991: IF (L_inv_trx.class = 'CM') THEN
992: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
993: cons_inv_line_number,
994: customer_trx_id,
995: customer_trx_line_id,
996: line_number,
1022: customer_trx_id = L_inv_trx.trx_id
1023: AND line_type NOT IN ('TAX', 'FREIGHT');
1024:
1025: ELSE
1026: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1027: cons_inv_line_number,
1028: customer_trx_id,
1029: customer_trx_line_id,
1030: line_number,
1058: END IF;
1059:
1060: /** now update lines with associated tax line **/
1061: /* bug3039537 : Removed
1062: UPDATE ar_cons_inv_trx_lines TL
1063: set TL.tax_amount =
1064: (SELECT sum(nvl(CTL.extended_amount,0))
1065: FROM ra_customer_trx_lines CTL
1066: WHERE CTL.link_to_cust_trx_line_id =
1079:
1080: -- 1. Update tax_amount
1081: -- 2. Exclude inclusive tax amount total from extended_amount
1082: FORALL i IN 1..l_line_id.count
1083: UPDATE ar_cons_inv_trx_lines
1084: SET tax_amount = l_tax_sum(i),
1085: extended_amount = extended_amount - l_include_tax_sum(i)
1086: WHERE customer_trx_id = L_inv_trx.trx_id /*4413567*/
1087: AND
1091: END LOOP;
1092: CLOSE c_tax;
1093:
1094: /** now create 1 summary row for freight **/
1095: INSERT INTO ar_cons_inv_trx_lines (cons_inv_id,
1096: cons_inv_line_number,
1097: customer_trx_id,
1098: customer_trx_line_id,
1099: line_number,
1137: /** gathering adjustments, check the class of the related **/
1138: /** payment schedule and omit if class = 'GUAR'. **/
1139:
1140: /*
1141: 1357024 fbreslin put AR_ADJUSTMENTS.tax_adjusted into AR_CONS_INV.TAX_ORIGINAL
1142: */
1143:
1144: /*
1145: 1340426 fbreslin: Only include approved adjustments
1147:
1148: /* bug2882196 : Added exclude_from_cons_bill_flag condition not to get legacy
1149: transactions. */
1150: /* bug2922922 : Added hint */
1151: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1152: transaction_type,
1153: trx_number,
1154: transaction_date,
1155: amount_original,
1184:
1185: /** For Site: cash receipts. **/
1186: /* bug2882196 : Added exclude_from_cons_bill_flag condition not to get legacy
1187: receipts. */
1188: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1189: transaction_type,
1190: trx_number,
1191: transaction_date,
1192: amount_original,
1219:
1220: /** For Site: cash receipts reversals. **/
1221: /* bug2882196 : Added exclude_from_cons_bill_flag condition not to get legacy
1222: receipts. */
1223: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1224: transaction_type,
1225: trx_number,
1226: transaction_date,
1227: amount_original,
1257: /** 531330 - changed '(-1)*RA.amount_applied' to 'RA.amount_applied **/
1258: /** Cross Currency functionality implemented. **/
1259: /* bug2882196 : Added 'EXCLUDE RECREV' for when applied to legacy invoices */
1260:
1261: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1262: transaction_type,
1263: trx_number,
1264: transaction_date,
1265: amount_original,
1304: /*Bug2677085- Added a select statement to pick up those applications which were considered as XSITE RECAPP but now have the same bill to site as that of the
1305: invoice being processed by the CBI. A XSITE RECREV (or XSITE XCURR RECREV) is
1306: created to negate the application from receipt amount. */
1307:
1308: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1309: transaction_type,
1310: trx_number,
1311: transaction_date,
1312: amount_original,
1325: RA.receivable_application_id,
1326: NULL,
1327: ps_cash.org_id
1328: FROM
1329: ar_cons_inv_trx inv_trx,
1330: ar_receivable_applications ra,
1331: ar_payment_schedules ps_cash,
1332: ar_payment_schedules ps_inv
1333: WHERE ra.cons_inv_id_to is not null
1355: /** Cross Currency functionality has been added. **/
1356: /* bug2882196 : Added 'EXCLUDE RECAPP' for when legacy receipt applied to
1357: non-legacy invoices. */
1358:
1359: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1360: transaction_type,
1361: trx_number,
1362: transaction_date,
1363: amount_original,
1402: /* Bug2778646- Added a select statement to pick up those applications which were
1403: considered as XSITE RECREV but now have the same bill to site as that of the
1404: invoice being processed by the CBI. A XSITE RECAPP (or XSITE XCURR RECAPP) is
1405: created to negate the application from receipt amount. */
1406: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1407: transaction_type,
1408: trx_number,
1409: transaction_date,
1410: amount_original,
1423: RA.receivable_application_id,
1424: NULL,
1425: ps_cash.org_id
1426: FROM
1427: ar_cons_inv_trx inv_trx,
1428: ar_receivable_applications ra,
1429: ar_payment_schedules ps_cash,
1430: ar_payment_schedules ps_inv
1431: WHERE ra.cons_inv_id_to is null
1445: /** Will need to add a reversal line because Credit Memo was used **/
1446: /** for a bill-to that is different from the current bill-to. **/
1447: /* bug2882196 : Added 'EXCLUDE_CMREV' for when credit memo applied to
1448: legacy invoices */
1449: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1450: transaction_type,
1451: trx_number,
1452: transaction_date,
1453: amount_original,
1493: /** For Site: get on-account credit memos assigned to different bill-to but **/
1494: /** applied against invoice for current bill-to. **/
1495: /* bug2882196 : Added 'EXCLUDE_CMAPP' for when legacy credit memo applied to
1496: non-legacy invoice. */
1497: INSERT INTO ar_cons_inv_trx (cons_inv_id,
1498: transaction_type,
1499: trx_number,
1500: transaction_date,
1501: amount_original,
1541: /** For Site: update header for totals. **/
1542: /* bug2882196 Added EXCLUDE_CMREV/APP transaction_type */
1543: SELECT nvl(sum(amount_original),0)
1544: INTO l_new_billed
1545: FROM ar_cons_inv_trx
1546: WHERE cons_inv_id = l_consinv_id
1547: AND transaction_type IN ('INVOICE','CREDIT_MEMO','ADJUSTMENT',
1548: 'XSITE_CMREV','XSITE_CMAPP',
1549: 'EXCLUDE_CMREV', 'EXCLUDE_CMAPP');
1551: /* bug2786667 Added XCURR transaction_type */
1552: /* bug2882196 Added EXCLUDE RECREV/APP transaction_type */
1553: SELECT nvl(sum(amount_original),0)
1554: INTO l_period_receipts
1555: FROM ar_cons_inv_trx
1556: WHERE cons_inv_id = l_consinv_id
1557: AND transaction_type IN ('RECEIPT','RECEIPT REV','XSITE RECREV',
1558: 'XSITE RECAPP',
1559: 'XSITE XCURR RECAPP','XSITE XCURR RECREV',
1558: 'XSITE RECAPP',
1559: 'XSITE XCURR RECAPP','XSITE XCURR RECREV',
1560: 'EXCLUDE RECREV', 'EXCLUDE RECAPP');
1561:
1562: UPDATE ar_cons_inv
1563: SET ending_balance =
1564: beginning_balance + l_new_billed + l_period_receipts
1565: WHERE cons_inv_id = l_consinv_id;
1566:
1571: UPDATE ar_payment_schedules PS
1572: SET PS.cons_inv_id = l_consinv_id
1573: WHERE PS.payment_schedule_id IN
1574: (SELECT IT.adj_ps_id
1575: FROM ar_cons_inv_trx IT
1576: WHERE IT.cons_inv_id = l_consinv_id
1577: AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO',
1578: 'RECEIPT'));
1579:
1580: UPDATE ar_payment_schedules PS
1581: SET PS.cons_inv_id_rev = l_consinv_id
1582: WHERE PS.payment_schedule_id IN
1583: (SELECT IT.adj_ps_id
1584: FROM ar_cons_inv_trx IT
1585: WHERE IT.cons_inv_id = l_consinv_id
1586: AND IT.transaction_type = 'RECEIPT REV');
1587:
1588: /* bug2882196 Added 'EXCLUDE RECREV' and 'EXCLUDE_CMREV' */
1589: UPDATE ar_receivable_applications RA
1590: SET RA.cons_inv_id = l_consinv_id
1591: WHERE RA.receivable_application_id IN
1592: (SELECT IT.adj_ps_id
1593: FROM ar_cons_inv_trx IT
1594: WHERE IT.cons_inv_id = l_consinv_id
1595: AND IT.transaction_type IN ('XSITE RECREV',
1596: 'XSITE_CMREV',
1597: 'XCURR RECREV',
1605: UPDATE ar_receivable_applications RA
1606: SET RA.cons_inv_id_to = l_consinv_id
1607: WHERE RA.receivable_application_id IN
1608: (SELECT IT.adj_ps_id
1609: FROM ar_cons_inv_trx IT
1610: WHERE IT.cons_inv_id = l_consinv_id
1611: AND IT.transaction_type IN ('XSITE RECAPP',
1612: 'XSITE_CMAPP',
1613: 'XCURR RECAPP',
1618: /* bug2922922 : Added hint */
1619: UPDATE ar_adjustments RA
1620: SET RA.cons_inv_id = l_consinv_id
1621: WHERE RA.adjustment_id IN
1622: (SELECT /*+ index (IT AR_CONS_INV_TRX_N1) */
1623: IT.adj_ps_id
1624: FROM ar_cons_inv_trx IT
1625: WHERE IT.cons_inv_id = l_consinv_id
1626: AND IT.transaction_type = 'ADJUSTMENT');
1620: SET RA.cons_inv_id = l_consinv_id
1621: WHERE RA.adjustment_id IN
1622: (SELECT /*+ index (IT AR_CONS_INV_TRX_N1) */
1623: IT.adj_ps_id
1624: FROM ar_cons_inv_trx IT
1625: WHERE IT.cons_inv_id = l_consinv_id
1626: AND IT.transaction_type = 'ADJUSTMENT');
1627:
1628: -- bug2778646 Changed status of selected merged cbi.
1626: AND IT.transaction_type = 'ADJUSTMENT');
1627:
1628: -- bug2778646 Changed status of selected merged cbi.
1629: -- DRAFT_MERGE/MERGED status CBI is not selected by other CBI.
1630: UPDATE ar_cons_inv ci
1631: SET status = DECODE(P_print_option, 'DRAFT', 'DRAFT_MERGE','MERGED')
1632: WHERE status = 'MERGE_PENDING'
1633: AND site_use_id = L_sites.site_id
1634: AND currency_code = P_currency
1713:
1714: CURSOR c_pending_trx IS
1715: SELECT PS.customer_trx_id
1716: FROM ar_payment_schedules PS,
1717: ar_cons_inv_trx IT,
1718: ar_cons_inv CI
1719: WHERE
1720: CI.print_status = 'PENDING'
1721: AND IT.cons_inv_id = CI.cons_inv_id
1714: CURSOR c_pending_trx IS
1715: SELECT PS.customer_trx_id
1716: FROM ar_payment_schedules PS,
1717: ar_cons_inv_trx IT,
1718: ar_cons_inv CI
1719: WHERE
1720: CI.print_status = 'PENDING'
1721: AND IT.cons_inv_id = CI.cons_inv_id
1722: AND IT.transaction_type IN ('INVOICE','CREDIT_MEMO')
1725: BEGIN
1726:
1727: /* bug3604391 Changed the sequence of following update stmts.
1728: Because ra_customer_trx was not updated after
1729: ar_cons_inv.print_status was changed.
1730: */
1731: UPDATE ra_customer_trx CT
1732: SET CT.printing_original_date =
1733: nvl(CT.printing_original_date,sysdate),
1738: 1)
1739: WHERE CT.customer_trx_id IN
1740: (SELECT PS.customer_trx_id
1741: FROM ar_payment_schedules PS,
1742: ar_cons_inv_trx IT,
1743: ar_cons_inv CI
1744: WHERE (
1745: (P_print_option = 'REPRINT'
1746: AND CI.cons_inv_id=nvl(P_consinv_id,CI.cons_inv_id)
1739: WHERE CT.customer_trx_id IN
1740: (SELECT PS.customer_trx_id
1741: FROM ar_payment_schedules PS,
1742: ar_cons_inv_trx IT,
1743: ar_cons_inv CI
1744: WHERE (
1745: (P_print_option = 'REPRINT'
1746: AND CI.cons_inv_id=nvl(P_consinv_id,CI.cons_inv_id)
1747: AND CI.concurrent_request_id =
1763: 'PRINT');
1764: END LOOP;
1765: END IF;
1766:
1767: UPDATE ar_cons_inv
1768: SET print_status = 'PRINTED',
1769: last_update_date = arp_global.last_update_date,
1770: last_updated_by = arp_global.last_updated_by,
1771: last_update_login = arp_global.last_update_login