APPS.GMPMRRP SQL Statements

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

Line: 6

	PROCEDURE mr_insert_header ;

Line: 103

   SELECT user_name
   FROM   fnd_user
   WHERE  user_name BETWEEN C_fBuyer_Plnr AND C_tBuyer_Plnr;

Line: 108

   SELECT user_id
   FROM   fnd_user
   WHERE  user_name = C_Buyer_Plnr ;

Line: 127

         select min(user_name) INTO X_fBuyer_Plnr from fnd_user;

Line: 132

         select max(user_name) INTO X_tBuyer_Plnr from fnd_user;

Line: 154

          G_planning_tab.delete;

Line: 155

          mr_insert_header;

Line: 233

| PROCEDURE NAME	mr_insert_header                                            |
|                                                                             |
| DESCRIPTION		Procedure to insert data into ps_matl_hdr                   |
|                                                                             |
| MODIFICATION HISTORY                                                        |
|   07/14/01     Praveen Reddy   -----	created                                |
|                                                                             |
+============================================================================*/

PROCEDURE mr_insert_header IS

 X_where	     VARCHAR2(4000) := NULL;

Line: 245

 X_select1     	     VARCHAR2(4000) := NULL;

Line: 254

    X_where :=  'SELECT  i.planning_class,i.item_id '
                ||' FROM  ps_oper_pcl c, ps_plng_cls p, ic_item_mst i, '
                ||' fnd_user f '
                ||' WHERE c.delete_mark=0 ' ;

Line: 278

      X_where:= X_where||' AND i.item_id in (select distinct item_id '
                ||' FROM mr_tran_tbl  WHERE mrp_id= to_char(:6) )' ;

Line: 307

      SELECT gem5_matl_rep_id_s.nextval INTO   X_rep_id FROM dual;

Line: 317

              INSERT INTO ps_matl_hdr (matl_rep_id,planning_class,item_id)
                VALUES(X_rep_id,X_planning_class,X_item_id);

Line: 328

       FND_FILE.PUT_LINE ( FND_FILE.LOG,'Error in mr insert header'|| sqlerrm);

Line: 331

END mr_insert_header;  /***** END PROCEDURE********************/

Line: 420

     DELETE FROM ps_matl_hdr
     WHERE item_id = V_item_id
     AND matl_rep_id = G_matl_rep_id;

Line: 454

        SELECT whse_item_id
        FROM   ic_item_mst
        WHERE  item_id = V_item_id;

Line: 472

      SELECT min(orgn_code), max(orgn_code)
      FROM   ps_schd_dtl
      WHERE  schedule_id = p_schedule_id;

Line: 477

      SELECT MIN(whse_code) INTO X_fwhse_code  FROM ic_whse_mst;

Line: 482

      SELECT MAX(whse_code) INTO X_twhse_code FROM ic_whse_mst;

Line: 505

          SELECT distinct trn.whse_code, whs.whse_item_id
          FROM mr_tran_tbl trn, ps_whse_eff whs
             WHERE  mrp_id = G_mrp_id
             AND item_id =   V_item_id
             AND trn.whse_code >=  X_fwhse_code
             AND trn.whse_code <=  X_twhse_code
             AND trn.whse_code = whs.whse_code
             AND whs.plant_code in (select orgn_code from ps_schd_dtl
                 where schedule_id = G_schedule_id
             AND orgn_code between X_forgn_code and X_torgn_code )
         ORDER BY 1;

Line: 518

         SELECT distinct trn.whse_code, whs.whse_item_id      --Bug 3168907 Added ps.whse_item_id
         FROM   mr_tran_tbl trn, ps_whse_eff whs, sy_orgn_usr org
            WHERE  mrp_id =G_mrp_id
            AND item_id = V_item_id
            AND trn.whse_code >=  X_fwhse_code
            AND trn.whse_code <=  X_twhse_code
            AND trn.whse_code = whs.whse_code
            AND whs.plant_code in (select orgn_code from ps_schd_dtl
                where schedule_id = G_schedule_id
                AND orgn_code between X_forgn_code and X_torgn_code )
            and whs.plant_code = org.orgn_code
            and org.user_id = G_Buyer_plnr_id
        ORDER BY 1;

Line: 587

 X_select1      VARCHAR2(4000) := NULL;

Line: 591

    X_select1   :='SELECT sum(trans_qty) total'||
                ' FROM mr_tran_tbl mr'||
                ' WHERE mrp_id= to_char(:1) AND item_id = to_char(:2) ' ||
                ' AND whse_code in ( ' || G_whse_list || ' ) ' ||
                ' and doc_type='||''''||'BAL'||''''||
                ' group by mr.doc_type';

Line: 602

    dbms_sql.parse (cur_balance, X_select1,dbms_sql.NATIVE);

Line: 636

      SELECT safety_stock
      FROM   ic_whse_inv
      WHERE  item_id= C_item_id
      AND whse_code is NULL and delete_mark=0;

Line: 643

    X_select1           VARCHAR2(4000) := NULL ;

Line: 648

    X_select1 :='SELECT sum(safety_stock) total_ss,count(*) no_ss'||
                ' FROM ic_whse_inv'||
                ' WHERE item_id = to_char(:1) ' ||
                ' AND whse_code in ( ' || G_whse_list || ' ) ' ||
                ' AND delete_mark=0 ';

Line: 656

    dbms_sql.parse (cur_sstock, X_select1,dbms_sql.NATIVE);