[Home] [Help]
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
315: where sty.strategy_id = p_strategy_id
316: and sty.strategy_template_id = tpl.strategy_temp_id
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:
574: -- strategy work items of type automatic and open status without work flow
575: CURSOR c_strwi_witout_fulwf IS
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'
619: -- strategies in open or onhold status with work flow error no performer
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'
634: -- strategies in open or onhold status with work flow error no role
635:
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'
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'
660:
661: -- automatic work items processed in sysdate
662: CURSOR c_auto_wi_processed IS
663: Select wi.work_item_id
664: from iex_strategy_work_items wi,iex_dunnings dunn ,iex_stry_temp_work_items_vl stry_temp_wkitem
665: where wi.last_update_date>(sysdate-1)
666: and dunn.object_id (+) = wi.work_item_id
667: and wi.work_item_template_id = stry_temp_wkitem.work_item_temp_id
668: and exists
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)