SELECT
  NAME, ADDRESS,PHONE_NO,FAX, EMAIL_ADDRESS,GEO_DIVISION_NAME,GEO_DISTRICT_NAME
             FROM AMS.AGENCY, PMIS.L_GEO_DIVISION, PMIS.L_GEO_DISTRICT
            WHERE     AGENCY.DIVISION_ID = NVL (:P_DIV, L_GEO_DIVISION.GEO_DIVISION_ID)
                  AND AGENCY.DISTRICT_ID = NVL (:P_DIS, L_GEO_DISTRICT.GEO_DISTRICT_ID)
         GROUP BY NAME, ADDRESS,PHONE_NO,FAX, EMAIL_ADDRESS,GEO_DIVISION_NAME,GEO_DISTRICT_NAME
                ORDER BY NAME;     
===================================================================================================== 
SELECT
  NAME, ADDRESS,PHONE_NO,FAX, EMAIL_ADDRESS,GEO_DIVISION_NAME,GEO_DISTRICT_NAME
             FROM AMS.AGENCY, PMIS.L_GEO_DIVISION, PMIS.L_GEO_DISTRICT
            WHERE     AGENCY.DIVISION_ID = L_GEO_DIVISION.GEO_DIVISION_ID
                  AND AGENCY.DISTRICT_ID = L_GEO_DISTRICT.GEO_DISTRICT_ID
                  AND AGENCY.DIVISION_ID = :P_DIV
                  AND AGENCY.DISTRICT_ID = :P_DIS
=======================================================================================================
SELECT ST.NAME,
       AG.NAME AGENCY_NAME,
       ST.ORG_NAME,
       ST.DESIGNATION,
       ST.MOBILE_NO,
       LDV.GEO_DIVISION_NAME,
       LDS.GEO_DISTRICT_NAME
  FROM AMS.STAKEHOLDER ST, AMS.AGENCY AG, PMIS.L_GEO_DIVISION LDV, PMIS.L_GEO_DISTRICT LDS
  WHERE  ST.AGENCY_ID = AG.AGENCY_ID
         AND ST.DIVISION_ID = LDV.GEO_DIVISION_ID
         AND ST.DISTRICT_ID = LDS.GEO_DISTRICT_ID
         --AND ST.DIVISION_ID = :P_DIV
         --AND ST.DISTRICT_ID = :P_DIS    
========================================================================================================
SELECT VI.ACTIVITY_TYPE_ID,
       LAT.ACTIVITY_NAME,
       VI.VISIT_NAME,
       VI.VISIT_COUNTRY_ID,
       LGC.COUNTRY,
       TO_CHAR( VI.VISIT_START, 'DD-MM-YYYY' ) DATE_FROM,
       TO_CHAR( VI.VISIT_END, 'DD-MM-YYYY' ) DATE_TO
  FROM AMS.VISIT_INFO VI, AMS.L_ACTIVITY_TYPE LAT, PMIS.L_GEO_COUNTRY LGC
  WHERE VI.ACTIVITY_TYPE_ID = LAT.ACTIVITY_ID
        AND VI.VISIT_COUNTRY_ID = LGC.COUNTRY_ID
        AND VI.STATUS != 'D';
=========////===============Agency_List====================///===============
SELECT AG.NAME,
       AG.ADDRESS,
       AG.PHONE_NO,
       AG.FAX,
       AG.EMAIL_ADDRESS,
       LD.GEO_DIVISION_NAME,
       LDS.GEO_DISTRICT_NAME
 FROM AMS.AGENCY AG, PMIS.L_GEO_DIVISION LD, PMIS.L_GEO_DISTRICT LDS
 WHERE AG.DIVISION_ID = LD.GEO_DIVISION_ID 
        AND AG.DISTRICT_ID =LDS.GEO_DISTRICT_ID

---------------------Agency-SQL-2----------------------
SELECT ROWNUM SL,
       AG.NAME,
       AG.ADDRESS,
       AG.PHONE_NO,
       AG.FAX,
       AG.EMAIL_ADDRESS,
       LD.GEO_DIVISION_NAME,
       LDS.GEO_DISTRICT_NAME,
      'Printed By: '||INITCAP(:p_printed_by) p_printed_by
 FROM AMS.AGENCY AG, PMIS.L_GEO_DIVISION LD, PMIS.L_GEO_DISTRICT LDS
 WHERE AG.DIVISION_ID = LD.GEO_DIVISION_ID 
        AND AG.DISTRICT_ID =LDS.GEO_DISTRICT_ID 
========////===============Agency_List===========================////============

=======////=================Stackholder_List=====================///==============
SELECT ST.NAME,
       ST.AGENCY_ID,
       'Agency Name: '|| AG.NAME AGENCY_TITLE,
       AG.NAME AGENCY_NAME,
       ST.ORG_NAME,
       ST.DESIGNATION,
       ST.MOBILE_NO,
       LDV.GEO_DIVISION_NAME,
       LDS.GEO_DISTRICT_NAME
  FROM AMS.STAKEHOLDER ST, AMS.AGENCY AG, PMIS.L_GEO_DIVISION LDV, PMIS.L_GEO_DISTRICT LDS
  WHERE  ST.AGENCY_ID = AG.AGENCY_ID(+)
         AND ST.DIVISION_ID = LDV.GEO_DIVISION_ID(+)
         AND ST.DISTRICT_ID = LDS.GEO_DISTRICT_ID(+)
         AND ST.AGENCY_ID =NVL (:p_agency_id,ST.AGENCY_ID);

---------------------STAKEHOLDER-SQL-2----------------------
SELECT ROWNUM SL,
       ST.NAME,
       ST.AGENCY_ID,
       'Agency Name: '|| INITCAP (AG.NAME) AGENCY_TITLE,
       AG.NAME AGENCY_NAME,
       ST.ORG_NAME,
       ST.DESIGNATION,
       ST.MOBILE_NO,
       LDV.GEO_DIVISION_NAME,
       LDS.GEO_DISTRICT_NAME,
       'Printed By: '|| INITCAP(:p_printed_by) p_printed_by
  FROM AMS.STAKEHOLDER ST, AMS.AGENCY AG, PMIS.L_GEO_DIVISION LDV, PMIS.L_GEO_DISTRICT LDS
  WHERE  ST.AGENCY_ID = AG.AGENCY_ID(+)
         AND ST.DIVISION_ID = LDV.GEO_DIVISION_ID(+)
         AND ST.DISTRICT_ID = LDS.GEO_DISTRICT_ID(+)
         AND ST.AGENCY_ID =NVL (:p_agency_id,ST.AGENCY_ID)
==========////==============Stackholder_List=====================///===============  
      
========////=================Chairman_Visit====================////===============
SELECT VI.ACTIVITY_TYPE_ID,
       LAT.ACTIVITY_TYPE,
       VI.VISIT_NAME,
       VI.VISIT_COUNTRY_ID,
       'Visit Type: '|| INITCAP(LAT.ACTIVITY_TYPE) ||', Country: '|| INITCAP(LGC.COUNTRY) C_VISIT_TITLE,
       LGC.COUNTRY,
       VI.VISIT_START,
       VI.VISIT_END,
       TO_CHAR (VI.VISIT_START, 'DD-MM-YYYY') DATE_FROM,
       TO_CHAR (VI.VISIT_END, 'DD-MM-YYYY') DATE_TO
  FROM AMS.VISIT_INFO VI, AMS.L_ACTIVITY_TYPE LAT, PMIS.L_GEO_COUNTRY LGC
 WHERE     VI.ACTIVITY_TYPE_ID = LAT.ACTIVITY_ID(+)
       AND VI.VISIT_COUNTRY_ID = LGC.COUNTRY_ID(+)
       AND VI.STATUS != 'D'
       AND VI.VISIT_COUNTRY_ID = NVL (:p_visit_country_id, VI.VISIT_COUNTRY_ID)
       AND VI.ACTIVITY_TYPE_ID = NVL (:p_activity_type_id, VI.ACTIVITY_TYPE_ID)
       AND trunc (VI.VISIT_START) >= NVL (:p_visit_start, trunc (VI.VISIT_START))
       AND trunc (VI.VISIT_END) <= NVL (:p_visit_end, trunc (VI.VISIT_END ));

---------------------VISIT_INFO-SQL-2----------------------
SELECT ROWNUM SL,
         VI.ACTIVITY_TYPE_ID,
         INITCAP(LAT.ACTIVITY_TYPE) ACTIVITY_TYPE,
         VI.VISIT_NAME,
         VI.VISIT_COUNTRY_ID,
            'Visit Type: '
         || INITCAP (LAT.ACTIVITY_TYPE)
         || ', Country: '
         || INITCAP (LGC.COUNTRY)
            C_VISIT_TITLE,
            'From Date: '
         || TO_CHAR (:p_visit_start, 'dd/mm/yyyy')
         || '  '
         || 'To Date: '
         || TO_CHAR (:p_visit_end, 'dd/mm/yyyy')
            date_range,
         INITCAP(LGC.COUNTRY) COUNTRY,
         VI.VISIT_START,
         VI.VISIT_END,
         TO_CHAR (VI.VISIT_START, 'DD-MM-YYYY') DATE_FROM,
         TO_CHAR (VI.VISIT_END, 'DD-MM-YYYY') DATE_TO,
         (SELECT INITCAP(emp_name) FROM pmis.employee WHERE emp_id = (SELECT emp_id FROM CPA_SECURITY.SEC_USERS WHERE user_id = VI.VISITOR_ID)) visitor_name,
         DECODE (AMS.AMS_CORE_FUNCTION.GET_STATUS(VI.STATUS),'ACTIVE','Approved',INITCAP(AMS.AMS_CORE_FUNCTION.GET_STATUS(VI.STATUS)))STATUS, 
         'Printed By: ' || INITCAP (:p_printed_by) p_printed_by
    FROM AMS.VISIT_INFO VI, AMS.L_ACTIVITY_TYPE LAT, PMIS.L_GEO_COUNTRY LGC
   WHERE     VI.ACTIVITY_TYPE_ID = LAT.ACTIVITY_ID(+)
         AND VI.VISIT_COUNTRY_ID = LGC.COUNTRY_ID(+)
         AND VI.STATUS != 'D'
         AND VI.VISIT_COUNTRY_ID = NVL (:p_visit_country_id, VI.VISIT_COUNTRY_ID)
         AND VI.ACTIVITY_TYPE_ID = NVL (:p_activity_type_id, VI.ACTIVITY_TYPE_ID)
         AND TRUNC (VI.VISIT_START) >= NVL (:p_visit_start, TRUNC (VI.VISIT_START))
         AND TRUNC (VI.VISIT_END) <= NVL (:p_visit_end, TRUNC (VI.VISIT_END))
         AND VI.VISITOR_ID IN (SELECT OPERATOR_FOR_USER_ID FROM AMS.OPERATOR_MAPPING 
                                    WHERE (OPERATOR_USER_ID = :p_authid OR OPERATOR_FOR_USER_ID = :p_authid) GROUP BY OPERATOR_FOR_USER_ID)
ORDER BY  visitor_name asc,DATE_FROM ASC, DATE_TO ASC
=========////===============Chairman_Visit======================////=============

========///================Activity_Type======================////=============
SELECT ACTIVITY_TYPE,
       'Activity Type: ' || INITCAP (ACTIVITY_TYPE) ACTIVITY_TYPE_TITLE,
       ACTIVITY_NAME,
       DESCRIPTION,
       STATUS
  FROM AMS.L_ACTIVITY_TYPE
 WHERE     STATUS != 'D'
       AND ACTIVITY_TYPE = NVL (:p_activity_type, ACTIVITY_TYPE);

------------L_ACTIVITY_TYPE-SQL-1------------
SELECT ROWNUM SL,
       ACTIVITY_TYPE,
       'Activity Type: ' || INITCAP (ACTIVITY_TYPE) ACTIVITY_TYPE_TITLE,
       ACTIVITY_NAME,
       DESCRIPTION,
       STATUS,
      'Printed By: '|| INITCAP(:p_printed_by) p_printed_by
  FROM AMS.L_ACTIVITY_TYPE
 WHERE     STATUS != 'D'
       AND ACTIVITY_TYPE = NVL (:p_activity_type, ACTIVITY_TYPE)
===========///=============Activity_Type===========================///===========

===========///=============Chairman_Meeting======================///=============             
SELECT MI.MEETING_NAME,
       MI.ACTIVITY_ID,
       'Meeting Type: ' || INITCAP (LAT.ACTIVITY_NAME) ACTIVITY_NAME_TITLE,
       LAT.ACTIVITY_NAME,
       TO_CHAR( MI.MEETING_DATE, 'DD/MM/YYYY' ) DATE_FROM,
       TO_CHAR( MI.START_TIME, 'HH24:MI:SS' ) ||'-'|| TO_CHAR( MI.END_TIME, 'HH24:MI:SS' ) TIME,
       MI.MEETING_LOCATION,
       AMS.AMS_CORE_FUNCTION.GET_STATUS(MI.STATUS)STATUS
  FROM AMS.MEETING_INFO MI, AMS.L_ACTIVITY_TYPE LAT
 WHERE MI.ACTIVITY_ID = LAT.ACTIVITY_ID(+)
        --AND MI.MEETING_DATE BETWEEN trunc(:p_start_date) AND trunc(:p_end_date)
        AND trunc (MI.MEETING_DATE) >= NVL (:p_start_date, trunc (MI.MEETING_DATE))
        AND trunc (MI.MEETING_DATE) <= NVL (:p_end_date, trunc (MI.MEETING_DATE ))
        AND MI.ACTIVITY_ID = NVL (:p_activity_id, MI.ACTIVITY_ID);

------------MEETING_INFO-SQL-1------------
SELECT ROWNUM SL,
       INITCAP (MI.MEETING_NAME) MEETING_NAME,
       MI.ACTIVITY_ID,
       'Meeting Type: ' || INITCAP (LAT.ACTIVITY_NAME) ACTIVITY_NAME_TITLE,
       'Form Date: '||to_char(:p_start_date,'dd/mm/yyyy')||'  '||'To Date: '||to_char(:p_end_date,'mm/dd/yyyy') DATE_TITLE,
       INITCAP (LAT.ACTIVITY_NAME) ACTIVITY_NAME,
       TO_CHAR( MI.MEETING_DATE, 'DD/MM/YYYY' ) DATE_FROM,
       TO_CHAR( MI.START_TIME, 'HH24:MI:SS' ) ||'-'|| TO_CHAR( MI.END_TIME, 'HH24:MI:SS' ) TIME,
       MI.MEETING_LOCATION,
       DECODE (AMS.AMS_CORE_FUNCTION.GET_STATUS(MI.STATUS),'ACTIVE','Approved',INITCAP (AMS.AMS_CORE_FUNCTION.GET_STATUS(MI.STATUS))) STATUS,
       (SELECT INITCAP(emp_name) FROM pmis.employee WHERE emp_id = (SELECT emp_id FROM CPA_SECURITY.SEC_USERS WHERE user_id =MI.MEETING_CALLER_USER_ID )) MEETING_CALLER,
      'Printed By: '|| INITCAP(:p_printed_by) p_printed_by
  FROM AMS.MEETING_INFO MI, AMS.L_ACTIVITY_TYPE LAT
 WHERE  MI.STATUS != 'D'
        AND MI.ACTIVITY_ID = LAT.ACTIVITY_ID(+)
        AND MI.MEETING_DATE BETWEEN trunc(:p_start_date) AND trunc(:p_end_date)
        --AND to_char (MI.MEETING_DATE,'mm/dd/yyyy') >=to_char(:p_start_date,'mm/dd/yyyy')
        --AND to_char (MI.MEETING_DATE,'mm/dd/yyyy') <= to_char(:p_end_date,'mm/dd/yyyy')
        AND MI.ACTIVITY_ID = NVL (:p_activity_id, MI.ACTIVITY_ID)
        AND MI.MEETING_CALLER_USER_ID IN (SELECT OPERATOR_FOR_USER_ID FROM AMS.OPERATOR_MAPPING 
                                    WHERE (OPERATOR_USER_ID = :p_authid OR OPERATOR_FOR_USER_ID = :p_authid) GROUP BY OPERATOR_FOR_USER_ID)
ORDER BY MEETING_CALLER ASC,DATE_FROM ASC  
==============///==========Chairman_Meeting==========================///=============

=============///============Chairman_Appointment====================///==============
SELECT AI.APPOINTMENT_NAME,
       'Requester: '|| INITCAP(AI.REQUESTED_BY) REQUESTER_TITLE,
       AI.REQUESTED_BY,
       AI.REQUEST_TYPE_ID,
       LRT.REQUEST_NAME,
       AI.APOINTMNET_START_TIME,
       AI.APOINTMNET_END_TIME,
       TO_CHAR( AI.APOINTMNET_START_TIME, 'DD/MM/YYYY' ) DATE_FROM,
       TO_CHAR( AI.APOINTMNET_START_TIME, 'HH24:MI:SS' ) ||'-'|| TO_CHAR( AI.APOINTMNET_END_TIME, 'HH24:MI:SS' ) TIME_DURATION,
       AMS.AMS_CORE_FUNCTION.GET_STATUS(AI.STATUS)STATUS
  FROM AMS.APPOINTMENT_INFO AI, AMS.L_ACTIVITY_REQ_TYPE LRT
  WHERE AI.REQUEST_TYPE_ID = LRT.REQUEST_ID(+)
        AND AI.STATUS != 'D' 
        AND LOWER(AI.REQUESTED_BY) like '%'||LOWER(:p_requester)||'%' 
        AND trunc (AI.APOINTMNET_START_TIME) >= NVL (:p_apointmnet_start, trunc (AI.APOINTMNET_START_TIME))
        AND trunc (AI.APOINTMNET_END_TIME) <= NVL (:p_apointmnet_end, trunc (AI.APOINTMNET_END_TIME ))
        ORDER BY DATE_FROM desc,TIME_DURATION ASC
  
----------Chairman_Appointment-SQL-1---------------
SELECT AI.APPOINTMENT_NAME,
       --'Requester: '|| INITCAP(AI.REQUESTED_BY) REQUESTER_TITLE,
       AI.REQUESTED_BY,
       AI.REQUEST_TYPE_ID,
       LRT.REQUEST_NAME,
       AI.APOINTMNET_START_TIME,
       AI.APOINTMNET_END_TIME,
       TO_CHAR( AI.APOINTMNET_START_TIME, 'DD/MM/YYYY' ) DATE_FROM,
       'Date: '|| TO_CHAR( AI.APOINTMNET_START_TIME, 'DD/MM/YYYY' ) ||' - '|| TO_CHAR( AI.APOINTMNET_END_TIME, 'DD/MM/YYYY' ) DATE_FROM_TO,
       TO_CHAR( AI.APOINTMNET_START_TIME, 'HH24:MI:SS' ) ||'-'|| TO_CHAR( AI.APOINTMNET_END_TIME, 'HH24:MI:SS' ) TIME_DURATION,
       AI.APPOINTMENT_DURATION ||' '|| INITCAP(DECODE (AI.APPOINTMENT_DURATION_TYPE, 'M', 'MIN','H','HOURS')) APT_DUR,
       AMS.AMS_CORE_FUNCTION.GET_STATUS(AI.STATUS)STATUS
  FROM AMS.APPOINTMENT_INFO AI, AMS.L_ACTIVITY_REQ_TYPE LRT
  WHERE AI.REQUEST_TYPE_ID = LRT.REQUEST_ID(+)
        AND AI.STATUS != 'D' 
        --AND LOWER(AI.REQUESTED_BY) like '%'||LOWER(:p_requester)||'%' 
        AND (trunc (AI.APOINTMNET_START_TIME) >= NVL (:p_apointmnet_start, trunc (AI.APOINTMNET_START_TIME))
        or trunc (AI.APOINTMNET_END_TIME) <= NVL (:p_apointmnet_end, trunc (AI.APOINTMNET_END_TIME )))
        ORDER BY DATE_FROM desc,TIME_DURATION ASC

----------Chairman_Appointment-SQL-2---------------
SELECT ROWNUM SL,
       AI.APPOINTMENT_NAME,
       AI.AGENDA,
       --'Requester: '|| INITCAP(AI.REQUESTED_BY) REQUESTER_TITLE,
       INITCAP (AI.REQUESTED_BY) REQUESTED_BY,
       AI.REQUEST_TYPE_ID,
       INITCAP (LRT.REQUEST_NAME) REQUEST_NAME,
       AI.APOINTMNET_START_TIME,
       AI.APOINTMNET_END_TIME,
       TO_CHAR( AI.APOINTMNET_START_TIME, 'DD/MM/YYYY' ) DATE_FROM,
       (SELECT INITCAP(emp_name) FROM pmis.employee WHERE emp_id = (SELECT emp_id FROM CPA_SECURITY.SEC_USERS WHERE user_id =AI.APPOINTMENT_TO_USER_ID )) APPOINTMENT_FOR,
       'Date: '|| TO_CHAR( :p_apointmnet_start, 'DD/MM/YYYY' ) ||' - '|| TO_CHAR( :p_apointmnet_end, 'DD/MM/YYYY' ) DATE_FROM_TO,
       TO_CHAR( AI.APOINTMNET_START_TIME, 'HH24:MI:SS' ) ||'-'|| TO_CHAR( AI.APOINTMNET_END_TIME, 'HH24:MI:SS' ) TIME_DURATION,
       AI.APPOINTMENT_DURATION ||' '|| INITCAP(DECODE (AI.APPOINTMENT_DURATION_TYPE, 'M', 'MIN','H','HOURS')) APT_DUR,
       --AMS.AMS_CORE_FUNCTION.GET_STATUS(AI.STATUS)STATUS,       
       DECODE (AMS.AMS_CORE_FUNCTION.GET_STATUS(AI.STATUS),'ACTIVE','Approved',INITCAP(AMS.AMS_CORE_FUNCTION.GET_STATUS(AI.STATUS)))STATUS,       
       'Printed By: '|| INITCAP(:p_printed_by) p_printed_by
  FROM AMS.APPOINTMENT_INFO AI, AMS.L_ACTIVITY_REQ_TYPE LRT
  WHERE AI.REQUEST_TYPE_ID = LRT.REQUEST_ID(+)
        AND AI.STATUS != 'D' 
        --AND LOWER(AI.REQUESTED_BY) like '%'||LOWER(:p_requester)||'%' 
        AND trunc (AI.APOINTMNET_START_TIME) >= NVL (:p_apointmnet_start, trunc (AI.APOINTMNET_START_TIME))
        AND trunc (AI.APOINTMNET_END_TIME) <= NVL (:p_apointmnet_end, trunc (AI.APOINTMNET_END_TIME ))
        AND AI.APPOINTMENT_TO_USER_ID IN (SELECT OPERATOR_FOR_USER_ID FROM AMS.OPERATOR_MAPPING 
                                    WHERE (OPERATOR_USER_ID = :p_authid OR OPERATOR_FOR_USER_ID = :p_authid) GROUP BY OPERATOR_FOR_USER_ID)
        order by APPOINTMENT_FOR ASC,AI.APOINTMNET_START_TIME asc,AI.APOINTMNET_END_TIME asc
        --ORDER BY DATE_FROM desc,TIME_DURATION ASC,SL,AI.APOINTMNET_START_TIME, AI.APOINTMNET_END_TIME

=============///============Chairman_Appointment====================///===========================/////========================