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' -- 外协类型
优质生活从拆开始
