CST_XLA_COST_ADJUSTMENT_TXNS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
DISTRIBUTION_ID COST_ORG_NAME COST_BOOK_CODE ITEM_NUMBER QUANTITY UOM_CODE TRANSACTION_DATE VAL_UNIT_CODE TRANSACTION_SOURCE TRANSACTION_REFERENCE

Name

DISTRIBUTION_ID

COST_ORG_NAME

COST_BOOK_CODE

ITEM_NUMBER

QUANTITY

UOM_CODE

TRANSACTION_DATE

VAL_UNIT_CODE

TRANSACTION_SOURCE

TRANSACTION_REFERENCE

Query

SQL_Statement
SELECT DISTINCT ccd.distribution_id distribution_id, cco.cost_org_name cost_org_name , ccb.cost_book_code cost_book_code , esi.item_number item_number , ct.quantity quantity , iuom.unit_of_measure uom_code , cua.eff_date transaction_date , cvu.val_unit_code val_unit_code , DECODE(cua.transaction_id, 0, 'Manual Cost Adjustment', 'Receipt Cost Adjustment') transaction_source , 'Reference' transaction_reference FROM cst_user_adjustments cua , cst_cost_orgs_v cco , cst_cost_books_b ccb , cst_val_units_b cvu , egp_system_items_vl esi , cst_transactions ct , cst_cost_org_parameters ccop , cst_transaction_costs ctc , inv_units_of_measure_vl iuom, cst_cost_distributions ccd WHERE cua.cost_org_id = cco.cost_org_id AND ccop.cost_org_id = cua.cost_org_id AND cua.cost_book_id = ccb.cost_book_id AND cua.val_unit_id = cvu.val_unit_id AND esi.inventory_item_id = cua.inventory_item_id AND esi.organization_id = ccop.master_organization_id AND ct.transaction_id (+) = cua.transaction_id AND ctc.user_adjustment_id = cua.user_adjustment_id AND ctc.transaction_id = ccd.transaction_id AND ctc.eff_date = ccd.eff_date AND iuom.uom_code = ct.uom_code UNION ALL SELECT DISTINCT ccd.distribution_id distribution_id, cco.cost_org_name cost_org_name , ccb.cost_book_code cost_book_code , esi.item_number item_number , ct.quantity quantity , iuom.unit_of_measure uom_code , ct.transaction_date transaction_date , cvu.val_unit_code val_unit_code , 'Standard Cost Adjustment' transaction_source , 'Reference' transaction_reference FROM cst_transactions ct , cst_cost_orgs_v cco , cst_cost_books_b ccb , cst_val_units_b cvu , egp_system_items_vl esi , cst_cost_org_parameters ccop, cst_cost_distributions ccd, inv_units_of_measure_vl iuom WHERE ct.cost_org_id = cco.cost_org_id AND ccop.cost_org_id = ct.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 = ccop.master_organization_id AND ct.transaction_id = ccd.transaction_id AND iuom.uom_code = ct.uom_code AND ct.base_txn_type_id = 14 AND ct.base_txn_source_type_id = 0 AND ct.base_txn_action_id = 14

SQL_Statement

SELECT DISTINCT ccd.distribution_id distribution_id,

cco.cost_org_name cost_org_name ,

ccb.cost_book_code cost_book_code ,

esi.item_number item_number ,

ct.quantity quantity ,

iuom.unit_of_measure uom_code ,

cua.eff_date transaction_date ,

cvu.val_unit_code val_unit_code ,

DECODE(cua.transaction_id, 0, 'Manual Cost Adjustment', 'Receipt Cost Adjustment') transaction_source ,

'Reference' transaction_reference

FROM cst_user_adjustments cua ,

cst_cost_orgs_v cco ,

cst_cost_books_b ccb ,

cst_val_units_b cvu ,

egp_system_items_vl esi ,

cst_transactions ct ,

cst_cost_org_parameters ccop ,

cst_transaction_costs ctc ,

inv_units_of_measure_vl iuom,

cst_cost_distributions ccd

WHERE cua.cost_org_id = cco.cost_org_id

AND ccop.cost_org_id = cua.cost_org_id

AND cua.cost_book_id = ccb.cost_book_id

AND cua.val_unit_id = cvu.val_unit_id

AND esi.inventory_item_id = cua.inventory_item_id

AND esi.organization_id = ccop.master_organization_id

AND ct.transaction_id (+) = cua.transaction_id

AND ctc.user_adjustment_id = cua.user_adjustment_id

AND ctc.transaction_id = ccd.transaction_id

AND ctc.eff_date = ccd.eff_date

AND iuom.uom_code = ct.uom_code

UNION ALL

SELECT DISTINCT ccd.distribution_id distribution_id,

cco.cost_org_name cost_org_name ,

ccb.cost_book_code cost_book_code ,

esi.item_number item_number ,

ct.quantity quantity ,

iuom.unit_of_measure uom_code ,

ct.transaction_date transaction_date ,

cvu.val_unit_code val_unit_code ,

'Standard Cost Adjustment' transaction_source ,

'Reference' transaction_reference

FROM cst_transactions ct ,

cst_cost_orgs_v cco ,

cst_cost_books_b ccb ,

cst_val_units_b cvu ,

egp_system_items_vl esi ,

cst_cost_org_parameters ccop,

cst_cost_distributions ccd,

inv_units_of_measure_vl iuom

WHERE ct.cost_org_id = cco.cost_org_id

AND ccop.cost_org_id = ct.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 = ccop.master_organization_id

AND ct.transaction_id = ccd.transaction_id

AND iuom.uom_code = ct.uom_code

AND ct.base_txn_type_id = 14

AND ct.base_txn_source_type_id = 0

AND ct.base_txn_action_id = 14