APPS.ENG_NEW_ITEM_REQ_UTIL SQL Statements

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

Line: 88

        SELECT ecotv1.change_order_type_id change_mgmt_type_id,
            ecotv2.change_order_type_id,
            ecotv2.auto_numbering_method,
            ecotv1.auto_numbering_method change_mgmt_method
        FROM eng_change_order_types_vl  ecotv1, eng_change_order_types_vl ecotv2
        WHERE ecotv1.change_order_type_id  = l_change_order_type_id
        AND ecotv1.TYPE_CLASSIFICATION= 'CATEGORY'
        AND ecotv1.change_mgmt_type_code = ecotv2.change_mgmt_type_code
        AND ecotv2.type_name = change_type_code
        AND ecotv2.TYPE_CLASSIFICATION = 'HEADER';

Line: 103

        SELECT CHANGE_ID
        FROM   mtl_parameters org_param
             , eng_engineering_changes eng_change
        WHERE eng_change.change_notice =  p_change_notice
        AND eng_change.organization_id = org_param.organization_id
        AND org_param.organization_code = p_org_code;

Line: 112

               SELECT  a.status_name INTO stat_name
               FROM  eng_change_statuses_vl a,   eng_lifecycle_statuses b
               WHERE a.status_code = b.status_code
               AND b.entity_name='ENG_CHANGE_TYPE'
               AND b.entity_id1 = cp_change_mgmt_type_id
               ORDER BY b.sequence_number;

Line: 137

 	                          -- validaton on NIR type included in the select query.

 	      SELECT ECOT.change_order_type_id
 	      INTO   l_change_mgmt_type_id
 	      FROM   eng_change_order_types_tl ECOT,
 	             ENG_CHANGE_ORDER_TYPES ECO
 	      WHERE  ECOT.CHANGE_ORDER_TYPE_ID=ECO.CHANGE_ORDER_TYPE_ID
 	         AND ECO.CHANGE_MGMT_TYPE_CODE= 'NEW_ITEM_REQUEST'
 	         AND ECOT.type_name           = change_type_code
 	         AND ECOT.language            = userenv('LANG');

Line: 161

          select status_name into stat_name FROM ENG_CHANGE_STATUSES_VL where status_code=1;    --   1 = Open

Line: 165

    select change_order_type, change_order_type_id
      into change_mgmt_type_name, l_change_order_type_id
      FROM ENG_CHANGE_ORDER_TYPES_VL
     WHERE change_mgmt_type_code='NEW_ITEM_REQUEST'
       AND type_classification='CATEGORY';

Line: 208

                select alpha_prefix||next_available_number
                INTO l_change_number
                from eng_auto_number_ecn
                where change_type_id = l_type_id;

Line: 259

                ENG_CHANGE_IMPORT_UTIL.INSERT_IMPORT_CHANGE_ROW (
                X_ROWID => l_hist_rec_row_id,
                X_BATCH_ID => batch_id,
                X_CHANGE_ID => x_change_id,
                X_CREATION_DATE => SYSDATE,
                X_CREATED_BY => FND_GLOBAL.user_id,
                X_LAST_UPDATE_DATE => SYSDATE,
                X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
                X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
                )  ;

Line: 333

          select
               change_line_id,
               change_id,
               FND_GLOBAL.USER_ID created_by,
               sysdate creation_date,
               FND_GLOBAL.USER_ID last_updated_by,
               sysdate last_update_date,
               FND_GLOBAL.USER_ID last_update_login,
               (select change_order_type_id from eng_change_order_types where TYPE_CLASSIFICATION='LINE'
                AND CHANGE_MGMT_TYPE_CODE = 'NEW_ITEM_REQUEST' AND ROWNUM=1) change_type_id,
               1 status_code,
               1 APPROVAL_STATUS_TYPE
          from mtl_system_items_interface msii
          where msii.set_process_id = p_batch_id
          and msii.process_flag = l_process_flag
--        and msii.transaction_type=L_TRANSACTION_TYPE
          ;

Line: 356

SELECT DISTINCT MSII.ITEM_CATALOG_GROUP_ID,
       eng_types.type_name,
       msii.organization_id
 bulk collect  INTO l_icc_array ,
       l_change_type_array,
       l_org_array
  FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
       MTL_ITEM_CATALOG_GROUPS_VL micg,
       ENG_CHANGE_ORDER_TYPES_VL eng_types,
       MTL_PARAMETERS mp
 WHERE msii.set_process_id=P_BATCH_ID
   AND PROCESS_FLAG = l_process_flag
   AND msii.ITEM_CATALOG_GROUP_ID = micg.ITEM_CATALOG_GROUP_ID
--   AND micg.NEW_ITEM_REQ_CHANGE_TYPE_ID = eng_types.change_order_type_id
   AND
        (SELECT
       TO_NUMBER(SUBSTR(NEW_ITEM_REQ_CHANGE_TYPE_ID, INSTR(NEW_ITEM_REQ_CHANGE_TYPE_ID, '$$', 2)+2)) AS NEW_ITEM_REQ_CHANGE_TYPE_ID
       FROM
       (
         SELECT
           MIN( CASE WHEN micgb.NEW_ITEM_REQUEST_REQD = 'Y' AND ( PRIOR micgb.NEW_ITEM_REQUEST_REQD IS NULL OR PRIOR micgb.NEW_ITEM_REQUEST_REQD = 'I' )
                     THEN '$$'||LPad(LEVEL, 6, '0')||'$$'|| micgb.NEW_ITEM_REQ_CHANGE_TYPE_ID
                     ELSE NULL
                END
              ) NEW_ITEM_REQ_CHANGE_TYPE_ID

         FROM MTL_ITEM_CATALOG_GROUPS_B  micgb
         CONNECT BY PRIOR micgb.PARENT_CATALOG_GROUP_ID = micgb.ITEM_CATALOG_GROUP_ID
         START WITH micgb.ITEM_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
         )) = eng_types.change_order_type_id
   AND mp.organization_id = msii.organization_id
--   AND msii.TRANSACTION_TYPE = L_TRANSACTION_TYPE
   order by organization_id;

Line: 394

SELECT DISTINCT MSII.ITEM_CATALOG_GROUP_ID ,-- msii.segment1,
       eng_types.type_name,
       msii.organization_id
BULK COLLECT INTO l_icc_array ,
       l_change_type_array,
       l_org_array
FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
       ENG_CHANGE_ORDER_TYPES_VL eng_types,
       MTL_PARAMETERS mp,
       ego_item_cat_denorm_hier heir,
       mtl_item_catalog_groups_b micgb
 WHERE msii.set_process_id = P_BATCH_ID
   AND PROCESS_FLAG = l_process_flag
   AND mp.organization_id = msii.organization_id
   AND msii.item_catalog_group_id = heir.CHILD_CATALOG_GROUP_ID and
       eng_types.change_order_type_id = micgb.NEW_ITEM_REQ_CHANGE_TYPE_ID and
       micgb.item_catalog_group_id = heir.PARENT_CATALOG_GROUP_ID
ORDER BY organization_id;

Line: 414

SELECT DISTINCT MSII.ITEM_CATALOG_GROUP_ID,
       (
         SELECT
           MIN( CASE WHEN micgb.NEW_ITEM_REQUEST_REQD = 'Y' AND ( PRIOR micgb.NEW_ITEM_REQUEST_REQD IS NULL OR PRIOR micgb.NEW_ITEM_REQUEST_REQD = 'I' )
                     THEN '$$'||LPad(LEVEL, 6, '0')||'$$'|| micgb.NEW_ITEM_REQ_CHANGE_TYPE_ID
                     ELSE NULL
                END
              ) NEW_ITEM_REQ_CHANGE_TYPE_ID

         FROM MTL_ITEM_CATALOG_GROUPS_B  micgb
         CONNECT BY PRIOR micgb.PARENT_CATALOG_GROUP_ID = micgb.ITEM_CATALOG_GROUP_ID
         START WITH micgb.ITEM_CATALOG_GROUP_ID = msii.ITEM_CATALOG_GROUP_ID
         ) AS type_name,
       msii.organization_id
 bulk collect  INTO l_icc_array ,
       l_change_type_array,
       l_org_array
  FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
     --  ENG_CHANGE_ORDER_TYPES eng_types,
       MTL_PARAMETERS mp
 WHERE msii.set_process_id=P_BATCH_ID
   AND PROCESS_FLAG = l_process_flag
   AND mp.organization_id = msii.organization_id
   order by organization_id;

Line: 454

          select organization_code
            into l_org_code
            from mtl_parameters mp
           where mp.organization_id = l_org_id;

Line: 462

    SELECT  eng_types.type_name into l_change_type_code FROM ENG_CHANGE_ORDER_TYPES_VL eng_types WHERE eng_types.change_order_type_id =  l_change_type_code_num;

Line: 476

               update mtl_system_items_interface msii
                  set process_flag= 3
                where  msii.set_process_id = P_BATCH_ID
                  AND PROCESS_FLAG = l_process_flag
                  AND msii.ITEM_CATALOG_GROUP_ID =  l_icc_array(i)
--                  AND msii.TRANSACTION_TYPE = L_TRANSACTION_TYPE
                    ;

Line: 484

               select transaction_id
    bulk collect into l_transaction_array
                 from mtl_system_items_interface msii
                where msii.set_process_id = P_BATCH_ID
                  AND PROCESS_FLAG = 3
                  AND msii.ITEM_CATALOG_GROUP_ID =  l_icc_array(i)
--                  AND msii.TRANSACTION_TYPE = L_TRANSACTION_TYPE
                  ;

Line: 517

        UPDATE MTL_SYSTEM_ITEMS_INTERFACE
               set change_id = l_change_ids_array(count)
             WHERE ITEM_CATALOG_GROUP_ID = l_icc_array(count)
            AND ORGANIZATION_ID = l_org_array(count)
            AND set_process_id = p_batch_id
            AND PROCESS_FLAG = l_process_flag
--	          AND TRANSACTION_TYPE =L_TRANSACTION_TYPE
               ;

Line: 534

       select organization_code
         into l_org_code
         from mtl_parameters mp
        where mp.organization_id = l_org_id;

Line: 541

        select inventory_item_id ,transaction_id
         bulk collect   into l_item_ids_array,l_transaction_array
           FROM MTL_SYSTEM_ITEMS_INTERFACE
          where ITEM_CATALOG_GROUP_ID = l_icc_array(i)
            AND ORGANIZATION_ID = l_org_array(i)
            AND set_process_id = p_batch_id
            AND PROCESS_FLAG = l_process_flag
--            AND TRANSACTION_TYPE =L_TRANSACTION_TYPE
            ;

Line: 551

 SELECT  eng_types.type_name into l_change_type_code FROM ENG_CHANGE_ORDER_TYPES_VL eng_types WHERE eng_types.change_order_type_id =  l_change_type_code_num;

Line: 593

            UPDATE MTL_SYSTEM_ITEMS_INTERFACE
               set change_id = l_change_ids_array(count)
             WHERE ITEM_CATALOG_GROUP_ID = l_icc_array(i)
            AND ORGANIZATION_ID = l_org_array(i)
            AND set_process_id = p_batch_id
            AND PROCESS_FLAG = l_process_flag
--            AND TRANSACTION_TYPE =L_TRANSACTION_TYPE
            AND INVENTORY_ITEM_ID = l_item_ids_array(count);

Line: 605

  update mtl_system_items_interface  msii
     set change_line_id = ENG_CHANGE_LINES_S.nextval
     where msii.set_process_id = p_batch_id
  and msii.process_flag= l_process_flag
--  and msii.transaction_type=L_TRANSACTION_TYPE
  and change_id is not null;

Line: 613

insert into eng_change_lines
(  change_line_id              ,
  change_id                   ,
  created_by                  ,
  creation_date               ,
  last_updated_by             ,
  last_update_date            ,
  last_update_login           ,
  sequence_number             ,
  change_type_id              ,
  status_code                 ,
  APPROVAL_STATUS_TYPE
 )
(
select
msii.change_line_id,
msii.change_id,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
10,
(select change_order_type_id from eng_change_order_types where TYPE_CLASSIFICATION='LINE'
 AND CHANGE_MGMT_TYPE_CODE = 'NEW_ITEM_REQUEST' AND ROWNUM=1) TYPE_ID,
1 ,
1
from mtl_system_items_interface msii
where msii.set_process_id = p_batch_id
  and msii.process_flag = l_process_flag
--  and msii.transaction_type=L_TRANSACTION_TYPE
  );

Line: 649

         INSERT INTO eng_change_lines
          ( change_line_id              ,
            change_id                   ,
            created_by                  ,
            creation_date               ,
            last_updated_by             ,
            last_update_date            ,
            last_update_login           ,
            sequence_number             ,
            change_type_id              ,
            status_code                 ,
            APPROVAL_STATUS_TYPE
           )
          VALUES
           (
               cur_chg_line.change_line_id,
               cur_chg_line.change_id,
               cur_chg_line.created_by,
               cur_chg_line.creation_date,
               cur_chg_line.last_updated_by,
               cur_chg_line.last_update_date,
               cur_chg_line.last_update_login,
               l_nir_line_sequence,
               cur_chg_line.change_type_id,
               cur_chg_line.status_code,
               cur_chg_line.APPROVAL_STATUS_TYPE
           );

Line: 679

insert into eng_change_lines_tl
(  change_line_id              ,
  created_by                  ,
  creation_date               ,
  last_updated_by             ,
  last_update_date            ,
  last_update_login           ,
  language                    ,
  source_lang                 ,
  name
 )
(
select
msii.change_line_id,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
LANGS.LANGUAGE_CODE,
USERENV('LANG'),
msii.change_line_id
from mtl_system_items_interface msii,
     FND_LANGUAGES LANGS
where msii.set_process_id = p_batch_id
  and msii.process_flag = l_process_flag
--  and msii.transaction_type=L_TRANSACTION_TYPE
  AND LANGS.installed_flag IN ('B','I'));

Line: 708

SELECT subject_level, entity_name, parent_entity_name
BULK COLLECT INTO l_sub_desc_array
  FROM eng_subject_entities ese, eng_subjects_b esb
 WHERE ese.subject_id=esb.subject_id
   AND esb.subject_internal_name='EGO_NEW_ITEM'
   ORDER BY subject_level ;

Line: 717

l_dynamic_sql := 'insert into ENG_CHANGE_SUBJECTS '||
		'(change_subject_id , change_id , change_line_id , entity_name,';

Line: 731

		' last_updated_by, last_update_date, last_update_login)'
		|| ' (SELECT ENG_CHANGE_SUBJECTS_S.nextval , '||
		' MSII.CHANGE_ID, MSII.CHANGE_LINE_ID, ''' || l_sub_desc_array(sub_count).entity_name ||''',';