mardi 16 janvier 2018

Passing an invalid user id,code should display message-The given user ID is not valid but the dbms_output is blank.No message is getting displayed

--procedure should list all project ID mapped to the active user and on passing --in valid iser id it should print - User is not valid

PROCEDURE get_pid_info (p_return_status_o      OUT VARCHAR2,
                           p_error_message_o      OUT VARCHAR2,
                           p_user_id           IN     VARCHAR2)
   IS
      CURSOR c_pid
      IS
           SELECT DISTINCT xpppa.project_id,
                           papf.person_type_id,
                           xpppp.end_date_active,
                           papf.person_id,
                           COUNT (DISTINCT xpppa.project_id) pid_count
             FROM xxcas_prj_pa_projects_all xpppa,
                  xxcas_prj_pa_project_players xpppp,
                  per_all_people_f papf
            WHERE     xpppa.project_id = xpppp.project_id
                  AND xpppp.person_id = papf.person_id
                  AND papf.person_id = 61--p_user_id
                  AND xpppp.project_role_type = 'PROJECT MANAGER'
                  AND papf.person_type_id = 6
                  and sysdate between xpppa.start_date and nvl(xpppa.completion_date,sysdate+1)
                  and sysdate between xpppp.start_date_active and nvl(xpppp.end_date_active,sysdate+1)
                  AND EXISTS
                         (SELECT 1
                            FROM pa_lookups
                           WHERE     lookup_type = 'XXCAS_PRJ_USER_DETAILS'
                                 AND description=papf.email_address
                                 AND enabled_flag = 'Y'
                                 AND SYSDATE BETWEEN start_date_active
                                                 AND NVL (end_date_active,
                                                          SYSDATE + 1))
         GROUP BY xpppa.project_id,
                  papf.person_type_id,
                  xpppp.end_date_active,
                  papf.person_id;
   BEGIN
      FOR l_rec IN c_pid
      LOOP
         IF l_rec.project_id IS NOT NULL
         THEN
            dbms_output.put_line (
                  'The Projects mapped to the active user ID are : '
               || l_rec.project_id);
         ELSIF l_rec.end_date_active < SYSDATE
         THEN
            dbms_output.put_line (
               'The user has been end dated in the system on : ' || l_rec.end_date_active);
         ELSIF l_rec.pid_count = 0
         THEN
            dbms_output.put_line (
               'There are no projects mapped to the user having PM role');
         ELSE 
            dbms_output.put_line ('Please check the user ID passed');
         END IF;
         IF SQL%NOTFOUND 
         THEN
         dbms_output.put_line('Entered user id is not valid');
         end if;

      END LOOP;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         dbms_output.put_line ('Please provide valid user ID');
      WHEN OTHERS
      THEN
         dbms_output.put_line ('Error in get_pid' || SQLERRM);
   END get_pid_info;

Anonymous Block :

declare
status varchar2(30);
msg varchar2(30);
begin
--DBMS_OUTPUT.ENABLE('20000000'); 
XXCAS_PRJ_PROJECT_DTLS.GET_PID_INFO(status,msg,61);
end; 

Aucun commentaire:

Enregistrer un commentaire