Oracle Quality Collection Plan Api - Interface




create or replace PACKAGE      XXCUSTOM_EAM_COLLCT_PN_CONV_PKG  IS


/*
---------------------------Quality Collection Plan Set-up ----------------------

*/
  gc_request_id NUMBER := fnd_global.conc_request_id;
  gc_login_id   NUMBER := fnd_global.login_id;
  gc_user_id    NUMBER := fnd_global.user_id;


  PROCEDURE main(errbuf          OUT VARCHAR2,
                 retcode         OUT VARCHAR2,
                 p_process_flag  IN VARCHAR2);

  PROCEDURE validate_data;

  PROCEDURE collection_plan_process;

  PROCEDURE add_collection_plan_element;

END XXCUSTOM_EAM_COLLCT_PN_CONV_PKG ;




create or replace PACKAGE BODY      XXCUSTOM_EAM_COLLCT_PN_CONV_PKG IS

  /***************** Main Procedure Starts *********************/
  PROCEDURE main(errbuf          OUT VARCHAR2,
                 retcode         OUT VARCHAR2,
                 p_process_flag  IN VARCHAR2) IS
  BEGIN

    IF p_process_flag = 'V' THEN
      validate_data;
    END IF;

    IF p_process_flag = 'P' THEN
      collection_plan_process;
      add_collection_plan_element;
    END IF;

  EXCEPTION
    WHEN OTHERS THEN
      errbuf  := 'Exception in Main - ' || SQLERRM;
      retcode := 2;
      fnd_file.put_line(fnd_FILE.LOG,
                        ' Error Occured in The Procedure MAIN');
  END MAIN;

  /****************** Validate Procedure Starts *******************/
  PROCEDURE validate_data
  IS

    CURSOR c_stg_data IS
      SELECT rowid row_id, stg.*
        FROM XXCUSTOM_EAM_COLLCT_PN_STG STG
       WHERE PROCESS_FLAG in  ( 'N', 'E');

    l_organization_id  NUMBER;
    l_comp_item_id     NUMBER;
    l_assembly_item_id NUMBER;
    l_supply_type_cd   VARCHAR2(40);
    l_subinventory     VARCHAR2(10);
    l_status           VARCHAR2(1);
    l_error_message    VARCHAR2(4000);
 
    lv_item_valid     NUMBER;
    l_set_id          NUMBER;
     l_type_code       VARCHAR2(40);
    l_cnt_plan       NUMBER;
    l_prompt_name     VARCHAR2(120);
    lv_cnt_data      NUMBER;
  BEGIN



    BEGIN
      UPDATE  XXCUSTOM_EAM_COLLCT_PN_STG
      SET INFORMATION_FLAG = replace (INFORMATION_FLAG, CHR(13), '')
      WHERE process_flag   = 'N';
      COMMIT;
    End ;


  FOR i_stg_data IN c_stg_data LOOP
   
    l_organization_id :=null;
     l_type_code       :=null;
    l_status          :='N' ;
    l_error_message   :=null;
    l_cnt_plan        :=0;
    l_prompt_name     :=NULL;
    lv_cnt_data      :=0;
 
      /********** Validation for Organization Code ****************/
      BEGIN
        SELECT organization_id
          INTO l_organization_id
          FROM org_organization_definitions
         WHERE (upper (organization_code) = upper (i_stg_data.organization_code)
               OR upper (organization_name) = upper (i_stg_data.organization_code) );
      EXCEPTION
        WHEN OTHERS THEN
          l_status        := 'E';
          l_error_message := l_error_message || '-' ||
                             'Invalid Organization Code';
      END;
 
 
 
    /********** Validation for Collection Plan   ****************/
       BEGIN
             Select count (*)
              INTO lv_cnt_data
              From (
              SELECT
                organization_code,  plan_name,
                description,   effective_from_date,
                effective_to_date,   plan_type,
                display_multi_flag
              FROM    XXCUSTOM_eam_collct_pn_stg stg
              WHERE   PLAN_NAME = i_stg_data.PLAN_NAME
              GROUP BY
                organization_code,  plan_name,
                description,   effective_from_date,
                effective_to_date,   plan_type,
                display_multi_flag
                )  ;
   
       IF lv_cnt_data <> 1  THEN
          l_status        := 'E';
          l_error_message := l_error_message || '-' ||
                             ' Collection Plan must be uniq ';
      END IF;
   
   
   
       END;
     
     
 
 
 
 
 
 
  /********** Validation for Collection Plan  ****************/

    BEGIN
        SELECT count (*)
          INTO l_cnt_plan
          FROM QA_PLANS
         WHERE upper (NAME) = upper (i_stg_data.PLAN_NAME);
       
      IF l_cnt_plan <> 0  THEN
          l_status        := 'E';
          l_error_message := l_error_message || '-' ||
                             'Invalid Collection Plan Code';
      END IF;                     
      END;
 
    /********** Validation for Element Name ****************/
      BEGIN
        SELECT PROMPT
          INTO l_prompt_name
          FROM QA_CHARS
         WHERE NAME = i_stg_data.ELEMENT_NAME;
      EXCEPTION
        WHEN OTHERS THEN
          l_status        := 'E';
          l_error_message := l_error_message || '-' ||
                             'Invalid Element Name ';
      END;

/***************** Flag Updation , Validation Ends ******************/
 
 
  IF l_status = 'E' THEN
   
        UPDATE XXCUSTOM_EAM_COLLCT_PN_STG
           SET PROCESS_FLAG   = l_status,
               error_message  = l_error_message
         WHERE rowid          = i_stg_data.row_id;
       
   ---fnd_file.put_line(fnd_file.log,' Schedule name --: ' || i_stg_data.schedule_name ||' :- validation failed Details :-'||l_error_message);
       
       
  ELSE
    UPDATE XXCUSTOM_EAM_COLLCT_PN_STG
      SET PROCESS_FLAG          = 'V',
          error_message           = 'Record Validate',
          organization_id         = l_organization_id,
          prompt_name            = l_prompt_name,
          creation_date          = SYSDATE,
          last_updated_date     = SYSDATE ,
          last_updated_by       = gc_user_id
         WHERE rowid = i_stg_data.row_id;
  END IF;
   
      COMMIT;
    END LOOP;



    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      fnd_file.put_line(fnd_file.log,
                        'Validate Procedure Main Exception - ' || SQLERRM);
  END validate_data;

  /********************* Process BOM Procedure Starts ********************/
  PROCEDURE collection_plan_process  IS

    CURSOR cur_upload_data IS
SELECT
  organization_code,
  plan_name,
  description,
  effective_from_date,
  effective_to_date,
  plan_type,
  display_multi_flag
FROM
  XXCUSTOM_eam_collct_pn_stg stg
WHERE
  process_flag = 'V'
AND plan_id IS NULL 
GROUP BY
  organization_code,
  plan_name,
  description,
  effective_from_date,
  effective_to_date,
  plan_type ,
              display_multi_flag   ;


      l_success_count       NUMBER := 0;
      l_failure_count       NUMBER := 0;
      l_all_count           NUMBER := 0; 
 
lv_plan_id number;
l_msg_count number;
l_msg_data varchar2(4000);
l_return_status varchar2(1000);
l_multi_flag   number ;

   BEGIN
    fnd_global.apps_initialize(fnd_global.user_id,
                               fnd_global.resp_id,
                               fnd_global.resp_appl_id
                               );
 
       
FOR i_api IN   cur_upload_data
LOOP
l_multi_flag :=NULL;
lv_plan_id   :=NULL;
l_msg_data   :=NULL;
l_msg_count  :=NULL;
l_return_status :=NULL;

/*
Select decode ( upper (i_api.display_multi_flag), 'YES', 2, 'NO', 1,  2 )
INTO l_multi_flag
FROM dual;
*/
 l_all_count                  := l_all_count + 1;

  BEGIN

  qa_plans_pub.create_collection_plan
                          (   p_api_version                => 1.0,
                              p_init_msg_list             => fnd_api.g_false,
                              p_validation_level          => fnd_api.g_valid_level_full,
                              p_user_name                 =>  fnd_global.user_name ,
                              p_plan_name                 => i_api.plan_name,
                              p_organization_code         => i_api.organization_code,
                              p_plan_type                 => i_api.plan_type,
                              p_description               => i_api.description,
                              p_effective_from            => i_api.effective_from_date,
                              p_effective_to              => i_api.effective_to_date,
                              p_spec_assignment_type      => qa_plans_pub.g_spec_type_none,
                              p_multirow_flag             => 2, --l_multi_flag,
                              x_plan_id                   => lv_plan_id,
                              x_msg_count                 => l_msg_count,
                              x_msg_data                  => l_msg_data,
                              x_return_status             => l_return_status
                          ); 
   commit;

 
     IF l_return_status =fnd_api.g_ret_sts_success
THEN
  UPDATE XXCUSTOM_eam_collct_pn_stg
      SET process_flag          = 'P',
          error_message         = 'Callection Record Created',
          PLAN_ID               = lv_plan_id,
          last_updated_date     = SYSDATE ,
          last_updated_by       = gc_user_id
         WHERE plan_name = i_api.plan_name;
l_success_count := l_success_count + 1;
     ELSE

  IF l_msg_count >= 1
                    THEN
                       FOR l_index IN 1 .. l_msg_count
                       LOOP
                          l_msg_data :=
                             fnd_msg_pub.get (p_msg_index      => l_index,
                                              p_encoded        => fnd_api.g_false
                                             );
                       END LOOP;
            END IF;


  UPDATE XXCUSTOM_eam_collct_pn_stg
      SET process_flag           = 'E1',
          error_message          = l_msg_data,
          last_updated_date      = SYSDATE ,
          last_updated_by        = gc_user_id
         WHERE plan_name = i_api.plan_name;

l_failure_count := l_failure_count + 1;
     END IF;

COMMIT;

dbms_output.put_line ('Return Status '||l_return_status||' and Return Message '||l_msg_data);
       
  END;
END LOOP;       
     
       
     
  fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
  fnd_file.put_line (fnd_file.log,'  Total Record count : '||L_ALL_COUNT);
  fnd_file.put_line (fnd_file.log,'  Total Success Record Count : '||L_SUCCESS_COUNT);
  fnd_file.put_line (fnd_file.log,'  Total Fail Record Count :'||L_FAILURE_COUNT);
  fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
 
  EXCEPTION
    WHEN OTHERS THEN
    fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
    fnd_file.put_line(fnd_file.log,
                        '+********************** Exception found in XXCUSTOM_EAM_COLLCT_PN_CONV_PKG.MAIN ----> Process Data Procedure - ' || SQLERRM);
    fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
  END collection_plan_process;

PROCEDURE add_collection_plan_element

IS
 CURSOR cur_upload_data IS
SELECT
  rowid row_id, stg.*
FROM
  XXCUSTOM_eam_collct_pn_stg stg
WHERE
  process_flag = 'P'
AND plan_id IS NOT NULL  ;

l_msg_count        NUMBER;
l_msg_data         VARCHAR2(4000);
l_return_status    VARCHAR2(1000);

l_default_value    VARCHAR2(10);
l_mandatory_flag   VARCHAR2(10);
l_enabled_flag     VARCHAR2(10);
l_displayed_flag   VARCHAR2(10);
l_read_only_flag   VARCHAR2(10);
l_information_flag VARCHAR2(10);

l_success_count       NUMBER := 0;
l_failure_count       NUMBER := 0;
l_all_count           NUMBER := 0; 

BEGIN

BEGIN
 FOR i_api IN cur_upload_data

LOOP


l_default_value    :=NULL;
l_mandatory_flag   :=NULL;
l_enabled_flag     :=NULL;
l_displayed_flag   :=NULL;
l_read_only_flag   :=NULL;
l_enabled_flag     :=NULL;
l_information_flag :=NULL;

l_msg_data   :=NULL;
l_msg_count  :=NULL;
l_return_status :=NULL;
 l_all_count                  := l_all_count + 1;
 /*
 begin
   select --- decode(i_api.DEFAULT_VALUE,null,qa_plans_pub.g_inherit,cx_qa_elements.DEFAULT_VALUE)
   qa_plans_pub.g_inherit
   into l_default_value
  from dual;
    end;
*/
    begin
     select decode(i_api.mandatory_flag,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
     into l_mandatory_flag
     from dual;
    end;
    begin
    select decode(i_api.enabled_flag,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)--ok
    into l_enabled_flag
    from dual;
    end;

begin
    select decode(i_api.read_only_flag,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
    into l_read_only_flag
from dual;
    end;

    begin
    select decode(i_api.displayed_flag,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
    into l_displayed_flag
from dual;
    end;
 
    begin
    select decode(i_api.information_flag,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
    into l_information_flag
from dual;
    end;



    qa_plans_pub.add_plan_element
  ( p_api_version               => 1.0,
p_init_msg_list             => fnd_api.g_false,
p_validation_level          => fnd_api.g_valid_level_full,
p_user_name                 => fnd_global.user_name ,
p_plan_name                 => i_api.plan_name,
p_organization_code         => i_api.organization_code,
p_element_name              => i_api.element_name,
p_prompt_sequence           => NULL,-- i_api.prompt_seq,
p_prompt                    => i_api.prompt_name,
p_default_value             => l_default_value,
p_enabled_flag              => l_enabled_flag,
p_mandatory_flag            => l_mandatory_flag  ,
p_displayed_flag            => l_displayed_flag ,
p_read_only_flag            => l_read_only_flag ,
p_information_flag          => l_information_flag ,
p_result_column_name        => NULL, --'ATTRIBUTE',
x_msg_count                 => l_msg_count,
x_msg_data                  => l_msg_data,
x_return_status             => l_return_status
);
COMMIT;

 IF l_return_status =fnd_api.g_ret_sts_success
THEN
  UPDATE XXCUSTOM_eam_collct_pn_stg
      SET PROCESS_FLAG          = 'S',
          error_message         = 'Callection and Element Created',
          last_updated_date     = SYSDATE ,
          last_updated_by       = gc_user_id
         WHERE rowid = i_api.row_id;
l_success_count := l_success_count + 1;
     ELSE

  IF l_msg_count >= 1
                    THEN
                       FOR l_index IN 1 .. l_msg_count
                       LOOP
                          l_msg_data :=
                             fnd_msg_pub.get (p_msg_index      => l_index,
                                              p_encoded        => fnd_api.g_false
                                             );
                       END LOOP;
            END IF;


  UPDATE XXCUSTOM_EAM_COLLCT_PN_STG
      SET PROCESS_FLAG           = 'E1',
          error_message          = l_msg_data,
          last_updated_date      = SYSDATE ,
          last_updated_by        = gc_user_id
          WHERE rowid = i_api.row_id;

l_failure_count := l_failure_count + 1;
     END IF;
END LOOP;

  fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
  fnd_file.put_line (fnd_file.log,'  Total Record count : '||L_ALL_COUNT);
  fnd_file.put_line (fnd_file.log,'  Total Success Record Count : '||L_SUCCESS_COUNT);
  fnd_file.put_line (fnd_file.log,'  Total Fail Record Count :'||L_FAILURE_COUNT);
  fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
 
END ;


  EXCEPTION
    WHEN OTHERS THEN
    fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
    fnd_file.put_line(fnd_file.log,
                        '+********************** Exception found in XXCUSTOM_eam_collct_pn_conv_pkg.add_collection_plan_element ----> Process Data Procedure - ' || SQLERRM);
    fnd_file.put_line (fnd_file.log,'+--------------------------------------+');
END add_collection_plan_element;


END XXCUSTOM_EAM_COLLCT_PN_CONV_PKG;


CSV DATA FILE ---

Output file.