Search This Blog

Monday, April 9, 2018

Apply SIT SSHR Transaction and Initiate seeded AME Workflow from plsql Oracle Apps R12 (30 Segments Parameters)

To update SIT from plsql with AME workflow:
 http://mogalafzal.blogspot.ae/2018/04/update-sit-sshr-with-ame-approval-from.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:

CREATE OR REPLACE Package APPS.FUJ_HR_SIT_WORKFLOW_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_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.FUJ_HR_SIT_WORKFLOW_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);
                out_msg                                      varchar2(1500);
                V_activity_id                                number;
                v_p_responsibility_name               varchar2(1500);
                v_app_id                                     number;
                V_CREATOR_PERSON_DISPLAY_NAME VARCHAR2(200);
             
                 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','%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_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_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;
        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,segment10,segment11,segment12,segment13,segment3)
values
    (ln_analysis_criteria_id,P_ID_FLEX_NUM,'N','Y',sysdate,-1,-1,-1,sysdate
      ,P_segment1 ,P_segment10,P_segment11,P_segment12,P_segment13,P_segment3    
    );
  dbms_output.put_line ('V_transaction_step_id='||V_transaction_step_id);
  --dbms_output.put_line ('V_object_version_number='||V_object_version_number );
    --
  --Start create_transaction_Values
  --
                hr_transaction_api.set_number_value    (v_validate   ,V_transaction_step_id   , v_creator_person_id   ,'P_PERSON_ID'           ,v_selected_person_id,null );
                hr_transaction_api.set_number_value    (v_validate   ,V_transaction_step_id   , v_creator_person_id   ,'P_LOGIN_PERSON_ID' ,v_creator_person_id,null );
                hr_transaction_api.set_varchar2_value  (v_validate   ,V_transaction_step_id   , v_creator_person_id   ,'P_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_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_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:= FUJ_HR_SIT_WORKFLOW_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:= FUJ_HR_SIT_WORKFLOW_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_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');    
               FUJ_HR_SIT_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_SIT_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_SIT_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;
/
----------------------Block to Run

declare
 P_msg                    varchar2(5000);
begin

        begin
                 FUJ_HR_SIT_WORKFLOW_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_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';
                            DBMS_OUTPUT.PUT_LINE(SQLERRM);               
        End;         
     
                                                         
     dbms_output.put_line('P_msg='||P_msg);
     --DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;---------------------------------------------------------------------------

-----Approving from backend testing


create or replace PROCEDURE approve_reject_proc (pAction IN VARCHAR2, --APPROVE/REJECT
pComments IN VARCHAR2, -- User Comments -- Optional
pNotification_id IN NUMBER, -- Notification ID
pStatus OUT VARCHAR2,
pMessage OUT VARCHAR2
)
IS
l_user_name VARCHAR2(250);
BEGIN
BEGIN
SELECT RECIPIENT_ROLE
INTO l_user_name
FROM WF_NOTIFICATIONS
WHERE notification_id = pNotification_id;
EXCEPTION
WHEN OTHERS THEN
l_user_name := NULL;
END;
wf_notification.SETATTRTEXT(pNotification_id,'RESULT',pAction);
wf_notification.Respond(pNotification_id,
pComments,
l_user_name,
pAction);
pStatus := 'S';
pMessage := 'Successfully '||initcap(pAction);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
pStatus := 'E';
pMessage := 'Unexpected error while Approve/Reject the notification: '||SQLERRM;
END approve_reject_proc;

DECLARE
Pstatus_out varchar2(200);
PMessage_out Varchar2(200);
l_notification_id number := :notification_id;--get from the below query
l_approve_reject varchar2(200) := 'REJECTED'; --APPROVED  OR REJECTED
BEGIN
approve_reject_proc(l_approve_reject,'Test Comments',l_notification_id,Pstatus_out, PMessage_out);
dbms_output.put_line('Pstatus ->'||Pstatus_out|| '-PMessage->'||PMessage_out);
exception when others then
dbms_output.put_line(sqlerrm);
END;


SELECT rownum l_sequence ,a.l_notification_id ,a.l_role role ,a.l_action action ,a.l_comments ,to_char(a.l_date,'dd-mm-yyyy hh:mi:ss am')  l_date FROM (
SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname
 (nvl (wn.more_info_role ,wn.recipient_role)) l_role ,
 decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING')) ,
 decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action ,
 na.text_value l_comments ,ias.end_date l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
 ,wf_item_activity_statuses ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
 AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
 AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
 AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
 AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
 AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
 AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
 UNION
 SELECT pah.notification_id l_notification_id ,wf_directory.getroledisplayname (pah.user_name) l_role
 ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action)) l_action ,pah.user_comment l_comments
 ,pah.last_update_date l_date FROM pqh_ss_approval_history pah WHERE pah.transaction_item_type = :p_item_type
 AND pah.transaction_item_key = :p_item_key AND pah.action NOT IN ('APPROVED','REJECTED','SUBMIT' ,'RFC','RESUBMIT','TIMEOUT','QUESTION','ANSWER') 
 UNION 
 SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (wn.recipient_role) l_role
 ,decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING'))
 ,decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action
 ,decode (wn.status ,'CANCELED' ,NULL ,nvl (na.text_value ,
 ( SELECT user_comment FROM wf_comments WHERE notification_id = wn.notification_id AND action = l.lookup_code AND rownum = 1 ))) l_comments
 ,nvl (ias.end_date ,ias.begin_date) l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
 ,wf_item_activity_statuses_h ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
 AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
 AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
 AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
 AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
 AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
 AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
 UNION
 SELECT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (nvl (wn.more_info_role ,wn.recipient_role)) l_role
 ,decode (wn.status ,'CANCELED' ,decode (pah.action ,'TIMEOUT' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))
 ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN'))) ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))) l_action
 ,decode (pah.notification_id ,wn.notification_id ,pah.user_comment ,NULL) l_comments ,pah.last_update_date l_date
 FROM pqh_ss_approval_history pah ,wf_notifications wn
 WHERE pah.transaction_item_type = :p_item_type AND pah.transaction_item_key = :p_item_key
 AND pah.action IN ('TIMEOUT','RESUBMIT') AND wn.group_id = ( SELECT group_id FROM wf_notifications WHERE notification_id = pah.notification_id
 AND rownum = 1 )
 UNION select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role
 -- ,WF_CORE.TRANSLATE(C.ACTION) l_action
 ,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
 ,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES_H IAS, WF_COMMENTS C, wf_notifications wn
 where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id
 and wn.notification_id = c.notification_id and C.ACTION in('QUESTION', 'ANSWER')
 UNION select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role -- ,WF_CORE.TRANSLATE(C.ACTION) l_action
 ,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
 ,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES IAS, WF_COMMENTS C, wf_notifications wn
 where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id and wn.notification_id = c.notification_id
 and C.ACTION in('QUESTION', 'ANSWER')
 UNION SELECT 0 ,wf_directory.getroledisplayname (owner_role) l_role ,wf_core.translate ('SUBMIT')
 ,appr.text_value note ,begin_date l_date
 FROM wf_items i ,wf_item_attribute_values appr
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND i.item_type = appr.item_type (+) AND i.item_key = appr.item_key (+)
 AND appr.name (+) = 'SUBMIT_COMMENTS' ORDER BY l_notification_id ,l_date ) a

   



2 comments:

  1. Hi afzal,
    I need your help. Is there any to restart a completed transaction. Workflow is also completes.

    ReplyDelete
  2. Facing below error while approving document of record workflow.

    Wf_Engine.CompleteActivityInternalName(HRSSA, 279077, BLOCK, COMPLETE) Wf_Engine_Util.Function_Call(hr_dor_review_ss.complete_activity, HRSSA, 279077, 413456, RUN)

    ReplyDelete