[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT ihs.start_date ' ||
' ,ihs.end_date ' ||
' ,hps.identifying_address_flag ' ||
' FROM hz_party_sites hps ' ||
' ,igs_pe_hz_pty_sites ihs ' ||
' WHERE ihs.party_site_id(+) = hps.party_site_id ' ||
' AND hps.party_id = :c_party_id ' ||
' AND hps.location_id = :c_location_id ' ||
' AND hps.identifying_address_flag = '||'''Y''';
' SELECT hzl.location_id
,hps.party_site_id
,hzl.last_update_date
,hps.object_version_number
,hzl.object_version_number
,hzl.ROWID
FROM hz_party_sites hps
,hz_locations hzl
WHERE hps.party_id(+) = :c_party_id
AND hzl.location_id(+) = hps.location_id
AND hps.identifying_address_flag(+) = '||'''Y''';
SELECT *
FROM pqp_configuration_values pcv
WHERE pcv.pcv_information_category = c_info_category
AND ((pcv.business_group_id = c_bg_grp_id) OR
(pcv.legislation_code = c_bg_grp_leg_code AND
pcv.business_group_id IS NULL) OR
(pcv.business_group_id IS NULL AND
pcv.legislation_code IS NULL)
);
SELECT hrl.meaning
,hrl.lookup_code
FROM hr_lookups hrl
WHERE hrl.lookup_type = c_lookup_type
AND (Upper(hrl.meaning) = Upper(c_meaning)
OR
Upper(hrl.lookup_code) = Upper(c_meaning)
)
AND Trunc(g_effective_date)
BETWEEN Nvl(hrl.start_date_active,Trunc(g_effective_date))
AND Nvl(hrl.end_date_active, Trunc(g_effective_date));
SELECT hrl.lookup_code ins_code
,hrl.meaning ins_mapping
,irs.lookup_code irs_code
,irs.meaning irs_meaning
FROM hr_lookups hrl,
hr_lookups irs
WHERE hrl.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
AND hrl.enabled_flag = 'Y'
AND irs.lookup_type = 'PER_US_COUNTRY_CODE'
AND irs.enabled_flag = 'Y'
AND irs.lookup_code = substrb(hrl.meaning,-2)
AND ( (c_map_to = 'HR_TO_OSS' AND
hrl.lookup_code = c_country_code)
OR
(c_map_to = 'OSS_TO_HR' AND
irs.lookup_code = c_country_code)
);
SELECT pbg.legislation_code
FROM per_business_groups pbg
WHERE pbg.business_group_id = c_bg_grp_id;
SELECT *
FROM hz_locations hzl
WHERE hzl.location_id = c_location_id;
SELECT *
FROM hz_party_sites hps
WHERE hps.party_id = c_party_id
AND hps.identifying_address_flag = c_primary_flag
AND hps.status = 'A';
SELECT dfc.descriptive_flex_context_code
FROM fnd_descr_flex_contexts dfc
WHERE dfc.application_id = 800
AND dfc.Descriptive_flexfield_name = 'Address Structure'
AND dfc.enabled_flag ='Y';
SELECT hli.status
,hli.pi_steps_exist
FROM hr_legislation_installations hli
WHERE hli.legislation_code = c_leg_code
AND hli.application_short_name = c_app_short_name;
select fpi.product_version
,fpi.status
,fpi.db_status
,fpi.patch_level
,substr(fpi.patch_level,9,1) level_char -- fix for bug 5162947.
from fnd_product_installations fpi
,fnd_application app
where fpi.application_id = app.application_id
and app.application_short_name ='IGS';
ELSIF p_action = 'UPDATE' THEN
Hr_Person_Extra_Info_Api.Update_Person_Extra_Info
(p_validate => p_validate
,p_person_extra_info_id => p_extra_info_rec.person_extra_info_id
,p_object_version_number => p_extra_info_rec.object_version_number
--
,p_pei_information_category => p_extra_info_rec.pei_information_category
,p_pei_information1 => p_extra_info_rec.pei_information1
,p_pei_information2 => p_extra_info_rec.pei_information2
,p_pei_information3 => p_extra_info_rec.pei_information3
,p_pei_information4 => p_extra_info_rec.pei_information4
,p_pei_information5 => p_extra_info_rec.pei_information5
,p_pei_information6 => p_extra_info_rec.pei_information6
,p_pei_information7 => p_extra_info_rec.pei_information7
,p_pei_information8 => p_extra_info_rec.pei_information8
,p_pei_information9 => p_extra_info_rec.pei_information9
,p_pei_information10 => p_extra_info_rec.pei_information10
,p_pei_information11 => p_extra_info_rec.pei_information11
,p_pei_information12 => p_extra_info_rec.pei_information12
,p_pei_information13 => p_extra_info_rec.pei_information13
,p_pei_information14 => p_extra_info_rec.pei_information14
,p_pei_information15 => p_extra_info_rec.pei_information15
,p_pei_information16 => p_extra_info_rec.pei_information16
,p_pei_information17 => p_extra_info_rec.pei_information17
,p_pei_information18 => p_extra_info_rec.pei_information18
,p_pei_information19 => p_extra_info_rec.pei_information19
,p_pei_information20 => p_extra_info_rec.pei_information20
,p_pei_information21 => p_extra_info_rec.pei_information21
,p_pei_information22 => p_extra_info_rec.pei_information22
,p_pei_information23 => p_extra_info_rec.pei_information23
,p_pei_information24 => p_extra_info_rec.pei_information24
,p_pei_information25 => p_extra_info_rec.pei_information25
,p_pei_information26 => p_extra_info_rec.pei_information26
,p_pei_information27 => p_extra_info_rec.pei_information27
,p_pei_information28 => p_extra_info_rec.pei_information28
,p_pei_information29 => p_extra_info_rec.pei_information29
,p_pei_information30 => p_extra_info_rec.pei_information30
-- DF Segments
,p_pei_attribute_category => p_extra_info_rec.pei_attribute_category
,p_pei_attribute1 => p_extra_info_rec.pei_attribute1
,p_pei_attribute2 => p_extra_info_rec.pei_attribute2
,p_pei_attribute3 => p_extra_info_rec.pei_attribute3
,p_pei_attribute4 => p_extra_info_rec.pei_attribute4
,p_pei_attribute5 => p_extra_info_rec.pei_attribute5
,p_pei_attribute6 => p_extra_info_rec.pei_attribute6
,p_pei_attribute7 => p_extra_info_rec.pei_attribute7
,p_pei_attribute8 => p_extra_info_rec.pei_attribute8
,p_pei_attribute9 => p_extra_info_rec.pei_attribute9
,p_pei_attribute10 => p_extra_info_rec.pei_attribute10
,p_pei_attribute11 => p_extra_info_rec.pei_attribute11
,p_pei_attribute12 => p_extra_info_rec.pei_attribute12
,p_pei_attribute13 => p_extra_info_rec.pei_attribute13
,p_pei_attribute14 => p_extra_info_rec.pei_attribute14
,p_pei_attribute15 => p_extra_info_rec.pei_attribute15
,p_pei_attribute16 => p_extra_info_rec.pei_attribute16
,p_pei_attribute17 => p_extra_info_rec.pei_attribute17
,p_pei_attribute18 => p_extra_info_rec.pei_attribute18
,p_pei_attribute19 => p_extra_info_rec.pei_attribute19
,p_pei_attribute20 => p_extra_info_rec.pei_attribute20
);
ELSIF p_action ='DELETE' THEN
Hr_Person_Extra_Info_Api.Delete_Person_Extra_Info
(p_validate => p_validate
,p_person_extra_info_id => p_extra_info_rec.person_extra_info_id
,p_object_version_number => p_extra_info_rec.object_version_number
);
,p_program_update_date => p_analyses_rec.program_update_date
--
,p_attribute_category => p_analyses_rec.attribute_category
,p_attribute1 => p_analyses_rec.attribute1
,p_attribute2 => p_analyses_rec.attribute2
,p_attribute3 => p_analyses_rec.attribute3
,p_attribute4 => p_analyses_rec.attribute4
,p_attribute5 => p_analyses_rec.attribute5
,p_attribute6 => p_analyses_rec.attribute6
,p_attribute7 => p_analyses_rec.attribute7
,p_attribute8 => p_analyses_rec.attribute8
,p_attribute9 => p_analyses_rec.attribute9
,p_attribute10 => p_analyses_rec.attribute10
,p_attribute11 => p_analyses_rec.attribute11
,p_attribute12 => p_analyses_rec.attribute12
,p_attribute13 => p_analyses_rec.attribute13
,p_attribute14 => p_analyses_rec.attribute14
,p_attribute15 => p_analyses_rec.attribute15
,p_attribute16 => p_analyses_rec.attribute16
,p_attribute17 => p_analyses_rec.attribute17
,p_attribute18 => p_analyses_rec.attribute18
,p_attribute19 => p_analyses_rec.attribute19
,p_attribute20 => p_analyses_rec.attribute20
--
,p_segment1 => p_analysis_criteria_rec.segment1
,p_segment2 => p_analysis_criteria_rec.segment2
,p_segment3 => p_analysis_criteria_rec.segment3
,p_segment4 => p_analysis_criteria_rec.segment4
,p_segment5 => p_analysis_criteria_rec.segment5
,p_segment6 => p_analysis_criteria_rec.segment6
,p_segment7 => p_analysis_criteria_rec.segment7
,p_segment8 => p_analysis_criteria_rec.segment8
,p_segment9 => p_analysis_criteria_rec.segment9
,p_segment10 => p_analysis_criteria_rec.segment10
,p_segment11 => p_analysis_criteria_rec.segment11
,p_segment12 => p_analysis_criteria_rec.segment12
,p_segment13 => p_analysis_criteria_rec.segment13
,p_segment14 => p_analysis_criteria_rec.segment14
,p_segment15 => p_analysis_criteria_rec.segment15
,p_segment16 => p_analysis_criteria_rec.segment16
,p_segment17 => p_analysis_criteria_rec.segment17
,p_segment18 => p_analysis_criteria_rec.segment18
,p_segment19 => p_analysis_criteria_rec.segment19
,p_segment20 => p_analysis_criteria_rec.segment20
,p_segment21 => p_analysis_criteria_rec.segment21
,p_segment22 => p_analysis_criteria_rec.segment22
,p_segment23 => p_analysis_criteria_rec.segment23
,p_segment24 => p_analysis_criteria_rec.segment24
,p_segment25 => p_analysis_criteria_rec.segment25
,p_segment26 => p_analysis_criteria_rec.segment26
,p_segment27 => p_analysis_criteria_rec.segment27
,p_segment28 => p_analysis_criteria_rec.segment28
,p_segment29 => p_analysis_criteria_rec.segment29
,p_segment30 => p_analysis_criteria_rec.segment30
--
,p_concat_segments => Null
,p_analysis_criteria_id => p_analysis_criteria_rec.analysis_criteria_id
,p_person_analysis_id => p_analyses_rec.person_analysis_id
,p_pea_object_version_number => p_analyses_rec.object_version_number
);
ELSIF p_action = 'UPDATE' THEN
Hr_SIT_Api.Update_SIT
(p_validate => p_validate
,p_person_analysis_id => p_analyses_rec.person_analysis_id
,p_pea_object_version_number => p_analyses_rec.object_version_number
,p_comments => NULL
,p_date_from => p_analyses_rec.date_from
,p_date_to => p_analyses_rec.date_to
,p_concat_segments => Null
--
,p_analysis_criteria_id => p_analysis_criteria_rec.analysis_criteria_id
--
,p_request_id => p_analyses_rec.request_id
,p_program_application_id => p_analyses_rec.program_application_id
,p_program_id => p_analyses_rec.program_id
,p_program_update_date => p_analyses_rec.program_update_date
--
,p_attribute_category => p_analyses_rec.attribute_category
,p_attribute1 => p_analyses_rec.attribute1
,p_attribute2 => p_analyses_rec.attribute2
,p_attribute3 => p_analyses_rec.attribute3
,p_attribute4 => p_analyses_rec.attribute4
,p_attribute5 => p_analyses_rec.attribute5
,p_attribute6 => p_analyses_rec.attribute6
,p_attribute7 => p_analyses_rec.attribute7
,p_attribute8 => p_analyses_rec.attribute8
,p_attribute9 => p_analyses_rec.attribute9
,p_attribute10 => p_analyses_rec.attribute10
,p_attribute11 => p_analyses_rec.attribute11
,p_attribute12 => p_analyses_rec.attribute12
,p_attribute13 => p_analyses_rec.attribute13
,p_attribute14 => p_analyses_rec.attribute14
,p_attribute15 => p_analyses_rec.attribute15
,p_attribute16 => p_analyses_rec.attribute16
,p_attribute17 => p_analyses_rec.attribute17
,p_attribute18 => p_analyses_rec.attribute18
,p_attribute19 => p_analyses_rec.attribute19
,p_attribute20 => p_analyses_rec.attribute20
,p_segment1 => p_analysis_criteria_rec.segment1
,p_segment2 => p_analysis_criteria_rec.segment2
,p_segment3 => p_analysis_criteria_rec.segment3
,p_segment4 => p_analysis_criteria_rec.segment4
,p_segment5 => p_analysis_criteria_rec.segment5
,p_segment6 => p_analysis_criteria_rec.segment6
,p_segment7 => p_analysis_criteria_rec.segment7
,p_segment8 => p_analysis_criteria_rec.segment8
,p_segment9 => p_analysis_criteria_rec.segment9
,p_segment10 => p_analysis_criteria_rec.segment10
,p_segment11 => p_analysis_criteria_rec.segment11
,p_segment12 => p_analysis_criteria_rec.segment12
,p_segment13 => p_analysis_criteria_rec.segment13
,p_segment14 => p_analysis_criteria_rec.segment14
,p_segment15 => p_analysis_criteria_rec.segment15
,p_segment16 => p_analysis_criteria_rec.segment16
,p_segment17 => p_analysis_criteria_rec.segment17
,p_segment18 => p_analysis_criteria_rec.segment18
,p_segment19 => p_analysis_criteria_rec.segment19
,p_segment20 => p_analysis_criteria_rec.segment20
,p_segment21 => p_analysis_criteria_rec.segment21
,p_segment22 => p_analysis_criteria_rec.segment22
,p_segment23 => p_analysis_criteria_rec.segment23
,p_segment24 => p_analysis_criteria_rec.segment24
,p_segment25 => p_analysis_criteria_rec.segment25
,p_segment26 => p_analysis_criteria_rec.segment26
,p_segment27 => p_analysis_criteria_rec.segment27
,p_segment28 => p_analysis_criteria_rec.segment28
,p_segment29 => p_analysis_criteria_rec.segment29
,p_segment30 => p_analysis_criteria_rec.segment30
);
ELSIF p_action ='DELETE' THEN
Hr_SIT_Api.Delete_SIT
(p_validate => p_validate
,p_person_analysis_id => p_analyses_rec.person_analysis_id
,p_pea_object_version_number => p_analyses_rec.object_version_number
);
ELSIF p_action = 'UPDATE' THEN
Hr_Assignment_Extra_Info_Api.Update_Assignment_Extra_Info
(p_validate => p_validate
,p_Assignment_extra_info_id => p_extra_info_rec.Assignment_extra_info_id
,p_object_version_number => p_extra_info_rec.Object_Version_Number
-- DDF Segments
,p_aei_information_category => p_extra_info_rec.aei_information_category
,p_aei_information1 => p_extra_info_rec.aei_information1
,p_aei_information2 => p_extra_info_rec.aei_information2
,p_aei_information3 => p_extra_info_rec.aei_information3
,p_aei_information4 => p_extra_info_rec.aei_information4
,p_aei_information5 => p_extra_info_rec.aei_information5
,p_aei_information6 => p_extra_info_rec.aei_information6
,p_aei_information7 => p_extra_info_rec.aei_information7
,p_aei_information8 => p_extra_info_rec.aei_information8
,p_aei_information9 => p_extra_info_rec.aei_information9
,p_aei_information10 => p_extra_info_rec.aei_information10
,p_aei_information11 => p_extra_info_rec.aei_information11
,p_aei_information12 => p_extra_info_rec.aei_information12
,p_aei_information13 => p_extra_info_rec.aei_information13
,p_aei_information14 => p_extra_info_rec.aei_information14
,p_aei_information15 => p_extra_info_rec.aei_information15
,p_aei_information16 => p_extra_info_rec.aei_information16
,p_aei_information17 => p_extra_info_rec.aei_information17
,p_aei_information18 => p_extra_info_rec.aei_information18
,p_aei_information19 => p_extra_info_rec.aei_information19
,p_aei_information20 => p_extra_info_rec.aei_information20
,p_aei_information21 => p_extra_info_rec.aei_information21
,p_aei_information22 => p_extra_info_rec.aei_information22
,p_aei_information23 => p_extra_info_rec.aei_information23
,p_aei_information24 => p_extra_info_rec.aei_information24
,p_aei_information25 => p_extra_info_rec.aei_information25
,p_aei_information26 => p_extra_info_rec.aei_information26
,p_aei_information27 => p_extra_info_rec.aei_information27
,p_aei_information28 => p_extra_info_rec.aei_information28
,p_aei_information29 => p_extra_info_rec.aei_information29
,p_aei_information30 => p_extra_info_rec.aei_information30
-- DF segments
,p_aei_attribute_category => p_extra_info_rec.aei_attribute_category
,p_aei_attribute1 => p_extra_info_rec.aei_attribute1
,p_aei_attribute2 => p_extra_info_rec.aei_attribute2
,p_aei_attribute3 => p_extra_info_rec.aei_attribute3
,p_aei_attribute4 => p_extra_info_rec.aei_attribute4
,p_aei_attribute5 => p_extra_info_rec.aei_attribute5
,p_aei_attribute6 => p_extra_info_rec.aei_attribute6
,p_aei_attribute7 => p_extra_info_rec.aei_attribute7
,p_aei_attribute8 => p_extra_info_rec.aei_attribute8
,p_aei_attribute9 => p_extra_info_rec.aei_attribute9
,p_aei_attribute10 => p_extra_info_rec.aei_attribute10
,p_aei_attribute11 => p_extra_info_rec.aei_attribute11
,p_aei_attribute12 => p_extra_info_rec.aei_attribute12
,p_aei_attribute13 => p_extra_info_rec.aei_attribute13
,p_aei_attribute14 => p_extra_info_rec.aei_attribute14
,p_aei_attribute15 => p_extra_info_rec.aei_attribute15
,p_aei_attribute16 => p_extra_info_rec.aei_attribute16
,p_aei_attribute17 => p_extra_info_rec.aei_attribute17
,p_aei_attribute18 => p_extra_info_rec.aei_attribute18
,p_aei_attribute19 => p_extra_info_rec.aei_attribute19
,p_aei_attribute20 => p_extra_info_rec.aei_attribute20
);
ELSIF p_action ='DELETE' THEN
Hr_Assignment_Extra_Info_Api.Delete_Assignment_Extra_Info
(p_validate => p_validate
,p_assignment_extra_info_id => p_extra_info_rec.Assignment_extra_info_id
,p_object_version_number => p_extra_info_rec.object_version_number
);
l_Update_OSS_rec Boolean;
l_Insert_OSS_rec Boolean;
l_Update_OSS_Rec := FALSE; l_Insert_OSS_Rec := FALSE;
SQLstmt := ' SELECT pap.rowid '||
' ,pap.passport_id '||
' ,pap.passport_cntry_code '||
' ,pap.passport_number '||
' ,pap.passport_expiry_date '||
' ,pap.attribute_category '||
' ,pap.attribute1 '||
' ,pap.attribute2 '||
' ,pap.attribute3 '||
' ,pap.attribute4 '||
' ,pap.attribute5 '||
' ,pap.attribute6 '||
' ,pap.attribute7 '||
' ,pap.attribute8 '||
' ,pap.attribute9 '||
' ,pap.attribute10 '||
' ,pap.attribute11 '||
' ,pap.attribute12 '||
' ,pap.attribute13 '||
' ,pap.attribute14 '||
' ,pap.attribute15 '||
' ,pap.attribute16 '||
' ,pap.attribute17 '||
' ,pap.attribute18 '||
' ,pap.attribute19 '||
' ,pap.attribute20 '||
' FROM igs_pe_passport pap '||
' WHERE pap.person_id = :c_party_id '||
' AND pap.passport_number = :c_passport_number ';
IF p_action = 'UPDATE' THEN
IF (p_pei_info_rec_new.pei_information5 =
p_pei_info_rec_old.pei_information5 AND
p_pei_info_rec_new.pei_information6 =
p_pei_info_rec_old.pei_information6 AND
Fnd_Date.Canonical_To_Date
(p_pei_info_rec_new.pei_information8) =
Fnd_Date.Canonical_To_Date
(p_pei_info_rec_old.pei_information8))
THEN
RETURN;
ELSIF p_action ='INSERT' THEN
IF (p_pei_info_rec_new.pei_information8 is Null) THEN
l_msg_data := 'Passport Expiry Date is required for a Student.';
IF p_action = 'INSERT' THEN
l_oss_pp_rec.passport_cntry_code := p_pei_info_rec_new.pei_information5;
l_Insert_OSS_Rec := TRUE;
ELSIF p_action = 'UPDATE' THEN
Hr_Utility.set_location('..p_action :'||p_action, 10);
l_update_OSS_rec := TRUE;
l_Insert_OSS_Rec := TRUE;
l_Insert_OSS_Rec := TRUE;
l_update_OSS_rec := TRUE;
IF l_Insert_OSS_Rec THEN
Hr_Utility.set_location('Calling Dynamic PL/SQL Block: IGS_PE_Visapass_Pub.Create_Passport', 20);
IF l_update_OSS_rec THEN
Hr_Utility.set_location('Calling Dynamic PL/SQL Block: Igs_Pe_Passport_Pkg.Update_Row', 21);
Igs_Pe_Visapass_Pub.Update_Passport
(p_api_version => 1.0
,p_init_msg_list => Fnd_Api.G_TRUE
,p_passport_rec => l_passport_rec
,x_return_status => :6
,x_msg_count => :7
,x_msg_data => :8
);
l_Update_OSS_rec Boolean;
l_Insert_OSS_rec Boolean;
l_Update_OSS_Rec := FALSE; l_Insert_OSS_Rec := FALSE;
SQLstmt:='SELECT '||
' rowid '||
' ,visa_id '||
' ,visa_type '||
' ,visa_number '||
' ,visa_issue_date '||
' ,visa_expiry_date '||
' ,visa_category '||
' ,visa_issuing_post '||
' ,passport_id '||
' ,agent_org_unit_cd '||
' ,agent_person_id '||
' ,agent_contact_name '||
' ,visa_issuing_country '||
' ,attribute_category '||
' ,attribute1 '||
' ,attribute2 '||
' ,attribute3 '||
' ,attribute4 '||
' ,attribute5 '||
' ,attribute6 '||
' ,attribute7 '||
' ,attribute8 '||
' ,attribute9 '||
' ,attribute10 '||
' ,attribute11 '||
' ,attribute12 '||
' ,attribute13 '||
' ,attribute14 '||
' ,attribute15 '||
' ,attribute16 '||
' ,attribute17 '||
' ,attribute18 '||
' ,attribute19 '||
' ,attribute20 '||
' FROM igs_pe_visa '||
'WHERE person_id = :c_party_id '||
' AND visa_number = :c_visa_number ';
IF p_action = 'UPDATE' THEN
IF (p_pei_info_rec_new.pei_information5 =
p_pei_info_rec_old.pei_information5 AND
p_pei_info_rec_new.pei_information6 =
p_pei_info_rec_old.pei_information6 AND
Fnd_Date.Canonical_To_Date
(p_pei_info_rec_new.pei_information7) =
Fnd_Date.Canonical_To_Date
(p_pei_info_rec_old.pei_information7) AND
Fnd_Date.Canonical_To_Date
(nvl(p_pei_info_rec_new.pei_information8,l_dft_date)) =
Fnd_Date.Canonical_To_Date
(nvl(p_pei_info_rec_old.pei_information8,l_dft_date))
)
THEN
RETURN;
l_msg_data := 'For a student you cannot update the unique combination of '||
'Visa Number, Visa Type and Issue Date';
IF p_action = 'INSERT' THEN
l_oss_visa_rec.visa_type := p_pei_info_rec_new.pei_information5;
l_Insert_OSS_Rec := TRUE;
ELSIF p_action = 'UPDATE' THEN
IF (Trim(p_pei_info_rec_old.pei_information6) =
Trim(p_pei_info_rec_new.pei_information6)) THEN
OPEN csr_Visa FOR SQLstmt
Using p_party_id
,p_pei_info_rec_old.pei_information6;
l_update_OSS_rec := TRUE;
l_update_OSS_rec := FALSE;
l_insert_OSS_rec := TRUE;
l_Insert_OSS_Rec := TRUE;
l_Update_OSS_Rec := TRUE;
IF l_insert_OSS_rec THEN
Hr_Utility.set_location('..visa_type: '||l_oss_visa_rec.visa_type, 20);
IF l_update_OSS_rec THEN
Hr_Utility.set_location('..visa_type: '||l_oss_visa_rec.visa_type, 21);
Igs_Pe_Visapass_Pub.Update_Visa
(p_api_version => 1.0
,p_init_msg_list => Fnd_Api.G_TRUE
,p_visa_rec => l_visa_rec
,x_return_status => :7
,x_msg_count => :8
,x_msg_data => :9
);
l_Update_OSS_rec Boolean;
l_Insert_OSS_rec Boolean;
l_Update_OSS_Rec := FALSE; l_Insert_OSS_Rec := FALSE;
SQLstmt := ' SELECT row_id
,port_of_entry
,port_of_entry_m
,cntry_entry_form_num
,visa_id
,visa_type
,visa_number
,visa_issue_date
,visa_expiry_date
,visa_category
,visa_issuing_post
,passport_id
,visit_start_date
,visit_end_date
FROM igs_pe_visit_histry_v
WHERE person_id = :c_party_id
AND visa_number = :c_visa_number';
IF p_action = 'UPDATE' THEN
IF (nvl(p_pei_info_rec_new.pei_information11,'$$') =
nvl(p_pei_info_rec_old.pei_information11,'$$') AND
Fnd_Date.Canonical_To_Date
(p_pei_info_rec_new.pei_information7) =
Fnd_Date.Canonical_To_Date
(p_pei_info_rec_old.pei_information7) AND
Fnd_Date.Canonical_To_Date
(nvl(p_pei_info_rec_new.pei_information8,l_dft_date)) =
Fnd_Date.Canonical_To_Date
(nvl(p_pei_info_rec_old.pei_information8,l_dft_date)) AND
nvl(p_pei_info_rec_new.pei_information12,'$$') =
nvl(p_pei_info_rec_old.pei_information12,'$$') AND
nvl(p_pei_info_rec_new.pei_information13,'$$') =
nvl(p_pei_info_rec_old.pei_information13,'$$')
)
THEN
RETURN;
ELSIF p_action = 'INSERT' THEN
IF(p_pei_info_rec_new.pei_information12 Is Null or
p_pei_info_rec_new.pei_information13 Is Null) THEN
l_msg_data := 'Port Of Entry and Entry Number are required for student.';
IF p_action = 'INSERT' THEN
Hr_Utility.set_location(' p_action: '||p_action, 10);
l_Insert_OSS_Rec := TRUE;
ELSIF p_action = 'UPDATE' THEN
Hr_Utility.set_location(' p_action: '||p_action, 10);
l_update_OSS_rec := TRUE;
l_update_OSS_rec := FALSE;
l_insert_OSS_rec := TRUE;
l_Insert_OSS_Rec := TRUE;
l_Update_OSS_Rec := TRUE;
SQLstmt:='SELECT visa_id
FROM igs_pe_visa
WHERE person_id = :c_party_id
AND visa_number = :c_visa_number';
IF l_insert_OSS_rec THEN
l_oss_vvhist_rec_new.visa_id := l_new_visa_id;
IF l_update_OSS_rec THEN
l_oss_vvhist_rec_new.visa_id := l_new_visa_id;
Hr_Utility.set_location(' Calling: IGS_PE_VisaPass_Pub.Update_VisitHistry', 21);
Igs_Pe_Visapass_Pub.Update_VisitHistry
(p_api_version => 1.0
,p_init_msg_list => Fnd_Api.G_TRUE
,p_visit_hstry_rec => l_visit_hstry_rec
,x_return_status => :6
,x_msg_count => :7
,x_msg_data => :8
);
SELECT pei.pei_information5
,pei.pei_information6
,pei.pei_information7
,pei.pei_information8
,pei.object_version_number
,pei.person_extra_info_id
FROM per_people_extra_info pei
WHERE pei.person_id = c_person_id
AND pei.information_type ='PER_US_PASSPORT_DETAILS'
AND pei.pei_information5 = c_pp_cntry_code
AND pei.pei_information6 = c_pp_number ;
SQLstmt := ' SELECT pap.rowid '||
' ,pap.passport_id '||
' ,pap.passport_cntry_code '||
' ,pap.passport_number '||
' ,pap.passport_expiry_date '||
' ,pap.attribute_category '||
' ,pap.attribute1 '||
' ,pap.attribute2 '||
' ,pap.attribute3 '||
' ,pap.attribute4 '||
' ,pap.attribute5 '||
' ,pap.attribute6 '||
' ,pap.attribute7 '||
' ,pap.attribute8 '||
' ,pap.attribute9 '||
' ,pap.attribute10 '||
' ,pap.attribute11 '||
' ,pap.attribute12 '||
' ,pap.attribute13 '||
' ,pap.attribute14 '||
' ,pap.attribute15 '||
' ,pap.attribute16 '||
' ,pap.attribute17 '||
' ,pap.attribute18 '||
' ,pap.attribute19 '||
' ,pap.attribute20 '||
' FROM igs_pe_passport pap '||
' WHERE pap.person_id = :c_party_id ';
l_action := 'UPDATE';
SELECT pei.pei_information5
,pei.pei_information6
,pei.pei_information7
,pei.pei_information8
,pei.pei_information9
,pei.pei_information10
,pei.object_version_number
,pei.person_extra_info_id
FROM per_people_extra_info pei
WHERE pei.person_id = c_person_id
AND pei.information_type ='PER_US_VISA_DETAILS'
AND pei.pei_information5 = c_visa_type
AND pei.pei_information6 = c_visa_number;
SQLstmt:='SELECT '||
' rowid '||
' ,visa_id '||
' ,visa_type '||
' ,visa_number '||
' ,visa_issue_date '||
' ,visa_expiry_date '||
' ,visa_category '||
' ,visa_issuing_post '||
' ,passport_id '||
' ,agent_org_unit_cd '||
' ,agent_person_id '||
' ,agent_contact_name '||
' ,visa_issuing_country '||
' ,attribute_category '||
' ,attribute1 '||
' ,attribute2 '||
' ,attribute3 '||
' ,attribute4 '||
' ,attribute5 '||
' ,attribute6 '||
' ,attribute7 '||
' ,attribute8 '||
' ,attribute9 '||
' ,attribute10 '||
' ,attribute11 '||
' ,attribute12 '||
' ,attribute13 '||
' ,attribute14 '||
' ,attribute15 '||
' ,attribute16 '||
' ,attribute17 '||
' ,attribute18 '||
' ,attribute19 '||
' ,attribute20 '||
' FROM igs_pe_visa '||
'WHERE person_id = :c_party_id ';
l_action := 'UPDATE';
SELECT pei.pei_information5
,pei.pei_information11
,pei.pei_information7
,pei.pei_information8
,pei.pei_information9
,pei.pei_information10
,pei.object_version_number
,pei.person_extra_info_id
FROM per_people_extra_info pei
WHERE pei.person_id = c_person_id
AND pei.information_type = 'PER_US_VISIT_HISTORY'
AND pei.pei_information11 = c_visa_number;
SQLstmt:=' SELECT row_id '||
' ,port_of_entry '||
' ,port_of_entry_m '||
' ,cntry_entry_form_num '||
' ,visa_id '||
' ,visa_type '||
' ,visa_number '||
' ,visa_issue_date '||
' ,visa_expiry_date '||
' ,visa_category '||
' ,visa_issuing_post '||
' ,passport_id '||
' ,visit_start_date '||
' ,visit_end_date '||
' FROM igs_pe_visit_histry_v '||
' WHERE person_id = :c_party_id ';
l_action := 'UPDATE';
SELECT pei.pei_information1
,pei.pei_information2
,pei.pei_information3
,pei.pei_information4
,pei.pei_information5
,pei.object_version_number
,pei.person_extra_info_id
FROM per_people_extra_info pei
WHERE pei.person_id = c_person_id
AND pei.information_type = c_information_type;
SELECT hzp.party_number
FROM hz_parties hzp
WHERE hzp.party_id = c_party_id;
' SELECT igp.person_id_type ' ||
' ,igp.api_person_id ' ||
' ,igp.person_number ' ||
' ,ipt.system_type ' ||
' FROM igs_pe_person_v igp ' ||
' ,igs_pe_typ_instances_all pti ' ||
' ,igs_pe_person_types ipt ' ||
' WHERE igp.person_id = :c_party_id ' ||
' AND pti.person_type_code = ipt.person_type_code ' ||
' AND pti.person_id = igp.person_id ' ||
' AND ipt.system_type IN ('||'''STUDENT'''||',' ||
'''FACULTY'''||','||'''OTHER''' ||')' ;
,p_action => 'UPDATE'
,p_extra_info_rec => l_person_extra_info_rec
);
ELSIF p_action ='UPDATE' THEN
Hr_Utility.set_location('..Updating Primary Address: '||p_HR_Address_Rec.Style, 6);
Hr_Person_Address_Api.Update_Person_Address
(p_validate => Nvl(p_validate,FALSE)
,p_effective_date => p_effective_date
,p_validate_county => Nvl(p_validate_county,TRUE)
,p_address_id => p_HR_Address_Rec.address_id
,p_object_version_number => p_HR_Address_Rec.object_version_number
,p_date_from => p_HR_Address_Rec.date_from
,p_date_to => p_HR_Address_Rec.date_to
,p_primary_flag => p_HR_Address_Rec.primary_flag
,p_address_type => p_HR_Address_Rec.address_type
,p_comments => p_HR_Address_Rec.comments -- NULL, By Dbansal
,p_address_line1 => p_HR_Address_Rec.address_line1
,p_address_line2 => p_HR_Address_Rec.address_line2
,p_address_line3 => p_HR_Address_Rec.address_line3
,p_town_or_city => p_HR_Address_Rec.town_or_city
,p_region_1 => p_HR_Address_Rec.region_1
,p_region_2 => p_HR_Address_Rec.region_2
,p_region_3 => p_HR_Address_Rec.region_3
,p_postal_code => p_HR_Address_Rec.postal_code
,p_country => p_HR_Address_Rec.country
,p_telephone_number_1 => p_HR_Address_Rec.telephone_number_1
,p_telephone_number_2 => p_HR_Address_Rec.telephone_number_2
,p_telephone_number_3 => p_HR_Address_Rec.telephone_number_3
,p_addr_attribute_category => p_HR_Address_Rec.addr_attribute_category
,p_addr_attribute1 => p_HR_Address_Rec.addr_attribute1
,p_addr_attribute2 => p_HR_Address_Rec.addr_attribute2
,p_addr_attribute3 => p_HR_Address_Rec.addr_attribute3
,p_addr_attribute4 => p_HR_Address_Rec.addr_attribute4
,p_addr_attribute5 => p_HR_Address_Rec.addr_attribute5
,p_addr_attribute6 => p_HR_Address_Rec.addr_attribute6
,p_addr_attribute7 => p_HR_Address_Rec.addr_attribute7
,p_addr_attribute8 => p_HR_Address_Rec.addr_attribute8
,p_addr_attribute9 => p_HR_Address_Rec.addr_attribute9
,p_addr_attribute10 => p_HR_Address_Rec.addr_attribute10
,p_addr_attribute11 => p_HR_Address_Rec.addr_attribute11
,p_addr_attribute12 => p_HR_Address_Rec.addr_attribute12
,p_addr_attribute13 => p_HR_Address_Rec.addr_attribute13
,p_addr_attribute14 => p_HR_Address_Rec.addr_attribute14
,p_addr_attribute15 => p_HR_Address_Rec.addr_attribute15
,p_addr_attribute16 => p_HR_Address_Rec.addr_attribute16
,p_addr_attribute17 => p_HR_Address_Rec.addr_attribute17
,p_addr_attribute18 => p_HR_Address_Rec.addr_attribute18
,p_addr_attribute19 => p_HR_Address_Rec.addr_attribute19
,p_addr_attribute20 => p_HR_Address_Rec.addr_attribute20
,p_add_information13 => p_HR_Address_Rec.add_information13
,p_add_information14 => p_HR_Address_Rec.add_information14
,p_add_information15 => p_HR_Address_Rec.add_information15
,p_add_information16 => p_HR_Address_Rec.add_information16
,p_add_information17 => p_HR_Address_Rec.add_information17
,p_add_information18 => p_HR_Address_Rec.add_information18
,p_add_information19 => p_HR_Address_Rec.add_information19
,p_add_information20 => p_HR_Address_Rec.add_information20
,p_party_id => p_HR_Address_Rec.party_id
);
PROCEDURE Update_Address_TCA_To_HR
(p_validate IN Boolean
,p_effective_date IN Date
,p_party_id IN Number
,p_business_group_id IN Number
,p_party_site_id IN Number
,p_style IN Varchar2
,p_location_id IN Number
,p_pradd_ovlapval_override IN Boolean
,p_validate_county IN Boolean
,p_primary_flag IN Varchar2
,p_address_type IN Varchar2
,p_overide_TCA_Mapping IN Varchar2
--,p_HZ_Location_Rec IN Hz_Location_V2pub.Location_Rec_Type
-- Out Variable from HR
,p_HR_object_version_number IN OUT NOCOPY Number
) AS
l_proc_name Varchar2(150);
END Update_Address_TCA_To_HR;
SELECT fcu.column_seq_num
,fcu.application_column_name
,fcu.end_user_column_name
,fcu.enabled_flag
,fcu.required_flag
,fvs.maximum_size
,fvs.uppercase_only_flag
,fvs.maximum_value
,fvs.minimum_value
,fvs.alphanumeric_allowed_flag
,fvs.validation_type
FROM fnd_descr_flex_column_usages fcu,
fnd_flex_value_sets fvs,
fnd_flex_validation_tables fvt
WHERE fcu.descriptive_flexfield_name = 'Address Structure'
AND fcu.descriptive_flex_context_code = c_context_code
AND fcu.application_column_name = c_column_name
AND fcu.application_id = 800
AND fcu.enabled_flag = 'Y'
AND fvs.flex_value_set_id(+) = fcu.flex_value_set_id
AND fvt.flex_value_set_id(+) = fvs.flex_value_set_id
ORDER BY fcu.column_seq_num;
SELECT NULL
FROM pay_us_city_names
WHERE (c_leg_code = 'CA'AND
state_code = 70)
OR
(c_leg_code ='US' AND
state_code < 52);
SELECT hzl.country
FROM hz_party_sites hps,
hz_locations hzl
WHERE hps.location_id = hzl.location_id
AND hps.status = 'A'
AND hps.party_id = c_party_id
AND hps.identifying_address_flag = c_primary_flag;
SELECT hcp.phone_country_code
,hcp.phone_area_code
,hcp.phone_number
,hcp.phone_extension
,hcp.primary_flag
,hcp.phone_line_type
FROM hz_contact_points hcp
WHERE hcp.contact_point_type = 'PHONE'
AND hcp.status = 'A'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcp.owner_table_id = c_party_id;
SELECT concatenated_segment_delimiter
FROM fnd_descriptive_flexs
WHERE descriptive_flexfield_name = 'Address Structure'
AND application_table_name = 'PER_ADDRESSES'
AND application_id = 800;
SELECT fcu.column_seq_num
,fcu.application_column_name
,fcu.end_user_column_name
FROM fnd_descr_flex_column_usages fcu
WHERE fcu.descriptive_flexfield_name = 'Address Structure'
AND fcu.descriptive_flex_context_code = c_context
AND fcu.application_id = 800
AND fcu.enabled_flag = 'Y'
ORDER BY fcu.column_seq_num;
SELECT effective_date FROM fnd_sessions
WHERE session_id = (SELECT Userenv('SESSIONID')
FROM dual);
,p_last_update_date IN OUT NOCOPY Date
,p_party_site_ovn IN OUT NOCOPY Number
,p_location_ovn IN OUT NOCOPY Number
,p_rowid IN OUT NOCOPY Varchar2
-- Out Variables
,p_return_status OUT NOCOPY Varchar2
,p_msg_data OUT NOCOPY Varchar2
) AS
l_proc_name CONSTANT Varchar2(150):= g_pkg ||'Create_Address_HR_To_TCA';
Hr_Utility.set_location('..Call OSS Package : Igs_Pe_Person_Addr_Pkg.Insert_Row', 5);
'Igs_Pe_Person_Addr_Pkg.Insert_Row '||
'(p_action => :p_action '||
',p_party_type => :p_party_type '||
',p_party_id => :p_party_Id '||
',p_status => :p_status '||
',p_start_dt => :p_date_from '||
',p_end_dt => :p_date_to '||
',p_country => :p_country '||
',p_address_style => :p_address_style '||
',p_addr_line_1 => :p_Address1 '||
',p_addr_line_2 => :p_Address2 '||
',p_addr_line_3 => :p_Address3 '||
',p_addr_line_4 => :p_Address4 '||
',p_date_last_verified => :p_effective_date '||
',p_correspondence => :p_correspondence '||
',p_city => :p_city '||
',p_state => :p_state '||
',p_province => :p_province '||
',p_county => :p_county '||
',p_postal_code => :p_postal_code '||
',p_address_lines_phonetic => :p_Address_Lines_Phonetic '||
',p_delivery_point_code => :p_Delivery_Point_Code '||
',p_other_details_1 => :p_other_details_1 '||
',p_other_details_2 => :p_other_details_2 '||
',p_other_details_3 => :p_other_details_3 '||
-- In Out
',p_party_site_id => :p_party_site_id '||
',p_last_update_date => :p_last_update_date '||
',p_party_site_ovn => :p_party_site_ovn '||
',p_location_ovn => :p_location_ovn '||
-- Out
',p_rowid => :p_rowid '||
',p_location_id => :p_location_id '||
',l_return_status => :p_return_status '||
',l_msg_data => :p_msg_data '||
' ); '||
,IN OUT p_last_update_date
,IN OUT p_party_site_ovn
,IN OUT p_location_ovn
,IN OUT p_rowid
,IN OUT p_location_id
-- Out
,OUT p_return_status
,OUT p_msg_data;
PROCEDURE Update_Address_HR_To_TCA
(p_business_group_id IN Number
,p_person_id IN Number
,p_party_id IN Number
,p_address_id IN Number
,p_effective_date IN Date
,p_per_addr_rec_new IN per_addresses%ROWTYPE
,p_per_addr_rec_old IN per_addresses%ROWTYPE
-- TCA
,p_party_type IN Varchar2
,p_action IN Varchar2
,p_status IN Varchar2
-- In Out Variables
,p_location_id IN OUT NOCOPY Number
,p_party_site_id IN OUT NOCOPY Number
,p_last_update_date IN OUT NOCOPY Date
,p_party_site_ovn IN OUT NOCOPY Number
,p_location_ovn IN OUT NOCOPY Number
,p_rowid IN OUT NOCOPY Varchar2
-- Out Variables
,p_return_status OUT NOCOPY Varchar2
,p_msg_data OUT NOCOPY Varchar2
) AS
-- Cursor to get the current Primary Location
CURSOR csr_hz_loc(c_hz_location_id IN Number) IS
SELECT hzl.*
FROM hz_locations hzl
WHERE hzl.location_id = c_hz_location_id;
l_proc_name CONSTANT Varchar2(150) := g_pkg ||'Update_Address_HR_To_TCA';
l_update_pri_add Boolean;
,p_last_update_date
,p_party_site_ovn
,p_location_ovn
,p_rowid;
l_update_pri_add := TRUE;
p_last_update_date := NULL;
Hr_Utility.set_location('..Calling OSS API : Igs_Pe_Person_Addr_Pkg.Update_Row',8);
IF l_update_pri_add THEN
PLSQL_Block :=
'BEGIN ' ||
'Igs_Pe_Person_Addr_Pkg.Update_Row ' ||
'(p_action => :p_action ' ||
',p_party_type => :p_party_type ' ||
',p_party_id => :p_party_id ' ||
',p_status => :p_status ' ||
',p_start_dt => :p_Date_From ' ||
',p_end_dt => :p_Date_To ' ||
',p_country => :p_Country ' ||
',p_address_style => :p_hz_address_style ' ||
',p_addr_line_1 => :p_Address1 ' ||
',p_addr_line_2 => :p_Address2 ' ||
',p_addr_line_3 => :p_Address3 ' ||
',p_addr_line_4 => :p_Address4 ' ||
',p_date_last_verified => :p_effective_date ' ||
',p_correspondence => :p_correspondence ' ||
',p_city => :p_City ' ||
',p_state => :p_State ' ||
',p_province => :p_Province ' ||
',p_county => :p_County ' ||
',p_postal_code => :p_Postal_Code ' ||
',p_address_lines_phonetic => :p_Address_Lines_Phonetic ' ||
',p_delivery_point_code => :p_Delivery_Point_Code ' ||
',p_other_details_1 => :p_other_details_1 ' ||
',p_other_details_2 => :p_other_details_2 ' ||
',p_other_details_3 => :p_other_details_3 ' ||
-- In Out
',p_party_site_id => :p_party_site_id ' ||
',p_last_update_date => :p_last_update_date ' ||
',p_party_site_ovn => :p_party_site_ovn ' ||
',p_location_ovn => :p_location_ovn ' ||
',p_rowid => :p_rowid ' ||
',p_location_id => :p_location_id ' ||
-- Out
',l_return_status => :p_return_status ' ||
',l_msg_data => :p_msg_data ' ||
'); ' ||
,IN OUT p_last_update_date
,IN OUT p_party_site_ovn
,IN OUT p_location_ovn
,IN OUT p_rowid
,IN OUT p_location_id
-- Out
,OUT p_return_status
,OUT p_msg_data;
Hr_Utility.set_location('..Call OSS Package : Igs_Pe_Person_Addr_Pkg.Insert_Row', 8);
'Igs_Pe_Person_Addr_Pkg.Insert_Row '||
'(p_action => :p_action '||
',p_party_type => :p_party_type '||
',p_party_id => :p_party_Id '||
',p_status => :p_status '||
',p_start_dt => :p_date_from '||
',p_end_dt => :p_date_to '||
',p_country => :p_country '||
',p_address_style => :p_address_style '||
',p_addr_line_1 => :p_Address1 '||
',p_addr_line_2 => :p_Address2 '||
',p_addr_line_3 => :p_Address3 '||
',p_addr_line_4 => :p_Address4 '||
',p_date_last_verified => :p_effective_date '||
',p_correspondence => :p_correspondence '||
',p_city => :p_city '||
',p_state => :p_state '||
',p_province => :p_province '||
',p_county => :p_county '||
',p_postal_code => :p_postal_code '||
',p_address_lines_phonetic => :p_Address_Lines_Phonetic '||
',p_delivery_point_code => :p_Delivery_Point_Code '||
',p_other_details_1 => :p_other_details_1 '||
',p_other_details_2 => :p_other_details_2 '||
',p_other_details_3 => :p_other_details_3 '||
-- In Out
',p_party_site_id => :p_party_site_id '||
',p_last_update_date => :p_last_update_date '||
',p_party_site_ovn => :p_party_site_ovn '||
',p_location_ovn => :p_location_ovn '||
-- Out
',p_rowid => :p_rowid '||
',p_location_id => :p_location_id '||
',l_return_status => :p_return_status '||
',l_msg_data => :p_msg_data '||
' ); '||
,IN OUT p_last_update_date
,IN OUT p_party_site_ovn
,IN OUT p_location_ovn
,IN OUT p_rowid
,IN OUT p_location_id
-- Out
,OUT p_return_status
,OUT p_msg_data;
END Update_Address_HR_To_TCA;