Duplicate data on Oracle oe_drop_ship_sources

This Post is about Oracle Apps Drop Ship Sales Order.

Why I have duplicate data in oe_drop_ship_sources.answer is simple, for a drop ship sales order when I book Sales Order in Oracle Apps, Oracle workflow for order line progress to p\Purchasing - deferred activity.once we execute the WBP it insert the data into Oracle Requisition Interface Table ( to Create Requisition and Purchase Order).NOW
1. Some how if user rewind the order line workflow to an activity before purchasing -deferred and delete the data from Req interface .in such cases workflow when got progressed to purchasing -deferred,it will again insert data in oe drop ship sources.


- Posted using BlogPress

Receiving throwing error for drop ship SO line

This Post is about Oracle Apps Drop Ship Orders and Receiving

Customer not able to do a receipt against DropShip sales order in Oracle Apps.
Another issues that I came cross about drop ship Sales Orders is customer not able to do receipt against a drop ship sales order, as usual I started my investigation and found that customer has splitted drop ship order line because of partial receipt and this issue is for the splitted drop ship line.
1. Data and workflow looks good for original and splitted order line.
2. Requisition as well as purchase order looks good and have correct data.
3.but when I looks on OE_DROP_SHIP_Sources table I found that their is not data exist inthis table for the splitted order line and because of that when customer try to do receipt, a call has made to OM drop ship API which returns errror becUse of missing data.

Best solution I found is insert missing data in oe drop ship sources table.
That is my take , in case you have any such issue ,I advise contact oracle support and get data fix from them


- Posted using BlogPress

Order line status is FULFILLED

This Post is about Oracle Apps Order Fulfillment .
Why some time I see order line in Fulfilled status.pls note that fulfilled is a valid status and every line pass through this status,but lines comes to this status for fraction of time and then progressed to next status.
Oracle Order Management set the status of line to fulfilled when workflow activity FULLFILL_LINE completed, but immediately after that we have invoicing and close line actitity, so that is the reason we hardly see line in fulfiled status.line will remain in Fulfilled status if either invoicing activity in workflow fails or you have some sort of deferred activity after invoicing

That all .

Workflow RETRY ACTIVITIES in Error.

This Post is about Oracle Apps Workflow (Order Management)

Issue - order lines stuck at BOOKED/Awaiting Shipping/Production Open/Supply eligible for Standard item/ATO item/ATO Model .

Solution - in all the above cases check data in wsh_delivery_details table,if the delivery details are in SHIPPED status and ITS has not executed and
shipping Qty
Shipped Qty
Actual shipment date 
not populated in Oracle oe_order_lines_all table , and workflow for order line is in Error status ,theme go to order line in order UI and select line and press right click and select "retry activities in error" from pop up menu, system will show the activities thar are in error,if there are multiple activities that are in error the. Select the activity that you want to retry (from LOV) , and with that Oracle system will retry the activity and progress the work flow to next Activity(we don't need any y data-fixs script here).
Please not that there is possibilty that "retry activity in error" might fails ,because their is valid reason for failure.ij such cases there is need to further investigate why it fails
Thanks
- Posted using BlogPress

Receipt Details suddenly Disappear from Receipt UI

This  Post is about Oracle Apps Purchasing / /Inventory Module

One of my user complaints that she is not able to see the Oracle Drop Ship Receipt in the Receipt UI.Accoriding to him data was there few minutes back , but then suddenly gone.
During my Ananlysis , I found that because of the Receipt date tolerance issue user was not able to create the receipt for a Drop Ship Item.To make it work she navigates to the Oracle Purchasing and then from Purchae Order  > Lines > Shipment >Receiving controls , she modify Receipt Date
Days Early
Days Late

that all . After doing all that ,when she came back to receiving UI, she was not able to see infomation about the line that he want to receipt.
Well Soluttion is Simple - when ever you modify something like that for a Oracle Purchase Order line you need to reapprove the PO, and that waht has hapened. After user modified Receipt Controls PO was in Reapproval Requires Status, she Approve the PO and was able to see the line in the receiving UI

How to Create Supplier List and RFQ

This Post is about How to create Supplier List and RFQ in Oracle Purchasing.

In the post I will explain about the Supplier List and how to use Supplier List and Price breaks while creating a RFQ.

Navigate to Supplier List
Supplier Base > Supplier List

 Purchasing Responsibility  -Navigate to RFQ UI

Navigation > RFQ.
Enter details for the RFQ

Operating Unit
RFQ Type
Status
Due Date
Description
Reply Via
Close Date
Item
category
UOM etc

How to Define Expense Item

In this post I will explain how to create an Expense Item.


Go to Inventory responsibility
Navigation > Inventory > Items > Mater Items

In the Item Definition UI enter
1. Item Name
2. Description

• Main Tab - Select UOM.
• Inventory Tab - Don’t Select any Attribute in Inventory tab
• Purchasing Tab – Select
• Purchasable
• Enter Default Buyer Details
• Default Buyer
• Expense Account
• Receiving Tab – Enter
• Receipt Date Control Attribute

All line of SalesO rder Close , burt Header Still Open

Many a times I have noticed that all lines of Order are Closed , but Order Header is still open.Workflow for Order Header is at workflow acrtivity "Close - Wait For Line" in notified status, and workflow for all lines are Closed/Purged state and line open flag = N.

Well, adata -fix required to resolve this issue. But you can do little further investigation from your side by executing below Query

select number_value from wf_item_attribute_values
where item_type='OEOH'
and item_key = '&Order_header_id'
and name = '#WAITFORDETAIL'

if above query return value for "NUMBER_VALUE" , then that values indicates the No. of lines that are still Open from the Order Header Prospect.

Some Background - Header Workflow attribute #WAITFORDETAIL stores the # lines added in the Sales Order.Every time a line close this attribute decrease by 1 and finally when this attribute = 0 , Order header will close ( if all conditions to close header satisified). But ONCE a while a line close but this attribute at Order Header doesn't changes and finally when all lines close , this attribute still has a values > 0, and thus Order header remains open.
On setting this attribute to 0 Order headers will close (OFF COURSE all other condition (if any) to close header should satisify too).

ORA-01400: cannot insert NULL into("INV"."MTL_RESERVATIONS"."REQUIREMENT_DATE")

User was getting ORA-01400: cannot insert NULL into
("INV"."MTL_RESERVATIONS"."REQUIREMENT_DATE") G_MISS_CHAR
Reservation fails with unexpected error in dropshipreceive
when she was trying to do a receipt against a Drop Ship PO. Everything look fine , but system was still thowing Error . On Investigation I found that REQUIREMENT_DATE is mandatory in MTL_RESERVATIONS table and it is populated from the Schedule_ship_date from the oe_order_llines_all table.
In this particular case Requisition and Purchase Order was created for the drop ship sales order , but the schedule_ship_date was NULL . SSD should be populated when Sales Order push the data to Purchasing for Requistion.

I populated the SSD and user was able to create Receipt.

Query to Join Delivery Details and MTL Material Table

select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id,

wdd.released_status, oe_interfaced_flag, inv_interfaced_flag,
wdd.creation_date, mtl.transaction_id, l.flow_status_code,
l.open_flag, l.cancelled_flag
from wsh_delivery_details wdd,
mtl_material_transactions mtl,
oe_order_lines_all l
where
l.line_id = wdd.source_line_id
AND wdd.delivery_Detail_id = mtl.picking_line_id
AND wdd.source_code = 'OE'
and wdd.oe_interfaced_flag = 'Y'
and wdd.released_status = 'Y'

Also below is query to Join Order line with MTL_MATERIAL_TRANSACTIONS

select mtl.transaction_id, l.flow_status_code,

l.open_flag, l.cancelled_flag , l.shipped_quantity
from
mtl_material_transactions mtl,
oe_order_lines_all l
where
mtl.trx_source_line_id = l.line_id

Query to Check if Closed line stuck with MTL_TRANSACTION_INTERFACE

In this post I post query that shows the query to reterive all the order lines that are CLOSED , but the reservation is not yet RELEASED.(data stuck in mtl_transactions_interface)

SELECT count(*)
FROM oe_order_lines_all l
WHERE l.line_category_code = 'ORDER'
AND NVL(l.shipped_interface_flag,'N') = 'N'
AND l.flow_status_code = 'CLOSED'
AND l.open_flag = 'N'
AND NVL(l.invoice_interface_status_code, 'N') = 'YES'
AND EXISTS
(
SELECT 'MTI records'
FROM mtl_transactions_interface
WHERE source_code = 'ORDER ENTRY'
AND trx_source_line_id = l.line_id
)

Workflow Assignment Query

In this post I will post a query that shows what type of workflow process associated with the order line.

SELECT wf_assign.process_name ,
header.order_number,line.line_id
FROM oe_workflow_assignments wf_assign,
oe_order_headers_all header,
oe_order_lines_all line
WHERE line.line_id = oola.line_id
AND Nvl(wf_assign.item_type_code, 'OEOL') = 'OEOL'
AND header.header_id = line.header_id
AND header.order_type_id = wf_assign.order_type_id
AND line.line_type_id = wf_assign.line_type_id
AND wf_assign.line_type_id IS NOT NULL
AND SYSDATE >= wf_assign.start_date_active
AND SYSDATE <= nvl(wf_assign.end_date_active, SYSDATE)

Order Management Transaction Type.

In this post I will talk about oracle transaction type UI. Here I will not talk about , How to define transaction Type (TT), If you need more info about how to define transaction type and how to assign workflow , please refer my earlier post on same.

Here I will talk about what all info. we can default in sales order with TT.
In TT UI we have 3 tabs
1. Main
2. Shipping
3. Finance

and we can default fields like

Default Return Line Type

Sales Order Creation Order Management

In this blog I will discuss in brief about what info a user need to create sales order in order management.

Oracle has provides three ui to enter sales order info ,and these are
Order Organizer
Quick Sales order UI
Pricing and Availability

You can use any of these 3 based on your need of requirement

Order has dividend Order Entry into many sections.
Order Headers
Order Lines
Promotions/Pricing
Scheduling/ATP/Reservation

In order header we capture info like
Customer details
Sales rep details
Pricing list info
Order Type
Payment method
Shipping info
Payment type etc

OE_ORDER_PUB to Create/Split/Cancel Order Line

In this Post I will explain in short how to call Process Order API to

1. Create Sales Order with 1 Shippable line.
2. Create Sales Order with 1 RMA line.
3.Create Sales Order with 1 Shippable and 1 RMA line.
4. Canel Order line.
 (Oracle Apps)
Purpose of this post is to share some knowledge about OE_ORDER_PUB. Example that I use just creates simple Order and line.

Even the ITS is completed successfully we are seeing the WF line status is SHIP_LINE how can we close this line now?

Well this is a pure data corruption case.
Reason - When we Run ITS, it will for sure check the Order Line workflow, and ITS expect the workflow of the line should be in SHIP_LINE "Notified" status. If it is so ITS complete the SHIP_LINE activity by calling wf_engine API and then Push the workflow to Deferred fulfill. Once it is done, it will update OE_ORDER_LINES_ALL table and set the oe_interfaced_flag = 'Y' in WSH_DELIVERY_DETAILS.

In case you feel ITS is complete but workflow for line is not close, then check
1. Shipped Quantity, actual_shipment date in OE_ORDER_LINES_ALL
2. oe_interfaced_flag in WSH_DELIVERY_DETAILS

If Step#1 is populated and Step# mark as "Y”, then you need to Progress the line workflow to Deferred_fulfill by Script (I suggest contact Oracle Support for that).From Deferred_fulfill workflow background engine will take care.

Order line Bill only

In this post I will discuss little about the workflow process "Bill only with Inventory interface".

Suppose we have requirement to Bill customer for good that we sold him , but their is no Shipping Involves.
Example I step inWalmart and bought some stuff , paid for that and then carry it along with ourself in Car.

Now to handel these types og flow we have 2 flow exits in Order Management
1.Bill Only
2.Bill Only with the Inventory Interface.

But let's say if we use "Bill Only " then we will not be able to do Reservation , but In case we need to do Reservation , we should use "Bill Only with the Inventory Interface".

In this flow when user Book the sales order,  Header workflow proceed to booking atctivity and then Start theh line flow . In line flow there is activity Inventory interface , this activity validate the line data and if Reservation Quantity > 0 , It call the INV APIs and populate the MTL_TRANSACTION_LOTS_INTERFACE
MTL_TRANSACTIONS_INTERFACE
Workflow then proceed to the fulfill and Invoicing activity.But fulfill and Invoiving will not Release the Inventory .To release the reservation we need to run the interface manager  and on sucessful execution it will release the reservation.

Reason for writing this post is , Recently one of my Customer implemented this flow and they end up with lots of orde lines stuck with  Reservation not released , and on the other hand order line is already close and workflow also closed..
So whenever you Implement this flow make sure you run the interface manager.

Thanks

Master Details Oracle Workflow

For How to define a Master Detail Workflow please refer to my previous blog om the same.

In this blog I will add some more info about what we need to do or what has required when we define a Master Detail relation-ship  in Workflow.

1. First thing we need is define Master and Child flow.
2. Call the wf_engine API to establish relation ship betwen Master and Detail. This we usually do when we call Detail workflow from master.
3. Define a "CONTINUEFLOW" activity in Master flow and set the 'Waiting Activity' and 'Waiting Flow' attribute.
4. Similarly define  "WAITFORFLOW" activity in child flow and set the attributes 'Continuation Activity' and 'Continuation Flow'.

Once Matser flow got triggered and now we want to transfer the flow back to Master , we need to define the "CONTINUEFLOW" activity in the Master flow and to Receive the control back in Master we need to define "WAITFORFLOW" in the Master.
Please note that most important point here is how we define the attribute of these two activity.

Changes in OE_ORDER_PUB in R12i

From OM Prospect
OE_ORDER_PUB API has been modified to include the parameters
1. p_org_id - p_org_id is operating_unit_id
2. p_operating_unit - p_operating_unit is Operating Unit Name

User can enter any of these values. Please note that when user pass any of these values system will check if the responsibility from where user are executing these APIs has access Operating Unit or not, if not then API will return Error.
If User don’t pass this values then system will try to derive the values from the Default Operating Unit (Profile option) and if that too not specified then API will return Error.

Credit Card Changes in R12i Order Management

In this post I will explain in details about the new changes that Oracle has introduced in R12 about handling of the credit card transaction.

Oracle has made significant changes in R12 about handling of credit card Transaction

To begin with now in R12 Order Management “Credit Card Type “ will be refer as “Card Brand, also we need to enable OM profile OM: Credit Card Privileges, in order to enter credit card information in Sales Order and Payments form.

With R12 onwards OM will not store any of the Credit card/Bank Account related Information in it’s tables, all the credit and Bank related information will be encrypted and stored in a centralized system (i.e Payments). This would give enhanced security and control over such critical information.
Order Management has to link to these table to retrieve the related information

Progress Workflow on Hold Release - New Feature in R12.1.1

Progress Workflow on Hold Release R12.1.1

In this post I will explain the new functionality “Progress Workflow When Hold Released” offered by Oracle in R121.1.1.

As of now if Order Header or Line is on a workflow driven Hold, On Release of Hold Workflow still remain as it is and not progress. To Progress the workflow user need to

Navigate ACTION > Progress Order.

This is very time consuming if there are lot of order that are either on Header or Lines Hold.User need to progress each and every Header or Line Manually.
Please Note that Hold in Order Header /Line is nothing but a workflow BLOCK activity.

In R12.1.1 Oracle has introduced a new feature – with this feature whenever a workflow driven hold is released, system will progress the workflow too (based on the setup).

In R12.1.1 Oracle has provided a new check box in the Hold UI.This new check box is “Progress Workflow on Release “. Please Note that this Check box is enable only for only those Holds that are based on Workflow based activity. For any NON-workflow based Hold this check box will not be enabled.

So whenever a User Release a Workflow based Hold and if check box “Progress Workflow on Release “ is CHECKED for that Hold the workflow Progress. BUT at the same time oracle has provided a feature to Override this Setup , that means even this check box “Progress workflow “ is checked for a Hold , user still has ability to override the progressing of the workflow.

For Overriding Oracle has provided Checkbox “Progress Workflow” in
1. Hold Release UI
2. Release Sources UI.

Advantages if having checkboxes in these 2 UI is , even though check box “Progress Workflow on Release “ is checked in Hold Setup , if user Uncheck this check box in

1. Hold Release UI
2. Release Sources UI.

Workflow will not progress. Hence progressing if the workflow in Release of Hold can be the controlled till the Hold Transaction is not finally completed.

Comments and References

Dear Readers
Thanks for taking interest in my blog , it would be appreciated if you provide your feedback /comments as well as Refer the posts that you like to your friends.

Feedback and Comments Helps me to Improve my posts.

Thanks

Copy Sales Order Options in R12.1.1

Oracle in R12.1.1 has added lot of features. On of such feature is Copy Order in Order Management .In R12.1.1 Oracle Order Management has introduced 4 new features.

1.Copy Across Operating Units.
2.Creation of Multiple copies of Sales Order.
3.Show newely Copied Order after Copy Process.
4.Provided Default Values in the Copy Order UI.

For details info about these features feel free to contact me eoracleapps@hotmail.com

Third Party payments in R12.1 Oracle Payable

Payments are generally made to the supplier providing the goods or services, however sometimes there is requirement when supplier ask you to made payments to different party on supplier’s behalf.


All the payments made to other parties on behalf of the supplier is termed as Third Party Payments.

Let’s take an example, in ordinary case, Supplier ‘A’ supplied the goods to customer ‘B’ and invoiced ‘B’ , and customer ‘B’ then made payments to A.
But in Third Party payment
Supplier ‘A’ sends the invoice to Customer ‘B’ for the goods provided. Now there is an agreement between ‘A’ & ‘C’ and ‘A’ informs ‘B’ to make the invoice payment to ‘C’ instead of ‘A’.
Here are few instances where Third Party Payments are made:
Supplier files for bankrupcy.
In such case court or the collection agency sends notification to all customers of this supplier saying that any outstanding payments that are due to this supplier should be paid to them instead to the supplier.

ITS completes with Warning.

Today I notice that many a time ITS process when executed with Trip/Delivery blank , completes with WARNING.
On Further investigation I notice that it completes with warning because it is throwing Error for one of the sales order that we have migrated in Oracle in 2000.
Workflow for Order line is not available and also open flag = N.I tried to Progress the Order , but Inventory Interface was erroring out with the Error that Period is Closed(Since Order created some where in 2000 now period is closed). Since Item is already shipped and invoiced in 2000,so we don't want to Ship or Invoice this line again , and also system will allow me to open 2000 PERIOD now , so best possible option available to me is set the oe_interfaced_flag and inv_interfaced_flag , so that ITS will not pick this Order again. Solution looks good and works for us .

NOTE - this is my analysis and what we did , but it not a advise.

Workflow available in Oracle Order Management

Oracle EBS Orader Managment is workflow driven application.
Oracle has provide large # of flows to cover all types of Order lines.
Below are some of the very commonly use Order Management Line workflows
1.Return with Approval
2.Return with receipt and Credi Memo
3.Return with receipt only.
4.Ship Only Flow.
5.Bill Only Flow.
6.ATO-Item Flow
7.ATO -Model Flow
8.Genic Line flow ( Bill and Ship).
9.Config Item Flow
10.Order Line with Export Compliance Screening
11.Order Line with Header Level Invoicing

Workflow Errors – Some Simple Mistakes that we can Avoid

Recently I met couple of consultants to fix some workflow issues. During my analysis I found that those issues are related to the simple mistakes made at design time.

As it is not advisable to publish the actual workflow, I will try to explain issues I have encounter during my 1-day project at customer site with a dummy workflow

First Question that the Developer ask me -

1.Whey the Assign Workflow BUTTON in Transaction TYPE UI in OM is not enable for LINES –

Well answer is we don’t assign workflow to Order Lines Transaction Types. We only Assign Line Type workflows to Order Header Transaction Type workflow , that the reason t is not enable for order lines.

2.Activity WAIT-1 in process xxxxxxx has no OUT transition.
Customer has added the Wait activity in the workflow, and when they validate the workflow from Transaction type UI , they were getting this Error.
Answer – If you don’t need an activity, then why you want to add that in workflow. Since this activity has no INPUT and OUTPUT transaction system was throwing this error on validation.


NOTE - whenever you made changes in the workflow, pls. verify it. File > verify.

We simply remove this activity as that was not required.

3.Process zzzzzz is missing activity FULFILL_LINE
Error message clearly indicate that FULFILL_LINE activity is missing from workflow. When I look into the workflow, I found that customer has customized the Oracle Sales Order line workflow, they have remove the DEFEER_FULFILL and FULFILL_LINE activity, but still has Standard INVOICE_INTERFACE activity for invoicing.

Please note that if you want to invoice your Order with standard INVOICE_INTERFACE you should have FULFILL_LINE activity in the flow. Since it was missing when they try to validate the Workflow they were getting the Error (Process zzzzzz is missing activity FULFILL_LINE).

4.Wrong/bad OR Operations - Though System was not throwing an error, but when I look into it, it was a BAD example of OR operations

Analysis – they added the Wait activity in the flow, but there was no Input or Output Transaction from this activity.

In the Above Process “Test Activity 2 “ have 2 DEFAULT outcomes
1. “Test Activity 2 “
2. OR
That means after execution of “Test Activity 2 “ , workflow will progress to “Test Activity 5 “ as well as “OR” activity.

Once it reaches OR activity it will not wait for ““Test Activity 4 “ to complete (because it is OR), and it just progress to next activity.

Well there is nothing Wrong in it, but my suggestion is, if we don’t care for “Test Activity 4 “ to complete then why to add OR operation here, why not just REPLACE it with the DEFER_ACTIVITY, that way we will reduce one complete activity from the FLOW (as per ct. workflow , plus my example).

Well customer like my solution and made the required changes.




I found this book very interactive and Interesting for beginners as well as for experts.

Workflow activity based Hold in R12

In this Post I will explain in short about the workflow activity based Hold introduced in R12.

In R12 Order Management oracle has introduced a new feature regarding hold on workflow-based activity. With this new feature, if you are try to apply a Hold on workflow (header /line) activity, and that particular activity is already complete, then system will prompt you message that this particular activity is already completed, and this will not apply a hold.(Reason ? Why you want to apply HOLD on aa activity that is already completed).

But let?s say if you have one particular activity multiple times in Oracle Order workflow, and one of the activity already completed, but others INSTANCES of similar activity has not yet started, in that case if you try to apply hold for this activity, system will not all you to apply to hold and prompt that activity is already completed. For the Hold to work here, you have to wait for the NEXT instance of the activity to start.

Hello to all my Readers.

Hi! Readers

First of all I would like to thanks all my readers for their comments /question and folloing my blog.
Secondly from last 1 year I am trying to add more info/topic about Oracle Tools as possible ( from Apps to database , and ADF to BPEL) , but I know there are so much more about Oracle Apps (SCM ) , that I am not even touching .
So in case any of you have any question/query please feel free to write me at eoracleapps@hotmail.com and will write about it.

Thanks
eoracleapps