Search This Blog

Monday, April 16, 2018

Apply Absence SSHR Transaction and Initiate AME Workflow from plsql Oracle Apps R12

 here are the steps
 1. create views
 2. create procedure for xml
3. create package for create absence txn sshr.
4.  run the sample code and check the tables hr_api_transactions and columns like transaction_document
5. check the query to know the notification_id for approval from backend.
6. approve from backend using package and redo the same step 5, until complete.


1. views

1.1  --just check the function id and replace in below view.


CREATE OR REPLACE FORCE VIEW APPS.FUJ_SSHR_PERSINFO
(
    LOGINWORKERNUMBER,
    PRSNNAME,
    PRSNJOBID,
    PRSNID,
    PRSNPOSITIONID,
    PRSNBGID,
    PRSNJOBNAME,
    PRSNASSIGNMENTID,
    PRSNGRADEID,
    LOGINPRSNID,
    ASGSTARTDATE,
    PRSNLOCATIONID,
    PRSNMGRID,
    EFFECTIVEDATE,
    PCALLEDID,
    PRSNORGANIZATIONID,
    PRSNPOSITIONNAME,
    PRSNPAYROLLID,
    PRSNMGRNAME,
    DATENOTIFICATION,
    LASTUPDATEDATE,
    CREATIONDATE
)
AS
    SELECT papf.employee_number
               loginworkernumber,
           full_name
               prsnname,
           paaf.job_id
               prsnjobid,
           papf.person_id
               prsnid,
           paaf.position_id
               prsnpositionid,
           papf.business_group_id
               prsnbgid,
           pj.NAME
               prsnjobname,
           paaf.assignment_id
               prsnassignmentid,
           paaf.grade_id
               prsngradeid,
           paaf.person_id
               loginprsnid,
           TO_CHAR (paaf.effective_start_date, 'yyyy-mm-dd')
               asgstartdate,
           paaf.location_id
               prsnlocationid,
           supervisor_id
               prsnmgrid,
           TO_CHAR (TRUNC (SYSDATE), 'yyyy-mm-dd')
               effectivedate,
           12238
               pcalledid                                         --function_id
                        ,
           paaf.organization_id
               prsnorganizationid,
           pp.NAME
               prsnpositionname,
           paaf.payroll_id
               prsnpayrollid,
           (SELECT full_name
              FROM per_all_people_f papf2
             WHERE     papf2.person_id = paaf.supervisor_id
                   AND TRUNC (SYSDATE) BETWEEN papf2.effective_start_date
                                           AND papf2.effective_end_date)
               prsnmgrname,
           TO_CHAR (SYSDATE, 'yyyy-mm-dd hh:mi:ss')
               datenotification                 --format 2018-04-14 10:44:19.0
                               ,
           TO_CHAR (SYSDATE, 'yyyy-mm-dd hh:mi:ss')
               lastupdatedate,
           TO_CHAR (SYSDATE, 'yyyy-mm-dd hh:mi:ss')
               creationdate
      --,'000100000004'||RAWTOHEX(PER_ABSENCE_ATTENDANCES_S.CURRVAL) CDATA
      FROM per_all_people_f       papf,
           per_all_assignments_f  paaf,
           per_jobs               pj,
           per_positions          pp
     WHERE     papf.person_id = paaf.person_id
           AND pj.job_id(+) = paaf.job_id
           AND pp.position_id(+) = paaf.position_id
           AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                   AND papf.effective_end_date
           AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                   AND paaf.effective_end_date
           AND primary_flag = 'Y'
--AND papf.person_id = 26876;
;

-------------------------------------------------------------------------------------------------------------------------------------------------
1.2  --just check the function id and replace in below view.

/* Formatted on 9/23/2018 1:23:57 PM (QP5 v5.326) */
CREATE OR REPLACE FORCE VIEW APPS.FUJ_SSHR_PERSINFO_MGR
(
    PRSNNAME,
    PRSNJOBID,
    PRSNID,
    PRSNPOSITIONID,
    PRSNBGID,
    PRSNJOBNAME,
    PRSNASSIGNMENTID,
    PRSNGRADEID,
    LOGINPRSNID,
    ASGSTARTDATE,
    PRSNLOCATIONID,
    PRSNMGRID,
    EFFECTIVEDATE,
    PCALLEDID,
    PRSNORGANIZATIONID,
    PRSNPOSITIONNAME,
    PRSNPAYROLLID,
    PRSNMGRNAME,
    DATENOTIFICATION,
    LOGINWORKERNUMBER,
    LASTUPDATEDATE,
    CREATIONDATE,
    LOGINPRSNMGRNAME,
    LOGINPRSNMGRID
)
AS
    SELECT full_name
               prsnname,
           paaf.job_id
               prsnjobid,
           papf.person_id
               prsnid,
           paaf.position_id
               prsnpositionid,
           papf.business_group_id
               prsnbgid,
           pj.NAME
               prsnjobname,
           paaf.assignment_id
               prsnassignmentid,
           paaf.grade_id
               prsngradeid,
           paaf.person_id
               loginprsnid,
           TO_CHAR (paaf.effective_start_date, 'yyyy-mm-dd')
               asgstartdate,
           paaf.location_id
               prsnlocationid,
           supervisor_id
               prsnmgrid,
           TO_CHAR (TRUNC (SYSDATE), 'yyyy-mm-dd')
               effectivedate,
           12238
               pcalledid,
           paaf.organization_id
               prsnorganizationid,
           pp.NAME
               prsnpositionname,
           paaf.payroll_id
               prsnpayrollid,
           (SELECT full_name
              FROM per_all_people_f papf2
             WHERE     papf2.person_id = paaf.supervisor_id
                   AND TRUNC (SYSDATE) BETWEEN papf2.effective_start_date
                                           AND papf2.effective_end_date)
               prsnmgrname,
           TO_CHAR (SYSDATE, 'yyyy-mm-dd hh:mi:ss')
               datenotification,
           (SELECT papf2.employee_number
              FROM per_all_people_f papf2
             WHERE     papf2.person_id = paaf.supervisor_id
                   AND TRUNC (SYSDATE) BETWEEN papf2.effective_start_date
                                           AND papf2.effective_end_date
                   AND ROWNUM = 1)
               loginworkernumber,
           TO_CHAR (SYSDATE, 'yyyy-mm-dd hh:mi:ss')
               lastupdatedate,
           TO_CHAR (SYSDATE, 'yyyy-mm-dd hh:mi:ss')
               creationdate,
           (SELECT full_name
              FROM per_all_people_f papf3
             WHERE     papf3.person_id =
                       (SELECT supervisor_id
                          FROM per_all_assignments_f paaf4
                         WHERE     paaf4.person_id = paaf.supervisor_id
                               AND TRUNC (SYSDATE) BETWEEN paaf4.effective_start_date
                                                       AND paaf4.effective_end_date)
                   AND TRUNC (SYSDATE) BETWEEN papf3.effective_start_date
                                           AND papf3.effective_end_date)
               loginprsnmgrname,
           (SELECT papf3.person_id
              FROM per_all_people_f papf3
             WHERE     papf3.person_id =
                       (SELECT supervisor_id
                          FROM per_all_assignments_f paaf4
                         WHERE     paaf4.person_id = paaf.supervisor_id
                               AND TRUNC (SYSDATE) BETWEEN paaf4.effective_start_date
                                                       AND paaf4.effective_end_date)
                   AND TRUNC (SYSDATE) BETWEEN papf3.effective_start_date
                                           AND papf3.effective_end_date)
               loginprsnmgrid
      FROM per_all_people_f       papf,
           per_all_assignments_f  paaf,
           per_jobs               pj,
           per_positions          pp
     WHERE     papf.person_id = paaf.person_id
           AND pj.job_id(+) = paaf.job_id
           AND pp.position_id(+) = paaf.position_id
           AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                   AND papf.effective_end_date
           AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                   AND paaf.effective_end_date
           AND primary_flag = 'Y';

-------------------------------------------------------------------------------------------------------------------------------------------------

2.

2.1 Procedure change KFF 'FUJ_People_Group_Flexfield' to your Flexfield value
     and 'AED' currency to your local currency.
   
CREATE OR REPLACE PROCEDURE APPS.P_XML_TRANSACTION_DOCUMENT2
(
p_TransactionId NUMBER, --1
p_LoginPrsnId NUMBER ,
p_PrsnId    number,
p_pCalledId  number ,
p_AbsenceAttdId  number,   --PER_ABSENCE_ATTENDANCES_S.NEXTVAL
P_TransactionRefId  number , --PER_ABSENCE_ATTENDANCES_S.CURRVAL
p_AbsenceAction  varchar2 ,
p_AbsenceAttendanceTypeId number,
p_AbsAttendanceReasonId Number default null,-->1061</
p_AbsenceDays  number ,
p_DateStart  varchar2 ,
p_DateEnd  varchar2 ,
p_comments varchar2 default null,
p_userid number ,
P_xml out clob,
p_msg out varchar2
)
AS
--p_TransactionId number :=  12345;
--p_LoginPrsnId  number :=  26876;
--p_PrsnId    number :=  26876;
--p_pCalledId  number :=  12238;
--p_AbsenceAttdId   :=  PER_ABSENCE_ATTENDANCES_S.NEXTVAL ;--PER_ABSENCE_ATTENDANCES_S.CURRVAL  TransactionRefId,
--P_TransactionRefId   :=  PER_ABSENCE_ATTENDANCES_S.CURRVAL;
--p_AbsenceAction  varchar2(200) :=  'CreateMode';
--p_AbsenceAttendanceTypeId  number :=  64;
--p_AbsenceDays  number :=  1;
--p_DateStart  varchar2(200) :=  '2018-08-03';
--p_DateEnd  varchar2(200) :=  '2018-08-03' ;
--p_DateNotification  varchar2(200) := to_char(sysdate,'yyyy-mm-dd hh:mi:ss');

v_TransactionRefId number := p_AbsenceAttdId;
v_userId number := p_userid;
v_sessionid number := fnd_global.session_id;


   l_xmltype XMLTYPE;
   l_domdoc dbms_xmldom.DOMDocument;
   l_root_node dbms_xmldom.DOMNode;

   l_supp_numEle    dbms_xmldom.DOMElement;
   l_supp_name_element   dbms_xmldom.DOMElement;
  
   l_cnode_element2    dbms_xmldom.DOMElement;--dbms_xmldom.DOMProcessingInstruction;--
   l_cnode_node2        dbms_xmldom.DOMNode;

   l_supp_num_node       dbms_xmldom.DOMNode;
   l_supp_name_node      dbms_xmldom.DOMNode;

   l_supp_num_tnode      dbms_xmldom.DOMNode;
   l_supp_name_tnode     dbms_xmldom.DOMNode;

   l_supp_num_text       dbms_xmldom.DOMText;
   l_supp_name_text      dbms_xmldom.DOMText;

   l_TransCtx_element    dbms_xmldom.DOMElement;
   l_TransCtx_node       dbms_xmldom.DOMNode;
  
   l_TransCache_element    dbms_xmldom.DOMElement;
   l_TransCache_node       dbms_xmldom.DOMNode;
  
   l_AM_element    dbms_xmldom.DOMElement;
   l_AM_node       dbms_xmldom.DOMNode;
  
   l_EoApiMapEO_element    dbms_xmldom.DOMElement;
   l_EoApiMapEO_node       dbms_xmldom.DOMNode; 
   l_EoApiMapEO_name_text dbms_xmldom.DOMText;
   l_EoApiMapEO_name_tnode  dbms_xmldom.DOMNode;
  
   l_cd_element    dbms_xmldom.DOMElement;
   l_cd_node       dbms_xmldom.DOMNode;
  
   l_TXN_element    dbms_xmldom.DOMElement;
   l_TXN_node       dbms_xmldom.DOMNode;
  
   l_EO_element    dbms_xmldom.DOMElement;
   l_EO_node       dbms_xmldom.DOMNode;
  
   l_EO2_element    dbms_xmldom.DOMElement;
   l_EO2_node       dbms_xmldom.DOMNode;
  
   l_TransactionsEORow_element    dbms_xmldom.DOMElement;
   l_TransactionsEORow_node       dbms_xmldom.DOMNode;
  
  
   l_EoApiMap_element    dbms_xmldom.DOMElement;
   l_EoApiMap_node       dbms_xmldom.DOMNode;
  
  
  
   l_PerAbsAttendEORow_element    dbms_xmldom.DOMElement;
   l_PerAbsAttendEORow_node       dbms_xmldom.DOMNode;
  
  
   l_sup_node            dbms_xmldom.DOMNode;
   l_Transaction_node       dbms_xmldom.DOMNode;
   l_sup_element         dbms_xmldom.DOMElement;
  
   l_EOCDATA_name_element       dbms_xmldom.DOMCDataSection;
   l_EOCDATA_name_node       dbms_xmldom.DOMNode;
   l_EOCDATA_name_tnode      dbms_xmldom.DOMNode;
   l_EOCDATA_name_text       dbms_xmldom.DOMText;
  
   l_EOCDATA2_name_element       dbms_xmldom.DOMCDataSection;
   l_EOCDATA2_name_node       dbms_xmldom.DOMNode;
   l_EOCDATA2_name_tnode      dbms_xmldom.DOMNode;
   l_EOCDATA2_name_text       dbms_xmldom.DOMText;
  
   l_PrsnJobName_numEle    dbms_xmldom.DOMElement;
   l_PrsnJobName_num_node       dbms_xmldom.DOMNode;
   l_PrsnJobName_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnJobName_num_text       dbms_xmldom.DOMText;
  
      l_PrsnAsgFlag_numEle    dbms_xmldom.DOMElement;
   l_PrsnAsgFlag_num_node       dbms_xmldom.DOMNode;
   l_PrsnAsgFlag_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnAsgFlag_num_text       dbms_xmldom.DOMText;
  
      l_LoginPrsnNpwFlag_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnNpwFlag_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnNpwFlag_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnNpwFlag_num_text       dbms_xmldom.DOMText;
  
      l_FyiDetails_numEle    dbms_xmldom.DOMElement;
   l_FyiDetails_num_node       dbms_xmldom.DOMNode;
   l_FyiDetails_num_tnode      dbms_xmldom.DOMNode;
   l_FyiDetails_num_text       dbms_xmldom.DOMText;
  
      l_PrsnBgId_numEle    dbms_xmldom.DOMElement;
   l_PrsnBgId_num_node       dbms_xmldom.DOMNode;
   l_PrsnBgId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnBgId_num_text       dbms_xmldom.DOMText;
  
   l_EmpGen_numEle    dbms_xmldom.DOMElement;
   l_EmpGen_num_node       dbms_xmldom.DOMNode;
   l_EmpGen_num_tnode      dbms_xmldom.DOMNode;
   l_EmpGen_num_text       dbms_xmldom.DOMText;
  
  
      l_PrsnNpwFlag_numEle    dbms_xmldom.DOMElement;
   l_PrsnNpwFlag_num_node       dbms_xmldom.DOMNode;
   l_PrsnNpwFlag_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnNpwFlag_num_text       dbms_xmldom.DOMText;
  

      l_ItemType_numEle    dbms_xmldom.DOMElement;
   l_ItemType_num_node       dbms_xmldom.DOMNode;
   l_ItemType_num_tnode      dbms_xmldom.DOMNode;
   l_ItemType_num_text       dbms_xmldom.DOMText;
  
      l_PrsnJobId_numEle    dbms_xmldom.DOMElement;
   l_PrsnJobId_num_node       dbms_xmldom.DOMNode;
   l_PrsnJobId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnJobId_num_text       dbms_xmldom.DOMText;
  
      l_AsgStartDate_numEle    dbms_xmldom.DOMElement;
   l_AsgStartDate_num_node       dbms_xmldom.DOMNode;
   l_AsgStartDate_num_tnode      dbms_xmldom.DOMNode;
   l_AsgStartDate_num_text       dbms_xmldom.DOMText;
  
      l_PrsnGradeId_numEle    dbms_xmldom.DOMElement;
   l_PrsnGradeId_num_node       dbms_xmldom.DOMNode;
   l_PrsnGradeId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnGradeId_num_text       dbms_xmldom.DOMText;
  
  
      l_txnRefId_numEle    dbms_xmldom.DOMElement;
   l_txnRefId_num_node       dbms_xmldom.DOMNode;
   l_txnRefId_num_tnode      dbms_xmldom.DOMNode;
   l_txnRefId_num_text       dbms_xmldom.DOMText;
  
  
      l_LoginPrsnLegCode_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnLegCode_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnLegCode_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnLegCode_num_text       dbms_xmldom.DOMText;
  
      l_PrsnMgrId_numEle    dbms_xmldom.DOMElement;
   l_PrsnMgrId_num_node       dbms_xmldom.DOMNode;
   l_PrsnMgrId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnMgrId_num_text       dbms_xmldom.DOMText;
  
      l_PrsnAssignmentId_numEle    dbms_xmldom.DOMElement;
   l_PrsnAssignmentId_num_node       dbms_xmldom.DOMNode;
   l_PrsnAssignmentId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnAssignmentId_num_text       dbms_xmldom.DOMText;
  
  
      l_PrsnLegCode_numEle    dbms_xmldom.DOMElement;
   l_PrsnLegCode_num_node       dbms_xmldom.DOMNode;
   l_PrsnLegCode_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnLegCode_num_text       dbms_xmldom.DOMText;
  
  
      l_LoginPrsnId_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnId_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnId_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnId_num_text       dbms_xmldom.DOMText;
  
  
      l_LogPrsnCon_numEle    dbms_xmldom.DOMElement;
   l_LogPrsnCon_num_node       dbms_xmldom.DOMNode;
   l_LogPrsnCon_num_tnode      dbms_xmldom.DOMNode;
   l_LogPrsnCon_num_text       dbms_xmldom.DOMText;
  
  
      l_pNtfSubMsg_numEle    dbms_xmldom.DOMElement;
   l_pNtfSubMsg_num_node       dbms_xmldom.DOMNode;
   l_pNtfSubMsg_num_tnode      dbms_xmldom.DOMNode;
   l_pNtfSubMsg_num_text       dbms_xmldom.DOMText;
  
      l_ProductCode_numEle    dbms_xmldom.DOMElement;
   l_ProductCode_num_node       dbms_xmldom.DOMNode;
   l_ProductCode_num_tnode      dbms_xmldom.DOMNode;
   l_ProductCode_num_text       dbms_xmldom.DOMText;
  
      l_EffectiveDate_numEle    dbms_xmldom.DOMElement;
   l_EffectiveDate_num_node       dbms_xmldom.DOMNode;
   l_EffectiveDate_num_tnode      dbms_xmldom.DOMNode;
   l_EffectiveDate_num_text       dbms_xmldom.DOMText;
  
  
   l_SSHR_WF_BASED_numEle    dbms_xmldom.DOMElement;
   l_SSHR_WF_BASED_num_node       dbms_xmldom.DOMNode;
   l_SSHR_WF_BASED_num_tnode      dbms_xmldom.DOMNode;
   l_SSHR_WF_BASED_num_text       dbms_xmldom.DOMText;   
  
   l_HeaderType_numEle    dbms_xmldom.DOMElement;
   l_HeaderType_num_node       dbms_xmldom.DOMNode;
   l_HeaderType_num_tnode      dbms_xmldom.DOMNode;
   l_HeaderType_num_text       dbms_xmldom.DOMText;   
  

   l_PrsnBgCurrencyCode_numEle    dbms_xmldom.DOMElement;
   l_PrsnBgCurrencyCode_num_node       dbms_xmldom.DOMNode;
   l_PrsnBgCurrencyCode_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnBgCurrencyCode_num_text       dbms_xmldom.DOMText;   
  
  
   l_LoginWorkerNumber_numEle    dbms_xmldom.DOMElement;
   l_LoginWorkerNumber_num_node       dbms_xmldom.DOMNode;
   l_LoginWorkerNumber_num_tnode      dbms_xmldom.DOMNode;
   l_LoginWorkerNumber_num_text       dbms_xmldom.DOMText;   

   l_TxnStatus_numEle    dbms_xmldom.DOMElement;
   l_TxnStatus_num_node       dbms_xmldom.DOMNode;
   l_TxnStatus_num_tnode      dbms_xmldom.DOMNode;
   l_TxnStatus_num_text       dbms_xmldom.DOMText;   

   l_PrsnPositionId_numEle    dbms_xmldom.DOMElement;
   l_PrsnPositionId_num_node       dbms_xmldom.DOMNode;
   l_PrsnPositionId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnPositionId_num_text       dbms_xmldom.DOMText;   
  

   l_prsnOrgId_numEle    dbms_xmldom.DOMElement;
   l_prsnOrgId_num_node       dbms_xmldom.DOMNode;
   l_prsnOrgId_num_tnode      dbms_xmldom.DOMNode;
   l_prsnOrgId_num_text       dbms_xmldom.DOMText;   
  
  
   l_PrsnMgrName_numEle    dbms_xmldom.DOMElement;
   l_PrsnMgrName_num_node       dbms_xmldom.DOMNode;
   l_PrsnMgrName_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnMgrName_num_text       dbms_xmldom.DOMText;   

   l_PrsnEmpFlag_numEle    dbms_xmldom.DOMElement;
   l_PrsnEmpFlag_num_node       dbms_xmldom.DOMNode;
   l_PrsnEmpFlag_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnEmpFlag_num_text       dbms_xmldom.DOMText;   
  
  

   l_PrsnPositionName_numEle    dbms_xmldom.DOMElement;
   l_PrsnPositionName_num_node       dbms_xmldom.DOMNode;
   l_PrsnPositionName_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnPositionName_num_text       dbms_xmldom.DOMText;   
  
   l_PrsnContextSet_numEle    dbms_xmldom.DOMElement;
   l_PrsnContextSet_num_node       dbms_xmldom.DOMNode;
   l_PrsnContextSet_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnContextSet_num_text       dbms_xmldom.DOMText;   

   l_LoginPrsnMgrName_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnMgrName_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnMgrName_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnMgrName_num_text       dbms_xmldom.DOMText;  
  
  
   l_PrsnKfstrCode_numEle    dbms_xmldom.DOMElement;
   l_PrsnKfstrCode_num_node       dbms_xmldom.DOMNode;
   l_PrsnKfstrCode_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnKfstrCode_num_text       dbms_xmldom.DOMText;   
  
  
   l_PrsnLocationId_numEle    dbms_xmldom.DOMElement;
   l_PrsnLocationId_num_node       dbms_xmldom.DOMNode;
   l_PrsnLocationId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnLocationId_num_text       dbms_xmldom.DOMText;   

   l_TransactionId_numEle    dbms_xmldom.DOMElement;
   l_TransactionId_num_node       dbms_xmldom.DOMNode;
   l_TransactionId_num_tnode      dbms_xmldom.DOMNode;
   l_TransactionId_num_text       dbms_xmldom.DOMText;   
  
   l_ReviewTemRNAtt_numEle    dbms_xmldom.DOMElement;
   l_ReviewTemRNAtt_num_node       dbms_xmldom.DOMNode;
   l_ReviewTemRNAtt_num_tnode      dbms_xmldom.DOMNode;
   l_ReviewTemRNAtt_num_text       dbms_xmldom.DOMText;   
   
   l_pCalledId_numEle    dbms_xmldom.DOMElement;
   l_pCalledId_num_node       dbms_xmldom.DOMNode;
   l_pCalledId_num_tnode      dbms_xmldom.DOMNode;
   l_pCalledId_num_text       dbms_xmldom.DOMText;   

   l_LoginPrsnType_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnType_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnType_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnType_num_text       dbms_xmldom.DOMText;   
  
   l_LoginPrsnEmpFlag_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnEmpFlag_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnEmpFlag_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnEmpFlag_num_text       dbms_xmldom.DOMText;
      

   l_SS_RevFLMode_numEle   dbms_xmldom.DOMElement;
   l_SS_RevFLMode_num_node       dbms_xmldom.DOMNode;
   l_SS_RevFLMode_num_tnode      dbms_xmldom.DOMNode;
   l_SS_RevFLMode_num_text       dbms_xmldom.DOMText;  

   l_pAMEAppId_numEle   dbms_xmldom.DOMElement;
   l_pAMEAppId_num_node       dbms_xmldom.DOMNode;
   l_pAMEAppId_num_tnode      dbms_xmldom.DOMNode;
   l_pAMEAppId_num_text       dbms_xmldom.DOMText;  
  
  
   l_PrsnName_numEle   dbms_xmldom.DOMElement;
   l_PrsnName_num_node       dbms_xmldom.DOMNode;
   l_PrsnName_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnName_num_text       dbms_xmldom.DOMText;   

   l_PrsnPayrollId_numEle   dbms_xmldom.DOMElement;
   l_PrsnPayrollId_num_node       dbms_xmldom.DOMNode;
   l_PrsnPayrollId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnPayrollId_num_text       dbms_xmldom.DOMText;   
  
   l_ProcessName_numEle   dbms_xmldom.DOMElement;
   l_ProcessName_num_node       dbms_xmldom.DOMNode;
   l_ProcessName_num_tnode      dbms_xmldom.DOMNode;
   l_ProcessName_num_text       dbms_xmldom.DOMText;   

   l_PerzFunctionName_numEle   dbms_xmldom.DOMElement;
   l_PerzFunctionName_num_node       dbms_xmldom.DOMNode;
   l_PerzFunctionName_num_tnode      dbms_xmldom.DOMNode;
   l_PerzFunctionName_num_text       dbms_xmldom.DOMText;   
  
  
  
   l_LoginPrsnBgId_numEle   dbms_xmldom.DOMElement;
   l_LoginPrsnBgId_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnBgId_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnBgId_num_text       dbms_xmldom.DOMText;   

   l_pCalledFrom_numEle   dbms_xmldom.DOMElement;
   l_pCalledFrom_num_node       dbms_xmldom.DOMNode;
   l_pCalledFrom_num_tnode      dbms_xmldom.DOMNode;
   l_pCalledFrom_num_text       dbms_xmldom.DOMText;  
  
   l_TxnIdentifier_numEle   dbms_xmldom.DOMElement;
   l_TxnIdentifier_num_node       dbms_xmldom.DOMNode;
   l_TxnIdentifier_num_tnode      dbms_xmldom.DOMNode;
   l_TxnIdentifier_num_text       dbms_xmldom.DOMText;   
  
   l_TxnRefTab_numEle   dbms_xmldom.DOMElement;
   l_TxnRefTab_num_node       dbms_xmldom.DOMNode;
   l_TxnRefTab_num_tnode      dbms_xmldom.DOMNode;
   l_TxnRefTab_num_text       dbms_xmldom.DOMText;   
  
   L_PerzLocCode_numEle   dbms_xmldom.DOMElement;
   L_PerzLocCode_num_node       dbms_xmldom.DOMNode;
   L_PerzLocCode_num_tnode      dbms_xmldom.DOMNode;
   L_PerzLocCode_num_text       dbms_xmldom.DOMText;   
  
   l_RelaunchFunction_numEle   dbms_xmldom.DOMElement;
   l_RelaunchFunction_num_node       dbms_xmldom.DOMNode;
   l_RelaunchFunction_num_tnode      dbms_xmldom.DOMNode;
   l_RelaunchFunction_num_text       dbms_xmldom.DOMText;   

   l_pAMETranType_numEle   dbms_xmldom.DOMElement;
   l_pAMETranType_num_node       dbms_xmldom.DOMNode;
   l_pAMETranType_num_tnode      dbms_xmldom.DOMNode;
   l_pAMETranType_num_text       dbms_xmldom.DOMText;   

   l_pApprovalReqd_numEle   dbms_xmldom.DOMElement;
   l_pApprovalReqd_num_node       dbms_xmldom.DOMNode;
   l_pApprovalReqd_num_tnode      dbms_xmldom.DOMNode;
   l_pApprovalReqd_num_text       dbms_xmldom.DOMText; 
  
   l_NtfAttachAttr_numEle   dbms_xmldom.DOMElement;
   l_NtfAttachAttr_num_node       dbms_xmldom.DOMNode;
   l_NtfAttachAttr_num_tnode      dbms_xmldom.DOMNode;
   l_NtfAttachAttr_num_text       dbms_xmldom.DOMText;   
  
   l_TransactionType_numEle   dbms_xmldom.DOMElement;
   l_TransactionType_num_node       dbms_xmldom.DOMNode;
   l_TransactionType_num_tnode      dbms_xmldom.DOMNode;
   l_TransactionType_num_text       dbms_xmldom.DOMText; 

   l_PrsnId_numEle   dbms_xmldom.DOMElement;
   l_PrsnId_num_node       dbms_xmldom.DOMNode;
   l_PrsnId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnId_num_text       dbms_xmldom.DOMText;   

   l_CreatorPrsnId_numEle   dbms_xmldom.DOMElement;
   l_CreatorPrsnId_num_node       dbms_xmldom.DOMNode;
   l_CreatorPrsnId_num_tnode      dbms_xmldom.DOMNode;
   l_CreatorPrsnId_num_text       dbms_xmldom.DOMText;   

   l_LoginPrsnName_numEle   dbms_xmldom.DOMElement;
   l_LoginPrsnName_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnName_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnName_num_text       dbms_xmldom.DOMText;   

   l_PerzOrgId_numEle   dbms_xmldom.DOMElement;
   l_PerzOrgId_num_node       dbms_xmldom.DOMNode;
   l_PerzOrgId_num_tnode      dbms_xmldom.DOMNode;
   l_PerzOrgId_num_text       dbms_xmldom.DOMText;   

   l_PrsnType_numEle   dbms_xmldom.DOMElement;
   l_PrsnType_num_node       dbms_xmldom.DOMNode;
   l_PrsnType_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnType_num_text       dbms_xmldom.DOMText;   
  
   l_AttachCheck_numEle   dbms_xmldom.DOMElement;
   l_AttachCheck_num_node       dbms_xmldom.DOMNode;
   l_AttachCheck_num_tnode      dbms_xmldom.DOMNode;
   l_AttachCheck_num_text       dbms_xmldom.DOMText;   
  
   l_LoginPrsnMgrId_numEle   dbms_xmldom.DOMElement;
   l_LoginPrsnMgrId_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnMgrId_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnMgrId_num_text       dbms_xmldom.DOMText; 
  
   l_prsnSecGrpId_numEle   dbms_xmldom.DOMElement;
   l_prsnSecGrpId_num_node       dbms_xmldom.DOMNode;
   l_prsnSecGrpId_num_tnode      dbms_xmldom.DOMNode;
   l_prsnSecGrpId_num_text       dbms_xmldom.DOMText; 
  
          l_AbsenceAction_numEle    dbms_xmldom.DOMElement;
   l_AbsenceAction_num_node       dbms_xmldom.DOMNode;
   l_AbsenceAction_num_tnode      dbms_xmldom.DOMNode;
   l_AbsenceAction_num_text       dbms_xmldom.DOMText;
   
   l_AbsenceAttdId_numEle    dbms_xmldom.DOMElement;
   l_AbsenceAttdId_num_node       dbms_xmldom.DOMNode;
   l_AbsenceAttdId_num_tnode      dbms_xmldom.DOMNode;
   l_AbsenceAttdId_num_text       dbms_xmldom.DOMText;

   l_AbsAttendReaId_numEle    dbms_xmldom.DOMElement;
   l_AbsAttendReaId_num_node       dbms_xmldom.DOMNode;
   l_AbsAttendReaId_num_tnode      dbms_xmldom.DOMNode;
   l_AbsAttendReaId_num_text       dbms_xmldom.DOMText;

--p_AbsAttendanceReasonId

   l_OVN_numEle    dbms_xmldom.DOMElement;
   l_OVN_num_node       dbms_xmldom.DOMNode;
   l_OVN_num_tnode      dbms_xmldom.DOMNode;
   l_OVN_num_text       dbms_xmldom.DOMText;
  
   l_Comments_numEle    dbms_xmldom.DOMElement;
   l_Comments_num_node       dbms_xmldom.DOMNode;
   l_Comments_num_tnode      dbms_xmldom.DOMNode;
   l_Comments_num_text       dbms_xmldom.DOMText;
  
   l_LinkAbsIdAbsEO_numEle dbms_xmldom.DOMElement;
   l_LinkAbsIdAbsEO_num_node  dbms_xmldom.DOMNode; 

  
   l_CreationDate_numEle    dbms_xmldom.DOMElement;
   l_CreationDate_num_node       dbms_xmldom.DOMNode;
   l_CreationDate_num_tnode      dbms_xmldom.DOMNode;
   l_CreationDate_num_text       dbms_xmldom.DOMText;  

  
   l_CreatedBy_numEle    dbms_xmldom.DOMElement;
   l_CreatedBy_num_node       dbms_xmldom.DOMNode;
   l_CreatedBy_num_tnode      dbms_xmldom.DOMNode;
   l_CreatedBy_num_text       dbms_xmldom.DOMText;
     

  
   l_LastUpdateLogin_numEle    dbms_xmldom.DOMElement;
   l_LastUpdateLogin_num_node       dbms_xmldom.DOMNode;
   l_LastUpdateLogin_num_tnode      dbms_xmldom.DOMNode;
   l_LastUpdateLogin_num_text       dbms_xmldom.DOMText;
     

  
   l_LastUpdatedBy_numEle    dbms_xmldom.DOMElement;
   l_LastUpdatedBy_num_node       dbms_xmldom.DOMNode;
   l_LastUpdatedBy_num_tnode      dbms_xmldom.DOMNode;
   l_LastUpdatedBy_num_text       dbms_xmldom.DOMText;  
     

  
   l_LastUpdateDate_numEle    dbms_xmldom.DOMElement;
   l_LastUpdateDate_num_node       dbms_xmldom.DOMNode;
   l_LastUpdateDate_num_tnode      dbms_xmldom.DOMNode;
   l_LastUpdateDate_num_text       dbms_xmldom.DOMText;

  
   l_TimeProjSt_numEle    dbms_xmldom.DOMElement;
   l_TimeProjSt_num_node       dbms_xmldom.DOMNode;
   l_TimeProjSt_num_tnode      dbms_xmldom.DOMNode;
   l_TimeProjSt_num_text       dbms_xmldom.DOMText; 

  
   l_TimeProjEndnumEle    dbms_xmldom.DOMElement;
   l_TimeProjEndnum_node       dbms_xmldom.DOMNode;
   l_TimeProjEndnum_tnode      dbms_xmldom.DOMNode;
   l_TimeProjEndnum_text       dbms_xmldom.DOMText;
     

  
   l_DateStart_numEle    dbms_xmldom.DOMElement;
   l_DateStart_num_node       dbms_xmldom.DOMNode;
   l_DateStart_num_tnode      dbms_xmldom.DOMNode;
   l_DateStart_num_text       dbms_xmldom.DOMText;
     

  
   l_DatePrjStrt_numEle    dbms_xmldom.DOMElement;
   l_DatePrjStrt_num_node       dbms_xmldom.DOMNode;
   l_DatePrjStrt_num_tnode      dbms_xmldom.DOMNode;
   l_DatePrjStrt_num_text       dbms_xmldom.DOMText;
     

  
   l_DateProjEnd_numEle    dbms_xmldom.DOMElement;
   l_DateProjEnd_num_node       dbms_xmldom.DOMNode;
   l_DateProjEnd_num_tnode      dbms_xmldom.DOMNode;
   l_DateProjEnd_num_text       dbms_xmldom.DOMText;
     

  
   l_DateNoti_numEle    dbms_xmldom.DOMElement;
   l_DateNoti_num_node       dbms_xmldom.DOMNode;
   l_DateNoti_num_tnode      dbms_xmldom.DOMNode;
   l_DateNoti_num_text       dbms_xmldom.DOMText;
     

  
   l_DateEnd_numEle    dbms_xmldom.DOMElement;
   l_DateEnd_num_node       dbms_xmldom.DOMNode;
   l_DateEnd_num_tnode      dbms_xmldom.DOMNode;
   l_DateEnd_num_text       dbms_xmldom.DOMText;

     

  
   l_AbsenceHours_numEle    dbms_xmldom.DOMElement;
   l_AbsenceHours_num_node       dbms_xmldom.DOMNode;
   l_AbsenceHours_num_tnode      dbms_xmldom.DOMNode;
   l_AbsenceHours_num_text       dbms_xmldom.DOMText;
     

  
   l_AbsenceDays_numEle    dbms_xmldom.DOMElement;
   l_AbsenceDays_num_node       dbms_xmldom.DOMNode;
   l_AbsenceDays_num_tnode      dbms_xmldom.DOMNode;
   l_AbsenceDays_num_text       dbms_xmldom.DOMText;
     

  
   l_PersonId_numEle    dbms_xmldom.DOMElement;
   l_PersonId_num_node       dbms_xmldom.DOMNode;
   l_PersonId_num_tnode      dbms_xmldom.DOMNode;
   l_PersonId_num_text       dbms_xmldom.DOMText;
     

  
   l_AbsAttTypeId_numEle    dbms_xmldom.DOMElement;
   l_AbsAttTypeId_num_node       dbms_xmldom.DOMNode;
   l_AbsAttTypeId_num_tnode      dbms_xmldom.DOMNode;
   l_AbsAttTypeId_num_text       dbms_xmldom.DOMText;
   
       l_BusinessGroupId_numEle    dbms_xmldom.DOMElement;
   l_BusinessGroupId_num_node       dbms_xmldom.DOMNode;
   l_BusinessGroupId_num_tnode      dbms_xmldom.DOMNode;
   l_BusinessGroupId_num_text       dbms_xmldom.DOMText;
   
   l_AbsAttId_numEle    dbms_xmldom.DOMElement;
   l_AbsAttId_num_node       dbms_xmldom.DOMNode;
   l_AbsAttId_num_tnode      dbms_xmldom.DOMNode;
   l_AbsAttId_num_text       dbms_xmldom.DOMText;

BEGIN

   -- Create an empty XML document
   l_domdoc := dbms_xmldom.newDomDocument;

   -- Create a root node
   l_root_node := dbms_xmldom.makeNode(l_domdoc);

   -- Create a new Supplier Node and add it to the root node
   --l_sup_node := dbms_xmldom.appendChild( l_root_node , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'SUPPLIER_ADDRESS')));
   l_Transaction_node := dbms_xmldom.appendChild( l_root_node , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Transaction')));

FOR sup_rec IN (SELECT A.*
,'000100000004'||RAWTOHEX(PER_ABSENCE_ATTENDANCES_S.CURRVAL) CDATA 
FROM FUJ_SSHR_PERSINFO A
where PrsnId = p_PrsnId
)
  LOOP

    -- For each record, create a new Supplier element
    -- and add this new Supplier element to the Supplier Parent node
    l_TransCtx_element := dbms_xmldom.createElement(l_domdoc, 'TransCtx' );
    l_TransCtx_node    := dbms_xmldom.appendChild(l_Transaction_node,dbms_xmldom.makeNode(l_TransCtx_element));


    l_EoApiMap_element := dbms_xmldom.createElement(l_domdoc, 'EoApiMap' );
    l_EoApiMap_node    := dbms_xmldom.appendChild(l_Transaction_node,dbms_xmldom.makeNode(l_EoApiMap_element));


    l_EoApiMapEO_element := dbms_xmldom.createElement(l_domdoc, 'EO' );
    dbms_xmldom.setAttribute(l_EoApiMapEO_element,'Name', 'oracle.apps.per.schema.server.PerAbsenceAttendancesEO');
    l_EoApiMapEO_node    := dbms_xmldom.appendChild(l_EoApiMap_node,dbms_xmldom.makeNode(l_EoApiMapEO_element));
    l_EoApiMapEO_name_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_PERSON_ABSENCE_SWI.PROCESS_API' );
    l_EoApiMapEO_name_tnode   := dbms_xmldom.appendChild(l_EoApiMapEO_node,dbms_xmldom.makeNode(l_EoApiMapEO_name_text));


  

    l_TransCache_element := dbms_xmldom.createElement(l_domdoc, 'TransCache' );
    l_TransCache_node    := dbms_xmldom.appendChild(l_Transaction_node,dbms_xmldom.makeNode(l_TransCache_element));

   
    l_AM_element := dbms_xmldom.createElement(l_domdoc, 'AM' );
    dbms_xmldom.setAttribute(l_AM_element,'MomVer', '1044362310593');
    l_AM_node    := dbms_xmldom.appendChild(l_TransCache_node,dbms_xmldom.makeNode(l_AM_element));
   
    l_cd_element := dbms_xmldom.createElement(l_domdoc, 'cd' );
    l_cd_node    := dbms_xmldom.appendChild(l_AM_node,dbms_xmldom.makeNode(l_cd_element));
   
    l_TXN_element := dbms_xmldom.createElement(l_domdoc, 'TXN' );
    dbms_xmldom.setAttribute(l_TXN_element,'Def', '0');
    dbms_xmldom.setAttribute(l_TXN_element,'New', '0');
    dbms_xmldom.setAttribute(l_TXN_element,'Lok', '2');
    dbms_xmldom.setAttribute(l_TXN_element,'pcid', '91');
    l_TXN_node    := dbms_xmldom.appendChild(l_AM_node,dbms_xmldom.makeNode(l_TXN_element));
   
    l_EO_element := dbms_xmldom.createElement(l_domdoc, 'EO' );
    dbms_xmldom.setAttribute(l_EO_element,'Name', 'oracle.apps.per.schema.server.PerAbsenceAttendancesEO');
    l_EO_node    := dbms_xmldom.appendChild(l_TXN_node,dbms_xmldom.makeNode(l_EO_element));

    l_EOCDATA_name_element := dbms_xmldom.createCDATASection(l_domdoc, sup_rec.CDATA );
    l_EOCDATA_name_node    := dbms_xmldom.appendChild(l_EO_node,dbms_xmldom.makeNode(l_EOCDATA_name_element));
    l_EOCDATA_name_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.CDATA);
    l_EOCDATA_name_tnode   := dbms_xmldom.appendChild(l_EOCDATA_name_node,dbms_xmldom.makeNode(l_EOCDATA_name_text));
   
    l_PerAbsAttendEORow_element := dbms_xmldom.createElement(l_domdoc, 'PerAbsenceAttendancesEORow' );
    dbms_xmldom.setAttribute(l_PerAbsAttendEORow_element,'PS', '0');
    dbms_xmldom.setAttribute(l_PerAbsAttendEORow_element,'PK', 'Y');
    l_PerAbsAttendEORow_node    := dbms_xmldom.appendChild(l_EO_node,dbms_xmldom.makeNode(l_PerAbsAttendEORow_element));
   
    l_EO2_element := dbms_xmldom.createElement(l_domdoc, 'EO' );
    dbms_xmldom.setAttribute(l_EO2_element,'Name', 'oracle.apps.per.schema.server.TransactionsEO');
    l_EO2_node    := dbms_xmldom.appendChild(l_TXN_node,dbms_xmldom.makeNode(l_EO2_element));
   
    l_EOCDATA2_name_element := dbms_xmldom.createCDATASection(l_domdoc, sup_rec.CDATA);
    l_EOCDATA2_name_node    := dbms_xmldom.appendChild(l_EO2_node,dbms_xmldom.makeNode(l_EOCDATA2_name_element));
    l_EOCDATA2_name_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.CDATA );
    l_EOCDATA2_name_tnode   := dbms_xmldom.appendChild(l_EOCDATA2_name_node,dbms_xmldom.makeNode(l_EOCDATA2_name_text));
   
   
   
    l_TransactionsEORow_element := dbms_xmldom.createElement(l_domdoc, 'TransactionsEORow' );
    dbms_xmldom.setAttribute(l_TransactionsEORow_element,'PS', '0');
    l_TransactionsEORow_node    := dbms_xmldom.appendChild(l_EO2_node,dbms_xmldom.makeNode(l_TransactionsEORow_element));
   
   
    -- Each Supplier node will get a Number node which contains the Supplier Number as text
    l_supp_numEle := dbms_xmldom.createElement(l_domdoc, 'TransactionGroup' );
    l_supp_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_supp_numEle));
    l_supp_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'ABSENCE_MGMT' );
    l_supp_num_tnode   := dbms_xmldom.appendChild(l_supp_num_node,dbms_xmldom.makeNode(l_supp_num_text));


  
   l_PrsnJobName_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnJobName' ); --l_domdoc constant
   l_PrsnJobName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnJobName_numEle));--l_TransCtx_node parent
   l_PrsnJobName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnJobName );--column of cursor
   l_PrsnJobName_num_tnode   := dbms_xmldom.appendChild(l_PrsnJobName_num_node,dbms_xmldom.makeNode(l_PrsnJobName_num_text));
   
   l_PrsnNpwFlag_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnNpwFlag' ); --l_domdoc constant
   l_PrsnNpwFlag_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnNpwFlag_numEle));--l_TransCtx_node parent
   l_PrsnNpwFlag_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'A' );--column of cursor
   l_PrsnNpwFlag_num_tnode   := dbms_xmldom.appendChild(l_PrsnNpwFlag_num_node,dbms_xmldom.makeNode(l_PrsnNpwFlag_num_text));
   
  
  
  
   l_EmpGen_numEle := dbms_xmldom.createElement(l_domdoc, 'EmployeeGeneration' ); --l_domdoc constant
   l_EmpGen_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_EmpGen_numEle));--l_TransCtx_node parent
   l_EmpGen_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'A' );--column of cursor
   l_EmpGen_num_tnode   := dbms_xmldom.appendChild(l_EmpGen_num_node,dbms_xmldom.makeNode(l_EmpGen_num_text));
   
  
   l_PrsnBgId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnBgId' ); --l_domdoc constant
   l_PrsnBgId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnBgId_numEle));--l_TransCtx_node parent
   l_PrsnBgId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNBGID );--column of cursor
   l_PrsnBgId_num_tnode   := dbms_xmldom.appendChild(l_PrsnBgId_num_node,dbms_xmldom.makeNode(l_PrsnBgId_num_text));
   
  
  
   l_FyiDetails_numEle := dbms_xmldom.createElement(l_domdoc, 'FyiDetails' ); --l_domdoc constant
   l_FyiDetails_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_FyiDetails_numEle));--l_TransCtx_node parent
   l_FyiDetails_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'Y' );--column of cursor
   l_FyiDetails_num_tnode   := dbms_xmldom.appendChild(l_FyiDetails_num_node,dbms_xmldom.makeNode(l_FyiDetails_num_text));
   
  
  
  
   l_LoginPrsnNpwFlag_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnNpwFlag' ); --l_domdoc constant
   l_LoginPrsnNpwFlag_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnNpwFlag_numEle));--l_TransCtx_node parent
   l_LoginPrsnNpwFlag_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'A' );--column of cursor
   l_LoginPrsnNpwFlag_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnNpwFlag_num_node,dbms_xmldom.makeNode(l_LoginPrsnNpwFlag_num_text));
    
  
  
   l_PrsnAsgFlag_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnAsgFlag' ); --l_domdoc constant
   l_PrsnAsgFlag_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnAsgFlag_numEle));--l_TransCtx_node parent
   l_PrsnAsgFlag_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'Y' );--column of cursor
   l_PrsnAsgFlag_num_tnode   := dbms_xmldom.appendChild(l_PrsnAsgFlag_num_node,dbms_xmldom.makeNode(l_PrsnAsgFlag_num_text));
   

  
  
   l_EffectiveDate_numEle := dbms_xmldom.createElement(l_domdoc, 'EffectiveDate' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_EffectiveDate_numEle,'dataType', 'd');
   l_EffectiveDate_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_EffectiveDate_numEle));--l_TransCtx_node parent
   l_EffectiveDate_num_text    := dbms_xmldom.createTextNode(l_domdoc, to_char(sysdate,'yyyy-mm-dd'));--column of cursor
   l_EffectiveDate_num_tnode   := dbms_xmldom.appendChild(l_EffectiveDate_num_node,dbms_xmldom.makeNode(l_EffectiveDate_num_text));
   
  
   l_ProductCode_numEle := dbms_xmldom.createElement(l_domdoc, 'ProductCode' ); --l_domdoc constant
   l_ProductCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_ProductCode_numEle));--l_TransCtx_node parent
   l_ProductCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'PER' );--column of cursor
   l_ProductCode_num_tnode   := dbms_xmldom.appendChild(l_ProductCode_num_node,dbms_xmldom.makeNode(l_ProductCode_num_text));
   
  
   l_pNtfSubMsg_numEle := dbms_xmldom.createElement(l_domdoc, 'pNtfSubMsg' ); --l_domdoc constant
   l_pNtfSubMsg_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pNtfSubMsg_numEle));--l_TransCtx_node parent
   l_pNtfSubMsg_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_ABS_NTF_SUB_MSG' );--column of cursor
   l_pNtfSubMsg_num_tnode   := dbms_xmldom.appendChild(l_pNtfSubMsg_num_node,dbms_xmldom.makeNode(l_pNtfSubMsg_num_text));
   
    l_LogPrsnCon_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnContextSet' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_LogPrsnCon_numEle,'dataType', 'b');
   l_LogPrsnCon_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LogPrsnCon_numEle));--l_TransCtx_node parent
   l_LogPrsnCon_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'true');--column of cursor
   l_LogPrsnCon_num_tnode   := dbms_xmldom.appendChild(l_LogPrsnCon_num_node,dbms_xmldom.makeNode(l_LogPrsnCon_num_text));
   
   l_LoginPrsnId_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnId' ); --l_domdoc constant
   l_LoginPrsnId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnId_numEle));--l_TransCtx_node parent
   l_LoginPrsnId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_LoginPrsnId );--column of cursor
   l_LoginPrsnId_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnId_num_node,dbms_xmldom.makeNode(l_LoginPrsnId_num_text));
   
  
   l_PrsnLegCode_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnLegCode' ); --l_domdoc constant
   l_PrsnLegCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnLegCode_numEle));--l_TransCtx_node parent
   l_PrsnLegCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'AE' );--column of cursor
   l_PrsnLegCode_num_tnode   := dbms_xmldom.appendChild(l_PrsnLegCode_num_node,dbms_xmldom.makeNode(l_PrsnLegCode_num_text));
   
  
   l_PrsnAssignmentId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnAssignmentId' ); --l_domdoc constant
   l_PrsnAssignmentId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnAssignmentId_numEle));--l_TransCtx_node parent
   l_PrsnAssignmentId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnAssignmentId );--column of cursor
   l_PrsnAssignmentId_num_tnode   := dbms_xmldom.appendChild(l_PrsnAssignmentId_num_node,dbms_xmldom.makeNode(l_PrsnAssignmentId_num_text));
   
  
   l_PrsnMgrId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnMgrId' ); --l_domdoc constant
   l_PrsnMgrId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnMgrId_numEle));--l_TransCtx_node parent
   l_PrsnMgrId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnMgrId );--column of cursor
   l_PrsnMgrId_num_tnode   := dbms_xmldom.appendChild(l_PrsnMgrId_num_node,dbms_xmldom.makeNode(l_PrsnMgrId_num_text));
   
  
   l_LoginPrsnLegCode_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnLegCode' ); --l_domdoc constant
   l_LoginPrsnLegCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnLegCode_numEle));--l_TransCtx_node parent
   l_LoginPrsnLegCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'AE' );--column of cursor
   l_LoginPrsnLegCode_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnLegCode_num_node,dbms_xmldom.makeNode(l_LoginPrsnLegCode_num_text));
   
  
   l_txnRefId_numEle := dbms_xmldom.createElement(l_domdoc, 'TransactionRefId' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_txnRefId_numEle,'dataType', 'n');
   l_txnRefId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_txnRefId_numEle));--l_TransCtx_node parent
   l_txnRefId_num_text    := dbms_xmldom.createTextNode(l_domdoc, v_TransactionRefId);--column of cursor
   l_txnRefId_num_tnode   := dbms_xmldom.appendChild(l_txnRefId_num_node,dbms_xmldom.makeNode(l_txnRefId_num_text));
   
  
   l_PrsnGradeId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnGradeId' ); --l_domdoc constant
   l_PrsnGradeId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnGradeId_numEle));--l_TransCtx_node parent
   l_PrsnGradeId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnGradeId );--column of cursor
   l_PrsnGradeId_num_tnode   := dbms_xmldom.appendChild(l_PrsnGradeId_num_node,dbms_xmldom.makeNode(l_PrsnGradeId_num_text));
   
   
   
  
   l_AsgStartDate_numEle := dbms_xmldom.createElement(l_domdoc, 'AsgStartDate' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_AsgStartDate_numEle,'dataType', 'd');
   l_AsgStartDate_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_AsgStartDate_numEle));--l_TransCtx_node parent
   l_AsgStartDate_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.AsgStartDate );--column of cursor
   l_AsgStartDate_num_tnode   := dbms_xmldom.appendChild(l_AsgStartDate_num_node,dbms_xmldom.makeNode(l_AsgStartDate_num_text));
   
   
   
  
   l_PrsnJobId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnJobId' ); --l_domdoc constant
   l_PrsnJobId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnJobId_numEle));--l_TransCtx_node parent
   l_PrsnJobId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnJobId );--column of cursor
   l_PrsnJobId_num_tnode   := dbms_xmldom.appendChild(l_PrsnJobId_num_node,dbms_xmldom.makeNode(l_PrsnJobId_num_text));
   
  
   l_ItemType_numEle := dbms_xmldom.createElement(l_domdoc, 'ItemType' ); --l_domdoc constant
   l_ItemType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_ItemType_numEle));--l_TransCtx_node parent
   l_ItemType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HRSSA' );--column of cursor
   l_ItemType_num_tnode   := dbms_xmldom.appendChild(l_ItemType_num_node,dbms_xmldom.makeNode(l_ItemType_num_text));
   
   l_PrsnContextSet_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnContextSet' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_PrsnContextSet_numEle,'dataType', 'b');
   l_PrsnContextSet_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnContextSet_numEle));--l_TransCtx_node parent
   l_PrsnContextSet_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'true' );--column of cursor
   l_PrsnContextSet_num_tnode   := dbms_xmldom.appendChild(l_PrsnContextSet_num_node,dbms_xmldom.makeNode(l_PrsnContextSet_num_text));
  
  
   l_LoginPrsnEmpFlag_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnEmpFlag' ); --l_domdoc constant
   l_LoginPrsnEmpFlag_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnEmpFlag_numEle));--l_TransCtx_node parent
   l_LoginPrsnEmpFlag_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'A' );--column of cursor
   l_LoginPrsnEmpFlag_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnEmpFlag_num_node,dbms_xmldom.makeNode(l_LoginPrsnEmpFlag_num_text));
  
   l_LoginPrsnType_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnType' ); --l_domdoc constant
   l_LoginPrsnType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnType_numEle));--l_TransCtx_node parent
   l_LoginPrsnType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'E' );--column of cursor
   l_LoginPrsnType_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnType_num_node,dbms_xmldom.makeNode(l_LoginPrsnType_num_text));
  
   l_pCalledId_numEle := dbms_xmldom.createElement(l_domdoc, 'pCalledId' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_pCalledId_numEle,'dataType', 'n');
   l_pCalledId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pCalledId_numEle));--l_TransCtx_node parent
   l_pCalledId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_pCalledId  );--column of cursor
   l_pCalledId_num_tnode   := dbms_xmldom.appendChild(l_pCalledId_num_node,dbms_xmldom.makeNode(l_pCalledId_num_text));
  
       l_ReviewTemRNAtt_numEle := dbms_xmldom.createElement(l_domdoc, 'ReviewTemplateRNAttr' ); --l_domdoc constant
   l_ReviewTemRNAtt_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_ReviewTemRNAtt_numEle));--l_TransCtx_node parent
   l_ReviewTemRNAtt_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_ABS_NTF_SS' );--column of cursor
   l_ReviewTemRNAtt_num_tnode   := dbms_xmldom.appendChild(l_ReviewTemRNAtt_num_node,dbms_xmldom.makeNode(l_ReviewTemRNAtt_num_text));
  
 
  
   l_TransactionId_numEle := dbms_xmldom.createElement(l_domdoc, 'TransactionId' ); --l_domdoc constant
   l_TransactionId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_TransactionId_numEle));--l_TransCtx_node parent
   l_TransactionId_num_text    := dbms_xmldom.createTextNode(l_domdoc, P_TransactionId);--column of cursor
   l_TransactionId_num_tnode   := dbms_xmldom.appendChild(l_TransactionId_num_node,dbms_xmldom.makeNode(l_TransactionId_num_text));
  
  
  
   l_PrsnLocationId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnLocationId' ); --l_domdoc constant
   l_PrsnLocationId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnLocationId_numEle));--l_TransCtx_node parent
   l_PrsnLocationId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnLocationId );--column of cursor
   l_PrsnLocationId_num_tnode   := dbms_xmldom.appendChild(l_PrsnLocationId_num_node,dbms_xmldom.makeNode(l_PrsnLocationId_num_text));
  
   l_PrsnKfstrCode_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnKflexStructCode' ); --l_domdoc constant
   l_PrsnKfstrCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnKfstrCode_numEle));--l_TransCtx_node parent
   l_PrsnKfstrCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'FUJ_People_Group_Flexfield' );--column of cursor
   l_PrsnKfstrCode_num_tnode   := dbms_xmldom.appendChild(l_PrsnKfstrCode_num_node,dbms_xmldom.makeNode(l_PrsnKfstrCode_num_text));
  
   l_LoginPrsnMgrName_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnMgrName' ); --l_domdoc constant
   l_LoginPrsnMgrName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnMgrName_numEle));--l_TransCtx_node parent
   l_LoginPrsnMgrName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNMGRNAME );--column of cursor
   l_LoginPrsnMgrName_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnMgrName_num_node,dbms_xmldom.makeNode(l_LoginPrsnMgrName_num_text));
  
  
  
   l_PrsnPositionName_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnPositionName' ); --l_domdoc constant
   l_PrsnPositionName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnPositionName_numEle));--l_TransCtx_node parent
   l_PrsnPositionName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnPositionName );--column of cursor
   l_PrsnPositionName_num_tnode   := dbms_xmldom.appendChild(l_PrsnPositionName_num_node,dbms_xmldom.makeNode(l_PrsnPositionName_num_text));
  
  
  
   l_PrsnEmpFlag_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnEmpFlag' ); --l_domdoc constant
   l_PrsnEmpFlag_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnEmpFlag_numEle));--l_TransCtx_node parent
   l_PrsnEmpFlag_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'A' );--column of cursor
   l_PrsnEmpFlag_num_tnode   := dbms_xmldom.appendChild(l_PrsnEmpFlag_num_node,dbms_xmldom.makeNode(l_PrsnEmpFlag_num_text));
  
  
  
  
   l_PrsnMgrName_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnMgrName' ); --l_domdoc constant
   l_PrsnMgrName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnMgrName_numEle));--l_TransCtx_node parent
   l_PrsnMgrName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnMgrName );--column of cursor
   l_PrsnMgrName_num_tnode   := dbms_xmldom.appendChild(l_PrsnMgrName_num_node,dbms_xmldom.makeNode(l_PrsnMgrName_num_text));
  

  
   l_prsnOrgId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnOrganizationId' ); --l_domdoc constant
   l_prsnOrgId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_prsnOrgId_numEle));--l_TransCtx_node parent
   l_prsnOrgId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnOrganizationId );--column of cursor
   l_prsnOrgId_num_tnode   := dbms_xmldom.appendChild(l_prsnOrgId_num_node,dbms_xmldom.makeNode(l_prsnOrgId_num_text));
  
      l_PrsnPositionId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnPositionId' ); --l_domdoc constant
   l_PrsnPositionId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnPositionId_numEle));--l_TransCtx_node parent
   l_PrsnPositionId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnPositionId );--column of cursor
   l_PrsnPositionId_num_tnode   := dbms_xmldom.appendChild(l_PrsnPositionId_num_node,dbms_xmldom.makeNode(l_PrsnPositionId_num_text));
  
  
  
  
   l_TxnStatus_numEle := dbms_xmldom.createElement(l_domdoc, 'TxnStatus' ); --l_domdoc constant
   l_TxnStatus_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_TxnStatus_numEle));--l_TransCtx_node parent
   l_TxnStatus_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'W' );--column of cursor
   l_TxnStatus_num_tnode   := dbms_xmldom.appendChild(l_TxnStatus_num_node,dbms_xmldom.makeNode(l_TxnStatus_num_text));
  
  
  
  
   l_LoginWorkerNumber_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginWorkerNumber' ); --l_domdoc constant
   l_LoginWorkerNumber_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginWorkerNumber_numEle));--l_TransCtx_node parent
   l_LoginWorkerNumber_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.LoginWorkerNumber );--column of cursor
   l_LoginWorkerNumber_num_tnode   := dbms_xmldom.appendChild(l_LoginWorkerNumber_num_node,dbms_xmldom.makeNode(l_LoginWorkerNumber_num_text));
  

  
  
  
   l_PrsnBgCurrencyCode_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnBgCurrencyCode' ); --l_domdoc constant
   l_PrsnBgCurrencyCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnBgCurrencyCode_numEle));--l_TransCtx_node parent
   l_PrsnBgCurrencyCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'AED' );--column of cursor
   l_PrsnBgCurrencyCode_num_tnode   := dbms_xmldom.appendChild(l_PrsnBgCurrencyCode_num_node,dbms_xmldom.makeNode(l_PrsnBgCurrencyCode_num_text));
  
  
  
  
   l_HeaderType_numEle := dbms_xmldom.createElement(l_domdoc, 'HeaderType' ); --l_domdoc constant
   l_HeaderType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_HeaderType_numEle));--l_TransCtx_node parent
   l_HeaderType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'PER_HEADER' );--column of cursor
   l_HeaderType_num_tnode   := dbms_xmldom.appendChild(l_HeaderType_num_node,dbms_xmldom.makeNode(l_HeaderType_num_text));
  

  
   l_SSHR_WF_BASED_numEle := dbms_xmldom.createElement(l_domdoc, 'SSHR_WF_BASED' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_SSHR_WF_BASED_numEle,'dataType', 'b');
   l_SSHR_WF_BASED_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_SSHR_WF_BASED_numEle));--l_TransCtx_node parent
   l_SSHR_WF_BASED_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'true' );--column of cursor
   l_SSHR_WF_BASED_num_tnode   := dbms_xmldom.appendChild(l_SSHR_WF_BASED_num_node,dbms_xmldom.makeNode(l_SSHR_WF_BASED_num_text));
  
      l_prsnSecGrpId_numEle:= dbms_xmldom.createElement(l_domdoc, 'PrsnSecurityGroupId' ); --l_domdoc constant
   l_prsnSecGrpId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_prsnSecGrpId_numEle));--l_TransCtx_node parent
   l_prsnSecGrpId_num_text    := dbms_xmldom.createTextNode(l_domdoc, '0' );--column of cursor
   l_prsnSecGrpId_num_tnode   := dbms_xmldom.appendChild(l_prsnSecGrpId_num_node,dbms_xmldom.makeNode(l_prsnSecGrpId_num_text));
  

      l_LoginPrsnMgrId_numEle:= dbms_xmldom.createElement(l_domdoc, 'LoginPrsnMgrId' ); --l_domdoc constant
   l_LoginPrsnMgrId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnMgrId_numEle));--l_TransCtx_node parent
   l_LoginPrsnMgrId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNMGRID);--column of cursor
   l_LoginPrsnMgrId_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnMgrId_num_node,dbms_xmldom.makeNode(l_LoginPrsnMgrId_num_text));
  
   l_AttachCheck_numEle:= dbms_xmldom.createElement(l_domdoc, 'AttachCheck' ); --l_domdoc constant
   l_AttachCheck_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_AttachCheck_numEle));--l_TransCtx_node parent
   l_AttachCheck_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'Y');--column of cursor
   l_AttachCheck_num_tnode   := dbms_xmldom.appendChild(l_AttachCheck_num_node,dbms_xmldom.makeNode(l_AttachCheck_num_text));
  

      l_PrsnType_numEle:= dbms_xmldom.createElement(l_domdoc, 'PrsnType' ); --l_domdoc constant
   l_PrsnType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnType_numEle));--l_TransCtx_node parent
   l_PrsnType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'E' );--column of cursor
   l_PrsnType_num_tnode   := dbms_xmldom.appendChild(l_PrsnType_num_node,dbms_xmldom.makeNode(l_PrsnType_num_text));
  
  
      l_PerzOrgId_numEle:= dbms_xmldom.createElement(l_domdoc, 'PerzOrganizationId' ); --l_domdoc constant
   l_PerzOrgId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PerzOrgId_numEle));--l_TransCtx_node parent
   l_PerzOrgId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNBGID );--column of cursor
   l_PerzOrgId_num_tnode   := dbms_xmldom.appendChild(l_PerzOrgId_num_node,dbms_xmldom.makeNode(l_PerzOrgId_num_text));
  
  
      l_LoginPrsnName_numEle:= dbms_xmldom.createElement(l_domdoc, 'LoginPrsnName' ); --l_domdoc constant
   l_LoginPrsnName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnName_numEle));--l_TransCtx_node parent
   l_LoginPrsnName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNNAME );--column of cursor
   l_LoginPrsnName_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnName_num_node,dbms_xmldom.makeNode(l_LoginPrsnName_num_text));
  
      l_CreatorPrsnId_numEle:= dbms_xmldom.createElement(l_domdoc, 'CreatorPrsnId' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_CreatorPrsnId_numEle,'dataType', 'n');  
   l_CreatorPrsnId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_CreatorPrsnId_numEle));--l_TransCtx_node parent
   l_CreatorPrsnId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.LOGINPRSNID );--column of cursor
   l_CreatorPrsnId_num_tnode   := dbms_xmldom.appendChild(l_CreatorPrsnId_num_node,dbms_xmldom.makeNode(l_CreatorPrsnId_num_text));
  
  
      l_PrsnId_numEle:= dbms_xmldom.createElement(l_domdoc, 'PrsnId' ); --l_domdoc constant
   l_PrsnId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnId_numEle));--l_TransCtx_node parent
   l_PrsnId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.LOGINPRSNID );--column of cursor
   l_PrsnId_num_tnode   := dbms_xmldom.appendChild(l_PrsnId_num_node,dbms_xmldom.makeNode(l_PrsnId_num_text));
  
  
      l_TransactionType_numEle:= dbms_xmldom.createElement(l_domdoc, 'TransactionType' ); --l_domdoc constant
   l_TransactionType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_TransactionType_numEle));--l_TransCtx_node parent
   l_TransactionType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'WF' );--column of cursor
   l_TransactionType_num_tnode   := dbms_xmldom.appendChild(l_TransactionType_num_node,dbms_xmldom.makeNode(l_TransactionType_num_text));
  
      l_NtfAttachAttr_numEle:= dbms_xmldom.createElement(l_domdoc, 'NtfAttachAttr' ); --l_domdoc constant
   l_NtfAttachAttr_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_NtfAttachAttr_numEle));--l_TransCtx_node parent
   l_NtfAttachAttr_num_text    := dbms_xmldom.createTextNode(l_domdoc,  'FND:entity=PQH_SS_ATTACHMENT'||'&'||'amp;pk1name=TransactionId'||'&'||'amp;pk1value='||p_TransactionId );--column of cursor
   l_NtfAttachAttr_num_tnode   := dbms_xmldom.appendChild(l_NtfAttachAttr_num_node,dbms_xmldom.makeNode(l_NtfAttachAttr_num_text));
  

      l_pApprovalReqd_numEle:= dbms_xmldom.createElement(l_domdoc, 'pApprovalReqd' ); --l_domdoc constant
   l_pApprovalReqd_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pApprovalReqd_numEle));--l_TransCtx_node parent
   l_pApprovalReqd_num_text    := dbms_xmldom.createTextNode(l_domdoc,'YD' );--column of cursor
   l_pApprovalReqd_num_tnode   := dbms_xmldom.appendChild(l_pApprovalReqd_num_node,dbms_xmldom.makeNode(l_pApprovalReqd_num_text));
  
  
     l_pAMETranType_numEle:= dbms_xmldom.createElement(l_domdoc, 'pAMETranType' ); --l_domdoc constant
   l_pAMETranType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pAMETranType_numEle));--l_TransCtx_node parent
   l_pAMETranType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'SSHRMS' );--column of cursor
   l_pAMETranType_num_tnode   := dbms_xmldom.appendChild(l_pAMETranType_num_node,dbms_xmldom.makeNode(l_pAMETranType_num_text));
  
  
      l_RelaunchFunction_numEle:= dbms_xmldom.createElement(l_domdoc, 'RelaunchFunction' ); --l_domdoc constant
   l_RelaunchFunction_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_RelaunchFunction_numEle));--l_TransCtx_node parent
   l_RelaunchFunction_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_ABS_ENTRY_PAGE_SS' );--column of cursor
   l_RelaunchFunction_num_tnode   := dbms_xmldom.appendChild(l_RelaunchFunction_num_node,dbms_xmldom.makeNode(l_RelaunchFunction_num_text));
  

      L_PerzLocCode_numEle:= dbms_xmldom.createElement(l_domdoc, 'PerzLocalizationCode' ); --l_domdoc constant
   L_PerzLocCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(L_PerzLocCode_numEle));--l_TransCtx_node parent
   L_PerzLocCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'AE' );--column of cursor
   L_PerzLocCode_num_tnode   := dbms_xmldom.appendChild(L_PerzLocCode_num_node,dbms_xmldom.makeNode(L_PerzLocCode_num_text));
  

      l_TxnRefTab_numEle:= dbms_xmldom.createElement(l_domdoc, 'TransactionRefTable' ); --l_domdoc constant
   l_TxnRefTab_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_TxnRefTab_numEle));--l_TransCtx_node parent
   l_TxnRefTab_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'PER_ABSENCE_ATTENDANCES' );--column of cursor
   l_TxnRefTab_num_tnode   := dbms_xmldom.appendChild(l_TxnRefTab_num_node,dbms_xmldom.makeNode(l_TxnRefTab_num_text));
  

      l_TxnIdentifier_numEle:= dbms_xmldom.createElement(l_domdoc, 'TransactionIdentifier' ); --l_domdoc constant
   l_TxnIdentifier_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_TxnIdentifier_numEle));--l_TransCtx_node parent
   l_TxnIdentifier_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'ABSENCES');--column of cursor
   l_TxnIdentifier_num_tnode   := dbms_xmldom.appendChild(l_TxnIdentifier_num_node,dbms_xmldom.makeNode(l_TxnIdentifier_num_text));
  
   l_pCalledFrom_numEle:= dbms_xmldom.createElement(l_domdoc, 'pCalledFrom' ); --l_domdoc constant
   l_pCalledFrom_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pCalledFrom_numEle));--l_TransCtx_node parent
   l_pCalledFrom_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_LOA_SS' );--column of cursor
   l_pCalledFrom_num_tnode   := dbms_xmldom.appendChild(l_pCalledFrom_num_node,dbms_xmldom.makeNode(l_pCalledFrom_num_text));
  
  
      l_LoginPrsnBgId_numEle:= dbms_xmldom.createElement(l_domdoc, 'LoginPrsnBgId' ); --l_domdoc constant
   l_LoginPrsnBgId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnBgId_numEle));--l_TransCtx_node parent
   l_LoginPrsnBgId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNBGID);--column of cursor
   l_LoginPrsnBgId_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnBgId_num_node,dbms_xmldom.makeNode(l_LoginPrsnBgId_num_text));
  

      l_PerzFunctionName_numEle:= dbms_xmldom.createElement(l_domdoc, 'PerzFunctionName' ); --l_domdoc constant
   l_PerzFunctionName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PerzFunctionName_numEle));--l_TransCtx_node parent
   l_PerzFunctionName_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_LOA_SS' );--column of cursor
   l_PerzFunctionName_num_tnode   := dbms_xmldom.appendChild(l_PerzFunctionName_num_node,dbms_xmldom.makeNode(l_PerzFunctionName_num_text));
  
      l_ProcessName_numEle:= dbms_xmldom.createElement(l_domdoc, 'ProcessName' ); --l_domdoc constant
   l_ProcessName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_ProcessName_numEle));--l_TransCtx_node parent
   l_ProcessName_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_GENERIC_APPROVAL_PRC' );--column of cursor
   l_ProcessName_num_tnode   := dbms_xmldom.appendChild(l_ProcessName_num_node,dbms_xmldom.makeNode(l_ProcessName_num_text));
  

      l_PrsnPayrollId_numEle:= dbms_xmldom.createElement(l_domdoc, 'PrsnPayrollId' ); --l_domdoc constant
   l_PrsnPayrollId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnPayrollId_numEle));--l_TransCtx_node parent
   l_PrsnPayrollId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnPayrollId );--column of cursor
   l_PrsnPayrollId_num_tnode   := dbms_xmldom.appendChild(l_PrsnPayrollId_num_node,dbms_xmldom.makeNode(l_PrsnPayrollId_num_text));
  
      l_PrsnName_numEle:= dbms_xmldom.createElement(l_domdoc, 'PrsnName' ); --l_domdoc constant
   l_PrsnName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnName_numEle));--l_TransCtx_node parent
   l_PrsnName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnName );--column of cursor
   l_PrsnName_num_tnode   := dbms_xmldom.appendChild(l_PrsnName_num_node,dbms_xmldom.makeNode(l_PrsnName_num_text));
  

      l_pAMEAppId_numEle:= dbms_xmldom.createElement(l_domdoc, 'pAMEAppId' ); --l_domdoc constant
   l_pAMEAppId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pAMEAppId_numEle));--l_TransCtx_node parent
   l_pAMEAppId_num_text    := dbms_xmldom.createTextNode(l_domdoc, '800' );--column of cursor
   l_pAMEAppId_num_tnode   := dbms_xmldom.appendChild(l_pAMEAppId_num_node,dbms_xmldom.makeNode(l_pAMEAppId_num_text));
  
   l_SS_RevFLMode_numEle:= dbms_xmldom.createElement(l_domdoc, 'SSHR_REVIEW_FLOW_MODE' ); --l_domdoc constant
   l_SS_RevFLMode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_SS_RevFLMode_numEle));--l_TransCtx_node parent
   l_SS_RevFLMode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'WF_REVIEW_PAGE' );--column of cursor
   l_SS_RevFLMode_num_tnode   := dbms_xmldom.appendChild(l_SS_RevFLMode_num_node,dbms_xmldom.makeNode(l_SS_RevFLMode_num_text));
  
   l_cnode_element2 := dbms_xmldom.createElement(l_domdoc, 'CNode' );--dbms_xmldom.createProcessingInstruction(l_domdoc, 'CNode','Test="1"' );
   dbms_xmldom.setAttribute(l_cnode_element2,'name', 'AbsenceParams');
   dbms_xmldom.setAttribute(l_cnode_element2,'type', 'Ht');
   l_cnode_node2    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_cnode_element2));
  
   l_AbsenceAttdId_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceAttdId' ); --l_domdoc constant
   l_AbsenceAttdId_num_node    := dbms_xmldom.appendChild(l_cnode_node2,dbms_xmldom.makeNode(l_AbsenceAttdId_numEle));--l_TransCtx_node parent
   l_AbsenceAttdId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsenceAttdId );--column of cursor
   l_AbsenceAttdId_num_tnode   := dbms_xmldom.appendChild(l_AbsenceAttdId_num_node,dbms_xmldom.makeNode(l_AbsenceAttdId_num_text));
  

  
  
   l_AbsenceAction_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceAction' ); --l_domdoc constant
   l_AbsenceAction_num_node    := dbms_xmldom.appendChild(l_cnode_node2,dbms_xmldom.makeNode(l_AbsenceAction_numEle));--l_TransCtx_node parent
   l_AbsenceAction_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsenceAction );--column of cursor
   l_AbsenceAction_num_tnode   := dbms_xmldom.appendChild(l_AbsenceAction_num_node,dbms_xmldom.makeNode(l_AbsenceAction_num_text));
   


  
   l_AbsAttId_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceAttendanceId' ); --l_domdoc constant
   l_AbsAttId_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_AbsAttId_numEle));--l_TransCtx_node parent
   l_AbsAttId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsenceAttdId );--column of cursor
   l_AbsAttId_num_tnode   := dbms_xmldom.appendChild(l_AbsAttId_num_node,dbms_xmldom.makeNode(l_AbsAttId_num_text));
  
      if p_AbsAttendanceReasonId is not null then
   l_AbsAttendReaId_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsAttendanceReasonId' ); --l_domdoc constant
   l_AbsAttendReaId_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_AbsAttendReaId_numEle));--l_TransCtx_node parent
   l_AbsAttendReaId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsAttendanceReasonId );--column of cursor
   l_AbsAttendReaId_num_tnode   := dbms_xmldom.appendChild(l_AbsAttendReaId_num_node,dbms_xmldom.makeNode(l_AbsAttendReaId_num_text));
   end if;
   
       l_BusinessGroupId_numEle := dbms_xmldom.createElement(l_domdoc, 'BusinessGroupId' ); --l_domdoc constant
   l_BusinessGroupId_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_BusinessGroupId_numEle));--l_TransCtx_node parent
   l_BusinessGroupId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNBGID );--column of cursor
   l_BusinessGroupId_num_tnode   := dbms_xmldom.appendChild(l_BusinessGroupId_num_node,dbms_xmldom.makeNode(l_BusinessGroupId_num_text));
   
       l_AbsAttTypeId_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceAttendanceTypeId' ); --l_domdoc constant
   l_AbsAttTypeId_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_AbsAttTypeId_numEle));--l_TransCtx_node parent
   l_AbsAttTypeId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsenceAttendanceTypeId);--column of cursor
   l_AbsAttTypeId_num_tnode   := dbms_xmldom.appendChild(l_AbsAttTypeId_num_node,dbms_xmldom.makeNode(l_AbsAttTypeId_num_text));
  

  
   l_PersonId_numEle := dbms_xmldom.createElement(l_domdoc, 'PersonId' ); --l_domdoc constant
   l_PersonId_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_PersonId_numEle));--l_TransCtx_node parent
   l_PersonId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_PrsnId  );--column of cursor
   l_PersonId_num_tnode   := dbms_xmldom.appendChild(l_PersonId_num_node,dbms_xmldom.makeNode(l_PersonId_num_text));
  
   l_AbsenceDays_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceDays' ); --l_domdoc constant
   l_AbsenceDays_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_AbsenceDays_numEle));--l_TransCtx_node parent
   l_AbsenceDays_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsenceDays );--column of cursor
   l_AbsenceDays_num_tnode   := dbms_xmldom.appendChild(l_AbsenceDays_num_node,dbms_xmldom.makeNode(l_AbsenceDays_num_text));
  

   l_AbsenceHours_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceHours' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_AbsenceHours_numEle,'null', 'true');
   l_AbsenceHours_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_AbsenceHours_numEle));--l_TransCtx_node parent
   --l_AbsenceHours_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_AbsenceHours_num_tnode   := dbms_xmldom.appendChild(l_AbsenceHours_num_node,dbms_xmldom.makeNode(l_AbsenceHours_num_text));
  
      l_DateEnd_numEle := dbms_xmldom.createElement(l_domdoc, 'DateEnd' ); --l_domdoc constant
   l_DateEnd_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_DateEnd_numEle));--l_TransCtx_node parent
   l_DateEnd_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_DateEnd );--column of cursor
   l_DateEnd_num_tnode   := dbms_xmldom.appendChild(l_DateEnd_num_node,dbms_xmldom.makeNode(l_DateEnd_num_text));
  

     
   l_DateNoti_numEle := dbms_xmldom.createElement(l_domdoc, 'DateNotification' ); --l_domdoc constant
   l_DateNoti_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_DateNoti_numEle));--l_TransCtx_node parent
   l_DateNoti_num_text    := dbms_xmldom.createTextNode(l_domdoc, TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') );--column of cursor
   l_DateNoti_num_tnode   := dbms_xmldom.appendChild(l_DateNoti_num_node,dbms_xmldom.makeNode(l_DateNoti_num_text));
  

   l_DateProjEnd_numEle := dbms_xmldom.createElement(l_domdoc, 'DateProjectedEnd' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_DateProjEnd_numEle,'null', 'true');  
   l_DateProjEnd_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_DateProjEnd_numEle));--l_TransCtx_node parent
   --l_DateProjEnd_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_DateProjEnd_num_tnode   := dbms_xmldom.appendChild(l_DateProjEnd_num_node,dbms_xmldom.makeNode(l_DateProjEnd_num_text));
  

      l_DatePrjStrt_numEle := dbms_xmldom.createElement(l_domdoc, 'DateProjectedStart' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_DatePrjStrt_numEle,'null', 'true');  
   l_DatePrjStrt_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_DatePrjStrt_numEle));--l_TransCtx_node parent
   --l_DatePrjStrt_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_DatePrjStrt_num_tnode   := dbms_xmldom.appendChild(l_DatePrjStrt_num_node,dbms_xmldom.makeNode(l_DatePrjStrt_num_text));
  

   l_DateStart_numEle := dbms_xmldom.createElement(l_domdoc, 'DateStart' ); --l_domdoc constant
   l_DateStart_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_DateStart_numEle));--l_TransCtx_node parent
   l_DateStart_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_DateStart );--column of cursor
   l_DateStart_num_tnode   := dbms_xmldom.appendChild(l_DateStart_num_node,dbms_xmldom.makeNode(l_DateStart_num_text));
  

   l_TimeProjEndnumEle := dbms_xmldom.createElement(l_domdoc, 'TimeProjectedEnd' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_TimeProjEndnumEle,'null', 'true'); 
   l_TimeProjEndnum_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_TimeProjEndnumEle));--l_TransCtx_node parent
   --l_TimeProjEndnum_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_TimeProjEndnum_tnode   := dbms_xmldom.appendChild(l_TimeProjEndnum_node,dbms_xmldom.makeNode(l_TimeProjEndnum_text));
  

        l_TimeProjSt_numEle := dbms_xmldom.createElement(l_domdoc, 'TimeProjectedStart' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_TimeProjSt_numEle,'null', 'true');    
   l_TimeProjSt_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_TimeProjSt_numEle));--l_TransCtx_node parent
   --l_TimeProjSt_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_TimeProjSt_num_tnode   := dbms_xmldom.appendChild(l_TimeProjSt_num_node,dbms_xmldom.makeNode(l_TimeProjSt_num_text));
  

         l_LastUpdateDate_numEle := dbms_xmldom.createElement(l_domdoc, 'LastUpdateDate' ); --l_domdoc constant
   l_LastUpdateDate_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_LastUpdateDate_numEle));--l_TransCtx_node parent
   l_LastUpdateDate_num_text    := dbms_xmldom.createTextNode(l_domdoc, TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') );--column of cursor
   l_LastUpdateDate_num_tnode   := dbms_xmldom.appendChild(l_LastUpdateDate_num_node,dbms_xmldom.makeNode(l_LastUpdateDate_num_text));
  

       l_LastUpdatedBy_numEle := dbms_xmldom.createElement(l_domdoc, 'LastUpdatedBy' ); --l_domdoc constant
   l_LastUpdatedBy_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_LastUpdatedBy_numEle));--l_TransCtx_node parent
   l_LastUpdatedBy_num_text    := dbms_xmldom.createTextNode(l_domdoc, v_userId );--column of cursor
   l_LastUpdatedBy_num_tnode   := dbms_xmldom.appendChild(l_LastUpdatedBy_num_node,dbms_xmldom.makeNode(l_LastUpdatedBy_num_text));
  

     
   l_LastUpdateLogin_numEle := dbms_xmldom.createElement(l_domdoc, 'LastUpdateLogin' ); --l_domdoc constant
   l_LastUpdateLogin_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_LastUpdateLogin_numEle));--l_TransCtx_node parent
   l_LastUpdateLogin_num_text    := dbms_xmldom.createTextNode(l_domdoc, v_sessionid );--column of cursor
   l_LastUpdateLogin_num_tnode   := dbms_xmldom.appendChild(l_LastUpdateLogin_num_node,dbms_xmldom.makeNode(l_LastUpdateLogin_num_text));
  

      l_CreatedBy_numEle := dbms_xmldom.createElement(l_domdoc, 'CreatedBy' ); --l_domdoc constant
   l_CreatedBy_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_CreatedBy_numEle));--l_TransCtx_node parent
   l_CreatedBy_num_text    := dbms_xmldom.createTextNode(l_domdoc, v_userId );--column of cursor
   l_CreatedBy_num_tnode   := dbms_xmldom.appendChild(l_CreatedBy_num_node,dbms_xmldom.makeNode(l_CreatedBy_num_text));
  

       l_CreationDate_numEle := dbms_xmldom.createElement(l_domdoc, 'CreationDate' ); --l_domdoc constant
   l_CreationDate_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_CreationDate_numEle));--l_TransCtx_node parent
   l_CreationDate_num_text    := dbms_xmldom.createTextNode(l_domdoc, TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') );--column of cursor
   l_CreationDate_num_tnode   := dbms_xmldom.appendChild(l_CreationDate_num_node,dbms_xmldom.makeNode(l_CreationDate_num_text));
  

   l_OVN_numEle := dbms_xmldom.createElement(l_domdoc, 'ObjectVersionNumber' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_OVN_numEle,'null', 'true');
   l_OVN_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_OVN_numEle));--l_TransCtx_node parent
   --l_OVN_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_OVN_num_tnode   := dbms_xmldom.appendChild(l_OVN_num_node,dbms_xmldom.makeNode(l_OVN_num_text));
  
   l_Comments_numEle := dbms_xmldom.createElement(l_domdoc, 'Comments' ); --l_domdoc constant
   l_Comments_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_Comments_numEle));--l_TransCtx_node parent
   l_Comments_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_comments );--column of cursor
   l_Comments_num_tnode   := dbms_xmldom.appendChild(l_Comments_num_node,dbms_xmldom.makeNode(l_Comments_num_text));
 
   l_LinkAbsIdAbsEO_numEle := dbms_xmldom.createElement(l_domdoc, 'LinkedAbsenceIdPerAbsenceAttendancesEO' ); --l_domdoc constant
   l_LinkAbsIdAbsEO_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_LinkAbsIdAbsEO_numEle));--l_TransCtx_node parent
  


  END LOOP;

  l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
  dbms_xmldom.freeDocument(l_domdoc);

  --dbms_output.put_line(l_xmltype.getClobVal);
  --insert into xml_document values(1,l_xmltype.getClobVal);
  commit;

p_msg := 'S';
p_xml := l_xmltype.getClobVal;

exception when others then
p_msg := 'E';

END;

--select 1 from dual
/


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.2 xml for managers.
change the KFF as per your setup.

CREATE OR REPLACE PROCEDURE APPS.P_XML_TRANSACTION_DOCUMENT_MGR
(
p_TransactionId NUMBER, --1
p_LoginPrsnId NUMBER ,
p_PrsnId    number,
p_pCalledId  number ,
p_AbsenceAttdId  number,   --PER_ABSENCE_ATTENDANCES_S.NEXTVAL
P_TransactionRefId  number , --PER_ABSENCE_ATTENDANCES_S.CURRVAL
p_AbsenceAction  varchar2 ,
p_AbsenceAttendanceTypeId number,
p_AbsAttendanceReasonId Number default null,-->1061</
p_AbsenceDays  number ,
p_DateStart  varchar2 ,
p_DateEnd  varchar2 ,
p_comments varchar2 default null,
p_userid number ,
P_xml out clob,
p_msg out varchar2
)
AS
--p_TransactionId number :=  12345;
--p_LoginPrsnId  number :=  26876;
--p_PrsnId    number :=  26876;
--p_pCalledId  number :=  12238;
--p_AbsenceAttdId   :=  PER_ABSENCE_ATTENDANCES_S.NEXTVAL ;--PER_ABSENCE_ATTENDANCES_S.CURRVAL  TransactionRefId,
--P_TransactionRefId   :=  PER_ABSENCE_ATTENDANCES_S.CURRVAL;
--p_AbsenceAction  varchar2(200) :=  'CreateMode';
--p_AbsenceAttendanceTypeId  number :=  64;
--p_AbsenceDays  number :=  1;
--p_DateStart  varchar2(200) :=  '2018-08-03';
--p_DateEnd  varchar2(200) :=  '2018-08-03' ;
--p_DateNotification  varchar2(200) := to_char(sysdate,'yyyy-mm-dd hh:mi:ss');

v_TransactionRefId number := p_AbsenceAttdId;
v_userId number := p_userid;
v_sessionid number := fnd_global.session_id;


   l_xmltype XMLTYPE;
   l_domdoc dbms_xmldom.DOMDocument;
   l_root_node dbms_xmldom.DOMNode;

   l_supp_numEle    dbms_xmldom.DOMElement;
   l_supp_name_element   dbms_xmldom.DOMElement;
  
   l_cnode_element2    dbms_xmldom.DOMElement;--dbms_xmldom.DOMProcessingInstruction;--
   l_cnode_node2        dbms_xmldom.DOMNode;

   l_supp_num_node       dbms_xmldom.DOMNode;
   l_supp_name_node      dbms_xmldom.DOMNode;

   l_supp_num_tnode      dbms_xmldom.DOMNode;
   l_supp_name_tnode     dbms_xmldom.DOMNode;

   l_supp_num_text       dbms_xmldom.DOMText;
   l_supp_name_text      dbms_xmldom.DOMText;

   l_TransCtx_element    dbms_xmldom.DOMElement;
   l_TransCtx_node       dbms_xmldom.DOMNode;
  
   l_TransCache_element    dbms_xmldom.DOMElement;
   l_TransCache_node       dbms_xmldom.DOMNode;
  
   l_AM_element    dbms_xmldom.DOMElement;
   l_AM_node       dbms_xmldom.DOMNode;
  
   l_EoApiMapEO_element    dbms_xmldom.DOMElement;
   l_EoApiMapEO_node       dbms_xmldom.DOMNode; 
   l_EoApiMapEO_name_text dbms_xmldom.DOMText;
   l_EoApiMapEO_name_tnode  dbms_xmldom.DOMNode;
  
   l_cd_element    dbms_xmldom.DOMElement;
   l_cd_node       dbms_xmldom.DOMNode;
  
   l_TXN_element    dbms_xmldom.DOMElement;
   l_TXN_node       dbms_xmldom.DOMNode;
  
   l_EO_element    dbms_xmldom.DOMElement;
   l_EO_node       dbms_xmldom.DOMNode;
  
   l_EO2_element    dbms_xmldom.DOMElement;
   l_EO2_node       dbms_xmldom.DOMNode;
  
   l_TransactionsEORow_element    dbms_xmldom.DOMElement;
   l_TransactionsEORow_node       dbms_xmldom.DOMNode;
  
  
   l_EoApiMap_element    dbms_xmldom.DOMElement;
   l_EoApiMap_node       dbms_xmldom.DOMNode;
  
  
  
   l_PerAbsAttendEORow_element    dbms_xmldom.DOMElement;
   l_PerAbsAttendEORow_node       dbms_xmldom.DOMNode;
  
  
   l_sup_node            dbms_xmldom.DOMNode;
   l_Transaction_node       dbms_xmldom.DOMNode;
   l_sup_element         dbms_xmldom.DOMElement;
  
   l_EOCDATA_name_element       dbms_xmldom.DOMCDataSection;
   l_EOCDATA_name_node       dbms_xmldom.DOMNode;
   l_EOCDATA_name_tnode      dbms_xmldom.DOMNode;
   l_EOCDATA_name_text       dbms_xmldom.DOMText;
  
   l_EOCDATA2_name_element       dbms_xmldom.DOMCDataSection;
   l_EOCDATA2_name_node       dbms_xmldom.DOMNode;
   l_EOCDATA2_name_tnode      dbms_xmldom.DOMNode;
   l_EOCDATA2_name_text       dbms_xmldom.DOMText;
  
   l_PrsnJobName_numEle    dbms_xmldom.DOMElement;
   l_PrsnJobName_num_node       dbms_xmldom.DOMNode;
   l_PrsnJobName_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnJobName_num_text       dbms_xmldom.DOMText;
  
      l_PrsnAsgFlag_numEle    dbms_xmldom.DOMElement;
   l_PrsnAsgFlag_num_node       dbms_xmldom.DOMNode;
   l_PrsnAsgFlag_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnAsgFlag_num_text       dbms_xmldom.DOMText;
  
      l_LoginPrsnNpwFlag_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnNpwFlag_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnNpwFlag_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnNpwFlag_num_text       dbms_xmldom.DOMText;
  
      l_FyiDetails_numEle    dbms_xmldom.DOMElement;
   l_FyiDetails_num_node       dbms_xmldom.DOMNode;
   l_FyiDetails_num_tnode      dbms_xmldom.DOMNode;
   l_FyiDetails_num_text       dbms_xmldom.DOMText;
  
      l_PrsnBgId_numEle    dbms_xmldom.DOMElement;
   l_PrsnBgId_num_node       dbms_xmldom.DOMNode;
   l_PrsnBgId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnBgId_num_text       dbms_xmldom.DOMText;
  
   l_EmpGen_numEle    dbms_xmldom.DOMElement;
   l_EmpGen_num_node       dbms_xmldom.DOMNode;
   l_EmpGen_num_tnode      dbms_xmldom.DOMNode;
   l_EmpGen_num_text       dbms_xmldom.DOMText;
  
  
      l_PrsnNpwFlag_numEle    dbms_xmldom.DOMElement;
   l_PrsnNpwFlag_num_node       dbms_xmldom.DOMNode;
   l_PrsnNpwFlag_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnNpwFlag_num_text       dbms_xmldom.DOMText;
  

      l_ItemType_numEle    dbms_xmldom.DOMElement;
   l_ItemType_num_node       dbms_xmldom.DOMNode;
   l_ItemType_num_tnode      dbms_xmldom.DOMNode;
   l_ItemType_num_text       dbms_xmldom.DOMText;
  
      l_PrsnJobId_numEle    dbms_xmldom.DOMElement;
   l_PrsnJobId_num_node       dbms_xmldom.DOMNode;
   l_PrsnJobId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnJobId_num_text       dbms_xmldom.DOMText;
  
      l_AsgStartDate_numEle    dbms_xmldom.DOMElement;
   l_AsgStartDate_num_node       dbms_xmldom.DOMNode;
   l_AsgStartDate_num_tnode      dbms_xmldom.DOMNode;
   l_AsgStartDate_num_text       dbms_xmldom.DOMText;
  
      l_PrsnGradeId_numEle    dbms_xmldom.DOMElement;
   l_PrsnGradeId_num_node       dbms_xmldom.DOMNode;
   l_PrsnGradeId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnGradeId_num_text       dbms_xmldom.DOMText;
  
  
      l_txnRefId_numEle    dbms_xmldom.DOMElement;
   l_txnRefId_num_node       dbms_xmldom.DOMNode;
   l_txnRefId_num_tnode      dbms_xmldom.DOMNode;
   l_txnRefId_num_text       dbms_xmldom.DOMText;
  
  
      l_LoginPrsnLegCode_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnLegCode_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnLegCode_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnLegCode_num_text       dbms_xmldom.DOMText;
  
      l_PrsnMgrId_numEle    dbms_xmldom.DOMElement;
   l_PrsnMgrId_num_node       dbms_xmldom.DOMNode;
   l_PrsnMgrId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnMgrId_num_text       dbms_xmldom.DOMText;
  
      l_PrsnAssignmentId_numEle    dbms_xmldom.DOMElement;
   l_PrsnAssignmentId_num_node       dbms_xmldom.DOMNode;
   l_PrsnAssignmentId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnAssignmentId_num_text       dbms_xmldom.DOMText;
  
  
      l_PrsnLegCode_numEle    dbms_xmldom.DOMElement;
   l_PrsnLegCode_num_node       dbms_xmldom.DOMNode;
   l_PrsnLegCode_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnLegCode_num_text       dbms_xmldom.DOMText;
  
  
      l_LoginPrsnId_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnId_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnId_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnId_num_text       dbms_xmldom.DOMText;
  
  
      l_LogPrsnCon_numEle    dbms_xmldom.DOMElement;
   l_LogPrsnCon_num_node       dbms_xmldom.DOMNode;
   l_LogPrsnCon_num_tnode      dbms_xmldom.DOMNode;
   l_LogPrsnCon_num_text       dbms_xmldom.DOMText;
  
  
      l_pNtfSubMsg_numEle    dbms_xmldom.DOMElement;
   l_pNtfSubMsg_num_node       dbms_xmldom.DOMNode;
   l_pNtfSubMsg_num_tnode      dbms_xmldom.DOMNode;
   l_pNtfSubMsg_num_text       dbms_xmldom.DOMText;
  
      l_ProductCode_numEle    dbms_xmldom.DOMElement;
   l_ProductCode_num_node       dbms_xmldom.DOMNode;
   l_ProductCode_num_tnode      dbms_xmldom.DOMNode;
   l_ProductCode_num_text       dbms_xmldom.DOMText;
  
      l_EffectiveDate_numEle    dbms_xmldom.DOMElement;
   l_EffectiveDate_num_node       dbms_xmldom.DOMNode;
   l_EffectiveDate_num_tnode      dbms_xmldom.DOMNode;
   l_EffectiveDate_num_text       dbms_xmldom.DOMText;
  
  
   l_SSHR_WF_BASED_numEle    dbms_xmldom.DOMElement;
   l_SSHR_WF_BASED_num_node       dbms_xmldom.DOMNode;
   l_SSHR_WF_BASED_num_tnode      dbms_xmldom.DOMNode;
   l_SSHR_WF_BASED_num_text       dbms_xmldom.DOMText;   
  
   l_HeaderType_numEle    dbms_xmldom.DOMElement;
   l_HeaderType_num_node       dbms_xmldom.DOMNode;
   l_HeaderType_num_tnode      dbms_xmldom.DOMNode;
   l_HeaderType_num_text       dbms_xmldom.DOMText;   
  

   l_PrsnBgCurrencyCode_numEle    dbms_xmldom.DOMElement;
   l_PrsnBgCurrencyCode_num_node       dbms_xmldom.DOMNode;
   l_PrsnBgCurrencyCode_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnBgCurrencyCode_num_text       dbms_xmldom.DOMText;   
  
  
   l_LoginWorkerNumber_numEle    dbms_xmldom.DOMElement;
   l_LoginWorkerNumber_num_node       dbms_xmldom.DOMNode;
   l_LoginWorkerNumber_num_tnode      dbms_xmldom.DOMNode;
   l_LoginWorkerNumber_num_text       dbms_xmldom.DOMText;   

   l_TxnStatus_numEle    dbms_xmldom.DOMElement;
   l_TxnStatus_num_node       dbms_xmldom.DOMNode;
   l_TxnStatus_num_tnode      dbms_xmldom.DOMNode;
   l_TxnStatus_num_text       dbms_xmldom.DOMText;   

   l_PrsnPositionId_numEle    dbms_xmldom.DOMElement;
   l_PrsnPositionId_num_node       dbms_xmldom.DOMNode;
   l_PrsnPositionId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnPositionId_num_text       dbms_xmldom.DOMText;   
  

   l_prsnOrgId_numEle    dbms_xmldom.DOMElement;
   l_prsnOrgId_num_node       dbms_xmldom.DOMNode;
   l_prsnOrgId_num_tnode      dbms_xmldom.DOMNode;
   l_prsnOrgId_num_text       dbms_xmldom.DOMText;   
  
  
   l_PrsnMgrName_numEle    dbms_xmldom.DOMElement;
   l_PrsnMgrName_num_node       dbms_xmldom.DOMNode;
   l_PrsnMgrName_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnMgrName_num_text       dbms_xmldom.DOMText;   

   l_PrsnEmpFlag_numEle    dbms_xmldom.DOMElement;
   l_PrsnEmpFlag_num_node       dbms_xmldom.DOMNode;
   l_PrsnEmpFlag_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnEmpFlag_num_text       dbms_xmldom.DOMText;   
  
  

   l_PrsnPositionName_numEle    dbms_xmldom.DOMElement;
   l_PrsnPositionName_num_node       dbms_xmldom.DOMNode;
   l_PrsnPositionName_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnPositionName_num_text       dbms_xmldom.DOMText;   
  
   l_PrsnContextSet_numEle    dbms_xmldom.DOMElement;
   l_PrsnContextSet_num_node       dbms_xmldom.DOMNode;
   l_PrsnContextSet_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnContextSet_num_text       dbms_xmldom.DOMText;   

   l_LoginPrsnMgrName_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnMgrName_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnMgrName_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnMgrName_num_text       dbms_xmldom.DOMText;  
  
  
   l_PrsnKfstrCode_numEle    dbms_xmldom.DOMElement;
   l_PrsnKfstrCode_num_node       dbms_xmldom.DOMNode;
   l_PrsnKfstrCode_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnKfstrCode_num_text       dbms_xmldom.DOMText;   
  
  
   l_PrsnLocationId_numEle    dbms_xmldom.DOMElement;
   l_PrsnLocationId_num_node       dbms_xmldom.DOMNode;
   l_PrsnLocationId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnLocationId_num_text       dbms_xmldom.DOMText;   

   l_TransactionId_numEle    dbms_xmldom.DOMElement;
   l_TransactionId_num_node       dbms_xmldom.DOMNode;
   l_TransactionId_num_tnode      dbms_xmldom.DOMNode;
   l_TransactionId_num_text       dbms_xmldom.DOMText;   
  
   l_ReviewTemRNAtt_numEle    dbms_xmldom.DOMElement;
   l_ReviewTemRNAtt_num_node       dbms_xmldom.DOMNode;
   l_ReviewTemRNAtt_num_tnode      dbms_xmldom.DOMNode;
   l_ReviewTemRNAtt_num_text       dbms_xmldom.DOMText;   
   
   l_pCalledId_numEle    dbms_xmldom.DOMElement;
   l_pCalledId_num_node       dbms_xmldom.DOMNode;
   l_pCalledId_num_tnode      dbms_xmldom.DOMNode;
   l_pCalledId_num_text       dbms_xmldom.DOMText;   

   l_LoginPrsnType_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnType_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnType_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnType_num_text       dbms_xmldom.DOMText;   
  
   l_LoginPrsnEmpFlag_numEle    dbms_xmldom.DOMElement;
   l_LoginPrsnEmpFlag_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnEmpFlag_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnEmpFlag_num_text       dbms_xmldom.DOMText;
      

   l_SS_RevFLMode_numEle   dbms_xmldom.DOMElement;
   l_SS_RevFLMode_num_node       dbms_xmldom.DOMNode;
   l_SS_RevFLMode_num_tnode      dbms_xmldom.DOMNode;
   l_SS_RevFLMode_num_text       dbms_xmldom.DOMText;  

   l_pAMEAppId_numEle   dbms_xmldom.DOMElement;
   l_pAMEAppId_num_node       dbms_xmldom.DOMNode;
   l_pAMEAppId_num_tnode      dbms_xmldom.DOMNode;
   l_pAMEAppId_num_text       dbms_xmldom.DOMText;  
  
  
   l_PrsnName_numEle   dbms_xmldom.DOMElement;
   l_PrsnName_num_node       dbms_xmldom.DOMNode;
   l_PrsnName_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnName_num_text       dbms_xmldom.DOMText;   

   l_PrsnPayrollId_numEle   dbms_xmldom.DOMElement;
   l_PrsnPayrollId_num_node       dbms_xmldom.DOMNode;
   l_PrsnPayrollId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnPayrollId_num_text       dbms_xmldom.DOMText;   
  
   l_ProcessName_numEle   dbms_xmldom.DOMElement;
   l_ProcessName_num_node       dbms_xmldom.DOMNode;
   l_ProcessName_num_tnode      dbms_xmldom.DOMNode;
   l_ProcessName_num_text       dbms_xmldom.DOMText;   

   l_PerzFunctionName_numEle   dbms_xmldom.DOMElement;
   l_PerzFunctionName_num_node       dbms_xmldom.DOMNode;
   l_PerzFunctionName_num_tnode      dbms_xmldom.DOMNode;
   l_PerzFunctionName_num_text       dbms_xmldom.DOMText;   
  
  
  
   l_LoginPrsnBgId_numEle   dbms_xmldom.DOMElement;
   l_LoginPrsnBgId_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnBgId_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnBgId_num_text       dbms_xmldom.DOMText;   

   l_pCalledFrom_numEle   dbms_xmldom.DOMElement;
   l_pCalledFrom_num_node       dbms_xmldom.DOMNode;
   l_pCalledFrom_num_tnode      dbms_xmldom.DOMNode;
   l_pCalledFrom_num_text       dbms_xmldom.DOMText;  
  
   l_TxnIdentifier_numEle   dbms_xmldom.DOMElement;
   l_TxnIdentifier_num_node       dbms_xmldom.DOMNode;
   l_TxnIdentifier_num_tnode      dbms_xmldom.DOMNode;
   l_TxnIdentifier_num_text       dbms_xmldom.DOMText;   
  
   l_TxnRefTab_numEle   dbms_xmldom.DOMElement;
   l_TxnRefTab_num_node       dbms_xmldom.DOMNode;
   l_TxnRefTab_num_tnode      dbms_xmldom.DOMNode;
   l_TxnRefTab_num_text       dbms_xmldom.DOMText;   
  
   L_PerzLocCode_numEle   dbms_xmldom.DOMElement;
   L_PerzLocCode_num_node       dbms_xmldom.DOMNode;
   L_PerzLocCode_num_tnode      dbms_xmldom.DOMNode;
   L_PerzLocCode_num_text       dbms_xmldom.DOMText;   
  
   l_RelaunchFunction_numEle   dbms_xmldom.DOMElement;
   l_RelaunchFunction_num_node       dbms_xmldom.DOMNode;
   l_RelaunchFunction_num_tnode      dbms_xmldom.DOMNode;
   l_RelaunchFunction_num_text       dbms_xmldom.DOMText;   

   l_pAMETranType_numEle   dbms_xmldom.DOMElement;
   l_pAMETranType_num_node       dbms_xmldom.DOMNode;
   l_pAMETranType_num_tnode      dbms_xmldom.DOMNode;
   l_pAMETranType_num_text       dbms_xmldom.DOMText;   

   l_pApprovalReqd_numEle   dbms_xmldom.DOMElement;
   l_pApprovalReqd_num_node       dbms_xmldom.DOMNode;
   l_pApprovalReqd_num_tnode      dbms_xmldom.DOMNode;
   l_pApprovalReqd_num_text       dbms_xmldom.DOMText; 
  
   l_NtfAttachAttr_numEle   dbms_xmldom.DOMElement;
   l_NtfAttachAttr_num_node       dbms_xmldom.DOMNode;
   l_NtfAttachAttr_num_tnode      dbms_xmldom.DOMNode;
   l_NtfAttachAttr_num_text       dbms_xmldom.DOMText;   
  
   l_TransactionType_numEle   dbms_xmldom.DOMElement;
   l_TransactionType_num_node       dbms_xmldom.DOMNode;
   l_TransactionType_num_tnode      dbms_xmldom.DOMNode;
   l_TransactionType_num_text       dbms_xmldom.DOMText; 

   l_PrsnId_numEle   dbms_xmldom.DOMElement;
   l_PrsnId_num_node       dbms_xmldom.DOMNode;
   l_PrsnId_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnId_num_text       dbms_xmldom.DOMText;   

   l_CreatorPrsnId_numEle   dbms_xmldom.DOMElement;
   l_CreatorPrsnId_num_node       dbms_xmldom.DOMNode;
   l_CreatorPrsnId_num_tnode      dbms_xmldom.DOMNode;
   l_CreatorPrsnId_num_text       dbms_xmldom.DOMText;   

   l_LoginPrsnName_numEle   dbms_xmldom.DOMElement;
   l_LoginPrsnName_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnName_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnName_num_text       dbms_xmldom.DOMText;   

   l_PerzOrgId_numEle   dbms_xmldom.DOMElement;
   l_PerzOrgId_num_node       dbms_xmldom.DOMNode;
   l_PerzOrgId_num_tnode      dbms_xmldom.DOMNode;
   l_PerzOrgId_num_text       dbms_xmldom.DOMText;   

   l_PrsnType_numEle   dbms_xmldom.DOMElement;
   l_PrsnType_num_node       dbms_xmldom.DOMNode;
   l_PrsnType_num_tnode      dbms_xmldom.DOMNode;
   l_PrsnType_num_text       dbms_xmldom.DOMText;   
  
   l_AttachCheck_numEle   dbms_xmldom.DOMElement;
   l_AttachCheck_num_node       dbms_xmldom.DOMNode;
   l_AttachCheck_num_tnode      dbms_xmldom.DOMNode;
   l_AttachCheck_num_text       dbms_xmldom.DOMText;   
  
   l_LoginPrsnMgrId_numEle   dbms_xmldom.DOMElement;
   l_LoginPrsnMgrId_num_node       dbms_xmldom.DOMNode;
   l_LoginPrsnMgrId_num_tnode      dbms_xmldom.DOMNode;
   l_LoginPrsnMgrId_num_text       dbms_xmldom.DOMText; 
  
   l_prsnSecGrpId_numEle   dbms_xmldom.DOMElement;
   l_prsnSecGrpId_num_node       dbms_xmldom.DOMNode;
   l_prsnSecGrpId_num_tnode      dbms_xmldom.DOMNode;
   l_prsnSecGrpId_num_text       dbms_xmldom.DOMText; 
  
          l_AbsenceAction_numEle    dbms_xmldom.DOMElement;
   l_AbsenceAction_num_node       dbms_xmldom.DOMNode;
   l_AbsenceAction_num_tnode      dbms_xmldom.DOMNode;
   l_AbsenceAction_num_text       dbms_xmldom.DOMText;
   
   l_AbsenceAttdId_numEle    dbms_xmldom.DOMElement;
   l_AbsenceAttdId_num_node       dbms_xmldom.DOMNode;
   l_AbsenceAttdId_num_tnode      dbms_xmldom.DOMNode;
   l_AbsenceAttdId_num_text       dbms_xmldom.DOMText;

   l_AbsAttendReaId_numEle    dbms_xmldom.DOMElement;
   l_AbsAttendReaId_num_node       dbms_xmldom.DOMNode;
   l_AbsAttendReaId_num_tnode      dbms_xmldom.DOMNode;
   l_AbsAttendReaId_num_text       dbms_xmldom.DOMText;

--p_AbsAttendanceReasonId

   l_OVN_numEle    dbms_xmldom.DOMElement;
   l_OVN_num_node       dbms_xmldom.DOMNode;
   l_OVN_num_tnode      dbms_xmldom.DOMNode;
   l_OVN_num_text       dbms_xmldom.DOMText;
  
      l_Comments_numEle    dbms_xmldom.DOMElement;
   l_Comments_num_node       dbms_xmldom.DOMNode;
   l_Comments_num_tnode      dbms_xmldom.DOMNode;
   l_Comments_num_text       dbms_xmldom.DOMText;
  
   l_LinkAbsIdAbsEO_numEle dbms_xmldom.DOMElement;
   l_LinkAbsIdAbsEO_num_node  dbms_xmldom.DOMNode; 

  
   l_CreationDate_numEle    dbms_xmldom.DOMElement;
   l_CreationDate_num_node       dbms_xmldom.DOMNode;
   l_CreationDate_num_tnode      dbms_xmldom.DOMNode;
   l_CreationDate_num_text       dbms_xmldom.DOMText;  

  
   l_CreatedBy_numEle    dbms_xmldom.DOMElement;
   l_CreatedBy_num_node       dbms_xmldom.DOMNode;
   l_CreatedBy_num_tnode      dbms_xmldom.DOMNode;
   l_CreatedBy_num_text       dbms_xmldom.DOMText;
     

  
   l_LastUpdateLogin_numEle    dbms_xmldom.DOMElement;
   l_LastUpdateLogin_num_node       dbms_xmldom.DOMNode;
   l_LastUpdateLogin_num_tnode      dbms_xmldom.DOMNode;
   l_LastUpdateLogin_num_text       dbms_xmldom.DOMText;
     

  
   l_LastUpdatedBy_numEle    dbms_xmldom.DOMElement;
   l_LastUpdatedBy_num_node       dbms_xmldom.DOMNode;
   l_LastUpdatedBy_num_tnode      dbms_xmldom.DOMNode;
   l_LastUpdatedBy_num_text       dbms_xmldom.DOMText;  
     

  
   l_LastUpdateDate_numEle    dbms_xmldom.DOMElement;
   l_LastUpdateDate_num_node       dbms_xmldom.DOMNode;
   l_LastUpdateDate_num_tnode      dbms_xmldom.DOMNode;
   l_LastUpdateDate_num_text       dbms_xmldom.DOMText;

  
   l_TimeProjSt_numEle    dbms_xmldom.DOMElement;
   l_TimeProjSt_num_node       dbms_xmldom.DOMNode;
   l_TimeProjSt_num_tnode      dbms_xmldom.DOMNode;
   l_TimeProjSt_num_text       dbms_xmldom.DOMText; 

  
   l_TimeProjEndnumEle    dbms_xmldom.DOMElement;
   l_TimeProjEndnum_node       dbms_xmldom.DOMNode;
   l_TimeProjEndnum_tnode      dbms_xmldom.DOMNode;
   l_TimeProjEndnum_text       dbms_xmldom.DOMText;
     

  
   l_DateStart_numEle    dbms_xmldom.DOMElement;
   l_DateStart_num_node       dbms_xmldom.DOMNode;
   l_DateStart_num_tnode      dbms_xmldom.DOMNode;
   l_DateStart_num_text       dbms_xmldom.DOMText;
     

  
   l_DatePrjStrt_numEle    dbms_xmldom.DOMElement;
   l_DatePrjStrt_num_node       dbms_xmldom.DOMNode;
   l_DatePrjStrt_num_tnode      dbms_xmldom.DOMNode;
   l_DatePrjStrt_num_text       dbms_xmldom.DOMText;
     

  
   l_DateProjEnd_numEle    dbms_xmldom.DOMElement;
   l_DateProjEnd_num_node       dbms_xmldom.DOMNode;
   l_DateProjEnd_num_tnode      dbms_xmldom.DOMNode;
   l_DateProjEnd_num_text       dbms_xmldom.DOMText;
     

  
   l_DateNoti_numEle    dbms_xmldom.DOMElement;
   l_DateNoti_num_node       dbms_xmldom.DOMNode;
   l_DateNoti_num_tnode      dbms_xmldom.DOMNode;
   l_DateNoti_num_text       dbms_xmldom.DOMText;
     

  
   l_DateEnd_numEle    dbms_xmldom.DOMElement;
   l_DateEnd_num_node       dbms_xmldom.DOMNode;
   l_DateEnd_num_tnode      dbms_xmldom.DOMNode;
   l_DateEnd_num_text       dbms_xmldom.DOMText;

     

  
   l_AbsenceHours_numEle    dbms_xmldom.DOMElement;
   l_AbsenceHours_num_node       dbms_xmldom.DOMNode;
   l_AbsenceHours_num_tnode      dbms_xmldom.DOMNode;
   l_AbsenceHours_num_text       dbms_xmldom.DOMText;
     

  
   l_AbsenceDays_numEle    dbms_xmldom.DOMElement;
   l_AbsenceDays_num_node       dbms_xmldom.DOMNode;
   l_AbsenceDays_num_tnode      dbms_xmldom.DOMNode;
   l_AbsenceDays_num_text       dbms_xmldom.DOMText;
     

  
   l_PersonId_numEle    dbms_xmldom.DOMElement;
   l_PersonId_num_node       dbms_xmldom.DOMNode;
   l_PersonId_num_tnode      dbms_xmldom.DOMNode;
   l_PersonId_num_text       dbms_xmldom.DOMText;
     

  
   l_AbsAttTypeId_numEle    dbms_xmldom.DOMElement;
   l_AbsAttTypeId_num_node       dbms_xmldom.DOMNode;
   l_AbsAttTypeId_num_tnode      dbms_xmldom.DOMNode;
   l_AbsAttTypeId_num_text       dbms_xmldom.DOMText;
   
       l_BusinessGroupId_numEle    dbms_xmldom.DOMElement;
   l_BusinessGroupId_num_node       dbms_xmldom.DOMNode;
   l_BusinessGroupId_num_tnode      dbms_xmldom.DOMNode;
   l_BusinessGroupId_num_text       dbms_xmldom.DOMText;
   
   l_AbsAttId_numEle    dbms_xmldom.DOMElement;
   l_AbsAttId_num_node       dbms_xmldom.DOMNode;
   l_AbsAttId_num_tnode      dbms_xmldom.DOMNode;
   l_AbsAttId_num_text       dbms_xmldom.DOMText;

BEGIN

   -- Create an empty XML document
   l_domdoc := dbms_xmldom.newDomDocument;

   -- Create a root node
   l_root_node := dbms_xmldom.makeNode(l_domdoc);

   -- Create a new Supplier Node and add it to the root node
   --l_sup_node := dbms_xmldom.appendChild( l_root_node , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'SUPPLIER_ADDRESS')));
   l_Transaction_node := dbms_xmldom.appendChild( l_root_node , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Transaction')));

FOR sup_rec IN (SELECT A.*
,'000100000004'||RAWTOHEX(PER_ABSENCE_ATTENDANCES_S.CURRVAL) CDATA 
FROM FUJ_SSHR_PERSINFO_mgr A
where PrsnId = p_PrsnId
)
  LOOP

    -- For each record, create a new Supplier element
    -- and add this new Supplier element to the Supplier Parent node
    l_TransCtx_element := dbms_xmldom.createElement(l_domdoc, 'TransCtx' );
    l_TransCtx_node    := dbms_xmldom.appendChild(l_Transaction_node,dbms_xmldom.makeNode(l_TransCtx_element));


    l_EoApiMap_element := dbms_xmldom.createElement(l_domdoc, 'EoApiMap' );
    l_EoApiMap_node    := dbms_xmldom.appendChild(l_Transaction_node,dbms_xmldom.makeNode(l_EoApiMap_element));


    l_EoApiMapEO_element := dbms_xmldom.createElement(l_domdoc, 'EO' );
    dbms_xmldom.setAttribute(l_EoApiMapEO_element,'Name', 'oracle.apps.per.schema.server.PerAbsenceAttendancesEO');
    l_EoApiMapEO_node    := dbms_xmldom.appendChild(l_EoApiMap_node,dbms_xmldom.makeNode(l_EoApiMapEO_element));
    l_EoApiMapEO_name_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_PERSON_ABSENCE_SWI.PROCESS_API' );
    l_EoApiMapEO_name_tnode   := dbms_xmldom.appendChild(l_EoApiMapEO_node,dbms_xmldom.makeNode(l_EoApiMapEO_name_text));


  

    l_TransCache_element := dbms_xmldom.createElement(l_domdoc, 'TransCache' );
    l_TransCache_node    := dbms_xmldom.appendChild(l_Transaction_node,dbms_xmldom.makeNode(l_TransCache_element));

   
    l_AM_element := dbms_xmldom.createElement(l_domdoc, 'AM' );
    dbms_xmldom.setAttribute(l_AM_element,'MomVer', '1044362310593');
    l_AM_node    := dbms_xmldom.appendChild(l_TransCache_node,dbms_xmldom.makeNode(l_AM_element));
   
    l_cd_element := dbms_xmldom.createElement(l_domdoc, 'cd' );
    l_cd_node    := dbms_xmldom.appendChild(l_AM_node,dbms_xmldom.makeNode(l_cd_element));
   
    l_TXN_element := dbms_xmldom.createElement(l_domdoc, 'TXN' );
    dbms_xmldom.setAttribute(l_TXN_element,'Def', '0');
    dbms_xmldom.setAttribute(l_TXN_element,'New', '0');
    dbms_xmldom.setAttribute(l_TXN_element,'Lok', '2');
    dbms_xmldom.setAttribute(l_TXN_element,'pcid', '91');
    l_TXN_node    := dbms_xmldom.appendChild(l_AM_node,dbms_xmldom.makeNode(l_TXN_element));
   
    l_EO_element := dbms_xmldom.createElement(l_domdoc, 'EO' );
    dbms_xmldom.setAttribute(l_EO_element,'Name', 'oracle.apps.per.schema.server.PerAbsenceAttendancesEO');
    l_EO_node    := dbms_xmldom.appendChild(l_TXN_node,dbms_xmldom.makeNode(l_EO_element));

    l_EOCDATA_name_element := dbms_xmldom.createCDATASection(l_domdoc, sup_rec.CDATA );
    l_EOCDATA_name_node    := dbms_xmldom.appendChild(l_EO_node,dbms_xmldom.makeNode(l_EOCDATA_name_element));
    l_EOCDATA_name_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.CDATA);
    l_EOCDATA_name_tnode   := dbms_xmldom.appendChild(l_EOCDATA_name_node,dbms_xmldom.makeNode(l_EOCDATA_name_text));
   
    l_PerAbsAttendEORow_element := dbms_xmldom.createElement(l_domdoc, 'PerAbsenceAttendancesEORow' );
    dbms_xmldom.setAttribute(l_PerAbsAttendEORow_element,'PS', '0');
    dbms_xmldom.setAttribute(l_PerAbsAttendEORow_element,'PK', 'Y');
    l_PerAbsAttendEORow_node    := dbms_xmldom.appendChild(l_EO_node,dbms_xmldom.makeNode(l_PerAbsAttendEORow_element));
   
    l_EO2_element := dbms_xmldom.createElement(l_domdoc, 'EO' );
    dbms_xmldom.setAttribute(l_EO2_element,'Name', 'oracle.apps.per.schema.server.TransactionsEO');
    l_EO2_node    := dbms_xmldom.appendChild(l_TXN_node,dbms_xmldom.makeNode(l_EO2_element));
   
    l_EOCDATA2_name_element := dbms_xmldom.createCDATASection(l_domdoc, sup_rec.CDATA);
    l_EOCDATA2_name_node    := dbms_xmldom.appendChild(l_EO2_node,dbms_xmldom.makeNode(l_EOCDATA2_name_element));
    l_EOCDATA2_name_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.CDATA );
    l_EOCDATA2_name_tnode   := dbms_xmldom.appendChild(l_EOCDATA2_name_node,dbms_xmldom.makeNode(l_EOCDATA2_name_text));
   
   
   
    l_TransactionsEORow_element := dbms_xmldom.createElement(l_domdoc, 'TransactionsEORow' );
    dbms_xmldom.setAttribute(l_TransactionsEORow_element,'PS', '0');
    l_TransactionsEORow_node    := dbms_xmldom.appendChild(l_EO2_node,dbms_xmldom.makeNode(l_TransactionsEORow_element));
   
   
    -- Each Supplier node will get a Number node which contains the Supplier Number as text
    l_supp_numEle := dbms_xmldom.createElement(l_domdoc, 'TransactionGroup' );
    l_supp_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_supp_numEle));
    l_supp_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'ABSENCE_MGMT' );
    l_supp_num_tnode   := dbms_xmldom.appendChild(l_supp_num_node,dbms_xmldom.makeNode(l_supp_num_text));


  
   l_PrsnJobName_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnJobName' ); --l_domdoc constant
   l_PrsnJobName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnJobName_numEle));--l_TransCtx_node parent
   l_PrsnJobName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnJobName );--column of cursor
   l_PrsnJobName_num_tnode   := dbms_xmldom.appendChild(l_PrsnJobName_num_node,dbms_xmldom.makeNode(l_PrsnJobName_num_text));
   
   l_PrsnNpwFlag_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnNpwFlag' ); --l_domdoc constant
   l_PrsnNpwFlag_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnNpwFlag_numEle));--l_TransCtx_node parent
   l_PrsnNpwFlag_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'A' );--column of cursor
   l_PrsnNpwFlag_num_tnode   := dbms_xmldom.appendChild(l_PrsnNpwFlag_num_node,dbms_xmldom.makeNode(l_PrsnNpwFlag_num_text));
   
  
  
  
   l_EmpGen_numEle := dbms_xmldom.createElement(l_domdoc, 'EmployeeGeneration' ); --l_domdoc constant
   l_EmpGen_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_EmpGen_numEle));--l_TransCtx_node parent
   l_EmpGen_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'A' );--column of cursor
   l_EmpGen_num_tnode   := dbms_xmldom.appendChild(l_EmpGen_num_node,dbms_xmldom.makeNode(l_EmpGen_num_text));
   
  
   l_PrsnBgId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnBgId' ); --l_domdoc constant
   l_PrsnBgId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnBgId_numEle));--l_TransCtx_node parent
   l_PrsnBgId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNBGID );--column of cursor
   l_PrsnBgId_num_tnode   := dbms_xmldom.appendChild(l_PrsnBgId_num_node,dbms_xmldom.makeNode(l_PrsnBgId_num_text));
   
  
  
   l_FyiDetails_numEle := dbms_xmldom.createElement(l_domdoc, 'FyiDetails' ); --l_domdoc constant
   l_FyiDetails_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_FyiDetails_numEle));--l_TransCtx_node parent
   l_FyiDetails_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'Y' );--column of cursor
   l_FyiDetails_num_tnode   := dbms_xmldom.appendChild(l_FyiDetails_num_node,dbms_xmldom.makeNode(l_FyiDetails_num_text));
   
  
  
  
   l_LoginPrsnNpwFlag_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnNpwFlag' ); --l_domdoc constant
   l_LoginPrsnNpwFlag_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnNpwFlag_numEle));--l_TransCtx_node parent
   l_LoginPrsnNpwFlag_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'A' );--column of cursor
   l_LoginPrsnNpwFlag_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnNpwFlag_num_node,dbms_xmldom.makeNode(l_LoginPrsnNpwFlag_num_text));
    
  
  
   l_PrsnAsgFlag_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnAsgFlag' ); --l_domdoc constant
   l_PrsnAsgFlag_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnAsgFlag_numEle));--l_TransCtx_node parent
   l_PrsnAsgFlag_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'Y' );--column of cursor
   l_PrsnAsgFlag_num_tnode   := dbms_xmldom.appendChild(l_PrsnAsgFlag_num_node,dbms_xmldom.makeNode(l_PrsnAsgFlag_num_text));
   

  
  
   l_EffectiveDate_numEle := dbms_xmldom.createElement(l_domdoc, 'EffectiveDate' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_EffectiveDate_numEle,'dataType', 'd');
   l_EffectiveDate_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_EffectiveDate_numEle));--l_TransCtx_node parent
   l_EffectiveDate_num_text    := dbms_xmldom.createTextNode(l_domdoc, to_char(sysdate,'yyyy-mm-dd'));--column of cursor
   l_EffectiveDate_num_tnode   := dbms_xmldom.appendChild(l_EffectiveDate_num_node,dbms_xmldom.makeNode(l_EffectiveDate_num_text));
   
  
   l_ProductCode_numEle := dbms_xmldom.createElement(l_domdoc, 'ProductCode' ); --l_domdoc constant
   l_ProductCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_ProductCode_numEle));--l_TransCtx_node parent
   l_ProductCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'PER' );--column of cursor
   l_ProductCode_num_tnode   := dbms_xmldom.appendChild(l_ProductCode_num_node,dbms_xmldom.makeNode(l_ProductCode_num_text));
   
  
   l_pNtfSubMsg_numEle := dbms_xmldom.createElement(l_domdoc, 'pNtfSubMsg' ); --l_domdoc constant
   l_pNtfSubMsg_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pNtfSubMsg_numEle));--l_TransCtx_node parent
   l_pNtfSubMsg_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_ABS_NTF_SUB_MSG' );--column of cursor
   l_pNtfSubMsg_num_tnode   := dbms_xmldom.appendChild(l_pNtfSubMsg_num_node,dbms_xmldom.makeNode(l_pNtfSubMsg_num_text));
   
    l_LogPrsnCon_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnContextSet' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_LogPrsnCon_numEle,'dataType', 'b');
   l_LogPrsnCon_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LogPrsnCon_numEle));--l_TransCtx_node parent
   l_LogPrsnCon_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'true');--column of cursor
   l_LogPrsnCon_num_tnode   := dbms_xmldom.appendChild(l_LogPrsnCon_num_node,dbms_xmldom.makeNode(l_LogPrsnCon_num_text));
   
   l_LoginPrsnId_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnId' ); --l_domdoc constant
   l_LoginPrsnId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnId_numEle));--l_TransCtx_node parent
   l_LoginPrsnId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_LoginPrsnId );--column of cursor
   l_LoginPrsnId_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnId_num_node,dbms_xmldom.makeNode(l_LoginPrsnId_num_text));
   
  
   l_PrsnLegCode_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnLegCode' ); --l_domdoc constant
   l_PrsnLegCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnLegCode_numEle));--l_TransCtx_node parent
   l_PrsnLegCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'AE' );--column of cursor
   l_PrsnLegCode_num_tnode   := dbms_xmldom.appendChild(l_PrsnLegCode_num_node,dbms_xmldom.makeNode(l_PrsnLegCode_num_text));
   
  
   l_PrsnAssignmentId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnAssignmentId' ); --l_domdoc constant
   l_PrsnAssignmentId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnAssignmentId_numEle));--l_TransCtx_node parent
   l_PrsnAssignmentId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnAssignmentId );--column of cursor
   l_PrsnAssignmentId_num_tnode   := dbms_xmldom.appendChild(l_PrsnAssignmentId_num_node,dbms_xmldom.makeNode(l_PrsnAssignmentId_num_text));
   
  
   l_PrsnMgrId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnMgrId' ); --l_domdoc constant
   l_PrsnMgrId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnMgrId_numEle));--l_TransCtx_node parent
   l_PrsnMgrId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnMgrId );--column of cursor
   l_PrsnMgrId_num_tnode   := dbms_xmldom.appendChild(l_PrsnMgrId_num_node,dbms_xmldom.makeNode(l_PrsnMgrId_num_text));
   
  
   l_LoginPrsnLegCode_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnLegCode' ); --l_domdoc constant
   l_LoginPrsnLegCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnLegCode_numEle));--l_TransCtx_node parent
   l_LoginPrsnLegCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'AE' );--column of cursor
   l_LoginPrsnLegCode_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnLegCode_num_node,dbms_xmldom.makeNode(l_LoginPrsnLegCode_num_text));
   
  
   l_txnRefId_numEle := dbms_xmldom.createElement(l_domdoc, 'TransactionRefId' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_txnRefId_numEle,'dataType', 'n');
   l_txnRefId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_txnRefId_numEle));--l_TransCtx_node parent
   l_txnRefId_num_text    := dbms_xmldom.createTextNode(l_domdoc, v_TransactionRefId);--column of cursor
   l_txnRefId_num_tnode   := dbms_xmldom.appendChild(l_txnRefId_num_node,dbms_xmldom.makeNode(l_txnRefId_num_text));
   
  
   l_PrsnGradeId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnGradeId' ); --l_domdoc constant
   l_PrsnGradeId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnGradeId_numEle));--l_TransCtx_node parent
   l_PrsnGradeId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnGradeId );--column of cursor
   l_PrsnGradeId_num_tnode   := dbms_xmldom.appendChild(l_PrsnGradeId_num_node,dbms_xmldom.makeNode(l_PrsnGradeId_num_text));
   
   
   
  
   l_AsgStartDate_numEle := dbms_xmldom.createElement(l_domdoc, 'AsgStartDate' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_AsgStartDate_numEle,'dataType', 'd');
   l_AsgStartDate_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_AsgStartDate_numEle));--l_TransCtx_node parent
   l_AsgStartDate_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.AsgStartDate );--column of cursor
   l_AsgStartDate_num_tnode   := dbms_xmldom.appendChild(l_AsgStartDate_num_node,dbms_xmldom.makeNode(l_AsgStartDate_num_text));
   
   
   
  
   l_PrsnJobId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnJobId' ); --l_domdoc constant
   l_PrsnJobId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnJobId_numEle));--l_TransCtx_node parent
   l_PrsnJobId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnJobId );--column of cursor
   l_PrsnJobId_num_tnode   := dbms_xmldom.appendChild(l_PrsnJobId_num_node,dbms_xmldom.makeNode(l_PrsnJobId_num_text));
   
  
   l_ItemType_numEle := dbms_xmldom.createElement(l_domdoc, 'ItemType' ); --l_domdoc constant
   l_ItemType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_ItemType_numEle));--l_TransCtx_node parent
   l_ItemType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HRSSA' );--column of cursor
   l_ItemType_num_tnode   := dbms_xmldom.appendChild(l_ItemType_num_node,dbms_xmldom.makeNode(l_ItemType_num_text));
   
   l_PrsnContextSet_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnContextSet' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_PrsnContextSet_numEle,'dataType', 'b');
   l_PrsnContextSet_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnContextSet_numEle));--l_TransCtx_node parent
   l_PrsnContextSet_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'true' );--column of cursor
   l_PrsnContextSet_num_tnode   := dbms_xmldom.appendChild(l_PrsnContextSet_num_node,dbms_xmldom.makeNode(l_PrsnContextSet_num_text));
  
  
   l_LoginPrsnEmpFlag_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnEmpFlag' ); --l_domdoc constant
   l_LoginPrsnEmpFlag_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnEmpFlag_numEle));--l_TransCtx_node parent
   l_LoginPrsnEmpFlag_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'A' );--column of cursor
   l_LoginPrsnEmpFlag_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnEmpFlag_num_node,dbms_xmldom.makeNode(l_LoginPrsnEmpFlag_num_text));
  
   l_LoginPrsnType_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnType' ); --l_domdoc constant
   l_LoginPrsnType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnType_numEle));--l_TransCtx_node parent
   l_LoginPrsnType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'E' );--column of cursor
   l_LoginPrsnType_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnType_num_node,dbms_xmldom.makeNode(l_LoginPrsnType_num_text));
  
   l_pCalledId_numEle := dbms_xmldom.createElement(l_domdoc, 'pCalledId' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_pCalledId_numEle,'dataType', 'n');
   l_pCalledId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pCalledId_numEle));--l_TransCtx_node parent
   l_pCalledId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_pCalledId  );--column of cursor
   l_pCalledId_num_tnode   := dbms_xmldom.appendChild(l_pCalledId_num_node,dbms_xmldom.makeNode(l_pCalledId_num_text));
  
       l_ReviewTemRNAtt_numEle := dbms_xmldom.createElement(l_domdoc, 'ReviewTemplateRNAttr' ); --l_domdoc constant
   l_ReviewTemRNAtt_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_ReviewTemRNAtt_numEle));--l_TransCtx_node parent
   l_ReviewTemRNAtt_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_ABS_NTF_SS' );--column of cursor
   l_ReviewTemRNAtt_num_tnode   := dbms_xmldom.appendChild(l_ReviewTemRNAtt_num_node,dbms_xmldom.makeNode(l_ReviewTemRNAtt_num_text));
  
 
  
   l_TransactionId_numEle := dbms_xmldom.createElement(l_domdoc, 'TransactionId' ); --l_domdoc constant
   l_TransactionId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_TransactionId_numEle));--l_TransCtx_node parent
   l_TransactionId_num_text    := dbms_xmldom.createTextNode(l_domdoc, P_TransactionId);--column of cursor
   l_TransactionId_num_tnode   := dbms_xmldom.appendChild(l_TransactionId_num_node,dbms_xmldom.makeNode(l_TransactionId_num_text));
  
  
  
   l_PrsnLocationId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnLocationId' ); --l_domdoc constant
   l_PrsnLocationId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnLocationId_numEle));--l_TransCtx_node parent
   l_PrsnLocationId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnLocationId );--column of cursor
   l_PrsnLocationId_num_tnode   := dbms_xmldom.appendChild(l_PrsnLocationId_num_node,dbms_xmldom.makeNode(l_PrsnLocationId_num_text));
  
   l_PrsnKfstrCode_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnKflexStructCode' ); --l_domdoc constant
   l_PrsnKfstrCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnKfstrCode_numEle));--l_TransCtx_node parent
   l_PrsnKfstrCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'FUJ_People_Group_Flexfield' );--column of cursor
   l_PrsnKfstrCode_num_tnode   := dbms_xmldom.appendChild(l_PrsnKfstrCode_num_node,dbms_xmldom.makeNode(l_PrsnKfstrCode_num_text));
  
   l_LoginPrsnMgrName_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginPrsnMgrName' ); --l_domdoc constant
   l_LoginPrsnMgrName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnMgrName_numEle));--l_TransCtx_node parent
   l_LoginPrsnMgrName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.LoginPrsnMgrName );--column of cursor
   l_LoginPrsnMgrName_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnMgrName_num_node,dbms_xmldom.makeNode(l_LoginPrsnMgrName_num_text));
  
  
  
   l_PrsnPositionName_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnPositionName' ); --l_domdoc constant
   l_PrsnPositionName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnPositionName_numEle));--l_TransCtx_node parent
   l_PrsnPositionName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnPositionName );--column of cursor
   l_PrsnPositionName_num_tnode   := dbms_xmldom.appendChild(l_PrsnPositionName_num_node,dbms_xmldom.makeNode(l_PrsnPositionName_num_text));
  
  
  
   l_PrsnEmpFlag_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnEmpFlag' ); --l_domdoc constant
   l_PrsnEmpFlag_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnEmpFlag_numEle));--l_TransCtx_node parent
   l_PrsnEmpFlag_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'A' );--column of cursor
   l_PrsnEmpFlag_num_tnode   := dbms_xmldom.appendChild(l_PrsnEmpFlag_num_node,dbms_xmldom.makeNode(l_PrsnEmpFlag_num_text));
  
  
  
  
   l_PrsnMgrName_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnMgrName' ); --l_domdoc constant
   l_PrsnMgrName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnMgrName_numEle));--l_TransCtx_node parent
   l_PrsnMgrName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnMgrName );--column of cursor
   l_PrsnMgrName_num_tnode   := dbms_xmldom.appendChild(l_PrsnMgrName_num_node,dbms_xmldom.makeNode(l_PrsnMgrName_num_text));
  

  
   l_prsnOrgId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnOrganizationId' ); --l_domdoc constant
   l_prsnOrgId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_prsnOrgId_numEle));--l_TransCtx_node parent
   l_prsnOrgId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnOrganizationId );--column of cursor
   l_prsnOrgId_num_tnode   := dbms_xmldom.appendChild(l_prsnOrgId_num_node,dbms_xmldom.makeNode(l_prsnOrgId_num_text));
  
      l_PrsnPositionId_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnPositionId' ); --l_domdoc constant
   l_PrsnPositionId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnPositionId_numEle));--l_TransCtx_node parent
   l_PrsnPositionId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnPositionId );--column of cursor
   l_PrsnPositionId_num_tnode   := dbms_xmldom.appendChild(l_PrsnPositionId_num_node,dbms_xmldom.makeNode(l_PrsnPositionId_num_text));
  
  
  
  
   l_TxnStatus_numEle := dbms_xmldom.createElement(l_domdoc, 'TxnStatus' ); --l_domdoc constant
   l_TxnStatus_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_TxnStatus_numEle));--l_TransCtx_node parent
   l_TxnStatus_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'W' );--column of cursor
   l_TxnStatus_num_tnode   := dbms_xmldom.appendChild(l_TxnStatus_num_node,dbms_xmldom.makeNode(l_TxnStatus_num_text));
  
  
  
  
   l_LoginWorkerNumber_numEle := dbms_xmldom.createElement(l_domdoc, 'LoginWorkerNumber' ); --l_domdoc constant
   l_LoginWorkerNumber_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginWorkerNumber_numEle));--l_TransCtx_node parent
   l_LoginWorkerNumber_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.LoginWorkerNumber );--column of cursor
   l_LoginWorkerNumber_num_tnode   := dbms_xmldom.appendChild(l_LoginWorkerNumber_num_node,dbms_xmldom.makeNode(l_LoginWorkerNumber_num_text));
  

  
  
  
   l_PrsnBgCurrencyCode_numEle := dbms_xmldom.createElement(l_domdoc, 'PrsnBgCurrencyCode' ); --l_domdoc constant
   l_PrsnBgCurrencyCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnBgCurrencyCode_numEle));--l_TransCtx_node parent
   l_PrsnBgCurrencyCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'AED' );--column of cursor
   l_PrsnBgCurrencyCode_num_tnode   := dbms_xmldom.appendChild(l_PrsnBgCurrencyCode_num_node,dbms_xmldom.makeNode(l_PrsnBgCurrencyCode_num_text));
  
  
  
  
   l_HeaderType_numEle := dbms_xmldom.createElement(l_domdoc, 'HeaderType' ); --l_domdoc constant
   l_HeaderType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_HeaderType_numEle));--l_TransCtx_node parent
   l_HeaderType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'PER_HEADER' );--column of cursor
   l_HeaderType_num_tnode   := dbms_xmldom.appendChild(l_HeaderType_num_node,dbms_xmldom.makeNode(l_HeaderType_num_text));
  

  
   l_SSHR_WF_BASED_numEle := dbms_xmldom.createElement(l_domdoc, 'SSHR_WF_BASED' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_SSHR_WF_BASED_numEle,'dataType', 'b');
   l_SSHR_WF_BASED_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_SSHR_WF_BASED_numEle));--l_TransCtx_node parent
   l_SSHR_WF_BASED_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'true' );--column of cursor
   l_SSHR_WF_BASED_num_tnode   := dbms_xmldom.appendChild(l_SSHR_WF_BASED_num_node,dbms_xmldom.makeNode(l_SSHR_WF_BASED_num_text));
  
      l_prsnSecGrpId_numEle:= dbms_xmldom.createElement(l_domdoc, 'PrsnSecurityGroupId' ); --l_domdoc constant
   l_prsnSecGrpId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_prsnSecGrpId_numEle));--l_TransCtx_node parent
   l_prsnSecGrpId_num_text    := dbms_xmldom.createTextNode(l_domdoc, '0' );--column of cursor
   l_prsnSecGrpId_num_tnode   := dbms_xmldom.appendChild(l_prsnSecGrpId_num_node,dbms_xmldom.makeNode(l_prsnSecGrpId_num_text));
  

      l_LoginPrsnMgrId_numEle:= dbms_xmldom.createElement(l_domdoc, 'LoginPrsnMgrId' ); --l_domdoc constant
   l_LoginPrsnMgrId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnMgrId_numEle));--l_TransCtx_node parent
   l_LoginPrsnMgrId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.LoginPrsnMgrId);--column of cursor
   l_LoginPrsnMgrId_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnMgrId_num_node,dbms_xmldom.makeNode(l_LoginPrsnMgrId_num_text));
  
   l_AttachCheck_numEle:= dbms_xmldom.createElement(l_domdoc, 'AttachCheck' ); --l_domdoc constant
   l_AttachCheck_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_AttachCheck_numEle));--l_TransCtx_node parent
   l_AttachCheck_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'Y');--column of cursor
   l_AttachCheck_num_tnode   := dbms_xmldom.appendChild(l_AttachCheck_num_node,dbms_xmldom.makeNode(l_AttachCheck_num_text));
  

      l_PrsnType_numEle:= dbms_xmldom.createElement(l_domdoc, 'PrsnType' ); --l_domdoc constant
   l_PrsnType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnType_numEle));--l_TransCtx_node parent
   l_PrsnType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'E' );--column of cursor
   l_PrsnType_num_tnode   := dbms_xmldom.appendChild(l_PrsnType_num_node,dbms_xmldom.makeNode(l_PrsnType_num_text));
  
  
      l_PerzOrgId_numEle:= dbms_xmldom.createElement(l_domdoc, 'PerzOrganizationId' ); --l_domdoc constant
   l_PerzOrgId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PerzOrgId_numEle));--l_TransCtx_node parent
   l_PerzOrgId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNBGID );--column of cursor
   l_PerzOrgId_num_tnode   := dbms_xmldom.appendChild(l_PerzOrgId_num_node,dbms_xmldom.makeNode(l_PerzOrgId_num_text));
  
  
      l_LoginPrsnName_numEle:= dbms_xmldom.createElement(l_domdoc, 'LoginPrsnName' ); --l_domdoc constant
   l_LoginPrsnName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnName_numEle));--l_TransCtx_node parent
   l_LoginPrsnName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNMGRNAME );--column of cursor
   l_LoginPrsnName_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnName_num_node,dbms_xmldom.makeNode(l_LoginPrsnName_num_text));
  
      l_CreatorPrsnId_numEle:= dbms_xmldom.createElement(l_domdoc, 'CreatorPrsnId' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_CreatorPrsnId_numEle,'dataType', 'n');  
   l_CreatorPrsnId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_CreatorPrsnId_numEle));--l_TransCtx_node parent
   l_CreatorPrsnId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.LOGINPRSNID );--column of cursor
   l_CreatorPrsnId_num_tnode   := dbms_xmldom.appendChild(l_CreatorPrsnId_num_node,dbms_xmldom.makeNode(l_CreatorPrsnId_num_text));
  
  
      l_PrsnId_numEle:= dbms_xmldom.createElement(l_domdoc, 'PrsnId' ); --l_domdoc constant
   l_PrsnId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnId_numEle));--l_TransCtx_node parent
   l_PrsnId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.LOGINPRSNID );--column of cursor
   l_PrsnId_num_tnode   := dbms_xmldom.appendChild(l_PrsnId_num_node,dbms_xmldom.makeNode(l_PrsnId_num_text));
  
  
      l_TransactionType_numEle:= dbms_xmldom.createElement(l_domdoc, 'TransactionType' ); --l_domdoc constant
   l_TransactionType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_TransactionType_numEle));--l_TransCtx_node parent
   l_TransactionType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'WF' );--column of cursor
   l_TransactionType_num_tnode   := dbms_xmldom.appendChild(l_TransactionType_num_node,dbms_xmldom.makeNode(l_TransactionType_num_text));
  
      l_NtfAttachAttr_numEle:= dbms_xmldom.createElement(l_domdoc, 'NtfAttachAttr' ); --l_domdoc constant
   l_NtfAttachAttr_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_NtfAttachAttr_numEle));--l_TransCtx_node parent
   l_NtfAttachAttr_num_text    := dbms_xmldom.createTextNode(l_domdoc,  'FND:entity=PQH_SS_ATTACHMENT'||'&'||'amp;pk1name=TransactionId'||'&'||'amp;pk1value='||p_TransactionId );--column of cursor
   l_NtfAttachAttr_num_tnode   := dbms_xmldom.appendChild(l_NtfAttachAttr_num_node,dbms_xmldom.makeNode(l_NtfAttachAttr_num_text));
  

      l_pApprovalReqd_numEle:= dbms_xmldom.createElement(l_domdoc, 'pApprovalReqd' ); --l_domdoc constant
   l_pApprovalReqd_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pApprovalReqd_numEle));--l_TransCtx_node parent
   l_pApprovalReqd_num_text    := dbms_xmldom.createTextNode(l_domdoc,'YD' );--column of cursor
   l_pApprovalReqd_num_tnode   := dbms_xmldom.appendChild(l_pApprovalReqd_num_node,dbms_xmldom.makeNode(l_pApprovalReqd_num_text));
  
  
     l_pAMETranType_numEle:= dbms_xmldom.createElement(l_domdoc, 'pAMETranType' ); --l_domdoc constant
   l_pAMETranType_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pAMETranType_numEle));--l_TransCtx_node parent
   l_pAMETranType_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'SSHRMS' );--column of cursor
   l_pAMETranType_num_tnode   := dbms_xmldom.appendChild(l_pAMETranType_num_node,dbms_xmldom.makeNode(l_pAMETranType_num_text));
  
  
      l_RelaunchFunction_numEle:= dbms_xmldom.createElement(l_domdoc, 'RelaunchFunction' ); --l_domdoc constant
   l_RelaunchFunction_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_RelaunchFunction_numEle));--l_TransCtx_node parent
   l_RelaunchFunction_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_ABS_ENTRY_PAGE_SS' );--column of cursor
   l_RelaunchFunction_num_tnode   := dbms_xmldom.appendChild(l_RelaunchFunction_num_node,dbms_xmldom.makeNode(l_RelaunchFunction_num_text));
  

      L_PerzLocCode_numEle:= dbms_xmldom.createElement(l_domdoc, 'PerzLocalizationCode' ); --l_domdoc constant
   L_PerzLocCode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(L_PerzLocCode_numEle));--l_TransCtx_node parent
   L_PerzLocCode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'AE' );--column of cursor
   L_PerzLocCode_num_tnode   := dbms_xmldom.appendChild(L_PerzLocCode_num_node,dbms_xmldom.makeNode(L_PerzLocCode_num_text));
  

      l_TxnRefTab_numEle:= dbms_xmldom.createElement(l_domdoc, 'TransactionRefTable' ); --l_domdoc constant
   l_TxnRefTab_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_TxnRefTab_numEle));--l_TransCtx_node parent
   l_TxnRefTab_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'PER_ABSENCE_ATTENDANCES' );--column of cursor
   l_TxnRefTab_num_tnode   := dbms_xmldom.appendChild(l_TxnRefTab_num_node,dbms_xmldom.makeNode(l_TxnRefTab_num_text));
  

      l_TxnIdentifier_numEle:= dbms_xmldom.createElement(l_domdoc, 'TransactionIdentifier' ); --l_domdoc constant
   l_TxnIdentifier_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_TxnIdentifier_numEle));--l_TransCtx_node parent
   l_TxnIdentifier_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'ABSENCES');--column of cursor
   l_TxnIdentifier_num_tnode   := dbms_xmldom.appendChild(l_TxnIdentifier_num_node,dbms_xmldom.makeNode(l_TxnIdentifier_num_text));
  
   l_pCalledFrom_numEle:= dbms_xmldom.createElement(l_domdoc, 'pCalledFrom' ); --l_domdoc constant
   l_pCalledFrom_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pCalledFrom_numEle));--l_TransCtx_node parent
   l_pCalledFrom_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_LOA_SS' );--column of cursor
   l_pCalledFrom_num_tnode   := dbms_xmldom.appendChild(l_pCalledFrom_num_node,dbms_xmldom.makeNode(l_pCalledFrom_num_text));
  
  
      l_LoginPrsnBgId_numEle:= dbms_xmldom.createElement(l_domdoc, 'LoginPrsnBgId' ); --l_domdoc constant
   l_LoginPrsnBgId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_LoginPrsnBgId_numEle));--l_TransCtx_node parent
   l_LoginPrsnBgId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNBGID);--column of cursor
   l_LoginPrsnBgId_num_tnode   := dbms_xmldom.appendChild(l_LoginPrsnBgId_num_node,dbms_xmldom.makeNode(l_LoginPrsnBgId_num_text));
  

      l_PerzFunctionName_numEle:= dbms_xmldom.createElement(l_domdoc, 'PerzFunctionName' ); --l_domdoc constant
   l_PerzFunctionName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PerzFunctionName_numEle));--l_TransCtx_node parent
   l_PerzFunctionName_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_LOA_SS' );--column of cursor
   l_PerzFunctionName_num_tnode   := dbms_xmldom.appendChild(l_PerzFunctionName_num_node,dbms_xmldom.makeNode(l_PerzFunctionName_num_text));
  
      l_ProcessName_numEle:= dbms_xmldom.createElement(l_domdoc, 'ProcessName' ); --l_domdoc constant
   l_ProcessName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_ProcessName_numEle));--l_TransCtx_node parent
   l_ProcessName_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'HR_GENERIC_APPROVAL_PRC' );--column of cursor
   l_ProcessName_num_tnode   := dbms_xmldom.appendChild(l_ProcessName_num_node,dbms_xmldom.makeNode(l_ProcessName_num_text));
  

      l_PrsnPayrollId_numEle:= dbms_xmldom.createElement(l_domdoc, 'PrsnPayrollId' ); --l_domdoc constant
   l_PrsnPayrollId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnPayrollId_numEle));--l_TransCtx_node parent
   l_PrsnPayrollId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnPayrollId );--column of cursor
   l_PrsnPayrollId_num_tnode   := dbms_xmldom.appendChild(l_PrsnPayrollId_num_node,dbms_xmldom.makeNode(l_PrsnPayrollId_num_text));
  
      l_PrsnName_numEle:= dbms_xmldom.createElement(l_domdoc, 'PrsnName' ); --l_domdoc constant
   l_PrsnName_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_PrsnName_numEle));--l_TransCtx_node parent
   l_PrsnName_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PrsnName );--column of cursor
   l_PrsnName_num_tnode   := dbms_xmldom.appendChild(l_PrsnName_num_node,dbms_xmldom.makeNode(l_PrsnName_num_text));
  

      l_pAMEAppId_numEle:= dbms_xmldom.createElement(l_domdoc, 'pAMEAppId' ); --l_domdoc constant
   l_pAMEAppId_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_pAMEAppId_numEle));--l_TransCtx_node parent
   l_pAMEAppId_num_text    := dbms_xmldom.createTextNode(l_domdoc, '800' );--column of cursor
   l_pAMEAppId_num_tnode   := dbms_xmldom.appendChild(l_pAMEAppId_num_node,dbms_xmldom.makeNode(l_pAMEAppId_num_text));
  
   l_SS_RevFLMode_numEle:= dbms_xmldom.createElement(l_domdoc, 'SSHR_REVIEW_FLOW_MODE' ); --l_domdoc constant
   l_SS_RevFLMode_num_node    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_SS_RevFLMode_numEle));--l_TransCtx_node parent
   l_SS_RevFLMode_num_text    := dbms_xmldom.createTextNode(l_domdoc, 'WF_REVIEW_PAGE' );--column of cursor
   l_SS_RevFLMode_num_tnode   := dbms_xmldom.appendChild(l_SS_RevFLMode_num_node,dbms_xmldom.makeNode(l_SS_RevFLMode_num_text));
  
   l_cnode_element2 := dbms_xmldom.createElement(l_domdoc, 'CNode' );--dbms_xmldom.createProcessingInstruction(l_domdoc, 'CNode','Test="1"' );
   dbms_xmldom.setAttribute(l_cnode_element2,'name', 'AbsenceParams');
   dbms_xmldom.setAttribute(l_cnode_element2,'type', 'Ht');
   l_cnode_node2    := dbms_xmldom.appendChild(l_TransCtx_node,dbms_xmldom.makeNode(l_cnode_element2));
  
   l_AbsenceAttdId_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceAttdId' ); --l_domdoc constant
   l_AbsenceAttdId_num_node    := dbms_xmldom.appendChild(l_cnode_node2,dbms_xmldom.makeNode(l_AbsenceAttdId_numEle));--l_TransCtx_node parent
   l_AbsenceAttdId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsenceAttdId );--column of cursor
   l_AbsenceAttdId_num_tnode   := dbms_xmldom.appendChild(l_AbsenceAttdId_num_node,dbms_xmldom.makeNode(l_AbsenceAttdId_num_text));
  

  
  
   l_AbsenceAction_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceAction' ); --l_domdoc constant
   l_AbsenceAction_num_node    := dbms_xmldom.appendChild(l_cnode_node2,dbms_xmldom.makeNode(l_AbsenceAction_numEle));--l_TransCtx_node parent
   l_AbsenceAction_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsenceAction );--column of cursor
   l_AbsenceAction_num_tnode   := dbms_xmldom.appendChild(l_AbsenceAction_num_node,dbms_xmldom.makeNode(l_AbsenceAction_num_text));
   


  
   l_AbsAttId_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceAttendanceId' ); --l_domdoc constant
   l_AbsAttId_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_AbsAttId_numEle));--l_TransCtx_node parent
   l_AbsAttId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsenceAttdId );--column of cursor
   l_AbsAttId_num_tnode   := dbms_xmldom.appendChild(l_AbsAttId_num_node,dbms_xmldom.makeNode(l_AbsAttId_num_text));
  
      if p_AbsAttendanceReasonId is not null then
   l_AbsAttendReaId_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsAttendanceReasonId' ); --l_domdoc constant
   l_AbsAttendReaId_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_AbsAttendReaId_numEle));--l_TransCtx_node parent
   l_AbsAttendReaId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsAttendanceReasonId );--column of cursor
   l_AbsAttendReaId_num_tnode   := dbms_xmldom.appendChild(l_AbsAttendReaId_num_node,dbms_xmldom.makeNode(l_AbsAttendReaId_num_text));
   end if;
   
       l_BusinessGroupId_numEle := dbms_xmldom.createElement(l_domdoc, 'BusinessGroupId' ); --l_domdoc constant
   l_BusinessGroupId_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_BusinessGroupId_numEle));--l_TransCtx_node parent
   l_BusinessGroupId_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.PRSNBGID );--column of cursor
   l_BusinessGroupId_num_tnode   := dbms_xmldom.appendChild(l_BusinessGroupId_num_node,dbms_xmldom.makeNode(l_BusinessGroupId_num_text));
   
       l_AbsAttTypeId_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceAttendanceTypeId' ); --l_domdoc constant
   l_AbsAttTypeId_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_AbsAttTypeId_numEle));--l_TransCtx_node parent
   l_AbsAttTypeId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsenceAttendanceTypeId);--column of cursor
   l_AbsAttTypeId_num_tnode   := dbms_xmldom.appendChild(l_AbsAttTypeId_num_node,dbms_xmldom.makeNode(l_AbsAttTypeId_num_text));
  

  
   l_PersonId_numEle := dbms_xmldom.createElement(l_domdoc, 'PersonId' ); --l_domdoc constant
   l_PersonId_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_PersonId_numEle));--l_TransCtx_node parent
   l_PersonId_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_PrsnId  );--column of cursor
   l_PersonId_num_tnode   := dbms_xmldom.appendChild(l_PersonId_num_node,dbms_xmldom.makeNode(l_PersonId_num_text));
  
   l_AbsenceDays_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceDays' ); --l_domdoc constant
   l_AbsenceDays_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_AbsenceDays_numEle));--l_TransCtx_node parent
   l_AbsenceDays_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_AbsenceDays );--column of cursor
   l_AbsenceDays_num_tnode   := dbms_xmldom.appendChild(l_AbsenceDays_num_node,dbms_xmldom.makeNode(l_AbsenceDays_num_text));
  

   l_AbsenceHours_numEle := dbms_xmldom.createElement(l_domdoc, 'AbsenceHours' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_AbsenceHours_numEle,'null', 'true');
   l_AbsenceHours_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_AbsenceHours_numEle));--l_TransCtx_node parent
   --l_AbsenceHours_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_AbsenceHours_num_tnode   := dbms_xmldom.appendChild(l_AbsenceHours_num_node,dbms_xmldom.makeNode(l_AbsenceHours_num_text));
  
      l_DateEnd_numEle := dbms_xmldom.createElement(l_domdoc, 'DateEnd' ); --l_domdoc constant
   l_DateEnd_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_DateEnd_numEle));--l_TransCtx_node parent
   l_DateEnd_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_DateEnd );--column of cursor
   l_DateEnd_num_tnode   := dbms_xmldom.appendChild(l_DateEnd_num_node,dbms_xmldom.makeNode(l_DateEnd_num_text));
  

     
   l_DateNoti_numEle := dbms_xmldom.createElement(l_domdoc, 'DateNotification' ); --l_domdoc constant
   l_DateNoti_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_DateNoti_numEle));--l_TransCtx_node parent
   l_DateNoti_num_text    := dbms_xmldom.createTextNode(l_domdoc, TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') );--column of cursor
   l_DateNoti_num_tnode   := dbms_xmldom.appendChild(l_DateNoti_num_node,dbms_xmldom.makeNode(l_DateNoti_num_text));
  

   l_DateProjEnd_numEle := dbms_xmldom.createElement(l_domdoc, 'DateProjectedEnd' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_DateProjEnd_numEle,'null', 'true');  
   l_DateProjEnd_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_DateProjEnd_numEle));--l_TransCtx_node parent
   --l_DateProjEnd_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_DateProjEnd_num_tnode   := dbms_xmldom.appendChild(l_DateProjEnd_num_node,dbms_xmldom.makeNode(l_DateProjEnd_num_text));
  

      l_DatePrjStrt_numEle := dbms_xmldom.createElement(l_domdoc, 'DateProjectedStart' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_DatePrjStrt_numEle,'null', 'true');  
   l_DatePrjStrt_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_DatePrjStrt_numEle));--l_TransCtx_node parent
   --l_DatePrjStrt_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_DatePrjStrt_num_tnode   := dbms_xmldom.appendChild(l_DatePrjStrt_num_node,dbms_xmldom.makeNode(l_DatePrjStrt_num_text));
  

   l_DateStart_numEle := dbms_xmldom.createElement(l_domdoc, 'DateStart' ); --l_domdoc constant
   l_DateStart_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_DateStart_numEle));--l_TransCtx_node parent
   l_DateStart_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_DateStart );--column of cursor
   l_DateStart_num_tnode   := dbms_xmldom.appendChild(l_DateStart_num_node,dbms_xmldom.makeNode(l_DateStart_num_text));
  

   l_TimeProjEndnumEle := dbms_xmldom.createElement(l_domdoc, 'TimeProjectedEnd' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_TimeProjEndnumEle,'null', 'true'); 
   l_TimeProjEndnum_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_TimeProjEndnumEle));--l_TransCtx_node parent
   --l_TimeProjEndnum_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_TimeProjEndnum_tnode   := dbms_xmldom.appendChild(l_TimeProjEndnum_node,dbms_xmldom.makeNode(l_TimeProjEndnum_text));
  

        l_TimeProjSt_numEle := dbms_xmldom.createElement(l_domdoc, 'TimeProjectedStart' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_TimeProjSt_numEle,'null', 'true');    
   l_TimeProjSt_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_TimeProjSt_numEle));--l_TransCtx_node parent
   --l_TimeProjSt_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_TimeProjSt_num_tnode   := dbms_xmldom.appendChild(l_TimeProjSt_num_node,dbms_xmldom.makeNode(l_TimeProjSt_num_text));
  

         l_LastUpdateDate_numEle := dbms_xmldom.createElement(l_domdoc, 'LastUpdateDate' ); --l_domdoc constant
   l_LastUpdateDate_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_LastUpdateDate_numEle));--l_TransCtx_node parent
   l_LastUpdateDate_num_text    := dbms_xmldom.createTextNode(l_domdoc, TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') );--column of cursor
   l_LastUpdateDate_num_tnode   := dbms_xmldom.appendChild(l_LastUpdateDate_num_node,dbms_xmldom.makeNode(l_LastUpdateDate_num_text));
  

       l_LastUpdatedBy_numEle := dbms_xmldom.createElement(l_domdoc, 'LastUpdatedBy' ); --l_domdoc constant
   l_LastUpdatedBy_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_LastUpdatedBy_numEle));--l_TransCtx_node parent
   l_LastUpdatedBy_num_text    := dbms_xmldom.createTextNode(l_domdoc, v_userId );--column of cursor
   l_LastUpdatedBy_num_tnode   := dbms_xmldom.appendChild(l_LastUpdatedBy_num_node,dbms_xmldom.makeNode(l_LastUpdatedBy_num_text));
  

     
   l_LastUpdateLogin_numEle := dbms_xmldom.createElement(l_domdoc, 'LastUpdateLogin' ); --l_domdoc constant
   l_LastUpdateLogin_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_LastUpdateLogin_numEle));--l_TransCtx_node parent
   l_LastUpdateLogin_num_text    := dbms_xmldom.createTextNode(l_domdoc, v_sessionid );--column of cursor
   l_LastUpdateLogin_num_tnode   := dbms_xmldom.appendChild(l_LastUpdateLogin_num_node,dbms_xmldom.makeNode(l_LastUpdateLogin_num_text));
  

      l_CreatedBy_numEle := dbms_xmldom.createElement(l_domdoc, 'CreatedBy' ); --l_domdoc constant
   l_CreatedBy_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_CreatedBy_numEle));--l_TransCtx_node parent
   l_CreatedBy_num_text    := dbms_xmldom.createTextNode(l_domdoc, v_userId );--column of cursor
   l_CreatedBy_num_tnode   := dbms_xmldom.appendChild(l_CreatedBy_num_node,dbms_xmldom.makeNode(l_CreatedBy_num_text));
  

       l_CreationDate_numEle := dbms_xmldom.createElement(l_domdoc, 'CreationDate' ); --l_domdoc constant
   l_CreationDate_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_CreationDate_numEle));--l_TransCtx_node parent
   l_CreationDate_num_text    := dbms_xmldom.createTextNode(l_domdoc, TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') );--column of cursor
   l_CreationDate_num_tnode   := dbms_xmldom.appendChild(l_CreationDate_num_node,dbms_xmldom.makeNode(l_CreationDate_num_text));
  

   l_OVN_numEle := dbms_xmldom.createElement(l_domdoc, 'ObjectVersionNumber' ); --l_domdoc constant
   dbms_xmldom.setAttribute(l_OVN_numEle,'null', 'true');
   l_OVN_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_OVN_numEle));--l_TransCtx_node parent
   --l_OVN_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );--column of cursor
   --l_OVN_num_tnode   := dbms_xmldom.appendChild(l_OVN_num_node,dbms_xmldom.makeNode(l_OVN_num_text));
  
      l_Comments_numEle := dbms_xmldom.createElement(l_domdoc, 'Comments' ); --l_domdoc constant
   l_Comments_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_Comments_numEle));--l_TransCtx_node parent
   l_Comments_num_text    := dbms_xmldom.createTextNode(l_domdoc, p_comments );--column of cursor
   l_Comments_num_tnode   := dbms_xmldom.appendChild(l_Comments_num_node,dbms_xmldom.makeNode(l_Comments_num_text));
  
 
   l_LinkAbsIdAbsEO_numEle := dbms_xmldom.createElement(l_domdoc, 'LinkedAbsenceIdPerAbsenceAttendancesEO' ); --l_domdoc constant
   l_LinkAbsIdAbsEO_num_node    := dbms_xmldom.appendChild(l_PerAbsAttendEORow_node,dbms_xmldom.makeNode(l_LinkAbsIdAbsEO_numEle));--l_TransCtx_node parent
  


  END LOOP;

  l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
  dbms_xmldom.freeDocument(l_domdoc);

  --dbms_output.put_line(l_xmltype.getClobVal);
  --insert into xml_document values(1,l_xmltype.getClobVal);
  commit;

p_msg := 'S';
p_xml := l_xmltype.getClobVal;

exception when others then
p_msg := 'E';

END;

--select 1 from dual
/

----------------------------------------------------------------------------------------------------------------

2.3 Procedure for attachments.

CREATE OR REPLACE PROCEDURE P_UPLOAD_ATTACHMENT(
p_pk1_value number, --transaction_id
p_descirption varchar2, --any description
p_file_name varchar2, --file_name
p_user_name varchar2, --user_name
pStatus out varchar2
)
AS
--DECLARE

l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_media_id NUMBER;
l_category_id number;
l_pk1_value fnd_attached_documents.pk1_value%type:= p_pk1_value;--9999991; --–Primary Key information that uniquely identifies the information
l_description fnd_documents_tl.description%type:= p_descirption;--'Test Afzal Attachment';
l_filename VARCHAR2(240) := p_file_name;--'test.pdf';
l_file_path varchar2(240) := 'IN_FILE_LOC'; --–Server Directory Path for upload files
l_seq_num NUMBER;
l_blob_data BLOB;
l_blob BLOB;
l_bfile BFILE;
l_byte NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
x_blob BLOB;
fils BFILE;
blob_length integer;
l_entity_name varchar2(100) := 'PQH_SS_ATTACHMENT'; --Must be defined before or use existing ones. Table: FND_DOCUMENT_ENTITIES
l_category_name VARCHAR2(100) := 'Miscellaneous'; --–Must be defined before or use existing ones.
l_file_content_type VARCHAR2(100);

BEGIN
pStatus := 'S';
--–Enter USER_ID,RESP_ID,RESP_APPL_ID
fnd_global.apps_initialize (2605, 51683, 800);

SELECT fnd_documents_s.NEXTVAL
INTO l_document_id
FROM DUAL;

SELECT fnd_attached_documents_s.NEXTVAL
INTO l_attached_document_id
FROM DUAL;

SELECT NVL (MAX (seq_num), 0) + 10
INTO l_seq_num
FROM fnd_attached_documents
WHERE pk1_value = l_pk1_value AND entity_name = l_entity_name;

-- Select User_id
SELECT user_id
INTO l_fnd_user_id
from apps.fnd_user
WHERE UPPER(user_name) = UPPER(p_user_name);--'EG_AFZAL'; --Username who will be uploading file.

-- Get Data type id for Short Text types of attachments
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE NAME = 'FILE'
AND LANGUAGE = 'US';

-- Select Category id for Attachments
SELECT category_id
INTO l_category_id
FROM apps.fnd_document_categories_vl
WHERE USER_NAME = l_category_name;

-- Select nexvalues of document id, attached document id and
SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL
into l_document_id,l_attached_document_id
FROM DUAL;

SELECT MAX (file_id) + 1
INTO l_media_id
FROM fnd_lobs;

fils := BFILENAME (l_file_path, l_filename);

IF UPPER(REGEXP_SUBSTR(l_filename, '[^.]+$')) IN( 'JPEG','JPG') THEN
l_file_content_type := 'image/jpeg';
ELSIF UPPER(REGEXP_SUBSTR(l_filename, '[^.]+$')) IN ('PDF') THEN
l_file_content_type := 'application/pdf';
ELSE
l_file_content_type := 'image/jpeg';
END IF;

-- Obtain the size of the blob file
DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);
blob_length := DBMS_LOB.getlength (fils);
DBMS_LOB.fileclose (fils);

-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to x_blob.

INSERT INTO fnd_lobs
(file_id, file_name, file_content_type, upload_date,
expiration_date, program_name, program_tag, file_data,
LANGUAGE, oracle_charset, file_format
)
VALUES (l_media_id, l_filename, l_file_content_type,--,'text/plain',
SYSDATE, NULL, 'FNDATTCH', NULL,  EMPTY_BLOB (),--l_blob_data,--EMPTY_BLOB (),
'US', 'UTF8', 'binary'
)
RETURNING file_data
INTO x_blob;

-- Load the file into the database as a BLOB
DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (x_blob, fils, blob_length);

-- Close handles to blob and file
DBMS_LOB.CLOSE (x_blob);
DBMS_LOB.CLOSE (fils);

DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);

COMMIT;

-- This package allows user to share file across multiple orgs or restrict to single org

fnd_documents_pkg.insert_row
(x_rowid => l_rowid,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_datatype_id => l_short_datatype_id,
X_security_id => 21, --–Security ID defined in your Attchments, Usaully SOB ID/ORG_ID
x_publish_flag => 'N', --–This flag allow the file to share across multiple organization
x_category_id => l_category_id,
x_security_type => 1,
x_usage_type => 'S',
x_language => 'US',
x_description => l_description,
x_file_name => l_filename,
x_media_id => l_media_id
);

commit;

-- Description informations will be stored in below table based on languages.
fnd_documents_pkg.insert_tl_row
(x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_language => 'US',
x_description => l_description
);
commit;

fnd_attached_documents_pkg.insert_row
(x_rowid => l_rowid,
x_attached_document_id => l_attached_document_id,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_seq_num => l_seq_num,
x_entity_name => l_entity_name,
x_column1 => NULL,
x_pk1_value => l_pk1_value,
x_pk2_value => NULL,
x_pk3_value => NULL,
x_pk4_value => NULL,
x_pk5_value => NULL,
x_automatically_added_flag => 'N',
x_datatype_id => 6,
x_category_id => l_category_id,
x_security_type => 1,
X_security_id => 21, --Security ID defined in your Attchments, Usaully SOB ID/ORG_ID
x_publish_flag => 'Y',
x_language => 'US',
x_description => l_description,
x_file_name => l_filename,
x_media_id => l_media_id
);
COMMIT;
DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);
EXCEPTION WHEN OTHERS THEN
pStatus := 'E->'||sqlerrm;
dbms_output.put_line(sqlerrm);
end;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3. Package for SSHR transaction.

CREATE OR REPLACE Package APPS.FUJ_HR_ABSENCE_WORKFLOW_PKG
is
--absence activty_id = P_ACTIVITY_ID => 177352
  --
  -- Procedure create the Request
  --
 Procedure CREATE_REQUEST                (     PP_SELECTED_USER_NAME                   varchar2--1024
                                                ,PP_CREATOR_USER_NAME     varchar2--172
                                                ,PP_ITEM_TYPE                     varchar2--'HRSSA'
                                               ,PP_RESPONSIBILITY_NAME     varchar2--'MOFI Absence Management';
                                               ,PP_CALLED_FROM                  varchar2 --'MOF_HR_LOA_CREATE_DECREE_SS'
                                               ,PP_process                            varchar2--'MOF_HR_EIT_LOA_CREATE_PRC'
                                               ,p_AbsenceAttendanceTypeId  number
                                               ,p_AbsAttendanceReasonId number
                                               ,p_AbsenceDays  number
                                               ,p_DateStart  varchar2 --FORMAT  '2018-08-04';
                                               ,p_DateEnd  varchar2 --FORMAT  '2018-08-04'; 
                                               ,pp_comments varchar2   DEFAULT NULL                                                                                      
                                               ,PP_EIT_NAME                       varchar2 DEFAULT NULL--'MOF_HR_LOA_CREATE_DECREE'
                                               ,PP_INFORMATION1                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION2                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION3                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION4                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION5                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION6                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION7                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION8                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION9                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION10                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION11                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION12                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION13                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION14                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION15                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION16                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION17                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION18                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION19                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION20                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION21                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION22                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION23                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION24                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION25                      varchar2 DEFAULT NULL
                                               ,PP_INFORMATION26                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION27                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION28                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION29                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION30                       varchar2 DEFAULT NULL
                                               ,p_activity_id                        number  DEFAULT NULL
                                               ,PP_ATTACHFILENAME                  varchar2 DEFAULT NULL
                                               ,PP_ATTACHFILENAME2                  varchar2 DEFAULT NULL
                                               --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_ABSENCE_WORKFLOW_PKG

is
   --
  --Function Used to Create Work Flow and return with Item Key
  --
              function Create_WorkFlow_Process     (P_USER_NAME              Varchar2
                                                                     ,P_CREATOR_PERSON_ID   NUMBER
                                                                     ,P_CALLED_FROM            Varchar2
                                                                     ,P_process                      Varchar2
                                                                     ,p_activity_id           number DEFAULT NULL
                                                                     ,p_AbsenceAttendanceTypeId 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_leave_name_ar varchar2(1500);
             v_emp_num varchar2(1500);
             V_SUP_PERSON_ID NUMBER;
             V_SUP_USER_NAME varchar2(1500);
             V_SUP_NAME varchar2(1500);
             
                 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.LAST_NAME||', '||p.FIRST_NAME NAME, P.EMPLOYEE_NUMBER
                            into  V_CURRENT_PERSON_DISPLAY_NAME, v_emp_num       
                          from per_all_people_f p
                        where 1=1                         
                            and trunc(sysdate) between p.EFFECTIVE_START_DATE and p.EFFECTIVE_END_DATE
                            and p.person_id=V_CURRENT_PERSON_ID
                            ;         
                         
                            dbms_output.put_line ('V_CURRENT_PERSON_DISPLAY_NAME='||V_CURRENT_PERSON_DISPLAY_NAME);                                       
                Exception
                     when no_data_found
                       then                      
                         v_output    :='Invalide User Name or Not Found';                      
                         raise Exc_Error;                    
            End;                     
          
            --get assignment_id
         
                            Begin
                         select  distinct p.assignment_id
                            into  V_ASSIGNMENT_ID     
                          from per_all_assignments_f p
                        where 1=1                         
                            and trunc(sysdate) between p.EFFECTIVE_START_DATE and p.EFFECTIVE_END_DATE
                            and p.person_id=V_CURRENT_PERSON_ID
                            and primary_flag ='Y'
                            ;         
                         
                            dbms_output.put_line ('V_assignment_id ='||V_ASSIGNMENT_ID);                                       
                Exception
                     when no_data_found
                       then                      
                         v_output    :='Invalide assignment_id';                      
                         raise Exc_Error;                    
            End;
           
             Begin
                         select  distinct  p.supervisor_id
                            into V_SUP_PERSON_ID     
                          from per_all_assignments_f p
                        where 1=1                         
                            and trunc(sysdate) between p.EFFECTIVE_START_DATE and p.EFFECTIVE_END_DATE
                            and p.person_id=V_CURRENT_PERSON_ID
                            and primary_flag ='Y'
                            ;         
                         
                            dbms_output.put_line ('V_assignment_id ='||V_ASSIGNMENT_ID);                                       
                Exception
                     when no_data_found
                       then  V_SUP_PERSON_ID := '';                      
            End;
         
            begin
           select tl.NAME
           into v_leave_name_ar
          from PER_ABS_ATTENDANCE_TYPES_tl tl
          where language  = 'AR' --in wf_attribute_values it is storing in arabic in our instance, so i am using this
          and ABSENCE_ATTENDANCE_TYPE_ID = p_AbsenceAttendanceTypeId;
                                      dbms_output.put_line ('v_leave_name_ar  ='||v_leave_name_ar );                                       
                Exception
                     when no_data_found
                       then                      
                         v_output    :='v_leave_name_ar ';                      
                         raise Exc_Error;                    
            End;
         
 
 
          begin
          select p2.LAST_NAME||', '||p2.FIRST_NAME NAME, user_name
          into V_SUP_NAME,V_SUP_USER_NAME
          from per_all_people_f p2, fnd_user fu
          where fu.employee_id = p2.person_id
          and trunc(sysdate) between p2.EFFECTIVE_START_DATE and p2.EFFECTIVE_END_DATE
                            and p2.person_id=V_SUP_PERSON_ID ;
          Exception
                     when no_data_found
                       then  V_SUP_NAME:= ''; V_SUP_USER_NAME:= '';                    
                         --v_output    :='Invalide supervisor_detials';                      
                         --raise Exc_Error;                    
            End;                 
                            
         
                                                    
            --
            --Work Flow variables
            --
               -- V_activity_id    :=184884;
               V_activity_id := p_activity_id;--339
                v_user_key      :=null;
                v_owner_role   :=V_USER_NAME;
            -------
            Begin             
                 select  distinct ITEM_TYPE    ,DISPLAY_NAME    --Should be Handeled 06092015    --[Error]  ORA-01422: exact fetch returns more than requested number of rows
                     into v_itemtype    ,V_PROCESS_DISPLAY_NAME
                    from wf_activities_tl
                  where 1=1
                     and name=P_process
                     and LANGUAGE='AR';                  
            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
             --
--ADDED NEW FROM HERE

--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_ORIG_SYS','PER'); 
--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_ORIG_SYS','PER'); 
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FORWARD_FROM_DISPLAY_NAME',V_CURRENT_PERSON_DISPLAY_NAME); 
--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FORWARD_FROM_ORIG_SYS_ATTR','PER'); 
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FORWARD_FROM_USERNAME',v_USER_NAME); 

wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FORWARD_TO_DISPLAY_NAME',v_SUP_NAME); 
--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FORWARD_TO_ITEM_CLASS_ATTR','header'); 
 
--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FORWARD_TO_ORIG_SYS_ATTR','PER'); 
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FORWARD_TO_USER','to '||v_SUP_NAME); 
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FORWARD_TO_USERNAME',V_SUP_USER_NAME); 

--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_APR_API_INSERTION_ATTR','N'); 
--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_APR_CATEGORY_ATTR','A'); 
--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_APR_DISPLAY_NAME_ATTR',V_SUP_NAME); 
--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_APR_ITEM_CLASS_ATTR','header'); 
-- 

--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_APR_NAME_ATTR',V_SUP_USER_NAME); 
---wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_APR_ORIG_SYSTEM_ATTR','PER'); 
--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_APR_SOURCE_ATTR','14015'); 
--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_NTF_SUB_FND_MSG_ATTR','HR_ABS_NTF_SUB_MSG'); 
--wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PARALLEL_ATTR','SERIAL'); 

wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'SELECT_ABSENCE_NAME',v_leave_name_ar); 
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'SELECT_EMPLOYEE_NUMBER',v_emp_num); 


wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_OAF_EDIT_URL_ATTR','HR_RELAUNCH_SS'); 
wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_OAF_NAVIGATION_ATTR','N'); 


wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'APPROVAL_CREATOR_PERSON_ID',V_CURRENT_PERSON_ID); 
--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'CURRENT_DEF_APPR_INDEX',0); 

wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'FORWARD_FROM_PERSON_ID',V_CURRENT_PERSON_ID); 
--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'FORWARD_TO_GROUPORCHAINID',1); 
wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'FORWARD_TO_PERSON_ID',V_SUP_PERSON_ID); 
--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_APR_ACTION_TYPE_ID_ATTR',10000); 
--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_APR_ACT_TYP_ORD_NUM_ATTR',3); 

--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_APR_GRPORCHN_ID_ATTR',1); 
--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_APR_GRPORCHN_ORD_NUM_ATTR',1); 
--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_APR_ITM_CLS_ORD_NUM_ATTR',1); 
--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_APR_ITM_ORD_NUM_ATTR',1); 

--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_APR_MEMBER_ORD_NUM_ATTR',1); 
--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_APR_OCCURRENCE_ATTR',1); 
--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_APR_ORD_NUM_ATTR',1); 
--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_APR_ORIG_SYSTEM_ID_ATTR',V_SUP_PERSON_ID); 
--wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_APR_SUB_LST_ORD_NUM_ATTR',2); 

wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_CONTACT_SET',0); 

--TILL HERE NEW


              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'USER_NAME','SNACHURI'); --SNACHURI IS SHOWING IN THE BACKEND APPLIED FROM EBS
              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_DISPLAY_NAME','Leave of Absence');
              --wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_NAME',v_process);
           
              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_ID',V_CURRENT_PERSON_ID);
              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_USERNAME',v_USER_NAME);
              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_PERSON_DISPLAY_NAME',V_CURRENT_PERSON_DISPLAY_NAME);
              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_ASSIGNMENT_ID',V_ASSIGNMENT_ID);
              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CURRENT_EFFECTIVE_DATE',sysdate);
              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_ID',V_CURRENT_PERSON_ID);
              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_USERNAME',v_USER_NAME);
              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'CREATOR_PERSON_DISPLAY_NAME',V_CURRENT_PERSON_DISPLAY_NAME);
              --wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'P_ASSIGNMENT_ID',V_ASSIGNMENT_ID);
           
              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_AME_TRAN_TYPE_ATTR','SSHRMS'); 
              --wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'P_CALLED_FROM',V_CALLED_FROM);
              --wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'P_EFFECTIVE_DATE', TO_CHAR(SYSDATE,'YYYY-MM-DD'));
           
              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_FIRST_ACTIVITY_ID',V_activity_id);

              wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'SESSION_ID',fnd_global.session_id);--??
           
              dbms_output.put_line('loc4');
              --SELECT_ABSENCE_NAME add later
           
               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_ABS_NTF_SS');

                 --dbms_output.put_line ('Done SetItemAttr');
              
                 --added by afzal from here and resolved error Error:You do not have access to the selected person's record. Please contact your Human Resources Adminstrator.
              
                 wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'FYI_NTF_DETAILS','Y');
                 wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'HR_PERZ_ORGANIZATION_ID_ATTR','81'); --mandatory change 1 business group
                 --wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'PROCESS_ORDER_STRING','%347867%1%347874%2%347865%3%'); --mandatory change 2, chk from any txn applied from front end from table WF_ITEM_ATTRIBUTE_values
                 wf_engine.SetItemAttrText(v_itemtype, v_itemkey,'SAVE_ATTACHMENT','Y');
                 wf_engine.setitemattrnumber(v_itemtype, v_itemkey,'HR_AME_APP_ID_ATTR',800);
              

              
             dbms_output.put_line('loc5');
                return v_itemkey;
             
            Exception
               when Exc_Error
                   then
                        v_itemkey  :=-5;
                        return v_itemkey;                                                                       
             -----------------------------------------------------------------------------------------------
             -----------------------------------------------------------------------------------------------
            --HR_APPROVAL_PRC_VERSION     V5
            --PROCESS_ORDER_STRING          %184884%1%
            --TRAN_SUBMIT                            W          
            end;

    --
    --Procedure to Update WorkFlow Process Attributes
    --Should Run After Create transactions and before start process Aprovals
    Procedure Update_WorkFlow_Attributes (P_itemtype                  varchar2
                                                             , P_itemkey                    varchar2
                                                             , P_TRANSACTION_ID     Number
                                                              )
   is
       begin
           wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'TRAN_SUBMIT','Y');           
           wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'TRANSACTION_ID',P_TRANSACTION_ID);
           wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'HR_RUNTIME_APPROVAL_REQ_FLAG','YD');
           wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'HR_NTF_ATTACHMENTS_ATTR','FND:entity=PQH_SS_ATTACHMENT&'||'pk1name=TransactionId&'||'pk1value='||P_TRANSACTION_ID);
           --wf_engine.SetItemAttrText(P_itemtype, P_itemkey,'HR_NTF_SUB_FND_MSG_ATTR','HR_ABS_NTF_SUB_MSG');
           --wf_engine.SetItemAttrText(p_itemtype, p_itemkey,'FORWARD_TO_ITEM_ID_ATTR',P_transaction_id); 
           --wf_engine.SetItemAttrText(p_itemtype, p_itemkey,'HR_APR_ITEM_ID_ATTR',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 DEFAULT NULL                                            
                                                        )
    is
        v_result   varchar2(5000);
    begin    
          pqh_ss_workflow.start_approval_wf  ( itemtype   =>P_itemtype,
                                                                   itemkey    =>P_itemkey,
                                                                   --actid        =>'184884',
                                                                   actid        => p_activity_id,--339
                                                                   funcmode =>'RUN',
                                                                   result    =>v_result   );
                                                                                                                                              
          dbms_output.put_line ('v_result='||v_result);
   end;
   
 
    --

FUNCTION CREATE_TRANSACTION (
                                                      P_CREATOR_PERSON_ID   NUMBER
                                                      ,P_SELECTED_PERSON_ID NUMBER
                                                    , P_ITEM_TYPE                     VARCHAR2
                                                    , P_ITEM_KEY                       VARCHAR2
                                                    , P_PROCESS_NAME              VARCHAR2
                                                    , P_CALLED_FROM                 VARCHAR2
                                                    , P_EIT_NAME                       VARCHAR2 DEFAULT NULL
                                                    , P_ACTIVITY_ID          NUMBER  DEFAULT NULL
                                               ,p_AbsenceAttendanceTypeId  number
                                               ,p_AbsAttendanceReasonId number
                                               ,p_AbsenceDays  number
                                               ,p_DateStart  varchar2 --FORMAT  '2018-08-04';
                                               ,p_DateEnd  varchar2 --FORMAT  '2018-08-04';
                                               ,p_comments varchar2  DEFAULT NULL
                                                   ,P_INFORMATION1                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION2                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION3                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION4                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION5                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION6                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION7                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION8                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION9                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION10                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION11                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION12                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION13                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION14                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION15                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION16                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION17                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION18                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION19                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION20                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION21                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION22                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION23                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION24                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION25                      varchar2 DEFAULT NULL
                                               ,P_INFORMATION26                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION27                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION28                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION29                       varchar2 DEFAULT NULL
                                               ,P_INFORMATION30                       varchar2 DEFAULT NULL
                                                                                                   
                                                  )
  RETURN NUMBER
   is
        --p_item_type varchar2(200) := 'HRSSA';
--p_item_key varchar2(200) := hr_workflow_item_key_s.nextval;
--P_process_name varchar2(200) := 'HR_GENERIC_APPROVAL_PRC';
--P_CREATOR_PERSON_ID number := 26876;
--P_CALLED_FROM varchar2(200) := 'HR_LOA_SS';
--p_AbsenceAttendanceTypeId  number := 64;
--p_AbsenceDays  number :=  1;
--p_DateStart  varchar2(20) := '2018-08-04';
--p_DateEnd  varchar2 (20) := '2018-08-04';
p_userid number;
p_leave_type varchar2(200) ;
v_object_identifier varchar2(200);

        v_validate                               boolean   default false  ;
        v_creator_person_id                number;
        v_transaction_privilege             varchar2(500);
        v_product_code                       varchar2(500) default null;
        v_url                                       varchar2(500) default null;
        v_status                                  varchar2(500) default null;
        v_section_display_name           varchar2(500) default null;
        v_function_id                           number;              
        v_transaction_ref_table             varchar2(500) default null;
        v_transaction_ref_id                 number default null;
        v_transaction_type                   varchar2(500) default null;
        v_assignment_id                      number default null;
        v_api_addtnl_info                     varchar2(500) default null;
        v_selected_person_id               number default null;
        v_item_type                             varchar2(500) default null;
        v_item_key                              varchar2(500) default null;
        v_transaction_effective_date     date default null;
        v_process_name                      varchar2(500) default null;
        v_plan_id                                 number default null;
        v_rptg_grp_id                           number default null;
        v_effective_date_option             varchar2(500) default null;
        V_activity_id                            number;
        V_ACTIVE_ROW_ID                  number;
         v_api_name                           varchar2(500);
         V_EIT_NAME                          varchar2(500); 
         v_dummy                                varchar2(500) default null;
              
     
        --------OUT---------
        v_transaction_id                         number :=  hr_api_transactions_s.nextval ;
        V_transaction_step_id                 number ;
        V_object_version_number           number ;
        P_ID_FLEX_NUM                       NUMBER;
        V_BUSINESS_GROUP_ID                 NUMBER;
        ln_analysis_criteria_id             number;
        V_EIT_FULL_NAME                     VARCHAR2(200);
     v_relaunch_function varchar2(200) := 'HR_ABS_ENTRY_PAGE_SS';
     v_transaction_group varchar2(200) := 'ABSENCE_MGMT';
     v_transaction_identifier varchar2(200) := 'ABSENCES';
     v_xml_document clob;
     v_attendance_id number := PER_ABSENCE_ATTENDANCES_S.NEXTVAL;
          
V_MSG VARCHAR2(20000);
v_xml clob;

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            :='PER_ABSENCE_ATTENDANCES';
     v_transaction_ref_id                 :=PER_ABSENCE_ATTENDANCES_S.currval;-------------------------
     v_transaction_type                   :='WF';
  
     v_product_code                       :='PER';
     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;
     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_PERSON_ABSENCE_SWI.PROCESS_API';--constant
     --V_EIT_NAME                              := P_EIT_NAME;--
     v_dummy                                  :=TO_CHAR(SYSDATE,'yyyymmddhh24miss')||P_CREATOR_PERSON_ID;
    

    --
    --Get Function_id
    --  
     select FUNCTION_ID--, function_name
       into v_function_id
      from FND_FORM_FUNCTIONS
    where 1=1
    --and function_id = 12238
        and FUNCTION_NAME=P_CALLED_FROM;
       
        select  '000100000004'||RAWTOHEX(PER_ABSENCE_ATTENDANCES_S.CURRVAL)
        into v_object_identifier from dual;
       
         begin
        
        
          select --tl.NAME, v_leave_name_ar
          (SELECT DISTINCT meaning  FROM fnd_lookup_values
                        WHERE lookup_type = 'ABSENCE_CATEGORY'
                        AND LANGUAGE = 'US'
                        AND UPPER (lookup_code) = UPPER (ABSENCE_CATEGORY))Meaning 
          into  p_leave_type
          from PER_ABS_ATTENDANCE_TYPES_tl tl,
          per_absence_attendance_types abs
          where language  = 'AR' --in wf_attribute_values it is storing in arabic in our instance, so i am using this
          and abs.ABSENCE_ATTENDANCE_TYPE_ID = tl.ABSENCE_ATTENDANCE_TYPE_ID
          and abs.ABSENCE_ATTENDANCE_TYPE_ID = p_AbsenceAttendanceTypeId
          ;
         
         
         
          dbms_output.put_line ('p_leave_type  ='||p_leave_type );                                       
                Exception
                     when no_data_found
                       then                      
                      dbms_output.put_line ('Error ='||sqlerrm);                     
            End;       

 
                            Begin
                           
                         select  distinct p.assignment_id
                            into  V_ASSIGNMENT_ID     
                          from per_all_assignments_f p--, fnd_user fu
                        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 fu.employee_id = p.person_id
                            and primary_flag ='Y'
                            AND ROWNUM = 1
                            ;
                           
                          SELECT DISTINCT USER_ID
                          INTO p_userid 
                          FROM FND_USER 
                          WHERE EMPLOYEE_ID = P_CREATOR_PERSON_ID
                          AND END_DATE IS NULL;      
                         
                            dbms_output.put_line ('p_userid ='||p_userid);                                       
                Exception
                     when no_data_found
                       then                      
                      dbms_output.put_line ('Error ='||sqlerrm);                     
            End;                                   
     
 

  
 
IF P_CREATOR_PERSON_ID = P_SELECTED_PERSON_ID THEN
BEGIN
P_XML_TRANSACTION_DOCUMENT2(
hr_api_transactions_s.currval,
P_CREATOR_PERSON_ID,
P_SELECTED_PERSON_ID,
12238,
PER_ABSENCE_ATTENDANCES_S.NEXTVAL,
PER_ABSENCE_ATTENDANCES_S.currVAL,
'CreateMode',
p_AbsenceAttendanceTypeId,
p_AbsAttendanceReasonId,
p_AbsenceDays,
p_DateStart,
p_DateEnd,
p_comments,
p_userid,
v_xml,
V_MSG);
exception when others then null;
dbms_output.put_line(v_xml);
dbms_output.put_line(v_msg);
      END;   
      --select * from hr_api_transactions where creation_date like sysdate - 2
ELSE
BEGIN
P_XML_TRANSACTION_DOCUMENT_MGR(
hr_api_transactions_s.currval,
P_CREATOR_PERSON_ID,
P_SELECTED_PERSON_ID,
12238,
PER_ABSENCE_ATTENDANCES_S.NEXTVAL,
PER_ABSENCE_ATTENDANCES_S.currVAL,
'CreateMode',
p_AbsenceAttendanceTypeId,
p_AbsAttendanceReasonId,
p_AbsenceDays,
p_DateStart,
p_DateEnd,
p_comments,
p_userid,
v_xml,
V_MSG);
exception when others then null;
dbms_output.put_line(v_xml);
dbms_output.put_line(v_msg);
      END;
      END IF;
      --select * from hr_api_transactions where creation_date like sysdate - 2

     
 --
 --Start create_transaction-----------------------------------------------------
 --
  --hr_transaction_swi.create_transaction
       hr_transaction_swi.create_transaction
                                                                          (--p_validate                               =>v_validate 
                                                                          p_transaction_id                      =>v_transaction_id               
                                                                          ,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
                                                                          ,P_RELAUNCH_FUNCTION => v_relaunch_function
                                                                          ,P_TRANSACTION_GROUP => v_transaction_group
                                                                          ,P_TRANSACTION_IDENTIFIER => v_transaction_identifier
                                                                          ,P_TRANSACTION_DOCUMENT =>v_xml
                                                                          );

  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                      =>null
                                                                          --Out
                                                                          ,p_transaction_step_id         =>   V_transaction_step_id      
                                                                          ,p_object_version_number   =>   V_object_version_number
                                                                          );
                                                                                                     

  dbms_output.put_line ('V_transaction_step_id='||V_transaction_step_id);
  --dbms_output.put_line ('V_object_version_number='||V_object_version_number );
    --
   */
   select hr_api_transaction_steps_s.nextval
             into   V_transaction_step_id
             from   dual;
             --l_activity_id := '276385';
             hr_transaction_swi.create_transaction_step(
                                                  p_api_name                  => v_api_name
                                                 ,p_api_display_name          => null
                                                 ,p_processing_order          => 0
                                                 ,p_item_type                 => v_item_type
                                                 ,p_item_key                  => v_item_key  
                                                 ,p_creator_person_id         => v_creator_person_id 
                                                 --,p_activity_id               =>  l_activity_id
                                                 ,p_update_person_id          =>  v_creator_person_id           -- number    default null
                                                 ,p_object_state              => '0'
                                                 ,p_pk1                       => v_transaction_ref_id
                                                 ,p_object_version_number     => v_object_version_number
                                                 ,p_transaction_id            => v_transaction_id
                                                 ,p_transaction_step_id       => v_transaction_step_id
                                                 ,P_OBJECT_TYPE => 'ENTITY'
                                                ,P_OBJECT_NAME => 'oracle.apps.per.schema.server.PerAbsenceAttendancesEO'
                                                ,P_OBJECT_IDENTIFIER => v_object_identifier
                                                --,P_PK1 =>
                                                ,P_INFORMATION1 =>p_DateStart
                                                ,P_INFORMATION2 =>p_DateEnd
                                                ,P_INFORMATION5 => p_AbsenceAttendanceTypeId
                                                ,P_INFORMATION6 => p_leave_type--change this to dynamic later
                                                ,P_INFORMATION8 => p_AbsenceDays
                                                ,P_INFORMATION9 => 'CONFIRMED'
                                                ,P_INFORMATION30 => 'ATT'
                                                 );
 
           
              return  v_transaction_id;
  end;
 
 -- Procedure create the Request
  --

  -- Procedure create the Request
  --
 Procedure CREATE_REQUEST                (     PP_SELECTED_USER_NAME                   varchar2--1024
                                                ,PP_CREATOR_USER_NAME     varchar2--172
                                                ,PP_ITEM_TYPE                     varchar2--'HRSSA'
                                               ,PP_RESPONSIBILITY_NAME     varchar2--'MOFI Absence Management';
                                               ,PP_CALLED_FROM                  varchar2 --'MOF_HR_LOA_CREATE_DECREE_SS'
                                               ,PP_process                            varchar2--'MOF_HR_EIT_LOA_CREATE_PRC'
                                               ,p_AbsenceAttendanceTypeId  number
                                               ,p_AbsAttendanceReasonId number
                                               ,p_AbsenceDays  number
                                               ,p_DateStart  varchar2 --FORMAT  '2018-08-04';
                                               ,p_DateEnd  varchar2 --FORMAT  '2018-08-04';  
                                               ,PP_COMMENTS VARCHAR2 DEFAULT NULL                                                                                        
                                               ,PP_EIT_NAME                       varchar2 DEFAULT NULL--'MOF_HR_LOA_CREATE_DECREE'
                                               ,PP_INFORMATION1                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION2                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION3                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION4                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION5                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION6                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION7                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION8                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION9                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION10                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION11                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION12                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION13                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION14                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION15                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION16                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION17                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION18                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION19                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION20                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION21                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION22                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION23                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION24                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION25                      varchar2 DEFAULT NULL
                                               ,PP_INFORMATION26                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION27                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION28                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION29                       varchar2 DEFAULT NULL
                                               ,PP_INFORMATION30                       varchar2 DEFAULT NULL
                                               ,p_activity_id                        number  DEFAULT NULL
                                               ,PP_ATTACHFILENAME                  varchar2 DEFAULT NULL
                                               ,PP_ATTACHFILENAME2                  varchar2 DEFAULT NULL

                                               --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;
   pattachstatus varchar2(2000);
    pattachstatus2 varchar2(2000);
     L_ERROR VARCHAR2(2000);
  --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
          AND ROWNUM =1;

          SELECT EMPLOYEE_ID
          INTO PP_SELECTED_PERSON_ID
          FROM FND_USER
          WHERE USER_NAME= PP_SELECTED_USER_NAME
          AND ROWNUM =1;
 --
      --1----------------------------------------------------------------------------------------
      --
       dbms_output.put_line('Create_WorkFlow_Process');
      v_Item_Key_id:= FUJ_HR_ABSENCE_WORKFLOW_PKG.Create_WorkFlow_Process (P_USER_NAME               =>PP_CREATOR_USER_NAME
                                                                                                                     ,P_CREATOR_PERSON_ID =>PP_CREATOR_PERSON_ID
                                                                                                                     ,P_CALLED_FROM            =>PP_CALLED_FROM
                                                                                                                     ,P_process                      =>PP_process
                                                                                                                     ,p_activity_id            => p_activity_id
                                                                                                                     ,p_AbsenceAttendanceTypeId => p_AbsenceAttendanceTypeId
                                                                                                                    ,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_ABSENCE_WORKFLOW_PKG.CREATE_TRANSACTION (
                                                              P_CREATOR_PERSON_ID    =>PP_CREATOR_PERSON_ID
                                                              ,P_SELECTED_PERSON_ID  => PP_SELECTED_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_AbsenceAttendanceTypeId  => p_AbsenceAttendanceTypeId
                                                               ,p_AbsAttendanceReasonId  =>p_AbsAttendanceReasonId
                                                               ,p_AbsenceDays  => p_AbsenceDays
                                                               ,p_DateStart  => p_DateStart--FORMAT  '2018-08-04';
                                                               ,p_DateEnd  => p_DateEnd --FORMAT  '2018-08-04';
                                                               ,p_comments => pp_comments                                                            
                                                            , P_EIT_NAME                      => PP_EIT_NAME
                                                            ,P_INFORMATION1                       =>PP_INFORMATION1
                                                           ,P_INFORMATION2                       =>PP_INFORMATION2
                                                           ,P_INFORMATION3                       =>PP_INFORMATION3
                                                           ,P_INFORMATION4                       =>PP_INFORMATION4
                                                           ,P_INFORMATION5                       =>PP_INFORMATION5
                                                           ,P_INFORMATION6                       =>PP_INFORMATION6
                                                           ,P_INFORMATION7                       =>PP_INFORMATION7
                                                           ,P_INFORMATION8                       =>PP_INFORMATION8
                                                           ,P_INFORMATION9                       =>PP_INFORMATION9
                                                           ,P_INFORMATION10                       =>PP_INFORMATION10
                                                           ,P_INFORMATION11                       =>PP_INFORMATION11
                                                           ,P_INFORMATION12                       =>PP_INFORMATION12
                                                           ,P_INFORMATION13                       =>PP_INFORMATION13
                                                           ,P_INFORMATION14                       =>PP_INFORMATION14
                                                           ,P_INFORMATION15                       =>PP_INFORMATION15
                                                           ,P_INFORMATION16                       =>PP_INFORMATION16
                                                           ,P_INFORMATION17                       =>PP_INFORMATION17
                                                           ,P_INFORMATION18                       =>PP_INFORMATION18
                                                           ,P_INFORMATION19                       =>PP_INFORMATION19
                                                           ,P_INFORMATION20                       =>PP_INFORMATION20
                                                           ,P_INFORMATION21                       =>PP_INFORMATION21
                                                           ,P_INFORMATION22                       =>PP_INFORMATION22
                                                           ,P_INFORMATION23                       =>PP_INFORMATION23
                                                           ,P_INFORMATION24                      => PP_INFORMATION24
                                                           ,P_INFORMATION25                       => PP_INFORMATION25
                                                           ,P_INFORMATION26                       => PP_INFORMATION26
                                                           ,P_INFORMATION27                       => PP_INFORMATION27
                                                           ,P_INFORMATION28                       => PP_INFORMATION28
                                                           ,P_INFORMATION29                       => PP_INFORMATION29
                                                           ,P_INFORMATION30                       => PP_INFORMATION30
                                                      )  ;
          dbms_output.put_line ('v_trans_id='||v_trans_id);                                                                                                        
          --
          --3----------------------------------------------------------------------------------------
          --     
            if v_trans_id is  null
            then
                    v_msg:='Error--- trans_id  Not Generated';
                    dbms_output.put_line (v_msg);
            --Error
            else     
              --
              --4----------------------------------------------------------------------------------------
              --
              dbms_output.put_line('Update_WorkFlow_Attributes');    
               FUJ_HR_ABSENCE_WORKFLOW_PKG.Update_WorkFlow_Attributes  ( P_itemtype             =>PP_ITEM_TYPE 
                                                                                                             , P_itemkey               =>v_Item_Key_id
                                                                                                             , P_TRANSACTION_ID=>v_trans_id
                                                                                                             );                                                                                                                   
              --commit;
              --
              --5----------------------------------------------------------------------------------------
              --
                  FUJ_HR_ABSENCE_WORKFLOW_PKG.Start_WorkFlow_Process (P_itemtype   =>PP_ITEM_TYPE
                                                                                                      , P_itemkey   =>v_Item_Key_id);                                    
                --commit;                                                                                                                                                                                            
               --
              --6----------------------------------------------------------------------------------------
              --important to start Approval cycle
               FUJ_HR_ABSENCE_WORKFLOW_PKG.Start_Approval_Process (P_itemtype   =>PP_ITEM_TYPE
                                                                                              , P_itemkey    =>v_Item_Key_id , p_activity_id => p_activity_id);
                                                                                             
                                                                                             
                --7 Attachment
              
               IF PP_ATTACHFILENAME IS NOT NULL THEN
               BEGIN   
              P_UPLOAD_ATTACHMENT(v_trans_id,'Uploaded Description',PP_ATTACHFILENAME,PP_SELECTED_USER_NAME,pattachstatus);
              COMMIT;
              EXCEPTION WHEN OTHERS THEN
              --L_ERROR := SQLERRM;
              --insert into TESTA VALUES(1,'INSIDE-ATTACH1-EXCEPTION',L_ERROR);
              --COMMIT;
              NULL;
              END;         
              END IF;  
              IF PP_ATTACHFILENAME2 IS NOT NULL THEN
               BEGIN                                                                                  
             P_UPLOAD_ATTACHMENT(v_trans_id,'Uploaded Description',PP_ATTACHFILENAME2,PP_SELECTED_USER_NAME,pattachstatus2);
              EXCEPTION WHEN OTHERS THEN NULL;
              END;         
              END IF;                                                                                                                                                                                                                                          
                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;
/
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

4. block to the test the package
DECLARE
 P_msg                    varchar2(5000);
begin
 begin
                 FUJ_HR_ABSENCE_WORKFLOW_PKG.CREATE_REQUEST (     PP_SELECTED_USER_NAME                   =>'EG_AFZAL'
                                                               ,PP_CREATOR_USER_NAME => 'EG_AFZAL' --if supervisor is creating then giver his username
                                                               ,P_ACTIVITY_ID => 177352 --contant if not chk it from the below query
                                                               ,PP_ITEM_TYPE                =>'HRSSA'
                                                               ,PP_RESPONSIBILITY_NAME      =>'Employee Self Service Fujairah'                                                              
                                                               ,PP_CALLED_FROM              =>'HR_LOA_SS' --constant
                                                               ,PP_process                  =>'HR_GENERIC_APPROVAL_PRC'  --constant
                                                               ,p_AbsenceAttendanceTypeId  => 62 --Sick Leave
                                                               ,p_AbsAttendanceReasonId => 1061
                                                               --,P_ACTIVITY_ID => 177352 --contant if not chk it from the below query
                                                               ,p_AbsenceDays  => 1
                                                               ,p_DateStart  => '2018-09-10'
                                                               ,p_DateEnd  => '2018-09-10'
                                                               ,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;

commit


SELECT ITEM_TYPE, ITEM_KEY FROM HR_API_TRANSACTIONS
WHERE CREATION_DATE LIKE SYSDATE


--
SELECT WI.ITEM_TYPE
              ,WI.ITEM_KEY
              ,WI.BEGIN_DATE
              ,WPA.INSTANCE_ID ACTIVITY_ID
              ,WPA.ACTIVITY_NAME ACTIVITY_NAME
              ,WPA.PROCESS_NAME
          FROM APPS.WF_ITEMS WI
              ,APPS.WF_ITEM_ACTIVITY_STATUSES WIAS
              ,APPS.WF_PROCESS_ACTIVITIES WPA
         WHERE WI.ITEM_TYPE = WIAS.ITEM_TYPE
           AND WIAS.ITEM_TYPE = WPA.PROCESS_ITEM_TYPE
           AND WI.ITEM_KEY = WIAS.ITEM_KEY
           AND WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
           --AND WPA.ACTIVITY_NAME = UPPER('&Activity_Name')
           AND WI.ITEM_TYPE = UPPER('&Workflow_Item_Type')
           AND WIAS.ITEM_KEY = UPPER('&Workflow_Item_Key')
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5. check the notification_id and other details using below query.

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

 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 6. Approve from backend.

 DECLARE
PSTATUS VARCHAR2(200);PMSG VARCHAR2(200);
BEGIN
FUJ_HR_ABSENCE_WORKFLOW_PKG.APPROVE_REJECT_P('APPROVED','',9768701,PSTATUS,PMSG); --NOTIFICATION_ID FROM THE ABOVE QUERY
DBMS_OUTPUT.PUT_LINE(PSTATUS);
END;

COMMIT;


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    

11 comments:

  1. Hi afzal, it really helps us to create a transaction, but we are facing one issue, attachment is not appearing for approver and also in the details view page.
    Could you please advise!
    Appreciate your quick help!

    Regards
    Krish

    ReplyDelete
    Replies
    1. Krish,
      For attachments to appear.
      1. create directory in database with path.
      2. create folder in the server as per step1 and transfer any files in this folder
      3. use the above package to upload attachments with name of file from step 2.
      4. check the fnd_lobs tables once uploaded.

      Reply me if u need more info.

      Regards/Afzal.

      Delete
    2. This comment has been removed by the author.

      Delete
    3. This comment has been removed by the author.

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Dear Afzal,
    It's highly appreciative how you are helping others in their issues.
    I am seeking help about following:
    How can i get the data of Employees which are promoted/transferred through SSHR? Are there any tables who stores the Current and Proposed Information of Employee?
    I would highly grateful for your help.
    Thank You!

    ReplyDelete
    Replies
    1. 1. Please check the transaction tables for Current and pending SSHR transactions .
      hr_api_transactions
      hr_api_transaction_steps
      hr_api_transaction_values

      2. for approved from SSHR
      check for history (records cleared from transaction tables , will be saved in history tables)
      PQH_SS_TRANSACTION_HISTORY
      PQH_SS_STEP_HISTORY

      Delete
  4. Hello Afzal

    I have been trying to use your code for initiating the Absence SSHR transaction from back end. I am getting an error while executing the procedure (p_xml_transaction_document2).

    Error ORA-31020: The operation is not allowed, Reason: Invalid pl/sql DOM handle.

    Can you please advise some alternative.

    Regards

    Asad

    ReplyDelete
    Replies
    1. please check the procedure for generating xml is valid and share more details, sorry for late reply.

      Delete
  5. Hello Afzal,

    With the help of ur code. I am trying to terminate employee from backend.
    It's giving me error while approve by last approvers.

    Can you please advise on this

    ReplyDelete
    Replies
    1. Whats the error, can u pls share more details.. Thanks.

      Delete