[Home] [Help]
PACKAGE BODY: APPS.CSL_HZ_LOCATIONS_ACC_PKG
Source
1 PACKAGE BODY CSL_HZ_LOCATIONS_ACC_PKG AS
2 /* $Header: cslhlacb.pls 120.1 2005/08/31 02:58:17 utekumal noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_HZ_LOCATIONS_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_HZ_LOCATIONS');
8 g_table_name CONSTANT VARCHAR2(30) := 'HZ_LOCATIONS';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'LOCATION_ID';
10 g_debug_level NUMBER;
11
12 PROCEDURE INSERT_LOCATION( p_location_id IN NUMBER
13 , p_resource_id IN NUMBER )
14 IS
15 BEGIN
16 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
17
18 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
19 jtm_message_log_pkg.Log_Msg
20 ( p_location_id
21 , g_table_name
22 , 'Entering Insert_Location'
23 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
24 END IF;
25
26 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
27 jtm_message_log_pkg.Log_Msg
28 ( p_location_id
29 , g_table_name
30 , 'Inserting ACC record for resource_id = '||p_resource_id
31 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
32 END IF;
33
34 JTM_HOOK_UTIL_PKG.Insert_Acc
35 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
36 , P_ACC_TABLE_NAME => g_acc_table_name
37 , P_PK1_NAME => g_pk1_name
38 , P_PK1_NUM_VALUE => p_location_id
39 , P_RESOURCE_ID => p_resource_id
40 );
41
42 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
43 jtm_message_log_pkg.Log_Msg
44 ( p_location_id
45 , g_table_name
46 , 'Leaving Insert_Location'
47 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
48 END IF;
49
50 END INSERT_LOCATION;
51
52 PROCEDURE UPDATE_LOCATION( p_location_id IN NUMBER )
53 IS
54 l_tab_resource_id dbms_sql.Number_Table;
55 l_tab_access_id dbms_sql.Number_Table;
56 BEGIN
57 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
58 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
59 jtm_message_log_pkg.Log_Msg
60 ( p_location_id
61 , g_table_name
62 , 'Entering Update_Location'
63 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
64 END IF;
65
66 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
67 ( P_ACC_TABLE_NAME => g_acc_table_name
68 , P_PK1_NAME => g_pk1_name
69 , P_PK1_NUM_VALUE => p_location_id
70 , L_TAB_RESOURCE_ID => l_tab_resource_id
71 , L_TAB_ACCESS_ID => l_tab_access_id
72 );
73
74 /*** re-send rec to all resources ***/
75 IF l_tab_resource_id.COUNT > 0 THEN
76 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
77
78 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
79 jtm_message_log_pkg.Log_Msg
80 ( p_location_id
81 , g_table_name
82 , 'Updating ACC record for resource_id = ' || l_tab_resource_id(i) || fnd_global.local_chr(10) ||
83 'access_id = ' || l_tab_access_id(i)
84 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
85 END IF;
86
87 JTM_HOOK_UTIL_PKG.Update_Acc
88 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
89 ,P_ACC_TABLE_NAME => g_acc_table_name
90 ,P_RESOURCE_ID => l_tab_resource_id(i)
91 ,P_ACCESS_ID => l_tab_access_id(i)
92 );
93 END LOOP;
94 END IF;
95
96 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
97 jtm_message_log_pkg.Log_Msg
98 ( p_location_id
99 , g_table_name
100 , 'Leaving Udate_Location'
101 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
102 END IF;
103
104 END UPDATE_LOCATION;
105
106 PROCEDURE DELETE_LOCATION( p_location_id IN NUMBER
107 , p_resource_id IN NUMBER )
108 IS
109 BEGIN
110 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
111 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
112 jtm_message_log_pkg.Log_Msg
113 ( p_location_id
114 , g_table_name
115 , 'Entering Delete_Location'
116 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
117 END IF;
118
119 JTM_HOOK_UTIL_PKG.Delete_Acc
120 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
121 ,P_ACC_TABLE_NAME => g_acc_table_name
122 ,P_PK1_NAME => g_pk1_name
123 ,P_PK1_NUM_VALUE => p_location_id
124 ,P_RESOURCE_ID => p_resource_id
125 );
126
127 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
128 jtm_message_log_pkg.Log_Msg
129 ( p_location_id
130 , g_table_name
131 , 'Leaving Delete_Location'
132 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
133 END IF;
134
135 END DELETE_LOCATION;
136
137 PROCEDURE CHANGE_LOCATION( p_old_location_id IN NUMBER
138 , p_new_location_id IN NUMBER
139 , p_resource_id IN NUMBER )
140 IS
141 BEGIN
142 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
143 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
144 jtm_message_log_pkg.Log_Msg
145 ( p_old_location_id
146 , g_table_name
147 , 'Entering Change_Location'
148 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
149 END IF;
150
151 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
152 jtm_message_log_pkg.Log_Msg
153 ( p_old_location_id
154 , g_table_name
155 , 'Change location from '||p_old_location_id||' to '||p_new_location_id
156 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
157 END IF;
158
159 /*Party site is changed so delete the old one ( basicly we lower the counter )*/
160 JTM_HOOK_UTIL_PKG.Delete_Acc
161 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
162 ,P_ACC_TABLE_NAME => g_acc_table_name
163 ,P_PK1_NAME => g_pk1_name
164 ,P_PK1_NUM_VALUE => p_old_location_id
165 ,P_RESOURCE_ID => p_resource_id
166 );
167
168 /*Insert the new party site*/
169 JTM_HOOK_UTIL_PKG.Insert_Acc
170 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
171 , P_ACC_TABLE_NAME => g_acc_table_name
172 , P_PK1_NAME => g_pk1_name
173 , P_PK1_NUM_VALUE => p_new_location_id
174 , P_RESOURCE_ID => p_resource_id
175 );
176
177 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
178 jtm_message_log_pkg.Log_Msg
179 ( p_old_location_id
180 , g_table_name
181 , 'Leaving Change_Location'
182 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
183 END IF;
184
185 END CHANGE_LOCATION;
186
187 FUNCTION UPDATE_LOCATION_WFSUB( p_subscription_guid in raw
188 , p_event in out NOCOPY wf_event_t)
189 return varchar2
190 IS
191 l_key varchar2(240) := p_event.GetEventKey();
192 l_org_id NUMBER;
193 l_user_id NUMBER;
194 l_resp_id NUMBER;
195 l_resp_appl_id NUMBER;
196 l_security_group_id NUMBER;
197 l_count NUMBER;
198 l_location_id NUMBER;
199 BEGIN
200 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
201
202
203 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
204 jtm_message_log_pkg.Log_Msg
205 ( l_location_id
206 , g_table_name
207 , 'Entering UPDATE_LOCATION_WFSUB'
208 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
209 END IF;
210
211 l_org_id := p_event.GetValueForParameter('ORG_ID');
212 l_user_id := p_event.GetValueForParameter('USER_ID');
213 l_resp_id := p_event.GetValueForParameter('RESP_ID');
214 l_resp_appl_id := p_event.GetValueForParameter('RESP_APPL_ID');
215 l_security_group_id := p_event.GetValueForParameter('SECURITY_GROUP_ID');
216
217 fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id, l_security_group_id);
218
219 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
220 jtm_message_log_pkg.Log_Msg
221 ( l_location_id
222 , g_table_name
223 , 'Get parameter for hz parameter P_LOCATION_REC.LOCATION_ID'
224 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
225 END IF;
226 --Bug 4496299
227 /*
228 l_location_id := hz_param_pkg.ValueOfNumParameter (p_key => l_key,
229 p_parameter_name => 'P_LOCATION_REC.LOCATION_ID');
230 */
231
232 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
233 jtm_message_log_pkg.Log_Msg
234 ( l_location_id
235 , g_table_name
236 , 'Retrieved parameter for hz parameter P_LOCATION_REC.LOCATION_ID ' || l_location_id
237 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
238 END IF;
239
240 /*Is record valid ? assume so*/
241 -- IF Replicate_record( l_location_id ) THEN
242 UPDATE_LOCATION(l_location_id);
243 -- END IF;
244
245 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
246 jtm_message_log_pkg.Log_Msg
247 ( l_location_id
248 , g_table_name
249 , 'Leaving UPDATE_LOCATION_WFSUB'
250 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
251 END IF;
252
253 RETURN 'SUCCESS';
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 WF_CORE.CONTEXT('CSL_HZ_LOCATIONS_ACC_PKG', 'UPDATE_LOCATION_WFSUB', p_event.getEventName(), p_subscription_guid);
258 WF_EVENT.setErrorInfo(p_event, 'ERROR');
259 RETURN 'ERROR';
260 END UPDATE_LOCATION_WFSUB;
261
262 END CSL_HZ_LOCATIONS_ACC_PKG;