CST_XLA_INVENTORY_TXNS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
DISTRIBUTION_ID COST_ORG_NAME COST_BOOK_CODE FROM_INV_ORG_CODE FROM_SUBINVENTORY_CODE INV_ORG_CODE ITEM_NUMBER QUANTITY SUBINVENTORY_CODE TO_INV_ORG_CODE TO_SUBINVENTORY_CODE TRANSACTION_DATE TRANSACTION_REFERENCE TXN_SOURCE_REF_DOC_NUMBER UOM_CODE VAL_UNIT_CODE

Name

DISTRIBUTION_ID

COST_ORG_NAME

COST_BOOK_CODE

FROM_INV_ORG_CODE

FROM_SUBINVENTORY_CODE

INV_ORG_CODE

ITEM_NUMBER

QUANTITY

SUBINVENTORY_CODE

TO_INV_ORG_CODE

TO_SUBINVENTORY_CODE

TRANSACTION_DATE

TRANSACTION_REFERENCE

TXN_SOURCE_REF_DOC_NUMBER

UOM_CODE

VAL_UNIT_CODE

Query

SQL_Statement
SELECT DISTINCT ccd.distribution_id distribution_id, cco.cost_org_name cost_org_name , ccb.cost_book_code cost_book_code , DECODE(SIGN(quantity),1,iop2.organization_code , -1,iop.organization_code) from_inv_org_code , DECODE(SIGN(quantity),1,cit.transfer_subinventory_code , -1,cit.subinventory_code) from_subinventory_code , iop.organization_code inv_org_code , esi.item_number item_number , ct.quantity quantity , cit.subinventory_code subinventory_code , DECODE(SIGN(quantity),1,iop.organization_code , -1,iop2.organization_code) to_inv_org_code , DECODE(SIGN(quantity),1,cit.subinventory_code , -1,cit.transfer_subinventory_code) to_subinventory_code , ct.transaction_date transaction_date , 'Reference' transaction_reference , ct.txn_source_ref_doc_number txn_source_ref_doc_number, iuom.unit_of_measure uom_code , cvu.val_unit_code val_unit_code FROM cst_transactions ct , cst_inv_transactions cit , cst_cost_orgs_v cco , cst_cost_books_b ccb , cst_val_units_b cvu , egp_system_items_vl esi , inv_org_parameters iop , inv_org_parameters iop2 , cst_cost_distributions ccd, inv_units_of_measure_vl iuom WHERE ct.cst_inv_transaction_id = cit.cst_inv_transaction_id AND ct.cost_org_id = cco.cost_org_id AND ct.cost_book_id = ccb.cost_book_id AND ct.val_unit_id = cvu.val_unit_id AND esi.inventory_item_id = ct.inventory_item_id AND esi.organization_id = ct.inventory_org_id AND iop.organization_id = ct.inventory_org_id AND iop2.organization_id (+) = cit.transfer_inventory_org_id AND ct.transaction_id = ccd.transaction_id AND iuom.uom_code = ct.uom_code

SQL_Statement

SELECT DISTINCT ccd.distribution_id distribution_id,

cco.cost_org_name cost_org_name ,

ccb.cost_book_code cost_book_code ,

DECODE(SIGN(quantity),1,iop2.organization_code , -1,iop.organization_code) from_inv_org_code ,

DECODE(SIGN(quantity),1,cit.transfer_subinventory_code , -1,cit.subinventory_code) from_subinventory_code ,

iop.organization_code inv_org_code ,

esi.item_number item_number ,

ct.quantity quantity ,

cit.subinventory_code subinventory_code ,

DECODE(SIGN(quantity),1,iop.organization_code , -1,iop2.organization_code) to_inv_org_code ,

DECODE(SIGN(quantity),1,cit.subinventory_code , -1,cit.transfer_subinventory_code) to_subinventory_code ,

ct.transaction_date transaction_date ,

'Reference' transaction_reference ,

ct.txn_source_ref_doc_number txn_source_ref_doc_number,

iuom.unit_of_measure uom_code ,

cvu.val_unit_code val_unit_code

FROM cst_transactions ct ,

cst_inv_transactions cit ,

cst_cost_orgs_v cco ,

cst_cost_books_b ccb ,

cst_val_units_b cvu ,

egp_system_items_vl esi ,

inv_org_parameters iop ,

inv_org_parameters iop2 ,

cst_cost_distributions ccd,

inv_units_of_measure_vl iuom

WHERE ct.cst_inv_transaction_id = cit.cst_inv_transaction_id

AND ct.cost_org_id = cco.cost_org_id

AND ct.cost_book_id = ccb.cost_book_id

AND ct.val_unit_id = cvu.val_unit_id

AND esi.inventory_item_id = ct.inventory_item_id

AND esi.organization_id = ct.inventory_org_id

AND iop.organization_id = ct.inventory_org_id

AND iop2.organization_id (+) = cit.transfer_inventory_org_id

AND ct.transaction_id = ccd.transaction_id

AND iuom.uom_code = ct.uom_code