[Home] [Help]
SELECT ms.item_id ,ms.to_organization_id ,ms.req_header_id ,DECODE(prl.vendor_id, NULL, prl.suggested_vendor_name, pv.vendor_name) ,DECODE(prl.vendor_site_id, NULL, prl.suggested_vendor_location, pvs.vendor_site_code) ,ms.need_by_date ,prh.segment1 ,nvl(ms.quantity , 0) * pd.req_line_quantity / prl.quantity ,muom.uom_code ,nvl(ms.to_org_primary_quantity ,0) * pd.req_line_quantity / prl.quantity ,ms.to_org_primary_uom ,prl.unit_price ,prl.line_num ,ms.destination_type_code ,ms.mrp_expected_delivery_date ,nvl(ms.mrp_primary_quantity ,0) * pd.req_line_quantity / prl.quantity ,ms.mrp_primary_uom ,ms.mrp_destination_type_code ,ms.mrp_to_organization_id ,ms.mrp_to_subinventory ,ms.req_line_id ,ms.ITEM_REVISION ,ms.EXPECTED_DELIVERY_DATE ,nvl(ms.TO_ORG_PRIMARY_QUANTITY ,0) * pd.req_line_quantity / prl.quantity ,ms.TO_ORGANIZATION_ID ,pd.project_id ,pd.task_id ,pd.end_item_unit_number ,ms.to_subinventory ,prh.transferred_to_oe_flag ,ms.from_organization_id ,prl.VENDOR_SITE_ID ,NVL(PRL.VENDOR_ID,PV1.VENDOR_ID) ,prh.org_id FROM MTL_UNITS_OF_MEASURE MUOM, MTL_SUPPLY MS, PO_REQ_DISTRIBUTIONS_ALL PD, PO_REQUISITION_LINES_ALL PRL, PO_REQUISITION_HEADERS_ALL PRH, ap_suppliers pv, ap_suppliers pv1, ap_supplier_sites_all pvs where ms.supply_type_code = 'REQ' and pd.requisition_line_id = prl.requisition_line_id and prl.requisition_line_id = ms.req_line_id and prh.requisition_header_id = ms.req_header_id and prl.quantity > 0 and muom.unit_of_measure = ms.unit_of_measure AND prl.vendor_id = pv.vendor_id(+) AND prl.vendor_site_id = pvs.vendor_site_id(+) AND prl.suggested_vendor_name = pv1.vendor_name(+)
SELECT MS.ITEM_ID
, MS.TO_ORGANIZATION_ID
, MS.REQ_HEADER_ID
, DECODE(PRL.VENDOR_ID
, NULL
, PRL.SUGGESTED_VENDOR_NAME
, PV.VENDOR_NAME)
, DECODE(PRL.VENDOR_SITE_ID
, NULL
, PRL.SUGGESTED_VENDOR_LOCATION
, PVS.VENDOR_SITE_CODE)
, MS.NEED_BY_DATE
, PRH.SEGMENT1
, NVL(MS.QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PRL.QUANTITY
, MUOM.UOM_CODE
, NVL(MS.TO_ORG_PRIMARY_QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PRL.QUANTITY
, MS.TO_ORG_PRIMARY_UOM
, PRL.UNIT_PRICE
, PRL.LINE_NUM
, MS.DESTINATION_TYPE_CODE
, MS.MRP_EXPECTED_DELIVERY_DATE
, NVL(MS.MRP_PRIMARY_QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PRL.QUANTITY
, MS.MRP_PRIMARY_UOM
, MS.MRP_DESTINATION_TYPE_CODE
, MS.MRP_TO_ORGANIZATION_ID
, MS.MRP_TO_SUBINVENTORY
, MS.REQ_LINE_ID
, MS.ITEM_REVISION
, MS.EXPECTED_DELIVERY_DATE
, NVL(MS.TO_ORG_PRIMARY_QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PRL.QUANTITY
, MS.TO_ORGANIZATION_ID
, PD.PROJECT_ID
, PD.TASK_ID
, PD.END_ITEM_UNIT_NUMBER
, MS.TO_SUBINVENTORY
, PRH.TRANSFERRED_TO_OE_FLAG
, MS.FROM_ORGANIZATION_ID
, PRL.VENDOR_SITE_ID
, NVL(PRL.VENDOR_ID
, PV1.VENDOR_ID)
, PRH.ORG_ID
FROM MTL_UNITS_OF_MEASURE MUOM
, MTL_SUPPLY MS
, PO_REQ_DISTRIBUTIONS_ALL PD
, PO_REQUISITION_LINES_ALL PRL
, PO_REQUISITION_HEADERS_ALL PRH
, AP_SUPPLIERS PV
, AP_SUPPLIERS PV1
, AP_SUPPLIER_SITES_ALL PVS
WHERE MS.SUPPLY_TYPE_CODE = 'REQ'
AND PD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND PRL.REQUISITION_LINE_ID = MS.REQ_LINE_ID
AND PRH.REQUISITION_HEADER_ID = MS.REQ_HEADER_ID
AND PRL.QUANTITY > 0
AND MUOM.UNIT_OF_MEASURE = MS.UNIT_OF_MEASURE
AND PRL.VENDOR_ID = PV.VENDOR_ID(+)
AND PRL.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND PRL.SUGGESTED_VENDOR_NAME = PV1.VENDOR_NAME(+)
|
|
|
|