1 PACKAGE BODY HZ_POPULATE_BOT_PKG AS
2 /*$Header: ARHPBOTB.pls 120.19 2006/03/06 19:59:53 acng noship $ */
3
4 PROCEDURE pop_parent_record(
5 p_child_id IN NUMBER, -- child Id
6 p_lud IN DATE, -- last update date
7 p_centity_name IN VARCHAR2, -- child entity name
8 p_cbo_code IN VARCHAR2, -- child business object code
9 p_parent_id IN NUMBER, -- parent Id
10 p_pentity_name IN VARCHAR2, -- parent entity name
11 p_pbo_code IN VARCHAR2 -- parent business object code
12 );
13
14 FUNCTION is_valid_ps(
15 p_party_site_id IN NUMBER
16 ) RETURN BOOLEAN;
17
18 -----------------------------------------------------------------
19 -- Private Procedure name: pop_parent_record()
20 -- Purpose: populates parent record to bot tracking table
21 -- Scope: internal
22 -- Called From: procedure in this package
23 -- Input Parameters:
24 -- p_party_id -- person party_id
25 -- p_lud -- last update date
26 -----------------------------------------------------------------
27 -- operation is always U because parent record must exist before child
28 -- record can be created/updated
29 PROCEDURE pop_parent_record(
30 p_child_id IN NUMBER,
31 p_lud IN DATE,
32 p_centity_name IN VARCHAR2,
33 p_cbo_code IN VARCHAR2,
34 p_parent_id IN NUMBER,
35 p_pentity_name IN VARCHAR2,
36 p_pbo_code IN VARCHAR2) IS
37 -- local variables
38 l_child_rec_exists_no NUMBER;
39 l_debug_prefix VARCHAR2(40) := 'pop_parent_record';
40 BEGIN
41 hz_utility_v2pub.DEBUG(p_message=>'pop_parent_record+',
42 p_prefix=>l_debug_prefix,
43 p_msg_level=>fnd_log.level_procedure);
44
45 BEGIN
46 l_child_rec_exists_no := 0;
47 SELECT child_id INTO l_child_rec_exists_no
48 FROM HZ_BUS_OBJ_TRACKING
49 WHERE event_id IS NULL
50 AND CHILD_ENTITY_NAME = p_centity_name
51 AND CHILD_BO_CODE = p_cbo_code
52 AND CHILD_ID = p_child_id
53 AND nvl(PARENT_ID,-99) = nvl(p_parent_id,-99)
54 AND nvl(PARENT_BO_CODE,'X') = nvl(p_pbo_code,'X')
55 AND rownum = 1;
56
57 IF l_child_rec_exists_no <> 0 THEN
58 -- data already exists, no need to write
59 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
60 p_prefix=>l_debug_prefix,
61 p_msg_level=>fnd_log.level_procedure);
62 END IF;
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 INSERT INTO HZ_BUS_OBJ_TRACKING
66 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
67 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
68 ) VALUES (
69 'Y', 'U', p_child_id, p_centity_name, p_cbo_code,
70 p_lud, p_lud, p_pentity_name, p_parent_id, p_pbo_code);
71 END;
72
73 hz_utility_v2pub.DEBUG(p_message=>'pop_parent_record-',
74 p_prefix=>l_debug_prefix,
75 p_msg_level=>fnd_log.level_procedure);
76 EXCEPTION
77 WHEN NO_DATA_FOUND THEN
78 NULL;
79 END pop_parent_record;
80
81 -----------------------------------------------------------------
82 -- Procedure name: pop_hz_work_class()
83 -- Purpose: populates BOT for HZ_WORK_CLASS create or update
84 -- Scope: internal
85 -- Called From: V2 API
86 -- Called By: HZ_WORK_CLASS create or update APIs
87 -- Input Parameters:
88 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
89 -- p_work_class_id IN NUMBER
90 --
91 -- Note:
92 -- Following are the allowed PARENT and CHILD BO combinations for HZ_WORK_CLASS
93 -- This procedure must ensure that the combination is valid before populating BOT
94 -- PARENT BO: EMP_HIST :: CHILD BO:
95 -----------------------------------------------------------------
96 PROCEDURE pop_hz_work_class(p_operation IN VARCHAR2, p_work_class_id IN NUMBER) IS
97
98 -- cursor statement to select the info from child table
99 CURSOR c_child IS
100 SELECT wc.LAST_UPDATE_DATE lud, wc.EMPLOYMENT_HISTORY_ID parent_id, eh.party_id party_id,
101 wc.WORK_CLASS_ID child_id
102 FROM HZ_WORK_CLASS wc, HZ_EMPLOYMENT_HISTORY eh, HZ_PARTIES p
103 WHERE wc.WORK_CLASS_ID = P_WORK_CLASS_ID
104 AND wc.employment_history_id = eh.employment_history_id
105 AND eh.party_id = p.party_id
106 AND p.party_type = 'PERSON';
107
108 -- local variables
109 l_debug_prefix VARCHAR2(40) := 'pop_hz_work_class';
110 l_parent_id NUMBER; -- used to store parent entity identifier
111 l_bo_code VARCHAR2(30); -- used to store BO Code
112 l_child_bo_code VARCHAR2(30); -- used to store Child BO Code (if child entity is a root node)
113 l_child_id NUMBER; -- used to store HZ_WORK_CLASS identifier
114 l_lud DATE; -- used to store the child last update date
115 l_child_rec_exists_no NUMBER;
116 l_party_id NUMBER; -- person party_id of employment history record
117 l_cen VARCHAR2(30) := 'HZ_WORK_CLASS';
118 BEGIN
119 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_work_class+',
120 p_prefix=>l_debug_prefix,
121 p_msg_level=>fnd_log.level_procedure);
122
123 -- validate p_operation
124 IF p_operation IN ('I','U') THEN
125 NULL;
126 ELSE
127 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_work_class',
128 p_prefix=>l_debug_prefix,
129 p_msg_level=>fnd_log.level_procedure);
130 RAISE FND_API.G_EXC_ERROR;
131 END IF;
132
133 FOR c_child_rec IN c_child LOOP
134 -- collect the child info into variables
135 l_lud := c_child_rec.lud;
136 l_parent_id := c_child_rec.parent_id;
137 l_child_id := c_child_rec.child_id;
138 l_party_id := c_child_rec.party_id;
139
140 -- if record not existing for work class, insert into hz_bus_obj_tracking
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 = l_cen
147 AND CHILD_ID = l_child_id
148 AND rownum = 1;
149
150 IF l_child_rec_exists_no <> 0 THEN
151 -- data already exists, no need to write
152 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
153 p_prefix=>l_debug_prefix,
154 p_msg_level=>fnd_log.level_procedure);
155 END IF;
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 INSERT INTO HZ_BUS_OBJ_TRACKING
159 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
160 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
161 ) VALUES (
162 'N', p_operation, l_child_id, 'HZ_WORK_CLASS', NULL,
163 l_lud, l_lud, 'HZ_EMPLOYMENT_HISTORY', l_parent_id, 'EMP_HIST');
164
165 -- if record not existing for employment history, insert into hz_bus_obj_tracking
166 pop_parent_record(p_child_id => l_parent_id,
167 p_lud => l_lud,
168 p_centity_name => 'HZ_EMPLOYMENT_HISTORY',
169 p_cbo_code => 'EMP_HIST',
170 p_parent_id => l_party_id,
171 p_pentity_name => 'HZ_PARTIES',
172 p_pbo_code => 'PERSON');
173
174 -- if record not existing for person of employment history, insert into hz_bus_obj_tracking
175 pop_parent_record(p_child_id => l_party_id,
176 p_lud => l_lud,
177 p_centity_name => 'HZ_PARTIES',
178 p_cbo_code => 'PERSON',
179 p_parent_id => NULL,
180 p_pentity_name => NULL,
181 p_pbo_code => NULL);
182 END; -- anonymous block end
183 END LOOP;
184
185 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_work_class-',
186 p_prefix=>l_debug_prefix,
187 p_msg_level=>fnd_log.level_procedure);
188 EXCEPTION
189 WHEN OTHERS THEN
190 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
191 p_prefix=>l_debug_prefix,
192 p_msg_level=>fnd_log.level_procedure);
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
194 END pop_hz_work_class;
195
196 -----------------------------------------------------------------
197 -- Procedure name: pop_hz_role_responsibility()
198 -- Purpose: populates BOT for HZ_ROLE_RESPONSIBILITY create or update
199 -- Scope: internal
200 -- Called From: V2 API
201 -- Called By: HZ_ROLE_RESPONSIBILITY create or update APIs
202 -- Input Parameters:
203 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
204 -- p_responsibility_id IN NUMBER
205 --
206 -- Note:
207 -- Following are the allowed PARENT and CHILD BO combinations for HZ_ROLE_RESPONSIBILITY
208 -- This procedure must ensure that the combination is valid before populating BOT
209 --
210 -- PARENT BO: CUST_ACCT_CONTACT :: CHILD BO:
211 -----------------------------------------------------------------
212 PROCEDURE pop_hz_role_responsibility(p_operation IN VARCHAR2, p_responsibility_id IN NUMBER) IS
213
214 -- cursor statement to select the info from child table
215 CURSOR c_child IS
216 SELECT rr.LAST_UPDATE_DATE lud, rr.CUST_ACCOUNT_ROLE_ID parent_id, rr.RESPONSIBILITY_ID child_id,
217 nvl(car.cust_acct_site_id, car.cust_account_id) car_parent_id,
218 decode(car.cust_acct_site_id, null, 'HZ_CUST_ACCOUNTS', 'HZ_CUST_ACCT_SITES_ALL') car_parent_entity,
219 decode(car.cust_acct_site_id, null, 'CUST_ACCT', 'CUST_ACCT_SITE') car_parent_bo
220 FROM HZ_ROLE_RESPONSIBILITY rr, HZ_CUST_ACCOUNT_ROLES car
221 WHERE rr.RESPONSIBILITY_ID = P_RESPONSIBILITY_ID
222 AND rr.cust_account_role_id = car.cust_account_role_id
223 AND car.cust_account_id > 0;
224
225 -- local variables
226 l_debug_prefix VARCHAR2(40) := 'pop_hz_role_responsibility';
227 l_parent_id NUMBER; -- used to store parent entity identifier
228 l_child_id NUMBER; -- used to store HZ_ROLE_RESPONSIBILITY identifier
229 l_lud DATE; -- used to store the child last update date
230 l_child_rec_exists_no NUMBER;
231 l_car_parent_id NUMBER; -- parent Id of cust account roles
232 l_car_parent_entity VARCHAR2(30); -- entity name of cust account roles parent
233 l_car_parent_bo VARCHAR2(30); -- business object of cust account roles parent
234 l_cen VARCHAR2(30) := 'HZ_ROLE_RESPONSIBILITY';
235 BEGIN
236 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_role_responsibility+',
237 p_prefix=>l_debug_prefix,
238 p_msg_level=>fnd_log.level_procedure);
239
240 -- validate p_operation
241 IF p_operation IN ('I','U') THEN
242 NULL;
243 ELSE
244 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_role_responsibility',
245 p_prefix=>l_debug_prefix,
246 p_msg_level=>fnd_log.level_procedure);
247 RAISE FND_API.G_EXC_ERROR;
248 END IF;
249
250 FOR c_child_rec IN C_child LOOP
251 -- collect the child info into variables
252 l_lud := c_child_rec.lud;
253 l_parent_id := c_child_rec.parent_id;
254 l_child_id := c_child_rec.child_id;
255 l_car_parent_id := c_child_rec.car_parent_id;
256 l_car_parent_entity := c_child_rec.car_parent_entity;
257 l_car_parent_bo := c_child_rec.car_parent_bo;
258
259 -- if record not existing, insert into hz_bus_obj_tracking
260 BEGIN
261 l_child_rec_exists_no := 0;
262 SELECT child_id INTO l_child_rec_exists_no
263 FROM HZ_BUS_OBJ_TRACKING
264 WHERE event_id IS NULL
265 AND CHILD_ENTITY_NAME = l_cen
266 AND CHILD_ID = l_child_id
267 AND rownum = 1;
268
269 IF l_child_rec_exists_no <> 0 THEN
270 -- data already exists, no need to write
271 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
272 p_prefix=>l_debug_prefix,
273 p_msg_level=>fnd_log.level_procedure);
274 END IF;
275 EXCEPTION
276 WHEN NO_DATA_FOUND THEN
277 INSERT INTO HZ_BUS_OBJ_TRACKING
278 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
282 l_lud, l_lud, 'HZ_CUST_ACCOUNT_ROLES', l_parent_id, 'CUST_ACCT_CONTACT');
279 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
280 ) VALUES (
281 'N', p_operation, l_child_id, 'HZ_ROLE_RESPONSIBILITY', NULL,
283
284 -- if record not existing for customer account contact, insert into hz_bus_obj_tracking
285 pop_parent_record(p_child_id => l_parent_id,
286 p_lud => l_lud,
287 p_centity_name => 'HZ_CUST_ACCOUNT_ROLES',
288 p_cbo_code => 'CUST_ACCT_CONTACT',
289 p_parent_id => l_car_parent_id,
290 p_pentity_name => l_car_parent_entity,
291 p_pbo_code => l_car_parent_bo);
292 END ; -- anonymous block end
293 END LOOP;
294
295 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_role_responsibility-',
296 p_prefix=>l_debug_prefix,
297 p_msg_level=>fnd_log.level_procedure);
298 EXCEPTION
299 WHEN OTHERS THEN
300 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
301 p_prefix=>l_debug_prefix,
302 p_msg_level=>fnd_log.level_procedure);
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304 END pop_hz_role_responsibility;
305
306 -----------------------------------------------------------------
307 -- Procedure name: pop_hz_relationships()
308 -- Purpose: populates BOT for HZ_RELATIONSHIPS create or update
309 -- Scope: internal
310 -- Called From: V2 API
311 -- Called By: HZ_RELATIONSHIPS create or update APIs
312 -- Input Parameters:
313 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
314 -- p_RELATIONSHIP_ID IN NUMBER
315 --
316 -- Note:
317 -- Following are the allowed PARENT and CHILD BO combinations for HZ_RELATIONSHIPS
318 -- This procedure must ensure that the combination is valid before populating BOT
319 --
320 -- PARENT BO: ORG :: CHILD BO:
321 -- PARENT BO: PERSON :: CHILD BO:
322 -- PARENT BO: ORG_CONTACT :: CHILD BO:
323 -----------------------------------------------------------------
324 PROCEDURE pop_hz_relationships(p_operation IN VARCHAR2, p_relationship_id IN NUMBER) IS
325
326 CURSOR c_child IS
327 SELECT pp.LAST_UPDATE_DATE lud, pp.subject_id sparent_id, pp.object_id oparent_id,
328 pp.RELATIONSHIP_ID child_id,
329 decode(pp.subject_type, 'ORGANIZATION', 'ORG', 'PERSON', 'PERSON', NULL) sbo_code,
330 decode(pp.object_type, 'ORGANIZATION', 'ORG', 'PERSON', 'PERSON', NULL) obo_code
331 FROM HZ_RELATIONSHIPS pp
332 WHERE pp.RELATIONSHIP_ID = p_RELATIONSHIP_ID
333 AND subject_type in ('ORGANIZATION','PERSON')
334 AND object_type in ('ORGANIZATION','PERSON');
335
336 CURSOR c_get_oc_id IS
337 SELECT org_contact_id
338 FROM HZ_ORG_CONTACTS
339 WHERE party_relationship_id = p_relationship_id;
340
341 -- local variables
342 l_debug_prefix VARCHAR2(40) := 'pop_hz_relationships';
343 l_subj_id NUMBER; -- used to store subject entity identifier
344 l_obj_id NUMBER; -- used to store object entity identifier
345 l_sbo_code VARCHAR2(30); -- used to store subject BO Code
346 l_obo_code VARCHAR2(30); -- used to store object BO Code
347 l_child_id NUMBER; -- used to store HZ_RELATIONSHIPS identifier
348 l_lud DATE; -- used to store the child last update date
349 l_oc_id NUMBER;
350 l_child_rec_exists_no NUMBER;
351 l_cen VARCHAR2(30) := 'HZ_RELATIONSHIPS';
352 BEGIN
353 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_relationships+',
354 p_prefix=>l_debug_prefix,
355 p_msg_level=>fnd_log.level_procedure);
356
357 -- validate p_operation
358 IF p_operation IN ('I','U') THEN
359 NULL;
360 ELSE
361 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_relationships',
362 p_prefix=>l_debug_prefix,
363 p_msg_level=>fnd_log.level_procedure);
364 RAISE FND_API.G_EXC_ERROR;
365 END IF;
366
367 FOR c_child_rec IN c_child LOOP
368 l_lud := c_child_rec.lud;
369 l_subj_id := c_child_rec.sparent_id;
370 l_obj_id := c_child_rec.oparent_id;
371 l_sbo_code := c_child_rec.sbo_code;
372 l_obo_code := c_child_rec.obo_code;
373 l_child_id := c_child_rec.child_id;
374
375 -- if record not existing, insert into hz_bus_obj_tracking
376 BEGIN
377 l_child_rec_exists_no := 0;
378 SELECT child_id INTO l_child_rec_exists_no
379 FROM HZ_BUS_OBJ_TRACKING
380 WHERE event_id IS NULL
381 AND CHILD_ENTITY_NAME = l_cen
382 AND CHILD_ID = l_child_id
383 AND rownum = 1;
384
385 IF l_child_rec_exists_no <> 0 THEN
386 -- data already exists, no need to write
387 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
388 p_prefix=>l_debug_prefix,
389 p_msg_level=>fnd_log.level_procedure);
390 END IF;
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 -- for subject
394 INSERT INTO HZ_BUS_OBJ_TRACKING
395 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
396 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
397 ) VALUES (
398 'N', p_operation, l_child_id, 'HZ_RELATIONSHIPS', NULL,
399 l_lud, l_lud, 'HZ_PARTIES', l_subj_id, l_sbo_code);
400
404 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
401 -- for object
402 INSERT INTO HZ_BUS_OBJ_TRACKING
403 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
405 ) VALUES (
406 'N', p_operation, l_child_id, 'HZ_RELATIONSHIPS', NULL,
407 l_lud, l_lud, 'HZ_PARTIES', l_obj_id, l_obo_code);
408
409 -- if record not existing for customer account contact, insert into hz_bus_obj_tracking
410 pop_parent_record(p_child_id => l_subj_id,
411 p_lud => l_lud,
412 p_centity_name => 'HZ_PARTIES',
413 p_cbo_code => l_sbo_code,
414 p_parent_id => NULL,
415 p_pentity_name => NULL,
416 p_pbo_code => NULL);
417
418 pop_parent_record(p_child_id => l_obj_id,
419 p_lud => l_lud,
420 p_centity_name => 'HZ_PARTIES',
421 p_cbo_code => l_obo_code,
422 p_parent_id => NULL,
423 p_pentity_name => NULL,
424 p_pbo_code => NULL);
425
426 IF(p_operation = 'U') THEN
427 IF(l_sbo_code = 'PERSON' AND l_obo_code = 'ORGANIZATION') THEN
428 OPEN c_get_oc_id;
429 FETCH c_get_oc_id INTO l_oc_id;
430 CLOSE c_get_oc_id;
431 pop_hz_org_contacts(
432 p_operation => 'U',
433 p_org_contact_id => l_oc_id);
434 END IF;
435 END IF;
436 END ; -- anonymous block end
437 END LOOP;
438
439 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_relationships-',
440 p_prefix=>l_debug_prefix,
441 p_msg_level=>fnd_log.level_procedure);
442 EXCEPTION
443 WHEN OTHERS THEN
444 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
445 p_prefix=>l_debug_prefix,
446 p_msg_level=>fnd_log.level_procedure);
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448 END pop_hz_relationships;
449
450 -----------------------------------------------------------------
451 -- Procedure name: pop_hz_person_profiles()
452 -- Purpose: populates BOT for HZ_PERSON_PROFILES create or update
453 -- Scope: internal
454 -- Called From: V2 API
455 -- Called By: HZ_PERSON_PROFILES create or update APIs
456 -- Input Parameters:
457 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
458 -- p_person_profile_id IN NUMBER
459 --
460 -- Note:
461 -- Following are the allowed PARENT and CHILD BO combinations for HZ_PERSON_PROFILES
462 -- This procedure must ensure that the combination is valid before populating BOT
463 --
464 -- PARENT BO: PERSON :: CHILD BO:
465 -- PARENT BO: PERSON_CONTACT :: CHILD BO:
466 -----------------------------------------------------------------
467 PROCEDURE pop_hz_person_profiles(p_operation IN VARCHAR2, p_person_profile_id IN NUMBER) IS
468
469 -- cursor statement to select the info from child table
470 CURSOR c_child IS
471 SELECT LAST_UPDATE_DATE lud, PARTY_ID parent_id, PERSON_PROFILE_ID child_id
472 FROM HZ_PERSON_PROFILES
473 WHERE PERSON_PROFILE_ID = P_PERSON_PROFILE_ID;
474
475 -- local variables
476 l_debug_prefix VARCHAR2(40) := 'pop_hz_person_profiles';
477 l_parent_id NUMBER; -- used to store parent entity identifier
478 l_child_id NUMBER; -- used to store HZ_PERSON_PROFILES identifier
479 l_lud DATE; -- used to store the child last update date
480 l_child_rec_exists_no NUMBER;
481 l_org_contact_id NUMBER;
482 l_cen VARCHAR2(30) := 'HZ_PERSON_PROFILES';
483 BEGIN
484 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_person_profiles+',
485 p_prefix=>l_debug_prefix,
486 p_msg_level=>fnd_log.level_procedure);
487
488 -- validate p_operation
489 IF p_operation IN ('I','U') THEN
490 NULL;
491 ELSE
492 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_person_profiles',
493 p_prefix=>l_debug_prefix,
494 p_msg_level=>fnd_log.level_procedure);
495 RAISE FND_API.G_EXC_ERROR;
496 END IF;
497
498 FOR c_child_rec IN c_child LOOP
499 -- collect the child info into variables
500 l_lud := c_child_rec.lud;
501 l_child_id := c_child_rec.child_id;
502 l_parent_id := c_child_rec.parent_id;
503
504 -- if record not existing, insert into hz_bus_obj_tracking
505 BEGIN
506 l_child_rec_exists_no := 0;
507 SELECT child_id INTO l_child_rec_exists_no
508 FROM HZ_BUS_OBJ_TRACKING
509 WHERE event_id IS NULL
510 AND CHILD_ENTITY_NAME = l_cen
511 AND CHILD_ID = l_child_id
512 AND rownum = 1;
513
514 IF l_child_rec_exists_no <> 0 THEN
515 -- data already exists, no need to write
516 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
517 p_prefix=>l_debug_prefix,
518 p_msg_level=>fnd_log.level_procedure);
519 END IF;
520 EXCEPTION
521 WHEN NO_DATA_FOUND THEN
522 INSERT INTO HZ_BUS_OBJ_TRACKING
523 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
524 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
525 ) VALUES (
529 -- populate person party record
526 'N', p_operation, l_child_id, 'HZ_PERSON_PROFILES', NULL,
527 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, 'PERSON');
528
530 pop_parent_record(p_child_id => l_parent_id,
531 p_lud => l_lud,
532 p_centity_name => 'HZ_PARTIES',
533 p_cbo_code => 'PERSON',
534 p_parent_id => NULL,
535 p_pentity_name => NULL,
536 p_pbo_code => NULL);
537 END ; -- anonymous block end
538 END LOOP;
539
540 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_person_profiles-',
541 p_prefix=>l_debug_prefix,
542 p_msg_level=>fnd_log.level_procedure);
543 EXCEPTION
544 WHEN OTHERS THEN
545 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
546 p_prefix=>l_debug_prefix,
547 p_msg_level=>fnd_log.level_procedure);
548 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
549 END pop_hz_person_profiles;
550
551 -----------------------------------------------------------------
552 -- Procedure name: pop_hz_person_language()
553 -- Purpose: populates BOT for HZ_PERSON_LANGUAGE create or update
554 -- Scope: internal
555 -- Called From: V2 API
556 -- Called By: HZ_PERSON_LANGUAGE create or update APIs
557 -- Input Parameters:
558 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
559 -- p_LANGUAGE_USE_REFERENCE_ID IN NUMBER
560 --
561 -- Note:
562 -- Following are the allowed PARENT and CHILD BO combinations for HZ_PERSON_LANGUAGE
563 -- This procedure must ensure that the combination is valid before populating BOT
564 --
565 -- PARENT BO: PERSON :: CHILD BO:
566 -----------------------------------------------------------------
567 PROCEDURE pop_hz_person_language(p_operation IN VARCHAR2, p_language_use_reference_id IN NUMBER) IS
568
569 -- cursor statement to select the info from child table
570 CURSOR c_child IS
571 SELECT pl.LAST_UPDATE_DATE lud, pl.PARTY_ID parent_id, pl.LANGUAGE_USE_REFERENCE_ID child_id
572 FROM HZ_PERSON_LANGUAGE pl, HZ_PARTIES p
573 WHERE pl.LANGUAGE_USE_REFERENCE_ID = P_LANGUAGE_USE_REFERENCE_ID
574 AND pl.party_id = p.party_id
575 AND p.party_type = 'PERSON';
576
577 -- local variables
578 l_debug_prefix VARCHAR2(40) := 'pop_hz_person_language';
579 l_parent_id NUMBER; -- used to store parent entity identifier
580 l_child_id NUMBER; -- used to store HZ_PERSON_LANGUAGE identifier
581 l_lud DATE; -- used to store the child last update date
582 l_child_rec_exists_no NUMBER;
583 l_cen VARCHAR2(30) := 'HZ_PERSON_LANGUAGE';
584 BEGIN
585 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_person_language+',
586 p_prefix=>l_debug_prefix,
587 p_msg_level=>fnd_log.level_procedure);
588
589 -- validate p_operation
590 IF p_operation IN ('I','U') THEN
591 NULL;
592 ELSE
593 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_person_language',
594 p_prefix=>l_debug_prefix,
595 p_msg_level=>fnd_log.level_procedure);
596 RAISE FND_API.G_EXC_ERROR;
597 END IF;
598
599 FOR c_child_rec IN c_child LOOP
600 -- collect the child info into variables
601 l_lud := c_child_rec.lud;
602 l_parent_id := c_child_rec.parent_id;
603 l_child_id := c_child_rec.child_id;
604
605 -- if record not existing, insert into hz_bus_obj_tracking
606 BEGIN
607 l_child_rec_exists_no := 0;
608 SELECT child_id INTO l_child_rec_exists_no
609 FROM HZ_BUS_OBJ_TRACKING
610 WHERE event_id IS NULL
611 AND CHILD_ENTITY_NAME = l_cen
612 AND CHILD_ID = l_child_id
613 AND rownum = 1;
614
615 IF l_child_rec_exists_no <> 0 THEN
616 -- data already exists, no need to write
617 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
618 p_prefix=>l_debug_prefix,
619 p_msg_level=>fnd_log.level_procedure);
620 END IF;
621 EXCEPTION
622 WHEN NO_DATA_FOUND THEN
623 INSERT INTO HZ_BUS_OBJ_TRACKING
624 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
625 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
626 ) VALUES (
627 'N', p_operation, l_child_id, 'HZ_PERSON_LANGUAGE', NULL,
628 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, 'PERSON');
629
630 -- populate party record
631 pop_parent_record(p_child_id => l_parent_id,
632 p_lud => l_lud,
633 p_centity_name => 'HZ_PARTIES',
634 p_cbo_code => 'PERSON',
635 p_parent_id => NULL,
636 p_pentity_name => NULL,
637 p_pbo_code => NULL);
638 END ; -- anonymous block end
639 END LOOP;
640
641 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_person_language-',
642 p_prefix=>l_debug_prefix,
643 p_msg_level=>fnd_log.level_procedure);
644 EXCEPTION
645 WHEN OTHERS THEN
646 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
647 p_prefix=>l_debug_prefix,
648 p_msg_level=>fnd_log.level_procedure);
652 -----------------------------------------------------------------
649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650 END pop_hz_person_language;
651
653 -- Procedure name: pop_hz_person_interest()
654 -- Purpose: populates BOT for HZ_PERSON_INTEREST create or update
655 -- Scope: internal
656 -- Called From: V2 API
657 -- Called By: HZ_PERSON_INTEREST create or update APIs
658 -- Input Parameters:
659 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
660 -- p_person_interest_id IN NUMBER
661 --
662 -- Note:
663 -- Following are the allowed PARENT and CHILD BO combinations for HZ_PERSON_INTEREST
664 -- This procedure must ensure that the combination is valid before populating BOT
665 --
666 -- PARENT BO: PERSON :: CHILD BO:
667 -----------------------------------------------------------------
668 PROCEDURE pop_hz_person_interest(p_operation IN VARCHAR2, p_person_interest_id IN NUMBER) IS
669
670 -- cursor statement to select the info from child table
671 CURSOR C_child IS
672 SELECT pi.LAST_UPDATE_DATE lud, pi.PARTY_ID parent_id, pi.PERSON_INTEREST_ID child_id
673 FROM HZ_PERSON_INTEREST pi, HZ_PARTIES p
674 WHERE pi.PERSON_INTEREST_ID = P_PERSON_INTEREST_ID
675 AND pi.party_id = p.party_id
676 AND p.party_type = 'PERSON';
677
678 -- local variables
679 l_debug_prefix VARCHAR2(40) := 'pop_hz_person_interest';
680 l_parent_id NUMBER; -- used to store parent entity identifier
681 l_child_id NUMBER; -- used to store HZ_PERSON_INTEREST identifier
682 l_lud DATE; -- used to store the child last update date
683 l_child_rec_exists_no NUMBER;
684 l_cen VARCHAR2(30) := 'HZ_PERSON_INTEREST';
685 BEGIN
686 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_person_interest+',
687 p_prefix=>l_debug_prefix,
688 p_msg_level=>fnd_log.level_procedure);
689
690 -- validate p_operation
691 IF p_operation IN ('I','U') THEN
692 NULL;
693 ELSE
694 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_person_interest',
695 p_prefix=>l_debug_prefix,
696 p_msg_level=>fnd_log.level_procedure);
697 RAISE FND_API.G_EXC_ERROR;
698 END IF;
699
700 FOR c_child_rec IN c_child LOOP
701 -- collect the child info into variables
702 l_lud := c_child_rec.lud;
703 l_parent_id := c_child_rec.parent_id;
704 l_child_id := c_child_rec.child_id;
705
706 -- if record not existing, insert into hz_bus_obj_tracking
707 BEGIN
708 l_child_rec_exists_no := 0;
709 SELECT child_id INTO l_child_rec_exists_no
710 FROM HZ_BUS_OBJ_TRACKING
711 WHERE event_id IS NULL
712 AND CHILD_ENTITY_NAME = l_cen
713 AND CHILD_ID = l_child_id
714 AND rownum = 1;
715
716 IF l_child_rec_exists_no <> 0 THEN
717 -- data already exists, no need to write
718 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
719 p_prefix=>l_debug_prefix,
720 p_msg_level=>fnd_log.level_procedure);
721 END IF;
722 EXCEPTION
723 WHEN NO_DATA_FOUND THEN
724 INSERT INTO HZ_BUS_OBJ_TRACKING
725 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
726 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
727 ) VALUES (
728 'N', p_operation, l_child_id, 'HZ_PERSON_INTEREST', NULL,
729 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, 'PERSON');
730
731 -- populate party record
732 pop_parent_record(p_child_id => l_parent_id,
733 p_lud => l_lud,
734 p_centity_name => 'HZ_PARTIES',
735 p_cbo_code => 'PERSON',
736 p_parent_id => NULL,
737 p_pentity_name => NULL,
738 p_pbo_code => NULL);
739 END ; -- anonymous block end
740 END LOOP;
741
742 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_person_interest-',
743 p_prefix=>l_debug_prefix,
744 p_msg_level=>fnd_log.level_procedure);
745 EXCEPTION
746 WHEN OTHERS THEN
747 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
748 p_prefix=>l_debug_prefix,
749 p_msg_level=>fnd_log.level_procedure);
750 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
751 END pop_hz_person_interest;
752
753 -----------------------------------------------------------------
754 -- Procedure name: pop_hz_party_site_uses()
755 -- Purpose: populates BOT for HZ_PARTY_SITE_USES create or update
756 -- Scope: internal
757 -- Called From: V2 API
758 -- Called By: HZ_PARTY_SITE_USES create or update APIs
759 -- Input Parameters:
760 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
761 -- p_party_site_use_id IN NUMBER
762 --
763 -- Note:
764 -- Following are the allowed PARENT and CHILD BO combinations for HZ_PARTY_SITE_USES
765 -- This procedure must ensure that the combination is valid before populating BOT
766 --
767 -- PARENT BO: PARTY_SITE :: CHILD BO:
768 -----------------------------------------------------------------
769 PROCEDURE pop_hz_party_site_uses(p_operation IN VARCHAR2, p_party_site_use_id IN NUMBER) IS
770
771 -- cursor statement to select the info from child table
772 CURSOR C_child IS
776 WHERE psu.PARTY_SITE_USE_ID = P_PARTY_SITE_USE_ID
773 SELECT psu.LAST_UPDATE_DATE lud, psu.PARTY_SITE_ID parent_id, psu.PARTY_SITE_USE_ID child_id,
774 p.party_type, p.party_id
775 FROM HZ_PARTY_SITE_USES psu, HZ_PARTY_SITES ps, HZ_PARTIES p
777 AND psu.party_site_id = ps.party_site_id
778 AND ps.party_id = p.party_id
779 AND p.party_type in ('ORGANIZATION', 'PERSON', 'PARTY_RELATIONSHIP');
780
781 CURSOR get_org_contact(l_party_id NUMBER) IS
782 SELECT oc.org_contact_id
783 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS pr
784 WHERE oc.party_relationship_id = pr.relationship_id
785 AND pr.party_id = l_party_id
786 AND pr.subject_type = 'PERSON'
787 AND pr.object_type = 'ORGANIZATION'
788 AND rownum = 1;
789
790 -- local variables
791 l_debug_prefix VARCHAR2(40) := 'pop_hz_party_site_uses';
792 l_parent_id NUMBER; -- used to store parent entity identifier
793 l_child_id NUMBER; -- used to store HZ_PARTY_SITE_USES identifier
794 l_lud DATE; -- used to store the child last update date
795 l_child_rec_exists_no NUMBER;
796 l_ptype VARCHAR2(30);
797 l_pid NUMBER;
798 l_dummy_id NUMBER;
799 l_cen VARCHAR2(30) := 'HZ_PARTY_SITE_USES';
800 BEGIN
801 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_party_site_uses+',
802 p_prefix=>l_debug_prefix,
803 p_msg_level=>fnd_log.level_procedure);
804
805 -- validate p_operation
806 IF p_operation IN ('I','U') THEN
807 NULL;
808 ELSE
809 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_party_site_uses',
810 p_prefix=>l_debug_prefix,
811 p_msg_level=>fnd_log.level_procedure);
812 RAISE FND_API.G_EXC_ERROR;
813 END IF;
814
815 FOR c_child_rec IN c_child LOOP
816 -- collect the child info into variables
817 l_lud := c_child_rec.lud;
818 l_parent_id := c_child_rec.parent_id;
819 l_child_id := c_child_rec.child_id;
820 l_ptype := c_child_rec.party_type;
821 l_pid := c_child_rec.party_id;
822
823 IF(l_ptype = 'PARTY_RELATIONSHIP') THEN
824 -- get org_contact_id
825 OPEN get_org_contact(l_pid);
826 FETCH get_org_contact INTO l_dummy_id;
827 CLOSE get_org_contact;
828 IF(l_dummy_id IS NULL) THEN
829 RETURN;
830 END IF;
831 END IF;
832
833 -- if record not existing, insert into hz_bus_obj_tracking
834 BEGIN
835 l_child_rec_exists_no := 0;
836 SELECT child_id INTO l_child_rec_exists_no
837 FROM HZ_BUS_OBJ_TRACKING
838 WHERE event_id IS NULL
839 AND CHILD_ENTITY_NAME = l_cen
840 AND CHILD_ID = l_child_id
841 AND rownum = 1;
842
843 IF l_child_rec_exists_no <> 0 THEN
844 -- data already exists, no need to write
845 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
846 p_prefix=>l_debug_prefix,
847 p_msg_level=>fnd_log.level_procedure);
848 END IF;
849 EXCEPTION
850 WHEN NO_DATA_FOUND THEN
851 INSERT INTO HZ_BUS_OBJ_TRACKING
852 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
853 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
854 ) VALUES (
855 'N', p_operation, l_child_id, 'HZ_PARTY_SITE_USES', NULL,
856 l_lud, l_lud, 'HZ_PARTY_SITES', l_parent_id, 'PARTY_SITE');
857 END ; -- anonymous block end
858 END LOOP;
859
860 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_party_site_uses-',
861 p_prefix=>l_debug_prefix,
862 p_msg_level=>fnd_log.level_procedure);
863 EXCEPTION
864 WHEN OTHERS THEN
865 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
866 p_prefix=>l_debug_prefix,
867 p_msg_level=>fnd_log.level_procedure);
868 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
869 END pop_hz_party_site_uses;
870
871 -----------------------------------------------------------------
872 -- Procedure name: pop_hz_party_sites()
873 -- Purpose: populates BOT for HZ_PARTY_SITES create or update
874 -- Scope: internal
875 -- Called From: V2 API
876 -- Called By: HZ_PARTY_SITES create or update APIs
877 -- Input Parameters:
878 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
879 -- p_party_site_id IN NUMBER
880 --
881 -- Note:
882 -- Following are the allowed PARENT and CHILD BO combinations for HZ_PARTY_SITES
883 -- This procedure must ensure that the combination is valid before populating BOT
884 --
885 -- PARENT BO: ORG :: CHILD BO: PARTY_SITE
886 -- PARENT BO: PERSON :: CHILD BO: PARTY_SITE
887 -- PARENT BO: ORG_CONTACT :: CHILD BO: PARTY_SITE
888 -----------------------------------------------------------------
889 PROCEDURE pop_hz_party_sites(p_operation IN VARCHAR2, p_party_site_id IN NUMBER) IS
890
891 -- cursor statement to select the info from child table
892 CURSOR c_child IS
893 SELECT ps.LAST_UPDATE_DATE lud, ps.PARTY_ID parent_id,
894 decode(p.party_type, 'PARTY_RELATIONSHIP', 'HZ_ORG_CONTACTS', 'HZ_PARTIES') parent_tbl_name,
895 ps.PARTY_SITE_ID child_id,
896 decode(p.party_type, 'ORGANIZATION', 'ORG', 'PERSON', 'PERSON', 'PARTY_RELATIONSHIP', 'ORG_CONTACT', null) bo_code,
897 ps.location_id location_id
898 FROM HZ_PARTY_SITES ps, HZ_PARTIES p
902
899 WHERE ps.PARTY_SITE_ID = P_PARTY_SITE_ID
900 AND ps.party_id = p.party_id
901 AND p.party_type in ('ORGANIZATION','PERSON','PARTY_RELATIONSHIP');
903 CURSOR get_org_contact(l_party_id NUMBER) IS
904 SELECT oc.org_contact_id
905 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS pr
906 WHERE oc.party_relationship_id = pr.relationship_id
907 AND pr.party_id = l_party_id
908 AND pr.subject_type = 'PERSON'
909 AND pr.object_type = 'ORGANIZATION'
910 AND rownum = 1;
911
912 -- local variables
913 l_debug_prefix VARCHAR2(40) := 'pop_hz_party_sites';
914 l_parent_id NUMBER; -- used to store parent entity identifier
915 l_parent_tbl_name VARCHAR2(30); -- used to store parent entity name
916 l_bo_code VARCHAR2(30); -- used to store BO Code
917 l_child_bo_code VARCHAR2(30); -- used to store Child BO Code (if child entity is a root node)
918 l_child_id NUMBER; -- used to store HZ_PARTY_SITES identifier
919 l_lud DATE; -- used to store the child last update date
920 l_child_rec_exists_no NUMBER;
921 l_location_id NUMBER;
922 l_org_contact_id NUMBER;
923 l_org_id NUMBER; -- party_id of organization for org contact relationship
924 l_cen VARCHAR2(30) := 'HZ_PARTY_SITES';
925 BEGIN
926 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_party_sites+',
927 p_prefix=>l_debug_prefix,
928 p_msg_level=>fnd_log.level_procedure);
929
930 -- validate p_operation
931 IF p_operation IN ('I','U') THEN
932 NULL;
933 ELSE
934 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_party_sites',
935 p_prefix=>l_debug_prefix,
936 p_msg_level=>fnd_log.level_procedure);
937 RAISE FND_API.G_EXC_ERROR;
938 END IF;
939
940 FOR c_child_rec IN c_child LOOP
941 -- collect the child info into variables
942 l_lud := c_child_rec.lud;
943 l_bo_code := c_child_rec.bo_code;
944 l_child_id := c_child_rec.child_id;
945 l_location_id := c_child_rec.location_id;
946 l_parent_id := c_child_rec.parent_id;
947 l_parent_tbl_name := c_child_rec.parent_tbl_name;
948
949 IF(l_bo_code = 'ORG_CONTACT') THEN
950 -- get org_contact_id
951 OPEN get_org_contact(l_parent_id);
952 FETCH get_org_contact INTO l_parent_id;
953 CLOSE get_org_contact;
954 IF(l_parent_id IS NULL) THEN
955 RETURN;
956 END IF;
957 END IF;
958
959 -- if record not existing, insert into hz_bus_obj_tracking
960 BEGIN
961 l_child_rec_exists_no := 0;
962 SELECT child_id INTO l_child_rec_exists_no
963 FROM HZ_BUS_OBJ_TRACKING
964 WHERE event_id IS NULL
965 AND CHILD_ENTITY_NAME = l_cen
966 AND CHILD_ID = l_child_id
967 AND rownum = 1;
968
969 IF l_child_rec_exists_no <> 0 THEN
970 -- data already exists, no need to write
971 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
972 p_prefix=>l_debug_prefix,
973 p_msg_level=>fnd_log.level_procedure);
974 END IF;
975 EXCEPTION
976 WHEN NO_DATA_FOUND THEN
977 -- populate the child bo code also
978 INSERT INTO HZ_BUS_OBJ_TRACKING
979 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
980 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
981 ) VALUES (
982 'N', p_operation, l_child_id, 'HZ_PARTY_SITES', 'PARTY_SITE',
983 l_lud, l_lud, l_parent_tbl_name, l_parent_id, l_bo_code);
984 END ; -- anonymous block end
985 END LOOP;
986
987 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_party_sites-',
988 p_prefix=>l_debug_prefix,
989 p_msg_level=>fnd_log.level_procedure);
990 EXCEPTION
991 WHEN OTHERS THEN
992 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
993 p_prefix=>l_debug_prefix,
994 p_msg_level=>fnd_log.level_procedure);
995 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
996 END pop_hz_party_sites;
997
998 -----------------------------------------------------------------
999 -- Procedure name: pop_hz_party_preferences()
1000 -- Purpose: populates BOT for HZ_PARTY_PREFERENCES create or update
1001 -- Scope: internal
1002 -- Called From: V2 API
1003 -- Called By: HZ_PARTY_PREFERENCES create or update APIs
1004 -- Input Parameters:
1005 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
1006 -- p_party_preference_id IN NUMBER
1007 --
1008 -- Note:
1009 -- Following are the allowed PARENT and CHILD BO combinations for HZ_PARTY_PREFERENCES
1010 -- This procedure must ensure that the combination is valid before populating BOT
1011 --
1012 -- PARENT BO: ORG :: CHILD BO:
1013 -- PARENT BO: PERSON :: CHILD BO:
1014 -----------------------------------------------------------------
1015 PROCEDURE pop_hz_party_preferences(p_operation IN VARCHAR2, p_party_preference_id IN NUMBER) IS
1016
1017 CURSOR C_child IS
1018 SELECT pp.LAST_UPDATE_DATE lud, pp.PARTY_ID parent_id,
1019 'HZ_PARTIES' parent_tbl_name, pp.PARTY_PREFERENCE_ID child_id,
1020 decode(p.party_type, 'ORGANIZATION', 'ORG', 'PERSON', 'PERSON', NULL) bo_code
1021 FROM HZ_PARTY_PREFERENCES pp, HZ_PARTIES p
1022 WHERE pp.PARTY_PREFERENCE_ID = p_party_preference_id
1023 AND pp.party_id = p.party_id
1027 l_debug_prefix VARCHAR2(40) := 'pop_hz_party_preferences';
1024 AND p.party_type in ('ORGANIZATION', 'PERSON');
1025
1026 -- local variables
1028 l_parent_id NUMBER; -- used to store parent entity identifier
1029 l_parent_tbl_name VARCHAR2(30); -- used to store parent entity name
1030 l_bo_code VARCHAR2(30); -- used to store BO Code
1031 l_child_bo_code VARCHAR2(30); -- used to store Child BO Code (if child entity is a root node)
1032 l_child_id NUMBER; -- used to store HZ_PARTY_PREFERENCES identifier
1033 l_lud DATE; -- used to store the child last update date
1034 l_child_rec_exists_no NUMBER;
1035 l_cen VARCHAR2(30) := 'HZ_PARTY_PREFERENCES';
1036 BEGIN
1037 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_party_preferences+',
1038 p_prefix=>l_debug_prefix,
1039 p_msg_level=>fnd_log.level_procedure);
1040
1041 -- validate p_operation
1042 IF p_operation IN ('I','U') THEN
1043 NULL;
1044 ELSE
1045 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_party_preferences',
1046 p_prefix=>l_debug_prefix,
1047 p_msg_level=>fnd_log.level_procedure);
1048 RAISE FND_API.G_EXC_ERROR;
1049 END IF;
1050
1051 FOR c_child_rec IN c_child LOOP
1052 -- collect the child info into variables
1053 l_lud := c_child_rec.lud;
1054 l_parent_id := c_child_rec.parent_id;
1055 l_parent_tbl_name := c_child_rec.parent_tbl_name;
1056 l_bo_code := c_child_rec.bo_code;
1057 l_child_id := c_child_rec.child_id;
1058
1059 -- if record not existing, insert into hz_bus_obj_tracking
1060 BEGIN
1061 l_child_rec_exists_no := 0;
1062 SELECT child_id INTO l_child_rec_exists_no
1063 FROM HZ_BUS_OBJ_TRACKING
1064 WHERE event_id IS NULL
1065 AND CHILD_ENTITY_NAME = l_cen
1066 AND CHILD_ID = l_child_id
1067 AND rownum = 1;
1068
1069 IF l_child_rec_exists_no <> 0 THEN
1070 -- data already exists, no need to write
1071 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
1072 p_prefix=>l_debug_prefix,
1073 p_msg_level=>fnd_log.level_procedure);
1074 END IF;
1075 EXCEPTION
1076 WHEN NO_DATA_FOUND THEN
1077 INSERT INTO HZ_BUS_OBJ_TRACKING
1078 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
1079 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
1080 ) VALUES (
1081 'N', p_operation, l_child_id, 'HZ_PARTY_PREFERENCES', NULL,
1082 l_lud, l_lud, l_parent_tbl_name, l_parent_id, l_bo_code);
1083
1084 -- populate party record
1085 pop_parent_record(p_child_id => l_parent_id,
1086 p_lud => l_lud,
1087 p_centity_name => 'HZ_PARTIES',
1088 p_cbo_code => l_bo_code,
1089 p_parent_id => NULL,
1090 p_pentity_name => NULL,
1091 p_pbo_code => NULL);
1092 END ; -- anonymous block end
1093 END LOOP;
1094
1095 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_party_preferences-',
1096 p_prefix=>l_debug_prefix,
1097 p_msg_level=>fnd_log.level_procedure);
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
1101 p_prefix=>l_debug_prefix,
1102 p_msg_level=>fnd_log.level_procedure);
1103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1104 END pop_hz_party_preferences;
1105
1106 -----------------------------------------------------------------
1107 -- Procedure name: pop_hz_org_contact_roles()
1108 -- Purpose: populates BOT for HZ_ORG_CONTACT_ROLES create or update
1109 -- Scope: internal
1110 -- Called From: V2 API
1111 -- Called By: HZ_ORG_CONTACT_ROLES create or update APIs
1112 -- Input Parameters:
1113 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
1114 -- p_org_contact_role_id IN NUMBER
1115 --
1116 -- Note:
1117 -- Following are the allowed PARENT and CHILD BO combinations for HZ_ORG_CONTACT_ROLES
1118 -- This procedure must ensure that the combination is valid before populating BOT
1119 --
1120 -- PARENT BO: ORG_CONTACT :: CHILD BO:
1121 -----------------------------------------------------------------
1122 PROCEDURE pop_hz_org_contact_roles(p_operation IN VARCHAR2, p_org_contact_role_id IN NUMBER) IS
1123
1124 -- cursor statement to select the info from child table
1125 CURSOR C_child IS
1126 SELECT ocr.LAST_UPDATE_DATE lud, ocr.ORG_CONTACT_ID parent_id,
1127 ocr.ORG_CONTACT_ROLE_ID child_id, pr.object_id object_id
1128 FROM HZ_ORG_CONTACT_ROLES ocr, HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS pr
1129 WHERE ORG_CONTACT_ROLE_ID = P_ORG_CONTACT_ROLE_ID
1130 AND ocr.org_contact_id = oc.org_contact_id
1131 AND oc.party_relationship_id = pr.relationship_id
1132 AND pr.object_type = 'ORGANIZATION'
1133 AND pr.subject_type = 'PERSON'
1134 AND rownum = 1;
1135
1136 -- local variables
1137 l_debug_prefix VARCHAR2(40) := 'pop_hz_org_contact_roles';
1138 l_parent_id NUMBER; -- used to store parent entity identifier
1139 l_child_id NUMBER; -- used to store HZ_ORG_CONTACT_ROLES identifier
1140 l_lud DATE; -- used to store the child last update date
1141 l_child_rec_exists_no NUMBER;
1142 l_org_id NUMBER;
1146 p_prefix=>l_debug_prefix,
1143 l_cen VARCHAR2(30) := 'HZ_ORG_CONTACT_ROLES';
1144 BEGIN
1145 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_org_contact_roles+',
1147 p_msg_level=>fnd_log.level_procedure);
1148
1149 -- validate p_operation
1150 IF p_operation IN ('I','U') THEN
1151 NULL;
1152 ELSE
1153 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_org_contact_roles',
1154 p_prefix=>l_debug_prefix,
1155 p_msg_level=>fnd_log.level_procedure);
1156 RAISE FND_API.G_EXC_ERROR;
1157 END IF;
1158
1159 FOR c_child_rec IN c_child LOOP
1160 -- collect the child info into variables
1161 l_lud := c_child_rec.lud;
1162 l_parent_id := c_child_rec.parent_id;
1163 l_child_id := c_child_rec.child_id;
1164 l_org_id := c_child_rec.object_id;
1165
1166 -- if record not existing, insert into hz_bus_obj_tracking
1167 BEGIN
1168 l_child_rec_exists_no := 0;
1169 SELECT child_id INTO l_child_rec_exists_no
1170 FROM HZ_BUS_OBJ_TRACKING
1171 WHERE event_id IS NULL
1172 AND CHILD_ENTITY_NAME = l_cen
1173 AND CHILD_ID = l_child_id
1174 AND rownum = 1;
1175
1176 IF l_child_rec_exists_no <> 0 THEN
1177 -- data already exists, no need to write
1178 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
1179 p_prefix=>l_debug_prefix,
1180 p_msg_level=>fnd_log.level_procedure);
1181 END IF;
1182 EXCEPTION
1183 WHEN NO_DATA_FOUND THEN
1184 INSERT INTO HZ_BUS_OBJ_TRACKING
1185 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
1186 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
1187 ) VALUES (
1188 'N', p_operation, l_child_id, 'HZ_ORG_CONTACT_ROLES', NULL,
1189 l_lud, l_lud, 'HZ_ORG_CONTACTS', l_parent_id, 'ORG_CONTACT');
1190
1191 -- populate org party record
1192 pop_parent_record(p_child_id => l_parent_id,
1193 p_lud => l_lud,
1194 p_centity_name => 'HZ_ORG_CONTACTS',
1195 p_cbo_code => 'ORG_CONTACT',
1196 p_parent_id => l_org_id,
1197 p_pentity_name => 'HZ_PARTIES',
1198 p_pbo_code => 'ORG');
1199 END ; -- anonymous block end
1200 END LOOP;
1201
1202 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_org_contact_roles-',
1203 p_prefix=>l_debug_prefix,
1204 p_msg_level=>fnd_log.level_procedure);
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
1208 p_prefix=>l_debug_prefix,
1209 p_msg_level=>fnd_log.level_procedure);
1210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1211 END pop_hz_org_contact_roles;
1212
1213 -----------------------------------------------------------------
1214 -- Procedure name: pop_hz_org_contacts()
1215 -- Purpose: populates BOT for HZ_ORG_CONTACTS create or update
1216 -- Scope: internal
1217 -- Called From: V2 API
1218 -- Called By: HZ_ORG_CONTACTS create or update APIs
1219 -- Input Parameters:
1220 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
1221 -- p_org_contact_id IN NUMBER
1222 --
1223 -- Note:
1224 -- Following are the allowed PARENT and CHILD BO combinations for HZ_ORG_CONTACTS
1225 -- This procedure must ensure that the combination is valid before populating BOT
1226 --
1227 -- PARENT BO: ORG :: CHILD BO: ORG_CONTACT
1228 -----------------------------------------------------------------
1229 PROCEDURE pop_hz_org_contacts(p_operation IN VARCHAR2, p_org_contact_id IN NUMBER) IS
1230
1231 -- cursor statement to select the info from child table
1232 CURSOR C_child IS
1233 SELECT oc.LAST_UPDATE_DATE lud, oc.ORG_CONTACT_ID child_id,
1234 pr.object_id parent_id, pr.subject_id person_id, pr.relationship_id rel_id
1235 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS pr
1236 WHERE oc.ORG_CONTACT_ID = P_ORG_CONTACT_ID
1237 AND oc.party_relationship_id = pr.relationship_id
1238 AND pr.object_type = 'ORGANIZATION'
1239 AND pr.subject_type = 'PERSON'
1240 AND rownum = 1;
1241
1242 -- local variables
1243 l_debug_prefix VARCHAR2(40) := 'pop_hz_org_contacts';
1244 l_parent_id NUMBER; -- used to store parent entity identifier
1245 l_child_id NUMBER; -- used to store HZ_ORG_CONTACTS identifier
1246 l_lud DATE; -- used to store the child last update date
1247 l_child_rec_exists_no NUMBER;
1248 l_rel_id NUMBER;
1249 l_person_id NUMBER;
1250 l_pop_flag VARCHAR2(1);
1251 l_cen VARCHAR2(30) := 'HZ_ORG_CONTACTS';
1252 BEGIN
1253 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_org_contacts+',
1254 p_prefix=>l_debug_prefix,
1255 p_msg_level=>fnd_log.level_procedure);
1256
1257 -- validate p_operation
1258 IF p_operation IN ('I','U') THEN
1259 NULL;
1260 ELSE
1261 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_org_contacts',
1262 p_prefix=>l_debug_prefix,
1263 p_msg_level=>fnd_log.level_procedure);
1264 RAISE FND_API.G_EXC_ERROR;
1265 END IF;
1266
1267 FOR c_child_rec IN c_child LOOP
1268 -- collect the child info into variables
1272 l_person_id := c_child_rec.person_id;
1269 l_lud := c_child_rec.lud;
1270 l_parent_id := c_child_rec.parent_id;
1271 l_child_id := c_child_rec.child_id;
1273 l_rel_id := c_child_rec.rel_id;
1274
1275 -- if record not existing, insert into hz_bus_obj_tracking
1276 BEGIN
1277 l_child_rec_exists_no := 0;
1278 SELECT child_id, populated_flag INTO l_child_rec_exists_no, l_pop_flag
1279 FROM HZ_BUS_OBJ_TRACKING
1280 WHERE event_id IS NULL
1281 AND CHILD_ENTITY_NAME = l_cen
1282 AND CHILD_ID = l_child_id
1283 AND rownum = 1;
1284
1285 IF l_child_rec_exists_no <> 0 THEN
1286 IF(l_pop_flag = 'Y') THEN
1287 UPDATE HZ_BUS_OBJ_TRACKING
1288 SET populated_flag = 'N'
1289 WHERE event_id IS NULL
1290 AND CHILD_ENTITY_NAME = l_cen
1291 AND CHILD_ID = l_child_id;
1292 END IF;
1293 -- data already exists, no need to write
1294 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
1295 p_prefix=>l_debug_prefix,
1296 p_msg_level=>fnd_log.level_procedure);
1297 END IF;
1298 EXCEPTION
1299 WHEN NO_DATA_FOUND THEN
1300 -- populate the child bo code also
1301 INSERT INTO HZ_BUS_OBJ_TRACKING
1302 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
1303 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
1304 ) VALUES (
1305 'N', p_operation, l_child_id, 'HZ_ORG_CONTACTS', 'ORG_CONTACT',
1306 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, 'ORG');
1307
1308 -- populate subject person as child
1309 pop_parent_record(p_child_id => l_person_id,
1310 p_lud => l_lud,
1311 p_centity_name => 'HZ_PARTIES',
1312 p_cbo_code => 'PERSON_CONTACT',
1313 p_parent_id => l_child_id,
1314 p_pentity_name => 'HZ_ORG_CONTACTS',
1315 p_pbo_code => 'ORG_CONTACT');
1316
1317 -- populate relationship as child
1318 pop_parent_record(p_child_id => l_rel_id,
1319 p_lud => l_lud,
1320 p_centity_name => 'HZ_RELATIONSHIPS',
1321 p_cbo_code => NULL,
1322 p_parent_id => l_child_id,
1323 p_pentity_name => 'HZ_ORG_CONTACTS',
1324 p_pbo_code => 'ORG_CONTACT');
1325 END ; -- anonymous block end
1326 END LOOP;
1327
1328 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_org_contacts-',
1329 p_prefix=>l_debug_prefix,
1330 p_msg_level=>fnd_log.level_procedure);
1331 EXCEPTION
1332 WHEN OTHERS THEN
1333 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
1334 p_prefix=>l_debug_prefix,
1335 p_msg_level=>fnd_log.level_procedure);
1336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1337 END pop_hz_org_contacts;
1338
1339 -----------------------------------------------------------------
1340 -- Procedure name: pop_hz_organization_profiles()
1341 -- Purpose: populates BOT for HZ_ORGANIZATION_PROFILES create or update
1342 -- Scope: internal
1343 -- Called From: V2 API
1344 -- Called By: HZ_ORGANIZATION_PROFILES create or update APIs
1345 -- Input Parameters:
1346 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
1347 -- p_organization_profile_id IN NUMBER
1348 --
1349 -- Note:
1350 -- Following are the allowed PARENT and CHILD BO combinations for HZ_ORGANIZATION_PROFILES
1351 -- This procedure must ensure that the combination is valid before populating BOT
1352 --
1353 -- PARENT BO: ORG :: CHILD BO:
1354 -----------------------------------------------------------------
1355 PROCEDURE pop_hz_organization_profiles(p_operation IN VARCHAR2, p_organization_profile_id IN NUMBER) IS
1356
1357 -- cursor statement to select the info from child table
1358 CURSOR C_child IS
1359 SELECT LAST_UPDATE_DATE lud, PARTY_ID parent_id, ORGANIZATION_PROFILE_ID child_id
1360 FROM HZ_ORGANIZATION_PROFILES
1361 WHERE ORGANIZATION_PROFILE_ID = P_ORGANIZATION_PROFILE_ID;
1362
1363 -- local variables
1364 l_debug_prefix VARCHAR2(40) := 'pop_hz_organization_profiles';
1365 l_parent_id NUMBER; -- used to store parent entity identifier
1366 l_child_id NUMBER; -- used to store HZ_ORGANIZATION_PROFILES identifier
1367 l_lud DATE; -- used to store the child last update date
1368 l_child_rec_exists_no NUMBER;
1369 l_cen VARCHAR2(30) := 'HZ_ORGANIZATION_PROFILES';
1370 BEGIN
1371 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_organization_profiles+',
1372 p_prefix=>l_debug_prefix,
1373 p_msg_level=>fnd_log.level_procedure);
1374
1375 -- validate p_operation
1376 IF p_operation IN ('I','U') THEN
1377 NULL;
1378 ELSE
1379 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_organization_profiles',
1380 p_prefix=>l_debug_prefix,
1381 p_msg_level=>fnd_log.level_procedure);
1382 RAISE FND_API.G_EXC_ERROR;
1383 END IF;
1384
1385 FOR c_child_rec IN c_child LOOP
1386 -- collect the child info into variables
1387 l_lud := c_child_rec.lud;
1388 l_parent_id := c_child_rec.parent_id;
1389 l_child_id := c_child_rec.child_id;
1390
1391 -- if record not existing, insert into hz_bus_obj_tracking
1395 FROM HZ_BUS_OBJ_TRACKING
1392 BEGIN
1393 l_child_rec_exists_no := 0;
1394 SELECT child_id INTO l_child_rec_exists_no
1396 WHERE event_id IS NULL
1397 AND CHILD_ENTITY_NAME = l_cen
1398 AND CHILD_ID = l_child_id
1399 AND rownum = 1;
1400
1401 IF l_child_rec_exists_no <> 0 THEN
1402 -- data already exists, no need to write
1403 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
1404 p_prefix=>l_debug_prefix,
1405 p_msg_level=>fnd_log.level_procedure);
1406 END IF;
1407 EXCEPTION
1408 WHEN NO_DATA_FOUND THEN
1409 INSERT INTO HZ_BUS_OBJ_TRACKING
1410 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
1411 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
1412 ) VALUES (
1413 'N', p_operation, l_child_id, 'HZ_ORGANIZATION_PROFILES', NULL,
1414 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, 'ORG');
1415
1416 -- populate org party record
1417 pop_parent_record(p_child_id => l_parent_id,
1418 p_lud => l_lud,
1419 p_centity_name => 'HZ_PARTIES',
1420 p_cbo_code => 'ORG',
1421 p_parent_id => NULL,
1422 p_pentity_name => NULL,
1423 p_pbo_code => NULL);
1424 END ; -- anonymous block end
1425 END LOOP;
1426
1427 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_organization_profiles-',
1428 p_prefix=>l_debug_prefix,
1429 p_msg_level=>fnd_log.level_procedure);
1430 EXCEPTION
1431 WHEN OTHERS THEN
1432 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
1433 p_prefix=>l_debug_prefix,
1434 p_msg_level=>fnd_log.level_procedure);
1435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1436 END pop_hz_organization_profiles;
1437
1438 -----------------------------------------------------------------
1439 -- Procedure name: pop_hz_locations()
1440 -- Purpose: populates BOT for HZ_LOCATIONS create or update
1441 -- Scope: internal
1442 -- Called From: V2 API
1443 -- Called By: HZ_LOCATIONS create or update APIs
1444 -- Input Parameters:
1445 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
1446 -- p_location_id IN NUMBER
1447 --
1448 -- Note:
1449 -- Following are the allowed PARENT and CHILD BO combinations for HZ_LOCATIONS
1450 -- This procedure must ensure that the combination is valid before populating BOT
1451 --
1452 -- PARENT BO: PARTY_SITE :: CHILD BO: LOCATION
1453 -----------------------------------------------------------------
1454 PROCEDURE pop_hz_locations(p_operation IN VARCHAR2, p_location_id IN NUMBER) IS
1455
1456 CURSOR c_child IS -- this is incorrect - please change
1457 SELECT LAST_UPDATE_DATE lud, LOCATION_ID child_id
1458 FROM HZ_LOCATIONS
1459 WHERE location_id = p_location_id;
1460
1461 CURSOR c_ps(l_loc_id NUMBER) IS
1462 SELECT party_site_id
1463 FROM HZ_PARTY_SITES ps, HZ_PARTIES p
1464 WHERE ps.location_id = l_loc_id
1465 AND ps.party_id = p.party_id;
1466
1467 -- local variables
1468 l_debug_prefix VARCHAR2(40) := 'pop_hz_locations';
1469 l_parent_id NUMBER; -- used to store parent entity identifier
1470 l_parent_tbl_name VARCHAR2(30); -- used to store parent entity name
1471 l_bo_code VARCHAR2(30); -- used to store BO Code
1472 l_child_bo_code VARCHAR2(30); -- used to store Child BO Code (if child entity is a root node)
1473 l_child_id NUMBER; -- used to store HZ_LOCATIONS identifier
1474 l_lud DATE; -- used to store the child last update date
1475 l_child_rec_exists_no NUMBER;
1476 l_cen VARCHAR2(30) := 'HZ_LOCATIONS';
1477 BEGIN
1478 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_locations+',
1479 p_prefix=>l_debug_prefix,
1480 p_msg_level=>fnd_log.level_procedure);
1481
1482 -- validate p_operation
1483 IF p_operation IN ('I','U') THEN
1484 NULL;
1485 ELSE
1486 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_locations',
1487 p_prefix=>l_debug_prefix,
1488 p_msg_level=>fnd_log.level_procedure);
1489 RAISE FND_API.G_EXC_ERROR;
1490 END IF;
1491
1492 FOR c_child_rec IN c_child LOOP
1493 l_lud := c_child_rec.lud;
1494 l_child_id := c_child_rec.child_id;
1495
1496 -- if record not existing, insert into hz_bus_obj_tracking
1497 BEGIN
1498 l_child_rec_exists_no := 0;
1499 SELECT child_id INTO l_child_rec_exists_no
1500 FROM HZ_BUS_OBJ_TRACKING
1501 WHERE event_id IS NULL
1502 AND CHILD_ENTITY_NAME = l_cen
1503 AND CHILD_ID = l_child_id
1504 AND rownum = 1;
1505
1506 IF l_child_rec_exists_no <> 0 THEN
1507 -- data already exists, no need to write
1508 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
1509 p_prefix=>l_debug_prefix,
1510 p_msg_level=>fnd_log.level_procedure);
1511 END IF;
1512 EXCEPTION
1513 WHEN NO_DATA_FOUND THEN
1514 OPEN c_ps(l_child_id);
1515 LOOP
1516 FETCH c_ps INTO l_parent_id;
1517 EXIT WHEN c_ps%NOTFOUND;
1518 -- populate the child bo code also
1519 INSERT INTO HZ_BUS_OBJ_TRACKING
1523 'N', p_operation, l_child_id, 'HZ_LOCATIONS', 'LOCATION',
1520 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
1521 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
1522 ) VALUES (
1524 l_lud, l_lud, 'HZ_PARTY_SITES', l_parent_id, 'PARTY_SITE');
1525 END LOOP;
1526 CLOSE c_ps;
1527 END ; -- anonymous block end
1528 END LOOP;
1529
1530 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_locations-',
1531 p_prefix=>l_debug_prefix,
1532 p_msg_level=>fnd_log.level_procedure);
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
1536 p_prefix=>l_debug_prefix,
1537 p_msg_level=>fnd_log.level_procedure);
1538 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1539 END pop_hz_locations;
1540
1541 -----------------------------------------------------------------
1542 -- Procedure name: pop_hz_financial_reports()
1543 -- Purpose: populates BOT for HZ_FINANCIAL_REPORTS create or update
1544 -- Scope: internal
1545 -- Called From: V2 API
1546 -- Called By: HZ_FINANCIAL_REPORTS create or update APIs
1547 -- Input Parameters:
1548 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
1549 -- p_financial_report_id IN NUMBER
1550 --
1551 -- Note:
1552 -- Following are the allowed PARENT and CHILD BO combinations for HZ_FINANCIAL_REPORTS
1553 -- This procedure must ensure that the combination is valid before populating BOT
1554 --
1555 -- PARENT BO: ORG :: CHILD BO: FIN_REPORT
1556 -----------------------------------------------------------------
1557 PROCEDURE pop_hz_financial_reports(p_operation IN VARCHAR2, p_financial_report_id IN NUMBER) IS
1558
1559 -- cursor statement to select the info from child table
1560 CURSOR C_child IS
1561 SELECT fr.LAST_UPDATE_DATE lud, fr.PARTY_ID parent_id, fr.FINANCIAL_REPORT_ID child_id,
1562 decode(p.party_type, 'ORGANIZATION', 'ORG', null) bo_code
1563 FROM HZ_FINANCIAL_REPORTS fr, HZ_PARTIES p
1564 WHERE fr.FINANCIAL_REPORT_ID = P_FINANCIAL_REPORT_ID
1565 AND fr.party_id = p.party_id
1566 AND p.party_type = 'ORGANIZATION';
1567
1568 -- local variables
1569 l_debug_prefix VARCHAR2(40) := 'pop_hz_financial_reports';
1570 l_parent_id NUMBER; -- used to store parent entity identifier
1571 l_bo_code VARCHAR2(30); -- used to store BO Code
1572 l_child_bo_code VARCHAR2(30); -- used to store Child BO Code (if child entity is a root node)
1573 l_child_id NUMBER; -- used to store HZ_FINANCIAL_REPORTS identifier
1574 l_lud DATE; -- used to store the child last update date
1575 l_child_rec_exists_no NUMBER;
1576 l_pop_flag VARCHAR2(1);
1577 l_cen VARCHAR2(30) := 'HZ_FINANCIAL_REPORTS';
1578 BEGIN
1579 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_financial_reports+',
1580 p_prefix=>l_debug_prefix,
1581 p_msg_level=>fnd_log.level_procedure);
1582
1583 -- validate p_operation
1584 IF p_operation IN ('I','U') THEN
1585 NULL;
1586 ELSE
1587 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_financial_reports',
1588 p_prefix=>l_debug_prefix,
1589 p_msg_level=>fnd_log.level_procedure);
1590 RAISE FND_API.G_EXC_ERROR;
1591 END IF;
1592
1593 FOR c_child_rec IN c_child LOOP
1594 -- collect the child info into variables
1595 l_lud := c_child_rec.lud;
1596 l_parent_id := c_child_rec.parent_id;
1597 l_bo_code := c_child_rec.bo_code;
1598 l_child_id := c_child_rec.child_id;
1599
1600 -- if record not existing, insert into hz_bus_obj_tracking
1601 BEGIN
1602 l_child_rec_exists_no := 0;
1603 SELECT child_id, populated_flag INTO l_child_rec_exists_no, l_pop_flag
1604 FROM HZ_BUS_OBJ_TRACKING
1605 WHERE event_id IS NULL
1606 AND CHILD_ENTITY_NAME = l_cen
1607 AND CHILD_ID = l_child_id
1608 AND rownum = 1;
1609
1610 IF l_child_rec_exists_no <> 0 THEN
1611 IF(l_pop_flag = 'Y') THEN
1612 UPDATE HZ_BUS_OBJ_TRACKING
1613 SET populated_flag = 'N'
1614 WHERE event_id IS NULL
1615 AND CHILD_ENTITY_NAME = l_cen
1616 AND CHILD_ID = l_child_id;
1617 END IF;
1618 -- data already exists, no need to write
1619 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
1620 p_prefix=>l_debug_prefix,
1621 p_msg_level=>fnd_log.level_procedure);
1622 END IF;
1623 EXCEPTION
1624 WHEN NO_DATA_FOUND THEN
1625 -- populate the child bo code also
1626 INSERT INTO HZ_BUS_OBJ_TRACKING
1627 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
1628 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
1629 ) VALUES (
1630 'N', p_operation, l_child_id, 'HZ_FINANCIAL_REPORTS', 'FIN_REPORT',
1631 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, l_bo_code);
1632
1633 -- populate org party record
1634 pop_parent_record(p_child_id => l_parent_id,
1635 p_lud => l_lud,
1636 p_centity_name => 'HZ_PARTIES',
1637 p_cbo_code => l_bo_code,
1638 p_parent_id => NULL,
1639 p_pentity_name => NULL,
1640 p_pbo_code => NULL);
1641 END ; -- anonymous block end
1645 p_prefix=>l_debug_prefix,
1642 END LOOP;
1643
1644 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_financial_reports-',
1646 p_msg_level=>fnd_log.level_procedure);
1647 EXCEPTION
1648 WHEN OTHERS THEN
1649 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
1650 p_prefix=>l_debug_prefix,
1651 p_msg_level=>fnd_log.level_procedure);
1652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1653 END pop_hz_financial_reports;
1654
1655 -----------------------------------------------------------------
1656 -- Procedure name: pop_hz_financial_profile()
1657 -- Purpose: populates BOT for HZ_FINANCIAL_PROFILE create or update
1658 -- Scope: internal
1659 -- Called From: V2 API
1660 -- Called By: HZ_FINANCIAL_PROFILE create or update APIs
1661 -- Input Parameters:
1662 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
1663 -- p_financial_profile_id IN NUMBER
1664 --
1665 -- Note:
1666 -- Following are the allowed PARENT and CHILD BO combinations for HZ_FINANCIAL_PROFILE
1667 -- This procedure must ensure that the combination is valid before populating BOT
1668 --
1669 -- PARENT BO: ORG :: CHILD BO:
1670 -- PARENT BO: PERSON :: CHILD BO:
1671 -----------------------------------------------------------------
1672 PROCEDURE pop_hz_financial_profile(p_operation IN VARCHAR2, p_financial_profile_id IN NUMBER) IS
1673
1674 -- cursor statement to select the info from child table
1675 CURSOR c_child IS
1676 SELECT fp.LAST_UPDATE_DATE lud, fp.PARTY_ID parent_id,
1677 fp.FINANCIAL_PROFILE_ID child_id,
1678 decode(p.party_type, 'ORGANIZATION', 'ORG', 'PERSON', 'PERSON', NULL) bo_code
1679 FROM HZ_FINANCIAL_PROFILE fp, HZ_PARTIES p
1680 WHERE fp.FINANCIAL_PROFILE_ID = P_FINANCIAL_PROFILE_ID
1681 AND fp.party_id = p.party_id
1682 AND p.party_type in ('ORGANIZATION', 'PERSON');
1683
1684 -- local variables
1685 l_debug_prefix VARCHAR2(40) := 'pop_hz_financial_profile';
1686 l_parent_id NUMBER; -- used to store parent entity identifier
1687 l_bo_code VARCHAR2(30); -- used to store BO Code
1688 l_child_id NUMBER; -- used to store HZ_FINANCIAL_PROFILE identifier
1689 l_lud DATE; -- used to store the child last update date
1690 l_child_rec_exists_no NUMBER;
1691 l_cen VARCHAR2(30) := 'HZ_FINANCIAL_PROFILE';
1692 BEGIN
1693 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_financial_profile+',
1694 p_prefix=>l_debug_prefix,
1695 p_msg_level=>fnd_log.level_procedure);
1696
1697 -- validate p_operation
1698 IF p_operation IN ('I','U') THEN
1699 NULL;
1700 ELSE
1701 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_financial_profile',
1702 p_prefix=>l_debug_prefix,
1703 p_msg_level=>fnd_log.level_procedure);
1704 RAISE FND_API.G_EXC_ERROR;
1705 END IF;
1706
1707 FOR c_child_rec IN c_child LOOP
1708 -- collect the child info into variables
1709 l_lud := c_child_rec.lud;
1710 l_parent_id := c_child_rec.parent_id;
1711 l_bo_code := c_child_rec.bo_code;
1712 l_child_id := c_child_rec.child_id;
1713
1714 -- if record not existing, insert into hz_bus_obj_tracking
1715 BEGIN
1716 l_child_rec_exists_no := 0;
1717 SELECT child_id INTO l_child_rec_exists_no
1718 FROM HZ_BUS_OBJ_TRACKING
1719 WHERE event_id IS NULL
1720 AND CHILD_ENTITY_NAME = l_cen
1721 AND CHILD_ID = l_child_id
1722 AND rownum = 1;
1723
1724 IF l_child_rec_exists_no <> 0 THEN
1725 -- data already exists, no need to write
1726 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
1727 p_prefix=>l_debug_prefix,
1728 p_msg_level=>fnd_log.level_procedure);
1729 END IF;
1730 EXCEPTION
1731 WHEN NO_DATA_FOUND THEN
1732 INSERT INTO HZ_BUS_OBJ_TRACKING
1733 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
1734 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
1735 ) VALUES (
1736 'N', p_operation, l_child_id, 'HZ_FINANCIAL_PROFILE', NULL,
1737 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, l_bo_code);
1738
1739 -- populate org party record
1740 pop_parent_record(p_child_id => l_parent_id,
1741 p_lud => l_lud,
1742 p_centity_name => 'HZ_PARTIES',
1743 p_cbo_code => l_bo_code,
1744 p_parent_id => NULL,
1745 p_pentity_name => NULL,
1746 p_pbo_code => NULL);
1747 END ; -- anonymous block end
1748 END LOOP;
1749
1750 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_financial_profile-',
1751 p_prefix=>l_debug_prefix,
1752 p_msg_level=>fnd_log.level_procedure);
1753 EXCEPTION
1754 WHEN OTHERS THEN
1755 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
1756 p_prefix=>l_debug_prefix,
1757 p_msg_level=>fnd_log.level_procedure);
1758 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1759 END pop_hz_financial_profile;
1760
1761 -----------------------------------------------------------------
1762 -- Procedure name: pop_hz_financial_numbers()
1763 -- Purpose: populates BOT for HZ_FINANCIAL_NUMBERS create or update
1764 -- Scope: internal
1765 -- Called From: V2 API
1766 -- Called By: HZ_FINANCIAL_NUMBERS create or update APIs
1770 --
1767 -- Input Parameters:
1768 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
1769 -- p_financial_number_id IN NUMBER
1771 -- Note:
1772 -- Following are the allowed PARENT and CHILD BO combinations for HZ_FINANCIAL_NUMBERS
1773 -- This procedure must ensure that the combination is valid before populating BOT
1774 --
1775 -- PARENT BO: FIN_REPORT :: CHILD BO:
1776 -----------------------------------------------------------------
1777 PROCEDURE pop_hz_financial_numbers(p_operation IN VARCHAR2, p_financial_number_id IN NUMBER) IS
1778
1779 -- cursor statement to select the info from child table
1780 CURSOR C_child IS
1781 SELECT fn.LAST_UPDATE_DATE lud, fn.FINANCIAL_REPORT_ID parent_id,
1782 fn.FINANCIAL_NUMBER_ID child_id, fr.party_id party_id
1783 FROM HZ_FINANCIAL_NUMBERS fn, HZ_FINANCIAL_REPORTS fr, HZ_PARTIES p
1784 WHERE fn.FINANCIAL_NUMBER_ID = P_FINANCIAL_NUMBER_ID
1785 AND fn.financial_report_id = fr.financial_report_id
1786 AND fr.party_id = p.party_id
1787 AND p.party_type = 'ORGANIZATION';
1788
1789 -- local variables
1790 l_debug_prefix VARCHAR2(40) := 'pop_hz_financial_numbers';
1791 l_parent_id NUMBER; -- used to store parent entity identifier
1792 l_child_id NUMBER; -- used to store HZ_FINANCIAL_NUMBERS identifier
1793 l_lud DATE; -- used to store the child last update date
1794 l_child_rec_exists_no NUMBER;
1795 l_party_id NUMBER;
1796 l_cen VARCHAR2(30) := 'HZ_FINANCIAL_NUMBERS';
1797 BEGIN
1798 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_financial_numbers+',
1799 p_prefix=>l_debug_prefix,
1800 p_msg_level=>fnd_log.level_procedure);
1801
1802 -- validate p_operation
1803 IF p_operation IN ('I','U') THEN
1804 NULL;
1805 ELSE
1806 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_financial_numbers',
1807 p_prefix=>l_debug_prefix,
1808 p_msg_level=>fnd_log.level_procedure);
1809 RAISE FND_API.G_EXC_ERROR;
1810 END IF;
1811
1812 FOR c_child_rec IN c_child LOOP
1813 -- collect the child info into variables
1814 l_lud := c_child_rec.lud;
1815 l_child_id := c_child_rec.child_id;
1816 l_parent_id := c_child_rec.parent_id;
1817 l_party_id := c_child_rec.party_id;
1818
1819 -- if record not existing, insert into hz_bus_obj_tracking
1820 BEGIN
1821 l_child_rec_exists_no := 0;
1822 SELECT child_id INTO l_child_rec_exists_no
1823 FROM HZ_BUS_OBJ_TRACKING
1824 WHERE event_id IS NULL
1825 AND CHILD_ENTITY_NAME = l_cen
1826 AND CHILD_ID = l_child_id
1827 AND rownum = 1;
1828
1829 IF l_child_rec_exists_no <> 0 THEN
1830 -- data already exists, no need to write
1831 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
1832 p_prefix=>l_debug_prefix,
1833 p_msg_level=>fnd_log.level_procedure);
1834 END IF;
1835 EXCEPTION
1836 WHEN NO_DATA_FOUND THEN
1837 INSERT INTO HZ_BUS_OBJ_TRACKING
1838 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
1839 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
1840 ) VALUES (
1841 'N', p_operation, l_child_id, 'HZ_FINANCIAL_NUMBERS', NULL,
1842 l_lud, l_lud, 'HZ_FINANCIAL_REPORTS', l_parent_id, 'FIN_REPORT');
1843
1844 -- populate org party record
1845 pop_parent_record(p_child_id => l_parent_id,
1846 p_lud => l_lud,
1847 p_centity_name => 'HZ_FINANCIAL_REPORTS',
1848 p_cbo_code => 'FIN_REPORT',
1849 p_parent_id => l_party_id,
1850 p_pentity_name => 'HZ_PARTIES',
1851 p_pbo_code => 'ORG');
1852
1853 -- populate org party record
1854 pop_parent_record(p_child_id => l_party_id,
1855 p_lud => l_lud,
1856 p_centity_name => 'HZ_PARTIES',
1857 p_cbo_code => 'ORG',
1858 p_parent_id => NULL,
1859 p_pentity_name => NULL,
1860 p_pbo_code => NULL);
1861 END ; -- anonymous block end
1862 END LOOP;
1863
1864 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_financial_numbers-',
1865 p_prefix=>l_debug_prefix,
1866 p_msg_level=>fnd_log.level_procedure);
1867 EXCEPTION
1868 WHEN OTHERS THEN
1869 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
1870 p_prefix=>l_debug_prefix,
1871 p_msg_level=>fnd_log.level_procedure);
1872 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1873 END pop_hz_financial_numbers;
1874
1875 -----------------------------------------------------------------
1876 -- Procedure name: pop_hz_employment_history()
1877 -- Purpose: populates BOT for HZ_EMPLOYMENT_HISTORY create or update
1878 -- Scope: internal
1879 -- Called From: V2 API
1880 -- Called By: HZ_EMPLOYMENT_HISTORY create or update APIs
1881 -- Input Parameters:
1882 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
1883 -- p_employment_history_id IN NUMBER
1884 --
1885 -- Note:
1886 -- Following are the allowed PARENT and CHILD BO combinations for HZ_EMPLOYMENT_HISTORY
1887 -- This procedure must ensure that the combination is valid before populating BOT
1888 --
1892
1889 -- PARENT BO: PERSON :: CHILD BO: EMP_HIST
1890 -----------------------------------------------------------------
1891 PROCEDURE pop_hz_employment_history(p_operation IN VARCHAR2, p_EMPLOYMENT_HISTORY_ID IN NUMBER) IS
1893 -- cursor statement to select the info from child table
1894 CURSOR C_child IS
1895 SELECT eh.LAST_UPDATE_DATE lud, eh.PARTY_ID parent_id,
1896 eh.EMPLOYMENT_HISTORY_ID child_id
1897 FROM HZ_EMPLOYMENT_HISTORY eh, HZ_PARTIES p
1898 WHERE eh.EMPLOYMENT_HISTORY_ID = P_EMPLOYMENT_HISTORY_ID
1899 AND eh.party_id = p.party_id
1900 AND p.party_type = 'PERSON';
1901
1902 -- local variables
1903 l_debug_prefix VARCHAR2(40) := 'pop_hz_employment_history';
1904 l_parent_id NUMBER; -- used to store parent entity identifier
1905 l_child_id NUMBER; -- used to store HZ_EMPLOYMENT_HISTORY identifier
1906 l_lud DATE; -- used to store the child last update date
1907 l_child_rec_exists_no NUMBER;
1908 l_pop_flag VARCHAR2(1);
1909 l_cen VARCHAR2(30) := 'HZ_EMPLOYMENT_HISTORY';
1910 BEGIN
1911 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_employment_history+',
1912 p_prefix=>l_debug_prefix,
1913 p_msg_level=>fnd_log.level_procedure);
1914
1915 -- validate p_operation
1916 IF p_operation IN ('I','U') THEN
1917 NULL;
1918 ELSE
1919 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_employment_history',
1920 p_prefix=>l_debug_prefix,
1921 p_msg_level=>fnd_log.level_procedure);
1922 RAISE FND_API.G_EXC_ERROR;
1923 END IF;
1924
1925 FOR c_child_rec IN c_child LOOP
1926 -- collect the child info into variables
1927 l_lud := c_child_rec.lud;
1928 l_child_id := c_child_rec.child_id;
1929 l_parent_id := c_child_rec.parent_id;
1930
1931 -- if record not existing, insert into hz_bus_obj_tracking
1932 BEGIN
1933 l_child_rec_exists_no := 0;
1934 SELECT child_id, populated_flag INTO l_child_rec_exists_no, l_pop_flag
1935 FROM HZ_BUS_OBJ_TRACKING
1936 WHERE event_id IS NULL
1937 AND CHILD_ENTITY_NAME = 'HZ_EMPLOYMENT_HISTORY'
1938 AND CHILD_ID = l_child_id
1939 AND rownum = 1;
1940
1941 IF l_child_rec_exists_no <> 0 THEN
1942 IF(l_pop_flag = 'Y') THEN
1943 UPDATE HZ_BUS_OBJ_TRACKING
1944 SET populated_flag = 'N'
1945 WHERE event_id IS NULL
1946 AND CHILD_ENTITY_NAME = l_cen
1947 AND CHILD_ID = l_child_id;
1948 END IF;
1949 -- data already exists, no need to write
1950 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
1951 p_prefix=>l_debug_prefix,
1952 p_msg_level=>fnd_log.level_procedure);
1953 END IF;
1954 EXCEPTION
1955 WHEN NO_DATA_FOUND THEN
1956 -- populate the child bo code also
1957 INSERT INTO HZ_BUS_OBJ_TRACKING
1958 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
1959 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
1960 ) VALUES (
1961 'N', p_operation, l_child_id, 'HZ_EMPLOYMENT_HISTORY', 'EMP_HIST',
1962 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, 'PERSON');
1963
1964 -- populate org party record
1965 pop_parent_record(p_child_id => l_parent_id,
1966 p_lud => l_lud,
1967 p_centity_name => 'HZ_PARTIES',
1968 p_cbo_code => 'PERSON',
1969 p_parent_id => NULL,
1970 p_pentity_name => NULL,
1971 p_pbo_code => NULL);
1972 END ; -- anonymous block end
1973 END LOOP;
1974
1975 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_employment_history-',
1976 p_prefix=>l_debug_prefix,
1977 p_msg_level=>fnd_log.level_procedure);
1978 EXCEPTION
1979 WHEN OTHERS THEN
1980 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
1981 p_prefix=>l_debug_prefix,
1982 p_msg_level=>fnd_log.level_procedure);
1983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1984 END pop_hz_employment_history;
1985
1986 -----------------------------------------------------------------
1987 -- Procedure name: pop_hz_education()
1988 -- Purpose: populates BOT for HZ_EDUCATION create or update
1989 -- Scope: internal
1990 -- Called From: V2 API
1991 -- Called By: HZ_EDUCATION create or update APIs
1992 -- Input Parameters:
1993 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
1994 -- p_education_id IN NUMBER
1995 --
1996 -- Note:
1997 -- Following are the allowed PARENT and CHILD BO combinations for HZ_EDUCATION
1998 -- This procedure must ensure that the combination is valid before populating BOT
1999 --
2000 -- PARENT BO: PERSON :: CHILD BO:
2001 -----------------------------------------------------------------
2002 PROCEDURE pop_hz_education(p_operation IN VARCHAR2, p_EDUCATION_ID IN NUMBER) IS
2003
2004 -- cursor statement to select the info from child table
2005 CURSOR C_child IS
2006 SELECT edu.LAST_UPDATE_DATE lud, edu.PARTY_ID parent_id, edu.EDUCATION_ID child_id
2007 FROM HZ_EDUCATION edu, HZ_PARTIES p
2008 WHERE edu.EDUCATION_ID = P_EDUCATION_ID
2009 AND edu.party_id = p.party_id
2010 AND p.party_type = 'PERSON';
2011
2012 -- local variables
2013 l_debug_prefix VARCHAR2(40) := 'pop_hz_education';
2017 l_child_rec_exists_no NUMBER;
2014 l_parent_id NUMBER; -- used to store parent entity identifier
2015 l_child_id NUMBER; -- used to store HZ_EDUCATION identifier
2016 l_lud DATE; -- used to store the child last update date
2018 l_cen VARCHAR2(30) := 'HZ_EDUCATION';
2019 BEGIN
2020 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_education+',
2021 p_prefix=>l_debug_prefix,
2022 p_msg_level=>fnd_log.level_procedure);
2023
2024 -- validate p_operation
2025 IF p_operation IN ('I','U') THEN
2026 NULL;
2027 ELSE
2028 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_education',
2029 p_prefix=>l_debug_prefix,
2030 p_msg_level=>fnd_log.level_procedure);
2031 RAISE FND_API.G_EXC_ERROR;
2032 END IF;
2033
2034 FOR c_child_rec IN c_child LOOP
2035 -- collect the child info into variables
2036 l_lud := c_child_rec.lud;
2037 l_child_id := c_child_rec.child_id;
2038 l_parent_id := c_child_rec.parent_id;
2039
2040 -- if record not existing, insert into hz_bus_obj_tracking
2041 BEGIN
2042 l_child_rec_exists_no := 0;
2043 SELECT child_id INTO l_child_rec_exists_no
2044 FROM HZ_BUS_OBJ_TRACKING
2045 WHERE event_id IS NULL
2046 AND CHILD_ENTITY_NAME = l_cen
2047 AND CHILD_ID = l_child_id
2048 AND rownum = 1;
2049
2050 IF l_child_rec_exists_no <> 0 THEN
2051 -- data already exists, no need to write
2052 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
2053 p_prefix=>l_debug_prefix,
2054 p_msg_level=>fnd_log.level_procedure);
2055 END IF;
2056 EXCEPTION
2057 WHEN NO_DATA_FOUND THEN
2058 INSERT INTO HZ_BUS_OBJ_TRACKING
2059 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
2060 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
2061 ) VALUES (
2062 'N', p_operation, l_child_id, 'HZ_EDUCATION', NULL,
2063 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, 'PERSON');
2064
2065 -- populate org party record
2066 pop_parent_record(p_child_id => l_parent_id,
2067 p_lud => l_lud,
2068 p_centity_name => 'HZ_PARTIES',
2069 p_cbo_code => 'PERSON',
2070 p_parent_id => NULL,
2071 p_pentity_name => NULL,
2072 p_pbo_code => NULL);
2073 END ; -- anonymous block end
2074 END LOOP;
2075
2076 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_education-',
2077 p_prefix=>l_debug_prefix,
2078 p_msg_level=>fnd_log.level_procedure);
2079 EXCEPTION
2080 WHEN OTHERS THEN
2081 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
2082 p_prefix=>l_debug_prefix,
2083 p_msg_level=>fnd_log.level_procedure);
2084 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2085 END pop_hz_education;
2086
2087 -----------------------------------------------------------------
2088 -- Procedure name: pop_hz_cust_site_uses_all()
2089 -- Purpose: populates BOT for HZ_CUST_SITE_USES_ALL create or update
2090 -- Scope: internal
2091 -- Called From: V2 API
2092 -- Called By: HZ_CUST_SITE_USES_ALL create or update APIs
2093 -- Input Parameters:
2094 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
2095 -- p_site_use_id IN NUMBER
2096 --
2097 -- Note:
2098 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CUST_SITE_USES_ALL
2099 -- This procedure must ensure that the combination is valid before populating BOT
2100 --
2101 -- PARENT BO: CUST_ACCT_SITE :: CHILD BO: CUST_ACCT_SITE_USE
2102 -----------------------------------------------------------------
2103 PROCEDURE pop_hz_cust_site_uses_all(p_operation IN VARCHAR2, p_site_use_id IN NUMBER) IS
2104
2105 -- cursor statement to select the info from child table
2106 CURSOR C_child IS
2107 SELECT casu.LAST_UPDATE_DATE lud, casu.CUST_ACCT_SITE_ID parent_id,
2108 casu.SITE_USE_ID child_id, cas.cust_account_id cust_acct_id
2109 FROM HZ_CUST_SITE_USES_ALL casu, HZ_CUST_ACCT_SITES_ALL cas
2110 WHERE casu.SITE_USE_ID = P_SITE_USE_ID
2111 AND casu.cust_acct_site_id = cas.cust_acct_site_id
2112 AND cas.cust_account_id > 0;
2113
2114 -- local variables
2115 l_debug_prefix VARCHAR2(40) := 'pop_hz_cust_site_uses_all';
2116 l_parent_id NUMBER; -- used to store parent entity identifier
2117 l_child_id NUMBER; -- used to store HZ_CUST_SITE_USES_ALL identifier
2118 l_lud DATE; -- used to store the child last update date
2119 l_child_rec_exists_no NUMBER;
2120 l_acct_id NUMBER;
2121 l_cen VARCHAR2(30) := 'HZ_CUST_SITE_USES_ALL';
2122 BEGIN
2123 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_site_uses_all+',
2124 p_prefix=>l_debug_prefix,
2125 p_msg_level=>fnd_log.level_procedure);
2126
2127 -- validate p_operation
2128 IF p_operation IN ('I','U') THEN
2129 NULL;
2130 ELSE
2131 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_cust_site_uses_all',
2132 p_prefix=>l_debug_prefix,
2133 p_msg_level=>fnd_log.level_procedure);
2134 RAISE FND_API.G_EXC_ERROR;
2135 END IF;
2136
2137 FOR c_child_rec IN c_child LOOP
2138 -- collect the child info into variables
2142 l_acct_id := c_child_rec.cust_acct_id;
2139 l_lud := c_child_rec.lud;
2140 l_child_id := c_child_rec.child_id;
2141 l_parent_id := c_child_rec.parent_id;
2143
2144 -- if record not existing, insert into hz_bus_obj_tracking
2145 BEGIN
2146 l_child_rec_exists_no := 0;
2147 SELECT child_id INTO l_child_rec_exists_no
2148 FROM HZ_BUS_OBJ_TRACKING
2149 WHERE event_id IS NULL
2150 AND CHILD_ENTITY_NAME = l_cen
2151 AND CHILD_ID = l_child_id
2152 AND rownum = 1;
2153
2154 IF l_child_rec_exists_no <> 0 THEN
2155 -- data already exists, no need to write
2156 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
2157 p_prefix=>l_debug_prefix,
2158 p_msg_level=>fnd_log.level_procedure);
2159 END IF;
2160 EXCEPTION
2161 WHEN NO_DATA_FOUND THEN
2162 -- populate the child bo code also
2163 INSERT INTO HZ_BUS_OBJ_TRACKING
2164 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
2165 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
2166 ) VALUES (
2167 'N', p_operation, l_child_id, 'HZ_CUST_SITE_USES_ALL', 'CUST_ACCT_SITE_USE',
2168 l_lud, l_lud, 'HZ_CUST_ACCT_SITES_ALL', l_parent_id, 'CUST_ACCT_SITE');
2169 END ; -- anonymous block end
2170 END LOOP;
2171
2172 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_site_uses_all-',
2173 p_prefix=>l_debug_prefix,
2174 p_msg_level=>fnd_log.level_procedure);
2175 EXCEPTION
2176 WHEN OTHERS THEN
2177 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
2178 p_prefix=>l_debug_prefix,
2179 p_msg_level=>fnd_log.level_procedure);
2180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2181 END pop_hz_cust_site_uses_all;
2182
2183 -----------------------------------------------------------------
2184 -- Procedure name: pop_hz_cust_profile_amts()
2185 -- Purpose: populates BOT for HZ_CUST_PROFILE_AMTS create or update
2186 -- Scope: internal
2187 -- Called From: V2 API
2188 -- Called By: HZ_CUST_PROFILE_AMTS create or update APIs
2189 -- Input Parameters:
2190 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
2191 -- p_cust_acct_profile_amt_id IN NUMBER
2192 --
2193 -- Note:
2194 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CUST_PROFILE_AMTS
2195 -- This procedure must ensure that the combination is valid before populating BOT
2196 --
2197 -- PARENT BO: CUST_PROFILE :: CHILD BO:
2198 -----------------------------------------------------------------
2199 PROCEDURE pop_hz_cust_profile_amts(p_operation IN VARCHAR2, p_cust_acct_profile_amt_id IN NUMBER) IS
2200
2201 -- cursor statement to select the info from child table
2202 CURSOR C_child IS
2203 SELECT cpa.LAST_UPDATE_DATE lud, cpa.CUST_ACCOUNT_PROFILE_ID parent_id,
2204 cpa.CUST_ACCT_PROFILE_AMT_ID child_id,
2205 nvl(cp.site_use_id, cp.cust_account_id) cp_parent_id,
2206 decode(cp.site_use_id, null, 'HZ_CUST_ACCOUNTS', 'HZ_CUST_SITE_USES_ALL') cp_parent_entity,
2207 decode(cp.site_use_id, null, 'CUST_ACCT', 'CUST_ACCT_SITE_USE') cp_parent_bo
2208 FROM HZ_CUST_PROFILE_AMTS cpa, HZ_CUSTOMER_PROFILES cp
2209 WHERE cpa.CUST_ACCT_PROFILE_AMT_ID = P_CUST_ACCT_PROFILE_AMT_ID
2210 AND cpa.cust_account_profile_id = cp.cust_account_profile_id
2211 AND cp.cust_account_id > 0;
2212
2213 -- local variables
2214 l_debug_prefix VARCHAR2(40) := 'pop_hz_cust_profile_amts';
2215 l_parent_id NUMBER; -- used to store parent entity identifier
2216 l_child_id NUMBER; -- used to store HZ_CUST_PROFILE_AMTS identifier
2217 l_lud DATE; -- used to store the child last update date
2218 l_child_rec_exists_no NUMBER;
2219 l_cp_parent_id NUMBER;
2220 l_cp_parent_entity VARCHAR2(30);
2221 l_cp_parent_bo VARCHAR2(30);
2222 l_cen VARCHAR2(30) := 'HZ_CUST_PROFILE_AMTS';
2223 BEGIN
2224 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_profile_amts+',
2225 p_prefix=>l_debug_prefix,
2226 p_msg_level=>fnd_log.level_procedure);
2227
2228 -- validate p_operation
2229 IF p_operation IN ('I','U') THEN
2230 NULL;
2231 ELSE
2232 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_cust_profile_amts',
2233 p_prefix=>l_debug_prefix,
2234 p_msg_level=>fnd_log.level_procedure);
2235 RAISE FND_API.G_EXC_ERROR;
2236 END IF;
2237
2238 FOR c_child_rec IN c_child LOOP
2239 -- collect the child info into variables
2240 l_lud := c_child_rec.lud;
2241 l_child_id := c_child_rec.child_id;
2242 l_parent_id := c_child_rec.parent_id;
2243 l_cp_parent_id := c_child_rec.cp_parent_id;
2244 l_cp_parent_entity := c_child_rec.cp_parent_entity;
2245 l_cp_parent_bo := c_child_rec.cp_parent_bo;
2246
2247 -- if record not existing, insert into hz_bus_obj_tracking
2248 BEGIN
2249 l_child_rec_exists_no := 0;
2250 SELECT child_id INTO l_child_rec_exists_no
2251 FROM HZ_BUS_OBJ_TRACKING
2252 WHERE event_id IS NULL
2253 AND CHILD_ENTITY_NAME = l_cen
2254 AND CHILD_ID = l_child_id
2255 AND rownum = 1;
2256
2257 IF l_child_rec_exists_no <> 0 THEN
2258 -- data already exists, no need to write
2259 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
2260 p_prefix=>l_debug_prefix,
2264 WHEN NO_DATA_FOUND THEN
2261 p_msg_level=>fnd_log.level_procedure);
2262 END IF;
2263 EXCEPTION
2265 INSERT INTO HZ_BUS_OBJ_TRACKING
2266 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
2267 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
2268 ) VALUES (
2269 'N', p_operation, l_child_id, 'HZ_CUST_PROFILE_AMTS', NULL,
2270 l_lud, l_lud, 'HZ_CUSTOMER_PROFILES', l_parent_id, 'CUST_PROFILE');
2271
2272 -- populate org party record
2273 pop_parent_record(p_child_id => l_parent_id,
2274 p_lud => l_lud,
2275 p_centity_name => 'HZ_CUSTOMER_PROFILES',
2276 p_cbo_code => 'CUST_PROFILE',
2277 p_parent_id => l_cp_parent_id,
2278 p_pentity_name => l_cp_parent_entity,
2279 p_pbo_code => l_cp_parent_bo);
2280 END ; -- anonymous block end
2281 END LOOP;
2282
2283 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_profile_amts-',
2284 p_prefix=>l_debug_prefix,
2285 p_msg_level=>fnd_log.level_procedure);
2286 EXCEPTION
2287 WHEN OTHERS THEN
2288 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
2289 p_prefix=>l_debug_prefix,
2290 p_msg_level=>fnd_log.level_procedure);
2291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2292 END pop_hz_cust_profile_amts;
2293
2294 -----------------------------------------------------------------
2295 -- Procedure name: pop_hz_cust_acct_sites_all()
2296 -- Purpose: populates BOT for HZ_CUST_ACCT_SITES_ALL create or update
2297 -- Scope: internal
2298 -- Called From: V2 API
2299 -- Called By: HZ_CUST_ACCT_SITES_ALL create or update APIs
2300 -- Input Parameters:
2301 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
2302 -- p_cust_acct_site_id IN NUMBER
2303 --
2304 -- Note:
2305 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CUST_ACCT_SITES_ALL
2306 -- This procedure must ensure that the combination is valid before populating BOT
2307 --
2308 -- PARENT BO: CUST_ACCT :: CHILD BO: CUST_ACCT_SITE
2309 -----------------------------------------------------------------
2310 PROCEDURE pop_hz_cust_acct_sites_all(p_operation IN VARCHAR2, p_cust_acct_site_id IN NUMBER) IS
2311
2312 -- cursor statement to select the info from child table
2313 CURSOR C_child IS
2314 SELECT cas.LAST_UPDATE_DATE lud, cas.CUST_ACCOUNT_ID parent_id, cas.CUST_ACCT_SITE_ID child_id,
2315 ca.party_id ca_parent_id,
2316 decode(p.party_type, 'ORGANIZATION', 'ORG', 'PERSON', 'PERSON', NULL) ca_parent_bo
2317 FROM HZ_CUST_ACCT_SITES_ALL cas, HZ_CUST_ACCOUNTS ca, HZ_PARTIES p
2318 WHERE cas.CUST_ACCT_SITE_ID = P_CUST_ACCT_SITE_ID
2319 AND cas.cust_account_id = ca.cust_account_id
2320 AND ca.party_id = p.party_id
2321 AND p.party_type in ('ORGANIZATION', 'PERSON')
2322 AND cas.cust_account_id > 0;
2323
2324 -- local variables
2325 l_debug_prefix VARCHAR2(40) := 'pop_hz_cust_acct_sites_all';
2326 l_parent_id NUMBER; -- used to store parent entity identifier
2327 l_child_id NUMBER; -- used to store HZ_CUST_ACCT_SITES_ALL identifier
2328 l_lud DATE; -- used to store the child last update date
2329 l_child_rec_exists_no NUMBER;
2330 l_ca_parent_id NUMBER;
2331 l_ca_parent_bo VARCHAR2(30);
2332 l_pop_flag VARCHAR2(1);
2333 l_cen VARCHAR2(30) := 'HZ_CUST_ACCT_SITES_ALL';
2334 BEGIN
2335 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_acct_sites_all+',
2336 p_prefix=>l_debug_prefix,
2337 p_msg_level=>fnd_log.level_procedure);
2338
2339 -- validate p_operation
2340 IF p_operation IN ('I','U') THEN
2341 NULL;
2342 ELSE
2343 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_cust_acct_sites_all',
2344 p_prefix=>l_debug_prefix,
2345 p_msg_level=>fnd_log.level_procedure);
2346 RAISE FND_API.G_EXC_ERROR;
2347 END IF;
2348
2349 FOR c_child_rec IN c_child LOOP
2350 -- collect the child info into variables
2351 l_lud := c_child_rec.lud;
2352 l_child_id := c_child_rec.child_id;
2353 l_parent_id := c_child_rec.parent_id;
2354 l_ca_parent_id := c_child_rec.ca_parent_id;
2355 l_ca_parent_bo := c_child_rec.ca_parent_bo;
2356
2357 -- if record not existing, insert into hz_bus_obj_tracking
2358 BEGIN
2359 l_child_rec_exists_no := 0;
2360 SELECT child_id, populated_flag INTO l_child_rec_exists_no, l_pop_flag
2361 FROM HZ_BUS_OBJ_TRACKING
2362 WHERE event_id IS NULL
2363 AND CHILD_ENTITY_NAME = l_cen
2364 AND CHILD_ID = l_child_id
2365 AND rownum = 1;
2366
2367 IF l_child_rec_exists_no <> 0 THEN
2368 IF(l_pop_flag = 'Y') THEN
2369 UPDATE HZ_BUS_OBJ_TRACKING
2370 SET populated_flag = 'N'
2371 WHERE event_id IS NULL
2372 AND CHILD_ENTITY_NAME = l_cen
2373 AND CHILD_ID = l_child_id;
2374 END IF;
2375 -- data already exists, no need to write
2376 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
2377 p_prefix=>l_debug_prefix,
2378 p_msg_level=>fnd_log.level_procedure);
2379 END IF;
2380 EXCEPTION
2381 WHEN NO_DATA_FOUND THEN
2382 -- populate the child bo code also
2383 INSERT INTO HZ_BUS_OBJ_TRACKING
2387 'N', p_operation, l_child_id, 'HZ_CUST_ACCT_SITES_ALL', 'CUST_ACCT_SITE',
2384 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
2385 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
2386 ) VALUES (
2388 l_lud, l_lud, 'HZ_CUST_ACCOUNTS', l_parent_id, 'CUST_ACCT');
2389 END ; -- anonymous block end
2390 END LOOP;
2391
2392 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_acct_sites_all-',
2393 p_prefix=>l_debug_prefix,
2394 p_msg_level=>fnd_log.level_procedure);
2395 EXCEPTION
2396 WHEN OTHERS THEN
2397 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
2398 p_prefix=>l_debug_prefix,
2399 p_msg_level=>fnd_log.level_procedure);
2400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2401 END pop_hz_cust_acct_sites_all;
2402
2403 -----------------------------------------------------------------
2404 -- Procedure name: pop_hz_cust_acct_relate_all()
2405 -- Purpose: populates BOT for HZ_CUST_ACCT_RELATE_ALL create or update
2406 -- Scope: internal
2407 -- Called From: V2 API
2408 -- Called By: HZ_CUST_ACCT_RELATE_ALL create or update APIs
2409 -- Input Parameters:
2410 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
2411 -- p_cust_account_id IN NUMBER
2412 -- p_related_cust_account_id IN NUMBER
2413 --
2414 -- Note:
2415 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CUST_ACCT_RELATE_ALL
2416 -- This procedure must ensure that the combination is valid before populating BOT
2417 --
2418 -- PARENT BO: CUST_ACCT :: CHILD BO:
2419 -----------------------------------------------------------------
2420 PROCEDURE pop_hz_cust_acct_relate_all(p_operation IN VARCHAR2,
2421 p_cust_acct_relate_id IN NUMBER) IS
2422
2423 -- cursor statement to select the info from child table
2424 CURSOR C_child IS
2425 SELECT car.LAST_UPDATE_DATE lud,
2426 car.cust_acct_relate_id child_id,
2427 car.cust_account_id cap_id,
2428 car.related_cust_account_id rcap_id,
2429 decode(p.party_type, 'ORGANIZATION', 'ORG_CUST', 'PERSON', 'PERSON_CUST', NULL) pbo,
2430 decode(rel_p.party_type, 'ORGANIZATION', 'ORG_CUST', 'PERSON', 'PERSON_CUST', NULL) rel_pbo,
2431 p.party_id pid, rel_p.party_id rel_pid
2432 FROM HZ_CUST_ACCT_RELATE_ALL car, HZ_CUST_ACCOUNTS ca, HZ_CUST_ACCOUNTS rel_ca,
2433 HZ_PARTIES p, HZ_PARTIES rel_p
2434 WHERE car.cust_acct_relate_id = p_cust_acct_relate_id
2435 AND car.cust_account_id = ca.cust_account_id
2436 AND car.related_cust_account_id = rel_ca.cust_account_id
2437 AND ca.party_id = p.party_id
2438 AND rel_ca.party_id = rel_p.party_id
2439 AND p.party_type in ('ORGANIZATION', 'PERSON')
2440 AND rel_p.party_type in ('ORGANIZATION', 'PERSON');
2441
2442 -- local variables
2443 l_debug_prefix VARCHAR2(40) := 'pop_hz_cust_acct_relate_all';
2444 l_child_id NUMBER; -- used to store HZ_CUST_ACCT_RELATE_ALL identifier
2445 l_lud DATE; -- used to store the child last update date
2446 l_child_rec_exists_no NUMBER;
2447 l_cap_id NUMBER;
2448 l_rcap_id NUMBER;
2449 l_pbo VARCHAR2(30);
2450 l_pid NUMBER;
2451 l_rel_pbo VARCHAR2(30);
2452 l_rel_pid NUMBER;
2453 l_cen VARCHAR2(30) := 'HZ_CUST_ACCT_RELATE_ALL';
2454 BEGIN
2455 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_acct_relate_all+',
2456 p_prefix=>l_debug_prefix,
2457 p_msg_level=>fnd_log.level_procedure);
2458
2459 -- validate p_operation
2460 IF p_operation IN ('I','U') THEN
2461 NULL;
2462 ELSE
2463 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_cust_acct_relate_all',
2464 p_prefix=>l_debug_prefix,
2465 p_msg_level=>fnd_log.level_procedure);
2466 RAISE FND_API.G_EXC_ERROR;
2467 END IF;
2468
2469 FOR c_child_rec IN c_child LOOP
2470 -- collect the child info into variables
2471 l_lud := c_child_rec.lud;
2472 l_pbo := c_child_rec.pbo;
2473 l_rel_pbo := c_child_rec.rel_pbo;
2474 l_pid := c_child_rec.pid;
2475 l_rel_pid := c_child_rec.rel_pid;
2476 l_child_id := c_child_rec.child_id;
2477 l_cap_id := c_child_rec.cap_id;
2478 l_rcap_id := c_child_rec.rcap_id;
2479
2480 -- if record not existing, insert into hz_bus_obj_tracking
2481 BEGIN
2482 l_child_rec_exists_no := 0;
2483 SELECT child_id INTO l_child_rec_exists_no
2484 FROM HZ_BUS_OBJ_TRACKING
2485 WHERE event_id IS NULL
2486 AND CHILD_ENTITY_NAME = l_cen
2487 AND CHILD_ID = l_child_id
2488 AND rownum = 1;
2489
2490 IF l_child_rec_exists_no <> 0 THEN
2491 -- data already exists, no need to write
2492 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
2493 p_prefix=>l_debug_prefix,
2494 p_msg_level=>fnd_log.level_procedure);
2495 END IF;
2496 EXCEPTION
2497 WHEN NO_DATA_FOUND THEN
2498 INSERT INTO HZ_BUS_OBJ_TRACKING
2499 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
2500 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
2501 ) VALUES (
2502 'N', p_operation, l_child_id, 'HZ_CUST_ACCT_RELATE_ALL', NULL,
2503 l_lud, l_lud, 'HZ_CUST_ACCOUNTS', l_cap_id, 'CUST_ACCT');
2504
2508 p_cbo_code => 'CUST_ACCT',
2505 pop_parent_record(p_child_id => l_cap_id,
2506 p_lud => l_lud,
2507 p_centity_name => 'HZ_CUST_ACCOUNTS',
2509 p_parent_id => l_pid,
2510 p_pentity_name => 'HZ_PARTIES',
2511 p_pbo_code => l_pbo);
2512
2513 pop_parent_record(p_child_id => l_pid,
2514 p_lud => l_lud,
2515 p_centity_name => 'HZ_PARTIES',
2516 p_cbo_code => l_pbo,
2517 p_parent_id => NULL,
2518 p_pentity_name => NULL,
2519 p_pbo_code => NULL);
2520
2521 INSERT INTO HZ_BUS_OBJ_TRACKING
2522 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
2523 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
2524 ) VALUES (
2525 'N', p_operation, l_child_id, 'HZ_CUST_ACCT_RELATE_ALL', NULL,
2526 l_lud, l_lud, 'HZ_CUST_ACCOUNTS', l_rcap_id, 'CUST_ACCT');
2527
2528 pop_parent_record(p_child_id => l_rcap_id,
2529 p_lud => l_lud,
2530 p_centity_name => 'HZ_CUST_ACCOUNTS',
2531 p_cbo_code => 'CUST_ACCT',
2532 p_parent_id => l_rel_pid,
2533 p_pentity_name => 'HZ_PARTIES',
2534 p_pbo_code => l_rel_pbo);
2535
2536 pop_parent_record(p_child_id => l_rel_pid,
2537 p_lud => l_lud,
2538 p_centity_name => 'HZ_PARTIES',
2539 p_cbo_code => l_rel_pbo,
2540 p_parent_id => NULL,
2541 p_pentity_name => NULL,
2542 p_pbo_code => NULL);
2543
2544 END ; -- anonymous block end
2545 END LOOP;
2546
2547 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_acct_relate_all-',
2548 p_prefix=>l_debug_prefix,
2549 p_msg_level=>fnd_log.level_procedure);
2550 EXCEPTION
2551 WHEN OTHERS THEN
2552 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
2553 p_prefix=>l_debug_prefix,
2554 p_msg_level=>fnd_log.level_procedure);
2555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2556 END pop_hz_cust_acct_relate_all;
2557
2558 -----------------------------------------------------------------
2559 -- Procedure name: pop_hz_cust_account_roles()
2560 -- Purpose: populates BOT for HZ_CUST_ACCOUNT_ROLES create or update
2561 -- Scope: internal
2562 -- Called From: V2 API
2563 -- Called By: HZ_CUST_ACCOUNT_ROLES create or update APIs
2564 -- Input Parameters:
2565 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
2566 -- p_cust_account_role_id IN NUMBER
2567 --
2568 -- Note:
2569 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CUST_ACCOUNT_ROLES
2570 -- This procedure must ensure that the combination is valid before populating BOT
2571 --
2572 -- PARENT BO: CUST_ACCT :: CHILD BO: CUST_ACCT_CONTACT
2573 -- PARENT BO: CUST_ACCT_SITE :: CHILD BO: CUST_ACCT_CONTACT
2574 -----------------------------------------------------------------
2575 PROCEDURE pop_hz_cust_account_roles(p_operation IN VARCHAR2, p_cust_account_role_id IN NUMBER) IS
2576
2577 -- cursor statement to select the info from child table
2578 CURSOR C_child IS
2579 SELECT car.LAST_UPDATE_DATE lud, car.CUST_ACCOUNT_ROLE_ID child_id,
2580 nvl(car.cust_acct_site_id, car.cust_account_id) parent_id,
2581 decode(car.cust_acct_site_id, null, 'CUST_ACCT', 'CUST_ACCT_SITE') parent_bo,
2582 decode(car.cust_acct_site_id, null, 'HZ_CUST_ACCOUNTS', 'HZ_CUST_ACCT_SITES_ALL') parent_entity,
2583 ca.cust_account_id ca_id
2584 FROM HZ_CUST_ACCOUNT_ROLES car, HZ_CUST_ACCOUNTS ca, HZ_PARTIES p
2585 WHERE car.CUST_ACCOUNT_ROLE_ID = P_CUST_ACCOUNT_ROLE_ID
2586 AND car.cust_account_id = ca.cust_account_id
2587 AND ca.party_id = p.party_id
2588 AND p.party_type in ('ORGANIZATION', 'PERSON')
2589 AND car.cust_account_id > 0;
2590
2591 -- local variables
2592 l_debug_prefix VARCHAR2(40) := 'pop_hz_cust_account_roles';
2593 l_parent_id NUMBER; -- used to store parent entity identifier
2594 l_parent_bo VARCHAR2(30);
2595 l_parent_entity VARCHAR2(30);
2596 l_child_id NUMBER; -- used to store HZ_CUST_ACCOUNT_ROLES identifier
2597 l_lud DATE; -- used to store the child last update date
2598 l_child_rec_exists_no NUMBER;
2599 l_ca_id NUMBER;
2600 l_pop_flag VARCHAR2(1);
2601 l_cen VARCHAR2(30) := 'HZ_CUST_ACCOUNT_ROLES';
2602 BEGIN
2603 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_account_roles+',
2604 p_prefix=>l_debug_prefix,
2605 p_msg_level=>fnd_log.level_procedure);
2606
2607 -- validate p_operation
2608 IF p_operation IN ('I','U') THEN
2609 NULL;
2610 ELSE
2611 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_cust_account_roles',
2612 p_prefix=>l_debug_prefix,
2613 p_msg_level=>fnd_log.level_procedure);
2614 RAISE FND_API.G_EXC_ERROR;
2615 END IF;
2616
2617 FOR c_child_rec IN c_child LOOP
2618 l_lud := c_child_rec.lud;
2619 l_child_id := c_child_rec.child_id;
2620 l_parent_id := c_child_rec.parent_id;
2621 l_parent_bo := c_child_rec.parent_bo;
2625 BEGIN
2622 l_parent_entity := c_child_rec.parent_entity;
2623
2624 -- if record not existing, insert into hz_bus_obj_tracking
2626 l_child_rec_exists_no := 0;
2627 SELECT child_id, populated_flag INTO l_child_rec_exists_no, l_pop_flag
2628 FROM HZ_BUS_OBJ_TRACKING
2629 WHERE event_id IS NULL
2630 AND CHILD_ENTITY_NAME = l_cen
2631 AND CHILD_ID = l_child_id
2632 AND rownum = 1;
2633
2634 IF l_child_rec_exists_no <> 0 THEN
2635 IF(l_pop_flag = 'Y') THEN
2636 UPDATE HZ_BUS_OBJ_TRACKING
2637 SET populated_flag = 'N'
2638 WHERE event_id IS NULL
2639 AND CHILD_ENTITY_NAME = l_cen
2640 AND CHILD_ID = l_child_id;
2641 END IF;
2642 -- data already exists, no need to write
2643 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
2644 p_prefix=>l_debug_prefix,
2645 p_msg_level=>fnd_log.level_procedure);
2646 END IF;
2647 EXCEPTION
2648 WHEN NO_DATA_FOUND THEN
2649 -- populate the child bo code also
2650 INSERT INTO HZ_BUS_OBJ_TRACKING
2651 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
2652 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
2653 ) VALUES (
2654 'N', p_operation, l_child_id, 'HZ_CUST_ACCOUNT_ROLES', 'CUST_ACCT_CONTACT',
2655 l_lud, l_lud, l_parent_entity, l_parent_id, l_parent_bo);
2656 END ; -- anonymous block end
2657 END LOOP;
2658
2659 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_account_roles-',
2660 p_prefix=>l_debug_prefix,
2661 p_msg_level=>fnd_log.level_procedure);
2662 EXCEPTION
2663 WHEN OTHERS THEN
2664 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
2665 p_prefix=>l_debug_prefix,
2666 p_msg_level=>fnd_log.level_procedure);
2667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2668 END pop_hz_cust_account_roles;
2669
2670 -----------------------------------------------------------------
2671 -- Procedure name: pop_hz_cust_accounts()
2672 -- Purpose: populates BOT for HZ_CUST_ACCOUNTS create or update
2673 -- Scope: internal
2674 -- Called From: V2 API
2675 -- Called By: HZ_CUST_ACCOUNTS create or update APIs
2676 -- Input Parameters:
2677 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
2678 -- p_cust_account_id IN NUMBER
2679 --
2680 -- Note:
2681 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CUST_ACCOUNTS
2682 -- This procedure must ensure that the combination is valid before populating BOT
2683 --
2684 -- PARENT BO: ORG_CUST :: CHILD BO: CUST_ACCT
2685 -- PARENT BO: PERSON_CUST :: CHILD BO: CUST_ACCT
2686 -----------------------------------------------------------------
2687 PROCEDURE pop_hz_cust_accounts(p_operation IN VARCHAR2, p_cust_account_id IN NUMBER) IS
2688
2689 -- cursor statement to select the info from child table
2690 CURSOR C_child IS
2691 SELECT ca.LAST_UPDATE_DATE lud, ca.PARTY_ID parent_id, ca.CUST_ACCOUNT_ID child_id,
2692 decode(p.party_type, 'ORGANIZATION', 'ORG_CUST', 'PERSON', 'PERSON_CUST', NULL) bo_code
2693 FROM HZ_CUST_ACCOUNTS ca, HZ_PARTIES p
2694 WHERE ca.CUST_ACCOUNT_ID = P_CUST_ACCOUNT_ID
2695 AND ca.party_id = p.party_id
2696 AND p.party_type in ('ORGANIZATION', 'PERSON')
2697 AND ca.cust_account_id > 0;
2698
2699 -- local variables
2700 l_debug_prefix VARCHAR2(40) := 'pop_hz_cust_accounts';
2701 l_parent_id NUMBER; -- used to store parent entity identifier
2702 l_bo_code VARCHAR2(30); -- used to store BO Code
2703 l_child_id NUMBER; -- used to store HZ_CUST_ACCOUNTS identifier
2704 l_lud DATE; -- used to store the child last update date
2705 l_child_rec_exists_no NUMBER;
2706 l_pop_flag VARCHAR2(1);
2707 l_cen VARCHAR2(30) := 'HZ_CUST_ACCOUNTS';
2708 BEGIN
2709 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_accounts+',
2710 p_prefix=>l_debug_prefix,
2711 p_msg_level=>fnd_log.level_procedure);
2712
2713 -- validate p_operation
2714 IF p_operation IN ('I','U') THEN
2715 NULL;
2716 ELSE
2717 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_cust_accounts',
2718 p_prefix=>l_debug_prefix,
2719 p_msg_level=>fnd_log.level_procedure);
2720 RAISE FND_API.G_EXC_ERROR;
2721 END IF;
2722
2723 FOR c_child_rec IN c_child LOOP
2724 -- collect the child info into variables
2725 l_lud := c_child_rec.lud;
2726 l_child_id := c_child_rec.child_id;
2727 l_parent_id := c_child_rec.parent_id;
2728 l_bo_code := c_child_rec.bo_code;
2729
2730 -- if record not existing, insert into hz_bus_obj_tracking
2731 BEGIN
2732 l_child_rec_exists_no := 0;
2733 SELECT child_id, populated_flag INTO l_child_rec_exists_no, l_pop_flag
2734 FROM HZ_BUS_OBJ_TRACKING
2735 WHERE event_id IS NULL
2736 AND CHILD_ENTITY_NAME = l_cen
2737 AND CHILD_ID = l_child_id
2738 AND rownum = 1;
2739
2740 IF l_child_rec_exists_no <> 0 THEN
2741 IF(l_pop_flag = 'Y') THEN
2742 UPDATE HZ_BUS_OBJ_TRACKING
2743 SET populated_flag = 'N'
2744 WHERE event_id IS NULL
2745 AND CHILD_ENTITY_NAME = l_cen
2746 AND CHILD_ID = l_child_id;
2747 END IF;
2748 -- data already exists, no need to write
2752 END IF;
2749 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
2750 p_prefix=>l_debug_prefix,
2751 p_msg_level=>fnd_log.level_procedure);
2753 EXCEPTION
2754 WHEN NO_DATA_FOUND THEN
2755 -- populate the child bo code also
2756 INSERT INTO HZ_BUS_OBJ_TRACKING
2757 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
2758 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
2759 ) VALUES (
2760 'N', p_operation, l_child_id, 'HZ_CUST_ACCOUNTS', 'CUST_ACCT',
2761 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, l_bo_code);
2762 END ; -- anonymous block end
2763 END LOOP;
2764
2765 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_cust_accounts-',
2766 p_prefix=>l_debug_prefix,
2767 p_msg_level=>fnd_log.level_procedure);
2768 EXCEPTION
2769 WHEN OTHERS THEN
2770 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
2771 p_prefix=>l_debug_prefix,
2772 p_msg_level=>fnd_log.level_procedure);
2773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2774 END pop_hz_cust_accounts;
2775
2776 -----------------------------------------------------------------
2777 -- Procedure name: pop_hz_customer_profiles()
2778 -- Purpose: populates BOT for HZ_CUSTOMER_PROFILES create or update
2779 -- Scope: internal
2780 -- Called From: V2 API
2781 -- Called By: HZ_CUSTOMER_PROFILES create or update APIs
2782 -- Input Parameters:
2783 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
2784 -- p_cust_account_profile_id IN NUMBER
2785 --
2786 -- Note:
2787 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CUSTOMER_PROFILES
2788 -- This procedure must ensure that the combination is valid before populating BOT
2789 --
2790 -- PARENT BO: CUST_ACCT :: CHILD BO: CUST_PROFILE
2791 -- PARENT BO: CUST_ACCT_SITE_USE :: CHILD BO: CUST_PROFILE
2792 -----------------------------------------------------------------
2793 PROCEDURE pop_hz_customer_profiles(p_operation IN VARCHAR2, p_cust_account_profile_id IN NUMBER) IS
2794
2795 -- cursor statement to select the info from child table
2796 CURSOR C_child IS
2797 SELECT cp.LAST_UPDATE_DATE lud,
2798 nvl(cp.site_use_id, cp.cust_account_id) parent_id,
2799 decode(cp.site_use_id, NULL, 'HZ_CUST_ACCOUNTS', 'HZ_CUST_SITE_USES_ALL') parent_entity,
2800 decode(cp.site_use_id, NULL, 'CUST_ACCT', 'CUST_ACCT_SITE_USE') parent_bo,
2801 cp.CUST_ACCOUNT_PROFILE_ID child_id
2802 FROM HZ_CUSTOMER_PROFILES cp
2803 WHERE cp.CUST_ACCOUNT_PROFILE_ID = P_CUST_ACCOUNT_PROFILE_ID
2804 AND cp.cust_account_id > 0;
2805
2806 -- local variables
2807 l_debug_prefix VARCHAR2(40) := 'pop_hz_customer_profiles';
2808 l_parent_id NUMBER; -- used to store parent entity identifier
2809 l_parent_bo VARCHAR2(30);
2810 l_parent_entity VARCHAR2(30);
2811 l_child_id NUMBER; -- used to store HZ_CUSTOMER_PROFILES identifier
2812 l_lud DATE; -- used to store the child last update date
2813 l_child_rec_exists_no NUMBER;
2814 l_pop_flag VARCHAR2(1);
2815 l_cen VARCHAR2(30) := 'HZ_CUSTOMER_PROFILES';
2816 BEGIN
2817 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_customer_profiles+',
2818 p_prefix=>l_debug_prefix,
2819 p_msg_level=>fnd_log.level_procedure);
2820
2821 -- validate p_operation
2822 IF p_operation IN ('I','U') THEN
2823 NULL;
2824 ELSE
2825 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_customer_profiles',
2826 p_prefix=>l_debug_prefix,
2827 p_msg_level=>fnd_log.level_procedure);
2828 RAISE FND_API.G_EXC_ERROR;
2829 END IF;
2830
2831 FOR c_child_rec IN c_child LOOP
2832 -- collect the child info into variables
2833 l_lud := c_child_rec.lud;
2834 l_child_id := c_child_rec.child_id;
2835 l_parent_id := c_child_rec.parent_id;
2836 l_parent_bo := c_child_rec.parent_bo;
2837 l_parent_entity := c_child_rec.parent_entity;
2838
2839 -- credit mgmt team create customer profile with customer account id = -1
2840 -- we will not populate bot table for this record
2841 IF(l_parent_id > 0) THEN
2842 -- if record not existing, insert into hz_bus_obj_tracking
2843 BEGIN
2844 l_child_rec_exists_no := 0;
2845 SELECT child_id, populated_flag INTO l_child_rec_exists_no, l_pop_flag
2846 FROM HZ_BUS_OBJ_TRACKING
2847 WHERE event_id IS NULL
2848 AND CHILD_ENTITY_NAME = l_cen
2849 AND CHILD_ID = l_child_id
2850 AND rownum = 1;
2851
2852 IF l_child_rec_exists_no <> 0 THEN
2853 IF(l_pop_flag = 'Y') THEN
2854 UPDATE HZ_BUS_OBJ_TRACKING
2855 SET populated_flag = 'N'
2856 WHERE event_id IS NULL
2857 AND CHILD_ENTITY_NAME = l_cen
2858 AND CHILD_ID = l_child_id;
2859 END IF;
2860 -- data already exists, no need to write
2861 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
2862 p_prefix=>l_debug_prefix,
2863 p_msg_level=>fnd_log.level_procedure);
2864 END IF;
2865 EXCEPTION
2866 WHEN NO_DATA_FOUND THEN
2867 -- populate the child bo code also
2868 INSERT INTO HZ_BUS_OBJ_TRACKING
2869 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
2873 l_lud, l_lud, l_parent_entity, l_parent_id, l_parent_bo);
2870 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
2871 ) VALUES (
2872 'N', p_operation, l_child_id, 'HZ_CUSTOMER_PROFILES', 'CUST_PROFILE',
2874 END ; -- anonymous block end
2875 END IF; -- l_parent_id > 0
2876 END LOOP;
2877
2878 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_customer_profiles-',
2879 p_prefix=>l_debug_prefix,
2880 p_msg_level=>fnd_log.level_procedure);
2881 EXCEPTION
2882 WHEN OTHERS THEN
2883 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
2884 p_prefix=>l_debug_prefix,
2885 p_msg_level=>fnd_log.level_procedure);
2886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2887 END pop_hz_customer_profiles;
2888
2889 -----------------------------------------------------------------
2890 -- Procedure name: pop_hz_credit_ratings()
2891 -- Purpose: populates BOT for HZ_CREDIT_RATINGS create or update
2892 -- Scope: internal
2893 -- Called From: V2 API
2894 -- Called By: HZ_CREDIT_RATINGS create or update APIs
2895 -- Input Parameters:
2896 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
2897 -- p_credit_rating_id IN NUMBER
2898 --
2899 -- Note:
2900 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CREDIT_RATINGS
2901 -- This procedure must ensure that the combination is valid before populating BOT
2902 --
2903 -- PARENT BO: ORG :: CHILD BO:
2904 -----------------------------------------------------------------
2905 PROCEDURE pop_hz_credit_ratings(p_operation IN VARCHAR2, p_credit_rating_id IN NUMBER) IS
2906
2907 -- cursor statement to select the info from child table
2908 CURSOR C_child IS
2909 SELECT cr.LAST_UPDATE_DATE lud, cr.PARTY_ID parent_id, cr.CREDIT_RATING_ID child_id
2910 FROM HZ_CREDIT_RATINGS cr, HZ_PARTIES p
2911 WHERE cr.CREDIT_RATING_ID = P_CREDIT_RATING_ID
2912 AND cr.party_id = p.party_id
2913 AND p.party_type = 'ORGANIZATION';
2914
2915 -- local variables
2916 l_debug_prefix VARCHAR2(40) := 'pop_hz_credit_ratings';
2917 l_parent_id NUMBER; -- used to store parent entity identifier
2918 l_child_id NUMBER; -- used to store HZ_CREDIT_RATINGS identifier
2919 l_lud DATE; -- used to store the child last update date
2920 l_child_rec_exists_no NUMBER;
2921 l_cen VARCHAR2(30) := 'HZ_CREDIT_RATINGS';
2922 BEGIN
2923 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_credit_ratings+',
2924 p_prefix=>l_debug_prefix,
2925 p_msg_level=>fnd_log.level_procedure);
2926
2927 -- validate p_operation
2928 IF p_operation IN ('I','U') THEN
2929 NULL;
2930 ELSE
2931 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_credit_ratings',
2932 p_prefix=>l_debug_prefix,
2933 p_msg_level=>fnd_log.level_procedure);
2934 RAISE FND_API.G_EXC_ERROR;
2935 END IF;
2936
2937 FOR c_child_rec IN c_child LOOP
2938 -- collect the child info into variables
2939 l_lud := c_child_rec.lud;
2940 l_child_id := c_child_rec.child_id;
2941 l_parent_id := c_child_rec.parent_id;
2942
2943 -- if record not existing, insert into hz_bus_obj_tracking
2944 BEGIN
2945 l_child_rec_exists_no := 0;
2946 SELECT child_id INTO l_child_rec_exists_no
2947 FROM HZ_BUS_OBJ_TRACKING
2948 WHERE event_id IS NULL
2949 AND CHILD_ENTITY_NAME = l_cen
2950 AND CHILD_ID = l_child_id
2951 AND rownum = 1;
2952
2953 IF l_child_rec_exists_no <> 0 THEN
2954 -- data already exists, no need to write
2955 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
2956 p_prefix=>l_debug_prefix,
2957 p_msg_level=>fnd_log.level_procedure);
2958 END IF;
2959 EXCEPTION
2960 WHEN NO_DATA_FOUND THEN
2961 INSERT INTO HZ_BUS_OBJ_TRACKING
2962 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
2963 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
2964 ) VALUES (
2965 'N', p_operation, l_child_id, 'HZ_CREDIT_RATINGS', NULL,
2966 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, 'ORG');
2967
2968 pop_parent_record(p_child_id => l_parent_id,
2969 p_lud => l_lud,
2970 p_centity_name => 'HZ_PARTIES',
2971 p_cbo_code => 'ORG',
2972 p_parent_id => NULL,
2973 p_pentity_name => NULL,
2974 p_pbo_code => NULL);
2975 END ; -- anonymous block end
2976 END LOOP;
2977
2978 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_credit_ratings-',
2979 p_prefix=>l_debug_prefix,
2980 p_msg_level=>fnd_log.level_procedure);
2981 EXCEPTION
2982 WHEN OTHERS THEN
2983 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
2984 p_prefix=>l_debug_prefix,
2985 p_msg_level=>fnd_log.level_procedure);
2986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2987 END pop_hz_credit_ratings;
2988
2989 -----------------------------------------------------------------
2990 -- Procedure name: pop_hz_contact_preferences()
2991 -- Purpose: populates BOT for HZ_CONTACT_PREFERENCES create or update
2992 -- Scope: internal
2993 -- Called From: V2 API
2994 -- Called By: HZ_CONTACT_PREFERENCES create or update APIs
2995 -- Input Parameters:
2999 -- Note:
2996 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
2997 -- p_contact_preference_id IN NUMBER
2998 --
3000 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CONTACT_PREFERENCES
3001 -- This procedure must ensure that the combination is valid before populating BOT
3002 --
3003 -- PARENT BO: ORG :: CHILD BO:
3004 -- PARENT BO: PERSON :: CHILD BO:
3005 -- PARENT BO: ORG_CONTACT :: CHILD BO:
3006 -- PARENT BO: PARTY_SITE :: CHILD BO:
3007 -- PARENT BO: PHONE :: CHILD BO:
3008 -- PARENT BO: TLX :: CHILD BO:
3009 -- PARENT BO: EMAIL :: CHILD BO:
3010 -- PARENT BO: WEB :: CHILD BO:
3011 -- PARENT BO: EDI :: CHILD BO:
3012 -- PARENT BO: EFT :: CHILD BO:
3013 -- PARENT BO: SMS :: CHILD BO:
3014 -----------------------------------------------------------------
3015 PROCEDURE pop_hz_contact_preferences(p_operation IN VARCHAR2, p_contact_preference_id IN NUMBER) IS
3016
3017 CURSOR c_child IS
3018 SELECT cpp.LAST_UPDATE_DATE lud, cpp.CONTACT_LEVEL_TABLE_ID parent_id,
3019 cpp.CONTACT_LEVEL_TABLE parent_tbl_name, cpp.CONTACT_PREFERENCE_ID child_id
3020 FROM HZ_CONTACT_PREFERENCES cpp
3021 WHERE cpp.CONTACT_PREFERENCE_ID = P_CONTACT_PREFERENCE_ID;
3022
3023 CURSOR c_cp(p_parent_id NUMBER) IS
3024 SELECT decode(contact_point_type, 'PHONE', 'PHONE', 'EMAIL', 'EMAIL', 'WEB', 'WEB', 'EFT', 'EFT', 'SMS', 'SMS', 'TLX', 'TLX', 'EDI', 'EDI', NULL), owner_table_name, owner_table_id
3025 FROM HZ_CONTACT_POINTS
3026 WHERE contact_point_id = p_parent_id
3027 AND contact_point_type in ('PHONE', 'EMAIL', 'TLX', 'WEB', 'EFT', 'EDI', 'SMS');
3028
3029 CURSOR c_pty(p_parent_id NUMBER) IS
3030 SELECT decode(party_type, 'ORGANIZATION', 'ORG', 'PERSON', 'PERSON', 'PARTY_RELATIONSHIP', 'ORG_CONTACT', NULL),
3031 decode(party_type, 'ORGANIZATION', 'HZ_PARTIES', 'PERSON', 'HZ_PARTIES', 'PARTY_RELATIONSHIP', 'HZ_ORG_CONTACTS', NULL)
3032 FROM HZ_PARTIES
3033 WHERE party_id = p_parent_id
3034 AND party_type in ('ORGANIZATION', 'PERSON', 'PARTY_RELATIONSHIP');
3035
3036 CURSOR c_oc(p_parent_id NUMBER) IS
3037 SELECT oc.org_contact_id
3038 FROM HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc
3039 WHERE r.relationship_id = oc.party_relationship_id
3040 AND r.subject_type = 'PERSON'
3041 AND r.object_type = 'ORGANIZATION'
3042 AND r.party_id = p_parent_id
3043 AND rownum = 1;
3044
3045 -- local variables
3046 l_debug_prefix VARCHAR2(40) := 'pop_hz_contact_preferences';
3047 l_parent_id NUMBER; -- used to store parent entity identifier
3048 l_parent_tbl_name VARCHAR2(30); -- used to store parent entity name
3049 l_pty_bo_code VARCHAR2(30);
3050 l_pty_tbl_name VARCHAR2(30);
3051 l_bo_code VARCHAR2(30); -- used to store BO Code
3052 l_child_bo_code VARCHAR2(30); -- used to store Child BO Code (if child entity is a root node)
3053 l_child_id NUMBER; -- used to store HZ_CONTACT_PREFERENCES identifier
3054 l_lud DATE; -- used to store the child last update date
3055 l_child_rec_exists_no NUMBER;
3056 l_cp_owner_table VARCHAR2(30);
3057 l_cp_owner_id NUMBER;
3058 l_oc_id NUMBER;
3059 l_cen VARCHAR2(30) := 'HZ_CONTACT_PREFERENCES';
3060 BEGIN
3061 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_contact_preferences+',
3062 p_prefix=>l_debug_prefix,
3063 p_msg_level=>fnd_log.level_procedure);
3064
3065 -- validate p_operation
3066 IF p_operation IN ('I','U') THEN
3067 NULL;
3068 ELSE
3069 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_contact_preferences',
3070 p_prefix=>l_debug_prefix,
3071 p_msg_level=>fnd_log.level_procedure);
3072 RAISE FND_API.G_EXC_ERROR;
3073 END IF;
3074
3075 FOR c_child_rec IN c_child LOOP
3076 -- collect the child info into variables
3077 l_lud := c_child_rec.lud;
3078 l_child_id := c_child_rec.child_id;
3079 l_parent_id := c_child_rec.parent_id;
3080 l_parent_tbl_name := c_child_rec.parent_tbl_name;
3081
3082 IF(l_parent_tbl_name = 'HZ_PARTY_SITES') THEN
3083 l_bo_code := 'PARTY_SITE';
3084 IF NOT(is_valid_ps(l_parent_id)) THEN
3085 RETURN;
3086 END IF;
3087 ELSIF(l_parent_tbl_name = 'HZ_CONTACT_POINTS') THEN
3088 OPEN c_cp(l_parent_id);
3089 FETCH c_cp INTO l_bo_code, l_cp_owner_table, l_cp_owner_id;
3090 CLOSE c_cp;
3091 IF l_bo_code in ('PHONE', 'SMS', 'EDI') and l_cp_owner_table = 'HZ_PARTIES' THEN
3092 OPEN c_pty(l_cp_owner_id);
3093 FETCH c_pty INTO l_pty_bo_code, l_pty_tbl_name;
3094 CLOSE c_pty;
3095 IF(l_bo_code = 'PHONE' AND l_pty_bo_code IS NULL) THEN
3096 RETURN;
3097 ELSIF(l_bo_code = 'PHONE' AND l_pty_bo_code = 'ORG_CONTACT') THEN
3098 OPEN c_oc(l_cp_owner_id);
3099 FETCH c_oc INTO l_oc_id;
3100 CLOSE c_oc;
3101 IF(l_oc_id IS NULL) THEN
3102 RETURN;
3103 END IF;
3104 ELSIF(l_bo_code = 'SMS' AND l_pty_bo_code = 'ORG') THEN
3105 RETURN;
3106 ELSIF(l_bo_code = 'EDI' AND l_pty_bo_code = 'PERSON') THEN
3107 RETURN;
3108 END IF;
3109 END IF;
3110 ELSIF(l_parent_tbl_name = 'HZ_PARTIES') THEN
3111 OPEN c_pty(l_parent_id);
3112 FETCH c_pty INTO l_bo_code, l_parent_tbl_name;
3113 CLOSE c_pty;
3114 IF(l_bo_code = 'ORG_CONTACT') THEN
3115 OPEN c_oc(l_parent_id);
3116 FETCH c_oc INTO l_oc_id;
3117 CLOSE c_oc;
3118 IF(l_oc_id IS NULL) THEN
3122 END IF;
3119 RETURN;
3120 ELSE
3121 l_parent_id := l_oc_id;
3123 END IF;
3124 END IF;
3125
3126 -- if record not existing, insert into hz_bus_obj_tracking
3127 BEGIN
3128 l_child_rec_exists_no := 0;
3129 SELECT child_id INTO l_child_rec_exists_no
3130 FROM HZ_BUS_OBJ_TRACKING
3131 WHERE event_id IS NULL
3132 AND CHILD_ENTITY_NAME = l_cen
3133 AND CHILD_ID = l_child_id
3134 AND rownum = 1;
3135
3136 IF l_child_rec_exists_no <> 0 THEN
3137 -- data already exists, no need to write
3138 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
3139 p_prefix=>l_debug_prefix,
3140 p_msg_level=>fnd_log.level_procedure);
3141 END IF;
3142 EXCEPTION
3143 WHEN NO_DATA_FOUND THEN
3144 INSERT INTO HZ_BUS_OBJ_TRACKING
3145 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
3146 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
3147 ) VALUES (
3148 'N', p_operation, l_child_id, 'HZ_CONTACT_PREFERENCES', NULL,
3149 l_lud, l_lud, l_parent_tbl_name, l_parent_id, l_bo_code);
3150 END ; -- anonymous block end
3151 END LOOP;
3152
3153 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_contact_preferences-',
3154 p_prefix=>l_debug_prefix,
3155 p_msg_level=>fnd_log.level_procedure);
3156 EXCEPTION
3157 WHEN OTHERS THEN
3158 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
3159 p_prefix=>l_debug_prefix,
3160 p_msg_level=>fnd_log.level_procedure);
3161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3162 END pop_hz_contact_preferences;
3163
3164 -----------------------------------------------------------------
3165 -- Procedure name: pop_hz_contact_points()
3166 -- Purpose: populates BOT for HZ_CONTACT_POINTS create or update
3167 -- Scope: internal
3168 -- Called From: V2 API
3169 -- Called By: HZ_CONTACT_POINTS create or update APIs
3170 -- Input Parameters:
3171 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
3172 -- p_contact_point_id IN NUMBER
3173 --
3174 -- Note:
3175 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CONTACT_POINTS
3176 -- This procedure must ensure that the combination is valid before populating BOT
3177 --
3178 -- PARENT BO: ORG :: CHILD BO: PHONE
3179 -- PARENT BO: ORG :: CHILD BO: TLX
3180 -- PARENT BO: ORG :: CHILD BO: EMAIL
3181 -- PARENT BO: ORG :: CHILD BO: WEB
3182 -- PARENT BO: ORG :: CHILD BO: EDI
3183 -- PARENT BO: ORG :: CHILD BO: EFT
3184 -- PARENT BO: PERSON :: CHILD BO: PHONE
3185 -- PARENT BO: PERSON :: CHILD BO: EMAIL
3186 -- PARENT BO: PERSON :: CHILD BO: WEB
3187 -- PARENT BO: PERSON :: CHILD BO: SMS
3188 -- PARENT BO: ORG_CONTACT :: CHILD BO: PHONE
3189 -- PARENT BO: ORG_CONTACT :: CHILD BO: TLX
3190 -- PARENT BO: ORG_CONTACT :: CHILD BO: EMAIL
3191 -- PARENT BO: ORG_CONTACT :: CHILD BO: WEB
3192 -- PARENT BO: ORG_CONTACT :: CHILD BO: SMS
3193 -- PARENT BO: PARTY_SITE :: CHILD BO: PHONE
3194 -- PARENT BO: PARTY_SITE :: CHILD BO: TLX
3195 -- PARENT BO: PARTY_SITE :: CHILD BO: EMAIL
3196 -- PARENT BO: PARTY_SITE :: CHILD BO: WEB
3197 -----------------------------------------------------------------
3198 PROCEDURE pop_hz_contact_points(p_operation IN VARCHAR2, p_contact_point_id IN NUMBER) IS
3199
3200 -- cursor statement to select the info from child table
3201 CURSOR c_child IS
3202 SELECT LAST_UPDATE_DATE lud, OWNER_TABLE_ID parent_id, CONTACT_POINT_ID child_id,
3203 OWNER_TABLE_NAME parent_entity, CONTACT_POINT_TYPE child_bo_code
3204 FROM HZ_CONTACT_POINTS
3205 WHERE CONTACT_POINT_ID = P_CONTACT_POINT_ID
3206 AND OWNER_TABLE_NAME in ('HZ_PARTY_SITES', 'HZ_PARTIES');
3207
3208 -- cursor statement to select the info from party parent table
3209 CURSOR c_party_parent(p_parent_id IN NUMBER) IS
3210 SELECT decode(party_type, 'ORGANIZATION', 'ORG', 'PERSON', 'PERSON', 'PARTY_RELATIONSHIP', 'ORG_CONTACT', NULL),
3211 decode(party_type, 'ORGANIZATION', 'HZ_PARTIES', 'PERSON', 'HZ_PARTIES', 'PARTY_RELATIONSHIP', 'HZ_ORG_CONTACTS', NULL)
3212 FROM HZ_PARTIES
3213 WHERE PARTY_ID = p_parent_id
3214 AND party_type in ('ORGANIZATION', 'PERSON', 'PARTY_RELATIONSHIP');
3215
3216 CURSOR c_oc(p_parent_id NUMBER) IS
3217 SELECT oc.org_contact_id
3218 FROM HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc
3219 WHERE r.relationship_id = oc.party_relationship_id
3220 AND r.subject_type = 'PERSON'
3221 AND r.object_type = 'ORGANIZATION'
3222 AND r.party_id = p_parent_id
3223 AND rownum = 1;
3224
3225 -- local variables
3226 l_debug_prefix VARCHAR2(40) := 'pop_hz_contact_points';
3227 l_parent_id NUMBER; -- used to store parent entity identifier
3228 l_parent_tbl_name VARCHAR2(30); -- used to store parent entity name
3229 l_bo_code VARCHAR2(30); -- used to store BO Code
3230 l_child_bo_code VARCHAR2(30); -- used to store Child BO Code (if child entity is a root node)
3231 l_child_id NUMBER; -- used to store HZ_CONTACT_POINTS identifier
3232 l_lud DATE; -- used to store the child last update date
3233 l_child_rec_exists_no NUMBER;
3234 l_oc_id NUMBER;
3235 l_cen VARCHAR2(30) := 'HZ_CONTACT_POINTS';
3236 BEGIN
3237 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_contact_points+',
3238 p_prefix=>l_debug_prefix,
3239 p_msg_level=>fnd_log.level_procedure);
3240
3241 -- validate p_operation
3245 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_contact_points',
3242 IF p_operation IN ('I','U') THEN
3243 NULL;
3244 ELSE
3246 p_prefix=>l_debug_prefix,
3247 p_msg_level=>fnd_log.level_procedure);
3248 RAISE FND_API.G_EXC_ERROR;
3249 END IF;
3250
3251 FOR c_child_rec IN c_child LOOP
3252 -- collect the child info into variables
3253 l_lud := c_child_rec.lud;
3254 l_child_id := c_child_rec.child_id;
3255 l_child_bo_code := c_child_rec.child_bo_code;
3256 l_parent_id := c_child_rec.parent_id;
3257 l_parent_tbl_name := c_child_rec.parent_entity;
3258
3259 IF(l_parent_tbl_name = 'HZ_PARTY_SITES') THEN
3260 l_bo_code := 'PARTY_SITE';
3261 ELSIF(l_parent_tbl_name = 'HZ_PARTIES') THEN
3262 OPEN c_party_parent(l_parent_id);
3263 FETCH c_party_parent INTO l_bo_code, l_parent_tbl_name;
3264 CLOSE c_party_parent;
3265 IF(l_bo_code = 'ORG_CONTACT') THEN
3266 OPEN c_oc(l_parent_id);
3267 FETCH c_oc INTO l_oc_id;
3268 CLOSE c_oc;
3269 IF(l_oc_id IS NULL) THEN
3270 RETURN;
3271 ELSE
3272 l_parent_id := l_oc_id;
3273 END IF;
3274 END IF;
3275 END IF;
3276
3277 -- check invalid combination
3278 CASE
3279 WHEN l_child_bo_code = 'PHONE' THEN
3280 NULL;
3281 WHEN l_bo_code = 'PARTY_SITE' AND l_child_bo_code in ('TLX','EMAIL','WEB') THEN
3282 NULL;
3283 WHEN l_bo_code = 'ORG' AND l_child_bo_code in ('TLX','EMAIL','WEB','EDI','EFT') THEN
3284 NULL;
3285 WHEN l_bo_code = 'PERSON' AND l_child_bo_code in ('EMAIL','WEB','SMS') THEN
3286 NULL;
3287 WHEN l_bo_code = 'ORG_CONTACT' AND l_child_bo_code in ('TLX','EMAIL','WEB','SMS') THEN
3288 NULL;
3289 ELSE
3290 RETURN;
3291 END CASE;
3292
3293 CASE
3294 WHEN l_bo_code = 'PARTY_SITE' THEN
3295 IF NOT(is_valid_ps(l_parent_id)) THEN
3296 RETURN;
3297 END IF;
3298 ELSE
3299 NULL;
3300 END CASE;
3301
3302 -- if record not existing, insert into hz_bus_obj_tracking
3303 BEGIN
3304 l_child_rec_exists_no := 0;
3305 SELECT child_id INTO l_child_rec_exists_no
3306 FROM HZ_BUS_OBJ_TRACKING
3307 WHERE event_id IS NULL
3308 AND CHILD_ENTITY_NAME = l_cen
3309 AND CHILD_ID = l_child_id
3310 AND rownum = 1;
3311
3312 IF l_child_rec_exists_no <> 0 THEN
3313 -- data already exists, no need to write
3314 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
3315 p_prefix=>l_debug_prefix,
3316 p_msg_level=>fnd_log.level_procedure);
3317 END IF;
3318 EXCEPTION
3319 WHEN NO_DATA_FOUND THEN
3320 -- populate the child bo code also
3321 INSERT INTO HZ_BUS_OBJ_TRACKING
3322 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
3323 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
3324 ) VALUES (
3325 'N', p_operation, l_child_id, 'HZ_CONTACT_POINTS', l_child_bo_code,
3326 l_lud, l_lud, l_parent_tbl_name, l_parent_id, l_bo_code);
3327 END ; -- anonymous block end
3328 END LOOP;
3329
3330 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_contact_points-',
3331 p_prefix=>l_debug_prefix,
3332 p_msg_level=>fnd_log.level_procedure);
3333 EXCEPTION
3334 WHEN OTHERS THEN
3335 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
3336 p_prefix=>l_debug_prefix,
3337 p_msg_level=>fnd_log.level_procedure);
3338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3339 END pop_hz_contact_points;
3340
3341 -----------------------------------------------------------------
3342 -- Procedure name: pop_hz_code_assignments()
3343 -- Purpose: populates BOT for HZ_CODE_ASSIGNMENTS create or update
3344 -- Scope: internal
3345 -- Called From: V2 API
3346 -- Called By: HZ_CODE_ASSIGNMENTS create or update APIs
3347 -- p_code_assignment_id IN NUMBER
3348 --
3349 -- Note:
3350 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CODE_ASSIGNMENTS
3351 -- This procedure must ensure that the combination is valid before populating BOT
3352 --
3353 -- PARENT BO: ORG :: CHILD BO:
3354 -- PARENT BO: PERSON :: CHILD BO:
3355 -----------------------------------------------------------------
3356 PROCEDURE pop_hz_code_assignments(p_operation IN VARCHAR2, p_code_assignment_id IN NUMBER) IS
3357
3358 CURSOR C_child IS
3359 SELECT pp.LAST_UPDATE_DATE lud, pp.OWNER_TABLE_ID parent_id, pp.CODE_ASSIGNMENT_ID child_id,
3360 decode(p.party_type, 'ORGANIZATION', 'ORG', 'PERSON', 'PERSON', NULL) bo_code
3361 FROM HZ_CODE_ASSIGNMENTS pp, HZ_PARTIES p
3362 WHERE pp.code_assignment_id = p_code_assignment_id
3363 AND pp.OWNER_TABLE_ID = p.party_id
3364 AND pp.OWNER_TABLE_NAME = 'HZ_PARTIES'
3365 AND p.party_type in ('ORGANIZATION', 'PERSON');
3366
3367 -- local variables
3368 l_debug_prefix VARCHAR2(40) := 'pop_hz_code_assignments';
3369 l_parent_id NUMBER; -- used to store parent entity identifier
3370 l_child_id NUMBER; -- used to store HZ_CODE_ASSIGNMENTS identifier
3371 l_lud DATE; -- used to store the child last update date
3372 l_bo_code VARCHAR2(30);
3373 l_child_rec_exists_no NUMBER;
3374 l_cen VARCHAR2(30) := 'HZ_CODE_ASSIGNMENTS';
3375 BEGIN
3379
3376 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_code_assignments+',
3377 p_prefix=>l_debug_prefix,
3378 p_msg_level=>fnd_log.level_procedure);
3380 -- validate p_operation
3381 IF p_operation IN ('I','U') THEN
3382 NULL;
3383 ELSE
3384 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_code_assignments',
3385 p_prefix=>l_debug_prefix,
3386 p_msg_level=>fnd_log.level_procedure);
3387 RAISE FND_API.G_EXC_ERROR;
3388 END IF;
3389
3390 FOR c_child_rec IN c_child LOOP
3391 -- collect the child info into variables
3392 l_lud := c_child_rec.lud;
3393 l_child_id := c_child_rec.child_id;
3394 l_parent_id := c_child_rec.parent_id;
3395 l_bo_code := c_child_rec.bo_code;
3396
3397 -- if record not existing, insert into hz_bus_obj_tracking
3398 BEGIN
3399 l_child_rec_exists_no := 0;
3400 SELECT child_id INTO l_child_rec_exists_no
3401 FROM HZ_BUS_OBJ_TRACKING
3402 WHERE event_id IS NULL
3403 AND CHILD_ENTITY_NAME = l_cen
3404 AND CHILD_ID = l_child_id
3405 AND rownum = 1;
3406
3407 IF l_child_rec_exists_no <> 0 THEN
3408 -- data already exists, no need to write
3409 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
3410 p_prefix=>l_debug_prefix,
3411 p_msg_level=>fnd_log.level_procedure);
3412 END IF;
3413 EXCEPTION
3414 WHEN NO_DATA_FOUND THEN
3415 INSERT INTO HZ_BUS_OBJ_TRACKING
3416 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
3417 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
3418 ) VALUES (
3419 'N', p_operation, l_child_id, 'HZ_CODE_ASSIGNMENTS', NULL,
3420 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, l_bo_code);
3421
3422 pop_parent_record(p_child_id => l_parent_id,
3423 p_lud => l_lud,
3424 p_centity_name => 'HZ_PARTIES',
3425 p_cbo_code => l_bo_code,
3426 p_parent_id => NULL,
3427 p_pentity_name => NULL,
3428 p_pbo_code => NULL);
3429 END ; -- anonymous block end
3430 END LOOP;
3431
3432 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_code_assignments-',
3433 p_prefix=>l_debug_prefix,
3434 p_msg_level=>fnd_log.level_procedure);
3435 EXCEPTION
3436 WHEN OTHERS THEN
3437 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
3438 p_prefix=>l_debug_prefix,
3439 p_msg_level=>fnd_log.level_procedure);
3440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3441 END pop_hz_code_assignments;
3442
3443 -----------------------------------------------------------------
3444 -- Procedure name: pop_hz_citizenship()
3445 -- Purpose: populates BOT for HZ_CITIZENSHIP create or update
3446 -- Scope: internal
3447 -- Called From: V2 API
3448 -- Called By: HZ_CITIZENSHIP create or update APIs
3449 -- Input Parameters:
3450 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
3451 -- p_citizenship_id IN NUMBER
3452 --
3453 -- Note:
3454 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CITIZENSHIP
3455 -- This procedure must ensure that the combination is valid before populating BOT
3456 --
3457 -- PARENT BO: PERSON :: CHILD BO:
3458 -----------------------------------------------------------------
3459 PROCEDURE pop_hz_citizenship(p_operation IN VARCHAR2, p_citizenship_id IN NUMBER) IS
3460
3461 -- cursor statement to select the info from child table
3462 CURSOR C_child IS
3463 SELECT c.LAST_UPDATE_DATE lud, c.PARTY_ID parent_id, c.CITIZENSHIP_ID child_id
3464 FROM HZ_CITIZENSHIP c, HZ_PARTIES p
3465 WHERE c.CITIZENSHIP_ID = P_CITIZENSHIP_ID
3466 AND c.party_id = p.party_id
3467 AND p.party_type = 'PERSON';
3468
3469 -- local variables
3470 l_debug_prefix VARCHAR2(40) := 'pop_hz_citizenship';
3471 l_parent_id NUMBER; -- used to store parent entity identifier
3472 l_child_id NUMBER; -- used to store HZ_CITIZENSHIP identifier
3473 l_lud DATE; -- used to store the child last update date
3474 l_child_rec_exists_no NUMBER;
3475 l_cen VARCHAR2(30) := 'HZ_CITIZENSHIP';
3476 BEGIN
3477 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_citizenship+',
3478 p_prefix=>l_debug_prefix,
3479 p_msg_level=>fnd_log.level_procedure);
3480
3481 -- validate p_operation
3482 IF p_operation IN ('I','U') THEN
3483 NULL;
3484 ELSE
3485 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_citizenship',
3486 p_prefix=>l_debug_prefix,
3487 p_msg_level=>fnd_log.level_procedure);
3488 RAISE FND_API.G_EXC_ERROR;
3489 END IF;
3490
3491 FOR c_child_rec IN c_child LOOP
3492 -- collect the child info into variables
3493 l_lud := c_child_rec.lud;
3494 l_child_id := c_child_rec.child_id;
3495 l_parent_id := c_child_rec.parent_id;
3496
3497 -- if record not existing, insert into hz_bus_obj_tracking
3498 BEGIN
3499 l_child_rec_exists_no := 0;
3500 SELECT child_id INTO l_child_rec_exists_no
3501 FROM HZ_BUS_OBJ_TRACKING
3502 WHERE event_id IS NULL
3503 AND CHILD_ENTITY_NAME = l_cen
3504 AND CHILD_ID = l_child_id
3505 AND rownum = 1;
3509 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
3506
3507 IF l_child_rec_exists_no <> 0 THEN
3508 -- data already exists, no need to write
3510 p_prefix=>l_debug_prefix,
3511 p_msg_level=>fnd_log.level_procedure);
3512 END IF;
3513 EXCEPTION
3514 WHEN NO_DATA_FOUND THEN
3515 INSERT INTO HZ_BUS_OBJ_TRACKING
3516 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
3517 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
3518 ) VALUES (
3519 'N', p_operation, l_child_id, 'HZ_CITIZENSHIP', NULL,
3520 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, 'PERSON');
3521
3522 pop_parent_record(p_child_id => l_parent_id,
3523 p_lud => l_lud,
3524 p_centity_name => 'HZ_PARTIES',
3525 p_cbo_code => 'PERSON',
3526 p_parent_id => NULL,
3527 p_pentity_name => NULL,
3528 p_pbo_code => NULL);
3529 END ; -- anonymous block end
3530 END LOOP;
3531
3532 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_citizenship-',
3533 p_prefix=>l_debug_prefix,
3534 p_msg_level=>fnd_log.level_procedure);
3535 EXCEPTION
3536 WHEN OTHERS THEN
3537 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
3538 p_prefix=>l_debug_prefix,
3539 p_msg_level=>fnd_log.level_procedure);
3540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3541 END pop_hz_citizenship;
3542
3543 -----------------------------------------------------------------
3544 -- Procedure name: pop_hz_certifications()
3545 -- Purpose: populates BOT for HZ_CERTIFICATIONS create or update
3546 -- Scope: internal
3547 -- Called From: V2 API
3548 -- Called By: HZ_CERTIFICATIONS create or update APIs
3549 -- Input Parameters:
3550 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
3551 -- p_certification_id IN NUMBER
3552 --
3553 -- Note:
3554 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CERTIFICATIONS
3555 -- This procedure must ensure that the combination is valid before populating BOT
3556 --
3557 -- PARENT BO: ORG :: CHILD BO:
3558 -- PARENT BO: PERSON :: CHILD BO:
3559 -----------------------------------------------------------------
3560 PROCEDURE pop_hz_certifications(p_operation IN VARCHAR2, p_certification_id IN NUMBER) IS
3561
3562 -- cursor statement to select the info from child table
3563 CURSOR C_child IS
3564 SELECT c.LAST_UPDATE_DATE lud, c.PARTY_ID parent_id, c.CERTIFICATION_ID child_id,
3565 decode(p.party_type, 'ORGANIZATION', 'ORG', 'PERSON', 'PERSON', NULL) bo_code
3566 FROM HZ_CERTIFICATIONS c, HZ_PARTIES p
3567 WHERE c.CERTIFICATION_ID = P_CERTIFICATION_ID
3568 AND c.party_id = p.party_id
3569 AND p.party_type in ('ORGANIZATION', 'PERSON');
3570
3571 -- local variables
3572 l_debug_prefix VARCHAR2(40) := 'pop_hz_certifications';
3573 l_parent_id NUMBER; -- used to store parent entity identifier
3574 l_bo_code VARCHAR2(30); -- used to store BO Code
3575 l_child_id NUMBER; -- used to store HZ_CERTIFICATIONS identifier
3576 l_lud DATE; -- used to store the child last update date
3577 l_child_rec_exists_no NUMBER;
3578 l_cen VARCHAR2(30) := 'HZ_CERTIFICATIONS';
3579 BEGIN
3580 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_certifications+',
3581 p_prefix=>l_debug_prefix,
3582 p_msg_level=>fnd_log.level_procedure);
3583
3584 -- validate p_operation
3585 IF p_operation IN ('I','U') THEN
3586 NULL;
3587 ELSE
3588 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_certifications',
3589 p_prefix=>l_debug_prefix,
3590 p_msg_level=>fnd_log.level_procedure);
3591 RAISE FND_API.G_EXC_ERROR;
3592 END IF;
3593
3594 FOR c_child_rec IN c_child LOOP
3595 l_lud := c_child_rec.lud;
3596 l_child_id := c_child_rec.child_id;
3597 l_parent_id := c_child_rec.parent_id;
3598 l_bo_code := c_child_rec.bo_code;
3599
3600 -- if record not existing, insert into hz_bus_obj_tracking
3601 BEGIN
3602 l_child_rec_exists_no := 0;
3603 SELECT child_id INTO l_child_rec_exists_no
3604 FROM HZ_BUS_OBJ_TRACKING
3605 WHERE event_id IS NULL
3606 AND CHILD_ENTITY_NAME = l_cen
3607 AND CHILD_ID = l_child_id
3608 AND rownum = 1;
3609
3610 IF l_child_rec_exists_no <> 0 THEN
3611 -- data already exists, no need to write
3612 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
3613 p_prefix=>l_debug_prefix,
3614 p_msg_level=>fnd_log.level_procedure);
3615 END IF;
3616 EXCEPTION
3617 WHEN NO_DATA_FOUND THEN
3618 INSERT INTO HZ_BUS_OBJ_TRACKING
3619 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
3620 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
3621 ) VALUES (
3622 'N', p_operation, l_child_id, 'HZ_CERTIFICATIONS', NULL,
3623 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, l_bo_code);
3624
3625 pop_parent_record(p_child_id => l_parent_id,
3626 p_lud => l_lud,
3627 p_centity_name => 'HZ_PARTIES',
3628 p_cbo_code => l_bo_code,
3632 END ; -- anonymous block end
3629 p_parent_id => NULL,
3630 p_pentity_name => NULL,
3631 p_pbo_code => NULL);
3633 END LOOP;
3634
3635 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_certifications-',
3636 p_prefix=>l_debug_prefix,
3637 p_msg_level=>fnd_log.level_procedure);
3638 EXCEPTION
3639 WHEN OTHERS THEN
3640 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
3641 p_prefix=>l_debug_prefix,
3642 p_msg_level=>fnd_log.level_procedure);
3643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3644 END pop_hz_certifications;
3645 -----------------------------------------------------------------
3646 -- Procedure name: pop_HZ_PARTY_USG_ASSIGNMENTS()
3647 -- Purpose: populates BOT for HZ_PARTY_USG_ASSIGNMENTS create or update
3648 -- Scope: internal
3649 -- Called From: V2 API
3650 -- Called By: HZ_PARTY_USG_ASSIGNMENTS create or update APIs
3651 -- Input Parameters:
3652 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
3653 -- p_PARTY_USG_ASSIGNMENT_id IN NUMBER
3654 --
3655 -- Note:
3656 -- Following are the allowed PARENT and CHILD BO combinations for HZ_CERTIFICATIONS
3657 -- This procedure must ensure that the combination is valid before populating BOT
3658 --
3659 -- PARENT BO: ORG :: CHILD BO:
3660 -- PARENT BO: PERSON :: CHILD BO:
3661 -----------------------------------------------------------------
3662 PROCEDURE pop_HZ_PARTY_USG_ASSIGNMENTS (
3663 p_operation IN VARCHAR2,
3664 p_PARTY_USG_ASSIGNMENT_ID IN NUMBER) IS
3665
3666 -- cursor statement to select the info from child table
3667 CURSOR C_child IS
3668 SELECT c.LAST_UPDATE_DATE lud, c.PARTY_ID parent_id, c.PARTY_USG_ASSIGNMENT_ID child_id,
3669 decode(p.party_type, 'ORGANIZATION', 'ORG', 'PERSON', 'PERSON', NULL) bo_code
3670 FROM HZ_PARTY_USG_ASSIGNMENTS c, HZ_PARTIES p
3671 WHERE c.PARTY_USG_ASSIGNMENT_ID = P_PARTY_USG_ASSIGNMENT_ID
3672 AND c.party_id = p.party_id
3673 AND p.party_type in ('ORGANIZATION', 'PERSON');
3674
3675 -- local variables
3676 l_debug_prefix VARCHAR2(40) := 'pop_HZ_PARTY_USG_ASSIGNMENTS';
3677 l_parent_id NUMBER; -- used to store parent entity identifier
3678 l_bo_code VARCHAR2(30); -- used to store BO Code
3679 l_child_id NUMBER; -- used to store HZ_PARTY_USG_ASSIGNMENTS identifier
3680 l_lud DATE; -- used to store the child last update date
3681 l_child_rec_exists_no NUMBER;
3682 l_cen VARCHAR2(30) := 'HZ_PARTY_USG_ASSIGNMENTS';
3683 BEGIN
3684 hz_utility_v2pub.DEBUG(p_message=>'pop_HZ_PARTY_USG_ASSIGNMENTS+',
3685 p_prefix=>l_debug_prefix,
3686 p_msg_level=>fnd_log.level_procedure);
3687
3688 -- validate p_operation
3689 IF p_operation IN ('I','U') THEN
3690 NULL;
3691 ELSE
3692 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_HZ_PARTY_USG_ASSIGNMENTS',
3693 p_prefix=>l_debug_prefix,
3694 p_msg_level=>fnd_log.level_procedure);
3695 RAISE FND_API.G_EXC_ERROR;
3696 END IF;
3697
3698 FOR c_child_rec IN c_child LOOP
3699 l_lud := c_child_rec.lud;
3700 l_child_id := c_child_rec.child_id;
3701 l_parent_id := c_child_rec.parent_id;
3702 l_bo_code := c_child_rec.bo_code;
3703
3704 -- if record not existing, insert into hz_bus_obj_tracking
3705 BEGIN
3706 l_child_rec_exists_no := 0;
3707 SELECT child_id INTO l_child_rec_exists_no
3708 FROM HZ_BUS_OBJ_TRACKING
3709 WHERE event_id IS NULL
3710 AND CHILD_ENTITY_NAME = l_cen
3711 AND CHILD_ID = l_child_id
3712 AND rownum = 1;
3713
3714 IF l_child_rec_exists_no <> 0 THEN
3715 -- data already exists, no need to write
3716 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
3717 p_prefix=>l_debug_prefix,
3718 p_msg_level=>fnd_log.level_procedure);
3719 END IF;
3720 EXCEPTION
3721 WHEN NO_DATA_FOUND THEN
3722 INSERT INTO HZ_BUS_OBJ_TRACKING
3723 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
3724 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
3725 ) VALUES (
3726 'N', p_operation, l_child_id, 'HZ_PARTY_USG_ASSIGNMENTS', NULL,
3727 l_lud, l_lud, 'HZ_PARTIES', l_parent_id, l_bo_code);
3728
3729 pop_parent_record(p_child_id => l_parent_id,
3730 p_lud => l_lud,
3731 p_centity_name => 'HZ_PARTIES',
3732 p_cbo_code => l_bo_code,
3733 p_parent_id => NULL,
3734 p_pentity_name => NULL,
3735 p_pbo_code => NULL);
3736 END ; -- anonymous block end
3737 END LOOP;
3738
3739 hz_utility_v2pub.DEBUG(p_message=>'pop_HZ_PARTY_USG_ASSIGNMENTS-',
3740 p_prefix=>l_debug_prefix,
3741 p_msg_level=>fnd_log.level_procedure);
3742 EXCEPTION
3743 WHEN OTHERS THEN
3744 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
3745 p_prefix=>l_debug_prefix,
3746 p_msg_level=>fnd_log.level_procedure);
3747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3748 END pop_HZ_PARTY_USG_ASSIGNMENTS;
3749
3750 -----------------------------------------------------------------
3751 -- Procedure name: pop_hz_extensibility()
3755 -- Called By: HZ_EXTENSIBILITY_PUB create or update APIs
3752 -- Purpose: populates BOT for extensibility create or update
3753 -- Scope: internal
3754 -- Called From: V2 API
3756 -- Input Parameters:
3757 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
3758 -- p_certification_id IN NUMBER
3759 --
3760 -- Note:
3761 -- Following are the allowed PARENT and CHILD BO combinations for extensibility attributes
3762 -- This procedure must ensure that the combination is valid before populating BOT
3763 --
3764 -- PARENT BO: ORG :: CHILD BO:
3765 -- PARENT BO: PERSON :: CHILD BO:
3766 -- PARENT BO: LOCATION :: CHILD BO:
3767 -- PARENT BO: PARTY_SITE :: CHILD BO:
3768 -----------------------------------------------------------------
3769 PROCEDURE pop_hz_extensibility(p_operation IN VARCHAR2, p_object_type IN VARCHAR2, p_extension_id IN NUMBER) IS
3770
3771 -- cursor statement to select the info from child table
3772 CURSOR C_child_org IS
3773 SELECT c.LAST_UPDATE_DATE lud, p.PARTY_ID parent_id, c.extension_id child_id, 'ORG' bo_code, 'HZ_PARTIES' parent_entity
3774 FROM HZ_ORG_PROFILES_EXT_B c, HZ_ORGANIZATION_PROFILES p
3775 WHERE c.EXTENSION_ID = P_EXTENSION_ID
3776 AND c.ORGANIZATION_PROFILE_ID = p.ORGANIZATION_PROFILE_ID
3777 AND rownum = 1;
3778
3779 CURSOR C_child_per IS
3780 SELECT c.LAST_UPDATE_DATE lud, p.PARTY_ID parent_id, c.extension_id child_id, 'PERSON' bo_code, 'HZ_PARTIES' parent_entity
3781 FROM HZ_PER_PROFILES_EXT_B c, HZ_PERSON_PROFILES p
3782 WHERE c.EXTENSION_ID = P_EXTENSION_ID
3783 AND c.PERSON_PROFILE_ID = p.PERSON_PROFILE_ID
3784 AND rownum = 1;
3785
3786 CURSOR C_child_loc IS
3787 SELECT c.LAST_UPDATE_DATE lud, c.LOCATION_ID parent_id, c.extension_id child_id, 'LOCATION' bo_code, 'HZ_LOCATIONS' parent_entity
3788 FROM HZ_LOCATIONS_EXT_B c
3789 WHERE c.EXTENSION_ID = P_EXTENSION_ID
3790 AND rownum = 1;
3791
3792 CURSOR C_child_ps IS
3793 SELECT c.LAST_UPDATE_DATE lud, c.PARTY_SITE_ID parent_id, c.extension_id child_id, 'PARTY_SITE' bo_code, 'HZ_PARTY_SITES' parent_entity
3794 FROM HZ_PARTY_SITES_EXT_B c
3795 WHERE c.EXTENSION_ID = P_EXTENSION_ID
3796 AND rownum = 1;
3797
3798 -- local variables
3799 l_debug_prefix VARCHAR2(40) := 'pop_hz_extensibility';
3800 l_parent_id NUMBER; -- used to store parent entity identifier
3801 l_bo_code VARCHAR2(30); -- used to store BO Code
3802 l_child_entity VARCHAR2(30);
3803 l_parent_entity VARCHAR2(30);
3804 l_child_id NUMBER; -- used to store extensibility attributes identifier
3805 l_lud DATE; -- used to store the child last update date
3806 l_child_rec_exists_no NUMBER;
3807 BEGIN
3808 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_extensibility+',
3809 p_prefix=>l_debug_prefix,
3810 p_msg_level=>fnd_log.level_procedure);
3811
3812 -- validate p_operation
3813 IF p_operation IN ('I','U') THEN
3814 NULL;
3815 ELSE
3816 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_hz_extensibility',
3817 p_prefix=>l_debug_prefix,
3818 p_msg_level=>fnd_log.level_procedure);
3819 RAISE FND_API.G_EXC_ERROR;
3820 END IF;
3821
3822 IF(p_object_type = 'ORG') THEN
3823 OPEN c_child_org;
3824 l_child_entity := 'HZ_ORG_PROFILES_EXT_VL';
3825 ELSIF(p_object_type = 'PERSON') THEN
3826 OPEN c_child_per;
3827 l_child_entity := 'HZ_PER_PROFILES_EXT_VL';
3828 ELSIF(p_object_type = 'LOCATION') THEN
3829 OPEN c_child_loc;
3830 l_child_entity := 'HZ_LOCATIONS_EXT_VL';
3831 ELSIF(p_object_type = 'PARTY_SITE') THEN
3832 OPEN c_child_ps;
3833 l_child_entity := 'HZ_PARTY_SITES_EXT_VL';
3834 END IF;
3835
3836 IF(p_object_type = 'ORG') THEN
3837 FETCH c_child_org INTO l_lud, l_parent_id, l_child_id, l_bo_code, l_parent_entity;
3838 ELSIF(p_object_type = 'PERSON') THEN
3839 FETCH c_child_per INTO l_lud, l_parent_id, l_child_id, l_bo_code, l_parent_entity;
3840 ELSIF(p_object_type = 'LOCATION') THEN
3841 FETCH c_child_loc INTO l_lud, l_parent_id, l_child_id, l_bo_code, l_parent_entity;
3842 ELSIF(p_object_type = 'PARTY_SITE') THEN
3843 FETCH c_child_ps INTO l_lud, l_parent_id, l_child_id, l_bo_code, l_parent_entity;
3844 END IF;
3845
3846 -- if record not existing, insert into hz_bus_obj_tracking
3847 BEGIN
3848 l_child_rec_exists_no := 0;
3849 SELECT child_id INTO l_child_rec_exists_no
3850 FROM HZ_BUS_OBJ_TRACKING
3851 WHERE event_id IS NULL
3852 AND CHILD_ENTITY_NAME = l_child_entity
3853 AND CHILD_ID = l_child_id
3854 AND rownum = 1;
3855
3856 IF l_child_rec_exists_no <> 0 THEN
3857 -- data already exists, no need to write
3858 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
3859 p_prefix=>l_debug_prefix,
3860 p_msg_level=>fnd_log.level_procedure);
3861 END IF;
3862 EXCEPTION
3863 WHEN NO_DATA_FOUND THEN
3864 INSERT INTO HZ_BUS_OBJ_TRACKING
3865 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
3866 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
3867 ) VALUES (
3868 'N', p_operation, l_child_id, l_child_entity, NULL,
3869 l_lud, l_lud, l_parent_entity, l_parent_id, l_bo_code);
3870 END ; -- anonymous block end
3871
3872 IF(p_object_type = 'ORG') THEN
3873 CLOSE c_child_org;
3874 ELSIF(p_object_type = 'PERSON') THEN
3875 CLOSE c_child_per;
3876 ELSIF(p_object_type = 'LOCATION') THEN
3877 CLOSE c_child_loc;
3881
3878 ELSIF(p_object_type = 'PARTY_SITE') THEN
3879 CLOSE c_child_ps;
3880 END IF;
3882 hz_utility_v2pub.DEBUG(p_message=>'pop_hz_extensibility-',
3883 p_prefix=>l_debug_prefix,
3884 p_msg_level=>fnd_log.level_procedure);
3885 EXCEPTION
3886 WHEN OTHERS THEN
3887 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
3888 p_prefix=>l_debug_prefix,
3889 p_msg_level=>fnd_log.level_procedure);
3890 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3891 END pop_hz_extensibility;
3892
3893 -----------------------------------------------------------------
3894 -- Procedure name: pop_ra_cust_receipt_methods()
3895 -- Purpose: populates BOT for RA_CUST_RECEIPT_METHODS create or update
3896 -- Scope: internal
3897 -- Called From: V2 API
3898 -- Called By: HZ_PAYMENT_METHOD_PUB create or update APIs
3899 -- Input Parameters:
3900 -- p_operation -- contains I or U. 'I' if create API is calling this otherwise 'U'.
3901 -- p_cust_receipt_method_id IN NUMBER
3902 --
3903 -- Note:
3904 -- Following are the allowed PARENT and CHILD BO combinations for RA_CUST_RECEIPT_METHODS
3905 -- This procedure must ensure that the combination is valid before populating BOT
3906 --
3907 -- PARENT BO: CUST_ACCT_SITE_USE :: CHILD BO:
3908 -- PARENT BO: CUST_ACCT :: CHILD BO:
3909 -----------------------------------------------------------------
3910 PROCEDURE pop_ra_cust_receipt_methods(p_operation IN VARCHAR2, p_cust_receipt_method_id IN NUMBER) IS
3911
3912 -- cursor statement to select the info from child table
3913 CURSOR C_child IS
3914 SELECT rcrm.last_update_date lud, rcrm.cust_receipt_method_id child_id,
3915 nvl(rcrm.site_use_id, rcrm.customer_id) parent_id,
3916 decode(rcrm.site_use_id, NULL, 'HZ_CUST_ACCOUNTS', 'HZ_CUST_SITE_USES_ALL') parent_tbl_name,
3917 decode(rcrm.site_use_id, NULL, 'CUST_ACCT', 'CUST_ACCT_SITE_USE') parent_bo_code,
3918 decode(rcrm.site_use_id, NULL, p.party_id, rcrm.customer_id) grand_parent_id,
3919 decode(rcrm.site_use_id, NULL, 'HZ_PARTIES', 'HZ_CUST_ACCOUNTS') grand_parent_tbl_name,
3920 decode(rcrm.site_use_id, NULL, decode(p.party_type, 'ORGANIZATION', 'ORG_CUST', 'PERSON', 'PERSON_CUST', NULL), 'CUST_ACCT') grand_parent_bo_code
3921 FROM RA_CUST_RECEIPT_METHODS rcrm, HZ_CUST_ACCOUNTS ca, HZ_PARTIES p
3922 WHERE rcrm.cust_receipt_method_id = p_cust_receipt_method_id
3923 AND rcrm.customer_id = ca.cust_account_id
3924 AND ca.party_id = p.party_id
3925 AND p.party_type in ('ORGANIZATION', 'PERSON')
3926 AND rcrm.customer_id > 0;
3927
3928 -- local variables
3929 l_debug_prefix VARCHAR2(40) := 'pop_ra_cust_receipt_methods';
3930 l_parent_id NUMBER; -- used to store parent entity identifier
3931 l_bo_code VARCHAR2(30); -- used to store BO Code
3932 l_child_id NUMBER; -- used to store HZ_CERTIFICATIONS identifier
3933 l_lud DATE; -- used to store the child last update date
3934 l_child_rec_exists_no NUMBER;
3935 l_parent_bo_code VARCHAR2(30);
3936 l_parent_tbl_name VARCHAR2(30);
3937 l_g_parent_id NUMBER;
3938 l_g_parent_tbl_name VARCHAR2(30);
3939 l_g_parent_bo_code VARCHAR2(30);
3940 l_cen VARCHAR2(30) := 'RA_CUST_RECEIPT_METHODS';
3941 BEGIN
3942 hz_utility_v2pub.DEBUG(p_message=>'pop_ra_cust_receipt_methods+',
3943 p_prefix=>l_debug_prefix,
3944 p_msg_level=>fnd_log.level_procedure);
3945
3946 -- validate p_operation
3947 IF p_operation IN ('I','U') THEN
3948 NULL;
3949 ELSE
3950 hz_utility_v2pub.DEBUG(p_message=> 'incorrect operation flag sent to pop_ra_cust_receipt_methods',
3951 p_prefix=>l_debug_prefix,
3952 p_msg_level=>fnd_log.level_procedure);
3953 RAISE FND_API.G_EXC_ERROR;
3954 END IF;
3955
3956 FOR c_child_rec IN c_child LOOP
3957 l_lud := c_child_rec.lud;
3958 l_child_id := c_child_rec.child_id;
3959 l_parent_id := c_child_rec.parent_id;
3960 l_parent_tbl_name := c_child_rec.parent_tbl_name;
3961 l_parent_bo_code := c_child_rec.parent_bo_code;
3962 l_g_parent_id := c_child_rec.grand_parent_id;
3963 l_g_parent_tbl_name := c_child_rec.grand_parent_tbl_name;
3964 l_g_parent_bo_code := c_child_rec.grand_parent_bo_code;
3965
3966 -- if record not existing, insert into hz_bus_obj_tracking
3967 BEGIN
3968 l_child_rec_exists_no := 0;
3969 SELECT child_id INTO l_child_rec_exists_no
3970 FROM HZ_BUS_OBJ_TRACKING
3971 WHERE event_id IS NULL
3972 AND CHILD_ENTITY_NAME = l_cen
3973 AND CHILD_ID = l_child_id
3974 AND rownum = 1;
3975
3976 IF l_child_rec_exists_no <> 0 THEN
3977 -- data already exists, no need to write
3978 hz_utility_v2pub.DEBUG(p_message=> 'CHILD record already exists in BOT',
3979 p_prefix=>l_debug_prefix,
3980 p_msg_level=>fnd_log.level_procedure);
3981 END IF;
3982 EXCEPTION
3983 WHEN NO_DATA_FOUND THEN
3984 INSERT INTO HZ_BUS_OBJ_TRACKING
3985 ( POPULATED_FLAG, CHILD_OPERATION_FLAG, CHILD_ID, CHILD_ENTITY_NAME, CHILD_BO_CODE,
3986 LAST_UPDATE_DATE, CREATION_DATE, PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE
3987 ) VALUES (
3988 'N', p_operation, l_child_id, 'RA_CUST_RECEIPT_METHODS', NULL,
3989 l_lud, l_lud, l_parent_tbl_name, l_parent_id, l_parent_bo_code);
3990
3991 pop_parent_record(p_child_id => l_parent_id,
3992 p_lud => l_lud,
3993 p_centity_name => l_parent_tbl_name,
3994 p_cbo_code => l_parent_bo_code,
3995 p_parent_id => l_g_parent_id,
3996 p_pentity_name => l_g_parent_tbl_name,
3997 p_pbo_code => l_g_parent_bo_code);
3998 END ; -- anonymous block end
3999 END LOOP;
4000
4001 hz_utility_v2pub.DEBUG(p_message=>'pop_ra_cust_receipt_methods-',
4002 p_prefix=>l_debug_prefix,
4003 p_msg_level=>fnd_log.level_procedure);
4004 EXCEPTION
4005 WHEN OTHERS THEN
4006 hz_utility_v2pub.DEBUG(p_message=> SQLERRM,
4007 p_prefix=>l_debug_prefix,
4008 p_msg_level=>fnd_log.level_procedure);
4009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4010 END pop_ra_cust_receipt_methods;
4011
4012 FUNCTION is_valid_ps(
4013 p_party_site_id IN NUMBER
4014 ) RETURN BOOLEAN IS
4015 CURSOR get_ps IS
4016 SELECT p.party_type, p.party_id
4017 FROM HZ_PARTY_SITES ps, HZ_PARTIES p
4018 WHERE ps.party_site_id = p_party_site_id
4019 AND ps.party_id = p.party_id
4020 AND p.party_type in ('ORGANIZATION', 'PERSON', 'PARTY_RELATIONSHIP');
4021
4022 CURSOR get_oc(l_party_id NUMBER) IS
4023 SELECT 1
4024 FROM HZ_PARTIES p, HZ_RELATIONSHIPS r
4025 WHERE p.party_id = l_party_id
4026 AND p.party_id = r.party_id
4027 AND r.subject_type = 'PERSON'
4028 AND r.object_type = 'ORGANIZATION'
4029 AND rownum = 1;
4030
4031 l_party_type VARCHAR2(30);
4032 l_party_id NUMBER;
4033 l_dummy NUMBER;
4034 BEGIN
4035 OPEN get_ps;
4036 FETCH get_ps INTO l_party_type, l_party_id;
4037 CLOSE get_ps;
4038 IF(l_party_type IS NULL) THEN
4039 RETURN FALSE;
4040 ELSIF(l_party_type = 'PARTY_RELATIONSHIP') THEN
4041 OPEN get_oc(l_party_id);
4042 FETCH get_oc INTO l_dummy;
4043 CLOSE get_oc;
4044 IF(l_dummy IS NULL) THEN
4045 RETURN FALSE;
4046 END IF;
4047 END IF;
4048 RETURN TRUE;
4049 END;
4050
4051 END HZ_POPULATE_BOT_PKG;
[Home] [Help]