ECC Cost Management, WIP Valuation (Process)

➜ Back to Library
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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Parameter Name SQL text Validation
Organization Code 
x.organization_code=:organization_code
 LOV
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: