CST_ITEM_COSTS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
COST_ORG_ID COST_ORG_NAME COST_BOOK_ID COST_BOOK_CODE CATEGORY_NAME COST_PROFILE_CODE ORGANIZATION_ID INVENTORY_ITEM_ID ITEM_NUMBER DESCRIPTION COST_METHOD_CODE VAL_STRUCTURE_ID VAL_STRUCTURE_CODE VAL_STRUCTURE_TYPE_CODE VAL_UNIT_ID VAL_UNIT_CODE TRANSACTION_ID COST_DATE COST_ASOF_DATE CURRENCY_CODE UOM_CODE TOTAL_COST PREVIOUS_COST RECEIPT_NUM PRECISION EXTENDED_PRECISION EFFECTIVE_START_DATE EFFECTIVE_END_DATE

Name

COST_ORG_ID

COST_ORG_NAME

COST_BOOK_ID

COST_BOOK_CODE

CATEGORY_NAME

COST_PROFILE_CODE

ORGANIZATION_ID

INVENTORY_ITEM_ID

ITEM_NUMBER

DESCRIPTION

COST_METHOD_CODE

VAL_STRUCTURE_ID

VAL_STRUCTURE_CODE

VAL_STRUCTURE_TYPE_CODE

VAL_UNIT_ID

VAL_UNIT_CODE

TRANSACTION_ID

COST_DATE

COST_ASOF_DATE

CURRENCY_CODE

UOM_CODE

TOTAL_COST

PREVIOUS_COST

RECEIPT_NUM

PRECISION

EXTENDED_PRECISION

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

Query

SQL_Statement
SELECT cpc.cost_org_id , houft.name cost_org_name , cpc.cost_book_id , ccb.cost_book_code , ec.category_name , ccp.cost_profile_code , ccop.master_organization_id organization_id , cpc.inventory_item_id , esi.item_number , esi.description , 'PERPETUAL_AVERAGE' cost_method_code , cvs.val_structure_id , cvs.val_structure_code , cvs.val_structure_type_code , cpc.val_unit_id , cvu.val_unit_code , cpc.transaction_id , cpc.cost_date , cpc.cost_date cost_asof_date , cpc.currency_code , (SELECT unit_of_measure FROM fusion.INV_UNITS_OF_MEASURE_vl WHERE uom_code = cpc.uom_code ) AS uom_code , SUM(cpc.unit_cost_average) total_cost , SUM(cpc.unit_cost_onhand) previous_cost , NULL receipt_num , fc.precision , fc.extended_precision, NULL effective_start_date, NULL effective_end_date FROM cst_perpavg_cost cpc , cst_transactions ct , cst_cost_profiles_b ccp , cst_val_units_b cvu , cst_val_structures_b cvs , egp_system_items_vl esi , cst_cost_org_parameters ccop , egp_item_categories eic , egp_default_category_sets edcs , egp_categories_vl ec , hr_organization_units_f_tl houft , cst_cost_books_b ccb , fnd_currencies_b fc WHERE cpc.cost_book_id = ccb.cost_book_id AND cpc.cost_org_id = houft.organization_id AND ccop.cost_org_id = houft.organization_id AND cvu.val_structure_id = cvs.val_structure_id AND cpc.val_unit_id = cvu.val_unit_id AND cpc.transaction_id = ct.transaction_id AND ct.cost_profile_id = ccp.cost_profile_id AND cpc.currency_code = fc.currency_code AND esi.inventory_item_id = cpc.inventory_item_id AND esi.organization_id = ccop.master_organization_id AND eic.inventory_item_id(+) = esi.inventory_item_id AND eic.organization_id(+) = esi.organization_id AND ( eic.category_id IS NULL OR eic.category_set_id = edcs.category_set_id ) AND edcs.functional_area_id = 5 AND ec.category_id(+) = eic.category_id AND houft.language = userenv('LANG') AND houft.effective_start_date <= TRUNC(cpc.cost_date) AND houft.effective_end_date >= TRUNC (cpc.cost_date) GROUP BY cpc.cost_org_id, houft.name, cpc.cost_book_id, cpc.uom_code , ccb.cost_book_code, ec.category_name, ccp.cost_profile_code, ccop.master_organization_id, cpc.inventory_item_id, esi.item_number, esi.description, 'PERPETUAL_AVERAGE', cvs.val_structure_id, cvs.val_structure_code, cvs.val_structure_type_code, cpc.val_unit_id, cvu.val_unit_code, cpc.transaction_id, cpc.cost_date, cpc.cost_date, cpc.currency_code, cpc.uom_code, NULL, fc.precision, fc.extended_precision, effective_start_date, effective_end_date UNION ALL SELECT /+ FIRST_ROWS(10) / DISTINCT TransactionEO.COST_ORG_ID, CostOrgPEO.COST_ORG_NAME, TransactionEO.COST_BOOK_ID, (SELECT cost_book_code FROM cst_cost_books_vl WHERE cost_book_id = TransactionEO.cost_book_id ) AS COST_BOOK_CODE, NULL , ccp.cost_profile_code, TransactionEO.INVENTORY_ORG_ID, TransactionEO.INVENTORY_ITEM_ID, ItemPEO.ITEM_NUMBER, ItemPEO.description, 'ACTUAL' cost_method_code, TransactionEO.VAL_structure_ID, cvs.VAL_structure_code, cvs.val_structure_type_code, TransactionEO.VAL_UNIT_ID, ValUnitPEO.VAL_UNIT_CODE, TransactionEO.transaction_id, nvl(TransactionCostEO.COST_DATE,TransactionEO.COST_DATE) cost_date, nvl(TransactionCostEO.COST_DATE,TransactionEO.COST_DATE) cost_asof_date, TransactionCostEO.CURRENCY_CODE, (SELECT unit_of_measure FROM fusion.INV_UNITS_OF_MEASURE_vl WHERE uom_code = TransactionEO.uom_code ) AS uom_code, SUM(TransactionCostEO.UNIT_COST) over(partition BY TransactionCostEO.transaction_id, TransactionCostEO.eff_date, ItemPEO.item_number, ValUnitPEO.VAL_UNIT_CODE order by TransactionCostEO.eff_date) total_cost, NULL , RcvShipmentHeaders.RECEIPT_NUM, fc.precision, fc.extended_precision, NULL effective_start_date, NULL effective_end_date FROM CST_TRANSACTION_COSTS TransactionCostEO, EGP_SYSTEM_ITEMS_VL ItemPEO, CST_TRANSACTIONS TransactionEO, CST_COST_ORGS_V CostOrgPEO, CST_VAL_UNITS_VL ValUnitPEO, RCV_TRANSACTIONS ReceivingTransactionPEO, RCV_SHIPMENT_HEADERS RcvShipmentHeaders, cst_val_structures_b cvs, fnd_currencies_b fc, cst_cost_profiles_b ccp WHERE TransactionEO.INVENTORY_ITEM_ID = ItemPEO.INVENTORY_ITEM_ID AND TransactionEO.INVENTORY_ORG_ID = ItemPEO.ORGANIZATION_ID AND TransactionCostEO.TRANSACTION_ID = TransactionEO.TRANSACTION_ID AND TransactionEO.COST_ORG_ID = CostOrgPEO.COST_ORG_ID AND TransactionEO.VAL_UNIT_ID = ValUnitPEO.VAL_UNIT_ID AND TransactionEO.COST_METHOD_CODE ='ACTUAL' AND TransactionEO.VAL_structure_ID = cvs.VAL_structure_ID AND TransactionEO.RCV_TRANSACTION_ID = ReceivingTransactionPEO.TRANSACTION_ID(+) AND ReceivingTransactionPEO.SHIPMENT_HEADER_ID = RcvShipmentHeaders.SHIPMENT_HEADER_ID(+) AND TransactionEO.COST_TRANSACTION_TYPE = 'RECEIPT' AND TransactionEO.cost_profile_id = ccp.cost_profile_id AND TransactionCostEO.UNIT_COST IS NOT NULL AND TransactionCostEO.EXPENSE_POOL_ID <> -5 AND fc.currency_code = TransactionCostEO.currency_code UNION ALL SELECT csc.cost_org_id , houft.name cost_org_name , csc.cost_book_id , ccb.cost_book_code , ec.category_name , ccp.cost_profile_code , ccop.master_organization_id organization_id , csc.inventory_item_id , esi.item_number , esi.description , 'STANDARD' cost_method_code , cvs.val_structure_id , cvs.val_structure_code , cvs.val_structure_type_code , csc.val_unit_id , cvu.val_unit_code , 0 transaction_id , csc.effective_start_date cost_date, csc.effective_start_date cost_asof_date , csc.currency_code , uom.unit_of_measure AS uom_code , total_cost , previous_total_cost , NULL receipt_num , fc.PRECISION , fc.extended_precision , csc.effective_start_date , csc.effective_end_date FROM cst_std_costs csc , cst_cost_profiles_b ccp , cst_val_units_b cvu , cst_val_structures_b cvs , egp_system_items_vl esi , cst_cost_org_parameters ccop , egp_item_categories eic , egp_default_category_sets edcs , egp_categories_vl ec , hr_organization_units_f_tl houft , cst_cost_books_b ccb , fnd_currencies_b fc , INV_UNITS_OF_MEASURE_vl uom , cst_item_cost_profiles cicp WHERE csc.cost_book_id = ccb.cost_book_id AND csc.cost_org_id = houft.organization_id AND ccop.cost_org_id = houft.organization_id AND cvu.val_structure_id = cvs.val_structure_id AND csc.val_unit_id = cvu.val_unit_id AND csc.cost_org_id = cicp.cost_org_id AND csc.cost_book_id = cicp.cost_book_id AND csc.inventory_item_id = cicp.inventory_item_id AND ccp.cost_profile_id = ( CASE WHEN cvs.val_structure_type_code = 'EXPENSE' THEN cicp.expense_cost_profile_id WHEN cvs.val_structure_type_code = 'ASSET' THEN cicp.asset_cost_profile_id END ) AND csc.currency_code = fc.currency_code AND esi.inventory_item_id = csc.inventory_item_id AND esi.organization_id = ccop.master_organization_id AND eic.inventory_item_id(+) = esi.inventory_item_id AND eic.organization_id(+) = esi.organization_id AND ( eic.category_id IS NULL OR eic.category_set_id = edcs.category_set_id ) AND edcs.functional_area_id = 5 AND ec.category_id(+) = eic.category_id AND houft.LANGUAGE = userenv('LANG') AND TRUNC(csc.effective_start_date) >= houft.effective_start_date AND TRUNC(csc.effective_start_date) <= houft.effective_end_date AND csc.uom_code = uom.uom_code AND csc.status_code = 'PUBLISHED'

SQL_Statement

SELECT cpc.cost_org_id ,

houft.name cost_org_name ,

cpc.cost_book_id ,

ccb.cost_book_code ,

ec.category_name ,

ccp.cost_profile_code ,

ccop.master_organization_id organization_id ,

cpc.inventory_item_id ,

esi.item_number ,

esi.description ,

'PERPETUAL_AVERAGE' cost_method_code ,

cvs.val_structure_id ,

cvs.val_structure_code ,

cvs.val_structure_type_code ,

cpc.val_unit_id ,

cvu.val_unit_code ,

cpc.transaction_id ,

cpc.cost_date ,

cpc.cost_date cost_asof_date ,

cpc.currency_code ,

(SELECT unit_of_measure

FROM fusion.INV_UNITS_OF_MEASURE_vl

WHERE uom_code = cpc.uom_code

) AS uom_code ,

SUM(cpc.unit_cost_average) total_cost ,

SUM(cpc.unit_cost_onhand) previous_cost ,

NULL receipt_num ,

fc.precision ,

fc.extended_precision,

NULL effective_start_date,

NULL effective_end_date

FROM cst_perpavg_cost cpc ,

cst_transactions ct ,

cst_cost_profiles_b ccp ,

cst_val_units_b cvu ,

cst_val_structures_b cvs ,

egp_system_items_vl esi ,

cst_cost_org_parameters ccop ,

egp_item_categories eic ,

egp_default_category_sets edcs ,

egp_categories_vl ec ,

hr_organization_units_f_tl houft ,

cst_cost_books_b ccb ,

fnd_currencies_b fc

WHERE cpc.cost_book_id = ccb.cost_book_id

AND cpc.cost_org_id = houft.organization_id

AND ccop.cost_org_id = houft.organization_id

AND cvu.val_structure_id = cvs.val_structure_id

AND cpc.val_unit_id = cvu.val_unit_id

AND cpc.transaction_id = ct.transaction_id

AND ct.cost_profile_id = ccp.cost_profile_id

AND cpc.currency_code = fc.currency_code

AND esi.inventory_item_id = cpc.inventory_item_id

AND esi.organization_id = ccop.master_organization_id

AND eic.inventory_item_id(+) = esi.inventory_item_id

AND eic.organization_id(+) = esi.organization_id

AND ( eic.category_id IS NULL

OR eic.category_set_id = edcs.category_set_id )

AND edcs.functional_area_id = 5

AND ec.category_id(+) = eic.category_id

AND houft.language = userenv('LANG')

AND houft.effective_start_date <= TRUNC(cpc.cost_date)

AND houft.effective_end_date >= TRUNC (cpc.cost_date)

GROUP BY cpc.cost_org_id,

houft.name,

cpc.cost_book_id,

cpc.uom_code ,

ccb.cost_book_code,

ec.category_name,

ccp.cost_profile_code,

ccop.master_organization_id,

cpc.inventory_item_id,

esi.item_number,

esi.description,

'PERPETUAL_AVERAGE',

cvs.val_structure_id,

cvs.val_structure_code,

cvs.val_structure_type_code,

cpc.val_unit_id,

cvu.val_unit_code,

cpc.transaction_id,

cpc.cost_date,

cpc.currency_code,

cpc.uom_code,

NULL,

fc.precision,

fc.extended_precision,

effective_start_date,

effective_end_date

UNION ALL

SELECT

/*+ FIRST_ROWS(10) */

DISTINCT TransactionEO.COST_ORG_ID,

CostOrgPEO.COST_ORG_NAME,

TransactionEO.COST_BOOK_ID,

(SELECT cost_book_code

FROM cst_cost_books_vl

WHERE cost_book_id = TransactionEO.cost_book_id

) AS COST_BOOK_CODE,

NULL ,

ccp.cost_profile_code,

TransactionEO.INVENTORY_ORG_ID,

TransactionEO.INVENTORY_ITEM_ID,

ItemPEO.ITEM_NUMBER,

ItemPEO.description,

'ACTUAL' cost_method_code,

TransactionEO.VAL_structure_ID,

cvs.VAL_structure_code,

cvs.val_structure_type_code,

TransactionEO.VAL_UNIT_ID,

ValUnitPEO.VAL_UNIT_CODE,

TransactionEO.transaction_id,

nvl(TransactionCostEO.COST_DATE,TransactionEO.COST_DATE) cost_date,

nvl(TransactionCostEO.COST_DATE,TransactionEO.COST_DATE) cost_asof_date,

TransactionCostEO.CURRENCY_CODE,

(SELECT unit_of_measure

FROM fusion.INV_UNITS_OF_MEASURE_vl

WHERE uom_code = TransactionEO.uom_code

) AS uom_code,

SUM(TransactionCostEO.UNIT_COST) over(partition BY TransactionCostEO.transaction_id, TransactionCostEO.eff_date, ItemPEO.item_number, ValUnitPEO.VAL_UNIT_CODE order by TransactionCostEO.eff_date) total_cost,

NULL ,

RcvShipmentHeaders.RECEIPT_NUM,

fc.precision,

fc.extended_precision,

NULL effective_start_date,

NULL effective_end_date

FROM CST_TRANSACTION_COSTS TransactionCostEO,

EGP_SYSTEM_ITEMS_VL ItemPEO,

CST_TRANSACTIONS TransactionEO,

CST_COST_ORGS_V CostOrgPEO,

CST_VAL_UNITS_VL ValUnitPEO,

RCV_TRANSACTIONS ReceivingTransactionPEO,

RCV_SHIPMENT_HEADERS RcvShipmentHeaders,

cst_val_structures_b cvs,

fnd_currencies_b fc,

cst_cost_profiles_b ccp

WHERE TransactionEO.INVENTORY_ITEM_ID = ItemPEO.INVENTORY_ITEM_ID

AND TransactionEO.INVENTORY_ORG_ID = ItemPEO.ORGANIZATION_ID

AND TransactionCostEO.TRANSACTION_ID = TransactionEO.TRANSACTION_ID

AND TransactionEO.COST_ORG_ID = CostOrgPEO.COST_ORG_ID

AND TransactionEO.VAL_UNIT_ID = ValUnitPEO.VAL_UNIT_ID

AND TransactionEO.COST_METHOD_CODE ='ACTUAL'

AND TransactionEO.VAL_structure_ID = cvs.VAL_structure_ID

AND TransactionEO.RCV_TRANSACTION_ID = ReceivingTransactionPEO.TRANSACTION_ID(+)

AND ReceivingTransactionPEO.SHIPMENT_HEADER_ID = RcvShipmentHeaders.SHIPMENT_HEADER_ID(+)

AND TransactionEO.COST_TRANSACTION_TYPE = 'RECEIPT'

AND TransactionEO.cost_profile_id = ccp.cost_profile_id

AND TransactionCostEO.UNIT_COST IS NOT NULL

AND TransactionCostEO.EXPENSE_POOL_ID <> -5

AND fc.currency_code = TransactionCostEO.currency_code

UNION ALL

SELECT csc.cost_org_id ,

houft.name cost_org_name ,

csc.cost_book_id ,

ccb.cost_book_code ,

ec.category_name ,

ccp.cost_profile_code ,

ccop.master_organization_id organization_id ,

csc.inventory_item_id ,

esi.item_number ,

esi.description ,

'STANDARD' cost_method_code ,

cvs.val_structure_id ,

cvs.val_structure_code ,

cvs.val_structure_type_code ,

csc.val_unit_id ,

cvu.val_unit_code ,

0 transaction_id ,

csc.effective_start_date cost_date,

csc.effective_start_date cost_asof_date ,

csc.currency_code ,

uom.unit_of_measure AS uom_code ,

total_cost ,

previous_total_cost ,

NULL receipt_num ,

fc.PRECISION ,

fc.extended_precision ,

csc.effective_start_date ,

csc.effective_end_date

FROM cst_std_costs csc ,

cst_cost_profiles_b ccp ,

cst_val_units_b cvu ,

cst_val_structures_b cvs ,

egp_system_items_vl esi ,

cst_cost_org_parameters ccop ,

egp_item_categories eic ,

egp_default_category_sets edcs ,

egp_categories_vl ec ,

hr_organization_units_f_tl houft ,

cst_cost_books_b ccb ,

fnd_currencies_b fc ,

INV_UNITS_OF_MEASURE_vl uom ,

cst_item_cost_profiles cicp

WHERE csc.cost_book_id = ccb.cost_book_id

AND csc.cost_org_id = houft.organization_id

AND ccop.cost_org_id = houft.organization_id

AND cvu.val_structure_id = cvs.val_structure_id

AND csc.val_unit_id = cvu.val_unit_id

AND csc.cost_org_id = cicp.cost_org_id

AND csc.cost_book_id = cicp.cost_book_id

AND csc.inventory_item_id = cicp.inventory_item_id

AND ccp.cost_profile_id = (

CASE

WHEN cvs.val_structure_type_code = 'EXPENSE'

THEN cicp.expense_cost_profile_id

WHEN cvs.val_structure_type_code = 'ASSET'

THEN cicp.asset_cost_profile_id

END )

AND csc.currency_code = fc.currency_code

AND esi.inventory_item_id = csc.inventory_item_id

AND esi.organization_id = ccop.master_organization_id

AND eic.inventory_item_id(+) = esi.inventory_item_id

AND eic.organization_id(+) = esi.organization_id

AND ( eic.category_id IS NULL

OR eic.category_set_id = edcs.category_set_id )

AND edcs.functional_area_id = 5

AND ec.category_id(+) = eic.category_id

AND houft.LANGUAGE = userenv('LANG')

AND TRUNC(csc.effective_start_date) >= houft.effective_start_date

AND TRUNC(csc.effective_start_date) <= houft.effective_end_date

AND csc.uom_code = uom.uom_code

AND csc.status_code = 'PUBLISHED'