AME Configuration

 

Login -> Approvals Management Administrator -> Admin Dashboard

 

 

Application: Human Resorce

Transaction Type Key: QAG_CABIN_CREW_UPGRADE_TRANS_TYPE

Transaction Type Name: QAG Cabin Crew Upgrade Transaction Process

 

 

REJECTTION_RESPONSE: Stop all Processing

WORKFLOW_ITEM_KEY =:

Pseudo Code:

select item_key from QAIR.qag_hr_cc_upgrade_batch_stg  where transaction_id = :transactionId and process_name = 'QAG_QACCUPGR_AUTOMATION' and rownum = 1

 

WORKFLOW_ITEM_TYPE: QACCUPGR

 

SSSSSS

 

 

Process name: QAG Cabin Crew Upgrade Transaction Process

Navigate to Approvals Management Business Analyst ->Business Analyst Dashboard

Select Approval Process Setup -> ‘QAG Cabin Crew Upgrade Transaction Process’ ->Select ‘Attributes’

 

 

->Use Existing Attributes 

Name : ALLOW_EMPTY_APPROVAL_GROUPS

 

 

 

->Create

Name: QAG_CCUPGRADE_PROCESS_NAME

Description: QAG CC Upgrade Process

Data Type: String

Value: QAG_QACCUPGR_AUTOMATION

 

 

 

->Navigate to Conditions Tab:

Attribute: QAG_CCUPGRADE_PROCESS_NAME

String Value: QAG_QACCUPGR_AUTOMATION

 

 

->Navigate ->Action Type (Tab) ->Use Existing Action Type

Select

1)      pre-chain-of-authority approvals

2)      Production rule

3)      approval-group chain of authority

4)      post-chain-of-authority approvals

 

 

->Navigate -> Approver Groups (Tab)

 

 

Name: QAG_HR_CC_UPGRADE_APP1

Description: QAG CC Upgrade First Approver

Order Number: 100

Usage Type: Dynamic

Query:

SELECT 'person_Id:'

  ||person_id

  FROM per_all_people_f papf,fnd_lookup_values flv

  WHERE papf.employee_number = flv.description

  and flv.enabled_flag = 'Y'

  and flv.meaning like 'FIRST_APPROVER%'

and flv.lookup_type = 'QAG_CC_UPGRADE_APPROVERS_LIST'

and trunc(sysdate) between effective_start_date and effective_end_date

and current_employee_flag = 'Y'

 

Name: QAG_HR_CC_UPGRADE_APP2

Description: QAG CC Upgrade Second Approver

Order Number: 200

Usage Type: Dynamic

SELECT 'person_Id:'

  ||person_id

  FROM per_all_people_f papf,fnd_lookup_values flv

  WHERE papf.employee_number = flv.description

  and flv.enabled_flag = 'Y'

  and flv.meaning like 'SECOND_APPROVER%'

and flv.lookup_type = 'QAG_CC_UPGRADE_APPROVERS_LIST'

and trunc(sysdate) between effective_start_date and effective_end_date

and current_employee_flag = 'Y'

 

Name: QAG_HR_CC_UPGRADE_APP3

Description: QAG CC Upgrade Third Approver

Order Number: 300

Usage Type: Dynamic

 

SELECT 'person_Id:'

  ||person_id

  FROM per_all_people_f papf,fnd_lookup_values flv

  WHERE papf.employee_number = flv.description

  and flv.enabled_flag = 'Y'

  and flv.meaning like 'THIRD_APPROVER%'

and flv.lookup_type = 'QAG_CC_UPGRADE_APPROVERS_LIST'

and trunc(sysdate) between effective_start_date and effective_end_date

and current_employee_flag = 'Y'

Name: QAG_HR_CC_UPGRADE_APP4

Description: QAG CC Upgrade Fourth Approver

Order Number: 400

Usage Type: Dynamic

 

SELECT 'person_Id:'

  ||person_id

  FROM per_all_people_f papf,fnd_lookup_values flv

  WHERE papf.employee_number = flv.description

  and flv.enabled_flag = 'Y'

  and flv.meaning like 'FOURTH_APPROVER%'

and flv.lookup_type = 'QAG_CC_UPGRADE_APPROVERS_LIST'

and trunc(sysdate) between effective_start_date and effective_end_date

and current_employee_flag = 'Y'

Name: QAG_HR_CC_UPGRADE_APP5

Description: QAG CC Upgrade Fifth Approver

Order Number: 500

Usage Type: Dynamic

 

SELECT 'person_Id:'

  ||person_id

  FROM per_all_people_f papf,fnd_lookup_values flv

  WHERE papf.employee_number = flv.description

  and flv.enabled_flag = 'Y'

  and flv.meaning like 'FIFTH_APPROVER%'

and flv.lookup_type = 'QAG_CC_UPGRADE_APPROVERS_LIST'

and trunc(sysdate) between effective_start_date and effective_end_date

and current_employee_flag = 'Y'

 

 

Name: QAG_HR_CC_UPGRADE_APP6

Description: QAG CC Upgrade Sixth Approver

Order Number: 600

Usage Type: Dynamic

 

SELECT 'person_Id:'

  ||person_id

  FROM per_all_people_f papf,fnd_lookup_values flv

  WHERE papf.employee_number = flv.description

  and flv.enabled_flag = 'Y'

  and flv.meaning like 'SIXTH_APPROVER%'

and flv.lookup_type = 'QAG_CC_UPGRADE_APPROVERS_LIST'

and trunc(sysdate) between effective_start_date and effective_end_date

and current_employee_flag = 'Y'

 

 

 

 

 

 

 

 

 

 

Navigate ->Rules -> Create

 

 

Name : QAG_HR_CC_UPGRADE_PROC_RULE

 

OAF How to invoke concurrent program with parameters on button event

package cnsi.oracle.apps.fnd.webui;

import java.util.Vector; import oracle.apps.fnd.common.VersionInfo; import oracle.apps.fnd.cp.request.ConcurrentRequest; import oracle.apps.fnd.framework.OAApplicationModule; import oracle.apps.fnd.framework.OAException; import oracle.apps.fnd.framework.server.OADBTransaction; import oracle.apps.fnd.framework.webui.OAControllerImpl; import oracle.apps.fnd.framework.webui.OAPageContext; import oracle.apps.fnd.framework.webui.beans.OAWebBean; /** * Controller for ... */ public class InvokeConcurrentProgCO extends OAControllerImpl { public static final String RCS_ID="$Header$"; public static final boolean RCS_ID_RECORDED = VersionInfo.recordClassVersion(RCS_ID, "%packagename%"); /** * Layout and page setup logic for a region. * @param pageContext the current OA page context * @param webBean the web bean corresponding to the region */ public void processRequest(OAPageContext pageContext, OAWebBean webBean) { super.processRequest(pageContext, webBean); } /** * Procedure to handle form submissions for form elements in * a region. * @param pageContext the current OA page context * @param webBean the web bean corresponding to the region */ public void processFormRequest(OAPageContext pageContext, OAWebBean webBean) { super.processFormRequest(pageContext, webBean); String eventNameStr= pageContext.getParameter(EVENT_PARAM); if(eventNameStr!=null && "invoke_event".equals(eventNameStr)) { int requestID = submitCPRequest(pageContext,webBean); throw new OAException("COncurrent Request ID-->"+requestID); } } public int submitCPRequest(OAPageContext pageContext, OAWebBean webBean) { OAApplicationModule am = (OAApplicationModule)pageContext.getApplicationModule(webBean); try { OADBTransaction tx = (OADBTransaction)am.getOADBTransaction(); java.sql.Connection pConncection = tx.getJdbcConnection(); ConcurrentRequest cr = new ConcurrentRequest(pConncection); String applnName = "FND"; //Application that contains the concurrent program String cpName = "QAG_INV_REPORT_NEW"; //Concurrent program name String cpDesc = "Invoking From OAF"; // concurrent Program description // Pass the Arguments using vector // Here i have added my parameter headerId to the vector and passed the vector to the concurrent program String headerIDStr ="123"; //Number invoiceIDNum = new Number(123); Vector cpArgs = new Vector(); cpArgs.addElement(headerIDStr); // cpArgs.addElement("Enum"); // cpArgs.addElement("pBatch"); // Calling the Concurrent Program int requestId = cr.submitRequest(applnName, cpName, cpDesc, null, false, cpArgs); tx.commit(); return requestId; } catch (Exception e) { e.printStackTrace(); throw new OAException(e.getMessage()); } } }

Oracle Bursting Program

 1)Create Data definition.

2)Create Bursting File.

3)Template

4 ) After Trigger.

                              <?xml version="1.0" encoding="utf-8"?>

<dataTemplate name="QAG_HR_ISP_SEPARATION" description = "QAG HR Separation Report - ISP" Version="1.0" defaultPackage="xxqr_uip_utility_pkg">

  <properties>

<property name="xml_tag_case"      value="UPPER"/>

<property name="debug_mode"        value="ON"/>

<property name="include_rowsettag" value="FALSE"/>

  </properties>

   <parameters>

   </parameters>

<dataQuery>

<sqlstatement name="SEPARATION_REPORT">

<![CDATA[

SELECT papf.employee_number STAFF_NUMBER ,

  INITCAP (papf.title)

  || ' '

  || papf.first_name

  || ' '

  || DECODE (TRIM (papf.middle_names), NULL, TRIM (papf.last_name), TRIM (papf.middle_names)

  || ' '

  || TRIM (papf.last_name)) STAFF_NAME ,

  TO_CHAR(qr_final_settlement_cpnt.qr_service_date( papf.person_id) ,'DD-MON-YYYY') HIRE_DATE ,

  hr_general.decode_organization(paaf.organization_id) DEPARTMENT ,

  TO_CHAR(ppos.actual_termination_date,'DD-MON-YYYY') LWD ,

  TO_CHAR(ppos.LAST_UPDATE_DATE,'DD-MON-YYYY') LAST_UPDATE_DATE ,

  FU.USER_NAME LAST_UPDATED_BY ,

  'wassem@qatarairways.com.qa' EMAIL_TO ,

  'bvijayakumaran@qatarairways.com.qa' EMAIL_CC

FROM per_all_people_f papf,

  per_all_assignments_f paaf,

  per_periods_of_service ppos,

  FND_USER FU

WHERE papf.person_id              = paaf.person_id

AND primary_flag                  = 'Y'

AND ppos.person_id                = papf.person_id

AND ppos.actual_termination_date IS NOT NULL

AND FU.USER_ID                    = PPOS.LAST_UPDATED_BY

AND (trunc(ppos.actual_termination_date) = trunc(sysdate-1)

              or (ppos.actual_termination_date is not null and trunc(ppos.LAST_UPDATE_DATE) = trunc(sysdate-1) and trunc(ppos.actual_termination_date) < trunc(sysdate-1))

             )

AND ppos.actual_termination_date IS NOT NULL

--AND TRUNC(ppos.LAST_UPDATE_DATE)  = TRUNC(sysdate-1)

AND ppos.date_start               =

  (SELECT MAX (date_start)

  FROM per_periods_of_service

  WHERE person_id = ppos.person_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 employee_number NOT LIKE 'TSS%'

AND employee_number NOT IN ('99996','99997','IT001')

AND employee_number     IN

  (SELECT papf.employee_number

  FROM PER_ALL_PEOPLE_F PAPF,

    PER_ALL_ASSIGNMENTS_F PAAF ,

    PAY_ELEMENT_TYPES_F patf ,

    PAY_ELEMENT_ENTRIES_F peef

  WHERE PAPF.PERSON_ID =PAAF.PERSON_ID

  AND patf.element_name='QAG_ISP_INFO'

  AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE

  AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

  AND PATF.ELEMENT_TYPE_ID =PEEF.ELEMENT_TYPE_ID

  AND PEEF.ASSIGNMENT_ID   =PAAF.ASSIGNMENT_ID

  AND TRUNC(sysdate) BETWEEN peef.effective_start_date AND peef.effective_end_date

 -- AND current_employee_flag='Y'

  )

 ORDER BY 1,1 

   ]]> 

</sqlstatement>

<sqlstatement name="ROOT_DATA">

<![CDATA[

     SELECT TRUNC(SYSDATE) FROM DUAL

   ]]> 

</sqlstatement>

</dataQuery>  

<dataTrigger name="afterReport" source="xxqr_uip_utility_pkg.submit_bursting()"></dataTrigger>  

 <dataStructure>

   <group name="ROOT_DATA_GROUP" dataType="varchar2" source="ROOT_DATA">

       <group name="DATA_GROUP" dataType="varchar2" source="SEPARATION_REPORT">

             <element name="STAFF_NUMBER"      dataType="varchar2" value="STAFF_NUMBER"/>

             <element name="STAFF_NAME"        dataType="varchar2" value="STAFF_NAME"/>

             <element name="HIRE_DATE"         dataType="DATE"     value="HIRE_DATE"/>

             <element name="DEPARTMENT"        dataType="varchar2" value="DEPARTMENT"/>

             <element name="LWD"               dataType="varchar2" value="LWD"/>

             <element name="LAST_UPDATED_BY"   dataType="varchar2" value="LAST_UPDATED_BY"/>

             <element name="LAST_UPDATE_DATE"  dataType="varchar2" value="LAST_UPDATE_DATE"/>

             <element name="EMAIL_TO"          dataType="varchar2" value="EMAIL_TO"/>

             <element name="EMAIL_CC"          dataType="varchar2" value="EMAIL_CC"/>

       </group>

   </group>

</dataStructure>

</dataTemplate>

2)<?xml version="1.0" encoding="UTF-8"?>

<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">

<xapi:request select="/QAG_HR_ISP_SEPARATION/ROOT_DATA_GROUP">

<xapi:delivery>

<xapi:email id="123" server="qrsmtp.gmail.com" port="25" from="erpservices@gmail.com" reply-to="erpservices@gmail.com">

<xapi:message id="123" to="bvijayakumaran@gmail.com" bcc="bvijayakumaran@gmail.com" attachment="true" content-type="excel" subject="Separation Report">

Hi,

Please find the attached Separation Report List.

</xapi:message>

</xapi:email>

</xapi:delivery>

<xapi:document output="QAG_HR_ISP_SEPARATION" output-type="excel" delivery="123">

<xapi:template type="rtf" location="xdo://QAIR.QAG_HR_ISP_SEPARATION.en.US/?getSource=true">

</xapi:template>

</xapi:document>

</xapi:request>

</xapi:requestset>

FUNCTION submit_bursting

  RETURN BOOLEAN;

FUNCTION submit_bursting

  RETURN BOOLEAN

IS

  l_req_id          NUMBER;

  p_conc_request_id NUMBER := fnd_global.conc_request_id;

BEGIN

  l_req_id   := fnd_request.submit_request ('XDO' ,'XDOBURSTREP' ,'' ,'' ,FALSE ,'Y' ,p_conc_request_id ,'N' ,CHR (0) ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' );

  IF l_req_id > 0 THEN

    RETURN TRUE;

  ELSE

    RETURN FALSE;

  END IF;

END submit_bursting;

Staff Number	Staff  Name	Department	Last Working Day
FSTAFF_NUMBER	STAFF_NAME	DEPARTMENT	LWDE