[Home] [Help]
9646: AND loc.country = fnd_terr.territory_code(+) -- Changed for the bug#8538945
9647: AND fnd_terr.LANGUAGE(+) = userenv('LANG')
9648: and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
9649: and trx_summ.org_id = sys.org_id
9650: and trx_summ.site_use_id in (select temp.object_id from iex_pop_uwq_summ_gt temp where
9651: temp.org_id=trx_summ.org_id)
9652: GROUP BY trx_summ.org_id,
9653: objb.object_function,
9654: objb.object_parameters,
9697: wkitem.escalated_yn --Added for bug#6981126 by schekuri on 27-Jul-2008
9698: from iex_strategies strat,
9699: iex_strategy_work_items wkitem,
9700: iex_stry_temp_work_items_b stry_temp_wkitem,
9701: iex_pop_uwq_summ_gt temp
9702: where strat.jtf_object_type = temp.object_type
9703: AND strat.status_code IN('OPEN', 'ONHOLD')
9704: AND wkitem.strategy_id = strat.strategy_id
9705: AND wkitem.status_code IN('OPEN', 'ONHOLD')
9718: hp.site_use_id
9719: FROM
9720: hz_customer_profiles hp,
9721: ar_collectors ac,
9722: iex_pop_uwq_summ_gt temp,
9723: jtf_rs_resource_extns rs,
9724: JTF_RS_GROUPS_VL rg
9725: WHERE
9726: hp.site_use_id=temp.object_id
9737: phone.phone_extension phone_extension
9738: FROM
9739: hz_contact_points phone,
9740: iex_dln_uwq_summary ids,
9741: iex_pop_uwq_summ_gt temp
9742: WHERE
9743: phone.owner_table_id = ids.party_id
9744: AND phone.owner_table_name = 'HZ_PARTIES'
9745: AND phone.contact_point_type = 'PHONE'
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')
9863: group by del.CUSTOMER_SITE_USE_ID;
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
9876: and del.org_id = temp.org_id
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
9897: AND pd.delinquency_id = del.delinquency_id
9908: -- Start for the bug#7562130 by PNAVEENK
9909: cursor c_billto_score is
9910: SELECT sh.score_object_id, sh.score_value score , sh.score_id, sc.score_name
9911: FROM iex_score_histories sh,
9912: iex_pop_uwq_summ_gt temp,
9913: iex_scores sc
9914: WHERE sh.creation_date = (SELECT MAX(creation_date)
9915: FROM iex_score_histories sh1
9916: WHERE sh1.score_object_code = 'IEX_BILLTO'
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
9932: AND b.status = 'OP'
9933: AND a.status IN('DELINQUENT', 'PREDELINQUENT')
9939: o_summ.last_payment_number last_payment_number,
9940: o_summ.last_payment_amount -- Bug 14226230 bibeura
9941: -- iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
9942: FROM ar_trx_bal_summary o_summ
9943: WHERE o_summ.site_use_id in (select object_id from iex_pop_uwq_summ_gt)
9944: AND o_summ.last_payment_date = (SELECT MAX(last_payment_date)
9945: FROM ar_trx_bal_summary
9946: WHERE site_use_id = o_summ.site_use_id);
9947:
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
9956: and ca.cust_account_id=cas.cust_account_id
9957: and cas.cust_acct_site_id=sua.cust_acct_site_id
9958: and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION') -- Changed for bug#7693986
10043: --Begin Bug 8707923 27-Jul-2009 barathsr
10044:
10045: FND_FILE.PUT_LINE(FND_FILE.LOG, 'delete frm GT table at billto level');
10046:
10047: delete from iex_pop_uwq_summ_gt;
10048:
10049: FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into bill_to gt');
10050: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
10051: select del.customer_site_use_id,'IEX_BILLTO',del.org_id from iex_delinquencies del,hz_party_preferences party_pref
10046:
10047: delete from iex_pop_uwq_summ_gt;
10048:
10049: FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into bill_to gt');
10050: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
10051: select del.customer_site_use_id,'IEX_BILLTO',del.org_id from iex_delinquencies del,hz_party_preferences party_pref
10052: where del.status in ('DELINQUENT','PRE-DELINQUENT')
10053: and del.party_cust_id=party_pref.party_id(+)
10054: and party_pref.module(+)='COLLECTIONS'
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
10983: AND party.country = fnd_terr.territory_code(+)
10984: AND fnd_terr.LANGUAGE(+) = userenv('LANG')
10985: and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
10986: and trx_summ.org_id = sys.org_id
10987: and trx_summ.cust_account_id in (select temp.object_id from iex_pop_uwq_summ_gt temp where
10988: temp.org_id=trx_summ.org_id)
10989: GROUP BY trx_summ.org_id,
10990: objb.object_function,
10991: objb.object_parameters,
11023: wkitem.escalated_yn --Added for bug#6981126 by schekuri on 27-Jul-2008
11024: from iex_strategies strat,
11025: iex_strategy_work_items wkitem,
11026: iex_stry_temp_work_items_b stry_temp_wkitem,
11027: iex_pop_uwq_summ_gt temp
11028: where strat.jtf_object_type = temp.object_type
11029: AND strat.status_code IN('OPEN', 'ONHOLD')
11030: AND wkitem.strategy_id = strat.strategy_id
11031: AND wkitem.status_code IN('OPEN', 'ONHOLD')
11043: hp.cust_account_id
11044: FROM
11045: hz_customer_profiles hp,
11046: ar_collectors ac,
11047: iex_pop_uwq_summ_gt temp,
11048: jtf_rs_resource_extns rs,
11049: JTF_RS_GROUPS_VL rg
11050: WHERE
11051: hp.site_use_id is null
11064: phone.phone_extension phone_extension
11065: FROM
11066: hz_contact_points phone,
11067: iex_dln_uwq_summary ids,
11068: iex_pop_uwq_summ_gt temp
11069: WHERE
11070: phone.owner_table_id = ids.party_id
11071: AND phone.owner_table_name = 'HZ_PARTIES'
11072: AND phone.contact_point_type = 'PHONE'
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')
11190: group by del.cust_account_id;
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
11203: and del.org_id = temp.org_id
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
11224: AND pd.delinquency_id = del.delinquency_id
11235: -- Start for the bug#7562130 by PNAVEENK
11236: cursor c_account_score is
11237: SELECT sh.score_object_id, sh.score_value score,sh.score_id, sc.score_name
11238: FROM iex_score_histories sh,
11239: iex_pop_uwq_summ_gt temp,
11240: iex_scores sc
11241: WHERE sh.creation_date = (SELECT MAX(creation_date)
11242: FROM iex_score_histories sh1
11243: WHERE sh1.score_object_code = 'IEX_ACCOUNT'
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
11258: AND b.status = 'OP'
11259: AND a.status IN('DELINQUENT', 'PREDELINQUENT')
11265: o_summ.last_payment_number last_payment_number,
11266: o_summ.last_payment_amount -- Bug 14226230 bibeura
11267: --iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
11268: FROM ar_trx_bal_summary o_summ
11269: WHERE o_summ.cust_account_id in (select object_id from iex_pop_uwq_summ_gt)
11270: AND o_summ.last_payment_date = (SELECT MAX(last_payment_date)
11271: FROM ar_trx_bal_summary
11272: WHERE cust_account_id = o_summ.cust_account_id);
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;
11282:
11365:
11366: --Begin Bug 8707923 27-Jul-2009 barathsr
11367: FND_FILE.PUT_LINE(FND_FILE.LOG,'deleting rows from A/C gt table');
11368:
11369: delete from iex_pop_uwq_summ_gt;
11370: -- End Bug #6251657 bibeura 25-OCT-2007
11371: FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into account gt');
11372: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
11373: select del.cust_account_id,'IEX_ACCOUNT',del.org_id from iex_delinquencies del,hz_party_preferences party_pref
11368:
11369: delete from iex_pop_uwq_summ_gt;
11370: -- End Bug #6251657 bibeura 25-OCT-2007
11371: FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into account gt');
11372: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
11373: select del.cust_account_id,'IEX_ACCOUNT',del.org_id from iex_delinquencies del,hz_party_preferences party_pref
11374: where del.status in ('DELINQUENT','PRE-DELINQUENT')
11375: and del.party_cust_id=party_pref.party_id(+)
11376: and party_pref.module(+)='COLLECTIONS'
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)
12300: AND fnd_terr.LANGUAGE(+) = userenv('LANG')
12301: and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
12302: and trx_summ.org_id = sys.org_id
12303: and acc.party_id in
12304: (select temp.object_id from iex_pop_uwq_summ_gt temp where temp.org_id=trx_summ.org_id)
12305: GROUP BY trx_summ.org_id,
12306: objb.object_function,
12307: objb.object_parameters,
12308: party.party_id,
12336: wkitem.escalated_yn --Added for bug#6981126 by schekuri on 27-Jul-2008
12337: from iex_strategies strat,
12338: iex_strategy_work_items wkitem,
12339: iex_stry_temp_work_items_b stry_temp_wkitem,
12340: iex_pop_uwq_summ_gt temp
12341: where strat.status_code IN('OPEN', 'ONHOLD')
12342: AND wkitem.strategy_id = strat.strategy_id
12343: AND wkitem.status_code IN('OPEN', 'ONHOLD')
12344: AND wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id
12354: hp.party_id
12355: FROM
12356: hz_customer_profiles hp,
12357: ar_collectors ac,
12358: iex_pop_uwq_summ_gt temp,
12359: jtf_rs_resource_extns rs,
12360: JTF_RS_GROUPS_VL rg
12361: WHERE
12362: hp.cust_account_id = -1
12375: phone.phone_number phone_number,
12376: phone.phone_extension phone_extension
12377: FROM
12378: hz_contact_points phone,
12379: iex_pop_uwq_summ_gt temp
12380: WHERE
12381: phone.owner_table_id = temp.object_id
12382: AND phone.owner_table_name = 'HZ_PARTIES'
12383: AND phone.contact_point_type = 'PHONE'
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')
12500: group by del.party_cust_id;
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
12513: and del.org_id = temp.org_id
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
12534: AND pd.delinquency_id = del.delinquency_id
12545: -- Start for the bug#7562130 by PNAVEENK
12546: cursor c_customer_score is
12547: SELECT sh.score_object_id, sh.score_value score, sh.score_id, sc.score_name
12548: FROM iex_score_histories sh,
12549: iex_pop_uwq_summ_gt temp,
12550: iex_scores sc
12551: WHERE sh.creation_date = (SELECT MAX(creation_date)
12552: FROM iex_score_histories sh1
12553: WHERE sh1.score_object_code = 'PARTY'
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
12567: AND b.status = 'OP'
12568: AND a.status IN('DELINQUENT', 'PREDELINQUENT')
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,
12583: hz_cust_accounts acc
12584: WHERE acc.cust_account_id = summ.cust_account_id
12587: cursor c_bankruptcies is
12588: select bkr.party_id,
12589: COUNT(1) number_of_bankruptcies
12590: FROM iex_bankruptcies bkr
12591: where bkr.party_id in (select object_id from iex_pop_uwq_summ_gt)
12592: and NVL(BKR.DISPOSITION_CODE,'GRANTED') in ('GRANTED','NEGOTIATION') -- Changed for bug#7693986
12593: group by bkr.party_id;
12594:
12595: -- Bug #6251657 bibeura 25-OCT-2007
12674:
12675: --Begin Bug 8707923 27-Jul-2009 barathsr
12676:
12677: FND_FILE.PUT_LINE(FND_FILE.LOG,'delete rows from customer gt');
12678: delete from iex_pop_uwq_summ_gt;
12679: FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into customer gt');
12680:
12681: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
12682: select del.party_cust_id,'PARTY',del.org_id from iex_delinquencies del,hz_party_preferences party_pref
12677: FND_FILE.PUT_LINE(FND_FILE.LOG,'delete rows from customer gt');
12678: delete from iex_pop_uwq_summ_gt;
12679: FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into customer gt');
12680:
12681: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
12682: select del.party_cust_id,'PARTY',del.org_id from iex_delinquencies del,hz_party_preferences party_pref
12683: where del.status in ('DELINQUENT','PRE-DELINQUENT')
12684: and del.party_cust_id=party_pref.party_id(+)
12685: and party_pref.module(+)='COLLECTIONS'
12700: end if;
12701:
12702: delete from iex_dln_uwq_summary summ
12703: where exists(select 1
12704: from iex_pop_uwq_summ_gt gt
12705: where gt.object_id=summ.party_id
12706: and summ.cust_account_id is null
12707: and summ.site_use_id is null
12708: and gt.org_id=summ.org_id)