ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 785DDFB69F4F406C9657D5993320405FY select mp.organization_code, eec.change_notice change_notice, ecotv.type_name change_type, xxen_util.meaning(eec.status_type,'ECG_ECN_STATUS',700) change_status, xxen_util.meaning(eec.approval_status_type,'ENG_ECN_APPROVAL_STATUS',700) approval_status, eec.priority_code priority, eec.reason_code reason, (select hp.party_name from hz_parties hp where eec.requestor_id=hp.party_id) requestor, (select hp.party_name from hz_parties hp where eec.assignee_id=hp.party_id) assignee, (select haouv.name from hr_all_organization_units_vl haouv where eec.responsible_organization_id=haouv.organization_id) responsible_organization, eec.description change_description, eec.initiation_date, eec.need_by_date, eec.approval_request_date, eec.approval_date, eec.implementation_date change_implementation_date, eec.cancellation_date change_cancellation_date, eec.cancellation_comments, decode(row_number() over (partition by eec.organization_id, eec.change_notice order by msiv.concatenated_segments, eri.revised_item_sequence_id &component_order),1,eec.estimated_eng_cost) estimated_eng_cost, decode(row_number() over (partition by eec.organization_id, eec.change_notice order by msiv.concatenated_segments, eri.revised_item_sequence_id &component_order),1,eec.estimated_mfg_cost) estimated_mfg_cost, &eco_dff_columns msiv.concatenated_segments revised_item, msiv.description revised_item_description, msiv.primary_uom_code uom, (select bsb.alternate_bom_designator from bom_structures_b bsb where eri.bill_sequence_id=bsb.bill_sequence_id) alternate_bom, eri.new_item_revision new_revision, eri.new_routing_revision, xxen_util.meaning(eri.status_type,'ECG_ECN_STATUS',700) revised_item_status, eri.early_schedule_date early_effectivity_date, eri.scheduled_date effectivity_date, eri.implementation_date, eri.cancellation_date, (select msiv2.concatenated_segments from mtl_system_items_vl msiv2 where eri.use_up_item_id=msiv2.inventory_item_id and eri.organization_id=msiv2.organization_id) use_up_item, eri.use_up_plan_name use_up_plan, xxen_util.meaning(eri.disposition_type,'ECG_MATERIAL_DISPOSITION',700) disposition, xxen_util.yes(decode(eri.mrp_active,1,'Y')) mrp_active, xxen_util.yes(decode(eri.update_wip,1,'Y')) update_wip, eri.from_end_item_unit_number from_unit_number, eri.descriptive_text revised_item_comment, &revised_item_dff_columns decode(row_number() over (partition by eec.organization_id, eec.change_notice, eri.revised_item_sequence_id order by eri.revised_item_sequence_id &component_order),1,wdj.open_jobs) open_jobs, wdj.open_job_list &component_columns from mtl_parameters mp, eng_engineering_changes eec, eng_change_order_types_vl ecotv, eng_revised_items eri, mtl_system_items_vl msiv, ( select y.organization_id, y.primary_item_id, count(*) open_jobs, listagg(case when y.rn<=15 then y.wip_entity_name end,', ') within group (order by y.rn) open_job_list from ( select wdj.organization_id, wdj.primary_item_id, we.wip_entity_name, row_number() over (partition by wdj.organization_id, wdj.primary_item_id order by wdj.scheduled_start_date, we.wip_entity_name) rn from wip_discrete_jobs wdj, wip_entities we where wdj.status_type in (1,3,6) and (wdj.organization_id,wdj.primary_item_id) in (select eri.organization_id, eri.revised_item_id from eng_revised_items eri) and wdj.wip_entity_id=we.wip_entity_id ) y group by y.organization_id, y.primary_item_id ) wdj &component_from where 1=1 and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and mp.organization_id=eec.organization_id and eec.change_order_type_id=ecotv.change_order_type_id and ecotv.base_change_mgmt_type_code='CHANGE_ORDER' and eec.change_notice=eri.change_notice(+) and eec.organization_id=eri.organization_id(+) and eri.revised_item_id=msiv.inventory_item_id(+) and eri.organization_id=msiv.organization_id(+) and eri.organization_id=wdj.organization_id(+) and eri.revised_item_id=wdj.primary_item_id(+) &component_where order by mp.organization_code, eec.change_notice, msiv.concatenated_segments, eri.revised_item_sequence_id &component_order New report. US ENG Engineering Change Orders Engineering change orders (ECOs) with one row per revised item: the ECO header with its type, status, approval status, priority, reason, requestor and dates, and the revised item with its new revision, effectivity, implementation and cancellation dates, use-up item and material disposition. An ECO without revised items is listed once with blank revised item columns. Change types are those based on the Change Order change category, as on the Engineering Change Orders form. Show Component Changes adds one row per component the revised item adds, changes or disables. Old Quantity and Old Op Seq come from the component the change replaces. Open Jobs is the current number of unreleased, released and on hold discrete jobs building the revised item in the ECO's organization, whatever the ECO's status. Open Job List shows the first 15 of these jobs by scheduled start date. Estimated Eng Cost and Estimated Mfg Cost are shown on the first row of an ECO and Open Jobs on the first row of a revised item only, so that they can be summed. Enginatics &component_columns &component_from &component_order &component_where &eco_dff_columns &revised_item_dff_columns 1=1 1 10 1=1 mp.organization_code=:organization_code LOV INV Organization Code (including master) 0B22DF18E239D64AE0630100007FBD85 Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 2 20 1=1 eec.change_notice=:eco LOV custom Y Y select eec.change_notice value, max(eec.description) description from mtl_parameters mp, eng_engineering_changes eec where (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y') and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and mp.organization_id=eec.organization_id and eec.change_order_type_id in (select ecot.change_order_type_id from eng_change_order_types ecot where ecot.base_change_mgmt_type_code='CHANGE_ORDER') group by eec.change_notice order by eec.change_notice US ECO 3 30 1=1 ecotv.type_name=:change_type LOV custom Y select ecotv.type_name value, max(ecotv.description) description from eng_change_order_types_vl ecotv where ecotv.base_change_mgmt_type_code='CHANGE_ORDER' group by ecotv.type_name order by ecotv.type_name US Change Type 4 40 1=1 eec.status_type=:eco_status LOV custom Y select ml.lookup_code id, ml.meaning value, null description from mfg_lookups ml where ml.lookup_type='ECG_ECN_STATUS' and ml.enabled_flag='Y' order by ml.lookup_code US ECO Status 5 50 1=1 eec.approval_status_type=:approval_status LOV custom Y select ml.lookup_code id, ml.meaning value, null description from mfg_lookups ml where ml.lookup_type='ENG_ECN_APPROVAL_STATUS' and ml.enabled_flag='Y' order by ml.lookup_code US Approval Status 6 60 1=1 eec.status_type not in (5,6) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Open Only Excludes cancelled and implemented ECOs. 7 70 1=1 msiv.concatenated_segments=:revised_item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Revised Item 8 80 1=1 eri.scheduled_date>=:effectivity_date_from Date US Effectivity Date From Scheduled effectivity date of the revised item. 9 90 1=1 eri.scheduled_date<:effectivity_date_to+1 Date US Effectivity Date To 10 100 &component_columns , xxen_util.meaning(bcb.acd_type,'ECG_ACTION',700) action, bcb.operation_seq_num op_seq, bcb.item_num item_seq, (select msiv3.concatenated_segments from mtl_system_items_vl msiv3 where bcb.component_item_id=msiv3.inventory_item_id and eri.organization_id=msiv3.organization_id) component, (select msiv3.description from mtl_system_items_vl msiv3 where bcb.component_item_id=msiv3.inventory_item_id and eri.organization_id=msiv3.organization_id) component_description, (select msiv3.primary_uom_code from mtl_system_items_vl msiv3 where bcb.component_item_id=msiv3.inventory_item_id and eri.organization_id=msiv3.organization_id) component_uom, (select bcb2.operation_seq_num from bom_components_b bcb2 where bcb.old_component_sequence_id=bcb2.component_sequence_id and bcb.acd_type in (2,3)) old_op_seq, (select bcb2.component_quantity from bom_components_b bcb2 where bcb.old_component_sequence_id=bcb2.component_sequence_id and bcb.acd_type in (2,3)) old_quantity, decode(bcb.acd_type,3,to_number(null),bcb.component_quantity) new_quantity, bcb.component_yield_factor yield, xxen_util.meaning(bcb.wip_supply_type,'WIP_SUPPLY',700) wip_supply_type, bcb.supply_subinventory, bcb.effectivity_date component_effectivity_date, bcb.disable_date component_disable_date, bcb.implementation_date component_implementation_date LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Component Changes Adds one row per component added, changed or disabled by the revised item, with the old and new quantity and operation sequence. 11 &component_from , bom_components_b bcb US Show Component Changes 12 &component_order , bcb.operation_seq_num, bcb.item_num, bcb.component_sequence_id US Show Component Changes 13 &component_where and eri.revised_item_sequence_id=bcb.revised_item_sequence_id(+) US Show Component Changes 14 110 &eco_dff_columns select xxen_util.dff_columns(p_table_name=>'eng_engineering_changes',p_table_alias=>'eec',p_row_id=>'rowid',p_column_name_prefix=>'ECO: ',p_display_mode=>:dff_display) sql_text from dual LOV Blitz Report DFF Display 53B31EFFC6B85470E0630100007F501D Y select 'V' id, 'Value' value, 'Value only' description from dual union all select 'C' id, 'Value: Description' value, 'Concatenated value and description' description from dual union all select 'S' id, 'Value, Description' value, 'Value and description in separate columns' description from dual coalesce(fnd_profile.value('XXEN_REPORT_DFF_DISPLAY'),'V') US Show DFF Attributes Shows the descriptive flexfield attributes of the ECO and the revised item. 15 &revised_item_dff_columns select xxen_util.dff_columns(p_table_name=>'eng_revised_items',p_table_alias=>'eri',p_row_id=>'rowid',p_column_name_prefix=>'Revised Item: ',p_display_mode=>:dff_display) sql_text from dual US Show DFF Attributes D821A539570B47EDA1812203C4313431 ECO Component Changes Component additions, quantity and operation changes and disables per ECO revised item, with old and new quantity and effectivity. SYSADMIN 7 ACTION 2 CHANGE_NOTICE 3 CHANGE_STATUS 10 COMPONENT 11 COMPONENT_DESCRIPTION 16 COMPONENT_DISABLE_DATE 15 COMPONENT_EFFECTIVITY_DATE 17 COMPONENT_IMPLEMENTATION_DATE 12 COMPONENT_UOM 6 EFFECTIVITY_DATE 14 NEW_QUANTITY 5 NEW_REVISION 9 OLD_OP_SEQ 13 OLD_QUANTITY 8 OP_SEQ 1 ORGANIZATION_CODE 4 REVISED_ITEM Show Component Changes Y S Site DC95A0CBCCB041CEB2CE796486761532 Open ECOs Open engineering change orders with their revised items, new revision and effectivity date, excluding implemented and cancelled ECOs. SYSADMIN 5 APPROVAL_STATUS 2 CHANGE_NOTICE 2 4 CHANGE_STATUS 3 CHANGE_TYPE 13 EFFECTIVITY_DATE 8 NEED_BY_DATE 11 NEW_REVISION 14 OPEN_JOBS 1 ORGANIZATION_CODE 1 6 PRIORITY 7 REQUESTOR 9 REVISED_ITEM 3 10 REVISED_ITEM_DESCRIPTION 12 REVISED_ITEM_STATUS Open Only Y S Site