[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
SELECT distinct ppc.batch_name
FROM psp_payroll_controls ppc,
psp_adjustment_control_table pact
WHERE ppc.status_code = decode(g_run_type, 'N', 'N', 'R', 'I')
AND ppc.source_type = 'A'
AND ppc.batch_name = pact.adjustment_batch_name
AND (g_run_type = 'N' OR (g_run_type = 'R' and
ppc.adj_sum_batch_name = p_adj_sum_batch_name))
AND (dist_dr_amount is not null or dist_cr_amount is not null)
AND ppc.business_group_id = p_business_group_id
AND ppc.set_of_books_id = p_set_of_books_id
AND (pact.approver_id is not null and pact.approver_id <> -999);
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name,
gms_phase
FROM psp_payroll_controls
WHERE source_type = 'A'
AND ((adj_sum_batch_name is null) OR
(adj_sum_batch_name is not null and run_id is not null))
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND batch_name = p_batch_name
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND status_code in ( 'N','I'); --- added 'I' for 2444657
select decode(count(*), 0, 'N', 'R')
from psp_payroll_controls
where source_type = 'A'
and adj_sum_batch_name = p_adj_sum_batch_name
and status_code = 'I';
SELECT count(*)
FROM psp_payroll_controls
WHERE adj_sum_batch_name = p_adj_sum_batch_name
AND source_type = 'A' -- Bug 2133056
AND g_run_type <> 'R';
SELECT psp_st_run_id_s.nextval
INTO g_run_id
FROM DUAL;
UPDATE psp_payroll_controls
SET status_code = 'I',
adj_sum_batch_name = p_adj_sum_batch_name,
run_id = g_run_id
WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' IF updated psp_payroll_controls count: '||SQL%rowcount);
UPDATE psp_payroll_controls
SET run_id = g_run_id
WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' ELSE updated psp_payroll_controls count: '||SQL%rowcount);
SELECT distinct batch_name
FROM psp_payroll_controls
WHERE source_type = 'A'
AND adj_sum_batch_name = p_adj_sum_batch_name
AND (dist_dr_amount is not null or dist_cr_amount is not null)
-- AND (gl_phase = 'GL_Tie_Back' OR gms_phase = 'GMS_Tie_Back') : Bug 1776606 : Commented out
-- Bug 1776606 : Added the next two conditions..Phase =Null or GMS_TIE_BACK /GL_TIE_BACK
AND (gms_phase is null or gms_phase = 'GMS_Tie_Back')
AND (gl_phase is null or gl_phase = 'GL_Tie_Back')
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 = nvl(g_run_id, run_id);
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name,
gl_phase,
gms_phase
FROM psp_payroll_controls
WHERE source_type = 'A'
AND batch_name = p_batch_name
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND status_code = 'I'
AND run_id = g_run_id;
SELECT count(*)
FROM psp_summary_lines
WHERE payroll_control_id = p_payroll_control_id
AND status_code <> 'A';
SELECT count(*)
FROM psp_adjustment_lines
WHERE payroll_control_id = p_payroll_control_id
AND gl_code_combination_id is not null
AND status_code <> 'A';
DELETE FROM pa_transaction_interface_all
-- WHERE batch_name = g_gms_batch_name
WHERE batch_name IN (SELECT GMS_BATCH_NAME
FROM psp_summary_lines
WHERE PAYROLL_CONTROL_ID IN(SELECT payroll_control_id
FROM psp_payroll_controls
WHERE adj_sum_batch_name =p_adj_sum_batch_name))
---AND transaction_status_code = 'A' delete 'R' also, for 2445196
AND transaction_source in ('OLD', 'GOLD');
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' delete from pa_transaction_interface_all count : '||sql%rowcount);
delete from gms_transaction_interface_all
-- where batch_name = g_gms_batch_name
WHERE batch_name IN (SELECT GMS_BATCH_NAME
FROM psp_summary_lines
WHERE PAYROLL_CONTROL_ID IN(SELECT payroll_control_id
FROM psp_payroll_controls
WHERE adj_sum_batch_name =p_adj_sum_batch_name))
and transaction_source = 'GOLD';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' delete from gms_transaction_interface_all count : '||sql%rowcount);
UPDATE psp_payroll_controls
SET status_code = 'P'
WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' UPDATE psp_payroll_controls status_code P count: '||sql%rowcount);
SELECT distinct batch_name
FROM psp_payroll_controls
WHERE source_type = 'A'
AND status_code = 'I'
AND run_id = nvl(g_run_id, run_id)
AND gl_phase is null
AND adj_sum_batch_name = p_adj_sum_batch_name
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND (dist_dr_amount is not null or dist_cr_amount is not null);
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name,
gl_posting_override_date
FROM psp_payroll_controls
WHERE batch_name = p_batch_name
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND source_type = 'A'
AND status_code = 'I'
AND run_id = nvl(g_run_id, run_id);
SELECT pal.person_id,
pal.assignment_id,
pal.gl_code_combination_id gl_ccid,
pal.dr_cr_flag,
pal.effective_date,
psl.accounting_date, -- removed nvl on a/c date for 4734810
psl.exchange_rate_type,
pal.distribution_amount,
pal.adjustment_line_id distribution_line_id,
'A' tab_flag,
DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
FROM psp_adjustment_lines pal,
psp_distribution_lines_history pdlh,
psp_summary_lines psl
WHERE pal.status_code = 'N'
AND pal.gl_code_combination_id is not null
AND pal.payroll_control_id = p_payroll_control_id
AND pal.orig_source_type = 'D'
AND pal.orig_line_id = pdlh.distribution_line_id
AND pdlh.summary_line_id = psl.summary_line_id
UNION --- added union to get accounting dates -- 3108109
SELECT pal.person_id,
pal.assignment_id,
pal.gl_code_combination_id gl_ccid,
pal.dr_cr_flag,
pal.effective_date,
psl.accounting_date, ---3108109
psl.exchange_rate_type,
pal.distribution_amount,
pal.adjustment_line_id distribution_line_id,
'A' tab_flag,
DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
FROM psp_adjustment_lines pal,
psp_adjustment_lines_history palh,
psp_summary_lines psl
WHERE pal.status_code = 'N'
AND pal.gl_code_combination_id is not null
AND pal.payroll_control_id = p_payroll_control_id
AND pal.orig_source_type = 'A'
AND pal.orig_line_id = palh.adjustment_line_id
AND palh.summary_line_id = psl.summary_line_id
UNION
SELECT pal.person_id,
pal.assignment_id,
pal.gl_code_combination_id gl_ccid,
pal.dr_cr_flag,
pal.effective_date,
psl.accounting_date, ---3108109
psl.exchange_rate_type,
pal.distribution_amount,
pal.adjustment_line_id distribution_line_id,
'A' tab_flag,
DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
FROM psp_adjustment_lines pal,
psp_pre_gen_dist_lines_history pglh,
psp_summary_lines psl
WHERE pal.status_code = 'N'
AND pal.gl_code_combination_id is not null
AND pal.payroll_control_id = p_payroll_control_id
AND pal.orig_source_type = 'P'
AND pal.orig_line_id = pglh.pre_gen_dist_line_id
AND pglh.summary_line_id = psl.summary_line_id
ORDER BY 1,2,3,4,6,7,11,12,13,14,15,16,17,18,19,20,21,5;
update psp_payroll_controls
set gl_phase = 'Summarize_GL_Lines'
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,
l_accounting_date, -- added for 3108109
l_exchange_rate_type,
payroll_control_rec.source_type,
payroll_control_rec.payroll_source_code,
l_sob_id,
l_gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL,
l_summary_amount,
l_dr_cr_flag,
'N',
payroll_control_rec.batch_name,
payroll_control_rec.payroll_control_id,
p_business_group_id,
l_attribute_category, -- Introduced DFF parameters 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);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling insert_into_summary_lines');
UPDATE psp_adjustment_lines
SET summary_line_id = l_summary_line_id
WHERE adjustment_line_id = l_dist_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 1');
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,
l_accounting_date, --- added for 3108109
l_exchange_rate_type,
payroll_control_rec.source_type,
payroll_control_rec.payroll_source_code,
l_sob_id,
l_gl_ccid,
NULL,
NULL,
NULL,
NULL,
NULL,
l_summary_amount,
l_dr_cr_flag,
'N',
payroll_control_rec.batch_name,
payroll_control_rec.payroll_control_id,
p_business_group_id,
l_attribute_category, -- Introduced DFF parameters 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);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' After insert_into_summary_lines 2 l_summary_line_id: '||l_summary_line_id);
UPDATE psp_adjustment_lines
SET summary_line_id = l_summary_line_id,
status_code = 'N'
WHERE adjustment_line_id = l_dist_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 2 set status_code to N');
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-> 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_msg_id number(9);
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,
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 DFF columns 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, --- 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 DFF columns for bug fix 2908859
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
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_ADJ','INSERT_INTO_SUMMARY_LINES');
SELECT distinct batch_name
FROM psp_payroll_controls
WHERE adj_sum_batch_name = p_adj_sum_batch_name
AND source_type = 'A'
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND status_code = 'I'
AND gl_phase = 'Summarize_GL_Lines'
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND run_id = nvl(g_run_id, run_id);
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name,
-- Introduced for bug fix 2916848
currency_code
FROM psp_payroll_controls
WHERE batch_name = p_batch_name
AND source_type = 'A'
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND status_code = 'I'
AND gl_phase = 'Summarize_GL_Lines'
AND run_id = g_run_id;
SELECT psl.summary_line_id,
psl.source_code,
psl.effective_date,
psl.accounting_date, --- added 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,
psl.person_id --Included this column as part of bug fix 1828519
FROM psp_summary_lines psl
WHERE psl.status_code = 'N'
AND psl.gl_code_combination_id IS NOT NULL
AND psl.payroll_control_id = p_payroll_control_id;
SELECT period_name, end_date
FROM per_time_periods
WHERE time_period_id = gl_batch_rec.time_period_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = l_sob_id;
SELECT full_name
FROM per_people_f ppf
WHERE ppf.person_id = (SELECT pal.person_id
FROM psp_adjustment_lines pal
WHERE pal.payroll_control_id = gl_batch_rec.payroll_control_id
AND rownum = 1); --Bug 2133056 removed distinct and replace batch_name with payroll_control_id
SELECT gl_interface_control_s.nextval
INTO l_group_id
FROM DUAL;
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_batch_rec.payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling insert_into_gl_interface ');
insert_into_gl_interface(
L_SOB_ID,GL_INTERFACE_REC.EFFECTIVE_DATE, gl_batch_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,
GL_INTERFACE_REC.accounting_date, --- added for 3108109
L_RETURN_STATUS);
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) ||' Calling insert_into_gl_interface ');
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,
l_sob_id
);
update psp_payroll_controls
set gl_phase = 'Submitted_Import_Request'
--where payroll_control_id = gl_batch_rec.payroll_control_id; Replaced this line for Bug 2133056
SELECT user_je_source_name
FROM gl_je_sources
WHERE je_source_name = 'OLD';
SELECT user_je_category_name
FROM gl_je_categories
WHERE je_category_name = 'OLD';
SELECT distinct batch_name
FROM psp_payroll_controls
WHERE adj_sum_batch_name = p_adj_sum_batch_name
AND (dist_dr_amount is not null and dist_cr_amount is not null)
AND source_type = 'A'
AND status_code = 'I'
AND gl_phase = 'Submitted_Import_Request'
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND run_id = nvl(g_run_id, run_id);
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 reversal_entry_flag
FROM psp_adjustment_lines
WHERE summary_line_id = p_summary_line_id;
SELECT payroll_control_id,
source_type
FROM psp_payroll_controls
WHERE batch_name = p_batch_name
AND (dist_dr_amount is not null and dist_cr_amount is not null)
AND source_type = 'A'
AND status_code = 'I'
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND gl_phase = 'Submitted_Import_Request'
AND run_id = nvl(g_run_id, run_id);
SELECT PSL.group_id
FROM psp_summary_lines PSL,
psp_payroll_controls PPC
WHERE PPC.payroll_control_id = PSL.payroll_control_id
-- WHERE payroll_control_id = gl_tie_back_rec.payroll_control_id
AND PPC.batch_name = pc_batch_rec.batch_name
AND PPC.source_type = 'A'
AND PSL.group_id IS NOT NULL
AND rownum =1; -- Removed max function on group_id and introduced rownum=1 Bug 2133056
SELECT count(*)
FROM gl_interface
WHERE group_id = l_group_id
AND set_of_books_id = p_set_of_books_id
AND user_je_source_name = 'OLD';
SELECT count(*)
FROM gl_interface
WHERE group_id = l_group_id
AND user_je_source_name = 'OLD'
AND status = 'NEW';
update psp_payroll_controls
set gl_phase = 'Summarize_GL_Lines'
where gl_phase = 'Submitted_Import_Request'
and batch_name = pc_batch_rec.batch_name; --- 3184075
delete from gl_interface
where group_id = l_group_id
and user_je_source_name = 'OLD';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gl_interface count: '||sql%rowcount);
delete from gl_interface_control
where group_id = l_group_id
and je_source_name = 'OLD';
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gl_interface count: '||sql%rowcount);
UPDATE psp_payroll_controls
SET gl_phase = 'GL_Tie_Back'
WHERE payroll_control_id = gl_tie_back_rec.payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_payroll_controls set gl_phase to GL_Tie_Back count: '||sql%rowcount||' for control_id : '||gl_tie_back_rec.payroll_control_id);
UPDATE psp_summary_lines
SET status_code = 'A'
WHERE summary_line_id = l_summary_line_id;
UPDATE psp_adjustment_lines
SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
INSERT INTO psp_adjustment_lines_history
(adjustment_line_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_org_account_id,suspense_reason_code,effort_report_id,version_num,
summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
orig_source_type,
orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
attribute12,attribute13,attribute14,attribute15,last_update_date,
last_updated_by,last_update_login,created_by,creation_date, business_group_id,
adj_set_number, line_number) --- added cols 2634557 DA Multiple element Enh
SELECT adjustment_line_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_org_account_id,suspense_reason_code,effort_report_id,version_num,
summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
orig_source_type,
orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
attribute12,attribute13,attribute14,attribute15,SYSDATE,FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,FND_GLOBAL.USER_ID,SYSDATE, business_group_id,
adj_set_number, line_number --- added cols 2634557 DA Multiple element Enh
FROM psp_adjustment_lines
WHERE status_code = 'A'
AND summary_line_id = l_summary_line_id;
DELETE FROM psp_adjustment_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) ||' Updated psp_summary_lines, psp_adjustment_lines inserted into
psp_adjustment_lines_history then deleted from psp_adjustment_lines for summary_line_id : '||l_summary_line_id);
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 = gl_tie_back_rec.payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_payroll_controls set gl_dr_amount and gl_cr_amount for cotrol_id : '||gl_tie_back_rec.payroll_control_id);
SELECT person_id,
assignment_id,
to_number(null) element_type_id,
dr_cr_flag,
effective_date,
accounting_date,
exchange_rate_type,
source_type,
source_code,
time_period_id,
summary_amount reversal_dist_amount,
interface_batch_name,
summary_line_id
FROM psp_summary_lines
WHERE payroll_control_id = p_payroll_control_id
AND gl_code_combination_id IS NOT NULL
AND status_code = 'N'
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id;
SELECT reversing_gl_ccid
FROM psp_clearing_account
WHERE set_of_books_id = p_set_of_books_id
AND business_group_id = p_business_group_id
AND payroll_id = l_payroll_id; -- Added for bug 5592964
select sum(nvl(distribution_amount,0))
from psp_adjustment_lines
where dr_cr_flag = 'D'
and gl_code_combination_id is not null
and payroll_control_id = p_payroll_control_id;
select sum(nvl(distribution_amount,0))
from psp_adjustment_lines
where dr_cr_flag = 'C'
and gl_code_combination_id is not null
and payroll_control_id = p_payroll_control_id;
select element_type_id
from psp_adjustment_lines
where summary_line_id = p_summary_line_id
and rownum = 1;
SELECT payroll_id INTO l_payroll_id
FROM psp_payroll_controls
WHERE payroll_control_id = p_payroll_control_id;
DELETE FROM psp_adjustment_lines
WHERE reversal_entry_flag = 'Y'
AND status_code = 'N'
AND payroll_control_id = p_payroll_control_id;
insert_into_summary_lines(
l_summary_line_id,
ad_reversal_entry_rec.person_id,
ad_reversal_entry_rec.assignment_id,
ad_reversal_entry_rec.time_period_id,
ad_reversal_entry_rec.effective_date,
ad_reversal_entry_rec.accounting_date,
ad_reversal_entry_rec.exchange_rate_type,
'A',
ad_reversal_entry_rec.source_code,
p_set_of_books_id,
l_clrg_account_glccid,
NULL,
NULL,
NULL,
NULL,
NULL,
ad_reversal_entry_rec.reversal_dist_amount,
ad_reversal_entry_rec.dr_cr_flag,
'N',
ad_reversal_entry_rec.interface_batch_name,
p_payroll_control_id,
p_business_group_id,
-- Introduced NULL for DFF parameters as clearing a/c doesnt require DFF values as part bug fix 2908859
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_return_status);
insert into psp_adjustment_lines
(adjustment_line_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,gl_code_combination_id,reversal_entry_flag,last_update_date,last_updated_by,
last_update_login,created_by,creation_date, business_group_id, set_of_books_id,
summary_line_id)
values
(PSP_ADJUSTMENT_LINES_S.NEXTVAL,ad_reversal_entry_rec.person_id,
ad_reversal_entry_rec.assignment_id,ad_reversal_entry_rec.element_type_id,
ad_reversal_entry_rec.effective_date,ad_reversal_entry_rec.effective_date,
ad_reversal_entry_rec.reversal_dist_amount, ----l_reversal_dist_amount, Bug 1976999
ad_reversal_entry_rec.dr_cr_flag,
p_payroll_control_id,ad_reversal_entry_rec.source_type,
ad_reversal_entry_rec.source_code,ad_reversal_entry_rec.time_period_id,
ad_reversal_entry_rec.interface_batch_name,'N',l_clrg_account_glccid,'Y',
SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID,FND_GLOBAL.USER_ID,SYSDATE,
p_business_group_id, p_set_of_books_id, l_summary_line_id);
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
l_msg_id number(9);
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 teh 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,
-- Introduced the following columns for bug fix 2916848
P_CURRENCY_CONVERSION_TYPE,
DECODE(p_currency_conversion_type, NULL, NULL, P_CURRENCY_CONVERSION_DATE));
g_error_api_path := 'INSERT_INTO_GL_INTERFACE:'||g_error_api_path;
fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','INSERT_INTO_GL_INTERFACE');
SELECT distinct batch_name
FROM psp_payroll_controls
WHERE adj_sum_batch_name = p_adj_sum_batch_name
AND source_type = 'A'
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND status_code = 'I'
AND run_id = nvl(g_run_id, run_id)
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND gms_phase is null;
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name
FROM psp_payroll_controls
WHERE batch_name = p_batch_name
AND source_type = 'A'
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND status_code = 'I'
AND run_id = nvl(g_run_id, run_id);
SELECT pal.person_id,
pal.assignment_id,
pal.project_id project_id,
pal.expenditure_organization_id,
pal.expenditure_type,
pal.task_id,
pal.award_id,
pal.dr_cr_flag,
pal.effective_date,
psl.accounting_date, -- new column 3108109, removed nvl for 4734810
psl.exchange_rate_type,
pal.distribution_amount,
pal.adjustment_line_id distribution_line_id,
'A' tab_flag,
DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
FROM psp_adjustment_lines pal,
psp_distribution_lines_history pdh,
psp_summary_lines psl
WHERE pal.status_code = 'N'
AND pal.gl_code_combination_id IS NULL
AND pal.payroll_control_id = p_payroll_control_id
AND pdh.distribution_line_id = pal.orig_line_id
AND pal.orig_source_type = 'D'
AND psl.summary_line_id = pdh.summary_line_id
UNION
SELECT pal.person_id,
pal.assignment_id,
pal.project_id project_id,
pal.expenditure_organization_id,
pal.expenditure_type,
pal.task_id,
pal.award_id,
pal.dr_cr_flag,
pal.effective_date,
psl.accounting_date, -- new column 3108109
psl.exchange_rate_type,
pal.distribution_amount,
pal.adjustment_line_id distribution_line_id,
'A' tab_flag,
DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
FROM psp_adjustment_lines pal,
psp_adjustment_lines_history pal2,
psp_summary_lines psl
WHERE pal.status_code = 'N'
AND pal.gl_code_combination_id IS NULL
AND pal.payroll_control_id = p_payroll_control_id
AND pal2.adjustment_line_id = pal.orig_line_id
AND pal.orig_source_type = 'A'
AND psl.summary_line_id = pal2.summary_line_id
union
SELECT pal.person_id,
pal.assignment_id,
pal.project_id project_id,
pal.expenditure_organization_id,
pal.expenditure_type,
pal.task_id,
pal.award_id,
pal.dr_cr_flag,
pal.effective_date,
psl.accounting_date, -- new column 3108109
psl.exchange_rate_type,
pal.distribution_amount,
pal.adjustment_line_id distribution_line_id,
'A' tab_flag,
DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
FROM psp_adjustment_lines pal,
psp_pre_gen_dist_lines_history pgh,
psp_summary_lines psl
WHERE pal.status_code = 'N'
AND pal.gl_code_combination_id IS NULL
AND pal.payroll_control_id = p_payroll_control_id
AND pgh.pre_gen_dist_line_id = pal.orig_line_id
AND pal.orig_source_type = 'P'
AND psl.summary_line_id = pgh.summary_line_id
ORDER BY 1,2,3,4,5,6,7,8,10,11,15,16,17,18,19,20,21,22,23,24,25,9;
UPDATE psp_payroll_controls
SET gms_phase = 'Summarize_GMS_Lines'
WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
-- 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,
l_accounting_date, --- 3108109
l_exchange_rate_type,
payroll_control_rec.source_type,
payroll_control_rec.payroll_source_code,
p_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,
p_business_group_id,
l_attribute_category, -- Introduced DFF parameters 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
);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' After insert_into_summary_lines 3 l_summary_line_id: '||l_summary_line_id);
UPDATE psp_adjustment_lines
SET summary_line_id = l_summary_line_id WHERE adjustment_line_id = l_dist_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 3');
dist_line_id.delete;
-- 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,
l_accounting_date, --- added for 3108109
l_exchange_rate_type,
payroll_control_rec.source_type,
payroll_control_rec.payroll_source_code,
p_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,
p_business_group_id,
l_attribute_category, -- Introduced DFF parameters 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
);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' After insert_into_summary_lines 4 l_summary_line_id: '||l_summary_line_id);
UPDATE psp_adjustment_lines
SET summary_line_id = l_summary_line_id,
status_code = 'N'
WHERE adjustment_line_id = l_dist_line_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 4 set status_code to N');
dist_line_id.delete;
SELECT distinct batch_name
FROM psp_payroll_controls
WHERE adj_sum_batch_name = p_adj_sum_batch_name
AND source_type = 'A'
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND gms_phase = 'Summarize_GMS_Lines'
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 = nvl(g_run_id, run_id);
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name,
-- Introduced the following for bug fix 2916848
currency_code
FROM psp_payroll_controls
WHERE batch_name = p_batch_name
AND source_type = 'A'
AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
AND gms_phase = 'Summarize_GMS_Lines'
AND status_code = 'I'
AND run_id = g_run_id;
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, /* Bug: 1994421 Column modified for Enhancement Employee Assignment with Zero Work Days */
psl.accounting_date, --- 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 * 5 for bug fix 29098859
psl.attribute2,
psl.attribute3,
psl.attribute4,
psl.attribute5,
psl.attribute6,
psl.attribute7,
psl.attribute8,
psl.attribute9,
psl.attribute10,
org_id -- R12 MOAC uptake
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
FROM pa_transaction_sources
WHERE transaction_source = 'OLD';
SELECT transaction_source
FROM pa_transaction_sources
WHERE transaction_source = 'GOLD';
SELECT period_name, end_date
FROM per_time_periods
WHERE time_period_id = gms_batch_rec.time_period_id;
SELECT 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 task_number
FROM pa_tasks_expend_v -- Bug : 16391366 (20/03/2013)
WHERE task_id = gms_interface_rec.task_id;
SELECT name -- Bug 2447912: Removed SUBSTR function
FROM hr_all_organization_units hou
WHERE organization_id = gms_interface_rec.expenditure_organization_id;
SELECT segment1, org_id
FROM pa_projects_all
WHERE project_id = gms_interface_rec.project_Id;
SELECT full_name
FROM per_people_f ppf
WHERE ppf.person_id = (SELECT pal.person_id
FROM psp_adjustment_lines pal
WHERE pal.payroll_control_id = gms_batch_rec.payroll_control_id
AND rownum = 1); -- Bug 2133056 Replaced batch_name with payroll_control_id and removed DISTINCT
SELECT DISTINCT payroll_control_id
FROM psp_summary_lines
WHERE gms_batch_name = l_gms_batch_name;
SELECT distinct org_id
FROM psp_payroll_controls ppc,
psp_summary_lines psl
WHERE ppc.payroll_control_id = psl.payroll_control_id
AND ppc.adj_sum_batch_name = p_adj_sum_batch_name
AND ppc.source_type = 'A'
AND (ppc.dist_dr_amount IS NOT NULL OR ppc.dist_cr_amount IS NOT NULL)
AND ppc.gms_phase = 'Summarize_GMS_Lines'
AND ppc.status_code = 'I'
AND ppc.business_group_id = p_business_group_id
AND ppc.set_of_books_id = p_set_of_books_id
AND run_id = nvl(g_run_id, run_id)
AND psl.status_code = 'N'
AND psl.gl_code_combination_id IS NULL;
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
SET gms_batch_name = gms_batch_name_tab(i) -- 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
-- update psp_summary_lines with gms batch name
UPDATE psp_summary_lines
SET gms_batch_name = gms_batch_name_tab(i) -- 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
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 for USD ');
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, -- Introduced 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.ORG_ID,
-- GMS_INTERFACE_REC.AWARD_ID, GMS_INTERFACE_REC.ATTRIBUTE2,
-- gms_attr are sent to gms_interface
gms_batch_rec.currency_code,
GMS_INTERFACE_REC.SUMMARY_AMOUNT,
gms_interface_rec.attribute1,
gms_interface_rec.attribute2,
GMS_INTERFACE_REC.ATTRIBUTE3,
gms_interface_rec.attribute4,
GMS_INTERFACE_REC.ATTRIBUTE5,
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, --- 3108109
p_business_group_id, -- Introduced for the Bug fix 2935850
L_RETURN_STATUS);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling insert_into_pa_interface for STAT ');
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, -- Introduced for Bug : 16591849
GMS_INTERFACE_REC.EXPENDITURE_TYPE,
GMS_INTERFACE_REC.SUMMARY_AMOUNT,
1,
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,
gms_interface_rec.attribute2,
GMS_INTERFACE_REC.ATTRIBUTE3,
gms_interface_rec.attribute4,
GMS_INTERFACE_REC.ATTRIBUTE5,
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,
p_business_group_id,
L_RETURN_STATUS);
SELECT to_char(psp_gms_batch_name_s.nextval)
INTO l_gms_stat_batch_name
FROM DUAL;
UPDATE pa_transaction_interface_all
SET BATCH_NAME = l_gms_stat_batch_name
where DENOM_CURRENCY_CODE = 'STAT'
and BATCH_NAME = l_gms_batch_name
and TRANSACTION_SOURCE = l_txn_source;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated pa_transaction_interface_all set batch_name to '||l_gms_stat_batch_name||' for STAT records count: '||sql%rowcount);
UPDATE psp_summary_lines
SET gms_batch_name = l_gms_stat_batch_name
WHERE payroll_control_id IN(select payroll_control_id from psp_payroll_controls
where run_id = g_run_id
and currency_code = 'STAT')
AND gms_batch_name = l_gms_batch_name
AND status_code = 'N'
AND gl_code_combination_id IS NULL;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_summary_lines set gms_batch_name to '||l_gms_stat_batch_name||' for STAT records count: '||sql%rowcount);
SELECT count(*) INTO l_not_stat_count
FROM pa_transaction_interface_all
WHERE BATCH_NAME = l_gms_batch_name;
UPDATE psp_payroll_controls
SET gms_phase = 'Submitted_Import_Request'
WHERE payroll_control_id = r_payroll_controls.payroll_control_id(I);
r_payroll_controls.payroll_control_id.DELETE;
SELECT count(*) INTO l_stat_count -- change
FROM pa_transaction_interface_all
WHERE BATCH_NAME = l_gms_stat_batch_name;
UPDATE psp_payroll_controls
SET gms_phase = 'Submitted_Import_Request'
WHERE payroll_control_id = r_payroll_controls.payroll_control_id(I);
r_payroll_controls.payroll_control_id.DELETE;
SELECT distinct batch_name
FROM psp_payroll_controls
WHERE adj_sum_batch_name = p_adj_sum_batch_name
AND (dist_dr_amount is not null and dist_cr_amount is not null)
AND source_type = 'A'
AND status_code = 'I'
AND gms_phase = 'Submitted_Import_Request'
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND run_id = nvl(g_run_id, run_id);
SELECT payroll_control_id,
source_type,
payroll_source_code,
time_period_id,
batch_name,
currency_code -- 6902514
FROM psp_payroll_controls
WHERE batch_name = p_batch_name
AND (dist_dr_amount is not null and dist_cr_amount is not null)
AND source_type = 'A'
AND status_code = 'I'
AND gms_phase = 'Submitted_Import_Request'
AND run_id = nvl(g_run_id, run_id);
SELECT summary_line_id,
dr_cr_flag,summary_amount
FROM psp_summary_lines
WHERE gms_batch_name = p_gms_batch_name
AND payroll_control_id = p_payroll_control_id;
SELECT nvl(transaction_rejection_code,'P'),
orig_transaction_reference,
transaction_status_code
FROM pa_transaction_interface_all
WHERE batch_name = to_char(p_gms_batch_name)
AND transaction_status_code in ('R', 'PO', 'PI', 'PR')
AND transaction_source = p_txn_src
AND orig_transaction_reference in
(select to_char(summary_line_id)
from psp_summary_lines
where payroll_control_id = p_payroll_control_id
and gms_batch_name = p_gms_batch_name);
x_update_count number(9);
x_insert_count number(9);
x_delete_count number(9);
SELECT DISTINCT gms_batch_name
FROM psp_summary_lines
WHERE payroll_control_id = gms_tie_back_rec.payroll_control_id
AND gms_batch_name IS NOT NULL;
SELECT transaction_source
FROM pa_transaction_interface_all
WHERE batch_name = TO_CHAR(l_gms_batch_name);
SELECT count(*)
FROM pa_transaction_interface_all
WHERE batch_name = TO_CHAR(l_gms_batch_name)
AND transaction_source = l_txn_source
AND transaction_status_code IN ('R', 'PO', 'PI', 'PR')
AND orig_transaction_reference IN (SELECT TO_CHAR(summary_line_id)
FROM psp_summary_lines
WHERE payroll_control_id = gms_tie_back_rec.payroll_control_id
AND gms_batch_name = l_gms_batch_name);
select count(*), transaction_status_code
from pa_transaction_interface_all
where batch_name = to_char(l_gms_batch_name)
and transaction_source = l_txn_source
and transaction_status_code in ('P', 'I')
group by transaction_status_code ;
select expenditure_group
from pa_expenditures_all
where expenditure_id in
(select expenditure_id
from pa_transaction_interface_all
where transaction_source in ('OLD', 'GOLD') and
transaction_rejection_code is null and
batch_name = l_gms_batch_name )
and expenditure_group is not null;
delete from pa_transaction_interface_all
where batch_name = to_char(l_gms_batch_name)
and transaction_source = l_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 = to_char(l_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 = l_gms_batch_name
and payroll_control_id = gms_tie_back_rec.payroll_control_id;
update psp_payroll_controls
set gms_phase = 'Summarize_GMS_Lines'
where gms_phase is not null
and adj_sum_batch_name = p_adj_sum_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_payroll_controls set gms_phase to Summarize_GMS_Lines
for adj_sum_batch_name: '||p_adj_sum_batch_name||' count: '||sql%rowcount);
/* Added the delete statements for EXP, EXP items, EXP groups etc for bug 2133056 */
OPEN group_name_cur;
delete gms_award_distributions
where document_type = 'EXP'
and expenditure_item_id in
( select expenditure_item_id
from pa_expenditure_items_all
where transaction_source = 'GOLD'
and expenditure_id in
(select expenditure_id
from pa_expenditures_all
where expenditure_group = l_exp_group_name));
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gms_award_distributions count: '||sql%rowcount);
delete pa_expenditure_items_all
where transaction_source in ('OLD','GOLD')
and expenditure_id in
(select expenditure_id
from pa_expenditures_all
where expenditure_group = l_exp_group_name);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_expenditure_items_all count: '||sql%rowcount);
delete pa_expenditures_all
where expenditure_group = l_exp_group_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_expenditures_all count: '||sql%rowcount);
delete pa_expenditure_Groups_all
where expenditure_group = l_exp_group_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_expenditure_Groups_all count: '||sql%rowcount);
delete gms_transaction_interface_all
where transaction_source in ('GOLD') and
batch_name = l_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);
delete pa_transaction_interface_all
where transaction_source in ('GOLD','OLD') and
batch_name = l_gms_batch_name;
delete psp_summary_lines
where payroll_control_id in
(select payroll_control_id
from psp_payroll_controls
where source_type = 'A'
and adj_sum_batch_name = p_adj_sum_batch_name);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from psp_summary_lines count: '||sql%rowcount);
update psp_payroll_controls
set gms_phase = null
where gms_phase is not null
and source_type = 'A'
and adj_sum_batch_name = p_adj_sum_batch_name;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_payroll_controls set gms_phase to null count: '||sql%rowcount);
/* 1685685 added update statement...to clean pa tables in cleanup_batch_details..Venkat.*/
update psp_payroll_controls
set gms_phase = 'GMS_Tie_Back'
where payroll_control_id = gms_tie_back_rec.payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_payroll_controls for control_id = '||gms_tie_back_rec.payroll_control_id);
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' before bulk update of psp_summary_lines in tieback - ');
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 = l_txn_source
and PTXN.orig_transaction_reference= to_char(PSL.summary_line_id)
and PTXN.batch_name = l_gms_batch_name1)
WHERE PSL.gms_batch_name = l_gms_batch_name1; --- changed g_gms_batch_name to l_gms_batch_name for 2444657
UPDATE psp_payroll_controls
SET gms_phase = 'GMS_Tie_Back'
WHERE payroll_control_id = l_control_id;
UPDATE psp_adjustment_lines
SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
INSERT INTO psp_adjustment_lines_history
(adjustment_line_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_org_account_id,suspense_reason_code,effort_report_id,version_num,
summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
orig_source_type,
orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
attribute12,attribute13,attribute14,attribute15,last_update_date,
last_updated_by,last_update_login,created_by,creation_date, business_group_id,
adj_set_number, line_number) --- added cols 2634557 DA Multiple element Enh
SELECT adjustment_line_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_org_account_id,suspense_reason_code,effort_report_id,version_num,
summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
orig_source_type,
orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
attribute12,attribute13,attribute14,attribute15,SYSDATE,FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,FND_GLOBAL.USER_ID,SYSDATE, business_group_id,
adj_set_number, line_number --- added cols 2634557 DA Multiple element Enh
FROM psp_adjustment_lines
WHERE status_code = 'A'
AND summary_line_id = l_summary_line_id
AND payroll_control_id = gms_tie_back_rec.payroll_control_id;
DELETE FROM psp_adjustment_lines
WHERE status_code = 'A'
AND summary_line_id = l_summary_line_id
AND payroll_control_id = gms_tie_back_rec.payroll_control_id;
DELETE FROM pa_transaction_interface_all
WHERE orig_transaction_reference = to_char(l_summary_line_id)
AND transaction_status_code = 'A'
AND transaction_source = l_txn_source; */
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_summary_lines, psp_adjustment_lines inserted into
psp_adjustment_lines_history then deleted from psp_adjustment_lines for 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 = gms_tie_back_rec.payroll_control_id;
fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_payroll_controls set ogm_dr_amount and ogm_cr_amount for cotrol_id : '||gms_tie_back_rec.payroll_control_id);
PROCEDURE insert_into_pa_interface(
P_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, -- Introduced 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,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 fix 2935850
P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
l_msg_id number(9);
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, -- Introduced 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,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,
UNMATCHED_NEGATIVE_TXN_FLAG)
VALUES(
P_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, -- Introduced 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,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),
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_ADJ','INSERT_INTO_PA_INTERFACE');
select orig_source_type,
orig_line_id
from psp_adjustment_lines pal,
psp_payroll_controls ppc
WHERE ppc.source_type ='A'
AND ppc.batch_name = p_batch_name
AND pal.payroll_control_id = ppc.payroll_control_id
AND pal.reversal_entry_flag IS NULL
UNION ALL -- added hist table for 2133056
SELECT palh.orig_source_type,
palh.orig_line_id
FROM psp_adjustment_lines_history palh,
psp_payroll_controls ppc
WHERE palh.reversal_entry_flag IS NULL
AND ppc.source_type ='A'
AND ppc.batch_name = p_batch_name
AND palh.payroll_control_id = ppc.payroll_control_id
AND palh.reversal_entry_flag IS NULL;
SELECT gl_phase, gms_phase, batch_name
FROM psp_payroll_controls
WHERE payroll_control_id = p_payroll_control_id;
select expenditure_group
from pa_expenditures_all
where expenditure_id in
(select expenditure_id
from pa_transaction_interface_all
where transaction_source in ('OLD', 'GOLD') and
transaction_status_code = 'A' and
-- batch_name = g_gms_batch_name and
batch_name IN (SELECT gms_batch_name FROM psp_summary_lines WHERE payroll_control_id = p_payroll_control_id) and
rownum = 1);
delete gms_award_distributions
where document_type = 'EXP'
and expenditure_item_id in
(select expenditure_item_id
from pa_expenditure_items_all
where transaction_source = 'GOLD'
and orig_transaction_reference in
(select to_char(summary_line_id)
from psp_summary_lines
where payroll_control_id in
(select payroll_control_id
from psp_payroll_controls
where batch_name = l_batch_name
and source_type='A'))); ----- Added this delete for Bug 2133056
delete from pa_expenditure_items_all
where transaction_source in ('OLD','GOLD')
and orig_transaction_reference in
(select to_char(summary_line_id)
from psp_summary_lines
where payroll_control_id in
(select payroll_control_id
from psp_payroll_controls
where batch_name = l_batch_name
and source_type='A')); --Added for bug 1685685
delete gms_transaction_interface_all
where transaction_source in ('GOLD') and
batch_name = g_gms_batch_name and
orig_transaction_reference in
(select to_char(summary_line_id)
from psp_summary_lines
where payroll_control_id in
(select payroll_control_id
from psp_payroll_controls
where batch_name = l_batch_name
and source_type = 'A'));
delete pa_expenditures_all EXP
where EXP.expenditure_id in
(select XFACE.expenditure_id
from pa_transaction_interface_all XFACE
where XFACE.transaction_source in('OLD','GOLD')
and XFACE.orig_transaction_reference in
(select to_char(PSL.summary_line_id)
from psp_summary_lines PSL
where PSL.payroll_control_id in
(select PPC.payroll_control_id
from psp_payroll_controls PPC
where PPC.batch_name=l_batch_name
and PPC.source_type='A')))
and 0 = (select count(*)
from pa_expenditure_items_all ITEMS
where ITEMS.expenditure_id = EXP.expenditure_id);
delete pa_transaction_interface_all
where transaction_source in ('OLD','GOLD') and
batch_name = g_gms_batch_name and
orig_transaction_reference in
(select to_char(summary_line_id)
from psp_summary_lines
where payroll_control_id in
(select payroll_control_id
from psp_payroll_controls
where batch_name = l_batch_name
and source_type = 'A'));
delete pa_expenditure_groups_all
where transaction_source in('OLD','GOLD')
and (0) = (select count(*) from pa_expenditures_all where expenditure_group=l_exp_group_name)
and expenditure_group = l_exp_group_name;
delete from gl_interface
where user_je_source_name = l_user_je_source_name
and group_id = p_group_id ;
delete from gl_interface_control
where je_source_name = l_user_je_source_name
and group_id = p_group_id;
update psp_distribution_lines_history
set adjustment_batch_name = NULL
where distribution_line_id = adj_batch_rec.orig_line_id;
update psp_pre_gen_dist_lines_history
set adjustment_batch_name = NULL
where pre_gen_dist_line_id = adj_batch_rec.orig_line_id;
update psp_adjustment_lines_history
set adjustment_batch_name = NULL
where adjustment_line_id = adj_batch_rec.orig_line_id;
delete from psp_adjustment_lines_history
where payroll_control_id in --- Corrected this statment 2133056
( SELECT ppc.payroll_control_id
FROM psp_payroll_controls ppc
WHERE ppc.batch_name = l_batch_name
AND ppc.source_type = 'A');
delete from psp_adjustment_lines
where batch_name = l_batch_name;
delete from psp_summary_lines
where payroll_control_id in (select payroll_control_id
from psp_payroll_controls
where batch_name = l_batch_name
and source_type = 'A'); --- added this condn 2133056
delete from psp_payroll_controls
where batch_name = l_batch_name and
source_type = 'A'; --- added this condn 2133056
delete from psp_adjustment_control_table
where adjustment_batch_name = l_batch_name;
SELECT distinct batch_name
FROM psp_payroll_controls
WHERE source_type = 'A'
AND adj_sum_batch_name = p_adj_sum_batch_name
AND status_code = 'I';
SELECT payroll_control_id
FROM psp_payroll_controls
WHERE batch_name = p_batch_name
AND status_code = 'I'
AND decode(p_target_name, 'GL', gl_phase, gms_phase) = 'Submitted_Import_Request'
AND source_type = 'A';
SELECT MAX(group_id)
FROM psp_summary_lines
WHERE payroll_control_id = payroll_control_rec.payroll_control_id
AND group_id IS NOT NULL;
SELECT count(*)
FROM gl_interface
WHERE group_id = l_group_id
AND user_je_source_name = 'OLD'
AND status = 'NEW';
SELECT MAX(gms_batch_name)
FROM psp_summary_lines
WHERE payroll_control_id = payroll_control_rec.payroll_control_id
AND gms_batch_name IS NOT NULL;
SELECT count(*)
FROM pa_transaction_interface
WHERE batch_name = TO_CHAR(l_gms_batch_name)
AND transaction_status_code = 'P'
AND transaction_source in ('OLD','GOLD'); --- Added condn for Bug 2133056
SELECT count(*)
FROM pa_transaction_interface_all
WHERE batch_name = TO_CHAR(l_gms_batch_name)
AND transaction_source in ('OLD','GOLD')
AND transaction_status_code = 'I'; --- Added condn for Bug 2133056
update psp_payroll_controls
set gl_phase = 'Summarize_GL_Lines'
where gl_phase = 'Submitted_Import_Request'
and payroll_control_id = payroll_control_rec.payroll_control_id;
delete from gl_interface
where group_id = l_group_id
and user_je_source_name = 'OLD';
delete from gl_interface_control
where group_id = l_group_id
and je_source_name = 'OLD';
update psp_payroll_controls
set gms_phase = NULL
where payroll_control_id = payroll_control_rec.payroll_control_id
and gms_phase = 'Submitted_Import_Request';
delete from pa_transaction_interface_all
where batch_name = to_char(l_gms_batch_name)
and transaction_source in ('GOLD', 'OLD');
delete from gms_transaction_interface_all
where batch_name = to_char(l_gms_batch_name)
and transaction_source = 'GOLD';
delete from psp_summary_lines
where payroll_control_id = payroll_control_rec.payroll_control_id
and gms_batch_name = l_gms_batch_name;
SELECT adjustment_batch_name, person_id, assignment_id, element_type_id,
distribution_start_date, distribution_end_date,
currency_code -- Introduced for bug fix 2916848
FROM psp_adjustment_control_table
WHERE adjustment_batch_name = p_batch_name;
SELECT substr(full_name, 1, 80)
FROM per_people_f ppf1
WHERE person_id = l_person_id
AND trunc(sysdate) BETWEEN effective_start_date and effective_end_date ;
SELECT assignment_number
FROM per_all_assignments_f paf1
WHERE assignment_id = l_assignment_id
AND effective_start_date = (SELECT MAX(effective_start_date)
FROM per_all_assignments_f paf2
WHERE paf2.assignment_id = l_assignment_id
AND paf2.effective_start_date < trunc(sysdate));
SELECT element_name
FROM pay_element_types_f pet1
WHERE element_type_id = l_element_type_id
AND effective_start_date = (SELECT MAX(effective_start_date)
FROM pay_element_types_f pet2
WHERE pet2.element_type_id = l_element_type_id
AND pet2.effective_start_date < trunc(sysdate));
PROCEDURE insert_into_psp_stout(
P_MSG IN VARCHAR2) IS
l_msg_id number(9);
SELECT PSP_STOUT_S.NEXTVAL
INTO l_msg_id
FROM DUAL;
INSERT INTO PSP_STOUT(
MSG_ID,
MSG)
VALUES(
l_msg_id,
P_MSG);