DARTS : Identifying Source & Query Use Data

Table or views are built in various ways, so finding the data sources can be challenging.

1. SQL Code:
   All views are based on SQL code. Use all_views function to look at the SQL code.
   
   
2. SQR:
   

   CS: The SQR code is located in S:\STUDENT\SQR\PROD for some tables. Not every table was made by SQR, but here are the samples. The SQR file name should start with 'um'.

   • • umappext.sqr for UM_APPL_EXTRACT - Applicant Reporting Extract
     • umstuarc.sqr for UM_STUD_EXT_ARC - Student Reporting Extract Arch
     • umstuext.sqr for  UM_STUD_EXTRACT - Student Reporting Extract

   FIN: The SQR code is located in S:\FIN\SQR\Prod.

   • • umglrex.sqr for UM_GLQ_REPRTING - Ledger Data
     • umglacta.sqr for UM_GLQ_ACCTA - GL reporting account attribute
       
       

   HR: The SQR code is located in S:\HRMS\SQR\Prod

   • • (Not sure)
   
   
3. Application Engine Program:
   
   
   • UM_STUD_CENSUS - Student Reporting Extract Arch - based on UM_STUD_EXT_ARC and UM_STUD_EXT_ARC is based on UM_STUD_EXTRACT, so look at the codes for UM_STUD_EXTRACT.
   • UM_STUCRSTAB creates PS_UM_D_STD_ENRL table
   
   
4. PeopleCode:

• • Code behind People Soft page where senior stuff change the code. (Ex. UM_GRAD_APPL_HD)
    
    

Find a certain field in SQR:

1. Open  UltraEdit
2. Go to Home> find/replace in files > Find in files
3. Enter field you are looking for, file type (*.SQR), and directory
4. 
5. Click Find

Here are useful tables in PL/SQL Developer when you search for tables, views, fields or data source. Log in PL/SQL Developer in the environment you want to be and explore them.

all_tables

• select * from all_tables gives the list of all tables available in the database
  

all_views

• select * from all_views gives the list of all tables/ views available in the database.

• Click '... '  under 'TEXT' field for the view you want to see. The pop-up window shows all fields in the view and the table(s) that the view is based on.

psrecdefn

• select * from sysadm.psrecdefn gives the short description of table/view, field count, etc. for each table or view. The LASTUPDDTTM is the date that the query was last run.

psrecfield

• psrecfield is useful when you know the field name and want to know which table it is used. 
  For example,  you want to know the table name that contains 'CRSE_ATTR' in the field name. The SQL below gives the list of all tables whose fieldname includes  'CRSE_ATTR'.

select * from sysadm.psrecfield
where fieldname like '%CRSE_ATTR%'

ps_um_psqrydfn_sql (For CS and HR only.)

ps_um_psqrydfn_sql has PS query information. You can do the similar search in PL/SQL Developer when you use 'Advance Search' on PS Query Manager.

• Example 1: If you want to get a list of queries start with "UMS_DTS", the SQL below gives the list.

select * from sysadm.ps_um_psqrydfn_sql
where qryname like 'UMS_DTS%'

• Example 2: If you want to get a list of queries that was created by you, the SQL below gives the list.

select * from sysadm.ps_um_psqrydfn_sql
where createoprid='(your id)'

• Example 3: If you want to get a list of queries that has 'ACAD_PLAN' field, the SQL below gives the list.

select * from sysadm.ps_um_psqrydfn_sql
where sqltext like '%ACAD_PLAN%'

• Example 4: If you want to get a list of queries that has certain table or view (UM_STUD_CENS_VW, for instance),  the SQL below gives the list and click  '... '  under 'UM_DESCR_LONG' field for the query you want to see. The pop-up window shows the list of all tables or views that was used in the query. Also see CLOB Large Data Fields - Retrieving The Data.

select o.oprdefndesc, q.* from sysadm.ps_um_psqrydfn_sql q
left join sysadm.psoprdefn o on q.oprid=o.oprid
where um_descr_long like '%UM_STUD_CENS_VW%'

psqryexpr (For Finance)

We can get PS query information by joining this table to psqrydefn. 

• Example 1: If you want to get a list of queries that is use /*-BASE_BUDGET (expression for BASE_BUDGET based on Discoverer SQL) , the SQL below gives the list.

select * from sysadm.psqrydefn s
left join sysadm.psqryexpr p on (p.qryname = s.qryname )
where dbms_lob.instr(p.expressiontext, '/*-BASE_BUDGET') > 0

pspnlfield

To find page data, use Control -J on PeopleSoft page. This query will provide more information of the page data.

select * from sysadm.pspnlfield
where PNLNAME='UM_PRAXPASSED_INFO'    ← enter page name for PNLNAME

RECNAME on pspnlfield is the table or view name of the field