ECC Cost Management, WIP Valuation (Process)
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: cst-pval-batch
Query Procedure: CST_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: CST_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Dataset Key: cst-pval-batch
Query Procedure: CST_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: CST_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Run
ECC Cost Management, WIP Valuation (Process) and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM (SELECT ECC_SPEC_ID , org.currency_code||wipval.report_period||wipval.report_mode_code CURR_PERIOD_MODE , rep_type.meaning REPORT_MODE , org.ORGANIZATION_CODE , org.ORGANIZATION_NAME , org.SOB_NAME LEDGER , org.OPERATING_UNIT , org.LEGAL_ENTITY , org.CURRENCY_CODE , VALUATION_COST_TYPE, ITEM , msi_tl.description ITEM_DESCRIPTION , usage_lkp.meaning USAGE_VAR_TYPE , usage_lkp_alt.meaning USAGE_VAR_SUBTYPE, COST_CMPNTCLS_CODE, ACCOUNT , ACC_DESCRIPTION , bcf_lkp.meaning BATCH_CLOSED_IN_PERIOD, pcf_lkp.meaning PERIOD_CLOSED , org.LANGUAGE , PERIOD_BATCH_KEY , REPORT_MODE_CODE , BATCH_NO , BATCH_ID , wipval.ORGANIZATION_ID , ACTUAL_START_DATE , ACTUAL_CMPLT_DATE , BATCH_CLOSE_DATE , BATCH_STATUS_CODE, bs_lkp.meaning BATCH_STATUS , SCHEDULE_CLOSE_DATE , REPORT_PERIOD , TXN_PERIOD , ACCT_PERIOD_ID , TXN_PERIOD_ID , REP_PERIOD_YEAR , REP_PERIOD_NUM , REP_PERIOD_YEAR_NUM , TXN_PERIOD_YEAR , TXN_PERIOD_NUM , TXN_PERIOD_YEAR_NUM , PERIOD_CLOSED_FLAG , BATCH_CLOSED_IN_PERIOD_FLAG, wipval.INVENTORY_ITEM_ID , USAGE_IND , USAGE_IND_ALT , COST_CMPNTCLS_ID , COST_ANALYSIS_CODE , CODE_COMBINATION_ID , ECC_LAST_UPDATE_DATE , FINAL_ACCOUNTED , DELETE_FLAG , COST_TYPE_ID , COSTS_INCURRED , COSTS_RELIEVED , VARIANCES_RELIEVED , SINGLE_LEVEL_VAR , PERIOD_ACTIVITY , ENDING_BALANCE FROM (SELECT wipval.acct_period_id || '-' || wipval.batch_id || '-' || wipval.report_mode_code || '-' || wipval.txn_period_id || '-' || wipval.code_combination_id || '-' || wipval.usage_ind || '-' || wipval.cost_cmpntcls_id || '-' || wipval.cost_analysis_code || '-' || wipval.usage_ind_alt || '-' || wipval.final_accounted || '-' || wipval.cost_type_id ecc_spec_id, NULL /* org.currency_code||wipval.report_period||wipval.report_mode_code */ curr_period_mode, NULL /* rep_type.meaning */ report_mode, NULL organization_code, NULL organization_name, NULL ledger, NULL operating_unit, NULL legal_entity, NULL currency_code, cmm.cost_mthd_code valuation_cost_type, msi.concatenated_segments item, NULL item_description, NULL /* usage_lkp.meaning */ usage_var_type, NULL /* usage_lkp_alt.meaning */ usage_var_subtype, cmpt_mst.cost_cmpntcls_code, /* gcc.concatenated_segments account, gl_flexfields_pkg.get_concat_description(gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description, */ wipval.account, wipval.acc_description, NULL /* bcf_lkp.meaning */ batch_closed_in_period, NULL /* pcf_lkp.meaning */ period_closed, NULL /*org.language*/ language , TO_CHAR(wipval.batch_status) /* bs_lkp.meaning */ batch_status, wipval.acct_period_id||'-'||wipval.batch_id period_batch_key, wipval.report_mode_code, wipval.batch_no, wipval.batch_id, wipval.organization_id, wipval.actual_start_date, wipval.actual_cmplt_date, wipval.batch_close_date, wipval.batch_status batch_status_code, wipval.schedule_close_date, wipval.report_period, wipval.txn_period, wipval.acct_period_id, wipval.txn_period_id, wipval.rep_period_year, wipval.rep_period_num, wipval.rep_period_year_num, wipval.txn_period_year, wipval.txn_period_num, wipval.txn_period_year_num, wipval.period_closed_flag, wipval.batch_closed_in_period_flag, wipval.inventory_item_id, wipval.usage_ind, wipval.usage_ind_alt, wipval.cost_cmpntcls_id, wipval.cost_analysis_code, wipval.code_combination_id, wipval.ecc_last_update_date, wipval.final_accounted, wipval.delete_flag, wipval.cost_type_id, wipval.costs_incurred, wipval.costs_relieved, wipval.variances_relieved, wipval.single_level_var, wipval.period_activity, wipval.ending_balance FROM ( WITH balances AS ( SELECT bh.batch_no, bh.batch_id, bh.organization_id, bh.actual_start_date, bh.actual_cmplt_date, bh.batch_close_date, bh.batch_status, oap.schedule_close_date, oap.period_name report_period, oap.period_year rep_period_year, oap.period_num rep_period_num, oap.period_year||'-'||oap.period_num rep_period_year_num, pb.period_name txn_period, pb.period_year txn_period_year, pb.period_num txn_period_num, pb.period_year||'-'||pb.period_num txn_period_year_num, oap.acct_period_id, pb.acct_period_id txn_period_id, CASE WHEN decode(nvl(oap.period_close_date, sysdate), oap.period_close_date, decode(oap.open_flag, 'N', decode (oap.summarized_flag, 'N', 'CNS', 'C'), 'Y', 'E', 'P', 'P', 'E'), 'O') = 'C' THEN 'Y' ELSE 'N' END period_closed_flag, CASE WHEN bh.batch_close_date IS NOT NULL AND trunc(bh.batch_close_date) BETWEEN oap.period_start_date AND oap.schedule_close_date THEN 'Y' ELSE 'N' END batch_closed_in_period_flag, gvr.inventory_item_id, pb.usage_ind, pb.usage_ind_alt, pb.cost_cmpntcls_id, pb.cost_analysis_code, pb.code_combination_id, pb.account, pb.acc_description, pb.ecc_last_update_date, pb.final_accounted, pb.delete_flag, pb.cost_type_id, pb.value_in, pb.value_out, pb.value_var, pb.value_sing_lvl_var, ( pb.value_in - pb.value_out - pb.value_var ) net_activity FROM ( SELECT b.batch_id, b.organization_id, b.actual_start_date, b.acct_period_id, b.period_name, b.period_year, b.period_num, nvl(usage_ind, 0) usage_ind, nvl(usage_ind_alt, 0) usage_ind_alt, nvl(cost_cmpntcls_id, 0) cost_cmpntcls_id, nvl(cost_analysis_code, 'NULL') cost_analysis_code, nvl(bal.code_combination_id, 0) code_combination_id, nvl(bal.ecc_last_update_date, b.ecc_last_update_date) ecc_last_update_date, nvl(final_accounted, 'N') final_accounted, nvl(delete_flag, 'N') delete_flag, bal.cost_type_id cost_type_id, ( nvl(bal.value_in, 0) ) value_in, ( nvl(bal.value_out, 0) ) value_out, ( nvl(bal.value_var, 0) ) value_var, ( nvl(bal.value_sing_lvl_var, 0) ) value_sing_lvl_var, ( nvl(bal.value, 0) ) value, gcc.concatenated_segments account, gl_flexfields_pkg.get_concat_description(gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description FROM ( SELECT gbh.batch_no, gbh.batch_id, gbh.organization_id, gbh.actual_start_date, oap.acct_period_id, oap.period_name, oap.period_year, oap.period_num, greatest(gbh.last_update_date, oap.last_update_date) ecc_last_update_date FROM org_acct_periods oap, gme_batch_header gbh WHERE nvl(gbh.update_inventory_ind, 'N') = 'Y' AND gbh.actual_start_date IS NOT NULL AND gbh.organization_id = oap.organization_id AND oap.schedule_close_date >= to_date(cst_ecc_util_pvt.get_val_load_start_date, 'MM/DD/YYYY') AND trunc(gbh.actual_start_date) <= oap.schedule_close_date AND nvl(trunc(gbh.batch_close_date), oap.period_start_date) >= oap.period_start_date ) b, cst_ecc_val_batch_balances bal, gl_code_combinations_kfv gcc WHERE b.batch_id = bal.batch_id (+) AND b.acct_period_id = bal.acct_period_id (+) AND bal.code_combination_id = gcc.code_combination_id (+) ) pb, gme_batch_header bh, gmd_recipe_validity_rules gvr, org_acct_periods oap WHERE bh.organization_id = oap.organization_id AND pb.batch_id = bh.batch_id AND pb.organization_id = bh.organization_id AND bh.recipe_validity_rule_id = gvr.recipe_validity_rule_id AND pb.acct_period_id <= oap.acct_period_id AND trunc(bh.actual_start_date) <= oap.schedule_close_date AND nvl(trunc(bh.batch_close_date), oap.period_start_date) >= oap.period_start_date AND oap.period_start_date <= sysdate ) SELECT '1' report_mode_code, batch_no, batch_id, organization_id, actual_start_date, actual_cmplt_date, batch_close_date, batch_status, schedule_close_date, report_period, txn_period, acct_period_id, txn_period_id, rep_period_year, rep_period_num, rep_period_year_num, txn_period_year, txn_period_num, txn_period_year_num, period_closed_flag, batch_closed_in_period_flag, inventory_item_id, usage_ind, usage_ind_alt, cost_cmpntcls_id, cost_analysis_code, code_combination_id, account, acc_description, ecc_last_update_date, final_accounted, delete_flag, cost_type_id, decode(txn_period_id, acct_period_id, value_in, 0) costs_incurred, decode(txn_period_id, acct_period_id, value_out, 0) costs_relieved, decode(txn_period_id, acct_period_id, value_var, 0) variances_relieved, decode(txn_period_id, acct_period_id, value_sing_lvl_var, 0) single_level_var, decode(txn_period_id, acct_period_id, net_activity, 0) period_activity, net_activity ending_balance FROM balances UNION ALL SELECT '2' report_mode_code, batch_no, batch_id, organization_id, actual_start_date, actual_cmplt_date, batch_close_date, batch_status, schedule_close_date, report_period, txn_period, acct_period_id, txn_period_id, rep_period_year, rep_period_num, rep_period_year_num, txn_period_year, txn_period_num, txn_period_year_num, period_closed_flag, batch_closed_in_period_flag, inventory_item_id, usage_ind, usage_ind_alt, cost_cmpntcls_id, cost_analysis_code, code_combination_id, account, acc_description, ecc_last_update_date, final_accounted, delete_flag, cost_type_id, value_in costs_incurred, value_out costs_relieved, value_var variances_relieved, value_sing_lvl_var single_level_var, net_activity period_activity, net_activity ending_balance FROM balances ) wipval, ( SELECT cost_cmpntcls_id, cost_cmpntcls_code FROM cm_cmpt_mst UNION SELECT 0, 'NULL' FROM dual ) cmpt_mst, cm_mthd_mst cmm, mtl_system_items_kfv msi WHERE cmpt_mst.cost_cmpntcls_id = wipval.cost_cmpntcls_id AND wipval.inventory_item_id = msi.inventory_item_id AND wipval.organization_id = msi.organization_id AND nvl(wipval.cost_type_id,-1) = cmm.cost_type_id (+)) wipval, mtl_system_items_tl msi_tl, cst_ecc_org_struct_tl_gt org, FND_LOOKUP_VALUES usage_lkp, FND_LOOKUP_VALUES usage_lkp_alt, FND_LOOKUP_VALUES rep_type, FND_LOOKUP_VALUES bcf_lkp, FND_LOOKUP_VALUES pcf_lkp, FND_LOOKUP_VALUES bs_lkp where wipval.organization_id = org.organization_id AND usage_lkp.lookup_code = wipval.usage_ind AND usage_lkp.view_application_id = 555 AND usage_lkp.security_group_id = 0 AND usage_lkp.lookup_type = 'GMF_USAGE_AND_VAR_TYPE' AND usage_lkp.language = org.language AND usage_lkp.language = usage_lkp_alt.language AND usage_lkp_alt.lookup_code = wipval.usage_ind_alt AND usage_lkp_alt.lookup_type = 'GMF_USAGE_AND_VAR_TYPE' AND usage_lkp_alt.view_application_id = 555 AND usage_lkp_alt.security_group_id = 0 AND usage_lkp_alt.language = org.language AND usage_lkp_alt.language = rep_type.language AND rep_type.lookup_code = wipval.report_mode_code AND rep_type.lookup_type = 'WIP_REP_VAL_TYPE' AND rep_type.view_application_id = 700 AND rep_type.security_group_id = 0 AND rep_type.language = org.language AND rep_type.language = bcf_lkp.language AND bcf_lkp.lookup_code = wipval.batch_closed_in_period_flag AND bcf_lkp.lookup_type = 'YES_NO' AND bcf_lkp.view_application_id = 0 AND bcf_lkp.security_group_id = 0 AND bcf_lkp.language = org.language AND bcf_lkp.language = pcf_lkp.language AND pcf_lkp.lookup_code = wipval.period_closed_flag AND pcf_lkp.lookup_type = 'YES_NO' AND pcf_lkp.view_application_id = 0 AND pcf_lkp.security_group_id = 0 AND pcf_lkp.language = org.language AND pcf_lkp.language = msi_tl.language AND wipval.organization_id = msi_tl.organization_id AND wipval.inventory_item_id = msi_tl.inventory_item_id AND msi_tl.language = org.language AND TO_CHAR (wipval.batch_status) = bs_lkp.LOOKUP_CODE AND bs_lkp.lookup_type = 'GME_BATCH_STATUS' AND bs_lkp.view_application_id = 553 AND bs_lkp.security_group_id = 0 AND bs_lkp.language = org.language AND msi_tl.language = bs_lkp.language AND org.language in ('US') ) PIVOT ( max(ITEM_DESCRIPTION) as ITEM_DESCRIPTION, max(ORGANIZATION_NAME) as ORGANIZATION_NAME, max(OPERATING_UNIT) as OPERATING_UNIT, max(REPORT_MODE) as REPORT_MODE, max(PERIOD_CLOSED) as PERIOD_CLOSED, max(BATCH_CLOSED_IN_PERIOD) as BATCH_CLOSED_IN_PERIOD, max(USAGE_VAR_TYPE) as USAGE_VAR_TYPE, max(USAGE_VAR_SUBTYPE) as USAGE_VAR_SUBTYPE, max(BATCH_STATUS) as BATCH_STATUS for LANGUAGE in ('US' "US")) ) x where 2=2 |