Process Definition File Dependency

This is a post from my old kbenson.net site.  In my blogger stats I have seen some traffic being redirected from this old post. I pulled the page back from Google cache, so it is mostly the same. However the screenshots are missing; I will attempt to recreate them soon. so I had to recreate them.

---

I had a head banger on a simple issue today.  It took a few hours to realize what the issue was, turned out to be way too obvious.  In PeopleSoft process scheduler we have setup some Process Definitions with a File Dependency turned on.  What this does is Block a process from running until a file is found.  This is setup in the Process Definition on the Process Definition Options tab under the On File Creation section.

Well today we updated the Wait for File path.  We rescheduled our processes but in the Parameters it still had the old file path.  After some head scratching I looked in PeopleBooks where it says this file path can be set at runtime.   So I looked and sure enough this value is stored on each Run Control Id.  It will only pull from the Process Definition if it is a new Run Control Id or if the filepath is blank.

Here is some SQL to help with this situation

• Process Sched Parameters
• SELECT prcsinstance,
         prcsfilename
  FROM   psprcsparms
  ORDER  BY prcsinstance DESC 
• Stored at Process Defn Level
• SELECT prcsname,
         prcsfilename
  FROM   ps_prcsdefn
  WHERE  prcsname LIKE 'TEST123' 
• Stored at Run Control Level
• SELECT a.prcsfilename,
         a.*
  FROM   ps_prcsruncntldtl a
  WHERE  a.prcsname LIKE 'TEST123'

• Update Run Control to match current Process Definition
• UPDATE ps_prcsruncntldtl a
  SET    a.prcsfilename = (SELECT c.prcsfilename
                           FROM   ps_prcsdefn c
                           WHERE  a.prcstype = c.prcstype
                                  AND a.prcsname = c.prcsname)
  WHERE  a.prcstype = 'Application Engine'
         AND a.prcsname = 'TEST123'
         AND EXISTS (SELECT 'X'
                     FROM   ps_prcsdefn b
                     WHERE  a.prcstype = b.prcstype
                            AND a.prcsname = b.prcsname)

PeopleSoft Expense Report : Status stuck in "Submission in Progress"

Today I returned to the office after being at Oracle OpenWorld all last week.  After I caught up on emails, a co-worker came to me with an issue the Training team was seeing in our Training environment.  This environment gets refreshed every weekend, so there a normally very few issues. However, the past few weeks they were having issues submitting Expense report sheets for approval.  When the user would submit, the status would change to "Submission in Process" and wouldn't move forward in the approval process.  The sheet would show no approvers and the expected approvers had no new work in their worklist.

They all assumed some AWE or Security configuration was missing or messed up.  This didn't make sense to me, since the environment was being refreshed weekly and working fine up to this point.  That being said, I still looked there for a solution, as I had no other ideas.  Everything seemed to look OK, matching our production environment for the most part.  I then remembered that Expense approvals use a Service Operation!  I went to Monitor Asynchronous Servives for Integration Broker, and sure enough there were multiple EX_APPROVAL messages waiting in a status of New.

It turns out, for whatever reason the Integration Broker Pub\Sub server wasn't being brought up.  Once an admin ensured IB was up and running, we started seeing the EX_APPROVAL messages flow to Done.  All the previous sheets in status  "Submission in Process" were now "Submitted".  These sheets also showed in the manager's worklist, ready for approval.  To anyone familiar with Expenses this would probably be a first step in troubleshooting, but I figured I would write this up just in case others are new to the module like I am.

For more information, I would recommend reviewing this Doc in My Oracle Support: How does the Expenses Approval Workflow work and is setup in FSCM 8.9, 9.0, and 9.1? ID 650548.1.