VIEW: APPS.CST_RCV_RAE_V

Source

View Text - Preformatted

SELECT rae.rowid, rae.organization_id, rae.org_id, cod.organization_name, cod.organization_code, cod.set_of_books_id, rae.rcv_transaction_id, rae.accounting_event_id, rae.event_type_id, decode(rae.event_type_id,15,raet.description, 16,raet.description, 17,raet.description, raet.event_type_name) event_type_name, rae.transaction_date, rae.transaction_quantity, muomtxn.uom_code, rae.primary_quantity, muomprim.uom_code, rt.po_header_id, nvl(poh.clm_document_number,poh.segment1), rt.po_line_id, nvl(pol.line_num_display, to_char(pol.line_num)), rae.po_distribution_id, rae.po_release_id, por.release_num, prh.requisition_header_id, prh.segment1, prl.requisition_line_id, prl.line_num, poh.vendor_id, pov.vendor_name, poh.vendor_site_id, pvs.vendor_site_code, rsh.receipt_num, rsh.shipment_num, rsh.shipment_header_id, rsl.line_num, rsl.shipment_line_id, rt.location_id, pol.item_id, nvl(msi.description,pol.item_description), rsl.item_revision, rae.unit_price, rae.prior_unit_price, rae.unit_landed_cost, rae.intercompany_pricing_option, rae.transaction_amount, rae.nr_tax, rae.rec_tax, rae.nr_tax_amount, rae.rec_tax_amount, rae.prior_nr_tax, rae.prior_rec_tax, rae.currency_code, rae.currency_conversion_type, rae.currency_conversion_rate, rae.currency_conversion_date, rae.accounted_flag, rae.procurement_org_flag, rae.cross_ou_flag, rae.trx_flow_header_id, rae.invoiced_flag, rae.pa_addition_flag, rae.last_update_date, rae.last_updated_by, rae.creation_date, rae.created_by, rae.last_update_login, rae.request_id, rae.program_application_id, rae.program_id, rae.program_udpate_date FROM rcv_accounting_events rae, rcv_transactions rt, po_headers poh, po_lines pol, po_line_locations poll, po_requisition_headers prh, po_requisition_lines prl, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_vendors pov, po_vendor_sites_all pvs, rcv_accounting_event_types raet, mtl_system_items_vl msi, cst_organization_definitions cod, mtl_units_of_measure muomtxn, mtl_units_of_measure muomprim, po_releases por WHERE rae.rcv_transaction_id = rt.transaction_id AND prl.requisition_line_id(+) = rt.requisition_line_id AND prh.requisition_header_id(+) = prl.requisition_header_id AND rae.po_header_id = poh.po_header_id AND rae.po_line_id = pol.po_line_id AND rae.po_line_location_id = poll.line_location_id AND rsh.shipment_header_id = rsl.shipment_header_id AND rsl.shipment_line_id = rt.shipment_line_id AND rsh.shipment_header_id = rt.shipment_header_id AND cod.organization_id = rae.organization_id AND rae.event_type_id = raet.event_type_id AND pov.vendor_id(+) = rt.vendor_id AND pvs.vendor_site_id(+) = poh.vendor_site_id AND msi.inventory_item_id(+) = rae.inventory_item_id AND msi.organization_id (+) = rae.organization_id AND nvl(poll.accrue_on_receipt_flag,'N') = 'Y' AND rae.event_type_id NOT IN (18,19,20,14) AND muomtxn.unit_of_measure(+) = rae.transaction_unit_of_measure AND muomprim.unit_of_measure(+) = rae.primary_unit_of_measure AND por.po_release_id (+) = rae.po_release_id AND pol.item_id(+) = rae.inventory_item_id AND rt.po_header_id IS NOT NULL AND poh.po_header_id = rt.po_header_id AND pol.po_line_id = rt.po_line_id AND rt.destination_type_code != 'INVENTORY' UNION ALL select rae.rowid, rae.organization_id, rae.org_id, cod.organization_name, cod.organization_code, cod.set_of_books_id, rae.rcv_transaction_id, rae.accounting_event_id, rae.event_type_id, raet.event_type_name, rae.transaction_date, rae.transaction_quantity, muomtxn.uom_code, rae.primary_quantity, muomprim.uom_code, poh.po_header_id, Nvl(poh.clm_document_number, poh.segment1), pol.po_line_id, nvl(pol.line_num_display, to_char(pol.line_num)), rae.po_distribution_id, rae.po_release_id, por.release_num, to_number(null), to_char(null), to_number(null), to_number(null), poh.vendor_id, pov.vendor_name, poh.vendor_site_id, pvs.vendor_site_code, to_char(null), to_char(null), to_number(null), to_number(null), to_number(null), to_number(null), rae.inventory_item_id, nvl(msi.description,pol.item_description), to_char(null), rae.unit_price, rae.prior_unit_price, rae.unit_landed_cost, rae.intercompany_pricing_option, rae.transaction_amount, rae.nr_tax, rae.rec_tax, rae.nr_tax_amount, rae.rec_tax_amount, rae.prior_nr_tax, rae.prior_rec_tax, rae.currency_code, rae.currency_conversion_type, rae.currency_conversion_rate, rae.currency_conversion_date, rae.accounted_flag, rae.procurement_org_flag, rae.cross_ou_flag, rae.trx_flow_header_id, rae.invoiced_flag, rae.pa_addition_flag, rae.last_update_date, rae.last_updated_by, rae.creation_date, rae.created_by, rae.last_update_login, rae.request_id, rae.program_application_id, rae.program_id, rae.program_udpate_date FROM rcv_accounting_events rae, po_headers poh, po_lines pol, po_line_locations poll, po_vendors pov, po_vendor_sites_all pvs, rcv_accounting_event_types raet, mtl_system_items_vl msi, cst_organization_definitions cod, mtl_units_of_measure muomtxn, mtl_units_of_measure muomprim, po_releases por where rae.po_header_id = poh.po_header_id AND rae.po_line_id = pol.po_line_id AND rae.po_line_location_id = poll.line_location_id AND cod.organization_id = rae.organization_id AND rae.event_type_id = raet.event_type_id AND pov.vendor_id = poh.vendor_id AND pvs.vendor_site_id = poh.vendor_site_id AND msi.inventory_item_id(+) = rae.inventory_item_id AND msi.organization_id (+) = rae.organization_id AND nvl(poll.accrue_on_receipt_flag,'N') = 'N' AND rae.event_type_id IN (13,14) AND muomtxn.unit_of_measure(+) = rae.transaction_unit_of_measure AND muomprim.unit_of_measure(+) = rae.primary_unit_of_measure AND por.po_release_id (+) = rae.po_release_id

View Text - HTML Formatted

SELECT RAE.ROWID
, RAE.ORGANIZATION_ID
, RAE.ORG_ID
, COD.ORGANIZATION_NAME
, COD.ORGANIZATION_CODE
, COD.SET_OF_BOOKS_ID
, RAE.RCV_TRANSACTION_ID
, RAE.ACCOUNTING_EVENT_ID
, RAE.EVENT_TYPE_ID
, DECODE(RAE.EVENT_TYPE_ID
, 15
, RAET.DESCRIPTION
, 16
, RAET.DESCRIPTION
, 17
, RAET.DESCRIPTION
, RAET.EVENT_TYPE_NAME) EVENT_TYPE_NAME
, RAE.TRANSACTION_DATE
, RAE.TRANSACTION_QUANTITY
, MUOMTXN.UOM_CODE
, RAE.PRIMARY_QUANTITY
, MUOMPRIM.UOM_CODE
, RT.PO_HEADER_ID
, NVL(POH.CLM_DOCUMENT_NUMBER
, POH.SEGMENT1)
, RT.PO_LINE_ID
, NVL(POL.LINE_NUM_DISPLAY
, TO_CHAR(POL.LINE_NUM))
, RAE.PO_DISTRIBUTION_ID
, RAE.PO_RELEASE_ID
, POR.RELEASE_NUM
, PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM
, RSH.SHIPMENT_HEADER_ID
, RSL.LINE_NUM
, RSL.SHIPMENT_LINE_ID
, RT.LOCATION_ID
, POL.ITEM_ID
, NVL(MSI.DESCRIPTION
, POL.ITEM_DESCRIPTION)
, RSL.ITEM_REVISION
, RAE.UNIT_PRICE
, RAE.PRIOR_UNIT_PRICE
, RAE.UNIT_LANDED_COST
, RAE.INTERCOMPANY_PRICING_OPTION
, RAE.TRANSACTION_AMOUNT
, RAE.NR_TAX
, RAE.REC_TAX
, RAE.NR_TAX_AMOUNT
, RAE.REC_TAX_AMOUNT
, RAE.PRIOR_NR_TAX
, RAE.PRIOR_REC_TAX
, RAE.CURRENCY_CODE
, RAE.CURRENCY_CONVERSION_TYPE
, RAE.CURRENCY_CONVERSION_RATE
, RAE.CURRENCY_CONVERSION_DATE
, RAE.ACCOUNTED_FLAG
, RAE.PROCUREMENT_ORG_FLAG
, RAE.CROSS_OU_FLAG
, RAE.TRX_FLOW_HEADER_ID
, RAE.INVOICED_FLAG
, RAE.PA_ADDITION_FLAG
, RAE.LAST_UPDATE_DATE
, RAE.LAST_UPDATED_BY
, RAE.CREATION_DATE
, RAE.CREATED_BY
, RAE.LAST_UPDATE_LOGIN
, RAE.REQUEST_ID
, RAE.PROGRAM_APPLICATION_ID
, RAE.PROGRAM_ID
, RAE.PROGRAM_UDPATE_DATE
FROM RCV_ACCOUNTING_EVENTS RAE
, RCV_TRANSACTIONS RT
, PO_HEADERS POH
, PO_LINES POL
, PO_LINE_LOCATIONS POLL
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, RCV_ACCOUNTING_EVENT_TYPES RAET
, MTL_SYSTEM_ITEMS_VL MSI
, CST_ORGANIZATION_DEFINITIONS COD
, MTL_UNITS_OF_MEASURE MUOMTXN
, MTL_UNITS_OF_MEASURE MUOMPRIM
, PO_RELEASES POR
WHERE RAE.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND PRL.REQUISITION_LINE_ID(+) = RT.REQUISITION_LINE_ID
AND PRH.REQUISITION_HEADER_ID(+) = PRL.REQUISITION_HEADER_ID
AND RAE.PO_HEADER_ID = POH.PO_HEADER_ID
AND RAE.PO_LINE_ID = POL.PO_LINE_ID
AND RAE.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND COD.ORGANIZATION_ID = RAE.ORGANIZATION_ID
AND RAE.EVENT_TYPE_ID = RAET.EVENT_TYPE_ID
AND POV.VENDOR_ID(+) = RT.VENDOR_ID
AND PVS.VENDOR_SITE_ID(+) = POH.VENDOR_SITE_ID
AND MSI.INVENTORY_ITEM_ID(+) = RAE.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID (+) = RAE.ORGANIZATION_ID
AND NVL(POLL.ACCRUE_ON_RECEIPT_FLAG
, 'N') = 'Y'
AND RAE.EVENT_TYPE_ID NOT IN (18
, 19
, 20
, 14)
AND MUOMTXN.UNIT_OF_MEASURE(+) = RAE.TRANSACTION_UNIT_OF_MEASURE
AND MUOMPRIM.UNIT_OF_MEASURE(+) = RAE.PRIMARY_UNIT_OF_MEASURE
AND POR.PO_RELEASE_ID (+) = RAE.PO_RELEASE_ID
AND POL.ITEM_ID(+) = RAE.INVENTORY_ITEM_ID
AND RT.PO_HEADER_ID IS NOT NULL
AND POH.PO_HEADER_ID = RT.PO_HEADER_ID
AND POL.PO_LINE_ID = RT.PO_LINE_ID
AND RT.DESTINATION_TYPE_CODE != 'INVENTORY' UNION ALL SELECT RAE.ROWID
, RAE.ORGANIZATION_ID
, RAE.ORG_ID
, COD.ORGANIZATION_NAME
, COD.ORGANIZATION_CODE
, COD.SET_OF_BOOKS_ID
, RAE.RCV_TRANSACTION_ID
, RAE.ACCOUNTING_EVENT_ID
, RAE.EVENT_TYPE_ID
, RAET.EVENT_TYPE_NAME
, RAE.TRANSACTION_DATE
, RAE.TRANSACTION_QUANTITY
, MUOMTXN.UOM_CODE
, RAE.PRIMARY_QUANTITY
, MUOMPRIM.UOM_CODE
, POH.PO_HEADER_ID
, NVL(POH.CLM_DOCUMENT_NUMBER
, POH.SEGMENT1)
, POL.PO_LINE_ID
, NVL(POL.LINE_NUM_DISPLAY
, TO_CHAR(POL.LINE_NUM))
, RAE.PO_DISTRIBUTION_ID
, RAE.PO_RELEASE_ID
, POR.RELEASE_NUM
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, RAE.INVENTORY_ITEM_ID
, NVL(MSI.DESCRIPTION
, POL.ITEM_DESCRIPTION)
, TO_CHAR(NULL)
, RAE.UNIT_PRICE
, RAE.PRIOR_UNIT_PRICE
, RAE.UNIT_LANDED_COST
, RAE.INTERCOMPANY_PRICING_OPTION
, RAE.TRANSACTION_AMOUNT
, RAE.NR_TAX
, RAE.REC_TAX
, RAE.NR_TAX_AMOUNT
, RAE.REC_TAX_AMOUNT
, RAE.PRIOR_NR_TAX
, RAE.PRIOR_REC_TAX
, RAE.CURRENCY_CODE
, RAE.CURRENCY_CONVERSION_TYPE
, RAE.CURRENCY_CONVERSION_RATE
, RAE.CURRENCY_CONVERSION_DATE
, RAE.ACCOUNTED_FLAG
, RAE.PROCUREMENT_ORG_FLAG
, RAE.CROSS_OU_FLAG
, RAE.TRX_FLOW_HEADER_ID
, RAE.INVOICED_FLAG
, RAE.PA_ADDITION_FLAG
, RAE.LAST_UPDATE_DATE
, RAE.LAST_UPDATED_BY
, RAE.CREATION_DATE
, RAE.CREATED_BY
, RAE.LAST_UPDATE_LOGIN
, RAE.REQUEST_ID
, RAE.PROGRAM_APPLICATION_ID
, RAE.PROGRAM_ID
, RAE.PROGRAM_UDPATE_DATE
FROM RCV_ACCOUNTING_EVENTS RAE
, PO_HEADERS POH
, PO_LINES POL
, PO_LINE_LOCATIONS POLL
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, RCV_ACCOUNTING_EVENT_TYPES RAET
, MTL_SYSTEM_ITEMS_VL MSI
, CST_ORGANIZATION_DEFINITIONS COD
, MTL_UNITS_OF_MEASURE MUOMTXN
, MTL_UNITS_OF_MEASURE MUOMPRIM
, PO_RELEASES POR
WHERE RAE.PO_HEADER_ID = POH.PO_HEADER_ID
AND RAE.PO_LINE_ID = POL.PO_LINE_ID
AND RAE.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND COD.ORGANIZATION_ID = RAE.ORGANIZATION_ID
AND RAE.EVENT_TYPE_ID = RAET.EVENT_TYPE_ID
AND POV.VENDOR_ID = POH.VENDOR_ID
AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND MSI.INVENTORY_ITEM_ID(+) = RAE.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID (+) = RAE.ORGANIZATION_ID
AND NVL(POLL.ACCRUE_ON_RECEIPT_FLAG
, 'N') = 'N'
AND RAE.EVENT_TYPE_ID IN (13
, 14)
AND MUOMTXN.UNIT_OF_MEASURE(+) = RAE.TRANSACTION_UNIT_OF_MEASURE
AND MUOMPRIM.UNIT_OF_MEASURE(+) = RAE.PRIMARY_UNIT_OF_MEASURE
AND POR.PO_RELEASE_ID (+) = RAE.PO_RELEASE_ID