[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select TRANSACTION_ID
from HR_API_TRANSACTIONS
where TRANSACTION_REF_ID=p_copy_entity_txn_id
and TRANSACTION_REF_TABLE='PER_SP_PLAN'
and PROCESS_NAME='HR_SP_APPROVAL_PRC';
INSERT INTO HR_API_TRANSACTIONS (TRANSACTION_ID, CREATOR_PERSON_ID, STATUS, TRANSACTION_REF_ID, TRANSACTION_REF_TABLE, TRANSACTION_TYPE, PROCESS_NAME,TRANSACTION_PRIVILEGE,ITEM_TYPE,ITEM_KEY)
VALUES(p_hr_api_txn_id,p_person_id,'W', p_copy_entity_txn_id,'PER_SP_PLAN','NWF','HR_SP_APPROVAL_PRC','PRIVATE','HRSSA',hr_workflow_item_key_s.nextval);
update HR_API_TRANSACTIONS
set item_key=hr_workflow_item_key_s.nextval
where item_key is null
and TRANSACTION_ID = p_hr_api_txn_id
and PROCESS_NAME='HR_SP_APPROVAL_PRC';
select transaction_category_id
into l_sp_txn_cat
from pqh_transaction_categories
where short_name ='SP'
and business_group_id is null;
SELECT copy_entity_result_id
,information1 "PLAN_ID"
,information11 "PLAN_TYPE"
,information151 "PLAN_NAME"
,information4 "SUCCESSEE_ID"
,information152 "DESCRIPTION"
,information160 "PLAN_OWNER"
,information2 "START_DATE"
,information3 "END_DATE"
,information161 "FILLED_BY"
,information166 "FILLED_ON"
,information8 "STATUS"
,information167 "NEXT_REVIEW_DATE"
,information15 "FILLED_FROM_PLAN"
,information169 "CRITERIA_SET_ID"
,information219 "COMMENTS" -- not there in table
,information221 "BUSINESS_GROUP_ID"
,information265 "OBJECT_VERSION_NUMBER"
,information266 "CREATED_BY"
,information306 "CREATION_DATE"
,information267 "LAST_UPDATED_BY"
,information307 "LAST_UPDATE_DATE"
,Information110 "ATTRIBUTE_CATEGORY"
,Information111 "ATTRIBUTE1"
,Information112 "ATTRIBUTE2"
,Information113 "ATTRIBUTE3"
,Information114 "ATTRIBUTE4"
,Information115 "ATTRIBUTE5"
,Information116 "ATTRIBUTE6"
,Information117 "ATTRIBUTE7"
,Information118 "ATTRIBUTE8"
,Information119 "ATTRIBUTE9"
,Information120 "ATTRIBUTE10"
,Information121 "ATTRIBUTE11"
,Information122 "ATTRIBUTE12"
,Information123 "ATTRIBUTE13"
,Information124 "ATTRIBUTE14"
,Information125 "ATTRIBUTE15"
,Information126 "ATTRIBUTE16"
,Information127 "ATTRIBUTE17"
,Information128 "ATTRIBUTE18"
,Information129 "ATTRIBUTE19"
,Information130 "ATTRIBUTE20"
,dml_operation
,table_alias
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'SP';
SELECT copy_entity_result_id
,information160 "SUCCESSOR_IN_PLAN_ID"
,information1 "PLAN_ID"
,information4 "SUCCESSOR_ID"
,information11 "POTENTIAL"
,information12 "PERFORMANCE_RATING"
,information13 "RISK_OF_LOSS"
,information14 "IMPACT_OF_LOSS"
,information15 "KEY_PERSON" -- not there in table
,information300 "READINESS_PCT"
,information161 "RANK"
,information8 "STATUS"
,information2 "EARLIEST_SUCCESSION_DATE"
,information3 "LATEST_SUCCESSION_DATE"
,information15 "ELIGIBLE_FOR_PROMOTION"
,information221 "BUSINESS_GROUP_ID"
,information219 "COMMENTS"
,information110 "ATTRIBUTE_CATEGORY" -- not mapped
,information111 "ATTRIBUTE1"
,information112 "ATTRIBUTE2"
,information113 "ATTRIBUTE3"
,information114 "ATTRIBUTE4"
,information115 "ATTRIBUTE5"
,information116 "ATTRIBUTE6"
,information117 "ATTRIBUTE7"
,information118 "ATTRIBUTE8"
,information119 "ATTRIBUTE9"
,information120 "ATTRIBUTE10"
,information121 "ATTRIBUTE11"
,information122 "ATTRIBUTE12"
,information123 "ATTRIBUTE13"
,information124 "ATTRIBUTE14"
,information125 "ATTRIBUTE15"
,information126 "ATTRIBUTE16"
,information127 "ATTRIBUTE17"
,information128 "ATTRIBUTE18"
,information129 "ATTRIBUTE19"
,information130 "ATTRIBUTE20"
,information166 "PLANNED_DATE"
,information230 "ASSIGNMENT_ID"
,information231 "SUPERVISOR_ID"
,information232 "GRADE_ID"
,information233 "GRADE_LADDER_ID"
,information234 "GRADE_STEP_ID"
,information235 "GRADE_POINT_ID"
,information236 "POSITION_ID"
,information237 "JOB_ID"
,information238 "ASSIGNMENT_STATUS_TYPE_ID"
,information239 "ORGANIZATION_ID"
,information240 "PEOPLE_GROUP_ID"
,information241 "PAY_BASIS_ID"
,information242 "COLLECTIVE_AGREEMENT_ID"
,information243 "LOCATION_ID"
,information244 "CONTRACT_ID" --- ???
,information101 "EMPLOYEE_CATEGORY"
,information102 "BARGAINING_UNIT_CODE"
,information103 "LABOUR_UNION_MEMBER_FLAG"
,information104 "ASSIGNMENT_CATEGORY"
,information265 "OBJECT_VERSION_NUMBER"
,information266 "CREATED_BY"
,information306 "CREATION_DATE"
,information267 "LAST_UPDATED_BY"
,information307 "LAST_UPDATE_DATE"
,dml_operation
,table_alias
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'SCSR'
AND parent_entity_result_id = p_parent_entity_result_id
ORDER BY information160;
hr_utility.trace('Inserting the new Succession Plan: '||i.plan_name);
ELSIF i.dml_operation = 'UPDATE' THEN
hr_utility.trace('Updating the new Succession Plan: '||i.plan_name);
per_sp_plan_api.update_sp_plan(p_validate => false
,p_effective_date => l_effective_date
,p_plan_type => i.plan_type
,p_plan_name => i.plan_name
,p_successee_id => i.successee_id
,p_business_group_id => i.business_group_id
,p_status => i.status
,p_plan_owner => i.plan_owner
,p_start_date => i.start_date
,p_end_date => i.end_date
,p_description => i.description
,p_filled_from_plan => i.filled_from_plan
,p_filled_by => i.filled_by
,p_filled_on => i.filled_on
,p_next_review_date => i.next_review_date
,p_criteria_set_id => i.criteria_set_id
,p_attribute_category => i.attribute_category
,p_attribute1 => i.attribute1
,p_attribute2 => i.attribute2
,p_attribute3 => i.attribute3
,p_attribute4 => i.attribute4
,p_attribute5 => i.attribute5
,p_attribute6 => i.attribute6
,p_attribute7 => i.attribute7
,p_attribute8 => i.attribute8
,p_attribute9 => i.attribute9
,p_attribute10 => i.attribute10
,p_attribute11 => i.attribute11
,p_attribute12 => i.attribute12
,p_attribute13 => i.attribute13
,p_attribute14 => i.attribute14
,p_attribute15 => i.attribute15
,p_attribute16 => i.attribute16
,p_attribute17 => i.attribute17
,p_attribute18 => i.attribute18
,p_attribute19 => i.attribute19
,p_attribute20 => i.attribute20
,p_plan_id => l_spp_id
,p_object_version_number => l_spp_ovn);
ELSIF i.dml_operation = 'DELETE' THEN
hr_utility.trace('Deleting the new Succession Plan: '||i.plan_name);
per_successor_in_plan_api.delete_successor_in_plan(p_validate => false
,p_successor_in_plan_id => j.successor_in_plan_id
,p_object_version_number => j.object_version_number);
per_sp_plan_api.delete_sp_plan(p_validate => false
,p_plan_id => l_spp_id
,p_object_version_number => l_spp_ovn);
IF i.dml_operation IN ('CREATE','UPDATE') THEN
FOR j IN csr_sip_stage(p_copy_entity_txn_id, i.copy_entity_result_id)
LOOP
IF j.dml_operation = 'CREATE' THEN
hr_utility.set_location(l_proc,45);
ELSIF j.dml_operation = 'UPDATE' THEN
hr_utility.set_location(l_proc,55);
per_successor_in_plan_api.update_successor_in_plan
(p_validate => false
,p_effective_date => l_effective_date
,p_plan_id => j.plan_id
,p_successor_id => j.successor_id
,p_business_group_id => j.business_group_id
,p_status => j.status
,p_rank => j.rank
,p_potential => j.potential
,p_performance_rating => j.performance_rating
,p_risk_of_loss => j.risk_of_loss
,p_impact_of_loss => j.impact_of_loss
,p_readiness_pct => j.readiness_pct
,p_eligible_for_promotion => j.eligible_for_promotion
,p_earliest_succession_date => j.earliest_succession_date
,p_latest_succession_date => j.latest_succession_date
,p_comments => j.comments
,p_attribute_category => j.attribute_category
,p_attribute1 => j.attribute1
,p_attribute2 => j.attribute2
,p_attribute3 => j.attribute3
,p_attribute4 => j.attribute4
,p_attribute5 => j.attribute5
,p_attribute6 => j.attribute6
,p_attribute7 => j.attribute7
,p_attribute8 => j.attribute8
,p_attribute9 => j.attribute9
,p_attribute10 => j.attribute10
,p_attribute11 => j.attribute11
,p_attribute12 => j.attribute12
,p_attribute13 => j.attribute13
,p_attribute14 => j.attribute14
,p_attribute15 => j.attribute15
,p_attribute16 => j.attribute16
,p_attribute17 => j.attribute17
,p_attribute18 => j.attribute18
,p_attribute19 => j.attribute19
,p_attribute20 => j.attribute20
,p_planned_date => j.planned_date
,p_assignment_id => j.assignment_id
,p_supervisor_id => j.supervisor_id
,p_grade_id => j.grade_id
,p_grade_ladder_id => j.grade_ladder_id
,p_grade_step_id => j.grade_step_id
,p_grade_point_id => j.grade_point_id
,p_position_id => j.position_id
,p_job_id => j.job_id
,p_assignment_status_type_id => j.assignment_status_type_id
,p_organization_id => j.organization_id
,p_people_group_id => j.people_group_id
,p_pay_basis_id => j.pay_basis_id
,p_employee_category => j.employee_category
,p_bargaining_unit_code => j.bargaining_unit_code
,p_labour_union_member_flag => j.labour_union_member_flag
,p_collective_agreement_id => j.collective_agreement_id
,p_assignment_category => j.assignment_category
,p_location_id => j.location_id
,p_contract_id => j.contract_id
,p_successor_in_plan_id => l_sip_id
,p_object_version_number => l_sip_ovn);
ELSIF j.dml_operation = 'DELETE' THEN
per_successor_in_plan_api.delete_successor_in_plan(p_validate => false
,p_successor_in_plan_id => j.successor_in_plan_id
,p_object_version_number => j.object_version_number);
if j.dml_operation <> 'DELETE' then
--
-- CHANGE THE PK1_VALUE FOR ATTACHMENTS
--
UPDATE fnd_attached_documents
SET PK1_VALUE=l_sip_id,
ENTITY_NAME='PER_SP_SUCCESSOR_IN_PLAN'
WHERE ENTITY_NAME='BEN_COPY_ENTITY_RESULTS_SCSR'
AND PK1_VALUE=j.copy_entity_result_id;
if i.dml_operation <> 'DELETE' then
--
-- CHANGE THE PK1_VALUE FOR ATTACHMENTS
--
UPDATE fnd_attached_documents
SET PK1_VALUE=l_spp_id,
ENTITY_NAME='PER_SP_PLAN'
WHERE ENTITY_NAME='BEN_COPY_ENTITY_RESULTS_PL'
AND PK1_VALUE=i.copy_entity_result_id;
SELECT plan_id
,plan_type
,plan_name
,successee_id
,description
,plan_owner
,start_date
,end_date
,filled_by
,filled_on
,status
,next_review_date
,filled_from_plan
,NULL comments
,business_group_id
,object_version_number
,criteria_set_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
FROM per_sp_plan
WHERE plan_id = p_plan_id;
SELECT plan_id
,successor_id
,business_group_id
,status
,rank
,potential
,performance_rating
,risk_of_loss
,impact_of_loss
,readiness_pct
,eligible_for_promotion
,earliest_succession_date
,latest_succession_date
,comments
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,planned_date
,assignment_id
,supervisor_id
,grade_id
,grade_ladder_id
,grade_step_id
,grade_point_id
,position_id
,job_id
,assignment_status_type_id
,organization_id
,people_group_id
,pay_basis_id
,employee_category
,bargaining_unit_code
,labour_union_member_flag
,collective_agreement_id
,assignment_category
,location_id
,contract_id
,successor_in_plan_id
,object_version_number
,creation_date
,created_by
,last_updated_by
,last_update_date
FROM per_sp_successor_in_plan
WHERE plan_id = p_plan_id
ORDER BY rank;
SELECT business_group_id INTO l_business_group_id
FROM PER_SP_PLAN WHERE PLAN_ID=p_plan_id;
INSERT INTO ben_copy_entity_results (copy_entity_result_id
,information1
,information11
,information151
,information4
,information152
,information160
,information2
,information3
,information161
,information166
,information8
,information167
,information15
,information169
,information219
,information221
,information265
,information266
,information306
,information267
,information307
,information110
,information111
,information112
,information113
,information114
,information115
,information116
,information117
,information118
,information119
,information120
,information121
,information122
,information123
,information124
,information125
,information126
,information127
,information128
,information129
,information130
,dml_operation
,table_alias
,copy_entity_txn_id
,RESULT_TYPE_CD
,OBJECT_VERSION_NUMBER
,STATUS)
VALUES
(ben_copy_entity_results_s.nextval
,i.plan_id
,i.plan_type
,i.plan_name
,i.successee_id
,i.description
,i.plan_owner
,i.start_date
,i.end_date
,i.filled_by
,i.filled_on
,i.status
,i.next_review_date
,i.filled_from_plan
,i.criteria_set_id
,null --- comments
,i.business_group_id
,i.object_version_number
,i.created_by
,i.creation_date
,i.last_updated_by
,i.last_update_date
,i.attribute_category
,i.attribute1
,i.attribute2
,i.attribute3
,i.attribute4
,i.attribute5
,i.attribute6
,i.attribute7
,i.attribute8
,i.attribute9
,i.attribute10
,i.attribute11
,i.attribute12
,i.attribute13
,i.attribute14
,i.attribute15
,i.attribute16
,i.attribute17
,i.attribute18
,i.attribute19
,i.attribute20
,'COPIED'
,'SP'
,l_copy_entity_txn_id
,'DISPLAY'
,1
,'W');
select ben_copy_entity_results_s.currval into l_pl_cer_id from dual;
--- Now insert the SPP records
FOR j IN csr_successors(p_plan_id)
LOOP
INSERT INTO ben_copy_entity_results
( copy_entity_result_id
,information160
,information1
,information4
,information11
,information12
,information13
,information14
-- ,information15 -- key person not there in table
,information300
,information161
,information8
,information2
,information3
,information15
,information221
,information219
,information110
,information111
,information112
,information113
,information114
,information115
,information116
,information117
,information118
,information119
,information120
,information121
,information122
,information123
,information124
,information125
,information126
,information127
,information128
,information129
,information130
,information166
,information230
,information231
,information232
,information233
,information234
,information235
,information236
,information237
,information238
,information239
,information240
,information241
,information242
,information243
,information244
,information101
,information102
,information103
,information104
,information265
,information266
,information306
,information267
,information307
,dml_operation
,table_alias
,copy_entity_txn_id
,RESULT_TYPE_CD
,OBJECT_VERSION_NUMBER
,PARENT_ENTITY_RESULT_ID
)
VALUES
( ben_copy_entity_results_s.nextval
,j.successor_in_plan_id
,j.plan_id
,j.successor_id
,j.potential
,j.performance_rating
,j.risk_of_loss
,j.impact_of_loss
,j.readiness_pct
,j.rank
,j.status
,j.earliest_succession_date
,j.latest_succession_date
,j.eligible_for_promotion
,j.business_group_id
,j.comments
,j.attribute_category
,j.attribute1
,j.attribute2
,j.attribute3
,j.attribute4
,j.attribute5
,j.attribute6
,j.attribute7
,j.attribute8
,j.attribute9
,j.attribute10
,j.attribute11
,j.attribute12
,j.attribute13
,j.attribute14
,j.attribute15
,j.attribute16
,j.attribute17
,j.attribute18
,j.attribute19
,j.attribute20
,j.planned_date
,j.assignment_id
,j.supervisor_id
,j.grade_id
,j.grade_ladder_id
,j.grade_step_id
,j.grade_point_id
,j.position_id
,j.job_id
,j.assignment_status_type_id
,j.organization_id
,j.people_group_id
,j.pay_basis_id
,j.collective_agreement_id
,j.location_id
,j.contract_id
,j.employee_category
,j.bargaining_unit_code
,j.labour_union_member_flag
,j.assignment_category
,j.object_version_number
,j.created_by
,j.creation_date
,j.last_updated_by
,j.last_update_date
,'COPIED'
,'SCSR'
,l_copy_entity_txn_id
,'DISPLAY'
,1
,l_pl_cer_id);
select transaction_ref_id into l_copy_entity_txn_id
from hr_api_transactions
where transaction_id=l_hrApiTxnId
and process_name='HR_SP_APPROVAL_PRC';
update ben_copy_entity_results
set status='W', information220=response
where copy_entity_txn_id=l_copy_entity_txn_id
and table_alias='SP'
and status='Y';
update hr_api_transactions
set item_key=null
where transaction_id=l_hrApiTxnId
and process_name='HR_SP_APPROVAL_PRC';
select transaction_ref_id into l_copy_entity_txn_id
from hr_api_transactions
where transaction_id=l_hrApiTxnId
and process_name='HR_SP_APPROVAL_PRC';
hr_utility.set_location('delete from hr_api_transactions..', 50);
delete from hr_api_transactions
where item_type=itemtype and item_key=itemkey;
Delete from BEN_COPY_ENTITY_RESULTS
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias in ('SP','SCSR');
delete from pqh_copy_entity_txns
where copy_entity_txn_id = p_copy_entity_txn_id
and context='SP';
delete from hr_api_transactions
where transaction_ref_id = p_copy_entity_txn_id
and process_name='HR_SP_APPROVAL_PRC';
select distinct information1 into plan_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias='SP';
Delete from BEN_COPY_ENTITY_RESULTS
where information1 = plan_id
and table_alias in ('SP','SCSR');
Delete from BEN_COPY_ENTITY_RESULTS
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias in ('SP','SCSR');
delete from pqh_copy_entity_txns
where copy_entity_txn_id = p_copy_entity_txn_id;
delete from hr_api_transactions
where transaction_ref_id = p_copy_entity_txn_id
and process_name='HR_SP_APPROVAL_PRC';
SELECT wrpv.display_name displayName
FROM wf_runnable_processes_v wrpv
WHERE wrpv.item_type = p_item_type
AND wrpv.process_name = p_process;
select Information151 plan_name
from ben_copy_entity_results
where copy_entity_txn_id=p_copy_entity_txn_id
and table_alias='SP';
select transaction_id, item_key into l_hrApiTxnId, l_item_key
from hr_api_transactions
where TRANSACTION_REF_ID=p_copy_entity_txn_id
and TRANSACTION_REF_TABLE='PER_SP_PLAN'
and PROCESS_NAME='HR_SP_APPROVAL_PRC';
update ben_copy_entity_results
set status='Y'
where copy_entity_txn_id=p_copy_entity_txn_id
and table_alias='SP'
and status='W';