Monday 24 September 2018

QA Collection Elements creation

API used for EAM preventive collection plan creation and its Elements insertion.
Basically this QA tables are used for capturing daily meter reading of asset in EAM and then
This will used for work order creation.

CREATE OR REPLACE PACKAGE APPS.MLGWEAM_QA_PLANS_ELEMENTS_PKG
IS
  /*+-----------------------------------------------------------------------------------+
  -- ================================== Record History  =================================
  ---------------------------------------------------------------------------------------
  --
  --
  --                  Oracle Applications Release 12.1.3
  --
  --   Type             :  Interface
  --   Reference        :  EX067 - MD050 - Pavement Replacement Form -1[1]
  --   Description      :  Pavement Collaction Plan Creation and its elements insertion
  --   Module Name      :  Enterprise Asset Management
  --   Program Name     :  MLGWEAM_QA_PLANS_ELEMENTS_PKG
  --   Schema Owner     :  APPS
  --   Run environment  :  PL/SQL
  --
  
  +----------------------------------------------------------------------------------+ */
  /*+-----------------------------------------------------------------------------------+
  Procedure "MLGWEAM_QA_PLANS_ELEMENTS_PKG" to create Pavement Collaction Plan Creation and its elements insertion
  /*+-----------------------------------------------------------------------------------+*/
procedure QA_PLANS_CREATION_PROC
 (P_Errbuf OUT VARCHAR2,
    P_Retcode OUT NUMBER
);
procedure qa_plan_elements_proc
 (P_Errbuf OUT VARCHAR2,
    P_Retcode OUT NUMBER
);
      /*+-----------------------------------------------------------------------------------+
  Declaring Global Variables.
  /*+-----------------------------------------------------------------------------------+*/
  g_count              NUMBER         := 1;
  g_error_mesg        VARCHAR2 (4000) := NULL;
  g_error_log_flag    VARCHAR2 (10)   := 'N';
  g_error_flag        VARCHAR2 (1)    := 'N';
  g_org_id            VARCHAR2 (4)    := fnd_profile.VALUE ('ORG_ID');
  g_request_id        NUMBER          := fnd_global.conc_request_id;
  g_user_id           NUMBER          := fnd_profile.VALUE ('USER_ID');
  g_resp_appl_id      NUMBER          := fnd_profile.VALUE ('RESP_APPL_ID');
  G_RESPONSIBILITY_ID NUMBER          := FND_PROFILE.VALUE ('RESP_ID');
END MLGWEAM_QA_PLANS_ELEMENTS_PKG;
/



CREATE OR REPLACE PACKAGE body APPS.MLGWEAM_QA_PLANS_ELEMENTS_PKG
IS
  /*+-----------------------------------------------------------------------------------+
  -- ================================== Record History  =================================
  ---------------------------------------------------------------------------------------
  --
  --
  --                  Oracle Applications Release 12.1.3
  --
  --   Type             :  Interface
  --   Reference        :  EX067 - MD050 - Pavement Replacement Form -1[1]
  --   Description      :  Pavement Collaction Plan Creation and its elements insertion
  --   Module Name      :  Enterprise Asset Management
  --   Program Name     :  MLGWEAM_QA_PLANS_ELEMENTS_PKG
  --   Schema Owner     :  APPS
  --   Run environment  :  PL/SQL
  --
  

  /*+-----------------------------------------------------------------------------------+
  Procedure "MLGWEAM_QA_PLANS_ELEMENTS_PKG" to create Pavement Collaction Plan Creation and its elements insertion
  /*+-----------------------------------------------------------------------------------+*/
procedure QA_PLANS_CREATION_PROC
 (P_Errbuf OUT VARCHAR2,
    P_Retcode OUT NUMBER
)
is
x_v_plan_id number;
x_v_msg_count number;
x_v_msg_data varchar2(4000);
x_v_return_status varchar2(1000);
x_error_flag varchar2(1);
x_user_name  varchar2(100);
v_plan_id number:= null;
begin
 FND_GLOBAL.APPS_INITIALIZE (G_USER_ID, 
                              G_RESPONSIBILITY_ID, 
                              G_RESP_APPL_ID );
      FND_FILE.PUT_LINE (FND_FILE.log, 'User Id : ' || G_USER_ID);
      FND_FILE.PUT_LINE (FND_FILE.log, 'Responsibility Id : ' || G_RESPONSIBILITY_ID );
      FND_FILE.PUT_LINE (FND_FILE.log, 'Resp Application Id : ' || G_RESP_APPL_ID );
      FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request ID          : ' || G_REQUEST_ID );
x_error_flag  := null;
    begin
    select user_name
    into   x_user_name 
    from fnd_user
    where 1=1
    and user_id = G_USER_ID;
      exception
      when others then
      x_error_flag := 'E';
      fnd_file.put_line(fnd_file.log,'Error While selection username '||SQLERRM);
     end;
         begin
            select plan_id
            into v_plan_id
            from qa_plans
            where 1=1
            and name = 'PAVEMENT REPLACEMENT';
            exception
            when no_data_found then
            null;
            when others then
            x_error_flag := 'E';
           fnd_file.put_line(fnd_file.log,'Error While selection of PAVEMENT REPLACEMENT plan '||SQLERRM);
           end;
fnd_file.put_line(fnd_file.log,'Org id is '||g_org_id);
   if x_error_flag is null and v_plan_id  is null then
    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                 => x_user_name ,
        p_plan_name                 => 'PAVEMENT REPLACEMENT',
        p_organization_code         => 'LGW',
        p_plan_type                 => 'Pavement Replacement Plan',
        p_description               => 'Pavement Replacement Plan For WO',
        p_effective_from            => sysdate,
        p_effective_to              => NULL,
        p_spec_assignment_type      => qa_plans_pub.g_spec_type_none,
        p_multirow_flag             => 2,
        x_plan_id                   => x_v_plan_id,
        x_msg_count                 => x_v_msg_count,
        x_msg_data                  => x_v_msg_data,
        x_return_status             => x_v_return_status
    );    
   end if;
   commit;
   fnd_file.put_line(fnd_file.log,'Return Status '||x_v_return_status||' and Return Message '||x_v_msg_data);
end;
procedure qa_plan_elements_proc
 (P_Errbuf OUT VARCHAR2,
    P_Retcode OUT NUMBER
)
is
cursor c_qa_elements is select * from MLGWEAM_QA_ELEMENTS_TBL
where 1=1
and ELEMENT_NAME != 'COMPACTION_FAILURE_WO';
x_v_plan_id number;
x_v_msg_count number;
x_v_msg_data varchar2(4000);
x_v_return_status varchar2(1000);
x_user_name varchar2(100);
v_plan_id number;
x_error_flag varchar2(1);
x_default_value varchar2(10);
x_mandatory_flag  varchar2(10);
x_enabled_flag  varchar2(10);
x_displayed_flag varchar2(10);
begin
 FND_GLOBAL.APPS_INITIALIZE (G_USER_ID, 
                              G_RESPONSIBILITY_ID, 
                              G_RESP_APPL_ID );
      FND_FILE.PUT_LINE (FND_FILE.log, 'User Id : ' || G_USER_ID);
      FND_FILE.PUT_LINE (FND_FILE.log, 'Responsibility Id : ' || G_RESPONSIBILITY_ID );
      FND_FILE.PUT_LINE (FND_FILE.log, 'Resp Application Id : ' || G_RESP_APPL_ID );
      FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request ID          : ' || G_REQUEST_ID );
null;
x_error_flag := null;
 begin
    select user_name
    into   x_user_name 
    from fnd_user
    where 1=1
    and user_id = G_USER_ID;
      exception
      when others then
      x_error_flag := 'E';
      fnd_file.put_line(fnd_file.log,'Error While selection username '||SQLERRM);
     end;
         begin
            select plan_id
            into v_plan_id
            from qa_plans
            where 1=1
            and name = 'PAVEMENT REPLACEMENT';
            exception
            when no_data_found then
            null;
            when others then
            x_error_flag := 'E';
           fnd_file.put_line(fnd_file.log,'Error While selection of PAVEMENT REPLACEMENT plan '||SQLERRM);
           end;
           fnd_file.put_line(fnd_file.log,'Org id is collec '||g_org_id);
 if x_error_flag is null and v_plan_id is not null then
 fnd_file.put_line(fnd_file.log,'2');
  for cx_qa_elements in c_qa_elements loop
   fnd_file.put_line(fnd_file.log,'1');
    x_default_value:= null;
    x_mandatory_flag   := null;
    x_enabled_flag   := null;
    x_displayed_flag  := null;

   begin
   select decode(cx_qa_elements.DEFAULT_VALUE,null,qa_plans_pub.g_inherit,cx_qa_elements.DEFAULT_VALUE)
   into x_default_value
   from dual;
    end;
    begin
     select decode(cx_qa_elements.MANDATORY_FLAG,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
     into x_mandatory_flag
     from dual;
    end;
    begin
    select decode(cx_qa_elements.ENABLED_FLAG,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)--ok
    into x_enabled_flag 
    from dual;
    end;
    begin
    select decode(cx_qa_elements.DISPLAYED_FLAG,'YES',fnd_api.g_true,'NO',fnd_api.g_false,null)
    into x_displayed_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                 => x_user_name ,
        p_plan_name                 => 'PAVEMENT REPLACEMENT',
        p_organization_code         => 'LGW',
        p_element_name              => cx_qa_elements.ELEMENT_NAME,
        p_prompt_sequence           => cx_qa_elements.PROMPT_SEQUENCE,
        p_prompt                    => cx_qa_elements.P_PROMPT,
        p_default_value             => x_default_value,
        p_enabled_flag              =>  x_enabled_flag,
        p_mandatory_flag            => x_mandatory_flag  ,
        p_displayed_flag            => x_displayed_flag ,
        p_result_column_name        => cx_qa_elements.RESULT_COLUMN_NAME,
        x_msg_count                 => x_v_msg_count,
        x_msg_data                  => x_v_msg_data,
        x_return_status             => x_v_return_status
    );
    fnd_file.put_line(fnd_file.log,'Return Status '||x_v_return_status||' and Return Message '||x_v_msg_data);
  end loop;
  end if;
commit;
end;
END MLGWEAM_QA_PLANS_ELEMENTS_PKG;
/

1 comment:

  1. Could you Please share how to delete specification element and also add both

    ReplyDelete