Error:

Solution :

Increase the Maximum Size in Format Validation section in Value Set to size of actual table column size

Default subinventory is not defined in the destination organization(Subinventory Transfer)

ERROR

Default subinventory is not defined in the destination organization

Solution

mtl_transactions_interface  -        Add  to_subinventory    column

Negative balances not allowed(Subinventory Material Transactions)

ERROR

Negative balances not allowed


Solution

Increase the on hand stock

Navigation

India Local Inventory - query the item -  check item quantity available to move

BOM Extract Query(Bill Of materials)

/* Formatted on 2020/02/25 16:49 (Formatter Plus v4.8.8) */
SELECT ood.organization_code, ood.organization_name, msi.segment1 bom_code,
       msi.description bom_desc, bic.item_num, bic.operation_seq_num,
       msi1.segment1 component_code, msi1.description component_desc,
       ml.meaning supply_type, bic.component_quantity, bic.effectivity_date,
       bic.disable_date, msi1.inventory_item_status_code,
       bic.supply_subinventory, bic.wip_supply_type
  FROM mtl_system_items_b msi,
       org_organization_definitions ood,
       bom_bill_of_materials bom,
       bom_inventory_components bic,
       mtl_system_items_b msi1,
       mfg_lookups ml
 WHERE bom.assembly_item_id = msi.inventory_item_id
   AND bom.bill_sequence_id = bic.bill_sequence_id
   AND msi.organization_id = ood.organization_id
   AND bom.organization_id = ood.organization_id
   -- AND msi1.inventory_item_status_code = 'Active'
   AND bic.component_item_id = msi1.inventory_item_id
   AND msi1.organization_id = ood.organization_id
   AND ml.lookup_code(+) = bic.wip_supply_type
   AND ml.lookup_type(+) = 'WIP_SUPPLY'

Customer Address update(Postal Code)

CREATE TABLE xx_CUST_LOC_PIN_UPDATE_STG
(
  OPERATING_UNIT   VARCHAR2(500 BYTE),
  CUSTOMER_NAME    VARCHAR2(50 BYTE),
  CUSTOMER_NUMBER  VARCHAR2(30 BYTE),
  CUSTOMER_GROUP   VARCHAR2(500 BYTE),
  CUSTOMER_SITE    VARCHAR2(500 BYTE),
  PIN_CODE         VARCHAR2(60 BYTE),
  STATUS_FLAG      VARCHAR2(10 BYTE),
  STATUS_MESSAGE   VARCHAR2(2000 BYTE),
  PROCESS_FLAG     VARCHAR2(10 BYTE)
)

       CREATE OR REPLACE PROCEDURE APPS.xx_cust_loc_pin_update_prc
IS
---DECLARE
   p_location_rec            hz_location_v2pub.location_rec_type;
   p_object_version_number   NUMBER                              := 1;
   x_return_status           VARCHAR2 (2000);
   x_msg_count               NUMBER;
   x_msg_data                VARCHAR2 (2000);
   l_error_message           VARCHAR2 (2000);
   l_location_id             NUMBER;
   l_status_flag             VARCHAR2 (10);
   l_object_version_number   NUMBER                              := 1;

   CURSOR c1
   IS
      SELECT stg.ROWID, stg.*
        FROM xx_cust_loc_pin_update_stg stg ;-- where CUSTOMER_GROUP='ATITH FIBER PVT. LTD.';
BEGIN
   fnd_global.apps_initialize (user_id           => 1130,
                               resp_id           => 50855,
                               resp_appl_id      => 222
                              );
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);
-- Initializing the Mandatory API parameters
   FOR i IN c1
   LOOP
      IF c1%NOTFOUND
      THEN
         COMMIT;
         CONTINUE;
      END IF;

      l_error_message := NULL;
      l_status_flag := NULL;
      DBMS_OUTPUT.put_line ('Looop started..');

      BEGIN
         SELECT DISTINCT hps.location_id
                    INTO l_location_id
                    FROM apps.hz_parties hp,
                         apps.hz_cust_accounts hca,
                         apps.hz_party_sites hps,
                         apps.hz_locations hl,
                         apps.hz_cust_acct_sites_all hcasa1,
                         hr_operating_units ood,
                         hz_cust_site_uses_all hsu
                   WHERE 1 = 1
                     AND hp.party_id = hca.party_id
                     AND hp.party_id = hps.party_id
                     AND nvl(party_site_name,hp.province)=i.customer_site                                                           
                     AND hps.location_id = hl.location_id
                     AND hcasa1.party_site_id = hps.party_site_id
                     AND ood.NAME = i.operating_unit
                     AND ood.organization_id = hcasa1.org_id
                     AND account_number = i.customer_number
                     AND hsu.cust_acct_site_id = hcasa1.cust_acct_site_id;

         DBMS_OUTPUT.put_line ('location ' || l_location_id);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_status_flag := 'E';

            UPDATE xx_cust_loc_pin_update_stg
               SET status_flag = 'E',
                   status_message = 'Customer Site not found',
                   process_flag = 'I'
             WHERE ROWID = i.ROWID;

            DBMS_OUTPUT.put_line ('Customer Site not found '
                                  || i.customer_site
                                 );
            COMMIT;
            CONTINUE;
         WHEN TOO_MANY_ROWS
         THEN
            l_status_flag := 'E';

            UPDATE xx_cust_loc_pin_update_stg
               SET status_flag = 'E',
                   status_message = 'Customer Site not found-too many rows',
                   process_flag = 'I'
             WHERE ROWID = i.ROWID;

            DBMS_OUTPUT.put_line (   'Customer Site not found too many'
                                  || i.customer_site
                                 );
            COMMIT;
            CONTINUE;
      END;

      IF l_location_id IS NOT NULL
      THEN
         SELECT object_version_number
           INTO l_object_version_number
           FROM hz_locations
          WHERE location_id = l_location_id;
      END IF;

      DBMS_OUTPUT.put_line ('before process ' || l_location_id);
      DBMS_OUTPUT.put_line ('l_status_flag ' || l_status_flag);

      IF l_status_flag IS NULL
      THEN
         DBMS_OUTPUT.put_line ('Validated ' || l_location_id);
         p_location_rec.location_id := l_location_id;   
         p_location_rec.postal_code := i.pin_code;
         p_object_version_number := l_object_version_number;
         DBMS_OUTPUT.put_line
                         ('Calling the API hz_location_v2pub.update_location');
         hz_location_v2pub.update_location
                         (p_init_msg_list              => fnd_api.g_true,
                          p_location_rec               => p_location_rec,
                          p_object_version_number      => p_object_version_number,
                          x_return_status              => x_return_status,
                          x_msg_count                  => x_msg_count,
                          x_msg_data                   => x_msg_data
                         );
         COMMIT;
         DBMS_OUTPUT.put_line ('x_return_status' || x_return_status);

         IF x_return_status = fnd_api.g_ret_sts_success
         THEN
            -- COMMIT;
            DBMS_OUTPUT.put_line ('Creation of Location is Successful ');
            DBMS_OUTPUT.put_line ('Output information ....');
            DBMS_OUTPUT.put_line ('New Address1: ' || p_location_rec.address1);
            DBMS_OUTPUT.put_line (   'p_object_version_number = '
                                  || p_object_version_number
                                 );

            UPDATE xx_cust_loc_pin_update_stg
               SET status_flag = 'S',
                   status_message = 'Sucess',
                   process_flag = 'C'
             WHERE ROWID = i.ROWID;

            COMMIT;
            CONTINUE;
            DBMS_OUTPUT.put_line ('sucess  ' || x_return_status);
         ELSE
            DBMS_OUTPUT.put_line ('Creation of Location failed:' || x_msg_data
                                 );
            ROLLBACK;

            FOR i IN 1 .. x_msg_count
            LOOP
               x_msg_data :=
                          oe_msg_pub.get (p_msg_index      => i,
                                          p_encoded        => 'F');
               DBMS_OUTPUT.put_line (i || ') ' || x_msg_data);
            END LOOP;

            l_error_message := SQLCODE || SQLERRM || x_msg_data;
            DBMS_OUTPUT.put_line ('error-2 ' || l_error_message);

            UPDATE xx_cust_loc_pin_update_stg
               SET status_flag = 'E',
                   status_message = l_error_message,
                   process_flag = 'I';

            DBMS_OUTPUT.put_line ('error-2 ' || l_error_message);
            COMMIT;
            CONTINUE;
         END IF;

         DBMS_OUTPUT.put_line ('Completion of API');
      END IF;
   END LOOP;
END;
/

0ORA-0000: normal, successful completion -API error

Error : 

error-2 0ORA-0000: normal, successful completion

Scnario:

when updating customer sites using  hz_location_v2pub.update_location

Solution

 SELECT object_version_number
           INTO l_object_version_number
           FROM hz_locations
          WHERE location_id = l_location_id;

PLSQL Error

Error:

ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112

Solution

 DBMS_OUTPUT.ENABLE(1000000);

Starting with Oracle release 10g, it is possible to use the following unlimited buffer settings:


DBMS_OUTPUT.ENABLE (buffer_size => NULL);