[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_insert_str VARCHAR2(5000);
/*****Commented the following for Create and Update multi thread enh.
PROCEDURE enc_liq_trans(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_payroll_action_id IN NUMBER,
p_payroll_id IN NUMBER,
p_action_type IN VARCHAR2,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER
) IS
l_return_status VARCHAR2(10);
/ *Introduced g_payroll_id global initializations for Restart Update/Quick Update Encumbrance Lines Enh.* /
g_payroll_id:=p_payroll_id;
g_insert_str: ' || g_insert_str || '
g_dff_grouping_option: ' || g_dff_grouping_option || '
g_request_id: ' || g_request_id || '
g_gl_run: FALSE
g_g_liq_has_failed_transactions: FALSE');
p_action_type, -- Added for Restart Update Enh.
l_return_status
);
--insert_into_psp_stout( 'l_phase '||l_phase_status );
p_action_type, -- Added for Restart Update Enh.
l_return_status
);
--insert_into_psp_stout( 'l_phase '||l_phase_status );
-- insert into psp_stout values( 14 ,l_phase_status );
--insert_into_psp_stout( 'l_phase '||l_phase_status );
-- insert into psp_stout values( 15 ,l_phase_status );
p_action_type, -- Added for Restart Update Enh.
l_return_status
);
--insert_into_psp_stout( 'l_phase '||l_phase_status );
-- insert into psp_stout values( 16 ,l_phase_status );
--insert_into_psp_stout( 'l_phase '||l_phase_status );
-- insert into psp_stout values( 17 ,l_phase_status );
p_action_type, -- Added for Restart Update Enh.
l_return_status
);
--insert_into_psp_stout( 'l_phase '||l_phase_status );
-- insert into psp_stout values( 18 ,l_phase_status );
End of comment for create and update multi thread enh. *****/
-- Introduced the following for Create and Update multi thread enh.
PROCEDURE enc_liq_trans(p_payroll_action_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(10);
SELECT DISTINCT payroll_id
FROM psp_enc_controls
WHERE payroll_action_id = p_payroll_action_id;
SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id = g_bg_id
AND ppa.payroll_id = l_payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id;
SELECT NVL(MAX(time_period_id),0)
FROM per_time_periods
WHERE end_date = l_payroll_run_date
/***** Commented for bug fix 4625734
(SELECT MAX(date_earned)
FROM pay_payroll_actions ppa
WHERE ppa.business_group_id = p_business_group_id
AND payroll_id = pec.payroll_id
AND action_type = 'R'
AND action_status = 'C')
End of comment for bug fix 4625734 *****/
AND payroll_id = l_payroll_id;
SELECT SUM(number_of_dr),
SUM(number_of_cr),
SUM(total_dr_amount),
SUM(total_cr_amount),
SUM(gl_dr_amount),
SUM(gl_cr_amount),
SUM(ogm_dr_amount),
SUM(ogm_cr_amount),
SUM(summ_gl_dr_amount),
SUM(summ_gl_cr_amount),
SUM(summ_ogm_dr_amount),
SUM(summ_ogm_cr_amount),
MIN(time_period_id),
MAX(time_period_id),
COUNT(1),
action_code,
action_type,
NVL(run_id, 0),
gl_phase,
gms_phase,
batch_name
FROM psp_enc_controls pec
WHERE payroll_action_id = p_payroll_action_id
-- WHERE payroll_id = p_payroll_id
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type IN ('N', 'U', 'Q')
AND action_code IN ('P', 'IL', 'IU')
-- AND business_group_id = g_bg_id
-- AND set_of_books_id = g_sob_id
AND time_period_id <= l_max_time_period
GROUP BY action_code,
action_type,
NVL(run_id, 0),
gl_phase,
gms_phase,
batch_name;
SELECT SUM(number_of_dr),
SUM(number_of_cr),
SUM(total_dr_amount),
SUM(total_cr_amount),
SUM(gl_dr_amount),
SUM(gl_cr_amount),
SUM(ogm_dr_amount),
SUM(ogm_cr_amount),
SUM(summ_gl_dr_amount),
SUM(summ_gl_cr_amount),
SUM(summ_ogm_dr_amount),
SUM(summ_ogm_cr_amount),
MIN(time_period_id),
MAX(time_period_id),
COUNT(1),
action_code,
action_type,
NVL(run_id, 0),
gl_phase,
gms_phase,
batch_name
FROM psp_enc_controls pec
WHERE payroll_action_id = p_payroll_action_id
-- WHERE payroll_id = p_payroll_id
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type IN ('N', 'U', 'Q')
AND action_code IN ('P', 'IU', 'IL')
-- AND business_group_id = g_bg_id
-- AND set_of_books_id = g_sob_id
AND time_period_id > l_max_time_period
GROUP BY action_code,
action_type,
NVL(run_id, 0),
gl_phase,
gms_phase,
batch_name;
/***** Commented for Create and Update multi thread enh.
CURSOR enc_control_status_cur3 IS
SELECT SUM(number_of_dr),
SUM(number_of_cr),
SUM(total_dr_amount),
SUM(total_cr_amount),
SUM(gl_dr_amount),
SUM(gl_cr_amount),
SUM(ogm_dr_amount),
SUM(ogm_cr_amount),
SUM(summ_gl_dr_amount),
SUM(summ_gl_cr_amount),
SUM(summ_ogm_dr_amount),
SUM(summ_ogm_cr_amount),
MIN(time_period_id),
MAX(time_period_id),
COUNT(1),
action_code,
action_type,
NVL(run_id, 0),
gl_phase,
gms_phase,
batch_name
FROM psp_enc_controls pec
WHERE payroll_id = p_payroll_id
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type IN ('N', 'U', 'Q')
AND action_code IN ('P', 'IT')
AND business_group_id = g_bg_id
AND set_of_books_id = g_sob_id
AND time_period_id >= g_term_period_id
GROUP BY action_code,
action_type,
NVL(run_id, 0),
gl_phase,
gms_phase,
batch_name;
End of comment for Create and Update multi threa enh. *****/
CURSOR summary_lines_cur IS
SELECT pesl.person_id,
SUBSTR(ppf.full_name, 1, 50) full_name,
ppf.employee_number,
pesl.assignment_id,
paf.assignment_number,
DECODE(pesl.gl_project_flag, 'G', 'GL', 'Project') gl_project_flag,
NVL(TO_CHAR(pesl.group_id), ' '),
NVL(pesl.gms_batch_name, ' '),
DECODE(pesl.dr_cr_flag, 'D', 'Debit', 'Credit') dr_cr_flag,
DECODE(pesl.status_code, 'A', 'Accepted', 'L', 'Liquidated', 'S', 'Superceded', 'N', 'New', 'R', 'Rejected', pesl.status_code) status_code,
SUM(pesl.summary_amount),
COUNT(1)
FROM psp_enc_summary_lines pesl,
psp_enc_controls pec,
per_assignments_f paf,
per_people_f ppf
WHERE pesl.enc_control_id = pec.enc_control_id
AND paf.assignment_id = pesl.assignment_id
AND pesl.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppf.person_id = pesl.person_id
AND pesl.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pec.payroll_action_id = p_payroll_action_id
-- AND pec.run_id = g_run_id
-- AND ( g_person_id IS NULL
-- OR pesl.person_id = g_person_id)
GROUP BY pesl.person_id,
ppf.full_name,
ppf.employee_number,
pesl.assignment_id,
paf.assignment_number,
DECODE(pesl.gl_project_flag, 'G', 'GL', 'Project'),
NVL(TO_CHAR(pesl.group_id), ' '),
NVL(pesl.gms_batch_name, ' '),
DECODE(pesl.dr_cr_flag, 'D', 'Debit', 'Credit'),
DECODE(pesl.status_code, 'A', 'Accepted', 'L', 'Liquidated', 'S', 'Superceded', 'N', 'New', 'R', 'Rejected', pesl.status_code);
SELECT enc_control_id,
payroll_id,
time_period_id
FROM psp_enc_controls pec
WHERE payroll_id = nvl(p_payroll_id, payroll_id)
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type in ('N', 'U', 'Q') -- Included 'Q' for Enh. 2143723
AND action_code in ('P','IL') --- added 'IL' for 2444657
AND business_group_id = g_bg_id
AND set_of_books_id = g_sob_id
AND time_period_id <=p_max_time_period;
SELECT enc_control_id,
payroll_id,
time_period_id
FROM psp_enc_controls pec
WHERE payroll_id = nvl(p_payroll_id, payroll_id)
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type in ('N', 'U', 'Q')
AND action_code in ('P','IL')
AND business_group_id = g_bg_id
AND set_of_books_id = g_sob_id
AND time_period_id >= g_term_period_id; --- g_term_period_id is global var
(select max(time_period_id) from per_time_periods
--for bug fix 1971612 where end_date = (select max(effective_date)
where end_date = (select max(date_earned)
from pay_payroll_actions
where business_group_id = g_bg_id
-- and set_of_books_id = g_sob_id
-- above commented out as there is no set of books column
-- in pay_payroll_actions will go as part of bug 1971612
and payroll_id = p_payroll_id
and action_type = 'R'
and action_status = 'C')
and payroll_id = p_payroll_id);
Includes Restart Update process fix.
AND EXISTS
(SELECT pelh.assignment_id from psp_enc_summary_lines pesl,
psp_Enc_lines_history pelh where
pelh.enc_control_id=pec.enc_control_id and
pelh.enc_summary_line_id=pesl.enc_summary_line_id
AND pesl.enc_control_id=pec.enc_control_id AND
pesl.status_code='A');
End of Enh. fix 2143723 (Includes Restart Update process fix) * /
***** Commented for bug 2259310 **/
/***** Commented for bug fix 4625734
CURSOR enc_control_cur2 (p_max_time_period NUMBER)IS
SELECT enc_control_id,
payroll_id,
time_period_id
FROM psp_enc_controls pec
WHERE payroll_id = nvl(p_payroll_id, payroll_id)
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type in ('N', 'U', 'Q') -- Included 'Q' for Enh. 2143723
AND action_code in ('P','IU') --- added 'IU' for 2444657
AND business_group_id = g_bg_id
AND set_of_books_id = g_sob_id
AND time_period_id > p_max_time_period;
(select max(time_period_id) from per_time_periods
--for bug fix 1971612 where end_date = (select max(effective_date)
where end_date = (select max(date_earned)
from pay_payroll_actions
where business_group_id = g_bg_id
-- and set_of_books_id = g_sob_id
-- above commented out as there is no set of books column
-- in pay_payroll_actions, will go as part of bug 1971612
and payroll_id = p_payroll_id
and action_type = 'R'
and action_status = 'C')
and payroll_id = p_payroll_id);
Includes Restart Update process fix.
AND EXISTS (SELECT pelh.assignment_id from psp_enc_summary_lines pesl,
psp_enc_lines_history pelh WHERE
pelh.enc_control_id=pec.enc_control_id AND
pelh.enc_summary_line_id =pesl.enc_summary_line_id AND
pesl.enc_control_id=pec.enc_control_id AND
pesl.status_code='A' and
pelh.change_flag='N');
End of Enh. fix 2143723 (Includes Restart Update process fix) * /
******************************************** Commented for bug 2259310 */
/***** Commented for big fix 4625734
enc_control_rec1 enc_control_cur1%ROWTYPE;
SELECT psp_st_run_id_s.nextval
INTO g_run_id
FROM dual;
UPDATE psp_enc_controls
SET action_code = 'IL', / * Changed from 'I' for Restart Update/Quick Update Encumbrance Lines * /
run_id = g_run_id
-- gms_phase = null,
-- gl_phase = null commented null for 2444657
WHERE enc_control_id = enc_control_rec1.enc_control_id;
SELECT psp_st_run_id_s.nextval
INTO g_run_id
FROM dual;
UPDATE psp_enc_controls
SET action_code = 'IU',/ * Changed from 'I' for Restart Update/Quick Update Encumbrance Lines * /
run_id = g_run_id
WHERE enc_control_id = enc_control_rec2.enc_control_id;
SELECT psp_st_run_id_s.nextval
INTO g_run_id
FROM dual;
UPDATE psp_enc_controls
SET action_code = 'IL',
run_id = g_run_id
WHERE enc_control_id = enc_control_rec3.enc_control_id;
SELECT psp_st_run_id_s.nextval INTO g_run_id FROM dual;
/**** Commented the following for Create and Update multi threading enh.
IF (g_person_id IS NULL) THEN
OPEN max_period_cur;
End of comment for Create and Update multi threading enh. ****/
OPEN enc_control_status_cur1;
UPDATE psp_enc_controls pec
SET action_code = 'IL',
run_id = g_run_id,
liquidate_request_id = g_request_id
WHERE payroll_id = l_payroll_id
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type in ('N', 'U', 'Q')
AND action_code in ('P','IL', 'IU')
AND payroll_action_id = p_payroll_action_id
AND time_period_id <= l_max_time_period;
UPDATE psp_enc_controls pec
SET action_code = 'IU',
run_id = g_run_id,
liquidate_request_id = g_request_id
WHERE payroll_id = l_payroll_id
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type in ('N', 'U', 'Q')
AND action_code in ('P','IU', 'IL')
AND payroll_action_id = p_payroll_action_id
AND time_period_id > l_max_time_period;
/**** Commented the following for Create and Update multi threading enh.
END IF;
UPDATE psp_enc_controls pec
SET action_code = 'IT',
run_id = g_run_id,
liquidate_request_id = g_request_id
WHERE payroll_id = p_payroll_id
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type in ('N', 'U', 'Q')
AND action_code in ('P','IT')
AND business_group_id = g_bg_id
AND set_of_books_id = g_sob_id
AND time_period_id >= g_term_period_id;
End of comment for Create and Update multi threading enh. ****/
fnd_file.put_line(fnd_file.output, 'Liquidation Batch Processing, Started at: ' ||
fnd_date.date_to_canonical(SYSDATE) || 'Run ID: ' || g_run_id || 'Payroll Action Id: ' || p_payroll_action_id || ' Last payroll run date: ' ||
l_payroll_run_date || 'Encumbrance control record(s) status prior to liquidation processing');
SELECT DISTINCT enc_control_id
--FROM psp_enc_controls
FROM psp_enc_summary_lines
--WHERE payroll_id = p_payroll_id
WHERE payroll_action_id = p_payroll_action_id
--AND run_id = g_run_id
AND superceded_line_id IS NOT NULL
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id;
SELECT SUM(number_of_dr),
SUM(number_of_cr),
SUM(total_dr_amount),
SUM(total_cr_amount),
SUM(gl_dr_amount),
SUM(gl_cr_amount),
SUM(ogm_dr_amount),
SUM(ogm_cr_amount),
SUM(summ_gl_dr_amount),
SUM(summ_gl_cr_amount),
SUM(summ_ogm_dr_amount),
SUM(summ_ogm_cr_amount),
MIN(time_period_id),
MAX(time_period_id),
COUNT(1),
action_code,
action_type,
run_id,
gl_phase,
gms_phase,
batch_name
FROM psp_enc_controls pec
--WHERE payroll_id = p_payroll_id
WHERE payroll_action_id = p_payroll_action_id
AND run_id = g_run_id
AND business_group_id = g_bg_id
AND set_of_books_id = g_sob_id
GROUP BY action_code,
action_type,
run_id,
gl_phase,
gms_phase,
batch_name;
SELECT pesl.person_id,
SUBSTR(ppf.full_name, 1, 50) full_name,
ppf.employee_number,
pesl.assignment_id,
paf.assignment_number,
DECODE(pesl.gl_project_flag, 'G', 'GL', 'Project') gl_project_flag,
NVL(TO_CHAR(pesl.group_id), ' '),
NVL(pesl.gms_batch_name, ' '),
DECODE(pesl.dr_cr_flag, 'D', 'Debit', 'Credit') dr_cr_flag,
DECODE(pesl.status_code, 'A', 'Accepted', 'L', 'Liquidated', 'S', 'Superceded', 'N', 'New', 'R', 'Rejected', pesl.status_code) status_code,
SUM(pesl.summary_amount),
COUNT(1)
FROM psp_enc_summary_lines pesl,
psp_enc_controls pec,
per_assignments_f paf,
per_people_f ppf
WHERE pesl.enc_control_id = pec.enc_control_id
AND paf.assignment_id = pesl.assignment_id
AND pesl.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppf.person_id = pesl.person_id
AND pesl.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pec.run_id = g_run_id
AND ( g_person_id IS NULL
OR pesl.person_id = g_person_id)
GROUP BY pesl.person_id,
ppf.full_name,
ppf.employee_number,
pesl.assignment_id,
paf.assignment_number,
DECODE(pesl.gl_project_flag, 'G', 'GL', 'Project'),
NVL(TO_CHAR(pesl.group_id), ' '),
NVL(pesl.gms_batch_name, ' '),
DECODE(pesl.dr_cr_flag, 'D', 'Debit', 'Credit'),
DECODE(pesl.status_code, 'A', 'Accepted', 'L', 'Liquidated', 'S', 'Superceded', 'N', 'New', 'R', 'Rejected', pesl.status_code);
UPDATE psp_enc_lines_history pelh
SET change_flag = 'U'
WHERE EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE status_code = 'A'
AND pesl.enc_control_id = t_enc_control_id(recno)
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl2
WHERE pesl2.status_code = 'L'
AND pesl2.enc_control_id = t_enc_control_id(recno)
AND pesl2.superceded_line_id = pesl.enc_summary_line_id))
AND pelh.enc_control_id = t_enc_control_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines for superceded lines count:'|| g_bulk_row_count);
UPDATE psp_enc_summary_lines pesl
SET status_code = 'S'
WHERE EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl3
WHERE pesl3.status_code = 'L'
AND pesl3.enc_control_id = t_enc_control_id(recno)
AND pesl3.superceded_line_id = pesl.enc_summary_line_id)
AND pesl.enc_control_id = t_enc_control_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines for superceded lines count'||g_bulk_row_count);
UPDATE psp_enc_summary_lines pesl
SET status_code = 'A'
WHERE status_code = 'S'
AND pesl.enc_summary_line_id IN (SELECT pesl2.superceded_line_id
FROM psp_enc_summary_lines pesl2
WHERE pesl2.status_code = 'N'
AND pesl2.enc_control_id = t_enc_control_id(recno))
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl3
WHERE pesl3.status_code = 'L'
AND pesl3.enc_control_id = t_enc_control_id(recno)
AND pesl3.superceded_line_id = pesl.enc_summary_line_id)
AND pesl.enc_control_id = t_enc_control_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines for lines rejected or not imported into target systems, count: '||g_bulk_row_count);
UPDATE psp_enc_lines_history pelh
SET change_flag = 'L'
WHERE pelh.enc_summary_line_id IN (SELECT pesl.superceded_line_id
FROM psp_enc_summary_lines pesl
WHERE pesl.status_code = 'L'
AND pesl.enc_control_id = t_enc_control_id(recno))
AND pelh.enc_control_id = t_enc_control_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated change_flag to ''L'' in psp_enc_lines_history for lines that are liquidated count: '||g_bulk_row_count);
UPDATE psp_enc_controls
SET action_code = 'L'
WHERE enc_control_id = t_enc_control_id(recno)
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_control_id = t_enc_control_id(recno)
AND status_code IN ('N','R','A'));
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated action_code to ''L'' in psp_enc_controls for control records whose enc summary lines are completely liquidated count '||g_bulk_row_count);
UPDATE psp_enc_controls
SET action_code = 'P'
WHERE enc_control_id = t_enc_control_id(recno)
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_control_id = t_enc_control_id(recno)
AND status_code = 'A');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated action_code to ''P'' in psp_enc_controls for control records whose enc summary lines aren''t completely liquidated count'||g_bulk_row_count);
UPDATE psp_enc_processes
SET process_phase = 'completed',
process_status = 'P'
WHERE payroll_action_id = p_payroll_action_id
AND process_code = 'ST'
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.status_code = 'N');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to completed as no summarize and transfer is required');
UPDATE psp_enc_processes
SET process_phase = 'summarize_transfer'
WHERE payroll_action_id = p_payroll_action_id
AND process_code = 'ST'
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.status_code = 'N'
AND pesl.superceded_line_id IS NULL);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to summarize_transfer as there are new lines to be suimmarized and transferred');
UPDATE psp_enc_processes
SET process_phase = 'liquidate'
WHERE payroll_action_id = p_payroll_action_id
AND process_code = 'ST'
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.status_code = 'N'
AND pesl.superceded_line_id IS NOT NULL);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to liquidate as liquidation process isn''t complete');
/***** Commented for Create and Update multi thread enh.
IF p_action_type in ('U','Q') THEN
IF NOT NVL(g_liq_has_failed_transactions, FALSE) THEN
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_enc_update_lines.cleanup_on_success');
psp_enc_update_lines.cleanup_on_success(p_action_type,
p_payroll_id,
p_business_group_id,
p_set_of_books_id,
'N',
p_return_status);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_enc_update_lines.cleanup_on_success');
psp_enc_update_lines.rollback_rejected_asg (p_payroll_id ,
p_action_type,
g_gms_batch_name,
g_accepted_group_id,
g_rejected_group_id,
g_run_id,
p_business_group_id,
p_set_of_books_id,
p_return_status);
End of comment for Create and Update multi thread *****/
UPDATE psp_enc_process_assignments pepa
SET assignment_status = 'P'
WHERE payroll_action_id = p_payroll_action_id
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.assignment_id = pepa.assignment_id
AND pesl.payroll_id = pepa.payroll_id
AND pesl.status_code = 'N');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_enc_process_assignmnets set assignment_status to P sql%rowcount'||sql%rowcount);
UPDATE psp_enc_process_assignments pepa
SET assignment_status = 'S'
WHERE payroll_action_id = p_payroll_action_id
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.assignment_id = pepa.assignment_id
AND pesl.payroll_id = pepa.payroll_id
AND pesl.status_code = 'N'
AND pesl.superceded_line_id IS NULL);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_enc_process_assignmnets set assignment_status to S sql%rowcount'||sql%rowcount);
UPDATE psp_enc_process_assignments pepa
SET assignment_status = 'L'
WHERE payroll_action_id = p_payroll_action_id
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.assignment_id = pepa.assignment_id
AND pesl.payroll_id = pepa.payroll_id
AND pesl.status_code = 'N'
AND pesl.superceded_line_id IS NOT NULL);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_enc_process_assignmnets set assignment_status to L sql%rowcount'||sql%rowcount);
SELECT enc_control_id,
payroll_id,
time_period_id,
gl_phase,
gms_phase
FROM psp_enc_controls
WHERE payroll_id = nvl(p_payroll_id, payroll_id)
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type in ('N', 'U', 'Q') -- Included 'Q' for Enh. 2143723
AND action_code = DECODE(p_action_type,'U','IU','Q','IU','IL') / * Restart Update/Quick Update Encumbrance Lines Changes * /
AND (run_id = g_run_id or p_mode = 'R') --- bug 2039196: introduced p_mode = 'R'
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id;
SELECT status_code,superceded_line_id
FROM psp_enc_summary_lines
WHERE enc_control_id = p_enc_control_id
AND status_code <> 'A';
SELECT count(*)
FROM psp_enc_summary_lines
WHERE enc_control_id = enc_control_rec.enc_control_id
AND status_code = 'A';
SELECT count(*)
FROM psp_enc_lines
WHERE payroll_id = p_payroll_id
AND rownum = 1;
UPDATE psp_enc_summary_lines
SET status_code = 'R'
WHERE enc_control_id = enc_control_rec.enc_control_id
AND status_code = 'N';
UPDATE psp_Enc_summary_lines S1 set S1.status_code='A' where
S1.enc_summary_line_id=l_sup_enc_summary_line_id and
S1.status_code='S' and not exists
-- added code to ensure there is no L line before reverting S
-- for 2479579
(select 1 from psp_enc_summary_lines S2
where S2.status_code = 'L' and S1.enc_control_id = S2.enc_control_id
and S2.superceded_line_id = l_sup_enc_summary_line_id);
update psp_Enc_lines_history
set change_flag= DECODE(p_action_type, 'Q', 'U', p_action_type)
where enc_summary_line_id=l_sup_enc_summary_line_id
and change_flag='N';
/ * Start Bug#2142865 Added delete to delete rejected records * /
/ * reverted the delete for 2445196
DELETE FROM psp_enc_summary_lines
WHERE enc_control_id = enc_control_rec.enc_control_id
AND status_code = 'R'; * /
/ * Commented for Restart Update/Quick Update Encumbrance Lines Enh.
DELETE FROM psp_enc_summary_lines
WHERE enc_control_id = enc_control_rec.enc_control_id
AND status_code = 'R';
UPDATE psp_enc_controls
SET action_code = 'P',
run_id = NULL
WHERE enc_control_id = enc_control_rec.enc_control_id;
UPDATE psp_enc_controls
SET action_code = 'L',
run_id = NULL
WHERE enc_control_id = enc_control_rec.enc_control_id;
update psp_enc_Controls set action_code='L' where
time_period_id=enc_control_rec.time_period_id and
payroll_id=p_payroll_id;
update psp_enc_controls set action_code='P', run_id=null
where enc_control_id=enc_control_rec.enc_control_id;
SELECT count(*)
into l_line_count
FROM psp_enc_summary_lines
WHERE enc_control_id = enc_control_rec.enc_control_id
and status_code = 'A';
update psp_enc_controls set action_code='L' ---, run_id=null ...commented run_id = null for 3473294
where enc_control_id=enc_control_rec.enc_control_id;
update psp_enc_controls set action_code='P' ---, run_id=null
where enc_control_id=enc_control_rec.enc_control_id;
psp_enc_update_lines.cleanup_on_success(p_action_type,
p_payroll_id,
p_business_group_id,
p_set_of_books_id,
'N', -- Replaced g_invalid_suspense with 'N' for Enh. 2768298
p_return_status);
psp_enc_update_lines.rollback_rejected_asg (p_payroll_id ,
p_action_type,
g_gms_batch_name,
g_accepted_group_id,
g_rejected_group_id,
g_run_id,
p_business_group_id,
p_set_of_books_id,
p_return_status);
/ * Added For Restart Update/Quick Update Encumbrance Lines Enh. * /
p_return_status := fnd_api.g_ret_sts_success;/ * pulled this line from below commit * /
/ * Commented for Restart Update/Quick Update Encumbrance Lines Enh.
and the same code is moved to PSPENUPB
-- the foll. code is moved from PSPENUPB to here as part of Quick Update Enc. Enh. 2143723
-- This fix also includes the Restart Update Enc. Enh. fix
IF p_action_type IN ('Q', 'U') THEN
OPEN pending_enc_lines_cur;
DELETE FROM psp_enc_controls
WHERE action_type = p_action_type
AND payroll_id = p_payroll_id
AND action_code = 'N';
UPDATE psp_enc_lines_history
SET change_flag = 'L'
WHERE change_flag = 'N'
AND payroll_id = p_payroll_id;
UPDATE psp_enc_lines_history
SET change_flag = 'N'
WHERE change_flag = 'U'
AND payroll_id = p_payroll_id;
UPDATE psp_enc_lines_history
SET change_flag = 'N'
WHERE change_flag = p_action_type
AND payroll_id = p_payroll_id;
SELECT pesl.enc_summary_line_id,
pesl.effective_date,
enc_control_id,
time_period_id,
pesl.set_of_books_id,
pesl.gl_code_combination_id,
pesl.summary_amount,
DECODE(pesl.dr_cr_flag, 'C', 'D', 'D', 'C') dr_cr_flag,
pesl.person_id,
pesl.assignment_id,
pesl.gl_project_flag,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute_category, NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND (pec.gl_phase IS NULL or pec.gl_phase = 'TieBack'))
AND pesl.gl_project_flag = 'G'
AND pesl.status_code = 'A'
AND EXISTS (SELECT 1
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.enc_summary_line_id = pesl.enc_summary_line_id);
SELECT pesl.enc_summary_line_id,
pesl.effective_date,
enc_control_id,
time_period_id,
pesl.set_of_books_id,
pesl.gl_code_combination_id,
pesl.summary_amount,
DECODE(pesl.dr_cr_flag, 'C', 'D', 'D', 'C') dr_cr_flag,
pesl.person_id,
pesl.assignment_id,
pesl.gl_project_flag,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute_category, NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND (pec.gl_phase IS NULL or pec.gl_phase = 'TieBack'))
AND pesl.gl_project_flag = 'G'
AND pesl.status_code = 'A'
AND EXISTS (SELECT 1 FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = pesl.assignment_id
AND peca.request_id IS NOT NULL
AND peca.payroll_id =p_payroll_id)
AND EXISTS (SELECT 1
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.enc_summary_line_id = pesl.enc_summary_line_id);
SELECT pesl.enc_summary_line_id,
pesl.effective_date,
enc_control_id,
time_period_id,
pesl.set_of_books_id,
pesl.gl_code_combination_id,
pesl.summary_amount,
DECODE(pesl.dr_cr_flag, 'C', 'D', 'D', 'C') dr_cr_flag,
pesl.person_id,
pesl.assignment_id,
pesl.gl_project_flag,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute_category, NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND (pec.gl_phase IS NULL or pec.gl_phase = 'TieBack'))
AND pesl.gl_project_flag = 'G'
AND pesl.status_code = 'A'
AND EXISTS (SELECT 1 FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = pesl.assignment_id
AND peca.request_id IS NOT NULL
AND peca.payroll_id = p_payroll_id
AND peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
AND EXISTS (SELECT 1
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.enc_summary_line_id = pesl.enc_summary_line_id);
SELECT enc_summary_line_id,
effective_date,
enc_control_id,
time_period_id,
set_of_books_id,
gl_code_combination_id,
summary_amount,
DECODE(pesl.dr_cr_flag, 'C', 'D', 'D', 'C') dr_cr_flag,
person_id,
assignment_id,
gl_project_flag,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute_category, NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
FROM psp_enc_summary_lines pesl
WHERE enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = NVL(p_payroll_id, pec.payroll_id)
AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND (pec.gl_phase IS NULL OR pec.gl_phase = 'TieBack'))
AND gl_project_flag = 'G'
AND status_code = 'A'
AND person_id = g_person_id
AND EXISTS (SELECT 1
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.enc_summary_line_id = pesl.enc_summary_line_id);
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
INSERT INTO psp_enc_summary_lines
(enc_summary_line_id, business_group_id, enc_control_id,
time_period_id, person_id, assignment_id,
effective_date, set_of_books_id, gl_code_combination_id,
summary_amount, dr_cr_flag, status_code,
payroll_id, gl_project_flag, superceded_line_id,
attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8,
attribute9, attribute10, liquidate_request_id,
proposed_termination_date, last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
VALUES (psp_enc_summary_lines_s.NEXTVAL, g_bg_id,
r_liq_lines.enc_control_id(recno), r_liq_lines.time_period_id(recno),
r_liq_lines.person_id(recno), r_liq_lines.assignment_id(recno),
r_liq_lines.effective_date(recno), r_liq_lines.set_of_books_id(recno),
r_liq_lines.gl_code_combination_id(recno), r_liq_lines.summary_amount(recno),
r_liq_lines.dr_cr_flag(recno), 'N',
p_payroll_id, r_liq_lines.gl_project_flag(recno),
r_liq_lines.enc_summary_line_id(recno), r_liq_lines.attribute_category(recno),
r_liq_lines.attribute1(recno), r_liq_lines.attribute2(recno),
r_liq_lines.attribute3(recno), r_liq_lines.attribute4(recno),
r_liq_lines.attribute5(recno), r_liq_lines.attribute6(recno),
r_liq_lines.attribute7(recno), r_liq_lines.attribute8(recno),
r_liq_lines.attribute9(recno), r_liq_lines.attribute10(recno),
g_request_id, g_actual_term_date,
SYSDATE, l_last_updated_by, l_last_update_login, l_last_updated_by, SYSDATE);
UPDATE psp_enc_summary_lines
SET status_code = 'S'
WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');
UPDATE psp_enc_controls
SET gl_phase = 'Summarize'
WHERE enc_control_id = r_liq_lines.enc_control_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase to ''Summarize'' in psp_enc_controls');
SELECT enc_control_id,
payroll_id,
time_period_id
FROM psp_enc_controls
WHERE payroll_id = nvl(p_payroll_id, payroll_id)
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type in ('N', 'U', 'Q') -- Included 'Q' for Enh. 2143723
AND action_code IN ('IL','IU') -- Replaced I with IL and IU for Bug#2142865
AND run_id = g_run_id
AND business_group_id = g_bg_id
AND set_of_books_id = g_sob_id
AND (gl_phase is null or gl_phase = 'TieBack'); --- added this line for 2444657
SELECT DISTINCT pesl.enc_summary_line_id,
pesl.effective_date,
pesl.set_of_books_id,
pesl.gl_code_combination_id,
pesl.summary_amount,
pesl.dr_cr_flag,
pesl.person_id,
pesl.assignment_id,
pesl.gl_project_flag,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', pesl.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_control_id = p_enc_control_id
--- changed pelh to pesl for performance ..3684930
AND pesl.gl_project_flag = 'G'
AND pesl.status_code = 'A'
AND ((p_action_type='L' and g_person_id is null) --- g_person_id null check for 3413373
OR (g_person_id is not null AND peSL.person_id = g_person_id
AND EXISTS (SELECT 1 FROM PSP_ENC_LINES_HISTORY PELH WHERE PELH.ENC_SUMMARY_LINE_ID = PESL.ENC_SUMMARY_LINE_ID
AND PELH.CHANGE_FLAG = 'N')
AND peSL.time_period_id >= g_term_period_id)
OR
(p_action_type IN ('Q', 'U') AND EXISTS
(SELECT 1 FROM PSP_ENC_LINES_HISTORY PELH WHERE PELH.ENC_SUMMARY_LINE_ID = PESL.ENC_SUMMARY_LINE_ID
AND PELH.CHANGE_FLAG = 'N')
AND EXISTS (SELECT 1 FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = pesl.assignment_id
AND peca.request_id IS NOT NULL
AND peca.payroll_id =p_payroll_id
AND ((p_action_type = 'Q' and peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
OR p_action_type = 'U')
)
)
);
(SELECT distinct pelh.assignment_id FROM
psp_enc_lines_history pelh, psp_enc_controls pec
WHERE pec.enc_control_id=p_enc_control_id AND
pelh.time_period_id=pec.time_period_id and pelh.change_flag='N')));
insert_into_enc_sum_lines(
l_enc_summary_line_id,
g_bg_id,
enc_control_rec.enc_control_id,
enc_control_rec.time_period_id,
enc_liq_rec.person_id,
enc_liq_rec.assignment_id,
enc_liq_rec.effective_date,
enc_liq_rec.set_of_books_id,
enc_liq_rec.gl_code_combination_id,
NULL,
NULL,
NULL,
NULL,
NULL,
enc_liq_rec.summary_amount,
enc_liq_rec.dr_cr_flag,
'N',
enc_control_rec.payroll_id,
NULL,
enc_liq_rec.gl_project_flag,
NULL,
enc_liq_rec.enc_summary_line_id,
NULL,
NULL,
enc_liq_rec.attribute_category, -- Introduced DFF columns for bug fix 2908859
enc_liq_rec.attribute1,
enc_liq_rec.attribute2,
enc_liq_rec.attribute3,
enc_liq_rec.attribute4,
enc_liq_rec.attribute5,
enc_liq_rec.attribute6,
enc_liq_rec.attribute7,
enc_liq_rec.attribute8,
enc_liq_rec.attribute9,
enc_liq_rec.attribute10,
NULL, -- Bug 4068182
l_return_status);
update psp_enc_summary_lines set status_code='S'
where enc_summary_line_id=enc_liq_rec.enc_summary_line_id;
update psp_enc_controls
set gl_phase = 'Summarize' --- replaced NULL for 2444657
where enc_control_id = enc_control_rec.enc_control_id;
PROCEDURE insert_into_enc_sum_lines(
p_enc_summary_line_id OUT NOCOPY NUMBER,
p_business_group_id IN NUMBER,
p_enc_control_id IN NUMBER,
p_time_period_id IN NUMBER,
p_person_id IN NUMBER,
p_assignment_id IN NUMBER,
p_effective_date IN DATE,
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_summary_amount IN NUMBER,
p_dr_cr_flag IN VARCHAR2,
p_status_code IN VARCHAR2,
p_payroll_id IN NUMBER,
p_gl_period_id IN NUMBER,
p_gl_project_flag IN VARCHAR2,
p_suspense_org_account_id IN NUMBER,
p_superceded_line_id IN NUMBER,
p_gms_posting_override_date IN DATE,
p_gl_posting_override_date IN DATE,
p_attribute_category IN VARCHAR2, -- Introduced DFF columns for bug fix 2908859
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_expenditure_item_id IN NUMBER, -- bug 4068182
p_return_status OUT NOCOPY VARCHAR2
) IS
BEGIN
SELECT PSP_ENC_SUMMARY_LINES_S.NEXTVAL
INTO P_ENC_SUMMARY_LINE_ID
FROM DUAL;
INSERT INTO PSP_ENC_SUMMARY_LINES(
ENC_SUMMARY_LINE_ID,
BUSINESS_GROUP_ID,
ENC_CONTROL_ID,
TIME_PERIOD_ID,
PERSON_ID,
ASSIGNMENT_ID,
EFFECTIVE_DATE,
SET_OF_BOOKS_ID,
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
SUMMARY_AMOUNT,
DR_CR_FLAG,
STATUS_CODE,
PAYROLL_ID,
GL_PERIOD_ID,
GL_PROJECT_FLAG,
SUSPENSE_ORG_ACCOUNT_ID,
SUPERCEDED_LINE_ID,
GMS_POSTING_OVERRIDE_DATE,
GL_POSTING_OVERRIDE_DATE,
ATTRIBUTE_CATEGORY, -- Introduced DFF columns for bug fix 2908859
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
expenditure_item_id, -- bug 4068182
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
VALUES(
p_enc_summary_line_id,
p_business_group_id,
p_enc_control_id,
p_time_period_id,
nvl(p_person_id,NULL),
nvl(p_assignment_id,NULL),
p_effective_date,
nvl(p_set_of_books_id,NULL),
p_gl_code_combination_id,
p_project_id,
p_expenditure_organization_id,
p_expenditure_type,
p_task_id,
p_award_id,
p_summary_amount,
p_dr_cr_flag,
p_status_code,
p_payroll_id,
p_gl_period_id,
p_gl_project_flag,
p_suspense_org_account_id,
p_superceded_line_id,
p_gms_posting_override_date,
p_gl_posting_override_date,
p_attribute_category, -- Introduced DFF columns for bug fix 2908859
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_expenditure_item_id, -- bug 4068182
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE);
--dbms_output.put_line('Insert into psp_enc_summary_lines failed');
g_error_api_path := 'insert_into_enc_sum_lines:'||g_error_api_path;
fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','INSERT_INTO_ENC_SUM_LINES');
END insert_into_enc_sum_lines;
SELECT enc_control_id,
payroll_id,
time_period_id,
gl_phase ---added for 2444657
FROM psp_enc_controls
WHERE payroll_id = nvl(p_payroll_id, payroll_id)
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type in ('N', 'Q', 'U') -- Included 'Q' for Enh. 2143723
AND action_code IN ('IT', 'IL','IU') -- Replaced I with IL and IU for Bug#2142865
AND run_id = g_run_id
AND business_group_id = g_bg_id
AND set_of_books_id = g_sob_id
AND gl_phase in ('Summarize','Transfer'); --- added for 2444657
SELECT pesl.enc_summary_line_id,
pesl.enc_control_id,
pesl.effective_date,
pesl.gl_code_combination_id,
TO_NUMBER(DECODE(pesl.dr_cr_flag, 'D', pesl.summary_amount, NULL)) debit_amount,
TO_NUMBER(DECODE(pesl.dr_cr_flag, 'C', pesl.summary_amount, NULL)) credit_amount,
pesl.dr_cr_flag,
pesl.set_of_books_id,
pesl.attribute1,
pesl.attribute2,
pesl.attribute3,
pesl.attribute4,
pesl.attribute5,
pesl.attribute6,
pesl.attribute7,
pesl.attribute8,
pesl.attribute9,
pesl.attribute10,
pesl.attribute11,
pesl.attribute12,
pesl.attribute13,
pesl.attribute14,
pesl.attribute15,
pesl.attribute16,
pesl.attribute17,
pesl.attribute18,
pesl.attribute19,
pesl.attribute20,
pesl.attribute21,
pesl.attribute22,
pesl.attribute23,
pesl.attribute24,
pesl.attribute25,
pesl.attribute26,
pesl.attribute27,
pesl.attribute28,
pesl.attribute29,
pesl.attribute30
FROM psp_enc_summary_lines pesl
WHERE pesl.status_code = 'N'
AND pesl.gl_code_combination_id is NOT NULL
AND pesl.enc_control_id = l_enc_control_id;
SELECT DISTINCT group_id
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
/*WHERE enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = NVL(p_payroll_id, pec.payroll_id)
AND action_type IN ('N', 'U', 'Q')
AND action_code IN ('IT', 'IL', 'IU')
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND pec.gl_phase = 'Transfer')*/
AND status_code = 'N'
AND gl_code_combination_id IS NOT NULL;
SELECT DISTINCT enc_control_id
FROM psp_enc_summary_lines
WHERE group_id = l_group_id;
SELECT gl_interface_control_s.nextval
INTO l_group_id
FROM dual;
UPDATE psp_enc_summary_lines
SET group_id = l_group_id
WHERE status_code = 'N'
AND gl_code_combination_id IS NOT NULL
AND superceded_line_id IS NOT NULL
AND payroll_action_id = p_payroll_action_id;
/* AND enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)
AND action_type IN ('N', 'U', 'Q')
AND action_code IN ('IT', 'IL', 'IU')
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND pec.gl_phase = 'Summarize');*/
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated group_id in psp_enc_summary_lines for new liquidation lines');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into gl_interface');
INSERT INTO gl_interface
(status, set_of_books_id, accounting_date,
currency_code, date_created, created_by,
actual_flag, user_je_category_name, user_je_source_name,
encumbrance_type_id, code_combination_id, entered_dr,
entered_cr, group_id, reference1,
reference2, reference4, reference6,
reference10, attribute1, attribute2,
attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11,
attribute12, attribute13, attribute14,
attribute15, attribute16, attribute17,
attribute18, attribute19, attribute20,
reference21, reference22, reference23,
reference24, reference25, reference26,
reference27, reference28, reference29,
reference30)
SELECT 'NEW', pesl.set_of_books_id, pesl.effective_date,
DECODE(pec.uom, 'M', g_currency_code, 'STAT'), SYSDATE, l_created_by,
'E', l_user_je_cat, l_user_je_source,
l_enc_type_id, pesl.gl_code_combination_id,
TO_NUMBER(DECODE(pesl.dr_cr_flag, 'D', pesl.summary_amount, NULL)) debit_amount,
TO_NUMBER(DECODE(pesl.dr_cr_flag, 'C', pesl.summary_amount, NULL)) credit_amount,
l_group_id, pesl.enc_control_id,
pesl.enc_control_id, 'LD ENCUMBRANCE', 'E:' || pesl.enc_summary_line_id,
'LD ENCUMBRANCE', attribute1, attribute2,
attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11,
attribute12, attribute13, attribute14,
attribute15, attribute16, attribute17,
attribute18, attribute19, attribute20,
attribute21, attribute22, attribute23,
attribute24, attribute25, attribute26,
attribute27, attribute28, attribute29,
attribute30
FROM psp_enc_summary_lines pesl,
psp_enc_controls pec
WHERE pec.enc_control_id = pesl.enc_control_id
AND pesl.status_code = 'N'
AND pesl.gl_code_combination_id is NOT NULL
AND superceded_line_id IS NOT NULL
AND pesl.payroll_action_id = p_payroll_action_id;
/* AND enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)
AND action_type IN ('N', 'U', 'Q')
AND action_code IN ('IT', 'IL', 'IU')
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND pec.gl_phase = 'Summarize');*/
SELECT period_name, end_date
INTO l_period_name, l_period_end_dt
FROM per_time_periods
WHERE time_period_id = enc_control_rec.time_period_id ;
UPDATE psp_enc_summary_lines
SET group_id = l_group_id
WHERE status_code = 'N'
AND gl_code_combination_id is NOT NULL
AND enc_control_id = enc_control_rec.enc_control_id;
SELECT currency_code
INTO l_cur_code
FROM gl_sets_of_books
WHERE set_of_books_id = l_sob_id;
insert_into_gl_int(
L_SOB_ID,
INT_REC.EFFECTIVE_DATE,
G_CURRENCY_CODE,
L_USER_JE_CAT,
L_USER_JE_SOURCE,
L_ENC_TYPE_ID,
INT_REC.GL_CODE_COMBINATION_ID,
L_ENT_DR,
L_ENT_CR,
l_group_id,
L_REF1,
L_REF1,
L_REF4,
'E:' || INT_REC.ENC_SUMMARY_LINE_ID,
L_REF4,
INT_REC.ATTRIBUTE1,
INT_REC.ATTRIBUTE2,
INT_REC.ATTRIBUTE3,
INT_REC.ATTRIBUTE4,
INT_REC.ATTRIBUTE5,
INT_REC.ATTRIBUTE6,
INT_REC.ATTRIBUTE7,
INT_REC.ATTRIBUTE8,
INT_REC.ATTRIBUTE9,
INT_REC.ATTRIBUTE10,
INT_REC.ATTRIBUTE11,
INT_REC.ATTRIBUTE12,
INT_REC.ATTRIBUTE13,
INT_REC.ATTRIBUTE14,
INT_REC.ATTRIBUTE15,
INT_REC.ATTRIBUTE16,
INT_REC.ATTRIBUTE17,
INT_REC.ATTRIBUTE18,
INT_REC.ATTRIBUTE19,
INT_REC.ATTRIBUTE20,
INT_REC.ATTRIBUTE21,
INT_REC.ATTRIBUTE22,
INT_REC.ATTRIBUTE23,
INT_REC.ATTRIBUTE24,
INT_REC.ATTRIBUTE25,
INT_REC.ATTRIBUTE26,
INT_REC.ATTRIBUTE27,
INT_REC.ATTRIBUTE28,
INT_REC.ATTRIBUTE29,
INT_REC.ATTRIBUTE30,
L_RETURN_STATUS);
--dbms_output.put_line('Insert into gl_interface failed');
select group_id
into gl_tie_tab(l_rec_no).r_group_id
from psp_enc_summary_lines
where status_code = 'N'
and gl_code_combination_id is not null
and rownum = 1;
SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
INTO l_int_run_id
FROM dual;
insert into gl_interface_control(
je_source_name,
status,
interface_run_id,
group_id,
set_of_books_id)
VALUES (
l_user_je_source,
'S',
l_int_run_id,
l_group_id,
g_sob_id
);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted control record into gl_interface_control');
/***** Converted the following UPDATE TO BULK for R12 performance fixes (bug 4507892)
update psp_enc_controls
set gl_phase = 'Transfer'
where enc_control_id in (select distinct enc_control_id
from psp_enc_summary_lines
where group_id = l_group_id);
UPDATE psp_enc_controls
SET gl_phase = 'Transfer'
WHERE enc_control_id = r_enc_controls.enc_control_id(I);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase to ''Transfer'' in psp_enc_controls');
r_enc_controls.enc_control_id.DELETE;
--insert into psp_stout values(5, ' request submitted ');
p_action_type, -- Added for Restart Update Enh.
l_return_status);
delete gl_interface
where group_id = gl_tie_tab(i).r_group_id
and user_je_source_name = l_user_je_source
and set_of_books_id = l_sob_id;
DELETE gl_interface
WHERE group_id = r_groups.group_id(recno)
AND user_je_source_name = l_user_je_source
AND set_of_books_id = g_sob_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted groups from gl_interface for which gl_enc_tie_back is complete');
SELECT user_je_source_name
INTO p_user_je_source_name
FROM gl_je_sources
WHERE je_source_name = 'OLD';
SELECT user_je_category_name
INTO p_user_je_category_name
FROM gl_je_categories
WHERE je_category_name = 'OLD';
SELECT encumbrance_type_id
INTO p_encumbrance_type_id
FROM gl_encumbrance_types
WHERE encumbrance_type = 'OLD'
AND enabled_flag = 'Y';
p_action_type IN VARCHAR2, -- Added for Restart Update Enh.
p_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR int_count_cur IS
SELECT COUNT(1)
FROM gl_interface
WHERE user_je_source_name = 'OLD'
AND set_of_books_id = p_set_of_books_id
AND group_id = p_group_id;
SELECT enc_summary_line_id,
enc_control_id,
dr_cr_flag,
summary_amount
FROM psp_enc_summary_lines
WHERE group_id = p_group_id;
SELECT status,
to_number(trim(substr(reference6,3))) --- 4072324
FROM gl_interface
WHERE user_je_source_name = 'OLD'
AND set_of_books_id = p_set_of_books_id
AND group_id = p_group_id;
AND reference6 IN (SELECT 'E:' || enc_summary_line_id -- Introduced for bug fix 3953230
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_control_id = p_enc_control_id);
SELECT pel.rowid,
pel.effective_date,
--pel.attribute30
pel.suspense_org_account_id,
pel.superceded_line_id
FROM psp_enc_summary_lines pel
WHERE pel.enc_summary_line_id = p_enc_line_id
and pel.enc_control_id=p_enc_control_id
and pel.status_code='N';
SELECT hou.organization_id, hou.name, poa.gl_code_combination_id
FROM hr_all_organization_units hou, psp_organization_accounts poa
WHERE hou.organization_id = poa.organization_id
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND poa.organization_account_id = p_org_id;
SELECT hou.organization_id, hou.name
FROM hr_all_organization_units hou,
per_assignments_f paf,
psp_enc_summary_lines pel
WHERE pel.enc_summary_line_id = p_line_id
AND pel.enc_control_id=p_enc_control_id
-- AND pel.assignment_id = paf.assignment_id
AND pel.person_id= paf.person_id
AND paf.primary_flag='Y'
AND pel.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id
AND pel.effective_date between
hou.date_from and nvl(hou.date_to,pel.effective_date);
SELECT hou.name,
hou.organization_id,
pel.rowid,
pel.assignment_id,
pel.effective_date,
pel.attribute30,
pel.gl_code_combination_id
FROM hr_all_organization_units hou,
per_assignments_f paf,
psp_enc_summary_lines pel
WHERE pel.enc_control_id = p_enc_control_id
AND pel.gl_project_flag = 'G'
AND pel.status_code = 'A'
AND pel.assignment_id = paf.assignment_id(+)
AND pel.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND pel.business_group_id = g_bg_id
AND pel.set_of_books_id = g_sob_id
AND paf.organization_id = hou.organization_id
AND pel.effective_date between hou.date_from and nvl(hou.date_to,pel.effective_date);
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.expenditure_organization_id,
poa.expenditure_type,
poa.award_id,
poa.task_id
FROM psp_organization_accounts poa
WHERE poa.organization_id = p_organization_id
AND poa.account_type_code = 'S'
AND poa.business_group_id = g_bg_id
AND poa.set_of_books_id = g_sob_id
AND p_encumbrance_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_encumbrance_date);
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.expenditure_organization_id,
poa.expenditure_type,
poa.award_id,
poa.task_id
FROM psp_organization_accounts poa
WHERE
/ * poa.account_type_code = 'G'
AND poa.business_group_id = g_bg_id
AND poa.set_of_books_id = g_sob_id
AND p_encumbrance_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_encumbrance_date); Bug 2056877.* /
SELECT COUNT(1)
FROM gl_interface
WHERE user_je_source_name = 'OLD'
AND set_of_books_id = p_set_of_books_id
AND group_id = p_group_id
AND status = 'NEW';
SELECT superceded_line_id
FROM psp_enc_summary_lines
WHERE group_id = p_group_id;
SELECT DISTINCT enc_control_id
FROM psp_enc_summary_lines
WHERE group_id = p_group_id;
/***** Changed the following SELECT into CURSOR for bug fix 4625734
select count(*)
into l_cnt
from gl_interface
where user_je_source_name = 'OLD'
and set_of_books_id = p_set_of_books_id
and group_id = p_group_id
and status = 'NEW';
delete from gl_interface
where user_je_source_name = 'OLD'
and set_of_books_id = p_set_of_books_id
and group_id = p_group_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface');
UPDATE psp_enc_summary_lines
SET status_code = 'A'
WHERE enc_summary_line_id = r_superceded_lines.superceded_line_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to A for original summary lines');
r_superceded_lines.superceded_line_id.DELETE;
delete from psp_enc_summary_lines
where group_id = p_group_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
UPDATE psp_enc_controls pec
SET gl_phase = 'TieBack'
WHERE enc_control_id = r_enc_controls.enc_control_id(recno);
r_enc_controls.enc_control_id.DELETE;
SELECT count(*)
INTO l_cnt_gl_interface
FROM gl_interface
WHERE user_je_source_name = 'OLD'
AND set_of_books_id = p_set_of_books_id
AND group_id = p_group_id;
UPDATE psp_enc_summary_lines
SET interface_status = r_interface.status(recno)
-- status_code = 'R'
WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
AND r_interface.status(recno) <> 'P'
AND SUBSTR(r_interface.status(recno), 1, 1) <> 'W';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_enc_summary_lines setting interface_status');
UPDATE psp_enc_controls
SET gl_phase = 'TieBack'
WHERE enc_control_id IN (SELECT pesl.enc_control_id
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_controls resetting gl_phase to TieBack SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
UPDATE psp_enc_controls
SET gl_phase = 'Summarize'
WHERE enc_control_id IN (SELECT pesl.enc_control_id
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
AND pesl.status_code = 'N');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_controls resetting gl_phase to Summarize SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
UPDATE psp_enc_summary_lines
SET status_code = 'L'
WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
AND status_code = 'N';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_summary_lines setting status_code to ''L'' SQL%ROWCOUNT ' || SQL%ROWCOUNT);
UPDATE psp_enc_lines_history
SET change_flag = 'L'
WHERE enc_summary_line_id = (SELECT pesl2.superceded_line_id
FROM psp_enc_summary_lines pesl2
WHERE pesl2.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated respective lines in psp_enc_lines_history to ''L'' status SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
UPDATE psp_enc_controls pec
SET gl_phase = 'TieBack',
summ_gl_dr_amount = NVL(summ_gl_dr_amount, 0) + DECODE(r_interface.dr_cr_flag(recno), 'D', r_interface.summary_amount(recno), 0),
summ_gl_cr_amount = NVL(summ_gl_cr_amount, 0) + DECODE(r_interface.dr_cr_flag(recno), 'C', r_interface.summary_amount(recno), 0)
WHERE enc_control_id = r_interface.enc_control_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase, summ_gl_dr_amount, summ_gl_cr_amount in psp_enc_controls SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
UPDATE psp_enc_summary_lines
SET status_code='N'
WHERE enc_summary_line_id = TO_NUMBER(l_reference6);
UPDATE psp_enc_summary_lines
SET interface_status = l_status,
status_code='R'
WHERE enc_summary_line_id = TO_NUMBER(l_reference6);
UPDATE psp_enc_summary_lines
SET status_code='A'
WHERE enc_summary_line_id IN (SELECT superceded_line_id
FROM psp_enc_summary_lines
WHERE enc_summary_line_id = TO_NUMBER(l_reference6));
-- update enc_summary_lines with the reject status code
UPDATE psp_enc_summary_lines
SET interface_status = l_status
--, status_code = 'R'
WHERE enc_summary_line_id = to_number(l_reference6);
UPDATE psp_enc_summary_lines
-- SET status_code = 'A'
-- set status_code='L'
set status_code='N' ---- changed to NEW for 2479579
WHERE rowid = l_rowid;
UPDATE psp_enc_summary_lines
SET reject_reason_code = l_status,
-- status_code = 'A'
status_code='R'
WHERE rowid = l_rowid;
update psp_enc_summary_lines set status_code='A' where
enc_summary_line_id in (select superceded_line_id from
psp_Enc_summary_lines where rowid=l_rowid);
/ * Introduced for Restart Update/Quick Update Encumbrance Lines enh.
Record for invalid suspense reason in control record record so
that Restart Update can derive the failed point * /
IF l_return_value <> 'PROFILE_VAL_DATE_MATCHES' THEN
IF p_action_type IN ('Q','U') THEN
UPDATE psp_enc_controls
SET gl_phase = 'INVALID_SUSPENSE'
WHERE enc_control_id = p_enc_control_id;
/ * Added for Restart Update/Quick Update Encumbrance Lines Enh. * /
IF p_action_type IN ('Q','U') THEN
UPDATE psp_enc_controls
SET gl_phase = 'INVALID_SUSPENSE'
WHERE enc_control_id = p_enc_control_id;
UPDATE psp_enc_summary_lines
SET suspense_org_account_id = l_organization_account_id,
reject_reason_code ='EL:'||l_status,
gl_project_flag = l_gl_project_flag,
gl_code_combination_id = decode(l_gl_project_flag, 'P', null, l_susp_glccid),
project_id = decode(l_gl_project_flag, 'P', l_project_id, null),
expenditure_organization_id = decode(l_gl_project_flag, 'P', l_expenditure_organization_id, null),
expenditure_type = decode(l_gl_project_flag, 'P', l_expenditure_type, null),
task_id = decode(l_gl_project_flag, 'P', l_task_id, null),
award_id = decode(l_gl_project_flag, 'P', l_award_id, null),
-- status_code = 'A'
status_code='N'
WHERE rowid = l_rowid;
update psp_Enc_summary_lines set ------dr_cr_flag='D',
summary_amount= decode(dr_cr_flag,'C',-summary_amount,summary_amount)
where rowid=l_rowid;
UPDATE psp_enc_summary_lines
SET attribute30 = l_organization_account_id,
reject_reason_code = 'EL:' ||l_status,
gl_project_flag = l_gl_project_flag,
effective_date = l_encumbrance_date,
status_code = 'A'
WHERE rowid = l_rowid;
update psp_enc_controls
set gl_phase = 'Summarize' --- replaced 'TieBack' .... for 2444657
where enc_control_id = p_enc_control_id;
update psp_enc_controls --- introduced else part for 3413373
set gl_phase = 'TieBack'
where enc_control_id = p_enc_control_id;
/ * Added for Restart Update/Quick Update Encumbrance Lines Enh. * /
IF p_action_type IN ('Q','U') THEN
UPDATE psp_enc_controls
SET gl_phase = 'INVALID_SUSPENSE'
WHERE enc_control_id = p_enc_control_id;
/ *Commented for Restart Update/Quick Update Encumbrance Lines Enh.
because of the introduction of get global suspense in previous fix 2056877
IF l_suspense_ac_not_found = 'Y' THEN
fnd_message.set_name('PSP','PSP_LD_SUSPENSE_AC_NOT_EXIST');
UPDATE psp_enc_summary_lines
SET status_code = 'L'
WHERE enc_summary_line_id = l_enc_summary_line_id
and status_code = 'N';
update psp_enc_lines_history
set change_flag = 'L'
where enc_summary_line_id = ( select superceded_line_id
from psp_enc_summary_lines
where enc_summary_line_id = l_enc_summary_line_id);
UPDATE psp_enc_summary_lines
SET status_code = 'P'
-- WHERE enc_summary_line_id = l_enc_summary_line_id
WHERE enc_control_id = p_enc_control_id
and group_id=p_group_id
and gl_project_flag = 'G'
and status_code = 'A';
UPDATE psp_enc_controls
SET summ_gl_cr_amount = l_dr_summary_amount
WHERE enc_control_id = p_enc_control_id;
UPDATE psp_enc_controls
SET summ_gl_dr_amount = l_cr_summary_amount
WHERE enc_control_id = p_enc_control_id;
update psp_enc_controls
set gl_phase = 'TieBack'
where enc_control_id = p_enc_control_id;
PROCEDURE insert_into_gl_int (
P_SET_OF_BOOKS_ID IN NUMBER,
P_ACCOUNTING_DATE IN DATE,
P_CURRENCY_CODE IN VARCHAR2,
P_USER_JE_CATEGORY_NAME IN VARCHAR2,
P_USER_JE_SOURCE_NAME IN VARCHAR2,
P_ENCUMBRANCE_TYPE_ID IN NUMBER,
P_CODE_COMBINATION_ID IN NUMBER,
P_ENTERED_DR IN NUMBER,
P_ENTERED_CR IN NUMBER,
P_GROUP_ID IN NUMBER,
P_REFERENCE1 IN VARCHAR2,
P_REFERENCE2 IN VARCHAR2,
P_REFERENCE4 IN VARCHAR2,
P_REFERENCE6 IN VARCHAR2,
P_REFERENCE10 IN VARCHAR2,
P_ATTRIBUTE1 IN VARCHAR2,
P_ATTRIBUTE2 IN VARCHAR2,
P_ATTRIBUTE3 IN VARCHAR2,
P_ATTRIBUTE4 IN VARCHAR2,
P_ATTRIBUTE5 IN VARCHAR2,
P_ATTRIBUTE6 IN VARCHAR2,
P_ATTRIBUTE7 IN VARCHAR2,
P_ATTRIBUTE8 IN VARCHAR2,
P_ATTRIBUTE9 IN VARCHAR2,
P_ATTRIBUTE10 IN VARCHAR2,
P_ATTRIBUTE11 IN VARCHAR2,
P_ATTRIBUTE12 IN VARCHAR2,
P_ATTRIBUTE13 IN VARCHAR2,
P_ATTRIBUTE14 IN VARCHAR2,
P_ATTRIBUTE15 IN VARCHAR2,
P_ATTRIBUTE16 IN VARCHAR2,
P_ATTRIBUTE17 IN VARCHAR2,
P_ATTRIBUTE18 IN VARCHAR2,
P_ATTRIBUTE19 IN VARCHAR2,
P_ATTRIBUTE20 IN VARCHAR2,
P_ATTRIBUTE21 IN VARCHAR2,
P_ATTRIBUTE22 IN VARCHAR2,
P_ATTRIBUTE23 IN VARCHAR2,
P_ATTRIBUTE24 IN VARCHAR2,
P_ATTRIBUTE25 IN VARCHAR2,
P_ATTRIBUTE26 IN VARCHAR2,
P_ATTRIBUTE27 IN VARCHAR2,
P_ATTRIBUTE28 IN VARCHAR2,
P_ATTRIBUTE29 IN VARCHAR2,
P_ATTRIBUTE30 IN VARCHAR2,
P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
BEGIN
INSERT INTO GL_INTERFACE(
STATUS,
SET_OF_BOOKS_ID,
ACCOUNTING_DATE,
CURRENCY_CODE,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
USER_JE_CATEGORY_NAME,
USER_JE_SOURCE_NAME,
ENCUMBRANCE_TYPE_ID,
CODE_COMBINATION_ID,
ENTERED_DR,
ENTERED_CR,
GROUP_ID,
REFERENCE1,
REFERENCE2,
REFERENCE4,
REFERENCE6,
REFERENCE10,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
REFERENCE21,
REFERENCE22,
REFERENCE23,
REFERENCE24,
REFERENCE25,
REFERENCE26,
REFERENCE27,
REFERENCE28,
REFERENCE29,
REFERENCE30)
VALUES(
'NEW',
P_SET_OF_BOOKS_ID,
P_ACCOUNTING_DATE,
P_CURRENCY_CODE,
SYSDATE,
FND_GLOBAL.USER_ID,
'E',
P_USER_JE_CATEGORY_NAME,
P_USER_JE_SOURCE_NAME,
P_ENCUMBRANCE_TYPE_ID,
P_CODE_COMBINATION_ID,
P_ENTERED_DR,
P_ENTERED_CR,
P_GROUP_ID,
P_REFERENCE1,
P_REFERENCE2,
P_REFERENCE4,
P_REFERENCE6,
P_REFERENCE10,
P_ATTRIBUTE1,
P_ATTRIBUTE2,
P_ATTRIBUTE3,
P_ATTRIBUTE4,
P_ATTRIBUTE5,
P_ATTRIBUTE6,
P_ATTRIBUTE7,
P_ATTRIBUTE8,
P_ATTRIBUTE9,
P_ATTRIBUTE10,
P_ATTRIBUTE11,
P_ATTRIBUTE12,
P_ATTRIBUTE13,
P_ATTRIBUTE14,
P_ATTRIBUTE15,
P_ATTRIBUTE16,
P_ATTRIBUTE17,
P_ATTRIBUTE18,
P_ATTRIBUTE19,
P_ATTRIBUTE20,
P_ATTRIBUTE21,
P_ATTRIBUTE22,
P_ATTRIBUTE23,
P_ATTRIBUTE24,
P_ATTRIBUTE25,
P_ATTRIBUTE26,
P_ATTRIBUTE27,
P_ATTRIBUTE28,
P_ATTRIBUTE29,
P_ATTRIBUTE30);
g_error_api_path := 'insert_into_gl_int:'||g_error_api_path;
fnd_msg_pub.add_exc_msg('psp_enc_liq_tran','insert_into_gl_int');
END insert_into_gl_int;
SELECT enc_summary_line_id,
effective_date,
enc_control_id,
time_period_id,
set_of_books_id,
project_id,
task_id,
award_id,
expenditure_organization_id,
expenditure_type,
expenditure_item_id,
-summary_amount,
DECODE(dr_cr_flag, 'C', 'D', 'D', 'C') dr_cr_flag,
person_id,
assignment_id,
gl_project_flag,
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_enc_summary_lines pesl
WHERE enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)
AND action_type IN ('N', 'U', 'Q')
AND action_code = 'IL'
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND (pec.gms_phase IS NULL or pec.gms_phase = 'TieBack'))
AND gl_project_flag = 'P'
AND status_code = 'A'
AND EXISTS (SELECT 1
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.enc_summary_line_id = pesl.enc_summary_line_id);
SELECT enc_summary_line_id,
effective_date,
enc_control_id,
time_period_id,
set_of_books_id,
project_id,
task_id,
award_id,
expenditure_organization_id,
expenditure_type,
expenditure_item_id,
-summary_amount,
DECODE(dr_cr_flag, 'C', 'D', 'D', 'C') dr_cr_flag,
person_id,
assignment_id,
gl_project_flag,
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_enc_summary_lines pesl
WHERE enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)
AND action_type IN ('N', 'U', 'Q')
AND action_code = 'IU'
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND (pec.gms_phase IS NULL or pec.gms_phase = 'TieBack'))
AND gl_project_flag = 'P'
AND status_code = 'A'
AND EXISTS (SELECT 1 FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = pesl.assignment_id
AND peca.request_id IS NOT NULL
AND peca.payroll_id = p_payroll_id)
AND EXISTS (SELECT 1
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.enc_summary_line_id = pesl.enc_summary_line_id);
SELECT enc_summary_line_id,
effective_date,
enc_control_id,
time_period_id,
set_of_books_id,
project_id,
task_id,
award_id,
expenditure_organization_id,
expenditure_type,
expenditure_item_id,
-summary_amount,
DECODE(dr_cr_flag, 'C', 'D', 'D', 'C') dr_cr_flag,
person_id,
assignment_id,
gl_project_flag,
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_enc_summary_lines pesl
WHERE enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)
AND action_type IN ('N', 'U', 'Q')
AND action_code = 'IU'
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND (pec.gms_phase IS NULL or pec.gms_phase = 'TieBack'))
AND gl_project_flag = 'P'
AND status_code = 'A'
AND EXISTS (SELECT 1 FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = pesl.assignment_id
AND peca.request_id IS NOT NULL
AND peca.payroll_id = p_payroll_id
AND peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
AND EXISTS (SELECT 1
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.enc_summary_line_id = pesl.enc_summary_line_id);
SELECT enc_summary_line_id,
effective_date,
enc_control_id,
time_period_id,
set_of_books_id,
project_id,
task_id,
award_id,
expenditure_organization_id,
expenditure_type,
expenditure_item_id,
-summary_amount,
DECODE(dr_cr_flag, 'C', 'D', 'D', 'C') dr_cr_flag,
person_id,
assignment_id,
gl_project_flag,
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_enc_summary_lines pesl
WHERE enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = NVL(p_payroll_id, pec.payroll_id)
AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)
AND action_type IN ('N', 'U', 'Q')
AND action_code = 'IT'
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND (pec.gms_phase IS NULL OR pec.gms_phase = 'TieBack'))
AND gl_project_flag = 'P'
AND status_code = 'A'
AND person_id = g_person_id
AND EXISTS (SELECT 1
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.enc_summary_line_id = pesl.enc_summary_line_id);
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Fetching Update Liquidation Lines');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Fetching Quick Update Liquidation Lines');
INSERT INTO psp_enc_summary_lines
(enc_summary_line_id, business_group_id, enc_control_id,
time_period_id, person_id, assignment_id,
effective_date, set_of_books_id, project_id,
task_id, award_id, expenditure_organization_id,
expenditure_type, expenditure_item_id,
summary_amount, dr_cr_flag, status_code,
payroll_id, gl_project_flag, superceded_line_id,
attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8,
attribute9, attribute10, liquidate_request_id,
proposed_termination_date, last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
VALUES (psp_enc_summary_lines_s.NEXTVAL, g_bg_id,
r_liq_lines.enc_control_id(recno), r_liq_lines.time_period_id(recno),
r_liq_lines.person_id(recno), r_liq_lines.assignment_id(recno),
r_liq_lines.effective_date(recno), r_liq_lines.set_of_books_id(recno),
r_liq_lines.project_id(recno), r_liq_lines.task_id(recno),
r_liq_lines.award_id(recno), r_liq_lines.expenditure_org_id(recno),
r_liq_lines.expenditure_type(recno), r_liq_lines.expenditure_item_id(recno),
r_liq_lines.summary_amount(recno), r_liq_lines.dr_cr_flag(recno), 'N',
p_payroll_id, r_liq_lines.gl_project_flag(recno),
r_liq_lines.enc_summary_line_id(recno), r_liq_lines.attribute_category(recno),
r_liq_lines.attribute1(recno), r_liq_lines.attribute2(recno),
r_liq_lines.attribute3(recno), r_liq_lines.attribute4(recno),
r_liq_lines.attribute5(recno), r_liq_lines.attribute6(recno),
r_liq_lines.attribute7(recno), r_liq_lines.attribute8(recno),
r_liq_lines.attribute9(recno), r_liq_lines.attribute10(recno),
g_request_id, g_actual_term_date,
SYSDATE, l_last_updated_by, l_last_update_login, l_last_updated_by, SYSDATE);
UPDATE psp_enc_summary_lines
SET status_code = 'S'
WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');
UPDATE psp_enc_controls
SET gms_phase = 'Summarize'
WHERE enc_control_id = r_liq_lines.enc_control_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''Summarize'' in psp_enc_controls');
SELECT enc_control_id,
payroll_id,
time_period_id
FROM psp_enc_controls
WHERE payroll_id = nvl(p_payroll_id, payroll_id)
AND (total_dr_amount IS NOT NULL OR total_cr_amount IS NOT NULL)
AND action_type in ('N', 'Q', 'U') -- Included 'Q' for Enh. 2143723
AND action_code IN ('IL','IU') -- Replaced I with IL and IU for Bug#2142865
AND run_id = g_run_id
AND business_group_id = g_bg_id
AND set_of_books_id = g_sob_id
AND (gms_phase = 'TieBack' or gms_phase is null); --- added for 2444657
SELECT distinct pesl.enc_summary_line_id,
pesl.effective_date,
pesl.project_id,
pesl.expenditure_organization_id,
pesl.expenditure_type,
pesl.task_id,
pesl.award_id,
pesl.summary_amount,
pesl.dr_cr_flag,
pesl.person_id,
pesl.assignment_id,
pesl.gl_project_flag,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', pesl.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10,
pesl.expenditure_item_id -- 4068182
--- removed history table from the from clause.. performace 3953230
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_control_id = p_enc_control_id
--- changed pelh to pesl for performance ..3684930
AND pesl.gl_project_flag = 'P'
AND pesl.status_code = 'A'
AND pesl.gl_code_combination_id is NULL
AND ( (p_action_type='L' and g_person_id is null) --- g_person_id null check for 3413373
OR (g_person_id is not null AND peSL.person_id = g_person_id
AND EXISTS (SELECT 1 FROM PSP_ENC_LINES_HISTORY PELH WHERE PELH.ENC_SUMMARY_LINE_ID = PESL.ENC_SUMMARY_LINE_ID
AND PELH.CHANGE_FLAG = 'N')
AND peSL.time_period_id >= g_term_period_id)
OR
(p_action_type IN ('Q', 'U') AND EXISTS
(SELECT 1 FROM PSP_ENC_LINES_HISTORY PELH WHERE PELH.ENC_SUMMARY_LINE_ID = PESL.ENC_SUMMARY_LINE_ID
AND PELH.CHANGE_FLAG = 'N')
AND EXISTS (SELECT 1 FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = pesl.assignment_id
AND peca.request_id IS NOT NULL
AND peca.payroll_id =p_payroll_id
AND ((p_action_type = 'Q' and peca.change_type IN ('LS', 'ET', 'AS', 'QU'))
OR p_action_type = 'U'))
)
);
pesl.assignment_id in (select pelh.assignment_id FROM psp_enc_lines_history pelh,
psp_enc_controls pec
where pec.enc_control_id=p_enc_control_id AND
pelh.time_period_id=pec.time_period_id
and pelh.change_flag='N'));
update psp_enc_controls
set gms_phase = 'Summarize' --- replaced NULL...for 2444657
where enc_control_id = enc_control_rec.enc_control_id;
insert_into_enc_sum_lines(
l_enc_summary_line_id,
g_bg_id,
enc_control_rec.enc_control_id,
enc_control_rec.time_period_id,
enc_liq_rec.person_id,
enc_liq_rec.assignment_id, -- Included for Enh. 2143723
enc_liq_rec.effective_date,
g_sob_id,
NULL,
enc_liq_rec.project_id,
enc_liq_rec.expenditure_organization_id,
enc_liq_rec.expenditure_type,
enc_liq_rec.task_id,
enc_liq_rec.award_id,
enc_liq_rec.summary_amount,
enc_liq_rec.dr_cr_flag,
'N',
enc_control_rec.payroll_id,
NULL,
enc_liq_rec.gl_project_flag,
NULL,
enc_liq_rec.enc_summary_line_id,
NULL,
NULL,
enc_liq_rec.attribute_category, -- Introduced DFF columns for bug fix 2908859
enc_liq_rec.attribute1,
enc_liq_rec.attribute2,
enc_liq_rec.attribute3,
enc_liq_rec.attribute4,
enc_liq_rec.attribute5,
enc_liq_rec.attribute6,
enc_liq_rec.attribute7,
enc_liq_rec.attribute8,
enc_liq_rec.attribute9,
enc_liq_rec.attribute10,
enc_liq_rec.expenditure_item_id, -- Introduced for bug 4068182
p_return_status);
update psp_enc_summary_lines set status_code='S'
where enc_summary_line_id=enc_liq_rec.enc_summary_line_id;
/***** Commented for Create and Update multi thread enh.
CURSOR enc_control_cur IS
SELECT DISTINCT pec.enc_control_id,
pec.payroll_id,
pec.time_period_id,
pec.gms_phase, ---- added for 2444657
ptp.end_date
per_time_periods ptp
WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)
AND pec.action_type in ('N', 'Q', 'U') -- Included 'Q' for Enh. 2143723
AND pec.action_code IN ('IT', 'IL','IU') -- Replaced I with IL and IU for Bug#2142865
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND pec.gms_phase in ('Summarize','Transfer')
AND ptp.time_period_id = pec.time_period_id;
End of Comment for Create and Update multi thread enh. *****/
-- CURSOR int_cur(p_enc_control_id IN NUMBER) IS
CURSOR int_cur IS
SELECT pa_txn_interface_s.NEXTVAL,
pesl.enc_summary_line_id,
pesl.effective_date,
pesl.time_period_id,
pesl.person_id,
pesl.project_id,
pesl.task_id,
pesl.award_id,
pesl.expenditure_type,
pesl.expenditure_organization_id,
DECODE(pec.uom, 'M', g_currency_code, 'STAT') currency_code,
TO_NUMBER(DECODE(pec.uom, 'H', pesl.summary_amount, 1)) quantity,
TO_NUMBER(DECODE(pec.uom, 'M', pesl.summary_amount, 0)) summary_amount,
pesl.dr_cr_flag,
pesl.attribute2,
pesl.attribute3,
pesl.attribute6,
pesl.attribute7,
pesl.attribute8,
pesl.attribute9,
pesl.attribute10,
pesl.superceded_line_id, -- Introduced for bug fix 6062628
hou.name exp_org_name, -- Introduced the following columns for bug fix 4625734
ppa.segment1 project_number,
ppa.org_id operating_unit,
pt.task_number,
TO_CHAR(pesl.enc_control_id) || ':' || ptp.period_name expenditure_comment,
ptp.period_name,
ptp.end_date,
pesl.effective_date,
papf.employee_number,
pesl.gms_batch_name --6146805
FROM psp_enc_summary_lines pesl,
hr_organization_units hou, -- Introduced the following tables as part of bug fix 4625734
pa_projects_all ppa,
pa_tasks_expend_v pt, -- Replaced pa_tasks with pa_tasks_expend_v for Bug : 16391366
per_time_periods ptp,
per_all_people_f papf,
psp_enc_controls pec
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pec.enc_control_id = pesl.enc_control_id
AND pesl.status_code = 'N'
AND pesl.gl_code_combination_id is NULL
AND superceded_line_id IS NOT NULL
AND pesl.award_id IS NOT NULL
AND pesl.expenditure_organization_id = hou.organization_id (+)
AND pesl.project_id = ppa.project_id (+)
AND pesl.task_id = pt.task_id (+)
AND pesl.time_period_id = ptp.time_period_id
AND papf.person_id = pesl.person_id
AND pesl.effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND pesl.gms_batch_name IS NOT NULL; --6146805
SELECT DISTINCT enc_control_id
FROM psp_enc_summary_lines
WHERE gms_batch_name = g_gms_batch_name;
SELECT transaction_source
FROM pa_transaction_sources
WHERE transaction_source = 'GOLDE';
SELECT DISTINCT gms_batch_name
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
/*WHERE enc_control_id IN (SELECT pec.enc_control_id
FROM psp_enc_controls pec
WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
AND pec.run_id = g_run_id
AND pec.business_group_id = g_bg_id
AND pec.set_of_books_id = g_sob_id
AND pec.gms_phase = 'Transfer')*/
AND status_code = 'N'
AND gl_code_combination_id IS NULL;
/***** Commented for Create and Update multi thread enh.
TYPE t_enc_control IS RECORD
(enc_control_id t_number_15,
payroll_id t_number_15,
time_period_id t_number_15,
gms_phase t_char_300,
end_date t_date);
End of Comment for Create and Update multi thread enh. *****/
TYPE t_interface IS RECORD
(txn_interface_id t_number_15,
enc_summary_line_id t_number_15,
effective_date t_date,
time_period_id t_number_15,
person_id t_number_15,
project_id t_number_15,
task_id t_number_15,
award_id t_number_15,
expenditure_type t_char_300,
expenditure_organization_id t_number_15,
currency_code t_char_300,
quantity t_number,
summary_amount t_number,
dr_cr_flag t_char_300,
attribute2 t_char_300,
attribute3 t_char_300,
attribute6 t_char_300,
attribute7 t_char_300,
attribute8 t_char_300,
attribute9 t_char_300,
attribute10 t_char_300,
superceded_line_id t_number_15, --Bug 6062628
employee_number t_char_300,
exp_org_name t_char_300,
project_number t_char_300,
operating_unit t_char_300,
task_number t_char_300,
expenditure_comment t_char_300,
period_name t_char_300,
end_date t_date,
gms_overriding_date t_date,
exp_end_date t_date,
gms_batch_name t_number_15);
SELECT distinct org_id
FROM psp_enc_summary_lines
WHERE status_code = 'N'
AND gl_code_combination_id IS NULL
AND gms_batch_name IS NULL
AND payroll_action_id = p_payroll_action_id;
SELECT transaction_source
INTO l_gms_transaction_source
FROM pa_transaction_sources
WHERE transaction_source = 'GOLDE';
org_id_tab.delete;
gms_batch_name_tab.delete;
req_id_tab.delete;
call_status_tab.delete;
/***** SELECT to_char(psp_gms_batch_name_s.nextval)
INTO g_gms_batch_name --- replaced with global for 3473294
FROM dual; *****/
SELECT to_char(psp_gms_batch_name_s.nextval)
INTO gms_batch_name_tab(i)
FROM dual;
/***** Commented for Create and Update multi thread enh.
OPEN enc_control_cur;
End of Comment for Create and Update multi thread enh. *****/
-- FORALL recno IN 1..r_enc_control.enc_control_id.COUNT
FORALL I IN 1..org_id_tab.count
UPDATE psp_enc_summary_lines
SET gms_batch_name = gms_batch_name_tab(i)
WHERE status_code = 'N'
AND gl_code_combination_id is NULL
AND superceded_line_id IS NOT NULL
-- AND enc_control_id = r_enc_control.enc_control_id(recno)
AND payroll_action_id = p_payroll_action_id
AND org_id = org_id_tab(i);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated batch_name in psp_enc_summary_lines for each org_id count: '||g_bulk_row_count);
UPDATE psp_enc_summary_lines pesl
SET gms_posting_override_date = r_interface.gms_overriding_date(recno)
WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
AND TRUNC(r_interface.effective_date(recno)) <> TRUNC(r_interface.gms_overriding_date(recno));
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated GMS Override Date count: '||g_bulk_row_count);
INSERT INTO pa_transaction_interface_all
(txn_interface_id, transaction_source,
batch_name, expenditure_ending_date,
employee_number, organization_name,
expenditure_item_date, project_number,
task_number, expenditure_type,
quantity, raw_cost,
expenditure_comment, transaction_status_code,
orig_transaction_reference, org_id,
denom_currency_code, denom_raw_cost,
attribute1, attribute2,
attribute3, attribute6,
attribute7, attribute8,
attribute9, attribute10,
person_business_group_id)
VALUES (r_interface.txn_interface_id(recno), l_gms_transaction_source,
r_interface.gms_batch_name(recno), r_interface.exp_end_date(recno),
r_interface.employee_number(recno), r_interface.exp_org_name(recno),
r_interface.gms_overriding_date(recno), r_interface.project_number(recno),
r_interface.task_number(recno), r_interface.expenditure_type(recno),
1, r_interface.summary_amount(recno),
r_interface.expenditure_comment(recno), 'P',
'E:' || r_interface.enc_summary_line_id(recno), r_interface.operating_unit(recno),
g_currency_code, r_interface.summary_amount(recno),
NULL, NULL,
r_interface.attribute3(recno), r_interface.attribute6(recno),
r_interface.attribute7(recno), r_interface.attribute8(recno),
r_interface.attribute9(recno), r_interface.attribute10(recno),
g_bg_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into pa_transaction_interface_all count: '||g_bulk_row_count);
SELECT expenditure_item_id
INTO l_expenditure_item_id
FROM psp_enc_summary_lines
WHERE enc_summary_line_id = r_interface.superceded_line_id(recno); --Bug 6062628
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into gms_transaction_interface_all by calling gms_transactions_pub.LOAD_GMS_XFACE_API');
SELECT period_name, end_date
INTO l_period_name, l_period_end_dt
FROM per_time_periods
WHERE time_period_id = enc_control_rec.time_period_id;
UPDATE psp_enc_summary_lines
SET gms_batch_name = g_gms_batch_name --- replaced with global for 3473294
WHERE status_code = 'N'
AND gl_code_combination_id is NULL
AND enc_control_id = enc_control_rec.enc_control_id;
--insert_into_psp_stout( 'fetched int cur in tr to gms int');
SELECT employee_number
INTO l_emp_num
FROM per_people_f
WHERE person_id = int_rec.person_id
AND int_rec.effective_date between effective_start_date and effective_end_date;
SELECT name --Removed substr,bug 2447912.
INTO l_org_name
FROM hr_all_organization_units
WHERE organization_id = int_rec.expenditure_organization_id;
SELECT org_id
INTO l_org_id
FROM pa_projects_all
WHERE project_id = int_rec.project_id;
SELECT segment1
INTO l_seg1
FROM pa_projects_all
WHERE project_id = int_rec.project_id;
SELECT task_number
INTO l_task_number
FROM pa_tasks
WHERE task_id = int_rec.task_id;
update psp_Enc_summary_lines set gms_posting_override_date=l_effective_date
where enc_summary_line_id=int_rec.enc_summary_line_id;
select pa_txn_interface_s.nextval
into l_txn_interface_id
from dual;
insert_into_pa_int(
l_txn_interface_id,
l_txn_source,
g_gms_batch_name, --- replaced with global for 3473294
l_exp_end_dt,
l_emp_num,
l_org_name,
--int_rec.effective_date,
l_effective_date,
l_seg1,
l_task_number,
int_rec.expenditure_type,
1,
int_rec.summary_amount,
l_exp_comment,
'P',
'E:' || int_rec.enc_summary_line_id,
l_org_id,
G_CURRENCY_CODE,
int_rec.summary_amount,
-----Award attr put into gms_txn int_rec.award_id,
-----Award attr put into gms_txn l_enc_type_id,
null,
null,
int_rec.attribute3,
int_rec.attribute6,
int_rec.attribute7,
int_rec.attribute8,
int_rec.attribute9,
int_rec.attribute10,
g_bg_id, -- Introduced for Bug 2935850
l_return_status);
select gms_batch_name
into gms_tie_tab(l_rec_no).r_gms_batch_name
from psp_enc_summary_lines
where enc_control_id = enc_control_rec.enc_control_id
and status_code = 'N'
and gl_code_combination_id is null
and rownum = 1;
/***** Modified teh following update to BULK UPDATE for R12 performance fixes (bug 4507892)
update psp_enc_controls
set gms_phase = 'Transfer'
where enc_control_id in (select distinct enc_control_id
from psp_enc_summary_lines
where gms_batch_name = g_gms_batch_name); --- replaced with global for 3473294
UPDATE psp_enc_controls
SET gms_phase = 'Transfer'
WHERE enc_control_id = r_enc_controls.enc_control_id(I);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''Transfer'' in psp_enc_controls count: '||g_bulk_row_count);
r_enc_controls.enc_control_id.DELETE;
END IF; --record_count > 0 ...move this line from below delete xface...for 2444657
p_action_type, -- Added for Restart Update Enh.
l_return_status);
UPDATE psp_enc_summary_lines pesl
SET (pesl.expenditure_id, pesl.expenditure_item_id, pesl.expenditure_ending_date,
pesl.txn_interface_id, pesl.interface_id) =
(SELECT ptxn.expenditure_id, ptxn.expenditure_item_id, ptxn.expenditure_ending_date,
ptxn.txn_interface_id, ptxn.interface_id
FROM pa_transaction_interface_all ptxn
WHERE ptxn.transaction_source = 'GOLDE'
AND ptxn.batch_name = r_gms_batch.gms_batch_name(recno)
AND ptxn.orig_transaction_reference = 'E:' || TO_CHAR(pesl.enc_summary_line_id))
WHERE pesl.gms_batch_name = r_gms_batch.gms_batch_name(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_summary_lines setting expenditure_item_id count'||g_bulk_row_count);
DELETE pa_transaction_interface_all
WHERE batch_name = r_gms_batch.gms_batch_name(recno)
AND transaction_source = 'GOLDE';
DELETE gms_transaction_interface_all
WHERE batch_name = r_gms_batch.gms_batch_name(recno)
AND transaction_source = 'GOLDE';
p_action_type IN VARCHAR2, -- Added for Restart Update Enh.
p_return_status OUT NOCOPY VARCHAR2) IS
CURSOR gms_tie_back_success_cur IS
SELECT enc_control_id,
enc_summary_line_id,
dr_cr_flag,
TO_NUMBER(DECODE(dr_cr_flag, 'C', -summary_amount, summary_amount)) summary_amount
FROM psp_enc_summary_lines
WHERE gms_batch_name = p_gms_batch_name;
SELECT nvl(transaction_rejection_code,'P'),
TO_NUMBER(SUBSTR(orig_transaction_reference, 3)), -- Introuduced TO_NUMBER and SUBSTR for bug fix 4625734
transaction_status_code
FROM pa_transaction_interface_all
WHERE transaction_source = 'GOLDE'
AND batch_name = p_gms_batch_name;
AND orig_transaction_reference IN (SELECT 'E:' || enc_summary_line_id -- Introduced for bug fix 3953230
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_control_id = p_enc_control_id);
SELECT pel.rowid,
pel.effective_date,
-- pel.attribute30
pel.suspense_org_account_id,
pel.superceded_line_id
FROM psp_enc_summary_lines pel
WHERE pel.enc_summary_line_id = p_enc_line_id
and pel.enc_control_id=p_enc_control_id
and pel.gl_project_flag='P' and
pel.status_code='N';
SELECT hou.organization_id, hou.name
FROM hr_all_organization_units hou, psp_organization_accounts poa
WHERE hou.organization_id = poa.organization_id
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND poa.organization_account_id = p_org_id;
SELECT hou.organization_id, hou.name
FROM hr_all_organization_units hou,
per_assignments_f paf,
psp_enc_summary_lines pel
WHERE pel.enc_summary_line_id = p_line_id
AND pel.enc_control_id=p_enc_control_id
-- AND pel.assignment_id = paf.assignment_id
and pel.person_id=paf.person_id
and paf.primary_flag='Y'
AND pel.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id
AND pel.effective_date between
hou.date_from and nvl(hou.date_to,pel.effective_date);
SELECT hou.name,
hou.organization_id,
pel.rowid,
pel.assignment_id,
pel.effective_date,
pel.attribute30
FROM hr_all_organization_units hou,
per_assignments_f paf,
psp_enc_summary_lines pel
WHERE pel.enc_control_id = p_enc_control_id
AND pel.gl_project_flag = 'P'
AND pel.status_code = 'A'
AND pel.assignment_id = paf.assignment_id(+)
AND pel.business_group_id = p_business_group_id
AND pel.set_of_books_id = p_set_of_books_id
AND pel.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id;
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.award_id,
poa.task_id,
poa.expenditure_type,
poa.expenditure_organization_id
FROM psp_organization_accounts poa
WHERE poa.organization_id = p_organization_id
AND poa.account_type_code = 'S'
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND p_encumbrance_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_encumbrance_date);
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.award_id,
poa.task_id,
poa.expenditure_type,
poa.expenditure_organization_id
FROM psp_organization_accounts poa
WHERE
/ * poa.account_type_code = 'G'
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND p_encumbrance_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_encumbrance_date); Bug 2056877 * /
SELECT COUNT(1)
FROM pa_transaction_interface_all
WHERE transaction_source = 'GOLDE'
AND batch_name = p_gms_batch_name
AND transaction_status_code in ('R', 'PI', 'PO', 'PR');
SELECT enc_control_id,
enc_summary_line_id,
dr_cr_flag,
TO_NUMBER(DECODE(dr_cr_flag, 'C', -summary_amount, summary_amount)) summary_amount
FROM psp_enc_summary_lines
WHERE gms_batch_name = p_gms_batch_name
AND status_code = 'L';
SELECT superceded_line_id
FROM psp_enc_summary_lines
WHERE enc_summary_line_id in
(
select to_number(substr(ORIG_TRANSACTION_REFERENCE,3))
from pa_transaction_interface_all
WHERE batch_name = p_gms_batch_name
AND transaction_status_code = 'P'
)
AND gms_batch_name = p_gms_batch_name;
SELECT DISTINCT enc_control_id
FROM psp_enc_summary_lines
WHERE enc_summary_line_id in
(
select to_number(substr(ORIG_TRANSACTION_REFERENCE,3))
from pa_transaction_interface_all
WHERE batch_name = p_gms_batch_name
AND transaction_status_code = 'P'
)
AND gms_batch_name = p_gms_batch_name;
SELECT COUNT(*),
transaction_status_code
FROM pa_transaction_interface_all
WHERE transaction_source='GOLDE'
AND batch_name = p_gms_batch_name
AND transaction_Status_code in ('P','I')
GROUP BY transaction_status_code;
select count(*), transaction_status_code
into l_cnt, l_status
from pa_transaction_interface_all
where transaction_source = 'GOLDE'
and batch_name = (select distinct gms_batch_name
from psp_enc_summary_lines
where enc_control_id = p_enc_control_id
and gms_batch_name is not null)
and transaction_status_code in ('P', 'I')
group by transaction_status_code ;
select count(*), transaction_status_code into l_cnt,l_status from
pa_transaction_interface_all where transaction_source='GOLDE' and
batch_name=p_gms_batch_name and transaction_Status_code in ('P','I')
group by transaction_status_code;
delete from pa_transaction_interface_all
where transaction_source = 'GOLDE'
and batch_name = p_gms_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from pa_trancsaction_interface_all sql%rowcount'||sql%rowcount);
delete from gms_transaction_interface_all
where transaction_source = 'GOLDE'
and batch_name = p_gms_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all sql%rowcount'||sql%rowcount);
/***** Converted the following UPDATE to BULK for R12 performance fixes (bug 4507892)
update psp_enc_summary_lines set status_code ='A' where
enc_summary_line_id in (select superceded_line_id from
psp_enc_summary_lines where gms_batch_name=p_gms_batch_name);
UPDATE psp_enc_summary_lines
SET status_code = 'A'
WHERE enc_summary_line_id = r_superceded_lines.superceded_line_id(I);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated original lines to status_code ''A'' in psp_enc_summary_lines g_bulk_row_count'||g_bulk_row_count);
r_superceded_lines.superceded_line_id.DELETE;
delete from psp_enc_summary_lines
where enc_summary_line_id in
(
select to_number(substr(ORIG_TRANSACTION_REFERENCE,3))
from pa_transaction_interface_all
WHERE batch_name = p_gms_batch_name
AND transaction_status_code = 'P'
)
AND gms_batch_name = p_gms_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines sql%rowcount'||sql%rowcount);
UPDATE psp_enc_controls pec
SET gms_phase = 'TieBack'
WHERE enc_control_id = r_enc_controls.enc_control_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''TieBack'' in psp_enc_controls count: '||g_bulk_row_count);
r_enc_controls.enc_control_id.DELETE;
delete from pa_transaction_interface_all
where transaction_source = 'GOLDE'
and batch_name = p_gms_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from pa_trancsaction_interface_all sql%rowcount'||sql%rowcount);
delete from gms_transaction_interface_all
where transaction_source = 'GOLDE'
and batch_name = p_gms_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all sql%rowcount'||sql%rowcount);
/***** Changed the following SELECT into CURSOR for bug fix 4625734
SELECT count(*)
INTO l_cnt_gms_interface
FROM pa_transaction_interface_all
WHERE transaction_source = 'GOLDE'
AND batch_name = p_gms_batch_name
AND transaction_status_code in ('R', 'PI', 'PO', 'PR');
UPDATE psp_enc_summary_lines
SET interface_status = r_reject_recs.reason_code(recno)
WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
AND r_reject_recs.txn_status_code(recno) IN ('R', 'PI', 'PO', 'PR');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated interface_status with reject reason code in psp_enc_summary_lines count '||g_bulk_row_count);
UPDATE psp_enc_summary_lines
SET interface_status = r_reject_recs.reason_code(recno),
status_code = 'L'
WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
AND r_reject_recs.txn_status_code(recno) = 'A';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''L'' for accepted records in psp_enc_summary_lines count:'||g_bulk_row_count);
UPDATE psp_enc_lines_history
SET change_flag = 'L'
WHERE enc_summary_line_id IN (SELECT superceded_line_id
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno))
AND r_reject_recs.txn_status_code(recno) = 'A';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated change_flag to ''L'' in psp_enc_lines_history for employee level encumbrance liquidation count'||g_bulk_row_count);
UPDATE psp_enc_controls pec
SET gms_phase = 'TieBack',
summ_ogm_dr_amount = NVL(pec.summ_ogm_dr_amount, 0) + DECODE(r_success_recs.dr_cr_flag(recno), 'D', r_success_recs.summary_amount(recno), 0),
summ_ogm_cr_amount = NVL(pec.summ_ogm_cr_amount, 0) + DECODE(r_success_recs.dr_cr_flag(recno), 'C', r_success_recs.summary_amount(recno), 0)
WHERE enc_control_id = r_success_recs.enc_control_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update summ_ogm_cr_amount, summ_ogm_dr_amount in psp_enc_controls count'||g_bulk_row_count);
r_reject_recs.enc_summary_line_id.DELETE;
r_reject_recs.enc_control_id.DELETE;
r_reject_recs.reason_code.DELETE;
r_reject_recs.txn_status_code.DELETE;
r_success_recs.enc_summary_line_id.DELETE;
r_success_recs.enc_control_id.DELETE;
r_success_recs.reason_code.DELETE;
r_success_recs.txn_status_code.DELETE;
UPDATE psp_enc_summary_lines
SET interface_status = l_trx_reject_code
-- , status_code = 'R'
WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
UPDATE psp_enc_summary_lines
SET interface_status = l_trx_reject_code, status_code = 'L'
WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
update psp_enc_lines_history
set change_flag = 'L'
where enc_summary_line_id = ( select superceded_line_id
from psp_enc_summary_lines
where enc_summary_line_id = to_number(l_orig_trx_reference));
For Bug 2290051 : Interface Lines shall not be deleted for accepeted summary lines
DELETE from pa_transaction_interface_all
where transaction_source = 'GOLDE'
and batch_name = p_gms_batch_name
and transaction_status_code = 'A'
and orig_transaction_reference = l_enc_ref;
DELETE from gms_transaction_interface_all
where batch_name = p_gms_batch_name
and transaction_status_code = 'A'
and orig_transaction_reference = l_enc_ref;
SELECT summary_amount, dr_cr_flag
INTO l_summary_amount, l_dr_cr_flag
FROM psp_enc_summary_lines
WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
UPDATE psp_enc_summary_lines
-- SET status_code = 'P'
set status_code='L'
WHERE rowid = l_rowid;
UPDATE psp_enc_summary_lines
SET reject_reason_code = 'EL:' || l_trx_reject_code,
-- status_code = 'A'
status_code = 'R'
WHERE rowid = l_rowid;
update psp_enc_summary_lines set status_code='A' where
enc_summary_line_id in (select superceded_line_id from
psp_Enc_summary_lines where rowid=l_rowid);
/ * Added for Restart Update/Quick Update Encumbrance Lines Enh. * /
IF l_return_value <> 'PROFILE_VAL_DATE_MATCHES' THEN
IF p_action_type IN ('Q','U') THEN
UPDATE psp_enc_controls
SET gms_phase='INVALID_SUSPENSE'
WHERE enc_control_id=p_enc_control_id;
/ * Added for Restart Update/Quick Update Encumbrance Lines * /
IF p_action_type IN ('Q','U') THEN
UPDATE psp_enc_controls
SET gms_phase = 'INVALID_SUSPENSE'
WHERE enc_control_id = p_enc_control_id;
UPDATE psp_enc_summary_lines
SET suspense_org_account_id = l_organization_account_id,
reject_reason_code ='EL:'||l_trx_status_code,
gl_project_flag = l_gl_project_flag,
gl_code_combination_id = decode(l_gl_project_flag, 'P', null, l_gl_code_combination_id ),
project_id = decode(l_gl_project_flag, 'P', l_project_id, null),
expenditure_organization_id = decode(l_gl_project_flag, 'P', l_exp_org_id, null),
expenditure_type = decode(l_gl_project_flag, 'P', l_expenditure_type, null),
task_id = decode(l_gl_project_flag, 'P', l_task_id, null),
award_id = decode(l_gl_project_flag, 'P', l_award_id, null),
-- status_code = 'A'
status_code='N'
WHERE rowid = l_rowid;
update psp_Enc_summary_lines set ----- dr_cr_flag='C',
summary_amount=decode(dr_cr_flag,'C', -summary_amount,summary_amount)
where rowid=l_rowid;
UPDATE psp_enc_summary_lines
SET attribute30 = l_organization_account_id,
reject_reason_code = 'EL:' || l_trx_reject_code,
gl_project_flag = l_gl_project_flag,
effective_date = l_encumbrance_date,
status_code = 'A'
WHERE rowid = l_rowid;
UPDATE psp_enc_controls
SET summ_ogm_cr_amount = nvl(summ_ogm_cr_amount, 0) + l_cr_summary_amount,
summ_ogm_dr_amount = nvl(summ_ogm_dr_amount, 0) + l_dr_summary_amount,
gms_phase = 'TieBack'
WHERE enc_control_id = p_enc_control_id;
/ * Added the below IF condition for Restart Update/Quick Update Encumbrance Lines Enh. * /
IF p_action_type IN ('Q','U') THEN
UPDATE psp_enc_controls
SET gms_phase = 'INVALID_SUSPENSE'
WHERE enc_control_id = p_enc_control_id;
/* Commented for Restart Update/Quick Update Encumbrance Lines
because global suspense function introduced,this situation is handled there.
IF l_suspense_ac_not_found = 'Y' THEN
fnd_message.set_name('PSP','PSP_LD_SUSPENSE_AC_NOT_EXIST');
UPDATE psp_enc_summary_lines
SET status_code = 'L'
WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''L'' in psp_enc_summary_lines '||g_bulk_row_count);
UPDATE psp_enc_lines_history
SET change_flag = 'L'
WHERE enc_summary_line_id IN (SELECT superceded_line_id
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_summary_line_id = r_success_recs.enc_summary_line_id(recno));
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated change_flag to ''L'' in psp_enc_lines_history for employee level liquidation count'||g_bulk_row_count);
UPDATE psp_enc_controls pec
SET gms_phase = 'TieBack',
summ_ogm_dr_amount = NVL(pec.summ_ogm_dr_amount, 0) + DECODE(r_success_recs.dr_cr_flag(recno), 'D', r_success_recs.summary_amount(recno), 0),
summ_ogm_cr_amount = NVL(pec.summ_ogm_cr_amount, 0) + DECODE(r_success_recs.dr_cr_flag(recno), 'C', r_success_recs.summary_amount(recno), 0)
WHERE enc_control_id = r_success_recs.enc_control_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated summ_ogm_cr_amount, summ_ogm_dr_amount, gms_phase in psp_enc_controls count: '||g_bulk_row_count);
r_success_recs.enc_summary_line_id.DELETE;
r_success_recs.enc_control_id.DELETE;
r_success_recs.reason_code.DELETE;
r_success_recs.txn_status_code.DELETE;
UPDATE psp_enc_summary_lines
SET status_code = 'L'
WHERE enc_summary_line_id = l_enc_summary_line_id
and status_code = 'N';
update psp_enc_lines_history
set change_flag = 'L'
where enc_summary_line_id = ( select superceded_line_id
from psp_enc_summary_lines
where enc_summary_line_id = l_enc_summary_line_id);
For Bug 2290051 : Interface Lines shall not be deleted for accepeted summary lines
DELETE from pa_transaction_interface_all
where transaction_source = 'GOLDE'
and batch_name = p_gms_batch_name
and transaction_status_code = 'A'
and orig_transaction_reference = 'E:' || to_char(l_enc_summary_line_id);
DELETE from gms_transaction_interface_all
where transaction_source = 'GOLDE'
and batch_name = p_gms_batch_name
and transaction_status_code = 'A'
and orig_transaction_reference = 'E:' || to_char(l_enc_summary_line_id);
UPDATE psp_enc_summary_lines
SET status_code = 'P'
where enc_control_id = p_enc_control_id
and gl_project_flag = 'P'
and gms_batch_name=p_gms_batch_name
and status_code = 'A';
UPDATE psp_enc_controls
SET summ_ogm_cr_amount = l_cr_summary_amount,
summ_ogm_dr_amount = l_dr_summary_amount,
gms_phase = 'TieBack'
WHERE enc_control_id = p_enc_control_id;
select 1 into l_count from
per_all_assignments_f ainner,
per_assignment_status_types binner
where ainner.person_id=p_person_id
and ainner.primary_flag='Y'
and ainner.assignment_status_type_id=binner.assignment_status_type_id
and binner.per_system_status='ACTIVE_ASSIGN'
and p_effective_date between ainner.effective_start_date and ainner.effective_end_date;
select max(a.effective_end_date) into l_effective_date
from per_all_assignments_f a, per_assignment_status_types b
where a.person_id=p_person_id
and a.primary_flag='Y'
and a.assignment_status_type_id=b.assignment_status_type_id
and b.per_system_status='ACTIVE_ASSIGN' and
(trunc(a.effective_end_date) <= trunc(p_effective_date));
PROCEDURE insert_into_psp_stout(
P_MSG IN VARCHAR2) IS
l_msg_id number(9);
SELECT PSP_STOUT_S.NEXTVAL
INTO l_msg_id
FROM DUAL;
INSERT INTO PSP_STOUT(
MSG_ID,
MSG)
VALUES(
l_msg_id,
P_MSG);
END insert_into_psp_stout;
Purpose : 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.
Known limitations, enhancements or remarks
Change History
Who When What
spchakra 10-Jan-2002 Created
**************************************************************/
/* Commented the below procedure for Restart Update/Quick Update Encumbrance Lines Enhancement
PROCEDURE move_qkupd_rec_to_hist
(p_payroll_id IN NUMBER,
p_action_type IN VARCHAR2,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2)
IS
no_of_asg NUMBER DEFAULT 0;
SELECT DISTINCT peca.assignment_id,
peca.change_type
FROM psp_enc_changed_assignments peca
WHERE peca.payroll_id = p_payroll_id;
SELECT COUNT(DISTINCT peca.assignment_id)
FROM psp_enc_changed_assignments peca
WHERE peca.payroll_id = p_payroll_id;
INSERT INTO psp_enc_changed_asg_history
(assignment_id,
payroll_id,
change_type,
processing_module,
created_by,
creation_date,
processed_flag)
VALUES (get_rec_to_move_rec.assignment_id,
p_payroll_id,
get_rec_to_move_rec.change_type,
p_action_type,
fnd_global.user_id,
sysdate,
NULL);
DELETE FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = get_rec_to_move_rec.assignment_id
AND peca.change_type = get_rec_to_move_rec.change_type
AND peca.payroll_id = p_payroll_id;
fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES','MOVE_QKUPD_REC_TO_HIST');
/***** Commented for Create and Update multi thread enh.
Procedure liquidate_emp_term(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_business_group_id in number,
p_set_of_books_id in number,
p_person_id in number,
p_actual_term_date in date) is
---- there is no index on person/assignment on psp_enc_summary_lines
--- psp_enc_lines_history has index on assignment
cursor get_enc_hist_lines_cur is
select ESL.payroll_id payroll_id,
min(ESL.time_period_id) time_period_id
from psp_enc_lines_history ELH,
psp_enc_summary_lines ESL,
per_all_assignments_f ASG
where ASG.person_id = p_person_id
and ASG.assignment_id = ELH.assignment_id
and ELH.enc_summary_line_id = ESL.enc_summary_line_id
and ESL.status_code = 'A'
and ESL.effective_date > p_actual_term_date
and p_actual_term_date between ASG.effective_start_date and ASG.effective_end_date
group by ESL.payroll_id;
select CTRL.payroll_id, min(CTRL.time_period_id) time_period_id
from psp_enc_controls CTRL
where CTRL.action_code = 'IT'
and (CTRL.payroll_id, CTRL.time_period_id) in
(select ASG.payroll_id, min(PER.time_period_id)
from per_all_assignments_f ASG,
per_time_periods PER
where ASG.payroll_id = PER.payroll_id
and ASG.person_id = p_person_id
and p_Actual_term_date between PER.start_date and PER.end_date
group by ASG.payroll_id)
group by CTRL.payroll_id;
SELECT pec.liquidate_request_id,
pec.payroll_id,
MIN(pec.time_period_id),
MIN(enc_control_id)
FROM psp_enc_controls pec
WHERE pec.action_code = 'IT'
GROUP BY pec.liquidate_request_id, pec.payroll_id;
SELECT TO_NUMBER(argument3),
fnd_date.canonical_to_date(fnd_date.date_to_canonical(argument4))
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = l_request_id;
SELECT DISTINCT enc_control_id
FROM psp_enc_lines
WHERE person_id = p_person_id
AND encumbrance_date > p_actual_term_date;
SELECT full_name
FROM per_all_people_f
WHERE person_id = p_person_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
UPDATE psp_enc_controls
SET number_of_dr = (SELECT number_of_dr - COUNT(1)
FROM psp_enc_lines
WHERE enc_control_id = t_enc_control_id(recno)
AND dr_cr_flag = 'D'
AND person_id = p_person_id
AND encumbrance_date > p_actual_term_date),
number_of_cr = (SELECT number_of_cr - COUNT(1)
FROM psp_enc_lines
WHERE enc_control_id = t_enc_control_id(recno)
AND dr_cr_flag = 'C'
AND person_id = p_person_id
AND encumbrance_date > p_actual_term_date),
total_dr_amount = (SELECT total_dr_amount - SUM(encumbrance_amount)
FROM psp_enc_lines
WHERE enc_control_id = t_enc_control_id(recno)
AND dr_cr_flag = 'D'
AND person_id = p_person_id
AND encumbrance_date > p_actual_term_date),
total_cr_amount = (SELECT total_cr_amount - SUM(encumbrance_amount)
FROM psp_enc_lines
WHERE enc_control_id = t_enc_control_id(recno)
AND dr_cr_flag = 'C'
AND person_id = p_person_id
AND encumbrance_date > p_actual_term_date),
gl_dr_amount = (SELECT gl_dr_amount - SUM(encumbrance_amount)
FROM psp_enc_lines
WHERE enc_control_id = t_enc_control_id(recno)
AND dr_cr_flag = 'D'
AND gl_project_flag = 'G'
AND person_id = p_person_id
AND encumbrance_date > p_actual_term_date),
gl_cr_amount = (SELECT gl_cr_amount - SUM(encumbrance_amount)
FROM psp_enc_lines
WHERE enc_control_id = t_enc_control_id(recno)
AND dr_cr_flag = 'C'
AND gl_project_flag = 'G'
AND person_id = p_person_id
AND encumbrance_date > p_actual_term_date),
ogm_dr_amount = (SELECT ogm_dr_amount - SUM(encumbrance_amount)
FROM psp_enc_lines
WHERE enc_control_id = t_enc_control_id(recno)
AND dr_cr_flag = 'D'
AND gl_project_flag = 'P'
AND person_id = p_person_id
AND encumbrance_date > p_actual_term_date),
ogm_cr_amount = (SELECT ogm_cr_amount - SUM(encumbrance_amount)
FROM psp_enc_lines
WHERE enc_control_id = t_enc_control_id(recno)
AND dr_cr_flag = 'C'
AND gl_project_flag = 'P'
AND person_id = p_person_id
AND encumbrance_date > p_actual_term_date)
WHERE enc_control_id = t_enc_control_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated number_of_dr, number_of_cr, total_dr_amount, total_cr_amount, gl_dr_amount, gl_cr_amount, ogm_dr_amount, ogm_cr_cmount in psp_enc_controls');
DELETE psp_enc_lines
WHERE person_id = p_person_id
AND encumbrance_date > p_actual_term_date;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted in psp_enc_lines');
End of comment for create and Update multi thread enh. *****/
END psp_enc_liq_tran;