[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
x_stmt := ' SELECT ' ||
''''||G_forg||''''||' forg, ' ||
''''||G_torg||''''||' torg, ' ||
''''||G_fres||''''||' fres, ' ||
''''||G_tores||''''||' tores, ' ||
''''||G_fres_instance||''''||' fres_instance, ' ||
''''||G_tores_instance||''''||' tores_instance, ' ||
''''||G_start_date||''''||' fdate, ' ||
''''||G_to_date||''''||' tdate, ' ||
' CURSOR( ' ||
' SELECT ' ||
' gmp_despatch_load_rpt_pkg.get_orgn_code(mp.organization_id) organzation_code, ' ||
' crd.resources resource_desc, ' ||
' crd.usage_uom uom, '||
' gi.instance_number instance_number, ' ||
' CURSOR( ' ||
' SELECT h.batch_no batch_no, ' ||
' gs.batchstep_no operation, ' ||
' gsa.activity activity, ' ||
' to_char(t.start_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') start_date, '||
' to_char(t.end_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') end_date, '||
' msi.segment1 Item, ' ||
' round(t.resource_usage,3) resource_usage ' ||
' FROM ' ||
' gme_batch_header h , '||
' gme_batch_steps gs , '||
' gme_material_details gmt, '||
' gme_batch_step_activities gsa, '||
' gme_batch_step_resources gsr, '||
' gme_batch_step_items gsi, '||
' gme_resource_txns t, '||
' mtl_system_items msi, '||
' gmp_resource_instances gri '||
' WHERE h.organization_id = mp.organization_id '||
' and h.batch_id = gs.batch_id '||
' and h.batch_status in (1,2) '||
' and gs.batch_id = gsa.batch_id '||
' and gs.step_status in (1,2) '||
' and gs.batchstep_id = gsa.batchstep_id '||
' and gmt.batch_id = gs.batch_id '||
' and gmt.batch_id = gsi.batch_id (+) '||
' and gmt.material_detail_id = gsi.material_detail_id (+) '||
' and gsi.batchstep_id = gs.batchstep_id (+) '||
' and gsr.batch_id = gsa.batch_id '||
' and gsr.batchstep_id = gsa.batchstep_id '||
' and gsr.batchstep_activity_id = gsa.batchstep_activity_id '||
' and gsr.resources = crd.resources '||
' and gmt.organization_id = msi.organization_id '||
' and gmt.inventory_item_id = msi.inventory_item_id '||
' and gmt.batch_id = t.doc_id '||
' and gsr.batchstep_resource_id = t.line_id '||
' and t.completed_ind = 0 '||
' and t.delete_mark = 0 '||
' and t.start_date >= nvl( to_date('||''''||to_char(G_start_date, 'dd-mm-yy hh24:mi:ss' )||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), t.start_date) '||
' and t.end_date <= nvl( to_date('||''''||to_char(G_to_date, 'dd-mm-yy hh24:mi:ss')||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), t.end_date) '||
' and t.instance_id = gri.instance_id (+) '||
' and nvl(gri.inactive_ind,0) = 0 '||
' and gri.resource_id (+) = gi.resource_id '||
' and gri.instance_id (+) = gi.instance_id '||
' order by 1,2,4 '||
' ) DETAIL ' ||
' FROM ' ||
' mtl_parameters mp, ' ||
' hr_organization_units hr, ' ||
' cr_rsrc_dtl crd, ' ||
' gmp_resource_instances gi '||
' WHERE mp.organization_id = hr.organization_id '||
' and mp.organization_id between '||''''||f_org||''''||' AND '||''''||t_org||''''||
' and crd.resources between nvl('||''''||G_fres||''''||', crd.resources) and nvl('||''''||G_tores||''''||', crd.resources)'||
' and mp.process_enabled_flag = '||''''||'Y'||''''||
' and nvl(hr.date_to,sysdate) >= sysdate '||
' and crd.organization_id = mp.organization_id '||
' and crd.resource_id = gi.resource_id (+) ';
INSERT INTO GMP_RESDISP_XML_TEMP (RESDISP_XML_RPT_ID,RESULT) VALUES (resdisp_rpt_id, result1);
seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
x_stmt1 := ' SELECT ' ||
''''||G_inst||''''||' Inst, ' ||
''''||G_plan||''''||' Plan, ' ||
''''||G_forg||''''||' forg, ' ||
''''||G_torg||''''||' torg, ' ||
''''||G_fres||''''||' fres, ' ||
''''||G_tores||''''||' tores, ' ||
''''||G_fres_instance||''''||' fres_instance, ' ||
''''||G_tores_instance||''''||' tores_instance, ' ||
''''||G_start_date||''''||' fdate, ' ||
''''||G_to_date||''''||' tdate, ' ||
' CURSOR( ' ||
' SELECT ' ||
' gmp_despatch_load_rpt_pkg.get_orgn_code(dr.organization_id) organization_code, '||
' substr(dr.resource_description,1,15) resource_desc,'||
' dr.unit_of_measure UOM, '||
' mri.serial_number Instance_number,'||
' CURSOR( ' ||
' SELECT ms.transaction_id Order_Id, '||
' decode(rr.routing_sequence_id,null,null,rt.routing_comment) routing_desc, '||
' decode(rr.operation_sequence_id,null,null,mr.operation_seq_num) oprseq_no, '||
' to_char(rr.start_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') St_date, '||
' to_char(rr.end_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') Edate, '||
' msi.item_name Item, '||
' round(rr.resource_hours,3) resource_usage '||
' FROM ' ||
' msc_resource_requirements rr, '||
' msc_supplies ms, '||
' msc_system_items msi, '||
' msc_operation_resources mor, '||
' msc_routing_operations mr, '||
' msc_routings rt '||
' WHERE rr.sr_instance_id = ms.sr_instance_id '||
' AND rr.sr_instance_id = msi.sr_instance_id '||
' AND rr.sr_instance_id = mor.sr_instance_id '||
' AND rr.sr_instance_id = mr.sr_instance_id '||
' AND rr.sr_instance_id = rt.sr_instance_id '||
' AND rr.sr_instance_id = dr.sr_instance_id '||
' AND rr.plan_id = ms.plan_id '||
' AND rr.plan_id = msi.plan_id '||
' AND rr.plan_id = mor.plan_id '||
' AND rr.plan_id = mr.plan_id '||
' AND rr.plan_id = rt.plan_id '||
' AND rr.plan_id = dr.plan_id '||
' AND rr.organization_id = ms.organization_id '||
' AND rr.organization_id = msi.organization_id '||
' AND rr.organization_id = mor.organization_id '||
-- ' AND rr.organization_id = mr.organization_id '||
' AND rr.organization_id = rt.organization_id '||
' AND rr.organization_id = dr.organization_id '||
' AND rr.supply_id = ms.transaction_id '||
' AND ms.inventory_item_id = msi.inventory_item_id '||
' AND rr.resource_id = mor.resource_id '||
' AND rr.resource_id = dr.resource_id '||
' AND rr.start_date >= nvl( to_date('||''''||to_char(G_start_date, 'dd-mm-yy hh24:mi:ss' )||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), rr.start_date) '||
' AND rr.end_date <= nvl( to_date('||''''||to_char(G_to_date, 'dd-mm-yy hh24:mi:ss')||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), rr.end_date) '||
' AND rr.resource_hours > 0 '||
' AND rr.parent_id <> 2 ' ||
' AND mor.routing_sequence_id = mr.routing_sequence_id '||
' AND mor.operation_sequence_id = mr.operation_sequence_id '||
' AND mr.routing_sequence_id = rt.routing_sequence_id '||
' AND ms.order_type IN ( 5,17 ) '||
' UNION '||
' SELECT md.disposition_id Order_Id, '||
' decode(rr.routing_sequence_id,null,null,rt.routing_comment) routing_desc, '||
' decode(rr.operation_sequence_id,null,null,mr.operation_seq_num) oprseq_no, '||
' to_char(rr.start_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') St_date, '||
' to_char(rr.end_date, '||''''||'dd-mon-yy hh24:mi:ss'||''''||') Edate, '||
' msi.item_name Item, '||
' round(rr.resource_hours,3) resource_usage '||
' FROM ' ||
' msc_resource_requirements rr, '||
' msc_demands md, '||
' msc_system_items msi, '||
' msc_operation_resources mor, '||
' msc_routing_operations mr, '||
' msc_routings rt '||
' WHERE rr.sr_instance_id = md.sr_instance_id '||
' AND rr.sr_instance_id = msi.sr_instance_id '||
' AND rr.sr_instance_id = mor.sr_instance_id '||
' AND rr.sr_instance_id = mr.sr_instance_id '||
' AND rr.sr_instance_id = rt.sr_instance_id '||
' AND rr.sr_instance_id = dr.sr_instance_id '||
' AND rr.plan_id = md.plan_id '||
' AND rr.plan_id = msi.plan_id '||
' AND rr.plan_id = mor.plan_id '||
' AND rr.plan_id = mr.plan_id '||
' AND rr.plan_id = rt.plan_id '||
' AND rr.plan_id = dr.plan_id '||
' AND rr.organization_id = md.organization_id '||
' AND rr.organization_id = msi.organization_id '||
' AND rr.organization_id = mor.organization_id '||
-- ' AND rr.organization_id = mr.organization_id '||
' AND rr.organization_id = rt.organization_id '||
' AND rr.organization_id = dr.organization_id '||
' AND rr.supply_id = md.disposition_id '||
' AND md.inventory_item_id = msi.inventory_item_id '||
' AND rr.resource_id = mor.resource_id '||
' AND rr.resource_id = dr.resource_id '||
' AND rr.start_date >= nvl( to_date('||''''||to_char(G_start_date, 'dd-mm-yy hh24:mi:ss' )||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), rr.start_date) '||
' AND rr.end_date <= nvl( to_date('||''''||to_char(G_to_date, 'dd-mm-yy hh24:mi:ss')||''''||','||''''||'dd-mm-yy hh24:mi:ss'||''''||'), rr.end_date) '||
' AND rr.resource_hours > 0 '||
' AND rr.parent_id <> 2 ' ||
' AND mor.routing_sequence_id = mr.routing_sequence_id '||
' AND mor.operation_sequence_id = mr.operation_sequence_id '||
' AND mr.routing_sequence_id = rt.routing_sequence_id '||
' AND md.origination_type = 1 '||
' ORDER BY 1,4 '||
' ) DETAIL ' ||
' FROM ' ||
' msc_dept_res_instances mri, '||
' msc_department_resources dr '||
' WHERE dr.sr_instance_id = '|| inst_id ||
' AND dr.sr_instance_id = mri.sr_instance_id (+) '||
' AND dr.plan_id = '|| plan_id ||
' AND dr.plan_id = mri.plan_id (+)'||
' AND dr.organization_id between '||''''||f_org||''''||' AND '||''''||t_org||''''||
' AND dr.organization_id = mri.organization_id (+) '||
' AND dr.resource_id BETWEEN nvl('||''''||f_res||''''||', dr.resource_id) and nvl('||''''||t_res||''''||', dr.resource_id)'||
' AND dr.resource_id = mri.resource_id (+) ';
INSERT INTO GMP_RESDISP_XML_TEMP (RESDISP_XML_RPT_ID,RESULT) VALUES (resdisp_rpt_id, result1);
SELECT organization_code FROM mtl_parameters
WHERE organization_id = p_orgn_id;
SELECT instance_code FROM msc_apps_instances
WHERE instance_id = p_inst_id;
SELECT compile_designator FROM msc_plans
WHERE plan_id = p_plan_id;
SELECT distinct substr(resource_description,1,16) FROM msc_department_resources
WHERE plan_id = plan_id
AND resource_id = p_resource_id
AND sr_instance_id = inst_id;
DELETE FROM GMP_RESDISP_XML_TEMP WHERE RESDISP_XML_RPT_ID = p_sequence_num;
SELECT RESULT INTO l_file
FROM GMP_RESDISP_XML_TEMP
WHERE RESDISP_XML_RPT_ID = p_sequence_num;