CRP Bill of Resources

SELECT   &P_FLEXDATA                          C_FLEXDATA,
         rh.bill_of_resources                 C_resource_scenario_name,
         rh.organization_id                   C_organization_id,
         rh.assembly_item_id                  C_assembly_id,
         rh.source_item_id                    C_source_item_id,
         wl.line_code                         C_line_code,
         bd.department_code                   C_department_code,
         br.resource_code                     C_resource_code,
         rh.setback_days                      C_setback_days,
         rh.assembly_usage                    C_assembly_usage,
         rh.operation_seq_num                 C_op_seq_num,
         rh.resource_department_hours         C_required_hours,
         lu1.meaning                          C_basis_meaning,
         rh.resource_units                    C_resource_units,
         lu2.meaning                          C_origination_meaning,
         (rh.resource_department_hours * 
            DECODE ( rh.basis, 1, 
				 rh.assembly_usage, 1))       C_total_hours,
         substr(dr.resource_group_name,1,12)  C_resource_use, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'INV', 'MSTK', 101, sys.ORGANIZATION_ID, sys.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD
FROM     mtl_system_items         sys,
         crp_resource_hours       rh,
         wip_lines                wl,
         bom_departments          bd,
         bom_resources            br,
         mfg_lookups              lu1,
         mfg_lookups              lu2,
         bom_department_resources dr
WHERE    rh.organization_id       = :P_ORG_ID 
AND      rh.organization_id       = sys.organization_id
AND      rh.source_item_id        = sys.inventory_item_id
AND      rh.line_id               = wl.line_id(+)
AND      rh.organization_id       = wl.organization_id(+)
AND      rh.department_id         = bd.department_id(+)
AND      rh.organization_id       = bd.organization_id(+)
AND      rh.resource_id           = br.resource_id(+)
AND      rh.organization_id       = br.organization_id(+)
AND      rh.basis                 = lu1.lookup_code(+)
AND      'CST_BASIS'              = lu1.lookup_type(+)
AND      rh.origination_type      = lu2.lookup_code
AND      'CRP_ROLLUP_ORIGINATION' = lu2.lookup_type
AND      rh.department_id         = dr.department_id(+)
AND      rh.resource_id           = dr.resource_id(+)
         &C_RSRC_USE_WHERE 
 and rh.bill_of_resources=:C_bill 
 and rh.assembly_item_id=:C_inventory_id 
 and rh.organization_id=:C_org_id
ORDER BY rh.source_item_id,
         rh.operation_seq_num,
         rh.line_id,
         rh.department_id,
         rh.resource_id,
		 rh.assembly_usage,
		 rh.resource_department_hours