Blanket Release Interface or conversion in Oracle R12

Overview:

A blanket release is an actual order of goods or services you raise against a blanket purchase agreement. The blanket purchase agreement determines the characteristics and the prices of the items. The release can be created manually or automatically.

    This article help you to create or upload Blanket Releases from one instance to another instance.

I have used this article to upload Blanket Releases from 11i instance to R12.2.7

Use below steps for complete your Interface or Conversion of Blanket Releases from Source instance to Destination instance.

1. Insert data in staging table.

    Insert data in staging table using PL/SQL block or manually.

Use below data extract query to get data from Source instance:

     SELECT pha.segment1 po_number,

                  pla.line_num,

                  msib.segment1 item_number,

                  msib.description item_description,

                  (plla.quantity - (plla.quantity_received + plla.quantity_cancelled)) quantity,

                  pla.unit_price base_unit_price,

                  hr.location_code deliver_to_location_code,

                  pha.type_lookup_code document_type_code,

                  pr.creation_date,

                  papf.full_name deliver_to_requestor_name,

                  ood.organization_code destination_organization_code,

                  plla.need_by_date,
                  msib.primary_uom_code supplier_uom,

                  papf.full_name preparer_name,

                  pr.po_release_id batch_id, -- Or use any sequence for BATCH_ID

                  pha.org_id,

                  plla.shipment_num,

                  pr.release_num,

                  pr.release_date,

glcc.segment1||'.'||glcc.segment2||'.'||glcc.segment3||'.'||glcc.segment4||'.'||glcc.segment5||'.'||glcc.segment6||'.'||glcc.segment7 charge_account

      FROM mtl_system_items_b            msib

               ,  po_lines_all                  pla

               ,  po_line_locations_all         plla

               ,  po_headers_all                pha

               ,  hr_locations                  hr

               ,  org_organization_definitions  ood

               ,  po_releases_all               pr

               ,  per_all_people_f              papf

               ,  po_distributions_all          pod

               ,  gl_code_combinations          glcc

    WHERE pla.item_id                  = msib.inventory_item_id

         AND pla.po_line_id               = plla.po_line_id

         AND pha.po_header_id             = pla.po_header_id

         AND pha.ship_to_location_id      = hr.location_id
         AND hr.inventory_organization_id = msib.organization_id

         AND pha.po_header_id             = pr.po_header_id

         AND plla.po_release_id           = pr.po_release_id

         AND pha.agent_id                 = papf.person_id

         AND pha.org_id                   = pla.org_id(+)

         AND pla.po_line_id               = pod.po_line_id

         AND plla.po_header_id            = pod.po_header_id

         AND pod.code_combination_id      = glcc.code_combination_id(+)

         AND plla.line_location_id        = pod.line_location_id

         AND pha.type_lookup_code         = 'BLANKET'

         AND pha.authorization_status     = 'APPROVED'

         AND NVL(msib.outside_operation_flag,'N') !='Y'

         AND msib.organization_id         = ood.organization_id

         AND pha.agent_id                 = papf.person_id

         AND pha.org_id                   = pla.org_id

         AND pha.org_id                   = 204             -- Operating Unit wise data

         AND plla.po_release_id IS NOT NULL

         AND NVL (pla.closed_code, 'OPEN') IN ('OPEN')

         AND NVL (plla.closed_code, 'OPEN') IN ('OPEN')

         AND NVL (pha.closed_code, 'OPEN') IN ('OPEN')

         AND NVL (pr.closed_code, 'OPEN') IN ('OPEN')

         AND (plla.quantity - (plla.quantity_received + plla.quantity_cancelled)) > 0;

    **Interface Table:

    PO_REQUISITIONS_INTERFACE_ALL

2. Validate staging table data, create Package/Procedure for validating data:

For Example:
**Package Specification
-------------------------------
      CREATE OR REPLACE PACKAGE xxpo_bpo_releases_pkg
      AS
          PROCEDURE XX_main_prc (
            p_errbuf            OUT      VARCHAR2,
            p_retcode           OUT      VARCHAR2,
            p_organization_id   IN       NUMBER
           );
      END xxpo_bpo_releases_pkg;
      /


**Package Body
--------------------------------
     CREATE OR REPLACE PACKAGE BODY XX_bpo_releases_pkg
     AS
        PROCEDURE xx_main_prc (
           p_errbuf                OUT      VARCHAR2,
           p_retcode              OUT      VARCHAR2,
           p_organization_id   IN       NUMBER
          )
     IS
      -- This cursor will fetch the data from xx_bpo_release_stg table for which process_flag is NULL.
         CURSOR lcur_po_bpo_releases (p_organization_id IN NUMBER)
         IS
            SELECT   po_number, item_description, quantity, base_unit_price,
                  deliver_to_location_code, document_type_code, line_num,
                  creation_date, deliver_to_requestor_name,
                  destination_organization_code, item_segment1, need_by_date,
                  preparer_name, requisition_type,
                  source_type_code, org_id, shipment_num, release_num,
                  release_date, supplier_uom, deliver_to_location_id,
                  deliver_to_person_id
             FROM xx_bpo_release_stg
              WHERE process_flag IS NULL
                   AND custom_error_message IS NULL
                   AND org_id = p_organization_id
            GROUP BY po_number,
                  item_description,
                  quantity,
                  base_unit_price,
                  deliver_to_location_code,
                  document_type_code,
                  line_num,
                  creation_date,
                  deliver_to_requestor_name,
                  destination_organization_code,
                  item_segment1,
                  need_by_date,
                  preparer_name,
                  requisition_type,

                  source_type_code,
                  org_id,
                  shipment_num,
                  release_num,
                  release_date,
                  supplier_uom,
                  deliver_to_location_id,
                  deliver_to_person_id;

          -- Declare Variables
          -- Header Level
             ln_user_id                     NUMBER         := fnd_global.user_id;
             lc_rec_status                 VARCHAR2 (10);
             lc_error_msg                  CLOB;
          --
             ln_deliver_to_location_code    NUMBER;
             ln_org_id2                     NUMBER         := p_organization_id;
             ln_headers_cnt                 NUMBER         := 0;
             lc_deliver_to_requestor_name   VARCHAR2 (240);
             lc_preparer_name               VARCHAR2 (240);
             lc_item_segment1               VARCHAR2 (80);
             ln_organization_id             NUMBER;
             ln_inventory_item_id           NUMBER;
             ln_item_id                     NUMBER;
             lc_release_num                 VARCHAR2 (20);
             lc_po_number                   VARCHAR2 (30);
             ln_po_header_id                NUMBER;
             ln_po_line_id                  NUMBER;
             ln_charge_account_id           NUMBER;
             ln_deliver_to_location_id      NUMBER;
             ln_deliver_to_person_id        NUMBER;
             lc_deliver_to_location         VARCHAR2 (250);
             lc_deliver_to_person           VARCHAR2 (250);
             ln_vendor_id                   NUMBER;
             ln_vendor_site_id              NUMBER;
           --
   BEGIN
      -- Start PO Header loop
      FOR lrec_release IN lcur_po_bpo_releases (ln_org_id2)
      LOOP
         -- Assign values to header level variables
         lc_rec_status := 'A';
         lc_error_msg := NULL;
         ln_deliver_to_location_code := 0;
         lc_deliver_to_requestor_name := NULL;
         lc_preparer_name := NULL;
         lc_item_segment1 := NULL;
         ln_organization_id := 0;
         ln_inventory_item_id := 0;
         ln_item_id := 0;
         lc_release_num := NULL;
         lc_po_number := NULL;
         ln_po_header_id := 0;
         ln_po_line_id := 0;
         ln_charge_account_id := NULL;
         ln_deliver_to_location_id := 0;
         ln_deliver_to_person_id := 0;
         lc_deliver_to_location := NULL;
         lc_deliver_to_person := NULL;
         ln_vendor_id := NULL;
         ln_vendor_site_id := NULL;

         -- Start Header level validation
         --
         --Validation of PO NUMBER
         IF (lrec_release.po_number IS NOT NULL) THEN
            BEGIN
               SELECT segment1, po_header_id, vendor_id,
                      vendor_site_id
                 INTO lc_po_number, ln_po_header_id, ln_vendor_id,
                      ln_vendor_site_id
                 FROM po_headers_all
                WHERE UPPER (segment1) = UPPER (lrec_release.po_number)
                  AND type_lookup_code = 'BLANKET'
                  AND (end_date_active IS NULL OR end_date_active > SYSDATE)
                  AND enabled_flag = 'Y'
                  AND authorization_status = 'APPROVED'
                  AND NVL (closed_code, 'OPEN') = 'OPEN';
            EXCEPTION
               WHEN TOO_MANY_ROWS THEN
                  fnd_file.put_line (fnd_file.LOG, 'Get more than one record of PO Number:' || lrec_release.po_number);
                  lc_rec_status := 'E';
               WHEN NO_DATA_FOUND THEN
                  fnd_file.put_line (fnd_file.LOG,'PO Number:' || lrec_release.po_number || ' not found.'||SQLERRM);
                  lc_rec_status := 'E';
            END;
         ELSE
            fnd_file.put_line (fnd_file.LOG, ' PO Number is empty.');
            lc_rec_status := 'E';
         END IF;

         -- Get po_line_id
         BEGIN
            SELECT po_line_id
              INTO ln_po_line_id
              FROM po_lines_all
             WHERE po_header_id = ln_po_header_id
               AND line_num = lrec_release.line_num;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               fnd_file.put_line (fnd_file.LOG, 'PO line no.:' || lrec_release.line_num || ' not found.' );
               lc_rec_status := 'E';
            WHEN OTHERS THEN
               fnd_file.put_line (fnd_file.LOG, 'Error in PO line no.:' || lrec_release.line_num || SQLERRM);
               lc_rec_status := 'E';
         END;

         -- Validation for deliver_to_location_code
         IF (lrec_release.deliver_to_location_code IS NOT NULL) THEN
           BEGIN
               SELECT location_id
               INTO ln_deliver_to_location_code
               FROM hr_locations_all
               WHERE UPPER (location_code) = UPPER (lrec_release.deliver_to_location_code);
           EXCEPTION
               WHEN TOO_MANY_ROWS THEN
                fnd_file.put_line (fnd_file.LOG, 'Get more than one record of Deliver to location code:' || lrec_release.deliver_to_location_code);
                lc_rec_status := 'E';
              WHEN NO_DATA_FOUND THEN
                fnd_file.put_line (fnd_file.LOG, 'Deliver to location code:' || lrec_release.deliver_to_location_code|| ' not found.' || SQLERRM );
                lc_rec_status := 'E';
             END;
         ELSE
            fnd_file.put_line (fnd_file.LOG, ' Deliver to location code is empty.' );
            lc_rec_status := 'E';
         END IF;

         -- Validation of Charge_account
         IF (lrec_release.charge_account IS NOT NULL) THEN
          BEGIN
             SELECT glcc.code_combination_id
             INTO ln_charge_account_id
             FROM gl_code_combinations glcc
            WHERE glcc.segment1|| '.'|| glcc.segment2|| '.'|| glcc.segment3|| '.'|| glcc.segment4|| '.'|| glcc.segment5|| '.'
         || glcc.segment6|| '.'|| glcc.segment7 = lrec_release.gl_code AND (end_date_active IS NULL OR end_date_active > SYSDATE);
          EXCEPTION
            WHEN TOO_MANY_ROWS THEN
               fnd_file.put_line
                           (fnd_file.LOG, ' Too many rows found against charge_account: ' || lrec_release.charge_account);
               lc_rec_status := 'E';
            WHEN OTHERS THEN
               fnd_file.put_line (fnd_file.LOG,' Error in charge_account: '|| lrec_release.charge_account || ', error: ' || SQLERRM);
               lc_rec_status := 'E';
            END;
         END IF;

         -- Validation for deliver_to_requestor_name
         IF (lrec_release.deliver_to_requestor_name IS NOT NULL) THEN
            BEGIN
                 SELECT full_name
                 INTO lc_deliver_to_requestor_name
                 FROM per_all_people_f
                WHERE UPPER (full_name) =
                                UPPER (lrec_release.deliver_to_requestor_name);
            EXCEPTION
               WHEN TOO_MANY_ROWS THEN
                  fnd_file.put_line (fnd_file.LOG, 'Get more than one record of deliver to requestor name:' || lrec_release.deliver_to_requestor_name);
                  lc_rec_status := 'E';
               WHEN NO_DATA_FOUND THEN
                  fnd_file.put_line (fnd_file.LOG,'Deliver to requestor name:'|| lrec_release.deliver_to_requestor_name|| ' not found.'|| SQLERRM);
                  lc_rec_status := 'E';
            END;
         ELSE
            fnd_file.put_line(fnd_file.LOG,' Deliver to requestor name is empty.');
            lc_rec_status := 'E';
         END IF;

         -- Validation for preparer_name
         IF (lrec_release.preparer_name IS NOT NULL) THEN
            BEGIN
             SELECT full_name
             INTO lc_preparer_name
             FROM per_all_people_f
             WHERE UPPER (full_name) = UPPER (lrec_release.preparer_name);
            EXCEPTION
               WHEN TOO_MANY_ROWS THEN
                  fnd_file.put_line (fnd_file.LOG, 'Get more than one record of preparer name:'|| lrec_release.preparer_name);
                  lc_rec_status := 'E';
               WHEN NO_DATA_FOUND THEN
                  fnd_file.put_line (fnd_file.LOG,' Preparer name:'|| lrec_release.preparer_name|| ' not found.'|| SQLERRM);
                  lc_rec_status := 'E';
            END;
         ELSE
            fnd_file.put_line (fnd_file.LOG, ' Preparer name is empty.');
            lc_rec_status := 'E';
            lc_error_msg := lc_error_msg || ' Preparer name is empty.';
         END IF;

         -- Get organization_id
         IF (lrec_release.destination_organization_code IS NOT NULL) THEN
            BEGIN
               SELECT organization_id
               INTO ln_organization_id
               FROM org_organization_definitions
               WHERE UPPER (organization_code) = UPPER (lrec_release.destination_organization_code);
            EXCEPTION
               WHEN TOO_MANY_ROWS THEN
                fnd_file.put_line(fnd_file.LOG,
                         'Get more than one record of distination organization_code:'|| lrec_release.destination_organization_code);
                  lc_rec_status := 'E';
               WHEN NO_DATA_FOUND THEN
                  fnd_file.put_line(fnd_file.LOG, ' Organization code:'|| lrec_release.destination_organization_code|| ' not found.'|| SQLERRM);
                  lc_rec_status := 'E';
            END;
         ELSE
            fnd_file.put_line (fnd_file.LOG, ' Organization code is empty.');
            lc_rec_status := 'E';
         END IF;

         -- Validation for item_number
         IF (lrec_release.item_segment1 IS NOT NULL) THEN
            BEGIN
               SELECT segment1, inventory_item_id
                 INTO lc_item_segment1, ln_inventory_item_id
                 FROM mtl_system_items_b
                  WHERE UPPER (segment1) = UPPER (lrec_release.item_segment1)
                  AND organization_id = ln_organization_id;
            EXCEPTION
               WHEN TOO_MANY_ROWS THEN
                  fnd_file.put_line (fnd_file.LOG, 'Get more than one record of item:'|| lrec_release.item_segment1);
                  lc_rec_status := 'E';
               WHEN NO_DATA_FOUND THEN
                  lc_rec_status := 'E';
            END;
         ELSE
            fnd_file.put_line (fnd_file.LOG, ' Item segment1 is empty.');
            lc_rec_status := 'E';
         END IF;

         -- Check Item is assign or not to ASL, SR and assignment
         -- This is important, without ASL, SR and assignment Blanket Release not created.
         BEGIN
            SELECT DISTINCT pasl.item_id
                       INTO ln_item_id
                       FROM po_approved_supplier_list pasl,
                            mrp_sr_assignments msa,
                            mrp_sr_receipt_org_v msr,
                            mrp_sr_source_org_v msrv,
                            po_asl_attributes_v paav
                      WHERE 1 = 1
                        AND pasl.item_id = msa.inventory_item_id
                        AND pasl.owning_organization_id = msa.organization_id
                        AND msa.sourcing_rule_id = msr.sourcing_rule_id
                        AND msr.sr_receipt_id = msrv.sr_receipt_id
                        AND msrv.vendor_id = pasl.vendor_id
                        AND msrv.vendor_site_id = pasl.vendor_site_id
                        AND paav.asl_id = pasl.asl_id
                        AND organization_id = ln_organization_id
                        AND inventory_item_id = ln_inventory_item_id
                        AND pasl.vendor_id = ln_vendor_id
                        AND pasl.vendor_site_id = ln_vendor_site_id
                        AND NVL (pasl.disable_flag, 'N') = 'N'
                        AND UPPER (paav.release_generation_method_dsp) = 'AUTOMATIC RELEASE'
                        AND (   msr.disable_date IS NULL
                             OR msr.disable_date > SYSDATE
                            );
         EXCEPTION
            WHEN TOO_MANY_ROWS THEN
               fnd_file.put_line(fnd_file.LOG,'Get more than one record of ASL for item:'|| lrec_release.item_number);
               lc_rec_status := 'E';
            WHEN NO_DATA_FOUND THEN
               fnd_file.put_line(fnd_file.LOG,' ASL/SR/Assignment is missing for Item:'|| lrec_release.item_number);
               lc_rec_status := 'E';
         END;

         -- Get deliver_to_location_code
         IF (lrec_release.deliver_to_location_code IS NOT NULL)
         THEN
            BEGIN
               SELECT location_id, location_code
                 INTO ln_deliver_to_location_id, lc_deliver_to_location
                 FROM hr_locations_all
                WHERE (inactive_date IS NULL OR inactive_date > SYSDATE)
                  AND location_code = lrec_release.deliver_to_location_code;
            EXCEPTION
               WHEN TOO_MANY_ROWS THEN
                  lc_rec_status := 'E';
               WHEN OTHERS THEN
                  lc_rec_status := 'E';
            END;
         END IF;

          -- Get deliver_to_person_id
         IF (lrec_release.deliver_to_requestor_name IS NOT NULL)
         THEN
            BEGIN
               SELECT person_id, full_name
                 INTO ln_deliver_to_person_id, lc_deliver_to_person
                 FROM per_all_people_f
                WHERE (   effective_end_date IS NULL OR effective_end_date > SYSDATE)
                  AND full_name = lrec_release.deliver_to_requestor_name;
            EXCEPTION
               WHEN too_many_rows THEN
                  lc_rec_status := 'E';
               WHEN OTHERS THEN
                  fnd_file.put_line (fnd_file.LOG, ' Error in deliver_to_requestor_name :' || SQLERRM );
                  lc_rec_status := 'E';
            END;
         END IF;

           -- Insert valid records in po_requisitions_interface_all table.
         IF (lc_rec_status = 'A')
         THEN
            BEGIN
               INSERT INTO po_requisitions_interface_all
                           (interface_source_code, 
                            source_type_code,
                            destination_type_code,
                            authorization_status,
                            preparer_name,
                            item_segment1,
                            quantity,
                            charge_account_id,
                            uom_code,
                            destination_organization_code,
                            deliver_to_location_code,
                            autosource_flag,
                            need_by_date,
                            deliver_to_requestor_name,
                            org_id,
                            autosource_doc_header_id,
                            autosource_doc_line_num,
                            created_by,
                            creation_date,
                            last_updated_by,
                            last_update_date, last_update_login,
                            line_attribute14, suggested_vendor_id,
                            suggested_vendor_site_id
                           )
                    VALUES ('XX_007',                -- interface_source_code
                            'VENDOR',                   -- source_type_code
                            'INVENTORY',                -- destination_type_code
                            'APPROVED',                 -- authorization_status
                            lrec_release.preparer_name, -- preparer_name
                            lrec_release.item_number, -- item_segment1
                            lrec_release.quantity,      -- quantity
                            ln_charge_account_id,       -- charge_account_id
                            lrec_release.supplier_uom,  -- uom_code
                            lrec_release.destination_organization_code, -- destination_organization_code
                            NVL (lc_deliver_to_location,lrec_release.deliver_to_location_code),  -- deliver_to_location_code
                            'Y',                        -- autosource_flag
                            lrec_release.need_by_date,  -- need_by_date
                            NVL (lc_deliver_to_person,
                                 lrec_release.preparer_name
                                ),                      -- deliver_to_requestor_name
                            lrec_release.org_id,        -- org_id
                            ln_po_header_id,            -- autosource_doc_header_id
                            ln_po_line_id,              -- autosource_doc_line_num
                            fnd_global.user_id,         -- created_by
                            SYSDATE,                    -- creation_date
                            fnd_global.user_id,         -- last_updated_by
                            SYSDATE,                    -- last_update_date
                            fnd_global.user_id,         -- last_update_login
                            lrec_release.req_number_segment1,-- line_attribute14
                            ln_vendor_id,               -- suggested_vendor_id
                            ln_vendor_site_id           -- suggested_vendor_site_id
                           );
            EXCEPTION
             WHEN OTHERS THEN
              fnd_file.put_line (fnd_file.LOG, 'Error in insert data in po_requisitions_interface_all table, Error:' || SQLERRM );
              lc_rec_status := 'E';
              lc_error_msg := lc_error_msg || ' Error in insert data in po_requisitions_interface_all table, Error:' || SQLERRM;
            END;
         END IF;

         -- Update process_flag in staging table
            UPDATE xx_bpo_release_stg
               SET process_flag = lc_rec_status
                WHERE 1 = 1
                      AND po_number = lrec_release.po_number
                      AND line_num = lrec_release.line_num
                      AND release_num = lrec_release.release_num
                      AND org_id = lrec_release.org_id
                      AND shipment_num = lrec_release.shipment_num
                      AND need_by_date = lrec_release.need_by_date;
         --
         IF (lc_rec_status = 'A')
         THEN
            ln_headers_cnt := ln_headers_cnt + 1;
         END IF;
      --
      END LOOP;   -- End of Loop..
      fnd_file.put_line (fnd_file.LOG, 'Total successfully inserted POs are:'|| ln_headers_cnt );
   --
   END xx_main_prc;
END xx_bpo_releases_pkg;
/ ------------------------------------------------------------------------------

3. Run standard program : 

Standard Program Name: Requisition Import (enter mandatory parameters).

4. Data inserted/updated in below base tables:

       1) PO_REQUISITION_HEADERS_ALL
       2) PO_REQUISITION_LINES_ALL
       3) PO_REQ_DISTRIBUTIONS_ALL
       4) PO_LINE_LOCATIONS_ALL (po_release_id available)
       5) PO_RELEASES_ALL  (po_header_id available)
       6) PO_DISTRIBUTIONS_ALL
       7) MTL_SUPPLY

*** Below are some SQL queries related to Blanket Releases:

      Check Sourcing Rule available or not for Item:

SELECT * FROM mrp_sourcing_rules WHERE sourcing_rule_name = 'A007123'; -- this is Item Number.

SELECT * FROM mrp_sr_receipt_org_v WHERE sourcing_rule_id = 5124082;

SELECT * FROM mrp_sr_source_org_v WHERE sr_receipt_id = 5861265;

-- Check Item Sourcing Rule allocation percentage - Vendor & Vendor Site wise:

SELECT msr.sourcing_rule_name

        , (SELECT vendor_name FROM ap_suppliers WHERE vendor_id = pha.vendor_id)

          ||'-'|| (SELECT vendor_site_code FROM ap_supplier_sites_all WHERE vendor_site_id = pha.vendor_site_id) po_vendor_name

        ,  source.vendor_name ||'-'|| source.vendor_site vendor_name

        ,  source.allocation_percent

        ,  source.source_type

        ,  msr.status

        ,  msr.sourcing_rule_type

        ,  msr.planning_active

        ,  receipt.organization_code

FROM mrp_sourcing_rules msr

       ,mrp_sr_receipt_org_v receipt

       ,mrp_sr_source_org_v source

       ,po_headers_all pha

       ,po_lines_all pla

       ,mtl_system_items_b msib

       ,org_organization_definitions ood

WHERE msr.sourcing_rule_id  =  receipt.sourcing_rule_id

   AND  receipt.sr_receipt_id  = source.sr_receipt_id

   AND  pla.po_header_id   = pha.po_header_id

   AND pla.item_id = msib.inventory_item_id

   AND msib.segment1 = msr.sourcing_rule_name

   AND msib.organization_id = receipt.receipt_organization_id

   AND msib.organization_id = ood.organization_id

   AND pha.segment1 = '200776'                    --  this is Blanket Number

   AND msr.sourcing_rule_name = 'A007123'  -- this is Item Number

   AND ood.organization_code = 'KUI'              -- this is Organization Code

   ORDER BY 1,2;

-- Query to check Blanket Releases created or not against Blanket:

SELECT pha.segment1 po_number

           , pra.release_num

FROM   po_releases_all pra

   , po_headers_all  pha

WHERE pra.po_header_id  = pha.po_header_id

     AND pha.segment1      = '200776';  

-- '200776' this is Blanket Number.