[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT dgu.oracle_id
, fr.data_group_application_id
FROM fnd_data_group_units dgu
, fnd_responsibility fr
WHERE fr.data_group_id = dgu.data_group_id
AND fr.data_group_application_id = dgu.application_id
AND fr.application_id = X_Resp_Appl_Id
AND fr.responsibility_id = X_Resp_Id;
SELECT gsb.chart_of_accounts_id
, gsb.name
FROM gl_sets_of_books gsb
WHERE gsb.set_of_books_id = X_Set_Of_Books_Id;
SELECT pbg.legislation_code
, pbg.name
, DECODE(pbg.METHOD_OF_GENERATION_EMP_NUM,'A'
, X_property_off , X_property_on)
FROM per_business_groups pbg
WHERE pbg.business_group_id = X_Business_group_id;
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type= 'HOME_OFFICE'
AND hl.lookup_code = X_expense_check_to_address;
SELECT ppt.person_type_id
, pst.current_employee_flag
, pst.current_applicant_flag
, pst.current_emp_or_apl_flag
, TRUNC(SYSDATE)
, TO_DATE('31/12/4712','DD/MM/YYYY')
FROM per_person_types ppt
, per_startup_person_types pst
WHERE ppt.business_group_id = X_Business_group_id -- Bug 3648460
AND ppt.system_person_type = pst.system_person_type
AND ppt.system_person_type = DECODE(X_current_appl_id,810,'OTHER','EMP')
AND ppt.default_flag = 'Y'
AND pst.default_flag = 'Y';
l_sql_text := 'select fsp.set_of_books_id '
|| ',fsp.expense_check_address_flag '
|| 'from financials_system_parameters fsp';
PROCEDURE Insert_period_of_Service(X_Person_Id NUMBER
,X_Business_Group_Id NUMBER
,X_Date_Start DATE
,X_Period_of_Service_Id IN OUT NOCOPY NUMBER)
IS
CURSOR C1 IS SELECT per_periods_of_service_s.NEXTVAL FROM sys.dual;
INSERT INTO PER_PERIODS_OF_SERVICE(
PERIOD_OF_SERVICE_ID
,BUSINESS_GROUP_ID
,PERSON_ID
,DATE_START
) VALUES (
X_Period_of_Service_Id
,X_Business_Group_Id
,X_Person_Id
,X_Date_Start);
,p_event => 'INSERTING'
,p_effective_date => x_date_start);
END insert_period_of_service;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Person_Id IN OUT NOCOPY NUMBER,
X_Party_Id NUMBER DEFAULT NULL,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Person_Type_Id NUMBER,
X_Last_Name VARCHAR2,
X_Start_Date DATE,
X_Applicant_Number VARCHAR2,
X_Comment_Id NUMBER,
X_Current_Applicant_Flag VARCHAR2,
X_Current_Emp_Or_Apl_Flag VARCHAR2,
X_Current_Employee_Flag VARCHAR2,
X_Date_Employee_Data_Verified DATE,
X_Date_Of_Birth DATE,
X_Email_Address VARCHAR2,
X_Employee_Number VARCHAR2,
X_Expense_Check_To_Address VARCHAR2,
X_First_Name VARCHAR2,
X_Full_Name VARCHAR2,
X_Known_As VARCHAR2,
X_Marital_Status VARCHAR2,
X_Middle_Names VARCHAR2,
X_Nationality VARCHAR2,
X_National_Identifier VARCHAR2,
X_Previous_Last_Name VARCHAR2,
X_Registered_Disabled_Flag VARCHAR2,
X_Sex VARCHAR2,
X_Title VARCHAR2,
X_Vendor_Id NUMBER,
X_Work_Telephone VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Attribute21 VARCHAR2,
X_Attribute22 VARCHAR2,
X_Attribute23 VARCHAR2,
X_Attribute24 VARCHAR2,
X_Attribute25 VARCHAR2,
X_Attribute26 VARCHAR2,
X_Attribute27 VARCHAR2,
X_Attribute28 VARCHAR2,
X_Attribute29 VARCHAR2,
X_Attribute30 VARCHAR2,
X_Per_Information_Category VARCHAR2,
X_Per_Information1 VARCHAR2,
X_Per_Information2 VARCHAR2,
X_Per_Information3 VARCHAR2,
X_Per_Information4 VARCHAR2,
X_Per_Information5 VARCHAR2,
X_Per_Information6 VARCHAR2,
X_Per_Information7 VARCHAR2,
X_Per_Information8 VARCHAR2,
X_Per_Information9 VARCHAR2,
X_Per_Information10 VARCHAR2,
X_Per_Information11 VARCHAR2,
X_Per_Information12 VARCHAR2,
X_Per_Information13 VARCHAR2,
X_Per_Information14 VARCHAR2,
X_Per_Information15 VARCHAR2,
X_Per_Information16 VARCHAR2,
X_Per_Information17 VARCHAR2,
X_Per_Information18 VARCHAR2,
X_Per_Information19 VARCHAR2,
X_Per_Information20 VARCHAR2,
X_Per_Information21 VARCHAR2,
X_Per_Information22 VARCHAR2,
X_Per_Information23 VARCHAR2,
X_Per_Information24 VARCHAR2,
X_Per_Information25 VARCHAR2,
X_Per_Information26 VARCHAR2,
X_Per_Information27 VARCHAR2,
X_Per_Information28 VARCHAR2,
X_Per_Information29 VARCHAR2,
X_Per_Information30 VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Created_By NUMBER,
X_Creation_Date DATE,
X_Order_Name VARCHAR2,
X_Global_Name VARCHAR2,
X_Local_Name VARCHAR2,
X_PERIOD_OF_SERVICE_ID IN OUT NOCOPY NUMBER,
X_TOWN_OF_BIRTH VARCHAR2 DEFAULT NULL,
X_REGION_OF_BIRTH VARCHAR2 DEFAULT NULL,
X_COUNTRY_OF_BIRTH VARCHAR2 DEFAULT NULL,
X_GLOBAL_PERSON_ID VARCHAR2 DEFAULT NULL,
X_blood_type VARCHAR2 default NULL,
X_correspondence_language VARCHAR2 default NULL,
X_honors VARCHAR2 default NULL,
X_pre_name_adjunct VARCHAR2 default NULL,
X_rehire_authorizor VARCHAR2 default NULL,
X_rehire_recommendation VARCHAR2 default NULL,
X_resume_exists VARCHAR2 default NULL,
X_resume_last_updated DATE default NULL,
X_second_passport_exists VARCHAR2 default NULL,
X_student_status VARCHAR2 default NULL,
X_suffix VARCHAR2 default NULL,
X_date_of_death DATE default NULL,
X_uses_tobacco_flag VARCHAR2 default NULL,
X_fast_path_employee VARCHAR2 default NULL,
X_fte_capacity VARCHAR2 default NULL
) IS
l_period_of_service_id NUMBER;
CURSOR C IS SELECT ROWID FROM PER_ALL_PEOPLE_F
WHERE person_id = X_Person_Id;
CURSOR C2 IS SELECT per_people_s.NEXTVAL FROM sys.dual;
SELECT *
FROM per_all_people_f
WHERE person_id = x_person_id
AND x_effective_start_date
BETWEEN effective_start_date
AND effective_end_date;
l_proc VARCHAR2(72):='per_people_v14_pkg.insert_row';
INSERT INTO PER_ALL_PEOPLE_F(
person_id,
effective_start_date,
effective_end_date,
business_group_id,
person_type_id,
last_name,
start_date,
applicant_number,
comment_id,
current_applicant_flag,
current_emp_or_apl_flag,
current_employee_flag,
date_employee_data_verified,
date_of_birth,
email_address,
employee_number,
expense_check_send_to_address,
first_name,
full_name,
known_as,
marital_status,
middle_names,
nationality,
national_identifier,
previous_last_name,
registered_disabled_flag,
sex,
title,
vendor_id,
-- work_telephone, -- Now handled by the Create_phone call.
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
per_information_category,
per_information1,
per_information2,
per_information3,
per_information4,
per_information5,
per_information6,
per_information7,
per_information8,
per_information9,
per_information10,
per_information11,
per_information12,
per_information13,
per_information14,
per_information15,
per_information16,
per_information17,
per_information18,
per_information19,
per_information20,
per_information21,
per_information22,
per_information23,
per_information24,
per_information25,
per_information26,
per_information27,
per_information28,
per_information29,
per_information30,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
town_of_birth,
region_of_birth,
country_of_birth,
global_person_id,
party_id,
blood_type,
correspondence_language,
honors,
pre_name_adjunct,
rehire_authorizor,
rehire_recommendation,
resume_exists,
resume_last_updated,
second_passport_exists,
student_status,
suffix,
date_of_death,
uses_tobacco_flag,
fast_path_employee,
fte_capacity,
order_name, -- #3889584
global_name,
local_name
) VALUES (
X_Person_Id,
X_Effective_Start_Date,
X_Effective_End_Date,
X_Business_Group_Id,
-- X_Person_Type_Id,
hr_person_type_usage_info.get_default_person_type_id(X_Person_Type_Id),
X_Last_Name,
X_Start_Date,
X_Applicant_Number,
X_Comment_Id,
X_Current_Applicant_Flag,
X_Current_Emp_Or_Apl_Flag,
X_Current_Employee_Flag,
X_Date_Employee_Data_Verified,
X_Date_Of_Birth,
X_Email_Address,
X_Employee_Number,
X_Expense_Check_To_Address,
X_First_Name,
X_Full_Name,
X_Known_As,
X_Marital_Status,
X_Middle_Names,
X_Nationality,
X_National_Identifier,
X_Previous_Last_Name,
X_Registered_Disabled_Flag,
X_Sex,
X_Title,
X_Vendor_Id,
-- X_Work_Telephone, -- Now handled by the Create_phone call.
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Attribute16,
X_Attribute17,
X_Attribute18,
X_Attribute19,
X_Attribute20,
X_Attribute21,
X_Attribute22,
X_Attribute23,
X_Attribute24,
X_Attribute25,
X_Attribute26,
X_Attribute27,
X_Attribute28,
X_Attribute29,
X_Attribute30,
X_per_information_category,
X_per_information1,
X_per_information2,
X_per_information3,
X_per_information4,
X_per_information5,
X_per_information6,
X_per_information7,
X_per_information8,
X_per_information9,
X_per_information10,
X_per_information11,
X_per_information12,
X_per_information13,
X_per_information14,
X_per_information15,
X_per_information16,
X_per_information17,
X_per_information18,
X_per_information19,
X_per_information20,
X_per_information21,
X_per_information22,
X_per_information23,
X_per_information24,
X_per_information25,
X_per_information26,
X_per_information27,
X_per_information28,
X_per_information29,
X_per_information30,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login,
X_Created_By,
X_Creation_Date,
X_town_of_birth,
X_region_of_birth,
X_country_of_birth,
X_global_person_id,
X_party_id,
X_blood_type,
X_correspondence_language,
X_honors,
X_pre_name_adjunct,
X_rehire_authorizor,
X_rehire_recommendation,
X_resume_exists,
X_resume_last_updated,
X_second_passport_exists,
X_student_status,
X_suffix,
X_date_of_death,
X_uses_tobacco_flag,
X_fast_path_employee,
X_fte_capacity,
X_Order_Name,
X_Global_Name,
X_Local_Name);
UPDATE per_all_people_f
SET party_id = l_person.party_id
WHERE person_id = x_person_id;
p_action => 'INSERT');
insert_period_of_service(X_Person_id => X_Person_Id
,X_Business_Group_Id => X_Business_Group_id
,X_Date_Start => X_effective_Start_Date
,X_Period_Of_Service_Id => X_Period_Of_Service_Id
);
END Insert_Row;
SELECT *
FROM PER_ALL_PEOPLE_F
WHERE ROWID = X_Rowid
FOR UPDATE OF Person_Id NOWAIT;
SELECT phone_number
FROM per_phones
WHERE parent_id = X_person_id
FOR UPDATE OF phone_id NOWAIT;