Standard Sales Order (Data Flow)

Steps
1. Order Entry
2. Order Booking
3. Pick Release
4. Ship Confim
5. Invoice Entry
6. Transfer 2 GL

Steps in Detail
1. Order Entry

(for an example I am creating dummy item & increasing on hand quantities)
Item Creation
Navigation: Inventory, Vision Operations (USA) - Items >> Master Items

Navigation: Inventory, Vision Operations (USA) - Items >> Master Items : Organization Assignment

For on hand quantity, to receive the item by miscellaneous transaction

Navigation: Order Management Super User, Vision Operations (USA) - Inventory >> Transaction : Miscellaneous Transaction

Transaction Lines

On hand Quantity verification

Navigation: Inventory, Vision Operations (USA) - On-hand, Availability : On-hand Quantity

Navigation: Order Management Super User, Vision Operations (USA) - Order, Returns : Sales Orders

Line Items

2. Order Booking

3. Pick Release

Navigation: Order Management Super User, Vision Operations (USA) - Shipping - Release Sales Orders >> Release Sales Orders (Order Number - 56733)

Execute Now

Navigation: Order Management Super User, Vision Operations (USA) - Shipping - Release Sales Orders >> Transactions

Delivery Tab

4. Ship Confim

Delivery Confirmed

Run the "Workflow Background Process" Concurrent Program

5. Invoice

Navigation: Receivables, Vision Operations (USA) - Transactions >> Transactions
In Query Mode (Reference - Sales Order Number - 56733)

Distributions

Navigation: Receivables, Vision Operations (USA) - Receipts >> Receipts

Apply & Save

6. Transfer 2 GL

Navigation: Receivables, Vision Operations (USA) - Interfaces >> General Ledger

How to create the Hierarchical Tree in oracle forms?

There are 2 ways to populate a hierarchical tree (1. Record Group, 2. Query Text)

Step1 - Create Record group
Step2 - Create hierarchical tree item (Control Block)
                         - WHEN-TREE-NODE-SELECTED
                         - WHEN-TREE-NODE-ACTIVATED
Step3 – Create data block

Step1 - Create Record group

The record Group query should be in the specified structure., which requires 5 Columns.

SELECT STATUS, LEVEL, LABEL, ICON, VALUE FROM TABLE;

STATUS - Indicates the initiate status of the Node (Normally Value is 1).
LEVEL - This is a specific pseudo-column Derived from “CONNECT BY”.
LABEL -This is the visible label of the Node.
ICON - That contains the icon name of the Node (can be NULL).
VALUE -That contains the value of the Node.



Step2 - Create hierarchical tree item (Control Block)



Add the trigger code @ WHEN-NEW-FORM-INSTANCE and WHEN-TREE-NODE-ACTIVATED

DECLARE

   vi_tree      item;

   vn_tree_rg   NUMBER;

BEGIN

   vi_tree      := FIND_ITEM ('TREE_BLK.EMP_TREE');

   vn_tree_rg   := POPULATE_GROUP ('EMP_TREE_RG');

   ftree.SET_TREE_PROPERTY ( vi_tree, ftree.record_group, 'EMP_TREE_RG');

EXCEPTION

   WHEN OTHERS

   THEN

      fnd_message.debug (SQLERRM);

END;

Add the trigger code @ WHEN-TREE-NODE-SELECTED

DECLARE

   vi_htree        item;

   vc_node_value   VARCHAR2 (100);

BEGIN

   vi_htree        := FIND_ITEM ('TREE_BLK.EMP_TREE');

   vc_node_value   := ftree.GET_TREE_NODE_PROPERTY ( vi_htree, :SYSTEM.trigger_node, ftree.node_value);

   SET_BLOCK_PROPERTY ( 'emp', default_where, 'EMPNO = ' || vc_node_value);

   GO_BLOCK ('EMP');

   EXECUTE_QUERY;

EXCEPTION

   WHEN OTHERS

   THEN

      fnd_message.debug (SQLERRM);

END;

Step3 – Create data block





@ Runtime

Interface(s) and API(s) List

R12 gives much idea about interfaces and APIs details within the applications itself.
The responsibility “Integration Repository” will provides the informations.

Table Creations

This section describes specifications for how to define your tables and the required columns to add. It also covers the APPS registration.

While creating the table (with table and column comments), the developer needs to ensure the following things.

1. Normalization.
2. WHO Columns.
3. DFF (ATTRIBUTES) / KFF (SEGMENTS) enabling feature. (APPS initialization.)

1. Normalization

Data anomalies can add to problems with large consumption of storage space, slow execution times, etc. There are 3 types of data anomalies

There are 5 levels of simplification (forms)

2.WHO Columns

If you add special WHO columns to your tables, your application users can track changes made to their data. Oracle Application Object Library lets users differentiate between changes they make using forms and changes they make using concurrent programs. Columns that tell you information about the creation and the last update of a row are:

LAST_UPDATED_BY
LAST_UPDATE_DATE
LAST_UPDATE_LOGIN
CREATED_BY
CREATION_DATE

Add the following columns to tell you information about the concurrent program that updated a row:

REQUEST_ID
PROGRAM_APPLICATION_ID
PROGRAM_ID
PROGRAM_UPDATE_DATE

You should allow NULLs in these columns because they do not have values when a user inserts a new row using a form.

3. DFF (ATTRIBUTES) / KFF (SEGMENTS) enabling feature. (APPS initialization.)

If you are going to create KFF / DFF based on your custom table; you need to include flex field structured columns and should registered your table in APPS.

Flex fields and Oracle Alert are the only features or products that require the custom tables to be registered in Oracle Applications (Application Object Library) before they can be used.

Oracle provides AD_DD API for registering table (and their columns) in AOL.

Note: The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need not register views.

The list of procedures (AD_DD)

What is the difference between API and interface?

An API is nothing but “Application Programming Interface”.

API – it’s validating the record(s) and inserting directly into base tables. (if any error occurs, it’s just thrown an error at run time.)

Interface – Loads the data through interface table(s) and error-out record(s) histories' available in the same interface tables or provided error tables.

(The below image can help you understands easier way.)

RANK(), DENSE_RANK(), PARTITION BY ...

1. How to get the N th Max value?
    (Everybody knows this question in another form - query for the 2nd max salary)

If N = 2

SELECT MAX (sal)
FROM emp
WHERE sal < (SELECT MAX (sal)
FROM emp);
 
If N = 4 / 5 or n ...
It’s an easily achievable using by DENSE_RANK function.
 
SELECT empno, ename, sal
FROM (SELECT empno, ename, sal, DENSE_RANK () OVER (ORDER BY sal DESC NULLS LAST) top_most
FROM emp)

WHERE top_most = 4;

2. What is the difference between RANK and DENSE_RANK function?

       (For better understanding; check the below queries and outputs.)

SELECT * FROM emp;

SELECT empno, ename, sal, RANK () OVER (ORDER BY sal DESC NULLS LAST) RANK, DENSE_RANK () OVER (ORDER BY sal DESC NULLS LAST) DENSE_RANK

FROM emp;

3. Example for PARTITION BY

SELECT ename, sal, RANK () OVER (ORDER BY sal DESC NULLS LAST) RANK, DENSE_RANK () OVER (ORDER BY sal DESC NULLS LAST) DENSE_RANK, deptno, DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) deptno_rank
FROM emp
ORDER BY deptno, deptno_rank

Pay on Receipt(s) in Oracle Apps R12

Pay on Receipt(s) in Oracle Apps R12
POR (Pay on Receipt) is a feature in P2P cycle. Its helping to generate the invoice automatically when you receive the goods.

Pre-Requisites
1. Supplier site should be enabled the following flags. (PAY and PURCHASING)
2. "Pay on" field should have a value "Receipt". The invoice summary level should also have the value of "Receipt".
3. In Purchase Order; Terms window "Pay On" field value should be "Receipt".

1. Supplier Creation
Navigation (Purchasing Super User > Supply Base > Suppliers > Create Supplier)
Name    : SUPPLIER_1
Type     : Standard
Country : United States

2. Supplier Site Creation (Address Book)
Enter valid address and check the PURCHASING & PAYMENT flags.

3. Purchase Order Creations
** For an example, I am creating the Blanket PO. (for an easy process/testing purpose, we can create multiple requisitions against this PO.)

(Approval Notification)

4. Just ensure the Approved Supplier list. (Supplier, Site and Item) If the combination is not present please create the same.

5. Define the Sourcing Rule and assign the same into "Assignment Set"

6. Create the PO Requisitions for the item as we mentioned in PO. (Complete the approval and Release process.)

Inventory Responsibility >> Receipts

Purchasing Responsibility >> Requests >> Submit Request ("Pay On Receipt Autoinvoice")

It's automatically submits the "Payables Open Interface Import". Verify the invoice details ...