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_PROMISE_RECON_RPT_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 159


     FND_FILE.PUT_LINE( FND_FILE.LOG,'*   Please select Collector name from the lov.  *');

Line: 175


select NVL(FND_PROFILE.value('IEX_LEASE_ENABLED'), 'N')
into l_ctr_enbl_flg
from dual;

Line: 190


select nvl(fnd_profile.value('IEX_COLLECTIONS_RATE_TYPE'),'N')
into l_coll_rate
from dual;

Line: 200


  select name
  into l_org_id
  from hr_operating_units
  where organization_id=p_org_id;

Line: 216


   select meaning
   into l_pro_status
   from iex_lookups_v
   where lookup_code=p_pro_status
   and lookup_type='IEX_PROMISE_STATUSES';

Line: 231


 select meaning
 into l_pro_state
 from iex_lookups_v
 where lookup_code=p_pro_state
 and lookup_type='IEX_PROMISE_STATES';

Line: 241


       select source_name
       into l_collector
       from jtf_rs_resource_extns
       where resource_id=p_group_by_value_coll;

Line: 247


       select distinct count(resource_id)
       into l_res_cnt
       from
       JTF_RS_RESOURCE_EXTNS
       where source_id in ( select distinct b.person_id
       from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a
       where b.manager_person_id = a.source_id
       and a.resource_id = p_group_by_value_coll);--100000937);

Line: 257


       l_res_qry:= '(select distinct resource_id '||
                    'from '||
                    'JTF_RS_RESOURCE_EXTNS '||
		    'where source_id in (select distinct b.person_id '||
                    'from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a '||
		    'where b.manager_person_id = a.source_id '||
		    'and a.resource_id = '||p_group_by_value_coll;

Line: 281


	    select source_code
	    into l_campaign
	    from ams_source_codes
	    where source_code_id=p_group_by_value_sch;

Line: 291


	   l_where:=l_where||' and (ipd1.campaign_sched_id in (Select source_code_id '||
 	                     'from  ams_source_codes '||
 	                      'where arc_source_code_for =''CAMP'') '||
                              'OR IPD1.campaign_sched_id is NULL)';

Line: 321


 'select '||
 'resource_id,'||
 'resource_name,'||
 'source_code_id,'||
 'source_code,'||
 'account_number,'||
 'invoice,'||
 'installment_number,'||
 'promise_status,'||
 'p_amt,'||
 'p_itemno,'||
 'p_origdt,'||
 'p_exp_pmtdt,'||
 'remaining_balance,'||
 'pmt_dt,'||
 'pmt_amt,'||
 'pmt_type,'||
 'pmt_itemno,'||
 'currency,'||
 'promise_detail_id,'||
 'promise_state '||
 'from (select jrev.resource_id resource_id,'||
 'IEX_UTILITIES.REPLACE_SPECIAL_CHARS(jrev.resource_name) resource_name, amsc.source_code_id source_code_id,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(amsc.source_code) source_code,'||
 'IEX_UTILITIES.REPLACE_SPECIAL_CHARS(hca.account_number) account_number,IEX_UTILITIES.REPLACE_SPECIAL_CHARS(aps.trx_number) invoice, aps.terms_sequence_number installment_number,'||
 'iex_utilities.get_lookup_meaning(''IEX_PROMISE_STATUSES'',ipd1.status) promise_status,'||
 'gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipd1.promise_amount) p_amt,'||
 'ipd1.promise_item_number p_itemno,to_char(ipd1.creation_date,''YYYY-MM-DD'')p_origdt, to_char(ipd1.promise_date,''YYYY-MM-DD'')p_exp_pmtdt,'||
 'gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipd1.amount_due_remaining) remaining_balance,'||
 'decode(nvl(ipax.reversed_flag,''N''),''N'',to_char(ara.apply_date,''YYYY-MM-DD''),null)pmt_dt,'||
 'decode(nvl(ipax.reversed_flag,''N''),''N'',gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipax.amount_applied),null)pmt_amt,'||
 'decode(nvl(ipax.reversed_flag,''N''),''N'',IEX_UTILITIES.REPLACE_SPECIAL_CHARS(acr.payment_method_dsp),null) pmt_type, decode(nvl(ipax.reversed_flag,''N''),''N'',IEX_UTILITIES.REPLACE_SPECIAL_CHARS(acr.receipt_number),null) pmt_itemno,'||
 'ipd1.currency_code currency, ipd1.promise_detail_id promise_detail_id,'||
 'iex_utilities.get_lookup_meaning(''IEX_PROMISE_STATES'',ipd1.state) promise_state '||
 'from '||
 'iex_promise_details ipd1, ams_source_codes amsc, ar_cash_receipts_v acr, iex_prd_appl_xref ipax,'||
 'ar_receivable_applications ara, hz_cust_accounts hca, jtf_rs_resource_extns_vl jrev, iex_delinquencies id, ar_payment_schedules aps,ar_system_parameters asp '||
 ' where '||
 'ipd1.promise_detail_id=ipax.promise_detail_id(+) and ipax.receivable_application_id=ara.receivable_application_id(+) '||
 'and ara.cash_receipt_id=acr.cash_receipt_id(+) '||
 'and aps.payment_schedule_id(+)=id.payment_schedule_id and jrev.resource_id(+)=ipd1.resource_id and ipd1.org_id=asp.org_id '||
 'and amsc.source_code_id(+)=ipd1.campaign_sched_id '||
 'and ipd1.cust_account_id=hca.cust_account_id '||
 'and id.delinquency_id(+)=ipd1.delinquency_id ';

Line: 375


    l_query:='select '||
 'source_code_id,'||
 'source_code,'||
 'resource_id,'||
 'resource_name,'||
 'ptp_count,'||
 'ptp_amt,'||
 'pmt_count,'||
 'pmt_amt,'||
 'broken_count,'||
 'broken_amt,'||
 'open_count,'||
 'open_amt '||
 'from '||
 '(select amsc.source_code_id source_code_id,'||
 'IEX_UTILITIES.REPLACE_SPECIAL_CHARS(amsc.source_code) source_code,'||
 'jrev.resource_id resource_id, IEX_UTILITIES.REPLACE_SPECIAL_CHARS(jrev.resource_name) resource_name,'||
 'count(ipd1.promise_detail_id) ptp_count,'||
 'sum(gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipd1.promise_amount))ptp_amt,'||
 'iex_promise_recon_rpt_pkg.get_pmt_count(jrev.resource_id,amsc.source_code_id) pmt_count,'||
 'iex_promise_recon_rpt_pkg.get_pmt_amount(jrev.resource_id,amsc.source_code_id) pmt_amt,'||
 'sum(decode(ipd1.state,''BROKEN_PROMISE'',1,0))broken_count,'||
 'sum(decode(ipd1.state,''BROKEN_PROMISE'',gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipd1.promise_amount),0))broken_amt,'||
 'sum(decode(ipd1.state,''PROMISE'',1,0))open_count,'||
 'sum(decode(ipd1.state,''PROMISE'',gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',ipd1.promise_amount),0))open_amt '||
 'from '||
 'iex_promise_details ipd1,jtf_rs_resource_extns_vl jrev,ar_system_parameters asp,'||
 'ams_source_codes amsc,hz_cust_accounts hzca '||
 'where jrev.resource_id(+)=ipd1.resource_id '||
 'and amsc.source_code_id(+)=ipd1.campaign_sched_id '||
 'and ipd1.cust_account_id=hzca.cust_account_id and ipd1.org_id=asp.org_id ';

Line: 460


    select TRUNC(sysdate)
    into l_sysdate
    from dual;

Line: 560


    select nvl(fnd_profile.value('IEX_COLLECTIONS_RATE_TYPE'),'N')
into l_coll_rate
from dual;

Line: 565


  select default_exchange_rate_type
  into l_coll_rate
  from ar_cmgt_setup_options;

Line: 576


select NVL(FND_PROFILE.value('IEX_LEASE_ENABLED'), 'N')
into l_ctr_enbl_flg
from dual;

Line: 604


 l_pmt_sum:='select source_code_id,resource_id,pmt_count, pmt_amount from '||
                             '(select amsc.source_code_id,jrev.resource_id resource_id,count(unique arra.cash_receipt_id) pmt_count ,'||
                             'sum(decode(arra.cash_receipt_id, null,0,gl_currency_api.convert_amount(ipd1.currency_code,'''||p_currency||''',sysdate,'''||l_coll_rate||''',prdapplx.amount_applied)))pmt_amount '||
                            'FROM  IEX_PROMISE_DETAILS IPD1 , JTF_RS_RESOURCE_EXTNS_VL JREV , AMS_SOURCE_CODES AMSC , iex_prd_appl_xref PRDAPPLX , ar_receivable_applications  ARRA , hz_cust_accounts HZCA,ar_system_parameters asp '||
                            'WHERE  JREV.resource_id(+) = IPD1.resource_id '||
                             'AND AMSC.source_code_id(+) = IPD1.campaign_sched_id '||
                             'and IPD1.cust_account_id  = HZCA.cust_account_id and ipd1.org_id=asp.org_id '||
                             'and nvl(PRDAPPLX.reversed_flag,''N'') <> ''Y'' '||
                             'and IPD1.promise_detail_id =  PRDAPPLX.promise_detail_id (+) '||
                             'and PRDAPPLX.receivable_application_id =  ARRA.receivable_application_id(+) ';

Line: 628


       select count(distinct(resource_id))
       into l_resr_cnt
       from
       JTF_RS_RESOURCE_EXTNS
       where source_id in ( select distinct b.person_id
       from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a
       where b.manager_person_id = a.source_id
       and a.resource_id = p_group_by_value_coll);--100000937);

Line: 638


       l_resr_qry:= '(select distinct resource_id '||
                    'from '||
                    'JTF_RS_RESOURCE_EXTNS '||
		    'where source_id in (select distinct b.person_id '||
                    'from jtf_rs_rep_managers b, JTF_RS_RESOURCE_EXTNS a '||
		    'where b.manager_person_id = a.source_id '||
		    'and a.resource_id = '||p_group_by_value_coll;

Line: 671


	   l_where:=l_where||' and (ipd1.campaign_sched_id in (Select source_code_id '||
 	                     'from  ams_source_codes '||
 	                      'where arc_source_code_for =''CAMP'') '||
                              'OR IPD1.campaign_sched_id is NULL)';