Order Header workflow with Header Level Invoicing.

Oracle has bundled Order header workflow with header level invoicing , this is just like other header workflow , but invoicing activity is added to order header. In other type of flow Invoicing activity is at lline workflow.So when ever line is fulfilled , it progress to Invoicing activity and create invoice in AR.So if an Order has 10 lines , and they reach to Invoicing activity at different time interval , then system may create 10 invoices .But if we choose Order Header workflow with Header Level Invoicing.when ever line is fulfilled it will go to the activity Fulfill - Continue Header Flow and inform the Order header that it is ready for Invoicing, Once all the lines inform that they are done with the fulfillment , Order Header will progress and complete the Invoicing activity . In this case there will be only 1 invoice for all the lines in the Order.
Please Note that here we are talikng
1. Order header work flow - Order Flow - Generic with Header Level Invoice Interface
and to support that we have to make sure that we choose Line level work flow as - Line Flow - Generic with Header Level Invoice Interface

NOTE - we came use fulfillment Set too if we want to invoice all the lines together by adding all the lines in same fulfillment set.

No Workflow has been assigned to this Transaction type

As I have stated in my previous post on the Oracle Sales Negotiation, when we do the setup we should have complete picture of our requirement.

After I finished my Transaction type Setup, I was able to create and Progress the Sales Quote, but when I tried to progress quote header workflow from "Negotiation Complete - Eligible?, system was throwing Error
"No Workflow has been assigned to this Transaction type?,

I checked my negotiation workflow and it was seeded one and everything from workflow side looks good, but still this error, well with no where to go I have looked into user guide, and noticed that in transaction type setup I have left the column "Fulfillment Flow" BLANK.
Please Note that Once Quote has negotiated and Approved it will be converted into sales Order. But in my case I have left the "Fulfillment Flow? BLANK, so system was not able to create the Sales Order and throwing Error.

I just Assign the Fulfillment Flow = Order Flow - Generic and once again tried "Progress Order?, well as expected Quotes converted in Sales Order.

Few other Tips

1. In case you want to retain the Quote Number as Sales Order #, please check the check box "Retain Document Number" in transaction type.

2.Though Quotes completely depend upon the Quote Header Workflow, we still need to assign the Line workflow in the Transaction Type for the quote, plus assign Line workflow to quote line.
Reason = Once the Quote converted into Sales Order, System will assign the workflow associated with the quite lines to Sales Order Line.

ORA-20001: APP-FND-01728: An assignment does not exists for these Parameters.

While creating the Sales Quote in Order Management , I was getting Error

ORA-20001: APP-FND-01728: An assignment does not exists for these Parameters.

OE_HEADER_UTIL Procedure Get_Order_Number
user-Defined Exception in packahe
OE_HEADER_UTIL Procedure Pre_Write_process

I check all my required stepup again and again and found every thing is as correct as it should be.
I checked ...
1.Transaction Type has define for Sales Quotes
2.Document Sequence has Defined and Assigned

but still this error .

But Finally I found that i was missing an Important Step .

Resolution -
Please Note that When we define the Transaction Type for Quotes, A new document category with ?- Quote? appended to Order Type name is created.

Please check
Nevigation>Documents > categories

We have to assign a document sequence with this category to generate the quote number.Once you do that , you can create the Sales Quotes with out any Error.

Encryption and Decryption Utilities in Oracle Database

Encryption and Decryption of data is very importanat for everyone of us . I was just scrolling through found lof of options provide by Oracle.


Oracle is provide an API -DBMS_OBFUSCATION_TOOLKIT that enables an application to encrypt data using either the Data Encryption Standard (DES) or the Triple DES algorithms

Oracle installs this package in the SYS schema. You can then grant package access to existing users and roles as needed. The package also grants access to the PUBLIC role so no explicit grant needs to be done.
When we do Encryption and Decryption, Most Important part of this Process is Key Management.
1. Key Management
2. Key Generation
3. Key Maintainance.

Key management, including both generation and secure storage of cryptographic keys, is one of the most important aspects of encryption. If keys are poorly chosen or stored improperly, then it is far easier for a malefactor to break the encryption. Rather than using an exhaustive key search attack (that is, cycling through all the possible keys in hopes of finding the correct decryption key), cryptanalysts typically seek weaknesses in the choice of keys, or the way in which keys are stored.

Oracle Utility to encrypt and decrypted data

Thanks to Robet for sharing the Oracle Utility to encrypt and decrypted.
Below is example to encrypt and decrypted.

declare
l_encrypted VARCHAR2(1000);
l_decrypted VARCHAR2(1000);
l_key VARCHAR2(20):='7777888855551111';
BEGIN
DBMS_OBFUSCATION_TOOLKIT.desencrypt(input => UTL_RAW.cast_to_raw('4505111111111331'),
KEY => l_key,
encrypted_data => l_encrypted);
dbms_output.put_line('l_encrypted = '||l_encrypted);
DBMS_OBFUSCATION_TOOLKIT.desdecrypt
(input=> l_encrypted,
KEY => l_key,
decrypted_data => l_decrypted);
dbms_output.put_line('l_decrypted = '||l_decrypted);
END;

that's for Now ( I am really busy, to do something with ADF Mobile)

arp_bank_pkg.process_cust_bank_account

While exploring how to replace credit card with token I came across a interesting API to create the data in AP_BANK_ACCOUNTS_ALL and AP_BANK_ACCOUNT_USES_ALL table. This API is part of appliaction from ages so I am sure almost everyone knows about that , but blogging in case someone like me need it .

declare

l_bank_acct_id NUMBER;
l_bank_acct_uses_id NUMBER;
begin
arp_bank_pkg.process_cust_bank_account
( p_trx_date => &cust_trx_date
, p_currency_code => &transactional_curr_code
, p_cust_id => &cust_id
, p_site_use_id => &invoice_to_org_id
, p_credit_card_num => &credit_card_number
, p_acct_name => &credit_card_holder_name
, p_exp_date => &credit_card_expiration_date
, p_bank_account_id => l_bank_acct_id
, p_bank_account_uses_id => l_bank_acct_uses_id
) ;
dbms_output.put_line('l_bank_acct_id = '||l_bank_acct_id||' l_bank_acct_uses_id = '
||l_bank_acct_uses_id);
end;

How to Check demand and Supply generated by Sales Order n PO respectively

In Oracle Order e-Business Suite, we are few very useful UI available.
When ever we create Sales Order It Create Demand and and when ever PO created in System , it create supply.

MTL_RESERVATION is table that record all these details.Below is one simple query for TECHNICAL consultants

select ph.segment1,a. supply_source_line_id, a.supply_source_header_id, a.supply_source_type_id,
a.demand_source_header_id, a.demand_source_line_id,
a.demand_source_name, a.demand_source_type_id
from mtl_reservations a, oe_order_lines_all l, po_headers_all ph
where a.demand_source_line_id = Sales_Order line_id --(Pls enter PARAMETER)
and l.line_id = a.demand_source_line_id
and a.supply_source_header_id = ph.po_header_id

above query take sales order as parameter and display the Supply and demand for a sales Order ( Good in case of B2B and Drop ship order).

Please note that above will not work as generic query.
FROM Functional point of view, we have UI in Inventory

Navigation -
                 Inventory -
                                Availability -
                                                Demand/Source.

This UI will display us the DEMAND(SO) as well as SUPPLY (PO) .We can narrow down further based on SubInventory as well as various Source of Demand as well as Supply.


I will post the screen short of UI soon.

How to Desgin Master Detail Flow in BPEL

In this blog , I will explain with simple example how to design Master/Detail BPEL Flow .For aurdience who has working knowledge of workflow it is very easy to understand and visualize the process, but Oracle BPEL has made it so simple to design Master Detail that any one who want to learm , can learn it.

Master -Detail flow Co ordinations consists of one-to-many relationship between Single Master and multiple details process.Here I will discuss One master and One detail.Basic Concept here is Master Flow will Trigger Detail flow and detail flow once completed will pass the control; back to master flow.

In Oracle BPEL process following activities coordinates actions between master and detail process.

• Signal: Notifies the other processes (master or detail) to continue processing
• Receive Signal: Waits until it receives the proper notification signal from the other process (master or detail) before continuing its processing

Oracle Order Management DropShip Flow for R12

In this blog I will explain Oracle  Drop Ship Flow for R12i.
What is Dropship Order - In dropship Order, sales order line creates a requisition line that becomes a PO and sent to your supplier. You instruct your supplier to send the item or configured item directly to your customer only and items never physically pass through your warehouse, and therefore you do not pick, pack or ship them yourselves

In this blog I will explain the R12 Dataflow of Dropship order from OM to Oracle Payables.
Topics covered in this blog are
Drop Ship Sales order creation and booking.
Requisition Import
Auto Creation of PO
Receipt against PO
AP against PO

enabled_flag in MTL_SYSTEM_ITEMS_B for Inactive Items.

I am not big fan of playing update on Oracle standard tables , but recently their was a question of updating the enable_flag to N for Inactive Items.

My take on it is once we Inactive item (Note - System will allow you to inactive items from the master Org only and not from Non Master Organizations), system will not allow you perform
MTL Transactions
Purchasing transactions
Customer Orader Trasactions
so what is the purpose of updating the enabled_flag field. Updating column in MTL table is not a big task , but it is hard to to analyze it's Impact on other Modules.But in case you have some customization based in this field I suggest to conatct oracle and check with them.

How to Call Synchronous BPEL Process from Mediator

In this blog I will explain in details about how to built the interactions between Mediator and Synchronous BPEL Process.(Request and Response Pattern)

Please Note that both BPEL as well as Mediator are the complementary components and do similar tasks.

BPEL is process based and Mediator is routing based.

BPEL is usually for long running process where as Mediator is for short and fast messages.

As stated this blog is about design Interaction Pattern between Mediator and Sysnchronous BPEL process .

Example -In this example we have WebService that will take Input for Sales Order and call Mediator process.

If Order Price is < 50 Mediator route the flow to Partner link Service  to Insert the data into Sales Order Table.

If Order Price is > 50 Mediator route flow to BPEL Process , to validate Customer Raiting. We can also define Sysnchronous Reply in Mediator Process.In My example I define Sysnchorous reply as SERVICE to Insert the Sales Order Data in table.

Complete Flow . -

Below is complete flow of my Process. I have Web service to received Input , Meditor to Route the process.BPEL to Validate the Rating and 2 database Adaptor to validate the data.

 

Step #1

Define your Message Structure ( Structure for your Input data).In my case I have defined a simple Order Structure with few elements, as shown below.

Save it as OrderInfo.xsd

 Step # 2

My Requirements

1. Let user Enter Sales Order data Manually.
2. Insert Data into Sales Order Table  if Order less than 50.
3. Validate Customer rating if Order More than 50 and then Insert into Sales  Order table.

Requirement #1

Create a Web Service by Dragging "Web Service" Service Adapters from Component Palette and the associate "Order Info" Schema " by populating the OrderInfo.xsd in URL field in Request. Tab

Requirement #2

Define a Partner Link by Dragging "Database Adapter"  from Component Palette , drop it in External References, name it "InsertOrder".

Configure this database adapter to Insert the data into so_info table.Please note that in my example structure of so_info database table and "OrderInfo.xsd" file is same.

Requirement #3

Drag Mediator Service component in Component and link "WebService" and Database Adaptor. (Pls refer Complete flow diagram)

Configure the Mediator component to Insert data accepted as Input in webService  to "InsertOrder" partner link as shown below.

Requirement #3
As part of requirement 3 , I need to validate the customer rating if Order amount is MORE than 50.For that I have designed a BPEL Process which will take "OrderInfo.xsd" as input and then call the PartnerLink "CheckCustStatus" to check the Credit Status of Customer.
While desiging the BPEL Process make sure that you assign "OrderInfo" Message type to

1. Input - Process Request Message
2. Output - Process Response Message

 Step A -

Import "OrderInfo" Message structure in BPEL Process shown Below. -
Click the BPEL Process > Click the Schema and then > Import Schema

Step B -

Once you imported your Message Type .Assigin it to Input and Output variable of your Process.In my Example I want my Input and Output Variable should be of same structure , so I associate same message
structure with input and output variable.

To fuilfill my requirement , I have bulit a filter in Mediator process and called a BPEL process to validate the Customer Rating if Order is More than 50.Also I want to pass the same "OrderInfo"Message Structure to BPEL Process.For that I have already assign "OrderInfo" Messaage type to  

1. 
   
   
   Process Request Message
   
   
   
   
2. 
   
   
   Process Response Message  , please verify that.If it is not assigned yet , assign that as shown below.
   
   
   
   
3. 
   
   
   (Refer muy blog http://eoracleapps.blogspot.com/2009/10/how-to-create-bpel-process-to-insert.html to learn more).
   
   
   
   

As shown above I have selected -Message Type  > Process WSDL >  Assign the OrderInfo Structure to Request and Response Message Type.

Below is the BPEL Process , this Process Take the Input data in OrderInfo Structure and then Read the Customer Name and Get Credit Rating for the Customer.I am using the "Database Adapter"Parther Link  to read the customer credit rating from database table.

Once BPEL Proces is ready configure Mediator to Call the BPEL Process to validate rating if Order Total is Greate than 50 as shown below.

Mediator Structure . - Here I set the Routing Rules.

That's it .

Below are details of I/p and O/p

Drop Ship Error: Line #1 Shipment #1 Distribution #1 Quantity billed X greater than quantity delivered Y

This is common Error in Drop Ship Orders  - system will raise thsi error  if we have 3 Way Match in PO and only AP Invoice Exists , but no Receipt.

Make sure that if you have 3 Way Match then you have all 3.

Difference Bewteen Drop Ship and Back to Back Order

Difference Bewteen Drop Ship and Back to Back Order


Both the Process are quite similar . In both cases, your sales order line creates a requisition line that becomes a PO sent to your supplier.

In a drop shipment, however, you instruct your supplier to send the item or configured item directly to your customer. The items never physically pass through your warehouse, and therefore you do not

1. pick, 
2. pack or 
3. ship them yourselves. 

In the back-to-back scenario, you instruct your supplier to send you the goods, and then you

1. pick, 
2. pack or 
3. ship them on to your customer

Possible options to avoid custom.pll in Oracle Apps

this is just 2 line post about how to avoid custom.pll (Oracle don't support your custom.pll).

Possible options are
1.Defaulting Rules - If you want to default something .

2.Personalizations - If you want to customized some behaviour.

best part in these 2 is you can easily call your customAPIs.

ITS some more insight

As I have explained in my earlier posts on ITS. ITS in OM has 2 phase.

First Phase related to Order Management and it updates the OM related data.
Second Phase related to Inventory and it updates(increment/decrement) inventory related data.

If first Phase failed , system will not execute the second Phase, even if you try to run the 2nd phase directly by executing the concurrent program "Inventory Interface".

Point that I want to make in this post is , On oe_order_lines_all table we have a field reserved_quantity , and will show the quantity that has reserved against the order line, this field will shows the reserved_quantity till "Inventory Interface" part of the ITS has not exedcuted sucessfully.One that is executed sucessfully this field updated to NULL.
Some time I have notice that even the order line is close and workflow is ended , but the reserved_quantity field has still value , which is very very confusing , but latter in I found that in all such cases Inventory Interface part of ITS has not executed sucessfully.
So when ever you encounted sych cases make sure Inventory Interface part of ITS has executed sucessfully.

How to open Oracle Forms in Unix.

Below are the commands to open Oracle Forms in UNIX.

Forms 6i.
to open                        - f60desm
to compile/generate      - f60genm

Forms 9i +
to open                       -   frmbld
to compile/generate     -   frmcmp

Why my workflow ROOT activity shows Result = "#STUCK"

This type of situation occurs when your workflow activity return a value which is not any of the possible outcome of the workflow activity.And after that if you run the workflow background process with Stuck Parameter = yes, then Root activity of the the workflow complete with Status = Error and result = #Stuck.

 

For e.g 

we have a workflow activity  which has outcome Yes/No, but if because of some error/exception/bad programming somehow your workflow activity return value which is not Yes/No then on execution of workflow background process with Stuck Parameter = yes, Root activity of the the workflow complete with Status = Error and result = #Stuck.

ADF Page Based on Web Services (oe_order_pub.process_order)

In this Blog I will explain with explain how to create ADF Pages based on the Web services.Here I will take the example of creation of webservice based on PL/SQL API OE_ORDER_PUB.PROCESS_ORDER.This API is integral part of Oracle Order management .Main feature of this API is to

• 
  
  Create Sales Order
  
  
  

Once ADF Page Ready , it will create Sales Order in Oracle Order Management , based on the input Parameters.

Components involves in my test case are

1. 
   
   PL/SQL API wrapper that will call OE_ORDER_PUB.PROCESS_ORDER.
   
   
   
   
2. 
   
   Webservice based on wrapper (Step 1).
   
   
   
   
3. 
   
   ADF Page
   
   
   
   

Step# 1 - Create Wrapper database Function which call the OE_ORDER_PUB.PROCESS_ORDER. (For complete info about how to create Sales Order with OE_ORDER_PUB , please refer my previous blog on that).

Step #2 - Call the Wizard to create Webservice based on PL/SQL package.

Step # 3 - Create the data Control for the web Service.

Step #4 - Go to the View Controller Project in Jdev and select adfc-fages. Drag and Drop "View Component " on the adfc-faces.xml and Name it Sales Order.

Step #5- Drag "Split-Panel" on the Sales Order Page.

Step#6 - Drag and Drop the Data Control for web Services on the adf Page and Select "Parameter Page" from the pop-up menu  .System will create the adf-page with the wen-Service data control as shown below.

Run your page.Enter the Input parameter as shown in below diagram (left Hand side).Press the Create Order Button and System will Create the Sales Order and Lines in Oracle Order Management Application and Display the
Order # , Order Header ID and Booked Flag on Right hand Side ( as shown below).