Tuesday, June 23, 2015

Oracle Apps : Steps to Implement Employee/Supervisor Approval Hierarchy

Purchasing-> Setup-> Organization-> Financial Options



Uncheck “Use Approval Hierarchies”



Purchasing-> Setup-> Purchasing-> Document Types



Click the update icon for specific document as in our case “Standard Purchase Order”



“Owner Can Approve” should be Unchecked.
“Default Hierarchy” should be left blank.



HRMS Manager-> People-> Enter and Maintain-> Find->Assignments



Select Supervisor Tab and select Supervisor



Purchasing-> View-> Request-> ”Fill Employee Hierarchy”







3 Stars
Oracle Apps : Steps to Implement Employee/Supervisor Approval Hierarchy
Reviewed by Anurag Jainon Jul 07 1995
Rating: Your-Rating

Monday, June 22, 2015

Oracle Apps : Steps to implement Position Hierarchies

Create a User

System Administrator -> Security -> User

Assign Purchasing and HRMS Responsibility


Create Employee

HRMS Manager-> People-> Enter and Maintain ->New

Click Assignment Button to Assign Job and Position

Assign Job and Position

Assign Employee to User

Go to System Administrator -> Security - > User

If Employee is not a buyer, We will get a pop-up as below

Press “Add Buyer” Button to make an employee a buyer

Go to Purchasing->Setup->Personnel-> Buyer


To remove an employee as buyer Query the employee name and End Date it


Now Create a Purchase Order Purchasing- > Purchase Orders -> Purchase Orders

Enter the PO Details

Assigned User’s employee will be defaulted as Buyer

Press “Approve” Button to approve the PO


Document Types in Purchasing

Purchasing-> Setup-> Purchasing-> Document Types

“Owner Can Approve” Option if checked and employee is assigned to a Approval Group which has no limits to approve the PO created then created PO will get approved.

Setup Steps


Uncheck the “Owner Can Approve” box in Document Types under Standard Purchase Order
Purchasing-> Setup-> Purchasing -> Document Types

Assign the Default Hierarchy that needs to be followed in Document Types under Standard Purchase Order
Purchasing-> Setup-> Purchasing-> Document Types

Go to Financial Option and check “Use Approval Hierarchy”
Purchasing-> Setup-> Organization-> Financial Options

Position Hierarchy in HRMS Manager should have a correct assignments as required.
HRMS Manager ->Work Structures ->Position ->Hierarchy

Superiors approval Limit should be good enough to approve the Purchase Order created.

Defining Position Hierarchy




  • Purchasing-> Setup-> Purchasing->Document Types
  • “Owner Can Approve” Option Uncheck “Default Hierarchy” should be assigned
  • “Forward Method” if it should directly go to the next level employee who has the authority to approve the select “Direct” else select “Hierarchy”

Purchasing-> Setup-> Organization-> Financial Options


“Use Approval Hierarchy” box should be checked

Tuesday, June 9, 2015

Oracle Apps : Apply AP Invoice Hold using API

The hold codes can be extracted from query below.

SELECT *
  FROM ap_hold_codes
 WHERE hold_type = 'INVOICE HOLD REASON';

Below is the API to apply hold using Standard Oracle API :


PROCEDURE inv_hold (p_invoice_id        IN     NUMBER,
                    p_reject_code       IN     VARCHAR2,
                    p_hold_val_status   IN     VARCHAR2,
                    x_status               OUT VARCHAR2)
IS
   l_hold_invoice_id   NUMBER := NULL;
   l_hold_err_msg      VARCHAR2 (2000) := NULL;
BEGIN
   l_hold_invoice_id := p_invoice_id;

   IF     l_hold_invoice_id IS NOT NULL
      AND p_reject_code IS NOT NULL
      AND p_hold_val_status = 'Y'
   THEN
      BEGIN
         ap_holds_pkg.insert_single_hold (
            X_invoice_id         => l_hold_invoice_id,
            X_hold_lookup_code   => p_reject_code);

         x_status := 'S';
      EXCEPTION
         WHEN OTHERS
         THEN
            l_hold_err_msg :=
                  'Unknow Exception Occured in Hold API call.. Exception Code : '
               || SQLCODE
               || ' Exception Details : '
               || SQLERRM;
            DBMS_OUTPUT.put_line (l_hold_err_msg);
      END;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END inv_hold;



Thursday, June 4, 2015

Query to Find Attached Template Name from Template Code


SELECT template_code, file_name
  FROM xdo_templates_b a, xdo_lobs b
 WHERE     b.lob_code = a.template_code
       AND b.xdo_file_type = 'RTF'
       AND a.template_code = 'XXXBITEMPLATE';