One common process in PeopleSoft is to call an App Engine program from PeopleCode. This may be as simple as calling the App Engine from PeopleCode when the page is saved or on a field change event. If you don’t know what an app engine is you may want to learn more about that before […]
Category: Featured
Alter Session Oracle PL/SQL
Change Schema ALTER SESSION can be used with a database has multiple schema owners. Example: a single database instance contains both development and test environments. You need to alter your session in order to be able to access the tables that are not in your default schema. For this example you will need to access the […]
Running Total in PL/SQL
Problem: You need to display a result with a running total in PL/SQL query. The code below will accomplish displaying a running total in PL/SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT C.VENDOR_ID, C.VENDOR_NAME_SHORT, A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID, A.INVOICE_DT, A.GROSS_AMT, SUM(A.GROSS_AMT) OVER (ORDER BY C.VENDOR_ID, A.BUSINESS_UNIT) "RUNNING TOTAL" FROM PS_VOUCHER A, PS_VENDOR C WHERE (A.PROCESS_MAN_CLOSE = 'N' OR A.CLOSE_STATUS <> 'C') AND A.ENTRY_STATUS <> 'X' AND A.VENDOR_SETID = C.SETID AND A.VENDOR_ID = C.VENDOR_ID AND C.VENDOR_ID = '0000000001' GROUP BY C.VENDOR_ID, C.VENDOR_NAME_SHORT, A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID, A.INVOICE_DT, A.GROSS_AMT ORDER BY A.GROSS_AMT, C.VENDOR_ID, A.BUSINESS_UNIT, A.VOUCHER_ID |
This is a pretty simple query to show the details of a vendor balance. The column to be used in calculating the “running total” needs to be listed twice. […]