CMR_XLA_CONSIGN_RECEIPT_TXNS

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 INVENTORY_ITEM_ID ITEM_NUMBER INV_ORG_ID INV_ORG_CODE BUSINESS_UNIT_ID BUSINESS_UNIT_NAME

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

INVENTORY_ITEM_ID

ITEM_NUMBER

INV_ORG_ID

INV_ORG_CODE

BUSINESS_UNIT_ID

BUSINESS_UNIT_NAME

Query

SQL_Statement
SELECT cre.accounting_event_id accounting_event_id , cre.cmr_rcv_transaction_id cmr_rcv_transaction_id , cre.cmr_po_distribution_id cmr_po_distribution_id , vl.transaction_type_name transaction_type , cre.transaction_date transaction_date , cpod.po_number po_number , DECODE(cre.event_type_code, 'CS_RETRO_PRICE_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 , cre.source_doc_qty quantity , iuom.unit_of_measure uom_code , cre.inventory_item_id inventory_item_id , esi.item_number item_number , cre.inventory_org_id inv_org_id , iop.organization_code inv_org_code , cre.business_unit_id business_unit_id , haous.name business_unit_name FROM cmr_transaction_types_vl vl, cmr_purchase_order_dtls cpod , egp_system_items_vl esi , inv_org_parameters iop , hr_all_organization_units haous , poz_suppliers_v ps , poz_supplier_sites_v pss , cmr_rcv_events cre , inv_units_of_measure_vl iuom WHERE cre.cmr_po_distribution_id = cpod.cmr_po_distribution_id AND cre.inventory_item_id = esi.inventory_item_id(+) AND cre.inventory_org_id = esi.organization_id(+) AND iop.organization_id = cre.inventory_org_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 iuom.uom_code = cre.source_doc_uom_code AND cre.consigned_flag ='Y' AND cre.event_class_code = 'CS_RECEIPT_ACCRUAL' AND cre.transaction_type_code = vl.transaction_type_code

SQL_Statement

SELECT cre.accounting_event_id accounting_event_id ,

cre.cmr_rcv_transaction_id cmr_rcv_transaction_id ,

cre.cmr_po_distribution_id cmr_po_distribution_id ,

vl.transaction_type_name transaction_type ,

cre.transaction_date transaction_date ,

cpod.po_number po_number ,

DECODE(cre.event_type_code, 'CS_RETRO_PRICE_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 ,

cre.source_doc_qty quantity ,

iuom.unit_of_measure uom_code ,

cre.inventory_item_id inventory_item_id ,

esi.item_number item_number ,

cre.inventory_org_id inv_org_id ,

iop.organization_code inv_org_code ,

cre.business_unit_id business_unit_id ,

haous.name business_unit_name

FROM

cmr_transaction_types_vl vl,

cmr_purchase_order_dtls cpod ,

egp_system_items_vl esi ,

inv_org_parameters iop ,

hr_all_organization_units haous ,

poz_suppliers_v ps ,

poz_supplier_sites_v pss ,

cmr_rcv_events cre ,

inv_units_of_measure_vl iuom

WHERE

cre.cmr_po_distribution_id = cpod.cmr_po_distribution_id

AND cre.inventory_item_id = esi.inventory_item_id(+)

AND cre.inventory_org_id = esi.organization_id(+)

AND iop.organization_id = cre.inventory_org_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 iuom.uom_code = cre.source_doc_uom_code

AND cre.consigned_flag ='Y'

AND cre.event_class_code = 'CS_RECEIPT_ACCRUAL'

AND cre.transaction_type_code = vl.transaction_type_code