VIEW: APPS.CST_PAC_RECEIVING_VALUES_V

Source

View Text - Preformatted

SELECT rct.transaction_id, rct.transaction_date, rct.organization_id, ccg.legal_entity, ccg.cost_group_id, msi.organization_id, msi.primary_uom_code, rs.item_id, rs.item_revision, rsl.item_description, rs.unit_of_measure, rs.location_id, rs.to_organization_id, rs.to_org_primary_quantity, rs.to_org_primary_uom, rs.receipt_date, rsh.receipt_num, rsh.shipment_num, rsh.shipped_date, rsh.freight_carrier_code, rsl.quantity_shipped, rsl.destination_type_code, nvl(rsl.packing_slip,rsh.packing_slip), rsl.source_document_code, decode(rsl.source_document_code, 'INVENTORY', rsl.deliver_to_location_id, 'PO', pd.deliver_to_location_id, 'REQ', prl.deliver_to_location_id) deliver_to_location_id, decode(rsl.source_document_code, 'INVENTORY', rsh.shipment_num, 'PO', ph.segment1, 'REQ', prh.segment1) source_document, decode(rsl.source_document_code, 'INVENTORY', rsl.line_num, 'PO', pl.line_num, 'REQ', prl.line_num) document_line_num, CRAC.acquisition_cost, rsh.vendor_id, rs.rcv_transaction_id, crac.period_id FROM CST_RCV_ACQ_COSTS CRAC, RCV_SUPPLY RS, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, PO_HEADERS_ALL PH, PO_LINES_ALL PL, PO_LINE_LOCATIONS_ALL PLL, PO_DISTRIBUTIONS_ALL PD, PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL, RCV_TRANSACTIONS RCT, CST_COST_GROUPS CCG, MTL_SYSTEM_ITEMS MSI WHERE crac.rcv_transaction_id = rct.transaction_id and NVL(rct.consigned_flag, 'N') = 'N' and rsh.shipment_header_id = rs.shipment_header_id and rsl.shipment_line_id = rs.shipment_line_id and ph.po_header_id (+) = rs.po_header_id and pl.po_line_id (+) = rs.po_line_id and pll.line_location_id (+) = rs.po_line_location_id and pll.line_location_id = pd.line_location_id and prh.requisition_header_id (+) = rs.req_header_id and prl.requisition_line_id (+) = rs.req_line_id and rs.rcv_transaction_id = rct.transaction_id and upper(rs.supply_type_code) = 'RECEIVING' and (ccg.legal_entity, ccg.cost_group_id) IN (select distinct ccg1.legal_entity, ccga.cost_group_id from cst_cost_group_assignments ccga, cst_cost_groups ccg1 where ccg1.cost_group_id = ccga.cost_group_id and ccga.organization_id = rct.organization_id) and msi.inventory_item_id = rs.item_id and msi.organization_id = (select distinct mp.master_organization_id from mtl_parameters mp where mp.organization_id = rct.organization_id) order by rs.rcv_transaction_id, rs.item_id

View Text - HTML Formatted

SELECT RCT.TRANSACTION_ID
, RCT.TRANSACTION_DATE
, RCT.ORGANIZATION_ID
, CCG.LEGAL_ENTITY
, CCG.COST_GROUP_ID
, MSI.ORGANIZATION_ID
, MSI.PRIMARY_UOM_CODE
, RS.ITEM_ID
, RS.ITEM_REVISION
, RSL.ITEM_DESCRIPTION
, RS.UNIT_OF_MEASURE
, RS.LOCATION_ID
, RS.TO_ORGANIZATION_ID
, RS.TO_ORG_PRIMARY_QUANTITY
, RS.TO_ORG_PRIMARY_UOM
, RS.RECEIPT_DATE
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM
, RSH.SHIPPED_DATE
, RSH.FREIGHT_CARRIER_CODE
, RSL.QUANTITY_SHIPPED
, RSL.DESTINATION_TYPE_CODE
, NVL(RSL.PACKING_SLIP
, RSH.PACKING_SLIP)
, RSL.SOURCE_DOCUMENT_CODE
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'INVENTORY'
, RSL.DELIVER_TO_LOCATION_ID
, 'PO'
, PD.DELIVER_TO_LOCATION_ID
, 'REQ'
, PRL.DELIVER_TO_LOCATION_ID) DELIVER_TO_LOCATION_ID
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'INVENTORY'
, RSH.SHIPMENT_NUM
, 'PO'
, PH.SEGMENT1
, 'REQ'
, PRH.SEGMENT1) SOURCE_DOCUMENT
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'INVENTORY'
, RSL.LINE_NUM
, 'PO'
, PL.LINE_NUM
, 'REQ'
, PRL.LINE_NUM) DOCUMENT_LINE_NUM
, CRAC.ACQUISITION_COST
, RSH.VENDOR_ID
, RS.RCV_TRANSACTION_ID
, CRAC.PERIOD_ID
FROM CST_RCV_ACQ_COSTS CRAC
, RCV_SUPPLY RS
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, PO_LINE_LOCATIONS_ALL PLL
, PO_DISTRIBUTIONS_ALL PD
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, RCV_TRANSACTIONS RCT
, CST_COST_GROUPS CCG
, MTL_SYSTEM_ITEMS MSI
WHERE CRAC.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID
AND NVL(RCT.CONSIGNED_FLAG
, 'N') = 'N'
AND RSH.SHIPMENT_HEADER_ID = RS.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RS.SHIPMENT_LINE_ID
AND PH.PO_HEADER_ID (+) = RS.PO_HEADER_ID
AND PL.PO_LINE_ID (+) = RS.PO_LINE_ID
AND PLL.LINE_LOCATION_ID (+) = RS.PO_LINE_LOCATION_ID
AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND PRH.REQUISITION_HEADER_ID (+) = RS.REQ_HEADER_ID
AND PRL.REQUISITION_LINE_ID (+) = RS.REQ_LINE_ID
AND RS.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID
AND UPPER(RS.SUPPLY_TYPE_CODE) = 'RECEIVING'
AND (CCG.LEGAL_ENTITY
, CCG.COST_GROUP_ID) IN (SELECT DISTINCT CCG1.LEGAL_ENTITY
, CCGA.COST_GROUP_ID
FROM CST_COST_GROUP_ASSIGNMENTS CCGA
, CST_COST_GROUPS CCG1
WHERE CCG1.COST_GROUP_ID = CCGA.COST_GROUP_ID
AND CCGA.ORGANIZATION_ID = RCT.ORGANIZATION_ID)
AND MSI.INVENTORY_ITEM_ID = RS.ITEM_ID
AND MSI.ORGANIZATION_ID = (SELECT DISTINCT MP.MASTER_ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = RCT.ORGANIZATION_ID) ORDER BY RS.RCV_TRANSACTION_ID
, RS.ITEM_ID