[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Update_Enc_lines (errbuf out NOCOPY varchar2,
retcode out NOCOPY varchar2,
p_payroll_id IN Number,
p_enc_line_type IN VARCHAR2,
p_business_group_id IN Number,
p_set_of_books_id IN Number) IS
BEGIN
NULL;
SELECT DISTINCT action_code
FROM psp_enc_controls
WHERE action_code = 'N'
AND payroll_id=p_payroll_id;
/ * Following cursors are added for Enh. Restart Update Enc Proces * /
CURSOR inprogress_count_cur IS
--SELECT count(*) Commented for bug fix 3434626
SELECT DISTINCT action_code
FROM psp_enc_controls
WHERE action_type in ('U','N','Q')
AND action_code IN ('IU' , 'IC') -- Introduced 'IC' check for bug fix 3434626
/ * action code = 'IU' means Failure due to db crash * /
AND payroll_id = p_payroll_id
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
ORDER BY action_code DESC; -- Introduced for bug fix 3434626
SELECT NVL(action_type, p_enc_line_type)
FROM psp_enc_controls
WHERE action_code = 'IC'
AND payroll_id = p_payroll_id
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND rownum = 1; End of comment for bug fix 3434626 ***** /
SELECT processed_flag
FROM psp_enc_changed_assignments peca
WHERE processed_flag IS NOT NULL
AND payroll_id = p_payroll_id
AND ROWNUM = 1;
SELECT count(change_flag)
FROM psp_enc_lines_history pelh,
psp_enc_changed_assignments peca
WHERE pelh.change_flag = 'N'
AND pelh.payroll_id = p_payroll_id
AND rownum = 1
AND pelh.assignment_id = peca.assignment_id
AND peca.payroll_id = p_payroll_id
AND peca.request_id IS NOT NULL
AND ((p_enc_line_type = 'Q' AND peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
OR p_enc_line_type = 'U');
/ * Following Variables are added for Enh. Restart Update Enc Process * /
--l_inprogress_count NUMBER DEFAULT -1; -- Added for Restart, count in-progress control recs Commented for bug fix 3434626
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'PSP_ENC_LINE_TYPES'
AND lookup_code = l_enc_line_type;
SELECT DISTINCT liquidate_request_id
FROM psp_enc_controls
WHERE payroll_id = p_payroll_id
AND action_code = 'IT';
SELECT TO_NUMBER(argument3),
fnd_date.canonical_to_date(fnd_date.date_to_canonical(argument4))
FROM fnd_concurrent_requests
WHERE request_id = l_liquidate_request_id;
SELECT full_name
FROM per_people_f
WHERE person_id = l_person_id
AND l_termination_date BETWEEN effective_start_date and effective_end_date;
/ * Following code is added for Enh.Restart Update Process. * /
OPEN inprogress_count_cur;
fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES',g_error_api_path); --- added this line for 2444657
UPDATE psp_enc_changed_assignments
SET processed_flag = p_enc_line_type
WHERE payroll_id = p_payroll_id
AND p_enc_line_type = 'U'
OR ( p_enc_line_type = 'Q'
AND change_type IN ('AS', 'LS', 'ET', 'QU'));
/ * commented following proc Restart Update Enc process * /
/ * clean_up_when_error; * /
END IF; / * create_inprogress_count ,Enh. Restart Update Encumbrance Proecss * /
/ * commented following proc Restart Update Enc process * /
/ * clean_up_when_error; * /
/ * commented following line for update Restart * /
/ * clean_up_when_error; * /
g_error_api_path := 'UPDATE_ENC_LINES:'||g_error_api_path;
fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES',g_error_api_path || sqlerrm);
g_error_api_path := 'UPDATE_ENC_LINES:'||g_error_api_path||' UNEXPECTED ERROR';
fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES',g_error_api_path || sqlerrm);
end update_enc_lines;
Redesign-Pre Process Bug #2259310.Introdced Bulk Update For
1. Marking chage_flag ='U'for unchanged lines in psp_enc_lines_history
2. Marking change_flag ='N' for unmodfied lines in psp_enc_lines_history,
which are having same summary_line_id as modified lines.
Introduced Bulk Delete to delete newly created lines in psp_enc_lines
where the corresponding history lines are flagged as unmodified
i.e change_flag ='U'.
lveerubh 07-May-2002 Introduced Deletion of controls when there are no corresponding lines present-Bug 2359599
***************************************************************************************************** /
Procedure verify_changes(p_payroll_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_enc_line_type IN VARCHAR2,
l_retcode OUT NOCOPY VARCHAR2) IS
CURSOR new_control_recs IS
SELECT enc_control_id,
time_period_id
FROM psp_enc_controls
WHERE payroll_id = p_payroll_id
AND action_code = 'IC';
SELECT distinct pel.time_period_id,
pel.encumbrance_date,
pel.dr_cr_flag,
pel.encumbrance_amount,
pel.gl_project_flag,
NVL(pel.schedule_line_id,-99),
NVL(pel.org_schedule_id, -99),
NVL(pel.default_org_account_id, -99),
NVL(pel.suspense_org_account_id, -99),
NVL(pel.element_account_id, -99),
NVL(project_id, -99),
NVL(pel.task_id, -99),
NVL(pel.award_id, -99),
NVL(pel.expenditure_type, '-99'),
NVL(pel.expenditure_organization_id, -99),
NVL(pel.gl_code_combination_id, -99),
pel.assignment_id,
NVL(pel.attribute1,-99),
NVL(pel.attribute2,-99),
NVL(pel.attribute3,-99),
NVL(pel.attribute4,-99),
NVL(pel.attribute5,-99),
NVL(pel.attribute6,-99),
NVL(pel.attribute7,-99),
NVL(pel.attribute8,-99),
NVL(pel.attribute9,-99),
NVL(pel.attribute10,-99)
FROM psp_enc_lines pel
WHERE enc_control_id in (Select enc_control_id
FROM psp_enc_controls pec
WHERE payroll_id = p_payroll_id
AND pec.enc_control_id = pel.enc_control_id
AND action_code = 'IC');
SELECT distinct pelh.time_period_id ,
pelh.encumbrance_date ,
pelh.dr_cr_flag,
pelh.encumbrance_amount ,
pelh.gl_project_flag,
NVL(pelh.schedule_line_id,-99) ,
NVL(pelh.org_schedule_id, -99) ,
NVL(pelh.default_org_account_id, -99),
NVL(pelh.suspense_org_account_id, -99),
NVL(pelh.element_account_id, -99) ,
NVL(pelh.project_id, -99),
NVL(pelh.task_id, -99) ,
NVL(pelh.award_id, -99),
NVL(pelh.expenditure_type, '-99') ,
NVL(pelh.expenditure_organization_id, -99) ,
NVL(pelh.gl_code_combination_id, -99),
pelh.assignment_id,
NVL(pelh.attribute1,-99),
NVL(pelh.attribute2,-99),
NVL(pelh.attribute3,-99),
NVL(pelh.attribute4,-99),
NVL(pelh.attribute5,-99),
NVL(pelh.attribute6,-99),
NVL(pelh.attribute7,-99),
NVL(pelh.attribute8,-99),
NVL(pelh.attribute9,-99),
NVL(pelh.attribute10,-99)
FROM psp_enc_lines_history pelh
where pelh.change_flag = 'U'
AND payroll_id = p_payroll_id;
/ * Following BULK update identifies all newly lines that are unchanged from the
earlier line created in history. * /
/ * Commenting for Bug 3821553
IF (l_grouping_option = 'N') THEN -- Introduced IF for bug fix 2908859
FORALL i IN 1 .. l_enc_control_id_tl.COUNT
UPDATE psp_enc_lines_history pelh
SET change_flag='U'
WHERE pelh.assignment_id IN (SELECT peca.assignment_id
FROM psp_enc_changed_assignments peca
WHERE peca.payroll_id = p_payroll_id
-- AND peca.request_id = g_request_id) commented for bug 2330057
AND peca.request_id IS NOT NULL)
AND time_period_id = l_time_period_id_tl(i)
AND change_flag = 'N'
AND EXISTS (SELECT 1
FROM psp_enc_lines pel
WHERE pel.enc_control_id = l_enc_control_id_tl(i)
AND pel.time_period_id = l_time_period_id_tl(i)
AND pel.change_flag = 'N'
AND pelh.encumbrance_date = pel.encumbrance_date
AND pelh.dr_cr_flag = pel.dr_cr_flag
AND pelh.encumbrance_amount = pel.encumbrance_amount
AND pelh.gl_project_flag = pel.gl_project_flag
AND NVL(pelh.schedule_line_id,-99) = NVL(pel.schedule_line_id,-99)
AND NVL(pelh.org_schedule_id, -99) = NVL(pel.org_schedule_id, -99)
AND NVL(pelh.default_org_account_id, -99) = NVL(pel.default_org_account_id, -99)
AND NVL(pelh.suspense_org_account_id, -99) = NVL(pel.suspense_org_account_id, -99)
AND NVL(pelh.element_account_id, -99) = NVL(pel.element_account_id, -99)
AND NVL(pelh.project_id, -99) = NVL(project_id, -99)
AND NVL(pelh.task_id, -99) = NVL(pel.task_id, -99)
AND NVL(pelh.award_id, -99) = NVL(pel.award_id, -99)
AND NVL(pelh.expenditure_type, '-99') = NVL(pel.expenditure_type, '-99')
AND NVL(pelh.expenditure_organization_id, -99) = NVL(pel.expenditure_organization_id, -99)
AND NVL(pelh.gl_code_combination_id, -99) = NVL(pel.gl_code_combination_id, -99)
AND pelh.assignment_id = pel.assignment_id); ----added for 3230387
UPDATE psp_enc_lines_history pelh
SET change_flag='U'
WHERE pelh.assignment_id IN (SELECT peca.assignment_id
FROM psp_enc_changed_assignments peca
WHERE peca.payroll_id = p_payroll_id
AND peca.request_id IS NOT NULL)
AND time_period_id = l_time_period_id_tl(i)
AND change_flag = 'N'
AND EXISTS (SELECT 1
FROM psp_enc_lines pel
WHERE pel.enc_control_id = l_enc_control_id_tl(i)
AND pel.time_period_id = l_time_period_id_tl(i)
AND pel.change_flag = 'N'
AND pelh.encumbrance_date = pel.encumbrance_date
AND pelh.dr_cr_flag = pel.dr_cr_flag
AND pelh.encumbrance_amount = pel.encumbrance_amount
AND pelh.gl_project_flag = pel.gl_project_flag
AND NVL(pelh.schedule_line_id,-99) = NVL(pel.schedule_line_id,-99)
AND NVL(pelh.org_schedule_id, -99) = NVL(pel.org_schedule_id, -99)
AND NVL(pelh.default_org_account_id, -99) = NVL(pel.default_org_account_id, -99)
AND NVL(pelh.suspense_org_account_id, -99) = NVL(pel.suspense_org_account_id, -99)
AND NVL(pelh.element_account_id, -99) = NVL(pel.element_account_id, -99)
AND NVL(pelh.project_id, -99) = NVL(project_id, -99)
AND NVL(pelh.task_id, -99) = NVL(pel.task_id, -99)
AND NVL(pelh.award_id, -99) = NVL(pel.award_id, -99)
AND NVL(pelh.expenditure_type, '-99') = NVL(pel.expenditure_type, '-99')
AND NVL(pelh.expenditure_organization_id, -99) = NVL(pel.expenditure_organization_id, -99)
AND NVL(pelh.gl_code_combination_id, -99) = NVL(pel.gl_code_combination_id, -99)
AND NVL(pelh.attribute_category, 'NULL') = NVL(pel.attribute_category, 'NULL')
AND NVL(pelh.attribute1, 'NULL') = NVL(pel.attribute1, 'NULL')
AND NVL(pelh.attribute2, 'NULL') = NVL(pel.attribute2, 'NULL')
AND NVL(pelh.attribute3, 'NULL') = NVL(pel.attribute3, 'NULL')
AND NVL(pelh.attribute4, 'NULL') = NVL(pel.attribute4, 'NULL')
AND NVL(pelh.attribute5, 'NULL') = NVL(pel.attribute5, 'NULL')
AND NVL(pelh.attribute6, 'NULL') = NVL(pel.attribute6, 'NULL')
AND NVL(pelh.attribute7, 'NULL') = NVL(pel.attribute7, 'NULL')
AND NVL(pelh.attribute8, 'NULL') = NVL(pel.attribute8, 'NULL')
AND NVL(pelh.attribute9, 'NULL') = NVL(pel.attribute9, 'NULL')
AND NVL(pelh.attribute10, 'NULL') = NVL(pel.attribute10, 'NULL')
AND pelh.assignment_id = pel.assignment_id);
UPDATE psp_enc_lines_history pelh
SET change_flag='U'
WHERE time_period_id = l_time_period_id_tl(i)
AND change_flag = 'N'
AND pelh.encumbrance_date = l_encumbrance_date_tl(I)
AND pelh.dr_cr_flag = l_dr_cr_flag_tl(I)
AND pelh.encumbrance_amount = l_encumbrance_amount_tl(I)
AND pelh.gl_project_flag = l_gl_project_flag_tl(I)
AND NVL(pelh.schedule_line_id,-99) = l_schedule_line_id_tl(I)
AND NVL(pelh.org_schedule_id, -99) = l_org_schedule_id_tl(I)
AND NVL(pelh.default_org_account_id, -99) = l_default_org_account_id_tl(I)
AND NVL(pelh.suspense_org_account_id, -99) = l_suspense_org_account_id_tl(I)
AND NVL(pelh.element_account_id, -99) = l_element_account_id_tl(I)
AND NVL(pelh.project_id, -99) = l_project_id_tl(I)
AND NVL(pelh.task_id, -99) = l_task_id_tl(i)
AND NVL(pelh.award_id, -99) = l_award_id_tl(i)
AND NVL(pelh.expenditure_type, '-99') = l_expenditure_type_tl(I)
AND NVL(pelh.expenditure_organization_id, -99) = l_exp_organization_id_tl(i)
AND NVL(pelh.gl_code_combination_id, -99) = l_gl_code_combination_id_tl(i)
AND pelh.assignment_id = l_assignment_id_tl(i);
UPDATE psp_enc_lines_history pelh
SET change_flag='U'
WHERE time_period_id = l_time_period_id_tl(i)
AND change_flag = 'N'
AND pelh.encumbrance_date = l_encumbrance_date_tl(I)
AND pelh.dr_cr_flag = l_dr_cr_flag_tl(I)
AND pelh.encumbrance_amount = l_encumbrance_amount_tl(I)
AND pelh.gl_project_flag = l_gl_project_flag_tl(I)
AND NVL(pelh.schedule_line_id,-99) = l_schedule_line_id_tl(I)
AND NVL(pelh.org_schedule_id, -99) = l_org_schedule_id_tl(I)
AND NVL(pelh.default_org_account_id, -99) = l_default_org_account_id_tl(I)
AND NVL(pelh.suspense_org_account_id, -99) = l_suspense_org_account_id_tl(I)
AND NVL(pelh.element_account_id, -99) = l_element_account_id_tl(I)
AND NVL(pelh.project_id, -99) = l_project_id_tl(I)
AND NVL(pelh.task_id, -99) = l_task_id_tl(i)
AND NVL(pelh.award_id, -99) = l_award_id_tl(i)
AND NVL(pelh.expenditure_type, '-99') = l_expenditure_type_tl(I)
AND NVL(pelh.expenditure_organization_id, -99) = l_exp_organization_id_tl(i)
AND NVL(pelh.gl_code_combination_id, -99) = l_gl_code_combination_id_tl(i)
AND pelh.assignment_id = l_assignment_id_tl(i)
-- removed nvl on rhs and changed NULL to -99 on lhs for 4072324
AND NVL(pelh.attribute1, '-99') = l_attribute1_tl(i)
AND NVL(pelh.attribute2, '-99') = l_attribute2_tl(i)
AND NVL(pelh.attribute3, '-99') = l_attribute3_tl(i)
AND NVL(pelh.attribute4, '-99') = l_attribute4_tl(i)
AND NVL(pelh.attribute5, '-99') = l_attribute5_tl(i)
AND NVL(pelh.attribute6, '-99') = l_attribute6_tl(i)
AND NVL(pelh.attribute7, '-99') = l_attribute7_tl(i)
AND NVL(pelh.attribute8, '-99') = l_attribute8_tl(i)
AND NVL(pelh.attribute9, '-99') = l_attribute9_tl(i)
AND NVL(pelh.attribute10, '-99') = l_attribute10_tl(i);
UPDATE psp_enc_lines_history pelh
SET change_flag='N'
WHERE enc_summary_line_id in (SELECT enc_summary_line_id
FROM psp_enc_lines_history
WHERE change_flag = 'N'
AND time_period_id = l_time_period_id_tl(i))
AND change_flag='U'
AND time_period_id=l_time_period_id_tl(i);
l_time_period_id_tl.delete;
l_encumbrance_date_tl.delete;
l_dr_cr_flag_tl.delete;
l_encumbrance_amount_tl.delete;
l_gl_project_flag_tl.delete;
l_schedule_line_id_tl.delete;
l_org_schedule_id_tl.delete;
l_default_org_account_id_tl.delete;
l_suspense_org_account_id_tl.delete;
l_element_account_id_tl.delete;
l_project_id_tl.delete;
l_task_id_tl.delete;
l_award_id_tl.delete;
l_expenditure_type_tl.delete;
l_exp_organization_id_tl.delete;
l_gl_code_combination_id_tl.delete;
l_assignment_id_tl.delete;
l_attribute1_tl.delete;
l_attribute2_tl.delete;
l_attribute3_tl.delete;
l_attribute4_tl.delete;
l_attribute5_tl.delete;
l_attribute6_tl.delete;
l_attribute7_tl.delete;
l_attribute8_tl.delete;
l_attribute9_tl.delete;
l_attribute10_tl.delete;
/ * Delete all those duplicate lines in psp_enc_lines that need not be summarized. * /
/ * Commenting the code for Bug 3821553
IF (l_grouping_option = 'N') THEN -- Introduced IF for bug fix 2908859
FORALL i IN 1 .. l_enc_control_id_tl.COUNT
DELETE psp_enc_lines pel
WHERE time_period_id = l_time_period_id_tl(i)
AND change_flag = 'N'
AND EXISTS (SELECT 1
FROM psp_enc_lines_history pelh
-- WHERE pelh.enc_control_id = l_enc_control_id_tl(i)
WHERE pelh.time_period_id = l_time_period_id_tl(i)
AND pelh.change_flag = 'U'
AND pelh.encumbrance_date = pel.encumbrance_date
AND pelh.dr_cr_flag = pel.dr_cr_flag
AND pelh.encumbrance_amount = pel.encumbrance_amount
AND pelh.gl_project_flag = pel.gl_project_flag
AND NVL(pelh.schedule_line_id,-99) = NVL(pel.schedule_line_id,-99)
AND NVL(pelh.org_schedule_id, -99) = NVL(pel.org_schedule_id, -99)
AND NVL(pelh.default_org_account_id, -99) = NVL(pel.default_org_account_id, -99)
AND NVL(pelh.suspense_org_account_id, -99) = NVL(pel.suspense_org_account_id, -99)
AND NVL(pelh.element_account_id, -99) = NVL(pel.element_account_id, -99)
AND NVL(pelh.project_id, -99) = NVL(project_id, -99)
AND NVL(pelh.task_id, -99) = NVL(pel.task_id, -99)
AND NVL(pelh.award_id, -99) = NVL(pel.award_id, -99)
AND NVL(pelh.expenditure_type, '-99') = NVL(pel.expenditure_type, '-99')
AND NVL(pelh.expenditure_organization_id, -99) = NVL(pel.expenditure_organization_id, -99)
AND NVL(pelh.gl_code_combination_id, -99) = NVL(pel.gl_code_combination_id, -99)
AND pel.assignment_id = pelh.assignment_id); --- 3230387
DELETE psp_enc_lines pel
WHERE time_period_id = l_time_period_id_tl(i)
AND change_flag = 'N'
AND EXISTS (SELECT 1
FROM psp_enc_lines_history pelh
WHERE pelh.time_period_id = l_time_period_id_tl(i)
AND pelh.change_flag = 'U'
AND pelh.encumbrance_date = pel.encumbrance_date
AND pelh.dr_cr_flag = pel.dr_cr_flag
AND pelh.encumbrance_amount = pel.encumbrance_amount
AND pelh.gl_project_flag = pel.gl_project_flag
AND NVL(pelh.schedule_line_id,-99) = NVL(pel.schedule_line_id,-99)
AND NVL(pelh.org_schedule_id, -99) = NVL(pel.org_schedule_id, -99)
AND NVL(pelh.default_org_account_id, -99) = NVL(pel.default_org_account_id, -99)
AND NVL(pelh.suspense_org_account_id, -99) = NVL(pel.suspense_org_account_id, -99)
AND NVL(pelh.element_account_id, -99) = NVL(pel.element_account_id, -99)
AND NVL(pelh.project_id, -99) = NVL(project_id, -99)
AND NVL(pelh.task_id, -99) = NVL(pel.task_id, -99)
AND NVL(pelh.award_id, -99) = NVL(pel.award_id, -99)
AND NVL(pelh.expenditure_type, '-99') = NVL(pel.expenditure_type, '-99')
AND NVL(pelh.expenditure_organization_id, -99) = NVL(pel.expenditure_organization_id, -99)
AND NVL(pelh.gl_code_combination_id, -99) = NVL(pel.gl_code_combination_id, -99)
AND NVL(pelh.attribute_category, 'NULL') = NVL(pel.attribute_category, 'NULL')
AND NVL(pelh.attribute1, 'NULL') = NVL(pel.attribute1, 'NULL')
AND NVL(pelh.attribute2, 'NULL') = NVL(pel.attribute2, 'NULL')
AND NVL(pelh.attribute3, 'NULL') = NVL(pel.attribute3, 'NULL')
AND NVL(pelh.attribute4, 'NULL') = NVL(pel.attribute4, 'NULL')
AND NVL(pelh.attribute5, 'NULL') = NVL(pel.attribute5, 'NULL')
AND NVL(pelh.attribute6, 'NULL') = NVL(pel.attribute6, 'NULL')
AND NVL(pelh.attribute7, 'NULL') = NVL(pel.attribute7, 'NULL')
AND NVL(pelh.attribute8, 'NULL') = NVL(pel.attribute8, 'NULL')
AND NVL(pelh.attribute9, 'NULL') = NVL(pel.attribute9, 'NULL')
AND NVL(pelh.attribute10, 'NULL') = NVL(pel.attribute10, 'NULL')
AND pel.assignment_id = pelh.assignment_id);
DELETE psp_enc_lines pel
WHERE time_period_id = l_time_period_id_tl(i)
AND pel.change_flag = 'N'
AND pel.encumbrance_date = l_encumbrance_date_tl(i)
AND pel.dr_cr_flag = l_dr_cr_flag_tl(i)
AND pel.encumbrance_amount = l_encumbrance_amount_tl(i)
AND pel.gl_project_flag = l_gl_project_flag_tl(i)
AND NVL(pel.schedule_line_id,-99) = l_schedule_line_id_tl(i)
AND NVL(pel.org_schedule_id, -99) = l_org_schedule_id_tl(i)
AND NVL(pel.default_org_account_id, -99) = l_default_org_account_id_tl(i)
AND NVL(pel.suspense_org_account_id, -99) = l_suspense_org_account_id_tl(i)
AND NVL(pel.element_account_id, -99) = l_element_account_id_tl(i)
AND NVL(pel.project_id, -99) = l_project_id_tl(I)
AND NVL(pel.task_id, -99) = l_task_id_tl(i)
AND NVL(pel.award_id, -99) = l_award_id_tl(i)
AND NVL(pel.expenditure_type, '-99') = l_expenditure_type_tl(i)
AND NVL(pel.expenditure_organization_id, -99) = l_exp_organization_id_tl(i)
AND NVL(pel.gl_code_combination_id, -99) = l_gl_code_combination_id_tl(i)
AND pel.assignment_id = l_assignment_id_tl(i);
DELETE psp_enc_lines pel
WHERE time_period_id = l_time_period_id_tl(i)
AND pel.change_flag = 'N'
AND pel.encumbrance_date = l_encumbrance_date_tl(i)
AND pel.dr_cr_flag = l_dr_cr_flag_tl(i)
AND pel.encumbrance_amount = l_encumbrance_amount_tl(i)
AND pel.gl_project_flag = l_gl_project_flag_tl(i)
AND NVL(pel.schedule_line_id,-99) = l_schedule_line_id_tl(i)
AND NVL(pel.org_schedule_id, -99) = l_org_schedule_id_tl(i)
AND NVL(pel.default_org_account_id, -99) = l_default_org_account_id_tl(i)
AND NVL(pel.suspense_org_account_id, -99) = l_suspense_org_account_id_tl(i)
AND NVL(pel.element_account_id, -99) = l_element_account_id_tl(i)
AND NVL(pel.project_id, -99) = l_project_id_tl(I)
AND NVL(pel.task_id, -99) = l_task_id_tl(i)
AND NVL(pel.award_id, -99) = l_award_id_tl(i)
AND NVL(pel.expenditure_type, '-99') = l_expenditure_type_tl(i)
AND NVL(pel.expenditure_organization_id, -99) = l_exp_organization_id_tl(i)
AND NVL(pel.gl_code_combination_id, -99) = l_gl_code_combination_id_tl(i)
AND pel.assignment_id = l_assignment_id_tl(i)
-- removed nvl on rhs, not necessary.. for 4072324
AND NVL(pel.attribute1, '-99') = l_attribute1_tl(i)
AND NVL(pel.attribute2, '-99') = l_attribute2_tl(i)
AND NVL(pel.attribute3, '-99') = l_attribute3_tl(i)
AND NVL(pel.attribute4, '-99') = l_attribute4_tl(i)
AND NVL(pel.attribute5, '-99') = l_attribute5_tl(i)
AND NVL(pel.attribute6, '-99') = l_attribute6_tl(i)
AND NVL(pel.attribute7, '-99') = l_attribute7_tl(i)
AND NVL(pel.attribute8, '-99') = l_attribute8_tl(i)
AND NVL(pel.attribute9, '-99') = l_attribute9_tl(i)
AND NVL(pel.attribute10, '-99') = l_attribute10_tl(i);
l_time_period_id_tl.delete;
l_encumbrance_date_tl.delete;
l_dr_cr_flag_tl.delete;
l_encumbrance_amount_tl.delete;
l_gl_project_flag_tl.delete;
l_schedule_line_id_tl.delete;
l_org_schedule_id_tl.delete;
l_default_org_account_id_tl.delete;
l_suspense_org_account_id_tl.delete;
l_element_account_id_tl.delete;
l_project_id_tl.delete;
l_task_id_tl.delete;
l_award_id_tl.delete;
l_expenditure_type_tl.delete;
l_exp_organization_id_tl.delete;
l_gl_code_combination_id_tl.delete;
l_assignment_id_tl.delete;
l_attribute1_tl.delete;
l_attribute2_tl.delete;
l_attribute3_tl.delete;
l_attribute4_tl.delete;
l_attribute5_tl.delete;
l_attribute6_tl.delete;
l_attribute7_tl.delete;
l_attribute8_tl.delete;
l_attribute9_tl.delete;
l_attribute10_tl.delete;
DELETE FROM psp_enc_controls pec
WHERE pec.action_type IN ('U','Q')
AND pec.action_code = 'IC'
AND pec.payroll_id = p_payroll_id
AND NOT EXISTS ( SELECT 1
FROM psp_enc_lines pel
WHERE pel.enc_control_id = pec.enc_control_id);
fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES','VERIFY_CHANGES');
This procedure has been introduced for the Bug 2110930 -Quick Update Encumbrance Enhancement.
The procedure shall be invoked in Q (quick update) or U (update) mode to move the processed
assignments to history
Know limitations, enhancements or remarks
Change History
Who When What
ddubey 21-Dec-01 Created
ddubey 07-Mar-02 Re Engineered the procedure for Enh. Encumbrance Re design
Pre process,Bug #2259310.
************************************************************************************************* /
PROCEDURE move_qkupd_rec_to_hist( p_payroll_id IN NUMBER,
p_enc_line_type 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 big fix 2324917
Cursor to dislay no of assignments processed by update
CURSOR get_no_asg_to_move IS
SELECT COUNT(DISTINCT peca.assignment_id)
FROM psp_enc_changed_assignments peca
WHERE peca.request_id = g_request_id;
/ * Moving records to psp_enc_changed_asg_history table .Also inserting concurrent request_id
into history for debugging purpose * /
INSERT INTO psp_enc_changed_asg_history
(request_id, assignment_id, payroll_id, change_type,processing_module, created_by
,creation_date, processed_flag, reference_id, action_type)
SELECT g_request_id, peca.assignment_id, peca.payroll_id, peca.change_type,
p_enc_line_type, l_global_user_id, SYSDATE, NULL, NVL(peca.reference_id, 0),
NVL(peca.action_type, p_enc_line_type)
FROM psp_enc_changed_assignments peca
-- WHERE peca.request_id = g_request_id; commented as a part of bug 2330057
DELETE psp_enc_changed_assignments peca
-- WHERE peca.request_id =g_request_id; commented as a part of bug 2330057
/ * Displaying no of records moved to history in quick update mode * /
fnd_message.set_name('PSP','PSP_ENC_NUM_ASG');
fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES','MOVE_QKUPD_REC_TO_HIST');
The following procedure is added for Enh. Resstart Update Encumbrance process
Created By : ddubey
Date Created By : 16-Jan-01
Change History
Who When What
ddubey 16-jan-2001 Created
**************************************************************** /
procedure cleanup_on_success ( p_enc_line_type in varchar2,
p_payroll_id in number,
p_business_group_id in number,
p_set_of_books_id in number,
p_invalid_suspense in Varchar2,
p_return_status out NOCOPY varchar2) is
/ ***************************************************************************
For Bug 2359599 : Controls are deleted in the Verify changes procedure
CURSOR pending_enc_lines_cur Is
SELECT count(*)
FROM psp_enc_lines
WHERE payroll_id = p_payroll_id
AND rownum = 1;
-- Restart Update Enc related change.
/ ***************************************************************************
For Bug 2359599 : Controls are deleted in the Verify changes procedure
OPEN pending_enc_lines_cur;
DELETE FROM psp_Enc_controls
WHERE action_type = p_enc_line_type
AND payroll_id = p_payroll_id
AND action_code='IC';
--Modified the Update statement to include the assignment_id check for Bug 2345813
UPDATE psp_enc_lines_history pelh
SET pelh.change_flag='L'
WHERE pelh.change_flag='N'
AND pelh.payroll_id = p_payroll_id
-- AND EXISTS (SELECT 1 Commented for Bug 3821553
and pelh.assignment_id in (SELECT peca.assignment_id -- Introduced for bug 3821553
FROM psp_enc_changed_assignments peca
WHERE peca.payroll_id = p_payroll_id
-- AND pelh.assignment_id = peca.assignment_id Commented for Bug 3821553
AND peca.request_id IS NOT NULL
AND ((p_enc_line_type = 'Q'
AND peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
OR p_enc_line_type = 'U'));
UPDATE psp_enc_lines_history
SET change_flag = 'N'
WHERE change_flag = 'U'
AND payroll_id = p_payroll_id;
/ * New code added for restart update process * /
UPDATE psp_enc_controls
SET action_code = 'N'
WHERE action_code = 'IC'
AND payroll_id = p_payroll_id
AND set_of_books_id = p_set_of_books_id
AND business_group_id = p_business_group_id;
fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES',g_error_api_path);
SELECT LINE.enc_control_id,
count(decode(LINE.dr_cr_flag, 'D', 'x',null)) number_of_dr,
count(decode(LINE.dr_cr_flag, 'C', 'x', null)) number_of_cr,
sum(decode(LINE.dr_cr_flag,'D',LINE.encumbrance_amount,0)) total_dr_amount ,
sum(decode(LINE.dr_cr_flag,'C',LINE.encumbrance_amount,0)) total_cr_amount,
sum(decode(LINE.dr_cr_flag,'D',decode(LINE.gl_project_flag,'G',LINE.encumbrance_amount,0),0)) gl_dr_amount,
sum(decode(LINE.dr_cr_flag,'C',decode(LINE.gl_project_flag,'G',LINE.encumbrance_amount,0),0)) gl_cr_amount,
sum(decode(LINE.dr_cr_flag,'D',decode(LINE.gl_project_flag,'P',LINE.encumbrance_amount,0),0)) ogm_dr_amount,
sum(decode(LINE.dr_cr_flag,'C',decode(LINE.gl_project_flag,'P',LINE.encumbrance_amount,0),0)) ogm_cr_amount
FROM psp_enc_lines LINE
WHERE LINE.assignment_id in (SELECT assignment_id FROM psp_enc_changed_assignments peca
WHERE peca.payroll_id=p_payroll_id
AND peca.request_id IS NOT NULL
AND (p_action_type = 'U' or peca.change_type IN ('LS', 'ET', 'AS', 'QU')))
AND LINE.enc_control_id in (SELECT CTRL2.enc_control_id FROM psp_enc_controls CTRL2
WHERE CTRL2.action_code = 'IC' and CTRL2.payroll_id = p_payroll_id)
GROUP BY LINE.enc_control_id;
UPDATE psp_enc_lines_history pelh
SET pelh.change_flag='L'
WHERE pelh.change_flag='N'
AND pelh.payroll_id = p_payroll_id
AND pelh.enc_summary_line_id in
(select superceded_line_id
from psp_enc_summary_lines
where status_code = 'L'
and ((gms_batch_name is not null and p_gms_batch_name is not null and gms_batch_name = p_gms_batch_name)
or (group_id is not null and p_accepted_group_id is not null and group_id = p_accepted_group_id))
and enc_control_id in
(select enc_control_id
from psp_enc_controls
where run_id = p_run_id
and action_code in ('P','L')));
UPDATE psp_enc_lines_history
SET change_flag = 'N'
WHERE change_flag = 'U'
AND payroll_id = p_payroll_id;
INSERT INTO psp_enc_changed_asg_history
(request_id, assignment_id, payroll_id, change_type,processing_module, created_by
,creation_date, processed_flag, reference_id, action_type)
SELECT g_request_id, peca.assignment_id, peca.payroll_id, peca.change_type,
p_action_type, l_global_user_id, SYSDATE, NULL, NVL(peca.reference_id, 0),
NVL(peca.action_type, p_action_type)
FROM psp_enc_changed_assignments peca
WHERE payroll_id =p_payroll_id
AND peca.request_id IS NOT NULL
AND ((p_action_type = 'Q' AND peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
OR p_action_type = 'U')
AND peca.assignment_id NOT IN
( select distinct assignment_id
from psp_enc_summary_lines
where status_code = 'R'
and ((gms_batch_name is not null and p_gms_batch_name is not null and gms_batch_name = p_gms_batch_name)
or (group_id is not null and p_rejected_group_id is not null and group_id = p_rejected_group_id))
and enc_control_id in
(select enc_control_id
from psp_enc_controls
where run_id = p_run_id
and action_code in ('P','L')));
DELETE psp_enc_changed_assignments peca
WHERE peca.payroll_id=p_payroll_id
AND peca.request_id IS NOT NULL
AND ((p_action_type = 'Q' AND peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
OR p_action_type = 'U')
AND peca.assignment_id NOT IN
(select distinct assignment_id
from psp_enc_summary_lines
where status_code = 'R'
and ((gms_batch_name is not null and p_gms_batch_name is not null and gms_batch_name = p_gms_batch_name)
or (group_id is not null and p_rejected_group_id is not null and p_rejected_group_id = group_id))
and enc_control_id in
(select enc_control_id
from psp_enc_controls
where run_id = p_run_id
and action_code in ('P','L'))) ;
update psp_enc_controls
set number_of_dr= number_of_dr - control_rec.number_of_dr,
number_of_cr = number_of_cr - control_rec.number_of_cr,
total_dr_amount= total_dr_amount - control_rec.total_dr_amount,
total_cr_amount= total_cr_amount - control_rec.total_cr_amount,
gl_dr_amount = gl_dr_amount -control_rec.gl_dr_amount,
gl_cr_amount = gl_cr_amount -control_rec.gl_cr_amount,
ogm_dr_amount= ogm_dr_amount - control_rec.ogm_dr_amount,
ogm_cr_amount= ogm_cr_amount - control_rec.ogm_cr_amount
where enc_control_id = control_rec.enc_control_id;
DELETE psp_enc_lines LINE
WHERE LINE.assignment_id in (SELECT assignment_id FROM psp_enc_changed_assignments peca
WHERE peca.payroll_id=p_payroll_id
AND peca.request_id IS NOT NULL
AND (p_action_type = 'U' or peca.change_type IN ('LS', 'ET', 'AS', 'QU')))
AND LINE.enc_control_id in (SELECT CTRL2.enc_control_id
FROM psp_enc_controls CTRL2
WHERE CTRL2.action_code = 'IC' and CTRL2.payroll_id = p_payroll_id);
DELETE psp_enc_controls CTRL
WHERE CTRL.action_code = 'IC'
AND CTRL.payroll_id = p_payroll_id
AND NOT EXISTS ( SELECT 1
FROM psp_enc_lines LINE
WHERE LINE.enc_control_id = CTRL.enc_control_id);
UPDATE psp_enc_controls
SET action_code = 'N'
WHERE action_code = 'IC'
AND payroll_id = p_payroll_id
AND set_of_books_id = p_set_of_books_id
AND business_group_id = p_business_group_id;
fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES','ROLLBACK_REJECTED_ASG');
End of comment of for Creatwe and Update multi-thread *****/
END psp_enc_update_lines;