Query to get Item Categories

Query to get Item Categories

SELECT MCB.*
    FROM
        MTL_CATEGORY_SETS_TL CST,
        MTL_ITEM_CATEGORIES MIC,
        MTL_CATEGORIES_B MCB
    WHERE  MIC.CATEGORY_ID = MCB.CATEGORY_ID
    AND MIC.CATEGORY_SET_ID = CST.CATEGORY_SET_ID
    AND MIC.ORGANIZATION_ID = 0
    AND CST.CATEGORY_SET_NAME =  '&item_category_name'
   AND MIC.INVENTORY_ITEM_ID = DL.INVENTORY_ITEM_ID;

Query to Join Order Management Tables with Oracle Quoting tables

Query to Join Order Management Tables with Oracle Quoting tables

SELECT ooh.header_id
         , ooh.order_number
         , ool.line_Id
         , ool.ship_from_org_id organization_id
         , ool.inventory_item_id
         , ool.ordered_quantity
         , ool.order_quantity_uom
     , ool.line_type_id  xx_line_type_id
     , qtl.order_line_type_id
   , ool.request_date
     , ool.creation_date
     , ool.ship_to_org_id
     , ool.sold_to_org_id
      , ool.subinventory

Source Code to create customer contact with TCA api

Code to  create customer contact with TCA api


CREATE OR REPLACE PACKAGE BODY TCA001 AS
PROCEDURE CUST_CONTACT_RELATIONSHIP --(p_party_parent_id  hz_parties.party_id%TYPE,
IS
p_org_contact_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
x_org_contact_id NUMBER;
x_party_rel_id NUMBER;
x_party_id NUMBER;
x_party_number VARCHAR2(2000);
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
            p_org_contact_rec.decision_maker_flag := 'Y';
            p_org_contact_rec.created_by_module             := 'HZ_CPUI';--p_cust_rec.created_by_module;
            p_org_contact_rec.party_rel_rec.subject_id         := 324217;--p_party_id;            
            p_org_contact_rec.party_rel_rec.subject_type         := 'PERSON';              
            p_org_contact_rec.party_rel_rec.subject_table_name     := 'HZ_PARTIES';

Query to Get TOP 5 Item Ordered by Customer

Below is query to Get TOP 5 Item Ordered by Customer in last 250 days




select ordered_item,Total_Qty_Ordered, sold_to_org_id
FROM (
SELECT  l.ordered_item,SUM(l.ordered_quantity) Total_Qty_Ordered, l.sold_to_org_id,RANK() OVER ( ORDER BY SUM(l.ordered_quantity) DESC) "RANK"
  from oe_order_lines_all l
   where 1=1--h.header_id = l.header_id
   and l.creation_date between sysdate-250 and sysdate
   and l.sold_to_org_id = &Enter_Cust_account_id
   group by l.ordered_item,l.sold_to_org_id
)
where  RANK <= 5
order by RANK ASC

High level setups necessary to enable MOAC

Below are the Flow Chart about the High level setups necessary to enable MOAC, for more information on MOAC, refer my post of same subject .

<<You can search for my posts related to MOAC in above search window >>

Steps to Process data in Counter Reading Open Interface - 2

In continuation to discussion about how to import the Counter Reading in Oracle Application using Interface table , Steps to Process data in Counter Reading Open Interface - 1 , in this Post I will highlight the APIs that we can ti import the Counter Reading in Oracle application interface.


CSI_COUNTER_PUB
CSI_COUNTER_READINGS_PUB

Apart from importing the counter reading , many a time we have requirement to update the values in counter reading , this can be achieved by means of Open Interface by populating table CSI_CTR_READINGS_INTERFACE or by means of above 2 APIs.

Apart from that if we have any requirement JUST to update the ATTRIBUTES columns for counters then we have one additional option along with 

Steps to Process data in Counter Reading Open Interface - 1

In this Post I will explain what all are the Steps to process the Counter Reading data from the Counter Reading Open interface table.

With Counter Readings Open Interface we have the ability to import significant volumes of data into counter readings and counter property readings tables.The data may be from multiple source including external legacy systems.

Step by Step details about how to process the data from the Counter Reading Interface table .

1. Load data into the counter readings interface tables.SI_CTR_READINGS_INTERFACE  , Process Status Column in this table can has any of the below  3 values
1. P - Processed
2. E - Error
3. R - Ready for processing.
2. Run the Install Base - Counter Readings Open Interface concurrent program that
   reads, validates, and posts the data from the interface tables into the counter
   readings in Oracle Install Base.
3. STEPS to Run the Concurrent Program

   -----------------------
   Navigate to 
   1. (R) Install Base Administrator 
   2. (N) Others > Requests 
   3. Single Request > OK 
   4. Search for 'Install Base - Counter Readings Open Interface' 
   5. Batch Name = specific batch name  
   6. From Transaction Date = SYSDATE 
   7. To Transaction Date = SYSDATE 
   8. Purge Processed Records = Yes 
   9. Error Reprocess Options = NULL 
   10. OK 
   11. Submit 
   
   
4. 
5. Please note that as of now System will let you process only one batch at a time ,it will not let you process multiple batches together << We at Bizinsight Consulting have a Custom Solution to process multiple batches together , in case you need additional information about this Multi batches process solution , feel free to contact us at sgupta@bizinsightinc.com >>.
6. If you want the processed records to be cleared from the interface tables after
   processing, select Yes for Purge Processed Records.
   

How to define and progress Service Items in Oracle Order Management - 1

This is First Post on series "How to define and progress Service Items in Oracle Order Management ", stay tune in for more post on same topic.

Order Management enables you to order services from its Sales Order workbench. You

can order services for product items currently being ordered, i.e. immediate services, or

you can order services for already installed product items or delayed services.

Order Management enables you to:

1. order service lines along with the product lines.
2. import service lines and service orders using order import.
3. perform applicable operations that the application applies to any other order, including billing.
4. enter service for all serviceable options in a configuration once.

Order Management I workflow driven , so as the case for the service line too.

Order Management enables you to utilize Oracle Workflow to manage your service lines with service item types. Service lines are non-schedulable and non-shippable, to implement service items ,assign a  Bill Only  workflow process that does not include these two functions  ( as Shown below)

How to Assign Work flow to line type - You can Assign work flow to Order line type from Oracle Workflow Assignments window in Order Transaction type UI.

Once  bill only  workflow assigned to Service line, any transaction for Service line will use the same work flow. A s soon as you book the Order , Service line progress to Invoicing activity , as there are no scheduling or Shipping activities involves( and for how to delay service line from invoicing till product line ship please refer below Shipping Section , and regarding how service line interact with the Service contract , will explain that in coming posts)

Note - When you apply duration-related changes to the service order line, Order Management

automatically applies those changes to the associated service order lines in the configuration.

Shipping

Order Management, Shipping, and Oracle Service provide you with the ability to  synchronize the start of a service program with the shipment of an associated product. You can define the Service Starting Delay when you define serviceable products in Oracle Inventory. The Service Starting Delay represents the time in days an included warranty is offset to commence after the shipment date.

The start date of the support service is the ship date plus the starting delay. The end date is calculated by adding the duration to the start date of the support service. This is applicable for included

warranties and not extended warranties (service programs).

Configuration Validation Hold

 Configuration Validation Hold
In Oracle Order management , if we enter an ATO Model line to a BOOKED sales order , Standard out of box functionality will Apply Configuration Validation Hold on Model line ( If Oracle Configurator is installed) to prevent further processing of the line.