Purchase Order (PO) approval Hierarchy

PO Approval in Oracle is based on the Approval hierarchy setup for this process. The basic setup can be explained via a flow diagram as below

The basic setups need for the setup are:

1. Postion creation and assignment to a Position Hierarchy
2. PO approval Group Creation and Assignment
3. User Creation , Assigning it to an employee and setting it up as a buyer

The approval workflow consists of the disparate setups which are interlinked together to complete the PO Approval workflow in Oracle

1. Postion creation and assignment to a Position Hierarchy

First Step in setting up the Approval hierarchy to Define a position which basically signifies the position of the employee in the organization. This position will be assigned in a position hierarchy to identify the level of this approval / the complete hierarchy of the employee . This will determine that if an employee submits a PO for approval if it will directly approved or if it will be passed on to someone else for review and approval . The position hierarchy will be assigned to the PO document type which will be mapped to the PO workflow

2. PO approval Group Creation and Assignment :

The first step is PO Approval Group creation ,  we need to create an approval group which has information of the amount of PO that can be approvaed and also the list/range of distribution accounts that can be approved . Once the Approval Group has been created , it needs to be assigned to a position

3. User Creation , Assigning it to an employee and setting it up as a buyer

The PO’s will in the end be created and approved by business users. This will need creation of employees in the system these employees need to be assigned one of the positions created above. This will determine where the employee lies in the hierarchy and if their PO will be directly approved or passed to someone else for approval. The Employee has to be setup as a buyer and needs to be mapped to an end user in Oracle .

Run the Standard Program : Fill Employee hierarchy details to make these changes come into effect

Once this is done create a PO and try for approval. Based on the position hierarchy setup either the PO will be directly approved or passed on to the superior

Sales Order --> Rules --> Processing Constraints

Depending on the business requirements we can be required to check for some conditions and based on their applicability restrict some operations to be performed on the Sales Order.
These restrictions can be applied via the Processing Constraints. There are number of Seeded Oracle Constraints which prevent certain operations in orders to maintain Data Integrity .
E.g. We cannot split an Order line After it is shipped , thus Seeded Oracle Processing Constraint will ensure that this action cannot be performed by the user.


Apart from these we can have custom constraints defined too. Once the constraints have been defined we need to apply it i.e. map it to the responsibilities to specify which responsibilities will be bound by the constraint or can override the constraint.


This can be done by the Applicable to Option :


Oracle provides 3 options which work as below :


All Responsibilities : This means this constraint will be applicable to all responsibilities.
Authorized Responsibility : When any responsibility is mentioned here, then the processing constraint will NOT BE applicable to that responsibility
Constrained Responsibility : When any responsibility is mentioned here, then the processing constraint will BE applicable ONLY to that responsibility


For Seeded constraints the Applicability is All Responsibilities and cannot be updated

Item Attachment Details - SQL and code

We might be faced with a situation when we have attachment text uploaded for an Item and we wish to see that information . In such a scenario we have the option of opening the item and see the attachment from EBS screen , or use the below SQL to fetch the attachment text for 1 or more items

select long_text,att1.media_id,msib.segment1,text1.rowid row_id,mp.organization_code
  from fnd_documents_long_text text1,
  FND_ATTACHED_DOCS_FORM_VL att1,
  mtl_system_items_b msib,
  mtl_parameters mp
  where text1.media_id = att1.media_id
  and att1.entity_name = 'MTL_SYSTEM_ITEMS'
  and att1.PK1_VALUE = msib.organization_id
  and att1.PK2_VALUE = msib.inventory_item_id
  and mp.organization_id = msib.organization_id
  --and att1.creation_date like sysdate ;

Long Text is the attachment description on the Item.

In another scenario the business knew what is the Item Attachment description and wants to know the Set of items where this description exists, Unfortunately the above SQL cannot be used in this case since the Attachment description is a long text column with datatype LONG  which cannot be used in a SQL WHERE clause , However the requirement can be met by using the script in anonymous block as below : Additional condition and checks can be added as need be.

/* The code will search across item description
     and find the item with the given description
     current code will check all the items where item has attachment description
     text as 'test' , and will display the item details
  */
declare

l_chr_desc varchar2(32000);
l_chr_desc_substr varchar2(100);
l_num_media_id number;

cursor c1 is
select long_text,att1.media_id,msib.segment1,text1.rowid row_id,mp.organization_code
  from fnd_documents_long_text text1,
  FND_ATTACHED_DOCS_FORM_VL att1,
  mtl_system_items_b msib,
  mtl_parameters mp
  where text1.media_id = att1.media_id
  and att1.entity_name = 'MTL_SYSTEM_ITEMS'
  and att1.PK1_VALUE = msib.organization_id
  and att1.PK2_VALUE = msib.inventory_item_id
  and mp.organization_id = msib.organization_id
  --and att1.creation_date like sysdate
  ;

begin

  for c1_rec in C1
  LOOP
     select long_text,media_id
       into l_chr_desc,l_num_media_id
       from apps.fnd_documents_long_text
       where rowid = c1_rec.row_id;

     l_chr_desc_substr := substr(l_chr_desc,1,25);

     If l_chr_desc_substr = 'test'
     then
        dbms_output.put_line('l_num_media_id '||l_num_media_id);
        dbms_output.put_line('Item  '||c1_rec.segment1);
        dbms_output.put_line('IWarehouse  '||c1_rec.organization_code);
     end if;

   
  end loop;
 
exception
when others
then
dbms_output.put_line('error  '||SQLERRM);
end;

Any feedback on Improvements is appreciated .