Updated Code with 30 Segments as parameters:
http://mogalafzal.blogspot.ae/2018/04/oracle-apps-r12-apply-sit-sshr.html
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 steps 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%'); --SELECT text_value FROM WF_ITEM_ATTRIBUTE_values where item_type = 'HRSSA' and item_key in ('70482') and name = 'PROCESS_ORDER_STRING'
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 SITV4.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
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.
Package Starts here ----------------------------------
CREATE OR REPLACE Package APPS.FUJ_HR_WORKFLOW_API_PKG
is
--
-- Procedure create the Request
--
Procedure CREATE_REQUEST ( PP_USER_NAME varchar2--EG_AFZAL
-- ,PP_CREATOR_PERSON_ID number--26876
,PP_ITEM_TYPE varchar2--'HRSSA'
,PP_RESPONSIBILITY_NAME varchar2--'Employee Self Service XX'
,PP_CALLED_FROM varchar2 --'LSG_HR_LOA_CREATE_DECREE_SS'
,PP_process varchar2--'LSG_HR_EIT_LOA_CREATE_PRC'
,PP_SIT_NAME varchar2--'LSG_HR_LOA_CREATE_DECREE'
,PP_SEGMENT1 varchar2 --SEGMENT1
,PP_SEGMENT10 varchar2 --SEGMENT11
,PP_SEGMENT11 varchar2 --SEGMENT12
,PP_SEGMENT12 varchar2--SEGMENT13
,PP_SEGMENT13 varchar2 --SEGMENT14
,PP_SEGMENT3 varchar2 default null --segment3
,p_activity_id number
,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_WORKFLOW_API_PKG
is
--
--Function Used to Create Work Flow and return with Item Key
--
function Create_WorkFlow_Process (P_USER_NAME Varchar2
,P_CREATOR_PERSON_ID NUMBER
,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;
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=V_CURRENT_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;
--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=V_CURRENT_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 :=V_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',v_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',V_CURRENT_PERSON_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_USERNAME',v_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',V_CURRENT_PERSON_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_USERNAME',v_USER_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_DISPLAY_NAME',V_CURRENT_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','%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);
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_CREATOR_PERSON_ID NUMBER
, P_ITEM_TYPE VARCHAR2
, P_ITEM_KEY VARCHAR2
, P_PROCESS_NAME VARCHAR2
, P_CALLED_FROM VARCHAR2
, P_SIT_NAME VARCHAR2
, P_ACTIVITY_ID NUMBER
,P_SEGMENT1 varchar2
,P_SEGMENT10 varchar2
,P_SEGMENT11 varchar2
,P_SEGMENT12 varchar2
,P_SEGMENT13 varchar2
,P_SEGMENT3 varchar2
)
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_SIT_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_SIT_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_CREATOR_PERSON_ID;
v_transaction_effective_date :=sysdate;
--Step
V_activity_id := p_activity_id;
--V_ACTIVE_ROW_ID :='-987123654';
v_api_name :='HR_PROCESS_SIT_SS.PROCESS_API';--constant
V_SIT_NAME := P_SIT_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 ID_FLEX_STRUCTURE_NAME, ID_FLEX_NUM
INTO V_SIT_FULL_NAME, P_ID_FLEX_NUM
FROM FND_ID_FLEX_STRUCTURES_VL
WHERE ID_FLEX_STRUCTURE_CODE = V_SIT_NAME
AND APPLICATION_ID = 800
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_CREATOR_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_creator_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_REVIEW_PROC_CALL','HrSit',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' ,'INSERT',null );------constant
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ANALYSIS_CRITERIA_ID',ln_analysis_criteria_id,null );
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_PEA_OBJECT_VERSION_NUMBER' ,'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_ATTRIBUTE_CATEGORY',NULL,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_ATTRIBUTE1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE2','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE3','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE4','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE5','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE6','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE7','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE8','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE9','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE10','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE11','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE12','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE13','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE14','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE15','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE16','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE17','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE18','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE19','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE20','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_STRUCTURE_CODE',V_SIT_NAME,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_STRUCTURE_NAME',V_SIT_FULL_NAME,null );
Hr_transaction_api.set_date_value ( v_validate ,V_transaction_step_id , v_creator_person_id ,'P_DATE_FROM', trunc(sysdate),null);
Hr_transaction_api.set_date_value ( v_validate ,V_transaction_step_id , v_creator_person_id ,'P_DATE_TO', null,null);
Hr_transaction_api.set_date_value ( v_validate ,V_transaction_step_id , v_creator_person_id ,'P_EFFECTIVE_DATE', trunc(sysdate),null);
--dbms_output.put_line ('create_transaction_Values=Done' );
return v_transaction_id;
end;
--
-- Procedure create the Request
--
Procedure CREATE_REQUEST ( PP_USER_NAME varchar2--1024
--,PP_CREATOR_PERSON_ID number--172
,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_SIT_NAME varchar2--'MOF_HR_LOA_CREATE_DECREE'
,PP_SEGMENT1 varchar2
,PP_SEGMENT10 varchar2
,PP_SEGMENT11 varchar2
,PP_SEGMENT12 varchar2
,PP_SEGMENT13 varchar2
,PP_SEGMENT3 varchar2
,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;
--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_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_USER_NAME;
--
--1----------------------------------------------------------------------------------------
--
dbms_output.put_line('Create_WorkFlow_Process');
v_Item_Key_id:= FUJ_HR_WORKFLOW_API_PKG.Create_WorkFlow_Process (P_USER_NAME =>PP_USER_NAME
,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_WORKFLOW_API_PKG.CREATE_TRANSACTION (
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_SIT_NAME =>PP_SIT_NAME
,P_SEGMENT1 =>PP_SEGMENT1
,P_SEGMENT10 =>PP_SEGMENT10
,P_SEGMENT11 =>PP_SEGMENT11
,P_SEGMENT12 =>PP_SEGMENT12
,P_SEGMENT13 =>PP_SEGMENT13
,P_SEGMENT3 =>PP_SEGMENT3
) ;
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_WORKFLOW_API_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_WORKFLOW_API_PKG.Start_WorkFlow_Process (P_itemtype =>PP_ITEM_TYPE
, P_itemkey =>v_Item_Key_id);
--commit;
--
--6----------------------------------------------------------------------------------------
--important to start Approval cycle
FUJ_HR_WORKFLOW_API_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------------------------------------------------
Block to run the above package:
---------------------------
declare
P_msg varchar2(5000);
begin
begin
FUJ_HR_WORKFLOW_API_PKG.CREATE_REQUEST ( PP_USER_NAME =>'EG_AFZAL'
--,PP_CREATOR_PERSON_ID =>V_EMPLOYEE_ID
,PP_ITEM_TYPE =>'HRSSA'
,PP_RESPONSIBILITY_NAME =>'Employee Self Service XX'
,PP_CALLED_FROM =>'HR_SIT_CR' --SELECT HR_PERZ_FUNCTION_NAME_ATTR FROM WF_ITEM_ATTRIBUTE_values where item_type = 'HRSSA' and item_key in ('70482') --applied from ebs
,PP_process =>'HR_SIT_CRGOV_PRC' --SELECT PROCESS_NAME FROM HR_API_TRANSACTIONS WHERE TRANSACTION_ID in ( 252710) applied from ebs
,PP_SIT_NAME =>'FUJ_CERTIFICATES' --select ID_FLEX_STRUCTURE_CODE from FND_ID_FLEX_STRUCTURES_VL where id_flex_num = <ur sit_flex_id_num)
,PP_SEGMENT1 =>'Others'
,PP_SEGMENT10 =>'Attention Test FROM api2'
,PP_SEGMENT11 =>'108'
,PP_SEGMENT12 =>'17568'
,PP_SEGMENT13 => 'Remarks Test apI2'
,PP_SEGMENT3 =>''
,p_activity_id => 345294 --SELECT ACTIVITY_ID FROM HR_API_TRANSACTION_STEPS WHERE TRANSACTION_ID IN(252710) applied from ebs
--OUT
,v_msg =>P_msg
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN P_msg :='Error103=No Employee Assigned Or UserName Not Found';
End;
dbms_output.put_line('P_msg='||P_msg);
end;
-----------------------------------------------------------------------------------------------------------------------
Thanks to Mr.Farhan for providing the script which I modified.
Website of Mr. Farhan http://oraclehrmsoverview.com/author/oraclehrmsoverview/.
http://mogalafzal.blogspot.ae/2018/04/oracle-apps-r12-apply-sit-sshr.html
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 steps 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%'); --SELECT text_value FROM WF_ITEM_ATTRIBUTE_values where item_type = 'HRSSA' and item_key in ('70482') and name = 'PROCESS_ORDER_STRING'
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 SITV4.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
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.
Package Starts here ----------------------------------
CREATE OR REPLACE Package APPS.FUJ_HR_WORKFLOW_API_PKG
is
--
-- Procedure create the Request
--
Procedure CREATE_REQUEST ( PP_USER_NAME varchar2--EG_AFZAL
-- ,PP_CREATOR_PERSON_ID number--26876
,PP_ITEM_TYPE varchar2--'HRSSA'
,PP_RESPONSIBILITY_NAME varchar2--'Employee Self Service XX'
,PP_CALLED_FROM varchar2 --'LSG_HR_LOA_CREATE_DECREE_SS'
,PP_process varchar2--'LSG_HR_EIT_LOA_CREATE_PRC'
,PP_SIT_NAME varchar2--'LSG_HR_LOA_CREATE_DECREE'
,PP_SEGMENT1 varchar2 --SEGMENT1
,PP_SEGMENT10 varchar2 --SEGMENT11
,PP_SEGMENT11 varchar2 --SEGMENT12
,PP_SEGMENT12 varchar2--SEGMENT13
,PP_SEGMENT13 varchar2 --SEGMENT14
,PP_SEGMENT3 varchar2 default null --segment3
,p_activity_id number
,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_WORKFLOW_API_PKG
is
--
--Function Used to Create Work Flow and return with Item Key
--
function Create_WorkFlow_Process (P_USER_NAME Varchar2
,P_CREATOR_PERSON_ID NUMBER
,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;
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=V_CURRENT_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;
--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=V_CURRENT_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 :=V_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',v_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',V_CURRENT_PERSON_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_USERNAME',v_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',V_CURRENT_PERSON_ID);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_USERNAME',v_USER_NAME);
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_DISPLAY_NAME',V_CURRENT_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','%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);
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_CREATOR_PERSON_ID NUMBER
, P_ITEM_TYPE VARCHAR2
, P_ITEM_KEY VARCHAR2
, P_PROCESS_NAME VARCHAR2
, P_CALLED_FROM VARCHAR2
, P_SIT_NAME VARCHAR2
, P_ACTIVITY_ID NUMBER
,P_SEGMENT1 varchar2
,P_SEGMENT10 varchar2
,P_SEGMENT11 varchar2
,P_SEGMENT12 varchar2
,P_SEGMENT13 varchar2
,P_SEGMENT3 varchar2
)
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_SIT_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_SIT_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_CREATOR_PERSON_ID;
v_transaction_effective_date :=sysdate;
--Step
V_activity_id := p_activity_id;
--V_ACTIVE_ROW_ID :='-987123654';
v_api_name :='HR_PROCESS_SIT_SS.PROCESS_API';--constant
V_SIT_NAME := P_SIT_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 ID_FLEX_STRUCTURE_NAME, ID_FLEX_NUM
INTO V_SIT_FULL_NAME, P_ID_FLEX_NUM
FROM FND_ID_FLEX_STRUCTURES_VL
WHERE ID_FLEX_STRUCTURE_CODE = V_SIT_NAME
AND APPLICATION_ID = 800
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_CREATOR_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_creator_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_REVIEW_PROC_CALL','HrSit',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' ,'INSERT',null );------constant
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ANALYSIS_CRITERIA_ID',ln_analysis_criteria_id,null );
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_PEA_OBJECT_VERSION_NUMBER' ,'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_ATTRIBUTE_CATEGORY',NULL,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_ATTRIBUTE1','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE2','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE3','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE4','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE5','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE6','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE7','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE8','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE9','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE10','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE11','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE12','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE13','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE14','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE15','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE16','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE17','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE18','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE19','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_ATTRIBUTE20','',null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_STRUCTURE_CODE',V_SIT_NAME,null );
hr_transaction_api.set_varchar2_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_STRUCTURE_NAME',V_SIT_FULL_NAME,null );
Hr_transaction_api.set_date_value ( v_validate ,V_transaction_step_id , v_creator_person_id ,'P_DATE_FROM', trunc(sysdate),null);
Hr_transaction_api.set_date_value ( v_validate ,V_transaction_step_id , v_creator_person_id ,'P_DATE_TO', null,null);
Hr_transaction_api.set_date_value ( v_validate ,V_transaction_step_id , v_creator_person_id ,'P_EFFECTIVE_DATE', trunc(sysdate),null);
--dbms_output.put_line ('create_transaction_Values=Done' );
return v_transaction_id;
end;
--
-- Procedure create the Request
--
Procedure CREATE_REQUEST ( PP_USER_NAME varchar2--1024
--,PP_CREATOR_PERSON_ID number--172
,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_SIT_NAME varchar2--'MOF_HR_LOA_CREATE_DECREE'
,PP_SEGMENT1 varchar2
,PP_SEGMENT10 varchar2
,PP_SEGMENT11 varchar2
,PP_SEGMENT12 varchar2
,PP_SEGMENT13 varchar2
,PP_SEGMENT3 varchar2
,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;
--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_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_USER_NAME;
--
--1----------------------------------------------------------------------------------------
--
dbms_output.put_line('Create_WorkFlow_Process');
v_Item_Key_id:= FUJ_HR_WORKFLOW_API_PKG.Create_WorkFlow_Process (P_USER_NAME =>PP_USER_NAME
,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_WORKFLOW_API_PKG.CREATE_TRANSACTION (
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_SIT_NAME =>PP_SIT_NAME
,P_SEGMENT1 =>PP_SEGMENT1
,P_SEGMENT10 =>PP_SEGMENT10
,P_SEGMENT11 =>PP_SEGMENT11
,P_SEGMENT12 =>PP_SEGMENT12
,P_SEGMENT13 =>PP_SEGMENT13
,P_SEGMENT3 =>PP_SEGMENT3
) ;
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_WORKFLOW_API_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_WORKFLOW_API_PKG.Start_WorkFlow_Process (P_itemtype =>PP_ITEM_TYPE
, P_itemkey =>v_Item_Key_id);
--commit;
--
--6----------------------------------------------------------------------------------------
--important to start Approval cycle
FUJ_HR_WORKFLOW_API_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------------------------------------------------
Block to run the above package:
---------------------------
declare
P_msg varchar2(5000);
begin
begin
FUJ_HR_WORKFLOW_API_PKG.CREATE_REQUEST ( PP_USER_NAME =>'EG_AFZAL'
--,PP_CREATOR_PERSON_ID =>V_EMPLOYEE_ID
,PP_ITEM_TYPE =>'HRSSA'
,PP_RESPONSIBILITY_NAME =>'Employee Self Service XX'
,PP_CALLED_FROM =>'HR_SIT_CR' --SELECT HR_PERZ_FUNCTION_NAME_ATTR FROM WF_ITEM_ATTRIBUTE_values where item_type = 'HRSSA' and item_key in ('70482') --applied from ebs
,PP_process =>'HR_SIT_CRGOV_PRC' --SELECT PROCESS_NAME FROM HR_API_TRANSACTIONS WHERE TRANSACTION_ID in ( 252710) applied from ebs
,PP_SIT_NAME =>'FUJ_CERTIFICATES' --select ID_FLEX_STRUCTURE_CODE from FND_ID_FLEX_STRUCTURES_VL where id_flex_num = <ur sit_flex_id_num)
,PP_SEGMENT1 =>'Others'
,PP_SEGMENT10 =>'Attention Test FROM api2'
,PP_SEGMENT11 =>'108'
,PP_SEGMENT12 =>'17568'
,PP_SEGMENT13 => 'Remarks Test apI2'
,PP_SEGMENT3 =>''
,p_activity_id => 345294 --SELECT ACTIVITY_ID FROM HR_API_TRANSACTION_STEPS WHERE TRANSACTION_ID IN(252710) applied from ebs
--OUT
,v_msg =>P_msg
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN P_msg :='Error103=No Employee Assigned Or UserName Not Found';
End;
dbms_output.put_line('P_msg='||P_msg);
end;
-----------------------------------------------------------------------------------------------------------------------
Thanks to Mr.Farhan for providing the script which I modified.
Website of Mr. Farhan http://oraclehrmsoverview.com/author/oraclehrmsoverview/.
No comments:
Post a Comment