ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN DAA1CB6DA7F44D6FBFBD72122C920A3EY select x.organization_code, x.item, x.item_description, x.serial_number, xxen_util.meaning(x.current_status,'SERIAL_NUM_STATUS',700) current_status, mmsv.status_code material_status, x.revision, x.lot_number, x.current_subinventory_code subinventory, (select milk.concatenated_segments from mtl_item_locations_kfv milk where x.current_locator_id=milk.inventory_location_id and x.current_organization_id=milk.organization_id) locator, wlpn.license_plate_number, x.initialization_date, x.completion_date, x.ship_date, mtst.transaction_source_type_name last_source_type, case when x.last_txn_source_type_id in (2,8,12) then mso.segment1 when x.last_txn_source_type_id=5 then we.wip_entity_name when x.last_txn_source_type_id=1 then pha.segment1 when x.last_txn_source_type_id=7 then prha.segment1 when x.last_txn_source_type_id=4 then mtrh.request_number else x.last_txn_source_name end last_source, mtt.transaction_type_name last_transaction_type, mmt.transaction_date last_transaction_date, hp.party_name customer, hca.account_number customer_number, we_o.wip_entity_name original_job, aps.vendor_name supplier, x.vendor_serial_number supplier_serial_number, x.vendor_lot_number supplier_lot_number, msiv_p.concatenated_segments parent_item, msn_p.serial_number parent_serial_number, &dff_columns xxen_util.user_name(x.created_by) created_by, x.creation_date, xxen_util.user_name(x.last_updated_by) last_updated_by, x.last_update_date from ( select mp.organization_code, msiv.concatenated_segments item, msiv.description item_description, msn.rowid row_id, msn.*, (select max(mog.parent_object_id) from mtl_object_genealogy mog where msn.gen_object_id=mog.object_id and mog.parent_object_type=2 and mog.end_date_active is null) parent_gen_object_id, case when msn.last_txn_source_type_id in (2,8) then ( select max(ooha.header_id) from mtl_sales_orders mso, oe_order_headers_all ooha where msn.last_txn_source_id=mso.sales_order_id and case when mso.segment3=fnd_profile.value('ONT_SOURCE_CODE') then to_number(mso.segment1) end=ooha.order_number and ooha.order_type_id in (select ottt.transaction_type_id from oe_transaction_types_tl ottt where mso.segment2=ottt.name) ) end order_header_id from mtl_parameters mp, mtl_serial_numbers msn, mtl_system_items_vl msiv 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=msn.current_organization_id and msn.current_organization_id=msiv.organization_id and msn.inventory_item_id=msiv.inventory_item_id ) x, mtl_material_statuses_vl mmsv, wms_license_plate_numbers wlpn, mtl_txn_source_types mtst, mtl_sales_orders mso, wip_entities we, po_headers_all pha, po_requisition_headers_all prha, mtl_txn_request_headers mtrh, mtl_material_transactions mmt, mtl_transaction_types mtt, oe_order_headers_all ooha, hz_cust_accounts hca, hz_parties hp, wip_entities we_o, ap_suppliers aps, mtl_serial_numbers msn_p, mtl_system_items_vl msiv_p where 2=2 and x.status_id=mmsv.status_id(+) and x.lpn_id=wlpn.lpn_id(+) and x.last_txn_source_type_id=mtst.transaction_source_type_id(+) and case when x.last_txn_source_type_id in (2,8,12) then x.last_txn_source_id end=mso.sales_order_id(+) and case when x.last_txn_source_type_id=5 then x.last_txn_source_id end=we.wip_entity_id(+) and case when x.last_txn_source_type_id=1 then x.last_txn_source_id end=pha.po_header_id(+) and case when x.last_txn_source_type_id=7 then x.last_txn_source_id end=prha.requisition_header_id(+) and case when x.last_txn_source_type_id=4 then x.last_txn_source_id end=mtrh.header_id(+) and x.last_transaction_id=mmt.transaction_id(+) and mmt.transaction_type_id=mtt.transaction_type_id(+) and x.order_header_id=ooha.header_id(+) and ooha.sold_to_org_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) and x.original_wip_entity_id=we_o.wip_entity_id(+) and x.original_unit_vendor_id=aps.vendor_id(+) and x.parent_gen_object_id=msn_p.gen_object_id(+) and msn_p.inventory_item_id=msiv_p.inventory_item_id(+) and msn_p.current_organization_id=msiv_p.organization_id(+) order by x.organization_code, x.item, x.serial_number New report :Organization_Code is not null or :Item is not null or :Serial_Number_like is not null US INV Serial Numbers Serial numbers in any status (defined, in stores, issued out of stores, in transit, in receiving, in WIP), one row per serial number, with current organization, subinventory and locator, the last transaction source, the customer of a serial issued on a sales order and the genealogy parent. Organization is the current organization; for an issued serial it is the organization that issued it. Customer is the sold-to customer of the sales order of the last transaction, blank when the order no longer exists. Last Transaction Type and Date are blank where Oracle did not record the last transaction on the serial. Use INV Transaction Register (Serial Number Detail) for the full transaction history of a serial. Enginatics &dff_columns 1=1 2=2 1 10 1=1 mp.organization_code=:organization_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) 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 and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) 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 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 3 30 1=1 msn.serial_number like :serial_number Char US Serial Number like Allows wildcards, e.g. SN10% 4 40 1=1 msn.current_status=:current_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='SERIAL_NUM_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 Current Status 5 50 1=1 msn.current_subinventory_code=: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 Subinventory 6 60 1=1 msn.lot_number=:lot_number LOV INV Lot Number 2450E07B1B9F64E9E0630100007FCFA7 Y select distinct mln.lot_number value, mln.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_id = msiv.organization_id and mln.organization_id = msiv.organization_id and mln.inventory_item_id = msiv.inventory_item_id and (:$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 (:$flex$.item is null or xxen_util.contains(:$flex$.item,msiv.concatenated_segments) = 'Y') and (:$flex$.item_from is null or msiv.concatenated_segments >= :$flex$.item_from) and (:$flex$.item_to is null or msiv.concatenated_segments <= :$flex$.item_to) order by value, description US Lot Number 7 70 1=1 msn.last_txn_source_type_id=:last_source_type_id LOV custom Y select mtst.transaction_source_type_id id, mtst.transaction_source_type_name value, mtst.description from mtl_txn_source_types mtst order by mtst.transaction_source_type_name US Last Source Type 8 80 2=2 mso.segment1=:sales_order Char US Sales Order Sales order of the last transaction, e.g. the order a serial was shipped on. 9 90 2=2 hp.party_name=:customer LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name US Customer 10 100 1=1 msn.ship_date>=:ship_date_from Date US Ship Date From 11 110 1=1 msn.ship_date<:ship_date_to+1 Date last_day(:$flex$.ship_date_from) US Ship Date To 12 120 &dff_columns select xxen_util.dff_columns(p_table_name=>'mtl_serial_numbers',p_table_alias=>'x',p_row_id=>'row_id',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 FA2B1DE7DD3744309F4FD4DD569C856B Serials Shipped to Customers Serial numbers issued out of stores on a sales order, with ship date, sales order and customer. ENGINATICS 8 CUSTOMER 9 CUSTOMER_NUMBER 2 ITEM 2 3 ITEM_DESCRIPTION 7 LAST_SOURCE 10 LAST_TRANSACTION_DATE 5 LOT_NUMBER 1 ORGANIZATION_CODE 1 11 PARENT_ITEM 12 PARENT_SERIAL_NUMBER 4 SERIAL_NUMBER 4 6 SHIP_DATE -3 Current Status 4 Last Source Type 2 S Site 0C3CFE23F5CD4FF1A5910476791615CD Serials in Transit Serial numbers residing in intransit between organizations, with ship date and the shipping organization. ENGINATICS 2 ITEM 2 3 ITEM_DESCRIPTION 8 LAST_SOURCE 7 LAST_SOURCE_TYPE 10 LAST_TRANSACTION_DATE 9 LAST_TRANSACTION_TYPE 5 LOT_NUMBER 1 ORGANIZATION_CODE 1 4 SERIAL_NUMBER 3 6 SHIP_DATE Current Status 5 S Site