OM Useful Query

1)develop a query which lists the orders which r on hold

i)order date,order no,order type(name),order item,date of hold,reason of hold,hold by,days of hold.

ii)for same query the no of days of hold should be a parameter

/* Formatted on 9/4/2014 2:16:11 PM (QP5 v5.115.810.9015) */
SELECT h.order_number,
       ot.name,
       l.ordered_item,
       ohsa.creation_date,
       ohsa.hold_comment,
       fu.user_name,
       TRUNC (SYSDATE) - TRUNC (ohsa.creation_date)
FROM oe_order_headers_all h,
     oe_order_lines_all l,
     oe_transaction_types_tl ot,
     oe_order_holds_all ooha,
     oe_hold_sources_all ohsa,
     fnd_user fu
WHERE     h.header_id = l.header_id
      AND h.order_type_id = ot.transaction_type_id
      AND h.header_id = ooha.header_id
      AND ooha.hold_source_id = ohsa.hold_source_id
      AND ohsa.created_by = fu.user_id
      AND order_number = 57265;

2)develop a query to list all the unbooked sales orders

order no,order item,order quantity,order date,order status

/* Formatted on 9/4/2014 2:16:20 PM (QP5 v5.115.810.9015) */
SELECT oha.order_number,
       ola.ordered_item,
       ola.ordered_quantity,
       oha.ordered_date,
       oha.flow_status_code "Status"
FROM oe_order_headers_all oha, oe_order_lines_all ola
WHERE     1 = 1
      AND oha.header_id = ola.header_id
      AND oha.org_id = 204
      AND ola.flow_status_code = 'ENTERED';

3)list all the orders which r backordered

order no,customer no,cust name,order type(name),status,date of order,item no,description

/* Formatted on 9/4/2014 2:16:37 PM (QP5 v5.115.810.9015) */
SELECT oha.order_number,
       hp.party_name,
       hca.account_number,
       ott.name "Order Type",
       oha.flow_status_code "Order Status",
       ola.flow_status_code "Line Status",
       oha.ordered_date,
       ola.ordered_item,
       wdd.item_description
FROM oe_order_headers_all oha,
     oe_order_lines_all ola,
     wsh_delivery_details wdd,
     fnd_lookup_values flv,
     oe_transaction_types_tl ott,
     hz_parties hp,
     hz_cust_accounts hca
WHERE     1 = 1
      AND oha.header_id = ola.header_id
      AND ott.transaction_type_id = oha.order_type_id
      AND wdd.source_header_id = ola.header_id
      AND oha.sold_to_org_id = hca.cust_account_id(+)
      AND hp.party_id = hca.party_id
      AND flv.lookup_code = wdd.released_status
      AND wdd.released_status = 'B'
      AND oha.org_id = 204
      AND oha.order_number = 57271
      AND flv.lookup_type = 'PICK_STATUS';

4)develop a query to list all the orders which r ready for shipping


/* Formatted on 9/4/2014 2:16:46 PM (QP5 v5.115.810.9015) */
SELECT oha.order_number,
       hp.party_name,
       oha.ordered_date,
       oha.batch_id,
       ola.flow_status_code
FROM oe_order_headers_all oha,
     oe_order_lines_all ola,
     wsh_delivery_details wdd,
     hz_parties hp,
     hz_cust_accounts hca
WHERE     1 = 1
      AND oha.header_id = ola.header_id
      AND wdd.source_header_id = ola.header_id
      AND wdd.source_line_id = ola.line_id
      AND hca.cust_account_id = oha.sold_to_org_id
      AND hp.party_id = hca.party_id
      AND oha.org_id = 204
      AND ola.flow_status_code = 'AWAITING_SHIPPING';