[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pdl.summary_line_id, psl.summary_amount,
sum(pdl.distribution_amount)
FROM psp_distribution_lines pdl,
psp_summary_lines psl
WHERE pdl.summary_line_id = psl.summary_line_id
AND psl.payroll_control_id = p_payroll_control_id
AND psl.status_code = 'N'
AND psl.attribute29 is not null
ANd psl.attribute30 is not null
GROUP BY pdl.summary_line_id, psl.summary_amount
HAVING summary_amount - sum(distribution_amount) <> 0;
SELECT ppgl.summary_line_id, psl.summary_amount,
sum(ppgl.distribution_amount)
FROM psp_pre_gen_dist_lines ppgl,
psp_summary_lines psl
WHERE ppgl.summary_line_id = psl.summary_line_id
AND psl.payroll_control_id = p_payroll_control_id
AND psl.status_code = 'N'
AND psl.attribute29 is not null
ANd psl.attribute30 is not null
GROUP BY ppgl.summary_line_id, psl.summary_amount
HAVING summary_amount - sum(distribution_amount) <> 0;
SELECT to_number(attribute30) old_summary_amount,
attribute29 old_summary_line_id,
sum(summary_amount) new_summary_amount,
max(summary_line_id) new_summary_line_id
FROM psp_summary_lines
WHERE attribute29 is not null
AND attribute30 is not null
AND gms_batch_name = p_gms_batch_name
AND status_code <> 'S'
GROUP BY attribute29, attribute30
HAVING sum(summary_amount) <> to_number(attribute30);
SELECT default_dist_award_id
INTO l_default_dist_award_id
FROM gms_implementations;
hr_utility.trace('Actual Tieback userhook before inserting into Summary lines');
INSERT INTO psp_summary_lines
( SUMMARY_LINE_ID,
SOURCE_TYPE,
SOURCE_CODE,
TIME_PERIOD_ID,
INTERFACE_BATCH_NAME,
PERSON_ID,
ASSIGNMENT_ID,
EFFECTIVE_DATE,
PAYROLL_CONTROL_ID,
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
SUMMARY_AMOUNT,
DR_CR_FLAG,
GROUP_ID,
INTERFACE_STATUS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
SET_OF_BOOKS_ID,
BUSINESS_GROUP_ID,
STATUS_CODE,
GMS_BATCH_NAME,
GMS_POSTING_EFFECTIVE_DATE,
EXPENDITURE_ENDING_DATE ,
EXPENDITURE_ID,
INTERFACE_ID,
EXPENDITURE_ITEM_ID,
TXN_INTERFACE_ID,
ACTUAL_SUMMARY_AMOUNT,
ACCOUNTING_DATE,
EXCHANGE_RATE_TYPE)
SELECT
PSP_SUMMARY_LINES_S.NEXTVAL,
psl.SOURCE_TYPE,
psl.SOURCE_CODE,
psl.TIME_PERIOD_ID,
psl.INTERFACE_BATCH_NAME,
psl.PERSON_ID,
psl.ASSIGNMENT_ID,
psl.EFFECTIVE_DATE,
psl.PAYROLL_CONTROL_ID,
psl.GL_CODE_COMBINATION_ID,
psl.PROJECT_ID,
psl.EXPENDITURE_ORGANIZATION_ID,
psl.EXPENDITURE_TYPE,
psl.TASK_ID,
gti.AWARD_ID,
pti.denom_raw_cost, -- putting new value in the summary_amount column
psl.DR_CR_FLAG,
psl.GROUP_ID,
psl.INTERFACE_STATUS,
psl.ATTRIBUTE_CATEGORY,
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.SUMMARY_LINE_ID, -- storing in attribute29
psl.SUMMARY_AMOUNT, -- storing in attribute30
psl.LAST_UPDATE_DATE,
psl.LAST_UPDATED_BY,
psl.LAST_UPDATE_LOGIN,
psl.CREATED_BY,
psl.CREATION_DATE,
psl.SET_OF_BOOKS_ID,
psl.BUSINESS_GROUP_ID,
psl.STATUS_CODE,
psl.GMS_BATCH_NAME,
psl.GMS_POSTING_EFFECTIVE_DATE,
psl.EXPENDITURE_ENDING_DATE ,
psl.EXPENDITURE_ID,
psl.INTERFACE_ID,
psl.EXPENDITURE_ITEM_ID,
pti.TXN_INTERFACE_ID, -- insert the new txn_reference_id from pa_transaction_interface_all
psl.ACTUAL_SUMMARY_AMOUNT,
psl.ACCOUNTING_DATE,
psl.EXCHANGE_RATE_TYPE
FROM pa_transaction_interface_all pti,
gms_transaction_interface_all gti,
psp_summary_lines psl
WHERE pti.txn_interface_id = gti.txn_interface_id
AND pti.orig_transaction_reference = psl.summary_line_id
AND psl.award_id = l_default_dist_award_id
AND pti.batch_name = p_gms_batch_name
AND pti.transaction_source = 'GOLD';
UPDATE psp_summary_lines
SET status_code = 'S'
WHERE award_id = l_default_dist_award_id
AND summary_line_id IN (SELECT orig_transaction_reference
FROM pa_transaction_interface_all pti
WHERE pti.batch_name = p_gms_batch_name
AND pti.transaction_source = 'GOLD');
update psp_summary_lines
set summary_amount = summary_split_rec.old_summary_amount - summary_split_rec.new_summary_amount + summary_amount
where summary_line_id = summary_split_rec.new_summary_line_id;
UPDATE pa_transaction_interface_all pti
SET pti.orig_transaction_reference = (SELECT summary_line_id
FROM psp_summary_lines psl
WHERE psl.txn_interface_id = pti.txn_interface_id
AND psl.status_code = 'N'
AND psl.gms_batch_name = p_gms_batch_name)
WHERE pti.batch_name = p_gms_batch_name
AND pti.transaction_source = 'GOLD'
AND pti.orig_transaction_reference in (SELECT to_char(summary_line_id)
FROM psp_summary_lines
WHERE status_code = 'S'
AND gms_batch_name = p_gms_batch_name) ;
INSERT INTO PSP_DISTRIBUTION_LINES(
DISTRIBUTION_LINE_ID,
DISTRIBUTION_DATE,
EFFECTIVE_DATE,
DISTRIBUTION_AMOUNT,
STATUS_CODE,
DEFAULT_REASON_CODE,
SUSPENSE_REASON_CODE,
EFFORT_REPORT_ID,
PAYROLL_SUB_LINE_ID,
SCHEDULE_LINE_ID,
DEFAULT_ORG_ACCOUNT_ID,
SUSPENSE_ORG_ACCOUNT_ID,
ELEMENT_ACCOUNT_ID,
ORG_SCHEDULE_ID,
GL_PROJECT_FLAG,
REVERSAL_ENTRY_FLAG,
PRE_DISTRIBUTION_RUN_FLAG,
SUMMARY_LINE_ID,
SET_OF_BOOKS_ID,
VERSION_NUM,
USER_DEFINED_FIELD,
AUTO_EXPENDITURE_TYPE,
AUTO_GL_CODE_COMBINATION_ID,
BUSINESS_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
CAP_EXCESS_DIST_LINE_ID,
CAP_EXCESS_GLCCID,
CAP_EXCESS_AWARD_ID,
CAP_EXCESS_PROJECT_ID,
CAP_EXCESS_TASK_ID,
CAP_EXCESS_EXP_ORG_ID,
CAP_EXCESS_EXP_TYPE,
FUNDING_SOURCE_CODE,
ANNUAL_SALARY_CAP,
SUSPENSE_AUTO_GLCCID,
SUSPENSE_AUTO_EXP_TYPE,
ADJ_ACCOUNT_FLAG)
SELECT
PSP_DISTRIBUTION_LINES_S.NEXTVAL,
pdl.DISTRIBUTION_DATE,
pdl.EFFECTIVE_DATE,
decode(psl.attribute30,0,0,((pdl.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT)/psl.ATTRIBUTE30)),
--pdl.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT/psl.ATTRIBUTE30, --Bug 12761630
pdl.STATUS_CODE,
pdl.DEFAULT_REASON_CODE,
pdl.SUSPENSE_REASON_CODE,
pdl.EFFORT_REPORT_ID,
pdl.PAYROLL_SUB_LINE_ID,
pdl.SCHEDULE_LINE_ID,
pdl.DEFAULT_ORG_ACCOUNT_ID,
pdl.SUSPENSE_ORG_ACCOUNT_ID,
pdl.ELEMENT_ACCOUNT_ID,
pdl.ORG_SCHEDULE_ID,
pdl.GL_PROJECT_FLAG,
pdl.REVERSAL_ENTRY_FLAG,
pdl.PRE_DISTRIBUTION_RUN_FLAG,
psl.SUMMARY_LINE_ID,
pdl.SET_OF_BOOKS_ID,
pdl.VERSION_NUM,
pdl.USER_DEFINED_FIELD,
pdl.AUTO_EXPENDITURE_TYPE,
pdl.AUTO_GL_CODE_COMBINATION_ID,
pdl.BUSINESS_GROUP_ID,
pdl.ATTRIBUTE_CATEGORY,
pdl.ATTRIBUTE1,
pdl.ATTRIBUTE2,
pdl.ATTRIBUTE3,
pdl.ATTRIBUTE4,
pdl.ATTRIBUTE5,
pdl.ATTRIBUTE6,
pdl.ATTRIBUTE7,
pdl.ATTRIBUTE8,
pdl.ATTRIBUTE9,
pdl.DISTRIBUTION_LINE_ID, -- storing in attribute10
pdl.CAP_EXCESS_DIST_LINE_ID,
pdl.CAP_EXCESS_GLCCID,
pdl.CAP_EXCESS_AWARD_ID,
pdl.CAP_EXCESS_PROJECT_ID,
pdl.CAP_EXCESS_TASK_ID,
pdl.CAP_EXCESS_EXP_ORG_ID,
pdl.CAP_EXCESS_EXP_TYPE,
pdl.FUNDING_SOURCE_CODE,
pdl.ANNUAL_SALARY_CAP,
pdl.SUSPENSE_AUTO_GLCCID,
pdl.SUSPENSE_AUTO_EXP_TYPE,
pdl.ADJ_ACCOUNT_FLAG
FROM psp_distribution_lines pdl,
psp_summary_lines psl
WHERE pdl.summary_line_id = psl.attribute29
AND psl.gms_batch_name = p_gms_batch_name
AND (source_type = 'O'
OR source_type = 'N');
UPDATE psp_distribution_lines pdl
SET distribution_amount = distribution_amount+l_sum_summary_amt-l_sum_dist_amt
WHERE distribution_line_id = (SELECT max(distribution_line_id)
FROM psp_distribution_lines pdl1
WHERE pdl.summary_line_id = l_summary_line_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,
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,
USER_DEFINED_FIELD,
BUSINESS_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
SUSPENSE_AUTO_GLCCID,
SUSPENSE_AUTO_EXP_TYPE)
SELECT
PSP_DISTRIBUTION_LINES_S.NEXTVAL,
ppgl.DISTRIBUTION_INTERFACE_ID,
ppgl.PERSON_ID,
ppgl.ASSIGNMENT_ID,
ppgl.ELEMENT_TYPE_ID,
ppgl.DISTRIBUTION_DATE,
ppgl.EFFECTIVE_DATE,
--ppgl.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT/psl.ATTRIBUTE30, --Bug 12761630
decode(psl.attribute30,0,0,((ppgl.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT)/psl.ATTRIBUTE30)),
ppgl.DR_CR_FLAG,
ppgl.PAYROLL_CONTROL_ID,
ppgl.SOURCE_TYPE,
ppgl.SOURCE_CODE,
ppgl.TIME_PERIOD_ID,
ppgl.BATCH_NAME,
ppgl.STATUS_CODE,
ppgl.SET_OF_BOOKS_ID,
ppgl.GL_CODE_COMBINATION_ID,
ppgl.PROJECT_ID,
ppgl.EXPENDITURE_ORGANIZATION_ID,
ppgl.EXPENDITURE_TYPE,
ppgl.TASK_ID,
psl.AWARD_ID, -- update the new award_id
ppgl.SUSPENSE_ORG_ACCOUNT_ID,
ppgl.SUSPENSE_REASON_CODE,
ppgl.EFFORT_REPORT_ID,
ppgl.VERSION_NUM,
psl.SUMMARY_LINE_ID, -- update the new summary_line_id
ppgl.REVERSAL_ENTRY_FLAG,
ppgl.USER_DEFINED_FIELD,
ppgl.BUSINESS_GROUP_ID,
ppgl.ATTRIBUTE_CATEGORY,
ppgl.ATTRIBUTE1,
ppgl.ATTRIBUTE2,
ppgl.ATTRIBUTE3,
ppgl.ATTRIBUTE4,
ppgl.ATTRIBUTE5,
ppgl.ATTRIBUTE6,
ppgl.ATTRIBUTE7,
ppgl.ATTRIBUTE8,
ppgl.PRE_GEN_DIST_LINE_ID, -- attribute9
ppgl.DISTRIBUTION_AMOUNT, -- attribute10
ppgl.SUSPENSE_AUTO_GLCCID,
ppgl.SUSPENSE_AUTO_EXP_TYPE
FROM psp_pre_gen_dist_lines ppgl,
psp_summary_lines psl
WHERE ppgl.summary_line_id = psl.attribute29
AND psl.payroll_control_id = p_payroll_control_id
AND psl.source_type = 'P';
UPDATE psp_pre_gen_dist_lines
SET distribution_amount = distribution_amount+l_pg_sum_summary_amt-l_pg_sum_dist_amt
WHERE pre_gen_dist_line_id = (SELECT max(pre_gen_dist_line_id)
FROM psp_pre_gen_dist_lines
WHERE summary_line_id = l_pg_summary_line_id);
DELETE psp_distribution_lines
WHERE summary_line_id in
(select summary_line_id
from psp_summary_lines
where gms_batch_name = p_gms_batch_name
and award_id = l_default_dist_award_id
and status_code = 'S');
DELETE psp_pre_gen_dist_lines
WHERE summary_line_id in
(select summary_line_id
from psp_summary_lines
where gms_batch_name = p_gms_batch_name
and award_id = l_default_dist_award_id
and status_code = 'S');
DELETE psp_summary_lines
WHERE gms_batch_name = p_gms_batch_name
and status_code = 'S';
SELECT pal.summary_line_id, psl.summary_amount, sum(pal.distribution_amount)
FROM psp_adjustment_lines pal,
psp_summary_lines psl
WHERE pal.summary_line_id = psl.summary_line_id
AND psl.payroll_control_id = p_payroll_control_id
AND psl.status_code = 'N'
AND psl.attribute29 is not null
ANd psl.attribute30 is not null
GROUP BY pal.summary_line_id, psl.summary_amount
HAVING summary_amount - sum(distribution_amount) <> 0;
SELECT to_number(attribute30) old_summary_amount,
attribute29 old_summary_line_id,
sum(summary_amount) new_summary_amount,
max(summary_line_id) new_summary_line_id
FROM psp_summary_lines
WHERE attribute29 is not null
AND attribute30 is not null
AND gms_batch_name = p_gms_batch_name
AND status_code <> 'S'
GROUP BY attribute29, attribute30
HAVING sum(summary_amount) <> to_number(attribute30);
SELECT default_dist_award_id
INTO l_default_dist_award_id
FROM gms_implementations;
INSERT INTO psp_summary_lines
(SUMMARY_LINE_ID,
SOURCE_TYPE,
SOURCE_CODE,
TIME_PERIOD_ID,
INTERFACE_BATCH_NAME,
PERSON_ID,
ASSIGNMENT_ID,
EFFECTIVE_DATE,
PAYROLL_CONTROL_ID,
GL_CODE_COMBINATION_ID,
PROJECT_ID,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
TASK_ID,
AWARD_ID,
SUMMARY_AMOUNT,
DR_CR_FLAG,
GROUP_ID,
INTERFACE_STATUS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
SET_OF_BOOKS_ID,
BUSINESS_GROUP_ID,
STATUS_CODE,
GMS_BATCH_NAME,
GMS_POSTING_EFFECTIVE_DATE,
EXPENDITURE_ENDING_DATE ,
EXPENDITURE_ID,
INTERFACE_ID,
EXPENDITURE_ITEM_ID,
TXN_INTERFACE_ID,
ACTUAL_SUMMARY_AMOUNT,
ACCOUNTING_DATE,
EXCHANGE_RATE_TYPE)
SELECT
PSP_SUMMARY_LINES_S.NEXTVAL,
psl.SOURCE_TYPE,
psl.SOURCE_CODE,
psl.TIME_PERIOD_ID,
psl.INTERFACE_BATCH_NAME,
psl.PERSON_ID,
psl.ASSIGNMENT_ID,
psl.EFFECTIVE_DATE,
psl.PAYROLL_CONTROL_ID,
psl.GL_CODE_COMBINATION_ID,
psl.PROJECT_ID,
psl.EXPENDITURE_ORGANIZATION_ID,
psl.EXPENDITURE_TYPE,
psl.TASK_ID,
gti.AWARD_ID,
pti.denom_raw_cost, -- putting new value in the summary_amount column
psl.DR_CR_FLAG,
psl.GROUP_ID,
psl.INTERFACE_STATUS,
psl.ATTRIBUTE_CATEGORY,
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.SUMMARY_LINE_ID, -- storing in attribute29
psl.SUMMARY_AMOUNT, -- storing in attribute30
psl.LAST_UPDATE_DATE,
psl.LAST_UPDATED_BY,
psl.LAST_UPDATE_LOGIN,
psl.CREATED_BY,
psl.CREATION_DATE,
psl.SET_OF_BOOKS_ID,
psl.BUSINESS_GROUP_ID,
psl.STATUS_CODE,
psl.GMS_BATCH_NAME,
psl.GMS_POSTING_EFFECTIVE_DATE,
psl.EXPENDITURE_ENDING_DATE ,
psl.EXPENDITURE_ID,
psl.INTERFACE_ID,
psl.EXPENDITURE_ITEM_ID,
pti.TXN_INTERFACE_ID, -- insert the new txn_reference_id from pa_transaction_interface_all
psl.ACTUAL_SUMMARY_AMOUNT,
psl.ACCOUNTING_DATE,
psl.EXCHANGE_RATE_TYPE
FROM pa_transaction_interface_all pti,
gms_transaction_interface_all gti,
psp_summary_lines psl
WHERE pti.txn_interface_id = gti.txn_interface_id
AND pti.orig_transaction_reference = psl.summary_line_id
AND psl.award_id = l_default_dist_award_id
AND pti.batch_name = p_gms_batch_name
AND pti.transaction_source = 'GOLD';
UPDATE psp_summary_lines
SET status_code = 'S'
WHERE award_id = l_default_dist_award_id
AND summary_line_id IN (SELECT orig_transaction_reference
FROM pa_transaction_interface_all pti
WHERE pti.batch_name = p_gms_batch_name
AND pti.transaction_source = 'GOLD');
update psp_summary_lines
set summary_amount = summary_split_rec.old_summary_amount - summary_split_rec.new_summary_amount + summary_amount
where summary_line_id = summary_split_rec.new_summary_line_id;
hr_utility.trace('Update of pa xface table');
UPDATE pa_transaction_interface_all pti
SET pti.orig_transaction_reference = (SELECT summary_line_id FROM psp_summary_lines psl
WHERE psl.txn_interface_id = pti.txn_interface_id
AND psl.status_code = 'N'
AND psl.gms_batch_name = p_gms_batch_name)
WHERE pti.batch_name = p_gms_batch_name
AND pti.transaction_source = 'GOLD'
AND pti.orig_transaction_reference in (SELECT to_char(summary_line_id)
FROM psp_summary_lines
WHERE status_code = 'S'
AND gms_batch_name = p_gms_batch_name) ;
INSERT INTO PSP_ADJUSTMENT_LINES(
ADJUSTMENT_LINE_ID ,
PERSON_ID ,
ASSIGNMENT_ID ,
ELEMENT_TYPE_ID ,
DISTRIBUTION_DATE ,
EFFECTIVE_DATE ,
DISTRIBUTION_AMOUNT ,
DR_CR_FLAG ,
SOURCE_CODE ,
SOURCE_TYPE ,
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 ,
PAYROLL_CONTROL_ID ,
BUSINESS_GROUP_ID)
SELECT
PSP_ADJUSTMENT_LINES_S.NEXTVAL,
pal.PERSON_ID ,
pal.ASSIGNMENT_ID ,
pal.ELEMENT_TYPE_ID ,
pal.DISTRIBUTION_DATE ,
pal.EFFECTIVE_DATE ,
--pal.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT/psl.ATTRIBUTE30, --Bug 12761630
decode(psl.attribute30,0,0,((pal.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT)/psl.ATTRIBUTE30)),
pal.DR_CR_FLAG ,
pal.SOURCE_CODE ,
pal.SOURCE_TYPE ,
pal.TIME_PERIOD_ID ,
pal.BATCH_NAME ,
pal.STATUS_CODE ,
pal.SET_OF_BOOKS_ID ,
pal.GL_CODE_COMBINATION_ID ,
pal.PROJECT_ID ,
pal.EXPENDITURE_ORGANIZATION_ID,
pal.EXPENDITURE_TYPE ,
pal.TASK_ID ,
psl.AWARD_ID , --- new award_id
pal.SUSPENSE_ORG_ACCOUNT_ID,
pal.SUSPENSE_REASON_CODE ,
pal.EFFORT_REPORT_ID ,
pal.VERSION_NUM ,
psl.SUMMARY_LINE_ID , -- new sum line_id
pal.REVERSAL_ENTRY_FLAG ,
pal.ORIGINAL_LINE_FLAG ,
pal.USER_DEFINED_FIELD ,
pal.PERCENT ,
pal.ORIG_SOURCE_TYPE ,
pal.ORIG_LINE_ID ,
pal.ATTRIBUTE_CATEGORY ,
pal.ATTRIBUTE1 ,
pal.ATTRIBUTE2 ,
pal.ATTRIBUTE3 ,
pal.ATTRIBUTE4 ,
pal.ATTRIBUTE5 ,
pal.ATTRIBUTE6 ,
pal.ATTRIBUTE7 ,
pal.ATTRIBUTE8 ,
pal.ATTRIBUTE9 ,
pal.ATTRIBUTE10 ,
pal.ATTRIBUTE11 ,
pal.ATTRIBUTE12,
pal.ATTRIBUTE13,
pal.adjustment_LINE_ID, --- line_id
pal.distribution_amount, -- dist_amount
pal.LAST_UPDATE_DATE ,
pal.LAST_UPDATED_BY ,
pal.LAST_UPDATE_LOGIN ,
pal.CREATED_BY ,
pal.CREATION_DATE ,
pal.PAYROLL_CONTROL_ID ,
pal.BUSINESS_GROUP_ID
FROM psp_adjustment_lines pal,
psp_summary_lines psl
WHERE pal.summary_line_id = psl.attribute29
AND psl.gms_batch_name = p_gms_batch_name;
UPDATE psp_adjustment_lines pal
SET distribution_amount = distribution_amount+l_sum_summary_amt-l_sum_dist_amt
WHERE adjustment_line_id = (SELECT max(adjustment_line_id)
FROM psp_adjustment_lines pal1
WHERE pal1.summary_line_id = l_summary_line_id);
DELETE psp_adjustment_lines
where summary_line_id IN (SELECT summary_line_id FROM psp_summary_lines
WHERE payroll_control_id = p_payroll_control_id
AND status_code = 'S');
DELETE psp_summary_lines
WHERE status_code = 'S'
and payroll_control_id = p_payroll_control_id;