TCA Api to Update the Party Information (HZ_PARTY_V2PUB.UPDATE_ORGANIZATION)

DECLARE
   l_init_msg_list                                   VARCHAR2(200);
   l_organization_rec                                apps.hz_party_v2pub.organization_rec_type;
   l_party_rec                                       apps.hz_party_v2pub.party_rec_type;
   l_party_object_version_number                     NUMBER;
   x_profile_id                                      NUMBER;
   l_error_message                                   VARCHAR2(2000);
   l_msg_index_out                                   NUMBER;
   x_return_status                                   VARCHAR2(200);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2(200);
BEGIN
   l_init_msg_list                                := 1.0;
   l_party_rec.party_id                           := 4302;
   l_party_rec.attribute4                         := 'Valid';
   l_organization_rec.party_rec                   := l_party_rec;
   x_profile_id                                   := NULL;
   x_return_status                                := NULL;
   x_msg_count                                    := NULL;
   x_msg_data                                     := NULL;
   SELECT object_version_number
     INTO l_party_object_version_number
     FROM hz_parties
    WHERE party_id = l_party_rec.party_id
      AND status = 'A';
   apps.hz_party_v2pub.update_organization(p_init_msg_list               => apps.fnd_api.g_true
                                         , p_organization_rec            => l_organization_rec
                                         , p_party_object_version_number => l_party_object_version_number
                                         , x_profile_id                  => x_profile_id
                                         , x_return_status               => x_return_status
                                         , x_msg_count                   => x_msg_count
                                         , x_msg_data                    => x_msg_data
                                          );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get(p_msg_index                   => i, p_encoded => fnd_api.g_false, p_data => x_msg_data
                            , p_msg_index_out               => l_msg_index_out);

         IF l_error_message IS NULL
         THEN
            l_error_message                                := SUBSTR(x_msg_data, 1, 250);
         ELSE
            l_error_message                                := l_error_message || ' /' || SUBSTR(x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line('*****************************************');
      DBMS_OUTPUT.put_line('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line('*****************************************');
      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line('*****************************************');
      DBMS_OUTPUT.put_line('Attribute4 for Party : ' || l_party_rec.party_id || ' Updated Successfully ');
      DBMS_OUTPUT.put_line('*****************************************');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line('Unexpected Error ' || SUBSTR(SQLERRM, 1, 250));
END;

PLSQL Script to load the data from Flat file into Oracle Table dynamically

1. create below log table to capture the error records details .

CREATE TABLE XX_BAD_DATA_LOG_TBL
(
  FILE_NAME                      VARCHAR2(250 BYTE),
  CREATION_DATE            DATE,
  CREATED_BY                   NUMBER,
  ERROR_MESSAGE           VARCHAR2(2000 BYTE),
  FILE_DATA                        VARCHAR2(2000 BYTE)
);

2. Create the following function.

CREATE OR REPLACE FUNCTION apps.xx_upload_data_to_tbl (
   p_table                              IN   VARCHAR2
 , p_dir                                IN   VARCHAR2
 , p_filename                           IN   VARCHAR2
 , p_delimiter                          IN   VARCHAR2 DEFAULT ','
)
   RETURN NUMBER
IS
   l_utl_file                                   UTL_FILE.file_type;
   l_cursor                                     INTEGER DEFAULT DBMS_SQL.open_cursor;
   l_buffer                                     VARCHAR2 (4000);
   l_data                                   VARCHAR2 (4000);
   l_status                                     INTEGER;
   l_column_cnt                                 NUMBER DEFAULT 0;
   l_count                                      NUMBER DEFAULT 0;
   l_sep                                        CHAR (1) DEFAULT NULL;
   l_error_message                              VARCHAR2 (4000);
   l_column_names                               VARCHAR2 (4000);
   l_num                                        NUMBER;
BEGIN
   l_num                                := 10;
   l_utl_file                           := UTL_FILE.fopen (p_dir
                                                         , p_filename
                                                         , 'r'
                                                          );
   l_num                                := 20;
   l_buffer                             := 'INSERT INTO ' || p_table || ' VALUES ( ';
   l_num                                := 30;

   FOR z IN (SELECT column_name
               FROM dba_tab_columns
              WHERE table_name = p_table)
   LOOP
      IF l_column_names IS NULL
      THEN
         l_column_names                       := z.column_name;
      ELSE
         l_column_names                       := l_column_names || ',' || z.column_name;
      END IF;
   END LOOP;

   l_num                                := 40;
   DBMS_OUTPUT.put_line (l_num || ' ' || l_column_names);

   BEGIN
      l_column_cnt                         := LENGTH (l_column_names) - LENGTH (REPLACE (l_column_names
                                                                                       , ','
                                                                                       , ''
                                                                                        )) + 1;
      l_num                                := 45;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (l_num || ' Error ' || SQLERRM);
   END;

   DBMS_OUTPUT.put_line (l_column_cnt);
   l_num                                := 50;

   FOR i IN 1 .. l_column_cnt
   LOOP
      l_buffer                             := l_buffer || l_sep || ':b' || i;
      l_sep                                := ',';
   END LOOP;

   l_num                                := 60;
   l_buffer                             := l_buffer || ')';
   l_num                                := 70;
   DBMS_SQL.parse (l_cursor
                 , l_buffer
                 , DBMS_SQL.native
                  );
   l_num                                := 80;

   LOOP
      BEGIN
         UTL_FILE.get_line (l_utl_file, l_data);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;

      l_num                                := 90;
      l_buffer                             := l_data || p_delimiter;
      l_num                                := 100;

      FOR i IN 1 .. l_column_cnt
      LOOP
         DBMS_SQL.bind_variable (l_cursor
                               , ':b' || i
                               , SUBSTR (l_buffer
                                       , 1
                                       , INSTR (l_buffer, p_delimiter) - 1
                                        )
                                );
         l_buffer                             := SUBSTR (l_buffer, INSTR (l_buffer, p_delimiter) + 1);
         l_num                                := 110;
      END LOOP;

      BEGIN
         l_status                             := DBMS_SQL.EXECUTE (l_cursor);
         l_count                              := l_count + 1;
         l_num                                := 120;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error_message                      := SQLERRM;

            INSERT INTO xx_bad_data_log_tbl
                 VALUES (p_filename
                       , SYSDATE
                       , fnd_global.user_id
                       , l_error_message
                       , l_data
                        );
      END;
   END LOOP;

   DBMS_SQL.close_cursor (l_cursor);
   UTL_FILE.fclose (l_utl_file);
   l_num                                := 130;
   COMMIT;
   DBMS_OUTPUT.put_line (l_num || ' ' || l_count);
   RETURN l_count;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (l_num || ' Unexpected Error ' || SUBSTR(SQLERRM,1,250));
      RETURN 0;
END xx_upload_data_to_tbl;
/

API Script to Update Extensible Attributes for a instance in Install Base

DECLARE
   x_instance_rec                                    csi_datastructures_pub.instance_rec;
   p_ext_attrib_values                               csi_datastructures_pub.extend_attrib_values_tbl;
   p_party_tbl                                       csi_datastructures_pub.party_tbl;
   p_account_tbl                                     csi_datastructures_pub.party_account_tbl;
   p_pricing_attrib_tbl                              csi_datastructures_pub.pricing_attribs_tbl;
   p_org_assignments_tbl                             csi_datastructures_pub.organization_units_tbl;
   p_asset_assignment_tbl                            csi_datastructures_pub.instance_asset_tbl;
   p_txn_rec                                         csi_datastructures_pub.transaction_rec;
   x_instance_id_lst                                 csi_datastructures_pub.id_tbl;
   x_return_status                                   VARCHAR2(2000);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2(2000);
   x_msg_index_out                                   NUMBER;
   t_output                                          VARCHAR2(2000);
   t_msg_dummy                                       NUMBER;
   p_validation_level                                NUMBER;
   p_commit                                          VARCHAR2(5);
   p_init_msg_lst                                    VARCHAR2(500);
BEGIN
   x_instance_rec.instance_id                     := 4004190;
   x_instance_rec.object_version_number           := 7;
   p_txn_rec.transaction_id                       := fnd_api.g_miss_num;
   p_txn_rec.transaction_date                     := SYSDATE;
   p_txn_rec.source_transaction_date              := SYSDATE;
   p_txn_rec.transaction_type_id                  := 1;
   p_ext_attrib_values(1).attribute_value         := 'TEST_1';
   p_ext_attrib_values(1).attribute_value_id      := 1747998;
   p_ext_attrib_values(1).instance_id             := 4004190;
   p_ext_attrib_values(1).object_version_number   := 2;
   csi_item_instance_pub.update_item_instance(p_api_version                 => 1.0
                                            , p_commit                      => p_commit
                                            , p_init_msg_list               => p_init_msg_lst
                                            , p_validation_level            => 1
                                            , p_instance_rec                => x_instance_rec
                                            , p_ext_attrib_values_tbl       => p_ext_attrib_values
                                            , p_party_tbl                   => p_party_tbl
                                            , p_account_tbl                 => p_account_tbl
                                            , p_pricing_attrib_tbl          => p_pricing_attrib_tbl
                                            , p_org_assignments_tbl         => p_org_assignments_tbl
                                            , p_asset_assignment_tbl        => p_asset_assignment_tbl
                                            , p_txn_rec                     => p_txn_rec
                                            , x_instance_id_lst             => x_instance_id_lst
                                            , x_return_status               => x_return_status
                                            , x_msg_count                   => x_msg_count
                                            , x_msg_data                    => x_msg_data
                                             );
   COMMIT;

-- Output the results
   IF x_msg_count > 0
   THEN
      FOR j IN 1 .. x_msg_count
      LOOP
         fnd_msg_pub.get(j
                       , fnd_api.g_false
                       , x_msg_data
                       , t_msg_dummy
                        );
         t_output                                       :=('Msg' || TO_CHAR(j) || ': ' || x_msg_data);
         DBMS_OUTPUT.put_line(SUBSTR(t_output
                                   , 1
                                   , 255
                                    ));
      END LOOP;
   END IF;

   DBMS_OUTPUT.put_line('x_return_status = ' || x_return_status);
   DBMS_OUTPUT.put_line('x_msg_count = ' || TO_CHAR(x_msg_count));
   DBMS_OUTPUT.put_line('x_msg_data = ' || x_msg_data);
   COMMIT;
END;

API Script to Create Extensible Attributes for a instance in Install Base

DECLARE
   x_instance_rec                                    csi_datastructures_pub.instance_rec;
   p_ext_attrib_values                               csi_datastructures_pub.extend_attrib_values_tbl;
   p_party_tbl                                       csi_datastructures_pub.party_tbl;
   p_account_tbl                                     csi_datastructures_pub.party_account_tbl;
   p_pricing_attrib_tbl                              csi_datastructures_pub.pricing_attribs_tbl;
   p_org_assignments_tbl                             csi_datastructures_pub.organization_units_tbl;
   p_asset_assignment_tbl                            csi_datastructures_pub.instance_asset_tbl;
   p_txn_rec                                         csi_datastructures_pub.transaction_rec;
   x_instance_id_lst                                 csi_datastructures_pub.id_tbl;
   x_return_status                                   VARCHAR2(2000);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2(2000);
   x_msg_index_out                                   NUMBER;
   t_output                                          VARCHAR2(2000);
   t_msg_dummy                                       NUMBER;
   p_validation_level                                NUMBER;
   p_commit                                          VARCHAR2(5);
   p_init_msg_lst                                    VARCHAR2(500);
BEGIN
   x_instance_rec.instance_id                     := 4004190;
   x_instance_rec.object_version_number           := 7;
   p_txn_rec.transaction_id                       := fnd_api.g_miss_num;
   p_txn_rec.transaction_date                     := SYSDATE;
   p_txn_rec.source_transaction_date              := SYSDATE;
   p_txn_rec.transaction_type_id                  := 1;
   p_ext_attrib_values(1).instance_id             := 4004190;
   p_ext_attrib_values(1).attribute_id            := 31000;
   p_ext_attrib_values(1).attribute_value         := 'TEST_5';
   csi_item_instance_pub.update_item_instance(p_api_version                 => 1.0
                                            , p_commit                      => p_commit
                                            , p_init_msg_list               => p_init_msg_lst
                                            , p_validation_level            => 1
                                            , p_instance_rec                => x_instance_rec
                                            , p_ext_attrib_values_tbl       => p_ext_attrib_values
                                            , p_party_tbl                   => p_party_tbl
                                            , p_account_tbl                 => p_account_tbl
                                            , p_pricing_attrib_tbl          => p_pricing_attrib_tbl
                                            , p_org_assignments_tbl         => p_org_assignments_tbl
                                            , p_asset_assignment_tbl        => p_asset_assignment_tbl
                                            , p_txn_rec                     => p_txn_rec
                                            , x_instance_id_lst             => x_instance_id_lst
                                            , x_return_status               => x_return_status
                                            , x_msg_count                   => x_msg_count
                                            , x_msg_data                    => x_msg_data
                                             );
   COMMIT;

-- Output the results
   IF x_msg_count > 0
   THEN
      FOR j IN 1 .. x_msg_count
      LOOP
         fnd_msg_pub.get(j
                       , fnd_api.g_false
                       , x_msg_data
                       , t_msg_dummy
                        );
         t_output                                       :=('Msg' || TO_CHAR(j) || ': ' || x_msg_data);
         DBMS_OUTPUT.put_line(SUBSTR(t_output
                                   , 1
                                   , 255
                                    ));
      END LOOP;
   END IF;

   DBMS_OUTPUT.put_line('x_return_status = ' || x_return_status);
   DBMS_OUTPUT.put_line('x_msg_count = ' || TO_CHAR(x_msg_count));
   DBMS_OUTPUT.put_line('x_msg_data = ' || x_msg_data);
   COMMIT;
END;

Storing Images in Oracle tables

Images and Oracle BLOB data are easy to add to a Oracle table. There are two ways to load BLOBs and CLOBs into the database. The first method uses PL/SQL and the DBMS_LOB package and the BFILE datatype to transfer external LOB files into the database internal LOB structures. The second uses the Oracle Call Interface (OCI) to perform the same function. Let's look at the first method.

To load external LOB data into internal LOB storage using PL/SQL alone you must first use a table containing BFILE locators for the files to allow Oracle to access them.

create table xx_images_tbl (

  file_id number,

  file_desc varchar2(30),

  file_loc bfile,

  file_type varchar2(15))

 /

Listing 3: Example Use of BFILE datatype in a Table

Notice in the above code that no LOB storage clause is specified. This is because all that is stored in the database is a locator value for the BFILE consisting of an internal DIRECTORY specification and a file name. The BFILE locators are loaded into the table using the BFILENAME function and a standard insert statement.

An example of this process is shown here in Listing 4.

INSERT INTO xx_images_tbl VALUES(1,'Test_file',bfilename('XX_DIR_NAME','TEST.JPG'),'JPEG');

commit;

Listing 4: Example Set of INSERT Commands to Load BFILE Locators Manually

TIP:

Using a host command to perform a single column directory listing into a file (for example on NT: dir /B >file.lis), then using the UTL_FILE package to read the contents of the created file into the DBMS_SQL package to build the INSERT commands on the fly, an entire directory of LOB datafiles can be loaded at one time into a BFILE table and then on into the internal LOB storage table. For an example see Listing 5.

CREATE OR REPLACE PROCEDURE xx_insert_images_proc(
   bfile_dir                  IN             VARCHAR2
 , bfile_lis                  IN             VARCHAR2
 , bfile_int_dir                             VARCHAR2
)
AS
   cur                                               INTEGER;
   bfile_int                                         VARCHAR2(100);
   sql_com                                           VARCHAR2(2000);
   file_proc                                         INTEGER;
   file_hand                                         UTL_FILE.file_type;
   file_buff                                         VARCHAR2(1022);
   file_type                                         VARCHAR2(4);
BEGIN
   bfile_int                                      := UPPER(bfile_int_dir);
   file_hand                                      := UTL_FILE.fopen(bfile_dir
                                                                  , bfile_lis
                                                                  , 'R'
                                                                   );

   LOOP
      BEGIN
         UTL_FILE.get_line(file_hand
                         , file_buff
                          );
         cur                                            := DBMS_SQL.open_cursor;
         file_type                                      := SUBSTR(file_buff
                                                                , INSTR(file_buff
                                                                      , '.'
                                                                       ) + 1
                                                                , 3
                                                                 );
         file_type                                      := UPPER(file_type);

         IF file_type = 'GIF'
         THEN
            file_type                                      := 'GIF';
         ELSIF file_type = 'JPG'
         THEN
            file_type                                      := 'JPEG';
         END IF;

         sql_com                                        :=
               'INSERT INTO xx_images_tbl '
            || CHR(10)
            || 'VALUES (xx_images_tbl_seq.NEXTVAL,'
            || CHR(39)
            || CHR(39)
            || ', bfilename('
            || CHR(39)
            || bfile_int
            || CHR(39)
            || ','
            || CHR(39)
            || file_buff
            || CHR(39)
            || ') ,'
            || CHR(39)
            || file_type
            || CHR(39)
            || ')';
         DBMS_OUTPUT.put_line(sql_com);
         DBMS_SQL.parse(cur
                      , sql_com
                      , DBMS_SQL.v7
                       );
         file_proc                                      := DBMS_SQL.EXECUTE(cur);
         DBMS_SQL.close_cursor(cur);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;
   END LOOP;

   UTL_FILE.fclose(file_hand);
END;
/

Listing 5: Example Procedure for Loading BFILE Locators based on an External File List

Once the BFILE locators are set in the BFILE table we can use the DBMS_LOB package to read the external LOB (BFILE) into an internal LOB (BLOB, CLOB or NCLOB). This is shown in Listing

 6. The SELECT from the TEMP_BLOB table initializes the internal LOB values so they can be used, otherwise an error will be returned.

CREATE OR REPLACE PROCEDURE load_lob
AS
   ID                                                NUMBER;
   image1                                            BLOB;
   LOCATOR                                           BFILE;
   bfile_len                                         NUMBER;
   bf_desc                                           VARCHAR2(30);
   bf_name                                           VARCHAR2(30);
   bf_dir                                            VARCHAR2(30);
   bf_typ                                            VARCHAR2(4);
   ctr                                               INTEGER;

   CURSOR get_id
   IS
      SELECT file_id
           , file_desc
           , file_type
        FROM xx_images_tbl;
BEGIN
   OPEN get_id;

   LOOP
      FETCH get_id
       INTO ID
          , bf_desc
          , bf_typ;

      EXIT WHEN get_id%NOTFOUND;
      DBMS_OUTPUT.put_line('ID: ' || TO_CHAR(ID));

      SELECT file_loc
        INTO LOCATOR
        FROM xx_images_tbl
       WHERE file_id = ID;

      DBMS_LOB.filegetname(LOCATOR
                         , bf_dir
                         , bf_name
                          );
      DBMS_OUTPUT.put_line('Dir: ' || bf_dir);
      DBMS_LOB.fileopen(LOCATOR
                      , DBMS_LOB.file_readonly
                       );
      bfile_len                                      := DBMS_LOB.getlength(LOCATOR);
      DBMS_OUTPUT.put_line('ID: ' || TO_CHAR(ID) || ' length: ' || TO_CHAR(bfile_len));

      SELECT temp_blob
        INTO image1
        FROM temp_blob;

      bfile_len                                      := DBMS_LOB.getlength(LOCATOR);
      DBMS_LOB.loadfromfile(image1
                          , LOCATOR
                          , bfile_len
                          , 1
                          , 1
                           );

      INSERT INTO xx_images_tbl
           VALUES (ID
                 , bf_desc
                 , image1
                 , bf_typ
                  );

      DBMS_OUTPUT.put_line(bf_desc || ' Length: ' || TO_CHAR(bfile_len) || ' Name: ' || bf_name || ' Dir: ' || bf_dir || ' ' || bf_typ);
      DBMS_LOB.fileclose(LOCATOR);
   END LOOP;
END;
/

/

Listing 6: Example Procedure to Load BFILE values into Internal LOBs

By enforcing a naming standard on the external LOB files the loading procedure in Listing 6.6 could be modified to place BLOB types into BLOBs, CLOB types into CLOBs and NCLOB types into NCLOBs based on the file type values that are parsed from the file extensions in Listing 5. Between the procedure in Listing 5 and the one shown in Listing 6 the example tables in Listings 1 and 3 are populated with LOB values located in a specific directory.