[Home] [Help]
144: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
145: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
146: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
147: (SELECT SUM(b.acctd_amount_due_remaining)
148: FROM iex_delinquencies_all a,
149: ar_payment_schedules_all b
150: WHERE a.customer_site_use_id = trx_summ.site_use_id
151: AND a.payment_schedule_id = b.payment_schedule_id
152: AND b.status = 'OP'
162: MAX(trx_summ.creation_date) creation_date,
163: MAX(trx_summ.created_by) created_by,
164: MAX(trx_summ.last_update_login) last_update_login,
165: (SELECT COUNT(1)
166: FROM iex_delinquencies_all
167: WHERE customer_site_use_id = trx_summ.site_use_id
168: AND status IN('DELINQUENT', 'PREDELINQUENT')
169: AND org_id = trx_summ.org_id)
170: number_of_delinquencies,
171: (SELECT 1
172: FROM dual
173: WHERE EXISTS
174: (SELECT 1
175: FROM iex_delinquencies_all
176: WHERE customer_site_use_id = trx_summ.site_use_id
177: AND status IN('DELINQUENT', 'PREDELINQUENT')
178: AND org_id = trx_summ.org_id
179: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
183: (SELECT 1
184: FROM dual
185: WHERE EXISTS
186: (SELECT 1
187: FROM iex_delinquencies_all
188: WHERE customer_site_use_id = trx_summ.site_use_id
189: AND status IN('DELINQUENT', 'PREDELINQUENT')
190: AND org_id = trx_summ.org_id
191: AND(uwq_status = 'COMPLETE'
195: (SELECT 1
196: FROM dual
197: WHERE EXISTS
198: (SELECT 1
199: FROM iex_delinquencies_all where customer_site_use_id = trx_summ.site_use_id
200: AND status IN('DELINQUENT', 'PREDELINQUENT')
201: AND org_id = trx_summ.org_id
202: AND(uwq_status = 'PENDING'
203: AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
226: phone.phone_number phone_number,
227: phone.phone_extension phone_extension,
228: (SELECT COUNT(1) FROM iex_bankruptcies bkr WHERE bkr.party_id = party.party_id and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION')) number_of_bankruptcies, -- Changed for bug#7693986
229:
230: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
231: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
232: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
233: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
234: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
235: NOT IN ('CURRENT', 'CLOSE')
236: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
237: AND DEL.org_id = trx_summ.org_id) number_of_promises,
238:
239: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
240: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
241: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
242: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
243: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
244: NOT IN ('CURRENT', 'CLOSE')
245: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
246: AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
247:
248: (SELECT SUM(PROMISE_AMOUNT) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
249: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
250: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
251: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
252: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
256:
257: (SELECT 1 FROM dual WHERE EXISTS
258: (SELECT 1 FROM dual WHERE EXISTS
259: (SELECT 1
260: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
261: WHERE pro.cust_account_id = trx_summ.cust_account_id
262: and del.customer_site_use_id = TRX_SUMM.site_use_ID
263: AND pro.state = 'BROKEN_PROMISE'
264: AND(pro.uwq_status IS NULL OR pro.uwq_status = 'ACTIVE' OR(TRUNC(pro.uwq_active_date) <= TRUNC(sysdate)
268:
269: (SELECT 1 FROM dual WHERE EXISTS
270: (SELECT 1 FROM dual WHERE EXISTS
271: (SELECT 1
272: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
273: WHERE pro.cust_account_id = trx_summ.cust_account_id
274: and del.customer_site_use_id = TRX_SUMM.site_use_ID
275: AND pro.state = 'BROKEN_PROMISE'
276: AND(pro.uwq_status = 'COMPLETE'
280:
281: (SELECT 1 FROM dual WHERE EXISTS
282: (SELECT 1 FROM dual WHERE EXISTS
283: (SELECT 1
284: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
285: WHERE pro.cust_account_id = trx_summ.cust_account_id
286: and del.customer_site_use_id = TRX_SUMM.site_use_ID
287: AND pro.state = 'BROKEN_PROMISE'
288: AND(pro.uwq_status = 'PENDING'
407: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
408: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
409: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
410: (SELECT SUM(b.acctd_amount_due_remaining)
411: FROM iex_delinquencies_all a,
412: ar_payment_schedules_all b
413: WHERE a.customer_site_use_id = trx_summ.site_use_id
414: AND a.payment_schedule_id = b.payment_schedule_id
415: AND b.status = 'OP'
425: MAX(trx_summ.creation_date) creation_date,
426: MAX(trx_summ.created_by) created_by,
427: MAX(trx_summ.last_update_login) last_update_login,
428: (SELECT COUNT(1)
429: FROM iex_delinquencies_all
430: WHERE customer_site_use_id = trx_summ.site_use_id
431: AND status IN('DELINQUENT', 'PREDELINQUENT')
432: AND org_id = trx_summ.org_id)
433: number_of_delinquencies,
434: (SELECT 1
435: FROM dual
436: WHERE EXISTS
437: (SELECT 1
438: FROM iex_delinquencies_all
439: WHERE customer_site_use_id = trx_summ.site_use_id
440: AND status IN('DELINQUENT', 'PREDELINQUENT')
441: AND org_id = trx_summ.org_id
442: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
446: (SELECT 1
447: FROM dual
448: WHERE EXISTS
449: (SELECT 1
450: FROM iex_delinquencies_all
451: WHERE customer_site_use_id = trx_summ.site_use_id
452: AND status IN('DELINQUENT', 'PREDELINQUENT')
453: AND org_id = trx_summ.org_id
454: AND(uwq_status = 'COMPLETE'
458: (SELECT 1
459: FROM dual
460: WHERE EXISTS
461: (SELECT 1
462: FROM iex_delinquencies_all where customer_site_use_id = trx_summ.site_use_id
463: AND status IN('DELINQUENT', 'PREDELINQUENT')
464: AND org_id = trx_summ.org_id
465: AND(uwq_status = 'PENDING'
466: AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
489: phone.phone_number phone_number,
490: phone.phone_extension phone_extension,
491: (SELECT COUNT(1) FROM iex_bankruptcies bkr WHERE bkr.party_id = party.party_id and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION')) number_of_bankruptcies, -- Changed for bug#7693986
492:
493: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
494: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
495: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
496: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
497: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
498: NOT IN ('CURRENT', 'CLOSE')
499: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV')) --Added for Bug 6446848 06-Jan-2009 barathsr
500: AND DEL.org_id = trx_summ.org_id) number_of_promises,
501:
502: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
503: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
504: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
505: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
506: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
507: NOT IN ('CURRENT', 'CLOSE')
508: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV')) --Added for Bug 6446848 06-Jan-2009 barathsr
509: AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
510:
511: (SELECT SUM(PROMISE_AMOUNT) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
512: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
513: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
514: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
515: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
519:
520: (SELECT 1 FROM dual WHERE EXISTS
521: (SELECT 1 FROM dual WHERE EXISTS
522: (SELECT 1
523: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
524: WHERE pro.cust_account_id = trx_summ.cust_account_id
525: and del.customer_site_use_id = TRX_SUMM.site_use_ID
526: AND pro.state = 'BROKEN_PROMISE'
527: AND(pro.uwq_status IS NULL OR pro.uwq_status = 'ACTIVE' OR(TRUNC(pro.uwq_active_date) <= TRUNC(sysdate)
531:
532: (SELECT 1 FROM dual WHERE EXISTS
533: (SELECT 1 FROM dual WHERE EXISTS
534: (SELECT 1
535: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
536: WHERE pro.cust_account_id = trx_summ.cust_account_id
537: and del.customer_site_use_id = TRX_SUMM.site_use_ID
538: AND pro.state = 'BROKEN_PROMISE'
539: AND(pro.uwq_status = 'COMPLETE'
543:
544: (SELECT 1 FROM dual WHERE EXISTS
545: (SELECT 1 FROM dual WHERE EXISTS
546: (SELECT 1
547: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
548: WHERE pro.cust_account_id = trx_summ.cust_account_id
549: and del.customer_site_use_id = TRX_SUMM.site_use_ID
550: AND pro.state = 'BROKEN_PROMISE'
551: AND(pro.uwq_status = 'PENDING'
674: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
675: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
676: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
677: (SELECT SUM(b.acctd_amount_due_remaining)
678: FROM iex_delinquencies_all a,
679: ar_payment_schedules_all b
680: WHERE a.cust_account_id = trx_summ.cust_account_id
681: AND a.payment_schedule_id = b.payment_schedule_id
682: AND b.status = 'OP'
692: MAX(trx_summ.creation_date) creation_date,
693: MAX(trx_summ.created_by) created_by,
694: MAX(trx_summ.last_update_login) last_update_login,
695: (SELECT COUNT(1)
696: FROM iex_delinquencies_all
697: WHERE cust_account_id = trx_summ.cust_account_id
698: AND status IN('DELINQUENT', 'PREDELINQUENT')
699: AND org_id = trx_summ.org_id)
700: number_of_delinquencies,
701: (SELECT 1
702: FROM dual
703: WHERE EXISTS
704: (SELECT 1
705: FROM iex_delinquencies_all
706: WHERE cust_account_id = trx_summ.cust_account_id
707: AND status IN('DELINQUENT', 'PREDELINQUENT')
708: AND org_id = trx_summ.org_id
709: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
713: (SELECT 1
714: FROM dual
715: WHERE EXISTS
716: (SELECT 1
717: FROM iex_delinquencies_all
718: WHERE cust_account_id = trx_summ.cust_account_id
719: AND status IN('DELINQUENT', 'PREDELINQUENT')
720: AND org_id = trx_summ.org_id
721: AND(uwq_status = 'COMPLETE'
725: (SELECT 1
726: FROM dual
727: WHERE EXISTS
728: (SELECT 1
729: FROM iex_delinquencies_all WHERE cust_account_id = trx_summ.cust_account_id
730: AND status IN('DELINQUENT', 'PREDELINQUENT')
731: AND org_id = trx_summ.org_id
732: AND(uwq_status = 'PENDING'
733: AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
756: phone.phone_number phone_number,
757: phone.phone_extension phone_extension,
758: (SELECT COUNT(1) FROM iex_bankruptcies bkr WHERE bkr.party_id = party.party_id and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION')) number_of_bankruptcies, -- Changed for bug#7693986
759:
760: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_all DEL
761: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
762: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
763: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
764: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
765: NOT IN ('CURRENT', 'CLOSE')
766: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
767: AND DEL.org_id = trx_summ.org_id) NUMBER_OF_PROMISES ,
768:
769: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
770: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
771: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
772: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
773: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
774: NOT IN ('CURRENT', 'CLOSE')
775: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
776: AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
777:
778: (SELECT SUM(PROMISE_AMOUNT) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
779: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
780: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
781: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
782: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
932: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
933: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
934: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
935: (SELECT SUM(b.acctd_amount_due_remaining)
936: FROM iex_delinquencies_all a,
937: ar_payment_schedules_all b
938: WHERE a.cust_account_id = trx_summ.cust_account_id
939: AND a.payment_schedule_id = b.payment_schedule_id
940: AND b.status = 'OP'
950: MAX(trx_summ.creation_date) creation_date,
951: MAX(trx_summ.created_by) created_by,
952: MAX(trx_summ.last_update_login) last_update_login,
953: (SELECT COUNT(1)
954: FROM iex_delinquencies_all
955: WHERE cust_account_id = trx_summ.cust_account_id
956: AND status IN('DELINQUENT', 'PREDELINQUENT')
957: AND org_id = trx_summ.org_id)
958: number_of_delinquencies,
959: (SELECT 1
960: FROM dual
961: WHERE EXISTS
962: (SELECT 1
963: FROM iex_delinquencies_all
964: WHERE cust_account_id = trx_summ.cust_account_id
965: AND status IN('DELINQUENT', 'PREDELINQUENT')
966: AND org_id = trx_summ.org_id
967: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
971: (SELECT 1
972: FROM dual
973: WHERE EXISTS
974: (SELECT 1
975: FROM iex_delinquencies_all
976: WHERE cust_account_id = trx_summ.cust_account_id
977: AND status IN('DELINQUENT', 'PREDELINQUENT')
978: AND org_id = trx_summ.org_id
979: AND(uwq_status = 'COMPLETE'
983: (SELECT 1
984: FROM dual
985: WHERE EXISTS
986: (SELECT 1
987: FROM iex_delinquencies_all WHERE cust_account_id = trx_summ.cust_account_id
988: AND status IN('DELINQUENT', 'PREDELINQUENT')
989: AND org_id = trx_summ.org_id
990: AND(uwq_status = 'PENDING'
991: AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
1014: phone.phone_number phone_number,
1015: phone.phone_extension phone_extension,
1016: (SELECT COUNT(1) FROM iex_bankruptcies bkr WHERE bkr.party_id = party.party_id and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION')) number_of_bankruptcies, -- Changed for bug#7693986
1017:
1018: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_all DEL
1019: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
1020: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
1021: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
1022: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
1023: NOT IN ('CURRENT', 'CLOSE')
1024: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
1025: AND DEL.org_id = trx_summ.org_id) NUMBER_OF_PROMISES ,
1026:
1027: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
1028: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
1029: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
1030: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
1031: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
1032: NOT IN ('CURRENT', 'CLOSE')
1033: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
1034: AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
1035:
1036: (SELECT SUM(PROMISE_AMOUNT) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
1037: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
1038: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
1039: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
1040: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr
1197: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
1198: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
1199: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
1200: (SELECT SUM(b.acctd_amount_due_remaining)
1201: FROM iex_delinquencies_all a,
1202: ar_payment_schedules_all b
1203: WHERE a.party_cust_id = party.party_id
1204: AND a.payment_schedule_id = b.payment_schedule_id
1205: AND b.status = 'OP'
1215: MAX(trx_summ.creation_date) creation_date,
1216: MAX(trx_summ.created_by) created_by,
1217: MAX(trx_summ.last_update_login) last_update_login,
1218: (SELECT COUNT(1)
1219: FROM iex_delinquencies_all
1220: WHERE party_cust_id = party.party_id
1221: AND status IN('DELINQUENT', 'PREDELINQUENT')
1222: AND org_id = trx_summ.org_id)
1223: number_of_delinquencies,
1224: (SELECT 1
1225: FROM dual
1226: WHERE EXISTS
1227: (SELECT 1
1228: FROM iex_delinquencies_all
1229: WHERE party_cust_id = party.party_id
1230: AND status IN('DELINQUENT', 'PREDELINQUENT')
1231: AND org_id = trx_summ.org_id
1232: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
1236: (SELECT 1
1237: FROM dual
1238: WHERE EXISTS
1239: (SELECT 1
1240: FROM iex_delinquencies_all
1241: WHERE party_cust_id = party.party_id
1242: AND status IN('DELINQUENT', 'PREDELINQUENT')
1243: AND org_id = trx_summ.org_id
1244: AND(uwq_status = 'COMPLETE'
1248: (SELECT 1
1249: FROM dual
1250: WHERE EXISTS
1251: (SELECT 1
1252: FROM iex_delinquencies_all
1253: WHERE party_cust_id = party.party_id
1254: AND status IN('DELINQUENT', 'PREDELINQUENT')
1255: AND org_id = trx_summ.org_id
1256: AND(uwq_status = 'PENDING'
1437: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
1438: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
1439: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
1440: (SELECT SUM(b.acctd_amount_due_remaining)
1441: FROM iex_delinquencies_all a,
1442: ar_payment_schedules_all b
1443: WHERE a.party_cust_id = party.party_id
1444: AND a.payment_schedule_id = b.payment_schedule_id
1445: AND b.status = 'OP'
1455: MAX(trx_summ.creation_date) creation_date,
1456: MAX(trx_summ.created_by) created_by,
1457: MAX(trx_summ.last_update_login) last_update_login,
1458: (SELECT COUNT(1)
1459: FROM iex_delinquencies_all
1460: WHERE party_cust_id = party.party_id
1461: AND status IN('DELINQUENT', 'PREDELINQUENT')
1462: AND org_id = trx_summ.org_id)
1463: number_of_delinquencies,
1464: (SELECT 1
1465: FROM dual
1466: WHERE EXISTS
1467: (SELECT 1
1468: FROM iex_delinquencies_all
1469: WHERE party_cust_id = party.party_id
1470: AND status IN('DELINQUENT', 'PREDELINQUENT')
1471: AND org_id = trx_summ.org_id
1472: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
1476: (SELECT 1
1477: FROM dual
1478: WHERE EXISTS
1479: (SELECT 1
1480: FROM iex_delinquencies_all
1481: WHERE party_cust_id = party.party_id
1482: AND status IN('DELINQUENT', 'PREDELINQUENT')
1483: AND org_id = trx_summ.org_id
1484: AND(uwq_status = 'COMPLETE'
1488: (SELECT 1
1489: FROM dual
1490: WHERE EXISTS
1491: (SELECT 1
1492: FROM iex_delinquencies_all
1493: WHERE party_cust_id = party.party_id
1494: AND status IN('DELINQUENT', 'PREDELINQUENT')
1495: AND org_id = trx_summ.org_id
1496: AND(uwq_status = 'PENDING'
1730: --Start bug 6876187 gnramasa 14th mar 08
1731: CURSOR c_cust_account_id_1 IS
1732: SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
1733: WHERE ARS.REFERENCE_1 IS Null
1734: AND EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
1735: IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
1736: AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID);
1737:
1738: CURSOR c_cust_account_id_n IS
1737:
1738: CURSOR c_cust_account_id_n IS
1739: SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
1740: WHERE ARS.REFERENCE_1 = 1
1741: AND NOT EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
1742: IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
1743: AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID);
1744:
1745: TYPE cust_account_id_list_1 is TABLE of NUMBER INDEX BY BINARY_INTEGER;
1753: CURSOR c_cust_account_id_dln(p_cust_account_id number) IS
1754: SELECT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
1755: WHERE ARS.REFERENCE_1 = 1
1756: AND ARS.CUST_ACCOUNT_ID=P_CUST_ACCOUNT_ID
1757: AND NOT EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
1758: IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
1759: AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID
1760: ) for update of reference_1 nowait;
1761:
2085:
2086: /*update ar_trx_bal_summary set reference_1 = '1'
2087: where cust_account_id in
2088: ( select distinct cust_account_id
2089: from iex_delinquencies_all
2090: where status in ('DELINQUENT','PREDELINQUENT'));*/
2091: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Done updating Reference_1 of AR_TRX_BAL_SUMMARY ');
2092:
2093: end if;
3682: count(1) number_of_delinquencies,
3683: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
3684: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
3685: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
3686: from iex_delinquencies_all del,
3687: iex_pop_uwq_summ_gt temp
3688: WHERE del.customer_site_use_id = temp.object_id AND
3689: del.org_id = temp.org_id and
3690: del.status IN('DELINQUENT', 'PREDELINQUENT')
3695: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
3696: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
3697: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
3698: from iex_promise_details pd,
3699: IEX_DELINQUENCIES_ALL DEL,
3700: iex_pop_uwq_summ_gt temp
3701: WHERE pd.cust_account_id = del.cust_account_id
3702: and pd.delinquency_id = del.delinquency_id
3703: and del.customer_site_use_id = temp.object_id
3710: COUNT(1) number_of_promises,
3711: SUM(amount_due_remaining) broken_promise_amount,
3712: SUM(promise_amount) promise_amount
3713: FROM iex_promise_details pd,
3714: iex_delinquencies_all del,
3715: iex_pop_uwq_summ_gt temp
3716: WHERE pd.cust_account_id = del.cust_account_id
3717: AND pd.delinquency_id = del.delinquency_id
3718: AND pd.status IN('COLLECTABLE', 'PENDING')
3738:
3739: cursor c_billto_past_due is
3740: SELECT a.customer_site_use_id,
3741: SUM(b.acctd_amount_due_remaining) past_due_inv_value
3742: FROM iex_delinquencies_all a,
3743: ar_payment_schedules_all b,
3744: iex_pop_uwq_summ_gt temp
3745: WHERE a.customer_site_use_id = temp.object_id
3746: AND a.payment_schedule_id = b.payment_schedule_id
4910: count(1) number_of_delinquencies,
4911: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
4912: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
4913: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
4914: from iex_delinquencies_all del,
4915: iex_pop_uwq_summ_gt temp
4916: WHERE del.cust_account_id = temp.object_id AND
4917: del.org_id = temp.org_id and
4918: del.status IN('DELINQUENT', 'PREDELINQUENT')
4923: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
4924: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
4925: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
4926: from iex_promise_details pd,
4927: IEX_DELINQUENCIES_ALL DEL,
4928: iex_pop_uwq_summ_gt temp
4929: WHERE pd.cust_account_id = del.cust_account_id
4930: and pd.delinquency_id = del.delinquency_id
4931: and del.cust_account_id = temp.object_id
4938: COUNT(1) number_of_promises,
4939: SUM(amount_due_remaining) broken_promise_amount,
4940: SUM(promise_amount) promise_amount
4941: FROM iex_promise_details pd,
4942: iex_delinquencies_all del,
4943: iex_pop_uwq_summ_gt temp
4944: WHERE pd.cust_account_id = del.cust_account_id
4945: AND pd.delinquency_id = del.delinquency_id
4946: AND pd.status IN('COLLECTABLE', 'PENDING')
4966:
4967: cursor c_account_past_due is
4968: SELECT a.cust_account_id,
4969: SUM(b.acctd_amount_due_remaining) past_due_inv_value
4970: FROM iex_delinquencies_all a,
4971: ar_payment_schedules_all b,
4972: iex_pop_uwq_summ_gt temp
4973: WHERE a.cust_account_id = temp.object_id
4974: AND a.payment_schedule_id = b.payment_schedule_id
6130: count(1) number_of_delinquencies,
6131: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
6132: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
6133: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
6134: from iex_delinquencies_all del,
6135: iex_pop_uwq_summ_gt temp
6136: WHERE del.party_cust_id = temp.object_id AND
6137: del.org_id = temp.org_id and
6138: del.status IN('DELINQUENT', 'PREDELINQUENT')
6143: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
6144: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
6145: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
6146: from iex_promise_details pd,
6147: IEX_DELINQUENCIES_ALL DEL,
6148: iex_pop_uwq_summ_gt temp
6149: WHERE pd.cust_account_id = del.cust_account_id
6150: AND pd.delinquency_id = del.delinquency_id
6151: and del.party_cust_id = temp.object_id
6158: COUNT(1) number_of_promises,
6159: SUM(amount_due_remaining) broken_promise_amount,
6160: SUM(promise_amount) promise_amount
6161: FROM iex_promise_details pd,
6162: iex_delinquencies_all del,
6163: iex_pop_uwq_summ_gt temp
6164: WHERE pd.cust_account_id = del.cust_account_id
6165: AND pd.delinquency_id = del.delinquency_id
6166: AND pd.status IN('COLLECTABLE','PENDING')
6185:
6186: cursor c_customer_past_due is
6187: SELECT a.party_cust_id,
6188: SUM(b.acctd_amount_due_remaining) past_due_inv_value
6189: FROM iex_delinquencies_all a,
6190: ar_payment_schedules_all b,
6191: iex_pop_uwq_summ_gt temp
6192: WHERE a.party_cust_id = temp.object_id
6193: AND a.payment_schedule_id = b.payment_schedule_id