Query to connect cz_config_details_v cz AND oe_order_lines_all tables

SELECT cz.*
         FROM   cz_config_details_v cz, oe_order_lines_all oe
         WHERE  oe.line_id = p_model_line_id
         AND    oe.config_header_id = cz.config_hdr_id
         AND    oe.config_rev_nbr   = cz.config_rev_nbr
         AND    oe.component_code   = cz.component_code;

Query to Check the Pricing Event for Oracle Advance Pricing

SELECT count(*), pricing_event_code
   FROM QP_EVENT_PHASES
   group by pricing_event_code
  

Workflow Query to Check if Order Header is Eligible forBooking or Not

SELECT  'Eligible for Booking '
   FROM WF_ITEM_ACTIVITY_STATUSES WIAS
       , WF_PROCESS_ACTIVITIES WPA
   WHERE WIAS.item_type = 'OEOH'
     AND WIAS.item_key = to_char(&Enter_order_header_id)
     AND WIAS.activity_status = 'NOTIFIED'
     AND WPA.activity_name = 'BOOK_ELIGIBLE'


   SELECT  'Error - Order Header not Eligible for Booking'
     FROM WF_ITEM_ACTIVITY_STATUSES WIAS
       , WF_PROCESS_ACTIVITIES WPA
      WHERE WIAS.item_type = 'OEOH'
      AND WIAS.item_key = to_char(&Enter_order_header_id)
      AND WIAS.activity_status = 'ERROR'
      AND WPA.activity_name = 'BOOK_ORDER'
      AND WPA.instance_id = WIAS.process_activity;

Queries to Get Customer Relationship and Contact in Oracle TCA(Part-2)

Some More Interesting TCA Queries .

Query #1
      SELECT cust_account_role_id, status
      FROM hz_cust_account_roles
      WHERE party_id = p_party_id
      AND cust_account_id = p_cust_account_id
      AND role_type ='CONTACT'
      AND cust_acct_site_id is null
     and status = 'A';

Query #2



 SELECT par.party_id
      FROM hz_relationships par,
           hz_org_contacts     org ,
           hz_cust_accounts acc
      WHERE org.party_relationship_id = par.relationship_id
      AND org.org_contact_id  = p_org_contact_id
      and par.status = 'A'
      and (sysdate between nvl(par.start_date, sysdate) and nvl(par.end_date, sysdate))
      AND acc.cust_account_id = p_cust_account_id
      AND par.object_id = acc.party_id
      AND acc.status = 'A'
      AND (sysdate BETWEEN NVL(acc.account_activation_date, sysdate) AND
                           NVL(acc.account_termination_date, sysdate));

Queries to drive Customer Relation Ship , Role , Account Info in Oracle TCA(Part-1).

Some Interesting TCA Queries

Query #1 
select a.object_id
from
hz_relationships a, hz_cust_accounts  b
where  a.party_id = l_party_id
and a.object_id = b.party_id
and b.cust_account_id = p_cust_account_id
and a.status = 'A'
and (sysdate between nvl(a.start_date, sysdate)
and nvl(a.end_date, sysdate))
AND b.status = 'A'
AND (sysdate BETWEEN NVL(b.account_activation_date, sysdate) AND
                     NVL(b.account_termination_date, sysdate));

Query #2
  select a.cust_account_id
    from hz_cust_accounts a, hz_cust_account_roles b
    where a.cust_account_id = b.cust_account_id
    and b.party_id = p_party_id
    and b.role_type = 'ACCOUNT_USER'
    AND b.status = 'A'
    AND a.status = 'A'
    AND (sysdate BETWEEN NVL(a.account_activation_date, sysdate) AND
                         NVL(a.account_termination_date, sysdate));

Query to get Customer Role in TCA

Below is simple query to get the Customer Role Details in TCA

select a.cust_account_role_id,a.*
from
hz_cust_account_roles a, hz_role_responsibility b, hz_cust_acct_sites_all c
where
a.role_type = 'CONTACT'
and a.party_id = p_party_id
and a.cust_account_id = p_cust_account_id
and a.cust_acct_site_id = c.cust_acct_site_id
and a.cust_account_id = c.cust_account_id
and c.party_site_id = p_party_site_id
and a.cust_account_role_id = b.cust_account_role_id
and responsibility_type = p_acct_site_type;

Script to Create ATO Model in Oracle Order Management with OE_ORDER_PUB

I have spent some good amount of time to write script to create ATO Model in Oracle Order Management by means of Process Order API (OE_ORDER_PUB)  , so thought of Sharing with my readers.

Please feel free to share your thoughts.

My Test case is - 

Model

     Class

       Option

-- API to create Sales Order and Return Order.

declare

l_api_version_number NUMBER := 1;

l_return_status VARCHAR2(2000);

l_msg_count NUMBER;

l_msg_data VARCHAR2(2000);

/*****************PARAMETERS****************************************************/

l_debug_level number := 5; -- OM DEBUG LEVEL (MAX 5)

l_org number  := 204; -- OPERATING UNIT

l_user number := 1318; -- USER

l_resp number := 21623; -- RESPONSIBLILTY

l_appl number := 660; -- ORDER MANAGEMENT

/***INPUT VARIABLES FOR PROCESS_ORDER API*************************/

l_header_rec oe_order_pub.header_rec_type;

l_line_tbl oe_order_pub.line_tbl_type;

l_action_request_tbl oe_order_pub.Request_Tbl_Type;

/***OUT VARIABLES FOR PROCESS_ORDER API***************************/

l_header_rec_out oe_order_pub.header_rec_type;

l_header_val_rec_out oe_order_pub.header_val_rec_type;

l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;

l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;

l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;

l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;

l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;

l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;

l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;

l_line_tbl_out oe_order_pub.line_tbl_type;

l_line_val_tbl_out oe_order_pub.line_val_tbl_type;

l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;

Query to connect Order Sales Order Header and MTL Sales Order Table

Query to connect Oracle Sales Order Header (OE_ORDER_HEADERS_ALL) and MTL Sales Order(MTL_SALES_ORDER)  Table

 SELECT s.sales_order_id,s.segment1, s.segment2,s.segment3,h.order_number,h.flow_status_code,
 h.sold_to_org_id,h.open_flag
   FROM mtl_sales_orders s,
        oe_order_headers_all h
   WHERE s.segment1 = TO_CHAR(h.order_number)
   AND s.segment2 = &order_type_name   --Enter Transaction Type
   AND s.segment3 = &source_code       -- Enter Order Source

Simple Query to connect Receiving Transaction table with Oracle Order line table for Drop Ship Sales Order.

Query to connect Receiving Transaction table with Oracle Order line table for Drop Ship Sales Order.

 SELECT l.line_id,l.header_id,rt.transaction_id,
              odss.line_location_id, odss.po_header_id, odss.requisition_line_id,
              rt.po_header_id,rt.po_line_id,l.flow_status_code,NVL(l.shipped_quantity,0),
              l.ordered_quantity
       FROM   oe_order_lines_all     l,
              oe_drop_ship_sources   odss,
               rcv_transactions       rt
       where  l.line_id = odss.line_id
       and    l.source_type_code = 'EXTERNAL'
       and    odss.po_header_id = rt.po_header_id
       and    odss.po_line_id = rt.po_line_id
       and    l.open_flag = 'Y'               
       and    l.flow_status_code = 'AWAITING_RECEIPT'
       and    odss.line_location_id = rt.po_line_location_id
       and    rt.transaction_id = &Enter_Receiving_transaction_id

Hope that helps

SHIP SET in Oracle Order Management

Ship Set , Order Lines and Delivery Details in OM

In Oracle Order Management When we ever add Order line in a SHIP_SET , I will add the data into OE_SETS tables as well as update OE_ORDER_LINES_ALL table for ship_set_ID.After Booking event ship_set_id got populated in WSH_DELIVERY_DETAILS Table.

Now if we TRY to remove the line from the SHIP SET , system will update the WSH_DELIVERY_DETAILS table and OE_ORDER_LINES_ALL table and set the ship_set_id column in both the tables to NULL.

BUT please note that once delivery is CLOSED/SHIPPED , system will not let you remove the line from SHIP_SET ( And why we need that , suppose we have say 5 lines in the SHIP SET and these lines assocaited with say 2 deliveries and if 1 of the of the delivery is closed and we want to remove the lines from the ship set the system will not let yoy do that). Only Option possible is DELETE from OE_SETS and UPDATE on WSH_DELIVERY_DETAILS and OE_ORDER_LINES_ALL ( Off course best practices from the Oracle will not allow us to do any DML on base Oracle Apps table).

Hope that Helps.

WF_ENGINE.CREATE_PROCESS

WF_ENGINE.CREATE_PROCESS- this is the process/API that on execution will insert the data in wf_items table
Well I guess this is my shortest post
;)


- Posted using BlogPress

How to enable Debug for Oracle Order Management

Below are simple steps to debug in oracle application for Order management.
Navigate to sysadmin > profiles>
OM debug level = 5
navigate back to OM responsibility and then open the process for which you want to generate debug.let say you want to debug the BOOK Order process, in that case open order and just befor you press book button , navigate to tools > Check the debug check box .Checking the debug check box will popup the dialog box with the complete path of the debug file path. Once your test case is done , disable the debug by unchecking the debug check box from tools menu, and then retrieve the debug file from unix or run the concurrent program diagnostic debug file .
Hope that helps.


- Posted using BlogPress

WF_Engine complete activity and Retry activity

Oracle workflow has provided a very powerful workflow engine API WF_ENGINE , with this API we can perform RETRY , SKIP Or COMPLETE workflow activity. In this post I will add few lines to explain what is the difference between RETRY and and COMPLETE activity. For both of these WF_engine API uses different procedures.
For complete activity it uses
wf_engine.completeactivit and for RETRY it uses wf_engine.handleError . Fundamental difference between thee 2 are if root activity for a process is already CLOSED and we do completeactivity then it will have no impact on root activity , it will just complete the activity for which you have executed completeactivity , on the contrary if root activity is closed and you execute RETRY with wf_engine.handleError then retry will open the root activity too and then retry the activity for which you have executed it.
Another difference for completeactivity activity should be in NOTIFIED status, but same is not true for RETRY via wf_engine.handleError.
Hope that helps

dures
- Posted using BlogPress

Query to get the lines where cancel as well as open Flag = Y

Query to get the lines where Cancel flag = Y,Cancel Qty <> 0  but Open Flag = Y too

SELECT oh.header_id,
oh.order_number,oh.cancelled_flag,oh.open_flag,oh.booked_flag,
oh.FLOW_STATUS_CODE,ol.LINE_ID,ol.CANCELLED_FLAG,ol.OPEN_FLAG, ol.BOOKED_FLAG

FROM oe_order_headers_all oh, oe_order_lines_all ol
WHERE oh.header_id=ol.header_id
and ol.open_flag ='Y' and oh.open_flag ='Y'
and oh.flow_status_code='BOOKED'
and NVL(ol.cancelled_quantity,0) > 0
and NVL(ol.cancelled_flag,'N') ='Y'
and NVL(ol.ordered_quantity,0) = 0
AND ol.flow_status_code ='CANCELLED

Demo for Mobile ADF Pages for Oracle Order Management

Working On Prototype to develop Mobile Pages with Oracle ADF around Oracle Order Management.

Enclosed is Sample, If anyone doing similar work please share idea/tips about how to Approch the Development of ADF Mobile Pages.

Query to get Contact Assocaited with Party ID and Account ID

SELECT party.party_type,party.party_name Contact_Name,
rel.relationship_code,rel.relationship_id,
--org_cont.*,cont_point.*,
--party.party_id,rel_party.party_id,rel.subject_id,rel.object_id, rel.party_id,
SUBSTRB(PARTY.PERSON_FIRST_NAME,1,40) FIRST_NAME ,
SUBSTRB(PARTY.PERSON_LAST_NAME,1,50) LAST_NAME ,
PARTY.CUSTOMER_KEY CONTACT_KEY ,
REL_PARTY.EMAIL_ADDRESS ,
-- PARTY.PARTY_ID ,
ORG_CONT.ORG_CONTACT_ID ,
CONT_POINT.CONTACT_POINT_ID ,
ORG_CONT.CONTACT_NUMBER
FROM HZ_CONTACT_POINTS CONT_POINT,
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_PARTIES REL_PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT ,
HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL_PARTY.PARTY_ID = REL.PARTY_ID
AND CONT_POINT.OWNER_TABLE_ID(+) = REL_PARTY.PARTY_ID
AND CONT_POINT.PRIMARY_FLAG = 'Y'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
and ROLE_ACCT.cust_account_id = &Enter_Account_id
and rel.object_id = &Enter_Party_id
AND rel.subject_table_name ='HZ_PARTIES'
AND rel.object_table_name ='HZ_PARTIES'
and party.status ='A'
and rel_party.status ='A'
and rel.status ='A'

How to Disable a Processing Constraints in Oracle Order Management ( If allowed).

This is a Short post Supported by Presentation about How to disable a Processing Constraints ( If Allowed) in Oracle Order Management.

I am posting this presentation as one of reader asked about it .

How to disable Processing Constraints in Oracle Order Management

View more presentations from eoracle apps.

Query to get Qualifier associated with Customer Account

Below is simple  Query to get Qualifier associated with Customer Account

 

SELECT 

      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

       from  qp_qualifiers q

     where qualifier_context = 'CUSTOMER'

       and  q.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2'

       and q.comparison_operator_code = '='

       and q.qualifier_attr_value = '&Enter_Customer_account_ID'

ATO and PTO configurations except options of ATO model do not support decimal quantities"

ATO and PTO configurations except options of ATO model do not support decimal quantities"

We usually encounter this error we are trying to split the model in a such a manner that it result in is model quantity getting split into fractions , pls note that that is not allowed/supported in Oracle Applications.

Please note that Oracle apps will not allow Model/option has quantity in fraction and if user/system try to split the model that result in Model qty in fraction, it leads to error “ATO and PTO configurations except options of ATO model do not support decimal quantities"”

 Let’s say an example.
1.1 Qty = 100  (Model)
1.1.1 Qty = 200  (option)
1.1.2 Qty =100(Option).

Based on above details ration between 1.1 and 1.1.1 is 1:2 and that of 1.1 and 1.1.2 is 1:1

If we try to ship 1.1.1 with qty says 125 then system will try to split the complete model in same proportion and try to updater the model qty in fraction and thus leads to this Error.

Solution – If you are getting this error because of the Back order of some of the qty, in that case I should suggest you not to ship the delivery details rather wait for the Supply of the material and then Ship the item in a manner that not leads to fraction qty for Model.

This field cannot be updated, as there are Open Sales Order Lines

This field cannot be updated, as there are Open Sales Order Lines
User usually encounter this error if they try to deactivate an item and there exists open order lines/deliveries for the same item. And this type of error message are easy to handle and debug, and possible solutions are to close/cancel the order lines and corresponding delivery details (if any) but many a times I have notice that when user try to deactivate an old item (not used for months /years) , they get same error message even when there are no open order lines , in such scenario my recommendations are to just check in the delivery details table (WSH_DELIVERY_DETAILS) and see if there are any record for this item that has Release Status as

   1. Back Order
   2. ready to released

If so, then that is the culprit, Cancel those delivery details and try to deactivate the item and now it should work.

If you are thinking why on earth order line is closed/cancelled but the delivery detail is still open/ready to release/back ordered ,then believe it is not worth to spend time on this as this is a data corruption (Unless you have 100+ records of this type).

So just cancel the delivery details /Lines and then deactivate the item

Hope that helps