Oracle Applications & Fusion Blog

Transaction flexfields for AutoInvoice in Oracle Receivables

2013-05-15T19:38:00.000-07:00

Transaction flexfields

Transaction flexfields are descriptive flexfields that AutoInvoice uses to uniquely identify transaction lines.The definition of these flexfields is seeded and we should not modified
these, any change in the seeded definition of Transaction flexfields result in AutoInvoice fail.

Every Application Module that interact with Oracle receivable has it's own definition Transaction flexfields , based on what information it is passing to AR.

like In Service Contracts R12i there are ten transaction flexfield segments defined ( as you can see in the screen shot) .These identify values that are passed to AR by the Service Contracts Main Billing program.
For Order Management there are 11 Segments and for Claims there are 7 etc.

In case we are integrating any 3rd party or legacy system to AR , best practice is to define a New definition of Transaction flexfields for Legacy system.
If you import transactions with AutoInvoice from a legacy system, you
can define a new context for the Line Transaction Flexfield to distinguish these
transactions from transactions that originated in Oracle applications.

How to access the definition of the Transaction flexfields -
Login to Oracle Application using the System Administrator responsibility complete the following navigation:

Application > FlexField > Descriptive > Segments.

Select "Oracle Receivables" as the Application and "Line Transaction Flexfield" for the Title and execute the find.

Once you execute the Find , System will display all the definition of the Transaction flexfields

Scroll to the Any Context like OKS CONTRACTS , ORDER MANAGEMENT ,CLAIMS etc and system will display the definition of transaction flexfields.
In my example I scroll down to Context = "OKS CONTRACTS" . You should see the following data for the ten segments:

Number Name / Window Prompt Column
------ ----------------------- --------------------------
1 CONTRACT_NUMBER INTERFACE_LINE_ATTRIBUTE1
2 CONTRACT_MODIFIER INTERFACE_LINE_ATTRIBUTE2
3 INSTANCE_NO INTERFACE_LINE_ATTRIBUTE3
4 BILLED_FROM INTERFACE_LINE_ATTRIBUTE4
5 BILLED_TO INTERFACE_LINE_ATTRIBUTE5
6 AMOUNT INTERFACE_LINE_ATTRIBUTE6
7 BILLED_FROM_DATE INTERFACE_LINE_ATTRIBUTE7
8 START_DATE INTERFACE_LINE_ATTRIBUTE8
9 LINE_TYPE INTERFACE_LINE_ATTRIBUTE9
10 BILL_INSTALLMENT_NUMBER INTERFACE_LINE_ATTRIBUTE10

Key Concurrent Program tables

2013-05-13T22:36:00.002-07:00

Below is list of Key Concurrent Program tables

FND_CONCURRENT_REQUESTS - Details of user requests , including status, start date, and completion date.

FND_CONCURRENT_PROGRAMS - Details of concurrent programs, including execution methods , whether the program is constrained , and whether it must be run alone

FND_CONCURRENT_PROCESSES - Cross - references between concurrent requests and queue , and a history of concurrent manager processes.

FND_CONCURRENT_QUEUES - Information about each of the concurrent manager Queues.

Concurrent Program Life cycle

2013-05-11T13:36:00.000-07:00

Concurrent Program Life cycle -

Following all are stages of Concurrent Program (Oracle Applications).

Oracle E-Business Suite Architecture

2013-05-10T21:37:00.000-07:00

The Oracle E-Business Suite Architecture is a framework for multi-tiered, distributed computing that supports Oracle E-Business Suite products. In this model, various servers or services are distributed among three levels, or tiers.

A server (or services) is a process or group of processes that runs on a single machine and provides a particular functionality. For example, Web services process HTTP requests, and Forms services process requests for activities related to Oracle Forms. The Concurrent Processing server supports data-intensive programs that run in the background.

A tier is a logical grouping of services, potentially spread across more than one physical machine.
The three-tier architecture that comprises an Oracle E-Business Suite installation is made up of

Read More >>

Oracle SalesOrder Line Stuck with Deferred Status

2013-05-10T15:37:00.000-07:00

Many a time I have heard from my clients that Sales Order line stuck at the DEFERRED status and is not progress by the workflow background process for hours n days..

My Initial reaction is "check if these lines are in DEFERRED status because of valid reason like

they are part of ATO/PTO MODEL
they are part of FULFILLMENT set.

But if lines are not part of Set /Model then for sure workflow for these lines stuck in the DEFERRED status because of corruption of DEFERRED workflow Queue.

Oracle Support most of time advised to REBUILD the DEFERRED workflow queue , and scripts for same available at metalink.oracle.com and believe me that is the best and permanent solution ( though it is little time consuming).

In case you are looking for some quick fix to progress critical orders then you can "RETRY" the activity that stuck at the DEFERRED status and majority of the time doing RETRY on the activity progress the workflow .

NOTE - As I have said earlier there could be a valid reason that lines stuck at the DEFERRED workflow activity in that case RETRY is not good option , so before you look for RETRY make sure that you have analyzed your data well.

High Level Overview of return with replacement flow in Oracle Order Management.

2013-05-06T00:55:00.001-07:00

This YouTube presentation is about High Level Overview of return with replacement flow in Oracle Order Management.Here I talked about
#1.Return Flow
#2.Seeded RMA workflow in oracle Order Management
#3.How we can perform return with replacement with Seeded flow ( 2 step Process)
#4.How we can perform return with replacement with Customized RMA flow ( 1 step Process).

For technical details about how to create Outbound line ( with OR_ORDER_PUB) as soon as we received the goods in Inventorym refer my posts on OE_ORDER_PUB OR send me email at sgupta@bizinsightinc.com or leave message here .

Oracle Optimizer - Some Interesting Insight

2013-04-30T18:04:00.002-07:00

This is Short Post about

what is Oracle Optimizer .
How it works
How we can manipulate optimizer for session or Individual Query

A SQL statement can be executed in many different ways, such as

full table scans,
index scans,
nested loops, and
hash joins.

What is Query optimizer - The query optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query.

Note:The optimizer might not make the same decisions from one version of Oracle to the next. In recent versions, the optimizer might make different decisions, because better information is available.

BI Publisher Reports for Oracle Applications

2013-04-29T01:45:00.002-07:00

BI Publisher Reports for Oracle Applications

Query to get Interesting details about tables

2013-04-26T02:19:00.001-07:00

Query to get Interesting details about tables

select table_name, tablespace_name, num_rows, blocks,
round((blocks *
(select value from v$parameter where name = 'db_block_size')
)/1000000,1)||'M' as SIZEB
from dba_tables
where table_name in
('OE_ORDER_LINES_ALL',
'OE_ORDER_HEADERS_ALL',
'OE_PRICE_ADJUSTMENTS',
'OE_PROCESSING_MSGS',
'OE_PROCESSING_MSGS_TL',
'WSH_EXCEPTIONS',
'WF_ITEM_ACTIVITY_STATUSES',
'WF_PROCESS_ACTIVITIES',
'WF_ITEMS')
order by 1;

RMA flow for Drop Ship Orders in Oracle Application.-1

2013-04-20T00:18:00.002-07:00

RMA flow for Drop Ship Orders in Oracle Application.-1

This Presentation is 2 Part , please check part 2 too

RMA flow for Drop Ship Orders in Oracle Application.-2

2013-04-20T00:17:00.003-07:00

RMA flow for Drop Ship Orders in Oracle Application.-2

This is 2 part presentation , check part -1 at Part-1

How to Define and Progress KIT in Oracle Order Management.

2013-04-18T04:33:00.003-07:00

This youtube Post is about How to Define and Progress KIT in Oracle Order Management.

Adding lines in a Closed Order to fulfill business Requirement

2013-04-09T03:20:00.002-07:00

Recently someone asked
---
They have order which have 5 lines, 2 lines we shipped and invoiced successfully , remaining 3 lines accidently cancelled and order is also closed.

Now Business has need to Ship and fulfill those 3 lines that got cancelled accidently, business don't want to create New Order.

Solution - Though Not supported by Oracle , reason - we need to do an update on OE_ORDER_HEADERS_ALL table.

OPTION # 1

Once an Order is Closed it is not advisable to open the Order because Work flow is closed and open Flag = N, so good idea is to let this order closed and create a New Order.

OPTION # 2

Open the Order by setting open-flag = Y , but do not do any thing with workflow , let it be close ,Now add new lines to the Order , and then progress the newly created lines, at any moment of time , please not try to open the lines that are accidently closed , believe me that will be very messy .

Option #2 is advisable only and only if you don't have header level invoicing , but if you have header level invoicing then do not try to open the Order header .

Once New lines are shipped and close we need to set the OPEN_FLAG= N at the Order Header Level manually again , as system will not close the Order header ( reason , workflow is already closed).

Inter company transaction flow types.

2013-04-08T01:08:00.002-07:00

There are two Inter company transaction flow types.

Shipping and
Procuring.

A shipping flow is setup between two operating units of type shipping. You use the shipping flow when the shipping operating unit differs from the selling operating unit on the shipping document.

A procuring flow is set up between two operating units of type procuring when the receiving operating unit differs from the procuring operating unit on the receiving document. This type of flow indicates that the operating unit that created the PO differs from the operating unit that receives the PO.

Command to check the architecture of your Window Operating System

2013-04-01T17:49:00.000-07:00

Command to check the architecture of your Window Operating System

wmic os get osarchitecture

Query to get Item Categories

2013-03-29T00:43:00.002-07:00

Query to get Item Categories

SELECT MCB.*
    FROM
        MTL_CATEGORY_SETS_TL CST,
        MTL_ITEM_CATEGORIES MIC,
        MTL_CATEGORIES_B MCB
    WHERE MIC.CATEGORY_ID = MCB.CATEGORY_ID
    AND MIC.CATEGORY_SET_ID = CST.CATEGORY_SET_ID
    AND MIC.ORGANIZATION_ID = 0
    AND CST.CATEGORY_SET_NAME = '&item_category_name'
   AND MIC.INVENTORY_ITEM_ID = DL.INVENTORY_ITEM_ID;

Query to Join Order Management Tables with Oracle Quoting tables

2013-03-29T00:33:00.000-07:00

Query to Join Order Management Tables with Oracle Quoting tables

SELECT ooh.header_id
         , ooh.order_number
         , ool.line_Id
         , ool.ship_from_org_id organization_id
         , ool.inventory_item_id
         , ool.ordered_quantity
         , ool.order_quantity_uom
    , ool.line_type_id xx_line_type_id
    , qtl.order_line_type_id
   , ool.request_date
    , ool.creation_date
    , ool.ship_to_org_id
    , ool.sold_to_org_id
      , ool.subinventory
Read More >>

Source Code to create customer contact with TCA api

2013-03-29T00:19:00.000-07:00

Code to create customer contact with TCA api

CREATE OR REPLACE PACKAGE BODY TCA001 AS
PROCEDURE CUST_CONTACT_RELATIONSHIP --(p_party_parent_id hz_parties.party_id%TYPE,
IS
p_org_contact_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
x_org_contact_id NUMBER;
x_party_rel_id NUMBER;
x_party_id NUMBER;
x_party_number VARCHAR2(2000);
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
            p_org_contact_rec.decision_maker_flag := 'Y';
            p_org_contact_rec.created_by_module             := 'HZ_CPUI';--p_cust_rec.created_by_module;
            p_org_contact_rec.party_rel_rec.subject_id         := 324217;--p_party_id;
            p_org_contact_rec.party_rel_rec.subject_type         := 'PERSON';
            p_org_contact_rec.party_rel_rec.subject_table_name     := 'HZ_PARTIES';
Read More >>

Query to Get TOP 5 Item Ordered by Customer

2013-03-25T17:47:00.002-07:00

Below is query to Get TOP 5 Item Ordered by Customer in last 250 days

select ordered_item,Total_Qty_Ordered, sold_to_org_id
FROM (
SELECT l.ordered_item,SUM(l.ordered_quantity) Total_Qty_Ordered, l.sold_to_org_id,RANK() OVER ( ORDER BY SUM(l.ordered_quantity) DESC) "RANK"
from oe_order_lines_all l
where 1=1--h.header_id = l.header_id
and l.creation_date between sysdate-250 and sysdate
and l.sold_to_org_id = &Enter_Cust_account_id
group by l.ordered_item,l.sold_to_org_id
)
where RANK <= 5
order by RANK ASC

High level setups necessary to enable MOAC

2013-03-22T13:00:00.000-07:00

Below are the Flow Chart about the High level setups necessary to enable MOAC, for more information on MOAC, refer my post of same subject .

<<You can search for my posts related to MOAC in above search window >>

Steps to Process data in Counter Reading Open Interface - 2

2013-03-21T13:25:00.000-07:00

In continuation to discussion about how to import the Counter Reading in Oracle Application using Interface table , Steps to Process data in Counter Reading Open Interface - 1 , in this Post I will highlight the APIs that we can ti import the Counter Reading in Oracle application interface.

CSI_COUNTER_PUB
CSI_COUNTER_READINGS_PUB

Apart from importing the counter reading , many a time we have requirement to update the values in counter reading , this can be achieved by means of Open Interface by populating table CSI_CTR_READINGS_INTERFACE or by means of above 2 APIs.

Apart from that if we have any requirement JUST to update the ATTRIBUTES columns for counters then we have one additional option along with
Read More >>

Steps to Process data in Counter Reading Open Interface - 1

2013-03-20T23:49:00.002-07:00

In this Post I will explain what all are the Steps to process the Counter Reading data from the Counter Reading Open interface table.

With Counter Readings Open Interface we have the ability to import significant volumes of data into counter readings and counter property readings tables.The data may be from multiple source including external legacy systems.

Step by Step details about how to process the data from the Counter Reading Interface table .

Load data into the counter readings interface tables.SI_CTR_READINGS_INTERFACE , Process Status Column in this table can has any of the below 3 values

P - Processed
E - Error
R - Ready for processing.

Run the Install Base - Counter Readings Open Interface concurrent program that

reads, validates, and posts the data from the interface tables into the counter

readings in Oracle Install Base.
STEPS to Run the Concurrent Program
-----------------------
Navigate to
1. (R) Install Base Administrator
2. (N) Others > Requests
3. Single Request > OK
4. Search for 'Install Base - Counter Readings Open Interface'
5. Batch Name = specific batch name
6. From Transaction Date = SYSDATE
7. To Transaction Date = SYSDATE
8. Purge Processed Records = Yes
9. Error Reprocess Options = NULL
10. OK
11. Submit
Please note that as of now System will let you process only one batch at a time ,it will not let you process multiple batches together << We at Bizinsight Consulting have a Custom Solution to process multiple batches together , in case you need additional information about this Multi batches process solution , feel free to contact us at sgupta@bizinsightinc.com >>.
If you want the processed records to be cleared from the interface tables after
processing, select Yes for Purge Processed Records.

How to define and progress Service Items in Oracle Order Management - 1

2013-03-15T19:37:00.002-07:00

This is First Post on series "How to define and progress Service Items in Oracle Order Management ", stay tune in for more post on same topic.

Order Management enables you to order services from its Sales Order workbench. You

can order services for product items currently being ordered, i.e. immediate services, or

you can order services for already installed product items or delayed services.

Order Management enables you to:

order service lines along with the product lines.
import service lines and service orders using order import.
perform applicable operations that the application applies to any other order, including billing.
enter service for all serviceable options in a configuration once.

Order Management I workflow driven , so as the case for the service line too.

Order Management enables you to utilize Oracle Workflow to manage your service lines with service item types. Service lines are non-schedulable and non-shippable, to implement service items ,assign a Bill Only workflow process that does not include these two functions ( as Shown below)

How to Assign Work flow to line type - You can Assign work flow to Order line type from Oracle Workflow Assignments window in Order Transaction type UI.

Once bill only workflow assigned to Service line, any transaction for Service line will use the same work flow. A s soon as you book the Order , Service line progress to Invoicing activity , as there are no scheduling or Shipping activities involves( and for how to delay service line from invoicing till product line ship please refer below Shipping Section , and regarding how service line interact with the Service contract , will explain that in coming posts)

Note - When you apply duration-related changes to the service order line, Order Management

automatically applies those changes to the associated service order lines in the configuration.

Shipping

Order Management, Shipping, and Oracle Service provide you with the ability to synchronize the start of a service program with the shipment of an associated product. You can define the Service Starting Delay when you define serviceable products in Oracle Inventory. The Service Starting Delay represents the time in days an included warranty is offset to commence after the shipment date.

The start date of the support service is the ship date plus the starting delay. The end date is calculated by adding the duration to the start date of the support service. This is applicable for included

warranties and not extended warranties (service programs).

Configuration Validation Hold

2013-03-05T01:00:00.000-08:00

Configuration Validation Hold
In Oracle Order management , if we enter an ATO Model line to a BOOKED sales order , Standard out of box functionality will Apply Configuration Validation Hold on Model line ( If Oracle Configurator is installed) to prevent further processing of the line.

Some of the changes in R12i eAM as compared to R11i

2013-02-16T23:57:00.001-08:00

In this post I have highlight few of the technical Changes that oracle has introduced in R12i

MTL_SERIAL_NUMBERSof R11i has replaced with CSI_ITEM_INSTANCES in R12i.
EAM_METERS ( R11i) has replaced with CSI_COUNTERS and CSI_COUNTERS_B , CSI_COUNTER_READING in R12i.
in r12i we have IB data Model , data from eAM interfaced to Oracle Install Base (IB) , SFM used by IB to process eAM transaction.
In R11i all asset that need to manage should be defined in Maintenance Organization , where as in R12i Maintenance Org can create Work order on Asset that define in Maintenance Org or in any other aAM enabled org.
in R12i Asset number has globally unique where as in R11i we have duplicate asset number in different organization.
In R12i we have status like START_DATE/END_DATE where as in R11i we have status like active/de-active.
In R12i we have "Audit" feature , where as in R11i we don't have "Audit" feature.