[Home] [Help]
PACKAGE BODY: APPS.HZ_BES_BO_TRACKING_PVT
Source
1 PACKAGE BODY HZ_BES_BO_TRACKING_PVT AS
2 /*$Header: ARHBOTVB.pls 120.1 2005/09/01 19:16:04 smattegu noship $ */
3 ---------------------------------------------------------------------------
4 ---------------------------------------------------------------------------
5 G_DEBUG_PREFIX VARCHAR2(30) := 'HZ_BES_BOT_PVT';
6 ---------------------------------------------------------------------------
7 /*
8 List of internal procedures.
9 do_create_bot() -- this does actual insert into bot
10 do_val_mandatory() -- does check mandatory validations
11 do_val_gp() -- does check for existence of grand parent info
12 */
13 ---------------------------------------------------------------------------
14 ---------------------------------------------------------------------------
15 /*
16 Name: do_val_lkup
17 Scope: Internal
18 Purpose: to check the values of some columns
19 */
20 PROCEDURE do_val_lkup(
21 P_lkup_code IN VARCHAR2,
22 P_lkup_type IN VARCHAR2
23 )IS
24
25 -- cursor to identify if the given name is part of BOD or not.
26 CURSOR c_entity IS
27 SELECT distinct ENTITY_NAME
28 FROM HZ_BUS_OBJ_DEFINITIONS
29 WHERE entity_name = P_lkup_code;
30
31 -- cursor to check if there exists a valid code in the system for a given lookup type.
32 CURSOR C_bo_code IS
33 SELECT 1
34 FROM ar_lookups b
35 WHERE b.lookup_type = P_lkup_type
36 AND b.lookup_code = P_lkup_code;
37
38 -- temporary variables
39 l_tmp_var VARCHAR2(40);
40 l_tmp_no NUMBER;
41
42
43 BEGIN
44 -- Debug info.
45 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
46 hz_utility_v2pub.debug(
47 p_message=>'do_val_lkup()+',
48 p_prefix=>G_DEBUG_PREFIX,
49 p_msg_level=>fnd_log.level_procedure);
50 END IF;
51 /*
52 The following columns must have valid values.
53 P_PARENT_BO_CODE IN VARCHAR2,
54 P_CHILD_BO_CODE IN VARCHAR2,
55 P_CHILD_TBL_NAME IN VARCHAR2,
56 P_CHILD_OPR_FLAG IN VARCHAR2,
57 P_PARENT_OPR_FLAG IN VARCHAR2,
58 P_PARENT_TBL_NAME IN VARCHAR2,
59 p_GPARENT_BO_CODE IN VARCHAR2
60
61
62 CASE P_lkup_type
63 WHEN 'HZ_BUSINESS_OBJECTS' THEN
64 OPEN c_entity;
65 FETCH c_entity INTO l_tmp_var;
66 CLOSE c_entity;
67 IF l_tmp_var IS NOT NULL THEN
68 NULL;
69 ELSE
70 fnd_message.set_name('AR', 'HZ_API_INVALID_LOOKUP');
71 fnd_message.set_token('COLUMN' ,P_lkup_code);
72 fnd_message.set_token('LOOKUP_TYPE' ,P_lkup_type);
73 fnd_msg_pub.add;
74 RAISE FND_API.G_EXC_ERROR;
75 END IF;
76 WHEN 'INSERT_UPDATE_FLAG' THEN
77 IF P_lkup_code IN ('I','U') THEN
78 NULL;
79 ELSE
80 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
81 fnd_message.set_token('PARAMETER' ,P_lkup_code);
82 fnd_msg_pub.add;
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85 WHEN 'HZ_BUSINESS_ENTITIES' THEN
86 OPEN C_bo_code;
87 FETCH C_bo_code INTO l_tmp_no;
88 CLOSE C_bo_code;
89 IF l_tmp_no IS NOT NULL THEN
90 NULL;
91 ELSE
92 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
93 fnd_message.set_token('PARAMETER' ,P_lkup_code);
94 fnd_msg_pub.add;
95 RAISE FND_API.G_EXC_ERROR;
96 END IF;
97 ELSE
98 RAISE FND_API.G_EXC_ERROR;
99 END CASE;
100 */
101 -- Debug info.
102 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
103 hz_utility_v2pub.debug(
104 p_message=>'do_val_lkup()-',
105 p_prefix=>G_DEBUG_PREFIX,
106 p_msg_level=>fnd_log.level_procedure);
107 END IF;
108 END do_val_lkup;
109 ---------------------------------------------------------------------------
110 ---------------------------------------------------------------------------
111 /*
112 Name: do_create_bot
113 Scope: Internal
114 Purpose: to check mandatory validations
115 Input parameters: None. Will access all the input parameters of create_bot()
116 Output parameters: None.
117 */
118 PROCEDURE do_create_bot(
119 POPULATED_FLAG IN VARCHAR2,
120 p_CHILD_BO_CODE IN VARCHAR2,
121 P_CHILD_TBL_NAME IN VARCHAR2,
122 p_CHILD_ID IN NUMBER,
123 P_CHILD_OPR_FLAG IN VARCHAR2,
124 P_CHILD_UPDATE_DT IN DATE,
125 P_CREATION_DATE IN DATE,
126 p_PARENT_BO_CODE IN VARCHAR2,
127 P_PARENT_TBL_NAME IN VARCHAR2,
128 p_PARENT_ID IN NUMBER
129 )IS
130 l_child_rec_exists_no NUMBER;
131 BEGIN
132 -- Debug info.
133 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
134 hz_utility_v2pub.debug(
135 p_message=>'do_create_bot()+',
136 p_prefix=>G_DEBUG_PREFIX,
137 p_msg_level=>fnd_log.level_procedure);
138 END IF;
139 -- check for existence of the record.
140 -- if the record is not existing in BOT, only then insert.
141 BEGIN
142 l_child_rec_exists_no := 0;
143 SELECT child_id INTO l_child_rec_exists_no
144 FROM HZ_BUS_OBJ_TRACKING
145 WHERE event_id IS NULL
146 AND CHILD_ENTITY_NAME = P_CHILD_TBL_NAME
147 AND CHILD_ID = p_CHILD_ID
148 AND PARENT_ENTITY_NAME = P_PARENT_TBL_NAME
149 AND PARENT_BO_CODE = p_PARENT_BO_CODE
150 AND PARENT_ID = p_PARENT_ID;
151 IF l_child_rec_exists_no <> 0 THEN
152 -- data already exists, no need to write
153 hz_utility_v2pub.DEBUG
154 (p_message=> 'CHILD record already exists in BOT',
155 p_prefix=>G_DEBUG_PREFIX,
156 p_msg_level=>fnd_log.level_procedure);
157 END IF;
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160 INSERT INTO HZ_BUS_OBJ_TRACKING (
161 POPULATED_FLAG ,
162 CHILD_BO_CODE ,
163 CHILD_ENTITY_NAME ,
164 CHILD_ID ,
165 CHILD_OPERATION_FLAG ,
166 LAST_UPDATE_DATE ,
167 CREATION_DATE ,
168 PARENT_BO_CODE ,
169 PARENT_ENTITY_NAME ,
170 PARENT_ID )
171 VALUES (
172 POPULATED_FLAG ,
173 p_CHILD_BO_CODE ,
174 P_CHILD_TBL_NAME ,
175 p_CHILD_ID ,
176 P_CHILD_OPR_FLAG ,
177 P_CHILD_UPDATE_DT,
178 P_CREATION_DATE ,
179 p_PARENT_BO_CODE ,
180 P_PARENT_TBL_NAME,
181 p_PARENT_ID);
182 END; -- end of anonymous block
183 -- Debug info.
184 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
185 hz_utility_v2pub.debug(
186 p_message=>'do_create_bot()-',
187 p_prefix=>G_DEBUG_PREFIX,
188 p_msg_level=>fnd_log.level_procedure);
189 END IF;
190 END do_create_bot;
191 ---------------------------------------------------------------------------
192 /*
193 Name: do_val_gp
194 Scope: Internal
195 Purpose: to check mandatory validations
196 */
197 PROCEDURE do_val_gp(
198 P_CHILD_TBL_NAME IN VARCHAR2,
199 p_GPARENT_BO_CODE IN VARCHAR2,
200 P_GPARENT_TBL_NAME IN VARCHAR2,
201 p_GPARENT_ID IN NUMBER
202 )IS
203 BEGIN
204 -- Debug info.
205 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
206 hz_utility_v2pub.debug(
207 p_message=>'do_val_gp()+',
208 p_prefix=>G_DEBUG_PREFIX,
209 p_msg_level=>fnd_log.level_procedure);
210 END IF;
211
212 IF P_CHILD_TBL_NAME IN (
213 'RA_CUST_RECEIPT_METHODS',
214 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V')
215 THEN
216 CASE
217 WHEN LTRIM(RTRIM(p_GPARENT_BO_CODE)) IS NULL THEN
218 fnd_message.set_name('AR', 'HZ_API_NULL_PARAM');
219 fnd_message.set_token('PARAMETER' ,'p_GPARENT_BO_CODE');
220 fnd_msg_pub.add;
221 RAISE FND_API.G_EXC_ERROR;
222 WHEN (p_GPARENT_ID IS NULL OR p_GPARENT_ID = 0)THEN
223 fnd_message.set_name('AR', 'HZ_API_NULL_PARAM');
224 fnd_message.set_token('PARAMETER' ,'p_GPARENT_ID');
225 fnd_msg_pub.add;
226 RAISE FND_API.G_EXC_ERROR;
227 WHEN LTRIM(RTRIM(P_GPARENT_TBL_NAME)) IS NULL THEN
228 fnd_message.set_name('AR', 'HZ_API_NULL_PARAM');
229 fnd_message.set_token('PARAMETER' ,'P_GPARENT_TBL_NAME');
230 fnd_msg_pub.add;
231 RAISE FND_API.G_EXC_ERROR;
232 ELSE NULL;
233 END CASE;
234 END IF ;
235
236 -- Debug info.
237 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
238 hz_utility_v2pub.debug(
239 p_message=>'do_val_gp()-',
240 p_prefix=>G_DEBUG_PREFIX,
241 p_msg_level=>fnd_log.level_procedure);
242 END IF;
243 END do_val_gp;
244 ---------------------------------------------------------------------------
245 ---------------------------------------------------------------------------
246 /*
247 Name: do_val_mandatory
248 Scope: Internal
249 Purpose: to check mandatory validations
250 */
251 PROCEDURE do_val_mandatory(
252 P_CHILD_TBL_NAME IN VARCHAR2,
253 P_CHILD_ID IN NUMBER,
254 P_CHILD_OPR_FLAG IN VARCHAR2,
255 P_CHILD_UPDATE_DT IN DATE,
256 P_PARENT_TBL_NAME IN VARCHAR2,
257 P_PARENT_ID IN NUMBER
258 )IS
259 BEGIN
260 -- Debug info.
261 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
262 hz_utility_v2pub.debug(
263 p_message=>'do_val_mandatory()+',
264 p_prefix=>G_DEBUG_PREFIX,
265 p_msg_level=>fnd_log.level_procedure);
266 END IF;
267 /*
268 The following columns must be present.
269 P_CHILD_TBL_NAME
270 P_CHILD_ID
271 P_CHILD_OPR_FLAG
272 P_CHILD_UPDATE_DT
273 P_PARENT_TBL_NAME
274 P_PARENT_ID
275 */
276 CASE
277 WHEN LTRIM(RTRIM(P_CHILD_TBL_NAME)) IS NULL THEN
278 fnd_message.set_name('AR', 'HZ_API_NULL_PARAM');
279 fnd_message.set_token('PARAMETER' ,'P_CHILD_TBL_NAME');
280 fnd_msg_pub.add;
281 RAISE FND_API.G_EXC_ERROR;
282 WHEN (P_CHILD_ID IS NULL OR P_CHILD_ID = 0)THEN
283 fnd_message.set_name('AR', 'HZ_API_NULL_PARAM');
284 fnd_message.set_token('PARAMETER' ,'P_CHILD_ID');
285 fnd_msg_pub.add;
286 RAISE FND_API.G_EXC_ERROR;
287 WHEN LTRIM(RTRIM(P_CHILD_OPR_FLAG)) IS NULL THEN
288 fnd_message.set_name('AR', 'HZ_API_NULL_PARAM');
289 fnd_message.set_token('PARAMETER' ,'P_CHILD_OPR_FLAG');
290 fnd_msg_pub.add;
291 RAISE FND_API.G_EXC_ERROR;
292 WHEN P_CHILD_UPDATE_DT IS NULL THEN
293 fnd_message.set_name('AR', 'HZ_API_NULL_PARAM');
294 fnd_message.set_token('PARAMETER' ,'P_CHILD_UPDATE_DT');
295 fnd_msg_pub.add;
296 RAISE FND_API.G_EXC_ERROR;
297 WHEN P_PARENT_TBL_NAME IS NULL THEN
298 fnd_message.set_name('AR', 'HZ_API_NULL_PARAM');
299 fnd_message.set_token('PARAMETER' ,'P_PARENT_TBL_NAME');
300 fnd_msg_pub.add;
301 RAISE FND_API.G_EXC_ERROR;
302 WHEN (P_PARENT_ID IS NULL OR P_PARENT_ID = 0)THEN
303 fnd_message.set_name('AR', 'HZ_API_NULL_PARAM');
304 fnd_message.set_token('PARAMETER' ,'P_PARENT_ID');
305 fnd_msg_pub.add;
306 RAISE FND_API.G_EXC_ERROR;
307 ELSE NULL;
308 END CASE;
309 -- Debug info.
310 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
311 hz_utility_v2pub.debug(
312 p_message=>'do_val_mandatory()-',
313 p_prefix=>G_DEBUG_PREFIX,
314 p_msg_level=>fnd_log.level_procedure);
315 END IF;
316 END do_val_mandatory;
317 ---------------------------------------------------------------------------
318 ---------------------------------------------------------------------------
319 PROCEDURE create_bot(
320 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
321 P_CHILD_BO_CODE IN VARCHAR2,
322 P_CHILD_TBL_NAME IN VARCHAR2,
323 P_CHILD_ID IN NUMBER,
324 P_CHILD_OPR_FLAG IN VARCHAR2,
325 P_CHILD_UPDATE_DT IN DATE,
326 P_PARENT_BO_CODE IN VARCHAR2,
327 P_PARENT_TBL_NAME IN VARCHAR2,
328 P_PARENT_ID IN NUMBER,
329 P_PARENT_OPR_FLAG IN VARCHAR2,
330 P_GPARENT_BO_CODE IN VARCHAR2,
331 P_GPARENT_TBL_NAME IN VARCHAR2,
332 P_GPARENT_ID IN NUMBER,
333 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
334 X_MSG_COUNT OUT NOCOPY NUMBER,
335 X_MSG_DATA OUT NOCOPY VARCHAR2
336 )IS
337
338 BEGIN
339
340 -- initialize API return status to success.
341 x_return_status := FND_API.G_RET_STS_SUCCESS;
342
343 -- Initialize message list if p_init_msg_list is set to TRUE
344 IF FND_API.to_Boolean(p_init_msg_list) THEN
345 FND_MSG_PUB.initialize;
346 END IF;
347
348 -- Debug info.
349 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
350 hz_utility_v2pub.debug(
351 p_message=>'create_bot()+',
352 p_prefix=>G_DEBUG_PREFIX,
353 p_msg_level=>fnd_log.level_procedure);
354 END IF;
355
356 SAVEPOINT cre_bot;
357
358 /* Flow:
359 . do the validations
360 . after existence checking,
361 . insert into BOT (child info, parent info)
362 . after existence checking,
363 . insert into BOT (paremt info as child info, grant parent info as parent info)
364 */
365
366 /* To insert first record,
367 . do the validations by calling the do_val_mandatory() procedure
368 . insert BOT (child info, parent info) by calling do_create_bot
369 */
370
371 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
372 do_val_mandatory(
373 P_CHILD_TBL_NAME ,
374 P_CHILD_ID ,
375 P_CHILD_OPR_FLAG ,
376 P_CHILD_UPDATE_DT,
377 P_PARENT_TBL_NAME,
378 P_PARENT_ID);
379
380 do_create_bot(
381 POPULATED_FLAG => 'N',
382 p_CHILD_BO_CODE => p_CHILD_BO_CODE,
383 P_CHILD_TBL_NAME => P_CHILD_TBL_NAME,
384 p_CHILD_ID => p_CHILD_ID,
385 P_CHILD_OPR_FLAG => P_CHILD_OPR_FLAG,
386 P_CHILD_UPDATE_DT => P_CHILD_UPDATE_DT,
387 P_CREATION_DATE => P_CHILD_UPDATE_DT,
388 p_PARENT_BO_CODE => p_PARENT_BO_CODE,
389 P_PARENT_TBL_NAME => P_PARENT_TBL_NAME,
390 p_PARENT_ID => p_PARENT_ID);
391
392 /* As a second step, create the parent, grand parent info in BOT
393 . validate the grand parent info by calling do_val_gp()
394 . then, if rec bot existing, insert a record in BOT as.
395 bot.child info => parent info
396 bot.parent info => grand parent info
397 */
398
399 do_val_gp(
400 P_CHILD_TBL_NAME ,
404
401 p_GPARENT_BO_CODE ,
402 P_GPARENT_TBL_NAME ,
403 p_GPARENT_ID);
405 do_create_bot(
406 POPULATED_FLAG => 'Y',
407 p_CHILD_BO_CODE => p_PARENT_BO_CODE,
408 P_CHILD_TBL_NAME => P_PARENT_TBL_NAME,
409 p_CHILD_ID => p_PARENT_ID,
410 P_CHILD_OPR_FLAG => 'U',
411 P_CHILD_UPDATE_DT => P_CHILD_UPDATE_DT,
412 P_CREATION_DATE => P_CHILD_UPDATE_DT,
413 p_PARENT_BO_CODE => P_GPARENT_BO_CODE,
414 P_PARENT_TBL_NAME => P_GPARENT_TBL_NAME,
415 p_PARENT_ID => P_GPARENT_ID);
416
417 END IF; -- profile check
418
419 -- Debug info.
420 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
421 hz_utility_v2pub.debug(
422 p_message=>'create_bot()-',
423 p_prefix=>G_DEBUG_PREFIX,
424 p_msg_level=>fnd_log.level_procedure);
425 END IF;
426
427
428 EXCEPTION
429
430 WHEN fnd_api.g_exc_error THEN
431 ROLLBACK TO cre_bot;
432 x_return_status := fnd_api.g_ret_sts_error;
433
434 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
435 p_count => x_msg_count,
436 p_data => x_msg_data);
437
438 -- Debug info.
439 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
440 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
441 p_msg_data=>x_msg_data,
442 p_msg_type=>'ERROR',
443 p_msg_level=>fnd_log.level_error);
444 END IF;
445 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
446 hz_utility_v2pub.debug(p_message=>'create_bot()-',
447 p_prefix=>G_DEBUG_PREFIX,
448 p_msg_level=>fnd_log.level_procedure);
449 END IF;
450 WHEN fnd_api.g_exc_unexpected_error THEN
451 ROLLBACK TO cre_bot;
452 x_return_status := fnd_api.g_ret_sts_unexp_error;
453
454 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
455 p_count => x_msg_count,
456 p_data => x_msg_data);
457
458 -- Debug info.
459 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
460 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
461 p_msg_data=>x_msg_data,
462 p_msg_type=>'UNEXPECTED ERROR',
463 p_msg_level=>fnd_log.level_error);
464 END IF;
465 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
466 hz_utility_v2pub.debug(p_message=>'create_bot()-',
467 p_prefix=>G_DEBUG_PREFIX,
468 p_msg_level=>fnd_log.level_procedure);
469 END IF;
470 WHEN OTHERS THEN
471 ROLLBACK TO cre_bot;
472 x_return_status := fnd_api.g_ret_sts_unexp_error;
473
474 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
475 fnd_message.set_token('ERROR' ,SQLERRM);
476 fnd_msg_pub.add;
477
478 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
479 p_count => x_msg_count,
480 p_data => x_msg_data);
481
482 -- Debug info.
483 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
484 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
485 p_msg_data=>x_msg_data,
486 p_msg_type=>'SQL ERROR',
487 p_msg_level=>fnd_log.level_error);
488 END IF;
489 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
490 hz_utility_v2pub.debug(p_message=>'create_bot()-',
491 p_prefix=>G_DEBUG_PREFIX,
492 p_msg_level=>fnd_log.level_procedure);
493 END IF;
494 END create_bot;
495 ---------------------------------------------------------------------------
496 END HZ_BES_BO_TRACKING_PVT;