WIP Discrete Job Data

SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID1,
       DJ.CLASS_CODE Class_Code,
       ML.MEANING Class_Type,
       WAC.DISABLE_DATE Disable_Date,
       WAC.DESCRIPTION Description,
       DECODE(DJ.MATERIAL_ACCOUNT,NULL,NULL,
        null) C_Mtl_Val_Data,
       DECODE(DJ.MATERIAL_OVERHEAD_ACCOUNT,NULL,NULL,
        null) C_Mtl_OH_Val_Data,
       DECODE(DJ.RESOURCE_ACCOUNT,NULL,NULL,
        null) C_Res_Val_Data,
       DECODE(DJ.OUTSIDE_PROCESSING_ACCOUNT,NULL,NULL,
        null) C_Out_Proc_Val_Data,
       DECODE(DJ.OVERHEAD_ACCOUNT,NULL,NULL,
        null) C_OH_Val_Data,
       DECODE(DJ.MATERIAL_VARIANCE_ACCOUNT,NULL,NULL,
        null) C_Mtl_Variance_Data,
       DECODE(DJ.STD_COST_ADJUSTMENT_ACCOUNT,NULL,NULL,
        null) C_Std_Cost_Variance_Data,
       DECODE(DJ.RESOURCE_VARIANCE_ACCOUNT,NULL,NULL,
        null) C_Res_Variance_Data,
       DECODE(DJ.OUTSIDE_PROC_VARIANCE_ACCOUNT,NULL,NULL,
        null) C_Out_Proc_Variance_Data,
       DECODE(DJ.OVERHEAD_VARIANCE_ACCOUNT,NULL,NULL,
        null) C_OH_Variance_Data, 
	DECODE(DJ.MATERIAL_ACCOUNT,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_material_valuation', 'SQLGL', 'GL#', GL1.CHART_OF_ACCOUNTS_ID, NULL, GL1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) C_Material_Valuation, 
	DECODE(DJ.MATERIAL_OVERHEAD_ACCOUNT,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_material_overh', 'SQLGL', 'GL#', GL2.CHART_OF_ACCOUNTS_ID, NULL, GL2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) C_Material_Overhead_Valuat, 
	DECODE(DJ.RESOURCE_ACCOUNT,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_resource_valuation', 'SQLGL', 'GL#', GL3.CHART_OF_ACCOUNTS_ID, NULL, GL3.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) C_Resource_Valuation, 
	DECODE(DJ.OUTSIDE_PROCESSING_ACCOUNT,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_outside_proc_valuation', 'SQLGL', 'GL#', GL4.CHART_OF_ACCOUNTS_ID, NULL, GL4.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) C_Outside_Proc_Valuation, 
	DECODE(DJ.OVERHEAD_ACCOUNT,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_overhead_valuation', 'SQLGL', 'GL#', GL5.CHART_OF_ACCOUNTS_ID, NULL, GL5.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) C_Overhead_Valuation, 
	DECODE(DJ.MATERIAL_VARIANCE_ACCOUNT,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_material_variance', 'SQLGL', 'GL#', GL6.CHART_OF_ACCOUNTS_ID, NULL, GL6.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) C_Material_Variance, 
	DECODE(DJ.STD_COST_ADJUSTMENT_ACCOUNT,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_standard_cost_variance', 'SQLGL', 'GL#', GL7.CHART_OF_ACCOUNTS_ID, NULL, GL7.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) C_Standard_Cost_Variance, 
	DECODE(DJ.RESOURCE_VARIANCE_ACCOUNT,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_resource_variance', 'SQLGL', 'GL#', GL8.CHART_OF_ACCOUNTS_ID, NULL, GL8.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) C_Resource_Variance, 
	DECODE(DJ.OUTSIDE_PROC_VARIANCE_ACCOUNT,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_outside_proc_variance', 'SQLGL', 'GL#', GL9.CHART_OF_ACCOUNTS_ID, NULL, GL9.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) C_Outside_Proc_Variance, 
	DECODE(DJ.OVERHEAD_VARIANCE_ACCOUNT,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_overhead_variance', 'SQLGL', 'GL#', GL10.CHART_OF_ACCOUNTS_ID, NULL, GL10.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) C_Overhead_Variance
FROM   MFG_LOOKUPS ML,
       GL_CODE_COMBINATIONS GL1,
       GL_CODE_COMBINATIONS GL2,
       GL_CODE_COMBINATIONS GL3,
       GL_CODE_COMBINATIONS GL4,
       GL_CODE_COMBINATIONS GL5,
       GL_CODE_COMBINATIONS GL6,
       GL_CODE_COMBINATIONS GL7,
       GL_CODE_COMBINATIONS GL8,
       GL_CODE_COMBINATIONS GL9,
       GL_CODE_COMBINATIONS GL10,
       WIP_ACCOUNTING_CLASSES WAC,
       WIP_DISCRETE_JOBS DJ,
       WIP_ENTITIES WE
WHERE  DJ.ORGANIZATION_ID = :P_ORG_ID
AND    WAC.ORGANIZATION_ID = :P_ORG_ID
AND    WAC.CLASS_TYPE != 5
AND    DJ.CLASS_CODE = WAC.CLASS_CODE
AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND    DJ.MATERIAL_ACCOUNT = GL1.CODE_COMBINATION_ID (+)
AND    DJ.MATERIAL_OVERHEAD_ACCOUNT = GL2.CODE_COMBINATION_ID (+)
AND    DJ.RESOURCE_ACCOUNT = GL3.CODE_COMBINATION_ID (+)
AND    DJ.OUTSIDE_PROCESSING_ACCOUNT = GL4.CODE_COMBINATION_ID (+)
AND    DJ.OVERHEAD_ACCOUNT = GL5.CODE_COMBINATION_ID (+)
AND    DJ.MATERIAL_VARIANCE_ACCOUNT = GL6.CODE_COMBINATION_ID (+)
AND    DJ.STD_COST_ADJUSTMENT_ACCOUNT = GL7.CODE_COMBINATION_ID (+)
AND    DJ.RESOURCE_VARIANCE_ACCOUNT = GL8.CODE_COMBINATION_ID (+)
AND    DJ.OUTSIDE_PROC_VARIANCE_ACCOUNT = GL9.CODE_COMBINATION_ID (+)
AND    DJ.OVERHEAD_VARIANCE_ACCOUNT = GL10.CODE_COMBINATION_ID (+)
AND    GL1.CHART_OF_ACCOUNTS_ID (+) = :CHART_OF_ACCOUNTS_ID_DEF
AND    GL2.CHART_OF_ACCOUNTS_ID (+) = :CHART_OF_ACCOUNTS_ID_DEF
AND    GL3.CHART_OF_ACCOUNTS_ID (+) = :CHART_OF_ACCOUNTS_ID_DEF
AND    GL4.CHART_OF_ACCOUNTS_ID (+) = :CHART_OF_ACCOUNTS_ID_DEF
AND    GL5.CHART_OF_ACCOUNTS_ID (+) = :CHART_OF_ACCOUNTS_ID_DEF
AND    GL6.CHART_OF_ACCOUNTS_ID (+) = :CHART_OF_ACCOUNTS_ID_DEF
AND    GL7.CHART_OF_ACCOUNTS_ID (+) = :CHART_OF_ACCOUNTS_ID_DEF
AND    GL8.CHART_OF_ACCOUNTS_ID (+) = :CHART_OF_ACCOUNTS_ID_DEF
AND    GL9.CHART_OF_ACCOUNTS_ID (+) = :CHART_OF_ACCOUNTS_ID_DEF
AND    GL10.CHART_OF_ACCOUNTS_ID (+) = :CHART_OF_ACCOUNTS_ID_DEF
AND    ML.LOOKUP_TYPE = 'WIP_CLASS_TYPE'
AND    ML.LOOKUP_CODE = WAC.CLASS_TYPE 
 and DJ.WIP_ENTITY_ID=:WIP_Entity_ID