[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE_TXN_CURRENT_VALUES
Private procedure to update current values to previous values in
hr_api_transaction_values table
*************************************************************** */
PROCEDURE update_txn_current_values ( p_transactionId NUMBER) IS
BEGIN
IF ( p_transactionId IS NULL ) THEN
RETURN;
UPDATE hr_api_transaction_values
SET varchar2_value = previous_varchar2_value,
number_value = previous_number_value,
date_value = previous_date_value
WHERE transaction_step_id IN (
SELECT transaction_step_id
FROM hr_api_transaction_steps
WHERE transaction_id = p_transactionId
--AND api_name <> 'HR_SUPERVISOR_SS.PROCESS_API');
UPDATE_TXN_PREVIOUS_VALUES
Private procedure to update previous values to current values in
hr_api_transaction_values table
*************************************************************** */
--
PROCEDURE update_txn_previous_values ( p_transactionId NUMBER) IS
--
BEGIN
--
IF ( p_transactionId IS NULL ) THEN
RETURN;
UPDATE hr_api_transaction_values
SET previous_varchar2_value = varchar2_value,
previous_date_value = date_value,
previous_number_value = number_value
WHERE transaction_step_id IN (
SELECT transaction_step_id
FROM hr_api_transaction_steps
WHERE transaction_id = p_transactionId
--AND api_name <> 'HR_SUPERVISOR_SS.PROCESS_API');
SELECT step_history_id, datatype, name, value
FROM pqh_ss_value_history
WHERE (step_history_id,approval_history_id) IN (
SELECT sh.step_history_id, ah.approval_history_id
FROM pqh_ss_step_history sh,
pqh_ss_approval_history ah
WHERE ah.transaction_history_id = sh.transaction_history_id
AND sh.api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
AND ah.transaction_history_id = p_txnId
AND ah.approval_history_id = 0);
l_dt_update_mode VARCHAR2(50);
HR_TRANSACTION_API.update_transaction (
p_transaction_id => p_txnId
,p_transaction_effective_date => l_effectiveDate
,p_effective_date_option => NVL(l_dateOption,'E') );
SELECT item_type, item_key
FROM wf_item_activity_statuses
WHERE notification_id = p_ntfId;
SELECT SUBSTR(context,1,INSTR(context,':',1)-1)
,SUBSTR(context,INSTR(context,':')+1, ( INSTR(context,':',INSTR(context,':')+1 ) - INSTR(context,':')-1) )
FROM wf_notifications
WHERE notification_id = p_ntfId;
SELECT transaction_id
FROM hr_api_transactions
WHERE item_type = p_itemType
AND item_key = p_itemKey;
SELECT ias.notification_id
FROM WF_ITEM_ACTIVITY_STATUSES IAS
WHERE ias.item_type = p_itemType
and ias.item_key = p_itemKey
and IAS.ACTIVITY_STATUS = 'NOTIFIED'
and notification_id is not null
and rownum < 2;
SELECT process_activity
FROM WF_ITEM_ACTIVITY_STATUSES IAS
WHERE ias.item_type = p_itemType
and ias.item_key = p_itemKey
AND ias.notification_id IS NULL
and IAS.ACTIVITY_STATUS = 'NOTIFIED'
and rownum < 2;
SELECT process_activity
FROM WF_ITEM_ACTIVITY_STATUSES IAS
WHERE ias.item_type = p_itemType
and ias.item_key = p_itemKey
and ias.notification_id = p_ntfId
and IAS.ACTIVITY_STATUS = 'NOTIFIED'
and rownum < 2;
/*SELECT ias.process_activity
FROM wf_item_activity_statuses ias
WHERE ias.item_type = p_itemType
and ias.item_key = p_itemKey
and ias.activity_status = 'NOTIFIED'
and ias.process_activity in (
select pa.instance_id
FROM wf_process_activities PA,
wf_activity_attributes AA,
wf_activities WA,
wf_items WI
WHERE pa.process_item_type = ias.item_type
and wa.item_type = pa.process_item_type
and wa.name = pa.activity_name
and wi.item_type = ias.item_type
and wi.item_key = ias.item_key
and wi.begin_date >= wa.begin_date
and wi.begin_date < nvl(wa.end_date,wi.begin_date+1)
and aa.activity_item_type = wa.item_type
and aa.activity_name = wa.name
and aa.activity_version = wa.version
and aa.type = 'FORM'
)
order by Decode(ias.activity_result_code,'#NULL',1,2);
SELECT process_activity
from
(select process_activity
FROM WF_ITEM_ACTIVITY_STATUSES IAS
WHERE ias.item_type = p_itemType
and ias.item_key = p_itemKey
and ias.activity_status = 'NOTIFIED'
and ias.process_activity in (
select wpa.instance_id
FROM WF_PROCESS_ACTIVITIES WPA,
WF_ACTIVITY_ATTRIBUTES WAA,
WF_ACTIVITIES WA,
WF_ITEMS WI
WHERE wpa.process_item_type = ias.item_type
and wa.item_type = wpa.process_item_type
and wa.name = wpa.activity_name
and wi.item_type = ias.item_type
and wi.item_key = ias.item_key
and wi.begin_date >= wa.begin_date
and wi.begin_date < nvl(wa.end_date,wi.begin_date+1)
and waa.activity_item_type = wa.item_type
and waa.activity_name = wa.name
and waa.activity_version = wa.version
and waa.type = 'FORM'
)
order by begin_date desc)
where rownum<=1;
SELECT instance_label,ias.process_activity actvityId, ias.notification_id ntfId
FROM WF_ITEM_ACTIVITY_STATUSES IAS,
WF_PROCESS_ACTIVITIES PA
WHERE IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
AND ias.item_type = pa.process_item_type
and ias.item_type = p_itemType
and ias.item_key = p_itemKey
and ias.activity_status = 'NOTIFIED'
and ias.process_activity <> p_activity
and not exists (select 'e'
from WF_ACTIVITIES wa, WF_ACTIVITY_ATTRIBUTES waa, WF_ITEMS wi
where wa.item_type = pa.process_item_type
and wa.name = pa.activity_name
and wi.item_type = ias.item_type
and wi.item_key = ias.item_key
and wi.begin_date between wa.begin_date and nvl(wa.end_date,wi.begin_date)
and waa.activity_item_type = wa.item_type
and waa.activity_name = wa.name
and waa.activity_version = wa.version
and waa.type = 'FORM');
SELECT nvl(decode(wav.text_value, null, hat.status,
decode(hat.status,'S','SUBMIT',hat.status)),'N')
FROM hr_api_transactions hat,
wf_item_attribute_values wav
WHERE hat.item_type = wav.item_Type
AND hat.item_key = wav.item_Key
AND wav.item_type = itemType
AND wav.item_key = itemKey
AND wav.name = 'SAVED_ACTIVITY_ID';
SELECT transaction_id, status, transaction_state, NVL(transaction_effective_date,sysdate),
assignment_id, effective_date_option, plan_id, rptg_grp_id,
NVL(selected_person_id,-1), process_name,function_id
FROM hr_api_transactions
WHERE item_type = p_itemType
AND item_key = p_itemKey;
SELECT 'X'
FROM hr_api_transaction_steps
WHERE transaction_id = c_txnId
AND api_name IN (
'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API',
'HR_SUPERVISOR_SS.PROCESS_API',
'HR_TERMINATION_SS.PROCESS_API',
'HR_PAY_RATE_SS.PROCESS_API',
'PER_SSHR_CHANGE_PAY.PROCESS_API' );
SELECT 'X'
FROM per_all_assignments_f
WHERE assignment_id = c_asgnId
AND effective_end_date <= trunc(c_effective_date)
AND assignment_status_type_id in (
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status in ('TERM_ASSIGN', 'END'));
SELECT to_char(ser.actual_termination_date,g_date_format)
FROM per_periods_of_service ser,
per_all_assignments_f ass
where ass.period_of_service_id = ser.period_of_service_id
AND ass.assignment_id = c_asgnId
AND TRUNC(c_effective_date) between ass.effective_start_date AND ass.effective_end_date ;
SELECT 'X'
FROM per_all_people_f
WHERE nvl(current_employee_flag,'N') <> 'Y'
AND nvl(current_applicant_flag,'N') <> 'Y'
AND nvl(current_npw_flag,'N') <> 'Y'
AND TRUNC(c_effective_date) BETWEEN effective_start_date AND effective_end_date
AND person_id = c_personId;
SELECT parameters
FROM fnd_form_functions
WHERE function_id = c_functionId;
SELECT change_date
FROM per_pay_proposals
WHERE assignment_id = c_assignmentId
AND business_group_id = c_bgId
ORDER BY change_date desc ;
IF ( NVL(l_flowName,'x') <> 'HrCommonInsertOab' AND
NVL(l_flowName, 'x') <> 'CWKPlacement' AND
p_personid <> -1 AND
NVL(p_calledFrom,'X') <> 'FINAL_VALIDATION') THEN
BEGIN
select 'x'
into dummy
from per_people_f
where person_id = p_personId
and l_effDate between effective_start_date and effective_end_date;
IF ( NVL(l_flowName,'x') = 'HrCommonInsertOab' OR
NVL(l_flowName, 'x') = 'CWKPlacement' ) THEN
if p_effDateOption IS NULL then
p_effDateOption := 'E';
SELECT transaction_step_id
FROM hr_api_transaction_steps
WHERE transaction_id = p_txnId
AND api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API';
SELECT 'X'
FROM hr_api_transaction_values tv,
per_assignments_f af
WHERE af.assignment_id = p_assignmentId
AND tv.transaction_step_id = c_txn_step_id
AND tv.name = 'P_OBJECT_VERSION_NUMBER'
AND c_effDate BETWEEN af.effective_start_date AND af.effective_end_date
AND NVL(original_number_value,number_value) = af.object_version_number;
UPDATE wf_item_activity_statuses
SET activity_status = 'COMPLETE',
activity_result_code ='#NULL'
WHERE item_type = p_itemType
AND item_key = p_itemKey
AND process_activity = l_currentActivityId;
hr_transaction_api.update_transaction(
p_transaction_id => p_txnId,
p_status => p_status,
p_transaction_state => l_newState );
SELECT transaction_id, status, transaction_state
FROM hr_api_transactions
WHERE item_type = p_itemType
AND item_key = p_itemKey;
/* update_txn_previous_values (l_transactionId); */ -- ##history
hr_transaction_api.update_transaction(
p_transaction_id => l_transactionId,
p_status => l_newStatus,
p_transaction_state => l_newState );
hr_transaction_api.update_transaction(
p_transaction_id => l_transactionId,
p_status => l_newStatus,
p_transaction_state => l_newState );
hr_transaction_api.update_transaction(
p_transaction_id => l_transactionId,
p_status => l_newStatus,
p_transaction_state => l_newState );
/*update_txn_current_values( l_transactionId );
hr_transaction_api.update_transaction(
p_transaction_id => l_transactionId,
p_status => l_newStatus,
p_transaction_state => l_newState );
/*hr_transaction_api.update_transaction(
p_transaction_id => l_transactionId,
p_status => l_newStatus,
p_transaction_state => l_newState );*/
SELECT api_name,transaction_step_id
FROM hr_api_transaction_steps
WHERE transaction_id = p_txnId;
HR_TRANSACTION_API.update_transaction (
p_transaction_id => p_txnId
,p_transaction_effective_date => l_effectiveDate
,p_effective_date_option => NVL(p_effectiveDateOption,'E') );
UPDATE hr_api_transaction_values
SET date_value = l_effectiveDate
WHERE transaction_step_id = I.transaction_step_id
AND name like 'P%DATE%';
select status into l_status
from hr_api_transactions
where transaction_id=l_transactionId;
/* hr_transaction_api.update_transaction (
p_transaction_id => l_transactionId
,p_transaction_state => 'T' );
SELECT NVL(status,'N')
FROM hr_api_transactions
WHERE item_type = itemType
AND item_key = itemKey;
* p_approverIndex - Selected users index in the approval chain, to set wf attribute
* p_txnId - To fetch last default approver before the approver who is performing RFC
*/
PROCEDURE return_for_correction (
p_itemType IN VARCHAR2
, p_itemKey IN VARCHAR2
, p_userId IN VARCHAR2 -- NOTE: not really userid, it is the personId
, p_userName IN VARCHAR2
, p_userDisplayName IN VARCHAR2
, p_ntfId IN VARCHAR2
, p_note IN VARCHAR2
, p_approverIndex IN NUMBER
, p_txnId IN VARCHAR2) IS
--
l_activity NUMBER;
SELECT 'X'
FROM wf_item_attribute_values
WHERE item_type = p_itemType
AND item_key = p_itemKey
AND name like 'ADDITIONAL_APPROVER_%'
AND number_value = p_userId;
SELECT pth.employee_id
FROM pqh_ss_approval_history pah,
fnd_user pth
WHERE pah.user_name = pth.user_name
AND pah.transaction_history_id = p_txnId
AND approval_history_id = (
SELECT MAX(approval_history_id)
FROM pqh_ss_approval_history pah1,
fnd_user pth1
WHERE pah1.user_name = pth1.user_name
AND pah1.transaction_history_id = pah.transaction_history_id
AND pth1.employee_id IN (
SELECT pth2.employee_id --, pth2.user_name, approval_history_id
FROM pqh_ss_approval_history pah2,
fnd_user pth2
WHERE pah2.user_name = pth2.user_name
AND pah2.transaction_history_id = pah.transaction_history_id
AND approval_history_id < (
SELECT MIN(approval_history_id)
FROM pqh_ss_approval_history
WHERE transaction_history_id = pah.transaction_history_id
AND user_name = p_userName
AND approval_history_id > 0
)
and approval_history_id > 0
MINUS
SELECT number_value
FROM wf_item_attribute_values
WHERE item_type = p_itemType
AND item_key = p_itemKey
AND name like 'ADDITIONAL_APPROVER_%'
)
);
select user_name
into l_userName
from fnd_user
where employee_id=p_userId;
SELECT wma.display_name
FROM wf_notifications wn, wf_message_attributes_vl wma
WHERE wn.notification_id = p_ntfId
AND wn.message_name = wma.message_name
AND wma.message_type = p_itemType
AND wma.name = 'EDIT_TXN_URL';
SELECT -- to_char(NVL(transaction_effective_date,sysdate),g_date_format),
-- assignment_id, selected_person_id,
NVL(status,'N'), NVL(fnd_profile.value('PQH_ALLOW_APPROVER_TO_EDIT_TXN'),'N')
FROM hr_api_transactions
WHERE item_type = c_itemType
AND item_key = c_itemKey;
SELECT transaction_id, NVL(effective_date_option,'X')
FROM hr_api_transactions
WHERE item_type = itemType
AND item_key = itemKey;
HR_TRANSACTION_API.update_transaction (
p_transaction_id => l_txnId
,p_transaction_effective_date => l_effectiveDate );
UPDATE hr_api_transaction_values
SET date_value = l_effectiveDate
WHERE datatype = 'DATE'
AND name = 'P_PASSED_EFFECTIVE_DATE'
AND transaction_step_id = (
SELECT transaction_step_id
FROM hr_api_transaction_steps
WHERE transaction_id = l_txnId
AND api_name = 'HR_SUPERVISOR_SS.PROCESS_API' );
select fa.application_id
from fnd_application fa
where fa.application_short_name = p_apps_short_name ;
hr_transaction_api.update_transaction(
p_transaction_id => get_transaction_id(itemType,itemKey),
p_status => 'E');
procedure delete_txn_notification(
p_itemType IN VARCHAR2
,p_itemKey IN VARCHAR2
,p_transactionId IN VARCHAR2
) is
l_activity_id number;
SELECT name
FROM wf_roles
WHERE orig_system = 'PQH_ROLE'
AND orig_system_id = p_role_id ;
hr_transaction_api.update_transaction(
p_transaction_id => get_transaction_id(itemType,itemKey),
p_status => 'E');
SELECT rownum h_sequence
,h_notification_id
,h_from_role
,h_action
,h_comment
,h_action_date
FROM (
SELECT h_notification_id
,h_from_role
,h_action
,h_comment
,h_action_date
FROM (
SELECT 99999999 h_sequence
,ias.notification_id h_notification_id
,ias.assigned_user h_from_role
,wf_core.activity_result (a.result_type
,ias.activity_result_code) h_action
,
(
SELECT text_value
FROM wf_notification_attributes
WHERE notification_id = ias.notification_id
AND name = 'WF_NOTE'
) h_comment
,nvl (ias.end_date
,ias.begin_date) h_action_date
FROM wf_item_activity_statuses ias
,wf_activities a
,wf_process_activities pa
,wf_items i
,WF_ITEM_TYPES IT
where IAS.ITEM_TYPE = 'HRSSA'
and IAS.ITEM_KEY = '15018'
and IAS.NOTIFICATION_ID is not null
and nvl(RESULT_TYPE,'*') NOT IN ( '*','HR_DONE')
and IAS.ITEM_TYPE = I.ITEM_TYPE
and IAS.ITEM_KEY = I.ITEM_KEY
and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE)
and I.ITEM_TYPE = IT.NAME
and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
and PA.ACTIVITY_NAME = A.NAME
and PA.ACTIVITY_ITEM_TYPE= A.ITEM_TYPE
and (IAS.ACTIVITY_RESULT_CODE is null or IAS.ACTIVITY_RESULT_CODE not in ('SFL','#NULL'))
UNION ALL
SELECT 99999999 h_sequence
,ias.notification_id h_notification_id
,ias.assigned_user h_from_role
,wf_core.activity_result (a.result_type
,ias.activity_result_code) h_action
,
(
SELECT text_value
FROM wf_notification_attributes
WHERE notification_id = ias.notification_id
AND name = 'WF_NOTE'
) h_comment
,nvl (ias.end_date
,ias.begin_date) h_action_date
FROM wf_item_activity_statuses_h ias
,wf_activities a
,wf_process_activities pa
,wf_items i
,WF_ITEM_TYPES IT
where IAS.ITEM_TYPE = 'HRSSA'
and IAS.ITEM_KEY = '15018'
and IAS.NOTIFICATION_ID is not null
and (IAS.ACTIVITY_RESULT_CODE is null or IAS.ACTIVITY_RESULT_CODE not in ('SFL','#NULL'))
and nvl(RESULT_TYPE,'*') NOT IN ( '*','HR_DONE')
and IAS.ITEM_TYPE = I.ITEM_TYPE
and IAS.ITEM_KEY = I.ITEM_KEY
and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE)
and I.ITEM_TYPE = IT.NAME
and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
and PA.ACTIVITY_NAME = A.NAME
and PA.ACTIVITY_ITEM_TYPE= A.ITEM_TYPE
UNION ALL
SELECT c.sequence h_sequence
,c.notification_id h_notification_id
,c.from_role h_from_role
,wf_core.translate (c.action) h_action
,c.user_comment h_comment
,c.comment_date h_action_date
FROM wf_item_activity_statuses ias
,wf_comments c
WHERE ias.item_type = x_item_type
AND ias.item_key = x_item_key
-- AND ias.process_activity = x_actid
AND ias.notification_id = c.notification_id
AND c.action NOT IN ('RESPOND','RESPOND_WA','RESPOND_RULE'
,'SEND','CANCEL')
UNION ALL
SELECT c.sequence h_sequence
,c.notification_id h_notification_id
,c.from_role h_from_role
,wf_core.translate (c.action) h_action
,c.user_comment h_comment
,c.comment_date h_action_date
FROM wf_item_activity_statuses_h ias
,wf_comments c
WHERE ias.item_type = x_item_type
AND ias.item_key = x_item_key
-- AND ias.process_activity = x_actid
AND ias.notification_id = c.notification_id
AND c.action NOT IN ('RESPOND','RESPOND_WA','RESPOND_RULE'
,'SEND','CANCEL')
)
ORDER BY h_action_date desc
,h_notification_id desc
,h_sequence desc
);
select OWNER_ROLE, BEGIN_DATE
into l_owner_role, l_begin_date
from WF_ITEMS
where ITEM_TYPE = l_itype
and ITEM_KEY = l_ikey;
SELECT process_activity
FROM WF_ITEM_ACTIVITY_STATUSES IAS
WHERE ias.item_type = p_itemType
AND ias.item_key = p_itemKey
AND ias.activity_status = 'NOTIFIED'
ORDER BY begin_date DESC, execution_time DESC;