CMR_XLA_RECEIVING_TXNS_V

Details

• Schema: FUSION

• Object owner: CMR

• Object type: VIEW

Columns

Name
ACCOUNTING_EVENT_ID CMR_RCV_TRANSACTION_ID CMR_PO_DISTRIBUTION_ID TRANSACTION_TYPE TRANSACTION_DATE PO_NUMBER RECEIPT_NUMBER PO_DISTRIBUTION_NUMBER VENDOR_ID VENDOR_NAME VENDOR_SITE_CODE QUANTITY UOM_CODE PO_PRICE CURRENCY_CODE INVENTORY_ITEM_ID ITEM_NUMBER INVENTORY_ORGANIZATION_ID INV_ORG_CODE BUSINESS_UNIT_ID BUSINESS_UNIT_NAME

Query

SQL_Statement

SELECT cre.accounting_event_id accounting_event_id , DECODE(cre.event_type_code,'EXPENSE_ADJUSTMENT', -1 , cre.cmr_rcv_transaction_id) cmr_rcv_transaction_id , cre.cmr_po_distribution_id cmr_po_distribution_id , txntypes.transaction_type_name transaction_type , cre.transaction_date transaction_date , cpod.po_number po_number , DECODE(cre.event_type_code,'EXPENSE_ADJUSTMENT',NULL,cre.sla_transaction_number) receipt_number , cpod.distribution_number po_distribution_number , cpod.vendor_id vendor_id , ps.vendor_name vendor_name , pss.vendor_site_code vendor_site_code , DECODE(event_type_code,'EXPENSE_ADJUSTMENT',NULL,cre.source_doc_qty) quantity , DECODE(event_type_code,'EXPENSE_ADJUSTMENT',NULL,iuom.unit_of_measure) uom_code , cpod.price + nvl(cpod.nonrecoverable_tax,0) po_price , cpod.currency_code currency_code , cre.inventory_item_id inventory_item_id , esi.item_number item_number , cre.inventory_org_id inventory_organization_id , iop.organization_code inv_org_code , cre.business_unit_id business_unit_id , haous.name business_unit_name FROM fusion.cmr_purchase_order_dtls cpod , fusion.egp_system_items_vl esi , fusion.inv_org_parameters iop , fusion.hr_all_organization_units haous , fusion.poz_suppliers_v ps , fusion.poz_supplier_sites_v pss , fusion.cmr_rcv_events cre , fusion.inv_units_of_measure_vl iuom , FUSION.cmr_transaction_types_vl txntypes WHERE cre.cmr_po_distribution_id = cpod.cmr_po_distribution_id AND esi.inventory_item_id(+) = cre.inventory_item_id AND cre.inventory_org_id = esi.organization_id(+) AND cre.inventory_org_id = iop.organization_id AND haous.organization_id = cre.business_unit_id AND cpod.vendor_id = ps.vendor_id AND cpod.vendor_site_id = pss.vendor_site_id AND cpod.active_flag = 'Y' AND cre.cmr_po_distribution_id = cpod.cmr_po_distribution_id AND cre.event_type_code not in ('RETRO_PRICE_ADJUSTMENT') AND cre.transaction_type_code = txntypes.transaction_type_code AND iuom.uom_code (+) = cre.source_doc_uom_code