WFENG_COMMIT_INSIDE error...

One of the reason for WFENG_COMMIT_INSIDE error is workflow engine detect the commit in the program called from the worlflow process.

Workflow user guide clearly says that we should not put commit or rollback in API code called from workflow process.

If your workflow activity stuck , then you can try to progress the workflow by either RETRY (if you want to execute the underliying code ) or SKIP (if you want to skip the inderlying code).API for that is WF_ENGINE.HANDLE_ERROR.

For more Info on Workflow , please refer
http://eoracleapps.blogspot.com/2008/07/oracle-workflow-concept.html

http://eoracleapps.blogspot.com/2009/04/how-to-progress-stuck-order-lines-in.html

To refer how to design a Master Detail Workflow
http://eoracleapps.blogspot.com/2008/07/designing-simple-masterdeatil-oracle.html

Designing Simple Master/Deatil Oracle workflow

When you spawn a detail process from a master process in Oracle Workflow, you are
in effect creating a separate process with its own unique item type and item key. Oracle Workflow engine has provided an API “SetItemParent API” to define a master/detail relationship between the two processess. This API should be called after we called the CreateProcess API for the DETAIL process, BUT before a call has made to Startprocess API for detail process.

In the workflow we have two activities (one will be define in master and other in detail) to coordinate flow between master and detail process. As explained below,
One activity lets you pause a process and the other signals the halted process to continue. To use these activities, you place one activity in the master process and the other in each detail process.


These activities are

1. Wait for Flow Activity
2. Continue Flow Activity

Both activities contain two activity attributes that you use to identify the coordinating
activity in the other process(es).

Wait for Flow Activity – This activity can be in master /Detail and will pause the flow until the corresponding detail/master process completes for this activity. pl/sql procedure for Wait for Flow activity is WF_STANDARD.WAITFORFLOW.
Based on our business requirements we can have any number of Wait for Flow activities in the same workflow process.One very Important thing that should be keep in consideration when we design Mater /Detail relationship, If we have ONE master and ONE detail process, but MASTER process wait for the DETAIL process at multiple stages of its (master) flow, in that case master process should have separate Wait for Flow activity node for each time it wait for detail process. Similarly DETAIL process should have separate Continue Flow Activity corresponding to each Wait for Flow activity in Master process.


Wait for Flow activity contains 2 activity level attributes

Continuation Flow - specify whether this activity is waiting for a corresponding
"Master" or "Detail" process to complete.

Continuation Activity - specify the label of the activity node that must complete in
the corresponding process before the current process continues. The default value isCONTINUEFLOW.

Continue Flow Activity

Use this activity to mark the position in the corresponding detail or master process
where, upon completion, you want the halted process to continue. This activity calls a
PL/SQL procedure named WF_STANDARD.CONTINUEFLOW.
When a Continue Flow activity is executed, the WF_STANDARD.CONTINUEFLOW
procedure checks whether the corresponding Wait for Flow activity is associated with
any other processes that have not yet completed their Continue Flow activities. If so, the
waiting process keeps waiting for those other processes. If the Wait for Flow activity
is not waiting for any other processes, then the WF_STANDARD.CONTINUEFLOW
procedure completes the Wait for Flow activity so that the process that was waiting
now continues to the next activity.


Activity Attributes
The Continue Flow activity contains two activity attributes:

1. Waiting Flow - specify whether the halted process that is waiting for this activity to
   complete is a "Master" or "Detail" flow.
2. Waiting Activity - specify the label of the activity node in the halted process that
   is waiting for this activity to complete.

Below is example for how to call the master/detail workflow from pl/sql


Master Workflow (Calling Detail Flow with "Waiting Activity Label = Name of Child Activity waiting for this master to complete)








Master Workflow (received call from Detail activity with Continutation Activity Label = Name of Child Activity, that this Master is waiting in Order to progress further.)

Detail Flow (Received Call from master flow with "Continutation Activity Label = Name of Parent Activity that this Detail is waiting in Order to progress further)

Detail Workflow (Calling master flow with "Waiting Activity Label = Name of Parent Activity Waiting for this Child activity to complete)

declare
l_ckey varchar2(10) :='615';
l_pkey varchar2(10) :=l_key; --(can be any value)
l_ptype varchar2(10) :='SR_TYPE'; -- Parent type
l_ctype varchar2(10) :=’SR_INTER’; -- Child type
l_pprocess varchar2(100) := 'SR_PROCESS'; -- Parent process
l_cprocess varchar2(100) := 'SR_INT_PROCESS'; -- Child process
l_url varchar2(300);
begin
wf_engine.createprocess(l_ptype,l_pkey, l_pprocess,NULL,NULL);
wf_engine.createprocess(l_ctype,l_pkey, , l_cprocess,NULL,NULL);
wf_engine.SetItemParent(l_ctype,l_ckey,l_ptype,l_pkey,NULL,TRUE);
wf_engine.startprocess(l_ptype,l_pkey);
wf_engine.startprocess(l_ctype,l_ckey);

l_url:=wf_engine.getitemAttrText(l_ptype,l_pkey,'RECEIVER',TRUE);
dbms_output.put_line(l_url);

end;

Oracle Technologies.: How to Create LOV in JSF Page (Java /SOA)

Oracle Technologies.: How to Create LOV in JSF Page (Java /SOA)

ORA-01403: no data found R11i , R12i

In Oracle Apps there are many reason for this Error

1.If you are using ship set and anytime during line processing user has removed the ship set, there is possibility that Ship_set has been removed from Order lines , but it still exists in the WSH Delivery tables (data corruption).Possible fix is Set the value of ship_set_id to NULL
<>

2.SHIPPING_INTERFACED_FLAG = ‘Y’ in OE_ORDER_LINES_ALL , but there is no corresponding data in WSH_DELIVERY_DETAILS.

3.when the scheduled ship date for the newly added configured option class is not matching with the Schedule ship date for rest of the lines already in the MODEL system throws error "No Data...".

APP-FND-01564. Oracle Error 1403 in fdxwho (R11i, R12i)

Recently one of my customer reported error "APP-FND-01564. Oracle Error 1403 in fdxwho' while checking record history.Initially I was like what is this all about.
On Further Investigation I found that If last_updated_by/created_by column (column to capture the user_id) has value that doen't exists in FND_USER table , then if we go to Help>record History , Oracle Apps will throw this error.

If you ask me , I say nothing much to worry about.But if you want update this column with a valid value only if it is a custom table .For Oracle Standard ERP table , it is always good to contact Oracle Support.

UPDATE XXXX_TABLE
set last_updated_by /created_by = Valid value
where xxx = Y

How to Setup transaction type in Order Management R11i

How to Setup transaction type in Order Management R11i

UPDATE - in R12 it is more like same

There are tons of document available on this topic,but here in this blog I try to make it further simple and easy by posting screen shot of each step.

Step1:Profile Option Setup
Select the Responsibility Application Developer and Navigate System Profile Navigate to >Profile>System


Open the profile Form and Query for User Profile Name ‘Sequential Numbering’



Sql validation Item should hav following Text
SQL="SELECT MEANING \"Sequential Numbering\",lookup_code
Into :visible_option_value,:profile_option_value
from fnd_lookups
where lookup_type='SEQ_NUMBERS'" COLUMN="\"Sequential Numbering\"(*)"'


Go to System Administrator responsibility , and select the following check boxes
Site - Visible , Updatable
Application - Visible , Updatable
Responsibility - Visible , Updatable
(Above setup is not required if profile is already Visible and Updatable.)


Step2: Profile value Setup

1. Select the Responsibility SysAdmin and Navigate System Profile OptionNavigate to >Profile>System
2. Query (From Find Window) for profile Option Sequential Numbering.
   
   
   Sequential Numbering should set to a value from LOV , In my case I have selected ‘Partially Used’ at Site Level.

Step3: Order Type SetupNavigate to Order Management Responsibility where you want to define the New Order Type
(In My Case Order Management Super User, Vision Services (USA) and I am defining Order Type iStore Order Type)

Navigate to the Transaction Type and select ‘Define’
Navigator > Transaction Types > Define

1. Define the New Order Type ‘iStore Order Type’ .
2. Attach the Default Order Line Type and default Pricelist (if required) in Main Tab.
3. Attach the Order Workflow
4. Assign the Line Flows (From Button Assign Line Flows).
5. Enter the Required Details for Shipping and Finance Tabs
   
   

Step4: Define the Document Sequence for the Order Type (Define in Step3)
Navigate to the Documents and Select the ‘Define’
Navigator >Documents>DefineDefine the Document Sequence for iStore Order Type

Name: iStore Order Type (LOV)
Application: Oracle Order Management (LOV)
From Date: Enter your From Effective Date
To Date: usually leave that Null
Type: Automatic (Drop Down List)
Message: Select this Check box
Initial Value: Enter the Number from where you want to start Order No.
Set the Initial Value to any value from where you want to start the Order Numbers.



Step5: Assign the Document Sequence for the Order Type (Define in Step4)
Navigate to Assign Document Sequence
Navigator > Documents>Assign

Go to Document Tab and Enter
Application: Oracle Order Management(LOV)
Category: iStore Order Type(LOV)
Set of Books: Vision Services (USA) (LOV)
Method: Null



Go to Assignment Tab and Enter

Application: Oracle Order Management (LOV)
Category: iStore Order Type (LOV)
Start Date: Enter Start Date
Sequence: iStore Order Type (LOV)


Now you can create Order with this Transaction type.
There is not much difference between R11i and R12i.In R12i we have option to specify the Operating Unit. For Multi Org Access control in R12 , please refre my blog http://eoracleapps.blogspot.com/2008/06/r12-multi-org-access-control.html

RMA stuck at APPROVAL_RETURN_ORDER_NTFA (Oracle R11i)

A very common issue with RMA is , Order header workflow stuck at APPROVAL_RETURN_ORDER_NTF activity. Most of time root cause of this issue is Profile Option “OM: Notification Approver” is missing. Setting up this profile option at the SITE Level will prevent this error for NEW orders, but that will not fix the orders that are already in process.A data fix has required to skip workflow activity APPROVAL_RETURN_ORDER_NTF.
Best option for you is contact Oracle Support.

Oracle External Table

Oracle External Tables provide great deal to upload data into Oracle table from a file without executing the Sql Loader.
What is External Table - Definition of External table from Oracle Database manual An external table load creates an external table for data in a data file and executes INSERT statements to insert the data from the data file into the target table.
There are two advantages of using external table loads over conventional path and direct path loads:
An external table load attempts to load data files in parallel. If a data file is big enough, it will attempt to load that file in parallel.
An external table load allows modification of the data being loaded by using SQL functions and PL/SQL functions as part of the INSERT statement that is used to create the external table.

Disadvantages of External Table.
Till R11g there is no option to execute DML against an external table. External tables supports SELECT only.

Steps for External table.
1. Create database directory and map it with your local PC directory. You need to have Admin Privileged to create Database directory.
2.Grant read/write access of database directory to user.
3.Create External table with DEFAULT DIRECTORY as Database directory.

Step #1
CREATE OR REPLACE DIRECTORY load_dir AS 'C:\myexternaltable';

GRANT CREATE ANY DIRECTORY to user_name
Grant DROP ANY DIRECTORY to user_name

Step #2
GRANT READ ON DIRECTORY load_dir TO user_name;
GRANT WRITE ON DIRECTORY load_dir TO user_name;


CREATE TABLE BG_STATEMENT_LINES_EXT
(
BG0 VARCHAR2(150),
BG1 VARCHAR(150),
BG2 VARCHAR2(150),
BG3 VARCHAR2(150),
BG4 VARCHAR2(150),
BG5 VARCHAR2(150))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY LOAD_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
badfile load_dir:'bst_lns.bad'
logfile load_dir:'bst_lns.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
BG0,
BG1,
BG2,
BG3,
BG4,
BG5
))
LOCATION
(
LOAD_DIR: 'file_name.csv')
)
REJECT LIMIT UNLIMITED
;

In my example I have mapped my PC's directory 'C:\myexternaltable' to database directory load_dir. While defining the external table, I have added the clause "DEFAULT DIRECTORY LOAD_DIR" to map External Table with database directory.

Data Processing

Create and Grant Database directory and External Table as explain above.
Get your data file in the same format as of External table.
Save your data file into your PC's directory that has mapped to database directory , make sure filename is same as defined "LOAD_DIR: 'file_name.csv'"
On Save, data from file will Import to External table.

Oracle External Tables provide great deal to Upload data into Oracle table from a file without executing the Sql Loader.What is External Table - Definition of External table from Oracle Database manual

An external table load creates an external table for data in a datafile and executes INSERT statements to insert the data from the datafile into the target table.
There are two advantages of using external table loads over conventional path and direct path loads:
An external table load attempts to load datafiles in parallel. If a datafile is big enough, it will attempt to load that file in parallel.
An external table load allows modification of the data being loaded by using SQL functions and PL/SQL functions as part of the INSERT statement that is used to create the external table.Steps that Required for External table.
Create database directory and map it with your local PC directory. You need to have Admin Privileged to create Database directory.
Grant read/write access of database directory to your user who want to use External table.
Create External table with DEFAULT DIRECTORY as Database directory.Step #1CREATE OR REPLACE DIRECTORY load_dir AS 'C:\myexternaltable'; GRANT CREATE ANY DIRECTORY to user_nameGrant DROP ANY DIRECTORY to user_nameStep #2GRANT READ ON DIRECTORY load_dir TO user_name; GRANT WRITE ON DIRECTORY load_dir TO user_name; Step #3CREATE TABLE BG_STATEMENT_LINES_EXT( BG0 VARCHAR2(150), BG1 VARCHAR(150), BG2 VARCHAR2(150), BG3 VARCHAR2(150), BG4 VARCHAR2(150) )ORGANIZATION EXTERNAL( TYPE ORACLE_LOADER DEFAULT DIRECTORY LOAD_DIR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE badfile load_dir:'bst_lns.bad' logfile load_dir:'bst_lns.log' FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ( BG0, BG1, BG2, BG3, BG4, BG5 ) ) LOCATION ( LOAD_DIR: 'Invoices.csv' ))REJECT LIMIT UNLIMITED;In My example I have Mapped my PC's directory 'C:\myexternaltable' to Database directory load_dir.While defining the external Table , I added the clause "DEFAULT DIRECTORY LOAD_DIR" to Map External Table with Database Directory.How to Process data with External table.
Create and Grant Database directory and External Table as explain Above.
Get your data file in the same format as of External table.
Save your data file into your PC's directory that has mapped to Database directory.
On Save , Data from file will Import to External table.Advantages -Very Easy Friendly , will till Training even we can engage business user to handle it's Processing.Less Maintenance Cost .Disadvantages of External Table.
Till R11g there is no option to execute DML against an external table.External tables supports SELECT only.

Oracle R12 Standard COGS API Flow (COGS derivation based on Order Type).

OE_Flex_Cogs_Pub.Start_process

 FND_FLEX_WORKFLOW.INITIALIZE    

wf_engine.CreateProcess  

FND_FLEX_WORKFLOW.GENERATE  

wf_engine.StartProcess    
FND_FLEX_WORKFLOW_APIS.START_GENERATION    
OE_Flex_Cogs_Pub.GET_ORDER_TYPE_DERIVED    
FND_FLEX_WORKFLOW_APIS.COPY_FROM_COMBINATION   
 FND_FLEX_WORKFLOW_APIS.VALIDATE_COMBINATION    
FND_FLEX_WORKFLOW_APIS.END_GENERATION

Blogged with the Flock Browser

Oracle Release COGS API Flows

Copy Order fail to copy Context Senstive DFF with Synchronize with reference Field checked

Recently I came across one of my customer who has requirement to Copy the Descriptive flex field on Order Header when creates a order by Action >Copy.
Sound very easy to me , but this specific requirement was not that easy as it sounds.Reason? let me explain.

customer has context sensitive Order Header DFF with the following context

1. EDI
2. Online
3. PkMS etc

and he want to maintain the follwing setup

1. Reference Fields = ORDER_SOURCE
2. Synchronize with reference Field = Checked

DFF Setup

Context Attributes


Global Attributes



as per them SETUP step 2 us a MUST for Importing Orders from different Sources and he was not willing to change it.(well ct. is King)
There was nothing wrong with the above setup , but when he copied the Order , the new copied order has values for the Global Attributes , but all the DFF attributes that were based on the Context appears NULL. Isn't that Strange , but no that not.

After doing copy order I have executed query on OE_ORDER_HEADERS_ALL ,and I have found that though the values appear NULL in DFF in Context sensitive DFF attributes , but these attributes has values in database fields.

On further Investigation I have found that , When we creates new order by Copying the existing Order (in my example existing order is Manul Order with ORDER SOURCE colum = "Online") system will Copy all the DFF Values to the newly created Order
In copied order
Context = "Online"
Attribute3 = Copied value from Old Order
Attribute4 = Copied value from Old Order
I have verified that after doing a simple backend query on OE_ORDER_HEADERS_ALL table.

But interestingly , if I open the Order Header DFF of newly copied Order , due to DFF
Set up "Synchronize with reference Field in DFF " , system will copy the ORDER_SOURCE (in this case it is "Copy" ) to the "Context" column of the DFF , and that results in NULL Values apppear for the Context senstive attribute Attribute3 and Attribute4 ( these attributes has value copied from the Original Order with Context = "Online") .Now If user press the Save button in DFF
Context = "Copy"
Attribute3 = NULL

Attribute4 = NULL
will be save in OE_ORDER_HEADERS_ALL table.

And this is very much the expected Behavious with the Setup "Synchronize with reference Field".Now If customer want to copy the values while doing the copy Order , he need to UNCHECK the "Synchronize with reference Field", But if that is not possible they need to do the Customization by means of

1. Database Trigger
   
2. Form Personalization.

NOTE - In My Example Attribute1 &2 are Global Attribute and Attribute3&4 are Context senstive Attribute.
.

Blogged with the Flock Browser

Oracle ADF Master Detail Page

In this blog I will provides details about ADF Master detail Page, plus how to drop graph UI on Page.

Test case – we have an application to capture the details from credit card statements and then prepare Monthly Budget and compare Actual Amount Vs Allocated Account for each Spending category, plus display Bar Chart for Allocated amount Vs Actual Amount for each Category.

In this blog I will explain how to design Master Detail Page , plus how to add graph Component.
Components for this example
Database table
budget_header
budget_lines.
budget_categories

Steps
1.Open Oracle Jdeveloper (Jdev11g).
2.Select New application.
3.Select the template Fusion Web Application (ADF) and Wizard will help you to create TWO projects.
A. Model
B. View Controller.

Model is for Data Control and ViewController for UI.

In Model Project
1. Design the Business Services by selecting categories > business tier >ADF Business components >“Business Components from table” and Wizard will help you to create Business Components..
2. Complete Database connection Setup.
3. Select the budget_header, budget_lines and budget_categories table and create Entity Object (EO), Updatable view (VO) and Application Module (AM) .
4. Define view links between these 3 Vos.
5. Creation of Application Model will exposed all the Vos to client, and they will appear under Data Control.
6. Save your Work.

In ViewController Project open adfc-config.xml and drag a JSF page component by dragging View from Component palette. Save your work

1. Open the page and save it as budget.jspx. (Please note that this is unbounded task flow).
2. From Component palette Drag Panel Splitter in page as shown in pic1.1 and add the Panel Accordion, Panel collection and panel tabbed to newly created Spitted panel.
3. Now Drag the Budget header, lines from data control to JSF Page and our page is ready, as shown below.

In Pic 1.1
Split area “Split1” shows Budget Headre
Split area “Split2.1” shows budget lines Detail in table (Read – Only).
Split area “Split2.2” shows budget lines Detail in Form.


Pic 1.2 shows the Graph for Actual Spending Amount Vs Allocated Amount for a category.


Pic 1.2

Both Pic 1.1 &1.2 shows the Master detail relationship between Budget Header/Lines.


Pic 1.3

As shown above Pic 1.3, ADF Control offer you built in function to select the column you want to Show or Hide, no coding has required for it.


As shown below Pic 1.4 , you can move the columns at run time without writing any code. In this example I have moved /draged Category Name column from last to First position.


Pic 1.4


ADF Provide another built in feature “Detach” as shown below in Pic 1.5, that will detach one particular Page Section (in my case detail table section ) and display it over the Main Page. It will be very helpful if there is large amount of data in Detail and you want to see it all.

Pic 1.5

As shown below in Pic 1.6, I am calling the Custom Menu Function to export table data to excel sheet. (Please refer my previous blog about how to do that)


Pic 1.6




Pic 1.7

How to Create LOV in JSF Page (Java /SOA)

In this blog I will explain in details how to create LOV on JSF Page.
Test case – In this example, I will create a VO on STATEMENT_LINES(BgStatementLinesView). One of attribute of this VO is Budget category_id.I will create another VO on BUDGET_CATEGORY(BgBudgetCategoryView) , this VO has attributes
1.Category ID
2.Category Name.
Finally I create LOV that will display category Name and category ID and attach that to the category_id attribute of BUDGET_LINES.
Steps
1.Open Oracle Jdeveloper (Jdev11g).
2.Select New application.
3.Select the template Fusion Web Application (ADF) and Wizard will help you to create TWO projects.
Model
View Controller.
Model is for Data Control and ViewController for UI.In Model Project Design the Business Services by selecting categories > business tier >ADF Business components >“Business Components from table”.
1. Complete Database connection Setup.

2. Select the BUDGET_LINES and BUDGET_CATEGORIES table and created Entity Object (EO). Then Create VO budgetlinesVOI and budgetcategoryVO finally create Application Module (AM)

3. Creation of Application Model will exposed our VO to client, and they will appear under Data Control.


4.Save your Work.

In ViewController Project open adfc-config.xml and drag a JSF page component by dragging View from Component palette. Save your work


1. Open the page and save it as budgetlov.jspx. (Please note that this is unbounded task flow).
2.Drag Statement lines VO (BgStatementLilnesView)from data control and drop on JSF pages as ADF Form.
3.Open BgStatementLilnesView.xml and Select View Accessor and then select ADD (+).
4. Add BudgetCategory VO (bgBudgetCategoryView1) to BudgetStatementLines VO by moving bugetcategory VO from “Available View Objects “ to “View Accessors” as shown in Pic1.1



4. Go to the attributes of the BgStatementLinesView.xml (STATEMENT LINES VO) select the attribute where you want attach LOV (in my case it is CategoryId).

5. Double click the attribute




6. Select List of Values (in Searach) and then Check the check box “Enable List of values”, it will highlight the category view “BgBudgetCategoryView1” , double click the category ID (because we want to build LOV on category ID , as category_id column exists in both the Vos).

7. Go to “Go to UI Hints” tab




8. Set Default List Type = Combo box with List of valies and Include Search region = All Queryable Attributes

9. If Include Search Region = “All Query able attribute” LOV will popup search block which allow us to search based on all the fields of category table


On the other hand , if we have requirement to allow user to query based on few attribute we need to define the view criteria.In my example I have added a restriction to allow user to do query based on 1.BudgetID
2.BudgetName
3.Comments
by defining view criteria
4.save and let’s go back to LOV again.

As we can see in above Pic while defing LOV definition we can see just defined view criteria that we have created in previous step.
Save and Run page again

Seach page on selecting the category ID attribute will allow us to Query only based on BudgetID, BudgetName and Comments.

Litte more Add on for this blog - If we change Default List Type to Choice List , JSF page will not dispaly serach Page any more , It will just list of value with category ID and category Name.

How to Skip/Retry Workflow.

Oracle workflow engine provide wf_engine API to SKIP/Retry workflow activity

Below is example how we do it in Oracle Order Management.
wf_engine.handleerror('OEOL',TO_CHAR(LINE_ID),activity_label,RETRY,NULL)

wf_engine.handleerror('OEOL',TO_CHAR(LINE_ID),activity_label,SKIP,NULL)

Oracle Apps Inventory Queries

--Reservation Qty for SKU

select SUM(mtr.reservation_quantity)
from
mtl_reservations mtr ,
oe_order_lines_all ool,
oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id
and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id
and mtr.inventory_item_id =1
and ooh.order_number = 316

--Reservation Qty for Order
select SUM(mtr.reservation_quantity)
from
mtl_reservations mtr ,
oe_order_lines_all ool,
oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id
and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id
and ooh.order_number = 316


--Order to HZ (Customer Location)
select ooh.ship_to_org_id,ooh.sold_to_org_id ,hp.party_name,hca.party_id,
hca.account_number,hcsu.site_use_code,hcsu.location,hcsu.primary_flag,
hcsu.bill_to_site_use_id,--hpsu.site_use_type,hps.party_site_number,hps.party_site_id,
hps.location_id,hpsu.primary_per_type
,hl.address1,hl.address2,hl.address3,hl.address4,hl.city,hl.state,hl.postal_code,hl.county
FROM
oe_order_headers_all ooh,
hz_cust_accounts_all hca,
hz_parties hp,hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_locations hl
where
ooh.sold_to_org_id = hca.cust_account_id
and hca.party_id = hp.party_id
and hca.party_id = hps.party_id
and hca.cust_account_id = hcas.cust_account_id
and hps.party_site_id = hcas.party_site_id
and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and hl.location_id = hps.location_id
and ooh.order_number = 351
order by hps.party_site_id

Why do ERP projects fail so often?

At its simplest level, ERP is a set of best practices for performing different duties in your company, including finance, manufacturing and the warehouse. To get the most from the software, you have to get people inside your company to adopt the work methods outlined in the software.

If the people in the different departments that will use ERP don't agree that the work methods embedded in the software are better than the ones they currently use, they will resist using the software or will want IT to change the software to match the ways they currently do things. This is where ERP projects break down. Political fights break out over how—or even whether—the software will be installed. IT gets bogged down in long, expensive customization efforts to modify the ERP software to fit with powerful business barons' wishes. Customizations make the software more unstable and harder to maintain when it finally does come to life.

The horror stories you hear in the press about ERP can usually be traced to the changes the company made in the core ERP software to fit its own work methods. Because ERP covers so much of what a business does, a failure in the software can bring a company to a halt, literally.

But IT can fix the bugs pretty quickly in most cases, and besides, few big companies can avoid customizing ERP in some fashion—every business is different and is bound to have unique work methods that a vendor cannot account for when developing its software. The mistake companies make is assuming that changing people's habits will be easier than customizing the software. It's not. Getting people inside your company to use the software to improve the ways they do their jobs is by far the harder challenge. If your company is resistant to change, then your ERP project is more likely to fail.

How to Customize COGS Workflow.

How to customized Standard Cost of Goods Account (COGS) workflow to derive COGS from Order Type.

Oracle has provided Standard COGS workflow to derive cost of goods sold (COGS) account from Inventory Item (defined in Shipping Organization).
If there is requirement to derived the COGS account based on the Order type , we need to customized the Standard workflow.

Follow the below steps to customized the workflow.
1.Open the standard workflow in workflow builder.
2.Copy the Standard Workflow "Generate Default Account (DEFAULT_ACCOUNT_GENERATION) " and name it XX_DEFAULT_ACCOUNT_GENERATION (custom_Generate Default Account ).
2.Remove the link between START and "Get CCID for a line".
3.Add New function "Get CCID from the Order Type ID(GET_ORDER_TYPE_DERIVED).
4.Make link between START and function GET_ORDER_TYPE_DERIVED.
5.Remove the function Get CCID for a line(GET_ITEM_DERIVED).
6.Connect the GET_ORDER_TYPE_DERIVED with Copy Values from Code Combination (FND_FLEX_COPY_FROM_COMB) for Result = "Success" and connect GET_ORDER_TYPE_DERIVED with Abort generating Code Combination(FND_FLEX_ABORT_GENERATION" for result ="Failure"
7.Verify the Workflow.
8.Save in database.
9.Test the complete Process from APPS.In R11 COGS workflow would be called during Interface Trip stop(ITS) , where as in R12 COGS workflow would be called from CLOSE line Subprocess.

R12 Multi-Org Access Control

Release 12 came with a new feature of accessing the multiple Operating Units with the single responsibility. In R12 they call it Multi-Org Access Control (MOAC).

Multi-Org Access Control (MOAC) enables companies that have implemented a Shared Services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications responsibility.

Features of MOAC
Access multiple operating units within a single application responsibility
Perform tasks for and across multiple operating units:
1. Set-up controls, Negotiate sales agreements
2. Enter quotes, orders and returns
3. Schedule orders, Apply and Release holds
4. Run reports and concurrent programs
5. Setup Transaction Type, apply and release holds

This increases the productivity of Shared Service Centers as users no longer have to switch application responsibilities when processing transactions for multiple operating units at a time.

Ability to view data from multiple operating units from a single responsibility, gives users more information. This enables them to make better decisions. For example when performing scheduling actions, users can now look at orders across multiple operating units and make more informed decisions on inventory allocation.

Setup required for MOAC

At a high level we need to set-up security profiles that allow access to multiple Operating Units. We also need to set the following MO profile options, in order to enable Multi-Org Access Control :
MO: Security Profile
MO: Default Operating Unit.

Note that If you do not set these profiles the application will behave as it does now.

There are almost 13 profile options in OM that has been converted to System parameters.
(For details send me an email)

To support Multi-Org Access Control the Operating Unit has been has added as a hidden folder field in the following forms:

All of the Sales Order Form Windows like Sales Order window, Order Organizer Find window (All tabs), Order Summary, Quick Sales Order window, Quick Sales Order Organizer, Quick Order Summary, Quote window, Quote Organizer, Quote Summary, Find Customer window

All of the Sales Agreement Form Windows, these are –
The Sales Agreement window, Sales Agreement Organizer, Sales Agreement Summary

Other Form Windows including –
The Scheduling Organizer window, Pricing and Availability window, Order Import Corrections window, Open Interface Tracking window, Retro-bill Organizer window, Retro-bill Requests tab

Since operating unit is a hidden fileld we can made it visible (using folder tools) in both the Order Organizer Find Window and the Summary Window.

In the Find Window, if you leave the Operating Unit field blank and do not specify criteria that are Operating Unit sensitive (such as Order Type or Ship To Location etc) you can search for transactions across all the Operating Units that you have access to via your MO: Security Profile.

You can also restrict your search to a single Operating Unit by picking one from the LOV or by specifying a query criteria that is Operating Unit sensitive (such as the Order Type).

Benefits
1. Improve accessibility
2. Increase information for decision making
3. Reduce costs

Setup Required for MOAC

You can create global security profiles that enable users to work on organizations in multiple business groups.
You do this by setting up a global hierarchy, which can contain organizations from any business group on your database, and associating it with a global security profile. This enables you to create a security hierarchy that gives users access to organizations across business groups.

Enter and Schedule Orders Across OU's
A new Operating field has been added to all the forms that allow you to view and manage Sales Agreements, Quotes, Orders and Returns across multiple Operating Units. With R12 you can:
- Choose an Operating Unit when entering a transaction
- Query transactions across multiple Operating Units
- Perform various actions on transactions from multiple Operating Units

How to do setup
Go to Responsibility > Human Resource
Navigaton > Security > Global Profile.

Create global security profiles that enable users to work on organizations in multiple business groups.

Set up the global organization hierarchy.
Setup top Organization
Define the organizations from any business group in your database, and associating it with a global security profile. This enables you to create a security hierarchy that gives users access to organizations across business groups.
Run Concurrent Request "Security List Maintenance"

For More Info - Please conatct em or you can go to www.oracle.com or metalink.oracle.com

Oracle 10g BULK Binding for better Performance.

Performance is always a very important key in the design and development of code , irrespective of the language , and it is very important when we have database operations.
Oracle in last few releases of database like 9i and 10g came up with New Built in features to improve the performances , like

* RETURNING CLAUSE
* BULK BINDING

and of course design always plays very crucial role for performance.

RETURNING CLAUSE

By thumb rule , we can improve performance by minimizing explicit calls to database.If we have requirement to get the information about the row that are impacted by DML operations (INSERT, UPDATE, DELETE) , we can do SELECT statement after DML operations , but in that case we need to run a additional SELECT Clause.RETURNING is a feature which helps us to avoid the SELECT clause after the DML operations.
We can include RETURNING clause in DML statements , it returns column values from the affected row in pl/sql variable, thus eliminate the need for additional SELECT statement to retrieve the data and finally fewer network trip, less server resources.
Below are examples about how to use RETURNING CLAUSE.

-------------------
create or replace
PROCEDURE update_item_price(p_header_id NUMBER) IS
type itemdet_type is RECORD
(
ordered_item order_test.ordered_item%TYPE,
unit_selling_price order_test.unit_selling_price%TYPE,
line_id order_test.line_id%TYPE
);
recITEMDET itemdet_type;
BEGIN
--
UPDATE order_test
SET unit_selling_price = unit_selling_price+100
WHERE header_id = p_header_id
RETURNING ordered_item,unit_selling_price, line_id INTO recITEMDET;

dbms_output.put_line('Ordered Item - 'recITEMDET.ordered_item' 'recITEMDET.unit_selling_price
' 'recITEMDET.line_id);
INSERT into order_test (ordered_item,unit_selling_price, line_id, header_id)
values ('ABCD',189,9090,1)
RETURNING ordered_item,unit_selling_price, line_id INTO recITEMDET;

dbms_output.put_line('Ordered Item - 'recITEMDET.ordered_item' 'recITEMDET.unit_selling_price
' 'recITEMDET.line_id);

DELETE from order_test
where header_id = 119226
RETURNING ordered_item,unit_selling_price, line_id into recITEMDET;

dbms_output.put_line('Ordered Item - 'recITEMDET.ordered_item' 'recITEMDET.unit_selling_price
' 'recITEMDET.line_id);
END;
-- End of Example 1 ---

When we talk about oracle database , our code is combination of PL/SQL and SQL. Oracle server uses two engines to run PL/SQL blocks , subprograms , packages etc.

* PL/SQL engine to run the procedural statements but passes the SQL statements to SQL engine.
* SQL engine executes the sql statements and if required returns data to PL/SQL engine.

thus in execution of pl/sql code our code results in switch between these two engines, and if we have SQL statement in LOOP like structure switching between these two engines results in performance penalty for excessive amount of SQL processing.This makes more sense when we have a SQL statement in a loop that uses indexed collections element values (e.g index-by tables, nexted tables , varrays).

We can improve the performance to great extends by minimizing the number of switches between these 2 engines.Oracle has introduced the concept of Bulk Binding to reduce the switching between these engines.

Bulk binding passes the entire collection of values back and forth between the two engines in single context switch rather than switching between the engines for each collection values in an iteration of a loop.

Syntax for BULK operations are
FORALL index low..high
sql_statement

..bulk collection INTO collection_name

Please note down that although FORALL statement contains an iteration scheme, it is not a FOR LOOP.Looping is not required at all when using Bulk Binding.
FORALL instruct pl/sql engine to bulk bind the collection before passing it to SQL engine, and BULK COLLECTION instruct SQL engine to bulk bind the collection before returning it to PL/SQL engine.

we can improve performance with bulk binding in DML as well as SELECT statment as shown in examples below.

declare
type line_rec_type is RECORD
(line_id NUMBER,
ordered_item varchar2(200),
header_id NUMBER,
attribute1 varchar2(100));
type line_type is table of line_rec_type
index by pls_integer;
i pls_integer:=1;
l_att varchar2(100);
l_line_id number;
l_linetbl line_type;
l_linetbl_l line_type;

type line_type_t is table of integer
index by pls_integer;
j pls_integer:=1;
l_lin_tbl line_type_t;


type line_type_t2 is table of oe_order_lines_all.attribute2%TYPE
index by pls_integer;
j pls_integer:=1;
l_lin_tbl2 line_type_t2;

begin
dbms_output.put_line('Test');

for line in (select attribute10, line_id , ordered_item, header_id
from oe_order_lines_all
where creation_date between sysdate-10 and sysdate)
loop
l_linetbl(i).line_id:=line.line_id;
l_linetbl(i).header_id:=line.header_id;
l_linetbl(i).ordered_item:=line.ordered_item;
l_lin_tbl(i):=line.line_id;
i:=i+1;
end loop;
dbms_output.put_line('Total count in table 'l_lin_tbl.COUNT);

-- Below statement will call the Update Statement ONLY Once for complete Collection.
forall i in l_lin_tbl.FIRST..l_lin_tbl.LAST
save exceptions
update oe_order_lines_all
set attribute1=l_lin_tbl(i)
where line_id = l_lin_tbl(i);
--Common Error
--DML ststement without BULK In-BIND canot be used inside FORALL
--implementation restriction;cannot reference fields of BULK In_BIND table of records

--In below statement we are passing complete collection to pl/sql table in Single statement and thus avoiding the Cursor.
SELECT line_id, ordered_item, header_id, attribute1 BULK COLLECT INTO l_linetbl_l
FROM oe_order_lines_all
WHERE creation_date between sysdate-10 and sysdate;

FOR i in 1..l_linetbl_l.count LOOP
dbms_output.put_line(' Line ID = 'l_linetbl_l(i).line_id' Ordered Item = 'l_linetbl_l(i).ordered_item' Attribute1 ='l_linetbl_l(i).attribute1);
END LOOP;

--Returning
forall i in l_lin_tbl.FIRST..l_lin_tbl.LAST
UPDATE oe_order_lines_all
SET ATTRIBUTE2 = l_lin_tbl(i)
WHERE line_id = l_lin_tbl(i)
RETURNING line_id BULK COLLECT into l_lin_tbl2;

FOR i in 1..l_lin_tbl2.count LOOP
dbms_output.put_line(' Attribute2 ='l_lin_tbl2(i));
END LOOP;

END;
-- End of Example 2 ---


For More Info - Please conatct me or you can go to www.oracle.com or metalink.oracle.com or refere Oracle 10g user guide.

Oracle Application R12 COGS

In Oracle Application R12 COGS process has been changed. Reason for that are aggressive revenue recognition practices as well as the guidelines from various governing bodies.

Till R11 Cost of goods sold has been recognized as soon as the Order lines has shipped, as shown in below steps

After ship confirm, user run the interface trip stop (ITS).
ITS in turns run the OM Interface and Inventory Interface.
Inventory Interface calls Inventory transaction manager which in turns call COGS WF.
But as per new practices COGS should be recognized along with the revenue.

In R12 used need to define deferred cogs account. These deferred cogs account can be defined at each inventory org level.

During shipping process Inventory tables will hold the deferred COGS accounts. Only after invoicing has done in AR, AR will notify the Costing, and Costing in turns call the COGS account generator to get the cogs account .In that way COGS and revenue will be recognized in the same period.

There are few exceptions like how to get the COGS for

1. Ship only line (No Invoice will be created).

To handle above cases Close-line activity of the order line workflow has modified to call the costing API to get the cogs value