[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT payroll_interface_id
FROM psp_payroll_interface
WHERE batch_name = v_batch_name
AND business_group_id = v_business_group_id
AND set_of_books_id = v_set_of_books_id
FOR UPDATE OF payroll_interface_id NOWAIT;
SELECT currency_code
FROM gl_sets_of_books gsob
WHERE set_of_books_id = v_set_of_books_id;
select STATUS_CODE
from PSP_PAYROLL_INTERFACE
where Batch_Name = v_Batch_Name
FOR UPDATE OF STATUS_CODE;
Update PSP_PAYROLL_INTERFACE set STATUS_CODE = 'T' where CURRENT OF Change_Statuses;
select count(*)
into n_Valid_Records
from PSP_PAYROLL_INTERFACE
where Batch_Name = v_Batch_Name
and STATUS_CODE <> 'V';
Select DISTINCT Payroll_Period_ID, Payroll_Source_Code,
GL_POSTING_OVERRIDE_DATE,GMS_POSTING_OVERRIDE_DATE
From PSP_PAYROLL_INTERFACE
Where Batch_Name = v_Batch_Name
And STATUS_CODE <> 'T';
SELECT end_date
FROM PER_TIME_PERIODS
WHERE time_period_id = n_time_period_id;
Select DISTINCT Payroll_ID
Into n_Payroll_ID
From PSP_PAYROLL_INTERFACE
where PAYROLL_PERIOD_ID = Control_Record_Agg.Payroll_Period_ID
and PAYROLL_SOURCE_CODE = Control_Record_Agg.Payroll_Source_Code
and BATCH_NAME = v_Batch_Name;
select Count(DR_CR_FLAG), SUM(ROUND(PAY_AMOUNT,v_precision))
into n_Number_Of_Credits, n_Credit_Amount
from PSP_PAYROLL_INTERFACE
where Payroll_Period_ID = Control_Record_Agg.Payroll_Period_ID
and Payroll_Source_Code = Control_Record_Agg.Payroll_Source_Code
and Batch_Name = v_Batch_Name
and UPPER(DR_CR_FLAG) = 'C';
select Count(DR_CR_FLAG), SUM(ROUND(PAY_AMOUNT,v_precision))
into n_Number_Of_Debits, n_Debit_Amount
from PSP_PAYROLL_INTERFACE
where Payroll_Period_ID = Control_Record_Agg.Payroll_Period_ID
and Payroll_Source_Code = Control_Record_Agg.Payroll_Source_Code
and Batch_Name = v_Batch_Name
and UPPER(DR_CR_FLAG) = 'D';
Select SUM(ROUND(PAY_AMOUNT,v_precision))
into v_Sublines_CR_Amount
from PSP_PAYROLL_INTERFACE
where Batch_Name = v_Batch_Name
-- Introduced Time Period and Source Code check for bug fix 3116383
AND payroll_period_id = control_record_agg.payroll_period_id
AND payroll_source_code = control_record_agg.payroll_source_code
and UPPER(DR_CR_FLAG) = 'C';
Select SUM(ROUND(PAY_AMOUNT,v_precision))
into v_Sublines_DR_Amount
from PSP_PAYROLL_INTERFACE
where Batch_Name = v_Batch_Name
-- Introduced Time Period and Source Code check for bug fix 3116383
AND payroll_period_id = control_record_agg.payroll_period_id
AND payroll_source_code = control_record_agg.payroll_source_code
and UPPER(DR_CR_FLAG) = 'D';
Select PSP_PAYROLL_CONTROLS_S.NextVal
into n_Payroll_Control_ID
from DUAL;
PSP_PAYROLL_CONTROLS_PKG.INSERT_ROW (
X_ROWID => v_ROWID,
X_PAYROLL_CONTROL_ID => n_Payroll_Control_ID,
X_PAYROLL_ACTION_ID => n_Payroll_Action_ID,
X_PAYROLL_SOURCE_CODE => Control_Record_Agg.Payroll_Source_Code,
X_SOURCE_TYPE => 'N',
X_PAYROLL_ID => n_Payroll_ID,
X_TIME_PERIOD_ID => Control_Record_Agg.Payroll_Period_ID,
X_NUMBER_OF_CR => n_Number_Of_Credits,
X_NUMBER_OF_DR => n_Number_Of_Debits,
X_TOTAL_DR_AMOUNT => n_Debit_Amount,
X_TOTAL_CR_AMOUNT => n_Credit_Amount,
-- X_ROLLBACK_FLAG => v_Rollback_Flag,
-- X_ROLLBACK_DATE => v_Rollback_Date,
X_BATCH_NAME => v_Batch_Name,
X_SUBLINES_DR_AMOUNT => v_Sublines_DR_Amount,
X_SUBLINES_CR_AMOUNT => v_Sublines_CR_Amount,
-- X_DISTRIBUTION_AMOUNT => NULL,
X_DIST_DR_AMOUNT => NULL,
X_DIST_CR_AMOUNT => NULL,
X_OGM_DR_AMOUNT => NULL,
X_OGM_CR_AMOUNT => NULL,
X_GL_DR_AMOUNT => NULL,
X_GL_CR_AMOUNT => NULL,
X_STATUS_CODE => 'N',
X_MODE => 'R' ,
X_GL_POSTING_OVERRIDE_DATE => Control_Record_Agg.GL_POSTING_OVERRIDE_DATE,
X_GMS_POSTING_OVERRIDE_DATE => Control_Record_Agg.GMS_POSTING_OVERRIDE_DATE,
X_set_of_books_id => v_set_of_books_id,
X_business_group_id => v_business_group_id,
X_GL_phase => NULL,
X_GMS_PHASE => NULL,
X_ADJ_SUM_BATCH_NAME => NULL,
X_CURRENCY_CODE => v_currency_code,
X_EXCHANGE_RATE_TYPE => l_exchange_rate_type);
inserts records to the PSP_PAYROLL_LINES table and creates corresponding sub_lines
by calling the Process_Payroll_Sub_Lines procedure.
ASSUMPTIONS: The foll. fields that are to be entered to the Payroll Lines tables have been left
empty (ref: Subbarao, date: 03/27/98)
COST_ID, COST_ALLOCATION_KEYFLEX_ID, GL_CODE_COMBINATION_ID, BALANCE_AMOUNT
CREATED BY: AL ARUNACHALAM
DATE: 03/27/98
-- Intoduced v_precision parameter for Bug 2916848
*****************************************************************************************/
Function Import_Payroll_Lines(v_Batch_Name IN varchar2,v_business_group_id IN NUMBER,
v_set_of_books_id IN NUMBER,
v_precision IN NUMBER,v_ext_precision IN NUMBER) return Number IS
cursor Lines_Record IS
Select DISTINCT Payroll_Period_ID, Assignment_ID, Element_Type_ID, Payroll_Source_Code,
Sub_Line_Start_Date, Sub_Line_End_Date
From PSP_PAYROLL_INTERFACE
Where Batch_Name = v_Batch_Name
And STATUS_CODE <> 'T';
select DISTINCT PAYROLL_CONTROL_ID
into n_Payroll_Control_ID
from PSP_PAYROLL_CONTROLS
where TIME_PERIOD_ID = Lines_Record_Agg.Payroll_Period_ID
and PAYROLL_SOURCE_CODE = Lines_Record_Agg.Payroll_Source_Code
and BATCH_NAME = v_Batch_Name
and business_group_id = v_business_group_id
and set_of_books_id = v_set_of_books_id;
/* Bug 4155144 - commented this block since the same check has already been done when inserting the control record
-- Obtain Payroll ID for a given Payroll Period and the non-oracle source code
BEGIN
Select DISTINCT Payroll_ID
Into n_Payroll_ID
From PSP_PAYROLL_INTERFACE
where PAYROLL_PERIOD_ID = Lines_Record_Agg.Payroll_Period_ID
and PAYROLL_SOURCE_CODE = Lines_Record_Agg.Payroll_Source_Code
and BATCH_NAME = v_Batch_Name;
Select DISTINCT Effective_Date, Person_ID, round(Pay_Amount,v_precision),
Check_Date, Earned_Date, UPPER(DR_CR_Flag), payroll_id
Into d_Effective_Date, n_Person_ID, n_Pay_Amount, d_Check_Date, d_Earned_Date,
v_DR_CR_Flag, n_payroll_id
From PSP_PAYROLL_INTERFACE
where PAYROLL_PERIOD_ID = Lines_Record_Agg.Payroll_Period_ID
and PAYROLL_SOURCE_CODE = Lines_Record_Agg.Payroll_Source_Code
--Condition Droped for bug fix 4179476
-- and PAYROLL_ID = n_Payroll_ID
and ASSIGNMENT_ID = Lines_Record_Agg.Assignment_ID
and ELEMENT_TYPE_ID = Lines_Record_Agg.Element_Type_ID
and SUB_LINE_START_DATE = Lines_Record_Agg.Sub_Line_Start_Date
and SUB_LINE_END_DATE = Lines_Record_Agg.Sub_Line_End_Date
and BATCH_NAME = v_Batch_Name;
select Cost_Allocation_KeyFlex_ID
into n_Cost_Allocation_KeyFlex_ID
from PAY_PAYROLLS_F a
where a.PAYROLL_ID = n_Payroll_ID
and d_Effective_Date BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
and a.business_group_id = v_business_group_id;
select PSP_PAYROLL_LINES_S.NextVal
into n_Payroll_Lines_ID
from DUAL;
Select reversing_gl_ccid
into n_GL_Code_Combination_ID
from PSP_CLEARING_ACCOUNT a
where a.business_group_id = v_business_group_id
and a.set_of_books_id = v_set_of_books_id
and a.payroll_id = n_payroll_id; -- Added for bug 5592964
-- dbms_output.put_line('Obtained GL CCID. Now, inserting to Payroll Lines');
PSP_PAYROLL_LINES_PKG.INSERT_ROW (
X_ROWID => v_ROWID,
X_PAYROLL_LINE_ID => n_Payroll_Lines_ID,
X_PAYROLL_CONTROL_ID => n_Payroll_Control_ID,
X_SET_OF_BOOKS_ID => v_Set_Of_Books_ID,
X_ASSIGNMENT_ID => Lines_Record_Agg.Assignment_ID,
X_PERSON_ID => n_Person_ID,
X_COST_ID => n_Cost_ID,
X_ELEMENT_TYPE_ID => Lines_Record_Agg.Element_Type_ID,
X_PAY_AMOUNT => n_Pay_Amount,
X_STATUS_CODE => 'N',
X_EFFECTIVE_DATE => d_Effective_Date,
X_CHECK_DATE => d_Check_Date,
X_EARNED_DATE => d_Earned_Date,
X_COST_ALLOCATION_KEYFLEX_ID => n_Cost_Allocation_KeyFlex_ID,
X_GL_CODE_COMBINATION_ID => n_GL_Code_Combination_ID,
X_BALANCE_AMOUNT => n_Balance_Amount,
X_DR_CR_FLAG => v_DR_CR_Flag,
X_MODE => 'R'
);
-- dbms_output.put_line('Inserted into Payroll Lines. Now, inserting to Payroll Sub
-- lines');
inserts records to the PSP_PAYROLL_SUB_LINES table and creates corresponding sub_lines
by calling the Import_Payroll_Sub_Lines procedure.
ASSUMPTIONS: The foll. fields that are to be entered to the Payroll Lines tables have been left
empty (ref: Subbarao, date: 03/27/98)
ORGANIZATION_ID, JOB_ID, POSITION_ID, EMP_BEGIN_DATE, EMP_END_DATE, EMP_STATUS_INACTIVE_DATE,EMP_STATUS_ACTIVE_DATE, ASSIGNMENT_BEGIN_DATE, ASSIGNMENT_END_DATE
CREATED BY: AL ARUNACHALAM
DATE: 03/27/98
*****************************************************************************************/
Function Process_Payroll_Sub_Lines(v_Batch_Name varchar2, n_Payroll_Period_ID number,
n_Assignment_ID Number, n_Element_Type_Id Number, n_Payroll_Lines_ID Number,
d_Sub_Line_Start_Date DATE, d_Sub_Line_End_Date DATE,
v_precision IN NUMBER,v_ext_precision IN NUMBER,
v_business_group_id IN NUMBER) -- Introduced BG for bug 2908859
return Number IS
cursor Sub_Lines_Record IS
Select *
From PSP_PAYROLL_INTERFACE
where Batch_Name = v_batch_name
and PAYROLL_PERIOD_ID = n_Payroll_Period_ID
and ASSIGNMENT_ID = n_Assignment_ID
and ELEMENT_TYPE_ID = n_Element_Type_ID
and SUB_LINE_START_DATE = d_Sub_Line_Start_Date
and SUB_LINE_END_DATE = d_Sub_Line_End_Date
and STATUS_CODE <> 'T';
select PSP_PAYROLL_SUB_LINES_S.nextval
into n_Payroll_Sub_Lines_ID
from DUAL;
-- dbms_output.put_line('Inserting into payroll sub lines table');
PSP_PAYROLL_SUB_LINES_PKG.INSERT_ROW (
X_ROWID => v_RowID,
X_PAYROLL_SUB_LINE_ID => n_Payroll_Sub_Lines_ID,
X_PAYROLL_LINE_ID => n_Payroll_Lines_ID,
X_SUB_LINE_START_DATE => d_Sub_Line_Start_Date,
X_SUB_LINE_END_DATE => d_Sub_Line_End_Date,
X_REASON_CODE => v_Reason_Code,
X_PAY_AMOUNT => n_Pay_Amount,
X_DAILY_RATE => n_Daily_Rate,
X_SALARY_USED => n_Salary_Used,
X_CURRENT_SALARY => n_Current_Salary,
X_FTE => n_FTE,
X_ORGANIZATION_ID => n_Organization_ID,
X_JOB_ID => n_Job_ID,
X_POSITION_ID => n_Position_ID,
X_GRADE_ID => NULL, --- Bug Fix 2023955
X_PEOPLE_GRP_ID => NULL,
X_EMPLOYMENT_BEGIN_DATE => d_Employment_Begin_Date,
X_EMPLOYMENT_END_DATE => d_Employment_End_Date,
X_EMPLOYEE_STATUS_INACTIVE_DAT => d_Status_Inactive_Date,
X_EMPLOYEE_STATUS_ACTIVE_DATE => d_Status_Active_Date,
X_ASSIGNMENT_BEGIN_DATE => d_Assignment_Begin_Date,
X_ASSIGNMENT_END_DATE => d_Assignment_End_Date,
X_ATTRIBUTE_CATEGORY => p_attribute_category, -- Introduced DFF column parameters for bug 2908859
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_MODE => 'R'
);
Select DISTINCT a.batch_name batch_name
from PSP_PAYROLL_INTERFACE a,
PSP_PAYROLL_CONTROLS b
where a.batch_name = b.batch_name
and a.status_code <> 'T'
and b.SOURCE_TYPE = 'N'
and a.BATCH_NAME = v_Batch_Name
and b.business_group_id = v_business_group_id
and b.set_of_books_id = v_set_of_books_id;
SELECT ppi.batch_name batch_name
FROM psp_payroll_interface ppi
WHERE ppi.batch_name = v_batch_name
AND ppi.business_group_id = v_business_group_id
AND ppi.set_of_books_id = v_set_of_books_id
AND ppi.status_code <> 'T'
AND EXISTS (SELECT 1
FROM psp_payroll_controls ppc
WHERE ppc.batch_name = v_batch_name
AND ppc.SOURCE_TYPE = 'N'
AND ppc.business_group_id = v_business_group_id
AND ppc.set_of_books_id = v_set_of_books_id);
SELECT COUNT(DISTINCT(NVL(currency_code,'*')))
FROM PSP_PAYROLL_INTERFACE
WHERE batch_name = v_batch_name
AND business_group_id = v_business_group_id
AND set_of_books_id = v_set_of_books_id;
SELECT DISTINCT(NVL(currency_code,g_bg_currency_code))
FROM PSP_PAYROLL_INTERFACE
WHERE batch_name = v_batch_name
AND business_group_id = v_business_group_id
AND set_of_books_id = v_set_of_books_id
AND rownum = 1;