[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 NUMBER ; /* B3970993 */
/* NAVIN: Alternate Resource selection */
TYPE prod_alt_resource_typ IS RECORD
(
prim_resource_id PLS_INTEGER,
alt_resource_id PLS_INTEGER,
min_capacity NUMBER,
max_capacity NUMBER,
runtime_factor NUMBER, /* B2353759,alternate runtime_factor */
preference PLS_INTEGER, /* B5688153 Prod spec alternates */
item_id PLS_INTEGER /* B5688153 Prod spec alternates */
);
bom_last_update_date date_idx_tbl ;
bomc_last_update_date date_idx_tbl ;
pef_last_update_date date_idx_tbl ;
rtg_last_update_date date_idx_tbl ;
or_last_update_date date_idx_tbl ;
opr_last_update_date date_idx_tbl ;
rs_last_update_date date_idx_tbl ;
oc_last_update_date date_idx_tbl ;
i_backward_update_time_fence number_idx_tbl;/* akaruppa B5007729 */
i_forward_update_time_fence number_idx_tbl;/* akaruppa B5007729 */
o_deleted_flag pls_idx_tbl;/* akaruppa B5007729 */
/* Bug:6156957 Vpedarla select organisations to restrict the data collections */
sql_stmt := 'select distinct plant_code from gmp_item_aps ' ||at_apps_link ;
/* B2989806 Added inline tables and outer joins to select aps_fmeff_id */
/* NAMIT UOM Changes */
-- l_gmp_um_code := fnd_profile.VALUE('SY$UOM_HOURS'); /* OPM UOM */
' select um_type '
||' from sy_uoms_mst'||at_apps_link
||' where um_code = :gmp_um_code ';
' SELECT eff.recipe_validity_rule_id, '
||' nvl(gfe.aps_fmeff_id,-1),eff.item_id, '
||' eff.formula_id,eff.lorgn_code, eff.organization_id, '
||' eff.start_date, eff.end_date, eff.inv_min_qty, '
||' eff.inv_max_qty, eff.preference, eff.uom_code, '
||' eff.wcode, eff.routing_id, '
||' eff.routing_no, eff.routing_vers, eff.routing_desc, '
||' eff.item_um, eff.routing_qty, '
||' eff.prd_fct , eff.prd_ind, '
||' eff.aps_item_id, eff.recipe_id, eff.recipe_no, eff.recipe_version, eff.rhdr_loc, '
||' decode(eff.calculate_step_quantity,0,2,1) calculate_step_quantity, '
||' eff.category_id,NULL , '
||' eff.seq_dpnd_class '
||' FROM ( '
||' SELECT /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(ffm) DRIVING_SITE(frh) DRIVING_SITE(som) DRIVING_SITE(gia)'
||' DRIVING_SITE(gs1) DRIVING_SITE(gs2) DRIVING_SITE(gs3) DRIVING_SITE(gs4) */ ffe.recipe_validity_rule_id, ffe.item_id, '
||' grb.formula_id, ffe.orgn_code lorgn_code, gia.organization_id, '
||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
||' ffe.inv_max_qty, ffe.preference, gia.uom_code, '
||' som.resource_whse_code wcode , grb.routing_id, '
||' frh.routing_no, frh.routing_vers, frh.routing_desc, '
||' frh.item_um, frh.routing_qty, ' /*B2870041*/
||' DECODE(frh.item_um,gia.item_um ,1, '
||' GMICUOM.uom_conversion'||at_apps_link
||' (ffe.item_id, '
||' 0, '
||' 1, '
||' gia.item_um , ' /* primary */
||' frh.item_um , ' /* routing um */
||' 0 '
||' ) '
||' ) prd_fct, -1 prd_ind, '
||' gia.aps_item_id, grb.recipe_id, grb.recipe_no, grb.recipe_version, 0 rhdr_loc, '
||' grb.calculate_step_quantity,'
|| ' gia.category_id,NULL, '
||' gia.seq_dpnd_class '
||' 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,'
||' sy_orgn_mst'||at_apps_link||' som,'
||' gmp_item_aps'||at_apps_link||' gia,'
||' 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'''|| ') '
||' 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'''|| ') '
||' AND gs2.delete_mark = 0 '
||' AND frh.delete_mark = 0 '
||' AND ffm.delete_mark = 0 '
||' AND som.delete_mark = 0 '
||' AND frh.inactive_ind = 0 '
||' AND ffm.inactive_ind = 0 '
||' AND grb.routing_id IS NOT NULL '
||' AND ffe.orgn_code IS NOT NULL '
||' AND ffe.recipe_use IN (0,1) '
||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
||' AND ffe.orgn_code = som.orgn_code '
||' AND grb.formula_id = ffm.formula_id '
||' AND ffm.formula_status = gs3.status_code '
||' AND gs3.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ') '
||' 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'''|| ') '
||' AND gs4.delete_mark = 0 '
||' AND gia.plant_code = ffe.orgn_code '
||' AND gia.item_id = ffe.item_id '
||' AND gia.whse_code = som.resource_whse_code '
||' AND gia.replen_ind = 1 '
||' AND EXISTS ( SELECT /*+ DRIVING_SITE(fmdtl) */ 1 '
||' FROM fm_matl_dtl'||at_apps_link||' fmdtl '
||' WHERE formula_id = grb.formula_id '
||' AND line_type = 1 '
||' AND item_id = ffe.item_id ) '
||' UNION ALL '
||' SELECT /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(ffm) DRIVING_SITE(som) DRIVING_SITE(gia)'
||' DRIVING_SITE(gs1) DRIVING_SITE(gs2) DRIVING_SITE(gs3) */ ffe.recipe_validity_rule_id, ffe.item_id, '
||' grb.formula_id, ffe.orgn_code lorgn_code, gia.organization_id, '
||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
||' ffe.inv_max_qty, ffe.preference, gia.uom_code, '
||' gia.whse_code wcode , to_number(null) , '
||' NULL, to_number(null), NULL, '
||' NULL, to_number(null), to_number(null) prd_fct, -1 prd_ind, '
||' gia.aps_item_id, grb.recipe_id, grb.recipe_no, grb.recipe_version , 0 rhdr_loc, '
/* NAMIT_CR,SGIDUGU */
||' 0 calculate_step_quantity, '
||' gia.category_id,NULL, '
||' gia.seq_dpnd_class '
||' FROM gmd_recipes_b'||at_apps_link||' grb,'
||' gmd_recipe_validity_rules'||at_apps_link||' ffe,'
||' fm_form_mst'||at_apps_link||' ffm, '
||' sy_orgn_mst'||at_apps_link||' som, '
||' gmp_item_aps'||at_apps_link||' gia, '
||' 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'''|| ') '
||' 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'''|| ') '
||' AND gs2.delete_mark = 0 '
||' AND ffm.delete_mark = 0 '
||' AND som.delete_mark = 0 '
||' AND ffm.inactive_ind = 0 '
||' AND grb.routing_id IS NULL '
||' AND ffe.orgn_code IS NOT NULL '
||' AND ffe.orgn_code = som.orgn_code '
||' AND ffe.recipe_use IN (0,1) '
||' 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'''|| ') '
||' AND gs3.delete_mark = 0 '
||' AND gia.plant_code = ffe.orgn_code '
||' AND gia.item_id = ffe.item_id '
||' AND gia.whse_code = som.resource_whse_code '
||' AND gia.replen_ind = 1 '
||' AND EXISTS ( SELECT /*+ DRIVING_SITE(fmdtl) */ 1 '
||' FROM fm_matl_dtl'||at_apps_link||' fmdtl '
||' WHERE formula_id = grb.formula_id '
||' AND line_type = 1 '
||' AND item_id = ffe.item_id ) '
||' UNION ALL '
||' SELECT /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(ffm) DRIVING_SITE(frh) DRIVING_SITE(som) DRIVING_SITE(gia)'
||' DRIVING_SITE(gs1) DRIVING_SITE(gs2) DRIVING_SITE(gs3) DRIVING_SITE(gs4) */ ffe.recipe_validity_rule_id, ffe.item_id, '
||' grb.formula_id, gia.plant_code lorgn_code, gia.organization_id, '
||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
||' ffe.inv_max_qty, ffe.preference, gia.uom_code, '
||' som.resource_whse_code wcode , grb.routing_id, '
||' frh.routing_no, frh.routing_vers, frh.routing_desc, '
||' frh.item_um, frh.routing_qty,' /*B2870041*/
||' DECODE(frh.item_um,gia.item_um, 1, '
||' GMICUOM.uom_conversion'||at_apps_link
||' (ffe.item_id, '
||' 0, '
||' 1, '
||' gia.item_um , ' /* primary */
||' frh.item_um , ' /* routing um */
||' 0 '
||' ) '
||' ) prd_fct, -1 prd_ind, '
||' gia.aps_item_id, grb.recipe_id, grb.recipe_no, grb.recipe_version , 0 rhdr_loc, '
/* NAMIT_CR,SGIDUGU */
||' grb.calculate_step_quantity, '
||' gia.category_id,NULL, '
||' gia.seq_dpnd_class '
||' 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,'
||' sy_orgn_mst'||at_apps_link||' som,'
||' gmp_item_aps'||at_apps_link||' gia,'
||' 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'''|| ') '
||' 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'''|| ') '
||' AND gs2.delete_mark = 0 '
||' AND frh.delete_mark = 0 '
||' AND ffm.delete_mark = 0 '
||' AND som.delete_mark = 0 '
||' AND frh.inactive_ind = 0 '
||' AND ffm.inactive_ind = 0 '
||' AND grb.routing_id IS NOT NULL '
||' AND ffe.orgn_code IS NULL '
||' AND ffe.recipe_use IN (0,1) '
||' 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'''|| ') '
||' 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'''|| ') '
||' AND gs4.delete_mark = 0 '
||' AND gia.plant_code = som.orgn_code '
||' AND gia.item_id = ffe.item_id '
||' AND gia.whse_code = som.resource_whse_code '
||' AND gia.replen_ind = 1 '
||' AND EXISTS ( SELECT /*+ DRIVING_SITE(fmdtl) */ 1 '
||' FROM fm_matl_dtl'||at_apps_link||' fmdtl '
||' WHERE formula_id = grb.formula_id '
||' AND line_type = 1 '
||' AND item_id = ffe.item_id ) '
||' UNION ALL '
||' SELECT /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(som) DRIVING_SITE(ffm) DRIVING_SITE(gia)'
||' DRIVING_SITE(gs1) DRIVING_SITE(gs2) DRIVING_SITE(gs3) */ ffe.recipe_validity_rule_id, ffe.item_id, '
||' grb.formula_id, gia.plant_code lorgn_code, gia.organization_id, '
||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
||' ffe.inv_max_qty, ffe.preference, gia.uom_code, '
||' gia.whse_code wcode , to_number(null) , '
||' NULL, to_number(null), NULL, '
||' NULL, to_number(null), to_number(null) prd_fct, -1 prd_ind, ' /*B2870041*/
||' gia.aps_item_id, grb.recipe_id, grb.recipe_no, grb.recipe_version , 0 rhdr_loc, '
/* NAMIT_CR,SGIDUGU */
||' 0 calculate_step_quantity, '
||' gia.category_id,NULL, '
||' gia.seq_dpnd_class '
||' FROM gmd_recipes_b'||at_apps_link||' grb,'
||' gmd_recipe_validity_rules'||at_apps_link||' ffe,'
||' sy_orgn_mst'||at_apps_link||' som, '
||' fm_form_mst'||at_apps_link||' ffm, '
||' gmp_item_aps'||at_apps_link||' gia,'
||' 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'''|| ') '
||' 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'''|| ') '
||' AND gs2.delete_mark = 0 '
||' AND ffm.delete_mark = 0 '
||' AND som.delete_mark = 0 '
||' AND ffm.inactive_ind = 0 '
||' AND grb.routing_id IS NULL '
||' AND ffe.orgn_code IS NULL '
||' AND ffe.recipe_use IN (0,1) '
||' 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'''|| ') '
||' AND gs3.delete_mark = 0 '
||' AND gia.plant_code = som.orgn_code ' ;
||' AND EXISTS ( SELECT /*+ DRIVING_SITE(fmdtl) */ 1 '
||' FROM fm_matl_dtl'||at_apps_link||' fmdtl '
||' WHERE formula_id = grb.formula_id '
||' AND line_type = 1 '
||' AND item_id = ffe.item_id ) ) eff,'
||'( SELECT /*+ DRIVING_SITE(gfrme) */ plant_code, whse_code, fmeff_id, '
||' max(aps_fmeff_id) aps_fmeff_id '
||' FROM gmp_form_eff'||at_apps_link||' gfrme'
||' GROUP BY plant_code, whse_code, fmeff_id '
||' ) gfe '
-- Rajesh B8260178 code changes
||'WHERE EXISTS ( select 1 from sy_orgn_mst'||at_apps_link||' som '
||' WHERE som.orgn_code ' || l_in_str_plant
||' AND som.orgn_code = eff.lorgn_code ) '
||' AND eff.lorgn_code = gfe.plant_code (+) '
||' AND eff.wcode = gfe.whse_code (+) '
||' AND eff.recipe_validity_rule_id = gfe.fmeff_id (+) '
||' ORDER BY 4,5,6 ' ;
' SELECT /*+ DRIVING_SITE(ffm) DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(gs) */ 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, '
||' gmd_status_b'||at_apps_link||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id '
||' AND ( ffe.orgn_code is NULL or ffe.orgn_code '|| l_in_str_plant || ')' -- Bug:6156957 Vpedarla
||' 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 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;
' SELECT /*+ DRIVING_SITE (fmd) DRIVING_SITE (ffm) DRIVING_SITE(a) */ 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, '
||' decode(fmd.original_item_flag,1,fmd.qty,(( fmd.sub_replace_qty / fmd.sub_original_qty) * fmd.line_item_qty)) 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, '
||' gia.uom_code, ' --akaruppa changed gia.uom_code to msi.primary_uom_code
||' fmd.item_um , ' /*B2870041*/ --akaruppa changed fmd.item_um to fmd.detail_uom and gia.item_um to msi.primary_uom_code
/* bug: 6433328 vpedarla changed the primary_um from gia.uom_code to gia.item_um
||' gia.uom_code, ' */
||' gia.item_um, '
||' DECODE(fmd.scale_type,0,0,1,2) bom_scale_type, '
||' DECODE(fmd.item_um,gia.uom_code,decode(fmd.original_item_flag,1,fmd.qty,((fmd.sub_replace_qty / fmd.sub_original_qty) * fmd.line_item_qty)), '
||' GMICUOM.uom_conversion'||at_apps_link
||' ( fmd.item_id, '
||' 0, '
||' decode(fmd.original_item_flag,1,fmd.qty,((fmd.sub_replace_qty / fmd.sub_original_qty) * fmd.line_item_qty)), '
||' fmd.item_um , '
||' gia.item_um , '
||' 0)) primary_qty, '
||' gia.aps_item_id, '
||' fmd.scale_multiple, '
||' (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.line_item_id, '
||' fmd.start_date, '
||' fmd.end_date, '
||' fmd.formulaline_id formula_line_id , '
||' fmd.preference , '
||' null actual_end_date ,'
||' 0 actual_end_flag ,'
||' fmd.sub_original_qty ,' -- venu
-- ||' fmd.sub_replace_qty ,' -- venu
-- ||' fmd.replacement_uom , ' -- venu
||' fmd.original_item_flag , '
||' fmd.formulaline_id formula_line_id '
||' FROM gmd_material_effectivities_vw'||at_apps_link||' fmd,'
||' fm_form_mst'||at_apps_link||' ffm, '
||' (SELECT a.item_id, a.aps_item_id, a.item_um, a.uom_code '
||' FROM (SELECT item_id, aps_item_id, item_um, uom_code, '
||' ROW_NUMBER() OVER ( PARTITION BY item_id ORDER BY item_id,aps_item_id ) AS first_row '
||' FROM gmp_item_aps ' ||at_apps_link || ' ) a where a.first_row = 1 ) gia '
||' WHERE gia.item_id = fmd.item_id '
||' AND ffm.formula_id = fmd.formula_id '
||' AND ffm.formula_id IN ( select /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(gs) */ unique grb.formula_id ' -- -- Bug:6156957 Vpedarla add this where condition
||' FROM gmd_recipes_b '||at_apps_link ||' grb, '
||' gmd_recipe_validity_rules '||at_apps_link ||' ffe, '
||' gmd_status_b '||at_apps_link ||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id '
||' AND ( ffe.orgn_code is NULL or ffe.orgn_code '|| l_in_str_plant || ')'
||' 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 '
||' AND nvl(fmd.qty,fmd.sub_replace_qty) <> 0' -- venu added
||' 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, '
||' fmd.original_item_flag desc,fmd.start_date,fmd.preference ';
/* insert processed substitutes now */
FOR k in 1..substcount
LOOP
fd_size := fd_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
fd_size := fd_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 ;
subst_tab.delete;
prev_detail_tab.delete;
orig_detail_tab.delete;
rtg_offset_cur_stmt := ' SELECT '||
' gro.plant_code, '||
' 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.plant_code, rsm.formulaline_id ' ;
' SELECT /*+ DRIVING_SITE(som) DRIVING_SITE(frh) DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(gs) */ unique frh.routing_id, som.orgn_code, '
/* 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 sy_orgn_mst'||at_apps_link||' som, '
||' fm_rout_hdr'||at_apps_link||' frh, '
||' gmd_recipes_b'||at_apps_link||' grb, '
||' gmd_recipe_validity_rules'||at_apps_link||' ffe, '
||' gmd_status_b'||at_apps_link||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id '
||' AND som.orgn_code '|| l_in_str_plant -- Bug:6156957 Vpedarla
||' 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 frh.routing_id = grb.routing_id '
||' AND som.delete_mark = 0 '
||' AND som.resource_whse_code is NOT NULL '
||' AND nvl(ffe.orgn_code, som.orgn_code) = som.orgn_code' ;
||' AND EXISTS ( SELECT 1 FROM gmp_item_aps'||at_apps_link||' gia '
||' WHERE gia.whse_code = som.resource_whse_code )' ;
||' AND ffe.delete_mark = 0 '
||' AND frh.delete_mark = 0 '
||' AND frh.inactive_ind = 0 '
||' ORDER BY frh.routing_id, som.orgn_code ' ;
/* 2582849 minimum_transfer_qty selected */
-- Bug: 6030499 Vpedarla conditinalised the following code.
IF collect_ps_data THEN
/* Select Seq Dep rows SGIDUGU
Construct PL/Sql table used in bsearch
Select Setup id rows for oprn_id <> -1 SGIDUGU */
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 ' ;
/* End of Select Seq Dep rows SGIDUGU */
--
END IF; -- Bug: 6030499 Vpedarla end of condition code.
' SELECT /*+ DRIVING_SITE(sou) DRIVING_SITE(sou2) DRIVING_SITE(crd) DRIVING_SITE(frd) DRIVING_SITE(fom) DRIVING_SITE(goa) DRIVING_SITE(gor) */ frd.routing_id, '
||' crd.orgn_code, '
||' 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, '
||' sou2.uom_code,'
||' goa.activity, '
||' goa.oprn_line_id, '
||' gor.resource_count, '
||' gor.resource_usage, '
||' 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,1) , ' /* B2967464 */
||' DECODE(gor.scale_type,0,2,1,1,2,3) , ' /* B2967464 */
||' sou.uom_code, '
||' goa.activity_factor, '
||' gor.process_qty, '
-- ||' NVL(goa.sequence_dependent_ind,0), '
||' NVL(goa.material_ind,0), '
||' 1 , '
|| ' SUM(NVL(goa.material_ind,0)) OVER (PARTITION BY '
|| ' frd.routing_id, crd.orgn_code, 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, '
||' (SUM(DECODE(NVL(goa.sequence_dependent_ind,0),1,1,0)) OVER '
||' (PARTITION BY '
||' frd.routing_id, crd.orgn_code)) 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 sy_uoms_mst'||at_apps_link||' sou, '
||' sy_uoms_mst'||at_apps_link||' sou2, '
||' 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 '
||' WHERE frd.routing_id IN ( select /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(gs) */ unique grb.routing_id ' -- Bug:6156957 Vpedarla added this where condition
||' FROM gmd_recipes_b '||at_apps_link||' grb, '
||' gmd_recipe_validity_rules '||at_apps_link||' ffe, '
||' gmd_status_b '||at_apps_link||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id '
||' AND ( ffe.orgn_code is NULL or ffe.orgn_code '|| l_in_str_plant || ')'
||' 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 frd.oprn_id = fom.oprn_id '
||' AND fom.oprn_id = goa.oprn_id '
||' AND goa.oprn_line_id = gor.oprn_line_id '
||' AND crd.resources = gor.resources '
||' AND sou.um_code = gor.usage_um '
||' AND sou2.um_code = fom.process_qty_um '
||' AND sou.delete_mark = 0 '
||' AND fom.delete_mark = 0 '
||' AND goa.activity_factor > 0 '
||' AND sou.um_type = :gmp_uom_class '
||' 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 <> rtg_org_dtl_tab(ri).orgn_code 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(sou) */ '
||' frd.routing_id, '
||' frd.routingstep_no, '
/* NAMIT_RD */
||' 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, '
||' decode(gor.prim_rsrc_ind,1,1,2), ' -- Bug: 6030499 Vpedarla ||' gor.prim_rsrc_ind, '
-- ||' gor.resources, '
-- ||' decode(gor.prim_rsrc_ind,1,1,2) prim_rsrc_ind, '
-- ||' NVL(goa.sequence_dependent_ind,0), '
||' goa.offset_interval, '
/* NAMIT_RD */
||' sou.uom_code '
||' 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, '
/* NAMIT_RD */
||' sy_uoms_mst'||at_apps_link||' sou '
||' WHERE frd.routing_id IN ( select /*+ DRIVING_SITE(grb) DRIVING_SITE(ffe) DRIVING_SITE(gs) */ unique grb.routing_id ' -- Bug:6156957 Vpedarla added this where condition
||' FROM gmd_recipes_b '||at_apps_link||' grb, '
||' gmd_recipe_validity_rules '||at_apps_link||' ffe, '
||' gmd_status_b '||at_apps_link||' gs '
||' WHERE grb.recipe_id = ffe.recipe_id '
||' AND ( ffe.orgn_code is NULL or ffe.orgn_code '|| l_in_str_plant || ')'
||' 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 frd.oprn_id = fom.oprn_id '
||' AND fom.oprn_id = goa.oprn_id '
/* NAMIT_RD */
-- ||' AND gor.prim_rsrc_ind in (1,2) '
||' AND fom.delete_mark = 0'
||' AND goa.oprn_line_id = gor.oprn_line_id '
/* NAMIT_RD */
||' AND sou.um_code = gor.usage_um '
||' AND sou.delete_mark = 0 '
||' AND sou.um_type = :gmp_uom_class '
/* NAMIT_RD */
||' ORDER BY 1, 2, 3, 4, 5 ' ;
recipe_orgn_statement := ' SELECT '
||' grb.routing_id, gc.orgn_code, '
||' 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, '
||' ( '
||' SELECT /*+ DRIVING_SITE(goa) DRIVING_SITE(gor) */'
||' gor.recipe_id, '
||' gor.orgn_code, '
||' 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.orgn_code = goa.orgn_code '
||' AND gor.oprn_line_id = goa.oprn_line_id '
||' AND gor.routingstep_id = goa.routingstep_id '
||' UNION ALL '
||' SELECT /*+ DRIVING_SITE(goa) DRIVING_SITE(gor) */ goa.recipe_id, '
||' goa.orgn_code, '
||' 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 1 '
||' FROM gmd_recipe_orgn_resources'||at_apps_link||' gor '
||' WHERE gor.recipe_id = goa.recipe_id '
||' AND gor.orgn_code = goa.orgn_code '
||' AND gor.oprn_line_id = goa.oprn_line_id '
||' AND gor.routingstep_id = goa.routingstep_id ) '
||' UNION ALL '
||' SELECT /*+ DRIVING_SITE(goa) DRIVING_SITE(gor) */ gor.recipe_id, '
||' gor.orgn_code, '
||' 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 1 '
||' FROM gmd_recipe_orgn_activities'||at_apps_link||' goa'
||' WHERE goa.recipe_id = gor.recipe_id '
||' AND goa.orgn_code = gor.orgn_code '
||' AND goa.oprn_line_id = gor.oprn_line_id '
||' AND goa.routingstep_id = gor.routingstep_id ) '
||' ) gc, '
||' gmd_status_b'||at_apps_link||' gs1 '
||' WHERE grb.recipe_id = gc.recipe_id '
||' AND grb.delete_mark = 0 '
||' AND grb.recipe_status = gs1.status_code '
||' AND gs1.status_type IN (' ||'''700''' || ',' || '''900''' || ') '
||' AND gs1.delete_mark = 0 '
||' ORDER BY 1,2,3,4,5 ' ;
' SELECT /*+ DRIVING_SITE(grb) DRIVING_SITE(grs) DRIVING_SITE(gs1) */ '
||' grb.routing_id, grs.routingstep_id, grs.recipe_id, '
||' grs.step_qty '
||' FROM gmd_recipes'||at_apps_link||' grb, '
||' gmd_recipe_routing_steps'||at_apps_link||' grs, '
||' gmd_status_b'||at_apps_link||' gs1 '
||' WHERE grb.recipe_id = grs.recipe_id '
||' AND grb.delete_mark = 0 '
||' AND grb.recipe_status = gs1.status_code '
||' AND gs1.status_type IN (' ||'''700''' || ',' || '''900''' || ') '
||' AND gs1.delete_mark = 0 '
||' ORDER BY 1,2,3 ' ;
/* Alternate Resource selection */
/* B5688153, Rajesh Patangya prod spec alt*/
statement_alt_resource :=
' SELECT /*+ DRIVING_SITE(acrd) DRIVING_SITE(pcrd) DRIVING_SITE(cam) DRIVING_SITE(prod) */ '
||' pcrd.resource_id, acrd.resource_id, '
||' acrd.min_capacity, acrd.max_capacity, '
||' cam.runtime_factor, '
/*prod spec alt*/ ||' nvl(cam.preference,-1), nvl(prod.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.orgn_code = pcrd.orgn_code '
||' AND cam.primary_resource = prod.primary_resource(+) '
||' AND cam.alternate_resource = prod.alternate_resource(+) '
||' AND acrd.delete_mark = 0 '
||' ORDER BY pcrd.resource_id, '
||' DECODE(cam.preference,NULL,cam.runtime_factor,cam.preference),'
||' prod.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
log_message('before link_routing') ;
' SELECT formula_id, '
||' recipe_id, '
||' line_type, '
||' line_no, '
||' x_formulaline_id, '
||' x_routingstep_id, '
||' item_id, '
||' routingstep_no, '
||' aps_item_id, '
||' DECODE(fmd_item_um, gia_item_um, 1, GMICUOM.uom_conversion'||at_apps_link||' (item_id, 0, 1, fmd_item_um, gia_item_um, 0)) uom_conv_factor, '
||' minimum_transfer_qty, '
||' minimum_delay, '
||' maximum_delay '
||' FROM ( '
||' SELECT DISTINCT '
||' /*+ DRIVING_SITE(r) DRIVING_SITE(frm) DRIVING_SITE(fmd) DRIVING_SITE(frd) DRIVING_SITE(gia) */ '
||' fmd.formula_id, '
||' frm.recipe_id, '
||' DECODE(fmd.line_type, 1,1,2,2,-1,3) line_type, '
||' fmd.line_no, '
||' ((frm.formulaline_id * 2) + 1) x_formulaline_id, '
||' ((frm.routingstep_id * 2) + 1) x_routingstep_id, '
||' fmd.item_id, '
||' frd.routingstep_no, '
||' gia.aps_item_id, '
||' 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, '
||' fmd.item_um fmd_item_um, '
||' gia.item_um gia_item_um '
||' FROM gmd_recipes_b'||at_apps_link||' r ,'
||' gmd_recipe_step_materials'||at_apps_link||' frm, '
||' fm_matl_dtl'||at_apps_link||' fmd, '
||' fm_rout_dtl'||at_apps_link||' frd, '
||' gmp_item_aps'||at_apps_link||' gia '
||' WHERE frm.formulaline_id = fmd.formulaline_id '
||' AND frm.formulaline_id LIKE ''%'' '
||' AND frm.recipe_id = r.recipe_id '
||' AND frm.recipe_id LIKE ''%'' '
||' AND EXISTS '
||' ( SELECT /*+ DRIVING_SITE(ffe) DRIVING_SITE(gs) */ 1 '
||' FROM gmd_recipe_validity_rules'||at_apps_link||' ffe, '
||' gmd_status_b'||at_apps_link||' gs '
||' WHERE ffe.recipe_id = r.recipe_id '
||' AND ( ffe.orgn_code is NULL or ffe.orgn_code '|| l_in_str_plant || ')'
||' AND ffe.validity_rule_status = gs.status_code '
||' AND gs.status_type IN (' ||'''700''' || ',' || '''900''' || ') '
||' AND gs.delete_mark = 0 '
||' AND ffe.recipe_use IN (0,1) '
||' AND NVL(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
||' AND ffe.delete_mark = 0 '
||' ) '
||' AND (fmd.release_type in (1,2,3) '
||' OR NVL(r.calculate_step_quantity,0) = 1 ) '
||' AND frd.routingstep_id = frm.routingstep_id '
||' AND gia.item_id = fmd.item_id '
||' ) '
||' ORDER BY 1,2,3,6,7 ';
'SELECT '
||' GMICUOM.uom_conversion'||at_apps_link
||' (:pitem, 0, :pqty, :pfrom_um, :pto_um, 0) '
||'FROM dual';
/* venu 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('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' ) ;
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 ;
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 /*+ DRIVING_SITE(fmd) DRIVING_SITE(ffm) DRIVING_SITE(gia) DRIVING_SITE(som) */ fmd.formula_id, gia.plant_code, '
||' gia.organization_id, count(*), 0 '
||' FROM fm_matl_dtl'||at_apps_link||' fmd, '
||' fm_form_mst'||at_apps_link||' ffm, '
||' gmp_item_aps'||at_apps_link||' gia, '
||' sy_orgn_mst'||at_apps_link||' som '
||' WHERE ffm.formula_id = fmd.formula_id '
||' AND ffm.delete_mark = 0 '
||' AND fmd.qty <> 0 ' /* 2362810 Voltek Fix */
||' AND fmd.item_id = gia.item_id '
||' AND gia.plant_code = som.orgn_code ' ;
' SELECT /*+ DRIVING_SITE(fmd) DRIVING_SITE(ffm) */ 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 ' ;
REM| inserts - B2989806 |
REM+=========================================================================+
*/
PROCEDURE write_process_effectivity
(
p_x_aps_fmeff_id IN NUMBER,
p_aps_fmeff_id IN NUMBER,
return_status OUT NOCOPY BOOLEAN
)
IS
statement_form_eff VARCHAR2(32700) ;
'INSERT INTO gmp_form_eff'||at_apps_link
||' ( '
||' aps_fmeff_id,whse_code,plant_code,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,:p10)';
/* 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 */
bomc_index := bomc_index + 1 ;
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.aps_item_id = formula_detail_tab(loop_index).aps_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 ;
select count(*)
from all_tab_columns
where owner = p_owner
and table_name = 'MSC_PLANS'
and column_name = p_column_name;
select application_short_name
into lv_msc_schema
from fnd_application
where application_id = 724;
insert_statement VARCHAR2(32700);
last_update_date DATE,
last_updated_by PLS_INTEGER,
last_update_login PLS_INTEGER,
experimental_ind PLS_INTEGER); /* Bug # 5238790 */
retrieval_cursor := 'DELETE FROM gmp_item_aps'||at_apps_link;
'INSERT INTO gmp_item_aps'||at_apps_link||' '
||' ( '
||' item_no, '
||' item_id,category_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, '
||' experimental_ind ' /* Bug # 5238790 */
||' ) '
||' SELECT '
||' /*+ DRIVING_SITE(iim) DRIVING_SITE(sou) DRIVING_SITE(pwe) '
||' DRIVING_SITE(iwm) DRIVING_SITE(msi)'
||' DRIVING_SITE(mum) DRIVING_SITE(som) */ '
||' iim.item_no, '
||' iim.item_id,nvl(iim.seq_category_id,-1), '
||' iim.seq_dpnd_class , '
||' iim.item_um, mum.uom_code, '
||' iim.lot_ctl, substr(iim.item_desc1,1,69) 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, :r_dt1, iim.created_by, '
||' :r_dt2,iim.last_updated_by, 0, '
||' NVL(iim.experimental_ind,0) ' /* Bug # 5238790 */
||' 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 '
||' 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.last_updated_by, 0, '
||' iim.experimental_ind ' ;
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| 10/13/99 - Added deleted_flag in the insert statement |
REM| 04/21/2004 - Navin Sinha - B3577871 -ST:OSFME2: collections failing |
REM| in planning data pull. |
REM| Return return_status as TRUE in case |
REM| no_warehouses Exception is raised. |
REM| |
REM+=========================================================================+
*/
PROCEDURE extract_sub_inventory
(
at_apps_link IN VARCHAR2,
instance IN INTEGER,
run_date IN DATE,
return_status IN OUT NOCOPY BOOLEAN
)
IS
c_whse_cursor ref_cursor_typ;
' SELECT iwm.whse_code, iwm.mtl_organization_id '
||' FROM ic_whse_mst'||at_apps_link||' iwm '
||' WHERE iwm.delete_mark = 0 AND '
||' iwm.mtl_organization_id IS NOT NULL ';
INSERT INTO msc_st_sub_inventories
(
sub_inventory_code,
organization_id,
netting_type,
sr_instance_id,
deleted_flag,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES
(
whse_code,
organization_id,
1,
instance,
2,
run_date,
0,
run_date,
0
);
/* 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,'DD-MON-RRRR 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,
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,
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,
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),
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),
null_value,
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: 6064590 Vpedarla 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),
opr_last_update_date(i),
0,
opr_creation_date(i),
0,
itm_mtq_frm_op_seq_num(i),
100 -- Bug: 6064590 Vpedarla 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: 6064590 Vpedarla 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: 6064590 Vpedarla
opr_last_update_date(i),
0,
opr_creation_date(i),
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)
);
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 ;
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 ;
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 ;
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 ;
pef_process_sequence_id.delete ;
pef_item_id.delete ;
pef_disable_date.delete ;
pef_minimum_quantity.delete ;
pef_maximum_quantity.delete ;
pef_preference.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;
bom_sr_instance_id.delete;
bom_op_seq_number.delete;
bomc_sr_instance_id.delete;
bomc_contribute_to_step_qty.delete;
bomc_disable_date.delete;
rtg_sr_instance_id.delete;
rtg_auto_step_qty_flag.delete;
or_sr_instance_id.delete;
or_minimum_capacity.delete;
or_maximum_capacity.delete;
or_setup_id.delete;
or_orig_rs_seq_num.delete;
or_break_ind.delete;
opr_sr_instance_id.delete;
opr_step_qty.delete;
opr_step_qty_uom.delete;
oc_sr_instance_id.delete;
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| 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/02/2004 Sridhar Gidugu created |
REM| 0519/06 Rewrite for SDS |
REM| MSC_RESOURCE_SETUPS unique key is ON |
REM| Instance_id,resource_id,organization_id and setup_id |
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.mtl_organization_id, '
||' a.seq_dep_id, '
||' b.seq_dep_id, '
||' 0 setup_time, '
||' 0 penalty_factor, '
||' :profile, '
||' :instance1 , '
||' 2 '
||' FROM ( '
||' SELECT '
||' iwm.mtl_organization_id, '
||' s.category_id, '
||' s.seq_dep_id, '
||' o.oprn_id, '
||' rd.resource_id, '
||' count(o.oprn_id) OVER (PARTITION BY rd.orgn_code,s.category_id,rd.resource_id) CNT '
||' FROM '
||' ic_whse_mst'||at_apps_link||' iwm, '
||' sy_orgn_mst'||at_apps_link||' sy, '
||' 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 rd.orgn_code = sy.orgn_code '
||' AND sy.resource_whse_code = iwm.whse_code '
||' 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.mtl_organization_id, '
||' s.category_id, '
||' s.seq_dep_id, '
||' o.oprn_id, '
||' rd.alt_resource_id, '
||' count(o.oprn_id) OVER (PARTITION BY rd.mtl_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, '
||' iwm.mtl_organization_id '
||' FROM ic_whse_mst'||at_apps_link||' iwm, '
||' sy_orgn_mst'||at_apps_link||' sy, '
||' 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.orgn_code = pcrd.orgn_code '
||' AND acrd.orgn_code = sy.orgn_code'
||' AND sy.resource_whse_code = iwm.whse_code ' ;
||' 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 '
||' iwm.mtl_organization_id, '
||' s.category_id, '
||' s.seq_dep_id, '
||' o.oprn_id, '
||' rd.resource_id, '
||' count(o.oprn_id) OVER (PARTITION BY rd.orgn_code,s.category_id,rd.resource_id) CNT '
||' FROM '
||' ic_whse_mst'||at_apps_link||' iwm, '
||' sy_orgn_mst'||at_apps_link||' sy, '
||' 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 rd.orgn_code = sy.orgn_code '
||' AND sy.resource_whse_code = iwm.whse_code '
||' 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.mtl_organization_id, '
||' s.category_id, '
||' s.seq_dep_id, '
||' o.oprn_id, '
||' rd.alt_resource_id, '
||' count(o.oprn_id) OVER (PARTITION BY rd.mtl_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, '
||' iwm.mtl_organization_id '
||' FROM ic_whse_mst'||at_apps_link||' iwm, '
||' sy_orgn_mst'||at_apps_link||' sy, '
||' 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.orgn_code = sy.orgn_code '
||' AND sy.resource_whse_code = iwm.whse_code '
||' AND acrd.orgn_code = pcrd.orgn_code ' ;
||' 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.mtl_organization_id = b.mtl_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 '
||' 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 unique RESOURCE_ID, ORGANIZATION_ID,'
||' setup_id , deleted_flag, sr_instance_id, uom_code '
||' from msc_st_operation_resources '
||' WHERE sr_instance_id = :instance1 '
||' and setup_id is not null ) b '
||' WHERE ( b.setup_id = a.from_seq_dep_id OR '
||' b.setup_id = a.to_seq_dep_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 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 ) ' ;
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 NUMBER
)
IS
stpdep_start_index INTEGER;
'SELECT fnd_profile.VALUE'||pdblink||'('''||profile_name||''')'||' FROM dual ';
* Inserts Data into step charge staging table.
* HISTORY
* B4761946, 20-DEC-2005 Rajesh Patangya Changed the while loop logic
************************************************************************/
PROCEDURE inst_stp_chg_tbl(pinstance_id IN NUMBER, p_batch_loc IN NUMBER)
IS
rsrc_chg_loc NUMBER;
row_count := 1; /* NAVIN :- Maintains the row count. From set of repetitive rows, only one row is inserted. */
select NVL(max(process_seq_id),0) into l_process_seq_id from msc_st_supplies
where sr_instance_id = pinstance_id ;
' select um_type '
||' from sy_uoms_mst'||pdblink
||' where um_code = :gmp_um_code ';
v_prod_cursor := 'SELECT /*+ DRIVING_SITE(h) DRIVING_SITE(d) DRIVING_SITE(gbsi) DRIVING_SITE(gbs) '
||' DRIVING_SITE(v) DRIVING_SITE(iwm) DRIVING_SITE(t) DRIVING_SITE(i) */ '
|| ' h.batch_no,'
|| ' h.plant_code,'
|| ' h.batch_id,'
|| ' ((h.batch_id * 2) + 1), '
|| ' h.wip_whse_code,'
|| ' iwm.mtl_organization_id, '
|| ' h.routing_id,'
|| ' h.plan_start_date, '
|| ' h.plan_cmplt_date end_date,'
|| ' DECODE(d.line_type,-1,MIN(t.trans_date),MAX(t.trans_date)),'
|| ' h.batch_status,'
|| ' h.batch_type,'
|| ' i.organization_id,'
|| ' t.whse_code,'
|| ' i.aps_item_id,'
|| ' d.material_detail_id,'
|| ' d.line_no ,' /* B2919303 */
|| ' DECODE(d.item_id ,v.item_id,0,d.line_no) t_line_no,' /* B2953953 */
|| ' d.line_type,'
|| ' DECODE(d.line_type,1,3,d.line_type) t_line_type,' /* B2953953 */
|| ' SUM(t.trans_qty),'
|| ' d.item_id matl_item_id,'
|| ' v.item_id recipe_item_id, '
|| ' h.poc_ind, '
|| ' DECODE(h.firmed_ind,1,1,2), '
|| ' decode(d.release_type,0, -1, nvl(gbs.batchstep_no,-1)) batchstep_no,'
|| ' d.plan_qty, '
|| ' DECODE(d.item_um, i.item_um, 1, '
|| ' GMICUOM.uom_conversion'||pdblink
|| ' (d.item_id, 0, 1, d.item_um, i.item_um, 0)), ' /* NAVIN: Get UOM
conversion factor for unit qty */
|| ' h.due_date,'
|| ' h.order_priority,'
-- || ' gbsi.batchstep_id,'
||' ((gbsi.batchstep_id*2)+1) from_op_seq_id, ' /* Bug:6030499 Vpedarla Back Porting of Bug: 5461922 */
|| ' DECODE(d.line_type,1,nvl(gbsi.minimum_transfer_qty,gbs.minimum_transfer_qty), NULL) t_minimum_transfer_qty,'
/* Bug: 6327356 vpedarla changed the column expression for mtq.
|| ' DECODE(d.line_type,1,gbsi.minimum_transfer_qty, NULL) t_minimum_transfer_qty,' */
|| ' DECODE(d.line_type,1,gbsi.minimum_delay, NULL) t_minimum_delay, '
|| ' DECODE(d.line_type,1,gbsi.maximum_delay, NULL) t_maximum_delay,'
|| ' gbs.batchstep_no'
|| ' FROM'
|| ' gme_batch_header'||pdblink||' h,'
|| ' gme_material_details'||pdblink||' d,'
|| ' gme_batch_step_items'||pdblink||' gbsi,' /* 2919303 */
|| ' gme_batch_steps'||pdblink||' gbs,' /* 2919303 */
|| ' gmd_recipe_validity_rules'||pdblink||' v,'
|| ' ic_whse_mst'||pdblink||' iwm,'
|| ' ic_tran_pnd'||pdblink||' t,'
|| ' gmp_item_aps'||pdblink||' i'
|| ' WHERE'
|| ' h.batch_id = d.batch_id'
|| ' AND h.recipe_validity_rule_id = v.recipe_validity_rule_id'
|| ' AND EXISTS (SELECT 1 '
|| ' FROM '
|| ' gme_material_details'||pdblink||' gmd '
|| ' WHERE '
|| ' gmd.batch_id = h.batch_id '
|| ' AND gmd.item_id = v.item_id '
|| ' AND gmd.line_type = 1 ) ' /* B11794865 */
|| ' AND h.wip_whse_code = iwm.whse_code'
|| ' AND h.batch_id = t.doc_id'
|| ' AND ((h.batch_type = 0 and t.doc_type = :p1) OR'
|| ' (h.batch_type = 10 and t.doc_type = :p2))'
|| ' AND d.material_detail_id = gbsi.material_detail_id (+)' /* 2919303 */
|| ' AND d.batch_id = gbsi.batch_id (+) ' /* 2919303 */
|| ' AND gbsi.batch_id = gbs.batch_id (+) ' /* 2919303 */
|| ' AND gbsi.batchstep_id = gbs.batchstep_id (+)' /* 2919303 */
|| ' AND d.material_detail_id = t.line_id'
|| ' AND t.item_id = i.item_id'
|| ' AND t.whse_code = i.whse_code'
|| ' AND t.orgn_code = i.plant_code'
|| ' AND h.batch_status in (1, 2)'
|| ' AND t.completed_ind = 0'
|| ' AND t.delete_mark = 0'
-- B3721336 Rajesh Patangya If product is 100% Yeiled, But steps are pending
-- || ' AND t.trans_qty <> 0'
|| ' GROUP BY'
|| ' h.batch_no,'
|| ' h.plant_code,'
|| ' h.batch_id,'
|| ' h.wip_whse_code,'
|| ' iwm.mtl_organization_id, '
|| ' h.routing_id,'
|| ' h.plan_start_date,'
|| ' h.plan_cmplt_date,'
|| ' v.item_id,'
|| ' h.poc_ind,'
|| ' h.firmed_ind,'
|| ' decode(d.release_type, 0, -1, nvl(gbs.batchstep_no,-1)),' /*2919303*/
|| ' d.item_id,'
|| ' h.batch_status,'
|| ' h.batch_type,'
|| ' i.organization_id,'
|| ' t.whse_code,'
|| ' i.aps_item_id,'
|| ' d.material_detail_id,'
|| ' d.line_no,'
|| ' d.line_type,'
/* NAVIN: Added following columns in group by as these are newly added in the select clouse.*/
|| ' d.plan_qty, '
|| ' DECODE(d.item_um, i.item_um, 1, GMICUOM.uom_conversion' || pdblink
|| ' (d.item_id, 0, 1, d.item_um, i.item_um, 0)), '
|| ' h.due_date,'
|| ' h.order_priority,'
|| ' gbsi.batchstep_id,'
|| ' DECODE(d.line_type,1,nvl(gbsi.minimum_transfer_qty,gbs.minimum_transfer_qty), NULL) ,'
|| ' DECODE(d.line_type,1,gbsi.minimum_delay, NULL), '
|| ' DECODE(d.line_type,1,gbsi.maximum_delay, NULL),'
|| ' gbs.batchstep_no'
|| ' ORDER BY h.batch_id ,t_line_type DESC ,t_line_no , '
|| ' DECODE(d.line_type,-1,MIN(t.trans_date),MAX(t.trans_date)) DESC ';
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');
v_rsrc_cursor := 'SELECT /*+ DRIVING_SITE(uom) DRIVING_SITE(uom2) DRIVING_SITE(h) DRIVING_SITE(r) '
||' DRIVING_SITE(o) DRIVING_SITE(gs) DRIVING_SITE(t) DRIVING_SITE(gri) DRIVING_SITE(c) */ '
|| ' h.batch_id,'
|| ' ((r.batch_id * 2) + 1), '
|| ' r.batchstep_no,'
|| ' NVL(o.sequence_dependent_ind, -1),' /* NAVIN: Moved this column up for order by clause and changed from NVL(o.sequence_dependent_ind,0) */
|| ' DECODE(gs.prim_rsrc_ind, 1,1,2,2,0,3),' /* This will ensure that ordering will always have primary first */
|| ' gs.resources,'
|| ' ((gri.instance_id * 2) + 1) , ' /* SOWMYA - Resources Instances */
|| ' NVL(t.sequence_dependent_ind,0), '
|| ' gs.plan_start_date,'
|| ' h.plant_code,'
-- || ' o.activity,' /* NAVIN: Remove this column. */
|| ' gs.prim_rsrc_ind,'
|| ' c.resource_id,'
|| ' ((c.resource_id * 2) + 1),'
|| ' gs.plan_rsrc_count,'
|| ' gs.actual_rsrc_count,'
|| ' gs.actual_start_date,'
|| ' gs.plan_cmplt_date,'
|| ' gs.actual_cmplt_date,'
-- || ' DECODE(r.step_status,2,1,NULL), '
|| ' r.step_status, ' /* B3995361 */
|| ' SUM(t.resource_usage) OVER (PARTITION BY t.doc_id, t.resources, t.line_id) resource_usage, ' -- summarized usage for the step resource
|| ' SUM(t.resource_usage) OVER (PARTITION BY t.doc_id, t.resources, t.line_id, t.instance_id) resource_instance_usage, ' -- summarized usage for the step resource instances
|| ' nvl(gri.eqp_serial_number,to_char(gri.instance_number)), ' /* SOWMYA - As Per latest FDD changes - Resources Instances */
|| ' DECODE(gs.scale_type,0,2,1,1,2,3), '
|| ' c.capacity_constraint , '
|| ' r.plan_step_qty, '
|| ' NVL(r.minimum_transfer_qty,-1), '
|| ' NVL(o.material_ind,0), '
|| ' 1 schedule_flag, '
-- || ' o.offset_interval, ' /* NAVIN: Remove this column. */
|| ' o.plan_start_date, '
|| ' (DECODE(c.utilization,0,100,NVL(c.utilization,100))/100) * '
|| ' (DECODE(c.efficiency,0,100,NVL(c.efficiency,100))/100), '
|| ' o.batchstep_activity_id, '
|| ' gs.group_sequence_id,'
|| ' gs.group_sequence_number,'
|| ' nvl(gs.firm_type,0),' /*Sowmya - If null then pass 0*/
|| ' gs.sequence_dependent_id setup_id,'
-- In the situation that value of calculate_charges at Step Resource has been
-- set to 0 or NULL the values will need to be adjusted for min and max capacity
-- at the resource level. min capacity will be set to 0 and the max capacity
-- will be set to 99999999999999999
|| ' DECODE(NVL(gs.calculate_charges,0), 0, 0, gs.min_capacity) t_min_capacity,'
|| ' DECODE(NVL(gs.calculate_charges,0), 0, 99999999999999999, gs.max_capacity) t_max_capacity,'
|| ' gs.sequence_dependent_usage, '
|| ' gs.batchstep_resource_id,'
/* NAVIN: for calculating WIP Charges */
|| ' r.step_status, '
|| ' r.plan_charges,'
|| ' gs.plan_rsrc_usage,'
-- Bug: 6925112 Vpedarla modified the actual_rsrc_usage column inserted a NVl funtion
|| ' nvl(gs.actual_rsrc_usage,0) actual_rsrc_usage,'
-- || ' r.batchstep_id,' /* Navin 6/23/2004 Added for resource charges*/
|| ' ((r.batchstep_id*2)+1),' /* bug: 6030499 Vpedarla back porting of bug: 5461922 */
|| ' SUM(NVL(o.material_ind,0)) OVER (PARTITION BY '
|| ' o.batch_id, r.batchstep_id) mat_found, '
-- OPM break_ind values 0 and NULL maps to value 2 of MSC breakable_activity_flag
-- and 1 maps with 1.
|| ' DECODE(NVL(o.break_ind,0), 1, 1, 2) breakable_activity_flag , '
|| ' uom.uom_code ,'
|| ' uom2.uom_code ,'
|| ' gri.equipment_item_id ,' /* SOWMYA - As Per latest FDD changes */
|| ' gs.plan_rsrc_count gmd_rsrc_count,' /*passed on msc_st_resource_requirements*/
|| ' r.plan_start_date, ' /* populate msc_st_job_operations.reco_start_date */
|| ' r.plan_cmplt_date ' /* populate msc_st_job_operations.reco_completion_date */
|| ' FROM'
--Bug 9760218. Use sy_uoms_mst instead of mtl_units_of_measure
-- || ' mtl_units_of_measure'||pdblink||' uom, '
-- || ' mtl_units_of_measure'||pdblink||' uom2, '
|| ' sy_uoms_mst'||pdblink||' uom, '
|| ' sy_uoms_mst'||pdblink||' uom2, '
|| ' gme_batch_header'||pdblink||' h,'
|| ' gme_batch_steps'||pdblink||' r,'
|| ' gme_batch_step_activities'||pdblink||' o,'
|| ' gme_batch_step_resources'||pdblink||' gs,'
|| ' gme_resource_txns'||pdblink||' t , '
|| ' gmp_resource_instances'||pdblink||' gri, '
|| ' cr_rsrc_dtl'||pdblink||' c'
|| ' WHERE'
|| ' h.batch_id = r.batch_id '
|| ' AND r.batch_id = o.batch_id'
|| ' AND r.batchstep_id = o.batchstep_id'
|| ' AND o.batchstep_activity_id = gs.batchstep_activity_id'
|| ' AND o.batch_id = t.doc_id'
|| ' AND gs.batchstep_resource_id = t.line_id'
|| ' AND t.completed_ind = 0 '
|| ' AND NVL(t.sequence_dependent_ind,0) = 0 ' /* B4900503, Rajesh Patangya */
|| ' AND t.delete_mark = 0 '
|| ' AND t.instance_id = gri.instance_id (+) '
|| ' AND nvl(gri.inactive_ind,0) = 0 '
|| ' AND c.orgn_code = h.plant_code '
|| ' AND c.resources = gs.resources'
/*B4313202 COLLECTING DATA FOR COMPLETED OPERATIONS:Included a chk for step status = 3*/
|| ' AND r.step_status in (1, 2, 3)'
|| ' AND c.Schedule_Ind <> 3 ' /* NAVIN: gs.prim_rsrc_ind in (1,2) */
--Bug 9760218.
-- || ' AND uom.uom_class = :gmp_uom_class '
-- || ' AND uom.unit_of_measure = gs.usage_uom '
-- || ' AND uom2.unit_of_measure = r.step_qty_uom '
|| ' AND uom.um_type = :gmp_uom_class '
|| ' AND uom.um_code = gs.usage_uom '
|| ' AND uom2.um_code = r.step_qty_uom '
|| ' AND c.delete_mark = 0 '
|| ' AND nvl(c.inactive_ind,0) = 0 ' ;
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');
' SELECT /*+ DRIVING_SITE(gbsc) DRIVING_SITE(crd) DRIVING_SITE(iwm) DRIVING_SITE(v) '
||' DRIVING_SITE(gbs) DRIVING_SITE(h) */ '
||' ((gbsc.batch_id*2)+1) x_batch_id,'
-- ||' gbsc.batchstep_id,'
||' ((gbsc.batchstep_id*2)+1),' /* Bug: 6030499 Vpedarla back porting bug:5461922 */
|| ' ((crd.resource_id * 2) + 1),'
||' gbsc.charge_number,'
||' iwm.mtl_organization_id,'
||' gbs.batchstep_no,'
||' gbsc.activity_sequence_number,'
||' gbsc.charge_quantity, '
||' gbsc.plan_start_date, '
||' gbsc.plan_cmplt_date'
||' FROM'
||' gme_batch_step_charges'||pdblink||' gbsc,'
||' cr_rsrc_dtl'||pdblink||' crd,'
||' ic_whse_mst'||pdblink||' iwm,'
||' gmd_recipe_validity_rules'||pdblink||' v,'
||' gme_batch_steps'||pdblink||' gbs,'
||' gme_batch_header'||pdblink||' h'
||' WHERE '
||' h.batch_id = gbs.batch_id '
||' AND gbsc.batch_id = gbs.batch_id '
||' AND gbsc.batchstep_id = gbs.batchstep_id '
||' AND h.recipe_validity_rule_id = v.recipe_validity_rule_id'
|| ' AND EXISTS (SELECT /*+ DRIVING_SITE(gmd) */ 1 '
|| ' FROM '
|| ' gme_material_details'||pdblink||' gmd '
|| ' WHERE '
|| ' gmd.batch_id = h.batch_id '
|| ' AND gmd.item_id = v.item_id '
|| ' AND gmd.line_type = 1 ) ' /* B11794865 */
||' AND crd.resources = gbsc.resources '
||' AND h.wip_whse_code = iwm.whse_code'
||' AND gbs.step_status in (1, 2) ';
||' AND EXISTS ( SELECT 1 FROM sy_orgn_mst'||pdblink||' som '
||' WHERE h.wip_whse_code = som.resource_whse_code )' ;
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');
/* Alternate Resource selection */
/* B5688153, Rajesh Patangya prod spec alt*/
/* B5879844 Uday Phadtare replaced at_apps_link with pdblink for prod */
statement_alt_resource :=
' SELECT /*+ DRIVING_SITE(acrd) DRIVING_SITE(pcrd) DRIVING_SITE(cam) DRIVING_SITE(prod) */ '
||' pcrd.resource_id, acrd.resource_id, '
||' acrd.min_capacity, acrd.max_capacity, '
||' cam.runtime_factor, '
/*prod spec alt*/ ||' nvl(cam.preference,-1), nvl(prod.item_id,-1) '
||' FROM cr_rsrc_dtl'||pdblink||' acrd, '
||' cr_rsrc_dtl'||pdblink||' pcrd, '
||' cr_ares_mst'||pdblink||' cam, '
||' gmp_altresource_products'||pdblink||' prod'
||' WHERE cam.alternate_resource = acrd.resources '
||' AND cam.primary_resource = pcrd.resources '
||' AND acrd.orgn_code = pcrd.orgn_code '
||' AND cam.primary_resource = prod.primary_resource(+) '
||' AND cam.alternate_resource = prod.alternate_resource(+) '
||' AND acrd.delete_mark = 0 '
||' ORDER BY pcrd.resource_id, '
||' DECODE(cam.preference,NULL,cam.runtime_factor,cam.preference),'
||' prod.item_id ' ;
'SELECT '
|| ' iwm.mtl_organization_id '
|| 'FROM '
|| ' sy_orgn_mst' ||pdblink|| ' sy, '
|| ' ic_whse_mst' ||pdblink|| ' iwm '
|| 'WHERE '
|| ' sy.orgn_code = :p1'
|| ' AND sy.resource_whse_code = iwm.whse_code';
/* nsinghi APSK - Insert Step related information in msc_st_job_operations
every time step changes. */
jo_index := jo_index + 1;
/* Bulk Insert for insert_resource_requirements */
rr_index := rr_index + 1 ;
Now 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.
*/
IF rsrc_tab(r).prim_rsrc_ind = 1 THEN
---------------------------------------------------------------------
-- Use Bsearch technique to identify if any Alternate exists for the primary.
-- Enh_bsearch_alternate_rsrc is a new procedure to locate the Alternate Resource
-- for a given Primary resource in the PL/SQl table.
---------------------------------------------------------------------
alternate_rsrc_loc := Enh_bsearch_alternate_rsrc (rsrc_tab(r).resource_id);
/* Bulk Insert for Alternate_resource_requirements */
arr_index := arr_index + 1 ;
alternate resources that has been inserted for the Primary resource
of the group. Now insert all the resource records other than primary
with a value of Alternate_Number from 1 to v_alternate, to complete
the pattern of resource group.
NAVIN: */
IF rsrc_tab(r).prim_rsrc_ind <> 1 AND v_alternate > 0 THEN
/* B3995361 rpatangy start */
mk_alt_grp := 0 ;
/* Bulk Insert for insert_resource_requirements */
inst_indx := inst_indx + 1 ;
/* B3267522, Rajesh Patangya Do not insert demands, if ingradient is same as product
(single level circular reference) */
IF prod_tab(p).item_id <> product_line THEN
/* Demands Bulk inserts */
d_index := d_index + 1 ;
/* Supply Bulk Insert Assignments */
s_index := s_index + 1 ;
INSERT INTO msc_st_resource_requirements (
organization_id,
sr_instance_id,
supply_id,
supply_type, /* sultripa B4612203 Need to populate supply_type field */
resource_seq_num,
resource_id,
start_date,
end_date,
operation_hours_required,
usage_rate, /* B4637398 Rajesh Patangya */
assigned_units,
department_id,
wip_entity_id,
operation_seq_num,
deleted_flag,
firm_flag,
minimum_transfer_quantity,
parent_seq_num,
schedule_flag,
-- HW B4902328 - Added inventory_item_id
inventory_item_id,
basis_type,
setup_id,
group_sequence_id,
group_sequence_number,
minimum_capacity,
maximum_capacity,
orig_resource_seq_num,
alternate_number,
hours_expended,
breakable_activity_flag,
step_quantity, /* Sowmya - As per latest FDD changes*/
step_quantity_uom , /* Sowmya - As per latest FDD changes*/
maximum_assigned_units, /* Sowmya - As per latest FDD changes*/
-- unadjusted_resource_hours, /*B4320561 - Same as in wip (without eff and util) */
-- touch_time, /* B4320561 - Unadjusted res. hrs / efficiency.*/
activity_group_id, /* B3995361 rpatangy */
operation_sequence_id /* Bug: 6030499 vpedarla back port Bug:5461922 */
)
VALUES (
rr_organization_id(i),
rr_sr_instance_id(i),
rr_supply_id(i),
1, /* sultripa B4612203 supply_type = 1 for OPM batches*/
rr_resource_seq_num(i),
rr_resource_id(i),
rr_start_date(i),
rr_end_date(i),
rr_opr_hours_required(i),
nvl(rr_usage_rate(i),0), /* B4637398 Rajesh Patangya */
rr_assigned_units(i),
rr_department_id(i),
rr_wip_entity_id(i),
rr_operation_seq_num(i),
2,
rr_firm_flag(i),
rr_minimum_transfer_quantity(i),
rr_parent_seq_num(i),
rr_schedule_flag(i),
-- HW B4902328 - Added inventory_item_id
rr_inventory_item_id(i),
rr_basis_type(i),
rr_setup_id(i),
rr_sequence_id(i), -- group_sequence_id
rr_sequence_number(i), -- group_sequence_number
rr_min_capacity(i),
rr_max_capacity(i),
rr_original_seq_num(i),
rr_alternate_number(i),
rr_hours_expended(i),
rr_breakable_activity_flag(i),
rr_plan_step_qty(i), /* Sowmya - As per the latest FDD changes*/
rr_step_qty_uom(i) , /* Sowmya - As per the latest FDD changes*/
rr_gmd_rsrc_cnt(i),
-- rr_unadjusted_resource_hrs(i), /*B4320561 - sowsubra*/
-- rr_touch_time(i), /*B4320561 - sowsubra*/
rr_activity_group_id(i), /* B3995361 rpatangy */
rr_operation_sequence_id(i) /* Bug: 6030499 vpedarla back port Bug:5461922 */
) ;
/* ----------------------- Supply Insert --------------------- */
i := 1 ;
INSERT INTO msc_st_supplies (
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
new_schedule_date,
old_schedule_date,
new_wip_start_date,
old_wip_start_date,
last_unit_completion_date,
disposition_id,
order_type,
order_number,
new_order_quantity,
old_order_quantity,
firm_planned_type,
firm_quantity,
firm_date,
wip_entity_name,
lot_number,
expiration_date,
operation_seq_num,
by_product_using_assy_id,
deleted_flag,
requested_completion_date,
wip_status_code, /*B5100481*/
schedule_priority,
process_seq_id /* B6795244 */
)
VALUES (
-1,
s_inventory_item_id(i),
s_organization_id(i),
s_sr_instance_id(i),
s_new_schedule_date(i),
s_old_schedule_date(i),
s_new_wip_start_date(i),
s_old_wip_start_date(i),
s_lunit_completion_date(i),
s_disposition_id(i),
s_order_type(i),
s_order_number(i),
s_new_order_quantity(i),
s_old_order_quantity(i),
s_firm_planned_type(i), /* 2 */
s_firm_quantity(i),
s_firm_date(i),
s_wip_entity_name(i), /* Order Number */
null_value,
null_value,
s_operation_seq_num(i),
s_by_product_using_assy_id(i),
2, /* Deleted Flag */
s_requested_completion_date(i),
s_wip_status_code(i), /*B5100481 - 16 for pending, 3 for wip */
s_schedule_priority(i),
s_process_seq_id(i) /* B6795244 */
) ;
/* ----------------------- Demands Insert --------------------- */
i := 1 ;
INSERT INTO msc_st_demands (
organization_id,
inventory_item_id,
sr_instance_id,
using_assembly_item_id,
using_assembly_demand_date,
using_requirement_quantity,
demand_type,
origination_type,
wip_entity_id,
demand_schedule_name,
order_number,
wip_entity_name,
selling_price,
operation_seq_num,
wip_status_code, /*B5100481*/
deleted_flag )
VALUES (
d_organization_id(i),
d_inventory_item_id(i),
d_sr_instance_id(i),
d_assembly_item_id(i),
d_demand_date(i),
d_requirement_quantity(i),
d_demand_type(i),
d_origination_type(i),
d_wip_entity_id(i),
d_demand_schedule(i),
d_order_number(i),
d_wip_entity_name(i),
d_selling_price(i),
d_operation_seq_num(i),
d_wip_status_code(i), /*B5100481*/
2 ) ;
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');
/* Bug:6030499 Vpedarla conditionalsing the insert code */
IF ( NOT collect_ps_data ) THEN
return_status := TRUE;
/* NAVIN: -- START: Complex Route -- Collect Batch Step Dependencies in one insert-select --*/
sql_stmt :=
' INSERT INTO msc_st_job_operation_networks '
|| ' ( '
|| ' from_op_seq_id, '
|| ' to_op_seq_id, '
|| ' wip_entity_id, '
|| ' dependency_type, '
|| ' transition_type, '
|| ' sr_instance_id, '
|| ' deleted_flag, '
|| ' minimum_time_offset, '
|| ' maximum_time_offset, '
|| ' transfer_pct, '
|| ' from_op_seq_num, '
|| ' to_op_seq_num, '
|| ' apply_to_charges, '
|| ' organization_id, '
|| ' recommended '
|| ' ) '
|| ' SELECT '
||' gbsd.dep_step_id, '
||' gbsd.batchstep_id, '
||' ((gbsd.batch_id * 2) + 1) x_batch_id, '
||' decode(gbsd.dep_type,0,1,2) dependency_type, '
||' 1, '
||' :1, '
||' 2, '
||' gbsd.standard_delay, '
||' gbsd.max_delay, '
||' gbsd.transfer_percent, '
||' gbs1.batchstep_no, '
||' gbs2.batchstep_no, '
||' DECODE(NVL(gbsd.chargeable_ind,0),1,1,2), ' /* convert a Null or 0 to a 2, a 1 remains a 1 */
||' iwm.mtl_organization_id, '
|| '''Y'''
||' FROM '
||' gme_batch_step_dependencies'||pdblink||' gbsd, '
||' gme_batch_header'||pdblink||' h,'
||' gme_batch_steps'||pdblink||' gbs1, '
||' gme_batch_steps'||pdblink||' gbs2, '
||' ic_whse_mst'||pdblink||' iwm, '
||' sy_orgn_mst'||pdblink||' som '
||' WHERE '
||' h.batch_id = gbsd.batch_id '
||' AND gbs1.batch_id = gbsd.batch_id '
||' AND gbs1.batchstep_id = gbsd.dep_step_id '
||' AND gbs2.batch_id = gbsd.batch_id '
||' AND gbs2.batchstep_id = gbsd.batchstep_id '
||' AND h.batch_status in (1, 2) '
||' AND h.plant_code = som.orgn_code '
||' AND som.delete_mark = 0 '
||' AND som.resource_whse_code = iwm.whse_code ' ;
/* NAVIN: ------------ END: Complex Route -- Collect Batch Step Dependencies in one insert-select ------------*/
/* NAVIN: ----------------------- MTQ with Hardlinks --------------------- */
i := 1 ;
INSERT INTO msc_st_job_operation_networks(
from_op_seq_id,
wip_entity_id,
dependency_type,
transition_type,
sr_instance_id,
deleted_flag,
from_item_id,
organization_id,
minimum_time_offset,
maximum_time_offset,
from_op_seq_num,
minimum_transfer_qty,
recommended
)
VALUES
(
stp_var_itm_from_op_seq_id(i),
stp_var_itm_wip_entity_id(i),
5, -- dependency_type for mtq with hardlink
1, -- transition_type: primary
stp_var_itm_instance_id(i),
2,
stp_var_itm_FROM_item_ID(i),
stp_var_itm_organization_id(i),
stp_var_itm_min_tm_off(i),
stp_var_itm_max_tm_off(i),
stp_var_itm_from_op_seq_num(i),
stp_var_min_tran_qty(i),
'Y'
);
INSERT INTO msc_st_resource_charges
(
sr_instance_id ,
resource_id ,
organization_id ,
department_id ,
wip_entity_id ,
operation_sequence_id ,
operation_seq_num ,
resource_seq_num ,
charge_number ,
charge_quantity ,
deleted_flag ,
charge_start_datetime ,
charge_end_datetime
)
VALUES
(
stp_instance_id(i) ,
stp_chg_resource_id(i) ,
stp_chg_organization_id(i),
stp_chg_department_id(i),
stp_chg_wip_entity_id(i),
stp_chg_operation_seq_id(i),
stp_chg_operation_seq_no(i),
stp_chg_resource_seq_num(i),
stp_chg_charge_num(i),
stp_chg_charge_quanitity(i),
2,
stp_chg_charge_start_dt_time(i) ,
stp_chg_charge_end_dt_time(i)
);
INSERT INTO msc_st_resource_instance_reqs (
supply_id,
organization_id,
sr_instance_id,
resource_seq_num,
resource_id,
res_instance_id,
start_date,
end_date,
resource_instance_hours,
/* NAVIN :- CHECK Should This be Included. It is
mentioned in FDD, but not included in APS script file. */
-- schedule_flag,
operation_seq_num,
department_id,
wip_entity_id,
serial_number,
deleted_flag,
parent_seq_num, /* Sowmya - as the column was changed from parent_seq_number to parent_seq_num */
orig_resource_seq_num,
equipment_item_id /*Sowmya - As per the latest FDD changes - End*/
)
VALUES (
rec_inst_supply_id(i) ,
rec_inst_organization_id(i) ,
rec_inst_sr_instance_id(i) ,
rec_inst_rec_resource_seq_num(i) ,
rec_inst_resource_id(i) ,
rec_inst_instance_id(i) ,
rec_inst_start_date(i) ,
rec_inst_end_date(i) ,
rec_inst_rsrc_instance_hours(i) ,
-- 1 , /* Schedule Flag 1 = Scheduled */
rec_inst_operation_seq_num(i) ,
rec_inst_department_id(i) ,
rec_inst_wip_entity_id(i) ,
rec_inst_serial_number(i) ,
2 , /* Delete Flag */
rec_inst_parent_seq_num(i) ,
rec_inst_original_seq_num(i),
rec_inst_equp_item_id(i) /*Sowmya - As per the latest FDD changes - End*/
) ;
INSERT INTO msc_st_job_op_resources
(
wip_entity_id,
organization_id,
sr_instance_id ,
operation_seq_num ,
resource_seq_num ,
resource_id ,
alternate_num ,
reco_start_date ,
reco_completion_date ,
usage_rate_or_amount ,
assigned_units ,
schedule_flag ,
parent_seq_num ,
recommended ,
department_id ,
uom_code ,
activity_group_id ,
basis_type ,
firm_flag ,
setup_id ,
schedule_seq_num ,
group_sequence_id ,
group_sequence_number ,
-- resource_batch_id ,
maximum_assigned_units ,
deleted_flag ,
batch_number
)
VALUES
(
arr_wip_entity_id(i),
arr_organization_id(i),
arr_sr_instance_id(i),
arr_operation_seq_num(i),
arr_res_seq_num(i),
arr_resource_id(i),
arr_alternate_num(i),
null_value,
null_value,
arr_usage_rate(i),
arr_assigned_units(i),
1,
null_value,
1,
arr_department_id(i),
arr_uom_code(i),
arr_activity_group_id(i),
arr_basis_type(i),
null_value,
arr_setup_id(i),
arr_schedule_seq_num(i),
null_value,
null_value,
-- null_value,
arr_maximum_assigned_units(i),
2,
null_value
);
INSERT INTO msc_st_job_operations
(
wip_entity_id,
sr_instance_id,
operation_seq_num,
recommended,
network_start_end,
reco_start_date,
reco_completion_date,
operation_sequence_id,
organization_id,
department_id,
minimum_transfer_quantity,
effectivity_date,
deleted_flag
)
VALUES
(
jo_wip_entity_id(i),
jo_instance_id(i),
jo_operation_seq_num(i),
jo_recommended(i),
jo_network_start_end(i),
jo_reco_start_date(i),
jo_reco_completion_date(i),
jo_operation_sequence_id(i),
jo_organization_id(i),
jo_department_id(i),
jo_minimum_transfer_quantity(i),
(SYSDATE-100),
2
);
* insert_supplies
*
* DESCRIPTION
* This procedure will take the parameter values and insert a row into
* the table msc_st_supplies
* HISTORY
* M Craig
* 2/10/2000 - Populating Order number column with Wip Entity Name ( porder_no )
* 2/24/2003 - populating Firmed batches Indicator, Qty and Date
************************************************************************/
PROCEDURE insert_supplies(
pitem_id NUMBER,
porganization_id NUMBER,
pinstance_id NUMBER,
pdate DATE,
pstart_date DATE,
pend_date DATE,
pbatch_id NUMBER,
pqty NUMBER,
pfirmed_ind NUMBER,
pbatchstep_no NUMBER, /* Added pbatchstep_no - B2919303 */
porder_no VARCHAR2,
plot_number VARCHAR2,
pexpire_date DATE,
psupply_type NUMBER,
pproduct_item_id NUMBER) /* B2953953 - CoProduct */
AS
st_supplies VARCHAR2(32000) ;
' INSERT INTO msc_st_supplies ( '
||' plan_id, inventory_item_id, organization_id, sr_instance_id, '
||' new_schedule_date, old_schedule_date, new_wip_start_date, '
||' old_wip_start_date, last_unit_completion_date, disposition_id, '
||' order_type, order_number, new_order_quantity, old_order_quantity, '
||' firm_planned_type,firm_quantity,firm_date, wip_entity_name, '
||' lot_number, expiration_date,operation_seq_num, by_product_using_assy_id, '
||' deleted_flag ) '
||' VALUES '
||' (:p1, :p2, :p3, :p4, '
||' :p5, :p6, :p7, '
||' :p8, :p9, :p10, '
||' :p11,:p12,:p13,:p14,'
||' :p15,:p16,:p17,:p18,'
||' :p19,:p20,:p21,'
||' :p22,:p23 ) ' ;
log_message('Failure occured during the insert into msc_st_supplies');
END insert_supplies;
* insert_resource_requirements
*
* DESCRIPTION
* This procedure wil insert a row into the table
* msc_st_resource_requirements using the parameters passed in
* HISTORY
* M Craig
* 10/13/99 - Added deleted_flag in the insert statement
* 13-SEP-2002 - firm_flag = 1 for WIP steps B2266934
************************************************************************/
PROCEDURE insert_resource_requirements(
porganization_id IN NUMBER,
pinstance_id IN NUMBER,
pseq_num IN NUMBER,
presource_id IN NUMBER,
pstart_date IN DATE,
pend_date IN DATE,
presource_usage IN NUMBER,
prsrc_cnt IN NUMBER,
pbatchstep_no IN NUMBER, /* B1224660 new parm to write step number */
pbatch_id IN NUMBER,
pstep_status IN NUMBER,
pschedule_flag IN NUMBER,
pparent_seq_num IN NUMBER,
pmin_xfer_qty IN NUMBER)
AS
st_resource_requirements VARCHAR2(32000) ;
' INSERT INTO msc_st_resource_requirements ( '
||' organization_id, sr_instance_id, supply_id, resource_seq_num,'
||' resource_id, start_date, end_date, operation_hours_required,'
||' assigned_units, department_id, wip_entity_id, operation_seq_num, '
||' deleted_flag, firm_flag, minimum_transfer_quantity, '
||' parent_seq_num, schedule_flag ) '
||' VALUES '
||' ( :p1, :p2, :p3, :p4, '
||' :p5, :p6, :p7, :p8, '
||' :p9, :p10,:p11,:p12, '
||' :p13,:p14, :p15, '
||' :p16, :p17 ) ';
log_message('Failure occured during the insert into msc_st_resource_requirements');
END insert_resource_requirements;
* insert_demands
*
* DESCRIPTION
* This procedure will take the parameter values and insert a row into
* the table msc_st_demands
* HISTORY
* M Craig
* 10/13/99 - Added deleted_flag in the insert statement
* P Dong
* 09/14/01 - added api_mode and pschedule_id parameters
************************************************************************/
PROCEDURE insert_demands(
pitem_id NUMBER,
porganization_id NUMBER,
pinstance_id NUMBER,
pbatch_id NUMBER,
pproduct_item_id NUMBER,
pdate DATE,
pqty NUMBER,
pbatchstep_no NUMBER, /* B2919303 - BatchStep */
porder_no VARCHAR2,
pdesignator VARCHAR2,
pnet_price NUMBER, /* B1200400 added net price */
porigination_type NUMBER,
api_mode BOOLEAN,
pschedule_id NUMBER )
AS
statement_demands_api VARCHAR2(32000) ;
' INSERT INTO gmp_demands_api ( '
||' organization_id, schedule_id, inventory_item_id, demand_date, '
||' demand_quantity, origination_type, doc_id, selling_price ) '
||' VALUES '
||' ( :p1, :p2, :p3, :p4, '
||' :p5, :p6, :p7, :p8 ) ';
log_message('Failure occured during the insert into gmp_demands_api');
SELECT DECODE(porigination_type,1,NULL,porder_no) ,
DECODE(porigination_type,1,porder_no,NULL)
INTO t_order_number, t_wip_entity_name
FROM dual ;
' INSERT INTO msc_st_demands ( '
||' organization_id, inventory_item_id, sr_instance_id, '
||' using_assembly_item_id, using_assembly_demand_date, '
||' using_requirement_quantity, demand_type, origination_type, '
||' wip_entity_id, demand_schedule_name, order_number, '
||' wip_entity_name, selling_price,operation_seq_num,deleted_flag ) '
||' VALUES '
||' ( :p1, :p2, :p3, '
||' :p4, :p5, '
||' :p6, :p7, :p8 , '
||' :p9, :p10,:p11, '
||' :p12,:p13,:p14,:p15 )' ;
log_message('Failure occured during the insert into msc_st_demands');
END insert_demands;
* This procedure will insert records into the table msc_st_supplies
* for the onhand balances in inventory. The insert is split into 3 parts
* one for non-lot controlled, lot controlled, and lot and status
* controlled item. Each inserted will need touse a distnct list from
* the table gmp_item_aps. The table may contain multiple values for
* the item/whse combination
* HISTORY
* M Craig
* M Craig B1332662 changed to call two new procs to collect onhand and
* Inventory transfers
* Navin 21-APR-2003 B3577871 ST:OSFME2: collections failing in planning data pull.
* Added handling of NO_DATA_FOUND Exception.
* And return the return_status as TRUE.
************************************************************************/
PROCEDURE onhand_inventory(
pdblink IN VARCHAR2,
pinstance_id IN NUMBER,
prun_date IN DATE,
pdelimiter IN VARCHAR2,
return_status IN OUT NOCOPY BOOLEAN)
AS
local_ret_status1 BOOLEAN ;
* This procedure will insert records into the table msc_st_supplies
* for the onhand balances in inventory. The insert is split into 3 parts
* one for non-lot controlled, lot controlled, and lot and status
* controlled item. Each inserted will need touse a distnct list from
* the table gmp_item_aps. The table may contain multiple values for
* the item/whse combination
* HISTORY
* M Craig
* 10/13/99 - Added deleted_flag in the insert statement
* 2/10/2000 - Populating sub inventory code with whse code - bug# 1172875
* M Craig B1332662 created a new function to just collect onhand inventory
* Sgidugu B2251375 - Changed Substr Function to substrb Function
* AKARUPPA B4287033 - Changed direct insert selects to BULK INSERT as direct insert
* select over dblink causes performance issues.
* AKARUPPA B4278082 - Changed query to select status controlled items to
* fetch items with status control as No Inventory (status_ctl = 2)
************************************************************************/
PROCEDURE extract_onhand_balances(
pdblink IN VARCHAR2,
pinstance_id IN NUMBER,
prun_date IN DATE,
pdelimiter IN VARCHAR2,
return_status IN OUT NOCOPY BOOLEAN)
AS
/* akaruppa B4287033 07-APR-2005 Adding new variable definitions for doing BULK INSERT in place of direct INSERT SELECT */
TYPE onhand_balance_nolot_typ IS RECORD(
plan_id PLS_INTEGER,
inventory_item_id PLS_INTEGER,
organization_id PLS_INTEGER,
sr_instance_id PLS_INTEGER,
new_schedule_date DATE,
order_type PLS_INTEGER,
firm_planned_type PLS_INTEGER,
deleted_flag PLS_INTEGER,
subinventory_code VARCHAR2(10),
new_order_quantity NUMBER );
deleted_flag PLS_INTEGER,
subinventory_code VARCHAR2(10),
new_order_quantity NUMBER );
deleted_flag PLS_INTEGER,
subinventory_code VARCHAR2(10),
new_order_quantity NUMBER,
non_nettable_qty NUMBER );
insert_count NUMBER;
insert_count := 1;
/* Query to select the production order details where the batch/fpo is pending
the balances from ic_summ for the item/whse that are not lot controlled
are inserted */
v_onhand_cursor := ' SELECT '
|| ' -1,'
|| ' i.aps_item_id,'
|| ' i.organization_id,'
|| ' :pinstance_id, '
|| ' :prun_date, '
|| ' 18,' /* onhand inventory value */
|| ' 2,'
|| ' 2,'
|| ' s.whse_code,' /* Populate subinventory with Whse code B1172875 */
|| ' s.onhand_qty'
|| ' FROM '
|| ' ic_summ_inv_onhand_v' ||pdblink|| ' s,'
|| ' (select distinct aps_item_id,item_id,whse_code,organization_id, '
|| ' lot_control,experimental_ind from gmp_item_aps'||pdblink||') i'
|| ' WHERE '
|| ' s.item_id = i.item_id '
|| ' and s.whse_code = i.whse_code '
|| ' and i.lot_control = 0'
|| ' and s.onhand_qty <> 0';
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');
o_plan_id(insert_count) := onhand_balance_nolot_tab(i).plan_id;
o_inventory_item_id(insert_count) := onhand_balance_nolot_tab(i).inventory_item_id;
o_organization_id(insert_count) := onhand_balance_nolot_tab(i).organization_id;
o_sr_instance_id(insert_count) := onhand_balance_nolot_tab(i).sr_instance_id;
o_new_schedule_date(insert_count) := onhand_balance_nolot_tab(i).new_schedule_date;
o_new_dock_date(insert_count) := NULL;
o_order_type(insert_count) := onhand_balance_nolot_tab(i).order_type;
o_lot_number(insert_count) := NULL;
o_expiration_date(insert_count) := NULL;
o_firm_planned_type(insert_count) := onhand_balance_nolot_tab(i).firm_planned_type;
o_deleted_flag(insert_count) := onhand_balance_nolot_tab(i).deleted_flag;
o_subinventory_code(insert_count) := onhand_balance_nolot_tab(i).subinventory_code;
o_new_order_quantity(insert_count) := onhand_balance_nolot_tab(i).new_order_quantity;
o_non_nettable_qty(insert_count) := NULL;
insert_count := insert_count + 1;
insert the lot number is the combo of lot and sublot
*/
v_onhand_cursor := ' SELECT /*+ DRIVING_SITE(s) DRIVING_SITE(l) DRIVING_SITE(m) */ '
|| ' -1,'
|| ' i.aps_item_id,'
|| ' i.organization_id,'
|| ' :pinstance_id,'
|| ' :prun_date,'
|| ' 18,' /* onhand inventory value */
|| ' substrb(l.lot_no||DECODE(l.sublot_no, NULL,NULL ,:pdelimiter || '
|| ' l.sublot_no),1,30),'
|| ' l.expire_date,'
|| ' 2,'
|| ' 2,'
|| ' s.whse_code,' /* Populate subinventory with whse code B1172875 */
|| ' s.loct_onhand'
|| ' FROM'
|| ' ic_loct_inv'||pdblink||' s,'
|| ' ic_lots_mst'||pdblink||' l,'
|| ' ic_item_mst_b'||pdblink||' m,'
|| ' (select distinct aps_item_id,item_id,whse_code,organization_id, '
|| ' lot_control,experimental_ind from gmp_item_aps'||pdblink||') i'
|| ' WHERE'
|| ' s.item_id = i.item_id'
|| ' and s.item_id = m.item_id'
|| ' and s.whse_code = i.whse_code'
|| ' and i.lot_control = 1'
|| ' and m.status_ctl = 0'
|| ' and s.lot_id = l.lot_id'
|| ' and s.item_id = l.item_id'
|| ' and s.lot_id > 0'
|| ' and l.delete_mark = 0'
|| ' and s.loct_onhand <> 0';
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');
o_plan_id(insert_count) := onhand_balance_lot_tab(i).plan_id;
o_inventory_item_id(insert_count) := onhand_balance_lot_tab(i).inventory_item_id;
o_organization_id(insert_count) := onhand_balance_lot_tab(i).organization_id;
o_sr_instance_id(insert_count) := onhand_balance_lot_tab(i).sr_instance_id;
o_new_schedule_date(insert_count) := onhand_balance_lot_tab(i).new_schedule_date;
o_new_dock_date(insert_count) := NULL;
o_order_type(insert_count) := onhand_balance_lot_tab(i).order_type;
o_lot_number(insert_count) := onhand_balance_lot_tab(i).lot_number;
o_expiration_date(insert_count) := NULL;
o_expiration_date(insert_count) := onhand_balance_lot_tab(i).expiration_date;
o_firm_planned_type(insert_count) := onhand_balance_lot_tab(i).firm_planned_type;
o_deleted_flag(insert_count) := onhand_balance_lot_tab(i).deleted_flag;
o_subinventory_code(insert_count) := onhand_balance_lot_tab(i).subinventory_code;
o_new_order_quantity(insert_count) := onhand_balance_lot_tab(i).new_order_quantity;
o_non_nettable_qty(insert_count) := NULL;
insert_count := insert_count + 1;
v_onhand_cursor := ' SELECT /*+ DRIVING_SITE(s) DRIVING_SITE(l) DRIVING_SITE(m) */ '
|| ' -1,'
|| ' i.aps_item_id,'
|| ' i.organization_id,'
|| ' :pinstance_id,'
|| ' DECODE(c.ic_hold_date,NULL,:prun_date,c.ic_hold_date),'
/* B8560329 dock date should be equal to schedule date, ASCP will calcualte the
* new schedule date as new dock date + pre processing lead time and if it is on
* not working day, it will push the calulated date to working day, As per discussion with sanjay
|| ' :prun_date,' */
|| ' DECODE(c.ic_hold_date,NULL,:prun_date,c.ic_hold_date),'
|| ' DECODE(c.ic_hold_date,NULL,18,8),' /* onhand inventory value */
|| ' substrb(l.lot_no||DECODE(l.sublot_no, NULL,NULL ,:pdelimiter || '
|| ' l.sublot_no),1,30),'
|| ' l.expire_date,'
|| ' 2,'
|| ' 2,'
|| ' s.whse_code,' /* Populating subinventory code with whse code B1172875 */
|| ' s.loct_onhand, '
|| ' decode(t.order_proc_ind,0,s.loct_onhand,0)'
|| ' FROM'
|| ' ic_loct_inv'||pdblink||' s,'
|| ' ic_lots_mst'||pdblink||' l,'
|| ' ic_item_mst_b'||pdblink||' m,'
|| ' (select distinct aps_item_id, item_id, whse_code, organization_id, '
|| ' lot_control,experimental_ind from gmp_item_aps'||pdblink||') i,'
|| ' ic_lots_sts'||pdblink||' t,'
|| ' ic_lots_cpg'||pdblink||' c'
|| ' WHERE'
|| ' s.item_id = i.item_id'
|| ' and s.item_id = m.item_id'
|| ' and s.whse_code = i.whse_code'
|| ' and i.lot_control = 1'
|| ' and s.lot_id = l.lot_id'
|| ' and s.item_id = l.item_id'
|| ' and s.lot_id > 0'
|| ' and l.delete_mark = 0'
|| ' and m.status_ctl IN (1,2)' -- akaruppa B4278082 19-APR-2005 Added status_ctl = 2 also as Items with Status Control with No Inventory were not getting collected
|| ' and s.lot_status = t.lot_status'
|| ' and t.rejected_ind = 0'
|| ' and t.nettable_ind = 1'
|| ' and s.loct_onhand <> 0'
|| ' and c.item_id (+) = l.item_id'
|| ' and c.lot_id (+) = l.lot_id'
|| ' and c.ic_hold_date (+) > :run_date' ;
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');
o_plan_id(insert_count) := onhand_balance_status_tab(i).plan_id;
o_inventory_item_id(insert_count) := onhand_balance_status_tab(i).inventory_item_id;
o_organization_id(insert_count) := onhand_balance_status_tab(i).organization_id;
o_sr_instance_id(insert_count) := onhand_balance_status_tab(i).sr_instance_id;
o_new_schedule_date(insert_count) := onhand_balance_status_tab(i).new_schedule_date;
o_new_dock_date(insert_count) := onhand_balance_status_tab(i).new_dock_date;
o_order_type(insert_count) := onhand_balance_status_tab(i).order_type;
o_lot_number(insert_count) := onhand_balance_status_tab(i).lot_number;
o_expiration_date(insert_count) := NULL;
o_expiration_date(insert_count) := onhand_balance_status_tab(i).expiration_date;
o_firm_planned_type(insert_count) := onhand_balance_status_tab(i).firm_planned_type;
o_deleted_flag(insert_count) := onhand_balance_status_tab(i).deleted_flag;
o_subinventory_code(insert_count) := onhand_balance_status_tab(i).subinventory_code;
o_new_order_quantity(insert_count) := onhand_balance_status_tab(i).new_order_quantity;
o_non_nettable_qty(insert_count) := onhand_balance_status_tab(i).non_nettable_qty;
insert_count := insert_count + 1;
insert_count := insert_count - 1;
FORALL i IN 1..insert_count
INSERT INTO msc_st_supplies
(plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
new_schedule_date,
new_dock_date,
order_type,
lot_number,
expiration_date,
firm_planned_type,
deleted_flag,
subinventory_code,
new_order_quantity,
non_nettable_qty
)
VALUES(o_plan_id(i),
o_inventory_item_id(i),
o_organization_id(i),
o_sr_instance_id(i),
o_new_schedule_date(i),
o_new_dock_date(i),
o_order_type(i),
o_lot_number(i),
o_expiration_date(i),
o_firm_planned_type(i),
o_deleted_flag(i),
o_subinventory_code(i),
o_new_order_quantity(i),
o_non_nettable_qty(i)
);
o_deleted_flag := empty_pls_table ;
* This procedure will insert records into the table msc_st_demands
* According to APS team (Sam Tupe < prganesh Shah etc.
* The inventory transfer demand is similar to Internal Sales Order
* demand hence should be added to each of the demand schedule
* The specifics are
* demand_type = 6
* origination_type = 6
* disposition_id = same transfer_id This should match with the
* corresponding transaction_id of the supply created
* by the same transfer
* demand_schedule_name = OPM specific demand_schedule name - The
* MDS names used in forecast/SO extraction
* HISTORY
* 25-Jan-2003 B2756431
* Note : Old procedure extract_inv_transfers is now removed
* and replaced with these two new procedures
************************************************************************/
PROCEDURE extract_inv_transfer_demands(
pdblink IN VARCHAR2,
pinstance_id IN NUMBER,
prun_date IN DATE,
pdelimiter IN VARCHAR2,
pwhse_code IN VARCHAR2,
pdesignator IN VARCHAR2,
return_status IN OUT NOCOPY BOOLEAN)
AS
pdoc_type VARCHAR2(4) ;
v_sql_stmt := 'INSERT into msc_st_demands ('
|| ' organization_id,'
|| ' inventory_item_id,'
|| ' sr_instance_id,'
|| ' using_assembly_item_id,'
|| ' using_assembly_demand_date,'
|| ' using_requirement_quantity,'
|| ' demand_type,'
|| ' origination_type,'
|| ' order_number,'
|| ' demand_schedule_name,'
|| ' disposition_id,' /* B2756431 */
|| ' demand_source_type,' /* B2756431 */
|| ' original_system_reference,' /* B2756431 */
|| ' original_system_line_reference,' /* being added for B2756431 */
|| ' deleted_flag)'
|| ' SELECT '
|| ' i.organization_id,'
|| ' i.aps_item_id,'
|| ' :pinstance_id, '
|| ' i.aps_item_id,'
|| ' s.scheduled_release_date,'
|| ' s.release_quantity1,'
|| ' 1,' /* Discrete , other demands types are interpreted as continuous */
|| ' 6,' /* Orig_type should br 6 per Sam Tupe so change from 11 */
|| ' :pdoc_type || :pdelimiter || s.orgn_code ||'
|| ' :pdelimiter2 || s.transfer_no, '
--Bug: 6030499 Vpedarla Based on Bug: 5665290
-- || ' :pdesignator,'
|| ' :p_doc_type || :p_delimiter3 || s.from_warehouse , ' --Bug: 6030499 added this column.
|| ' s.transfer_id,'
|| ' 8,' /* B2756431 Demand_source_type */
|| ' s.transfer_id,' /* B2756431 original_system_reference */
|| ' s.transfer_id,' /* B2756431 original_system_line_reference */
|| ' 2'
|| ' FROM '
|| ' ic_xfer_mst' ||pdblink|| ' s,'
|| ' (select distinct aps_item_id, item_id, whse_code, organization_id '
|| ' ,experimental_ind from gmp_item_aps'||pdblink||') i'
|| ' WHERE '
|| ' s.item_id = i.item_id '
|| ' and s.from_warehouse = i.whse_code '
|| ' and s.transfer_status IN (1) '
--Bug: 6242499 Vpedarla - commented the below line
-- || ' and s.from_warehouse = :pwhse_code '
|| ' and s.release_quantity1 <> 0';
/* Abhay Now we should put an insert into the Designator table too */
stmt_design := 'INSERT into msc_st_designators ( '
|| ' designator, '
|| ' forecast_set, '
|| ' organization_id, '
|| ' sr_instance_id, '
|| ' description, '
|| ' mps_relief, '
|| ' inventory_atp_flag, '
|| ' designator_type, '
|| ' disable_date, '
|| ' consume_forecast, '
|| ' update_type, '
|| ' backward_update_time_fence, '
|| ' forward_update_time_fence, '
|| ' bucket_type, '
|| ' deleted_flag, '
|| ' refresh_id ) '
|| ' SELECT '
|| ' :p_doc_type ||:p_delimitor|| organization_code, ' /* Designator */
|| ' NULL, ' /* forecast set */
|| ' organization_id, ' /* org id */
|| ' :p_instance_id, ' /* inst_id */
|| ' :p_desgnDesc , ' /* description */
|| ' 2, ' /* mps_relief */
|| ' 2, ' /* atp_flag */
|| ' 1, ' /* desgn_type */
|| ' To_date(NULL) , ' /* disable_date */
|| ' 2, ' /* do not consume ??*/
|| ' 6, ' /* update_type */
|| ' 0, ' /* backward_fence */
|| ' 0, ' /* forward_fence */
|| ' 1, ' /* bucket_type */
|| ' 2, ' /* deleted_flag */
|| ' 0 ' /* refresh_id */
|| ' FROM mtl_parameters'||pdblink
|| ' WHERE process_enabled_flag = ' || '''Y''' ;
* This procedure will insert records into the table msc_st_supplies
* and msc_st_demands for pending inventory transfers.
* HISTORY
* 25-Jan-2003 B1332662 Created New procedure to insert supplies
* Per discussions with APS team the specifics are
* Order_type = 2
* Transaction_id = transafer_id of the transfer in OPM
************************************************************************/
PROCEDURE extract_inv_transfer_supplies(
pdblink IN VARCHAR2,
pinstance_id IN NUMBER,
prun_date IN DATE,
pdelimiter IN VARCHAR2,
return_status IN OUT NOCOPY BOOLEAN)
AS
pdoc_type VARCHAR2(4) ;
v_sql_stmt := 'INSERT into msc_st_supplies ('
|| ' plan_id,'
|| ' inventory_item_id,'
|| ' organization_id,'
|| ' sr_instance_id,'
|| ' source_sr_instance_id,'
|| ' new_schedule_date,'
|| ' order_type,'
|| ' order_number,'
|| ' lot_number,'
|| ' firm_planned_type,'
|| ' deleted_flag,'
|| ' subinventory_code,'
|| ' transaction_id,' /* being added for B2756431 */
|| ' disposition_id,' /* being added for B2756431 */
|| ' po_line_id,' /* being added for B2756431 */
|| ' source_organization_id,' /* being added for B2756431 */
|| ' new_order_quantity)'
|| ' SELECT '
|| ' -1,'
|| ' i.aps_item_id,'
|| ' i.organization_id,'
|| ' :pinstance_id, '
|| ' :pinstance_id, '
|| ' s.scheduled_receive_date, '
|| ' DECODE(s.transfer_status,1,2,11), ' /* modified as per bug 12721880 Old value was hard coded 2 */
|| ' :pdoc_type || :pdelimiter || s.orgn_code ||'
|| ' :pdelimiter2 || s.transfer_no, '
|| ' DECODE(s.lot_id, 0, NULL, '
|| ' substrb(l.lot_no||DECODE(l.sublot_no, NULL,NULL ,:pdelimiter3 || '
|| ' l.sublot_no),1,30)),'
|| ' 2,'
|| ' 2,'
|| ' s.to_warehouse,'
|| ' s.transfer_id,' /* B2756431 transaction_id */
|| ' s.transfer_id,' /* B2756431 disposition_id */
|| ' s.transfer_id,' /* B2756431 po_line_id */
|| ' w.mtl_organization_id,' /* B2756431 source_organization_id */
|| ' s.release_quantity1'
|| ' FROM '
|| ' ic_xfer_mst' ||pdblink|| ' s,'
|| ' ic_whse_mst' ||pdblink|| ' w,'
|| ' ic_lots_mst'||pdblink||' l,'
|| ' (select distinct aps_item_id, item_id, whse_code, organization_id '
|| ' ,experimental_ind from gmp_item_aps'||pdblink||') i'
|| ' WHERE '
|| ' s.item_id = i.item_id '
|| ' and s.to_warehouse = i.whse_code '
|| ' and s.from_warehouse = w.whse_code '
|| ' and s.transfer_status IN (1,2) '
|| ' and s.lot_id = l.lot_id'
|| ' and s.item_id = l.item_id'
|| ' and s.release_quantity1 <> 0';
* for the current schedule/whse. The rows will be inserted into the
* database in the procedure sales_forecast which calls this procedure.
* A unique designator must be created for each schedule/whse otherwise a
* number is added to make it unique. If the row exists already the value
* is returned otherwise the table is added to and the new value is returned
* in the out parameter
* HISTORY
* M Craig
************************************************************************/
PROCEDURE build_designator(
poccur IN NUMBER,
pdelimiter IN VARCHAR2,
pdesignator OUT NOCOPY VARCHAR2)
AS
temp_designator VARCHAR2(10);
if a row has alreday been inserted for the schedule and warehouse
use the value from that row and stop the loop
*/
IF desig_tab(i).schedule = sched_dtl_tab(poccur).schedule and
desig_tab(i).whse_code = sched_dtl_tab(poccur).whse_code THEN
pdesignator := desig_tab(i).designator;
* which demands are inserted. The standard procedure inserts into
* msc_st_demands.
* This new procedure inserts into gmp_demands_api. The difference between
* the two tables is the addition of a schedule_id column in
* gmp_demands_api. Also, this version of sales_forecast begins by
* truncating gmp_demands_api and leaves it populated after
* it completes. By contrast, msc_st_demands (which is an APS staging table)
* is immediately truncated after APS reads its data. This difference allows
* gmp_demands_api to be a general purpose version of msc_st_demands.
*
* HISTORY
* P. Dong
* 09/14/01 - Created
* 12/21/01 - Replaced TRUNCATE with DELETE
************************************************************************/
PROCEDURE sales_forecast_api(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_cp_enabled IN BOOLEAN ,
p_run_date IN DATE )
AS
lv_cp_enabled BOOLEAN;
DELETE FROM gmp_demands_api;
* 10/13/99 - Sridhar Added Designator Type column in the insert statement
* 12/17/99 - Changes made to the insert statement for designators,
* changed desig_tab(1).schedule and desig_tab(1).whse_code to
* desig_tab(i).schedule and desig_tab(i).whse_code
* 04/01/00 - Code Fix for Bug# 1137597.
* 07/01/00 - Code Fix for Error in Designators Insert
*
* 02-MAY-2002 Re-engineered By : Abhay Satpute, Rajesh Patangya
* Brief Logic of the new code
* Fetch the following data into PL/SQL tables
* a. Distinct schd/item/whse combinations
* b. Sales order details
* c. Forecast details
* d. Schedule forecast associations
* For each item combination loop through and
* For each change of schedule change mark reuqired
* forecast rows as well note down the stock and ord ind.
* For each item insert sales orders, unconsumed forecast
* or the forecast , based on the indicators
* P Dong
* 09/14/01 - Added api_mode to pass to insert_demands
*
* Navin 21-APR-2003 B3577871 ST:OSFME2: collections failing in planning data pull.
* Added handling of NO_DATA_FOUND Exception.
* And return the return_status as TRUE.
****************************************************************************/
PROCEDURE sales_forecast( pdblink IN VARCHAR2,
pinstance_id IN NUMBER,
prun_date IN DATE,
pdelimiter IN VARCHAR2,
return_status IN OUT NOCOPY BOOLEAN,
api_mode IN BOOLEAN)
AS
TYPE gmp_cursor_typ IS REF CURSOR;
/* If forecast and sales order select queries have joins with gmp_item_aps
we need to select only schedules and warehouses here
ORDERED By Schedule , Aps_Item, Organization_id(Warehouse) */
/* Extract Schedule Details */
v_item_sql_stmt := 'SELECT DISTINCT'
|| ' h.schedule,'
|| ' h.schedule_id,'
|| ' h.order_ind,'
|| ' h.stock_ind,'
|| ' a.whse_code,'
|| ' d.orgn_code,'
|| ' a.organization_id, '
|| ' a.aps_item_id inventory_item_id'
|| ' FROM'
|| ' ps_schd_hdr'||pdblink||' h,'
|| ' ps_schd_dtl'||pdblink||' d,'
|| ' gmp_item_aps'||pdblink||' a'
|| ' WHERE'
|| ' h.schedule_id = d.schedule_id'
|| ' and d.orgn_code = a.plant_code'
|| ' and h.active_ind = 1'
|| ' and a.replen_ind = 1'
|| ' and (h.order_ind = 1 or h.stock_ind = 1)'
|| ' and h.delete_mark = 0'
|| ' and a.item_id > 0 ' ;
v_sales_sql_stmt := 'SELECT '
|| ' msi.inventory_item_id, '
|| ' msi.organization_id, '
|| ' h.orgn_code, '
|| ' h.order_no, '
|| ' d.line_id, '
|| ' d.net_price, '
|| ' d.sched_shipdate, '
|| ' d.requested_shipdate, ' /* B2971996 */
|| ' (sum(t.trans_qty) * -1) trans_qty '
|| ' FROM '
|| ' mtl_system_items'||pdblink||' msi, '
|| ' ic_item_mst'||pdblink||' iim,'
|| ' ic_whse_mst'||pdblink||' wm, '
|| ' op_ordr_hdr'||pdblink||' h, '
|| ' op_ordr_dtl'||pdblink||' d, '
|| ' ic_tran_pnd'||pdblink||' t '
|| ' WHERE '
|| ' msi.organization_id = wm.mtl_organization_id '
|| ' AND msi.segment1 = iim.item_no '
|| ' and wm.delete_mark = 0 '
|| ' and h.order_id = d.order_id '
|| ' and h.order_status = 0 '
|| ' and h.delete_mark = 0 '
|| ' and h.order_id = t.doc_id '
|| ' and d.line_status >= 0 '
|| ' and d.line_status < 20 '
|| ' and h.from_whse = wm.whse_code '
|| ' and t.line_id = d.line_id '
|| ' and t.item_id = d.item_id '
|| ' and iim.item_id = t.item_id '
|| ' and iim.delete_mark = 0 '
|| ' AND iim.inactive_ind = 0 '
|| ' and t.trans_qty <> 0 '
|| ' and t.completed_ind = 0 '
|| ' and t.delete_mark = 0 '
|| ' and t.doc_type = :popso '
|| ' GROUP BY '
|| ' msi.inventory_item_id, '
|| ' msi.organization_id, '
|| ' h.orgn_code, '
|| ' h.order_no, '
|| ' d.line_id, '
|| ' d.net_price, '
|| ' d.sched_shipdate, '
|| ' d.requested_shipdate ' /* B2971996 */
|| ' UNION ALL '
|| ' SELECT '
|| ' items.inventory_item_id, '
|| ' items.organization_id, '
|| ' org.organization_code, '
|| ' TO_CHAR(hdr.order_number), '
|| ' TO_NUMBER(NULL), '
|| ' TO_NUMBER(NULL), '
|| ' mtl.requirement_date, '
|| ' dtl.request_date, ' /* B2971996 */
|| ' mtl.primary_uom_quantity '
|| ' FROM '
|| ' mtl_demand_omoe'||pdblink||' mtl, '
|| ' mtl_system_items'||pdblink||' items, '
|| ' oe_order_headers_all'||pdblink||' hdr, '
|| ' oe_order_lines_all'||pdblink||' dtl, '
|| ' mtl_parameters'||pdblink||' org '
|| ' WHERE '
|| ' items.organization_id = mtl.organization_id '
|| ' and items.inventory_item_id = mtl.inventory_item_id '
|| ' and NVL(mtl.completed_quantity,0) = 0 '
|| ' and mtl.open_flag = ' || '''Y'''
|| ' and mtl.available_to_mrp = 1 '
|| ' and mtl.parent_demand_id is NULL '
|| ' and mtl.demand_source_type IN (2,8) '
|| ' and mtl.demand_id = dtl.line_id '
|| ' and dtl.header_id = hdr.header_id '
-- B2743626, Changed the join to take process sales order (OMSO)
|| ' and dtl.ship_from_org_id = org.organization_id '
|| ' and org.process_enabled_flag = ' || '''Y'''
|| ' and NOT EXISTS '
|| ' (SELECT 1 '
|| ' FROM so_lines_all'||pdblink||' sl,'
|| ' so_lines_all'||pdblink||' slp,'
|| ' mtl_demand_omoe'||pdblink||' dem'
|| ' WHERE '
|| ' slp.line_id(+) = nvl(sl.parent_line_id,sl.line_id) '
|| ' and to_number(dem.demand_source_line) = sl.line_id(+) '
|| ' and dem.demand_source_type in (2,8) '
|| ' and sl.end_item_unit_number IS NULL '
|| ' and slp.end_item_unit_number IS NULL '
|| ' and dem.demand_id = mtl.demand_id '
|| ' and items.effectivity_control = 2) '
|| ' ORDER BY 1,2,7 DESC ' ;
v_forecast_sql_stmt := 'SELECT '
|| ' msi.inventory_item_id, '
|| ' msi.organization_id, '
|| ' h.forecast_id, '
|| ' h.forecast, '
|| ' d.orgn_code, '
|| ' d.trans_date, '
|| ' (sum(d.trans_qty * -1) ) trans_qty, '
|| ' (sum(d.trans_qty * -1) ) consumed_qty ,'
|| ' 0 use_fcst_flag '
|| ' FROM '
|| ' mtl_system_items'||pdblink||' msi, '
|| ' ic_item_mst'||pdblink||' iim, '
|| ' ic_whse_mst'||pdblink||' wm, '
|| ' fc_fcst_hdr'||pdblink||' h, '
|| ' fc_fcst_dtl'||pdblink||' d '
|| ' WHERE '
|| ' msi.organization_id = wm.mtl_organization_id ' ;
|| ' and wm.delete_mark = 0 '
|| ' and h.forecast_id = d.forecast_id '
|| ' and d.forecast_id > 0 '
|| ' and d.item_id = iim.item_id '
|| ' and d.whse_code = wm.whse_code '
|| ' and d.orgn_code = wm.orgn_code '
|| ' and h.delete_mark = 0 '
|| ' and d.delete_mark = 0 '
|| ' and d.trans_qty <> 0 '
|| ' and d.trans_date >= sysdate '
|| ' and EXISTS (SELECT 1 FROM '
|| ' ps_schd_for'||pdblink||' sf, '
|| ' ps_schd_hdr'||pdblink||' sh '
|| ' WHERE sh.schedule_id = sf.schedule_id '
|| ' and sh.delete_mark = 0 '
|| ' and sh.active_ind = 1 '
|| ' and sf.forecast_id = h.forecast_id) '
|| ' GROUP BY '
|| ' msi.inventory_item_id, '
|| ' msi.organization_id, '
|| ' h.forecast, '
|| ' h.forecast_id, '
|| ' d.orgn_code, '
|| ' d.trans_date '
|| ' ORDER BY msi.inventory_item_id,msi.organization_id, '
|| ' d.trans_date DESC ' ;
/* Extract Schedule Forecast Association SQL selection */
v_association_sql_stmt := 'SELECT '
|| ' schedule_id, forecast_id '
|| ' from ps_schd_for'||pdblink
|| ' ORDER BY 1,2 ' ;
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');
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');
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');
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');
Insert_Designator;
1. Sales order Record should be inserted in msc_sales_order table, this record is directly coming from APS data collection, we have to modify ceratin columns (or insert an record in mtl_demand table).
2. Reservation record should be inserted in msc_sales_order table
*/
--Bug 6047794. Added missing database links.
v_sql_stmt := 'INSERT INTO MSC_ST_SALES_ORDERS ( '
||' INVENTORY_ITEM_ID,'
||' SOURCE_INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' PRIMARY_UOM_QUANTITY,'
||' RESERVATION_TYPE, '
||' RESERVATION_QUANTITY,'
||' DEMAND_SOURCE_TYPE, '
||' DEMAND_SOURCE_HEADER_ID, '
||' COMPLETED_QUANTITY,'
||' SUBINVENTORY,'
||' DEMAND_CLASS, '
||' REQUIREMENT_DATE,'
||' DEMAND_SOURCE_LINE, '
||' SOURCE_DEMAND_SOURCE_LINE, '
||' DEMAND_SOURCE_DELIVERY,'
||' DEMAND_SOURCE_NAME,'
||' PARENT_DEMAND_ID,'
||' DEMAND_ID,'
||' SOURCE_DEMAND_ID,'
||' SALES_ORDER_NUMBER,'
||' FORECAST_VISIBLE, '
||' DEMAND_VISIBLE,'
||' SALESREP_CONTACT,'
||' SALESREP_ID,'
||' CUSTOMER_ID,'
||' SHIP_TO_SITE_USE_ID,'
||' BILL_TO_SITE_USE_ID, '
||' REQUEST_DATE, '
||' PROJECT_ID, '
||' TASK_ID, '
||' PLANNING_GROUP,'
||' SELLING_PRICE, '
||' END_ITEM_UNIT_NUMBER, '
||' ORDERED_ITEM_ID, '
||' ORIGINAL_ITEM_ID,'
||' LINK_TO_LINE_ID ,'
||' CUST_PO_NUMBER, '
||' CUSTOMER_LINE_NUMBER,'
||' MFG_LEAD_TIME, '
||' ORG_FIRM_FLAG,'
||' SHIP_SET_ID, '
||' ARRIVAL_SET_ID,'
||' SHIP_SET_NAME, '
||' ARRIVAL_SET_NAME,'
||' ATP_REFRESH_NUMBER, '
||' DELETED_FLAG, '
||' ORIGINAL_SYSTEM_LINE_REFERENCE, '
||' ORIGINAL_SYSTEM_REFERENCE, '
||' CTO_FLAG, '
||' AVAILABLE_TO_MRP,'
||' DEMAND_PRIORITY,'
||' PROMISE_DATE, '
||' REFRESH_ID,'
||' SR_INSTANCE_ID, '
||' SCHEDULE_ARRIVAL_DATE, '
||' LATEST_ACCEPTABLE_DATE,'
||' SHIPPING_METHOD_CODE, '
||' ATO_LINE_ID,'
||' ORDER_DATE_TYPE_CODE,'
||' INTRANSIT_LEAD_TIME '
||' ) '
||' SELECT '
||' OOL.INVENTORY_ITEM_ID, '
||' OOL.INVENTORY_ITEM_ID SOURCE_INVENTORY_ITEM_ID, '
||' OOL.ORGANIZATION_ID, '
||' (t.trans_qty * -1) PRIMARY_UOM_QUANTITY, '
||' 2 RESERVATION_TYPE, '
||' TO_NUMBER(NULL) RESERVATION_QUANTITY, '
||' decode(ool.SOURCE_DOCUMENT_TYPE_ID,10,8,2) DEMAND_SOURCE_TYPE, '
||' so.SALES_ORDER_ID DEMAND_SOURCE_HEADER_ID, '
||' 0 COMPLETED_QUANTITY, '
||' TO_CHAR(NULL) SUBINVENTORY, '
||' OOL.DEMAND_CLASS, '
||' OOL.SCHEDULE_SHIP_DATE REQUIREMENT_DATE, '
||' TO_CHAR(OOL.LINE_ID) DEMAND_SOURCE_LINE, '
||' TO_CHAR(OOL.LINE_ID) SOURCE_DEMAND_SOURCE_LINE, '
||' TO_CHAR(NULL) DEMAND_SOURCE_DELIVERY, '
||' TO_CHAR(NULL) DEMAND_SOURCE_NAME, '
||' TO_NUMBER(NULL) PARENT_DEMAND_ID, '
||' MTL_DEMAND_S.nextval DEMAND_ID, '
||' MTL_DEMAND_S.currval SOURCE_DEMAND_ID, '
||' so.Concatenated_Segments, '
||' ''Y'' , '
||' ''Y'' , '
||' TO_CHAR(NULL) Salesrep_Contact, '
||' ool.salesrep_id, '
||' ool.CUSTOMER_ID, '
||' ool.SHIP_TO_SITE_ID, '
||' ool.BILL_TO_SITE_ID, '
||' ool.REQUEST_DATE, '
||' ool.project_id, '
||' ool.task_id, '
||' TO_CHAR(NULL) PLANNING_GROUP, '
||' ool.LIST_PRICE * decode( GL_CURRENCY_API.get_rate_sql'||pdblink||' ( '
||' h.transactional_curr_code, '
||' gsb.currency_code, '
||' h.booked_date, '
||' nvl(h.conversion_type_code, :b_prof1 )), '
||' -2,1,-1,1, '
||' GL_CURRENCY_API.get_rate_sql'||pdblink||' ( '
||' h.transactional_curr_code, '
||' gsb.currency_code, '
||' h.booked_date, '
||' nvl(h.conversion_type_code, :b_prof2 )) '
||' ) LIST_PRICE, '
||' ool.end_item_unit_number, '
||' DECODE(DECODE(ool.ITEM_TYPE_CODE, '
||' ''CLASS'',2, '
||' ''CONFIG'',4, '
||' ''MODEL'',1, '
||' ''OPTION'' ,3, '
||' ''STANDARD'',6, -1), 1, ool.inventory_item_id, NULL) ORDERED_ITEM_ID, '
||' decode(ool.ORIGINAL_INVENTORY_ITEM_ID,-1,to_number(null), '
||' decode(ool.ITEM_RELATIONSHIP_TYPE,-1,to_number(null), '
||' 2, ool.ORIGINAL_INVENTORY_ITEM_ID, '
||' null,ool.ORIGINAL_INVENTORY_ITEM_ID, '
||' to_number(null)) '
||' ) ORIGINAL_ITEM_ID, '
||' TO_NUMBER(NULL) LINK_TO_LINE_ID, '
||' nvl(ool.CUST_PO_NUMBER,''-1'') CUST_PO_NUMBER, '
||' nvl(ool.CUSTOMER_LINE_NUMBER,''-1'') CUSTOMER_LINE_NUMBER, '
||' ool.mfg_lead_time, '
||' decode(ool.firm_demand_flag,NULL,to_number(null),''Y'',1,2) FIRM_DEMAND_FLAG, '
||' ool.SHIP_SET_ID, '
||' ool.ARRIVAL_SET_ID, '
||' mrp_cl_function.get_ship_set_name(ool.SHIP_SET_ID) SHIP_SET_NAME, '
||' mrp_cl_function.get_arrival_set_name(ool.ARRIVAL_SET_ID) ARRIVAL_SET_NAME, '
||' TO_NUMBER(NULL) ATP_REFRESH_NUMBER, '
||' 2 DELETED_FLAG, '
||' ool.original_system_reference, '
||' ool.original_system_line_reference, '
||' 2 CTO_FLAG, '
||' TO_NUMBER(NULL) available_to_mrp, '
||' ool.DEMAND_PRIORITY, '
||' ool.PROMISE_DATE, '
||' TO_NUMBER(NULL) refresh_id, '
||' :instance_id, '
||' ool.SCHEDULE_ARRIVAL_DATE, '
||' ool.LATEST_ACCEPTABLE_DATE, '
||' ool.SHIPPING_METHOD_CODE, '
||' ool.ATO_LINE_ID, '
||' decode(h.ORDER_DATE_TYPE_CODE,''ARRIVAL'',2,1) ORDER_DATE_TYPE_CODE, '
||' OOL.DELIVERY_LEAD_TIME '
||' FROM '
||' MRP_SN_SYS_ITEMS'||pdblink||' msik, '
||' MTL_SALES_ORDERS_KFV'||pdblink||' so, '
||' OE_ORDER_HEADERS_ALL'||pdblink||' h, '
||' GL_SETS_OF_BOOKS'||pdblink||' gsb, '
||' AR_SYSTEM_PARAMETERS_ALL'||pdblink||' aspa , '
||' IC_TRAN_PND'||pdblink||' t , '
||' GMP_ITEM_APS'||pdblink||' i , '
||' MRP_SN_ODR_LINES'||pdblink||' ool '
||' WHERE '
||' t.item_id = i.item_id '
||' AND t.doc_type = :p_doc_type '
||' AND ool.line_id = t.line_id '
||' AND ( t.lot_id <> 0 OR t.location <> :p_prof ) '
||' AND t.whse_code = i.whse_code '
||' AND t.orgn_code = i.plant_code '
||' AND t.completed_ind = 0 '
||' AND t.trans_qty <> 0 '
||' AND t.delete_mark = 0 '
||' AND msik.inventory_item_id = i.aps_item_id '
||' AND msik.organization_id = i.organization_id '
||' AND so.Sales_Order_ID = t.doc_id '
||' AND ool.header_id = h.header_id(+) '
||' AND h.org_id = aspa.org_id(+) '
||' AND aspa.set_of_books_id = gsb.set_of_books_id(+) '
||' AND h.org_id is not null ' ;
v_sql_stmt := 'INSERT INTO MSC_ST_SALES_ORDERS ( '
||' INVENTORY_ITEM_ID,'
||' SOURCE_INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' PRIMARY_UOM_QUANTITY,'
||' RESERVATION_TYPE, '
||' RESERVATION_QUANTITY,'
||' DEMAND_SOURCE_TYPE, '
||' DEMAND_SOURCE_HEADER_ID, '
||' COMPLETED_QUANTITY,'
||' SUBINVENTORY,'
||' DEMAND_CLASS, '
||' REQUIREMENT_DATE,'
||' DEMAND_SOURCE_LINE, '
||' SOURCE_DEMAND_SOURCE_LINE, '
||' DEMAND_SOURCE_DELIVERY,'
||' DEMAND_SOURCE_NAME,'
||' PARENT_DEMAND_ID,'
||' DEMAND_ID,'
||' SOURCE_DEMAND_ID,'
||' SALES_ORDER_NUMBER,'
||' FORECAST_VISIBLE, '
||' DEMAND_VISIBLE,'
||' SALESREP_CONTACT,'
||' SALESREP_ID,'
||' CUSTOMER_ID,'
||' SHIP_TO_SITE_USE_ID,'
||' BILL_TO_SITE_USE_ID, '
||' REQUEST_DATE, '
||' PROJECT_ID, '
||' TASK_ID, '
||' PLANNING_GROUP,'
||' SELLING_PRICE, '
||' END_ITEM_UNIT_NUMBER, '
||' ORDERED_ITEM_ID, '
||' ORIGINAL_ITEM_ID,'
||' LINK_TO_LINE_ID ,'
||' CUST_PO_NUMBER, '
||' CUSTOMER_LINE_NUMBER,'
||' MFG_LEAD_TIME, '
||' ORG_FIRM_FLAG,'
||' SHIP_SET_ID, '
||' ARRIVAL_SET_ID,'
||' SHIP_SET_NAME, '
||' ARRIVAL_SET_NAME,'
||' ATP_REFRESH_NUMBER, '
||' DELETED_FLAG, '
||' ORIGINAL_SYSTEM_LINE_REFERENCE, '
||' ORIGINAL_SYSTEM_REFERENCE, '
||' CTO_FLAG, '
||' AVAILABLE_TO_MRP,'
||' DEMAND_PRIORITY,'
||' PROMISE_DATE, '
||' REFRESH_ID,'
||' SR_INSTANCE_ID, '
||' SCHEDULE_ARRIVAL_DATE, '
||' LATEST_ACCEPTABLE_DATE,'
||' SHIPPING_METHOD_CODE, '
||' ATO_LINE_ID,'
||' ORDER_DATE_TYPE_CODE,'
||' INTRANSIT_LEAD_TIME '
||' ) '
||' SELECT '
||' OOL.INVENTORY_ITEM_ID, '
||' OOL.INVENTORY_ITEM_ID SOURCE_INVENTORY_ITEM_ID, '
||' OOL.ORGANIZATION_ID, '
||' (t.trans_qty * -1 ) PRIMARY_UOM_QUANTITY, '
||' 2 RESERVATION_TYPE, '
||' TO_NUMBER(NULL) RESERVATION_QUANTITY, '
||' decode(ool.SOURCE_DOCUMENT_TYPE_ID,10,8,2) DEMAND_SOURCE_TYPE, '
||' so.SALES_ORDER_ID DEMAND_SOURCE_HEADER_ID, '
||' 0 COMPLETED_QUANTITY, '
||' TO_CHAR(NULL) SUBINVENTORY, '
||' OOL.DEMAND_CLASS, '
||' OOL.SCHEDULE_SHIP_DATE REQUIREMENT_DATE, '
||' TO_CHAR(OOL.LINE_ID) DEMAND_SOURCE_LINE, '
||' TO_CHAR(OOL.LINE_ID) SOURCE_DEMAND_SOURCE_LINE, '
||' TO_CHAR(NULL) DEMAND_SOURCE_DELIVERY, '
||' TO_CHAR(NULL) DEMAND_SOURCE_NAME, '
||' TO_NUMBER(NULL) PARENT_DEMAND_ID, '
||' MTL_DEMAND_S.nextval DEMAND_ID, '
||' MTL_DEMAND_S.currval SOURCE_DEMAND_ID, '
||' so.Concatenated_Segments, '
||' ''Y'' , '
||' ''Y'' , '
||' TO_CHAR(NULL) Salesrep_Contact, '
||' ool.salesrep_id, '
||' ool.CUSTOMER_ID, '
||' ool.SHIP_TO_SITE_ID, '
||' ool.BILL_TO_SITE_ID, '
||' ool.REQUEST_DATE, '
||' ool.project_id, '
||' ool.task_id, '
||' TO_CHAR(NULL) PLANNING_GROUP, '
||' ool.LIST_PRICE * decode( GL_CURRENCY_API.get_rate_sql'||pdblink||' ( '
||' h.transactional_curr_code, '
||' gsb.currency_code, '
||' h.booked_date, '
||' nvl(h.conversion_type_code, :b_prof1 )), '
||' -2,1,-1,1, '
||' GL_CURRENCY_API.get_rate_sql'||pdblink||' ( '
||' h.transactional_curr_code, '
||' gsb.currency_code, '
||' h.booked_date, '
||' nvl(h.conversion_type_code, :b_prof2 )) '
||' ) LIST_PRICE, '
||' ool.end_item_unit_number, '
||' DECODE(DECODE(ool.ITEM_TYPE_CODE, '
||' ''CLASS'',2, '
||' ''CONFIG'',4, '
||' ''MODEL'',1, '
||' ''OPTION'' ,3, '
||' ''STANDARD'',6, -1), 1, ool.inventory_item_id, NULL) ORDERED_ITEM_ID, '
||' decode(ool.ORIGINAL_INVENTORY_ITEM_ID,-1,to_number(null), '
||' decode(ool.ITEM_RELATIONSHIP_TYPE,-1,to_number(null), '
||' 2, ool.ORIGINAL_INVENTORY_ITEM_ID, '
||' null,ool.ORIGINAL_INVENTORY_ITEM_ID, '
||' to_number(null)) '
||' ) ORIGINAL_ITEM_ID, '
||' TO_NUMBER(NULL) LINK_TO_LINE_ID, '
||' nvl(ool.CUST_PO_NUMBER,''-1'') CUST_PO_NUMBER, '
||' nvl(ool.CUSTOMER_LINE_NUMBER,''-1'') CUSTOMER_LINE_NUMBER, '
||' ool.mfg_lead_time, '
||' decode(ool.firm_demand_flag,NULL,to_number(null),''Y'',1,2) FIRM_DEMAND_FLAG, '
||' ool.SHIP_SET_ID, '
||' ool.ARRIVAL_SET_ID, '
||' mrp_cl_function.get_ship_set_name(ool.SHIP_SET_ID) SHIP_SET_NAME, '
||' mrp_cl_function.get_arrival_set_name(ool.ARRIVAL_SET_ID) ARRIVAL_SET_NAME, '
||' TO_NUMBER(NULL) ATP_REFRESH_NUMBER, '
||' 2 DELETED_FLAG, '
||' ool.original_system_reference, '
||' ool.original_system_line_reference, '
||' 2 CTO_FLAG, '
||' TO_NUMBER(NULL) available_to_mrp, '
||' ool.DEMAND_PRIORITY, '
||' ool.PROMISE_DATE, '
||' TO_NUMBER(NULL) refresh_id, '
||' :instance_id, '
||' ool.SCHEDULE_ARRIVAL_DATE, '
||' ool.LATEST_ACCEPTABLE_DATE, '
||' ool.SHIPPING_METHOD_CODE, '
||' ool.ATO_LINE_ID, '
||' decode(h.ORDER_DATE_TYPE_CODE,''ARRIVAL'',2,1) ORDER_DATE_TYPE_CODE, '
||' OOL.DELIVERY_LEAD_TIME '
||' FROM '
||' MRP_SN_SYS_ITEMS'||pdblink||' msik, '
||' MTL_SALES_ORDERS_KFV'||pdblink||' so, '
||' OE_ORDER_HEADERS_ALL'||pdblink||' h, '
||' GL_SETS_OF_BOOKS'||pdblink||' gsb, '
||' AR_SYSTEM_PARAMETERS_ALL'||pdblink||' aspa , '
||' IC_TRAN_PND'||pdblink||' t , '
||' GMP_ITEM_APS'||pdblink||' i , '
||' MRP_SN_ODR_LINES'||pdblink||' ool '
||' WHERE '
||' t.item_id = i.item_id '
||' AND t.doc_type = :p_doc_type '
||' AND ool.line_id = t.line_id '
||' AND ( t.lot_id <> 0 OR t.location <> :p_prof ) '
||' AND t.whse_code = i.whse_code '
||' AND t.orgn_code = i.plant_code '
||' AND t.completed_ind = 0 '
||' AND t.trans_qty <> 0 '
||' AND t.delete_mark = 0 '
||' AND msik.inventory_item_id = i.aps_item_id '
||' AND msik.organization_id = i.organization_id '
||' AND so.Sales_Order_ID = t.doc_id '
||' AND ool.header_id = h.header_id(+) '
||' AND h.org_id = nvl(aspa.org_id,-99) '
||' AND aspa.set_of_books_id = gsb.set_of_books_id(+) '
||' AND h.org_id is null ' ;
/* For Engine Reseration record inserted */
--Bug 6056320. Insert MTL_DEMAND_S.nextval as transaction_id to avoid unique constraint violation.
v_sql_stmt := 'INSERT into msc_st_reservations ('
|| ' inventory_item_id,'
|| ' organization_id,'
|| ' sr_instance_id,'
|| ' transaction_id,'
|| ' parent_demand_id,'
|| ' disposition_id ,'
|| ' requirement_date,'
|| ' reserved_quantity,'
|| ' disposition_type,'
|| ' subinventory,'
|| ' reservation_type,'
|| ' demand_class,'
|| ' available_to_mrp,'
|| ' reservation_flag,'
|| ' planning_group,'
|| ' deleted_flag'
|| ' )'
|| ' SELECT '
|| ' i.aps_item_id, '
|| ' ool.organization_id, '
|| ' :p_instance_id , '
|| ' ((MTL_DEMAND_S.nextval * 2 ) + 1), ' /* Bug 6056320. Replaced t.doc_id with MTL_DEMAND_S.nextval */
|| ' ool.line_id, '
|| ' INV_SALESORDER.GET_SALESORDER_FOR_OEHEADER'||pdblink||' ( '
|| ' ool.HEADER_ID), '
|| ' t.trans_date , '
|| ' (t.trans_qty * -1 ), '
|| ' 2, ' /* DISPOSITION_TYPE */
|| ' NULL, ' /* SUBINVENTORY */
|| ' 1, ' /* RESERVATION_TYPE 1 is for Discrete ?? */
|| ' ool.demand_class, ' /* DEMAND_CLASS CODE */
|| ' NULL, ' /* AVAILABLE_TO_MRP */
|| ' 2, ' /* RESERVATION_FLAG */
|| ' NULL, ' /* PLANNING_GROUP */
|| ' 2 '
|| ' FROM '
|| ' ic_tran_pnd'||pdblink|| ' t,'
|| ' gmp_item_aps'||pdblink|| ' i,'
|| ' MRP_SN_ODR_LINES'||pdblink|| ' ool'
|| ' WHERE '
|| ' t.doc_type = :p_doctype '
|| ' AND t.item_id = i.item_id '
|| ' AND ool.line_id = t.line_id'
|| ' AND ( t.lot_id <> 0 OR '
|| ' t.location <> :loc_profile ) '
|| ' AND t.whse_code = i.whse_code '
|| ' AND t.orgn_code = i.plant_code '
|| ' AND t.completed_ind = 0 '
|| ' and ool.open_flag = ' || '''Y'''
|| ' AND t.trans_qty <> 0 '
|| ' AND t.delete_mark = 0 ' ;
l_insert_set_stmt VARCHAR2(32000);
l_fcst_stmt := 'SELECT '
|| ' msi.inventory_item_id, '
|| ' msi.organization_id, '
|| ' h.forecast_id, '
|| ' d.line_id, '
|| ' h.forecast, '
|| ' h.forecast_set FSET , '
|| ' d.trans_date, '
|| ' d.orgn_code, '
|| ' (d.trans_qty * -1) trans_qty, '
|| ' 0 use_fcst_flag '
|| ' FROM '
|| ' mtl_system_items'||pdblink||' msi, '
|| ' ic_item_mst'||pdblink||' iim, '
|| ' ic_whse_mst'||pdblink||' wm, '
|| ' fc_fcst_hdr'||pdblink||' h, '
|| ' fc_fcst_dtl'||pdblink||' d '
|| ' WHERE '
|| ' msi.organization_id = wm.mtl_organization_id ' ;
|| ' and wm.delete_mark = 0 '
|| ' and h.forecast_id = d.forecast_id '
|| ' and d.forecast_id > 0 '
|| ' and d.item_id = iim.item_id '
|| ' and d.whse_code = wm.whse_code '
|| ' and d.orgn_code = wm.orgn_code '
|| ' and h.forecast_set is NOT NULL '
|| ' and h.delete_mark = 0 '
|| ' and d.delete_mark = 0 '
|| ' and d.trans_qty <> 0 '
|| ' ORDER BY wm.mtl_organization_id ,FSET DESC,h.forecast_id ' ;
l_insert_set_stmt :=
' INSERT INTO msc_st_designators ( '
||' designator,forecast_set, organization_id, sr_instance_id, '
||' description, mps_relief, inventory_atp_flag, '
||' designator_type,disable_date,consume_forecast, '
||' update_type,backward_update_time_fence,forward_update_time_fence, '
||' bucket_type,deleted_flag,refresh_id ) '
||' VALUES '
||' ( :p1, :p2, :p3,:p4, '
||' :p5, :p6, :p7, '
||' :p8, :p9, :p10, '
||' :p11, :p12, :p13, '
||' :p14,:p15,:p16 ) ';
' INSERT INTO msc_st_demands ( '
||' organization_id, inventory_item_id, sr_instance_id, '
||' using_assembly_item_id, using_assembly_demand_date, '
||' using_requirement_quantity,demand_class,bucket_type, '
||' demand_type, origination_type, wip_entity_id, '
||' demand_schedule_name,forecast_designator, order_number,'
||' wip_entity_name,sales_order_line_id, selling_price, deleted_flag ) '
||' VALUES '
||' ( :p1, :p2, :p3, '
||' :p4, :p5, :p6, '
||' :p7, :p8, :p9, '
||' :p10,:p11,:p12, '
||' :p13,:p14,:p15, '
||' :p16,:p17,:p18 )' ;
l_design_stmt := 'SELECT '||
' forecast_id, '||
' forecast, '||
' substr(forecast,1,10) DESGN, '||
' nvl(forecast_set ,substr(forecast,1,10)) FSET, '||
' 1 DESGN_IND ,' ||
' consumption_ind, '||
' backward_time_fence, '||
' forward_time_fence '||
' FROM fc_fcst_hdr'||pdbLink ||
' WHERE delete_mark = 0 '||
' UNION ALL '||
-- Add forecast_sets to the list
' SELECT '||
' -1 , '||
' min(forecast), '||
' forecast_set DESGN , '||
' to_char(NULL) FSET, '||
' 3 DESGN_IND, ' ||
' to_number(NULL), '||
' to_number(NULL), '||
' to_number(NULL) '||
' FROM fc_fcst_hdr'||pdblink ||
' WHERE delete_mark = 0 '||
' AND forecast_set is NOT NULL '||
' GROUP BY forecast_set ' ||
' ORDER BY FSET, 1 DESC , DESGN_IND ' ;
' SELECT '||
' -1, '||
' forecast, '||
' substr(forecast,1,10) DESGN_IND , '||
' to_char(NULL) FSET, '||
' 2 DESGN_IND, '||
' to_number(NULL), '||
' to_number(NULL), '||
' to_number(NULL) '||
' FROM fc_fcst_hdr'||pdblink ||
' WHERE delete_mark = 0 '||
' AND forecast_set is NULL '||
-- With these changes some logic in designator generation has become redundant
*/
OPEN fcst_hdr for l_design_stmt ;
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');
-- insert set name for currrent org
write_fcst_set := TRUE ;
i_backward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).backward_time_fence ;
i_forward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).forward_time_fence ;
i_backward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).backward_time_fence ;
i_forward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).forward_time_fence ;
/* Demands Bulk inserts */
d_index := d_index + 1 ;
/* ----------------------- Demands Insert --------------------- */
i := 1 ;
INSERT INTO msc_st_demands (
organization_id,
inventory_item_id,
sr_instance_id,
using_assembly_item_id,
using_assembly_demand_date,
using_requirement_quantity,
demand_class,
bucket_type,
demand_type,
origination_type,
wip_entity_id,
demand_schedule_name,
forecast_designator,
order_number,
wip_entity_name,
sales_order_line_id,
selling_price,
deleted_flag )
VALUES (
f_organization_id(i),
f_inventory_item_id(i),
f_sr_instance_id(i),
f_assembly_item_id(i),
f_demand_date(i),
f_requirement_quantity(i),
null_value, /* demand_class */
1, /* bucket_type */
1, /* demand_type */
29, /* origination_type */
null_value, /* wip_entity_id */
null_value, /* demand_schedule_name */
f_forecast_designator(i),
f_order_number(i),
null_value, /* wip_entity_name */
f_sales_order_line_id(i),
null_value, /* selling_price */
2 /* deleted_flag */
) ;
/* ----------------------- Designator Insert --------------------- */
i := 1 ;
INSERT INTO msc_st_designators (
designator,
forecast_set,
organization_id,
sr_instance_id,
description,
mps_relief,
inventory_atp_flag,
designator_type,
disable_date,
consume_forecast,
update_type,
backward_update_time_fence,
forward_update_time_fence,
bucket_type,
deleted_flag,
refresh_id
)
VALUES (
i_designator(i) ,
i_forecast_set(i) ,
i_organization_id(i),
i_sr_instance_id(i) ,
i_description(i) ,
0, /* mps relief */
0, /* inventory atp flag */
6, /* designator type,For forecast the value will be 6 */
i_disable_date(i) ,
i_consume_forecast(i),
6, /* Update Type,For Process value will be 6 */
i_backward_update_time_fence(i),
i_forward_update_time_fence(i) ,
1, /* bucket_type */
2, /* deleted_flag */
0 /* refresh_id */
) ;
* 09/14/01 - Added api_mode to pass to insert_demands
************************************************************************/
PROCEDURE consume_forecast( pinventory_item_id IN NUMBER,
porganization_id IN NUMBER,
papi_mode IN BOOLEAN )
AS
cfcst_cnt NUMBER ;
* and insert into the destination table
* exit when item_id changes after noting down the counter position
* HISTORY
* Created By : Rajesh Patangya
* P Dong
* 09/14/01 - Added api_mode to pass to insert_demands
************************************************************************/
PROCEDURE write_forecast( pfcst_counter IN NUMBER,
pinventory_item_id IN NUMBER,
porganization_id IN NUMBER,
papi_mode IN BOOLEAN)
AS
fcst_i NUMBER ;
* and insert into the destination table
* exit when item_id changes after noting down the counter position
* HISTORY
* Created By : Rajesh Patangya
* P Dong
* 09/14/01 - Added api_mode to pass to insert_demands
************************************************************************/
PROCEDURE write_so( pso_counter IN NUMBER,
pinventory_item_id IN NUMBER,
porganization_id IN NUMBER,
papi_mode IN BOOLEAN)
AS
so_i NUMBER ;
* insert sales order into msc_st_demand
* HISTORY
* Created By : Rajesh Patangya
* P Dong
* 09/14/01 - Added api_mode to pass to insert_demands
* 05/21/03 - B2971996 - Populating request_date in msc_st_demands table
************************************************************************/
PROCEDURE write_this_so(pcounter IN NUMBER,
sapi_mode IN BOOLEAN)
AS
statement_demands_api VARCHAR2(32000) ;
' INSERT INTO gmp_demands_api ( '
||' organization_id, schedule_id, inventory_item_id, demand_date, '
||' demand_quantity, origination_type, doc_id, selling_price ) '
||' VALUES '
||' ( :p1, :p2, :p3, :p4, '
||' :p5, :p6, :p7, :p8 ) ';
log_message('Failure occured during the insert into gmp_demands_api');
' INSERT INTO msc_st_demands ( '
||' organization_id, inventory_item_id, sr_instance_id, '
||' using_assembly_item_id, using_assembly_demand_date, '
||' using_requirement_quantity, demand_type, origination_type, '
||' wip_entity_id, demand_schedule_name, order_number, '
||' wip_entity_name, selling_price,request_date,deleted_flag ) ' /*B2971996*/
||' VALUES '
||' ( :p1, :p2, :p3, '
||' :p4, :p5, '
||' :p6, :p7, :p8 , '
||' :p9, :p10,:p11, '
||' :p12,:p13,:p14,:p15 )' ;
* insert forecast into msc_st_demand
* HISTORY
* Created By : Rajesh Patangya
* P Dong
* 09/14/01 - Added api_mode to pass to insert_demands
************************************************************************/
PROCEDURE write_this_fcst(pcounter IN NUMBER,
fapi_mode IN BOOLEAN)
AS
statement_demands_api VARCHAR2(32000) ;
' INSERT INTO gmp_demands_api ( '
||' organization_id, schedule_id, inventory_item_id, demand_date, '
||' demand_quantity, origination_type, doc_id, selling_price ) '
||' VALUES '
||' ( :p1, :p2, :p3, :p4, '
||' :p5, :p6, :p7, :p8 ) ';
log_message('Failure occured during the insert into gmp_demands_api');
' INSERT INTO msc_st_demands ( '
||' organization_id, inventory_item_id, sr_instance_id, '
||' using_assembly_item_id, using_assembly_demand_date, '
||' using_requirement_quantity, demand_type, origination_type, '
||' wip_entity_id, demand_schedule_name, order_number, '
||' wip_entity_name, selling_price, deleted_flag ) '
||' VALUES '
||' ( :p1, :p2, :p3, '
||' :p4, :p5, '
||' :p6, :p7, :p8 , '
||' :p9, :p10,:p11, '
||' :p12,:p13,:p14 )' ;
* insert_designator
*
* DESCRIPTION
* Insert all the designator for schedule/item/warehouse combination
* HISTORY
* Created By : Rajesh Patangya
************************************************************************/
PROCEDURE insert_designator IS
i NUMBER ;
' INSERT INTO msc_st_designators ( '
||' designator, organization_id, sr_instance_id, '
||' description, mps_relief, inventory_atp_flag, '
||' designator_type ) '
||' VALUES '
||' ( :p1, :p2, :p3, '
||' :p4, :p5, :p6, '
||' :p7 ) ';
log_message('Failure occured in insert_designator');
END insert_designator;
* call the insert for resource requirements.
* HISTORY
* M Craig
************************************************************************/
PROCEDURE process_resource_rows(
pfirst_row IN NUMBER,
plast_row IN NUMBER,
pfound_mtl IN NUMBER,
porgn_id IN NUMBER,
pinstance_id IN NUMBER,
pinflate_wip IN NUMBER,
pmin_xfer_qty IN NUMBER)
IS
v_resource_usage NUMBER ;
/* Bulk Insert for insert_resource_requirements */
rr_index := rr_index + 1 ;
v_stmt_alt_rsrc := 'INSERT INTO MSC_ST_RESERVATIONS'
||' ( '
||' TRANSACTION_ID , '
||' INVENTORY_ITEM_ID , '
||' ORGANIZATION_ID, '
||' SR_INSTANCE_ID , '
||' REQUIREMENT_DATE , '
||' PARENT_DEMAND_ID , '
||' REVISION , '
||' DISPOSITION_ID , '
||' RESERVED_QUANTITY , '
||' DISPOSITION_TYPE , '
||' SUBINVENTORY , '
||' RESERVATION_TYPE , '
||' DEMAND_CLASS , '
||' AVAILABLE_TO_MRP , '
||' RESERVATION_FLAG , '
||' PROJECT_ID , '
||' TASK_ID , '
||' PLANNING_GROUP , '
||' SUPPLY_SOURCE_HEADER_ID , '
||' SUPPLY_SOURCE_TYPE_ID , '
||' DELETED_FLAG '
||' ) '
||' SELECT '
||' ((gbo.batch_res_id * 2) + 1), '
||' gia.aps_item_id , '
||' gbo.organization_id, '
||' :p1, '
||' gbo.scheduled_ship_date, '
||' gbo.so_line_id , '
||' NULL , '
||' gbo.order_id , '
||' gbo.reserved_qty , '
||' :p2 ,'
||' NULL , '
||' :p3 ,'
||' ool.demand_class_code , '
||' NULL , '
||' :p4 ,'
||' ool.project_id, '
||' ool.task_id, '
||' ppp.planning_group, '
||' ((gbo.batch_id * 2) + 1) , '
||' :p5 ,'
||' :p6 '
||' FROM '
||' gml_batch_so_reservations'||pdblink||' gbo, '
||' (SELECT '
||' DISTINCT item_id, aps_item_id, organization_id , whse_code '
||' FROM gmp_item_aps'||pdblink||') gia, '
||' oe_order_lines_all'||pdblink||' ool, '
||' pjm_project_parameters'||pdblink||' ppp '
||' WHERE '
||' gbo.item_id = gia.item_id '
||' AND gbo.organization_id = gia.organization_id '
||' AND gbo.delete_mark = 0 '
||' AND gbo.so_line_id = ool.line_id '
||' AND ool.project_id = ppp.project_id (+) ';
||' AND EXISTS ( SELECT 1 FROM sy_orgn_mst'||pdblink||' som '
||' WHERE gia.whse_code = som.resource_whse_code )' ;
log_message('Failure occured during the insert into msc_st_reservations');
* update_last_setup_id
*
* DESCRIPTION
* This procedure is triggered by the concurrent program for
* updating the last setup id.
*
* HISTORY
* Namit 14-09-2004 Procedure Created
************************************************************************/
PROCEDURE update_last_setup_id (
effbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
f_orgn_code IN VARCHAR2,
t_orgn_code IN VARCHAR2
)
IS
TYPE ref_cursor_typ IS REF CURSOR;
x_select VARCHAR2(32000);
lsetup_updated BOOLEAN;
x_select := NULL;
lsetup_updated := TRUE;
X_select := ' SELECT '
||' gbsr.sequence_dependent_id, '
||' crd.resource_id, '
||' grt.instance_id, '
||' crd.orgn_code, '
||' gbsr.batch_id '
||' FROM gme_batch_step_resources gbsr, '
||' gme_resource_txns grt, '
||' sy_orgn_usr sou, '
||' cr_rsrc_dtl crd, '
||' gme_batch_header gbh '
||' WHERE gbsr.batch_id = grt.doc_id '
||' AND gbh.batch_id = gbsr.batch_id '
||' AND gbh.plant_code = crd.orgn_code '
||' AND crd.orgn_code = sou.orgn_code '
||' AND sou.user_id = :user_id '
||' AND gbsr.batchstep_resource_id = grt.line_id '
||' AND grt.completed_ind = 1 '
||' AND crd.resources = gbsr.resources '
||' AND crd.resources = grt.resources '
||' AND crd.schedule_ind = 2 '
||' AND grt.instance_id IS NOT NULL '
||' AND crd.delete_mark = 0 ';
x_select := x_select
||' AND crd.orgn_code >= :frm_orgn ' ;
x_select := x_select
||' AND crd.orgn_code <= :to_orgn ' ;
x_select := x_select
||' ORDER BY grt.resources, grt.instance_id, '
||' grt.end_date DESC, grt.poc_trans_id ' ;
OPEN cur_lsetup_id FOR x_select USING l_user_id, f_orgn_code, t_orgn_code;
OPEN cur_lsetup_id FOR x_select USING l_user_id, f_orgn_code;
OPEN cur_lsetup_id FOR x_select USING l_user_id, t_orgn_code;
OPEN cur_lsetup_id FOR x_select USING l_user_id;
lsetup_updated := FALSE;
IF NOT (lsetup_updated) THEN
lsetup_updated := TRUE;
UPDATE gmp_resource_instances gri
SET gri.last_setup_id = v_last_setup_id
WHERE gri.resource_id = v_resource_id
AND gri.instance_id = v_instance_id;
log_message(' NO_DATA_FOUND exception raised in Procedure: gmp_aps_ds_pull.update_last_setup_id ' );
END update_last_setup_id;
REM| 10/13/99 - Added deleted_flag in the insert statement |
REM| 11/23/99 - Changed value of aggregate_resource_flag from 1 to 2 |
REM| 01/12/00 - Added owning_department_id column in the Insert statement |
REM| - Bug# 1140113 |
REM| 4/03/00 - using mtl_organization_id from ic_whse_mst instead of |
REM| - organization_id from sy_orgn_mst - Bug# 1252322 |
REM| 4/18/00 - Fixed Bug# 1273557 - Department count is Zero |
REM| - Changes made to the insert statement, changed |
REM| - s.organization_id to w.mtl_organization_id |
REM| 12/26/01 - Adding Code changes for Resource Utilization and Resource |
REM| Efficiency - B2163006 |
REM| 12/20/02 - Sridhar Gidugu B2714583, Populated 3 new columns for |
REM| msc_st_department_resources |
REM| 1.Resource_excess_type, |
REM| 2.Resource_shortage_type |
REM| 3.User_time_fence |
REM| 01/09/03 - Sridhar Gidugu Used mrp_planning_exception_sets |
REM| instead of mrp_planning_exception_sets_v |
REM| also added extra join with Organization_id |
REM| 01/22/03 - Sridhar Gidugu Insert statement for Resource Groups |
REM| 05/11/03 - Rajesh Patangya Used to_number(NULL) in palce of NULL |
REM| 05/20/03 - Sridhar Gidugu B2971120 Populating new columns |
REM| Over_utilized_percent and |
REM| under_utilized_percent in dept_rsc table |
REM| 04/21/2004 - Navin Sinha - B3577871 -ST:OSFME2: collections failing |
REM| in planning data pull. |
REM| Added handling of NO_DATA_FOUND Exception.|
REM| And return the return_status as TRUE. |
REM| 12/30/04 - Arvind Karuppasamy - B4081551, Modified query in rsrc_extract|
REM| to select the resource description from |
REM| cr_rsrc_mst. |
REM| 02/17/05 - Teresa Wong - B4179616 Increased length of variables holding |
REM| dynamic sql stmts with string of org |
REM| codes. |
REM+==========================================================================+
*/
PROCEDURE rsrc_extract(p_instance_id IN NUMBER,
p_db_link IN VARCHAR2,
return_status OUT NOCOPY BOOLEAN) is
ins_dept_res varchar2(32000);
/* AKARUPPA 12/30/2004 B4081551 - Modified query to select resource description from cr_rsrc_mst */
stmt_no := 10 ;
ins_dept_res := ' INSERT INTO msc_st_department_resources '
|| ' ( organization_id, '
|| ' sr_instance_id, '
|| ' resource_id, '
|| ' department_id, '
|| ' resource_code, '
|| ' resource_description, '
|| ' department_code, '
|| ' owning_department_id, '
|| ' line_flag, '
|| ' aggregated_resource_flag, '
|| ' capacity_units, '
|| ' available_24_hours_flag, '
|| ' resource_cost, '
|| ' ctp_flag, '
|| ' deleted_flag, '
|| ' resource_excess_type, '
|| ' resource_shortage_type, '
|| ' user_time_fence, '
|| ' over_utilized_percent, ' /* B2971120 */
|| ' under_utilized_percent, ' /* B2971120 */
|| ' efficiency, '
|| ' utilization, '
|| ' planning_exception_set, '
|| ' resource_group_name, '
|| ' bottleneck_flag, '
|| ' chargeable_flag, '
|| ' capacity_tolerance, '
|| ' batchable_flag, '
|| ' batching_window, '
|| ' min_capacity, '
|| ' max_capacity, '
|| ' unit_of_measure, '
|| ' idle_time_tolerance, '
|| ' sds_scheduling_window, '
|| ' batching_penalty, '
|| ' schedule_to_instance, '
/*B4487118 - HLINK GC:(RV): MULTIPLE ROWS ARE DISPALYED FOR A RESOURCE IN THE RV*/
|| ' resource_type '
|| ') '
|| ' SELECT /*+ DRIVING_SITE(r) DRIVING_SITE(rsm) DRIVING_SITE(mrp) DRIVING_SITE(p) '
|| 'DRIVING_SITE(w) DRIVING_SITE(sou) */ w.mtl_organization_id , '
|| ' :instance_id, '
|| ' ((r.resource_id * 2) + 1),' /* B1177070 encoded */
|| ' ((w.mtl_organization_id * 2) + 1),' /* B1177070 encoded */
|| ' r.resources, ' /* B6522434 */
-- || ' substrb(r.resources,1,10), '
|| ' rsm.resource_desc, ' /* B4081551 */
|| ' w.whse_code , '
|| ' ((w.mtl_organization_id * 2) + 1) , ' /* B1177070 */
|| ' 2, ' /* Line Flag */
|| ' 2, ' /* Yes = 1 and No = 2 resource Flag */
|| ' r.assigned_qty, '
|| ' 2, ' /* Avail 24 hrs flag */
|| ' r.nominal_cost, '
|| ' 1,' /* for ATP to check Resources (RDP)*/
|| ' 2, '
|| ' mrp.resource_excess_type, ' /* B2714583 */
|| ' mrp.resource_shortage_type, ' /* B2714583 */
|| ' mrp.user_time_fence, ' /* B2714583 */
|| ' mrp.over_utilized_percent, ' /* B2971120 */
|| ' mrp.under_utilized_percent, ' /* B2971120 */
|| ' r.efficiency, ' /* B2163006 */
|| ' r.utilization, ' /* B2163006 */
|| ' r.planning_exception_set, ' /* B2714583 */
|| ' r.group_resource, '
|| ' NULL, '
|| ' decode(r.capacity_constraint,1,1,2), '
|| ' r.capacity_tolerance, '
|| ' 2, ' /* batchable_flag */
|| ' NULL, '
|| ' r.min_capacity, '
|| ' r.max_capacity, '
|| ' sou.uom_code, '
|| ' idle_time_tolerence, '
|| ' sds_window, '
|| ' NULL, '
/* If the Resource is scheduled to Instance, then value is Yes else No */
|| ' decode(r.schedule_ind,2,1,2), '
/*B4487118 - HLINK GC:(RV): MULTIPLE ROWS ARE DISPALYED FOR A RESOURCE IN THE RV*/
|| ' 1 '
|| ' FROM cr_rsrc_dtl'||p_db_link||' r, '
|| ' cr_rsrc_mst'||p_db_link||' rsm, ' /* B4081551 */
|| ' mrp_planning_exception_sets'||p_db_link||' mrp, '
|| ' sy_orgn_mst'||p_db_link||' p, '
|| ' ic_whse_mst'||p_db_link||' w, '
-- Bug 6467457 modified as below ||' sy_uoms_mst'||p_db_link||' sou '
||' mtl_units_of_measure'||p_db_link||' sou '
|| ' WHERE r.orgn_code = p.orgn_code '
|| ' AND r.planning_exception_set = mrp.exception_set_name '
|| ' AND w.mtl_organization_id = mrp.organization_id '
|| ' AND r.resources = rsm.resources ' /* B4081551 */
|| ' AND p.resource_whse_code = w.whse_code ' ;
|| ' AND r.delete_mark = 0 '
|| ' AND rsm.delete_mark = 0 ' /* B4081551 */
|| ' AND p.delete_mark = 0 '
|| ' AND w.delete_mark = 0 '
-- 6467457 modified as below ||' AND sou.delete_mark = 0 '
||' AND NVL(sou.disable_date, sysdate+1) > sysdate '
-- 6467457 modified as below ||' AND sou.um_code = r.capacity_uom '
||' AND ( sou.unit_of_measure = r.capacity_uom OR sou.uom_code = r.capacity_uom) '
|| ' UNION ALL '
|| ' SELECT /*+ DRIVING_SITE(r) DRIVING_SITE(rsm) DRIVING_SITE(p) '
|| ' DRIVING_SITE(w) DRIVING_SITE(sou) */ w.mtl_organization_id , '
|| ' :instance_id1, '
|| ' ((r.resource_id * 2) + 1),' /* B1177070 encoded */
|| ' ((w.mtl_organization_id * 2) + 1),' /* B1177070 encoded */
|| ' r.resources, ' /* B6522434 */
-- || ' substrb(r.resources,1,10), '
|| ' rsm.resource_desc, ' /* B4081551 */
|| ' w.whse_code , '
|| ' ((w.mtl_organization_id * 2) + 1) , ' /* B1177070 */
|| ' 2, ' /* Line Flag */
|| ' 2, ' /* Yes = 1 and No = 2 resource Flag */
|| ' r.assigned_qty, '
|| ' 2, ' /* Avail 24 hrs flag */
|| ' r.nominal_cost, '
|| ' 1,' /* for ATP to check Resources (RDP)*/
|| ' 2, '
|| ' to_number(NULL), ' /* B2714583 */
|| ' to_number(NULL), ' /* B2714583 */
|| ' to_number(NULL), ' /* B2714583 */
|| ' to_number(NULL), ' /* B2971120 */
|| ' to_number(NULL), ' /* B2971120 */
|| ' r.efficiency, ' /* B2163006 */
|| ' r.utilization, ' /* B2163006 */
|| ' r.planning_exception_set, ' /* B2714583 */
|| ' r.group_resource, '
|| ' NULL, '
|| ' decode(r.capacity_constraint,1,1,2), '
|| ' r.capacity_tolerance, '
|| ' 2, ' /* batchable_flag */
|| ' NULL, '
|| ' r.min_capacity, '
|| ' r.max_capacity, '
|| ' sou.uom_code, '
|| ' idle_time_tolerence, '
|| ' sds_window, '
|| ' NULL, '
/* If the Resource is scheduled to Instance, then value is Yes else No */
|| ' decode(r.schedule_ind,2,1,2), '
/*B4487118 - HLINK GC:(RV): MULTIPLE ROWS ARE DISPALYED FOR A RESOURCE IN THE RV*/
|| ' 1 '
|| ' FROM cr_rsrc_dtl'||p_db_link||' r, '
|| ' cr_rsrc_mst'||p_db_link||' rsm, ' /* B4081551 */
|| ' sy_orgn_mst'||p_db_link||' p, '
|| ' ic_whse_mst'||p_db_link||' w, '
-- Bug 6467457 modified as below ||' sy_uoms_mst'||p_db_link||' sou '
||' mtl_units_of_measure'||p_db_link||' sou '
|| ' WHERE r.orgn_code = p.orgn_code '
|| ' AND r.planning_exception_set IS NULL '
|| ' AND p.resource_whse_code = w.whse_code '
|| ' AND r.resources = rsm.resources ' /* B4081551 */
|| ' AND r.delete_mark = 0 '
|| ' AND rsm.delete_mark = 0 ' /* B4081551 */
|| ' AND p.delete_mark = 0 '
|| ' AND w.delete_mark = 0 '
-- Bug 6467457 modified as below ||' AND sou.delete_mark = 0 '
||' AND NVL(sou.disable_date, sysdate+1) > sysdate '
-- Bug 6467457 modified as below ||' AND sou.um_code = r.capacity_uom ';
log_message('msc_st_department_resources Insert statement '||ins_dept_res);
/* Insert into MSC_ST_RESOURCE_GROUPS for Bottleneck Resources
Sending only those resources that are used in Planning for APS
*/
stmt_no := 20 ;
ins_res_group := ' INSERT INTO msc_st_resource_groups '
|| ' ( group_code, '
|| ' meaning, '
|| ' description, '
|| ' from_date, '
|| ' to_date, '
|| ' enabled_flag, '
|| ' sr_instance_id '
|| ' ) '
|| ' SELECT distinct '
|| ' crd.group_resource , '
|| ' crm.resource_desc,'
|| ' crm.resource_desc,'
|| ' sysdate,'
|| ' NULL,'
|| ' 1,'
|| ' :instance_id '
|| ' FROM sy_orgn_mst'||p_db_link||' sy, '
|| ' cr_rsrc_dtl'||p_db_link||' crd, '
|| ' cr_rsrc_mst'||p_db_link||' crm '
|| ' WHERE sy.orgn_code = crd.orgn_code '
|| ' AND sy.resource_whse_code is NOT NULL '
|| ' AND crd.resources = crm.resources '
|| ' AND crd.group_resource = crm.resources '
|| ' AND crd.delete_mark = 0 ';
|| ' AND EXISTS ( SELECT 1 FROM gmp_item_aps'||p_db_link||' gia '
|| ' WHERE gia.whse_code = sy.resource_whse_code )' ;
log_message('msc_st_resource_groups Insert statement '||ins_res_group);
ins_res_instance := ' INSERT INTO msc_st_dept_res_instances '
||' ( sr_instance_id, '
||' res_instance_id, '
||' resource_id, '
||' department_id, '
||' organization_id, '
||' serial_number, '
||' equipment_item_id, '
||' last_known_setup, '
||' effective_start_date, '
||' effective_end_date, '
||' deleted_flag '
||' ) '
||' SELECT :instance_id, '
||' ((gri.instance_id * 2) + 1), '
||' ((gri.resource_id * 2) + 1) x_resource_id, '
||' ((iwm.mtl_organization_id * 2) + 1) department_id,' /* encoded */
||' iwm.mtl_organization_id , '
||' NVL(gri.eqp_serial_number, to_char(gri.instance_number)), '
||' gri.equipment_item_id, '
||' gri.last_setup_id, ' -- Conc Prog routine will populate this
||' gri.eff_start_date, '
||' gri.eff_end_date, '
||' 2 '
||' FROM '
||' gmp_resource_instances'||p_db_link||' gri, '
||' cr_rsrc_dtl'||p_db_link||' crd, '
||' sy_orgn_mst'||p_db_link||' som,'
||' ic_whse_mst'||p_db_link||' iwm '
||' WHERE '
||' gri.resource_id = crd.resource_id '
||' AND crd.schedule_ind = 2 '
||' AND crd.orgn_code = som.orgn_code '
||' AND gri.inactive_ind = 0 '
||' AND crd.delete_mark = 0 '
||' AND som.delete_mark = 0'
||' AND iwm.delete_mark = 0'
||' AND som.resource_whse_code = iwm.whse_code' ;
||' AND EXISTS ( SELECT 1 FROM gmp_item_aps'||p_db_link||' gia '
||' WHERE gia.whse_code = som.resource_whse_code )' ;
log_message('Error in department/Res Group Insert: '||p_instance_id);
REM| The following procedure inserts rows into |
REM| msc_st_net_rsrc_avail table |
REM| |
REM| Input Parameters |
REM| p_instance_id - Instance Id |
REM| p_org_id - Organization id |
REM| p_simulation_set - Simulation Set |
REM| p_shift_no - Shift number |
REM| p_cal_date - Calendar date |
REM| p_from_time - shift starting time |
REM| p_to_time - Shift Ending time |
REM| |
REM| Output Parameters |
REM| None |
REM| |
REM| |
REM| HISTORY |
REM| Created 5th Aug 1999 by Sridhar Gidugu (OPM Development Oracle US) |
REM| 10/13/99 - Added deleted_flag in the insert statement |
REM| 01/24/01 - Bug Fix - 1612090, Added new name to the unavailable view |
REM| |
REM| |
REM+==========================================================================+
*/
PROCEDURE net_rsrc(p_instance_id IN NUMBER,
p_org_id IN NUMBER,
p_simulation_set IN VARCHAR2,
p_resource_id IN NUMBER,
p_assigned_qty IN NUMBER,
p_shift_num IN NUMBER,
p_calendar_date IN DATE,
p_from_time IN NUMBER,
p_to_time IN NUMBER ) IS
BEGIN
stmt_no := 31;
INSERT INTO msc_st_net_resource_avail
( organization_id,
sr_instance_id,
resource_id,
department_id,
simulation_set,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
deleted_flag
)
values
( p_org_id,
p_instance_id,
((p_resource_id * 2) + 1), /* B1177070 */
((p_org_id * 2) + 1), /* B1177070 encoded key */
p_simulation_set,
p_shift_num,
p_calendar_date,
p_from_time,
p_to_time,
p_assigned_qty,
2
);
REM| update_trading_partners |
REM| |
REM| Type |
REM| public |
REM| |
REM| DESCRIPTION |
REM| |
REM| This procedure updates the following table : |
REM| |
REM| 1. msc_st_trading_partners |
REM| |
REM| Input Parameters |
REM| p_org_id - Organization_id |
REM| p_cal_code - Calendar_code |
REM| |
REM| Output Parameters |
REM| None |
REM| |
REM| |
REM| HISTORY |
REM| Created 5th Aug 1999 by Sridhar Gidugu (OPM Development Oracle US) |
REM| 8/30/99 - Removed the existing Trading Partner Procedure and changed |
REM| to a single Update Procedure. |
REM| 10/1/99 - Changed Updating Trading Partners, |
REM| - Updated Organization_typw with a value 2 and changed |
REM| - partner_type = 3 |
REM| |
REM| |
REM+==========================================================================+
*/
PROCEDURE update_trading_partners(p_org_id IN NUMBER,
p_cal_code IN VARCHAR2,
return_status OUT NOCOPY BOOLEAN) IS
BEGIN
IF return_status THEN
v_cp_enabled := TRUE;
UPDATE MSC_ST_TRADING_PARTNERS
SET calendar_code = p_cal_code,
organization_type = 2
WHERE sr_tp_id = p_org_id
AND partner_type = 3;
log_message('Failure:Trading Partners Update Occured ' || stmt_no);
END update_trading_partners; /* End of Updating Trading partners */
/* if a row has already been inserted for the calendar id
use the value from that row and stop the loop */
IF plsqltbl_rec(i).calendar_id = p_cal_id
THEN
/* Commented the following statement and used substrb to pick first
10 characters as it causes a buffer too small problem - Bug#1288143 */
out_cal_no := substrb(plsqltbl_rec(i).calendar_no,1,14);
REM| 10/13/99 - Added deleted_flag in the insert statement |
REM| 10/18/99 - Changed value of Exception set Id from 1 to -1 |
REM| 12/09/99 - Added Code to include all Calendar Days |
REM| 12/17/99 - Fixed Code for Bug# 1117565 |
REM| 02/01/00 - next seq and prior seqs are made same as seq number in |
REM| - msc_calendar_dates insert, bug#1175906 |
REM| - similarly for next date and prior date are same as calendar|
REM| - dates |
REM| 03/01/00 - Added Code to not to include rows which have |
REM| shift_duration as zero seconds - Bug#1221285 |
REM| 03/20/03 - Added Inserts to msc_st_shift_times table - 2213101 |
REM| 03/20/03 - Added Inserts to msc_st_shift_dates table - 2213101 |
REM| |
REM| |
REM+==========================================================================+
*/
PROCEDURE retrieve_calendar_detail( p_cal_id IN NUMBER,
p_calendar_no IN VARCHAR2,
p_cal_desc IN VARCHAR2,
p_run_date IN DATE,
p_db_link IN VARCHAR2,
p_instance_id IN NUMBER,
p_usage IN VARCHAR2,
return_status OUT NOCOPY BOOLEAN) IS
cal_cur ref_cursor_typ;
/* Insert for Net Resource starts here, The following select statement
gets the period that are availble for a given calendar, From time
and To Time are taken in seconds here.
*/
IF return_status THEN
v_cp_enabled := TRUE;
sql_stmt3 := ' SELECT msd.calendar_date calendar_date, '
|| ' dd.shift_no shift_no, '
|| ' dd.shift_start from_time, '
|| ' dd.shift_start + dd.shift_duration to_time '
|| ' FROM mr_shcl_dtl'||p_db_link||' msd, '
|| ' mr_shdy_hdr'||p_db_link||' dh, '
|| ' mr_shdy_dtl'||p_db_link||' dd '
|| ' WHERE msd.calendar_id = :curr_cal_id '
|| ' and dh.shopday_no = msd.shopday_no '
|| ' AND dd.shopday_no = dh.shopday_no '
|| ' AND msd.delete_mark = 0 '
|| ' AND dh.delete_mark = 0 '
|| ' AND dd.delete_mark = 0 '
|| ' AND dd.shift_duration > 0 '
|| ' ORDER BY calendar_date, '
|| ' from_time, '
|| ' to_time ';
new_rec.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');
ins_stmt := 'INSERT INTO gmp_calendar_detail_gtmp'||p_db_link
||' ( '
||' calendar_id, '
||' shift_num, '
||' shift_date, '
||' from_time, '
||' to_time, '
||' from_date, '
||' to_date '
||' ) '
||' VALUES '
||' ( :p1,:p2,:p3,:p4,:p5,:p6,:p7)';
ins_stmt1 := 'INSERT INTO temp_cal'||p_db_link
||' ( '
||' calendar_id, '
||' shift_num, '
||' shift_date, '
||' from_time, '
||' to_time, '
||' from_date, '
||' to_date '
||' ) '
||' VALUES '
||' ( :p1,:p2,:p3,:p4,:p5,:p6,:p7)';
/* Insert for msc_st_shift_times Starts Here - 2213101 */
stmt_no := 41;
shft_time := ' INSERT INTO msc_st_shift_times '
|| ' (shift_num, '
|| ' calendar_code, '
|| ' from_time, '
|| ' to_time, '
|| ' deleted_flag, '
|| ' sr_instance_id '
|| ' ) '
|| ' SELECT distinct shift_num , '
|| ' :calendar_no, '
|| ' from_time, '
|| ' to_time, '
|| ' 2 , '
|| ' :instance_id '
|| ' FROM gmp_calendar_detail_gtmp'||p_db_link||' gtmp '
|| ' WHERE calendar_id = :curr_cal_id '
|| ' ORDER BY shift_num,from_time, to_time ' ;
/* Insert for msc_st_shift_times Ends Here - 2213101 */
--
/* Start writing the Calendar dates */
old_occur := 1;
SELECT to_char(new_rec(old_weekly).cal_date,'D') INTO week_num FROM dual;
/* After allowing the first row insert, check from the second row on
if there are any gaps in the dates
*/
IF seq_num >= 1 THEN
/* using information from the Prior set values of sequences and dates */
v_next_seq_num := next_seq_num;
INSERT INTO msc_st_calendar_dates
(calendar_date,
calendar_code,
exception_set_id,
seq_num,
next_seq_num,
prior_seq_num,
next_date,
prior_date,
calendar_start_date,
calendar_end_date,
description,
sr_instance_id,
deleted_flag
)
values(v_old_cal_date + 1,
p_calendar_no,
-1,
NULL,
v_next_seq_num,
v_prior_seq_num,
new_rec(j).cal_date,
v_prior_date,
cal_start_date,
cal_end_date,
p_cal_desc,
p_instance_id,
2
);
INSERT INTO msc_st_calendar_dates
(calendar_date,
calendar_code,
exception_set_id,
seq_num,
next_seq_num,
prior_seq_num,
next_date,
prior_date,
calendar_start_date,
calendar_end_date,
description,
sr_instance_id,
deleted_flag
)
VALUES(new_rec(old_occur).cal_date,
p_calendar_no,
-1,
seq_num,
seq_num,
seq_num,
new_rec(old_occur).cal_date,
new_rec(old_occur).cal_date,
cal_start_date,
cal_end_date,
p_cal_desc,
p_instance_id,
2
);
INSERT INTO msc_st_cal_week_start_dates
( CALENDAR_CODE ,
EXCEPTION_SET_ID ,
WEEK_START_DATE ,
NEXT_DATE ,
PRIOR_DATE ,
SEQ_NUM ,
DELETED_FLAG ,
SR_INSTANCE_ID)
VALUES
( p_calendar_no ,
-1,
new_rec(old_weekly).cal_date,
new_rec(j).cal_date,
new_rec(prior_weekly).cal_date,
weekly_seq,
2,
p_INSTANCE_ID) ;
SELECT TO_CHAR(new_rec(j).cal_date,'D') INTO week_num FROM dual;
INSERT INTO msc_st_period_start_dates
( CALENDAR_CODE ,
EXCEPTION_SET_ID ,
PERIOD_START_DATE ,
PERIOD_SEQUENCE_NUM ,
PERIOD_NAME ,
NEXT_DATE ,
PRIOR_DATE ,
DELETED_FLAG ,
SR_INSTANCE_ID)
VALUES
( p_calendar_no ,
-1,
new_rec(old_period).cal_date,
period_seq,
TO_CHAR(new_rec(old_period).cal_date, 'MON'),
new_rec(j).cal_date,
new_rec(prior_period).cal_date,
2,
p_INSTANCE_ID);
/* Insert for the last record */
stmt_no := 60;
INSERT INTO msc_st_calendar_dates
( calendar_date,
calendar_code,
exception_set_id,
seq_num,
next_seq_num,
prior_seq_num,
next_date,
prior_date,
calendar_start_date,
calendar_end_date,
description,
sr_instance_id
)
VALUES
( new_rec(old_occur).cal_date,
p_calendar_no,
-1,
seq_num + 1,
seq_num + 1,
seq_num + 1,
new_rec(old_occur).cal_date,
new_rec(old_occur).cal_date,
cal_start_date,
cal_end_date,
p_cal_desc,
p_instance_id
);
INSERT INTO msc_st_cal_week_start_dates
( CALENDAR_CODE ,
EXCEPTION_SET_ID ,
WEEK_START_DATE ,
NEXT_DATE ,
PRIOR_DATE ,
SEQ_NUM ,
DELETED_FLAG ,
SR_INSTANCE_ID)
VALUES
( p_calendar_no ,
-1,
new_rec(old_weekly).cal_date,
new_rec(old_weekly).cal_date,
new_rec(prior_weekly).cal_date,
weekly_seq,
2,
p_INSTANCE_ID) ;
INSERT INTO msc_st_period_start_dates
( CALENDAR_CODE ,
EXCEPTION_SET_ID ,
PERIOD_START_DATE ,
PERIOD_SEQUENCE_NUM ,
PERIOD_NAME ,
NEXT_DATE ,
PRIOR_DATE ,
DELETED_FLAG ,
SR_INSTANCE_ID
)
VALUES
(p_calendar_no ,
-1,
new_rec(old_period).cal_date,
period_seq,
TO_CHAR(new_rec(old_period).cal_date, 'MON'),
new_rec(old_period).cal_date,
new_rec(prior_period).cal_date,
2,
p_INSTANCE_ID);
/* B2213101 - Code added for Insert into msc_st_shift_dates */
stmt_no := 70;
INSERT INTO msc_st_shift_dates
( calendar_code,
exception_set_id,
shift_num,
shift_date,
seq_num,
next_seq_num,
prior_seq_num,
next_date,
prior_date,
deleted_flag,
sr_instance_id
)
VALUES
( p_calendar_no,
-1,
new_rec(h).shift_num,
new_rec(h).cal_date,
shift_seq_num,
shift_next_seq_num,
shift_prior_seq_num,
shift_next_date,
shift_prior_date,
2,
p_instance_id
);
/* B2213101 - End of changes for Insert into msc_st_shift_dates */
END IF ; /* End if for usage */
REM| net_rsrc_insert |
REM| |
REM| Type |
REM| public |
REM| |
REM| DESCRIPTION |
REM| |
REM| |
REM| Input Parameters |
REM| p_org_id - Organization id |
REM| p_orgn_code - Orgn Code |
REM| p_calendar_id - calendar_id |
REM| p_instance_id - Instance Id |
REM| p_usage - Used foir APS or WPS |
REM| p_db_link - Data Base Link |
REM| |
REM| Output Parameters |
REM| return_status |
REM| |
REM| HISTORY |
REM| Created on 4th Jan 2002 By Rajesh Patangya |
REM| 7th Mar 2003 -- Performance issue fix and B2671540 00:00 shift fix |
REM| B3161696 - 26-SEP-2003 TARGETTED RESOURCE AVAILABILITY PLACEHOLDER BUG |
REM| B4309093 - 20-APR-2005 Modified code to TO ADD TIME OR A SHIFT TO A |
REM| PLANT RESOURCE |
REM+==========================================================================+
*/
PROCEDURE net_rsrc_insert(p_org_id IN PLS_INTEGER,
p_orgn_code IN VARCHAR2,
p_simulation_set IN VARCHAR2,
p_db_link IN VARCHAR2,
p_instance_id IN PLS_INTEGER,
p_run_date IN DATE ,
p_calendar_id IN PLS_INTEGER,
p_usage IN VARCHAR2,
return_status OUT NOCOPY BOOLEAN) IS
ri_shift_interval ref_cursor_typ;
log_message(' net_rsrc_insert called '||p_org_id||'**'||p_orgn_code||'**'||
p_simulation_set||'**'||p_db_link||'**'||p_instance_id
||'**'||p_calendar_id||'**'||p_usage);
gsql_stmt := ' SELECT mfg_calendar_id '
|| ' FROM sy_orgn_mst'||p_db_link
|| ' WHERE orgn_code = :orgn_code1 ';
sql_stmt1 := ' SELECT /*+ ALL_ROWS DRIVING_SITE (rt) */ '
|| ' decode(rt.interval_date,rt.lead_idate,rt.assigned_qty,decode(rt.rsum,0,rt.assigned_qty,rt.assigned_qty-rt.rsum)) resource_count '
|| ' ,rt.resource_id '
|| ' ,0 instance_id '
|| ' ,0 instance_number '
|| ' ,rt.shift_num '
|| ' ,rt.interval_date from_date '
|| ' ,rt.lead_idate to_date '
|| ' FROM '
|| ' ( '
|| ' SELECT '
|| ' t.resource_id '
|| ' ,t.shift_num '
|| ' ,t.interval_date '
|| ' ,t.assigned_qty '
|| ' ,nvl(sum(u.resource_units),0) rsum '
|| ' ,max(t.lead_idate) lead_idate '
|| ' FROM '
|| ' ( '
|| ' SELECT unique resource_id,instance_number,from_date, '
|| ' to_date to_date1,resource_units '
|| ' FROM ( '
|| ' SELECT un.resource_id, '
|| ' gri.instance_number, '
|| ' un.from_date, '
|| ' un.to_date, '
|| ' 1 resource_units'
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.orgn_code = :orgn_code ' ;
|| ' AND crd.delete_mark = 0 ' ;
|| ' SELECT un.resource_id, '
|| ' gri.instance_number, '
|| ' un.from_date, '
|| ' un.to_date, '
|| ' 1 resource_units'
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.orgn_code = :orgn_code1 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.schedule_ind = 2 '
|| ' AND nvl(un.instance_id,0) = 0 ' ;
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances '||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' UNION ALL '
|| ' SELECT un.resource_id, '
|| ' 0 instance_number, '
|| ' un.from_date, '
|| ' un.to_date, '
|| ' un.resource_units '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v ' ||p_db_link||' un'
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.orgn_code = :orgn_code2 ' ;
|| ' AND crd.delete_mark = 0 ' ;
|| ' (SELECT 1 '
|| ' FROM gmp_resource_instances ' ||p_db_link||' gri '
|| ' WHERE gri.resource_id = un.resource_id ) '
|| ' ) '
|| ' ) u, '
|| ' ( '
|| ' SELECT resource_id,shift_num,interval_date, '
|| ' assigned_qty,lead_idate '
|| ' FROM '
|| ' ( '
|| ' SELECT resource_id,shift_num,interval_date, '
|| ' assigned_qty '
|| ' ,lead(resource_id,1) over(order by '
|| ' resource_id,interval_date,shift_num) as lead_rid '
|| ' ,lead(interval_date,1) over(order by '
|| ' resource_id,interval_date,shift_num) as lead_idate '
|| ' ,lead(shift_num,1) over(order by '
|| ' resource_id,interval_date,shift_num) as lead_snum '
|| ' FROM '
|| ' ( '
|| ' SELECT unique cmd.resource_id, '
|| ' 0 , '
|| ' exp.shift_num, '
|| ' 0 , '
|| ' cmd.interval_date, '
|| ' cmd.assigned_qty '
|| ' FROM ( '
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_number instance_number,'
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.from_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.orgn_code = :orgn_code1 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) <> 0 ' ;
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_number instance_number,'
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.to_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.orgn_code = :orgn_code2 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) <> 0 ' ;
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_number instance_number,'
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.from_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.orgn_code = :orgn_code3 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) = 0 ' ;
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances '||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' UNION ALL '
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_number instance_number,'
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.to_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.orgn_code = :orgn_code4 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.schedule_ind = 2 '
|| ' AND nvl(un.instance_id,0) = 0 ' ;
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances '||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' UNION ALL '
|| ' SELECT un.resource_id, '
|| ' 0 instance_number, '
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.from_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.orgn_code = :orgn_code44 ' ;
|| ' AND crd.delete_mark = 0 ' ;
|| ' (SELECT 1 '
|| ' FROM gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE gri.resource_id = un.resource_id ) '
|| ' UNION ALL '
|| ' SELECT un.resource_id, '
|| ' 0 instance_number, '
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.to_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.orgn_code = :orgn_code444 ' ;
|| ' AND crd.delete_mark = 0 ' ;
|| ' (SELECT 1 '
|| ' FROM gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE gri.resource_id = un.resource_id ) '
|| ' ) cmd, '
|| ' gmp_calendar_detail_gtmp ' ||p_db_link||' exp '
|| ' WHERE exp.calendar_id = :curr_cal1 '
|| ' AND cmd.interval_date BETWEEN '
|| ' exp.from_date AND exp.to_date '
|| ' UNION ALL '
|| ' SELECT crd.resource_id , '
|| ' 0 , '
|| ' exp.shift_num, '
|| ' 0 , '
|| ' exp.from_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_calendar_detail_gtmp ' ||p_db_link||' exp '
|| ' WHERE crd.orgn_code = :orgn_code5 ' ;
|| ' AND crd.delete_mark = 0 ' ;
|| ' SELECT crd.resource_id , '
|| ' 0 , '
|| ' exp.shift_num, '
|| ' 0 , '
|| ' exp.to_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_calendar_detail_gtmp ' ||p_db_link||' exp '
|| ' WHERE crd.orgn_code = :orgn_code6 ' ;
|| ' AND crd.delete_mark = 0 ' ;
INSERT INTO msc_st_net_resource_avail
( organization_id,
sr_instance_id,
resource_id,
department_id,
simulation_set,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
deleted_flag
)
VALUES
( p_org_id,
p_instance_id,
((resource_id(i) * 2) + 1), /* B1177070 */
((p_org_id * 2) + 1), /* B1177070 encoded key */
p_simulation_set,
shift_num(i),
trunc(f_date(i)),
((f_date(i) - trunc(f_date(i))) * 86400 ),
((t_date(i) - trunc(t_date(i))) * 86400 ),
resource_count(i),
2
);
INSERT INTO gmp_resource_avail
(
instance_id, plant_code, resource_id,
calendar_id, resource_instance_id, shift_num,
shift_date, from_time, to_time,
resource_units, creation_date, created_by,
last_update_date, last_updated_by, last_update_login
) VALUES
(
p_instance_id,
p_orgn_code,
resource_id(i),
p_calendar_id,
instance_id(i),
shift_num(i),
trunc(f_date(i)),
((f_date(i) - trunc(f_date(i))) * 86400 ),
((t_date(i) - trunc(t_date(i))) * 86400 ),
resource_count(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
) ;
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');
/* Insert for msc_st_resource_shifts Starts here - 2213101 */
-- PK Bug 6330140 Modified query to remove ic_whse_mst to eliminate cartesian product
stmt_no := 80;
sql_shifts := ' INSERT INTO msc_st_resource_shifts '
|| ' ( department_id, '
|| ' shift_num, '
|| ' resource_id, '
|| ' deleted_flag, '
|| ' sr_instance_id, '
|| ' capacity_units '
|| ' ) '
|| ' SELECT /*+ DRIVING_SITE(gtmp) DRIVING_SITE(som) DRIVING_SITE(crd) */ unique '
|| ' ((:org_id*2)+1) organization_id, '
|| ' gtmp.shift_num, '
|| ' ((crd.resource_id*2)+1), '
|| ' 2, '
|| ' :instance_id, '
|| ' crd.assigned_qty '
|| ' FROM gmp_calendar_detail_gtmp'||p_db_link||' gtmp, '
|| ' sy_orgn_mst'||p_db_link||' som, '
|| ' cr_rsrc_dtl'||p_db_link||' crd '
|| ' WHERE gtmp.calendar_id = :curr_cal_id '
|| ' AND NVL(crd.calendar_id,som.mfg_calendar_id)=gtmp.calendar_id '
|| ' AND som.orgn_code = crd.orgn_code '
|| ' AND som.resource_whse_code IS NOT NULL '
|| ' AND crd.delete_mark = 0 ' ;
/* End of Inserts to msc_st_resource_shifts - 2213101 */
return_status := TRUE;
log_message('NO DATA FOUND : MSC_CL_GMP_UTILITY.net_rsrc_insert' || stmt_no);
log_message('Error in Net Resource Insert: '||stmt_no);
END net_rsrc_insert;
REM| insert_simulation_sets |
REM| |
REM| Type |
REM| public |
REM| |
REM| DESCRIPTION |
REM| |
REM| |
REM| Input Parameters |
REM| p_org_id - Organization id |
REM| p_rsrc_whse_code - Resource Whse Code |
REM| p_instance_id - Instance Id |
REM| p_delimiter - Delimiter |
REM| |
REM| Output Parameters |
REM| return_status |
REM| |
REM| |
REM| HISTORY |
REM| Created 23th Sep 1999 by Sridhar Gidugu (OPM Development Oracle US) |
REM| 10/01/1999 - Chaged passing of Parameters to insert_simulation_sets |
REM| - Added p_simulation_sets as a parameter and removed |
REM| - p_rsrc_whse_code parameter |
REM| 10/13/1999 - Added deleted_flag in the insert statement |
REM| |
REM| |
REM+==========================================================================+
*/
PROCEDURE insert_simulation_sets(p_org_id IN NUMBER,
p_instance_id IN NUMBER,
p_simulation_set IN VARCHAR2,
return_status OUT NOCOPY BOOLEAN) IS
BEGIN
IF return_status THEN
v_cp_enabled := TRUE;
INSERT INTO msc_st_simulation_sets
(organization_id,
sr_instance_id,
simulation_set,
description,
use_in_wip_flag,
deleted_flag
)
values (p_org_id,
p_instance_id,
p_simulation_set,
p_simulation_set,
2,
2
); /* Simulation Set Insert ends here */
log_message('Error in insert simulation: ');
END insert_simulation_sets;
REM| Update trading Partners and net_rsrc_insert procedure. |
REM| |
REM| 9/7/99 - Changed the Main Procedure, removed UNION ALL for main cursor|
REM| 9/28/99 - Changed the main query ordering by Organization Id and |
REM| - changed logic for populating plsqltbl |
REM| 4/03/00 - using mtl_organization_id from ic_whse_mst instead of |
REM| - organization_id from sy_orgn_mst - Bug# 1252322 |
REM| 5/03/00 - Add instance code as a prefix to the calendar code |
REM| - Bug # 1288143 |
REM| 7/07/00 - Anchor Date Problem Fixed in the Calendar Code |
REM| - Bug # 1337084. |
REM| 7/12/00 - Removed the Debugging Statement shcl.calendar_id in |
REM| - (121,126) - bug#1353845 |
REM| 10/18/01 - B2041247 - Modified the cursor to consider Calendars |
REM| associated with the OPM Plants |
REM| |
REM| 7th Mar 2003 -- Performance issue fix and B2671540 00:00 shift fix |
REM| 04/21/2004 - Navin Sinha - B3577871 -ST:OSFME2: collections failing |
REM| in planning data pull. |
REM| Added handling of NO_DATA_FOUND Exception.|
REM| And return the return_status as TRUE. |
REM| |
REM| 07-May-2004 - Sowmya - B3599089 - ST: ORG SPECIFIC COMPLETE COLLETION |
REM| FOR OPM ORGS TAKING MORE TIME. |
REM| As the varaibale l_org_specific was not getting |
REM| refreshed,the resource availability |
REM| was getting collected irrespective of whether or|
REM| not the org is enabled. To overcome this, added |
REM| if clause containing the l_cur%NOTFOUND.So when |
REM| the no values are returned the l_org_specific= 0|
REM| |
REM+==========================================================================+
REM
*/
PROCEDURE populate_rsrc_cal(p_run_date IN DATE,
p_instance_id IN NUMBER,
p_delimiter IN VARCHAR2,
p_db_link IN VARCHAR2,
p_nra_enabled IN NUMBER,
return_status OUT NOCOPY BOOLEAN) IS
/* Local Array Defintions */
TYPE interval_typ_a is RECORD
(
organization_id PLS_INTEGER,
simulation_set VARCHAR2(10),
resource_id PLS_INTEGER,
shift_date DATE,
shift_num PLS_INTEGER,
capacity_units PLS_INTEGER,
from_time PLS_INTEGER,
to_time PLS_INTEGER
);
inst_stmt := ' SELECT instance_code '
|| ' FROM msc_apps_instances WHERE instance_id = :instance_id ';
sql_allcal := ' SELECT sy.mfg_calendar_id, '
|| ' shcl.calendar_no, '
|| ' shcl.calendar_desc, '
|| ' sy.orgn_code, '
|| ' decode(whse.whse_code,sy.resource_whse_code, '
|| ' sy.resource_whse_code,NULL) resource_whse_code, '
|| ' ic.mtl_organization_id organization_id, '
|| ' 0 '
|| ' FROM ps_schd_hdr'||p_db_link||' h, '
|| ' ps_schd_dtl'||p_db_link||' d, '
|| ' mr_shcl_hdr'||p_db_link||' shcl, '
|| ' (select distinct plant_code,whse_code '
|| ' from ps_whse_eff'||p_db_link|| ') whse, '
|| ' sy_orgn_mst'||p_db_link||' sy, '
|| ' ic_whse_mst'||p_db_link||' ic '
|| ' WHERE d.schedule_id = h.schedule_id '
|| ' AND d.orgn_code = sy.orgn_code '
|| ' AND shcl.calendar_id = sy.mfg_calendar_id ' /* B2041247 */
|| ' AND whse.plant_code = sy.orgn_code '
|| ' AND whse.whse_code = ic.whse_code '
|| ' AND h.active_ind = 1 '
|| ' AND shcl.active_ind = 1 '
|| ' AND h.delete_mark = 0 '
|| ' AND shcl.delete_mark = 0 '
|| ' ORDER BY organization_id, '
|| ' resource_whse_code, '
|| ' mfg_calendar_id ';
cursor and inserts rows into plsqltbl when the organization_id changes */
stmt_no := 20;
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');
/* INSERT A LOOP TO SPIT OUT ALL THE ROWS IN PL/SQL TABLE HERE */
stmt_no := 30;
/* Calling the Update Trading Partner Procedure,which updates the
MSC_ST_TRADING_PARTNERS with the Calendar Code for a given Organization
Id
*/
stmt_no := 50;
update_trading_partners(plsqltbl_rec(k).organization_id,
plsqltbl_rec(k).calendar_no,
return_status
);
l_stmt := 'SELECT 1 FROM dual WHERE '||
l_opm_org||MSC_CL_GMP_UTILITY.g_in_str_org ;
/* Populating Net Resource Insert Table */
stmt_no := 65;
net_rsrc_insert(plsqltbl_rec(k).organization_id,
plsqltbl_rec(k).orgn_code,
simulation_set,
p_db_link,
p_instance_id,
p_run_date,
plsqltbl_rec(k).calendar_id,
V_APS,
return_status
);
Upd_Process_Org := 'UPDATE MSC_ST_TRADING_PARTNERS '
||' SET organization_type = 2 '
||' WHERE sr_tp_id in (SELECT organization_id '
||' FROM mtl_parameters'||p_db_link
||' WHERE process_enabled_flag = '||''''||'Y'||'''' || ')'
||' AND partner_type = 3' ;
log_message('Trading Partner Update is Done' );
log_message('Error in UPDATE TRADING_PARTNERS '||stmt_no);
ins_res_avl := ' SELECT '
|| ' net.organization_id, '
|| ' net.simulation_set, '
|| ' net.resource_id , '
|| ' net.shift_date , '
|| ' net.shift_num , '
|| ' net.capacity_units , '
|| ' min(net.from_time) from_time, '
|| ' max(net.lead_tt) to_time '
|| ' FROM ( '
|| ' SELECT organization_id , '
|| ' simulation_set, '
|| ' resource_id, '
|| ' shift_date , '
|| ' shift_num , '
|| ' capacity_units , '
|| ' from_time , '
|| ' to_time , '
|| ' lead(organization_id,1) '
|| ' over(order by organization_id,simulation_set, '
|| ' resource_id,shift_date, shift_num,from_time,to_time,capacity_units) '
|| ' as lead_iid, '
|| ' lead(simulation_set,1) '
|| ' over(order by organization_id,simulation_set,'
|| ' resource_id,shift_date, shift_num,from_time,to_time,capacity_units) '
|| ' as lead_ss, '
|| ' lead(resource_id,1) '
|| ' over(order by organization_id,simulation_set,'
|| ' resource_id,shift_date, shift_num,from_time,to_time,capacity_units) '
|| ' as lead_rid, '
|| ' lead(shift_date,1) '
|| ' over(order by organization_id,simulation_set,'
|| ' resource_id,shift_date, shift_num,from_time,to_time,capacity_units) '
|| ' as lead_sdt, '
|| ' lead(shift_num,1) '
|| ' over(order by organization_id,simulation_set,'
|| ' resource_id,shift_date, shift_num,from_time,to_time,capacity_units) '
|| ' as lead_sn, '
|| ' lead(from_time,1) '
|| ' over(order by organization_id,simulation_set,'
|| ' resource_id,shift_date, shift_num,from_time,to_time,capacity_units) '
|| ' as lead_ft, '
|| ' lead(to_time,1) '
|| ' over(order by organization_id,simulation_set,'
|| ' resource_id,shift_date, shift_num,from_time,to_time,capacity_units) '
|| ' as lead_tt, '
|| ' lead(capacity_units,1) '
|| ' over(order by organization_id,simulation_set,'
|| ' resource_id,shift_date, shift_num,from_time,to_time,capacity_units) '
|| ' as lead_rc '
|| ' FROM msc_st_net_resource_avail '
|| ' WHERE sr_instance_id = :inst_id '
|| ' ) net '
|| ' WHERE net.resource_id = net.lead_rid '
|| ' AND net.organization_id = net.lead_iid '
|| ' AND net.simulation_set = net.lead_ss '
|| ' AND net.shift_num = net.lead_sn '
|| ' AND net.shift_date = net.lead_sdt '
|| ' AND net.to_time = net.lead_ft '
|| ' AND net.capacity_units = net.lead_rc '
|| ' GROUP BY '
|| ' net.organization_id , '
|| ' net.simulation_set , '
|| ' net.resource_id , '
|| ' net.shift_date , '
|| ' net.shift_num , '
|| ' net.capacity_units ' ;
sqlstmt := ' DELETE FROM msc_st_net_resource_avail '
|| ' WHERE organization_id = :org_id '
|| ' AND simulation_set = :sim_set '
|| ' AND sr_instance_id = :inst_id '
|| ' AND resource_id = :prid '
|| ' AND shift_date = :psdt '
|| ' AND shift_num = :psn '
|| ' AND capacity_units = :prc '
|| ' AND from_time >= :pft '
|| ' AND to_time <= :ptt ' ;
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');
upd_res_avl := 'UPDATE msc_st_net_resource_avail '
||' SET to_time = 86400 '
||' WHERE to_time = 86399 '
||' AND shift_num >= 99999 ' ;
upd_res_avl := 'UPDATE msc_st_net_resource_avail '
||' SET shift_num = (shift_num - 99999) '
||' WHERE shift_num >= 99999 ' ;
inst_resavl := ' SELECT '
|| ' net.organization_id, '
|| ' net.Department_id, '
|| ' net.resource_id , '
|| ' net.res_instance_id , '
|| ' net.equipment_item_id , '
|| ' net.serial_number, '
|| ' net.shift_date , '
|| ' net.shift_num , '
|| ' min(net.from_time) from_time, '
|| ' max(net.lead_tt) to_time '
|| ' FROM ( '
|| ' SELECT organization_id , '
|| ' Department_id, '
|| ' resource_id, '
|| ' res_instance_id, '
|| ' equipment_item_id, '
|| ' serial_number, '
|| ' shift_date , '
|| ' shift_num , '
|| ' from_time , '
|| ' to_time , '
|| ' lead(organization_id,1) '
|| ' over(order by organization_id,Department_id,resource_id,res_instance_id, '
|| ' equipment_item_id,serial_number, shift_date,shift_num,from_time,to_time) '
|| ' as lead_iid, '
|| ' lead(Department_id,1) '
|| ' over(order by organization_id,Department_id,resource_id,res_instance_id, '
|| ' equipment_item_id,serial_number, shift_date,shift_num,from_time,to_time) '
|| ' as lead_did, '
|| ' lead(resource_id,1) '
|| ' over(order by organization_id,Department_id,resource_id,res_instance_id, '
|| ' equipment_item_id,serial_number, shift_date,shift_num,from_time,to_time) '
|| ' as lead_rid, '
|| ' lead(res_instance_id,1) '
|| ' over(order by organization_id,Department_id,resource_id,res_instance_id, '
|| ' equipment_item_id,serial_number, shift_date,shift_num,from_time,to_time) '
|| ' as lead_r_inst_id, '
|| ' lead(equipment_item_id,1) '
|| ' over(order by organization_id,Department_id,resource_id,res_instance_id, '
|| ' equipment_item_id,serial_number, shift_date,shift_num,from_time,to_time) '
|| ' as lead_eid, '
|| ' lead(serial_number,1) '
|| ' over(order by organization_id,Department_id,resource_id,res_instance_id, '
|| ' equipment_item_id,serial_number, shift_date,shift_num,from_time,to_time) '
|| ' as lead_sr_no, '
|| ' lead(shift_date,1) '
|| ' over(order by organization_id,Department_id,resource_id,res_instance_id, '
|| ' equipment_item_id,serial_number, shift_date,shift_num,from_time,to_time) '
|| ' as lead_sdt, '
|| ' lead(shift_num,1) '
|| ' over(order by organization_id,Department_id,resource_id,res_instance_id, '
|| ' equipment_item_id,serial_number, shift_date,shift_num,from_time,to_time) '
|| ' as lead_sn, '
|| ' lead(from_time,1) '
|| ' over(order by organization_id,Department_id,resource_id,res_instance_id, '
|| ' equipment_item_id,serial_number, shift_date,shift_num,from_time,to_time) '
|| ' as lead_ft, '
|| ' lead(to_time,1) '
|| ' over(order by organization_id,Department_id,resource_id,res_instance_id, '
|| ' equipment_item_id,serial_number, shift_date,shift_num,from_time,to_time) '
|| ' as lead_tt '
|| ' FROM msc_st_net_res_inst_avail '
|| ' WHERE sr_instance_id = :inst_id '
|| ' ) net '
|| ' WHERE '
|| ' net.organization_id = net.lead_iid '
|| ' AND net.Department_id = net.lead_did '
|| ' AND net.resource_id = net.lead_rid '
|| ' AND net.res_instance_id = net.lead_r_inst_id '
|| ' AND net.equipment_item_id = net.lead_eid '
|| ' AND net.serial_number = net.lead_sr_no '
|| ' AND net.shift_date = net.lead_sdt '
|| ' AND net.shift_num = net.lead_sn '
|| ' AND net.to_time = net.lead_ft '
|| ' GROUP BY '
|| ' net.organization_id, '
|| ' net.Department_id, '
|| ' net.resource_id , '
|| ' net.res_instance_id , '
|| ' net.equipment_item_id , '
|| ' net.serial_number, '
|| ' net.shift_date , '
|| ' net.shift_num ' ;
sqlstmt := ' DELETE FROM msc_st_net_res_inst_avail '
|| ' WHERE organization_id = :org_id '
|| ' AND Department_id = :dept_id '
|| ' AND sr_instance_id = :inst_id '
|| ' AND resource_id = :prid '
|| ' AND res_instance_id = :pr_inst_id '
|| ' AND equipment_item_id = :pe_id '
|| ' AND serial_number = :ps_no '
|| ' AND shift_date = :psdt '
|| ' AND shift_num = :psn '
|| ' AND from_time >= :pft '
|| ' AND to_time <= :ptt ' ;
INSERT INTO msc_st_net_res_inst_avail
( Organization_Id,
Department_id,
sr_instance_id,
Resource_Id,
res_instance_id,
serial_number,
equipment_item_id,
Shift_Num,
Shift_Date,
From_Time,
To_Time
) VALUES
( inst_record_aps.organization_id ,
inst_record_aps.Department_id ,
p_instance_id ,
inst_record_aps.resource_id,
inst_record_aps.res_instance_id,
inst_record_aps.equipment_item_id,
inst_record_aps.serial_number ,
inst_record_aps.shift_num,
inst_record_aps.shift_date,
inst_record_aps.from_time,
inst_record_aps.to_time
) ;
sqlstmt := 'UPDATE msc_st_net_res_inst_avail '
||' SET to_time = 86400 '
||' WHERE to_time = 86399 '
||' AND shift_num >= 99999 ' ;
sqlstmt := 'UPDATE msc_st_net_res_inst_avail '
||' SET shift_num = (shift_num - 99999) '
||' WHERE shift_num >= 99999 ' ;
sql_get_cal := ' SELECT distinct crd.calendar_id, '
||' shcl.calendar_no, shcl.calendar_desc, '
||' sy.orgn_code, iwm.mtl_organization_id '
||' FROM mr_shcl_hdr'||p_db_link||' shcl, '
||' sy_orgn_mst'||p_db_link||' sy, '
||' cr_rsrc_dtl'||p_db_link||' crd, '
||' ic_whse_mst'||p_db_link||' iwm '
||' WHERE sy.orgn_code = crd.orgn_code '
||' AND sy.mfg_calendar_id <> crd.calendar_id '
||' AND crd.calendar_id IS NOT NULL '
||' AND crd.calendar_id = shcl.calendar_id '
||' AND iwm.whse_code = sy.resource_whse_code ' ;
||' AND shcl.delete_mark = 0 '
||' AND crd.delete_mark = 0 '
||' ORDER BY crd.calendar_id ' ;
/* Populating Net Resource Insert Table */
net_rsrc_insert(calorg_record.organization_id,
calorg_record.orgn_code,
NULL, -- simulation_set,
p_db_link,
p_instance_id,
p_run_date,
calorg_record.calendar_id,
V_BASED,
return_status
);
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| insert_gmp_resource_avail |
REM| |
REM| Type |
REM| public |
REM| |
REM| DESCRIPTION |
REM| |
REM| Input Parameters |
REM| p_orgn_code - Orgn Code |
REM| |
REM| Output Parameters |
REM| errbuf and retcode |
REM| |
REM| HISTORY |
REM| Created on 4th Jan 2002 By Rajesh Patangya |
REM| B3161696 - 26-SEP-2003 TARGETTED RESOURCE AVAILABILITY PLACEHOLDER BUG |
REM| B4309093 - 20-APR-2005 Modified code to TO ADD TIME OR A SHIFT TO A |
REM| PLANT RESOURCE |
REM+==========================================================================+
*/
PROCEDURE insert_gmp_resource_avail( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER ,
p_orgn_code IN VARCHAR2 ,
p_from_rsrc IN VARCHAR2 ,
p_to_rsrc IN VARCHAR2 ,
p_calendar_id IN NUMBER ) IS
cal_detail_ref ref_cursor_typ;
delete_stmt VARCHAR2(32000) ;
delete_stmt := NULL;
sql_get_cal := ' SELECT '
||' DISTINCT NVL(r.calendar_id,sy.mfg_calendar_id), '
||' shcl.calendar_no, shcl.calendar_desc '
||' FROM mr_shcl_hdr shcl, '
||' sy_orgn_mst sy, '
||' cr_rsrc_dtl r '
||' WHERE sy.orgn_code = r.orgn_code '
||' AND NVL(r.calendar_id,sy.mfg_calendar_id)=shcl.calendar_id '
||' AND r.orgn_code = :lorgn_code '
||' AND shcl.delete_mark = 0 ' ;
sql_get_cal := 'Select calendar_id, '
||' calendar_no,calendar_desc '
||' FROM mr_shcl_hdr '
||' WHERE calendar_id = :cal_id ' ;
delete_stmt := 'DELETE FROM gmp_resource_avail '||
' WHERE calendar_id = :cal_id ' ||
' AND plant_code = :Plant_code1 ';
delete_stmt := delete_stmt ||' AND resource_id in (select resource_id '
||' FROM cr_rsrc_dtl '
||' WHERE orgn_code = :Plant_code2 '
||' AND resources BETWEEN :frsrc and :trsrc ) ';
EXECUTE IMMEDIATE delete_stmt USING l_calendar_id, p_orgn_code,
p_orgn_code, v_from_rsrc, v_to_rsrc;
delete_stmt := delete_stmt ||' AND resource_id in (select resource_id '
||' FROM cr_rsrc_dtl '
||' WHERE orgn_code = :Plant_code2 '
||' AND resources > :frsrc ) ';
EXECUTE IMMEDIATE delete_stmt USING l_calendar_id, p_orgn_code,
p_orgn_code, v_from_rsrc;
EXECUTE IMMEDIATE delete_stmt USING l_calendar_id, p_orgn_code ;
net_rsrc_insert(null,
p_orgn_code,
null,
null,
0,
sysdate,
l_calendar_id,
V_WPS,
ret_status) ;
END insert_gmp_resource_avail;
sql_stmt1 := ' SELECT mfg_calendar_id '
|| ' FROM sy_orgn_mst'||p_db_link
|| ' WHERE orgn_code = :orgn_code1 ';
sql_stmt1 := ' SELECT /*+ ALL_ROWS */ '
|| ' decode(rt.interval_date,rt.lead_idate,rt.assigned_qty,'
|| ' (rt.assigned_qty-nvl(rt.rsum,0))) resource_count '
|| ' ,rt.resource_id '
|| ' ,rt.instance_id '
|| ' ,rt.shift_num '
|| ' ,rt.interval_date '
|| ' ,rt.lead_idate '
-- for OPM-PS
|| ' ,NVL(gri.eqp_serial_number, to_char(gri.instance_number)) '
|| ' ,gri.equipment_item_id '
|| ' ,((rt.resource_id * 2) + 1) '
|| ' ,((rt.instance_id * 2) + 1) '
|| ' FROM '
|| ' ( '
|| ' SELECT '
|| ' t.resource_id '
|| ' ,t.instance_id '
|| ' ,t.shift_num '
|| ' ,t.interval_date '
|| ' ,t.assigned_qty '
|| ' ,nvl(u.resource_units,0) rsum '
|| ' ,max(t.lead_idate) lead_idate '
|| ' FROM ( '
|| ' SELECT unique resource_id,instance_id,from_date, '
|| ' to_date to_date1,resource_units '
|| ' FROM ( '
|| ' SELECT un.resource_id, '
|| ' gri.instance_id, '
|| ' un.from_date, '
|| ' un.to_date, '
|| ' 1 resource_units'
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.orgn_code = :orgn_code1 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) <> 0 ' ;
|| ' SELECT un.resource_id, '
|| ' gri.instance_id, '
|| ' un.from_date, '
|| ' un.to_date, '
|| ' 1 resource_units'
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.orgn_code = :orgn_code2 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.schedule_ind = 2 '
|| ' AND nvl(un.instance_id,0) = 0 ' ;
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances '||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' ) '
|| ' ) u, '
|| ' ( '
|| ' SELECT resource_id,instance_id, shift_num, '
|| ' interval_date,assigned_qty,lead_idate '
|| ' FROM '
|| ' ( '
|| ' SELECT '
|| ' resource_id,instance_id,shift_num, '
|| ' interval_date,1 assigned_qty, '
|| ' lead(resource_id,1) over(order by '
|| ' resource_id,instance_id,interval_date,shift_num) as lead_rid, '
|| ' lead(instance_id,1) over(order by '
|| ' resource_id,instance_id,interval_date,shift_num) as lead_iid, '
|| ' lead(interval_date,1) over(order by '
|| ' resource_id,instance_id,interval_date,shift_num) as lead_idate, '
|| ' lead(shift_num,1) over(order by '
|| ' resource_id,instance_id,interval_date,shift_num) as lead_snum '
|| ' FROM '
|| ' ( '
|| ' SELECT unique cmd.resource_id, '
|| ' cmd.instance_id, '
|| ' exp.shift_num, '
|| ' 1 , '
|| ' cmd.interval_date '
|| ' FROM ( '
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_id instance_id,'
|| ' 0 shift_num,'
|| ' 1 resource_count,'
|| ' un.from_date interval_date '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.orgn_code = :orgn_code3 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) <> 0 ' ;
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_id instance_id,'
|| ' 0 shift_num,'
|| ' 1 resource_count,'
|| ' un.to_date interval_date '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.orgn_code = :orgn_code4 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) <> 0 ' ;
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_id instance_id,'
|| ' 0 shift_num,'
|| ' 1 resource_count,'
|| ' un.from_date interval_date '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.orgn_code = :orgn_code5 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) = 0 ' ;
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances '||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' UNION ALL '
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_id instance_id,'
|| ' 0 shift_num,'
|| ' 1 resource_count,'
|| ' un.to_date interval_date '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v '||p_db_link||' un, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.orgn_code = :orgn_code6 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.schedule_ind = 2 '
|| ' AND nvl(un.instance_id,0) = 0 ' ;
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances '||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' ) cmd, '
|| ' gmp_calendar_detail_gtmp ' ||p_db_link||' exp '
|| ' WHERE exp.calendar_id = :curr_cal1 '
|| ' AND cmd.interval_date BETWEEN '
|| ' exp.from_date AND exp.to_date '
|| ' UNION ALL '
|| ' SELECT crd.resource_id , '
|| ' gri.instance_id, '
|| ' exp.shift_num, '
|| ' 1 , '
|| ' (exp.shift_date + '
|| ' (exp.from_time/86400)) interval_date '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_calendar_detail_gtmp ' ||p_db_link||' exp, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.orgn_code = :orgn_code7 ' ;
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.schedule_ind = 2 ' ;
|| ' SELECT crd.resource_id , '
|| ' gri.instance_id, '
|| ' exp.shift_num, '
|| ' 1 , '
|| ' (exp.shift_date + '
|| ' (exp.to_time/86400)) interval_date '
|| ' FROM cr_rsrc_dtl '||p_db_link||' crd, '
|| ' gmp_calendar_detail_gtmp ' ||p_db_link||' exp, '
|| ' gmp_resource_instances '||p_db_link||' gri '
|| ' WHERE crd.orgn_code = :orgn_code8 ' ;
|| ' AND crd.delete_mark = 0 ' ;
INSERT INTO msc_st_net_res_inst_avail
( Organization_Id,
Department_id,
sr_instance_id ,
Resource_Id,
res_instance_id,
serial_number,
equipment_item_id,
Shift_Num,
Shift_Date,
From_Time,
To_Time
) VALUES
(
p_org_id,
x_dept_id,
p_instance_id ,
x_resource_id(i),
x_instance_id(i),
msc_serial_number(i),
equipment_item_id(i),
shift_num(i),
trunc(f_date(i)),
((f_date(i) - trunc(f_date(i))) * 86400 ),
((t_date(i) - trunc(t_date(i))) * 86400 )
) ;
INSERT INTO gmp_resource_avail
(
instance_id, plant_code, resource_id,
calendar_id, resource_instance_id, shift_num,
shift_date, from_time, to_time,
resource_units, creation_date, created_by,
last_update_date, last_updated_by, last_update_login
) VALUES
(
p_instance_id,
p_orgn_code,
resource_id(i),
p_calendar_id,
instance_id(i),
shift_num(i),
trunc(f_date(i)),
((f_date(i) - trunc(f_date(i))) * 86400 ),
((t_date(i) - trunc(t_date(i))) * 86400 ),
resource_count(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
) ;
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');
INSERT INTO msc_st_net_res_inst_avail
( Organization_Id,
Department_id,
sr_instance_id ,
Resource_Id,
res_instance_id,
serial_number,
equipment_item_id,
Shift_Num,
Shift_Date,
From_Time,
To_Time
) VALUES
(
p_org_id,
x_dept_id,
p_instance_id ,
x_resource_id(i),
x_instance_id(i),
msc_serial_number(i),
equipment_item_id(i),
shift_num(i),
trunc(f_date(i)),
((f_date(i) - trunc(f_date(i))) * 86400 ),
((t_date(i) - trunc(t_date(i))) * 86400 )
) ;
INSERT INTO gmp_resource_avail
(
instance_id, plant_code, resource_id,
calendar_id, resource_instance_id, shift_num,
shift_date, from_time, to_time,
resource_units, creation_date, created_by,
last_update_date, last_updated_by, last_update_login
) VALUES
(
p_instance_id,
p_orgn_code,
resource_id(i),
p_calendar_id,
instance_id(i),
shift_num(i),
trunc(f_date(i)),
((f_date(i) - trunc(f_date(i))) * 86400 ),
((t_date(i) - trunc(t_date(i))) * 86400 ),
resource_count(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
) ;
sql_stmt2 := ' SELECT /*+ ALL_ROWS */ '
|| ' net.resource_id , '
|| ' net.resource_instance_id, '
|| ' net.shift_date , '
|| ' net.shift_num , '
|| ' net.resource_units , '
|| ' min(net.from_time) from_time, '
|| ' max(net.lead_tt) to_time '
|| ' FROM ( '
|| ' SELECT resource_id , '
|| ' resource_instance_id, '
|| ' shift_date , '
|| ' shift_num , '
|| ' from_time , '
|| ' to_time , '
|| ' resource_units , '
|| ' lead(resource_id,1) over(order by resource_id,resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_rid, '
|| ' lead(resource_instance_id,1) over(order by resource_id, '
|| ' resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_iid, '
|| ' lead(shift_date,1) over(order by resource_id,resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_sdt, '
|| ' lead(shift_num,1) over(order by resource_id,resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_sn, '
|| ' lead(from_time,1) over(order by resource_id,resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_ft, '
|| ' lead(to_time,1) over(order by resource_id,resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_tt, '
|| ' lead(resource_units,1) over(order by resource_id, '
|| ' resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_rc '
|| ' FROM gmp_resource_avail'
|| ' WHERE plant_code = :orgn_code1 '
|| ' AND calendar_id = :cal_id ' ;
sql_stmt2 := sql_stmt2 ||' AND resource_id in (select resource_id '
||' from cr_rsrc_dtl '||p_db_link
||' WHERE orgn_code = :orgn_code2 '
||' AND resources BETWEEN :frsrc and :trsrc )' ;
sqlstmt := 'DELETE FROM gmp_resource_avail'
|| ' WHERE plant_code = :Plant_code1 '
|| ' AND calendar_id = :cal_id '
|| ' AND resource_id = :prid '
|| ' AND resource_instance_id = :piid '
|| ' AND shift_date = :psdt '
|| ' AND shift_num = :psn '
|| ' AND from_time >= :pft '
|| ' AND to_time <= :ptt '
|| ' AND resource_units = :prc ' ;
net_rsrc_avail_insert(
p_instance_id,
p_orgn_code,
interval_record.resource_instance_id,
p_calendar_id,
interval_record.resource_id,
interval_record.resource_units,
interval_record.shift_num,
interval_record.shift_date,
interval_record.from_time,
interval_record.to_time
);
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');
sqlupt := 'UPDATE gmp_resource_avail'
||' SET to_time = 86400 '
||' WHERE to_time = 86399 '
||' AND shift_num >= 99999 ' ;
sqlupt := 'UPDATE gmp_resource_avail'
||' SET shift_num = (shift_num - 99999) '
||' WHERE shift_num >= 99999 ' ;
log_message('Error in Net Resource Instance Insert: '||stmt_no);
REM| net_rsrc_avail_insert |
REM| |
REM| Type |
REM| public |
REM| |
REM| DESCRIPTION |
REM| The following procedure inserts rows into gmp_resource_avail |
REM| |
REM| Input Parameters |
REM| p_instance_id - Instance Id |
REM| p_orgn_code - Plant Code |
REM| p_resource_instance_id - Resource Instance Id |
REM| p_Calendar_id - Calendar id |
REM| p_resource_id - Resource Id |
REM| p_assigned_qty - Resource units |
REM| p_shift_num - Shift number |
REM| p_calendar_date - Calendar date |
REM| p_from_time - shift starting time |
REM| p_to_time - Shift Ending time |
REM| |
REM| Output Parameters |
REM| None |
REM| |
REM| HISTORY |
REM| Created on 4th Jan 2002 By Rajesh Patangya |
REM| |
REM+==========================================================================+
*/
PROCEDURE net_rsrc_avail_insert(p_instance_id IN NUMBER,
p_orgn_code IN VARCHAR2,
p_resource_instance_id IN NUMBER,
p_calendar_id IN NUMBER,
p_resource_id IN NUMBER,
p_assigned_qty IN NUMBER,
p_shift_num IN NUMBER,
p_calendar_date IN DATE,
p_from_time IN NUMBER,
p_to_time IN NUMBER ) IS
BEGIN
IF nvl(p_from_time,0) = 0 AND nvl(p_to_time,0) = 0 THEN
NULL ;
INSERT INTO gmp_resource_avail (
instance_id, plant_code, resource_id,
calendar_id, resource_instance_id, shift_num,
shift_date, from_time, to_time,
resource_units, creation_date, created_by,
last_update_date, last_updated_by, last_update_login )
VALUES (
p_instance_id,
p_orgn_code,
p_resource_id,
p_calendar_id,
p_resource_instance_id,
p_shift_num,
p_calendar_date,
p_from_time,
p_to_time,
p_assigned_qty,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID ) ;
log_message('Error in Net Resource Avail Insert ' || sqlerrm);
END net_rsrc_avail_insert;
SELECT MSC_CL_GMP_UTILITY.is_aps_compatible
INTO l_aps_compatible FROM DUAL ;
/*sql_stmt := 'SELECT MSC_CL_PULL.get_org_str(' || instance_id || ') FROM dual ' ;