Steps to have an Automatically Approved Purchase Orders Created from Requisitions

1. Identify the Supplier and Supplier Site that is desired to have the PO created automatically for.

2. Create a Quotation (Set it to Approved Status) for each item used by the selected Supplier and Supplier Site.

3. Create an ASL for the item and add the Supplier and Supplier Site.

4. Under the ASL Attributes, add the Quotation entered in Step 2

5. Create a Sourcing Rule for the Supplier and Supplier Site

6. Create an Assignment Set for this Organization or Item and list this Sourcing Rule in that Assignment Set
    (One Assignment Set can be used that will include all of the desired Assignments)

7. Set the Profile - MRP: Default Sourcing Assignment Set to the Assignment Set created in Step 6

8. Set the Workflow Item Attributes

- Is Automatic Creation Allowed? = Yes    (This is the PO Create Documents workflow)
- Is Automatic Approval Allowed? = Yes     (This is the PO Create Documents workflow)
- Send PO Autocreation to Background? = No    (This is the Requisition approval workflow)

To update the attributes, follow these steps in workflow builder:

a. Open workflow builder and connect to the database as the APPS user. Open the PO Create Workflow. The display name is PO Create Documents.

b. Expand the data source, and then the PO Create workflow item type branch within that data source.

c. Expand the Attributes branch within the PO Create workflow branch.

d. Double click on the attribute to open the Properties window. Set the value of the attributes as indicated.

e. Repeat the steps for item type PO Requisition Approval to set the indicated attribute.

9. Create a Requisition and submit it for Approval

10. Once Approved the Create Documents workflow will kick off and create a Standard PO

11. Since Is Automatic Approval Allowed? = Yes, the PO will be automatically submitted for Approval

12. If the Buyer defaulted on the PO has the approval authority the PO will be approved.
      If not, the PO will be forwarded up the Buyer's Approval Hierarchy that is already setup.
      The hierarchy used in the Create Documents Workflow, by which the Buyer is determined is as follows:
      (Will start with the Step 1, and continue until either a Buyer is found, or the hierarchy is exhausted.)

1. Get Buyer from Requisition Line
2. Get Buyer from Item
3. Get Buyer from Category on Requisition
4. Get Buyer from Source Document (For this case the Quotation created in Step 2 above)
5. Get Buyer from Contract (The Contract would need to exist for the Supplier and Supplier site)

NOTE: In R12 and higher this logic has changed where we decide if OK to use buyer from source document.
-If OK to use buyer from source document depends on the HR: Security Profile setting and if the source document is a global agreement.
-If the source document is a CPA, it will not be considered.

References:
http://mahamadsulthanoracleapplications.blogspot.com/2013/05/steps-to-have-automatically-approved.html

Charge Account Defaulting in PO

 

Charge account defaulting rules depend on 
a) Type of Item 
b) Destination Type
c) Destination Sub-inventory.

There are 3 kinds of Items used in Purchasing.

Expense Items - Referred to as Item A
Inventory Expense Items - Referred to as Item B
Inventory Asset Items - Referred to Item C


1) Expense Items - Referred to as Item A.
These items normally have the following attributes...

INVENTORY_ASSET_FLAG = N
PURCHASING_ITEM_FLAG = Y
INVENTORY_ITEM_FLAG = N

2) Inventory Expense Items - Referred to as Item B
These items normally have the following attributes...

INVENTORY_ASSET_FLAG = N
PURCHASING_ITEM_FLAG = Y
INVENTORY_ITEM_FLAG = Y


c) Inventory Asset Items - Referred to Item C
These items have the following attributes...

INVENTORY_ASSET_FLAG = Y
PURCHASING_ITEM_FLAG = Y
INVENTORY_ITEM_FLAG = Y
COSTING_ENABLED_FLAG = Y


2. DESTINATION TYPE :
The Destination type determines the final destination of the
items.
This can be of two types 
a) EXPENSE
b) INVENTORY - 



3. DESTINATION Subinventory:
When an Item is being received into Inventory , a sub-inventory 
needs to be specified. A subinventory can be 
a) ASSET subinventory
b) EXPENSE sub-inventory


The sql below will help in identifying what type of a 
subinventory , the item is being delivered INTO.


select 
substr(EXPENSE_ACCOUNT,1,6) Exp_act , 
substr(ENCUMBRANCE_ACCOUNT,1,7) Enc_act ,
ASSET_INVENTORY,
substr(SECONDARY_INVENTORY_NAME,1,15) S_name,
substr(DESCRIPTION,1,15) descrip,
substr(SOURCE_ORGANIZATION_ID,1,8) s_o_id
from 
MTL_SECONDARY_INVENTORIES
where 
SECONDARY_INVENTORY_NAME = '&destination_subinventory';

pass the value of DESTINATION_SUBINVENTORY 
from PO_DISTRIBUTIONS_ALL.DESTINATION_SUBINVENTORY

if the value of ASSET_INVENTORY = 1 then this is an ASSET subinventory.
if the value of ASSET_INVENTORY = 2 then this is NOT an ASSET subinventory.




3.Keeping in view of the types of Items and the destination mentioned above
These are the destination types that can be possible against each type of item

Item A can have ONLY one destination- EXPENSE

Item B can have the destination of 
a) EXPENSE
b) INVENTORY -- ASSET subinventory
c) INVENTORY -- EXPENSE subinventory

Item C can have the destination of 
a) EXPENSE
b) INVENTORY -- ASSET subinventory
c) INVENTORY -- EXPENSE subinventory



Defaulting Rules for 
1) Item A --- Defaults from the Expense a/c defined against the ITEM in the Receiving Organization.( Organization Items) 

=======================================================================================
2) Item B--- EXPENSE -Defaults from the Expense a/c defined against the ITEM in the
in the Receiving Organization.( Organization Items) 

2) Item B ---INVENTORY--- ASSET Subinventory .... charge account comes from 
a) EXPENSE a/c of the ASSET SUBINVENTORY in the RECEIVING Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information) 

b) If the PO distribution DOES NOT have a SUBINVENTORY information
Expense a/c defined in the Organizational Parameters of the Receiving Organization.

3) Item B ---INVENTORY--- EXPENSE subinventory -- comes from 
a) EXPENSE a/c of the EXPENSE SUBINVENTORY in the RECEIVING Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information) 

b) If the PO distribution DOES NOT have a SUBINVENTORY information
Expense a/c defined in the Organizational Parameters of the Receiving Organization.

=========================================================================================
4) Item C --- EXPENSE -Defaults from the Expense a/c defined against the ITEM in the
in the Receiving Organization.( Organization Items) 

5) Item C ---INVENTORY---ASSEST Subinventory .... charge account comes from 

a) Material a/c defined against the ASSET SUBINVENTORY /of the RECEIVING Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information)

b) Material a/c defined in the Organizational Parameters of the Receiving Organization.

6) Item C ---INVENTORY --EXPENSE Subinventory -- comes from 
a) Expense a/c defined against the EXPENSE Subinventory in Receiving Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information)

b) If the PO distirbution DOES NOT have a SUBINVENTORY information
Material a/c defined in the Organizational Parameters of the Receiving Organization.

References:
http://snroracle.blogspot.com/2013_10_01_archive.html

A New Custom Form in Oracle Apps

In this training article (for the readers of http://getappstraining.blogspot.com ), we will learn the steps needed to develop a custom Oracle Apps form from the very scratch.

In the previous chapter we learnt "how to customize an existing oracle delivered form"

Question: Why bother teaching this when Oracle fusion is destined to replace oracle forms by OA Framework?
Answer: Well firstly I am yet to hear an official word from Oracle in this regard, but  I agree it is highly likely that fusion will se demise of Oracle Forms. However more importantly Oracle will support current tech stack indefinitely, I.e Release 12 will be supported for foreseen time as per Apps Unlimited statement. Hence, many of the clients will keep using this technology for decades. Yes, I won't bother my kid learning Oracle form though. 


Question : Ok, what are the steps for building a screen from scratch?
Answer: Below steps in brief
A) Open up TEMPLATE.fmb, and save this as XXHELLOAPPS.fmb
B) Create a new window, by right clicking on Window/new
Name this window as XXHELLOAPPS,and assign it SubClass Type “WINDOW” from picklist.
C) Create a new canvas and name it XXHELLOAPPS , ensuring its Sublcass Type is “Content”
D) Make the windows property reference canvas XXHELLOAPPS and vice versa make the canvas reference windows XXHELLOAPPS.
E) Now create a block named XXHELLOAPPS . Lets keep this a control block for simplicity.
F) go to form level property, and set first navigation block to XXHELLOAPPS. 
G) Add a label and a field named “Hello_World” to this block.
    Go to pre-form to specify the new window and also the new blcok name in When-New-Form-Instance triggers.
   Open the program unit app_stand_window package body and the change the name of the window in Close_Window procedure.

H) Generate the form on PC using Control-T keystrokes. This will ensure that nothing critical has been missed out.
I)  FTP the form file to $XXPO_TOP/forms/US
Surely, this XX will be replaced by the naming convention at your client/company.
J)  cd to  $XXPO_TOP/forms/US
And f60gen on XXHELLOAPPS.fmb
This will create a file executable as XXHELLOAPPS.fmx
K) Go to Application Developer responsibility
Menu /applicaton/form
Register the form
L) Register the Forms Function
Have you read he article form functions[http://getappstraining.blogspot.com/2006/10/oracle-forms-functions-menus-and-their.html] yet?
This forms function must be registered against application "XX Purchasing".
M) Now add a menu item so that this forms function becomes available to specific responsibility.

Thats it, you will be able to open up this form from the responsibility.

Now some important notes:-
1. If you have a table based block, and if that block has some description type fields, then try not to fetch them from post query trigger. Instead, develop a view in apps schema and assign that view as base tale to this block. This has a benefit that users will be able to query on the description field if need be. Indeed you will need to do dml on the actual table yourself by overriding on-insert, on-update, on-delete and on-lock triggers.
2. For each block in the screen, create a form level package spec and package body. Your triggers in the block/block fields will make calls to that package.
3. Try not to do to much pl/sql within the form. Always do such database intensive operations in a database level package.
4. Try not using global variables, unless really needed. Give preference to the creation of form package variables.
5. In a multi record block, always add a field for Current Record Indicator.

Please let me know if anything is unclear. Feel free to ask your questions.

References:
http://oracle.anilpassi.com/a-new-custom-form-in-oracle-apps.html

FAQ Serial Numbers

 

1. What are the forms/programs?

Inventory > On-hand, Availability > Serial Numbers = INVITSNU.fmb
Inventory > On-hand, Availability > Generate serial numbers = Serial number generation (INV_SERIAL_NUMBER_PUB.GENERATE_SERIALS)

2. What tables?

1) MTL_SERIAL_NUMBERS: Serial numbers, this stores the serial numbers with a serial status noting if the serial is in stores, the current organization and subinventory, as well as mark ids that note if the serial number is locked..

2) MTL_SERIAL_NUMBERS_INTERFACE: Interface serial number table, this is the serial interface table used to note serial numbers that relate to transactions in the interface table (MTL_TRANSACTIONS_INTERFACE). See the Manufacturing Open Interface for more details.

3) MTL_SERIAL_NUMBERS_TEMP: Pending serial number table, this is the serial pending, temporary table used behind the scenes when transacting serial numbers. This goes hand-in-hand with the transaction temporary table (MTL_MATERIAL_TRANSACTIONS_TEMP).

4) MTL_UNIT_TRANSACTIONS: Unit transactions, this is the table used to track the history of serial numbers. Each unit transaction relates to a history record (MTL_MATERIAL_TRANSACTIONS) that outlines the transaction. The tables are joined by transaction_id. 

3. What is the difference between the various levels of serial uniqueness?

Here is an explanation of the uniqueness from the Inventory User's Guide page 5-162:

You use the Organization Parameters window to choose a type of serial number uniqueness for your organization. You can choose to enforce uniqueness Within inventory items, Within an organization, or Across organizations. The three levels for serial uniqueness are cumulative the definitions are as follows: 

• Within Inventory Items: 
Once you assign a serial number to a particular item you cannot assign the same serial number to the same item regardless of the organization. For example if you assign serial number SN100 to item A, you cannot assign serial number SN100 to any other instance of item A in any organization. This also includes CTO items derrived from base model A. However you could receive item B with serial number SN100 in any organization. 

• Within Organizations: 
In addition to the restrictions Within Inventory Items control, the same serial number cannot exist twice within the same organization. For example if you assign SN100 to item A, you will not be able to receive item B with the serial number SN100 in the same organization. However, you could receive item B with the serial number SN100 in any other organization. 

• Across Organizations: 
In addition to the restrictions Within Organizations, you cannot assign the same serial number to any other item regardless of the organization. For example if you assign SN100 to item A you will not be able to receive item B with the serial number SN100 in any organization. If you assign Across Organization uniqueness to any organization it restricts the serial generation in all other organizations. If one organization dictates Across Organizations, all other organizations must do so.

 

4.. What do the serial status numbers mean?

When one reviews the serial number table (MTL_SERIAL_NUMBERS) the column 'CURRENT_STATUS' indicates the status of a serial number. The column is populated with a number. For example, 3 means resides in stores. This means that the serial number has an associated onhand quantity and should be available to transact.

Here is a list of common serial number statuses and ids: 

Status Codes (ID and Description) 
--------------------- 
1 Defined but not used 
3 Resides in stores 
4 Issued out of stores 
5 Resides in intransit 
6 Pending status 
7 Resides in receiving 
8 Resides in WIP 

You can get a full list of the codes with the following SQL:

 

SELECT
lookup_type, 
lookup_code, 
meaning 
FROM mfg_lookups 
WHERE lookup_type = 'SERIAL_NUM_STATUS' 
ORDER BY lookup_type, lookup_code;

 

5.What is a mark id?

The serial number table (MTL_SERIAL_NUMBERS) holds a list of existing serial numbers in the Oracle Applications. When a serial number is being used by a transaction, mark ids are often populated until the transactions are complete. The mark ids act as a lock on the serial number and are meant to only be temporary until the transaction processes. The mark id columns are GROUP_MARK_ID, LINE_MARK_ID, LOT_LINE_MARK_ID. When the mark ids are populated, other transactions cannot use the serials. The Transact Move Order form is an example form that locks serials when they are allocated using the mark ids.
In the example of a move order allocation and assuming no lot control, the serial (MTL_SERIAL_NUMBERS) mark ids (GROUP_MARK_ID, LINE_MARK_ID, LOT_LINE_MARK_ID) match the pending transaction (MTL_MATERIAL_TRANSACTIONS_TEMP) transaction temp id (TRANSACTION_TEMP_ID).


6.What is the difference between serial generation 'At Receipt' vs. 'At Sales Order Issue'?

At inventory receipt 
Create and assign serial numbers when you 
receive the item. Thereafter, for any material 
transaction, you must provide a serial number 
for each unit. 

At sales order issue 
Create and assign serial numbers when you 
issue (ship) the item against a sales order. If 
you select this option, serial numbers are required 
at ship confirm. If you receive an item 
on an RMA (return material authorization), 
you must specify the same serial numbers you 
created at sales order issue. All other material 
transactions for this item bypass serial number 
information. 

No control 
Serial number control not established for this 
item. All material transactions involving this 
item bypass serial number information. 

Predefined 
Assign predefined serial numbers when you 
receive the item. Thereafter, for any material 
transaction, you must provide a serial number 
for each unit.

The serial generation of an item is also called the serial control code. One can also look up the serial control codes for an item via SQL. The list of items in the system is maintained in the table MTL_SYSTEM_ITEMS_B. The column SERIAL_NUMBER_CONTROL_CODE will list the current value of the serial generation. 
Here is a list of common serial control code ids and there descriptions: 

Control Codes (ID and Description) 
--------------------- 
1 No serial number control 
2 Predefined serial numbers 
5 Dynamic entry at inventory receipt 
6 Dynamic entry at sales order issue 

You can get a full list of the codes with the following SQL:
SELECT
lookup_type, 
lookup_code, 
meaning 
FROM mfg_lookups 
WHERE lookup_type = 'MTL_SERIAL_NUMBER' 
ORDER BY lookup_type, lookup_code;

7.How to track a serial number across organizations?
After transferring a serial number between organizations, it only appears in the current organization. The Onhand > Serial number query screen displays the current state of the serial number. It queries the serial number table (MTL_SERIAL_NUMBERS). The serial number table will only have one record for the serial number and item combination. The organization of the serial number is maintained in a column called, CURRENT_ORGANIZATION_ID. This maintains only the last organization to have the serial number. If the serial number changes organizations, the serial number will show only in the new organization. If a serial number is shipped to a customer, the serial is visible with status 'issued out of stores' in the last organization that owned the serial. 

To see the history of the serial number, one would look at the transaction history using the Transactions > Material Transactions form. You can query by item or serial number and see all related transactions. The form queries any transaction that touched the serial number. Unlike the serial number form that queried the serial's current state from MTL_SERIAL_NUMBERS, the material transaction screen queries against all transactions (MTL_MATERIAL_TRANSACTIONS) and looks at the serial number history table (MTL_UNIT_TRANSACTIONS).

Example -- The following query could be useful looking for all transactions of a given serial number -- Note that two queries are joined because the transaction history relates through the lot table if the item is lot controlled:
prompt
accept YourSerial DEFAULT 'JBP10000' prompt 'Please enter your serial (Default JBP10000): '
prompt

break on serial_number

select c.serial_number, a.transaction_id, a.transaction_type_id, a.transaction_quantity, 'No Lot'
from MTL_MATERIAL_TRANSACTIONS a, MTL_UNIT_TRANSACTIONS c
where a.TRANSACTION_ID = c.TRANSACTION_ID
and c.serial_number like '&YourSerial'
UNION
select c.serial_number, a.transaction_id, a.transaction_type_id, a.transaction_quantity, b.lot_number
from MTL_MATERIAL_TRANSACTIONS a, MTL_TRANSACTION_LOT_NUMBERS b, MTL_UNIT_TRANSACTIONS c
where a.TRANSACTION_ID = b.TRANSACTION_ID
and b.SERIAL_TRANSACTION_ID = c.TRANSACTION_ID
and c.serial_number like '&YourSerial'
/

8.How to enter serial number descriptions (or Descriptive Flexfields / DFF)?

You can enter descriptions for serial numbers using descriptive flexfields. 
There are two that would be helpful: Serial Attributes or Serial numbers 
Serial attributes apper in the onhand > serial number screen in the Serial Info tab with the column name of Attributes. This is similar to the serial entry screens that label the field attributes as well. The Serial numbers descriptive flexfield is visible upon placing the cursor in the descriptive flexfield field usually labled with two brackets like [] also called mug-handles.
To maintain descriptive flexfields for serial numbers:
Navigate to Inventory > Setup > Flexfield > Descriptive > Segments 
Query Serial Attributes or Serial numbers 
You could then setup descriptions for the serials
Read more about descriptive flexfields (DFF) also called attributes in the user's guide.

 

References:

http://snroracle.blogspot.com/2014/02/faq-serial-numbers.html

 

Oracle Purchasing Approval

 

In PO there are two methods to route documents for approval.
1.Approval Hierarchies (uses position hierarchies, A tree of hierarchy more than one person above to approve)

2.Employee/Supervisor Relationships (use employee/supervisor relationship, Just one person above to approve)
Approval 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
Employee/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

References:
http://2lyoracleapps.blogspot.com/

ERP-Enterprise Resource Planning

ERP is often a confusing terminology but to put it in simple terms: ERP is an acronym meaning Enterprise Resource Planning. It is a software package/solution most often used within the manufacturing environment. ERP is a business tool that the management uses to operate the business for the day-in and day-out. It is usually comprised of several modules such as a financial module, a distribution module and a production module. Each of these modules share information that is housed within the database structures on which the ERP system was coded.

ERP helps to break down barriers between departments within a company. For example, many times the sales department may be selling 25% more product than the production department produces. By utilizing an ERP system, the sales department, production department, operations management, shipping, financial department, purchasing department all have access to the up-to-date information that is needed to operate smoothly within any manufacturing environment.

Maximum Personalization on Purchase Requisition Form

We keep on creating personalization on Purchase Requisition screen as per business requirements, one fine day we found the limit. Oracle allows only maximum of 100 personalizations in a standard form its what we thought but digging deeper we found that numbers could be tweaked with fractions (eg: 10.1, 10.2,10.3,11,12)

Its very rare you will need more than 100-Personalizations in a form.

References:

http://2lyoracleapps.blogspot.com/