DBA Data[Home] [Help]

APPS.CSM_MTL_SYSTEM_ITEMS_EVENT_PKG SQL Statements

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

Line: 25

PROCEDURE insert_mtl_system_items( p_user_id     IN NUMBER,
                                   p_organization_id IN NUMBER,
                                   p_category_set_id IN NUMBER,
                                   p_category_id IN NUMBER,
                                   p_last_run_date IN DATE,
                                   p_changed IN VARCHAR2)
IS
l_run_date 		DATE;
Line: 54

SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
FROM   MTL_SYSTEM_ITEMS_B msi
WHERE  msi.organization_id = b_organization_id
AND    (b_changed = 'Y'
            OR msi.creation_date >= NVL(b_last_run_date, msi.creation_date))
AND    NOT EXISTS
	   ( SELECT 1
  	   FROM  csm_mtl_system_items_acc acc
  	   WHERE user_id = b_user_id
  	   AND 	 acc.inventory_item_id = msi.inventory_item_id
  	   AND 	 acc.organization_id   = msi.organization_id);
Line: 70

SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
FROM mtl_system_items_b msi
WHERE NOT EXISTS
(SELECT 1
 FROM csm_mtl_system_items_acc acc
 WHERE user_id = b_user_id
 AND acc.inventory_item_id = msi.inventory_item_id
 AND acc.organization_id = msi.organization_id
 )
AND msi.organization_id = b_organization_id
AND msi.inventory_item_id IN
(SELECT itemcat.inventory_item_id
 FROM   mtl_item_categories itemcat
 WHERE  itemcat.category_id = b_category_id
 AND    itemcat.organization_id = b_organization_id
 AND    (b_changed = 'Y'
         OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)
         )
 );
Line: 94

SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
FROM mtl_system_items_b msi
WHERE NOT EXISTS
(SELECT 1
 FROM csm_mtl_system_items_acc acc
 WHERE user_id = b_user_id
 AND acc.inventory_item_id = msi.inventory_item_id
 AND acc.organization_id = msi.organization_id
 )
AND msi.organization_id = b_organization_id
AND msi.inventory_item_id IN
(SELECT itemcat.inventory_item_id
 FROM   mtl_item_categories itemcat
 WHERE  itemcat.category_set_id = b_category_set_id
 AND    itemcat.organization_id = b_organization_id
 AND    (b_changed = 'Y'
         OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)
         )
 );
Line: 118

SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
FROM mtl_system_items_b msi
WHERE NOT EXISTS
(SELECT 1
 FROM csm_mtl_system_items_acc acc
 WHERE user_id = b_user_id
 AND acc.inventory_item_id = msi.inventory_item_id
 AND acc.organization_id = msi.organization_id
 )
AND msi.organization_id = b_organization_id
AND msi.inventory_item_id IN
(SELECT itemcat.inventory_item_id
 FROM   mtl_item_categories itemcat
 WHERE  itemcat.category_set_id = b_category_set_id
 AND    itemcat.category_id = b_category_id
 AND    itemcat.organization_id = b_organization_id
 AND    (b_changed = 'Y'
         OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)
         )
 );
Line: 140

  CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
Line: 150

      l_stmt := 'UPDATE csm_mtl_system_items_acc';
Line: 152

      l_stmt :=   l_stmt || '  ,   last_update_date = SYSDATE';
Line: 153

      l_stmt :=   l_stmt || '  ,   last_updated_by = 1';
Line: 173

        l_stmt :=   l_stmt || '     (SELECT inventory_item_id';
Line: 185

  ELSE  -- if p_changed = 'N', delete items that are no longer assigned to the category/category set
      -- changed to dynamic sql to support either category or category set
      l_stmt :=  NULL;
Line: 188

      l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
Line: 190

      l_stmt :=   l_stmt || '  ,   last_update_date = SYSDATE';
Line: 191

      l_stmt :=   l_stmt || '  ,   last_updated_by = 1';
Line: 210

        l_stmt :=   l_stmt || ' (SELECT 1';
Line: 216

        l_stmt :=   l_stmt || '  SELECT 1';
Line: 226

        l_tab_access_id.DELETE;
Line: 227

        l_tab_user_id.DELETE;
Line: 229

        SELECT access_id, user_id
        BULK COLLECT INTO l_tab_access_id, l_tab_user_id
        FROM csm_mtl_system_items_acc acc
        WHERE acc.counter = 0;
Line: 245

                  DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
Line: 258

        l_tab_access_id.DELETE;
Line: 259

        l_inventory_item_id_tbl.DELETE;
Line: 260

        l_organization_id_tbl.DELETE;
Line: 261

        l_tab_user_id.DELETE;
Line: 268

         CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
                         || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
Line: 273

           INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
                       created_by, creation_date, last_updated_by, last_update_date, last_update_login)
                VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
                       fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
Line: 299

        l_tab_access_id.DELETE;
Line: 300

        l_inventory_item_id_tbl.DELETE;
Line: 301

        l_organization_id_tbl.DELETE;
Line: 302

        l_tab_user_id.DELETE;
Line: 309

        CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
                         || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
Line: 314

           INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
                       created_by, creation_date, last_updated_by, last_update_date, last_update_login)
                VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
                       fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
Line: 340

        l_tab_access_id.DELETE;
Line: 341

        l_inventory_item_id_tbl.DELETE;
Line: 342

        l_organization_id_tbl.DELETE;
Line: 343

        l_tab_user_id.DELETE;
Line: 350

        CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
                         || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
Line: 355

           INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
                       created_by, creation_date, last_updated_by, last_update_date, last_update_login)
                VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
                       fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
Line: 381

        l_tab_access_id.DELETE;
Line: 382

        l_inventory_item_id_tbl.DELETE;
Line: 383

        l_organization_id_tbl.DELETE;
Line: 384

        l_tab_user_id.DELETE;
Line: 391

        CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
                         || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
Line: 396

           INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
                       created_by, creation_date, last_updated_by, last_update_date, last_update_login)
                VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
                       fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
Line: 418

 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
Line: 424

     l_error_msg := ' Exception in  INSERT_MTL_SYSTEM_ITEMS for user_id :'
                       || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
Line: 426

     CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
Line: 428

END INSERT_MTL_SYSTEM_ITEMS;
Line: 430

PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)
IS
l_sqlerrno      VARCHAR2(20);
Line: 440

l_max_last_update_date_b  DATE;
Line: 441

l_max_last_update_date_tl DATE;
Line: 445

SELECT /*+ INDEX (acc CSM_MTL_SYSTEM_ITEMS_ACC_U1) INDEX(msi MTL_SYSTEM_ITEMS_B_U1) */
       acc.access_id, acc.user_id
FROM   csm_mtl_system_items_acc acc, mtl_system_items_b msi
WHERE  msi.inventory_item_id = acc.inventory_item_id
AND    msi.organization_id   = acc.organization_id
AND    (msi.last_update_date >= b_last_date);
Line: 453

 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
Line: 458

    /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
    /* , MTL_SYSTEM_ITEMS_TL which were custom created */
    SELECT MAX(last_update_date) INTO l_max_last_update_date_b
    FROM mtl_system_items_b;
Line: 462

    IF( l_max_last_update_date_b < p_last_run_date) THEN
       SELECT MAX(last_update_date) INTO l_max_last_update_date_tl
       FROM mtl_system_items_tl;
Line: 465

       IF(l_max_last_update_date_tl < p_last_run_date) THEN
            -- No updates
            CSM_UTIL_PKG.LOG('Leaving UPDATE_MTL_SYSTEM_ITEMS - No Updates ',
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
Line: 475

       l_tab_access_id.DELETE;
Line: 476

       l_tab_user_id.DELETE;
Line: 483

                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
Line: 497

 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
Line: 503

        l_error_msg := ' Exception in  UPDATE_MTL_SYSTEM_ITEMS :' || l_sqlerrno || ':' || l_sqlerrmsg;
Line: 504

        CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
Line: 506

END UPDATE_MTL_SYSTEM_ITEMS;
Line: 508

PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,
                                  p_organization_id IN NUMBER,
                                  p_category_set_id IN NUMBER,
                                  p_category_id IN NUMBER)
IS
l_sqlerrno 		 VARCHAR2(20);
Line: 525

 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
Line: 530

 l_tab_access_id.DELETE;
Line: 532

    l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
Line: 534

    l_stmt :=   l_stmt || '  ,   LAST_UPDATE_DATE = SYSDATE';
Line: 535

    l_stmt :=   l_stmt || '  ,   last_updated_by = nvl(fnd_global.user_id, 1)';
Line: 554

  	  l_stmt :=   l_stmt || '  SELECT 1 ';
Line: 568

    l_tab_access_id.DELETE;
Line: 569

    l_tab_user_id.DELETE;
Line: 571

    SELECT access_id, user_id
    BULK COLLECT INTO l_tab_access_id, l_tab_user_id
    FROM csm_mtl_system_items_acc acc
    WHERE acc.counter = 0;
Line: 587

                 DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
Line: 590

 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
Line: 596

     CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
Line: 599

END DELETE_MTL_SYSTEM_ITEMS;
Line: 617

SELECT organization_id, category_set_id, category_id
FROM csm_user_inventory_org
WHERE user_id = b_user_id
FOR UPDATE;
Line: 633

                          || ' - Inserting all mtl_system_items',
                         'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
Line: 638

   INSERT INTO csm_user_inventory_org (
      user_id, organization_id, last_update_date, last_updated_by,
      creation_date, created_by, category_set_id, category_id )
   VALUES (
      p_user_id, p_organization_id, SYSDATE, 1, SYSDATE, 1,
      p_category_set_id, p_category_id );
Line: 682

                  csm_system_item_event_pkg.delete_system_items(p_user_id=>p_user_id,
                                                                p_organization_id=>r_org.organization_id);
Line: 689

           delete_mtl_system_items(p_user_id=>p_user_id,
                                   p_organization_id=>r_org.organization_id,
                                   p_category_set_id=>r_org.category_set_id,
                                   p_category_id=>r_org.category_id);
Line: 694

           CSM_UTIL_PKG.LOG('Inserting records for new profile settings for user_id: ' || TO_CHAR(p_user_id),
                            'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
Line: 697

           insert_mtl_system_items(p_user_id=>p_user_id,
                           p_organization_id=>p_organization_id,
                           p_category_set_id=>p_category_set_id,
                           p_category_id=>p_category_id,
                           p_last_run_date=>NULL,
                           p_changed=>'Y');
Line: 704

           CSM_UTIL_PKG.LOG('Update csm_user_inventory_org with new profile settings for user_id: ' || TO_CHAR(p_user_id),
                            'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
Line: 707

           UPDATE csm_user_inventory_org
           SET organization_id = p_organization_id
           ,   category_set_id = p_category_set_id
           ,   category_id     = p_category_id
           ,   last_update_date = SYSDATE
           WHERE CURRENT OF c_org;
Line: 720

           insert_mtl_system_items(p_user_id=>p_user_id,
                           p_organization_id=>p_organization_id,
                           p_category_set_id=>p_category_set_id,
                           p_category_id=>p_category_id,
                           p_last_run_date=>p_last_run_date,
                           p_changed=>'N');
Line: 746

l_prog_update_date      jtm_con_request_data.last_run_date%TYPE;
Line: 759

SELECT NVL(last_run_date, TO_DATE('1','J'))
FROM jtm_con_request_data
WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
Line: 772

 FETCH l_last_run_date_csr INTO l_prog_update_date;
Line: 776

 update_mtl_system_items(p_last_run_date => l_prog_update_date);
Line: 793

                           p_last_run_date=>l_prog_update_date);
Line: 798

 UPDATE jtm_con_request_data
 SET last_run_date = l_run_date
 WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
 AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
Line: 832

 insert_mtl_system_items(p_user_id=>p_user_id,
                         p_organization_id=>p_organization_id,
                         p_category_set_id=>p_category_set_id,
                         p_category_id=>p_category_id,
                         p_last_run_date=>NULL,
                         p_changed=>'N'); -- new user, no profiles are changed
Line: 864

     CSM_ACC_PKG.Insert_Acc
    ( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name
     ,P_ACC_TABLE_NAME         => g_mtl_sys_items_acc_table_name
     ,P_SEQ_NAME               => g_mtl_sys_items_seq_name
     ,P_PK1_NAME               => g_mtl_sys_items_pk1_name
     ,P_PK1_NUM_VALUE          => p_inventory_item_id
     ,P_PK2_NAME               => g_mtl_sys_items_pk2_name
     ,P_PK2_NUM_VALUE          => p_organization_id
     ,P_USER_ID                => p_user_id
    );
Line: 900

    CSM_ACC_PKG.Delete_Acc
   ( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name
   ,P_ACC_TABLE_NAME         => g_mtl_sys_items_acc_table_name
   ,P_PK1_NAME               => g_mtl_sys_items_pk1_name
   ,P_PK1_NUM_VALUE          => p_inventory_item_id
   ,P_PK2_NAME               => g_mtl_sys_items_pk2_name
   ,P_PK2_NUM_VALUE          => p_organization_id
   ,P_USER_ID                => p_user_id
   );