Versions Compared

Old Version 3

changes.mady.by.user Tabassum Lodhi

Saved on

compared with

New Version Current

changes.mady.by.user Tabassum Lodhi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Final Report Delivery with requirements:

The query for for Enrl Stdnt NOCamp Email not PI CU_BAR_SR_00071_1 has been shared with Patrick Ackerman and the query is working fine. 

 

CU_BAR_SR_00071-_1 Prompts are below:  

  • Institution: Bar01 
  • Term: pick any term 

...

Panel
SELECT A.EMPLID, C.EMAIL_ADDR
  FROM PS_STDNT_CAR_TERM A, PS_SCC_EMAIL_QVW C, PS_SCC_EMAIL_QVW D
  WHERE A.INSTITUTION = :1
     AND A.STRM = :2
     AND NOT EXISTS (SELECT B.EMPLID
  FROM PS_STDNT_GRPS_HIST B
  WHERE B.EFFDT BETWEEN TO_DATE('2015-05-30','YYYY-MM-DD') AND TO_DATE('2015-08-14','YYYY-MM-DD')
     AND A.EMPLID = B.EMPLID
     AND A.INSTITUTION = B.INSTITUTION
     AND B.EFF_STATUS = 'A'
     AND substr( B.STDNT_GROUP,1,2) IN ('PI'))
     AND A.UNT_TAKEN_PRGRSS > 0
     AND A.EMPLID =  C.EMPLID (+)
     AND 'CAMP' =  C.E_ADDR_TYPE (+)
     AND A.EMPLID =  D.EMPLID  (+)
     AND 'OTHR' =  D.E_ADDR_TYPE (+)
     AND ( NOT EXISTS (SELECT E.EMPLID
  FROM PS_SCC_EMAIL_QVW E
  WHERE E.EMPLID = A.EMPLID
     AND E.E_ADDR_TYPE = 'CAMP')
     OR EXISTS (SELECT F.EMPLID
  FROM PS_SCC_EMAIL_QVW F
  WHERE F.EMPLID = A.EMPLID
     AND F.E_ADDR_TYPE = 'CAMP'
     AND lower ( F.EMAIL_ADDR) NOT LIKE '%@baruchmail%'))

Future Goal:

To add another prompt called 'Effective Date From and Effective Date To' though STDNT_GRP_HIST record in the query.  In this is case it will help the query to retrieve data within a certain range of date.

Modification:

The modified version name DATE_CU_BAR_SR_00071_1 which has been shared with Patrick.  Now waiting for a response from him.  Please follow the link for CU_BAR_SR_00071_2

Panel

Patrick,

The query DATE_CU_BAR_SR_00071_1 has been copied to you.  Please verify and confirm me the data.   I have checked it and looks fine to me.  The effective dates as prompt have been added to view.  I will change the query name once you confirm.  Thank you.

Panel

The new query CU_BAR_SR_00071_2 has been copied to you as per the requirements have been provided.

  • Definition has been updated as ‘ dates are for active permit in student group’.
  • Modification of  the prompts have been added and labeled the To and From fields as “PI Stu Grp Active - Beg Date” and “PI Stu Grp Active - End Date”.

The total 129 rows return as of today based on effective date from May 30, 2015 to August 14, 2015.