ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 98A62D535C3940AF8BCA8C39DFAF3036Y select mp.organization_code, mtrh.request_number move_order, mtrh.description move_order_description, xxen_util.meaning(mtrh.move_order_type,'MOVE_ORDER_TYPE',700) move_order_type, mtt.transaction_type_name transaction_type, xxen_util.meaning(mtrh.header_status,'MTL_TXN_REQUEST_STATUS',700) header_status, mtrl.line_number line, xxen_util.meaning(mtrl.line_status,'MTL_TXN_REQUEST_STATUS',700) line_status, mtrl.status_date line_status_date, msiv.concatenated_segments item, msiv.description item_description, mtrl.revision, mtrl.lot_number, mtrl.serial_number_start serial_number_from, mtrl.serial_number_end serial_number_to, mtrl.uom_code uom, mtrl.quantity, mtrl.quantity_detailed allocated_quantity, mtrl.quantity_delivered delivered_quantity, case when mtrl.line_status in (1,2,3,7,8) then mtrl.quantity-nvl(mtrl.quantity_detailed,0) end unallocated_quantity, case when mtrl.line_status in (1,2,3,7,8) then mtrl.quantity-nvl(mtrl.quantity_delivered,0) end open_quantity, mtrl.from_subinventory_code from_subinventory, (select milk.concatenated_segments from mtl_item_locations_kfv milk where mtrl.from_locator_id=milk.inventory_location_id and mtrl.organization_id=milk.organization_id) from_locator, mtrl.to_subinventory_code to_subinventory, (select milk.concatenated_segments from mtl_item_locations_kfv milk where mtrl.to_locator_id=milk.inventory_location_id and nvl(mtrl.to_organization_id,mtrl.organization_id)=milk.organization_id) to_locator, (select gcck.concatenated_segments from gl_code_combinations_kfv gcck where mtrl.to_account_id=gcck.code_combination_id) to_account, mtrl.date_required, mtst.transaction_source_type_name source_type, case when mtrl.transaction_source_type_id in (2,8,12) then coalesce(to_char(ooha.order_number),mso.segment1) when mtrl.transaction_source_type_id=5 then we.wip_entity_name when mtrl.transaction_source_type_id=1 then pha.segment1 when mtrl.transaction_source_type_id=7 then prha.segment1 end source, case when mtrl.transaction_source_type_id in (2,8,12) then oola.line_number||'.'||oola.shipment_number when mtrl.transaction_source_type_id=1 then pla.line_num||'.'||plla.shipment_num when mtrl.transaction_source_type_id=7 then to_char(prla.line_num) end source_line, case when mtrh.move_order_type=5 then mtrl.txn_source_line_id end operation_seq, (select wnd.name from wsh_delivery_assignments wda, wsh_new_deliveries wnd where mtrl.txn_source_line_detail_id=wda.delivery_detail_id and wda.delivery_id=wnd.delivery_id) delivery, hp.party_name customer, xxen_util.meaning(mtrl.reference_type_code,'MTL_TXN_REQUEST_SOURCE',700) reference_source, mkc.kanban_card_number kanban_card, mtr.reason_name reason, mtrl.reference, ppa.segment1 project, pt.task_number task, mtrl.pick_slip_number, &allocation_columns &dff_columns xxen_util.user_name(mtrl.created_by) created_by, xxen_util.client_time(mtrl.creation_date) creation_date, xxen_util.user_name(mtrl.last_updated_by) last_updated_by, xxen_util.client_time(mtrl.last_update_date) last_update_date from mtl_parameters mp, mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl, mtl_system_items_vl msiv, mtl_transaction_types mtt, mtl_txn_source_types mtst, mtl_sales_orders mso, oe_order_lines_all oola, oe_order_headers_all ooha, hz_cust_accounts hca, hz_parties hp, wip_entities we, po_line_locations_all plla, po_lines_all pla, po_headers_all pha, rcv_shipment_lines rsl, po_requisition_lines_all prla, po_requisition_headers_all prha, mtl_kanban_cards mkc, mtl_transaction_reasons mtr, pa_projects_all ppa, pa_tasks pt, (select mmtt.* from mtl_material_transactions_temp mmtt where '&show_allocations'='Y') mmtt 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=mtrl.organization_id and mtrl.header_id=mtrh.header_id and mtrl.organization_id=msiv.organization_id and mtrl.inventory_item_id=msiv.inventory_item_id and mtrl.transaction_type_id=mtt.transaction_type_id(+) and mtrl.transaction_source_type_id=mtst.transaction_source_type_id(+) and case when mtrl.transaction_source_type_id in (2,8) then mtrl.txn_source_id end=mso.sales_order_id(+) and case when mtrl.transaction_source_type_id in (2,8) then mtrl.txn_source_line_id when mtrl.transaction_source_type_id=12 and mtrl.reference='ORDER_LINE_ID' then mtrl.reference_id end=oola.line_id(+) and oola.header_id=ooha.header_id(+) and ooha.sold_to_org_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) and case when mtrl.transaction_source_type_id=5 then mtrl.txn_source_id end=we.wip_entity_id(+) and case when mtrl.reference='PO_LINE_LOCATION_ID' then mtrl.reference_id end=plla.line_location_id(+) and plla.po_line_id=pla.po_line_id(+) and plla.po_header_id=pha.po_header_id(+) and case when mtrl.reference='SHIPMENT_LINE_ID' then mtrl.reference_id end=rsl.shipment_line_id(+) and rsl.requisition_line_id=prla.requisition_line_id(+) and prla.requisition_header_id=prha.requisition_header_id(+) and case when mtrl.reference_type_code=1 then mtrl.reference_id end=mkc.kanban_card_id(+) and mtrl.reason_id=mtr.reason_id(+) and mtrl.project_id=ppa.project_id(+) and mtrl.task_id=pt.task_id(+) and mtrl.line_id=mmtt.move_order_line_id(+) order by mp.organization_code, mtrh.request_number, mtrl.line_number New report US INV Move Orders Move order lines of all move order types (requisition, replenishment, pick wave, manufacturing component pick, put away), one row per line, with requested, allocated (detailed), delivered, unallocated and open quantity. Unallocated and open quantity are shown for open lines only. Source shows the sales order, WIP job, purchase order or internal requisition the line was created for; Reference Source and Kanban Card identify replenishment move orders created by kanban, min-max or replenishment counts. Show Allocations lists the pending allocations (subinventory, locator and lot) of each line, repeating the line quantities on each allocation row. Enginatics &allocation_columns &dff_columns &show_allocations 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') Y US Organization Code 2 20 1=1 mtrh.move_order_type=:move_order_type_id LOV custom Y select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='MOVE_ORDER_TYPE' and flvv.view_application_id=700 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) US Move Order Type 3 30 1=1 mtrh.request_number=:move_order Char US Move Order 4 40 1=1 mtrl.line_status=:line_status_id LOV custom Y select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='MTL_TXN_REQUEST_STATUS' and flvv.view_application_id=700 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) US Line Status 5 50 1=1 mtrl.line_status in (1,2,3,7,8) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Open Lines Only Line status Incomplete, Pending Approval, Approved, Pre Approved or Partially Approved. 6 60 1=1 mtrl.quantity>nvl(mtrl.quantity_detailed,0) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Unallocated Only Lines with a quantity not yet allocated (detailed) to a source subinventory and locator. 7 70 1=1 msiv.concatenated_segments=: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 Item 8 80 1=1 mtrl.from_subinventory_code=:from_subinventory LOV INV Subinventory 8E2FF36EDF2A79D2E0530100007F1FF2 Y select msi.secondary_inventory_name value, msi.description||' ('||mp.organization_code||')' description from mtl_parameters mp, mtl_secondary_inventories msi where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msi.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 msi.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' or xxen_util.contains(:$flex$.organization_code,mp.organization_id)='Y' ) and mp.organization_id=msi.organization_id order by mp.organization_code, msi.secondary_inventory_name US From Subinventory 9 90 1=1 mtrl.to_subinventory_code=:to_subinventory LOV INV Subinventory 8E2FF36EDF2A79D2E0530100007F1FF2 Y select msi.secondary_inventory_name value, msi.description||' ('||mp.organization_code||')' description from mtl_parameters mp, mtl_secondary_inventories msi where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msi.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 msi.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' or xxen_util.contains(:$flex$.organization_code,mp.organization_id)='Y' ) and mp.organization_id=msi.organization_id order by mp.organization_code, msi.secondary_inventory_name US To Subinventory 10 100 1=1 mtrl.date_required>=:date_required_from Date US Date Required From 11 110 1=1 mtrl.date_required<:date_required_to+1 Date last_day(:$flex$.date_required_from) US Date Required To 12 120 1=1 mtrl.creation_date>=:creation_date_from Date US Creation Date From 13 130 1=1 mtrl.creation_date<:creation_date_to+1 Date last_day(:$flex$.creation_date_from) US Creation Date To 14 140 1=1 mtrl.created_by=xxen_util.user_id(:created_by) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US Created By 15 150 &show_allocations Y LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Allocations One row per allocation (pending transaction) of a line. Line quantities repeat on each allocation row. 16 &allocation_columns mmtt.subinventory_code allocation_subinventory, (select milk.concatenated_segments from mtl_item_locations_kfv milk where mmtt.locator_id=milk.inventory_location_id and mmtt.organization_id=milk.organization_id) allocation_locator, (select listagg(mtlt.lot_number,', ') within group (order by mtlt.lot_number) from mtl_transaction_lots_temp mtlt where mmtt.transaction_temp_id=mtlt.transaction_temp_id) allocation_lot, mmtt.transaction_quantity allocation_quantity, mmtt.transaction_uom allocation_uom, US Show Allocations 17 160 &dff_columns select xxen_util.dff_columns(p_table_name=>'mtl_txn_request_headers',p_table_alias=>'mtrh',p_column_name_prefix=>'Header: ',p_display_mode=>:dff_display)||xxen_util.dff_columns(p_table_name=>'mtl_txn_request_lines',p_table_alias=>'mtrl',p_column_name_prefix=>'Line: ',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 fnd_profile.value('XXEN_REPORT_DFF_DISPLAY') US DFF Display 9C997F581DF846399B29E16326900FB9 Open Move Order Lines Y Open move order lines of all types with requested, allocated and delivered quantities and their source document. ENGINATICS 3 LINE 3 2 MOVE_ORDER 2 1 ORGANIZATION_CODE 1 Open Lines Only Y S Site A60BF179D1004A94B6A39C5A958D1CEE Replenishment Move Orders Y Open replenishment move orders created by min-max, kanban or replenishment counts, with their reference source. ENGINATICS 3 LINE 3 2 MOVE_ORDER 2 1 ORGANIZATION_CODE 1 Move Order Type 2 Open Lines Only Y S Site C662BDC1F9E24DED8A012EDB5BBC1F48 Unallocated Move Order Lines Y Open move order lines with a quantity not yet allocated to a source subinventory, e.g. pick wave lines that failed allocation. ENGINATICS 3 LINE 3 2 MOVE_ORDER 2 1 ORGANIZATION_CODE 1 Open Lines Only Y Unallocated Only Y S Site 4B51CCBDC8164A88B6EC238BF72F56A6 WIP Component Pick Move Orders Y Open manufacturing component pick move orders with job and operation. ENGINATICS 3 LINE 3 2 MOVE_ORDER 2 1 ORGANIZATION_CODE 1 Move Order Type 5 Open Lines Only Y S Site