PS Query & SQL

Hello Friends,

We are working on a project where the student account activity details (from the student center) need to be shared with a third-party vendor so they can replicate this detailed summary in their system. 

I am very new to writing queries for student financials and have been diving into the records and data over the last couple of days. I know that I need to use ITEM_SF and ITEM_LINE_SF, and I can see the matching data (Posted Date, Item, Term, Charge, Payment, Refund). 

The struggle now becomes how to align the information to the appropriate column, for "Charge", "Payment", "Refund." My experience in PS tells me that there must be some type of code or identifier that I need to tie in to determine how these amounts should apply to the columns. But I don't know where to start with this.

Any guidance is appreciated.

SELECT TO_CHAR(B.POSTED_DATE,'YYYY-MM-DD'), B.DESCR, B.ITEM_TERM, B.LINE_AMT
  FROM PS_ITEM_SF A, PS_ITEM_LINE_SF B
  WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT
     AND A.COMMON_ID = B.COMMON_ID
     AND A.ITEM_NBR = B.ITEM_NBR
     AND B.EMPLID = '12345678'
     AND B.ITEM_TERM = '1263')
  ORDER BY 1 DESC

------------------------------
Lidia Anderson
Manager, Campus Solutions
Central Washington University
lidia.anderson@cwu.edu
------------------------------

Good morning Lidia,

I'm not sure if your question was just about criteria for determining what the B.LINE_AMT "type" was for each row (Charge, Payment, Refund), or if it was also about report formatting to match the needs of your vendor. I can't help with the first, as I only work in HCM (on the employee payroll/benefits side of Oracle). 

But I can offer some help if your question is about formatting the report for a vendor.

For fields that return a value whose purpose or meaning is determined by another field value (ie. a $$$ amount that corresponds to a specific type of Charge or Earn_Code, etc), where you cannot simply add a "Description" column, you can use the field in a CASE expression and return that expression as a field.

For example:

(CASE

WHEN C.ITEM_TYPE_CD = 'P'

THEN B.LINE_AMT

ELSE ' '

END)

AS Payment

When used as a field, this expression would result in a column titled "Payment" where the $$$ amounts are only payments. You would then create additional expressions where C.ITEM_TYPE_CD was equal to C, R, etc. (assuming that's correct) and use those as fields to create their respective columns. I don't know the SQL for setting the field type or width for an expression since I normally work in PSQuery. But I'm sure someone else in the community can help with that part if you need it.

Hope this helps,

Scott Frey

Senior Data Analyst-HRIS, Employee Services

University of Colorado

1800 Grant Street, Suite 400

Denver, CO  80203

t  303 860 4200

Scott.Frey@cu.edu
www.cu.edu

------------------------------
Scott Frey
Senior Data Analyst - Human Resource Information Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------