ECC Inventory Management, Inbound Details (1)

Description

Imported from Enterprise Command Center
Description: Inventory receiving details
Dataset Key: Inbound_Details
Query Procedure: INV_ECC_INBOUND_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: inv_ecc_datasecurity_pkg_pub.GetFilterAttributeValues

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

select
x.*
from
(
 
SELECT * FROM (
SELECT
  reiv.ecc_spec_id AS receiving_id,
  reiv.ecc_spec_id,
  reiv.trx_type as trx_type_code,
  reiv.priority_flag as priority_flag_code,
  reiv.shipment_line_id,
  reiv.organization_code,
  reiv.expected_trx_date,
  (SELECT meaning FROM fnd_lookup_values WHERE lookup_code = reiv.source_doc_type AND lookup_type = 'DOC_TYPE' AND view_application_id=3 AND language = msit.language) source_doc_type,
  (SELECT meaning FROM fnd_lookup_values WHERE lookup_code = reiv.trx_type AND lookup_type = 'INV_RCV_ECC_TRX_TYPES' AND view_application_id=3 AND language = msit.language) trx_type,
  (SELECT meaning FROM fnd_lookup_values WHERE lookup_code = reiv.priority_flag AND lookup_type = 'YES_NO' AND view_application_id=3 AND language = msit.language) priority_flag,
  reiv.parent_trx_type,
  decode(reiv.source_doc_type,'INTSHIP',(SELECT haou.name FROM MTL_PARAMETERS MP, HR_ALL_ORGANIZATION_UNITS_TL HAOU WHERE mp.organization_code = reiv.source_name AND HAOU.organization_id = mp.organization_id AND language = msit.language),
         'REQ',(SELECT haou.name FROM MTL_PARAMETERS MP, HR_ALL_ORGANIZATION_UNITS_TL HAOU WHERE mp.organization_code = reiv.source_name AND HAOU.organization_id = mp.organization_id AND language = msit.language),reiv.source_name) source_name,
  reiv.document_num,
  reiv.document_line_num,
  reiv.item_number,
  msit.description item_description,
  reiv.ordered_qty,
  reiv.primary_qty,
  reiv.trx_qty,
  reiv.amount,
  reiv.routing_id,
  reiv.to_organization_id,
  reiv.item_id,
  reiv.lpn_id,
  reiv.po_header_id,
  reiv.oe_order_header_id,
  reiv.license_plate_number,
  reiv.gross_weight,
  reiv.container_volume,
  reiv.content_volume,
  msit.LANGUAGE LANGUAGE,
  reiv.dock_to_stock,
  nvl2(reiv.lpn_id, 'N', 'Y')as enable_action,
  reiv.deliver_date  FROM rcv_ecc_inbound_v reiv,
     mtl_system_items_tl msit WHERE reiv.item_id = msit.inventory_item_id
AND   reiv.to_organization_id = msit.organization_id AND msit.LANGUAGE in ( 'US')) PIVOT ( Max(source_doc_type) AS source_doc_type,
          Max(source_name) AS source_name,
          Max(trx_type) AS trx_type,
          Max(priority_flag) AS priority_flag,
          Max(item_description) AS item_description for LANGUAGE in ( 'US' "US"))
) x
where
2=2

select x.* from ( SELECT * FROM ( SELECT reiv.ecc_spec_id AS receiving_id, reiv.ecc_spec_id, reiv.trx_type as trx_type_code, reiv.priority_flag as priority_flag_code, reiv.shipment_line_id, reiv.organization_code, reiv.expected_trx_date, (SELECT meaning FROM fnd_lookup_values WHERE lookup_code = reiv.source_doc_type AND lookup_type = 'DOC_TYPE' AND view_application_id=3 AND language = msit.language) source_doc_type, (SELECT meaning FROM fnd_lookup_values WHERE lookup_code = reiv.trx_type AND lookup_type = 'INV_RCV_ECC_TRX_TYPES' AND view_application_id=3 AND language = msit.language) trx_type, (SELECT meaning FROM fnd_lookup_values WHERE lookup_code = reiv.priority_flag AND lookup_type = 'YES_NO' AND view_application_id=3 AND language = msit.language) priority_flag, reiv.parent_trx_type, decode(reiv.source_doc_type,'INTSHIP',(SELECT haou.name FROM MTL_PARAMETERS MP, HR_ALL_ORGANIZATION_UNITS_TL HAOU WHERE mp.organization_code = reiv.source_name AND HAOU.organization_id = mp.organization_id AND language = msit.language), 'REQ',(SELECT haou.name FROM MTL_PARAMETERS MP, HR_ALL_ORGANIZATION_UNITS_TL HAOU WHERE mp.organization_code = reiv.source_name AND HAOU.organization_id = mp.organization_id AND language = msit.language),reiv.source_name) source_name, reiv.document_num, reiv.document_line_num, reiv.item_number, msit.description item_description, reiv.ordered_qty, reiv.primary_qty, reiv.trx_qty, reiv.amount, reiv.routing_id, reiv.to_organization_id, reiv.item_id, reiv.lpn_id, reiv.po_header_id, reiv.oe_order_header_id, reiv.license_plate_number, reiv.gross_weight, reiv.container_volume, reiv.content_volume, msit.LANGUAGE LANGUAGE, reiv.dock_to_stock, nvl2(reiv.lpn_id, 'N', 'Y')as enable_action, reiv.deliver_date FROM rcv_ecc_inbound_v reiv, mtl_system_items_tl msit WHERE reiv.item_id = msit.inventory_item_id AND reiv.to_organization_id = msit.organization_id AND msit.LANGUAGE in ( 'US')) PIVOT ( Max(source_doc_type) AS source_doc_type, Max(source_name) AS source_name, Max(trx_type) AS trx_type, Max(priority_flag) AS priority_flag, Max(item_description) AS item_description for LANGUAGE in ( 'US' "US")) ) x where 2=2

Parameter Name

SQL text

Validation

Organization Code