[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_SERIAL_NUMBERS( p_resource_id IN NUMBER )
IS
TYPE item_Tab IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
, 'Entering INSERT_SERIAL_NUMBERS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'Inserting records for resource: '||p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*Block insert every item from given subinventory/org not yet in acc table*/
SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, SERIAL_NUMBER, p_resource_id
BULK COLLECT INTO sequences, items, serials, resources
FROM MTL_SERIAL_NUMBERS
WHERE CURRENT_STATUS IN (1,3)
AND ( CURRENT_SUBINVENTORY_CODE, CURRENT_ORGANIZATION_ID ) IN (
SELECT SUBINVENTORY_CODE
, ORGANIZATION_ID
FROM CSP_INV_LOC_ASSIGNMENTS
WHERE RESOURCE_ID = p_resource_id
AND RESOURCE_TYPE = 'RS_EMPLOYEE'
AND SYSDATE BETWEEN NVL( EFFECTIVE_DATE_START, SYSDATE )
AND NVL( EFFECTIVE_DATE_END , SYSDATE ))
AND ( INVENTORY_ITEM_ID, SERIAL_NUMBER ) NOT IN (
SELECT INVENTORY_ITEM_ID, SERIAL_NUMBER
FROM JTM_MTL_SERIAL_NUMBERS_ACC
WHERE RESOURCE_ID = p_resource_id );
INSERT INTO JTM_MTL_SERIAL_NUMBERS_ACC(
ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY
, COUNTER, RESOURCE_ID, INVENTORY_ITEM_ID, SERIAL_NUMBER ) VALUES (
sequences(i), sysdate, 1, sysdate, 1, 1, p_resource_id, items(i), serials(i));
, 'Pushing ' || sequences.COUNT || ' inserted record(s) to resource: '||p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
, 'Leaving INSERT_SERIAL_NUMBERS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'INSERT_SERIAL_NUMBERS'||fnd_global.local_chr(10)||
'Error: '||sqlerrm
, JTM_HOOK_UTIL_PKG.g_debug_level_error);
END INSERT_SERIAL_NUMBERS;
PROCEDURE DELETE_SERIAL_NUMBERS
IS
l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
/*Delete all serial numbers from acc table for which the assigned subinventory is no more,
or for which the status has changed*/
CURSOR c_remove IS
/* Performance bug (3920090)fixing */
/*
SELECT acc.ACCESS_ID, acc.RESOURCE_ID
FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
, MTL_SERIAL_NUMBERS msn
WHERE msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
AND msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
AND ( msn.CURRENT_SUBINVENTORY_CODE, msn.CURRENT_ORGANIZATION_ID ) NOT IN (
SELECT SUBINVENTORY_CODE
, ORGANIZATION_ID
FROM CSP_INV_LOC_ASSIGNMENTS
WHERE RESOURCE_ID = acc.RESOURCE_ID
AND SYSDATE BETWEEN NVL( EFFECTIVE_DATE_START, SYSDATE )
AND NVL( EFFECTIVE_DATE_END , SYSDATE ))
UNION
SELECT acc.ACCESS_ID, acc.RESOURCE_ID
FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
, MTL_SERIAL_NUMBERS msn
WHERE msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
AND msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
AND msn.CURRENT_STATUS NOT IN (1,3);
SELECT /*+ INDEX (msn MTL_SERIAL_NUMBERS_U1) */
acc.ACCESS_ID, acc.RESOURCE_ID
FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
, MTL_SERIAL_NUMBERS msn
WHERE msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
AND msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
AND (NOT EXISTS
(SELECT 1
FROM CSP_INV_LOC_ASSIGNMENTS cila
WHERE cila.RESOURCE_ID = acc.RESOURCE_ID
AND cila.organization_id = msn.current_organization_id
AND cila.subinventory_code = msn.current_subinventory_code
AND SYSDATE BETWEEN NVL( cila.EFFECTIVE_DATE_START, SYSDATE )
AND NVL( cila.EFFECTIVE_DATE_END , SYSDATE)
)
OR msn.CURRENT_STATUS NOT IN (1,3)
);
, 'Entering DELETE_SERIAL_NUMBERS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
/*Delete not used records*/
OPEN c_remove;
/*To avoid a mismatch only delete records which are marked dirty*/
FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
DELETE JTM_MTL_SERIAL_NUMBERS_ACC
WHERE ACCESS_ID = l_tab_access_id(i);
, 'Leaving DELETE_SERIAL_NUMBERS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'DELETE_SERIAL_NUMBERS'||fnd_global.local_chr(10)||
'Error: '||sqlerrm
, JTM_HOOK_UTIL_PKG.g_debug_level_error);
END DELETE_SERIAL_NUMBERS;
PROCEDURE UPDATE_SERIAL_NUMBERS( p_date IN DATE )
IS
/*Get all existing and valid records which are changed*/
CURSOR c_changed( b_date DATE ) IS
SELECT acc.ACCESS_ID, acc.RESOURCE_ID
FROM JTM_MTL_SERIAL_NUMBERS_ACC acc
, MTL_SERIAL_NUMBERS msn
WHERE msn.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
AND msn.SERIAL_NUMBER = acc.SERIAL_NUMBER
AND msn.LAST_UPDATE_DATE >= p_date
AND ( msn.CURRENT_SUBINVENTORY_CODE, msn.CURRENT_ORGANIZATION_ID ) IN (
SELECT SUBINVENTORY_CODE
, ORGANIZATION_ID
FROM CSP_INV_LOC_ASSIGNMENTS
WHERE RESOURCE_ID = acc.RESOURCE_ID
AND SYSDATE BETWEEN NVL( EFFECTIVE_DATE_START, SYSDATE )
AND NVL( EFFECTIVE_DATE_END , SYSDATE ));
, 'Entering UPDATE_SERIAL_NUMBERS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'Leaving UPDATE_SERIAL_NUMBERS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'UPDATE_SERIAL_NUMBERS'||fnd_global.local_chr(10)||
'Error: '||sqlerrm
, JTM_HOOK_UTIL_PKG.g_debug_level_error);
END UPDATE_SERIAL_NUMBERS;
SELECT res.resource_id
FROM asg_pub pub
, asg_pub_responsibility pubresp
, fnd_user_resp_groups usrresp
, fnd_user usr
, jtf_rs_resource_extns res
, asg_user au
WHERE res.resource_id = au.resource_id
AND pub.name = 'SERVICEL'
AND pub.enabled='Y'
AND pub.status='Y'
AND pub.pub_id = pubresp.pub_id
AND pubresp.responsibility_id = usrresp.responsibility_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usrresp.start_date,sysdate))
AND TRUNC(NVL(usrresp.end_date,sysdate))
AND usrresp.user_id = usr.user_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usr.start_date,sysdate))
AND TRUNC(NVL(usr.end_date,sysdate))
AND usr.user_id = res.user_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(res.start_date_active,sysdate))
AND TRUNC(NVL(res.end_date_active,sysdate));
select LAST_RUN_DATE
from JTM_CON_REQUEST_DATA
where package_name = 'CSL_MTL_SERIAL_NUMBERS_ACC_PKG'
AND procedure_name = 'CON_REQUEST_SERIAL_NUMBERS';
/*Update the last run date*/
UPDATE JTM_CON_REQUEST_DATA
SET LAST_RUN_DATE = SYSDATE
WHERE package_name = 'CSL_MTL_SERIAL_NUMBERS_ACC_PKG'
AND procedure_name = 'CON_REQUEST_SERIAL_NUMBERS';
DELETE_SERIAL_NUMBERS;
/*Second, check for updated serial numbers*/
UPDATE_SERIAL_NUMBERS( l_date);
/*Third, insert all serial numbers that are not yet in the acc table*/
INSERT_SERIAL_NUMBERS( r_mobile_user.resource_id );