CMR_XLA_TRO_TXNS

Details

Schema: FUSION
Object owner: CMR
Object type: VIEW

Columns

Name
CST_TRANSFER_ORDER_DIST_ID ACCOUNTING_EVENT_ID CMR_RCV_TRANSACTION_ID TRANSFER_ORDER_DISTRIBUTION_ID TRANSFER_ORDER_LINE_ID TRANSACTION_TYPE TRANSACTION_DATE TRO_NUMBER RECEIPT_NUMBER TRO_DISTRIBUTION_NUMBER QUANTITY UOM_CODE INVENTORY_ITEM_ID ITEM_NUMBER INV_ORG_ID INV_ORG_CODE BUSINESS_UNIT_ID BUSINESS_UNIT_NAME TRANSFER_ORDER_CHARGE_ACCOUNT SFO_AGREEMENT_NUMBER PHYSICAL_RETURN_REQUIRED PRIOR_INVENTORY_ORG_CODE SHIP_FROM_INVENTORY_ORG_CODE

Name

CST_TRANSFER_ORDER_DIST_ID

ACCOUNTING_EVENT_ID

CMR_RCV_TRANSACTION_ID

TRANSFER_ORDER_DISTRIBUTION_ID

TRANSFER_ORDER_LINE_ID

TRANSACTION_TYPE

TRANSACTION_DATE

TRO_NUMBER

RECEIPT_NUMBER

TRO_DISTRIBUTION_NUMBER

QUANTITY

UOM_CODE

INVENTORY_ITEM_ID

ITEM_NUMBER

INV_ORG_ID

INV_ORG_CODE

BUSINESS_UNIT_ID

BUSINESS_UNIT_NAME

TRANSFER_ORDER_CHARGE_ACCOUNT

SFO_AGREEMENT_NUMBER

PHYSICAL_RETURN_REQUIRED

PRIOR_INVENTORY_ORG_CODE

SHIP_FROM_INVENTORY_ORG_CODE

Query

SQL_Statement
SELECT ctod.cst_transfer_order_dist_id, cre.accounting_event_id accounting_event_id, cre.cmr_rcv_transaction_id cmr_rcv_transaction_id, cre.transfer_order_dist_id transfer_order_distribution_id, cre.transfer_order_line_id transfer_order_line_id, cret.transaction_type transaction_type, cre.transaction_date transaction_date, cre.source_doc_number tro_number, DECODE( cre.cmr_rcv_transaction_id, NULL, NULL, cre.sla_transaction_number ) receipt_number, ctod.distribution_number tro_distribution_number, 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, ctod.charge_account_id transfer_order_charge_account, (select min(agreement_number) from cst_trade_events cte where cte.inv_transaction_id = cre.inv_shipping_transaction_id and transaction_type = 'RECEIPT_ACCRUAL') sfo_agreement_number, NULL physical_return_required, (select iop_in.organization_code from inv_org_parameters iop_in where iop_in.organization_id = cre.prior_inventory_org_id) prior_inventory_org_code, (select iop_in.organization_code from inv_org_parameters iop_in where iop_in.organization_id = cre.ship_from_inv_org_id) ship_from_inventory_org_code FROM cst_transfer_order_dists ctod, egp_system_items_vl esi, inv_org_parameters iop, hr_all_organization_units haous, cmr_rcv_events cre, cmr_rcv_event_types cret, inv_units_of_measure_vl iuom WHERE cre.event_class_code = 'TRO_RECEIPTS' AND cret.event_type_code = cre.event_type_code AND cret.event_class_code = cre.event_class_code 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 iuom.uom_code = cre.source_doc_uom_code AND cre.transfer_order_dist_id = ctod.distribution_id (+) AND ctod.external_system_reference(+) = 'FUSION' AND TO_CHAR(cre.transfer_order_line_id) = ctod.external_system_ref_id (+)

SQL_Statement

SELECT

ctod.cst_transfer_order_dist_id,

cre.accounting_event_id accounting_event_id,

cre.cmr_rcv_transaction_id cmr_rcv_transaction_id,

cre.transfer_order_dist_id transfer_order_distribution_id,

cre.transfer_order_line_id transfer_order_line_id,

cret.transaction_type transaction_type,

cre.transaction_date transaction_date,

cre.source_doc_number tro_number,

DECODE( cre.cmr_rcv_transaction_id, NULL, NULL, cre.sla_transaction_number ) receipt_number,

ctod.distribution_number tro_distribution_number,

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,

ctod.charge_account_id transfer_order_charge_account,

(select min(agreement_number) from cst_trade_events cte where cte.inv_transaction_id = cre.inv_shipping_transaction_id and transaction_type = 'RECEIPT_ACCRUAL') sfo_agreement_number,

NULL physical_return_required,

(select iop_in.organization_code from inv_org_parameters iop_in where iop_in.organization_id = cre.prior_inventory_org_id) prior_inventory_org_code,

(select iop_in.organization_code from inv_org_parameters iop_in where iop_in.organization_id = cre.ship_from_inv_org_id) ship_from_inventory_org_code

FROM

cst_transfer_order_dists ctod,

egp_system_items_vl esi,

inv_org_parameters iop,

hr_all_organization_units haous,

cmr_rcv_events cre,

cmr_rcv_event_types cret,

inv_units_of_measure_vl iuom

WHERE

cre.event_class_code = 'TRO_RECEIPTS'

AND cret.event_type_code = cre.event_type_code

AND cret.event_class_code = cre.event_class_code

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 iuom.uom_code = cre.source_doc_uom_code

AND cre.transfer_order_dist_id = ctod.distribution_id (+)

AND ctod.external_system_reference(+) = 'FUSION'

AND TO_CHAR(cre.transfer_order_line_id) = ctod.external_system_ref_id (+)