[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
hr_transaction_api.delete_transaction_step
(p_validate => FALSE
,p_transaction_step_id => l_old_transaction_step_id
,p_person_id => p_login_person_id
,p_object_version_number => l_old_object_version_number);
If (p_transaction_mode = 'UPDATE') Then
check_changes
(p_historyId
,to_number(p_internal_contact_person)
,p_title
,p_supplier
,p_trntype
,p_location
,p_completion_date
,p_award
,p_score
,to_number(p_duration)
,p_duration_unit
,to_number(p_eq_ota_activity)
,p_status
,p_nth_information_category
,p_nth_information1
,p_nth_information2
,p_nth_information3
,p_nth_information4
,p_nth_information5
,p_nth_information6
,p_nth_information7
,p_nth_information8
,p_nth_information9
,p_nth_information10
,p_nth_information11
,p_nth_information12
,p_nth_information13
,p_nth_information14
,p_nth_information15
,p_nth_information16
,p_nth_information17
,p_nth_information18
,p_nth_information19
,p_nth_information20
,l_check_changes_result);
p_message_name => 'HR_UPDATE_NOT_ALLOWED');
Select
hrtrns.transaction_step_id
From
wf_item_activity_statuses process
,wf_item_attribute_values attribute2
,wf_process_activities activity
,hr_api_transaction_steps hrtrns
Where
activity.activity_name = p_processname
and activity.process_item_type = p_item_type
and activity.activity_item_type = p_item_type
and activity.instance_id = process.process_activity
and process.activity_status = 'ACTIVE'
and process.item_type = p_item_type
and hrtrns.update_person_id = p_person_id
and process.item_key = attribute2.item_key
and attribute2.item_type = process.item_type
and attribute2.name = 'TRAN_SUBMIT'
and attribute2.text_value = 'Y'
and process.item_key = hrtrns.item_key
and trim(upper(hrtrns.api_name)) = trim(upper(g_package ||'.PROCESS_API'))
and hrtrns.item_type = p_item_type;
If (l_transaction_mode = 'INSERT') Then
create_add_training_tt(p_validate => true, p_transaction_step_id => p_transaction_step_id);
ElsIf (l_transaction_mode = 'UPDATE') Then
update_add_training_tt(p_validate => true, p_transaction_step_id => p_transaction_step_id);
If (l_transaction_mode = 'INSERT') Then
create_add_training_tt(p_validate => p_validate, p_transaction_step_id => p_transaction_step_id);
ElsIf (l_transaction_mode = 'UPDATE') Then
update_add_training_tt(p_validate => p_validate, p_transaction_step_id => p_transaction_step_id);
procedure update_add_training_tt
(p_validate IN BOOLEAN, p_transaction_step_id IN NUMBER)
IS
l_title ota_notrng_histories.trng_title%TYPE;
SAVEPOINT update_tt;
update_add_training
(p_effective_date => SYSDATE
,p_nota_history_id => l_historyid
,p_person_id => l_person_id
,p_contact_id => l_internal_contact_person
,p_trng_title => l_title
,p_provider => l_supplier
,p_type => l_trntype
,p_centre => l_location
,p_completion_date => l_completion_date
,p_award => l_award
,p_rating => l_score
,p_duration => l_duration
,p_duration_units => l_duration_unit
,p_activity_version_id => l_eq_ota_activity
,p_status => l_status
,p_verified_by_id => NULL
,p_nth_information_category => l_nth_information_category
,p_nth_information1 => l_nth_information1
,p_nth_information2 => l_nth_information2
,p_nth_information3 => l_nth_information3
,p_nth_information4 => l_nth_information4
,p_nth_information5 => l_nth_information5
,p_nth_information6 => l_nth_information6
,p_nth_information7 => l_nth_information7
,p_nth_information8 => l_nth_information8
,p_nth_information9 => l_nth_information9
,p_nth_information10 => l_nth_information10
,p_nth_information11 => l_nth_information11
,p_nth_information12 => l_nth_information12
,p_nth_information13 => l_nth_information13
,p_nth_information15 => l_nth_information15
,p_nth_information16 => l_nth_information16
,p_nth_information17 => l_nth_information17
,p_nth_information18 => l_nth_information18
,p_nth_information19 => l_nth_information19
,p_nth_information20 => l_nth_information20
,p_org_id => NULL
,p_old_object_version_number => l_old_obj_ver_no
,p_new_object_version_number => l_new_obj_ver_no
,p_business_group_id => l_business_grp_id
,p_nth_information14 => l_nth_information14
,p_customer_id => NULL
,p_organization_id => l_org_id
,p_some_warning => l_some_warning
,p_message => l_message
,p_item_type => l_item_type
,p_item_key => l_item_key
);
rollback to update_tt;
END update_add_training_tt;
PROCEDURE delete_add_training
( p_nota_history_id IN OTA_NOTRNG_HISTORIES.NOTA_HISTORY_ID%TYPE
, p_trng_title IN VARCHAR2
, p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE
, p_item_key IN WF_ITEMS.ITEM_TYPE%TYPE
, p_message OUT NOCOPY VARCHAR2
) IS
CURSOR c_get_obj_ver_no IS
SELECT OBJECT_VERSION_NUMBER
FROM OTA_NOTRNG_HISTORIES
WHERE NOTA_HISTORY_ID = p_nota_history_id;
l_proc VARCHAR2(72) := 'Delete_Add_Training';
END delete_add_training;
'INSERT');
Procedure update_add_training
(p_effective_date IN DATE
,p_nota_history_id IN NUMBER
,p_person_id IN NUMBER
,p_contact_id IN NUMBER
,p_trng_title IN VARCHAR2
,p_provider IN VARCHAR2
,p_type IN VARCHAR2
,p_centre IN VARCHAR2
,p_completion_date IN DATE
,p_award IN VARCHAR2
,p_rating IN VARCHAR2
,p_duration IN NUMBER
,p_duration_units IN VARCHAR2
,p_activity_version_id IN NUMBER
,p_status IN VARCHAR2
,p_verified_by_id IN NUMBER
,p_nth_information_category IN VARCHAR2
,p_nth_information1 IN VARCHAR2
,p_nth_information2 IN VARCHAR2
,p_nth_information3 IN VARCHAR2
,p_nth_information4 IN VARCHAR2
,p_nth_information5 IN VARCHAR2
,p_nth_information6 IN VARCHAR2
,p_nth_information7 IN VARCHAR2
,p_nth_information8 IN VARCHAR2
,p_nth_information9 IN VARCHAR2
,p_nth_information10 IN VARCHAR2
,p_nth_information11 IN VARCHAR2
,p_nth_information12 IN VARCHAR2
,p_nth_information13 IN VARCHAR2
,p_nth_information14 IN VARCHAR2
,p_nth_information15 IN VARCHAR2
,p_nth_information16 IN VARCHAR2
,p_nth_information17 IN VARCHAR2
,p_nth_information18 IN VARCHAR2
,p_nth_information19 IN VARCHAR2
,p_nth_information20 IN VARCHAR2
,p_org_id IN NUMBER
,p_old_object_version_number IN NUMBER
,p_business_group_id IN NUMBER
,p_customer_id IN NUMBER
,p_organization_id IN NUMBER
,p_some_warning OUT NOCOPY NUMBER
,p_message OUT NOCOPY VARCHAR2
,p_new_object_version_number OUT NOCOPY NUMBER
,p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE
,p_item_key IN WF_ITEMS.ITEM_TYPE%TYPE
) IS
--
-- Declare cursors and local variables
--
l_some_warning BOOLEAN;
l_proc VARCHAR2(72) := 'Update_Add_Training';
OTA_NHS_API.update_non_ota_histories
(p_validate => false
,p_effective_date => p_effective_date
,p_nota_history_id => p_nota_history_id
,p_person_id => p_person_id
,p_contact_id => p_contact_id
,p_trng_title => p_trng_title
,p_provider => p_provider
,p_type => p_type
,p_centre => p_centre
,p_completion_date => p_completion_date
,p_award => p_award
,p_rating => p_rating
,p_duration => p_duration
,p_duration_units => p_duration_units
,p_activity_version_id => p_activity_version_id
,p_status => p_status
,p_verified_by_id => p_verified_by_id
,p_nth_information_category => p_nth_information_category
,p_nth_information1 => p_nth_information1
,p_nth_information2 => p_nth_information2
,p_nth_information3 => p_nth_information3
,p_nth_information4 => p_nth_information4
,p_nth_information5 => p_nth_information5
,p_nth_information6 => p_nth_information6
,p_nth_information7 => p_nth_information7
,p_nth_information8 => p_nth_information8
,p_nth_information9 => p_nth_information9
,p_nth_information10 => p_nth_information10
,p_nth_information11 => p_nth_information11
,p_nth_information12 => p_nth_information12
,p_nth_information13 => p_nth_information13
,p_nth_information14 => p_nth_information14
,p_nth_information15 => p_nth_information15
,p_nth_information16 => p_nth_information16
,p_nth_information17 => p_nth_information17
,p_nth_information18 => p_nth_information18
,p_nth_information19 => p_nth_information19
,p_nth_information20 => p_nth_information20
,p_org_id => p_org_id
,p_object_version_number => l_new_object_version_number
,p_business_group_id => p_business_group_id
,p_customer_id => p_customer_id
-- ,p_organization_id => p_organization_id
,p_some_warning => l_some_warning
);
'UPDATE');
END update_add_training;
IF l_transaction_mode='INSERT' THEN
resultout:='COMPLETE:INSERT';
resultout:='COMPLETE:UPDATE';
SELECT full_name
FROM per_all_people_f
WHERE person_id = p_person_id
AND sysdate BETWEEN effective_start_date and effective_end_date; --Bug 5464327: date tracking of per_all_people_f is considered.
SELECT full_name
FROM per_all_people_f
WHERE person_id = p_person_id
AND sysdate BETWEEN effective_start_date and effective_end_date;
SELECT
substrb( PARTY.person_last_name,1,50) || ',' ||
substrb( PARTY.person_first_name,1,40) || ' ' ||
party.person_pre_name_adjunct
from HZ_CUST_ACCOUNT_ROLES acct_role,
HZ_PARTIES party,
HZ_RELATIONSHIPS rel,
HZ_ORG_CONTACTS org_cont,
HZ_PARTIES rel_party,
HZ_CUST_ACCOUNTS role_acct
where acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id = rel.relationship_id
and rel.subject_id = party.party_id
and rel.party_id = rel_party.party_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and acct_role.cust_account_id = role_acct.cust_account_id
and role_acct.party_id = rel.object_id
and acct_role.cust_account_role_id = p_person_id;
SELECT
substrb(PARTY.party_name,1,50)
FROM
HZ_PARTIES party,
HZ_CUST_ACCOUNTS cust_acct
WHERE
CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = p_customer_id;
SELECT OTA_GENERAL.get_org_name(p_organization_id) INTO l_value FROM DUAL;
Select
ONH.Trng_Title,
ONH.Provider,
ONH.activity_version_id,
ONH.centre,
ONH.type,
ONH.duration,
ONH.duration_units,
ONH.status,
ONH.completion_date,
ONH.award,
ONH.rating,
ONH.contact_id,
ONH.nth_information_category,
ONH.nth_information1,
ONH.nth_information2,
ONH.nth_information3,
ONH.nth_information4,
ONH.nth_information5,
ONH.nth_information6,
ONH.nth_information7,
ONH.nth_information8,
ONH.nth_information9,
ONH.nth_information10,
ONH.nth_information11,
ONH.nth_information12,
ONH.nth_information13,
ONH.nth_information14,
ONH.nth_information15,
ONH.nth_information16,
ONH.nth_information17,
ONH.nth_information18,
ONH.nth_information19,
ONH.nth_information20
From
OTA_NOTRNG_HISTORIES ONH
where
ONH.nota_history_id = p_history_id;
Select
hrtrns.transaction_step_id
From
wf_item_activity_statuses process
,wf_item_attribute_values attribute2
,wf_process_activities activity
,hr_api_transaction_steps hrtrns
Where
activity.activity_name = 'OTA_ADDTRNG_JSP_PRC'
and activity.process_item_type = 'HRSSA'
and activity.activity_item_type = 'HRSSA'
and activity.instance_id = process.process_activity
and process.activity_status = 'ACTIVE'
and process.item_type = 'HRSSA'
and hrtrns.update_person_id = p_person_id
and process.item_key = attribute2.item_key
and attribute2.item_type = process.item_type
and attribute2.name = 'TRAN_SUBMIT'
and attribute2.text_value = 'Y'
and process.item_key = hrtrns.item_key
and trim(upper(hrtrns.api_name)) = trim(upper(g_package||'.PROCESS_API'))
and hrtrns.item_type = 'HRSSA';