CST_UNPUB_ITEMS_RESOURCES_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
NODE DESCRIPTION EXCEPTION_TYPE ITEM_RESOURCE_ID TRANS_INV_ORG_ID ERROR_CODE SCENARIO_INV_ORG_ID PLANNING_COST_ORG_ID TRANS_COST_ORG_ID TRANS_COST_BOOK_ID SCENARIO_HEADER_ID SCENARIO_ID SCENARIO_NUMBER SCENARIO_VERSION_NUMBER SCENARIO_VERSION_TYPE LAST_PUBLISH_DATE TXN_COUNT REF_ID REF_LINK

Query

SQL_Statement

select distinct node, description, exception_type, trans_exception.ITEM_RESOURCE_ID ITEM_RESOURCE_ID, trans_exception.trans_inv_org_id trans_inv_org_id, trans_exception.error_code, last_pub_scenario.INV_ORG_ID scenario_inv_org_id, last_pub_scenario.PLANNING_COST_ORG_ID, trans_exception.TRANS_COST_ORG_ID trans_cost_org_id, trans_exception.TRANS_COST_BOOK_ID trans_cost_book_id, last_pub_scenario.scenario_header_id SCENARIO_HEADER_ID, last_pub_scenario.scenario_id SCENARIO_ID, last_pub_scenario.scenario_number SCENARIO_NUMBER, last_pub_scenario.scenario_version_number SCENARIO_VERSION_NUMBER, last_pub_scenario.scenario_version_type SCENARIO_VERSION_TYPE, last_pub_scenario.LAST_UPDATE_DATE LAST_PUBLISH_DATE, trans_exception.txn_count, last_pub_scenario.scenario_id Ref_id, last_pub_scenario.scenario_number Ref_Link from (select distinct esi.item_number node, esi.description description, cuir.inventory_item_id item_resource_id, cuir.trans_cost_org_id, cuir.trans_cost_book_id, cuir.trans_inv_org_id, cuir.EXCEPTION_TYPE, cuir.error_code, cuir.txn_count from egp_system_items_vl esi, (select item_trans.exception_type, item_trans.trans_inv_org_id, item_trans.trans_cost_org_id, item_trans.trans_cost_book_id, item_trans.inventory_item_id, item_trans.request_id, item_trans.error_code, item_trans.num txn_count, error_count.row_count from (select distinct 'ITEM' exception_type, ct.inventory_item_id, ct.INVENTORY_ORG_ID trans_inv_org_id, ccp.request_id, ccp.error_code, ccp.row_count, ct.COST_ORG_ID trans_cost_org_id, ct.COST_BOOK_ID trans_cost_book_id, count(ct.transaction_id) over (PARTITION by ct.inventory_item_id,ct.INVENTORY_ORG_ID,ct.COST_ORG_ID, ct.COST_BOOK_ID,ct.request_id) num from fusion.cst_transactions ct, fusion.cst_transaction_errors cte, fusion.cst_cp_errors ccp where ct.transaction_id = cte.TRANSACTION_ID and ct.REQUEST_ID = cte.REQUEST_ID and cte.CP_ERROR_ID = ccp.CP_ERROR_ID and ccp.ERROR_CODE = 'CST_CPROC_STD_COST_MISSING' ) item_trans, (select distinct a.cost_org_id, a.COST_BOOK_ID, a.row_count, a.request_id from cst_cp_errors a, (select distinct max(b.request_id) over (PARTITION by b.cost_org_id,b.COST_BOOK_ID) request_id from cst_cp_errors b where ERROR_CODE = 'CST_CPROC_STD_COST_MISSING') c where ERROR_CODE = 'CST_CPROC_STD_COST_MISSING' and a.request_id = c.request_id ) error_count where item_trans.trans_cost_org_id = error_count.cost_org_id and item_trans.trans_cost_book_id = error_count.COST_BOOK_ID and item_trans.request_id = error_count.request_id ) cuir where cuir.inventory_item_id = esi.inventory_item_id and cuir.trans_inv_org_id = esi.organization_id union all select distinct resources.resource_name node, null description, crt.resource_id item_resource_id, resources.cost_org_id, resources.cost_book_id, crt.INVENTORY_ORGANIZATION_ID trans_inv_org_id, 'RESOURCE' EXCEPTION_TYPE, resources.error_code, resources.row_count num from (select distinct a.cost_org_id ,a.COST_BOOK_ID ,a.error_code ,a.TOKEN1_NAME ,a.TOKEN1_VALUE ,a.TOKEN2_NAME ,a.TOKEN2_VALUE ,a.TOKEN3_NAME ,a.TOKEN3_VALUE resource_name ,a.TOKEN4_NAME ,a.TOKEN4_VALUE ,a.row_count ,a.request_id ,a.cp_error_id ,a.last_update_date from cst_cp_errors a where a.cost_org_id is not null and ERROR_CODE = 'CST_CPROC_STD_RES_RATE_MISSING' and ( a.request_id) = (select distinct max(request_id) from cst_cp_errors b where b.cost_org_id = a.cost_org_id and b.cost_book_id = a.cost_book_id and ERROR_CODE = 'CST_CPROC_STD_RES_RATE_MISSING' ) and (a.TOKEN4_VALUE) = (select distinct max(TOKEN4_VALUE) from cst_cp_errors b where b.cost_org_id = a.cost_org_id and b.cost_book_id = a.cost_book_id and ERROR_CODE = 'CST_CPROC_STD_RES_RATE_MISSING' ) ) resources, fusion.CST_RESOURCE_TRANSACTIONS crt, fusion.cst_transaction_errors cte where crt.RESOURCE_TRANSACTION_ID = cte.TRANSACTION_ID and crt.REQUEST_ID = cte.REQUEST_ID and cte.CP_ERROR_ID = resources.CP_ERROR_ID ) trans_exception, ( select sce.scenario_header_id, sce.scenario_id, sce.scenario_number, sce.SCENARIO_VERSION_NUMBER, sce.SCENARIO_VERSION_TYPE, sce.INV_ORG_ID, sce.PLANNING_COST_ORG_ID, sce.COST_ORG_ID, csph.COST_BOOK_ID, csph.LAST_UPDATE_DATE from fusion.CST_SCENARIOS sce, fusion.CST_SCENARIO_PUBLISH_HEADERS csph where sce.scenario_id = csph.scenario_id and csph.LAST_UPDATE_DATE = (select max(a.last_update_date) from fusion.CST_SCENARIO_PUBLISH_HEADERS a where a.cost_book_id = csph.cost_book_id ) ) last_pub_scenario where trans_exception.TRANS_COST_ORG_ID = last_pub_scenario.COST_ORG_ID (+) and trans_exception.TRANS_COST_BOOK_ID = last_pub_scenario.COST_BOOK_ID (+)