APPS.IEX_STRATEGY_DIAG_REPORT SQL Statements

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

Line: 88

 SELECT competence_id
 from iex_strategy_work_skills
 where work_item_temp_id = p_work_item_temp_id;

Line: 94

select source_name
from jtf_rs_resource_extns
where resource_id = p_resource_id;

Line: 178

   select
     meaning
   from fnd_lookups
   where lookup_type= 'YES_NO'
    and lookup_code = p_lookup_code;

Line: 186

   select source_name
   from jtf_rs_resource_extns
   where resource_id = p_resource_id;

Line: 194

      select to_char(sysdate, 'YYYY-MM-DD')
      into l_report_date
      from dual;

Line: 299

   select
     sty.party_id,
     sty.cust_Account_id,
     sty.customer_site_use_id,
     sty.delinquency_id,
     sty.score_value,
     tpl.strategy_name,
     to_char(sty.creation_date , 'YYYY-MM-DD') creation_date,
     stry_temp_wkitem.name,
     iex_utilities.get_lookup_meaning('IEX_STRATEGY_WORK_STATUS',swi.status_code) STATUS_MEANING,
     jtf.source_name
   from iex_strategies sty,
    iex_strategy_templates_tl tpl,
    iex_strategy_work_items swi,
    iex_stry_temp_work_items_vl stry_temp_wkitem,
    jtf_rs_resource_extns jtf
   where sty.strategy_id = p_strategy_id
    and sty.strategy_template_id = tpl.strategy_temp_id
    and tpl.language = userenv('LANG')
    and sty.next_work_item_id = swi.work_item_id
    and swi.work_item_template_id = stry_temp_wkitem.work_item_temp_id
    and stry_temp_wkitem.language = userenv('LANG')
    and swi.resource_id = jtf.resource_id;

Line: 325

   select
     sty.party_id,
     sty.cust_Account_id,
     sty.customer_site_use_id,
     sty.delinquency_id,
     sty.score_value,
     tpl.strategy_name,
     TO_CHAR(sty.creation_date,'YYYY-MM-DD') creation_date
   from iex_strategies sty,
    iex_strategy_templates_tl tpl
   where sty.strategy_id = p_strategy_id
    and sty.strategy_template_id = tpl.strategy_temp_id
    and tpl.language = userenv('LANG');

Line: 341

   select
     tpl.strategy_name
   from iex_strategy_templates_tl tpl
   where tpl.strategy_temp_id = l_sty_template_id
     and tpl.language = userenv('LANG');

Line: 349

   select stry_temp_wkitem.name,
    stry_temp_wkitem.work_item_temp_id,
    iex_utilities.get_lookup_meaning('IEX_STRATEGY_WORK_STATUS',(decode(stry_temp_wkitem.pre_execution_wait,0,'OPEN','PRE-WAIT'))) STATUS_MEANING
   from iex_strategy_work_temp_xref xref
    ,iex_stry_temp_work_items_vl stry_temp_wkitem
   where xref.work_item_temp_id = stry_temp_wkitem.work_item_temp_id
    and xref.strategy_temp_id = l_sty_template_id
    and stry_temp_wkitem.language = userenv('LANG')
    order by xref.work_item_order;

Line: 361

   select
    party_name
   from hz_parties
   where party_id = p_party_id;

Line: 368

   select
    p.party_name,
    c.account_number
   from hz_parties p,
    hz_cust_accounts c
   where c.cust_account_id = p_cust_acct_id
    and c.party_id = p.party_id;

Line: 378

   select
    p.party_name,
    c.account_number,
    site_uses.location
   from hz_parties p,
    hz_cust_accounts c,
    hz_cust_acct_sites_all acct_sites,
    hz_cust_site_uses_all site_uses
   where site_uses.site_use_id = p_cust_site_use_id
   and acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id
   and c.cust_account_id = acct_sites.cust_account_id
   and p.party_id = c.party_id;

Line: 393

   select
    p.party_name,
    aps.trx_number TRANSACTION_NUMBER
   from iex_delinquencies_all del,
    ar_payment_schedules_all aps ,
    hz_parties p
   where del.delinquency_id = p_delinquency_id
    and del.payment_Schedule_id = aps.payment_Schedule_id
    and del.party_cust_id = p.party_id;

Line: 560

   select strategy_id
   from iex_strategies str
   where str.status_code IN ('OPEN','ONHOLD')
   AND not exists ( select 1 from wf_items wf
                    where wf.item_key = str.strategy_id and wf.item_type='IEXSTRY');

Line: 568

   select strategy_id
   from iex_strategies str
   where str.status_code in ('OPEN','ONHOLD')
   and not exists ( select 1 from iex_strategy_work_items wi
                    where wi.strategy_id = str.strategy_id);

Line: 576

   select str.strategy_id
   from iex_strategies str, iex_strategy_work_items wi , IEX_STRY_TEMP_WORK_ITEMS_B witemp
   where str.strategy_id = wi.strategy_id
   and wi.work_item_template_id = witemp.work_item_temp_id
   and witemp.work_type='AUTOMATIC'
   and wi.status_code = 'OPEN'
   and not exists ( select 1 from wf_items wf
                    where wf.item_key = wi.work_item_id
                    and wf.item_type in ('IEXSTFFM','IEXSTCM'));

Line: 588

   select str.strategy_id
   from iex_strategies str, iex_strategy_work_items wi
   where str.strategy_id = wi.strategy_id
   and wi.status_code = 'INERROR_CHECK_NOTIFY'
   and str.status_code in ('OPEN','ONHOLD');

Line: 596

   select str.strategy_id
   from iex_strategies str, iex_strategy_work_items wi , iex_stry_temp_work_items_b witemp
   where str.strategy_id = wi.strategy_id
   and wi.work_item_template_id = witemp.work_item_temp_id
   and witemp.work_type = 'AUTOMATIC'
   and wi.status_code in 'OPEN'
   and trunc(wi.execute_start + 2 ) < trunc(sysdate);

Line: 606

   select work_item_temp_id
   from  IEX_STRY_TEMP_WORK_ITEMS_B
   where work_type = 'AUTOMATIC'
   AND category_type IN ('EMAIL','FAX')
   and xdo_template_id is null;

Line: 614

   select work_item_temp_id
   from IEX_STRY_TEMP_WORK_ITEMS_B
   where work_type='WORKFLOW'
   and category_type='WORKFLOW' and workflow_item_type is null;

Line: 622

   select b.strategy_id
   from iex_strategy_work_items wi,
       IEX_STRATEGIES b,
   WF_ITEM_ACTIVITY_STATUSES_V wfia
   where wi.work_item_id = wfia.item_key
   and wfia.item_type='IEXSTFFM'
   and wfia.activity_label = 'FULFILLMENT FAILED'
   and wfia.error_name ='WFENG_NOTIFICATION_PERFORMER'
   and wfia.activity_status_code='ERROR'
   and wi.status_code ='INERROR_CHECK_NOTIFY'
   and wi.STRATEGY_ID= b.STRATEGY_ID
   and b.status_code in ('OPEN','ONHOLD');

Line: 637

   select str.strategy_id
   from iex_strategy_work_items wi,
   WF_ITEM_ACTIVITY_STATUSES_V wfia,
   iex_strategies str
   where wi.work_item_id = wfia.item_key
   and wfia.error_name = 'WFNTF_ROLE'
   and wfia.activity_status_code='ERROR'
   and wfia.item_type='IEXSTFFM'
   and wfia.activity_label = 'FULFILLMENT FAILED'
   and wi.status_code='INERROR_CHECK_NOTIFY'
   and str.strategy_id = wi.strategy_id
   and str.status_code in ('OPEN','ONHOLD');

Line: 652

   select str.strategy_id from
   iex_strategies str,iex_strategy_work_items wi, iex_stry_temp_work_items_vl wit, wf_items wfi
   where str.strategy_id = wi.strategy_id
   and str.status_code in ('OPEN','ONHOLD')
   and wi.work_item_template_id = wit.work_item_temp_id
   and wit.work_type <> 'AUTOMATIC'
   and wi.work_item_id = wfi.item_key
   and wfi.item_type = 'IEXSTFFM';

Line: 663

   Select wi.work_item_id
   from iex_strategy_work_items wi,iex_dunnings dunn ,iex_stry_temp_work_items_vl stry_temp_wkitem
   where wi.last_update_date>(sysdate-1)
   and dunn.object_id (+) = wi.work_item_id
   and wi.work_item_template_id = stry_temp_wkitem.work_item_temp_id
   and exists
   (select 1 from IEX_STRY_TEMP_WORK_ITEMS_B wt
    where wt.work_item_temp_id=wi.work_item_template_id
    and wt.xdo_template_id is not null);

Line: 676

   SELECT  STRATEGY_ID
,       STATUS_CODE
,       STRATEGY_TEMPLATE_ID
,       DELINQUENCY_ID
,       OBJECT_TYPE
,       OBJECT_ID
,       CUST_ACCOUNT_ID
,       PARTY_ID
,       SCORE_VALUE
,       NEXT_WORK_ITEM_ID
,       USER_WORK_ITEM_YN
,       LAST_UPDATE_DATE
,       LAST_UPDATED_BY
,       LAST_UPDATE_LOGIN
,       CREATION_DATE
,       CREATED_BY
,       OBJECT_VERSION_NUMBER
,       REQUEST_ID
,       PROGRAM_APPLICATION_ID
,       PROGRAM_ID
,       PROGRAM_UPDATE_DATE
,       CHECKLIST_STRATEGY_ID
,       CHECKLIST_YN
,       STRATEGY_LEVEL
,       JTF_OBJECT_TYPE
,       JTF_OBJECT_ID
,       CUSTOMER_SITE_USE_ID
 ,       ORG_ID
   FROM iex_strategies
   WHERE strategy_id = p_strategy_id;

Line: 822

   select ist.work_item_temp_id , istv.name,to_char(ist.creation_date , 'YYYY-MM-DD') creation_date, ist.work_type,ist.category_type,ist.xdo_template_id,ist.workflow_item_type
   from IEX_STRY_TEMP_WORK_ITEMS_B ist , IEX_STRY_TEMP_WORK_ITEMS_VL istv
   where ist.work_item_temp_id = istv.work_item_temp_id
   and ist.work_item_temp_id = p_wi_temp_id;

Line: 872

   Select hzp.PARTY_NAME, acct.account_number, iexST.PARTY_ID, IEXST.CUST_ACCOUNT_ID,
   TO_CHAR(wi.LAST_UPDATE_DATE, 'YYYY-MM-DD') Last_Update,
   to_char(wi.creation_date , 'YYYY-MM-DD') creation_date,
   wi.WORK_ITEM_ID,
   wi.status_code,
   dunn.delivery_status,
   stytm.strategy_name strategy_name,
   stry_temp_wkitem.name workitem_name
   from iex_strategies iexst, iex_strategy_work_items wi,
   hz_parties hzp, hz_cust_accounts acct, iex_dunnings dunn ,
   iex_strategy_templates_vl stytm,
   iex_stry_temp_work_items_vl stry_temp_wkitem
   where wi.last_update_date>(sysdate-1)
   and wi.strategy_id = iexst.strategy_id
   AND hzp.party_id = iexst.party_id
   and dunn.object_id (+) = wi.work_item_id
   and iexst.cust_account_id = acct.cust_account_id (+)
   and iexst.strategy_template_id = stytm.strategy_temp_id
   and wi.work_item_template_id = stry_temp_wkitem.work_item_temp_id
   and exists
   (select 1 from IEX_STRY_TEMP_WORK_ITEMS_B wt
   where wt.work_item_temp_id=wi.work_item_template_id
   and wt.xdo_template_id is not null)
   AND wi.work_item_id = p_work_item_id;

Line: 901

   l_last_update_date VARCHAR2(100);

Line: 912

	FETCH c_wi_temp INTO L_PARTY_NAME,l_account_number,l_party_id,l_cust_account_id,l_last_update_date,l_creation_date,
	l_work_item_id,l_status_code,l_delivery_status,l_strategy_name,l_workitem_name;

Line: 922

	l_last_update_date    := format_string(l_last_update_date);

Line: 937

    	l_xml_body     := l_xml_body ||l_new_line||' ' || l_last_update_date || '';

Line: 957

select preference_value
from iex_app_preferences_b
where preference_name='COLLECTIONS STRATEGY LEVEL'
and enabled_flag='Y';