CREATE OR REPLACE Package APPS.FUJ_HR_EIT_WORKFLOW_PKG
is
--
-- Procedure create the Request
--
Procedure CREATE_REQUEST ( PP_SELECTED_USER_NAME varchar2--1024
,PP_CREATOR_USER_NAME varchar2
,PP_ITEM_TYPE varchar2--'HRSSA'
,PP_RESPONSIBILITY_NAME varchar2--'MOFI Absence Management';
,PP_CALLED_FROM varchar2 --'MOF_HR_LOA_CREATE_DECREE_SS'
,PP_process varchar2--'MOF_HR_EIT_LOA_CREATE_PRC'
,PP_EIT_NAME varchar2--'MOF_HR_LOA_CREATE_DECREE'
,PP_INFORMATION1 varchar2 DEFAULT NULL
,PP_INFORMATION2 varchar2 DEFAULT NULL
,PP_INFORMATION3 varchar2 DEFAULT NULL
,PP_INFORMATION4 varchar2 DEFAULT NULL
,PP_INFORMATION5 varchar2 DEFAULT NULL
,PP_INFORMATION6 varchar2 DEFAULT NULL
,PP_INFORMATION7 varchar2 DEFAULT NULL
,PP_INFORMATION8 varchar2 DEFAULT NULL
,PP_INFORMATION9 varchar2 DEFAULT NULL
,PP_INFORMATION10 varchar2 DEFAULT NULL
,PP_INFORMATION11 varchar2 DEFAULT NULL
,PP_INFORMATION12 varchar2 DEFAULT NULL
,PP_INFORMATION13 varchar2 DEFAULT NULL
,PP_INFORMATION14 varchar2 DEFAULT NULL
,PP_INFORMATION15 varchar2 DEFAULT NULL
,PP_INFORMATION16 varchar2 DEFAULT NULL
,PP_INFORMATION17 varchar2 DEFAULT NULL
,PP_INFORMATION18 varchar2 DEFAULT NULL
,PP_INFORMATION19 varchar2 DEFAULT NULL
,PP_INFORMATION20 varchar2 DEFAULT NULL
,PP_INFORMATION21 varchar2 DEFAULT NULL
,PP_INFORMATION22 varchar2 DEFAULT NULL
,PP_INFORMATION23 varchar2 DEFAULT NULL
,PP_INFORMATION24 varchar2 DEFAULT NULL
,PP_INFORMATION25 varchar2 DEFAULT NULL
,PP_INFORMATION26 varchar2 DEFAULT NULL
,PP_INFORMATION27 varchar2 DEFAULT NULL
,PP_INFORMATION28 varchar2 DEFAULT NULL
,PP_INFORMATION29 varchar2 DEFAULT NULL
,PP_INFORMATION30 varchar2 DEFAULT NULL
,p_activity_id number
--OUT
,v_msg out varchar2
);
PROCEDURE approve_reject_p (pAction IN VARCHAR2, --APPROVED/REJECTED
pComments IN VARCHAR2, -- User Comments -- Optional
pNotification_id IN NUMBER, -- Notification ID
pStatus OUT VARCHAR2,
pMessage OUT VARCHAR2
);
--
-- Function to Approve By notificaion ID
--
Function Approve_Request(p_Not_ID Number
,p_responder varchar2
)
return Varchar2;
--
-- Function to Reject By notificaion ID
--
Function Reject_Request(p_Not_ID Number
,p_responder varchar2
)
return Varchar2;
--
-- Function to Close (FYI) Notifications By notificaion ID
--
Function Close_FYI_Notification (p_Not_ID Number
,p_responder varchar2
)
return Varchar2;
procedure Approve_Request(p_Not_ID Number
,p_responder varchar2
);
end;
/
CREATE OR REPLACE Package body APPS.FUJ_HR_EIT_WORKFLOW_PKG
is
--
--Function Used to Create Work Flow and return with Item Key
--
function Create_WorkFlow_Process (P_SELECTED_PERSON_ID number
,P_CREATOR_PERSON_ID NUMBER
,P_SELECTED_USER_NAME VARCHAR2
,P_CREATOR_USER_NAME VARCHAR2
,P_CALLED_FROM Varchar2
,P_process Varchar2
,p_activity_id number
,V_output Out Varchar2
)
return number
is
v_itemtype varchar2(500);
v_itemkey varchar2(500);
v_process varchar2(500);
V_PROCESS_DISPLAY_NAME varchar2(500);
V_CALLED_FROM varchar2(500);
v_user_key varchar2(500);
v_owner_role varchar2(500);
v_USER_NAME varchar2(500);
V_CURRENT_PERSON_ID number;
V_ASSIGNMENT_ID number;
V_CURRENT_PERSON_DISPLAY_NAME varchar2(500);
out_msg varchar2(1500);
V_activity_id number;
v_p_responsibility_name varchar2(1500);
v_app_id number;
V_CREATOR_PERSON_DISPLAY_NAME VARCHAR2(2000);
Exc_Error Exception;
begin
dbms_output.put_line('loc 1');
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--
--Variables
--
--V_USER_NAME :=P_USER_NAME;
V_CURRENT_PERSON_ID :=P_CREATOR_PERSON_ID;
V_CALLED_FROM :=P_CALLED_FROM;--'LSG_HR_LOA_CREATE_DECREE_SS';
v_process :=P_process; --'MOF_HR_EIT_LOA_CREATE_PRC';
--------------------------------------------------------------------------------------------
--Get Person_id
Begin
select p.FIRST_NAME||' '||p.LAST_NAME NAME
into V_CURRENT_PERSON_DISPLAY_NAME
from per_all_people_f p
where 1=1
and trunc(sysdate) between p.EFFECTIVE_START_DATE and p.EFFECTIVE_END_DATE
and p.person_id=P_SELECTED_PERSON_ID
;
dbms_output.put_line ('V_CURRENT_PERSON_DISPLAY_NAME='||V_CURRENT_PERSON_DISPLAY_NAME);
Exception
when no_data_found
then
v_output :='Invalide User Name or Not Found';
raise Exc_Error;
End;
Begin
select p.FIRST_NAME||' '||p.LAST_NAME NAME
into V_CREATOR_PERSON_DISPLAY_NAME
from per_all_people_f p
where 1=1
and trunc(sysdate) between p.EFFECTIVE_START_DATE and p.EFFECTIVE_END_DATE
and p.person_id=P_CREATOR_PERSON_ID
;
dbms_output.put_line ('V_CREATOR_PERSON_DISPLAY_NAME='||V_CURRENT_PERSON_DISPLAY_NAME);
Exception
when no_data_found
then
v_output :='Invalide User Name or Not Found';
raise Exc_Error;
End;
--get assignment_id
Begin
select distinct p.assignment_id
into V_ASSIGNMENT_ID
from per_all_assignments_f p
where 1=1
and trunc(sysdate) between p.EFFECTIVE_START_DATE and p.EFFECTIVE_END_DATE
and p.person_id=P_SELECTED_PERSON_ID
and primary_flag ='Y'
;
dbms_output.put_line ('V_assignment_id ='||V_ASSIGNMENT_ID);
Exception
when no_data_found
then
v_output :='Invalide assignment_id';
raise Exc_Error;
End;
--
--Work Flow variables
--
-- V_activity_id :=184884;
V_activity_id := p_activity_id;--339
v_user_key :=null;
v_owner_role :=P_CREATOR_USER_NAME;
-------
Begin
select distinct ITEM_TYPE ,DISPLAY_NAME --Should be Handeled 06092015 --[Error] ORA-01422: exact fetch returns more than requested number of rows
into v_itemtype ,V_PROCESS_DISPLAY_NAME
from wf_activities_tl
where 1=1
and name=P_process
and LANGUAGE='US';
Exception
when no_data_found
then
v_output :='Invalide Process Name or Not Found';
raise Exc_Error;
End;
dbms_output.put_line('loc2');
-------
select hr_workflow_item_key_s.nextval
into v_itemkey
from dual;
/*
---------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--
--NITIALIZE
--
v_p_responsibility_name :='MOFI Absence Management';
v_app_id :=800;
MOF_Sec_init_user ( p_user_name =>V_USER_NAME
,p_responsibility_name =>v_p_responsibility_name
,p_app_id =>v_app_id
,o_msg =>out_msg
);
--dbms_output.put_line ('Done INITIALIZE'||out_msg);
*/
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--
--CreateProcess
--
dbms_output.put_line('loc 3');
wf_engine.CreateProcess( ITEMTYPE =>v_itemtype
, ITEMKEY =>v_itemkey
, PROCESS =>v_process
, USER_KEY =>v_user_key
, OWNER_ROLE =>v_owner_role
);
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--
--SetItemAttr
--
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'USER_NAME',P_CREATOR_USER_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_DISPLAY_NAME',V_PROCESS_DISPLAY_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_NAME',v_process);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_ID',P_SELECTED_PERSON_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_USERNAME',P_SELECTED_USER_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_DISPLAY_NAME',V_CURRENT_PERSON_DISPLAY_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_ASSIGNMENT_ID',V_ASSIGNMENT_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_EFFECTIVE_DATE',sysdate);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_ID',P_CREATOR_PERSON_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_USERNAME',P_CREATOR_USER_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_DISPLAY_NAME',V_CREATOR_PERSON_DISPLAY_NAME );
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'P_ASSIGNMENT_ID',V_ASSIGNMENT_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_AME_TRAN_TYPE_ATTR','SSHRMS');
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'P_CALLED_FROM',V_CALLED_FROM);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'P_EFFECTIVE_DATE', TO_CHAR(SYSDATE,'YYYY-MM-DD'));
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_FIRST_ACTIVITY_ID',V_activity_id);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'SESSION_ID',fnd_global.session_id);--??
dbms_output.put_line('loc4');
wf_engine.AddItemAttr (v_itemtype, v_itemkey,'TRANSACTION_PRIVILEGE','PRIVATE',null,null);
wf_engine.AddItemAttr (v_itemtype,v_itemkey ,'HR_REVIEW_HEADER_STYLE','PER_HEADER',null,null);
--Important to open page from EBS
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'APPROVAL_GENERIC_URL','JSP:/OA_HTML/OA.jsp?akRegionCode=HR_REVIEW_TOP_SS&'||'akRegionApplicationId=800&'||'NtfId=-&'||'#NID-&'||'retainAM=Y&'||'OAFunc='||V_CALLED_FROM);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_GENERIC_URL_REDIRECT','OA.jsp?akRegionCode=HR_REVIEW_TOP_SS&'||'akRegionApplicationId=800&'||'OAFunc='||V_CALLED_FROM);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PERZ_FUNCTION_NAME_ATTR',V_CALLED_FROM);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PERZ_LOCALIZATION_CODE_ATTR','AE');
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_REVIEW_TEMPLATE_RN_ATTR','HR_REVIEWRN_NTF');
--dbms_output.put_line ('Done SetItemAttr');
--added by afzal from here and resolved error Error:You do not have access to the selected person's record. Please contact your Human Resources Adminstrator.
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FYI_NTF_DETAILS','Y');
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PERZ_ORGANIZATION_ID_ATTR','81'); --mandatory change 1 business group
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_ORDER_STRING','%347867%1%347874%2%347865%3%'); --mandatory change 2, chk from any txn applied from front end from table WF_ITEM_ATTRIBUTE_values
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'SAVE_ATTACHMENT','Y');
wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_AME_APP_ID_ATTR',800);
dbms_output.put_line('loc5');
return v_itemkey;
Exception
when Exc_Error
then
v_itemkey :=-5;
return v_itemkey;
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--HR_APPROVAL_PRC_VERSION V5
--PROCESS_ORDER_STRING %184884%1%
--TRAN_SUBMIT W
end;
--
--Procedure to Update WorkFlow Process Attributes
--Should Run After Create transactions and before start process Aprovals
Procedure Update_WorkFlow_Attributes (P_itemtype varchar2
, P_itemkey varchar2
, P_TRANSACTION_ID Number
)
is
begin
wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'TRAN_SUBMIT','Y');
wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'TRANSACTION_ID',P_TRANSACTION_ID);
wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'HR_RUNTIME_APPROVAL_REQ_FLAG','YES_DYNAMIC');
wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'HR_NTF_ATTACHMENTS_ATTR','FND:entity=PQH_SS_ATTACHMENT&'||'pk1name=TransactionId&'||'pk1value='||P_TRANSACTION_ID);
end;
--
--Procedure to Start WorkFlow Process
--
Procedure Start_WorkFlow_Process (P_itemtype varchar2
, P_itemkey varchar2
)
is
begin
wf_engine.StartProcess(itemtype => P_itemtype
, itemkey => P_itemkey
);
--commit;
end;
--
--Procedure to Start Approval Process
--
Procedure Start_Approval_Process (P_itemtype varchar2
, P_itemkey varchar2
,p_activity_id number
)
is
v_result varchar2(5000);
begin
pqh_ss_workflow.start_approval_wf ( itemtype =>P_itemtype,
itemkey =>P_itemkey,
--actid =>'184884',
actid => p_activity_id,--339
funcmode =>'RUN',
result =>v_result );
dbms_output.put_line ('v_result='||v_result);
end;
--
FUNCTION CREATE_TRANSACTION ( P_SELECTED_PERSON_ID NUMBER
,P_CREATOR_PERSON_ID NUMBER
, P_ITEM_TYPE VARCHAR2
, P_ITEM_KEY VARCHAR2
, P_PROCESS_NAME VARCHAR2
, P_CALLED_FROM VARCHAR2
, P_EIT_NAME VARCHAR2
, P_ACTIVITY_ID NUMBER
,P_INFORMATION1 varchar2 DEFAULT NULL
,P_INFORMATION2 varchar2 DEFAULT NULL
,P_INFORMATION3 varchar2 DEFAULT NULL
,P_INFORMATION4 varchar2 DEFAULT NULL
,P_INFORMATION5 varchar2 DEFAULT NULL
,P_INFORMATION6 varchar2 DEFAULT NULL
,P_INFORMATION7 varchar2 DEFAULT NULL
,P_INFORMATION8 varchar2 DEFAULT NULL
,P_INFORMATION9 varchar2 DEFAULT NULL
,P_INFORMATION10 varchar2 DEFAULT NULL
,P_INFORMATION11 varchar2 DEFAULT NULL
,P_INFORMATION12 varchar2 DEFAULT NULL
,P_INFORMATION13 varchar2 DEFAULT NULL
,P_INFORMATION14 varchar2 DEFAULT NULL
,P_INFORMATION15 varchar2 DEFAULT NULL
,P_INFORMATION16 varchar2 DEFAULT NULL
,P_INFORMATION17 varchar2 DEFAULT NULL
,P_INFORMATION18 varchar2 DEFAULT NULL
,P_INFORMATION19 varchar2 DEFAULT NULL
,P_INFORMATION20 varchar2 DEFAULT NULL
,P_INFORMATION21 varchar2 DEFAULT NULL
,P_INFORMATION22 varchar2 DEFAULT NULL
,P_INFORMATION23 varchar2 DEFAULT NULL
,P_INFORMATION24 varchar2 DEFAULT NULL
,P_INFORMATION25 varchar2 DEFAULT NULL
,P_INFORMATION26 varchar2 DEFAULT NULL
,P_INFORMATION27 varchar2 DEFAULT NULL
,P_INFORMATION28 varchar2 DEFAULT NULL
,P_INFORMATION29 varchar2 DEFAULT NULL
,P_INFORMATION30 varchar2 DEFAULT NULL
)
RETURN NUMBER
is
v_validate boolean default false ;
v_creator_person_id number;
v_transaction_privilege varchar2(500);
v_product_code varchar2(500) default null;
v_url varchar2(500) default null;
v_status varchar2(500) default null;
v_section_display_name varchar2(500) default null;
v_function_id number;
v_transaction_ref_table varchar2(500) default null;
v_transaction_ref_id number default null;
v_transaction_type varchar2(500) default null;
v_assignment_id number default null;
v_api_addtnl_info varchar2(500) default null;
v_selected_person_id number default null;
v_item_type varchar2(500) default null;
v_item_key varchar2(500) default null;
v_transaction_effective_date date default null;
v_process_name varchar2(500) default null;
v_plan_id number default null;
v_rptg_grp_id number default null;
v_effective_date_option varchar2(500) default null;
V_activity_id number;
V_ACTIVE_ROW_ID number;
v_api_name varchar2(500);
V_EIT_NAME varchar2(500);
v_dummy varchar2(500) default null;
--------OUT---------
v_transaction_id number ;
V_transaction_step_id number ;
V_object_version_number number ;
P_ID_FLEX_NUM NUMBER;
V_BUSINESS_GROUP_ID NUMBER;
ln_analysis_criteria_id number;
V_EIT_FULL_NAME VARCHAR2(200);
Begin
--
--Variables
--
--constants-----------------------------------------------------
v_validate :=FALSE;
V_BUSINESS_GROUP_ID := 81; --mandatory change 3
--P_ID_FLEX_NUM := 50455;
v_transaction_privilege :='PRIVATE';
v_status :='W';--?? --Y
v_transaction_ref_table :='HR_API_TRANSACTIONS';
v_transaction_ref_id :=null;-------------------------
v_transaction_type :='WF';
v_product_code :=null;
v_url :=null;
v_section_display_name :=null;
v_api_addtnl_info :=null;
v_plan_id :=null;
v_rptg_grp_id :=null;
v_effective_date_option :=null;
--Variables-----------------------------------------------------
v_item_type :=P_item_type;--'HRSSA';
v_item_key :=P_item_key ;--217929;--**
v_process_name :=P_process_name;--
v_creator_person_id :=P_CREATOR_PERSON_ID;
--v_assignment_id := 22432; --
v_selected_person_id :=P_SELECTED_PERSON_ID;
v_transaction_effective_date :=sysdate;
--Step
V_activity_id := p_activity_id;
--V_ACTIVE_ROW_ID :='-987123654';
v_api_name :='HR_PROCESS_EIT_SS.PROCESS_API';--constant
V_EIT_NAME := P_EIT_NAME;--
v_dummy :=TO_CHAR(SYSDATE,'yyyymmddhh24miss')||P_CREATOR_PERSON_ID;
--
--Get Function_id
--
select FUNCTION_ID
into v_function_id
from FND_FORM_FUNCTIONS
where 1=1
and FUNCTION_NAME=P_CALLED_FROM;
/*
SELECT DISTINCT NVL(DESCRIPTION,DESCRIPTIVE_FLEX_CONTEXT_NAME)--, ID_FLEX_NUM
INTO V_EIT_FULL_NAME--, P_ID_FLEX_NUM
FROM FND_DESCR_FLEX_CONTEXTS_VL
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = V_EIT_NAME
AND APPLICATION_ID = 800
AND DESCRIPTIVE_FLEXFIELD_NAME = 'Extra Person Info DDF'
--AND ID_FLEX_CODE = 'PEA'
;
*/
Begin
select distinct p.assignment_id
into V_ASSIGNMENT_ID
from per_all_assignments_f p
where 1=1
and trunc(sysdate) between p.EFFECTIVE_START_DATE and p.EFFECTIVE_END_DATE
and p.person_id = P_SELECTED_PERSON_ID
and primary_flag ='Y'
;
dbms_output.put_line ('V_assignment_id ='||V_ASSIGNMENT_ID);
Exception
when no_data_found
then
dbms_output.put_line ('Error ='||sqlerrm);
End;
--
--Start create_transaction-----------------------------------------------------
--
hr_transaction_api.create_transaction
(p_validate =>v_validate
,p_creator_person_id =>v_creator_person_id
,p_transaction_privilege =>v_transaction_privilege
,p_product_code =>v_product_code
,p_url =>v_url
,p_status =>v_status
,p_section_display_name =>v_section_display_name
,p_function_id =>v_function_id
,p_transaction_ref_table =>v_transaction_ref_table
,p_transaction_ref_id =>v_transaction_ref_id
,p_transaction_type =>v_transaction_type
,p_assignment_id =>v_assignment_id
,p_api_addtnl_info =>v_api_addtnl_info
,p_selected_person_id =>v_selected_person_id
,p_item_type =>v_item_type
,p_item_key =>v_item_key
,p_transaction_effective_date => trunc(v_transaction_effective_date)
,p_process_name =>v_process_name
,p_plan_id =>v_plan_id
,p_rptg_grp_id =>v_rptg_grp_id
,p_effective_date_option =>v_effective_date_option
--Out
,p_transaction_id =>v_transaction_id
) ;
dbms_output.put_line ('v_transaction_id='||v_transaction_id);
--
--Start create_transaction_Step
--
hr_transaction_api.create_transaction_step
(p_validate =>v_validate
,p_creator_person_id =>v_creator_person_id
,p_transaction_id =>v_transaction_id
,p_api_name => v_api_name
,p_api_display_name =>null
,p_item_type =>v_item_type
,p_item_key =>v_item_key
,p_activity_id =>V_activity_id
--Out
,p_transaction_step_id => V_transaction_step_id
,p_object_version_number => V_object_version_number
);
/*
--Inserting Record in Per anslysis criteria table
--
select PER_ANALYSIS_CRITERIA_S.nextval
into ln_analysis_criteria_id
from dual;
ln_analysis_criteria_id := ln_analysis_criteria_id;
insert into per_analysis_criteria
(analysis_criteria_id,id_flex_num,summary_flag,enabled_flag,last_update_date,last_updated_by,last_update_login,created_by,creation_date
,segment1,segment10,segment11,segment12,segment13,segment3)
values
(ln_analysis_criteria_id,P_ID_FLEX_NUM,'N','Y',sysdate,-1,-1,-1,sysdate
,P_segment1 ,P_segment10,P_segment11,P_segment12,P_segment13,P_segment3
);
*/
dbms_output.put_line ('V_transaction_step_id='||V_transaction_step_id);
--dbms_output.put_line ('V_object_version_number='||V_object_version_number );
--
--Start create_transaction_Values
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_PERSON_ID' ,v_selected_person_id,null );
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_LOGIN_PERSON_ID' ,v_creator_person_id,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_EIT_TYPE','PERSON',null );------constant
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_EIT_TYPE_ID' ,v_selected_person_id,null );
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_EIT_NUMBER' ,'1',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_REVIEW_PROC_CALL','HrEit',null );------constant
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_REVIEW_ACTID' ,V_activity_id,null ); ------constant
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ACTION_1' ,'NEW_ROW',null );------constant
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_EXTRA_INFO_ID_1' ,'-1',null );------constant
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_OBJECT_VERSION_NUMBER_1' ,'0',null );------constant
--hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_PERSON_ANALYSIS_ID','0',null );
--hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_OLD_ANALYSIS_CRITERIA_ID','0',null );
--hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ID_FLEX_NUM',P_ID_FLEX_NUM,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION_TYPE_1',V_EIT_NAME,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION_CATEGORY_1',V_EIT_NAME,null );
--hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_BUSINESS_GROUP_ID',V_BUSINESS_GROUP_ID,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION1_1',P_INFORMATION1,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION2_1',P_INFORMATION2,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION3_1',P_INFORMATION3,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION4_1',P_INFORMATION4,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION5_1',P_INFORMATION5,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION6_1',P_INFORMATION6,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION7_1',P_INFORMATION7,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION8_1',P_INFORMATION8,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION9_1',P_INFORMATION9,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION10_1',P_INFORMATION10,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION11_1',P_INFORMATION11,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION12_1',P_INFORMATION12,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION13_1',P_INFORMATION13,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION14_1',P_INFORMATION14,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION15_1',P_INFORMATION15,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION16_1',P_INFORMATION16,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION17_1',P_INFORMATION17,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION18_1',P_INFORMATION18,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION19_1',P_INFORMATION19,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION20_1',P_INFORMATION20,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION21_1',P_INFORMATION21,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION22_1',P_INFORMATION22,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION23_1',P_INFORMATION23,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION24_1',P_INFORMATION24,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION25_1',P_INFORMATION25,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION26_1',P_INFORMATION26,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION27_1',P_INFORMATION27,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION28_1',P_INFORMATION28,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION29_1',P_INFORMATION29,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION30_1',P_INFORMATION30,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE_CATEGORY_1',NULL,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE1_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE2_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE3_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE4_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE5_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE6_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE7_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE8_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE9_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE10_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE11_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE12_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE13_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE14_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE15_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE16_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE17_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE18_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE19_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE20_1','',null );
--dbms_output.put_line ('create_transaction_Values=Done' );
return v_transaction_id;
end;
--
-- Procedure create the Request
--
Procedure CREATE_REQUEST ( PP_SELECTED_USER_NAME varchar2--1024
,PP_CREATOR_USER_NAME varchar2--1024
,PP_ITEM_TYPE varchar2--'HRSSA'
,PP_RESPONSIBILITY_NAME varchar2--'MOFI Absence Management';
,PP_CALLED_FROM varchar2 --'MOF_HR_LOA_CREATE_DECREE_SS'
,PP_process varchar2--'MOF_HR_EIT_LOA_CREATE_PRC'
,PP_EIT_NAME varchar2--'MOF_HR_LOA_CREATE_DECREE'
,PP_INFORMATION1 varchar2 DEFAULT NULL
,PP_INFORMATION2 varchar2 DEFAULT NULL
,PP_INFORMATION3 varchar2 DEFAULT NULL
,PP_INFORMATION4 varchar2 DEFAULT NULL
,PP_INFORMATION5 varchar2 DEFAULT NULL
,PP_INFORMATION6 varchar2 DEFAULT NULL
,PP_INFORMATION7 varchar2 DEFAULT NULL
,PP_INFORMATION8 varchar2 DEFAULT NULL
,PP_INFORMATION9 varchar2 DEFAULT NULL
,PP_INFORMATION10 varchar2 DEFAULT NULL
,PP_INFORMATION11 varchar2 DEFAULT NULL
,PP_INFORMATION12 varchar2 DEFAULT NULL
,PP_INFORMATION13 varchar2 DEFAULT NULL
,PP_INFORMATION14 varchar2 DEFAULT NULL
,PP_INFORMATION15 varchar2 DEFAULT NULL
,PP_INFORMATION16 varchar2 DEFAULT NULL
,PP_INFORMATION17 varchar2 DEFAULT NULL
,PP_INFORMATION18 varchar2 DEFAULT NULL
,PP_INFORMATION19 varchar2 DEFAULT NULL
,PP_INFORMATION20 varchar2 DEFAULT NULL
,PP_INFORMATION21 varchar2 DEFAULT NULL
,PP_INFORMATION22 varchar2 DEFAULT NULL
,PP_INFORMATION23 varchar2 DEFAULT NULL
,PP_INFORMATION24 varchar2 DEFAULT NULL
,PP_INFORMATION25 varchar2 DEFAULT NULL
,PP_INFORMATION26 varchar2 DEFAULT NULL
,PP_INFORMATION27 varchar2 DEFAULT NULL
,PP_INFORMATION28 varchar2 DEFAULT NULL
,PP_INFORMATION29 varchar2 DEFAULT NULL
,PP_INFORMATION30 varchar2 DEFAULT NULL
,p_activity_id number
--OUT
,v_msg out varchar2
)
is
v_Item_Key_id number;
v_trans_id number;
vv_output varchar2(5000);
v_responsibility_name varchar2(500);
v_app_id number;
l_user_id number;
l_resp_id number;
PP_CREATOR_PERSON_ID number;
PP_SELECTED_PERSON_ID number;
--out_msg varchar2(5000);
begin
-----------------------------------------------------------------------------------------------
--
--NITIALIZE
--
dbms_output.put_line('Initilize');
v_responsibility_name :=PP_RESPONSIBILITY_NAME;
v_app_id :=800;
select RESPONSIBILITY_ID into l_resp_id
from fnd_responsibility_tl
where RESPONSIBILITY_NAME = v_responsibility_name;
select user_id into l_user_id
from fnd_user where user_name = PP_CREATOR_USER_NAME;
fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
resp_id=>l_resp_id,
resp_appl_id=>v_app_id);
--dbms_output.put_line ('Done INITIALIZE'||out_msg);
SELECT EMPLOYEE_ID
INTO PP_CREATOR_PERSON_ID
FROM FND_USER
WHERE USER_NAME= PP_CREATOR_USER_NAME;
SELECT EMPLOYEE_ID
INTO PP_SELECTED_PERSON_ID
FROM FND_USER
WHERE USER_NAME= PP_SELECTED_USER_NAME;
--
--1----------------------------------------------------------------------------------------
--
dbms_output.put_line('Create_WorkFlow_Process');
v_Item_Key_id:= FUJ_HR_EIT_WORKFLOW_PKG.Create_WorkFlow_Process (P_CREATOR_USER_NAME =>PP_CREATOR_USER_NAME
,P_SELECTED_USER_NAME => PP_SELECTED_USER_NAME
,P_SELECTED_PERSON_ID => PP_SELECTED_PERSON_ID
,P_CREATOR_PERSON_ID =>PP_CREATOR_PERSON_ID
,P_CALLED_FROM =>PP_CALLED_FROM
,P_process =>PP_process
,p_activity_id => p_activity_id
,v_output =>vv_output
);
dbms_output.put_line ('v_Item_Key_id='||v_Item_Key_id);
if v_Item_Key_id =-5 or v_Item_Key_id is null -- Errors
then
v_msg:='Error--- Item_Key_id Not Generated'||vv_output;
dbms_output.put_line (v_msg);
--Error
else
--
--2----------------------------------------------------------------------------------------
--
dbms_output.put_line('CREATE_TRANSACTION');
v_trans_id:= FUJ_HR_EIT_WORKFLOW_PKG.CREATE_TRANSACTION (P_SELECTED_PERSON_ID =>PP_SELECTED_PERSON_ID
, P_CREATOR_PERSON_ID =>PP_CREATOR_PERSON_ID
, P_ITEM_TYPE =>PP_ITEM_TYPE
, P_ITEM_KEY =>v_Item_Key_id
, P_PROCESS_NAME =>PP_process
, P_CALLED_FROM =>PP_CALLED_FROM
,p_activity_id => p_activity_id
, P_EIT_NAME => PP_EIT_NAME
,P_INFORMATION1 =>PP_INFORMATION1
,P_INFORMATION2 =>PP_INFORMATION2
,P_INFORMATION3 =>PP_INFORMATION3
,P_INFORMATION4 =>PP_INFORMATION4
,P_INFORMATION5 =>PP_INFORMATION5
,P_INFORMATION6 =>PP_INFORMATION6
,P_INFORMATION7 =>PP_INFORMATION7
,P_INFORMATION8 =>PP_INFORMATION8
,P_INFORMATION9 =>PP_INFORMATION9
,P_INFORMATION10 =>PP_INFORMATION10
,P_INFORMATION11 =>PP_INFORMATION11
,P_INFORMATION12 =>PP_INFORMATION12
,P_INFORMATION13 =>PP_INFORMATION13
,P_INFORMATION14 =>PP_INFORMATION14
,P_INFORMATION15 =>PP_INFORMATION15
,P_INFORMATION16 =>PP_INFORMATION16
,P_INFORMATION17 =>PP_INFORMATION17
,P_INFORMATION18 =>PP_INFORMATION18
,P_INFORMATION19 =>PP_INFORMATION19
,P_INFORMATION20 =>PP_INFORMATION20
,P_INFORMATION21 =>PP_INFORMATION21
,P_INFORMATION22 =>PP_INFORMATION22
,P_INFORMATION23 =>PP_INFORMATION23
,P_INFORMATION24 => PP_INFORMATION24
,P_INFORMATION25 => PP_INFORMATION25
,P_INFORMATION26 => PP_INFORMATION26
,P_INFORMATION27 => PP_INFORMATION27
,P_INFORMATION28 => PP_INFORMATION28
,P_INFORMATION29 => PP_INFORMATION29
,P_INFORMATION30 => PP_INFORMATION30
) ;
dbms_output.put_line ('v_trans_id='||v_trans_id);
--
--3----------------------------------------------------------------------------------------
--
if v_trans_id is null
then
v_msg:='Error--- trans_id Not Generated';
dbms_output.put_line (v_msg);
--Error
else
--
--4----------------------------------------------------------------------------------------
--
dbms_output.put_line('Update_WorkFlow_Attributes');
FUJ_HR_EIT_WORKFLOW_PKG.Update_WorkFlow_Attributes ( P_itemtype =>PP_ITEM_TYPE
, P_itemkey =>v_Item_Key_id
, P_TRANSACTION_ID=>v_trans_id
);
--commit;
--
--5----------------------------------------------------------------------------------------
--
FUJ_HR_EIT_WORKFLOW_PKG.Start_WorkFlow_Process (P_itemtype =>PP_ITEM_TYPE
, P_itemkey =>v_Item_Key_id);
--commit;
--
--6----------------------------------------------------------------------------------------
--important to start Approval cycle
FUJ_HR_EIT_WORKFLOW_PKG.Start_Approval_Process (P_itemtype =>PP_ITEM_TYPE
, P_itemkey =>v_Item_Key_id , p_activity_id => p_activity_id);
v_msg:='Done';
end if;
null;
End if;
end;
--procedure to approve and reject both
PROCEDURE approve_reject_p (pAction IN VARCHAR2, --APPROVED/REJECTED
pComments IN VARCHAR2, -- User Comments -- Optional
pNotification_id IN NUMBER, -- Notification ID
pStatus OUT VARCHAR2,
pMessage OUT VARCHAR2
)
IS
l_user_name VARCHAR2(250);
BEGIN
BEGIN
SELECT RECIPIENT_ROLE
INTO l_user_name
FROM WF_NOTIFICATIONS
WHERE notification_id = pNotification_id;
EXCEPTION
WHEN OTHERS THEN
l_user_name := NULL;
END;
wf_notification.SETATTRTEXT(pNotification_id,'RESULT',pAction);
wf_notification.Respond(pNotification_id,pComments,l_user_name,pAction);
pStatus := 'S';
pMessage := 'Successfully '||initcap(pAction);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
pStatus := 'E';
pMessage := 'Unexpected error while Approve/Reject the notification: '||SQLERRM;
END approve_reject_p;
-- Procedure only to Approve By notificaion ID
--
procedure Approve_Request(p_Not_ID Number
,p_responder varchar2
)
--return Varchar2
as
l_lookup_code varchar2(80) := 'APPROVED';
v_msg varchar2(5000) :='Done';
begin
begin
wf_notification.SetAttrText (p_Not_ID , 'RESULT', l_lookup_code);
wf_notification.Respond (p_Not_ID ,p_responder,p_responder);
v_msg :='Done';
commit;
exception
when others
then
v_msg:='Error---'||SQLCODE||' = '||SQLERRM;
end;
-- return v_msg;
End;
--
--
-- Function to Approve By notificaion ID
--
Function Approve_Request(p_Not_ID Number
,p_responder varchar2
)
return Varchar2
is
l_lookup_code varchar2(80) := 'APPROVED';
v_msg varchar2(5000) :='Done';
begin
begin
wf_notification.SetAttrText (p_Not_ID , 'RESULT', l_lookup_code);
wf_notification.Respond (p_Not_ID ,p_responder,p_responder);
v_msg :='Done';
commit;
exception
when others
then
v_msg:='Error---'||SQLCODE||' = '||SQLERRM;
end;
return v_msg;
End;
--
-- Function to Reject By notificaion ID
--
Function Reject_Request(p_Not_ID Number
,p_responder varchar2
)
return Varchar2
is
l_lookup_code varchar2(80) := 'REJECTED';
v_msg varchar2(5000) :='Done';
begin
begin
wf_notification.SetAttrText (p_Not_ID , 'RESULT', l_lookup_code);
wf_notification.Respond (p_Not_ID ,p_responder,p_responder);
v_msg :='Done';
commit;
exception
when others
then
v_msg:='Error---'||SQLCODE||' = '||SQLERRM;
end;
return v_msg;
End;
--
-- Function to Close (FYI) Notifications By notificaion ID
--
Function Close_FYI_Notification (p_Not_ID Number
,p_responder varchar2
)
return Varchar2
is
v_msg varchar2(5000) :='Done';
begin
begin
-- wf_notification.Close (p_Not_ID ,p_responder);
wf_notification.Respond (p_Not_ID ,p_responder,p_responder);
v_msg :='Done';
commit;
exception
when others
then
v_msg:='Error---'||SQLCODE||' = '||SQLERRM;
end;
return v_msg;
End;
end;
/
------------------------------------------Package ends here ---------------------------------
2.
--Block to run the above package
-
---------------------------
Declare
P_msg varchar2(5000);
begin
begin
FUJ_HR_EIT_WORKFLOW_PKG.CREATE_REQUEST ( PP_CREATOR_USER_NAME =>'EG_A.ALNUIMI'
,PP_SELECTED_USER_NAME =>'EG_AFZAL'
--,PP_CREATOR_PERSON_ID =>V_EMPLOYEE_ID
,PP_ITEM_TYPE =>'HRSSA'
,PP_RESPONSIBILITY_NAME =>'Employee Self Service Fujairah'
,PP_CALLED_FROM =>'HR_EIT_VS_SS' --SELECT text_value FROM WF_ITEM_ATTRIBUTE_values where item_type = 'HRSSA' and item_key in ('70482') and name = 'HR_PERZ_FUNCTION_NAME_ATTR'--applied from ebs
,PP_process =>'HR_EIT_VEN_PRC' --SELECT PROCESS_NAME FROM HR_API_TRANSACTIONS WHERE TRANSACTION_ID in ( 252710) applied from ebs
,PP_EIT_NAME =>'FUJ_VISA_ENTITLEMENTS' --SELECT VARCHAR2_VALUE FROM HR_API_TRANSACTION_VALUES WHERE TRANSACTION_STEP_ID = 269097 AND NAME = 'P_INFORMATION_CATEGORY_1' --applied from ebs
,p_activity_id => '347867' --SELECT ACTIVITY_ID FROM HR_API_TRANSACTION_STEPS WHERE TRANSACTION_ID IN(252710) applied from ebs --OUT
,PP_INFORMATION1 =>'Renewal'
,PP_INFORMATION2 =>'0'
,PP_INFORMATION5 => 'TEST FROM API'
,v_msg =>P_msg
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN P_msg :='Error103=No Employee Assigned Or UserName Not Found';
DBMS_OUTPUT.PUT_LINE(SQLERRM);
End;
dbms_output.put_line('P_msg='||P_msg);
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
-------------------------------------------------------------
3. Approve_Reject_Proc optional for testing
create or replace PROCEDURE approve_reject_proc (pAction IN VARCHAR2, --APPROVE/REJECT
pComments IN VARCHAR2, -- User Comments -- Optional
pNotification_id IN NUMBER, -- Notification ID
pStatus OUT VARCHAR2,
pMessage OUT VARCHAR2
)
IS
l_user_name VARCHAR2(250);
BEGIN
BEGIN
SELECT RECIPIENT_ROLE
INTO l_user_name
FROM WF_NOTIFICATIONS
WHERE notification_id = pNotification_id;
EXCEPTION
WHEN OTHERS THEN
l_user_name := NULL;
END;
wf_notification.SETATTRTEXT(pNotification_id,'RESULT',pAction);
wf_notification.Respond(pNotification_id,
pComments,
l_user_name,
pAction);
pStatus := 'S';
pMessage := 'Successfully '||initcap(pAction);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
pStatus := 'E';
pMessage := 'Unexpected error while Approve/Reject the notification: '||SQLERRM;
END approve_reject_proc;
--RUN BELOW SCRIPT IN separate new session
DECLARE
Pstatus_out varchar2(200);
PMessage_out Varchar2(200);
l_notification_id number := :notification_id;--get from the below query
l_approve_reject varchar2(200) := 'REJECTED'; --APPROVED OR REJECTED
BEGIN
approve_reject_proc(l_approve_reject,'Test Comments',l_notification_id,Pstatus_out, PMessage_out);
dbms_output.put_line('Pstatus ->'||Pstatus_out|| '-PMessage->'||PMessage_out);
exception when others then
dbms_output.put_line(sqlerrm);
END;
--to get notification id for approval
SELECT rownum l_sequence ,a.l_notification_id ,a.l_role role ,a.l_action action ,a.l_comments ,to_char(a.l_date,'dd-mm-yyyy hh:mi:ss am') l_date FROM (
SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname
(nvl (wn.more_info_role ,wn.recipient_role)) l_role ,
decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING')) ,
decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action ,
na.text_value l_comments ,ias.end_date l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
,wf_item_activity_statuses ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
UNION
SELECT pah.notification_id l_notification_id ,wf_directory.getroledisplayname (pah.user_name) l_role
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action)) l_action ,pah.user_comment l_comments
,pah.last_update_date l_date FROM pqh_ss_approval_history pah WHERE pah.transaction_item_type = :p_item_type
AND pah.transaction_item_key = :p_item_key AND pah.action NOT IN ('APPROVED','REJECTED','SUBMIT' ,'RFC','RESUBMIT','TIMEOUT','QUESTION','ANSWER')
UNION
SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (wn.recipient_role) l_role
,decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING'))
,decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action
,decode (wn.status ,'CANCELED' ,NULL ,nvl (na.text_value ,
( SELECT user_comment FROM wf_comments WHERE notification_id = wn.notification_id AND action = l.lookup_code AND rownum = 1 ))) l_comments
,nvl (ias.end_date ,ias.begin_date) l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
,wf_item_activity_statuses_h ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
UNION
SELECT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (nvl (wn.more_info_role ,wn.recipient_role)) l_role
,decode (wn.status ,'CANCELED' ,decode (pah.action ,'TIMEOUT' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN'))) ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))) l_action
,decode (pah.notification_id ,wn.notification_id ,pah.user_comment ,NULL) l_comments ,pah.last_update_date l_date
FROM pqh_ss_approval_history pah ,wf_notifications wn
WHERE pah.transaction_item_type = :p_item_type AND pah.transaction_item_key = :p_item_key
AND pah.action IN ('TIMEOUT','RESUBMIT') AND wn.group_id = ( SELECT group_id FROM wf_notifications WHERE notification_id = pah.notification_id
AND rownum = 1 )
UNION select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role
-- ,WF_CORE.TRANSLATE(C.ACTION) l_action
,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES_H IAS, WF_COMMENTS C, wf_notifications wn
where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id
and wn.notification_id = c.notification_id and C.ACTION in('QUESTION', 'ANSWER')
UNION select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role -- ,WF_CORE.TRANSLATE(C.ACTION) l_action
,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES IAS, WF_COMMENTS C, wf_notifications wn
where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id and wn.notification_id = c.notification_id
and C.ACTION in('QUESTION', 'ANSWER')
UNION SELECT 0 ,wf_directory.getroledisplayname (owner_role) l_role ,wf_core.translate ('SUBMIT')
,appr.text_value note ,begin_date l_date
FROM wf_items i ,wf_item_attribute_values appr
WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND i.item_type = appr.item_type (+) AND i.item_key = appr.item_key (+)
AND appr.name (+) = 'SUBMIT_COMMENTS' ORDER BY l_notification_id ,l_date ) a
4. Useful tables and queries..
SELECT * FROM HR_API_TRANSACTIONS
WHERE TRANSACTION_ID in ( 252719,252725)
--where item_key in ('70480','70486')
--WHERE CREATION_DATE LIKE SYSDATE
SELECT * FROM HR_API_TRANSACTION_STEPS WHERE TRANSACTION_ID IN( 252719,252725)
SELECT VARCHAR2_VALUE FROM HR_API_TRANSACTION_VALUES WHERE TRANSACTION_STEP_ID = 269097 AND NAME = 'P_INFORMATION_CATEGORY_1'
and (varchar2_value is not null or number_value is not null)
order by 1
SELECT * FROM WF_ITEM_ATTRIBUTE_values where item_type = 'HRSSA' and item_key in ('70494')
and name = 'PROCESS_ORDER_STRING'
5. Errors and Solutions.
Step1: Apply from front end and check all the approvers generated and working fine.
check the tables hr_api_transaction, hr_api_transaction_steps, hr_api_transaction_values, WF_ITEM_ATTRIBUTE_values
Step2: Create the package specification.
Step3: modify the package body as below and create it
3.1 wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PERZ_ORGANIZATION_ID_ATTR','81'); --mandatory change 1 business group
3.2 wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_ORDER_STRING','%345294%1%345284%2%345279%3%'); --mandatory change 2, SELECT text_value FROM WF_ITEM_ATTRIBUTE_values where item_type = 'HRSSA' and item_key in ('70482') and name = 'PROCESS_ORDER_STRING' applied from ebs
3.3 V_BUSINESS_GROUP_ID := 81; --mandatory change 3 business group
Step4: Activity_id can be find from hr_api_transaction_step pass ur transaction_id applied from ebs
Errors and Solutions:
1. Approvers r not initiated or stopping at EITV4.0 without result
Solution: missing to call pqh_ss_workflow.start_approval_wf chk the package.
2. Error:You do not have access to the selected person record. Please contact your Human Resources Adminstrator.
above error appears for the approver while opening the notification.
Solution: Check all the attributes r assinged or not, i resolved it by finding from backend missing below attributes.
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FYI_NTF_DETAILS','Y');
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PERZ_ORGANIZATION_ID_ATTR','81'); --mandatory change 1 business group
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_ORDER_STRING','%345294%1%345284%2%345279%3%'); --mandatory change 2, chk from any txn applied from front end from table WF_ITEM_ATTRIBUTE_values
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'SAVE_ATTACHMENT','Y');
wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_AME_APP_ID_ATTR',800);
3. Error after final approval: the changes were not applied because The Person ID that you have entered does not exist.
One transaction applied from api and approved from ebs final commit is fine, but if approval is done using procedure from backend then commit error.
solution:
chk the query
SELECT * FROM WF_ITEM_ATTRIBUTE_values
where item_type = 'HRSSA'
and item_key in ('70478','70480')--'one applied from ebs approved from ebs, the other applied from api approved from procedure.
and (text_value is not null or number_value is not null)
AND NAME = 'HR_USER_ID_ATTR'
order by 3
Solution: I was getting value of user initiated in toad, so i used new session and approved using procedure, then the value store in above attribute is '-1', commit worked fine.
is
--
-- Procedure create the Request
--
Procedure CREATE_REQUEST ( PP_SELECTED_USER_NAME varchar2--1024
,PP_CREATOR_USER_NAME varchar2
,PP_ITEM_TYPE varchar2--'HRSSA'
,PP_RESPONSIBILITY_NAME varchar2--'MOFI Absence Management';
,PP_CALLED_FROM varchar2 --'MOF_HR_LOA_CREATE_DECREE_SS'
,PP_process varchar2--'MOF_HR_EIT_LOA_CREATE_PRC'
,PP_EIT_NAME varchar2--'MOF_HR_LOA_CREATE_DECREE'
,PP_INFORMATION1 varchar2 DEFAULT NULL
,PP_INFORMATION2 varchar2 DEFAULT NULL
,PP_INFORMATION3 varchar2 DEFAULT NULL
,PP_INFORMATION4 varchar2 DEFAULT NULL
,PP_INFORMATION5 varchar2 DEFAULT NULL
,PP_INFORMATION6 varchar2 DEFAULT NULL
,PP_INFORMATION7 varchar2 DEFAULT NULL
,PP_INFORMATION8 varchar2 DEFAULT NULL
,PP_INFORMATION9 varchar2 DEFAULT NULL
,PP_INFORMATION10 varchar2 DEFAULT NULL
,PP_INFORMATION11 varchar2 DEFAULT NULL
,PP_INFORMATION12 varchar2 DEFAULT NULL
,PP_INFORMATION13 varchar2 DEFAULT NULL
,PP_INFORMATION14 varchar2 DEFAULT NULL
,PP_INFORMATION15 varchar2 DEFAULT NULL
,PP_INFORMATION16 varchar2 DEFAULT NULL
,PP_INFORMATION17 varchar2 DEFAULT NULL
,PP_INFORMATION18 varchar2 DEFAULT NULL
,PP_INFORMATION19 varchar2 DEFAULT NULL
,PP_INFORMATION20 varchar2 DEFAULT NULL
,PP_INFORMATION21 varchar2 DEFAULT NULL
,PP_INFORMATION22 varchar2 DEFAULT NULL
,PP_INFORMATION23 varchar2 DEFAULT NULL
,PP_INFORMATION24 varchar2 DEFAULT NULL
,PP_INFORMATION25 varchar2 DEFAULT NULL
,PP_INFORMATION26 varchar2 DEFAULT NULL
,PP_INFORMATION27 varchar2 DEFAULT NULL
,PP_INFORMATION28 varchar2 DEFAULT NULL
,PP_INFORMATION29 varchar2 DEFAULT NULL
,PP_INFORMATION30 varchar2 DEFAULT NULL
,p_activity_id number
--OUT
,v_msg out varchar2
);
PROCEDURE approve_reject_p (pAction IN VARCHAR2, --APPROVED/REJECTED
pComments IN VARCHAR2, -- User Comments -- Optional
pNotification_id IN NUMBER, -- Notification ID
pStatus OUT VARCHAR2,
pMessage OUT VARCHAR2
);
--
-- Function to Approve By notificaion ID
--
Function Approve_Request(p_Not_ID Number
,p_responder varchar2
)
return Varchar2;
--
-- Function to Reject By notificaion ID
--
Function Reject_Request(p_Not_ID Number
,p_responder varchar2
)
return Varchar2;
--
-- Function to Close (FYI) Notifications By notificaion ID
--
Function Close_FYI_Notification (p_Not_ID Number
,p_responder varchar2
)
return Varchar2;
procedure Approve_Request(p_Not_ID Number
,p_responder varchar2
);
end;
/
CREATE OR REPLACE Package body APPS.FUJ_HR_EIT_WORKFLOW_PKG
is
--
--Function Used to Create Work Flow and return with Item Key
--
function Create_WorkFlow_Process (P_SELECTED_PERSON_ID number
,P_CREATOR_PERSON_ID NUMBER
,P_SELECTED_USER_NAME VARCHAR2
,P_CREATOR_USER_NAME VARCHAR2
,P_CALLED_FROM Varchar2
,P_process Varchar2
,p_activity_id number
,V_output Out Varchar2
)
return number
is
v_itemtype varchar2(500);
v_itemkey varchar2(500);
v_process varchar2(500);
V_PROCESS_DISPLAY_NAME varchar2(500);
V_CALLED_FROM varchar2(500);
v_user_key varchar2(500);
v_owner_role varchar2(500);
v_USER_NAME varchar2(500);
V_CURRENT_PERSON_ID number;
V_ASSIGNMENT_ID number;
V_CURRENT_PERSON_DISPLAY_NAME varchar2(500);
out_msg varchar2(1500);
V_activity_id number;
v_p_responsibility_name varchar2(1500);
v_app_id number;
V_CREATOR_PERSON_DISPLAY_NAME VARCHAR2(2000);
Exc_Error Exception;
begin
dbms_output.put_line('loc 1');
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--
--Variables
--
--V_USER_NAME :=P_USER_NAME;
V_CURRENT_PERSON_ID :=P_CREATOR_PERSON_ID;
V_CALLED_FROM :=P_CALLED_FROM;--'LSG_HR_LOA_CREATE_DECREE_SS';
v_process :=P_process; --'MOF_HR_EIT_LOA_CREATE_PRC';
--------------------------------------------------------------------------------------------
--Get Person_id
Begin
select p.FIRST_NAME||' '||p.LAST_NAME NAME
into V_CURRENT_PERSON_DISPLAY_NAME
from per_all_people_f p
where 1=1
and trunc(sysdate) between p.EFFECTIVE_START_DATE and p.EFFECTIVE_END_DATE
and p.person_id=P_SELECTED_PERSON_ID
;
dbms_output.put_line ('V_CURRENT_PERSON_DISPLAY_NAME='||V_CURRENT_PERSON_DISPLAY_NAME);
Exception
when no_data_found
then
v_output :='Invalide User Name or Not Found';
raise Exc_Error;
End;
Begin
select p.FIRST_NAME||' '||p.LAST_NAME NAME
into V_CREATOR_PERSON_DISPLAY_NAME
from per_all_people_f p
where 1=1
and trunc(sysdate) between p.EFFECTIVE_START_DATE and p.EFFECTIVE_END_DATE
and p.person_id=P_CREATOR_PERSON_ID
;
dbms_output.put_line ('V_CREATOR_PERSON_DISPLAY_NAME='||V_CURRENT_PERSON_DISPLAY_NAME);
Exception
when no_data_found
then
v_output :='Invalide User Name or Not Found';
raise Exc_Error;
End;
--get assignment_id
Begin
select distinct p.assignment_id
into V_ASSIGNMENT_ID
from per_all_assignments_f p
where 1=1
and trunc(sysdate) between p.EFFECTIVE_START_DATE and p.EFFECTIVE_END_DATE
and p.person_id=P_SELECTED_PERSON_ID
and primary_flag ='Y'
;
dbms_output.put_line ('V_assignment_id ='||V_ASSIGNMENT_ID);
Exception
when no_data_found
then
v_output :='Invalide assignment_id';
raise Exc_Error;
End;
--
--Work Flow variables
--
-- V_activity_id :=184884;
V_activity_id := p_activity_id;--339
v_user_key :=null;
v_owner_role :=P_CREATOR_USER_NAME;
-------
Begin
select distinct ITEM_TYPE ,DISPLAY_NAME --Should be Handeled 06092015 --[Error] ORA-01422: exact fetch returns more than requested number of rows
into v_itemtype ,V_PROCESS_DISPLAY_NAME
from wf_activities_tl
where 1=1
and name=P_process
and LANGUAGE='US';
Exception
when no_data_found
then
v_output :='Invalide Process Name or Not Found';
raise Exc_Error;
End;
dbms_output.put_line('loc2');
-------
select hr_workflow_item_key_s.nextval
into v_itemkey
from dual;
/*
---------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--
--NITIALIZE
--
v_p_responsibility_name :='MOFI Absence Management';
v_app_id :=800;
MOF_Sec_init_user ( p_user_name =>V_USER_NAME
,p_responsibility_name =>v_p_responsibility_name
,p_app_id =>v_app_id
,o_msg =>out_msg
);
--dbms_output.put_line ('Done INITIALIZE'||out_msg);
*/
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--
--CreateProcess
--
dbms_output.put_line('loc 3');
wf_engine.CreateProcess( ITEMTYPE =>v_itemtype
, ITEMKEY =>v_itemkey
, PROCESS =>v_process
, USER_KEY =>v_user_key
, OWNER_ROLE =>v_owner_role
);
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--
--SetItemAttr
--
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'USER_NAME',P_CREATOR_USER_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_DISPLAY_NAME',V_PROCESS_DISPLAY_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_NAME',v_process);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_ID',P_SELECTED_PERSON_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_USERNAME',P_SELECTED_USER_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_DISPLAY_NAME',V_CURRENT_PERSON_DISPLAY_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_ASSIGNMENT_ID',V_ASSIGNMENT_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_EFFECTIVE_DATE',sysdate);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_ID',P_CREATOR_PERSON_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_USERNAME',P_CREATOR_USER_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_DISPLAY_NAME',V_CREATOR_PERSON_DISPLAY_NAME );
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'P_ASSIGNMENT_ID',V_ASSIGNMENT_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_AME_TRAN_TYPE_ATTR','SSHRMS');
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'P_CALLED_FROM',V_CALLED_FROM);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'P_EFFECTIVE_DATE', TO_CHAR(SYSDATE,'YYYY-MM-DD'));
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_FIRST_ACTIVITY_ID',V_activity_id);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'SESSION_ID',fnd_global.session_id);--??
dbms_output.put_line('loc4');
wf_engine.AddItemAttr (v_itemtype, v_itemkey,'TRANSACTION_PRIVILEGE','PRIVATE',null,null);
wf_engine.AddItemAttr (v_itemtype,v_itemkey ,'HR_REVIEW_HEADER_STYLE','PER_HEADER',null,null);
--Important to open page from EBS
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'APPROVAL_GENERIC_URL','JSP:/OA_HTML/OA.jsp?akRegionCode=HR_REVIEW_TOP_SS&'||'akRegionApplicationId=800&'||'NtfId=-&'||'#NID-&'||'retainAM=Y&'||'OAFunc='||V_CALLED_FROM);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_GENERIC_URL_REDIRECT','OA.jsp?akRegionCode=HR_REVIEW_TOP_SS&'||'akRegionApplicationId=800&'||'OAFunc='||V_CALLED_FROM);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PERZ_FUNCTION_NAME_ATTR',V_CALLED_FROM);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PERZ_LOCALIZATION_CODE_ATTR','AE');
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_REVIEW_TEMPLATE_RN_ATTR','HR_REVIEWRN_NTF');
--dbms_output.put_line ('Done SetItemAttr');
--added by afzal from here and resolved error Error:You do not have access to the selected person's record. Please contact your Human Resources Adminstrator.
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FYI_NTF_DETAILS','Y');
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PERZ_ORGANIZATION_ID_ATTR','81'); --mandatory change 1 business group
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_ORDER_STRING','%347867%1%347874%2%347865%3%'); --mandatory change 2, chk from any txn applied from front end from table WF_ITEM_ATTRIBUTE_values
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'SAVE_ATTACHMENT','Y');
wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_AME_APP_ID_ATTR',800);
dbms_output.put_line('loc5');
return v_itemkey;
Exception
when Exc_Error
then
v_itemkey :=-5;
return v_itemkey;
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
--HR_APPROVAL_PRC_VERSION V5
--PROCESS_ORDER_STRING %184884%1%
--TRAN_SUBMIT W
end;
--
--Procedure to Update WorkFlow Process Attributes
--Should Run After Create transactions and before start process Aprovals
Procedure Update_WorkFlow_Attributes (P_itemtype varchar2
, P_itemkey varchar2
, P_TRANSACTION_ID Number
)
is
begin
wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'TRAN_SUBMIT','Y');
wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'TRANSACTION_ID',P_TRANSACTION_ID);
wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'HR_RUNTIME_APPROVAL_REQ_FLAG','YES_DYNAMIC');
wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'HR_NTF_ATTACHMENTS_ATTR','FND:entity=PQH_SS_ATTACHMENT&'||'pk1name=TransactionId&'||'pk1value='||P_TRANSACTION_ID);
end;
--
--Procedure to Start WorkFlow Process
--
Procedure Start_WorkFlow_Process (P_itemtype varchar2
, P_itemkey varchar2
)
is
begin
wf_engine.StartProcess(itemtype => P_itemtype
, itemkey => P_itemkey
);
--commit;
end;
--
--Procedure to Start Approval Process
--
Procedure Start_Approval_Process (P_itemtype varchar2
, P_itemkey varchar2
,p_activity_id number
)
is
v_result varchar2(5000);
begin
pqh_ss_workflow.start_approval_wf ( itemtype =>P_itemtype,
itemkey =>P_itemkey,
--actid =>'184884',
actid => p_activity_id,--339
funcmode =>'RUN',
result =>v_result );
dbms_output.put_line ('v_result='||v_result);
end;
--
FUNCTION CREATE_TRANSACTION ( P_SELECTED_PERSON_ID NUMBER
,P_CREATOR_PERSON_ID NUMBER
, P_ITEM_TYPE VARCHAR2
, P_ITEM_KEY VARCHAR2
, P_PROCESS_NAME VARCHAR2
, P_CALLED_FROM VARCHAR2
, P_EIT_NAME VARCHAR2
, P_ACTIVITY_ID NUMBER
,P_INFORMATION1 varchar2 DEFAULT NULL
,P_INFORMATION2 varchar2 DEFAULT NULL
,P_INFORMATION3 varchar2 DEFAULT NULL
,P_INFORMATION4 varchar2 DEFAULT NULL
,P_INFORMATION5 varchar2 DEFAULT NULL
,P_INFORMATION6 varchar2 DEFAULT NULL
,P_INFORMATION7 varchar2 DEFAULT NULL
,P_INFORMATION8 varchar2 DEFAULT NULL
,P_INFORMATION9 varchar2 DEFAULT NULL
,P_INFORMATION10 varchar2 DEFAULT NULL
,P_INFORMATION11 varchar2 DEFAULT NULL
,P_INFORMATION12 varchar2 DEFAULT NULL
,P_INFORMATION13 varchar2 DEFAULT NULL
,P_INFORMATION14 varchar2 DEFAULT NULL
,P_INFORMATION15 varchar2 DEFAULT NULL
,P_INFORMATION16 varchar2 DEFAULT NULL
,P_INFORMATION17 varchar2 DEFAULT NULL
,P_INFORMATION18 varchar2 DEFAULT NULL
,P_INFORMATION19 varchar2 DEFAULT NULL
,P_INFORMATION20 varchar2 DEFAULT NULL
,P_INFORMATION21 varchar2 DEFAULT NULL
,P_INFORMATION22 varchar2 DEFAULT NULL
,P_INFORMATION23 varchar2 DEFAULT NULL
,P_INFORMATION24 varchar2 DEFAULT NULL
,P_INFORMATION25 varchar2 DEFAULT NULL
,P_INFORMATION26 varchar2 DEFAULT NULL
,P_INFORMATION27 varchar2 DEFAULT NULL
,P_INFORMATION28 varchar2 DEFAULT NULL
,P_INFORMATION29 varchar2 DEFAULT NULL
,P_INFORMATION30 varchar2 DEFAULT NULL
)
RETURN NUMBER
is
v_validate boolean default false ;
v_creator_person_id number;
v_transaction_privilege varchar2(500);
v_product_code varchar2(500) default null;
v_url varchar2(500) default null;
v_status varchar2(500) default null;
v_section_display_name varchar2(500) default null;
v_function_id number;
v_transaction_ref_table varchar2(500) default null;
v_transaction_ref_id number default null;
v_transaction_type varchar2(500) default null;
v_assignment_id number default null;
v_api_addtnl_info varchar2(500) default null;
v_selected_person_id number default null;
v_item_type varchar2(500) default null;
v_item_key varchar2(500) default null;
v_transaction_effective_date date default null;
v_process_name varchar2(500) default null;
v_plan_id number default null;
v_rptg_grp_id number default null;
v_effective_date_option varchar2(500) default null;
V_activity_id number;
V_ACTIVE_ROW_ID number;
v_api_name varchar2(500);
V_EIT_NAME varchar2(500);
v_dummy varchar2(500) default null;
--------OUT---------
v_transaction_id number ;
V_transaction_step_id number ;
V_object_version_number number ;
P_ID_FLEX_NUM NUMBER;
V_BUSINESS_GROUP_ID NUMBER;
ln_analysis_criteria_id number;
V_EIT_FULL_NAME VARCHAR2(200);
Begin
--
--Variables
--
--constants-----------------------------------------------------
v_validate :=FALSE;
V_BUSINESS_GROUP_ID := 81; --mandatory change 3
--P_ID_FLEX_NUM := 50455;
v_transaction_privilege :='PRIVATE';
v_status :='W';--?? --Y
v_transaction_ref_table :='HR_API_TRANSACTIONS';
v_transaction_ref_id :=null;-------------------------
v_transaction_type :='WF';
v_product_code :=null;
v_url :=null;
v_section_display_name :=null;
v_api_addtnl_info :=null;
v_plan_id :=null;
v_rptg_grp_id :=null;
v_effective_date_option :=null;
--Variables-----------------------------------------------------
v_item_type :=P_item_type;--'HRSSA';
v_item_key :=P_item_key ;--217929;--**
v_process_name :=P_process_name;--
v_creator_person_id :=P_CREATOR_PERSON_ID;
--v_assignment_id := 22432; --
v_selected_person_id :=P_SELECTED_PERSON_ID;
v_transaction_effective_date :=sysdate;
--Step
V_activity_id := p_activity_id;
--V_ACTIVE_ROW_ID :='-987123654';
v_api_name :='HR_PROCESS_EIT_SS.PROCESS_API';--constant
V_EIT_NAME := P_EIT_NAME;--
v_dummy :=TO_CHAR(SYSDATE,'yyyymmddhh24miss')||P_CREATOR_PERSON_ID;
--
--Get Function_id
--
select FUNCTION_ID
into v_function_id
from FND_FORM_FUNCTIONS
where 1=1
and FUNCTION_NAME=P_CALLED_FROM;
/*
SELECT DISTINCT NVL(DESCRIPTION,DESCRIPTIVE_FLEX_CONTEXT_NAME)--, ID_FLEX_NUM
INTO V_EIT_FULL_NAME--, P_ID_FLEX_NUM
FROM FND_DESCR_FLEX_CONTEXTS_VL
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = V_EIT_NAME
AND APPLICATION_ID = 800
AND DESCRIPTIVE_FLEXFIELD_NAME = 'Extra Person Info DDF'
--AND ID_FLEX_CODE = 'PEA'
;
*/
Begin
select distinct p.assignment_id
into V_ASSIGNMENT_ID
from per_all_assignments_f p
where 1=1
and trunc(sysdate) between p.EFFECTIVE_START_DATE and p.EFFECTIVE_END_DATE
and p.person_id = P_SELECTED_PERSON_ID
and primary_flag ='Y'
;
dbms_output.put_line ('V_assignment_id ='||V_ASSIGNMENT_ID);
Exception
when no_data_found
then
dbms_output.put_line ('Error ='||sqlerrm);
End;
--
--Start create_transaction-----------------------------------------------------
--
hr_transaction_api.create_transaction
(p_validate =>v_validate
,p_creator_person_id =>v_creator_person_id
,p_transaction_privilege =>v_transaction_privilege
,p_product_code =>v_product_code
,p_url =>v_url
,p_status =>v_status
,p_section_display_name =>v_section_display_name
,p_function_id =>v_function_id
,p_transaction_ref_table =>v_transaction_ref_table
,p_transaction_ref_id =>v_transaction_ref_id
,p_transaction_type =>v_transaction_type
,p_assignment_id =>v_assignment_id
,p_api_addtnl_info =>v_api_addtnl_info
,p_selected_person_id =>v_selected_person_id
,p_item_type =>v_item_type
,p_item_key =>v_item_key
,p_transaction_effective_date => trunc(v_transaction_effective_date)
,p_process_name =>v_process_name
,p_plan_id =>v_plan_id
,p_rptg_grp_id =>v_rptg_grp_id
,p_effective_date_option =>v_effective_date_option
--Out
,p_transaction_id =>v_transaction_id
) ;
dbms_output.put_line ('v_transaction_id='||v_transaction_id);
--
--Start create_transaction_Step
--
hr_transaction_api.create_transaction_step
(p_validate =>v_validate
,p_creator_person_id =>v_creator_person_id
,p_transaction_id =>v_transaction_id
,p_api_name => v_api_name
,p_api_display_name =>null
,p_item_type =>v_item_type
,p_item_key =>v_item_key
,p_activity_id =>V_activity_id
--Out
,p_transaction_step_id => V_transaction_step_id
,p_object_version_number => V_object_version_number
);
/*
--Inserting Record in Per anslysis criteria table
--
select PER_ANALYSIS_CRITERIA_S.nextval
into ln_analysis_criteria_id
from dual;
ln_analysis_criteria_id := ln_analysis_criteria_id;
insert into per_analysis_criteria
(analysis_criteria_id,id_flex_num,summary_flag,enabled_flag,last_update_date,last_updated_by,last_update_login,created_by,creation_date
,segment1,segment10,segment11,segment12,segment13,segment3)
values
(ln_analysis_criteria_id,P_ID_FLEX_NUM,'N','Y',sysdate,-1,-1,-1,sysdate
,P_segment1 ,P_segment10,P_segment11,P_segment12,P_segment13,P_segment3
);
*/
dbms_output.put_line ('V_transaction_step_id='||V_transaction_step_id);
--dbms_output.put_line ('V_object_version_number='||V_object_version_number );
--
--Start create_transaction_Values
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_PERSON_ID' ,v_selected_person_id,null );
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_LOGIN_PERSON_ID' ,v_creator_person_id,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_EIT_TYPE','PERSON',null );------constant
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_EIT_TYPE_ID' ,v_selected_person_id,null );
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_EIT_NUMBER' ,'1',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_REVIEW_PROC_CALL','HrEit',null );------constant
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_REVIEW_ACTID' ,V_activity_id,null ); ------constant
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ACTION_1' ,'NEW_ROW',null );------constant
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_EXTRA_INFO_ID_1' ,'-1',null );------constant
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_OBJECT_VERSION_NUMBER_1' ,'0',null );------constant
--hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_PERSON_ANALYSIS_ID','0',null );
--hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_OLD_ANALYSIS_CRITERIA_ID','0',null );
--hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ID_FLEX_NUM',P_ID_FLEX_NUM,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION_TYPE_1',V_EIT_NAME,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION_CATEGORY_1',V_EIT_NAME,null );
--hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_BUSINESS_GROUP_ID',V_BUSINESS_GROUP_ID,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION1_1',P_INFORMATION1,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION2_1',P_INFORMATION2,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION3_1',P_INFORMATION3,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION4_1',P_INFORMATION4,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION5_1',P_INFORMATION5,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION6_1',P_INFORMATION6,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION7_1',P_INFORMATION7,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION8_1',P_INFORMATION8,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION9_1',P_INFORMATION9,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION10_1',P_INFORMATION10,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION11_1',P_INFORMATION11,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION12_1',P_INFORMATION12,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION13_1',P_INFORMATION13,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION14_1',P_INFORMATION14,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION15_1',P_INFORMATION15,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION16_1',P_INFORMATION16,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION17_1',P_INFORMATION17,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION18_1',P_INFORMATION18,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION19_1',P_INFORMATION19,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION20_1',P_INFORMATION20,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION21_1',P_INFORMATION21,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION22_1',P_INFORMATION22,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION23_1',P_INFORMATION23,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION24_1',P_INFORMATION24,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION25_1',P_INFORMATION25,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION26_1',P_INFORMATION26,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION27_1',P_INFORMATION27,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION28_1',P_INFORMATION28,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION29_1',P_INFORMATION29,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_INFORMATION30_1',P_INFORMATION30,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE_CATEGORY_1',NULL,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE1_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE2_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE3_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE4_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE5_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE6_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE7_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE8_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE9_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE10_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE11_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE12_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE13_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE14_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE15_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE16_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE17_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE18_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE19_1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE20_1','',null );
--dbms_output.put_line ('create_transaction_Values=Done' );
return v_transaction_id;
end;
--
-- Procedure create the Request
--
Procedure CREATE_REQUEST ( PP_SELECTED_USER_NAME varchar2--1024
,PP_CREATOR_USER_NAME varchar2--1024
,PP_ITEM_TYPE varchar2--'HRSSA'
,PP_RESPONSIBILITY_NAME varchar2--'MOFI Absence Management';
,PP_CALLED_FROM varchar2 --'MOF_HR_LOA_CREATE_DECREE_SS'
,PP_process varchar2--'MOF_HR_EIT_LOA_CREATE_PRC'
,PP_EIT_NAME varchar2--'MOF_HR_LOA_CREATE_DECREE'
,PP_INFORMATION1 varchar2 DEFAULT NULL
,PP_INFORMATION2 varchar2 DEFAULT NULL
,PP_INFORMATION3 varchar2 DEFAULT NULL
,PP_INFORMATION4 varchar2 DEFAULT NULL
,PP_INFORMATION5 varchar2 DEFAULT NULL
,PP_INFORMATION6 varchar2 DEFAULT NULL
,PP_INFORMATION7 varchar2 DEFAULT NULL
,PP_INFORMATION8 varchar2 DEFAULT NULL
,PP_INFORMATION9 varchar2 DEFAULT NULL
,PP_INFORMATION10 varchar2 DEFAULT NULL
,PP_INFORMATION11 varchar2 DEFAULT NULL
,PP_INFORMATION12 varchar2 DEFAULT NULL
,PP_INFORMATION13 varchar2 DEFAULT NULL
,PP_INFORMATION14 varchar2 DEFAULT NULL
,PP_INFORMATION15 varchar2 DEFAULT NULL
,PP_INFORMATION16 varchar2 DEFAULT NULL
,PP_INFORMATION17 varchar2 DEFAULT NULL
,PP_INFORMATION18 varchar2 DEFAULT NULL
,PP_INFORMATION19 varchar2 DEFAULT NULL
,PP_INFORMATION20 varchar2 DEFAULT NULL
,PP_INFORMATION21 varchar2 DEFAULT NULL
,PP_INFORMATION22 varchar2 DEFAULT NULL
,PP_INFORMATION23 varchar2 DEFAULT NULL
,PP_INFORMATION24 varchar2 DEFAULT NULL
,PP_INFORMATION25 varchar2 DEFAULT NULL
,PP_INFORMATION26 varchar2 DEFAULT NULL
,PP_INFORMATION27 varchar2 DEFAULT NULL
,PP_INFORMATION28 varchar2 DEFAULT NULL
,PP_INFORMATION29 varchar2 DEFAULT NULL
,PP_INFORMATION30 varchar2 DEFAULT NULL
,p_activity_id number
--OUT
,v_msg out varchar2
)
is
v_Item_Key_id number;
v_trans_id number;
vv_output varchar2(5000);
v_responsibility_name varchar2(500);
v_app_id number;
l_user_id number;
l_resp_id number;
PP_CREATOR_PERSON_ID number;
PP_SELECTED_PERSON_ID number;
--out_msg varchar2(5000);
begin
-----------------------------------------------------------------------------------------------
--
--NITIALIZE
--
dbms_output.put_line('Initilize');
v_responsibility_name :=PP_RESPONSIBILITY_NAME;
v_app_id :=800;
select RESPONSIBILITY_ID into l_resp_id
from fnd_responsibility_tl
where RESPONSIBILITY_NAME = v_responsibility_name;
select user_id into l_user_id
from fnd_user where user_name = PP_CREATOR_USER_NAME;
fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
resp_id=>l_resp_id,
resp_appl_id=>v_app_id);
--dbms_output.put_line ('Done INITIALIZE'||out_msg);
SELECT EMPLOYEE_ID
INTO PP_CREATOR_PERSON_ID
FROM FND_USER
WHERE USER_NAME= PP_CREATOR_USER_NAME;
SELECT EMPLOYEE_ID
INTO PP_SELECTED_PERSON_ID
FROM FND_USER
WHERE USER_NAME= PP_SELECTED_USER_NAME;
--
--1----------------------------------------------------------------------------------------
--
dbms_output.put_line('Create_WorkFlow_Process');
v_Item_Key_id:= FUJ_HR_EIT_WORKFLOW_PKG.Create_WorkFlow_Process (P_CREATOR_USER_NAME =>PP_CREATOR_USER_NAME
,P_SELECTED_USER_NAME => PP_SELECTED_USER_NAME
,P_SELECTED_PERSON_ID => PP_SELECTED_PERSON_ID
,P_CREATOR_PERSON_ID =>PP_CREATOR_PERSON_ID
,P_CALLED_FROM =>PP_CALLED_FROM
,P_process =>PP_process
,p_activity_id => p_activity_id
,v_output =>vv_output
);
dbms_output.put_line ('v_Item_Key_id='||v_Item_Key_id);
if v_Item_Key_id =-5 or v_Item_Key_id is null -- Errors
then
v_msg:='Error--- Item_Key_id Not Generated'||vv_output;
dbms_output.put_line (v_msg);
--Error
else
--
--2----------------------------------------------------------------------------------------
--
dbms_output.put_line('CREATE_TRANSACTION');
v_trans_id:= FUJ_HR_EIT_WORKFLOW_PKG.CREATE_TRANSACTION (P_SELECTED_PERSON_ID =>PP_SELECTED_PERSON_ID
, P_CREATOR_PERSON_ID =>PP_CREATOR_PERSON_ID
, P_ITEM_TYPE =>PP_ITEM_TYPE
, P_ITEM_KEY =>v_Item_Key_id
, P_PROCESS_NAME =>PP_process
, P_CALLED_FROM =>PP_CALLED_FROM
,p_activity_id => p_activity_id
, P_EIT_NAME => PP_EIT_NAME
,P_INFORMATION1 =>PP_INFORMATION1
,P_INFORMATION2 =>PP_INFORMATION2
,P_INFORMATION3 =>PP_INFORMATION3
,P_INFORMATION4 =>PP_INFORMATION4
,P_INFORMATION5 =>PP_INFORMATION5
,P_INFORMATION6 =>PP_INFORMATION6
,P_INFORMATION7 =>PP_INFORMATION7
,P_INFORMATION8 =>PP_INFORMATION8
,P_INFORMATION9 =>PP_INFORMATION9
,P_INFORMATION10 =>PP_INFORMATION10
,P_INFORMATION11 =>PP_INFORMATION11
,P_INFORMATION12 =>PP_INFORMATION12
,P_INFORMATION13 =>PP_INFORMATION13
,P_INFORMATION14 =>PP_INFORMATION14
,P_INFORMATION15 =>PP_INFORMATION15
,P_INFORMATION16 =>PP_INFORMATION16
,P_INFORMATION17 =>PP_INFORMATION17
,P_INFORMATION18 =>PP_INFORMATION18
,P_INFORMATION19 =>PP_INFORMATION19
,P_INFORMATION20 =>PP_INFORMATION20
,P_INFORMATION21 =>PP_INFORMATION21
,P_INFORMATION22 =>PP_INFORMATION22
,P_INFORMATION23 =>PP_INFORMATION23
,P_INFORMATION24 => PP_INFORMATION24
,P_INFORMATION25 => PP_INFORMATION25
,P_INFORMATION26 => PP_INFORMATION26
,P_INFORMATION27 => PP_INFORMATION27
,P_INFORMATION28 => PP_INFORMATION28
,P_INFORMATION29 => PP_INFORMATION29
,P_INFORMATION30 => PP_INFORMATION30
) ;
dbms_output.put_line ('v_trans_id='||v_trans_id);
--
--3----------------------------------------------------------------------------------------
--
if v_trans_id is null
then
v_msg:='Error--- trans_id Not Generated';
dbms_output.put_line (v_msg);
--Error
else
--
--4----------------------------------------------------------------------------------------
--
dbms_output.put_line('Update_WorkFlow_Attributes');
FUJ_HR_EIT_WORKFLOW_PKG.Update_WorkFlow_Attributes ( P_itemtype =>PP_ITEM_TYPE
, P_itemkey =>v_Item_Key_id
, P_TRANSACTION_ID=>v_trans_id
);
--commit;
--
--5----------------------------------------------------------------------------------------
--
FUJ_HR_EIT_WORKFLOW_PKG.Start_WorkFlow_Process (P_itemtype =>PP_ITEM_TYPE
, P_itemkey =>v_Item_Key_id);
--commit;
--
--6----------------------------------------------------------------------------------------
--important to start Approval cycle
FUJ_HR_EIT_WORKFLOW_PKG.Start_Approval_Process (P_itemtype =>PP_ITEM_TYPE
, P_itemkey =>v_Item_Key_id , p_activity_id => p_activity_id);
v_msg:='Done';
end if;
null;
End if;
end;
--procedure to approve and reject both
PROCEDURE approve_reject_p (pAction IN VARCHAR2, --APPROVED/REJECTED
pComments IN VARCHAR2, -- User Comments -- Optional
pNotification_id IN NUMBER, -- Notification ID
pStatus OUT VARCHAR2,
pMessage OUT VARCHAR2
)
IS
l_user_name VARCHAR2(250);
BEGIN
BEGIN
SELECT RECIPIENT_ROLE
INTO l_user_name
FROM WF_NOTIFICATIONS
WHERE notification_id = pNotification_id;
EXCEPTION
WHEN OTHERS THEN
l_user_name := NULL;
END;
wf_notification.SETATTRTEXT(pNotification_id,'RESULT',pAction);
wf_notification.Respond(pNotification_id,pComments,l_user_name,pAction);
pStatus := 'S';
pMessage := 'Successfully '||initcap(pAction);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
pStatus := 'E';
pMessage := 'Unexpected error while Approve/Reject the notification: '||SQLERRM;
END approve_reject_p;
-- Procedure only to Approve By notificaion ID
--
procedure Approve_Request(p_Not_ID Number
,p_responder varchar2
)
--return Varchar2
as
l_lookup_code varchar2(80) := 'APPROVED';
v_msg varchar2(5000) :='Done';
begin
begin
wf_notification.SetAttrText (p_Not_ID , 'RESULT', l_lookup_code);
wf_notification.Respond (p_Not_ID ,p_responder,p_responder);
v_msg :='Done';
commit;
exception
when others
then
v_msg:='Error---'||SQLCODE||' = '||SQLERRM;
end;
-- return v_msg;
End;
--
--
-- Function to Approve By notificaion ID
--
Function Approve_Request(p_Not_ID Number
,p_responder varchar2
)
return Varchar2
is
l_lookup_code varchar2(80) := 'APPROVED';
v_msg varchar2(5000) :='Done';
begin
begin
wf_notification.SetAttrText (p_Not_ID , 'RESULT', l_lookup_code);
wf_notification.Respond (p_Not_ID ,p_responder,p_responder);
v_msg :='Done';
commit;
exception
when others
then
v_msg:='Error---'||SQLCODE||' = '||SQLERRM;
end;
return v_msg;
End;
--
-- Function to Reject By notificaion ID
--
Function Reject_Request(p_Not_ID Number
,p_responder varchar2
)
return Varchar2
is
l_lookup_code varchar2(80) := 'REJECTED';
v_msg varchar2(5000) :='Done';
begin
begin
wf_notification.SetAttrText (p_Not_ID , 'RESULT', l_lookup_code);
wf_notification.Respond (p_Not_ID ,p_responder,p_responder);
v_msg :='Done';
commit;
exception
when others
then
v_msg:='Error---'||SQLCODE||' = '||SQLERRM;
end;
return v_msg;
End;
--
-- Function to Close (FYI) Notifications By notificaion ID
--
Function Close_FYI_Notification (p_Not_ID Number
,p_responder varchar2
)
return Varchar2
is
v_msg varchar2(5000) :='Done';
begin
begin
-- wf_notification.Close (p_Not_ID ,p_responder);
wf_notification.Respond (p_Not_ID ,p_responder,p_responder);
v_msg :='Done';
commit;
exception
when others
then
v_msg:='Error---'||SQLCODE||' = '||SQLERRM;
end;
return v_msg;
End;
end;
/
------------------------------------------Package ends here ---------------------------------
2.
--Block to run the above package
-
---------------------------
Declare
P_msg varchar2(5000);
begin
begin
FUJ_HR_EIT_WORKFLOW_PKG.CREATE_REQUEST ( PP_CREATOR_USER_NAME =>'EG_A.ALNUIMI'
,PP_SELECTED_USER_NAME =>'EG_AFZAL'
--,PP_CREATOR_PERSON_ID =>V_EMPLOYEE_ID
,PP_ITEM_TYPE =>'HRSSA'
,PP_RESPONSIBILITY_NAME =>'Employee Self Service Fujairah'
,PP_CALLED_FROM =>'HR_EIT_VS_SS' --SELECT text_value FROM WF_ITEM_ATTRIBUTE_values where item_type = 'HRSSA' and item_key in ('70482') and name = 'HR_PERZ_FUNCTION_NAME_ATTR'--applied from ebs
,PP_process =>'HR_EIT_VEN_PRC' --SELECT PROCESS_NAME FROM HR_API_TRANSACTIONS WHERE TRANSACTION_ID in ( 252710) applied from ebs
,PP_EIT_NAME =>'FUJ_VISA_ENTITLEMENTS' --SELECT VARCHAR2_VALUE FROM HR_API_TRANSACTION_VALUES WHERE TRANSACTION_STEP_ID = 269097 AND NAME = 'P_INFORMATION_CATEGORY_1' --applied from ebs
,p_activity_id => '347867' --SELECT ACTIVITY_ID FROM HR_API_TRANSACTION_STEPS WHERE TRANSACTION_ID IN(252710) applied from ebs --OUT
,PP_INFORMATION1 =>'Renewal'
,PP_INFORMATION2 =>'0'
,PP_INFORMATION5 => 'TEST FROM API'
,v_msg =>P_msg
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN P_msg :='Error103=No Employee Assigned Or UserName Not Found';
DBMS_OUTPUT.PUT_LINE(SQLERRM);
End;
dbms_output.put_line('P_msg='||P_msg);
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
-------------------------------------------------------------
3. Approve_Reject_Proc optional for testing
create or replace PROCEDURE approve_reject_proc (pAction IN VARCHAR2, --APPROVE/REJECT
pComments IN VARCHAR2, -- User Comments -- Optional
pNotification_id IN NUMBER, -- Notification ID
pStatus OUT VARCHAR2,
pMessage OUT VARCHAR2
)
IS
l_user_name VARCHAR2(250);
BEGIN
BEGIN
SELECT RECIPIENT_ROLE
INTO l_user_name
FROM WF_NOTIFICATIONS
WHERE notification_id = pNotification_id;
EXCEPTION
WHEN OTHERS THEN
l_user_name := NULL;
END;
wf_notification.SETATTRTEXT(pNotification_id,'RESULT',pAction);
wf_notification.Respond(pNotification_id,
pComments,
l_user_name,
pAction);
pStatus := 'S';
pMessage := 'Successfully '||initcap(pAction);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
pStatus := 'E';
pMessage := 'Unexpected error while Approve/Reject the notification: '||SQLERRM;
END approve_reject_proc;
--RUN BELOW SCRIPT IN separate new session
DECLARE
Pstatus_out varchar2(200);
PMessage_out Varchar2(200);
l_notification_id number := :notification_id;--get from the below query
l_approve_reject varchar2(200) := 'REJECTED'; --APPROVED OR REJECTED
BEGIN
approve_reject_proc(l_approve_reject,'Test Comments',l_notification_id,Pstatus_out, PMessage_out);
dbms_output.put_line('Pstatus ->'||Pstatus_out|| '-PMessage->'||PMessage_out);
exception when others then
dbms_output.put_line(sqlerrm);
END;
--to get notification id for approval
SELECT rownum l_sequence ,a.l_notification_id ,a.l_role role ,a.l_action action ,a.l_comments ,to_char(a.l_date,'dd-mm-yyyy hh:mi:ss am') l_date FROM (
SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname
(nvl (wn.more_info_role ,wn.recipient_role)) l_role ,
decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING')) ,
decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action ,
na.text_value l_comments ,ias.end_date l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
,wf_item_activity_statuses ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
UNION
SELECT pah.notification_id l_notification_id ,wf_directory.getroledisplayname (pah.user_name) l_role
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action)) l_action ,pah.user_comment l_comments
,pah.last_update_date l_date FROM pqh_ss_approval_history pah WHERE pah.transaction_item_type = :p_item_type
AND pah.transaction_item_key = :p_item_key AND pah.action NOT IN ('APPROVED','REJECTED','SUBMIT' ,'RFC','RESUBMIT','TIMEOUT','QUESTION','ANSWER')
UNION
SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (wn.recipient_role) l_role
,decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING'))
,decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action
,decode (wn.status ,'CANCELED' ,NULL ,nvl (na.text_value ,
( SELECT user_comment FROM wf_comments WHERE notification_id = wn.notification_id AND action = l.lookup_code AND rownum = 1 ))) l_comments
,nvl (ias.end_date ,ias.begin_date) l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
,wf_item_activity_statuses_h ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
UNION
SELECT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (nvl (wn.more_info_role ,wn.recipient_role)) l_role
,decode (wn.status ,'CANCELED' ,decode (pah.action ,'TIMEOUT' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN'))) ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))) l_action
,decode (pah.notification_id ,wn.notification_id ,pah.user_comment ,NULL) l_comments ,pah.last_update_date l_date
FROM pqh_ss_approval_history pah ,wf_notifications wn
WHERE pah.transaction_item_type = :p_item_type AND pah.transaction_item_key = :p_item_key
AND pah.action IN ('TIMEOUT','RESUBMIT') AND wn.group_id = ( SELECT group_id FROM wf_notifications WHERE notification_id = pah.notification_id
AND rownum = 1 )
UNION select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role
-- ,WF_CORE.TRANSLATE(C.ACTION) l_action
,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES_H IAS, WF_COMMENTS C, wf_notifications wn
where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id
and wn.notification_id = c.notification_id and C.ACTION in('QUESTION', 'ANSWER')
UNION select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role -- ,WF_CORE.TRANSLATE(C.ACTION) l_action
,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES IAS, WF_COMMENTS C, wf_notifications wn
where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id and wn.notification_id = c.notification_id
and C.ACTION in('QUESTION', 'ANSWER')
UNION SELECT 0 ,wf_directory.getroledisplayname (owner_role) l_role ,wf_core.translate ('SUBMIT')
,appr.text_value note ,begin_date l_date
FROM wf_items i ,wf_item_attribute_values appr
WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND i.item_type = appr.item_type (+) AND i.item_key = appr.item_key (+)
AND appr.name (+) = 'SUBMIT_COMMENTS' ORDER BY l_notification_id ,l_date ) a
4. Useful tables and queries..
SELECT * FROM HR_API_TRANSACTIONS
WHERE TRANSACTION_ID in ( 252719,252725)
--where item_key in ('70480','70486')
--WHERE CREATION_DATE LIKE SYSDATE
SELECT * FROM HR_API_TRANSACTION_STEPS WHERE TRANSACTION_ID IN( 252719,252725)
SELECT VARCHAR2_VALUE FROM HR_API_TRANSACTION_VALUES WHERE TRANSACTION_STEP_ID = 269097 AND NAME = 'P_INFORMATION_CATEGORY_1'
and (varchar2_value is not null or number_value is not null)
order by 1
SELECT * FROM WF_ITEM_ATTRIBUTE_values where item_type = 'HRSSA' and item_key in ('70494')
and name = 'PROCESS_ORDER_STRING'
5. Errors and Solutions.
Step1: Apply from front end and check all the approvers generated and working fine.
check the tables hr_api_transaction, hr_api_transaction_steps, hr_api_transaction_values, WF_ITEM_ATTRIBUTE_values
Step2: Create the package specification.
Step3: modify the package body as below and create it
3.1 wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PERZ_ORGANIZATION_ID_ATTR','81'); --mandatory change 1 business group
3.2 wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_ORDER_STRING','%345294%1%345284%2%345279%3%'); --mandatory change 2, SELECT text_value FROM WF_ITEM_ATTRIBUTE_values where item_type = 'HRSSA' and item_key in ('70482') and name = 'PROCESS_ORDER_STRING' applied from ebs
3.3 V_BUSINESS_GROUP_ID := 81; --mandatory change 3 business group
Step4: Activity_id can be find from hr_api_transaction_step pass ur transaction_id applied from ebs
Errors and Solutions:
1. Approvers r not initiated or stopping at EITV4.0 without result
Solution: missing to call pqh_ss_workflow.start_approval_wf chk the package.
2. Error:You do not have access to the selected person record. Please contact your Human Resources Adminstrator.
above error appears for the approver while opening the notification.
Solution: Check all the attributes r assinged or not, i resolved it by finding from backend missing below attributes.
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FYI_NTF_DETAILS','Y');
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PERZ_ORGANIZATION_ID_ATTR','81'); --mandatory change 1 business group
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_ORDER_STRING','%345294%1%345284%2%345279%3%'); --mandatory change 2, chk from any txn applied from front end from table WF_ITEM_ATTRIBUTE_values
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'SAVE_ATTACHMENT','Y');
wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_AME_APP_ID_ATTR',800);
3. Error after final approval: the changes were not applied because The Person ID that you have entered does not exist.
One transaction applied from api and approved from ebs final commit is fine, but if approval is done using procedure from backend then commit error.
solution:
chk the query
SELECT * FROM WF_ITEM_ATTRIBUTE_values
where item_type = 'HRSSA'
and item_key in ('70478','70480')--'one applied from ebs approved from ebs, the other applied from api approved from procedure.
and (text_value is not null or number_value is not null)
AND NAME = 'HR_USER_ID_ATTR'
order by 3
Solution: I was getting value of user initiated in toad, so i used new session and approved using procedure, then the value store in above attribute is '-1', commit worked fine.
No comments:
Post a Comment