Monday, March 19, 2018

Query to Find Purchase Order in Oracle Apps R12

SELECT
  O.NAME "OPERATING_UNIT_NAME",
  PH.SEGMENT1 "PO_REV_NUM",
  PH.PO_HEADER_ID,
  PH.TYPE_LOOKUP_CODE "TYPE",
  PH.CURRENCY_CODE,
  PH.AUTHORIZATION_STATUS "STATUS",
  PV.VENDOR_SITE_CODE,
  V.VENDOR_NAME "SUPPLIER_NAME",
  LOC1.LOCATION_CODE "SHIP_TO_LOC",
  LOC2.LOCATION_CODE "BILL_TO_LOC",
  (PL1.QUANTITY*PL1.UNIT_PRICE) "TOTAL",
--LINES INFORMATION
  PL1.LINE_NUM,
  PL1.PURCHASE_BASIS "TYPE",
  I.SEGMENT1 "ITEM",
  PL1.ITEM_REVISION,
  C.SEGMENT1||C.SEGMENT1 "CATEGORY",
  PL1.ITEM_DESCRIPTION,
  PL1.QUANTITY,
  PL.UNIT_MEAS_LOOKUP_CODE,
  PL1.UNIT_PRICE,
  PL.NEED_BY_DATE,
  PL.SHIPMENT_TYPE,
  PL.SHIPMENT_NUM,
  M.ORGANIZATION_CODE "SHP_ORG_CODE",
  LOC1.LOCATION_CODE "SHIPMENT_LOC",
  PL.UNIT_MEAS_LOOKUP_CODE "SHP_UOM",
  PL.QUANTITY "SHP_QUANTITY",
  (PL.QUANTITY*PL.PRICE_OVERRIDE) "SHP_AMOUNT",
  D.DISTRIBUTION_NUM,
  D.DESTINATION_TYPE_CODE,
  GL.CONCATENATED_SEGMENTS,
  D.QUANTITY_ORDERED "DIS_QUANTITY",
  P.FULL_NAME "REQUESTER NAME",
  I.INVENTORY_ITEM_ID,
  C.CATEGORY_ID
FROM
  --PO_HEADERS
  HR_ORGANIZATION_UNITS O,
  po_headers_all PH,
  PO_VENDORS V,
  PO_VENDOR_SITES_ALL PV,
  HR_LOCATIONS_ALL_TL LOC1,
  HR_LOCATIONS_ALL_TL LOC2,
  --PO_LINES
  po_line_locations_all PL,
  PO_LINES_ALL PL1,
  MTL_SYSTEM_ITEMS_B I,
  MTL_CATEGORIES_B C,
  MTL_PARAMETERS M,
  PO_DISTRIBUTIONS_ALL D,
  GL_CODE_COMBINATIONS_KFV GL,
  PER_ALL_PEOPLE_F P
WHERE PH.VENDOR_SITE_ID=PV.VENDOR_SITE_ID
  AND PH.SHIP_TO_LOCATION_ID=PV.SHIP_TO_LOCATION_ID
  AND PH.VENDOR_ID=V.VENDOR_ID
  AND LOC1.LOCATION_ID=PH.SHIP_TO_LOCATION_ID
  AND LOC2.LOCATION_ID=PV.BILL_TO_LOCATION_ID
  AND PH.PO_HEADER_ID=PL1.PO_HEADER_ID
    --LINES
  AND PL1.ITEM_ID=I.INVENTORY_ITEM_ID
  AND PL.PO_HEADER_ID=PL1.PO_HEADER_ID
  AND PL.PO_LINE_ID=PL1.PO_LINE_ID
  AND PL1.CATEGORY_ID=C.CATEGORY_ID
  --ORG_CODE
  AND M.ORGANIZATION_ID=PL.SHIP_TO_ORGANIZATION_ID
  --AND O.ORGANIZATION_ID=M.ORGANIZATION_ID
  --AND C.ORGANIZATION_ID=M.ORGANIZATION_ID
  AND O.ORGANIZATION_ID=PH.ORG_ID
  --DISTRIBUTION
  AND D.PO_HEADER_ID=PL.PO_HEADER_ID
  AND D.PO_LINE_ID=PL.PO_LINE_ID
  AND D.LINE_LOCATION_ID=PL.LINE_LOCATION_ID
  AND GL.CODE_COMBINATION_ID=D.CODE_COMBINATION_ID
  AND D.DELIVER_TO_PERSON_ID=P.PERSON_ID;

No comments:

Post a Comment