Monday, August 20, 2012

Useful queries/code/scripts in Oracle Apps Order Management



Useful queries/code/scripts in Oracle Apps Order Management


Following are some of the most frequently used queries in oracle apps order management:


Order header info
Select * from oe_order_headers_all ooh
where order_number= :p_order_number

Operating unit info
select * from hr_operating_units
where organization_id=oe_order_headers_all.org_id

Order type info
select * from apps.oe_transaction_types_tl
where transaction_type_id=oe_order_headers_all.order_type_id

Price list info
select * from apps.qp_list_headers_tl
where list_header_id=oe_order_headers_all.price_list_id

select * from apps.qp_list_lines
where list_header_id=oe_order_headers_all.price_list_id

Find customer info
select * from hz_cust_accounts hca
where cust_account_id=oe_order_headers_all.sold_to_org_id

select * from apps.hz_parties
where party_id=hz_cust_accounts.party_id

Find Ship to location info
select * from hz_cust_site_uses_all
where site_use_id=oe_order_headers_all.ship_to_org_id

select * from apps.hz_cust_acct_sites_all
where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id

select * from hz_party_sites
where party_site_id=hz_cust_acct_sites_all.party_site_id

Find Bill to location
select * from hz_cust_site_uses_all
where site_use_id=oe_order_headers_all.invoice_to_org_id
         
select * from hz_cust_acct_sites_all
where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id
         
select * from hz_party_sites
where party_site_id=hz_cust_acct_sites_all.party_site_id

actual address
select * from hz_locations 
where location_id=hz_party_sites.location_id

Sales rep id
select name from apps.ra_salesreps_all salerep  where
salesrep_id = oe_order_headers_all.salesrep_id  and rownum =1

Payment terms
select name from apps.ra_terms_tl
where term_id =oe_order_headers_all.payment_term_id
and language = 'US'

Order source
select name from apps.oe_order_sources
where order_source_id= oe_order_headers_all.order_source_id
and enabled_flag= 'Y'

Order Source Reference
select orig_sys_document_ref from oe_order_headers_all ooh
where order_number='&oracle order number'

FOB Point Code
select lookup_code from ar_lookups
where lookup_type = 'FOB' and enabled_flag = 'Y'
and upper(meaning) = upper(oe_order_headers_all.fob_point_code)

Freight terms
select lookup_code from apps.oe_lookups
where upper (lookup_type) = 'FREIGHT_TERMS'  and enabled_flag = 'Y'
and upper (lookup_code) = upper (oe_order_headers_all.freight_terms_code)

For sales channel code validation
select lookup_code from apps.oe_lookups
where lookup_type = 'SALES_CHANNEL' and enabled_flag = 'Y'
upper(lookup_code) = upper(oe_order_headers_all.sales_channel_code)

Ship method
select ship_method_code from wsh.wsh_carrier_services
where ship_method_code = oe_order_headers_all.shipping_method_code

Warehouse Info
select * from org_organization_definitions
where organization_id = oe_order_headers_all.ship_from_org_id

Sales order Lines Details
select * from apps.oe_order_lines_all
where header_id=oe_order_headers_all.header_id

Transactional currency code
select ota.price_list_id, qhb.currency_code
from ont.oe_transaction_types_all ota, qp.qp_list_headers_b qhb
where ota.transaction_type_id = oe_order_headers_all.order_type_id
and ota.price_list_id = qhb.list_header_id(+)
and NVL(qhb.list_type_code, 'PRL') = 'PRL'
and qhb.currency_code =oe_order_headers_all.transactional_curr_code

Item info
select * from apps.mtl_system_items_b
where segment1 like oe_order_lines_all.ordered_item
and organization_id=oe_order_lines_all.ship_from_org_id

UOM
select uom_code from inv.mtl_units_of_measure_tl
where upper(uom_code)= upper(oe_order_lines_all.order_quantity_uom)
and language= 'US' and nvl(disable_date, (sysdate + 1)) > sysdate

Item type code validation
select lookup_code from apps.oe_lookups
where upper(lookup_type) = 'ITEM_TYPE'
and enabled_flag = 'Y'
and upper(lookup_code)= oe_order_lines_all.item_type_code

On hand quantities
select * from apps.mtl_onhand_quantities
where inventory_item_id=oe_order_lines_all.inventory_item_id
and organization_id=oe_order_lines_all.ship_from_org_id

Shipping
select * from wsh_delivery_details
where source_header_id=oe_order_headers_all.header_id

select * from wsh_delivery_assignments
where delivery_detail_id=wsh_delivery_details.delivery_detail_id

select * from wsh_new_deliveries
where delivery_id=wsh_delivery_assignments.delivery_id

select * from wsh_delivery_legs
where delivery_id=wsh_new_deliveries.delivery_id

select * from wsh_trip_stops wts
where stop_id=wsh_delivery_legs.pick_up_stop_id

select * from wsh_trips wt
where trip_id=wsh_trip_stops.trip_id

select * from org_organization_definitions
where organization_id = wsh_new_deliveries.organization_id

Material transactions
select * from mtl_material_transactions
where inventory_item_id=oe_order_lines_all.inventory_item_id
and organization_id=oe_order_lines_all.ship_from_org_id

select * from mtl_transaction_types
where transaction_type_id = mmt.transaction_type_id

select * from apps.mtl_txn_source_types
where transaction_source_type_id= mmt.transaction_source_type_id
mmt = mtl_material_transactions


Join between OMWSHAR Tables
SELECT ooh.order_number
              ,ool.line_id
              ,ool.ordered_quantity
              ,ool.shipped_quantity
              ,ool.invoiced_quantity
              ,wdd.delivery_detail_id
              ,wnd.delivery_id
              ,rctl.interface_line_attribute1
              ,rctl.interface_line_attribute3
              ,rctl.interface_line_attribute6
              ,rct.org_id
              ,rct.creation_date
              ,trx_number
              ,rctl.quantity_ordered
              ,rct.interface_header_context
  FROM oe_order_headers_all ooh
             ,oe_order_lines_all ool
             ,wsh_delivery_details wdd
             ,wsh_new_deliveries wnd
             ,wsh_delivery_assignments wda
             ,ra_customer_trx_all rct
             ,ra_customer_trx_lines_all rctl
 WHERE ooh.header_Id=ool.header_id
      AND wdd.source_header_id=ooh.header_id
      AND wdd.delivery_detail_Id=wda.delivery_detail_id
      AND wda.delivery_id=wnd.delivery_id
      AND rctl.interface_line_attribute1=to_char(ooh.order_number)
      AND rctl.interface_line_attribute6=to_char(ool.line_id)
      AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)
      AND rctl.customer_trx_id=rct.customer_trx_id
      AND rct.interface_header_context='ORDER ENTRY'



Purchase release concurrent program will transfer the details from OM to PO requisitions interface. The following query will verify the same:
SELECT interface_source_code,

           interface_source_line_id,
           quantity,
           destination_type_code,
           transaction_id,
           process_flag,
           request_id,
           TRUNC (creation_date)
  FROM po_requisitions_interface_all
 WHERE interface_source_code = 'ORDER ENTRY'
   AND interface_source_line_id IN (SELECT drop_ship_source_id
                                      FROM oe_drop_ship_sources
                                     WHERE header_id = &order_hdr_id
                                       AND line_id = &order_line_id);

The following sql is used to review the requisition, sales order, and receipt number. It shows the joins between various tables in Internal Sales order (ISO)

SELECT porh.segment1,
           porl.line_num,
           pord.distribution_num,
           ooh.order_number
           sales_order,
           ool.line_number so_line_num,
           rsh.receipt_num,
           rcv.transaction_type
  FROM oe_order_headers_all ooh,
          po_requisition_headers_all porh,
          po_requisition_lines_all porl,
          po_req_distributions_all pord,
          oe_order_lines_all ool,
          po_system_parameters_all posp,
          rcv_shipment_headers rsh,
          rcv_transactions rcv
 WHERE ooh.order_source_id = posp.order_source_id
   AND porh.org_id = posp.org_id
   AND porh.requisition_header_id = ool.source_document_id
   AND porl.requisition_line_id = ool.source_document_line_id
   AND porh.requisition_header_id = porl.requisition_header_id
   AND porl.requisition_line_id = pord.requisition_line_id
   AND porl.requisition_line_id = rcv.requisition_line_id
   AND pord.distribution_id = rcv.req_distribution_id
   AND rcv.shipment_header_id = rsh.shipment_header_id

Friday, August 10, 2012

REP-0613: Value does not match mask 'DD-MON-RR'


REP-0613: Value does not match mask 'DD-MON-RR'


Oracle Reports Date Handling When you register the report in Oracle apps. 

Use the value set FND_STANDARD_DATE Value set in the Oracle apps front end.
and in the user parameters defined in the report use the following as input mask against the date field.for the parameter as  RRRR/MM/DD HH24:MI:SS.

Then the Input to the code will be in the format of 'DD-MON-RR' and it is a date data type.


v_date_where    VARCHAR2(2000) := NULL;
begin
  IF :P_FROM_DT IS NOT NULL THEN
-- 06/30/2004 NPRASAD Commented the following 
--   v_date_where := ' AND trunc(ctb.creation_date)  >= to_date('||''''||to_char(:P_FROM_DT, 'DD-MON-RR')||''''||','|| '''DD-MON-RR'||''')-14 ';
  v_date_where := v_date_where || ' AND trunc(ctri.creation_date) >= to_date('||''''||to_char(:P_FROM_DT, 'DD-MON-RR')||''''||','|| '''DD-MON-RR'||''') ';
  END IF;
  IF :P_TO_DT IS NOT NULL THEN
  v_date_where := v_date_where || ' AND trunc(ctri.creation_date) <= to_date('||''''||to_char(:P_TO_DT, 'DD-MON-RR')||''''||','|| '''DD-MON-RR'||''') ';
  END IF;  
  srw.message(100,'Date where:'|| v_date_where);  
  return (v_date_where);
exception
  when others then
  srw.message (101,'Error! CF_DATE_WHERE failed '||sqlerrm);
  return (NULL);