EBS: WIP 工单类型

-- WIP工单类型
-- N: WIP>>设置>>WIP 工单类型
select WAC.ORGANIZATION_ID,
       (SELECT HOU.NAME FROM HR_ORGANIZATION_UNITS HOU WHERE HOU.organization_id = WAC.ORGANIZATION_ID AND ROWNUM =1) AS "组织",
       WAC.CLASS_CODE AS "分类", wac.DESCRIPTION AS "说明", --CLASS_DESCRIPTION,
       DECODE(WAC.CLASS_TYPE,'1','标准离散',
       '2','重复性装配',
       '3','非标准资产',
       '4','非标准费用',
       '5','基于标准批次',
       '6','维护',
       '7','基于非标准批次',
       WAC.CLASS_TYPE) AS "类型",
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.MATERIAL_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "物料帐户" ,
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>50368,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.MATERIAL_ACCOUNT   --  CODE_COMBINZATION_ID 
        )  AS "物料帐户描述",
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.MATERIAL_VARIANCE_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "物料帐户-差异" , 
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.MATERIAL_VARIANCE_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "物料帐户描述-差异", 
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.RESOURCE_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "资源帐户" ,
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.RESOURCE_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "资源帐户描述",
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.RESOURCE_VARIANCE_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "资源-差异" ,
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.RESOURCE_VARIANCE_ACCOUNT   --  CODE_COMBINZATION_ID 
        )  AS "资源描述-差异",
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.OUTSIDE_PROCESSING_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "外协加工" , 
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.OUTSIDE_PROCESSING_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "外协加工描述", 
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.OUTSIDE_PROC_VARIANCE_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "外协加工-差异" ,
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>50368,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.OUTSIDE_PROC_VARIANCE_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "外协加工描述-差异",
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.OVERHEAD_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "制造费用" ,
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.OVERHEAD_ACCOUNT   --  CODE_COMBINZATION_ID 
        )  AS "制造费用描述",
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.OVERHEAD_VARIANCE_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "制造费用-差异" , --制造费用帐户-差异
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID ,-- 50368,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.OVERHEAD_VARIANCE_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "制造费用描述-差异", --制造费用帐户描述-差异
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.MATERIAL_OVERHEAD_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "物料间接费用" , --物料间接费用帐户
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.MATERIAL_OVERHEAD_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "物料间接费用描述", --物料间接费用帐户描述
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.STD_COST_ADJUSTMENT_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "标准成本-差异" , 
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.STD_COST_ADJUSTMENT_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "标准成本描述-差异", 
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.BRIDGING_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "过渡-差异" , 
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.BRIDGING_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "过渡描述-差异", 
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.EXPENSE_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "费用-差异" , 
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.EXPENSE_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "费用描述-差异", 
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.EST_SCRAP_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "估计废料" , 
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.EST_SCRAP_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "会计废料描述", 
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.EST_SCRAP_VAR_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "会计废料-差异" , 
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.EST_SCRAP_VAR_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "会计废料描述-差异", 
       fnd_flex_ext.get_segs(application_short_name =>'SQLGL', --     IN  VARCHAR2,
                    key_flex_code      =>'GL#', --         IN  VARCHAR2,
                    structure_number   =>OOD.CHART_OF_ACCOUNTS_ID, --         IN  NUMBER,  -- CHART_OF_ACCOUNT_ID
                    combination_id     =>WAC.ENCUMBRANCE_ACCOUNT --        IN  NUMBER   -- code_Combination_id 
                    ) AS "保留款" , 
       xla_oa_functions_pkg.get_ccid_description(p_coa_id =>OOD.CHART_OF_ACCOUNTS_ID,  -- CHART_OF_ACCOUNT_ID
       p_ccid => WAC.ENCUMBRANCE_ACCOUNT   --  CODE_COMBINZATION_ID 
       )  AS "保留款描述", 
       DECODE(WAC.COMPLETION_COST_SOURCE,'1','系统计算','2','自定义',WAC.COMPLETION_COST_SOURCE) AS "完成成本来源",  --COMPLETION_COST_SOURCE_MEANING
     DECODE( WAC.SYSTEM_OPTION_ID, '1','使用实际资源','2','使用预定义资源',WAC.SYSTEM_OPTION_ID) AS "系统选项",
    ( SELECT CCT.COST_TYPE  FROM CST_COST_TYPES CCT WHERE CCT.COST_TYPE_ID =  WAC.COST_TYPE_ID AND ROWNUM=1) AS "成本类型"
   -- , WAC.* 
from --wip_discrete_jobs_v wdj,
     WIP_ACCOUNTING_CLASSES WAC,
     ORG_ORGANIZATION_DEFINITIONS OOD
where 1=1 --wac.class_code =  wdj.class_code 
--AND WAC.ORGANIZATION_ID = WDJ.organization_id
--and wdj.creation_date > sysdate - 1  
 AND WAC.ORGANIZATION_ID = 301 
 AND WAC.ORGANIZATION_ID = OOD.ORGANIZATION_ID
  -- and wac.CLASS_CODE LIKE '%WX'  -- 外协类型