1 PACKAGE BODY PQH_DE_CHILD_SEQ_PKG as
2 /* $Header: pqhdeseq.pkb 120.0 2005/05/29 02:03:15 appldev noship $ */
3 /*---------------------------------------------------------------------------------------------+
4 Procedure REGENERATE_SEQ_NUM
5 ----------------------------------------------------------------------------------------------+
6 Description:
7 This is intended to run as a concurrent program that generates Sequence Numbers
8 for children of Employees in German Public Sector. The processing is as follows
9 1. Find all employess in the bussiness group who want the sequence number of their children
10 to be automatically generated.
11 2. For each such employee find out all the children(contacts) in order of date of birth
12 3. Assigns sequence number 1 to first child, 2 to second child and so on. However a child
13 is eligable for a sequence number if and only if the child satisfies certain rules on
14 age,qualification,disability and military/civilian service.
15 4.Update the child contact to insert the new sequence number.
16
17 In Parameters:
18 1. Business Group ID
19 2. Effective Date
20
21 Post Success:
22 Updates the child contact record to insert the new sequence number.
23
24 Post Failure:
25
26 Developer Implementation Notes:
27 1. Cursor C_Emp_In_Business_Grp finds all Employees in the Business Group who want child
28 sequence number to be auto generated.
29 2. Cursor C_Emp_Children finds all the chldren of a employee in the Business Group in the order of Date of Birth.
30 3. Cursor C_Children_Disability finds Disability information of a Person(Child)
31 4. Cursor C_Children_Qualification finds Qualification details of a Person(Child)
32 5. Cursor C_Child_Military_Info finds Military Service/ Civil Service info of the Person(Child)
33 6. The procedure PER_CONTACT_RELATIONSHIPS_PKG.Update_Row updates the Child Contact record in
34 PER_CONTACT_RELATIONSHIPS to change the Sequence Number.
35
36 -------------------------------------------------------------------------------------------------*/
37
38 PROCEDURE REGENERATE_SEQ_NUM(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY NUMBER, pBusiness_grp_id IN NUMBER, pEffective_date IN VARCHAR2) is
39
40 lBusiness_group_id HR_ALL_ORGANIZATION_UNITS.BUSINESS_GROUP_ID%type;
41 cSeq number(2);
42 c_Age number;
43 --l_api_ovn number;
44 update_flag boolean;
45 l_session_date date;
46
47 --Given Business Group name find business group id
48
49 CURSOR C_B_Grp(pBusiness_grp_name varchar) is
50 select BUSINESS_GROUP_ID
51 from HR_ALL_ORGANIZATION_UNITS
52 where name=pBusiness_grp_name;
53
54
55
56
57 --Find all Employees in the Business Group with auto seq yes
58 CURSOR C_Emp_In_Business_Grp(lBusiness_group_id number) is
59 select pap.PERSON_ID,
60 pap.EFFECTIVE_START_DATE,
61 pap.EFFECTIVE_END_DATE
62 from PER_ALL_PEOPLE_F pap,
63 PER_PERSON_TYPES ppt,
64 PER_PEOPLE_EXTRA_INFO pei
65 where
66 pap.BUSINESS_GROUP_ID=lBusiness_group_id
67 and pap.person_type_id=ppt.person_type_id
68 and ppt.SYSTEM_PERSON_TYPE='EMP'
69 and pap.EFFECTIVE_START_DATE <= l_session_date
70 and pap.EFFECTIVE_END_DATE >= l_session_date
71 and pei.PERSON_ID=pap.PERSON_ID
72 and pei.PEI_INFORMATION_CATEGORY='DE_PQH_AUTO_SEQUENCE'
73 and nvl(pei.pei_information1,'Y')='Y';
74
75
76 --Given Person_id and business group find all children of the person
77 CURSOR C_Emp_Children(lBusiness_group_id number,
78 lPERSON_ID number,
79 lEFFECTIVE_START_DATE date,
80 lEFFECTIVE_END_DATE date) is
81 select pap.PERSON_ID CHILD_ID,
82 pap.EFFECTIVE_START_DATE,
83 pap.EFFECTIVE_END_DATE,
84 pap.DATE_OF_BIRTH,
85 pap.REGISTERED_DISABLED_FLAG,
86 pcr.Rowid,
87 pcr.Contact_Relationship_Id,
88 pcr.Business_Group_Id,
89 pcr.Person_Id,
90 pcr.Contact_Person_Id,
91 pcr.Contact_Type,
92 pcr.Comments,
93 pcr.Bondholder_Flag,
94 pcr.Third_Party_Pay_Flag,
95 pcr.Primary_Contact_Flag,
96 pcr.Cont_Attribute_Category,
97 pcr.Cont_Attribute1,
98 pcr.Cont_Attribute2,
99 pcr.Cont_Attribute3,
100 pcr.Cont_Attribute4,
101 pcr.Cont_Attribute5,
102 pcr.Cont_Attribute6,
103 pcr.Cont_Attribute7 ,
104 pcr.Cont_Attribute8 ,
105 pcr.Cont_Attribute9,
106 pcr.Cont_Attribute10,
107 pcr.Cont_Attribute11,
108 pcr.Cont_Attribute12,
109 pcr.Cont_Attribute13,
110 pcr.Cont_Attribute14,
111 pcr.Cont_Attribute15,
112 pcr.Cont_Attribute16,
113 pcr.Cont_Attribute17,
114 pcr.Cont_Attribute18,
115 pcr.Cont_Attribute19,
116 pcr.Cont_Attribute20,
117 pcr.Cont_Information_Category,
118 pcr.Cont_Information1,
119 pcr.Cont_Information2,
120 pcr.Cont_Information3,
121 pcr.Cont_Information4,
122 pcr.Cont_Information5,
123 pcr.Cont_Information6,
124 pcr.Cont_Information7,
125 pcr.Cont_Information8,
126 pcr.Cont_Information9,
127 pcr.Cont_Information10,
128 pcr.Cont_Information11,
129 pcr.Cont_Information12,
130 pcr.Cont_Information13,
131 pcr.Cont_Information14,
132 pcr.Cont_Information15,
133 pcr.Cont_Information16,
134 pcr.Cont_Information17,
135 pcr.Cont_Information18,
136 pcr.Cont_Information19,
137 pcr.Cont_Information20,
138 pcr.Date_Start,
139 pcr.Start_Life_Reason_Id,
140 pcr.Date_End,
141 pcr.End_Life_Reason_Id,
142 pcr.Rltd_Per_Rsds_W_Dsgntr_Flag,
143 pcr.Personal_Flag,
144 pcr.Sequence_Number,
145 pcr.Dependent_Flag,
146 pcr.Beneficiary_Flag,
147 pei.pei_information1,
148 pei.pei_information2,
149 pei.pei_information3
150
151 from PER_CONTACT_RELATIONSHIPS pcr,
152 PER_PEOPLE_F pap,
153 PER_PEOPLE_EXTRA_INFO pei
154 where
155 pcr.Contact_Relationship_Id in
156 (select
157 xpcr.Contact_Relationship_Id
158 from PER_CONTACT_RELATIONSHIPS xpcr,
159 PER_ALL_PEOPLE_F xpap
160 where xpcr.BUSINESS_GROUP_ID=lBusiness_group_id
161 and xpcr.PERSON_ID=xpap.PERSON_ID
162 and xpap.PERSON_ID=lPerson_id
163 and nvl(xpcr.DATE_START,l_session_date) <= l_session_date
164 and nvl(xpcr.DATE_END,l_session_date) >= l_session_date
165 and xpcr.CONTACT_TYPE IN ('A','O','OC','T')
166 and xpap.EFFECTIVE_START_DATE = lEffective_Start_Date
167 and xpap.EFFECTIVE_END_DATE = lEffective_End_Date
168 )
169 and pap.PERSON_ID = pcr.CONTACT_PERSON_ID
170 and pcr.PERSON_ID = pei.PERSON_ID (+)
171 and pei.PEI_INFORMATION_CATEGORY (+)='DE_PQH_CHILD_DETAILS'
172 and l_session_date between nvl(fnd_date.canonical_to_date(pei.pei_information4), l_session_date)
173 and nvl(fnd_date.canonical_to_date(pei.pei_information5), l_session_date)
174 order by nvl(pap.DATE_OF_BIRTH,l_session_date);
175
176 --Select disability status of a person
177
178 CURSOR C_Children_Disability(lBusiness_group_id number,lPERSON_ID number,Date_of_Birth date) is
179 select pdf.DISABILITY_ID,
180 pdf.CATEGORY,
181 pdf.STATUS
182
183 from PER_DISABILITIES_V pdf
184 where pdf.PERSON_ID=lPERSON_ID
185 and pdf.BUSINESS_GROUP_ID=lBusiness_group_id
186 and pdf.EFFECTIVE_START_DATE <= l_session_date
187 and pdf.EFFECTIVE_END_DATE >= l_session_date
188 and STATUS in ('A','APP')
189 and (months_between(pdf.REGISTRATION_DATE,Date_of_Birth)/12) <=27;
190
191
192 CURSOR C_Child_Military_Info(lBusiness_group_id number,
193 lCHILD_ID number,
194 lEFFECTIVE_START_DATE date,
195 lEFFECTIVE_END_DATE date) is
196 select pap.PERSON_ID,
197 pei.person_extra_info_id,
198 pei.pei_information_category,
199 pei.pei_information1, --Start Date
200 pei.pei_information2, --End Date
201 pei.pei_information3, --Type of Service
202 pei.pei_information4 --Certificate Presented
203 from PER_ALL_PEOPLE_F pap,
204 PER_PEOPLE_EXTRA_INFO pei
205 where
206 pap.BUSINESS_GROUP_ID=lBusiness_group_id
207 and pap.PERSON_ID= lCHILD_ID
208 and pei.PERSON_ID=pap.PERSON_ID
209 and pei.PEI_INFORMATION_CATEGORY='DE_MILITARY_SERVICE' --German Public Sector Child Military Deatails
210 and pap.EFFECTIVE_START_DATE = lEFFECTIVE_START_DATE
211 and pap.EFFECTIVE_END_DATE = lEFFECTIVE_END_DATE;
212
213 CURSOR C_Children_Qualification(lBusiness_group_id number,lPERSON_ID number, Date_of_Birth date) is
214 select
215 pq.PERSON_ID,
216 pq.QUALIFICATION_ID,
217 pq.QUA_INFORMATION1,
218 pq.START_DATE,
219 pq.END_DATE
220 from per_qualifications pq
221 where pq.BUSINESS_GROUP_ID=lBusiness_group_id
222 and pq.PERSON_ID=lPERSON_ID
223 and pq.START_DATE is not null
224 and pq.END_DATE is not null
225 and (months_between(pq.START_DATE ,Date_of_Birth)/12) <27
226 and pq.QUA_INFORMATION1='Y' -- Certificates Presented
227 and (
228 (
229 ((months_between(pq.END_DATE ,Date_of_Birth)/12) >21)
230 and exists
231 (select xpq.QUALIFICATION_ID
232 from per_qualifications xpq
233 where
234 xpq.BUSINESS_GROUP_ID=lBusiness_group_id
235 and xpq.PERSON_ID=pq.PERSON_ID
236 and months_between(pq.END_DATE,xpq.START_DATE)<=4
237 )
238 )
239 OR (pq.END_DATE>=l_session_date and (months_between(pq.START_DATE ,Date_of_Birth)/12) <21)
240 );
241
242
243
244 c_Child_Mil C_Child_Military_Info%rowtype;
245 c_Child_Qua C_Children_Qualification%rowtype;
246 c_Dis_Cur C_Children_Disability%rowtype;
247
248
249 Begin
250 savepoint PQH_DE_SEQ_NUM;
251 --select effective_date into l_session_date from fnd_sessions where session_id=userenv('sessionid');
252 --OPEN C_B_Grp(pBusiness_grp_name);
253 --fetch C_B_grp into lBusiness_group_id;
254 lBusiness_group_id :=pBusiness_grp_id;
255
256 l_session_date := fnd_date.canonical_to_date(pEffective_date);
257
258 --l_session_date := to_date(pEffective_date, 'RRRR/MM/DD HH24:MI:SS');
259 --l_session_date := to_date(to_char(trunc(l_session_date), 'DD/MM/RRRR'),'DD/MM/RRRR');
260
261 FOR C1 in C_Emp_In_Business_Grp(lBusiness_group_id)
262 LOOP
263 cSeq:=0;
264 FOR C2 in C_Emp_Children(lBusiness_group_id, C1.PERSON_id, C1.EFFECTIVE_START_DATE, C1.EFFECTIVE_END_DATE)
265 LOOP
266 update_flag := false;
267 if (C2.DATE_OF_BIRTH is not null
268 and C2.CONT_INFORMATION1='N' --Not Entitled for some body else
269 and C2.CONT_INFORMATION4='Y' --Elligible for local cost of living allowance
270 )
271 then
272 OPEN C_Children_Disability(lBusiness_group_id,C2.CHILD_ID,C2.DATE_OF_BIRTH);
273 FETCH C_Children_Disability into c_Dis_Cur;
274
275 OPEN C_Child_Military_Info(lBusiness_group_id, C2.CHILD_ID, C2.EFFECTIVE_START_DATE, C2.EFFECTIVE_END_DATE);
276 FETCH C_Child_Military_Info into c_Child_Mil;
277
278 OPEN C_Children_Qualification(lBusiness_group_id, C2.CHILD_ID,C2.DATE_OF_BIRTH);
279 FETCH C_Children_Qualification into c_Child_Qua;
280 c_Age := floor(months_between(l_session_date,last_day(C2.DATE_OF_BIRTH))/12);
281 if c_Age >= 18 -- if age is greater than 18 years then do the following
282 then
283
284 if (C2.PEI_INFORMATION1 ='N') -- Child is Unemployed
285 then
286 if
287 ( --Child age less than 21
288 ( c_Age < 21)
289 or -- or Child is Unemployed and has done militaty service and less than 21 +period of service
290 (C_Child_Military_Info%FOUND and c_Child_Mil.PEI_INFORMATION3 ='M' and c_Child_Mil.PEI_INFORMATION4 ='Y'
291 and c_Age < (21 + months_between(fnd_date.canonical_to_date(c_Child_Mil.PEI_INFORMATION2),fnd_date.canonical_to_date(c_Child_Mil.PEI_INFORMATION1))/12)
292 )
293 or -- or Child is Unemployed and has done civil service and less than 21 +period of sevice+1 month
294 (C_Child_Military_Info%FOUND and c_Child_Mil.PEI_INFORMATION3 ='C' and c_Child_Mil.PEI_INFORMATION4 ='Y'
295 and c_Age < (21 + (1+months_between(fnd_date.canonical_to_date(c_Child_Mil.PEI_INFORMATION2),fnd_date.canonical_to_date(c_Child_Mil.PEI_INFORMATION1)))/12)
296 )
297 or
298 ( C_Children_Qualification%FOUND )
299 or
300 (C_Children_Disability%FOUND)
301 )
302 then
303 cSeq:=cSeq+1;
304 update_flag := true;
305 end if;
306 elsif (nvl(to_number(C2.PEI_INFORMATION3),0) < 14040 )
307 then
308 if
309 ( --Child age less than 21
310 ( c_Age < 21)
311 or -- or Child is Unemployed and has done militaty service and less than 22 +period of service
312 (C_Child_Military_Info%FOUND and c_Child_Mil.PEI_INFORMATION3 ='M' and c_Child_Mil.PEI_INFORMATION4 ='Y'
313 and c_Age < (21 + months_between(fnd_date.canonical_to_date(c_Child_Mil.PEI_INFORMATION2),fnd_date.canonical_to_date(c_Child_Mil.PEI_INFORMATION1))/12)
314 )
315 or -- or Child is Unemployed and has done civil service and less than 22 +period of sevice+1 month
316 (C_Child_Military_Info%FOUND and c_Child_Mil.PEI_INFORMATION3 ='C' and c_Child_Mil.PEI_INFORMATION4 ='Y'
317 and c_Age < (21 + (1+months_between(fnd_date.canonical_to_date(c_Child_Mil.PEI_INFORMATION2),fnd_date.canonical_to_date(c_Child_Mil.PEI_INFORMATION1)))/12)
318 )
319 or
320 (C_Children_Qualification%FOUND )
321 or
322 (C_Children_Disability%FOUND)
323 )
324 then
325 cSeq:=cSeq+1;
326 update_flag := true;
327 end if; -- salary
328 end if; -- unemployment
329 else
330 cSeq:=cSeq+1;
331 update_flag := true;
332 end if; -- c_Age > 18
333 /*if (c_Age < 18)
334 then
335 cSeq:=cSeq+1;
336 update_flag := true;
337 end if;*/
338 end if; -- DATE_OF_BIRTH
339 if (update_flag=true)
340 then
341 PER_CONTACT_RELATIONSHIPS_PKG.Update_Row(
342 X_Rowid => C2.Rowid,
343 X_Contact_Relationship_Id => c2.Contact_Relationship_Id,
344 X_Business_Group_Id => C2.Business_Group_Id,
345 X_Person_Id => C2.Person_Id,
346 X_Contact_Person_Id => C2.Contact_Person_Id,
347 X_Contact_Type => C2.Contact_Type,
348 X_Comments => C2.Comments,
349 X_Bondholder_Flag => C2.Bondholder_Flag,
350 X_Third_Party_Pay_Flag => C2.Third_Party_Pay_Flag,
351 X_Primary_Contact_Flag => C2.Primary_Contact_Flag,
352 X_Cont_Attribute_Category => C2.Cont_Attribute_Category,
353 X_Cont_Attribute1 => C2.Cont_Attribute1,
354 X_Cont_Attribute2 => C2.Cont_Attribute2,
355 X_Cont_Attribute3 => C2.Cont_Attribute3,
356 X_Cont_Attribute4 => C2.Cont_Attribute4,
357 X_Cont_Attribute5 => C2.Cont_Attribute5,
358 X_Cont_Attribute6 => C2.Cont_Attribute6,
359 X_Cont_Attribute7 => C2.Cont_Attribute7,
360 X_Cont_Attribute8 => C2.Cont_Attribute8,
361 X_Cont_Attribute9 => C2.Cont_Attribute9,
362 X_Cont_Attribute10 => C2.Cont_Attribute10,
363 X_Cont_Attribute11 => C2.Cont_Attribute11,
364 X_Cont_Attribute12 => C2.Cont_Attribute12,
365 X_Cont_Attribute13 => C2.Cont_Attribute13,
366 X_Cont_Attribute14 => C2.Cont_Attribute14,
367 X_Cont_Attribute15 => C2.Cont_Attribute15,
368 X_Cont_Attribute16 => C2.Cont_Attribute16,
369 X_Cont_Attribute17 => C2.Cont_Attribute17,
370 X_Cont_Attribute18 => C2.Cont_Attribute18,
371 X_Cont_Attribute19 => C2.Cont_Attribute19,
372 X_Cont_Attribute20 => C2.Cont_Attribute20,
373 X_Cont_Information_Category => C2.Cont_Information_Category,
374 X_Cont_Information1 => C2.Cont_Information1,
375 X_Cont_Information2 => C2.Cont_Information2,
376 X_Cont_Information3 => C2.Cont_Information3,
377 X_Cont_Information4 => C2.Cont_Information4,
378 X_Cont_Information5 => C2.Cont_Information5,
379 X_Cont_Information6 => cSeq,
380 X_Cont_Information7 => C2.Cont_Information7,
381 X_Cont_Information8 => C2.Cont_Information8,
382 X_Cont_Information9 => C2.Cont_Information9,
383 X_Cont_Information10 => C2.Cont_Information10,
384 X_Cont_Information11 => C2.Cont_Information11,
385 X_Cont_Information12 => C2.Cont_Information12,
386 X_Cont_Information13 => C2.Cont_Information13,
387 X_Cont_Information14 => C2.Cont_Information14,
388 X_Cont_Information15 => C2.Cont_Information15,
389 X_Cont_Information16 => C2.Cont_Information16,
390 X_Cont_Information17 => C2.Cont_Information17,
391 X_Cont_Information18 => C2.Cont_Information18,
392 X_Cont_Information19 => C2.Cont_Information19,
393 X_Cont_Information20 => C2.Cont_Information20,
394 X_Session_Date => l_session_date,
395 X_Date_Start => C2.Date_Start,
396 X_Start_Life_Reason_Id => C2.Start_Life_Reason_Id,
397 X_Date_End => C2.Date_End,
398 X_End_Life_Reason_Id => C2.End_Life_Reason_Id,
399 X_Rltd_Per_Rsds_W_Dsgntr_Flag => C2.Rltd_Per_Rsds_W_Dsgntr_Flag,
400 X_Personal_Flag => C2.Personal_Flag,
401 X_Sequence_Number => C2.Sequence_Number,
402 X_Dependent_Flag => C2.Dependent_Flag,
403 X_Beneficiary_Flag => C2.Beneficiary_Flag
404 );
405 ELSE
406 PER_CONTACT_RELATIONSHIPS_PKG.Update_Row(
407 X_Rowid => C2.Rowid,
408 X_Contact_Relationship_Id => c2.Contact_Relationship_Id,
409 X_Business_Group_Id => C2.Business_Group_Id,
410 X_Person_Id => C2.Person_Id,
411 X_Contact_Person_Id => C2.Contact_Person_Id,
412 X_Contact_Type => C2.Contact_Type,
413 X_Comments => C2.Comments,
414 X_Bondholder_Flag => C2.Bondholder_Flag,
415 X_Third_Party_Pay_Flag => C2.Third_Party_Pay_Flag,
416 X_Primary_Contact_Flag => C2.Primary_Contact_Flag,
417 X_Cont_Attribute_Category => C2.Cont_Attribute_Category,
418 X_Cont_Attribute1 => C2.Cont_Attribute1,
419 X_Cont_Attribute2 => C2.Cont_Attribute2,
420 X_Cont_Attribute3 => C2.Cont_Attribute3,
421 X_Cont_Attribute4 => C2.Cont_Attribute4,
422 X_Cont_Attribute5 => C2.Cont_Attribute5,
423 X_Cont_Attribute6 => C2.Cont_Attribute6,
424 X_Cont_Attribute7 => C2.Cont_Attribute7,
425 X_Cont_Attribute8 => C2.Cont_Attribute8,
426 X_Cont_Attribute9 => C2.Cont_Attribute9,
427 X_Cont_Attribute10 => C2.Cont_Attribute10,
428 X_Cont_Attribute11 => C2.Cont_Attribute11,
429 X_Cont_Attribute12 => C2.Cont_Attribute12,
430 X_Cont_Attribute13 => C2.Cont_Attribute13,
431 X_Cont_Attribute14 => C2.Cont_Attribute14,
432 X_Cont_Attribute15 => C2.Cont_Attribute15,
433 X_Cont_Attribute16 => C2.Cont_Attribute16,
434 X_Cont_Attribute17 => C2.Cont_Attribute17,
435 X_Cont_Attribute18 => C2.Cont_Attribute18,
436 X_Cont_Attribute19 => C2.Cont_Attribute19,
437 X_Cont_Attribute20 => C2.Cont_Attribute20,
438 X_Cont_Information_Category => C2.Cont_Information_Category,
439 X_Cont_Information1 => C2.Cont_Information1,
440 X_Cont_Information2 => C2.Cont_Information2,
441 X_Cont_Information3 => C2.Cont_Information3,
442 X_Cont_Information4 => C2.Cont_Information4,
443 X_Cont_Information5 => C2.Cont_Information5,
444 X_Cont_Information6 => NULL,
445 X_Cont_Information7 => C2.Cont_Information7,
446 X_Cont_Information8 => C2.Cont_Information8,
447 X_Cont_Information9 => C2.Cont_Information9,
448 X_Cont_Information10 => C2.Cont_Information10,
449 X_Cont_Information11 => C2.Cont_Information11,
450 X_Cont_Information12 => C2.Cont_Information12,
451 X_Cont_Information13 => C2.Cont_Information13,
452 X_Cont_Information14 => C2.Cont_Information14,
453 X_Cont_Information15 => C2.Cont_Information15,
454 X_Cont_Information16 => C2.Cont_Information16,
455 X_Cont_Information17 => C2.Cont_Information17,
456 X_Cont_Information18 => C2.Cont_Information18,
457 X_Cont_Information19 => C2.Cont_Information19,
458 X_Cont_Information20 => C2.Cont_Information20,
459 X_Session_Date => l_session_date,
460 X_Date_Start => C2.Date_Start,
461 X_Start_Life_Reason_Id => C2.Start_Life_Reason_Id,
462 X_Date_End => C2.Date_End,
463 X_End_Life_Reason_Id => C2.End_Life_Reason_Id,
464 X_Rltd_Per_Rsds_W_Dsgntr_Flag => C2.Rltd_Per_Rsds_W_Dsgntr_Flag,
465 X_Personal_Flag => C2.Personal_Flag,
466 X_Sequence_Number => C2.Sequence_Number,
467 X_Dependent_Flag => C2.Dependent_Flag,
468 X_Beneficiary_Flag => C2.Beneficiary_Flag
469 );
470
471 end if; -- update_flag
472
473 --Added condition to check if cursor is open to fix bug 2361730
474 IF C_Children_Disability%ISOPEN THEN
475 CLOSE C_Children_Disability;
476 END IF;
477
478 IF C_Child_Military_Info%ISOPEN THEN
479 CLOSE C_Child_Military_Info;
480 END IF;
481
482 IF C_Children_Qualification%ISOPEN THEN
483 CLOSE C_Children_Qualification;
484 END IF;
485
486
487 END LOOP; -- LOOP C2
488 END LOOP; -- LOOP C1
489 commit;
490 --CLOSE C_B_Grp;
491 EXCEPTION
492 --
493 WHEN Others THEN
494 rollback to PQH_DE_SEQ_NUM;
495 raise_application_error(-20001, sqlerrm);
496 --
497
498 End REGENERATE_SEQ_NUM;
499 /*---------------------------------------------------------------------------------------------+
500 Function DEFAULT_SEQ_NUM
501 ----------------------------------------------------------------------------------------------+
502 Description:
503 This is intended to return a default Sequence Number whenever a new Child contact is
504 being added to an Employee in German Public Sector.The processing is as follows:
505 1. Checks if the Parent is Employee of the Business Group.
506 2. If the Parent is Employee go to next step else return -1.
507 3. Find maximum of Sequence Numbers given to the Children of the Employee.
508 4. Return Maximum Sequence Number +1.
509
510 In Parameters:
511 1. Parent_id
512 2. bg_id
513 3. session_date
514 Post Success:
515 Returns -1 if Parent is not an Employee or returns some non negative number.
516
517 Post Failure:
518
519 Developer Implementation Notes:
520 1. Cursor finds all Employees in the Business Group who want child sequence number to be auto generated.
521 2. Cursor finds all the chldren of a employee in the Business Group in the order of Date of Birth.
522 3. Cursor finds Disability information of a Person(Child)
523 4. Cursor finds Qualification details of a Person(Child)
524 5. The procedure PER_CONTACT_RELATIONSHIPS_PKG.Update_Row updates the Child Contact record in
525 PER_CONTACT_RELATIONSHIPS to change the Sequence Number.
526
527 -------------------------------------------------------------------------------------------------*/
528 FUNCTION DEFAULT_SEQ_NUM (parent_id IN NUMBER, bg_id IN NUMBER, session_date IN date) RETURN NUMBER IS
529
530 parent_is_emp NUMBER(1) :=0;
531 seq_num NUMBER(2) :=0;
532
533 CURSOR IS_Emp_OF_Business_Grp(lPERSON_ID number ,lBusiness_group_id number) is
534 select pap.PERSON_ID,
535 pap.EFFECTIVE_START_DATE,
536 pap.EFFECTIVE_END_DATE
537
538 from PER_ALL_PEOPLE_F pap,
539 PER_PERSON_TYPES ppt
540 where
541 pap.PERSON_ID=lPERSON_ID
542 and pap.BUSINESS_GROUP_ID=lBusiness_group_id
543 and pap.person_type_id=ppt.person_type_id
544 and ppt.SYSTEM_PERSON_TYPE='EMP'
545 and pap.EFFECTIVE_START_DATE <= session_date
546 and pap.EFFECTIVE_END_DATE >= session_date ;
547
548 c_Emp_Cur IS_Emp_OF_Business_Grp%rowtype;
549
550 BEGIN
551 OPEN IS_Emp_OF_Business_Grp(parent_id,bg_id);
552 FETCH IS_Emp_OF_Business_Grp into c_Emp_Cur;
553
554 if IS_Emp_OF_Business_Grp%FOUND then
555 select max(nvl(pcr.cont_information6,0)) into seq_num
556 from PER_CONTACT_RELATIONSHIPS pcr
557 where
558 pcr.BUSINESS_GROUP_ID=bg_id
559 and pcr.PERSON_ID=parent_id
560 and nvl(pcr.DATE_START,session_date) <=session_date
561 and nvl(pcr.DATE_END,session_date) >= session_date
562 and pcr.CONTACT_TYPE IN ('A','O','OC','T');
563
564 seq_num :=nvl(seq_num,0)+1;
565 RETURN seq_num;
566 end if;
567 CLOSE IS_Emp_OF_Business_Grp;
568 RETURN -1;
569 END DEFAULT_SEQ_NUM;
570 END PQH_DE_CHILD_SEQ_PKG;
571
[Home] [Help]