APPS.CSM_MTL_ITEM_SUBINV_EVENT_PKG SQL Statements

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

Line: 24

PROCEDURE INSERT_MTL_ITEM_SUBINV( p_organization_id IN number
                                 , p_user_id     IN number
		                               , p_last_run_date   IN date)
IS
TYPE inventory_item_tbl_typ  IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;

Line: 48

SELECT cila_acc.user_id, mis.inventory_item_id, mis.organization_id, mis.secondary_inventory
FROM csm_mtl_system_items_acc msi_acc,
     csm_inv_loc_ass_acc cila_acc,
     csp_inv_loc_assignments cila,
     mtl_item_sub_inventories mis
WHERE msi_acc.inventory_item_id = mis.inventory_item_id
AND msi_acc.organization_id = mis.organization_id
AND cila.csp_inv_loc_assignment_id = cila_acc.csp_inv_loc_assignment_id
AND cila.subinventory_code = mis.secondary_inventory
AND cila_acc.user_id = msi_acc.user_id
AND NOT EXISTS
    (SELECT 1
     FROM csm_mtl_item_subinv_acc acc
     WHERE acc.user_id = msi_acc.user_id
     AND acc.inventory_item_id = mis.inventory_item_id
     AND acc.organization_id = mis.organization_id
     AND acc.secondary_inventory = mis.secondary_inventory
     );

Line: 68

 CSM_UTIL_PKG.LOG('Entering CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV ',
                         'CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);

Line: 80

      INSERT INTO csm_mtl_item_subinv_acc(access_id, user_id, inventory_item_id, organization_id, secondary_inventory, counter,
                                           created_by, creation_date, last_updated_by, last_update_date, last_update_login)
                                    VALUES (l_access_id_tbl(i), p_user_id, l_inventory_items_tbl(i), l_organizations_tbl(i),l_sec_inventory_tbl(i), 1,
                                            fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);

Line: 85

   CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_item_subinv_acc for user ' || p_user_id ,
                         'CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV',FND_LOG.LEVEL_STATEMENT);

Line: 97

   l_access_id_tbl.delete;

Line: 101

/* --insert into acc
 FOR i IN 1..l_inventory_items_tbl.count LOOP
   CSM_ACC_PKG.Insert_Acc
     ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
      ,P_ACC_TABLE_NAME         => g_acc_table_name1
      ,P_SEQ_NAME               => g_acc_sequence_name1
      ,P_PK1_NAME               => g_pk1_name1
      ,P_PK1_NUM_VALUE          => l_inventory_items_tbl(i)
      ,P_PK2_NAME               => g_pk2_name1
      ,P_PK2_NUM_VALUE          => l_organizations_tbl(i)
      ,P_PK3_NAME               => g_pk3_name1
      ,P_PK3_CHAR_VALUE         => l_sec_inventory_tbl(i)
      ,P_USER_ID                => p_user_id
     );

Line: 118

 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV ',
                         'CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);

Line: 126

     CSM_UTIL_PKG.LOG('Exception in CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV: ' || l_sqlerrno || ':' || l_sqlerrmsg,
                         'CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV',FND_LOG.LEVEL_EXCEPTION);

Line: 128

END INSERT_MTL_ITEM_SUBINV;

Line: 131

PROCEDURE UPDATE_MTL_ITEM_SUBINV( p_organization_id IN number
                                 , p_user_id     IN number
		                               , p_last_run_date   IN date)
IS
TYPE access_id_tbl_typ  IS TABLE OF csm_mtl_system_items_acc.access_id%TYPE INDEX BY BINARY_INTEGER;

Line: 145

SELECT access_id
FROM csm_mtl_item_subinv_acc acc
,    mtl_item_sub_inventories mis
WHERE mis.inventory_item_id = acc.inventory_item_id
AND   mis.organization_id = acc.organization_id
AND   mis.secondary_inventory = acc.secondary_inventory
AND   mis.last_update_date  >= p_last_run_date
AND   acc.organization_id = p_organizationid
AND   acc.user_id = p_userid;

Line: 156

 CSM_UTIL_PKG.LOG('Entering CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV ',
                         'CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);

Line: 162

    l_access_id_tbl.DELETE;

Line: 172

         CSM_ACC_PKG.Update_Acc
               ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
                ,P_ACC_TABLE_NAME         => g_acc_table_name1
                ,P_ACCESS_ID              => l_access_id_tbl(i)
                ,P_USER_ID                => p_user_id
               );

Line: 180

   l_access_id_tbl.DELETE;

Line: 183

 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV ',
                         'CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);

Line: 191

     CSM_UTIL_PKG.LOG('Exception in CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV: ' || l_sqlerrno || ':' || l_sqlerrmsg,
                         'CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_EXCEPTION);

Line: 193

END UPDATE_MTL_ITEM_SUBINV;

Line: 196

PROCEDURE DELETE_MTL_ITEM_SUBINV( p_organization_id IN number
                                 , p_user_id     IN number
		                               , p_last_run_date   IN date)
IS
TYPE inventory_item_tbl_typ  IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;

Line: 212

SELECT acc.inventory_item_id, acc.organization_id, acc.secondary_inventory
FROM  csm_mtl_item_subinv_acc acc
WHERE acc.user_id = p_userid
AND   acc.organization_id = p_organizationid
AND   NOT EXISTS
     (SELECT 1
      FROM mtl_item_sub_inventories mis
      WHERE mis.inventory_item_id = acc.inventory_item_id
      AND mis.organization_id = acc.organization_id
      AND mis.secondary_inventory = acc.secondary_inventory
      );

Line: 225

 CSM_UTIL_PKG.LOG('Entering CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV ',
                         'CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);

Line: 229

    l_inventory_items_tbl.delete;

Line: 233

    l_organizations_tbl.delete;

Line: 237

    l_sec_inventory_tbl.delete;

Line: 248

    CSM_ACC_PKG.Delete_Acc
     ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
      ,P_ACC_TABLE_NAME         => g_acc_table_name1
      ,P_PK1_NAME               => g_pk1_name1
      ,P_PK1_NUM_VALUE          => l_inventory_items_tbl(i)
      ,P_PK2_NAME               => g_pk2_name1
      ,P_PK2_NUM_VALUE          => l_organizations_tbl(i)
      ,P_PK3_NAME               => g_pk3_name1
      ,P_PK3_CHAR_VALUE         => l_sec_inventory_tbl(i)
      ,P_USER_ID                => p_user_id
     );

Line: 263

 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV ',
                         'CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);

Line: 271

     CSM_UTIL_PKG.LOG('Exception in CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV: ' || l_sqlerrno || ':' || l_sqlerrmsg,
                         'CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_EXCEPTION);

Line: 273

END DELETE_MTL_ITEM_SUBINV;

Line: 280

l_prog_update_date jtm_con_request_data.last_run_date%TYPE;

Line: 294

SELECT nvl(last_run_date, (sysdate - 365*50))
FROM jtm_con_request_data
WHERE package_name = 'CSM_MTL_ITEM_SUBINV_EVENT_PKG'
AND procedure_name = 'REFRESH_ACC';

Line: 302

SELECT cila_acc.user_id, mis.inventory_item_id, mis.organization_id, mis.secondary_inventory
FROM csm_mtl_system_items_acc msi_acc,
     csm_inv_loc_ass_acc cila_acc,
     csp_inv_loc_assignments cila,
     mtl_item_sub_inventories mis
WHERE msi_acc.inventory_item_id = mis.inventory_item_id
AND msi_acc.organization_id = mis.organization_id
AND cila.csp_inv_loc_assignment_id = cila_acc.csp_inv_loc_assignment_id
AND cila.subinventory_code = mis.secondary_inventory
AND cila_acc.user_id = msi_acc.user_id
AND NOT EXISTS
    (SELECT 1
     FROM csm_mtl_item_subinv_acc acc
     WHERE acc.user_id = msi_acc.user_id
     AND acc.inventory_item_id = mis.inventory_item_id
     AND acc.organization_id = mis.organization_id
     AND acc.secondary_inventory = mis.secondary_inventory
     );

Line: 324

SELECT acc.access_id, acc.secondary_inventory, acc.organization_id,
       acc.inventory_item_id, acc.user_id
FROM csm_mtl_item_subinv_acc acc,
     mtl_item_sub_inventories mis
WHERE mis.inventory_item_id = acc.inventory_item_id
AND mis.secondary_inventory = acc.secondary_inventory
AND mis.organization_id = acc.organization_id
AND mis.last_update_date >= p_last_upd_date;

Line: 336

SELECT acc.access_id, acc.secondary_inventory, acc.organization_id,
       acc.inventory_item_id, acc.user_id
FROM csm_mtl_item_subinv_acc acc
WHERE NOT EXISTS
(SELECT 1
 FROM csm_mtl_system_items_acc msi_acc,
     csm_inv_loc_ass_acc cila_acc,
     csp_inv_loc_assignments cila,
     mtl_item_sub_inventories mis
 WHERE msi_acc.inventory_item_id = mis.inventory_item_id
 AND msi_acc.organization_id = mis.organization_id
 AND cila.csp_inv_loc_assignment_id = cila_acc.csp_inv_loc_assignment_id
 AND cila.subinventory_code = mis.secondary_inventory
 AND cila_acc.user_id = msi_acc.user_id
 AND msi_acc.user_id = acc.user_id
 AND msi_acc.inventory_item_id = acc.inventory_item_id
 AND msi_acc.organization_id = acc.organization_id
 AND acc.secondary_inventory = cila.subinventory_code
 );

Line: 358

SELECT user_id
FROM asg_user
WHERE resource_id = p_resourceid;

Line: 370

 FETCH l_last_run_date_csr INTO l_prog_update_date;

Line: 375

   CSM_ACC_PKG.Delete_acc
     ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
      ,P_ACC_TABLE_NAME         => g_acc_table_name1
      ,P_PK1_NAME               => g_pk1_name1
      ,P_PK1_NUM_VALUE          => r_del_mtl_item_subinv_rec.inventory_item_id
      ,P_PK2_NAME               => g_pk2_name1
      ,P_PK2_NUM_VALUE          => r_del_mtl_item_subinv_rec.organization_id
      ,P_PK3_NAME               => g_pk3_name1
      ,P_PK3_CHAR_VALUE         => r_del_mtl_item_subinv_rec.secondary_inventory
      ,P_USER_ID                => r_del_mtl_item_subinv_rec.user_id
      );

Line: 389

 FOR r_upd_mtl_item_subinv_rec IN l_upd_mtl_item_subinv_csr(l_prog_update_date) LOOP
   CSM_ACC_PKG.Update_acc
     ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
      ,P_ACC_TABLE_NAME         => g_acc_table_name1
      ,P_ACCESS_ID              => r_upd_mtl_item_subinv_rec.access_id
      ,P_USER_ID                => r_upd_mtl_item_subinv_rec.user_id
      );

Line: 400

     CSM_ACC_PKG.Insert_Acc
     ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
      ,P_ACC_TABLE_NAME         => g_acc_table_name1
      ,P_SEQ_NAME               => g_acc_sequence_name1
      ,P_PK1_NAME               => g_pk1_name1
      ,P_PK1_NUM_VALUE          => r_ins_mtl_item_subinv_rec.inventory_item_id
      ,P_PK2_NAME               => g_pk2_name1
      ,P_PK2_NUM_VALUE          => r_ins_mtl_item_subinv_rec.organization_id
      ,P_PK3_NAME               => g_pk3_name1
      ,P_PK3_CHAR_VALUE         => r_ins_mtl_item_subinv_rec.secondary_inventory
      ,P_USER_ID                => r_ins_mtl_item_subinv_rec.user_id
     );

Line: 415

 UPDATE jtm_con_request_data
 SET last_run_date = l_run_date
 WHERE package_name = 'CSM_MTL_ITEM_SUBINV_EVENT_PKG'
 AND procedure_name = 'REFRESH_ACC';