EMPLIDPrompt Table: PEOPLE_SRCH
INSTITUTIONPrompt Table: INSTITUTION_TBL
FROM_TERMTO_TERMITEM_TYPESSR_VB_FYRPAYVW(SQL View) |
Index Back |
|---|---|
Federal Payments/AdjustmentsFederal Yellow Ribbon Payments/Adjustments View |
| SELECT sf.COMMON_ID emplid , bu_sf.INSTITUTION , pay_hist.FROM_TERM , pay_hist.TO_TERM , sf.ITEM_TYPE , sf.ITEM_TERM , sf.ACCOUNT_NBR , sf.ITEM_EFFECTIVE_DT , sf.ITEM_AMT FROM %Table(ITEM_SF) sf , %Table(SSR_VB_PAY_HIST) pay_hist , %Table(BUS_UNIT_TBL_SF) bu_sf WHERE sf.COMMON_ID = pay_hist.EMPLID AND sf.BUSINESS_UNIT = bu_sf.BUSINESS_UNIT AND pay_hist.INSTITUTION = ( SELECT INSTITUTION FROM %Table(BUS_UNIT_TBL_SF) bus_unit WHERE bus_unit.BUSINESS_UNIT= sf.BUSINESS_UNIT ) AND sf.SA_ID_TYPE = 'P' AND (sf.ITEM_TERM IN ( SELECT term_tbl.STRM FROM %Table(TERM_TBL) term_tbl WHERE term_tbl.TERM_BEGIN_DT >= ( SELECT MIN(c.TERM_BEGIN_DT) FROM PS_TERM_TBL c WHERE c.strm = pay_hist.FROM_TERM AND c.INSTITUTION =pay_hist.INSTITUTION AND (c.ACAD_CAREER = sf.ACAD_CAREER OR sf.ACAD_CAREER = ' '))) AND (sf.ITEM_TERM IN ( SELECT term_tbl.STRM FROM %Table(TERM_TBL) term_tbl WHERE term_tbl.TERM_END_DT <= ( SELECT MAX(c.TERM_END_DT) FROM PS_TERM_TBL c WHERE c.strm = pay_hist.TO_TERM AND c.INSTITUTION =pay_hist.INSTITUTION AND (c.ACAD_CAREER = sf.ACAD_CAREER OR sf.ACAD_CAREER = ' ')) ) )) AND EXISTS( SELECT 1 FROM %Table(SSR_VB_ITMGRPVW) grp WHERE grp.ITEM_TYPE = sf.ITEM_TYPE AND grp.ITEM_TYPE_GROUP IN ( SELECT map.ITEM_TYPE_GROUP FROM %Table(SSR_VB_PAY_MAP) map WHERE map.INSTITUTION=( SELECT INSTITUTION FROM %Table(BUS_UNIT_TBL_SF) bus_unit WHERE bus_unit.BUSINESS_UNIT= sf.BUSINESS_UNIT ) AND map.SSR_VB_YEL_RIBBON='Y')) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PEOPLE_SRCH |
| 2 | INSTITUTION |
Character(5) | VARCHAR2(5) NOT NULL |
Academic Institution
Prompt Table: INSTITUTION_TBL |
| 3 | FROM_TERM |
Character(4) | VARCHAR2(4) NOT NULL | From Term |
| 4 | TO_TERM |
Character(4) | VARCHAR2(4) NOT NULL | To Term |
| 5 | ITEM_TYPE |
Character(12) | VARCHAR2(12) NOT NULL | Item Type |
| 6 | ITEM_TERM | Character(4) | VARCHAR2(4) NOT NULL | Item Term |
| 7 | ACCOUNT_NBR | Character(10) | VARCHAR2(10) NOT NULL | Account Nbr |
| 8 | ITEM_EFFECTIVE_DT | Date(10) | DATE | Item Effective Date |
| 9 | ITEM_AMT | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | Item Amount |