[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT b.batch_id, b.batch_no, batch_status, batch_type
,
--Bug3315440
DECODE (batch_status
,1, date_to_clientdt (plan_start_date)
,date_to_clientdt (actual_start_date) ) start_date
,DECODE (batch_status
,3, date_to_clientdt (actual_cmplt_date)
,date_to_clientdt (plan_cmplt_date) ) end_date
,date_to_clientdt (plan_start_date)
,date_to_clientdt (plan_cmplt_date)
,DECODE (date_to_clientdt (actual_start_date)
,NULL, date_to_clientdt (plan_start_date)
,date_to_clientdt (actual_start_date) ) actual_start
,DECODE (date_to_clientdt (actual_cmplt_date)
,NULL, date_to_clientdt (plan_cmplt_date)
,date_to_clientdt (actual_cmplt_date) ) actual_end
,NVL(b.formula_id,0), NVL (routing_id, 0)
,NVL (enforce_step_dependency, 0), NVL (terminated_ind, 0)
,
--Bug#2479773 Swapna Kommineni 02-FEB-2005
NVL (finite_scheduled_ind, 0),
NVL(parentline_id,0) --Bug#5550337
FROM gme_batch_header b
WHERE organization_id = p_organization_id
--Bug#4867640 check for batch_no within the given range
AND ((p_to_batch_no IS NULL AND batch_no LIKE p_batch_no) OR
(lpad(batch_no,32,'0') >= lpad(p_batch_no,32,'0') AND
lpad(batch_no,32,'0') <= lpad(p_to_batch_no,32,'0') )
)
AND ( ( batch_type = p_batch_type
AND ( batch_status = p_pending_status
OR batch_status = p_released_status
OR batch_status = p_certified_status) )
OR (batch_type = p_fpo_type AND batch_status = 1) )
AND ( ( batch_status = 1
AND ( plan_start_date >= p_from_date
OR plan_cmplt_date >= p_from_date) )
OR ( batch_status = 2
AND ( actual_start_date >= p_from_date
OR plan_cmplt_date >= p_from_date) )
OR ( batch_status = 3
AND ( actual_start_date >= p_from_date
OR actual_cmplt_date >= p_from_date) ) )
AND ( ( p_resource = '%'
AND p_prim_rsrc_ind = 1
AND p_aux_rsrc_ind = 2
AND p_sec_rsrc_ind = 0)
OR (EXISTS (
SELECT 1
FROM gme_batch_step_resources d2
WHERE d2.batch_id = b.batch_id
AND d2.resources LIKE p_resource
AND ( d2.prim_rsrc_ind = p_prim_rsrc_ind
OR d2.prim_rsrc_ind = p_aux_rsrc_ind
OR d2.prim_rsrc_ind = p_sec_rsrc_ind) ) ) )
ORDER BY 5;
SELECT b.batch_id, batch_no, batch_status, batch_type
,
--Bug3315440
DECODE (batch_status
,1, date_to_clientdt (plan_start_date)
,date_to_clientdt (actual_start_date) ) start_date
,DECODE (batch_status
,3, date_to_clientdt (actual_cmplt_date)
,date_to_clientdt (plan_cmplt_date) ) end_date
,date_to_clientdt (plan_start_date)
,date_to_clientdt (plan_cmplt_date)
,DECODE (date_to_clientdt (actual_start_date)
,NULL, date_to_clientdt (plan_start_date)
,date_to_clientdt (actual_start_date) ) actual_start
,DECODE (date_to_clientdt (actual_cmplt_date)
,NULL, date_to_clientdt (plan_cmplt_date)
,date_to_clientdt (actual_cmplt_date) ) actual_end
,NVL(formula_id,0), NVL (routing_id, 0)
,NVL (enforce_step_dependency, 0), NVL (terminated_ind, 0)
,
--Bug#2479773 Swapna Kommineni 02-FEB-2005
NVL (finite_scheduled_ind, 0),
NVL(parentline_id,0) --Bug#5550337
FROM gme_batch_header b
WHERE organization_id = p_organization_id
AND ((p_to_batch_no IS NULL AND batch_no LIKE p_batch_no) OR
(lpad(batch_no,32,'0') >= lpad(p_batch_no,32,'0') AND
lpad(batch_no,32,'0') <= lpad(p_to_batch_no,32,'0') )
)
AND ( ( batch_type = p_batch_type
AND ( batch_status = p_pending_status
OR batch_status = p_released_status
OR batch_status = p_certified_status) )
OR (batch_type = p_fpo_type AND batch_status = 1) )
AND ( ( batch_status = 1
AND ( ( plan_start_date >= p_from_date
AND plan_start_date <= p_to_date)
OR ( plan_cmplt_date >= p_from_date
AND plan_start_date <= p_to_date) ) )
OR ( batch_status = 2
AND ( ( actual_start_date >= p_from_date
AND actual_start_date <= p_to_date)
OR ( plan_cmplt_date >= p_from_date
AND actual_start_date <= p_to_date) ) )
OR ( batch_status = 3
AND ( ( actual_start_date >= p_from_date
AND actual_start_date <= p_to_date)
OR ( actual_cmplt_date >= p_from_date
AND actual_start_date <= p_to_date) ) ) )
AND ( ( p_resource = '%'
AND p_prim_rsrc_ind = 1
AND p_aux_rsrc_ind = 2
AND p_sec_rsrc_ind = 0)
OR (EXISTS (
SELECT 1
FROM gme_batch_step_resources d2
WHERE d2.batch_id = b.batch_id
AND d2.resources LIKE p_resource
AND ( d2.prim_rsrc_ind = p_prim_rsrc_ind
OR d2.prim_rsrc_ind = p_aux_rsrc_ind
OR d2.prim_rsrc_ind = p_sec_rsrc_ind) ) ) )
ORDER BY 5;
SELECT i.inventory_item_id, i.concatenated_segments, i.description
FROM mtl_system_items_kfv i
,gme_batch_header b
,gmd_recipe_validity_rules v
WHERE b.batch_id = p_batch_id
AND b.recipe_validity_rule_id = v.recipe_validity_rule_id
AND v.inventory_item_id = i.inventory_item_id
AND b.organization_id = i.organization_id;
SELECT i.inventory_item_id, i.concatenated_segments, i.description
FROM mtl_system_items_kfv i
,gme_batch_header b
,gme_material_details m
WHERE b.batch_id = p_batch_id
AND b.batch_id = m.batch_id
AND m.organization_id = i.organization_id
AND m.inventory_item_id = i.inventory_item_id
AND m.line_type = 1
AND m.line_no = 1;
SELECT SUM (plan_qty), NVL (SUM (actual_qty), 0), dtl_um
FROM gme_material_details
WHERE batch_id = p_batch_id
AND line_type = 1
AND inventory_item_id = p_inventory_item_id
GROUP BY dtl_um;
SELECT COUNT (*)
FROM mtl_system_items_kfv i, gme_material_details m
WHERE m.batch_id = p_batch_id
AND m.inventory_item_id = i.inventory_item_id
AND m.line_type = -1
AND m.organization_id = i.organization_id
AND i.concatenated_segments LIKE p_item_no;
SELECT COUNT (*)
FROM mtl_system_items_kfv i, gme_material_details m
WHERE m.batch_id = p_batch_id
AND m.inventory_item_id = i.inventory_item_id
AND m.organization_id = i.organization_id
AND m.line_type = 1
AND i.concatenated_segments LIKE p_item_no;
batch_id.DELETE;
batch_id.DELETE;
SELECT DISTINCT
--Bug3746919 bsatpute
DECODE
(step_status
,1, date_to_clientdt (r.plan_start_date)
,5, date_to_clientdt (b.actual_start_date)
,date_to_clientdt (r.actual_start_date) )
start_date
--NVL(date_to_clientDT(r.actual_start_date),date_to_clientDT(r.plan_start_date)) start_date
,r.batchstep_no, r.step_status, r.plan_step_qty
,DECODE (r.actual_step_qty
,NULL, 0
,r.actual_step_qty) actual_step_qty
,date_to_clientdt (r.plan_start_date)
,NVL (date_to_clientdt (r.actual_start_date)
,date_to_clientdt (r.plan_start_date) )
,date_to_clientdt (r.plan_cmplt_date)
,NVL (date_to_clientdt (r.actual_cmplt_date)
,date_to_clientdt (r.plan_cmplt_date) )
,NVL (date_to_clientdt (r.step_close_date)
,date_to_clientdt (SYSDATE) )
,o.oprn_id, o.oprn_no, NVL (o.oprn_vers, 0)
,o.oprn_desc
,DECODE (o.oprn_class
,NULL, '?'
,o.oprn_class) oper_class
,o.process_qty_um
,DECODE
(step_status
,3, date_to_clientdt (r.actual_cmplt_date)
,date_to_clientdt (r.plan_cmplt_date) ) end_date
--bug3746919 bsatpute
,NVL (r.terminated_ind, 0)
FROM gme_batch_steps r
,gme_batch_step_resources d
,gmd_operations o
,gme_batch_header b
WHERE r.batch_id = p_batch_id
AND r.batch_id = d.batch_id
AND r.batch_id = b.batch_id
AND r.oprn_id = o.oprn_id
AND r.batchstep_id = d.batchstep_id
AND d.resources LIKE p_resource
AND ( d.prim_rsrc_ind = p_prim_rsrc_ind
OR d.prim_rsrc_ind = p_aux_rsrc_ind
OR d.prim_rsrc_ind = p_sec_rsrc_ind)
ORDER BY 1;
SELECT oprn_class_desc
FROM gmd_operation_class
WHERE oprn_class = p_oper_class;
oprn_id.DELETE;
SELECT a.activity, a.activity_desc, a.cost_analysis_code
,c.cost_analysis_desc
,MIN
(DECODE (r.step_status
,1, date_to_clientdt (o.plan_start_date)
,5, date_to_clientdt (b.actual_start_date)
,date_to_clientdt (o.actual_start_date) ) )
start_date
,MAX
(DECODE (r.step_status
,3, date_to_clientdt (o.actual_cmplt_date)
,date_to_clientdt (o.plan_cmplt_date) ) )
end_date
FROM gme_batch_step_resources o
,gme_batch_steps r
,gme_batch_step_activities s
,gmd_activities a
,gme_batch_header b
,cm_alys_mst c
WHERE o.batch_id = p_batch_id
AND r.batch_id = o.batch_id
AND s.batch_id = r.batch_id
AND b.batch_id = r.batch_id
AND s.delete_mark = 0
AND o.batchstep_activity_id = s.batchstep_activity_id
AND r.batchstep_no = p_batchstep_no
AND o.batchstep_id = r.batchstep_id
AND s.activity = a.activity
AND c.cost_analysis_code = a.cost_analysis_code
AND o.resources LIKE p_resource
AND ( o.prim_rsrc_ind = p_prim_rsrc_ind
OR o.prim_rsrc_ind = p_aux_rsrc_ind
OR o.prim_rsrc_ind = p_sec_rsrc_ind)
GROUP BY a.activity
,a.activity_desc
,a.cost_analysis_code
,c.cost_analysis_desc
ORDER BY 5;
activity.DELETE;
SELECT o.resources, c.resource_desc, o.prim_rsrc_ind
,o.scale_type, NVL (o.plan_rsrc_count, 0)
,DECODE (o.actual_rsrc_count
,NULL, 0
,o.actual_rsrc_count) actual_res_count
,NVL (o.plan_rsrc_qty, 0)
,DECODE (o.actual_rsrc_qty
,NULL, 0
,o.actual_rsrc_qty) actual_res_qty
,NVL (o.plan_rsrc_usage, 0)
,DECODE (o.actual_rsrc_usage
,NULL, 0
,o.actual_rsrc_usage) actual_res_usage
,NVL (c.std_usage_um, ' '), NVL (o.offset_interval, 0)
--Swapna K Bug#2479773 17-MAR-2005 Rework
/* Null actual start date is sent as planned start date */
,DECODE
(r.step_status
,1, date_to_clientdt (o.plan_start_date)
,5, date_to_clientdt (b.actual_start_date)
,NVL (date_to_clientdt (o.actual_start_date)
,date_to_clientdt (o.plan_start_date) ) )
start_date
,DECODE (r.step_status
,3, date_to_clientdt (o.actual_cmplt_date)
,date_to_clientdt (o.plan_cmplt_date) ) end_date
,date_to_clientdt (o.plan_start_date)
,date_to_clientdt (o.plan_cmplt_date)
,NVL (date_to_clientdt (o.actual_start_date)
,date_to_clientdt (o.plan_start_date) )
,NVL (date_to_clientdt (o.actual_cmplt_date)
,date_to_clientdt (o.plan_cmplt_date) )
,NVL (f.process_qty_um, ' '), NVL (r.step_status, 0)
FROM gme_batch_steps r
,gme_batch_step_activities a
,gme_batch_step_resources o
,gmd_operations f
,gme_batch_header b
,cr_rsrc_mst c
WHERE r.batch_id = p_batch_id
AND b.batch_id = r.batch_id
AND r.batchstep_no = p_batchstep_no
AND a.batchstep_id = r.batchstep_id
AND a.delete_mark = 0
AND o.batchstep_id = a.batchstep_id
AND o.batchstep_activity_id = a.batchstep_activity_id
AND a.activity = p_activity
AND f.oprn_id = r.oprn_id
AND r.batch_id = o.batch_id
AND o.resources = c.resources
AND o.resources LIKE p_resource
AND ( o.prim_rsrc_ind = p_prim_rsrc_ind
OR o.prim_rsrc_ind = p_aux_rsrc_ind
OR o.prim_rsrc_ind = p_sec_rsrc_ind)
ORDER BY 13;
resources.DELETE;
SELECT b.formula_id, b.routing_id, b.recipe_validity_rule_id
,v.recipe_id
FROM gme_batch_header b, gmd_recipe_validity_rules v
WHERE b.batch_id = p_batch_id
AND b.recipe_validity_rule_id = v.recipe_validity_rule_id(+);
SELECT routing_no, routing_vers, routing_desc
FROM gmd_routings
WHERE routing_id = p_routing_id;
SELECT formula_no, formula_vers, formula_desc1
FROM fm_form_mst
WHERE formula_id = p_formula_id;
SELECT recipe_no, recipe_version, recipe_description
FROM gmd_recipes
WHERE recipe_id = p_recipe_id;
SELECT p.calendar_code, c.description
FROM mtl_parameters p, bom_calendars c
WHERE p.organization_id = p_organization_id
AND c.calendar_code = p.calendar_code;
SELECT MIN (date_to_clientdt (calendar_start_date) )
,MAX (date_to_clientdt (calendar_end_date) )
FROM bom_calendars
WHERE calendar_code = p_calendar_code;