[Home] [Help]
306: to_char(sty.creation_date , 'YYYY-MM-DD') creation_date,
307: stry_temp_wkitem.name,
308: iex_utilities.get_lookup_meaning('IEX_STRATEGY_WORK_STATUS',swi.status_code) STATUS_MEANING,
309: jtf.source_name
310: from iex_strategies sty,
311: iex_strategy_templates_tl tpl,
312: iex_strategy_work_items swi,
313: iex_stry_temp_work_items_vl stry_temp_wkitem,
314: jtf_rs_resource_extns jtf
329: sty.delinquency_id,
330: sty.score_value,
331: tpl.strategy_name,
332: TO_CHAR(sty.creation_date,'YYYY-MM-DD') creation_date
333: from iex_strategies sty,
334: iex_strategy_templates_tl tpl
335: where sty.strategy_id = p_strategy_id
336: and sty.strategy_template_id = tpl.strategy_temp_id
337: and tpl.language = userenv('LANG');
557:
558: -- strategies in open or onhold status without work flow
559: CURSOR c_str_without_wf IS
560: select strategy_id
561: from iex_strategies str
562: where str.status_code IN ('OPEN','ONHOLD')
563: AND not exists ( select 1 from wf_items wf
564: where wf.item_key = str.strategy_id and wf.item_type='IEXSTRY');
565:
565:
566: -- strategies in open or onhold status without work items
567: CURSOR c_str_without_wi IS
568: select strategy_id
569: from iex_strategies str
570: where str.status_code in ('OPEN','ONHOLD')
571: and not exists ( select 1 from iex_strategy_work_items wi
572: where wi.strategy_id = str.strategy_id);
573:
573:
574: -- strategy work items of type automatic and open status without work flow
575: CURSOR c_strwi_witout_fulwf IS
576: select str.strategy_id
577: from iex_strategies str, iex_strategy_work_items wi , IEX_STRY_TEMP_WORK_ITEMS_B witemp
578: where str.strategy_id = wi.strategy_id
579: and wi.work_item_template_id = witemp.work_item_temp_id
580: and witemp.work_type='AUTOMATIC'
581: and wi.status_code = 'OPEN'
585:
586: -- strategy work items in error check notify status
587: CURSOR c_str_inerror_notify IS
588: select str.strategy_id
589: from iex_strategies str, iex_strategy_work_items wi
590: where str.strategy_id = wi.strategy_id
591: and wi.status_code = 'INERROR_CHECK_NOTIFY'
592: and str.status_code in ('OPEN','ONHOLD');
593:
593:
594: -- work items of type automatic in open status for more than 2 days
595: CURSOR c_str_automatic_wait IS
596: select str.strategy_id
597: from iex_strategies str, iex_strategy_work_items wi , iex_stry_temp_work_items_b witemp
598: where str.strategy_id = wi.strategy_id
599: and wi.work_item_template_id = witemp.work_item_temp_id
600: and witemp.work_type = 'AUTOMATIC'
601: and wi.status_code in 'OPEN'
620:
621: CURSOR c_wi_no_performer IS
622: select b.strategy_id
623: from iex_strategy_work_items wi,
624: IEX_STRATEGIES b,
625: WF_ITEM_ACTIVITY_STATUSES_V wfia
626: where wi.work_item_id = wfia.item_key
627: and wfia.item_type='IEXSTFFM'
628: and wfia.activity_label = 'FULFILLMENT FAILED'
636: CURSOR c_wi_no_role IS
637: select str.strategy_id
638: from iex_strategy_work_items wi,
639: WF_ITEM_ACTIVITY_STATUSES_V wfia,
640: iex_strategies str
641: where wi.work_item_id = wfia.item_key
642: and wfia.error_name = 'WFNTF_ROLE'
643: and wfia.activity_status_code='ERROR'
644: and wfia.item_type='IEXSTFFM'
649:
650: -- strategy work items with IEXSTFFM work flow and work item template is not automatic
651: CURSOR c_wi_wf_not_auto IS
652: select str.strategy_id from
653: iex_strategies str,iex_strategy_work_items wi, iex_stry_temp_work_items_vl wit, wf_items wfi
654: where str.strategy_id = wi.strategy_id
655: and str.status_code in ('OPEN','ONHOLD')
656: and wi.work_item_template_id = wit.work_item_temp_id
657: and wit.work_type <> 'AUTOMATIC'
700: , JTF_OBJECT_TYPE
701: , JTF_OBJECT_ID
702: , CUSTOMER_SITE_USE_ID
703: , ORG_ID
704: FROM iex_strategies
705: WHERE strategy_id = p_strategy_id;
706:
707: BEGIN
708: writelog('Begin gen_xml_corpt_str_list');
876: wi.status_code,
877: dunn.delivery_status,
878: stytm.strategy_name strategy_name,
879: stry_temp_wkitem.name workitem_name
880: from iex_strategies iexst, iex_strategy_work_items wi,
881: hz_parties hzp, hz_cust_accounts acct, iex_dunnings dunn ,
882: iex_strategy_templates_vl stytm,
883: iex_stry_temp_work_items_vl stry_temp_wkitem
884: where wi.last_update_date>(sysdate-1)