CST_MISSING_STD_COSTS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
COST_ORG_ID COST_ORG_NAME COST_ORG_CODE COST_BOOK_ID COST_BOOK_CODE PRIMARY_BOOK_FLAG RESOURCE_ID RESOURCE_CODE INVENTORY_ITEM_ID INVENTORY_ORG_ID ITEM_NUMBER DESCRIPTION VAL_UNIT_ID VAL_UNIT_CODE EFFECTIVE_START_DATE SCENARIO_ID SCENARIO_NUMBER

Name

COST_ORG_ID

COST_ORG_NAME

COST_ORG_CODE

COST_BOOK_ID

COST_BOOK_CODE

PRIMARY_BOOK_FLAG

RESOURCE_ID

RESOURCE_CODE

INVENTORY_ITEM_ID

INVENTORY_ORG_ID

ITEM_NUMBER

DESCRIPTION

VAL_UNIT_ID

VAL_UNIT_CODE

EFFECTIVE_START_DATE

SCENARIO_ID

SCENARIO_NUMBER

Query

SQL_Statement
SELECT ct.cost_org_id, cco.cost_org_name, cco.cost_org_code, ct.cost_book_id, ccb.cost_book_code, ct.primary_book_flag, ct.resource_id, wr.resource_code, ct.inventory_item_id, ct.inventory_org_id, esi.item_number, esi.description, ct.val_unit_id, cvu.val_unit_code, ct.effective_start_date, MIN( cs.scenario_id ) KEEP(DENSE_RANK FIRST ORDER BY cs.effective_start_date ) scenario_id, MIN( cs.scenario_number ) KEEP(DENSE_RANK FIRST ORDER BY cs.effective_start_date ) scenario_number FROM ( SELECT ct.cost_org_id, ct.cost_book_id, ccob.primary_book_flag, NULL resource_id, ct.inventory_item_id, ct.inventory_org_id, ct.val_unit_id, MIN(TRUNC(ct.transaction_date) ) effective_start_date FROM cst_transactions ct, cst_cost_profiles_b ccp, cst_cost_org_books ccob WHERE ct.cost_status IN ( 'N', 'E' ) AND ccp.cost_profile_id = ct.cost_profile_id AND ccp.cost_method_code = 'STANDARD' AND ccob.cost_org_id = ct.cost_org_id AND ccob.cost_book_id = ct.cost_book_id GROUP BY ct.cost_org_id, ct.cost_book_id, ccob.primary_book_flag, ct.inventory_item_id, ct.inventory_org_id, ct.val_unit_id UNION ALL SELECT crt.cost_org_id, crt.cost_book_id, ccob.primary_book_flag, crt.resource_id, NULL inventory_item_id, crt.inventory_organization_id, NULL val_unit_id, MIN(TRUNC(crt.transaction_date) ) effective_start_date FROM cst_resource_transactions crt, cst_cost_org_books ccob WHERE crt.costing_status IN ( 'N', 'E' ) AND ccob.cost_org_id = crt.cost_org_id AND ccob.cost_book_id = crt.cost_book_id GROUP BY crt.cost_org_id, crt.cost_book_id, ccob.primary_book_flag, crt.resource_id, crt.inventory_organization_id UNION ALL SELECT cs.cost_org_id, cs.cost_book_id, ccob.primary_book_flag, NULL resource_id, cwd.material_inventory_item_id, cwd.material_inventory_org_id, NULL val_unit_id, MIN(TRUNC(cs.effective_start_date) ) effective_start_date FROM cst_scenarios cs, cst_work_definitions cwd, cst_cost_org_books ccob WHERE cwd.scenario_id = cs.scenario_id AND ccob.cost_org_id = cs.cost_org_id AND ccob.cost_book_id = cs.cost_book_id GROUP BY cs.cost_org_id, cs.cost_book_id, ccob.primary_book_flag, cwd.material_inventory_item_id, cwd.material_inventory_org_id UNION ALL SELECT cs.cost_org_id, cs.cost_book_id, ccob.primary_book_flag, cwd.resource_id, NULL inventory_item_id, cwd.organization_id, NULL val_unit_id, MIN(TRUNC(cs.effective_start_date) ) effective_start_date FROM cst_scenarios cs, cst_work_definitions cwd, cst_cost_org_books ccob WHERE cwd.scenario_id = cs.scenario_id AND ccob.cost_org_id = cs.cost_org_id AND ccob.cost_book_id = cs.cost_book_id GROUP BY cs.cost_org_id, cs.cost_book_id, ccob.primary_book_flag, cwd.resource_id, cwd.organization_id ) ct, cst_cost_orgs_v cco, cst_cost_books_vl ccb, egp_system_items_vl esi, cst_val_units_vl cvu, cst_scenarios cs, wis_resources_vl wr WHERE cco.cost_org_id = ct.cost_org_id AND ccb.cost_book_id = ct.cost_book_id AND esi.inventory_item_id (+) = ct.inventory_item_id AND esi.organization_id (+) = ct.inventory_org_id AND esi.bom_item_type(+) NOT IN ('1') AND esi.costing_enabled_flag(+) = 'Y' AND cvu.val_unit_id (+) = ct.val_unit_id AND wr.resource_id (+) = ct.resource_id AND wr.organization_id (+) = ct.inventory_org_id AND wr.costed_flag (+) = 'Y' AND cs.cost_org_id (+) = ct.cost_org_id AND cs.cost_book_id (+) = ct.cost_book_id AND cs.scenario_type (+) = 'STANDARD' AND cs.state_code (+) \|\| '_' \|\| status_code (+) IN ( 'NEW_NEW', 'MODIFIED_IN_PROGRESS', 'ROLLUP_IN_SUCCESS', 'ROLLUP_IN_WARNING', 'ROLLUP_IN_ERROR') AND ( ( ct.inventory_item_id IS NOT NULL AND NOT EXISTS ( SELECT 'X' FROM cst_std_costs x WHERE x.cost_org_id = ct.cost_org_id AND x.cost_book_id = ct.cost_book_id AND x.inventory_item_id = ct.inventory_item_id AND x.val_unit_id = ct.val_unit_id AND x.effective_start_date <= ct.effective_start_date ) ) OR ( ct.resource_id IS NOT NULL AND NOT EXISTS ( SELECT 'X' FROM cst_std_resource_rates x WHERE x.cost_org_id = ct.cost_org_id AND x.cost_book_id = ct.cost_book_id AND x.resource_id = ct.resource_id AND x.effective_start_date <= ct.effective_start_date ) ) ) GROUP BY ct.cost_org_id, cco.cost_org_name, cco.cost_org_code, ct.cost_book_id, ccb.cost_book_code, ct.primary_book_flag, ct.resource_id, wr.resource_code, ct.inventory_item_id, ct.inventory_org_id, esi.item_number, esi.description, ct.val_unit_id, cvu.val_unit_code, ct.effective_start_date

SQL_Statement

SELECT

ct.cost_org_id,

cco.cost_org_name,

cco.cost_org_code,

ct.cost_book_id,

ccb.cost_book_code,

ct.primary_book_flag,

ct.resource_id,

wr.resource_code,

ct.inventory_item_id,

ct.inventory_org_id,

esi.item_number,

esi.description,

ct.val_unit_id,

cvu.val_unit_code,

ct.effective_start_date,

MIN( cs.scenario_id ) KEEP(DENSE_RANK FIRST

ORDER BY

cs.effective_start_date ) scenario_id,

MIN( cs.scenario_number ) KEEP(DENSE_RANK FIRST

ORDER BY

cs.effective_start_date ) scenario_number

FROM

(

SELECT

ct.cost_org_id,

ct.cost_book_id,

ccob.primary_book_flag,

NULL resource_id,

ct.inventory_item_id,

ct.inventory_org_id,

ct.val_unit_id,

MIN(TRUNC(ct.transaction_date) ) effective_start_date

FROM

cst_transactions ct,

cst_cost_profiles_b ccp,

cst_cost_org_books ccob

WHERE

ct.cost_status IN ( 'N', 'E' )

AND ccp.cost_profile_id = ct.cost_profile_id

AND ccp.cost_method_code = 'STANDARD'

AND ccob.cost_org_id = ct.cost_org_id

AND ccob.cost_book_id = ct.cost_book_id

GROUP BY

ct.cost_org_id,

ct.cost_book_id,

ccob.primary_book_flag,

ct.inventory_item_id,

ct.inventory_org_id,

ct.val_unit_id

UNION ALL

SELECT

crt.cost_org_id,

crt.cost_book_id,

ccob.primary_book_flag,

crt.resource_id,

NULL inventory_item_id,

crt.inventory_organization_id,

NULL val_unit_id,

MIN(TRUNC(crt.transaction_date) ) effective_start_date

FROM

cst_resource_transactions crt,

cst_cost_org_books ccob

WHERE

crt.costing_status IN ( 'N', 'E' )

AND ccob.cost_org_id = crt.cost_org_id

AND ccob.cost_book_id = crt.cost_book_id

GROUP BY

crt.cost_org_id,

crt.cost_book_id,

ccob.primary_book_flag,

crt.resource_id,

crt.inventory_organization_id

UNION ALL

SELECT

cs.cost_org_id,

cs.cost_book_id,

ccob.primary_book_flag,

NULL resource_id,

cwd.material_inventory_item_id,

cwd.material_inventory_org_id,

NULL val_unit_id,

MIN(TRUNC(cs.effective_start_date) ) effective_start_date

FROM

cst_scenarios cs,

cst_work_definitions cwd,

cst_cost_org_books ccob

WHERE

cwd.scenario_id = cs.scenario_id

AND ccob.cost_org_id = cs.cost_org_id

AND ccob.cost_book_id = cs.cost_book_id

GROUP BY

cs.cost_org_id,

cs.cost_book_id,

ccob.primary_book_flag,

cwd.material_inventory_item_id,

cwd.material_inventory_org_id

UNION ALL

SELECT

cs.cost_org_id,

cs.cost_book_id,

ccob.primary_book_flag,

cwd.resource_id,

NULL inventory_item_id,

cwd.organization_id,

NULL val_unit_id,

MIN(TRUNC(cs.effective_start_date) ) effective_start_date

FROM

cst_scenarios cs,

cst_work_definitions cwd,

cst_cost_org_books ccob

WHERE

cwd.scenario_id = cs.scenario_id

AND ccob.cost_org_id = cs.cost_org_id

AND ccob.cost_book_id = cs.cost_book_id

GROUP BY

cs.cost_org_id,

cs.cost_book_id,

ccob.primary_book_flag,

cwd.resource_id,

cwd.organization_id

)

ct,

cst_cost_orgs_v cco,

cst_cost_books_vl ccb,

egp_system_items_vl esi,

cst_val_units_vl cvu,

cst_scenarios cs,

wis_resources_vl wr

WHERE

cco.cost_org_id = ct.cost_org_id

AND ccb.cost_book_id = ct.cost_book_id

AND esi.inventory_item_id (+) = ct.inventory_item_id

AND esi.organization_id (+) = ct.inventory_org_id

AND esi.bom_item_type(+) NOT IN ('1')

AND esi.costing_enabled_flag(+) = 'Y'

AND cvu.val_unit_id (+) = ct.val_unit_id

AND wr.resource_id (+) = ct.resource_id

AND wr.organization_id (+) = ct.inventory_org_id

AND wr.costed_flag (+) = 'Y'

AND cs.cost_org_id (+) = ct.cost_org_id

AND cs.cost_book_id (+) = ct.cost_book_id

AND cs.scenario_type (+) = 'STANDARD'

AND cs.state_code (+)

|| '_'

|| status_code (+) IN ( 'NEW_NEW', 'MODIFIED_IN_PROGRESS', 'ROLLUP_IN_SUCCESS', 'ROLLUP_IN_WARNING', 'ROLLUP_IN_ERROR')

AND

(

ct.inventory_item_id IS NOT NULL

AND NOT EXISTS

(

SELECT

'X'

FROM

cst_std_costs x

WHERE

x.cost_org_id = ct.cost_org_id

AND x.cost_book_id = ct.cost_book_id

AND x.inventory_item_id = ct.inventory_item_id

AND x.val_unit_id = ct.val_unit_id

AND x.effective_start_date <= ct.effective_start_date

)

OR

(

ct.resource_id IS NOT NULL

AND NOT EXISTS

(

SELECT

'X'

FROM

cst_std_resource_rates x

WHERE

x.cost_org_id = ct.cost_org_id

AND x.cost_book_id = ct.cost_book_id

AND x.resource_id = ct.resource_id

AND x.effective_start_date <= ct.effective_start_date

)

GROUP BY

ct.cost_org_id,

cco.cost_org_name,

cco.cost_org_code,

ct.cost_book_id,

ccb.cost_book_code,

ct.primary_book_flag,

ct.resource_id,

wr.resource_code,

ct.inventory_item_id,

ct.inventory_org_id,

esi.item_number,

esi.description,

ct.val_unit_id,

cvu.val_unit_code,

ct.effective_start_date