APPS.GMD_QC_ERES_UTILS SQL Statements

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

Line: 59

    UPDATE gmd_specifications_b
    SET    spec_status = p_to_status
    WHERE  spec_id = p_spec_id;

Line: 67

      UPDATE gmd_specifications_b
      SET spec_status  = l_pending_status
      WHERE spec_id    = p_spec_id;

Line: 76

      UPDATE gmd_specifications_b
      SET spec_status  = l_rework_status
      WHERE spec_id    = p_spec_id;

Line: 96

    update_vr_status(p_entity_type,
                  p_spec_vr_id,
                  p_to_status);

Line: 104

      update_vr_status(p_entity_type,
                    p_spec_vr_id,
                    l_pending_status);

Line: 113

      update_vr_status(p_entity_type,
                    p_spec_vr_id,
                    l_rework_status);

Line: 119

  PROCEDURE update_vr_status(pentity_type IN VARCHAR2,
                             pspec_vr_id  IN NUMBER,
                             p_to_status IN NUMBER) IS
  BEGIN

      IF (pentity_type = 'I') THEN
        UPDATE gmd_inventory_spec_vrs
           SET spec_vr_status = p_to_status
         WHERE spec_vr_id = pspec_vr_id;

Line: 129

        UPDATE gmd_wip_spec_vrs
           SET spec_vr_status = p_to_status
          WHERE spec_vr_id = pspec_vr_id;

Line: 133

        UPDATE gmd_customer_spec_vrs
           SET spec_vr_status = p_to_status
         WHERE spec_vr_id = pspec_vr_id;

Line: 137

         UPDATE gmd_supplier_spec_vrs
           SET spec_vr_status = p_to_status
          WHERE spec_vr_id = pspec_vr_id;

Line: 141

         UPDATE gmd_monitoring_spec_vrs
           SET spec_vr_status = p_to_status
          WHERE spec_vr_id = pspec_vr_id;

Line: 146

  END update_vr_status;

Line: 155

    SELECT v.spec_vr_id,'I' spec_type
  FROM gmd_inventory_spec_vrs v
 WHERE v.spec_id = p_spec_id
 AND   v.spec_vr_status  < p_to_status
UNION
SELECT v.spec_vr_id,'W' spec_type
  FROM gmd_wip_spec_vrs v
 WHERE v.spec_id = p_spec_id
 AND   v.spec_vr_status  < p_to_status
UNION
SELECT v.spec_vr_id,'C' spec_type
  FROM gmd_customer_spec_vrs v
 WHERE v.spec_id = p_spec_id
 AND   v.spec_vr_status  < p_to_status
UNION
SELECT v.spec_vr_id,'S' spec_type
  FROM gmd_supplier_spec_vrs v
 WHERE v.spec_id = p_spec_id
 AND   v.spec_vr_status  < p_to_status
UNION
SELECT v.spec_vr_id,v.rule_type
  FROM gmd_monitoring_spec_vrs v
 WHERE v.spec_id = p_spec_id
 AND   v.spec_vr_status  < p_to_status
UNION
SELECT v.spec_vr_id,'T' spec_type
  FROM gmd_stability_spec_vrs v
 WHERE v.spec_id = p_spec_id
 AND   v.spec_vr_status  < p_to_status;

Line: 197

    update_vr_status(pentity_type  => l_spec_type,
                     pspec_vr_id  => l_spec_validity_rule_id,
                     p_to_status => p_to_status);

Line: 200

  SELECT DECODE(l_spec_type,'I','oracle.apps.gmd.qm.spec.vr.inv',
                            'W','oracle.apps.gmd.qm.spec.vr.wip',
                            'C','oracle.apps.gmd.qm.spec.vr.cus',
                            'S','oracle.apps.gmd.qm.spec.vr.sup',
                            'R','oracle.apps.gmd.qm.spec.vr.mon',
                            'L','oracle.apps.gmd.qm.spec.vr.mon')
       INTO l_event_name
   FROM sys.dual;

Line: 232

  /* Lets first update the status of the entity to to status */
  IF p_event = 'oracle.apps.gmd.qm.spec.vr.inv' THEN
    UPDATE gmd_inventory_spec_vrs
    SET spec_vr_status = p_to_status
    WHERE spec_vr_id = p_event_key
    AND   spec_vr_status <> p_to_status;

Line: 239

    UPDATE gmd_wip_spec_vrs
    SET spec_vr_status = p_to_status
    WHERE spec_vr_id = p_event_key
    AND   spec_vr_status <> p_to_status;

Line: 245

   UPDATE gmd_customer_spec_vrs
    SET spec_vr_status = p_to_status
    WHERE spec_vr_id = p_event_key
    AND   spec_vr_status <> p_to_status;

Line: 250

    UPDATE gmd_supplier_spec_vrs
    SET spec_vr_status = p_to_status
    WHERE spec_vr_id = p_event_key
    AND   spec_vr_status <> p_to_status;

Line: 255

    UPDATE gmd_specifications_b
    SET spec_status = p_to_status
    WHERE spec_id = p_event_key
    AND   spec_status <> p_to_status;

Line: 281

       SELECT hr.NAME
       FROM MTL_parameters mp, hr_all_organization_units hr
       WHERE mp.ORGANIZATION_CODE =p_orgn_code
       and hr.organization_id = mp.organization_id;

Line: 296

      SELECT USER_NAME
      FROM FND_USER
      WHERE user_id = p_user_id;

Line: 307

      SELECT TEST_METHOD_CODE
      FROM  gmd_test_methods
      WHERE TEST_METHOD_ID = p_test_method_id;

Line: 318

      SELECT TEST_METHOD_DESC
      FROM  gmd_test_methods
      WHERE TEST_METHOD_ID = p_test_method_id;

Line: 335

    SELECT
        hzp.party_name
    FROM
        hz_parties hzp
      , hz_cust_accounts_all hzca
    WHERE   hzp.party_id = hzca.party_id
    AND     hzca.cust_account_id  = p_cust_id
    ;

Line: 359

  SELECT name
  FROM   hr_operating_units
  WHERE  organization_id = p_org_id
  ;

Line: 378

  SELECT location
  FROM   hz_cust_site_uses_all
  WHERE  site_use_id = p_ship_to_site_id
  ;

Line: 397

  SELECT order_number
  FROM   oe_order_headers_all
  WHERE  header_id = p_order_id
  ;

Line: 415

  SELECT b.name
  FROM   oe_order_headers_all a, oe_transaction_types_tl b
  WHERE  a.order_type_id = b.transaction_type_id
  AND    a.header_id = p_order_id
  ;

Line: 434

  SELECT line_number
  FROM   oe_order_lines_all
  WHERE  line_id = p_order_line_id
  ;

Line: 452

  SELECT segment1
  FROM   po_vendors
  WHERE  vendor_id = p_supp_id
  ;

Line: 470

  SELECT vendor_name
  FROM   po_vendors
  WHERE  vendor_id = p_supp_id
  ;

Line: 488

  SELECT vendor_site_code
  FROM   po_vendor_sites_all
  WHERE  vendor_site_id = p_supp_site_id
  ;

Line: 506

  SELECT segment1
  FROM   po_headers_all
  WHERE  po_header_id = p_po_id
  ;

Line: 524

  SELECT line_num
  FROM   po_lines_all
  WHERE  po_line_id = p_po_line_id
  ;

Line: 543

  SELECT receipt_num
  FROM   rcv_shipment_headers
  WHERE  shipment_header_id = p_receipt_id
  ;

Line: 561

  SELECT line_num
  FROM   rcv_shipment_lines
  WHERE  shipment_line_id = p_receipt_line_id
  ;

Line: 580

  SELECT meaning
  FROM   gmd_qc_status
  WHERE  status_code = p_status_code
  AND    entity_type = p_entity_type
  ;

Line: 607

    UPDATE gmd_stability_studies_b
    SET    status = p_to_status
    WHERE  ss_id = p_ss_id;

Line: 624

      UPDATE gmd_stability_studies_b
      SET    status = l_pending_status
      WHERE  ss_id = p_ss_id;

Line: 633

      UPDATE gmd_stability_studies_b
      SET    status = l_rework_status
      WHERE  ss_id = p_ss_id;

Line: 650

        select test_desc
        from gmd_qc_tests
        where test_id = p_test_id;

Line: 670

SELECT meaning
       FROM gem_lookups
      WHERE lookup_type = 'GMD_QC_FREQUENCY_PERIOD'
      and   lookup_code = p_time ;

Line: 688

      SELECT ORGANIZATION_CODE
      FROM MTL_PARAMETERS
      WHERE ORGANIZATION_ID = p_orgn_id;

Line: 701

      SELECT concatenated_segments
      FROM mtl_system_items_kfv
      WHERE inventory_item_Id = p_item_id
      AND ORGANIZATION_ID = p_org_id;

Line: 714

      SELECT distinct concatenated_segments
      FROM mtl_system_items_kfv
      WHERE inventory_item_Id = p_item_id ;

Line: 727

      SELECT description
      FROM mtl_system_items_kfv
      WHERE inventory_item_Id = p_item_id
      AND ORGANIZATION_ID = p_org_id;

Line: 741

      SELECT SPEC_NAME
      FROM  gmd_specifications
      WHERE SPEC_ID  = p_spec_id ;

Line: 753

      SELECT  SPEC_VERS
      FROM  gmd_specifications
      WHERE SPEC_ID  = p_spec_id ;

Line: 766

      SELECT primary_uom_code
      FROM mtl_system_items
      WHERE inventory_item_Id = p_item_id
      AND ORGANIZATION_ID = p_org_id;

Line: 791

      SELECT concatenated_segments
      FROM mtl_item_locations_kfv
      WHERE inventory_location_id = p_loc_id ;

Line: 806

  SELECT meaning
  FROM fnd_lookup_values_vl
  WHERE  lookup_type = plookup_type
  AND    lookup_code = plookup_code;

Line: 870

   SELECT CALC_UOM_CONV_IND
     FROM GMD_SPEC_TESTS
     WHERE SPEC_ID = p_spec_id
     AND TEST_ID = p_test_id;

Line: 876

   SELECT primary_uom_code
      FROM mtl_system_items
      WHERE inventory_item_Id = p_item_id
      AND ORGANIZATION_ID = p_organization_id;

Line: 931

   SELECT NVL(REPORT_PRECISION,-1)
   FROM GMD_QC_TESTS
   WHERE TEST_ID = p_test_id;

Line: 958

   SELECT LENGTH(l_value) INTO l_total_length FROM DUAL;

Line: 960

   SELECT INSTR(l_value,'.') INTO l_dot_precision FROM DUAL;

Line: 970

       SELECT SUBSTR(l_value, 0) INTO x_decimal_value FROM DUAL;

Line: 976

         SELECT SUBSTR(l_value, 0, l_dot_precision - 1) INTO x_decimal_value FROM DUAL;

Line: 987

       SELECT RPAD(l_value,l_total_length,'.') INTO l_value FROM DUAL;

Line: 990

     SELECT RPAD(l_value,l_final_length,'0') INTO x_decimal_value FROM DUAL;

Line: 1010

 select max(change_disp_id)
 from GMD_CHANGE_DISPOSITION
 where sampling_event_id = p_sampling_event_id
 and organization_id = p_organization_id
 group by organization_id;

Line: 1017

 select max(change_disp_id)
 from GMD_CHANGE_DISPOSITION
 where sample_id = p_sample_id
 and sampling_event_id = p_sampling_event_id
 and organization_id = p_organization_id
 group by organization_id;

Line: 1025

 select gl.meaning
 from gmd_change_disposition gcd ,gem_lookups gl
 where gl.lookup_type = 'GMD_QC_SAMPLE_DISP'
 and gl.lookup_code = gcd.disposition_from
 and gcd.change_disp_id = p_change_disp_id;

Line: 1032

 select gl.meaning
 from gmd_change_disposition gcd ,gem_lookups gl
 where gl.lookup_type = 'GMD_QC_SAMPLE_DISP'
 and gl.lookup_code = gcd.disposition_to
 and gcd.change_disp_id = p_change_disp_id;

Line: 1096

  SELECT 'Y'
  FROM gmd_composite_spec_disp
  WHERE event_spec_disp_id = p_event_spec_disp_id;

Line: 1132

  SELECT license_plate_number
  FROM wms_license_plate_numbers
  WHERE lpn_id = p_lpn_id;