APPS.GMI_MSCA_PUB SQL Statements

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

Line: 24

     Select um_code
     Into   v_um_code
     From   sy_uoms_mst
     Where  unit_of_measure = x_apps_unit_meas_lookup_code;

Line: 60

  SELECT item_no, item_desc1, item_id
  , loct_ctl, lot_ctl, sublot_ctl, grade_ctl, status_ctl, dualum_ind
  , qc_grade, lot_status, lot_indivisible, item_um, item_um2, noninv_ind
  FROM   ic_item_mst
  WHERE  UPPER(item_no) like UPPER(p_item_no)
  AND    delete_mark = 0

      --- For GTIN support
  UNION

  SELECT item_no, item_desc1, item_id
  , loct_ctl, lot_ctl, sublot_ctl, grade_ctl, status_ctl, dualum_ind
  , qc_grade, lot_status, lot_indivisible,
  Get_Opm_Uom_Code(mcr.uom_code) item_um,
  item_um2, noninv_ind
  FROM
      mtl_cross_references mcr,
      mtl_system_items mti,
      ic_item_mst opi
  WHERE
      opi.item_no = mti.segment1
      AND mti.inventory_item_id = mcr.inventory_item_id
      AND    mcr.cross_reference_type = g_gtin_cross_ref_type
      AND    mcr.cross_reference      LIKE l_cross_ref
---      AND    (mcr.organization_id = mti.organization_id
           ---OR
---      AND    mcr.org_independent_flag = 'Y'

  ORDER BY item_no;

Line: 93

  SELECT item_no, item_desc1, item_id
  , loct_ctl, lot_ctl, sublot_ctl, grade_ctl, status_ctl, dualum_ind
  , qc_grade, lot_status, lot_indivisible, item_um, item_um2, noninv_ind
  FROM   ic_item_mst
  WHERE  1=2;

Line: 115

select location, loct_desc
from ic_loct_mst
where whse_code = :ic_adjs_jnl_vw.to_whse_code
and delete_mark = 0 and location <> :parameter.default_loct
UNION
select distinct l.location, '' loct_desc
from ic_loct_inv l
where   whse_code = :ic_adjs_jnl_vw.to_whse_code
and not exists (select location from ic_loct_mst where location =  l.location)
and  (:ic_adjs_jnl_vw.item_loct_ctl = 2 OR :ic_adjs_jnl_vw.to_whse_loct_ctl = 2) order by 1
*/

BEGIN

  OPEN x_loct_cursor FOR
  SELECT location, loct_desc, whse_code
  FROM ic_loct_mst
  WHERE whse_code LIKE p_whse_code
  AND   location <> FND_PROFILE.VALUE('IC$DEFAULT_LOCT')
  AND   location LIKE p_loct
  AND   delete_mark = 0
  UNION
  SELECT l.location, null, l.whse_code
  FROM ic_loct_inv l
  WHERE l.whse_code LIKE p_whse_code
  AND   l.location LIKE p_loct
  AND   NOT EXISTS(select location from ic_loct_mst where location =  l.location)
  ORDER BY 1;

Line: 163

SELECT DISTINCT A.lot_no, A.sublot_no
FROM ic_lots_mst A, ic_loct_inv B
WHERE  A.lot_id = B.lot_id AND A.delete_mark = 0 AND B.delete_mark = 0
AND B.item_id = :PARAMETER.item_id and a.item_id = b.item_id
AND lot_no <> :parameter.default_lot
UNION
select lot_no, sublot_no
from ic_lots_mst
where item_id = :parameter.item_id
and (substr(:ic_jrnl_mst.trans_type,1,3) in ('CRE','ADJ')) and delete_mark = 0
and lot_no <> :parameter.default_lot
ORDER BY 1, 2
*/
BEGIN

    OPEN x_lot_cursor FOR
    SELECT DISTINCT A.lot_no, NVL(A.sublot_no, ' '), NVL(A.lot_desc, ' ')
    , A.lot_id, A.item_id, NVL(A.qc_grade, ' '), B.lot_status
    FROM ic_lots_mst A, ic_loct_inv B
    WHERE  A.lot_id = B.lot_id
    AND A.lot_no LIKE p_lot
    AND A.delete_mark = 0
    AND B.delete_mark = 0
    AND B.item_id IN (SELECT item_id FROM ic_item_mst WHERE item_no = p_item_no)
    AND A.item_id = B.item_id
    AND NVL(p_whse_code, B.whse_code) = B.whse_code
    AND NVL(p_location, B.location) = B.location
   UNION
    SELECT DISTINCT A.lot_no, NVL(A.sublot_no, ' '), NVL(A.lot_desc, ' ')
    , A.lot_id, A.item_id, NVL(A.qc_grade, ' '), null
    FROM ic_lots_mst A
    WHERE A.lot_no LIKE p_lot
    AND A.delete_mark = 0
    AND A.item_id IN (SELECT item_id FROM ic_item_mst WHERE item_no = p_item_no)
    AND A.lot_id > 0
    ORDER BY 1, 2;

Line: 218

SELECT m.orgn_code, m.orgn_name, m.co_code
FROM   sy_orgn_mst m, sy_orgn_usr u
WHERE  u.user_id = p_user_id
AND    u.orgn_code = m.orgn_code
AND    m.orgn_code like p_orgn
AND    m.delete_mark = 0
ORDER BY m.orgn_code;

Line: 250

SELECT reason_code, reason_desc1, reason_type
FROM sy_reas_cds
WHERE delete_mark = 0
AND ((NVL(FND_PROFILE.VALUE('GMA_REASON_CODE_SECURITY'), 'N') = 'N'
   OR reason_code IN (SELECT reason_code FROM gma_reason_code_security
                   WHERE (doc_type = p_doc_type or doc_type IS NULL)
                     AND (responsibility_id = FND_GLOBAL.RESP_id OR responsibility_id IS NULL)) ) )
AND reason_code LIKE UPPER(p_reason)
ORDER BY 1 ;

Line: 278

    SELECT DISTINCT A.lot_no, NVL(A.sublot_no, ' '), NVL(A.lot_desc, ' ')
    , A.lot_id, A.item_id, NVL(A.qc_grade, ' '), B.lot_status
    FROM   ic_lots_mst A
    , ic_loct_inv B
    WHERE  B.lot_id = A.lot_id
    AND    B.item_id = A.item_id
    AND    A.sublot_no like p_sublot
    AND    A.lot_no = p_lot
    AND    A.item_id IN (SELECT item_id FROM ic_item_mst WHERE item_no = p_item_no)
    AND    B.delete_mark = 0
    AND    A.delete_mark = 0
    ORDER BY 1, 2;

Line: 308

SELECT u.uom_code
FROM   sy_uoms_mst u
, ic_item_cnv i
WHERE  i.um_type = u.um_type
AND    u.uom_code like p_uom
AND    i.item_id IN (SELECT item_id FROM ic_item_mst WHERE item_no = p_item_no)
AND    i.delete_mark = 0
AND    u.delete_mark = 0
UNION ALL
SELECT item_um
FROM ic_item_mst
WHERE item_no = p_item_no
AND   item_um like p_uom
ORDER BY 1;

Line: 345

   SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
   FROM   ic_whse_mst w, sy_orgn_usr u
   WHERE  u.orgn_code = w.orgn_code
   AND    u.user_id = p_user_id
   AND    w.whse_code like p_whse_code
   AND    w.delete_mark = 0
   ORDER BY whse_code;

Line: 355

   SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
   FROM   ic_whse_mst w
   WHERE  1=2;

Line: 363

   SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
   FROM   ic_whse_mst w, sy_orgn_usr u
   WHERE  u.orgn_code = w.orgn_code
   AND    u.orgn_code = p_orgn_code
   AND    u.user_id = p_user_id
   AND    w.whse_code like p_whse_code
   AND    w.delete_mark = 0
   ORDER BY whse_code;

Line: 374

   SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
   FROM   ic_whse_mst w
   WHERE  1=2;

Line: 406

   SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
   FROM   ic_whse_mst w
   WHERE  w.whse_code like p_whse_code
   AND    w.delete_mark = 0
   ORDER BY whse_code;

Line: 414

   SELECT w.whse_code, w.whse_name, w.orgn_code, w.mtl_organization_id, loct_ctl
   FROM   ic_whse_mst w
   WHERE  1=2;

Line: 436

select location, loct_desc
from ic_loct_mst
where whse_code = :ic_adjs_jnl_vw.to_whse_code
and delete_mark = 0 and location <> :parameter.default_loct UNION
select distinct l.location, '' loct_desc
from ic_loct_inv l
where   whse_code = :ic_adjs_jnl_vw.to_whse_code
and not exists (select location from ic_loct_mst where location =  l.location)
and  (:ic_adjs_jnl_vw.item_loct_ctl = 2 OR :ic_adjs_jnl_vw.to_whse_loct_ctl = 2) order by 1
*/

BEGIN

  OPEN x_loct_cursor FOR
  SELECT location, loct_desc, whse_code
  FROM ic_loct_mst
  WHERE whse_code LIKE p_whse_code
  AND   location <> FND_PROFILE.VALUE('IC$DEFAULT_LOCT')
  AND   location LIKE p_loct
  AND   ( (location <> p_from_loct AND whse_code = p_from_whse)
        OR (whse_code <> p_from_whse) )
  AND   delete_mark = 0
  UNION
  SELECT l.location, null, l.whse_code
  FROM ic_loct_inv l
  WHERE l.whse_code LIKE p_whse_code
  AND   l.location LIKE p_loct
  AND   ( (l.location <> p_from_loct AND l.whse_code = p_from_whse)
        OR (l.whse_code <> p_from_whse) )
  AND   NOT EXISTS(select location from ic_loct_mst where location =  l.location)
  ORDER BY 1;

Line: 685

  SELECT ili.lot_status
  FROM ic_lots_sts sts
  , ic_loct_inv ili
  WHERE ili.lot_status = sts.lot_status
  AND   sts.lot_status LIKE stat
  AND   ili.location = loct
  AND   ili.item_id = item
  AND   NVL(lot, ili.lot_id) = ili.lot_id
  AND   ili.delete_mark = 0
  AND   sts.delete_mark = 0
  ORDER BY ili.lot_status;