The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ INDEX ( b GMD_INVENTORY_SPEC_VRS_N1 ) */
a.spec_id,b.spec_vr_id,a.grade_code,revision,decode(a.grade_code,
p_inventory_spec_rec.grade_code,1,2),
b.locator_id,b.subinventory,b.parent_lot_number,b.lot_number,
b.organization_id
FROM gmd_specifications_b a,gmd_inventory_spec_vrs b
WHERE
a.inventory_item_id = p_inventory_spec_rec.inventory_item_id
AND ((p_inventory_spec_rec.revision = a.revision) OR ( p_inventory_spec_rec.revision IS NULL AND a.revision IS NULL)
OR (p_inventory_spec_rec.revision IS NOT NULL AND a.revision IS NULL ))
AND ((a.spec_status between 400 and 499) OR (a.spec_status between 700 and 799) OR (a.spec_status between 900 and 999))
AND a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_inventory_spec_rec.grade_code = a.grade_code ) OR (l_check_for_null_grade = 'Y' AND a.grade_code IS NULL )
OR (l_check_for_default_grade = 'Y' AND a.grade_code = l_item_default_grade_code))
AND a.spec_id = b.spec_id
AND ((b.spec_vr_status between 400 and 499) OR (b.spec_vr_status between 700 and 799) OR (b.spec_vr_status between 900 and 999))
AND b.delete_mark = 0
AND p_inventory_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_inventory_spec_rec.date_effective)
AND ((p_inventory_spec_rec.locator_id = b.locator_id) OR ( p_inventory_spec_rec.locator_id IS NULL AND b.locator_id IS NULL)
OR (p_inventory_spec_rec.locator_id IS NOT NULL AND b.locator_id IS NULL ))
AND ((p_inventory_spec_rec.subinventory = b.subinventory) OR ( p_inventory_spec_rec.subinventory IS NULL AND b.subinventory IS NULL)
OR (p_inventory_spec_rec.subinventory IS NOT NULL AND b.subinventory IS NULL ))
AND ((p_inventory_spec_rec.parent_lot_number = b.parent_lot_number) OR ( p_inventory_spec_rec.parent_lot_number IS NULL AND b.parent_lot_number IS NULL)
OR (p_inventory_spec_rec.parent_lot_number IS NOT NULL AND b.parent_lot_number IS NULL ))
AND ((p_inventory_spec_rec.lot_number = b.lot_number) OR ( p_inventory_spec_rec.lot_number IS NULL AND b.lot_number IS NULL)
OR (p_inventory_spec_rec.lot_number IS NOT NULL AND b.lot_number IS NULL ))
AND ((p_inventory_spec_rec.organization_id = b.organization_id)
OR (p_inventory_spec_rec.organization_id IS NOT NULL AND b.organization_id IS NULL ))
ORDER BY decode(a.grade_code,p_inventory_spec_rec.grade_code,1,2),b.lot_number,b.location,b.subinventory,b.organization_id ;
SELECT a.spec_id,b.spec_vr_id,a.grade_code,revision,decode(a.grade_code,
p_inventory_spec_rec.grade_code,1,2),
b.locator_id,b.subinventory,b.parent_lot_number,b.lot_number,
b.organization_id
FROM gmd_specifications_b a,gmd_inventory_spec_vrs b , gmd_spec_tests_b c
WHERE
a.inventory_item_id = p_inventory_spec_rec.inventory_item_id
AND ((p_inventory_spec_rec.revision = a.revision) OR ( p_inventory_spec_rec.revision IS NULL AND a.revision IS NULL)
OR (p_inventory_spec_rec.revision IS NOT NULL AND a.revision IS NULL ))
AND ((a.spec_status between 400 and 499) OR (a.spec_status between 700 and 799) OR (a.spec_status between 900 and 999))
AND a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_inventory_spec_rec.grade_code = a.grade_code ) OR (l_check_for_null_grade = 'Y' AND a.grade_code IS NULL )
OR (l_check_for_default_grade = 'Y' AND a.grade_code = l_item_default_grade_code))
AND a.spec_id = c.spec_id
AND c.test_id = p_inventory_spec_rec.test_id
AND a.spec_id = b.spec_id
AND ((b.spec_vr_status between 400 and 499) OR (b.spec_vr_status between 700 and 799) OR (b.spec_vr_status between 900 and 999))
AND b.delete_mark = 0
AND p_inventory_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_inventory_spec_rec.date_effective)
AND ((p_inventory_spec_rec.locator_id = b.locator_id) OR ( p_inventory_spec_rec.locator_id IS NULL AND b.locator_id IS NULL)
OR (p_inventory_spec_rec.locator_id IS NOT NULL AND b.locator_id IS NULL ))
AND ((p_inventory_spec_rec.subinventory = b.subinventory) OR ( p_inventory_spec_rec.subinventory IS NULL AND b.subinventory IS NULL)
OR (p_inventory_spec_rec.subinventory IS NOT NULL AND b.subinventory IS NULL ))
AND ((p_inventory_spec_rec.parent_lot_number = b.parent_lot_number) OR ( p_inventory_spec_rec.parent_lot_number IS NULL AND b.parent_lot_number IS NULL)
OR (p_inventory_spec_rec.parent_lot_number IS NOT NULL AND b.parent_lot_number IS NULL ))
AND ((p_inventory_spec_rec.lot_number = b.lot_number) OR ( p_inventory_spec_rec.lot_number IS NULL AND b.lot_number IS NULL)
OR (p_inventory_spec_rec.lot_number IS NOT NULL AND b.lot_number IS NULL ))
AND ((p_inventory_spec_rec.organization_id = b.organization_id) OR ( p_inventory_spec_rec.organization_id IS NULL AND b.organization_id IS NULL)
OR (p_inventory_spec_rec.organization_id IS NOT NULL AND b.organization_id IS NULL ))
ORDER BY decode(a.grade_code,p_inventory_spec_rec.grade_code,1,2),b.lot_number,b.locator_id,b.subinventory,b.organization_id ;
SELECT grade_control_flag, default_grade INTO l_grade_ctl ,l_item_default_grade_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_spec_rec.inventory_item_id
AND organization_id = p_inventory_spec_rec.organization_id;
SELECT /*+ INDEX (b gmd_customer_spec_vrs_n1 ) */
a.spec_id,b.spec_vr_id,a.revision,a.grade_code ,DECODE(a.grade_code,p_customer_spec_rec.grade_code,1,2) grade_order_by,
b.order_line_id,b.order_line,b.order_id,b.ship_to_site_id,b.org_id
FROM gmd_specifications_b a,gmd_customer_spec_vrs b
WHERE
a.inventory_item_id = p_customer_spec_rec.inventory_item_id
AND ((p_customer_spec_rec.revision = a.revision) OR ( p_customer_spec_rec.revision IS NULL AND a.revision IS NULL)
OR (p_customer_spec_rec.revision IS NOT NULL AND a.revision IS NULL ))
AND ((a.spec_status between 700 and 799) OR (a.spec_status between 900 and 999))
AND a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_customer_spec_rec.grade_code = a.grade_code ) OR (l_check_for_null_grade = 'Y' AND a.grade_code IS NULL ))
AND a.spec_id = b.spec_id
AND ((b.spec_vr_status between 700 and 799) OR (b.spec_vr_status between 900 and 999))
AND b.delete_mark = 0
AND b.cust_id = p_customer_spec_rec.cust_id
AND p_customer_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_customer_spec_rec.date_effective + 1)
AND ((p_customer_spec_rec.organization_id = b.organization_id) OR ( p_customer_spec_rec.organization_id IS NULL AND b.organization_id IS NULL)
OR (p_customer_spec_rec.organization_id IS NOT NULL AND b.organization_id IS NULL ))
AND ((p_customer_spec_rec.org_id = b.org_id) OR ( p_customer_spec_rec.org_id IS NULL AND b.org_id IS NULL)
OR (p_customer_spec_rec.org_id IS NOT NULL AND b.org_id IS NULL ))
AND ((p_customer_spec_rec.ship_to_site_id = b.ship_to_site_id) OR ( p_customer_spec_rec.ship_to_site_id IS NULL AND b.ship_to_site_id IS NULL)
OR (p_customer_spec_rec.ship_to_site_id IS NOT NULL AND b.ship_to_site_id IS NULL ))
AND ((p_customer_spec_rec.order_id = b.order_id) OR ( p_customer_spec_rec.order_id IS NULL AND b.order_id IS NULL)
OR (p_customer_spec_rec.order_id IS NOT NULL AND b.order_id IS NULL ))
AND ((l_order_line_number = b.order_line) OR ( l_order_line_number IS NULL AND b.order_line IS NULL)
OR (l_order_line_number IS NOT NULL AND b.order_line IS NULL ))
ORDER BY grade_order_by,b.order_line_id,b.order_line,b.order_id,b.ship_to_site_id,b.org_id;
SELECT line_number + (shipment_number/10) INTO l_order_line_number
FROM OE_ORDER_LINES_ALL
WHERE line_id = p_customer_spec_rec.order_line_id ;
SELECT /*+ INDEX ( b gmd_wip_spec_vrs_n1 ) */
a.spec_id,
b.spec_vr_id,
a.revision,
a.grade_code,
DECODE(a.grade_code,p_wip_spec_rec.grade_code,1,2) grade_order_by,
b.charge,
b.step_no,
b.routing_vers,
b.routing_no,
b.formulaline_id,
b.material_detail_id,
b.formula_vers,
b.formula_no,
b.recipe_version,
b.recipe_no,
b.batch_id,
b.oprn_vers,
b.oprn_no,
b.organization_id
FROM gmd_specifications_b a,
gmd_wip_spec_vrs b
WHERE
a.inventory_item_id = p_wip_spec_rec.inventory_item_id
AND ((p_wip_spec_rec.revision = a.revision)
OR ( p_wip_spec_rec.revision IS NULL AND a.revision IS NULL)
OR (p_wip_spec_rec.revision IS NOT NULL AND a.revision IS NULL ))
AND ((a.spec_status between 400 and 499)
OR (a.spec_status between 700 and 799)
OR (a.spec_status between 900 and 999))
AND a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_wip_spec_rec.grade_code = a.grade_code )
OR (l_check_for_null_grade = 'Y' AND a.grade_code IS NULL )
OR (l_check_for_default_grade = 'Y' AND a.grade_code = l_item_default_grade_code))
AND a.spec_id = b.spec_id
AND ((b.spec_vr_status between 400 and 499)
OR (b.spec_vr_status between 700 and 799)
OR (b.spec_vr_status between 900 and 999))
AND b.delete_mark = 0
AND p_wip_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_wip_spec_rec.date_effective)
AND (p_wip_spec_rec.organization_id = NVL(b.organization_id,p_wip_spec_rec.organization_id))
AND ((p_wip_spec_rec.batch_id = b.batch_id)
OR ( p_wip_spec_rec.batch_id IS NULL AND b.batch_id IS NULL)
OR (p_wip_spec_rec.batch_id IS NOT NULL AND b.batch_id IS NULL ))
AND ((l_formula_no = b.formula_no)
OR ( l_formula_no IS NULL AND b.formula_no IS NULL)
OR (l_formula_no IS NOT NULL AND b.formula_no IS NULL ))
AND ((l_formula_vers = b.formula_vers)
OR ( l_formula_vers IS NULL AND b.formula_vers IS NULL)
OR (l_formula_vers IS NOT NULL AND b.formula_vers IS NULL ))
AND ((l_recipe_no = b.recipe_no)
OR ( l_recipe_no IS NULL AND b.recipe_no IS NULL)
OR (l_recipe_no IS NOT NULL AND b.recipe_no IS NULL ))
AND ((l_recipe_version = b.recipe_version)
OR ( l_recipe_version IS NULL AND b.recipe_version IS NULL)
OR (l_recipe_version IS NOT NULL AND b.recipe_version IS NULL ))
AND ((p_wip_spec_rec.charge = b.charge)
OR ( p_wip_spec_rec.charge IS NULL AND b.charge IS NULL)
OR (p_wip_spec_rec.charge IS NOT NULL AND b.charge IS NULL ))
AND ((l_step_no = b.step_no)
OR (l_step_no IS NULL AND b.step_no IS NULL)
OR (nvl(p_wip_spec_rec.find_spec_with_step,'N') = 'N' and l_step_no IS NOT NULL AND b.step_no IS NULL ))
AND ((l_routing_no = b.routing_no)
OR ( l_routing_no IS NULL AND b.routing_no IS NULL)
OR (l_routing_no IS NOT NULL AND b.routing_no IS NULL ))
AND ((l_routing_vers = b.routing_vers)
OR ( l_routing_vers IS NULL AND b.routing_vers IS NULL)
OR (l_routing_vers IS NOT NULL AND b.routing_vers IS NULL ))
AND ((p_wip_spec_rec.formulaline_id = b.formulaline_id)
OR ( p_wip_spec_rec.formulaline_id IS NULL AND b.formulaline_id IS NULL)
OR (p_wip_spec_rec.formulaline_id IS NOT NULL AND b.formulaline_id IS NULL )
OR (p_wip_spec_rec.batch_id IS NOT NULL ))
AND ((p_wip_spec_rec.material_detail_id = b.material_detail_id)
OR ( p_wip_spec_rec.material_detail_id IS NULL AND b.material_detail_id IS NULL)
OR (p_wip_spec_rec.material_detail_id IS NOT NULL AND b.material_detail_id IS NULL ))
AND ((l_oprn_no = b.oprn_no)
OR ( l_oprn_no IS NULL AND b.oprn_no IS NULL)
OR (l_oprn_no IS NOT NULL AND b.oprn_no IS NULL ))
AND ((l_oprn_vers = b.oprn_vers)
OR ( l_oprn_vers IS NULL AND b.oprn_vers IS NULL)
OR (l_oprn_vers IS NOT NULL AND b.oprn_vers IS NULL ))
ORDER BY grade_order_by, b.charge, b.step_no, b.routing_id, b.routing_no, b.material_detail_id,
b.formulaline_id, b.formula_id, b.formula_no, b.recipe_id, b.recipe_no, b.batch_id, b.oprn_id,
b.oprn_no, b.organization_id ;
SELECT recipe_no,recipe_version INTO l_recipe_no,l_recipe_version
FROM GMD_RECIPES_B
WHERE recipe_id = p_wip_spec_rec.recipe_id ;
SELECT formula_no,formula_vers INTO l_formula_no,l_formula_vers
FROM fm_form_mst_b
WHERE formula_id = p_wip_spec_rec.formula_id;
SELECT routing_no,routing_vers INTO l_routing_no,l_routing_vers
FROM gmd_routings_b
WHERE routing_id = p_wip_spec_rec.routing_id ;
SELECT oprn_no,oprn_vers INTO l_oprn_no,l_oprn_vers
FROM gmd_operations_b
WHERE oprn_id = p_wip_spec_rec.oprn_id;
SELECT BATCHSTEP_NO INTO l_step_no
FROM gme_batch_steps
WHERE batchstep_id = p_wip_spec_rec.step_id
AND batch_id = p_wip_spec_rec.batch_id;
SELECT ROUTINGSTEP_NO INTO l_step_no
FROM fm_rout_dtl
WHERE routingstep_id = p_wip_spec_rec.step_id
AND routing_id = p_wip_spec_rec.routing_id;
SELECT ROUTINGSTEP_NO INTO l_step_no
FROM fm_rout_dtl
WHERE routingstep_id = p_wip_spec_rec.step_id
AND routing_id = p_wip_spec_rec.routing_id ;
SELECT BATCHSTEP_NO INTO l_step_no
FROM gme_batch_steps
WHERE batchstep_id = p_wip_spec_rec.step_id
AND batch_id = p_wip_spec_rec.batch_id;
SELECT grade_control_flag, default_grade INTO l_grade_ctl ,l_item_default_grade_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_wip_spec_rec.inventory_item_id
AND organization_id = p_wip_spec_rec.organization_id;
SELECT /*+ INDEX ( b gmd_supplier_spec_vrs_n1) */
a.spec_id,b.spec_vr_id,a.revision,a.grade_code,
decode(a.grade_code,p_supplier_spec_rec.grade_code,1,2),b.po_line_id,
b.po_header_id,b.supplier_site_id,b.supplier_id
FROM gmd_specifications_b a,gmd_supplier_spec_vrs b
WHERE
a.inventory_item_id = p_supplier_spec_rec.inventory_item_id
AND ((p_supplier_spec_rec.revision = a.revision) OR ( p_supplier_spec_rec.revision IS NULL AND a.revision IS NULL)
OR (p_supplier_spec_rec.revision IS NOT NULL AND a.revision IS NULL ))
AND ((a.spec_status between 700 and 799) OR (a.spec_status between 900 and 999))
AND a.delete_mark = 0
AND ((l_check_for_given_grade = 'Y' and p_supplier_spec_rec.grade_code = a.grade_code ) OR
(l_check_for_null_grade = 'Y' AND a.grade_code IS NULL ))
AND a.spec_id = b.spec_id
AND ((b.spec_vr_status between 700 and 799) OR (b.spec_vr_status between 900 and 999))
AND b.delete_mark = 0
AND b.supplier_id = p_supplier_spec_rec.supplier_id
AND p_supplier_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_supplier_spec_rec.date_effective)
AND ((p_supplier_spec_rec.organization_id = b.organization_id) OR ( p_supplier_spec_rec.organization_id IS NULL AND b.organization_id IS NULL)
OR (p_supplier_spec_rec.organization_id IS NOT NULL AND b.organization_id IS NULL ))
AND ((p_supplier_spec_rec.org_id = b.org_id) OR ( p_supplier_spec_rec.org_id IS NULL AND b.org_id IS NULL)
OR (p_supplier_spec_rec.org_id IS NOT NULL AND b.org_id IS NULL ))
AND ((p_supplier_spec_rec.po_line_id = b.po_line_id) OR ( p_supplier_spec_rec.po_line_id IS NULL AND b.po_line_id IS NULL)
OR (p_supplier_spec_rec.po_line_id IS NOT NULL AND b.po_line_id IS NULL ))
AND ((p_supplier_spec_rec.po_header_id = b.po_header_id) OR ( p_supplier_spec_rec.po_header_id IS NULL AND b.po_header_id IS NULL)
OR (p_supplier_spec_rec.po_header_id IS NOT NULL AND b.po_header_id IS NULL ))
AND ((p_supplier_spec_rec.supplier_site_id = b.supplier_site_id) OR ( p_supplier_spec_rec.supplier_site_id IS NULL AND b.supplier_site_id IS NULL)
OR (p_supplier_spec_rec.supplier_site_id IS NOT NULL AND b.supplier_site_id IS NULL ))
ORDER BY DECODE(a.grade_code,p_supplier_spec_rec.grade_code,1,2),b.po_line_id,b.po_header_id,b.supplier_site_id,b.supplier_id;
SELECT gst.test_id
FROM GMD_SPEC_TESTS_B gst
WHERE gst.spec_id = p_spec_id
AND gst.optional_ind is NULL ;
SELECT gs.sample_id,gr.test_id,gr.result_value_num,gr.result_value_char
FROM GMD_SAMPLES gs , GMD_RESULTS gr
WHERE gs.sample_id = gr.sample_id
AND gs.delete_mark = 0
AND gs.sample_id IN ( SELECT ssd.sample_id FROM gmd_sample_spec_disp ssd
WHERE ssd.sample_id = gs.sample_id
AND ssd.disposition IN ('3C','4A','5AV','6RJ') )
AND gs.inventory_item_id = l_inventory_item_id
AND gs.organization_id = l_organization_id
AND (gs.lot_number = l_lot_number OR gs.lot_number IS NULL)
AND (gs.subinventory = l_subinventory OR gs.subinventory IS NULL)
AND (gs.locator_id = l_locator_id OR gs.locator_id IS NULL )
AND gr.delete_mark = 0
AND (gr.result_value_num IS NOT NULL or gr.result_value_char IS NOT NULL)
ORDER BY gs.lot_number,gs.date_drawn desc,gs.location,gs.subinventory,gr.result_date desc ;
SELECT lot_no,sublot_no INTO l_lot_no,l_sublot_no
FROM IC_LOTS_MST
WHERE lot_id = p_lots(l_lot_counter).lot_id
AND item_id = p_lots(l_lot_counter).item_id;
result_test_list.DELETE;
result_test_list.DELETE;
SELECT a.spec_id, b.spec_vr_id,
b.locator_organization_id,
b.subinventory,
b.locator_id
FROM gmd_specifications_b a,
gmd_monitoring_spec_vrs b,
gmd_qc_status qs1,
gmd_qc_status qs2
WHERE (a.spec_status = qs1.status_code AND
qs1.entity_type = 'S' AND
qs1.status_type in (400, 700, 900)
)
AND a.delete_mark = 0
AND a.spec_id = b.spec_id
AND (b.spec_vr_status = qs2.status_code AND
qs2.entity_type = 'S' AND
qs2.status_type in (400, 700, 900)
)
AND b.delete_mark = 0
AND b.rule_type = 'L'
AND p_location_spec_rec.date_effective between b.start_date and nvl(b.end_date, p_location_spec_rec.date_effective)
AND ((p_location_spec_rec.locator_organization_id = b.locator_organization_id) OR
(p_location_spec_rec.locator_organization_id IS NULL AND b.locator_organization_id IS NULL) OR
(p_location_spec_rec.locator_organization_id IS NOT NULL AND b.locator_organization_id IS NULL)
)
AND ((p_location_spec_rec.subinventory = b.subinventory) OR
(p_location_spec_rec.subinventory IS NULL AND b.subinventory IS NULL) OR
(p_location_spec_rec.subinventory IS NOT NULL AND b.subinventory IS NULL)
)
AND ((p_location_spec_rec.locator_id = b.locator_id) OR
(p_location_spec_rec.locator_id IS NULL AND b.locator_id IS NULL) OR
(p_location_spec_rec.locator_id IS NOT NULL AND b.locator_id IS NULL )
)
ORDER BY b.locator_id,b.subinventory,b.locator_organization_id ;
SELECT a.spec_id, b.spec_vr_id,
b.resource_organization_id,
b.resources,
b.resource_instance_id
FROM gmd_specifications_b a,
gmd_monitoring_spec_vrs b,
gmd_qc_status qs1,
gmd_qc_status qs2
WHERE (a.spec_status = qs1.status_code AND
qs1.entity_type = 'S' AND
qs1.status_type in (400, 700, 900)
)
AND a.delete_mark = 0
AND a.spec_id = b.spec_id
AND (b.spec_vr_status = qs2.status_code AND
qs2.entity_type = 'S' AND
qs2.status_type in (400, 700, 900)
)
AND b.delete_mark = 0
AND b.rule_type = 'R'
AND p_resource_spec_rec.date_effective between b.start_date and nvl(b.end_date, p_resource_spec_rec.date_effective)
AND ((p_resource_spec_rec.resource_organization_id = b.resource_organization_id) OR
(p_resource_spec_rec.resource_organization_id IS NULL AND b.resource_organization_id IS NULL) OR
(p_resource_spec_rec.resource_organization_id IS NOT NULL AND b.resource_organization_id IS NULL)
)
AND ((p_resource_spec_rec.resources = b.resources) OR
(p_resource_spec_rec.resources IS NULL AND b.resources IS NULL) OR
(p_resource_spec_rec.resources IS NOT NULL AND b.resources IS NULL)
)
AND ((p_resource_spec_rec.resource_instance_id = b.resource_instance_id) OR
(p_resource_spec_rec.resource_instance_id IS NULL AND b.resource_instance_id IS NULL) OR
(p_resource_spec_rec.resource_instance_id IS NOT NULL AND b.resource_instance_id IS NULL )
)
ORDER BY b.resource_instance_id, b.resources, b.resource_organization_id ;