SQL & PL/SQL

Hello everyone! I am having some difficulty getting the correct results from this query. This query generates on hand quantity. I started getting the issue when I added the last three fields to this query, VENDOR, PO and UNIT_PRICE. I think that I am missing other parameters aside from just joining the tables


CREATE OR REPLACE FORCE VIEW "APPS"."XXGVN_INVENTORY_ONHAND_VIEW2" ("ORG", "ITEM_TYPE", "ITEM", "DESCRIPTION", "LOT_NUMBER", "SUBINVENTORY", "LOCATOR", "UOM", "QTY", "SEC_UOM", "SEC_QTY", "COST", "AMOUNT", "PO_VENDOR", "VENDOR", "PO", "UNIT_PRICE") AS
SELECT MP.ORGANIZATION_CODE, M.ITEM_TYPE, M.SEGMENT1 ITEM, M.DESCRIPTION, Q.LOT_NUMBER, Q.SUBINVENTORY_CODE, L.CONCATENATED_SEGMENTS LOCATOR
, M.PRIMARY_UOM_CODE UOM, SUM(Q.TRANSACTION_QUANTITY) QTY, Q.SECONDARY_UOM_CODE SEC_UOM, SUM(Q.SECONDARY_TRANSACTION_QUANTITY) SEC_QTY
, NVL(C.ITEM_COST,0) COST, SUM(Q.TRANSACTION_QUANTITY)*NVL(C.ITEM_COST,0) AMOUNT
, M.ATTRIBUTE16 PO_VENDOR, PV.VENDOR_NAME VENDOR, POH.SEGMENT1 PO, PLA.UNIT_PRICE PRICE
FROM MTL_ONHAND_QUANTITIES Q, MTL_SYSTEM_ITEMS_B M,MTL_ITEM_LOCATIONS_KFV L, CST_ITEM_COSTS C, MTL_PARAMETERS MP, PO_VENDORS PV, PO_LINES_ALL PLA, PO_HEADERS_ALL POH
WHERE 83 = M.ORGANIZATION_ID
AND poh.po_header_id = pla.po_header_id
AND pla.item_id = M.inventory_item_id
AND poh.vendor_id = pv.vendor_id

AND Q.INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
AND Q.LOCATOR_ID = L.INVENTORY_LOCATION_ID(+)
AND Q.ORGANIZATION_ID = C.ORGANIZATION_ID(+)
AND Q.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID(+)
AND C.COST_TYPE_ID(+) = 2
AND Q.ORGANIZATION_ID = 86
AND MP.ORGANIZATION_ID = Q.ORGANIZATION_ID
GROUP BY MP.ORGANIZATION_CODE, M.ITEM_TYPE, M.SEGMENT1 , M.DESCRIPTION, Q.SUBINVENTORY_CODE, L.CONCATENATED_SEGMENTS, C.ITEM_COST, Q.LOT_NUMBER
, M.PRIMARY_UOM_CODE, Q.SECONDARY_UOM_CODE, M.ATTRIBUTE16, PV.VENDOR_NAME, POH.SEGMENT1, PLA.UNIT_PRICE
UNION
SELECT MP.ORGANIZATION_CODE, M.ITEM_TYPE, M.SEGMENT1 ITEM, M.DESCRIPTION, Q.LOT_NUMBER, Q.SUBINVENTORY_CODE, L.CONCATENATED_SEGMENTS LOCATOR
, M.PRIMARY_UOM_CODE UOM, SUM(Q.TRANSACTION_QUANTITY) QTY, Q.SECONDARY_UOM_CODE SEC_UOM, SUM(Q.SECONDARY_TRANSACTION_QUANTITY) SEC_QTY
, (SELECT NVL(SUM(C.CMPNT_COST),0) FROM CM_CMPT_DTL_VW C, GMF_PERIOD_STATUSES C1
WHERE Q.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID
AND C.COST_TYPE_ID = 1001
AND C.ORGANIZATION_ID = 84
AND C.PERIOD_ID = C1.PERIOD_ID
AND (SYSDATE-3) BETWEEN C1.START_DATE AND C1.END_DATE
AND C1.CALENDAR_CODE = 'COST_CAL'
AND C.COST_ANALYSIS_CODE = 'VAL') COST
, SUM(Q.TRANSACTION_QUANTITY)*(SELECT NVL(SUM(C.CMPNT_COST),0) FROM CM_CMPT_DTL_VW C, GMF_PERIOD_STATUSES C1
WHERE Q.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID
AND C.COST_TYPE_ID = 1001
AND C.ORGANIZATION_ID = 84
AND C.PERIOD_ID = C1.PERIOD_ID
AND (SYSDATE-3) BETWEEN C1.START_DATE AND C1.END_DATE
AND C1.CALENDAR_CODE = 'COST_CAL'
AND C.COST_ANALYSIS_CODE = 'VAL') AMOUNT
, M.ATTRIBUTE16 PO_VENDOR, PV.VENDOR_NAME VENDOR, POH.SEGMENT1, PLA.UNIT_PRICE
FROM MTL_ONHAND_QUANTITIES Q, MTL_SYSTEM_ITEMS_B M,MTL_ITEM_LOCATIONS_KFV L, MTL_PARAMETERS MP, PO_VENDORS PV, PO_LINES_ALL PLA, PO_HEADERS_ALL POH
WHERE 83 = M.ORGANIZATION_ID
AND poh.po_header_id = pla.po_header_id
AND pla.item_id = M.inventory_item_id
AND poh.vendor_id = pv.vendor_id

AND Q.INVENTORY_ITEM_ID = M.INVENTORY_ITEM_ID
AND Q.LOCATOR_ID = L.INVENTORY_LOCATION_ID(+)
AND Q.ORGANIZATION_ID != 86
AND MP.ORGANIZATION_ID = Q.ORGANIZATION_ID
GROUP BY MP.ORGANIZATION_CODE, M.ITEM_TYPE, M.SEGMENT1, M.DESCRIPTION, Q.SUBINVENTORY_CODE, L.CONCATENATED_SEGMENTS, Q.LOT_NUMBER,
M.PRIMARY_UOM_CODE, Q.SECONDARY_UOM_CODE, Q.INVENTORY_ITEM_ID, M.ATTRIBUTE16, PV.VENDOR_NAME, POH.SEGMENT1, PLA.UNIT_PRICE
ORDER BY 1,2,3 ;

Any help would be greatly appreciated.

Thanks!

Edited by: user8741170 on Aug 2, 2011 8:40 AM