Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Approved Supplier Lists (ASL)

Posted on July 20th, 2008 by Sanjit Anand ||Email This Post Email This Post

Approved Supplier Lists in Oracle is the term used to describe a list of items and commodities that have approved sources from a list of suppliers.

In Oracle , while setting up Approved Supplier Lists you can specify the manufacturer of the product, the distributor of the product or a company that both sells and distributes. You must specify the name of the supplier, you can optionally specify the site that you purchase from. For each Approved Supplier Lists, you can specify whether it applies to the whole enterprise or a single facility.

Take a note, there is always one organization which is responsible for negotiating on behalf of subsidiaries and operating units, this is called the ‘Owning organization’.

Moreover ,you need to specify the unit of measure that will be used when creating purchase orders and the method of creating releases against blanket orders, which could be any of the following:

  • Manual release through Autocreate
  • Automatic release in complete status (i.e. generate and approve PO)
  • Automatic release in incomplete status (i.e. generate PO and approve in separate step).

Read the rest of this entry »

Posted in Oracle Purchasing | 5 Comments »

Purchasing Approvals

Posted on March 24th, 2008 by Sanjit Anand ||Email This Post Email This Post

In PO there are two methods to route documents for approval.

1Approval Hierarchies (uses position hierarchies)

2Employee/Supervisor Relationships (use employee/supervisor relationship)

1Approval Hierarchies

Purchasing utilizes positions as a roadmap to determine how and where documents will be routed once the approval process has been initiated. It is first necessary to have created all positions that are going to be used in the system. Once all positions have been created, it is necessary to build the position hierarchy.

Each position has approval limits, so when a purchase order exceeds the limits of the position, the purchase order is forwarded onto the next position in the Hierarchy

2Employee/Supervisor Relationships

This type of hierarchy does not use the Approval Hierarchy form,but is defined by the employee/supervisor relationship. The supervisor of an employee is defined on the Assignment region of the Employee form

If the purchase order entered by the employee exceeds the approval limits, the purchase order is forwarded onto the employees’ supervisor, as defined on the Employee form

To implement this form of approval routing, you need only to define JOBS. The JOB will then serve as the tie to the Approval group, and based on the approval limits from the Approval Group, the Document will either be Approved or Forwarded to the Employees’ Supervisor.

Similar Post

Posted in Oracle Purchasing | No Comments »

Quick notes :”Procure 2 Pay” Cycle

Posted on February 22nd, 2008 by Sanjit Anand ||Email This Post Email This Post

Similar to O2C in last post , these things you should Know about Procure 2 Pay Cycle.

Oracle Purchasing

  • You enter purchasing documents by operating unit. These include standard and blanket agreement purchase orders, requisitions, RFQ’s and quotes. You assign a PO shipment to an inventory organization, any inventory organization in the same set of books as the PO’s operating unit.
  • You enter receipts by inventory organization.

Account Payable

  • You setup one default liability account by operating unit.
  • You enter invoices in one operating unit at a time.
  • You run payment runs in one operating unit at a time.
  • You can consolidate supplier invoices on one payment only within an operating unit.
  • You setup bank accounts and associated cash accounts within an operating unit.
  • You select invoices for payment on one payment run for one bank account based on pay group, priority, amount,currency, or payment method (i.e. check, electronic) within an operating unit.

PO/AP:

  • You setup suppliers for the entire database instance but addresses for each operating unit.
  • You merge suppliers and addresses within an operating unit.
  • You report supplier/customer netting within an operating unit.

Posted in Oracle Payable, Oracle Purchasing | 2 Comments »

General Procurement Workflows

Posted on February 18th, 2008 by Sanjit Anand ||Email This Post Email This Post

Oracle provides Workflow technology in this operational area as a vehicle to assist companies with leveraging their investment in the applications.

Oracle supports using Workflow Builder to modify the standard workflows that manage purchasing document creation, document approvals, receipts, invoices and reminders. This post is focus on listing for such workflow which helps you to understand and how to leverage procure-to-pay workflows for operational efficiency.

S.N Workflows Name Purpose
1 Requisition Account Generator

Workflow for automatically generating accrual, budget, charge, and variance accounts on purchase orders and releases

  • Defaults charge, budget, accrual, variance
  • Check cross validation, security rules
  • Generate project, task, expenditure types
2 RequisitionApproval

Workflow for approving requisitions

  • Determine completeness
  • Verify Approval Authority
  • Notify Approvers
  • Process Responses
3 Requestor Change Order
  • Manage Requestor changes after PO Creation
  • Date, quantity, price, cancellation
  • Calls PO Change Tolerance Check
  • Process notifications and responses – Approver
  • Process notifications and responses – Buyer
4 PO Change Request Tolerance
  • Date, quantity, price, cancellation
  • Determines re-approval need on requisition
5 PO Change Order for Requestor
  • Process buyer response
  • Notify requestor
  • Initiate change order for PO
6 PO Create Documents

Workflow for automatically creating purchase orders and releases

  • Evaluate eligibility of requisition Lines
  • Group eligible requisition lines
  • Create standard orders or releases
  • Submit PO Approval Workflow
7 PO Account Generator
  • Default charge, budget, variance accounts
  • Check cross validation, security rules
  • Generate project, task, expenditure types
8 PO Approval

Workflow for approving purchase orders

  • Determine completeness
  • Verify Approval Authority
  • Notify Approvers
  • Process Responses
  • Execute Document Transmission
  • Create-Update Sourcing
9 Change Order

Workflow for controlling which changes to purchase orders require a manual reapproval and which are automatically reapproved; the change order workflow is really a group of workflow processes contained in the PO Approval workflow

  • Gather details of change
  • Determine re-approval requirements
  • Notify approver
  • Process response
  • Transmit Document
10 PO Confirm Receipts

Workflow for sending receipt notifications to requesters or buyers notifying them that they should have received their order

  • Notify requestors of overdue receipts
  • Process responses
  • Create receipts from notification response
  • Notify buyers of exceptions
11 Debit Memo Notification
  • Notify Buyer of Automatic Debit Memo Exception
12 PO Approval Error

Workflow for troubleshooting errors that occur when using the PO Approval workflow

  • Capture timeout errors (180 sec)
  • Capture DAM errors (not active)
  • Capture Exceptions
  • Notify Last Approver
  • Notify Sysadmin
  • Retry Workflow
13 PO Send Notifications

Notify requestors of incomplete requisitions

  • Notify Buyers of incomplete documents
  • Notify Buyers of past due acceptance
  • Notify Buyers of quotes expiring
  • Notify Contactor assignment ending
  • Notify amount billed near budget
14 Invoice Approval

Manage Approval Routing of Invoices (intended for non-PO)

  • Setup Rules in AME
  • Setup Routing in AME

Here are some more from other Procurement Modules

S.N … Other Procurement Modules WorkFlow Name
1 iSupplier Portal Supplier Registration
2 iSupplier Portal Supplier Change Order
3 iSupplier Portal Advance Shipment
4 iSupplier Portal PO Acknowledgement
5 Sourcing Sourcing Publish
6 Sourcing Sourcing Negotiation
7 Sourcing Sourcing Approval
8 Sourcing Sourcing Auction
9 Services Procurement Requisition Approval
10 Services Procurement PO Create Documents
11 Services Procurement Contractor Cancellation
12 Procurement Contracts Deliverables Notification
13 Procurement Contracts Contract Clause Approval
14 Procurement Contracts Contract Template Approval
15 Procurement Contracts Contract Rep. Approval

Posted in Oracle Purchasing, Technical | No Comments »

PO: Tips and useful Query

Posted on February 11th, 2008 by Sanjit Anand ||Email This Post Email This Post

The consultant life while working at client site is not easy during ERP transformation projects, many times it’s required to provide some adhoc query for extract to ends users, therefore it is important to have a cheat sheet so that such untimely things can be easily handled in sort span. Hope these query and tips useful to all Inhouse IT personals who is part of Implementation Project team.

1. You need to list out all Internal Requisitions that do not have an associated Internal Sales order.

Internal Requisitions without Sales order

2. You want to display what requisition and PO are linked(Relation with Requisition and PO )

Requisition and PO

3. You need to list out all cancel Requisitions

Cancel Requisition

4. You need to list those PR which havn’t auto created to PO.(Purchase Requisition without a Purchase Order)

PR without PO

5. You need to list all information form PR to PO …as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.

PR to PO

6.Identifying all PO’s which does not have any PR’s

PO without Requisition

7. Relation between Requisition and PO tables

Here is link:

PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID
PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)
PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1

What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.

8.You need to find table which hold PO Approval path…

These two table keeps the data:

  • PO_APPROVAL_LIST_HEADERS
  • PO_APPROVAL_LIST_LINES

9. List all the PO’s with there approval ,invoice and Payment Details

List PO’s with Approval , invoice and Payment info

Read the rest of this entry »

Posted in Oracle Purchasing | 29 Comments »

A single Query covering P2P life Cycle

Posted on January 31st, 2008 by Sanjit Anand ||Email This Post Email This Post

In Finance, transaction management processing is one of labor intensive task in ERP, as it requires extensive data entry , chance are very very high for duplication/re-entry. As we know Procure to Pay life cycle start itself from contract management till making payment.

As we know the efficient Procure to pay process have these sub processes;

  • Contract Management
  • Purchase Requisitions
  • Purchase Orders
  • Accounts Payable – Managing invoice
  • Supplier Payment

p2p

In real business world, many time when system is running external/internal auditor are more interested in scrutiny of:

  • Goods received / invoices received
  • Inaccurate or duplicate vendor & material master records
  • Discrepancies in payment terms
  • Delays / long processing times
  • Detect duplicate vendor
  • Unusually large or small payments
  • Unauthorized changes made to invoices
  • Detect Duplicate invoice
  • Detect Duplicate payment
  • Approval status

Therefore, it is Inhouse ISD/Finance IT or implementing company responsibility is to provide such kind of adhoc reporting for auditor so that they can satisfy the audit requirement.

A ‘P2P’ query that made Auditors happy

It was brought by ISD team , as part year end audit for a ERP system which went live 3 month back. It was a one of requirement to display data for a particular PO which covers data from there all 5 five phases, means a particular PO line consist of:

  1. Requisition Detail
  2. Purchase Order Details
  3. Receiving Details
  4. Invoicing Detail
  5. Payment Details

Therefore thought to share this query, hope this would be great help who have such kind of adhoc requirement from daily life.

Here is query:

SELECT
A.ORG_ID “ORG ID”,
E.VENDOR_NAME “VENDOR NAME”,
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) “VENDOR TYPE”,
F.VENDOR_SITE_CODE “VENDOR SITE”,
F.ADDRESS_LINE1 “ADDRESS”,
F.CITY “CITY”,
F.COUNTRY “COUNTRY”,
TO_CHAR(TRUNC(D.CREATION_DATE)) “PO DATE”,
D.SEGMENT1 “PO NUMBER”,
D.TYPE_LOOKUP_CODE “PO TYPE”,
C.QUANTITY_ORDERED “QTY ORDERED”,
C.QUANTITY_CANCELLED “QTY CANCALLED”,
G.ITEM_DESCRIPTION “ITEM DESCRIPTION”,
G.UNIT_PRICE “UNIT PRICE”,
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) “PO Line Amount”,
(SELECT
DECODE(PH.APPROVED_FLAG, ‘Y’, ‘Approved’)
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) “PO STATUS”,
A.INVOICE_TYPE_LOOKUP_CODE “INVOICE TYPE”,
A.INVOICE_AMOUNT “INVOICE AMOUNT”,
TO_CHAR(TRUNC(A.INVOICE_DATE)) “INVOICE DATE”,
A.INVOICE_NUM “INVOICE NUMBER”,
(SELECT
DECODE(X.MATCH_STATUS_FLAG, ‘A’, ‘Approved’)
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)”Invoice Approved?”,
A.AMOUNT_PAID,
H.AMOUNT,
I.CHECK_NUMBER “CHEQUE NUMBER”,
TO_CHAR(TRUNC(I.CHECK_DATE)) “PAYMENT DATE”
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = ‘Y’
AND D.TYPE_LOOKUP_CODE != ‘BLANKET’;

The important section which cover in the query output is as:

1. Information for Supplier

1

2.Purchase Order details

2

3. Receiving Items Details

3

4.Invoice Details

4

5.Payment Details

5

You download the query and details here.download btn

Posted in Oracle Payable, Oracle Purchasing | 12 Comments »

10 Min Guide for “Internal Requisition To Internal Sales Order Flow”

Posted on January 22nd, 2008 by Sanjit Anand ||Email This Post Email This Post

You already aware , Internal requisition is a requisition in the Purchasing system that will directly result in the generation of a sales order in the Order Management.

Internal Requisition/Internal Sales Order provide the mechanism for requesting and transferring material from one inventory organization to other inventory
organization or expense location.

When Purchasing, Order Management, Shipping Execution, and Inventory are installed, they combine to give you a flexible solution for your inter-organization
and intra-organization requests.

dgreybarrow Where : First you need to understand , where demand comes for Internal requisitions in company.

Demand for internal requisitions can come from the following sources

  • Online user’s request for stock items out of inventory
  • Inventory Replenishment Requests
  • Inventory generates replenishment requests automatically using the following methods:
    1. Min-Max Planning
    2. Reorder Point Planning
    3. Subinventory Replenishments for Replenishment Counts
    4. Kanban Replenishments

Oracle Master Scheduling/MRP creates requisitions for buy items when you release them using the Planner Workbench.

For External system’s requests, you can automatically import internal requisitions from other Oracle Applications or existing non-Oracle systems using the Requisitions Open Interface.

dgreybarrow How : here are steps which helps you to understand the flow from IR to ISO.

The key setup Steps Includes for IRO should be:

Item to be used on Internal requisition should have the following flags enabled.

  • Purchasing -> Purchasable
  • Order Management -> Internal Orders Enabled
  • Order Management -> Internal Ordered
  • Order Management -> OE Transactable

More important is , Item needs to be assigned to both source and destination inventory organizations.

Then you need to create shipping network between source and destination Inventory organizations.

Follow this navigation

Inventory responsibility -> Setup -> Organizations -> Shipping Networks

Once your shipping network should be created from source to destination organization.

You can setup transfer type can be either Direct or Intransit and internal Order required checkbox should be checked.

When you are using Internal Requisition/Internal Sales Orders, it is required to create an internal customer for each destination organization and a customer ship-to site for each deliver-to location within the destination organization.

Make sure you have defined the same address for your customer ship-to-site as your deliver-to location.

The Customer must be created in the Operating Unit of the Source Inventory Organization that is used on the Internal Requisition.

Make sure Profile option PO: Legal Requisition Type should be setup correctly which should be either BOTH or INTERNAL

  • PO: Legal Requisition Type = BOTH – That means items can be purchased from a vendor or from on hand inventory
  • PO: Legal Requisition Type = INTERNAL – that means items can only be obtained from on hand inventory

Thats all.

Next, refer to previous Post “Understanding data flow for “Internal Orders” helps you to understand the data flow table to table level.

Posted in Oracle Purchasing | No Comments »

What is In “Purchase Order import”

Posted on December 24th, 2007 by Sanjit Anand ||Email This Post Email This Post

You can use Purchase Document Open Interface which allows you to quickly import a large volume of Standard Purchase Orders into Oracle Purchasing.

The Import process involves populating the PO interface tables with the document information to be imported and then running the Import
Standard Purchase Orders concurrent program which will validate the data and create the PO in the application and return an error message if something fail.

dgreybarrow First Timer, know these first

Before you start, you need to understand the database objects which play critical role.

Table Name Description Type
PO_HEADERS_INTERFACE This is the table where to insert PO headers data in interface table. Interface table
PO_LINES_INTERFACE This is where we insert PO lines information to be imported ( it is used also for Shipments details ) Interface table
PO_DISTRIBUTIONS_INTERFACE This is where we insert PO distribution details before import Interface table
PO_INTERFACE_ERRORS Stores all errors resulted from import process. Errors table
PO_HEADERS_ALL Stores document headers for purchase orders, purchase agreements,quotations, and RFQs PO Base table
PO_LINES_ALL Stores purchase document lines for purchase orders, purchase agreements, quotations, and RFQs PO Base table
PO_LINE_LOCATIONS_ALL Stores document shipment schedules for purchase orders, purchase agreements, quotations, and RFQs PO Base table
PO_DISTRIBUTIONS_ALL Stores purchase order distributions PO Base table

dgreybarrow Steps by Steps

Know what is getting ining Data into Purchase Order Interface Tables

  1. Load PO header, lines, shipments and distributions data from your source system into the following interface tables
    • PO_HEADERS_INTERFACE
    • PO_LINES_INTERFACE
    • PO_DISTRIBUTIONS_INTERFACE
  2. Once the data has been inserted into the interface tables, a queries like the following can be used to review the information before running the import program :
    • Select * from PO_HEADERS_INTERFACE where INTERFACE_HEADER_ID=<headerid>
    • Select * from PO_LINES_INTERFACE where INTERFACE_HEADER_ID=&headerid
    • Select * from PO_DISTRIBUTIONS_INTERFACE where INTERFACE_HEADER_ID=&headerid

Review data before calling Import Standard Purchase Orders program.

when you submit the import program, third parameter is approval status , which altogether have different logic, which you need to understand the impact.

Purchase Order import

dgreybarrow Understanding approval status in parameter

Significant impact is there on Approval Status parameter and have import logic which is as below:

 

Status in
Interface Table
Imporft Program Approval Status Parameter Resulting Document Status
NULL Incomplete Incomplete
NULL Approved Approved
NULL Initiate Approval Initiate Approval
Incomplete Incomplete Incomplete
Incomplete Approved Incomplete
Incomplete Initiate Approval Initiate Approval
Approved Incomplete Approved
Approved Approved Approved
Approved Initiate Approval Approved

dgreybarrow Take Away

If the records got imported successfully without issues, the records will stay in the interface tables.

  • You can notice , successful records get PROCESS_CODE as “ACCEPTED”
  • It is good practice and important to check the Purchasing Interface Errors report always.
  • This Error report you can submit after your import completed.
  • Because the Purchasing Documents Open Interface saves or errors out line by line, it can accept partial documents. So that you may find a document has been accepted although some lines from it has been rejected . Therefore, to see which document lines were not submitted because of errors, you must check the Purchasing Interface Errors report.

dgreybarrow What happen with IMPORT

This seems sound intresting to you.

  • The Purchasing Documents Open Interface (PDOI) programs first process a record from the PO_HEADERS_INTERFACE table.
  • Then, the program processes the child records in the PO_LINES_INTERFACE table then process the PO_DISTRIBUTIONS_INTERFACE table, before going on to the next PO represented by a record in PO_HEADERS_INTERFACE. Make sense.
  • In between , If the program gets an error while processing a record, the program writes the error details to the PO_INTERFACE_ERRORS table and increments the record’s error counter.
  • Therefore, the Purchasing Documents Open Interface saves or errors out on a line-byline basis.
  • This means that if an error is found in a document line, only that line is rolled back (not submitted to Purchasing), and we will be able to find the error in the PO_INTERFACE_ERRORS table.
  • You should be aware , because the Purchasing Documents Open Interface can accept partial documents as it saves or errors out line by line.
  • If an error is found in a header, none of its lines are processed.
  • The Purchasing Documents Open Interface rolls back the header, does not process its lines, and does the following:
    • Sets the PROCESS_CODE column value to REJECTED in the PO_HEADERS_INTERFACE table.
    • Writes out the record identification number and the details of the error to the PO_INTERFACE_ERRORS table.
    • Begins processing the next header record.
  • If no processing errors are found during processing, the header record and all successfully submitted child records are loaded into Purchasing, and then flagged as processed by setting the PROCESS_CODE column to ACCEPTED.

As mention earlier, To check for records in error, the Purchasing Interface Errors Report can be run to provide information as to the cause of the error.

dgreybarrow Other tools

This is most acceptable interface and widly used every where, therefore Oracle have Diagnostics tool for this keeping developer in mind.

Oracle Diagnostics tool name is Oracle Purchasing Documents Open Interface Data Collection Test.

This diagnostic test will verify the data in the interface tables used by the purchasing documents open interface (PDOI) so that it can be used proactively or reactively to resolve or prevent issues in the purchasing documents open interface (PDOI).

Hope you find this is very useful and productive tool.should you need any input send me offline.

Posted in Oracle Purchasing | No Comments »

What is In Requisition import

Posted on December 23rd, 2007 by Sanjit Anand ||Email This Post Email This Post

This interface lets you integrate Oracle Purchasing quickly with new or existing applications such as material requirements planning, inventory management, and production control systems and also helps to enter requisitions from external sources.

dgreybarrow First Timer, know these first

Before you start, you need to understand the database objects which play critical role.

Table Name Description Type
PO_REQUISITIONS_INTERFACE_ALL This is the main Requisition Import interface table Interface table
PO_REQ_DIST_INTERFACE_ALL

The PO_REQ_DIST_INTERFACE_ALL table was used in Release 11, for Self-
Service Purchasing

Interface table
PO_INTERFACE_ERRORS This table stores all errors resulted from import process. Errors table
PO_REQUISITION_HEADERS_ALL Base table that stores requisition headers REQ Base table
PO_REQUISITION_LINES_ALL Base table that stores requisition lines REQ Base table
PO_REQ_DISTRIBUTIONS_ALL This table stores requisition distributions REQ Base table

dgreybarrow Steps by Steps

These are the basic steps for the import process :

  • Insert data into interface table
  • Run import program to insert data into base tables
  • Review imported data from the front end.

First steps start with Inserting data into the Requisitions Interface Tables

You can inserts a single row into the PO_REQUISITIONS_INTERFACE_ALL and/or the PO_REQ_DIST_INTERFACE_ALL table for each requisition line that you want to import.

You identify the set of rows you want to import with each other by setting the INTERFACE_SOURCE_CODE and BATCH_ID columns appropriately in the PO_REQUISITIONS_INTERFACE_ALL table. You then pass these values as parameters to the Requisition Import program. If you do not specify any values for these parameters, the program imports all the requisition lines in the PO_REQUISITIONS_INTERFACE_ALL table.

Typically while inserting you might notice as per documentation, there are three types of columns exist in the in interface tables, as

  • Required Data
    • Required : That mean, You must provide values for all columns that are required.No choice.
    • Conditionally required :You may also have to provide values for columns that are conditionally required. Providing a CURRENCY_CODE, the RATE, RATE_DATE, and RATE_TYPE accordingly to Rate fields are conditionally required.
  • Derived Data : System will default those columns using logic similar to that used by the Requisitions form.
  • Optional Data : These are optional to be filled and will not stop the import process.

Review data before calling Import program.

when you submit the import program, third parameter is approval status , which altogether have different logic, which you need to understand the impact.

dgreybarrow How the Initiate Approval After Requisition Import parameter works and is the relationship between it and AUTHORIZATION_STATUS field?

Yes, this is important to understand the significance and importance of this field .

Req Import

Look at the table below , helps you to understand the value of parameter.

Value in table Parameter
value
Result
Approved YES Req is approved, no call to workflow
Incomplete YES Req is created, workflow is called. Result could be Incomplete, Approved, or In-Process
Approved NO Has no effect, record in the Interface states
Approved, no call to req approval workflow. Req is created as Approved.
Incomplete NO Req is created, and is Incomplete. No call to the workflow for req approval

dgreybarrow Take Away

If the records got imported successfully without issues, the records will stay in the interface tables.

  • You can notice ,successful records get PROCESS_CODE as “ACCEPTED”
  • It is good practice and important to check the Purchasing Interface Errors report always.
  • This Error report you can submit after your import completed.
  • Because the Purchasing Documents Open Interface saves or errors out line by line, it can accept partial documents. So that you may find a document has been accepted although some lines from it has been rejected . Therefore, to see which document lines were not submitted because of errors, you must check the Purchasing Interface Errors report.

dgreybarrow What happen when IMPORT run

This seems sound intresting to you.

when you run Requisition Import concurrent request , program enforced to complete in three steps phases.

The Requisition Import program operates in three phases.

Phase 1 :Validation

The first phase, where the program validates your data and derives or defaults additional information.

  1. The program generates an error message for every validation that fails and accordingly, it will create a row in the PO_INTERFACE_ERRORS table with detailed information about each error.
  2. Program will then check for the column MULTI_DISTRIBUTIONS in the PO_REQUISITIONS_INTERFACE_ALL table, if it is set to yes ( Y ) , Requisition Import will then check for corresponding distributions in the PO_REQ_DIST_INTERFACE_ALL table and if it did not find any corresponding distribution information, it will loads these as errors in the PO_INTERFACE_ERRORS table.

Phase 2 :Grouping

In the second phase, the program groups and numbers the validated requisition lines according to the following criteria:

  1. If you specify a value in the REQ_NUMBER_SEGMENT1 column of the PO_REQUISITIONS_INTERFACE_ALL table, all lines with the same value for this column are grouped together under a requisition header.
  2. If you provide a value in the GROUP_CODE column, all lines with the same value in this column are grouped together under a requisition header.
  3. If you do not provide values in either of these columns, the Requisition Import program uses the Group By parameter to group lines together.
  4. If you do not provide a value for this parameter, the program uses the default Group By that you set up to group requisition lines.

Phase 3 :Deletion

In the third phase, the program deletes all the successfully processed rows in the interface tables, and creates an output which lists the following :

  1. Number of interface records that were successfully imported.
  2. Number of interface records that were not imported.

This output can be viewed by choosing View Output for the Requisition Import concurrent Request ID in the Requests window.

dgreybarrow What Can , what Cann’t

You can import approved or unapproved requisitions using the Requisitions Open Interface.

If you are using requisition encumbrance, approved requisitions that you import automatically become pre-approved.

In case there are some records not imported appears in the output from the report, then you can launch the Requisition Import Exceptions Report to view the rows that were not imported by the Requisition Import program along with the failure reason(s) for each row.

dgreybarrow A very common Observation while Requisition import

When you trying to submit a request to run the Requisition Import and when you try to select from the list of values in the parameter field for Import Source, you do not find any value and you receive the error message: “FRM-41830 List of Values contains No Entries “

Therefore you need to understand the List of Values for the Import Source parameter is dynamically determined by the INTERFACE_SOURCE_CODE column value in the PO_REQUISITIONS_INTERFACE_ALL table, So that if there are no values for the LOV, then there are no records in the table for the current organization.

In that case you just have to ensure that there are records in the interface and that the field INTERFACE_SOURCE_CODE is already populated.

Hope this post will be helpful.

Posted in Oracle Purchasing | No Comments »

Understanding Retroactive Pricing in EBS

Posted on December 12th, 2007 by Sanjit Anand ||Email This Post Email This Post

Retroactive pricing allows buyers to adjust the price on blanket agreement lines and have the new pricing be updated on all release lines that have not been received against. Over the life of purchasing documents price changes, this functionality allows buyers to make sure that the
price on the blanket agreement is the agreed upon price.

The Retroactive Price Update on Purchasing Documents ‘ concurrent program automatically updates existing blanket releases and standard purchase orders retroactively with price changes from the parent blanket agreement or global purchase agreement. Other changes can
include updates to price breaks quantities, ship–to organization or location, new price breaks, and deletion of existing price breaks.

Take a note:

  1. The Retroactive Price Update on Purchasing Documents concurrent program does not update the prices if purchase orders or releases have any accounting entries (receipt accruals, invoices, encumbrance) associated with them.
  2. It does not process documents associated with blanket Agreement lines that are cumulatively priced.
  3. It only considers price updates from lines of blanket agreements that are currently in thefollowing statuses:
    • Approved
    • Closed
  4. It updates the open Blanket Releases and Standard Purchase Orders against Global Agreements

‘The Retroactive Price Update on Purchasing Documents’ program uses Buyer entered parameter values to retrieve blanket agreement lines that have undergone price changes as well as the release shipments created against these lines. It then updates shipments with any new prices from the blanket agreement and update the timesta mp information on release shipments with that of the blanket agreement line.

Purchasing documents that were in an approved status prior to the update can be automatically submitted for re–approval.

Profile option PO: Allow Retroactive Pricing of POs is need to be set, generally it has to be set to ‘Open Release Only’ to update only all open releases on that Blanket and that the PO Change Order Workflow has been configured to allow automatic re–approval of the updated releases and purchase orders.

Posted in Oracle Purchasing | No Comments »

« Previous Entries Next Entries »