[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);
process and this is used in the insert statement of PSP_ENC_CHANGED_ASSIGNMENTS.
For this we have created a temp table PSP_ASG_END_DATES in order
to hold assignment information until the CUEL process completes.
Before this process was introduced the cursor query of
ORACLE_PAYROLL_ASG_END_DATE (ORACLE PAYROLL CURSOR) is used in all the
insert statements and the query executes for every assignment multiple
times which is the actual cause for this performance issue. So instead
of executing the query multiple times for the same assignment we have
introduced the CACHING MECHANISM.
In this mechanism the query executes only once for an assignment and stores
the assignment information in the temp table and once again if the same
assignment is used for querying the data then the process picks up the
data from the temp table.
Similarly for OTHER PAYROLL we have introduced the same mechanism
with the help of NON_ORACLE_ASG_END_DATE (OTHER PAYROLL CURSOR).
And the variable g_payroll_mode helps to choose the cursors respectively
basing on the type of payroll which they have used.
2)
GET_PAYROLL_PAY_END_DATE function is introduced to avoid using the
same query of the cursor PAYROLL_PAY_END_DATE_CUR multiple times.
For this we have used a normal variable.
*************************************************************************************** */
function get_asg_pay_end_date(p_payroll_mode IN VARCHAR,
p_payroll_action_id IN NUMBER,
p_assignmnet_id IN NUMBER,
p_business_group_id IN NUMBER,
p_payroll_id IN NUMBER
) return date IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT /*+ use_nl(PTP)
INDEX(ptp PER_TIME_PERIODS_N50)
INDEX(paa PAY_ASSIGNMENT_ACTIONS_N4)*/
Max(ptp.end_date)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE paa.assignment_id = p_assignmnet_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.business_group_id = p_business_group_id
AND ppa.payroll_id = p_payroll_id
AND ppa.action_type IN ( 'R','Q')
AND paa.action_status = 'C'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = ppa.payroll_id;
SELECT /*+ use_nl(PTP)
INDEX(ptp PER_TIME_PERIODS_N50)
INDEX(paa PAY_ASSIGNMENT_ACTIONS_N4)*/
max (ptp.end_date)
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
, per_time_periods ptp
WHERE paa.assignment_id (+) = p_assignmnet_id
AND ppa.payroll_action_id = paa.payroll_action_id (+)
AND ppa.business_group_id = p_business_group_id
AND ppa.payroll_id = p_payroll_id
AND ppa.action_type IN ('R', 'Q')
AND paa.action_status (+) = 'C'
AND ppa.date_earned
BETWEEN ptp.start_date
AND ptp.end_date
AND ptp.payroll_id = ppa.payroll_id;
select assignment_id,asg_end_date
from psp_asg_end_dates
where assignment_id = p_assignmnet_id
and payroll_id = p_payroll_id
and bg_grp_id = p_business_group_id
and payroll_action_id = p_payroll_action_id;
-- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' fnd ::: If --- > assignment_id details is inserted in the table '||p_assignmnet_id);
-- hr_utility.trace(' hr:::If --- > assignment_id details is inserted in the table '||p_assignmnet_id);
insert into psp_asg_end_dates (PAYROLL_ACTION_ID ,ASSIGNMENT_ID ,PAYROLL_ID ,BG_GRP_ID ,ASG_END_DATE ) --jnerella 26/07/2012
values(p_payroll_action_id,p_assignmnet_id,p_payroll_id,p_business_group_id,l_asgn_end_date);
-- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' fnd:::If table --- > insert '||sql%rowcount);
-- hr_utility.trace(' hr:::If table --- > insert '||sql%rowcount);
SELECT /*+ INDEX(ptp PER_TIME_PERIODS_N50)*/
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.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 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
ORDER BY element_start_date,element_end_date; -- Added for bug 12724506
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
ORDER BY start_date,end_date; -- Added for bug 12724506
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_expend_v -- Bug : 16391366 (20/03/2013)
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 peg.element_group_id = pgel.element_group_id -- Added for Bug 10113387
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 assignment_status IN ('S','L')
AND payroll_id = l_payroll_id
)
-- Added the above condition for the bug 13036705
/*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 /*+ INDEX(ppa PAY_PAYROLL_ACTIONS_N51)
INDEX(ptp PER_TIME_PERIODS_N50) */
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
-- AND ppa.time_period_id = ptp.time_period_id -- Commented for the Bug 12647364
;
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_expend_v -- Bug : 16391366 (20/03/2013)
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;
select count(*)
into l_asg_sl_count
from psp_enc_process_assignments
where assignment_id = t_assignments.asg_array(k)
and assignment_status in ('S','L');
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((get_payroll_pay_end_date(l_payroll_id)
-- and ptp.time_period_id = ppa.time_period_id -- Added for 11661463 -- Commented for the Bug 12647364
),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((get_payroll_pay_end_date(l_payroll_id)
-- and ptp.time_period_id = ppa.time_period_id -- Added for 11661463 -- Commented for the Bug 12647364
),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');
-- 13566809 The delete statement deletes all the assignment information
-- from temp table before the CUEL process completes.
DELETE FROM psp_asg_end_dates
WHERE payroll_action_id = p_payroll_action_id;
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');
SELECT /*+INDEX(peca PSP_ENC_CHANGED_ASSIGNMENTS_N3)
*/
peca.assignment_id
FROM psp_enc_changed_assignments peca
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 /*+ ORDERED
INDEX(pep PSP_ENC_PAYROLLS_N1)
INDEX(pepa PSP_ENC_PAYROLL_ASSIGNMENTS_N1)
*/
pepa.assignment_id
FROM psp_enc_payrolls pep
,psp_enc_payroll_assignments pepa
WHERE pep.payroll_id = p_payroll_id
AND pep.inc_exc_flag = 'Y'
AND pep.enc_payroll_id = pepa.enc_payroll_id
AND pep.business_group_id = p_business_group_id
UNION
SELECT /*+ORDERED
NO_USE_MERGE(paaf) */
DISTINCT
paaf.assignment_id
FROM psp_enc_payrolls pep
,per_all_assignments_f paaf
WHERE pep.inc_exc_flag = 'N'
AND pep.payroll_id = paaf.payroll_id
AND pep.payroll_id = p_payroll_id
AND pep.business_group_id = p_business_group_id
AND paaf.assignment_type = 'E'
AND NOT EXISTS
(
SELECT pepa.assignment_id
FROM psp_enc_payroll_assignments pepa
WHERE pepa.business_group_id = p_business_group_id
AND pepa.assignment_id = paaf.assignment_id
)
);
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
and default_org_flag='Y';
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((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl.assignment_id,pesl.business_group_id,pesl.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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 -- Commenting this in the fix 12769510 as it is found to be commented in 11i code
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pa.effective_end_date >= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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 /*+
index(Pepa PSP_ENC_PAYROLL_ASSIGNMENTS_N1)
index(PA PER_ASSIGNMENTS_F_PK)
INDEX(pep PSP_ENC_PAYROLLS_N1) */
DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'CR', p_payroll_action_id
FROM psp_enc_payrolls pep,
psp_enc_payroll_assignments pepa,
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 -- Commenting this in the fix 12769510 as it is found to be commented in 11i code
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pa.effective_end_date >= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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.payroll_id = pa.payroll_id -- Added for bug 9862281
AND pesl.status_code IN ('A', 'N')
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pesl.effective_date > (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl.assignment_id,pesl.business_group_id,pesl.payroll_id) --13566809
),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((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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 -- Commenting this in the fix 12769510 as it is found to be commented in 11i code
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pa.effective_end_date >= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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 -- Commenting this in the fix 12769510 as it is found to be commented in 11i code
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pesl.effective_date <= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl.assignment_id,pesl.business_group_id,pesl.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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 /*+INDEX(pesl2 PSP_ENC_SUMMARY_LINES_N6)*/ 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((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl2.assignment_id,pesl2.business_group_id,pesl2.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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');
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((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl.assignment_id,pesl.business_group_id,pesl.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),l_enc_begin_date)) --13566809
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((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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 -- Commenting this in the fix 12769510 as it is found to be commented in 11i code
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pa.effective_end_date >= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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 /*+
index(Pepa PSP_ENC_PAYROLL_ASSIGNMENTS_N1)
index(PA PER_ASSIGNMENTS_F_PK)
INDEX(pep PSP_ENC_PAYROLLS_N1) */
DISTINCT l_request_id, p_payroll_id, pa.assignment_id, 'CR', p_payroll_action_id
FROM psp_enc_payrolls pep,
psp_enc_payroll_assignments pepa,
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 -- Commenting this in the fix 12769510 as it is found to be commented in 11i code
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pa.effective_end_date >= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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.payroll_id = pa.payroll_id -- Added for bug 9862281
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 /*+
INDEX(ppa PAY_PAYROLL_ACTIONS_N51)
INDEX(ptp PER_TIME_PERIODS_N50)
INDEX(paa PAY_ASSIGNMENT_ACTIONS_N4) */
Max(ptp.end_date)
FROM pay_payroll_actions ppa,
per_time_periods ptp,
pay_assignment_actions paa
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 -- Commented for the Bug 12647364
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' -- Bug: 12769510 -- added the following two conditions as
-- they are existing in 11i code and are related to the fix 6122540
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) || '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((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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 -- Commenting this in the fix 12769510 as it is found to be commented in 11i code
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pa.effective_end_date >= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pa.assignment_id,pa.business_group_id,pa.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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 -- Commenting this in the fix 12769510 as it is found to be commented in 11i code
-- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
AND pesl.effective_date <= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl.assignment_id,pesl.business_group_id,pesl.payroll_id) --13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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 /*+INDEX(pesl2 PSP_ENC_SUMMARY_LINES_N6)*/ 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((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl2.assignment_id,pesl2.business_group_id,pesl2.payroll_id)--13566809
-- AND ptp.time_period_id = ppa.time_period_id -- Added for bug 9862281 -- Commented for the Bug 12647364
),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);
SELECT /*+ FIRST_ROWS(1) */ 1
INTO l_asg_exists
FROM psp_enc_changed_asg_history
WHERE assignment_id = asg_id_tab(rec_i)
AND payroll_id = p_payroll_id
AND ROWNUM < 2;
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 = asg_id_tab(rec_i);
fnd_file.put_line(fnd_file.log,fnd_date.date_to_canonical(SYSDATE) || ' Deleted asg ' ||asg_id_tab(rec_i));
hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Deleted new 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 ( gl_code_combination_id IS NOT NULL
OR 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 ( gl_code_combination_id IS NOT NULL
OR 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 ( gl_code_combination_id IS NOT NULL
OR 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 ( gl_code_combination_id IS NOT NULL
OR 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 ( gl_code_combination_id IS NOT NULL
OR 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;