Update Price List on Order Header and Lines for Book Sales Order

If requirement is to change the Price List on the Order Header and Lines and you don't want to do that from Mass Change UI, then other option is to use Process Order API to perform same function.

I will not be able to post the complete code here , but if you need send me an email.

To change the Price List  at Order header and line

Write a Small Wrapper  with the below Logic

• Write a cursor to select all the header/lines record where you want to Change the Price.
• For each record Populate the  below PL/SQL table 

--For Header
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_header_rec.header_id := Pass Header ID;
l_header_rec.price_list_id := Pass New Price List;


--For Line

l_line_tbl(index) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl(index).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl(index).line_id := Pass Line ID;
l_line_tbl(index).price_list_id := Pass New price List ID;
l_line_tbl(index).calculate_price_flag := 'Y';


---Below is info for l_line_tbl and l_header_rec
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
---

---

And then call oe_order_pub.Process_Order

Please refer my Post
CreateOrderAPI
for the info. on  how to use
l_header_rec

l_line_tbl
in  OE_ORDER_PUB.

Hope that helps.

Thanks

Queries to Drive price List /Qualifiers/Modifiers/Conext/Segments

 Below queries are for Oracle Applications Order Management and Advance Pricing Modules.

 

SELECT  l.list_line_id,q.qualifier_grouping_no,
      q.qualifier_id, q.qualifier_context, q.qualifier_attr_value,
      q.comparison_operator_code,q.qualifier_precedence,q.qual_attr_value_from_number,
      q.qualifier_attribute,q.end_date_active,l.end_date_active,h.end_date_active
    FROM
      qp_list_headers_all h,
      qp_list_lines l,
      qp_qualifiers q
    where h.list_header_id = l.list_header_id
    and h.list_header_id = q.list_header_id
    and h.list_header_id = &list_id -- Price List Header ID or Modifier header ID
    and NVL(h.end_date_active,sysdate) >= sysdate
    and NVL(l.end_date_active,sysdate) >= sysdate
    and NVL(q.end_date_active,sysdate) >= sysdate;

 

 

select q.qualifier_id,q.qualifier_context,q.qualifier_attribute,qualifier_attr_value ,
ct.prc_context_id, qs.segment_code
from qp_qualifiers q , qp_prc_contexts_b ct , qp_segments_b qs
where q.list_header_id = &ListHeaderID   --PriceList Header ID
and ct.prc_context_type ='QUALIFIER'
and q.qualifier_context = ct.prc_context_code
and qs.prc_context_id =  ct.prc_context_id
and qs.segment_mapping_column = q.qualifier_attribute

 

 

Queries to connect RA_CUSTOMER_TRX_ALL andOE_ORDER_HEADERS_ALL/LINES_ALL

SELECT rt.trx_number,rt.interface_header_attribute1,rt.interface_header_context,h.order_number from
RA_CUSTOMER_TRX_ALL rt , oe_order_headers_all h
where 1=1
and interface_header_context = 'ORDER ENTRY'
and interface_header_attribute1 = to_char(h.order_number)



SELECT rl.customer_trx_line_id,rl.customer_trx_id,rl.line_number Invoice_line_num,
rl.interface_line_attribute1,h.order_number, l.line_id, rl.sales_order_line
from
RA_CUSTOMER_TRX_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order = h.order_number


SELECT rl.interface_status,rl.line_number Invoice_line_num,
h.order_number, l.line_id
from RA_INTERFACE_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order_line IS NOT NULL;

AIA – Application Integration Architecture ( Concept)

 AIA – Application Integration Architecture
Oracle Application Integration Architecture is application Independent approach to build Integration and business process flow, it doesn’t matter what the whether application is

• Oracle e-business suite
• Microsoft
• SAP
• 3rd Party

AIA is a complete architecture (pre-build) for orchestrating agile, user-centric business process across the enterprise applications.

With AIA Oracle let the customer to enable best –in – class applications to work together as a suite, thus allows organizations to utilize the applications of their choice to create composite business process that are unique and best to their business and that to on a flexible Service Oriented architecture (SOA).It is Integration of reusable components ( Integrating not only processes, but the data and UIs too).

Above all, all the AIA products are supported by Oracle.

Oracle iStore and Advance Pricing Integration

Recently I was working with my client to build a custom solution to Integrate iStore with Pricing.

Requirement was, Price in the iStore catalog should be same as that of Order Management .Not a big deal , that was what I said whey they called me , but letter I have realized that when we create Sales Order in Order Management we have all the information that we need like
1.    Customer Party Details
2.    Bill – To /Ship – To
3.    Order Type
4.    Price List
5.    Shipping Method

And based on above information, Qualifier /modifier got triggered and pricing engine derived the Price for Item. where as in iStore Catalog we don't have these attributes/fields available, more over customer has complex business requirements for pricing and for that he has lot of custom qualifiers, and these qualifiers have no issues in Order Management (as OM support all the standard and custom Pricing Qualifiers) , but using those custom qualifier in iStore is not available in out of box solution.

In iStore Catalog at the time of Catalog we have very limited Information available
1.    Customer Name and Party Name
and based on this information none of the custom qualifiers  conditions satisfied and thus there was lot of price difference in iStore Catalog and the iStore Cart (in Cart price was same as  Order Management where as in case of Catalog it was different).

To fulfill this requirement, we quickly designed one UI where custom enter all the attributes ( like the above 5) , and then I developed an custom process to read the values from custom UI and search through the qualifer in advance pricing and get the qualifiers and feed that to QP_PREQ_PUB.PRICE_REQUEST , and as expected value return by the API was same as price return by the Order management.

In face we latter scrap the custom UI and used the lookup code to capture all the  attributes on which customer has build the qualifiers.

That’s all for now.

Workflow Background Process not Processing DEFERRED records (Bad Design)

In this post I would like to highlight a common mistake that developer usually made while designing the workflow, some of so serious that it crash WBP (workflow background process).Context of my example is - Oracle Applications...

First -
Wrong flow – If you look into flow from Activity1, activit2,activity4 and END, everything looks look fine logically, but if we look closely into Activity1 has 2 outcomes.
1. Default and
2. Yes
If outcome is Yes then workflow will progress to Activity2 but at the same time we have default outcome from activity1 which will progress the flow to END activity and that’s it, workflow for this sub process (if it flow is part of big process) will come to end and it doesn’t matter whether activity 2 and activity 4 complete or not.

How to customize OM to Recognize the COGS in Past Date

In this post I will provide brief information about how to customized COGS to recognize revenue in Past Period. This Post is more Technical and less Functional. For More Info on COGS please refer my previous posts on COGS
(Oracle Apps)

In R12, COGS got generated when Order line close.
Process - During Close Line Oracle Order Management Call API cst_revenuecogsmatch_grp.receive_closelineevent for COGS

One of parameter for this API is p_event_date and by default Oracle provides SYSDATE for this Parameter (as revenue recognition should be occur when order line close). Further this API insert the Data into cst_revenue_recognition_lines table and value in Parameter (p_event_date) got populated into LAST_EVENT_DATE column of this table.

Effective Date in Oracle Workflow

Recenty I came across a issue where customer has save workflow from workflow builder in database with effective date as past date, Not sure why they did that , but now with all this done,all workflows that were in progress got stuck with ROOT activity complete with error status and result = #Stuck.
Only relief was they did it for a particular type of custom order line flow.

Now the possible solutions are
1.Ask customer to cancel as many these lines as possible and enter again with New workflow.
2.Purge and recreate workflow again for allthose lines where step#1 it is not possible.
3.retry the activity that is next to the activity where line workflow stuck.

ITS error out

Many a times I notice that sales order lines are shipped and closed, but Shipped qty , fulfill qty and invoice qty still not populated in oe_order_lines table , but delivery details are SHIPPED and delivery is close and every Run of ITS trying to pick the Closed delivery for processing and it error out, in such cases look for flag oe_interfaced_flag  in wash_delivery_details and make sure it is Y.As long as it has a value of 'N' ITS will try to pick this delivery for processing. To fix this issues set this flag to Y and then update the order lines for shipped/shipping and invoicing quantities with the sum of DELIVERY DETAILS quantities for that particular order line.


- Posted using BlogPress