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.
PACKAGE BODY: APPS.CSM_MTL_SEC_LOCATORS_EVENT_PKG

Source


1 PACKAGE BODY CSM_MTL_SEC_LOCATORS_EVENT_PKG AS
2 /* $Header: csmemslb.pls 120.1.12020000.2 2013/04/09 10:56:47 saradhak ship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person      Date    Comments
11 -- ---------   ------  ------------------------------------------
12    -- Enter procedure, function bodies as shown below
13 
14 g_table_name1            CONSTANT VARCHAR2(30) := 'CSM_MTL_SECONDARY_LOCATORS';
15 g_acc_table_name1        CONSTANT VARCHAR2(30) := 'CSM_MTL_SECONDARY_LOCATORS_ACC';
16 g_acc_sequence_name1     CONSTANT VARCHAR2(30) := 'CSM_MTL_SEC_LOCATORS_ACC_S';
17 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18                              CSM_ACC_PKG.t_publication_item_list('CSM_MTL_SECONDARY_LOCATORS');
19 g_pk1_name1              CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
20 g_pk2_name1              CONSTANT VARCHAR2(30) := 'SECONDARY_LOCATOR';
21 g_pk3_name1              CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
22 g_pub_item               CONSTANT VARCHAR2(30) := 'CSM_MTL_SECONDARY_LOCATORS';
23 
24 PROCEDURE Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
25                        p_message OUT NOCOPY VARCHAR2)
26 IS
27 PRAGMA AUTONOMOUS_TRANSACTION;
28 l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
29 l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
30 l_dsql varchar2(2048);
31 l_pub_item VARCHAR2(30) := 'CSM_MTL_SECONDARY_LOCATORS';
32 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
33 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
34 l_mark_dirty boolean;
35 l_all_omfs_palm_resource_list asg_download.user_list;
36 l_null_resource_list asg_download.user_list;
37 l_single_access_id_list asg_download.access_list;
38 --a null list
39 l_null_access_list asg_download.access_list;
40 l_run_date date;
41 l_sqlerrno varchar2(20);
42 l_sqlerrmsg varchar2(2000);
43 
44 CURSOR l_last_run_date_csr
45 IS
46 SELECT nvl(last_run_date, (sysdate - 365*50))
47 FROM jtm_con_request_data
48 WHERE package_name = 'CSM_MTL_SEC_LOCATORS_EVENT_PKG'
49 AND procedure_name = 'REFRESH_ACC';
50 
51 -- process inserts
52 CURSOR l_mtl_sec_loc_ins_csr
53 IS
54 SELECT msi_acc.user_id, msl.inventory_item_id,
55       msl.organization_id, msl.secondary_locator
56 FROM csm_mtl_system_items_acc msi_acc,
57      csm_mtl_item_locations_acc mil_acc,
58      mtl_secondary_locators msl
59 WHERE msl.inventory_item_id = msi_acc.inventory_item_id
60 AND msl.organization_id = msi_acc.organization_id
61 AND msl.secondary_locator = mil_acc.inventory_location_id
62 AND msl.organization_id = mil_acc.organization_id
63 AND msi_acc.user_id = mil_acc.user_id
64 AND NOT EXISTS
65 (SELECT 1
66  FROM csm_mtl_secondary_locators_acc acc
67  WHERE acc.user_id = msi_acc.user_id
68  AND acc.inventory_item_id = msi_acc.inventory_item_id
69  AND acc.organization_id = msi_acc.organization_id
70  AND acc.secondary_locator = msl.secondary_locator
71 )
72 UNION ALL
73 SELECT mil_acc.user_id, msl.inventory_item_id,
74       msl.organization_id, msl.secondary_locator
75 FROM csm_mtl_system_items_acc msi_acc,
76      csm_mtl_item_locations_acc mil_acc,
77      mtl_secondary_locators msl,
78      csm_user_inventory_org uorg
79 WHERE msl.inventory_item_id = msi_acc.inventory_item_id
80 AND msl.organization_id = msi_acc.organization_id
81 AND msl.secondary_locator = mil_acc.inventory_location_id
82 AND msl.organization_id = mil_acc.organization_id
83 AND msi_acc.user_id = -1
84 AND mil_acc.user_id = uorg.user_id
85 AND msi_acc.organization_id=uorg.organization_id
86 AND NOT EXISTS(SELECT 1
87  FROM csm_mtl_secondary_locators_acc acc
88  WHERE acc.user_id = mil_acc.user_id
89  AND acc.inventory_item_id = msl.inventory_item_id
90  AND acc.organization_id   = msl.organization_id
91  AND acc.secondary_locator = msl.secondary_locator
92 );
93 
94 -- process updates
95 CURSOR l_mtl_sec_loc_upd_csr(p_last_upd_date DATE)
96 IS
97 SELECT acc.access_id, acc.secondary_locator, acc.organization_id,
98        acc.inventory_item_id, acc.user_id
99 FROM csm_mtl_secondary_locators_acc acc,
100      mtl_secondary_locators msl
101 WHERE msl.secondary_locator = acc.secondary_locator
102 AND msl.organization_id = acc.organization_id
103 AND msl.inventory_item_id = acc.inventory_item_id
104 AND msl.last_update_date >= p_last_upd_date;
105 
106 -- process deletes
107 CURSOR l_mtl_sec_loc_del_csr
108 IS
109 SELECT acc.user_id, acc.secondary_locator, acc.organization_id, acc.inventory_item_id
110 FROM csm_mtl_secondary_locators_acc acc
111 WHERE NOT EXISTS
112 ((SELECT 1
113  FROM csm_mtl_system_items_acc msi_acc,
114       csm_mtl_item_locations_acc mil_acc,
115       mtl_secondary_locators msl
116  WHERE msl.inventory_item_id = msi_acc.inventory_item_id
117  AND msl.organization_id = msi_acc.organization_id
118  AND msl.secondary_locator = mil_acc.inventory_location_id
119  AND msl.organization_id = mil_acc.organization_id
120  AND msi_acc.user_id = mil_acc.user_id
121  AND acc.secondary_locator = msl.secondary_locator
122  AND acc.organization_id = msl.organization_id
123  AND acc.inventory_item_id = msl.inventory_item_id
124  AND acc.user_id = msi_acc.user_id
125  )
126  UNION ALL
127 (SELECT 1
128  FROM csm_mtl_system_items_acc msi_acc,
129       csm_mtl_item_locations_acc mil_acc,
130       mtl_secondary_locators msl,
131      csm_user_inventory_org uorg
132  WHERE msl.inventory_item_id = msi_acc.inventory_item_id
133  AND msl.organization_id = msi_acc.organization_id
134  AND msl.secondary_locator = mil_acc.inventory_location_id
135  AND msl.organization_id = mil_acc.organization_id
136  AND acc.user_id = mil_acc.user_id
137  AND acc.secondary_locator = msl.secondary_locator
138  AND acc.organization_id = msl.organization_id
139  AND acc.inventory_item_id = msl.inventory_item_id
140  AND acc.user_id = uorg.user_id
141  AND msi_acc.USER_ID = -1
142  AND acc.organization_id = uorg.organization_id
143  ));
144 
145 BEGIN
146  -- data program is run
147  l_run_date := SYSDATE;
148 
149  -- get last conc program update date
150  OPEN l_last_run_date_csr;
151  FETCH l_last_run_date_csr INTO l_prog_update_date;
152  CLOSE l_last_run_date_csr;
153 
154  -- process deletes
155  FOR r_mtl_sec_loc_del_rec IN l_mtl_sec_loc_del_csr LOOP
156    CSM_ACC_PKG.Delete_acc
157      ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
158       ,P_ACC_TABLE_NAME         => g_acc_table_name1
159       ,P_PK1_NAME               => g_pk1_name1
160       ,P_PK1_NUM_VALUE          => r_mtl_sec_loc_del_rec.inventory_item_id
161       ,P_PK2_NAME               => g_pk2_name1
162       ,P_PK2_NUM_VALUE          => r_mtl_sec_loc_del_rec.secondary_locator
163       ,P_PK3_NAME               => g_pk3_name1
164       ,P_PK3_NUM_VALUE          => r_mtl_sec_loc_del_rec.organization_id
165       ,P_USER_ID                => r_mtl_sec_loc_del_rec.user_id
166       );
167  END LOOP;
168 
169 -- process updates
170  FOR r_mtl_sec_loc_upd_rec IN l_mtl_sec_loc_upd_csr(l_prog_update_date) LOOP
171    CSM_ACC_PKG.Update_acc
172      ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
173       ,P_ACC_TABLE_NAME         => g_acc_table_name1
174       ,P_ACCESS_ID              => r_mtl_sec_loc_upd_rec.access_id
175       ,P_USER_ID                 => r_mtl_sec_loc_upd_rec.user_id
176       );
177  END LOOP;
178 
179  -- process inserts
180  FOR r_mtl_sec_loc_ins_rec IN l_mtl_sec_loc_ins_csr LOOP
181      CSM_ACC_PKG.Insert_Acc
182      ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
183       ,P_ACC_TABLE_NAME         => g_acc_table_name1
184       ,P_SEQ_NAME               => g_acc_sequence_name1
185       ,P_PK1_NAME               => g_pk1_name1
186       ,P_PK1_NUM_VALUE          => r_mtl_sec_loc_ins_rec.inventory_item_id
187       ,P_PK2_NAME               => g_pk2_name1
188       ,P_PK2_NUM_VALUE          => r_mtl_sec_loc_ins_rec.secondary_locator
189       ,P_PK3_NAME               => g_pk3_name1
190       ,P_PK3_NUM_VALUE          => r_mtl_sec_loc_ins_rec.organization_id
191       ,P_USER_ID                => r_mtl_sec_loc_ins_rec.user_id
192      );
193  END LOOP;
194 
195   -- set the program update date in jtm_con_request_data to sysdate
196   UPDATE jtm_con_request_data
197   SET last_run_date = l_run_date
198   WHERE package_name = 'CSM_MTL_SEC_LOCATORS_EVENT_PKG'
199     AND procedure_name = 'REFRESH_ACC';
200 
201  COMMIT;
202 
203   p_status := 'FINE';
204   p_message :=  'CSM_MTL_SEC_LOCATORS_EVENT_PKG.Refresh_Acc Executed successfully';
205 
206  EXCEPTION
207   WHEN others THEN
208      l_sqlerrno := to_char(SQLCODE);
209      l_sqlerrmsg := substr(SQLERRM, 1,2000);
210      p_status := 'ERROR';
211      p_message :=  'Error in CSM_MTL_SEC_LOCATORS_EVENT_PKG.Refresh_Acc:' || l_sqlerrno || ':' || l_sqlerrmsg;
212      ROLLBACK;
213      CSM_UTIL_PKG.LOG('CSM_MTL_SEC_LOCATORS_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg, 'CSM_MTL_SEC_LOCATORS_EVENT_PKG.Refresh_acc',FND_LOG.LEVEL_EXCEPTION);
214 END Refresh_Acc;
215 
216 END CSM_MTL_SEC_LOCATORS_EVENT_PKG;