[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_insert_str VARCHAR2(5000); -- Introduced for Bug fix 2935850
select pcv_information7 suspense_account
from pqp_configuration_values
where pcv_information_category = 'PSP_ENABLE_AUTOPOPULATION'
and legislation_code is null
and nvl(business_group_id, p_business_group_id) = p_business_group_id;
SELECT NVL(pcv_information1,'N')
FROM pqp_configuration_values
WHERE pcv_information_category = 'PSP_CREATE_STAT_BATCH_IN_GMS'
AND legislation_code IS NULL
AND NVL(business_group_id, p_business_group_id) = p_business_group_id; -- change
SELECT payroll_control_id
FROM psp_payroll_controls
WHERE source_type = nvl(p_source_type,source_type)
AND payroll_source_code = nvl(p_source_code,payroll_source_code)
AND payroll_id = NVL (p_payroll_id, payroll_id) -- Bug 7137063
AND time_period_id = nvl(p_time_period_id,time_period_id)
AND nvl(batch_name,'N') = nvl(nvl(p_batch_name,batch_name),'N')
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND status_code in ('N','I') --- added 'I' for 2444657
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND source_type <> 'A' -- Adjustments are delinked.
AND parent_payroll_control_id is null;
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name,
phase
FROM psp_payroll_controls
where status_code in ('N','I')
and source_type <> 'A'
and payroll_control_id = l_payroll_control_id
or parent_payroll_control_id = l_payroll_control_id;
SELECT psp_st_run_id_s.nextval
INTO g_run_id
FROM dual;
UPDATE psp_payroll_controls
SET status_code = 'I',
run_id = g_run_id
WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
select max(gms_batch_name)
into l_gms_batch_name
from psp_summary_lines
where payroll_control_id = payroll_control_rec.payroll_control_id;
select count(*)
into l_ti_not_complete
from pa_transaction_interface_all
where transaction_status_code = 'I'
and batch_name = to_char(l_gms_batch_name)
and transaction_source in ('GOLD', 'OLD');
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name,
phase
FROM psp_payroll_controls
WHERE status_code = 'I'
AND run_id = g_run_id
AND source_type <> 'A';
--- Also removed update statment that makes summary lines status = R
-- whenever status = 'N', becos there is a new phase Summarize
-- This part is used to delete the rejected summary lines, mark the status_code
-- in psp_payroll_controls to 'P' or 'N',delete the zero line reversal entry records
/* Bug 1929317.. Did some changes here...deleted the original code.....Look into previous version
to compare .. Basically to check the number of lines in dist lines ...instead of summing on dist amount
for deciding wether to change control rec status to either 'P' or 'N' */
IF payroll_control_rec.source_type = 'O' OR payroll_control_rec.source_type = 'N' THEN
DELETE FROM psp_distribution_lines
WHERE distribution_amount = 0
AND payroll_sub_line_id IN (
select payroll_sub_line_id from psp_payroll_sub_lines where payroll_line_id IN (
select payroll_line_id from psp_payroll_lines where payroll_control_id IN (
select payroll_control_id from psp_payroll_controls where payroll_control_id=
payroll_control_rec.payroll_control_id)));
SELECT count(*)
INTO l_line_counter
FROM psp_distribution_lines pdl,
psp_payroll_sub_lines ppsl,
psp_payroll_lines ppl
WHERE ppl.payroll_control_id = payroll_control_rec.payroll_control_id
AND ppl.payroll_line_id = ppsl.payroll_line_id
AND ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id;
DELETE FROM psp_summary_lines
WHERE payroll_control_id = payroll_control_rec.payroll_control_id
AND status_code = 'R';
UPDATE psp_payroll_controls
SET status_code = 'P',
run_id = NULL,
phase = NULL
WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
UPDATE psp_payroll_controls
SET status_code = 'N',
run_id = NULL
WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
DELETE FROM psp_pre_gen_dist_lines
WHERE distribution_amount = 0
AND reversal_entry_flag = 'Y'
AND payroll_control_id = payroll_control_rec.payroll_control_id;
SELECT count(*)
INTO l_line_counter
FROM psp_pre_gen_dist_lines ppgd
WHERE ppgd.payroll_control_id = payroll_control_rec.payroll_control_id;
DELETE FROM psp_summary_lines
WHERE payroll_control_id = payroll_control_rec.payroll_control_id
AND status_code = 'R';
UPDATE psp_payroll_controls
SET status_code = 'P',
run_id = NULL,
phase = NULL
WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
UPDATE psp_payroll_controls
SET status_code = 'N',
run_id = NULL
WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name,
gl_posting_override_date,
business_group_id,
set_of_books_id,
currency_code -- Introduced for Bug 2916848 Ilo Mrc
FROM psp_payroll_controls
WHERE source_type <> 'A'
AND run_id = g_run_id
AND (phase is null or
phase in ('GMS_Tie_Back', 'GL_Tie_Back')); ---- added for 2444657
SELECT ppl.person_id,
ppl.assignment_id,
decode(pdl.reversal_entry_flag,'Y',ppl.gl_code_combination_id,NULL,
nvl(pdl.suspense_auto_glccid, --- 5080403
nvl(pos.gl_code_combination_id,
nvl(pdl.auto_gl_code_combination_id, --- added for 2663344
nvl(psl.gl_code_combination_id,
nvl(pod.gl_code_combination_id,
nvl(pea.gl_code_combination_id,
pdls.gl_code_combination_id))))))) gl_ccid,
decode(pdl.reversal_entry_flag,'Y',decode(ppl.dr_cr_flag,'D','C','C','D'),NULL,ppl.dr_cr_flag) dr_cr_flag,
pdl.effective_date,
nvl(ppc.gl_posting_override_date, ppl.accounting_date) accounting_date,
nvl(ppl.exchange_rate_type,ppc.exchange_rate_type) exchange_rate_type, --- added for 3108109
pdl.distribution_amount,
pdl.distribution_line_id,
pdl.auto_gl_code_combination_id,
'D' tab_flag,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute_category, pos.attribute_category), NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute1, pos.attribute8), NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute2, pos.attribute8), NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute3, pos.attribute8), NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute4, pos.attribute8), NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute5, pos.attribute8), NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute6, pos.attribute8), NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute7, pos.attribute8), NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute8, pos.attribute8), NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute9, pos.attribute9), NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute10, pos.attribute10), NULL) attribute10
---decode(pdl.suspense_org_account_id, NULL, 'N', 'Y') Suspense_Flag commented for 2663344
FROM psp_schedule_lines psl,
psp_organization_accounts pod,
psp_organization_accounts pos,
psp_element_type_accounts pea,
psp_default_labor_schedules pdls,
psp_payroll_controls ppc,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_distribution_lines pdl
WHERE pdl.status_code = 'N'
AND pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
AND ppsl.payroll_line_id = ppl.payroll_line_id
AND ppl.payroll_control_id = ppc.payroll_control_id
AND pdl.gl_project_flag = 'G'
AND pdl.distribution_amount <> 0
AND ppc.payroll_control_id = p_payroll_control_id
AND ppc.business_group_id = p_business_group_id
AND ppc.set_of_books_id = p_set_of_books_id
AND pdl.schedule_line_id = psl.schedule_line_id(+)
AND pdl.default_org_account_id = pod.organization_account_id(+)
AND pdl.element_account_id = pea.element_account_id(+)
AND pdl.org_schedule_id = pdls.org_schedule_id(+)
AND pdl.suspense_org_account_id = pos.organization_account_id(+)
AND pdl.cap_excess_glccid is null
UNION
SELECT ppg.person_id,
ppg.assignment_id,
nvl(ppg.suspense_auto_glccid,
nvl(pos.gl_code_combination_id,
ppg.gl_code_combination_id)) gl_ccid,
decode(ppg.reversal_entry_flag, 'Y', decode(ppg.dr_cr_flag, 'C', 'D', 'D', 'C'), ppg.dr_cr_flag) dr_cr_flag,
ppg.effective_date,
ppc.gl_posting_override_date accounting_date, --- 3108109
ppc.exchange_rate_type, --- added for 3108109
ppg.distribution_amount,
ppg.pre_gen_dist_line_id distribution_line_id,
to_number(NULL) auto_gl_code_combination_id, -- Place holder for auto pop details
'P' tab_flag,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute_category, pos.attribute_category), NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute1, pos.attribute1), NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute2, pos.attribute2), NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute3, pos.attribute3), NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute4, pos.attribute4), NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute5, pos.attribute5), NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute6, pos.attribute6), NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute7, pos.attribute7), NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute8, pos.attribute8), NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute9, pos.attribute9), NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute10, pos.attribute10), NULL) attribute10
---decode(ppg.suspense_org_Account_id, NULL, 'N', 'Y') Suspense_Flag commented for 2663344
FROM psp_pre_gen_dist_lines ppg,
psp_organization_accounts pos,
psp_payroll_controls ppc
WHERE ppg.status_code = 'N'
/* changed following condition for bug 2007521 */
AND ((ppg.gl_code_combination_id IS NOT NULL and pos.project_id is null)
OR pos.gl_code_combination_id IS NOT NULL)
AND ppg.suspense_org_account_id = pos.organization_account_id(+)
AND ppg.payroll_control_id = p_payroll_control_id
AND ppg.business_group_id = p_business_group_id
AND ppg.set_of_books_id = p_set_of_books_id
AND ppc.payroll_control_id = p_payroll_control_id
UNION
SELECT ppl.person_id,
ppl.assignment_id,
decode(pdl.reversal_entry_flag,'Y',ppl.gl_code_combination_id,NULL,
nvl(pos.gl_code_combination_id,
nvl(pdl.auto_gl_code_combination_id, pdl.cap_excess_glccid))) gl_ccid,
decode(pdl.reversal_entry_flag,'Y',decode(ppl.dr_cr_flag,'D','C','C','D'),NULL,ppl.dr_cr_flag) dr_cr_flag,
pdl.effective_date,
nvl(ppc.gl_posting_override_date, ppl.accounting_date) accounting_date,
nvl(ppl.exchange_rate_type,ppc.exchange_rate_type) exchange_rate_type, --- added for 3108109
pdl.distribution_amount,
pdl.distribution_line_id,
pdl.auto_gl_code_combination_id,
'D' tab_flag,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute_category, pos.attribute_category), NULL) attribute_category,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute1, pos.attribute8), NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute2, pos.attribute8), NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute3, pos.attribute8), NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute4, pos.attribute8), NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute5, pos.attribute8), NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute6, pos.attribute8), NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute7, pos.attribute8), NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute8, pos.attribute8), NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute9, pos.attribute9), NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute10, pos.attribute10), NULL) attribute10
FROM psp_payroll_controls ppc,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_organization_accounts pos,
psp_distribution_lines pdl
WHERE pdl.status_code = 'N'
AND pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
AND ppsl.payroll_line_id = ppl.payroll_line_id
AND ppl.payroll_control_id = ppc.payroll_control_id
AND pdl.gl_project_flag = 'G'
AND pdl.distribution_amount <> 0
AND ppc.payroll_control_id = p_payroll_control_id
AND ppc.business_group_id = p_business_group_id
AND ppc.set_of_books_id = p_set_of_books_id
AND pdl.suspense_org_account_id = pos.organization_account_id(+)
AND pdl.cap_excess_glccid is not null
AND pdl.reversal_entry_flag is null
ORDER BY 1,2,3,4,6,7,12,13,14,15,16,17,18,19,20,21,22,5;
SELECT end_date
INTO l_period_end_date
FROM per_time_periods
WHERE time_period_id = payroll_control_rec.time_period_id;
update psp_payroll_controls set phase = 'Summarize_GL_Lines' ---2444657:changed from NULL
where payroll_control_id = payroll_control_rec.payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling insert_into_summary_lines');
insert_into_summary_lines(
l_summary_line_id,
l_person_id,
l_assignment_id,
payroll_control_rec.time_period_id,
l_effective_date,
nvl(l_accounting_date,l_period_end_date),
l_exchange_rate_type, --- added 2 vars for 3108109
payroll_control_rec.source_type,
payroll_control_rec.payroll_source_code,
payroll_control_rec.set_of_books_id,
l_gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL,
round(l_summary_amount,l_precision), -- For Bug 2916848 Ilo Mrc Ehn
l_dr_cr_flag,
'N',
payroll_control_rec.batch_name,
payroll_control_rec.payroll_control_id,
payroll_control_rec.business_group_id,
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
UPDATE psp_distribution_lines
SET summary_line_id = l_summary_line_id WHERE distribution_line_id = l_dist_line_id;
UPDATE psp_pre_gen_dist_lines
SET summary_line_id = l_summary_line_id WHERE pre_gen_dist_line_id = l_dist_line_id;
dist_line_id.delete;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling insert_into_summary_lines');
insert_into_summary_lines(
l_summary_line_id,
l_person_id,
l_assignment_id,
payroll_control_rec.time_period_id,
l_effective_date,
nvl(l_accounting_date,l_period_end_date), --- added for 3108109
l_exchange_rate_type,
payroll_control_rec.source_type,
payroll_control_rec.payroll_source_code,
payroll_control_rec.set_of_books_id,
l_gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL,
round(l_summary_amount, l_precision),
l_dr_cr_flag,
'N',
payroll_control_rec.batch_name,
payroll_control_rec.payroll_control_id,
payroll_control_rec.business_group_id,
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status);
UPDATE psp_distribution_lines
SET summary_line_id = l_summary_line_id,
status_code = 'N'
WHERE distribution_line_id = l_dist_line_id;
UPDATE psp_pre_gen_dist_lines
SET summary_line_id = l_summary_line_id,
status_code = 'N'
WHERE pre_gen_dist_line_id = l_dist_line_id;
dist_line_id.delete;
PROCEDURE insert_into_summary_lines(
P_SUMMARY_LINE_ID OUT NOCOPY NUMBER,
P_PERSON_ID IN NUMBER,
P_ASSIGNMENT_ID IN NUMBER,
P_TIME_PERIOD_ID IN NUMBER,
P_EFFECTIVE_DATE IN DATE,
P_ACCOUNTING_DATE IN DATE, --- added 2 vars for 3108109
P_EXCHANGE_RATE_TYPE IN VARCHAR2,
P_SOURCE_TYPE IN VARCHAR2,
P_SOURCE_CODE IN VARCHAR2,
P_SET_OF_BOOKS_ID IN NUMBER,
P_GL_CODE_COMBINATION_ID IN NUMBER,
P_PROJECT_ID IN NUMBER,
P_EXPENDITURE_ORGANIZATION_ID IN NUMBER,
P_EXPENDITURE_TYPE IN VARCHAR2,
P_TASK_ID IN NUMBER,
P_AWARD_ID IN NUMBER,
P_SUMMARY_AMOUNT IN NUMBER,
P_DR_CR_FLAG IN VARCHAR2,
P_STATUS_CODE IN VARCHAR2,
P_INTERFACE_BATCH_NAME IN VARCHAR2,
P_PAYROLL_CONTROL_ID IN NUMBER,
P_BUSINESS_GROUP_ID IN NUMBER,
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_RETURN_STATUS OUT NOCOPY VARCHAR2,
P_ORG_ID IN NUMBER DEFAULT NULL -- R12 MOAc uptake
) IS
l_gms_posting_effective_date DATE;
SELECT PSP_SUMMARY_LINES_S.NEXTVAL
INTO P_SUMMARY_LINE_ID
FROM DUAL;
INSERT INTO PSP_SUMMARY_LINES(
SUMMARY_LINE_ID,
PERSON_ID,
ASSIGNMENT_ID,
TIME_PERIOD_ID,
EFFECTIVE_DATE,
ACCOUNTING_DATE, --- added 2 vars for 3108109
EXCHANGE_RATE_TYPE,
GMS_POSTING_EFFECTIVE_DATE, /* New column added for Enhancement Employee Assignment with Zero Work Days */
SOURCE_TYPE,
SOURCE_CODE,
SET_OF_BOOKS_ID,
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
SUMMARY_AMOUNT,
DR_CR_FLAG,
STATUS_CODE,
INTERFACE_BATCH_NAME,
PAYROLL_CONTROL_ID,
BUSINESS_GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
ACTUAL_SUMMARY_AMOUNT, --For Bug 2496661
attribute_category, -- Introduced for bug fix 2908859
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
org_id -- R12 MOAc uptake
)
VALUES(
P_SUMMARY_LINE_ID,
P_PERSON_ID,
P_ASSIGNMENT_ID,
P_TIME_PERIOD_ID,
P_EFFECTIVE_DATE,
P_ACCOUNTING_DATE, -- added 2 vars for 3108109
P_EXCHANGE_RATE_TYPE,
L_GMS_POSTING_EFFECTIVE_DATE, /* New column added for Enhancement Employee Assignment with Zero Work Days */
P_SOURCE_TYPE,
P_SOURCE_CODE,
P_SET_OF_BOOKS_ID,
P_GL_CODE_COMBINATION_ID,
P_PROJECT_ID,
P_EXPENDITURE_ORGANIZATION_ID,
P_EXPENDITURE_TYPE,
P_TASK_ID,
P_AWARD_ID,
P_SUMMARY_AMOUNT,
P_DR_CR_FLAG,
P_STATUS_CODE,
P_INTERFACE_BATCH_NAME,
P_PAYROLL_CONTROL_ID,
P_BUSINESS_GROUP_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE,
DECODE(P_PROJECT_ID, NULL,P_SUMMARY_AMOUNT, DECODE(P_DR_CR_FLAG,'C',0 - P_SUMMARY_AMOUNT,P_SUMMARY_AMOUNT)), --For Bug 2496661
p_attribute_category, -- Introduced for bug fix 2908859
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_org_id -- R12 MOAC uptake
);
g_error_api_path := 'INSERT_INTO_SUMMARY_LINES:'||g_error_api_path;
fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','INSERT_INTO_SUMMARY_LINES');
SELECT DISTINCT source_type,
payroll_source_code,
time_period_id,
batch_name,
phase --- 2444657, to separate the old and new gl imports
--- S and T can pull control recs with statuses 'I' and 'N' now
FROM psp_payroll_controls
WHERE status_code = 'I'
AND source_type <> 'A'
AND run_id = g_run_id
AND phase in ('Summarize_GL_Lines', 'Submitted_JI_Request'); --- 2444657: added the condition
SELECT payroll_control_id,
currency_code,
exchange_rate_type,
phase --- 2444657: added
FROM psp_payroll_controls
WHERE source_type = p_source_type
AND payroll_source_code = p_source_code
AND time_period_id = p_time_period_id
AND nvl(batch_name,'N') = nvl(nvl(p_batch_name,batch_name),'N')
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND status_code = 'I'
AND source_type <> 'A'
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND run_id = g_run_id
AND phase = p_phase; --- 2444657: added the condition;
SELECT psl.summary_line_id,
psl.source_code,
psl.effective_date,
psl.accounting_date, -- added 2 cols for 3108109
psl.exchange_Rate_type,
psl.set_of_books_id,
psl.gl_code_combination_id,
psl.summary_amount,
psl.dr_cr_flag,
psl.attribute1,
psl.attribute2,
psl.attribute3,
psl.attribute4,
psl.attribute5,
psl.attribute6,
psl.attribute7,
psl.attribute8,
psl.attribute9,
psl.attribute10,
psl.attribute11,
psl.attribute12,
psl.attribute13,
psl.attribute14,
psl.attribute15,
psl.attribute16,
psl.attribute17,
psl.attribute18,
psl.attribute19,
psl.attribute20,
psl.attribute21,
psl.attribute22,
psl.attribute23,
psl.attribute24,
psl.attribute25,
psl.attribute26,
psl.attribute27,
psl.attribute28,
psl.attribute29,
psl.attribute30
FROM psp_summary_lines psl
WHERE psl.status_code = 'N'
AND psl.gl_code_combination_id IS NOT NULL
AND psl.payroll_control_id = l_payroll_control_id;
SELECT gl_interface_control_s.nextval
INTO l_group_id
FROM dual;
SELECT substr(period_name,1,35),end_date
INTO l_period_name, l_period_end_date
FROM per_time_periods
WHERE time_period_id = gl_batch_rec.time_period_id;
-- update psp_summary_lines with group_id
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' update psp_summary_lines with group_id : '||l_group_id);
UPDATE psp_summary_lines
SET group_id = l_group_id
WHERE status_code = 'N'
AND gl_code_combination_id IS NOT NULL
AND payroll_control_id = gl_payroll_control_rec.payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling insert_into_gl_interface GL_INTERFACE_REC.SUMMARY_LINE_ID: '||GL_INTERFACE_REC.SUMMARY_LINE_ID);
insert_into_gl_interface(
P_SET_OF_BOOKS_ID, GL_INTERFACE_REC.EFFECTIVE_DATE,
gl_payroll_control_rec.CURRENCY_CODE,
L_USER_JE_CATEGORY_NAME,L_USER_JE_SOURCE_NAME,L_ENCUMBRANCE_TYPE_ID,
GL_INTERFACE_REC.GL_CODE_COMBINATION_ID,L_ENTERED_DR,L_ENTERED_CR,
L_GROUP_ID,L_REFERENCE1,L_REFERENCE1,L_REFERENCE4,
GL_INTERFACE_REC.SUMMARY_LINE_ID,L_REFERENCE4,
GL_INTERFACE_REC.ATTRIBUTE1,GL_INTERFACE_REC.ATTRIBUTE2,
GL_INTERFACE_REC.ATTRIBUTE3,GL_INTERFACE_REC.ATTRIBUTE4,
GL_INTERFACE_REC.ATTRIBUTE5,GL_INTERFACE_REC.ATTRIBUTE6,
GL_INTERFACE_REC.ATTRIBUTE7,GL_INTERFACE_REC.ATTRIBUTE8,
GL_INTERFACE_REC.ATTRIBUTE9,GL_INTERFACE_REC.ATTRIBUTE10,
GL_INTERFACE_REC.ATTRIBUTE11,GL_INTERFACE_REC.ATTRIBUTE12,
GL_INTERFACE_REC.ATTRIBUTE13,GL_INTERFACE_REC.ATTRIBUTE14,
GL_INTERFACE_REC.ATTRIBUTE15,GL_INTERFACE_REC.ATTRIBUTE16,
GL_INTERFACE_REC.ATTRIBUTE17,GL_INTERFACE_REC.ATTRIBUTE18,
GL_INTERFACE_REC.ATTRIBUTE19,GL_INTERFACE_REC.ATTRIBUTE20,
GL_INTERFACE_REC.ATTRIBUTE21,GL_INTERFACE_REC.ATTRIBUTE22,
GL_INTERFACE_REC.ATTRIBUTE23,GL_INTERFACE_REC.ATTRIBUTE24,
GL_INTERFACE_REC.ATTRIBUTE25,GL_INTERFACE_REC.ATTRIBUTE26,
GL_INTERFACE_REC.ATTRIBUTE27,GL_INTERFACE_REC.ATTRIBUTE28,
GL_INTERFACE_REC.ATTRIBUTE29,GL_INTERFACE_REC.ATTRIBUTE30,
GL_INTERFACE_REC.EXCHANGE_RATE_TYPE, -- modified for 3108109
GL_INTERFACE_REC.ACCOUNTING_DATE,
-- Introduced for Bug 2916848
L_RETURN_STATUS);
select group_id
into l_group_id
from psp_summary_lines
where payroll_control_id in
(SELECT payroll_control_id
FROM psp_payroll_controls
WHERE source_type = gl_batch_rec.source_type
AND payroll_source_code = gl_batch_rec.payroll_source_code
AND time_period_id = gl_batch_rec.time_period_id
AND nvl(batch_name,'N') = nvl(nvl(gl_batch_rec.batch_name,batch_name),'N')
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND status_code = 'I'
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND run_id = g_run_id
AND phase = 'Submitted_JI_Request')
and gl_code_combination_id is not null
and rownum = 1;
-- insert into gl_interface_control
SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
INTO l_int_run_id
FROM dual;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' insert into gl_interface_control');
INSERT into gl_interface_control(je_source_name,status,interface_run_id,
group_id,set_of_books_id)
VALUES (l_user_je_source_name, 'S',l_int_run_id,
l_group_id,p_set_of_books_id);
-- Added the update for Rollback purposes.
UPDATE psp_payroll_controls
SET phase = 'Submitted_JI_Request'
-- Introduced selective payroll control filter for bug fix 3157895
WHERE source_type = NVL(gl_batch_rec.source_type, source_type)
AND payroll_source_code = NVL(gl_batch_rec.payroll_source_code, payroll_source_code)
AND time_period_id = NVL(gl_batch_rec.time_period_id, time_period_id)
AND NVL(batch_name,'N') = NVL(NVL(gl_batch_rec.batch_name, batch_name), 'N')
AND status_code = 'I'
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND run_id = g_run_id;
SELECT user_je_source_name
INTO p_user_je_source_name
FROM gl_je_sources
WHERE je_source_name = 'OLD';
SELECT user_je_category_name
INTO p_user_je_category_name
FROM gl_je_categories
WHERE je_category_name = 'OLD';
SELECT encumbrance_type_id
INTO p_encumbrance_type_id
FROM gl_encumbrance_types
WHERE encumbrance_type = 'OLD'
AND enabled_flag = 'Y';
SELECT summary_line_id,
dr_cr_flag,summary_amount
FROM psp_summary_lines
WHERE group_id = p_group_id
AND payroll_control_id = p_payroll_control_id;
SELECT status,
reference6
FROM gl_interface
WHERE group_id = p_group_id
AND set_of_books_id = p_set_of_books_id
AND user_je_source_name = 'OLD'
AND reference6 in(select summary_line_id FROM psp_summary_lines -- Bug 7376898
WHERE GROUP_ID = p_group_id
AND payroll_control_id = p_payroll_control_id);
SELECT pdl.rowid,
pdl.distribution_line_id line_id,
pdl.distribution_date,
pdl.suspense_org_account_id,
pdl.reversal_entry_flag,
pdl.effective_date -- Bug 7040943 Added
FROM psp_distribution_lines pdl
WHERE pdl.summary_line_id = p_summary_line_id
UNION
SELECT ppgd.rowid,
ppgd.pre_gen_dist_line_id line_id,
ppgd.distribution_date,
ppgd.suspense_org_account_id,
ppgd.reversal_entry_flag,
ppgd.effective_date -- Bug 7040943 Added
FROM psp_pre_gen_dist_lines ppgd
WHERE ppgd.summary_line_id = p_summary_line_id;
SELECT hou.organization_id, hou.name, poa.gl_code_combination_id
FROM hr_all_organization_units hou, psp_organization_accounts poa
WHERE hou.organization_id = poa.organization_id
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND poa.organization_account_id = p_org_id;
SELECT hou.organization_id, hou.name
FROM hr_all_organization_units hou,
per_assignments_f paf,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_distribution_lines pdl
WHERE pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
AND ppsl.payroll_line_id = ppl.payroll_line_id
AND pdl.distribution_line_id = p_line_id
AND ppl.assignment_id = paf.assignment_id
AND pdl.distribution_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id
AND pdl.distribution_date between
hou.date_from and nvl(hou.date_to,pdl.distribution_date)
UNION
SELECT hou.organization_id, hou.name
FROM hr_all_organization_units hou,
per_assignments_f paf,
psp_pre_gen_dist_lines ppgd
WHERE ppgd.pre_gen_dist_line_id = p_line_id
AND ppgd.assignment_id = paf.assignment_id
AND ppgd.distribution_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.organization_id = hou.organization_id;
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.award_id,
poa.task_id,
poa.expenditure_organization_id,
poa.expenditure_type
FROM psp_organization_accounts poa
WHERE poa.organization_id = p_organization_id
AND poa.account_type_code = 'S'
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND p_distribution_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_distribution_date);
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.award_id,
poa.task_id,
poa.expenditure_organization_id,
poa.expenditure_type
FROM psp_organization_accounts poa
WHERE
/* poa.account_type_code = 'G'
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND p_distribution_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_distribution_date); Bug 2056877.*/
select ppl.element_type_id,
ppl.assignment_id,
ppl.person_id
from psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_distribution_lines pdl
where pdl.distribution_line_id = x_line_id
and pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
and ppsl.payroll_line_id = ppl.payroll_line_id
union all
select ppg.element_type_id,
ppg.assignment_id,
ppg.person_id
from psp_pre_gen_dist_lines ppg
where pre_gen_dist_line_id = x_line_id;
select ppf.full_name,
paf.assignment_number,
pet.element_name,
hou.name
from per_all_people_f ppf,
per_all_assignments_f paf,
pay_element_types_f pet,
hr_all_organization_units hou
where ppf.person_id = l_person_id
and l_distribution_date between ppf.effective_start_date and ppf.effective_end_date
and paf.assignment_id = l_assignment_id
and l_distribution_date between paf.effective_start_date and paf.effective_end_date
and pet.element_type_id = l_element_type_id
and l_distribution_date between pet.effective_start_date and pet.effective_end_date
and hou.organization_id = paf.organization_id;
select count(*)
into l_no_run
from gl_interface
where status = 'NEW'
and group_id = p_group_id
and user_je_source_name = 'OLD'
AND reference6 in(select summary_line_id FROM psp_summary_lines -- Bug 7376898
WHERE GROUP_ID = p_group_id
AND payroll_control_id = p_payroll_control_id);
delete from gl_interface
where group_id = p_group_id
and user_je_source_name = 'OLD'
AND reference6 in(select summary_line_id FROM psp_summary_lines -- Bug 7376898
WHERE GROUP_ID = p_group_id
AND payroll_control_id = p_payroll_control_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface count: '||sql%rowcount);
delete from psp_summary_lines
where payroll_control_id = p_payroll_control_id
and group_id = p_group_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_summary_lines count: '||sql%rowcount);
delete gl_interface_Control
where group_id = p_group_id
and je_source_name = l_user_je_source_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface_control count: '||sql%rowcount);
update psp_payroll_controls
set phase = null
where payroll_control_id = p_payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_payroll_controls count: '||sql%rowcount);
SELECT count(*)
INTO l_cnt_gl_interface
FROM gl_interface
WHERE group_id = p_group_id
AND user_je_source_name = 'OLD'
AND set_of_books_id = p_set_of_books_id
AND reference6 in(select summary_line_id FROM psp_summary_lines -- Bug 7376898
WHERE GROUP_ID = p_group_id
AND payroll_control_id = p_payroll_control_id);
UPDATE psp_payroll_controls
SET phase = 'GL_Tie_Back'
WHERE payroll_control_id = p_payroll_control_id;
UPDATE psp_summary_lines
SET interface_status = l_status, status_code = 'R'
WHERE summary_line_id = to_number(l_reference6);
SELECT payroll_action_type
INTO l_pay_action_type
FROM psp_payroll_lines
WHERE payroll_control_id = p_payroll_control_id
and payroll_line_id = (select payroll_line_id from psp_payroll_sub_lines
where payroll_sub_line_id = (select payroll_sub_line_id
from psp_distribution_lines
where distribution_line_id = x_line_id));
select gl_code_combination_id into x_lines_glccid
from psp_summary_lines where summary_line_id = to_number(l_reference6);
/* Bug 1874696: deleted the procedure call for psp_general.poeta_effective_date
*/
l_effective_date := l_distribution_date; --- added for Bug 2663344
UPDATE PSP_DISTRIBUTION_LINES
SET pre_distribution_run_flag = gl_project_flag
WHERE rowid = l_rowid;
UPDATE psp_distribution_lines
SET suspense_org_account_id = l_organization_account_id,
suspense_reason_code = 'ST:' || l_status,
gl_project_flag = l_gl_project_flag,
status_code = 'N',
effective_date = l_effective_date, --- for Bug 2663344
suspense_auto_glccid = l_new_glccid, --- added suspense_auto for 5080403
suspense_auto_exp_type = l_new_expenditure_type
WHERE rowid = l_rowid;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 1 updated psp_distribution_lines count: '||sql%rowcount);
UPDATE psp_pre_gen_dist_lines
SET suspense_org_account_id = l_organization_account_id,
suspense_reason_code = 'ST:' || l_status,
status_code = 'N',
effective_date = l_effective_date, --- for Bug 2663344
suspense_auto_glccid = l_new_glccid, --- added suspense_auto for 5080403
suspense_auto_exp_type = l_new_expenditure_type
WHERE rowid = l_rowid;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 1 updated psp_pre_gen_dist_lines count: '||sql%rowcount);
delete gl_interface
where group_id = p_group_id
and set_of_books_id = p_set_of_books_id
AND user_je_source_name = l_user_je_source_name
and reference6 in(select summary_line_id FROM psp_summary_lines -- Bug 7376898
WHERE GROUP_ID = p_group_id
AND payroll_control_id = p_payroll_control_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from gl_interface count: '||sql%rowcount);
UPDATE psp_distribution_lines
SET suspense_org_account_id = NULL,
suspense_reason_code = NULL,
gl_project_flag = pre_distribution_run_flag,
effective_date = decode(pre_distribution_run_flag,'G',
p_period_end_date,distribution_date)
WHERE suspense_reason_code like 'ST:%'
AND summary_line_id
IN (SELECT summary_line_id
FROM psp_summary_lines
WHERE payroll_control_id = p_payroll_control_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 2 updated psp_distribution_lines count: '||sql%rowcount);
UPDATE psp_pre_gen_dist_lines
SET suspense_org_account_id = NULL,
suspense_reason_code = NULL,
effective_date = decode(NVL(gl_code_combination_id,-999),gl_code_combination_id,
p_period_end_date,distribution_date)
WHERE suspense_reason_code like 'ST:%'
AND summary_line_id
IN (SELECT summary_line_id
FROM psp_summary_lines
WHERE payroll_control_id = p_payroll_control_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 2 updated psp_pre_gen_dist_lines count: '||sql%rowcount);
UPDATE psp_distribution_lines
SET suspense_org_account_id = NULL,
suspense_reason_code = NULL,
gl_project_flag = pre_distribution_run_flag,
effective_date = decode(pre_distribution_run_flag,'G',
p_period_end_date,distribution_date)
WHERE suspense_reason_code like 'ST:%'
AND summary_line_id
IN (SELECT summary_line_id
FROM psp_summary_lines
WHERE payroll_control_id = p_payroll_control_id );
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 3 updated psp_distribution_lines count: '||sql%rowcount);
UPDATE psp_pre_gen_dist_lines
SET suspense_org_account_id = NULL,
suspense_reason_code = NULL,
effective_date = decode(NVL(gl_code_combination_id,-999),gl_code_combination_id,
p_period_end_date,distribution_date)
WHERE suspense_reason_code like 'ST:%'
AND summary_line_id
IN ( SELECT summary_line_id
FROM psp_summary_lines
WHERE payroll_control_id = p_payroll_control_id );
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 3 updated psp_pre_gen_dist_lines count: '||sql%rowcount);
UPDATE psp_summary_lines
SET status_code = 'A'
WHERE summary_line_id = l_summary_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_summary_lines set status_code = A count: '||sql%rowcount);
UPDATE psp_distribution_lines
SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_distribution_lines set status_code = A count: '||sql%rowcount);
INSERT INTO psp_distribution_lines_history
(distribution_line_id,payroll_sub_line_id,distribution_date,
effective_date,distribution_amount,status_code,suspense_reason_code,
effort_report_id,version_num,schedule_line_id,
summary_line_id,default_org_account_id,suspense_org_account_id,
element_account_id,org_schedule_id,
user_defined_field,default_reason_code,reversal_entry_flag,gl_project_flag,
auto_gl_code_combination_id, business_group_id, set_of_books_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
cap_excess_glccid, cap_excess_award_id, cap_excess_task_id,
cap_excess_project_id, cap_excess_exp_type, cap_excess_exp_org_id,
funding_source_code, annual_salary_cap, cap_excess_dist_line_id,
suspense_auto_exp_type, suspense_auto_glccid, adj_account_flag) --- added 2 cols for 508040
SELECT distribution_line_id,payroll_sub_line_id,distribution_date,
effective_date,distribution_amount,status_code,suspense_reason_code,
effort_report_id,version_num,schedule_line_id,
summary_line_id,default_org_account_id,suspense_org_account_id,
element_account_id,org_schedule_id,
user_defined_field,default_reason_code,reversal_entry_flag,gl_project_flag,
auto_gl_code_combination_id, business_group_id, set_of_books_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
cap_excess_glccid, cap_excess_award_id, cap_excess_task_id,
cap_excess_project_id, cap_excess_exp_type, cap_excess_exp_org_id,
funding_source_code, annual_salary_cap, cap_excess_dist_line_id,
suspense_auto_exp_type, suspense_auto_glccid, adj_account_flag
FROM psp_distribution_lines
WHERE status_code = 'A'
AND summary_line_id = l_summary_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' inserted into psp_distribution_lines_history count: '||sql%rowcount);
DELETE FROM psp_distribution_lines
WHERE status_code = 'A'
AND summary_line_id = l_summary_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from psp_distribution_lines count: '||sql%rowcount);
UPDATE psp_pre_gen_dist_lines
SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 4 updated psp_pre_gen_distribution_lines set status_code = A count: '||sql%rowcount);
INSERT INTO psp_pre_gen_dist_lines_history
(pre_gen_dist_line_id,distribution_interface_id,person_id,assignment_id,
element_type_id,distribution_date,effective_date,distribution_amount,
dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
batch_name,status_code,set_of_books_id,
gl_code_combination_id,project_id,expenditure_organization_id,
expenditure_type,task_id,award_id,suspense_reason_code,
effort_report_id,version_num,summary_line_id,suspense_org_account_id,
user_defined_field,reversal_entry_flag, business_group_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
suspense_auto_exp_type, suspense_auto_glccid) -- added 2 cols for 5080403
SELECT pre_gen_dist_line_id,distribution_interface_id,person_id,assignment_id,
element_type_id,distribution_date,effective_date,distribution_amount,
dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
batch_name,status_code,set_of_books_id,
gl_code_combination_id,project_id,expenditure_organization_id,
expenditure_type,task_id,award_id,suspense_reason_code,
effort_report_id,version_num,summary_line_id,suspense_org_account_id,
user_defined_field,reversal_entry_flag, business_group_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
suspense_auto_exp_type, suspense_auto_glccid
FROM psp_pre_gen_dist_lines
WHERE status_code = 'A'
AND summary_line_id = l_summary_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' inserted into psp_pre_gen_dist_lines_history count: '||sql%rowcount);
DELETE FROM psp_pre_gen_dist_lines
WHERE status_code = 'A'
AND summary_line_id = l_summary_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from psp_pre_gen_dist_lines count: '||sql%rowcount);
UPDATE psp_payroll_controls
SET gl_dr_amount = nvl(gl_dr_amount,0) + l_dr_summary_amount,
gl_cr_amount = nvl(gl_cr_amount,0) + l_cr_summary_amount
WHERE payroll_control_id = p_payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_payroll_controls count: '||sql%rowcount);
SELECT pdl.payroll_sub_line_id,pdl.effective_date,
round(sum(pdl.distribution_amount), p_precision) reversal_dist_amount,-- Changed rounding from 2 to p_precision
-- For Bug 2916848
pdl.business_group_id,
pdl.set_of_books_id
FROM psp_distribution_lines pdl,
psp_payroll_sub_lines ppsl,
psp_payroll_lines ppl,
psp_payroll_controls ppc
WHERE ppc.payroll_control_id = p_payroll_control_id
AND ppc.payroll_control_id = ppl.payroll_control_id
AND ppl.payroll_line_id = ppsl.payroll_line_id
AND ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
AND nvl(pdl.reversal_entry_flag,'N') = 'N'
AND pdl.gl_project_flag = 'G'
AND pdl.status_code = 'N'
GROUP BY pdl.payroll_sub_line_id, pdl.effective_date, pdl.business_group_id, pdl.set_of_books_id;
SELECT ppgd.distribution_interface_id,
ppgd.person_id,
ppgd.assignment_id,
ppgd.element_type_id,
ppgd.dr_cr_flag,
ppgd.distribution_date,
ppgd.effective_date,
ppgd.source_type,
ppgd.source_code,
ppgd.time_period_id,
ppgd.batch_name,
ppgd.set_of_books_id,
ppgd.business_group_id,
round(sum(ppgd.distribution_amount), p_precision) reversal_dist_amount -- For Bug 2916848 changed to p_precision
FROM psp_pre_gen_dist_lines ppgd,
psp_organization_accounts pos
WHERE ppgd.payroll_control_id = p_payroll_control_id
AND nvl(ppgd.reversal_entry_flag,'N') = 'N'
AND ((ppgd.gl_code_combination_id IS NOT NULL and pos.project_id is null ) OR -- Bug 2007521 Changed condn
pos.gl_code_combination_id IS NOT NULL)
AND ppgd.suspense_org_account_id = pos.organization_account_id(+)
AND ppgd.status_code = 'N'
-- AND ppgd.business_group_id = p_business_group_id
-- AND ppgd.set_of_books_id = p_set_of_books_id
GROUP BY ppgd.distribution_interface_id, ppgd.person_id,
ppgd.assignment_id, ppgd.element_type_id,
ppgd.dr_cr_flag, ppgd.distribution_date, ppgd.effective_date,
ppgd.source_type, ppgd.source_code,
ppgd.time_period_id, ppgd.batch_name, ppgd.set_of_books_id, ppgd.business_group_id;
DELETE FROM psp_distribution_lines
WHERE reversal_entry_flag = 'Y'
AND status_code = 'N'
AND payroll_sub_line_id IN (
select payroll_sub_line_id from psp_payroll_sub_lines where payroll_line_id IN (
select payroll_line_id from psp_payroll_lines where payroll_control_id IN (
select payroll_control_id from psp_payroll_controls where payroll_control_id=
p_payroll_control_id)));
insert into psp_distribution_lines
(distribution_line_id,payroll_sub_line_id,distribution_date,effective_date,
distribution_amount,status_code,gl_project_flag,reversal_entry_flag,
business_group_id, set_of_books_id)
values
(PSP_DISTRIBUTION_LINES_S.NEXTVAL,dist_reversal_entry_rec.payroll_sub_line_id,
dist_reversal_entry_rec.effective_date,dist_reversal_entry_rec.effective_date,
dist_reversal_entry_rec.reversal_dist_amount,'N','G','Y',
dist_reversal_entry_rec.business_group_id, dist_reversal_entry_rec.set_of_books_id);
select sum(decode(reversal_entry_flag, 'Y', distribution_amount, 0)) cr_amount,
sum(decode(reversal_entry_flag, 'Y', 0, distribution_amount)) dr_amount
INTO l_cr_amount, l_dr_amount
FROM psp_distribution_lines pdl,
psp_payroll_sub_lines ppsl,
psp_payroll_lines ppl,
psp_payroll_controls ppc
WHERE ppc.payroll_control_id = p_payroll_control_id
AND ppc.payroll_control_id = ppl.payroll_control_id
AND ppl.payroll_line_id = ppsl.payroll_line_id
AND ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
AND pdl.gl_project_flag = 'G'
AND pdl.status_code = 'N';
SELECT payroll_id INTO l_payroll_id
FROM psp_payroll_controls
WHERE payroll_control_id = p_payroll_control_id;
SELECT reversing_gl_ccid
INTO l_clrg_account_glccid
FROM PSP_CLEARING_ACCOUNT
WHERE set_of_books_id = p_set_of_books_id
AND business_group_id = p_business_group_id -- Changed for MO.
AND payroll_id = l_payroll_id; -- Added for bug 5592964
delete from psp_pre_gen_dist_lines
where reversal_entry_flag = 'Y'
and status_code = 'N'
and source_type = 'P'
and payroll_control_id = p_payroll_control_id;
insert into psp_pre_gen_dist_lines
(pre_gen_dist_line_id, distribution_interface_id, person_id, assignment_id,
element_type_id, distribution_date, effective_date, distribution_amount,
dr_cr_flag, payroll_control_id, source_type, source_code, time_period_id,
batch_name, status_code, set_of_books_id, business_group_id, gl_code_combination_id,
reversal_entry_flag)
values (psp_distribution_lines_s.nextval, pg_reversal_entry_rec.distribution_interface_id,
pg_reversal_entry_rec.person_id, pg_reversal_entry_rec.assignment_id,
pg_reversal_entry_rec.element_type_id, pg_reversal_entry_rec.distribution_date,
pg_reversal_entry_rec.effective_date, pg_reversal_entry_rec.reversal_dist_amount,
pg_reversal_entry_rec.dr_cr_flag, p_payroll_control_id, pg_reversal_entry_rec.source_type,
pg_reversal_entry_rec.source_code, pg_reversal_entry_rec.time_period_id,
pg_reversal_entry_rec.batch_name, 'N', pg_reversal_entry_rec.set_of_books_id,
pg_reversal_entry_rec.business_group_id, l_clrg_account_glccid, 'Y');
select sum(decode(reversal_entry_flag, 'Y', distribution_amount, 0)),
sum(decode(reversal_entry_flag, 'Y', 0, distribution_amount))
into l_cr_amount, l_dr_amount
from psp_pre_gen_dist_lines ppgd, psp_organization_accounts pos
where payroll_control_id = p_payroll_control_id
and ppgd.status_code = 'N'
and ppgd.suspense_org_account_id = pos.organization_account_id(+)
AND ((ppgd.gl_code_combination_id IS NOT NULL and ppgd.suspense_org_account_id is null ) OR
pos.gl_code_combination_id IS NOT NULL);
PROCEDURE insert_into_gl_interface(
P_SET_OF_BOOKS_ID IN NUMBER,
P_ACCOUNTING_DATE IN DATE,
P_CURRENCY_CODE IN VARCHAR2,
P_USER_JE_CATEGORY_NAME IN VARCHAR2,
P_USER_JE_SOURCE_NAME IN VARCHAR2,
P_ENCUMBRANCE_TYPE_ID IN NUMBER,
P_CODE_COMBINATION_ID IN NUMBER,
P_ENTERED_DR IN NUMBER,
P_ENTERED_CR IN NUMBER,
P_GROUP_ID IN NUMBER,
P_REFERENCE1 IN VARCHAR2,
P_REFERENCE2 IN VARCHAR2,
P_REFERENCE4 IN VARCHAR2,
P_REFERENCE6 IN VARCHAR2,
P_REFERENCE10 IN VARCHAR2,
P_ATTRIBUTE1 IN VARCHAR2,
P_ATTRIBUTE2 IN VARCHAR2,
P_ATTRIBUTE3 IN VARCHAR2,
P_ATTRIBUTE4 IN VARCHAR2,
P_ATTRIBUTE5 IN VARCHAR2,
P_ATTRIBUTE6 IN VARCHAR2,
P_ATTRIBUTE7 IN VARCHAR2,
P_ATTRIBUTE8 IN VARCHAR2,
P_ATTRIBUTE9 IN VARCHAR2,
P_ATTRIBUTE10 IN VARCHAR2,
P_ATTRIBUTE11 IN VARCHAR2,
P_ATTRIBUTE12 IN VARCHAR2,
P_ATTRIBUTE13 IN VARCHAR2,
P_ATTRIBUTE14 IN VARCHAR2,
P_ATTRIBUTE15 IN VARCHAR2,
P_ATTRIBUTE16 IN VARCHAR2,
P_ATTRIBUTE17 IN VARCHAR2,
P_ATTRIBUTE18 IN VARCHAR2,
P_ATTRIBUTE19 IN VARCHAR2,
P_ATTRIBUTE20 IN VARCHAR2,
P_ATTRIBUTE21 IN VARCHAR2,
P_ATTRIBUTE22 IN VARCHAR2,
P_ATTRIBUTE23 IN VARCHAR2,
P_ATTRIBUTE24 IN VARCHAR2,
P_ATTRIBUTE25 IN VARCHAR2,
P_ATTRIBUTE26 IN VARCHAR2,
P_ATTRIBUTE27 IN VARCHAR2,
P_ATTRIBUTE28 IN VARCHAR2,
P_ATTRIBUTE29 IN VARCHAR2,
P_ATTRIBUTE30 IN VARCHAR2,
P_CURRENCY_CONVERSION_TYPE IN VARCHAR2, -- Introduced for bug fix 2916848
P_CURRENCY_CONVERSION_DATE IN DATE, -- Introduced for bug fix 2916848
P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
BEGIN
--dbms_output.put_line('sob id='||to_char(p_set_of_books_id));
INSERT INTO GL_INTERFACE(
STATUS,
SET_OF_BOOKS_ID,
ACCOUNTING_DATE,
CURRENCY_CODE,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
USER_JE_CATEGORY_NAME,
USER_JE_SOURCE_NAME,
ENCUMBRANCE_TYPE_ID,
CODE_COMBINATION_ID,
ENTERED_DR,
ENTERED_CR,
GROUP_ID,
REFERENCE1,
REFERENCE2,
REFERENCE4,
REFERENCE6,
REFERENCE10,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
REFERENCE21,
REFERENCE22,
REFERENCE23,
REFERENCE24,
REFERENCE25,
REFERENCE26,
REFERENCE27,
REFERENCE28,
REFERENCE29,
REFERENCE30,
-- Introduced the following columns for bug fix 2916848
USER_CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE)
VALUES(
'NEW',
P_SET_OF_BOOKS_ID,
P_ACCOUNTING_DATE,
P_CURRENCY_CODE,
SYSDATE,
FND_GLOBAL.USER_ID,
'A',
P_USER_JE_CATEGORY_NAME,
P_USER_JE_SOURCE_NAME,
P_ENCUMBRANCE_TYPE_ID,
P_CODE_COMBINATION_ID,
P_ENTERED_DR,
P_ENTERED_CR,
P_GROUP_ID,
P_REFERENCE1,
P_REFERENCE2,
P_REFERENCE4,
P_REFERENCE6,
P_REFERENCE10,
P_ATTRIBUTE1,
P_ATTRIBUTE2,
P_ATTRIBUTE3,
P_ATTRIBUTE4,
P_ATTRIBUTE5,
P_ATTRIBUTE6,
P_ATTRIBUTE7,
P_ATTRIBUTE8,
P_ATTRIBUTE9,
P_ATTRIBUTE10,
P_ATTRIBUTE11,
P_ATTRIBUTE12,
P_ATTRIBUTE13,
P_ATTRIBUTE14,
P_ATTRIBUTE15,
P_ATTRIBUTE16,
P_ATTRIBUTE17,
P_ATTRIBUTE18,
P_ATTRIBUTE19,
P_ATTRIBUTE20,
P_ATTRIBUTE21,
P_ATTRIBUTE22,
P_ATTRIBUTE23,
P_ATTRIBUTE24,
P_ATTRIBUTE25,
P_ATTRIBUTE26,
P_ATTRIBUTE27,
P_ATTRIBUTE28,
P_ATTRIBUTE29,
P_ATTRIBUTE30,
P_CURRENCY_CONVERSION_TYPE,-- Introduced the following code for Bug fix 2916848
DECODE(p_currency_conversion_type, NULL, NULL, P_CURRENCY_CONVERSION_DATE));-- Introduced the following code for Bug fix 2916848
g_error_api_path := 'INSERT_INTO_GL_INTERFACE:'||g_error_api_path;
fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','INSERT_INTO_GL_INTERFACE');
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name,
business_group_id,
set_of_books_id
FROM psp_payroll_controls
WHERE status_code = 'I'
AND source_type <> 'A'
AND run_id = g_run_id
AND (phase is null or
phase in ('GMS_Tie_Back', 'GL_Tie_Back')); --- added for 2444657
SELECT ppl.person_id,
ppl.assignment_id,
nvl(pos.project_id,
nvl(psl.project_id,
nvl(pod.project_id,
nvl(pea.project_id,
pdls.project_id)))) project_id,
nvl(pos.expenditure_organization_id,
nvl(psl.expenditure_organization_id,
nvl(pod.expenditure_organization_id,
nvl(pea.expenditure_organization_id,
pdls.expenditure_organization_id)))) expenditure_organization_id,
nvl(pdl.suspense_auto_exp_type, --- added for 5080403
nvl(pos.expenditure_type,
nvl(pdl.auto_expenditure_type, --- added for 2663344
nvl(psl.expenditure_type,
nvl(pod.expenditure_type,
nvl(pea.expenditure_type,
pdls.expenditure_type)))))) expenditure_type,
nvl(pos.task_id,
nvl(psl.task_id,
nvl(pod.task_id,
nvl(pea.task_id,
pdls.task_id)))) task_id,
nvl(pos.award_id,
nvl(psl.award_id,
nvl(pod.award_id,
nvl(pea.award_id,
pdls.award_id)))) award_id,
ppl.dr_cr_flag,
pdl.effective_date,
nvl(ppc.gl_posting_override_date,ppl.accounting_date) accounting_date, --- added for 3108109
nvl(ppc.exchange_rate_type,ppl.exchange_rate_type) exchange_rate_type, --- added for 3108109
pdl.distribution_amount,
pdl.distribution_line_id distribution_line_id,
pdl.auto_expenditure_type,
'D' tab_flag,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute_category, pos.attribute_category), NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute1, pos.attribute8), NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute2, pos.attribute8), NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute3, pos.attribute8), NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute4, pos.attribute8), NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute5, pos.attribute8), NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute6, pos.attribute8), NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute7, pos.attribute8), NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute8, pos.attribute8), NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute9, pos.attribute9), NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute10, pos.attribute10), NULL) attribute10
---decode(pdl.suspense_org_account_id, NULL, 'N', 'Y') Suspense_Flag uncommented for 2663344
FROM psp_schedule_lines psl,
psp_organization_accounts pod,
psp_organization_accounts pos,
psp_element_type_accounts pea,
psp_default_labor_schedules pdls,
psp_payroll_controls ppc,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_distribution_lines pdl
WHERE pdl.status_code = 'N'
AND pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
AND ppsl.payroll_line_id = ppl.payroll_line_id
AND ppl.payroll_control_id = ppc.payroll_control_id
AND pdl.schedule_line_id = psl.schedule_line_id(+)
AND pdl.default_org_account_id = pod.organization_account_id(+)
AND pdl.element_account_id = pea.element_account_id(+)
AND pdl.org_schedule_id = pdls.org_schedule_id(+)
AND pdl.suspense_org_account_id = pos.organization_account_id(+)
AND pdl.gl_project_flag = 'P'
AND ppc.business_group_id = p_business_group_id
AND ppc.set_of_books_id = p_set_of_books_id
AND ppc.payroll_control_id = p_payroll_control_id
ANd pdl.cap_excess_project_id is null
UNION
SELECT ppg.person_id,
ppg.assignment_id,
nvl(pos.project_id,
ppg.project_id) project_id,
nvl(pos.expenditure_organization_id,
ppg.expenditure_organization_id) expenditure_organization_id,
nvl(ppg.suspense_auto_exp_type, --- 5080403
nvl(pos.expenditure_type,
ppg.expenditure_type)) expenditure_type,
nvl(pos.task_id,
ppg.task_id) task_id,
nvl(pos.award_id,
ppg.award_id) award_id,
ppg.dr_cr_flag,
ppg.effective_date,
ppc.gl_posting_override_date accounting_date, -- for 3108109
ppc.exchange_rate_type, -- added for 3108109
ppg.distribution_amount,
ppg.pre_gen_dist_line_id distribution_line_id,
NULL, -- Place holder for autopop details
'P' tab_flag,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute_category, pos.attribute_category), NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute1, pos.attribute8), NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute2, pos.attribute8), NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute3, pos.attribute8), NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute4, pos.attribute8), NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute5, pos.attribute8), NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute6, pos.attribute8), NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute7, pos.attribute8), NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute8, pos.attribute8), NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute9, pos.attribute9), NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute10, pos.attribute10), NULL) attribute10
---decode(ppg.suspense_org_Account_id, NULL, 'N', 'Y') Suspense_Flag commented for 2663344
FROM psp_pre_gen_dist_lines ppg,
psp_organization_accounts pos,
psp_payroll_controls ppc
WHERE ppg.status_code = 'N'
/* changed following condn. First Pass can have suspense: Bug 2007521 */
AND ((ppg.gl_code_combination_id IS NULL and pos.gl_code_combination_id is null) OR
pos.project_id is not null)
AND ppc.payroll_control_id = p_payroll_control_id --- 3108109
AND ppg.suspense_org_account_id = pos.organization_account_id(+)
AND ppg.payroll_control_id = p_payroll_control_id
AND ppg.set_of_books_id = p_set_of_books_id
AND ppg.payroll_control_id = p_payroll_control_id
union
SELECT ppl.person_id,
ppl.assignment_id,
nvl(pos.project_id, pdl.cap_excess_project_id) project_id,
nvl(pos.expenditure_organization_id, pdl.cap_excess_exp_org_id) expenditure_organization_id,
nvl(pos.expenditure_type, nvl(pdl.auto_expenditure_type, pdl.cap_excess_exp_type)) expenditure_type,
nvl(pos.task_id, pdl.cap_excess_task_id) task_id,
nvl(pos.award_id, pdl.cap_excess_award_id) award_id,
ppl.dr_cr_flag,
pdl.effective_date,
nvl(ppc.gl_posting_override_date,ppl.accounting_date) accounting_date, --- added for 3108109
nvl(ppc.exchange_rate_type,ppl.exchange_rate_type) exchange_rate_type, --- added for 3108109
pdl.distribution_amount,
pdl.distribution_line_id distribution_line_id,
pdl.auto_expenditure_type,
'D' tab_flag,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute_category, pos.attribute_category), NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute1, pos.attribute8), NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute2, pos.attribute8), NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute3, pos.attribute8), NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute4, pos.attribute8), NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute5, pos.attribute8), NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute6, pos.attribute8), NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute7, pos.attribute8), NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute8, pos.attribute8), NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute9, pos.attribute9), NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute10, pos.attribute10), NULL) attribute10
---decode(pdl.suspense_org_account_id, NULL, 'N', 'Y') Suspense_Flag uncommented for 2663344
FROM psp_organization_accounts pos,
psp_payroll_controls ppc,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_distribution_lines pdl
WHERE pdl.status_code = 'N'
AND pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
AND ppsl.payroll_line_id = ppl.payroll_line_id
AND ppl.payroll_control_id = ppc.payroll_control_id
AND pdl.suspense_org_account_id = pos.organization_account_id(+)
AND pdl.gl_project_flag = 'P'
AND ppc.business_group_id = p_business_group_id
AND ppc.set_of_books_id = p_set_of_books_id
AND ppc.payroll_control_id = p_payroll_control_id
AND pdl.cap_excess_project_id is not null
ORDER BY 1,2,3,4,5,6,7,8,10,11,16,17,18,19,20,21,22,23,24,25,26,9;
SELECT end_date
INTO l_period_end_date
FROM per_time_periods
WHERE time_period_id = payroll_control_rec.time_period_id;
update psp_payroll_controls
set phase = 'Summarize_GMS_Lines' ----2444657: Replaced NULL
where payroll_control_id = payroll_control_rec.payroll_control_id;
-- insert into summary lines
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling insert_into_summary_lines');
insert_into_summary_lines(
l_summary_line_id,
l_person_id,
l_assignment_id,
payroll_control_rec.time_period_id,
l_effective_date,
nvl(l_accounting_date,l_period_end_date), --- added for 3108109
l_exchange_rate_type,
payroll_control_rec.source_type,
payroll_control_rec.payroll_source_code,
payroll_control_rec.set_of_books_id,
NULL,
l_project_id,
l_expenditure_organization_id,
l_expenditure_type,
l_task_id,
l_award_id,
l_summary_amount,
l_dr_cr_flag,
'N',
payroll_control_rec.batch_name,
payroll_control_rec.payroll_control_id,
payroll_control_rec.business_group_id,
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status,
l_org_id -- R12 MOAC Uptake
);
UPDATE psp_distribution_lines
SET summary_line_id = l_summary_line_id WHERE distribution_line_id = l_dist_line_id;
UPDATE psp_pre_gen_dist_lines
SET summary_line_id = l_summary_line_id WHERE pre_gen_dist_line_id = l_dist_line_id;
dist_line_id.delete;
insert_into_summary_lines(
l_summary_line_id,
l_person_id,
l_assignment_id,
payroll_control_rec.time_period_id,
l_effective_date,
nvl(l_accounting_date,l_period_end_date), --- 3108109
l_exchange_rate_type,
payroll_control_rec.source_type,
payroll_control_rec.payroll_source_code,
payroll_control_rec.set_of_books_id,
NULL,
l_project_id,
l_expenditure_organization_id,
l_expenditure_type,
l_task_id,
l_award_id,
l_summary_amount,
l_dr_cr_flag,
'N',
payroll_control_rec.batch_name,
payroll_control_rec.payroll_control_id,
payroll_control_rec.business_group_id,
l_attribute_category, -- Introduced DFF columns for bug fix 2908859
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_return_status,
l_org_id -- R12 MOAC uptake
);
UPDATE psp_distribution_lines
SET summary_line_id = l_summary_line_id,
status_code = 'N'
WHERE distribution_line_id = l_dist_line_id;
UPDATE psp_pre_gen_dist_lines
SET summary_line_id = l_summary_line_id,
status_code = 'N'
WHERE pre_gen_dist_line_id = l_dist_line_id;
dist_line_id.delete;
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name,
-- Introduced the following currency_code,exchange_rate_type for bug 2916848
Currency_code,
exchange_rate_type,
phase --- added for 2444657
FROM psp_payroll_controls
WHERE status_code = 'I'
AND source_type <> 'A'
AND run_id = g_run_id
AND phase in ('Summarize_GMS_Lines','Submitted_TI_Request');
SELECT psl.summary_line_id,
psl.source_code,
psl.person_id,
psl.assignment_id,
NVL(psl.gms_posting_effective_date,psl.effective_date) effective_date, /* Column modified for Enhancement Employee Assignment with Zero Work Days */
psl.accounting_date, --- added 2 cols for 3108109
psl.exchange_Rate_type,
psl.project_id,
psl.expenditure_organization_id,
psl.expenditure_type,
psl.task_id,
psl.award_id,
psl.summary_amount,
psl.dr_cr_flag,
psl.attribute1, -- Introduced attributes 1, 4 and 5 for bug fix 2908859
psl.attribute2,
psl.attribute3,
psl.attribute4,
psl.attribute5,
psl.attribute6,
psl.attribute7,
psl.attribute8,
psl.attribute9,
psl.attribute10,
org_id
FROM psp_summary_lines psl
WHERE psl.status_code = 'N'
AND psl.gl_code_combination_id IS NULL
AND psl.payroll_control_id = p_payroll_control_id;
SELECT Distinct org_id
FROM psp_summary_lines psl
WHERE psl.status_code = 'N'
AND psl.gl_code_combination_id IS NULL
AND psl.payroll_control_id = p_payroll_control_id;
SELECT transaction_source
INTO l_transaction_source
FROM pa_transaction_sources
WHERE transaction_source = 'OLD';
SELECT transaction_source
INTO l_gms_transaction_source
FROM pa_transaction_sources
WHERE transaction_source = 'GOLD';
SELECT substr(period_name ,1,35),end_date
INTO l_period_name ,l_period_end_date
FROM per_time_periods
WHERE time_period_id = gms_batch_rec.time_period_id;
ORG_ID_TAB.delete;
gms_batch_name_TAB.delete;
req_id_TAB.delete;
call_status_TAB.delete;
SELECT to_char(psp_gms_batch_name_s.nextval)
INTO gms_batch_name_tab(i)
FROM dual;
SELECT to_char(psp_gms_batch_name_s.nextval)
INTO l_gms_batch_name
FROM dual;
-- update psp_summary_lines with gms batch name
FOR I in 1..org_id_tab.count
LOOP
UPDATE psp_summary_lines
SET gms_batch_name = gms_batch_name_tab(i) -- R12 MOAC uptake. changed from l_gms_batch_name
WHERE payroll_control_id = gms_batch_rec.payroll_control_id
AND status_code = 'N'
AND gl_code_combination_id IS NULL
AND org_id = org_id_tab(i); -- R12 MOAC uptake
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_summary_lines set status_code for org_id'||org_id_tab(i)||' to N count: '||sql%rowcount);
SELECT employee_number
INTO l_employee_number
FROM per_people_f
WHERE person_id = gms_interface_rec.person_id
AND gms_interface_rec.effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT name -- Removed SUBSTR for bug fix 2447912
INTO l_org_name
FROM hr_all_organization_units hou
WHERE organization_id = gms_interface_rec.expenditure_organization_id;
SELECT segment1
INTO l_segment1
FROM pa_projects_all
WHERE project_id = gms_interface_rec.project_Id;
SELECT org_id
INTO l_org_id
FROM pa_projects_all
WHERE project_id = gms_interface_rec.project_Id;
SELECT task_number
INTO l_task_number
FROM pa_tasks_expend_v -- Bug : 16391366 (20/03/2013)
WHERE task_id = gms_interface_rec.task_id;
select pa_txn_interface_s.nextval
into l_txn_interface_id
from dual;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling insert_into_pa_interface ');
insert_into_pa_interface(l_txn_interface_id,
l_txn_source,
L_GMS_BATCH_NAME,
L_EXPENDITURE_ENDING_DATE,
L_EMPLOYEE_NUMBER,
L_ORG_NAME,
GMS_INTERFACE_REC.EFFECTIVE_DATE,
L_SEGMENT1,
--L_TASK_NUMBER, -- Commented for Bug : 16591849
GMS_INTERFACE_REC.TASK_ID, -- Added for Bug : 16591849
GMS_INTERFACE_REC.EXPENDITURE_TYPE,
1,
GMS_INTERFACE_REC.SUMMARY_AMOUNT,
L_EXPENDITURE_COMMENT,
-- 'P',GMS_INTERFACE_REC.SUMMARY_LINE_ID,GMS_INTERFACE_REC.AWARD_ID,
-- Award details are populated into GMS_INT
'P',
GMS_INTERFACE_REC.SUMMARY_LINE_ID,
GMS_INTERFACE_REC.ORG_ID,
gms_batch_rec.CURRENCY_CODE ,
GMS_INTERFACE_REC.SUMMARY_AMOUNT,
gms_interface_rec.attribute1, --Changed NULL vale for Attribute1 to interface value for bug fix 2908859
GMS_INTERFACE_REC.ATTRIBUTE2,
GMS_INTERFACE_REC.ATTRIBUTE3,
GMS_INTERFACE_REC.ATTRIBUTE4,
GMS_INTERFACE_REC.ATTRIBUTE5, -- Introduced attributes 4 and 5 for bug fix 2908859
GMS_INTERFACE_REC.ATTRIBUTE6,
GMS_INTERFACE_REC.ATTRIBUTE7,
GMS_INTERFACE_REC.ATTRIBUTE8,
GMS_INTERFACE_REC.ATTRIBUTE9,
GMS_INTERFACE_REC.ATTRIBUTE10,
GMS_INTERFACE_REC.EXCHANGE_RATE_TYPE,
gms_interface_rec.accounting_date,-- Introduced for bug 2916848 Ilo Ehn Mrc
p_business_group_id,--Introduced for bug 2935850
L_RETURN_STATUS);
insert_into_pa_interface(l_txn_interface_id,
l_txn_source,
L_GMS_BATCH_NAME,
L_EXPENDITURE_ENDING_DATE,
L_EMPLOYEE_NUMBER,
L_ORG_NAME,
GMS_INTERFACE_REC.EFFECTIVE_DATE,
L_SEGMENT1,
--L_TASK_NUMBER, -- Commented for Bug 16591849
GMS_INTERFACE_REC.TASK_ID, -- Added for Bug 16591849
GMS_INTERFACE_REC.EXPENDITURE_TYPE,
GMS_INTERFACE_REC.SUMMARY_AMOUNT,
0,
L_EXPENDITURE_COMMENT,
'P',
GMS_INTERFACE_REC.SUMMARY_LINE_ID,
GMS_INTERFACE_REC.ORG_ID,
gms_batch_rec.CURRENCY_CODE ,
GMS_INTERFACE_REC.SUMMARY_AMOUNT,
gms_interface_rec.attribute1, --Changed NULL vale for Attribute1 to interface value for bug fix 2908859
GMS_INTERFACE_REC.ATTRIBUTE2,
GMS_INTERFACE_REC.ATTRIBUTE3,
GMS_INTERFACE_REC.ATTRIBUTE4,
GMS_INTERFACE_REC.ATTRIBUTE5, -- Introduced attributes 4 and 5 for bug fix 2908859
GMS_INTERFACE_REC.ATTRIBUTE6,
GMS_INTERFACE_REC.ATTRIBUTE7,
GMS_INTERFACE_REC.ATTRIBUTE8,
GMS_INTERFACE_REC.ATTRIBUTE9,
GMS_INTERFACE_REC.ATTRIBUTE10,
GMS_INTERFACE_REC.EXCHANGE_RATE_TYPE,
gms_interface_rec.accounting_date,-- Introduced for bug 2916848 Ilo Ehn Mrc
p_business_group_id,--Introduced for bug 2935850
L_RETURN_STATUS);
select gms_batch_name
into gms_batch_name_tab(i) -- change
from psp_summary_lines
where payroll_control_id = gms_batch_rec.payroll_control_id
and org_id = org_id_tab(i)
and project_id is not null
and rownum = 1;
update psp_payroll_controls
set phase = 'Submitted_TI_Request'
WHERE payroll_control_id = gms_batch_rec.payroll_control_id;
SELECT summary_line_id,
dr_cr_flag,summary_amount
FROM psp_summary_lines
WHERE gms_batch_name = p_gms_batch_name;
select transaction_rejection_code,
orig_transaction_reference,
transaction_status_code,
expenditure_ending_date, -- added following five columns for 2445196
expenditure_id,
interface_id,
expenditure_item_id,
txn_interface_id
FROM pa_transaction_interface_all
WHERE batch_name = p_gms_batch_name
AND transaction_source = p_txn_source;
SELECT pdl.rowid,
pdl.distribution_line_id line_id,
pdl.distribution_date,
pdl.suspense_org_account_id
FROM psp_distribution_lines pdl
WHERE pdl.summary_line_id = p_summary_line_id
UNION
SELECT ppgd.rowid,
ppgd.pre_gen_dist_line_id line_id,
ppgd.distribution_date,
ppgd.suspense_org_account_id
FROM psp_pre_gen_dist_lines ppgd
WHERE ppgd.summary_line_id = p_summary_line_id;
SELECT hou.organization_id, hou.name
FROM hr_all_organization_units hou, psp_organization_accounts poa
WHERE hou.organization_id = poa.organization_id
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND poa.organization_account_id = p_org_id;
SELECT hou.organization_id, hou.name
FROM hr_all_organization_units hou,
per_assignments_f paf,
psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_distribution_lines pdl
WHERE paf.assignment_id = ppl.assignment_id
AND hou.organization_id = paf.organization_id
AND pdl.distribution_line_id = p_line_id
AND ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
AND ppl.payroll_line_id = ppsl.payroll_line_id
AND pdl.distribution_date between paf.effective_start_date and paf.effective_end_date
UNION
SELECT hou.organization_id, hou.name
FROM hr_all_organization_units hou,
per_assignments_f paf,
psp_pre_gen_dist_lines ppgdl
WHERE paf.assignment_id = ppgdl.assignment_id
AND hou.organization_id = paf.organization_id
AND ppgdl.pre_gen_dist_line_id = p_line_id
AND ppgdl.distribution_date between paf.effective_start_date and paf.effective_end_date;
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.award_id,
poa.task_id, -- Line added by pvelamur for bug fix 897553
poa.expenditure_organization_id,
poa.expenditure_type
FROM psp_organization_accounts poa
WHERE poa.organization_id = p_organization_id
AND poa.account_type_code = 'S'
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND p_distribution_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_distribution_date);
SELECT poa.organization_account_id,
poa.gl_code_combination_id,
poa.project_id,
poa.award_id,
poa.task_id, -- Line added by pvelamur for bugfix 897553
poa.expenditure_organization_id,
poa.expenditure_type
FROM psp_organization_accounts poa
WHERE
/* poa.account_type_code = 'G'
AND poa.business_group_id = p_business_group_id
AND poa.set_of_books_id = p_set_of_books_id
AND p_distribution_date BETWEEN poa.start_date_active AND
nvl(poa.end_date_active,p_distribution_date);Bug 2056877 */
select ppl.element_type_id,
ppl.assignment_id,
ppl.person_id
from psp_payroll_lines ppl,
psp_payroll_sub_lines ppsl,
psp_distribution_lines pdl
where pdl.distribution_line_id = x_line_id
and pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
and ppsl.payroll_line_id = ppl.payroll_line_id
union all
select ppg.element_type_id,
ppg.assignment_id,
ppg.person_id
from psp_pre_gen_dist_lines ppg
where pre_gen_dist_line_id = x_line_id;
select ppf.full_name,
paf.assignment_number,
pet.element_name,
hou.name
from per_all_people_f ppf,
per_all_assignments_f paf,
pay_element_types_f pet,
hr_all_organization_units hou
where ppf.person_id = l_person_id
and l_distribution_date between ppf.effective_start_date and ppf.effective_end_date
and paf.assignment_id = l_assignment_id
and l_distribution_date between paf.effective_start_date and paf.effective_end_date
and pet.element_type_id = l_element_type_id
and l_distribution_date between pet.effective_start_date and pet.effective_end_date
and hou.organization_id = paf.organization_id;
Select SUMMARY_LINE_ID, to_CHAR(SUMMARY_LINE_ID) , GMS_BATCH_NAME
From PSP_SUMMARY_LINES PSL
where PSL.GMS_BATCH_NAME = p_gms_batch_name;
select count(*), transaction_status_code
from pa_transaction_interface_all
where batch_name = p_gms_batch_name
and transaction_source = p_txn_source
and transaction_status_code in ('P', 'I')
group by transaction_status_code ;
delete from pa_transaction_interface_all
where batch_name = p_gms_batch_name
and transaction_source = p_txn_source;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from pa_transaction_interface_all count:'||sql%rowcount);
delete from gms_transaction_interface_all
where batch_name = p_gms_batch_name
and transaction_source = 'GOLD';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from gms_transaction_interface_all count:'||sql%rowcount);
delete from psp_summary_lines
where gms_batch_name = to_number(p_gms_batch_name)
and payroll_control_id = p_payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from psp_summary_lines count:'||sql%rowcount);
SELECT count(*)
INTO l_cnt_gms_interface
FROM pa_transaction_interface_all
WHERE batch_name = p_gms_batch_name
AND transaction_source = p_txn_source
AND transaction_status_code in ('R', 'PO', 'PI', 'PR');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' selected pa_transaction_interface_all with status_code in R,PO,PI,PR l_cnt_gms_interface :'||l_cnt_gms_interface);
UPDATE psp_payroll_controls
SET phase = 'GL_Tie_Back'
WHERE payroll_control_id = p_payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' update psp_payroll_controls SQL%ROWCOUNT :'||SQL%ROWCOUNT);
UPDATE psp_summary_lines
SET interface_status = l_trx_reject_code, status_code = 'R',
expenditure_ending_date = l_expenditure_ending_date, -- added 5 fields for 2445196
expenditure_id = l_expenditure_id, interface_id=l_interface_id,
expenditure_item_id=l_expenditure_item_id, txn_interface_id=l_txn_interface_id
WHERE summary_line_id = to_number(l_orig_trx_reference);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_summary_lines for rejected records count: '||sql%rowcount);
UPDATE psp_summary_lines
SET interface_status = l_trx_reject_code, status_code = 'A',
expenditure_ending_date = l_expenditure_ending_date, -- added 5 fields for 2445196
expenditure_id = l_expenditure_id, interface_id=l_interface_id,
expenditure_item_id=l_expenditure_item_id, txn_interface_id=l_txn_interface_id
WHERE summary_line_id = to_number(l_orig_trx_reference);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_summary_lines for accepted records count: '||sql%rowcount);
SELECT summary_amount,dr_cr_flag
INTO l_summary_amount,l_dr_cr_flag
FROM psp_summary_lines
WHERE summary_line_id = to_number(l_orig_trx_reference) ;
SELECT payroll_action_type, effective_date
INTO l_pay_action_type, l_effective_date
FROM psp_payroll_lines
WHERE payroll_control_id = p_payroll_control_id
and payroll_line_id = (select payroll_line_id from psp_payroll_sub_lines
where payroll_sub_line_id = (select payroll_sub_line_id
from psp_distribution_lines
where distribution_line_id = x_line_id));
UPDATE psp_distribution_lines
SET status_code = 'A'
WHERE rowid = l_rowid;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_distribution_lines set status_code A count: '||sql%rowcount);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code in psp_distribution_lines to A for rowid :'||l_rowid);
INSERT INTO psp_distribution_lines_history
(distribution_line_id,payroll_sub_line_id,distribution_date,
effective_date,distribution_amount,status_code,suspense_reason_code,
effort_report_id,version_num,schedule_line_id,summary_line_id,
default_org_account_id,suspense_org_account_id,
element_account_id,org_schedule_id,user_defined_field,
default_reason_code,reversal_entry_flag,gl_project_flag,
auto_expenditure_type, business_group_id, set_of_books_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
cap_excess_glccid, cap_excess_award_id, cap_excess_task_id,
cap_excess_project_id, cap_excess_exp_type, cap_excess_exp_org_id,
funding_source_code, annual_salary_cap, cap_excess_dist_line_id,
suspense_auto_exp_type, suspense_auto_glccid, adj_account_flag)
SELECT distribution_line_id,payroll_sub_line_id,distribution_date,
effective_date,distribution_amount,status_code,suspense_reason_code,
effort_report_id,version_num,schedule_line_id,summary_line_id,
default_org_account_id,suspense_org_account_id,
element_account_id,org_schedule_id,user_defined_field,
default_reason_code,reversal_entry_flag,gl_project_flag,
auto_expenditure_type, business_group_id, set_of_books_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
cap_excess_glccid, cap_excess_award_id, cap_excess_task_id,
cap_excess_project_id, cap_excess_exp_type, cap_excess_exp_org_id,
funding_source_code, annual_salary_cap, cap_excess_dist_line_id,
suspense_auto_exp_type, suspense_auto_glccid, adj_account_flag
FROM psp_distribution_lines
WHERE status_code = 'A'
AND summary_line_id = to_number(l_orig_trx_reference);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into psp_distribution_lines_history for summary_line_id: '||l_orig_trx_reference
||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' after insert into dist lines');
hr_utility.trace('after insert into dist lines');
DELETE FROM psp_distribution_lines
WHERE status_code = 'A'
AND summary_line_id = to_number(l_orig_trx_reference);
UPDATE psp_pre_gen_dist_lines
SET status_code = 'A'
WHERE rowid = l_rowid;
INSERT INTO psp_pre_gen_dist_lines_history
(pre_gen_dist_line_id,distribution_interface_id,person_id,assignment_id,
element_type_id,distribution_date,effective_date,distribution_amount,
dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
batch_name,status_code,set_of_books_id,
gl_code_combination_id,project_id,expenditure_organization_id,
expenditure_type,task_id,award_id,suspense_reason_code,
effort_report_id,version_num,summary_line_id,suspense_org_account_id,
user_defined_field,reversal_entry_flag, business_group_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
suspense_auto_exp_type, suspense_auto_glccid)
SELECT pre_gen_dist_line_id,distribution_interface_id,person_id,assignment_id,
element_type_id,distribution_date,effective_date,distribution_amount,
dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
batch_name,status_code,set_of_books_id,
gl_code_combination_id,project_id,expenditure_organization_id,
expenditure_type,task_id,award_id,suspense_reason_code,
effort_report_id,version_num,summary_line_id,suspense_org_account_id,
user_defined_field,reversal_entry_flag, business_group_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
suspense_auto_exp_type, suspense_auto_glccid
FROM psp_pre_gen_dist_lines
WHERE status_code = 'A'
AND summary_line_id = to_number(l_orig_trx_reference);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into psp_pre_gen_distribution_lines_history for summary_line_id: '||l_orig_trx_reference
||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' after insert into pregen lines');
hr_utility.trace('after insert into pregen lines');
DELETE FROM psp_pre_gen_dist_lines
WHERE status_code = 'A'
AND summary_line_id = to_number(l_orig_trx_reference);
UPDATE psp_distribution_lines
SET suspense_reason_code = l_trx_reject_code,
status_code = 'N'
WHERE rowid = l_rowid;
UPDATE psp_pre_gen_dist_lines
SET suspense_reason_code = l_trx_reject_code,
status_code = 'N'
WHERE rowid = l_rowid;
UPDATE psp_distribution_lines
SET pre_distribution_run_flag = gl_project_flag
WHERE rowid = l_rowid;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_distribution_lines for rowid: '||l_rowid||' with l_effective_date'||l_effective_date);
UPDATE psp_distribution_lines
SET suspense_org_account_id = l_organization_account_id,
suspense_reason_code = 'ST:' || l_trx_reject_code,
gl_project_flag = l_gl_project_flag,
status_code = 'N',
-- effective_date = l_effective_date, ---uncommented this line for Bug 2663344 -- Commented for Bug 10181561
suspense_auto_glccid = l_new_glccid, --- added suspense_auto for 5080403
suspense_auto_exp_type = l_new_expenditure_type
WHERE rowid = l_rowid;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_distribution_lines set pre_distribution_run_flag count: '||sql%rowcount);
UPDATE psp_pre_gen_dist_lines
SET suspense_org_account_id = l_organization_account_id,
suspense_reason_code = 'ST:' || l_trx_reject_code,
status_code = 'N',
-- effective_date = l_effective_date, ---uncommented this line for Bug 2663344 -- Commented for Bug 10181561
suspense_auto_glccid = l_new_glccid,
suspense_auto_exp_type = l_new_expenditure_type
WHERE rowid = l_rowid;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_pre_gen_dist_lines for rowid: '||l_rowid||' with l_effective_date'||l_effective_date||' count: '||sql%rowcount);
hr_utility.trace('update control record');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' update control record');
UPDATE psp_payroll_controls
SET ogm_dr_amount = nvl(ogm_dr_amount,0) + l_dr_summary_amount,
ogm_cr_amount = nvl(ogm_cr_amount,0) + l_cr_summary_amount
WHERE payroll_control_id = p_payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_payroll_controls count: '||sql%rowcount);
DELETE pa_transaction_interface_all
WHERE transaction_source = p_txn_source
AND batch_name = p_gms_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from pa_transaction_interface_all count: '||sql%rowcount);
DELETE gms_transaction_interface_all
WHERE transaction_source = p_txn_source
AND batch_name = p_gms_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from gms_transaction_interface_all count: '||sql%rowcount);
UPDATE psp_distribution_lines
SET suspense_org_account_id = NULL,
suspense_reason_code = NULL,
gl_project_flag = pre_distribution_run_flag,
effective_date = decode(pre_distribution_run_flag,'G',
p_period_end_date,distribution_date)
WHERE suspense_reason_code like 'ST:%'
AND summary_line_id
IN ( SELECT summary_line_id
FROM psp_summary_lines
WHERE payroll_control_id = p_payroll_control_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_distribution_lines count: '||sql%rowcount);
UPDATE psp_pre_gen_dist_lines
SET suspense_org_account_id = NULL,
suspense_reason_code = NULL,
effective_date = decode(NVL(gl_code_combination_id,-999),gl_code_combination_id,
p_period_end_date,distribution_date)
WHERE suspense_reason_code like 'ST:%'
AND summary_line_id
IN (SELECT summary_line_id
FROM psp_summary_lines
WHERE payroll_control_id = p_payroll_control_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_pre_gen_dist_lines count: '||sql%rowcount);
UPDATE psp_distribution_lines
SET suspense_org_account_id = NULL,
suspense_reason_code = NULL
WHERE suspense_reason_code like 'ST:%'
AND summary_line_id
IN( SELECT summary_line_id
FROM psp_summary_lines
WHERE payroll_control_id = p_payroll_control_id);
UPDATE psp_pre_gen_dist_lines
SET suspense_org_account_id = NULL,
suspense_reason_code = NULL
WHERE suspense_reason_code like 'ST:%'
AND summary_line_id
IN( SELECT summary_line_id
FROM psp_summary_lines
WHERE payroll_control_id = p_payroll_control_id);
UPDATE PSP_SUMMARY_LINES PSL
Set (PSL.STATUS_CODE, PSL.EXPENDITURE_ENDING_DATE,PSL.EXPENDITURE_ID,
PSL.INTERFACE_ID,PSL.EXPENDITURE_ITEM_ID,PSL.TXN_INTERFACE_ID) =
( SELECT 'A', PTXN.EXPENDITURE_ENDING_DATE,PTXN.EXPENDITURE_ID, PTXN.INTERFACE_ID,
PTXN.EXPENDITURE_ITEM_ID,PTXN.TXN_INTERFACE_ID
FROM PA_TRANSACTION_INTERFACE_ALL PTXN
WHERE PTXN.TRANSACTION_SOURCE = p_txn_source
AND PTXN.ORIG_TRANSACTION_REFERENCE= SUMMARY_LINES_REC.L_SUMMARY_LINE_ID_CHAR(i)
AND PTXN.BATCH_NAME = SUMMARY_LINES_REC.L_GMS_BATCH_NAME(i)
)
WHERE --GMS_BATCH_NAME = SUMMARY_LINES_REC.L_GMS_BATCH_NAME(i) AND
PSL.SUMMARY_LINE_ID = SUMMARY_LINES_REC.L_SUMMARY_LINE_ID(i);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_summary_lines table SQL%ROWCOUNT: '||SQL%ROWCOUNT);
UPDATE psp_summary_lines PSL
SET (PSL.status_code, PSL.expenditure_ending_date,PSL.expenditure_id,
PSL.interface_id,PSL.expenditure_item_id,PSL.txn_interface_id) =
(select 'A', PTXN.expenditure_ending_date,PTXN.expenditure_id,
PTXN.interface_id,PTXN.expenditure_item_id,PTXN.txn_interface_id
from pa_transaction_interface_all PTXN
where PTXN.transaction_source = p_txn_source
and PTXN.orig_transaction_reference= to_char(PSL.summary_line_id)
and PTXN.batch_name = p_gms_batch_name)
WHERE gms_batch_name = p_gms_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_distribution_lines for summary_line_id'||l_summary_line_id);
UPDATE psp_distribution_lines
SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_distribution_lines set status_code to A count: '||sql%rowcount);
INSERT INTO psp_distribution_lines_history
(distribution_line_id,payroll_sub_line_id,distribution_date,
effective_date,distribution_amount,status_code,suspense_reason_code,
effort_report_id,version_num,schedule_line_id,summary_line_id,
default_org_account_id,suspense_org_account_id,
element_account_id,org_schedule_id,user_defined_field,
default_reason_code,reversal_entry_flag,gl_project_flag,
auto_expenditure_type, business_group_id, set_of_books_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
cap_excess_glccid, cap_excess_award_id, cap_excess_task_id,
cap_excess_project_id, cap_excess_exp_type, cap_excess_exp_org_id,
funding_source_code, annual_salary_cap, cap_excess_dist_line_id,
suspense_auto_exp_type, suspense_auto_glccid, adj_account_flag)
SELECT distribution_line_id,payroll_sub_line_id,distribution_date,
effective_date,distribution_amount,status_code,suspense_reason_code,
effort_report_id,version_num,schedule_line_id,summary_line_id,
default_org_account_id,suspense_org_account_id,
element_account_id,org_schedule_id,user_defined_field,
default_reason_code,reversal_entry_flag,gl_project_flag,
auto_expenditure_type, business_group_id, set_of_books_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
cap_excess_glccid, cap_excess_award_id, cap_excess_task_id,
cap_excess_project_id, cap_excess_exp_type, cap_excess_exp_org_id,
funding_source_code, annual_salary_cap, cap_excess_dist_line_id,
suspense_auto_exp_type, suspense_auto_glccid, adj_account_flag
FROM psp_distribution_lines
WHERE status_code = 'A'
AND summary_line_id = l_summary_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into psp_distribution_lines_history for summary_line_id: '||l_orig_trx_reference
||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
DELETE FROM psp_distribution_lines
WHERE status_code = 'A'
AND summary_line_id = l_summary_line_id;
UPDATE psp_pre_gen_dist_lines
SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_pre_gen_distribution_lines for summary_line_id'||l_summary_line_id);
INSERT INTO psp_pre_gen_dist_lines_history
(pre_gen_dist_line_id,distribution_interface_id,person_id,assignment_id,
element_type_id,distribution_date,effective_date,distribution_amount,
dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
batch_name,status_code,set_of_books_id,
gl_code_combination_id,project_id,expenditure_organization_id,
expenditure_type,task_id,award_id,suspense_reason_code,
effort_report_id,version_num,summary_line_id,suspense_org_account_id,
user_defined_field,reversal_entry_flag, business_group_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
suspense_auto_exp_type, suspense_auto_glccid)
SELECT pre_gen_dist_line_id,distribution_interface_id,person_id,assignment_id,
element_type_id,distribution_date,effective_date,distribution_amount,
dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
batch_name,status_code,set_of_books_id,
gl_code_combination_id,project_id,expenditure_organization_id,
expenditure_type,task_id,award_id,suspense_reason_code,
effort_report_id,version_num,summary_line_id,suspense_org_account_id,
user_defined_field,reversal_entry_flag, business_group_id,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
suspense_auto_exp_type, suspense_auto_glccid
FROM psp_pre_gen_dist_lines
WHERE status_code = 'A'
AND summary_line_id = l_summary_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into psp_pre_gen_distribution_lines_history for summary_line_id: '||l_orig_trx_reference
||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
DELETE FROM psp_pre_gen_dist_lines
WHERE status_code = 'A'
AND summary_line_id = l_summary_line_id;
UPDATE psp_payroll_controls
SET ogm_dr_amount = nvl(ogm_dr_amount,0) + l_dr_summary_amount,
ogm_cr_amount = nvl(ogm_cr_amount,0) + l_cr_summary_amount
WHERE payroll_control_id = p_payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_payroll_controls count: '||sql%rowcount);
DELETE pa_transaction_interface_all
WHERE transaction_source = p_txn_source
AND batch_name = p_gms_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' delete from pa_transaction_interface_all for batch_name: '||p_gms_batch_name);
DELETE gms_transaction_interface_all
WHERE transaction_source = p_txn_source
AND batch_name = p_gms_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' delete from gms_transaction_interface_all for batch_name: '||p_gms_batch_name);
delete pa_transaction_interface_all
where transaction_source = p_txn_source
and batch_name = p_gms_batch_name;
delete gms_transaction_interface_all
where transaction_source = p_txn_source
and batch_name = p_gms_batch_name;
PROCEDURE insert_into_pa_interface(
P_TRANSACTION_INTERFACE_ID IN NUMBER,
P_TRANSACTION_SOURCE IN VARCHAR2,
P_BATCH_NAME IN VARCHAR2,
P_EXPENDITURE_ENDING_DATE IN DATE,
P_EMPLOYEE_NUMBER IN VARCHAR2,
P_ORGANIZATION_NAME IN VARCHAR2,
P_EXPENDITURE_ITEM_DATE IN DATE,
P_PROJECT_NUMBER IN VARCHAR2,
--P_TASK_NUMBER IN VARCHAR2, -- Commented for Bug 16591849
P_TASK_ID IN NUMBER, -- Added for Bug 16591849
P_EXPENDITURE_TYPE IN VARCHAR2,
P_QUANTITY IN NUMBER,
P_RAW_COST IN NUMBER,
P_EXPENDITURE_COMMENT IN VARCHAR2,
P_TRANSACTION_STATUS_CODE IN VARCHAR2,
P_ORIG_TRANSACTION_REFERENCE IN VARCHAR2,
P_ORG_ID IN NUMBER,
P_DENOM_CURRENCY_CODE IN VARCHAR2,
P_DENOM_RAW_COST IN NUMBER,
P_ATTRIBUTE1 IN VARCHAR2,
P_ATTRIBUTE2 IN VARCHAR2,
P_ATTRIBUTE3 IN VARCHAR2,
P_ATTRIBUTE4 IN VARCHAR2, -- Introduced attributes 4 and 5 for bug fix 2908859
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_ACCT_RATE_TYPE IN VARCHAR2, -- Introduced for bug fix 2916848
P_ACCT_RATE_DATE IN DATE, -- Introduced for bug fix 2916848
P_PERSON_BUSINESS_GROUP_ID IN NUMBER, -- Introduced for bug 2935850
P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
l_unmatched_nve_txn_flag char(1); -- Bug 8984069
INSERT INTO PA_TRANSACTION_INTERFACE_ALL(
TXN_INTERFACE_ID,
TRANSACTION_SOURCE,
BATCH_NAME,
EXPENDITURE_ENDING_DATE,
EMPLOYEE_NUMBER,
ORGANIZATION_NAME,
EXPENDITURE_ITEM_DATE,
PROJECT_NUMBER,
--TASK_NUMBER, -- Commented for Bug 16591849
TASK_ID, -- Added for Bug 16591849
EXPENDITURE_TYPE,
QUANTITY,
RAW_COST,
EXPENDITURE_COMMENT,
TRANSACTION_STATUS_CODE,
ORIG_TRANSACTION_REFERENCE,
ORG_ID,
DENOM_CURRENCY_CODE,
DENOM_RAW_COST,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4, -- Introduced attributes 4 and 5 for bug fix 2908859
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
PERSON_BUSINESS_GROUP_ID,
-- Introduced the following columns for bug fix 2916848
ACCT_RATE_TYPE,
ACCT_RATE_DATE,
-- Bug 8984069
UNMATCHED_NEGATIVE_TXN_FLAG)
VALUES(
P_TRANSACTION_INTERFACE_ID,
P_TRANSACTION_SOURCE,
P_BATCH_NAME,
P_EXPENDITURE_ENDING_DATE,
P_EMPLOYEE_NUMBER,
P_ORGANIZATION_NAME,
P_EXPENDITURE_ITEM_DATE,
P_PROJECT_NUMBER,
--P_TASK_NUMBER, -- Commented For Bug 16591849
P_TASK_ID, -- Added For Bug 16591849
P_EXPENDITURE_TYPE,
P_QUANTITY,
P_RAW_COST,
P_EXPENDITURE_COMMENT,
P_TRANSACTION_STATUS_CODE,
P_ORIG_TRANSACTION_REFERENCE,
P_ORG_ID,
P_DENOM_CURRENCY_CODE,
P_DENOM_RAW_COST,
P_ATTRIBUTE1,
P_ATTRIBUTE2,
P_ATTRIBUTE3,
P_ATTRIBUTE4, -- Introduced attributes 4 and 5 for bug fix 2908859
P_ATTRIBUTE5,
P_ATTRIBUTE6,
P_ATTRIBUTE7,
P_ATTRIBUTE8,
P_ATTRIBUTE9,
P_ATTRIBUTE10,
P_PERSON_BUSINESS_GROUP_ID,
-- Introduced the following columns for bug fix 2916848
P_ACCT_RATE_TYPE,
DECODE(p_acct_rate_type, NULL, NULL, P_ACCT_RATE_DATE),
-- Bug 8984069
l_unmatched_nve_txn_flag);
g_error_api_path := 'INSERT_INTO_PA_INTERFACE:'||g_error_api_path;
fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','INSERT_INTO_PA_INTERFACE');
Select distinct pth.template_id,
pth.request_id
From psp_payroll_controls ppc,
Psp_report_templates_h pth,
Per_time_periods ptp
Where ppc.run_id = p_run_id
And ppc.time_period_id = ptp.time_period_id
And fnd_date.canonical_to_date(fnd_date.date_to_canonical(pth.parameter_value_3)) >= ptp.start_date
And fnd_date.canonical_to_date(fnd_date.date_to_canonical(pth.parameter_value_2)) <= ptp.end_date;
select nvl(sprcd_tolerance_amt,-999),
nvl(sprcd_tolerance_percent,-999)
from psp_report_templates
where template_id = p_template_id;
Select per.effort_report_id,
per.person_id,
min(pal.summary_line_id) summary_line_id
From psp_adjustment_lines_history pal,
Psp_eff_reports per,
Psp_payroll_controls ppc
Where pal.distribution_date between per.start_date and per.end_date
And per.request_id in ( select -person_id from psp_supercede_persons_gt where person_id < 0)
And ppc.run_id = p_run_id
And pal.person_id = per.person_id
And ppc.payroll_control_id = pal.payroll_control_id
And per.status_code not in ('S','R')
And pal.element_type_id in
((select petr.element_type_id from pay_element_type_rules petr, psp_report_template_details_h prth where
petr.include_or_exclude ='I' and
prth.request_id =per.request_id and prth.criteria_lookup_code='EST' and
petr.element_Set_id = prth.criteria_value1 )
union all (select pet1.element_type_id from
pay_element_types_f pet1, pay_ele_classification_rules pecr,
psp_report_template_details_h prth1
where pet1.classification_id = pecr.classification_id and
prth1.criteria_lookup_code='EST' and prth1.request_id =per.request_id and
pecr.element_Set_id = prth1.criteria_value1 ))
And pal.element_type_id not in
(select petr1.element_type_id from pay_element_type_rules petr1,
psp_report_template_Details_h prth2
where petr1.include_or_exclude='E'
and prth2.request_id = per.request_id
and prth2.criteria_lookup_code='EST' and prth2.criteria_value1= petr1.element_Set_id )
group by per.person_id, per.effort_report_id;
Select per.effort_report_id,
per.person_id,
min(pregen.summary_line_id)
From psp_pre_gen_dist_lines_history pregen,
Psp_eff_reports per,
Psp_payroll_controls ppc
Where pregen.distribution_date between per.start_date and per.end_date
And per.request_id in (select -person_id from psp_supercede_persons_gt where person_id < 0)
And ppc.run_id = p_run_id
And pregen.person_id = per.person_id
And ppc.payroll_control_id = pregen.payroll_control_id
And per.status_code not in ('S','R')
And pregen.element_type_id in
((select petr.element_type_id from pay_element_type_rules petr, psp_report_template_details_h prth where
petr.include_or_exclude ='I' and
prth.request_id =per.request_id and prth.criteria_lookup_code='EST' and
petr.element_Set_id = prth.criteria_value1
)
union all
(select pet1.element_type_id from
pay_element_types_f pet1, pay_ele_classification_rules pecr,
psp_report_template_details_h prth1
where pet1.classification_id = pecr.classification_id and
prth1.criteria_lookup_code='EST' and prth1.request_id =per.request_id and
pecr.element_Set_id = prth1.criteria_value1
))
And pregen.element_type_id not in
(select petr1.element_type_id from pay_element_type_rules petr1,
psp_report_template_Details_h prth2
where petr1.include_or_exclude='E'
and prth2.request_id = per.request_id
and prth2.criteria_lookup_code='EST' and prth2.criteria_value1= petr1.element_Set_id
)
group by per.person_id,per.effort_report_id;
Select per.effort_report_id,
per.person_id,
MIN(dlh.summary_line_id) summary_line_id
From psp_eff_reports per,
Psp_distribution_lines_history DLH,
Psp_payroll_controls ppc,
psp_payroll_sub_lines psub,
psp_payroll_lines ppl
Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
And per.request_id in (select -person_id from psp_supercede_persons_gt where person_id < 0)
And psub.payroll_line_id = ppl.payroll_line_id
And ppl.person_id = per.person_id
And dlh.distribution_date between per.start_date and per.end_date
And ppc.run_id = p_run_id
And ppc.payroll_control_id = ppl.payroll_control_id
And per.status_code not in ('S','R')
And ppl.element_type_id in
((select petr.element_type_id from pay_element_type_rules petr, psp_report_template_details_h prth where
petr.include_or_exclude ='I' and
prth.request_id =per.request_id and prth.criteria_lookup_code='EST' and
petr.element_Set_id = prth.criteria_value1
)
union all
(select pet1.element_type_id from
pay_element_types_f pet1, pay_ele_classification_rules pecr,
psp_report_template_details_h prth1
where pet1.classification_id = pecr.classification_id and
prth1.criteria_lookup_code='EST' and prth1.request_id =per.request_id and
pecr.element_Set_id = prth1.criteria_value1
))
And ppl.element_type_id not in
(select petr1.element_type_id from pay_element_type_rules petr1,
psp_report_template_Details_h prth2
where petr1.include_or_exclude='E'
and prth2.request_id = per.request_id
and prth2.criteria_lookup_code='EST' and prth2.criteria_value1= petr1.element_Set_id
)
group by per.person_id,per.effort_report_id;
select effort_report_id,
person_id,
min(summary_line_id) summary_line_id
from (
Select per.effort_report_id,
per.person_id,
dlh.summary_line_id summary_line_id
From psp_eff_reports per,
Psp_distribution_lines_history DLH,
Psp_payroll_controls ppc,
psp_payroll_sub_lines psub,
psp_payroll_lines ppl
Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
And per.request_id in (select -person_id from psp_supercede_persons_gt)
And psub.payroll_line_id = ppl.payroll_line_id
And ppl.person_id = per.person_id
And dlh.distribution_date between per.start_date and per.end_date
And ppc.run_id = p_run_id
And ppc.payroll_control_id = ppl.payroll_control_id
And per.status_code not in ('S','R')
And ppl.element_type_id in
((select petr.element_type_id from pay_element_type_rules petr, psp_report_template_details_h prth where
petr.include_or_exclude ='I' and
prth.request_id =per.request_id and prth.criteria_lookup_code='EST' and
petr.element_Set_id = prth.criteria_value1
)
union all
(select pet1.element_type_id from
pay_element_types_f pet1, pay_ele_classification_rules pecr,
psp_report_template_details_h prth1
where pet1.classification_id = pecr.classification_id and
prth1.criteria_lookup_code='EST' and prth1.request_id =per.request_id
and pecr.element_Set_id = prth1.criteria_value1
))
And ppl.element_type_id not in
(select petr1.element_type_id from pay_element_type_rules petr1,
psp_report_template_Details_h prth2
where petr1.include_or_exclude='E'
and prth2.request_id = per.request_id
and prth2.criteria_lookup_code='EST' and prth2.criteria_value1= petr1.element_Set_id
)
UNION ALL
Select per.effort_report_id,
per.person_id,
pregen.summary_line_id summary_line_id
From psp_pre_gen_dist_lines_history pregen,
Psp_eff_reports per,
Psp_payroll_controls ppc
Where pregen.distribution_date between per.start_date and per.end_date
And per.request_id in ( select -person_id from psp_supercede_persons_gt )
And ppc.run_id = p_run_id
And pregen.person_id = per.person_id
And ppc.payroll_control_id = pregen.payroll_control_id
And per.status_code not in ('S','R')
And pregen.element_type_id in
((select petr.element_type_id from pay_element_type_rules petr, psp_report_template_details_h prth where
petr.include_or_exclude ='I' and
prth.request_id =per.request_id and prth.criteria_lookup_code='EST'
and petr.element_Set_id = prth.criteria_value1
)
union all
(select pet1.element_type_id from
pay_element_types_f pet1, pay_ele_classification_rules pecr,
psp_report_template_details_h prth1
where pet1.classification_id = pecr.classification_id and
prth1.criteria_lookup_code='EST' and prth1.request_id =per.request_id
and pecr.element_Set_id = prth1.criteria_value1
))
And pregen.element_type_id not in
(select petr1.element_type_id from pay_element_type_rules petr1,
psp_report_template_Details_h prth2
where petr1.include_or_exclude='E'
and prth2.request_id = per.request_id
and prth2.criteria_lookup_code='EST' and prth2.criteria_value1= petr1.element_Set_id
))
group by person_id,effort_report_id;
Select per.effort_report_id,
per.person_id,
min(pal.summary_line_id) summary_line_id
From psp_adjustment_lines_history pal,
Psp_eff_reports per,
Psp_payroll_controls ppc,
psp_eff_report_details perd
Where pal.distribution_date between per.start_date and per.end_date
and perd.effort_report_id = per.effort_report_id
and perd.assignment_id = pal.assignment_id
And per.request_id in ( select -person_id from psp_supercede_persons_gt where person_id < 0)
And ppc.run_id = p_run_id
And pal.person_id = per.person_id
And ppc.payroll_control_id = pal.payroll_control_id
And per.status_code not in ('S','R')
And pal.element_type_id in
((select petr.element_type_id from pay_element_type_rules petr, psp_report_template_details_h prth where
petr.include_or_exclude ='I'
and prth.request_id =per.request_id and prth.criteria_lookup_code='EST'
and petr.element_Set_id = prth.criteria_value1 )
union all (select pet1.element_type_id from
pay_element_types_f pet1, pay_ele_classification_rules pecr,
psp_report_template_details_h prth1
where pet1.classification_id = pecr.classification_id
and prth1.criteria_lookup_code='EST' and prth1.request_id =per.request_id
and pecr.element_Set_id = prth1.criteria_value1 ))
And pal.element_type_id not in
(select petr1.element_type_id from pay_element_type_rules petr1,
psp_report_template_Details_h prth2
where petr1.include_or_exclude='E'
and prth2.request_id = per.request_id
and prth2.criteria_lookup_code='EST' and prth2.criteria_value1= petr1.element_Set_id )
group by per.person_id, per.effort_report_id;
Select per.effort_report_id,
per.person_id,
min(pregen.summary_line_id)
From psp_pre_gen_dist_lines_history pregen,
Psp_eff_reports per,
Psp_payroll_controls ppc,
psp_eff_report_details perd
Where pregen.distribution_date between per.start_date and per.end_date
and perd.effort_report_id = per.effort_report_id
and perd.assignment_id = pregen.assignment_id
And per.request_id in (select -person_id from psp_supercede_persons_gt where person_id < 0)
And ppc.run_id = p_run_id
And pregen.person_id = per.person_id
And ppc.payroll_control_id = pregen.payroll_control_id
And per.status_code not in ('S','R')
And pregen.element_type_id in
((select petr.element_type_id from pay_element_type_rules petr, psp_report_template_details_h prth where
petr.include_or_exclude ='I' and
prth.request_id =per.request_id and prth.criteria_lookup_code='EST'
and petr.element_Set_id = prth.criteria_value1
)
union all
(select pet1.element_type_id from
pay_element_types_f pet1, pay_ele_classification_rules pecr,
psp_report_template_details_h prth1
where pet1.classification_id = pecr.classification_id and
prth1.criteria_lookup_code='EST' and prth1.request_id =per.request_id
and pecr.element_Set_id = prth1.criteria_value1
))
And pregen.element_type_id not in
(select petr1.element_type_id from pay_element_type_rules petr1,
psp_report_template_Details_h prth2
where petr1.include_or_exclude='E'
and prth2.request_id = per.request_id
and prth2.criteria_lookup_code='EST' and prth2.criteria_value1= petr1.element_Set_id
)
group by per.person_id,per.effort_report_id;
Select per.effort_report_id,
per.person_id,
MIN(dlh.summary_line_id) summary_line_id
From psp_eff_reports per,
Psp_distribution_lines_history DLH,
Psp_payroll_controls ppc,
psp_payroll_sub_lines psub,
psp_payroll_lines ppl,
psp_eff_report_details perd
Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
and perd.effort_report_id = per.effort_report_id
and perd.assignment_id = ppl.assignment_id
And per.request_id in (select -person_id from psp_supercede_persons_gt where person_id < 0)
And psub.payroll_line_id = ppl.payroll_line_id
And ppl.person_id = per.person_id
And dlh.distribution_date between per.start_date and per.end_date
And ppc.run_id = p_run_id
And ppc.payroll_control_id = ppl.payroll_control_id
And per.status_code not in ('S','R')
And ppl.element_type_id in
((select petr.element_type_id from pay_element_type_rules petr, psp_report_template_details_h prth where
petr.include_or_exclude ='I'
and prth.request_id =per.request_id and prth.criteria_lookup_code='EST'
and petr.element_Set_id = prth.criteria_value1
)
union all
(select pet1.element_type_id from
pay_element_types_f pet1, pay_ele_classification_rules pecr,
psp_report_template_details_h prth1
where pet1.classification_id = pecr.classification_id and
prth1.criteria_lookup_code='EST' and prth1.request_id =per.request_id
and pecr.element_Set_id = prth1.criteria_value1
))
And ppl.element_type_id not in
(select petr1.element_type_id from pay_element_type_rules petr1,
psp_report_template_Details_h prth2
where petr1.include_or_exclude='E'
and prth2.request_id = per.request_id
and prth2.criteria_lookup_code='EST' and prth2.criteria_value1= petr1.element_Set_id
)
group by per.person_id,per.effort_report_id;
select effort_report_id,
person_id,
min(summary_line_id) summary_line_id
from (
Select per.effort_report_id,
per.person_id,
dlh.summary_line_id summary_line_id
From psp_eff_reports per,
Psp_distribution_lines_history DLH,
Psp_payroll_controls ppc,
psp_payroll_sub_lines psub,
psp_payroll_lines ppl,
psp_eff_report_details perd
Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
and perd.effort_report_id = per.effort_report_id
and perd.assignment_id = ppl.assignment_id
And per.request_id in (select -person_id from psp_supercede_persons_gt)
And psub.payroll_line_id = ppl.payroll_line_id
And ppl.person_id = per.person_id
And dlh.distribution_date between per.start_date and per.end_date
And ppc.run_id = p_run_id
And ppc.payroll_control_id = ppl.payroll_control_id
And per.status_code not in ('S','R')
And ppl.element_type_id in
((select petr.element_type_id from pay_element_type_rules petr, psp_report_template_details_h prth where
petr.include_or_exclude ='I' and
prth.request_id =per.request_id and prth.criteria_lookup_code='EST'
and petr.element_Set_id = prth.criteria_value1
)
union all
(select pet1.element_type_id from
pay_element_types_f pet1, pay_ele_classification_rules pecr,
psp_report_template_details_h prth1
where pet1.classification_id = pecr.classification_id
and prth1.criteria_lookup_code='EST' and prth1.request_id =per.request_id
and pecr.element_Set_id = prth1.criteria_value1
))
And ppl.element_type_id not in
(select petr1.element_type_id from pay_element_type_rules petr1,
psp_report_template_Details_h prth2
where petr1.include_or_exclude='E'
and prth2.request_id = per.request_id
and prth2.criteria_lookup_code='EST' and prth2.criteria_value1= petr1.element_Set_id
)
UNION ALL
Select per.effort_report_id,
per.person_id,
pregen.summary_line_id summary_line_id
From psp_pre_gen_dist_lines_history pregen,
Psp_eff_reports per,
Psp_payroll_controls ppc,
psp_eff_report_details perd
Where pregen.distribution_date between per.start_date and per.end_date
and perd.effort_report_id = per.effort_report_id
and perd.assignment_id = pregen.assignment_id
And per.request_id in ( select -person_id from psp_supercede_persons_gt )
And ppc.run_id = p_run_id
And pregen.person_id = per.person_id
And ppc.payroll_control_id = pregen.payroll_control_id
And per.status_code not in ('S','R')
And pregen.element_type_id in
((select petr.element_type_id from pay_element_type_rules petr, psp_report_template_details_h prth where
petr.include_or_exclude ='I'
and prth.request_id =per.request_id and prth.criteria_lookup_code='EST'
and petr.element_Set_id = prth.criteria_value1
)
union all
(select pet1.element_type_id from
pay_element_types_f pet1, pay_ele_classification_rules pecr,
psp_report_template_details_h prth1
where pet1.classification_id = pecr.classification_id
and prth1.criteria_lookup_code='EST' and prth1.request_id =per.request_id
and pecr.element_Set_id = prth1.criteria_value1
))
And pregen.element_type_id not in
(select petr1.element_type_id from pay_element_type_rules petr1,
psp_report_template_Details_h prth2
where petr1.include_or_exclude='E'
and prth2.request_id = per.request_id
and prth2.criteria_lookup_code='EST' and prth2.criteria_value1= petr1.element_Set_id
))
group by person_id,effort_report_id;
select count(*)
from psp_Eff_reports per,
psp_summary_lines psl
where per.person_id = psl.person_id
and psl.payroll_control_id in
(select payroll_control_id
from psp_payroll_controls
where run_id = p_run_id)
and rownum = 1
and per.request_id in
(select abs(person_id)
from psp_supercede_persons_gt
where person_id < 0);
select count(*)
from psp_report_templates_h
where request_id in ( select -person_id from psp_supercede_persons_gt )
and selection_match_level = 'EMP';
select effort_report_id
from psp_eff_reports
where request_id = p_req_id and person_id = p_per_id;
SELECT USER_CONCURRENT_PROGRAM_NAME
FROM fnd_concurrent_programs_vl fcp
WHERE CONCURRENT_PROGRAM_ID = fnd_global.CONC_PROGRAM_ID;
select template_name
from psp_report_templates
where template_id = p_template_id;
select description
from psp_payroll_sources
where source_type = p_source_type
and source_code = p_source_code;
select period_name
from per_time_periods
where time_period_id = p_time_period_id;
select distinct wf.name,
wf.orig_system_id
from wf_roles wf,
psp_report_templates_h h,
psp_report_template_details_h dtl
where wf.orig_system = 'PER'
and to_char(wf.orig_system_id) = dtl.criteria_value1
and dtl.criteria_lookup_type = 'PSP_SELECTION_CRITERIA'
and dtl.criteria_lookup_code = 'FRP'
and dtl.request_id = h.request_id
and h.request_id in ( select -person_id
from psp_supercede_persons_gt
where person_id < 0)
and h.final_recip_notified_flag = 'Y';
select distinct wf.name,
wf.orig_system_id
from wf_roles wf,
psp_report_templates_h h
where wf.orig_system = 'PER'
and wf.orig_system_id = h.initiator_person_id
and h.request_id in ( select -person_id
from psp_supercede_persons_gt
where person_id < 0)
and exists (select 1 from psp_eff_reports per
where per.request_id = h.request_id
and per.superceding_request_id = p_SandT_reqid);
select distinct wf.name,
er.person_id
from wf_roles wf,
psp_eff_reports er,
psp_report_templates_h h
where wf.orig_system = 'PER'
and wf.orig_system_id = er.person_id
and h.request_id = er.request_id
and h.initiator_accept_flag = 'Y'
and er.request_id in ( select -person_id
from psp_supercede_persons_gt
where person_id < 0)
and er.superceding_request_id = p_SandT_reqid;
select distinct era.wf_role_name
from psp_eff_report_approvals era,
psp_eff_report_details erd,
psp_eff_reports er,
psp_report_templates_h h
where erd.effort_report_detail_id = era.effort_report_detail_id
and erd.effort_report_id = er.effort_report_id
and er.request_id in ( select -person_id from psp_supercede_persons_gt where person_id < 0 )
and er.superceding_request_id = p_SandT_reqid
and era.approval_status <> 'R'
and er.request_id = h.request_id
and h.initiator_accept_flag = 'Y';
select criteria_lookup_code
from psp_report_template_details
where template_id = p_template_id
and criteria_lookup_type = 'PSP_SUPERCEDED_NOTIFICATION';
select psp_wf_item_key_s.nextval
into l_wf_itemkey
from dual;
delete psp_supercede_persons_gt;
insert into psp_supercede_persons_gt
(person_id)
values ( -t_request_id(k));
hr_utility.trace( 'SandT_Supercede--> Before update Eff table 1');
update psp_eff_reports
set status_code = 'S',
superceding_summary_line_id = t_sum_line_id(i),
superceding_request_id = l_S_and_T_reqid,
superceding_program_name = l_Con_Program_name
where effort_report_id = t_efforts_table(i);
hr_utility.trace( 'SandT_Supercede--> Updated the ER table, to put S');
insert into psp_supercede_persons_gt
(person_id)
values (t_person_table(i));
hr_utility.trace( 'SandT_Supercede--> insert into GT potential persons Number of recodrs=' ||t_person_table.count||'person_id =' ||t_person_table(1));
select distinct er.request_id, er.person_id
bulk collect into :reqtable, :pertable
from psp_eff_report_details erd,
psp_supercede_persons_gt gt,
psp_eff_reports er
where er.status_code not in
('||''''||'S'||''''||','||''''||'R'||''''||')
and er.request_id in ('||l_supercede_reqid_str||')
and er.effort_report_id = erd.effort_report_id
and gt.person_id = er.person_id
group by er.request_id, er.person_id '|| PSP_CREATE_EFF_REPORTS.g_summarization_criteria ||
' having abs(sum(decode(er.status_code,'||''''|| 'T'||''''||',
-erd.actual_salary_amt,
erd.actual_salary_amt))) > :l_tolerance_amt;
select distinct er.request_id, er.person_id
bulk collect into :reqtable, :pertable
from psp_eff_report_details erd,
psp_supercede_persons_gt gt,
psp_eff_reports er
where er.status_code not in
('||''''||'S'||''''||','||''''||'R'||''''||')
and er.request_id in ('||l_supercede_reqid_str||')
and gt.person_id = er.person_id
and er.effort_report_id = erd.effort_report_id
group by er.request_id, er.person_id '||PSP_CREATE_EFF_REPORTS.g_summarization_criteria ||
' having abs(sum(decode(er.status_code,'||''''|| 'T'||''''||',
-erd.payroll_percent,
erd.payroll_percent))) > :l_tolerance_percent;
update psp_eff_reports
set status_code = 'S',
superceding_summary_line_id = t_tolerance_sline(i),
superceding_request_id = l_S_and_T_reqid,
superceding_program_name = l_Con_Program_name
where effort_report_id = t_tolerance_erid(i);
hr_utility.trace( 'SandT_Supercede--> Updated erid='|| t_tolerance_erid(i));
delete psp_supercede_persons_gt where person_id > 0;
delete psp_supercede_persons_gt;
delete psp_supercede_persons_gt;
insert into psp_supercede_persons_gt (person_id) values ( -t_request_id(k));
delete psp_supercede_persons_gt;
t_person_table.delete;
t_efforts_table.delete;
t_sum_line_id.delete ;
t_tolerance_erid.delete;
t_request_id.delete;
t_template_id.delete;
t_tolerance_sline.delete;