The following lines contain the word 'select', 'insert', 'update' or 'delete':
select tp.time_period_id
from per_time_periods tp
where tp.period_set_name = p_period_set_name
and tp.time_period_id <> p_time_period_id
and upper(tp.period_name) = upper(p_period_name);
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Time_Period_Id IN OUT NOCOPY NUMBER,
X_Payroll_Id NUMBER,
X_End_Date DATE,
X_Period_Name VARCHAR2,
X_Period_Num NUMBER,
X_Period_Type VARCHAR2,
X_Start_Date DATE,
X_Cut_Off_Date DATE,
X_Default_Dd_Date DATE,
X_Description VARCHAR2,
X_Pay_Advice_Date DATE,
X_Period_Set_Name VARCHAR2,
X_Period_Year NUMBER,
X_Proc_Period_Type VARCHAR2,
X_Quarter_Num NUMBER,
X_Quickpay_Display_Number NUMBER,
X_Regular_Payment_Date DATE,
X_Run_Display_Number NUMBER,
X_Status VARCHAR2,
X_Year_Number NUMBER,
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_Prd_Information_Category VARCHAR2,
X_Prd_Information1 VARCHAR2,
X_Prd_Information2 VARCHAR2,
X_Prd_Information3 VARCHAR2,
X_Prd_Information4 VARCHAR2,
X_Prd_Information5 VARCHAR2,
X_Prd_Information6 VARCHAR2,
X_Prd_Information7 VARCHAR2,
X_Prd_Information8 VARCHAR2,
X_Prd_Information9 VARCHAR2,
X_Prd_Information10 VARCHAR2,
X_Prd_Information11 VARCHAR2,
X_Prd_Information12 VARCHAR2,
X_Prd_Information13 VARCHAR2,
X_Prd_Information14 VARCHAR2,
X_Prd_Information15 VARCHAR2,
X_Prd_Information16 VARCHAR2,
X_Prd_Information17 VARCHAR2,
X_Prd_Information18 VARCHAR2,
X_Prd_Information19 VARCHAR2,
X_Prd_Information20 VARCHAR2,
X_Prd_Information21 VARCHAR2,
X_Prd_Information22 VARCHAR2,
X_Prd_Information23 VARCHAR2,
X_Prd_Information24 VARCHAR2,
X_Prd_Information25 VARCHAR2,
X_Prd_Information26 VARCHAR2,
X_Prd_Information27 VARCHAR2,
X_Prd_Information28 VARCHAR2,
X_Prd_Information29 VARCHAR2,
X_Prd_Information30 VARCHAR2,
X_Payslip_view_date DATE default null
) IS
--
CURSOR C IS SELECT rowid FROM per_time_periods
WHERE time_period_id = X_Time_Period_Id;
CURSOR C2 IS SELECT per_time_periods_s.nextval FROM sys.dual;
INSERT INTO per_time_periods
(time_period_id,
payroll_id,
end_date,
period_name,
period_num,
period_type,
start_date,
cut_off_date,
default_dd_date,
description,
pay_advice_date,
period_set_name,
period_year,
proc_period_type,
quarter_num,
quickpay_display_number,
regular_payment_date,
run_display_number,
status,
year_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
prd_information_category,
prd_information1,
prd_information2,
prd_information3,
prd_information4,
prd_information5,
prd_information6,
prd_information7,
prd_information8,
prd_information9,
prd_information10,
prd_information11,
prd_information12,
prd_information13,
prd_information14,
prd_information15,
prd_information16,
prd_information17,
prd_information18,
prd_information19,
prd_information20,
prd_information21,
prd_information22,
prd_information23,
prd_information24,
prd_information25,
prd_information26,
prd_information27,
prd_information28,
prd_information29,
prd_information30,
payslip_view_date)
VALUES
(X_Time_Period_Id,
X_Payroll_Id,
X_End_Date,
X_Period_Name,
X_Period_Num,
X_Period_Type,
X_Start_Date,
X_Cut_Off_Date,
X_Default_Dd_Date,
X_Description,
X_Pay_Advice_Date,
X_Period_Set_Name,
X_Period_Year,
X_Proc_Period_Type,
X_Quarter_Num,
X_Quickpay_Display_Number,
X_Regular_Payment_Date,
X_Run_Display_Number,
X_Status,
X_Year_Number,
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_Prd_Information_Category,
X_Prd_Information1,
X_Prd_Information2,
X_Prd_Information3,
X_Prd_Information4,
X_Prd_Information5,
X_Prd_Information6,
X_Prd_Information7,
X_Prd_Information8,
X_Prd_Information9,
X_Prd_Information10,
X_Prd_Information11,
X_Prd_Information12,
X_Prd_Information13,
X_Prd_Information14,
X_Prd_Information15,
X_Prd_Information16,
X_Prd_Information17,
X_Prd_Information18,
X_Prd_Information19,
X_Prd_Information20,
X_Prd_Information21,
X_Prd_Information22,
X_Prd_Information23,
X_Prd_Information24,
X_Prd_Information25,
X_Prd_Information26,
X_Prd_Information27,
X_Prd_Information28,
X_Prd_Information29,
X_Prd_Information30,
X_payslip_view_date
);
'per_time_periods_pkg.insert_row');
END Insert_Row;
CURSOR C IS SELECT * FROM per_time_periods
WHERE rowid = X_Rowid FOR UPDATE of Time_Period_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Time_Period_Id NUMBER,
X_Payroll_Id NUMBER,
X_End_Date DATE,
X_Period_Name VARCHAR2,
X_Period_Num NUMBER,
X_Period_Type VARCHAR2,
X_Start_Date DATE,
X_Cut_Off_Date DATE,
X_Default_Dd_Date DATE,
X_Description VARCHAR2,
X_Pay_Advice_Date DATE,
X_Period_Set_Name VARCHAR2,
X_Period_Year NUMBER,
X_Proc_Period_Type VARCHAR2,
X_Quarter_Num NUMBER,
X_Quickpay_Display_Number NUMBER,
X_Regular_Payment_Date DATE,
X_Run_Display_Number NUMBER,
X_Status VARCHAR2,
X_Year_Number NUMBER,
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_Prd_Information_Category VARCHAR2,
X_Prd_Information1 VARCHAR2,
X_Prd_Information2 VARCHAR2,
X_Prd_Information3 VARCHAR2,
X_Prd_Information4 VARCHAR2,
X_Prd_Information5 VARCHAR2,
X_Prd_Information6 VARCHAR2,
X_Prd_Information7 VARCHAR2,
X_Prd_Information8 VARCHAR2,
X_Prd_Information9 VARCHAR2,
X_Prd_Information10 VARCHAR2,
X_Prd_Information11 VARCHAR2,
X_Prd_Information12 VARCHAR2,
X_Prd_Information13 VARCHAR2,
X_Prd_Information14 VARCHAR2,
X_Prd_Information15 VARCHAR2,
X_Prd_Information16 VARCHAR2,
X_Prd_Information17 VARCHAR2,
X_Prd_Information18 VARCHAR2,
X_Prd_Information19 VARCHAR2,
X_Prd_Information20 VARCHAR2,
X_Prd_Information21 VARCHAR2,
X_Prd_Information22 VARCHAR2,
X_Prd_Information23 VARCHAR2,
X_Prd_Information24 VARCHAR2,
X_Prd_Information25 VARCHAR2,
X_Prd_Information26 VARCHAR2,
X_Prd_Information27 VARCHAR2,
X_Prd_Information28 VARCHAR2,
X_Prd_Information29 VARCHAR2,
X_Prd_Information30 VARCHAR2,
X_Payslip_view_Date DATE default null
) IS
--
BEGIN
--
-- Lock payroll record to enforce integrity when changing time period
-- statuses.
pay_payrolls_f_pkg.lock_payroll(X_Payroll_Id);
UPDATE per_time_periods
SET time_period_id = X_Time_Period_Id,
payroll_id = X_Payroll_Id,
end_date = X_End_Date,
period_name = X_Period_Name,
period_num = X_Period_Num,
period_type = X_Period_Type,
start_date = X_Start_Date,
cut_off_date = X_Cut_Off_Date,
default_dd_date = X_Default_Dd_Date,
description = X_Description,
pay_advice_date = X_Pay_Advice_Date,
period_set_name = X_Period_Set_Name,
period_year = X_Period_Year,
proc_period_type = X_Proc_Period_Type,
quarter_num = X_Quarter_Num,
quickpay_display_number = X_Quickpay_Display_Number,
regular_payment_date = X_Regular_Payment_Date,
run_display_number = X_Run_Display_Number,
status = X_Status,
year_number = X_Year_Number,
attribute_category = X_Attribute_Category,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15,
attribute16 = X_Attribute16,
attribute17 = X_Attribute17,
attribute18 = X_Attribute18,
attribute19 = X_Attribute19,
attribute20 = X_Attribute20,
prd_information_category = X_Prd_Information_Category,
prd_information1 = X_Prd_Information1,
prd_information2 = X_Prd_Information2,
prd_information3 = X_Prd_Information3,
prd_information4 = X_Prd_Information4,
prd_information5 = X_Prd_Information5,
prd_information6 = X_Prd_Information6,
prd_information7 = X_Prd_Information7,
prd_information8 = X_Prd_Information8,
prd_information9 = X_Prd_Information9,
prd_information10 = X_Prd_Information10,
prd_information11 = X_Prd_Information11,
prd_information12 = X_Prd_Information12,
prd_information13 = X_Prd_Information13,
prd_information14 = X_Prd_Information14,
prd_information15 = X_Prd_Information15,
prd_information16 = X_Prd_Information16,
prd_information17 = X_Prd_Information17,
prd_information18 = X_Prd_Information18,
prd_information19 = X_Prd_Information19,
prd_information20 = X_Prd_Information20,
prd_information21 = X_Prd_Information21,
prd_information22 = X_Prd_Information22,
prd_information23 = X_Prd_Information23,
prd_information24 = X_Prd_Information24,
prd_information25 = X_Prd_Information25,
prd_information26 = X_Prd_Information26,
prd_information27 = X_Prd_Information27,
prd_information28 = X_Prd_Information28,
prd_information29 = X_Prd_Information29,
prd_information30 = X_Prd_Information30,
payslip_view_date = X_Payslip_view_date
WHERE rowid = X_rowid;
'per_time_periods_pkg.update_row');
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
--
BEGIN
--
DELETE FROM per_time_periods
WHERE rowid = X_Rowid;
'per_time_periods_pkg.delete_row');
END Delete_Row;
PROCEDURE Update_Payslip_View_Date (P_Payroll_id Number,
P_offset Number
) IS
Begin
Update per_time_periods
Set Payslip_view_Date = Regular_Payment_Date + P_offset
where Payroll_id = P_Payroll_id;
End Update_Payslip_View_Date;