[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* This cursor selects from three different tables.
For the table psp_distribution_lines_history, if
suspense account information is present, then a join
to the suspense table is used to get the GL or POETA
account information. Otherwise, the information
is found from element account, schedule line,
default labor schedule, or default org account tables.
For the tables psp_pre_gen_dist_lines_history
and psp_adjustment_lines_history, account information
is taken from suspense account if present, otherwise
it is taken from the line. */
--Introduced the EXISTS clause of adjust='Y' for Bug 2860013
CURSOR lines_c1(p_person_id IN NUMBER,
p_assignment_id IN NUMBER,
p_begin_date IN DATE,
p_end_date IN DATE) IS
SELECT ppl.element_type_id, --- added this and line below for DA-ENH
pegl.element_group_id, -- Modified to inline query column for bug fix 3658235
psl.gl_code_combination_id,
psl.project_id,
psl.expenditure_organization_id,
psl.expenditure_type,
psl.task_id,
psl.award_id,
pdl.distribution_date,
ROUND(pdl.distribution_amount, g_precision), -- Introduced ROUND() for bug fix 2916848
--- pdl.gl_project_flag, commented for DA-ENH
pdl.distribution_line_id distribution_line_id,
ppl.dr_cr_flag,
'D' tab_flag ,
--Added the following 3 new columns : For bug fix 2252881
pdl.effective_date,
psl.time_period_id,
psl.payroll_control_id,
DECODE(g_dff_grouping_option, 'Y', psl.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', psl.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', psl.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', psl.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', psl.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', psl.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', psl.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', psl.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', psl.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', psl.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', psl.attribute10, NULL) attribute10
FROM
psp_distribution_lines_history pdl,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_summary_lines psl,
--psp_group_element_list pgel, --- added for DA-ENH Modified to inline query for bug 3658235
(SELECT peg.element_group_id, pgel.element_type_id
FROM psp_element_groups peg, psp_group_element_list pgel
WHERE business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND peg.element_group_id (+) = pgel.element_group_id
and (peg.start_date_active between p_begin_date and p_end_date -- Bug 7582233
OR peg.end_date_active between p_begin_date and p_end_date)) pegl,-- Introduced for bug fix 3098050
psp_payroll_controls ppc -- Introduced for bug fix 2916848
WHERE
psl.person_id = p_person_id
AND psl.summary_line_id = pdl.summary_line_id
AND psl.assignment_id = p_assignment_id
AND psl.business_group_id = p_business_group_id
AND psl.set_of_books_id = p_set_of_books_id
AND pdl.distribution_date between p_begin_date and p_end_date
AND pegl.element_type_id(+) = ppl.element_type_id --- added for DA-ENH
-- Introduced BG/SOB check on psp_element_groups for bug fix 3098050
--AND peg.element_group_id(+) = pgel.element_group_id
-- AND NVL(peg.business_group_id, p_business_group_id) = p_business_group_id -- Introduced NVL for bug fix 3145038, Commented for 3658235
-- AND NVL(peg.set_of_books_id, p_set_of_books_id) = p_set_of_books_id -- Introduced NVL for bug fix 3145038, Commented for 3658235
AND pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
AND ppsl.payroll_line_id = ppl.payroll_line_id
-- Introduced the following for bug fix 2916848
AND ppc.payroll_control_id = ppl.payroll_control_id
AND ppc.currency_code = p_currency_code
-- End of bug fix 2916848
AND pdl.reversal_entry_flag is NULL
AND pdl.adjustment_batch_name is null
AND EXISTS (SELECT 1
FROM psp_element_types pet
WHERE pet.element_type_id = ppl.element_type_id
AND pet.adjust = 'Y'
-- Introduced BG/SOB check fopr bug fix 3098050
AND pet.business_group_id = p_business_group_id
AND pet.set_of_books_id = p_set_of_books_id)
for update of reversal_entry_flag nowait ;
SELECT ppg.element_type_id, -- added this line and line below for DA-ENH
pegl.element_group_id, -- Modified to inline query column for bug 3658235
decode(ppg.suspense_org_account_id, NULL, ppg.gl_code_combination_id,
nvl(ppg.suspense_auto_glccid, pos.gl_code_combination_id)) gl_code_combination_id,
decode(ppg.suspense_org_account_id, NULL, ppg.project_id,
pos.project_id) project_id,
decode(ppg.suspense_org_account_id, NULL, ppg.expenditure_organization_id,
pos.expenditure_organization_id) expenditure_organization_id,
decode(ppg.suspense_org_account_id, NULL, ppg.expenditure_type,
nvl(ppg.suspense_auto_exp_type, pos.expenditure_type)) expenditure_type,
decode(ppg.suspense_org_account_id, NULL, ppg.task_id,
pos.task_id) task_id,
decode(ppg.suspense_org_account_id, NULL, ppg.award_id,
pos.award_id) award_id,
ppg.distribution_date,
ROUND(ppg.distribution_amount, g_precision), -- Introduced ROUND() for bug fix 2916848
ppg.pre_gen_dist_line_id distribution_line_id,
ppg.dr_cr_flag,
'P' tab_flag ,
--Added the following 3 new columns : For bug fix 2252881
ppg.effective_date,
ppg.time_period_id,
ppg.payroll_control_id ,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute_category, pos.attribute_category), NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute1, pos.attribute1), NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute2, pos.attribute2), NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute3, pos.attribute3), NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute4, pos.attribute4), NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute5, pos.attribute5), NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute6, pos.attribute6), NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute7, pos.attribute7), NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute8, pos.attribute8), NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute9, pos.attribute9), NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute10, pos.attribute10), NULL) attribute10
FROM
psp_pre_gen_dist_lines_history ppg,
psp_organization_accounts pos ,
--psp_group_element_list pgel, --- added for DA-ENH Modified to inline query for bug fix 3658235
(SELECT peg.element_group_id, pgel.element_type_id
FROM psp_element_groups peg, psp_group_element_list pgel
WHERE business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND peg.element_group_id (+) = pgel.element_group_id
and (peg.start_date_active between p_begin_date and p_end_date -- Bug 7582233
OR peg.end_date_active between p_begin_date and p_end_date)) pegl, -- Introduced for bug fix 3098050
psp_payroll_controls ppc -- Introduced for bug fix 2916848
WHERE
ppg.assignment_id = p_assignment_id
AND ppg.distribution_date between p_begin_date and p_end_date
AND ppg.business_group_id = p_business_group_id
AND ppg.set_of_books_id = p_set_of_books_id
-- Introduced the following for bug fix 2916848
AND ppc.payroll_control_id = ppg.payroll_control_id
AND ppc.currency_code = p_currency_code
-- End of bug fix 2916848
AND pegl.element_type_id(+) = ppg.element_type_id -- added for DA-ENH
-- Introduced BG/SOB check on psp_element_groups for bug fix 3098050
--AND peg.element_group_id(+) = pgel.element_group_id
-- AND NVL(peg.business_group_id, p_business_group_id) = p_business_group_id -- Introduced NVL for bug fix 3145038, Commented for bug 3658235
-- AND NVL(peg.set_of_books_id, p_set_of_books_id) = p_set_of_books_id -- Introduced NVL for bug fix 3145038, Commented for bug 3658235
AND ppg.status_code = 'A'
AND ppg.reversal_entry_flag is NULL
AND ppg.suspense_org_account_id = pos.organization_account_id(+)
AND ppg.adjustment_batch_name is null
AND EXISTS (SELECT 1
FROM psp_element_types pet
WHERE pet.element_type_id = ppg.element_type_id
AND pet.adjust = 'Y'
-- Introduced BG/SOB check fopr bug fix 3098050
AND pet.business_group_id = p_business_group_id
AND pet.set_of_books_id = p_set_of_books_id)
for update of reversal_entry_flag nowait;
SELECT pal.element_type_id, --- for DA-ENH
pegl.element_group_id, -- added for DA-ENH Modified to inline query column for bug 3658235
pal.gl_code_combination_id,
pal.project_id,
pal.expenditure_organization_id,
pal.expenditure_type,
pal.task_id,
pal.award_id,
pal.distribution_date,
ROUND(pal.distribution_amount, g_precision), -- Introduced for bug fix 2916848
pal.adjustment_line_id distribution_line_id,
dr_cr_flag, /* changed from 'D', Bug 1976999 */
'A' tab_flag ,
pal.effective_date,
pal.time_period_id,
pal.payroll_control_id ,
DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
FROM
psp_adjustment_lines_history pal,
--psp_group_element_list pgel, --- added table for DA-ENH Modified to inline query for bug fix 3658235
(SELECT peg.element_group_id, pgel.element_type_id
FROM psp_element_groups peg, psp_group_element_list pgel
WHERE business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND peg.element_group_id (+) = pgel.element_group_id
and (peg.start_date_active between p_begin_date and p_end_date -- Bug 7582233
OR peg.end_date_active between p_begin_date and p_end_date)) pegl, -- Introduced for bug fix 3098050
psp_payroll_controls ppc -- Introduced for bug fix 2916848
WHERE
pal.assignment_id = p_assignment_id
AND pal.distribution_date between p_begin_date and p_end_date
AND pal.business_group_id = p_business_group_id
AND pal.set_of_books_id = p_set_of_books_id
-- Introduced the following for bug fix 2916848
AND ppc.payroll_control_id = pal.payroll_control_id
AND ppc.currency_code = p_currency_code
-- End of bug fix 2916848
AND pegl.element_type_id(+) = pal.element_type_id
-- Introduced BG/SOB check on psp_element_groups for bug fix 3098050
--AND peg.element_group_id(+) = pgel.element_group_id
-- AND NVL(peg.business_group_id, p_business_group_id) = p_business_group_id -- Introduced NVL for bug fix 3145038, Commented for bug 3658235
-- AND NVL(peg.set_of_books_id, p_set_of_books_id) = p_set_of_books_id -- Introduced NVL for bug fix 3145038, Commented for bug 3658235
AND pal.status_code = 'A'
AND NVL(pal.original_line_flag,'N') ='N'
AND pal.reversal_entry_flag is NULL
AND pal.adjustment_batch_name is null
AND EXISTS (SELECT 1
FROM psp_element_types pet
WHERE pet.element_type_id = pal.element_type_id
AND pet.adjust = 'Y'
-- Introduced BG/SOB check fopr bug fix 3098050
AND pet.business_group_id = p_business_group_id
AND pet.set_of_books_id = p_set_of_books_id)
for update of reversal_entry_flag nowait ;
SELECT ELEMENT_TYPE_ID,
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
-- DR_CR_FLAG, Commented for Bug 3625667
DECODE(sign(SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount))),-1,'C','D') DR_CR_FLAG,
SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount)),
RUN_ID,
SET_OF_BOOKS_ID,
BUSINESS_GROUP_ID,
DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
from PSP_TEMP_ORIG_LINES
where RUN_ID = g_run_id
group by RUN_ID,
SET_OF_BOOKS_ID,
BUSINESS_GROUP_ID,
ELEMENT_TYPE_ID,
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
-- DR_CR_FLAG, Commented for Bug 3625667
DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute10, NULL);
SELECT ELEMENT_GROUP_ID,
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
-- DR_CR_FLAG, Commented for Bug 3625667
DECODE(sign(SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount))),-1,'C','D') DR_CR_FLAG,
SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount)),
RUN_ID,
SET_OF_BOOKS_ID,
BUSINESS_GROUP_ID,
DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
from PSP_TEMP_ORIG_LINES
where RUN_ID = g_run_id
and element_group_id IS NOT NULL
group by RUN_ID,
SET_OF_BOOKS_ID,
BUSINESS_GROUP_ID,
ELEMENT_GROUP_ID,
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
-- DR_CR_FLAG, Commented for bug 3625667
DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute10, NULL);
select GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
-- DR_CR_FLAG, Commented for bug 3625667
DECODE(sign(SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount))),-1,'C','D') DR_CR_FLAG,
SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount)),
RUN_ID,
SET_OF_BOOKS_ID,
BUSINESS_GROUP_ID,
DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
from PSP_TEMP_ORIG_LINES
where RUN_ID = g_run_id
group by RUN_ID,
SET_OF_BOOKS_ID,
BUSINESS_GROUP_ID,
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
-- DR_CR_FLAG, Commented for Bug 3625667
DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', attribute10, NULL);
insert into psp_temp_orig_lines(
element_type_id,
element_group_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
orig_distribution_date,
distribution_amount,
orig_line_id,
dr_cr_flag,
orig_source_type,
effective_date,
time_period_id,
payroll_control_id,
run_id,
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)
values
(orig_line_rec.array_element_type_id(i),
orig_line_rec.array_element_group_id(i),
orig_line_rec.array_glccid(i),
orig_line_rec.array_project_id(i),
orig_line_rec.array_exp_org_id(i),
orig_line_rec.array_exp_type(i),
orig_line_rec.array_task_id(i),
orig_line_rec.array_award_id(i),
orig_line_rec.array_distribution_date(i),
orig_line_rec.array_distribution_amount(i),
orig_line_rec.array_distribution_line_id(i),
orig_line_rec.array_dr_cr_flag(i),
orig_line_rec.array_tab_flag(i),
orig_line_rec.array_effective_date(i),
orig_line_rec.array_time_period_id(i),
orig_line_rec.array_payroll_control_id(i),
g_run_id,
p_business_group_id,
p_set_of_books_id,
orig_line_rec.array_attribute_category(i), -- Introduced DFF columns for bug fix 2908859
orig_line_rec.array_attribute1(i),
orig_line_rec.array_attribute2(i),
orig_line_rec.array_attribute3(i),
orig_line_rec.array_attribute4(i),
orig_line_rec.array_attribute5(i),
orig_line_rec.array_attribute6(i),
orig_line_rec.array_attribute7(i),
orig_line_rec.array_attribute8(i),
orig_line_rec.array_attribute9(i),
orig_line_rec.array_attribute10(i));
orig_line_rec.array_element_type_id .delete;
orig_line_rec.array_element_group_id .delete;
orig_line_rec.array_glccid .delete;
orig_line_rec.array_project_id .delete;
orig_line_rec.array_exp_org_id .delete;
orig_line_rec.array_exp_type .delete;
orig_line_rec.array_task_id .delete;
orig_line_rec.array_award_id .delete;
orig_line_rec.array_distribution_date .delete;
orig_line_rec.array_distribution_amount .delete;
orig_line_rec.array_distribution_line_id .delete;
orig_line_rec.array_dr_cr_flag .delete;
orig_line_rec.array_tab_flag .delete;
orig_line_rec.array_effective_date .delete;
orig_line_rec.array_time_period_id .delete;
orig_line_rec.array_payroll_control_id .delete;
orig_line_rec.array_attribute_category.delete; -- Introduced for bug fix 2908859
orig_line_rec.array_attribute1.delete;
orig_line_rec.array_attribute2.delete;
orig_line_rec.array_attribute3.delete;
orig_line_rec.array_attribute4.delete;
orig_line_rec.array_attribute5.delete;
orig_line_rec.array_attribute6.delete;
orig_line_rec.array_attribute7.delete;
orig_line_rec.array_attribute8.delete;
orig_line_rec.array_attribute9.delete;
orig_line_rec.array_attribute10.delete;
insert into psp_temp_orig_lines(
element_type_id,
element_group_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
orig_distribution_date,
distribution_amount,
orig_line_id,
dr_cr_flag,
orig_source_type,
effective_date,
time_period_id,
payroll_control_id,
run_id,
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)
values
(orig_line_rec.array_element_type_id(i),
orig_line_rec.array_element_group_id(i),
orig_line_rec.array_glccid(i),
orig_line_rec.array_project_id(i),
orig_line_rec.array_exp_org_id(i),
orig_line_rec.array_exp_type(i),
orig_line_rec.array_task_id(i),
orig_line_rec.array_award_id(i),
orig_line_rec.array_distribution_date(i),
orig_line_rec.array_distribution_amount(i),
orig_line_rec.array_distribution_line_id(i),
orig_line_rec.array_dr_cr_flag(i),
orig_line_rec.array_tab_flag(i),
orig_line_rec.array_effective_date(i),
orig_line_rec.array_time_period_id(i),
orig_line_rec.array_payroll_control_id(i),
g_run_id,
p_business_group_id,
p_set_of_books_id,
orig_line_rec.array_attribute_category(i), -- Introduced DFF columns for bug fix 2908859
orig_line_rec.array_attribute1(i),
orig_line_rec.array_attribute2(i),
orig_line_rec.array_attribute3(i),
orig_line_rec.array_attribute4(i),
orig_line_rec.array_attribute5(i),
orig_line_rec.array_attribute6(i),
orig_line_rec.array_attribute7(i),
orig_line_rec.array_attribute8(i),
orig_line_rec.array_attribute9(i),
orig_line_rec.array_attribute10(i));
orig_line_rec.array_element_type_id .delete;
orig_line_rec.array_element_group_id .delete;
orig_line_rec.array_glccid .delete;
orig_line_rec.array_project_id .delete;
orig_line_rec.array_exp_org_id .delete;
orig_line_rec.array_exp_type .delete;
orig_line_rec.array_task_id .delete;
orig_line_rec.array_award_id .delete;
orig_line_rec.array_distribution_date .delete;
orig_line_rec.array_distribution_amount .delete;
orig_line_rec.array_distribution_line_id .delete;
orig_line_rec.array_dr_cr_flag .delete;
orig_line_rec.array_tab_flag .delete;
orig_line_rec.array_effective_date .delete;
orig_line_rec.array_time_period_id .delete;
orig_line_rec.array_payroll_control_id .delete;
orig_line_rec.array_attribute_category.delete; -- Introduced for bug fix 2908859
orig_line_rec.array_attribute1.delete;
orig_line_rec.array_attribute2.delete;
orig_line_rec.array_attribute3.delete;
orig_line_rec.array_attribute4.delete;
orig_line_rec.array_attribute5.delete;
orig_line_rec.array_attribute6.delete;
orig_line_rec.array_attribute7.delete;
orig_line_rec.array_attribute8.delete;
orig_line_rec.array_attribute9.delete;
orig_line_rec.array_attribute10.delete;
insert into psp_temp_orig_lines(
element_type_id,
element_group_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
orig_distribution_date,
distribution_amount,
orig_line_id,
dr_cr_flag,
orig_source_type,
effective_date,
time_period_id,
payroll_control_id,
run_id,
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)
values
(orig_line_rec.array_element_type_id(i),
orig_line_rec.array_element_group_id(i),
orig_line_rec.array_glccid(i),
orig_line_rec.array_project_id(i),
orig_line_rec.array_exp_org_id(i),
orig_line_rec.array_exp_type(i),
orig_line_rec.array_task_id(i),
orig_line_rec.array_award_id(i),
orig_line_rec.array_distribution_date(i),
orig_line_rec.array_distribution_amount(i),
orig_line_rec.array_distribution_line_id(i),
orig_line_rec.array_dr_cr_flag(i),
orig_line_rec.array_tab_flag(i),
orig_line_rec.array_effective_date(i),
orig_line_rec.array_time_period_id(i),
orig_line_rec.array_payroll_control_id(i),
g_run_id,
p_business_group_id,
p_set_of_books_id,
orig_line_rec.array_attribute_category(i), -- Introduced DFF columns for bug fix 2908859
orig_line_rec.array_attribute1(i),
orig_line_rec.array_attribute2(i),
orig_line_rec.array_attribute3(i),
orig_line_rec.array_attribute4(i),
orig_line_rec.array_attribute5(i),
orig_line_rec.array_attribute6(i),
orig_line_rec.array_attribute7(i),
orig_line_rec.array_attribute8(i),
orig_line_rec.array_attribute9(i),
orig_line_rec.array_attribute10(i));
orig_line_rec.array_element_type_id .delete;
orig_line_rec.array_element_group_id .delete;
orig_line_rec.array_glccid .delete;
orig_line_rec.array_project_id .delete;
orig_line_rec.array_exp_org_id .delete;
orig_line_rec.array_exp_type .delete;
orig_line_rec.array_task_id .delete;
orig_line_rec.array_award_id .delete;
orig_line_rec.array_distribution_date .delete;
orig_line_rec.array_distribution_amount .delete;
orig_line_rec.array_distribution_line_id .delete;
orig_line_rec.array_dr_cr_flag .delete;
orig_line_rec.array_tab_flag .delete;
orig_line_rec.array_effective_date .delete;
orig_line_rec.array_time_period_id .delete;
orig_line_rec.array_payroll_control_id .delete;
orig_line_rec.array_attribute_category.delete; -- Introduced for bug fix 2908859
orig_line_rec.array_attribute1.delete;
orig_line_rec.array_attribute2.delete;
orig_line_rec.array_attribute3.delete;
orig_line_rec.array_attribute4.delete;
orig_line_rec.array_attribute5.delete;
orig_line_rec.array_attribute6.delete;
orig_line_rec.array_attribute7.delete;
orig_line_rec.array_attribute8.delete;
orig_line_rec.array_attribute9.delete;
orig_line_rec.array_attribute10.delete;
insert into PSP_TEMP_ORIG_SUMLINES (
ELEMENT_TYPE_ID,
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
DR_CR_FLAG,
DISTRIBUTION_SUM,
RUN_ID,
SET_OF_BOOKS_ID,
BUSINESS_GROUP_ID,
ACCT_GROUP_ID,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10)
values ( orig_sumline_rec.array_element_type_id(k),
orig_sumline_rec.array_glccid(k),
orig_sumline_rec.array_project_id(k),
orig_sumline_rec.array_exp_org_id(k),
orig_sumline_rec.array_exp_type(k),
orig_sumline_rec.array_task_id(k),
orig_sumline_rec.array_award_id(k),
orig_sumline_rec.array_dr_cr_flag(k),
orig_sumline_rec.array_distribution_sum(k),
orig_sumline_rec.array_run_id(k),
orig_sumline_rec.array_set_of_books_id(k),
orig_sumline_rec.array_business_group_id(k),
orig_sumline_rec.array_acct_group_id(k),
orig_sumline_rec.array_attribute_category(k),
orig_sumline_rec.array_attribute1(k),
orig_sumline_rec.array_attribute2(k),
orig_sumline_rec.array_attribute3(k),
orig_sumline_rec.array_attribute4(k),
orig_sumline_rec.array_attribute5(k),
orig_sumline_rec.array_attribute6(k),
orig_sumline_rec.array_attribute7(k),
orig_sumline_rec.array_attribute8(k),
orig_sumline_rec.array_attribute9(k),
orig_sumline_rec.array_attribute10(k));
Update psp_temp_orig_lines LINE
set LINE.acct_group_id = (select MAS.acct_group_id
from psp_temp_orig_sumlines MAS
where MAS.run_id = g_run_id and
nvl(MAS.element_type_id,-9) = nvl(LINE.element_type_id,-9) and
-- MAS.dr_cr_flag = LINE.dr_cr_flag and Commented for Bug 3625667
nvl(MAS.gl_code_combination_id,-9) =
nvl(LINE.gl_code_combination_id,-9) and
nvl(MAS.project_id,-9)= nvl(LINE.project_id,-9) and
nvl(MAS.task_id,-9) = nvl(LINE.task_id,-9) and
nvl(MAS.expenditure_organization_id,-9) =
nvl(LINE.expenditure_organization_id,-9) and
nvl(MAS.award_id, -9) = nvl(LINE.award_id, -9) and
(MAS.expenditure_type = LINE.expenditure_type or
(MAS.expenditure_type is null and LINE.expenditure_type is null))
AND (NVL(mas.attribute_category, 'NULL') = NVL(line.attribute_category, 'NULL')) -- Introduced DFF column check for bug fix 2908859
AND (NVL(mas.attribute1, 'NULL') = NVL(line.attribute1, 'NULL'))
AND (NVL(mas.attribute2, 'NULL') = NVL(line.attribute2, 'NULL'))
AND (NVL(mas.attribute3, 'NULL') = NVL(line.attribute3, 'NULL'))
AND (NVL(mas.attribute4, 'NULL') = NVL(line.attribute4, 'NULL'))
AND (NVL(mas.attribute5, 'NULL') = NVL(line.attribute5, 'NULL'))
AND (NVL(mas.attribute6, 'NULL') = NVL(line.attribute6, 'NULL'))
AND (NVL(mas.attribute7, 'NULL') = NVL(line.attribute7, 'NULL'))
AND (NVL(mas.attribute8, 'NULL') = NVL(line.attribute8, 'NULL'))
AND (NVL(mas.attribute9, 'NULL') = NVL(line.attribute9, 'NULL'))
AND (NVL(mas.attribute10, 'NULL') = NVL(line.attribute10, 'NULL')))
where LINE.run_id = g_run_id;
insert into PSP_TEMP_ORIG_SUMLINES (
ELEMENT_GROUP_ID,
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
DR_CR_FLAG,
DISTRIBUTION_SUM,
RUN_ID,
SET_OF_BOOKS_ID,
BUSINESS_GROUP_ID,
ACCT_GROUP_ID,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10)
values ( orig_sumline_rec.array_element_group_id(k),
orig_sumline_rec.array_glccid(k),
orig_sumline_rec.array_project_id(k),
orig_sumline_rec.array_exp_org_id(k),
orig_sumline_rec.array_exp_type(k),
orig_sumline_rec.array_task_id(k),
orig_sumline_rec.array_award_id(k),
orig_sumline_rec.array_dr_cr_flag(k),
orig_sumline_rec.array_distribution_sum(k),
orig_sumline_rec.array_run_id(k),
orig_sumline_rec.array_set_of_books_id(k),
orig_sumline_rec.array_business_group_id(k),
orig_sumline_rec.array_acct_group_id(k),
orig_sumline_rec.array_attribute_category(k),
orig_sumline_rec.array_attribute1(k),
orig_sumline_rec.array_attribute2(k),
orig_sumline_rec.array_attribute3(k),
orig_sumline_rec.array_attribute4(k),
orig_sumline_rec.array_attribute5(k),
orig_sumline_rec.array_attribute6(k),
orig_sumline_rec.array_attribute7(k),
orig_sumline_rec.array_attribute8(k),
orig_sumline_rec.array_attribute9(k),
orig_sumline_rec.array_attribute10(k));
Update psp_temp_orig_lines LINE
set LINE.acct_group_id = (select MAS.acct_group_id
from psp_temp_orig_sumlines MAS
where MAS.run_id = g_run_id and
nvl(MAS.element_group_id,-9) = nvl(LINE.element_group_id,-9) and
-- MAS.dr_cr_flag = LINE.dr_cr_flag and Commented for Bug 3625667
nvl(MAS.gl_code_combination_id,-9) =
nvl(LINE.gl_code_combination_id,-9) and
nvl(MAS.project_id,-9)= nvl(LINE.project_id,-9) and
nvl(MAS.task_id,-9) = nvl(LINE.task_id,-9) and
nvl(MAS.expenditure_organization_id,-9) =
nvl(LINE.expenditure_organization_id,-9) and
nvl(MAS.award_id, -9) = nvl(LINE.award_id, -9) and
(MAS.expenditure_type = LINE.expenditure_type or
(MAS.expenditure_type is null and LINE.expenditure_type is null))
AND (NVL(mas.attribute_category, 'NULL') = NVL(line.attribute_category, 'NULL')) -- Introduced DFF column check for bug fix 2908859
AND (NVL(mas.attribute1, 'NULL') = NVL(line.attribute1, 'NULL'))
AND (NVL(mas.attribute2, 'NULL') = NVL(line.attribute2, 'NULL'))
AND (NVL(mas.attribute3, 'NULL') = NVL(line.attribute3, 'NULL'))
AND (NVL(mas.attribute4, 'NULL') = NVL(line.attribute4, 'NULL'))
AND (NVL(mas.attribute5, 'NULL') = NVL(line.attribute5, 'NULL'))
AND (NVL(mas.attribute6, 'NULL') = NVL(line.attribute6, 'NULL'))
AND (NVL(mas.attribute7, 'NULL') = NVL(line.attribute7, 'NULL'))
AND (NVL(mas.attribute8, 'NULL') = NVL(line.attribute8, 'NULL'))
AND (NVL(mas.attribute9, 'NULL') = NVL(line.attribute9, 'NULL'))
AND (NVL(mas.attribute10, 'NULL') = NVL(line.attribute10, 'NULL')))
where LINE.run_id = g_run_id;
insert into PSP_TEMP_ORIG_SUMLINES (
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
DR_CR_FLAG,
DISTRIBUTION_SUM,
RUN_ID,
SET_OF_BOOKS_ID,
BUSINESS_GROUP_ID,
ACCT_GROUP_ID,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10)
values ( orig_sumline_rec.array_glccid(k),
orig_sumline_rec.array_project_id(k),
orig_sumline_rec.array_exp_org_id(k),
orig_sumline_rec.array_exp_type(k),
orig_sumline_rec.array_task_id(k),
orig_sumline_rec.array_award_id(k),
orig_sumline_rec.array_dr_cr_flag(k),
orig_sumline_rec.array_distribution_sum(k),
orig_sumline_rec.array_run_id(k),
orig_sumline_rec.array_set_of_books_id(k),
orig_sumline_rec.array_business_group_id(k),
orig_sumline_rec.array_acct_group_id(k),
orig_sumline_rec.array_attribute_category(k),
orig_sumline_rec.array_attribute1(k),
orig_sumline_rec.array_attribute2(k),
orig_sumline_rec.array_attribute3(k),
orig_sumline_rec.array_attribute4(k),
orig_sumline_rec.array_attribute5(k),
orig_sumline_rec.array_attribute6(k),
orig_sumline_rec.array_attribute7(k),
orig_sumline_rec.array_attribute8(k),
orig_sumline_rec.array_attribute9(k),
orig_sumline_rec.array_attribute10(k));
Update psp_temp_orig_lines LINE
set LINE.acct_group_id = (select MAS.acct_group_id
from psp_temp_orig_sumlines MAS
where MAS.run_id = g_run_id and
-- MAS.dr_cr_flag = LINE.dr_cr_flag and Commented for bug 3625667
nvl(MAS.gl_code_combination_id,-9) =
nvl(LINE.gl_code_combination_id,-9) and
nvl(MAS.project_id,-9)= nvl(LINE.project_id,-9) and
nvl(MAS.task_id,-9) = nvl(LINE.task_id,-9) and
nvl(MAS.expenditure_organization_id,-9) =
nvl(LINE.expenditure_organization_id,-9) and
nvl(MAS.award_id, -9) = nvl(LINE.award_id, -9) and
(MAS.expenditure_type = LINE.expenditure_type or
(MAS.expenditure_type is null and LINE.expenditure_type is null))
AND (NVL(mas.attribute_category, 'NULL') = NVL(line.attribute_category, 'NULL')) -- Introduced DFF column check for bug fix 2908859
AND (NVL(mas.attribute1, 'NULL') = NVL(line.attribute1, 'NULL'))
AND (NVL(mas.attribute2, 'NULL') = NVL(line.attribute2, 'NULL'))
AND (NVL(mas.attribute3, 'NULL') = NVL(line.attribute3, 'NULL'))
AND (NVL(mas.attribute4, 'NULL') = NVL(line.attribute4, 'NULL'))
AND (NVL(mas.attribute5, 'NULL') = NVL(line.attribute5, 'NULL'))
AND (NVL(mas.attribute6, 'NULL') = NVL(line.attribute6, 'NULL'))
AND (NVL(mas.attribute7, 'NULL') = NVL(line.attribute7, 'NULL'))
AND (NVL(mas.attribute8, 'NULL') = NVL(line.attribute8, 'NULL'))
AND (NVL(mas.attribute9, 'NULL') = NVL(line.attribute9, 'NULL'))
AND (NVL(mas.attribute10, 'NULL') = NVL(line.attribute10, 'NULL')))
where LINE.run_id = g_run_id;
orig_sumline_rec.array_element_type_id.delete;
orig_sumline_rec.array_element_group_id.delete;
orig_sumline_rec.array_glccid.delete;
orig_sumline_rec.array_project_id.delete;
orig_sumline_rec.array_exp_org_id.delete;
orig_sumline_rec.array_exp_type.delete;
orig_sumline_rec.array_task_id.delete;
orig_sumline_rec.array_award_id.delete;
orig_sumline_rec.array_dr_cr_flag.delete;
orig_sumline_rec.array_distribution_sum.delete;
orig_sumline_rec.array_run_id.delete;
orig_sumline_rec.array_set_of_books_id.delete;
orig_sumline_rec.array_business_group_id.delete;
orig_sumline_rec.array_acct_group_id.delete;
orig_sumline_rec.array_attribute_category.delete;
orig_sumline_rec.array_attribute1.delete;
orig_sumline_rec.array_attribute2.delete;
orig_sumline_rec.array_attribute3.delete;
orig_sumline_rec.array_attribute4.delete;
orig_sumline_rec.array_attribute5.delete;
orig_sumline_rec.array_attribute6.delete;
orig_sumline_rec.array_attribute7.delete;
orig_sumline_rec.array_attribute8.delete;
orig_sumline_rec.array_attribute9.delete;
orig_sumline_rec.array_attribute10.delete;
SELECT
person_id,
assignment_id,
min(distribution_date),
max(distribution_date)
FROM
psp_adjustment_lines
WHERE
batch_name = p_batch_name
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id
GROUP BY
person_id,assignment_id;
/* changed the cursor select for DA-ENH */
/***** Modified the following cursor defn for R12 performance fixes (bug 4507892)
select distinct ppf.full_name,
ppf.employee_number,
pas.assignment_number
from per_people_f ppf,
per_assignments_f pas
where ppf.person_id = l_person_id and
pas.assignment_id = l_assignment_id;
SELECT DISTINCT ppf.full_name,
ppf.employee_number,
paf.assignment_number
FROM per_people_f ppf,
per_assignments_f paf
WHERE ppf.person_id = l_person_id
AND paf.person_id = ppf.person_id
AND paf.assignment_id = l_assignment_id;
SELECT comments ,
currency_code -- Introduced this for bug fix 2916848
FROM psp_adjustment_control_table
WHERE adjustment_batch_name = p_batch_name;
SELECT name
FROM hr_all_organization_units haou,
per_assignments_f paf
WHERE haou.organization_id = paf.organization_id
AND paf.assignment_id = l_assignment_id
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date;
select adjust_by
from psp_adjustment_control_table
where adjustment_batch_name = p_batch_name;
SELECT adj_set_number, SUM(distribution_sum)
FROM psp_temp_dest_sumlines ptdl
WHERE ptdl.run_id = p_run_id
AND ptdl.business_group_id = p_business_group_id
AND ptdl.set_of_books_id = p_set_of_books_id
AND ptdl.dr_cr_flag = 'D'
GROUP BY adj_set_number;
INSERT into psp_temp_dest_sumlines (
acct_group_id, --- added four fields for DA-ENH
adj_set_number,
original_line_flag,
line_number,
element_type_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
dr_cr_flag,
distribution_sum,
distribution_percent,
run_id,
set_of_books_id,
business_group_id,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10)
SELECT
MIN(ROWNUM), --- added for DA-ENH
adj_set_number, --- added for DA-ENH
original_line_flag, --- moved from below for DA-ENH...
line_number, --- added for DA-ENH
element_type_id, --- added for DA-ENH
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
-- dr_cr_flag, Commented for Bug 3625667
DECODE(sign(sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
percent, ---- added for DA-ENH
p_run_id,
p_set_of_books_id,
p_business_group_id,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10
FROM psp_adjustment_lines
WHERE batch_name = p_batch_name
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id
GROUP by adj_set_number,
original_line_flag,
line_number,
element_type_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
-- dr_cr_flag, Commented for Bug 3625667
percent,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10;
INSERT into psp_temp_dest_sumlines (
acct_group_id, --- added four fields for DA-ENH
adj_set_number,
original_line_flag,
line_number,
element_group_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
dr_cr_flag,
distribution_sum,
distribution_percent,
run_id,
set_of_books_id,
business_group_id,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10)
SELECT
MIN(ROWNUM), --- added for DA-ENH
a.adj_set_number, --- added for DA-ENH
a.original_line_flag, --- moved from below for DA-ENH...
a.line_number, --- added for DA-ENH
b.element_group_id, --- added for DA-ENH
a.gl_code_combination_id,
a.project_id,
a.expenditure_organization_id,
a.expenditure_type,
a.task_id,
a.award_id,
-- a.dr_cr_flag, Commented for bug 3625667
decode(sign(sum(decode(a.dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
a.percent, ---- added for DA-ENH
p_run_id,
p_set_of_books_id,
p_business_group_id,
a.attribute_category, -- Introduced DFF columns for bug fix 2908859
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10
FROM psp_adjustment_lines a,
psp_group_element_list b
WHERE a.batch_name = p_batch_name
and a.business_group_id = p_business_group_id
and a.set_of_books_id = p_set_of_books_id
and b.element_type_id(+) = a.element_type_id
GROUP by a.adj_set_number,
a.original_line_flag,
a.line_number,
b.element_group_id,
a.gl_code_combination_id,
a.project_id,
a.expenditure_organization_id,
a.expenditure_type,
a.task_id,
a.award_id,
-- a.dr_cr_flag, Commented for bug 3625667
a.percent,
a.attribute_category, -- Introduced DFF columns for bug fix 2908859
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10;
INSERT into psp_temp_dest_sumlines (
acct_group_id, --- added four fields for DA-ENH
adj_set_number,
original_line_flag,
line_number,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
dr_cr_flag,
distribution_sum,
distribution_percent,
run_id,
set_of_books_id,
business_group_id,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10)
SELECT
MIN(ROWNUM), --- added for DA-ENH
adj_set_number, --- added for DA-ENH
original_line_flag, --- moved from below for DA-ENH...
line_number, --- added for DA-ENH
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
-- dr_cr_flag, Commented for Bug 3625667
decode(sign(sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
percent, ---- added for DA-ENH
p_run_id,
p_set_of_books_id,
p_business_group_id,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10
FROM psp_adjustment_lines
WHERE batch_name = p_batch_name
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id
GROUP by adj_set_number,
original_line_flag,
line_number,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
-- dr_cr_flag, Commented for Bug 3625667
percent,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10;
INSERT into psp_temp_dest_sumlines (
acct_group_id, --- added four fields for DA-ENH
adj_set_number,
original_line_flag,
line_number,
element_type_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
dr_cr_flag,
distribution_sum,
distribution_percent,
run_id,
set_of_books_id,
business_group_id)
SELECT
MIN(ROWNUM), --- added for DA-ENH
adj_set_number, --- added for DA-ENH
original_line_flag, --- moved from below for DA-ENH...
line_number, --- added for DA-ENH
element_type_id, --- added for DA-ENH
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
-- dr_cr_flag, Commented for Bug 3625667
DECODE(sign(sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
percent, ---- added for DA-ENH
p_run_id,
p_set_of_books_id,
p_business_group_id
FROM psp_adjustment_lines
WHERE batch_name = p_batch_name
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id
GROUP by adj_set_number,
original_line_flag,
line_number,
element_type_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
-- dr_cr_flag, Commented for Bug 3625667
percent;
INSERT into psp_temp_dest_sumlines (
acct_group_id, --- added four fields for DA-ENH
adj_set_number,
original_line_flag,
line_number,
element_group_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
dr_cr_flag,
distribution_sum,
distribution_percent,
run_id,
set_of_books_id,
business_group_id)
SELECT
MIN(ROWNUM), --- added for DA-ENH
a.adj_set_number, --- added for DA-ENH
a.original_line_flag, --- moved from below for DA-ENH...
a.line_number, --- added for DA-ENH
b.element_group_id, --- added for DA-ENH
a.gl_code_combination_id,
a.project_id,
a.expenditure_organization_id,
a.expenditure_type,
a.task_id,
a.award_id,
-- a.dr_cr_flag, Commented for bug 3625667
decode(sign(sum(decode(a.dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
a.percent, ---- added for DA-ENH
p_run_id,
p_set_of_books_id,
p_business_group_id
FROM psp_adjustment_lines a,
psp_group_element_list b
WHERE a.batch_name = p_batch_name
and a.business_group_id = p_business_group_id
and a.set_of_books_id = p_set_of_books_id
and b.element_type_id(+) = a.element_type_id
GROUP by a.adj_set_number,
a.original_line_flag,
a.line_number,
b.element_group_id,
a.gl_code_combination_id,
a.project_id,
a.expenditure_organization_id,
a.expenditure_type,
a.task_id,
a.award_id,
-- a.dr_cr_flag, Commented for bug 3625667
a.percent;
INSERT into psp_temp_dest_sumlines (
acct_group_id, --- added four fields for DA-ENH
adj_set_number,
original_line_flag,
line_number,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
dr_cr_flag,
distribution_sum,
distribution_percent,
run_id,
set_of_books_id,
business_group_id)
SELECT
MIN(ROWNUM), --- added for DA-ENH
adj_set_number, --- added for DA-ENH
original_line_flag, --- moved from below for DA-ENH...
line_number, --- added for DA-ENH
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
-- dr_cr_flag, Commented for Bug 3625667
decode(sign(sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
percent, ---- added for DA-ENH
p_run_id,
p_set_of_books_id,
p_business_group_id
FROM psp_adjustment_lines
WHERE batch_name = p_batch_name
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id
GROUP by adj_set_number,
original_line_flag,
line_number,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
-- dr_cr_flag, Commented for Bug 3625667
percent;
UPDATE psp_temp_dest_sumlines ptdl
SET distribution_percent = (ROUND((100 * ABS(distribution_sum) / l_adj_set_total), 2))
WHERE ptdl.run_id = p_run_id
AND ptdl.business_group_id = p_business_group_id
AND ptdl.set_of_books_id = p_set_of_books_id
AND ptdl.adj_set_number = l_adj_set_number;
UPDATE psp_temp_dest_sumlines ptdl
SET distribution_percent = (-1 * distribution_percent)
WHERE ptdl.run_id = p_run_id
AND ptdl.business_group_id = p_business_group_id
AND ptdl.set_of_books_id = p_set_of_books_id
AND ptdl.dr_cr_flag = 'C';
procedure insert_adj_lines(p_person_id IN NUMBER,
p_assignment_id IN NUMBER,
p_element_type_id IN NUMBER,
p_batch_name IN VARCHAR2,
p_distribution_date IN DATE,
p_effective_date IN DATE,
p_distribution_amount IN NUMBER,
p_dr_cr_flag IN VARCHAR2,
p_payroll_control_id IN NUMBER,
p_time_period_id IN NUMBER,
p_status_code IN VARCHAR2,
p_set_of_books_id IN NUMBER,
p_gl_code_combination_id IN NUMBER,
p_project_id IN NUMBER,
p_expenditure_organization_id IN NUMBER,
p_expenditure_type IN VARCHAR2,
p_task_id IN NUMBER,
p_award_id IN NUMBER,
p_reversal_entry_flag IN VARCHAR2,
p_original_line_flag IN VARCHAR2,
p_distribution_percent In NUMBER,
p_orig_source_type IN VARCHAR2,
p_orig_line_id IN NUMBER,
p_business_group_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2) IS
begin
insert into psp_adjustment_lines(adjustment_line_id,
person_id,
assignment_id,
element_type_id,
distribution_date,
effective_date,
distribution_amount,
dr_cr_flag,
payroll_control_id,
source_type,
source_code,
time_period_id,
batch_name,
status_code,
set_of_books_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
suspense_org_account_id,
suspense_reason_code,
effort_report_id,
version_num,
summary_line_id,
reversal_entry_flag,
original_line_flag,
user_defined_field,
percent,
orig_source_type,
orig_line_id,
business_group_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values(psp_adjustment_lines_s.nextval,
p_person_id,
p_assignment_id,
p_element_type_id,
p_distribution_date,
p_effective_date,
p_distribution_amount,
p_dr_cr_flag,
p_payroll_control_id,
'A',
'Adjustments',
p_time_period_id,
p_batch_name,
p_status_code,
p_set_of_books_id,
p_gl_code_combination_id,
p_project_id,
p_expenditure_organization_id,
p_expenditure_type,
p_task_id,
p_award_id,
null,
null,
null,
null,
null,
p_reversal_entry_flag,
p_original_line_flag,
null,
p_distribution_percent,
p_orig_source_type,
p_orig_line_id,
p_business_group_id,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.user_id,
sysdate);
g_error_api_path := 'INSERT_ADJ_LINES:Batch Name '||p_batch_name||':'||g_error_api_path;
procedure insert_adjustment_control(p_batch_name IN VARCHAR2,
p_batch_comments IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_gl_posting_override_date IN DATE DEFAULT NULL,
-- Fixed 1087529
p_person_id IN NUMBER,
p_assignment_id IN NUMBER,
-- p_element_type_id IN NUMBER, commented for DA-ENH
p_distribution_start_date IN DATE,
p_distribution_end_date IN DATE,
p_currency_code IN VARCHAR2, -- Introduced for bug fix 2916848
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_adjust_by IN VARCHAR2) is
begin
insert into psp_adjustment_control_table(
adjustment_batch_name,
comments,
gl_posting_override_date,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
person_id,
assignment_id,
---element_type_id, commented for DA-ENH
distribution_start_date,
distribution_end_date,
currency_code, -- Introduced for bug fix 2916848
business_group_id,
set_of_books_id,
adjust_by) --- added for DA-ENH
values(
p_batch_name,
p_batch_comments,
p_gl_posting_override_date,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.user_id,
sysdate,
p_person_id,
p_assignment_id,
--- p_element_type_id, commented for DA-ENH
p_distribution_start_date,
p_distribution_end_date,
p_currency_code, -- Introduced for bug fix 2916848
p_business_group_id,
p_set_of_books_id,
p_adjust_by); --- added for DA-ENH
g_error_api_path := 'INSERT_ADJUSTMENT_CONTROL:Batch Name '||p_batch_name||':'||g_error_api_path;
procedure update_adjustment_ctrl_comment(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_batch_name IN VARCHAR2,
p_comments IN VARCHAR2)
IS
l_msg_count NUMBER;
update psp_adjustment_control_table
set comments = p_comments,
approver_id = FND_GLOBAL.USER_ID, -- 1087529
approval_date = SYSDATE -- Added to fix bug 1661405. approval_date is a new column added to the table
where adjustment_batch_name = p_batch_name;
update psp_payroll_controls set status_code = 'N'
where batch_name = p_batch_name and source_type = 'A'
and status_code = 'C';
g_error_api_path := 'UPDATE_ADJUSTMENT_CONTROL:'||g_error_api_path;
procedure update_payroll_control(p_batch_name IN VARCHAR2,
p_payroll_control_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_currency_code IN VARCHAR2, -- Introduced for bug fix 2916848
p_return_status OUT NOCOPY VARCHAR2,
p_gl_posting_override_date IN DATE DEFAULT NULL) is
l_payroll_control_id NUMBER(10);
select distinct palh.time_period_id
from psp_adjustment_lines palh
where palh.batch_name = p_batch_name
and palh.business_group_id = p_business_group_id
and palh.set_of_books_id = p_set_of_books_id;
and to be able to select both teh DR amount and cr amount
cursor tot_dr_c is
select sum(distribution_amount)
from psp_adjustment_lines
where batch_name = p_batch_name
and time_period_id = l_time_period_id
and dr_cr_flag = 'D'
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id
group by batch_name, time_period_id, dr_cr_flag;
select sum(distribution_amount)
from psp_adjustment_lines
where batch_name = p_batch_name
and time_period_id = l_time_period_id
and dr_cr_flag = 'C'
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id
group by batch_name, time_period_id, dr_cr_flag;
SELECT SUM(DECODE(pal.dr_cr_flag,'D',pal.distribution_amount,0)) l_total_dr,
SUM(DECODE(pal.dr_cr_flag,'C',pal.distribution_amount,0)) l_total_cr
FROM psp_adjustment_lines pal
WHERE pal.batch_name = p_batch_name
and pal.time_period_id = l_time_period_id;
select payroll_id
from per_time_periods
where time_period_id = l_time_period_id;
Commented for Bug 2252881 : Both the credit and debit amount are being selected in the cursor tot_dr_c
l_tot_dr := 0;
insert into psp_payroll_controls(
payroll_control_id,
payroll_action_id,
payroll_source_code,
source_type,
payroll_id,
time_period_id,
batch_name,
dist_dr_amount,
dist_cr_amount,
status_code,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
run_id,
GL_POSTING_OVERRIDE_DATE,
GMS_POSTING_OVERRIDE_DATE,
business_group_id,
set_of_books_id,
-- Introduced for bug fix 2916848
currency_code,
exchange_rate_type)
values(
p_payroll_control_id,
1,
'Adjustments',
'A',
l_payroll_id, /* 1, --Bug 1677534 */
l_time_period_id,
p_batch_name,
l_tot_dr,
l_tot_cr,
'C',
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.user_id,
sysdate,
null,
p_gl_posting_override_date,
null,
p_business_group_id,
p_set_of_books_id,
-- Introduced for bug fix 2916848
p_currency_code,
null);
select psp_payroll_controls_s.nextval into l_payroll_control_id from dual;
insert into psp_payroll_controls(
payroll_control_id,
payroll_action_id,
payroll_source_code,
source_type,
payroll_id,
time_period_id,
batch_name,
dist_dr_amount,
dist_cr_amount,
status_code,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
run_id,
GL_POSTING_OVERRIDE_DATE,
GMS_POSTING_OVERRIDE_DATE,
business_group_id,
set_of_books_id,
-- Introduced for bug fix 2916848
currency_code,
exchange_rate_type)
values(
l_payroll_control_id,
1,
'Adjustments',
'A',
l_payroll_id, /* 1, --Bug 1677534 */
l_time_period_id,
p_batch_name,
l_tot_dr,
l_tot_cr,
'C',
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.user_id,
sysdate,
null,
p_gl_posting_override_date,
null,
p_business_group_id,
p_set_of_books_id,
-- Introduced for bug fix 2916848
p_currency_code,
null);
update psp_adjustment_lines
set payroll_control_id = l_payroll_control_id
where time_period_id = l_time_period_id
and batch_name = p_batch_name
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id;
g_error_api_path := 'UPDATE_PAYROLL_CONTROL:Batch Name '||p_batch_name||':'||g_error_api_path;
procedure update_effort_reports(p_batch_name IN VARCHAR2,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2) is
/*** Commented the following for bug fix 2724110
l_per_id NUMBER(9);
select distinct person_id, distribution_date
from psp_adjustment_lines
where batch_name = p_batch_name
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id
and element_type_id in (select element_type_id from psp_effort_report_elements
where use_in_effort_report='Y');
select template_id
from psp_effort_reports
where person_id = p_per_id;
select per.template_id
from psp_effort_reports per
where per.person_id = p_per_id
and per.business_group_id = p_business_group_id
and per.set_of_books_id = p_set_of_books_id
and per.template_id in (select pert.template_id
from psp_effort_report_templates pert
where pert.report_type = 'N'
-- and pert.person_id = p_per_id
and pert.business_group_id = p_business_group_id
and pert.set_of_books_id = p_set_of_books_id);
select begin_date, end_date
from psp_effort_report_templates
where template_id = p_template_id and
report_type='N'
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id;
SELECT pert.template_id, pert.begin_date, pert.end_date
FROM psp_effort_report_templates pert
WHERE pert.report_type ='N'
AND pert.template_id IN(
SELECT per.template_id
FROM psp_effort_reports per
WHERE per.person_id = p_per_id
AND per.business_group_id = p_business_group_id
AND per.set_of_books_id = p_set_of_books_id
);
UPDATE psp_effort_reports
SET previous_status_code = status_code
WHERE person_id = l_per_id
AND template_id = l_template_id;
update psp_effort_reports
set status_code = 'S'
where person_id = l_per_id
and template_id = l_template_id;
UPDATE psp_effort_reports per
SET per.previous_status_code = per.status_code,
per.status_code = 'S'
WHERE per.status_code <> 'S'
AND per.business_group_id = p_business_group_id
AND per.set_of_books_id = p_set_of_books_id
AND (per.person_id, per.template_id) IN
(SELECT pal.person_id, pert.template_id
FROM psp_effort_report_templates pert,
psp_effort_reports per2,
psp_adjustment_lines pal
WHERE pal.batch_name = p_batch_name
AND pal.business_group_id = p_business_group_id
AND pal.set_of_books_id = p_set_of_books_id
AND pert.business_group_id = p_business_group_id
AND pert.set_of_books_id = p_set_of_books_id
AND pert.template_id = per2.template_id
AND pert.report_type = 'N'
AND per2.person_id = pal.person_id
AND pal.distribution_date BETWEEN pert.begin_date
AND pert.end_date
AND pal.element_type_id IN
(SELECT pere.element_type_id
FROM psp_effort_report_elements pere
WHERE pere.use_in_effort_report = 'Y'));
g_error_api_path := 'UPDATE_EFFORT_REPORTS:Batch Name '||p_batch_name||':'||g_error_api_path;
select count(*), element_type_id
from psp_adjustment_lines
where batch_name = p_batch_name
group by element_type_id
having sum(decode(dr_cr_flag,'D',distribution_amount,-distribution_amount)) <> 0;
select element_name
from pay_element_types
where element_type_id = l_element_type_id;
select PTDS.adj_set_number,
PTOS.element_type_id,
PTOS.time_period_id,
Sum(decode (PTOS.dr_cr_flag, 'D', PTOS.distribution_amount,
-PTOS.distribution_amount))
from psp_temp_dest_sumlines PTDS,
psp_temp_orig_lines PTOS
where PTDS.original_line_flag = 'Y'
and PTDS.acct_group_id = PTOS.acct_group_id
and PTDS.run_id = g_run_id
and PTOS.run_id = g_run_id
group by PTDS.adj_set_number,
PTOS.element_type_id,
PTOS.time_period_id;
select adj_set_number,
time_period_id,
element_type_id,
line_number,
sum(decode(dr_cr_flag, 'D', round(distribution_amount, g_precision),
-round(distribution_amount, g_precision))), --|>actual amnt
round(sum(decode(dr_cr_flag, 'D', distribution_amount,
-distribution_amount)) -
sum(decode(dr_cr_flag, 'D',round(distribution_amount, g_precision), --|>delta amnt =
-round(distribution_amount, g_precision))), g_precision), -- unrounded amnt - act amnt
max(adjustment_line_id)
from psp_adjustment_lines
where batch_name = p_batch_name and
original_line_flag = 'N'
group by adj_set_number, time_period_id, element_type_id, line_number;
select adj_set_number,
element_type_id,
time_period_id,
sum(decode(dr_cr_flag, 'D', distribution_amount,
-distribution_amount)),
max(adjustment_line_id),
0 --- delta sum
from psp_adjustment_lines
where original_line_flag = 'N' and
batch_name = p_batch_name
group by adj_set_number, element_type_id, time_period_id;
select sum(decode(LINE.dr_cr_flag,'D',LINE.distribution_amount,
-LINE.distribution_amount)) amount,
DEST.distribution_sum,
LINE.adj_set_number,
LINE.line_number
from psp_adjustment_lines LINE,
psp_temp_dest_sumlines DEST
where LINE.batch_name = p_batch_name and
LINE.original_line_flag = 'N' and
DEST.run_id = p_run_id and
DEST.original_line_flag = 'N' and
DEST.adj_set_number = LINE.adj_set_number and
DEST.line_number = LINE.line_number
group by LINE.adj_set_number, LINE.line_number, DEST.distribution_sum;
select element_type_id,
time_period_id
from psp_adjustment_lines
where batch_name = p_batch_name
and original_line_flag = 'N'
and adj_set_number = p_adj_set_number
and line_number = p_adj_line_number
order by element_type_id, time_period_id desc;
select element_type_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id
from psp_adjustment_lines
where batch_name = p_batch_name
and original_line_flag <> 'Y'
and set_of_books_id = p_set_of_books_id
and business_group_id = p_business_group_id
Group by element_type_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id ;
select effective_date ,
adjustment_line_id
from psp_adjustment_lines
where element_type_id = p_element_type_id
and project_id = p_project_id
and expenditure_organization_id = p_expenditure_organization_id
and expenditure_type = p_expenditure_type
and task_id = p_task_id
and award_id = p_award_id
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id
and batch_name = p_batch_name
and original_line_flag <> 'Y'
group by effective_date,
adjustment_line_id;
SELECT 'x'
FROM psp_organizations_expend_v
WHERE organization_id = p_exp_org_id
and trunc(p_eff_date) between date_from and nvl(date_to,trunc(p_eff_date));
SELECT orig_line_id,
orig_source_type
FROM psp_adjustment_lines
WHERE payroll_control_id = l_payroll_control_id;
SELECT sum(decode(dr_cr_flag,'D',distribution_amount,-distribution_amount)) amount
FROM psp_adjustment_lines
WHERE batch_name = p_batch_name;
select psp_payroll_controls_s.nextval into l_payroll_control_id from dual;
hr_utility.trace('Inserting into PSP_ADJUSTMENT_CONTROL_TABLE : batch = '||p_batch_name);
insert_adjustment_control(p_batch_name,
p_batch_comments,
l_return_status,
p_gl_posting_override_date,
p_person_id,
p_assignment_id,
---p_element_type_id, replaced with NULL for DA-ENH
p_distribution_start_date,
p_distribution_end_date,
p_currency_code, -- Introduced for bug fix 2916848
p_business_group_id,
p_set_of_books_id,
p_adjust_by); --- added param for DA-ENH
hr_utility.trace('Inserting into PSP_ADJUSTMENT_LINES - 10');
insert into psp_adjustment_lines(adjustment_line_id,
person_id,
assignment_id,
element_type_id,
distribution_date,
effective_date,
distribution_amount,
dr_cr_flag,
payroll_control_id,
source_type,
source_code,
time_period_id,
batch_name,
status_code,
set_of_books_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
reversal_entry_flag,
original_line_flag,
user_defined_field,
percent,
orig_source_type,
orig_line_id,
business_group_id,
adj_set_number, --- new column for DA-ENH
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
line_number, --- new column for DA-ENH
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10)
select psp_adjustment_lines_s.nextval,
p_person_id,
p_assignment_id,
ptol.element_type_id,
ptol.orig_distribution_date,
ptol.effective_date,
ptol.distribution_amount,
decode(ptol.dr_cr_flag,'D','C','D'), -- reverse the dr_cr_flag
l_payroll_control_id,
'A',
'Adjustments',
ptol.time_period_id,
p_batch_name,
'N',
p_set_of_books_id,
ptds.gl_code_combination_id,
ptds.project_id,
ptds.expenditure_organization_id,
ptds.expenditure_type,
ptds.task_id, ptds.award_id,
null, -- reversal entry flag.
'Y', -- original line flag
null,
ptds.distribution_percent,
ptol.orig_source_type,
ptol.orig_line_id,
p_business_group_id,
ptds.adj_set_number,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.user_id,
sysdate,
ptds.line_number,
ptds.attribute_category, -- Introduced DFF columns for bug fix 2908859
ptds.attribute1,
ptds.attribute2,
ptds.attribute3,
ptds.attribute4,
ptds.attribute5,
ptds.attribute6,
ptds.attribute7,
ptds.attribute8,
ptds.attribute9,
ptds.attribute10
from psp_temp_orig_lines ptol, psp_temp_dest_sumlines ptds
where ptol.business_group_id = p_business_group_id
and ptol.set_of_books_id = p_set_of_books_id
and ptol.business_group_id = ptds.business_group_id
and ptol.set_of_books_id = ptds.set_of_books_id
and ptol.acct_group_id = ptds.acct_group_id
and ptds.original_line_flag = 'Y'
and ptol.run_id = p_run_id
and ptds.run_id = ptol.run_id;
hr_utility.trace('Inserted into PSP_ADJUSTMENT_LINES - 10');
/***** Converted the following update statements into BULK update for R12 performance fixes (bug 4507892)
UPDATE psp_distribution_lines_history
SET adjustment_batch_name = p_batch_name
WHERE distribution_line_id in ( select orig_line_id
from psp_adjustment_lines
where payroll_control_id = l_payroll_control_id
and orig_source_type = 'D');
UPDATE psp_pre_gen_dist_lines_history
SET adjustment_batch_name = p_batch_name
WHERE pre_gen_dist_line_id in ( select orig_line_id
from psp_adjustment_lines
where payroll_control_id = l_payroll_control_id
and orig_source_type = 'P');
UPDATE psp_adjustment_lines_history
SET adjustment_batch_name = p_batch_name
WHERE adjustment_line_id in ( select orig_line_id
from psp_adjustment_lines
where payroll_control_id = l_payroll_control_id
and orig_source_type = 'A');
UPDATE psp_distribution_lines_history
SET adjustment_batch_name = p_batch_name
WHERE distribution_line_id = r_orig_lines.orig_line_id(I)
AND r_orig_lines.orig_source_type(I) = 'D';
UPDATE psp_pre_gen_dist_lines_history
SET adjustment_batch_name = p_batch_name
WHERE pre_gen_dist_line_id = r_orig_lines.orig_line_id(I)
AND r_orig_lines.orig_source_type(I) = 'P';
UPDATE psp_adjustment_lines_history
SET adjustment_batch_name = p_batch_name
WHERE adjustment_line_id = r_orig_lines.orig_line_id(I)
AND r_orig_lines.orig_source_type(I) = 'A';
r_orig_lines.orig_line_id.DELETE;
r_orig_lines.orig_source_type.DELETE;
hr_utility.trace('Completed BULK updates');
hr_utility.trace('Inserting into PSP_ADJUSTMENT_LINES - 20');
insert into psp_adjustment_lines
(adjustment_line_id,
person_id,
assignment_id,
element_type_id,
distribution_date,
effective_date,
distribution_amount,
dr_cr_flag,
payroll_control_id,
source_type,
source_code,
time_period_id,
batch_name,
status_code,
set_of_books_id,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
reversal_entry_flag,
original_line_flag,
user_defined_field,
percent,
orig_source_type,
orig_line_id,
business_group_id,
adj_set_number, --- new column for DA-ENH
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
line_number,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10) --- new column for DA-ENH
select psp_adjustment_lines_s.nextval,
p_person_id,
p_assignment_id,
LINES.element_type_id,
LINES.orig_distribution_date,
-- LINES.effective_date, Commented for bug fix 3927570
-- Introduced the following for bug fix 3892097
fnd_date.canonical_to_date(DECODE(trans_type.transaction_type_count,
1, fnd_date.date_to_canonical(LINES.effective_date),
DECODE(dest.gl_code_combination_id,
NULL, fnd_date.date_to_canonical(lines.orig_distribution_date),
fnd_date.date_to_canonical(ptp.end_date)))) effective_date,
-- End of changes for bug fix 3927570
LINES.distribution_amount * DEST.distribution_percent/100, ------- unrounded, unlimited precision DA_ENH
LINES.dr_cr_flag,
l_payroll_control_id,
'A',
'Adjustments',
LINES.time_period_id,
p_batch_name,
'N',
p_set_of_books_id,
DEST.gl_code_combination_id,
DEST.project_id,
DEST.expenditure_organization_id,
DEST.expenditure_type,
DEST.task_id,
DEST.award_id,
null, -- reversal entry flag.
'N', -- original line flag
null,
DEST.distribution_percent,
LINES.orig_source_type,
LINES.orig_line_id,
p_business_group_id,
DEST.adj_set_number,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.user_id,
sysdate,
DEST.line_number,
dest.attribute_category, --Introduced DFF columns for bug fix 2908859
dest.attribute1,
dest.attribute2,
dest.attribute3,
dest.attribute4,
dest.attribute5,
dest.attribute6,
dest.attribute7,
dest.attribute8,
dest.attribute9,
dest.attribute10
from psp_temp_dest_sumlines ORIG,
psp_temp_dest_sumlines DEST,
psp_temp_orig_lines LINES,
-- Introduced the following for bug fix 3892097
per_time_periods ptp,
(SELECT adj_set_number,
COUNT(DISTINCT DECODE(gl_code_combination_id, NULL, 'P', 'G')) transaction_type_count
FROM psp_temp_dest_sumlines ptds
WHERE ptds.run_id = g_run_id
GROUP BY adj_set_number) trans_type
-- End of changes for bug fix 3892097
where ORIG.run_id = g_run_id and
DEST.run_id = g_run_id and
LINES.run_id = g_run_id and
-- Introduced the following for bug fix 3892097
ptp.time_period_id = lines.time_period_id AND
dest.adj_set_number = trans_type.adj_set_number AND
-- End of changes for bug fix 3892097
DEST.adj_set_number = ORIG.adj_set_number and
LINES.acct_group_id = ORIG.acct_group_id and
nvl(ORIG.original_line_flag,'Y') = 'Y' and
DEST.original_line_flag = 'N';
hr_utility.trace('Inserted into PSP_ADJUSTMENT_LINES - 20');
update psp_adjustment_lines
set distribution_amount = round(distribution_amount, g_precision)
where original_line_flag = 'N'and
batch_name = p_batch_name;
update psp_adjustment_lines
set distribution_amount = distribution_amount +
decode(dr_cr_flag,'D', r_sline_ideal_amnt_matrix.delta_sum(i),
-r_sline_ideal_amnt_matrix.delta_sum(i))
where adjustment_line_id = r_sline_ideal_amnt_matrix.adjustment_sline_id(i);
r_sline_ideal_amnt_matrix.adj_set_number.delete;
r_sline_ideal_amnt_matrix.time_period_id.delete;
r_sline_ideal_amnt_matrix.sline_number.delete;
r_sline_ideal_amnt_matrix.distribution_sum.delete;
r_sline_ideal_amnt_matrix.delta_sum.delete;
r_sline_ideal_amnt_matrix.adjustment_sline_id.delete;
r_adj_matrix. adj_set_number.delete;
r_adj_matrix.element_type_id.delete;
r_adj_matrix.distribution_sum.delete;
update psp_adjustment_lines
set distribution_amount = distribution_amount +
decode(dr_cr_flag, 'D', r_sline_actual_amnt_matrix.delta_sum(I),
- r_sline_actual_amnt_matrix.delta_sum(I))
where adjustment_line_id = r_sline_actual_amnt_matrix.adjustment_sline_id(i);
r_sline_actual_amnt_matrix.adj_set_number.delete;
r_sline_actual_amnt_matrix.distribution_sum.delete;
r_sline_actual_amnt_matrix.adjustment_sline_id.delete;
r_sline_actual_amnt_matrix.delta_sum.delete;
update psp_adjustment_lines
set distribution_amount = distribution_amount +
decode(dr_cr_flag,'D',r_dest_diff.distribution_sum-r_dest_diff.amount,
-r_dest_diff.distribution_sum+r_dest_diff.amount)
where adjustment_line_id in
(select max(adjustment_line_id)
from psp_adjustment_lines
where batch_name = p_batch_name and
original_line_flag = 'N' and
line_number = r_dest_diff.line_number and
adj_set_number = r_dest_diff.adj_set_number and
element_type_id = r_element_tp.element_type_id and
time_period_id = r_element_tp.time_period_id);
delete psp_adjustment_lines
where distribution_amount = 0
and batch_name = p_batch_name;
deferred_autopop_rec.array_element_type_id.delete;
deferred_autopop_rec.array_glccid.delete;
deferred_autopop_rec.array_project_id.delete;
deferred_autopop_rec.array_exp_org_id.delete;
deferred_autopop_rec.array_exp_type.delete;
deferred_autopop_rec.array_task_id.delete;
deferred_autopop_rec.array_award_id.delete;
final_autopop_rec.array_element_type_id.delete;
final_autopop_rec.array_glccid.delete;
final_autopop_rec.array_project_id.delete;
final_autopop_rec.array_exp_org_id.delete;
final_autopop_rec.array_exp_type.delete;
final_autopop_rec.array_task_id.delete;
final_autopop_rec.array_award_id.delete;
Update psp_adjustment_lines
set
gl_code_combination_id = final_autopop_rec.array_glccid(i),
project_id = final_autopop_rec.array_project_id(i),
expenditure_organization_id = final_autopop_rec.array_exp_org_id(i),
expenditure_type = final_autopop_rec.array_exp_type(i),
task_id = final_autopop_rec.array_task_id(i),
award_id = final_autopop_rec.array_award_id(i)
Where Element_type_id = deferred_autopop_rec.array_element_type_id(i)
And nvl(gl_code_combination_id,0) =nvl(deferred_autopop_rec.array_glccid(i),0)
And nvl(project_id,0) = nvl(deferred_autopop_rec.array_project_id(i),0)
And nvl(expenditure_organization_id,0) =nvl(deferred_autopop_rec.array_exp_org_id(i),0)
And nvl(expenditure_type,0) = nvl(deferred_autopop_rec.array_exp_type(i),0)
And nvl(task_id,0) = nvl(deferred_autopop_rec.array_task_id(i),0)
And nvl(award_id,0) =nvl(deferred_autopop_rec.array_award_id(i),0)
And batch_name = p_batch_name
And business_group_id = p_business_group_id
And set_of_books_id = p_set_of_books_id
AND original_line_flag <> 'Y'; -- Added for Bug 5013847
update_effort_reports(p_batch_name,
p_business_group_id,
p_set_of_books_id,
l_return_status);
update_payroll_control(p_batch_name,
l_payroll_control_id,
p_business_group_id,
p_set_of_books_id,
p_currency_code, -- Introduced for bug fix 2916848
l_return_status,
p_gl_posting_override_date);
delete from psp_temp_orig_lines where run_id = p_run_id;
delete from psp_temp_orig_sumlines where run_id = p_run_id;
delete from psp_temp_dest_sumlines where run_id = p_run_id;
/***** Commented the following DELETE statements for the following reasons as part of R12 performance fixes (bug 4507892)
1) These DELETE statements donot have proper COMMIT logic.
2) Doesnt check for proper run_id.
3) Non-performant SQLs.
delete from psp_temp_orig_lines;
delete from psp_temp_orig_sumlines;
delete from psp_temp_dest_sumlines;
PROCEDURE insert_psp_clearing_account(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_reversing_gl_ccid IN NUMBER,
p_comments IN VARCHAR2,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_payroll_id IN Number,
p_rowid OUT NOCOPY VARCHAR2) IS
v_count number;
select count(*)
from psp_clearing_account
where BUSINESS_GROUP_ID = p_business_group_id
AND SET_OF_BOOKS_ID = p_set_of_books_id
AND PAYROLL_ID = p_payroll_id;
insert into psp_clearing_account(reversing_gl_ccid,
comments,
business_group_id,
set_of_books_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
PAYROLL_ID)
values(p_reversing_gl_ccid,
p_comments,
p_business_group_id,
p_set_of_books_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.user_id,
sysdate,
p_payroll_id) RETURNING rowid into p_rowid;
errbuf:= 'PSP_ADJ_DRIVER : INSERT_PSP_CLEARING_ACCOUNT' || sqlerrm;
end insert_psp_clearing_account;
PROCEDURE update_psp_clearing_account(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_reversing_gl_ccid IN NUMBER,
p_comments IN VARCHAR2,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_payroll_id IN Number,
p_rowid IN VARCHAR2) IS
begin
update psp_clearing_account
set reversing_gl_ccid = p_reversing_gl_ccid,
comments = p_comments,
business_group_id = p_business_group_id,
set_of_books_id = p_set_of_books_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id,
payroll_id = p_payroll_id
where business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND rowid = p_rowid;
errbuf:= 'PSP_ADJ_DRIVER : UPDATE_PSP_CLEARING_ACCOUNT' || sqlerrm;
end update_psp_clearing_account;
PROCEDURE delete_psp_clearing_account(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_reversing_gl_ccid IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_rowid IN VARCHAR2) IS
begin
delete from psp_clearing_account
where business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id
and rowid = p_rowid;
errbuf:= 'PSP_ADJ_DRIVER : DELETE_PSP_CLEARING_ACCOUNT' || sqlerrm;
end delete_psp_clearing_account;
cursor c1 is select
BUSINESS_GROUP_ID,
SET_OF_BOOKS_ID,
REVERSING_GL_CCID,
COMMENTS,
PAYROLL_ID
from PSP_CLEARING_ACCOUNT
where business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id
and payroll_id = p_payroll_id
and reversing_gl_ccid = p_reversing_gl_ccid
for update nowait;
SELECT orig_line_id,
orig_source_type
FROM psp_adjustment_lines
WHERE original_line_flag = 'Y'
AND batch_name = p_batch_name
and business_group_id = p_business_group_id
and set_of_books_id = p_set_of_books_id;
SELECT report.effort_report_id,
report.person_id,
min(begin_date),
max(end_date)
FROM psp_effort_reports report,
psp_adjustment_lines line,
psp_effort_report_templates template
WHERE line.batch_name = p_batch_name
AND report.person_id = line.person_id
AND template.template_id = report.template_id
AND line.distribution_date BETWEEN template.begin_date AND template.end_date
AND template.report_type = 'N'
AND report.status_code = 'S'
GROUP BY report.effort_report_id,
report.person_id;
SELECT distinct report.effort_report_id
FROM psp_adjustment_lines line,
psp_effort_reports report,
psp_effort_report_templates template
WHERE line.business_group_id = p_business_group_id
and line.set_of_books_id = p_set_of_books_id
and line.batch_name = p_batch_name
AND line.person_id = report.person_id
and line.business_group_id = template.business_group_id
and line.set_of_books_id = template.set_of_books_id
AND template.template_id = report.template_id
AND line.distribution_date BETWEEN template.begin_date AND template.end_date
AND template.report_type = 'N';
SELECT COUNT(1)
FROM psp_adjustment_lines pal
WHERE person_id = p_person_id
AND batch_name <> p_batch_name
AND distribution_date BETWEEN p_begin_date AND p_end_date
AND EXISTS (SELECT 1
FROM psp_effort_report_elements pere
WHERE pere.element_type_id = pal.element_type_id
AND pere.use_in_effort_report='Y')
AND ROWNUM = 1;
/*1. Update one of the history tables PSP_ADJUSTMENT_LINES_HISTORY or */
/* PSP_PRE_GEN_DIST_LINES_HISTORY or PSP_DISTRIBUTION_LINES_HISTORY */
/* by setting the adjustment_batch_name to NULL. */
/*---------------------------------------------------------------------*/
OPEN get_line_id_csr;
UPDATE psp_adjustment_lines_history
SET adjustment_batch_name = NULL
WHERE adjustment_line_id = l_orig_line_id;
UPDATE psp_pre_gen_dist_lines_history
SET adjustment_batch_name = NULL
WHERE pre_gen_dist_line_id = l_orig_line_id;
UPDATE psp_distribution_lines_history
SET adjustment_batch_name = NULL
WHERE distribution_line_id = l_orig_line_id;
errbuf := 'Failed when update history table: no rows found.';
/*2. Update table PSP_EFFORT_REPORTS by setting status_code back to */
/* what it is before the batch is created and flush the */
/* previous_status_code column. */
/*---------------------------------------------------------------------*/
OPEN get_template_id_csr;
UPDATE psp_effort_reports
SET status_code = previous_status_code,
previous_status_code = NULL
WHERE effort_report_id = l_report_id
AND previous_status_code IS NOT NULL;
UPDATE psp_effort_reports
SET status_code = previous_status_code
WHERE effort_report_id = l_report_id;
UPDATE psp_effort_reports
SET previous_status_code = NULL
WHERE effort_report_id = l_report_id;
/*3. Delete the record for the rejected batch from PSP_PAYROLL_CONTROL */
/*---------------------------------------------------------------------*/
DELETE FROM psp_payroll_controls
WHERE batch_name = p_batch_name
AND source_type = 'A';
errbuf := 'Failed when update psp_payroll_control table: no row found.';
/*4. Delete the distribution lines of the rejected batch from table */
/* PSP_ADJUSTMENT_LINES. */
/*---------------------------------------------------------------------*/
DELETE FROM psp_adjustment_lines
WHERE batch_name = p_batch_name;
errbuf := 'Failed when update psp_adjustment_lines: no row found.';
/*5. Delete the record for the rejected batch from table */
/* PSP_ADJUSTMENT_CONTROL_TABLE. */
/*---------------------------------------------------------------------*/
UPDATE psp_adjustment_control_table
SET void = 'Y',
comments = p_comments
WHERE adjustment_batch_name = p_batch_name;
errbuf := 'Failed when update psp_adjustment_control_table: no row found.';
SELECT MAX(ptol.effective_date)
FROM psp_temp_orig_lines ptol
WHERE ptol.acct_group_id = p_acct_group_id
AND ptol.run_id = p_run_id
GROUP BY payroll_control_id,
dr_cr_flag;
SELECT 'x'
FROM psp_organizations_expend_v
WHERE organization_id = p_expenditure_org_id
and trunc(l_effective_date) between date_from and nvl(date_to,trunc(l_effective_date));