APPS.GMD_SPEC_VRS_GRP SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 94

  SELECT 1
  FROM mtl_parameters m
  WHERE m.process_enabled_flag = 'Y';

Line: 100

  SELECT 1
  FROM   gmd_qc_status
  WHERE  status_code = p_status_code
  AND    delete_mark = 0;

Line: 163

  IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
    -- Invalid Operation
    GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');

Line: 226

  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');

Line: 305

SELECT 1
FROM   mtl_secondary_inventories
WHERE  secondary_inventory_name   = l_mon_vr.subinventory
AND organization_id = l_mon_vr.locator_organization_id;

Line: 311

SELECT 1
FROM   mtl_item_locations
WHERE  organization_id   = l_mon_vr.locator_organization_id
AND    inventory_location_id    = l_mon_vr.locator_id;

Line: 317

select 1
from cr_rsrc_mst
where resources = l_mon_vr.resources
and delete_mark = 0;

Line: 323

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 ;

Line: 340

  check_who( p_user_id  => l_mon_vr.last_updated_by);

Line: 342

   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');

Line: 564

  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   */

Line: 597

  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)
  ;

Line: 702

  IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
    -- Invalid Operation
    GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');

Line: 774

  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');

Line: 796

    SELECT inventory_item_id INTO l_inventory_item_id FROM
    gmd_specifications WHERE spec_id = p_inv_vr.spec_id;

Line: 798

    SELECT owner_organization_id INTO l_organization_id FROM
    gmd_specifications WHERE spec_id = p_inv_vr.spec_id;

Line: 800

    SELECT * INTO l_item_mst
    FROM mtl_system_items_b
    WHERE inventory_item_id = l_inventory_item_id
    AND organization_id = l_organization_id;

Line: 929

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;

Line: 936

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;

Line: 944

SELECT 1
FROM   mtl_secondary_inventories
WHERE  secondary_inventory_name   = l_inv_vr.subinventory
AND organization_id = l_inv_vr.organization_id;

Line: 950

SELECT 1
FROM   mtl_item_locations
WHERE  organization_id   = l_inv_vr.organization_id
AND    inventory_location_id    = l_inv_vr.locator_id;

Line: 970

  check_who( p_user_id  => l_inv_vr.last_updated_by);

Line: 972

   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');

Line: 1196

  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   */

Line: 1232

  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)
  ;

Line: 1331

  IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
    -- Invalid Operation
    GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');

Line: 1413

  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');

Line: 1424

    SELECT inventory_item_id INTO l_inventory_item_id FROM
    gmd_specifications WHERE spec_id = p_wip_vr.spec_id;

Line: 1426

    SELECT owner_organization_id INTO l_organization_id FROM
    gmd_specifications WHERE spec_id = p_wip_vr.spec_id;

Line: 1428

    SELECT * INTO l_item_mst
    FROM mtl_system_items_b
    WHERE inventory_item_id = l_inventory_item_id
    AND organization_id = l_organization_id;

Line: 1545

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 ;

Line: 1558

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
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
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, bh.routing_id) = bh.routing_id;

Line: 1599

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
AND rout.delete_mark = 0
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;

Line: 1627

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
AND rout.delete_mark = 0
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;

Line: 1654

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;

Line: 1674

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;

Line: 1696

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;

Line: 1705

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;

Line: 1714

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, grec.recipe_id) = grec.recipe_id
AND NVL( l_wip_vr.formula_id, grec.formula_id) = grec.formula_id
AND s.delete_mark = 0
AND grec.delete_mark = 0
AND r.delete_mark = 0
AND r.routing_id = l_wip_vr.routing_id;

Line: 1728

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, grec.recipe_id) = grec.recipe_id
AND NVL( l_wip_vr.formula_id, grec.formula_id) = grec.formula_id
AND s.delete_mark = 0
AND grec.delete_mark = 0
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;

Line: 1743

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;

Line: 1756

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;

Line: 1769

SELECT oprn_no, oprn_vers
FROM gmd_operations o
WHERE o.delete_mark = 0
AND o.oprn_id = l_wip_vr.oprn_id;

Line: 1814

  check_who( p_user_id  => l_wip_vr.last_updated_by);

Line: 1816

   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');

Line: 2389

  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   */

Line: 2456

  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)
  ;

Line: 2550

  IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
    -- Invalid Operation
    GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');

Line: 2606

  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');

Line: 2617

    SELECT inventory_item_id INTO l_inventory_item_id FROM
    gmd_specifications WHERE spec_id = p_cust_vr.spec_id;

Line: 2619

    SELECT owner_organization_id INTO l_organization_id FROM
    gmd_specifications WHERE spec_id = p_cust_vr.spec_id;

Line: 2621

    SELECT * INTO l_item_mst
    FROM mtl_system_items_b
    WHERE inventory_item_id = l_inventory_item_id
    AND organization_id = l_organization_id;

Line: 2721

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;

Line: 2737

/*SELECT 1
FROM hr_operating_units
WHERE organization_id = p_cust_vr.org_id; */

Line: 2742

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';

Line: 2756

  SELECT 1
  FROM GMD_ORG_ACCESS_VW;

Line: 2760

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;

Line: 2773

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');

Line: 2789

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;

Line: 2809

  check_who( p_user_id  => p_cust_vr.last_updated_by);

Line: 2811

   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');

Line: 3062

  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   */

Line: 3103

  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)
  ;

Line: 3200

  IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
    -- Invalid Operation
    GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');

Line: 3263

  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');

Line: 3283

    SELECT inventory_item_id INTO l_inventory_item_id FROM
    gmd_specifications WHERE spec_id = p_supp_vr.spec_id;

Line: 3285

    SELECT owner_organization_id INTO l_organization_id FROM
    gmd_specifications WHERE spec_id = p_supp_vr.spec_id;

Line: 3287

    SELECT * INTO l_item_mst
    FROM mtl_system_items_b
    WHERE inventory_item_id = l_inventory_item_id
    AND organization_id = l_organization_id;

Line: 3387

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;

Line: 3393

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);

Line: 3401

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;

Line: 3412

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); */

Line: 3425

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;

Line: 3439

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;

Line: 3455

  check_who( p_user_id  => p_supp_vr.last_updated_by);

Line: 3457

   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');

Line: 3696

  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   */

Line: 3731

  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)
  ;

Line: 3771

  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);

Line: 3828

        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);

Line: 3845

      FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');

Line: 3852

END VALIDATE_BEFORE_DELETE_INV_VRS ;

Line: 3855

  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);

Line: 3912

        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);

Line: 3929

      FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');

Line: 3936

END VALIDATE_BEFORE_DELETE_WIP_VRS ;

Line: 3939

  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);

Line: 3995

        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);

Line: 4012

      FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');

Line: 4019

END VALIDATE_BEFORE_DELETE_CST_VRS ;

Line: 4022

  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);

Line: 4078

        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);

Line: 4095

      FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');

Line: 4102

END VALIDATE_BEFORE_DELETE_SUP_VRS ;

Line: 4117

SELECT 1
FROM fnd_user
WHERE  user_id = userid;

Line: 4167

SELECT 1
FROM gem_lookups
WHERE lookup_type = 'GMD_QC_CERTIFICATE_TYPE'
AND lookup_code = p_coa_type;

Line: 4287

SELECT 1
FROM mtl_material_statuses
WHERE NVL(enabled_flag,0) = 1
AND   status_id = lot_status_id;