[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM mtl_parameters m
WHERE m.process_enabled_flag = 'Y';
SELECT 1
FROM gmd_qc_status
WHERE status_code = p_status_code
AND delete_mark = 0;
IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
-- Invalid Operation
GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
IF (p_operation IN ('INSERT', 'UPDATE')
AND mon_vr_exist(l_mon_vr, l_spec))
THEN
-- Disaster, Trying to insert duplicate
-- Put the message in function mon_vr_exist.
-- GMD_API_PUB.Log_Message('GMD_MON_VR_EXIST');
SELECT 1
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_mon_vr.subinventory
AND organization_id = l_mon_vr.locator_organization_id;
SELECT 1
FROM mtl_item_locations
WHERE organization_id = l_mon_vr.locator_organization_id
AND inventory_location_id = l_mon_vr.locator_id;
select 1
from cr_rsrc_mst
where resources = l_mon_vr.resources
and delete_mark = 0;
SELECT ri.INSTANCE_NUMBER
FROM GMP_RESOURCE_INSTANCES ri, CR_RSRC_DTL rd
WHERE rd.resource_id = ri.resource_id
AND rd.organization_id = NVL(l_mon_vr.resource_organization_id, rd.organization_id)
AND rd.resources = NVL(l_mon_vr.resources, rd.resources)
AND ri.INACTIVE_IND = 0
ORDER BY 1 ;
check_who( p_user_id => l_mon_vr.last_updated_by);
OR l_mon_vr.last_update_date IS NULL)
THEN
GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
'WHAT', 'the dates must not be NULL');
SELECT vr.spec_vr_id, s.spec_name, s.spec_vers
FROM gmd_specifications_b s, gmd_monitoring_spec_vrs vr
WHERE s.spec_id = vr.spec_id
AND ((s.grade_code is NULL AND p_spec.grade_code is NULL) OR
(s.grade_code = p_spec.grade_code)
)
AND ((vr.locator_organization_id is NULL AND p_mon_vr.locator_organization_id is NULL) OR
(vr.locator_organization_id = p_mon_vr.locator_organization_id)
)
AND ((vr.subinventory is NULL AND p_mon_vr.subinventory is NULL) OR
(vr.subinventory = p_mon_vr.subinventory)
)
AND ((vr.locator_id is NULL AND p_mon_vr.locator_id is NULL) OR
(vr.locator_id = p_mon_vr.locator_id)
)
AND ((vr.resource_organization_id is NULL AND p_mon_vr.resource_organization_id is NULL) OR
(vr.resource_organization_id = p_mon_vr.resource_organization_id)
)
AND ((vr.resources is NULL AND p_mon_vr.resources is NULL) OR
(vr.resources = p_mon_vr.resources)
)
AND ((vr.resource_instance_id is NULL AND p_mon_vr.resource_instance_id is NULL) OR
(vr.resource_instance_id = p_mon_vr.resource_instance_id)
)
AND ((vr.end_date is NULL AND (p_mon_vr.end_date IS NULL OR
p_mon_vr.end_date >= vr.start_date)) OR
(p_mon_vr.end_date IS NULL AND
p_mon_vr.start_date <= nvl(vr.end_date, p_mon_vr.start_date)) OR
(p_mon_vr.start_date <= vr.end_date AND p_mon_vr.end_date >= vr.start_date)
)
AND ( floor(vr.spec_vr_status / 100) = floor(p_mon_vr.spec_vr_status/100) AND
/* Bug 3090290; allow duplicate spec vr with "OBSOLUTE" status */
AND vr.spec_vr_status NOT IN (SELECT status_code FROM gmd_qc_status
WHERE status_type = 800)
AND vr.delete_mark = 0
AND s.delete_mark = 0
AND vr.spec_vr_id <> NVL(p_mon_vr.spec_vr_id, -1)
;
IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
-- Invalid Operation
GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
IF (p_operation IN ('INSERT', 'UPDATE')
AND inv_vr_exist(l_inv_vr, l_spec))
THEN
-- Disaster, Trying to insert duplicate
-- bug 2630007, odaboval put the message in function inv_vr_exist.
-- GMD_API_PUB.Log_Message('GMD_INV_VR_EXIST');
SELECT inventory_item_id INTO l_inventory_item_id FROM
gmd_specifications WHERE spec_id = p_inv_vr.spec_id;
SELECT owner_organization_id INTO l_organization_id FROM
gmd_specifications WHERE spec_id = p_inv_vr.spec_id;
SELECT * INTO l_item_mst
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
SELECT 1
FROM mtl_lot_numbers
WHERE organization_id = l_inv_vr.organization_id
AND inventory_item_id = l_spec.inventory_item_id
AND lot_number = l_inv_vr.lot_number;
SELECT 1
FROM mtl_lot_numbers
WHERE organization_id = l_inv_vr.organization_id
AND inventory_item_id = l_spec.inventory_item_id
AND parent_lot_number = l_inv_vr.parent_lot_number;
SELECT 1
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_inv_vr.subinventory
AND organization_id = l_inv_vr.organization_id;
SELECT 1
FROM mtl_item_locations
WHERE organization_id = l_inv_vr.organization_id
AND inventory_location_id = l_inv_vr.locator_id;
check_who( p_user_id => l_inv_vr.last_updated_by);
OR l_inv_vr.last_update_date IS NULL)
THEN
GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
'WHAT', 'the dates must not be NULL');
SELECT vr.spec_vr_id, s.spec_name, s.spec_vers
FROM gmd_specifications_b s, gmd_inventory_spec_vrs vr
WHERE s.spec_id = vr.spec_id
AND s.owner_organization_id = p_spec.owner_organization_id
AND s.inventory_item_id = p_spec.inventory_item_id
AND ( (s.revision is null and p_spec.revision is NULL ) OR -- handle item revision 5117733
(s.revision = p_spec.revision )
)
AND ((s.grade_code is NULL AND p_spec.grade_code is NULL) OR
(s.grade_code = p_spec.grade_code)
)
AND ((vr.organization_id is NULL AND p_inv_vr.organization_id is NULL) OR
(vr.organization_id = p_inv_vr.organization_id)
)
AND ((vr.lot_number is NULL AND p_inv_vr.lot_number is NULL) OR
(vr.lot_number = p_inv_vr.lot_number)
)
AND ((vr.parent_lot_number is NULL AND p_inv_vr.parent_lot_number is NULL) OR
(vr.parent_lot_number = p_inv_vr.parent_lot_number)
)
AND ((vr.subinventory is NULL AND p_inv_vr.subinventory is NULL) OR
(vr.subinventory = p_inv_vr.subinventory)
)
AND ((vr.locator_id is NULL AND p_inv_vr.locator_id is NULL) OR
(vr.locator_id = p_inv_vr.locator_id)
)
AND ((vr.end_date is NULL AND (p_inv_vr.end_date IS NULL OR
p_inv_vr.end_date >= vr.start_date)) OR
(p_inv_vr.end_date IS NULL AND
p_inv_vr.start_date <= nvl(vr.end_date, p_inv_vr.start_date)) OR
(p_inv_vr.start_date <= vr.end_date AND p_inv_vr.end_date >= vr.start_date)
)
AND ( floor(vr.spec_vr_status / 100) = floor(p_inv_vr.spec_vr_status/100) AND
/* Bug 3090290; allow duplicate spec vr with "OBSOLUTE" status */
AND vr.spec_vr_status NOT IN (SELECT status_code FROM gmd_qc_status
WHERE status_type = 800)
AND vr.delete_mark = 0
AND s.delete_mark = 0
AND vr.spec_vr_id <> NVL(p_inv_vr.spec_vr_id, -1)
;
IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
-- Invalid Operation
GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
IF (p_operation IN ('INSERT', 'UPDATE')
AND wip_vr_exist(l_wip_vr, l_spec))
THEN
-- Disaster, Trying to insert duplicate
-- bug 2630007, odaboval put the message in function wip_vr_exist.
-- GMD_API_PUB.Log_Message('GMD_WIP_VR_EXIST');
SELECT inventory_item_id INTO l_inventory_item_id FROM
gmd_specifications WHERE spec_id = p_wip_vr.spec_id;
SELECT owner_organization_id INTO l_organization_id FROM
gmd_specifications WHERE spec_id = p_wip_vr.spec_id;
SELECT * INTO l_item_mst
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
SELECT 1
FROM
-- org_access_view o,
mtl_parameters m,
gmd_parameters_hdr h
WHERE h.organization_id = m.organization_id
AND m.process_enabled_flag = 'Y'
AND m.organization_id = p_organization_id;
SELECT gr.recipe_id, gr.recipe_no, gr.recipe_version
, ffm.formula_id, ffm.formula_no, ffm.formula_vers
, rout.routing_id, rout.routing_no, rout.routing_vers
FROM gme_batch_header bh
, gme_material_details md
, gmd_recipes_b gr -- just need base table here not view
, gmd_recipe_validity_rules rvr
, gmd_status gs
, fm_matl_dtl fmd
, fm_form_mst_b ffm -- just need base table here not view
, gmd_routings_b rout -- just need base table here not view
WHERE rout.routing_id(+) = bh.routing_id
AND rvr.recipe_validity_rule_id = bh.recipe_validity_rule_id
AND rvr.recipe_id = gr.recipe_id
AND ffm.formula_id = bh.formula_id
AND ffm.formula_id = fmd.formula_id
AND fmd.formula_id = bh.formula_id
AND rout.delete_mark(+) = 0 /*changed in bug 6278463 */
AND gs.delete_mark = 0
AND rvr.delete_mark = 0
AND gr.delete_mark = 0
AND bh.delete_mark = 0
AND ffm.delete_mark = 0
AND fmd.formula_id = gr.formula_id
AND ( fmd.inventory_item_id = p_spec.inventory_item_id
OR MD.FORMULALINE_ID IS NULL) -- 13451274 added for when item is not on formula
AND gr.recipe_status = gs.status_code
AND gs.status_code <> '1000'
AND gr.formula_id = bh.formula_id
AND bh.batch_id = md.batch_id
AND bh.batch_type = 0 -- Only BATCH, no FPO
--AND bh.batch_status IN (1, 2) -- PENDING or WIP BATCH only.
AND ( ( bh.batch_status IN (1,2, 3) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'N') )
or ( bh.batch_status IN (1,2, 3,4 ) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'Y') ) ) -- Bug # 4619570
AND md.inventory_item_id = p_spec.inventory_item_id
AND bh.organization_id = l_wip_vr.organization_id
AND bh.batch_id = l_wip_vr.batch_id
AND NVL( l_wip_vr.recipe_id, gr.recipe_id) = gr.recipe_id
AND NVL( l_wip_vr.formula_id, bh.formula_id) = bh.formula_id
AND ( NVL( l_wip_vr.routing_id, 0) = nvl(bh.routing_id,0) -- 13027522 - better fix than one for 6278463 as when there was no routing id passed then the batch with no routing was not hit
OR NVL( l_wip_vr.routing_id, 0) = nvl(l_wip_vr.routing_id,0) ) ; -- extra line -- 13105549 to handle behaviour for recipe with route.
SELECT r.recipe_no, r.recipe_version
, ffm.formula_id, ffm.formula_no, ffm.formula_vers
, rout.routing_id, rout.routing_no, rout.routing_vers
FROM gmd_recipes r
, gmd_status s
, gmd_recipe_validity_rules rvr
, gmd_routings rout
, fm_form_mst ffm
, fm_matl_dtl fmd
WHERE rout.routing_id(+) = r.routing_id
AND ffm.formula_id = r.formula_id
AND rvr.recipe_id = r.recipe_id
AND (NVL( l_wip_vr.organization_id, rvr.organization_id) = rvr.organization_id OR rvr.organization_id IS NULL)
AND r.recipe_status = s.status_code
AND r.formula_id = fmd.formula_id
AND fmd.inventory_item_id = p_spec.inventory_item_id
--AND NVL(rout.delete_mark, 0) = 0/*commented in bug# 6278463 */
AND rout.delete_mark(+) = 0 /*changed in bug# 6278463 */
AND rvr.delete_mark = 0
AND s.delete_mark = 0
AND r.delete_mark = 0
AND ffm.delete_mark = 0
AND s.status_type <> '1000'
AND r.recipe_id = l_wip_vr.recipe_id
AND NVL( l_wip_vr.formula_id, r.formula_id) = r.formula_id
--AND NVL( l_wip_vr.routing_id, rout.routing_id) = rout.routing_id;/*commented in bug# 6278463 */
SELECT r.recipe_id, r.recipe_version
FROM gmd_recipes r
, gmd_status s
, gmd_recipe_validity_rules rvr
, gmd_routings rout
, fm_form_mst ffm
, fm_matl_dtl fmd
WHERE rout.routing_id(+) = r.routing_id
AND ffm.formula_id = r.formula_id
AND rvr.recipe_id = r.recipe_id
AND (NVL( l_wip_vr.organization_id, rvr.organization_id) = rvr.organization_id OR rvr.organization_id IS NULL)
AND r.recipe_status = s.status_code
AND r.formula_id = fmd.formula_id
AND fmd.inventory_item_id = p_spec.inventory_item_id
--AND NVL(rout.delete_mark, 0) = 0/*changed in bug# 6278463 */
AND rout.delete_mark(+) = 0/*changed in bug# 6278463 */
AND rvr.delete_mark = 0
AND s.delete_mark = 0
AND r.delete_mark = 0
AND ffm.delete_mark = 0
AND s.status_type <> '1000'
AND r.recipe_no = l_wip_vr.recipe_no
AND NVL( l_wip_vr.recipe_version, r.recipe_version) = r.recipe_version
AND NVL( l_wip_vr.formula_id, r.formula_id) = r.formula_id
--AND NVL( l_wip_vr.routing_id, rout.routing_id) = rout.routing_id;/*changed in bug# 6278463 */
SELECT ffm.formula_no, ffm.formula_vers
FROM gmd_recipes grec
, fm_form_mst ffm
, fm_matl_dtl fmd
, gem_lookups gl
, gmd_status s
WHERE s.status_code = ffm.formula_status
AND gl.lookup_code = fmd.line_type
AND gl.lookup_type = 'LINE_TYPE'
AND grec.formula_id(+) = ffm.formula_id
AND fmd.formula_id = ffm.formula_id
AND fmd.inventory_item_id = p_spec.inventory_item_id
AND s.delete_mark = 0
AND grec.delete_mark = 0
AND ffm.delete_mark = 0
AND NVL( l_wip_vr.recipe_id, grec.recipe_id) = grec.recipe_id
AND NVL( l_wip_vr.formulaline_id, fmd.formulaline_id) = fmd.formulaline_id
AND ffm.formula_id = l_wip_vr.formula_id;
SELECT ffm.formula_id, ffm.formula_vers
FROM gmd_recipes grec
, fm_form_mst ffm
, fm_matl_dtl fmd
, gem_lookups gl
, gmd_status s
WHERE s.status_code = ffm.formula_status
AND gl.lookup_code = fmd.line_type
AND gl.lookup_type = 'LINE_TYPE'
AND grec.formula_id(+) = ffm.formula_id
AND fmd.formula_id = ffm.formula_id
AND fmd.inventory_item_id = p_spec.inventory_item_id
AND s.delete_mark = 0
AND grec.delete_mark = 0
AND ffm.delete_mark = 0
AND NVL( l_wip_vr.recipe_id, grec.recipe_id) = grec.recipe_id
AND NVL( l_wip_vr.formulaline_id, fmd.formulaline_id) = fmd.formulaline_id
AND NVL( l_wip_vr.formula_vers, ffm.formula_vers) = ffm.formula_vers
AND ffm.formula_no = l_wip_vr.formula_no;
SELECT 1
FROM fm_matl_dtl fmd
WHERE fmd.inventory_item_id = p_spec.inventory_item_id
AND fmd.formula_id = l_wip_vr.formula_id
AND fmd.formulaline_id = l_wip_vr.formulaline_id;
SELECT 1
FROM gme_material_details
WHERE inventory_item_id = p_spec.inventory_item_id
AND batch_id = l_wip_vr.batch_id
AND organization_id = l_wip_vr.organization_id
AND material_detail_id = l_wip_vr.material_detail_id;
SELECT r.routing_no, r.routing_vers
FROM gmd_recipes grec
, gmd_status s
, gmd_routings r
WHERE grec.routing_id(+) = r.routing_id
AND s.status_code = r.routing_status
AND NVL( l_wip_vr.recipe_id, 0) = grec.recipe_id(+) /*changed in bug# 6278463 */
AND NVL( l_wip_vr.formula_id, 0) = grec.formula_id(+) /*changed in bug# 6278463 */
AND s.delete_mark = 0
AND grec.delete_mark(+) = 0 /*changed in bug# 6278463 */
AND r.delete_mark = 0
AND r.routing_id = l_wip_vr.routing_id;
SELECT r.routing_id, r.routing_vers
FROM gmd_recipes grec
, gmd_status s
, gmd_routings r
WHERE grec.routing_id(+) = r.routing_id
AND s.status_code = r.routing_status
AND NVL( l_wip_vr.recipe_id, 0) = grec.recipe_id(+) /*changed in bug# 6278463 */
AND NVL( l_wip_vr.formula_id, 0) = grec.formula_id(+) /*changed in bug# 6278463 */
AND s.delete_mark = 0
AND grec.delete_mark(+) = 0 /*changed in bug# 6278463 */
AND r.delete_mark = 0
AND NVL( l_wip_vr.routing_vers, r.routing_vers) = r.routing_vers
AND r.routing_no = l_wip_vr.routing_no;
SELECT bs.batchstep_no
FROM gme_batch_steps bs
, gmd_operations o
WHERE bs.oprn_id = o.oprn_id
AND o.delete_mark = 0
AND bs.delete_mark = 0
AND NVL( l_wip_vr.oprn_id, o.oprn_id) = o.oprn_id
AND NVL( l_wip_vr.oprn_no, o.oprn_no) = o.oprn_no
AND NVL( l_wip_vr.step_no, bs.batchstep_no) = bs.batchstep_no
AND bs.batchstep_id = l_wip_vr.step_id
AND bs.batch_id = l_wip_vr.batch_id;
SELECT rd.routingstep_no
FROM fm_rout_dtl rd
, gmd_operations o
WHERE rd.oprn_id = o.oprn_id
AND o.delete_mark = 0
AND NVL( l_wip_vr.oprn_id, o.oprn_id) = o.oprn_id
AND NVL( l_wip_vr.oprn_no, o.oprn_no) = o.oprn_no
AND NVL( l_wip_vr.step_no, rd.routingstep_no) = rd.routingstep_no
AND rd.routingstep_id = l_wip_vr.step_id
AND rd.routing_id = l_wip_vr.routing_id;
SELECT oprn_no, oprn_vers
FROM gmd_operations o
WHERE o.delete_mark = 0
AND o.oprn_id = l_wip_vr.oprn_id;
SELECT r.recipe_no, r.recipe_version
, ffm.formula_id, ffm.formula_no, ffm.formula_vers
FROM gmd_recipes r
, gmd_status s
, gmd_recipe_validity_rules rvr
, fm_form_mst ffm
, fm_matl_dtl fmd
WHERE ffm.formula_id = r.formula_id
AND rvr.recipe_id = r.recipe_id
AND (NVL( l_wip_vr.organization_id, rvr.organization_id) = rvr.organization_id OR rvr.organization_id IS NULL)
AND r.recipe_status = s.status_code
AND r.formula_id = fmd.formula_id
AND fmd.inventory_item_id = p_spec.inventory_item_id
AND rvr.delete_mark = 0
AND s.delete_mark = 0
AND r.delete_mark = 0
AND ffm.delete_mark = 0
AND s.status_type <> '1000'
AND r.recipe_id = l_wip_vr.recipe_id
AND NVL( l_wip_vr.formula_id, r.formula_id) = r.formula_id;
SELECT r.recipe_id, r.recipe_version
FROM gmd_recipes r
, gmd_status s
, gmd_recipe_validity_rules rvr
, fm_form_mst ffm
, fm_matl_dtl fmd
WHERE ffm.formula_id = r.formula_id
AND rvr.recipe_id = r.recipe_id
AND (NVL( l_wip_vr.organization_id, rvr.organization_id) = rvr.organization_id OR rvr.organization_id IS NULL)
AND r.recipe_status = s.status_code
AND r.formula_id = fmd.formula_id
AND fmd.inventory_item_id = p_spec.inventory_item_id
AND rvr.delete_mark = 0
AND s.delete_mark = 0
AND r.delete_mark = 0
AND ffm.delete_mark = 0
AND s.status_type <> '1000'
AND r.recipe_no = l_wip_vr.recipe_no
AND NVL( l_wip_vr.recipe_version, r.recipe_version) = r.recipe_version
AND NVL( l_wip_vr.formula_id, r.formula_id) = r.formula_id;
check_who( p_user_id => l_wip_vr.last_updated_by);
OR l_wip_vr.last_update_date IS NULL)
THEN
GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
'WHAT', 'the dates must not be NULL');
SELECT vr.spec_vr_id, s.spec_name, s.spec_vers
FROM gmd_specifications_b s,
gmd_wip_spec_vrs vr
WHERE s.spec_id = vr.spec_id
AND s.owner_organization_id = p_spec.owner_organization_id
AND s.inventory_item_id = p_spec.inventory_item_id
AND ( (s.revision is null and p_spec.revision is NULL ) OR -- handle item revision 5117733
(s.revision = p_spec.revision )
)
AND ((s.grade_code is NULL AND p_spec.grade_code is NULL) OR
(s.grade_code = p_spec.grade_code)
)
AND ((vr.organization_id is NULL AND p_wip_vr.organization_id is NULL) OR
(vr.organization_id = p_wip_vr.organization_id)
)
AND ((vr.batch_id is NULL AND p_wip_vr.batch_id is NULL) OR
(vr.batch_id = p_wip_vr.batch_id)
)
AND ((vr.recipe_id is NULL AND p_wip_vr.recipe_id is NULL) OR
(vr.recipe_id = p_wip_vr.recipe_id)
)
AND ((vr.recipe_no is NULL AND p_wip_vr.recipe_no is NULL) OR
(vr.recipe_no = p_wip_vr.recipe_no)
)
AND ((vr.formula_id is NULL AND p_wip_vr.formula_id is NULL) OR
(vr.formula_id = p_wip_vr.formula_id)
)
AND ((vr.formula_no is NULL AND p_wip_vr.formula_no is NULL) OR
(vr.formula_no = p_wip_vr.formula_no)
)
AND ((vr.formulaline_id is NULL AND p_wip_vr.formulaline_id is NULL) OR
(vr.formulaline_id = p_wip_vr.formulaline_id) OR
(vr.batch_id IS NOT NULL) -- added for new Material detail field
)
AND ((vr.material_detail_id is NULL AND p_wip_vr.material_detail_id is NULL) OR
(vr.material_detail_id = p_wip_vr.material_detail_id)
)
AND ((vr.routing_id is NULL AND p_wip_vr.routing_id is NULL) OR
(vr.routing_id = p_wip_vr.routing_id)
)
AND ((vr.routing_no is NULL AND p_wip_vr.routing_no is NULL) OR
(vr.routing_no = p_wip_vr.routing_no)
)
AND ((vr.step_id is NULL AND p_wip_vr.step_id is NULL) OR
(vr.step_id = p_wip_vr.step_id)
)
AND ((vr.oprn_id is NULL AND p_wip_vr.oprn_id is NULL) OR
(vr.oprn_id = p_wip_vr.oprn_id)
)
AND ((vr.oprn_no is NULL AND p_wip_vr.oprn_no is NULL) OR
(vr.oprn_no = p_wip_vr.oprn_no)
)
AND ((vr.charge is NULL AND p_wip_vr.charge is NULL) OR
(vr.charge = p_wip_vr.charge)
)
AND ((vr.end_date is NULL AND (p_wip_vr.end_date IS NULL OR
p_wip_vr.end_date >= vr.start_date)) OR
(p_wip_vr.end_date IS NULL AND
p_wip_vr.start_date <= nvl(vr.end_date, p_wip_vr.start_date)) OR
(p_wip_vr.start_date <= vr.end_date AND p_wip_vr.end_date >= vr.start_date)
)
AND (floor(vr.spec_vr_status/100) = floor(p_wip_vr.spec_vr_status/100) AND
/* Bug 3090290; allow duplicate spec vr with "OBSOLUTE" status */
AND vr.spec_vr_status NOT IN (SELECT status_code FROM gmd_qc_status
WHERE status_type = 800)
AND vr.delete_mark = 0
AND s.delete_mark = 0
AND vr.spec_vr_id <> NVL(p_wip_vr.spec_vr_id, -1)
;
IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
-- Invalid Operation
GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
IF (p_operation IN ('INSERT', 'UPDATE')
AND cust_vr_exist(p_cust_vr, l_spec))
THEN
-- Disaster, Trying to insert duplicate
-- bug 2630007, odaboval put the message in function cust_vr_exist.
-- GMD_API_PUB.Log_Message('GMD_CUST_VR_EXIST');
SELECT inventory_item_id INTO l_inventory_item_id FROM
gmd_specifications WHERE spec_id = p_cust_vr.spec_id;
SELECT owner_organization_id INTO l_organization_id FROM
gmd_specifications WHERE spec_id = p_cust_vr.spec_id;
SELECT * INTO l_item_mst
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
SELECT 1
FROM hr_operating_units ou
, hz_cust_acct_sites_all casa
, hz_cust_site_uses_all csua
, hz_parties hzp
, hz_cust_accounts_all hzca
WHERE ou.organization_id = csua.org_id
AND casa.cust_acct_site_id = csua.cust_acct_site_id
AND casa.cust_account_id = hzca.cust_account_id
AND casa.org_id = csua.org_id
AND hzp.party_id = hzca.party_id
AND NVL( p_cust_vr.org_id, csua.org_id) = csua.org_id
AND hzca.cust_account_id = p_cust_vr.cust_id;
/*SELECT 1
FROM hr_operating_units
WHERE organization_id = p_cust_vr.org_id; */
SELECT 1
FROM HR_ALL_ORGANIZATION_UNITS O,
HR_ORGANIZATION_INFORMATION O2
--HR_ORGANIZATION_INFORMATION O3
WHERE o.organization_id = p_cust_vr.org_id
and O2.ORGANIZATION_ID = o.organization_id
AND O2.ORG_INFORMATION_CONTEXT||'' = 'CLASS'
AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND O2.ORG_INFORMATION2 = 'Y';
SELECT 1
FROM GMD_ORG_ACCESS_VW;
SELECT 1
FROM hz_cust_acct_sites_all casa
, hz_cust_site_uses_all csua
, hz_cust_accounts_all caa
WHERE casa.cust_acct_site_id = csua.cust_acct_site_id
AND casa.org_id = csua.org_id
AND casa.cust_account_id = caa.cust_account_id
AND csua.site_use_code = 'SHIP_TO'
AND NVL( p_cust_vr.org_id, csua.org_id) = csua.org_id
AND caa.cust_account_id = p_cust_vr.cust_id
AND csua.site_use_id = p_cust_vr.ship_to_site_id;
SELECT 1
FROM oe_order_headers_all oha
, oe_order_lines_all oola
, oe_transaction_types_tl ttt
WHERE oola.header_id = oha.header_id
AND oola.inventory_item_id = p_spec.inventory_item_id
AND oha.order_type_id = ttt.transaction_type_id
AND NVL( p_cust_vr.ship_to_site_id, oola.ship_to_org_id) = oola.ship_to_org_id
AND NVL( p_cust_vr.org_id, oha.org_id) = oha.org_id
AND NVL( p_cust_vr.cust_id, oha.sold_to_org_id) = oha.sold_to_org_id
AND oha.header_id = p_cust_vr.order_id
AND oha.cancelled_flag <> 'Y'
AND ttt.language = USERENV('LANG');
SELECT 1
FROM oe_order_lines_all oola
WHERE oola.header_id = p_cust_vr.order_id
AND NVL( p_cust_vr.ship_to_site_id, oola.ship_to_org_id) = oola.ship_to_org_id
AND oola.inventory_item_id = p_spec.inventory_item_id
AND oola.header_id = p_cust_vr.order_id
AND (oola.line_number + (oola.shipment_number / 10)) = p_cust_vr.order_line
AND oola.line_id = p_cust_vr.order_line_id;
check_who( p_user_id => p_cust_vr.last_updated_by);
OR p_cust_vr.last_update_date IS NULL)
THEN
GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
'WHAT', 'the dates must not be NULL');
SELECT vr.spec_vr_id, s.spec_name, s.spec_vers
FROM gmd_specifications_b s, gmd_customer_spec_vrs vr
WHERE s.spec_id = vr.spec_id
AND s.owner_organization_id = p_spec.owner_organization_id
AND s.inventory_item_id = p_spec.inventory_item_id
AND ( (s.revision is null and p_spec.revision is NULL ) OR -- handle item revision 5117733
(s.revision = p_spec.revision )
)
AND ((s.grade_code is NULL AND p_spec.grade_code is NULL) OR
(s.grade_code = p_spec.grade_code)
)
AND ((vr.organization_id is NULL AND p_cust_vr.organization_id is NULL) OR
(vr.organization_id = p_cust_vr.organization_id)
)
AND ((vr.cust_id is NULL AND p_cust_vr.cust_id is NULL) OR
(vr.cust_id = p_cust_vr.cust_id)
)
AND ((vr.org_id is NULL AND p_cust_vr.org_id is NULL) OR
(vr.org_id = p_cust_vr.org_id)
)
AND ((vr.order_id is NULL AND p_cust_vr.order_id is NULL) OR
(vr.order_id = p_cust_vr.order_id)
)
AND ((vr.order_line is NULL AND p_cust_vr.order_line is NULL) OR
(vr.order_line = p_cust_vr.order_line)
)
AND ((vr.order_line_id is NULL AND p_cust_vr.order_line_id is NULL) OR
(vr.order_line_id = p_cust_vr.order_line_id)
)
AND ((vr.ship_to_site_id is NULL AND p_cust_vr.ship_to_site_id is NULL) OR
(vr.ship_to_site_id = p_cust_vr.ship_to_site_id)
)
AND ((vr.end_date is NULL AND (p_cust_vr.end_date IS NULL OR
p_cust_vr.end_date >= vr.start_date)) OR
(p_cust_vr.end_date IS NULL AND
p_cust_vr.start_date <= nvl(vr.end_date, p_cust_vr.start_date)) OR
(p_cust_vr.start_date <= vr.end_date AND p_cust_vr.end_date >= vr.start_date)
)
AND ( floor(vr.spec_vr_status/100) = floor(p_cust_vr.spec_vr_status/100) AND
/* Bug 3090290; allow duplicate spec vr with "OBSOLUTE" status */
AND vr.spec_vr_status NOT IN (SELECT status_code FROM gmd_qc_status
WHERE status_type = 800)
AND vr.delete_mark = 0
AND s.delete_mark = 0
AND vr.spec_vr_id <> NVL(p_cust_vr.spec_vr_id, -1)
;
IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
-- Invalid Operation
GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
IF (p_operation IN ('INSERT', 'UPDATE')
AND supp_vr_exist(p_supp_vr, l_spec))
THEN
-- Disaster, Trying to insert duplicate
-- bug 2630007, odaboval put the message in function supp_vr_exist.
-- GMD_API_PUB.Log_Message('GMD_SUPP_VR_EXIST');
SELECT inventory_item_id INTO l_inventory_item_id FROM
gmd_specifications WHERE spec_id = p_supp_vr.spec_id;
SELECT owner_organization_id INTO l_organization_id FROM
gmd_specifications WHERE spec_id = p_supp_vr.spec_id;
SELECT * INTO l_item_mst
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
SELECT lot_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_spec.inventory_item_id
AND organization_id = p_spec.owner_organization_id;
SELECT 1
FROM po_vendors v
WHERE v.vendor_id = p_supp_vr.supplier_id
AND v.enabled_flag = 'Y'
AND sysdate between nvl(v.start_date_active, sysdate-1)
AND nvl(v.end_date_active, sysdate+1);
SELECT 1
FROM po_vendor_sites_all v
WHERE (v.purchasing_site_flag = 'Y'
OR v.rfq_only_site_flag = 'Y')
AND sysdate < NVL(inactive_date, sysdate + 1)
AND v.vendor_id = p_supp_vr.supplier_id
AND v.vendor_site_id = p_supp_vr.supplier_site_id;
SELECT 1
FROM po_headers_all pha
WHERE pha.po_header_id IN
(SELECT pla.po_header_id
FROM po_lines_all pla
WHERE pla.po_header_id = pha.po_header_id
AND pla.item_id = p_spec.inventory_item_id
AND pha.vendor_id = p_supp_vr.supplier_id
AND pha.vendor_site_id = p_supp_vr.supplier_site_id
AND pha.po_header_id = p_supp_vr.po_header_id); */
SELECT 1
FROM po_headers_all pha, po_lines_all pla
WHERE pha.po_header_id = p_supp_vr.po_header_id
AND pha.vendor_id = p_supp_vr.supplier_id
AND pha.vendor_site_id = p_supp_vr.supplier_site_id
AND pha.po_header_id = pla.po_header_id
AND pla.item_id = p_spec.inventory_item_id;
SELECT 1
FROM po_lines_all pla
WHERE pla.item_id = p_spec.inventory_item_id
AND pla.po_header_id = p_supp_vr.po_header_id
AND pla.po_line_id = p_Supp_vr.po_line_id;
check_who( p_user_id => p_supp_vr.last_updated_by);
OR p_supp_vr.last_update_date IS NULL)
THEN
GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
'WHAT', 'the dates must not be NULL');
SELECT vr.spec_vr_id, s.spec_name, s.spec_vers
FROM gmd_specifications_b s, gmd_supplier_spec_vrs vr
WHERE s.spec_id = vr.spec_id
AND s.owner_organization_id = p_spec.owner_organization_id
AND s.inventory_item_id = p_spec.inventory_item_id
AND ( (s.revision is null and p_spec.revision is NULL ) OR -- handle item revision 5117733
(s.revision = p_spec.revision )
)
AND ((s.grade_code is NULL AND p_spec.grade_code is NULL) OR
(s.grade_code = p_spec.grade_code)
)
AND ((vr.organization_id is NULL AND p_supp_vr.organization_id is NULL) OR
(vr.organization_id = p_supp_vr.organization_id)
)
AND ((vr.supplier_id is NULL AND p_supp_vr.supplier_id is NULL) OR
(vr.supplier_id = p_supp_vr.supplier_id)
)
AND ((vr.supplier_site_id is NULL AND p_supp_vr.supplier_site_id is NULL) OR
(vr.supplier_site_id = p_supp_vr.supplier_site_id)
)
AND ((vr.po_header_id is NULL AND p_supp_vr.po_header_id is NULL) OR
(vr.po_header_id = p_supp_vr.po_header_id)
)
AND ((vr.po_line_id is NULL AND p_supp_vr.po_line_id is NULL) OR
(vr.po_line_id = p_supp_vr.po_line_id)
)
AND ((vr.end_date is NULL AND (p_supp_vr.end_date IS NULL OR
p_supp_vr.end_date >= vr.start_date)) OR
(p_supp_vr.end_date IS NULL AND
p_supp_vr.start_date <= nvl(vr.end_date, p_supp_vr.start_date)) OR
(p_supp_vr.start_date <= vr.end_date AND p_supp_vr.end_date >= vr.start_date)
)
AND ( floor(vr.spec_vr_status/100) = floor(p_supp_vr.spec_vr_status/100) AND
/* Bug 3090290; allow duplicate spec vr with "OBSOLUTE" status */
AND vr.spec_vr_status NOT IN (SELECT status_code FROM gmd_qc_status
WHERE status_type = 800)
AND vr.delete_mark = 0
AND s.delete_mark = 0
AND vr.spec_vr_id <> NVL(p_supp_vr.spec_vr_id, -1)
;
PROCEDURE NAME: validate_before_delete_inv_vrs
DESCRIPTION: This procedure validates:
a) Primary key supplied
b) Inventory Spec VRS is not already delete_marked
PARAMETERS:
CHANGE HISTORY: Created 09-JUL-02 KYH
===========================================================================*/
PROCEDURE VALIDATE_BEFORE_DELETE_INV_VRS(
p_spec_id IN NUMBER,
p_spec_vr_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_message_data OUT NOCOPY VARCHAR2) IS
l_progress VARCHAR2(3);
IF l_inventory_spec_vrs.delete_mark <> 0
THEN
GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
'l_table_name', 'GMD_INVENTORY_SPEC_VRS',
'l_column_name', 'SPEC_VR_ID',
'l_key_value', l_inventory_spec_vrs.spec_vr_id);
FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
END VALIDATE_BEFORE_DELETE_INV_VRS ;
PROCEDURE NAME: validate_before_delete_wip_vrs
DESCRIPTION: This procedure validates:
a) Primary key supplied
b) WIP Spec VRS is not already delete_marked
PARAMETERS:
CHANGE HISTORY: Created 09-JUL-02 KYH
===========================================================================*/
PROCEDURE VALIDATE_BEFORE_DELETE_WIP_VRS(
p_spec_id IN NUMBER,
p_spec_vr_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_message_data OUT NOCOPY VARCHAR2) IS
l_progress VARCHAR2(3);
IF l_wip_spec_vrs.delete_mark <> 0
THEN
GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
'l_table_name', 'GMD_WIP_SPEC_VRS',
'l_column_name', 'SPEC_VR_ID',
'l_key_value', l_wip_spec_vrs.spec_vr_id);
FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
END VALIDATE_BEFORE_DELETE_WIP_VRS ;
PROCEDURE NAME: validate_before_delete_cst_vrs
DESCRIPTION: This procedure validates:
a) Primary key supplied
b) Customer Spec VRS is not already delete_marked
PARAMETERS:
CHANGE HISTORY: Created 09-JUL-02 KYH
===========================================================================*/
PROCEDURE VALIDATE_BEFORE_DELETE_CST_VRS(
p_spec_id IN NUMBER,
p_spec_vr_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_message_data OUT NOCOPY VARCHAR2) IS
l_progress VARCHAR2(3);
IF l_customer_spec_vrs.delete_mark <> 0
THEN
GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
'l_table_name', 'GMD_CUSTOMER_SPEC_VRS',
'l_column_name', 'SPEC_VR_ID',
'l_key_value', l_customer_spec_vrs.spec_vr_id);
FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
END VALIDATE_BEFORE_DELETE_CST_VRS ;
PROCEDURE NAME: validate_before_delete_sup_vrs
DESCRIPTION: This procedure validates:
a) Primary key supplied
b) Supplier Spec VRS is not already delete_marked
PARAMETERS:
CHANGE HISTORY: Created 09-JUL-02 KYH
===========================================================================*/
PROCEDURE VALIDATE_BEFORE_DELETE_SUP_VRS(
p_spec_id IN NUMBER,
p_spec_vr_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_message_data OUT NOCOPY VARCHAR2) IS
l_progress VARCHAR2(3);
IF l_supplier_spec_vrs.delete_mark <> 0
THEN
GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
'l_table_name', 'GMD_SUPPLIER_SPEC_VRS',
'l_column_name', 'SPEC_VR_ID',
'l_key_value', l_supplier_spec_vrs.spec_vr_id);
FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
END VALIDATE_BEFORE_DELETE_SUP_VRS ;
SELECT 1
FROM fnd_user
WHERE user_id = userid;
SELECT 1
FROM gem_lookups
WHERE lookup_type = 'GMD_QC_CERTIFICATE_TYPE'
AND lookup_code = p_coa_type;
SELECT 1
FROM mtl_material_statuses
WHERE NVL(enabled_flag,0) = 1
AND status_id = lot_status_id;
SELECT NVL(wms_enabled_flag,'N')
INTO l_wms_enabled_flag
FROM mtl_parameters
WHERE organization_id = p_organization_id;