[Home] [Help]
496: if (g_party_lvl_enb='N' and l_allowed_lvl=g_system_level) or g_party_lvl_enb='Y' then
497: IF l_allowed_lvl = 'CUSTOMER' THEN
498: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at Customer level--in OU');
499: delete from IEX_DLN_UWQ_SUMMARY where (party_id,org_id) in
500: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza
501: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date)
502: and trb.org_id=nvl(l_curr_org_id,trb.org_id))
503: and cust_account_id is null and site_use_id is null;--Added for Bug 8707923 27-Jul-2009 barathsr
504: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at Cust level-->'||sql%rowcount);
551: FND_FILE.PUT_LINE(FND_FILE.LOG,'looping for other levels in party-->'||r_allowed_LEVELS.lookup_code);
552: IF l_allowed_lvl = 'CUSTOMER' THEN
553: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at Customer level--in party ');
554: delete from IEX_DLN_UWQ_SUMMARY where (party_id,org_id) in
555: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza
556: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date))
557: -- and trb.org_id=nvl(l_curr_org_id,trb.org_id)
558: and cust_account_id is null and site_use_id is null;--Added for Bug 8707923 27-Jul-2009 barathsr
559: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at Cust level-->'||sql%rowcount);
600: if G_SYSTEM_LEVEL is not null then
601: IF g_system_level = 'CUSTOMER' THEN
602: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at Customer level--in sys lvl ');
603: delete from IEX_DLN_UWQ_SUMMARY where (party_id,org_id) in
604: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza
605: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date))
606: -- and trb.org_id=nvl(l_curr_org_id,trb.org_id)
607: and cust_account_id is null and site_use_id is null;--Added for Bug 8707923 27-Jul-2009 barathsr
608: ELSIF g_system_level = 'ACCOUNT' THEN
1141: )
1142: ) pending_promises
1143:
1144: FROM ar_trx_bal_summary trx_summ,
1145: hz_cust_accounts acc,
1146: hz_parties party,
1147: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
1148: jtf_objects_b objb,
1149: hz_contact_points phone,
1291: FROM ar_trx_bal_summary trx_summ,
1292: GL_SETS_OF_BOOKS gl,
1293: AR_SYSTEM_PARAMETERS_all sys,
1294: jtf_objects_b objb,
1295: hz_cust_accounts acc,
1296: hz_party_preferences party_pref
1297: where
1298: P_MODE = 'CP'
1299: AND trx_summ.reference_1 = '1'
1401: fnd_terr.territory_short_name country,
1402: loc.province province,
1403: loc.postal_code postal_code
1404: FROM iex_dln_uwq_summary summ,
1405: hz_cust_accounts acc,
1406: hz_parties party,
1407: -- hz_party_preferences party_pref, -- Commented for bug13452739
1408: fnd_territories_tl fnd_terr,
1409: hz_cust_site_uses_all site_uses,
1759: AND(TRUNC(pro.uwq_active_date) > TRUNC(sysdate))))
1760: )
1761: ) pending_promises
1762: FROM ar_trx_bal_summary trx_summ,
1763: hz_cust_accounts acc,
1764: hz_parties party,
1765: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
1766: jtf_objects_b objb,
1767: hz_contact_points phone,
1915: FROM ar_trx_bal_summary trx_summ,
1916: GL_SETS_OF_BOOKS gl,
1917: AR_SYSTEM_PARAMETERS_all sys,
1918: jtf_objects_b objb,
1919: hz_cust_accounts acc,
1920: hz_party_preferences party_pref
1921: where
1922: P_MODE = 'CP'
1923: AND trx_summ.reference_1 = '1'
2038: fnd_terr.territory_short_name country,
2039: loc.province province,
2040: loc.postal_code postal_code
2041: FROM iex_dln_uwq_summary summ,
2042: hz_cust_accounts acc,
2043: hz_parties party,
2044: hz_party_preferences party_pref,
2045: fnd_territories_tl fnd_terr,
2046: hz_cust_site_uses_all site_uses,
2413: )
2414: ) pending_promises
2415:
2416: FROM ar_trx_bal_summary trx_summ,
2417: hz_cust_accounts acc,
2418: hz_parties party,
2419: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
2420: jtf_objects_b objb,
2421: hz_contact_points phone,
2540: MAX(trx_summ.creation_date) creation_date,
2541: MAX(trx_summ.created_by) created_by,
2542: MAX(trx_summ.last_update_login) last_update_login
2543: FROM ar_trx_bal_summary trx_summ,
2544: hz_cust_accounts acc,
2545: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
2546: jtf_objects_b objb,
2547: GL_SETS_OF_BOOKS gl,
2548: AR_SYSTEM_PARAMETERS_all sys
2653: fnd_terr.territory_short_name country,
2654: party.province province,
2655: party.postal_code postal_code
2656: FROM iex_dln_uwq_summary summ,
2657: hz_cust_accounts acc,
2658: hz_parties party,
2659: hz_party_preferences party_pref,
2660: fnd_territories_tl fnd_terr
2661: WHERE
2995: )
2996: ) pending_promises
2997:
2998: FROM ar_trx_bal_summary trx_summ,
2999: hz_cust_accounts acc,
3000: hz_parties party,
3001: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
3002: jtf_objects_b objb,
3003: hz_contact_points phone,
3132: MAX(trx_summ.creation_date) creation_date,
3133: MAX(trx_summ.created_by) created_by,
3134: MAX(trx_summ.last_update_login) last_update_login
3135: FROM ar_trx_bal_summary trx_summ,
3136: hz_cust_accounts acc,
3137: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
3138: jtf_objects_b objb,
3139: GL_SETS_OF_BOOKS gl,
3140: AR_SYSTEM_PARAMETERS_all sys
3251: fnd_terr.territory_short_name country,
3252: party.province province,
3253: party.postal_code postal_code
3254: FROM iex_dln_uwq_summary summ,
3255: hz_cust_accounts acc,
3256: hz_parties party,
3257: hz_party_preferences party_pref,
3258: fnd_territories_tl fnd_terr
3259: WHERE
3572: FROM dual
3573: WHERE EXISTS
3574: (SELECT 1
3575: FROM iex_promise_details pd,
3576: hz_cust_accounts b
3577: WHERE b.party_id = party.party_id
3578: AND pd.cust_account_id = b.cust_account_id
3579: AND pd.state = 'BROKEN_PROMISE'
3580: AND(pd.uwq_status IS NULL OR pd.uwq_status = 'ACTIVE' OR(TRUNC(pd.uwq_active_date) <= TRUNC(sysdate)
3585: FROM dual
3586: WHERE EXISTS
3587: (SELECT 1
3588: FROM iex_promise_details pd,
3589: hz_cust_accounts b
3590: WHERE b.party_id = party.party_id
3591: AND pd.cust_account_id = b.cust_account_id
3592: AND pd.state = 'BROKEN_PROMISE'
3593: AND(pd.uwq_status = 'COMPLETE'
3598: FROM dual
3599: WHERE EXISTS
3600: (SELECT 1
3601: FROM iex_promise_details pd,
3602: hz_cust_accounts b
3603: WHERE b.party_id = party.party_id
3604: AND pd.cust_account_id = b.cust_account_id
3605: and pd.state = 'BROKEN_PROMISE'
3606: AND(pd.uwq_status = 'PENDING'
3608: )
3609: pending_promises
3610:
3611: FROM ar_trx_bal_summary trx_summ,
3612: hz_cust_accounts acc,
3613: hz_parties party,
3614: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
3615: jtf_objects_b objb,
3616: hz_contact_points phone,
3738: MAX(trx_summ.creation_date) creation_date,
3739: MAX(trx_summ.created_by) created_by,
3740: MAX(trx_summ.last_update_login) last_update_login
3741: FROM ar_trx_bal_summary trx_summ,
3742: hz_cust_accounts acc,
3743: hz_parties party,
3744: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
3745: jtf_objects_b objb,
3746: GL_SETS_OF_BOOKS gl,
3856: fnd_terr.territory_short_name country,
3857: party.province province,
3858: party.postal_code postal_code
3859: FROM iex_dln_uwq_summary summ,
3860: -- hz_cust_accounts acc,
3861: hz_parties party,
3862: hz_party_preferences party_pref,
3863: fnd_territories_tl fnd_terr
3864: WHERE
3940: summ.last_payment_amount last_payment_amount,
3941: summ.currency last_payment_currency,
3942: summ.last_payment_number last_payment_number
3943: from ar_trx_bal_summary summ,
3944: hz_cust_accounts hca,
3945: gl_sets_of_books gl,
3946: ar_system_parameters_all sys
3947: where summ.cust_account_id=hca.cust_account_id
3948: and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
4150: FROM dual
4151: WHERE EXISTS
4152: (SELECT 1
4153: FROM iex_promise_details pd,
4154: hz_cust_accounts b
4155: WHERE b.party_id = party.party_id
4156: AND pd.cust_account_id = b.cust_account_id
4157: AND pd.state = 'BROKEN_PROMISE'
4158: AND(pd.uwq_status IS NULL OR pd.uwq_status = 'ACTIVE' OR(TRUNC(pd.uwq_active_date) <= TRUNC(sysdate)
4163: FROM dual
4164: WHERE EXISTS
4165: (SELECT 1
4166: FROM iex_promise_details pd,
4167: hz_cust_accounts b
4168: WHERE b.party_id = party.party_id
4169: AND pd.cust_account_id = b.cust_account_id
4170: AND pd.state = 'BROKEN_PROMISE'
4171: AND(pd.uwq_status = 'COMPLETE'
4176: FROM dual
4177: WHERE EXISTS
4178: (SELECT 1
4179: FROM iex_promise_details pd,
4180: hz_cust_accounts b
4181: WHERE b.party_id = party.party_id
4182: AND pd.cust_account_id = b.cust_account_id
4183: and pd.state = 'BROKEN_PROMISE'
4184: AND(pd.uwq_status = 'PENDING'
4186: )
4187: pending_promises
4188:
4189: FROM ar_trx_bal_summary trx_summ,
4190: hz_cust_accounts acc,
4191: hz_parties party,
4192: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
4193: jtf_objects_b objb,
4194: hz_contact_points phone,
4320: MAX(trx_summ.creation_date) creation_date,
4321: MAX(trx_summ.created_by) created_by,
4322: MAX(trx_summ.last_update_login) last_update_login
4323: FROM ar_trx_bal_summary trx_summ,
4324: hz_cust_accounts acc,
4325: hz_parties party,
4326: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
4327: jtf_objects_b objb,
4328: GL_SETS_OF_BOOKS gl,
4449: fnd_terr.territory_short_name country,
4450: party.province province,
4451: party.postal_code postal_code
4452: FROM iex_dln_uwq_summary summ,
4453: -- hz_cust_accounts acc,
4454: hz_parties party,
4455: hz_party_preferences party_pref,
4456: fnd_territories_tl fnd_terr
4457: WHERE
4562: summ.last_payment_amount last_payment_amount,
4563: summ.currency last_payment_currency,
4564: summ.last_payment_number last_payment_number
4565: from ar_trx_bal_summary summ,
4566: hz_cust_accounts hca,
4567: gl_sets_of_books gl,
4568: ar_system_parameters_all sys
4569: where summ.cust_account_id=hca.cust_account_id
4570: and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
5007: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Partially repopulating IEX_DLN_UWQ_SUMMARY table...');
5008: /* Begin gnramasa Modified for bug 5677415 27/11/2006 */
5009: /* IF p_level = 'CUSTOMER' THEN
5010: delete from IEX_DLN_UWQ_SUMMARY where (party_id,org_id) in
5011: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza
5012: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date)
5013: and trb.org_id=nvl(p_org_id,trb.org_id));--Added for Bug 8707923 27-Jul-2009 barathsr
5014: ELSIF p_level = 'ACCOUNT' THEN
5015: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, org_id) in
9619: loc.province province,
9620: loc.postal_code postal_code
9621: -- end for the bug#8538945
9622: FROM ar_trx_bal_summary trx_summ,
9623: hz_cust_accounts acc,
9624: hz_parties party,
9625: jtf_objects_b objb,
9626: fnd_territories_tl fnd_terr,
9627: hz_cust_site_uses_all site_uses,
9947:
9948: cursor c_bankruptcies is
9949: select sua.site_use_id,
9950: COUNT(1) number_of_bankruptcies
9951: FROM iex_bankruptcies bkr,hz_cust_accounts ca,
9952: hz_cust_acct_sites_all cas,--Modified for Bug 9487600 23-Mar-2010 barathsr
9953: hz_cust_site_uses_all sua
9954: where sua.site_use_id in (select object_id from iex_pop_uwq_summ_gt)
9955: and bkr.party_id=ca.party_id
10069: end if;
10070:
10071: delete from iex_dln_uwq_summary summ
10072: where exists(select 1
10073: from iex_pop_uwq_summ_gt gt,hz_cust_site_uses_all hcsua,hz_cust_acct_sites_all hcasa,hz_cust_accounts hca
10074: where gt.object_id=hcsua.site_use_id
10075: and hcsua.cust_acct_site_id=hcasa.cust_acct_site_id
10076: and hcasa.cust_account_id=hca.cust_account_id
10077: and hca.party_id=summ.party_id
10967: fnd_terr.territory_short_name country,
10968: party.province province,
10969: party.postal_code postal_code
10970: FROM ar_trx_bal_summary trx_summ,
10971: hz_cust_accounts acc,
10972: hz_parties party,
10973: jtf_objects_b objb,
10974: fnd_territories_tl fnd_terr,
10975: GL_SETS_OF_BOOKS gl,
11273:
11274: cursor c_bankruptcies is
11275: select ca.cust_account_id,
11276: COUNT(1) number_of_bankruptcies
11277: FROM iex_bankruptcies bkr,hz_cust_accounts ca
11278: where ca.cust_account_id in (select object_id from iex_pop_uwq_summ_gt)
11279: and bkr.party_id=ca.party_id
11280: and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION') -- Changed for bug#7693986
11281: group by ca.cust_account_id;
11391: end if;
11392:
11393: delete from iex_dln_uwq_summary summ
11394: where exists(select 1
11395: from iex_pop_uwq_summ_gt gt,hz_cust_accounts hca
11396: where gt.object_id=hca.cust_account_id
11397: and hca.party_id=summ.party_id
11398: and summ.site_use_id is null
11399: and gt.org_id=summ.org_id)
12284: fnd_terr.territory_short_name country,
12285: party.province province,
12286: party.postal_code postal_code
12287: FROM ar_trx_bal_summary trx_summ,
12288: hz_cust_accounts acc,
12289: hz_parties party,
12290: jtf_objects_b objb,
12291: fnd_territories_tl fnd_terr,
12292: GL_SETS_OF_BOOKS gl,
12574: o_summ.last_payment_number last_payment_number,
12575: o_summ.last_payment_amount -- Bug 14226230 bibeura
12576: -- iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
12577: FROM ar_trx_bal_summary o_summ,
12578: hz_cust_accounts o_acc
12579: WHERE o_summ.cust_account_id = o_acc.cust_account_id
12580: and o_acc.party_id in (select object_id from iex_pop_uwq_summ_gt)
12581: AND o_summ.last_payment_date = (SELECT MAX(summ.last_payment_date)
12582: FROM ar_trx_bal_summary summ,
12579: WHERE o_summ.cust_account_id = o_acc.cust_account_id
12580: and o_acc.party_id in (select object_id from iex_pop_uwq_summ_gt)
12581: AND o_summ.last_payment_date = (SELECT MAX(summ.last_payment_date)
12582: FROM ar_trx_bal_summary summ,
12583: hz_cust_accounts acc
12584: WHERE acc.cust_account_id = summ.cust_account_id
12585: and acc.party_id=o_acc.party_id);
12586:
12587: cursor c_bankruptcies is
14187: if p_fmode='CUSTOMER' then
14188: FORALL cnt IN l_party_id.first..l_party_id.last
14189: update iex_dln_uwq_summary dln_summ
14190: set net_balance=(select SUM(NVL(aps.acctd_amount_due_remaining,0))
14191: from ar_payment_schedules_all aps,hz_cust_accounts hca -- bug 12771316 PNAVEENK
14192: where aps.customer_id=hca.cust_account_id
14193: and aps.org_id=l_org_id(cnt)
14194: and aps.status='OP'
14195: and hca.party_id=l_party_id(cnt))
14206: elsif p_fmode='ACCOUNT' then
14207: FORALL cnt IN l_cust_acct_id.first..l_cust_acct_id.last
14208: update iex_dln_uwq_summary dln_summ
14209: set net_balance=(select SUM(NVL(aps.acctd_amount_due_remaining,0))
14210: from ar_payment_schedules_all aps,hz_cust_accounts hca--,ar_system_parameters asp
14211: where aps.customer_id=hca.cust_account_id
14212: and aps.org_id=l_org_id(cnt)
14213: and aps.status='OP'
14214: and hca.party_id=l_party_id(cnt)
14226: elsif p_fmode='BILL_TO' then
14227: FORALL cnt IN l_party_id.first..l_party_id.last
14228: update iex_dln_uwq_summary dln_summ
14229: set net_balance=(select SUM(NVL(aps.acctd_amount_due_remaining,0))
14230: from ar_payment_schedules_all aps,hz_cust_accounts hca--,ar_system_parameters asp
14231: where aps.customer_id=hca.cust_account_id
14232: and aps.org_id=l_org_id(cnt)
14233: and aps.status='OP'
14234: and hca.party_id=l_party_id(cnt)