[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT object_id
FROM pa_perf_bz_object ppbo, pa_projects_all ppa
WHERE ppbo.object_type = 'PA_PROJECTS'
AND ppbo.perf_txn_set_id = l_perf_txn_set_id
AND ppa.segment1 between nvl((select segment1 from pa_projects_all where project_id = l_proj_from),' ') and
nvl((select segment1 from pa_projects_all where project_id = l_proj_to),ppa.segment1)
AND ppa.project_id = ppbo.object_id
ORDER BY object_id;
SELECT DISTINCT ppa.project_id
FROM pa_projects_all ppa,
pa_project_parties ppp,
pa_project_types_all ppt,
pa_project_statuses pps -- Added for Bug 4338924
WHERE ppa.project_id = ppp.project_id
AND ppp.object_type = 'PA_PROJECTS'
AND ppa.project_status_code = pps.project_status_code -- Added for Bug 4338924
AND pps.status_type = 'PROJECT' -- Added for Bug 4338924
AND pps.project_system_status_code NOT IN ('CLOSED', 'PURGED') -- Added for Bug 4338924
AND ppa.segment1 BETWEEN NVL((select segment1 from pa_projects_all where project_id = l_proj_from),' ') AND
NVL((select segment1 from pa_projects_all where project_id = l_proj_to), ppa.segment1)
AND ppa.project_id = ppp.project_id
AND ppp.resource_source_id = nvl(l_project_manager,ppp.resource_source_id)
AND ppa.carrying_out_organization_id = nvl(l_project_ou, ppa.carrying_out_organization_id)
AND ppa.org_id = nvl(l_project_org, ppa.org_id)
AND ppa.project_type = nvl(l_proj_type, ppt.project_type)
AND ppt.org_id = ppa.org_id
AND ppp.project_role_id = 1 -- Added for Bug 4338924
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(ppp.start_date_active, SYSDATE)) AND TRUNC(NVL(ppp.end_date_active, SYSDATE)) -- Added for Bug 4338924
ORDER BY ppa.project_id;
SELECT distinct ppa.project_id
FROM pa_projects_all ppa,
pa_project_types_all ppt,
pa_project_statuses pps -- Added for Bug 4338924
WHERE ppa.carrying_out_organization_id = nvl(l_project_ou, ppa.carrying_out_organization_id)
AND ppa.org_id = nvl(l_project_org, ppa.org_id)
AND ppa.project_status_code = pps.project_status_code -- Added for Bug 4338924
AND pps.status_type = 'PROJECT' -- Added for Bug 4338924
AND pps.project_system_status_code NOT IN ('CLOSED', 'PURGED') -- Added for Bug 4338924
AND ppa.segment1 between nvl((select segment1 from pa_projects_all where project_id = l_proj_from),' ') and
nvl((select segment1 from pa_projects_all where project_id = l_proj_to), ppa.segment1)
AND ppa.project_type = nvl(l_proj_type, ppt.project_type)
and ppt.org_id = ppa.org_id
ORDER BY ppa.project_id;
SELECT bz_ent_code
INTO l_bz_event_code
FROM pa_perf_bz_object
WHERE perf_txn_set_id = p_perf_txn_set_id
and rownum = 1;
pa_debug.write_file('LOG', 'Project count selected : '||l_project_list.COUNT);
SELECT distinct(ppor.object_id)
FROM pa_perf_object_rules ppor, pa_perf_rules ppr, pa_lookups pl -- Bug 4275320: Added pa_lookups
WHERE ppor.object_id = l_proj_id
AND ppor.rule_id is not null
AND ppor.object_type = 'PA_PROJECTS'
AND ppor.rule_id = ppr.rule_id
AND ppr.rule_type = 'PERF_RULE'
AND pl.lookup_code (+) = ppr.kpa_code -- For Bug 4275320
AND pl.lookup_type (+) = 'PA_PERF_KEY_AREAS' --Bug 4958325. Added look up type outer join, See the Bug for more details.
AND trunc(sysdate) between trunc(nvl(pl.start_date_active,sysdate)) and trunc(nvl(pl.end_date_active,sysdate)); -- For Bug 4275320
SELECT ppor.rule_id
FROM pa_perf_object_rules ppor, pa_perf_rules ppr, pa_lookups pl -- Bug 4275320: Added pa_lookups
WHERE ppor.object_id = l_proj_id
AND ppor.object_type = 'PA_PROJECTS'
AND ppor.rule_id = ppr.rule_id
AND ppr.rule_type = 'PERF_RULE'
AND pl.lookup_code (+) = ppr.kpa_code -- For Bug 4275320
AND pl.lookup_type (+) = 'PA_PERF_KEY_AREAS' --Bug 4958325. Added look up type outer join, See the Bug for more details.
AND trunc(sysdate) between trunc(nvl(pl.start_date_active,sysdate)) and trunc(nvl(pl.end_date_active,sysdate)); -- For Bug 4275320
SELECT ppr.measure_id, ppr.period_type,
ppr.currency_type, ppor.object_type
FROM pa_perf_object_rules ppor,
pa_perf_rules ppr
WHERE ppor.object_type = 'PA_PROJECTS'
AND ppor.object_id = l_proj_id
AND ppor.rule_id = l_rule_id
AND ppr.rule_id = ppor.rule_id
AND ppr.rule_type = 'PERF_RULE';
SELECT rule_id
FROM pa_perf_temp_obj_measure
WHERE object_id = l_proj_id
AND object_type = 'PA_PROJECTS'
AND measure_id = l_measure_id;
SELECT DISTINCT measure_id
FROM pa_perf_temp_obj_measure
WHERE object_type = 'PA_PROJECTS'
AND object_id = l_object_id;
SELECT DISTINCT object_id
FROM pa_perf_temp_obj_measure
WHERE object_type = 'PA_PROJECTS'
;
SELECT distinct bz_ent_code
FROM pa_perf_temp_obj_measure
WHERE object_type = 'PA_PROJECTS'
AND bz_ent_code is not null;
EXECUTE IMMEDIATE ('delete from pa_perf_temp_obj_measure') ;
PA_DEBUG.write_file('LOG', 'Inserting into PA_PERF_TEMP_OBJ_MEASURE temp table for Project: '||p_project_list(i));
SELECT count(*) INTO l_count FROM dual where EXISTS
(SELECT bz_event_code
FROM pa_perf_bz_measures
WHERE measure_id = l_measure_id);
SELECT bz_event_code
INTO l_bz_ent_code
FROM pa_perf_bz_measures
WHERE measure_id = l_measure_id
AND rownum = 1;
INSERT INTO pa_perf_temp_obj_measure
( object_type
,object_id
,measure_id
,measure_value
,rule_id
,calendar_type
,currency_type
,period_name
,bz_ent_code
)
VALUES
('PA_PROJECTS'
,p_project_list(i)
,l_measure_id
,null
,l_rule_list(j)
,l_period_type
,l_currency_type
,null
,l_bz_ent_code
);
INSERT INTO pa_perf_temp_obj_measure
( object_type
,object_id
,measure_id
,measure_value
,rule_id
,calendar_type
,currency_type
,period_name
,bz_ent_code
)
VALUES
('PA_PROJECTS'
,p_project_list(i)
,l_measure_id
,null
,l_rule_list(j)
,l_period_type
,l_currency_type
,null
,l_bz_ent_code
);
UPDATE pa_perf_transactions
SET current_flag = 'N'
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = p_project_list(i)
AND current_flag = 'Y';
commit; --Save the work after inserting all the projects into temp table
PA_DEBUG.write_file('LOG', 'End of Step 1 .. Inserting into PA_PERF_TEMP_OBJ_MEASURE temp table');
SELECT attribute1
INTO l_program_name
FROM pa_lookups
WHERE lookup_type = 'PA_PERF_BZ_EVENTS'
AND lookup_code = l_bz_code_list(i)
AND enabled_flag = 'Y';
PA_DEBUG.write_file('LOG', 'No Business Event code selected from PA_PERF_TEMP_OBJ_MEASURE temp table.');
PA_DEBUG.write_file('LOG', 'Number of Object IDs to be inserted into transaction table : '||l_object_list.COUNT);
UPDATE pa_perf_transactions
SET current_flag = 'N'
-- WHERE project_id = l_object_list(i) --Modified for Bug 3639490
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = l_object_list(i)
AND current_flag = 'Y';
SELECT count(*) INTO l_count FROM dual where EXISTS
(SELECT project_id
FROM pa_perf_transactions
--WHERE project_id = l_object_list(i) --Modified for Bug3639490
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = l_object_list(i)
AND measure_id in (SELECT measure_id
FROM pa_perf_bz_measures
WHERE bz_event_code = p_business_event_code)
AND current_flag = 'Y');
UPDATE pa_perf_transactions
SET current_flag = 'N'
--WHERE project_id = l_object_list(i) --Modified for bug 3639490
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = l_object_list(i)
AND measure_id in (SELECT measure_id
FROM pa_perf_bz_measures
WHERE bz_event_code = p_business_event_code)
AND current_flag = 'Y';
SELECT calendar_type
INTO l_period_type
FROM pa_perf_temp_obj_measure
WHERE object_id = l_object_list(i)
AND object_type = 'PA_PROJECTS'
AND measure_id = l_measure_list(j)
AND rule_id = l_rule_list(k);
SELECT nvl(pptom.measure_value, null), nvl(pptom.period_name,null),
pptom.rule_id, pptom.object_type, ppor.object_rule_id,
ppr.kpa_code, ppr.precision, ppr.currency_type, ppr.measure_format,
ppr.rule_type
INTO l_measure_value, l_period_name,
l_rule_id, l_object_type, l_object_rule_id,
l_kpa_code, l_precision, l_currency_type, l_measure_format,
l_rule_type
FROM pa_perf_rules ppr,
pa_perf_object_rules ppor,
pa_perf_temp_obj_measure pptom
WHERE pptom.object_id = l_object_list(i)
AND pptom.object_id = ppor.object_id
AND ppor.object_type = 'PA_PROJECTS'
AND pptom.measure_id = l_measure_list(j)
AND pptom.object_type = 'PA_PROJECTS'
AND ppor.rule_id = l_rule_list(k)
AND ppor.rule_id = ppr.rule_id
AND ppor.rule_id = pptom.rule_id
AND rownum = 1;
PA_DEBUG.write_file('LOG', 'After selecting values from PA_PERF_TEMP_OBJ_MEASURE temp table' );
SELECT pl_sql_api
INTO l_program_name
FROM pji_mt_measures_v
WHERE measure_id = l_measure_list(j)
AND rownum = 1 ;
UPDATE pa_perf_temp_obj_measure
SET measure_value = l_measure_value,
period_name = l_period_name
WHERE object_id = l_object_list(i)
AND measure_id = l_measure_list(j)
AND rule_id = l_rule_list(k)
AND object_type = 'PA_PROJECTS';
SELECT measure_value
INTO l_measure_value
FROM pa_perf_temp_obj_measure
WHERE object_id = l_object_list(i)
AND measure_id = l_measure_list(j)
AND object_type = 'PA_PROJECTS'
AND rule_id = l_rule_list(k)
AND ROWNUM = 1;
-- inserting the record into pa_perf_transactions table ELSE do nothing
PA_EXCEPTION_ENGINE_PKG.GET_THRESHOLD(
l_rule_list(k)
,l_rule_type
,l_measure_value
,l_threshold_id
,l_indicator_code
,l_exception_flag
,l_weighting
,l_thres_from
,l_thres_to
,l_errbuf
,l_retcode);
pa_debug.write_file('LOG','Inserting record into PA_PERF_TRANSACTIONS');
INSERT INTO pa_perf_transactions
( perf_txn_id
,perf_txn_obj_type
,perf_txn_obj_id
,object_rule_id
,related_obj_type
,related_obj_id
,rule_id
,project_id
,kpa_code
,measure_id
,measure_value
,period_name
,indicator_code
,threshold_from
,threshold_to
,weighting
,precision
,period_type
,currency_type
,measure_format
,program_id
,date_checked
,exception_flag
,current_flag
,included_in_scoring
,record_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
)
VALUES ( pa_perf_transactions_s1.nextval
,'PA_PROJECTS'
,l_object_list(i)
,l_object_rule_id
,null
,null
,l_rule_list(k)
,l_object_list(i)
,l_kpa_code
,l_measure_list(j)
,l_measure_value
,l_period_name
,l_indicator_code
,l_thres_from
,l_thres_to
,l_weighting
,l_precision
,l_period_type
,l_currency_type
,l_measure_format
,fnd_global.CONC_REQUEST_ID
,sysdate
,l_exception_flag
,'Y'
,'N'
,1
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
);
PA_DEBUG.write_file('LOG', 'End of Step 3 . . Inserting into PA_PERF_TRANSACTIONS table');
pa_debug.write_file('LOG','No records will be deleted from PA_PERF_BZ_OBJECT since Business Event code is not passed in.');
DELETE from pa_perf_bz_object
WHERE bz_ent_code = p_business_event_code;
SELECT object_page_layout_id
FROM pa_progress_report_setup_v
WHERE object_id = c_object_id
AND object_type = 'PA_PROJECTS'
AND page_type_code='PPR'
AND generation_method='AUTOMATIC';
DELETE from pa_perf_comments
WHERE perf_txn_id in (SELECT perf_txn_id
FROM pa_perf_transactions
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = p_project_list(i)
AND trunc(creation_date) < trunc(sysdate - p_days_old)
AND current_flag = 'N');
DELETE from pa_perf_kpa_trans
WHERE perf_txn_id in (SELECT perf_txn_id
FROM pa_perf_transactions
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = p_project_list(i)
AND trunc(creation_date) < trunc(sysdate - p_days_old)
AND current_flag = 'N');
DELETE from pa_perf_kpa_summary_det
WHERE object_type = 'PA_PROJECTS'
AND object_id = p_project_list(i)
AND trunc(creation_date) < trunc(sysdate - p_days_old)
AND kpa_summary_id in (SELECT kpa_summary_id
FROM pa_perf_kpa_summary
WHERE object_type = 'PA_PROJECTS'
AND object_id = p_project_list(i)
AND trunc(creation_date) < trunc(sysdate - p_days_old)
AND current_flag = 'N');
DELETE from pa_perf_kpa_summary
WHERE object_type = 'PA_PROJECTS'
AND object_id = p_project_list(i)
AND trunc(creation_date) < trunc(sysdate - p_days_old)
AND current_flag = 'N';
DELETE from pa_perf_transactions
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = p_project_list(i)
AND trunc(creation_date) < trunc(sysdate - p_days_old)
AND current_flag = 'N';
SELECT COUNT(*) INTO l_count FROM dual WHERE EXISTS
(
SELECT pt.threshold_Id, pt.indicator_code, pt.exception_flag,
pt.weighting, pt.from_value, pt.to_value
FROM pa_perf_thresholds pt, pa_perf_rules pr
WHERE pr.rule_id = NVL(p_rule_id, -99)
AND pr.rule_id = pt.thres_obj_id
AND pt.rule_type = p_rule_type
AND pr.rule_type = pt.rule_type
AND NVL(round(p_cur_value, DECODE(pr.precision,0.1,1,0.01,2,0.001,3,0)),
-99999999999) between pt.from_value and pt.to_value
);
SELECT pt.threshold_Id, pt.indicator_code, pt.exception_flag,
pt.weighting, pt.from_value, pt.to_value
INTO x_threshold_id, x_indicator_code, x_exception_flag,
x_weighting, x_from_value, x_to_value
FROM pa_perf_thresholds pt, pa_perf_rules pr
WHERE pr.rule_id = NVL(p_rule_id, -99)
AND pr.rule_id = pt.thres_obj_id
AND pt.rule_type = p_rule_type
AND pr.rule_type = pt.rule_type
AND NVL(round(p_cur_value, DECODE(pr.precision,0.1,1,0.01,2,0.001,3,0)),
-99999999999) between pt.from_value and pt.to_value
AND rownum = 1;
SELECT lookup_code
FROM pa_lookups
WHERE lookup_type = 'PA_PERF_KEY_AREAS'
AND lookup_code <> 'ALL'
ORDER BY To_number(predefined_flag) ASC ;
select
ppor.rule_id
from
pa_perf_rules ppr, pa_perf_object_rules ppor
where
ppor.object_type = 'PA_PROJECTS'
AND ppor.object_id = l_proj_id
and ppr.kpa_code = l_kpa_code
AND ppor.rule_id = ppr.rule_id
AND ppr.rule_type = 'SCORE_RULE'
AND ppr.score_method = 'SUM'
AND Trunc(Sysdate) BETWEEN ppr.start_date_active
AND Nvl(ppr.end_date_active, Trunc(Sysdate +1));
select Nvl(sum(ppem.weighting), 0),
MIN (ppor.rule_id), COUNT(ppem.perf_txn_id)
from pa_perf_transactions ppem ,
pa_perf_rules ppr, pa_perf_object_rules ppor
where ppem.current_flag = 'Y'
and ppem.perf_txn_obj_type = 'PA_PROJECTS'
and ppem.perf_txn_obj_id = l_proj_id
AND ppor.object_type = 'PA_PROJECTS'
AND ppor.object_id = l_proj_id
AND ppor.rule_id = ppr.rule_id
AND ppr.rule_type = 'SCORE_RULE'
AND ppr.score_method = 'SUM'
AND ppr.kpa_code = ppem.kpa_code
AND ppem.kpa_code = l_kpa_code
AND Nvl(ppem.exception_flag, 'Y') = 'Y'
AND Trunc(Sysdate) BETWEEN ppr.start_date_active
AND Nvl(ppr.end_date_active, Trunc(Sysdate +1))
group by ppem.kpa_code;
select
COUNT(ppem.perf_txn_id),
Nvl(sum(ppem.weighting), 0)
from pa_perf_transactions ppem ,
pa_perf_rules ppr, pa_perf_object_rules ppor
where ppem.current_flag = 'Y'
and ppem.perf_txn_obj_type = 'PA_PROJECTS'
and ppem.perf_txn_obj_id = l_proj_id
AND ppor.object_type = 'PA_PROJECTS'
AND ppor.object_id = l_proj_id
AND ppor.rule_id = ppr.rule_id
AND ppr.rule_type = 'SCORE_RULE'
AND ppr.score_method = 'SUM'
AND ppr.kpa_code = ppem.kpa_code
AND ppem.kpa_code = l_kpa_code
AND Nvl(ppem.exception_flag, 'Y') = 'Y'
AND Trunc(Sysdate) BETWEEN ppr.start_date_active
AND Nvl(ppr.end_date_active, Trunc(Sysdate +1))
AND ppem.indicator_code = l_ind
group by ppem.kpa_code;
SELECT lookup_code
FROM pa_lookups
WHERE lookup_type = 'PA_PERF_INDICATORS'
ORDER BY predefined_flag ASC;
PA_DEBUG.write_file('LOG', 'Mass update the KPA Summary table');
UPDATE pa_perf_kpa_summary
SET current_flag = 'N'
WHERE object_type = 'PA_PROJECTS'
AND object_id = p_project_list(i);
SELECT pa_perf_kpa_summary_s1.nextval
INTO l_summary_seq
FROM DUAL;
l_summary_table.DELETE();
SELECT pa_perf_kpa_summary_det_s1.NEXTVAL
INTO l_summary_det_seq
FROM dual;
PA_DEBUG.write_file('LOG', 'Inserting into PA_PERF_KPA_SUMMARY_DET');
INSERT INTO pa_perf_kpa_summary_det
(
kpa_summary_det_id,
kpa_summary_id,
object_type,
object_id,
kpa_code,
indicator_code,
COUNT,
score,
rule_id,
ind1_count,
ind1_score,
ind2_count,
ind2_score,
ind3_count,
ind3_score,
ind4_count,
ind4_score,
ind5_count,
ind5_score,
creation_date,
created_by ,
last_update_date,
last_updated_by ,
last_update_login
)
VALUES
(
l_summary_det_seq,
l_summary_seq,
'PA_PROJECTS',
p_project_list(i),
l_kpas(j),
null,
0,
0,
l_rule_id,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.login_id
);
l_score_list.DELETE;
l_count_list.DELETE;
SELECT pa_perf_kpa_summary_det_s1.NEXTVAL
INTO l_summary_det_seq
FROM dual;
PA_DEBUG.write_file('LOG', 'Inserting into PA_PERF_KPA_SUMMARY_DET');
INSERT INTO pa_perf_kpa_summary_det
(
kpa_summary_det_id,
kpa_summary_id,
object_type,
object_id,
kpa_code,
indicator_code,
COUNT,
score,
rule_id,
ind1_count,
ind1_score,
ind2_count,
ind2_score,
ind3_count,
ind3_score,
ind4_count,
ind4_score,
ind5_count,
ind5_score,
creation_date,
created_by ,
last_update_date,
last_updated_by ,
last_update_login
)
VALUES
(
l_summary_det_seq,
l_summary_seq,
'PA_PROJECTS',
p_project_list(i),
l_kpas(j),
l_indicator_code,
l_count,
l_score,
l_kpa_rule_id,
l_count_list(1),
l_score_list(1),
l_count_list(2),
l_score_list(2),
l_count_list(3),
l_score_list(3),
l_count_list(4),
l_score_list(4),
l_count_list(5),
l_score_list(5),
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.login_id
);
PA_DEBUG.write_file('LOG', 'Inserting into PA_PERF_KPA_TRANS');
INSERT INTO pa_perf_kpa_trans
(kpa_summary_det_id,
perf_txn_id,
creation_date,
created_by ,
last_update_date,
last_updated_by ,
last_update_login)
SELECT l_summary_det_seq,perf_txn_id, Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM pa_perf_transactions
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = p_project_list(i)
AND kpa_code = l_kpas(j)
AND current_flag = 'Y'
AND Nvl(exception_flag, 'Y') = 'Y'
;
--- update the transaction to be as included in the last scoring
UPDATE pa_perf_transactions
SET included_in_scoring = 'Y'
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = p_project_list(i)
AND kpa_code = l_kpas(j)
AND current_flag = 'Y'
AND Nvl(exception_flag, 'Y') = 'Y'
;
l_score_list.DELETE;
l_count_list.DELETE;
SELECT pa_perf_kpa_summary_det_s1.NEXTVAL
INTO l_summary_det_seq
FROM dual;
PA_DEBUG.write_file('LOG', 'Inserting into PA_PERF_KPA_SUMMARY_DET');
INSERT INTO pa_perf_kpa_summary_det
(
kpa_summary_det_id,
kpa_summary_id,
object_type,
object_id,
kpa_code,
indicator_code,
COUNT,
score,
rule_id,
ind1_count,
ind1_score,
ind2_count,
ind2_score,
ind3_count,
ind3_score,
ind4_count,
ind4_score,
ind5_count,
ind5_score,
creation_date,
created_by ,
last_update_date,
last_updated_by ,
last_update_login
)
VALUES
(
l_summary_det_seq,
l_summary_seq,
'PA_PROJECTS',
p_project_list(i),
l_kpas(j),
null,
l_count,
l_score,
l_kpa_rule_id,
l_count_list(1),
l_score_list(1),
l_count_list(2),
l_score_list(2),
l_count_list(3),
l_score_list(3),
l_count_list(4),
l_score_list(4),
l_count_list(5),
l_score_list(5),
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.login_id
);
INSERT INTO pa_perf_kpa_trans
(kpa_summary_det_id,
perf_txn_id,
creation_date,
created_by ,
last_update_date,
last_updated_by ,
last_update_login)
SELECT l_summary_det_seq,perf_txn_id, Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.login_id
FROM pa_perf_transactions
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = p_project_list(i)
AND kpa_code = l_kpas(j)
AND current_flag = 'Y'
AND Nvl(exception_flag, 'Y') = 'Y'
;
--- update the transaction to be as included in the last scoring
UPDATE pa_perf_transactions
SET included_in_scoring = 'Y'
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = p_project_list(i)
AND kpa_code = l_kpas(j)
AND current_flag = 'Y'
AND Nvl(exception_flag, 'Y') = 'Y'
;
INSERT INTO pa_perf_kpa_summary
(
kpa_summary_id,
object_type,
object_id,
date_checked,
current_flag,
perf_status_code,
kpa1_code,
kpa1_indicator,
kpa1_score,
kpa1_thres_from,
kpa1_thres_to,
kpa2_code,
kpa2_indicator,
kpa2_score,
kpa2_thres_from,
kpa2_thres_to,
kpa3_code,
kpa3_indicator,
kpa3_score,
kpa3_thres_from,
kpa3_thres_to,
kpa4_code,
kpa4_indicator,
kpa4_score,
kpa4_thres_from,
kpa4_thres_to,
kpa5_code,
kpa5_indicator,
kpa5_score,
kpa5_thres_from,
kpa5_thres_to,
creation_date,
created_by ,
last_update_date,
last_updated_by ,
last_update_login
)
VALUES
(
l_summary_seq,
'PA_PROJECTS',
p_project_list(i),
Sysdate,
'Y',
l_status,
l_summary_table(1).kpa_code,
l_summary_table(1).indicator_code,
l_summary_table(1).score,
l_summary_table(1).thres_from,
l_summary_table(1).thres_to,
l_summary_table(2).kpa_code,
l_summary_table(2).indicator_code,
l_summary_table(2).score,
l_summary_table(2).thres_from,
l_summary_table(2).thres_to,
l_summary_table(3).kpa_code,
l_summary_table(3).indicator_code,
l_summary_table(3).score,
l_summary_table(3).thres_from,
l_summary_table(3).thres_to,
l_summary_table(4).kpa_code,
l_summary_table(4).indicator_code,
l_summary_table(4).score,
l_summary_table(4).thres_from,
l_summary_table(4).thres_to,
l_summary_table(5).kpa_code,
l_summary_table(5).indicator_code,
l_summary_table(5).score,
l_summary_table(5).thres_from,
l_summary_table(5).thres_to,
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.login_id
);
--- update the included in scoring flag for all other transactions
UPDATE pa_perf_transactions
SET included_in_scoring = 'N'
WHERE perf_txn_obj_type = 'PA_PROJECTS'
AND perf_txn_obj_id = p_project_list(i)
AND Nvl(exception_flag, 'Y') = 'Y'
AND included_in_scoring = 'Y'
AND perf_txn_id NOT IN
(
select ppkt.perf_txn_id
from pa_perf_kpa_summary ppks, pa_perf_kpa_summary_det ppkd,
pa_perf_kpa_trans ppkt
where ppks.object_type = 'PA_PROJECTS' and
ppks.object_id = p_project_list(i)
and ppks.current_flag = 'Y'
and ppks.kpa_summary_id = ppkd.kpa_summary_id
and ppkd.kpa_summary_det_id = ppkt.kpa_summary_det_id
)
;