How to load(Interface) Contract note text into Oracle Apps R12 ?

This Package is used to load the OKL contracts note text into Oracle Apps Base tables.

After loading the data, you can check it in JTF_NOTES_B,JTF_NOTES_TL tables.

--Package Spec
create or replace PACKAGE XX_NOTESCON_PKG AUTHID CURRENT_USER
AS
 PROCEDURE XXEC_OKL_MAIN_PROC (
      p_errbuf_out    OUT   VARCHAR2,
      p_retcode_out   OUT   VARCHAR2
   );
lv_error_location varchar2(2000);
END XX_NOTESCON_PKG;
/
create or replace PUBLIC synonym XXEC_OKL_AGCN_NOTESCON_PKG for XXEC.XXEC_OKL_AGCN_NOTESCON_PKG
/
GRANT ALL ON XXEC.XXEC_OKL_AGCN_NOTESCON_PKG TO APPS
/
SHOW ERRORS
/


--Package Body.

create or replace PACKAGE BODY XXEC_NOTESCON_PKG
AS

 PROCEDURE XXEC_OKL_MAIN_PROC (
      p_errbuf_out    OUT   VARCHAR2,
      p_retcode_out   OUT   VARCHAR2
 )
 IS
/*-------------------------------------------------------
  Defining the Cursor 
 --------------------------------------------------------*/
 --Cursor to get the data from stage table.

      CURSOR notes_data
      IS
         select XNT.CUSTOMER_NAME
               ,XNT.CONTRACT_NUMBER
               ,HAB.ID
               ,HAB.BILL_TO_SITE_USE_ID
               ,to_date(XNT.creation_date,'DD-MM-YY') creation_date
               ,XNT.NOTE_TEXT
               ,XNT.STATUS_FLAG
        from XXEC_OKL_NOTESCON_TMP XNT
            ,OKC_K_HEADERS_ALL_B HAB
        WHERE 1=1
          AND XNT.CONTRACT_NUMBER=HAB.CONTRACT_NUMBER
          ORDER BY XNT.CONTRACT_NUMBER;


 -- Decalring Local Variables.
      l_api_version          NUMBER;
      l_init_msg_list        VARCHAR2 (1);
      l_validation_level     NUMBER;
      l_commit               VARCHAR2 (1);
      l_return_status        VARCHAR2 (1);
      l_msg_count            NUMBER;
      l_msg_data             VARCHAR2 (2000);
      l_jtf_note_id          NUMBER                                   := NULL;
      l_source_object_code   VARCHAR2 (50);
      l_note_status          VARCHAR2 (8);
      l_note_contexts        jtf_notes_pub.jtf_note_contexts_tbl_type;
      l_msg_index            NUMBER;
      l_msg_index_out        NUMBER;
      l_count                NUMBER                                   := 0;
      l_acount               NUMBER                                   := 0;
      l_success  number:=0;
      l_error  number:=0;
      l_error_e number:=0;
      l_notes_err_count      NUMBER                                   := 0;
      l_note_type            VARCHAR2(80) ;
      l_flag                 VARCHAR2(1)  := 'N';
BEGIN
   XXEC_NOTESCON_PKG.lv_error_location:='Starts Execution of Code'; --Intializing value for error location variable.
   fnd_file.put_line (fnd_file.LOG, 'Begin ACCESS NOTES Conversion ');
   fnd_file.put_line (fnd_file.LOG, fnd_profile.VALUE ('ORG_ID'));

   --Intializing the values for Local Variables.
   l_api_version := 1.0;
   l_init_msg_list := fnd_api.g_true;
   l_validation_level := fnd_api.g_valid_level_full;
   l_commit := fnd_api.g_true;
   l_msg_count := 0;
   l_note_status := 'I';
   l_source_object_code := 'OKC_K_HEADER';
   l_note_type := 'AS_USER';

   XXEC_NOTESCON_PKG.lv_error_location:='Before loop1'; --Intializing value for error location variable.
   FOR v_cur_notes_data IN notes_data
   LOOP  --For Loop1
      XXEC_NOTESCON_PKG.lv_error_location:='Inside loop1';  --Intializing value for error location variable.
      fnd_file.put_line (fnd_file.LOG, 'Customer : ' || v_cur_notes_data.customer_name);
      fnd_file.put_line (fnd_file.LOG, 'Cteation Date : ' || v_cur_notes_data.creation_date);
      fnd_file.put_line (fnd_file.LOG, 'Note : ' || v_cur_notes_data.note_text);
      l_flag := 'N';
      l_acount := l_acount + 1;

           l_flag := 'Y';
         /*-------------------------------------------------
           Populating the l_note_contexts of pl/sql table type
           -------------------------------------------------*/
         l_count := l_count + 1;
         fnd_file.put_line (fnd_file.LOG, 'Current Cursor count ' || l_count);
         l_note_contexts (1).note_context_type := 'OKC_K_HEADER';
         l_note_contexts (1).note_context_type_id := v_cur_notes_data.ID;
         l_note_contexts (1).last_update_date := SYSDATE;
         l_note_contexts (1).last_updated_by := fnd_global.user_id;
         l_note_contexts (1).creation_date := SYSDATE;
         l_note_contexts (1).created_by := fnd_global.user_id;
         l_note_contexts (1).last_update_login := fnd_global.login_id;
         /*-------------------------------------------------
                     Calling the API
           ------------------------------------------------*/
         -- fnd_file.put_line (fnd_file.LOG, 'Calling API ');

         XXEC_NOTESCON_PKG.lv_error_location:='Before calling API JTF_NOTES_PUB';  --Intializing value for error location variable.

        jtf_notes_pub.create_note  --Calling API.
                 (p_api_version                => l_api_version,
                  p_init_msg_list              => l_init_msg_list,
                  p_commit                     => l_commit,
                  p_validation_level           => l_validation_level,
                  x_return_status              => l_return_status,
                  x_msg_count                  => l_msg_count,
                  x_msg_data                   => l_msg_data,
                  p_jtf_note_id                => l_jtf_note_id,
                  --p_entered_by                 => 1396,
                  p_entered_date               => v_cur_notes_data.creation_date,
                 -- p_source_object_id           => v_cur_notes_data.bill_to_site_use_id,
                  p_source_object_id           => v_cur_notes_data.ID,
                  p_source_object_code         => l_source_object_code, --OKC_K_HEADER
                  p_notes                      => v_cur_notes_data.note_text,
                  p_notes_detail               => NULL,
                  p_note_type                  => l_note_type,
                  p_note_status                => l_note_status,
                  p_jtf_note_contexts_tab      => l_note_contexts,
                  x_jtf_note_id                => l_jtf_note_id,
                  p_last_update_date           => SYSDATE,
                  p_last_updated_by            => fnd_global.user_id,
                  p_creation_date              => v_cur_notes_data.creation_date,
                  --p_created_by                 => 1396,
                  p_last_update_login          => fnd_global.login_id
                 );
                fnd_file.put_line(fnd_file.LOG,'ACCESS NOTES transferred into Collections and Note ID '|| l_jtf_note_id);
                 XXEC_NOTESCON_PKG.lv_error_location:='After Calling the API JTF_NOTES_PUB';

                 XXEC_NOTESCON_PKG.lv_error_location:='Updating Status Flag in XXEC_OKL_NOTESCON_TMP table';
                 if l_return_status='S' then
                 XXEC_NOTESCON_PKG.lv_error_location:='Counting the Success records';
                   l_success := l_success+1;
                      update XXEC_OKL_NOTESCON_TMP SET STATUS_FLAG=l_return_status
                      where contract_number=v_cur_notes_data.CONTRACT_NUMBER
                        and NOTE_TEXT=v_cur_notes_data.note_text;
                    fnd_file.put_line(fnd_file.output,'Successed Contract'||v_cur_notes_data.CONTRACT_NUMBER);
                 elsif l_return_status='E' then
                 XXEC_NOTESCON_PKG.lv_error_location:='Counting Error records';
                    l_error := l_error+1;
                      update XXEC_OKL_NOTESCON_TMP SET STATUS_FLAG=l_return_status
                      where contract_number=v_cur_notes_data.CONTRACT_NUMBER
                        and NOTE_TEXT=v_cur_notes_data.note_text;
                  fnd_file.put_line(fnd_file.log,'Errored Contract Number '||v_cur_notes_data.CONTRACT_NUMBER);
                 else
                  XXEC_NOTESCON_PKG.lv_error_location:='Counting Error records';
                      l_error_e := l_error_e+1;
                       update XXEC_OKL_NOTESCON_TMP SET STATUS_FLAG=l_return_status
                      where contract_number=v_cur_notes_data.CONTRACT_NUMBER
                        and NOTE_TEXT=v_cur_notes_data.note_text;
                  fnd_file.put_line(fnd_file.log,'l_error_e Errored Contract Number '||v_cur_notes_data.CONTRACT_NUMBER);
                 end if;
                 COMMIT;

         /*----------------------------------------------
           Checking for Errors
           ---------------------------------------------*/
         IF (fnd_msg_pub.count_msg > 0)
         THEN
            FOR i IN 1 .. fnd_msg_pub.count_msg
            LOOP --Loop3

            XXEC_NOTESCON_PKG.lv_error_location:='Inside Loop3';  --Intializing value for error location variable.
               fnd_msg_pub.get (p_msg_index          => i,
                                p_encoded            => 'F',
                                p_data               => l_msg_data,
                                p_msg_index_out      => l_msg_index_out
                               );
               fnd_file.put_line (fnd_file.LOG, 'API ERROR: ' || l_msg_data);
            END LOOP; --end of loop3
            XXEC_NOTESCON_PKG.lv_error_location:='End of Loop3';  --Intializing value for error location variable.
            l_notes_err_count := l_notes_err_count + 1;
         END IF;        
   END LOOP; --end of loop1
   XXEC_NOTESCON_PKG.lv_error_location:='end of Loop1';  --Intializing value for error location variable.
   COMMIT;  --commiting the processed records.

   fnd_file.put_line (fnd_file.LOG, 'Total count of ALL Customer Notes - ' || l_acount); --Printing Total Count of Customer Notes.
   fnd_file.put_line (fnd_file.LOG, 'Total Success Records Count - '|| l_success);
   fnd_file.put_line (fnd_file.log, 'Total error Records Count - '|| l_error);
   fnd_file.put_line (fnd_file.log, 'Total error Records Count e- '|| l_error_e);
   fnd_file.put_line (fnd_file.LOG, 'Total Error count  - ' || l_notes_err_count); --Printing Total error count.
   fnd_file.put_line (fnd_file.LOG, 'END of ACCESS NOTES Conversion ');
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'ERROR: ' || SQLERRM);
         fnd_file.put_line (fnd_file.log,'The error is at '||XXEC_NOTESCON_PKG.lv_error_location);
         p_errbuf_out := SQLERRM;
         p_retcode_out := 2;
 END XXEC_OKL_MAIN_PROC;
END XXEC_NOTESCON_PKG;
/
SHOW ERRORS
/