Creation of Purchase Requisitions using PO_REQUISITIONS_INTERFACE_ALL Interface table

Brief

The Purchase Requisitions can be created using the following two methods:

1. Using Seeded Function ( Requisitions Form ) available in Oracle eBusiness Suite
2. Oracle Requisition Interface

The aim of this document is demonstrate how to create a requisition using interface table (PO_REQUISITIONS_INTERFACE_ALL) and then import as an Approved or Unapproved Requisition.

Important fields of PO_REQUISITIONS_INTERFACE_ALL and their meanings:

INTERFACE_SOURCE_CODE	Provide a meaningful name that will identify Requisition Source
BATCH_ID	Batch Id; a number that can used to group together the requisition lines in one Requisition
ORG_ID	Operating unit unique identifier; References HR_ALL_ORGANIZATION_UNITS table
DESTINATION_TYPE_CODE	Requisition Destination Type: INVENTORY or EXPENSE. References PO_LOOKUP_CODES where lookup_type='DESTINATION TYPE'
AUTHORIZATION_STATUS	Authorization status type: APPROVED ; INCOMPLETE
PREPARER_ID	Requisition preparer's person_id; references PER_ALL_PEOPLE_F
CHARGE_ACCOUNT_ID	General Ledger charge account; references to GL_CODE_COMBINATIONS
SOURCE_TYPE_CODE	Requisition Source Type Code; INVENTORY or VENDOR. References PO_LOOKUP_CODES where lookup_type='REQUISITION SOURCE TYPE'
UNIT_OF_MEASURE	Unit of measurement ; References MTL_UNITS_OF_MEASURE_TL
LINE_TYPE_ID	Requisition line type is a unique identifier; from 1..n
CATEGORY_ID	Requisition Item Category; References MTL_CATEGORIES_B
UNIT_PRICE	Requisition Unit Price
QUANTITY	Requisition Quantity
DESTINATION_ORGANIZATION_ID	Destination organization unique identifier; References HR_ALL_ORGANIZATION_UNITS table
DELIVER_TO_LOCATION_ID	Deliver To Location ID; Reference HR_LOCATIONS_ALL.
DELIVER_TO_REQUESTOR_ID	Deliver to Requester; References PER_ALL_PEOPLE_F(PERSON_ID)
ITEM_DESCRIPTION	Item Description; Any meaningful Item Description

Case:
A Company wants to send following Expense Purchase Request to Oracle Purchasing using a third party application (My Application):
Item Description: HP Printer Cartridge - CE400X
Item Quantity:  5
Requested Organization: Information Technology Division
Requested By:  Aadil Qadri

Solution:

Step 1: Interface Requisition Line

INSERT INTO po_requisitions_interface_all
(
interface_source_code,
batch_id,
org_id,
destination_type_code,
authorization_status,
preparer_id,
charge_account_id,
source_type_code,
unit_of_measure,
line_type_id,
category_id,
unit_price,
quantity,
destination_organization_id,
deliver_to_location_id,
deliver_to_requestor_id,
item_description
)
VALUES
('MyAPP',                 --Interface Source
1001,                         --Batch Id ; a number to group the requisition lines in a Requisition
161,                           --Operating Unit
'EXPENSE',              --Destination Type
'APPROVED',          --Status
573,                           --This comes from per_people_f.person_id of preparer
8717,                         --Code Combination ID of charge account
'VENDOR',               --Source Type
'Each',                        --UOM
1,                                --Line Type of Goods
2060,                          -- Category ID
100.5,                         --Price
7,                                --Quantity
181,                            --Destination Org ID.
121,                            --Deliver to Location ID
573,                            --Requester ID
'Cartridge CE400X'   --Item Description
);

Step 2: Run "Requisition Import" with the parameters as follows:

Import Source:      MyAPP
Import Batch ID:  1001
Group By:             All
Multiple Distributions:  No [In our case we do not have multiple distributions ]
Initiate Approval After ReqImport: No [Because we have set Authorization_status= APPROVED in the requisition interfaced line]

Setting Up the Oracle E-Business Suite Security in Oracle BI Publisher 10.1.3.4.2

Brief:
Oracle BI Publisher can use Oracle eBusiness Suite security by which users can login to Oracle BI Publisher using EBS Username and Password. Once we setup the security, the EBS responsibilities will be available in the BI Publisher as roles. And then you can add Oracle BI folders and Data Sources to above mentioned auto imported Roles.

Setup:

   Part A: Setup Oracle eBusiness Suite

1. Login to Oracle eBusiness Suite as SYSADMIN
2. Create a dummy menu TEST BI PUBLISHER  (with no functions)
3. Create following six responsibilities as shown in the screenshot :
1. XMLP_ADMIN – this is the administrator role for the BI Publisher server
2. XMLP_DEVELOPER – allows users to build reports in the system.
3. XMLP_SCHEDULER – allows users to schedule reports.
4. XMLP_ANALYZER_EXCEL – allows users to use the Excel analysis feature.
5. XMLP_ANALYZER_ONLINE – allows users to use the online analysis feature.
6. XMLP_TEMPLATE_DESIGNER - allows users to connect to the BI Publisher server from the Template Builder and to upload and download templates.
   
   
   



4. Add the above responsibilities (based on the need) to the Oracle eBusiness Suite user
5. Get dbc file from EBS Application server, which will be used during setting up the Oracle BI Publisher. And you can find the dbc at $FND_SECURE location.

    Part B: Setup Oracle BI Publisher Enterprise

1. Install Oracle BI Publisher Enterprise (OBIPE) [ For Installation follow the Oracle Document ]
2. Get Oracle eBusiness Suite dbc file. The file is located in Oracle 
3. Login to OBIPE as Administrator
4. Enable Local Superuser as follows:
1. Click Admin Tab
2. Click on Security Configuration
3. Select Enable Local Superuser
4. Enter Superuser Name [Any meaningful name]
5. set Password
6. Scroll down to Security Model
7. Set Security Model to Oracle E-Business Suite
8. Click on Upload DBC File button to upload the dbc file
9. Apply to Save the changes
   



5. Stop and Start Oracle BI Publisher Enterprise to take the effect.
6. Login to the BI Publisher as a Local Superuser
7. Click on Admin Tab then Roles and Permissions
8. Add folder and Data Source to the Role
   
9. Logout from OBIE as Admin
10. Login again with E-Business suite credentials