EBS : Sql Query: OE : How to get open Supply Demand Query for Internal Sales Order

How to get open Supply Demand Query for Internal Sales Order

/********* Internal Sales Order & Sale Order Query***/

SELECT  TO_CHAR (ooha.order_number) order_num,ml.meaning supply_demand_type,d.requirement_date required_date,      oola.line_id, oola.request_date, wda.delivery_id,

       NVL (  -1 * (  d.primary_uom_quantity - d.total_reservation_quantity    - d.completed_quantity ), 0 ) qty,

  FROM mtl_parameters p,

       mtl_system_items_b i,

       bom_calendar_dates c,

       mrp_demand_om_reservations_v d,

       oe_order_headers_all ooha,

       oe_order_lines_all oola,

       wsh_delivery_assignments wda,

       wsh_delivery_details wdd,

       mfg_lookups ml,

       (SELECT DECODE (demand_source_type,

                       2, DECODE (reservation_type, 1, 2, 3, 23, 9),

                       8, DECODE (reservation_type, 1, 21, 22),

                       demand_source_type

                      ) supply_demand_source_type,

               demand_id

          FROM mrp_demand_om_reservations_v) dx

 WHERE d.open_flag = 'Y'

   AND ml.lookup_type = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'

   AND ml.lookup_code = dx.supply_demand_source_type

   AND d.demand_id = dx.demand_id

   AND ooha.header_id = oola.header_id

   AND oola.line_id = d.demand_id

   AND wdd.source_line_id(+) = oola.line_id

   AND wdd.source_header_id(+) = oola.header_id

   AND wdd.delivery_detail_id = wda.delivery_detail_id(+)

   AND d.reservation_type != 2

   AND d.organization_id = :p_org_id

   AND d.primary_uom_quantity >

                        (d.total_reservation_quantity + d.completed_quantity

                        )

   AND d.inventory_item_id = :p_inventory_item_id

   AND (   d.visible_demand_flag = 'Y'

        OR (    NVL (d.visible_demand_flag, 'N') = 'N'

            AND d.ato_line_id IS NOT NULL

            AND NOT EXISTS (

                   SELECT 1

                     FROM oe_order_lines_all ool, mtl_demand md

                    WHERE TO_CHAR (ool.line_id) = md.demand_source_line

                      AND ool.ato_line_id = d.ato_line_id

                      AND ool.item_type_code = 'CONFIG'

                      AND md.reservation_type IN (2, 3))

           )

       )

   AND d.reservation_type != -1

   AND d.reservation_type != -1

   AND d.demand_source_type != -1

   AND d.demand_source_type != -1

   AND TRUNC (d.requirement_date) <= :p_to_date

   AND (   d.subinventory IS NULL

        OR d.subinventory IN (

              SELECT s.secondary_inventory_name

                FROM mtl_secondary_inventories s

               WHERE s.organization_id = d.organization_id

                 AND s.inventory_atp_code = 1

                 AND s.attribute1 = 'FG')

       )

   AND i.organization_id = d.organization_id

   AND i.inventory_item_id = d.inventory_item_id

   AND p.organization_id = d.organization_id

   AND p.calendar_code = c.calendar_code

   AND p.calendar_exception_set_id = c.exception_set_id

   AND c.calendar_date = TRUNC (d.requirement_date)

   AND d.inventory_item_id =

          DECODE (d.reservation_type,

                  1, DECODE (d.parent_demand_id,

                             NULL, d.inventory_item_id,

                             -1

                            ),

                  2, d.inventory_item_id,

                  3, d.inventory_item_id,

                  -1

                 )