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.IEX_UWQ_POP_SUM_TBL_PVT dependencies on IEX_DELINQUENCIES_ALL

Line 128: AND EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE

124: --Start bug 6876187 gnramasa 14th mar 08
125: CURSOR c_cust_account_id_1 IS
126: SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
127: WHERE ARS.REFERENCE_1 IS Null
128: AND EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
129: IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
130: AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID);
131: -- and ied.org_id=nvl(p_org_id,ied.org_id));
132:

Line 136: AND NOT EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE

132:
133: CURSOR c_cust_account_id_n IS
134: SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
135: WHERE ARS.REFERENCE_1 = 1
136: AND NOT EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
137: IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
138: AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID);
139: -- and ied.org_id=nvl(p_org_id,ied.org_id));
140:

Line 153: AND NOT EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE

149: CURSOR c_cust_account_id_dln(p_cust_account_id number) IS
150: SELECT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
151: WHERE ARS.REFERENCE_1 = 1
152: AND ARS.CUST_ACCOUNT_ID=P_CUST_ACCOUNT_ID
153: AND NOT EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
154: IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
155: AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID)
156: -- and ied.org_id=nvl(p_org_id,ied.org_id))
157: for update of reference_1 nowait;

Line 362: from iex_delinquencies_all

358:
359: /*update ar_trx_bal_summary set reference_1 = '1'
360: where cust_account_id in
361: ( select distinct cust_account_id
362: from iex_delinquencies_all
363: where status in ('DELINQUENT','PREDELINQUENT'));*/
364: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Done updating Reference_1 of AR_TRX_BAL_SUMMARY ');
365:
366: -- end if;

Line 981: FROM iex_delinquencies_all a,

977: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
978: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
979: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
980: (SELECT SUM(b.acctd_amount_due_remaining)
981: FROM iex_delinquencies_all a,
982: ar_payment_schedules_all b
983: WHERE a.customer_site_use_id = trx_summ.site_use_id
984: AND a.payment_schedule_id = b.payment_schedule_id
985: AND b.status = 'OP'

Line 999: FROM iex_delinquencies_all

995: MAX(trx_summ.creation_date) creation_date,
996: MAX(trx_summ.created_by) created_by,
997: MAX(trx_summ.last_update_login) last_update_login,
998: (SELECT COUNT(1)
999: FROM iex_delinquencies_all
1000: WHERE customer_site_use_id = trx_summ.site_use_id
1001: AND status IN('DELINQUENT', 'PREDELINQUENT')
1002: AND org_id = trx_summ.org_id)
1003: number_of_delinquencies,

Line 1008: FROM iex_delinquencies_all

1004: (SELECT 1
1005: FROM dual
1006: WHERE EXISTS
1007: (SELECT 1
1008: FROM iex_delinquencies_all
1009: WHERE customer_site_use_id = trx_summ.site_use_id
1010: AND status IN('DELINQUENT', 'PREDELINQUENT')
1011: AND org_id = trx_summ.org_id
1012: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)

Line 1020: FROM iex_delinquencies_all

1016: (SELECT 1
1017: FROM dual
1018: WHERE EXISTS
1019: (SELECT 1
1020: FROM iex_delinquencies_all
1021: WHERE customer_site_use_id = trx_summ.site_use_id
1022: AND status IN('DELINQUENT', 'PREDELINQUENT')
1023: AND org_id = trx_summ.org_id
1024: AND(uwq_status = 'COMPLETE'

Line 1032: FROM iex_delinquencies_all where customer_site_use_id = trx_summ.site_use_id

1028: (SELECT 1
1029: FROM dual
1030: WHERE EXISTS
1031: (SELECT 1
1032: FROM iex_delinquencies_all where customer_site_use_id = trx_summ.site_use_id
1033: AND status IN('DELINQUENT', 'PREDELINQUENT')
1034: AND org_id = trx_summ.org_id
1035: AND(uwq_status = 'PENDING'
1036: AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))

Line 1081: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1077: phone.phone_extension phone_extension,
1078: (SELECT COUNT(1) FROM iex_bankruptcies bkr
1079: WHERE bkr.party_id = party.party_id and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION')) number_of_bankruptcies, -- Changed for bug#7693986
1080:
1081: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1082: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
1083: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
1084: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
1085: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 1090: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1086: NOT IN ('CURRENT', 'CLOSE')
1087: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
1088: AND DEL.org_id = trx_summ.org_id) number_of_promises,
1089:
1090: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1091: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
1092: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
1093: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
1094: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 1099: (SELECT SUM(PROMISE_AMOUNT) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1095: NOT IN ('CURRENT', 'CLOSE')
1096: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
1097: AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
1098:
1099: (SELECT SUM(PROMISE_AMOUNT) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1100: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
1101: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
1102: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
1103: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 1111: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1107:
1108: (SELECT 1 FROM dual WHERE EXISTS
1109: (SELECT 1 FROM dual WHERE EXISTS
1110: (SELECT 1
1111: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1112: WHERE pro.cust_account_id = trx_summ.cust_account_id
1113: and del.customer_site_use_id = TRX_SUMM.site_use_ID
1114: AND pro.state = 'BROKEN_PROMISE'
1115: AND(pro.uwq_status IS NULL OR pro.uwq_status = 'ACTIVE' OR(TRUNC(pro.uwq_active_date) <= TRUNC(sysdate)

Line 1123: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1119:
1120: (SELECT 1 FROM dual WHERE EXISTS
1121: (SELECT 1 FROM dual WHERE EXISTS
1122: (SELECT 1
1123: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1124: WHERE pro.cust_account_id = trx_summ.cust_account_id
1125: and del.customer_site_use_id = TRX_SUMM.site_use_ID
1126: AND pro.state = 'BROKEN_PROMISE'
1127: AND(pro.uwq_status = 'COMPLETE'

Line 1135: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1131:
1132: (SELECT 1 FROM dual WHERE EXISTS
1133: (SELECT 1 FROM dual WHERE EXISTS
1134: (SELECT 1
1135: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1136: WHERE pro.cust_account_id = trx_summ.cust_account_id
1137: and del.customer_site_use_id = TRX_SUMM.site_use_ID
1138: AND pro.state = 'BROKEN_PROMISE'
1139: AND(pro.uwq_status = 'PENDING'

Line 1322: FROM iex_delinquencies_all a,

1318: cursor c_billto_deln_cnt is
1319: SELECT a.customer_site_use_id,
1320: count(a.delinquency_id) number_of_delinquencies,
1321: SUM(b.acctd_amount_due_remaining) past_due_inv_value
1322: FROM iex_delinquencies_all a,
1323: ar_payment_schedules_all b,
1324: iex_dln_uwq_summary dln
1325: WHERE a.customer_site_use_id =dln.site_use_id
1326: AND a.payment_schedule_id = b.payment_schedule_id

Line 1339: from iex_delinquencies_all del,

1335: select del.CUSTOMER_SITE_USE_ID,
1336: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
1337: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
1338: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
1339: from iex_delinquencies_all del,
1340: iex_dln_uwq_summary dln
1341: WHERE del.customer_site_use_id = dln.site_use_id AND
1342: del.org_id = dln.org_id and
1343: del.status IN('DELINQUENT', 'PREDELINQUENT')

Line 1359: iex_delinquencies_all del,

1355: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, dln.currency, sysdate,
1356: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), pd.promise_amount)) promise_amount
1357: -- End Bug 14197188 bibeura 26-Jun-2012
1358: FROM iex_promise_details pd,
1359: iex_delinquencies_all del,
1360: iex_dln_uwq_summary dln
1361: WHERE pd.cust_account_id = del.cust_account_id
1362: AND pd.delinquency_id = del.delinquency_id
1363: AND pd.status IN('COLLECTABLE', 'PENDING')

Line 1378: IEX_DELINQUENCIES_ALL DEL,

1374: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
1375: 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,
1376: 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
1377: from iex_promise_details pd,
1378: IEX_DELINQUENCIES_ALL DEL,
1379: iex_dln_uwq_summary dln
1380: WHERE pd.cust_account_id = del.cust_account_id
1381: and pd.delinquency_id = del.delinquency_id
1382: and del.customer_site_use_id = dln.site_use_id

Line 1601: FROM iex_delinquencies_all a,

1597: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
1598: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
1599: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
1600: (SELECT SUM(b.acctd_amount_due_remaining)
1601: FROM iex_delinquencies_all a,
1602: ar_payment_schedules_all b
1603: WHERE a.customer_site_use_id = trx_summ.site_use_id
1604: AND a.payment_schedule_id = b.payment_schedule_id
1605: AND b.status = 'OP'

Line 1619: FROM iex_delinquencies_all

1615: MAX(trx_summ.creation_date) creation_date,
1616: MAX(trx_summ.created_by) created_by,
1617: MAX(trx_summ.last_update_login) last_update_login,
1618: (SELECT COUNT(1)
1619: FROM iex_delinquencies_all
1620: WHERE customer_site_use_id = trx_summ.site_use_id
1621: AND status IN('DELINQUENT', 'PREDELINQUENT')
1622: AND org_id = trx_summ.org_id)
1623: number_of_delinquencies,

Line 1628: FROM iex_delinquencies_all

1624: (SELECT 1
1625: FROM dual
1626: WHERE EXISTS
1627: (SELECT 1
1628: FROM iex_delinquencies_all
1629: WHERE customer_site_use_id = trx_summ.site_use_id
1630: AND status IN('DELINQUENT', 'PREDELINQUENT')
1631: AND org_id = trx_summ.org_id
1632: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)

Line 1640: FROM iex_delinquencies_all

1636: (SELECT 1
1637: FROM dual
1638: WHERE EXISTS
1639: (SELECT 1
1640: FROM iex_delinquencies_all
1641: WHERE customer_site_use_id = trx_summ.site_use_id
1642: AND status IN('DELINQUENT', 'PREDELINQUENT')
1643: AND org_id = trx_summ.org_id
1644: AND(uwq_status = 'COMPLETE'

Line 1652: FROM iex_delinquencies_all where customer_site_use_id = trx_summ.site_use_id

1648: (SELECT 1
1649: FROM dual
1650: WHERE EXISTS
1651: (SELECT 1
1652: FROM iex_delinquencies_all where customer_site_use_id = trx_summ.site_use_id
1653: AND status IN('DELINQUENT', 'PREDELINQUENT')
1654: AND org_id = trx_summ.org_id
1655: AND(uwq_status = 'PENDING'
1656: AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))

Line 1700: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1696: phone.phone_number phone_number,
1697: phone.phone_extension phone_extension,
1698: (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
1699:
1700: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1701: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
1702: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
1703: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
1704: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 1709: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1705: NOT IN ('CURRENT', 'CLOSE')
1706: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV')) --Added for Bug 6446848 06-Jan-2009 barathsr
1707: AND DEL.org_id = trx_summ.org_id) number_of_promises,
1708:
1709: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1710: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
1711: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
1712: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
1713: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 1718: (SELECT SUM(PROMISE_AMOUNT) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1714: NOT IN ('CURRENT', 'CLOSE')
1715: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV')) --Added for Bug 6446848 06-Jan-2009 barathsr
1716: AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
1717:
1718: (SELECT SUM(PROMISE_AMOUNT) FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1719: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID and del.customer_site_use_id = TRX_SUMM.site_use_ID AND
1720: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
1721: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
1722: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 1730: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1726:
1727: (SELECT 1 FROM dual WHERE EXISTS
1728: (SELECT 1 FROM dual WHERE EXISTS
1729: (SELECT 1
1730: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1731: WHERE pro.cust_account_id = trx_summ.cust_account_id
1732: and del.customer_site_use_id = TRX_SUMM.site_use_ID
1733: AND pro.state = 'BROKEN_PROMISE'
1734: AND(pro.uwq_status IS NULL OR pro.uwq_status = 'ACTIVE' OR(TRUNC(pro.uwq_active_date) <= TRUNC(sysdate)

Line 1742: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1738:
1739: (SELECT 1 FROM dual WHERE EXISTS
1740: (SELECT 1 FROM dual WHERE EXISTS
1741: (SELECT 1
1742: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1743: WHERE pro.cust_account_id = trx_summ.cust_account_id
1744: and del.customer_site_use_id = TRX_SUMM.site_use_ID
1745: AND pro.state = 'BROKEN_PROMISE'
1746: AND(pro.uwq_status = 'COMPLETE'

Line 1754: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL

1750:
1751: (SELECT 1 FROM dual WHERE EXISTS
1752: (SELECT 1 FROM dual WHERE EXISTS
1753: (SELECT 1
1754: FROM iex_promise_details PRO, IEX_DELINQUENCIES_ALL DEL
1755: WHERE pro.cust_account_id = trx_summ.cust_account_id
1756: and del.customer_site_use_id = TRX_SUMM.site_use_ID
1757: AND pro.state = 'BROKEN_PROMISE'
1758: AND(pro.uwq_status = 'PENDING'

Line 1949: FROM iex_delinquencies_all a,

1945: is
1946: SELECT a.customer_site_use_id,
1947: count(a.delinquency_id) number_of_delinquencies,
1948: SUM(b.acctd_amount_due_remaining) past_due_inv_value
1949: FROM iex_delinquencies_all a,
1950: ar_payment_schedules_all b,
1951: iex_dln_uwq_summary dln
1952: WHERE a.customer_site_use_id =dln.site_use_id
1953: AND a.payment_schedule_id = b.payment_schedule_id

Line 1969: from iex_delinquencies_all del,

1965: select del.CUSTOMER_SITE_USE_ID,
1966: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
1967: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
1968: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
1969: from iex_delinquencies_all del,
1970: iex_dln_uwq_summary dln
1971: WHERE del.customer_site_use_id = dln.site_use_id AND
1972: del.org_id = dln.org_id and
1973: del.status IN('DELINQUENT', 'PREDELINQUENT')

Line 1992: iex_delinquencies_all del,

1988: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, dln.currency, sysdate,
1989: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), pd.promise_amount)) promise_amount
1990: -- End Bug 14197188 bibeura 26-Jun-2012
1991: FROM iex_promise_details pd,
1992: iex_delinquencies_all del,
1993: iex_dln_uwq_summary dln
1994: WHERE pd.cust_account_id = del.cust_account_id
1995: AND pd.delinquency_id = del.delinquency_id
1996: AND pd.status IN('COLLECTABLE', 'PENDING')

Line 2013: IEX_DELINQUENCIES_ALL DEL,

2009: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
2010: 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,
2011: 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
2012: from iex_promise_details pd,
2013: IEX_DELINQUENCIES_ALL DEL,
2014: iex_dln_uwq_summary dln
2015: WHERE pd.cust_account_id = del.cust_account_id
2016: and pd.delinquency_id = del.delinquency_id
2017: and del.customer_site_use_id = dln.site_use_id

Line 2264: FROM iex_delinquencies_all a,

2260: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
2261: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
2262: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
2263: (SELECT SUM(b.acctd_amount_due_remaining)
2264: FROM iex_delinquencies_all a,
2265: ar_payment_schedules_all b
2266: WHERE a.cust_account_id = trx_summ.cust_account_id
2267: AND a.payment_schedule_id = b.payment_schedule_id
2268: AND b.status = 'OP'

Line 2282: FROM iex_delinquencies_all

2278: MAX(trx_summ.creation_date) creation_date,
2279: MAX(trx_summ.created_by) created_by,
2280: MAX(trx_summ.last_update_login) last_update_login,
2281: (SELECT COUNT(1)
2282: FROM iex_delinquencies_all
2283: WHERE cust_account_id = trx_summ.cust_account_id
2284: AND status IN('DELINQUENT', 'PREDELINQUENT')
2285: AND org_id = trx_summ.org_id)
2286: number_of_delinquencies,

Line 2291: FROM iex_delinquencies_all

2287: (SELECT 1
2288: FROM dual
2289: WHERE EXISTS
2290: (SELECT 1
2291: FROM iex_delinquencies_all
2292: WHERE cust_account_id = trx_summ.cust_account_id
2293: AND status IN('DELINQUENT', 'PREDELINQUENT')
2294: AND org_id = trx_summ.org_id
2295: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)

Line 2303: FROM iex_delinquencies_all

2299: (SELECT 1
2300: FROM dual
2301: WHERE EXISTS
2302: (SELECT 1
2303: FROM iex_delinquencies_all
2304: WHERE cust_account_id = trx_summ.cust_account_id
2305: AND status IN('DELINQUENT', 'PREDELINQUENT')
2306: AND org_id = trx_summ.org_id
2307: AND(uwq_status = 'COMPLETE'

Line 2315: FROM iex_delinquencies_all WHERE cust_account_id = trx_summ.cust_account_id

2311: (SELECT 1
2312: FROM dual
2313: WHERE EXISTS
2314: (SELECT 1
2315: FROM iex_delinquencies_all WHERE cust_account_id = trx_summ.cust_account_id
2316: AND status IN('DELINQUENT', 'PREDELINQUENT')
2317: AND org_id = trx_summ.org_id
2318: AND(uwq_status = 'PENDING'
2319: AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))

Line 2356: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_all DEL

2352: phone.phone_number phone_number,
2353: phone.phone_extension phone_extension,
2354: (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
2355:
2356: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_all DEL
2357: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
2358: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
2359: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
2360: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 2365: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL

2361: NOT IN ('CURRENT', 'CLOSE')
2362: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
2363: AND DEL.org_id = trx_summ.org_id) NUMBER_OF_PROMISES ,
2364:
2365: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
2366: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
2367: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
2368: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
2369: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 2374: (SELECT SUM(PROMISE_AMOUNT) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL

2370: NOT IN ('CURRENT', 'CLOSE')
2371: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
2372: AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
2373:
2374: (SELECT SUM(PROMISE_AMOUNT) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
2375: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
2376: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
2377: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
2378: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 2576: FROM iex_delinquencies_all a,

2572: cursor c_acc_deln_cnt is
2573: SELECT a.cust_account_id,dln.org_id,
2574: count(a.delinquency_id) number_of_delinquencies,
2575: SUM(b.acctd_amount_due_remaining) past_due_inv_value
2576: FROM iex_delinquencies_all a,
2577: ar_payment_schedules_all b,
2578: iex_dln_uwq_summary dln
2579: WHERE a.cust_account_id =dln.cust_account_id
2580: AND a.payment_schedule_id = b.payment_schedule_id

Line 2593: from iex_delinquencies_all del,

2589: select del.cust_account_ID,dln.org_id,
2590: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
2591: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
2592: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
2593: from iex_delinquencies_all del,
2594: iex_dln_uwq_summary dln
2595: WHERE del.cust_account_id = dln.cust_account_id AND
2596: del.org_id = dln.org_id and
2597: del.status IN('DELINQUENT', 'PREDELINQUENT')

Line 2612: iex_delinquencies_all del,

2608: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, dln.currency, sysdate,
2609: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), pd.promise_amount)) promise_amount
2610: -- End Bug 14197188 bibeura 26-Jun-2012
2611: FROM iex_promise_details pd,
2612: iex_delinquencies_all del,
2613: iex_dln_uwq_summary dln
2614: WHERE dln.cust_account_id = del.cust_account_id
2615: AND pd.delinquency_id = del.delinquency_id
2616: AND pd.status IN('COLLECTABLE', 'PENDING')

Line 2631: IEX_DELINQUENCIES_ALL DEL,

2627: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
2628: 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,
2629: 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
2630: from iex_promise_details pd,
2631: IEX_DELINQUENCIES_ALL DEL,
2632: iex_dln_uwq_summary dln
2633: WHERE dln.cust_account_id = del.cust_account_id
2634: and pd.delinquency_id = del.delinquency_id
2635: and dln.site_use_id is null

Line 2847: FROM iex_delinquencies_all a,

2843: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
2844: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
2845: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
2846: (SELECT SUM(b.acctd_amount_due_remaining)
2847: FROM iex_delinquencies_all a,
2848: ar_payment_schedules_all b
2849: WHERE a.cust_account_id = trx_summ.cust_account_id
2850: AND a.payment_schedule_id = b.payment_schedule_id
2851: AND b.status = 'OP'

Line 2865: FROM iex_delinquencies_all

2861: MAX(trx_summ.creation_date) creation_date,
2862: MAX(trx_summ.created_by) created_by,
2863: MAX(trx_summ.last_update_login) last_update_login,
2864: (SELECT COUNT(1)
2865: FROM iex_delinquencies_all
2866: WHERE cust_account_id = trx_summ.cust_account_id
2867: AND status IN('DELINQUENT', 'PREDELINQUENT')
2868: AND org_id = trx_summ.org_id)
2869: number_of_delinquencies,

Line 2874: FROM iex_delinquencies_all

2870: (SELECT 1
2871: FROM dual
2872: WHERE EXISTS
2873: (SELECT 1
2874: FROM iex_delinquencies_all
2875: WHERE cust_account_id = trx_summ.cust_account_id
2876: AND status IN('DELINQUENT', 'PREDELINQUENT')
2877: AND org_id = trx_summ.org_id
2878: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)

Line 2886: FROM iex_delinquencies_all

2882: (SELECT 1
2883: FROM dual
2884: WHERE EXISTS
2885: (SELECT 1
2886: FROM iex_delinquencies_all
2887: WHERE cust_account_id = trx_summ.cust_account_id
2888: AND status IN('DELINQUENT', 'PREDELINQUENT')
2889: AND org_id = trx_summ.org_id
2890: AND(uwq_status = 'COMPLETE'

Line 2898: FROM iex_delinquencies_all WHERE cust_account_id = trx_summ.cust_account_id

2894: (SELECT 1
2895: FROM dual
2896: WHERE EXISTS
2897: (SELECT 1
2898: FROM iex_delinquencies_all WHERE cust_account_id = trx_summ.cust_account_id
2899: AND status IN('DELINQUENT', 'PREDELINQUENT')
2900: AND org_id = trx_summ.org_id
2901: AND(uwq_status = 'PENDING'
2902: AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))

Line 2938: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_all DEL

2934: phone.phone_number phone_number,
2935: phone.phone_extension phone_extension,
2936: (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
2937:
2938: (SELECT COUNT(1) FROM iex_promise_details PRO, IEX_DELINQUENCIES_all DEL
2939: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
2940: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
2941: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
2942: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 2947: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL

2943: NOT IN ('CURRENT', 'CLOSE')
2944: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
2945: AND DEL.org_id = trx_summ.org_id) NUMBER_OF_PROMISES ,
2946:
2947: (SELECT SUM(AMOUNT_DUE_REMAINING) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
2948: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
2949: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
2950: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
2951: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 2956: (SELECT SUM(PROMISE_AMOUNT) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL

2952: NOT IN ('CURRENT', 'CLOSE')
2953: or (del.status='CURRENT' and del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 06-Jan-2009 barathsr
2954: AND DEL.org_id = trx_summ.org_id) BROKEN_PROMISE_AMOUNT ,
2955:
2956: (SELECT SUM(PROMISE_AMOUNT) FROM IEX_PROMISE_DETAILS PRO, IEX_DELINQUENCIES_all DEL
2957: WHERE PRO.CUST_ACCOUNT_ID = TRX_SUMM.CUST_ACCOUNT_ID AND
2958: PRO.STATUS IN ('COLLECTABLE', 'PENDING') AND PRO.STATE = 'BROKEN_PROMISE' AND PRO.AMOUNT_DUE_REMAINING > 0 AND
2959: PRO.DELINQUENCY_ID = DEL.DELINQUENCY_ID(+)
2960: AND (DEL.STATUS --(+) Commented for Bug 6446848 06-Jan-2009 barathsr

Line 3168: FROM iex_delinquencies_all a,

3164: cursor c_acc_deln_cnt_dt is
3165: SELECT a.cust_account_id,dln.org_id,
3166: count(a.delinquency_id) number_of_delinquencies,
3167: SUM(b.acctd_amount_due_remaining) past_due_inv_value
3168: FROM iex_delinquencies_all a,
3169: ar_payment_schedules_all b,
3170: iex_dln_uwq_summary dln
3171: WHERE a.cust_account_id =dln.cust_account_id
3172: AND a.payment_schedule_id = b.payment_schedule_id

Line 3185: from iex_delinquencies_all del,

3181: select del.cust_account_ID,dln.org_id,
3182: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
3183: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
3184: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
3185: from iex_delinquencies_all del,
3186: iex_dln_uwq_summary dln
3187: WHERE del.cust_account_id = dln.cust_account_id
3188: AND del.org_id = dln.org_id
3189: and del.status IN('DELINQUENT', 'PREDELINQUENT')

Line 3206: iex_delinquencies_all del,

3202: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, dln.currency, sysdate,
3203: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), pd.promise_amount)) promise_amount
3204: -- End Bug 14197188 bibeura 26-Jun-2012
3205: FROM iex_promise_details pd,
3206: iex_delinquencies_all del,
3207: iex_dln_uwq_summary dln
3208: WHERE dln.cust_account_id = del.cust_account_id
3209: AND pd.delinquency_id = del.delinquency_id
3210: AND pd.status IN('COLLECTABLE', 'PENDING')

Line 3227: IEX_DELINQUENCIES_ALL DEL,

3223: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
3224: 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,
3225: 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
3226: from iex_promise_details pd,
3227: IEX_DELINQUENCIES_ALL DEL,
3228: iex_dln_uwq_summary dln
3229: WHERE dln.cust_account_id = del.cust_account_id
3230: and pd.delinquency_id = del.delinquency_id
3231: and dln.site_use_id is null

Line 3475: FROM iex_delinquencies_all a,

3471: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
3472: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
3473: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
3474: (SELECT SUM(b.acctd_amount_due_remaining)
3475: FROM iex_delinquencies_all a,
3476: ar_payment_schedules_all b
3477: WHERE a.party_cust_id = party.party_id
3478: AND a.payment_schedule_id = b.payment_schedule_id
3479: AND b.status = 'OP'

Line 3493: FROM iex_delinquencies_all

3489: MAX(trx_summ.creation_date) creation_date,
3490: MAX(trx_summ.created_by) created_by,
3491: MAX(trx_summ.last_update_login) last_update_login,
3492: (SELECT COUNT(1)
3493: FROM iex_delinquencies_all
3494: WHERE party_cust_id = party.party_id
3495: AND status IN('DELINQUENT', 'PREDELINQUENT')
3496: AND org_id = trx_summ.org_id)
3497: number_of_delinquencies,

Line 3502: FROM iex_delinquencies_all

3498: (SELECT 1
3499: FROM dual
3500: WHERE EXISTS
3501: (SELECT 1
3502: FROM iex_delinquencies_all
3503: WHERE party_cust_id = party.party_id
3504: AND status IN('DELINQUENT', 'PREDELINQUENT')
3505: AND org_id = trx_summ.org_id
3506: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)

Line 3514: FROM iex_delinquencies_all

3510: (SELECT 1
3511: FROM dual
3512: WHERE EXISTS
3513: (SELECT 1
3514: FROM iex_delinquencies_all
3515: WHERE party_cust_id = party.party_id
3516: AND status IN('DELINQUENT', 'PREDELINQUENT')
3517: AND org_id = trx_summ.org_id
3518: AND(uwq_status = 'COMPLETE'

Line 3526: FROM iex_delinquencies_all

3522: (SELECT 1
3523: FROM dual
3524: WHERE EXISTS
3525: (SELECT 1
3526: FROM iex_delinquencies_all
3527: WHERE party_cust_id = party.party_id
3528: AND status IN('DELINQUENT', 'PREDELINQUENT')
3529: AND org_id = trx_summ.org_id
3530: AND(uwq_status = 'PENDING'

Line 3776: FROM iex_delinquencies_all a,

3772: cursor c_cu_deln_cnt is
3773: SELECT a.party_cust_id,dln.org_id,
3774: count(a.delinquency_id) number_of_delinquencies,
3775: SUM(b.acctd_amount_due_remaining) past_due_inv_value
3776: FROM iex_delinquencies_all a,
3777: ar_payment_schedules_all b,
3778: iex_dln_uwq_summary dln
3779: WHERE a.party_cust_id =dln.party_id
3780: AND a.payment_schedule_id = b.payment_schedule_id

Line 3794: from iex_delinquencies_all del,

3790: select del.party_cust_ID,dln.org_id,
3791: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
3792: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
3793: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
3794: from iex_delinquencies_all del,
3795: iex_dln_uwq_summary dln
3796: WHERE del.party_cust_id = dln.party_id AND
3797: del.org_id = dln.org_id and
3798: del.status IN('DELINQUENT', 'PREDELINQUENT')

Line 3813: iex_delinquencies_all del,

3809: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, dln.currency, sysdate,
3810: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), pd.promise_amount)) promise_amount
3811: -- End Bug 14197188 bibeura 26-Jun-2012
3812: FROM iex_promise_details pd,
3813: iex_delinquencies_all del,
3814: iex_dln_uwq_summary dln
3815: WHERE dln.party_id = del.party_cust_id
3816: AND pd.delinquency_id = del.delinquency_id
3817: AND pd.status IN('COLLECTABLE', 'PENDING')

Line 3833: IEX_DELINQUENCIES_ALL DEL,

3829: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
3830: 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,
3831: 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
3832: from iex_promise_details pd,
3833: IEX_DELINQUENCIES_ALL DEL,
3834: iex_dln_uwq_summary dln
3835: WHERE dln.party_id = del.party_cust_id
3836: and pd.delinquency_id = del.delinquency_id
3837: and dln.site_use_id is null

Line 4054: FROM iex_delinquencies_all a,

4050: SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
4051: gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
4052: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
4053: (SELECT SUM(b.acctd_amount_due_remaining)
4054: FROM iex_delinquencies_all a,
4055: ar_payment_schedules_all b
4056: WHERE a.party_cust_id = party.party_id
4057: AND a.payment_schedule_id = b.payment_schedule_id
4058: AND b.status = 'OP'

Line 4072: FROM iex_delinquencies_all

4068: MAX(trx_summ.creation_date) creation_date,
4069: MAX(trx_summ.created_by) created_by,
4070: MAX(trx_summ.last_update_login) last_update_login,
4071: (SELECT COUNT(1)
4072: FROM iex_delinquencies_all
4073: WHERE party_cust_id = party.party_id
4074: AND status IN('DELINQUENT', 'PREDELINQUENT')
4075: AND org_id = trx_summ.org_id)
4076: number_of_delinquencies,

Line 4081: FROM iex_delinquencies_all

4077: (SELECT 1
4078: FROM dual
4079: WHERE EXISTS
4080: (SELECT 1
4081: FROM iex_delinquencies_all
4082: WHERE party_cust_id = party.party_id
4083: AND status IN('DELINQUENT', 'PREDELINQUENT')
4084: AND org_id = trx_summ.org_id
4085: AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)

Line 4093: FROM iex_delinquencies_all

4089: (SELECT 1
4090: FROM dual
4091: WHERE EXISTS
4092: (SELECT 1
4093: FROM iex_delinquencies_all
4094: WHERE party_cust_id = party.party_id
4095: AND status IN('DELINQUENT', 'PREDELINQUENT')
4096: AND org_id = trx_summ.org_id
4097: AND(uwq_status = 'COMPLETE'

Line 4105: FROM iex_delinquencies_all

4101: (SELECT 1
4102: FROM dual
4103: WHERE EXISTS
4104: (SELECT 1
4105: FROM iex_delinquencies_all
4106: WHERE party_cust_id = party.party_id
4107: AND status IN('DELINQUENT', 'PREDELINQUENT')
4108: AND org_id = trx_summ.org_id
4109: AND(uwq_status = 'PENDING'

Line 4359: FROM iex_delinquencies_all a,

4355: cursor c_cu_deln_cnt_dt is
4356: SELECT a.party_cust_id,dln.org_id,
4357: count(a.delinquency_id) number_of_delinquencies,
4358: SUM(b.acctd_amount_due_remaining) past_due_inv_value
4359: FROM iex_delinquencies_all a,
4360: ar_payment_schedules_all b,
4361: iex_dln_uwq_summary dln
4362: WHERE a.party_cust_id =dln.party_id
4363: AND a.payment_schedule_id = b.payment_schedule_id

Line 4379: from iex_delinquencies_all del,

4375: select del.party_cust_ID,dln.org_id,
4376: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
4377: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
4378: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
4379: from iex_delinquencies_all del,
4380: iex_dln_uwq_summary dln
4381: WHERE del.party_cust_id = dln.party_id AND
4382: del.org_id = dln.org_id and
4383: del.status IN('DELINQUENT', 'PREDELINQUENT')

Line 4402: iex_delinquencies_all del,

4398: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, dln.currency, sysdate,
4399: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), pd.promise_amount)) promise_amount
4400: -- End Bug 14197188 bibeura 26-Jun-2012
4401: FROM iex_promise_details pd,
4402: iex_delinquencies_all del,
4403: iex_dln_uwq_summary dln
4404: WHERE dln.party_id = del.party_cust_id
4405: AND pd.delinquency_id = del.delinquency_id
4406: AND pd.status IN('COLLECTABLE', 'PENDING')

Line 4424: IEX_DELINQUENCIES_ALL DEL,

4420: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
4421: 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,
4422: 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
4423: from iex_promise_details pd,
4424: IEX_DELINQUENCIES_ALL DEL,
4425: iex_dln_uwq_summary dln
4426: WHERE dln.party_id = del.party_cust_id
4427: and pd.delinquency_id = del.delinquency_id
4428: and dln.site_use_id is null

Line 9858: from iex_delinquencies_all del,

9854: count(1) number_of_delinquencies,
9855: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
9856: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
9857: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
9858: from iex_delinquencies_all del,
9859: iex_pop_uwq_summ_gt temp
9860: WHERE del.customer_site_use_id = temp.object_id AND
9861: del.org_id = temp.org_id and
9862: del.status IN('DELINQUENT', 'PREDELINQUENT')

Line 9871: IEX_DELINQUENCIES_ALL DEL,

9867: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
9868: 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,
9869: 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
9870: from iex_promise_details pd,
9871: IEX_DELINQUENCIES_ALL DEL,
9872: iex_pop_uwq_summ_gt temp
9873: WHERE pd.cust_account_id = del.cust_account_id
9874: and pd.delinquency_id = del.delinquency_id
9875: and del.customer_site_use_id = temp.object_id

Line 9892: iex_delinquencies_all del,

9888: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
9889: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.promise_amount)) promise_amount
9890: -- End Bug 14197188 bibeura 26-Jun-2012
9891: FROM iex_promise_details pd,
9892: iex_delinquencies_all del,
9893: iex_pop_uwq_summ_gt temp,
9894: ar_system_parameters_all sys ,
9895: gl_sets_of_books gl
9896: WHERE pd.cust_account_id = del.cust_account_id

Line 9927: FROM iex_delinquencies_all a,

9923:
9924: cursor c_billto_past_due is
9925: SELECT a.customer_site_use_id,
9926: SUM(b.acctd_amount_due_remaining) past_due_inv_value
9927: FROM iex_delinquencies_all a,
9928: ar_payment_schedules_all b,
9929: iex_pop_uwq_summ_gt temp
9930: WHERE a.customer_site_use_id = temp.object_id
9931: AND a.payment_schedule_id = b.payment_schedule_id

Line 11185: from iex_delinquencies_all del,

11181: count(1) number_of_delinquencies,
11182: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
11183: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
11184: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
11185: from iex_delinquencies_all del,
11186: iex_pop_uwq_summ_gt temp
11187: WHERE del.cust_account_id = temp.object_id AND
11188: del.org_id = temp.org_id and
11189: del.status IN('DELINQUENT', 'PREDELINQUENT')

Line 11198: IEX_DELINQUENCIES_ALL DEL,

11194: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
11195: 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,
11196: 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
11197: from iex_promise_details pd,
11198: IEX_DELINQUENCIES_ALL DEL,
11199: iex_pop_uwq_summ_gt temp
11200: WHERE pd.cust_account_id = del.cust_account_id
11201: and pd.delinquency_id = del.delinquency_id
11202: and del.cust_account_id = temp.object_id

Line 11219: iex_delinquencies_all del,

11215: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
11216: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.promise_amount)) promise_amount
11217: -- End Bug 14197188 bibeura 26-Jun-2012
11218: FROM iex_promise_details pd,
11219: iex_delinquencies_all del,
11220: iex_pop_uwq_summ_gt temp,
11221: ar_system_parameters_all sys ,
11222: gl_sets_of_books gl
11223: WHERE pd.cust_account_id = del.cust_account_id

Line 11253: FROM iex_delinquencies_all a,

11249: -- end for the bug#7562130
11250: cursor c_account_past_due is
11251: SELECT a.cust_account_id,
11252: SUM(b.acctd_amount_due_remaining) past_due_inv_value
11253: FROM iex_delinquencies_all a,
11254: ar_payment_schedules_all b,
11255: iex_pop_uwq_summ_gt temp
11256: WHERE a.cust_account_id = temp.object_id
11257: AND a.payment_schedule_id = b.payment_schedule_id

Line 12495: from iex_delinquencies_all del,

12491: count(1) number_of_delinquencies,
12492: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
12493: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
12494: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
12495: from iex_delinquencies_all del,
12496: iex_pop_uwq_summ_gt temp
12497: WHERE del.party_cust_id = temp.object_id AND
12498: del.org_id = temp.org_id and
12499: del.status IN('DELINQUENT', 'PREDELINQUENT')

Line 12508: IEX_DELINQUENCIES_ALL DEL,

12504: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
12505: 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,
12506: 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
12507: from iex_promise_details pd,
12508: IEX_DELINQUENCIES_ALL DEL,
12509: iex_pop_uwq_summ_gt temp
12510: WHERE pd.cust_account_id = del.cust_account_id
12511: AND pd.delinquency_id = del.delinquency_id
12512: and del.party_cust_id = temp.object_id

Line 12529: iex_delinquencies_all del,

12525: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
12526: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.promise_amount)) promise_amount
12527: -- End Bug 14197188 bibeura 26-Jun-2012
12528: FROM iex_promise_details pd,
12529: iex_delinquencies_all del,
12530: iex_pop_uwq_summ_gt temp,
12531: ar_system_parameters_all sys ,
12532: gl_sets_of_books gl
12533: WHERE pd.cust_account_id = del.cust_account_id

Line 12562: FROM iex_delinquencies_all a,

12558: -- end for the bug#7562130
12559: cursor c_customer_past_due is
12560: SELECT a.party_cust_id,
12561: SUM(b.acctd_amount_due_remaining) past_due_inv_value
12562: FROM iex_delinquencies_all a,
12563: ar_payment_schedules_all b,
12564: iex_pop_uwq_summ_gt temp
12565: WHERE a.party_cust_id = temp.object_id
12566: AND a.payment_schedule_id = b.payment_schedule_id