CST_ITEM_COST_HISTORY_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name

COST_ORG_ID COST_BOOK_ID INVENTORY_ITEM_ID VAL_UNIT_ID TRANSACTION_ID COST_DATE EFF_DATE COST_METHOD_CODE MATERIAL_COST OVERHEAD_COST PROFIT_IN_INVENTORY_COST MATERIAL_OVERHEAD_COST RESOURCE_COST RESOURCE_DIRECT_COST RESOURCE_OVERHEAD_COST TOTAL_COST CURRENCY_CODE PRIOR_PERIOD_COST PRIOR_PERIOD_ONHAND QUANTITY_ONHAND TRANSACTION_TYPE TRANSACTION_SOURCE_TYPE TRANSACTION_NUMBER REFERENCE_TYPE REFERENCE_NUMBER TRANSACTION_DATE TRANSACTION_QUANTITY TRANSACTION_COST VARIANCE_COST LE_TIMEZONE_CODE LE_ENABLED_FLAG COST_DATE_DISPLAY PERIOD_END_DATE PAC_ADJUSTMENT ADJUSTED_PRIOR_PERIOD_COST

Query

SQL_Statement

SELECT DISTINCT a.cost_org_id , a.cost_book_id , a.inventory_item_id , a.val_unit_id , a.transaction_id , a.cost_date , a.eff_date , a.cost_method_code , a.material_cost , a.overhead_cost , a.profit_in_inventory_cost , a.material_overhead_cost, a.resource_cost, a.resource_direct_cost, a.resource_overhead_cost, a.total_cost , a.currency_code, a.prior_period_cost, a.prior_period_onhand, a.quantity_onhand , c.base_txn_type_name transaction_type , a.transaction_source_type , a.transaction_number , a.reference_type , a.reference_number , DECODE(b.base_txn_source_type_id, 0, NVL(a.adjustment_date,b.eff_date), a.transaction_date) transaction_date , a.transaction_quantity , a.transaction_cost, a.variance_cost, a.LE_TIMEZONE_CODE LE_TIMEZONE_CODE, ctsa.LE_ENABLED_FLAG, a.cost_date cost_date_display, NULL period_end_date, CAST('N' AS VARCHAR2(1)) pac_adjustment, CAST(NULL AS NUMBER) adjusted_prior_period_cost FROM (SELECT cpc.cost_org_id , cpc.cost_book_id , cpc.inventory_item_id , cpc.val_unit_id , cpc.transaction_id , cpc.cost_date , cpc.eff_date , 'PERPETUAL_AVERAGE' cost_method_code , SUM( CASE WHEN cce.cost_element_type = 'MATERIAL' THEN cpc.unit_cost_average ELSE 0 END) AS material_cost , SUM( CASE WHEN cce.cost_element_type = 'OVERHEAD' THEN cpc.unit_cost_average ELSE 0 END) AS overhead_cost , SUM( CASE WHEN cce.cost_element_type = 'PROFIT_IN_INVENTORY' THEN cpc.unit_cost_average ELSE 0 END) AS profit_in_inventory_cost , SUM( CASE WHEN cce.cost_element_type = 'MATERIAL_OVERHEAD' THEN cpc.unit_cost_average ELSE 0 END) AS material_overhead_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE' THEN cpc.unit_cost_average ELSE 0 END) AS resource_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE_DIRECT' THEN cpc.unit_cost_average ELSE 0 END) AS resource_direct_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE_OVERHEAD' THEN cpc.unit_cost_average ELSE 0 END) AS resource_overhead_cost , SUM(cpc.unit_cost_average) AS total_cost , cpc.currency_code , CAST (NULL AS NUMBER) AS prior_period_cost, CAST (NULL AS NUMBER) AS prior_period_onhand, MIN(cpc.quantity_onhand) AS quantity_onhand , csit.base_txn_source_type_id transaction_source_type , ct.txn_source_doc_number transaction_number , ct.txn_source_ref_doc_type reference_type , ct.txn_source_ref_doc_number reference_number , ct.transaction_date transaction_date , (select max(ctc2.adjustment_date) from cst_transaction_costs ctc2 where ctc2.transaction_id = cpc.transaction_id and ctc2.eff_date = cpc.eff_date and ctc2.adjustment_date is not null) adjustment_date, MIN(ct.quantity) AS transaction_quantity , SUM(cpc.unit_cost_new + cpc.unit_cost_adjust) AS transaction_cost, NULL variance_cost, ct.LE_TIMEZONE_CODE LE_TIMEZONE_CODE FROM cst_perpavg_cost cpc , cst_transactions ct , cst_inv_transactions csit , cst_cost_elements_b cce WHERE ct.transaction_id = cpc.transaction_id AND csit.cst_inv_transaction_id(+) = ct.cst_inv_transaction_id AND cce.cost_element_id = cpc.cost_element_id GROUP BY cpc.cost_org_id, cpc.cost_book_id, cpc.inventory_item_id, cpc.val_unit_id, cpc.transaction_id, cpc.cost_date, cpc.eff_date, 'PERPETUAL_AVERAGE', csit.base_txn_source_type_id, ct.txn_source_doc_number, ct.txn_source_ref_doc_type, ct.txn_source_ref_doc_number, ct.transaction_date,ct.LE_TIMEZONE_CODE, cpc.currency_code UNION ALL SELECT b.cost_org_id , b.cost_book_id , b.INVENTORY_ITEM_ID , b.val_unit_id , b.transaction_id , b.cost_date , b.eff_date , b.cost_method_code , b.material_cost , b.overhead_cost , b.profit_in_inventory_cost , b.material_overhead_cost, b.resource_cost, b.resource_direct_cost, b.resource_overhead_cost, b.total_cost , b.currency_code, b.prior_period_cost, b.prior_period_onhand, (SELECT DISTINCT LAST_VALUE (L.QUANTITY_ONHAND) OVER (PARTITION BY L.COST_ORG_ID, L.COST_BOOK_ID, L.VAL_UNIT_ID, L.INVENTORY_ITEM_ID ORDER BY L.COST_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM CST_TRANSACTION_LAYERS L WHERE L.POSTED_FLAG = 'Y' AND L.QUANTITY_ONHAND IS NOT NULL AND L.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID AND L.rec_trxn_ID = b.TRANSACTION_ID AND L.COST_BOOK_ID = b.COST_BOOK_ID AND L.VAL_UNIT_ID = b.VAL_UNIT_ID AND L.COST_ORG_ID = b.COST_ORG_ID ) AS quantity_onhand , b.transaction_source_type , b.transaction_number , b.reference_type , b.reference_number , b.transaction_date , b.adjustment_date , b.transaction_quantity , b.transaction_cost, NULL variance_cost, b.LE_TIMEZONE_CODE LE_TIMEZONE_CODE FROM (SELECT ct.cost_org_id , ct.cost_book_id , ct.inventory_item_id , ct.val_unit_id , ctc.transaction_id , NVL(ctc.cost_date ,ct.cost_date) cost_date , ctc.eff_date eff_date , 'ACTUAL' cost_method_code , SUM( CASE WHEN cce.cost_element_type = 'MATERIAL' THEN ctc.unit_cost ELSE 0 END) AS material_cost , SUM( CASE WHEN cce.cost_element_type = 'OVERHEAD' THEN ctc.unit_cost ELSE 0 END) AS overhead_cost , SUM( CASE WHEN cce.cost_element_type = 'PROFIT_IN_INVENTORY' THEN ctc.unit_cost ELSE 0 END) AS profit_in_inventory_cost , SUM( CASE WHEN cce.cost_element_type = 'MATERIAL_OVERHEAD' THEN ctc.unit_cost ELSE 0 END) AS material_overhead_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE' THEN ctc.unit_cost ELSE 0 END) AS resource_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE_DIRECT' THEN ctc.unit_cost ELSE 0 END) AS resource_direct_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE_OVERHEAD' THEN ctc.unit_cost ELSE 0 END) AS resource_overhead_cost , SUM(ctc.unit_cost) AS total_cost , ctc.currency_code, CAST (NULL AS NUMBER) AS prior_period_cost, CAST (NULL AS NUMBER) AS prior_period_onhand, csit.base_txn_source_type_id transaction_source_type , ct.txn_source_doc_number transaction_number , ct.txn_source_ref_doc_type reference_type , ct.txn_source_ref_doc_number reference_number , ct.transaction_date transaction_date , MAX(ctc.adjustment_date) adjustment_date, MIN(ct.quantity) AS transaction_quantity , SUM(ctc.unit_cost) AS transaction_cost, ct.LE_TIMEZONE_CODE LE_TIMEZONE_CODE FROM cst_transaction_costs ctc , cst_transactions ct , cst_inv_transactions csit , cst_cost_elements_b cce WHERE ct.transaction_id = ctc.transaction_id AND csit.cst_inv_transaction_id(+) = ct.cst_inv_transaction_id AND cce.cost_element_id = ctc.cost_element_id AND ct.cost_method_code ='ACTUAL' AND ctc.valid_cost_flag ='Y' AND ct.COST_TRANSACTION_TYPE = 'RECEIPT' AND ctc.expense_pool_id <> -5 GROUP BY ct.cost_org_id, ct.cost_book_id, ct.inventory_item_id, ct.val_unit_id, ctc.transaction_id, NVL(ctc.cost_date,ct.cost_date), ctc.eff_date, ctc.currency_code, 'ACTUAL', csit.base_txn_source_type_id, ct.txn_source_doc_number, ct.txn_source_ref_doc_type, ct.txn_source_ref_doc_number, ct.transaction_date, ct.LE_TIMEZONE_CODE )b ) a, cst_transaction_costs b, cst_all_txn_types_v c, CST_TXN_SOURCE_ACTIONS ctsa WHERE a.transaction_id = b.transaction_id AND a.eff_date = b.eff_date AND b.valid_cost_flag = 'Y' AND b.base_txn_type_id = c.base_txn_type_id AND b.base_txn_source_type_id = c.base_txn_source_type_id AND b.base_txn_source_type_id = ctsa.base_txn_source_type_id AND b.BASE_TXN_ACTION_ID = ctsa.BASE_TXN_ACTION_ID AND b.expense_pool_id <> -5 UNION ALL SELECT DISTINCT a.cost_org_id , a.cost_book_id , a.inventory_item_id , a.val_unit_id , a.transaction_id , a.cost_date , a.eff_date , a.cost_method_code , a.material_cost , a.overhead_cost , a.profit_in_inventory_cost , a.material_overhead_cost, a.resource_cost, a.resource_direct_cost, a.resource_overhead_cost, a.total_cost , a.currency_code, a.prior_period_cost, a.prior_period_onhand, a.quantity_onhand , c.base_txn_type_name transaction_type , a.transaction_source_type , a.transaction_number , a.reference_type , a.reference_number , DECODE(a.base_txn_source_type_id, 0, a.cost_date, a.transaction_date) transaction_date , a.transaction_quantity , a.transaction_cost, a.variance_cost, a.LE_TIMEZONE_CODE LE_TIMEZONE_CODE, ctsa.LE_ENABLED_FLAG, a.cost_date cost_date_display, NULL period_end_date, CAST('N' AS VARCHAR2(1)) pac_adjustment, CAST(NULL AS NUMBER) adjusted_prior_period_cost FROM (SELECT csc.cost_org_id , csc.cost_book_id , csc.inventory_item_id , csc.val_unit_id , NVL(ct.transaction_id,0) transaction_id , NVL(ct.cost_date,csc.effective_start_date) cost_date , NVL(ct.cost_date,csc.effective_start_date) eff_date , 'STANDARD' cost_method_code , SUM( CASE WHEN cce.cost_element_type = 'MATERIAL' THEN cscd.unit_cost ELSE 0 END) AS material_cost , SUM( CASE WHEN cce.cost_element_type = 'OVERHEAD' THEN cscd.unit_cost ELSE 0 END) AS overhead_cost , SUM( CASE WHEN cce.cost_element_type = 'PROFIT_IN_INVENTORY' THEN cscd.unit_cost ELSE 0 END) AS profit_in_inventory_cost , SUM( CASE WHEN cce.cost_element_type = 'MATERIAL_OVERHEAD' THEN cscd.unit_cost ELSE 0 END) AS material_overhead_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE' THEN cscd.unit_cost ELSE 0 END) AS resource_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE_DIRECT' THEN cscd.unit_cost ELSE 0 END) AS resource_direct_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE_OVERHEAD' THEN cscd.unit_cost ELSE 0 END) AS resource_overhead_cost , csc.total_cost AS total_cost , csc.currency_code, CAST (NULL AS NUMBER) AS prior_period_cost, CAST (NULL AS NUMBER) AS prior_period_onhand, ctl.quantity_onhand AS quantity_onhand, ct.base_txn_source_type_id transaction_source_type, ct.txn_source_doc_number transaction_number, ct.txn_source_ref_doc_type reference_type, ct.txn_source_ref_doc_number reference_number, ct.transaction_date transaction_date, ct.quantity AS transaction_quantity, csc.total_cost AS transaction_cost, ct.base_txn_type_id AS base_txn_type_id, ct.base_txn_source_type_id AS base_txn_source_type_id, sum(cvc.unit_cost) variance_cost, ct.LE_TIMEZONE_CODE LE_TIMEZONE_CODE FROM cst_std_costs csc, cst_std_cost_details cscd , cst_transactions ct , cst_cost_elements_b cce, cst_transaction_layers ctl, cst_variance_costs cvc WHERE ct.cost_org_id = csc.cost_org_id AND ct.cost_book_id = csc.cost_book_id AND ct.val_unit_id = csc.val_unit_id AND ct.inventory_item_id = csc.inventory_item_id AND TRUNC(ct.cost_date) <= csc.effective_end_date AND TRUNC(ct.cost_date) >= csc.effective_start_date AND csc.status_code = 'PUBLISHED' AND csc.std_cost_id = cscd.std_cost_id AND cce.cost_element_id = cscd.cost_element_id AND ct.transaction_id = ctl.transaction_id AND ct.transaction_id = ctl.rec_trxn_id AND 0 = ctl.dep_trxn_id AND ct.COST_TRANSACTION_TYPE = 'RECEIPT' AND ct.cost_method_code = 'STANDARD' AND ct.transaction_id = cvc.transaction_id (+) AND ct.transaction_id = cvc.rec_trxn_id(+) AND 0 = cvc.dep_trxn_id(+) AND ( cvc.cost_element_id IS NULL OR cvc.cost_element_id = cscd.cost_element_id ) GROUP BY csc.cost_org_id, csc.cost_book_id, csc.inventory_item_id, csc.val_unit_id, ct.transaction_id, ct.cost_date, csc.currency_code, 'STANDARD', ct.base_txn_source_type_id, ct.txn_source_doc_number, ct.txn_source_ref_doc_type, ct.txn_source_ref_doc_number, ct.transaction_date, csc.total_cost, ct.quantity, ctl.quantity_onhand, csc.effective_start_date, ct.base_txn_type_id, ct.base_txn_source_type_id, ct.LE_TIMEZONE_CODE ) a, cst_all_txn_types_v c, CST_TXN_SOURCE_ACTIONS ctsa WHERE a.base_txn_type_id = c.base_txn_type_id AND a.base_txn_source_type_id = c.base_txn_source_type_id AND c.base_txn_source_type_id = ctsa.base_txn_source_type_id AND c.BASE_TXN_ACTION_ID = ctsa.BASE_TXN_ACTION_ID UNION ALL SELECT DISTINCT a.cost_org_id , a.cost_book_id , a.inventory_item_id , a.val_unit_id , a.transaction_id , a.cost_date , a.eff_date , a.cost_method_code , a.material_cost , a.overhead_cost , a.profit_in_inventory_cost , a.material_overhead_cost, a.resource_cost, a.resource_direct_cost, a.resource_overhead_cost, a.total_cost , a.currency_code, a.prior_period_cost, a.prior_period_onhand, a.quantity_onhand , c.base_txn_type_name transaction_type , a.transaction_source_type , a.transaction_number , a.reference_type , a.reference_number , DECODE(a.base_txn_source_type_id, 0, a.adjustment_date, a.transaction_date) transaction_date , a.transaction_quantity , a.transaction_cost, a.variance_cost, a.LE_TIMEZONE_CODE LE_TIMEZONE_CODE, ctsa.LE_ENABLED_FLAG, a.cost_date_display cost_date_display, a.end_date period_end_date, a.pac_adjustment, a.adjusted_prior_period_cost FROM (SELECT pac.cost_org_id , pac.cost_book_id , pac.inventory_item_id , pac.val_unit_id , ct.transaction_id transaction_id , ct.cost_date cost_date , ctc.eff_date eff_date , 'PERIODIC_AVERAGE' cost_method_code , SUM( CASE WHEN cce.cost_element_type = 'MATERIAL' THEN ctc.unit_cost ELSE 0 END) AS material_cost , SUM( CASE WHEN cce.cost_element_type = 'OVERHEAD' THEN ctc.unit_cost ELSE 0 END) AS overhead_cost , SUM( CASE WHEN cce.cost_element_type = 'PROFIT_IN_INVENTORY' THEN ctc.unit_cost ELSE 0 END) AS profit_in_inventory_cost , SUM( CASE WHEN cce.cost_element_type = 'MATERIAL_OVERHEAD' THEN ctc.unit_cost ELSE 0 END) AS material_overhead_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE' THEN ctc.unit_cost ELSE 0 END) AS resource_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE_DIRECT' THEN ctc.unit_cost ELSE 0 END) AS resource_direct_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE_OVERHEAD' THEN ctc.unit_cost ELSE 0 END) AS resource_overhead_cost , sum(pac.periodic_avg_cost) AS total_cost , pac.currency_code, sum(pac.prior_period_cost) AS prior_period_cost, pac.prior_period_onhand AS prior_period_onhand, 0 AS quantity_onhand, ct.base_txn_source_type_id transaction_source_type, ct.txn_source_doc_number transaction_number, ct.txn_source_ref_doc_type reference_type, ct.txn_source_ref_doc_number reference_number, ct.transaction_date transaction_date, ct.quantity AS transaction_quantity, sum(ctc.unit_cost) AS transaction_cost, ct.base_txn_type_id AS base_txn_type_id, ct.base_txn_source_type_id AS base_txn_source_type_id, 0 variance_cost, ct.LE_TIMEZONE_CODE LE_TIMEZONE_CODE, NULL adjustment_date, ct.cost_date cost_date_display, to_timestamp(cps.end_date) end_date, CAST('N' AS VARCHAR2(1)) pac_adjustment, SUM(pac.adjusted_prior_period_cost) adjusted_prior_period_cost FROM cst_periodic_avg_costs pac, cst_transactions ct , cst_transaction_costs ctc, cst_cost_elements_b cce, cst_period_statuses cps WHERE ct.cost_org_id = pac.cost_org_id AND ct.cost_book_id = pac.cost_book_id AND ct.val_unit_id = pac.val_unit_id AND ct.inventory_item_id = pac.inventory_item_id AND ct.period_name = pac.period_name AND cce.cost_element_id = pac.cost_element_id AND cce.cost_element_id = ctc.cost_element_id AND ct.transaction_id = ctc.transaction_id AND (nvl(ctc.user_adjustment_id,0) = 0 AND nvl(ctc.adjustment_transaction_id,0) =0) AND ct.use_item_cost_flag = 'N' AND ct.cost_transaction_type <> 'ADJUST' AND ct.logical_flag = 'N' AND ct.intransit_flag = 'N' AND cps.cost_org_id = pac.cost_org_id AND cps.cost_book_id = pac.cost_book_id AND cps.period_name = pac.period_name AND ctc.period_name = ct.period_name GROUP BY pac.cost_org_id, pac.cost_book_id, pac.inventory_item_id, pac.val_unit_id, ct.transaction_id, pac.currency_code, pac.prior_period_onhand, ct.base_txn_source_type_id, ct.txn_source_doc_number, ct.txn_source_ref_doc_type, ct.txn_source_ref_doc_number, ct.transaction_date, ct.quantity, ct.cost_date, ctc.eff_date, ct.base_txn_type_id, ct.base_txn_source_type_id, ct.LE_TIMEZONE_CODE, cps.end_date UNION ALL SELECT pac.cost_org_id , pac.cost_book_id , pac.inventory_item_id , pac.val_unit_id , ct.transaction_id transaction_id , ctc.cost_date cost_date , ctc.eff_date eff_date , 'PERIODIC_AVERAGE' cost_method_code , SUM( CASE WHEN cce.cost_element_type = 'MATERIAL' THEN ctc.unit_cost ELSE 0 END) AS material_cost , SUM( CASE WHEN cce.cost_element_type = 'OVERHEAD' THEN ctc.unit_cost ELSE 0 END) AS overhead_cost , SUM( CASE WHEN cce.cost_element_type = 'PROFIT_IN_INVENTORY' THEN ctc.unit_cost ELSE 0 END) AS profit_in_inventory_cost , SUM( CASE WHEN cce.cost_element_type = 'MATERIAL_OVERHEAD' THEN ctc.unit_cost ELSE 0 END) AS material_overhead_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE' THEN ctc.unit_cost ELSE 0 END) AS resource_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE_DIRECT' THEN ctc.unit_cost ELSE 0 END) AS resource_direct_cost , SUM( CASE WHEN cce.cost_element_type = 'RESOURCE_OVERHEAD' THEN ctc.unit_cost ELSE 0 END) AS resource_overhead_cost , sum(pac.periodic_avg_cost) AS total_cost , pac.currency_code, sum(pac.prior_period_cost) AS prior_period_cost, pac.prior_period_onhand AS prior_period_onhand, 0 AS quantity_onhand, ctc.base_txn_source_type_id transaction_source_type, ctc.cost_reference transaction_number, ct.txn_source_ref_doc_type reference_type, ct.txn_source_ref_doc_number reference_number, ct.transaction_date transaction_date, ct.quantity AS transaction_quantity, sum(ctc.unit_cost) AS transaction_cost, ctc.base_txn_type_id AS base_txn_type_id, ctc.base_txn_source_type_id AS base_txn_source_type_id, 0 variance_cost, ct.LE_TIMEZONE_CODE LE_TIMEZONE_CODE, ctc.adjustment_date, ctc.cost_date cost_date_display, to_timestamp(cps.end_date) end_date, CAST('Y' AS VARCHAR2(1)) pac_adjustment, SUM(pac.adjusted_prior_period_cost) adjusted_prior_period_cost FROM cst_periodic_avg_costs pac, cst_transaction_costs ctc, cst_transactions ct , cst_cost_elements_b cce, cst_period_statuses cps WHERE ctc.transaction_id = ct.transaction_id AND ctc.cost_org_id = pac.cost_org_id AND ctc.cost_book_id = pac.cost_book_id AND ct.val_unit_id = pac.val_unit_id AND ct.inventory_item_id = pac.inventory_item_id AND ctc.period_name = pac.period_name AND (nvl(ctc.user_adjustment_id,0) <> 0 OR nvl(ctc.adjustment_transaction_id,0) <>0) AND cce.cost_element_id = pac.cost_element_id AND cce.cost_element_id = ctc.cost_element_id AND ct.use_item_cost_flag = 'N' AND cps.cost_org_id = pac.cost_org_id AND cps.cost_book_id = pac.cost_book_id AND cps.period_name = pac.period_name AND ctc.period_name = ct.period_name GROUP BY pac.cost_org_id, pac.cost_book_id, pac.inventory_item_id, pac.val_unit_id, ct.transaction_id, pac.currency_code, pac.prior_period_onhand, ctc.base_txn_source_type_id, ct.txn_source_doc_number, ct.txn_source_ref_doc_type, ct.txn_source_ref_doc_number, ct.transaction_date, ct.quantity, ctc.cost_date , ctc.eff_date , ctc.base_txn_type_id, ctc.base_txn_source_type_id, ct.LE_TIMEZONE_CODE, ctc.adjustment_date, cps.end_date, ctc.cost_reference ) a, cst_all_txn_types_v c, CST_TXN_SOURCE_ACTIONS ctsa WHERE a.base_txn_type_id = c.base_txn_type_id AND a.base_txn_source_type_id = c.base_txn_source_type_id AND c.base_txn_source_type_id = ctsa.base_txn_source_type_id AND c.BASE_TXN_ACTION_ID = ctsa.BASE_TXN_ACTION_ID