DBA Data[Home] [Help]

VIEW: APPS.RCV_RECEIVING_VALUE_VIEW

Source

View Text - Preformatted

SELECT rs.item_id item_id, rs.item_revision item_revision, rsl.item_description item_description, rs.unit_of_measure unit_of_measure, rs.location_id location_id, rs.to_organization_id to_organization_id, rs.to_org_primary_quantity to_org_primary_quantity, rs.receipt_date receipt_date, rsh.receipt_num receipt_num, rsh.shipment_num shipment_num, rsh.shipped_date shipped_date, rsh.freight_carrier_code freight_carrier_code, rsl.quantity_shipped quantity_shipped, rs.destination_type_code destination_type_code, nvl(rsl.packing_slip,rsh.packing_slip) packing_slip, rsl.source_document_code source_document_code, decode(rsl.source_document_code, 'INV', 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, 'INV', rsh.shipment_num, 'PO', ph.segment1, 'REQ', prh.segment1) source_document, decode(rsl.source_document_code, 'INV', rsl.line_num, 'PO', pl.line_num, 'REQ', prl.line_num) document_line_num, decode(rsl.source_document_code, 'INV', (rsl.shipment_unit_price * nvl(ph.rate,1)) * (rct.source_doc_quantity/rct.primary_quantity), 'PO', (pll.price_override *NVL(ph.rate,1)) * (rct.source_doc_quantity/rct.primary_quantity), 'REQ', prl.unit_price * (rct.source_doc_quantity / rct.primary_quantity)) actual_price, rsh.vendor_id vendor_id, rs.rcv_transaction_id rcv_transaction_id, pd.bom_resource_id bom_resource_id, pd.wip_entity_id wip_entity_id, pd.wip_resource_seq_num wip_resource_seq_num, pd.wip_operation_seq_num wip_operation_seq_num, pd.wip_repetitive_schedule_id wip_repetitive_schedule_id FROM mtl_supply rs, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_headers ph, po_lines pl, po_line_locations pll, po_distributions pd, po_requisition_headers prh, po_requisition_lines prl, rcv_transactions rct where 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 pd.po_distribution_id (+) = rs.po_distribution_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 rs.supply_type_code = 'RECEIVING'
View Text - HTML Formatted

SELECT RS.ITEM_ID ITEM_ID
, RS.ITEM_REVISION ITEM_REVISION
, RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, RS.UNIT_OF_MEASURE UNIT_OF_MEASURE
, RS.LOCATION_ID LOCATION_ID
, RS.TO_ORGANIZATION_ID TO_ORGANIZATION_ID
, RS.TO_ORG_PRIMARY_QUANTITY TO_ORG_PRIMARY_QUANTITY
, RS.RECEIPT_DATE RECEIPT_DATE
, RSH.RECEIPT_NUM RECEIPT_NUM
, RSH.SHIPMENT_NUM SHIPMENT_NUM
, RSH.SHIPPED_DATE SHIPPED_DATE
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE
, RSL.QUANTITY_SHIPPED QUANTITY_SHIPPED
, RS.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE
, NVL(RSL.PACKING_SLIP
, RSH.PACKING_SLIP) PACKING_SLIP
, RSL.SOURCE_DOCUMENT_CODE SOURCE_DOCUMENT_CODE
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'INV'
, 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
, 'INV'
, RSH.SHIPMENT_NUM
, 'PO'
, PH.SEGMENT1
, 'REQ'
, PRH.SEGMENT1) SOURCE_DOCUMENT
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'INV'
, RSL.LINE_NUM
, 'PO'
, PL.LINE_NUM
, 'REQ'
, PRL.LINE_NUM) DOCUMENT_LINE_NUM
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'INV'
, (RSL.SHIPMENT_UNIT_PRICE * NVL(PH.RATE
, 1)) * (RCT.SOURCE_DOC_QUANTITY/RCT.PRIMARY_QUANTITY)
, 'PO'
, (PLL.PRICE_OVERRIDE *NVL(PH.RATE
, 1)) * (RCT.SOURCE_DOC_QUANTITY/RCT.PRIMARY_QUANTITY)
, 'REQ'
, PRL.UNIT_PRICE * (RCT.SOURCE_DOC_QUANTITY / RCT.PRIMARY_QUANTITY)) ACTUAL_PRICE
, RSH.VENDOR_ID VENDOR_ID
, RS.RCV_TRANSACTION_ID RCV_TRANSACTION_ID
, PD.BOM_RESOURCE_ID BOM_RESOURCE_ID
, PD.WIP_ENTITY_ID WIP_ENTITY_ID
, PD.WIP_RESOURCE_SEQ_NUM WIP_RESOURCE_SEQ_NUM
, PD.WIP_OPERATION_SEQ_NUM WIP_OPERATION_SEQ_NUM
, PD.WIP_REPETITIVE_SCHEDULE_ID WIP_REPETITIVE_SCHEDULE_ID
FROM MTL_SUPPLY RS
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_HEADERS PH
, PO_LINES PL
, PO_LINE_LOCATIONS PLL
, PO_DISTRIBUTIONS PD
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, RCV_TRANSACTIONS RCT
WHERE 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 PD.PO_DISTRIBUTION_ID (+) = RS.PO_DISTRIBUTION_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 RS.SUPPLY_TYPE_CODE = 'RECEIVING'