To Create new sit :http://mogalafzal.blogspot.ae/2018/04/apply-sit-sshr-transaction-and-initiate.html
CREATE OR REPLACE Package APPS.FUJHR_SITUPDATE_API_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_SIT_NAME varchar2--'MOF_HR_LOA_CREATE_DECREE'
,PP_PERSON_ANALYSIS_ID NUMBER
,PP_SEGMENT1 varchar2 DEFAULT NULL
,PP_SEGMENT2 varchar2 DEFAULT NULL
,PP_SEGMENT3 varchar2 DEFAULT NULL
,PP_SEGMENT4 varchar2 DEFAULT NULL
,PP_SEGMENT5 varchar2 DEFAULT NULL
,PP_SEGMENT6 varchar2 DEFAULT NULL
,PP_SEGMENT7 varchar2 DEFAULT NULL
,PP_SEGMENT8 varchar2 DEFAULT NULL
,PP_SEGMENT9 varchar2 DEFAULT NULL
,PP_SEGMENT10 varchar2 DEFAULT NULL
,PP_SEGMENT11 varchar2 DEFAULT NULL
,PP_SEGMENT12 varchar2 DEFAULT NULL
,PP_SEGMENT13 varchar2 DEFAULT NULL
,PP_SEGMENT14 varchar2 DEFAULT NULL
,PP_SEGMENT15 varchar2 DEFAULT NULL
,PP_SEGMENT16 varchar2 DEFAULT NULL
,PP_SEGMENT17 varchar2 DEFAULT NULL
,PP_SEGMENT18 varchar2 DEFAULT NULL
,PP_SEGMENT19 varchar2 DEFAULT NULL
,PP_SEGMENT20 varchar2 DEFAULT NULL
,PP_SEGMENT21 varchar2 DEFAULT NULL
,PP_SEGMENT22 varchar2 DEFAULT NULL
,PP_SEGMENT23 varchar2 DEFAULT NULL
,PP_SEGMENT24 varchar2 DEFAULT NULL
,PP_SEGMENT25 varchar2 DEFAULT NULL
,PP_SEGMENT26 varchar2 DEFAULT NULL
,PP_SEGMENT27 varchar2 DEFAULT NULL
,PP_SEGMENT28 varchar2 DEFAULT NULL
,PP_SEGMENT29 varchar2 DEFAULT NULL
,PP_SEGMENT30 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.FUJHR_SITUPDATE_API_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);
V_CREATOR_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=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','%345335%1%345325%2%345320%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_SIT_NAME VARCHAR2
, P_ACTIVITY_ID NUMBER
, p_person_analysis_id NUMBER
,P_SEGMENT1 varchar2 DEFAULT NULL
,P_SEGMENT2 varchar2 DEFAULT NULL
,P_SEGMENT3 varchar2 DEFAULT NULL
,P_SEGMENT4 varchar2 DEFAULT NULL
,P_SEGMENT5 varchar2 DEFAULT NULL
,P_SEGMENT6 varchar2 DEFAULT NULL
,P_SEGMENT7 varchar2 DEFAULT NULL
,P_SEGMENT8 varchar2 DEFAULT NULL
,P_SEGMENT9 varchar2 DEFAULT NULL
,P_SEGMENT10 varchar2 DEFAULT NULL
,P_SEGMENT11 varchar2 DEFAULT NULL
,P_SEGMENT12 varchar2 DEFAULT NULL
,P_SEGMENT13 varchar2 DEFAULT NULL
,P_SEGMENT14 varchar2 DEFAULT NULL
,P_SEGMENT15 varchar2 DEFAULT NULL
,P_SEGMENT16 varchar2 DEFAULT NULL
,P_SEGMENT17 varchar2 DEFAULT NULL
,P_SEGMENT18 varchar2 DEFAULT NULL
,P_SEGMENT19 varchar2 DEFAULT NULL
,P_SEGMENT20 varchar2 DEFAULT NULL
,P_SEGMENT21 varchar2 DEFAULT NULL
,P_SEGMENT22 varchar2 DEFAULT NULL
,P_SEGMENT23 varchar2 DEFAULT NULL
,P_SEGMENT24 varchar2 DEFAULT NULL
,P_SEGMENT25 varchar2 DEFAULT NULL
,P_SEGMENT26 varchar2 DEFAULT NULL
,P_SEGMENT27 varchar2 DEFAULT NULL
,P_SEGMENT28 varchar2 DEFAULT NULL
,P_SEGMENT29 varchar2 DEFAULT NULL
,P_SEGMENT30 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_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;
old_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_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_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_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,segment2,segment3,segment5,segment8)
select
ln_analysis_criteria_id,P_ID_FLEX_NUM,'N','Y',sysdate,-1,-1,-1,sysdate
,segment1 ,segment2,P_segment3,segment5,segment8
from PER_ANALYSIS_CRITERIA paa, PER_PERSON_ANALYSES pac
where paa.analysis_criteria_id = pac.analysis_criteria_id
and person_analysis_id = p_person_analysis_id;--4552982;
dbms_output.put_line ('V_transaction_step_id='||V_transaction_step_id);
select
pac.analysis_criteria_id
into old_analysis_criteria_id
from PER_ANALYSIS_CRITERIA paa, PER_PERSON_ANALYSES pac
where paa.analysis_criteria_id = pac.analysis_criteria_id
and person_analysis_id = p_person_analysis_id
and PAC.id_flex_num = P_ID_FLEX_NUM;
--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_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' ,'UPDATE',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' ,'1',null );------constant
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_PERSON_ANALYSIS_ID', p_person_analysis_id,null );
hr_transaction_api.set_number_value (v_validate ,V_transaction_step_id , v_creator_person_id ,'P_OLD_ANALYSIS_CRITERIA_ID',old_analysis_criteria_id ,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_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_SIT_NAME varchar2--'MOF_HR_LOA_CREATE_DECREE'
,PP_PERSON_ANALYSIS_ID NUMBER
,PP_SEGMENT1 varchar2 DEFAULT NULL
,PP_SEGMENT2 varchar2 DEFAULT NULL
,PP_SEGMENT3 varchar2 DEFAULT NULL
,PP_SEGMENT4 varchar2 DEFAULT NULL
,PP_SEGMENT5 varchar2 DEFAULT NULL
,PP_SEGMENT6 varchar2 DEFAULT NULL
,PP_SEGMENT7 varchar2 DEFAULT NULL
,PP_SEGMENT8 varchar2 DEFAULT NULL
,PP_SEGMENT9 varchar2 DEFAULT NULL
,PP_SEGMENT10 varchar2 DEFAULT NULL
,PP_SEGMENT11 varchar2 DEFAULT NULL
,PP_SEGMENT12 varchar2 DEFAULT NULL
,PP_SEGMENT13 varchar2 DEFAULT NULL
,PP_SEGMENT14 varchar2 DEFAULT NULL
,PP_SEGMENT15 varchar2 DEFAULT NULL
,PP_SEGMENT16 varchar2 DEFAULT NULL
,PP_SEGMENT17 varchar2 DEFAULT NULL
,PP_SEGMENT18 varchar2 DEFAULT NULL
,PP_SEGMENT19 varchar2 DEFAULT NULL
,PP_SEGMENT20 varchar2 DEFAULT NULL
,PP_SEGMENT21 varchar2 DEFAULT NULL
,PP_SEGMENT22 varchar2 DEFAULT NULL
,PP_SEGMENT23 varchar2 DEFAULT NULL
,PP_SEGMENT24 varchar2 DEFAULT NULL
,PP_SEGMENT25 varchar2 DEFAULT NULL
,PP_SEGMENT26 varchar2 DEFAULT NULL
,PP_SEGMENT27 varchar2 DEFAULT NULL
,PP_SEGMENT28 varchar2 DEFAULT NULL
,PP_SEGMENT29 varchar2 DEFAULT NULL
,PP_SEGMENT30 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:= FUJHR_SITUPDATE_API_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:= FUJHR_SITUPDATE_API_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_SIT_NAME => PP_SIT_NAME
,P_PERSON_ANALYSIS_ID =>PP_PERSON_ANALYSIS_ID
,P_SEGMENT1 =>PP_SEGMENT1
,P_SEGMENT2 =>PP_SEGMENT2
,P_SEGMENT3 =>PP_SEGMENT3
,P_SEGMENT4 =>PP_SEGMENT4
,P_SEGMENT5 =>PP_SEGMENT5
,P_SEGMENT6 =>PP_SEGMENT6
,P_SEGMENT7 =>PP_SEGMENT7
,P_SEGMENT8 =>PP_SEGMENT8
,P_SEGMENT9 =>PP_SEGMENT9
,P_SEGMENT10 =>PP_SEGMENT10
,P_SEGMENT11 =>PP_SEGMENT11
,P_SEGMENT12 =>PP_SEGMENT12
,P_SEGMENT13 =>PP_SEGMENT13
,P_SEGMENT14 =>PP_SEGMENT14
,P_SEGMENT15 =>PP_SEGMENT15
,P_SEGMENT16 =>PP_SEGMENT16
,P_SEGMENT17 =>PP_SEGMENT17
,P_SEGMENT18 =>PP_SEGMENT18
,P_SEGMENT19 =>PP_SEGMENT19
,P_SEGMENT20 =>PP_SEGMENT20
,P_SEGMENT21 =>PP_SEGMENT21
,P_SEGMENT22 =>PP_SEGMENT22
,P_SEGMENT23 =>PP_SEGMENT23
,P_SEGMENT24 => PP_SEGMENT24
,P_SEGMENT25 => PP_SEGMENT25
,P_SEGMENT26 => PP_SEGMENT26
,P_SEGMENT27 => PP_SEGMENT27
,P_SEGMENT28 => PP_SEGMENT28
,P_SEGMENT29 => PP_SEGMENT29
,P_SEGMENT30 => PP_SEGMENT30
) ;
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');
FUJHR_SITUPDATE_API_PKG.Update_WorkFlow_Attributes ( P_itemtype =>PP_ITEM_TYPE
, P_itemkey =>v_Item_Key_id
, P_TRANSACTION_ID=>v_trans_id
);
--commit;
--
--5----------------------------------------------------------------------------------------
--
FUJHR_SITUPDATE_API_PKG.Start_WorkFlow_Process (P_itemtype =>PP_ITEM_TYPE
, P_itemkey =>v_Item_Key_id);
--commit;
--
--6----------------------------------------------------------------------------------------
--important to start Approval cycle
FUJHR_SITUPDATE_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;
-----------------------Block to run
declare
P_msg varchar2(5000);
begin
begin
FUJHR_SITUPDATE_API_PKG.CREATE_REQUEST ( PP_CREATOR_USER_NAME =>'EG_A.ALNUIMI'
,PP_SELECTED_USER_NAME =>'EG_AFZAL'
,PP_ITEM_TYPE =>'HRSSA'
,PP_RESPONSIBILITY_NAME =>'Employee Self Service Fujairah'
,PP_CALLED_FROM =>'HR_SIT_JD' --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_JD_PRC' --SELECT PROCESS_NAME FROM HR_API_TRANSACTIONS WHERE TRANSACTION_ID in ( 252710) applied from ebs
,PP_SIT_NAME =>'FUJ_Leave_Details' --select ID_FLEX_STRUCTURE_CODE from FND_ID_FLEX_STRUCTURES_VL where id_flex_num = <ur sit_flex_id_num)
,PP_SEGMENT3 =>'2018/12/20 00:00:00' --updating segment3
,p_activity_id => 345335 --SELECT ACTIVITY_ID FROM HR_API_TRANSACTION_STEPS WHERE TRANSACTION_ID IN(252710) applied from ebs
,PP_PERSON_ANALYSIS_ID => 4670241 --Existing record in per_analysis_criteria
--OUT
,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;
No comments:
Post a Comment