[Home] [Help]
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'
986: AND a.status IN('DELINQUENT', 'PREDELINQUENT')
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
1327: AND b.status = 'OP'
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'
1606: AND a.status IN('DELINQUENT', 'PREDELINQUENT')
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
1954: AND b.status = 'OP'
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'
2269: AND a.status IN('DELINQUENT', 'PREDELINQUENT')
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
2581: AND b.status = 'OP'
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'
2852: AND a.status IN('DELINQUENT', 'PREDELINQUENT')
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
3173: AND b.status = 'OP'
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'
3480: AND a.status IN('DELINQUENT', 'PREDELINQUENT')
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
3781: AND b.status = 'OP'
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'
4059: AND a.status IN('DELINQUENT', 'PREDELINQUENT')
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
4364: AND b.status = 'OP'
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
9932: AND b.status = 'OP'
9967: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
9968: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies,
9969: del.org_id org_id
9970: from iex_delinquencies del,
9971: ar_payment_schedules ps
9972: WHERE del.payment_schedule_id = ps.payment_schedule_id AND
9973: del.org_id = ps.org_id and
9974: exists(select 1 from iex_delinquencies del1
9975: where del1.last_update_date>=trunc(sysdate-1) --changed for bug 13322580
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
11258: AND b.status = 'OP'
11289: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
11290: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies,
11291: del.org_id org_id
11292: from iex_delinquencies del,
11293: ar_payment_schedules ps
11294: WHERE del.payment_schedule_id = ps.payment_schedule_id AND
11295: del.org_id = ps.org_id and
11296: exists(select 1 from iex_delinquencies del1
11297: where del1.last_update_date>=trunc(sysdate-1) --changed for bug 13322580
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
12567: AND b.status = 'OP'
12601: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
12602: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies,
12603: del.org_id org_id
12604: from iex_delinquencies del,
12605: ar_payment_schedules ps
12606: WHERE del.payment_schedule_id = ps.payment_schedule_id AND
12607: del.org_id = ps.org_id and
12608: exists(select 1 from iex_delinquencies del1
12609: where del1.last_update_date>=trunc(sysdate-1) --changed for bug 13322580
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)