[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_into_enc_lines(
L_ENC_ELEMENT_TYPE_ID IN NUMBER,
L_ENCUMBRANCE_DATE IN DATE,
L_DR_CR_FLAG IN VARCHAR2,
L_ENCUMBRANCE_AMOUNT IN NUMBER,
L_ENC_LINE_TYPE IN VARCHAR2,
L_SCHEDULE_LINE_ID IN NUMBER,
L_ORG_SCHEDULE_ID IN NUMBER,
L_DEFAULT_ORG_ACCOUNT_ID IN NUMBER,
L_SUSPENSE_ORG_ACCOUNT_ID IN NUMBER,
L_ELEMENT_ACCOUNT_ID IN NUMBER,
L_GL_PROJECT_FLAG IN VARCHAR2,
L_PERSON_ID IN NUMBER,
L_ASSIGNMENT_ID IN NUMBER,
L_AWARD_ID IN NUMBER,
L_TASK_ID IN NUMBER,
L_EXPENDITURE_TYPE IN VARCHAR2,
L_EXPENDITURE_ORGANIZATION_ID IN NUMBER,
L_PROJECT_ID IN NUMBER,
L_GL_CODE_COMBINATION_ID IN NUMBER,
L_TIME_PERIOD_ID IN NUMBER,
L_PAYROLL_ID IN NUMBER,
l_business_group_id IN NUMBER,
L_SET_OF_BOOKS_ID IN NUMBER,
L_SUSPENSE_REASON_CODE IN VARCHAR2,
L_DEFAULT_REASON_CODE IN VARCHAR2,
L_CHANGE_FLAG IN VARCHAR2,
L_ENC_START_DATE IN DATE, --Added the new parameter
L_ENC_END_DATE IN DATE, --Added the new parameter
p_attribute_category IN VARCHAR2, -- Introduced DFF parameters 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_orig_gl_code_combination_id IN NUMBER,
p_orig_project_id IN NUMBER,
p_orig_task_id IN NUMBER,
p_orig_award_id IN NUMBER,
p_orig_expenditure_org_id IN NUMBER,
p_orig_expenditure_type IN VARCHAR2,
p_hierarchy_code IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2);
Procedure insert_enc_lines_from_arrays(
p_payroll_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_enc_line_type IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2);
PROCEDURE delete_previous_error_log(p_assignment_id IN NUMBER,
p_payroll_id IN NUMBER,
p_payroll_action_id IN NUMBER);
PROCEDURE update_hierarchy_dates (p_assignment_id IN NUMBER,
p_payroll_id IN NUMBER,
p_payroll_action_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2);
SELECT paf.assignment_number,
ppf.employee_number
FROM
per_assignments_f paf,per_people_f ppf
WHERE paf.assignment_id =p_assignment_id
AND paf.person_id =ppf.person_id
AND p_effective_date between paf.effective_start_date and paf.effective_end_date
AND p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT peed.period_end_date,
peed.enc_end_date_id,
NVL(peed.prev_enc_end_date, fnd_date.canonical_to_date('4712/12/31')) prev_enc_end_date
FROM psp_enc_end_dates peed
WHERE
--- removed sysdate check for Bug fix 2597666
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_end_dates peed
WHERE
-- removed sysdate check for Bug Fix 2597666
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';
-- moved the select to Cursor
OPEN c_cnt_default_org;
UPDATE psp_enc_end_dates peed
SET peed.prev_enc_end_date = p_enc_org_end_date
WHERE peed.enc_end_date_id = l_enc_end_date_id;
SELECT time_period_id,
SUM(period_amount)
FROM (SELECT ((MAX(fnd_number.canonical_to_number(NVL(peev.screen_entry_value, 0))) *
SUM(psp_general.business_days( GREATEST(ptp.start_date, paf.effective_start_date, peev.effective_start_date),
LEAST(ptp.end_date, paf.effective_end_date, peev.effective_end_date),p_assignment_id)) ) /
DECODE(psp_general.business_days(MAX(ptp.start_date), MAX(ptp.end_date),p_assignment_id), 0, 1,
psp_general.business_days(MAX(ptp.start_date), MAX(ptp.end_date),p_assignment_id)) ) period_amount,
ptp.time_period_id
FROM pay_element_entry_values_f peev,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
psp_enc_elements peel,
per_assignments_f paf,
per_assignment_status_types past,
per_time_periods ptp
WHERE peev.element_entry_id = pee.element_entry_id
AND peev.effective_start_date <= paf.effective_end_date
AND peev.effective_end_date >= paf.effective_start_date
AND pel.element_link_id = pee.element_link_id
AND pel.element_type_id = p_element_type_id
AND pel.effective_start_date <= paf.effective_end_date
AND pel.effective_end_date >= paf.effective_start_date
AND pet.effective_start_date <= paf.effective_end_date
AND pet.effective_end_date >= paf.effective_start_date
AND pee.assignment_id = p_assignment_id
AND pee.effective_start_date <= paf.effective_end_date
AND pee.effective_end_date >= paf.effective_start_date
AND peev.input_value_id = piv.input_value_id
AND piv.input_value_id = peel.input_value_id
AND pet.element_type_id = p_element_type_id
AND piv.effective_start_date <= paf.effective_end_date
AND piv.effective_end_date >= paf.effective_start_date
AND piv.effective_start_date <= peev.effective_end_date
AND piv.effective_end_date >= peev.effective_start_date
AND pee.effective_start_date <= peev.effective_end_date
AND pee.effective_end_date >= peev.effective_start_date
AND ptp.start_date <= paf.effective_end_date
AND ptp.end_date >= paf.effective_start_date
AND pel.effective_start_date <= pee.effective_end_date
AND pel.effective_end_date >= pee.effective_start_date
AND pet.effective_start_date <= pel.effective_end_date
AND pet.effective_end_date >= pel.effective_start_date
AND paf.assignment_id = p_assignment_id
AND paf.effective_start_date <= l_max_end_date
AND paf.effective_end_date >= l_min_start_date
AND past.assignment_status_type_id = paf.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND pel.business_group_id = p_business_group_id
AND peel.business_group_id = p_business_group_id
AND peel.set_of_books_id = p_set_of_books_id
AND ptp.payroll_id = p_payroll_id
AND ptp.time_period_id >= r_enc_period.r_time_period_id(1)
AND SUBSTR(piv.uom, 1, 1) = g_uom
GROUP BY ptp.time_period_id,
peev.effective_start_date,
peev.effective_end_date)
GROUP BY time_period_id;
SELECT ppp.change_date,
ppp.proposed_salary_n proposed_salary,
NVL(ppb.pay_basis, ' ') pay_basis,
ppb.pay_annualization_factor,
paf.effective_start_date,
paf.effective_end_date
FROM per_pay_proposals ppp,
pay_input_values_f piv,
per_pay_bases ppb,
per_all_assignments_f paf,
per_assignment_status_types past
WHERE paf.assignment_id =p_assignment_id
AND ( ppp.change_date BETWEEN paf.effective_start_date AND paf.effective_end_date
OR ppp.change_date = (SELECT MAX(ppp1.change_date)
FROM per_pay_proposals ppp1
WHERE ppp1.assignment_id = p_assignment_id
AND ppp1.approved = 'Y'
AND ppp1.change_date <= paf.effective_start_date))
AND paf.pay_basis_id = ppb.pay_basis_id
AND ppp.assignment_id = p_assignment_id
AND past.assignment_status_type_id = paf.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND piv.element_type_id +0 = p_element_type_id
AND ppp.approved = 'Y'
AND ((ppp.change_date >= (SELECT NVL(MAX(ppp1.change_date), l_min_start_date)
FROM per_pay_proposals ppp1
WHERE ppp1.assignment_id = p_assignment_id
AND ppp1.approved = 'Y'
AND ppp1.change_date <= l_min_start_date))
AND (ppp.change_date <= (SELECT NVL(MIN(ppp1.change_date), l_max_end_date + 1)
FROM per_pay_proposals ppp1
WHERE ppp1.assignment_id = p_assignment_id
AND ppp1.approved = 'Y'
AND ppp1.change_date >= l_max_end_date)))
AND ppb.input_value_id = piv.input_value_id
AND ppp.change_date BETWEEN piv.effective_start_date AND piv.effective_end_date
ORDER BY paf.effective_end_date DESC, ppp.change_date DESC;
SELECT number_per_fiscal_year
FROM per_time_period_types ptpt
WHERE period_type = (SELECT ppf.period_type
FROM pay_payrolls_f ppf
WHERE ppf.payroll_id = p_payroll_id
/* Added for Bug 3869766 */
AND rownum = 1);
SELECT DISTINCT GREATEST(paf.effective_start_date, peev.effective_start_date) element_start_date,
LEAST(paf.effective_end_date, peev.effective_end_date) element_end_date,
fnd_number.canonical_to_number(NVL(peev.screen_entry_value, 0)) pay_amount
FROM pay_element_entry_values_f peev,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
psp_enc_elements peel,
per_assignments_f paf,
per_assignment_status_types past
WHERE peev.element_entry_id = pee.element_entry_id
AND peev.effective_start_date <= paf.effective_end_date
AND peev.effective_end_date >= paf.effective_start_date
AND pel.element_link_id = pee.element_link_id
AND pel.element_type_id = p_element_type_id
AND pel.effective_start_date <= paf.effective_end_date
AND pel.effective_end_date >= paf.effective_start_date
AND pet.effective_start_date <= paf.effective_end_date
AND pet.effective_end_date >= paf.effective_start_date
AND pee.assignment_id = p_assignment_id
AND pee.effective_start_date <= paf.effective_end_date
AND pee.effective_end_date >= paf.effective_start_date
AND peev.input_value_id = piv.input_value_id
AND piv.input_value_id = peel.input_value_id
AND pet.element_type_id = p_element_type_id
AND piv.effective_start_date <= paf.effective_end_date
AND piv.effective_end_date >= paf.effective_start_date
AND piv.effective_start_date <= peev.effective_end_date
AND piv.effective_end_date >= peev.effective_start_date
AND pee.effective_start_date <= peev.effective_end_date
AND pee.effective_end_date >= peev.effective_start_date
AND pel.effective_start_date <= pee.effective_end_date
AND pel.effective_end_date >= pee.effective_start_date
AND pet.effective_start_date <= pel.effective_end_date
AND pet.effective_end_date >= pel.effective_start_date
AND paf.assignment_id = p_assignment_id
AND paf.effective_start_date <= l_max_end_date
AND paf.effective_end_date >= l_min_start_date
AND past.assignment_status_type_id = paf.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND SUBSTR(piv.uom, 1, 1) = g_uom
AND pel.business_group_id = p_business_group_id
AND peel.business_group_id = p_business_group_id
AND peel.set_of_books_id = p_set_of_books_id ;
SELECT NVL(input_value_id, -1),
formula_id
FROM psp_enc_elements pee
WHERE element_type_id = p_element_type_id
AND ( formula_id IS NOT NULL
OR EXISTS (SELECT 1
FROM pay_input_values_f piv
WHERE piv.input_value_id = pee.input_value_id
AND SUBSTR(piv.uom, 1, 1) = g_uom));
SELECT GREATEST(pee.effective_start_date, paf.effective_start_date, ff.effective_start_date) start_date,
LEAST(pee.effective_end_date, paf.effective_end_date, ff.effective_end_date) end_date
FROM ff_formulas_f ff,
per_assignments_f paf,
pay_element_entries_f pee
WHERE formula_id = l_formula_id
AND paf.assignment_id = p_assignment_id
AND pee.assignment_id = p_assignment_id
AND pee.element_type_id = p_element_type_id
AND paf.effective_start_date <= l_max_end_date
AND paf.effective_end_date >= l_min_start_date
AND pee.effective_start_date <= l_max_end_date
AND pee.effective_end_date >= l_min_start_date
AND ff.effective_start_date <= l_max_end_date
AND ff.effective_end_date >= l_min_start_date
AND paf.effective_start_date <= pee.effective_end_date
AND paf.effective_end_date >= pee.effective_start_date
AND paf.effective_start_date <= ff.effective_end_date
AND paf.effective_end_date >= ff.effective_start_date
AND pee.effective_start_date <= ff.effective_end_date
AND pee.effective_end_date >= ff.effective_start_date;
l_inputs.DELETE;
l_outputs.DELETE;
--When called in Update/Quick Update Mode periods between last payroll run date and max. encumbered date
for that assignment are considered.
--All the active assignment chunks are Bulk Collected inot r_enc_period RECORD.
--> Next, go through a loop (for each record of r_enc_period) to calculate the encumbrance amount for each active assignment chunk within a period
in the payroll.
--> For each active assignment chunk, calculate the daily rate (from Encumbrance amount and business days)
--> Then, go through a loop to find the schedule for that assignment chunk by going through the schedule
hierarchy.
--> Once the schedule has been determined, then create encumbrance lines
--> Exit out of the First Loop when the assignment start date > Enumbrance End date Calculated within each
Hierarchy for each CI. For GL Encumrbance End date = Org. Default End Date for POETA it is computed
thorugh procedure Obtain_Enc_Poeta_Enc_date.
--> Close First Loop
***********************************************************************************/
PROCEDURE create_lines( p_assignment_id IN NUMBER,
p_payroll_id IN NUMBER,
p_element_type_id IN NUMBER,
p_last_paid_date IN DATE,
p_return_status OUT NOCOPY VARCHAR2) IS
CURSOR enc_period_cur IS
SELECT ptp.time_period_id,
ptp.start_date,
ptp.end_date,
GREATEST(ptp.start_date, paf.effective_start_date),
LEAST(ptp.end_date, paf.effective_end_date),
DECODE(g_Eff_Date_Value, 1, ptp.end_date,
2, ptp.start_date,
3, ptp.regular_payment_date,
4, ptp.default_dd_date,
5, ptp.cut_off_date) effective_date,
'Y',
0,
NULL
FROM per_time_periods ptp,
per_all_assignments_f paf,
per_assignment_status_types past
WHERE ptp.payroll_id = p_payroll_id
AND paf.assignment_id = p_assignment_id
AND ptp.start_date <= paf.effective_end_date
AND ptp.end_date >= paf.effective_start_date
AND past.assignment_status_type_id = paf.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND paf.payroll_id=p_payroll_id
AND ptp.start_date >= p_last_paid_date
AND paf.effective_start_date <= g_enc_org_end_date
AND (g_actual_term_date IS NULL OR ptp.start_date <= g_actual_term_date)
AND (g_pateo_end_date IS NULL OR ptp.start_date <= g_pateo_end_date)
ORDER BY 1, 4;
SELECT paf.person_id
FROM per_all_assignments_f paf
WHERE paf.assignment_id =p_assignment_id
AND ROWNUM=1;
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND payroll_id = p_payroll_id
AND effective_end_date >= l_asg_start_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 = l_project_id;
SELECT award_number
FROM gms_awards_all
WHERE award_id = l_award_id;
SELECT task_number
FROM pa_tasks
WHERE task_id = l_task_id;
SELECT name
FROM hr_organization_units
WHERE organization_id = l_expenditure_org_id;
insert_into_enc_lines(
p_element_type_id,
l_effective_date,
g_dr_cr_flag ,
ROUND(l_dist_amount, g_precision),
g_enc_line_type,
l_schedule_line_id,
l_org_schedule_id,
l_default_account_id,
l_suspense_account_id,
l_element_account_id,
l_gl_project_flag,
l_person_id,
p_assignment_id,
l_award_id,
l_task_id,
l_expenditure_type,
l_expenditure_org_id,
l_project_id,
l_gl_code_combination_id,
r_enc_period.r_time_period_id(p_chunk_pointer),
p_payroll_id,
g_business_group_id,
g_set_of_books_id,
l_sa_reason_code,
l_da_reason_code,
'N',
t_poeta_gl_hier_array(i).r_enc_start_date,
t_poeta_gl_hier_array(i).r_enc_end_date,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_orig_gl_code_combination_id,
l_orig_project_id,
l_orig_task_id,
l_orig_award_id,
l_orig_expenditure_org_id,
l_orig_expenditure_type,
l_process_flag,
l_return_status);
insert_into_enc_lines
(p_element_type_id,
l_effective_date,
g_dr_cr_flag,
ROUND(l_dist_amount,g_precision),
g_enc_line_type,
l_schedule_line_id,
l_org_schedule_id,
l_default_account_id,
l_suspense_account_id,
l_element_account_id,
l_gl_project_flag,
l_person_id,
p_assignment_id,
l_award_id,
l_task_id,
l_expenditure_type,
l_expenditure_org_id,
l_project_id,
l_gl_code_combination_id,
r_enc_period.r_time_period_id(p_chunk_pointer),
p_payroll_id,
g_business_group_id,
g_set_of_books_id,
l_sa_reason_code,
l_da_reason_code,
'N',
t_poeta_gl_hier_array(i).r_enc_start_date,
t_poeta_gl_hier_array(i).r_enc_end_date,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_orig_gl_code_combination_id,
l_orig_project_id,
l_orig_task_id,
l_orig_award_id,
l_orig_expenditure_org_id,
l_orig_expenditure_type,
l_process_flag,
l_return_status);
t_poeta_gl_hier_array.DELETE;
orig_ci.gl_code_combination_id.DELETE;
orig_ci.project_id.DELETE;
orig_ci.task_id.DELETE;
orig_ci.award_id.DELETE;
orig_ci.expenditure_organization_id.DELETE;
orig_ci.expenditure_type.DELETE;
r_enc_period.r_time_period_id.DELETE;
r_enc_period.r_period_start_date.DELETE;
r_enc_period.r_period_end_date.DELETE;
r_enc_period.r_asg_start_date.DELETE;
r_enc_period.r_asg_end_date.DELETE;
r_enc_period.r_process_flag.DELETE;
r_enc_period.r_period_ind.DELETE;
r_enc_period.r_schedule_percent.DELETE;
r_enc_period.r_encumbrance_amount.DELETE;
r_enc_period.r_period_amount.DELETE;
r_enc_period.r_reason_code.DELETE;
PROCEDURE insert_into_enc_lines(
L_ENC_ELEMENT_TYPE_ID IN NUMBER,
L_ENCUMBRANCE_DATE IN DATE,
L_DR_CR_FLAG IN VARCHAR2,
L_ENCUMBRANCE_AMOUNT IN NUMBER,
L_ENC_LINE_TYPE IN VARCHAR2,
L_SCHEDULE_LINE_ID IN NUMBER,
L_ORG_SCHEDULE_ID IN NUMBER,
L_DEFAULT_ORG_ACCOUNT_ID IN NUMBER,
L_SUSPENSE_ORG_ACCOUNT_ID IN NUMBER,
L_ELEMENT_ACCOUNT_ID IN NUMBER,
L_GL_PROJECT_FLAG IN VARCHAR2,
L_PERSON_ID IN NUMBER,
L_ASSIGNMENT_ID IN NUMBER,
L_AWARD_ID IN NUMBER,
L_TASK_ID IN NUMBER,
L_EXPENDITURE_TYPE IN VARCHAR2,
L_EXPENDITURE_ORGANIZATION_ID IN NUMBER,
L_PROJECT_ID IN NUMBER,
L_GL_CODE_COMBINATION_ID IN NUMBER,
L_TIME_PERIOD_ID IN NUMBER,
L_PAYROLL_ID IN NUMBER,
L_BUSINESS_GROUP_ID IN NUMBER,
L_SET_OF_BOOKS_ID IN NUMBER,
L_SUSPENSE_REASON_CODE IN VARCHAR2,
L_DEFAULT_REASON_CODE IN VARCHAR2,
L_CHANGE_FLAG IN VARCHAR2,
L_ENC_START_DATE IN DATE,
L_ENC_END_DATE IN DATE,
p_attribute_category IN VARCHAR2, -- Introduced DFF parameters 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_orig_gl_code_combination_id IN NUMBER,
p_orig_project_id IN NUMBER,
p_orig_task_id IN NUMBER,
p_orig_award_id IN NUMBER,
p_orig_expenditure_org_id IN NUMBER,
p_orig_expenditure_type IN VARCHAR2,
p_hierarchy_code IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2) IS
l_enc_line_id NUMBER;
/***** Commented the following for Create and Update multi thread enh.
-- Get a number for enc control id
BEGIN
SELECT psp_enc_controls_s.nextval
INTO l_enc_control_id
FROM DUAL;
End of comment for Create and Update multi thread enh. *****/
enc_control_tab.r_enc_control_id(l_rec_no) := l_enc_control_id;
-- Insert into enc lines record
t_enc_lines_array.r_enc_element_type_id(g_enc_lines_counter) := l_enc_element_type_id;
END IF; /* skip inserting lines of zero dollars */
g_error_message := 'INSERT_INTO_ENC_LINES: ' || SQLERRM;
fnd_msg_pub.add_exc_msg('PSP_ENC_LINES','INSERT_INTO_ENC_LINES');
END insert_into_enc_lines;
l_last_update_date DATE DEFAULT SYSDATE;
l_last_updated_by NUMBER DEFAULT NVL(FND_GLOBAL.USER_ID, -1);
l_last_updated_login NUMBER DEFAULT NVL(FND_GLOBAL.LOGIN_ID, -1);
SELECT enc_control_id
FROM psp_enc_controls
WHERE payroll_action_id = p_payroll_action_id
AND payroll_id = p_payroll_id
AND time_period_id = p_time_period_id
AND uom = g_uom;
/* Added IF conditon below for Restart update/Quick Update Encumbrance Lines Enh. */
-- IF g_enc_line_type IN ('U','Q') THEN
l_action_code := 'IC';
SELECT psp_enc_controls_s.NEXTVAL INTO p_enc_control_id FROM DUAL;
INSERT INTO PSP_ENC_CONTROLS
(time_period_id, enc_control_id,
number_of_dr, number_of_cr,
total_dr_amount, total_cr_amount,
gl_dr_amount, gl_cr_amount,
ogm_dr_amount, ogm_cr_amount,
payroll_id, set_of_books_id,
encumbrance_date, action_code,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, batch_name,
business_group_id, action_type,
payroll_action_id, uom)
VALUES (p_time_period_id, p_enc_control_id,
0, 0,
0, 0,
0, 0,
0, 0,
p_payroll_id, p_set_of_books_id,
l_last_update_date, l_action_code,
l_last_update_date, l_last_updated_by,
l_last_update_date, l_last_updated_by,
l_last_updated_login, l_batch_name,
p_business_group_id, 'U',
p_payroll_action_id, g_uom);
/* Commented for Create and Update multi thread enh.
VALUES (enc_control_tab.R_TIME_PERIOD_ID(i), enc_control_tab.R_ENC_CONTROL_ID(i),
enc_control_tab.R_NO_OF_DR(i), enc_control_tab.R_NO_OF_CR(i),
round(enc_control_tab.R_TOTAL_DR_AMOUNT(i),g_precision),-- intoduced Rounding for Bug 2916848 Ilo Ehnc.
round(enc_control_tab.R_TOTAL_CR_AMOUNT(i),g_precision),-- intoduced Rounding for Bug 2916848 Ilo Ehnc.
round(enc_control_tab.R_GL_DR_AMOUNT(i),g_precision),-- intoduced Rounding for Bug 2916848 Ilo Ehnc.
round(enc_control_tab.R_GL_CR_AMOUNT(i),g_precision),-- intoduced Rounding for Bug 2916848 Ilo Ehnc.
round(enc_control_tab.R_OGM_DR_AMOUNT(i),g_precision),-- intoduced Rounding for Bug 2916848 Ilo Ehnc.
round(enc_control_tab.R_OGM_CR_AMOUNT(i),g_precision),-- intoduced Rounding for Bug 2916848 Ilo Ehnc.
p_payroll_id, p_set_of_books_id,
l_last_update_date, l_action_code,
l_last_update_date, l_last_updated_by,
l_last_update_date, l_last_updated_by,
l_last_updated_login, l_batch_name,
p_business_group_id, g_enc_line_type,
p_payroll_action_id);
End of comment for Create and Update Multi thread enh. *****/
END IF;
enc_control_tab.r_time_period_id.delete;
enc_control_tab.r_enc_control_id.delete;
enc_control_tab.r_no_of_dr.delete;
enc_control_tab.r_no_of_cr.delete;
enc_control_tab.r_total_dr_amount.delete;
enc_control_tab.r_total_cr_amount.delete;
enc_control_tab.r_gl_dr_amount.delete;
enc_control_tab.r_gl_cr_amount.delete;
enc_control_tab.r_ogm_dr_amount.delete;
enc_control_tab.r_ogm_cr_amount.delete;
enc_control_tab.r_uom.delete;
fnd_message.set_name('PSP','PSP_ENC_INSERT_CONTROLS');
Purpose: To insert into psp_enc_lines from Array. This procedure has been introduced to bulk insert
into psp_enc_lines from the record of collection t_enc_lines_array.
Introduced as part of Enhnacement Enc Redesign : Bug 2259310.
Who When What
lveerubh 08-MAR-2002 Created the procedure
********************************************************************************************************************************/
PROCEDURE insert_enc_lines_from_arrays( p_payroll_id IN NUMBER,
p_business_group_id IN NUMBER,
p_set_of_books_id IN NUMBER,
p_enc_line_type IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2)
IS
l_last_update_date DATE DEFAULT SYSDATE;
l_last_updated_by NUMBER DEFAULT NVL(FND_GLOBAL.USER_ID, -1);
l_last_update_login NUMBER DEFAULT NVL(FND_GLOBAL.LOGIN_ID, -1);
insert into psp_enc_lines
(
enc_element_type_id,
enc_line_id,
business_group_id,
encumbrance_date,
dr_cr_flag,
encumbrance_amount,
enc_line_type,
schedule_line_id,
org_schedule_id,
default_org_account_id,
suspense_org_account_id,
element_account_id,
gl_project_flag,
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,
status_code,
enc_control_id,
change_flag,
enc_start_date,
enc_end_date,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
attribute_category, -- Introduced DFF columns for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
payroll_action_id,
orig_gl_code_combination_id,
orig_project_id,
orig_task_id,
orig_award_id,
orig_expenditure_org_id,
orig_expenditure_type,
hierarchy_code
)
values (
t_enc_lines_array2.r_enc_element_type_id(i)
,PSP_ENC_LINES_S.NEXTVAL
,p_business_group_id
, t_enc_lines_array2.r_encumbrance_date(i)
, t_enc_lines_array2.r_dr_cr_flag(i)
,round( t_enc_lines_array2.r_encumbrance_amount(i),g_precision) -- introduced rounding for BUg 2916848 Ilo Ehnc.
, t_enc_lines_array2.r_enc_line_type(i)
, t_enc_lines_array2.r_schedule_line_id(i)
, t_enc_lines_array2.r_org_schedule_id(i)
, t_enc_lines_array2.r_default_org_account_id(i)
, t_enc_lines_array2.r_suspense_org_account_id(i)
, t_enc_lines_array2.r_element_account_id(i)
, t_enc_lines_array2.r_gl_project_flag(i)
, t_enc_lines_array2.r_person_id(i)
, t_enc_lines_array2.r_assignment_id(i)
, t_enc_lines_array2.r_award_id(i)
, t_enc_lines_array2.r_task_id(i)
, t_enc_lines_array2.r_expenditure_type(i)
, t_enc_lines_array2.r_expenditure_organization_id(i)
, t_enc_lines_array2.r_project_id(i)
, t_enc_lines_array2.r_gl_code_combination_id(i)
, t_enc_lines_array2.r_time_period_id(i)
, p_payroll_id
, p_set_of_books_id
, t_enc_lines_array2.r_default_reason_code(i)
, t_enc_lines_array2.r_suspense_reason_code(i)
, p_enc_line_type --status_code
, t_enc_lines_array2.r_enc_control_id(i)
, t_enc_lines_array2.r_change_flag(i)
, t_enc_lines_array2.r_enc_start_date(i)
, t_enc_lines_array2.r_enc_end_date(i)
, l_last_update_date
, l_last_updated_by
, l_last_update_login
, l_last_updated_by
, l_last_update_date
, DECODE(t_enc_lines_array2.r_attribute_category(i), 'NULL_VALUE', NULL, t_enc_lines_array2.r_attribute_category(i))
, DECODE(t_enc_lines_array2.r_attribute1(i), 'NULL_VALUE', NULL, t_enc_lines_array2.r_attribute1(i))
, DECODE(t_enc_lines_array2.r_attribute2(i), 'NULL_VALUE', NULL, t_enc_lines_array2.r_attribute2(i))
, DECODE(t_enc_lines_array2.r_attribute3(i), 'NULL_VALUE', NULL, t_enc_lines_array2.r_attribute3(i))
, DECODE(t_enc_lines_array2.r_attribute4(i), 'NULL_VALUE', NULL, t_enc_lines_array2.r_attribute4(i))
, DECODE(t_enc_lines_array2.r_attribute5(i), 'NULL_VALUE', NULL, t_enc_lines_array2.r_attribute5(i))
, DECODE(t_enc_lines_array2.r_attribute6(i), 'NULL_VALUE', NULL, t_enc_lines_array2.r_attribute6(i))
, DECODE(t_enc_lines_array2.r_attribute7(i), 'NULL_VALUE', NULL, t_enc_lines_array2.r_attribute7(i))
, DECODE(t_enc_lines_array2.r_attribute8(i), 'NULL_VALUE', NULL, t_enc_lines_array2.r_attribute8(i))
, DECODE(t_enc_lines_array2.r_attribute9(i), 'NULL_VALUE', NULL, t_enc_lines_array2.r_attribute9(i))
, DECODE(t_enc_lines_array2.r_attribute10(i), 'NULL_VALUE', NULL, t_enc_lines_array2.r_attribute10(i))
, g_payroll_action_id
, t_enc_lines_array2.r_orig_gl_code_combination_id(i)
, t_enc_lines_array2.r_orig_project_id(i)
, t_enc_lines_array2.r_orig_task_id(i)
, t_enc_lines_array2.r_orig_award_id(i)
, t_enc_lines_array2.r_orig_expenditure_org_id(i)
, t_enc_lines_array2.r_orig_expenditure_type(i)
, t_enc_lines_array2.r_hierarchy_code(i)
);
DELETE psp_enc_lines
WHERE payroll_id = p_payroll_id
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND encumbrance_amount = 0;
t_enc_lines_array2.r_enc_element_type_id.delete;
t_enc_lines_array2.r_encumbrance_date.delete;
t_enc_lines_array2.r_dr_cr_flag.delete;
t_enc_lines_array2.r_encumbrance_amount.delete;
t_enc_lines_array2.r_enc_line_type.delete;
t_enc_lines_array2.r_schedule_line_id.delete;
t_enc_lines_array2.r_org_schedule_id.delete;
t_enc_lines_array2.r_default_org_account_id.delete;
t_enc_lines_array2.r_suspense_org_account_id.delete;
t_enc_lines_array2.r_element_account_id.delete;
t_enc_lines_array2.r_gl_project_flag.delete;
t_enc_lines_array2.r_person_id.delete;
t_enc_lines_array2.r_assignment_id.delete;
t_enc_lines_array2.r_award_id.delete;
t_enc_lines_array2.r_task_id.delete;
t_enc_lines_array2.r_expenditure_type.delete;
t_enc_lines_array2.r_expenditure_organization_id.delete;
t_enc_lines_array2.r_project_id.delete;
t_enc_lines_array2.r_gl_code_combination_id.delete;
t_enc_lines_array2.r_time_period_id.delete;
t_enc_lines_array2.r_default_reason_code.delete;
t_enc_lines_array2.r_suspense_reason_code.delete;
t_enc_lines_array2.r_enc_control_id.delete;
t_enc_lines_array2.r_change_flag.delete;
t_enc_lines_array2.r_enc_start_date.delete;
t_enc_lines_array2.r_enc_end_date.delete;
t_enc_lines_array2.r_attribute_category.delete;
t_enc_lines_array2.r_attribute1.delete;
t_enc_lines_array2.r_attribute2.delete;
t_enc_lines_array2.r_attribute3.delete;
t_enc_lines_array2.r_attribute4.delete;
t_enc_lines_array2.r_attribute5.delete;
t_enc_lines_array2.r_attribute6.delete;
t_enc_lines_array2.r_attribute7.delete;
t_enc_lines_array2.r_attribute8.delete;
t_enc_lines_array2.r_attribute9.delete;
t_enc_lines_array2.r_attribute10.delete;
t_enc_lines_array2.r_orig_gl_code_combination_id.delete;
t_enc_lines_array2.r_orig_project_id.delete;
t_enc_lines_array2.r_orig_award_id.delete;
t_enc_lines_array2.r_orig_task_id.delete;
t_enc_lines_array2.r_orig_expenditure_type.delete;
t_enc_lines_array2.r_orig_expenditure_org_id.delete;
t_enc_lines_array2.r_hierarchy_code.delete;
g_error_message := 'INSERT_ENC_LINES_FROM_ARRAYS: ' || SQLERRM;
g_error_api_path := SUBSTR(' INSERT_ENC_LINES_FROM_ARRAYS:'||g_error_api_path,1,230);
fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', ' INSERT_ENC_LINES_FROM_ARRAYS');
END insert_enc_lines_from_arrays;
SELECT GREATEST(l_min_start_date, start_date_active) start_date_active,
LEAST(l_max_end_date, end_date_active) end_date_active,
SUM(percent) schedule_percent
FROM psp_element_type_accounts
WHERE business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND element_type_id = p_element_type_id
AND ( gl_code_combination_id IS NOT NULL
OR award_id IS NOT NULL)
AND start_date_active <= l_max_end_date
AND end_date_active >= l_min_start_date
GROUP BY GREATEST(l_min_start_date, start_date_active),
LEAST(l_max_end_date, end_date_active)
ORDER BY start_date_active;
SELECT GREATEST(l_min_start_date, paf.effective_start_date, schedule_begin_date) schedule_begin_date,
LEAST(l_max_end_date, paf.effective_end_date, schedule_end_date) schedule_end_date,
SUM(schedule_percent) schedule_percent
FROM per_assignments_f paf,
psp_default_labor_schedules pdls
WHERE paf.assignment_id = p_assignment_id
AND l_min_start_date <= paf.effective_end_date
AND l_max_end_date >= paf.effective_start_date
AND paf.organization_id = pdls.organization_id
AND pdls.business_group_id = p_business_group_id
AND pdls.set_of_books_id = p_set_of_books_id
AND ( gl_code_combination_id IS NOT NULL
OR award_id IS NOT NULL)
AND schedule_begin_date <= l_max_end_date
AND schedule_end_date >= l_min_start_date
GROUP BY GREATEST(l_min_start_date, paf.effective_start_date, schedule_begin_date),
LEAST(l_max_end_date, paf.effective_end_date, schedule_end_date)
ORDER BY 1;
SELECT psh.schedule_hierarchy_id
FROM psp_schedule_hierarchy psh
WHERE psh.business_group_id = p_business_group_id
AND psh.set_of_books_id = p_set_of_books_id
AND psh.scheduling_types_code = p_scheduling_types_code
AND ( (p_scheduling_types_code = 'ET' AND psh.element_type_id = p_element_type_id)
OR (p_scheduling_types_code = 'A'))
AND psh.assignment_id = p_assignment_id;
SELECT DISTINCT schedule_hierarchy_id
FROM psp_schedule_hierarchy psh,
psp_element_groups peg,
psp_group_element_list pgel
WHERE psh.business_group_id = p_business_group_id
AND psh.set_of_books_id = p_set_of_books_id
AND peg.business_group_id = p_business_group_id
AND peg.set_of_books_id = p_set_of_books_id
AND psh.scheduling_types_code = 'EG'
AND psh.element_group_id = peg.element_group_id
AND peg.end_date_active >= r_enc_period.r_period_start_date(1)
AND peg.start_date_active <= r_enc_period.r_period_end_date(l_period_count)
AND pgel.element_type_id = p_element_type_id
AND psh.assignment_id = p_assignment_id;
SELECT schedule_hierarchy_id
FROM psp_schedule_hierarchy psh
WHERE EXISTS (SELECT 1
FROM psp_element_groups peg,
psp_group_element_list pgel
WHERE peg.business_group_id = p_business_group_id
AND peg.set_of_books_id = p_set_of_books_id
AND peg.end_date_active >= r_enc_period.r_period_start_date(1)
AND peg.start_date_active <= r_enc_period.r_period_end_date(l_period_count)
AND peg.element_group_id = psh.element_group_id
AND pgel.element_type_id = p_element_type_id)
AND psh.business_group_id = p_business_group_id
AND psh.set_of_books_id = p_set_of_books_id
AND psh.scheduling_types_code = 'EG'
AND psh.assignment_id = p_assignment_id;
SELECT GREATEST(l_min_start_date, period_start_date) period_start_date,
LEAST(l_max_end_date, period_end_date) period_end_date,
SUM(period_schedule_percent) schedule_percent
FROM psp_matrix_driver pmd
WHERE run_id = l_run_id
AND period_start_date <= l_max_end_date
AND period_end_date >= l_min_start_date
GROUP BY GREATEST(l_min_start_date, period_start_date),
LEAST(l_max_end_date, period_end_date)
ORDER BY 1;
SELECT GREATEST(l_min_start_date, peg.start_date_active, period_start_date) period_start_date,
LEAST(l_max_end_date, peg.end_date_active, period_end_date) period_end_date,
SUM(period_schedule_percent) schedule_percent
FROM psp_matrix_driver pmd,
psp_schedule_lines psl,
psp_schedule_hierarchy psh,
psp_element_groups peg
WHERE run_id = l_run_id
AND psl.schedule_line_id = pmd.schedule_line_id
AND psl.schedule_hierarchy_id = psh.schedule_hierarchy_id
AND psh.element_group_id = peg.element_group_id
AND peg.start_date_active <= l_max_end_date
AND peg.end_date_active >= l_min_start_date
AND period_start_date <= l_max_end_date
AND period_end_date >= l_min_start_date
AND ( gl_code_combination_id IS NOT NULL
OR award_id IS NOT NULL)
GROUP BY GREATEST(l_min_start_date, peg.start_date_active, period_start_date),
LEAST(l_max_end_date, peg.end_date_active, period_end_date)
ORDER BY 1;
SELECT schedule_line_id l_id,
schedule_begin_date sbd,
schedule_end_date sed,
schedule_percent sp
FROM psp_schedule_lines
WHERE schedule_hierarchy_id = schedule_hierarchy_id
AND ( gl_code_combination_id IS NOT NULL
OR award_id IS NOT NULL)
AND schedule_end_date >= l_min_start_date
AND schedule_begin_date <= l_max_end_date;
SELECT schedule_begin_date dat , 'B'
FROM psp_schedule_lines
WHERE schedule_hierarchy_id = p_schedule_hierarchy_id
AND ( gl_code_combination_id IS NOT NULL
OR award_id IS NOT NULL)
AND schedule_end_date >= l_min_start_date
AND schedule_begin_date <= l_max_end_date
UNION
SELECT schedule_end_date dat , 'E'
FROM psp_schedule_lines
WHERE schedule_hierarchy_id = p_schedule_hierarchy_id
AND ( gl_code_combination_id IS NOT NULL
OR award_id IS NOT NULL)
AND schedule_end_date >= l_min_start_date
AND schedule_begin_date <= l_max_end_date
ORDER BY 1, 2 ;
r_sch_rec.start_date.delete;
r_sch_rec.end_date.delete;
r_sch_rec.schedule_percent.delete;
r_enc_period.r_time_period_id.DELETE;
r_enc_period.r_period_start_date.DELETE;
r_enc_period.r_period_end_date.DELETE;
r_enc_period.r_asg_start_date.DELETE;
r_enc_period.r_asg_end_date.DELETE;
r_enc_period.r_process_flag.DELETE;
r_enc_period.r_period_ind.DELETE;
r_enc_period.r_schedule_percent.DELETE;
r_enc_period.r_encumbrance_amount.DELETE;
r_enc_period.r_period_amount.DELETE;
r_enc_period.r_reason_code.DELETE;
r_enc_period.r_effective_date.DELETE;
DELETE psp_matrix_driver
WHERE run_id = l_run_id
AND (period_start_date > l_max_end_date
OR period_end_date < l_min_start_date
OR period_schedule_percent = 0);
UPDATE psp_matrix_driver pmd
SET period_end_date = period_end_date - 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND period_start_date = (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_line_id <> pmd.schedule_line_id
AND psl1.schedule_begin_date = pmd.period_end_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
UPDATE psp_matrix_driver pmd
SET period_end_date = period_end_date - 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND NOT (NOT EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_line_id <> pmd.schedule_line_id
AND psl1.schedule_begin_date = pmd.period_end_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_end_date = pmd.period_end_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id))
AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
UPDATE psp_matrix_driver pmd
SET period_start_date = period_start_date + 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND NOT EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_begin_date = pmd.period_start_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
UPDATE psp_matrix_driver pmd
SET period_start_date = period_start_date + 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_begin_date = pmd.period_start_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_line_id <> pmd.schedule_line_id
AND psl1.schedule_end_date = pmd.period_start_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
INSERT INTO psp_matrix_driver
(RUN_ID, SCHEDULE_LINE_ID,
PERIOD_START_DATE,
PERIOD_END_DATE,
PERIOD_SCHEDULE_PERCENT)
SELECT l_run_id, schedule_line_id,
GREATEST(l_min_start_date, schedule_chunk.schedule_begin_date(rowno)),
LEAST(l_max_end_date, schedule_chunk.schedule_end_date(rowno)),
schedule_percent
FROM psp_schedule_lines psl
WHERE schedule_hierarchy_id = l_schedule_hierarchy_id
AND schedule_end_date >= l_min_start_date
AND schedule_begin_date <= l_max_end_date
AND ( gl_code_combination_id IS NOT NULL
OR award_id IS NOT NULL)
AND psl.schedule_begin_date <= schedule_chunk.schedule_end_date(rowno)
AND psl.schedule_end_date >= schedule_chunk.schedule_begin_date(rowno);
initial_dates.delete;
date_type.delete;
schedule_chunk.schedule_end_date.delete;
schedule_chunk.schedule_begin_date.delete;
r_sch_rec.start_date.delete;
r_sch_rec.end_date.delete;
r_sch_rec.schedule_percent.delete;
r_enc_period_tmp1.r_time_period_id.DELETE;
r_enc_period_tmp1.r_period_start_date.DELETE;
r_enc_period_tmp1.r_period_end_date.DELETE;
r_enc_period_tmp1.r_asg_start_date.DELETE;
r_enc_period_tmp1.r_asg_end_date.DELETE;
r_enc_period_tmp1.r_process_flag.DELETE;
r_enc_period_tmp1.r_period_ind.DELETE;
r_enc_period_tmp1.r_schedule_percent.DELETE;
r_enc_period_tmp1.r_encumbrance_amount.DELETE;
r_enc_period_tmp1.r_period_amount.DELETE;
r_enc_period_tmp1.r_reason_code.DELETE;
r_enc_period_tmp1.r_effective_date.DELETE;
DELETE psp_matrix_driver
WHERE run_id = l_run_id
AND (period_start_date > l_max_end_date
OR period_end_date < l_min_start_date
OR period_schedule_percent = 0);
UPDATE psp_matrix_driver pmd
SET period_end_date = period_end_date - 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND period_start_date = (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_line_id <> pmd.schedule_line_id
AND psl1.schedule_begin_date = pmd.period_end_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
UPDATE psp_matrix_driver pmd
SET period_end_date = period_end_date - 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND NOT (NOT EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_line_id <> pmd.schedule_line_id
AND psl1.schedule_begin_date = pmd.period_end_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_end_date = pmd.period_end_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id))
AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
UPDATE psp_matrix_driver pmd
SET period_start_date = period_start_date + 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND NOT EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_begin_date = pmd.period_start_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
UPDATE psp_matrix_driver pmd
SET period_start_date = period_start_date + 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_begin_date = pmd.period_start_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_line_id <> pmd.schedule_line_id
AND psl1.schedule_end_date = pmd.period_start_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
INSERT INTO psp_matrix_driver
(RUN_ID, SCHEDULE_LINE_ID,
PERIOD_START_DATE,
PERIOD_END_DATE,
PERIOD_SCHEDULE_PERCENT)
SELECT l_run_id, schedule_line_id,
GREATEST(l_min_start_date, schedule_chunk.schedule_begin_date(rowno)),
LEAST(l_max_end_date, schedule_chunk.schedule_end_date(rowno)),
schedule_percent
FROM psp_schedule_lines psl
WHERE schedule_hierarchy_id = l_schedule_hierarchy_id
AND schedule_end_date >= l_min_start_date
AND schedule_begin_date <= l_max_end_date
AND ( gl_code_combination_id IS NOT NULL
OR award_id IS NOT NULL)
AND psl.schedule_begin_date <= schedule_chunk.schedule_end_date(rowno)
AND psl.schedule_end_date >= schedule_chunk.schedule_begin_date(rowno);
initial_dates.delete;
date_type.delete;
schedule_chunk.schedule_end_date.delete;
schedule_chunk.schedule_begin_date.delete;
r_enc_period.r_time_period_id.DELETE;
r_enc_period.r_period_start_date.DELETE;
r_enc_period.r_period_end_date.DELETE;
r_enc_period.r_asg_start_date.DELETE;
r_enc_period.r_asg_end_date.DELETE;
r_enc_period.r_process_flag.DELETE;
r_enc_period.r_period_ind.DELETE;
r_enc_period.r_schedule_percent.DELETE;
r_enc_period.r_encumbrance_amount.DELETE;
r_enc_period.r_period_amount.DELETE;
r_enc_period.r_reason_code.DELETE;
r_enc_period.r_effective_date.DELETE;
DELETE psp_matrix_driver
WHERE run_id = l_run_id
AND (period_start_date > l_max_end_date
OR period_end_date < l_min_start_date
OR period_schedule_percent = 0);
UPDATE psp_matrix_driver pmd
SET period_end_date = period_end_date - 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND period_start_date = (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_line_id <> pmd.schedule_line_id
AND psl1.schedule_begin_date = pmd.period_end_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
UPDATE psp_matrix_driver pmd
SET period_end_date = period_end_date - 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND NOT (NOT EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_line_id <> pmd.schedule_line_id
AND psl1.schedule_begin_date = pmd.period_end_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_end_date = pmd.period_end_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id))
AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
UPDATE psp_matrix_driver pmd
SET period_start_date = period_start_date + 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND NOT EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_begin_date = pmd.period_start_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
UPDATE psp_matrix_driver pmd
SET period_start_date = period_start_date + 1
WHERE run_id = l_run_id
AND period_start_date < period_end_date
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_begin_date = pmd.period_start_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND EXISTS (SELECT 1
FROM psp_schedule_lines psl1
WHERE psl1.schedule_line_id <> pmd.schedule_line_id
AND psl1.schedule_end_date = pmd.period_start_date
AND psl1.schedule_hierarchy_id = l_schedule_hierarchy_id)
AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
FROM psp_schedule_lines psl1
WHERE psl1.schedule_hierarchy_id = l_schedule_hierarchy_id);
INSERT INTO psp_matrix_driver
(RUN_ID, SCHEDULE_LINE_ID,
PERIOD_START_DATE, PERIOD_END_DATE,
PERIOD_SCHEDULE_PERCENT)
SELECT l_run_id, schedule_line_id,
GREATEST(l_min_start_date, schedule_chunk.schedule_begin_date(rowno)),
LEAST(l_max_end_date, schedule_chunk.schedule_end_date(rowno)),
schedule_percent
FROM psp_schedule_lines psl
WHERE schedule_hierarchy_id = l_schedule_hierarchy_id
AND schedule_end_date >= l_min_start_date
AND schedule_begin_date <= l_max_end_date
AND ( gl_code_combination_id IS NOT NULL
OR award_id IS NOT NULL)
AND psl.schedule_begin_date <= schedule_chunk.schedule_end_date(rowno)
AND psl.schedule_end_date >= schedule_chunk.schedule_begin_date(rowno);
initial_dates.delete;
date_type.delete;
schedule_chunk.schedule_end_date.delete;
schedule_chunk.schedule_begin_date.delete;
r_sch_rec.start_date.delete;
r_sch_rec.end_date.delete;
r_sch_rec.schedule_percent.delete;
r_enc_period_tmp1.r_time_period_id.DELETE;
r_enc_period_tmp1.r_period_start_date.DELETE;
r_enc_period_tmp1.r_period_end_date.DELETE;
r_enc_period_tmp1.r_asg_start_date.DELETE;
r_enc_period_tmp1.r_asg_end_date.DELETE;
r_enc_period_tmp1.r_process_flag.DELETE;
r_enc_period_tmp1.r_period_ind.DELETE;
r_enc_period_tmp1.r_schedule_percent.DELETE;
r_enc_period_tmp1.r_encumbrance_amount.DELETE;
r_enc_period_tmp1.r_period_amount.DELETE;
r_enc_period_tmp1.r_reason_code.DELETE;
r_enc_period_tmp1.r_effective_date.DELETE;
r_sch_rec.start_date.delete;
r_sch_rec.end_date.delete;
r_sch_rec.schedule_percent.delete;
r_enc_period.r_time_period_id.DELETE;
r_enc_period.r_period_start_date.DELETE;
r_enc_period.r_period_end_date.DELETE;
r_enc_period.r_asg_start_date.DELETE;
r_enc_period.r_asg_end_date.DELETE;
r_enc_period.r_process_flag.DELETE;
r_enc_period.r_period_ind.DELETE;
r_enc_period.r_schedule_percent.DELETE;
r_enc_period.r_encumbrance_amount.DELETE;
r_enc_period.r_period_amount.DELETE;
r_enc_period.r_reason_code.DELETE;
r_enc_period.r_effective_date.DELETE;
r_enc_period.r_time_period_id.DELETE;
r_enc_period.r_period_start_date.DELETE;
r_enc_period.r_period_end_date.DELETE;
r_enc_period.r_asg_start_date.DELETE;
r_enc_period.r_asg_end_date.DELETE;
r_enc_period.r_process_flag.DELETE;
r_enc_period.r_period_ind.DELETE;
r_enc_period.r_schedule_percent.DELETE;
r_enc_period.r_encumbrance_amount.DELETE;
r_enc_period.r_period_amount.DELETE;
r_enc_period.r_reason_code.DELETE;
r_enc_period.r_effective_date.DELETE;
r_enc_period_tmp1.r_time_period_id.DELETE;
r_enc_period_tmp1.r_period_start_date.DELETE;
r_enc_period_tmp1.r_period_end_date.DELETE;
r_enc_period_tmp1.r_asg_start_date.DELETE;
r_enc_period_tmp1.r_asg_end_date.DELETE;
r_enc_period_tmp1.r_process_flag.DELETE;
r_enc_period_tmp1.r_period_ind.DELETE;
r_enc_period_tmp1.r_schedule_percent.DELETE;
r_enc_period_tmp1.r_encumbrance_amount.DELETE;
r_enc_period_tmp1.r_period_amount.DELETE;
r_enc_period_tmp1.r_reason_code.DELETE;
r_enc_period_tmp1.r_effective_date.DELETE;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_updated_login NUMBER;
NO_UPDATE_REC_FOUND EXCEPTION;
SELECT fnd_number.canonical_to_number(NVL(argument11, -1)),
argument12
FROM fnd_concurrent_requests
WHERE request_id = l_request_id;
SELECT pep.payroll_id
FROM psp_enc_payrolls pep
WHERE pep.business_group_id = l_business_group_id
AND pep.set_of_books_id = l_set_of_books_id;
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)
VALUES
(l_request_id, l_process_code, pactid,
'I', NULL, l_business_group_id,
l_set_of_books_id, l_last_update_date, l_last_updated_by,
l_last_update_date, l_last_updated_by, l_last_updated_login);
sqlstr := 'SELECT DISTINCT assignment_id FROM psp_enc_changed_assignments WHERE ';
INSERT INTO psp_enc_process_assignments
(payroll_action_id, assignment_id, payroll_id,
assignment_status, creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
SELECT DISTINCT pactid, assignment_id, payroll_id,
'I', l_last_update_date, l_last_updated_by,
l_last_update_date, l_last_updated_by, l_last_updated_login
FROM psp_enc_changed_assignments
WHERE payroll_action_id = pactid;
RAISE NO_UPDATE_REC_FOUND;
WHEN NO_UPDATE_REC_FOUND THEN
psp_message_s.print_error (p_mode => FND_FILE.LOG, p_print_header => FND_API.G_FALSE);
SELECT DISTINCT assignment_id
FROM psp_enc_changed_assignments
WHERE assignment_id BETWEEN start_asg AND end_asg
AND payroll_action_id = p_pactid;
SELECT pay_assignment_actions_s.NEXTVAL INTO l_asg_action_id FROM DUAL;
SELECT fnd_number.canonical_to_number(NVL(argument11, -1)),
argument12
FROM fnd_concurrent_requests fcr,
psp_enc_processes pep
WHERE pep.payroll_action_id = p_payroll_action_id
AND fcr.request_id = pep.request_id;
SELECT MIN(object_id),
MAX(object_id)
FROM pay_temp_object_actions
WHERE payroll_action_id = p_payroll_action_id
AND chunk_number = p_chunk_number;
SELECT DISTINCT payroll_id,
assignment_id,
change_date
FROM psp_enc_changed_assignments peca
WHERE assignment_id BETWEEN l_min_asg_id AND l_max_asg_id
AND ( (l_process_mode = 'TERMINATE' AND change_type = 'TR')
OR (l_process_mode = 'REGULAR' AND change_type <> 'TR'))
AND payroll_id = NVL(l_payroll_id, payroll_id)
/*AND NOT EXISTS (SELECT 1
FROM psp_enc_process_assignments pepa
WHERE pepa.assignment_id = peca.assignment_id
AND pepa.payroll_action_id = p_payroll_action_id
AND pepa.assignment_status <> 'B')*/;
SELECT DISTINCT
pet.element_type_id,
pc.costing_debit_or_credit
FROM PAY_ELEMENT_ENTRIES_F pee,
PAY_ELEMENT_LINKS_F pel,
PAY_ELEMENT_TYPES_F pet,
PER_ASSIGNMENTS_F pa,
PAY_ELEMENT_CLASSIFICATIONS pc
WHERE pee.assignment_id = p_assignment_id
AND pa.assignment_id = p_assignment_id
AND pee.effective_end_date >= pa.effective_start_date
AND pee.effective_start_date <= pa.effective_end_date
AND pee.element_link_id = pel.element_link_id
AND pel.effective_end_date >= pa.effective_start_date
AND pel.effective_start_date <= pa.effective_end_date
AND pee.entry_type = 'E'
AND pel.element_type_id = pet.element_type_id
AND pet.effective_end_date >= pa.effective_start_date
AND pet.effective_start_date <=pa.effective_end_date
AND pel.business_group_id = l_business_group_id
AND pet.element_type_id IN ( SELECT element_type_id
FROM psp_enc_elements
WHERE business_group_id = l_business_group_id
AND set_of_books_id = l_set_of_books_id)
AND pet.classification_id = pc.classification_id
ORDER BY pet.element_type_id;
SELECT COUNT(1),
NVL(MAX(pelh.encumbrance_date),p_enc_begin_date)
FROM psp_enc_lines_history pelh
WHERE pelh.assignment_id = l_assignment_id
AND pelh.enc_element_type_id = p_element_type_id
AND pelh.payroll_id = l_payroll_id;
SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.business_group_id = l_business_group_id
AND ppa.payroll_id = l_payroll_id
AND NVL(paa.assignment_id, p_assignment_id) = p_assignment_id
AND ppa.action_type IN ( 'R','Q')
AND NVL(paa.action_status, ppa.action_status) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id;
SELECT MIN(ptp.start_date)
FROM per_time_periods ptp
WHERE ptp.payroll_id = l_payroll_id;
SELECT NVL(parameter_value,1)
FROM psp_enc_setup_options peso
WHERE peso.setup_parameter ='EFFECTIVE_DATE'
AND peso.business_group_id = l_business_group_id
AND peso.set_of_books_id = l_set_of_books_id;
SELECT COUNT(1), NVL(MAX(pel.encumbrance_date),p_enc_begin_date)
FROM psp_enc_lines pel
WHERE pel.enc_element_type_id = p_element_type_id
AND pel.assignment_id = p_assignment_id
AND pel.payroll_id = l_payroll_id;
SELECT request_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT assignment_number,
person_id,
organization_id
FROM per_all_assignments_f
WHERE assignment_id = l_assignment_id
AND payroll_id = l_payroll_id
AND effective_end_date >= p_effective_date
AND ROWNUM = 1;
SELECT payroll_name
FROM pay_all_payrolls_f
WHERE payroll_id = l_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 element_name
FROM pay_element_types_f
WHERE element_type_id = l_element_type_id
AND ROWNUM = 1;
SELECT pcv_information2 employee_hours
FROM pqp_configuration_values
WHERE pcv_information_category = 'PSP_IMPORT_EMPLOYEE_HOURS'
AND legislation_code IS NULL
AND NVL(business_group_id, l_business_group_id) = l_business_group_id;
SELECT pcv_information1 global_element_autopop,
pcv_information2 element_type_autopop,
pcv_information3 element_class_autopop,
pcv_information4 assignment_autopop,
pcv_information5 default_schedule_autopop,
pcv_information6 default_account_autopop,
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, l_business_group_id) = l_business_group_id
ORDER BY business_group_id;
SELECT COUNT(1)
FROM psp_enc_changed_assignments
WHERE assignment_id = l_assignment_id
AND payroll_id = l_payroll_id
AND payroll_action_id = p_payroll_action_id
AND change_type <> 'LQ';
SELECT COUNT(1)
FROM psp_enc_changed_assignments
WHERE assignment_id = l_assignment_id
AND payroll_id = l_payroll_id
AND payroll_action_id = p_payroll_action_id
AND change_type = 'ZZ';
SELECT COUNT(1)
FROM psp_enc_elements pee
WHERE element_type_id = p_element_type_id
AND ( formula_id IS NOT NULL
OR EXISTS (SELECT 1
FROM pay_input_values_f piv
WHERE piv.input_value_id = pee.input_value_id
AND SUBSTR(piv.uom, 1, 1) <> 'H'));
SELECT COUNT(1)
FROM psp_enc_elements pee
WHERE element_type_id = p_element_type_id
AND ( formula_id IS NOT NULL
OR EXISTS (SELECT 1
FROM pay_input_values_f piv
WHERE piv.input_value_id = pee.input_value_id
AND SUBSTR(piv.uom, 1, 1) = 'H'));
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
WHERE task_id = p_task_id;
SELECT name
FROM hr_organization_units
WHERE organization_id = p_expenditure_org_id;
cel_warnings.start_date.DELETE;
cel_warnings.end_date.DELETE;
cel_warnings.warning_code.DELETE;
cel_warnings.project_id.DELETE;
cel_warnings.task_id.DELETE;
cel_warnings.award_id.DELETE;
cel_warnings.exp_org_id.DELETE;
cel_warnings.exp_type.DELETE;
cel_warnings.effective_date.DELETE;
cel_warnings.error_status.DELETE;
cel_warnings.percent.DELETE;
delete_previous_error_log(p_assignment_id => l_assignment_id,
p_payroll_id => l_payroll_id,
p_payroll_action_id => p_payroll_action_id);
UPDATE psp_enc_lines_history pelh
SET change_flag = 'N'
WHERE assignment_id = l_assignment_id
AND payroll_id = l_payroll_id
AND change_flag = 'U'
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = l_assignment_id
AND pesl.payroll_id = l_payroll_id
AND status_code = 'A'
AND pesl.enc_summary_line_id = pelh.enc_summary_line_id);
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Updated lines to be liquidated');
DELETE psp_enc_changed_assignments
WHERE assignment_id = l_assignment_id
AND payroll_id = l_payroll_id
AND change_type = 'ZZ';
UPDATE psp_enc_lines_history pelh
SET change_flag = 'N'
WHERE assignment_id = l_assignment_id
AND payroll_id = l_payroll_id
AND change_flag = 'U'
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = l_assignment_id
AND pesl.payroll_id = l_payroll_id
AND status_code = 'A'
AND pesl.enc_summary_line_id = pelh.enc_summary_line_id
AND pesl.effective_date <= (NVL((SELECT MAX (ptp.end_date)--MAX(ppa.date_earned): Bug 5642002: Replaced earned date with period end date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.payroll_id = l_payroll_id
AND ppa.action_type IN( 'R','Q')
AND NVL(paa.action_status, ppa.action_status) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date)) );
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Updated lines to be liquidated by regular liquidation');
UPDATE psp_enc_lines_history pelh
SET change_flag = 'U'
WHERE assignment_id = l_assignment_id
AND payroll_id = l_payroll_id
AND change_flag = 'N'
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = l_assignment_id
AND pesl.payroll_id = l_payroll_id
AND status_code = 'A'
AND pesl.enc_summary_line_id = pelh.enc_summary_line_id
AND pesl.effective_date > (NVL((SELECT MAX (ptp.end_date)--MAX(ppa.date_earned): Bug 5642002: Replaced earned date with period end date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.payroll_id = l_payroll_id
AND ppa.action_type IN( 'R','Q')
AND NVL(paa.action_status, ppa.action_status) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date)) );
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Updated lines not to be liquidated by regular liquidation');
update_hierarchy_dates (p_payroll_action_id => p_payroll_action_id,
p_payroll_id => l_payroll_id,
p_assignment_id => l_assignment_id,
p_return_status => l_return_status);
UPDATE psp_enc_process_assignments pepa
SET assignment_status = 'B'
WHERE pepa.payroll_action_id = p_payroll_action_id
AND pepa.assignment_id = t_assignments.asg_array(recno)
AND pepa.payroll_id = t_assignments.payroll_array(recno)
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.assignment_id = t_assignments.asg_array(recno)
AND pesl.payroll_id = t_assignments.payroll_array(recno));
UPDATE psp_enc_process_assignments pepa
SET assignment_status = 'S'
WHERE pepa.payroll_action_id = p_payroll_action_id
AND pepa.assignment_id = t_assignments.asg_array(recno)
AND pepa.payroll_id = t_assignments.payroll_array(recno)
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.assignment_id = t_assignments.asg_array(recno)
AND pesl.payroll_id = t_assignments.payroll_array(recno));
UPDATE psp_enc_process_assignments pepa
SET assignment_status = 'L'
WHERE pepa.payroll_action_id = p_payroll_action_id
AND pepa.assignment_id = t_assignments.asg_array(recno)
AND pepa.payroll_id = t_assignments.payroll_array(recno)
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.superceded_line_id IS NOT NULL
AND pesl.assignment_id = t_assignments.asg_array(recno)
AND pesl.payroll_id = t_assignments.payroll_array(recno));
t_assignments.payroll_array.delete;
t_assignments.asg_array.delete;
l_last_update_date DATE;
l_last_updated_by NUMBER;
SELECT argument12
FROM fnd_concurrent_requests fcr,
psp_enc_processes pep
WHERE pep.payroll_action_id = p_payroll_action_id
AND fcr.request_id = pep.request_id;
SELECT process_phase
FROM psp_enc_processes
WHERE payroll_action_id = p_payroll_action_id
AND process_code = 'ST';
SELECT COUNT(1)
FROM psp_report_errors
WHERE payroll_action_id = p_payroll_action_id
AND request_id >= l_request_id
AND message_level = 'E';
SELECT COUNT(1)
FROM psp_enc_summary_lines
WHERE payroll_action_id = p_payroll_action_id
AND status_code = 'N';
l_last_update_date := SYSDATE;
l_last_updated_by:= NVL(FND_GLOBAL.USER_ID, -1);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Create and Update Encumbrance process has errors. Please review the Run Results Report for more details.');
UPDATE psp_enc_process_assignments pepa
SET assignment_status = 'B'
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');
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);
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);
DELETE FROM psp_enc_controls pec
WHERE pec.payroll_action_id = p_payroll_action_id
AND NOT EXISTS (SELECT 1
FROM psp_enc_lines pel
WHERE pel.enc_control_id = pec.enc_control_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted lines in psp_enc_controls which doesnt have a line in psp_enc_lines');
UPDATE psp_enc_controls pec
SET (action_code,
number_of_dr, number_of_cr,
total_dr_amount, total_cr_amount,
gl_dr_amount, gl_cr_amount,
ogm_dr_amount, ogm_cr_amount) =
(SELECT 'N',
SUM(fnd_number.canonical_to_number(DECODE(pel.dr_cr_flag, 'D', 1, 0))), SUM(fnd_number.canonical_to_number(DECODE(pel.dr_cr_flag, 'C', 1, 0))),
SUM(fnd_number.canonical_to_number(DECODE(pel.dr_cr_flag, 'D', pel.encumbrance_amount, 0))), SUM(fnd_number.canonical_to_number(DECODE(pel.dr_cr_flag, 'C', pel.encumbrance_amount, 0))),
SUM(fnd_number.canonical_to_number(DECODE(pel.gl_project_flag, 'G', DECODE(pel.dr_cr_flag, 'D', pel.encumbrance_amount, 0), 0))),
SUM(fnd_number.canonical_to_number(DECODE(pel.gl_project_flag, 'G', DECODE(pel.dr_cr_flag, 'C', pel.encumbrance_amount, 0), 0))),
SUM(fnd_number.canonical_to_number(DECODE(pel.gl_project_flag, 'P', DECODE(pel.dr_cr_flag, 'D', pel.encumbrance_amount, 0), 0))),
SUM(fnd_number.canonical_to_number(DECODE(pel.gl_project_flag, 'P', DECODE(pel.dr_cr_flag, 'C', pel.encumbrance_amount, 0), 0)))
FROM psp_enc_lines pel
WHERE pel.enc_control_id = pec.enc_control_id)
WHERE payroll_action_id = p_payroll_action_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated summary columns in psp_enc_controls');
UPDATE psp_enc_changed_assignments peca
SET payroll_action_id = p_payroll_action_id
WHERE EXISTS (SELECT 1
FROM psp_enc_changed_assignments peca2
WHERE peca2.assignment_id = peca.assignment_id
AND peca2.change_type = 'TR');
INSERT INTO psp_enc_changed_asg_history
(request_id, assignment_id, payroll_id, change_type, processing_module, created_by,
creation_date, processed_flag, reference_id, action_type, payroll_action_id, change_date)
SELECT l_request_id, peca.assignment_id, peca.payroll_id, peca.change_type,
'U', l_last_updated_by, l_last_update_date, NULL, NVL(peca.reference_id, 0),
NVL(peca.action_type, 'U'), p_payroll_action_id, change_date
FROM psp_enc_changed_assignments peca
WHERE payroll_action_id = p_payroll_action_id;
DELETE psp_enc_changed_assignments peca
WHERE peca.payroll_action_id = p_payroll_action_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted processed change assignment records in psp_enc_change_assignments');
UPDATE psp_enc_processes
SET process_status = 'P'
WHERE payroll_action_id = p_payroll_action_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_status in psp_enc_processes');
UPDATE psp_enc_processes
SET process_status = 'B',
process_phase = 'no_summarize_transfer'
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');
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);
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 in psp_enc_processes');
UPDATE psp_enc_processes
SET process_phase = 'deinit_st'
WHERE payroll_action_id = p_payroll_action_id
AND process_code = 'ST';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase in psp_enc_processes');
SELECT DISTINCT pelh.time_period_id ,
pelh.encumbrance_date ,
pelh.dr_cr_flag,
pelh.encumbrance_amount ,
pelh.gl_project_flag,
pelh.enc_element_type_id,
NVL(pelh.schedule_line_id,-99) ,
NVL(pelh.org_schedule_id, -99) ,
NVL(pelh.default_org_account_id, -99),
NVL(pelh.suspense_org_account_id, -99),
NVL(pelh.element_account_id, -99) ,
NVL(pelh.project_id, -99),
NVL(pelh.task_id, -99) ,
NVL(pelh.award_id, -99),
NVL(pelh.expenditure_type, '-99') ,
NVL(pelh.expenditure_organization_id, -99) ,
NVL(pelh.gl_code_combination_id, -99),
NVL(pelh.attribute_category,'NULL_VALUE'),
NVL(pelh.attribute1, 'NULL_VALUE'),
NVL(pelh.attribute2, 'NULL_VALUE'),
NVL(pelh.attribute3, 'NULL_VALUE'),
NVL(pelh.attribute4, 'NULL_VALUE'),
NVL(pelh.attribute5, 'NULL_VALUE'),
NVL(pelh.attribute6, 'NULL_VALUE'),
NVL(pelh.attribute7, 'NULL_VALUE'),
NVL(pelh.attribute8, 'NULL_VALUE'),
NVL(pelh.attribute9, 'NULL_VALUE'),
NVL(pelh.attribute10, 'NULL_VALUE'),
NVL(pelh.default_reason_code, 'NULL'),
NVL(pelh.suspense_reason_code, 'NULL'),
hierarchy_code
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'U'
AND payroll_id = p_payroll_id
AND assignment_id = p_assignment_id;
SELECT DISTINCT enc_summary_line_id
FROM psp_enc_lines_history
WHERE change_flag = 'N'
AND payroll_id = p_payroll_id
AND assignment_id = p_assignment_id;
l_delete_flag CHAR(1);
UPDATE psp_enc_lines_history
SET change_flag = 'N'
WHERE assignment_id = p_assignment_id
AND payroll_id = p_payroll_id
AND change_flag = 'U'
AND status_code = 'A';
UPDATE psp_enc_lines_history pelh
SET change_flag='U'
WHERE time_period_id = t_enc_lines_array.r_time_period_id(recno)
AND change_flag = 'N'
AND pelh.encumbrance_date = t_enc_lines_array.r_encumbrance_date(recno)
AND pelh.enc_element_type_id = t_enc_lines_array.r_enc_element_type_id(recno)
AND pelh.dr_cr_flag = t_enc_lines_array.r_dr_cr_flag(recno)
AND pelh.encumbrance_amount = ROUND( t_enc_lines_array.r_encumbrance_amount(recno),g_precision)
AND pelh.gl_project_flag = t_enc_lines_array.r_gl_project_flag(recno)
AND pelh.hierarchy_code = t_enc_lines_array.r_hierarchy_code(recno)
AND NVL(pelh.project_id, -99) = NVL(t_enc_lines_array.r_project_id(recno), -99)
AND NVL(pelh.task_id, -99) = NVL(t_enc_lines_array.r_task_id(recno), -99)
AND NVL(pelh.award_id, -99) = NVL(t_enc_lines_array.r_award_id(recno), -99)
AND NVL(pelh.expenditure_type, '-99') = NVL(t_enc_lines_array.r_expenditure_type(recno), '-99')
AND NVL(pelh.expenditure_organization_id, -99) = NVL(t_enc_lines_array.r_expenditure_organization_id(recno), -99)
AND NVL(pelh.gl_code_combination_id, -99) = NVL(t_enc_lines_array.r_gl_code_combination_id(recno), -99)
AND NVL(suspense_reason_code, 'NULL') = NVL(t_enc_lines_array.r_suspense_reason_code(recno), 'NULL')
AND NVL(default_reason_code, 'NULL') = NVL(t_enc_lines_array.r_default_reason_code(recno), 'NULL')
AND pelh.assignment_id = p_assignment_id
AND pelh.payroll_id = p_payroll_id;
UPDATE psp_enc_lines_history pelh
SET change_flag='U'
WHERE time_period_id = t_enc_lines_array.r_time_period_id(recno)
AND change_flag = 'N'
AND pelh.encumbrance_date = t_enc_lines_array.r_encumbrance_date(recno)
AND pelh.enc_element_type_id = t_enc_lines_array.r_enc_element_type_id(recno)
AND pelh.dr_cr_flag = t_enc_lines_array.r_dr_cr_flag(recno)
AND pelh.encumbrance_amount = ROUND( t_enc_lines_array.r_encumbrance_amount(recno),g_precision)
AND pelh.gl_project_flag = t_enc_lines_array.r_gl_project_flag(recno)
AND pelh.hierarchy_code = t_enc_lines_array.r_hierarchy_code(recno)
AND NVL(pelh.project_id, -99) = NVL(t_enc_lines_array.r_project_id(recno), -99)
AND NVL(pelh.task_id, -99) = NVL(t_enc_lines_array.r_task_id(recno), -99)
AND NVL(pelh.award_id, -99) = NVL(t_enc_lines_array.r_award_id(recno), -99)
AND NVL(pelh.expenditure_type, '-99') = NVL(t_enc_lines_array.r_expenditure_type(recno), '-99')
AND NVL(pelh.expenditure_organization_id, -99) = NVL(t_enc_lines_array.r_expenditure_organization_id(recno), -99)
AND NVL(pelh.gl_code_combination_id, -99) = NVL(t_enc_lines_array.r_gl_code_combination_id(recno), -99)
AND NVL(suspense_reason_code, 'NULL') = NVL(t_enc_lines_array.r_suspense_reason_code(recno), 'NULL')
AND NVL(default_reason_code, 'NULL') = NVL(t_enc_lines_array.r_default_reason_code(recno), 'NULL')
AND pelh.assignment_id = p_assignment_id
AND pelh.payroll_id = p_payroll_id
AND NVL(pelh.attribute_category, 'NULL_VALUE') = t_enc_lines_array.r_attribute_category(recno)
AND NVL(pelh.attribute1, 'NULL_VALUE') = t_enc_lines_array.r_attribute1(recno)
AND NVL(pelh.attribute2, 'NULL_VALUE') = t_enc_lines_array.r_attribute2(recno)
AND NVL(pelh.attribute3, 'NULL_VALUE') = t_enc_lines_array.r_attribute3(recno)
AND NVL(pelh.attribute4, 'NULL_VALUE') = t_enc_lines_array.r_attribute4(recno)
AND NVL(pelh.attribute5, 'NULL_VALUE') = t_enc_lines_array.r_attribute5(recno)
AND NVL(pelh.attribute6, 'NULL_VALUE') = t_enc_lines_array.r_attribute6(recno)
AND NVL(pelh.attribute7, 'NULL_VALUE') = t_enc_lines_array.r_attribute7(recno)
AND NVL(pelh.attribute8, 'NULL_VALUE') = t_enc_lines_array.r_attribute8(recno)
AND NVL(pelh.attribute9, 'NULL_VALUE') = t_enc_lines_array.r_attribute9(recno)
AND NVL(pelh.attribute10, 'NULL_VALUE') = t_enc_lines_array.r_attribute10(recno);
UPDATE psp_enc_lines_history pelh
SET change_flag='N'
WHERE enc_summary_line_id = l_enc_summary_line_id_tl(recno)
AND change_flag='U';
l_enc_summary_line_id_tl.DELETE;
l_delete_flag := 'N';
l_delete_flag := 'Y';
IF (l_delete_flag = 'N') THEN
t_enc_lines_array2.r_enc_element_type_id(l_enc_lines_no) := t_enc_lines_array.r_enc_element_type_id(recno1);
l_delete_flag := 'N';
l_delete_flag := 'Y';
IF (l_delete_flag = 'N') THEN
t_enc_lines_array2.r_enc_element_type_id(l_enc_lines_no) := t_enc_lines_array.r_enc_element_type_id(recno1);
t_enc_lines_array.r_enc_element_type_id.DELETE;
t_enc_lines_array.r_encumbrance_date.DELETE;
t_enc_lines_array.r_dr_cr_flag.DELETE;
t_enc_lines_array.r_encumbrance_amount.DELETE;
t_enc_lines_array.r_enc_line_type.DELETE;
t_enc_lines_array.r_schedule_line_id.DELETE;
t_enc_lines_array.r_org_schedule_id.DELETE;
t_enc_lines_array.r_default_org_account_id.DELETE;
t_enc_lines_array.r_suspense_org_account_id.DELETE;
t_enc_lines_array.r_element_account_id.DELETE;
t_enc_lines_array.r_gl_project_flag.DELETE;
t_enc_lines_array.r_person_id.DELETE;
t_enc_lines_array.r_assignment_id.DELETE;
t_enc_lines_array.r_award_id.DELETE;
t_enc_lines_array.r_task_id.DELETE;
t_enc_lines_array.r_expenditure_type.DELETE;
t_enc_lines_array.r_expenditure_organization_id.DELETE;
t_enc_lines_array.r_project_id.DELETE;
t_enc_lines_array.r_gl_code_combination_id.DELETE;
t_enc_lines_array.r_time_period_id.DELETE;
t_enc_lines_array.r_default_reason_code.DELETE;
t_enc_lines_array.r_suspense_reason_code.DELETE;
t_enc_lines_array.r_enc_control_id.DELETE;
t_enc_lines_array.r_change_flag.DELETE;
t_enc_lines_array.r_enc_start_date.DELETE;
t_enc_lines_array.r_enc_end_date.DELETE;
t_enc_lines_array.r_attribute_category.DELETE;
t_enc_lines_array.r_attribute1.DELETE;
t_enc_lines_array.r_attribute2.DELETE;
t_enc_lines_array.r_attribute3.DELETE;
t_enc_lines_array.r_attribute4.DELETE;
t_enc_lines_array.r_attribute5.DELETE;
t_enc_lines_array.r_attribute6.DELETE;
t_enc_lines_array.r_attribute7.DELETE;
t_enc_lines_array.r_attribute8.DELETE;
t_enc_lines_array.r_attribute9.DELETE;
t_enc_lines_array.r_attribute10.DELETE;
t_enc_lines_array.r_orig_gl_code_combination_id.DELETE;
t_enc_lines_array.r_orig_project_id.DELETE;
t_enc_lines_array.r_orig_award_id.DELETE;
t_enc_lines_array.r_orig_task_id.DELETE;
t_enc_lines_array.r_orig_expenditure_type.DELETE;
t_enc_lines_array.r_orig_expenditure_org_id.DELETE;
t_enc_lines_array.r_hierarchy_code.DELETE;
t_enc_lines_array.r_hierarchy_start_date.DELETE;
t_enc_lines_array.r_hierarchy_end_date.DELETE;
--delete_previous_error_log(p_assignment_id => p_assignment_id,
--p_payroll_id => p_payroll_id,
--p_payroll_action_id => g_payroll_action_id);
insert_enc_lines_from_arrays (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_enc_line_type => 'U',
p_return_status => l_return_status);
fnd_msg_pub.add_exc_msg('PSP_ENC_UPDATE_LINES', 'VERIFY_CHANGES');
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
INSERT INTO psp_enc_summary_lines
(enc_summary_line_id, business_group_id, set_of_books_id,
enc_control_id, time_period_id, person_id,
assignment_id, effective_date, 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, superceded_line_id,
attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8,
attribute9, attribute10, payroll_action_id,
proposed_termination_date, last_update_date, last_updated_by,
last_update_login, created_by, creation_date,
update_flag, org_id)
SELECT psp_enc_summary_lines_s.NEXTVAL,
p_business_group_id,
p_set_of_books_id,
pesl.enc_control_id,
pesl.time_period_id,
pesl.person_id,
pesl.assignment_id,
pesl.effective_date,
pesl.gl_code_combination_id,
pesl.project_id,
pesl.task_id,
pesl.award_id,
pesl.expenditure_organization_id,
pesl.expenditure_type,
DECODE(pesl.gl_project_flag, 'G', pesl.summary_amount, -pesl.summary_amount),
DECODE(pesl.dr_cr_flag, 'C', 'D', 'D', 'C') dr_cr_flag,
'N',
pesl.payroll_id,
pesl.gl_project_flag,
pesl.enc_summary_line_id,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute_category, NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10,
p_payroll_action_id,
g_actual_term_date,
SYSDATE,
l_last_updated_by,
l_last_update_login,
l_last_updated_by,
SYSDATE,
DECODE(SIGN(TRUNC(effective_date)-TRUNC(p_enc_begin_date)), 1, 'U', 'L'),
pesl.org_id
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = p_assignment_id
AND pesl.payroll_id = p_payroll_id
AND pesl.status_code = 'A'
AND pesl.enc_summary_line_id IN (SELECT pelh.enc_summary_line_id
FROM psp_enc_lines_history pelh
WHERE pelh.change_flag = 'N'
AND pelh.assignment_id = p_assignment_id
AND pelh.payroll_id = p_payroll_id);
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.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,
PSP_ORGANIZATION_ACCOUNTS pos,
pa_projects_all pa
WHERE pel.ENCUMBRANCE_AMOUNT <> 0
AND pel.assignment_id = p_assignment_id
AND pel.payroll_id = p_payroll_id
AND pel.suspense_org_account_id = pos.organization_account_id(+)
AND pa.project_id (+) = pel.project_id
AND pel.payroll_action_id = p_payroll_action_id
GROUP BY pel.enc_control_id,
pel.time_period_id,
pel.person_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, payroll_action_id,
proposed_termination_date, last_update_date, last_updated_by,
last_update_login, created_by, creation_date,
org_id)
VALUES (t_sum_lines.enc_summary_line_id(recno), p_business_group_id,
t_sum_lines.enc_control_id(recno), t_sum_lines.time_period_id(recno),
t_sum_lines.person_id(recno), p_assignment_id,
t_sum_lines.effective_date(recno), p_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', p_payroll_id, 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), p_payroll_action_id,
g_actual_term_date, SYSDATE,
l_last_updated_by, l_last_update_login,
l_last_updated_by, SYSDATE,
t_sum_lines.org_id(recno));
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.assignment_id = p_assignment_id
AND pel.payroll_id = p_payroll_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.assignment_id = p_assignment_id
AND pel.payroll_id = p_payroll_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);
SELECT COUNT(1)
FROM psp_enc_controls
WHERE ROWNUM = 1;
SELECT fnd_number.canonical_to_number(NVL(argument13, '-1')),
fnd_date.canonical_to_date(NVL(argument14, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
fnd_number.canonical_to_number(NVL(argument15, '-1')),
fnd_date.canonical_to_date(NVL(argument16, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
fnd_number.canonical_to_number(NVL(argument17, '-1')),
fnd_date.canonical_to_date(NVL(argument18, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
fnd_number.canonical_to_number(NVL(argument19, '-1')),
fnd_date.canonical_to_date(NVL(argument20, fnd_date.date_to_canonical(TRUNC(SYSDATE)))),
fnd_number.canonical_to_number(NVL(argument21, '-1')),
fnd_date.canonical_to_date(NVL(argument22, fnd_date.date_to_canonical(TRUNC(SYSDATE))))
FROM psp_enc_processes pep,
fnd_concurrent_requests fcr
WHERE pep.payroll_action_id = p_payroll_action_id
AND fcr.request_id = pep.request_id;
SELECT pep.inc_exc_flag
FROM psp_enc_payrolls pep
WHERE pep.payroll_id = p_payroll_id
AND pep.business_group_id = l_business_group_id
AND pep.set_of_books_id = l_set_of_books_id;
SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.business_group_id = l_business_group_id
AND ppa.payroll_id = p_payroll_id
AND ppa.action_type IN ( 'R','Q')
AND NVL(paa.action_status, ppa.action_status) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id;
SELECT MIN(ptp.start_date)
FROM per_time_periods ptp
WHERE ptp.payroll_id= p_payroll_id;
SELECT assignment_number,
person_id,
organization_id
FROM per_all_assignments_f
WHERE assignment_id = l_assignment_id
AND payroll_id = NVL(l_payroll_id, p_payroll_id)
AND effective_end_date >= p_start_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
AND ROWNUM = 1;
SELECT full_name
FROM per_all_people_f
WHERE person_id = l_person_id
AND effective_end_date >= p_start_date
AND ROWNUM = 1;
SELECT name
FROM hr_organization_units
WHERE organization_id = l_organization_id;
SELECT pep.request_id || ': ' || fcpt.user_concurrent_program_name
FROM psp_enc_processes pep,
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt
WHERE EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = pep.payroll_action_id
AND pesl.payroll_action_id = l_payroll_action_id)
AND fcr.request_id = pep.request_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcpt.language = USERENV('LANG')
ORDER BY DECODE(pep.process_code, 'LET', 1, 'ST', 2, 3);
SELECT DISTINCT assignment_id,
payroll_id,
payroll_action_id
FROM psp_enc_summary_lines pesl
WHERE pesl.person_id = p_person_id
AND pesl.status_code = 'N';
SELECT pepa.assignment_id,
pepa.payroll_action_id
FROM psp_enc_process_assignments pepa
WHERE pepa.payroll_id = p_payroll_id
AND pepa.assignment_status NOT IN ('B', 'P');
IF (l_new_cust = 0 AND psp_general.start_capturing_updates(l_business_group_id) = 'N') THEN
l_pre_process_mode := 'F';
select prev_enc_end_date into l_prev_enc_end_date
from PSP_ENC_END_DATES_V
where business_group_id = l_business_group_id
and set_of_books_id = l_set_of_books_id;
INSERT INTO psp_enc_changed_assignments
(request_id, payroll_id, assignment_id, change_type, payroll_action_id)
SELECT DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'CR', p_payroll_action_id
FROM per_assignments_f pa
WHERE pa.payroll_id = p_payroll_id
AND pa.assignment_type = 'E'
AND pa.business_group_id = l_business_group_id
-- AND pa.effective_end_date >= l_enc_begin_date
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pa.effective_end_date >= (NVL((SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE paa.assignment_id(+) = pa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.business_group_id = pa.business_group_id
AND ppa.payroll_id = pa.payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status(+) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date))
AND NOT EXISTS (SELECT pepa.assignment_id
FROM psp_enc_payroll_assignments pepa,
psp_enc_payrolls pep
WHERE pepa.enc_payroll_id = pep.enc_payroll_id
AND pepa.business_group_id = l_business_group_id
AND pepa.set_of_books_id = l_set_of_books_id
AND pepa.business_group_id = pep.business_group_id
AND pepa.set_of_books_id = pep.set_of_books_id
AND pep.payroll_id = p_payroll_id
AND pepa.assignment_id = pa.assignment_id)
AND NOT EXISTS (SELECT 1
FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = pa.assignment_id
AND peca.payroll_id = p_payroll_id)
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = pa.assignment_id
AND pesl.status_code IN ('A', 'N')
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pesl.effective_date > (NVL((SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE paa.assignment_id(+) = pesl.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.business_group_id = pesl.business_group_id
AND ppa.payroll_id = pesl.payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status(+) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date))
AND pesl.payroll_id = p_payroll_id);
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '10-A Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
INSERT INTO psp_enc_changed_assignments
(request_id, payroll_id, assignment_id, change_type, payroll_action_id)
SELECT DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'CR', p_payroll_action_id
FROM per_assignments_f pa
WHERE pa.payroll_id = p_payroll_id
AND pa.assignment_type = 'E'
AND pa.business_group_id = l_business_group_id
AND pa.effective_end_date >= (NVL((SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE paa.assignment_id(+) = pa.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id (+)
and ppa.business_group_id = pa.business_group_id
AND ppa.payroll_id = pa.payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status(+) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date))
AND NOT EXISTS (SELECT pepa.assignment_id
FROM psp_enc_payroll_assignments pepa,
psp_enc_payrolls pep
WHERE pepa.enc_payroll_id = pep.enc_payroll_id
AND pepa.business_group_id = l_business_group_id
AND pepa.set_of_books_id = l_set_of_books_id
AND pepa.business_group_id = pep.business_group_id
AND pepa.set_of_books_id = pep.set_of_books_id
AND pep.payroll_id = p_payroll_id
AND pepa.assignment_id = pa.assignment_id)
AND NOT EXISTS (SELECT 1
FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = pa.assignment_id
AND peca.payroll_id = p_payroll_id)
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = pa.assignment_id
AND pesl.status_code IN ('A', 'N')
AND pesl.effective_date > l_prev_enc_end_date
AND pesl.payroll_id = p_payroll_id);
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '10-B Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
INSERT INTO psp_enc_changed_assignments
(request_id, payroll_id, assignment_id, change_type, payroll_action_id)
SELECT DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'ZZ', p_payroll_action_id
FROM per_assignments_f pa
WHERE pa.payroll_id = p_payroll_id
AND pa.assignment_type = 'E'
AND pa.business_group_id = l_business_group_id
AND pa.effective_end_date >= l_enc_begin_date
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pa.effective_end_date >= (NVL((SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE paa.assignment_id(+) = pa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.business_group_id = pa.business_group_id
AND ppa.payroll_id = pa.payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status(+) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date))
AND EXISTS (SELECT pepa.assignment_id
FROM psp_enc_payroll_assignments pepa,
psp_enc_payrolls pep
WHERE pepa.enc_payroll_id = pep.enc_payroll_id
AND pepa.business_group_id = l_business_group_id
AND pepa.set_of_books_id = l_set_of_books_id
AND pepa.business_group_id = pep.business_group_id
AND pepa.set_of_books_id = pep.set_of_books_id
AND pep.payroll_id = p_payroll_id
AND pepa.assignment_id = pa.assignment_id)
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = pa.assignment_id
AND pesl.status_code = 'A'
AND pesl.payroll_id = p_payroll_id)
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = pa.assignment_id
AND pesl.status_code = 'N'
AND pesl.payroll_id = p_payroll_id);
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '20 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
INSERT INTO psp_enc_changed_assignments
(request_id, payroll_id, assignment_id, change_type, payroll_action_id)
SELECT DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'CR', p_payroll_action_id
FROM psp_enc_payroll_assignments pepa,
psp_enc_payrolls pep,
per_assignments_f pa
WHERE pa.payroll_id = p_payroll_id
AND pepa.business_group_id = l_business_group_id
AND pepa.set_of_books_id = l_set_of_books_id
AND pepa.assignment_id = pa.assignment_id
AND pa.effective_end_date >= l_enc_begin_date
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pa.effective_end_date >= (NVL((SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE paa.assignment_id(+) = pa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.business_group_id = pa.business_group_id
AND ppa.payroll_id = pa.payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status(+) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date))
AND pep.payroll_id = p_payroll_id
AND pep.enc_payroll_id = pepa.enc_payroll_id
AND NOT EXISTS (SELECT 1
FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = pepa.assignment_id
AND peca.payroll_id = p_payroll_id)
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = pa.assignment_id
AND pesl.status_code IN ('A', 'N')
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pesl.effective_date > (NVL((SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE paa.assignment_id(+) = pesl.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.time_period_id = ptp.time_period_id
AND ppa.business_group_id = pesl.business_group_id
AND ppa.payroll_id = pesl.payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status(+) = 'C'),l_enc_begin_date)) );
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '30-A Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
INSERT INTO psp_enc_changed_assignments
(request_id, payroll_id, assignment_id, change_type, payroll_action_id)
SELECT DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'CR', p_payroll_action_id
FROM psp_enc_payroll_assignments pepa,
psp_enc_payrolls pep,
per_assignments_f pa
WHERE pa.payroll_id = p_payroll_id
AND pepa.business_group_id = l_business_group_id
AND pepa.set_of_books_id = l_set_of_books_id
AND pepa.assignment_id = pa.assignment_id
AND pa.effective_end_date >= (NVL((SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE paa.assignment_id(+)= pa.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id (+)
and ppa.business_group_id = pa.business_group_id
AND ppa.payroll_id = pa.payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status(+) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date))
AND pep.payroll_id = p_payroll_id
AND pep.enc_payroll_id = pepa.enc_payroll_id
AND NOT EXISTS (SELECT 1
FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = pepa.assignment_id
AND peca.payroll_id = p_payroll_id)
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = pa.assignment_id
AND pesl.status_code IN ('A', 'N')
AND pesl.effective_date > l_prev_enc_end_date);
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '30-B Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
INSERT INTO psp_enc_changed_assignments
(request_id, payroll_id, assignment_id, change_type, payroll_action_id)
SELECT DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'ZZ', p_payroll_action_id
FROM per_assignments_f pa
WHERE pa.payroll_id = p_payroll_id
AND pa.assignment_type = 'E'
AND pa.business_group_id = l_business_group_id
AND pa.effective_end_date >= l_enc_begin_date
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pa.effective_end_date >= (NVL((SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE paa.assignment_id(+) = pa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.business_group_id = pa.business_group_id
AND ppa.payroll_id = pa.payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status(+) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date))
AND NOT EXISTS (SELECT pepa.assignment_id
FROM psp_enc_payroll_assignments pepa,
psp_enc_payrolls pep
WHERE pepa.enc_payroll_id = pep.enc_payroll_id
AND pepa.business_group_id = l_business_group_id
AND pepa.set_of_books_id = l_set_of_books_id
AND pepa.business_group_id = pep.business_group_id
AND pepa.set_of_books_id = pep.set_of_books_id
AND pep.payroll_id = p_payroll_id
AND pepa.assignment_id = pa.assignment_id)
AND EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = pa.assignment_id
AND pesl.status_code = 'A'
AND pesl.payroll_id = p_payroll_id)
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl
WHERE pesl.assignment_id = pa.assignment_id
AND pesl.status_code = 'N'
AND pesl.payroll_id = p_payroll_id);
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '40 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
INSERT INTO psp_enc_changed_assignments
(request_id, payroll_id, assignment_id, change_type, payroll_action_id)
SELECT DISTINCT l_request_id, p_payroll_id, assignment_id, 'LQ', p_payroll_action_id
FROM psp_enc_summary_lines pesl
WHERE payroll_id = p_payroll_id
AND business_group_id = l_business_group_id
AND status_code = 'A'
AND effective_date <= l_enc_begin_date
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pesl.effective_date <= (NVL((SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE paa.assignment_id(+) = pesl.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.business_group_id = pesl.business_group_id
AND ppa.payroll_id = pesl.payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status(+) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date))
AND NOT EXISTS (SELECT 1
FROM psp_enc_changed_assignments peca
WHERE peca.assignment_id = pesl.assignment_id
AND peca.payroll_id = p_payroll_id)
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl2
WHERE pesl2.assignment_id = pesl.assignment_id
AND pesl2.status_code = 'N'
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pesl2.effective_date <= (NVL((SELECT /*+ use_nl(PTP) */ Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE paa.assignment_id(+) = pesl2.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.business_group_id = pesl2.business_group_id
AND ppa.payroll_id = pesl2.payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status(+) = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id),l_enc_begin_date))
AND pesl2.payroll_id = p_payroll_id);
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '50 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
UPDATE psp_enc_changed_assignments peca
SET payroll_action_id = p_payroll_action_id
WHERE payroll_action_id IS NULL
AND payroll_id = NVL(p_payroll_id, payroll_id)
AND NOT EXISTS (SELECT 1
FROM psp_enc_process_assignments pepa
WHERE pepa.assignment_id = peca.assignment_id
AND pepa.assignment_status NOT IN ('B', 'P')
AND pepa.payroll_id = peca.payroll_id);
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Marked ' || SQL%ROWCOUNT || ' assignments in psp_enc_changed_assignments to be processed by this Create and Update process');
DELETE FROM psp_enc_changed_assignments
WHERE payroll_action_id = p_payroll_action_id
AND payroll_id = p_payroll_id
AND request_id = l_request_id
and assignment_id IN(SELECT assignment_id from PSP_ENC_PAYROLL_ASSIGNMENT_V
WHERE payroll_id = p_payroll_id
AND exclude = 'Y'
MINUS
SELECT ASSIGNMENT_ID FROM psp_enc_changed_asg_history
WHERE payroll_id = p_payroll_id);
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Deleted ' || SQL%ROWCOUNT || ' fresh excluded assignments in psp_enc_changed_assignments not to be processed by this Create and Update process');
INSERT INTO PSP_ENC_CHANGED_ASSIGNMENTS
(request_id, assignment_id, payroll_id, change_type, payroll_action_id, change_date)
SELECT DISTINCT l_request_id,
pesl.assignment_id,
pesl.payroll_id,
'TR',
p_payroll_action_id,
l_termination_date1
FROM psp_enc_summary_lines pesl
WHERE pesl.person_id = l_person_id1
AND pesl.effective_date >= l_termination_date1
AND pesl.award_id IS NOT NULL
AND pesl.status_code = 'A'
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl2
WHERE pesl2.person_id = l_person_id1
AND pesl2.status_code = 'N');
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
INSERT INTO PSP_ENC_CHANGED_ASSIGNMENTS
(request_id, assignment_id, payroll_id, change_type, payroll_action_id, change_date)
SELECT DISTINCT l_request_id,
pesl.assignment_id,
pesl.payroll_id,
'TR',
p_payroll_action_id,
l_termination_date2
FROM psp_enc_summary_lines pesl
WHERE pesl.person_id = l_person_id2
AND pesl.effective_date >= l_termination_date2
AND pesl.award_id IS NOT NULL
AND pesl.status_code = 'A'
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl2
WHERE pesl2.person_id = l_person_id2
AND pesl2.status_code = 'N');
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
INSERT INTO PSP_ENC_CHANGED_ASSIGNMENTS
(request_id, assignment_id, payroll_id, change_type, payroll_action_id, change_date)
SELECT DISTINCT l_request_id,
pesl.assignment_id,
pesl.payroll_id,
'TR',
p_payroll_action_id,
l_termination_date3
FROM psp_enc_summary_lines pesl
WHERE pesl.person_id = l_person_id3
AND pesl.effective_date >= l_termination_date3
AND pesl.award_id IS NOT NULL
AND pesl.status_code = 'A'
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl2
WHERE pesl2.person_id = l_person_id3
AND pesl2.status_code = 'N');
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
INSERT INTO PSP_ENC_CHANGED_ASSIGNMENTS
(request_id, assignment_id, payroll_id, change_type, payroll_action_id, change_date)
SELECT DISTINCT l_request_id,
pesl.assignment_id,
pesl.payroll_id,
'TR',
p_payroll_action_id,
l_termination_date4
FROM psp_enc_summary_lines pesl
WHERE pesl.person_id = l_person_id4
AND pesl.effective_date >= l_termination_date4
AND pesl.award_id IS NOT NULL
AND pesl.status_code = 'A'
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl2
WHERE pesl2.person_id = l_person_id4
AND pesl2.status_code = 'N');
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
INSERT INTO PSP_ENC_CHANGED_ASSIGNMENTS
(request_id, assignment_id, payroll_id, change_type, payroll_action_id, change_date)
SELECT DISTINCT l_request_id,
pesl.assignment_id,
pesl.payroll_id,
'TR',
p_payroll_action_id,
l_termination_date5
FROM psp_enc_summary_lines pesl
WHERE pesl.person_id = l_person_id5
AND pesl.effective_date >= l_termination_date5
AND pesl.award_id IS NOT NULL
AND pesl.status_code = 'A'
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines pesl2
WHERE pesl2.person_id = l_person_id5
AND pesl2.status_code = 'N');
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
SELECT DISTINCT pepa.assignment_id
FROM psp_enc_process_assignments pepa,
per_all_assignments_f paf
WHERE pepa.payroll_action_id = p_payroll_action_id
AND paf.person_id = p_person_id
AND paf.assignment_id = pepa.assignment_id
AND ( p_assignment_id IS NULL
OR pepa.assignment_id = p_assignment_id);
SELECT superceded_line_id
FROM psp_enc_summary_lines pesl
WHERE pesl.payroll_action_id = p_payroll_action_id
AND pesl.superceded_line_id IS NOT NULL
AND assignment_id = p_assignment_id;
UPDATE psp_enc_lines_history
SET change_flag = 'N'
WHERE enc_summary_line_id = t_superceded_lines.enc_summary_line_id(recno);
DELETE psp_enc_summary_lines
WHERE payroll_action_id = p_payroll_action_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_summary_lines');
DELETE psp_enc_lines
WHERE payroll_action_id = p_payroll_action_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_lines');
DELETE psp_enc_controls
WHERE payroll_action_id = p_payroll_action_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_controls');
DELETE psp_report_errors
WHERE payroll_action_id = p_payroll_action_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_report_errors');
UPDATE psp_enc_processes
SET process_status = 'B',
process_phase = 'rollback'
WHERE payroll_action_id = p_payroll_action_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process status to ''Rollback'' in psp_enc_processes');
UPDATE psp_enc_process_assignments
SET assignment_status = 'B'
WHERE payroll_action_id = p_payroll_action_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process status to ''Rollback'' in psp_enc_process_assignments');
UPDATE psp_enc_changed_assignments
SET payroll_action_id = NULL
WHERE payroll_action_id = p_payroll_action_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated payroll_action_id in psp_enc_changed_assignments');
INSERT INTO psp_enc_changed_assignments
(request_id, assignment_id, payroll_id, change_type,
processed_flag, reference_id, action_type, change_date)
SELECT l_request_id, pecah.assignment_id, pecah.payroll_id, pecah.change_type,
NULL, NVL(pecah.reference_id, 0), pecah.action_type, change_date
FROM psp_enc_changed_asg_history pecah
WHERE payroll_action_id = p_payroll_action_id
AND action_type NOT IN ('CR', 'LQ', 'TR');
DELETE psp_enc_changed_asg_history
WHERE payroll_action_id = p_payroll_action_id;
l_assignments_tmp.DELETE;
l_assignments_tmp.DELETE;
l_assignments_tmp.DELETE;
l_assignments_tmp.DELETE;
UPDATE psp_enc_lines_history
SET change_flag = 'N'
WHERE enc_summary_line_id = t_superceded_lines.enc_summary_line_id(recno);
DELETE psp_enc_summary_lines
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = l_assignments(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_summary_lines');
DELETE psp_enc_lines
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = l_assignments(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_lines');
DELETE psp_enc_controls pec
WHERE payroll_action_id = p_payroll_action_id
AND NOT EXISTS (SELECT 1
FROM psp_enc_lines pel
WHERE payroll_action_id = p_payroll_action_id
AND pel.enc_control_id = pec.enc_control_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_controls');
DELETE psp_report_errors
WHERE payroll_action_id = p_payroll_action_id
AND source_id = l_assignments(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_report_errors');
UPDATE psp_enc_process_assignments
SET assignment_status = 'B'
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = l_assignments(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated assignment status to ''Rollback'' in psp_enc_process_assignments');
UPDATE psp_enc_changed_assignments
SET payroll_action_id = NULL
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = l_assignments(recno);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated payroll_action_id in psp_enc_changed_assignments');
INSERT INTO psp_enc_changed_assignments
(request_id, assignment_id, payroll_id, change_type,
processed_flag, reference_id, action_type, change_date)
SELECT l_request_id, pecah.assignment_id, pecah.payroll_id, pecah.change_type,
NULL, NVL(pecah.reference_id, 0), pecah.action_type, change_date
FROM psp_enc_changed_asg_history pecah
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = l_assignments(recno)
AND action_type NOT IN ('CR', 'LQ', 'TR');
DELETE psp_enc_changed_asg_history
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = l_assignments(recno);
UPDATE psp_enc_processes
SET process_status = 'B',
process_phase = 'rollback'
WHERE payroll_action_id = p_payroll_action_id
AND NOT EXISTS (SELECT 1
FROM psp_enc_summary_lines
WHERE payroll_action_id = p_payroll_action_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process status to ''Rollback'' in psp_enc_processes');
SELECT peta.element_account_id,
peta.gl_code_combination_id,
peta.project_id,
peta.task_id,
peta.award_id,
peta.expenditure_type,
peta.expenditure_organization_id,
peta.start_date_active,
peta.end_date_active,
peta.poeta_start_date,
peta.poeta_end_date,
peta.percent,
DECODE(g_dff_grouping_option, 'Y', peta.attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', peta.attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', peta.attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', peta.attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', peta.attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', peta.attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', peta.attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', peta.attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', peta.attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', peta.attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', peta.attribute10, NULL),
DECODE(peta.expenditure_type, NULL, 'N', 'E') acct_type
FROM psp_element_type_accounts peta
WHERE peta.element_type_id = p_element_type_id
AND peta.business_group_id = p_business_group_id
AND peta.set_of_books_id = p_set_of_books_id
AND ( peta.gl_code_combination_id IS NOT NULL
OR peta.award_id IS NOT NULL)
AND peta.end_date_active >= l_min_start_date
AND peta.start_date_active <= g_enc_org_end_date
ORDER BY peta.start_date_active, peta.end_date_active;
SELECT psl.schedule_line_id,
psl.gl_code_combination_id,
psl.project_id,
psl.task_id,
psl.award_id,
psl.expenditure_type,
psl.expenditure_organization_id,
psl.schedule_begin_date,
psl.schedule_end_date,
psl.poeta_start_date,
psl.poeta_end_date,
psl.schedule_percent,
DECODE(g_dff_grouping_option, 'Y', psl.attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute10, NULL),
DECODE(psl.expenditure_type, NULL, 'N', 'E') acct_type
FROM psp_schedule_hierarchy psh,
psp_schedule_lines psl
WHERE psh.assignment_id = p_assignment_id
AND psh.element_type_id = p_element_type_id
AND psh.business_group_id = p_business_group_id
AND psh.set_of_books_id = p_set_of_books_id
AND psh.schedule_hierarchy_id = psl.schedule_hierarchy_id
AND ( psl.gl_code_combination_id IS NOT NULL
OR psl.award_id IS NOT NULL )
AND psl.schedule_begin_date <= g_enc_org_end_date
AND psl.schedule_end_date >= l_min_start_date
AND psl.default_flag IS NULL
ORDER BY psl.schedule_begin_date, psl.schedule_end_date;
SELECT psl.schedule_line_id,
psl.gl_code_combination_id,
psl.project_id,
psl.task_id,
psl.award_id,
psl.expenditure_type,
psl.expenditure_organization_id,
psl.schedule_begin_date,
psl.schedule_end_date,
psl.poeta_start_date,
psl.poeta_end_date,
psl.schedule_percent,
DECODE(g_dff_grouping_option, 'Y', psl.attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute10, NULL),
DECODE(psl.expenditure_type, NULL, 'N', 'E') acct_type
FROM psp_element_types pet,
psp_group_element_list pgel,
psp_schedule_hierarchy psh,
psp_schedule_lines psl
WHERE pet.element_type_id = p_element_type_id
AND pet.business_group_id = p_business_group_id
AND pet.set_of_books_id = p_set_of_books_id
AND pet.start_date_active <= g_enc_org_end_date
AND pet.end_date_active >= l_min_start_date
AND pet.element_type_id = pgel.element_type_id
AND psh.business_group_id = p_business_group_id
AND psh.set_of_books_id = p_set_of_books_id
AND pgel.element_group_id = psh.element_group_id
AND psh.assignment_id = p_assignment_id
AND psh.schedule_hierarchy_id = psl.schedule_hierarchy_id
AND ( psl.gl_code_combination_id IS NOT NULL
OR psl.award_id IS NOT NULL)
AND psl.schedule_begin_date <= pet.end_date_active
AND psl.schedule_end_date >= pet.start_date_active
AND psl.default_flag IS NULL
ORDER BY psl.schedule_begin_date, psl.schedule_end_date;
SELECT psl.schedule_line_id,
psl.gl_code_combination_id,
psl.project_id,
psl.task_id,
psl.award_id,
psl.expenditure_type,
psl.expenditure_organization_id,
psl.schedule_begin_date,
psl.schedule_end_date,
psl.poeta_start_date,
psl.poeta_end_date,
psl.schedule_percent,
DECODE(g_dff_grouping_option, 'Y', psl.attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', psl.attribute10, NULL),
DECODE(psl.expenditure_type, NULL, 'N', 'E') acct_type
FROM psp_schedule_hierarchy psh,
psp_schedule_lines psl
WHERE psh.scheduling_types_code = 'A'
AND psh.element_group_id IS NULL
AND psh.element_type_id IS NULL
AND psh.assignment_id = p_assignment_id
AND psh.business_group_id = p_business_group_id
AND psh.set_of_books_id = p_set_of_books_id
AND psh.schedule_hierarchy_id = psl.schedule_hierarchy_id
AND ( psl.gl_code_combination_id IS NOT NULL
OR psl.award_id IS NOT NULL)
AND psl.schedule_begin_date <= g_enc_org_end_date
AND psl.schedule_end_date >= l_min_start_date
AND psl.default_flag IS NULL
ORDER BY psl.schedule_begin_date, psl.schedule_end_date;
SELECT organization_id,
effective_start_date,
NVL(LEAD(effective_start_date - 1) OVER (ORDER BY effective_end_date), LEAST(l_max_end_date, effective_end_date))
FROM per_assignments_f paf
WHERE assignment_id = p_assignment_id
AND payroll_id = p_payroll_id
AND effective_start_date <= LEAST(l_max_end_date, g_enc_org_end_date)
ANd effective_end_date >= l_min_start_date
AND effective_start_date = (SELECT MIN(paf2.effective_start_date)
FROM per_assignments_f paf2
WHERE paf2.assignment_id = p_assignment_id
AND paf2.payroll_id = paf.payroll_id
AND paf2.organization_id = paf.organization_id
AND paf2.effective_start_date >= paf.effective_start_date);
SELECT pdls.org_schedule_id,
pdls.gl_code_combination_id,
pdls.project_id,
pdls.task_id,
pdls.award_id,
pdls.expenditure_type,
pdls.expenditure_organization_id,
GREATEST(pdls.schedule_begin_date, p_org_start_date),
LEAST(pdls.schedule_end_date, p_org_end_date),
pdls.poeta_start_date,
pdls.poeta_end_date,
pdls.schedule_percent,
DECODE(g_dff_grouping_option, 'Y', pdls.attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', pdls.attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', pdls.attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', pdls.attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', pdls.attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', pdls.attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', pdls.attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', pdls.attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', pdls.attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', pdls.attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', pdls.attribute10, NULL),
DECODE(pdls.expenditure_type, NULL, 'N', 'E') acct_type
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.organization_id = p_organization_id
AND ( pdls.gl_code_combination_id IS NOT NULL
OR pdls.award_id IS NOT NULL)
AND pdls.schedule_begin_date <= p_org_end_date
AND pdls.schedule_end_date >= p_org_start_date
ORDER BY GREATEST(pdls.schedule_begin_date, p_org_start_date), LEAST(pdls.schedule_end_date, p_org_end_date);
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.task_id,
poa.award_id,
poa.expenditure_type,
poa.expenditure_organization_id,
GREATEST(poa.start_date_active, p_org_start_date),
LEAST(poa.end_date_active, p_org_end_date),
poa.poeta_start_date,
poa.poeta_end_date,
100 percent,
DECODE(g_dff_grouping_option, 'Y', poa.attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute10, NULL),
DECODE(poa.expenditure_type, NULL, 'N', 'E') acct_type
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 = 'D'
AND poa.start_date_active <= p_org_end_date
AND poa.end_date_active >= p_org_start_date
AND ( poa.gl_code_combination_id IS NOT NULL
OR poa.award_id IS NOT NULL)
ORDER BY GREATEST(poa.start_date_active, p_org_start_date), LEAST(poa.end_date_active, p_org_end_date);
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.task_id,
poa.award_id,
poa.expenditure_type,
poa.expenditure_organization_id,
GREATEST(poa.start_date_active, p_org_start_date),
LEAST(poa.end_date_active, p_org_end_date),
poa.poeta_start_date,
poa.poeta_end_date,
100 percent,
DECODE(g_dff_grouping_option, 'Y', poa.attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute10, NULL),
DECODE(poa.expenditure_type, NULL, 'N', 'E') acct_type
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.start_date_active <= p_org_end_date
AND poa.end_date_active >= p_org_start_date
AND ( poa.gl_code_combination_id IS NOT NULL
OR poa.award_id IS NOT NULL)
ORDER BY GREATEST(poa.start_date_active, p_org_start_date), LEAST(poa.end_date_active, p_org_end_date);
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.task_id,
poa.award_id,
poa.expenditure_type,
poa.expenditure_organization_id,
poa.start_date_active,
poa.end_date_active,
poa.poeta_start_date,
poa.poeta_end_date,
100 percent,
DECODE(g_dff_grouping_option, 'Y', poa.attribute_category, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute1, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute2, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute3, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute4, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute5, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute6, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute7, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute8, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute9, NULL),
DECODE(g_dff_grouping_option, 'Y', poa.attribute10, NULL),
DECODE(poa.expenditure_type, NULL, 'N', 'E') acct_type
FROM psp_organization_accounts poa
WHERE poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND poa.account_type_code = 'G'
AND poa.start_date_active <= g_enc_org_end_date
AND poa.end_date_active >= l_min_start_date
AND ( poa.gl_code_combination_id IS NOT NULL
OR poa.award_id IS NOT NULL)
ORDER BY poa.start_date_active, poa.end_date_active;
r_gsa.line_account_id.DELETE;
r_gsa.gl_code_combination_id.DELETE;
r_gsa.project_id.DELETE;
r_gsa.task_id.DELETE;
r_gsa.award_id.DELETE;
r_gsa.expenditure_type.DELETE;
r_gsa.expenditure_organization_id.DELETE;
r_gsa.start_date_active.DELETE;
r_gsa.end_date_active.DELETE;
r_gsa.poeta_start_date.DELETE;
r_gsa.poeta_end_date.DELETE;
r_gsa.percent.DELETE;
r_gsa.attribute_category.DELETE;
r_gsa.attribute1.DELETE;
r_gsa.attribute2.DELETE;
r_gsa.attribute3.DELETE;
r_gsa.attribute4.DELETE;
r_gsa.attribute5.DELETE;
r_gsa.attribute6.DELETE;
r_gsa.attribute7.DELETE;
r_gsa.attribute8.DELETE;
r_gsa.attribute9.DELETE;
r_gsa.attribute10.DELETE;
r_gsa.acct_type.DELETE;
r_gsa.line_account_id.DELETE;
r_gsa.gl_code_combination_id.DELETE;
r_gsa.project_id.DELETE;
r_gsa.task_id.DELETE;
r_gsa.award_id.DELETE;
r_gsa.expenditure_type.DELETE;
r_gsa.expenditure_organization_id.DELETE;
r_gsa.start_date_active.DELETE;
r_gsa.end_date_active.DELETE;
r_gsa.poeta_start_date.DELETE;
r_gsa.poeta_end_date.DELETE;
r_gsa.percent.DELETE;
r_gsa.attribute_category.DELETE;
r_gsa.attribute1.DELETE;
r_gsa.attribute2.DELETE;
r_gsa.attribute3.DELETE;
r_gsa.attribute4.DELETE;
r_gsa.attribute5.DELETE;
r_gsa.attribute6.DELETE;
r_gsa.attribute7.DELETE;
r_gsa.attribute8.DELETE;
r_gsa.attribute9.DELETE;
r_gsa.attribute10.DELETE;
r_gsa.acct_type.DELETE;
r_gsa.line_account_id.DELETE;
r_gsa.gl_code_combination_id.DELETE;
r_gsa.project_id.DELETE;
r_gsa.task_id.DELETE;
r_gsa.award_id.DELETE;
r_gsa.expenditure_type.DELETE;
r_gsa.expenditure_organization_id.DELETE;
r_gsa.start_date_active.DELETE;
r_gsa.end_date_active.DELETE;
r_gsa.poeta_start_date.DELETE;
r_gsa.poeta_end_date.DELETE;
r_gsa.percent.DELETE;
r_gsa.attribute_category.DELETE;
r_gsa.attribute1.DELETE;
r_gsa.attribute2.DELETE;
r_gsa.attribute3.DELETE;
r_gsa.attribute4.DELETE;
r_gsa.attribute5.DELETE;
r_gsa.attribute6.DELETE;
r_gsa.attribute7.DELETE;
r_gsa.attribute8.DELETE;
r_gsa.attribute9.DELETE;
r_gsa.attribute10.DELETE;
r_gsa.acct_type.DELETE;
r_sa_tmp.line_account_id.DELETE;
r_sa_tmp.gl_code_combination_id.DELETE;
r_sa_tmp.project_id.DELETE;
r_sa_tmp.task_id.DELETE;
r_sa_tmp.award_id.DELETE;
r_sa_tmp.expenditure_type.DELETE;
r_sa_tmp.expenditure_organization_id.DELETE;
r_sa_tmp.start_date_active.DELETE;
r_sa_tmp.end_date_active.DELETE;
r_sa_tmp.poeta_start_date.DELETE;
r_sa_tmp.poeta_end_date.DELETE;
r_sa_tmp.percent.DELETE;
r_sa_tmp.attribute_category.DELETE;
r_sa_tmp.attribute1.DELETE;
r_sa_tmp.attribute2.DELETE;
r_sa_tmp.attribute3.DELETE;
r_sa_tmp.attribute4.DELETE;
r_sa_tmp.attribute5.DELETE;
r_sa_tmp.attribute6.DELETE;
r_sa_tmp.attribute7.DELETE;
r_sa_tmp.attribute8.DELETE;
r_sa_tmp.attribute9.DELETE;
r_sa_tmp.attribute10.DELETE;
r_sa_tmp.acct_type.DELETE;
r_gsa.line_account_id.DELETE;
r_gsa.gl_code_combination_id.DELETE;
r_gsa.project_id.DELETE;
r_gsa.task_id.DELETE;
r_gsa.award_id.DELETE;
r_gsa.expenditure_type.DELETE;
r_gsa.expenditure_organization_id.DELETE;
r_gsa.start_date_active.DELETE;
r_gsa.end_date_active.DELETE;
r_gsa.poeta_start_date.DELETE;
r_gsa.poeta_end_date.DELETE;
r_gsa.percent.DELETE;
r_gsa.attribute_category.DELETE;
r_gsa.attribute1.DELETE;
r_gsa.attribute2.DELETE;
r_gsa.attribute3.DELETE;
r_gsa.attribute4.DELETE;
r_gsa.attribute5.DELETE;
r_gsa.attribute6.DELETE;
r_gsa.attribute7.DELETE;
r_gsa.attribute8.DELETE;
r_gsa.attribute9.DELETE;
r_gsa.attribute10.DELETE;
r_gsa.acct_type.DELETE;
PROCEDURE delete_previous_error_log(p_assignment_id IN NUMBER,
p_payroll_id IN NUMBER,
p_payroll_action_id IN NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE psp_report_errors
WHERE source_id = p_assignment_id
AND value1 = p_payroll_id
AND payroll_action_id = p_payroll_action_id;
END delete_previous_error_log;
PROCEDURE update_hierarchy_dates (p_assignment_id IN NUMBER,
p_payroll_id IN NUMBER,
p_payroll_action_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2) IS
CURSOR hierarchy_dates_cur 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
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = p_assignment_id
AND payroll_id = p_payroll_id
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10;
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
WHERE assignment_id = p_assignment_id
AND payroll_id = p_payroll_id
AND change_flag = 'N'
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10;
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Entering UPDATE_HIERARCHY_DATES');
UPDATE psp_enc_lines
SET hierarchy_start_date = t_enc_nlines.enc_start_date(recno),
hierarchy_end_date = t_enc_nlines.enc_end_date(recno)
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = p_assignment_id
AND payroll_id = p_payroll_id
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 t_enc_nlines.gl_ccid(recno) IS NOT NULL
AND gl_code_combination_id = t_enc_nlines.gl_ccid(recno)
AND enc_start_date <= t_enc_nlines.enc_end_date(recno)
AND enc_end_date >= t_enc_nlines.enc_start_date(recno);
UPDATE psp_enc_lines
SET hierarchy_start_date = t_enc_nlines.enc_start_date(recno),
hierarchy_end_date = t_enc_nlines.enc_end_date(recno)
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = p_assignment_id
AND payroll_id = p_payroll_id
AND enc_element_type_id = t_enc_nlines.element_type_id(recno)
AND hierarchy_code = t_enc_nlines.hierarchy_code(recno)
AND award_id IS NOT NULL
AND t_enc_nlines.award_id(recno) IS NOT NULL
AND 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);
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;
t_enc_nlines.element_type_id.DELETE;
t_enc_nlines.hierarchy_code.DELETE;
t_enc_nlines.gl_ccid.DELETE;
t_enc_nlines.project_id.DELETE;
t_enc_nlines.task_id.DELETE;
t_enc_nlines.award_id.DELETE;
t_enc_nlines.exp_org_id.DELETE;
t_enc_nlines.exp_type.DELETE;
t_enc_nlines.enc_start_date.DELETE;
t_enc_nlines.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 = p_assignment_id
AND payroll_id = p_payroll_id
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 t_enc_nlines.gl_ccid(recno) IS NOT NULL
AND gl_code_combination_id = t_enc_nlines.gl_ccid(recno)
AND enc_start_date <= t_enc_nlines.enc_end_date(recno)
AND enc_end_date >= t_enc_nlines.enc_start_date(recno);
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 = p_assignment_id
AND payroll_id = p_payroll_id
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 award_id IS NOT NULL
AND t_enc_nlines.award_id(recno) IS NOT NULL
AND 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);
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
g_error_message := 'UPDATE_HIERARCHY_DATES: ' || SQLERRM;
g_error_api_path := SUBSTR(' UPDATE_HIERARCHY_DATES:'||g_error_api_path,1,230);
fnd_msg_pub.add_exc_msg('PSP_ENC_CREATE_LINES', ' UPDATE_HIERARCHY_DATES');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
END update_hierarchy_dates;
r_sa.line_account_id.DELETE;
r_sa.gl_code_combination_id.DELETE;
r_sa.project_id.DELETE;
r_sa.task_id.DELETE;
r_sa.award_id.DELETE;
r_sa.expenditure_type.DELETE;
r_sa.expenditure_organization_id.DELETE;
r_sa.start_date_active.DELETE;
r_sa.end_date_active.DELETE;
r_sa.poeta_start_date.DELETE;
r_sa.poeta_end_date.DELETE;
r_sa.percent.DELETE;
r_sa.attribute_category.DELETE;
r_sa.attribute1.DELETE;
r_sa.attribute2.DELETE;
r_sa.attribute3.DELETE;
r_sa.attribute4.DELETE;
r_sa.attribute5.DELETE;
r_sa.attribute6.DELETE;
r_sa.attribute7.DELETE;
r_sa.attribute8.DELETE;
r_sa.attribute9.DELETE;
r_sa.attribute10.DELETE;
r_sa.acct_type.DELETE;
r_da.line_account_id.DELETE;
r_da.gl_code_combination_id.DELETE;
r_da.project_id.DELETE;
r_da.task_id.DELETE;
r_da.award_id.DELETE;
r_da.expenditure_type.DELETE;
r_da.expenditure_organization_id.DELETE;
r_da.start_date_active.DELETE;
r_da.end_date_active.DELETE;
r_da.poeta_start_date.DELETE;
r_da.poeta_end_date.DELETE;
r_da.percent.DELETE;
r_da.attribute_category.DELETE;
r_da.attribute1.DELETE;
r_da.attribute2.DELETE;
r_da.attribute3.DELETE;
r_da.attribute4.DELETE;
r_da.attribute5.DELETE;
r_da.attribute6.DELETE;
r_da.attribute7.DELETE;
r_da.attribute8.DELETE;
r_da.attribute9.DELETE;
r_da.attribute10.DELETE;
r_da.acct_type.DELETE;
r_odls.line_account_id.DELETE;
r_odls.gl_code_combination_id.DELETE;
r_odls.project_id.DELETE;
r_odls.task_id.DELETE;
r_odls.award_id.DELETE;
r_odls.expenditure_type.DELETE;
r_odls.expenditure_organization_id.DELETE;
r_odls.start_date_active.DELETE;
r_odls.end_date_active.DELETE;
r_odls.poeta_start_date.DELETE;
r_odls.poeta_end_date.DELETE;
r_odls.percent.DELETE;
r_odls.attribute_category.DELETE;
r_odls.attribute1.DELETE;
r_odls.attribute2.DELETE;
r_odls.attribute3.DELETE;
r_odls.attribute4.DELETE;
r_odls.attribute5.DELETE;
r_odls.attribute6.DELETE;
r_odls.attribute7.DELETE;
r_odls.attribute8.DELETE;
r_odls.attribute9.DELETE;
r_odls.attribute10.DELETE;
r_odls.acct_type.DELETE;
r_asg.line_account_id.DELETE;
r_asg.gl_code_combination_id.DELETE;
r_asg.project_id.DELETE;
r_asg.task_id.DELETE;
r_asg.award_id.DELETE;
r_asg.expenditure_type.DELETE;
r_asg.expenditure_organization_id.DELETE;
r_asg.start_date_active.DELETE;
r_asg.end_date_active.DELETE;
r_asg.poeta_start_date.DELETE;
r_asg.poeta_end_date.DELETE;
r_asg.percent.DELETE;
r_asg.attribute_category.DELETE;
r_asg.attribute1.DELETE;
r_asg.attribute2.DELETE;
r_asg.attribute3.DELETE;
r_asg.attribute4.DELETE;
r_asg.attribute5.DELETE;
r_asg.attribute6.DELETE;
r_asg.attribute7.DELETE;
r_asg.attribute8.DELETE;
r_asg.attribute9.DELETE;
r_asg.attribute10.DELETE;
r_asg.acct_type.DELETE;
r_ec.line_account_id.DELETE;
r_ec.gl_code_combination_id.DELETE;
r_ec.project_id.DELETE;
r_ec.task_id.DELETE;
r_ec.award_id.DELETE;
r_ec.expenditure_type.DELETE;
r_ec.expenditure_organization_id.DELETE;
r_ec.start_date_active.DELETE;
r_ec.end_date_active.DELETE;
r_ec.poeta_start_date.DELETE;
r_ec.poeta_end_date.DELETE;
r_ec.percent.DELETE;
r_ec.attribute_category.DELETE;
r_ec.attribute1.DELETE;
r_ec.attribute2.DELETE;
r_ec.attribute3.DELETE;
r_ec.attribute4.DELETE;
r_ec.attribute5.DELETE;
r_ec.attribute6.DELETE;
r_ec.attribute7.DELETE;
r_ec.attribute8.DELETE;
r_ec.attribute9.DELETE;
r_ec.attribute10.DELETE;
r_ec.acct_type.DELETE;
r_et.line_account_id.DELETE;
r_et.gl_code_combination_id.DELETE;
r_et.project_id.DELETE;
r_et.task_id.DELETE;
r_et.award_id.DELETE;
r_et.expenditure_type.DELETE;
r_et.expenditure_organization_id.DELETE;
r_et.start_date_active.DELETE;
r_et.end_date_active.DELETE;
r_et.poeta_start_date.DELETE;
r_et.poeta_end_date.DELETE;
r_et.percent.DELETE;
r_et.attribute_category.DELETE;
r_et.attribute1.DELETE;
r_et.attribute2.DELETE;
r_et.attribute3.DELETE;
r_et.attribute4.DELETE;
r_et.attribute5.DELETE;
r_et.attribute6.DELETE;
r_et.attribute7.DELETE;
r_et.attribute8.DELETE;
r_et.attribute9.DELETE;
r_et.attribute10.DELETE;
r_et.acct_type.DELETE;
r_gee.line_account_id.DELETE;
r_gee.gl_code_combination_id.DELETE;
r_gee.project_id.DELETE;
r_gee.task_id.DELETE;
r_gee.award_id.DELETE;
r_gee.expenditure_type.DELETE;
r_gee.expenditure_organization_id.DELETE;
r_gee.start_date_active.DELETE;
r_gee.end_date_active.DELETE;
r_gee.poeta_start_date.DELETE;
r_gee.poeta_end_date.DELETE;
r_gee.percent.DELETE;
r_gee.attribute_category.DELETE;
r_gee.attribute1.DELETE;
r_gee.attribute2.DELETE;
r_gee.attribute3.DELETE;
r_gee.attribute4.DELETE;
r_gee.attribute5.DELETE;
r_gee.attribute6.DELETE;
r_gee.attribute7.DELETE;
r_gee.attribute8.DELETE;
r_gee.attribute9.DELETE;
r_gee.attribute10.DELETE;
r_gee.acct_type.DELETE;