[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rdtl.routing_id ,
rhdr.routing_no ,
rhdr.routing_qty,
rhdr.routing_uom, /*Sowmya - Inventory convergence*/
rdtl.routingstep_id ,
opr.oprn_no ,
rdtl.step_qty ,
opr.process_qty_uom, /*Sowmya - Inventory convergence*/
act.activity ,
act.activity_factor,
act.oprn_line_id ,
ores.resources ,
ores.process_qty ,
ores.resource_process_uom, /*Sowmya - Inventory convergence*/
ores.resource_count ,
inv_convert.inv_um_convert
(-1, -- Item_id
38, -- Precision
ores.resource_usage, -- Quantity
ores.resource_usage_uom , -- from Unit
g_uom_hr , -- To Unit
NULL , -- From_name
NULL -- To_name
) resource_usage, -- B5885931
ores.resource_usage_uom, /*Sowmya - Inventory convergence*/
ores.scale_type ,
ores.prim_rsrc_ind,
act.material_ind ,
-1 o_step_qy,
-1 o_process_qty,
-1 o_resource_usage,
-1 o_activity_factor
FROM gmd_operations opr,
gmd_operation_activities act,
gmd_operation_resources ores,
fm_rout_dtl rdtl,
gmd_routings_b rhdr,
(SELECT DISTINCT gr.routing_id
FROM gmd_recipes_b gr,
gmd_recipe_validity_rules grv,
gmd_status_b gs
WHERE gr.recipe_id = grv.recipe_id
AND grv.validity_rule_status = gs.status_code
AND gs.status_type IN ('700','900')
AND grv.recipe_use IN ('0','1') /* B13598533 */
AND gs.delete_mark = 0
AND gr.delete_mark = 0
AND grv.delete_mark = 0
) eff
WHERE eff.routing_id = rhdr.routing_id
AND rhdr.routing_id = rdtl.routing_id
AND rdtl.oprn_id = opr.oprn_id
AND opr.oprn_id = act.oprn_id
AND act.oprn_line_id = ores.oprn_line_id
AND ores.prim_rsrc_ind IN (1,2)
AND opr.delete_mark = 0
AND ores.delete_mark = 0
AND rhdr.delete_mark = 0
ORDER BY
rdtl.routing_id, rdtl.routingstep_id, act.oprn_line_id, act.offset_interval,
ores.prim_rsrc_ind, ores.resources ;
recipe_orgn_statement := ' SELECT '
||' grb.routing_id, gc.organization_id, '
||' gc.routingstep_id, gc.oprn_line_id, gc.recipe_id, '
||' gc.activity_factor, '
||' gc.resources, gc.resource_usage, gc.process_qty '
||' FROM gmd_recipes grb, '
||' gmd_status_b gs, '
||' ( '
||' SELECT '
||' gor.recipe_id, '
||' gor.organization_id, '
||' gor.oprn_line_id, '
||' gor.routingstep_id, '
||' goa.activity_factor, '
||' gor.resources, '
||' inv_convert.inv_um_convert '
||' (-1, ' -- Item_id
||' 38,' -- Precision
||' gor.resource_usage,' -- Quantity
||' gor.usage_uom , ' -- from Unit
||' :b_uom_hr , ' -- To Unit
||' NULL , ' -- From_name
||' NULL ' -- To_name
||' ) resource_usage, ' -- B5885931
||' gor.process_qty '
||' FROM gmd_recipe_orgn_activities goa, '
||' gmd_recipe_orgn_resources gor '
||' WHERE gor.recipe_id = goa.recipe_id '
||' AND gor.organization_id = goa.organization_id '
||' AND gor.oprn_line_id = goa.oprn_line_id '
||' AND gor.routingstep_id = goa.routingstep_id '
||' UNION ALL '
||' SELECT goa.recipe_id, '
||' goa.organization_id, '
||' goa.oprn_line_id, '
||' goa.routingstep_id, '
||' goa.activity_factor, '
||' NULL resources, '
||' -1 resource_usage, '
||' -1 process_qty '
||' FROM gmd_recipe_orgn_activities goa '
||' WHERE NOT EXISTS( SELECT 1 '
||' FROM gmd_recipe_orgn_resources gor '
||' WHERE gor.recipe_id = goa.recipe_id '
||' AND gor.organization_id = goa.organization_id '
||' AND gor.oprn_line_id = goa.oprn_line_id '
||' AND gor.routingstep_id = goa.routingstep_id ) '
||' UNION ALL '
||' SELECT gor.recipe_id, '
||' gor.organization_id, '
||' gor.oprn_line_id, '
||' gor.routingstep_id, '
||' -1 activity_factor, '
||' gor.resources, '
||' inv_convert.inv_um_convert '
||' (-1, ' -- Item_id
||' 38,' -- Precision
||' gor.resource_usage,' -- Quantity
||' gor.usage_uom , ' -- from Unit
||' :b_uom_hr , ' -- To Unit
||' NULL , ' -- From_name
||' NULL ' -- To_name
||' ) resource_usage, ' -- B5885931
||' gor.process_qty '
||' FROM gmd_recipe_orgn_resources gor '
||' WHERE NOT EXISTS( SELECT 1 '
||' FROM gmd_recipe_orgn_activities goa'
||' WHERE goa.recipe_id = gor.recipe_id '
||' AND goa.organization_id = gor.organization_id '
||' AND goa.oprn_line_id = gor.oprn_line_id '
||' AND goa.routingstep_id = gor.routingstep_id ) '
||' ) gc '
||' , gmd_operation_activities act ' /* Bug 13877108 */
||' , gmd_operation_resources ores ' /* Bug 13877108 */
||' WHERE grb.recipe_id = gc.recipe_id '
||' AND grb.delete_mark = 0 '
||' AND grb.recipe_status = gs.status_code '
||' AND gs.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ') '
||' AND gs.delete_mark = 0 '
||' AND gc.organization_id >= NVL(:orgn_id,gc.organization_id) '
||' AND gc.organization_id <= NVL(:to_orgn,gc.organization_id) '
||' AND act.oprn_line_id = gc.oprn_line_id '
||' AND act.oprn_line_id = ores.oprn_line_id '
||' AND ores.prim_rsrc_ind IN (1,2) '
||' AND ores.delete_mark = 0 '
||' AND ores.resources = gc.resources '
||' ORDER BY ' /* Bug 13877108 */
||' grb.routing_id, gc.organization_id, '
||' gc.routingstep_id, gc.oprn_line_id, act.offset_interval, '
||' ores.prim_rsrc_ind, gc.resources, gc.recipe_id ' ;
' SELECT grb.routing_id, grs.routingstep_id, grs.recipe_id, '
||' grs.step_qty '
||' FROM gmd_recipes grb, '
||' gmd_status_b gs, '
||' gmd_recipe_routing_steps grs '
||' WHERE grb.recipe_id = grs.recipe_id '
||' AND grb.delete_mark = 0 '
||' AND grb.recipe_status = gs.status_code '
||' AND gs.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ') '
||' AND gs.delete_mark = 0 '
||' ORDER BY 1,2,3 ' ;
sql_stmt := ' SELECT '
||' msi.inventory_item_id, '
||' msi.segment1, ' --Added as part of inventory convergence
||' inv_convert.inv_um_convert '
||' (eff.inventory_item_id, '
||' NULL, '
||' msi.organization_id, '
||' NULL, '
||' eff.std_qty, '
||' msi.primary_uom_code , ' /* primary */
||' eff.detail_uom , ' /* routing um */
||' NULL , '
||' NULL '
||' ) eff_qty, '
||' msi.primary_uom_code , '
||' mp.organization_id , '
||' gmp_lead_time_calculator_pkg.get_avg_working_hours(bc.calendar_code) , '
||' eff.fmeff_id, '
||' eff.recipe_id, '
||' eff.formula_id, '
||' eff.routing_id, '
||' prvr.scale_type, '
||' ffm.total_output_qty, ' -- Bug: 8736658 Added code to fetch total output of the formula
||' ffm.yield_uom ' -- Bug: 8736658 Added code to fetch yield uom of the formula
||' FROM mtl_parameters mp, '
||' mtl_system_items msi, '
||' bom_calendars bc, '
||' fm_form_eff eff, '
||' fm_form_mst ffm, '
||' (SELECT DISTINCT '
||' NVL(eff.organization_id,mp.organization_id) organization_id, '
||' eff.fmeff_id pref_eff, '
||' DENSE_RANK () '
||' OVER (PARTITION BY eff.inventory_item_id,NVL(eff.organization_id,mp.organization_id) '
||' ORDER BY eff.preference,eff.last_update_date DESC) drank, '
||' DENSE_RANK () '
||' OVER (PARTITION BY fmd.inventory_item_id,fmd.organization_id '
--Start of Bug 10398492
--||' ORDER BY fmd.line_no, fmd.last_update_date DESC) frank, '
||' ORDER BY fmd.line_no DESC) frank, '--,fmd.last_update_date DESC) frank, '
--End of Bug 10398492
||' eff.inventory_item_id , '
||' fmd.scale_type ' -- Bug: 8736658 Added code to fetch scale type of product item
||' FROM gmd_status_b gs, '
||' mtl_parameters mp, '
||' hr_organization_units hr, '
||' fm_form_eff eff , '
||' Fm_Matl_Dtl fmd '
||' WHERE NVL(eff.organization_id,mp.organization_id) = mp.organization_id '
||' AND eff.validity_rule_status = gs.status_code '
||' AND eff.formula_use IN (''0'',''1'') ' /* B13598533 */
||' AND gs.delete_mark = 0 ' /* B13598533 */
||' AND eff.delete_mark = 0 ' /* B13598533 */
||' AND fmd.formula_id = eff.formula_id '
||' AND fmd.line_type = 1 '
||' AND fmd.inventory_item_id = eff.inventory_item_id '
||' AND gs.status_type IN (' ||'''700'''|| ',' ||'''900'''|| ') '
--B3696730 niyadav 06/22/2004 code changes end.
||' AND mp.organization_id = hr.organization_id '
||' AND mp.process_enabled_flag = '||''''||'Y'||''''
||' AND nvl(hr.date_to,sysdate) >= sysdate '
--Inventory convergence. Resource whse does not exist any longer
--'AND sy.resource_whse_code IS NOT NULL '
||' ) prvr '
||' WHERE eff.fmeff_id = prvr.pref_eff '
||' AND ffm.formula_id = eff.formula_id '
||' AND prvr.organization_id = mp.organization_id '
||' AND mp.calendar_code = bc.calendar_code '
||' AND eff.formula_use IN (''0'',''1'') ' /* B13598533 */
||' AND eff.delete_mark = 0 ' /* B13598533 */
||' AND eff.inventory_item_id = msi.inventory_item_id '
||' AND msi.organization_id = mp.organization_id '
||' AND msi.inventory_item_id >= NVL(:from_item_id,msi.inventory_item_id ) '
||' AND msi.inventory_item_id <= NVL(:to_item_id,msi.inventory_item_id ) '
||' AND msi.organization_id between NVL(:from_org_id,msi.organization_id) '
||' and NVL(:to_org_id,msi.organization_id) '
||' AND drank = 1 '
||' AND frank = 1 '
||' ORDER BY eff.routing_id , eff.recipe_id ' ;
log_message('Total number of Items updated : '||g_item_cnt );
rtg_steps_tbl.DELETE ;
SELECT calculate_step_quantity
FROM gmd_recipes
WHERE recipe_id = item_eff.recipe_id;
/* log_message('Step quantities updated..'); */
/* log_message('Step quantities updated..'); */
log_message('Recipe Step quantity override update failed');
/* log_message('Step quantities updated..'); */
takes care of the Update_item API , currently the API
returns error for many seemingly correct data conditions
l_item_rec.organization_id := l_inv_org_id ;
inv_item_grp.update_item (
fnd_api.g_TRUE,
fnd_api.g_TRUE,
1,
l_item_rec ,
o_item_rec ,
temp_ret_stat,
l_error_tbl
);
UPDATE mtl_system_items
SET
fixed_lead_time = l_temp_f_lead_time,
variable_lead_time = l_temp_v_lead_time ,
lead_time_lot_size = 1,
last_update_date = g_curr_time,
last_updated_by = g_user_id
--WHERE organization_id = item_eff.mtl_org_id
WHERE organization_id = item_eff.org_id
AND inventory_item_id = item_eff.inventory_item_id;
log_message('Error occurred during item attribute update '|| SQLERRM) ;
END ; -- anonymous block for item update
DELETE FROM gmp_routing_offsets
WHERE fmeff_id = item_eff.fmeff_id
AND organization_id = item_eff.org_id;
END ; -- anonymous block for rtg off delete
INSERT INTO gmp_routing_offsets(
/*Sowmya - Inventory convergence - commented plant code and included organization id*/
--plant_code, fmeff_id, recipe_id,
organization_id,
fmeff_id,
recipe_id,
formula_id,
routing_id,
routingstep_id,
start_offset,
end_offset,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login )
VALUES (
/*Sowmya - Inventory convergence - commented plant code and included organization id*/
--item_eff.org_code, item_eff.fmeff_id,item_eff.recipe_id,
item_eff.org_id,
item_eff.fmeff_id,
item_eff.recipe_id,
item_eff.formula_id,
rtg_steps_tbl(i).routing_id,
rtg_steps_tbl(i).routingstep_id,
rtg_steps_tbl(i).start_offset,
rtg_steps_tbl(i).end_offset,
g_curr_time,
g_user_id,
g_curr_time,
g_user_id,
g_user_id)
;
log_message('Error in insert into gmp_routing_offsets'||SQLERRM) ;
END ; -- end of anonymous block for insert
SELECT TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS')
INTO cur_time FROM sys.dual ;
SELECT (SUM(days_on) + SUM(days_off) )/ SUM(days_on) work_ratio
FROM bom_workday_patterns
WHERE shift_num IS NULL
AND calendar_code = p_calendar_code ;
SELECT
p.calendar_code,
p.shift_num,
SUM(p.days_on *( (DECODE ((SIGN (st.to_time - st.from_time) ),-1,((86400-st.from_time)+st.to_time),(st.to_time-st.from_time)) ) / 3600 )) work_hrs
FROM bom_workday_patterns p, bom_calendar_shifts s, bom_shift_times st
WHERE p.calendar_code = s.calendar_code
AND p.shift_num = s.shift_num
AND s.calendar_code = st.calendar_code
AND s.shift_num = st.shift_num
AND p.shift_num IS NOT NULL
AND p.calendar_code = p_calendar_code
GROUP BY p.calendar_code, p.shift_num;
SELECT
(SUM(p.days_on) + SUM(p.days_off)) tot_days
FROM bom_workday_patterns p, bom_calendar_shifts s
WHERE p.calendar_code = s.calendar_code
AND p.shift_num = l_shift_num
AND s.shift_num = p.shift_num
AND p.shift_num IS NOT NULL
AND p.calendar_code = l_calendar_code
GROUP BY p.calendar_code, p.shift_num;