[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Select the available resources for the current organization and send them one by
* one to the gantt
*/
PROCEDURE get_available_plant_resources (
p_organization_id IN NUMBER
,x_nb_resources OUT NOCOPY NUMBER
,x_plant_rsrc_tbl OUT NOCOPY plantresourcedetailtabletype)
IS
CURSOR get_plant_resources
IS
SELECT DISTINCT r.resources, r.resource_desc
FROM cr_rsrc_mst r, cr_rsrc_dtl d
WHERE d.organization_id = p_organization_id
AND d.delete_mark = 0
AND r.resources = d.resources
AND r.delete_mark = 0
ORDER BY 1;
resources.DELETE;
* Fetch pending and WIP batches that consume the selected resource at the selected time.
*/
PROCEDURE get_reschedule_batch_list (
p_organization_id IN NUMBER
,p_resource IN VARCHAR2
,p_from_date IN DATE
,p_to_date IN DATE
,x_nb_batches OUT NOCOPY NUMBER
,x_resch_batch_tbl OUT NOCOPY reschbatchdetailtabletype)
IS
CURSOR get_product_info (p_batch_id NUMBER)
IS
SELECT i.inventory_item_id, i.concatenated_segments
FROM mtl_system_items_kfv i --Bug#5550337 changed from ic_item_mst
,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;
SELECT b.batch_id, b.batch_no, b.batch_type, b.batch_status
/*Bug#5350537 We have to show planned start date in reschedule window irrespective of batch status
commented the following code and seletcing plan start date directly */
,b.plan_start_date
/*,DECODE (b.batch_status
,1, b.plan_start_date
,b.actual_start_date) start_date */
,b.plan_cmplt_date, NVL (b.enforce_step_dependency, 0)
FROM gme_batch_header b
WHERE b.organization_id = p_organization_id
AND b.parentline_id IS NULL --Bug#5550337 not showing phantom batches as we cant reschedule
AND b.batch_status IN (1, 2)
AND ( ( b.plan_start_date >= p_from_date
AND b.plan_start_date <= p_to_date)
OR ( b.plan_cmplt_date >= p_from_date
AND b.plan_start_date <= p_to_date) )
AND EXISTS (
SELECT 1
FROM gme_batch_steps r, gme_batch_step_resources o
WHERE o.batch_id = b.batch_id
AND r.batch_id = o.batch_id
AND o.batchstep_id = r.batchstep_id
AND r.step_status IN (1, 2)
AND r.delete_mark = 0
AND o.resources = p_resource
AND ( ( o.plan_start_date >= p_from_date
AND o.plan_start_date <= p_to_date)
OR ( o.plan_cmplt_date >= p_from_date
AND o.plan_start_date <= p_to_date) ) )
ORDER BY 4;
batch_id.DELETE;
SELECT start_date from_date, end_date end_DATE
,SUM (required_units) rsrc_count
FROM gme_resource_txns_summary
WHERE resource_id = l_resource_id
AND start_date >= p_from_date
AND start_date <= p_to_date
GROUP BY start_date, end_date
UNION
SELECT start_date from_date, end_date end_DATE
,SUM (required_units) rsrc_count
FROM gme_resource_txns_summary
WHERE resource_id = l_resource_id
AND end_date >= p_from_date
AND start_date <= p_to_date
GROUP BY start_date, end_date
ORDER BY 1;
SELECT d.resource_id, m.std_usage_um, m.resource_desc
FROM cr_rsrc_mst m, cr_rsrc_dtl d
WHERE d.organization_id = p_organization_id
AND d.resources = p_resource_code
AND d.resources = m.resources;
from_date.DELETE;