[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
INTO g_template_row
FROM psp_effort_report_templates
WHERE template_id = a_template_id;
SELECT 'x'
INTO t_dummy
FROM psp_template_projects
WHERE template_id = a_template_id AND
ROWNUM = 1;
SELECT 'x'
INTO t_dummy
FROM psp_template_projects
WHERE template_id = a_template_id AND
project_id = t_project_id;
SELECT 'x'
INTO t_dummy
FROM psp_template_awards
WHERE template_id = a_template_id AND
ROWNUM = 1;
SELECT 'x'
INTO t_dummy
FROM psp_template_awards
WHERE template_id = a_template_id AND
award_id = t_award_id;
SELECT 'x'
INTO t_dummy
FROM psp_template_organizations
WHERE template_id = a_template_id AND
ROWNUM = 1;
SELECT 'x'
INTO t_dummy
FROM psp_template_organizations
WHERE template_id = a_template_id AND
expenditure_organization_id = t_expenditure_organization_id;
SELECT gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id
INTO t_poeta
FROM psp_organization_accounts
WHERE organization_account_id = NVL(ssdseo.suspense_org_account_id,-955);
SELECT gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id
INTO t_poeta
FROM psp_organization_accounts
WHERE organization_account_id = NVL(ssdseo.default_org_account_id,-955);
SELECT gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id
INTO t_poeta
FROM psp_schedule_lines
WHERE schedule_line_id = NVL(ssdseo.schedule_line_id,-955);
SELECT gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id
INTO t_poeta
FROM psp_element_type_accounts
WHERE element_account_id = NVL(ssdseo.element_account_id,-955);
SELECT gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id
INTO t_poeta
FROM psp_default_labor_schedules
WHERE org_schedule_id = NVL(ssdseo.org_schedule_id,-955);
log_errors(template_id1,person_id1,'GL_POETA Selection Criteria failed' ,NULL,NULL);
SELECT 'x'
INTO t_dummy
FROM psp_effort_report_elements
WHERE element_type_id = element_type_id1
-- Introduced BG/SOB check for bug fix 3098050
AND business_group_id = p_business_group_id
AND set_of_books_id = p_set_of_books_id
AND NVL(use_in_effort_report,'N') = 'Y'
AND ROWNUM = 1;
SELECT 'x'
INTO t_dummy
FROM psp_effort_report_details
WHERE effort_report_id = effort_report_id1 AND
version_num = version_number1 AND
ROWNUM = 1;
PROCEDURE p_old_adhoc_delete(template_id1 IN NUMBER, person_id1 IN NUMBER,
begin_date1 IN DATE, end_date1 IN DATE)
--- The following private procedure checks to see whether an earlier Adhoc report(s) exists
--- for a given Adhoc report, with an exact match of Person_id, Start date And End
--- Date. If, yes, it deletes all record(s) from all concerned tables.
IS
CURSOR t_old_templates(template_id2 IN NUMBER, person_id2 IN NUMBER,
begin_date2 IN DATE, end_date2 IN DATE) IS
SELECT distinct E.template_id
FROM PSP_EFFORT_REPORTS E,PSP_EFFORT_REPORT_TEMPLATES T
WHERE E.person_id = person_id2 AND
E.template_id = T.template_id AND
begin_date2 = T.begin_date AND
end_date2 = T.end_date AND
T.report_type = 'A'
MINUS
SELECT template_id2
FROM DUAL;
PSP_TEMPLATE_AWARDS_PKG.delete_row(t_old_template_id);
PSP_TEMPLATE_PROJECTS_PKG.delete_row(t_old_template_id);
PSP_TEMPLATE_ORGANIZATIONS_PKG.delete_row(t_old_template_id);
PSP_EFT_REPORT_TEMPLATES_PKG.delete_row(t_old_template_id);
DELETE FROM PSP_EFFORT_REPORT_DETAILS D
WHERE exists ( SELECT 'x'
FROM PSP_EFFORT_REPORTS R
WHERE R.effort_report_id = D.effort_report_id AND
R.version_num = D.version_num AND
template_id = t_old_template_id );
DELETE FROM PSP_EFFORT_REPORTS
WHERE template_id = t_old_template_id;
SELECT *
INTO g_details_row
FROM psp_effort_report_details
WHERE effort_report_id = effort_report_id1 AND
version_num = version_num1 AND
assignment_id = assignment_id1 AND
element_type_id = element_type_id1 AND
NVL(gl_code_combination_id,-99) = NVL(gl_poeta1.gl_code_combination_id,-99) AND
NVL(project_id,-99) = NVL(gl_poeta1.project_id,-99) AND
NVL(expenditure_organization_id,-99) = NVL(gl_poeta1.expenditure_organization_id,-99) AND
NVL(expenditure_type,'-99') = NVL(gl_poeta1.expenditure_type,'-99') AND
NVL(task_id,-99) = NVL(gl_poeta1.task_id,-99) AND
NVL(award_id,-99) = NVL(gl_poeta1.award_id,-99) AND
ROWNUM = 1;
UPDATE PSP_EFFORT_REPORT_DETAILS
SET total_amount = g_details_row.TOTAL_AMOUNT
WHERE effort_report_id = g_details_row.effort_report_id AND
version_num = g_details_row.version_num AND
effort_report_line_num = g_details_row.effort_report_line_num;
INSERT INTO PSP_EFFORT_ERRORS(template_id,person_id,message,prev_effort_report_id,
prev_version_num) values (template_id1,person_id1,errmsg,effort_report_id1,version_num1);
UPDATE PSP_EFFORT_REPORT_TEMPLATES
SET error_date_time = SYSDATE
WHERE template_id = template_id1;
SELECT D.distribution_line_id, --- N(10)
L.person_id person_id, --- N(9)
L.assignment_id assignment_id, --- N(9)
L.element_type_id, --- N(9)
D.distribution_date, --- Date
NVL(D.distribution_amount,0) distribution_amount, --- N(22)
D.effort_report_id, --- N(9)
D.status_code, --- N(9)
---D.version_num, --- N(9)
NVL(AUTO_GL_CODE_COMBINATION_ID, TO_NUMBER(NULL)) gl_code_combination_id, -- Ravindra
TO_NUMBER(NULL) project_id,
TO_NUMBER(NULL) expenditure_organization_id,
NVL(AUTO_EXPENDITURE_TYPE, NULL) expenditure_type, -- Ravindra
TO_NUMBER(NULL) task_id,
TO_NUMBER(NULL) award_id,
D.schedule_line_id, --- N(9)
D.summary_line_id, --- N(10)
D.default_org_account_id, --- N(9)
D.suspense_org_account_id, --- N(9)
element_account_id, --- N(9)
org_schedule_id, --- N(9)
'distribution_lines' source
FROM psp_distribution_lines_history D,psp_payroll_sub_lines S, psp_payroll_lines L
WHERE D.payroll_sub_line_id = S.payroll_sub_line_id AND
S.payroll_line_id = L.payroll_line_id AND
D.distribution_DATE between sdate AND edate AND
L.person_id = person_id1 AND
NVL(include_in_er_flag,'Y') = 'Y' AND
NVL(reversal_entry_flag,'N') = 'N'
UNION
SELECT pre_gen_dist_line_id,
person_id, --- N(9)
assignment_id, --- N(9)
element_type_id,
distribution_date,
NVL(distribution_amount,0), --- N(22)
effort_report_id,
status_code,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
TO_NUMBER(NULL), --- N(9)
TO_NUMBER(NULL), --- N(10)
TO_NUMBER(NULL), --- N(9)
TO_NUMBER(NULL), --- N(9)
TO_NUMBER(NULL), --- N(9)
TO_NUMBER(NULL), --- N(9)
'pre_gen_dist_lines'
FROM psp_pre_gen_dist_lines_history
WHERE distribution_date between sdate AND edate AND
person_id = person_id1 AND
NVL(include_in_er_flag,'Y') = 'Y' AND
NVL(reversal_entry_flag,'N') = 'N'
UNION
SELECT adjustment_line_id,
person_id, --- N(9)
assignment_id, --- N(9)
element_type_id,
distribution_date,
NVL(distribution_amount,0), --- N(22)
effort_report_id,
status_code,
gl_code_combination_id,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
TO_NUMBER(NULL), --- N(9)
TO_NUMBER(NULL), --- N(10)
TO_NUMBER(NULL), --- N(9)
TO_NUMBER(NULL), --- N(9)
TO_NUMBER(NULL), --- N(9)
TO_NUMBER(NULL), --- N(9)
'adjustment_lines'
FROM psp_adjustment_lines_history
WHERE distribution_date between sdate AND edate AND
person_id = person_id1 AND
NVL(include_in_er_flag,'Y') = 'Y' AND
NVL(reversal_entry_flag,'N') = 'N'
---ORDER BY person_id,assignment_id; Rel 11 Requirement
SELECT pdlh.distribution_line_id,
psl.person_id,
psl.assignment_id,
ppl.element_type_id,
pdlh.distribution_date,
decode(ppl.dr_cr_flag, 'C', NVL(-pdlh.distribution_amount,0),
nvl(pdlh.distribution_amount,0))distribution_amount, -- bug fix 1952627
pdlh.effort_report_id,
pdlh.status_code,
psl.gl_code_combination_id, ---NVL(AUTO_GL_CODE_COMBINATION_ID, TO_NUMBER(NULL)) gl_code_combination_id,
psl.project_id, ---TO_NUMBER(NULL) project_id, commented for 5080403
psl.expenditure_organization_id, ----TO_NUMBER(NULL) expenditure_organization_id,
psl.expenditure_type, ----NVL(AUTO_EXPENDITURE_TYPE, NULL) expenditure_type,
psl.task_id, ---TO_NUMBER(NULL) task_id,
psl.award_id, ---TO_NUMBER(NULL) award_id,
pdlh.schedule_line_id,
pdlh.summary_line_id,
pdlh.default_org_account_id,
pdlh.suspense_org_account_id,
element_account_id,
org_schedule_id,
'distribution_lines' source
FROM psp_distribution_lines_history pdlh,
psp_payroll_sub_lines pps,
psp_payroll_lines ppl,
psp_summary_lines psl
WHERE
psl.person_id = person_id1 AND
pdlh.summary_line_id = psl.summary_line_id AND
psl.status_code||''='A' and
pdlh.distribution_DATE between sdate AND edate
and NVL(pdlh.include_in_er_flag,'Y') = 'Y' AND
NVL(pdlh.reversal_entry_flag,'N') = 'N' AND
pdlh.adjustment_batch_name is null
and pdlh.payroll_sub_line_id= pps.payroll_sub_line_id
and pps.payroll_line_id=ppl.payroll_line_id
UNION
SELECT ppgh.pre_gen_dist_line_id,
ppgh.person_id,
ppgh.assignment_id,
ppgh.element_type_id,
ppgh.distribution_date,
decode(ppgh.dr_cr_flag,'C', NVL(-ppgh.distribution_amount,0),
nvl(ppgh.distribution_amount,0))distribution_amount,-- bug fix 1952627
ppgh.effort_report_id,
ppgh.status_code,
psl.gl_code_combination_id, --- changed from ppgh prefix to
---psl.. for this line and below 4 lines... for 5080403
psl.project_id,
psl.expenditure_organization_id,
psl.expenditure_type,
psl.task_id,
psl.award_id,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
'pre_gen_dist_lines'
FROM psp_pre_gen_dist_lines_history ppgh,
psp_summary_lines psl
WHERE
psl.person_id=person_id1 and
ppgh.summary_line_id=psl.summary_line_id
and psl.status_code||''='A' and
ppgh.distribution_date between sdate AND edate AND
NVL(ppgh.include_in_er_flag,'Y') = 'Y' AND
NVL(ppgh.reversal_entry_flag,'N') = 'N' AND
ppgh.adjustment_batch_name is null
UNION
SELECT palh.adjustment_line_id,
palh.person_id,
palh.assignment_id,
palh.element_type_id,
palh.distribution_date,
decode(palh.dr_cr_flag, 'C',NVL(-palh.distribution_amount,0),
NVL(palh.distribution_amount, 0))distribution_amount, -- bug fix 1952627
palh.effort_report_id,
palh.status_code,
palh.gl_code_combination_id,
palh.project_id,
palh.expenditure_organization_id,
palh.expenditure_type,
palh.task_id,
palh.award_id,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
'adjustment_lines'
FROM psp_adjustment_lines_history palh,
psp_summary_lines psl
WHERE
psl.person_id=person_id1 and
palh.summary_line_id=psl.summary_line_id and
psl.status_code||''='A' and
palh.distribution_date between sdate AND edate AND
NVL(palh.include_in_er_flag,'Y') = 'Y' AND
NVL(palh.reversal_entry_flag,'N') = 'N' AND
nvl(original_line_flag, 'N') = 'N'
and palh.adjustment_batch_name is null
ORDER BY 2,3;
SELECT distinct F.person_id,to_date(NULL) effective_start_date,to_date(NULL) effective_end_date
FROM psp_effort_report_templates E,
per_assignments_f F
WHERE NVL(E.people_group_id, NVL(F.people_group_id,-99)) = NVL(F.people_group_id,-99) AND
NVL(E.supervisor_id, NVL(F.supervisor_id,-99)) = NVL(F.supervisor_id,-99) AND
E.template_id = a_template_id AND
E.person_id IS NULL AND
exists (SELECT 'x'
FROM PSP_DISTRIBUTION_COMBO_V V
WHERE V.person_id = F.person_id AND
V.distribution_date between E.begin_date and E.end_date )
UNION
SELECT P.person_id,P.effective_start_date,P.effective_end_date
FROM psp_effort_report_templates P
WHERE template_id = a_template_id AND
P.person_id IS NOT NULL AND
exists (SELECT 'x'
FROM PSP_DISTRIBUTION_COMBO_V V
WHERE V.person_id = P.person_id AND
V.distribution_date between P.begin_date and P.end_date ); */
SELECT DISTINCT
psl.person_id,
to_date(NULL)
effective_start_date,
to_date(NULL) effective_end_date
FROM
per_all_assignments_f paf,
psp_Summary_lines psl,
psp_effort_report_templates pet
WHERE
(pet.template_id=a_template_id and
pet.person_id is null and
(
((paf.people_group_id = pet.people_group_id
OR paf.supervisor_id = pet.supervisor_id)
-- Added for Bug Fix 2892637 by tbalacha
AND (paf.effective_start_date <= pet.end_date AND paf.effective_end_date >= pet.begin_date))
--End of code for Bug 2892637
-- Start bug fix:1988747 by Ritesh on Sep 12, 2001
OR psl.gl_code_combination_id = pet.gl_code_combination_id
OR psl.project_id IN (SELECT project_id FROM psp_template_projects
WHERE template_id = a_template_id)
OR psl.award_id IN (SELECT award_id FROM psp_template_awards
WHERE template_id = a_template_id)
OR psl.expenditure_organization_id IN (SELECT expenditure_organization_id FROM psp_template_organizations
WHERE template_id = a_template_id)
-- End bug fix:1988747
)
and
psl.person_id =paf.person_id
AND paf.assignment_type ='E' --Added for bug 2624259.
and psl.status_code||''='A'
and
exists
(
SELECT summary_line_id
FROM psp_distribution_lines_history pdlh
WHERE
pdlh.summary_line_id=psl.summary_line_id and
pdlh.distribution_date
between
pet.begin_date
and
pet.end_date
and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
nvl(REVERSAL_ENTRY_FLAG,'N')='N'
UNION ALL
SELECT summary_line_id
FROM psp_adjustment_lineS_history palh
WHERE
palh.summary_line_id=psl.summary_line_id and
palh.distribution_date
BETWEEN pet.begin_date AND pet.end_date
and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
nvl(REVERSAL_ENTRY_FLAG,'N')='N'
UNION ALL
SELECT summary_line_id
FROM psp_pre_gen_dist_lines_history ppgh where
ppgh.summary_line_id=psl.summary_line_id and
ppgh.distribution_date
BETWEEN pet.begin_date AND pet.end_date
and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
nvl(REVERSAL_ENTRY_FLAG,'N')='N'))
UNION
SELECT
pet.person_id,
pet.effective_start_date,
pet.effective_end_date
FROM psp_Summary_lines psl,
psp_effort_report_templates pet
WHERE
pet.template_id=a_template_id and
pet.person_id=psl.person_id and
pet.person_id is not null
and psl.status_code||''='A'
and exists
(
SELECT summary_line_id
FROM psp_distribution_lines_history pdlh
WHERE
pdlh.summary_line_id=psl.summary_line_id and
pdlh.distribution_date
BETWEEN
pet.begin_date
and
pet.end_date
and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
nvl(REVERSAL_ENTRY_FLAG,'N')='N'
UNION ALL
SELECT summary_line_id
FROM psp_adjustment_lines_history palh
WHERE
palh.summary_line_id=psl.summary_line_id and
palh.distribution_date
BETWEEN pet.begin_date and pet.end_date
and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
nvl(REVERSAL_ENTRY_FLAG,'N')='N'
UNION ALL
SELECT summary_line_id
FROM psp_pre_gen_dist_lines_history ppgh
WHERE
ppgh.summary_line_id=psl.summary_line_id and
ppgh.distribution_date
BETWEEN pet.begin_date AND pet.end_date
and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
nvl(REVERSAL_ENTRY_FLAG,'N')='N');
SELECT version_num + 1,effort_report_id ----NVL(MAX(version_num) + 1,1)
FROM psp_effort_reports P, psp_effort_report_templates T
WHERE P.person_id = person_id1 AND ---person_id has no effective dt in Assignments
P.template_id = T.template_id AND
((begin_date1 BETWEEN T.begin_date AND T.end_date) OR
(end_date1 BETWEEN T.begin_date AND T.end_date) OR
(begin_date1 <= T.begin_date AND end_date1 >= T.end_date)) AND
T.report_type = report_type1
ORDER BY version_num + 1 DESC;
SELECT decode(upper(status_code),'S','N','R', 'N', 'W'),
E.effort_report_id,E.version_num
---INTO t_status_code,t_prev_effort_report_id,t_prev_version_num
FROM PSP_EFFORT_REPORTS E, PSP_EFFORT_REPORT_TEMPLATES T
WHERE E.person_id = person_id1 AND --- person_id has no effective dt in Assignments
((begin_date1 BETWEEN T.begin_date AND T.end_date) OR
(end_date1 BETWEEN T.begin_date AND T.end_date) OR
(begin_date1 <= T.begin_date AND end_date1 >= T.end_date)) AND
E.template_id = T.template_id AND
---g_template_row.report_type = 'N' AND
T.report_type = 'N'
ORDER BY status_code;
SELECT COUNT(*)
FROM psp_effort_reports
WHERE template_id = a_template_id;
SELECT message
FROM PSP_EFFORT_ERRORS
WHERE template_id = a_template_id AND
person_id = 0
UNION
SELECT message
FROM PSP_EFFORT_ERRORS
WHERE template_id = a_template_id AND
message not like 'Effort Rep%' AND
message not like 'Status Code%';
SELECT decode(upper(status_code),'S','N','W'),
E.effort_report_id,E.version_num
INTO t_status_code,t_prev_effort_report_id,t_prev_version_num
FROM PSP_EFFORT_REPORTS E, PSP_EFFORT_REPORT_TEMPLATES T
WHERE E.person_id = t_PERS_rec.person_id AND --- person_id has no effective dt in Assignments
((g_template_row.begin_date BETWEEN T.begin_date AND T.end_date) OR
(g_template_row.end_date BETWEEN T.begin_date AND T.end_date) OR
(g_template_row.begin_date <= T.begin_date AND g_template_row.end_date >= T.end_date)) AND
E.template_id = T.template_id AND
---g_template_row.report_type = 'N' AND
T.report_type = 'N' AND
ROWNUM = 1;
p_old_adhoc_delete(a_template_id,t_PERS_rec.person_id,
g_template_row.begin_date,g_template_row.end_date);
SELECT psp_effort_reports_s.NEXTVAL
INTO t_effort_report_id
FROM DUAL;
PSP_EFFORT_REPORTS_PKG.insert_row (
t_rowid,
t_effort_report_id, --- in NUMBER,
t_version_number, --- in NUMBER,
t_PERS_rec.person_id, --- in NUMBER,
t_PERS_rec.effective_start_date,
t_PERS_rec.effective_end_date,
SYSDATE, --- X_VERSION_CREATION_DATE in DATE,
NULL, --- X_VERSION_REASON_CODE in VARCHAR2,
NULL, --- X_MESSAGE_ID
NULL, --- X_REPORT_COMMENT in VARCHAR2,
SYSDATE,
t_status_code, --- X_STATUS_CODE in VARCHAR2,
a_template_id,
t_prev_effort_report_id,
p_business_group_id,
p_set_of_books_id,
'R');
/* Open Distribution Line Cursor and Select rows for given criterias */
---
OPEN t_DIST(t_PERS_rec.person_id,g_template_row.begin_date,g_template_row.end_date);
DELETE FROM PSP_EFFORT_REPORTS
WHERE person_id = t_PERS_rec.person_id AND
template_id = a_template_id;
SELECT 'x'
INTO t_dummy
FROM PSP_EFFORT_ERRORS
WHERE template_id = a_template_id AND
person_id = t_PERS_rec.person_id AND
ROWNUM = 1;
IF NOT(NVL(g_template_row.gl_code_combination_id, -- NULL NULL Select
NVL(g_poeta_row.gl_code_combination_id,-99)) = -- NULL Value Select
NVL(g_poeta_row.gl_code_combination_id,-99)) THEN -- Value NULL NoSelect
-- Value = Value Select
RAISE NO_GL_MATCHES_FOUND; -- Value <> Value NoSelect
UPDATE psp_effort_reports
SET prev_effort_report_id = t_DIST_row.effort_report_id
WHERE effort_report_id = t_effort_report_id AND
version_num = t_version_number;
SELECT NVL(MAX(effort_report_line_num) + 1,1)
INTO t_effort_report_line_num
FROM psp_effort_report_details
WHERE effort_report_id = t_effort_report_id AND
version_num = t_version_number;
PSP_EFFORT_REPORT_DETAILS_PKG.insert_row (
t_rowid, --- in out NOCOPY VARCHAR2,
t_effort_report_id, --- in NUMBER,
t_version_number, --- in NUMBER,
t_effort_report_line_num, --- in NUMBER,
t_DIST_row.assignment_id, --- in NUMBER,
t_DIST_row.element_type_id,
g_poeta_row.gl_code_combination_id, --- in NUMBER,
g_poeta_row.project_id, --- in NUMBER,
g_poeta_row.expenditure_organization_id, --- in NUMBER,
g_poeta_row.expenditure_type, --- X_EXPENDITURE_TYPE in VARCHAR2,
g_poeta_row.task_id, --- X_TASK_ID in NUMBER,
g_poeta_row.award_id, --- in NUMBER,
t_DIST_row.distribution_amount, --- in NUMBER,
'R');
UPDATE psp_distribution_lines_history
SET effort_report_id = NVL(t_effort_report_id,t_prev_effort_report_id)
WHERE distribution_line_id = t_DIST_row.distribution_line_id;
UPDATE psp_pre_gen_dist_lines_history
SET effort_report_id = NVL(t_effort_report_id,t_prev_effort_report_id)
WHERE pre_gen_dist_line_id = t_DIST_row.distribution_line_id;
UPDATE psp_adjustment_lines_history
SET effort_report_id = NVL(t_effort_report_id,t_prev_effort_report_id)
WHERE adjustment_line_id = t_DIST_row.distribution_line_id;
DELETE FROM PSP_EFFORT_REPORTS
WHERE effort_report_id = t_effort_report_id AND
version_num = t_version_number;
SELECT effort_report_id , max(version_num)
FROM psp_effort_reports
WHERE template_id = a_template_id
GROUP BY effort_report_id;