VIEW: APPS.RCV_RECEIVING_ACCT_DISTR_V

Source

View Text - Preformatted

SELECT POH.SEGMENT1 PO_NUMBER , GCC.CODE_COMBINATION_ID CODE_COMBINATION_ID , POL.ITEM_ID ITEM_ID , RCT.TRANSACTION_ID TRANSACTION_ID , RRSL.ACCOUNTING_DATE ACCOUNTING_DATE , RRSL.TRANSACTION_DATE TRANSACTION_DATE , NVL(RAE.PRIMARY_QUANTITY, RCT.PRIMARY_QUANTITY) PRIMARY_QUANTITY , NVL(RAE.PRIMARY_UNIT_OF_MEASURE, RCT.PRIMARY_UNIT_OF_MEASURE) PRIMARY_UOM , NVL(RAE.UNIT_PRICE, RCT.PO_UNIT_PRICE) PO_UNIT_PRICE , HRL.LOCATION_CODE DELIVER_TO_LOCATION , RRSL.CURRENCY_CODE CURRENCY_CODE , PRL.LINE_NUM REQUISITION_LINE_NUMBER , POL.LINE_NUM LINE_NUMBER , RSH.RECEIPT_NUM RECEIPT_NUMBER , RSL.ITEM_REVISION ITEM_REVISION , RSL.CATEGORY_ID CATEGORY_ID , RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION , PLC1.DISPLAYED_FIELD DESTINATION_TYPE , PLC2.DISPLAYED_FIELD TRANSACTION_TYPE , RCT.LOCATION_ID LOCATION_ID , RCT.SUBINVENTORY SUBINVENTORY , RCT.ORGANIZATION_ID ORGANIZATION_ID , NVL(RAE.CURRENCY_CONVERSION_TYPE, RCT.CURRENCY_CONVERSION_TYPE) CURRENCY_CONVERSION_TYPE , NVL(RAE.CURRENCY_CONVERSION_DATE, RRSL.CURRENCY_CONVERSION_DATE) CURRENCY_CONVERSION_DATE , NVL(RAE.CURRENCY_CONVERSION_RATE, RRSL.CURRENCY_CONVERSION_RATE) CURRENCY_CONVERSION_RATE , RCT.PO_REVISION_NUM PO_REVISION_NUM , NVL(RAE.TRANSACTION_QUANTITY, RCT.QUANTITY) QUANTITY , RCT.DEPARTMENT_CODE DEPARTMENT_CODE , RCT.SOURCE_DOC_UNIT_OF_MEASURE SOURCE_DOC_UNIT_OF_MEASURE , DECODE(RRSL.SOURCE_DOC_QUANTITY, NULL, RCT.SOURCE_DOC_QUANTITY, RRSL.SOURCE_DOC_QUANTITY) SOURCE_DOC_QUANTITY , NVL(RAE.SOURCE_DOC_QUANTITY, RCT.SOURCE_DOC_QUANTITY) , RSH.SHIPMENT_NUM SHIPMENT_NUMBER , RSH.PACKING_SLIP PACKING_SLIP , NVL(RCT.VENDOR_ID,RSH.VENDOR_ID) VENDOR_ID , RCT.LOCATOR_ID LOCATOR_ID , RCT.WIP_ENTITY_ID WIP_ENTITY_ID , RCT.WIP_LINE_ID WIP_LINE_ID , RCT.WIP_REPETITIVE_SCHEDULE_ID WIP_REPETITIVE_SCHEDULE_ID , RCT.WIP_OPERATION_SEQ_NUM WIP_OPERATION_SEQ_NUM , RCT.WIP_RESOURCE_SEQ_NUM WIP_RESOURCE_SEQ_NUM , RCT.BOM_RESOURCE_ID BOM_RESOURCE_ID , RRSL.ENTERED_DR ENTERED_DR , RRSL.ENTERED_CR ENTERED_CR , RRSL.SET_OF_BOOKS_ID SET_OF_BOOKS_ID , NVL(RAE.ACCOUNTING_EVENT_ID,-1) ACCOUNTING_EVENT_ID , NVL(RAE.EVENT_TYPE_ID,-1) EVENT_TYPE_ID , NVL(RAE.PRIOR_UNIT_PRICE,RCT.PO_UNIT_PRICE) PRIOR_UNIT_PRICE , RRSL.ACCOUNTING_LINE_TYPE , RAE.UNIT_LANDED_COST FROM PO_LOOKUP_CODES PLC1, PO_LOOKUP_CODES PLC2, HR_LOCATIONS HRL, GL_CODE_COMBINATIONS GCC, PO_REQUISITION_LINES PRL, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, PO_HEADERS POH, PO_LINES POL, RCV_RECEIVING_SUB_LEDGER RRSL, RCV_TRANSACTIONS RCT, RCV_ACCOUNTING_EVENTS RAE WHERE RCT.TRANSACTION_ID = RRSL.RCV_TRANSACTION_ID AND RCT.PO_HEADER_ID = POH.PO_HEADER_ID AND RCT.PO_LINE_ID = POL.PO_LINE_ID AND RCT.DELIVER_TO_LOCATION_ID = HRL.LOCATION_ID (+) AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND RRSL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND RCT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+) AND PLC1.LOOKUP_CODE = RCT.DESTINATION_TYPE_CODE AND PLC1.LOOKUP_TYPE = 'RCV DESTINATION TYPE' AND PLC2.LOOKUP_CODE = RCT.TRANSACTION_TYPE AND PLC2.LOOKUP_TYPE = 'RCV TRANSACTION TYPE' AND RCT.PRIMARY_QUANTITY != 0 AND RCT.SOURCE_DOC_QUANTITY != 0 AND RRSL.ACCOUNTING_EVENT_ID = RAE.ACCOUNTING_EVENT_ID(+)

View Text - HTML Formatted

SELECT POH.SEGMENT1 PO_NUMBER
, GCC.CODE_COMBINATION_ID CODE_COMBINATION_ID
, POL.ITEM_ID ITEM_ID
, RCT.TRANSACTION_ID TRANSACTION_ID
, RRSL.ACCOUNTING_DATE ACCOUNTING_DATE
, RRSL.TRANSACTION_DATE TRANSACTION_DATE
, NVL(RAE.PRIMARY_QUANTITY
, RCT.PRIMARY_QUANTITY) PRIMARY_QUANTITY
, NVL(RAE.PRIMARY_UNIT_OF_MEASURE
, RCT.PRIMARY_UNIT_OF_MEASURE) PRIMARY_UOM
, NVL(RAE.UNIT_PRICE
, RCT.PO_UNIT_PRICE) PO_UNIT_PRICE
, HRL.LOCATION_CODE DELIVER_TO_LOCATION
, RRSL.CURRENCY_CODE CURRENCY_CODE
, PRL.LINE_NUM REQUISITION_LINE_NUMBER
, POL.LINE_NUM LINE_NUMBER
, RSH.RECEIPT_NUM RECEIPT_NUMBER
, RSL.ITEM_REVISION ITEM_REVISION
, RSL.CATEGORY_ID CATEGORY_ID
, RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, PLC1.DISPLAYED_FIELD DESTINATION_TYPE
, PLC2.DISPLAYED_FIELD TRANSACTION_TYPE
, RCT.LOCATION_ID LOCATION_ID
, RCT.SUBINVENTORY SUBINVENTORY
, RCT.ORGANIZATION_ID ORGANIZATION_ID
, NVL(RAE.CURRENCY_CONVERSION_TYPE
, RCT.CURRENCY_CONVERSION_TYPE) CURRENCY_CONVERSION_TYPE
, NVL(RAE.CURRENCY_CONVERSION_DATE
, RRSL.CURRENCY_CONVERSION_DATE) CURRENCY_CONVERSION_DATE
, NVL(RAE.CURRENCY_CONVERSION_RATE
, RRSL.CURRENCY_CONVERSION_RATE) CURRENCY_CONVERSION_RATE
, RCT.PO_REVISION_NUM PO_REVISION_NUM
, NVL(RAE.TRANSACTION_QUANTITY
, RCT.QUANTITY) QUANTITY
, RCT.DEPARTMENT_CODE DEPARTMENT_CODE
, RCT.SOURCE_DOC_UNIT_OF_MEASURE SOURCE_DOC_UNIT_OF_MEASURE
, DECODE(RRSL.SOURCE_DOC_QUANTITY
, NULL
, RCT.SOURCE_DOC_QUANTITY
, RRSL.SOURCE_DOC_QUANTITY) SOURCE_DOC_QUANTITY
, NVL(RAE.SOURCE_DOC_QUANTITY
, RCT.SOURCE_DOC_QUANTITY)
, RSH.SHIPMENT_NUM SHIPMENT_NUMBER
, RSH.PACKING_SLIP PACKING_SLIP
, NVL(RCT.VENDOR_ID
, RSH.VENDOR_ID) VENDOR_ID
, RCT.LOCATOR_ID LOCATOR_ID
, RCT.WIP_ENTITY_ID WIP_ENTITY_ID
, RCT.WIP_LINE_ID WIP_LINE_ID
, RCT.WIP_REPETITIVE_SCHEDULE_ID WIP_REPETITIVE_SCHEDULE_ID
, RCT.WIP_OPERATION_SEQ_NUM WIP_OPERATION_SEQ_NUM
, RCT.WIP_RESOURCE_SEQ_NUM WIP_RESOURCE_SEQ_NUM
, RCT.BOM_RESOURCE_ID BOM_RESOURCE_ID
, RRSL.ENTERED_DR ENTERED_DR
, RRSL.ENTERED_CR ENTERED_CR
, RRSL.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, NVL(RAE.ACCOUNTING_EVENT_ID
, -1) ACCOUNTING_EVENT_ID
, NVL(RAE.EVENT_TYPE_ID
, -1) EVENT_TYPE_ID
, NVL(RAE.PRIOR_UNIT_PRICE
, RCT.PO_UNIT_PRICE) PRIOR_UNIT_PRICE
, RRSL.ACCOUNTING_LINE_TYPE
, RAE.UNIT_LANDED_COST
FROM PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, HR_LOCATIONS HRL
, GL_CODE_COMBINATIONS GCC
, PO_REQUISITION_LINES PRL
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_HEADERS POH
, PO_LINES POL
, RCV_RECEIVING_SUB_LEDGER RRSL
, RCV_TRANSACTIONS RCT
, RCV_ACCOUNTING_EVENTS RAE
WHERE RCT.TRANSACTION_ID = RRSL.RCV_TRANSACTION_ID
AND RCT.PO_HEADER_ID = POH.PO_HEADER_ID
AND RCT.PO_LINE_ID = POL.PO_LINE_ID
AND RCT.DELIVER_TO_LOCATION_ID = HRL.LOCATION_ID (+)
AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RRSL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND RCT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+)
AND PLC1.LOOKUP_CODE = RCT.DESTINATION_TYPE_CODE
AND PLC1.LOOKUP_TYPE = 'RCV DESTINATION TYPE'
AND PLC2.LOOKUP_CODE = RCT.TRANSACTION_TYPE
AND PLC2.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
AND RCT.PRIMARY_QUANTITY != 0
AND RCT.SOURCE_DOC_QUANTITY != 0
AND RRSL.ACCOUNTING_EVENT_ID = RAE.ACCOUNTING_EVENT_ID(+)