APPS.WSH_OPSM_ASN_BE_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 83

SELECT *
  FROM  wsh_opsm_asn_items_v
 WHERE  delivery_id=p_delivery_id
 AND delivery_detail_id = p_delivery_detail_id;

Line: 92

SELECT *
  FROM  wsh_opsm_asn_containers_v
 WHERE  delivery_id = p_delivery_id
 --AND delivery_detail_id = p_delivery_detail_id
   AND  DECODE(p_container_instance_id,NULL,1,container_instance_id) = DECODE(p_container_instance_id,NULL,1,p_container_instance_id)
 ORDER BY container_instance_id;

Line: 103

SELECT *
  FROM  wsh_opsm_asn_containers_v
 WHERE  delivery_id = p_delivery_id
 --AND delivery_detail_id = p_delivery_detail_id
   AND  DECODE(p_container_instance_id,NULL,1,container_instance_id) = DECODE(p_container_instance_id,NULL,1,p_container_instance_id);

Line: 111

SELECT *
FROM   wsh_opsm_asn_deliveries_v
WHERE  delivery_id              = p_delivery_id
AND delivery_detail_id = p_delivery_detail_id;

Line: 119

SELECT *
  FROM  wsh_opsm_asn_item_details_v
 WHERE  delivery_id        = p_delivery_id
   AND  delivery_detail_id = p_delivery_detail_id;

Line: 129

  SELECT *
  FROM wsh_opsm_asn_item_genealogy_v
  WHERE delivery_id = p_delivery_id
  AND delivery_detail_id = p_delivery_detail_id;

Line: 159

            SELECT SYSTIMESTAMP INTO l_start_time FROM dual;

Line: 189

        SELECT 1
          INTO l_count
          FROM	mtl_system_items_b_kfv msik,
                mtl_cross_references_vl mcr,
                mtl_parameters mp
          WHERE mp.organization_id             = msik.organization_id
            AND msik.inventory_item_id(+)      = mcr.inventory_item_id
            AND mcr.cross_reference_type       = 'OPSM INTEGRATED'
            AND mcr.cross_reference            = 'YES'
            AND mp.master_organization_id      = mcr.organization_id
            AND mp.opsm_enabled_FLAG           = 'Y'
            AND msik.lot_control_code          = 2
            AND msik.organization_id           = v_cur_item.organization_id
            AND NOT EXISTS                      (SELECT 'Y'
                                                   FROM MTL_CROSS_REFERENCES_VL
                                                  WHERE CROSS_REFERENCE_TYPE    = 'OPSM INTEGRATED'
                                                    AND ORGANIZATION_ID         = v_cur_item.organization_id
                                                    AND INVENTORY_ITEM_ID       = msik.inventory_item_id
                                                    AND UPPER(CROSS_REFERENCE)  ='NO')
            AND msik.inventory_item_id = v_cur_item.item_id;

Line: 288

            SELECT 1
              INTO l_count
              FROM	mtl_system_items_b_kfv msik,
                    mtl_cross_references_vl mcr,
                    mtl_parameters mp
              WHERE mp.organization_id             = msik.organization_id
                AND msik.inventory_item_id(+)      = mcr.inventory_item_id
                AND mcr.cross_reference_type       = 'OPSM INTEGRATED'
                AND mcr.cross_reference            = 'YES'
                AND mp.master_organization_id      = mcr.organization_id
                AND mp.opsm_enabled_FLAG           = 'Y'
                AND msik.lot_control_code          = 2
                AND msik.organization_id           = v_cur_item.organization_id
                AND NOT EXISTS                      (SELECT 'Y'
                                                       FROM MTL_CROSS_REFERENCES_VL
                                                      WHERE CROSS_REFERENCE_TYPE    = 'OPSM INTEGRATED'
                                                        AND ORGANIZATION_ID         = v_cur_item.organization_id
                                                        AND INVENTORY_ITEM_ID       = msik.inventory_item_id
                                                        AND UPPER(CROSS_REFERENCE)  ='NO')
                AND msik.inventory_item_id = v_cur_item.item_id;

Line: 523

                  SELECT 1
                    INTO l_count1
                    FROM	mtl_system_items_b_kfv msik,
                          mtl_cross_references_vl mcr,
                          mtl_parameters mp
                    WHERE mp.organization_id             = msik.organization_id
                      AND msik.inventory_item_id(+)      = mcr.inventory_item_id
                      AND mcr.cross_reference_type       = 'OPSM INTEGRATED'
                      AND mcr.cross_reference            = 'YES'
                      AND mp.master_organization_id      = mcr.organization_id
                      AND mp.opsm_enabled_FLAG           = 'Y'
                      AND msik.lot_control_code          = 2
                      AND msik.organization_id           = v_cur_itemgenealogy.organization_id
                      AND NOT EXISTS                      (SELECT 'Y'
                                                             FROM MTL_CROSS_REFERENCES_VL
                                                            WHERE CROSS_REFERENCE_TYPE    = 'OPSM INTEGRATED'
                                                              AND ORGANIZATION_ID         = v_cur_itemgenealogy.organization_id
                                                              AND INVENTORY_ITEM_ID       = msik.inventory_item_id
                                                              AND UPPER(CROSS_REFERENCE)  ='NO')
                      AND msik.inventory_item_id = v_cur_itemgenealogy.inventory_item_id;

Line: 622

                    SELECT opsm_integrated_flag ,
                    parent_inventory_item_id ,
                    parent_lot_number ,
                    parent_serial_number ,
                    parent_hierarchy_level ,
                    organization_id
                    INTO v_opsm_flag ,
                    v_inventory_item_id ,
                    v_lot_number ,
                    v_serial_number ,
                    v_hierarchy_level ,
                    v_organization_id
                    FROM wsh_opsm_asn_item_genealogy_v
                    WHERE delivery_id = v_cur_itemgenealogy.delivery_id
                    AND delivery_detail_id = v_cur_itemgenealogy.delivery_detail_id
                    AND inventory_item_id = v_parent_inventory_item_id
                    AND lot_number = v_parent_lot_number
                    AND serial_number = v_parent_serial_number
                    AND hierarchy_level = v_parent_hierarchy_level;

Line: 686

                          SELECT 1
                            INTO l_count2
                            FROM	mtl_system_items_b_kfv msik,
                                  mtl_cross_references_vl mcr,
                                  mtl_parameters mp
                            WHERE mp.organization_id             = msik.organization_id
                              AND msik.inventory_item_id(+)      = mcr.inventory_item_id
                              AND mcr.cross_reference_type       = 'OPSM INTEGRATED'
                              AND mcr.cross_reference            = 'YES'
                              AND mp.master_organization_id      = mcr.organization_id
                              AND mp.opsm_enabled_FLAG           = 'Y'
                              AND msik.lot_control_code          = 2
                              AND msik.organization_id           = v_organization_id
                              AND NOT EXISTS                      (SELECT 'Y'
                                                                     FROM MTL_CROSS_REFERENCES_VL
                                                                    WHERE CROSS_REFERENCE_TYPE    = 'OPSM INTEGRATED'
                                                                      AND ORGANIZATION_ID         = v_organization_id
                                                                      AND INVENTORY_ITEM_ID       = msik.inventory_item_id
                                                                      AND UPPER(CROSS_REFERENCE)  ='NO')
                              AND msik.inventory_item_id = v_parent_inventory_item_id;

Line: 737

                                cont_count.DELETE(TO_CHAR(v_cur_item.container_instance_id)
                                    ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
                                    ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
                                    ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
                                    ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
                                    ||'.'||TO_CHAR(v_cur_item.soldto_contact_id));

Line: 765

                                        cont_count.DELETE(TO_CHAR(v_cur_parent_container.parent_container_instance_id)
                                                    ||'.'||TO_CHAR(v_cur_parent_container.container_instance_id)
                                                    ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
                                                    ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
                                                    ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
                                                    ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
                                                    ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) ;

Line: 867

                           del_count.DELETE(TO_CHAR(v_cur_item.destination_cont_id)
                                            ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
                                            ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
                                            ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
                                            ||'.'||TO_CHAR(v_cur_item.soldto_contact_id));

Line: 932

                                                                        del_tab(l).last_updated_by               ,
                                                                        del_tab(l).last_update_date              ,
                                                                        del_tab(l).invoice_to_org_id             ,
                                                                        del_tab(l).invoice_to_contact_id         ,
                                                                        del_tab(l).invoice_name                  ,
                                                                        del_tab(l).invoice_address1              ,
                                                                        del_tab(l).invoice_address2              ,
                                                                        del_tab(l).invoice_address3              ,
                                                                        del_tab(l).invoice_address4              ,
                                                                        del_tab(l).invoice_city                  ,
                                                                        del_tab(l).invoice_postal_code           ,
                                                                        del_tab(l).invoice_country_int           ,
                                                                        del_tab(l).invoice_state_int             ,
                                                                        del_tab(l).invoice_province_int          ,
                                                                        del_tab(l).invoice_county                ,
                                                                        del_tab(l).invoice_cont_name             ,
                                                                        del_tab(l).invoice_cont_job_title        ,
                                                                        del_tab(l).invoice_cont_email_address    ,
                                                                        del_tab(l).invoice_cont_country_code     ,
                                                                        del_tab(l).invoice_cont_area_code        ,
                                                                        del_tab(l).invoice_cont_phone_number     ,
                                                                        del_tab(l).invoice_cont_phone_extn       ,
                                                                        del_tab(l).soldto_customer_id            ,
                                                                        del_tab(l).soldto_contact_id             ,
                                                                        del_tab(l).soldto_address_id             ,
                                                                        del_tab(l).soldto_name                   ,
                                                                        del_tab(l).soldto_address1               ,
                                                                        del_tab(l).soldto_address2               ,
                                                                        del_tab(l).soldto_address3               ,
                                                                        del_tab(l).soldto_address4               ,
                                                                        del_tab(l).soldto_city                   ,
                                                                        del_tab(l).soldto_postal_code            ,
                                                                        del_tab(l).soldto_country                ,
                                                                        del_tab(l).soldto_state                  ,
                                                                        del_tab(l).soldto_province               ,
                                                                        del_tab(l).soldto_county                 ,
                                                                        del_tab(l).soldto_cont_name              ,
                                                                        del_tab(l).soldto_cont_job_title         ,
                                                                        del_tab(l).soldto_cont_email             ,
                                                                        del_tab(l).soldto_cont_country_code      ,
                                                                        del_tab(l).soldto_cont_area_code         ,
                                                                        del_tab(l).soldto_cont_phone_number      ,
                                                                        del_tab(l).soldto_cont_phone_extn        ,
                                                                        del_tab(l).soldby_location_code          ,
                                                                        del_tab(l).soldby_country                ,
                                                                        del_tab(l).soldby_address_line_1         ,
                                                                        del_tab(l).soldby_address_line_2         ,
                                                                        del_tab(l).soldby_address_line_3         ,
                                                                        del_tab(l).soldby_address_line_4         ,
                                                                        del_tab(l).soldby_county                 ,
                                                                        del_tab(l).soldby_state                  ,
                                                                        del_tab(l).soldby_postal_code            ,
                                                                        del_tab(l).soldby_city                   ,
                                                                        del_tab(l).soldby_organization_id        ,
                                                                        del_tab(l).soldby_name                   ,
                                                                        del_tab(l).soldby_contact_name           ,
                                                                        del_tab(l).soldby_contact_emailid        ,
                                                                        del_tab(l).soldby_contact_phonenumber);

Line: 1042

                SELECT SYSTIMESTAMP INTO l_end_time FROM dual;

Line: 1050

                 SELECT (l_end_time-l_start_time) INTO l_time_diff FROM dual;

Line: 1156

SELECT *
  FROM  wsh_opsm_asn_items_v
 WHERE  delivery_id=p_delivery_id;

Line: 1163

SELECT *
  FROM  wsh_opsm_asn_containers_v
 WHERE  delivery_id = p_delivery_id
   AND  DECODE(p_container_instance_id,NULL,1,container_instance_id) = DECODE(p_container_instance_id,NULL,1,p_container_instance_id)
 ORDER BY container_instance_id;

Line: 1172

SELECT *
  FROM  wsh_opsm_asn_containers_v
 WHERE  delivery_id = p_delivery_id
   AND  DECODE(p_container_instance_id,NULL,1,container_instance_id) = DECODE(p_container_instance_id,NULL,1,p_container_instance_id);

Line: 1179

SELECT *
FROM   wsh_opsm_asn_deliveries_v
WHERE  delivery_id              = p_delivery_id ;

Line: 1186

SELECT *
  FROM  wsh_opsm_asn_item_details_v
 WHERE  delivery_id        = p_delivery_id
   AND  delivery_detail_id = p_delivery_detail_id;

Line: 1196

  SELECT *
  FROM wsh_opsm_asn_item_genealogy_v
  WHERE delivery_id = p_delivery_id
  AND delivery_detail_id = p_delivery_detail_id;

Line: 1226

            SELECT SYSTIMESTAMP INTO l_start_time FROM dual;

Line: 1256

        SELECT 1
          INTO l_count
          FROM	mtl_system_items_b_kfv msik,
                mtl_cross_references_vl mcr,
                mtl_parameters mp
          WHERE mp.organization_id             = msik.organization_id
            AND msik.inventory_item_id(+)      = mcr.inventory_item_id
            AND mcr.cross_reference_type       = 'OPSM INTEGRATED'
            AND mcr.cross_reference            = 'YES'
            AND mp.master_organization_id      = mcr.organization_id
            AND mp.opsm_enabled_FLAG           = 'Y'
            AND msik.lot_control_code          = 2
            AND msik.organization_id           = v_cur_item.organization_id
            AND NOT EXISTS                      (SELECT 'Y'
                                                   FROM MTL_CROSS_REFERENCES_VL
                                                  WHERE CROSS_REFERENCE_TYPE    = 'OPSM INTEGRATED'
                                                    AND ORGANIZATION_ID         = v_cur_item.organization_id
                                                    AND INVENTORY_ITEM_ID       = msik.inventory_item_id
                                                    AND UPPER(CROSS_REFERENCE)  ='NO')
            AND msik.inventory_item_id = v_cur_item.item_id;

Line: 1353

            SELECT 1
              INTO l_count
              FROM	mtl_system_items_b_kfv msik,
                    mtl_cross_references_vl mcr,
                    mtl_parameters mp
              WHERE mp.organization_id             = msik.organization_id
                AND msik.inventory_item_id(+)      = mcr.inventory_item_id
                AND mcr.cross_reference_type       = 'OPSM INTEGRATED'
                AND mcr.cross_reference            = 'YES'
                AND mp.master_organization_id      = mcr.organization_id
                AND mp.opsm_enabled_FLAG           = 'Y'
                AND msik.lot_control_code          = 2
                AND msik.organization_id           = v_cur_item.organization_id
                AND NOT EXISTS                      (SELECT 'Y'
                                                       FROM MTL_CROSS_REFERENCES_VL
                                                      WHERE CROSS_REFERENCE_TYPE    = 'OPSM INTEGRATED'
                                                        AND ORGANIZATION_ID         = v_cur_item.organization_id
                                                        AND INVENTORY_ITEM_ID       = msik.inventory_item_id
                                                        AND UPPER(CROSS_REFERENCE)  ='NO')
                AND msik.inventory_item_id = v_cur_item.item_id;

Line: 1588

                  SELECT 1
                    INTO l_count1
                    FROM	mtl_system_items_b_kfv msik,
                          mtl_cross_references_vl mcr,
                          mtl_parameters mp
                    WHERE mp.organization_id             = msik.organization_id
                      AND msik.inventory_item_id(+)      = mcr.inventory_item_id
                      AND mcr.cross_reference_type       = 'OPSM INTEGRATED'
                      AND mcr.cross_reference            = 'YES'
                      AND mp.master_organization_id      = mcr.organization_id
                      AND mp.opsm_enabled_FLAG           = 'Y'
                      AND msik.lot_control_code          = 2
                      AND msik.organization_id           = v_cur_itemgenealogy.organization_id
                      AND NOT EXISTS                      (SELECT 'Y'
                                                             FROM MTL_CROSS_REFERENCES_VL
                                                            WHERE CROSS_REFERENCE_TYPE    = 'OPSM INTEGRATED'
                                                              AND ORGANIZATION_ID         = v_cur_itemgenealogy.organization_id
                                                              AND INVENTORY_ITEM_ID       = msik.inventory_item_id
                                                              AND UPPER(CROSS_REFERENCE)  ='NO')
                      AND msik.inventory_item_id = v_cur_itemgenealogy.inventory_item_id;

Line: 1687

                    SELECT opsm_integrated_flag ,
                    parent_inventory_item_id ,
                    parent_lot_number ,
                    parent_serial_number ,
                    parent_hierarchy_level ,
                    organization_id
                    INTO v_opsm_flag ,
                    v_inventory_item_id ,
                    v_lot_number ,
                    v_serial_number ,
                    v_hierarchy_level ,
                    v_organization_id
                    FROM wsh_opsm_asn_item_genealogy_v
                    WHERE delivery_id = v_cur_itemgenealogy.delivery_id
                    AND delivery_detail_id = v_cur_itemgenealogy.delivery_detail_id
                    AND inventory_item_id = v_parent_inventory_item_id
                    AND lot_number = v_parent_lot_number
                    AND serial_number = v_parent_serial_number
                    AND hierarchy_level = v_parent_hierarchy_level;

Line: 1751

                          SELECT 1
                            INTO l_count2
                            FROM	mtl_system_items_b_kfv msik,
                                  mtl_cross_references_vl mcr,
                                  mtl_parameters mp
                            WHERE mp.organization_id             = msik.organization_id
                              AND msik.inventory_item_id(+)      = mcr.inventory_item_id
                              AND mcr.cross_reference_type       = 'OPSM INTEGRATED'
                              AND mcr.cross_reference            = 'YES'
                              AND mp.master_organization_id      = mcr.organization_id
                              AND mp.opsm_enabled_FLAG           = 'Y'
                              AND msik.lot_control_code          = 2
                              AND msik.organization_id           = v_organization_id
                              AND NOT EXISTS                      (SELECT 'Y'
                                                                     FROM MTL_CROSS_REFERENCES_VL
                                                                    WHERE CROSS_REFERENCE_TYPE    = 'OPSM INTEGRATED'
                                                                      AND ORGANIZATION_ID         = v_organization_id
                                                                      AND INVENTORY_ITEM_ID       = msik.inventory_item_id
                                                                      AND UPPER(CROSS_REFERENCE)  ='NO')
                              AND msik.inventory_item_id = v_parent_inventory_item_id;

Line: 1802

                                cont_count.DELETE(TO_CHAR(v_cur_item.container_instance_id)
                                    ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
                                    ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
                                    ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
                                    ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
                                    ||'.'||TO_CHAR(v_cur_item.soldto_contact_id));

Line: 1828

                                        cont_count.DELETE(TO_CHAR(v_cur_parent_container.parent_container_instance_id)
                                                    ||'.'||TO_CHAR(v_cur_parent_container.container_instance_id)
                                                    ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
                                                    ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
                                                    ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
                                                    ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
                                                    ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) ;

Line: 1930

                           del_count.DELETE(TO_CHAR(v_cur_item.destination_cont_id)
                                            ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
                                            ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
                                            ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
                                            ||'.'||TO_CHAR(v_cur_item.soldto_contact_id));

Line: 1995

                                                                        del_tab(l).last_updated_by               ,
                                                                        del_tab(l).last_update_date              ,
                                                                        del_tab(l).invoice_to_org_id             ,
                                                                        del_tab(l).invoice_to_contact_id         ,
                                                                        del_tab(l).invoice_name                  ,
                                                                        del_tab(l).invoice_address1              ,
                                                                        del_tab(l).invoice_address2              ,
                                                                        del_tab(l).invoice_address3              ,
                                                                        del_tab(l).invoice_address4              ,
                                                                        del_tab(l).invoice_city                  ,
                                                                        del_tab(l).invoice_postal_code           ,
                                                                        del_tab(l).invoice_country_int           ,
                                                                        del_tab(l).invoice_state_int             ,
                                                                        del_tab(l).invoice_province_int          ,
                                                                        del_tab(l).invoice_county                ,
                                                                        del_tab(l).invoice_cont_name             ,
                                                                        del_tab(l).invoice_cont_job_title        ,
                                                                        del_tab(l).invoice_cont_email_address    ,
                                                                        del_tab(l).invoice_cont_country_code     ,
                                                                        del_tab(l).invoice_cont_area_code        ,
                                                                        del_tab(l).invoice_cont_phone_number     ,
                                                                        del_tab(l).invoice_cont_phone_extn       ,
                                                                        del_tab(l).soldto_customer_id            ,
                                                                        del_tab(l).soldto_contact_id             ,
                                                                        del_tab(l).soldto_address_id             ,
                                                                        del_tab(l).soldto_name                   ,
                                                                        del_tab(l).soldto_address1               ,
                                                                        del_tab(l).soldto_address2               ,
                                                                        del_tab(l).soldto_address3               ,
                                                                        del_tab(l).soldto_address4               ,
                                                                        del_tab(l).soldto_city                   ,
                                                                        del_tab(l).soldto_postal_code            ,
                                                                        del_tab(l).soldto_country                ,
                                                                        del_tab(l).soldto_state                  ,
                                                                        del_tab(l).soldto_province               ,
                                                                        del_tab(l).soldto_county                 ,
                                                                        del_tab(l).soldto_cont_name              ,
                                                                        del_tab(l).soldto_cont_job_title         ,
                                                                        del_tab(l).soldto_cont_email             ,
                                                                        del_tab(l).soldto_cont_country_code      ,
                                                                        del_tab(l).soldto_cont_area_code         ,
                                                                        del_tab(l).soldto_cont_phone_number      ,
                                                                        del_tab(l).soldto_cont_phone_extn        ,
                                                                        del_tab(l).soldby_location_code          ,
                                                                        del_tab(l).soldby_country                ,
                                                                        del_tab(l).soldby_address_line_1         ,
                                                                        del_tab(l).soldby_address_line_2         ,
                                                                        del_tab(l).soldby_address_line_3         ,
                                                                        del_tab(l).soldby_address_line_4         ,
                                                                        del_tab(l).soldby_county                 ,
                                                                        del_tab(l).soldby_state                  ,
                                                                        del_tab(l).soldby_postal_code            ,
                                                                        del_tab(l).soldby_city                   ,
                                                                        del_tab(l).soldby_organization_id        ,
                                                                        del_tab(l).soldby_name                   ,
                                                                        del_tab(l).soldby_contact_name           ,
                                                                        del_tab(l).soldby_contact_emailid        ,
                                                                        del_tab(l).soldby_contact_phonenumber);

Line: 2105

                SELECT SYSTIMESTAMP INTO l_end_time FROM dual;

Line: 2113

                 SELECT (l_end_time-l_start_time) INTO l_time_diff FROM dual;

Line: 2166

  SELECT item_id ,organization_id
   FROM wsh_opsm_asn_items_v
  WHERE delivery_id=(l_delivery_id);

Line: 2184

    SELECT 1
      INTO l_count
      FROM	mtl_system_items_b_kfv msik,
            mtl_cross_references_vl mcr,
            mtl_parameters mp
      WHERE mp.organization_id             = msik.organization_id
        AND msik.inventory_item_id(+)      = mcr.inventory_item_id
        AND mcr.cross_reference_type       = 'OPSM INTEGRATED'
        AND mcr.cross_reference            = 'YES'
        AND mp.master_organization_id      = mcr.organization_id
        AND mp.opsm_enabled_FLAG           = 'Y'
        AND msik.lot_control_code          = 2
        AND msik.organization_id           = v_opsm_items.organization_id
        AND NOT EXISTS                    (SELECT 'Y'
                                             FROM MTL_CROSS_REFERENCES_VL
                                            WHERE CROSS_REFERENCE_TYPE    = 'OPSM INTEGRATED'
                                              AND ORGANIZATION_ID         = v_opsm_items.organization_id
                                              AND INVENTORY_ITEM_ID       = msik.inventory_item_id
                                              AND UPPER(CROSS_REFERENCE)  ='NO')
        AND msik.inventory_item_id = v_opsm_items.item_id;