[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
NON_ORCL 21 => Invalid GL_Code_Combination ID value obtained for selected Payroll
PRE_GEN 22 => Invalid Costed GL_CCID (Costed GL_CCID is NULL)
NON_ORCL 23 => Set_Of_Books_ID from Profile does not match Set of Books ID in Payrolls table
24 => No Business Days in selected date range (Summarize and Transfer will fail)
25 => Invalid Debit/Credit Flag
26 => Sub-line start and end dates co-incide with the dates in another sub-line in batch in Payroll Interface
27 => Sub-line start and end dates co-incide with dates in another sub-line in batch in Payroll Sub-lines table
NON_ORCL 28 => Pay Amount Not equal to Daily Rate * Business Days -- Added by Al on 02/16/99 for bug 707404
29 => Invalid Sub Line Start Date (Sub Line Start Date does not occur within Assignment Start Date
and End date)
30 => Invalid Sub Line End Date (Sub Line End Date does not occur within Assignment Start and End Dates)
PSP_HR_SHARED profile is deleted since we will not be able to support in multi-org
Multi-org changes with addition of business group id and set of books id conditions -- vcirigir
**********************************************************************************/
Function Validate_Payroll_ID(v_Payroll_ID IN Number, v_Assignment_ID IN Number, v_Effective_Date IN Date,
v_business_group_id IN NUMBER,v_set_of_books_id IN NUMBER) Return Number;
Select *
from PSP_PAYROLL_INTERFACE
where BATCH_NAME = v_Batch_Name
and STATUS_CODE <> 'T'
FOR UPDATE OF STATUS_CODE, ERROR_CODE;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
-- Need to check if a valid GL_Code_Combination_ID is to be obtained for selected payroll
Begin
retVal := Validate_GL_CCID(Import_Cursor_Agg.Payroll_ID, Import_Cursor_Agg.Effective_Date);
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
-- Need to check if there is atleast one business day in selected sub_line date range
Begin
If PSP_General.Business_Days(Import_Cursor_Agg.Sub_Line_Start_Date, Import_Cursor_Agg.Sub_Line_End_Date) = 0
AND g_hire_zero_work_days='N' Then --Modified for zero work days build.Bug 1994421.
retVal := 24;
UPDATE PSP_PAYROLL_INTERFACE set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'E', ERROR_CODE = (v_DB_Err_Code) where CURRENT OF Import_Cursor;
UPDATE PSP_PAYROLL_INTERFACE Set STATUS_CODE = 'V', ERROR_CODE = '' where CURRENT OF Import_Cursor;
Select count(*)
into v_Record_Count
from PSP_PAYROLL_INTERFACE
where Payroll_Period_ID = v_Payroll_Period_ID
and Assignment_ID = v_Assignment_ID
and Element_Type_ID = v_Element_Type_ID
and Batch_Name = v_Batch_Name
and ((Sub_Line_Start_Date Between v_Sub_Line_Start_Date and v_Sub_Line_End_Date)
or (Sub_Line_End_Date Between v_Sub_Line_Start_Date and v_Sub_Line_End_Date)
or (Sub_Line_Start_Date < v_Sub_Line_Start_Date and Sub_Line_End_Date > v_Sub_Line_End_Date));
Select count(*)
into v_Record_Count
from PSP_PAYROLL_LINES a,
PSP_PAYROLL_SUB_LINES b,
PSP_PAYROLL_CONTROLS c
where a.PAYROLL_CONTROL_ID = c.PAYROLL_CONTROL_ID
and a.PAYROLL_LINE_ID = b.PAYROLL_LINE_ID
and c.TIME_PERIOD_ID = v_Payroll_Period_ID
and a.ASSIGNMENT_ID = v_Assignment_ID
and a.ELEMENT_TYPE_ID = v_Element_Type_ID
and ((b.Sub_Line_Start_Date Between v_Sub_Line_Start_Date and v_Sub_Line_End_Date)
or (b.Sub_Line_End_Date Between v_Sub_Line_Start_Date and v_Sub_Line_End_Date)
or (b.Sub_Line_Start_Date < v_Sub_Line_Start_Date and b.Sub_Line_End_Date > v_Sub_Line_End_Date));
select a.GL_SET_OF_BOOKS_ID, Cost_Allocation_KeyFlex_ID
into n_Set_Of_Books_ID, n_Cost_Allocation_KeyFlex_ID
from PAY_PAYROLLS_F a
where a.PAYROLL_ID = v_Payroll_ID
and v_Effective_Date BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE;
/* The following cursor is added to replace the select statement to get assignment dates.
Bug 1994421 "Zero Work Days build */
CURSOR assignment_date_cur IS
SELECT min(effective_start_date), max(effective_end_date)
FROM PER_ALL_ASSIGNMENTS_F
WHERE assignment_id = v_assignment_id
AND assignment_type ='E'; --Added for bug 2624259
select a.Start_Date, a.End_Date
into Time_Period_Start_Date, Time_Period_End_Date
from PER_TIME_PERIODS a
where a.Time_Period_ID = v_Payroll_Period_ID
and a.PAYROLL_ID = v_Payroll_ID
and (v_Effective_Date BETWEEN a.Start_Date AND a.End_Date);
/* select min(a.effective_start_date),max(a.effective_end_date)
into assignment_start_date,assignment_end_date
from PER_ALL_ASSIGNMENTS_F a
where a.assignment_id = v_Assignment_id
and assignment_status_type_id IN (select distinct assignment_status_type_id
from per_assignment_status_types
where per_system_status = 'ACTIVE_ASSIGN') */
/* The following code is added to get assignment dates. Bug 1994421 "Zero Work Days build */
OPEN assignment_date_cur;
/***** Modifed the following SELECT for 11510_CU2 consolidated performance fixes.
Select DISTINCT a.payroll_id
into v_local_number
from pay_payrolls_f a, per_assignments_f b
where a.payroll_id = b.payroll_id
and a.PAYROLL_ID = v_Payroll_ID
and b.assignment_id = v_assignment_id
and (v_effective_date between a.effective_start_date and a.effective_end_date)
and a.business_group_id = v_business_group_id
and a.gl_set_of_books_id = v_set_of_books_id;
SELECT a.payroll_id
INTO v_local_number
FROM pay_payrolls_f a
WHERE a.payroll_id = v_payroll_id
AND (v_effective_date between a.effective_start_date and a.effective_end_date)
AND a.business_group_id = v_business_group_id
AND a.gl_set_of_books_id = v_set_of_books_id
AND EXISTS (SELECT 1
FROM per_assignments_f b
WHERE b.payroll_id = a.payroll_id
AND b.assignment_id = v_assignment_id
AND v_effective_date BETWEEN b.effective_start_date AND b.effective_end_date);
Select Time_Period_id
into v_local_number
From Per_Time_Periods
where Payroll_id = v_Payroll_ID
and Time_Period_ID = v_Payroll_Period_ID
and (v_Effective_Date between start_date and end_date);
Select Cost_Allocation_KeyFlex_ID
into v_Cost_Allocation_Keyflex_ID
from PAY_PAYROLLS_F
where Payroll_ID = v_Payroll_Id
and v_Effective_Date between Effective_Start_Date and Effective_End_Date
and business_group_id = v_business_group_id
and gl_set_of_books_id = v_set_of_books_id;
Select a.assignment_id
into v_local_number
from per_assignments_f a ,
pay_payrolls_f b
where a.person_id = v_person_id
AND a.assignment_type ='E' --Added for bug 2624259.
and a.assignment_id = v_Assignment_ID
and (v_effective_date between a.effective_start_date and a.effective_end_date)
and a.business_group_id = v_business_group_id
and b.gl_set_of_books_id = v_set_of_books_id
and a.payroll_id = b.payroll_id;
/* Following cursor is added to replace the select statement to get person_id.
Bug 1994421 "Zero Work Days Build" */
/***** Modified the following cursor for R12 performance fixes (bug 4507892)
CURSOR Valid_person_cur IS
SELECT a.person_id
FROM Per_People_F a
WHERE a.Person_ID = v_person_id
-- AND a.current_employee_flag ='Y' --Added for bug 2624259. Commented for Bug 3424494
AND (v_effective_date BETWEEN a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE)
AND v_effective_date <= (SELECT max(b.effective_end_date)
FROM per_assignments_f b,pay_payrolls_f f
WHERE a.person_id = b.person_id
AND b.assignment_type ='E' --Added for bug 2624259.
AND b.business_group_id = v_business_group_id
AND f.gl_set_of_books_id = v_set_of_books_id
AND f.payroll_id = b.payroll_id) ;
SELECT ppf.person_id
FROM per_people_f ppf
WHERE ppf.person_id = v_person_id
AND (v_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date)
AND v_effective_date <= (SELECT MAX(paf.effective_end_date)
FROM per_assignments_f paf,
pay_payrolls_f ppf2
WHERE paf.person_id = v_person_id
AND paf.business_group_id = v_business_group_id
AND ppf2.payroll_id = paf.payroll_id
AND paf.assignment_type ='E'
AND ppf2.gl_set_of_books_id = v_set_of_books_id);
/* Select a.person_id
into v_local_number
from Per_People_F a
where a.Person_ID = v_Person_ID
and (v_effective_date BETWEEN a.EFFECTIVE_START_DATE and A.EFFECTIVE_END_DATE)
and a.business_group_id = v_business_group_id
and a.person_id in ( select distinct person_id
from per_assignments_f f,pay_payrolls_f b
where (v_effective_date between f.effective_start_date and f.effective_end_date)
and f.business_group_id = v_business_group_id
and b.gl_set_of_books_id = v_set_of_books_id
and f.payroll_id = b.payroll_id )
and a.current_employee_flag = 'Y'; */
(select distinct Person_ID
from per_assignments_f
where (v_Effective_Date between per_assignments_f.effective_start_date
and per_assignments_f.effective_end_date));
select a.source_code
into v_local_char
from psp_payroll_sources a
where a.source_type = 'N'
and a.source_code = v_Payroll_Source_Code;
Select DISTINCT LOOKUP_CODE into v_local_char from PSP_LOOKUPS
where LOOKUP_CODE = v_Payroll_Source_Code
and LOOKUP_TYPE = 'PAYROLL_SOURCES'
and LOOKUP_CODE NOT IN ('PSP','LDM');
Select a.element_type_id
into v_local_number
from psp_element_types a, pay_element_types_f b, per_time_periods c
where a.element_type_id = b.element_type_id
and a.element_type_id = v_Element_Type_ID
and c.time_period_id = v_payroll_period_id
and ((c.start_date between a.start_date_active and a.end_date_active)
or (c.end_date between a.start_date_active and a.end_date_active)
or ((a.start_date_active < c.start_date) and (a.end_date_active > c.end_date)))
and b.output_currency_code = v_currency_code;
SELECT a.element_type_id
INTO v_local_number
FROM PSP_ELEMENT_TYPES a,
PER_TIME_PERIODS b
WHERE a.element_type_id = v_element_type_id
AND b.time_period_id = v_payroll_period_id
-- Introduced BG/SOB check on psp_element_types for bug fix 3098050
AND a.business_group_id = v_business_group_id
AND a.set_of_books_id = v_set_of_books_id
AND b.start_date <= a.end_date_active
AND b.end_date >= a.start_date_active
AND exists
(SELECT 1
FROM PAY_ELEMENT_TYPES_F pef
WHERE pef.element_type_id = a.element_type_id
AND ( pef.output_currency_code = v_currency_code
OR v_currency_code = 'STAT')
AND pef.effective_end_date >= a.start_date_active
AND pef.effective_start_date <= a.end_date_active
);