Sales order query in oracle apps
This sql query helps to extract the complete sales order information in oracle aps r12. This sql query is giving complete sale order information from Order to cash. It means we will get Sales Order information as well as the Customer Invoice created against the sale order with this sql query. Please find below sales order query in oracle apps.
Tables uses in the Sales order query in oracle apps:-
select distinct
ooha.order_number,
ooha.cust_po_number,
ooha.fob_point_code FOB,
ooha.flow_status_code "Order Status",
ooha.ordered_date,
ooha.booked_date,
oola.line_number,
oola.flow_status_code "Line Status",
ooha.org_id,
hcasa.cust_acct_site_id,
hp.party_name "Customer Name",
hl.city "Customer City",
hl.state "Customer State",
hl.country "Customer Country",
ft.nls_territory "Region",
rs.name "Salesperson",
nvl((select 'Y' from oe_order_holds where header_id =ooha.header_id and line_id = oola.line_id and rownum <=1),'N') "Hold Status",
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 "Item#/Part#",
msib.description "Item Description",
oola.source_type_code "Source Type",
oola.schedule_ship_date,
oola.pricing_quantity "Quantity",
oola.pricing_quantity_uom "UOM"
from oe_order_headers ooha,
oe_order_lines oola,
mtl_system_items_b msib,
org_organization_definitions ood,
ra_salesreps rs,
hz_cust_site_uses hcsua ,
hz_cust_acct_sites hcasa ,
hz_party_sites hps ,
hz_locations hl ,
hz_parties hp,
fnd_territories ft,
ra_customer_trx rcta
where ooha.header_id = oola.header_id
and ooha.org_id =oola.org_id
and oola.ordered_item = msib.segment1
and ooha.ship_from_org_id =msib.organization_id
and ooha.ship_from_org_id =ood.organization_id
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 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(+)
order by ooha.order_number,
oola.line_number;
1 comments:
Thankx
Post a Comment