EBS: WIP 工单类型

100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
-- 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'  -- 外协类型