EAM Weekly Schedule

Description
Categories: Administration, BI Publisher
Application: Enterprise Asset Management
Source: EAM Weekly Schedule report (XML)
Short Name: EAMWSREP_XML
DB package: EAM_EAMWSREP_XMLP_PKG
select distinct Decode(:P_SORT_BY,1,we.wip_entity_name,2,wdj.scheduled_start_date,3,wdj.scheduled_completion_date,4,round(wdj.scheduled_completion_date-wdj.scheduled_start_date),5,wdj.asset_number,6,msib.concatenated_segments,7,ml3.meaning,8,mel.location_codes,we.wip_entity_name) Sort_by,
we.wip_entity_name,
ml4.meaning FIRM_PLANNED_FLAG,
(select department_code from bom_departments where department_id=wdj.owning_department) Owning_Department,
we.description,
cii.instance_number as Asset,
cii.instance_description as DESCRIPTIVE_TEXT,
msib.concatenated_segments Asset_group,
ml3.meaning priority,
mel.location_codes Area,
worp.op_seq_num ,
bd.department_code ,
ml2.meaning Shutdown_type,
br.resource_code ,
worp.Assigned_units,
mlmsf.meaning material_shortage_flag,
mlwft.meaning workflow_type,
mlws.meaning warranty_status,
((SELECT PAPF.FULL_NAME  FROM PER_ALL_PEOPLE_F PAPF,bom_resource_employees bre,bom_resources br1
        WHERE trunc(sysdate) between papf.effective_start_date and papf.effective_end_date AND
        PAPF.PERSON_ID = BRE.PERSON_ID  and
        bre.instance_id =worp.instance_id and
        bre.resource_id =br.resource_id and
		br1.resource_id=bre.resource_id and
		br1.organization_id=worp.organization_id  and
        bre.organization_id = worp.organization_id
		and br1.resource_type=2)
		union
(select  MSIK.CONCATENATED_SEGMENTS
      FROM MTL_SYSTEM_ITEMS_KFV MSIK, bom_resource_equipments bre,bom_resources br2
      WHERE BRE.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID AND
            MSIK.ITEM_TYPE = 'EQ'  and
            bre.instance_id =worp.instance_id and
            bre.resource_id = br.resource_id and
		   br2.resource_id=bre.resource_id and
		   br2.organization_id=worp.organization_id  and
           bre.organization_id = worp.organization_id
		  and br2.resource_type=1)) Instance_name,
worp.res_start_date,
worp.res_completion_date,
round((worp.res_completion_date- worp.res_start_date)*24) Duration,
round((wdj.scheduled_completion_date-wdj.scheduled_start_date)*24) workorder_duration,
EAM_EAMWSREP_XMLP_PKG.days(RES_START_DATE, RES_COMPLETION_DATE) DAYS,
EAM_EAMWSREP_XMLP_PKG.CP_1_p CP_1,
	EAM_EAMWSREP_XMLP_PKG.CP_2_p CP_2,
	EAM_EAMWSREP_XMLP_PKG.CP_3_p CP_3,
	EAM_EAMWSREP_XMLP_PKG.CP_4_p CP_4,
	EAM_EAMWSREP_XMLP_PKG.CP_5_p CP_5,
	EAM_EAMWSREP_XMLP_PKG.CP_6_p CP_6,
	EAM_EAMWSREP_XMLP_PKG.CP_7_p CP_7,
	EAM_EAMWSREP_XMLP_PKG.CP_8_p CP_8,
	to_char(worp.res_start_date,'DD-MON-YY') res_start_date1,
   to_char(worp.res_completion_date,'DD-MON-YY') res_completion_date1,
	'X' dummy
from
(select distinct wo.wip_entity_id wip_entity_id, wo.operation_seq_num op_seq_num, wo.department_id, wo.organization_id,
wo.first_unit_start_date Op_start_date, wo.last_unit_completion_date Op_end_date,
wor.ASSIGNED_UNITS, wor.RESOURCE_ID, wor.START_DATE Res_Start_date, wor.COMPLETION_DATE Res_Completion_date,
wor.RESOURCE_SEQ_NUM,wori.instance_id,wo.operation_completed,wo.shutdown_type
from wip_operations wo,wip_operation_resources wor,wip_op_resource_instances wori
where wor.wip_entity_id(+) = wo.wip_entity_id
and wor.organization_id (+)= wo.organization_id
and wor.operation_seq_num (+)= wo.operation_seq_num
and wori.operation_seq_num(+)=wor.operation_seq_num
and wori.resource_seq_num(+)=wor.resource_seq_num
and wori.wip_entity_id(+)=wor.wip_entity_id) worp,
wip_entities we,
wip_discrete_jobs wdj,
eam_work_order_details ewod,
bom_departments bd,
bom_resources br,
mtl_system_items_b_kfv msib,
csi_item_instances cii,
eam_org_maint_defaults eomd,
mtl_parameters mp,
mtl_eam_locations mel,
mfg_lookups ml2,
mfg_lookups ml3,
mfg_lookups ml4,
mfg_lookups mlmsf,
mfg_lookups mlwft,
mfg_lookups mlws
where
we.organization_id = :P_ORG_ID and
we.wip_entity_id = wdj.wip_entity_id
and we.organization_id = wdj.organization_id
and wdj.wip_entity_id =worp.wip_entity_id(+)
and ewod.organization_id = wdj.organization_id
and ewod.wip_entity_id = wdj.wip_entity_id
and br.organization_id(+) = worp.organization_id
and br.resource_id(+)= worp.resource_id
&CF_RESOURCE
&CF_INSTANCE
&CF_OWNING_DEPARTMENT
&CF_ASSIGNED_DEPT
and worp.department_id = bd.department_id
and (worp.operation_completed = 'N' or  worp.operation_completed is null)
and ( :P_WEEK_START_DATE between wdj.scheduled_start_date and wdj.scheduled_completion_date
     or (wdj.scheduled_start_date >= :P_WEEK_START_DATE
     and wdj.scheduled_completion_date <= ( :P_WEEK_START_DATE +7))
     or (( :P_WEEK_START_DATE +7) between wdj.scheduled_start_date
     and wdj.scheduled_completion_date))
and (:P_WEEK_START_DATE  between  worp.op_start_date and worp.op_end_date
     or ( worp.op_start_date >= :P_WEEK_START_DATE
     and worp.op_end_date <= ( :P_WEEK_START_DATE +7))
     or (( :P_WEEK_START_DATE  +7) between  worp.op_start_date
     and worp.op_end_date))
and ((( :P_WEEK_START_DATE  between worp.res_start_date and worp.res_completion_date)
     or (worp.res_start_date >= :P_WEEK_START_DATE
	 and worp.res_completion_date <= ( :P_WEEK_START_DATE +7))
     or (( :P_WEEK_START_DATE +7) between worp.res_start_date and worp.res_completion_date))
	 or worp.res_start_date is null)
and wdj.organization_id= mp.maint_organization_id
and mp.organization_id = msib.organization_id
and msib.inventory_item_id=nvl(wdj.asset_group_id,rebuild_item_id)
and cii.instance_id (+) = decode(wdj.maintenance_object_type, 3, wdj.maintenance_object_id, NULL)
and eomd.object_type (+) = 50
and (eomd.organization_id is null or eomd.organization_id = wdj.organization_id)
and eomd.object_id (+) = cii.instance_id
and (eomd.area_id = mel.location_id (+))
&CF_AREA
&CF_REBUILD_ITEM
&CF_ASSET
and wdj.status_type=3 /*released*/
and ml2.lookup_type(+) = 'BOM_EAM_SHUTDOWN_TYPE'
and ml2.lookup_code(+) = nvl(worp.shutdown_type,-99)
&CF_SHUTDOWN_TYPE
and ml3.lookup_type(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
and ml3.lookup_code (+)= nvl(wdj.priority,-99)
and ml4.lookup_type ='SYS_YES_NO'
and ml4.lookup_code = wdj.FIRM_PLANNED_FLAG
and mlwft.lookup_type(+) = 'EAM_WORKFLOW_TYPE'
and mlwft.lookup_code (+)= ewod.workflow_type
and mlws.lookup_type(+) = 'EAM_WARRANTY_STATUS'
and mlws.lookup_code (+)= ewod.warranty_claim_status
and mlmsf.lookup_type(+) = 'EAM_MATERIAL_SHORTAGE'
and mlmsf.lookup_code (+)= ewod.material_shortage_flag
order by 1,2,4,3,5,6,7,11,12,13,14,15,20
Parameter Name SQL text Validation
Org ID
 
Number
Org Name
 
LOV Oracle
Sort By
 
LOV Oracle
Instance
 
LOV Oracle
Resource
 
LOV Oracle
Shutdown Type
 
LOV Oracle
Rebuild Item
 
LOV Oracle
Asset
 
LOV Oracle
Area
 
LOV Oracle
Week Starting
 
LOV Oracle
Assigned Department
 
LOV Oracle
Owning Department
 
LOV Oracle