[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_global_earning_elements
(p_pre_process_mode IN VARCHAR2,
p_payroll_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_max_pay_date IN DATE,
p_return_status OUT NOCOPY VARCHAR2);
PROCEDURE update_schedules (p_pre_process_mode IN VARCHAR2,
p_payroll_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_max_pay_date IN DATE,
p_return_status OUT NOCOPY VARCHAR2);
PROCEDURE update_default_susp_accounts
(p_pre_process_mode IN VARCHAR2,
p_payroll_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_max_pay_date IN DATE,
p_return_status OUT NOCOPY VARCHAR2);
PROCEDURE update_default_labor_schedules
(p_pre_process_mode IN VARCHAR2,
p_payroll_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_max_pay_date IN DATE,
p_return_status OUT NOCOPY VARCHAR2);
PROCEDURE insert_changed_assignments
(p_change_type IN VARCHAR2,
p_reference_id IN NUMBER DEFAULT NULL,
p_action_type IN VARCHAR2 DEFAULT NULL,
p_return_status OUT NOCOPY VARCHAR2);
/* Cursor for selecting maximum payroll date of the payroll */
CURSOR payroll_date_cur IS
SELECT max(date_earned) from pay_payroll_actions
WHERE payroll_id = p_payroll_id
AND action_type = 'R'
AND action_status = 'C';
SELECT min(start_date)
FROM per_time_periods
WHERE payroll_id = p_payroll_id;
delete from psp_enc_changed_assignments a
where exists
(select 1 from per_all_assignments_f b
where b.assignment_id = a.assignment_id
and b.effective_end_date = to_date('31-12-4712','DD-MM-RRRR'))
and a.chk_asg_end_date_flag = 'Y';
update_global_earning_elements (p_pre_process_mode => p_pre_process_mode,
p_payroll_id => p_payroll_id,
p_business_group_id => p_business_group_id,
p_set_of_books_id => p_set_of_books_id,
p_max_pay_date => l_max_pay_date,
p_return_status => p_return_status);
update_schedules (p_pre_process_mode => p_pre_process_mode,
p_payroll_id => p_payroll_id,
p_business_group_id => p_business_group_id,
p_set_of_books_id => p_set_of_books_id,
p_max_pay_date => l_max_pay_date,
p_return_status => p_return_status);
update_default_labor_schedules (p_pre_process_mode => p_pre_process_mode,
p_payroll_id => p_payroll_id,
p_business_group_id => p_business_group_id,
p_set_of_books_id => p_set_of_books_id,
p_max_pay_date => l_max_pay_date,
p_return_status => p_return_status);
update_default_susp_accounts (p_pre_process_mode => p_pre_process_mode,
p_payroll_id => p_payroll_id,
p_business_group_id => p_business_group_id,
p_set_of_books_id => p_set_of_books_id,
p_max_pay_date => l_max_pay_date,
p_return_status => p_return_status);
Procedure Name: UPDATE_GLOBAL_EARNING_ELEMENTS
Purpose: This procedure is called in the main procedure and used for updating poeta
dates in psp_element_type_accounts for poeta CI It inserts assignments in
psp_enc_changed_assignments if the poeta dates are different from previous
poeta dates and assignments exists in psp_enc_lines_history table.
****************************************************************************************************/
PROCEDURE update_global_earning_elements (p_pre_process_mode IN VARCHAR2,
p_payroll_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_max_pay_date IN DATE,
p_return_status OUT NOCOPY VARCHAR2)
IS
TYPE global_lines_rec is RECORD (
r_global_line_id v_line_id,
r_project_id v_project_id,
r_task_id v_task_id,
r_expenditure_organization_id v_exp_org,
r_expenditure_type v_exp_type,
r_award_id v_award_id,
r_start_date_active v_start_dt,
r_end_date_active v_end_dt,
r_poeta_start_date v_start_dt,
r_poeta_end_date v_end_dt);
l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'UPDATE_GLOBAL_EARNING_ELEMENTS';
SELECT peta.element_account_id,
peta.project_id,
peta.task_id,
peta.expenditure_organization_id,
peta.expenditure_type,
peta.award_id,
peta.start_date_active,
peta.end_date_active,
NVL(peta.poeta_start_date, TO_DATE('01-01-1800', 'DD-MM-YYYY')) poeta_start_date,
NVL(peta.poeta_end_date,TO_DATE('31-12-4712', 'DD-MM-YYYY')) poeta_end_date
FROM psp_element_type_accounts peta
WHERE peta.gl_code_combination_id is NULL
AND peta.end_date_active >= p_max_pay_date
AND peta.business_group_id = p_business_group_id
AND peta.set_of_books_id = p_set_of_books_id
ORDER BY 2,3,4,5,6;
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh
WHERE pelh.element_account_id = r_global_control_rec.r_global_line_id (j)
AND pelh.suspense_org_account_id is NULL
AND pelh.change_flag = 'N';
/* Insert assignments (exists in psp_enc_lines_history and poeta dates
are changed) into psp_enc_changed_assignments */
IF r_asg_id_array.r_asg_id.count<>0 THEN
insert_changed_assignments (p_change_type => 'PT',
p_return_status => p_return_status);
UPDATE psp_element_type_accounts
SET poeta_start_date = r_global_control_rec.r_poeta_start_date(i),
poeta_end_date = r_global_control_rec.r_poeta_end_date(i)
WHERE element_account_id = r_global_control_rec.r_global_line_id(i);
END update_global_earning_elements;
Procedure Name: UPDATE_SCHEDULES
Purpose: This procedure is called in the main procedure and used for updating poeta
dates psp_schedule_lines for poeta CI. It inserts assignments in
psp_enc_changed_assignments if the poeta dates are different from previous
poeta dates and assignments exist in psp_enc_lines_history table.
****************************************************************************************************/
PROCEDURE update_schedules (p_pre_process_mode IN VARCHAR2,
p_payroll_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_max_pay_date IN DATE,
p_return_status OUT NOCOPY VARCHAR2)
IS
TYPE schedule_lines_rec is RECORD (
r_assignment_id v_assignment_id,
r_payroll_id v_payroll_id,
r_schedule_line_id v_line_id,
r_project_id v_project_id,
r_task_id v_task_id,
r_expenditure_organization_id v_exp_org,
r_expenditure_type v_exp_type,
r_award_id v_award_id,
r_schedule_begin_date v_start_dt,
r_schedule_end_date v_end_dt,
r_poeta_start_date v_start_dt,
r_poeta_end_date v_end_dt);
l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'UPDATE_SCHEDULES';
SELECT psh.assignment_id,
paf.payroll_id,
psl.schedule_line_id,
psl.project_id,
psl.task_id,
psl.expenditure_organization_id,
psl.expenditure_type,
psl.award_id,
psl.schedule_begin_date,
psl.schedule_end_date,
NVL(psl.poeta_start_date,TO_DATE('01-01-1800', 'DD-MM-YYYY')) poeta_start_date,
NVL(psl.poeta_end_date,TO_DATE('31-12-4712', 'DD-MM-YYYY')) poeta_start_date
FROM psp_schedule_hierarchy psh,
psp_schedule_lines psl ,
per_assignments_f paf
WHERE psl.business_group_id = p_business_group_id
AND psl.set_of_books_id = p_set_of_books_id
AND psl.schedule_hierarchy_id = psh.schedule_hierarchy_id
AND psl.schedule_end_date >= p_max_pay_date
AND psl.gl_code_combination_id IS NULL
AND psh.assignment_id = paf.assignment_id
AND psl.schedule_begin_date <= paf.effective_end_date
AND psl.schedule_end_date >= paf.effective_start_date
AND paf.period_of_service_id IS NOT NULL
and paf.payroll_id = p_payroll_id --bug fix 2597666 Modified NOT NULL check to current payroll check for bug fix 3099540
AND paf.effective_end_date >= p_max_pay_date -- Introduced for bug fix 3099540 Corrected for bug fix 3434626
ORDER BY 4,5,6,7,8;
insert_changed_assignments (p_change_type => 'PT',
p_return_status => p_return_status);
UPDATE psp_schedule_lines
SET poeta_end_date = r_schedule_control_rec.r_poeta_end_date(i),
poeta_start_date = r_schedule_control_rec.r_poeta_start_date(i)
WHERE schedule_line_id = r_schedule_control_rec.r_schedule_line_id(i);
/* Inserting into psp_enc_changed_assignments */
IF j> 1 then -- bug fix 2597666
insert_changed_assignments (p_change_type => 'PT',
p_return_status => p_return_status);
END update_schedules;
Procedure Name: update_default_labor_schedules
Purpose: This procedure is called in the main procedure and used for updating poeta
dates in psp_org_default_labor_schedules table for poeta CI. It inserts
assignments in psp_enc_changed_assignments table if the poeta dates are
different from previous poeta dates and assignments exists in
psp_enc_lines_history table.
****************************************************************************************************/
PROCEDURE update_default_labor_schedules (p_pre_process_mode IN VARCHAR2,
p_payroll_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_max_pay_date IN DATE,
p_return_status OUT NOCOPY VARCHAR2)
IS
TYPE ls_lines_rec is RECORD (
r_org_schedule_id v_line_id,
r_project_id v_project_id,
r_task_id v_task_id,
r_expenditure_organization_id v_exp_org,
r_expenditure_type v_exp_type,
r_award_id v_award_id,
r_start_date_active v_start_dt,
r_end_date_active v_end_dt,
r_poeta_start_date v_start_dt,
r_poeta_end_date v_end_dt);
/* Cursor will select distinct poeta combinations from psp_default_labor_schedules for a payroll. */
CURSOR org_labor_schedule_cur IS
SELECT pdls.org_schedule_id,
pdls.project_id,
pdls.task_id,
pdls.expenditure_organization_id,
pdls.expenditure_type,
pdls.award_id,
pdls.schedule_begin_date,
pdls.schedule_end_date,
NVL(pdls.poeta_start_date,TO_DATE('01-01-1800', 'DD-MM-YYYY')) poeta_start_date,
NVL(pdls.poeta_end_date,TO_DATE('31-12-4712', 'DD-MM-YYYY')) poeta_end_date
FROM psp_default_labor_schedules pdls
WHERE pdls.business_group_id = p_business_group_id
AND pdls.set_of_books_id = p_set_of_books_id
AND pdls.gl_code_combination_id IS NULL
AND pdls.schedule_end_date >= p_max_pay_date
ORDER BY 2,3,4,5,6;
SELECT DISTINCT pelh.assignment_id,pelh.payroll_id
FROM psp_enc_lines_history pelh
-- Modified default_org_account_id to org_schedule_id for bug 2334434
WHERE pelh.org_schedule_id = r_ls_control_rec.r_org_schedule_id (j)
AND pelh.suspense_org_account_id IS NULL
AND pelh.change_flag = 'N';
l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'UPDATE_DEFAULT_LABOR_SCHEDULES';
/* Insert assignments (exists in psp_enc_lines_history and poeta dates are
changed) into psp_enc_changed_assignments table*/
IF r_asg_id_array.r_asg_id.count<>0 THEN
insert_changed_assignments (p_change_type => 'PT',
p_return_status => p_return_status);
UPDATE psp_default_labor_schedules
SET poeta_end_date = r_ls_control_rec.r_poeta_end_date(i),
poeta_start_date = r_ls_control_rec.r_poeta_start_date(i)
WHERE org_schedule_id = r_ls_control_rec.r_org_schedule_id(i);
END update_default_labor_schedules;
Procedure Name: UPDATE_DEFAULT_SUSP_ACCOUNTS
Purpose: This procedure is called in the main procedure and used for updating poeta
dates in psp_organization_accounts for poeta CI. It inserts assignments in
psp_enc_changed_assignments table if the poeta dates are different from
previous poeta dates and assignments exists in psp_enc_lines_history
table.
****************************************************************************************************/
PROCEDURE update_default_susp_accounts (p_pre_process_mode IN VARCHAR2,
p_payroll_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_max_pay_date IN DATE,
p_return_status OUT NOCOPY VARCHAR2)
IS
TYPE susp_lines_rec is RECORD
(r_organization_account_id v_line_id,
r_project_id v_project_id,
r_task_id v_task_id,
r_expenditure_organization_id v_exp_org,
r_expenditure_type v_exp_type,
r_award_id v_award_id,
r_start_date_active v_start_dt,
r_end_date_active v_end_dt,
r_poeta_start_date v_start_dt,
r_poeta_end_date v_end_dt);
SELECT poa.organization_account_id,
poa.project_id,
poa.task_id,
poa.expenditure_organization_id,
poa.expenditure_type,
poa.award_id,
poa.start_date_active,
poa.end_date_active,
NVL(poa.poeta_start_date,TO_DATE('01-01-1800', 'DD-MM-YYYY')) poeta_start_date,
NVL(poa.poeta_end_date,TO_DATE('31-12-4712', 'DD-MM-YYYY')) poeta_end_date
FROM psp_organization_accounts poa
WHERE poa.gl_code_combination_id IS NULL
AND poa.end_date_active >= p_max_pay_date
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
ORDER BY 2,3,4,5,6;
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh
WHERE NVL(suspense_org_account_id, default_org_account_id) = r_susp_control_rec.r_organization_account_id (j)
AND pelh.change_flag = 'N';
l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'UPDATE_DEFAULT_SUSP_ACCOUNTS';
/* Insert assignments (exists in psp_enc_lines_history and poeta dates are
changed) into psp_enc_changed_assignments table */
IF r_asg_id_array.r_asg_id.count<>0 THEN
insert_changed_assignments (p_change_type => 'PT',
p_return_status => p_return_status);
UPDATE psp_organization_accounts
SET poeta_end_date = r_susp_control_rec.r_poeta_end_date(i),
poeta_start_date = r_susp_control_rec.r_poeta_start_date(i)
WHERE organization_account_id = r_susp_control_rec.r_organization_account_id(i);
END update_default_susp_accounts;
is made use of by other procedures to update respective schedule lines.
Further certain date variant checks are taken care by date invariant
validations.
Additional Information:
Project_info_cur takes care of project - task link, project status, chargeable_flag etc.
Award_info_cur takes care of award and project link, award is active and other
award related validations
Expenditure_type_info_cur takes care of validation for expenditure types
Exp_org_cur takes care of expenditure organization validations
****************************************************************************************************/
PROCEDURE validate_poeta (p_project_id IN NUMBER,
p_task_id IN NUMBER,
p_award_id IN NUMBER,
p_expenditure_type IN VARCHAR2,
p_expenditure_organization_id IN NUMBER,
p_payroll_id IN NUMBER,
p_start_date OUT NOCOPY DATE,
p_end_date OUT NOCOPY DATE,
p_return_status OUT NOCOPY VARCHAR2)
IS
l_proj_start_date DATE DEFAULT fnd_date.canonical_to_date('1800/01/01');
SELECT ppa.project_status_code,
NVL(ppa.start_date, TO_DATE('1800/01/01', 'YYYY/MM/DD')),
NVL(ppa.completion_date, TO_DATE('4712/12/31', 'YYYY/MM/DD')),
NVL(pt.start_date, TO_DATE('1800/01/01', 'YYYY/MM/DD')),
NVL(pt.completion_date, TO_DATE('4712/12/31', 'YYYY/MM/DD'))
FROM pa_tasks pt,
pa_projects_all ppa
WHERE pt.task_id = p_task_id
AND ppa.project_id = pt.project_id
AND ppa.project_id = p_project_id
AND ppa.project_status_code <> 'CLOSED'
AND pt.chargeable_flag = 'Y';
SELECT NVL(gaw.preaward_date, NVL(gaw.start_date_active, TO_DATE('1800/01/01', 'YYYY/MM/DD'))),
NVL(gaw.end_date_active, TO_DATE('4712/12/31', 'YYYY/MM/DD')),
allowable_schedule_id
FROM gms_awards_all gaw, --6957888
gms_summary_project_fundings gspf,
gms_installments gi,
gms_budget_versions gbv,
pa_tasks pt
WHERE gaw.award_id = p_award_id
AND gbv.project_id = p_project_id
AND pt.task_id = p_task_id
AND gbv.budget_status_code = 'B'
AND gaw.status <>'CLOSED'
AND gspf.project_id = gbv.project_id
AND ((gspf.task_id = pt.task_id) OR (gspf.task_id IS NULL) OR (gspf.task_id = pt.top_task_id))
AND gi.installment_id = gspf.installment_id
AND gi.award_id = gaw.award_id
AND gaw.award_template_flag = 'DEFERRED';
SELECT NVL(pet.start_date_active, TO_DATE('1800/01/01', 'YYYY/MM/DD')),
NVL(pet.end_date_active, TO_DATE('4712/12/31', 'YYYY/MM/DD'))
FROM gms_allowable_expenditures gae,
pa_expenditure_types pet
WHERE pet.expenditure_type = p_expenditure_type
AND gae.expenditure_type = pet.expenditure_type
AND gae.allowability_schedule_id = l_allowable_schedule_id;
SELECT NVL(poe.date_from, TO_DATE('1800/01/01', 'YYYY/MM/DD')),
NVL(poe.date_to, TO_DATE('4712/12/31', 'YYYY/MM/DD'))
FROM pa_organizations_expend_v poe
WHERE poe.organization_id = p_expenditure_organization_id;
SELECT ppsc.enabled_flag
FROM pa_project_status_controls ppsc
WHERE ppsc.project_status_code = l_proj_status_code
AND ppsc.action_code = 'NEW_TXNS';
/*CURSOR for selecting maximum creation date from psp_enc_controls for which lines are not liquidated*/
CURSOR max_create_dt_cur IS
SELECT max(pec.creation_date)
FROM PSP_ENC_CONTROLS pec
WHERE pec.payroll_id = p_payroll_id
AND pec.action_code IN ('I', 'N', 'P'); -- Replaced <> 'L' with IN clause for bug fix 3099540
SELECT patc.project_id
FROM pa_transaction_controls patc
WHERE patc.project_id = p_project_id
AND patc.last_update_date > l_enc_cr_date
AND rownum = 1;
SELECT pelh.assignment_id, p_payroll_id
FROM psp_enc_lines_history pelh
WHERE pelh.project_id = p_project_id
AND pelh.payroll_id = p_payroll_id
AND pelh.change_flag = 'N' --Added for bug 2334434
GROUP BY pelh.assignment_id,p_payroll_id;
insert_changed_assignments (p_change_type => 'TC',
p_return_status => p_return_status);
Procedure Name: INSERT_CHANGED_ASSIGNMENTS
Purpose: This Procedure inserts identified assignments into psp_enc_changed_assignments
table.
****************************************************************************************************/
PROCEDURE insert_changed_assignments (p_change_type IN VARCHAR2,
p_reference_id IN NUMBER DEFAULT NULL,
p_action_type IN VARCHAR2 DEFAULT NULL,
p_return_status OUT NOCOPY VARCHAR2)
IS
-- Introduced for bug fix 3434626
l_proc_name VARCHAR2(61) DEFAULT g_package_name || 'INSERT_CHANGED_ASSIGNMENTS';
INSERT INTO PSP_ENC_CHANGED_ASSIGNMENTS
(request_id, assignment_id, payroll_id,
change_type, processed_flag, reference_id, action_type)
VALUES (g_request_id, r_asg_id_array.r_asg_id(k), r_asg_id_array.r_payroll_id(k),
p_change_type, NULL, p_reference_id, p_action_type);
r_asg_id_array.r_asg_id.delete; -- clear the array
r_asg_id_array.r_payroll_id.delete;
g_error_api_path := SUBSTR('INSERT_CHANGED_ASSIGNMENTS:'||g_error_api_path,1,30);
END insert_changed_assignments;
Update run because of the changes in LS other than employee level and as well
as in Enumbrance Payroll selection, Element selection forms.
****************************************************************************************************/
PROCEDURE labor_schedule_pre_process (p_enc_line_type IN VARCHAR2,
p_payroll_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT DISTINCT reference_id,
pecs.reference_field,
pecs.change_type,
DECODE(action_type, 'D', 'U', action_type) action_type
FROM psp_enc_changed_schedules pecs;
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.org_schedule_id = p_reference_id;
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh
WHERE pelh.default_org_account_id = p_reference_id
AND pelh.change_flag = 'N' ; --Added for bug 2334434;
Same query is used for GS updates, hence this cursor will be reused */
CURSOR assignment_payroll_sa_upd_cur (p_reference_id NUMBER) IS
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.suspense_org_account_id = p_reference_id;
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.element_account_id = p_reference_id;
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.assignment_id = p_reference_id;
CURSOR global_element_insert_cur (p_reference_id NUMBER) IS
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh
WHERE pelh.enc_element_type_id = p_reference_id
AND pelh.element_account_id IS NULL
AND pelh.change_flag = 'N';
/* Following cursor finds all assignments impacted because of Org. Default LS Inserts */
CURSOR org_ds_insert_cur (p_reference_id NUMBER) IS
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh,
per_assignments_f paf
WHERE pelh.schedule_line_id IS NULL
AND pelh.element_account_id IS NULL
AND pelh.change_flag = 'N'
AND pelh.assignment_id = paf.assignment_id
AND paf.organization_id = p_reference_id
/* Following code is modified for bug 2345584 */
AND (default_reason_code IN(3,1)
OR suspense_reason_code IN('LDM_NO_CI_FOUND','LDM_BAL_NOT_100_PERCENT'));
/* Following cursor would identify assignments impacted by Org. Default Account Inserts */
CURSOR org_da_insert_cur (p_reference_id NUMBER) IS
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh,
per_assignments_f paf
WHERE pelh.suspense_org_account_id IS NOT NULL
AND pelh.suspense_reason_code IN ('LDM_NO_CI_FOUND', 'LDM_BAL_NOT_100_PERCENT')
AND paf.organization_id = p_reference_id
AND pelh.assignment_id = paf.assignment_id
AND pelh.change_flag = 'N';
CURSOR org_sa_insert_cur (p_reference_id NUMBER) IS
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh,
per_assignments_f paf,
psp_organization_accounts poa
WHERE pelh.suspense_org_account_id = poa.organization_account_id
AND poa.organization_id = l_generic_suspense
AND pelh.change_flag = 'N'
AND pelh.assignment_id = paf.assignment_id
AND paf.organization_id = p_reference_id;
SELECT period_end_date,
prev_enc_end_date
FROM psp_enc_end_dates peed
WHERE peed.enc_end_date_id = p_reference_id;
SELECT DISTINCT
assignment_id,
payroll_id
FROM psp_enc_lines_history pelh
-- psp_enc_end_dates peed Commented for bug fix 4507892
WHERE pelh.change_flag = 'N'
AND pelh.encumbrance_date > l_period_end_date;
SELECT DISTINCT --Added distinct for bug 2664991.
pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh,
psp_default_labor_schedules pdls,
psp_schedule_lines psl,
psp_element_type_accounts peta,
per_assignments_f paf
-- psp_enc_end_dates peed Commented for bug fix 3426871
--WHERE peed.enc_end_date_id = p_reference_id Commented for bug fix 3426871
WHERE pelh.assignment_id = paf.assignment_id
AND pelh.change_flag = 'N'
AND pelh.gl_code_combination_id IS NULL
AND pelh.encumbrance_date = (SELECT MIN(pelhin.encumbrance_date)
FROM psp_enc_lines_history pelhin
WHERE pelhin.assignment_id = pelh.assignment_id
-- AND pelhin.encumbrance_date > peed.period_end_date)
-- AND pelhin.encumbrance_date > peed.prev_enc_end_date) --Added for bug 2396983. Commented for bug 3426871
AND pelhin.encumbrance_date > l_prev_enc_end_date) -- Introduced for bug fix 3426871
AND pelh.org_schedule_id = pdls.org_schedule_id (+)
AND pelh.element_account_id = peta.element_account_id (+)
AND pelh.schedule_line_id = psl.schedule_line_id (+)
AND pelh.encumbrance_date BETWEEN paf.effective_start_date AND paf.effective_end_date --Added for Bug 2675446
GROUP BY pelh.assignment_id, pelh.payroll_id,
pelh.enc_element_type_id --Added for bug 2664991.
HAVING SUM(NVL(pdls.schedule_percent, 0) + NVL(psl.schedule_percent, 0) + NVL(peta.percent, 0)) <> 100
AND SUM(NVL(pdls.schedule_percent, 0) + NVL(psl.schedule_percent, 0) + NVL(peta.percent, 0)) > 0;
SELECT DISTINCT pelh.assignment_id,
pelh.payroll_id
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.enc_element_type_id = p_reference_id;
UPDATE psp_enc_changed_assignments peca
SET request_id = g_request_id
WHERE peca.payroll_id = p_payroll_id
AND change_type IN ('AS', 'ET', 'LS', 'QU');
UPDATE psp_enc_changed_assignments peca
SET request_id = g_request_id
WHERE peca.payroll_id = p_payroll_id;
insert_changed_assignments(p_change_type => 'DA',
p_reference_id => r_reference_id_array.r_reference_id(i),
p_action_type => r_reference_id_array.r_action_type(i),
p_return_status => p_return_status);
/* Verifying Org. Default Account Inserts */
ELSIF (r_reference_id_array.r_change_type(i) = 'DA') AND
(r_reference_id_array.r_action_type(i) = 'I') THEN
l_proc_step := 70 + (I/100000); -- Introduced for bug fix 3434626
OPEN org_da_insert_cur(r_reference_id_array.r_reference_id(i));
FETCH org_da_insert_cur
BULK COLLECT INTO r_asg_id_array.r_asg_id,
r_asg_id_array.r_payroll_id;
CLOSE org_da_insert_cur;
insert_changed_assignments(p_change_type => 'DA',
p_reference_id => r_reference_id_array.r_reference_id(i),
p_action_type => r_reference_id_array.r_action_type(i),
p_return_status => p_return_status);
This section also takes care of GS updates */
ELSIF (r_reference_id_array.r_change_type(i) IN ('GS', 'SA')) AND
(r_reference_id_array.r_action_type(i) = 'U') THEN
l_proc_step := 80 + (I/100000); -- Introduced for bug fix 3434626
insert_changed_assignments(p_change_type => r_reference_id_array.r_change_type(i),
p_reference_id => r_reference_id_array.r_reference_id(i),
p_action_type => r_reference_id_array.r_action_type(i),
p_return_status => p_return_status);
/* Verifying Org. Suspense Account Inserts */
ELSIF (r_reference_id_array.r_change_type(i) = 'SA') AND
(r_reference_id_array.r_action_type(i) = 'I') THEN
l_proc_step := 90 + (I/100000); -- Introduced for bug fix 3434626
OPEN org_sa_insert_cur(r_reference_id_array.r_reference_id(i));
FETCH org_sa_insert_cur
BULK COLLECT INTO r_asg_id_array.r_asg_id,
r_asg_id_array.r_payroll_id;
CLOSE org_sa_insert_cur;
insert_changed_assignments(p_change_type => 'SA',
p_reference_id => r_reference_id_array.r_reference_id(i),
p_action_type => r_reference_id_array.r_action_type(i),
p_return_status => p_return_status);
insert_changed_assignments(p_change_type => 'DS',
p_reference_id => r_reference_id_array.r_reference_id(i),
p_action_type => r_reference_id_array.r_action_type(i),
p_return_status => p_return_status);
/* Verifying Org Default LS Inserts */
ELSIF (r_reference_id_array.r_change_type(i) = 'DS') AND
(r_reference_id_array.r_action_type(i) = 'I') THEN
IF (l_default_schedule = 'Y') THEN
l_proc_step := 110 + (I/100000); -- Introduced for bug fix 3434626
OPEN org_ds_insert_cur(r_reference_id_array.r_reference_id(i));
FETCH org_ds_insert_cur
BULK COLLECT INTO r_asg_id_array.r_asg_id,
r_asg_id_array.r_payroll_id;
CLOSE org_ds_insert_cur;
insert_changed_assignments(p_change_type => 'DS',
p_reference_id => r_reference_id_array.r_reference_id(i),
p_action_type => r_reference_id_array.r_action_type(i),
p_return_status => p_return_status);
insert_changed_assignments(p_change_type => 'GE',
p_reference_id => r_reference_id_array.r_reference_id(i),
p_action_type => r_reference_id_array.r_action_type(i),
p_return_status => p_return_status);
/* Verifying Global Earning Elements Inserts */
ELSIF (r_reference_id_array.r_change_type(i) = 'GE') AND
(r_reference_id_array.r_action_type(i) = 'I') THEN
l_proc_step := 130 + (I/100000); -- Introduced for bug fix 3434626
OPEN global_element_insert_cur(r_reference_id_array.r_reference_id(i));
FETCH global_element_insert_cur
BULK COLLECT INTO r_asg_id_array.r_asg_id,
r_asg_id_array.r_payroll_id;
CLOSE global_element_insert_cur;
insert_changed_assignments(p_change_type => 'GE',
p_reference_id => r_reference_id_array.r_reference_id(i),
p_action_type => r_reference_id_array.r_action_type(i),
p_return_status => p_return_status);
insert_changed_assignments(p_change_type => 'EX',
p_reference_id => r_reference_id_array.r_reference_id(i),
p_action_type => r_reference_id_array.r_action_type(i),
p_return_status => p_return_status);
insert_changed_assignments(p_change_type => 'OE',
p_reference_id => r_reference_id_array.r_reference_id(i),
p_action_type => r_reference_id_array.r_action_type(i),
p_return_status => p_return_status);
insert_changed_assignments(p_change_type => 'ED',
p_reference_id => r_reference_id_array.r_reference_id(i),
p_action_type => r_reference_id_array.r_action_type(i),
p_return_status => p_return_status);
INSERT INTO psp_enc_changed_sch_history
(request_id, reference_id,
change_type, action_type,
reference_field)
VALUES (g_request_id, r_reference_id_array.r_reference_id(i),
r_reference_id_array.r_change_type(i), r_reference_id_array.r_action_type(i),
r_reference_id_array.r_reference_field(i));
r_reference_id_array.r_reference_id.DELETE;
r_reference_id_array.r_change_type.DELETE;
r_reference_id_array.r_reference_field.DELETE;
r_reference_id_array.r_action_type.DELETE;
DELETE psp_enc_changed_schedules;