Technical components of Oracle Purchasing

Technical components of Oracle Purchasing

Oracle Purchasing allows requisitions, purchase orders, quotations, and receipts etc to be processed and integrated with modules such as General Ledger, Inventory, Order Management etc. The Oracle Purchasing design consists of various technical components like interfaces, workflows, profile options, tables etc which are summarized in this article.

Main Business Components in Oracle Purchasing are
Employee/Buyers

Vendor/Suppliers

Requisitions

Purchase Orders

Receipts

Employees

You must to be setup as an employee in order to create a requisition or a PO. If Oracle HR is installed then you have to use the form defined in Oracle HRMS to define an employee. If Oracle HR is not installed then you can use a form under Setup->Personnel->Employees to setup employees.

Main tables are HR_EMPLOYEES, PER_PEOPLE_F

Important Note: The view HR_EMPLOYEES_CURRENT_V gives one record per active employee. PER_PEOPLE_F/PER_ALL_PEOPLE_F store multiple records per employee with specific start and end dates
Vendors

PO_VENDORS, PO_VENDOR_SITES_ALL and PO_VENDOR_CONTACTS are the main tables for this entity. Vendors are global i.e. a vendor, once defined, can be used across operating units (OU). Vendor sites are OU specific. Most of the PO tables store the VENDOR_ID and VENDOR_SITE_ID columns. VENDOR_SITE_ID is unique (not unique within a VENDOR_ID) in 11i. It used to be unique for a vendor until 11.0.PO_VENDORS

PO_VENDORS stores information about your suppliers. You need one row for each supplier you define. Each row includes the supplier name as well as purchasing, receiving, payment, accounting, tax, classification, and general information.

Oracle Purchasing uses this information to determine active suppliers. VENDOR_ID is the unique system–generated receipt header number invisible to the user.

SEGMENT1 is the system–generated or manually assigned number you use to identify the supplier in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let Oracle Purchasing generate supplier numbers for you.

This table is one of three tables that store supplier information. PO_VENDORS corresponds to the Suppliers window.

PO_VENDOR_SITES_ALL

PO_VENDOR_SITES_ALL stores information about your supplier sites. You need a row for each supplier site you define. Each row includes the site address, supplier reference, purchasing, payment, bank, and general information. Oracle Purchasing uses this information to store supplier address information.

This table is one of three tables that store supplier information. PO_VENDOR_SITES_ALL corresponds to the Sites region of the Suppliers window.

PO_VENDOR_CONTACTS

PO_VENDOR_CONTACTS stores information about contacts for a supplier site. You need one row for each supplier contact you define.

Each row includes the contact name and site.

This table is one of three tables that store supplier information. PO_VENDOR_CONTACTS corresponds to the Contacts region of the Supplier Sites window

Requisition

This entity is the starting point of data flow in the PO module. Requisitions can be created by various means – Enter Reqs form, Requisition Interface tables or using Self Service Purchasing.

All requisitions need to be approved before being considered for future processing. An unapproved requisition has a value of ‘Incomplete’ for the column AUTHORIZATION_STATUS in the table PO_REQUISITION_HEADERS. After the requisition is completed it should be submitted for Approval. Approval is a separate piece of code that is reused in both Reqs as well as PO approval. It is a combination of Workflow, PL/SQL and Pro*C code.

There are 3 main tables for Reqs:

PO_REQUISITION_HEADERS:

PO_REQUISITION_HEADERS_ALL stores information about requisition headers. You need one row for each requisition header you create. Each row contains the requisition number, preparer, status, and description.

REQUISITION_HEADER_ID is the unique system–generated requisition number. REQUISITION_HEADER_ID is invisible to the user.

SEGMENT1 is the number you use to identify the requisition in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let Oracle Purchasing generate requisition numbers for you.

PO_REQUISITION_HEADERS_ALL corresponds to the Header region of the Requisitions window.

SEGMENT1 provides unique values for each row in the table in addition to REQUISITION_HEADER_ID.

PO_REQUISITION_LINES:

PO_REQUISITION_LINES stores information about requisition lines. You need one row for each requisition line you create.

Each row contains the line number, item number, item category, item description, need–by date, deliver–to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line.

LINE_LOCATION_ID identifies the purchase order shipment line on which you placed the requisition. LINE_LOCATION_ID is null if you have not placed the requisition line on a purchase order.

BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement or catalog quotation line information for the requisition line.

PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you exploded or multi-sourced this requisition line.

This table corresponds to the Lines region of the Requisitions window.

PO_REQ_DISTRIBUTIONS:

PO_REQ_DISTRIBUTIONS_ALL stores information about the accounting distributions associated with each requisition line. Each requisition line must have at least one accounting distribution. You need one row for each requisition distribution you create.

Each row includes the Accounting Flexfield ID and requisition line quantity.

PO_REQ_DISTRIBUTIONS_ALL is one of three tables storing your requisition information. This table corresponds to the requisition Distributions window, accessible through the Requisitions window

Purchase Order

This is the pivotal entity of Oracle Purchasing. All other entities function for or because of this entity. There are four main tables for this entity:

PO_HEADERS_ALL:

There are six types of documents that use PO_HEADERS_ALL:

• RFQs

• Quotations

• Standard purchase orders

• Planned purchase orders

• Blanket purchase orders

• Contracts

Each row contains buyer information, supplier information, brief notes, foreign currency information, terms and conditions information, and the status of the document. Oracle Purchasing uses this information to record information that is related to a complete document. PO_HEADER_ID is the unique system–generated primary key and is invisible to the user. SEGMENT1 is the system–assigned number you use to identify the document in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONT_ALL table if you choose to let Oracle Purchasing generate document numbers for you. SEGMENT1 is not unique for the entire table. Different document types can share the same numbers. You can uniquely identify a row in PO_HEADERS_ALL using SEGMENT1 and TYPE_LOOKUP_CODE or using PO_HEADER_ID.

If APPROVED_FLAG is ’Y’, the purchase order is approved. If your document type is a blanket purchase order, contract purchase order, RFQ, or quotation, Oracle Purchasing uses START_DATE and END_DATE to store the valid date range for the document. Oracle Purchasing only uses BLANKET_TOTAL_AMOUNT for blanket

PO_LINES_ALL:

Is a detail of headers table.

Each row includes the line number, the item number and category, unit, price, tax information, matching information, and quantity ordered for the line. Oracle Purchasing uses this information to record and update item and price information for purchase orders, quotations, and RFQs. PO_LINE_ID is the unique system–generated line number invisible to the user. LINE_NUM is the number of the line on the purchase order.

Oracle Purchasing uses CONTRACT_NUM to reference a contract purchase order from a standard purchase order line. Oracle Purchasing uses ALLOW_PRICE_OVERRIDE_FLAG, COMMITTED_AMOUNT, QUANTITY_COMMITTED, MIN_RELEASE_AMOUNT only for blanket and planned purchase order lines.

The QUANTITY field stores the total quantity of all purchase order shipment lines (found in PO_LINE_LOCATIONS_ALL).

PO_LINE_LOCATIONS_ALL:

Also known as Shipments is a detail of lines. PO_LINE_LOCATIONS_ALL contains information about purchase order shipment schedules and blanket agreement price breaks. You need one row for each schedule or price break you attach to a document line.

Each row includes the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses this information to record delivery schedule information for purchase orders, and price break information for blanket purchase orders, quotations and RFQs.

PO_RELEASE_ID applies only to blanket purchase order release shipments. PO_RELEASE_ID identifies the release on which you placed this shipment.

SOURCE_SHIPMENT_ID applies only to planned purchase order release shipments. It identifies the planned purchase order shipment you chose to release from.

PRICE_OVERRIDE always equals the purchase order line price for standard purchase order shipments. For blanket and planned purchase orders, PRICE_OVERRIDE depends on the values of the ALLOW_PRICE_OVERRIDE_FLAG and NOT_TO_EXCEED_PRICE in the corresponding row in PO_LINES_ALL:

If ALLOW_PRICE_OVERRIDE_FLAG is ’N’, then PRICE_OVERRIDE equals UNIT_PRICE in PO_LINES_ALL.

If ALLOW_PRICE_OVERRIDE_FLAG is ’Y’, then PRICE_OVERRIDE can take any value that is smaller than NOT_TO_EXCEED_PRICE in PO_LINES_ALL.

The QUANTITY field corresponds to the total quantity ordered on all purchase order distribution lines (found in PO_DISTRIBUTIONS_ALL).

PO_DISTRIBUTIONS_ALL:

PO_DISTRIBUTIONS_ALL contains accounting distribution information for a purchase order shipment line. You need one row for each distribution line you attach to a purchase order shipment.

Each row includes the destination type, requestor ID, quantity ordered and deliver–to location for the distribution. Oracle Purchasing uses this information to record accounting and requisition information for purchase orders and releases.

PO_DISTRIBUTIONS_ALL is one of five tables storing purchase order and release information.

Some columns in PO_DISTRIBUTIONS_ALL contain information only if certain conditions exist:

If you autocreate this accounting distribution from a requisition, REQ_DISTRIBUTION_ID corresponds to the ID of the requisition distribution you copy on the purchase order.

If you use a foreign currency on your purchase order, Oracle Purchasing stores currency conversion information in RATE and RATE_DATE.

If you use encumbrance, GL_ENCUMBERED_DATE and

GL_ENCUMBERED_PERIOD_NAME contain encumbrance information Oracle Purchasing uses to create journal entries in Oracle General Ledger.

If you do not autocreate the purchase order from online requisitions, REQ_LINE_REFERENCE_NUM and REQ_HEADER_REFERENCE_NUM contain the requisition number and requisition line number of the corresponding paper requisition. These two columns are not foreign keys to another table.

If the distribution corresponds to a blanket purchase order release, PO_RELEASE_ID identifies this release.

If SOURCE_DISTRIBUTION_ID has a value, the distribution is part of a planned purchase order release.

Reqs can be converted to Purchase Orders using either the Autocreate form or Create PO workflow. If certain conditions are satisfied then multiple req lines are converted to a single PO line or a single PO shipment.

Receipt

There are two receipt source types, Supplier (PO based) and Internal Order (Internal Requisitions and Inter-org transfers) that you need to use when receiving against different source document types. You use a receipt source type of ’Supplier’ when receiving items that you ordered from an external supplier using a purchase order.

When you receive items that are part of an inter–organization transfer, or when receiving items that you request from your inventory using an internal requisition, the receipt type would be ’Internal Order’. The ’Internal Order’ receipt source type populates the ORGANIZATION_ID column.

There are three main tables in receiving:

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_HEADERS stores common information about the source of your receipts or expected receipts. You group your receipts by the source type and the source of the receipt. Oracle Purchasing does not allow you to group receipts from different sources under one receipt header.

Oracle Purchasing creates a receipt header when you are entering your receipts or when you perform inter–organization transfers using Oracle Inventory. When Oracle Inventory creates a receipt header for an intransit shipment, the receipt number is not populated until you receive the shipment.

RCV_SHIPMENT_LINES

RCV_SHIPMENT_LINES stores information about items that have been shipped and/or received from a specific receipt source. RCV_SHIPMENT_LINES also stores information about the default destination for intransit shipments.

RCV_TRANSACTIONS

RCV_TRANSACTIONS stores historical information about receiving transactions that you have performed. When you enter a receiving transaction and the receiving transaction processor processes your transaction, the transaction is recorded in this table.

Once a row has been inserted into this table, it will never be updated.

When you correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY. The original transaction quantity does not get updated. You can only delete rows from this table using the Purge feature of Oracle Purchasing.

Main Interfaces

You could import requisitions, Purchase Orders and Receipts using the open interfaces for the respective entities. The Manufacturing APIs and Open Interfaces manual is a comprehensive guide to these interfaces.

Requisitions Interface

See ReqImport process below.

Purchasing Documents Open Interface (PDOI)

You can automatically import and update price/sales catalog information and request for quotation (RFQ) responses from suppliers through the Purchasing Documents Open Interface. You can also import standard purchase orders (for example, from a legacy system) through the Purchasing Documents Open Interface.

The Purchasing Documents Open Interface uses Application Program Interfaces (APIs) to process the data in the Oracle Applications interface tables to ensure that it is valid before importing it into Oracle Purchasing. After validating the price/sales catalog information or RFQ responses, the Purchasing Documents Open Interface program converts the information, including price break information, in the interface tables into blanket purchase agreements, or catalog quotations in Purchasing. For standard purchase orders, the Purchasing Documents Open Interface also validates the header, line, shipment, and distribution information before importing the purchase orders into Purchasing.

You can choose whether to import the data as standard purchase orders, blanket purchase agreements, or catalog quotations. You can also choose to update your item master and, for blanket purchase agreements and quotations, apply sourcing rules and release generation methods to the imported item. Blanket purchase agreements and quotations can also be replaced with the latest price/sales catalog information when your supplier sends a replacement catalog, or updated when the supplier sends an updated catalog. Standard purchase orders can only be imported as new documents.

One way to import the blanket purchase agreements and catalog quotations is through Electronic Data Interchange (EDI). The Purchasing Documents Open Interface supports the EDI transmissions of the price/sales catalogs (ANSI X12 832 or EDIFACT PRICAT) and responses to RFQs (ANSI X12 843 or EDIFACT QUOTES). Standard purchase orders cannot be transmitted through EDI. You can import these into the interface tables using a program that you write.

Receiving Open Interface

Within the Receiving Open Interface, receipt data is validated for compatibility with Purchasing. There are two Receiving Open Interface tables:

· RCV_HEADERS_INTERFACE

· RCV_TRANSACTIONS_INTERFACE

Receipt data that is entered through the Receipts window in Purchasing is derived, defaulted, and validated by the Receipts window. Most receipt data that is imported through the Receiving Open Interface is derived, defaulted, and validated by the receiving transaction pre-processor.

The pre-processor is a program that the Receiving Transaction Processor initiates for data entered in the Receiving Open Interface. The pre-processor simulates, in Batch mode, what the receiving windows do when you save a transaction.

After performing header- and line-level validation, the pre-processor checks the profile option RCV: Fail All ASN Lines if One Line Fails. If the profile option is set to ’Yes’ and any line failed validation, the pre-processor fails the entire transaction. If the profile option is set to ’No’ (and TEST_FLAG is not ’Y’), the Receiving Transaction Processor takes over and, for all successfully processed records, performs the same steps that occur when you normally save receipt information in Purchasing:

· Populates the RCV_SHIPMENT_HEADERS table in Purchasing with the receipt header information.

· Populates the RCV_SHIPMENT_LINES table in Purchasing for each receipt header entry in the RCV_SHIPMENT_HEADERS table in Purchasing.

· Populates the RCV_TRANSACTIONS table in Purchasing for each row in the RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES table if the column AUTO_TRANSACT_CODE in the RCV_TRANSACTIONS_INTERFACE table contains a value of ’RECEIVE’ or ’DELIVER’.

· Updates supply for accepted line items in the tables MTL_SUPPLY and RCV_SUPPLY.

· Calls the Oracle Inventory module for processing ’DELIVER’ transactions.

· Calls the Oracle General Ledger module for processing financial transactions, such as receipt-based accruals.

· Updates the corresponding purchase orders with the final received and delivered quantities.

Major Processes

A few important processes are described below. There are several other equally important processes in Oracle Purchasing. The user’s guide and Oracle Manufacturing API’s and Open Interfaces manual is a good source for information on them.

ReqImport

OVERVIEW

This interface lets you integrate Oracle Purchasing quickly with new or existing applications such as material requirements planning, inventory management, and production control systems. Purchasing automatically validates your data and imports your requisitions. You can import requisitions as often as you want. Then, you can review these requisitions, approve or reserve funds for them if necessary, and place them on purchase orders or internal sales orders.

FLOW

You must write the program that 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. Then you use the Submit Request window to launch the Requisition Import program for any set of rows.

You identify the set of rows you want to import 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 therequisition lines in the PO_REQUISITIONS_INTERFACE_ALL table. You also specify the requisition grouping and numbering criteria as parameters to the Requisition Import program.

Each run of the Requisition Import program picks up distribution information from either the PO_REQUISITIONS_INTERFACE_ALL or the PO_REQ_DIST_INTERFACE_ALL table. The PO_REQ_DIST_INTERFACE_ALL table was used in Release 11, for Self-Service Purchasing (known then as Web Requisitions). In Release 11i, you should use the PO_REQ_DIST_INTERFACE_ALL table to create multiple distributions only for requisitions created in non-Oracle systems that use multiple distributions. As long as the Multiple Distributions field in the Requisition Import program is No (or blank), Requisition Import looks for distribution information in the PO_REQUISITIONS_INTERFACE_ALL table.

The Requisition Import program operates in three phases. In the first phase, the program validates your data and derives or defaults additional information. The program generates an error message for every validation that fails and creates a row in the PO_INTERFACE_ERRORS table with detailed information about each error.

In the second phase, the program groups and numbers the validated requisition lines according to the following criteria. 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. 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.

If you do not provide values in either of these columns, the Requisition Import program uses the Group By parameter to group lines together. 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. You can group requisition lines in one of the following ways that the Requisition Import program supports by:

· BUYER

· CATEGORY

· LOCATION

· VENDOR

· ITEM

· ALL (all requisition lines grouped under one header)

If you provide a value in the REQ_NUMBER_SEGMENT1 column of the PO_REQUISITIONS_INTERFACE_ALL table, this value becomes the requisition number. If not, the Requisition Import program uses either the Last Requisition Number parameter if specified or the next unique number stored in the PO_UNIQUE_IDENTIFIER_CONTROL table, adds 1 to this number, and starts numbering requisitions. If any of the requisition numbers generated already exists, the program loops until it finds a unique number. For every line that is successfully imported, a default distribution is created with the account information that you specify. (You specify account information in any of the following columns in either the PO_REQUISITIONS_INTERFACE_ALL or the PO_REQ_DIST_INTERFACE_ALL table: CHARGE_ACCOUNT_ID, ACCRUAL_ACCOUNT_ID, VARIANCE_ACCOUNT_ID, BUDGET_ACCOUNT_ID, or any of the CHARGE_ACCOUNT_SEGMENT columns.) Requisition supply is also created for every approved requisition that is successfully imported.

In the third phase, the program deletes all the successfully processed rows in the interface tables, and creates a report which lists the number of interface records that were successfully imported and the number that were not imported. This report can be viewed by choosing View Output for the Requisition Import concurrent

Request ID in the Requests window. 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.

PO Create Documents Workflow

OVERVIEW

Purchasing integrates with Oracle Workflow technology to create standard purchase orders or blanket releases automatically from approved requisition lines. The workflow for creating purchasing documents automatically is called PO Create Documents.

In the Workflow Builder, PO Create Documents consists of several processes. Each of these processes is viewable in the Workflow Builder as a diagram whose objects and properties you can modify. Each workflow process consists of individual functions.

For each document that is created successfully by the PO Create Documents workflow, the PO Approval workflow is called to approve the document if you have allowed automatic approval.

FLOW

The PO Create Documents workflow is initiated at the end of the requisition approval workflow for approved requisition lines. The workflow begins automatic document creation if you’ve kept the item attribute Is Automatic Creation Allowed? set to Y for Yes, if source documents are associated with the requisition lines, and you have properly set up sourcing rules. If the source document associated with the requisition line is a quotation, a standard purchase order is created. If the source document is a blanket purchase agreement, a release is created.

PO Approval Workflow

OVERVIEW

Whenever you submit a purchase order or release for approval or take an action in the Notifications Summary window, Purchasing uses Oracle Workflow technology in the background to handle the approval process. Workflow uses the approval controls and hierarchies you define according to the setup steps in the section to route documents for approval. You can use the Workflow Builder interface to modify your approval process.

The purchase order approval workflow consists of processes, which are viewable in the Workflow Builder as a diagram, some of whose objects and properties you can modify. Each workflow process, in turn, consists of individual function activities.

The PO Approval workflow is initiated at the following points in Purchasing:

· When you choose Submit for Approval (and then choose OK) in the Approve Document window. See: Submitting a Document for Approval

· When you respond to a reminder in the Notifications Summary window reminding you to submit a document for approval that has not yet been submitted.

FLOW

The purchase order approval process is associated with an item type called PO Approval. This item type identifies all purchase order and release approval workflow processes available.

Refer to the Oracle Purchasing User’s guide for a comprehensive explanation of the flow.

Other important tables in Oracle Purchasing

PO_SYSTEM_PARAMETERS_ALL

PO_SYSTEM_PARAMETERS_ALL stores default, control, and option information you provide to customize Oracle Purchasing to your company’s needs. PO_SYSTEM_PARAMETERS_ALL corresponds to the Purchasing Options window. This table has no primary key. The table should never have more than one row.

PO_UNIQUE_IDENTIFIER_CONT_ALL

PO_UNIQUE_IDENTIFIER_CONT_ALL stores information about the current, highest, system–generated numbers for the Oracle Purchasing tables that require special sequencing. You need one row for each sequentially system–generated number for each organization. The table includes rows for each of the following: purchase orders, requisitions, receipts, suppliers, quotations, and requests for quotations (RFQs).

For each organization, there are four rows for each of the following entities: PO_HEADERS_ALL, PO_REQUISITION_HEADERS_ALL, PO_HEADERS_RFQ and PO_HEADERS_QUOTE. There are two rows corresponding to the entities PO_VENDORS and RCV_SHIPMENT_HEADERS.

The information for the quotation and RFQ sub–entities is associated with the PO_HEADERS_ALL table entity. TABLE_NAME values for quotations and RFQs are ’PO_HEADERS_QUOTE’ and ’PO_HEADERS_RFQ’ respectively.

PO_LINE_TYPES_B

PO_LINE_TYPES_B contains information about the line types you use in your business. You need each row for each line type you use. Oracle Purchasing uses this information to provide default information when you create a document line using a line type. Oracle Purchasing also uses this information to control how you enter information on your document lines according to the line type you choose.

ORDER_TYPE_LOOKUP_CODE is ’AMOUNT’ for an amount–based line type or ’QUANTITY’ for a quantity–based line type.

PO_DOCUMENT_TYPES_B

PO_DOCUMENT_TYPES_ALL_B contains information about default, control, and option information you provide to customize Oracle Purchasing document management for your company’s needs.

PO_DOCUMENT_TYPES_ALL_B corresponds to the Document Types window.

PO_ACTION_HISTORY

PO_ACTION_HISTORY contains information about the approval and control history of your purchasing documents. There is one record in this table for each approval or control action an employee takes on a purchase order, purchase agreement, release, or requisition. Each row includes references to the document itself, the employee who acted on the document, the date of the action, the type of action taken on the document, and a note each employee can leave when taking an action on the document.

Oracle Purchasing uses this information to display history information about documents and to forward documents in the approval process to the appropriate employee.

Important Profile Options in Oracle Purchasing

PO: AUTOCREATE GL DATE OPTION

Indicates the date used on purchase orders generated by AutoCreate: The autocreate date is used as the purchase order date. The GL date on the requisition distribution is used as the purchase order date.

PO: AUTOMATIC DOCUMENT SOURCING

Yes means that Purchasing automatically defaults source document and pricing information for an item from the most recently created blanket purchase agreement or quotation. No means that this source document information comes from the Approved Supplier List window, where you must specify which source documents to use. Note that if an item on a requisition is associated with both a blanket purchase agreement and a quotation, Purchasing uses the blanket purchase agreement even if the quotation was created more recently.

PO: DISPLAY THE AUTOCREATED DOCUMENT

Yes or No indicates whether Purchasing opens the appropriate transaction window (Purchase Orders window, RFQs window, or Sourcing negotiation page) and displays the created line(s) when you autocreate a document.

PO: ENABLE SQL TRACE FOR RECEIVING PROCESSOR

Yes means that when you run the Receiving Transaction Processor to import data from another system using the Receiving Open Interface, the View Log screen displays the receiving transaction pre–processor’s actions, including errors, as it processed the receipt data from start to finish. (The profile option RCV: Processing Mode must also be set to

Immediate or Batch for the Yes option to work.) Yes also generates a database trace file; if you need help with an error that occurs while the

Receiving Transaction Processor runs, Oracle Support Services may ask you for this trace file. This profile option should be set to Yes only while debugging the Receiving Open Interface or for generating a trace file.

The Receiving Open Interface validates receipt transactions from other systems and uses the Receiving Transaction Processor to import the validated data into Purchasing.

PO: RELEASE DURING REQIMPORT

Yes or No indicates whether Purchasing can automatically create releases during the Requisition Import process.

PO: RESTRICT REQUISITION LINE MODIFY TO QUANTITY SPLIT

Yes or No indicates whether Purchasing restricts requisition line modify in AutoCreate to only splitting the quantity of a line. No means that the standard AutoCreate requisition line modify logic applies.

PO: WRITE SERVER OUTPUT TO FILE

Yes or No indicates whether log details are written to a flat file rather than to the standard concurrent manager details log viewable through the View Log button in the Submit Request window when running the Purchasing Documents Open Interface program.

Yes means log details are written to a flat file. No means log details are written to the concurrent manager log screen, which can cause overflow problems for large catalogs. Leaving this profile option blank means log details are not written at all, which improves performance.

RCV: PROCESSING MODE

Indicates the processing mode used after you save your work for receiving transactions:

Batch	The transaction goes to the interface table, where it will be picked up the next time the Receiving Transaction Processor runs.
Immediate	The transaction goes to the interface table, and the Receiving Transaction Processor is called for the group of transactions that you entered since you last saved your work.
Online	The Receiving Transaction Processor is called directly.

RCV: ALLOW ROUTING OVERRIDE

Yes or No indicates whether the destination type assigned during requisition or purchase order entry can be overridden at receipt time.

RCV: DEBUG MODE

If set to Yes, and RCV: Processing Mode set to Immediate or Batch, debug messages will be printed to the concurrent log file.

RCV: DEFAULT INCLUDE CLOSED PO OPTION

If it is set to Yes, a search in the Enter Receipts window and the Receiving Transactions window automatically select the Include Closed POs checkbox. Your search results will then include closed orders.

The Receiving Open Interface (including ASN) will allow a receipt against orders with the status of Closed for Receiving if this profile is set to Yes. Any setting other than Yes prevents receiving against orders using the Receiving Open Interface with the status of Closed for Receiving. 

Basic Purchasing Setups

The purchasing user must be set as a buyer in Oracle applications. Before setting the user as buyer he/she must be an employee in applications.

Employee Setup

Employee should be assigned the position and job. This is useful in PO approval workflow.

The view used is per_people_v, per_people_address_v, per_people_assigment_v to store the employee information.

Buyer Setup

Once the user is set as buyer then he/she can create/approve/print the purchase orders. Whether the users can create/approve/print the purchase orders is decided by how the document types are setup.

The table which stores the buyer is PO_AGENTS and the view used for the buyer name and other details is PO_AGENTS_V.

The important columns PO_AGENTS_V

Sr.no	Column Name	Comments
1	Agent_id	Unique agent id
2	Agent_name	Agent Name
3	Location_id	Unique location id
4	Location_code	Location code
5	Start_date_active	Start date active
6	End_date_active	End date active

Document Types

Document types there are certain attributes needs to be set. They are explained below-:

1) Owner can approve: If we check this attribute then user can approve the documents he has created. This field is not updatable when the document type is RFQ or Requisition.

2) Approver can modify: If we check this attribute then approver the contents of the document. This is not applicable to RFQ and requisitions.

3) Can change forward to: This indicates test that the user can change the name of the approver in the approval window.

4) Can change forward from: This indicates that the user can change the name of the document creator. This is available only for document type requisition.

5) Can change approval hierarchy: Preparers and approvers can change the approval hierarchy in the approval document window.

6) Disable: Check it to disable the Document type.

7) Access Level: How the users can access the document type.

a. Full: Full access to the user

b. Modify: Can modify the document type

c. View Only: Can only view the document type

8) Archive On: When the archival of document type will take place.

a. On approval: On approval of the document

b. On Printing: On printing of the document.

9) Approval workflow: Which workflow the purchasing will use to approve the document type in question. One can define a custom workflow and also mention the name of the workflow.

10) Default Hierarchy: What hierarchy the approval process will follow is to be mentioned here.

Table Used

The table where the information is stored is PO_DOCUMENT_TYPES_V

Supplier Setup

The table where the information is stored is PO_VENDORS

Sr.no	Column Name	Comments
1	Vendor_id	Unique vendor id
2	Vendor_name	Vendor or supplier name
3	Segment1	Vendor Number
4	Start_date_active	Start date active
5	End date active	End date active

Another important table associated with this screen is PO_VENDORS_SITES_ALL. This stores the important information of vendor sites.

Sr.no	Column Name	Comments
1	Vendor_site_id	Unique vendor site Id
2	Vendor_id	Unique vendor site id refers PO_VENDORS
3	Vendor_site_code	Vendor site code

Purchase Orders

Creation Of Standard Purchase Orders

Creation of purchase orders has three parts. First is the header information second is the line information and the third is the shipments and distributions information. This applies for the standard purchase order.

Sr.no	Column Name	Comments
1	Po_header_id	Unique Po Header Id
2	Agent_id	Agent id refers PO_AGENTS_V
3	Segment1	PO Number
4	Revision_num	Revision Number for PO
5	Vendor_id	Unique vendor id refers PO_VENDOR_ID
6	Vendor_site_id	Unique vendor site id refers PO_VENDOR_SITES_ALL
7	Vendor_contact_id	Vendor contact id
8	Ship_to_location_id	Where the material will be shipped by supplier
9	Bill_to_location_id	Where the Bill/Invoice will be sent by the supplier
10	Currency_code	Currency code
11	Authorization_status	Authorization status for the PO Open/Closed/Approved/Incomplete
12	Type_look_up_code	What is the type of PO Standard/Blanket/Planned
13	Org_id	Operating Unit

The second type of information stored is line level information.

Its is stored in the table PO_LINES_ALL

Sr.no	Column Name	Comments
1	Po_line_id	Line identification number
2	Po_header_id	PO header id refers PO_HEADERS_ALL
3	Line_type_id	Line type_id such as Goods/Services/Expense etc
4	Line_num	Unique line num for each line item
5	Item_id	Item to purchased refers MTL_SYSTEMS_ITEMS
6	Item_rev	Revision of the item refers MTL_SYSTEM_ITEMS
7	Item_description	Description of item
8	Quantity	Quantity to be entered
9	Unit_price	Price of one unit
10	List_price	Unit price from price list
11	Org_id	Operating unit from where purchasing will take place
12	Promise_date	Promise date by supplier
13	Need_by_date	Date by which the material is required

The third type of information is the shipment

The information is stored in PO_LINE_LOCATIONS_ALL

Sr.no	Column Name	Comments
1	LINE_LOCATION_ID	Unique identifier LINE_LOCATION_ID
2	PO_HEADER_ID	Refers PO_HEADERS_ALL
3	PO_LINE_ID	Refers PO_LINE_ALL
4	QUANTITY	Quantity to be shipped
5	SHIP_TO_LOCATION_ID	Unique Identifier for the quantity to be shipped
6	SHIPMENT_TYPE	Price break, Blanket ,Standard
7	ORG_ID	Operating Unit

The distribution information is stored in PO_DISTRIBUTIONS_ALL

Sr.no	Column Name	Comments
1	Po_Distribution_Id	Unique Distribution Id
2	Po_Header_Id	PO Header Identification number referring PO_HEADERS_ALL
3	Po_Line_Id	PO Line identification number referring PO_LINES_ALL
4	Line_Location_Id	Refers PO_LINE_LOCATIONS_ALL
5	Set_Of_Books_Id	Set of Books
6	Code_Combination_Id	GL Code combination id for charge account
7	Quantity_Ordered	Quantity Ordered
8	Distribution_Num	Unique distribution number
9	Destinition_Type_Code	Destination type Code for e.g. Inventory
10	Destination_Organization_Id	Destination organization id
11	Destination_Subinventory	Destination Sub-inventory
12	Org_Id	Operating unit
13	Po_Release_Id	PO Release identification number if the PO type is blanket PO

Thus to summarize the information for Standard, Planned is stored in the following tables.

1) PO_HEADERS_ALL

2) PO_LINES_ALL

3) PO_LINE_LOCATIONS_ALL

4) PO_DISTRIBUTIONS_ALL

Creation of Blanket Purchase Order

When the purchase order type information is of the type blanket then the header and line level information is stored in same table as that of standard PO. For a blanket one more transaction named a Release transaction is made. This release transaction then creates the shipment information and the distribution information. Therefore for a blanket transactions following tables are used.

1) PO_HEADERS_ALL

2) PO_LINES_ALL

3) PO_RELEASE_ALL

4) PO_LINE_LOCATIONS_ALL

5) PO_DISTRIBUTIONS_ALL

Thus a blanket PO is same as Standard PO with the help of extra transaction call Releases. The table for releases is PO_RELEASE_ALL

Sr.no	Column Name	Comments
1	PO_RELEASE_ID	PO Release identification Number
2	PO_HEADER_ID	Refers PO_HEADERS_ALL
3	RELEASE_NUM	Unique release num
4	AGENT_ID	Buyer ID refers PO_AGENTS_V
5	RELEASE_DATE	The date on which release is created
6	REVISION_NUM	Revision number is generated when any changes are done to release information
7	APPROVED_FLAG	Y if the release in question is approved
8	APPROVED_DATE	Date on release is approved
9	PRINT_COUNT	No of times the release is printed
10	PRINT_DATE	Last printed date of the release
11	AUTHORIZATION_STATUS	Different status of the releases such as Open/Closed/Approved/Incomplete
12	ORG_ID	Operating unit

Concept of Multi Organization in Purchasing

In Oracle purchasing can be done across multiple organizations also called as operating units. So to accommodate this oracle has provided multi org views for the base tables of purchasing. For instance the table PO_HEADERS_ALL stores the header information of all the orgs. For using multi org view we need to set ORG_ID context variable using the AOL built in package. The syntax is given below.

FND_CLIENT_INFO.SET_ORG_CONTEXT(<ORG_ID Value>)

Once this is set then one can get rows in from all multi org views. Table below illustrates the base tables and there multi org views.

Base Table	Multi Org View
PO_HEADERS_ALL	PO_HEADERS
PO_LINES_ALL	PO_LINES
PO_LINE_LOCATIONS_ALL	PO_LINE_LOCATIONS
PO_RELEASES_ALL	PO_RELEASES
PO_DISTRIBUTIONS_ALL	PO_DISTRIBUTIONS
PO_VENDOR_SITES_ALL	PO_VENDOR_SITES

Order to Cash - Technical Flow in R12

This article lists the Order to Cash Flow in R12, with technical flow of data as taught in our FocusThread trainings. 

 1. Order Entry

This is first stage, When the order is entered in the system, it creates a record in order headers and Order Lines table.

• Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one tableOE_ORDER_HEADERS_ALL FLOW_STATUS_CODE = ENTERED, BOOKED_FLAG = N), Primary key=HEADER_ID

•  
  • No record exist in any other table for this order till now.

• Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID. OE_ORDER_LINES_ALL(FLOW_STATUS_CODE = ENTERED, BOOKED_FLAG = N, OPEN_FLAG = Y) Primary key= LINE_ID

2.Order Booking
This is next stage, when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table get affected.

• OE_ORDER_HEADERS_ALL (FLOW_STATUS_CODE as BOOKED, BOOKED_FLAG updated to Y)
• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE as AWAITING_SHIPPING, BOOKED_FLAG updated Y)
• WSH_DELIVERY_DETAILS (DELIVERY_DETAIL_ID is assigned here, RELEASED_STATUS ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)
• WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present inWSH_DELIVERY_DETAILS, DELIVERY_ID remains blank till this stage)

*In shipping transaction form order status remains "Ready to Release".

At the same time, Demand interface program runs in background And insert into inventory tables MTL_DEMAND, here LINE_ID come as a reference in DEMAND_SOURCE_LINE

3. Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once this program get successfully get completed, the MTL_DEMAND and MTL_RESERVATIONS table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.

4. Pick Release
Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.

Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.
Once pick release is done these are the tables get affected:

• If step 3 is not done then MTL_RESERVATIONS gets updated now.
• WSH_NEW_DELIVERIES (one record gets inserted with SOURCE_HEADER_ID= order header ID, STATUS_CODE=OP =>open)
• WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ID gets assigned which comes from WSH_NEW_DELIVERIES)
• WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘S’ ‘submitted for release’)
• MTL_TXN_REQUEST_HEADERS
• MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)
• (move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)
• MTL_MATERIAL_TRANSACTIONS_TEMP (link to above tables through MOVE_ORDER_HEADER_ID/LINE_ID, this table holds the record temporally)
• MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)
• MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )

*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS

5.Pick Confirm/ Move Order Transaction

Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.

Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.

• MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘PICKED’ )
• MTL_MATERIAL_TRANSACTIONS (LINE_ID goes as TRX_SOURCE_LINE_ID)
• MTL_TRANSACTION_ACCOUNTS
• WSH_DELIVERY_DETAILS (RELEASED_STATUS becomes ‘Y’ => ‘Released’ )
• WSH_DELIVERY_ASSIGNMENTS
• MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
• MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')

* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release

6.Ship Confirm
Here ship confirm interface program runs in background. Data removed from WSH_NEW_DELIVERIES.

The items on the delivery gets shipped to customer at this stage.

• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’)
• WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)
• WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)
• MTL_TRANSACTION_INTERFACE
• MTL_MATERIAL_TRANSACTIONS (linked through Transaction source header id)
• MTL_TRANSACTION_ACCOUNTS
• Data deleted from MTL_DEMAND, MTL_RESERVATIONS
• Item deducted from MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)
• MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , 'Issued out of store')

7.Enter Invoice

After shipping the order the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order.

RA_INTERFACE_LINES_ALL (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in

RA_CUSTOMER_TRX_ALL (CUST_TRX_ID is primary key to link it to TRX_LINES table and TRX_NUMBER is the invoice number)
RA_CUSTOMER_TRX_LINES_ALL (LINE_ATTRIBUTE_1 and LINE_ATTRIBUTE_6 are linked to order number and LINE_ID of the orders)

8.Complete Line
In this stage order line level table get updated with Flow status and open flag.
OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’, OPEN_FLAG “N”)

9.Close Order
This is last step of Order Processing. In this stage only OE_ORDER_LINES_ALL table get updated. These are the table get affected in this step.

OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘closed’, OPEN_FLAG “N”)

OE_ORDER_HEADERS_ALL

R12 SLA - Subledger Accounting

R12 SLA - Subledger Accounting 
Please find first in the series of articles on R12 Subledger Accounting .
This article is Part-1 of Subledger Accounting.

Why SLA and what does it do?Oracle Sub Ledger accounting (SLA) is accounting hub in Oracle Application Release 12 (R12). It is used to derive all attributes required to account a transaction in Oracle General Ledger. In R12, SLA is used to derive the very basic accounting attributes like entered amount, accounted amount, Date, Currency code etc and the complex attributes like Ledger, Code Combination ID, Periods etc. After deriving these accounting attributes the transactions are then interfaced to GL from SLA. Thus in R12 no sub ledgers (AP, PO, PA etc) interfaces the transactions directly to GL, but all the transactions are interfaced to GL in following 2 steps: 
1. Sub ledgers interface the data to SLA.
2. SLA derives the accounting information and interfaces the data to GL. 

SLA gives the flexibility to manage the entire accounting rule at one place, which acts as a single source of truth for GL. 

Note: There is no separate responsibility to access SLA setup or the view the transactions generated by SLA. Rather we can access SLA setup and review accounted transactions with extended menus attached to each sub ledger module. 

Menu to access the SLA accounted transactions. 



Menu to access the SLA Setup. 
 


How does SLA works? 1. Register sub ledger transactions in SLA. After validating / approving / costing the transaction in the respective module, the sub ledger calls SLA API to create a reference of the validated transaction in SLA. This reference is known as EVENT. Events are created by calling the public API “xla_events_pub_pkg.create_events” provided by SLA. It is up to the sub ledgers on how to call the API. For example Oracle Projects call this API from concurrent program “PRC: Generate Cost Accounting Events” and Oracle Payables calls this API while user creates accounting for the Invoice. 

While calling xla_events_pub_pkg.create_events, oracle passes a unique id and event class (Will discuss in next step). Unique ID can be an invoice id or a po_distribution id or an expenditure_item_id etc. As soon as the sub ledger generates event in SLA, SLA returns unique event_id. This event_id will then act as a reference to all the accounting entries generated by the SLA. Once event is successfully created in SLA, means that the transaction is registered in SLA for accounting. 

Taking the example of Oracle Projects in 11i where after costing the transaction user need to run the ‘PRC: Interface Cost to General Ledger’ followed by ‘Journal Import’ followed by ‘PRC: Tieback process’. But in R12 user only need to run “PRC: Generate Cost Accounting Events” which will register events in SLA and thereafter SLA will take care of accounting the transaction and interfacing it to GL. There is no tieback process in R12, as there is one to one reference of event id between SLA and sub ledger tables. 


2. How does SLA understand whether unique id is invoice id or a po_distribution id or an expenditure_item_id as SLA uses same table to store all the identifier? In step 1 we discussed that while creating the event we also need to pass event class. This event class is used to distinguish between the types of transaction passed for processing. To understand this better we will go thru the seeded oracle information. 


Navigation: 

Responsibility: Projects, Vision Services (USA) 
Menu: Setup > Subledger Accounting > Accounting Methods Builder > Events > Event Modal 


This screen shows the hierarchical structure of different transactions that can be interfaced to SLA. Because the above screen shot is from Oracle Projects responsibility thus it shows only the projects related transactions. In the entity screen we see only those transactions that can be interfaced to the GL, that’s why we do not see Invoice as one of the entity as Invoices are not directly interfaced to GL from PA but they are routed thru AR. 

Identifiers are the unique ID that is passed to SLA from sub ledgers. Per the screenshot Oracle is passing expenditure_Item_id for entity ‘EXPENDITURE’. “Identifier Column” field under Identifier window tells what column in SLA table should store expenditure_item_id. The identifier columns that can be used are SOURCE_ID_INT_1 to 4, SOURCE_ID_DATE_1 to 4, SOURCE_ID_CHAR_1 to 4 these values and columns are present in table XLA_TRANSACTION_ENTITIES. 


Event Class window displays the different kind of expenditure transactions that can be interfaced to GL. This level of hierarchy is known as Event class, which is further classified into Event Types. In PA we have different event types like Labor Cost, Misc Cost, Usage Cost, Supplier Cost etc. Further we could classify Supplier Cost as Expense Report and Invoices as Oracle Projects can interfaces only these 2 transactions from AP. 




3. Based on the identifiers and event class, how SLA creates accounting lines? After registering the event in SLA, we can create accounting entries by running executable XDODTEXE. This executable is provided by SLA and is used by all the sub ledgers with different concurrent program names. Around 160 concurrent programs are uses the same executable for example in Projects it is used with name “PRC: Create Accounting”. This executable does the following: 
a. Gather information from base tables in sub ledgers. 
b. Derive the accounting attributes based on the data fetched from sub ledgers. 
c. Derive code combination id based on the business rules. 
d. Create journal lines based on the seeded Journal definition. 
e. Create lines in XLA_AE_HEADERS and XLA_AE_LINES. 

MO_GLOBAL-Dive into R12 Multi Org Design

MO_GLOBAL-Dive into R12 Multi Org Design


I hope you have already read article Basics of Multi Org in R12 . A few questions come to mind when we think about Multi Org in R12. The best way to analyse those questions is by opening package MO_GLOBAL. Don't worry if you are not yet on R12, package MO_GLOBAL is installed 11.5.10 too.Lets get digging.


How is CLIENT_INFO being replaced in R12? 
Lets take an example.
In pre Release 12, you would have had following methodology for PO_HEADERS_ALL
a. A table is created in PO Schema, named PO_HEADERS_ALL
b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL
c. Create a view PO_HEADERS in APPS schema, as "select * from po_headers_all where org_id=client_info"

But now in R12, following will happen
a. A table is created in PO Schema, named PO_HEADERS_ALL
b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL
c. Another synonym named PO_HEADERS is created in APPS, referring to PO_HEADERS_ALL
d. A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY.
This can be double-checked by running SQL select * from all_policies where object_name='PO_HEADERS'
e. The effect of this policy is that,whenever you access PO_HEADERS, Oracle RLS will dynamically append WHERE CLAUSE similar to below
SELECT * FROM PO_HEADERS WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id) 
Also see **** below, latter



Does this mean, if I create a new custom table, I will have to apply RLS [ Row Level Security ] against Custom table too?
Yes indeed, if it contains data partitioned by ORG_ID. All you need to do in such case is to assign package function MO_GLOBAL.ORG_SECURITY to that table/synonym/view.



Will the Multi Org Row Level security be applied against the table or the synonym or the view?
In theory, RLS can be applied against any of the above objects. However in practice, you will apply RLS against Objects in APPS Schema. This means, you will most probably apply RLS on Synonyms. Basically, the Multi Org Views are now replaced by RLS Secured Synonyms. Hence no code change is required where the pre-R12 Multi-Org secured view was being accessed. The responsibility of securing data as per ORG_ID now lies with RLS [also known as VPD - Virtual Private Database].


I have made changes to my Multi Org Security Profile, by attaching a new Org Hierarchy. Do i need to run any process?
Just like we do in HRMS, it is advised that any changes to Security Profiles must be followed by running "Security List Maintenance"



What is MO_GLOBAL.INITPurpose of mo_global.init :-
It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used.
If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table mo_glob_org_access_tmp
When & from where is mo_global.init called ?
This package procedure will be called as soon as you login or as soon as you switch responsibility. Just like FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE


Is mo_glob_org_access_tmp table a global temporary table?
Yes, it is. Hence after Multi Org is initialised for your session, your session will have X number of records in table mo_glob_org_access_tmp. X is the number of organizations assigned to MO Security profile [view org hierarchy or org list in security profile]



What is the purpose of MO_GLOBAL.ORG_SECURITY?The purpose of Row-Level-Security is to hide certain data[based on some conditions]. RLS does so by appending a where clause to the secured object.
1. MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE CLAUSE
2. The where clause will be appended to Table/Synonym/View for which Multi Org Row Level security is enabled


What is the purpose of MO_GLOBAL.SET_POLICY_CONTEXT ?
This procedure has two parameters
p_access_mode
Pass a value "S" in case you want your current session to work against Single ORG_ID
Pass a value of "M" in case you want your current session to work against multiple ORG_ID's
p_org_id
Only applicable if p_access_mode is passed value of "S"


In SQL*Plus, I wish to set my session to work against a specific Org [one single org]. How do I do that in R12
SQL>> exec MO_GLOBAL.SET_POLICY_CONTEXT('S',101);
In the above case, ORG_ID 101 will be assigned as current org for your session.
Internally, following code in blue will be executed by Oracle when you set your context to single Org, dbms_session.set_context('multi_org2', 'current_org_id', 101);


**** If the current database session is initialised for Single Org[as in above step], then Where clause appended to object by Row-Level-Security will be
WHERE org_id = sys_context('multi_org2','current_org_id')


Why will I as a Apps Techie ever use MO_GLOBAL.SET_POLICY_CONTEXT ?
Lets say you wish to call an API to create invoices in ORG_ID 101. In case the API does not have a parameter for Org_id, you can do the below
a. exec MO_GLOBAL.SET_POLICY_CONTEXT('S',101)
b. Call the Invoice API, which will internally read the ORG_ID from MO Current Context.



From SQL*Plus, I wish to simulate login to a specific responsibility. How do I do this?
a. Call FND_GLOBAL.INITIALIZE
This will set your responsibility id, user_id etc
b. call MO_GLOBAL.INIT
This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.

Suppliers in TCA - A dive into Vendor Tables in R12

Suppliers in TCA - A dive into Vendor Tables in R12 

Prior to R12, creation of a vendor/supplier record in eBusiness suite largely meant insertion of records in PO_VENDORS.

However, from R12 onwards, records are inserted into at least half a dozen tables when a single Supplier record is created.
This is largely due to the fact that Suppliers have been moved into the TCA DataModel.

In this article, I would like to show you the set of tables that are effected when a Supplier record gets created in Release12.
I will also touch base upon Supplier Sites and changes to taxation related tables.


End User Step 1
To begin with, we need to create a Supplier. Lets name it Go4Gold [which also happens to be the name of my old company].
Simply enter name of the Supplier in organization name field and click on Apply. This will create a Supplier.

End User Step 2.
You can doublecheck the created Supplier, which has Supplier Number 20186. 
This supplier number comes from a table  named  AP_SUPPLIERS. 

The registry id that you see is the Party_number field from hz_parties [TCA Party Table]

Now, lets have a look at the list of tables impacted by creating the above Supplier record.
I am not saying that inserting into below listed tables is the way to create Suppliers in R12 TCA Model [Use API's for that].
This article is purely for your understanding of the new data model for Suppliers in R12 TCA. 
Of course this will also be helpful to you when developing reports in R12.





Table HZ_PARTIES
SELECT * FROM hz_parties WHERE party_name= 'Go4Gold' ;
This happens to be the master table now instead of PO_VENDORS.
You will notice that the PARTY_NUMBER below is the Registry id in the R12 supplier screen.
Also, this party_id = 301934 will be referenced in the remainder set of tables.

Table HZ_PARTY_USG_ASSIGNMENTS 
SELECT party_id ,party_usg_assignment_id,party_usage_code FROM hz_party_usg_assignments 
WHERE party_id = 301934;
This table stores the Party Usages, for example, in this case it captures the fact that the given party_id is of type SUPPLIER.

Table HZ_ORGANIZATION_PROFILES
SELECT * FROM hz_organization_profiles WHERE party_id = 301934
This table captures additional Supplier information, for example, credit scoring details of Supplier or the Number of Employees working in Supplier Organization.

Table IBY_EXTERNAL_PAYEES_ALL
SELECT * FROM iby_external_payees_all WHERE payee_party_id = 301934

This table captures Payment related details of the Supplier. 
For example :-
    1. How should the supplier's remittance advice must be sent?
    2. What is the default Payment method Code for this supplier?
    3. Who bears the bank charges when lets say SWIFT payment is made?
This information can be setup at either the Supplier level or at Supplier Site level.





Table AP_SUPPLIERS
SELECT vendor_id, vendor_name,segment1,enabled_flag FROM ap_suppliers WHERE party_id = 301934 Alongside HZ_PARTIES, this is another master table that replaces the PO_VENDORS table of 11i.
Instead of expanding the design of HZ_PARTIES, oracle decided to hold the supplier specific attributes in AP_SUPPLIERS [fair enough ! ].

Table POS_SUPPLIER_MAPPINGS
SELECT * FROM pos_supplier_mappings WHERE party_id = 301934
This table holds the mapping between the AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID.
This is useful in cases whereby two vendors  effectively belong  the same HZ_Party Record.

Table ZX_PARTY_TAX_PROFILE
SELECT party_type_code, party_tax_profile_id FROM zx_party_tax_profile WHERE party_id = 301934
The taxation related details like Tax Codes, and Tax Accounts etc have been moved from AP into ZX.
ZX is the name of a new Application "E-Business Tax".

Efectively this application is the Tax repository/Taxation Engine for eBusiness Suite starting from R12.
Effectively this also means that our good old AP_TAX_CODES_ALL is no longer the master table for Taxes.
Now we have a new tax rate table, i.e. ZX_RATES_B.
ZX_ACCOUNTS is another table that has been introduced to capture accounting setup related to Tax Codes.





Database View PO_VENDORS
select vendor_name, segment1, party_number from po_vendors WHERE party_id = 301934
PO_VENDORS is a view in R12, that joins AP_SUPPLIERS & HZ_PARTIES.
Similarly, PO_VENDOR_SITES and PO_VENDOR_SITES_ALL are also views based upon AP_SUPPLIER_SITES_ALL.