How to display table with multiple rows in a workflow notification along with additional text

Prerequisites:
1) Create Item Type
2) Create Process
3) Create Notification
4) Create message



Step1- Create an Attribute of type document. Give value as constant "plsqlclob:XX_DEMO.getclobdocument/&ITEM_KEY"

2) Copy this attribute and paste it under message created.

3) Click on the message and go to body tab.Give subject and body here.In Body include document type attribute you have created above.

4)  Now we have to write a procedure which will create table with multiple rows data along with additional text.

  procedure getClobDocument(document_id   IN VARCHAR2,
                            display_type  IN VARCHAR2,
                            document      IN OUT CLOB,
                            document_type IN OUT VARCHAR2)

   IS
    CURSOR cur_proj_invoice IS
      select b.segment1 as proj_num,
             a.customer_trx_id,
             a.TRX_NUMBER,
             trx_date,
             a.CUSTOMER_REFERENCE,
             b.INVOICE_COMMENT,
             a.ATTRIBUTE15 as amount
        from ra_customer_trx_all a, pa_projects_all b
       where a.INTERFACE_HEADER_ATTRIBUTE1 = b.segment1
         and rownum <= 2;

    l_document             VARCHAR2(32767);

    cnt                    INTEGER;
    l_docid                pls_integer;
    l_errmsg               varchar2(100) := 'The Document is not found in the Database';
    l_bdoc                 clob;

  BEGIN

    l_docid := to_number(document_id);

    --l_docid := document_id;

    dbms_lob.CreateTemporary(l_bdoc, FALSE, DBMS_LOB.CALL);

    dbms_lob.createtemporary(document, FALSE, dbms_lob.CALL);

    l_document := NULL;

    l_document := '<br></br>' || /*l_po_type*/
                  'Please review the below information for project ' ||
                  l_po_num || ' and provide your Approval.<br><br>';
    l_document := l_document || '<b>Amount: </b>INR ' || l_amt_total ||
                  ' <br></br><b>Tax Amount: </b> INR ' || l_tax_amt ||
                  '<br></br><b>Supplier: </b>' || l_VENDOR_NAME ||
                  '<br></br><b>Supplier Site: </b>' || l_sup_site ||
                  '<br></br>';


    l_document := l_document ||
                  '<table border="0" cellspacing="0" cellpadding="0" ><tr><td style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:11pt;font-weight:bold"><u>Project Details </u></font></td></tr><tr><td><table width=100% border=0 cellpadding=0 cellspacing=0 ><tr><td>' ||
                  '<br></br>';
    -- '<table width=80% border="0" cellspacing="0" cellpadding="0" ><tr><td style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:11pt;font-weight:bold">Release Shipment Details </font></td></tr><tr><td><table width=100% border=0 cellpadding=0 cellspacing=0 ><tr><td>';
    l_document := l_document ||
                  '<table summary="" width=100% border=0 cellpadding=3 cellspacing=1 bgcolor=white >';
    l_document := l_document ||
                  '<tr><th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">Project No</span></th>';
    l_document := l_document ||
                  '<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">Customer Name.</span></th>';
    l_document := l_document ||
                  '<th scope=col align=LEFT valign=baseline bgcolor= #7EC0EE><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">Project Lead.</span></th>';
    l_document := l_document ||
                  '<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">Project financial Analyst.</span></th>';
    /*l_document := l_document ||
                  '<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">CFC biller</span></th>';
    l_document := l_document ||
                  '<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">Quantity</span></th>';
    l_document := l_document ||
                  '<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">Unit Price</span></th>';*/
    /*l_document := l_document ||
                  '<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">Location</span></th></tr>';
    l_document := l_document ||
                  '<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">Ship-To Org</span></th>';
    l_document := l_document ||
                  '<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">Need-By Date</span></th></tr>';*/
    l_document := l_document ||
                  '<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">Amount</span></th></tr>';

    FOR cnt IN cur_proj_invoice LOOP
 
      l_document := l_document ||
                    '<tr><td align=LEFT valign=baseline bgcolor=#EEEEE0><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">' ||
                    cnt.proj_num || '</span></td>';
      l_document := l_document ||
                    '<td align=LEFT valign=baseline bgcolor=#EEEEE0><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">' ||
                    cnt.customer_trx_id || '</span></td>';
      l_document := l_document ||
                    '<td align=LEFT valign=baseline bgcolor=#EEEEE0><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">' ||
                    cnt.TRX_NUMBER || '</span></td>';
      l_document := l_document ||
                    '<td align=LEFT valign=baseline bgcolor=#EEEEE0><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">' ||
                    cnt.trx_date || '</span></td>';
      /*      l_document := l_document ||
                    '<td align=LEFT valign=baseline bgcolor=#EEEEE0><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">' ||
                    cnt.CUSTOMER_REFERENCE || '</span></td>';
      l_document := l_document ||
                    '<td align=LEFT valign=baseline bgcolor=#EEEEE0><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">' ||
                    cnt.INVOICE_COMMENT || '</span></td>';
      l_document := l_document ||
                    '<td align=LEFT valign=baseline bgcolor=#EEEEE0><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">' ||
                    cnt.INVOICE_COMMENT || '</span></td>';*/
      /*      l_document := l_document ||
                   '<td align=LEFT valign=baseline bgcolor=#EEEEE0><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">' ||
                   cnt.location_code || '</span></td></tr>';
      l_document := l_document ||
                   '<td align=LEFT valign=baseline bgcolor=#EEEEE0><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">' ||
                   cnt.name || '</span></td></tr>';
       l_document := l_document ||
                   '<td align=LEFT valign=baseline bgcolor=#EEEEE0><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">' ||
                   cnt.need_by_date || '</span></td></tr>'; */
      l_document := l_document ||
                    '<td align=LEFT valign=baseline bgcolor=#EEEEE0><span style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:10pt">' ||
                    cnt.INVOICE_COMMENT || '</span></td></tr>';
    END LOOP;

    l_document := l_document ||
                  '</table></td></tr></table></td></tr></table><br>';

    dbms_lob.writeappend(l_bdoc, length(l_document), l_document);

    l_nm_clob_length := dbms_lob.getlength(l_bdoc);

    dbms_lob.Copy(document, l_bdoc, l_nm_clob_length, 1, 1);

  EXCEPTION
    when others then
      wf_core.context('XX_DEMO', 'getClobDocument', document_id);
      raise;
  end getClobDocument;

How to make comments field mandatory for an approver in workflow notification

1) open workflow builder tool and connect to data base.

2) search for "standard" workflow

3) drag this to left hand side as below and click ok.

4)Right click on standard and click on new then Item type

5) give name and description And save

6)right click on process and New process. give details and save.

7) right click on attribute and new attribute. give details and save.

8) right click on message and new message. give all details and save.

9) go to result tab of the above window. select this details and save.

10) after creating message.Add comments attribute from attribute and paste it in the message and select attribute as respond type.

11) click on notification and new notification. give details and save.in this window select function as pl sql package and procedure name where we will write logic to make comments field mandatory.

12) Click on process, it will open blank window like below

13) copy start and end function from standard workflow and paste it under custom workflow function section.

drag both function to the process window

14) drag notification also in this window and connect these icons with each other as below and apply:

15) click on start activity and go to node tab. select value as start from drop down as below:

16)  click on End activity and go to node tab. select value as end from drop down as below and Apply:

17) now workflow builder part is finished. Now we will see pl/sql part. first create sequence named as XX_SEQ in the data base which will be used to store item key value. This is the code below


CREATE OR REPLACE PACKAGE BODY XX_DEMO IS
  PROCEDURE LAUNCH_WORKFLOW
   IS
    v_master_form_link varchar2(5000);
    v_item_number      varchar2(100);
    v_add_item_id      varchar2(5000);
    error_code         varchar2(100);
    error_msg          varchar2(5000);
    v_item_key         VARCHAR2(10) := to_char(xx_SEQ.nextval);
    v_item_type        varchar2(10) := 'XX_TEST';
    v_process          varchar2(20) := 'XX_PROC';
  BEGIN

    WF_ENGINE.CREATEPROCESS(v_item_type, v_item_key, v_process);


    WF_ENGINE.STARTPROCESS(v_item_type, v_item_key);
    commit;

  EXCEPTION
    WHEN OTHERS THEN
      error_code := SQLCODE;
      error_msg  := SQLERRM(SQLCODE);
      -- add dbms or fnd_output messages as required
  END LAUNCH_WORKFLOW;

  procedure check_comment_reject(ITEMTYPE  IN VARCHAR2,
                                 ITEMKEY   IN VARCHAR2,
                                 ACTID     IN NUMBER,
                                 FUNCMODE  IN VARCHAR2,
                                 RESULTOUT IN OUT VARCHAR2) is
    l_notfication_result varchar2(1000);
    l_sup_comments       varchar2(10000);

  begin

    if (FUNCMODE = 'RESPOND') then
      l_notfication_result := wf_notification.getAttrText(wf_engine.context_nid,
                                                          'RESULT');
      l_sup_comments       := wf_notification.GetAttrText(nid   => wf_engine.context_nid,
                                                          aname => 'XX_COMMENT');

      if (l_notfication_result = 'REJECTED') then
     
        if l_sup_comments is null then
          raise_application_error('-20002',
                                  'Comments are mandatory while rejecting request');
        else
          RESULTOUT := 'COMPLETE:REJECT';
        end if;
      elsif (l_notfication_result = 'APPROVED') then
        RESULTOUT := 'COMPLETE:APPROVE';
      end if;
        WF_ENGINE.SetItemAttrText(ITEMTYPE,
                                ITEMKEY,
                                'XX_COMMENT',
                                l_sup_comments);
    end if;
  exception
    when others then
      wf_core.context('XX_DEMO',
                      'check_comment_reject',
                      ITEMTYPE,
                      ITEMKEY,
                      ACTID,
                      FUNCMODE);
      raise;

  end;
END XX_DEMO;

Now if you are facing any difficulty, please put in comments, will respond you on the same.

How to install Jdevloper or Which version of OAFramework is required to download

Follow the below steps to download Jdeveloper for OA framework development.
1) Login to https://support.oracle.com/

 

 

 

 

 

 

 

 

 

 

 

 

 

2) Provide your user and and password and click on login button.

3) Search for 

OA Framework: How to Identify Required Oracle JDeveloper Patches For Oracle E-Business Release 12.x or 11i (Doc ID 416708.1)

 
4)

 4) you will see below zip file p9879989_R12_GENERIC.

 5) Click on the zip file and save it in your desktop.

 

How to Enable AME for GL Journal Approval

Prerequisites for doing AME setup
1) Person should have "Approval management Business Analyst" responsibility and "Approval management Administrator" responsibility  attached.
2) All roles for Approval management should be added to user login by using "user management" responsibility.
3) Grant Access has been given for AME.

Front End Setup steps in GL Module

Step1 : Enable journal Approval in ASM

Go to General Ledger Super User -> Setup -> Financials -> Accounting Setup Manager -> Accounting Setups

 Here search for ledger you want to enable AME for and click on Go button.

Click on Update Accounting Options:

In primary Ledger Section, Click on update in first row i.e. Ireland USD.

Now in left hand section, Click on Ledger options, you will be taken to the below screen:

Now in "Journal processing " Section , select the check box "Enable Journal Approval". Click on next, next and finish.

 Step2 : Enable require journal Approval in Journal Source

Go to General Ledger Super User -> Setup -> Journal -> Sources

 

 

 

Now query for the source for which you want to enable AME. and select the check box of "Require Journal Approval" and save the details.

 

 

 

 Step3: Set the required profile options:

Mandatory:

GL: Use Approval Management (AME) for journal approval  -  "Yes"
Journals: Allow Preparer Approval                                           -  "Yes"

 

Optional:

Journals: Find Approver Method                                            - "Go Direct"
GL: Number of journal lines to display in AME journal approval notification - "10"

 

 

Step4 : AME SETUP Steps

1) Create the Attribute as per your business requirement

2) Create the conditions based on attributes

3) Create Approver Group either static or dynamic based on your requirement

4) Use Existing Action type as "Approval group of chain authority".

5) Create an AME rule by using condition and approver group created.

 

Test the AME setup if done properly or not by using test workbench by providing Transaction id .Once journal Entry is created, data goes into GL_JE_HEADERS and GL_JE_LINES table. JE_BATCH_ID value is used as a TRANSACTION_ID in AME.

 

To create Journal Entry go to general Ledger Super User-> Journals -> Enter

 

 

 

When you save these details , below Approve button will get enabled. Once you press Approve button, it will send notification to Approver.

 

 

I hope this article will be useful for you people. Please let me know if you have any questions on the same. post your queries in comments section, will respond you.

 

 

 

 

Create Grant Access for Transaction Types to User

1. Login to Oracle Application. Go to Function Administrator Responsibility and Under the security tab, click on Grant link. You will be taken to the below screen. Enter the fields as shown and click on next button

2.  Keep Data context as all rows and click on next.

3. Here Select  set as "AME Calling Applications". And click next button.

4. You will see the review page, check all the details here. If all fine click on Finish.

By this way you can create grant access to User for AME Transaction Types.

 

 

FNDLOAD upload and download commands in oracle apps

FNDLOAD UPLOAD AND DOWNLOAD COMMANDS

Message

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct <Name of ldt file>.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=<application short name> MESSAGE_NAME="message code"

FNDLOAD  <username>/<password>  0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct <Name of ldt file>..ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Concurrent Program

FNDLOAD  <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct <Name of ldt file>.ldt PROGRAM APPLICATION_SHORT_NAME=<application_short name> CONCURRENT_PROGRAM_NAME="XXCUST_CP"
FNDLOAD  <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct <Name of ldt file>.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Data Definition

FNDLOAD  <username>/<password> 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct <Name of ldt file>.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=<application_short name>  DATA_SOURCE_CODE=XXCUST_DS
FNDLOAD  <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct <Name of ldt file>.ldt

Request Set

FNDLOAD  <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct <Name of ldt file>.ldt REQ_SET REQUEST_SET_NAME='XXCUST_RS'

FNDLOAD  <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  <Name of ldt file>.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Request Set Link

FNDLOAD  <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct <Name of ldt file>.ldt REQ_SET_LINKS REQUEST_SET_NAME='XXCUST_LNK'

FNDLOAD  <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  <Name of ldt file>.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Lookups

FNDLOAD  <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct <Name of ldt file>.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=<application_short name> LOOKUP_TYPE=<lookup type>
FNDLOAD  <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct  <Name of ldt file>.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Profile

FNDLOAD  <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct <Name of ldt file>.ldt PROFILE PROFILE_NAME=<profile name> APPLICATION_SHORT_NAME=<application_short name>
FNDLOAD  <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct <Name of ldt file>.ldt 

Menu

FNDLOAD  <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct <Name of ldt file>.ldt MENU MENU_NAME=<Menu Name >
FNDLOAD  <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct <Name of ldt file>.ldt

Form Function

FNDLOAD  <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct <Name of ldt file>.ldt FUNCTION FUNC_APP_SHORT_NAME=<application short name > FUNCTION_NAME=<function name>
FNDLOAD  <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct <Name of ldt file>.ldt

Descriptive Flex fields with all of specific Contexts

 FNDLOAD   <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct <Name of ldt file>.ldt DESC_FLEX P_LEVEL=:COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME=<application_short name>
DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"
 FNDLOAD   <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct <Name of ldt file>.ldt


Request Group


 FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct <Name of ldt file>.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group"APPLICATION_SHORT_NAME=<application_short name>
 FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt

Value Sets & Value Sets with values

 FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct <name of ldt file>..ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"
 FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct <name of ldt file>.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"
 FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct <name of ldt file>.ldt
 FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt

Key Tables and Views in Oracle HRMS Module

Please find the list of key tables used in Oracle HRMS Module:

HRMS KEY TABLES

1)  per_all_people_f
This table will give the basic information about the employee.
per_people_f
Its secured view on top of per_all_people_f table
per_people_x
Its secured view on top of per_all_people_f table effective at system date

2)  per_all_assignments_f
This table will give the information which has been entered in the employee assignment screen.
per_assignments_f
Its secured view on top of per_all_assignments_f table
peR_assignments_x
Its secured view on top of per_all_assignments_f table effective at system date

3. per_addresses
This table will give all the information which has been entered in the employee address screen.

4. per_pay_proposals
It will give the information which has been entered in the employee salary screen.

5. per_person_types_tl
This is translatable table, which is used to find the type of the employee. This table is linked with the per_all_people_f with the person_type_id to find out the type of person.

6. per_all_positions
This table will give the employee's position information.
per_jobs_tl
This is translatable table, which  will contain the various types of JOBS in oracle.

per_positions
Its secured view on top of per_all_positions table

7. per_grades_tl
This is translatable table, which  will store the various types of GRADES in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct grade name from the employee.

8. hr_locations_all
This table will store the different LOCATIONS in oracle. This table is been linked with the per_all_assignments_f table to retrieve the correct location name from the employee.

9. pay_all_payrolls_f
This table will contain the different types of PAYROLLS in oracle. This table has been linked with the per_all_assignments_f table to retrieve the payroll name from the employee.

10. per_pay_bases
This table will store the different types of PAY BASES in oracle. This table is been linked with the per_all_assignments_f table to retrieve the pay basis name from the employee.

11. per_assignment_status_types_tl
This table will contain the different types of assignment types in oracle.

12. per_person_type_usages_f
This table will store the correct person type of the particluar employee. We should never look on the person type present in the per_all_people_f table. we need to link the person_id with this table and get the correct person type.

13. hr_all_organization_units
This table will store the employee's s organization information
hr_organization_units
Its secured view on top of hr_all_organization_units table

I hope it will be useful for you. If you have any query, write in comments box.