PeopleSoft Tips and Tricks

CFO tool https://apexapps.oracle.com/pls/apex/f?p=10319:18:0

SELECT * FROM PSACCESSLOG A WHERE EXISTS (SELECT 'X'                   FROM PS_EM_VNDR_USR_SRC EM, PSOPRALIAS OPR                   WHERE EM.OPRID = OPR.OPRID                   AND A.OPRID = EM.OPRID                   AND OPR.OPRALIASTYPE = 'VND') ;

SELECT * FROM PS_ECQUEUE; SELECT * FROM SYSADM.PS_ECQUEUEINST; SELECT * FROM PS_ECTRANS; SELECT * FROM PS_ECTPPROFILE; SELECT * FROM PS_ECTRANSGROUPLOG; SELECT * FROM PS_ECTRANSGROUP_EC; SELECT * FROM PS_ECBUSDOCHDR; SELECT * FROM PS_ECBUSDOCDATA; SELECT * FROM PS_ECBUSDOCDTL;

Below SQL will give the details in a Message Log entry for a specific process or list of processes ran between the days. PS_MESSAGE_LOG -- Stores the Log Header Data for Specific Process Instance ID PS_MESSAGE_LOGPARM  -- Stores all the Details Data for a specific Process Instance Id. Below SQL will help you to write your own SQLs to get the Log Data for a specific process. Use NVL in the Sub Selects to avoid error in the SQL if there are no rows returning from the Sub Select. SELECT DISTINCT MLOG.PROCESS_INSTANCE , MLOG.MESSAGE_SEQ ,(SELECT LPRM1.MESSAGE_PARM FROM PS_MESSAGE_LOGPARM LPRM1 WHERE LPRM1.PROCESS_INSTANCE = LPRM.PROCESS_INSTANCE AND LPRM1.MESSAGE_SEQ = LPRM.MESSAGE_SEQ AND LPRM1.PARM_SEQ = '1') AS SETID ,(SELECT LPRM1.MESSAGE_PARM FROM PS_MESSAGE_LOGPARM LPRM1 WHERE LPRM1.PROCESS_INSTANCE = LPRM.PROCESS_INSTANCE AND LPRM1.MESSAGE_SEQ = LPRM.MESSAGE_SEQ AND LPRM1.PARM_SEQ = '2') AS VENDOR_ID ,(SELECT LPRM1.MESSAGE_PARM FROM PS_MESSAGE_LOGPARM...

http://docs.oracle.com/cd/E17566_01/epm91pbr0/eng/psbooks/psft_homepage.htm

Completions Pending Putaway = 1) Difference between the quantity (field COMPLETED_QTY) in PS_SF_OUTPUT_LIST for an Item by Production ID where MG_OUTPUT_TYPE = CP (Primary Item) or CS (Secondary Co-Product) and the sum of the quantities (field QTY_BASE) in PS_TRANSACTION_INV for the Item by Production ID for the Transaction Groups 021 (Receipts from production) and 221 (Route to Production Kit). + 2) Quantity (field COMPLETED_QTY) in PS_SF_OUTPUT_LIST for the Item by Production ID if the Production ID's are not found in PS_TRANSACTION_INV for the Transaction Groups 021 (Receipts from production) and 221 (Route to Production Kit). Part 1 of the above formula is done by running a query against the View PS_CM_NP_PID_VW and part 2 against the View PS_CM_NP_PID2_VW. So in order to check where the problem is, just run the following two queries: SELECT COUNT(*) FROM PS_CM_NP_PID_VW WHERE BUSINESS_UNIT = 'Your Business Unit' AND CM_BOOK = 'Your Cost Book'; SE...

Attached spreadhsheet will help format the data into a Fixed Length File Format and Export into a TXT file to use it for EDI Upload. Produce Fixed Length File Format Spreadsheet Please reach out to me for any questions and suggestions for improvement.