APPS.INVPUOPI SQL Statements

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

Line: 17

G_DYNAMIC_UPDATE    VARCHAR2(1000):= NULL;

Line: 44

       SELECT FS.application_column_name
       INTO   l_segment_name
       FROM   FND_ID_FLEX_SEGMENTS  FS
       WHERE  FS.SEGMENT_NUM    = G_SEGMENT_NUMS_USED(i)
       AND     FS.ID_FLEX_CODE   = l_id_flex_code
       AND     FS.ID_FLEX_NUM    = l_id_flex_num
       AND     FS.ENABLED_FLAG   = l_enabled_flag
       AND     FS.APPLICATION_ID = l_application_id;

Line: 70

   IF G_DYNAMIC_UPDATE IS NULL THEN

      G_DYNAMIC_UPDATE := ' UPDATE mtl_system_items_interface'||
           ' SET     item_number = '||G_SEGMENT_STRING||
           ' WHERE ROWID   = :p_row_id '||
           ' RETURNING inventory_item_id, item_number INTO :item_id, :item_number';

Line: 78

   EXECUTE IMMEDIATE G_DYNAMIC_UPDATE
   USING p_row_id, OUT item_id,  OUT item_number;

Line: 376

** update segment values in MTL_SYSTEM_ITEMS_INTERFACE
*/
   update MTL_SYSTEM_ITEMS_INTERFACE
   set segment1 = DECODE(seg_value(1),NULL,segment1,seg_value(1)),
   segment2 = DECODE(seg_value(2),NULL,segment2,seg_value(2)),
   segment3 = DECODE(seg_value(3),NULL,segment3,seg_value(3)),
   segment4 = DECODE(seg_value(4),NULL,segment4,seg_value(4)),
   segment5 = DECODE(seg_value(5),NULL,segment5,seg_value(5)),
   segment6 = DECODE(seg_value(6),NULL,segment6,seg_value(6)),
   segment7 = DECODE(seg_value(7),NULL,segment7,seg_value(7)),
   segment8 = DECODE(seg_value(8),NULL,segment8,seg_value(8)),
   segment9 = DECODE(seg_value(9),NULL,segment9,seg_value(9)),
   segment10 = DECODE(seg_value(10),NULL,segment10,seg_value(10)),
   segment11 = DECODE(seg_value(11),NULL,segment11,seg_value(11)),
   segment12 = DECODE(seg_value(12),NULL,segment12,seg_value(12)),
   segment13 = DECODE(seg_value(13),NULL,segment13,seg_value(13)),
   segment14 = DECODE(seg_value(14),NULL,segment14,seg_value(14)),
   segment15 = DECODE(seg_value(15),NULL,segment15,seg_value(15)),
   segment16 = DECODE(seg_value(16),NULL,segment16,seg_value(16)),
   segment17 = DECODE(seg_value(17),NULL,segment17,seg_value(17)),
   segment18 = DECODE(seg_value(18),NULL,segment18,seg_value(18)),
   segment19 = DECODE(seg_value(19),NULL,segment19,seg_value(19)),
   segment20 = DECODE(seg_value(20),NULL,segment20,seg_value(20))
   Where     rowid   = p_rowid ;

Line: 458

      select structure_id
      into struct_id
      from mtl_category_sets_b
      where category_set_id = set_id;

Line: 501

	 SELECT FT.concatenated_segment_delimiter
	   INTO delimiter
	   FROM  fnd_id_flex_structures FT
	   WHERE FT.id_flex_code = flex_code
	     AND FT.APPLICATION_ID = l_application_id
	     AND FT.ID_FLEX_NUM = structure_id;

Line: 510

	   SELECT  max(FS.segment_num)
	   INTO max_segment
	   FROM  FND_ID_FLEX_SEGMENTS FS
	   WHERE FS.APPLICATION_ID = l_application_id
	     AND FS.id_flex_code = flex_code
	     AND FS.ENABLED_FLAG = 'Y'
	     AND FS.id_flex_num = structure_id;

Line: 596

	SELECT to_number(substr(application_column_name,8)), segment_num BULK COLLECT INTO
	G_SEGMENTS_USED, G_SEGMENT_NUMS_USED
	FROM  FND_ID_FLEX_SEGMENTS
	WHERE APPLICATION_ID = l_application_id
	AND id_flex_code = flex_code
	AND ENABLED_FLAG = 'Y'
	AND id_flex_num = structure_id
	ORDER BY segment_num;

Line: 636

      select INVENTORY_ITEM_ID
      into flex_id
      from MTL_SYSTEM_ITEMS_INTERFACE
      where ORGANIZATION_ID = org_id
      and NVL(segment1,' ') = DECODE(seg_value(1),NULL,' ',seg_value(1))
      and NVL(segment2,' ') = DECODE(seg_value(2),NULL,' ',seg_value(2))
      and NVL(segment3,' ') = DECODE(seg_value(3),NULL,' ',seg_value(3))
      and NVL(segment4,' ') = DECODE(seg_value(4),NULL,' ',seg_value(4))
      and NVL(segment5,' ') = DECODE(seg_value(5),NULL,' ',seg_value(5))
      and NVL(segment6,' ') = DECODE(seg_value(6),NULL,' ',seg_value(6))
      and NVL(segment7,' ') = DECODE(seg_value(7),NULL,' ',seg_value(7))
      and NVL(segment8,' ') = DECODE(seg_value(8),NULL,' ',seg_value(8))
      and NVL(segment9,' ') = DECODE(seg_value(9),NULL,' ',seg_value(9))
      and NVL(segment10,' ') = DECODE(seg_value(10),NULL,' ',seg_value(10))
      and NVL(segment11,' ') = DECODE(seg_value(11),NULL,' ',seg_value(11))
      and NVL(segment12,' ') = DECODE(seg_value(12),NULL,' ',seg_value(12))
      and NVL(segment13,' ') = DECODE(seg_value(13),NULL,' ',seg_value(13))
      and NVL(segment14,' ') = DECODE(seg_value(14),NULL,' ',seg_value(14))
      and NVL(segment15,' ') = DECODE(seg_value(15),NULL,' ',seg_value(15))
      and NVL(segment16,' ') = DECODE(seg_value(16),NULL,' ',seg_value(16))
      and NVL(segment17,' ') = DECODE(seg_value(17),NULL,' ',seg_value(17))
      and NVL(segment18,' ') = DECODE(seg_value(18),NULL,' ',seg_value(18))
      and NVL(segment19,' ') = DECODE(seg_value(19),NULL,' ',seg_value(19))
      and NVL(segment20,' ') = DECODE(seg_value(20),NULL,' ',seg_value(20))
      --Bug: 6192567 process_flag = 1
      and process_flag IN (1,2) and inventory_item_id is NOT NULL
      and rownum = 1;   --Bug:3340808,3531430

Line: 665

      select INVENTORY_LOCATION_ID
      into flex_id
      from MTL_ITEM_LOCATIONS
      where ORGANIZATION_ID = org_id
      and NVL(segment1,' ') = DECODE(seg_value(1),NULL,' ',seg_value(1))
      and NVL(segment2,' ') = DECODE(seg_value(2),NULL,' ',seg_value(2))
      and NVL(segment3,' ') = DECODE(seg_value(3),NULL,' ',seg_value(3))
      and NVL(segment4,' ') = DECODE(seg_value(4),NULL,' ',seg_value(4))
      and NVL(segment5,' ') = DECODE(seg_value(5),NULL,' ',seg_value(5))
      and NVL(segment6,' ') = DECODE(seg_value(6),NULL,' ',seg_value(6))
      and NVL(segment7,' ') = DECODE(seg_value(7),NULL,' ',seg_value(7))
      and NVL(segment8,' ') = DECODE(seg_value(8),NULL,' ',seg_value(8))
      and NVL(segment9,' ') = DECODE(seg_value(9),NULL,' ',seg_value(9))
      and NVL(segment10,' ') = DECODE(seg_value(10),NULL,' ',seg_value(10))
      and NVL(segment11,' ') = DECODE(seg_value(11),NULL,' ',seg_value(11))
      and NVL(segment12,' ') = DECODE(seg_value(12),NULL,' ',seg_value(12))
      and NVL(segment13,' ') = DECODE(seg_value(13),NULL,' ',seg_value(13))
      and NVL(segment14,' ') = DECODE(seg_value(14),NULL,' ',seg_value(14))
      and NVL(segment15,' ') = DECODE(seg_value(15),NULL,' ',seg_value(15))
      and NVL(segment16,' ') = DECODE(seg_value(16),NULL,' ',seg_value(16))
      and NVL(segment17,' ') = DECODE(seg_value(17),NULL,' ',seg_value(17))
      and NVL(segment18,' ') = DECODE(seg_value(18),NULL,' ',seg_value(18))
      and NVL(segment19,' ') = DECODE(seg_value(19),NULL,' ',seg_value(19))
      and NVL(segment20,' ') = DECODE(seg_value(20),NULL,' ',seg_value(20));

Line: 691

      select CATEGORY_ID
      into flex_id
      from mtl_categories_b
      where structure_id = struct_id
      and NVL(segment1,' ') = DECODE(seg_value(1),NULL,' ',seg_value(1))
      and NVL(segment2,' ') = DECODE(seg_value(2),NULL,' ',seg_value(2))
      and NVL(segment3,' ') = DECODE(seg_value(3),NULL,' ',seg_value(3))
      and NVL(segment4,' ') = DECODE(seg_value(4),NULL,' ',seg_value(4))
      and NVL(segment5,' ') = DECODE(seg_value(5),NULL,' ',seg_value(5))
      and NVL(segment6,' ') = DECODE(seg_value(6),NULL,' ',seg_value(6))
      and NVL(segment7,' ') = DECODE(seg_value(7),NULL,' ',seg_value(7))
      and NVL(segment8,' ') = DECODE(seg_value(8),NULL,' ',seg_value(8))
      and NVL(segment9,' ') = DECODE(seg_value(9),NULL,' ',seg_value(9))
      and NVL(segment10,' ') = DECODE(seg_value(10),NULL,' ',seg_value(10))
      and NVL(segment11,' ') = DECODE(seg_value(11),NULL,' ',seg_value(11))
      and NVL(segment12,' ') = DECODE(seg_value(12),NULL,' ',seg_value(12))
      and NVL(segment13,' ') = DECODE(seg_value(13),NULL,' ',seg_value(13))
      and NVL(segment14,' ') = DECODE(seg_value(14),NULL,' ',seg_value(14))
      and NVL(segment15,' ') = DECODE(seg_value(15),NULL,' ',seg_value(15))
      and NVL(segment16,' ') = DECODE(seg_value(16),NULL,' ',seg_value(16))
      and NVL(segment17,' ') = DECODE(seg_value(17),NULL,' ',seg_value(17))
      and NVL(segment18,' ') = DECODE(seg_value(18),NULL,' ',seg_value(18))
      and NVL(segment19,' ') = DECODE(seg_value(19),NULL,' ',seg_value(19))
      and NVL(segment20,' ') = DECODE(seg_value(20),NULL,' ',seg_value(20));

Line: 761

   select min(FS.application_column_name),min(FS.segment_num)
     into min_segment,min_seg_num
   from FND_ID_FLEX_SEGMENTS FS
   where FS.APPLICATION_ID = l_application_id
     and FS.id_flex_code = l_id_flex_code
     and FS.ENABLED_FLAG = l_enabled_flag
     and FS.id_flex_num = l_id_flex_num;

Line: 771

      select FS.segment_num into check_seg_num
      from FND_ID_FLEX_SEGMENTS FS
      where FS.APPLICATION_ID = l_application_id
        and FS.id_flex_code = l_id_flex_code
        and FS.ENABLED_FLAG = l_enabled_flag
        and FS.id_flex_num =  l_id_flex_num
        and FS.application_column_name = 'SEGMENT1';

Line: 792

** item_number is a concatenation of columns) and is not selective enough
**
** 31-MAY-95: Added new if clause to not look at delimiter if
** num_of_segments = 1; Fix for  bug 285002

Line: 814

          select inventory_item_id
      into item_id_out
          from mtl_system_items_b_kfv
          where organization_id = org_id
          and concatenated_segments = item_number_in;

Line: 820

      select inventory_item_id
      into item_id_out
      from mtl_system_items_b_kfv
      where segment1 = seg1
        and organization_id = org_id
        and concatenated_segments = item_number_in;

Line: 829

       select inventory_item_id
         into item_id_out
       from mtl_system_items_b_kfv
       where organization_id = org_id
         and concatenated_segments  = item_number_in;

Line: 860

   select organization_id
   into org_id
   from mtl_parameters
   where organization_code = org_code;

Line: 886

   select template_id
   into templ_id
   from mtl_item_templates
   where template_name = templ_name;

Line: 940

   INSERT INTO mtl_interface_errors
   (
   TRANSACTION_ID,
   UNIQUE_ID,
   ORGANIZATION_ID,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   CREATION_DATE,
   CREATED_BY,
   LAST_UPDATE_LOGIN,
   COLUMN_NAME,
   TABLE_NAME,
   MESSAGE_NAME,
   ERROR_MESSAGE,
   REQUEST_ID,
   PROGRAM_APPLICATION_ID,
   PROGRAM_ID,
   PROGRAM_UPDATE_DATE
   )
   VALUES
   (
   trans_id,
   mtl_system_items_interface_s.NEXTVAL,
   org_id,
   l_sysdate,
   user_id,
   l_sysdate,
   user_id,
   login_id,
   p_column_name,
   tbl_name,
   msg_name,
   SUBSTRB(translated_text, 1,2000),
   req_id,
   prog_appid,
   prog_id,
   l_sysdate
   );

Line: 1057

          select inventory_item_id
       into item_id_out
       from mtl_system_items_b_kfv
      where concatenated_segments = item_number_in
      group by inventory_item_id; -- Bug: 3447718 - added group by to   get distinct inventory_item_id

Line: 1063

          select inventory_item_id
       into item_id_out
       from mtl_system_items_b_kfv
      where segment1 = seg1
        and concatenated_segments = item_number_in
      group by inventory_item_id; -- Bug: 3447718 - added group by to   get distinct inventory_item_id

Line: 1072

       select inventory_item_id
         into item_id_out
       from mtl_system_items_b_kfv
       where concatenated_segments  = item_number_in
       group by inventory_item_id;  -- Bug:  3447718  - added  group by to get   distinct inventory_item_id

Line: 1097

   SELECT FT.concatenated_segment_delimiter
   INTO  G_SEGMENT_DELIMITER
   FROM  fnd_id_flex_structures FT
   WHERE FT.id_flex_code = l_id_flex_code
     AND FT.APPLICATION_ID = l_application_id
     AND FT.ID_FLEX_NUM = l_id_flex_num;

Line: 1104

   SELECT max(FS.segment_num)
   INTO  G_SEGMENTS_INUSE
   FROM  FND_ID_FLEX_SEGMENTS FS
   WHERE FS.APPLICATION_ID = l_application_id
     AND FS.id_flex_code = l_id_flex_code
     AND FS.ENABLED_FLAG = l_enabled_flag
     AND FS.id_flex_num = l_id_flex_num;

Line: 1112

   SELECT to_number(substr(application_column_name,8)), segment_num BULK COLLECT INTO G_SEGMENTS_USED, G_SEGMENT_NUMS_USED
   FROM  FND_ID_FLEX_SEGMENTS
   WHERE APPLICATION_ID = l_application_id
      AND id_flex_code = l_id_flex_code
      AND ENABLED_FLAG = l_enabled_flag
      AND id_flex_num = l_id_flex_num
   ORDER BY segment_num;

Line: 1120

   SELECT COUNT(*) INTO G_NUM_OF_SEGMENTS
   FROM  FND_ID_FLEX_SEGMENTS FS
   WHERE FS.APPLICATION_ID = l_application_id
     AND FS.id_flex_code = l_id_flex_code
     AND FS.ENABLED_FLAG = l_enabled_flag
     AND FS.id_flex_num =  l_id_flex_num;

Line: 1127

   SELECT min(FS.application_column_name),min(FS.segment_num)
   INTO G_MIN_SEGMENT,G_MIN_SEG_NUM
   FROM FND_ID_FLEX_SEGMENTS FS
   WHERE FS.APPLICATION_ID = l_application_id
     AND FS.id_flex_code = l_id_flex_code
     AND FS.ENABLED_FLAG = l_enabled_flag
     AND FS.id_flex_num = l_id_flex_num;

Line: 1136

      select FS.segment_num into G_CHECK_SEG_NUM
      from FND_ID_FLEX_SEGMENTS FS
      where FS.APPLICATION_ID = l_application_id
        and FS.id_flex_code = l_id_flex_code
   and FS.ENABLED_FLAG = l_enabled_flag
   and FS.id_flex_num = l_id_flex_num
   and FS.application_column_name = 'SEGMENT1';