APPS.CSM_MTL_SEC_LOCATORS_EVENT_PKG SQL Statements

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

Line: 28

l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;

Line: 29

l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;

Line: 32

l_prog_update_date jtm_con_request_data.last_run_date%TYPE;

Line: 46

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

Line: 54

SELECT msi_acc.user_id, msl.inventory_item_id,
      msl.organization_id, msl.secondary_locator
FROM csm_mtl_system_items_acc msi_acc,
     csm_mtl_item_locations_acc mil_acc,
     mtl_secondary_locators msl
WHERE msl.inventory_item_id = msi_acc.inventory_item_id
AND msl.organization_id = msi_acc.organization_id
AND msl.secondary_locator = mil_acc.inventory_location_id
AND msl.organization_id = mil_acc.organization_id
AND msi_acc.user_id = mil_acc.user_id
AND NOT EXISTS
(SELECT 1
 FROM csm_mtl_secondary_locators_acc acc
 WHERE acc.user_id = msi_acc.user_id
 AND acc.inventory_item_id = msi_acc.inventory_item_id
 AND acc.organization_id = msi_acc.organization_id
 AND acc.secondary_locator = msl.secondary_locator
)
UNION ALL
SELECT mil_acc.user_id, msl.inventory_item_id,
      msl.organization_id, msl.secondary_locator
FROM csm_mtl_system_items_acc msi_acc,
     csm_mtl_item_locations_acc mil_acc,
     mtl_secondary_locators msl,
     csm_user_inventory_org uorg
WHERE msl.inventory_item_id = msi_acc.inventory_item_id
AND msl.organization_id = msi_acc.organization_id
AND msl.secondary_locator = mil_acc.inventory_location_id
AND msl.organization_id = mil_acc.organization_id
AND msi_acc.user_id = -1
AND mil_acc.user_id = uorg.user_id
AND msi_acc.organization_id=uorg.organization_id
AND NOT EXISTS(SELECT 1
 FROM csm_mtl_secondary_locators_acc acc
 WHERE acc.user_id = mil_acc.user_id
 AND acc.inventory_item_id = msl.inventory_item_id
 AND acc.organization_id   = msl.organization_id
 AND acc.secondary_locator = msl.secondary_locator
);

Line: 97

SELECT acc.access_id, acc.secondary_locator, acc.organization_id,
       acc.inventory_item_id, acc.user_id
FROM csm_mtl_secondary_locators_acc acc,
     mtl_secondary_locators msl
WHERE msl.secondary_locator = acc.secondary_locator
AND msl.organization_id = acc.organization_id
AND msl.inventory_item_id = acc.inventory_item_id
AND msl.last_update_date >= p_last_upd_date;

Line: 109

SELECT acc.user_id, acc.secondary_locator, acc.organization_id, acc.inventory_item_id
FROM csm_mtl_secondary_locators_acc acc
WHERE NOT EXISTS
((SELECT 1
 FROM csm_mtl_system_items_acc msi_acc,
      csm_mtl_item_locations_acc mil_acc,
      mtl_secondary_locators msl
 WHERE msl.inventory_item_id = msi_acc.inventory_item_id
 AND msl.organization_id = msi_acc.organization_id
 AND msl.secondary_locator = mil_acc.inventory_location_id
 AND msl.organization_id = mil_acc.organization_id
 AND msi_acc.user_id = mil_acc.user_id
 AND acc.secondary_locator = msl.secondary_locator
 AND acc.organization_id = msl.organization_id
 AND acc.inventory_item_id = msl.inventory_item_id
 AND acc.user_id = msi_acc.user_id
 )
 UNION ALL
(SELECT 1
 FROM csm_mtl_system_items_acc msi_acc,
      csm_mtl_item_locations_acc mil_acc,
      mtl_secondary_locators msl,
     csm_user_inventory_org uorg
 WHERE msl.inventory_item_id = msi_acc.inventory_item_id
 AND msl.organization_id = msi_acc.organization_id
 AND msl.secondary_locator = mil_acc.inventory_location_id
 AND msl.organization_id = mil_acc.organization_id
 AND acc.user_id = mil_acc.user_id
 AND acc.secondary_locator = msl.secondary_locator
 AND acc.organization_id = msl.organization_id
 AND acc.inventory_item_id = msl.inventory_item_id
 AND acc.user_id = uorg.user_id
 AND msi_acc.USER_ID = -1
 AND acc.organization_id = uorg.organization_id
 ));

Line: 151

 FETCH l_last_run_date_csr INTO l_prog_update_date;

Line: 156

   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_mtl_sec_loc_del_rec.inventory_item_id
      ,P_PK2_NAME               => g_pk2_name1
      ,P_PK2_NUM_VALUE          => r_mtl_sec_loc_del_rec.secondary_locator
      ,P_PK3_NAME               => g_pk3_name1
      ,P_PK3_NUM_VALUE          => r_mtl_sec_loc_del_rec.organization_id
      ,P_USER_ID                => r_mtl_sec_loc_del_rec.user_id
      );

Line: 170

 FOR r_mtl_sec_loc_upd_rec IN l_mtl_sec_loc_upd_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_mtl_sec_loc_upd_rec.access_id
      ,P_USER_ID                 => r_mtl_sec_loc_upd_rec.user_id
      );

Line: 181

     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_mtl_sec_loc_ins_rec.inventory_item_id
      ,P_PK2_NAME               => g_pk2_name1
      ,P_PK2_NUM_VALUE          => r_mtl_sec_loc_ins_rec.secondary_locator
      ,P_PK3_NAME               => g_pk3_name1
      ,P_PK3_NUM_VALUE          => r_mtl_sec_loc_ins_rec.organization_id
      ,P_USER_ID                => r_mtl_sec_loc_ins_rec.user_id
     );

Line: 196

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