select
ood.organization_name,
ood.organization_code,
msiv.concatenated_segments item,
-- validity_rule
grvr.recipe_id vrule_recipe_id,
grvr.recipe_validity_rule_id vrule_validity_rule_id,
grvr.revision vrule_revision,
(select haouv.name from hr_all_organization_units_vl haouv where haouv.organization_id = grvr.organization_id) vrule_organization,
grvr.orgn_code vrule_orgn_code,
grvr.delete_mark vrule_delete_mark,
grvr.validity_rule_status vrule_status,
gs.status_type vrule_status_type,
grvr.recipe_use vrule_recipe_use,
grvr.preference vrule_preference,
grvr.start_date vrule_start_date,
grvr.end_date vrule_end_date,
grvr.std_qty vrule_std_qty,
grvr.detail_uom vrule_detail_uom,
grvr.item_um vrule_item_um,
grvr.min_qty vrule_min_qty,
grvr.max_qty vrule_max_qty,
grvr.inv_min_qty vrule_inv_min_qty,
grvr.inv_max_qty vrule_inv_max_qty,
grvr.text_code vrule_text_code,
grvr.lab_type vrule_lab_type,
grvr.planned_process_loss vrule_planned_process_loss,
grvr.item_id vrule_item_id,
grvr.fixed_process_loss vrule_fixed_process_loss,
grvr.fixed_process_loss_uom vrule_fixed_process_loss_uom,
grvr.inventory_item_id vrule_inv_item_id,
-- recipe
gr.recipe_id,
gr.recipe_no,
gr.recipe_description,
gr.recipe_version,
gr.recipe_type,
gr.recipe_status,
gr.delete_mark recipe_delete_mark,
(select haouv.name from hr_all_organization_units_vl haouv where haouv.organization_id = gr.owner_organization_id) recipe_owner_organization,
(select haouv.name from hr_all_organization_units_vl haouv where haouv.organization_id = gr.creation_organization_id) recipe_creation_organization,
gr.planned_process_loss recipe_planned_process_loss,
gr.contiguous_ind recipe_contiguous_ind,
gr.text_code recipe_text_code,
xxen_util.user_name(gr.owner_id) recipe_owner,
gr.owner_lab_type recipe_owner_lab_type,
gr.calculate_step_quantity recipe_calculate_step_quantity,
gr.enhanced_pi_ind recipe_enhanced_pi_ind,
gr.master_recipe_id recipe_master_recipe_id,
gr.fixed_process_loss recipe_fixed_process_loss,
gr.fixed_process_loss_uom recipe_fixed_process_loss_uom,
gr.recipe_yield recipe_recipe_yield,
gr.formula_id recipe_formula_id,
gr.routing_id recipe_routing_id,
gr.project_id recipe_project_id,
-- formula
ffm.formula_id,
ffm.formula_no,
ffm.formula_vers,
ffm.formula_type,
ffm.formula_status,
ffm.delete_mark formula_delete_mark,
ffm.in_use formula_in_use,
ffm.inactive_ind formula_inactive_ind,
(select haouv.name from hr_all_organization_units_vl haouv where haouv.organization_id = ffm.owner_organization_id) formula_owner_organization,
ffm.formula_desc1,
ffm.formula_desc2,
ffm.scale_type formula_scale_type,
ffm.formula_class,
ffm.fmcontrol_class formula_fmcontrol_class,
ffm.text_code formula_text_code,
xxen_util.user_name(ffm.owner_id) formula_owner,
ffm.total_input_qty formula_total_input_qty,
ffm.total_output_qty formula_total_output_qty,
ffm.yield_uom formula_yield_uom,
ffm.master_formula_id formula_master_formula_id,
ffm.auto_product_calc formula_auto_product_calc,
-- formula line
fmd.formulaline_id,
fmd.line_no formulaline_line_no,
fmd.line_type formulaline_line_type_code,
decode(fmd.line_type,-1,'Ingredient',1,'Product',2,'By-product','Other') formula_line_type,
fmd.revision formulaline_revision,
(select haouv.name from hr_all_organization_units_vl haouv where haouv.organization_id = fmd.organization_id) formulaline_organization,
msiv1.concatenated_segments formulaline_item,
msiv1.primary_uom_code formulaline_item_primary_uom,
fmd.detail_uom formulaline_detail_uom,
fmd.qty formulaline_qty,
fmd.item_um formulaline_item_um,
case when fmd.detail_uom = msiv1.primary_uom_code
then
1
else
inv_convert.inv_um_convert
( item_id => fmd.inventory_item_id
, precision => 5
, from_quantity => fmd.qty
, from_unit => fmd.detail_uom
, to_unit => msiv1.primary_uom_code
, from_name => null
, to_name => null
)
end formulaline_inv_uom_conv,
fmd.release_type formulaline_release_type,
fmd.scrap_factor formulaline_scrap_factor,
fmd.scale_type formulaline_scale_type,
fmd.cost_alloc formulaline_cost_alloc,
fmd.phantom_type formulaline_phantom_type,
fmd.rework_type formulaline_rework_type,
fmd.text_code formulaline_text_code,
fmd.tpformula_id formulaline_tpformula_id,
fmd.iaformula_id formulaline_iaformula_id,
fmd.scale_uom formulaline_scale_uom,
fmd.contribute_step_qty_ind formulaline_contrib_st_qty_ind,
fmd.contribute_yield_ind formulaline_contrib_yield_ind,
fmd.scale_multiple formulaline_scale_multiple,
fmd.scale_rounding_variance formulaline_scale_round_var,
fmd.rounding_direction formulaline_round_direction,
fmd.by_product_type formulaline_by_product_type,
fmd.buffer_ind formulaline_buffer_ind,
fmd.ingredient_end_date formulaline_ingred_end_date,
fmd.prod_percent formulaline_prod_percent,
fmd.item_id formulaline_item_id,
fmd.inventory_item_id formulaline_inv_item_id,
--
sys_context('m_fs_context','pc_ind') security_context
from
org_organization_definitions ood,
mtl_system_items_vl msiv,
gmd_recipe_validity_rules grvr,
gmd_status gs,
gmd_recipes gr,
fm_form_mst ffm,
fm_matl_dtl fmd,
mtl_system_items_vl msiv1
where
1=1 and
ood.organization_id = msiv.organization_id and
msiv.inventory_item_id = grvr.inventory_item_id and
msiv.organization_id = nvl(grvr.organization_id,msiv.organization_id) and
grvr.validity_rule_status = gs.status_code (+) and
grvr.recipe_id = gr.recipe_id (+) and
gr.formula_id = ffm.formula_id (+) and
ffm.formula_id = fmd.formula_id (+) and
fmd.inventory_item_id = msiv1.inventory_item_id (+) and
fmd.organization_id = msiv1.organization_id (+)
order by
ood.organization_name,
msiv.concatenated_segments,
--
--grvr.orgn_code asc,
decode(gs.status_type,900,699,gs.status_type) desc,
grvr.preference,
grvr.start_date,
--
ffm.formula_no,
decode(fmd.line_type,1,1,2,3,3),
fmd.line_no |
