Query to Get Sales Order Total in Oracle Apps

Query to Get Sales Order Total in Oracle Apps

Hi friends, we are going to discuss about the Query to Get Sales Order Total in Oracle Apps. We will share the detail sql query which helps to get the sales order total amount in oracle apps. In oracle apps, sales order total does not stores directly in the sales order table that is the reason we need to calculate the sales order total from the sql query. To get the sales order total from sql query is quite complex job because we need to put many conditions to calculate the right sales order total. Oracle apps has provided very easy method we get the sales order total. Oracle apps has provided the standard api in which we just need to pass the Sales order Header Id and this api automatically get the sales order total for that sales order header id. This is very easy method rather than writing the complex query to find out the sales order total. We will share the complete informations about this api to get sales order total. Please find below the complete detail about Query to Get Sales Order Total in Oracle Apps.

Query to Get Sales Order Total in Oracle Apps

Important API to get the Sales Order Total in Oracle Apps

1.oe_oe_totals_summary.order_totals

Detail PLSQL code to get the Sales Order Total in Oracle Apps

Here below is the plsql code which helps to get the sales order total Amt in oracle apps.

DECLARE

   v_so_header_id                   NUMBER := 988732; --sales order header id----

   v_so_basic_tot                    NUMBER;

   v_so_discount                    NUMBER;

   v_so_charges                     NUMBER;

   v_so_tax                         NUMBER;

   v_so_total_amt                   NUMBER;

BEGIN

   apps.oe_oe_totals_summary.order_totals (v_so_header_id,

                                           v_so_basic_tot,

                                           v_so_discount,

                                           v_so_charges ,

                                           v_so_tax);

   

   v_so_total_amt := v_so_basic_tot + v_so_charges + v_so_tax;

   

   

   DBMS_OUTPUT.put_line ('Sales Order Total Amt'

                            ||v_so_total_amt );

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (   'error in api call '

                            || SUBSTR (SQLERRM, 1, 250));

END;

Detail SQL Query about the sales order details in oracle apps

Here below , is the detail sales order query in oracle apps which helps to extract the sales order information's in oracle apps.

select 

ooha.order_number,

ooha.cust_po_number,

ooha.flow_status_code "Order Status",

ooha.ordered_date,

oola.line_number,

oola.flow_status_code "Line Status",

hp.party_name "Customer Name",

hl.address1||' '||hl.address2||' '||hl.city||' '||hl.state||' '||hl.country  "Customer Address",

rs.name "Sales person",

oola.actual_shipment_date "Actual Ship Date",

rcta.trx_number "AR Invoice Number",

rcta.trx_date "AR Invoice Date",

organization_code "Inventory Org",

oola.ordered_item "Order Item",

oola.pricing_quantity_uom "Item UOM",

msib.description "Item Description",

oola.schedule_ship_date,

oola.pricing_quantity "Quantity"

from oe_order_headers ooha,

oe_order_lines oola,

mtl_system_items_b msib,

org_organization_definitions ood,

hz_party_sites hps ,

hz_locations hl ,

hz_parties hp,

fnd_territories ft,

ra_customer_trx rcta,

ra_salesreps rs,

hz_cust_site_uses hcsua ,

hz_cust_acct_sites hcasa 

where ooha.header_id = oola.header_id

and ooha.org_id =oola.org_id

and ooha.ship_from_org_id =ood.organization_id

AND hl.country = ft.territory_code

and nvl(rcta.interface_header_context,'ORDER ENTRY') = 'ORDER ENTRY'

and to_char(ooha.order_number) = rcta.interface_header_attribute1(+)

and oola.ordered_item = msib.segment1

and oola.salesrep_id = rs.salesrep_id

AND ooha.ship_to_org_id = hcsua.site_use_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hcasa.party_site_id = hps.party_site_id

AND hps.location_id = hl.location_id

AND hps.party_id = hp.party_id

and ooha.ship_from_org_id =msib.organization_id

order by 1,5

Query to Get Sales Order Total in Oracle Apps