[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
4.Update the child contact to insert the new sequence number.
In Parameters:
1. Business Group ID
2. Effective Date
Post Success:
Updates the child contact record to insert the new sequence number.
Post Failure:
Developer Implementation Notes:
1. Cursor C_Emp_In_Business_Grp finds all Employees in the Business Group who want child
sequence number to be auto generated.
2. Cursor C_Emp_Children finds all the chldren of a employee in the Business Group in the order of Date of Birth.
3. Cursor C_Children_Disability finds Disability information of a Person(Child)
4. Cursor C_Children_Qualification finds Qualification details of a Person(Child)
5. Cursor C_Child_Military_Info finds Military Service/ Civil Service info of the Person(Child)
6. The procedure PER_CONTACT_RELATIONSHIPS_PKG.Update_Row updates the Child Contact record in
PER_CONTACT_RELATIONSHIPS to change the Sequence Number.
-------------------------------------------------------------------------------------------------*/
PROCEDURE REGENERATE_SEQ_NUM(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY NUMBER, pBusiness_grp_id IN NUMBER, pEffective_date IN VARCHAR2) is
lBusiness_group_id HR_ALL_ORGANIZATION_UNITS.BUSINESS_GROUP_ID%type;
update_flag boolean;
select BUSINESS_GROUP_ID
from HR_ALL_ORGANIZATION_UNITS
where name=pBusiness_grp_name;
select pap.PERSON_ID,
pap.EFFECTIVE_START_DATE,
pap.EFFECTIVE_END_DATE
from PER_ALL_PEOPLE_F pap,
PER_PERSON_TYPES ppt,
PER_PEOPLE_EXTRA_INFO pei
where
pap.BUSINESS_GROUP_ID=lBusiness_group_id
and pap.person_type_id=ppt.person_type_id
and ppt.SYSTEM_PERSON_TYPE='EMP'
and pap.EFFECTIVE_START_DATE <= l_session_date
and pap.EFFECTIVE_END_DATE >= l_session_date
and pei.PERSON_ID=pap.PERSON_ID
and pei.PEI_INFORMATION_CATEGORY='DE_PQH_AUTO_SEQUENCE'
and nvl(pei.pei_information1,'Y')='Y';
select pap.PERSON_ID CHILD_ID,
pap.EFFECTIVE_START_DATE,
pap.EFFECTIVE_END_DATE,
pap.DATE_OF_BIRTH,
pap.REGISTERED_DISABLED_FLAG,
pcr.Rowid,
pcr.Contact_Relationship_Id,
pcr.Business_Group_Id,
pcr.Person_Id,
pcr.Contact_Person_Id,
pcr.Contact_Type,
pcr.Comments,
pcr.Bondholder_Flag,
pcr.Third_Party_Pay_Flag,
pcr.Primary_Contact_Flag,
pcr.Cont_Attribute_Category,
pcr.Cont_Attribute1,
pcr.Cont_Attribute2,
pcr.Cont_Attribute3,
pcr.Cont_Attribute4,
pcr.Cont_Attribute5,
pcr.Cont_Attribute6,
pcr.Cont_Attribute7 ,
pcr.Cont_Attribute8 ,
pcr.Cont_Attribute9,
pcr.Cont_Attribute10,
pcr.Cont_Attribute11,
pcr.Cont_Attribute12,
pcr.Cont_Attribute13,
pcr.Cont_Attribute14,
pcr.Cont_Attribute15,
pcr.Cont_Attribute16,
pcr.Cont_Attribute17,
pcr.Cont_Attribute18,
pcr.Cont_Attribute19,
pcr.Cont_Attribute20,
pcr.Cont_Information_Category,
pcr.Cont_Information1,
pcr.Cont_Information2,
pcr.Cont_Information3,
pcr.Cont_Information4,
pcr.Cont_Information5,
pcr.Cont_Information6,
pcr.Cont_Information7,
pcr.Cont_Information8,
pcr.Cont_Information9,
pcr.Cont_Information10,
pcr.Cont_Information11,
pcr.Cont_Information12,
pcr.Cont_Information13,
pcr.Cont_Information14,
pcr.Cont_Information15,
pcr.Cont_Information16,
pcr.Cont_Information17,
pcr.Cont_Information18,
pcr.Cont_Information19,
pcr.Cont_Information20,
pcr.Date_Start,
pcr.Start_Life_Reason_Id,
pcr.Date_End,
pcr.End_Life_Reason_Id,
pcr.Rltd_Per_Rsds_W_Dsgntr_Flag,
pcr.Personal_Flag,
pcr.Sequence_Number,
pcr.Dependent_Flag,
pcr.Beneficiary_Flag,
pei.pei_information1,
pei.pei_information2,
pei.pei_information3
from PER_CONTACT_RELATIONSHIPS pcr,
PER_PEOPLE_F pap,
PER_PEOPLE_EXTRA_INFO pei
where
pcr.Contact_Relationship_Id in
(select
xpcr.Contact_Relationship_Id
from PER_CONTACT_RELATIONSHIPS xpcr,
PER_ALL_PEOPLE_F xpap
where xpcr.BUSINESS_GROUP_ID=lBusiness_group_id
and xpcr.PERSON_ID=xpap.PERSON_ID
and xpap.PERSON_ID=lPerson_id
and nvl(xpcr.DATE_START,l_session_date) <= l_session_date
and nvl(xpcr.DATE_END,l_session_date) >= l_session_date
and xpcr.CONTACT_TYPE IN ('A','O','OC','T')
and xpap.EFFECTIVE_START_DATE = lEffective_Start_Date
and xpap.EFFECTIVE_END_DATE = lEffective_End_Date
)
and pap.PERSON_ID = pcr.CONTACT_PERSON_ID
and pcr.PERSON_ID = pei.PERSON_ID (+)
and pei.PEI_INFORMATION_CATEGORY (+)='DE_PQH_CHILD_DETAILS'
and l_session_date between nvl(fnd_date.canonical_to_date(pei.pei_information4), l_session_date)
and nvl(fnd_date.canonical_to_date(pei.pei_information5), l_session_date)
order by nvl(pap.DATE_OF_BIRTH,l_session_date);
--Select disability status of a person
CURSOR C_Children_Disability(lBusiness_group_id number,lPERSON_ID number,Date_of_Birth date) is
select pdf.DISABILITY_ID,
pdf.CATEGORY,
pdf.STATUS
from PER_DISABILITIES_V pdf
where pdf.PERSON_ID=lPERSON_ID
and pdf.BUSINESS_GROUP_ID=lBusiness_group_id
and pdf.EFFECTIVE_START_DATE <= l_session_date
and pdf.EFFECTIVE_END_DATE >= l_session_date
and STATUS in ('A','APP')
and (months_between(pdf.REGISTRATION_DATE,Date_of_Birth)/12) <=27;
select pap.PERSON_ID,
pei.person_extra_info_id,
pei.pei_information_category,
pei.pei_information1, --Start Date
pei.pei_information2, --End Date
pei.pei_information3, --Type of Service
pei.pei_information4 --Certificate Presented
from PER_ALL_PEOPLE_F pap,
PER_PEOPLE_EXTRA_INFO pei
where
pap.BUSINESS_GROUP_ID=lBusiness_group_id
and pap.PERSON_ID= lCHILD_ID
and pei.PERSON_ID=pap.PERSON_ID
and pei.PEI_INFORMATION_CATEGORY='DE_MILITARY_SERVICE' --German Public Sector Child Military Deatails
and pap.EFFECTIVE_START_DATE = lEFFECTIVE_START_DATE
and pap.EFFECTIVE_END_DATE = lEFFECTIVE_END_DATE;
select
pq.PERSON_ID,
pq.QUALIFICATION_ID,
pq.QUA_INFORMATION1,
pq.START_DATE,
pq.END_DATE
from per_qualifications pq
where pq.BUSINESS_GROUP_ID=lBusiness_group_id
and pq.PERSON_ID=lPERSON_ID
and pq.START_DATE is not null
and pq.END_DATE is not null
and (months_between(pq.START_DATE ,Date_of_Birth)/12) <27
and pq.QUA_INFORMATION1='Y' -- Certificates Presented
and (
(
((months_between(pq.END_DATE ,Date_of_Birth)/12) >21)
and exists
(select xpq.QUALIFICATION_ID
from per_qualifications xpq
where
xpq.BUSINESS_GROUP_ID=lBusiness_group_id
and xpq.PERSON_ID=pq.PERSON_ID
and months_between(pq.END_DATE,xpq.START_DATE)<=4
)
)
OR (pq.END_DATE>=l_session_date and (months_between(pq.START_DATE ,Date_of_Birth)/12) <21)
);
update_flag := false;
update_flag := true;
update_flag := true;
update_flag := true;
update_flag := true;
if (update_flag=true)
then
PER_CONTACT_RELATIONSHIPS_PKG.Update_Row(
X_Rowid => C2.Rowid,
X_Contact_Relationship_Id => c2.Contact_Relationship_Id,
X_Business_Group_Id => C2.Business_Group_Id,
X_Person_Id => C2.Person_Id,
X_Contact_Person_Id => C2.Contact_Person_Id,
X_Contact_Type => C2.Contact_Type,
X_Comments => C2.Comments,
X_Bondholder_Flag => C2.Bondholder_Flag,
X_Third_Party_Pay_Flag => C2.Third_Party_Pay_Flag,
X_Primary_Contact_Flag => C2.Primary_Contact_Flag,
X_Cont_Attribute_Category => C2.Cont_Attribute_Category,
X_Cont_Attribute1 => C2.Cont_Attribute1,
X_Cont_Attribute2 => C2.Cont_Attribute2,
X_Cont_Attribute3 => C2.Cont_Attribute3,
X_Cont_Attribute4 => C2.Cont_Attribute4,
X_Cont_Attribute5 => C2.Cont_Attribute5,
X_Cont_Attribute6 => C2.Cont_Attribute6,
X_Cont_Attribute7 => C2.Cont_Attribute7,
X_Cont_Attribute8 => C2.Cont_Attribute8,
X_Cont_Attribute9 => C2.Cont_Attribute9,
X_Cont_Attribute10 => C2.Cont_Attribute10,
X_Cont_Attribute11 => C2.Cont_Attribute11,
X_Cont_Attribute12 => C2.Cont_Attribute12,
X_Cont_Attribute13 => C2.Cont_Attribute13,
X_Cont_Attribute14 => C2.Cont_Attribute14,
X_Cont_Attribute15 => C2.Cont_Attribute15,
X_Cont_Attribute16 => C2.Cont_Attribute16,
X_Cont_Attribute17 => C2.Cont_Attribute17,
X_Cont_Attribute18 => C2.Cont_Attribute18,
X_Cont_Attribute19 => C2.Cont_Attribute19,
X_Cont_Attribute20 => C2.Cont_Attribute20,
X_Cont_Information_Category => C2.Cont_Information_Category,
X_Cont_Information1 => C2.Cont_Information1,
X_Cont_Information2 => C2.Cont_Information2,
X_Cont_Information3 => C2.Cont_Information3,
X_Cont_Information4 => C2.Cont_Information4,
X_Cont_Information5 => C2.Cont_Information5,
X_Cont_Information6 => cSeq,
X_Cont_Information7 => C2.Cont_Information7,
X_Cont_Information8 => C2.Cont_Information8,
X_Cont_Information9 => C2.Cont_Information9,
X_Cont_Information10 => C2.Cont_Information10,
X_Cont_Information11 => C2.Cont_Information11,
X_Cont_Information12 => C2.Cont_Information12,
X_Cont_Information13 => C2.Cont_Information13,
X_Cont_Information14 => C2.Cont_Information14,
X_Cont_Information15 => C2.Cont_Information15,
X_Cont_Information16 => C2.Cont_Information16,
X_Cont_Information17 => C2.Cont_Information17,
X_Cont_Information18 => C2.Cont_Information18,
X_Cont_Information19 => C2.Cont_Information19,
X_Cont_Information20 => C2.Cont_Information20,
X_Session_Date => l_session_date,
X_Date_Start => C2.Date_Start,
X_Start_Life_Reason_Id => C2.Start_Life_Reason_Id,
X_Date_End => C2.Date_End,
X_End_Life_Reason_Id => C2.End_Life_Reason_Id,
X_Rltd_Per_Rsds_W_Dsgntr_Flag => C2.Rltd_Per_Rsds_W_Dsgntr_Flag,
X_Personal_Flag => C2.Personal_Flag,
X_Sequence_Number => C2.Sequence_Number,
X_Dependent_Flag => C2.Dependent_Flag,
X_Beneficiary_Flag => C2.Beneficiary_Flag
);
PER_CONTACT_RELATIONSHIPS_PKG.Update_Row(
X_Rowid => C2.Rowid,
X_Contact_Relationship_Id => c2.Contact_Relationship_Id,
X_Business_Group_Id => C2.Business_Group_Id,
X_Person_Id => C2.Person_Id,
X_Contact_Person_Id => C2.Contact_Person_Id,
X_Contact_Type => C2.Contact_Type,
X_Comments => C2.Comments,
X_Bondholder_Flag => C2.Bondholder_Flag,
X_Third_Party_Pay_Flag => C2.Third_Party_Pay_Flag,
X_Primary_Contact_Flag => C2.Primary_Contact_Flag,
X_Cont_Attribute_Category => C2.Cont_Attribute_Category,
X_Cont_Attribute1 => C2.Cont_Attribute1,
X_Cont_Attribute2 => C2.Cont_Attribute2,
X_Cont_Attribute3 => C2.Cont_Attribute3,
X_Cont_Attribute4 => C2.Cont_Attribute4,
X_Cont_Attribute5 => C2.Cont_Attribute5,
X_Cont_Attribute6 => C2.Cont_Attribute6,
X_Cont_Attribute7 => C2.Cont_Attribute7,
X_Cont_Attribute8 => C2.Cont_Attribute8,
X_Cont_Attribute9 => C2.Cont_Attribute9,
X_Cont_Attribute10 => C2.Cont_Attribute10,
X_Cont_Attribute11 => C2.Cont_Attribute11,
X_Cont_Attribute12 => C2.Cont_Attribute12,
X_Cont_Attribute13 => C2.Cont_Attribute13,
X_Cont_Attribute14 => C2.Cont_Attribute14,
X_Cont_Attribute15 => C2.Cont_Attribute15,
X_Cont_Attribute16 => C2.Cont_Attribute16,
X_Cont_Attribute17 => C2.Cont_Attribute17,
X_Cont_Attribute18 => C2.Cont_Attribute18,
X_Cont_Attribute19 => C2.Cont_Attribute19,
X_Cont_Attribute20 => C2.Cont_Attribute20,
X_Cont_Information_Category => C2.Cont_Information_Category,
X_Cont_Information1 => C2.Cont_Information1,
X_Cont_Information2 => C2.Cont_Information2,
X_Cont_Information3 => C2.Cont_Information3,
X_Cont_Information4 => C2.Cont_Information4,
X_Cont_Information5 => C2.Cont_Information5,
X_Cont_Information6 => NULL,
X_Cont_Information7 => C2.Cont_Information7,
X_Cont_Information8 => C2.Cont_Information8,
X_Cont_Information9 => C2.Cont_Information9,
X_Cont_Information10 => C2.Cont_Information10,
X_Cont_Information11 => C2.Cont_Information11,
X_Cont_Information12 => C2.Cont_Information12,
X_Cont_Information13 => C2.Cont_Information13,
X_Cont_Information14 => C2.Cont_Information14,
X_Cont_Information15 => C2.Cont_Information15,
X_Cont_Information16 => C2.Cont_Information16,
X_Cont_Information17 => C2.Cont_Information17,
X_Cont_Information18 => C2.Cont_Information18,
X_Cont_Information19 => C2.Cont_Information19,
X_Cont_Information20 => C2.Cont_Information20,
X_Session_Date => l_session_date,
X_Date_Start => C2.Date_Start,
X_Start_Life_Reason_Id => C2.Start_Life_Reason_Id,
X_Date_End => C2.Date_End,
X_End_Life_Reason_Id => C2.End_Life_Reason_Id,
X_Rltd_Per_Rsds_W_Dsgntr_Flag => C2.Rltd_Per_Rsds_W_Dsgntr_Flag,
X_Personal_Flag => C2.Personal_Flag,
X_Sequence_Number => C2.Sequence_Number,
X_Dependent_Flag => C2.Dependent_Flag,
X_Beneficiary_Flag => C2.Beneficiary_Flag
);
end if; -- update_flag
5. The procedure PER_CONTACT_RELATIONSHIPS_PKG.Update_Row updates the Child Contact record in
PER_CONTACT_RELATIONSHIPS to change the Sequence Number.
-------------------------------------------------------------------------------------------------*/
FUNCTION DEFAULT_SEQ_NUM (parent_id IN NUMBER, bg_id IN NUMBER, session_date IN date) RETURN NUMBER IS
parent_is_emp NUMBER(1) :=0;
select pap.PERSON_ID,
pap.EFFECTIVE_START_DATE,
pap.EFFECTIVE_END_DATE
from PER_ALL_PEOPLE_F pap,
PER_PERSON_TYPES ppt
where
pap.PERSON_ID=lPERSON_ID
and pap.BUSINESS_GROUP_ID=lBusiness_group_id
and pap.person_type_id=ppt.person_type_id
and ppt.SYSTEM_PERSON_TYPE='EMP'
and pap.EFFECTIVE_START_DATE <= session_date
and pap.EFFECTIVE_END_DATE >= session_date ;
select max(nvl(pcr.cont_information6,0)) into seq_num
from PER_CONTACT_RELATIONSHIPS pcr
where
pcr.BUSINESS_GROUP_ID=bg_id
and pcr.PERSON_ID=parent_id
and nvl(pcr.DATE_START,session_date) <=session_date
and nvl(pcr.DATE_END,session_date) >= session_date
and pcr.CONTACT_TYPE IN ('A','O','OC','T');