APPS.GMPPSRP SQL Statements

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

Line: 6

	PROCEDURE ps_insert_header ;

Line: 135

   ps_insert_header;

Line: 141

     DELETE
     FROM   ps_matl_hdr pmh
     WHERE  pmh.matl_rep_id = G_matl_rep_id
        AND
        ((pmh.inventory_item_id NOT IN (SELECT pmd1.inventory_item_id
                               FROM   ps_matl_dtl pmd1
                               WHERE  pmd1.matl_rep_id = G_matl_rep_id))
        OR
        (pmh.organization_id NOT IN (SELECT organization_id
                                FROM ps_matl_dtl pmd2
                                WHERE pmd2.inventory_item_id = pmh.inventory_item_id
                                AND   pmd2.matl_rep_id = G_matl_rep_id)));

Line: 193

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

PROCEDURE ps_insert_header IS

 x_select               VARCHAR2(2000);

Line: 215

  x_select := ' SELECT DISTINCT '||
               ' msi.inventory_item_id, '||
               ' msi.organization_id, ';

Line: 220

     x_select := x_select || ' mca.category_id category_id';

Line: 222

     x_select := x_select || ' -999 category_id ';

Line: 224

     x_select := x_select || ' FROM ';

Line: 229

     x_select := x_select || ' mtl_planners mpl, ';

Line: 232

     x_select := x_select || ' hr_employees hem, ';

Line: 235

     x_select := x_select || ' mtl_parameters mpa, ';

Line: 238

     x_select := x_select || ' mtl_categories_kfv mca, ';

Line: 241

  x_select := x_select ||
               ' mtl_system_items_kfv msi, '||
               ' mtl_item_categories mic, '||
               ' ps_schd_dtl psd, '||
               ' mtl_category_sets mcs '||
            ' WHERE '||
               ' mcs.category_set_id = to_char(:category_set_id) '||
               ' AND mcs.structure_id = to_char(:structure_id) '||
               ' AND mic.category_set_id = mcs.category_set_id  '||
               ' AND psd.schedule_id = to_char(:schedule_id) '||
               ' AND psd.organization_id = msi.organization_id '||
               ' AND mic.inventory_item_id = msi.inventory_item_id '||
               ' AND mic.organization_id = msi.organization_id ';

Line: 256

       x_select := x_select || ' AND mcs.structure_id = mca.structure_id '||
               ' AND mic.category_id = mca.category_id ';

Line: 259

       x_select := x_select || ' AND mca.concatenated_segments >= :f_category ';

Line: 262

       x_select := x_select || ' AND mca.concatenated_segments <= :t_category ';

Line: 267

       x_select := x_select || ' AND mpl.planner_code = msi.planner_code  '||
               ' AND mpl.organization_id = msi.organization_id ';

Line: 270

       x_select := x_select || ' AND msi.planner_code  >= :f_planner ';

Line: 273

       x_select := x_select || ' AND msi.planner_code  <= :t_planner ';

Line: 278

       x_select := x_select || ' AND hem.employee_id = msi.buyer_id ';

Line: 280

       x_select := x_select || ' AND hem.full_name >= :f_buyer ';

Line: 283

       x_select := x_select || ' AND hem.full_name <= :t_buyer ';

Line: 288

       x_select := x_select || ' AND mpa.organization_id = msi.organization_id ';

Line: 290

       x_select := x_select || ' AND mpa.organization_code >= :f_org ';

Line: 293

       x_select := x_select || ' AND mpa.organization_code <= :t_org ';

Line: 298

    x_select := x_select || ' AND msi.concatenated_segments >= :f_item ';

Line: 301

    x_select := x_select || ' AND msi.concatenated_segments <= :t_item ';

Line: 305

  dbms_sql.parse (cur_item, x_select,dbms_sql.NATIVE);

Line: 352

     SELECT gmp_matl_rep_id_s.NEXTVAL INTO X_rep_id FROM dual;

Line: 364

        INSERT INTO ps_matl_hdr (matl_rep_id,inventory_item_id,organization_id,category_id)
             VALUES(X_rep_id,X_item_id,X_org_id,X_category_id);

Line: 377

END ps_insert_header;  /***** END PROCEDURE********************/

Line: 447

    SELECT primary_uom_code FROM mtl_system_items
    WHERE inventory_item_id =  V_item_id
       AND organization_id = V_organization_id;

Line: 485

      DELETE FROM ps_matl_hdr
      WHERE       inventory_item_id = V_item_id
      AND         organization_id = V_organization_id
      AND         matl_rep_id = G_matl_rep_id;

Line: 521

        SELECT NVL(nonnet_ind,0)
        FROM   ps_schd_hdr
        WHERE  schedule_id = V_schedule_id;

Line: 571

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

Line: 577

    X_select1 		VARCHAR2(2000) := NULL;

Line: 582

    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 '||
		' GROUP BY item_id';

Line: 589

    X_select1 :=
       ' SELECT NVL(SUM(s1.safety_stock_quantity), 0) total_ss'||
       ' FROM mtl_safety_stocks s1 '||
       ' WHERE s1.organization_id = to_char(:org_id)'||
       '    AND s1.inventory_item_id = to_char(:item_id)'||
       '    AND (s1.effectivity_date <= SYSDATE  '||
       '    AND s1.effectivity_date >= ( '||
       '       SELECT NVL(MAX(s2.effectivity_date), SYSDATE) '||
       '       FROM mtl_safety_stocks s2 '||
       '       WHERE s2.organization_id = s1.organization_id'||
       '       AND s2.inventory_item_id = to_char(:item_id)'||
       '       AND s2.effectivity_date <= SYSDATE)) ';

Line: 605

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

Line: 665

x_stmt := ' SELECT ' ||
    ' gmppsrp.organization_code( '||G_orgnanization_id||') master_org, ' ||
    ' gmppsrp.schedule( '||G_schedule_id||') schedule, ' ||
    ' gmppsrp.category_set( '||G_category_set||') category_set, ' ||
    ''''||G_fcategory||''''||' fcategory, ' ||
    ''''||G_tcategory||''''||' tcategory, ' ||
    ''''||G_fbuyer||''''||' fbuyer, ' ||
    ''''||G_tbuyer||''''||' tbuyer, ' ||
    ''''||G_fplanner||''''||' fplanner, ' ||
    ''''||G_tplanner||''''||' tplanner, ' ||
    ''''||G_forg||''''||' forg, ' ||
    ''''||G_torg||''''||' torg, ' ||
    ''''||G_fitem||''''||' fitem, ' ||
    ''''||G_titem||''''||' titem, ' ||
    ' CURSOR( ' ||
       ' SELECT  ' ||
          ' gmppsrp.item_name(pmh.inventory_item_id, pmh.organization_id) item_name,  ' ||
          ' gmppsrp.organization_code (pmh.organization_id) organization_code, ' ||
          ' gmppsrp.planner_code (pmh.inventory_item_id, pmh.organization_id) planner_code, ' ||
          ' gmppsrp.buyer_name (pmh.inventory_item_id, pmh.organization_id) buyer_name, ' ||
          ' gmppsrp.onhand_qty (pmh.inventory_item_id, pmh.organization_id) onhand_qty, ' ||
          ' gmppsrp.unit_of_measure(pmh.inventory_item_id, pmh.organization_id) primary_uom_code, ' ||
          ' gmppsrp.category(pmh.category_id) category, ' ||
          ' CURSOR(  ' ||
             ' SELECT pmd.* ' ||
             ' FROM ps_matl_dtl pmd ' ||
             ' WHERE pmd.inventory_item_id = pmh.inventory_item_id ' ||
             ' AND pmd.organization_id = pmh.organization_id ' ||
             ' AND pmd.matl_rep_id = pmh.matl_rep_id ' ||
             ' ORDER BY pmd.inventory_item_id, pmd.organization_id, pmd.perd_end_date ' ||
          ' ) DETAIL ' ||
       ' FROM ps_matl_hdr pmh ' ||
       ' WHERE pmh.matl_rep_id = ' ||G_matl_rep_id||
       ' ORDER BY pmh.inventory_item_id, pmh.organization_id ' ||
    ' ) HEADER ' ||
' FROM DUAL ';

Line: 734

     seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';

Line: 736

     INSERT INTO gmp_bucketed_xml_temp(bckt_matl_xml_id, xml_file) VALUES(x_seq_num, result);

Line: 761

   SELECT schedule INTO v_schedule_name
   FROM ps_schd_hdr
   WHERE schedule_id = p_schedule_id;

Line: 788

   SELECT category_set_name INTO v_category_set_name
   FROM mtl_category_sets
   WHERE category_set_id = p_category_set_id;

Line: 816

   SELECT concatenated_segments INTO v_item_name
   FROM mtl_system_items_kfv
   WHERE inventory_item_id = p_inventory_item_id
   AND organization_id = p_organization_id;

Line: 844

   SELECT organization_code INTO v_org_code
   FROM mtl_parameters
   WHERE organization_id = p_organization_id;

Line: 872

   SELECT planner_code INTO v_planner_code
   FROM mtl_system_items
   WHERE inventory_item_id = p_inventory_item_id
   AND organization_id = p_organization_id;

Line: 894

*     Rajesh Patangya - Modified the SELECT B4905328
*************************************************************** */

FUNCTION buyer_name (p_inventory_item_id NUMBER, p_organization_id NUMBER)
RETURN VARCHAR2 IS
   v_buyer_name VARCHAR2(240);

Line: 902

   SELECT he.full_name INTO v_buyer_name
     FROM mtl_system_items_b msi, per_people_f he
    WHERE msi.organization_id = p_organization_id
      AND msi.inventory_item_id = p_inventory_item_id
      AND msi.buyer_id = he.person_id  ;

Line: 974

   SELECT primary_uom_code INTO v_uom_code
   FROM mtl_system_items
   WHERE inventory_item_id = p_inventory_item_id
   AND organization_id = p_organization_id;

Line: 1002

   SELECT concatenated_segments INTO v_category
   FROM mtl_categories_kfv
   WHERE category_id = p_category_id;

Line: 1073

      DELETE FROM gmp_bucketed_xml_temp WHERE bckt_matl_xml_id = p_sequence_num;

Line: 1119

   SELECT xml_file INTO l_file
   FROM gmp_bucketed_xml_temp
   WHERE bckt_matl_xml_id = p_sequence_num;