ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEFC79D2E0530100007F1FF2 INV Item (org id dependent) Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where (msiv.organization_id=:$flex$.organization_code or :$flex$.organization_code is null) order by msiv.concatenated_segments US Required for certain reports matching :$flex$.organization_code to organization_id instead of organization_code, as these reports return the org_id instead of the code from the LOV 8E2FF36EDF0479D2E0530100007F1FF2 WIP Job (org id dependent) Y Y select we.wip_entity_name value, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700)||nvl2(we.description,': '||we.description,null) description from wip_entities we where we.organization_id=:$flex$.organization_code order by we.creation_date desc US 8E2FF36EDE9579D2E0530100007F1FF2 WIP Job Status Y select ml.meaning value, ml.description from mfg_lookups ml where ml.lookup_type='WIP_JOB_STATUS' order by ml.meaning US A68D2A82FC847EC8E053B46B6358B004Y select distinct We.Wip_Entity_Name JOB, msiv.concatenated_segments ASSEMBLY, msiv.description "DESCRIPTION", (SELECT Meaning FROM apps.fnd_lookup_values WHERE lookup_type LIKE 'WIP_ENTITY' and lookup_code = we.entity_type and language = 'US') TYPE, Wdj.Class_Code CLASS, wdj.net_quantity QUANTITY, (SELECT NVL(msib1.segment1, ' ') from apps.mtl_system_items_b msib1 where msib1.inventory_item_id = wdj.bom_reference_id and msib1.ORGANIZATION_ID = wdj.ORGANIZATION_ID) BOM_REFERENCE, NVL(Wdj.Alternate_BOM_Designator, ' ') ALTERNATE_BILL, (SELECT NVL(msib2.segment1, ' ') from apps.mtl_system_items_b msib2 where msib2.inventory_item_id = wdj.routing_reference_id and msib2.ORGANIZATION_ID = wdj.ORGANIZATION_ID) ROUTING_REFERENCE, NVL(Wdj.Alternate_Routing_Designator, ' ') ALTERNATE_ROUTING, (SELECT Meaning FROM apps.fnd_lookup_values WHERE lookup_type LIKE 'WIP_CLASS_TYPE' and lookup_code = wdj.job_type and language = 'US') "WO TYPE", (SELECT Meaning FROM apps.fnd_lookup_values WHERE lookup_type LIKE 'WIP_JOB_STATUS' and lookup_code = wdj.status_type and language = 'US') STATUS, NVL(We.Description, ' ') "WO DESCRIPTION", Wdj.date_released WO_RELEASE_DATE, Wdj.date_completed WO_COMPLETION_DATE, wdj.date_closed WO_CLOSED_DATE, wo.Operation_Seq_Num OPERATION_SEQ_NUM, to_char(Wo.First_Unit_Start_Date,'dd-mon-yyyy hh24:mi:ss') OPN_START_DATE, to_char(Wo.Last_Unit_Completion_Date,'dd-mon-yyyy hh24:mi:ss') OPN_COMPLETION_DATE, NVL(Bso.Operation_Code, ' ') OPERATION_CODE, (Select Round((wo.Last_Unit_Completion_Date - wo.First_Unit_Start_Date)* 24*60,0)"Round" from DUAL) RUN_TIME_MINS from apps.wip_operations wo ,apps.wip_entities we ,apps.wip_discrete_jobs wdj ,apps.mtl_system_items_vl msiv ,apps.bom_operational_routings bor ,apps.bom_operation_sequences bos ,apps.bom_standard_operations bso ,apps.mfg_lookups ml where 1=1 and wo.wip_entity_id = we.wip_entity_id and wo.operation_sequence_id = bos.operation_sequence_id and wdj.wip_entity_id = we.wip_entity_id and msiv.inventory_item_id = wdj.primary_item_id and msiv.ORGANIZATION_ID = wdj.ORGANIZATION_ID and msiv.inventory_item_id = bor.assembly_item_id and bos.routing_sequence_id = bor.routing_sequence_id and bso.standard_operation_id(+) = bos.standard_operation_id and ml.lookup_code (+) = to_number(bos.shutdown_type) and ml.lookup_type (+) = 'BOM_EAM_SHUTDOWN_TYPE' and we.entity_type not in (6,7) and Wdj.Class_Code not in ('BLDGMAINT', 'CALIB', 'ENGMAINT', 'MAINT') Order by to_char(Wdj.date_released,'dd-mon-yyyy hh24:mi:ss'), We.Wip_Entity_Name, Wo.Operation_Seq_Num F WIP - WIP Job Production Details This program extracts job details with release and operation dates and elapsed times according to when the transactions were entered in Oracle. It enables identifying job details to the operation level and delays in production, for example when an operation is not completed or exceeds the time expected. The report can be run by organization, date released, job, Assembly, job status. License: Creative commons with Attribution by Ion Yılmaz, Senfor International, www.senfor-intl.eu US WIP Senfor Intl WIP Production Job Details This program extracts job details with release and operation dates and elapsed times according to when the transactions were entered in Oracle. It is allows identifying job details to the operation level delays in production, for example when an operation is not completed or exceeds the time expected. The report can be run by any combination of organization, release dates, job, assembly, job status. License: Creative commons with Attribution by Ion Yılmaz, Senfor International, www.senfor-intl.eu Senfor International WIP Senfor Intl 1=1 1 10 1=1 wdj.organization_id=:organization_id LOV Oracle INV_SRS_ORG Y select to_char(organization_id) id, organization_code value, organization_name description from org_organization_definitions where nvl(disable_date,sysdate+1)>sysdate order by value,description coalesce( (select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')), (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id and rownum=1) ) Y US Organization 2 20 1=1 wdj.date_released >= :P_TRX_FROM Date US Release Date from 3 30 1=1 wdj.date_released < :P_TRX_TO Date US Release Date to 4 40 1=1 we.wip_entity_name=:job_name LOV WIP Job (org id dependent) 8E2FF36EDF0479D2E0530100007F1FF2 Y Y select we.wip_entity_name value, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700)||nvl2(we.description,': '||we.description,null) description from wip_entities we where we.organization_id=:$flex$.organization_code order by we.creation_date desc US Job 5 50 1=1 msiv.segment1=:item LOV INV Item (org id dependent) 8E2FF36EDEFC79D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where (msiv.organization_id=:$flex$.organization_code or :$flex$.organization_code is null) order by msiv.concatenated_segments US Assembly 6 60 1=1 wdj.status_type=xxen_util.lookup_code(:job_status,'WIP_JOB_STATUS',700) LOV WIP Job Status 8E2FF36EDE9579D2E0530100007F1FF2 Y select ml.meaning value, ml.description from mfg_lookups ml where ml.lookup_type='WIP_JOB_STATUS' order by ml.meaning US Job Status