[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT paf.assignment_number,
ppf.employee_number
FROM
per_assignments_f paf,per_people_f ppf
WHERE paf.assignment_id =p_assignment_id
AND paf.person_id =ppf.person_id
and paf.assignment_type = 'E'
AND p_payroll_start_date between paf.effective_start_date and paf.effective_end_date
AND p_payroll_start_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT payroll_control_id,
source_type, /* Bug 1874696 Introduced source type */
currency_code -- Introduced for bug fix 2916848
FROM psp_payroll_controls
WHERE cdl_payroll_Action_id = g_payroll_action_id;
SELECT ptp.end_date, --- added for 2663344
ppl.payroll_line_id,
ppl.assignment_id,
ppl.element_type_id,
ppl.dr_cr_flag,
nvl(ppl.accounting_date, ppl.effective_Date) effective_date,
ppsl.payroll_sub_line_id,
ppsl.sub_line_start_date,
ppsl.sub_line_end_date,
ppl.cost_id, --- 5090002
DECODE(g_dff_grouping_option, 'Y', ppsl.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', ppsl.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', ppsl.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', ppsl.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', ppsl.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', ppsl.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', ppsl.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', ppsl.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', ppsl.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', ppsl.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', ppsl.attribute10, NULL) attribute10,
round(ppsl.daily_rate, g_ext_precision) daily_rate, -- Get only 2 decimal places Modified to currency extended precision for bug fix 2916848
round(ppsl.pay_amount, g_precision) pay_amount, -- Bug 2916848: Modified to currency precision (from 2)
ppl.person_id,
ppl.payroll_action_type,
or_gl_code_combination_id,
or_project_id,
or_task_id,
or_award_id,
or_expenditure_org_id,
or_expenditure_type
FROM psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
per_time_periods ptp,
psp_payroll_controls ppc
WHERE ppl.payroll_line_id = ppsl.payroll_line_id
AND ppl.status_code = 'N'
AND ppsl.pay_amount <> 0
AND ppl.payroll_control_id = p_payroll_control_id
AND ppc.time_period_id = ptp.time_period_id
AND ppc.payroll_control_id = ppl.payroll_control_id
AND ppl.assignment_id between p_start_asg_id and p_end_asg_id
ORDER BY ppl.person_id,ppl.assignment_id,ppl.payroll_line_id, ppl.element_type_id;
select count(1)
from psp_adjustment_lines_history
where assignment_id = payroll_rec.assignment_id
and distribution_date = l_payroll_start_date
and adjustment_batch_name is null
and reversal_entry_flag is null
and element_type_id = payroll_rec.element_type_id
and original_line_flag = 'N';
select project_id,
task_id,
award_id,
expenditure_type,
expenditure_organization_id,
gl_code_combination_id,
sum(decode(dr_Cr_Flag, 'D', distribution_amount, - distribution_amount)) sum_dist_amount,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10
from ( select psl.project_id,
psl.task_id,
psl.award_id,
psl.expenditure_organization_id,
psl.expenditure_type,
psl.gl_code_combination_id,
pdl.distribution_amount,
psl.dr_Cr_flag,
pdl.attribute_category,
pdl.attribute1,
pdl.attribute2,
pdl.attribute3,
pdl.attribute4,
pdl.attribute5,
pdl.attribute6,
pdl.attribute7,
pdl.attribute8,
pdl.attribute9,
pdl.attribute10
from psp_summary_lines psl,
psp_distribution_lines_history pdl,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl
where pdl.summary_line_id = psl.summary_line_id
and pdl.reversal_entry_flag is null
and pdl.adjustment_batch_name is null
and psl.person_id = payroll_rec.person_id
and psl.assignment_id = payroll_rec.assignment_id
and pdl.distribution_date = l_payroll_start_date
and pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
and ppsl.payroll_line_id = ppl.payroll_line_id
and ppl.element_type_id = payroll_rec.element_type_id
union all
select project_id,
task_id,
award_id,
expenditure_organization_id,
expenditure_type,
gl_code_combination_id,
distribution_amount,
dr_Cr_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10
from psp_adjustment_lines_history
where reversal_entry_flag is null
and adjustment_batch_name is null
and assignment_id = payroll_rec.assignment_id
and distribution_date = l_payroll_start_date
and original_line_flag = 'N'
and element_type_id = payroll_rec.element_type_id)
group by project_id,
task_id,
award_id,
expenditure_organization_id,
expenditure_type,
gl_code_combination_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10;
select nvl(parameter_value, 'P') parameter_value
from PAY_ACTION_PARAMETER_VALUES
where parameter_name = 'TGL_REVB_ACC_DATE'
and action_parameter_group_id = l_action_parameter_group;
update psp_payroll_sub_lines
set daily_rate = l_daily_rate
where payroll_sub_line_id = payroll_rec.payroll_sub_line_id;
INSERT INTO PSP_DISTRIBUTION_LINES(
DISTRIBUTION_LINE_ID,
PAYROLL_SUB_LINE_ID,
DISTRIBUTION_DATE,
EFFECTIVE_DATE,
DISTRIBUTION_AMOUNT,
STATUS_CODE,
GL_PROJECT_FLAG,
business_group_id,
set_of_books_id,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
adj_account_flag,
CAP_EXCESS_GLCCID ,
CAP_EXCESS_PROJECT_ID,
CAP_EXCESS_AWARD_ID,
CAP_EXCESS_TASK_ID,
CAP_EXCESS_EXP_ORG_ID,
CAP_EXCESS_EXP_TYPE)
VALUES(
PSP_DISTRIBUTION_LINES_S.NEXTVAL,
payroll_rec.PAYROLL_SUB_LINE_ID,
L_payroll_start_DATE,
L_EFFECTIVE_DATE,
L_DIST_AMOUNT,
'N',
L_GL_PROJECT_FLAG,
P_BUSINESS_GROUP_ID,
P_SET_OF_BOOKS_ID,
decode(l_adj_dff_flag,'Y', attribute_category_array(i), payroll_rec.attribute_category),
decode(l_adj_dff_flag,'Y', attribute1_array(i), payroll_rec.attribute1),
decode(l_adj_dff_flag,'Y', attribute2_array(i), payroll_rec.attribute2),
decode(l_adj_dff_flag,'Y', attribute3_array(i), payroll_rec.attribute3),
decode(l_adj_dff_flag,'Y', attribute4_array(i), payroll_rec.attribute4),
decode(l_adj_dff_flag,'Y', attribute5_array(i), payroll_rec.attribute5),
decode(l_adj_dff_flag,'Y', attribute6_array(i), payroll_rec.attribute6),
decode(l_adj_dff_flag,'Y', attribute7_array(i), payroll_rec.attribute7),
decode(l_adj_dff_flag,'Y', attribute8_array(i), payroll_rec.attribute8),
decode(l_adj_dff_flag,'Y', attribute9_array(i), payroll_rec.attribute9),
decode(l_adj_dff_flag,'Y', attribute10_array(i), payroll_rec.attribute10),
'Y',
gl_ccid_array(i),
project_id_array(i),
award_id_array(i),
Task_id_array(i),
exp_org_array(i),
exp_type_array(i));
UPDATE psp_distribution_lines
SET distribution_amount = distribution_amount +
(payroll_rec.pay_amount - g_tot_dist_amount)
WHERE distribution_line_id = (select max(distribution_line_id) -- 2470954 added this SQL
from psp_distribution_lines
where payroll_rec.payroll_sub_line_id = payroll_sub_line_id);
-- dbms_output.put_line('after update ');
update_dist_schedule_autopop(p_payroll_control_id =>payroll_control_rec.payroll_control_id, p_business_group_id=>p_business_group_id, p_set_of_books_id=>p_Set_of_books_id,p_start_asg_id=>p_start_asg_id, p_end_asg_id=>p_end_asg_id,
p_return_Status =>l_return_status);
update_dist_odls_autopop(p_payroll_control_id => payroll_control_rec.payroll_control_id, p_business_group_id=>p_business_group_id, p_set_of_books_id =>p_set_of_books_id,p_start_asg_id=>p_start_asg_id, p_end_asg_id=>p_end_asg_id,
p_return_status => l_return_status);
SELECT element_account_id,
peta.gl_code_combination_id,
peta.project_id,
peta.task_id,
peta.award_id,
round(peta.percent,2) percent,
peta.expenditure_type,
peta.expenditure_organization_id,
peta.start_date_active,
peta.end_date_active, --- added decode below for 5014193
decode(g_dff_grouping_option, 'Y', NVL(p_attribute_category, peta.attribute_category)) attribute_category, -- Introduced DFF columns for bug fix 2908859
decode(g_dff_grouping_option, 'Y', NVL(p_attribute1, peta.attribute1)) attribute1,
decode(g_dff_grouping_option, 'Y', NVL(p_attribute2, peta.attribute2)) attribute2,
decode(g_dff_grouping_option, 'Y', NVL(p_attribute3, peta.attribute3)) attribute3,
decode(g_dff_grouping_option, 'Y', NVL(p_attribute4, peta.attribute4)) attribute4,
decode(g_dff_grouping_option, 'Y', NVL(p_attribute5, peta.attribute5)) attribute5,
decode(g_dff_grouping_option, 'Y', NVL(p_attribute6, peta.attribute6)) attribute6,
decode(g_dff_grouping_option, 'Y', NVL(p_attribute7, peta.attribute7)) attribute7,
decode(g_dff_grouping_option, 'Y', NVL(p_attribute8, peta.attribute8)) attribute8,
decode(g_dff_grouping_option, 'Y', NVL(p_attribute9, peta.attribute9)) attribute9,
decode(g_dff_grouping_option, 'Y', NVL(p_attribute10, peta.attribute10)) attribute10
FROM psp_element_type_accounts peta
WHERE business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND peta.element_type_id = p_element_type_id
AND p_payroll_start_date BETWEEN peta.start_date_active AND
nvl(peta.end_date_active,p_payroll_start_date) ;
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
g_gl_EFFECTIVE_DATE, -- replaced p_effective_date -- Bug 2663344
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
NULL, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
L_ELEMENT_ACCOUNT_ID, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-line_id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date Bug 1874696 Changed from l_Effective_date
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
NULL, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
l_element_account_id, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date changed from l_effective_date Bug 1874696
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
NULL, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
L_ELEMENT_ACCOUNT_ID, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
SELECT psl.schedule_line_id,
psl.gl_code_combination_id,
psl.project_id,
psl.task_id,
psl.award_id,
round(psl.schedule_percent,2) schedule_percent,
psl.expenditure_type,
psl.expenditure_organization_id,
psl.schedule_begin_date,
nvl(psl.schedule_begin_date,p_payroll_start_date),
---- added decode for 5014193
decode(g_dff_grouping_option, 'Y',NVL(p_attribute_category, psl.attribute_category)) attribute_category, -- Introduced DFF columns for bug fix 2908859
decode(g_dff_grouping_option, 'Y',NVL(p_attribute1, psl.attribute1)) attribute1,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute2, psl.attribute2)) attribute2,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute3, psl.attribute3)) attribute3,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute4, psl.attribute4)) attribute4,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute5, psl.attribute5)) attribute5,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute6, psl.attribute6)) attribute6,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute7, psl.attribute7)) attribute7,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute8, psl.attribute8)) attribute8,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute9, psl.attribute9)) attribute9,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute10, psl.attribute10)) attribute10
FROM psp_schedule_hierarchy psh,
psp_schedule_lines psl
WHERE psh.business_group_id = p_business_group_id
AND psh.set_of_books_id = p_set_of_books_id
AND psh.business_group_id = psl.business_group_id
AND psh.set_of_books_id = psl.set_of_books_id
AND psh.assignment_id = p_assignment_id
AND psh.element_type_id = p_element_type_id
AND psh.schedule_hierarchy_id = psl.schedule_hierarchy_id
AND p_payroll_start_date BETWEEN psl.schedule_begin_date AND
nvl(psl.schedule_end_date,p_payroll_start_date)
AND psl.default_flag IS NULL;
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
G_GL_EFFECTIVE_DATE, -- changed from p_effective_date Bug 2663344
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
L_SCHEDULE_LINE_ID, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date Changed from l_effective_date Bug 1874696
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
L_SCHEDULE_LINE_ID, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
-- Auto-Population is OFF. Get project status and insert record.
-- modified as per 11i changes
-- dbms_output.put_line('Project id 2 '|| to_char(l_project_id));
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date Changed from l_effective_date Bug 1874696
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
L_SCHEDULE_LINE_ID, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
SELECT psl.schedule_line_id,
psl.gl_code_combination_id,
psl.project_id,
psl.task_id,
psl.award_id,
round(psl.schedule_percent,2) schedule_percent,
psl.expenditure_type,
psl.expenditure_organization_id,
pet.start_date_active,
nvl(pet.end_date_active,p_payroll_start_date),
---- introduced decode for 5014193
decode(g_dff_grouping_option, 'Y',NVL(p_attribute_category, psl.attribute_category)) attribute_category, -- Introduced DFF columns for bug fix 2908859
decode(g_dff_grouping_option, 'Y',NVL(p_attribute1, psl.attribute1)) attribute1,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute2, psl.attribute2)) attribute2,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute3, psl.attribute3)) attribute3,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute4, psl.attribute4)) attribute4,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute5, psl.attribute5)) attribute5,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute6, psl.attribute6)) attribute6,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute7, psl.attribute7)) attribute7,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute8, psl.attribute8)) attribute8,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute9, psl.attribute9)) attribute9,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute10, psl.attribute10)) attribute10
FROM psp_element_types pet,
psp_group_element_list pgel,
psp_schedule_hierarchy psh,
psp_schedule_lines psl
WHERE pet.element_type_id = p_element_type_id
-- Introduced the following BG/SOB check for bug fix 3098050
AND pet.business_group_id = p_business_group_id
AND pet.set_of_books_id = p_set_of_books_id
AND p_payroll_start_date BETWEEN pet.start_date_active AND
nvl(pet.end_date_active,p_payroll_start_date)
AND pet.element_type_id = pgel.element_type_id
AND pet.start_date_active = pgel.start_date_active
AND pgel.element_group_id = psh.element_group_id
AND psl.business_group_id = p_business_group_id
AND psl.set_of_books_id = p_set_of_books_id
AND psh.assignment_id = p_assignment_id
AND psh.schedule_hierarchy_id = psl.schedule_hierarchy_id
AND p_payroll_start_date BETWEEN psl.schedule_begin_date AND
nvl(psl.schedule_end_date,p_payroll_start_date);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
G_GL_EFFECTIVE_DATE, -- Changed from P_Effective_date Bug 2663344
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
L_SCHEDULE_LINE_ID, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date,-- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date Changed from l_Effective_date Bug 1874696
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
L_SCHEDULE_LINE_ID, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date,-- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
-- Auto-Population is OFF. Get project status and insert record.
-- modified as per 11i changes
-- dbms_output.put_line('Project id 3 '|| to_char(l_project_id));
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date Changed from l_effective_date Bug 1874696
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
L_SCHEDULE_LINE_ID, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id , -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business_group_id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
SELECT psl.schedule_line_id,
psl.gl_code_combination_id,
psl.project_id,
psl.task_id,
psl.award_id,
round(psl.schedule_percent,2) schedule_percent,
psl.expenditure_type,
psl.expenditure_organization_id,
psl.schedule_begin_date,
nvl(psl.schedule_end_date,p_payroll_start_date),
decode(g_dff_grouping_option, 'Y',NVL(p_attribute_category, psl.attribute_category)) attribute_category, -- Introduced DFF columns for bug fix 2908859
decode(g_dff_grouping_option, 'Y',NVL(p_attribute1, psl.attribute1)) attribute1,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute2, psl.attribute2)) attribute2,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute3, psl.attribute3)) attribute3,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute4, psl.attribute4)) attribute4,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute5, psl.attribute5)) attribute5,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute6, psl.attribute6)) attribute6,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute7, psl.attribute7)) attribute7,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute8, psl.attribute8)) attribute8,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute9, psl.attribute9)) attribute9,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute10, psl.attribute10)) attribute10
FROM psp_schedule_hierarchy psh,
psp_schedule_lines psl
WHERE psh.business_group_id = p_business_group_id
AND psh.set_of_books_id = p_set_of_books_id
AND psh.scheduling_types_code = 'A'
AND psh.element_group_id IS NULL
AND psh.element_type_id IS NULL
AND psh.assignment_id = p_assignment_id
AND psh.schedule_hierarchy_id = psl.schedule_hierarchy_id
AND psl.business_group_id = psh.business_group_id
AND psl.set_of_books_id = psh.set_of_books_id
AND p_payroll_start_date BETWEEN psl.schedule_begin_date AND
nvl(psl.schedule_end_date,p_payroll_start_date);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
G_GL_EFFECTIVE_DATE, -- changed fron p_effective date --- Bug 2663344
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
L_SCHEDULE_LINE_ID, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-line_id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date Bug 1874696 Changed from l_Effective_date
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
L_SCHEDULE_LINE_ID, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
-- Auto-Population is OFF. Get project status and insert record.
-- modified as per 11i changes
-- dbms_output.put_line('Project id 4 '|| to_char(l_project_id));
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-line_id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date Bug 1874696 Changed from l_Effective_date
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
L_SCHEDULE_LINE_ID, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
SELECT pdls.org_schedule_id,
pdls.gl_code_combination_id,
pdls.project_id,
pdls.task_id,
pdls.award_id,
round(pdls.schedule_percent,2) schedule_percent,
pdls.expenditure_type,
pdls.expenditure_organization_id,
pdls.schedule_begin_date,
nvl(pdls.schedule_end_date,p_payroll_start_date),
--- introduced decode for 5014193
decode(g_dff_grouping_option, 'Y',NVL(p_attribute_category, pdls.attribute_category)) attribute_category, -- Introduced DFF columns for bug fix 2908859
decode(g_dff_grouping_option, 'Y',NVL(p_attribute1, pdls.attribute1)) attribute1,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute2, pdls.attribute2)) attribute2,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute3, pdls.attribute3)) attribute3,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute4, pdls.attribute4)) attribute4,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute5, pdls.attribute5)) attribute5,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute6, pdls.attribute6)) attribute6,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute7, pdls.attribute7)) attribute7,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute8, pdls.attribute8)) attribute8,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute9, pdls.attribute9)) attribute9,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute10, pdls.attribute10)) attribute10
FROM per_assignments_f paf,
psp_default_labor_schedules pdls
WHERE pdls.business_group_id = p_business_group_id
AND pdls.set_of_books_id = p_set_of_books_id
and paf.assignment_type = 'E'
AND pdls.organization_id = paf.organization_id
AND paf.assignment_id = p_assignment_id
AND p_payroll_start_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND p_payroll_start_date BETWEEN pdls.schedule_begin_date AND
nvl(pdls.schedule_end_date,p_payroll_start_date);
hr_utility.trace('inserting dist line for g_org_schedule_autopop ='||g_org_schedule_autopop);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
G_GL_EFFECTIVE_DATE, -- changed from p_effective date Bug 2663344
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
NULL, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
L_ORG_SCHEDULE_ID, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date Bug 1874696
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
NULL, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
L_ORG_SCHEDULE_ID, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date Bug 1874696
L_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
NULL, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
L_ORG_SCHEDULE_ID, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.task_id,
poa.award_id,
poa.expenditure_type,
poa.expenditure_organization_id,
poa.start_date_active,
nvl(poa.end_date_active,p_payroll_start_date), --- decode for 5014193
decode(g_dff_grouping_option, 'Y',NVL(p_attribute_category, poa.attribute_category)) attribute_category, -- Introduced DFF columns for bug fix 2908859
decode(g_dff_grouping_option, 'Y',NVL(p_attribute1, poa.attribute1)) attribute1,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute2, poa.attribute2)) attribute2,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute3, poa.attribute3)) attribute3,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute4, poa.attribute4)) attribute4,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute5, poa.attribute5)) attribute5,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute6, poa.attribute6)) attribute6,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute7, poa.attribute7)) attribute7,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute8, poa.attribute8)) attribute8,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute9, poa.attribute9)) attribute9,
decode(g_dff_grouping_option, 'Y',NVL(p_attribute10, poa.attribute10)) attribute10
FROM per_assignments_f paf,
psp_organization_accounts poa
WHERE poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
and paf.assignment_type = 'E'
AND paf.assignment_id = p_assignment_id
AND p_payroll_start_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND poa.organization_id = paf.organization_id
AND poa.account_type_code ||' '= 'D' ||' '
AND p_payroll_start_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_payroll_start_date);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
G_GL_EFFECTIVE_DATE, -- effective date Changed from p_effective_date Bug 2663344
ROUND(P_DAILY_RATE, g_precision),-- distribution amount Introduced rounding for bug 3109943
'N', -- status code
NULL, -- suspense reason code
P_DEFAULT_REASON_CODE, -- default reason code
NULL, -- schedule line id
L_ORGANIZATION_ACCOUNT_ID, -- default organization a/c
NULL, -- suspense organization a/c
NULL , -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-line_id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date Bug 1874696 Changed from l_Effective_date
ROUND(P_DAILY_RATE, g_precision),-- distribution amount Introduced rounding for bug 3109943
'N', -- status code
NULL, -- suspense reason code
p_default_Reason_code, -- default reason code
NULL, -- schedule line id
l_organization_account_id, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date Bug 1874696 changed from l_Effective_date
ROUND(P_DAILY_RATE, g_precision),-- distribution amount Introduced rounding for bug 3109943
'N', -- status code
NULL, -- suspense reason code
P_DEFAULT_REASON_CODE, -- default reason code
NULL, -- schedule line id
L_ORGANIZATION_ACCOUNT_ID, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
NULL, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
SELECT hou.organization_id,
hou.name
FROM hr_organization_units hou,
per_assignments_f paf
WHERE paf.business_group_id = p_business_group_id
AND paf.assignment_id = p_assignment_id
and paf.assignment_type = 'E'
AND paf.business_group_id = hou.business_group_id
AND p_payroll_start_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND hou.organization_id = paf.organization_id
AND p_payroll_start_date between date_from and nvl(date_to,p_payroll_start_date);
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.task_id,
poa.award_id,
poa.expenditure_type,
poa.expenditure_organization_id,
poa.start_date_active,
nvl(poa.end_date_active,p_payroll_start_date), ---- introduced decode below for 5014193
decode(g_dff_grouping_option, 'Y', poa.attribute_category),-- Introduced DFF columns for bug fix 2908859
decode(g_dff_grouping_option, 'Y',poa.attribute1),
decode(g_dff_grouping_option, 'Y',poa.attribute2),
decode(g_dff_grouping_option, 'Y',poa.attribute3),
decode(g_dff_grouping_option, 'Y',poa.attribute4),
decode(g_dff_grouping_option, 'Y',poa.attribute5),
decode(g_dff_grouping_option, 'Y',poa.attribute6),
decode(g_dff_grouping_option, 'Y',poa.attribute7),
decode(g_dff_grouping_option, 'Y',poa.attribute8),
decode(g_dff_grouping_option, 'Y',poa.attribute9),
decode(g_dff_grouping_option, 'Y',poa.attribute10)
FROM psp_organization_accounts poa
WHERE business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND poa.organization_id = l_organization_id
AND poa.account_type_code = l_account_type_code
AND p_payroll_start_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_payroll_start_date);
SELECT full_name
FROM per_people_f
WHERE person_id =p_person_id;
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.task_id,
poa.award_id,
poa.expenditure_type,
poa.expenditure_organization_id
FROM psp_organization_accounts poa
WHERE poa.account_type_code = 'G'
AND p_payroll_start_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_payroll_start_date);
SELECT gl_code_combination_id,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_organization_id,
start_date_active,
end_date_active,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10
INTO l_gl_code_combination_id,
l_project_id,
l_task_id,
l_award_id,
l_expenditure_type,
l_expenditure_org_id,
l_effective_start_date,
l_effective_end_date,
l_attribute_category, -- Introduced DFF variables for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10
FROM psp_organization_accounts
WHERE organization_account_id = l_organization_account_id;
update psp_distribution_lines set
suspense_org_account_id = l_organization_account_id,
suspense_reason_code= p_suspense_reason_code,
gl_project_flag = l_gl_project_flag,
effective_date = g_gl_effective_date, --- added for 2663344
attribute_category = l_attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1 = l_attribute1,
attribute2 = l_attribute2,
attribute3 = l_attribute3,
attribute4 = l_attribute4,
attribute5 = l_attribute5,
attribute6 = l_attribute6,
attribute7 = l_attribute7,
attribute8 = l_attribute8,
attribute9 = l_attribute9,
attribute10 = l_attribute10
where distribution_line_id = p_dist_line_id;
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
G_GL_EFFECTIVE_DATE, -- effective_date changed from p_Effective_date Bug 2663344
ROUND(P_DAILY_RATE, g_precision),-- distribution amount. Introduced rounding for bug 3109943
'N', -- status code
P_SUSPENSE_REASON_CODE, -- suspense reason code
NULL, -- default reason code
P_SCHEDULE_LINE_ID, -- schedule line id
P_DEFAULT_ORG_ACCOUNT_ID, -- default organization a/c
L_ORGANIZATION_ACCOUNT_ID, -- suspense organization a/c
P_ELEMENT_ACCOUNT_ID, -- global element type
P_ORG_SCHEDULE_ID, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective_date changed from p_Effective_date Bug 2663344
ROUND(P_DAILY_RATE, g_precision),-- distribution amount. Introduced rounding for bug 3109943
'N', -- status code
P_SUSPENSE_REASON_CODE, -- suspense reason code
NULL, -- default reason code
P_SCHEDULE_LINE_ID, -- schedule line id
P_DEFAULT_ORG_ACCOUNT_ID, -- default organization a/c
L_ORGANIZATION_ACCOUNT_ID, -- suspense organization a/c
P_ELEMENT_ACCOUNT_ID, -- global element type
P_ORG_SCHEDULE_ID, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
update psp_distribution_lines set
suspense_reason_code= p_suspense_reason_code,
suspense_org_Account_id= l_organization_account_id,
gl_project_flag = l_gl_project_flag,
effective_date = p_effective_date,
attribute_category = l_attribute_category,
attribute1 = l_attribute1,
attribute2 = l_attribute2,
attribute3 = l_attribute3,
attribute4 = l_attribute4,
attribute5 = l_attribute5,
attribute6 = l_attribute6,
attribute7 = l_attribute7,
attribute8 = l_attribute8,
attribute9 = l_attribute9,
attribute10 = l_attribute10
where distribution_line_id=p_dist_line_id;
update psp_distribution_lines set
suspense_reason_code= p_suspense_reason_code,
suspense_org_Account_id= l_organization_account_id,
gl_project_flag = l_gl_project_flag,
effective_date = p_effective_date, --- added for 2663344
attribute_category = l_attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1 = l_attribute1,
attribute2 = l_attribute2,
attribute3 = l_attribute3,
attribute4 = l_attribute4,
attribute5 = l_attribute5,
attribute6 = l_attribute6,
attribute7 = l_attribute7,
attribute8 = l_attribute8,
attribute9 = l_attribute9,
attribute10 = l_attribute10
where distribution_line_id=p_dist_line_id;
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective_date Bug 1874696
ROUND(P_DAILY_RATE, g_precision),-- distribution amount. Introduced rounding for bug 3109943
'N', -- status code
P_SUSPENSE_REASON_CODE, -- suspense reason code
NULL, -- default reason code
P_SCHEDULE_LINE_ID, -- schedule line id
P_DEFAULT_ORG_ACCOUNT_ID, -- default organization a/c
L_ORGANIZATION_ACCOUNT_ID, -- suspense organization a/c
P_ELEMENT_ACCOUNT_ID, -- global element type
P_ORG_SCHEDULE_ID, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
l_gl_code_combination_id, -- gl_code_combination_id
l_project_id, -- project_id,
l_task_id , -- task_id,
l_award_id , -- award_id,
l_expenditure_org_id, -- expenditure org id
l_expenditure_type, -- expenditure_type
l_effective_start_date, -- Eff start date of schedule
l_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
-- insert the record in psp_distribution_lines
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date
P_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
P_SCHEDULE_LINE_ID, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
P_ORG_SCHEDULE_ID, -- org default schedule id
l_gl_project_flag, -- gl project flag
NULL, -- reversal entry flag
l_new_gl_code_combination_id, -- gl_code_combination_id
p_project_id, -- project_id,
p_task_id , -- task_id,
p_award_id , -- award_id,
p_expenditure_organization_id, -- expenditure org id
p_expenditure_type, -- expenditure_type
p_effective_start_date, -- Eff start date of schedule
p_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group Id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_autopop_results(
G_DIST_LINE_ID,
NULL,
L_NEW_GL_CODE_COMBINATION_ID,
l_return_status);
insert_into_distribution_lines(
P_SUB_LINE_ID, -- payroll sub-lines id
P_PAYROLL_START_DATE, -- distribution date
P_EFFECTIVE_DATE, -- effective date, changed from l_Effective_date for 1874696
P_DIST_AMOUNT, -- distribution amount
'N', -- status code
NULL, -- suspense reason code
NULL, -- default reason code
P_SCHEDULE_LINE_ID, -- schedule line id
NULL, -- default organization a/c
NULL, -- suspense organization a/c
NULL, -- global element type
P_ORG_SCHEDULE_ID, -- org default schedule id
L_GL_PROJECT_FLAG, -- gl project flag
NULL, -- reversal entry flag
p_gl_code_combination_id, -- gl_code_combination_id
p_project_id, -- project_id,
p_task_id , -- task_id,
p_award_id , -- award_id,
p_expenditure_organization_id, -- expenditure org id
l_new_expenditure_type, -- expenditure_type
p_effective_start_date, -- Eff start date of schedule
p_effective_end_date, -- Eff start date of schedule
p_mode, -- 'I' for LD ,'R' for others
p_business_group_id, -- Business Group id
p_set_of_books_id, -- Set of Books Id
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
insert_into_autopop_results(
G_DIST_LINE_ID,
L_NEW_EXPENDITURE_TYPE,
NULL,
l_return_status);
PROCEDURE insert_into_distribution_lines(
L_PAYROLL_SUB_LINE_ID IN NUMBER,
L_DISTRIBUTION_DATE IN DATE,
L_EFFECTIVE_DATE IN DATE,
L_DISTRIBUTION_AMOUNT IN NUMBER,
L_STATUS_CODE IN VARCHAR2,
L_SUSPENSE_REASON_CODE IN VARCHAR2,
L_DEFAULT_REASON_CODE IN VARCHAR2,
L_SCHEDULE_LINE_ID IN NUMBER,
L_DEFAULT_ORG_ACCOUNT_ID IN NUMBER,
L_SUSPENSE_ORG_ACCOUNT_ID IN NUMBER,
L_ELEMENT_ACCOUNT_ID IN NUMBER,
L_ORG_SCHEDULE_ID IN NUMBER,
L_GL_PROJECT_FLAG IN VARCHAR2,
L_REVERSAL_ENTRY_FLAG IN VARCHAR2,
P_GL_CODE_COMBINATION_ID IN NUMBER, -- := FND_API.G_MISS_NUM, Commented initialization for bug fix 2916848
P_PROJECT_ID IN NUMBER, -- := FND_API.G_MISS_NUM, Commented initialization for bug fix 2916848
P_TASK_ID IN NUMBER, -- := FND_API.G_MISS_NUM, Commented initialization for bug fix 2916848
P_AWARD_ID IN NUMBER, -- := FND_API.G_MISS_NUM, Commented initialization for bug fix 2916848
P_EXPENDITURE_ORGANIZATION_ID IN NUMBER, -- := FND_API.G_MISS_NUM, Commented initialization for bug fix 2916848
P_EXPENDITURE_TYPE IN VARCHAR2, -- := FND_API.G_MISS_CHAR, Commented initialization for bug fix 2916848
P_EFFECTIVE_START_DATE IN DATE, -- := FND_API.G_MISS_DATE, Commented initialization for bug fix 2916848
P_EFFECTIVE_END_DATE IN DATE, -- := FND_API.G_MISS_DATE, Commented initialization for bug fix 2916848
P_MODE IN VARCHAR2 := 'I',
P_BUSINESS_GROUP_ID IN NUMBER,
P_SET_OF_BOOKS_ID IN NUMBER,
p_attribute_category IN VARCHAR2, -- Introduced DFF parameters for bug fix 2908859
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
P_RETURN_STATUS OUT NOCOPY VARCHAR2,
P_CAP_EXCESS_GLCCID IN NUMBER DEFAULT NULL,
P_CAP_EXCESS_PROJECT_ID IN NUMBER DEFAULT NULL,
P_CAP_EXCESS_TASK_ID IN NUMBER DEFAULT NULL,
P_CAP_EXCESS_AWARD_ID IN NUMBER DEFAULT NULL,
P_CAP_EXCESS_EXP_ORG_ID IN NUMBER DEFAULT NULL,
P_CAP_EXCESS_EXP_TYPE IN VARCHAR2 DEFAULT NULL ) IS
-- l_description VARCHAR2(180); Commented for bug fix 2447912
INSERT INTO PSP_DISTRIBUTION_LINES(
DISTRIBUTION_LINE_ID,
PAYROLL_SUB_LINE_ID,
DISTRIBUTION_DATE,
EFFECTIVE_DATE,
DISTRIBUTION_AMOUNT,
STATUS_CODE,
SUSPENSE_REASON_CODE,
DEFAULT_REASON_CODE,
SCHEDULE_LINE_ID,
DEFAULT_ORG_ACCOUNT_ID,
SUSPENSE_ORG_ACCOUNT_ID,
ELEMENT_ACCOUNT_ID,
ORG_SCHEDULE_ID,
GL_PROJECT_FLAG,
REVERSAL_ENTRY_FLAG,
business_group_id,
set_of_books_id,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
CAP_EXCESS_GLCCID,
CAP_EXCESS_PROJECT_ID,
CAP_EXCESS_AWARD_ID,
CAP_EXCESS_TASK_ID,
CAP_EXCESS_EXP_ORG_ID,
CAP_EXCESS_EXP_TYPE)
VALUES(
PSP_DISTRIBUTION_LINES_S.NEXTVAL,
L_PAYROLL_SUB_LINE_ID,
L_DISTRIBUTION_DATE,
L_EFFECTIVE_DATE,
L_DISTRIBUTION_AMOUNT,
L_STATUS_CODE,
L_SUSPENSE_REASON_CODE,
L_DEFAULT_REASON_CODE,
L_SCHEDULE_LINE_ID,
L_DEFAULT_ORG_ACCOUNT_ID,
L_SUSPENSE_ORG_ACCOUNT_ID,
L_ELEMENT_ACCOUNT_ID,
L_ORG_SCHEDULE_ID,
L_GL_PROJECT_FLAG,
L_REVERSAL_ENTRY_FLAG,
P_BUSINESS_GROUP_ID,
P_SET_OF_BOOKS_ID,
p_attribute_category, -- Introduced DFF columns for bug fix 2908859
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
P_CAP_EXCESS_GLCCID,
P_CAP_EXCESS_PROJECT_ID,
P_CAP_EXCESS_AWARD_ID,
P_CAP_EXCESS_TASK_ID,
P_CAP_EXCESS_EXP_ORG_ID,
P_CAP_EXCESS_EXP_TYPE);
fnd_msg_pub.add_exc_msg('PSB_LABOR_DIST','INSERT_INTO_DISTRIBUTION_LINES');
END insert_into_distribution_lines;
PROCEDURE insert_into_autopop_results(
P_DISTRIBUTION_LINE_ID IN NUMBER,
P_NEW_EXPENDITURE_TYPE IN VARCHAR2,
P_NEW_GL_CODE_COMBINATION_ID IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
--********************************************************************
INSERT INTO PSP_AUTO_POPULATION_RESULTS(
DISTRIBUTION_LINE_ID,
EXPENDITURE_TYPE,
GL_CODE_COMBINATION_ID)
VALUES(
P_DISTRIBUTION_LINE_ID,
P_NEW_EXPENDITURE_TYPE,
P_NEW_GL_CODE_COMBINATION_ID);
UPDATE PSP_DISTRIBUTION_LINES
SET auto_expenditure_type = p_new_expenditure_type,
auto_gl_code_combination_id = p_new_gl_code_combination_id
WHERE distribution_line_id = p_distribution_line_id;
fnd_msg_pub.add_exc_msg('PSB_LABOR_DIST','INSERT_INTO_AUTOPOP_RESULTS');
END insert_into_autopop_results;
select ppl.payroll_control_id
from psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl
where ppl.payroll_line_id = ppsl.payroll_line_id
and ppsl.payroll_sub_line_id = g_sub_line_id;
select pcv_information1 global_element_autopop,
pcv_information2 element_type_autopop,
pcv_information3 element_class_autopop,
pcv_information4 assignment_autopop,
pcv_information5 default_schedule_autopop,
pcv_information6 default_account_autopop,
pcv_information7 suspense_account,
pcv_information10 excess_account
from pqp_configuration_values
where pcv_information_category = 'PSP_ENABLE_AUTOPOPULATION'
and legislation_code is null
and nvl(business_group_id, p_business_group_id) = p_business_group_id;
select psp_payroll_lines_s.nextval,
psp_payroll_sub_lines_s.nextval,
psp_payroll_controls_s.nextval
into l_line_id,
g_sub_line_id,
l_control_id
from dual;
insert into psp_payroll_lines
(payroll_line_id,
set_of_books_id,
assignment_id,
person_id,
element_type_id,
pay_amount,
status_code,
payroll_control_id,
dr_cr_flag,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
effective_date)
select l_line_id,
p_set_of_books_id,
p_assignment_id,
person_id,
p_element_type_id,
p_daily_rate,
'N',
l_control_id,
'D',
sysdate,
0,
0,
0,
sysdate,
sysdate
from per_all_assignments_f
where assignment_id = p_assignment_id
and assignment_type = 'E'
and rownum = 1;
insert into psp_payroll_sub_lines
(payroll_sub_line_id,
sub_line_start_date,
sub_line_end_date,
pay_amount,
daily_rate,
salary_used,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
payroll_line_id)
values ( g_sub_line_id,
p_payroll_start_date,
p_payroll_start_date,
p_daily_rate,
p_daily_rate,
0,
sysdate,
0,
0,
0,
sysdate,
l_line_id);
select pdl.distribution_amount Amount,
nvl(poa2.start_date_active, nvl(psl.schedule_begin_date, nvl(poa.start_date_active,
nvl(peta.start_date_active, pdls.schedule_begin_date)))) start_date,
nvl(poa2.end_date_active, nvl(psl.schedule_end_date, nvl(poa.end_date_active,
nvl(peta.start_date_active, nvl(pdls.schedule_end_date, pdl.distribution_date))))) end_date,
DECODE(pdl.gl_project_flag, 'G', DECODE(pdl.suspense_org_Account_id, NULL,
nvl(pdl.auto_gl_code_combination_id,
nvl(psl.gl_code_combination_id,
nvl(poa.gl_code_combination_id,
nvl(peta.gl_code_combination_id,
pdls.gl_code_combination_id)))),
nvl(pdl.suspense_auto_glccid, poa2.gl_code_combination_id)), --5080403
NULL) glccid,
DECODE(pdl.gl_project_flag, 'P', DECODE(pdl.suspense_org_account_id, NULL,
nvl(psl.project_id,
nvl(poa.project_id,
nvl(peta.project_id,
pdls.project_id))),poa2.project_id),
NULL) project_id,
DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
nvl(psl.task_id,
nvl(poa.task_id,
nvl(peta.task_id,
pdls.task_id))),poa2.task_id),
NULL) task_id,
DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
nvl(psl.award_id,
nvl(poa.award_id,
nvl(peta.award_id,
pdls.award_id))),poa2.award_id),
NULL) award_id,
DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
nvl(pdl.auto_expenditure_type,
nvl(psl.expenditure_type,
nvl(poa.expenditure_type,
nvl(peta.expenditure_type,
pdls.expenditure_type)))),
nvl(pdl.suspense_auto_exp_type,poa2.expenditure_type)), --5080403
NULL) expenditure_type,
DECODE(pdl.gl_project_flag, 'P', decode(pdl.suspense_org_account_id, NULL,
nvl(psl.expenditure_organization_id,
nvl(poa.expenditure_organization_id,
nvl(peta.expenditure_organization_id,
pdls.expenditure_organization_id))),
poa2.expenditure_organization_id), NULL) exp_org_id
FROM Psp_distribution_lines pdl,
psp_payroll_sub_lines ppsl,
psp_payroll_lines ppl,
psp_schedule_lines psl,
psp_organization_accounts poa,
psp_element_type_accounts peta,
psp_default_labor_Schedules pdls,
psp_organization_accounts poa2
WHERE ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
AND ppsl.payroll_line_id = ppl.payroll_line_id
AND pdl.schedule_line_id = psl.schedule_line_id(+)
AND pdl.default_org_account_id = poa.organization_account_id(+)
AND pdl.element_account_id = peta.element_account_id(+)
AND pdl.org_schedule_id = pdls.org_schedule_id(+)
AND pdl.suspense_org_account_id = poa2.organization_account_id(+)
AND (pdl.reversal_entry_flag = 'N' OR pdl.reversal_entry_flag IS NULL)
AND pdl.status_code = 'N'
AND ppsl.payroll_sub_line_id = p_payroll_sub_line_id;
g_charging_instructions.delete;
insert_into_distribution_lines
(P_SUB_LINE_ID,
P_PAYROLL_START_DATE,
g_gl_EFFECTIVE_DATE,
ROUND(p_daily_rate, g_precision),
'N',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'G',
NULL,
p_or_gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
p_mode,
p_business_group_id,
p_set_of_books_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
l_return_status,
p_or_gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL);
insert_into_distribution_lines
(P_SUB_LINE_ID,
P_PAYROLL_START_DATE,
P_EFFECTIVE_DATE,
ROUND(p_daily_rate, g_precision),
'N',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'P',
NULL,
NULL,
p_or_project_id,
p_or_task_id ,
p_or_award_id ,
p_or_expenditure_org_id,
p_or_expenditure_type,
NULL,
NULL,
p_mode,
p_business_group_id,
p_set_of_books_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
l_return_status,
NULL,
p_or_project_id,
p_or_task_id,
p_or_award_id,
p_or_expenditure_org_id,
p_or_expenditure_type);
update_dist_schedule_autopop(p_payroll_control_id =>l_payroll_control_id,
p_business_group_id=>p_business_group_id,
p_set_of_books_id=>p_Set_of_books_id,
p_start_asg_id=>p_assignment_id,
p_end_asg_id=>p_assignment_id,
p_return_Status =>l_return_status);
update_dist_odls_autopop(p_payroll_control_id => l_payroll_control_id,
p_business_group_id=> p_business_group_id,
p_set_of_books_id => p_set_of_books_id,
p_start_asg_id=> p_assignment_id,
p_end_asg_id=> p_assignment_id,
p_return_status => l_return_status);
PROCEDURE update_dist_schedule_autopop(p_payroll_control_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_start_asg_id in integer,
p_end_asg_id in integer,
p_return_status OUT NOCOPY VARCHAR2) IS
CURSOR autopop_exc_cur(P_PAYROLL_CONTROL_ID IN NUMBER) IS
SELECT pdl.payroll_sub_line_id ,
max(pdl.distribution_date) max_dist_date, --5592789
max(pdl.effective_date) effective_date,
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id,
psl.project_id,
psl.expenditure_organization_id,
psl.expenditure_type,
psl.task_id,
psl.award_id,
psl.gl_code_combination_id,
pdl.schedule_line_id schedule_line_id,
ppl.cost_id,
ppl.payroll_action_type
FROM psp_distribution_lines pdl,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_Schedule_lines psl,
psp_schedule_hierarchy psh
WHERE
ppl.payroll_control_id = p_payroll_control_id
AND ppl.payroll_line_id = ppsl.payroll_line_id
and ppsl.payroll_sub_line_id=pdl.payroll_sub_line_id
and pdl.suspense_org_account_id is null
and pdl.schedule_line_id=psl.schedule_line_id
and ppl.assignment_id between p_start_asg_id and p_end_asg_id
and psh.schedule_hierarchy_id = psl.schedule_hierarchy_id
and psh.scheduling_types_code in (decode(g_asg_autopop,'Y','A') ,
decode(g_asg_element_autopop,'Y','ET'),
decode(g_asg_ele_group_autopop,'Y','EG'))
and pdl.adj_account_flag is null
group by
ppl.cost_id,
ppl.payroll_action_type,
pdl.payroll_Sub_line_id,
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id ,
psl.PROJECT_ID ,
psl.EXPENDITURE_ORGANIZATION_ID,
psl.EXPENDITURE_TYPE,
psl.TASK_ID,
psl.AWARD_ID,
psl.gl_code_combination_id,
pdl.schedule_line_id;
select distribution_line_id , effective_date, distribution_amount from psp_distribution_lines pdl
where payroll_sub_line_id= p_payroll_sub_line_id and
schedule_line_id= p_schedule_line_id
and suspense_org_account_id is null
order by effective_date;
insert into psp_stout values(l_dbg_ctr, 'inside schedule lines');
update psp_distribution_lines set auto_expenditure_type = l_new_expenditure_type
where distribution_line_id=l_dist_line_id;
update psp_distribution_lines set auto_expenditure_type = l_new_expenditure_type
where
distribution_line_id=l_dist_line_id;
update psp_distribution_lines set auto_gl_code_combination_id = l_new_gl_code_combination_id
where
payroll_sub_line_id=autopop_exc_rec.payroll_sub_line_id
and schedule_line_id =
autopop_exc_rec.schedule_line_id and suspense_org_account_id is null;
PROCEDURE update_dist_odls_autopop(p_payroll_control_id IN NUMBER,
p_business_group_id IN NUMBER,
p_Set_of_books_id IN NUMBER,
p_start_asg_id in integer,
p_end_asg_id in integer,
p_return_status OUT NOCOPY VARCHAR2) IS
CURSOR autopop_odls_cur(p_payroll_control_id in number) is
SELECT pdl.payroll_sub_line_id ,
max(pdl.distribution_date) max_dist_date,
max(pdl.effective_date) effective_date,
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id
,
pdls.project_id,
pdls.expenditure_organization_id,
pdls.expenditure_type,
pdls.task_id,
pdls.award_id,
pdls.gl_code_combination_id,
pdls.org_schedule_id,
ppl.cost_id,
ppl.payroll_action_type
FROM psp_distribution_lines pdl,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_default_labor_schedules pdls
WHERE
ppl.payroll_control_id = p_payroll_control_id
and ppl.assignment_id between p_start_asg_id and p_end_asg_id
AND ppl.payroll_line_id = ppsl.payroll_line_id
and ppsl.payroll_sub_line_id=pdl.payroll_sub_line_id
and pdl.suspense_org_account_id is null and
pdl.org_schedule_id= pdls.org_schedule_id
and pdl.adj_account_flag is null
group by
ppl.cost_id,
ppl.payroll_action_type,
pdl.payroll_Sub_line_id,
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id ,
pdls.PROJECT_ID,
pdls.EXPENDITURE_ORGANIZATION_ID,
pdls.EXPENDITURE_TYPE,
pdls.TASK_ID,
pdls.AWARD_ID,
pdls.gl_code_combination_id,
pdls.org_schedule_id;
select distribution_line_id , effective_date, distribution_amount from psp_distribution_lines pdl
where payroll_sub_line_id= autopop_odls_rec.payroll_sub_line_id and
org_schedule_id= autopop_odls_rec.org_schedule_id
and suspense_org_account_id is null
order by effective_date;
insert into psp_stout values(l_dbg_ctr,'inside odls');
update psp_distribution_lines set auto_expenditure_type = l_new_expenditure_type
where distribution_line_id =l_dist_line_id;
update psp_distribution_lines set auto_expenditure_type = l_new_expenditure_type
where
distribution_line_id =l_dist_line_id;
update psp_distribution_lines set auto_gl_code_combination_id = l_new_gl_code_combination_id
where
payroll_sub_line_id=autopop_odls_rec.payroll_sub_line_id
and org_schedule_id =
autopop_odls_rec.org_schedule_id and suspense_org_account_id is null;
Select project_number
from gms_projects_expend_v
where project_id = p_project_id; */
Select segment1 Project_number
from pa_projects_all
where project_id = p_project_id;
Select task_number
from pa_tasks_expend_v
where task_id = p_task_id; */
Select task_number
from pa_tasks
where task_id = p_task_id;
Select award_number
from gms_awards_basic_v
where award_id = p_award_id
and rownum = 1;
Select award_number
from gms_awards_all
where award_id = p_award_id
and rownum = 1;
Select name
from pa_organizations_expend_v
where organization_id = p_organization_id
and active_flag = 'Y'; -- #1339622
select type.number_per_fiscal_year
from per_time_period_types type,
pay_all_payrolls_f pay
where pay.payroll_id = p_payroll_id
and pay.period_type = type.period_type;
select person_id, assignment_number
from per_all_assignments_f
where assignment_id = p_asg_id
and assignment_type = 'E'
order by effective_end_date desc;
select full_name
from per_all_people_f
where person_id = p_person_id;
select time_period_id,
payroll_id,
currency_code
from psp_payroll_controls
where payroll_control_id = p_payroll_control_id;
select cap.start_date start_date,
cap.end_date end_date,
project_id,
substr(l_sponsor_str, instr(l_sponsor_str,fnd.lookup_code)+length(fnd.lookup_code),15) sponsor_id,
cap.annual_salary_cap / l_no_units_per_year / l_tp_no_of_work_days daily_cap_rate,
funding_source_code,
annual_salary_cap
from psp_salary_cap_overrides cap,
fnd_lookup_values fnd
where cap.start_date < l_tp_end_date
and cap.end_date > l_tp_start_date
and cap.funding_source_code = fnd.lookup_code
and fnd.lookup_type = 'PSP_SPONSOR_NAMES'
and fnd.language = userenv('LANG')
and fnd.enabled_flag = 'Y'
and l_tp_start_date between fnd.start_date_active and nvl(fnd.end_date_active, fnd_date.canonical_to_date('4000/01/31'))
and cap.currency_code = control_details_rec.currency_code
union all
select cap2.start_date start_date,
cap2.end_date end_date,
null,
substr(l_sponsor_str, instr(l_sponsor_str,fnd2.lookup_code)+length(fnd2.lookup_code),15) sponsor_id,
cap2.annual_salary_cap / l_no_units_per_year / l_tp_no_of_work_days daily_cap_rate,
funding_source_code,
annual_salary_cap
from psp_salary_caps cap2,
fnd_lookup_values fnd2
where cap2.start_date < l_tp_end_date
and cap2.end_date > l_tp_start_date
and fnd2.enabled_flag = 'Y'
and l_tp_start_date between fnd2.start_date_active and nvl(fnd2.end_date_active, fnd_date.canonical_to_date('4000/01/31'))
and cap2.funding_source_code = fnd2.lookup_code
and fnd2.lookup_type = 'PSP_SPONSOR_NAMES'
and fnd2.language = userenv('LANG')
and cap2.currency_code = control_details_rec.currency_code
order by project_id, sponsor_id;
select start_date,
end_date
from per_time_periods
where time_period_id = p_time_period_id;
select org.organization_account_id
from psp_organization_accounts org,
per_all_assignments_f paf
where org.account_type_code in ( 'ORG_EXCESS')
and p_dist_date between org.start_date_active and nvl(org.end_date_active, fnd_date.canonical_to_date('4000/01/31'))
and paf.assignment_type = 'E'
and paf.assignment_id = p_asg_id
and p_dist_date between paf.effective_Start_date and paf.effective_end_date
and paf.organization_id = org.organization_id
and org.funding_source_code = p_funding_source_code; --- added condn for 4744285
select organization_account_id
from psp_organization_accounts
where p_dist_date between start_date_active and nvl(end_date_active, fnd_date.canonical_to_date('4000/01/31'))
and account_type_code = 'ORG_EXCESS'
and organization_id = p_excess_org_id
and p_funding_source_code = funding_source_code;
select distinct lookup_code
from fnd_lookup_values
where lookup_type = 'PSP_SPONSOR_NAMES'
and language = userenv('LANG')
and enabled_flag = 'Y'
and p_tp_start_date between start_date_active and nvl(end_date_active, fnd_date.canonical_to_date('4000/01/31'));
select pdl.cap_excess_project_id,
pdl.cap_excess_task_id,
pdl.cap_excess_award_id,
pdl.cap_excess_exp_type,
pdl.cap_excess_exp_org_id,
pdl.effective_date,
ppl.person_id,
ppl.assignment_id,
pdl.distribution_date,
pdl.set_of_books_id,
pdl.distribution_amount,
pdl.business_group_id,
psl.payroll_sub_line_id,
pdl.distribution_line_id
from psp_distribution_lines pdl,
psp_payroll_sub_lines psl,
psp_payroll_lines ppl
where pdl.gl_projecT_flag = 'P'
and psl.payroll_sub_line_id = pdl.payroll_sub_line_id
and pdl.distribution_line_id = p_dist_line_id
and psl.payroll_line_id = ppl.payroll_line_id;
(select paf.assignment_id, min(piv.element_type_id)
From per_all_assignments_f paf,
per_pay_bases ppb,
pay_input_values_f piv
where paf.payroll_id = '||control_details_rec.payroll_id||'
and ('||''''||l_tp_start_date ||''''||' between paf.effective_start_date and paf.effective_end_date
or '||''''||l_tp_end_date ||''''||' between paf.effective_start_date and paf.effective_end_date )
and paf.assignment_id between '||p_start_asg_id||' and '||p_end_asg_id||'
and paf.pay_basis_id = ppb.pay_basis_id
and paf.assignment_type = '||''''||'E'||''''||'
and ppb.input_value_id = piv.input_value_id
group by paf.assignment_id, paf.pay_basis_id);';
((select petr.element_type_id
from pay_element_type_rules petr
where petr.include_or_exclude = '||''''||'I'||''''||'
and petr.element_Set_id = '||g_cap_element_set_id||')
union all
(select pet1.element_type_id
from pay_element_types_f pet1,
pay_ele_classification_rules pecr
where pet1.classification_id = pecr.classification_id
and pecr.element_set_id = '||g_cap_element_set_id||' ))
and ppl.element_type_id not in
(select petr1.element_type_id
from pay_element_type_rules petr1
where petr1.include_or_exclude='||''''||'E'||''''||'
and petr1.element_set_id = '||g_cap_element_set_id||');';
'Select pdl.distribution_line_id,
pdl.distribution_date,
pdl.distribution_amount,
round(nvl(psl.schedule_percent, nvl(pdls.schedule_percent, nvl(pea.percent, (100 * pdl.distribution_amount)/ppsl.daily_rate )))/100 * '||
fnd_number.number_to_canonical(cap_rec.daily_cap_rate)||', '||g_precision||') capped_schedule_amount,
ppl.assignment_id,
null excess_account_id,
null excess_line_id
bulk collect
into :t_dist_line_id,
:t_dist_date,
:t_dist_amount,
:t_capped_sched_amount,
:t_assignment_id,
:t_excess_account,
:t_excess_line_id
From psp_schedule_lines psl,
psp_organization_accounts pod,
psp_element_type_accounts pea,
psp_default_labor_schedules pdls,
psp_payroll_controls ppc,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
gms_awards_all awd,
psp_distribution_lines pdl
where pdl.status_code = '||''''||'N'||''''||'
and pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
and ppsl.payroll_line_id = ppl.payroll_line_id
and ppl.payroll_control_id = ppc.payroll_control_id
and pdl.schedule_line_id = psl.schedule_line_id(+)
and pdl.default_org_account_id = pod.organization_account_id(+)
and pdl.element_account_id = pea.element_account_id(+)
and pdl.suspense_org_account_id is null
and pdl.org_schedule_id = pdls.org_schedule_id(+)
and pdl.gl_project_flag = '||''''||'P'||''''||'
and pdl.distribution_amount <> 0
and ppc.payroll_control_id = '||p_payroll_control_id||'
and ppc.business_group_id = '||p_business_group_id||'
and ppc.set_of_books_id = '||p_set_of_books_id||'
and ppsl.daily_rate > '||cap_rec.daily_cap_rate||'
and ppl.dr_cr_flag = '||''''||'D'||''''||'
and ppl.assignment_id between '||p_start_asg_id||' and '||p_end_asg_id||'
and pdl.cap_excess_dist_line_id is null
and pdl.distribution_date between '||''''||cap_rec.start_date||''''||' and '||''''||cap_rec.end_date||''''||'
and nvl(psl.award_id,
nvl(pod.award_id,
nvl(pea.award_id, pdls.award_id))) = awd.award_id
and nvl(psl.project_id,
nvl(pod.project_id,
nvl(pea.project_id,
pdls.project_id))) = '||cap_rec.project_id;
' select pdl.distribution_line_id,
pdl.distribution_date,
pdl.distribution_amount,
round(nvl(psl.schedule_percent, nvl(pdls.schedule_percent, nvl(pea.percent, (100 * pdl.distribution_amount)/ppsl.daily_rate )))/100 * '||
fnd_number.number_to_canonical(cap_rec.daily_cap_rate)||', '||g_precision||') capped_schedule_amount,
ppl.assignment_id,
null excess_account_id,
null excess_line_id
bulk collect
into :t_dist_line_id,
:t_dist_date,
:t_dist_amount,
:t_capped_sched_amount,
:t_assignment_id,
:t_excess_account,
:t_excess_line_id
From psp_schedule_lines psl,
psp_organization_accounts pod,
psp_element_type_accounts pea,
psp_default_labor_schedules pdls,
psp_payroll_controls ppc,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_distribution_lines pdl,
gms_awards_all awd
where pdl.status_code = '||''''||'N'||''''||'
AND pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
AND ppsl.payroll_line_id = ppl.payroll_line_id
AND ppl.payroll_control_id = ppc.payroll_control_id
AND pdl.schedule_line_id = psl.schedule_line_id(+)
AND pdl.default_org_account_id = pod.organization_account_id(+)
AND pdl.element_account_id = pea.element_account_id(+)
AND pdl.org_schedule_id = pdls.org_schedule_id(+)
and pdl.suspense_org_account_id is null
AND pdl.gl_project_flag = '||''''||'P'||''''||'
AND pdl.distribution_amount <> 0
and ppc.payroll_control_id = '||p_payroll_control_id||'
and ppc.business_group_id = '||p_business_group_id||'
and ppc.set_of_books_id = '||p_set_of_books_id||'
and ppsl.daily_rate > '||cap_rec.daily_cap_rate||'
AND ppl.dr_cr_flag = '||''''||'D'||''''||'
and pdl.cap_excess_dist_line_id is null
and pdl.distribution_date between '||''''||cap_rec.start_date||''''||' and '||''''||cap_rec.end_date||''''||'
and ppl.assignment_id between '||p_start_asg_id||' and '||p_end_asg_id||'
and nvl(psl.award_id,
nvl(pod.award_id,
nvl(pea.award_id, pdls.award_id))) = awd.award_id
and not exists
(select 1
from psp_salary_cap_overrides over
where nvl(psl.project_id, nvl(pod.project_id, nvl(pea.project_id, pdls.project_id)))
= over.project_id
and over.currency_code = ppc.currency_code
and over.funding_source_code = '||''''||cap_rec.funding_source_code||''''||'
and pdl.distribution_date between over.start_date and over.end_date)';
select psp_distribution_lines_s.nextval
into t_excess_line_id(i)
from dual;
Insert into psp_distribution_lines(distribution_line_id,
distribution_date,
distribution_amount,
effective_date,
status_code,
payroll_sub_line_id,
business_group_id,
set_of_books_id,
gl_project_flag,
cap_excess_glccid,
cap_excess_project_id,
cap_excess_award_id,
cap_excess_task_id,
cap_excess_exp_org_id,
cap_excess_exp_type,
funding_source_code,
annual_salary_cap)
select t_excess_line_id(i),
pdl.distribution_date,
pdl.distribution_amount - t_cap_sched_amount(i),
decode(exc.gl_code_combination_id,null,pdl.effective_date,g_gl_effective_date),
pdl.status_code,
pdl.payroll_sub_line_id,
pdl.business_group_id,
pdl.set_of_books_id,
decode(exc.gl_code_combination_id,null,'P','G'),
exc.gl_code_combination_id,
exc.project_id,
exc.award_id,
exc.task_id,
exc.expenditure_organization_id,
exc.expenditure_type,
cap_rec.funding_source_code,
cap_rec.annual_salary_cap
from psp_distribution_lines pdl,
psp_organization_accounts exc
where pdl.distribution_line_id = t_dist_line_id(i)
and exc.organization_account_id = t_excess_account(i);
update psp_distribution_lines pdl
set distribution_amount = t_cap_sched_amount(i),
cap_excess_dist_line_id = t_excess_line_id(i),
funding_source_code = cap_rec.funding_source_code,
annual_salary_cap = cap_rec.annual_salary_cap
where distribution_line_id = t_dist_line_id(i);
t_dist_line_id.delete;
t_dist_date.delete;
t_dist_amount.delete;
t_assignment_id.delete;
t_excess_line_id.delete;
t_excess_account.delete;
t_cap_sched_amount.delete;
select pdl.payroll_sub_line_id ,
max(pdl.distribution_date) max_dist_date, ---5505041
max(pdl.effective_date) effective_date,
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id,
pdl.cap_excess_project_id project_id,
pdl.cap_excess_exp_org_id expenditure_organization_id,
pdl.cap_excess_exp_type expenditure_type,
pdl.cap_excess_task_id task_id,
pdl.cap_excess_award_id award_id,
pdl.cap_excess_glccid gl_code_combination_id,
ppl.cost_id,
ppl.payroll_action_type
from psp_distribution_lines pdl,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl
where ppl.payroll_control_id = p_payroll_control_id
and ppl.payroll_line_id = ppsl.payroll_line_id
and ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
and pdl.suspense_org_account_id is null
and (pdl.cap_excess_glccid is not null or
pdl.cap_excess_project_id is not null)
and ppl.assignment_id between p_start_asg_id and p_end_Asg_id
and pdl.adj_account_flag is null
group by ppl.cost_id,
ppl.payroll_action_type,
pdl.payroll_sub_line_id,
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id,
pdl.cap_excess_project_id,
pdl.cap_excess_exp_org_id,
pdl.cap_excess_exp_type,
pdl.cap_excess_task_id,
pdl.cap_excess_award_id,
pdl.cap_excess_glccid;
select distribution_line_id,
effective_date,
distribution_amount
from psp_distribution_lines pdl
where payroll_sub_line_id = p_payroll_sub_line_id
and (( p_cap_excess_glccid is not null
and p_cap_excess_glccid = pdl.cap_excess_glccid)
or
(p_cap_excess_project_id = pdl.cap_excess_project_id
and p_cap_excess_exp_org_id = pdl.cap_excess_exp_org_id
and p_cap_excess_exp_type = pdl.cap_excess_exp_type
and p_cap_excess_task_id = pdl.cap_excess_task_id
and nvl(p_cap_excess_award_id,-1) = nvl(pdl.cap_excess_award_id,-1)))
order by effective_date;
update psp_distribution_lines set auto_expenditure_type = l_new_expenditure_type
where distribution_line_id=l_dist_line_id;
update psp_distribution_lines set auto_gl_code_combination_id = l_new_gl_code_combination_id
where payroll_sub_line_id = autopop_exc_rec.payroll_sub_line_id
and cap_excess_glccid = autopop_exc_rec.gl_code_combination_id
and suspense_org_account_id is null;
select pdl.payroll_sub_line_id,
decode(g_use_eff_date, 'Y', max(pdl.effective_date) ,
max(pdl.distribution_date)) effective_date, --- 5505041
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id,
poa.project_id,
poa.expenditure_organization_id,
poa.expenditure_type,
poa.task_id,
poa.award_id,
poa.gl_code_combination_id,
poa.organization_account_id,
ppl.payroll_action_type,
ppl.cost_id
from psp_distribution_lines pdl,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_organization_accounts poa
where ppl.payroll_control_id = p_payroll_control_id
and ppl.assignment_id between p_start_asg_id and p_end_asg_id
and ppl.payroll_line_id = ppsl.payroll_line_id
and ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
and pdl.suspense_org_account_id is null
and pdl.default_org_account_id = poa.organization_account_id
and pdl.adj_account_flag is null
group by ppl.cost_id,
ppl.payroll_action_type,
pdl.payroll_Sub_line_id,
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id ,
poa.project_id,
poa.expenditure_organization_id,
poa.expenditure_type,
poa.task_id,
poa.award_id,
poa.gl_code_combination_id,
poa.organization_account_id;
select pdl.payroll_sub_line_id,
decode(g_use_eff_date, 'Y', max(pdl.effective_date) ,
max(pdl.distribution_date)) effective_date, --- 5505041
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id,
poa.project_id,
poa.expenditure_organization_id,
poa.expenditure_type,
poa.task_id,
poa.award_id,
poa.gl_code_combination_id,
poa.organization_account_id,
ppl.payroll_action_type,
ppl.cost_id
from psp_distribution_lines pdl,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_organization_accounts poa
where ppl.payroll_control_id = p_payroll_control_id
and ppl.assignment_id between p_start_asg_id and p_end_asg_id
and ppl.payroll_line_id = ppsl.payroll_line_id
and ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
and pdl.suspense_org_account_id = poa.organization_account_id
and pdl.adj_account_flag is null
group by ppl.cost_id,
ppl.payroll_action_type,
pdl.payroll_Sub_line_id,
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id ,
poa.project_id,
poa.expenditure_organization_id,
poa.expenditure_type,
poa.task_id,
poa.award_id,
poa.gl_code_combination_id,
poa.organization_account_id;
select pdl.payroll_sub_line_id,
decode(g_use_eff_date, 'Y', max(pdl.effective_date) ,
max(pdl.distribution_date)) effective_date, --- 5505041
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id,
ele.project_id,
ele.expenditure_organization_id,
ele.expenditure_type,
ele.task_id,
ele.award_id,
ele.gl_code_combination_id,
ele.element_account_id,
ppl.payroll_action_type,
ppl.cost_id
from psp_distribution_lines pdl,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_element_type_accounts ele
where ppl.payroll_control_id = p_payroll_control_id
and ppl.assignment_id between p_start_asg_id and p_end_asg_id
and ppl.payroll_line_id = ppsl.payroll_line_id
and ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
and pdl.suspense_org_account_id is null
and pdl.element_account_id = ele.element_account_id
and pdl.adj_account_flag is null
group by ppl.cost_id,
ppl.payroll_action_type,
pdl.payroll_Sub_line_id,
ppl.person_id,
ppl.assignment_id,
ppl.element_type_id,
ele.project_id,
ele.expenditure_organization_id,
ele.expenditure_type,
ele.task_id,
ele.award_id,
ele.gl_code_combination_id,
ele.element_account_id;
select ppf.full_name,
paf.assignment_number,
hou.name
from per_all_people_f ppf,
per_all_assignments_f paf,
hr_all_organization_units hou
where ppf.person_id = p_person_id
and p_eff_date between ppf.effective_start_date and ppf.effective_end_date
and paf.assignment_type = 'E'
and paf.assignment_id = p_assignment_id
and p_eff_date between paf.effective_start_date and paf.effective_end_date
and paf.organization_id = hou.organization_id;
'select pdl.distribution_line_id,
pdl.effective_date,
pdl.distribution_amount
from psp_distribution_lines pdl
where pdl.payroll_sub_line_id = :1
and pdl.default_org_account_id = :2
and pdl.suspense_org_account_id is null';
'select pdl.distribution_line_id,
pdl.effective_date,
pdl.distribution_amount
from psp_distribution_lines pdl
where pdl.payroll_sub_line_id = :1
and pdl.element_account_id = :2
and pdl.suspense_org_account_id is null';
'select pdl.distribution_line_id,
pdl.effective_date,
pdl.distribution_amount
from psp_distribution_lines pdl
where pdl.payroll_sub_line_id = :1
and pdl.suspense_org_account_id = :2';
update psp_distribution_lines
set auto_expenditure_type = l_new_expenditure_type
where distribution_line_id=l_dist_line_id;
update psp_distribution_lines
set suspense_auto_exp_type = l_new_expenditure_type
where distribution_line_id=l_dist_line_id;
update psp_distribution_lines
set auto_expenditure_type = l_new_expenditure_type
where distribution_line_id=l_dist_line_id;
update psp_distribution_lines
set suspense_auto_glccid = l_new_gl_code_combination_id
where payroll_sub_line_id = t_payroll_sub_line_id(i)
and suspense_org_account_id = t_account_id(i);
update psp_distribution_lines
set auto_gl_code_combination_id = l_new_gl_code_combination_id
where payroll_sub_line_id = t_payroll_sub_line_id(i)
and element_account_id = t_account_id(i)
and suspense_org_account_id is null;
update psp_distribution_lines
set auto_gl_code_combination_id = l_new_gl_code_combination_id
where payroll_sub_line_id = t_payroll_sub_line_id(i)
and default_org_account_id= t_account_id(i)
and suspense_org_account_id is null;
t_payroll_sub_line_id.delete;
t_effective_date.delete;
t_person_id.delete;
t_assignment_id.delete;
t_element_type_id.delete;
t_project_id.delete;
t_expenditure_organization_id.delete;
t_expenditure_type.delete;
t_task_id.delete;
t_award_id.delete;
t_gl_code_combination_id.delete;
t_account_id.delete;
t_payroll_action_type.delete;
t_cost_id.delete;
t_payroll_sub_line_id.delete;
t_effective_date.delete;
t_person_id.delete;
t_assignment_id.delete;
t_element_type_id.delete;
t_project_id.delete;
t_expenditure_organization_id.delete;
t_expenditure_type.delete;
t_task_id.delete;
t_award_id.delete;
t_gl_code_combination_id.delete;
t_account_id.delete;
t_payroll_sub_line_id.delete;
t_effective_date.delete;
t_person_id.delete;
t_assignment_id.delete;
t_element_type_id.delete;
t_project_id.delete;
t_expenditure_organization_id.delete;
t_expenditure_type.delete;
t_task_id.delete;
t_award_id.delete;
t_gl_code_combination_id.delete;
t_account_id.delete;
select pcv_information1 global_element_autopop,
pcv_information2 element_type_autopop,
pcv_information3 element_class_autopop,
pcv_information4 assignment_autopop,
pcv_information5 default_schedule_autopop,
pcv_information6 default_account_autopop,
pcv_information7 suspense_account,
pcv_information10 excess_account
from pqp_configuration_values
where pcv_information_category = 'PSP_ENABLE_AUTOPOPULATION'
and legislation_code is null
and nvl(business_group_id, g_business_group_id) = g_business_group_id;
select get_parameter('SOURCE_TYPE',ppa.legislative_parameters) ,
get_parameter('SOURCE_CODE',ppa.legislative_parameters) ,
get_parameter('TIME_PERIOD_ID',ppa.legislative_parameters),
get_parameter('BATCH_NAME',ppa.legislative_parameters),
get_parameter('PAYROLL_ID',ppa.legislative_parameters)
into g_source_type,
g_source_code,
g_time_period_id,
g_batch_name,
g_payroll_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
select payroll_id
into g_payroll_id
from per_time_periods
where time_period_id = g_time_period_id;
select count(*)
from psp_organization_accounts
where account_type_code = 'ORG_EXCESS'
and p_date between trunc(start_date_active) and trunc(end_date_active)
and organization_id = p_excess_org_id
and business_group_id = p_business_group_id ;
select min(ptp.start_date), max(ptp.end_date)
from psp_payroll_controls ppc,
per_time_periods ptp
where ppc.source_type = g_source_type
and ppc.payroll_source_code = g_source_code
and (ppc.batch_name = nvl(g_batch_name, ppc.batch_name)
or (ppc.batch_name is null and g_batch_name is null))
and ppc.time_period_id = nvl(g_time_period_id, ppc.time_period_id)
and ppc.payroll_id = nvl(g_payroll_id, ppc.payroll_id)
and ppc.cdl_payroll_action_id = pactid
and ppc.status_code = 'N'
and ppc.dist_dr_amount is null
and ppc.dist_cr_amount is null
and ppc.business_group_id = g_business_group_id
and ppc.set_of_books_id = g_set_of_books_id
and ptp.time_period_id = ppc.time_period_id;
select distinct lookup_code, meaning
from fnd_lookup_values
where lookup_type = 'PSP_SPONSOR_NAMES'
and language = 'US'
and p_begin_date between start_date_active and nvl(end_date_active, fnd_date.canonical_to_date('4000/01/31'))
and enabled_flag = 'Y';
select 1
from psp_salary_caps
where funding_source_code = p_sponsor_code
and p_date between start_date and end_date;
select payroll_control_id
from psp_payroll_controls ppc
where ppc.source_type = g_source_type
and ppc.payroll_source_code = g_source_code
and (ppc.batch_name = nvl(g_batch_name, ppc.batch_name)
or (ppc.batch_name is null and g_batch_name is null))
and ppc.time_period_id = nvl(g_time_period_id, ppc.time_period_id)
and ppc.payroll_id = nvl(g_payroll_id, ppc.payroll_id)
and ppc.cdl_payroll_action_id is null
and ppc.status_code = 'N'
and ppc.dist_dr_amount is null
and ppc.dist_cr_amount is null
and ppc.business_group_id = g_business_group_id
and ppc.parent_payroll_control_id is null
and ppc.set_of_books_id = g_set_of_books_id;
select get_parameter('SOURCE_TYPE',ppa.legislative_parameters) ,
get_parameter('SOURCE_CODE',ppa.legislative_parameters) ,
get_parameter('TIME_PERIOD_ID',ppa.legislative_parameters),
get_parameter('BATCH_NAME',ppa.legislative_parameters),
get_parameter('PAYROLL_ID',ppa.legislative_parameters)
into g_source_type,
g_source_code,
g_time_period_id,
g_batch_name,
g_payroll_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
update psp_payroll_controls ppc
set ppc.cdl_payroll_action_id = pactid
where payroll_control_id = l_payroll_control_id
or parent_payroll_control_id = l_payroll_control_id;
sqlstr := 'select distinct ppl.assignment_id
from psp_payroll_lines ppl,
psp_payroll_controls ppc
where ppc.payroll_control_id = ppl.payroll_control_id
and ppc.cdl_payroll_action_id = :payroll_action_id
order by ppl.assignment_id';
select action_status
from pay_temp_object_actions
where payroll_action_id = p_payroll_action_id
and chunk_number = p_chunk_number;
select min(object_id), max(object_id)
into l_start_asg, l_end_asg
from pay_temp_object_actions
where payroll_action_id = p_payroll_action_id
and chunk_number = p_chunk_number;
select distinct ppl.assignment_id
FROM psp_payroll_controls ppc,
psp_payroll_lines ppl
WHERE ppc.business_group_id = g_business_group_id
AND ppc.set_of_books_id = g_set_of_books_id
AND ppc.source_type = nvl(g_source_type,ppc.source_type)
AND ppc.payroll_source_code = nvl(g_source_code,ppc.payroll_source_code)
AND ppc.time_period_id <= nvl(p_time_period_id, ppc.time_period_id) -- Bug 6733614
AND ppc.payroll_id = nvl(g_payroll_id, ppc.payroll_id)
AND nvl(ppc.batch_name,'N') = nvl(nvl(g_batch_name,ppc.batch_name),'N')
AND (ppc.sublines_dr_amount IS NOT NULL OR ppc.sublines_cr_amount IS NOT NULL)
AND (ppc.dist_dr_amount IS NULL AND ppc.dist_cr_amount IS NULL)
AND ppl.payroll_control_id = ppc.payroll_control_id
AND ppl.assignment_id between stasg and endasg;
select get_parameter('SOURCE_TYPE',ppa.legislative_parameters) ,
get_parameter('SOURCE_CODE',ppa.legislative_parameters) ,
get_parameter('TIME_PERIOD_ID',ppa.legislative_parameters),
get_parameter('BATCH_NAME',ppa.legislative_parameters),
get_parameter('PAYROLL_ID',ppa.legislative_parameters)
into g_source_type,
g_source_code,
g_time_period_id,
g_batch_name,
g_payroll_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid;
select payroll_id
into g_payroll_id
from per_time_periods
where time_period_id = g_time_period_id;
select pay_assignment_actions_s.nextval into l_asgactid from dual;
SELECT payroll_control_id
FROM psp_payroll_controls
WHERE cdl_payroll_action_id = pactid;
select get_parameter('SOURCE_TYPE',ppa.legislative_parameters) ,
get_parameter('SOURCE_CODE',ppa.legislative_parameters) ,
get_parameter('TIME_PERIOD_ID',ppa.legislative_parameters),
get_parameter('BATCH_NAME',ppa.legislative_parameters),
get_parameter('PAYROLL_ID',ppa.legislative_parameters)
into g_source_type,
g_source_code,
g_time_period_id,
g_batch_name,
g_payroll_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select count(*)
into l_count_fail_actions
from pay_payroll_actions
where payroll_action_id = pactid
and action_status <> 'C';
select count(*)
into l_count_fail_actions
from pay_temp_object_actions
where payroll_action_id = pactid
and action_status <> 'C';
SELECT nvl(sum(distribution_amount),0)
INTO l_total_dist_dr_amount
FROM psp_distribution_lines pdl,
psp_payroll_sub_lines ppsl,
psp_payroll_lines ppl,
psp_payroll_controls ppc
WHERE ppc.payroll_control_id = payroll_control_rec.payroll_control_id
AND ppc.payroll_control_id = ppl.payroll_control_id
AND ppl.payroll_line_id = ppsl.payroll_line_id
AND ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
AND pdl.reversal_entry_flag IS NULL
AND ppl.dr_cr_flag = 'D';
SELECT nvl(sum(distribution_amount),0)
INTO l_total_dist_cr_amount
FROM psp_distribution_lines pdl,
psp_payroll_sub_lines ppsl,
psp_payroll_lines ppl,
psp_payroll_controls ppc
WHERE ppc.payroll_control_id = payroll_control_rec.payroll_control_id
AND ppc.payroll_control_id = ppl.payroll_control_id
AND ppl.payroll_line_id = ppsl.payroll_line_id
AND ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
AND pdl.reversal_entry_flag IS NULL
AND ppl.dr_cr_flag = 'C';
UPDATE psp_payroll_controls
SET dist_dr_amount = l_total_dist_dr_amount,
dist_cr_amount = l_total_dist_cr_amount
WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
select epd.source_element_type_id
from pay_entry_process_details epd,
pay_run_results prr,
pay_costs pc
where epd.element_entry_id = prr.source_id
and prr.run_result_id = pc.run_result_id
and pc.cost_id = p_cost_id;