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.CSL_CSP_LOCATIONS_ACC_PKG

Source


1 PACKAGE BODY CSL_CSP_LOCATIONS_ACC_PKG AS
2 /* $Header: cslsaacb.pls 120.0 2005/05/25 11:02:56 appldev noship $ */
3 
4 /*** Globals ***/
5 -- CSP_RS_CUST_RELATIONS
6 g_acc_table_name1        CONSTANT VARCHAR2(30) := 'JTM_CSP_RS_CUST_RELATIONS_ACC';
7 g_publication_item_name1 CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
8   JTM_HOOK_UTIL_PKG.t_publication_item_list('CSP_RS_CUST_RELATIONS');
9 g_pk1_name1              CONSTANT VARCHAR2(30) := 'RS_CUST_RELATION_ID';
10 -- HZ_CUST_ACCT_SITES_ALL
11 g_acc_table_name2        CONSTANT VARCHAR2(30) := 'CSL_HZ_CUST_ACCT_SITES_ALL_ACC';
12 g_publication_item_name2 CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
13   JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_HZ_CUST_ACCT_SITES');
14 g_pk1_name2              CONSTANT VARCHAR2(30) := 'CUST_ACCT_SITE_ID';
15 -- HZ_CUST_SITE_USES_ALL
16 g_acc_table_name3        CONSTANT VARCHAR2(30) := 'CSL_HZ_CUST_SITE_USES_ALL_ACC';
17 g_publication_item_name3 CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
18   JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_HZ_CUST_SITE_USES');
19 g_pk1_name3              CONSTANT VARCHAR2(30) := 'SITE_USE_ID';
20 -- PO_LOCATION_ASSOCIATIONS_ALL
21 g_acc_table_name4        CONSTANT VARCHAR2(30) := 'JTM_PO_LOC_ASS_ALL_ACC';
22 g_publication_item_name4 CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
23   JTM_HOOK_UTIL_PKG.t_publication_item_list('PO_LOC_ASS_ALL');
24 g_pk1_name4              CONSTANT VARCHAR2(30) := 'LOCATION_ID';
25 
26 g_table_name            CONSTANT VARCHAR2(30) := 'PO_LOCATION_ASSOCIATIONS_ALL';
27 g_debug_level           NUMBER; -- debug level
28 
29 /*** Function that checks if location record(s) should be replicated. Returns TRUE if it should ***/
30 FUNCTION Replicate_Record
31   ( p_location_id NUMBER
32   )
33 RETURN BOOLEAN
34 IS
35   CURSOR c_location (b_location_id NUMBER) IS
36    SELECT PLA.location_id                 LOCATION_ID
37    ,      CSU.status                      STATUS
38    ,      RCR.resource_id                 RESOURCE_ID
39    FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
40    ,      HZ_CUST_SITE_USES_ALL        CSU
41    ,      HZ_CUST_ACCT_SITES_ALL       CAS
42    ,      CSP_RS_CUST_RELATIONS       RCR
43    ,      HZ_PARTY_SITES               HPS
44    ,      HZ_LOCATIONS                 HZL
45    WHERE  PLA.location_id       = b_location_id
46    AND    CSU.site_use_id       = PLA.site_use_id
47    AND    CSU.site_use_code     = 'SHIP_TO'
48    AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
49    AND    CAS.cust_account_id   = RCR.customer_id
50    AND    CAS.party_site_id     = HPS.party_site_id
51    AND    HPS.location_id       = HZL.location_id
52    AND    PLA.LOCATION_ID       = b_location_id;
53   r_location c_location%ROWTYPE;
54 
55 BEGIN
56   /*** get debug level ***/
57   g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
58 
59   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
60     jtm_message_log_pkg.Log_Msg
61     ( p_location_id
62     , g_table_name
63     , 'Entering Replicate_Record'
64     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
65   END IF;
66 
67   OPEN c_location( p_location_id );
68   FETCH c_location INTO r_location;
69   IF c_location%NOTFOUND THEN
70     /*** could not find location record -> exit ***/
71     IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
72       jtm_message_log_pkg.Log_Msg
73       ( p_location_id
74       , g_table_name
75       , 'Replicate_Record error: Could not find record associated with PO_LOCATION_ASSOCIATIONS_ALL.LOCATION_ID '
76         || p_location_id
77       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
78     END IF;
79 
80     CLOSE c_location;
81     RETURN FALSE;
82   END IF;
83 
84   CLOSE c_location;
85 
86   /*** is resource a mobile user? ***/
87   IF NOT JTM_HOOK_UTIL_PKG.isMobileFSresource( r_location.resource_id ) THEN
88     /*** No -> exit ***/
89     IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
90       jtm_message_log_pkg.Log_Msg
91       ( p_location_id
92       , g_table_name
93       , 'Replicate_Record returned FALSE' || fnd_global.local_chr(10) ||
94         'Resource_id ' || r_location.resource_id || ' is not a mobile user.'
95       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
96     END IF;
97 
98     RETURN FALSE;
99   END IF;
100 
101 
102   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
103     jtm_message_log_pkg.Log_Msg
104     ( p_location_id
105     , g_table_name
106     , 'Replicate_Record returned TRUE'
107     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
108   END IF;
109 
110   /** Record matched criteria -> return true ***/
111   RETURN TRUE;
112 END Replicate_Record;
113 
114 /*** Private procedure that replicates given location related data for resource ***/
115 PROCEDURE Insert_ACC_Record
116   ( p_location_id        IN NUMBER
117   )
118 IS
119   CURSOR c_location_ids (b_location_id NUMBER) IS
120    SELECT CSU.site_use_id                 CSU_ID
121    ,      CAS.cust_acct_site_id           CAS_ID
122    ,      RCR.RS_CUST_RELATION_ID         RCR_ID
123    ,      HPS.party_site_id               HPS_ID
124    ,      RCR.RESOURCE_ID                 RESOURCE_ID
125    FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
126    ,      HZ_CUST_SITE_USES_ALL        CSU
127    ,      HZ_CUST_ACCT_SITES_ALL       CAS
128    ,      CSP_RS_CUST_RELATIONS       RCR
129    ,      HZ_PARTY_SITES               HPS
130    WHERE  PLA.location_id       = b_location_id
131    AND    CSU.site_use_id       = PLA.site_use_id
132    AND    CSU.site_use_code     = 'SHIP_TO'
133    AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
134    AND    CAS.cust_account_id   = RCR.customer_id
135    AND    CAS.party_site_id     = HPS.party_site_id
136    AND    PLA.LOCATION_ID       = b_location_id;
137   r_location_ids c_location_ids%ROWTYPE;
138   l_resource_id  NUMBER;
139 BEGIN
140   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
141     jtm_message_log_pkg.Log_Msg
142     ( p_location_id
143     , g_table_name
144     , 'Entering Insert_ACC_Record'
145     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
146   END IF;
147 
148   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
149     jtm_message_log_pkg.Log_Msg
150     ( p_location_id
151     , g_table_name
152     , 'Inserting ACC record'
153     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
154   END IF;
155 
156   -- get all of the primary key values, these must be inserted into the ACC tables
157   OPEN c_location_ids( p_location_id );
158   FETCH c_location_ids INTO r_location_ids;
159   IF c_location_ids%NOTFOUND THEN
160     /*** could not find location record -> exit ***/
161     IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
162       jtm_message_log_pkg.Log_Msg
163       ( p_location_id
164       , g_table_name
165       , 'Insert ACC Record error: Could not find record associated with PO_LOCATION_ASSOCIATIONS_ALL.LOCATION_ID ' || p_location_id
166       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
167     END IF;
168   ELSE
169 
170     l_resource_id := r_location_ids.RESOURCE_ID;
171 
172     -- CSP_RS_CUST_RELATIONS
173    JTM_HOOK_UTIL_PKG.Insert_Acc
174       (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
175        , P_ACC_TABLE_NAME         => g_acc_table_name1
176        , P_PK1_NAME               => g_pk1_name1
177        , P_PK1_NUM_VALUE          => r_location_ids.RCR_ID
178        , P_RESOURCE_ID            => l_resource_id
179       );
180     -- HZ_CUST_ACCT_SITES_ALL
181     JTM_HOOK_UTIL_PKG.Insert_Acc
182     (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
183      , P_ACC_TABLE_NAME         => g_acc_table_name2
184      , P_PK1_NAME               => g_pk1_name2
185      , P_PK1_NUM_VALUE          => r_location_ids.CAS_ID
186      , P_RESOURCE_ID            => l_resource_id
187     );
188     -- HZ_CUST_SITE_USES_ALL
189     JTM_HOOK_UTIL_PKG.Insert_Acc
190     (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
191      , P_ACC_TABLE_NAME         => g_acc_table_name3
192      , P_PK1_NAME               => g_pk1_name3
193      , P_PK1_NUM_VALUE          => r_location_ids.CSU_ID
194      , P_RESOURCE_ID            => l_resource_id
195     );
196     -- PO_LOCATION_ASSOCIATIONS_ALL
197     JTM_HOOK_UTIL_PKG.Insert_Acc
198     (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name4
199      , P_ACC_TABLE_NAME         => g_acc_table_name4
200      , P_PK1_NAME               => g_pk1_name4
201      , P_PK1_NUM_VALUE          => p_location_id
202      , P_RESOURCE_ID            => l_resource_id
203     );
204 
205   -- HZ_PARTY_SITES ( ALSO HZ_LOCATIONS IS FILLED BY THIS HOOK )
206     CSL_HZ_PARTY_SITES_ACC_PKG.INSERT_PARTY_SITE( r_location_ids.HPS_ID, l_resource_id );
207   END IF;
208   CLOSE c_location_ids;
209 
210   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
211     jtm_message_log_pkg.Log_Msg
212     ( p_location_id
213     , g_table_name
214     , 'Leaving Insert_ACC_Record'
215     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
216   END IF;
217 END Insert_ACC_Record;
218 
219 /*** Private procedure that re-sends given assignment to mobile ***/
220 PROCEDURE Update_ACC_Record
221   ( p_location_id        IN NUMBER
222   )
223 IS
224   CURSOR c_location_ids (b_location_id NUMBER) IS
225    SELECT CSU.site_use_id                 CSU_ID
226    ,      CAS.cust_acct_site_id           CAS_ID
227    ,      RCR.RS_CUST_RELATION_ID         RCR_ID
228    ,      HPS.party_site_id               HPS_ID
229    ,      HZL.location_id                 HZL_ID
230    ,      RCR.resource_id                 RESOURCE_ID
231    FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
232    ,      HZ_CUST_SITE_USES_ALL        CSU
233    ,      HZ_CUST_ACCT_SITES_ALL       CAS
234    ,      CSP_RS_CUST_RELATIONS       RCR
235    ,      HZ_PARTY_SITES               HPS
236    ,      HZ_LOCATIONS                 HZL
237    WHERE  PLA.location_id       = b_location_id
238    AND    CSU.site_use_id       = PLA.site_use_id
239    AND    CSU.site_use_code     = 'SHIP_TO'
240    AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
241    AND    CAS.cust_account_id   = RCR.customer_id
242    AND    CAS.party_site_id     = HPS.party_site_id
243    AND    HPS.location_id       = HZL.location_id
244    AND    PLA.LOCATION_ID       = b_location_id;
245   r_location_ids c_location_ids%ROWTYPE;
246 
247   l_rcr_acc_id  NUMBER;
248   l_cas_acc_id  NUMBER;
249   l_csu_acc_id  NUMBER;
250   l_pla_acc_id  NUMBER;
251   l_hps_acc_id  NUMBER;
252   l_hzl_acc_id  NUMBER;
253   l_resource_id NUMBER;
254 BEGIN
255   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
256     jtm_message_log_pkg.Log_Msg
257     ( p_location_id
258     , g_table_name
259     , 'Entering Update_ACC_Record'
260     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
261   END IF;
262 
263   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
264     jtm_message_log_pkg.Log_Msg
265     ( p_location_id
266     , g_table_name
267     , 'Updating ACC record(s)'
268     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
269   END IF;
270 
271   -- get all of the primary key values, these must be inserted into the ACC tables
272   OPEN c_location_ids( p_location_id );
273   FETCH c_location_ids INTO r_location_ids;
274   IF c_location_ids%NOTFOUND THEN
275     /*** could not find location record -> exit ***/
276     IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
277       jtm_message_log_pkg.Log_Msg
278       ( p_location_id
279       , g_table_name
280       , 'Update ACC Record error: Could not find record associated with PO_LOCATION_ASSOCIATIONS_ALL.LOCATION_ID '
281         || p_location_id
282       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
283     END IF;
284   ELSE
285     l_resource_id := r_location_ids.RESOURCE_ID;
286 
287     -- CSP_RS_CUST_RELATIONS
288     l_rcr_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id(
289                       P_ACC_TABLE_NAME   => g_acc_table_name1
290                     , P_PK1_NAME         => g_pk1_name1
291                     , P_PK1_NUM_VALUE    => r_location_ids.RCR_ID
292                     , P_RESOURCE_ID      => l_resource_id);
293     JTM_HOOK_UTIL_PKG.Update_Acc
294      ( g_publication_item_name1
295       ,g_acc_table_name1
296       ,l_resource_id
297       ,l_rcr_acc_id
298      );
299     -- HZ_CUST_ACCT_SITES_ALL
300     l_cas_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id(
301                       P_ACC_TABLE_NAME   => g_acc_table_name2
302                     , P_PK1_NAME         => g_pk1_name2
303                     , P_PK1_NUM_VALUE    => r_location_ids.CAS_ID
304                     , P_RESOURCE_ID      => l_resource_id);
305     JTM_HOOK_UTIL_PKG.Update_Acc
306      ( g_publication_item_name2
307       ,g_acc_table_name2
308       ,l_resource_id
309       ,l_cas_acc_id
310      );
311     -- HZ_CUST_SITE_USES_ALL
312     l_csu_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id(
313                       P_ACC_TABLE_NAME   => g_acc_table_name3
314                     , P_PK1_NAME         => g_pk1_name3
315                     , P_PK1_NUM_VALUE    => r_location_ids.CSU_ID
316                     , P_RESOURCE_ID      => l_resource_id);
317     JTM_HOOK_UTIL_PKG.Update_Acc
318      ( g_publication_item_name3
319       ,g_acc_table_name3
320       ,l_resource_id
321       ,l_csu_acc_id
322      );
323     -- PO_LOCATION_ASSOCIATIONS_ALL
324     l_pla_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id(
325                       P_ACC_TABLE_NAME   => g_acc_table_name4
326                     , P_PK1_NAME         => g_pk1_name4
327                     , P_PK1_NUM_VALUE    => p_location_id
328                     , P_RESOURCE_ID      => l_resource_id);
329     JTM_HOOK_UTIL_PKG.Update_Acc
330      ( g_publication_item_name4
331       ,g_acc_table_name4
332       ,l_resource_id
333       ,l_pla_acc_id
334      );
338     -- HZ_LOCATIONS
335     -- HZ_PARTY_SITES
336     CSL_HZ_PARTY_SITES_ACC_PKG.UPDATE_PARTY_SITE( r_location_ids.HPS_ID );
337 
339     CSL_HZ_LOCATIONS_ACC_PKG.UPDATE_LOCATION( r_location_ids.HZL_ID );
340   END IF;
341 
342   CLOSE c_location_ids;
343 
344   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
345     jtm_message_log_pkg.Log_Msg
346     ( p_location_id
347     , g_table_name
348     , 'Leaving Update_ACC_Record'
349     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
350   END IF;
351 END Update_ACC_Record;
352 
353 /*** Private procedure that deletes assignment for resource from acc table ***/
354 PROCEDURE Delete_ACC_Record
355   ( p_location_id IN NUMBER
356    ,p_resource_id IN NUMBER
357   )
358 IS
359   CURSOR c_location_ids (b_location_id NUMBER) IS
360    SELECT CSU.site_use_id                 CSU_ID
361    ,      CAS.cust_acct_site_id           CAS_ID
362    ,      RCR.RS_CUST_RELATION_ID         RCR_ID
363    ,      HPS.party_site_id               HPS_ID
364    ,      RCR.RESOURCE_ID                 RESOURCE_ID
365    FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
366    ,      HZ_CUST_SITE_USES_ALL        CSU
367    ,      HZ_CUST_ACCT_SITES_ALL       CAS
368    ,      CSP_RS_CUST_RELATIONS       RCR
369    ,      HZ_PARTY_SITES               HPS
370    WHERE  PLA.location_id       = b_location_id
371    AND    CSU.site_use_id       = PLA.site_use_id
372    AND    CSU.site_use_code     = 'SHIP_TO'
373    AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
374    AND    CAS.cust_account_id   = RCR.customer_id
375    AND    CAS.party_site_id     = HPS.party_site_id
376    AND    PLA.LOCATION_ID       = b_location_id;
377   r_location_ids c_location_ids%ROWTYPE;
378 BEGIN
379   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
380     jtm_message_log_pkg.Log_Msg
381     ( p_location_id
382     , g_table_name
383     , 'Entering Delete_ACC_Record'
384     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
385   END IF;
386 
387   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
388     jtm_message_log_pkg.Log_Msg
389     ( p_location_id
390     , g_table_name
391     , 'Deleting ACC record for resource_id = ' || p_resource_id
392     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
393   END IF;
394 
395   -- No delete of the shipment addres is possible
396   -- get all of the primary key values, these must be inserted into the ACC tables
397   OPEN c_location_ids( p_location_id );
398   FETCH c_location_ids INTO r_location_ids;
399   IF c_location_ids%NOTFOUND THEN
400     /*** could not find location record -> exit ***/
401     IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
402       jtm_message_log_pkg.Log_Msg
403       ( p_location_id
404       , g_table_name
405       , 'Delete ACC Record error: Could not find record for LOCATION_ID ' || p_location_id
406       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
407     END IF;
408   ELSE
409    IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
410       jtm_message_log_pkg.Log_Msg
411      ( p_resource_id
412       , g_table_name
413       , 'Delete CSP_RS_CUST_RELATIONS acc record for user: ' || p_resource_id
414       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
415     END IF;
416     JTM_HOOK_UTIL_PKG.Delete_Acc
417      (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
418       , P_ACC_TABLE_NAME         => g_acc_table_name1
419       , P_PK1_NAME               => g_pk1_name1
420       , P_PK1_NUM_VALUE          => r_location_ids.RCR_ID
421       , P_RESOURCE_ID            => p_resource_id
422      );
423 
424     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
425       jtm_message_log_pkg.Log_Msg
426       ( p_resource_id
427       , g_table_name
428       , 'Delete HZ_CUST_ACCT_SITES_ALL acc record for user: ' || p_resource_id
429       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
430     END IF;
431     JTM_HOOK_UTIL_PKG.Delete_Acc
432      (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
433       , P_ACC_TABLE_NAME         => g_acc_table_name2
434       , P_PK1_NAME               => g_pk1_name2
435       , P_PK1_NUM_VALUE          => r_location_ids.CAS_ID
436       , P_RESOURCE_ID            => p_resource_id
437      );
438 
439     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
440       jtm_message_log_pkg.Log_Msg
441       ( p_resource_id
442       , g_table_name
443       , 'Delete HZ_CUST_SITE_USES_ALL acc record for user: ' || p_resource_id
444       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
445     END IF;
446     JTM_HOOK_UTIL_PKG.Delete_Acc
447      (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
448       , P_ACC_TABLE_NAME         => g_acc_table_name3
449       , P_PK1_NAME               => g_pk1_name3
450       , P_PK1_NUM_VALUE          => r_location_ids.CSU_ID
451       , P_RESOURCE_ID            => p_resource_id
452      );
453 
454     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
455       jtm_message_log_pkg.Log_Msg
456       ( p_resource_id
457       , g_table_name
458       , 'Delete PO_LOCATION_ASSOCIATIONS_ALL acc record for user: ' || p_resource_id
459       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
460     END IF;
461     JTM_HOOK_UTIL_PKG.Delete_Acc
465       , P_PK1_NUM_VALUE          => p_location_id
462      (  P_PUBLICATION_ITEM_NAMES => g_publication_item_name4
463       , P_ACC_TABLE_NAME         => g_acc_table_name4
464       , P_PK1_NAME               => g_pk1_name4
466       , P_RESOURCE_ID            => p_resource_id
467      );
468 
469     IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
470       jtm_message_log_pkg.Log_Msg
471       ( p_resource_id
472       , g_table_name
473       , 'Calling CSL_HZ_PARTY_SITES_ACC_PKG.Delete_Party_Site'
474       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
475     END IF;
476 
477     CSL_HZ_PARTY_SITES_ACC_PKG.Delete_Party_Site( r_location_ids.HPS_ID, p_resource_id );
478   END IF;
479   CLOSE c_location_ids;
480 
481   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
482     jtm_message_log_pkg.Log_Msg
483     ( p_location_id
484     , g_table_name
485     , 'Leaving Delete_ACC_Record'
486     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
487   END IF;
488 END Delete_ACC_Record;
489 
490 /*** Called before location Insert ***/
491 PROCEDURE PRE_INSERT_SHIP_LOCATION
492   ( x_return_status OUT NOCOPY varchar2
493   )
494 IS
495 BEGIN
496   x_return_status := FND_API.G_RET_STS_SUCCESS;
497 END PRE_INSERT_SHIP_LOCATION;
498 
499 /*** Called after location Insert ***/
500 PROCEDURE POST_INSERT_SHIP_LOCATION
501   ( x_return_status OUT NOCOPY varchar2
502   )
503 IS
504   l_location_id        NUMBER;
505   l_enabled_flag       VARCHAR2(30);
506 BEGIN
507   l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL');
508   IF l_enabled_flag <>'Y' THEN
509    x_return_status := FND_API.G_RET_STS_SUCCESS;
510    RETURN;
511   END IF;
512   /*** get location record details from public API ***/
513   l_location_id  := csp_ship_to_address_pvt.g_inv_loc_id;
514 
515   /*** get debug level ***/
516   g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
517   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
518     jtm_message_log_pkg.Log_Msg
519     ( l_location_id
520     , g_table_name
521     , 'Entering POST_INSERT hook'
522     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
523   END IF;
524 
525   /*** Insert record if applicable ***/
526   IF Replicate_Record(l_location_id) THEN
527     Insert_ACC_Record(l_location_id);
528   END IF;
529 
530   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
531     jtm_message_log_pkg.Log_Msg
532     ( l_location_id
533     , g_table_name
534     , 'Leaving POST_INSERT hook'
535     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
536   END IF;
537 
538   x_return_status := FND_API.G_RET_STS_SUCCESS;
539   RETURN;
540 
541 EXCEPTION WHEN OTHERS THEN
542   /*** hook failed -> log error ***/
543   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
544     jtm_message_log_pkg.Log_Msg
545     ( l_location_id
546     , g_table_name
547     , 'Caught exception in POST_INSERT hook:' || fnd_global.local_chr(10) || sqlerrm
548     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
549   END IF;
550   fnd_msg_pub.Add_Exc_Msg('CSL_CSP_LOCATIONS_ACC_PKG','POST_INSERT_SHIP_LOCATION',sqlerrm);
551 --  x_return_status := FND_API.G_RET_STS_ERROR;
552   x_return_status := FND_API.G_RET_STS_SUCCESS;
553 END POST_INSERT_SHIP_LOCATION;
554 
555 /* Called before location Update */
556 PROCEDURE PRE_UPDATE_SHIP_LOCATION
557   ( x_return_status OUT NOCOPY varchar2
558   )
559 IS
560 BEGIN
561   x_return_status := FND_API.G_RET_STS_SUCCESS;
562 
563 EXCEPTION WHEN OTHERS THEN
564   /*** hook failed -> log error ***/
565   fnd_msg_pub.Add_Exc_Msg('CSL_CSP_LOCATIONS_ACC_PKG','PRE_UPDATE_SHIP_LOCATION',sqlerrm);
566 --  x_return_status := FND_API.G_RET_STS_ERROR;
567   x_return_status := FND_API.G_RET_STS_SUCCESS;
568 END PRE_UPDATE_SHIP_LOCATION;
569 
570 /* Called after assignment Update */
571 PROCEDURE POST_UPDATE_SHIP_LOCATION
572   ( x_return_status OUT NOCOPY varchar2
573   )
574 IS
575   l_location_id        NUMBER;
576   l_enabled_flag       VARCHAR2(30);
577 BEGIN
578   l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL');
579   IF l_enabled_flag <>'Y' THEN
580    x_return_status := FND_API.G_RET_STS_SUCCESS;
581    RETURN;
582   END IF;
583   /*** get assignment record details from public API ***/
584   l_location_id := csp_ship_to_address_pvt.g_inv_loc_id;
585 
586   /*** get debug level ***/
587   g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
588 
589   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
590     jtm_message_log_pkg.Log_Msg
591     ( l_location_id
592     , g_table_name
593     , 'Entering POST_UPDATE hook'
594     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
595   END IF;
596 
597   IF Replicate_Record( l_location_id ) THEN
598     Update_ACC_Record(l_location_id);
599   END IF;
600 
601   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
602     jtm_message_log_pkg.Log_Msg
603     ( l_location_id
604     , g_table_name
605     , 'Leaving POST_UPDATE hook'
606     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
607   END IF;
608 
612   IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
609   x_return_status := FND_API.G_RET_STS_SUCCESS;
610 EXCEPTION WHEN OTHERS THEN
611   /*** hook failed -> log error ***/
613     jtm_message_log_pkg.Log_Msg
614     ( l_location_id
615     , g_table_name
616     , 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
617     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
618   END IF;
619   fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASKS_ACC_PKG','POST_UPDATE_SHIP_LOCATION',sqlerrm);
620 --  x_return_status := FND_API.G_RET_STS_ERROR;
621   x_return_status := FND_API.G_RET_STS_SUCCESS;
622 END POST_UPDATE_SHIP_LOCATION;
623 
624 /* Called before assignment Delete */
625 PROCEDURE PRE_DELETE_SHIP_LOCATION
626   ( x_return_status OUT NOCOPY varchar2
627   )
628 IS
629 BEGIN
630   x_return_status := FND_API.G_RET_STS_SUCCESS;
631 END PRE_DELETE_SHIP_LOCATION;
632 
633 /* Called after assignment Delete */
634 PROCEDURE POST_DELETE_SHIP_LOCATION
635   ( x_return_status OUT NOCOPY varchar2
636   )
637 IS
638 BEGIN
639 
640   x_return_status := FND_API.G_RET_STS_SUCCESS;
641 
642 EXCEPTION WHEN OTHERS THEN
643   /*** hook failed -> log error ***/
644   fnd_msg_pub.Add_Exc_Msg('CSL_CSP_LOCATIONS_ACC_PKG','POST_DELETE_SHIP_LOCATION',sqlerrm);
645 --  x_return_status := FND_API.G_RET_STS_ERROR;
646   x_return_status := FND_API.G_RET_STS_SUCCESS;
647 END POST_DELETE_SHIP_LOCATION;
648 
649 /* Remove all ACC resords of a mobile user */
650 PROCEDURE Delete_All_ACC_Records
651   ( p_resource_id in NUMBER
652   , x_return_status OUT NOCOPY varchar2
653   )
654 IS
655  CURSOR c_location( b_resource_id NUMBER ) IS
656   SELECT PLA.LOCATION_ID
657   FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
658   ,      HZ_CUST_SITE_USES_ALL        CSU
659   ,      HZ_CUST_ACCT_SITES_ALL       CAS
660   ,      CSP_RS_CUST_RELATIONS       RCR
661   ,      HZ_PARTY_SITES               HPS
662   WHERE  CSU.site_use_id       = PLA.site_use_id
663   AND    CSU.site_use_code     = 'SHIP_TO'
664   AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
665   AND    CAS.cust_account_id   = RCR.customer_id
666   AND    CAS.party_site_id     = HPS.party_site_id
667   AND    RCR.RESOURCE_ID       = b_resource_id;
668 BEGIN
669  /*** get debug level ***/
670  g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
671  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
672     jtm_message_log_pkg.Log_Msg
673     ( p_resource_id
674     , g_table_name
675     , 'Entering Delete_All_ACC_Records procedure for user: ' || p_resource_id
676     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
677   END IF;
678 
679   FOR r_location IN c_location( p_resource_id ) LOOP
680     Delete_ACC_Record(r_location.location_id, p_resource_id );
681   END LOOP;
682 
683   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
684     jtm_message_log_pkg.Log_Msg
685     ( p_resource_id
686     , g_table_name
687     , 'Leaving Delete_All_ACC_Records procedure for user: ' || p_resource_id
688     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
689   END IF;
690 
691   x_return_status := FND_API.G_RET_STS_SUCCESS;
692 EXCEPTION WHEN OTHERS THEN
693   /*** hook failed -> log error ***/
694   fnd_msg_pub.Add_Exc_Msg('CSL_CSP_LOCATIONS_ACC_PKG','Delete_All_ACC_Records',sqlerrm);
695 --  x_return_status := FND_API.G_RET_STS_ERROR;
696   x_return_status := FND_API.G_RET_STS_SUCCESS;
697 END Delete_All_ACC_Records;
698 
699 /* Full synch for a mobile user */
700 PROCEDURE Insert_All_ACC_Records
701   ( p_resource_id in NUMBER
702   , x_return_status OUT NOCOPY varchar2
703   )
704 IS
705  CURSOR c_location( b_resource_id NUMBER ) IS
706   SELECT PLA.LOCATION_ID
707   FROM   PO_LOCATION_ASSOCIATIONS_ALL PLA
708   ,      HZ_CUST_SITE_USES_ALL        CSU
709   ,      HZ_CUST_ACCT_SITES_ALL       CAS
710   ,      CSP_RS_CUST_RELATIONS       RCR
711   ,      HZ_PARTY_SITES               HPS
712   WHERE  CSU.site_use_id       = PLA.site_use_id
713   AND    CSU.site_use_code     = 'SHIP_TO'
714   AND    CSU.cust_acct_site_id = CAS.cust_acct_site_id
715   AND    CAS.cust_account_id   = RCR.customer_id
716   AND    CAS.party_site_id     = HPS.party_site_id
717   AND    RCR.RESOURCE_ID       = b_resource_id;
718 
719 BEGIN
720   /*** get debug level ***/
721   g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
722   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
723     jtm_message_log_pkg.Log_Msg
724     ( p_resource_id
725     , g_table_name
726     , 'Entering Insert_All_ACC_Records procedure for user: ' || p_resource_id
727     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
728   END IF;
729 
730   FOR r_location IN c_location( p_resource_id ) LOOP
731     IF Replicate_Record(r_location.location_id) THEN
732       Insert_ACC_Record(r_location.location_id);
733     END IF;
734   END LOOP;
735 
736   IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
737     jtm_message_log_pkg.Log_Msg
738     ( p_resource_id
739     , g_table_name
740     , 'Leaving Insert_All_ACC_Records procedure for user: ' || p_resource_id
741     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
742   END IF;
743 
744   x_return_status := FND_API.G_RET_STS_SUCCESS;
745 EXCEPTION WHEN OTHERS THEN
746   /*** hook failed -> log error ***/
750 END Insert_All_ACC_Records;
747   fnd_msg_pub.Add_Exc_Msg('CSL_CSP_LOCATIONS_ACC_PKG','Insert_All_ACC_Records',sqlerrm);
748 --  x_return_status := FND_API.G_RET_STS_ERROR;
749   x_return_status := FND_API.G_RET_STS_SUCCESS;
751 
752 END CSL_CSP_LOCATIONS_ACC_PKG;