Useful Order Management Queries

1) To know the details of the Order Header.
--------------------------------------------------------------

SELECT * from oe_order_headers_all WHERE ORDER_NUMBER = ;

2) To know the line details of the Order lines:-
----------------------------------------------------------------

SELECT * FROM OE_ORDER_LINES_ALL WHERE HEADER_ID =

3) To know the reason of cancellation.
-------------------------------------------------------

SELECT reason_code
         FROM   oe_order_lines_history
         WHERE  line_id =
         AND    hist_type_code = 'CANCELLATION';

SELECT attribute1  pos_reason_code
        FROM FND_LOOKUP_VALUES
        WHERE lookup_type = p_lookup_type
        AND LANGUAGE = 'US'
        AND LOOKUP_CODE = ;
        
4) if some discount or adjustment is done for the item in the order line then we can know the original price and selling price.
   ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        
SELECT SUM(DECODE(line_id, , unit_selling_price,
                   DECODE(return_attribute5, , - unit_selling_price, 0))) unit_selling_price,
                SUM(DECODE(line_id, , unit_list_price, 0)) unit_list_price
         FROM   oe_order_lines_all
         WHERE  header_id =
         AND    Nvl(cancelled_flag, 'N') = 'N'
         AND    booked_flag = 'Y'
         AND    attribute7 IS Not Null;

5) To know the number of the days from the cash receipt day for any particular line.
-----------------------------------------------------------------------------------------------------------------------

SELECT floor(sysdate-to_date(To_Char(ar.receipt_date, 'dd-mon-yyyy'))) date_difference
         FROM oe_order_lines_all ol,
              ar_cash_receipts_all ar
         WHERE ol.header_id =
         AND   ol.line_id=
         AND   ol.attribute7=ar.cash_receipt_id
         AND   ar.attribute1=;

6) To know the receipt ID from the based on the header_id (HBC Specfic).
  --------------------------------------------------------------------------------------------------------

select distinct attribute1 from oe_price_adjustments where HEADER_ID =

7) To know the Location exist or not.
------------------------------------------------------

SELECT 1 FROM hr_organization_units_v
     WHERE  location_code = Trim();

8) Deposit_payment_made
  -------------------------------------
The following query is useful to know whether Deposit is made or not.
-----------------------------------------------------------------------------------------------------

SELECT 1 FROM ar_cash_receipts_all r, ar_receipt_methods m
     WHERE  r.attribute1=to_char('')
    AND r.receipt_method_id=m.receipt_method_id
    AND m.name='POS_DEPOSIT';

8.1) Deposit Payment and Order status and Header ID. (HBC Specfic).
-----------------------------------------------------------------------------------------------------

     SELECT header_id "Header ID",
             flow_status_code "Status",
             attribute3 "deposit_amount"
      FROM   oe_order_headers_all
      WHERE  order_number = 1029243
      AND    flow_status_code in ('BOOKED', 'CANCELLED');

8.2) Getting the Line information of the order.
  ---------------------------------------------------------------

SELECT line_id,
LINE_NUMBER,
             Nvl(cancelled_flag, 'N') cancelled_flag,
             open_flag,
             attribute7 receipt_id,
             line_category_code,
             global_attribute10,
             Nvl(attribute10, 'Y') pos_elig_flag,
            return_attribute5 closed_line_adjustment
      FROM   oe_order_lines_all
WHERE  header_id = ;    

8.3) If the Deposit Money is refunded, that can be found from the following query:-
-------------------------------------------------------------------------------------------------------------------

 SELECT 'Y'
      FROM   oe_order_headers_all oeoh
      WHERE  oeoh.header_id =
      AND    oeoh.flow_status_code = 'CANCELLED'
      AND    oeoh.attribute3 IS Not Null
      AND    oeoh.attribute5 IS NOT Null
      AND    oeoh.global_attribute20 IS Null
      AND    NOT EXISTS
             (SELECT 1
              FROM   oe_order_lines_all oeol
              WHERE  oeol.header_id = oeoh.header_id
              AND    oeol.attribute7 IS NOT Null
              AND    oeol.booked_flag = 'Y');

9) pos_payment_made
  ----------------------------------
The following query is useful to know whether payment is made or not.
-----------------------------------------------------------------------------------------------------

SELECT 1 FROM ar_cash_receipts_all r, ar_receipt_methods m
     WHERE  r.attribute1=to_char(p_order_number)
    AND r.receipt_method_id=m.receipt_method_id
    AND m.name='POS_PAYMENT';

10) Adjustment Amount for this order:-
   ---------------------------------------------------

  SELECT SUM(oepj.adjusted_amount)
      FROM   oe_order_lines_all oeol, oe_price_adjustments oepj
      WHERE  oeol.line_id = oepj.line_id
      AND    oeol.attribute7 IS NOT Null
      AND    Nvl(oeol.cancelled_flag, 'N') = 'N'
      AND    oeol.return_attribute5 IS Null
      AND    oeol.open_flag = 'Y'
      AND    Nvl(Upper(oeol.attribute10), 'Y') = 'Y'
      AND    oeol.booked_flag = 'Y'
      AND    oepj.attribute1 IS Null
      AND    oeol.header_id =

11) find out unprocessed lines in ORDER LINES table:-
    ---------------------------------------------------------------------------

SELECT NVL(SUM(DECODE(line_category_code,'RETURN',1,0)),0),
                       NVL(SUM(DECODE(line_category_code,'ORDER',1,0)),0)
            FROM OE_ORDER_LINES_ALL oola
      WHERE header_id=
               AND oola.BOOKED_FLAG = 'Y'
               AND oola.OPEN_FLAG = 'Y'
               AND oola.ATTRIBUTE7 IS NULL
               AND oola.CANCELLED_FLAG = 'N'
AND Nvl(Upper(oola.attribute10), 'Y') = 'Y';

12) Findout unhandled_cancels_exists or not:-
     -------------------------------------------------------------

 SELECT 1
           FROM oe_order_lines_all l, oe_order_headers_all h
           WHERE h.header_id = l.header_id
           AND l.line_id =
           AND l.attribute7 IS NOT NULL  --previously processed
           AND l.cancelled_flag='Y'      --now cancelled.
           AND h.order_number=
           AND NOT EXISTS (
             SELECT 1 FROM xxcofipos_posting_ol_history p
             WHERE p.order_line_id=l.line_id
             AND p.order_line_table_source = 'OL_CANCEL');

13) Findout the region short name with the location code:-
     ------------------------------------------------------------------------------

SELECT  region_1
             FROM hr_organization_units_v hv
             WHERE  hv.location_code = ''; --Store Number

14) For initial determination of whether it is a "NEW" or a "NON-NEW" transaction type
     -----------------------------------------------------------------------------------------------------------------------

          SELECT COUNT(*),
              SUM(DECODE(NVL(attribute7,'xx'),'xx',0,1)) --if null then do not count : old : --SUM(DECODE(attribute7,'Y',1,0))
       --   INTO l_total_lines_count, l_processed_lines_count
          FROM OE_ORDER_LINES_ALL
          WHERE header_id=;

15) find out line_category_codes for unprocessed lines in ORDER LINES table:-
     ------------------------------------------------------------------------------------------------------------

         SELECT NVL(SUM(DECODE(line_category_code,'RETURN',1,0)),0),
                       NVL(SUM(DECODE(line_category_code,'ORDER',1,0)),0)
      --   INTO l_return_count, l_sale_count
         FROM OE_ORDER_LINES_ALL oola
         WHERE header_id=
         AND oola.BOOKED_FLAG = 'Y'
         AND oola.OPEN_FLAG = 'Y'
         AND oola.ATTRIBUTE7 IS NULL
         AND oola.CANCELLED_FLAG = 'N';

16) To know all sales rep name and other information:-
      ----------------------------------------------------------------------

SELECT  jr.name,SALESREP_NUMBER,/* pf.attribute1 */, pf.EMAIL_ADDRESS , pf.PERSON_ID, pf.sex--nvl(pf.attribute1,'') --salesrep_number
         FROM   jtf_rs_salesreps  jr,
                per_all_people_f pf
         WHERE -- salesrep_id = c_salesrep_id  AND
             pf.person_id = jr.person_id
         AND    Nvl(status, 'A') = 'A'
         AND    Sysdate BETWEEN start_date_active AND Nvl(end_date_active, Sysdate + 1);