eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CSL_MTL_ITEM_LOCATIONS_ACC_PKG dependencies on MTL_ITEM_LOCATIONS

Line 1: PACKAGE BODY CSL_MTL_ITEM_LOCATIONS_ACC_PKG AS

1: PACKAGE BODY CSL_MTL_ITEM_LOCATIONS_ACC_PKG AS
2: /* $Header: cslmlacb.pls 115.1 2003/10/24 23:35:06 yliao noship $ */
3:
4: /*
5: This package will be called from CSL_CSP_INV_LOC_ASS_ACC_PKG.

Line 8: mtl_item_locations records associated with this sub-inventory,

4: /*
5: This package will be called from CSL_CSP_INV_LOC_ASS_ACC_PKG.
6: Pre_Insert_Child(..).
7: When assigning a sub-inventory to a resource, we check the
8: mtl_item_locations records associated with this sub-inventory,
9: and insert them into the CSL_MTL_ITEM_LOCATIONS_ACC table.
10: Same check applies for deletion of system items.
11:
12: We also need functions to be called for upgrade of exsting users

Line 9: and insert them into the CSL_MTL_ITEM_LOCATIONS_ACC table.

5: This package will be called from CSL_CSP_INV_LOC_ASS_ACC_PKG.
6: Pre_Insert_Child(..).
7: When assigning a sub-inventory to a resource, we check the
8: mtl_item_locations records associated with this sub-inventory,
9: and insert them into the CSL_MTL_ITEM_LOCATIONS_ACC table.
10: Same check applies for deletion of system items.
11:
12: We also need functions to be called for upgrade of exsting users
13: without new subinventory assignments.

Line 17: g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_MTL_ITEM_LOCATIONS_ACC';

13: without new subinventory assignments.
14: */
15:
16: /*** Globals ***/
17: g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_MTL_ITEM_LOCATIONS_ACC';
18: g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
19: JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_ITEM_LOCATIONS');
20: g_table_name CONSTANT VARCHAR2(30) := 'MTL_ITEM_LOCATIONS';
21: g_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_LOCATION_ID';

Line 19: JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_ITEM_LOCATIONS');

15:
16: /*** Globals ***/
17: g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_MTL_ITEM_LOCATIONS_ACC';
18: g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
19: JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_ITEM_LOCATIONS');
20: g_table_name CONSTANT VARCHAR2(30) := 'MTL_ITEM_LOCATIONS';
21: g_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_LOCATION_ID';
22: g_pk2_name CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
23: g_debug_level NUMBER; -- debug level

Line 20: g_table_name CONSTANT VARCHAR2(30) := 'MTL_ITEM_LOCATIONS';

16: /*** Globals ***/
17: g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_MTL_ITEM_LOCATIONS_ACC';
18: g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
19: JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_ITEM_LOCATIONS');
20: g_table_name CONSTANT VARCHAR2(30) := 'MTL_ITEM_LOCATIONS';
21: g_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_LOCATION_ID';
22: g_pk2_name CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
23: g_debug_level NUMBER; -- debug level
24:

Line 33: FROM CSL_MTL_ITEM_LOCATIONS_ACC acc

29: PROCEDURE UPDATE_ACC_REC_MARKDIRTY( p_last_run_date IN DATE )
30: IS
31: CURSOR c_changed( b_last_date DATE ) IS
32: SELECT acc.ACCESS_ID, acc.RESOURCE_ID
33: FROM CSL_MTL_ITEM_LOCATIONS_ACC acc
34: , MTL_ITEM_LOCATIONS b
35: , ASG_USER au
36: WHERE b.INVENTORY_LOCATION_ID = acc.INVENTORY_LOCATION_ID
37: AND b.ORGANIZATION_ID = acc.ORGANIZATION_ID

Line 34: , MTL_ITEM_LOCATIONS b

30: IS
31: CURSOR c_changed( b_last_date DATE ) IS
32: SELECT acc.ACCESS_ID, acc.RESOURCE_ID
33: FROM CSL_MTL_ITEM_LOCATIONS_ACC acc
34: , MTL_ITEM_LOCATIONS b
35: , ASG_USER au
36: WHERE b.INVENTORY_LOCATION_ID = acc.INVENTORY_LOCATION_ID
37: AND b.ORGANIZATION_ID = acc.ORGANIZATION_ID
38: AND au.RESOURCE_ID = acc.RESOURCE_ID

Line 107: FROM JTM_MTL_SEC_INV_ACC SEC, MTL_ITEM_LOCATIONS LOC

103: PROCEDURE INSERT_ACC_REC_MARKDIRTY( p_last_run_date IN DATE )
104: IS
105: CURSOR c_inserted( b_last_date DATE ) IS
106: SELECT CSL_ACC_SEQUENCE.NEXTVAL, SEC.RESOURCE_ID, LOC.INVENTORY_LOCATION_ID, LOC.ORGANIZATION_ID, SEC.COUNTER
107: FROM JTM_MTL_SEC_INV_ACC SEC, MTL_ITEM_LOCATIONS LOC
108: WHERE SEC.SECONDARY_INVENTORY_NAME = LOC.SUBINVENTORY_CODE
109: AND SEC.ORGANIZATION_ID = LOC.ORGANIZATION_ID
110: AND LOC.CREATION_DATE >= NVL(b_last_date, LOC.CREATION_DATE)
111: AND (SEC.RESOURCE_ID, LOC.INVENTORY_LOCATION_ID, LOC.ORGANIZATION_ID)

Line 114: FROM CSL_MTL_ITEM_LOCATIONS_ACC

110: AND LOC.CREATION_DATE >= NVL(b_last_date, LOC.CREATION_DATE)
111: AND (SEC.RESOURCE_ID, LOC.INVENTORY_LOCATION_ID, LOC.ORGANIZATION_ID)
112: NOT IN
113: ( SELECT RESOURCE_ID, INVENTORY_LOCATION_ID, ORGANIZATION_ID
114: FROM CSL_MTL_ITEM_LOCATIONS_ACC
115: );
116:
117: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
118: l_tab_resource_id ASG_DOWNLOAD.USER_LIST;

Line 119: TYPE location_Tab IS TABLE OF MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;

115: );
116:
117: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
118: l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
119: TYPE location_Tab IS TABLE OF MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
120: TYPE org_Tab IS TABLE OF MTL_ITEM_LOCATIONS.ORGANIZATION_ID%TYPE INDEX BY BINARY_INTEGER;
121: TYPE counter_Tab IS TABLE OF JTM_MTL_SEC_INV_ACC.COUNTER%TYPE INDEX BY BINARY_INTEGER;
122: locations location_Tab;
123: organizations org_Tab;

Line 120: TYPE org_Tab IS TABLE OF MTL_ITEM_LOCATIONS.ORGANIZATION_ID%TYPE INDEX BY BINARY_INTEGER;

116:
117: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
118: l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
119: TYPE location_Tab IS TABLE OF MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
120: TYPE org_Tab IS TABLE OF MTL_ITEM_LOCATIONS.ORGANIZATION_ID%TYPE INDEX BY BINARY_INTEGER;
121: TYPE counter_Tab IS TABLE OF JTM_MTL_SEC_INV_ACC.COUNTER%TYPE INDEX BY BINARY_INTEGER;
122: locations location_Tab;
123: organizations org_Tab;
124: counters counter_Tab;

Line 139: UPDATE CSL_MTL_ITEM_LOCATIONS_ACC

135: );
136: END IF;
137:
138: /*Increment count if the record already exists */
139: UPDATE CSL_MTL_ITEM_LOCATIONS_ACC
140: SET COUNTER = COUNTER + 1
141: , LAST_UPDATE_DATE = SYSDATE
142: , LAST_UPDATED_BY = 1
143: WHERE ( RESOURCE_ID, INVENTORY_LOCATION_ID, ORGANIZATION_ID ) IN

Line 145: FROM JTM_MTL_SEC_INV_ACC SEC, MTL_ITEM_LOCATIONS LOC

141: , LAST_UPDATE_DATE = SYSDATE
142: , LAST_UPDATED_BY = 1
143: WHERE ( RESOURCE_ID, INVENTORY_LOCATION_ID, ORGANIZATION_ID ) IN
144: ( SELECT SEC.RESOURCE_ID, LOC.INVENTORY_LOCATION_ID, LOC.ORGANIZATION_ID
145: FROM JTM_MTL_SEC_INV_ACC SEC, MTL_ITEM_LOCATIONS LOC
146: WHERE SEC.SECONDARY_INVENTORY_NAME = LOC.SUBINVENTORY_CODE
147: AND LOC.CREATION_DATE >= NVL(p_last_run_date, LOC.CREATION_DATE)
148: );
149:

Line 167: INSERT INTO CSL_MTL_ITEM_LOCATIONS_ACC(

163: );
164: END IF;
165:
166: FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
167: INSERT INTO CSL_MTL_ITEM_LOCATIONS_ACC(
168: ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY
169: , COUNTER, RESOURCE_ID, INVENTORY_LOCATION_ID, ORGANIZATION_ID ) VALUES (
170: l_tab_access_id(i), sysdate, 1, sysdate, 1, counters(i), l_tab_resource_id(i), locations(i), organizations(i));
171:

Line 217: FROM MTL_ITEM_LOCATIONS

213: CURSOR c_item_loc_by_subinv ( b_organization_id NUMBER,
214: b_subinventory_code VARCHAR2 )
215: IS
216: SELECT INVENTORY_LOCATION_ID
217: FROM MTL_ITEM_LOCATIONS
218: WHERE ORGANIZATION_ID = b_organization_id
219: AND SUBINVENTORY_CODE = b_subinventory_code
220: AND (DISABLE_DATE > sysdate OR DISABLE_DATE IS NULL)
221: ;

Line 249: FROM MTL_ITEM_LOCATIONS B, CSL_MTL_ITEM_LOCATIONS_ACC A

245: b_subinventory_code VARCHAR2,
246: b_resource_id NUMBER )
247: IS
248: SELECT B.INVENTORY_LOCATION_ID
249: FROM MTL_ITEM_LOCATIONS B, CSL_MTL_ITEM_LOCATIONS_ACC A
250: WHERE B.ORGANIZATION_ID = b_organization_id
251: AND B.SUBINVENTORY_CODE = b_subinventory_code
252: AND A.RESOURCE_ID = b_resource_id
253: AND B.ORGANIZATION_ID = A.ORGANIZATION_ID

Line 332: FROM CSL_MTL_ITEM_LOCATIONS_ACC

328: , b_organization_id NUMBER
329: , b_resource_id NUMBER )
330: IS
331: SELECT ACCESS_ID
332: FROM CSL_MTL_ITEM_LOCATIONS_ACC
333: WHERE INVENTORY_LOCATION_ID = b_inventory_location_id
334: AND ORGANIZATION_ID = b_organization_id
335: AND RESOURCE_ID = b_resource_id;
336: BEGIN

Line 430: populate the CSL_MTL_ITEM_LOCATIONS_ACC records for all mobile users.

426: END Delete_Item_Location;
427:
428: /*
429: Iterate over all the acc records for subinventories for given mobile user.
430: populate the CSL_MTL_ITEM_LOCATIONS_ACC records for all mobile users.
431: */
432: PROCEDURE POPULATE_ITEM_LOCATIONS_ACC
433: IS
434: PRAGMA AUTONOMOUS_TRANSACTION;

Line 437: FROM MTL_ITEM_LOCATIONS L, JTM_MTL_SEC_INV_ACC A

433: IS
434: PRAGMA AUTONOMOUS_TRANSACTION;
435: CURSOR c_inserted
436: IS SELECT CSL_ACC_SEQUENCE.NEXTVAL, A.RESOURCE_ID, L.INVENTORY_LOCATION_ID, L.ORGANIZATION_ID, A.COUNTER
437: FROM MTL_ITEM_LOCATIONS L, JTM_MTL_SEC_INV_ACC A
438: WHERE L.SUBINVENTORY_CODE = A.SECONDARY_INVENTORY_NAME
439: AND L.ORGANIZATION_ID = A.ORGANIZATION_ID;
440:
441: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;

Line 453: DELETE FROM CSL_MTL_ITEM_LOCATIONS_ACC;

449:
450: l_dummy BOOLEAN;
451:
452: BEGIN
453: DELETE FROM CSL_MTL_ITEM_LOCATIONS_ACC;
454:
455: OPEN c_inserted;
456: FETCH c_inserted BULK COLLECT
457: INTO l_tab_access_id, l_tab_resource_id, locations, organizations, counters;

Line 471: INSERT INTO CSL_MTL_ITEM_LOCATIONS_ACC(

467: );
468: END IF;
469:
470: FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
471: INSERT INTO CSL_MTL_ITEM_LOCATIONS_ACC(
472: ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY
473: , COUNTER, RESOURCE_ID, INVENTORY_LOCATION_ID, ORGANIZATION_ID ) VALUES (
474: l_tab_access_id(i), sysdate, 1, sysdate, 1,
475: counters(i), l_tab_resource_id(i), locations(i), organizations(i));

Line 516: where package_name = 'CSL_MTL_ITEM_LOCATIONS_ACC_PKG'

512: CURSOR c_LastRundate
513: IS
514: select LAST_RUN_DATE
515: from JTM_CON_REQUEST_DATA
516: where package_name = 'CSL_MTL_ITEM_LOCATIONS_ACC_PKG'
517: AND procedure_name = 'CON_REQUEST_MTL_ITEM_LOCATIONS';
518: r_LastRundate c_LastRundate%ROWTYPE;
519: l_current_run_date DATE;
520: BEGIN

Line 517: AND procedure_name = 'CON_REQUEST_MTL_ITEM_LOCATIONS';

513: IS
514: select LAST_RUN_DATE
515: from JTM_CON_REQUEST_DATA
516: where package_name = 'CSL_MTL_ITEM_LOCATIONS_ACC_PKG'
517: AND procedure_name = 'CON_REQUEST_MTL_ITEM_LOCATIONS';
518: r_LastRundate c_LastRundate%ROWTYPE;
519: l_current_run_date DATE;
520: BEGIN
521: /*** get debug level ***/

Line 527: , 'Entering CON_REQUEST_MTL_ITEM_LOCATIONS'

523: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
524: jtm_message_log_pkg.Log_Msg
525: ( 0
526: , g_table_name
527: , 'Entering CON_REQUEST_MTL_ITEM_LOCATIONS'
528: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
529: );
530: END IF;
531:

Line 567: WHERE package_name = 'CSL_MTL_ITEM_LOCATIONS_ACC_PKG'

563:
564: /*Update the last run date*/
565: UPDATE JTM_CON_REQUEST_DATA
566: SET LAST_RUN_DATE = l_current_run_date
567: WHERE package_name = 'CSL_MTL_ITEM_LOCATIONS_ACC_PKG'
568: AND procedure_name = 'CON_REQUEST_ITEM_LOCATIONS';
569:
570: COMMIT;
571:

Line 594: END CSL_MTL_ITEM_LOCATIONS_ACC_PKG;

590: END IF;
591: ROLLBACK;
592: END CON_REQUEST_ITEM_LOCATIONS;
593:
594: END CSL_MTL_ITEM_LOCATIONS_ACC_PKG;