PeopleSoft Tips and Tricks

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...