Monday, June 20, 2011

How to find order header information

SELECT ooh.order_number, ooh.header_id hdr_id,
       DECODE (ooh.order_category_code,
               'MIXED', 'Mixed',
               'ORDER', 'Regular',
               'RETURN', 'Return',
              ) CATEGORY,
       ott.NAME ord_typ, ooh.ordered_date, ooh.transactional_curr_code curr,
       os2.NAME sdt, ooh.source_document_id sdi, os1.NAME os,
       ooh.orig_sys_document_ref osdr, ooh.sold_to_org_id sold_to,
       ooh.invoice_to_org_id invoice_to, ooh.cust_po_number cust_po,
       ooh.payment_type_code pay_type, ooh.flow_status_code hdr_flow_status,
       ooh.open_flag, ooh.booked_flag, ooh.cancelled_flag,
       NVL (ooh.upgraded_flag, 'N') upgraded_flag
  FROM oe_order_headers_all ooh,
       oe_transaction_types_tl ott,
       oe_order_sources os1,
       oe_order_sources os2
 WHERE ooh.order_number = &order_number
   AND ooh.order_type_id = ott.transaction_type_id
   AND ooh.order_source_id = os1.order_source_id(+)
   AND ooh.source_document_type_id = os2.order_source_id(+)
   AND ott.LANGUAGE = (SELECT fl.language_code
                         FROM fnd_languages fl

                        WHERE fl.installed_flag = 'B');

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect