[Home] [Help]
PACKAGE BODY: APPS.CSL_HZ_PARTY_SITES_ACC_PKG
Source
1 PACKAGE BODY CSL_HZ_PARTY_SITES_ACC_PKG AS
2 /* $Header: cslpsacb.pls 120.0 2005/05/24 18:18:39 appldev noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_HZ_PARTY_SITES_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_PARTY_SITES');
8 g_table_name CONSTANT VARCHAR2(30) := 'HZ_PARTY_SITES';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'PARTY_SITE_ID';
10 g_debug_level NUMBER;
11
12 PROCEDURE INSERT_PARTY_SITE( p_party_site_id IN NUMBER
13 , p_resource_id IN NUMBER )
14 IS
15 CURSOR c_party_site( b_party_site_id NUMBER ) IS
16 SELECT *
17 FROM HZ_PARTY_SITES
18 WHERE party_site_id = b_party_site_id;
19
20 r_party_site c_party_site%ROWTYPE;
21
22 BEGIN
23 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
24
25 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
26 jtm_message_log_pkg.Log_Msg
27 ( p_party_site_id
28 , g_table_name
29 , 'Entering Insert_Party_Site'
30 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
31 END IF;
32
33 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
34 jtm_message_log_pkg.Log_Msg
35 ( p_party_site_id
36 , g_table_name
37 , 'Inserting ACC record for resource_id = '||p_resource_id
38 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
39 END IF;
40
41 JTM_HOOK_UTIL_PKG.Insert_Acc
42 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
43 , P_ACC_TABLE_NAME => g_acc_table_name
44 , P_PK1_NAME => g_pk1_name
45 , P_PK1_NUM_VALUE => p_party_site_id
46 , P_RESOURCE_ID => p_resource_id
47 );
48
49 /*Insert the matching location*/
50 OPEN c_party_site( b_party_site_id => p_party_site_id );
51 FETCH c_party_site INTO r_party_site;
52 IF c_party_site%FOUND THEN
53 CSL_HZ_LOCATIONS_ACC_PKG.INSERT_LOCATION( p_location_id => r_party_site.location_id
54 , p_resource_id => p_resource_id );
55 END IF;
56 CLOSE c_party_site;
57
58 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
59 jtm_message_log_pkg.Log_Msg
60 ( p_party_site_id
61 , g_table_name
62 , 'Leaving Insert_Party_Site'
63 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
64 END IF;
65
66 END INSERT_PARTY_SITE;
67
68 PROCEDURE UPDATE_PARTY_SITE( p_party_site_id IN NUMBER )
69 IS
70 l_tab_resource_id dbms_sql.Number_Table;
71 l_tab_access_id dbms_sql.Number_Table;
72 BEGIN
73 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
74 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
75 jtm_message_log_pkg.Log_Msg
76 ( p_party_site_id
77 , g_table_name
78 , 'Entering Update_Party_Site'
79 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
80 END IF;
81
82 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
83 ( P_ACC_TABLE_NAME => g_acc_table_name
84 , P_PK1_NAME => g_pk1_name
85 , P_PK1_NUM_VALUE => p_party_site_id
86 , L_TAB_RESOURCE_ID => l_tab_resource_id
87 , L_TAB_ACCESS_ID => l_tab_access_id
88 );
89
90 /*** re-send rec to all resources ***/
91 IF l_tab_resource_id.COUNT > 0 THEN
92 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
93
94 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
95 jtm_message_log_pkg.Log_Msg
96 ( p_party_site_id
97 , g_table_name
98 , 'Updating ACC record for resource_id = ' || l_tab_resource_id(i) || fnd_global.local_chr(10) ||
99 'access_id = ' || l_tab_access_id(i)
100 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
101 END IF;
102
103 JTM_HOOK_UTIL_PKG.Update_Acc
104 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
105 ,P_ACC_TABLE_NAME => g_acc_table_name
106 ,P_RESOURCE_ID => l_tab_resource_id(i)
107 ,P_ACCESS_ID => l_tab_access_id(i)
108 );
109 END LOOP;
110 END IF;
111
112 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
113 jtm_message_log_pkg.Log_Msg
114 ( p_party_site_id
115 , g_table_name
116 , 'Leaving Udate_Party_Site'
117 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
118 END IF;
119
120 END UPDATE_PARTY_SITE;
121
122 PROCEDURE DELETE_PARTY_SITE( p_party_site_id IN NUMBER
123 , p_resource_id IN NUMBER )
124 IS
125 CURSOR c_party_site( b_party_site_id NUMBER ) IS
126 SELECT *
127 FROM HZ_PARTY_SITES
128 WHERE party_site_id = b_party_site_id;
129
130 r_party_site c_party_site%ROWTYPE;
131
132 BEGIN
133 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
134 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
135 jtm_message_log_pkg.Log_Msg
136 ( p_party_site_id
137 , g_table_name
138 , 'Entering Delete_Party_Site'
139 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
140 END IF;
141
142 JTM_HOOK_UTIL_PKG.Delete_Acc
143 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
144 ,P_ACC_TABLE_NAME => g_acc_table_name
145 ,P_PK1_NAME => g_pk1_name
146 ,P_PK1_NUM_VALUE => p_party_site_id
147 ,P_RESOURCE_ID => p_resource_id
148 );
149
150 /*Delete the matching location*/
151 OPEN c_party_site( b_party_site_id => p_party_site_id );
152 FETCH c_party_site INTO r_party_site;
153 IF c_party_site%FOUND THEN
154 CSL_HZ_LOCATIONS_ACC_PKG.DELETE_LOCATION( p_location_id => r_party_site.location_id
155 , p_resource_id => p_resource_id );
156 END IF;
157 CLOSE c_party_site;
158
159 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
160 jtm_message_log_pkg.Log_Msg
161 ( p_party_site_id
162 , g_table_name
163 , 'Leaving Delete_Party_Site'
164 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
165 END IF;
166
167 END DELETE_PARTY_SITE;
168
169 PROCEDURE CHANGE_PARTY_SITE( p_old_party_site_id IN NUMBER
170 , p_new_party_site_id IN NUMBER
171 , p_resource_id IN NUMBER )
172 IS
173 CURSOR c_party_site( b_party_site_id NUMBER ) IS
174 SELECT *
175 FROM HZ_PARTY_SITES
176 WHERE party_site_id = b_party_site_id;
177
178 r_party_site c_party_site%ROWTYPE;
179
180 BEGIN
181 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
182 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
183 jtm_message_log_pkg.Log_Msg
184 ( p_old_party_site_id
185 , g_table_name
186 , 'Entering Change_Party_Site'
187 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
188 END IF;
189
190 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
191 jtm_message_log_pkg.Log_Msg
192 ( p_old_party_site_id
193 , g_table_name
194 , 'Change party site from '||p_old_party_site_id||' to '||p_new_party_site_id
195 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
196 END IF;
197
198 /*Party site is changed so delete the old one ( basicly we lower the counter )*/
199 IF p_old_party_site_id IS NOT NULL THEN
200 JTM_HOOK_UTIL_PKG.Delete_Acc
201 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
202 ,P_ACC_TABLE_NAME => g_acc_table_name
203 ,P_PK1_NAME => g_pk1_name
204 ,P_PK1_NUM_VALUE => p_old_party_site_id
205 ,P_RESOURCE_ID => p_resource_id
206 );
207
208 /*Delete the matching location*/
209 OPEN c_party_site( b_party_site_id => p_old_party_site_id );
210 FETCH c_party_site INTO r_party_site;
211 IF c_party_site%FOUND THEN
212 CSL_HZ_LOCATIONS_ACC_PKG.DELETE_LOCATION( p_location_id => r_party_site.location_id
213 , p_resource_id => p_resource_id );
214 END IF;
215 CLOSE c_party_site;
216 END IF;
217
218
219 /*Insert the new party site*/
220 IF p_new_party_site_id IS NOT NULL THEN
221 JTM_HOOK_UTIL_PKG.Insert_Acc
222 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
223 , P_ACC_TABLE_NAME => g_acc_table_name
224 , P_PK1_NAME => g_pk1_name
225 , P_PK1_NUM_VALUE => p_new_party_site_id
226 , P_RESOURCE_ID => p_resource_id
227 );
228 --Bug 3991346 - Call INSERT_LOCATION instead of DELETE_LOCATION
229 /*Insert the matching location*/
230 OPEN c_party_site( b_party_site_id => p_new_party_site_id );
231 FETCH c_party_site INTO r_party_site;
232 IF c_party_site%FOUND THEN
233 CSL_HZ_LOCATIONS_ACC_PKG.INSERT_LOCATION( p_location_id => r_party_site.location_id
234 , p_resource_id => p_resource_id );
235 END IF;
236 CLOSE c_party_site;
237 END IF;
238
239
240 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
241 jtm_message_log_pkg.Log_Msg
242 ( p_old_party_site_id
243 , g_table_name
244 , 'Leaving Change_Party_Site'
245 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
246 END IF;
247
248 END CHANGE_PARTY_SITE;
249
250 END CSL_HZ_PARTY_SITES_ACC_PKG;