APPS.POS_PRODUCT_SERVICE_UTL_PKG SQL Statements

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

Line: 76

   g_product_segments.DELETE;

Line: 77

   g_structure_segments.DELETE;

Line: 85

      SELECT parent_flex_value_set_id
	FROM fnd_flex_value_sets
	WHERE flex_value_set_id = p_value_set_id;

Line: 309

      SELECT category_set_id
	FROM mtl_default_category_sets
	WHERE functional_area_id = 2;

Line: 320

      SELECT structure_id
	FROM mtl_category_sets
	WHERE category_set_id = g_default_po_category_set_id;

Line: 348

   FOR x IN (SELECT profile_option_value
	       FROM fnd_profile_option_values
	      WHERE profile_option_id IN
	            (SELECT profile_option_id
		       FROM fnd_profile_options
		      WHERE profile_option_name = g_product_segment_profile_name
		        AND level_id = 10001
		        AND level_value = 0
		     )
	     ) LOOP
	g_product_segment_definition := x.profile_option_value;

Line: 449

	   'select ' ||
	   g_product_segments(p_product_segment_index).meaning_column || ' description' ||
	   ' from ' ||
	   g_product_segments(p_product_segment_index).table_name ||
	   ' ' || g_product_segments(p_product_segment_index).where_clause || ' and (' ||
	   l_id_or_value || ' = :1 )';

Line: 461

	'select description from fnd_flex_values_vl ' ||
	'where flex_value_set_id = :1 and flex_value = :2 ';

Line: 468

	'select description from fnd_flex_values_vl ' ||
	' where flex_value_set_id = :1 and flex_value = :2 and parent_flex_value_low = :3';

Line: 610

	SELECT * FROM pos_sup_products_services
	  WHERE classification_id = p_classification_id;

Line: 668

   l_query := 'select mcb.category_id from mtl_categories_b mcb, ' ||
     ' mtl_category_set_valid_cats mcsvc, pos_sup_products_services psps where ' ||
     ' (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'') ' ||
     ' and mcsvc.category_set_id = ' || g_default_po_category_set_id ||
     ' and mcb.category_id = mcsvc.category_id ' ||
     ' and psps.classification_id = :1 and (';

Line: 808

	SELECT * FROM pos_sup_products_services
	  WHERE classification_id = p_classification_id;

Line: 946

   l_query := 'select psps.vendor_id, mcb.category_id from mtl_categories_b mcb, ' ||
     ' mtl_category_set_valid_cats mcsvc, pos_sup_products_services psps where ' ||
     ' (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'') ' ||
     ' and mcsvc.category_set_id = ' || g_default_po_category_set_id ||
     ' and mcb.category_id = mcsvc.category_id and (';

Line: 976

   PROCEDURE insert_into_glb_temp
   (
       p_validation_type     IN  VARCHAR2,
       p_curr_seg_val_id     IN  NUMBER,
       p_parent_seg_val_id   IN  NUMBER,
       p_table_name          IN VARCHAR2,
       p_where_clause        IN VARCHAR2,
       p_meaning             IN VARCHAR2,
       p_id_column           IN VARCHAR2,
       p_value_column        IN VARCHAR2,
       x_return_status       OUT nocopy VARCHAR2,
       x_msg_count           OUT nocopy NUMBER,
       x_msg_data            OUT nocopy VARCHAR2
   ) IS

   l_tbl_sql varchar2(4000) := null;

Line: 997

   select *
   from fnd_flex_values_vl
   where flex_value_set_id = p_parent_seg_val_id;

Line: 1004

         insert into pos_products_services_gt(SEGMENT_VALUE ,
                             SEGMENT_VALUE_DESCRIPTION   ,
                             SUPPLIER_SELECTION          ,
                             PARENT_SEGMENT_VALUE        ,
                             PARENT_SEGMENT_VALUE_ID     ,
                             HIERARCHY_LEVEL             ,
                             SEGMENT_VALUE_ID            ,
                             LAST_UPDATE_DATE            ,
                             LAST_UPDATED_BY             ,
                             LAST_UPDATE_LOGIN           ,
                             CREATION_DATE               ,
                             CREATED_BY                 )
                   (  SELECT flex_value,
                         description,
                         null,
                         null,
                         null,
                         null,
                         flex_value_id,
                         sysdate,
                         fnd_global.user_id,
                         fnd_global.login_id,
                         sysdate,
                         fnd_global.user_id
                         FROM fnd_flex_values_vl
                        WHERE flex_value_set_id = p_curr_seg_val_id
                        and not exists (select SEGMENT_VALUE_ID
                                        from pos_products_services_gt
                                        where SEGMENT_VALUE_ID = flex_value_id )
                 );

Line: 1037

           insert into pos_products_services_gt(SEGMENT_VALUE ,
                             SEGMENT_VALUE_DESCRIPTION   ,
                             SUPPLIER_SELECTION          ,
                             PARENT_SEGMENT_VALUE        ,
                             PARENT_SEGMENT_VALUE_ID     ,
                             HIERARCHY_LEVEL             ,
                             SEGMENT_VALUE_ID            ,
                             LAST_UPDATE_DATE            ,
                             LAST_UPDATED_BY             ,
                             LAST_UPDATE_LOGIN           ,
                             CREATION_DATE               ,
                             CREATED_BY                 )
       (  SELECT flex_value,
                         description,
                         null,
                         c1.flex_value,
                         c1.flex_value_id,
                         null,
                         flex_value_id,
                         sysdate,
                         fnd_global.user_id,
                         fnd_global.login_id,
                         sysdate,
                         fnd_global.user_id
                         FROM fnd_flex_values_vl
                        WHERE flex_value_set_id = p_curr_seg_val_id and
                              parent_flex_value_low = c1.flex_value and
                              not exists  (select SEGMENT_VALUE_ID
                                           from pos_products_services_gt
                                           where SEGMENT_VALUE_ID = flex_value_id)

                 );

Line: 1078

        l_tbl_sql := 'SELECT '||p_meaning||' description, '||
              l_valorid_col||' value '||
              ' FROM '||p_table_name||
              ' WHERE '||p_where_clause||
              ' AND NOT EXISTS (select SEGMENT_VALUE_ID from pos_products_services_gt where SEGMENT_VALUE_ID = '||l_valorid_col||' ) ';

Line: 1088

          insert into pos_products_services_gt(SEGMENT_VALUE ,
                             SEGMENT_VALUE_DESCRIPTION   ,
                             SUPPLIER_SELECTION          ,
                             PARENT_SEGMENT_VALUE        ,
                             PARENT_SEGMENT_VALUE_ID     ,
                             HIERARCHY_LEVEL             ,
                             SEGMENT_VALUE_ID            ,
                             LAST_UPDATE_DATE            ,
                             LAST_UPDATED_BY             ,
                             LAST_UPDATE_LOGIN           ,
                             CREATION_DATE               ,
                             CREATED_BY                 )
           values(l_value,
                  l_description,
                  null,
                  null,
                  null,
                  null,
                  l_value,
                  sysdate,
                  fnd_global.user_id,
                  fnd_global.login_id,
                  sysdate,
                  fnd_global.user_id);

Line: 1120

         x_msg_data := 'Insert into Global temp failed  ';

Line: 1124

   END insert_into_glb_temp;

Line: 1134

     SELECT SEGMENT_VALUE_DESCRIPTION
     FROM pos_products_services_gt
     START WITH segment_value_id = x_segment_value_id
     CONNECT BY PRIOR parent_segment_value_id =segment_value_id
     ORDER BY parent_segment_value_id DESC;

Line: 1165

     SELECT SEGMENT_VALUE
     FROM pos_products_services_gt
     START WITH segment_value_id = x_segment_value_id
     CONNECT BY PRIOR parent_segment_value_id =segment_value_id
     ORDER BY parent_segment_value_id DESC;

Line: 1224

     l_sql := 'SELECT classification_id
              FROM pos_sup_products_services
              WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
              'AND status <> '||''''||'X'||''''||
              'AND vendor_id = '||x_vendor_id||
              'union all
               select PS_REQUEST_ID classification_id
               from POS_PRODUCT_SERVICE_REQUESTS psr , POS_SUPPLIER_MAPPINGS pmapp
               WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
               'and pmapp.mapping_id = psr.MAPPING_ID
               AND (psr.REQUEST_STATUS =''PENDING'' or psr.REQUEST_STATUS =''REJECTED'')
               AND pmapp.vendor_id = '||x_vendor_id  ;

Line: 1243

   /* check if the immediate parent has been already selected. If so return the classification Id of the parent for the children .
   This will disable the children on the screen so user cannot select them again
   */

   if (l_class_id = 0) then
     if (instr(l_concat_value,g_delimiter,-1) <> 0 ) then
       l_next_seg := substr(l_concat_value,instr(l_concat_value,g_delimiter,-1)+4);

Line: 1257

      l_parent_sql := 'SELECT classification_id
              FROM pos_sup_products_services
       WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
              'AND '||l_next_seg||' is null '||
              'AND status <> '||''''||'X'||''''||
              'AND vendor_id = '||x_vendor_id||
               'union all
               select PS_REQUEST_ID classification_id
               from POS_PRODUCT_SERVICE_REQUESTS psr , POS_SUPPLIER_MAPPINGS pmapp
               WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
               'AND '||l_next_seg||' is null '||
               'and pmapp.mapping_id = psr.MAPPING_ID
                AND (psr.REQUEST_STATUS ='||''''||'PENDING'||''''|| 'or psr.REQUEST_STATUS ='||''''|| 'REJECTED'||''''||')
               AND pmapp.vendor_id = '||x_vendor_id ;

Line: 1319

     l_sql := 'select PS_REQUEST_ID
               from POS_PRODUCT_SERVICE_REQUESTS
               WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
               'and MAPPING_ID='||x_mapp_id||
               'AND REQUEST_STATUS =''PENDING'' ';

Line: 1332

   /* check if the immediate parent has been already selected. If so return the classification Id of the parent for the children .
   This will disable the children on the screen so user cannot select them again
   */

   if (l_class_id = 0) then
     if (instr(l_concat_value,g_delimiter,-1) <> 0 ) then
       l_next_seg := substr(l_concat_value,instr(l_concat_value,g_delimiter,-1)+4);

Line: 1347

               select PS_REQUEST_ID
               from POS_PRODUCT_SERVICE_REQUESTS
               WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
               'AND '||l_next_seg||' is null '||
               'and MAPPING_ID='||x_mapp_id||
                'AND REQUEST_STATUS =''PENDING''';

Line: 1377

     (select segment1 , segment2, segment3 , segment4,segment5 , segment6,segment7 , segment8,segment9 , segment10,
     segment11 , segment12,segment13 , segment14,segment15 , segment16,segment17 , segment18,segment19 , segment20
     from pos_sup_products_services
     where classification_id = x_classification_id)
     UNION ALL
     (select segment1 , segment2, segment3 , segment4,segment5 , segment6,segment7 , segment8,segment9 , segment10,
     segment11 , segment12,segment13 , segment14,segment15 , segment16,segment17 , segment18,segment19 , segment20
     from POS_PRODUCT_SERVICE_REQUESTS
     where PS_REQUEST_ID = x_classification_id);

Line: 1445

   l_query := 'select mcb.category_id from mtl_categories_b mcb, ' ||
     ' mtl_category_set_valid_cats mcsvc, pos_product_service_requests ppsr where ' ||
     ' (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'') ' ||
     ' and mcsvc.category_set_id = ' || g_default_po_category_set_id ||
     ' and mcb.category_id = mcsvc.category_id ' ||
     ' and ppsr.ps_request_id = :1 and (';

Line: 1480

	SELECT * FROM pos_product_service_requests
	  WHERE ps_request_id = p_ps_request_id;

Line: 1557

 * insert a record into the pos_supplier_mappings table for corresponding vendor id and party id
 * else, use the existing mapping_id value of the party id to create a Pending Product and
 * Service Request.
 * Please refer the bug 7374266 for more information.
*/

SELECT party_id
INTO   l_party_id
FROM   AP_SUPPLIERS
WHERE  vendor_id = p_vendor_id;

Line: 1568

SELECT COUNT(mapping_id)
INTO l_count
FROM pos_supplier_mappings
WHERE party_id = l_party_id;

Line: 1574

   INSERT INTO pos_supplier_mappings
   (
      mapping_id, party_id , vendor_id ,
      created_by, creation_date,
      last_updated_by, last_update_date, last_update_login
    )
    VALUES
    (
       pos_supplier_mapping_s.nextval, l_party_id, p_vendor_id,
       fnd_global.user_id, sysdate,
       fnd_global.user_id, sysdate, fnd_global.login_id
    );

Line: 1588

select mapping_id
into l_mapping_id
from Pos_supplier_mappings
where vendor_id = p_vendor_id ;

Line: 1593

INSERT INTO POS_PRODUCT_SERVICE_REQUESTS
     (
        PS_REQUEST_ID
      , MAPPING_ID
      , segment1
      , segment2
      , segment3
      , segment4
      , segment5
      , segment6
      , segment7
      , segment8
      , segment9
      , segment10
      , segment11
      , segment12
      , segment13
      , segment14
      , segment15
      , segment16
      , segment17
      , segment18
      , segment19
      , segment20
      , request_status
      , request_type
      , segment_definition
      , created_by
      , creation_date
      , last_updated_by
      , last_update_date
      , last_update_login
     )
     VALUES
     (
        POS_PRODUCT_SERVICE_REQ_S.NEXTVAL
      , l_mapping_id
      , p_segment1
      , p_segment2
      , p_segment3
      , p_segment4
      , p_segment5
      , p_segment6
      , p_segment7
      , p_segment8
      , p_segment9
      , p_segment10
      , p_segment11
      , p_segment12
      , p_segment13
      , p_segment14
      , p_segment15
      , p_segment16
      , p_segment17
      , p_segment18
      , p_segment19
      , p_segment20
      , 'PENDING'
      , 'ADD'
      , p_segment_definition
      , fnd_global.user_id
      , Sysdate
      , fnd_global.user_id
      , Sysdate
      , fnd_global.login_id
     );

Line: 1663

PROCEDURE update_main_ps_req
(     p_req_id_tbl        IN  po_tbl_number,
      p_status            IN  VARCHAR2,
      x_return_status     OUT nocopy VARCHAR2,
      x_msg_count         OUT nocopy NUMBER,
      x_msg_data          OUT nocopy VARCHAR2
)
IS
BEGIN

   for i in 1..p_req_id_tbl.COUNT LOOP

   UPDATE POS_SUP_PRODUCTS_SERVICES
      SET status = p_status,
      last_updated_by = fnd_global.user_id,
      last_update_date = Sysdate,
      last_update_login = fnd_global.login_id
   WHERE classification_id = p_req_id_tbl(i);

Line: 1686

END update_main_ps_req;

Line: 1698

   UPDATE POS_PRODUCT_SERVICE_REQUESTS
            SET request_status = 'DELETED',
            last_updated_by = fnd_global.user_id,
            last_update_date = Sysdate,
            last_update_login = fnd_global.login_id
   WHERE PS_REQUEST_ID = p_req_id_tbl(i);

Line: 1719

   UPDATE POS_PRODUCT_SERVICE_REQUESTS
            SET request_status = 'PENDING',
            request_type = 'ADD',
            last_updated_by = fnd_global.user_id,
            last_update_date = Sysdate,
            last_update_login = fnd_global.login_id
   WHERE PS_REQUEST_ID = p_req_id_tbl(i);