[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_insert_str varchar2(5000); -- Introduced for bug fix 3233373
PROCEDURE update_hierarchy_dates (p_payroll_action_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2);
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_updated_login NUMBER;
SELECT pcv_information7 suspense_account
FROM pqp_configuration_values
WHERE pcv_information_category = 'PSP_ENABLE_AUTOPOPULATION'
AND legislation_code IS NULL
AND NVL(business_group_id, p_business_group_id) = p_business_group_id
ORDER BY business_group_id;
SELECT COUNT(1)
FROM psp_enc_processes
WHERE payroll_action_id = p_payroll_action_id
AND process_phase = 'liquidate';
SELECT DISTINCT assignment_id,
payroll_id
FROM psp_enc_summary_lines pesl
WHERE payroll_action_id = p_payroll_action_id
AND superceded_line_id IS NULL
AND status_code = 'N';
SELECT peed.period_end_date
FROM psp_enc_end_dates peed
WHERE peed.business_group_id = p_business_group_id
AND peed.set_of_books_id = p_set_of_books_id
AND peed.default_org_flag = 'Y';
SELECT COUNT(1)
FROM psp_enc_summary_lines
WHERE payroll_action_id = p_payroll_action_id
AND superceded_line_id IS NULL
AND gl_code_combination_id IS NOT NULL
AND status_code = 'N';
SELECT COUNT(1)
FROM psp_enc_summary_lines
WHERE payroll_action_id = p_payroll_action_id
AND superceded_line_id IS NULL
AND award_id IS NOT NULL
AND status_code = 'N';
l_last_update_date := SYSDATE;
l_last_updated_by := NVL(FND_GLOBAL.USER_ID, -1);
l_last_updated_login := NVL(FND_GLOBAL.LOGIN_ID, -1);
INSERT INTO psp_enc_processes
(request_id, process_code, payroll_action_id,
process_status, process_phase, business_group_id,
set_of_books_id, creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
SELECT l_request_id, 'ST', p_payroll_action_id,
'I', process_phase, p_business_group_id,
p_set_of_books_id, l_last_update_date, l_last_updated_by,
l_last_update_date, l_last_updated_by, l_last_updated_login
FROM psp_enc_processes pep
WHERE process_code IN ('CEL', 'LET')
AND payroll_action_id = p_payroll_action_id
AND NOT EXISTS (SELECT 1
FROM psp_enc_processes pep2
WHERE pep2.payroll_action_id = p_payroll_action_id
AND pep2.process_code = 'ST');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted row for process_code ST');
UPDATE psp_enc_processes
SET process_status = 'S',
process_phase = 'completed'
WHERE payroll_action_id = p_payroll_action_id
AND process_code IN ('CEL', 'LET');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated original and current process status in psp_enc_processes');
DELETE psp_enc_summary_lines
WHERE payroll_action_id = p_payroll_action_id
AND superceded_line_id IS NULL
AND status_code = 'N';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted un-imported summary lines (' || SQL%ROWCOUNT || ')');
UPDATE psp_enc_lines
SET suspense_org_account_id = NULL,
gl_code_combination_id = orig_gl_code_combination_id,
project_id = orig_project_id,
task_id = orig_task_id,
award_id = orig_award_id,
expenditure_organization_id = orig_expenditure_org_id,
expenditure_type = orig_expenditure_type,
gl_project_flag = decode(orig_gl_code_combination_id,NULL,'P','G'),
encumbrance_date = prev_effective_date
WHERE payroll_action_id = p_payroll_action_id
AND suspense_reason_code like 'ES:%';
UPDATE psp_enc_lines
SET orig_gl_code_combination_id = NULL,
orig_project_id = NULL,
orig_task_id = NULL,
orig_award_id = NULL,
orig_expenditure_org_id = NULL,
orig_expenditure_type = NULL,
suspense_reason_code = NULL
WHERE suspense_reason_code like 'ES:%'
AND payroll_action_id = p_payroll_action_id;
SELECT enc_control_id,
payroll_id,
time_period_id
FROM psp_enc_controls
WHERE payroll_action_id = p_payroll_action_id
-- 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 Quick Upd. Enh. 2143723
AND action_code in ('N','I') --- 2444657
AND business_group_id = g_business_group_id
AND set_of_books_id = g_set_of_books_id;
SELECT psp_st_run_id_s.nextval INTO g_run_id FROM dual;
UPDATE psp_enc_controls
SET action_code = 'I',
run_id = g_run_id
WHERE enc_control_id = enc_control_rec.enc_control_id
AND time_period_id = enc_control_rec.time_period_id;
SELECT COUNT(1)
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.status_code = 'N';
SELECT COUNT(1)
FROM psp_enc_lines
WHERE payroll_action_id = p_payroll_action_id
AND enc_start_date <= g_def_end_date;
update_hierarchy_dates(p_payroll_action_id => p_payroll_action_id,
p_return_status => l_return_status);
DELETE psp_enc_lines
WHERE payroll_action_id = p_payroll_action_id;
UPDATE psp_enc_controls pec
SET action_code = '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.enc_control_id = pec.enc_control_id
AND pesl.status_code = 'N');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated action_code to ''P'' count'||SQL%ROWCOUNT);
UPDATE psp_enc_processes
SET process_status = 'P', process_phase = 'completed'
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_lines pel
WHERE pel.payroll_action_id = p_payroll_action_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to summarize and transfer re-processing');
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 for liquidation re-processing');
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) || ' Updated assignment_status to processed for summarized and transferred assignments count: '||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) || ' Updated assignment_status to summarize and transfer for assignments to be summarized and transferred count: '||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) || ' Updated assignment_status to liquidate for assignments to be liquidated count: '||SQL%ROWCOUNT);
/***** Commented for Create and Update multi thread enh.
PROCEDURE enc_batch_end(p_payroll_action_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR enc_control_cur IS
SELECT enc_control_id,
payroll_id,
time_period_id,
gl_phase,
gms_phase
FROM psp_enc_controls
WHERE payroll_action_id = p_payroll_action_id
-- 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 Quick Upd. Enh. 2143723.
AND action_code = 'I'
AND run_id = nvl(g_run_id, run_id)
AND business_group_id = g_business_group_id
AND set_of_books_id = g_set_of_books_id;
SELECT enc_summary_line_id
FROM psp_enc_summary_lines
WHERE enc_control_id = p_enc_control_id
AND status_code = 'N';
SELECT count(*)
FROM psp_enc_lines pel
-- ,psp_enc_controls pec
WHERE pel.enc_control_id= enc_control_rec.enc_control_id
AND pel.time_period_id = enc_control_rec.time_period_id
AND pel.encumbrance_amount<>0
AND pel.business_group_id = g_business_group_id
AND pel.set_of_books_id = g_set_of_books_id;
update psp_enc_controls
set action_code = 'I'
where enc_control_id = enc_control_rec.enc_control_id; * / --- commented for 2479579
UPDATE psp_enc_summary_lines
SET status_code = 'R'
WHERE enc_control_id = enc_control_rec.enc_control_id
AND status_code = 'N';
SELECT nvl(sum(encumbrance_amount),0)
INTO l_o_enc_sum
FROM psp_enc_lines pel
-- ,psp_enc_controls pec
WHERE pel.enc_control_id = enc_control_rec.enc_control_id
AND pel.time_period_id = enc_control_rec.time_period_id
AND pel.business_group_id = g_business_group_id
AND pel.set_of_books_id = g_set_of_books_id; * /
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 = 'N',
run_id = NULL
WHERE enc_control_id = enc_control_rec.enc_control_id;
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 = 'N',
run_id = NULL
WHERE enc_control_id = enc_control_rec.enc_control_id;
UPDATE psp_enc_processes
SET process_status = 'P', process_phase = 'completed'
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.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 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 and transfer re-processing');
UPDATE psp_enc_processes
SET process_phase = 'liquidate'
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.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 for liqduidation re-processing');
End of comment for Create and Update multi thread enh. *****/
-- ##########################################################################
-- This procedure summarizes all the lines from psp_enc_lines
-- and inserts the summarized lines into psp_enc_summary_lines
-- Depending on the setup options, this procedure groups transactions from
-- PSP_ENC_LINES and inserts the summarized lines into PSP_ENC_SUMMARY_LINES
-- There are two setup options in PSP_ENC_SETUP_OPTIONS table called 'TIME_BASED_SUMM'
-- and 'CI_BASED_SUMM_GL' on which the procedure depends.
-- If the 'TIME_BASED_SUMM' = 1 and 'CI_BASED_SUMM_GL' = 3
-- then the summarization is done upto the assignment level for each time period
-- If the 'TIME_BASED_SUMM' = 1 and 'CI_BASED_SUMM_GL' = 2
-- then the summarization is done upto the employee level for each time period
-- If the 'TIME_BASED_SUMM' = 1 and 'CI_BASED_SUMM_GL' = 1
-- then the summarization is done upto the code combination level for each time period
-- If the 'TIME_BASED_SUMM' = 2 and 'CI_BASED_SUMM_GL' = 3
-- then the summarization is done upto the assignment level for each GL period
-- If the 'TIME_BASED_SUMM' = 2 and 'CI_BASED_SUMM_GL' = 2
-- then the summarization is done upto the employee level for each GL period
-- If the 'TIME_BASED_SUMM' = 2 and 'CI_BASED_SUMM_GL' = 1
-- then the summarization is done upto the code combination level for each GL period
-- ##########################################################################
/***** Commented for Create and Update multi thread enh.
PROCEDURE create_gl_enc_sum_lines(p_payroll_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR enc_control_cur IS
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 Quick Upd. Enh. 2143723.
AND action_code = 'I'
AND run_id = g_run_id
AND business_group_id = g_business_group_id
AND set_of_books_id = g_set_of_books_id
AND (gl_phase is null or gl_phase = 'TieBack'); --- 2444657
SELECT
-- ptp.end_date eff_dt, bug fix 1971612
pel.encumbrance_date eff_dt,
pel.dr_cr_flag,
sum(pel.encumbrance_amount) sum_amt,
pel.gl_project_flag,
pel.person_id,
pel.assignment_id,
-- pel.gl_code_combination_id gl_ccid Commented out for Bug 3194807
-- sob.set_of_books_id,
/ * Uncommented the decode statement for Bug 3194807 * /
decode(suspense_org_account_id, null, pel.gl_code_combination_id, pos.gl_code_combination_id) gl_ccid,
-- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute_category, pos.attribute_category), NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute1, pos.attribute1), NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute2, pos.attribute2), NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute3, pos.attribute3), NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute4, pos.attribute4), NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute5, pos.attribute5), NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute6, pos.attribute6), NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute7, pos.attribute7), NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute8, pos.attribute8), NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute9, pos.attribute9), NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute10, pos.attribute10), NULL) attribute10
FROM PSP_ENC_LINES PEL,
PER_TIME_PERIODS PTP,
-- ,GL_SETS_OF_BOOKS SOB,
-- GL_CODE_COMBINATIONS GCC,
PSP_ORGANIZATION_ACCOUNTS POS
WHERE PEL.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
-- AND PEL.ENCUMBRANCE_DATE BETWEEN PTP.START_DATE AND PTP.END_DATE
-- Commented out for bug fix 1971612
AND PEL.GL_PROJECT_FLAG = 'G'
AND PEL.ENCUMBRANCE_AMOUNT <> 0
AND PEL.ENC_CONTROL_ID = p_enc_control_id
-- AND pel.gl_code_combination_id = gcc.code_combination_id
-- AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
AND pel.suspense_org_account_id = pos.organization_account_id(+)
GROUP BY
-- ptp.end_date, -- for bug fix 1971612
pel.encumbrance_date,
pel.person_id,
pel.assignment_id,
pel.dr_cr_flag,
pel.gl_project_flag,
-- pel.gl_code_combination_id; Commented out for Bug 3194807
SELECT
--- ptp.end_date eff_dt, for bug fix 1971612
pel.encumbrance_date eff_dt,
pel.dr_cr_flag,
sum(pel.encumbrance_amount) sum_amt,
pel.gl_project_flag,
pel.person_id,
pel.gl_code_combination_id gl_ccid
-- sob.set_of_books_id,
-- decode(suspense_org_account_id, null, pel.gl_code_combination_id, pos.gl_code_combination_id) gl_ccid
FROM PSP_ENC_LINES PEL,
PER_TIME_PERIODS PTP
-- ,GL_SETS_OF_BOOKS SOB,
-- GL_CODE_COMBINATIONS GCC,
-- PSP_ORGANIZATION_ACCOUNTS POS
WHERE PEL.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
-- AND PEL.ENCUMBRANCE_DATE BETWEEN PTP.START_DATE AND PTP.END_DATE
-- Commented out for bug fix 1971612
AND PEL.GL_PROJECT_FLAG = 'G'
AND PEL.ENCUMBRANCE_AMOUNT <> 0
AND PEL.ENC_CONTROL_ID = p_enc_control_id
-- AND pel.gl_code_combination_id = gcc.code_combination_id
-- AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
-- AND pel.suspense_org_account_id = pos.organization_account_id(+)
GROUP BY
-- ptp.end_date, for bug fix 1971612
pel.encumbrance_date,
pel.person_id,
pel.dr_cr_flag,
pel.gl_project_flag,
pel.gl_code_combination_id;
SELECT
-- ptp.end_date eff_dt, for bug fix 1971612
pel.encumbrance_date eff_dt,
pel.dr_cr_flag,
sum(pel.encumbrance_amount) sum_amt,
pel.gl_project_flag,
pel.gl_code_combination_id gl_ccid
-- sob.set_of_books_id,
-- decode(suspense_org_account_id, null, pel.gl_code_combination_id, pos.gl_code_combination_id) gl_ccid
FROM PSP_ENC_LINES PEL,
PER_TIME_PERIODS PTP
-- ,GL_SETS_OF_BOOKS SOB,
-- GL_CODE_COMBINATIONS GCC,
-- PSP_ORGANIZATION_ACCOUNTS POS
WHERE PEL.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
-- AND PEL.ENCUMBRANCE_DATE BETWEEN PTP.START_DATE AND PTP.END_DATE
-- Commented out for bug fix 1971612
AND PEL.GL_PROJECT_FLAG = 'G'
AND PEL.ENCUMBRANCE_AMOUNT <> 0
AND PEL.ENC_CONTROL_ID = p_enc_control_id
-- AND pel.suspense_org_account_id = pos.organization_account_id(+)
GROUP BY
-- ptp.end_date, for bug fix 1971612
pel.encumbrance_date,
pel.dr_cr_flag,
pel.gl_project_flag,
pel.gl_code_combination_id;
SELECT glp.end_date eff_dt,
pel.dr_cr_flag,
sum(pel.encumbrance_amount) sum_amt,
pel.gl_project_flag,
pel.person_id,
pel.assignment_id,
pel.gl_code_combination_id gl_ccid
-- ,sob.set_of_books_id,
-- decode(suspense_org_account_id, null, pel.gl_code_combination_id, pos.gl_code_combination_id) gl_ccid
FROM PSP_ENC_LINES PEL,
GL_PERIODS GLP,
GL_SETS_OF_BOOKS SOB,
-- ,GL_CODE_COMBINATIONS GCC,
-- PSP_ORGANIZATION_ACCOUNTS POS
WHERE sob.accounted_period_type = glp.period_type
AND sob.set_of_books_id = g_set_of_books_id
AND PEL.GL_PROJECT_FLAG = 'G'
AND PEL.ENCUMBRANCE_AMOUNT <> 0
AND PEL.ENC_CONTROL_ID = p_enc_control_id
AND PEL.ENCUMBRANCE_DATE BETWEEN GLP.START_DATE AND GLP.END_DATE
AND pel.gl_code_combination_id = gcc.code_combination_id
-- AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
-- AND pel.suspense_org_account_id = pos.organization_account_id(+)
GROUP BY sob.set_of_books_id,
glp.end_date,
pel.person_id,
pel.assignment_id,
pel.dr_cr_flag,
pel.gl_project_flag,
pel.gl_code_combination_id;
SELECT sob.set_of_books_id,
glp.end_date eff_dt,
pel.dr_cr_flag,
sum(pel.encumbrance_amount) sum_amt,
pel.gl_project_flag,
pel.person_id,
pel.gl_code_combination_id gl_ccid
-- ,decode(suspense_org_account_id, null, pel.gl_code_combination_id, pos.gl_code_combination_id) gl_ccid
FROM PSP_ENC_LINES PEL,
GL_PERIODS GLP,
GL_SETS_OF_BOOKS SOB
-- ,GL_CODE_COMBINATIONS GCC,
-- PSP_ORGANIZATION_ACCOUNTS POS
WHERE sob.set_of_books_id = g_set_of_books_id
AND sob.accounted_period_type = glp.period_type
AND PEL.GL_PROJECT_FLAG = 'G'
AND PEL.ENCUMBRANCE_AMOUNT <> 0
AND PEL.ENC_CONTROL_ID = p_enc_control_id
AND PEL.ENCUMBRANCE_DATE BETWEEN GLP.START_DATE AND GLP.END_DATE
-- AND pel.suspense_org_account_id = pos.organization_account_id(+)
-- AND pel.gl_code_combination_id = gcc.code_combination_id
-- AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
GROUP BY sob.set_of_books_id,
glp.end_date,
pel.person_id,
pel.dr_cr_flag,
pel.gl_project_flag,
pel.gl_code_combination_id;
SELECT sob.set_of_books_id,
glp.end_date eff_dt,
pel.dr_cr_flag,
sum(pel.encumbrance_amount) sum_amt,
pel.gl_project_flag,
pel.gl_code_combination_id gl_ccid
-- ,decode(suspense_org_account_id, null, pel.gl_code_combination_id, pos.gl_code_combination_id) gl_ccid
FROM PSP_ENC_LINES PEL,
GL_PERIODS GLP,
GL_SETS_OF_BOOKS SOB
-- ,GL_CODE_COMBINATIONS GCC,
-- PSP_ORGANIZATION_ACCOUNTS POS
WHERE sob.set_of_books_id = g_set_of_books_id
AND sob.accounted_period_type = glp.period_type
AND PEL.GL_PROJECT_FLAG = 'G'
AND PEL.ENCUMBRANCE_AMOUNT <> 0
AND PEL.ENC_CONTROL_ID = p_enc_control_id
AND PEL.ENCUMBRANCE_DATE BETWEEN GLP.START_DATE AND GLP.END_DATE
-- AND pel.gl_code_combination_id = gcc.code_combination_id
-- AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
-- AND pel.suspense_org_account_id = pos.organization_account_id(+)
GROUP BY sob.set_of_books_id,
glp.end_date,
pel.dr_cr_flag,
pel.gl_project_flag,
pel.gl_code_combination_id;
SELECT parameter_value
INTO l_time_para_value
FROM psp_enc_setup_options
WHERE setup_parameter = 'TIME_BASED_SUMM'
AND business_group_id = l_bg_id;
SELECT parameter_value
INTO l_gl_para_value
FROM psp_enc_setup_options
WHERE setup_parameter = 'CI_BASED_SUMM_GL'
AND business_group_id = l_bg_id;
update psp_enc_controls
set gl_phase = 'Summarize' ---NULL ... for 2444657
where enc_control_id = enc_control_rec.enc_control_id;
insert_into_enc_sum_lines(
l_enc_summary_line_id,
l_bg_id,
enc_control_rec.enc_control_id,
enc_control_rec.time_period_id,
enc_sum_lines_p1_rec.person_id,
enc_sum_lines_p1_rec.assignment_id,
enc_sum_lines_p1_rec.eff_dt,
-- enc_sum_lines_p1_rec.set_of_books_id,
g_set_of_books_id,
enc_sum_lines_p1_rec.gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL,
enc_sum_lines_p1_rec.sum_amt,
enc_sum_lines_p1_rec.dr_cr_flag,
'N',
enc_control_rec.payroll_id,
NULL,
enc_sum_lines_p1_rec.gl_project_flag,
enc_sum_lines_p1_rec.attribute_category, -- Introduced DFF columns for bug fix 2908859
enc_sum_lines_p1_rec.attribute1,
enc_sum_lines_p1_rec.attribute2,
enc_sum_lines_p1_rec.attribute3,
enc_sum_lines_p1_rec.attribute4,
enc_sum_lines_p1_rec.attribute5,
enc_sum_lines_p1_rec.attribute6,
enc_sum_lines_p1_rec.attribute7,
enc_sum_lines_p1_rec.attribute8,
enc_sum_lines_p1_rec.attribute9,
enc_sum_lines_p1_rec.attribute10,
l_return_status);
UPDATE psp_enc_lines
SET enc_summary_line_id = l_enc_summary_line_id
WHERE enc_control_id = enc_control_rec.enc_control_id
AND payroll_id = enc_control_rec.payroll_id
AND time_period_id = enc_control_rec.time_period_id
AND person_id = enc_sum_lines_p1_rec.person_id
AND assignment_id = enc_sum_lines_p1_rec.assignment_id
AND dr_cr_flag = enc_sum_lines_p1_rec.dr_cr_flag
AND gl_project_flag = enc_sum_lines_p1_rec.gl_project_flag
AND trunc(encumbrance_date) = trunc(enc_sum_lines_p1_rec.eff_dt) --- added for 3462452
-- Enhanced the following check to accomodate suspense postings done in this process for bug fix 3194807.
-- ANd gl_code_combination_id =enc_sum_lines_p1_rec.gl_ccid;
AND EXISTS (SELECT 1 FROM psp_organization_accounts poa
WHERE poa.organization_account_id = suspense_org_account_id
AND poa.gl_code_combination_id = enc_sum_lines_p1_rec.gl_ccid))
OR gl_code_combination_id = enc_sum_lines_p1_rec.gl_ccid);
UPDATE psp_enc_lines
SET enc_summary_line_id = l_enc_summary_line_id
WHERE enc_control_id = enc_control_rec.enc_control_id
AND payroll_id = enc_control_rec.payroll_id
AND time_period_id = enc_control_rec.time_period_id
AND person_id = enc_sum_lines_p1_rec.person_id
AND assignment_id = enc_sum_lines_p1_rec.assignment_id
AND dr_cr_flag = enc_sum_lines_p1_rec.dr_cr_flag
AND gl_project_flag = enc_sum_lines_p1_rec.gl_project_flag
AND trunc(encumbrance_date) = trunc(enc_sum_lines_p1_rec.eff_dt) --- added for 3462452
AND ( (suspense_org_account_id IS NOT NULL
AND suspense_reason_code like 'ES:%'
AND EXISTS (SELECT 1 FROM psp_organization_accounts poa
WHERE poa.organization_account_id = suspense_org_account_id
AND poa.gl_code_combination_id = enc_sum_lines_p1_rec.gl_ccid
AND NVL(poa.attribute_category, 'NULL') =
NVL(enc_sum_lines_p1_rec.attribute_category, 'NULL')
AND NVL(poa.attribute1, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute1, 'NULL')
AND NVL(poa.attribute2, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute2, 'NULL')
AND NVL(poa.attribute3, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute3, 'NULL')
AND NVL(poa.attribute4, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute4, 'NULL')
AND NVL(poa.attribute5, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute5, 'NULL')
AND NVL(poa.attribute6, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute6, 'NULL')
AND NVL(poa.attribute7, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute7, 'NULL')
AND NVL(poa.attribute8, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute8, 'NULL')
AND NVL(poa.attribute9, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute9, 'NULL')
AND NVL(poa.attribute10, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute10, 'NULL')))
OR ( gl_code_combination_id = enc_sum_lines_p1_rec.gl_ccid
AND NVL(attribute_category, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute_category, 'NULL')
AND NVL(attribute1, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute1, 'NULL')
AND NVL(attribute2, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute2, 'NULL')
AND NVL(attribute3, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute3, 'NULL')
AND NVL(attribute4, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute4, 'NULL')
AND NVL(attribute5, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute5, 'NULL')
AND NVL(attribute6, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute6, 'NULL')
AND NVL(attribute7, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute7, 'NULL')
AND NVL(attribute8, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute8, 'NULL')
AND NVL(attribute9, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute9, 'NULL')
AND NVL(attribute10, 'NULL') = NVL(enc_sum_lines_p1_rec.attribute10, 'NULL')));
update psp_enc_controls
set gl_phase = 'Summarize' ---NULL for 2444657
where enc_control_id = enc_control_rec.enc_control_id;
insert_into_enc_sum_lines(
l_enc_summary_line_id,
l_bg_id,
enc_control_rec.enc_control_id,
enc_control_rec.time_period_id,
enc_sum_lines_p2_rec.person_id,
NULL,
enc_sum_lines_p2_rec.eff_dt,
-- enc_sum_lines_p2_rec.set_of_books_id,
g_set_of_books_id,
enc_sum_lines_p2_rec.gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL,
enc_sum_lines_p2_rec.sum_amt,
enc_sum_lines_p2_rec.dr_cr_flag,
'N',
enc_control_rec.payroll_id,
NULL,
enc_sum_lines_p2_rec.gl_project_flag,
l_return_status);
UPDATE psp_enc_lines
SET enc_summary_line_id = l_enc_summary_line_id
WHERE enc_control_id = enc_control_rec.enc_control_id
AND payroll_id = enc_control_rec.payroll_id
ANd time_period_id = enc_control_rec.time_period_id
-- added check on gl_ccid - subha 22 Mar
AND gl_code_combination_id = enc_sum_lines_p2_rec.gl_ccid
AND person_id = enc_sum_lines_p2_rec.person_id
AND dr_cr_flag = enc_sum_lines_p2_rec.dr_cr_flag
AND gl_project_flag = enc_sum_lines_p2_rec.gl_project_flag;
update psp_enc_controls
set gl_phase = 'Summarize' ---NULL commented for 2444657
where enc_control_id = enc_control_rec.enc_control_id;
insert_into_enc_sum_lines(
l_enc_summary_line_id,
l_bg_id,
enc_control_rec.enc_control_id,
enc_control_rec.time_period_id,
NULL,
NULL,
enc_sum_lines_p3_rec.eff_dt,
-- enc_sum_lines_p3_rec.set_of_books_id,
g_set_of_books_id,
enc_sum_lines_p3_rec.gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL,
enc_sum_lines_p3_rec.sum_amt,
enc_sum_lines_p3_rec.dr_cr_flag,
'N',
enc_control_rec.payroll_id,
NULL,
enc_sum_lines_p3_rec.gl_project_flag,
l_return_status);
UPDATE psp_enc_lines
SET enc_summary_line_id = l_enc_summary_line_id
WHERE enc_control_id = enc_control_rec.enc_control_id
AND time_period_id = enc_control_rec.time_period_id
AND payroll_id = enc_control_rec.payroll_id
AND dr_cr_flag = enc_sum_lines_p3_rec.dr_cr_flag
AND gl_code_combination_id = enc_sum_lines_p3_rec.gl_ccid
AND gl_project_flag = enc_sum_lines_p3_rec.gl_project_flag;
update psp_enc_controls
set gl_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,
l_bg_id,
enc_control_rec.enc_control_id,
enc_control_rec.time_period_id,
enc_sum_lines_g1_rec.person_id,
enc_sum_lines_g1_rec.assignment_id,
enc_sum_lines_g1_rec.eff_dt,
g_set_of_books_id,
enc_sum_lines_g1_rec.gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL,
enc_sum_lines_g1_rec.sum_amt,
enc_sum_lines_g1_rec.dr_cr_flag,
'N',
enc_control_rec.payroll_id,
NULL,
enc_sum_lines_g1_rec.gl_project_flag,
l_return_status);
UPDATE psp_enc_lines
SET enc_summary_line_id = l_enc_summary_line_id
WHERE enc_control_id = enc_control_rec.enc_control_id
AND time_period_id = enc_control_rec.time_period_id
AND payroll_id = enc_control_rec.payroll_id
AND person_id = enc_sum_lines_g1_rec.person_id
AND assignment_id = enc_sum_lines_g1_rec.assignment_id
AND dr_cr_flag = enc_sum_lines_g1_rec.dr_cr_flag
AND gl_code_combination_id = enc_sum_lines_g1_rec.gl_ccid
AND gl_project_flag = enc_sum_lines_g1_rec.gl_project_flag;
update psp_enc_controls
set gl_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,
l_bg_id,
enc_control_rec.enc_control_id,
enc_control_rec.time_period_id,
enc_sum_lines_g2_rec.person_id,
NULL,
enc_sum_lines_g2_rec.eff_dt,
enc_sum_lines_g2_rec.set_of_books_id,
enc_sum_lines_g2_rec.gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL,
enc_sum_lines_g2_rec.sum_amt,
enc_sum_lines_g2_rec.dr_cr_flag,
'N',
enc_control_rec.payroll_id,
NULL,
enc_sum_lines_g2_rec.gl_project_flag,
l_return_status);
UPDATE psp_enc_lines
SET enc_summary_line_id = l_enc_summary_line_id
WHERE enc_control_id = enc_control_rec.enc_control_id
AND payroll_id = enc_control_rec.payroll_id
AND time_period_id = enc_control_rec.time_period_id
AND person_id = enc_sum_lines_g2_rec.person_id
AND dr_cr_flag = enc_sum_lines_g2_rec.dr_cr_flag
AND gl_code_combination_id = enc_sum_lines_g2_rec.gl_ccid
AND gl_project_flag = enc_sum_lines_g2_rec.gl_project_flag;
update psp_enc_controls
set gl_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,
l_bg_id,
enc_control_rec.enc_control_id,
enc_control_rec.time_period_id,
NULL,
NULL,
enc_sum_lines_g3_rec.eff_dt,
enc_sum_lines_g3_rec.set_of_books_id,
enc_sum_lines_g3_rec.gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL,
enc_sum_lines_g3_rec.sum_amt,
enc_sum_lines_g3_rec.dr_cr_flag,
'N',
enc_control_rec.payroll_id,
NULL,
enc_sum_lines_g3_rec.gl_project_flag,
l_return_status);
UPDATE psp_enc_lines
SET enc_summary_line_id = l_enc_summary_line_id
WHERE enc_control_id = enc_control_rec.enc_control_id
AND payroll_id = enc_control_rec.payroll_id
AND time_period_id = enc_control_rec.time_period_id
AND dr_cr_flag = enc_sum_lines_g3_rec.dr_cr_flag
AND gl_code_combination_id = enc_sum_lines_g3_rec.gl_ccid
AND gl_project_flag = enc_sum_lines_g3_rec.gl_project_flag;
End of comment for Create and Update enh. *****/
-- ##########################################################################
-- This procedure inserts records into psp_enc_summary_lines
-- ##########################################################################
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_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_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,
ATTRIBUTE_CATEGORY, -- Introduced DFF columns for bug fix 2908859
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
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,
p_set_of_books_id,
p_gl_code_combination_id,
p_project_id,
p_expenditure_organization_id,
p_expenditure_type,
p_task_id,
p_award_id,
p_summary_amount,
p_dr_cr_flag,
p_status_code,
p_payroll_id,
p_gl_period_id,
p_gl_project_flag,
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,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE);
g_error_api_path := 'INSERT_INTO_ENC_SUM_LINES:'||g_error_api_path;
fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','INSERT_INTO_ENC_SUM_LINES');
/****** Commented for Create and Update multi thread enh.
PROCEDURE tr_to_gl_int(p_payroll_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR enc_control_cur IS
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 Quick Upd. Enh. 2143723.
AND action_code = 'I'
AND run_id = g_run_id
AND business_group_id = g_business_group_id
AND set_of_books_id = g_set_of_books_id
AND gl_phase in ('Summarize', 'Transfer');
SELECT pesl.enc_summary_line_id,
pesl.effective_date,
pesl.gl_code_combination_id,
pesl.summary_amount,
pesl.dr_cr_flag,
pesl.set_of_books_id,
pesl.time_period_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.enc_control_id = l_enc_control_id
AND pesl.payroll_id = p_payroll_id
AND pesl.gl_code_combination_id is NOT NULL;
SELECT DISTINCT enc_control_id
FROM psp_enc_summary_lines
WHERE group_id = l_group_id;
gl_tie_tab.delete;
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 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 = g_set_of_books_id;
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;
insert_into_gl_int(
INT_REC.SET_OF_BOOKS_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);
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 group_id
into gl_tie_tab(l_rec_no).r_group_id
from psp_enc_summary_lines
where gl_code_combination_id is NOT NULL
AND enc_control_id = enc_control_rec.enc_control_id
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,
l_sob_id
);
/ ***** Converted the following UPDATE to BULK for R12 performance fixes
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);
r_enc_controls.enc_control_id.DELETE;
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;
End of comment for Create and Update multi thread enh. *****/
-- Introduced the following modified tr_to_gl_int procedure for Create and Update multi thread enh.
PROCEDURE tr_to_gl_int( p_payroll_action_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2) IS
l_user_je_cat VARCHAR2(25);
SELECT DISTINCT group_id
FROM psp_enc_summary_lines pesl
WHERE payroll_action_id = p_payroll_action_id
AND status_code = 'N'
AND superceded_line_id IS NULL
AND gl_code_combination_id IS NOT NULL;
SELECT enc_control_id
FROM psp_enc_controls
WHERE payroll_action_id = p_payroll_action_id;
SELECT gl_interface_control_s.nextval
INTO l_group_id
FROM dual;
UPDATE psp_enc_summary_lines pesl
SET group_id = l_group_id
WHERE status_code = 'N'
AND gl_code_combination_id IS NOT NULL
AND group_id IS NULL
AND superceded_line_id IS NULL
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl2
WHERE pesl2.payroll_action_id = p_payroll_action_id
AND pesl2.assignment_id = pesl.assignment_id
AND pesl2.time_period_id = pesl.time_period_id
AND pesl2.status_code IN ('N', 'R')
AND pesl2.superceded_line_id IS NOT NULL)
AND payroll_action_id = p_payroll_action_id;
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 count: '||sql%rowcount);
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(dr_cr_flag, 'D', pesl.summary_amount, NULL)) debit_amount,
TO_NUMBER(DECODE(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', 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,
psp_enc_controls pec
WHERE pec.enc_control_id = pesl.enc_control_id
AND pec.payroll_action_id = p_payroll_action_id
AND pesl.status_code = 'N'
AND pesl.gl_code_combination_id is NOT NULL
AND pesl.superceded_line_id IS NULL
AND pesl.group_id = l_group_id
AND pesl.payroll_action_id = p_payroll_action_id;
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_set_of_books_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted control record into gl_interface_control');
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 count:'||g_bulk_row_count);
r_enc_controls.enc_control_id.DELETE;
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_set_of_books_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 count: '||g_bulk_row_count);
UPDATE psp_enc_summary_lines
SET group_id = NULL
WHERE group_id = r_groups.group_id(recno)
AND status_code = 'N';
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';
/***** Commented for Create and Update multi thread enh.
PROCEDURE gl_enc_tie_back(
p_enc_control_id IN NUMBER,
p_period_end_date IN DATE,
p_group_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_mode IN VARCHAR2, -- Included as part of Bug fix #1776606s
p_return_status OUT NOCOPY VARCHAR2
)IS
/ *
CURSOR enc_control_cur IS
SELECT enc_control_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 Quick Upd. Enh. 2143723.
AND action_code = 'I'
AND run_id = nvl(g_run_id, run_id)
AND gl_phase = 'Transfer'
AND business_group_id = nvl(g_business_group_id, business_group_id)
AND set_of_books_id = nvl(g_set_of_books_id, set_of_books_id);
SELECT enc_summary_line_id,
dr_cr_flag,
summary_amount
FROM psp_enc_summary_lines
WHERE group_id = p_group_id
and enc_control_id = p_enc_control_id;
SELECT status,
reference6
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.encumbrance_date,
pel.suspense_org_account_id
FROM psp_enc_lines pel
WHERE pel.enc_summary_line_id = p_enc_line_id
and enc_control_id=p_enc_control_id ; -- this added to fix suspense ac postings
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_lines pel
WHERE pel.enc_summary_line_id = p_line_id
AND pel.assignment_id = paf.assignment_id
AND pel.encumbrance_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id
AND pel.encumbrance_date between
hou.date_from and nvl(hou.date_to,pel.encumbrance_date);
SELECT hou.organization_id, hou.name
FROM hr_all_organization_units hou,
per_assignments_f paf,
psp_enc_lines pel
WHERE
-- pel.enc_summary_line_id = p_line_id
pel.rowid=p_rowid
AND pel.assignment_id = paf.assignment_id
AND pel.encumbrance_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id
AND pel.encumbrance_date between
hou.date_from and nvl(hou.date_to,pel.encumbrance_date);
SELECT hou.name,
hou.organization_id,
pel.rowid,
pel.assignment_id,
pel.encumbrance_date,
pel.suspense_org_account_id,
pel.gl_code_combination_id
FROM hr_organization_units hou,
per_assignments_f paf,
psp_enc_lines pel
WHERE pel.enc_summary_line_id = p_enc_summary_line_id
AND pel.assignment_id = paf.assignment_id
AND pel.encumbrance_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id
AND pel.business_group_id = g_business_group_id
AND pel.set_of_books_id = g_set_of_books_id
AND pel.encumbrance_date between hou.date_from and nvl(hou.date_to,pel.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.organization_id = p_organization_id
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND poa.account_type_code = 'S'
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 = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND p_distribution_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_distribution_date); Bug 2056877.* /
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;
delete from psp_enc_summary_lines
where group_id = p_group_id
and enc_control_id = p_enc_control_id;
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_controls
SET gl_phase = 'TieBack'
WHERE run_id = g_run_id;
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_lines
SET status_code = 'N'
WHERE rowid = l_rowid;
-- if the suspense a/c failed,update the status of the whole batch and display the error
ELSIF l_suspense_org_account_id IS NOT NULL AND *** /
ELSIF l_suspense_org_account_id IS NOT NULL AND
(l_status <> 'P' OR substr(l_status,1,1) <> 'W') THEN
x_susp_failed_org_name := l_organization_name;
UPDATE psp_enc_lines
SET suspense_reason_code = l_status,
status_code = 'N'
WHERE rowid = l_rowid;
UPDATE psp_enc_lines
SET prev_effective_date = encumbrance_date
WHERE rowid = l_rowid;
UPDATE psp_enc_lines
SET suspense_org_account_id = l_organization_account_id,
suspense_reason_code = 'ES:' || l_status,
gl_project_flag = l_gl_project_flag,
-- encumbrance_date = l_encumbrance_date, for Bug 3194807
encumbrance_date = l_effective_date,
/ * Commented for Bug fix 3194807
schedule_line_id = null,
org_schedule_id = null,
default_org_account_id = null,
element_account_id = null,
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), commented for bug 3194807 * /
status_code = 'N'
WHERE rowid = l_rowid;
UPDATE psp_enc_controls
SET gl_phase = 'TieBack'
WHERE enc_control_id = p_enc_control_id;* /
UPDATE psp_enc_lines
SET suspense_org_account_id = NULL,
suspense_reason_code = NULL,
gl_project_flag = decode(gl_code_combination_id,NULL,'P','G'),
encumbrance_date = prev_effective_date
WHERE suspense_reason_code like 'ES:%'
AND enc_summary_line_id
IN (SELECT enc_summary_line_id
FROM psp_enc_summary_lines
WHERE enc_control_id = p_enc_control_id);
UPDATE psp_enc_summary_lines
SET status_code = 'A'
WHERE enc_summary_line_id = l_enc_summary_line_id;
UPDATE psp_enc_lines
SET status_code = 'A'
WHERE enc_summary_line_id = l_enc_summary_line_id
AND enc_control_id = p_enc_control_id;
UPDATE psp_enc_lines
SET gl_code_combination_id = (SELECT poa.gl_code_combination_id
FROM psp_organization_accounts poa
where poa.organization_account_id = suspense_org_account_id),
project_id = NULL,
task_id = NULL,
award_id = NULL,
expenditure_organization_id = NULL,
expenditure_type = NULL
WHERE enc_summary_line_id = l_enc_summary_line_id
AND suspense_reason_code LIKE 'ES:%';
INSERT INTO psp_enc_lines_history
(enc_line_id,business_group_id,enc_element_type_id,encumbrance_date,
dr_cr_flag,encumbrance_amount,status_code,enc_line_type,schedule_line_id,org_schedule_id,
default_org_account_id,suspense_org_account_id,element_account_id,gl_project_flag,
enc_summary_line_id,person_id,assignment_id,award_id,task_id,expenditure_type,
expenditure_organization_id,project_id,gl_code_combination_id,time_period_id,payroll_id,
set_of_books_id,default_reason_code,suspense_reason_code,enc_control_id,change_flag,last_update_date,
last_updated_by,last_update_login,created_by,creation_date,enc_start_date,enc_end_date,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10)
SELECT enc_line_id,business_group_id,enc_element_type_id,encumbrance_date,
dr_cr_flag,encumbrance_amount,status_code,enc_line_type,schedule_line_id,org_schedule_id,
default_org_account_id,suspense_org_account_id,element_account_id,gl_project_flag,
enc_summary_line_id,person_id,assignment_id,award_id,task_id,expenditure_type,
expenditure_organization_id,project_id,gl_code_combination_id,time_period_id,payroll_id,
set_of_books_id,default_reason_code,suspense_reason_code,enc_control_id,change_flag,last_update_date,
last_updated_by,last_update_login,created_by,creation_date,enc_start_date,enc_end_date,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10
FROM psp_enc_lines
WHERE status_code = 'A'
AND enc_summary_line_id = l_enc_summary_line_id
AND enc_control_id = p_enc_control_id;
DELETE FROM psp_enc_lines
WHERE status_code = 'A'
AND enc_summary_line_id = l_enc_summary_line_id
AND enc_control_id = p_enc_control_id;
UPDATE psp_enc_controls
SET summ_gl_dr_amount = l_dr_summary_amount,
summ_gl_cr_amount = l_cr_summary_amount,
gl_phase = 'TieBack'
WHERE enc_control_id = p_enc_control_id;
End of comment for Create and Update multi thread enh. *****/
-- Introduced the following modified gl_enc_tie_back procedure for Create and Update multi thread enh.
PROCEDURE gl_enc_tie_back( p_payroll_action_id IN NUMBER,
p_group_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_mode IN VARCHAR2,
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)))
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 pel.rowid,
pel.encumbrance_date,
pel.enc_start_date,
pel.enc_end_date,
pel.assignment_id,
pel.payroll_id,
pel.enc_element_type_id,
pel.gl_code_combination_id,
pel.suspense_org_account_id,
pesl.interface_status,
ptp.end_date
FROM psp_enc_lines pel,
psp_enc_summary_lines pesl,
per_time_periods ptp
WHERE pel.payroll_action_id = p_payroll_action_id
AND pel.enc_summary_line_id = pesl.enc_summary_line_id
AND pesl.payroll_action_id = p_payroll_action_id
AND pesl.status_code = 'R'
AND pesl.group_id = p_group_id
AND ptp.time_period_id = pel.time_period_id
ORDER BY 5, 6, 7, 8, 3;
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_lines pel
WHERE pel.rowid=p_rowid
AND pel.assignment_id = paf.assignment_id
AND pel.encumbrance_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id
AND pel.encumbrance_date BETWEEN hou.date_from AND NVL(hou.date_to, pel.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.organization_id = p_organization_id
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND poa.account_type_code = 'S'
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 organization_account_id = p_organization_account_id;
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 DISTINCT enc_control_id
FROM psp_enc_summary_lines
WHERE group_id = p_group_id;
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 count: '||sql%rowcount);
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 '||sql%rowcount);
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 rejected lines status in psp_enc_summary_lines count: ' ||g_bulk_row_count);
UPDATE psp_enc_lines
SET prev_effective_date = encumbrance_date,
orig_gl_code_combination_id = gl_code_combination_id,
orig_project_id = project_id,
orig_task_id = task_id,
orig_award_id = award_id,
orig_expenditure_org_id = expenditure_organization_id,
orig_expenditure_type = expenditure_type
WHERE rowid = r_suspense_ac.row_id(recno)
AND enc_start_date <= g_def_end_date;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines count'||sql%rowcount);
UPDATE psp_enc_lines
SET suspense_org_account_id = l_organization_account_id,
gl_code_combination_id = l_susp_glccid,
project_id = l_project_id,
task_id = l_task_id,
award_id = l_award_id,
expenditure_organization_id = l_expenditure_organization_id,
expenditure_type = l_expenditure_type,
suspense_reason_code = 'ES:' || r_suspense_ac.interface_status(recno),
gl_project_flag = l_gl_project_flag,
encumbrance_date = l_effective_date,
status_code = 'N'
WHERE rowid = r_suspense_ac.row_id(recno)
AND enc_start_date <= g_def_end_date;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines1 count'||sql%rowcount);
UPDATE psp_enc_lines
SET suspense_org_account_id = NULL,
gl_code_combination_id = orig_gl_code_combination_id,
project_id = orig_project_id,
task_id = orig_task_id,
award_id = orig_award_id,
expenditure_organization_id = orig_expenditure_org_id,
expenditure_type = orig_expenditure_type,
gl_project_flag = decode(orig_gl_code_combination_id,NULL,'P','G'),
encumbrance_date = prev_effective_date
WHERE suspense_reason_code like 'ES:%'
AND enc_summary_line_id IN (SELECT enc_summary_line_id
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.group_id = p_group_id
AND status_code = 'R');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines2 count'||sql%rowcount);
UPDATE psp_enc_lines
SET orig_gl_code_combination_id = NULL,
orig_project_id = NULL,
orig_task_id = NULL,
orig_award_id = NULL,
orig_expenditure_org_id = NULL,
orig_expenditure_type = NULL,
suspense_reason_code = NULL
WHERE suspense_reason_code like 'ES:%'
AND enc_summary_line_id IN (SELECT enc_summary_line_id
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.group_id = p_group_id
AND status_code = 'R');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines3 count'||sql%rowcount);
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));
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');
UPDATE psp_enc_summary_lines
SET status_code = 'A'
WHERE 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 status_code to ''A'' in psp_enc_summary_lines count: '||g_bulk_row_count);
INSERT INTO psp_enc_lines_history
(enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,
dr_cr_flag, encumbrance_amount, status_code, enc_line_type,
schedule_line_id, org_schedule_id, default_org_account_id, suspense_org_account_id,
element_account_id, gl_project_flag, enc_summary_line_id, person_id,
assignment_id, award_id, task_id, expenditure_type,
expenditure_organization_id, project_id, gl_code_combination_id, time_period_id,
payroll_id, set_of_books_id, default_reason_code, suspense_reason_code,
enc_control_id, change_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date, enc_start_date,
enc_end_date, attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9, attribute10,
payroll_action_id, hierarchy_code, hierarchy_start_date, hierarchy_end_date,
orig_gl_code_combination_id, orig_project_id, orig_task_id, orig_award_id,
orig_expenditure_org_id, orig_expenditure_type)
SELECT enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,
dr_cr_flag, encumbrance_amount, 'A', enc_line_type,
schedule_line_id, org_schedule_id, default_org_account_id, suspense_org_account_id,
element_account_id, gl_project_flag, enc_summary_line_id, person_id,
assignment_id, award_id, task_id, expenditure_type,
expenditure_organization_id, project_id, gl_code_combination_id, time_period_id,
payroll_id, set_of_books_id, default_reason_code, suspense_reason_code,
enc_control_id, change_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date, enc_start_date,
enc_end_date, attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9, attribute10,
payroll_action_id, hierarchy_code, hierarchy_start_date, hierarchy_end_date,
orig_gl_code_combination_id, orig_project_id, orig_task_id, orig_award_id,
orig_expenditure_org_id, orig_expenditure_type
FROM psp_enc_lines
WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno);
DELETE FROM psp_enc_lines
WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted lines from psp_enc_lines that are summarized and trasnferred to target systems count: '||g_bulk_row_count);
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 count: '||g_bulk_row_count);
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_sum_tran','insert_into_gl_int');
/**** Commented for Create and Update Multi thread
PROCEDURE create_gms_enc_sum_lines( p_payroll_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR enc_control_cur IS
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 Quick Upd. Enh. 2143723.
AND action_code = 'I'
AND run_id = g_run_id
AND business_group_id = g_business_group_id
AND set_of_books_id = g_set_of_books_id
AND (gms_phase is null or gms_phase = 'TieBack'); --- 2444657
SELECT
-- ptp.end_date eff_dt, for bug fix 1971612
pel.encumbrance_date eff_dt,
pel.dr_cr_flag,
sum(pel.encumbrance_amount) sum_amt,
pel.gl_project_flag,
pel.person_id,
pel.assignment_id, -- Included for Enh. 2143723
decode(suspense_org_account_id,NULL,pel.project_id,poa.project_id) project_id,
decode(suspense_org_account_id,NULL,pel.task_id,poa.task_id) task_id ,
decode(suspense_org_account_id,NULL,pel.award_id,poa.award_id)award_id ,
decode(suspense_org_account_id,NULL,pel.expenditure_type,poa.expenditure_type) expenditure_type ,
decode(suspense_org_account_id,NULL,pel.expenditure_organization_id,poa.expenditure_organization_id)
expenditure_organization_id,
-- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute_category, poa.attribute_category), NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute1, poa.attribute1), NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute2, poa.attribute2), NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute3, poa.attribute3), NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute4, poa.attribute4), NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute5, poa.attribute5), NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute6, poa.attribute6), NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute7, poa.attribute7), NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute8, poa.attribute8), NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute9, poa.attribute9), NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute10, poa.attribute10), NULL) attribute10
/ * pel.project_id,
pel.task_id,
pel.award_id,
pel.expenditure_type,
pel.expenditure_organization_id * /
-- ,pai.set_of_books_id
FROM PSP_ENC_LINES PEL,
PER_TIME_PERIODS PTP,
PSP_ORGANIZATION_ACCOUNTS POA
-- ,PA_IMPLEMENTATIONS_ALL PAI
WHERE PEL.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
-- AND PEL.ENCUMBRANCE_DATE BETWEEN PTP.START_DATE AND PTP.END_DATE
-- Commented out for bug fix 1971612
AND PEL.GL_PROJECT_FLAG = 'P'
AND PEL.ENCUMBRANCE_AMOUNT <> 0
AND PEL.ENC_CONTROL_ID = p_enc_control_id
-- Refined the following condition for bug fix 3233373
AND ( (suspense_org_account_id IS NULL AND PEL.AWARD_ID IS NOT NULL)
OR (poa.award_id IS NOT NULL))
-- End of bug fix 3233373
AND PEL.SUSPENSE_ORG_ACCOUNT_ID = POA.ORGANIZATION_ACCOUNT_ID(+)
-- AND PEL.BUSINESS_GROUP_ID = PAI.BUSINESS_GROUP_ID
-- AND PAI.SET_OF_BOOKS_ID = g_set_of_books_id
GROUP BY
-- ptp.end_date,
pel.encumbrance_date,
-- pel.project_id,
-- pel.task_id,
-- pel.award_id,
-- pel.expenditure_type,
-- pel.expenditure_organization_id,
DECODE(suspense_org_account_id,NULL,pel.project_id,poa.project_id),
DECODE(suspense_org_account_id,NULL,pel.task_id,poa.task_id),
DECODE(suspense_org_account_id,NULL,pel.award_id,poa.award_id) ,
DECODE(suspense_org_account_id,NULL,pel.expenditure_type,poa.expenditure_type) ,
DECODE(suspense_org_account_id,NULL,pel.expenditure_organization_id,poa.expenditure_organization_id),
pel.person_id,
pel.assignment_id, -- Included for Enh. 2143723
pel.dr_cr_flag,
pel.gl_project_flag,
-- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute_category, poa.attribute_category), NULL),
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute1, poa.attribute1), NULL),
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute2, poa.attribute2), NULL),
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute3, poa.attribute3), NULL),
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute4, poa.attribute4), NULL),
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute5, poa.attribute5), NULL),
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute6, poa.attribute6), NULL),
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute7, poa.attribute7), NULL),
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute8, poa.attribute8), NULL),
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute9, poa.attribute9), NULL),
DECODE(g_dff_grouping_option, 'Y', DECODE(suspense_org_account_id, NULL, pel.attribute10, poa.attribute10), NULL);
SELECT glp.end_date eff_dt,
pel.dr_cr_flag,
sum(pel.encumbrance_amount) sum_amt,
pel.gl_project_flag,
pel.person_id,
pel.project_id,
pel.task_id,
pel.award_id,
pel.expenditure_type,
pel.expenditure_organization_id,
pai.set_of_books_id
FROM PSP_ENC_LINES PEL,
GL_PERIODS GLP,
PA_IMPLEMENTATIONS_ALL PAI
WHERE PEL.BUSINESS_GROUP_ID = PAI.BUSINESS_GROUP_ID
AND GLP.PERIOD_TYPE = PAI.PA_PERIOD_TYPE
AND PEL.ENCUMBRANCE_DATE BETWEEN GLP.START_DATE AND GLP.END_DATE
AND PEL.GL_PROJECT_FLAG = 'P'
AND PEL.ENCUMBRANCE_AMOUNT <> 0
AND PEL.ENC_CONTROL_ID = p_enc_control_id
AND PAI.SET_OF_BOOKS_ID = g_set_of_books_id
AND PEL.AWARD_ID IS NOT NULL
GROUP BY glp.end_date,
pel.project_id,
pel.task_id,
pel.award_id,
pel.expenditure_type,
pel.expenditure_organization_id,
pel.person_id,
pel.dr_cr_flag,
pel.gl_project_flag,
pai.set_of_books_id;
SELECT parameter_value
INTO l_time_para_value
FROM psp_enc_setup_options
WHERE setup_parameter = 'TIME_BASED_SUMM'
AND business_group_id = l_bg_id;
SELECT parameter_value
INTO l_ogm_para_value
FROM psp_enc_setup_options
WHERE setup_parameter = 'CI_BASED_SUMM_OGM'
AND business_group_id = l_bg_id;
update psp_enc_controls
set gms_phase = 'Summarize' ---NULL commented for 2444657
where enc_control_id = enc_control_rec.enc_control_id;
insert_into_enc_sum_lines(
l_enc_summary_line_id,
L_BG_ID,
enc_control_rec.enc_control_id,
enc_control_rec.time_period_id,
enc_sum_lines_p_rec.person_id,
enc_sum_lines_p_rec.assignment_id, --Included for Enh. 2143723
enc_sum_lines_p_rec.eff_dt,
-- enc_sum_lines_p_rec.set_of_books_id,
g_set_of_books_id,
NULL,
enc_sum_lines_p_rec.project_id,
enc_sum_lines_p_rec.expenditure_organization_id,
enc_sum_lines_p_rec.expenditure_type,
enc_sum_lines_p_rec.task_id,
enc_sum_lines_p_rec.award_id,
enc_sum_lines_p_rec.sum_amt,
enc_sum_lines_p_rec.dr_cr_flag,
'N',
enc_control_rec.payroll_id,
NULL,
enc_sum_lines_p_rec.gl_project_flag,
enc_sum_lines_p_rec.attribute_category, -- Introduced DFF columns for bug fix 2908859
enc_sum_lines_p_rec.attribute1,
enc_sum_lines_p_rec.attribute2,
enc_sum_lines_p_rec.attribute3,
enc_sum_lines_p_rec.attribute4,
enc_sum_lines_p_rec.attribute5,
enc_sum_lines_p_rec.attribute6,
enc_sum_lines_p_rec.attribute7,
enc_sum_lines_p_rec.attribute8,
enc_sum_lines_p_rec.attribute9,
enc_sum_lines_p_rec.attribute10,
p_return_status);
UPDATE psp_enc_lines
SET enc_summary_line_id = l_enc_summary_line_id
WHERE -- project_id = enc_sum_lines_p_rec.project_id Commented for bug fix 3194807
-- Introduced the suspense_org_account verification for bug fix 3194807
( (suspense_org_account_id IS NOT NULL
AND suspense_reason_code like 'ES:%'
AND EXISTS (SELECT 1 FROM psp_organization_accounts poa
WHERE poa.organization_account_id = suspense_org_account_id
AND poa.project_id = enc_sum_lines_p_rec.project_id
AND poa.task_id = enc_sum_lines_p_rec.task_id
AND poa.expenditure_organization_id = enc_sum_lines_p_rec.expenditure_organization_id
AND poa.expenditure_type = enc_sum_lines_p_rec.expenditure_type
AND poa.award_id = enc_sum_lines_p_rec.award_id))
OR (project_id = enc_sum_lines_p_rec.project_id
AND task_id = enc_sum_lines_p_rec.task_id
AND expenditure_organization_id = enc_sum_lines_p_rec.expenditure_organization_id
AND expenditure_type = enc_sum_lines_p_rec.expenditure_type
AND award_id = enc_sum_lines_p_rec.award_id))
-- AND task_id = enc_sum_lines_p_rec.task_id Commented for bug fix 3194807
AND enc_control_id = enc_control_rec.enc_control_id
AND time_period_id = enc_control_rec.time_period_id
-- AND award_id = enc_sum_lines_p_rec.award_id Commented for bug fix 3194807
-- AND expenditure_type = enc_sum_lines_p_rec.expenditure_type Commented for bug fix 3194807
-- AND expenditure_organization_id = enc_sum_lines_p_rec.expenditure_organization_id Commented for bug fix 3194807
AND person_id = enc_sum_lines_p_rec.person_id
-- Included the following chek for Enh. 2143723
AND assignment_id = enc_sum_lines_p_rec.assignment_id
AND dr_cr_flag = enc_sum_lines_p_rec.dr_cr_flag
AND gl_project_flag = enc_sum_lines_p_rec.gl_project_flag
AND trunc(encumbrance_date) = trunc(enc_sum_lines_p_rec.eff_dt); ---- bug 3462452
UPDATE psp_enc_lines
SET enc_summary_line_id = l_enc_summary_line_id
WHERE enc_control_id = enc_control_rec.enc_control_id
AND payroll_id = enc_control_rec.payroll_id
AND time_period_id = enc_control_rec.time_period_id
AND person_id = enc_sum_lines_p_rec.person_id
AND assignment_id = enc_sum_lines_p_rec.assignment_id
AND dr_cr_flag = enc_sum_lines_p_rec.dr_cr_flag
AND gl_project_flag = enc_sum_lines_p_rec.gl_project_flag
AND trunc(encumbrance_date) = trunc(enc_sum_lines_p_rec.eff_dt) --- added for 3462452
AND ( (suspense_org_account_id IS NOT NULL
AND suspense_reason_code like 'ES:%'
AND EXISTS (SELECT 1 FROM psp_organization_accounts poa
WHERE poa.organization_account_id = suspense_org_account_id
AND poa.project_id = enc_sum_lines_p_rec.project_id
AND poa.task_id = enc_sum_lines_p_rec.task_id
AND poa.expenditure_organization_id = enc_sum_lines_p_rec.expenditure_organization_id
AND poa.expenditure_type = enc_sum_lines_p_rec.expenditure_type
AND poa.award_id = enc_sum_lines_p_rec.award_id
AND NVL(poa.attribute_category, 'NULL') =
NVL(enc_sum_lines_p_rec.attribute_category, 'NULL')
AND NVL(poa.attribute1, 'NULL') = NVL(enc_sum_lines_p_rec.attribute1, 'NULL')
AND NVL(poa.attribute2, 'NULL') = NVL(enc_sum_lines_p_rec.attribute2, 'NULL')
AND NVL(poa.attribute3, 'NULL') = NVL(enc_sum_lines_p_rec.attribute3, 'NULL')
AND NVL(poa.attribute4, 'NULL') = NVL(enc_sum_lines_p_rec.attribute4, 'NULL')
AND NVL(poa.attribute5, 'NULL') = NVL(enc_sum_lines_p_rec.attribute5, 'NULL')
AND NVL(poa.attribute6, 'NULL') = NVL(enc_sum_lines_p_rec.attribute6, 'NULL')
AND NVL(poa.attribute7, 'NULL') = NVL(enc_sum_lines_p_rec.attribute7, 'NULL')
AND NVL(poa.attribute8, 'NULL') = NVL(enc_sum_lines_p_rec.attribute8, 'NULL')
AND NVL(poa.attribute9, 'NULL') = NVL(enc_sum_lines_p_rec.attribute9, 'NULL')
AND NVL(poa.attribute10, 'NULL') = NVL(enc_sum_lines_p_rec.attribute10, 'NULL')))
OR ( project_id = enc_sum_lines_p_rec.project_id
AND task_id = enc_sum_lines_p_rec.task_id
AND expenditure_organization_id = enc_sum_lines_p_rec.expenditure_organization_id
AND expenditure_type = enc_sum_lines_p_rec.expenditure_type
AND award_id = enc_sum_lines_p_rec.award_id
AND NVL(attribute_category, 'NULL') = NVL(enc_sum_lines_p_rec.attribute_category, 'NULL')
AND NVL(attribute1, 'NULL') = NVL(enc_sum_lines_p_rec.attribute1, 'NULL')
AND NVL(attribute2, 'NULL') = NVL(enc_sum_lines_p_rec.attribute2, 'NULL')
AND NVL(attribute3, 'NULL') = NVL(enc_sum_lines_p_rec.attribute3, 'NULL')
AND NVL(attribute4, 'NULL') = NVL(enc_sum_lines_p_rec.attribute4, 'NULL')
AND NVL(attribute5, 'NULL') = NVL(enc_sum_lines_p_rec.attribute5, 'NULL')
AND NVL(attribute6, 'NULL') = NVL(enc_sum_lines_p_rec.attribute6, 'NULL')
AND NVL(attribute7, 'NULL') = NVL(enc_sum_lines_p_rec.attribute7, 'NULL')
AND NVL(attribute8, 'NULL') = NVL(enc_sum_lines_p_rec.attribute8, 'NULL')
AND NVL(attribute9, 'NULL') = NVL(enc_sum_lines_p_rec.attribute9, 'NULL')
AND NVL(attribute10, 'NULL') = NVL(enc_sum_lines_p_rec.attribute10, 'NULL')));
update psp_enc_controls
set gms_phase = NULL
where enc_control_id = enc_control_rec.enc_control_id;
insert_into_enc_sum_lines(
l_enc_summary_line_id,
l_bg_id,
enc_control_rec.enc_control_id,
enc_control_rec.time_period_id,
enc_sum_lines_g_rec.person_id,
NULL,
enc_sum_lines_g_rec.eff_dt,
enc_sum_lines_g_rec.set_of_books_id,
NULL,
enc_sum_lines_g_rec.project_id,
enc_sum_lines_g_rec.expenditure_organization_id,
enc_sum_lines_g_rec.expenditure_type,
enc_sum_lines_g_rec.task_id,
enc_sum_lines_g_rec.award_id,
enc_sum_lines_g_rec.sum_amt,
enc_sum_lines_g_rec.dr_cr_flag,
'N',
enc_control_rec.payroll_id,
NULL,
enc_sum_lines_g_rec.gl_project_flag,
l_return_status);
UPDATE psp_enc_lines
SET enc_summary_line_id = l_enc_summary_line_id
WHERE project_id = enc_sum_lines_g_rec.project_id
AND task_id = enc_sum_lines_g_rec.task_id
AND enc_control_id = enc_control_rec.enc_control_id
AND time_period_id = enc_control_rec.time_period_id
AND award_id = enc_sum_lines_g_rec.award_id
AND expenditure_type = enc_sum_lines_g_rec.expenditure_type
AND expenditure_organization_id = enc_sum_lines_g_rec.expenditure_organization_id
AND person_id = enc_sum_lines_g_rec.person_id
AND dr_cr_flag = enc_sum_lines_g_rec.dr_cr_flag
AND gl_project_flag = enc_sum_lines_g_rec.gl_project_flag;
End of comment for Create and Update multi thread *****/
-- ##########################################################################
-- This procedure transfers summarized lines from psp_enc_summary_lines
-- with gl_project_flag = 'P' to pa_transaction_interface
-- This procedure transfers lines from PSP_ENC_SUMMARY_LINES into PA_TRANSACTION_INTERFACE,
-- kicks off the TRANSACTION IMPORT program in GMS and sends ENC_CONTROL_ID, END_DATE for
-- the relevant TIME_PERIOD_ID and GMS_BATCH_NAME into the tie back procedure.
-- ##########################################################################
-- Introduced the following modified procedure for Create and Update multi thread
PROCEDURE tr_to_gms_int(p_payroll_action_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2) IS
l_tr_source VARCHAR2(30);
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.expenditure_item_id,
hou.name exp_org_name,
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,
pesl.gms_batch_name,
DECODE(pec.uom, 'H', DECODE(SIGN(summary_amount), -1, 'Y')) unmatched_nve_txn_flag, --6242618
papf.employee_number --Added for Bug 10126350
FROM psp_enc_summary_lines pesl,
hr_organization_units hou,
pa_projects_all ppa,
pa_tasks_expend_v pt, -- Bug : 16391366 (20/03/2013)
per_time_periods ptp,
psp_enc_controls pec
,per_all_people_f papf --Added for Bug 10126350
WHERE pec.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 pesl.award_id IS NOT NULL
AND pesl.superceded_line_id IS 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 gms_batch_name IS NOT NULL
AND papf.person_id = pesl.person_id --Added for Bug 10126350
AND pesl.effective_date between papf.effective_start_date and papf.effective_end_date --Added for Bug 10126350
;
SELECT DISTINCT enc_control_id
FROM psp_enc_summary_lines
WHERE payroll_action_id = p_payroll_action_id
AND superceded_line_id IS NULL
AND gms_batch_name IS NOT NULL;
SELECT transaction_source
FROM pa_transaction_sources
WHERE transaction_source = 'GOLDE';
SELECT DISTINCT gms_batch_name
FROM psp_enc_summary_lines pesl
WHERE payroll_action_id = p_payroll_action_id
AND status_code = 'N'
AND superceded_line_id IS NULL
AND gl_code_combination_id IS NULL;
SELECT DISTINCT pesl.person_id,
papf.employee_number
FROM per_all_people_f papf,
psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND papf.person_id = pesl.person_id
AND pesl.superceded_line_id IS NULL
AND pesl.gms_batch_name IS NOT NULL
AND pesl.effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date;
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;
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 gms_batch_name_tab(i)
FROM DUAL;
UPDATE psp_enc_summary_lines pesl
SET gms_batch_name = gms_batch_name_tab(i)
WHERE status_code = 'N'
AND gl_code_combination_id IS NULL
AND gms_batch_name IS NULL
ANd superceded_line_id IS NULL
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl2
WHERE pesl2.payroll_action_id = p_payroll_action_id
AND pesl2.assignment_id = pesl.assignment_id
AND pesl2.time_period_id = pesl.time_period_id
AND pesl2.status_code IN ('N', 'R')
AND pesl2.superceded_line_id IS NOT NULL)
AND payroll_action_id = p_payroll_action_id
AND org_id = org_id_tab(i);
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,unmatched_negative_txn_flag)
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_business_group_id,r_interface.unmatched_nve_txn_flag(recno));
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into pa_transaction_interface_all '||g_bulk_row_count);
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');
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;
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) || 'update psp_enc_summary_lines setting encumbrance_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';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'delete from pa_transaction_interface_all count:'||g_bulk_row_count);
DELETE gms_transaction_interface_all
WHERE batch_name = r_gms_batch.gms_batch_name(recno)
AND transaction_source = 'GOLDE';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'delete from gms_transaction_interface_all count:'||g_bulk_row_count);
UPDATE psp_enc_summary_lines
SET gms_batch_name = NULL
WHERE gms_batch_name = r_gms_batch.gms_batch_name(recno)
AND status_code = 'N';
/***** Commented for Create and Update multi thread enh.
PROCEDURE gms_enc_tie_back( p_enc_control_id IN NUMBER,
p_period_end_date IN DATE,
p_gms_batch_name IN VARCHAR2,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_mode IN VARCHAR2, -- Included as part of Bug fix #1776606
p_return_status OUT NOCOPY VARCHAR2) IS
CURSOR gms_tie_back_success_cur IS
SELECT enc_summary_line_id,
dr_cr_flag,
summary_amount
FROM psp_enc_summary_lines
WHERE gms_batch_name = p_gms_batch_name
and enc_control_id = p_enc_control_id;
SELECT nvl(transaction_rejection_code,'P'),
orig_transaction_reference,
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 --added subqry for 3953230
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_control_id = p_enc_control_id);
SELECT pel.rowid,
pel.encumbrance_date,
pel.suspense_org_account_id
FROM psp_enc_lines pel
WHERE pel.enc_summary_line_id = p_enc_line_id
and pel.enc_control_id=p_enc_control_id;
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_lines pel
WHERE pel.enc_summary_line_id = p_line_id
AND pel.assignment_id = paf.assignment_id
AND pel.encumbrance_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id
AND pel.encumbrance_date between
hou.date_from and nvl(hou.date_to,pel.encumbrance_date);
SELECT hou.organization_id, hou.name
FROM hr_all_organization_units hou,
per_assignments_f paf,
psp_enc_lines pel
WHERE
-- pel.enc_summary_line_id = p_line_id
pel.rowid=p_rowid
AND pel.assignment_id = paf.assignment_id
AND pel.encumbrance_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id
AND pel.encumbrance_date between
hou.date_from and nvl(hou.date_to,pel.encumbrance_date);
SELECT hou.name,
hou.organization_id,
pel.rowid,
pel.assignment_id,
pel.encumbrance_date,
pel.suspense_org_account_id
FROM hr_organization_units hou,
per_assignments_f paf,
psp_enc_lines pel,
WHERE pel.enc_summary_line_id = p_enc_summary_line_id
AND pel.assignment_id = paf.assignment_id
AND pel.encumbrance_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND pel.business_group_id = g_business_group_id
AND pel.set_of_books_id = g_set_of_books_id
AND paf.organization_id = hou.organization_id;
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.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND poa.account_type_code = 'S'
-- AND poa.award_id is not null for Bug Fix 1776606
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 = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
-- AND poa.award_id is not null For Bug fix 1776606
AND p_encumbrance_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_encumbrance_date); Bug 2056877 * /
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 ;
delete from pa_transaction_interface_all
where transaction_source = 'GOLDE'
and batch_name = p_gms_batch_name;
delete from gms_transaction_interface_all
where transaction_source = 'GOLDE'
and batch_name = p_gms_batch_name;
delete from psp_enc_summary_lines
where gms_batch_name = p_gms_batch_name
and enc_control_id = p_enc_control_id;
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', 'PR', 'PO');
/ * Start bug#2142865 Added the code to update the gms_phase * /
UPDATE psp_enc_controls
SET gms_phase = 'TieBack'
WHERE run_id = g_run_id;
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 = 'A'
WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
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_lines
SET status_code = 'A'
WHERE rowid = l_rowid;
--insert_into_psp_stout( 'moving rec into enc lines hist');
INSERT INTO psp_enc_lines_history
(enc_line_id,business_group_id,enc_element_type_id,encumbrance_date,
dr_cr_flag,encumbrance_amount,status_code,enc_line_type,schedule_line_id,org_schedule_id,
default_org_account_id,suspense_org_account_id,element_account_id,gl_project_flag,
enc_summary_line_id,person_id,assignment_id,award_id,task_id,expenditure_type,
expenditure_organization_id,project_id,gl_code_combination_id,time_period_id,payroll_id,
set_of_books_id,default_reason_code,suspense_reason_code,enc_control_id,change_flag,last_update_date,
last_updated_by,last_update_login,created_by,creation_date,enc_start_date,enc_end_date,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10)
SELECT enc_line_id,business_group_id,enc_element_type_id,encumbrance_date,
dr_cr_flag,encumbrance_amount,status_code,enc_line_type,schedule_line_id,org_schedule_id,
default_org_account_id,suspense_org_account_id,element_account_id,gl_project_flag,
enc_summary_line_id,person_id,assignment_id,award_id,task_id,expenditure_type,
expenditure_organization_id,project_id,gl_code_combination_id,time_period_id,payroll_id,
set_of_books_id,default_reason_code,suspense_reason_code,enc_control_id,change_flag,last_update_date,
last_updated_by,last_update_login,created_by,creation_date,enc_start_date,enc_end_date,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10
FROM psp_enc_lines
WHERE status_code = 'A'
AND enc_summary_line_id = to_number(l_orig_trx_reference)
AND enc_control_id = p_enc_control_id;
DELETE FROM psp_enc_lines
WHERE status_code = 'A'
AND enc_summary_line_id = to_number(l_orig_trx_reference)
AND enc_control_id = p_enc_control_id;
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 transaction_source = 'GOLDE'
and batch_name = p_gms_batch_name
and transaction_status_code = 'A'
and orig_transaction_reference = l_enc_ref;
UPDATE psp_enc_lines
SET suspense_reason_code = 'ES:' || l_trx_reject_code,
status_code = 'N'
WHERE rowid = l_rowid; * /
UPDATE psp_enc_lines
SET prev_effective_date = encumbrance_date
WHERE rowid = l_rowid;
UPDATE psp_enc_lines
SET suspense_org_account_id = l_organization_account_id,
suspense_reason_code = 'ES:' || l_trx_reject_code,
gl_project_flag = l_gl_project_flag,
-- encumbrance_date = l_encumbrance_date, Commented for Bug 3194807
encumbrance_date = l_effective_date,
/ *Commented for Bug 3194807
schedule_line_id = null,
org_schedule_id = null,
default_org_account_id = null,
element_account_id = null,
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 = 'N'
WHERE rowid = l_rowid;
UPDATE psp_enc_controls
SET summ_ogm_dr_amount = l_dr_summary_amount,
summ_ogm_cr_amount = l_cr_summary_amount
--gms_phase = 'TieBack' Commented for Bug#2142865 , same moved above
WHERE enc_control_id = p_enc_control_id;
UPDATE psp_enc_lines
SET suspense_org_account_id = NULL,
suspense_reason_code = NULL,
gl_project_flag = decode(gl_code_combination_id,NULL,'P','G'),
encumbrance_date = prev_effective_date
WHERE suspense_reason_code like 'ES:%'
AND enc_summary_line_id
IN (SELECT enc_summary_line_id
FROM psp_enc_summary_lines
WHERE enc_control_id = p_enc_control_id);
UPDATE psp_enc_summary_lines
SET status_code = 'A'
WHERE enc_summary_line_id = l_enc_summary_line_id;
UPDATE psp_enc_lines
SET status_code = 'A'
WHERE enc_summary_line_id = l_enc_summary_line_id
AND enc_control_id = p_enc_control_id;
UPDATE psp_enc_lines
SET (gl_code_combination_id,project_id,task_id,award_id,expenditure_organization_id,expenditure_type)
= (select NULL,poa.project_id,poa.task_id,poa.award_id,poa.expenditure_organization_id,poa.expenditure_type
from psp_organization_accounts poa
where poa.organization_account_id = suspense_org_account_id
and enc_summary_line_id = l_enc_summary_line_id)
WHERE enc_summary_line_id= l_enc_summary_line_id
AND suspense_reason_code like 'ES:%';
INSERT INTO psp_enc_lines_history
(enc_line_id,business_group_id,enc_element_type_id,encumbrance_date,
dr_cr_flag,encumbrance_amount,status_code,enc_line_type,schedule_line_id,org_schedule_id,
default_org_account_id,suspense_org_account_id,element_account_id,gl_project_flag,
enc_summary_line_id,person_id,assignment_id,award_id,task_id,expenditure_type,
expenditure_organization_id,project_id,gl_code_combination_id,time_period_id,payroll_id,
set_of_books_id,default_reason_code,suspense_reason_code,enc_control_id,change_flag,last_update_date,
last_updated_by,last_update_login,created_by,creation_date,enc_start_date,enc_end_date,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10)
SELECT enc_line_id,business_group_id,enc_element_type_id,encumbrance_date,
dr_cr_flag,encumbrance_amount,status_code,enc_line_type,schedule_line_id,org_schedule_id,
default_org_account_id,suspense_org_account_id,element_account_id,gl_project_flag,
enc_summary_line_id,person_id,assignment_id,award_id,task_id,expenditure_type,
expenditure_organization_id,project_id,gl_code_combination_id,time_period_id,payroll_id,
set_of_books_id,default_reason_code,suspense_reason_code,enc_control_id,change_flag,last_update_date,
last_updated_by,last_update_login,created_by,creation_date,enc_start_date,enc_end_date,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10
FROM psp_enc_lines
WHERE status_code = 'A'
AND enc_summary_line_id = l_enc_summary_line_id
AND enc_control_id = p_enc_control_id;
DELETE FROM psp_enc_lines
WHERE status_code = 'A'
AND enc_summary_line_id = l_enc_summary_line_id
AND enc_control_id = p_enc_control_id;
/ * 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_controls
SET summ_ogm_dr_amount = l_dr_summary_amount,
summ_ogm_cr_amount = l_cr_summary_amount,
gms_phase = 'TieBack'
WHERE enc_control_id = p_enc_control_id;
End of comment for Create and Update multi thread enh. *****/
-- Introduced the following modified gms_enc_tie_back procedure for Create and Update multi thread enh.
PROCEDURE gms_enc_tie_back( p_payroll_action_id IN NUMBER,
p_gms_batch_name IN VARCHAR2,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
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)),
transaction_status_code
FROM pa_transaction_interface_all
WHERE transaction_source = 'GOLDE'
AND batch_name = p_gms_batch_name;
SELECT pel.rowid,
pel.encumbrance_date,
pel.enc_start_date,
pel.enc_end_date,
pel.person_id,
pel.assignment_id,
pel.payroll_id,
pel.enc_element_type_id,
pel.project_id,
pel.task_id,
pel.award_id,
pel.expenditure_organization_id,
pel.expenditure_type,
pel.suspense_org_account_id,
pesl.interface_status,
ptp.end_date
FROM psp_enc_lines pel,
psp_enc_summary_lines pesl,
per_time_periods ptp
WHERE pel.payroll_action_id = p_payroll_action_id
AND pel.enc_summary_line_id = pesl.enc_summary_line_id
AND pesl.payroll_action_id = p_payroll_action_id
AND pesl.status_code = 'R'
AND pesl.gms_batch_name = p_gms_batch_name
AND ptp.time_period_id = pel.time_period_id
ORDER BY 5, 6, 7, 8, 9, 10, 11, 12, 3;
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_lines pel
WHERE pel.rowid=p_rowid
AND pel.assignment_id = paf.assignment_id
AND pel.encumbrance_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id
AND pel.encumbrance_date BETWEEN hou.date_from AND NVL(hou.date_to, pel.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.organization_id = p_organization_id
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND poa.account_type_code = 'S'
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 organization_account_id = p_organization_account_id;
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 = 'A';
SELECT SEGMENT1
FROM pa_projects_all
WHERE project_id = l_project_id;
SELECT award_number
FROM gms_awards_all
WHERE award_id = l_award_id;
SELECT task_number
FROM pa_tasks_expend_v -- Bug : 16391366 (20/03/2013)
WHERE task_id = l_task_id;
SELECT name
FROM hr_organization_units
WHERE organization_id = l_expenditure_organization_id;
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;
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 count: '||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 count: '||sql%rowcount);
DELETE FROM psp_enc_summary_lines
WHERE 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 count: '||sql%rowcount);
UPDATE psp_enc_summary_lines
SET interface_status = r_reject_recs.reason_code(recno),
status_code = 'R'
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 = 'A'
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 ''A'' for accepted records in psp_enc_summary_lines count: '||g_bulk_row_count);
INSERT INTO psp_enc_lines_history
(enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,
dr_cr_flag, encumbrance_amount, status_code, enc_line_type,
schedule_line_id, org_schedule_id, default_org_account_id, suspense_org_account_id,
element_account_id, gl_project_flag, enc_summary_line_id, person_id,
assignment_id, award_id, task_id, expenditure_type,
expenditure_organization_id, project_id, gl_code_combination_id, time_period_id,
payroll_id, set_of_books_id, default_reason_code, suspense_reason_code,
enc_control_id, change_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date, enc_start_date,
enc_end_date, attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9, attribute10,
payroll_action_id, hierarchy_code, hierarchy_start_date, hierarchy_end_date,
orig_gl_code_combination_id, orig_project_id, orig_task_id, orig_award_id,
orig_expenditure_org_id, orig_expenditure_type)
SELECT enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,
dr_cr_flag, encumbrance_amount, 'A', enc_line_type,
schedule_line_id, org_schedule_id, default_org_account_id, suspense_org_account_id,
element_account_id, gl_project_flag, enc_summary_line_id, person_id,
assignment_id, award_id, task_id, expenditure_type,
expenditure_organization_id, project_id, gl_code_combination_id, time_period_id,
payroll_id, set_of_books_id, default_reason_code, suspense_reason_code,
enc_control_id, change_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date, enc_start_date,
enc_end_date, attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9, attribute10,
payroll_action_id, hierarchy_code, hierarchy_start_date, hierarchy_end_date,
orig_gl_code_combination_id, orig_project_id, orig_task_id, orig_award_id,
orig_expenditure_org_id, orig_expenditure_type
FROM psp_enc_lines
WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
DELETE FROM psp_enc_lines
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) || ' Deleted lines from psp_enc_lines that are summarized and trasnferred to target systems count: '||g_bulk_row_count);
UPDATE psp_enc_lines
SET prev_effective_date = encumbrance_date,
prev_enc_end_date = enc_end_date,
prev_encumbrance_amount = encumbrance_amount,
orig_gl_code_combination_id = gl_code_combination_id,
orig_project_id = project_id,
orig_task_id = task_id,
orig_award_id = award_id,
orig_expenditure_org_id = expenditure_organization_id,
orig_expenditure_type = expenditure_type
WHERE rowid = r_suspense_ac.row_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines4 count'||sql%rowcount);
UPDATE psp_enc_lines
SET suspense_org_account_id = l_organization_account_id,
gl_code_combination_id = l_susp_glccid,
project_id = l_project_id,
task_id = l_task_id,
award_id = l_award_id,
expenditure_organization_id = l_expenditure_organization_id,
expenditure_type = l_expenditure_type,
suspense_reason_code = 'ES:' || r_suspense_ac.interface_status(recno),
gl_project_flag = l_gl_project_flag,
encumbrance_date = l_effective_date,
enc_end_date = LEAST(enc_end_date, g_def_end_date),
encumbrance_amount = (encumbrance_amount * (psp_general.business_days(enc_start_date, LEAST(enc_end_date, g_def_end_date)) / psp_general.business_days(enc_start_date, enc_end_date))),
status_code = 'N'
WHERE rowid = r_suspense_ac.row_id(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines5 count'||sql%rowcount);
UPDATE psp_enc_lines
SET suspense_org_account_id = NULL,
gl_code_combination_id = orig_gl_code_combination_id,
project_id = orig_project_id,
task_id = orig_task_id,
award_id = orig_award_id,
expenditure_organization_id = orig_expenditure_org_id,
expenditure_type = orig_expenditure_type,
gl_project_flag = decode(orig_gl_code_combination_id,NULL,'P','G'),
encumbrance_date = prev_effective_date,
enc_end_date = NVL(prev_enc_end_date, enc_end_date),
encumbrance_amount = NVL(prev_encumbrance_amount, encumbrance_amount)
WHERE suspense_reason_code like 'ES:%'
AND enc_summary_line_id IN (SELECT enc_summary_line_id
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.gms_batch_name = p_gms_batch_name
AND status_code = 'R');
UPDATE psp_enc_lines
SET orig_gl_code_combination_id = NULL,
orig_project_id = NULL,
orig_task_id = NULL,
orig_award_id = NULL,
orig_expenditure_org_id = NULL,
orig_expenditure_type = NULL,
suspense_reason_code = NULL,
prev_enc_end_date = NULL,
prev_encumbrance_amount = NULL
WHERE suspense_reason_code like 'ES:%'
AND enc_summary_line_id IN (SELECT enc_summary_line_id
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.gms_batch_name = p_gms_batch_name
AND status_code = 'R');
UPDATE psp_enc_controls pec
SET 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');
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 status_code = 'A'
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 ''A'' in psp_enc_summary_lines count: '||g_bulk_row_count);
INSERT INTO psp_enc_lines_history
(enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,
dr_cr_flag, encumbrance_amount, status_code, enc_line_type,
schedule_line_id, org_schedule_id, default_org_account_id, suspense_org_account_id,
element_account_id, gl_project_flag, enc_summary_line_id, person_id,
assignment_id, award_id, task_id, expenditure_type,
expenditure_organization_id, project_id, gl_code_combination_id, time_period_id,
payroll_id, set_of_books_id, default_reason_code, suspense_reason_code,
enc_control_id, change_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date, enc_start_date,
enc_end_date, attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9, attribute10,
payroll_action_id, hierarchy_code, hierarchy_start_date, hierarchy_end_date)
SELECT enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,
dr_cr_flag, encumbrance_amount, 'A', enc_line_type,
schedule_line_id, org_schedule_id, default_org_account_id, suspense_org_account_id,
element_account_id, gl_project_flag, enc_summary_line_id, person_id,
assignment_id, award_id, task_id, expenditure_type,
expenditure_organization_id, project_id, gl_code_combination_id, time_period_id,
payroll_id, set_of_books_id, default_reason_code, suspense_reason_code,
enc_control_id, change_flag, last_update_date, last_updated_by,
last_update_login, created_by, creation_date, enc_start_date,
enc_end_date, attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9, attribute10,
payroll_action_id, hierarchy_code, hierarchy_start_date, hierarchy_end_date
FROM psp_enc_lines
WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
DELETE FROM psp_enc_lines
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) || ' Deleted lines from psp_enc_lines that are summarized and trasnferred to target systems'||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;
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
SELECT pel.enc_control_id,
pel.time_period_id,
pel.person_id,
pel.assignment_id,
pel.payroll_id,
pel.encumbrance_date,
pel.gl_code_combination_id,
pel.project_id,
pel.task_id,
pel.award_id,
pel.expenditure_type,
pel.expenditure_organization_id,
SUM(pel.encumbrance_amount),
pel.dr_cr_flag,
pel.gl_project_flag,
DECODE(g_dff_grouping_option, 'Y', pel.attribute_category, NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', pel.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pel.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pel.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pel.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pel.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pel.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pel.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pel.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pel.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pel.attribute10, NULL) attribute10,
pa.org_id
FROM PSP_ENC_LINES pel,
pa_projects_all pa
WHERE pel.ENCUMBRANCE_AMOUNT <> 0
AND pel.payroll_action_id = p_payroll_action_id
AND (g_suspense_failed = 'TRUE' OR pel.suspense_reason_code LIKE 'ES:%')
AND (g_suspense_failed = 'TRUE' OR enc_start_date <= g_def_end_date)
AND pa.project_id (+) = pel.project_id
GROUP BY pel.enc_control_id,
pel.time_period_id,
pel.person_id,
pel.assignment_id,
pel.payroll_id,
pel.encumbrance_date,
pel.gl_code_combination_id,
pel.project_id,
pel.task_id,
pel.award_id,
pel.expenditure_type,
pel.expenditure_organization_id,
pel.dr_cr_flag,
pel.gl_project_flag,
DECODE(g_dff_grouping_option, 'Y', pel.attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', pel.attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', pel.attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', pel.attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', pel.attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', pel.attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', pel.attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', pel.attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', pel.attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', pel.attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', pel.attribute10, NULL),
pa.org_id;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
SELECT psp_enc_summary_lines_s.NEXTVAL INTO t_sum_lines.enc_summary_line_id(recno) 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, task_id, award_id,
expenditure_organization_id, expenditure_type, summary_amount,
dr_cr_flag, status_code, payroll_id,
gl_project_flag,
attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8,
attribute9, attribute10, org_id,
payroll_action_id, last_update_date, last_updated_by,
last_update_login, created_by, creation_date)
VALUES (t_sum_lines.enc_summary_line_id(recno), g_business_group_id,
t_sum_lines.enc_control_id(recno), t_sum_lines.time_period_id(recno),
t_sum_lines.person_id(recno), t_sum_lines.assignment_id(recno),
t_sum_lines.effective_date(recno), g_set_of_books_id,
t_sum_lines.gl_code_combination_id(recno), t_sum_lines.project_id(recno),
t_sum_lines.task_id(recno), t_sum_lines.award_id(recno),
t_sum_lines.expenditure_organization_id(recno), t_sum_lines.expenditure_type(recno),
t_sum_lines.summary_amount(recno), t_sum_lines.dr_cr_flag(recno),
'N', t_sum_lines.payroll_id(recno), t_sum_lines.gl_project_flag(recno),
t_sum_lines.attribute_category(recno), t_sum_lines.attribute1(recno),
t_sum_lines.attribute2(recno), t_sum_lines.attribute3(recno),
t_sum_lines.attribute4(recno), t_sum_lines.attribute5(recno),
t_sum_lines.attribute6(recno), t_sum_lines.attribute7(recno),
t_sum_lines.attribute8(recno), t_sum_lines.attribute9(recno),
t_sum_lines.attribute10(recno), t_sum_lines.org_id(recno),
p_payroll_action_id, SYSDATE,
l_last_updated_by, l_last_update_login,
l_last_updated_by, SYSDATE);
UPDATE psp_enc_lines pel
SET enc_summary_line_id = t_sum_lines.enc_summary_line_id(recno)
WHERE payroll_action_id = p_payroll_action_id
AND pel.enc_control_id = t_sum_lines.enc_control_id(recno)
AND pel.time_period_id = t_sum_lines.time_period_id(recno)
AND pel.person_id = t_sum_lines.person_id(recno)
AND pel.encumbrance_date = t_sum_lines.effective_date(recno)
AND NVL(pel.gl_code_combination_id, -99) = NVL(t_sum_lines.gl_code_combination_id(recno), -99)
AND NVL(pel.project_id, -99) = NVL(t_sum_lines.project_id(recno), -99)
AND NVL(pel.task_id, -99) = NVL(t_sum_lines.task_id(recno), -99)
AND NVL(pel.award_id, -99) = NVL(t_sum_lines.award_id(recno), -99)
AND NVL(pel.expenditure_type, 'NULL') = NVL(t_sum_lines.expenditure_type(recno), 'NULL')
AND NVL(pel.expenditure_organization_id, -99) = NVL(t_sum_lines.expenditure_organization_id(recno), -99)
AND pel.dr_cr_flag = t_sum_lines.dr_cr_flag(recno)
AND pel.gl_project_flag = t_sum_lines.gl_project_flag(recno)
AND NVL(pel.attribute_category, 'NULL') = NVL(t_sum_lines.attribute_category(recno), 'NULL')
AND NVL(pel.attribute1, 'NULL') = NVL(t_sum_lines.attribute1(recno), 'NULL')
AND NVL(pel.attribute2, 'NULL') = NVL(t_sum_lines.attribute2(recno), 'NULL')
AND NVL(pel.attribute3, 'NULL') = NVL(t_sum_lines.attribute3(recno), 'NULL')
AND NVL(pel.attribute4, 'NULL') = NVL(t_sum_lines.attribute4(recno), 'NULL')
AND NVL(pel.attribute5, 'NULL') = NVL(t_sum_lines.attribute5(recno), 'NULL')
AND NVL(pel.attribute6, 'NULL') = NVL(t_sum_lines.attribute6(recno), 'NULL')
AND NVL(pel.attribute7, 'NULL') = NVL(t_sum_lines.attribute7(recno), 'NULL')
AND NVL(pel.attribute8, 'NULL') = NVL(t_sum_lines.attribute8(recno), 'NULL')
AND NVL(pel.attribute9, 'NULL') = NVL(t_sum_lines.attribute9(recno), 'NULL')
AND NVL(pel.attribute10, 'NULL') = NVL(t_sum_lines.attribute10(recno), 'NULL');
UPDATE psp_enc_lines pel
SET enc_summary_line_id = t_sum_lines.enc_summary_line_id(recno)
WHERE payroll_action_id = p_payroll_action_id
AND pel.enc_control_id = t_sum_lines.enc_control_id(recno)
AND pel.time_period_id = t_sum_lines.time_period_id(recno)
AND pel.person_id = t_sum_lines.person_id(recno)
AND pel.encumbrance_date = t_sum_lines.effective_date(recno)
AND NVL(pel.gl_code_combination_id, -99) = NVL(t_sum_lines.gl_code_combination_id(recno), -99)
AND NVL(pel.project_id, -99) = NVL(t_sum_lines.project_id(recno), -99)
AND NVL(pel.task_id, -99) = NVL(t_sum_lines.task_id(recno), -99)
AND NVL(pel.award_id, -99) = NVL(t_sum_lines.award_id(recno), -99)
AND NVL(pel.expenditure_type, 'NULL') = NVL(t_sum_lines.expenditure_type(recno), 'NULL')
AND NVL(pel.expenditure_organization_id, -99) = NVL(t_sum_lines.expenditure_organization_id(recno), -99)
AND pel.dr_cr_flag = t_sum_lines.dr_cr_flag(recno)
AND pel.gl_project_flag = t_sum_lines.gl_project_flag(recno);
PROCEDURE update_hierarchy_dates (p_payroll_action_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2) IS
CURSOR hierarchy_dates_cur (p_assignment_id IN NUMBER,
p_payroll_id IN NUMBER) IS
SELECT DISTINCT enc_element_type_id,
hierarchy_code,
NVL(gl_code_combination_id, -99),
NVL(project_id, -99),
NVL(task_id, -99),
NVL(award_id, -99),
NVL(expenditure_organization_id, -99),
NVL(expenditure_type, '-99'),
enc_start_date,
enc_end_date
FROM psp_enc_lines_history pelh
WHERE change_flag = 'N'
AND assignment_id = p_assignment_id
AND payroll_id = p_payroll_id
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering UPDATE_HIERARCHY_DATES');
UPDATE psp_enc_lines_history pelh
SET change_flag = 'N'
WHERE EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.enc_summary_line_id = pelh.enc_summary_line_id
AND status_code = 'A')
AND assignment_id = l_asgs.assignment_id(asg_no)
AND payroll_id = l_asgs.payroll_id(asg_no);
UPDATE psp_enc_lines_history pelh
SET hierarchy_code = 'SA'
WHERE change_flag = 'N'
AND hierarchy_code <> 'SA'
AND suspense_org_account_id IS NOT NULL
AND assignment_id = l_asgs.assignment_id(asg_no)
AND payroll_id = l_asgs.payroll_id(asg_no);
t_enc_lines.assignment_id.DELETE;
t_enc_lines.payroll_id.DELETE;
t_enc_lines.element_type_id.DELETE;
t_enc_lines.hierarchy_code.DELETE;
t_enc_lines.gl_ccid.DELETE;
t_enc_lines.project_id.DELETE;
t_enc_lines.task_id.DELETE;
t_enc_lines.award_id.DELETE;
t_enc_lines.exp_org_id.DELETE;
t_enc_lines.exp_type.DELETE;
t_enc_lines.enc_start_date.DELETE;
t_enc_lines.enc_end_date.DELETE;
UPDATE psp_enc_lines_history
SET hierarchy_start_date = t_enc_nlines.enc_start_date(recno),
hierarchy_end_date = t_enc_nlines.enc_end_date(recno)
WHERE assignment_id = t_enc_nlines.assignment_id(recno)
AND payroll_id = t_enc_nlines.payroll_id(recno)
AND change_flag = 'N'
AND enc_element_type_id = t_enc_nlines.element_type_id(recno)
AND hierarchy_code = t_enc_nlines.hierarchy_code(recno)
AND ( ( gl_code_combination_id IS NOT NULL
AND gl_code_combination_id = t_enc_nlines.gl_ccid(recno))
OR ( project_id = t_enc_nlines.project_id(recno)
AND task_id = t_enc_nlines.task_id(recno)
AND award_id = t_enc_nlines.award_id(recno)
AND expenditure_organization_id = t_enc_nlines.exp_org_id(recno)
AND expenditure_type = t_enc_nlines.exp_type(recno)))
AND enc_start_date <= t_enc_nlines.enc_end_date(recno)
AND enc_end_date >= t_enc_nlines.enc_start_date(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
g_error_api_path := SUBSTR(' UPDATE_HIERARCHY_DATES:'||g_error_api_path,1,230);
fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN', ' UPDATE_HIERARCHY_DATES');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
END update_hierarchy_dates;
SELECT assignment_number,
person_id,
organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND payroll_id = p_payroll_id
AND effective_end_date >= p_effective_date
AND ROWNUM = 1;
SELECT payroll_name
FROM pay_all_payrolls_f
WHERE payroll_id = p_payroll_id
AND business_group_id = g_business_group_id
AND gl_set_of_books_id = g_set_of_books_id;
SELECT full_name
FROM per_all_people_f
WHERE person_id = l_person_id
AND effective_end_date >= p_effective_date
AND ROWNUM = 1;
SELECT name
FROM hr_organization_units
WHERE organization_id = l_organization_id;
SELECT SEGMENT1
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT award_number
FROM gms_awards_all
WHERE award_id = p_award_id;
SELECT task_number
FROM pa_tasks_expend_v -- Bug 16391366
WHERE task_id = p_task_id;
SELECT name
FROM hr_organization_units
WHERE organization_id = p_expenditure_org_id;
SELECT element_name
FROM pay_element_types_f
WHERE element_type_id = p_element_type_id
AND ROWNUM = 1;
INSERT INTO PSP_ENC_SUMMARY_LINES_ARCH
(enc_summary_line_id, business_group_id, gms_batch_name,
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, group_id, interface_status,
payroll_id, gl_period_id, gl_project_flag,
attribute_category, 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, reject_reason_code, enc_control_id,
status_code, last_update_date, last_updated_by,
last_update_login, created_by, creation_date,
suspense_org_account_id, superceded_line_id, gms_posting_override_date,
gl_posting_override_date, expenditure_id, expenditure_item_id,
expenditure_ending_date, interface_id, txn_interface_id,
payroll_action_id, liquidate_request_id, proposed_termination_date,
update_flag)
SELECT enc_summary_line_id, business_group_id, gms_batch_name,
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, group_id, interface_status,
payroll_id, gl_period_id, gl_project_flag,
attribute_category, 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, reject_reason_code, enc_control_id,
status_code, last_update_date, last_updated_by,
last_update_login, created_by, creation_date,
suspense_org_account_id, superceded_line_id, gms_posting_override_date,
gl_posting_override_date, expenditure_id, expenditure_item_id,
expenditure_ending_date, interface_id, txn_interface_id,
payroll_action_id, liquidate_request_id, proposed_termination_date,
update_flag
FROM psp_enc_summary_lines
WHERE payroll_action_id = p_payroll_action_id
AND status_code = 'R';
DELETE psp_enc_summary_lines
WHERE payroll_action_id = p_payroll_action_id
AND status_code = 'R';
fnd_file.put_line(fnd_file.log, 'Deleted rows from psp_enc_summary_lines with rejection code R'||sql%rowcount);