[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* These variables store the MTQ related values that is last inserted. */
g_old_formula_id PLS_INTEGER ; /* B3970993 */
TYPE last_update_date IS TABLE OF msc_st_boms.last_update_date%TYPE INDEX BY BINARY_INTEGER;
bom_last_update_date last_update_date ;
bomc_last_update_date last_update_date ;
pef_last_update_date last_update_date ;
rtg_last_update_date last_update_date ;
or_last_update_date last_update_date ;
opr_last_update_date last_update_date ;
opr_stpdep_last_update_date last_update_date ; /* 7363807 */
itm_mtq_last_update_date last_update_date ; /* 7363807 */
rs_last_update_date last_update_date ;
oc_last_update_date last_update_date ;
effective validity rules. this will avoid insert over DB link for each effectivity */
-- Bug: 9572568 Vpedrla Made changes to the procedure call populate_eff ;
/* B2989806 Added inline tables and outer joins to select aps_fmeff_id */
/* NAMIT UOM Changes */
/* bug: 6710684 Vpedarla making changes to take the profile value from source server
and also made changes to use procedure get_profile_value */
-- commented the below code line
-- l_gmp_um_code := fnd_profile.VALUE('BOM:HOUR_UOM_CODE');
uom_code_cursor := ' select uom_class '
||' from mtl_units_of_measure'||at_apps_link
||' where uom_code = :gmp_um_code ';
' SELECT eff.recipe_validity_rule_id, '
||' nvl(gfe.aps_fmeff_id,-1),eff.inventory_item_id, '
||' eff.formula_id,eff.organization_id, '
||' eff.start_date, eff.end_date, eff.inv_min_qty, '
||' eff.inv_max_qty, eff.preference, eff.primary_uom_code, '
||' eff.wcode, eff.routing_id, '
||' eff.routing_no, eff.routing_vers, eff.routing_desc, '
||' eff.routing_uom, eff.routing_qty, '
||' eff.prd_fct , eff.prd_ind, '
||' eff.recipe_id, eff.recipe_no, eff.recipe_version, eff.rhdr_loc, '
/* NAMIT_CR Get Calculate Step Dependency Checkbox*/
/* SGIDUGU - added Category id and Setup Id */
||' decode(eff.calculate_step_quantity,0,2,1) calculate_step_quantity, '
||' scat.category_id, NULL, '
||' scat.category_concat_segs '
||'FROM ( '
||' SELECT /*+ ORDERED USE_NL(gmd_recipe_validity_rules,fm_form_mst,mtl_system_items)*/ ffe.recipe_validity_rule_id, ffe.inventory_item_id, '
||' grb.formula_id, ffe.organization_id, '
||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
||' ffe.inv_max_qty, ffe.preference, msi.primary_uom_code, '
||' mp.organization_code wcode , grb.routing_id, '
||' frh.routing_no, frh.routing_vers, frh.routing_desc, '
||' frh.routing_uom, frh.routing_qty, ' /*B2870041*/
||' DECODE(frh.routing_uom,msi.primary_uom_code ,1, '
||' inv_convert.inv_um_convert'||at_apps_link
||' ( ffe.inventory_item_id, '
||' NULL, '
||' ffe.organization_id, '
/* bug: 6918852 Vpedarla 04-Apr-2008 used the global variable for precision*/
/* ||' NULL, ' */
|| conv_precision || ' , '
||' 1, '
||' msi.primary_uom_code , ' /* primary */
||' frh.routing_uom , ' /* routing um */
||' NULL , '
||' NULL '
||' ) '
||' ) prd_fct, -1 prd_ind, '
||' grb.recipe_id, grb.recipe_no, grb.recipe_version , '
||' 0 rhdr_loc, '
/* NAMIT_CR, SGIDUGU - Seq dep Id */
||' grb.calculate_step_quantity '
||' FROM gmd_recipes_b'||at_apps_link||' grb,'
||' gmd_recipe_validity_rules'||at_apps_link||' ffe,'
||' fm_form_mst'||at_apps_link||' ffm,'
||' fm_rout_hdr'||at_apps_link||' frh,'
||' mtl_parameters'||at_apps_link||' mp,'
||' mtl_system_items'||at_apps_link||' msi,'
||' hr_organization_units'||at_apps_link||' hou,'
||' gmd_status_b'||at_apps_link||' gs1,'
||' gmd_status_b'||at_apps_link||' gs2,'
||' gmd_status_b'||at_apps_link||' gs3,'
||' gmd_status_b'||at_apps_link||' gs4 '
||' WHERE grb.delete_mark = 0 '
||' AND grb.recipe_id = ffe.recipe_id '
||' AND grb.recipe_status = gs1.status_code '
||' AND gs1.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs1.delete_mark = 0 '
||' AND ffe.delete_mark = 0 '
||' AND ffe.validity_rule_status = gs2.status_code '
||' AND gs2.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs2.delete_mark = 0 '
||' AND frh.delete_mark = 0 '
||' AND ffm.delete_mark = 0 '
||' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '
||' AND hou.organization_id = mp.organization_id '
||' AND frh.inactive_ind = 0 '
||' AND ffm.inactive_ind = 0 '
||' AND grb.routing_id IS NOT NULL '
||' AND ffe.organization_id IS NOT NULL '
||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
||' AND ffe.organization_id = mp.organization_id '
||' AND grb.formula_id = ffm.formula_id '
||' AND ffm.formula_status = gs3.status_code '
||' AND gs3.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs3.delete_mark = 0 '
||' AND grb.routing_id = frh.routing_id '
||' AND frh.routing_status = gs4.status_code '
||' AND gs4.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs4.delete_mark = 0 '
||' AND msi.organization_id = ffe.organization_id '
||' AND msi.inventory_item_id = ffe.inventory_item_id '
||' AND msi.recipe_enabled_flag = ''Y'' '
||' AND msi.process_execution_enabled_flag = ''Y'' '
||' AND mp.process_enabled_flag = ''Y'' '
/*B5161655 - Changed the where clause to pick up even when the formula belongs to a differnt organization
from the validity rules */
||' AND EXISTS ( SELECT /*+DRIVING_SITE(FM_MATL_DTL)*/ 1 '
||' FROM fm_matl_dtl'||at_apps_link||' '
||' WHERE formula_id = grb.formula_id '
||' AND line_type = 1 '
||' AND inventory_item_id = msi.inventory_item_id '
||' AND msi.organization_id = ffe.organization_id '
||' AND inventory_item_id = ffe.inventory_item_id ) '
||' UNION ALL '
||' SELECT /*+ ORDERED USE_NL(gmd_recipe_validity_rules,fm_form_mst,mtl_system_items)*/ ffe.recipe_validity_rule_id, ffe.inventory_item_id, '
||' grb.formula_id, ffe.organization_id, '
||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
||' ffe.inv_max_qty, ffe.preference, msi.primary_uom_code, '
||' mp.organization_code wcode , to_number(null) , '
||' NULL, to_number(null), NULL, '
||' NULL, to_number(null), to_number(null) prd_fct, -1 prd_ind, '
||' grb.recipe_id, grb.recipe_no, grb.recipe_version , '
||' 0 rhdr_loc, '
/* NAMIT_CR,SGIDUGU */
||' 0 calculate_step_quantity '
||' FROM gmd_recipes_b'||at_apps_link||' grb,'
||' gmd_recipe_validity_rules'||at_apps_link||' ffe,'
||' fm_form_mst'||at_apps_link||' ffm, '
||' mtl_parameters'||at_apps_link||' mp, '
||' mtl_system_items'||at_apps_link||' msi, '
||' hr_organization_units'||at_apps_link||' hou,'
||' gmd_status_b'||at_apps_link||' gs1,'
||' gmd_status_b'||at_apps_link||' gs2,'
||' gmd_status_b'||at_apps_link||' gs3 '
||' WHERE grb.delete_mark = 0 '
||' AND grb.recipe_id = ffe.recipe_id '
||' AND grb.recipe_status = gs1.status_code '
||' AND gs1.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs1.delete_mark = 0 '
||' AND ffe.delete_mark = 0 '
||' AND ffe.validity_rule_status = gs2.status_code '
||' AND gs2.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs2.delete_mark = 0 '
||' AND ffm.delete_mark = 0 '
||' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '
||' AND hou.organization_id = mp.organization_id '
||' AND ffm.inactive_ind = 0 '
||' AND grb.routing_id IS NULL '
||' AND ffe.organization_id IS NOT NULL '
||' AND ffe.organization_id = mp.organization_id '
||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
||' AND grb.formula_id = ffm.formula_id '
||' AND ffm.formula_status = gs3.status_code '
||' AND gs3.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs3.delete_mark = 0 '
||' AND msi.organization_id = ffe.organization_id '
||' AND msi.inventory_item_id = ffe.inventory_item_id '
||' AND msi.recipe_enabled_flag = ''Y'' '
||' AND msi.process_execution_enabled_flag = ''Y'' '
||' AND mp.process_enabled_flag = ''Y'' '
/*B5161655 - Changed the where clause to pick up even when the formula belongs to a differnt organization
from the validity rules */
||' AND EXISTS ( SELECT /*+DRIVING_SITE(FM_MATL_DTL)*/ 1 '
||' FROM fm_matl_dtl'||at_apps_link||' '
||' WHERE formula_id = grb.formula_id '
||' AND line_type = 1 '
||' AND inventory_item_id = msi.inventory_item_id '
||' AND msi.organization_id = ffe.organization_id '
||' AND inventory_item_id = ffe.inventory_item_id ) '
||' UNION ALL '
||' SELECT /*+ ORDERED USE_NL(gmd_recipe_validity_rules,fm_form_mst,mtl_system_items)*/ ffe.recipe_validity_rule_id, ffe.inventory_item_id, '
||' grb.formula_id, msi.organization_id, '
||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
||' ffe.inv_max_qty, ffe.preference, msi.primary_uom_code, '
||' mp.organization_code wcode , grb.routing_id, '
||' frh.routing_no, frh.routing_vers, frh.routing_desc, '
||' frh.routing_uom, frh.routing_qty,' /*B2870041*/
||' DECODE(frh.routing_uom,msi.primary_uom_code ,1, '
||' inv_convert.inv_um_convert'||at_apps_link
||' (ffe.inventory_item_id, '
||' NULL, '
||' msi.organization_id, '
/* bug: 6918852 Vpedarla 04-Apr-2008 used the global variable for precision*/
/* ||' NULL, ' */
|| conv_precision || ' , '
||' 1, '
||' msi.primary_uom_code , ' /* primary */
||' frh.routing_uom , ' /* routing um */
||' NULL , '
||' NULL '
||' ) '
||' ) prd_fct, -1 prd_ind, '
||' grb.recipe_id, grb.recipe_no, grb.recipe_version ,'
||' 0 rhdr_loc, '
/* NAMIT_CR,SGIDUGU */
||' grb.calculate_step_quantity '
||' FROM gmd_recipes_b'||at_apps_link||' grb,'
||' gmd_recipe_validity_rules'||at_apps_link||' ffe,'
||' fm_form_mst'||at_apps_link||' ffm,'
||' fm_rout_hdr'||at_apps_link||' frh,'
||' mtl_parameters'||at_apps_link||' mp,'
||' mtl_system_items'||at_apps_link||' msi,'
||' hr_organization_units'||at_apps_link||' hou,'
||' gmd_status_b'||at_apps_link||' gs1,'
||' gmd_status_b'||at_apps_link||' gs2,'
||' gmd_status_b'||at_apps_link||' gs3,'
||' gmd_status_b'||at_apps_link||' gs4 '
||' WHERE grb.delete_mark = 0 '
||' AND grb.recipe_id = ffe.recipe_id '
||' AND grb.recipe_status = gs1.status_code '
||' AND gs1.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs1.delete_mark = 0 '
||' AND ffe.delete_mark = 0 '
||' AND ffe.validity_rule_status = gs2.status_code '
||' AND gs2.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs2.delete_mark = 0 '
||' AND frh.delete_mark = 0 '
||' AND ffm.delete_mark = 0 '
||' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '
||' AND hou.organization_id = mp.organization_id '
||' AND frh.inactive_ind = 0 '
||' AND ffm.inactive_ind = 0 '
||' AND grb.routing_id IS NOT NULL '
||' AND ffe.organization_id IS NULL '
||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
||' AND grb.formula_id = ffm.formula_id '
||' AND ffm.formula_status = gs3.status_code '
||' AND gs3.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs3.delete_mark = 0 '
||' AND grb.routing_id = frh.routing_id '
||' AND frh.routing_status = gs4.status_code '
||' AND gs4.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs4.delete_mark = 0 '
||' AND mp.organization_id = msi.organization_id '
||' AND mp.process_enabled_flag = ''Y'' '
||' AND msi.inventory_item_id = ffe.inventory_item_id '
||' AND msi.recipe_enabled_flag = ''Y'' '
||' AND msi.process_execution_enabled_flag = ''Y'' '
/*B5161655 - Changed the where clause to pick up even when the formula belongs to a differnt organization
from the validity rules */
||' AND EXISTS ( SELECT /*+DRIVING_SITE(FM_MATL_DTL)*/ 1 '
||' FROM fm_matl_dtl'||at_apps_link||' '
||' WHERE formula_id = grb.formula_id '
||' AND line_type = 1 '
||' AND inventory_item_id = msi.inventory_item_id '
||' AND msi.organization_id = nvl(ffe.organization_id,msi.organization_id) '
||' AND inventory_item_id = ffe.inventory_item_id ) '
||' UNION ALL '
||' SELECT /*+ ORDERED USE_NL(gmd_recipe_validity_rules,fm_form_mst,mtl_system_items)*/ ffe.recipe_validity_rule_id, ffe.inventory_item_id, '
||' grb.formula_id, msi.organization_id, '
||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
||' ffe.inv_max_qty, ffe.preference, msi.primary_uom_code, '
||' mp.organization_code wcode , to_number(null) , '
||' NULL, to_number(null), NULL, '
||' NULL, to_number(null), to_number(null) prd_fct, -1 prd_ind, ' /*B2870041*/
||' grb.recipe_id, grb.recipe_no, grb.recipe_version ,'
||' 0 rhdr_loc, '
/* NAMIT_CR,SGIDUGU */
||' 0 calculate_step_quantity '
||' FROM gmd_recipes_b'||at_apps_link||' grb,'
||' gmd_recipe_validity_rules'||at_apps_link||' ffe,'
||' mtl_parameters'||at_apps_link||' mp, '
||' fm_form_mst'||at_apps_link||' ffm, '
||' mtl_system_items'||at_apps_link||' msi,'
||' hr_organization_units'||at_apps_link||' hou,'
||' gmd_status_b'||at_apps_link||' gs1,'
||' gmd_status_b'||at_apps_link||' gs2,'
||' gmd_status_b'||at_apps_link||' gs3 '
||' WHERE grb.delete_mark = 0 '
||' AND grb.recipe_id = ffe.recipe_id '
||' AND grb.recipe_status = gs1.status_code '
||' AND gs1.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs1.delete_mark = 0 '
||' AND ffe.delete_mark = 0 '
||' AND ffe.validity_rule_status = gs2.status_code '
||' AND gs2.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs2.delete_mark = 0 '
||' AND ffm.delete_mark = 0 '
||' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '
||' AND hou.organization_id = mp.organization_id '
||' AND ffm.inactive_ind = 0 '
||' AND grb.routing_id IS NULL '
||' AND ffe.organization_id IS NULL '
||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
||' AND grb.formula_id = ffm.formula_id '
||' AND ffm.formula_status = gs3.status_code '
||' AND gs3.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs3.delete_mark = 0 '
||' AND msi.organization_id = mp.organization_id '
||' AND mp.process_enabled_flag = ''Y'' ' ;
||' AND EXISTS ( SELECT /*+DRIVING_SITE(FM_MATL_DTL)*/ 1 '
||' FROM fm_matl_dtl'||at_apps_link||' '
||' WHERE formula_id = grb.formula_id '
||' AND line_type = 1 '
||' AND inventory_item_id = msi.inventory_item_id '
||' AND msi.organization_id = nvl(ffe.organization_id,msi.organization_id) '
||' AND inventory_item_id = ffe.inventory_item_id ) ) eff,'
||'( SELECT /*+DRIVING_SITE(gmp_form_eff)*/ organization_id, fmeff_id, '
||' max(aps_fmeff_id) aps_fmeff_id '
||' FROM gmp_form_eff'||at_apps_link||' '
||' WHERE organization_id is NOT NULL '
||' GROUP BY organization_id, fmeff_id '
||' ) gfe, '
-- B4918786 (RDP) SDS Changes
||' (SELECT /*+ DRIVING_SITE(mic) DRIVING_SITE(cat) */ mic.category_concat_segs, '
||' mic.category_id, '
||' mic.organization_id,'
||' mic.inventory_item_id '
||' FROM mtl_item_categories_v'||at_apps_link|| ' mic, '
||' mtl_default_category_sets_fk_v'||at_apps_link|| ' cat '
||' WHERE mic.category_set_id = cat.category_set_id '
||' AND cat.functional_area_id = 14 '
||' ) scat '
||'WHERE eff.organization_id = gfe.organization_id (+) '
||' AND (eff.organization_id IS NULL OR eff.organization_id ' || l_in_str_org ||')'
---#6358324 KBANDDYO restricting the collections from collecting unwanted inv_organizations
||' AND eff.recipe_validity_rule_id = gfe.fmeff_id (+) '
||' AND eff.inventory_item_id = scat.inventory_item_id (+) '
||' AND eff.organization_id = scat.organization_id (+)'
||' ORDER BY 4,5 ' ;
' SELECT unique ffm.formula_id, 0, 0, 0, -1, NULL '
||' FROM fm_form_mst'||at_apps_link||' ffm, '
||' gmd_recipes_b'||at_apps_link||' grb, '
||' gmd_recipe_validity_rules'||at_apps_link||' ffe, '
||' hr_organization_units'||at_apps_link||' hou, '
||' gmd_status_b'||at_apps_link||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id '
||' AND ffe.validity_rule_status = gs.status_code '
||' AND ( ffe.organization_id is NULL or ffe.organization_id = hou.organization_id)'
----B#6358324 KBANDDYO restricting the collections from collecting unwanted inv_organizations
----B#6489338 Added the next 1 conditions as below
||' AND hou.organization_id '|| l_in_str_org
||' AND gs.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs.delete_mark = 0 '
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
||' AND ffm.formula_id = grb.formula_id '
||' AND ffe.delete_mark = 0 '
||' AND ffm.delete_mark = 0 '
||' ORDER BY formula_id ' ;
v_gmd_seq := 'SELECT MAX(formulaline_id) FROM fm_matl_dtl'||at_apps_link ;
V_process_Org := ' SELECT count(*) from mtl_parameters'||At_Apps_Link
||' WHERE organization_id' || L_In_Str_Org
||' AND process_enabled_flag = ''Y'' ';
' SELECT ffm.formula_id, '
||' ffm.formula_no, '
||' ffm.formula_vers, '
||' ffm.formula_desc1, '
||' ((fmd.formulaline_id * 2) + 1) x_formulaline_id, '
||' fmd.line_type, '
||' fmd.item_id inventory_item_id, '
||' decode(fmd.original_item_flag,1,fmd.qty,(( fmd.sub_replace_qty / fmd.sub_original_qty) * fmd.line_item_qty)) formula_qty, '
/*B5176291 - for substitute items fmd.qty will be null, in those case subsittute qty should be used*/
||' fmd.scrap_factor , '
||' fmd.scale_type, '
||' fmd.contribute_yield_ind, '
||' decode(fmd.line_type, -1, decode(nvl(fmd.contribute_step_qty_ind, '''||'N'||''''||'),' -- venu
|| ''''||'Y'||''''||',1,2), 1) contribute_step_qty_ind,' -- venu
||' DECODE(fmd.phantom_type,0,null,6) phantom_type, '
||' msi.primary_uom_code, ' -- venu
||' fmd.item_um detail_uom, ' -- venu
-- Bug: 7348022 Vpedarla changed below line of code
||' DECODE(fmd.scale_type,2,4,fmd.scale_type) bom_scale_type, '
-- ||' DECODE(fmd.scale_type,0,0,1,2) bom_scale_type, '
||' DECODE(fmd.item_um,msi.primary_uom_code,decode(fmd.original_item_flag,1,fmd.qty,((fmd.sub_replace_qty / fmd.sub_original_qty) * fmd.line_item_qty)), '
||' inv_convert.inv_um_convert'||at_apps_link
||' ( fmd.item_id, '
||' NULL,msi.organization_id, '
/* bug: 6918852 Vpedarla 04-Apr-2008 used the global variable for precision*/
/* ||' NULL, ' */
|| conv_precision || ' , '
||' decode(fmd.original_item_flag,1,fmd.qty,((fmd.sub_replace_qty / fmd.sub_original_qty) * fmd.line_item_qty)), '
||' fmd.item_um , '
||' msi.primary_uom_code , '
||' NULL ,NULL )) primary_qty, '
||' DECODE(fmd.item_um,msi.primary_uom_code, decode(fmd.original_item_flag,1,fmd.scale_multiple,((fmd.sub_replace_qty / fmd.sub_original_qty) * fmd.line_item_qty)), '
||' inv_convert.inv_um_convert'||at_apps_link
||' ( fmd.item_id, '
||' NULL,msi.organization_id, '
|| conv_precision || ' , '
||' decode(fmd.original_item_flag,1,fmd.scale_multiple,((fmd.sub_replace_qty / fmd.sub_original_qty) * fmd.line_item_qty)), '
||' fmd.item_um , '
||' msi.primary_uom_code , '
||' NULL ,NULL )) scale_multiple, '
/* ||' fmd.scale_multiple, ' */ /* Bug 8529867 Vpedarla */
||' (fmd.scale_rounding_variance * 100) scale_rounding_variance, ' -- venu multipied it by 100
||' decode(fmd.rounding_direction,1,2,2,1,fmd.rounding_direction) ,'
||' fmd.release_type, '
||' fmd.original_item_flag, '
||' fmd.start_date, '
||' fmd.end_date, '
||' fmd.formulaline_id formula_line_id , '
||' fmd.preference '
-- ||' null actual_end_date ,'
-- ||' 0 actual_end_flag '
||' FROM gmd_material_effectivities_vw'||at_apps_link||' fmd,'
||' fm_form_mst'||at_apps_link||' ffm, '
||' mtl_system_items'||at_apps_link||' msi '
||' WHERE msi.inventory_item_id = fmd.item_id '
||' AND msi.organization_id = fmd.organization_id '
||' AND ffm.formula_id = fmd.formula_id '
||' AND ffm.formula_id IN ( select /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(gs) DRIVING_SITE(hou) */ unique grb.formula_id '
-- #6358324 KBANDDYO restricting the collections from collecting unwanted inv_organizations
||' FROM gmd_recipes_b'||at_apps_link ||' grb, '
||' gmd_recipe_validity_rules'||at_apps_link ||' ffe, '
||' hr_organization_units'||at_apps_link ||' hou, '
||' gmd_status_b'||at_apps_link ||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id '
----B#6489338 Added the next 3 conditions as below and commented the above clause
||' AND ( ffe.organization_id is NULL or ffe.organization_id = hou.organization_id )'
||' AND hou.organization_id '|| l_in_str_org
||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
||' AND ffe.validity_rule_status = gs.status_code '
||' AND gs.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ')'
||' AND gs.delete_mark = 0 '
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
||' AND ffe.delete_mark = 0 )'
||' AND ffm.delete_mark = 0 '
----B#6489338 Added the next condition as below
--||' AND msi.organization_id '|| l_in_str_org --vkinduri, Bug # 14837859
||' AND nvl(fmd.qty,fmd.sub_replace_qty) <> 0'
||' AND ( fmd.qty <> 0 OR (( fmd.sub_replace_qty / fmd.sub_original_qty) * fmd.line_item_qty) <> 0) '
||' ORDER BY ffm.formula_id ,fmd.line_type, fmd.formulaline_id, ' /* PennColor 13Feb fmd.line_no, vpedarla Bug: 7652265 */
||' fmd.original_item_flag desc,fmd.start_date,fmd.preference ';
/* insert processed substitutes now */
FOR k in 1..substcount
LOOP
formula_details_size := formula_details_size + 1 ;
/*insert trailing records if there is no substitue which has a null end date*/
enddatenull := FALSE; -- Bug: 6030499 Vpedarla forward port of 11.5.9 bug 6047372.
/* This is to insert original item */
IF nvl(temp_detail_tab(1).end_date,(SYSDATE +1)) > sysdate THEN
formula_details_size := formula_details_size + 1 ;
/* comparing with prevoious record to check if there is any gap so that we insert
the original item record in the gap. */
IF ( temp_detail_tab(1).start_date > orig_start_date ) and NOT(nullenddatefound) THEN
/* store the previous record' end date */
substcount := substcount + 1 ;
rtg_offset_cur_stmt := ' SELECT '||
' gro.organization_id, '||
' gro.fmeff_id, '||
' gro.formula_id, '||
' gro.routingstep_id, '||
' gro.start_offset, '||
' gro.end_offset, '||
' (rsm.formulaline_id *2 )+ 1'||
' FROM '||
' gmd_recipe_step_materials'||at_apps_link||' rsm, '||
' gmp_routing_offsets'||at_apps_link||' gro '||
' WHERE '||
' gro.recipe_id = rsm.recipe_id '||
' AND gro.routingstep_id = rsm.routingstep_id '||
' ORDER BY gro.formula_id,gro.organization_id, rsm.formulaline_id ' ;
oper_lead_time_cur_stmt := ' SELECT '||
' gro.organization_id, '||
' gro.fmeff_id, '||
' gro.formula_id, '||
' gro.routing_id, '||
' gro.routingstep_id, '||
' gro.start_offset, '||
' gro.end_offset '||
' FROM gmp_routing_offsets'||at_apps_link||' gro '||
' ORDER BY gro.fmeff_id, gro.organization_id , gro.routingstep_id ' ;
' SELECT unique frh.routing_id, mp.organization_id, '
/* NAMIT_CR 2 more zeros added for Linking Step Dependency to Routing Header */
||' 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 '
||' FROM mtl_parameters'||at_apps_link||' mp, '
||' fm_rout_hdr'||at_apps_link||' frh, '
||' gmd_recipes_b'||at_apps_link||' grb, '
||' gmd_recipe_validity_rules'||at_apps_link||' ffe, '
||' hr_organization_units'||at_apps_link||' hou,'
||' gmd_status_b'||at_apps_link||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id '
||' AND ffe.validity_rule_status = gs.status_code '
||' AND gs.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ',' ||'''400'''|| ') '
||' AND gs.delete_mark = 0 '
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
||' AND frh.routing_id = grb.routing_id '
||' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '
||' AND hou.organization_id = mp.organization_id '
||' AND nvl(ffe.organization_id, mp.organization_id) = mp.organization_id'
||' AND mp.process_enabled_flag = ''Y''' ;
||' AND ffe.delete_mark = 0 '
||' AND frh.delete_mark = 0 '
||' AND frh.inactive_ind = 0 '
||' ORDER BY frh.routing_id, mp.organization_id ' ;
/* 2582849 minimum_transfer_qty selected */
/* Select All the Sequence Dependent Changeover for oprn_id <> -1 */
setup_id_cursor :=
' SELECT oprn_id, '
||' category_id, '
||' seq_dep_id '
||' FROM gmp_sequence_types'||at_apps_link||' gst '
||' WHERE oprn_id <> -1 '
||' ORDER BY oprn_id,category_id ' ;
' SELECT /*+ DRIVING_SITE(crd) DRIVING_SITE(frd) DRIVING_SITE(fom) DRIVING_SITE(goa) DRIVING_SITE(gor) */ frd.routing_id, '
||' crd.organization_id, '
||' frd.routingstep_no, '
||' NVL(goa.sequence_dependent_ind,0), '
/* This will ensure that ordering will always have primary first */
||' DECODE(gor.prim_rsrc_ind, 1,1,2,2,0,3), '
||' gor.resources, '
||' gor.prim_rsrc_ind, '
||' decode(crd.capacity_constraint,1,1,2), '
||' crd.min_capacity, '
||' crd.max_capacity, '
||' crd.schedule_ind, '
||' frd.routingstep_id, '
||' ((frd.routingstep_id * 2) + 1) x_routingstep_id, '
||' frd.step_qty, '
||' NVL(frd.minimum_transfer_qty,0) minimum_transfer_qty, '
||' fom.oprn_desc, '
||' fom.oprn_id, ' /* SGIDUGU Seq Dep */
||' fom.oprn_no, '
||' fom.process_qty_uom,' -- akaruppa previously sou2.uom_code
||' goa.activity, '
||' goa.oprn_line_id, '
||' gor.resource_count, '
||' gor.resource_usage, '
||' gor.resource_usage_uom, ' -- akaruppa previously gor.usage_um
||' gor.scale_type,'
||' goa.offset_interval, '
||' crd.resource_id, '
||' ((crd.resource_id * 2) + 1) x_resource_id, '
||' DECODE(gor.scale_type,0,2,1,1,2,3) , ' /* B2967464 */
||' goa.activity_factor, '
||' gor.process_qty, '
||' NVL(goa.material_ind,0), '
||' 1 , '
||' SUM(NVL(goa.material_ind,0)) OVER (PARTITION BY '
||' frd.routing_id, crd.organization_id, frd.routingstep_no) mat_found,'
||' 1, ' /* flag for including rows */
||' decode(goa.break_ind,NULL,2,0,2,1,1) brk_ind'
||' ,-1, -1, -1, -1, -1, -1, '
-- B4918786 (RDP) SDS
||' (SUM(DECODE(NVL(goa.sequence_dependent_ind,0),1,1,0)) OVER '
||' (PARTITION BY '
||' frd.routing_id, crd.organization_id)) is_sds_rout,'
||' DECODE(NVL(goa.sequence_dependent_ind,0),1,DECODE(gor.prim_rsrc_ind,1,1,0),0) is_unique, '
||' DECODE(NVL(goa.sequence_dependent_ind,0),1,0,DECODE(gor.prim_rsrc_ind,1,1,0)) is_nonunique, '
||' NULL setup_id '
||' FROM cr_rsrc_dtl'||at_apps_link||' crd, '
||' fm_rout_dtl'||at_apps_link||' frd, '
||' gmd_operations'||at_apps_link||' fom, '
||' gmd_operation_activities'||at_apps_link||' goa, '
||' gmd_operation_resources'||at_apps_link||' gor, '
||' hr_organization_units'||at_apps_link||' hou1, '
||' mtl_units_of_measure'||at_apps_link||' mum, '
/*sowmya added - operation process qty should be verified with the uom master*/
||' mtl_units_of_measure'||at_apps_link||' mum2 '
----B#6489338 Added the next where condition as below
||' WHERE frd.routing_id in ( SELECT /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(gs) */ distinct routing_id '
||' FROM gmd_recipes'||at_apps_link ||' grb ,'
||' gmd_recipe_validity_rules'||at_apps_link ||' ffe ,'
||' hr_organization_units'||at_apps_link ||' hou ,'
||' gmd_status_b'||at_apps_link ||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id'
||' AND ffe.validity_rule_status = gs.status_code '
||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
||' AND gs.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ')'
||' AND gs.delete_mark = 0 '
||' AND ( ffe.organization_id is NULL or ffe.organization_id = hou.organization_id )'
||' AND hou.organization_id '|| l_in_str_org
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE )'
||' AND frd.oprn_id = fom.oprn_id '
||' AND fom.oprn_id = goa.oprn_id '
||' AND goa.oprn_line_id = gor.oprn_line_id '
/* NAMIT_RD */
||' AND crd.resources = gor.resources '
----B#6489338 Added the next 2 conditions as below
||' AND hou1.organization_id '|| l_in_str_org
||' AND crd.organization_id = hou1.organization_id'
||' AND mum.uom_code = gor.resource_usage_uom '
||' AND mum2.uom_code = fom.process_qty_uom ' --sowmya added
||' AND fom.delete_mark = 0 '
||' AND goa.activity_factor > 0 '
||' AND mum.uom_class = :gmp_uom_class '
/* NAMIT_RD */
||' ORDER BY '
||' 1, 2, 3, 4, 5, 6 ';
/* since the select includes orgn_code we need to track when the route
org or step changes. If any of them change this means the step has
changed. when there is a new step the process needs to reset. The
new values are saved and the first row of the step is saved to be
used to loop later. found will be used to indicate that an activity
has the material ind set to 1 */
IF old_route <> rtg_org_dtl_tab(ri).routing_id OR
old_orgn_id <> rtg_org_dtl_tab(ri).organization_id OR
old_step <> rtg_org_dtl_tab(ri).routingstep_no THEN
found := 0;
validation_statement := 'SELECT /*+ DRIVING_SITE(frd) DRIVING_SITE(fom) DRIVING_SITE(goa)'
||' DRIVING_SITE(gor) DRIVING_SITE(crm) DRIVING_SITE(mum) */ '
||' frd.routing_id, '
||' frd.routingstep_no, '
||' NVL(goa.sequence_dependent_ind,0), '
||' DECODE(gor.prim_rsrc_ind, 1,1,2,2,0,3), ' /* This will ensure that ordering will
always have primary firsr*/
||' gor.resources, '
||' frd.routingstep_id, '
||' fom.oprn_no, '
||' goa.oprn_line_id, '
||' goa.activity, '
||' gor.prim_rsrc_ind, '
||' goa.offset_interval, '
||' gor.resource_usage_uom, ' -- akaruppa changed sou.uom_code to gor.resource_usage_uom
||' decode(crm.capacity_constraint,1,1,2) ' -- akaruppa added to check if resource is chargeable, used for invalidating the routing if chargeable resource is not defined at the org level
||' FROM fm_rout_dtl'||at_apps_link||' frd, '
||' gmd_operations'||at_apps_link||' fom, '
||' gmd_operation_activities'||at_apps_link||' goa, '
||' gmd_operation_resources'||at_apps_link||' gor, '
||' cr_rsrc_mst'||at_apps_link||' crm, '
||' mtl_units_of_measure'||at_apps_link||' mum '
----B#6489338 Added the next where condition as below
||' WHERE frd.routing_id in ( SELECT /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(gs) */ distinct grb.routing_id '
||' FROM gmd_recipes'||at_apps_link ||' grb ,'
||' gmd_recipe_validity_rules'||at_apps_link ||' ffe ,'
||' hr_organization_units'||at_apps_link ||' hou ,'
||' gmd_status_b'||at_apps_link ||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id'
||' AND ffe.validity_rule_status = gs.status_code '
||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
||' AND gs.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ')'
||' AND gs.delete_mark = 0 '
||' AND ( ffe.organization_id is NULL or ffe.organization_id = hou.organization_id )'
||' AND hou.organization_id '|| l_in_str_org
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE )'
||' AND frd.oprn_id = fom.oprn_id '
||' AND fom.oprn_id = goa.oprn_id '
||' AND gor.resources = crm.resources '
||' AND fom.delete_mark = 0'
||' AND goa.oprn_line_id = gor.oprn_line_id '
||' AND mum.uom_code = gor.resource_usage_uom '
||' AND mum.uom_class = :gmp_uom_class '
||' ORDER BY 1, 2, 3, 4, 5 ' ;
recipe_orgn_statement := ' SELECT /*+ DRIVING_SITE(grb) DRIVING_SITE(gs) */ '
||' grb.routing_id, gc.organization_id, '
||' gc.routingstep_id, gc.oprn_line_id, gc.recipe_id, '
||' gc.activity_factor, '
||' gc.resources, gc.resource_usage, gc.process_qty, '
||' gc.min_capacity, gc.max_capacity '
||' FROM gmd_recipes'||at_apps_link||' grb, '
||' gmd_status_b'||at_apps_link||' gs, ' /* B5114783*/
||' ( '
||' SELECT /*+ DRIVING_SITE(goa) DRIVING_SITE(gor) */ '
||' gor.recipe_id, '
||' gor.organization_id, '
||' gor.oprn_line_id, '
||' gor.routingstep_id, '
||' goa.activity_factor, '
||' gor.resources, '
||' gor.resource_usage , '
||' gor.process_qty, '
||' gor.min_capacity, '
||' gor.max_capacity '
||' FROM gmd_recipe_orgn_activities'||at_apps_link||' goa, '
||' gmd_recipe_orgn_resources'||at_apps_link||' gor '
||' WHERE gor.recipe_id = goa.recipe_id '
||' AND gor.organization_id = goa.organization_id '
||' AND gor.oprn_line_id = goa.oprn_line_id '
||' AND gor.routingstep_id = goa.routingstep_id '
||' UNION ALL '
||' SELECT /*+ DRIVING_SITE(goa) */ goa.recipe_id, '
||' goa.organization_id, '
||' goa.oprn_line_id, '
||' goa.routingstep_id, '
||' goa.activity_factor, '
||' NULL resources, '
||' -1 resource_usage, '
||' -1 process_qty, '
||' -1 min_capacity, '
||' -1 max_capacity '
||' FROM gmd_recipe_orgn_activities'||at_apps_link||' goa '
||' WHERE NOT EXISTS( SELECT /*+ DRIVING_SITE(gor) */ 1 '
||' FROM gmd_recipe_orgn_resources'||at_apps_link||' gor '
||' WHERE gor.recipe_id = goa.recipe_id '
||' AND gor.organization_id = goa.organization_id '
||' AND gor.oprn_line_id = goa.oprn_line_id '
||' AND gor.routingstep_id = goa.routingstep_id ) '
||' UNION ALL '
||' SELECT /*+ DRIVING_SITE(gor) */ gor.recipe_id, '
||' gor.organization_id, '
||' gor.oprn_line_id, '
||' gor.routingstep_id, '
||' -1 activity_factor, '
||' gor.resources, '
||' gor.resource_usage , '
||' gor.process_qty, '
||' gor.min_capacity, '
||' gor.max_capacity '
||' FROM gmd_recipe_orgn_resources'||at_apps_link||' gor '
||' WHERE NOT EXISTS( SELECT /*+ DRIVING_SITE(goa) */ 1 '
||' FROM gmd_recipe_orgn_activities'||at_apps_link||' goa'
||' WHERE goa.recipe_id = gor.recipe_id '
||' AND goa.organization_id = gor.organization_id '
||' AND goa.oprn_line_id = gor.oprn_line_id '
||' AND goa.routingstep_id = gor.routingstep_id ) '
||' ) gc '
||' WHERE grb.recipe_id = gc.recipe_id '
||' AND grb.delete_mark = 0 '
/* B5114783 start */
||' AND grb.recipe_status = gs.status_code '
||' AND gs.status_type IN (' ||'''700''' || ',' || '''900''' || ',' || '''400'''|| ') '
||' AND gs.delete_mark = 0 '
/* B5114783 End */
||' AND grb.recipe_id in ( SELECT /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(gs) */ distinct grb.recipe_id '
||' FROM gmd_recipes'||at_apps_link ||' grb ,'
||' gmd_recipe_validity_rules'||at_apps_link ||' ffe ,'
||' hr_organization_units'||at_apps_link ||' hou ,'
||' gmd_status_b'||at_apps_link ||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id'
||' AND ffe.validity_rule_status = gs.status_code '
||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
||' AND gs.status_type IN (' ||'''700''' || ',' || '''900''' || ',' || '''400'''|| ') '
||' AND gs.delete_mark = 0 '
||' AND ( ffe.organization_id is NULL or ffe.organization_id = hou.organization_id )'
||' AND hou.organization_id '|| l_in_str_org
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE )'
||' ORDER BY 1,2,3,4,5 ' ;
' SELECT /*+ DRIVING_SITE(grb) DRIVING_SITE(gs) DRIVING_SITE(grs) DRIVING_SITE(frd) */ '
||' grb.routing_id, grs.routingstep_id, grs.recipe_id, '
||' grs.step_qty '
||' FROM gmd_recipes'||at_apps_link||' grb, '
||' gmd_status_b'||at_apps_link||' gs, ' /* B5114783*/
||' gmd_recipe_routing_steps'||at_apps_link||' grs, '
||' fm_rout_dtl'||at_apps_link||' frd '
||' WHERE grb.recipe_id = grs.recipe_id '
||' AND grb.delete_mark = 0 '
/* B5114783 start */
||' AND grb.recipe_status = gs.status_code '
||' AND gs.status_type IN (' ||'''700''' || ',' || '''900''' || ',' || '''400'''|| ') '
||' AND gs.delete_mark = 0 '
/* B5114783 End */
/* PennColor Bug: 8230710 */
----B#6489338 Added the next where condition as below
||' AND grs.recipe_id in ( SELECT /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(gs) */ distinct grb.recipe_id '
||' FROM gmd_recipes'||at_apps_link ||' grb ,'
||' gmd_recipe_validity_rules'||at_apps_link ||' ffe ,'
||' hr_organization_units'||at_apps_link ||' hou ,'
||' gmd_status_b'||at_apps_link ||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id'
||' AND ffe.validity_rule_status = gs.status_code '
||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
||' AND gs.status_type IN (' ||'''700''' || ',' || '''900''' || ',' || '''400'''|| ') '
||' AND gs.delete_mark = 0 '
||' AND ( ffe.organization_id is NULL or ffe.organization_id = hou.organization_id )'
||' AND hou.organization_id '|| l_in_str_org
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE )'
||' AND grb.routing_id = frd.routing_id '
||' AND grs.routingstep_id = frd.routingstep_id '
-- Bug: 8354988 removed the below condition as routing step qty and recipe override though equal
-- will not imply the same meaning. recipe overrides are the scaled values basing on formula total output and routing qty
-- ||' AND grs.step_qty <> frd.step_qty '
-- Bug: 8354988 modified the order by clause from 1,2,3 to 1,3,2
||' ORDER BY 1,3,2 ' ;
/* Alternate Resource selection */
/* B5688153, Rajesh Patangya prod spec alt*/
statement_alt_resource :=
' SELECT pcrd.resource_id, acrd.resource_id, '
||' acrd.min_capacity, acrd.max_capacity, '
||' cam.runtime_factor, '
/*prod spec alt*/ ||' nvl(cam.preference,-1), nvl(prod.inventory_item_id,-1) '
||' FROM cr_rsrc_dtl'||at_apps_link||' acrd, '
||' cr_rsrc_dtl'||at_apps_link||' pcrd, '
||' cr_ares_mst'||at_apps_link||' cam, '
||' gmp_altresource_products'||at_apps_link||' prod'
||' WHERE cam.alternate_resource = acrd.resources '
||' AND cam.primary_resource = pcrd.resources '
||' AND acrd.organization_id = pcrd.organization_id '
||' AND cam.primary_resource = prod.primary_resource(+) '
||' AND cam.alternate_resource = prod.alternate_resource(+) '
||' AND acrd.delete_mark = 0 '
||' AND pcrd.delete_mark = 0 '
||' ORDER BY pcrd.resource_id, '
||' DECODE(cam.preference,NULL,cam.runtime_factor,cam.preference),'
||' prod.inventory_item_id ' ;
opr_stpdep_cursor := ' SELECT /*+ DRIVING_SITE(frd1) DRIVING_SITE(frd2) DRIVING_SITE(frdp) */ '
||' frdp.routing_id, '
||' ((frd2.routingstep_id * 2) + 1) x_dep_routingstep_id, '
||' ((frd1.routingstep_id * 2) + 1) x_routingstep_id, '
||' decode(frdp.dep_type,0,1,2) dependency_type, '
||' frdp.standard_delay, '
||' frdp.max_delay, '
||' frdp.transfer_pct, '
||' frdp.dep_routingstep_no, '
||' frdp.routingstep_no, '
||' decode(nvl(frdp.chargeable_ind, 0),0,2,1,1) '
||' FROM '
||' fm_rout_dtl'||at_apps_link||' frd1, '
||' fm_rout_dtl'||at_apps_link||' frd2, '
||' fm_rout_dep'||at_apps_link||' frdp '
||' WHERE '
||' frd1.routing_id = frdp.routing_id '
||' AND frd1.routingstep_no = frdp.routingstep_no '
||' AND frd2.routing_id = frdp.routing_id '
||' AND frd2.routingstep_no = frdp.dep_routingstep_no '
||' ORDER BY 1,3,2 ' ;
/* PROCESSING STARTS AFTER SELECTION OF THE DATA IN MEMORY */
/* ------------------------------------------------------- */
-- Link the routing header and detail
link_routing ;
' SELECT /*+ DRIVING_SITE(r) DRIVING_SITE(frm) DRIVING_SITE(fmd) DRIVING_SITE(frd) '
||' DRIVING_SITE(msi) */ fmd.formula_id, frm.recipe_id, '
||' DECODE(fmd.line_type, 1,1,2,2,-1,3), fmd.line_no, '
||' ((frm.formulaline_id * 2) + 1) x_formulaline_id, '
||' ((frm.routingstep_id * 2) + 1) x_routingstep_id, '
/* NAMIT_MTQ */
||' fmd.inventory_item_id, frd.routingstep_no, '
|| ' DECODE(fmd.detail_uom, msi.primary_uom_code, 1, ' -- akaruppa previously DECODE(fmd.item_um, gia.item_um,1,
||' inv_convert.inv_um_convert'||at_apps_link -- akaruppa previously GMICUOM.uom_conversion
||' (fmd.inventory_item_id, '
||' NULL, '
||' msi.organization_id, '
/* bug: 6918852 Vpedarla 04-Apr-2008 used the global variable for precision*/
/* ||' NULL, ' */
|| conv_precision || ' , '
||' 1, '
||' fmd.detail_uom , '
||' msi.primary_uom_code , '
||' NULL , '
||' NULL '
||' ) '
||' ) uom_conv_factor, '
/*Sowmya - As per Latest FDD changes - Changes as per Matt's review comments-
Fetch the conversion factor for a unit item, needed for converting the MTQ.
MTQ value to be passed as promary UOM.*/
||' decode(fmd.line_type, 1, frm.minimum_transfer_qty, null) minimum_transfer_qty, '
||' decode(fmd.line_type, 1, frm.minimum_delay, null) minimum_delay, '
||' decode(fmd.line_type, 1, frm.maximum_delay, null) maximum_delay '
||' FROM gmd_recipes'||at_apps_link||' r ,' /* added for asqc flg*/
||' gmd_recipe_step_materials'||at_apps_link||' frm, '
||' fm_matl_dtl'||at_apps_link||' fmd, '
/* NAMIT_MTQ */
||' fm_rout_dtl'||at_apps_link||' frd, '
||' mtl_system_items'||at_apps_link||' msi'
||' WHERE fmd.formulaline_id = frm.formulaline_id '
||' AND msi.organization_id = fmd.organization_id '
||' AND frm.recipe_id = r.recipe_id ' /* B3054460 */
||' AND (fmd.release_type in (1,2,3) OR ' /* B3054460 */
||' NVL(r.calculate_step_quantity,0) = 1 ) ' /* xfer for ASQC */
/* NAMIT_MTQ */
||' AND frd.routingstep_id = frm.routingstep_id '
||' AND msi.inventory_item_id = fmd.inventory_item_id '
/* B3970993 nsinghi. Changed order by clause from 1,2,3,4,5 to 1,2,3,6,7 */
||' ORDER BY 1,2,3,4,6,7 ';
'SELECT '
||' inv_convert.inv_um_convert'||at_apps_link -- akaruppa previously GMICUOM.uom_conversion
||' (:pitem, '
||' NULL, '
||' :orgid, '
/* bug: 6918852 Vpedarla 04-Apr-2008 used the global variable for precision*/
/* ||' NULL, ' */
|| conv_precision || ' , '
||' :pqty, '
||' :pfrom_um, '
||' :pto_um , '
||' NULL , '
||' NULL '
||' ) '
||' FROM dual';
/* Vpedarla added B3837959 MMK Issue, Bulk insert after every 1000 effectivities */
eff_counter := eff_counter + 1 ;
/* If all is OK, Bulk Insert the data into MSC tables */
log_message('Before MSC Inserts' ) ;
msc_inserts(valid);
log_message('Error encountered in MSC_INSERTS');
log_message('Formula effectivity completed. Before MSC Inserts l_eff_counter = '||l_eff_counter ) ;
/* If all is OK, Bulk Insert the data into MSC tables */
msc_inserts(valid);
log_message('Invalid after MSC Inserts' ) ;
log_message('After MSC Inserts ' ) ;
log_message('Error encountered in MSC_INSERTS');
formula_header_tab.delete ;
formula_header_tab.delete ;
formula_detail_tab.delete ;
formula_orgn_count_tab.delete ;
rtg_org_hdr_tab.delete ;
rtg_org_dtl_tab.delete ;
oper_leadtime_percent.delete ;
rtg_gen_dtl_tab.delete ;
rtg_alt_rsrc_tab.delete ;
mat_assoc_tab.delete;
rcp_orgn_override.delete ;
recipe_override.delete ;
rstep_offsets.delete ;
SELECT st.VALUE INTO v_dummy from V$MYSTAT st, V$STATNAME sn
WHERE st.STATISTIC# = sn.STATISTIC#
AND sn.NAME in ('session pga memory max');
SELECT st.VALUE INTO v_dummy from V$MYSTAT st, V$STATNAME sn
where st.STATISTIC# = sn.STATISTIC#
and sn.NAME in ('session pga memory');
REM| should NOT be done here , so would be made immediately before inserting |
REM| rows. The same may also be achieved by joining to gmp_item_aps table |
REM| while getting formula details |
REM| |
REM| HISTORY |
REM| 07/14/2002 Rajesh Patangya - Reorgnized the complete code B2314052 |
REM+=========================================================================+
*/
PROCEDURE validate_formula IS
i INTEGER ;
' SELECT fmd.formula_id, '
||' msi.organization_id, count(*), 0 '
||' FROM fm_matl_dtl'||at_apps_link||' fmd, '
||' fm_form_mst'||at_apps_link||' ffm, '
||' mtl_system_items'||at_apps_link||' msi, '
||' mtl_parameters'||at_apps_link||' mp '
||' WHERE ffm.formula_id = fmd.formula_id '
||' AND ffm.delete_mark = 0 '
||' AND fmd.qty <> 0 ' /* 2362810 Voltek Fix */
||' AND fmd.inventory_item_id = msi.inventory_item_id '
||' AND msi.recipe_enabled_flag = ''Y'' '
||' AND msi.organization_id = mp.organization_id '
||' AND mp.process_enabled_flag = ''Y'' ';
' SELECT fmd.formula_id, count(*) '
||' FROM fm_matl_dtl'||at_apps_link||' fmd, '
||' fm_form_mst'||at_apps_link||' ffm '
||' WHERE ffm.formula_id = fmd.formula_id '
||' AND ffm.delete_mark = 0 '
||' AND fmd.qty <> 0 ' /* 2362810 Voltek Fix */
||' GROUP BY fmd.formula_id '
||' ORDER BY fmd.formula_id ' ; /* 4722080 Added Order by */
REM| inserts - B2989806 |
REM+=========================================================================+
*/
PROCEDURE write_process_effectivity
(
p_x_aps_fmeff_id IN PLS_INTEGER,
p_aps_fmeff_id IN PLS_INTEGER,
return_status OUT NOCOPY BOOLEAN
)
IS
statement_form_eff VARCHAR2(9000);
'INSERT INTO gmp_form_eff'||at_apps_link
||' ( '
||' aps_fmeff_id,organization_id,fmeff_id, '
||' formula_id, routing_id, '
||' creation_date, created_by, last_update_date, '
||' last_updated_by '
||' ) '
||' VALUES '
||' ( :p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9)';
/* Process Effectivity Bulk Insert assignment */
pef_index := pef_index + 1 ;
pef_last_update_date(pef_index) := current_date_time ;
/* BOM Bulk Insert assignments */
bom_index := bom_index + 1 ;
bom_last_update_date(bom_index) := current_date_time ;
/* BOM Component Bulk Insert assignments */
/* VPEDARLA BUG: 7348022 */
/* B2657068 Scailing type decision Rajesh Patangya */
/* Scale type in material detail 0-Fixed, 1-proportional 2-Integer */
IF formula_detail_tab(loop_index).contribute_yield_ind = 'Y' THEN
IF formula_detail_tab(loop_index).scale_type = 0 THEN
l_scale_type := 0 ;
bomc_last_update_date(bomc_index) := current_date_time ;
/* B3267522, Rajesh Patangya Do not insert ingredients, if ingredient is same
as product (single level circular reference) */
IF (effectivity.inventory_item_id = formula_detail_tab(loop_index).inventory_item_id) AND
(formula_detail_tab(loop_index).line_type = -1) THEN
NULL ;
/* BOM Component Bulk Insert assignments */
bomc_index := bomc_index + 1 ;
bomc_last_update_date(bomc_index) := current_date_time ;
/* Routing Bulk insert assignments */
rtg_index := rtg_index + 1 ;
rtg_last_update_date(rtg_index) := current_date_time ;
alternates_inserted VARCHAR2(1);
alternates_inserted := 'N';
opr_last_update_date(opr_index) := current_date_time ;
END IF; /* routing Step Insertion */
/* Operation resource seqs Bulk Insert assignments */
rs_index := rs_index + 1 ;
rs_last_update_date(rs_index) := current_date_time ;
The Resources are inserted as usual and then a check is made
to find if the resource is a Primary resource and if it has
any alternates,
the Alternate Resources are inserted. Then the groups secondaries
are inserted.
Insert the Resources : Bug# 1319610
mfc 12-01-99 changed scale type to 0>2 1>1
*/
f_step_qty := 0;
/* Bulk insert assignments for operation_resources */
/* OR insert # 1 */
orig_rs_seq_num := orig_rs_seq_num + 1;
or_last_update_date(or_index) := current_date_time ;
Now the check if the above resource inserted is a Primary. If it is
Primary then find its Alternates if existing, and then insert its rows
into msc_st_operation_resources table. Also keep track of number of
times alternates are inserted. 1319610
*/
statement_no := 120 ;
alternates_inserted := 'N';
/* Bulk insert assignments for operation_resources, Alternate resources */
/* OR insert # 2 */
or_index := or_index + 1 ;
/* SGIDUGU added min capacity and max capacity inserts */
or_minimum_capacity(or_index) :=
nvl(rtg_alt_rsrc_tab(alt_cnt).min_capacity,0) ;
or_last_update_date(or_index) := current_date_time ;
alternates are inserted */
alternates_inserted := 'Y'; /* Inserted alternates */
alternates are inserted, if both the conditions are satisfied,
then loop thru the number of times the alternate resources are inserted
and insert the Auxilary resources.
This will take care of the combinations that has to come with the
alternate resources. 1319610
08/10/00 - Bug# 1388757 Changed != to <> as per the Standards
*/
statement_no := 130 ;
(alternates_inserted = 'Y')
THEN
for k in 1 ..v_alternate
LOOP
/* Bulk insert assignments operation_resources, Alternate resources */
/* OR insert # 3 */
or_index := or_index + 1 ;
/* SGIDUGU - Added min capacity and max capacity inserts */
or_minimum_capacity(or_index) := nvl(f_min_capacity,0) ;
or_last_update_date(or_index) := current_date_time ;
/* operation components bulk insert */
IF (write_row ) THEN
oc_index := oc_index + 1 ;
oc_last_update_date(oc_index) := current_date_time ;
itm_mtq_last_update_date(mtq_index) := current_date_time;
select count(*)
from ad_applied_patches
where patch_name = '13833266';
select apps_ver
into l_apps_ver
from msc_apps_instances
where instance_id = g_instance_id;
/* select maximum aps_effectivity ID */
stat := ' SELECT max(APS_FMEFF_ID) from gmp_form_eff'||at_apps_link ;
/* These variables store the MTQ related values that is last inserted. */
g_old_formula_id := -1; /* B3970993 */
/* Initialize the counter values for bulk inserts */
bom_index := 0 ;
SELECT substrb(translate(ltrim(value),',',' '), 1,
instr(translate(ltrim(value),',',' '),' ') - 1)
INTO p_location
FROM v$parameter
WHERE name = 'utl_file_dir';
log_message('directory select failed ');
SELECT to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')
INTO cur_time FROM sys.dual ;
REM| msc_inserts |
REM| DESCRIPTION |
REM| All the Bulk insert to MSC tables for OPM data |
REM| |
REM| HISTORY |
REM| 03/12/2004 Created Rajesh Patangya |
REM| |
REM+=========================================================================+
*/
PROCEDURE msc_inserts
(
return_status OUT NOCOPY BOOLEAN
)
IS
stmt_no NUMBER ;
/* --------------------------- Process Effectivity Insert ------------------------- */
stmt_no := 901 ;
INSERT INTO msc_st_process_effectivity (
process_sequence_id,
item_id,
organization_id,
effectivity_date,
disable_date,
minimum_quantity,
maximum_quantity,
preference,
routing_sequence_id,
bill_sequence_id,
sr_instance_id,
item_process_cost, line_id,
total_product_cycle_time, primary_line_flag, production_line_rate,
-- Bug: 8715318 Vpedarla uncommented the below line
-- recipe, /* B5584507 */
deleted_flag,
last_update_date,
last_updated_by,
creation_date,
created_by )
VALUES
(
pef_process_sequence_id(i) ,
pef_item_id(i) ,
pef_organization_id(i) ,
pef_effectivity_date(i) ,
pef_disable_date(i) ,
pef_minimum_quantity(i) ,
pef_maximum_quantity(i) ,
pef_preference(i) ,
pef_routing_sequence_id(i) ,
pef_bill_sequence_id(i) ,
pef_sr_instance_id(i) ,
null_value, null_value,
null_value, null_value, null_value,
-- Bug: 8715318 Vpedarla uncommented the below line
-- pef_recipe(i), /* B5584507 */
2 , /* Deleted Flag */
pef_last_update_date(i) ,
0 , /* Last Updated By */
pef_creation_date(i) ,
0 /* Created By */
) ;
/* ------------------------------- BOM Insert --------------------------- */
stmt_no := 902 ;
INSERT INTO msc_st_boms (
bill_sequence_id,
sr_instance_id,
organization_id,
assembly_item_id,
assembly_type,
alternate_bom_designator,
specific_assembly_comment,
scaling_type,
assembly_quantity,
uom,
/* NAMIT_CR */
operation_seq_num,
deleted_flag,
last_update_date,
last_updated_by,
creation_date,
created_by )
VALUES
(
bom_bill_sequence_id(i),
bom_sr_instance_id(i) ,
bom_organization_id(i) ,
bom_assembly_item_id(i),
1 , /* Assembly Type */
bom_alternate_bom_designator(i),
bom_specific_assembly_comment(i),
bom_scaling_type(i) ,
bom_assembly_quantity(i),
bom_uom(i) ,
/* NAMIT_CR */
bom_op_seq_number(i) ,
2 , /* Deleted Flag */
bom_last_update_date(i) ,
0 , /* Last Updated By */
bom_creation_date(i) ,
0 /* Created By */
) ;
/* --------------------------- BOM Components Insert Stars ------------------------- */
stmt_no := 903 ;
INSERT INTO msc_st_bom_components
(
component_sequence_id,
sr_instance_id,
organization_id,
Inventory_item_id,
using_assembly_id,
bill_sequence_id,
component_type,
scaling_type,
change_notice,
revision,
uom_code,
usage_quantity,
effectivity_date,
/* NAMIT_ASQC */
contribute_to_step_qty,
disable_date,
from_unit_number,
to_unit_number,
use_up_code,
suggested_effectivity_date,
driving_item_id,
operation_offset_percent,
optional_component,
old_effectivity_date,
wip_supply_type,
planning_factor,
atp_flag,
component_yield_factor,
deleted_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
scale_multiple,
scale_rounding_variance,
rounding_direction
)
VALUES
(
bomc_component_sequence_id(i),
bomc_sr_instance_id(i),
bomc_organization_id(i),
bomc_Inventory_item_id(i),
bomc_using_assembly_id(i),
bomc_bill_sequence_id(i),
bomc_component_type(i),
bomc_scaling_type(i),
null_value,
null_value,
bomc_uom_code(i),
bomc_usage_quantity(i),
bomc_effectivity_date(i),
/* NAMIT_ASQC */
bomc_contribute_to_step_qty(i),
bomc_disable_date(i), /* B5176291 - Item substitution changes */
null_value,
null_value,
null_value,
null_value,
null_value,
bomc_opr_offset_percent(i),
bomc_optional_component(i),
null_value,
bomc_wip_supply_type(i),
null_value,
1, /* atp flag */
1, /* component_yield_factor */
2 , /* Deleted Flag */
bomc_last_update_date(i) ,
0 , /* Last Updated By */
bomc_creation_date(i) ,
0 , /* Created By */
bomc_scale_multiple(i),
bomc_scale_rounding_variance(i),
bomc_rounding_direction(i)
);
/* --------------------------- Routing Insert Stars ------------------------- */
stmt_no := 904 ;
INSERT INTO msc_st_routings (
routing_sequence_id,
sr_instance_id,
routing_type,
routing_comment,
alternate_routing_designator,
project_id,
task_id,
line_id,
uom_code,
cfm_routing_flag,
ctp_flag,
routing_quantity,
assembly_item_id,
organization_id,
auto_step_qty_flag,
deleted_flag,
last_update_date,
last_updated_by,
creation_date,
created_by )
VALUES (
rtg_routing_sequence_id(i),
rtg_sr_instance_id(i),
1 , /* routing_type */
rtg_routing_comment(i),
rtg_alt_routing_designator(i),
null_value,
null_value,
null_value,
rtg_uom_code(i),
null_value,
null_value,
rtg_routing_quantity(i),
rtg_assembly_item_id(i),
rtg_organization_id(i),
rtg_auto_step_qty_flag(i),
2, /* Deleted Flag */
rtg_last_update_date(i), /* Last Update Date */
0,
rtg_creation_date(i), /* Creation Date */
0 ) ;
/* ----------------------- Operation Resource Insert --------------------- */
stmt_no := 905 ;
INSERT INTO msc_st_operation_resources (
operation_sequence_id,
resource_seq_num,
resource_id,
alternate_number,
principal_flag,
basis_type,
resource_usage,
max_resource_units,
resource_units,
uom_code,
deleted_flag,
sr_instance_id,
routing_sequence_id,
organization_id,
minimum_capacity,
maximum_capacity,
setup_id,
orig_resource_seq_num,
breakable_activity_flag,
last_update_date,
last_updated_by,
creation_date,
created_by )
VALUES (
or_operation_sequence_id(i) ,
or_resource_seq_num(i) ,
or_resource_id(i) ,
or_alternate_number(i) ,
or_principal_flag(i) ,
or_basis_type(i) ,
or_resource_usage(i) ,
or_max_resource_units(i) ,
or_resource_units(i) ,
or_uom_code(i) ,
2,
or_sr_instance_id(i) ,
or_routing_sequence_id(i) ,
or_organization_id(i),
or_minimum_capacity(i),
or_maximum_capacity(i),
or_setup_id(i),
or_orig_rs_seq_num(i),
or_break_ind(i),
or_last_update_date(i) ,
0,
or_creation_date(i) ,
0 );
/* ----------------------- Operations Insert --------------------- */
stmt_no := 906 ;
INSERT INTO msc_st_routing_operations (
operation_sequence_id,
routing_sequence_id,
operation_seq_num,
sr_instance_id,
operation_description,
effectivity_date,
disable_date,
from_unit_number,
to_unit_number,
option_dependent_flag,
operation_type,
minimum_transfer_quantity,
yield,
/* NAMIT_ASQC */
step_quantity,
step_quantity_uom,
department_id,
department_code,
operation_lead_time_percent,
cumulative_yield,
reverse_cumulative_yield,
net_planning_percent,
setup_duration,
tear_down_duration,
uom_code,
organization_id,
standard_operation_code,
deleted_flag,
last_update_date,
last_updated_by,
creation_date,
created_by )
VALUES
(
opr_operation_sequence_id(i),
opr_routing_sequence_id(i),
opr_operation_seq_num(i),
opr_sr_instance_id(i),
opr_operation_description(i),
opr_effectivity_date(i),
null_value,
null_value,
null_value,
1,
null_value,
opr_mtransfer_quantity(i), /*B2870041*/
null_value, /* B2365684 rtg_org_dtl_tab(loop_index).step_qty, */
/* NAMIT_ASQC */
opr_step_qty(i),
opr_step_qty_uom(i),
opr_department_id(i),
opr_department_code(i),
opr_lead_time_percent(i), -- Bug: 7391495 Vpedarla
null_value,
null_value,
null_value,
null_value,
null_value,
opr_uom_code(i),
opr_organization_id(i),
null_value ,
2, /* Deleted Flag */
opr_last_update_date(i),
0,
opr_creation_date(i),
0 ) ;
/* ----------------------- Operation Sequence Insert --------------------- */
stmt_no := 907 ;
INSERT INTO msc_st_operation_resource_seqs (
operation_sequence_id,
resource_seq_num,
sr_instance_id,
department_id,
resource_offset_percent,
schedule_flag,
routing_sequence_id,
organization_id,
deleted_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
activity_group_id )
VALUES (
rs_operation_sequence_id(i),
rs_resource_seq_num(i),
rs_sr_instance_id(i),
rs_department_id(i),
null_value,
rs_schedule_flag(i),
rs_routing_sequence_id(i),
rs_organization_id(i),
2, /* deleted flag */
rs_last_update_date(i),
0,
rs_creation_date(i),
0 ,
rs_activity_group_id(i)
) ;
/* ----------------------- Operation Component Insert --------------------- */
stmt_no := 908 ;
INSERT INTO msc_st_operation_components (
operation_sequence_id, component_sequence_id, sr_instance_id,
bill_sequence_id, routing_sequence_id, organization_id,
deleted_flag, last_update_date, last_updated_by,
creation_date, created_by )
VALUES (
oc_operation_sequence_id(i),
oc_component_sequence_id(i),
oc_sr_instance_id(i),
oc_bill_sequence_id(i),
oc_routing_sequence_id(i),
oc_organization_id(i),
2,
oc_last_update_date(i),
0,
oc_creation_date(i),
0 ) ;
/* ----------------------- MTQ Insert --------------------- */
/* NAMIT_MTQ */
stmt_no := 909 ;
INSERT INTO msc_st_operation_networks(
from_op_seq_id,
routing_sequence_id,
dependency_type,
transition_type,
plan_id,
sr_instance_id,
deleted_flag,
from_item_id,
organization_id,
minimum_transfer_qty,
minimum_time_offset,
maximum_time_offset,
last_update_date,
last_updated_by,
creation_date,
created_by,
from_op_seq_num,
planning_pct -- Bug: 6407973 KBANDDYO Added the column to send default value of 100
) VALUES
(
itm_mtq_from_op_seq_id(i),
itm_mtq_routing_sequence_id(i),
5, /* MTQ with Hardlink */
1, /* Primary */
-1,
itm_mtq_sr_instance_id(i),
2,
itm_mtq_from_item_id(i),
itm_mtq_organization_id(i),
itm_mtq_min_tran_qty(i),
itm_mtq_min_time_offset(i),
itm_mtq_max_time_offset(i),
itm_mtq_last_update_date(i),
0,
itm_mtq_creation_date(i),
0,
itm_mtq_frm_op_seq_num(i),
100 -- Bug: 6407973 KBANDDYO added the column to send default value of 100
);
/* ----------------------- Step Dependency Insert --------------------- */
/* NAMIT_CR */
stmt_no := 910 ;
INSERT INTO msc_st_operation_networks(
from_op_seq_id,
to_op_seq_id,
routing_sequence_id,
dependency_type,
transition_type,
plan_id,
sr_instance_id,
deleted_flag,
minimum_time_offset,
maximum_time_offset,
transfer_pct,
-- Bug: 6407973 KBANDDYO inserting into column planning_pct along with transfer_pct
planning_pct,
last_update_date,
last_updated_by,
creation_date,
created_by,
from_op_seq_num,
to_op_seq_num,
apply_to_charges,
organization_id
) VALUES
(
opr_stpdep_frm_seq_id(i),
opr_stpdep_to_seq_id(i),
opr_stpdep_routing_sequence_id(i),
opr_stpdep_dependency_type(i),
1, /* Transition Type, 1 = Primary*/
-1, /* Plan Id */
opr_stpdep_sr_instance_id(i),
2, /* Deleted Flag */
opr_stpdep_min_time_offset(i),
opr_stpdep_max_time_offset(i),
opr_stpdep_trans_pct(i),
opr_stpdep_trans_pct(i), -- Bug: 6407973 KBANDDYO
opr_stpdep_last_update_date(i), /* 7363807 */
0,
opr_stpdep_creation_date(i), /* 7363807 */
0,
opr_stpdep_frm_op_seq_num(i),
opr_stpdep_to_op_seq_num(i),
opr_stpdep_app_to_chrg(i),
opr_stpdep_organization_id(i)
);
SELECT st.VALUE INTO v_dummy from V$MYSTAT st, V$STATNAME sn
WHERE st.STATISTIC# = sn.STATISTIC#
AND sn.NAME in ('session pga memory max');
log_message('MSC_INSERTS-Before Freeing up. Session pga memory max = ' || to_char(v_dummy) );
SELECT st.VALUE INTO v_dummy from V$MYSTAT st, V$STATNAME sn
where st.STATISTIC# = sn.STATISTIC#
and sn.NAME in ('session pga memory');
log_message('MSC_INSERTS-Before Freeing up. Session pga memory = ' || TO_CHAR(v_dummy) );
bom_organization_id.delete ;
bomc_organization_id.delete ;
pef_organization_id.delete ;
rtg_organization_id.delete ;
oc_organization_id.delete ;
opr_organization_id.delete ;
or_organization_id.delete ;
rs_organization_id.delete ;
bom_bill_sequence_id.delete ;
bomc_bill_sequence_id.delete ;
pef_bill_sequence_id.delete ;
oc_bill_sequence_id.delete ;
bom_last_update_date.delete ;
bomc_last_update_date.delete ;
pef_last_update_date.delete ;
rtg_last_update_date.delete ;
or_last_update_date.delete ;
opr_last_update_date.delete ;
rs_last_update_date.delete ;
oc_last_update_date.delete ;
bom_creation_date.delete ;
bomc_creation_date.delete ;
pef_creation_date.delete ;
rtg_creation_date.delete ;
or_creation_date.delete ;
opr_creation_date.delete ;
rs_creation_date.delete ;
oc_creation_date.delete ;
pef_effectivity_date.delete ;
bomc_effectivity_date.delete ;
opr_effectivity_date.delete ;
opr_lead_time_percent.delete ;
rtg_routing_sequence_id.delete ;
pef_routing_sequence_id.delete ;
or_routing_sequence_id.delete ;
opr_routing_sequence_id.delete ;
rs_routing_sequence_id.delete ;
oc_routing_sequence_id.delete ;
pef_recipe.delete ;
bomc_uom_code.delete;
rtg_uom_code.delete;
or_uom_code.delete ;
opr_uom_code.delete;
bom_assembly_item_id.delete ;
rtg_assembly_item_id.delete ;
bomc_component_sequence_id.delete ;
oc_component_sequence_id.delete ;
or_operation_sequence_id.delete ;
opr_operation_sequence_id.delete ;
rs_operation_sequence_id.delete ;
oc_operation_sequence_id.delete ;
or_resource_seq_num.delete ;
rs_resource_seq_num.delete ;
pef_process_sequence_id.delete ;
pef_item_id.delete ;
pef_disable_date.delete ;
pef_minimum_quantity.delete ;
pef_maximum_quantity.delete ;
pef_preference.delete ;
bom_alternate_bom_designator.delete ;
bom_specific_assembly_comment.delete ;
bom_scaling_type.delete ;
bom_assembly_quantity.delete ;
bom_uom.delete ;
bomc_Inventory_item_id.delete ;
bomc_using_assembly_id.delete ;
bomc_component_type.delete ;
bomc_scaling_type.delete ;
bomc_usage_quantity.delete ;
bomc_opr_offset_percent.delete ;
bomc_optional_component.delete ;
bomc_wip_supply_type.delete ;
bomc_scale_multiple.delete ;
bomc_scale_rounding_variance.delete ;
bomc_rounding_direction.delete ;
rtg_routing_comment.delete ;
rtg_alt_routing_designator.delete ;
rtg_routing_quantity.delete ;
or_resource_id.delete ;
or_alternate_number.delete ;
or_principal_flag.delete ;
or_basis_type.delete ;
or_resource_usage.delete ;
or_max_resource_units.delete ;
or_resource_units.delete ;
opr_operation_seq_num.delete ;
opr_operation_description.delete ;
opr_mtransfer_quantity.delete ;
opr_department_id.delete ;
rs_department_id.delete ;
opr_department_code.delete ;
rs_activity_group_id.delete ;
rs_schedule_flag.delete ;
opr_stpdep_frm_seq_id.delete;
opr_stpdep_to_seq_id.delete;
opr_stpdep_routing_sequence_id.delete;
opr_stpdep_dependency_type.delete;
opr_stpdep_app_to_chrg.delete;
opr_stpdep_sr_instance_id.delete;
opr_stpdep_organization_id.delete;
opr_stpdep_frm_op_seq_num.delete;
opr_stpdep_to_op_seq_num.delete;
opr_stpdep_trans_pct.delete;
opr_stpdep_min_time_offset.delete;
opr_stpdep_max_time_offset.delete;
itm_mtq_frm_op_seq_num.delete;
itm_mtq_max_time_offset.delete;
itm_mtq_min_tran_qty.delete;
itm_mtq_organization_id.delete;
itm_mtq_from_item_id.delete;
itm_mtq_sr_instance_id.delete;
itm_mtq_routing_sequence_id.delete;
itm_mtq_from_op_seq_id.delete;
SELECT st.VALUE INTO v_dummy from V$MYSTAT st, V$STATNAME sn
WHERE st.STATISTIC# = sn.STATISTIC#
AND sn.NAME in ('session pga memory max');
log_message('MSC_INSERTS-After Freeing up2. Session pga memory max = ' || to_char(v_dummy) );
SELECT st.VALUE INTO v_dummy from V$MYSTAT st, V$STATNAME sn
where st.STATISTIC# = sn.STATISTIC#
and sn.NAME in ('session pga memory');
log_message('MSC_INSERTS-After Freeing up2. Session pga memory = ' || TO_CHAR(v_dummy) );
log_message('Error in MSC Inserts : '||stmt_no || ':' || sqlerrm);
END msc_inserts ;
REM| This procedure inserts rows into msc_st_resource_setups and |
REM| msc_st_setup_transitions |
REM| |
REM| HISTORY |
REM| 02/03/2006 B4918786 Rajesh Patangya Rewrite for SDS Enhancement |
REM| MSC_RESOURCE_SETUPS unique key is ON |
REM| Instance_id,resource_id,organization_id and setup_id |
REM| 02-20-2007 B5741664 Added a join with mtl_parameters to filter |
REM| rows for process organization - similar to 11.5.10 code |
REM| 08-01-2008 B6710684 Vpedarla Added one more in parameter |
REM | 08-06-2011 B11692192 Modified the cursor Fact_tran for performance |
REM+=========================================================================+
*/
PROCEDURE write_setups_and_transitions
(
at_apps_link IN VARCHAR2,
return_status OUT NOCOPY BOOLEAN
) IS
l_profile VARCHAR2(4);
Zero_tran := ' INSERT INTO msc_st_setup_transitions ( '
||' resource_id, '
||' organization_id, '
||' from_setup_id, '
||' to_setup_id, '
||' transition_time, '
||' transition_penalty, '
||' transition_uom, '
||' sr_instance_id, '
||' deleted_flag ) '
||' SELECT '
||' ((a.resource_id * 2) + 1),'
||' a.organization_id, '
||' a.seq_dep_id, '
||' b.seq_dep_id, '
||' 0 setup_time, '
||' 0 penalty_factor, '
||' :profile, '
||' :instance1 , '
||' 2 '
||' FROM ( '
||' SELECT '
||' rd.organization_id, '
||' s.category_id, '
||' s.seq_dep_id, '
||' o.oprn_id, '
||' rd.resource_id, '
||' count(o.oprn_id) OVER (PARTITION BY rd.organization_id,s.category_id,rd.resource_id) CNT '
||' FROM '
||' cr_rsrc_dtl'||at_apps_link||' rd, '
||' gmp_sequence_types'||at_apps_link||' s, '
||' gmd_operation_resources'||at_apps_link||' r, '
||' gmd_operation_activities'||at_apps_link||' a, '
||' gmd_operations'||at_apps_link||' o '
||' WHERE o.oprn_id = a.oprn_id '
||' AND a.oprn_line_id = r.oprn_line_id '
||' AND a.sequence_dependent_ind = 1 '
||' AND r.prim_rsrc_ind = 1 '
||' AND r.resources = rd.resources '
||' AND o.oprn_id = s.oprn_id ' ;
||' SELECT '
||' rd.organization_id, '
||' s.category_id, '
||' s.seq_dep_id, '
||' o.oprn_id, '
||' rd.alt_resource_id, '
||' count(o.oprn_id) OVER (PARTITION BY rd.organization_id,s.category_id,rd.alt_resource_id) CNT '
||' FROM '
||' gmp_sequence_types'||at_apps_link||' s, '
||' gmd_operation_resources'||at_apps_link||' r, '
||' gmd_operation_activities'||at_apps_link||' a, '
||' gmd_operations'||at_apps_link||' o, '
||' (SELECT pcrd.resource_id prim_resource_id, '
||' pcrd.resources prim_resources, '
||' acrd.resource_id alt_resource_id, '
||' acrd.resources alt_resources, '
||' acrd.organization_id '
||' FROM cr_rsrc_dtl'||at_apps_link||' acrd, '
||' cr_rsrc_dtl'||at_apps_link||' pcrd, '
||' cr_ares_mst'||at_apps_link||' cam '
||' WHERE cam.alternate_resource = acrd.resources '
||' AND cam.primary_resource = pcrd.resources '
||' AND acrd.organization_id = pcrd.organization_id ' ;
||' AND acrd.delete_mark = 0 '
||' ORDER BY pcrd.resource_id ) rd '
||' WHERE o.oprn_id = a.oprn_id '
||' AND a.oprn_line_id = r.oprn_line_id '
||' AND a.sequence_dependent_ind = 1 '
||' AND r.prim_rsrc_ind = 1 '
||' AND o.oprn_id = s.oprn_id '
||' AND r.resources = rd.prim_resources '
||' ) a, '
||' ( '
||' SELECT '
||' rd.organization_id, '
||' s.category_id, '
||' s.seq_dep_id, '
||' o.oprn_id, '
||' rd.resource_id, '
||' count(o.oprn_id) OVER (PARTITION BY rd.organization_id,s.category_id,rd.resource_id) CNT '
||' FROM '
||' cr_rsrc_dtl'||at_apps_link||' rd, '
||' gmp_sequence_types'||at_apps_link||' s, '
||' gmd_operation_resources'||at_apps_link||' r, '
||' gmd_operation_activities'||at_apps_link||' a, '
||' gmd_operations'||at_apps_link||' o '
||' WHERE o.oprn_id = a.oprn_id '
||' AND a.oprn_line_id = r.oprn_line_id '
||' AND a.sequence_dependent_ind = 1 '
||' AND r.prim_rsrc_ind = 1 '
||' AND r.resources = rd.resources '
||' AND o.oprn_id = s.oprn_id ' ;
||' SELECT '
||' rd.organization_id, '
||' s.category_id, '
||' s.seq_dep_id, '
||' o.oprn_id, '
||' rd.alt_resource_id, '
||' count(o.oprn_id) OVER (PARTITION BY rd.organization_id,s.category_id,rd.alt_resource_id) CNT '
||' FROM '
||' gmp_sequence_types'||at_apps_link||' s, '
||' gmd_operation_resources'||at_apps_link||' r, '
||' gmd_operation_activities'||at_apps_link||' a, '
||' gmd_operations'||at_apps_link||' o, '
||' (SELECT pcrd.resource_id prim_resource_id, '
||' pcrd.resources prim_resources, '
||' acrd.resource_id alt_resource_id, '
||' acrd.resources alt_resources, '
||' acrd.organization_id '
||' FROM cr_rsrc_dtl'||at_apps_link||' acrd, '
||' cr_rsrc_dtl'||at_apps_link||' pcrd, '
||' cr_ares_mst'||at_apps_link||' cam '
||' WHERE cam.alternate_resource = acrd.resources '
||' AND cam.primary_resource = pcrd.resources '
||' AND acrd.organization_id = pcrd.organization_id ' ;
||' AND acrd.delete_mark = 0 '
||' ORDER BY pcrd.resource_id ) rd '
||' WHERE o.oprn_id = a.oprn_id '
||' AND a.oprn_line_id = r.oprn_line_id '
||' AND a.sequence_dependent_ind = 1 '
||' AND r.prim_rsrc_ind = 1 '
||' AND o.oprn_id = s.oprn_id '
||' AND r.resources = rd.prim_resources '
||' ORDER BY 1,2,4,3 '
||' ) b '
||' WHERE a.organization_id = b.organization_id '
||' AND a.category_id = b.category_id '
||' AND a.resource_id = b.resource_id '
||' AND a.cnt = b.cnt '
||' AND a.seq_dep_id <> b.seq_dep_id '
||' AND a.cnt > 1 ' ;
Fact_tran := ' INSERT INTO msc_st_setup_transitions ( '
||' resource_id, '
||' organization_id, '
||' from_setup_id, '
||' to_setup_id, '
||' transition_time, '
||' transition_penalty, '
||' transition_uom, '
||' sr_instance_id, '
||' deleted_flag ) '
||' SELECT unique '
||' a.resource_id, '
||' a.organization_id, '
||' a.from_seq_dep_id, '
||' a.to_seq_dep_id, '
||' a.setup_time, '
||' a.penalty_factor, '
||' a.uom_code, '
||' a.sr_instance_id, '
||' a.deleted_flag '
||' FROM ( '
||' SELECT unique '
||' b.resource_id, '
||' b.organization_id, '
||' a.from_seq_dep_id, '
||' a.to_seq_dep_id, '
||' a.setup_time, '
||' a.penalty_factor, '
||' b.uom_code, '
||' b.sr_instance_id, '
||' b.deleted_flag '
||' FROM gmp_sequence_dependencies'||at_apps_link||' a, '
||' (select /*+ noparallel(msc_st_operation_resources) */ unique mt.RESOURCE_ID, mt.ORGANIZATION_ID,'
||' mt.SETUP_ID , mt.deleted_flag, mt.sr_instance_id, mt.UOM_CODE '
||' FROM mtl_parameters'||at_apps_link|| ' mp, '
||' MSC_ST_OPERATION_RESOURCES mt '
||' WHERE mp.organization_id = mt.organization_id AND '
||' mt.sr_instance_id = :instance1 AND '
||' mt.setup_id is not null AND '
||' mp.process_enabled_flag = '|| ''''||'Y'||'''' ||' ) b '
||' WHERE b.setup_id = a.from_seq_dep_id ' ;
||' (select /*+ noparallel(msc_st_operation_resources) */ '
||' unique mt.RESOURCE_ID, mt.ORGANIZATION_ID,'
||' mt.SETUP_ID , mt.deleted_flag, mt.sr_instance_id, mt.UOM_CODE '
||' FROM MSC_ST_OPERATION_RESOURCES mt '
||' WHERE mt.sr_instance_id = :instance2 ' ;
rsrc_setup := ' INSERT INTO msc_st_resource_setups ( '
||' resource_id, '
||' organization_id, '
||' sr_instance_id, '
||' setup_id, '
||' setup_code, '
||' setup_description,'
||' deleted_flag ) '
||'SELECT unique '
||' mst.resource_id, '
||' mst.organization_id, '
||' mst.sr_instance_id, '
||' gst.SEQ_DEP_ID , '
||' mc.CONCATENATED_SEGMENTS, '
||' mc.CONCATENATED_SEGMENTS, '
||' 2 '
||' FROM gmp_sequence_types'||at_apps_link||' gst, '
||' MTL_CATEGORIES_B_KFV'||at_apps_link||' mc, '
||' ( SELECT unique mt.organization_id, mt.resource_id, '
||' mt.transition_uom,mt.sr_instance_id, '
||' mt.deleted_flag , mt.from_setup_id, mt.to_setup_id '
||' FROM mtl_parameters'||at_apps_link|| ' mp, '
||' msc_st_setup_transitions mt '
||' WHERE mp.organization_id = mt.organization_id AND '
||' mp.process_enabled_flag = '|| ''''||'Y'||'''' ||' ) mst '
||' WHERE gst.oprn_id <> -1 '
||' AND mc.category_id = gst.category_id '
||' AND mst.sr_instance_id = :instance1 '
||' AND (gst.seq_dep_id = mst.from_setup_id OR '
||' gst.seq_dep_id = mst.to_setup_id ) ' ;
rsrc_setup := ' INSERT INTO msc_st_resource_setups ( '
||' resource_id, '
||' organization_id, '
||' sr_instance_id, '
||' setup_id, '
||' setup_code, '
||' setup_description,'
||' deleted_flag ) '
||' SELECT /*+DRIVING_SITE(mtl) DRIVING_SITE(gmp) DRIVING_SITE(gao) DRIVING_SITE(gmd) DRIVING_SITE(crd) */ unique '
||' ((crd.resource_id*2) + 1) resource_id , '
||' crd.ORGANIZATION_ID , '
||' :instance1 , '
||' gmp.SEQ_DEP_ID , '
||' mtl.CONCATENATED_SEGMENTS, '
||' mtl.CONCATENATED_SEGMENTS, '
||' 2 '
||' FROM gmp_sequence_types'||at_apps_link||' gmp, '
||' MTL_CATEGORIES_B_KFV'||at_apps_link||' mtl, '
||' gmd_operation_activities'||at_apps_link||' gao, '
||' gmd_operation_resources'||at_apps_link||' gmd, '
||' cr_rsrc_dtl'||at_apps_link||' crd '
||' WHERE gmp.oprn_id = gao.oprn_id '
||' AND gmp.oprn_id <> -1 '
||' AND gao.OPRN_LINE_ID = gmd.OPRN_LINE_ID '
||' AND gao.sequence_dependent_ind = 1 '
||' AND gmd.prim_rsrc_ind = 1 '
||' AND mtl.category_id = gmp.category_id '
||' AND crd.RESOURCES = gmd.RESOURCES ' ;
REM| This procedure inserts rows for step dependency |
REM| |
REM| DESCRIPTION |
REM| |
REM| |
REM| INPUT PARAMETERS |
REM| None |
REM| |
REM| OUTPUT PARAMETERS |
REM| None |
REM| |
REM| INPUT/OUTPUT PARAMETERS |
REM| None |
REM| |
REM| HISTORY |
REM| 06/16/2004 Namit Singhi created |
REM+=========================================================================+
*/
PROCEDURE write_step_dependency (
p_x_aps_fmeff_id IN PLS_INTEGER
)
IS
stpdep_start_index INTEGER;
opr_stpdep_last_update_date(dep_index) := current_date_time ;
insert_statement VARCHAR2(4096);
last_update_date DATE,
last_updated_by PLS_INTEGER,
last_update_login PLS_INTEGER ) ;
retrieval_cursor := 'DELETE FROM gmp_item_aps'||at_apps_link;
'SELECT iim.item_no, iim.item_id,nvl(iim.seq_category_id,-1), '
||' t.seq_dep_id, '
||' iim.seq_dpnd_class , '
||' iim.item_um, mum.uom_code,iim.lot_ctl, iim.item_desc1, '
||' msi.inventory_item_id, '
||' iwm.mtl_organization_id, '
||' pwe.whse_code, decode(sum(pwe.replen_ind), 0, 0, 1), '
||' decode(sum(pwe.consum_ind), 0, 0, 1), '
||' pwe.plant_code, iim.creation_date, iim.created_by, '
||' iim.last_update_date,iim.last_updated_by, NULL '
||' FROM ic_item_mst'||at_apps_link||' iim,'
||' sy_uoms_mst'||at_apps_link||' sou,' /* B1540127 */
||' ps_whse_eff'||at_apps_link||' pwe,'
||' ic_whse_mst'||at_apps_link||' iwm,'
||' mtl_system_items'||at_apps_link||' msi,'
||' mtl_units_of_measure'||at_apps_link||' mum, '
||' sy_orgn_mst'||at_apps_link||' som, '
||' (SELECT category_id,seq_dep_id ' /* SGIDUGU */
||' FROM gmp_sequence_types'||at_apps_link /* SGIDUGU
*/
||' WHERE oprn_id = -1 ' /* SGIDUGU */
||' ) t '
||' WHERE iim.delete_mark = 0 '
||' AND som.delete_mark = 0 '
||' AND iim.inactive_ind = 0 '
||' AND iim.item_no = msi.segment1 '
||' AND iwm.mtl_organization_id = msi.organization_id '
||' AND pwe.plant_code = som.orgn_code '
||' AND pwe.whse_code = iwm.whse_code '
||' AND sou.unit_of_measure = mum.unit_of_measure '
||' AND sou.delete_mark = 0 ' ;
||' iim.creation_date, iim.created_by, iim.last_update_date, '
||' iim.last_updated_by ' ;
/* SGIDUGU - added inserts for Category Id and Seq Dep Id */
insert_statement :=
'INSERT INTO gmp_item_aps'||at_apps_link||' '
||' ( '
||' item_no, item_id,category_id,seq_dep_id,seq_dpnd_class, '
||' item_um, uom_code, '
||' lot_control, item_desc1, '
||' aps_item_id, organization_id, whse_code, replen_ind,'
||' consum_ind, plant_code, creation_date, created_by, '
||' last_update_date, last_updated_by, last_update_login '
||' ) '
||' VALUES '
||' (:p1,:p2,:p3,:p4,:p5,:p6, '
||' :p7,:p8,:p9,:p10,'
||' :p11,:p12,:p13,:p14, '
||' :p15,:p16,:p17,:p18,:p19,:p20)';
EXECUTE IMMEDIATE insert_statement USING
gmp_item_aps_rec.item_no,
gmp_item_aps_rec.item_id,
gmp_item_aps_rec.category_id, /* SGIDUGU */
gmp_item_aps_rec.seq_dep_id, /* SGIDUGU */
gmp_item_aps_rec.seq_dpnd_class, /* SGIDUGU */
gmp_item_aps_rec.item_um,
gmp_item_aps_rec.uom_code,
gmp_item_aps_rec.lot_control,
gmp_item_aps_rec.item_desc1,
gmp_item_aps_rec.aps_item_id,
gmp_item_aps_rec.organization_id,
gmp_item_aps_rec.whse_code,
gmp_item_aps_rec.replen_ind,
gmp_item_aps_rec.consum_ind,
gmp_item_aps_rec.plant_code,
run_date,
gmp_item_aps_rec.created_by,
run_date,
gmp_item_aps_rec.last_updated_by,
0;
uom_code_dblink := 'select fnd_profile.VALUE'||pdblink||'('''||profile_name||''')'||' from dual ';