Install Base Expiration Program

CREATE OR REPLACE PROCEDURE apps.xxstx_expire_ib_instance
IS
   l_instance_rec            csi_datastructures_pub.instance_rec;
   l_txn_rec                 csi_datastructures_pub.transaction_rec;
   l_instance_id_lst         csi_datastructures_pub.id_tbl;
   l_cust_account_id         NUMBER;
   l_party_id                NUMBER;
   l_line_type_id            NUMBER;
   l_msg_index_out           NUMBER;                         -- scratch index
   x_return_status           VARCHAR2 (1);
   x_msg_data                VARCHAR2 (4000);
   x_msg_count               NUMBER;
   g_message                 VARCHAR2 (4000);
   l_contract_number         NUMBER;
   l_inventory_item_id       NUMBER;
   l_segment1                VARCHAR2 (100);
   l_serial_number           VARCHAR2 (100);
   l_start_date              DATE;
   l_count                   NUMBER;
   l_count1                  NUMBER;
   l_instance_id             VARCHAR2 (20);
   l_msg_data                VARCHAR2 (2000);
   l_mesg                    VARCHAR2 (4000);
   l_mesg_count              NUMBER;
   l_item                    VARCHAR2 (100);
   l_instance_number         VARCHAR2 (150);
   l_inv_master_org_id       NUMBER;
   l_object_version_number   NUMBER;
   no_need_expire            EXCEPTION;

   CURSOR c1
   IS
      SELECT *
        FROM xxstx_bulk_exp_temp where REC_STS IS NULL;  --and serial_number = 'DMPJG2NNF18Y'; --Temp Table to store data, Custome table.

BEGIN
   -- fnd_global.apps_initialize(1230,22935,542);
   -- mo_global.set_policy_context('S',82);
   DBMS_OUTPUT.put_line ('IN FIRST BEGIN');

   FOR i IN c1
   LOOP
      DBMS_OUTPUT.put_line (   'Values got from Cursor = Serial_number:'
                            || i.serial_number
                            || ',item:'
                            || i.item
                           );

      BEGIN
         SELECT cii.instance_id, cii.serial_number, cii.inventory_item_id,
                cii.instance_number, cii.inv_master_organization_id,
                cii.object_version_number
           INTO l_instance_id, l_serial_number, l_inventory_item_id,
                l_instance_number, l_inv_master_org_id,
                l_object_version_number
           FROM csi_item_instances cii, mtl_system_items mtl
          WHERE cii.serial_number = i.serial_number            --'35200400288'
            AND mtl.segment1 = i.item                     --'760B85G4E800N000'
            AND mtl.organization_id = 101
            AND mtl.inventory_item_id = cii.inventory_item_id
            AND cii.instance_number =
                                  NVL (i.instance_number, cii.instance_number);

         BEGIN
            DBMS_OUTPUT.put_line (   'Values got from Query :'
                                  || l_instance_id
                                  || l_serial_number
                                  || l_inventory_item_id
                                  || l_instance_number
                                  || l_inv_master_org_id
                                  || l_object_version_number
                                 );

            SELECT COUNT (contract_number)
              INTO l_count
              FROM (SELECT contract_number
                      FROM okc_k_headers_all_b
                     WHERE ID IN (SELECT MAX (dnz_chr_id)
                                    FROM okc_k_items
                                   WHERE object1_id1 = l_instance_id)
                       AND sts_code IN
                                   ('ACTIVE', 'QA_HOLD', 'SIGNED', 'ENTERED')
                    UNION
                    SELECT contract_number
                      FROM okc_k_headers_all_b
                     WHERE ID IN (SELECT MAX (dnz_chr_id)
                                    FROM okc_k_items
                                   WHERE object1_id1 = l_inventory_item_id)
                       AND sts_code IN
                                   ('ACTIVE', 'QA_HOLD', 'SIGNED', 'ENTERED'));

            DBMS_OUTPUT.put_line
                  (   'If statement for serial nas active number of contracts:  '
                   || l_count
                  );

            IF l_count = 0
            THEN
               DBMS_OUTPUT.put_line ('If statement if  count =0');
               l_instance_rec.instance_id := l_instance_id;
               l_instance_rec.instance_number := l_instance_number;
               l_instance_rec.external_reference := NULL;
               l_instance_rec.inventory_item_id := l_inventory_item_id;
               l_instance_rec.inv_master_organization_id :=
                                                          l_inv_master_org_id;
               l_instance_rec.active_end_date := TRUNC (SYSDATE);
               l_instance_rec.object_version_number :=
                                                      l_object_version_number;
               l_txn_rec.transaction_date := TRUNC (SYSDATE);
               l_txn_rec.source_transaction_date := TRUNC (SYSDATE);
               l_txn_rec.transaction_type_id := 1;
               l_txn_rec.object_version_number := 1;
               DBMS_OUTPUT.put_line ('EXPIRE INSATCNE');
               csi_item_instance_pub.expire_item_instance
                                     (p_api_version          => 1.0
                                                                   -- IN      NUMBER
               ,
                                      p_instance_rec         => l_instance_rec
                                                                              -- IN      csi_datastructures_pub.instance_rec
               ,
                                      p_txn_rec              => l_txn_rec
                                                                         -- IN OUT  NOCOPY csi_datastructures_pub.transaction_rec
               ,
                                      x_instance_id_lst      => l_instance_id_lst
                                                                                 -- OUT     NOCOPY csi_datastructures_pub.id_tbl
               ,
                                      x_return_status        => x_return_status
                                                                               -- OUT     NOCOPY VARCHAR2
               ,
                                      x_msg_count            => x_msg_count
                                                                           -- OUT     NOCOPY NUMBER
               ,
                                      x_msg_data             => x_msg_data
                                     -- OUT     NOCOPY VARCHAR2
                                     );
               DBMS_OUTPUT.put_line ('EXPIRE INSATCNE DONE');
               DBMS_OUTPUT.put_line ('API Status:=' || x_return_status);

               IF x_return_status != apps.fnd_api.g_ret_sts_success
               THEN
                  DBMS_OUTPUT.put_line
                     ('failed. printing error msg... at owner and LOCATION changes'
                     );
                  fnd_msg_pub.get (p_msg_index          => apps.fnd_msg_pub.g_last,
                                   p_encoded            => apps.fnd_api.g_false,
                                   p_data               => x_msg_data,
                                   p_msg_index_out      => l_msg_index_out
                                  );
                  g_message := x_msg_data;
                  --fnd_file.put_line (fnd_file.LOG,
                               --          'Instance # '
                                ----      || l_instance_number                                     || ' could not be expired. Error: '                                    || g_message
                                --      );
                  DBMS_OUTPUT.put_line (   'Instance # '
                                        || l_instance_number
                                        || ' could not be expired. Error: '
                                        || g_message
                                       );
                  ROLLBACK;
               ELSE
                  DBMS_OUTPUT.put_line
                     (' IB instance updated sucessfully at owner and LOCATION changes '
                     );
                  DBMS_OUTPUT.put_line ('Step8 : end update API -');
                  NULL;
               END IF;

               UPDATE xxstx_bulk_exp_temp
                  SET rec_sts = 'PROCESSED'
                WHERE serial_number = l_serial_number;

               DBMS_OUTPUT.put_line ('UPDATED TABLE');
            ELSE
               UPDATE xxstx_bulk_exp_temp
                  SET rec_sts = 'ACTIVE CONTRACT'
                WHERE serial_number = l_serial_number;

               DBMS_OUTPUT.put_line ('l_count is More than 0' || l_count);
            END IF;
         END;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line (' Exception NO DATA FOUND');
      END;
   END LOOP;
   COMMIT;
EXCEPTION
   WHEN no_need_expire
   THEN
      DBMS_OUTPUT.put_line (' no_need_expire NO DATA FOUND');
   WHEN OTHERS
   THEN
      fnd_file.put_line (fnd_file.LOG, 'Error in Main:=' || SQLERRM);
END xxstx_expire_ib_instance;
/

Oracle Install Base Query

/* Formatted on 3/6/2014 2:10:52 PM (QP5 v5.256.13226.35510) */
--INSTALLED BASE ROOT ITEM QUERY
--ENTER A PARTY NUMBER AND GET RESULTS

SELECT SYSDATE,
       cipv.instance_number AS "Instance Number",
       (SELECT hp.party_name
          FROM ar.hz_parties hp
         WHERE hp.party_id = cipv.party_id)
          AS "Owner Name",
       (SELECT hp.party_number
          FROM ar.hz_parties hp
         WHERE hp.party_id = cipv.party_id)
          AS "Owner Party",
       (SELECT hca.account_number
          FROM ar.hz_cust_accounts hca
         WHERE hca.cust_account_id = cipv.party_account_id)
          AS "Owner Account",
       cidv.concatenated_segments AS "Item",
       cidv.description AS "Item Description",
       cidv.quantity AS "Qty",
       cidv.unit_of_measure AS "UOM",
       cidv.status_name AS "ISB Status",
       cidv.external_reference AS "External Reference (Tag)",
       cidv.instance_description AS "Instance Name",
       cidv.instance_usage_code AS "ISB Usage Code",
       cidv.serial_number AS "Serial",
       cidv.lot_number AS "Lot",
       cidv.instance_type_code AS "ISB Instance Type",
       cidv.install_date "Install Date",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'ALTITUDE')
          AS "Altitude (Value and Unit)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'BACNET')
          AS "BACNET (Device, Protocol)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'CELLULAR')
          AS "Celluar (Type, Carrier, #)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'CONFIG_CODE')
          AS "Configuration Code",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'CONFIG_COMMENT')
          AS "Configuration Comment",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'ELEVATION')
          AS "Elevation (Value and Unit)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'ETHERNET')
          AS "Ethernet (Type, IP Address)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'HIGHWAY_LOCATION')
          AS "Highway Location",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'ICAO_CODE')
          AS "ICAO Code",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'LATITUDE')
          AS "Latitude (DDD MM.MMM)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'LOCAL_PARTNER')
          AS "Local Partner",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'LONGITUDE')
          AS "Longitude (DDD MM.MMM)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'MAGNETIC_DECLINATION')
          AS "Magnetic Declination (Deg)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'MAINS_TYPE')
          AS "Mains Type",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'MAST_HEIGHT')
          AS "Mast Height (m)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'MODBUS')
          AS "Modbus (Device, Protocol)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'PHONE_PSTN')
          AS "Phone/PSTN (Type, Carrier, #)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'RADIO')
          AS "Radio (Type, Freq)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'ROAD_NUMBER')
          AS "Road Number",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'SATELLITE')
          AS "Satellite (Type, Provider)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'SENSOR_PROTOCOL')
          AS "Sensor Protocol (Type)",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'SERVICE_PROVIDER')
          AS "Service Provider",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'SITE_NAME')
          AS "Site Name",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'STATION_ID')
          AS "Station ID",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'XXVAI_ITEM_REV')
          AS "Vaisala Item Revision",
       (SELECT cieav.attribute_value
          FROM APPS.CSI_INST_EXTEND_ATTRIB_V cieav
         WHERE     cieav.instance_id = cipv.instance_id
               AND cieav.attribute_code = 'WMO_CODE')
          AS "WMO Code",
       DECODE (
          cidv.location_type_code,
          'HZ_PARTY_SITES',    (SELECT DISTINCT hpsv.address1
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id = cidv.location_id)
                            || '--'
                            || (SELECT DISTINCT hpsv.city
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id = cidv.location_id)
                            || '--'
                            || (SELECT DISTINCT hpsv.state
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id = cidv.location_id),
          'INVENTORY',    'VAISALA -- '
                       || (SELECT ionv.organization_name
                             FROM APPS.INV_ORGANIZATION_NAME_V ionv
                            WHERE ionv.organization_id =
                                     cidv.inv_organization_id)
                       || '--'
                       || cidv.inv_subinventory_name,
          'UNKNOWN')
          "Current_Location",
       DECODE (
          cidv.install_location_type_code,
          'HZ_PARTY_SITES',    (SELECT DISTINCT hpsv.address1
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id =
                                          cidv.install_location_id)
                            || '--'
                            || (SELECT DISTINCT hpsv.city
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id =
                                          cidv.install_location_id)
                            || '--'
                            || (SELECT DISTINCT hpsv.state
                                  FROM apps.hz_party_sites_v hpsv
                                 WHERE hpsv.party_site_id =
                                          cidv.install_location_id),
          NULL, 'NULL',
          'UNKNOWN')
          "Install_Location",
       (SELECT DISTINCT
               hpsv.address1 || '--' || hpsv.city || '--' || hpsv.state
          FROM apps.hz_party_sites_v hpsv
         WHERE hpsv.party_site_id =
                  (SELECT hcasa.party_site_id
                     FROM ar.hz_cust_acct_sites_all hcasa
                    WHERE hcasa.cust_acct_site_id =
                             (SELECT hcsua.cust_acct_site_id
                                FROM ar.hz_cust_site_uses_all hcsua
                               WHERE cipv.bill_to_address = hcsua.site_use_id)))
          AS "Bill To",
       (SELECT DISTINCT
               hpsv.address1 || '--' || hpsv.city || '--' || hpsv.state
          FROM apps.hz_party_sites_v hpsv
         WHERE hpsv.party_site_id =
                  (SELECT hcasa.party_site_id
                     FROM ar.hz_cust_acct_sites_all hcasa
                    WHERE hcasa.cust_acct_site_id =
                             (SELECT hcsua.cust_acct_site_id
                                FROM ar.hz_cust_site_uses_all hcsua
                               WHERE cipv.ship_to_address = hcsua.site_use_id)))
          AS "Ship To",
       DECODE ( (SELECT COUNT (*)
                   FROM CSI.CSI_II_RELATIONSHIPS CIR
                  WHERE CIR.OBJECT_ID = cipv.INSTANCE_ID),
               0, 'No BOM Components',
               'BOM Components Exist')
          AS "BOM?"
  FROM APPS.CSI_INSTANCE_PARTY_V cipv, apps.csi_instance_details_v cidv
 WHERE     1 = 1
       AND cidv.instance_id = cipv.instance_id
       AND cipv.instance_usage_code <> 'IN_RELATIONSHIP' --ROOT INSTANCES ONLY
       AND ( (cipv.party_account_id =
                 (SELECT hca.cust_account_id
                    FROM ar.hz_cust_accounts hca
                   WHERE hca.account_number IN TO_CHAR (&AccountNumber))));

Query to Find Junk Characters in table

SELECT   okh.contract_number, okl.line_number, okl.attribute6, okl.sts_code

    FROM okc_k_headers_all_b okh, okc_k_lines_b okl

   WHERE okh.ID = okl.dnz_chr_id AND okl.attribute6 IS NOT NULL

  HAVING REGEXP_COUNT(okl.attribute6, '[''~!@#$%^&*(),+=}{;:><?]') >= 1

GROUP BY okh.contract_number, okl.line_number, okl.attribute6, okl.sts_code

once it is identified remove it by updating table using below query  

REGEXP_REPLACE(okl.attribute6,'[''~!@#$%^&*(),+=}{;:><?]',NULL)

Sales Order Details

SELECT wdd.source_header_number order_number,
       mtrh.request_number move_order_number,
       wnd.global_attribute14 actual_delivery_date,
       mtrh.creation_date move_order_date, wdd.creation_date,
       wdd.source_header_type_name order_type, hp.party_name bill_to_customer,
       hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       wdd.src_requested_quantity ordered_quantity, wdd.shipped_quantity,
       CASE when oel.SOURCE_DOCUMENT_ID is null then
             wdd.cancelled_quantity
             else 
       null
       end as cancelled_quantity
       , oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       CASE
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity
               ) = 0
             THEN 'CLOSED'
          WHEN wdd.shipped_quantity IS NULL
             THEN 'AWAITING SHIPPING'
          WHEN wdd.shipped_quantity > 0
          AND wdd.shipped_quantity < wdd.src_requested_quantity
             THEN 'PARTIALLY SHIPED'
       END AS order_status,
       rct.trx_number, rct.trx_date, rct.status_trx,
       DECODE (wdd.released_status,
               'D', 'Cancelled',
               'C', 'Shipped',
               'R', 'Ready to Release',
               'N', 'Not Ready to Release',
               'S', 'Released to Warehouse',
               'Y', 'Staged/Pick Confirmed',
               'X', 'Not Applicable'
              ) released_status_name,
       ROUND (DECODE ((oel.ordered_quantity - wdd.shipped_quantity),
                      0, NULL,
                      TRUNC (SYSDATE) - mtrh.creation_date
                     ),
              0
             ) pending_since,
       wdd.released_status, NULL, NULL
  FROM wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       oe_order_headers_all oeh,
       oe_order_lines_all oel,
       hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       mtl_system_items_b msi,
       ra_customer_trx_lines_all rctl,
       ra_customer_trx_all rct,
       org_organization_definitions ood,
       mtl_txn_request_lines mtrl,
       mtl_txn_request_headers mtrh
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id(+)
   AND NVL (wdd.line_direction, 'O') IN ('O', 'IO')
   AND NVL (wda.TYPE, 'S') IN ('S', 'C')
   AND wdd.source_header_id = oeh.header_id
   AND wdd.source_line_id = oel.line_id
   AND oeh.header_id = oel.header_id
   AND hp.party_id = hca.party_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND msi.inventory_item_id = oel.inventory_item_id
   AND msi.organization_id = oel.ship_from_org_id
   ------------following join to get those order whose invoices are not created--------
   AND rctl.interface_line_attribute6(+)=oel.line_id
   AND rct.customer_trx_id(+) = rctl.customer_trx_id
   --AND rctl.interface_line_attribute1 = TO_CHAR (oeh.order_number)
   AND wdd.move_order_line_id = mtrl.line_id
   AND oel.ship_from_org_id = ood.organization_id
   AND mtrh.header_id = mtrl.header_id
   AND wdd.move_order_line_id = mtrl.line_id
    --and wdd.source_header_number=112104873 --112909588 --112400057
    AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                      AND NVL (:t_date, oeh.ordered_date)
union
SELECT wdd.source_header_number order_number,
       mtrh.request_number move_order_number,
       wnd.global_attribute14 actual_delivery_date, mtrh.creation_date move_order_date,
       wdd.creation_date, wdd.source_header_type_name order_type,
       hp.party_name bill_to_customer, hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       wdd.src_requested_quantity ordered_quantity, wdd.shipped_quantity,
       CASE when oel.SOURCE_DOCUMENT_ID is null then
             wdd.cancelled_quantity
             else 
       null
       end as cancelled_quantity
       , oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       CASE
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) =
                                                            0 AND wdd.released_status<>'D'
             THEN 'CLOSED'
          WHEN wdd.shipped_quantity IS NULL
             THEN 'AWAITING SHIPPING'
          WHEN wdd.shipped_quantity > 0
          AND wdd.shipped_quantity < wdd.src_requested_quantity
             THEN 'PARTIALLY SHIPPED'
             WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) =
                                                            0 AND wdd.released_status='D'
             THEN 'CANCELLED'
       END AS order_status,
       NULL, NULL, NULL, NULL,
       ROUND (DECODE ((oel.ordered_quantity - wdd.shipped_quantity),
                      0, NULL,
                      TRUNC (SYSDATE) - wdd.creation_date
                     ),
              0
             ) pending_since,
       wdd.released_status,null,null/*, oer.reason_code, oer.comments*/
  FROM wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       oe_order_headers_all oeh,
       oe_order_lines_all oel,
       hz_parties hp,
       hz_cust_accounts hca,
        hz_party_sites hps,
       mtl_system_items_b msi,
       org_organization_definitions ood,
       mtl_txn_request_lines mtrl,
       mtl_txn_request_headers mtrh   
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id(+)
  -- AND NVL (wdd.line_direction, 'O') IN ('O', 'IO')
   AND wdd.source_header_id = oeh.header_id
   AND wdd.source_line_id = oel.line_id
   AND oeh.header_id = oel.header_id
   AND hp.party_id = hca.party_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND msi.inventory_item_id = oel.inventory_item_id
   AND msi.organization_id = oel.ship_from_org_id
   AND oel.ship_from_org_id = ood.organization_id
    AND mtrh.header_id = mtrl.header_id
    AND wdd.move_order_line_id =NVL( mtrl.line_id,wdd.move_order_line_id)
  AND wdd.released_status IN ( 'B','S','R')
   --and wdd.source_header_number=112104873 --112909588
   AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                      AND NVL (:t_date, oeh.ordered_date)
   Union
   SELECT wdd.source_header_number order_number,
       NULL move_order_number,
       wnd.global_attribute14 actual_delivery_date,
       NULL move_order_date, wdd.creation_date,
       wdd.source_header_type_name order_type, hp.party_name bill_to_customer,
       hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       wdd.src_requested_quantity ordered_quantity, wdd.shipped_quantity,
       CASE when oel.SOURCE_DOCUMENT_ID is null then
             wdd.cancelled_quantity
             else 
       null
       end as cancelled_quantity
       , oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       CASE
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity
               ) = 0
          AND wdd.released_status <> 'D'
             THEN 'CLOSED'
          WHEN wdd.shipped_quantity IS NULL
             THEN 'AWAITING SHIPPING'
          WHEN wdd.shipped_quantity > 0
          AND wdd.shipped_quantity < wdd.src_requested_quantity
             THEN 'PARTIALLY SHIPPED'
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) = 0
          AND wdd.released_status = 'D'
             THEN 'CANCELLED'
       END AS order_status,
       NULL, NULL, NULL, NULL,
       ROUND (DECODE ((oel.ordered_quantity - wdd.shipped_quantity),
                      0, NULL,
                      TRUNC (SYSDATE) - wdd.creation_date
                     ),
              0
             ) pending_since,
       wdd.released_status, NULL,NULL --oer.reason_code, oer.comments
  FROM wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       oe_order_headers_all oeh,
       oe_order_lines_all oel,
       hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       mtl_system_items_b msi,
       org_organization_definitions ood
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id(+)
   -- AND NVL (wdd.line_direction, 'O') IN ('O', 'IO')
   AND wdd.source_header_id = oeh.header_id
   AND wdd.source_line_id = oel.line_id
   AND oeh.header_id = oel.header_id
   AND hp.party_id = hca.party_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND msi.inventory_item_id = oel.inventory_item_id
   AND msi.organization_id = oel.ship_from_org_id
   AND oel.ship_from_org_id = ood.organization_id
   AND wdd.released_status IN   ( 'B','S','R')
   AND wdd.move_order_line_id IS NULL
   --AND wdd.source_header_number = 112104873
   AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                      AND NVL (:t_date, oeh.ordered_date) 
   UNION
SELECT wdd.source_header_number order_number,
       mtrh.request_number move_order_number,
       wnd.global_attribute14 actual_delivery_date,
       mtrh.creation_date move_order_date, wdd.creation_date,
       wdd.source_header_type_name order_type, hp.party_name bill_to_customer,
       hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       wdd.src_requested_quantity ordered_quantity, wdd.shipped_quantity,
       CASE when oel.SOURCE_DOCUMENT_ID is null then
             wdd.cancelled_quantity
             else 
       null
       end as cancelled_quantity
       , oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       CASE
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity
               ) = 0
          AND wdd.released_status <> 'D'
             THEN 'CLOSED'
          WHEN wdd.shipped_quantity IS NULL
             THEN 'AWAITING SHIPPING'
          WHEN wdd.shipped_quantity > 0
          AND wdd.shipped_quantity < wdd.src_requested_quantity
             THEN 'PARTIALLY SHIPPED'
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) = 0
          AND wdd.released_status = 'D'
             THEN 'CANCELLED'
       END AS order_status,
       NULL, NULL, NULL, NULL,
       ROUND (DECODE ((oel.ordered_quantity - wdd.shipped_quantity),
                      0, NULL,
                      TRUNC (SYSDATE) - wdd.creation_date
                     ),
              0
             ) pending_since,
       wdd.released_status, oer.reason_code, oer.comments
  FROM wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       oe_order_headers_all oeh,
       oe_order_lines_all oel,
       hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       mtl_system_items_b msi,
       org_organization_definitions ood,
       mtl_txn_request_lines mtrl,
       mtl_txn_request_headers mtrh  ,oe_order_lines_history lh ,                                      
                                               oe_reasons oer
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id(+)
   -- AND NVL (wdd.line_direction, 'O') IN ('O', 'IO')
   AND wdd.source_header_id = oeh.header_id
   AND wdd.source_line_id = oel.line_id
   AND oeh.header_id = oel.header_id
   AND hp.party_id = hca.party_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND msi.inventory_item_id = oel.inventory_item_id
   AND msi.organization_id = oel.ship_from_org_id
   AND oel.ship_from_org_id = ood.organization_id
   AND mtrh.header_id(+) = mtrl.header_id
   AND mtrl.line_id(+) = wdd.move_order_line_id
   AND oel.line_id = lh.line_id
    AND lh.reason_id = oer.reason_id
   AND wdd.released_status IN  ('D')
   --AND wdd.source_header_number = 112104873                         --112909588
   AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                      AND NVL (:t_date, oeh.ordered_date)
    UNION       
SELECT TO_CHAR (oeh.order_number) order_number, NULL move_order_number,
       NULL actual_delivery_date, NULL move_order_date, NULL, NULL order_type,
       hp.party_name bill_to_customer, hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       oel.ordered_quantity ordered_quantity, NULL, NULL,
       oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       oel.flow_status_code order_status, NULL, NULL, NULL, NULL, NULL, NULL,
       NULL, NULL
  FROM oe_order_headers_all oeh,
       oe_order_lines_all oel,
       hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       mtl_system_items_b msi,
       org_organization_definitions ood
WHERE oeh.header_id = oel.header_id(+)--this join for rows whose line level data is not available
   AND hp.party_id = hca.party_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND msi.inventory_item_id(+)=oel.inventory_item_id--this join for rows whose line level data is not available
   AND msi.organization_id(+) = oel.ship_from_org_id--this join for rows whose line level data is not available
   AND oel.ship_from_org_id = ood.organization_id(+)--this join for rows whose line level data is not available
   --  and wdd.RELEASED_STATUS in('D','B')
     --and oeh.ORDER_NUMBER =112105131 --112909588--112104621
    AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                     AND NVL (:t_date, oeh.ordered_date)
      AND oeh.flow_status_code = 'ENTERED' 
      Union
---------this union for order Booked but his transactions are not done in delivery table and for cancelled order
      SELECT to_char(oeh.ORDER_NUMBER)  order_number,
       mtrh.request_number move_order_number,
       wnd.global_attribute14 actual_delivery_date, mtrh.creation_date move_order_date,
       wdd.creation_date, wdd.source_header_type_name order_type,
       hp.party_name bill_to_customer, hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       wdd.src_requested_quantity ordered_quantity, wdd.shipped_quantity,
       CASE when oel.SOURCE_DOCUMENT_ID is null then
             wdd.cancelled_quantity
             else 
       null
       end as cancelled_quantity,
        oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       CASE
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) =
                                                            0 AND wdd.released_status<>'D'
             THEN 'CLOSED'
          WHEN wdd.shipped_quantity IS NULL
             THEN 'AWAITING SHIPPING'
          WHEN wdd.shipped_quantity > 0
          AND wdd.shipped_quantity < wdd.src_requested_quantity
             THEN 'PARTIALLY SHIPPED'
             WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) =
                                                            0 AND wdd.released_status='D'
             THEN 'CANCELLED'
       END AS order_status,
       NULL, NULL, NULL, NULL,
       ROUND (DECODE ((oel.ordered_quantity - wdd.shipped_quantity),
                      0, NULL,
                      TRUNC (SYSDATE) - wdd.creation_date
                     ),
              0
             ) pending_since,
       wdd.released_status,null,null/*, oer.reason_code, oer.comments*/
  FROM oe_order_headers_all oeh,
       oe_order_lines_all oel,
       wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       hz_party_sites hps,
       hz_cust_accounts hca,
       hz_parties hp,
       mtl_system_items_b msi,
       org_organization_definitions ood,
       mtl_txn_request_lines mtrl,
       mtl_txn_request_headers mtrh
WHERE oeh.header_id = oel.header_id
--AND wdd.source_header_id = oeh.header_id
   AND wdd.source_line_id(+) = oel.line_id
   AND wda.delivery_id = wnd.delivery_id(+)
   AND wda.delivery_detail_id(+) = wdd.delivery_detail_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hp.party_id = hca.party_id
   AND msi.inventory_item_id = oel.inventory_item_id
   AND msi.organization_id = oel.ship_from_org_id
   AND oel.ship_from_org_id = ood.organization_id
   AND wdd.move_order_line_id = mtrl.line_id(+)
   AND mtrh.header_id(+) = mtrl.header_id
   AND NVL (wdd.released_status, 'N') IN ('N')
   --AND oeh.order_number = 112909462
  AND oeh.flow_status_code in ( 'BOOKED','CANCELLED')
   AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                      AND NVL (:t_date, oeh.ordered_date)