[Home] [Help]
290: )
291: ) pending_promises
292:
293: FROM ar_trx_bal_summary trx_summ,
294: hz_cust_accounts acc,
295: hz_parties party,
296: jtf_objects_b objb,
297: hz_contact_points phone,
298: fnd_territories_tl fnd_terr,
552: AND(TRUNC(pro.uwq_active_date) > TRUNC(sysdate))))
553: )
554: ) pending_promises
555: FROM ar_trx_bal_summary trx_summ,
556: hz_cust_accounts acc,
557: hz_parties party,
558: jtf_objects_b objb,
559: hz_contact_points phone,
560: fnd_territories_tl fnd_terr,
817: )
818: ) pending_promises
819:
820: FROM ar_trx_bal_summary trx_summ,
821: hz_cust_accounts acc,
822: hz_parties party,
823: jtf_objects_b objb,
824: hz_contact_points phone,
825: fnd_territories_tl fnd_terr,
1075: )
1076: ) pending_promises
1077:
1078: FROM ar_trx_bal_summary trx_summ,
1079: hz_cust_accounts acc,
1080: hz_parties party,
1081: jtf_objects_b objb,
1082: hz_contact_points phone,
1083: fnd_territories_tl fnd_terr,
1288: FROM dual
1289: WHERE EXISTS
1290: (SELECT 1
1291: FROM iex_promise_details pd,
1292: hz_cust_accounts b
1293: WHERE b.party_id = party.party_id
1294: AND pd.cust_account_id = b.cust_account_id
1295: AND pd.state = 'BROKEN_PROMISE'
1296: AND(pd.uwq_status IS NULL OR pd.uwq_status = 'ACTIVE' OR(TRUNC(pd.uwq_active_date) <= TRUNC(sysdate)
1301: FROM dual
1302: WHERE EXISTS
1303: (SELECT 1
1304: FROM iex_promise_details pd,
1305: hz_cust_accounts b
1306: WHERE b.party_id = party.party_id
1307: AND pd.cust_account_id = b.cust_account_id
1308: AND pd.state = 'BROKEN_PROMISE'
1309: AND(pd.uwq_status = 'COMPLETE'
1314: FROM dual
1315: WHERE EXISTS
1316: (SELECT 1
1317: FROM iex_promise_details pd,
1318: hz_cust_accounts b
1319: WHERE b.party_id = party.party_id
1320: AND pd.cust_account_id = b.cust_account_id
1321: and pd.state = 'BROKEN_PROMISE'
1322: AND(pd.uwq_status = 'PENDING'
1324: )
1325: pending_promises
1326:
1327: FROM ar_trx_bal_summary trx_summ,
1328: hz_cust_accounts acc,
1329: hz_parties party,
1330: jtf_objects_b objb,
1331: hz_contact_points phone,
1332: fnd_territories_tl fnd_terr,
1528: FROM dual
1529: WHERE EXISTS
1530: (SELECT 1
1531: FROM iex_promise_details pd,
1532: hz_cust_accounts b
1533: WHERE b.party_id = party.party_id
1534: AND pd.cust_account_id = b.cust_account_id
1535: AND pd.state = 'BROKEN_PROMISE'
1536: AND(pd.uwq_status IS NULL OR pd.uwq_status = 'ACTIVE' OR(TRUNC(pd.uwq_active_date) <= TRUNC(sysdate)
1541: FROM dual
1542: WHERE EXISTS
1543: (SELECT 1
1544: FROM iex_promise_details pd,
1545: hz_cust_accounts b
1546: WHERE b.party_id = party.party_id
1547: AND pd.cust_account_id = b.cust_account_id
1548: AND pd.state = 'BROKEN_PROMISE'
1549: AND(pd.uwq_status = 'COMPLETE'
1554: FROM dual
1555: WHERE EXISTS
1556: (SELECT 1
1557: FROM iex_promise_details pd,
1558: hz_cust_accounts b
1559: WHERE b.party_id = party.party_id
1560: AND pd.cust_account_id = b.cust_account_id
1561: and pd.state = 'BROKEN_PROMISE'
1562: AND(pd.uwq_status = 'PENDING'
1564: )
1565: pending_promises
1566:
1567: FROM ar_trx_bal_summary trx_summ,
1568: hz_cust_accounts acc,
1569: hz_parties party,
1570: jtf_objects_b objb,
1571: hz_contact_points phone,
1572: fnd_territories_tl fnd_terr,
2108: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Partially repopulating IEX_DLN_UWQ_SUMMARY table...');
2109: /* Begin gnramasa Modified for bug 5677415 27/11/2006 */
2110: IF l_level = 'CUSTOMER' THEN
2111: delete from IEX_DLN_UWQ_SUMMARY where party_id in
2112: (select party_id from ar_trx_bal_summary trb, hz_cust_accounts hza
2113: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date));
2114: ELSIF l_level = 'ACCOUNT' THEN
2115: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, org_id) in
2116: (select cust_account_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date));
3481: fnd_terr.territory_short_name country,
3482: party.province province,
3483: party.postal_code postal_code
3484: FROM ar_trx_bal_summary trx_summ,
3485: hz_cust_accounts acc,
3486: hz_parties party,
3487: jtf_objects_b objb,
3488: fnd_territories_tl fnd_terr,
3489: hz_cust_site_uses_all site_uses,
3761:
3762: cursor c_bankruptcies is
3763: select sua.site_use_id,
3764: COUNT(1) number_of_bankruptcies
3765: FROM iex_bankruptcies bkr,hz_cust_accounts ca,
3766: hz_cust_acct_sites cas,hz_cust_site_uses_all sua
3767: where sua.site_use_id in (select object_id from iex_pop_uwq_summ_gt)
3768: and bkr.party_id=ca.party_id
3769: and ca.cust_account_id=cas.cust_account_id
4712: fnd_terr.territory_short_name country,
4713: party.province province,
4714: party.postal_code postal_code
4715: FROM ar_trx_bal_summary trx_summ,
4716: hz_cust_accounts acc,
4717: hz_parties party,
4718: jtf_objects_b objb,
4719: fnd_territories_tl fnd_terr,
4720: GL_SETS_OF_BOOKS gl,
4989:
4990: cursor c_bankruptcies is
4991: select ca.cust_account_id,
4992: COUNT(1) number_of_bankruptcies
4993: FROM iex_bankruptcies bkr,hz_cust_accounts ca
4994: where ca.cust_account_id in (select object_id from iex_pop_uwq_summ_gt)
4995: and bkr.party_id=ca.party_id
4996: and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION') -- Changed for bug#7693986
4997: group by ca.cust_account_id;
5939: fnd_terr.territory_short_name country,
5940: party.province province,
5941: party.postal_code postal_code
5942: FROM ar_trx_bal_summary trx_summ,
5943: hz_cust_accounts acc,
5944: hz_parties party,
5945: jtf_objects_b objb,
5946: fnd_territories_tl fnd_terr,
5947: GL_SETS_OF_BOOKS gl,
6200: SELECT o_acc.party_id,
6201: o_summ.last_payment_number last_payment_number,
6202: iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
6203: FROM ar_trx_bal_summary o_summ,
6204: hz_cust_accounts o_acc
6205: WHERE o_summ.cust_account_id = o_acc.cust_account_id
6206: and o_acc.party_id in (select object_id from iex_pop_uwq_summ_gt)
6207: AND o_summ.last_payment_date = (SELECT MAX(summ.last_payment_date)
6208: FROM ar_trx_bal_summary summ,
6205: WHERE o_summ.cust_account_id = o_acc.cust_account_id
6206: and o_acc.party_id in (select object_id from iex_pop_uwq_summ_gt)
6207: AND o_summ.last_payment_date = (SELECT MAX(summ.last_payment_date)
6208: FROM ar_trx_bal_summary summ,
6209: hz_cust_accounts acc
6210: WHERE acc.cust_account_id = summ.cust_account_id
6211: and acc.party_id=o_acc.party_id);
6212:
6213: cursor c_bankruptcies is