[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update fnd_lookup_values
Set ATTRIBUTE_CATEGORY = G_WSP_CAT_ATTRIBUTE_CATEGORY
Where lookup_type = G_WSP_CAT_LOOKUP_TYPE
And ATTRIBUTE_CATEGORY IS null
AND security_group_id = fnd_global.lookup_security_group(lookup_type,3)
AND lookup_code NOT IN
( Select lookup_code
FROM hr_lookups
Where lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
);
Description : called from LOOKUP_VAL_INSERT_ROW
LOOKUP_VAL_INSERT_ROW creats row in the fnd_lookup_values
If the meaning column is duplicate it prefix
year and duplicate no to meaning
*****************************************************************************/
function validate_lookup_meaning
(
P_LOOKUP_TYPE in varchar2
, P_MEANING in varchar2
, p_lookup_code in number
) return varchar2
Is
l_count number;
select count(*)
into l_count
from
fnd_lookup_values
Where lookup_type = P_LOOKUP_TYPE
and MEANING = P_MEANING
and lookup_code <> p_lookup_code
AND security_group_id = fnd_global.lookup_security_group(P_LOOKUP_TYPE,3);
Name : LOOKUP_VAL_INSERT_ROW
Description : LOOKUP_VAL_INSERT_ROW creats row in the fnd_lookup_values
lookup_code will have the YEAR appneded with id
*****************************************************************************/
procedure LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE in varchar2
, P_LOOKUP_CODE in varchar2
, P_ATTRIBUTE1 in varchar2
, P_ATTRIBUTE2 in varchar2
, P_ATTRIBUTE3 in varchar2
, P_ATTRIBUTE4 in varchar2
, P_ATTRIBUTE5 in varchar2
, P_ATTRIBUTE6 in varchar2
, P_ATTRIBUTE7 in varchar2
, P_ATTRIBUTE8 in varchar2
, P_ATTRIBUTE9 in varchar2
, P_ATTRIBUTE10 in varchar2
, P_ATTRIBUTE11 in varchar2
, P_ATTRIBUTE12 in varchar2
, P_ATTRIBUTE13 in varchar2
, P_ATTRIBUTE14 in varchar2
, P_ATTRIBUTE15 in varchar2
, P_ENABLED_FLAG in varchar2
, P_MEANING in varchar2
, P_DESCRIPTION in varchar2
, P_START_DATE_ACTIVE in varchar2
, P_END_DATE_ACTIVE in varchar2
)
is
l_row_id varchar2(100);
Select count(*)
INTO l_count
From
FND_LOOKUP_values
where
lookup_type = P_LOOKUP_TYPE
and lookup_code = P_LOOKUP_CODE
AND security_group_id = fnd_global.lookup_security_group(P_LOOKUP_TYPE,3);
hr_utility.set_location('inside LOOKUP_VAL_INSERT_ROW',1);
hr_utility.set_location('calling FND_LOOKUP_VALUES_PKG.INSERT_ROW',1);
SELECT vsize(P_DESCRIPTION) INTO lenb_desc FROM dual;
SELECT vsize(L_DESCRIPTION) INTO lenb_desc FROM dual;
FND_LOOKUP_VALUES_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_LOOKUP_TYPE => P_LOOKUP_TYPE,
X_SECURITY_GROUP_ID => fnd_global.lookup_security_group(P_LOOKUP_TYPE,3),
X_VIEW_APPLICATION_ID => 3,
X_LOOKUP_CODE => P_LOOKUP_CODE,
X_TAG => null,
X_ATTRIBUTE_CATEGORY => G_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => P_ATTRIBUTE1,
X_ATTRIBUTE2 => P_ATTRIBUTE2,
X_ATTRIBUTE3 => P_ATTRIBUTE3,
X_ATTRIBUTE4 => P_ATTRIBUTE4,
X_ATTRIBUTE5 => P_ATTRIBUTE5,
X_ATTRIBUTE6 => P_ATTRIBUTE6,
X_ATTRIBUTE7 => P_ATTRIBUTE7,
X_ATTRIBUTE8 => P_ATTRIBUTE8,
X_ATTRIBUTE9 => P_ATTRIBUTE9,
X_ATTRIBUTE10 => P_ATTRIBUTE10,
X_ATTRIBUTE11 => P_ATTRIBUTE11,
X_ATTRIBUTE12 => P_ATTRIBUTE12,
X_ATTRIBUTE13 => P_ATTRIBUTE13,
X_ATTRIBUTE14 => P_ATTRIBUTE14,
X_ATTRIBUTE15 => P_ATTRIBUTE15,
X_ENABLED_FLAG => P_ENABLED_FLAG,
X_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
X_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
X_TERRITORY_CODE => null,
X_MEANING => l_meaning,
X_DESCRIPTION => L_DESCRIPTION,
X_CREATION_DATE => trunc(sysdate),
X_CREATED_BY => 1,
X_LAST_UPDATE_DATE => trunc(sysdate),
X_LAST_UPDATED_BY => 1,
X_LAST_UPDATE_LOGIN => 0);
end LOOKUP_VAL_INSERT_ROW;
It deletes the existing lookup_values for the year and
create freshly.
*****************************************************************************/
Procedure create_lookup_values
(errbuf out nocopy varchar2,
retcode out nocopy number,
--p_business_group_id in number,
p_year in number,
p_plan_trng_ind in varchar2,
p_del_mode in varchar2)
is
-- Query for OLM plan for next year
-- Query for the Courses
Cursor csr_wsp_plan_courses
(p_start_date in date
, p_end_date in date
, p_year1 in number
)
is
select OAV.ACTIVITY_VERSION_ID LOOKUP_CODE,
substr(p_year1||':'||OAV_TL.VERSION_NAME ,1,80) MEANING,
substr(OAV_TL.DESCRIPTION,1,240) DESCRIPTION,
OAV_TL.LANGUAGE,
OAV_TL.Source_Lang
From OTA_ACTIVITY_VERSIONS OAV
, OTA_ACTIVITY_VERSIONS_TL OAV_TL
, HR_ORGANIZATION_INFORMATION org_i
Where OAV.BUSINESS_GROUP_ID = org_i.ORGANIZATION_ID
and org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA'
and OAV.START_DATE <= p_end_date
and ( OAV.END_DATE >= p_start_date
OR
OAV.END_DATE is null)
and OAV.activity_version_id = OAV_TL.activity_version_id
and OAV_TL.LANGUAGE = userenv('LANG');
Select OLP.LEARNING_PATH_ID LOOKUP_CODE,
substr(p_year1||':'||OLP_TL.NAME,1,80) MEANING,
substr(OLP_TL.DESCRIPTION,1,240) DESCRIPTION,
OLP_TL.LANGUAGE,
OLP_TL.Source_Lang
From OTA_LEARNING_PATHS OLP
, OTA_LEARNING_PATHS_TL OLP_TL
, HR_ORGANIZATION_INFORMATION org_i
Where OLP.business_group_id = org_i.ORGANIZATION_ID
and org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA'
And OLP.path_source_code = 'CATALOG' --Only for Bg level not at Mgr /Emp/Appriasal
And OLP.START_DATE_ACTIVE <= p_end_date
And ( OLP.END_DATE_ACTIVE >= p_start_date
OR
OLP.END_DATE_ACTIVE is null)
And OLP.LEARNING_PATH_ID = OLP_TL.LEARNING_PATH_ID
And OLP_TL.language = userenv('LANG') ;
Select OC.CERTIFICATION_ID LOOKUP_CODE,
substr(p_year1||':'||OC_TL.NAME,1,80) MEANING,
substr(OC_TL.DESCRIPTION,1,240) DESCRIPTION,
OC_TL.LANGUAGE,
OC_TL.Source_Lang
From OTA_CERTIFICATIONS_B OC
, OTA_CERTIFICATIONS_TL OC_TL
, HR_ORGANIZATION_INFORMATION org_i
Where OC.business_group_id = org_i.ORGANIZATION_ID
and org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA'
And OC.START_DATE_ACTIVE <= p_end_date
And ( OC.END_DATE_ACTIVE >= p_start_date
OR
OC.END_DATE_ACTIVE is null)
And OC.CERTIFICATION_ID = OC_TL.CERTIFICATION_ID
And OC_TL.language = userenv('LANG') ;
Select PC.COMPETENCE_ID LOOKUP_CODE,
substr(p_year1||':'||PC_TL.NAME,1,80) MEANING,
substr(PC_TL.NAME,decode(sign(length(PC_TL.NAME)-240),1,-240,1)) DESCRIPTION,
PC_TL.LANGUAGE,
PC_TL.Source_Lang,
PC.business_group_id
From PER_COMPETENCES PC
, PER_COMPETENCES_TL PC_TL
Where PC.COMPETENCE_ID = PC_TL.COMPETENCE_ID
And PC_TL.language = userenv('LANG')
and PC.DATE_FROM <= p_end_date
and ( PC.DATE_TO >= p_start_date
OR PC.DATE_TO is null)
and (nvl(PC.business_group_id,0) =0
Or exists
( Select 1 from
HR_ORGANIZATION_INFORMATION org_i
Where PC.business_group_id = org_i.ORGANIZATION_ID
And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA')
)
And exists
( Select 1 from
Per_competence_elements pce
, HR_ORGANIZATION_INFORMATION org_i
, per_all_people_f pp
, per_all_assignments_f paa
Where pce.competence_id = pc.COMPETENCE_ID
And pce.type = 'PERSONAL'
And pce.person_id = pp.person_id
And pce.EFFECTIVE_DATE_FROM between pp.effective_start_date
and pp.effective_end_date
And paa.person_id = pp.person_id
And paa.assignment_type = 'E'
And paa.primary_flag = 'Y'
And pce.EFFECTIVE_DATE_FROM between paa.effective_start_date
and paa.effective_end_date
and pce.EFFECTIVE_DATE_FROM between g_trnd_year_start_date
And g_trnd_year_end_date
and pce.business_group_id = org_i.ORGANIZATION_ID
And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA'
);
Select pqt.qualification_type_id LOOKUP_CODE
, substr(p_year1||':'||pqtl.NAME,1,80) MEANING
, pqtl.NAME DESCRIPTION
from
per_qualification_types pqt
, per_qualification_types_tl pqtl
Where pqt.qualification_type_id = pqtl.qualification_type_id
and pqtl.language = userenv('LANG')
and pqt.qualification_type_id in
( Select pq.qualification_type_id
from
per_qualifications pq
, PER_ESTABLISHMENT_ATTENDANCES pea
, per_all_people_f pp
, per_all_assignments_f paa
, HR_ORGANIZATION_INFORMATION org_i
Where pqt.qualification_type_id = pq.qualification_type_id
And paa.person_id = pp.person_id
and paa.assignment_type = 'E'
and paa.primary_flag = 'Y'
and pq.AWARDED_DATE between paa.effective_start_date
and paa.effective_end_date
And pea.ATTENDANCE_ID (+) = pq.attendance_id
and pq.AWARDED_DATE between g_trnd_year_start_date
And g_trnd_year_end_date
and nvl(pea.person_id,pq.person_id) = pp.person_id
and pq.AWARDED_DATE between pp.effective_start_date
and pp.effective_end_date
And pp.business_group_id = org_i.ORGANIZATION_ID
And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA'
);
Delete from fnd_lookup_values
Where lookup_type in
( g_p_lpath_lookup_type
, g_p_course_lookup_type
, g_p_cert_lookup_type
)
AND security_group_id = fnd_global.lookup_security_group(lookup_type,3)
And substr(lookup_code,1,4) = p_year;
Select count(*)
into l_count
From
OTA_LP_ENROLLMENTS OLE
, HR_ORGANIZATION_INFORMATION org_i
, OTA_LP_MEMBER_ENROLLMENTS OLME
, per_all_people_f pp
, per_all_assignments_f paa
Where OLE.LEARNING_PATH_ID = lp_rec.LOOKUP_CODE
And OLME.LP_ENROLLMENT_ID = OLE.LP_ENROLLMENT_ID
And OLE.PATH_STATUS_CODE <> 'CANCELLED'
And ( OLE.COMPLETION_DATE between g_plan_year_start_date
And g_plan_year_end_date
OR
OLE.COMPLETION_DATE IS null)
AND pp.person_id = OLE.PERSON_ID
And paa.person_id = pp.person_id
and paa.assignment_type = 'E'
and paa.primary_flag = 'Y'
and OLE.business_group_id = paa.BUSINESS_GROUP_ID
And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA';
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => g_p_lpath_lookup_type
, P_LOOKUP_CODE => p_year||lp_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => null
, P_ATTRIBUTE2 => null
, P_ATTRIBUTE3 => null
, P_ATTRIBUTE4 => null
, P_ATTRIBUTE5 => null
, P_ATTRIBUTE6 => null
, P_ATTRIBUTE7 => null
, P_ATTRIBUTE8 => null
, P_ATTRIBUTE9 => null
, P_ATTRIBUTE10 => null
, P_ATTRIBUTE11 => null
, P_ATTRIBUTE12 => null
, P_ATTRIBUTE13 => null
, P_ATTRIBUTE14 => null
, P_ATTRIBUTE15 => null
, P_ENABLED_FLAG => 'Y'
, P_MEANING => lp_rec.MEANING
, P_DESCRIPTION => lp_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_plan_year_start_date
, P_END_DATE_ACTIVE => g_plan_year_end_date
);
Select count(*)
into l_count
from
OTA_EVENTS oe
, OTA_DELEGATE_BOOKINGS odb
, OTA_BOOKING_STATUS_TYPES obst
, HR_ORGANIZATION_INFORMATION org_i
, per_all_people_f pp
, per_all_assignments_f paa
wHERE ACTIVITY_VERSION_ID = course_rec.LOOKUP_CODE
aND oe.EVENT_TYPE in ( 'SCHEDULED', 'SELFPACED')
AND OE.course_START_DATE <= g_plan_year_end_date
AND NVL(OE.course_end_DATE, g_plan_year_start_date) >= g_plan_year_start_date
aND ODB.EVENT_ID = oe.EVENT_ID
And ODB.INTERNAL_BOOKING_FLAG = 'Y'
And paa.person_id = pp.person_id
and paa.assignment_type = 'E'
and paa.primary_flag = 'Y'
and ODB.DATE_BOOKING_PLACED between paa.effective_start_date
and paa.effective_end_date
And pp.person_id = ODB.DELEGATE_PERSON_ID
And ODB.DATE_BOOKING_PLACED between pp.effective_start_date
and pp.effective_end_date
And paa.business_group_id = org_i.ORGANIZATION_ID
And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA'
aND OBST.BOOKING_STATUS_TYPE_ID = odb.BOOKING_STATUS_TYPE_ID
AND obst.TYPE IN ('P','W','R','A'); -- 'C'' cANCELLED, 'P' Palced , 'W' Waitlisted, 'R' Requested, 'A' Attended
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => g_p_course_lookup_type
, P_LOOKUP_CODE => p_year||course_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => null
, P_ATTRIBUTE2 => null
, P_ATTRIBUTE3 => null
, P_ATTRIBUTE4 => null
, P_ATTRIBUTE5 => null
, P_ATTRIBUTE6 => null
, P_ATTRIBUTE7 => null
, P_ATTRIBUTE8 => null
, P_ATTRIBUTE9 => null
, P_ATTRIBUTE10 => null
, P_ATTRIBUTE11 => null
, P_ATTRIBUTE12 => null
, P_ATTRIBUTE13 => null
, P_ATTRIBUTE14 => null
, P_ATTRIBUTE15 => null
, P_ENABLED_FLAG => 'Y'
, P_MEANING => course_rec.MEANING
, P_DESCRIPTION => course_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_plan_year_start_date
, P_END_DATE_ACTIVE => g_plan_year_end_date
);
Select count(*)
into l_count
From
OTA_CERT_ENROLLMENTS OCE
, HR_ORGANIZATION_INFORMATION org_i
, per_all_people_f pp
, per_all_assignments_f paa
Where
OCE.CERTIFICATION_ID = cert_rec.LOOKUP_CODE
And OCE.PERSON_ID = PP.person_id
And paa.person_id = pp.person_id
and paa.assignment_type = 'E'
and paa.primary_flag = 'Y'
AND org_i.ORGANIZATION_ID = paa.business_group_id
And OCE.CERTIFICATION_STATUS_CODE = 'ENROLLED'
And ( OCE.COMPLETION_DATE Between g_plan_year_start_date
And g_plan_year_end_date
OR
OCE.COMPLETION_DATE IS null)
And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA';
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => g_p_cert_lookup_type
, P_LOOKUP_CODE => p_year||cert_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => null
, P_ATTRIBUTE2 => null
, P_ATTRIBUTE3 => null
, P_ATTRIBUTE4 => null
, P_ATTRIBUTE5 => null
, P_ATTRIBUTE6 => null
, P_ATTRIBUTE7 => null
, P_ATTRIBUTE8 => null
, P_ATTRIBUTE9 => null
, P_ATTRIBUTE10 => null
, P_ATTRIBUTE11 => null
, P_ATTRIBUTE12 => null
, P_ATTRIBUTE13 => null
, P_ATTRIBUTE14 => null
, P_ATTRIBUTE15 => null
, P_ENABLED_FLAG => 'Y'
, P_MEANING => cert_rec.MEANING
, P_DESCRIPTION => cert_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_plan_year_start_date
, P_END_DATE_ACTIVE => g_plan_year_end_date
);
Delete from fnd_lookup_values
Where lookup_type in
( g_t_lpath_lookup_type
, g_t_course_lookup_type
, g_t_cert_lookup_type
, g_t_comp_lookup_type
, g_t_qual_lookup_type
)
AND security_group_id = fnd_global.lookup_security_group(lookup_type,3)
And substr(lookup_code,1,4) = p_year - 1;
Select count(*)
into l_count
From
OTA_LP_ENROLLMENTS OLE
, HR_ORGANIZATION_INFORMATION org_i
, OTA_LP_MEMBER_ENROLLMENTS OLME
Where OLE.LEARNING_PATH_ID = lp_rec.LOOKUP_CODE
And OLE.PATH_STATUS_CODE = 'COMPLETED'
And OLME.LP_ENROLLMENT_ID = OLE.LP_ENROLLMENT_ID
And OLE.COMPLETION_DATE between g_trnd_year_start_date
And g_trnd_year_end_date
and OLE.business_group_id = org_i.ORGANIZATION_ID
And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA';
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => g_t_lpath_lookup_type
, P_LOOKUP_CODE => p_year -1||lp_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => null
, P_ATTRIBUTE2 => null
, P_ATTRIBUTE3 => null
, P_ATTRIBUTE4 => null
, P_ATTRIBUTE5 => null
, P_ATTRIBUTE6 => null
, P_ATTRIBUTE7 => null
, P_ATTRIBUTE8 => null
, P_ATTRIBUTE9 => null
, P_ATTRIBUTE10 => null
, P_ATTRIBUTE11 => null
, P_ATTRIBUTE12 => null
, P_ATTRIBUTE13 => null
, P_ATTRIBUTE14 => null
, P_ATTRIBUTE15 => null
, P_ENABLED_FLAG => 'Y'
, P_MEANING => lp_rec.MEANING
, P_DESCRIPTION => lp_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_trnd_year_start_date
, P_END_DATE_ACTIVE => g_trnd_year_end_date
);
Select count(*)
into l_count
from
OTA_EVENTS oe
, OTA_DELEGATE_BOOKINGS odb
, OTA_BOOKING_STATUS_TYPES obst
, HR_ORGANIZATION_INFORMATION org_i
, per_all_people_f pp
, per_all_assignments_f paa
wHERE ACTIVITY_VERSION_ID = course_rec.LOOKUP_CODE
aND oe.EVENT_TYPE in ( 'SCHEDULED', 'SELFPACED')
AND OE.course_START_DATE <= g_trnd_year_end_date
AND NVL(OE.course_end_DATE, g_trnd_year_start_date) >= g_trnd_year_start_date
aND ODB.EVENT_ID = oe.EVENT_ID
And ODB.INTERNAL_BOOKING_FLAG = 'Y'
And paa.person_id = pp.person_id
and paa.assignment_type = 'E'
and paa.primary_flag = 'Y'
AND odb.DATE_STATUS_CHANGED BETWEEN g_trnd_year_start_date
And g_trnd_year_end_date
and odb.DATE_STATUS_CHANGED between paa.effective_start_date
and paa.effective_end_date
And pp.person_id = odb.DELEGATE_PERSON_ID
And odb.DATE_STATUS_CHANGED between pp.effective_start_date
and pp.effective_end_date
And odb.business_group_id = org_i.ORGANIZATION_ID
And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA'
aND OBST.BOOKING_STATUS_TYPE_ID = odb.BOOKING_STATUS_TYPE_ID
AND obst.TYPE = 'A'; -- Attended
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => g_t_course_lookup_type
, P_LOOKUP_CODE => p_year -1||course_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => null
, P_ATTRIBUTE2 => null
, P_ATTRIBUTE3 => null
, P_ATTRIBUTE4 => null
, P_ATTRIBUTE5 => null
, P_ATTRIBUTE6 => null
, P_ATTRIBUTE7 => null
, P_ATTRIBUTE8 => null
, P_ATTRIBUTE9 => null
, P_ATTRIBUTE10 => null
, P_ATTRIBUTE11 => null
, P_ATTRIBUTE12 => null
, P_ATTRIBUTE13 => null
, P_ATTRIBUTE14 => null
, P_ATTRIBUTE15 => null
, P_ENABLED_FLAG => 'Y'
, P_MEANING => course_rec.MEANING
, P_DESCRIPTION => course_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_trnd_year_start_date
, P_END_DATE_ACTIVE => g_trnd_year_end_date
);
Select count(*)
into l_count
From
OTA_CERT_ENROLLMENTS OCE
, HR_ORGANIZATION_INFORMATION org_i
Where
OCE.CERTIFICATION_ID = cert_rec.LOOKUP_CODE
And OCE.BUSINESS_GROUP_ID = org_i.ORGANIZATION_ID
And OCE.CERTIFICATION_STATUS_CODE = 'CERTIFIED'
And OCE.COMPLETION_DATE Between g_trnd_year_start_date
And g_trnd_year_end_date
And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_i.ORG_INFORMATION9 = 'ZA';
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => g_t_cert_lookup_type
, P_LOOKUP_CODE => p_year -1||cert_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => null
, P_ATTRIBUTE2 => null
, P_ATTRIBUTE3 => null
, P_ATTRIBUTE4 => null
, P_ATTRIBUTE5 => null
, P_ATTRIBUTE6 => null
, P_ATTRIBUTE7 => null
, P_ATTRIBUTE8 => null
, P_ATTRIBUTE9 => null
, P_ATTRIBUTE10 => null
, P_ATTRIBUTE11 => null
, P_ATTRIBUTE12 => null
, P_ATTRIBUTE13 => null
, P_ATTRIBUTE14 => null
, P_ATTRIBUTE15 => null
, P_ENABLED_FLAG => 'Y'
, P_MEANING => cert_rec.MEANING
, P_DESCRIPTION => cert_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_trnd_year_start_date
, P_END_DATE_ACTIVE => g_trnd_year_end_date
);
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => g_t_comp_lookup_type
, P_LOOKUP_CODE => p_year -1||comp_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => null
, P_ATTRIBUTE2 => null
, P_ATTRIBUTE3 => null
, P_ATTRIBUTE4 => null
, P_ATTRIBUTE5 => null
, P_ATTRIBUTE6 => null
, P_ATTRIBUTE7 => null
, P_ATTRIBUTE8 => null
, P_ATTRIBUTE9 => null
, P_ATTRIBUTE10 => null
, P_ATTRIBUTE11 => null
, P_ATTRIBUTE12 => null
, P_ATTRIBUTE13 => null
, P_ATTRIBUTE14 => null
, P_ATTRIBUTE15 => null
, P_ENABLED_FLAG => 'Y'
, P_MEANING => comp_rec.MEANING
, P_DESCRIPTION => comp_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_trnd_year_start_date
, P_END_DATE_ACTIVE => g_trnd_year_end_date
);
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => g_t_qual_lookup_type
, P_LOOKUP_CODE => p_year -1||qual_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => null
, P_ATTRIBUTE2 => null
, P_ATTRIBUTE3 => null
, P_ATTRIBUTE4 => null
, P_ATTRIBUTE5 => null
, P_ATTRIBUTE6 => null
, P_ATTRIBUTE7 => null
, P_ATTRIBUTE8 => null
, P_ATTRIBUTE9 => null
, P_ATTRIBUTE10 => null
, P_ATTRIBUTE11 => null
, P_ATTRIBUTE12 => null
, P_ATTRIBUTE13 => null
, P_ATTRIBUTE14 => null
, P_ATTRIBUTE15 => null
, P_ENABLED_FLAG => 'Y'
, P_MEANING => qual_rec.MEANING
, P_DESCRIPTION => qual_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_trnd_year_start_date
, P_END_DATE_ACTIVE => g_trnd_year_end_date
);
Update fnd_lookup_values
Set ATTRIBUTE_CATEGORY = G_ATTRIBUTE_CATEGORY
Where lookup_type in
(
g_p_lpath_lookup_type
, g_p_course_lookup_type
, g_p_cert_lookup_type
)
And ATTRIBUTE_CATEGORY is null
And security_group_id = fnd_global.lookup_security_group(lookup_type,3);
Update fnd_lookup_values
Set ATTRIBUTE_CATEGORY = G_ATTRIBUTE_CATEGORY
Where lookup_type in
(
g_t_lpath_lookup_type
, g_t_course_lookup_type
, g_t_cert_lookup_type
, g_t_comp_lookup_type
, g_t_qual_lookup_type
)
And security_group_id = fnd_global.lookup_security_group(lookup_type,3)
And ATTRIBUTE_CATEGORY is null;
, p_del_mode => 'N' -- N do not delete the existing lookup values
);
select user_row_id,
user_table_id,
ROW_LOW_RANGE_OR_NAME,
DISPLAY_SEQUENCE,
business_group_id,
legislation_code,
row_high_range,
effective_end_date
from pay_user_rows_f usr
where usr.user_row_id = P_user_row_id
and usr.effective_end_date =
(
Select max(usr1.effective_end_date)
From pay_user_rows_f usr1
Where usr1.user_row_id = P_user_row_id
And usr.effective_end_date >= p_from_start_date
And usr.effective_start_date <= p_from_end_date
)
and usr.effective_end_date < p_start_date;
select USER_COLUMN_INSTANCE_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,USER_ROW_ID
,USER_COLUMN_ID
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,LEGISLATION_SUBGROUP
,VALUE
from pay_user_column_instances_f puv
Where puv.user_row_id = p_user_row_id
and puv.EFFECTIVE_END_DATE =
(
select max(puv1.EFFECTIVE_END_DATE)
From pay_user_column_instances_f puv1
Where puv1.EFFECTIVE_END_DATE >= p_from_start_date
and puv1.EFFECTIVE_START_DATE <= p_from_end_date
)
and puv.effective_end_date < p_start_date;
Select count(*) into l_obj_ver
From pay_user_rows_f usr1
Where usr1.user_row_id = P_user_row_id
And usr1.effective_end_date >= l_start_date
And usr1.effective_start_date <= l_end_date;
select l_year || substr(lookup_code,5) lookup_code
, lookup_type
, LANGUAGE
, l_year || substr(MEANING,5) MEANING
, DESCRIPTION
, ENABLED_FLAG
-- , to_date(to_char(START_DATE_ACTIVE,'DD-MM')||p_year -1,'DD-MM-YYYY') START_DATE_ACTIVE
-- , to_date(to_char(END_DATE_ACTIVE,'DD-MM')||p_year,'DD-MM-YYYY') END_DATE_ACTIVE
, SOURCE_LANG
, SECURITY_GROUP_ID
, VIEW_APPLICATION_ID
, TERRITORY_CODE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
from fnd_lookup_values
Where lookup_type = l_lookup_type
and substr(lookup_code,1,4) = to_char(l_from_year)
And security_group_id = fnd_global.lookup_security_group(l_lookup_type,3)
and (ATTRIBUTE1 ||ATTRIBUTE2 ||ATTRIBUTE3 ||ATTRIBUTE4 ||
ATTRIBUTE5 ||ATTRIBUTE6 ||ATTRIBUTE7 ||ATTRIBUTE8 ||
ATTRIBUTE9 ||ATTRIBUTE10||ATTRIBUTE11||ATTRIBUTE12||
ATTRIBUTE13||ATTRIBUTE14||ATTRIBUTE15) is not null;
select count(*) into l_trnd_count
from fnd_lookup_values
Where lookup_type in
(
g_t_lpath_lookup_type
, g_t_course_lookup_type
, g_t_cert_lookup_type
)
AND security_group_id = fnd_global.lookup_security_group(lookup_type,3)
and substr(lookup_code,1,4) = p_year-1;
select count(*) into l_plan_count
from fnd_lookup_values
Where lookup_type in
(
g_p_lpath_lookup_type
, g_p_course_lookup_type
, g_p_cert_lookup_type
)
AND security_group_id = fnd_global.lookup_security_group(lookup_type,3)
and substr(lookup_code,1,4) = p_year;
tab_usr_row_ids.delete;
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => csr_lp_rec.LOOKUP_TYPE
, P_LOOKUP_CODE => csr_lp_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => csr_lp_rec.ATTRIBUTE1
, P_ATTRIBUTE2 => csr_lp_rec.ATTRIBUTE2
, P_ATTRIBUTE3 => csr_lp_rec.ATTRIBUTE3
, P_ATTRIBUTE4 => csr_lp_rec.ATTRIBUTE4
, P_ATTRIBUTE5 => csr_lp_rec.ATTRIBUTE5
, P_ATTRIBUTE6 => csr_lp_rec.ATTRIBUTE6
, P_ATTRIBUTE7 => csr_lp_rec.ATTRIBUTE7
, P_ATTRIBUTE8 => csr_lp_rec.ATTRIBUTE8
, P_ATTRIBUTE9 => csr_lp_rec.ATTRIBUTE9
, P_ATTRIBUTE10 => csr_lp_rec.ATTRIBUTE10
, P_ATTRIBUTE11 => csr_lp_rec.ATTRIBUTE11
, P_ATTRIBUTE12 => csr_lp_rec.ATTRIBUTE12
, P_ATTRIBUTE13 => csr_lp_rec.ATTRIBUTE13
, P_ATTRIBUTE14 => csr_lp_rec.ATTRIBUTE14
, P_ATTRIBUTE15 => csr_lp_rec.ATTRIBUTE15
, P_ENABLED_FLAG => 'Y'
, P_MEANING => csr_lp_rec.MEANING
, P_DESCRIPTION => csr_lp_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_plan_year_start_date
, P_END_DATE_ACTIVE => g_plan_year_end_date
);
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => csr_cert_rec.LOOKUP_TYPE
, P_LOOKUP_CODE => csr_cert_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => csr_cert_rec.ATTRIBUTE1
, P_ATTRIBUTE2 => csr_cert_rec.ATTRIBUTE2
, P_ATTRIBUTE3 => csr_cert_rec.ATTRIBUTE3
, P_ATTRIBUTE4 => csr_cert_rec.ATTRIBUTE4
, P_ATTRIBUTE5 => csr_cert_rec.ATTRIBUTE5
, P_ATTRIBUTE6 => csr_cert_rec.ATTRIBUTE6
, P_ATTRIBUTE7 => csr_cert_rec.ATTRIBUTE7
, P_ATTRIBUTE8 => csr_cert_rec.ATTRIBUTE8
, P_ATTRIBUTE9 => csr_cert_rec.ATTRIBUTE9
, P_ATTRIBUTE10 => csr_cert_rec.ATTRIBUTE10
, P_ATTRIBUTE11 => csr_cert_rec.ATTRIBUTE11
, P_ATTRIBUTE12 => csr_cert_rec.ATTRIBUTE12
, P_ATTRIBUTE13 => csr_cert_rec.ATTRIBUTE13
, P_ATTRIBUTE14 => csr_cert_rec.ATTRIBUTE14
, P_ATTRIBUTE15 => csr_cert_rec.ATTRIBUTE15
, P_ENABLED_FLAG => 'Y'
, P_MEANING => csr_cert_rec.MEANING
, P_DESCRIPTION => csr_cert_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_plan_year_start_date
, P_END_DATE_ACTIVE => g_plan_year_end_date
);
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => csr_crs_rec.LOOKUP_TYPE
, P_LOOKUP_CODE => csr_crs_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => csr_crs_rec.ATTRIBUTE1
, P_ATTRIBUTE2 => csr_crs_rec.ATTRIBUTE2
, P_ATTRIBUTE3 => csr_crs_rec.ATTRIBUTE3
, P_ATTRIBUTE4 => csr_crs_rec.ATTRIBUTE4
, P_ATTRIBUTE5 => csr_crs_rec.ATTRIBUTE5
, P_ATTRIBUTE6 => csr_crs_rec.ATTRIBUTE6
, P_ATTRIBUTE7 => csr_crs_rec.ATTRIBUTE7
, P_ATTRIBUTE8 => csr_crs_rec.ATTRIBUTE8
, P_ATTRIBUTE9 => csr_crs_rec.ATTRIBUTE9
, P_ATTRIBUTE10 => csr_crs_rec.ATTRIBUTE10
, P_ATTRIBUTE11 => csr_crs_rec.ATTRIBUTE11
, P_ATTRIBUTE12 => csr_crs_rec.ATTRIBUTE12
, P_ATTRIBUTE13 => csr_crs_rec.ATTRIBUTE13
, P_ATTRIBUTE14 => csr_crs_rec.ATTRIBUTE14
, P_ATTRIBUTE15 => csr_crs_rec.ATTRIBUTE15
, P_ENABLED_FLAG => 'Y'
, P_MEANING => csr_crs_rec.MEANING
, P_DESCRIPTION => csr_crs_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_plan_year_start_date
, P_END_DATE_ACTIVE => g_plan_year_end_date
);
, p_del_mode => 'N' -- N do not delete the existing lookup values
);
tab_usr_row_ids.delete;
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => csr_lp_rec.LOOKUP_TYPE
, P_LOOKUP_CODE => csr_lp_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => csr_lp_rec.ATTRIBUTE1
, P_ATTRIBUTE2 => csr_lp_rec.ATTRIBUTE2
, P_ATTRIBUTE3 => csr_lp_rec.ATTRIBUTE3
, P_ATTRIBUTE4 => csr_lp_rec.ATTRIBUTE4
, P_ATTRIBUTE5 => csr_lp_rec.ATTRIBUTE5
, P_ATTRIBUTE6 => csr_lp_rec.ATTRIBUTE6
, P_ATTRIBUTE7 => csr_lp_rec.ATTRIBUTE7
, P_ATTRIBUTE8 => csr_lp_rec.ATTRIBUTE8
, P_ATTRIBUTE9 => csr_lp_rec.ATTRIBUTE9
, P_ATTRIBUTE10 => csr_lp_rec.ATTRIBUTE10
, P_ATTRIBUTE11 => csr_lp_rec.ATTRIBUTE11
, P_ATTRIBUTE12 => csr_lp_rec.ATTRIBUTE12
, P_ATTRIBUTE13 => csr_lp_rec.ATTRIBUTE13
, P_ATTRIBUTE14 => csr_lp_rec.ATTRIBUTE14
, P_ATTRIBUTE15 => csr_lp_rec.ATTRIBUTE15
, P_ENABLED_FLAG => 'Y'
, P_MEANING => csr_lp_rec.MEANING
, P_DESCRIPTION => csr_lp_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_trnd_year_start_date
, P_END_DATE_ACTIVE => g_trnd_year_end_date
);
hr_utility.SET_LOCATION('calling trained LOOKUP_VAL_INSERT_ROW ' ,1);
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => csr_cert_rec.LOOKUP_TYPE
, P_LOOKUP_CODE => csr_cert_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => csr_cert_rec.ATTRIBUTE1
, P_ATTRIBUTE2 => csr_cert_rec.ATTRIBUTE2
, P_ATTRIBUTE3 => csr_cert_rec.ATTRIBUTE3
, P_ATTRIBUTE4 => csr_cert_rec.ATTRIBUTE4
, P_ATTRIBUTE5 => csr_cert_rec.ATTRIBUTE5
, P_ATTRIBUTE6 => csr_cert_rec.ATTRIBUTE6
, P_ATTRIBUTE7 => csr_cert_rec.ATTRIBUTE7
, P_ATTRIBUTE8 => csr_cert_rec.ATTRIBUTE8
, P_ATTRIBUTE9 => csr_cert_rec.ATTRIBUTE9
, P_ATTRIBUTE10 => csr_cert_rec.ATTRIBUTE10
, P_ATTRIBUTE11 => csr_cert_rec.ATTRIBUTE11
, P_ATTRIBUTE12 => csr_cert_rec.ATTRIBUTE12
, P_ATTRIBUTE13 => csr_cert_rec.ATTRIBUTE13
, P_ATTRIBUTE14 => csr_cert_rec.ATTRIBUTE14
, P_ATTRIBUTE15 => csr_cert_rec.ATTRIBUTE15
, P_ENABLED_FLAG => 'Y'
, P_MEANING => csr_cert_rec.MEANING
, P_DESCRIPTION => csr_cert_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_trnd_year_start_date
, P_END_DATE_ACTIVE => g_trnd_year_end_date
);
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => csr_crs_rec.LOOKUP_TYPE
, P_LOOKUP_CODE => csr_crs_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => csr_crs_rec.ATTRIBUTE1
, P_ATTRIBUTE2 => csr_crs_rec.ATTRIBUTE2
, P_ATTRIBUTE3 => csr_crs_rec.ATTRIBUTE3
, P_ATTRIBUTE4 => csr_crs_rec.ATTRIBUTE4
, P_ATTRIBUTE5 => csr_crs_rec.ATTRIBUTE5
, P_ATTRIBUTE6 => csr_crs_rec.ATTRIBUTE6
, P_ATTRIBUTE7 => csr_crs_rec.ATTRIBUTE7
, P_ATTRIBUTE8 => csr_crs_rec.ATTRIBUTE8
, P_ATTRIBUTE9 => csr_crs_rec.ATTRIBUTE9
, P_ATTRIBUTE10 => csr_crs_rec.ATTRIBUTE10
, P_ATTRIBUTE11 => csr_crs_rec.ATTRIBUTE11
, P_ATTRIBUTE12 => csr_crs_rec.ATTRIBUTE12
, P_ATTRIBUTE13 => csr_crs_rec.ATTRIBUTE13
, P_ATTRIBUTE14 => csr_crs_rec.ATTRIBUTE14
, P_ATTRIBUTE15 => csr_crs_rec.ATTRIBUTE15
, P_ENABLED_FLAG => 'Y'
, P_MEANING => csr_crs_rec.MEANING
, P_DESCRIPTION => csr_crs_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_trnd_year_start_date
, P_END_DATE_ACTIVE => g_trnd_year_end_date
);
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => csr_comp_rec.LOOKUP_TYPE
, P_LOOKUP_CODE => csr_comp_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => csr_comp_rec.ATTRIBUTE1
, P_ATTRIBUTE2 => csr_comp_rec.ATTRIBUTE2
, P_ATTRIBUTE3 => csr_comp_rec.ATTRIBUTE3
, P_ATTRIBUTE4 => csr_comp_rec.ATTRIBUTE4
, P_ATTRIBUTE5 => csr_comp_rec.ATTRIBUTE5
, P_ATTRIBUTE6 => csr_comp_rec.ATTRIBUTE6
, P_ATTRIBUTE7 => csr_comp_rec.ATTRIBUTE7
, P_ATTRIBUTE8 => csr_comp_rec.ATTRIBUTE8
, P_ATTRIBUTE9 => csr_comp_rec.ATTRIBUTE9
, P_ATTRIBUTE10 => csr_comp_rec.ATTRIBUTE10
, P_ATTRIBUTE11 => csr_comp_rec.ATTRIBUTE11
, P_ATTRIBUTE12 => csr_comp_rec.ATTRIBUTE12
, P_ATTRIBUTE13 => csr_comp_rec.ATTRIBUTE13
, P_ATTRIBUTE14 => csr_comp_rec.ATTRIBUTE14
, P_ATTRIBUTE15 => csr_comp_rec.ATTRIBUTE15
, P_ENABLED_FLAG => 'Y'
, P_MEANING => csr_comp_rec.MEANING
, P_DESCRIPTION => csr_comp_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_trnd_year_start_date
, P_END_DATE_ACTIVE => g_trnd_year_end_date
);
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => csr_qual_rec.LOOKUP_TYPE
, P_LOOKUP_CODE => csr_qual_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => csr_qual_rec.ATTRIBUTE1
, P_ATTRIBUTE2 => csr_qual_rec.ATTRIBUTE2
, P_ATTRIBUTE3 => csr_qual_rec.ATTRIBUTE3
, P_ATTRIBUTE4 => csr_qual_rec.ATTRIBUTE4
, P_ATTRIBUTE5 => csr_qual_rec.ATTRIBUTE5
, P_ATTRIBUTE6 => csr_qual_rec.ATTRIBUTE6
, P_ATTRIBUTE7 => csr_qual_rec.ATTRIBUTE7
, P_ATTRIBUTE8 => csr_qual_rec.ATTRIBUTE8
, P_ATTRIBUTE9 => csr_qual_rec.ATTRIBUTE9
, P_ATTRIBUTE10 => csr_qual_rec.ATTRIBUTE10
, P_ATTRIBUTE11 => csr_qual_rec.ATTRIBUTE11
, P_ATTRIBUTE12 => csr_qual_rec.ATTRIBUTE12
, P_ATTRIBUTE13 => csr_qual_rec.ATTRIBUTE13
, P_ATTRIBUTE14 => csr_qual_rec.ATTRIBUTE14
, P_ATTRIBUTE15 => csr_qual_rec.ATTRIBUTE15
, P_ENABLED_FLAG => 'Y'
, P_MEANING => csr_qual_rec.MEANING
, P_DESCRIPTION => csr_qual_rec.DESCRIPTION
, P_START_DATE_ACTIVE => g_trnd_year_start_date
, P_END_DATE_ACTIVE => g_trnd_year_end_date
);
, p_del_mode => 'N' -- N do not delete the existing lookup values
);
tab_usr_row_ids.delete;
select p_year -1 || substr(lookup_code,5) lookup_code
, lookup_type
, LANGUAGE
, MEANING
, DESCRIPTION
, ENABLED_FLAG
, g_trnd_year_start_date START_DATE_ACTIVE
, g_trnd_year_end_date END_DATE_ACTIVE
, SOURCE_LANG
, SECURITY_GROUP_ID
, VIEW_APPLICATION_ID
, TERRITORY_CODE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
from fnd_lookup_values
Where lookup_type = l_lookup_type
and substr(lookup_code,1,4) = to_char(P_from_year)
And security_group_id = fnd_global.lookup_security_group(l_lookup_type,3)
and (ATTRIBUTE1 ||ATTRIBUTE2 ||ATTRIBUTE3 ||ATTRIBUTE4 ||
ATTRIBUTE5 ||ATTRIBUTE6 ||ATTRIBUTE7 ||ATTRIBUTE8 ||
ATTRIBUTE9 ||ATTRIBUTE10||ATTRIBUTE11||ATTRIBUTE12||
ATTRIBUTE13||ATTRIBUTE14||ATTRIBUTE15) is not null;
select count(*) into l_count
from fnd_lookup_values
Where lookup_type in
(
g_t_lpath_lookup_type
, g_t_course_lookup_type
, g_t_cert_lookup_type
)
And security_group_id = fnd_global.lookup_security_group(lookup_type,3)
and substr(lookup_code,1,4) = to_char(p_year-1);
tab_usr_row_ids.delete;
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => g_t_lpath_lookup_type
, P_LOOKUP_CODE => plan_2_train_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => plan_2_train_rec.ATTRIBUTE1
, P_ATTRIBUTE2 => plan_2_train_rec.ATTRIBUTE2
, P_ATTRIBUTE3 => plan_2_train_rec.ATTRIBUTE3
, P_ATTRIBUTE4 => plan_2_train_rec.ATTRIBUTE4
, P_ATTRIBUTE5 => plan_2_train_rec.ATTRIBUTE5
, P_ATTRIBUTE6 => plan_2_train_rec.ATTRIBUTE6
, P_ATTRIBUTE7 => plan_2_train_rec.ATTRIBUTE7
, P_ATTRIBUTE8 => plan_2_train_rec.ATTRIBUTE8
, P_ATTRIBUTE9 => plan_2_train_rec.ATTRIBUTE9
, P_ATTRIBUTE10 => plan_2_train_rec.ATTRIBUTE10
, P_ATTRIBUTE11 => plan_2_train_rec.ATTRIBUTE11
, P_ATTRIBUTE12 => plan_2_train_rec.ATTRIBUTE12
, P_ATTRIBUTE13 => plan_2_train_rec.ATTRIBUTE13
, P_ATTRIBUTE14 => plan_2_train_rec.ATTRIBUTE14
, P_ATTRIBUTE15 => plan_2_train_rec.ATTRIBUTE15
, P_ENABLED_FLAG => 'Y'
, P_MEANING => plan_2_train_rec.MEANING
, P_DESCRIPTION => plan_2_train_rec.DESCRIPTION
, P_START_DATE_ACTIVE => plan_2_train_rec.START_DATE_ACTIVE
, P_END_DATE_ACTIVE => plan_2_train_rec.END_DATE_ACTIVE
);
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => g_t_course_lookup_type
, P_LOOKUP_CODE => plan_2_train_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => plan_2_train_rec.ATTRIBUTE1
, P_ATTRIBUTE2 => plan_2_train_rec.ATTRIBUTE2
, P_ATTRIBUTE3 => plan_2_train_rec.ATTRIBUTE3
, P_ATTRIBUTE4 => plan_2_train_rec.ATTRIBUTE4
, P_ATTRIBUTE5 => plan_2_train_rec.ATTRIBUTE5
, P_ATTRIBUTE6 => plan_2_train_rec.ATTRIBUTE6
, P_ATTRIBUTE7 => plan_2_train_rec.ATTRIBUTE7
, P_ATTRIBUTE8 => plan_2_train_rec.ATTRIBUTE8
, P_ATTRIBUTE9 => plan_2_train_rec.ATTRIBUTE9
, P_ATTRIBUTE10 => plan_2_train_rec.ATTRIBUTE10
, P_ATTRIBUTE11 => plan_2_train_rec.ATTRIBUTE11
, P_ATTRIBUTE12 => plan_2_train_rec.ATTRIBUTE12
, P_ATTRIBUTE13 => plan_2_train_rec.ATTRIBUTE13
, P_ATTRIBUTE14 => plan_2_train_rec.ATTRIBUTE14
, P_ATTRIBUTE15 => plan_2_train_rec.ATTRIBUTE15
, P_ENABLED_FLAG => 'Y'
, P_MEANING => plan_2_train_rec.MEANING
, P_DESCRIPTION => plan_2_train_rec.DESCRIPTION
, P_START_DATE_ACTIVE => plan_2_train_rec.START_DATE_ACTIVE
, P_END_DATE_ACTIVE => plan_2_train_rec.END_DATE_ACTIVE
);
LOOKUP_VAL_INSERT_ROW
( P_LOOKUP_TYPE => g_t_cert_lookup_type
, P_LOOKUP_CODE => plan_2_train_rec.LOOKUP_CODE
, P_ATTRIBUTE1 => plan_2_train_rec.ATTRIBUTE1
, P_ATTRIBUTE2 => plan_2_train_rec.ATTRIBUTE2
, P_ATTRIBUTE3 => plan_2_train_rec.ATTRIBUTE3
, P_ATTRIBUTE4 => plan_2_train_rec.ATTRIBUTE4
, P_ATTRIBUTE5 => plan_2_train_rec.ATTRIBUTE5
, P_ATTRIBUTE6 => plan_2_train_rec.ATTRIBUTE6
, P_ATTRIBUTE7 => plan_2_train_rec.ATTRIBUTE7
, P_ATTRIBUTE8 => plan_2_train_rec.ATTRIBUTE8
, P_ATTRIBUTE9 => plan_2_train_rec.ATTRIBUTE9
, P_ATTRIBUTE10 => plan_2_train_rec.ATTRIBUTE10
, P_ATTRIBUTE11 => plan_2_train_rec.ATTRIBUTE11
, P_ATTRIBUTE12 => plan_2_train_rec.ATTRIBUTE12
, P_ATTRIBUTE13 => plan_2_train_rec.ATTRIBUTE13
, P_ATTRIBUTE14 => plan_2_train_rec.ATTRIBUTE14
, P_ATTRIBUTE15 => plan_2_train_rec.ATTRIBUTE15
, P_ENABLED_FLAG => 'Y'
, P_MEANING => plan_2_train_rec.MEANING
, P_DESCRIPTION => plan_2_train_rec.DESCRIPTION
, P_START_DATE_ACTIVE => plan_2_train_rec.START_DATE_ACTIVE
, P_END_DATE_ACTIVE => plan_2_train_rec.END_DATE_ACTIVE
);
tab_usr_row_ids.delete;
Select org_unit.organization_id
, org_unit.organization_id || '_' || substr(org_unit_tl.NAME,1,79 - length(org_unit.organization_id)) user_column_name
, org_unit.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, org_bg.ORG_INFORMATION9 legislation_code
from hr_organization_information org_Legal_ent
, hr_all_organization_units org_unit
, hr_organization_information org_bg
, hr_all_organization_units_tl org_unit_tl
Where org_Legal_ent.ORG_INFORMATION_CONTEXT = 'CLASS'
And org_Legal_ent.ORG_INFORMATION1 = 'HR_LEGAL'
and org_Legal_ent.ORGANIZATION_ID = org_unit.ORGANIZATION_ID
And org_bg.ORGANIZATION_ID = org_unit.BUSINESS_GROUP_ID
And org_bg.ORG_INFORMATION_CONTEXT = 'Business Group Information'
And org_bg.ORG_INFORMATION9 = 'ZA'
And org_unit_tl.ORGANIZATION_ID = org_unit.ORGANIZATION_ID
And org_unit_tl.LANGUAGE = userenv('LANG')
And not exists
( Select 1
from PAY_USER_TABLES PUT
, PAY_USER_COLUMNS PUC
, PAY_USER_COLUMNS_TL PUC_TL
Where PUT.USER_TABLE_NAME = 'ZA_WSP_SKILLS_PRIORITIES'
And PUT.legislation_code = 'ZA'
And PUT.USER_TABLE_ID = PUC.USER_TABLE_ID
And PUC_TL.USER_COLUMN_ID = PUC.USER_COLUMN_ID
And PUC_TL.language = userenv('LANG')
And PUC_TL.USER_COLUMN_NAME = org_unit.organization_id || '_' || substr(org_unit_tl.NAME,1,79 - length(org_unit.organization_id))
);
Select PUT.USER_TABLE_ID
into l_user_table_id
From PAY_USER_TABLES PUT
Where PUT.USER_TABLE_NAME = 'ZA_WSP_SKILLS_PRIORITIES'
And PUT.legislation_code = 'ZA';
pay_user_columns_pkg.insert_row (
p_rowid => l_row_id,
p_user_column_id => l_usr_col_id,
p_user_table_id => l_user_table_id,
p_business_group_id => legal_entity_rec.business_group_id,
p_legislation_code => legal_entity_rec.legislation_code,
p_legislation_subgroup => null,
p_user_column_name => legal_entity_rec.user_column_name,
p_formula_id => null ) ;
Select to_date('01-04-'||(p_year-1),'DD-MM-YYYY')
, to_date('31-03-'|| p_year ,'DD-MM-YYYY')
, to_date('01-04-'||(p_year-2),'DD-MM-YYYY')
, to_date('31-03-'||(p_year-1),'DD-MM-YYYY')
INTO
g_plan_year_start_date
, g_plan_year_end_date
, g_trnd_year_start_date
, g_trnd_year_end_date
From Dual;
it will delete existing lookup values for passed year
and create freash lookup values*/
hr_utility.set_location('Calling create_lookup_values' ,40);
, p_del_mode => 'Y' -- if values exists delete and re create
);
select decode(instr(wsp_lookup_type,'ATR'),0,substr(p_lookup_code,1,4),substr(p_lookup_code,1,4)+1)
into wsp_copy_year
from dual;