DELIVERY DETAIL FRIGHT ON BOARD

select  oola.ordered_item , wdd.REQUESTED_QUANTITY ,
oola.UNIT_SELLING_PRICE ,
sum(wdd.REQUESTED_QUANTITY*oola.UNIT_SELLING_PRICE) extended_price
     from       wsh_delivery_assignments wda ,
                wsh_delivery_details wdd,
                oe_order_lines_all oola
                where wda.delivery_detail_id = wdd.delivery_detail_id
                and wda.delivery_id = '1236'
                and wdd.source_header_id =  oola.header_id
                and wdd.source_line_id = oola.line_id
              --  and oola.ordered_item = '15400RAFT01'
              --  and wdd.inventory_item_id = oola.inventory_item_id
                group by oola.ordered_item , wdd.requested_quantity , oola.unit_selling_price

PRICE LIST UPLOAD THE DATA

/* Formatted on 2012/05/05 17:16 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR c1
   IS
      (SELECT xpl.new_price, f.segment1 item_code, f.inventory_item_id,
              f.primary_uom_code, a.NAME, a.list_header_id, b.list_line_id
         FROM qp_list_headers_tl a,
              qp_list_lines b,
              qp_pricing_attributes c,
              mtl_system_items_b f,
              xxhmi_price_xll xpl
        WHERE a.list_header_id = b.list_header_id
          AND b.list_line_id = c.list_line_id
          AND c.product_attr_value = TO_CHAR (f.inventory_item_id)
          AND f.segment1 = xpl.item_code
          AND xpl.price_header = a.NAME
          AND f.organization_id = 86
--          AND TRUNC (b.start_date_active) = TRUNC (SYSDATE)
--          AND xpl.price_header = '2W AS DLP Price List'
          AND b.start_date_active =
                 (SELECT MAX (k.start_date_active)
                    FROM qp_list_lines k, qp_pricing_attributes g
                   WHERE k.list_line_id = g.list_line_id
                     AND g.product_attr_value = c.product_attr_value
                     AND k.list_header_id = a.list_header_id));

--                  AND ROWNUM <= 1000;
   gpr_return_status             VARCHAR2 (1)                          := NULL;
   gpr_msg_count                 NUMBER                                   := 0;
   gpr_msg_data                  VARCHAR2 (2000);
   gpr_price_list_rec            qp_price_list_pub.price_list_rec_type;
   gpr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
   gpr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
   gpr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
   gpr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
   gpr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   gpr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
   gpr_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_type;
   ppr_price_list_rec            qp_price_list_pub.price_list_rec_type;
   ppr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
   ppr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
   ppr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
   ppr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
   ppr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   ppr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
   ppr_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_type;
   k                             NUMBER                                   := 1;
   j                             NUMBER                                   := 1;
   x_debug_file                  VARCHAR2 (100);
BEGIN
   --   oe_msg_pub.initialize;
   --   oe_debug_pub.initialize;
   --   X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode ('FILE');
   --   oe_debug_pub.SetDebugLevel (5);
   FOR i IN c1
   LOOP
      gpr_price_list_rec.list_header_id := i.list_header_id;
      --- 10015 RTLP --11015 MRP  ---10049 DLP
      --gpr_price_list_rec.name := 'MRP Price List';
      gpr_price_list_rec.list_type_code := 'PRL';
      gpr_price_list_rec.description := NULL;
      gpr_price_list_rec.operation := qp_globals.g_opr_update;
      k := 1;
      gpr_price_list_line_tbl (k).list_line_id := i.list_line_id;
      gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_update;
      gpr_price_list_line_tbl (k).end_date_active := SYSDATE - 5;
      k := 2;
      -- create the price list line rec
      gpr_price_list_line_tbl (k).list_header_id := i.list_header_id;
      ---11015; DLP ---10049 DLP 10049;
      gpr_price_list_line_tbl (k).list_line_id := fnd_api.g_miss_num;
      gpr_price_list_line_tbl (k).list_line_type_code := 'PLL';
      gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_create;
      gpr_price_list_line_tbl (k).operand := i.new_price;
      --77.00;      -- value of unit selling
      gpr_price_list_line_tbl (k).arithmetic_operator := 'UNIT_PRICE';
      gpr_price_list_line_tbl (k).start_date_active := SYSDATE - 4;
      gpr_price_list_line_tbl (k).end_date_active := '31-MAR-2099';
      gpr_price_list_line_tbl (k).modifier_level_code := 'LINE';
      gpr_price_list_line_tbl (k).automatic_flag := 'Y';
      gpr_price_list_line_tbl (k).reprice_flag := 'Y';
      j := 1;
      gpr_pricing_attr_tbl (j).pricing_attribute_id := fnd_api.g_miss_num;
      gpr_pricing_attr_tbl (j).list_line_id := fnd_api.g_miss_num;
      gpr_pricing_attr_tbl (j).product_attribute_context := 'ITEM';
      gpr_pricing_attr_tbl (j).product_attribute := 'PRICING_ATTRIBUTE1';
      gpr_pricing_attr_tbl (j).product_attr_value := i.inventory_item_id;
      gpr_pricing_attr_tbl (j).product_uom_code := i.primary_uom_code;
      --- item UOM
      gpr_pricing_attr_tbl (j).excluder_flag := 'N';
      --gpr_pricing_attr_tbl (J).ATTRIBUTE_GROUPING_NO := 1;
      gpr_pricing_attr_tbl (j).price_list_line_index := 2;
      gpr_pricing_attr_tbl (j).operation := qp_globals.g_opr_create;
      DBMS_OUTPUT.put_line ('before process price list ');
      qp_price_list_pub.process_price_list
                   (p_api_version_number           => 1.0,
                    p_init_msg_list                => fnd_api.g_false,
                    p_return_values                => fnd_api.g_false,
                    p_commit                       => fnd_api.g_false,
                    x_return_status                => gpr_return_status,
                    x_msg_count                    => gpr_msg_count,
                    x_msg_data                     => gpr_msg_data,
                    p_price_list_rec               => gpr_price_list_rec,
                    p_price_list_line_tbl          => gpr_price_list_line_tbl,
                    p_pricing_attr_tbl             => gpr_pricing_attr_tbl,
                    x_price_list_rec               => ppr_price_list_rec,
                    x_price_list_val_rec           => ppr_price_list_val_rec,
                    x_price_list_line_tbl          => ppr_price_list_line_tbl,
                    x_price_list_line_val_tbl      => ppr_price_list_line_val_tbl,
                    x_qualifiers_tbl               => ppr_qualifiers_tbl,
                    x_qualifiers_val_tbl           => ppr_qualifiers_val_tbl,
                    x_pricing_attr_tbl             => ppr_pricing_attr_tbl,
                    x_pricing_attr_val_tbl         => ppr_pricing_attr_val_tbl
                   );
      COMMIT;

--      DBMS_OUTPUT.put_line ('after process price list ');
--      DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
--      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);

      --   oe_debug_pub.add ('yusaito after calling process_price_list API');
      --   DBMS_OUTPUT.put_line (
      --      'OM Debug file: ' || oe_debug_pub.G_DIR || '/' || oe_debug_pub.G_FILE
      --   );
      IF gpr_return_status <> fnd_api.g_ret_sts_success
      THEN
         RAISE fnd_api.g_exc_unexpected_error;
      END IF;
   END LOOP;
--   DBMS_OUTPUT.put_line ('after process price list ');
--   DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
--   DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_count);
--   DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);
EXCEPTION
   WHEN fnd_api.g_exc_error
   THEN
--      DBMS_OUTPUT.put_line ('after process price list ');
--      DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
--      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_count);
--      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);
--      gpr_return_status := fnd_api.g_ret_sts_error;
      ROLLBACK;
   -- Get message count and data

   --dbms_output.put_line('err msg 1 is : ' || gpr_msg_data);
   WHEN fnd_api.g_exc_unexpected_error
   THEN
      gpr_return_status := fnd_api.g_ret_sts_unexp_error;
      ROLLBACK;

      --dbms_output.put_line(' msg count 2 is : ' || gpr_msg_count);
      FOR k IN 1 .. gpr_msg_count
      LOOP
         gpr_msg_data := oe_msg_pub.get (p_msg_index => k, p_encoded => 'F');
         /*

         oe_msg_pub.Count_And_Get
         ( p_count => gpr_msg_count
         , p_data => gpr_msg_data
         );

         */

         -- Get message count and data
         DBMS_OUTPUT.put_line ('err msg ' || k || 'is: ' || gpr_msg_data);
         NULL;
      END LOOP;
   WHEN OTHERS
   THEN
      gpr_return_status := fnd_api.g_ret_sts_unexp_error;
      ROLLBACK;
-- Get message count and data
--dbms_output.put_line('err msg 3 is : ' || gpr_msg_data);
END;



---------------------------------------------------------------------------------------------------------

2nd SCRIPT

/* Formatted on 2012/05/04 16:20 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR c1
   IS
      SELECT DISTINCT xx.new_price, mtl.segment1 item_code,
                      mtl.inventory_item_id, mtl.primary_uom_code,
                      (SELECT x1.list_header_id
                         FROM qp_list_headers_v x1
                        WHERE x1.NAME = xx.price_header) list_header_id
                 FROM mtl_system_items_b mtl, xxhmi_price_xll xx
                WHERE mtl.segment1 = xx.item_code
                  AND mtl.organization_id = 86
                  AND mtl.inventory_item_id NOT IN (
                         SELECT DISTINCT msi.inventory_item_id
                                    FROM mtl_system_items_b msi,
                                         xxhmi_price_xll xx1,
                                         qp_list_lines b,
                                         qp_pricing_attributes c
                                   WHERE msi.segment1 = xx1.item_code
                                     AND msi.organization_id = 86
                                     AND b.list_line_id = c.list_line_id
                                     AND xx.price_header = xx1.price_header
                                     AND b.list_header_id IN (
                                              SELECT x1.list_header_id
                                                FROM qp_list_headers_tl x1
                                               WHERE x1.NAME =
                                                              xx1.price_header)
                                     ---=10015  --- 10015 RTLP --11015 MRP  ---10049 DLP
                                     AND c.product_attribute_context = 'ITEM'
                                     AND c.product_attribute =
                                                          'PRICING_ATTRIBUTE1'
                                     AND c.product_attr_value =
                                                         msi.inventory_item_id);

   --AND mtl.segment1 IN ('72710TG1T01', '72711TG2K01');
   gpr_return_status             VARCHAR2 (1)                          := NULL;
   gpr_msg_count                 NUMBER                                   := 0;
   gpr_msg_data                  VARCHAR2 (2000);
   gpr_price_list_rec            qp_price_list_pub.price_list_rec_type;
   gpr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
   gpr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
   gpr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
   gpr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
   gpr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   gpr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
   gpr_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_type;
   ppr_price_list_rec            qp_price_list_pub.price_list_rec_type;
   ppr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
   ppr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
   ppr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
   ppr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
   ppr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   ppr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
   ppr_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_type;
   k                             NUMBER                                   := 1;
   j                             NUMBER                                   := 1;
   x_debug_file                  VARCHAR2 (100);
BEGIN
   --   oe_msg_pub.initialize;
   --   oe_debug_pub.initialize;
   --   X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode ('FILE');
   --   oe_debug_pub.SetDebugLevel (5);
   FOR i IN c1
   LOOP
      gpr_price_list_rec.list_header_id := i.list_header_id;
                                     --- 10015 RTLP --11015 MRP  ---10049 DLP
      --gpr_price_list_rec.name := 'MRP Price List';
      gpr_price_list_rec.list_type_code := 'PRL';
      gpr_price_list_rec.description := NULL;
      gpr_price_list_rec.operation := qp_globals.g_opr_update;
      k := 1;
      -- create the price list line rec
      gpr_price_list_line_tbl (k).list_header_id := i.list_header_id;
      ---11015; DLP ---10049 DLP 10049;
      gpr_price_list_line_tbl (k).list_line_id := fnd_api.g_miss_num;
      gpr_price_list_line_tbl (k).list_line_type_code := 'PLL';
      gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_create;
      gpr_price_list_line_tbl (k).operand := i.new_price;
      --77.00;      -- value of unit selling
      gpr_price_list_line_tbl (k).arithmetic_operator := 'UNIT_PRICE';
      gpr_price_list_line_tbl (k).start_date_active := SYSDATE - 3;
      gpr_price_list_line_tbl (k).end_date_active := '31-MAR-2099';
      gpr_price_list_line_tbl (k).modifier_level_code := 'LINE';
      gpr_price_list_line_tbl (k).automatic_flag := 'Y';
      gpr_price_list_line_tbl (k).reprice_flag := 'Y';
      j := 1;
      gpr_pricing_attr_tbl (j).pricing_attribute_id := fnd_api.g_miss_num;
      gpr_pricing_attr_tbl (j).list_line_id := fnd_api.g_miss_num;
      gpr_pricing_attr_tbl (j).product_attribute_context := 'ITEM';
      gpr_pricing_attr_tbl (j).product_attribute := 'PRICING_ATTRIBUTE1';
      gpr_pricing_attr_tbl (j).product_attr_value := i.inventory_item_id;
                                                                   --'92916';
      -- inventory item id
      gpr_pricing_attr_tbl (j).product_uom_code := i.primary_uom_code;
      --- item UOM
      gpr_pricing_attr_tbl (j).excluder_flag := 'N';
      --gpr_pricing_attr_tbl (J).ATTRIBUTE_GROUPING_NO := 1;
      gpr_pricing_attr_tbl (j).price_list_line_index := 1;
      gpr_pricing_attr_tbl (j).operation := qp_globals.g_opr_create;
      DBMS_OUTPUT.put_line ('before process price list ');
      qp_price_list_pub.process_price_list
                   (p_api_version_number           => 1.0,
                    p_init_msg_list                => fnd_api.g_false,
                    p_return_values                => fnd_api.g_false,
                    p_commit                       => fnd_api.g_false,
                    x_return_status                => gpr_return_status,
                    x_msg_count                    => gpr_msg_count,
                    x_msg_data                     => gpr_msg_data,
                    p_price_list_rec               => gpr_price_list_rec,
                    p_price_list_line_tbl          => gpr_price_list_line_tbl,
                    p_pricing_attr_tbl             => gpr_pricing_attr_tbl,
                    x_price_list_rec               => ppr_price_list_rec,
                    x_price_list_val_rec           => ppr_price_list_val_rec,
                    x_price_list_line_tbl          => ppr_price_list_line_tbl,
                    x_price_list_line_val_tbl      => ppr_price_list_line_val_tbl,
                    x_qualifiers_tbl               => ppr_qualifiers_tbl,
                    x_qualifiers_val_tbl           => ppr_qualifiers_val_tbl,
                    x_pricing_attr_tbl             => ppr_pricing_attr_tbl,
                    x_pricing_attr_val_tbl         => ppr_pricing_attr_val_tbl
                   );
      COMMIT;
      DBMS_OUTPUT.put_line ('after process price list ');
      DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);

      --   oe_debug_pub.add ('yusaito after calling process_price_list API');
      --   DBMS_OUTPUT.put_line (
      --      'OM Debug file: ' || oe_debug_pub.G_DIR || '/' || oe_debug_pub.G_FILE
      --   );
      IF gpr_return_status <> fnd_api.g_ret_sts_success
      THEN
         RAISE fnd_api.g_exc_unexpected_error;
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line ('after process price list ');
   DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
   DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_count);
   DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);
EXCEPTION
   WHEN fnd_api.g_exc_error
   THEN
      DBMS_OUTPUT.put_line ('after process price list ');
      DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_count);
      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);
      gpr_return_status := fnd_api.g_ret_sts_error;
      ROLLBACK;
   -- Get message count and data

   --dbms_output.put_line('err msg 1 is : ' || gpr_msg_data);
   WHEN fnd_api.g_exc_unexpected_error
   THEN
      gpr_return_status := fnd_api.g_ret_sts_unexp_error;
      ROLLBACK;

      --dbms_output.put_line(' msg count 2 is : ' || gpr_msg_count);
      FOR k IN 1 .. gpr_msg_count
      LOOP
         --         gpr_msg_data := oe_msg_pub.get (p_msg_index => k, p_encoded => 'F');
         /*

         oe_msg_pub.Count_And_Get
         ( p_count => gpr_msg_count
         , p_data => gpr_msg_data
         );

         */

         -- Get message count and data
         DBMS_OUTPUT.put_line ('err msg ' || k || 'is: ' || gpr_msg_data);
         NULL;
      END LOOP;
   WHEN OTHERS
   THEN
      gpr_return_status := fnd_api.g_ret_sts_unexp_error;
      ROLLBACK;
-- Get message count and data
--dbms_output.put_line('err msg 3 is : ' || gpr_msg_data);
END;

TO FIND THE ONHAND QUANTITY (INVENTORY)

SELECT
 msib.segment1 item_code  ,
                moqd.SUBINVENTORY_CODE ,
--                /*moqd.LOCATOR_ID */
                SUM(moqd.TRANSACTION_QUANTITY)  onhand_quantity ,
                milkfv.CONCATENATED_SEGMENTS LOCATORS      
           FROM APPS.MTL_SYSTEM_ITEMS_B msib,
                APPS.MTL_ONHAND_QUANTITIES_DETAIL moqd ,
                APPS.mtl_item_locations_kfv milkfv
          WHERE msib.organization_id = 110
            and msib.INVENTORY_ITEM_ID = moqd.INVENTORY_ITEM_ID
            AND MSIB.ORGANIZATION_ID =MOQD.ORGANIZATION_ID
         and moqd.SUBINVENTORY_CODE = 'ASRS-1'
           AND milkfv.INVENTORY_LOCATION_ID = moqd.LOCATOR_ID
          AND MSIB.SEGMENT1 = 'Z3NTCRS008002100297010'
            GROUP BY MSIB.SEGMENT1,MOQD.SUBINVENTORY_CODE,MILKFV.CONCATENATED_SEGMENTS 

RESERVATION QUERY FOR INVENTORY

SELECT  ORGANIZATION_CODE From_Organization ,substr(SHIPMENT_PRIORITY_CODE,1,3) to_organization,wdd.SOURCE_HEADER_NUMBER Order_number,
         wdd.SOURCE_LINE_NUMBER Line_no,
         SOURCE_LINE_ID,
         msib.segment1 parts,
         rs.RESERVATION_QUANTITY,
         wdd.organization_id,RESERVATION_ID
  FROM   mtl_reservations rs,
         wsh_delivery_details wdd,
         mtl_system_items_b msib,
         org_organization_definitions ord
 WHERE       rs.DEMAND_SOURCE_LINE_ID = wdd.SOURCE_LINE_ID
         AND wdd.RELEASED_STATUS = 'B'
         AND wdd.SOURCE_HEADER_TYPE_NAME LIKE 'Internal%Order%'
         AND STAGED_FLAG IS NULL
         AND wdd.REQUESTED_QUANTITY = RESERVATION_QUANTITY
         AND DETAILED_QUANTITY = 0
         AND msib.inventory_item_id = wdd.inventory_item_id
         AND msib.organization_id = wdd.organization_id
         AND ord.organization_id = wdd.organization_id
        
        
        
        
        
        
        
         SELECT *
  FROM mtl_reservations
 WHERE reservation_id IN (
          SELECT reservation_id
            FROM mtl_reservations rs,
                 wsh_delivery_details wdd,
                 mtl_system_items_b msib,
                 org_organization_definitions ord
           WHERE rs.demand_source_line_id = wdd.source_line_id
             AND wdd.released_status = 'B'
             AND wdd.source_header_type_name LIKE 'Internal%Order%'
             AND staged_flag IS NULL
             AND wdd.requested_quantity = reservation_quantity
             AND detailed_quantity = 0
             AND msib.inventory_item_id = wdd.inventory_item_id
             AND msib.organization_id = wdd.organization_id
             AND ord.organization_id = wdd.organization_id)
            
               
            
            
            
            
            
 delete from mtl_reservations
 WHERE reservation_id IN (
          SELECT reservation_id
            FROM mtl_reservations rs,
                 wsh_delivery_details wdd,
                 mtl_system_items_b msib,
                 org_organization_definitions ord
           WHERE rs.demand_source_line_id = wdd.source_line_id
             AND wdd.released_status = 'B'
             AND wdd.source_header_type_name LIKE 'Internal%Order%'
             AND staged_flag IS NULL
             AND wdd.requested_quantity = reservation_quantity
             AND detailed_quantity = 0
             AND msib.inventory_item_id = wdd.inventory_item_id
             AND msib.organization_id = wdd.organization_id
             AND ord.organization_id = wdd.organization_id)
            
            
            


Please find the Back_Order_details for today And Please find the attachment.


Thanks & Regards

Amresh kumaran M I 

ITEM CLASSIFICATION AND MASTER ITEM CATEGORIES

ITEM CLASSIFICATION AND MASTER ITEM CATEROIES                ------------------------------------------------------  1

SELECT e.segment1 part_number,
       (SELECT organization_code
          FROM mtl_parameters
         WHERE organization_id = b.organization_id) LOCATION, b.regime_code,
       a.attribute_code, a.attribute_value, r.concatenated_segments CATEGORY
  FROM jai_rgm_itm_tmpl_attrs a,
       jai_rgm_itm_regns b,
       mtl_system_items_b e,
       mtl_item_categories f,
       mtl_categories_kfv r
 WHERE b.rgm_item_regns_id = a.rgm_item_regns_id
   AND b.regime_code = 'EXCISE'
--   AND b.inventory_item_id = 77580
   AND b.organization_id = 151
   AND attribute_code = 'EXCISABLE'
   AND e.inventory_item_id = b.inventory_item_id
   AND b.organization_id = e.organization_id
   AND e.inventory_item_id = f.inventory_item_id
   AND f.organization_id = e.organization_id
   AND f.category_set_id = 1100000066
   AND r.category_id = f.category_id
   AND a.attribute_value <> r.concatenated_segments
--   and  a.attribute_value <> 'Y'
--  and  r.concatenated_segments <> 'Y' 


******************************************************************************

ITEM CLASSIFICATION AND MASTER ITEM CATEGORIES


 SELECT MSIB.SEGMENT1 PART_NAME ,                                ----------------------------------------------------  2
        JRIA.ATTRIBUTE_VALUE APPLICABLE,
        ATTRIBUTE_CODE ITEM_CLASSFICATION ,
        MICV.CATEGORY_SET_NAME ,
        MICV.CATEGORY_CONCAT_SEGS CATEGORY
  from  JAI_RGM_ITM_TMPL_ATTRS JRIA ,
        JAI_RGM_ITM_REGNS JRIR ,
        MTL_SYSTEM_ITEMS_B MSIB ,
        MTL_ITEM_CATEGORIES_V MICV
 WHERE  1=1
   and  JRIA.RGM_ITEM_REGNS_ID = JRIR.RGM_ITEM_REGNS_ID
   AND  JRIR.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
   and  MSIB.INVENTORY_ITEM_ID = MICV.INVENTORY_ITEM_ID
   AND  MICV.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
 -- AND  MSIB.SEGMENT1 = '17515MGE000'
   AND  JRIA.ATTRIBUTE_VALUE <> CATEGORY_CONCAT_SEGS
   AND  MICV.CATEGORY_SET_NAME = 'HMI Excise Applicability Set'
   AND  JRIA.ATTRIBUTE_CODE = 'EXCISABLE'
   AND  MSIB.ORGANIZATION_ID = 151
   and  JRIR.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID

VIEW MATERIAL TRANSACTIONS QUERY LPN

/* Formatted on 7/26/2012 4:28:45 PM (QP5 v5.114.809.3010) */
SELECT   msib.segment1 item,
         MMT.SUBINVENTORY_CODE Subinventory,
         -- milkfv.CONCATENATED_SEGMENTS LOCATORS,
         (SELECT   D.CONCATENATED_SEGMENTS
            FROM   mtl_item_locations_kfv D
           WHERE   D.INVENTORY_LOCATION_ID = MMT.LOCATOR_ID)
            LOCATOR,
         (SELECT   C.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers C
           WHERE   C.lpn_id = mmt.lpn_id)
            lpn,
         MMT.TRANSFER_SUBINVENTORY Transfer_Subinventory,
         (SELECT   A.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers A
           WHERE   A.lpn_id = mmt.TRANSFER_LPN_ID)
            Transfer_Lpn,
         (SELECT   B.CONCATENATED_SEGMENTS
            FROM   mtl_item_locations_kfv B
           WHERE   B.INVENTORY_LOCATION_ID = MMT.TRANSFER_LOCATOR_ID)
            TRANSFER_LOCATOR,
         (SELECT   ORGANIZATION_CODE
            FROM   ORG_ORGANIZATION_DEFINITIONS
           WHERE   ORGANIZATION_ID = MSIB.ORGANIZATION_ID)
            Transfer_Org,
         MMT.TRANSACTION_DATE,
         MMT.TRANSACTION_QUANTITY,
         MMT.TRANSACTION_UOM,
         MMT.PRIMARY_QUANTITY,
         --    ood.ORGANIZATION_CODE,
         MMT.SHIPMENT_NUMBER,
         (SELECT   USER_ID
            FROM   FND_USER
           WHERE   USER_ID = MMT.CREATED_BY)
            USER_ID
  --         mtt.TRANSACTION_TYPE_NAME transaction_type,
  --         mkfv.CONCATENATED_SEGMENTS transfer_locator
  FROM   MTL_SYSTEM_ITEMS_B msib, MTL_MATERIAL_TRANSACTIONS MMT
 --         mtl_item_locations_kfv milkfv,
 --         mtl_transaction_types mtt,
 --         mtl_item_locations_kfv mkfv,
 --         org_organization_definitions ood
 WHERE       msib.organization_id = 83
         AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
         AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
         -- and moqd.SUBINVENTORY_CODE = 'Z'
         --         AND milkfv.INVENTORY_ITEM_ID = mmt.inventory_item_id
         --         AND milkfv.organization_id = mmt.organization_id
         --         AND milkfv.INVENTORY_LOCATION_ID = mmt.LOCATOR_ID
         --         AND mtt.TRANSACTION_TYPE_ID = mmt.TRANSACTION_TYPE_ID
         --         AND mtt.TRANSACTION_SOURCE_TYPE_ID = mmt.TRANSACTION_SOURCE_TYPE_ID
                        AND TRUNC (MMT.creation_date) BETWEEN '01-APR-2012'
                                                      AND  '30-APR-2012'
          --       AND ood.organization_id = msib.organization_id
         --         AND mkfv.INVENTORY_LOCATION_ID = mmt.TRANSFER_LOCATOR_ID
        AND MSIB.SEGMENT1 = '23461KSP910'
--GROUP BY   MSIB.SEGMENT1,
--           MMT.SUBINVENTORY_CODE,
--           MILKFV.CONCATENATED_SEGMENTS,
--           MMT.TRANSFER_SUBINVENTORY,
--           MMT.TRANSACTION_DATE,
--           MMT.TRANSACTION_QUANTITY,
--           MMT.TRANSACTION_UOM,
--           MMT.PRIMARY_QUANTITY,
--           MMT.SHIPMENT_NUMBER,
--           mtt.TRANSACTION_TYPE_NAME
--         AND MMTMM.TRANSACTION_ID = MMT.TRANSACTION_ID
--         AND MMTMM.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
--         AND MMTMM.ORGANIZATION_ID = MMT.ORGANIZATION_ID
---------------------------------------------------------------------------------------------------------------------

/* Formatted on 7/26/2012 4:45:25 PM (QP5 v5.114.809.3010) */
SELECT   msib.segment1 item,
         MMT.SUBINVENTORY_CODE Subinventory,
         -- milkfv.CONCATENATED_SEGMENTS LOCATORS,
         (SELECT   D.CONCATENATED_SEGMENTS
            FROM   mtl_item_locations_kfv D
           WHERE   D.INVENTORY_LOCATION_ID = MMT.LOCATOR_ID)
            LOCATOR,
         (SELECT   C.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers C
           WHERE   C.lpn_id = mmt.lpn_id)
            lpn,
         MMT.TRANSFER_SUBINVENTORY Transfer_Subinventory,
         (SELECT   A.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers A
           WHERE   A.lpn_id = mmt.TRANSFER_LPN_ID)
            Transfer_Lpn,
         (SELECT   B.CONCATENATED_SEGMENTS
            FROM   mtl_item_locations_kfv B
           WHERE   B.INVENTORY_LOCATION_ID = MMT.TRANSFER_LOCATOR_ID)
            TRANSFER_LOCATOR,
         (SELECT   ORGANIZATION_CODE
            FROM   ORG_ORGANIZATION_DEFINITIONS
           WHERE   ORGANIZATION_ID = MSIB.ORGANIZATION_ID)
            Transfer_Org,
         MMT.TRANSACTION_DATE,
         MMT.TRANSACTION_QUANTITY,
         MMT.TRANSACTION_UOM,
         MMT.PRIMARY_QUANTITY,
         --    ood.ORGANIZATION_CODE,
         MMT.SHIPMENT_NUMBER,
         (SELECT   USER_ID
            FROM   FND_USER
           WHERE   USER_ID = MMT.CREATED_BY)
            USER_ID
  --         mtt.TRANSACTION_TYPE_NAME transaction_type,
  --         mkfv.CONCATENATED_SEGMENTS transfer_locator
  FROM   MTL_SYSTEM_ITEMS_B msib, MTL_MATERIAL_TRANSACTIONS MMT
 --         mtl_item_locations_kfv milkfv,
 --         mtl_transaction_types mtt,
 --         mtl_item_locations_kfv mkfv,
 --         org_organization_definitions ood
 WHERE       msib.organization_id = 83
         AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
         AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
--         AND TRUNC (MMT.creation_date) BETWEEN '01-APR-2012'
--                                           AND  '30-APR-2012'
         AND MSIB.SEGMENT1 = '23461KSP910'

----------------------------------------------------------------------------------------------------------------------
/* Formatted on 7/26/2012 10:09:01 AM (QP5 v5.114.809.3010) */
SELECT   msib.segment1 item_code,
         MMT.SUBINVENTORY_CODE,
         milkfv.CONCATENATED_SEGMENTS LOCATORS,
         MMT.TRANSFER_SUBINVENTORY,
         MMT.TRANSACTION_DATE,
         MMT.TRANSACTION_QUANTITY,
         MMT.TRANSACTION_UOM,
         MMT.PRIMARY_QUANTITY,
         ood.ORGANIZATION_CODE,
         MMT.SHIPMENT_NUMBER,
         --         WLPN.LICENSE_PLATE_NUMBER LPN,
         mtt.TRANSACTION_TYPE_NAME transaction_type,
         --   mtts.TRANSACTION_TYPE_NAME source_type,
         mkfv.CONCATENATED_SEGMENTS transfer_locator,
         (SELECT   LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers
           WHERE   lpn_id = mmt.lpn_id)
            lpn
  FROM   MTL_SYSTEM_ITEMS_B msib,
         MTL_MATERIAL_TRANSACTIONS MMT,
         mtl_item_locations_kfv milkfv,
         mtl_transaction_types mtt,
         mtl_item_locations_kfv mkfv,
         org_organization_definitions ood
 --mtl_transaction_types mtts
 --      WMS_LICENSE_PLATE_NUMBERS WLPN
 --      MTL_MATERIAL_TRANSACTIONS MMTMM
 WHERE       msib.organization_id = 83
         --     AND msib.INVENTORY_ITEM_ID = moqd.INVENTORY_ITEM_ID
         --     AND MSIB.ORGANIZATION_ID = MOQD.ORGANIZATION_ID
         AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
         AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
         -- and moqd.SUBINVENTORY_CODE = 'Z'
         AND milkfv.INVENTORY_ITEM_ID = mmt.inventory_item_id
         AND milkfv.organization_id = mmt.organization_id
         AND milkfv.INVENTORY_LOCATION_ID = mmt.LOCATOR_ID
         AND mtt.TRANSACTION_TYPE_ID = mmt.TRANSACTION_TYPE_ID
         AND mtt.TRANSACTION_SOURCE_TYPE_ID = mmt.TRANSACTION_SOURCE_TYPE_ID
         --         AND mtts.TRANSACTION_TYPE_ID = mmt.TRANSACTION_TYPE_ID
         --         AND mtts.TRANSACTION_SOURCE_TYPE_ID = mmt.TRANSACTION_SOURCE_TYPE_ID
         --         --         AND MSIB.ORGANIZATION_ID = WLPN.ORGANIZATION_ID
         --         AND MSIB.INVENTORY_ITEM_ID = WLPN.INVENTORY_ITEM_ID
         --         AND mmt.LOCATOR_ID(+) = WLPN.LOCATOR_ID
         AND TRUNC (MMT.creation_date) BETWEEN '01-APR-2012'
                                           AND  '30-APR-2012'
         AND ood.organization_id = msib.organization_id
         AND mkfv.INVENTORY_LOCATION_ID = mmt.TRANSFER_LOCATOR_ID
-- AND MSIB.SEGMENT1 = '23461KSP910'
--GROUP BY   MSIB.SEGMENT1,
--           MMT.SUBINVENTORY_CODE,
--           MILKFV.CONCATENATED_SEGMENTS,
--           MMT.TRANSFER_SUBINVENTORY,
--           MMT.TRANSACTION_DATE,
--           MMT.TRANSACTION_QUANTITY,
--           MMT.TRANSACTION_UOM,
--           MMT.PRIMARY_QUANTITY,
--           MMT.SHIPMENT_NUMBER,
--           mtt.TRANSACTION_TYPE_NAME
--         AND MMTMM.TRANSACTION_ID = MMT.TRANSACTION_ID
--         AND MMTMM.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
--         AND MMTMM.ORGANIZATION_ID = MMT.ORGANIZATION_ID

VIEW MATERIAL QUERY

/* Formatted on 7/27/2012 2:40:04 PM (QP5 v5.114.809.3010) */
SELECT   msib.segment1 item,
         MMT.SUBINVENTORY_CODE Subinventory,
         -- milkfv.CONCATENATED_SEGMENTS LOCATORS,
         (SELECT   D.CONCATENATED_SEGMENTS
            FROM   mtl_item_locations_kfv D
           WHERE   D.INVENTORY_LOCATION_ID = MMT.LOCATOR_ID)
            LOCATOR,
         (SELECT   C.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers C
           WHERE   C.lpn_id = mmt.lpn_id)
            lpn,
         (SELECT   location_code
            FROM   hr_locations_v
           WHERE   location_id = MMT.LOCATOR_ID)
            location,
         MMT.TRANSFER_SUBINVENTORY Transfer_Subinventory,
         (SELECT   B.CONCATENATED_SEGMENTS
            FROM   mtl_item_locations_kfv B
           WHERE   B.INVENTORY_LOCATION_ID = MMT.TRANSFER_LOCATOR_ID)
            TRANSFER_LOCATOR,
         (SELECT   A.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers A
           WHERE   A.lpn_id = mmt.TRANSFER_LPN_ID)
            Transfer_Lpn,
         (SELECT   S.LICENSE_PLATE_NUMBER
            FROM   wms_license_plate_numbers S
           WHERE   S.lpn_id = mmt.CONTENT_LPN_ID)
            CONTENT_Lpn,
         (SELECT   ORGANIZATION_CODE
            FROM   ORG_ORGANIZATION_DEFINITIONS
           WHERE   ORGANIZATION_ID = MSIB.ORGANIZATION_ID)
            Transfer_Org,
         MMT.TRANSACTION_DATE,
         MMT.TRANSACTION_QUANTITY,
         MMT.TRANSACTION_UOM,
         MMT.PRIMARY_QUANTITY,
         (SELECT   TRANSACTION_SOURCE_TYPE_NAME
            FROM   MTL_TXN_SOURCE_TYPES
           WHERE   TRANSACTION_SOURCE_TYPE_ID =
                      MMT.TRANSACTION_SOURCE_TYPE_ID)
            SOURCE_TYPE,
         --    ood.ORGANIZATION_CODE,
         MMT.SHIPMENT_NUMBER,
         (SELECT   USER_NAME
            FROM   FND_USER
           WHERE   USER_ID = MMT.CREATED_BY)
            USER_NAME
  --         mtt.TRANSACTION_TYPE_NAME transaction_type,
  --         mkfv.CONCATENATED_SEGMENTS transfer_locator
  FROM   MTL_SYSTEM_ITEMS_B msib, MTL_MATERIAL_TRANSACTIONS MMT
 --         mtl_item_locations_kfv milkfv,
 --         mtl_transaction_types mtt,
 --         mtl_item_locations_kfv mkfv,
 --         org_organization_definitions ood
 WHERE       msib.organization_id = 83
         AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
         AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
         --         AND TRUNC (MMT.creation_date) BETWEEN '01-APR-2012'
         --                                           AND  '30-APR-2012'
         AND MSIB.SEGMENT1 = '23461KSP910'

TO KNOW USER AND PASSWORD QUERY

SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = 'ORACLE.TECH';

QUERY FOR PAYMENT VOUCHED DETAILS

/* Formatted on 26-Apr-12 5:51:27 PM (QP5 v5.115.810.9015) */
SELECT   ACA.PAYMENT_TYPE_FLAG PAYMENT_TYPE,
         ACA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,
         ACA.CHECKRUN_NAME BATCH_NAME,
         ACA.BANK_ACCOUNT_NUM,
         FU.USER_NAME,
         ACA.CHECK_DATE GL_DATE,
         ACA.CHECK_ID,
         ACA.CHECK_ID CHECK_ID1,
         ACA.CHECK_NUMBER,
         ACA.VENDOR_ID,
         aca.vendor_site_id,
         NVL (
            (SELECT   aia.attribute2
               FROM   ap_invoice_payments_all aip, ap_invoices_all aia
              WHERE       aip.invoice_id = aia.invoice_id
                      AND aip.check_id = aca.check_id
                      AND ROWNUM = 1),
            HZP.PARTY_NAME
         )
            VENDOR_NAME,
         ACA.PARTY_ID,
         ACA.DOC_SEQUENCE_VALUE VOUCHER_NO,
         ACA.AMOUNT * NVL (ACA.EXCHANGE_RATE, 1) CREDIT_AMOUNT,
         GCC.SEGMENT2 COST_CENTER,
         GCC.SEGMENT3 || '.' || GCC.SEGMENT4 ACCOUNT_CODE,
         REPLACE (
            DECODE (GCC.SEGMENT4,
                    '00', A.DESCRIPTION,
                    A.DESCRIPTION || '.' || B.DESCRIPTION),
            'CLEARING A/C',
            NULL
         )
            DESRIPTION,
         NULL Status,
         1 Query
  FROM   AP_CHECKS_ALL ACA,
         FND_USER FU,
         CE_BANK_ACCT_USES_ALL CBAU,
         CE_BANK_ACCOUNTS CBA,
         GL_CODE_COMBINATIONS GCC,
         FND_FLEX_VALUES_VL A,
         FND_FLEX_VALUES_VL B,
         HZ_PARTIES HZP
 WHERE   ACA.ORG_ID = :P_ORG_ID AND HZP.PARTY_ID = ACA.PARTY_ID
         AND DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME) =
               NVL (
                  :P_BATCH_NAME,
                  DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME)
               )
         AND aca.doc_sequence_value BETWEEN NVL (:P_VOU_NUM,
                                                 aca.doc_sequence_value)
                                        AND  NVL (:P_VOU_TO,
                                                  aca.doc_sequence_value)
         /*AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) >=
                      NVL(:P_VOU_NUM,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))
                  AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) <=
                      NVL(:P_VOU_TO,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))*/
         AND ACA.CHECK_NUMBER >= NVL (:P_CHECK_NUMBER, ACA.CHECK_NUMBER)
         AND ACA.CHECK_NUMBER <= NVL (:P_CHECK_TO, ACA.CHECK_NUMBER)
         AND ACA.CE_BANK_ACCT_USE_ID =
               NVL (:P_BANK_ACCOUNT_ID, ACA.CE_BANK_ACCT_USE_ID)
         AND HZP.PARTY_NAME = NVL (:P_VENDOR_NAME, HZP.PARTY_NAME)
         AND FU.USER_ID = ACA.CREATED_BY
         AND CBAU.BANK_ACCT_USE_ID = ACA.CE_BANK_ACCT_USE_ID
         AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
         AND GCC.CODE_COMBINATION_ID = CBA.CASH_CLEARING_CCID
         AND A.FLEX_VALUE = GCC.SEGMENT3
         AND B.FLEX_VALUE = GCC.SEGMENT4
         AND B.PARENT_FLEX_VALUE_LOW = A.FLEX_VALUE
         AND aca.created_by = NVL (:p_created_by, aca.created_by)
UNION ALL
SELECT   ACA.PAYMENT_TYPE_FLAG PAYMENT_TYPE,
         ACA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,
         ACA.CHECKRUN_NAME BATCH_NAME,
         ACA.BANK_ACCOUNT_NUM,
         FU.USER_NAME,
         ACA.CHECK_DATE GL_DATE,
         ACA.CHECK_ID,
         ACA.CHECK_ID CHECK_ID1,
         ACA.CHECK_NUMBER,
         ACA.VENDOR_ID,
         aca.vendor_site_id,
         NVL (
            (SELECT   aia.attribute2
               FROM   ap_invoice_payments_all aip, ap_invoices_all aia
              WHERE       aip.invoice_id = aia.invoice_id
                      AND aip.check_id = aca.check_id
                      AND ROWNUM = 1),
            HZP.PARTY_NAME
         )
            VENDOR_NAME,
         ACA.PARTY_ID,
         ACA.DOC_SEQUENCE_VALUE VOUCHER_NO,
         ACA.AMOUNT * NVL (ACA.EXCHANGE_RATE, 1) * -1 CREDIT_AMOUNT,
         GCC.SEGMENT2 COST_CENTER,
         GCC.SEGMENT3 || '.' || GCC.SEGMENT4 ACCOUNT_CODE,
         REPLACE (
            DECODE (GCC.SEGMENT4,
                    '00', A.DESCRIPTION,
                    A.DESCRIPTION || '.' || B.DESCRIPTION),
            'CLEARING A/C',
            NULL
         )
            DESRIPTION,
         'VOIDED' status,
         2 Query
  FROM   AP_CHECKS_ALL ACA,
         FND_USER FU,
         CE_BANK_ACCT_USES_ALL CBAU,
         CE_BANK_ACCOUNTS CBA,
         GL_CODE_COMBINATIONS GCC,
         FND_FLEX_VALUES_VL A,
         FND_FLEX_VALUES_VL B,
         HZ_PARTIES HZP
 WHERE   ACA.ORG_ID = :P_ORG_ID AND HZP.PARTY_ID = ACA.PARTY_ID
         AND DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME) =
               NVL (
                  :P_BATCH_NAME,
                  DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME)
               )
         AND aca.doc_sequence_value BETWEEN NVL (:P_VOU_NUM,
                                                 aca.doc_sequence_value)
                                        AND  NVL (:P_VOU_TO,
                                                  aca.doc_sequence_value)
         /* AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) >=
                      NVL(:P_VOU_NUM,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))
                  AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) <=
                      NVL(:P_VOU_TO,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))*/
         AND ACA.CHECK_NUMBER >= NVL (:P_CHECK_NUMBER, ACA.CHECK_NUMBER)
         AND ACA.CHECK_NUMBER <= NVL (:P_CHECK_TO, ACA.CHECK_NUMBER)
         AND ACA.CE_BANK_ACCT_USE_ID =
               NVL (:P_BANK_ACCOUNT_ID, ACA.CE_BANK_ACCT_USE_ID)
         AND HZP.PARTY_NAME = NVL (:P_VENDOR_NAME, HZP.PARTY_NAME)
         AND FU.USER_ID = ACA.CREATED_BY
         AND CBAU.BANK_ACCT_USE_ID = ACA.CE_BANK_ACCT_USE_ID
         AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
         AND GCC.CODE_COMBINATION_ID = CBA.CASH_CLEARING_CCID
         AND A.FLEX_VALUE = GCC.SEGMENT3
         AND B.FLEX_VALUE = GCC.SEGMENT4
         AND B.PARENT_FLEX_VALUE_LOW = A.FLEX_VALUE
         AND ACA.status_lookup_code = 'VOIDED'
         AND aca.created_by = NVL (:p_created_by, aca.created_by)
ORDER BY   14, 20


query :2

 SELECT   ABA.BATCH_NAME INVOICE_BATCH,
           PV.VENDOR_NAME,
           AIA.INVOICE_NUM,
           AIPA.AMOUNT,
           AIA.INVOICE_ID,
           AIA.ORG_ID,
           AIPA.CHECK_ID,
           ROWNUM S_NO_INV
    FROM   AP_INVOICES_ALL AIA,
           PO_VENDORS PV,
           AP_BATCHES_ALL ABA,
           AP_INVOICE_PAYMENTS_ALL AIPA
   WHERE       ABA.BATCH_ID = AIA.BATCH_ID
           AND AIA.VENDOR_ID = PV.VENDOR_ID
           AND AIA.INVOICE_ID = AIPA.INVOICE_ID
           AND AIA.ORG_ID = AIPA.ORG_ID
           AND AIA.ORG_ID = :P_ORG_ID
--&P_DYAMIC_QUERY
ORDER BY   PV.VENDOR_NAME