1. Tables and Views for SCM
  2. CST_ITEM_COST_ELEMENTS_V

CST_ITEM_COST_ELEMENTS_V

Details

  • Schema: FUSION

  • Object owner: CST

  • Object type: VIEW

Columns

Name

COST_ORG_ID

COST_BOOK_ID

INVENTORY_ITEM_ID

COST_METHOD_CODE

VAL_UNIT_ID

TRANSACTION_ID

COST_DATE

PERIOD_NAME

COST_ELEMENT_TYPE

COST_ELEMENT_TYPE_DESC

COST_ELEMENT_ID

COST_ELEMENT_CODE

COST_ELEMENT

UNIT_COST_AVERAGE

TRANSACTION_COST

CURRENCY_CODE

UOM_CODE

ANALYSIS_GROUP_ID

VARIANCE_COST

PAC_ADJUSTMENT

Query

SQL_Statement

SELECT cost_org_id ,

cost_book_id ,

inventory_item_id ,

cost_method_code ,

val_unit_id ,

transaction_id ,

cost_date ,

CAST(NULL AS VARCHAR2(15)) period_name,

cost_element_type ,

cost_element_type_desc ,

cost_element_id ,

cost_element_code ,

cost_element_code cost_element ,

SUM(unit_cost_average) unit_cost_average,

SUM(transaction_cost) transaction_cost,

currency_code ,

uom_code ,

analysis_group_id,

sum(0) variance_cost,

CAST('N' AS VARCHAR2(1)) pac_adjustment

FROM

(SELECT a.cost_org_id ,

a.cost_book_id ,

a.inventory_item_id ,

CAST('PERPETUAL_AVERAGE' AS VARCHAR2(30)) cost_method_code ,

a.val_unit_id ,

a.transaction_id ,

a.cost_date ,

b.cost_element_type ,

c.meaning cost_element_type_desc ,

a.cost_element_id ,

b.cost_element_code ,

a.unit_cost_average ,

a.unit_cost_new + a.unit_cost_adjust transaction_cost ,

a.currency_code ,

a.uom_code ,

-1 analysis_group_id

FROM cst_perpavg_cost a ,

cst_cost_elements_b b ,

fnd_lookup_values_vl c

WHERE a.cost_element_id = b.cost_element_id

AND b.cost_element_type = c.lookup_code

AND c.lookup_type = 'CST_COST_ELEMENT_TYPES'

UNION ALL

SELECT x.cost_org_id ,

x.cost_book_id ,

x.inventory_item_id ,

CAST('PERPETUAL_AVERAGE' AS VARCHAR2(30)) cost_method_code ,

x.val_unit_id ,

x.transaction_id ,

x.cost_date ,

z.analysis_code ,

z.analysis_desc ,

x.cost_element_id ,

x.cost_element_code ,

x.unit_cost_average ,

x.transaction_cost ,

x.currency_code ,

x.uom_code ,

z.analysis_group_id

FROM

(SELECT DISTINCT pac.cost_org_id ,

pac.cost_book_id ,

pac.inventory_item_id ,

pac.val_unit_id ,

pac.transaction_id ,

pac.cost_date ,

ce.cost_element_id ,

ce.cost_element_code ,

ac.analysis_group_id ,

pac.unit_cost_average ,

pac.unit_cost_new + pac.unit_cost_adjust transaction_cost ,

pac.currency_code ,

pac.uom_code ,

ac.analysis_id,

ac.default_analysis_code_flag,

ct.cost_profile_id

FROM cst_perpavg_cost pac ,

cst_cost_elements_b ce ,

cst_analysis_codes_b ac,

cst_transactions ct

WHERE

pac.cost_element_id = ce.cost_element_id

AND pac.transaction_id = ct.transaction_id

) x ,

cst_analysis_mapping_details y ,

cst_cost_profiles_b ccp,

cst_analysis_codes_vl z

WHERE y.cost_element_id (+) = x.cost_element_id

and y.analysis_group_id (+) = x.analysis_group_id

and z.analysis_id = nvl(y.analysis_id,x.analysis_id)

and x.cost_profile_id = ccp.cost_profile_id

and nvl(ccp.analysis_mapping_id ,-999)=

(

CASE

WHEN x.default_analysis_code_flag = 'Y'

THEN nvl(ccp.analysis_mapping_id, -999)

WHEN x.default_analysis_code_flag = 'N'

THEN y.analysis_mapping_id

END )

) group by cost_org_id, cost_book_id, inventory_item_id, cost_method_code, val_unit_id,

transaction_id, cost_date, cost_element_type, cost_element_type_desc, cost_element_id,

cost_element_code, cost_element_code, currency_code, uom_code, analysis_group_id

UNION ALL

SELECT cost_org_id ,

cost_book_id ,

inventory_item_id ,

cost_method_code ,

val_unit_id ,

transaction_id ,

cost_date ,

CAST(NULL AS VARCHAR2(15)) period_name,

cost_element_type ,

cost_element_type_desc ,

cost_element_id ,

cost_element_code ,

cost_element_code cost_element ,

sum(unit_cost) unit_cost_average,

sum(transaction_cost) transaction_cost,

currency_code ,

uom_code ,

analysis_group_id,

sum(0) variance_cost,

CAST('N' AS VARCHAR2(1)) pac_adjustment

FROM

(SELECT DISTINCT ct.cost_org_id ,

ct.cost_book_id ,

ct.inventory_item_id ,

ct.cost_method_code ,

ct.val_unit_id ,

a.transaction_id ,

nvl(a.cost_date, ct.cost_date) cost_date ,

b.cost_element_type ,

c.meaning cost_element_type_desc ,

a.cost_element_id ,

b.cost_element_code ,

a.unit_cost,

a.unit_cost transaction_cost ,

a.currency_code ,

ct.transaction_uom_code uom_code,

-1 analysis_group_id

FROM CST_TRANSACTION_COSTS a ,

cst_cost_elements_b b ,

fnd_lookup_values_vl c ,

CST_TRANSACTIONS ct

WHERE a.cost_element_id = b.cost_element_id

AND b.cost_element_type = c.lookup_code

AND c.lookup_type = 'CST_COST_ELEMENT_TYPES'

AND a.TRANSACTION_ID = ct.TRANSACTION_ID

AND a.expense_pool_id <> -5

AND ct.COST_TRANSACTION_TYPE = 'RECEIPT'

AND ct.cost_method_code='ACTUAL'

UNION ALL

SELECT x.cost_org_id ,

x.cost_book_id ,

x.inventory_item_id ,

x.cost_method_code ,

x.val_unit_id ,

x.transaction_id ,

x.cost_date ,

z.analysis_code ,

z.analysis_desc ,

x.cost_element_id ,

x.cost_element_code ,

x.unit_cost ,

x.transaction_cost ,

x.currency_code ,

x.uom_code ,

z.analysis_group_id

FROM

(SELECT DISTINCT ct.cost_org_id ,

ct.cost_book_id ,

ct.inventory_item_id ,

ct.cost_method_code ,

ct.val_unit_id ,

ctc.transaction_id ,

nvl(ctc.cost_date, ct.cost_date) cost_date ,

ce.cost_element_id ,

ce.cost_element_code ,

ac.analysis_group_id ,

ctc.unit_cost,

ctc.unit_cost transaction_cost ,

ctc.currency_code ,

ct.transaction_uom_code uom_code,

ac.analysis_id,

ac.default_analysis_code_flag,

ct.cost_profile_id

FROM CST_TRANSACTION_COSTS ctc ,

cst_cost_elements_b ce ,

cst_analysis_codes_b ac,

CST_TRANSACTIONS ct

WHERE

ctc.cost_element_id = ce.cost_element_id

AND ctc.TRANSACTION_ID = ct.TRANSACTION_ID

AND ctc.expense_pool_id <> -5

AND ct.COST_TRANSACTION_TYPE = 'RECEIPT'

AND ct.cost_method_code='ACTUAL'

) x ,

cst_analysis_mapping_details y ,

cst_cost_profiles_b ccp,

cst_analysis_codes_vl z

WHERE y.cost_element_id (+) = x.cost_element_id

and y.analysis_group_id (+) = x.analysis_group_id

and z.analysis_id = nvl(y.analysis_id,x.analysis_id)

and x.cost_profile_id = ccp.cost_profile_id

and nvl(ccp.analysis_mapping_id ,-999)=

(

CASE

WHEN x.default_analysis_code_flag = 'Y'

THEN nvl(ccp.analysis_mapping_id, -999)

WHEN x.default_analysis_code_flag = 'N'

THEN y.analysis_mapping_id

END )

) group by cost_org_id, cost_book_id, inventory_item_id, cost_method_code, val_unit_id,

transaction_id, cost_date, cost_element_type, cost_element_type_desc, cost_element_id,

cost_element_code, cost_element_code, currency_code, uom_code, analysis_group_id

UNION ALL

SELECT cost_org_id ,

cost_book_id ,

inventory_item_id ,

cost_method_code ,

val_unit_id ,

transaction_id ,

cost_date ,

CAST(NULL AS VARCHAR2(15)) period_name,

cost_element_type ,

cost_element_type_desc ,

cost_element_id ,

cost_element_code ,

cost_element_code cost_element ,

sum(unit_cost_average) unit_cost_average ,

sum(transaction_cost) transaction_cost ,

currency_code ,

uom_code ,

analysis_group_id,

sum(variance_cost),

CAST('N' AS VARCHAR2(1)) pac_adjustment

FROM

(SELECT csc.cost_org_id ,

csc.cost_book_id ,

csc.inventory_item_id ,

CAST('STANDARD' AS VARCHAR2(30)) cost_method_code ,

csc.val_unit_id ,

NVL(ct.transaction_id, 0) transaction_id ,

NVL(ct.cost_date, csc.effective_start_date) cost_date ,

b.cost_element_type ,

c.meaning cost_element_type_desc ,

cscd.cost_element_id ,

b.cost_element_code ,

cscd.unit_cost unit_cost_average ,

cscd.unit_cost transaction_cost ,

csc.currency_code ,

csc.uom_code ,

CAST(-1 as NUMBER(18,0)) analysis_group_id,

cvc.unit_cost variance_cost

FROM cst_std_costs csc ,

cst_std_cost_details cscd ,

cst_cost_elements_b b ,

fnd_lookup_values_vl c ,

cst_transactions ct,

cst_variance_costs cvc

WHERE cscd.cost_element_id = b.cost_element_id

AND csc.std_cost_id = cscd.std_cost_id

AND b.cost_element_type = c.lookup_code

AND c.lookup_type = 'CST_COST_ELEMENT_TYPES'

AND csc.status_code = 'PUBLISHED'

AND csc.cost_org_id = ct.cost_org_id(+)

AND csc.cost_book_id = ct.cost_book_id(+)

AND csc.val_unit_id = ct.val_unit_id(+)

AND csc.inventory_item_id = ct.inventory_item_id(+)

AND ct.cost_date(+) >= csc.effective_start_date

AND ct.cost_date (+) <= csc.effective_end_date

AND ct.cost_method_code = 'STANDARD'

AND ct.cost_transaction_type = 'RECEIPT'

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 )

UNION ALL

SELECT csc.cost_org_id ,

csc.cost_book_id ,

csc.inventory_item_id ,

CAST('STANDARD' AS VARCHAR2(30)) cost_method_code ,

csc.val_unit_id ,

0 transaction_id ,

csc.effective_start_date cost_date ,

b.cost_element_type ,

c.meaning cost_element_type_desc ,

cscd.cost_element_id ,

b.cost_element_code ,

cscd.unit_cost unit_cost_average ,

cscd.unit_cost transaction_cost ,

csc.currency_code ,

csc.uom_code ,

CAST(-1 as NUMBER(18,0)) analysis_group_id,

NULL variance_cost

FROM cst_std_costs csc ,

cst_std_cost_details cscd ,

cst_cost_elements_b b ,

fnd_lookup_values_vl c

WHERE cscd.cost_element_id = b.cost_element_id

AND csc.std_cost_id = cscd.std_cost_id

AND b.cost_element_type = c.lookup_code

AND c.lookup_type = 'CST_COST_ELEMENT_TYPES'

AND csc.status_code = 'PUBLISHED'

UNION ALL

SELECT x.cost_org_id ,

x.cost_book_id ,

x.inventory_item_id ,

CAST('STANDARD' AS VARCHAR2(30)) cost_method_code ,

x.val_unit_id ,

x.transaction_id ,

x.cost_date ,

z.analysis_code ,

z.analysis_desc ,

x.cost_element_id ,

x.cost_element_code ,

x.unit_cost_average ,

x.transaction_cost ,

x.currency_code ,

x.uom_code ,

z.analysis_group_id,

x.variance_cost

FROM

(SELECT DISTINCT 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 ,

cscd.cost_element_id ,

ce.cost_element_code ,

ac.analysis_group_id ,

cscd.unit_cost unit_cost_average ,

cscd.unit_cost transaction_cost ,

csc.currency_code ,

csc.uom_code ,

ac.analysis_id,

cvc.unit_cost variance_cost,

ac.default_analysis_code_flag,

ct.cost_profile_id

FROM cst_std_costs csc ,

cst_std_cost_details cscd ,

cst_cost_elements_b ce ,

cst_analysis_codes_b ac ,

cst_transactions ct,

cst_variance_costs cvc

WHERE

cscd.cost_element_id = ce.cost_element_id

AND csc.std_cost_id = cscd.std_cost_id

AND csc.status_code = 'PUBLISHED'

AND csc.cost_org_id = ct.cost_org_id(+)

AND csc.cost_book_id = ct.cost_book_id(+)

AND csc.val_unit_id = ct.val_unit_id(+)

AND csc.inventory_item_id = ct.inventory_item_id(+)

AND ct.cost_date(+) >= csc.effective_start_date

AND ct.cost_date (+) <= csc.effective_end_date

AND ct.cost_method_code = 'STANDARD'

AND ct.cost_transaction_type = 'RECEIPT'

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 )

UNION ALL

SELECT DISTINCT csc.cost_org_id ,

csc.cost_book_id ,

csc.inventory_item_id ,

csc.val_unit_id ,

0 transaction_id ,

csc.effective_start_date cost_date ,

cscd.cost_element_id ,

ce.cost_element_code ,

ac.analysis_group_id ,

cscd.unit_cost unit_cost_average ,

cscd.unit_cost transaction_cost ,

csc.currency_code ,

csc.uom_code ,

ac.analysis_id,

NULL variance_cost,

ac.default_analysis_code_flag,

ccp.cost_profile_id

FROM cst_std_costs csc ,

cst_std_cost_details cscd ,

cst_cost_elements_b ce ,

cst_analysis_codes_b ac,

cst_cost_profiles_b ccp,

cst_val_units_b cvu,

cst_val_structures_b cvs,

cst_item_cost_profiles cicp

WHERE

cscd.cost_element_id = ce.cost_element_id

AND csc.std_cost_id = cscd.std_cost_id

AND csc.status_code = 'PUBLISHED'

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 cicp.cost_org_id = csc.cost_org_id

and cicp.cost_book_id = csc.cost_book_id

and cicp.inventory_item_id = csc.inventory_item_id

and cvu.val_unit_id = csc.val_unit_id

and cvs.val_structure_id = cvu.val_structure_id

) x ,

cst_analysis_mapping_details y ,

cst_cost_profiles_b ccp,

cst_analysis_codes_vl z

WHERE y.cost_element_id (+) = x.cost_element_id

and y.analysis_group_id (+) = x.analysis_group_id

and z.analysis_id = nvl(y.analysis_id,x.analysis_id)

and x.cost_profile_id = ccp.cost_profile_id

and nvl(ccp.analysis_mapping_id ,-999)=

(

CASE

WHEN x.default_analysis_code_flag = 'Y'

THEN nvl(ccp.analysis_mapping_id, -999)

WHEN x.default_analysis_code_flag = 'N'

THEN y.analysis_mapping_id

END )

) GROUP BY

cost_org_id ,

cost_book_id ,

inventory_item_id ,

cost_method_code ,

val_unit_id ,

transaction_id ,

cost_date ,

cost_element_type ,

cost_element_type_desc ,

cost_element_id ,

cost_element_code ,

currency_code ,

uom_code ,

analysis_group_id

UNION ALL

SELECT cost_org_id ,

cost_book_id ,

inventory_item_id ,

cost_method_code ,

val_unit_id ,

transaction_id ,

cost_date ,

period_name,

cost_element_type ,

cost_element_type_desc ,

cost_element_id ,

cost_element_code ,

cost_element_code cost_element ,

SUM(unit_cost_average) unit_cost_average,

SUM(transaction_cost) transaction_cost,

currency_code ,

uom_code ,

analysis_group_id,

sum(0) variance_cost,

CAST('N' AS VARCHAR2(1)) pac_adjustment

FROM

(SELECT a.cost_org_id ,

a.cost_book_id ,

a.inventory_item_id ,

CAST('PERIODIC_AVERAGE' AS VARCHAR2(30)) cost_method_code ,

a.val_unit_id ,

CAST(0 AS NUMBER(18,0)) transaction_id ,

cps.end_date cost_date ,

cps.period_name,

b.cost_element_type ,

c.meaning cost_element_type_desc ,

a.cost_element_id ,

b.cost_element_code ,

a.PERIODIC_AVG_COST unit_cost_average,

a.PERIODIC_AVG_COST transaction_cost ,

a.currency_code ,

a.uom_code ,

CAST(-1 AS NUMBER(18,0)) analysis_group_id

FROM cst_periodic_avg_costs a ,

CST_PERIOD_STATUSES cps,

cst_cost_elements_b b ,

fnd_lookup_values_vl c

WHERE a.cost_element_id = b.cost_element_id

AND b.cost_element_type = c.lookup_code

AND c.lookup_type = 'CST_COST_ELEMENT_TYPES'

AND a.cost_org_id = cps.cost_org_id

AND a.cost_book_id= cps.cost_book_id

AND a.period_name = cps.period_name

UNION ALL

SELECT x.cost_org_id ,

x.cost_book_id ,

x.inventory_item_id ,

CAST('PERIODIC_AVERAGE' AS VARCHAR2(30)) cost_method_code ,

x.val_unit_id ,

x.transaction_id ,

x.cost_date ,

x.period_name,

z.analysis_code ,

z.analysis_desc ,

x.cost_element_id ,

x.cost_element_code ,

x.unit_cost_average ,

x.transaction_cost ,

x.currency_code ,

x.uom_code ,

z.analysis_group_id

FROM

(SELECT DISTINCT pac.cost_org_id ,

pac.cost_book_id ,

pac.inventory_item_id ,

pac.val_unit_id ,

CAST(0 AS NUMBER(18,0)) transaction_id ,

cps.end_date cost_date,

cps.period_name,

ce.cost_element_id ,

ce.cost_element_code ,

ac.analysis_group_id ,

pac.PERIODIC_AVG_COST unit_cost_average ,

pac.PERIODIC_AVG_COST transaction_cost ,

pac.currency_code ,

pac.uom_code ,

ac.analysis_id,

ac.default_analysis_code_flag,

ccp.cost_profile_id cost_profile_id

FROM cst_periodic_avg_costs pac,

cst_period_statuses cps,

cst_cost_elements_b ce ,

cst_analysis_codes_b ac,

cst_cost_profiles_b ccp,

cst_val_units_b cvu,

cst_val_structures_b cvs,

cst_item_cost_profiles cicp

WHERE pac.cost_element_id = ce.cost_element_id

AND pac.cost_org_id = cps.cost_org_id

AND pac.cost_book_id= cps.cost_book_id

AND pac.period_name = cps.period_name

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 cicp.cost_org_id = pac.cost_org_id

and cicp.cost_book_id = pac.cost_book_id

and cicp.inventory_item_id = pac.inventory_item_id

and cvu.val_unit_id = pac.val_unit_id

and cvs.val_structure_id = cvu.val_structure_id

) x ,

cst_analysis_mapping_details y ,

cst_cost_profiles_b ccp,

cst_analysis_codes_vl z

WHERE y.cost_element_id (+) = x.cost_element_id

and y.analysis_group_id (+) = x.analysis_group_id

and z.analysis_id = nvl(y.analysis_id,x.analysis_id)

and x.cost_profile_id = ccp.cost_profile_id

and nvl(ccp.analysis_mapping_id ,-999)=

(

CASE

WHEN x.default_analysis_code_flag = 'Y'

THEN nvl(ccp.analysis_mapping_id, -999)

WHEN x.default_analysis_code_flag = 'N'

THEN y.analysis_mapping_id

END )

)

group by cost_org_id, cost_book_id, inventory_item_id, cost_method_code, val_unit_id,

transaction_id, cost_date, period_name, cost_element_type, cost_element_type_desc, cost_element_id,

cost_element_code, cost_element_code, currency_code, uom_code, analysis_group_id

UNION ALL

SELECT cost_org_id ,

cost_book_id ,

inventory_item_id ,

cost_method_code ,

val_unit_id ,

transaction_id ,

cost_date ,

period_name,

cost_element_type ,

cost_element_type_desc ,

cost_element_id ,

cost_element_code ,

cost_element_code cost_element ,

SUM(unit_cost_average) unit_cost_average,

SUM(transaction_cost) transaction_cost,

currency_code ,

uom_code ,

analysis_group_id,

sum(0) variance_cost,

CAST('N' AS VARCHAR2(1)) pac_adjustment

FROM

(SELECT a.cost_org_id ,

a.cost_book_id ,

a.inventory_item_id ,

CAST('PERIODIC_AVERAGE' AS VARCHAR2(30)) cost_method_code ,

a.val_unit_id ,

ct.transaction_id transaction_id ,

ct.cost_date cost_date ,

cps.period_name,

b.cost_element_type ,

c.meaning cost_element_type_desc ,

a.cost_element_id ,

b.cost_element_code ,

a.PERIODIC_AVG_COST unit_cost_average,

NVL(ctc.unit_cost,0) transaction_cost ,

a.currency_code ,

a.uom_code ,

CAST(-1 AS NUMBER(18,0)) analysis_group_id

FROM cst_periodic_avg_costs a ,

cst_transactions ct,

CST_PERIOD_STATUSES cps,

cst_cost_elements_b b ,

fnd_lookup_values_vl c,

CST_TRANSACTION_COSTS ctc

WHERE a.cost_element_id = b.cost_element_id

AND b.cost_element_type = c.lookup_code

AND c.lookup_type = 'CST_COST_ELEMENT_TYPES'

AND ct.USE_ITEM_COST_FLAG = 'N'

AND (nvl(ctc.user_adjustment_id,0) = 0 AND nvl(ctc.adjustment_transaction_id,0) =0)

AND ct.cost_transaction_type <> 'ADJUST'

AND ct.intransit_flag = 'N'

AND a.cost_org_id = cps.cost_org_id

AND a.cost_book_id= cps.cost_book_id

AND a.period_name = cps.period_name

AND ct.cost_org_id = cps.cost_org_id

AND ct.cost_book_id = cps.cost_book_id

AND ct.inventory_item_id= a.inventory_item_id

AND ct.val_unit_id = a.val_unit_id

AND ct.period_name = cps.period_name

AND ctc.cost_element_id = b.cost_element_id

AND ctc.transaction_id = ct.transaction_id

AND ctc.expense_pool_id <> -5

UNION ALL

SELECT x.cost_org_id ,

x.cost_book_id ,

x.inventory_item_id ,

CAST('PERIODIC_AVERAGE' AS VARCHAR2(30)) cost_method_code ,

x.val_unit_id ,

x.transaction_id ,

x.cost_date ,

x.period_name,

z.analysis_code ,

z.analysis_desc ,

x.cost_element_id ,

x.cost_element_code ,

x.unit_cost_average ,

x.transaction_cost ,

x.currency_code ,

x.uom_code ,

z.analysis_group_id

FROM

(SELECT DISTINCT pac.cost_org_id ,

pac.cost_book_id ,

pac.inventory_item_id ,

pac.val_unit_id ,

ct.transaction_id ,

ct.cost_date cost_date,

cps.period_name,

ce.cost_element_id ,

ce.cost_element_code ,

ac.analysis_group_id ,

pac.PERIODIC_AVG_COST unit_cost_average ,

NVL(ctc.unit_cost,0) transaction_cost ,

pac.currency_code ,

pac.uom_code ,

ac.analysis_id,

ac.default_analysis_code_flag,

ct.cost_profile_id

FROM cst_periodic_avg_costs pac,

cst_period_statuses cps,

cst_cost_elements_b ce ,

cst_analysis_codes_b ac,

cst_transactions ct,

cst_transaction_costs ctc

WHERE pac.cost_element_id = ce.cost_element_id

AND pac.cost_org_id = cps.cost_org_id

AND pac.cost_book_id= cps.cost_book_id

AND pac.period_name = cps.period_name

AND cps.cost_org_id = ct.cost_org_id

AND cps.cost_book_id= ct.cost_book_id

AND cps.period_name = ct.period_name

AND ct.use_item_cost_flag ='N'

AND (nvl(ctc.user_adjustment_id,0) = 0 AND nvl(ctc.adjustment_transaction_id,0) =0)

AND ct.cost_transaction_type <> 'ADJUST'

AND ct.intransit_flag = 'N'

AND pac.inventory_item_id = ct.inventory_item_id

AND pac.val_unit_id = ct.val_unit_id

AND ctc.cost_element_id = ce.cost_element_id

AND ctc.transaction_id = ct.transaction_id

AND ctc.expense_pool_id <> -5

) x ,

cst_analysis_mapping_details y ,

cst_cost_profiles_b ccp,

cst_analysis_codes_vl z

WHERE y.cost_element_id (+) = x.cost_element_id

and y.analysis_group_id (+) = x.analysis_group_id

and z.analysis_id = nvl(y.analysis_id,x.analysis_id)

and x.cost_profile_id = ccp.cost_profile_id

and nvl(ccp.analysis_mapping_id ,-999)=

(

CASE

WHEN x.default_analysis_code_flag = 'Y'

THEN nvl(ccp.analysis_mapping_id, -999)

WHEN x.default_analysis_code_flag = 'N'

THEN y.analysis_mapping_id

END )

)

group by cost_org_id, cost_book_id, inventory_item_id, cost_method_code, val_unit_id,

transaction_id, cost_date, period_name, cost_element_type, cost_element_type_desc, cost_element_id,

cost_element_code, cost_element_code, currency_code, uom_code, analysis_group_id

UNION ALL

SELECT cost_org_id ,

cost_book_id ,

inventory_item_id ,

cost_method_code ,

val_unit_id ,

transaction_id ,

cost_date ,

period_name,

cost_element_type ,

cost_element_type_desc ,

cost_element_id ,

cost_element_code ,

cost_element_code cost_element ,

SUM(unit_cost_average) unit_cost_average,

SUM(transaction_cost) transaction_cost,

currency_code ,

uom_code ,

analysis_group_id,

sum(0) variance_cost,

CAST('Y' AS VARCHAR2(1)) pac_adjustment

FROM

(SELECT a.cost_org_id ,

a.cost_book_id ,

a.inventory_item_id ,

CAST('PERIODIC_AVERAGE' AS VARCHAR2(30)) cost_method_code ,

a.val_unit_id ,

ct.transaction_id transaction_id ,

nvl(ctc.cost_date,ct.cost_date) cost_date ,

cps.period_name,

b.cost_element_type ,

c.meaning cost_element_type_desc ,

a.cost_element_id ,

b.cost_element_code ,

a.PERIODIC_AVG_COST unit_cost_average,

NVL(ctc.unit_cost,0) transaction_cost ,

a.currency_code ,

a.uom_code ,

CAST(-1 AS NUMBER(18,0)) analysis_group_id

FROM cst_periodic_avg_costs a ,

cst_transactions ct,

CST_PERIOD_STATUSES cps,

cst_cost_elements_b b ,

fnd_lookup_values_vl c,

CST_TRANSACTION_COSTS ctc

WHERE a.cost_element_id = b.cost_element_id

AND b.cost_element_type = c.lookup_code

AND c.lookup_type = 'CST_COST_ELEMENT_TYPES'

AND ct.USE_ITEM_COST_FLAG = 'N'

and (nvl(ctc.adjustment_transaction_id,0) <> 0 or nvl(ctc.user_adjustment_id,0) <> 0)

AND a.cost_org_id = cps.cost_org_id

AND a.cost_book_id= cps.cost_book_id

AND ct.cost_org_id = cps.cost_org_id

AND ct.cost_book_id= cps.cost_book_id

AND a.period_name = cps.period_name

AND ctc.cost_org_id = cps.cost_org_id

AND ctc.cost_book_id = cps.cost_book_id

AND ct.inventory_item_id= a.inventory_item_id

AND ct.val_unit_id = a.val_unit_id

AND ctc.period_name = cps.period_name

AND ctc.cost_element_id = b.cost_element_id

AND ctc.transaction_id = ct.transaction_id

AND ctc.expense_pool_id <> -5

UNION ALL

SELECT x.cost_org_id ,

x.cost_book_id ,

x.inventory_item_id ,

CAST('PERIODIC_AVERAGE' AS VARCHAR2(30)) cost_method_code ,

x.val_unit_id ,

x.transaction_id ,

x.cost_date ,

x.period_name,

z.analysis_code ,

z.analysis_desc ,

x.cost_element_id ,

x.cost_element_code ,

x.unit_cost_average ,

x.transaction_cost ,

x.currency_code ,

x.uom_code ,

z.analysis_group_id

FROM

(SELECT DISTINCT pac.cost_org_id ,

pac.cost_book_id ,

pac.inventory_item_id ,

pac.val_unit_id ,

ct.transaction_id ,

nvl(ctc.cost_date,ct.cost_date) cost_date,

cps.period_name,

ce.cost_element_id ,

ce.cost_element_code ,

ac.analysis_group_id ,

pac.PERIODIC_AVG_COST unit_cost_average ,

NVL(ctc.unit_cost,0) transaction_cost ,

pac.currency_code ,

pac.uom_code ,

ac.analysis_id,

ac.default_analysis_code_flag,

ct.cost_profile_id

FROM cst_periodic_avg_costs pac,

cst_period_statuses cps,

cst_cost_elements_b ce ,

cst_analysis_codes_b ac,

cst_transactions ct,

cst_transaction_costs ctc

WHERE pac.cost_element_id = ce.cost_element_id

AND pac.cost_org_id = cps.cost_org_id

AND pac.cost_book_id= cps.cost_book_id

AND pac.period_name = cps.period_name

AND ct.cost_org_id = cps.cost_org_id

AND ct.cost_book_id= cps.cost_book_id

AND cps.cost_org_id = ctc.cost_org_id

AND cps.cost_book_id= ctc.cost_book_id

AND cps.period_name = ctc.period_name

AND ct.use_item_cost_flag ='N'

and (nvl(ctc.adjustment_transaction_id,0) <> 0 or nvl(ctc.user_adjustment_id,0) <> 0)

AND pac.inventory_item_id = ct.inventory_item_id

AND pac.val_unit_id = ct.val_unit_id

AND ctc.cost_element_id = ce.cost_element_id

AND ctc.transaction_id = ct.transaction_id

AND ctc.expense_pool_id <> -5

) x ,

cst_analysis_mapping_details y ,

cst_cost_profiles_b ccp,

cst_analysis_codes_vl z

WHERE y.cost_element_id (+) = x.cost_element_id

and y.analysis_group_id (+) = x.analysis_group_id

and z.analysis_id = nvl(y.analysis_id,x.analysis_id)

and x.cost_profile_id = ccp.cost_profile_id

and nvl(ccp.analysis_mapping_id ,-999)=

(

CASE

WHEN x.default_analysis_code_flag = 'Y'

THEN nvl(ccp.analysis_mapping_id, -999)

WHEN x.default_analysis_code_flag = 'N'

THEN y.analysis_mapping_id

END )

)

group by cost_org_id, cost_book_id, inventory_item_id, cost_method_code, val_unit_id,

transaction_id, cost_date, period_name, cost_element_type, cost_element_type_desc, cost_element_id,

cost_element_code, cost_element_code, currency_code, uom_code, analysis_group_id