1 PACKAGE BODY zx_hr_locations_reporting_hook AS
2 /* $Header: zxlocreportingb.pls 120.4 2006/10/26 11:28:22 asengupt ship $*/
3
4
5 /*
6 ** PROCEDURE : CREATE_KR_BIZ_LOCATION
7 **
8 ** HISTORY :
9 ** 23-NOV-2005 Yoshimichi Konishi Created.
10 **
11 ** WHAT IT DOES :
12 ** If an HR location code being created exists in eBTax reporting code table
13 ** then eBTax synch hook does nothing.
14 ** If the HR location code being created does not exist in eBTax reporting code,
15 ** the synch hook will create a corresponding Reporting Code.
16 **
17 */
18
19 PROCEDURE create_kr_biz_location (p_location_code IN VARCHAR2,
20 p_style IN VARCHAR2,
21 p_country IN VARCHAR2)
22 IS
23 TYPE num15_tbl_type IS TABLE OF NUMBER(15);
24 l_rowid VARCHAR2(4000);
25 l_reporting_code_id NUMBER(15);
26 l_reporting_type_id num15_tbl_type;
27 l_cnt_reporting_code PLS_INTEGER;
28
29 BEGIN
30
31 IF p_location_code IS NOT NULL
32 AND
33 (p_style IN ('KR', 'KR_GBL') OR (p_country = 'KR'))
34 THEN
35
36 l_cnt_reporting_code := 0;
37
38 SELECT count(reporting_code_char_value)
39 INTO l_cnt_reporting_code
40 FROM zx_reporting_codes_b
41 WHERE reporting_code_char_value = p_location_code;
42
43 IF l_cnt_reporting_code = 0 THEN
44 SELECT reporting_type_id
45 BULK COLLECT INTO l_reporting_type_id
46 FROM zx_reporting_types_b
47 WHERE reporting_type_code = 'KR_BUSINESS_LOCATIONS';
48
49 IF l_reporting_type_id IS NOT NULL
50 THEN
51 FOR i in Nvl(l_reporting_type_id.first,1)..Nvl(l_reporting_type_id.last,0) LOOP
52 SELECT zx_reporting_codes_b_s.nextval
53 INTO l_reporting_code_id
54 FROM dual;
55
56
57 ZX_REPORTING_CODES_PKG.INSERT_ROW (
58 X_ROWID => l_rowid ,
59 X_REPORTING_CODE_ID => l_reporting_code_id ,
60 X_REPORTING_CODE_CHAR_VALUE => p_location_code ,
61 X_REPORTING_CODE_NUM_VALUE => NULL ,
62 X_REPORTING_CODE_DATE_VALUE => NULL ,
63 X_REPORTING_TYPE_ID => l_reporting_type_id(i) ,
64 X_EXCEPTION_CODE => NULL ,
65 X_EFFECTIVE_FROM => sysdate ,
66 X_EFFECTIVE_TO => NULL ,
67 X_RECORD_TYPE_CODE => 'USER_DEFINED' ,
68 X_REQUEST_ID => fnd_global.conc_request_id ,
69 X_PROGRAM_LOGIN_ID => fnd_global.conc_login_id ,
70 X_REPORTING_CODE_NAME => p_location_code ,
71 X_CREATION_DATE => sysdate ,
72 X_CREATED_BY => fnd_global.user_id ,
73 X_LAST_UPDATE_DATE => sysdate ,
74 X_LAST_UPDATED_BY => fnd_global.user_id ,
75 X_LAST_UPDATE_LOGIN => fnd_global.user_id ,
76 X_PROGRAM_APPLICATION_ID => fnd_global.prog_appl_id ,
77 X_PROGRAM_ID => fnd_global.conc_program_id ,
78 X_OBJECT_VERSION_NUMBER => 1);
79 END LOOP;
80 END IF;
81 END IF;
82 END IF;
83 END create_kr_biz_location;
84
85
86 /*
87 ** PROCEDURE : UPDATE_KR_BIZ_LOCATION
88 **
89 ** HISTORY :
90 ** 23-NOV-2005 Yoshimichi Konishi Created.
91 **
92 ** WHAT IT DOES :
93 ** eBTax reporting codes created from HR location through synchronization hook
94 ** call is used by user when user associates reporting codes with eBTax tax rate
95 ** codes. Therefore when user updates HR location code, eBTax synch program will
96 ** check if there is an associated tax rate code exists for the location being
97 ** updated. If there is an associated tax rate code, eBTax hook shows user an
98 ** error message and it will not allow user to update location_code. User needs
99 ** to create a new location in this situation.
100 **
101 ** [ CASE 1 ]
102 ** When user updates location_code with a new code that does not exist in eBTax
103 ** reporting code table and the former location_code being updated is not
104 ** associated with tax rate code, eBTax synch hook creates a new reporting code
105 ** and deletes the Reporting Code of the former Location Code
106 **
107 ** i.e.
108 ** LOC_A -> LOC_B
109 ** LOC_A is not associated with rates.
110 ** LOC_B does not exist in reporting code
111 **
112 **
113 ** [ CASE 2 ]
114 ** When user updates location_code with the new code which does not exist in
115 ** eBTax reporting code table and the location_code being updated is associated
116 ** with tax rate code, eBTax synch hook shows user an error message and user
117 ** will not be able to update location_code.
118 **
119 ** i.e.
120 ** LOC_A -> LOC_B
121 ** LOC_A is associated with rates.
122 ** LOC_B does not exist in reporting code
123 **
124 ** [ CASE 3]
125 ** When user updates location_code with a code that already exists in eBTax
126 ** reporting code table and the former location_code being updated is not
127 ** associated with tax rate code, eBTax synch hook deletes former location_code
128 ** being updated.
129 **
130 ** i.e.
131 ** LOC_A -> LOC_B
132 ** LOC_A is not associated with rates.
133 ** LOC_B exists in reporting code
134 **
135 **
136 ** [ CASE 4 ]
137 ** When user updates location_code with a code that already exists in eBTax
138 ** reporting code table and the former location_code being updated is associated
139 ** with tax rate code, eBTax synch hook shows users an error message and user
140 ** will not be able to update location_code.
141 **
142 ** i.e.
143 ** LOC_A -> LOC_B
144 ** LOC_A is associated with rates.
145 ** LOC_B exists in reporting code
146 **
147 */
148 PROCEDURE update_kr_biz_location (p_location_code IN VARCHAR2,
149 p_location_code_o IN VARCHAR2,
150 p_country IN VARCHAR2,
151 p_location_id IN NUMBER)
152 IS
153 TYPE num15_tbl_type IS TABLE OF NUMBER(15);
154
155 l_reporting_code_id_tbl num15_tbl_type;
156 l_rowid VARCHAR2(4000);
157 l_reporting_code_id NUMBER(15);
158 l_reporting_type_id num15_tbl_type;
159 l_cnt_assoc_rep_codes PLS_INTEGER;
160 l_cnt_rep_codes PLS_INTEGER;
161 l_address_style VARCHAR2(7);
162
163 BEGIN
164
165
166 SELECT style
167 INTO l_address_style
168 FROM hr_locations_all
169 WHERE location_id = p_location_id;
170
171 IF p_location_code IS NOT NULL
172 AND (p_country = 'KR' OR l_address_style IN ('KR', 'KR_GLB'))
173 AND p_location_code <> p_location_code_o
174 THEN
175
176 l_cnt_rep_codes := 0;
177
178 SELECT reporting_type_id
179 BULK COLLECT INTO l_reporting_type_id
180 FROM zx_reporting_types_b
181 WHERE reporting_type_code = 'KR_BUSINESS_LOCATIONS';
182
183 IF l_reporting_type_id is not null
184 then
185 FOR k in Nvl(l_reporting_type_id.first,1)..Nvl(l_reporting_type_id.last,0) LOOP
186
187 SELECT count(reporting_code_char_value)
188 INTO l_cnt_rep_codes
189 FROM zx_reporting_codes_b
190 WHERE reporting_code_char_value = p_location_code
191 AND reporting_type_id = l_reporting_type_id(k);
192
193 IF l_cnt_rep_codes = 0 THEN
194 l_cnt_assoc_rep_codes := 0;
195 -- Is reporting code associated with tax rate code?
196 SELECT count(reporting_code_char_value)
197 INTO l_cnt_assoc_rep_codes
198 FROM zx_report_codes_assoc
199 WHERE reporting_code_char_value = p_location_code_o
200 AND reporting_type_id = l_reporting_type_id(k);
201
202 IF l_cnt_assoc_rep_codes = 0 THEN
203 -- CASE 1 : Insert and delete
204 SELECT zx_reporting_codes_b_s.nextval
205 INTO l_reporting_code_id
206 FROM dual;
207
208 ZX_REPORTING_CODES_PKG.INSERT_ROW (
209 X_ROWID => l_rowid ,
210 X_REPORTING_CODE_ID => l_reporting_code_id ,
211 X_REPORTING_CODE_CHAR_VALUE => p_location_code ,
212 X_REPORTING_CODE_NUM_VALUE => NULL ,
213 X_REPORTING_CODE_DATE_VALUE => NULL ,
214 X_REPORTING_TYPE_ID => l_reporting_type_id(k) ,
215 X_EXCEPTION_CODE => NULL ,
216 X_EFFECTIVE_FROM => sysdate ,
217 X_EFFECTIVE_TO => NULL ,
218 X_RECORD_TYPE_CODE => 'USER_DEFINED' ,
219 X_REQUEST_ID => fnd_global.conc_request_id ,
220 X_PROGRAM_LOGIN_ID => fnd_global.conc_login_id ,
221 X_REPORTING_CODE_NAME => p_location_code ,
222 X_CREATION_DATE => sysdate ,
223 X_CREATED_BY => fnd_global.user_id ,
224 X_LAST_UPDATE_DATE => sysdate ,
225 X_LAST_UPDATED_BY => fnd_global.user_id ,
226 X_LAST_UPDATE_LOGIN => fnd_global.user_id ,
227 X_PROGRAM_APPLICATION_ID => fnd_global.prog_appl_id ,
228 X_PROGRAM_ID => fnd_global.conc_program_id ,
229 X_OBJECT_VERSION_NUMBER => 1);
230
231 SELECT reporting_code_id
232 BULK COLLECT INTO
233 l_reporting_code_id_tbl
234 FROM zx_reporting_codes_b
235 WHERE reporting_code_char_value = p_location_code_o --NOTE
236 AND reporting_type_id = l_reporting_type_id(k);
237
238 FORALL i IN 1..l_reporting_code_id_tbl.count
239 DELETE FROM zx_reporting_codes_tl
240 WHERE reporting_code_id = l_reporting_code_id_tbl(i);
241
242 FORALL j IN 1..l_reporting_code_id_tbl.count
243 DELETE FROM zx_reporting_codes_b
244 WHERE reporting_code_id = l_reporting_code_id_tbl(j);
245 ELSE
246 -- CASE 2 : Raise an error
247 fnd_message.set_name('ZX', 'ZX_HR_KR_LOC_UPD_NOT_ALLOWED');
248 app_exception.raise_exception;
249 END IF;
250 ELSE
251 l_cnt_assoc_rep_codes := 0;
252 -- Is reporting code associated with tax rate code?
253 SELECT count(reporting_code_char_value)
254 INTO l_cnt_assoc_rep_codes
255 FROM zx_report_codes_assoc
256 WHERE reporting_code_char_value = p_location_code_o
257 AND reporting_type_id = l_reporting_type_id(k);
258
259 IF l_cnt_assoc_rep_codes = 0 THEN
260 -- CASE 3 : Delete old reporting_code
261 SELECT reporting_code_id
262 BULK COLLECT INTO
263 l_reporting_code_id_tbl
264 FROM zx_reporting_codes_b
265 WHERE reporting_code_char_value = p_location_code_o
266 AND reporting_type_id = l_reporting_type_id(k);
267
268 FORALL i IN 1..l_reporting_code_id_tbl.count
269 DELETE FROM zx_reporting_codes_tl
270 WHERE reporting_code_id = l_reporting_code_id_tbl(i);
271
272 FORALL j IN 1..l_reporting_code_id_tbl.count
273 DELETE FROM zx_reporting_codes_b
274 WHERE reporting_code_id = l_reporting_code_id_tbl(j);
275 ELSE
279 END IF;
276 -- CASE 4 : Raise an error
277 fnd_message.set_name('ZX', 'ZX_HR_KR_LOC_UPD_NOT_ALLOWED');
278 app_exception.raise_exception;
280 END IF;
281 END LOOP;
282 END IF;
283 END IF;
284
285
286 END update_kr_biz_location;
287
288
289 /*
290 ** PROCEDURE : DELETE_KR_BIZ_LOCATION
291 **
292 ** HISTORY :
293 ** 23-NOV-2005 Yoshimichi Konishi Created.
294 **
295 ** WHAT IT DOES :
296 ** Same as updating location_code, eBTax synch hook will check if the location
297 ** being deleted is associated with eBTax tax rate codes. If it is associated
298 ** then eBTax synch hook does not allow user to delete the location. If it is
299 ** not associated, the hook will delete the corresponding Reporting Code.
300 **
301 */
302 PROCEDURE delete_kr_biz_location (p_location_code_o IN VARCHAR2,
303 p_style_o IN VARCHAR2,
304 p_country_o IN VARCHAR2)
305 IS
306 TYPE num15_tbl_type IS TABLE OF NUMBER(15);
307
308 l_reporting_code_id_tbl num15_tbl_type;
309 l_cnt_assoc_rep_codes PLS_INTEGER;
310
311 BEGIN
312 IF p_location_code_o IS NOT NULL
313 AND
314 (p_style_o IN ('KR', 'KR_GBL') OR (p_country_o = 'KR'))
315 THEN
316 -- Is reporting code associated with tax rate code?
317 SELECT count(reporting_code_char_value)
318 INTO l_cnt_assoc_rep_codes
319 FROM zx_report_codes_assoc
320 WHERE reporting_code_char_value = p_location_code_o
321 AND reporting_type_id in (SELECT reporting_type_id
322 FROM zx_reporting_types_b
323 WHERE reporting_type_code = 'KR_BUSINESS_LOCATIONS');
324
325 IF l_cnt_assoc_rep_codes = 0 THEN
326 SELECT reporting_code_id
327 BULK COLLECT INTO
328 l_reporting_code_id_tbl
329 FROM zx_reporting_codes_b
330 WHERE reporting_code_char_value = p_location_code_o;
331 IF l_reporting_code_id_tbl IS NOT NULL
332 THEN
333 FORALL i IN 1..l_reporting_code_id_tbl.count
334 DELETE FROM zx_reporting_codes_tl
335 WHERE reporting_code_id = l_reporting_code_id_tbl(i);
336
337 FORALL j IN 1..l_reporting_code_id_tbl.count
338 DELETE FROM zx_reporting_codes_b
339 WHERE reporting_code_id = l_reporting_code_id_tbl(j);
340 ELSE
341 fnd_message.set_name('ZX', 'ZX_HR_KR_LOC_DEL_NOT_ALLOWED');
342 app_exception.raise_exception;
343 END IF;
344 END IF;
345 END IF;
346 END delete_kr_biz_location;
347
348 END zx_hr_locations_reporting_hook;
[Home] [Help]