This query selects for users with available loan types matching those in the new Electronic Promissory Note PeopleSoft Campus Solutions data scheme, in which promossory notes are defined by generic type.

obtainTestUsers.sql

--  This query selects for users with available loan types matching those in the new Electronic Promissory Note 
-- PeopleSoft Campus Solutions data scheme, in which promossory notes are defined by generic type.
--
--  The key to these new loan types are definitions in the PeopleSoft PS_UM_FA_ITM_ADDL table, defining  
-- "loan templates" for promissory notes.  All types of these loans should be supportable via use of the new  
-- EPN ("epn", Electronic Promissory Notes for generic loan types) application.
--
--  Older Perkins and ULoan types are currently supported using the older "epromnote" application, but can be 
-- supported by the new application via updating the data in the PS_UM_FA_ITM_ADDL for a PeopleSoft Campus 
-- Solutions instance.  This should put loan type control back in the hands of the business experts supporting 
-- the Student systems.
--
--  This query pulls back data which the test user needs to submit to the application to complete the 
-- Promissory Note.
--
SELECT note.emplid, 
       email.email_addr email, 
       note.institution, 
       note.aid_year, 
       aid_item_information.um_eprom_tmplt_tp note_type, 
       national_id.national_id ssn, 
       TO_CHAR(person.birthdate,'yyyy-mm-dd') birth_date, 
       note.drivers_license_no, note.driv_lic_state
FROM ps_um_non_dl_pnote note, 
     ps_um_fa_itm_addl aid_item_information, 
     ps_pers_nid national_id, 
     ps_person person, 
     ps_email_addresses email
WHERE aid_item_information.setid = note.institution AND aid_item_information.aid_year = note.aid_year AND aid_item_information.item_type = note.item_type AND 
      national_id.emplid = note.emplid AND 
      person.emplid = note.emplid AND 
      email.emplid = note.emplid AND e_addr_type = 'USTU' AND
      note.ln_pnote_signed ='N' AND
      note.institution = 'UMNTC' AND note.aid_year = '2013' AND aid_item_information.um_eprom_tmplt_tp = 'HPL';