ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E295444A821940368FB548C55EB351C2Y select (select hou.name from hr_operating_units hou where hoi.org_information3=to_char(hou.organization_id)) operating_unit, mp.organization_code, rsh.receipt_num receipt_number, rsl.line_num receipt_line, xxen_util.client_time(rt.transaction_date) transaction_date, xxen_util.meaning(rt.transaction_type,'RCV TRANSACTION TYPE',201) transaction_type, (select xxen_util.meaning(rt_par.transaction_type,'RCV TRANSACTION TYPE',201) from rcv_transactions rt_par where rt.parent_transaction_id=rt_par.transaction_id) parent_transaction_type, xxen_util.meaning(rt.source_document_code,'SHIPMENT SOURCE DOCUMENT TYPE',201) source_document, xxen_util.meaning(rsh.receipt_source_code,'SHIPMENT SOURCE TYPE',201) receipt_source, case rt.source_document_code when 'PO' then pha.segment1 when 'REQ' then (select prha.segment1 from po_requisition_lines_all prla, po_requisition_headers_all prha where rt.requisition_line_id=prla.requisition_line_id and prla.requisition_header_id=prha.requisition_header_id) when 'RMA' then (select to_char(ooha.order_number) from oe_order_headers_all ooha where rt.oe_order_header_id=ooha.header_id) when 'INVENTORY' then rsh.shipment_num end document_number, (select pra.release_num from po_releases_all pra where rt.po_release_id=pra.po_release_id) release_number, case rt.source_document_code when 'PO' then (select to_char(pla.line_num) from po_lines_all pla where rt.po_line_id=pla.po_line_id) when 'REQ' then (select to_char(prla.line_num) from po_requisition_lines_all prla where rt.requisition_line_id=prla.requisition_line_id) when 'RMA' then (select oola.line_number||'.'||oola.shipment_number from oe_order_lines_all oola where rt.oe_order_line_id=oola.line_id) end document_line, plla.shipment_num po_shipment_number, aps.vendor_name supplier, aps.segment1 supplier_number, (select assa.vendor_site_code from ap_supplier_sites_all assa where rt.vendor_site_id=assa.vendor_site_id) supplier_site, (select hp.party_name from hz_cust_accounts hca, hz_parties hp where rt.customer_id=hca.cust_account_id and hca.party_id=hp.party_id) customer, (select mp_from.organization_code from mtl_parameters mp_from where rsl.from_organization_id=mp_from.organization_id) source_organization, msiv.concatenated_segments item, nvl(msiv.description,rsl.item_description) item_description, rt.unit_of_measure uom, nvl(rls.quantity,rt.quantity) quantity, rt.primary_unit_of_measure primary_uom, nvl(rls.primary_quantity,rt.primary_quantity) primary_quantity, rt.currency_code currency, rt.po_unit_price, nvl(rls.quantity,rt.quantity)*rt.po_unit_price amount, xxen_util.meaning(rt.destination_type_code,'RCV DESTINATION TYPE',201) destination_type, rt.subinventory, (select milk.concatenated_segments from mtl_item_locations_kfv milk where rt.locator_id=milk.inventory_location_id and rt.organization_id=milk.organization_id) locator, (select hla.location_code from hr_locations_all hla where rt.location_id=hla.location_id) receiving_location, (select hla.location_code from hr_locations_all hla where rt.deliver_to_location_id=hla.location_id) deliver_to_location, (select max(papf.full_name) keep (dense_rank last order by papf.effective_start_date) from per_all_people_f papf where rt.deliver_to_person_id=papf.person_id) deliver_to_person, (select max(papf.full_name) keep (dense_rank last order by papf.effective_start_date) from per_all_people_f papf where rt.employee_id=papf.person_id) receiver, rrh.routing_name routing, xxen_util.meaning(rt.inspection_status_code,'INSPECTION STATUS',201) inspection_status, rt.inspection_quality_code inspection_quality, (select mtr.reason_name from mtl_transaction_reasons mtr where rt.reason_id=mtr.reason_id) reason, rs.quantity quantity_in_receiving, case when rs.rcv_transaction_id is not null then trunc(sysdate)-trunc(nvl(rs.receipt_date,rt.transaction_date)) end days_in_receiving, &lot_serial_columns rt.vendor_lot_num supplier_lot, rt.rma_reference, nvl(rsl.packing_slip,rsh.packing_slip) packing_slip, decode(rsh.receipt_source_code,'VENDOR',rsh.shipment_num) asn_number, rsh.bill_of_lading, rsh.waybill_airbill_num waybill, rsh.freight_carrier_code freight_carrier, plla.need_by_date, plla.promised_date, rt.comments, xxen_util.user_name(rt.created_by) created_by, xxen_util.client_time(rt.creation_date) creation_date, rt.transaction_id from rcv_transactions rt, mtl_parameters mp, hr_organization_information hoi, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_headers_all pha, po_line_locations_all plla, ap_suppliers aps, mtl_system_items_vl msiv, rcv_routing_headers rrh, rcv_supply rs, ( select rlt.transaction_id, rlt.lot_num, rlt.expiration_date lot_expiration_date, to_char(null) serial_num, rlt.quantity, rlt.primary_quantity from rcv_lot_transactions rlt where '&show_lot_serial'='Y' and rlt.lot_transaction_type='TRANSACTION' and not exists (select null from rcv_serial_transactions rst where rlt.transaction_id=rst.transaction_id and rst.serial_transaction_type='TRANSACTION') union all select rst.transaction_id, rst.lot_num, (select max(rlt.expiration_date) from rcv_lot_transactions rlt where rst.transaction_id=rlt.transaction_id and rst.lot_num=rlt.lot_num and rlt.lot_transaction_type='TRANSACTION') lot_expiration_date, rst.serial_num, 1 quantity, 1 primary_quantity from rcv_serial_transactions rst where '&show_lot_serial'='Y' and rst.serial_transaction_type='TRANSACTION' ) rls where 1=1 and rt.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 rt.organization_id=mp.organization_id and rt.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and rt.shipment_header_id=rsh.shipment_header_id and rt.shipment_line_id=rsl.shipment_line_id and rt.po_header_id=pha.po_header_id(+) and rt.po_line_location_id=plla.line_location_id(+) and rt.vendor_id=aps.vendor_id(+) and rsl.item_id=msiv.inventory_item_id(+) and rsl.to_organization_id=msiv.organization_id(+) and rt.routing_header_id=rrh.routing_header_id(+) and rt.transaction_id=rs.rcv_transaction_id(+) and rs.supply_type_code(+)='RECEIVING' and rt.transaction_id=rls.transaction_id(+) order by mp.organization_code, rt.transaction_date, rt.transaction_id, rls.lot_num, rls.serial_num New report. :Transaction_Date_From is not null or :In_Receiving_Only is not null or :Receipt_Number is not null or :PO_Number is not null US PO Receiving Transactions Receiving transactions register: one row per receiving transaction (receive, accept, reject, transfer, deliver, return to supplier or receiving, correction) for purchase orders, internal requisitions, RMAs and inter-organization shipments, with the source document, item, quantity and PO price. Quantity In Receiving and Days In Receiving are current values from the receiving supply, populated only on the receive, accept, reject or transfer transaction whose quantity still sits in receiving. Days In Receiving counts from the receipt date. Amount is quantity times PO unit price and is blank for sources without a PO price. Please specify a Transaction Date From, Receipt Number or PO Number, or set In Receiving Only. Enginatics &lot_serial_columns &show_lot_serial 1=1 1 10 1=1 hoi.org_information3 in (select to_char(hou.organization_id) from hr_operating_units hou where hou.name=:operating_unit) LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name US Operating Unit 2 20 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 3 30 1=1 rt.transaction_date>=:transaction_date_from Date US Transaction Date From 4 40 1=1 rt.transaction_date<:transaction_date_to+1 Date last_day(:$flex$.transaction_date_from) US Transaction Date To 5 50 1=1 rt.transaction_type=xxen_util.lookup_code(:transaction_type,'RCV TRANSACTION TYPE',201) LOV RCV Transaction Type E7C3CD67B84B2AEFE0530100007FA2E5 Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='RCV TRANSACTION TYPE' and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning, flvv.description US Transaction Type 6 60 1=1 rt.source_document_code=xxen_util.lookup_code(:source_document,'SHIPMENT SOURCE DOCUMENT TYPE',201) LOV custom Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='SHIPMENT SOURCE DOCUMENT TYPE' and flvv.view_application_id=201 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning US Source Document 7 70 1=1 rsh.receipt_num=:receipt_number Char US Receipt Number 8 80 1=1 pha.segment1=:po_number LOV PO Number 8E2FF36EDEED79D2E0530100007F1FF2 Y Y select pha.segment1 value, xxen_util.meaning(pha.type_lookup_code,'PO TYPE',201)||nvl2(aps.vendor_name,': '||aps.vendor_name,null) description from po_headers_all pha, ap_suppliers aps where (:$flex$.operating_unit is null or pha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') ) and pha.vendor_id=aps.vendor_id(+) order by pha.creation_date desc US PO Number 9 90 1=1 aps.vendor_name=:supplier LOV AP Supplier B9847D20A0E4742FE0538931640A6379 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id US Supplier 10 100 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 11 110 1=1 rrh.routing_name=:routing LOV RCV Routing Headers 018562D2DB3BE2CFE0630100007F48D9 Y select rrh.routing_name value, rrh.description description from rcv_routing_headers rrh order by value US Routing 12 120 1=1 rt.transaction_id in (select rs2.rcv_transaction_id from rcv_supply rs2 where rs2.supply_type_code='RECEIVING') LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US In Receiving Only Show only receiving transactions whose quantity is still in receiving, awaiting inspection or delivery. 13 130 &show_lot_serial Y LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Lot and Serial One row per lot and serial number. Quantity and Amount then show the lot quantity, or one per serial number. 14 &lot_serial_columns rls.lot_num lot_number, rls.lot_expiration_date, rls.serial_num serial_number, US Show Lot and Serial 55304EF3CDD74CA18032ED3822E13A8D Inspection Results by Supplier Inspection results by supplier: accepted and rejected quantities per supplier and item, with a pivot of supplier by result. SYSADMIN 15 COMMENTS 9 DOCUMENT_NUMBER 12 INSPECTION_QUALITY 4 ITEM 2 5 ITEM_DESCRIPTION 1 ORGANIZATION_CODE 10 QUANTITY 13 REASON 8 RECEIPT_NUMBER 2 SUPPLIER 1 14 SUPPLIER_LOT 3 SUPPLIER_NUMBER 7 TRANSACTION_DATE 3 6 TRANSACTION_TYPE 11 UOM ORGANIZATION_CODE FILTER 4 QUANTITY VALUE 3 SUM SUPPLIER ROW 1 TRANSACTION_TYPE COLUMN 2 Transaction Type <multiple_values>Accept;Reject S Site E0612CC498C3492F9474D040EFE5B321 Material in Receiving Material currently in receiving, received but not yet delivered, longest in receiving first. SYSADMIN 18 CURRENCY 11 CUSTOMER 5 DAYS_IN_RECEIVING -1 22 DESTINATION_TYPE 9 DOCUMENT_LINE 7 DOCUMENT_NUMBER 20 INSPECTION_STATUS 13 ITEM 14 ITEM_DESCRIPTION 23 NEED_BY_DATE 1 ORGANIZATION_CODE 17 PO_UNIT_PRICE 15 QUANTITY_IN_RECEIVING 2 RECEIPT_NUMBER 21 RECEIVING_LOCATION 8 RELEASE_NUMBER 19 ROUTING 6 SOURCE_DOCUMENT 12 SOURCE_ORGANIZATION 10 SUPPLIER 3 TRANSACTION_DATE 4 TRANSACTION_TYPE 16 UOM In Receiving Only Y S Site 7E4A444796CD47498B8A50FD63961A21 Receipts Awaiting Inspection Receipts on inspection required routing that are still awaiting inspection, oldest need-by date first, for the inspection priority list. SYSADMIN 4 DAYS_IN_RECEIVING -2 15 DELIVER_TO_PERSON 9 DOCUMENT_LINE 7 DOCUMENT_NUMBER 16 INSPECTION_STATUS 10 ITEM 11 ITEM_DESCRIPTION 5 NEED_BY_DATE 1 1 ORGANIZATION_CODE 12 QUANTITY_IN_RECEIVING 2 RECEIPT_NUMBER 14 RECEIVING_LOCATION 8 RELEASE_NUMBER 6 SUPPLIER 3 TRANSACTION_DATE 13 UOM In Receiving Only Y Routing Inspection Required Transaction Type Receive S Site C29C851303B945FB8B81FA106387DE81 Receiving Transactions Register Receiving transactions register: every receipt, inspection, transfer, delivery, return and correction for a date range, with source document, item, quantity and amount. SYSADMIN 21 AMOUNT 31 COMMENTS 22 CURRENCY 14 CUSTOMER 23 DESTINATION_TYPE 11 DOCUMENT_LINE 9 DOCUMENT_NUMBER 28 INSPECTION_STATUS 16 ITEM 17 ITEM_DESCRIPTION 25 LOCATOR 1 OPERATING_UNIT 2 ORGANIZATION_CODE 30 PACKING_SLIP 7 PARENT_TRANSACTION_TYPE 20 PO_UNIT_PRICE 19 QUANTITY 29 REASON 4 RECEIPT_LINE 3 RECEIPT_NUMBER 2 26 RECEIVER 10 RELEASE_NUMBER 27 ROUTING 8 SOURCE_DOCUMENT 15 SOURCE_ORGANIZATION 24 SUBINVENTORY 12 SUPPLIER 13 SUPPLIER_SITE 5 TRANSACTION_DATE 1 6 TRANSACTION_TYPE 18 UOM S Site