Deploying the latest Peoplesoft image (HCM92002, Peopletools 8.53.06), and you’ll see a lot of reports not posted: 
Well, fine, they’re probably not that important, but it can be fixed quite easily especially since they are not posted for one single reason, the process scheduler was started before the PIA during the deployment.
More generally, if you have a bunch of reports which have a non-posted content for a problem you fixed, you may want to avoid to go in every single process just to click “re-send Content” as below: 
Going to all and every process is tedious, so let’s try to do it with back-end statements.
First, what this “Re-send Content” is doing ? Set the AppServer in trace mode (Trace SQL=7), and we’ll see all what we need to know:
<…>
PSAPPSRV.12845 (17) 1-1286 12.04.39 0.000045 Cur#2.12845.HR92DM02 RC=0 Dur=0.000018 COM Stmt=UPDATE PSPRCSRQST SET DISTSTATUS = :1 WHERE PRCSINSTANCE = :2
PSAPPSRV.12845 (17) 1-1287 12.04.39 0.000005 Cur#2.12845.HR92DM02 RC=0 Dur=0.000000 Bind-1 type=2 length=1 value=7
PSAPPSRV.12845 (17) 1-1288 12.04.39 0.000003 Cur#2.12845.HR92DM02 RC=0 Dur=0.000000 Bind-2 type=8 length=4 value=2563
PSAPPSRV.12845 (17) 1-1289 12.04.39 0.000879 Cur#2.12845.HR92DM02 RC=0 Dur=0.000019 COM Stmt=UPDATE PSPRCSQUE SET DISTSTATUS = :1 WHERE PRCSINSTANCE = :2
PSAPPSRV.12845 (17) 1-1290 12.04.39 0.000005 Cur#2.12845.HR92DM02 RC=0 Dur=0.000001 Bind-1 type=2 length=1 value=7
PSAPPSRV.12845 (17) 1-1291 12.04.39 0.000003 Cur#2.12845.HR92DM02 RC=0 Dur=0.000000 Bind-2 type=8 length=4 value=2563
PSAPPSRV.12845 (17) 1-1292 12.04.39 0.000321 Cur#2.12845.HR92DM02 RC=0 Dur=0.000022 COM Stmt=UPDATE PS_CDM_LIST SET DISTSTATUS = '8',TRANSFERINSTANCE = 0 WHERE PRCSINSTANCE = :1 AND DISTSTATUS NOT in ('5', '9')
PSAPPSRV.12845 (17) 1-1293 12.04.39 0.000004 Cur#2.12845.HR92DM02 RC=0 Dur=0.000001 Bind-1 type=8 length=4 value=2563
PSAPPSRV.12845 (17) 1-1294 12.04.39 0.010821 Cur#2.12845.HR92DM02 RC=0 Dur=0.009838 Commit
PSAPPSRV.12845 (17) 1-1295 12.04.39 0.000032 Cur#2.12845.HR92DM02 RC=0 Dur=0.000020 Disconnect
PSAPPSRV.12845 (17) 1-1296 12.04.39 0.000819 Cur#1.12845.HR92DM02 RC=0 Dur=0.000000 Commit
PSAPPSRV.12845 (17) 1-1297 12.04.39 0.000007 Cur#1.12845.HR92DM02 RC=0 Dur=0.000004 Disconnect
PSAPPSRV.12845 (17) 1-1298 12.04.39 0.004512 Cur#1.12845.notSamTran RC=0 Dur=0.000033 Open Cursor Handle=0000000002429BE0
<…>
Actually 3 tables are involved: PSPRCSRQST, PSPRCSQUE and PS_CDM_LIST. We just have to update them properly as indicated in the trace file above to make the process scheduler a try to repost.
From the given below screenshot, 9 reports are not posted: 
And from the back-end, the same:
SQL> select PRCSINSTANCE from PSPRCSQUE where DISTSTATUS=4 order by 1 desc;
PRCSINSTANCE
------------
2561
2560
2559
2558
2557
2556
2550
2549
2548
9 rows selected.
SQL> select PRCSINSTANCE from PS_CDM_LIST where DISTSTATUS NOT in ('5', '9');
PRCSINSTANCE
------------
2548
2550
2549
2556
2557
2558
2559
2560
2561
9 rows selected.
SQL> select PRCSINSTANCE from PS_CDM_LIST where DISTSTATUS NOT in ('5', '9') order by 1 desc;
PRCSINSTANCE
------------
2561
2560
2559
2558
2557
2556
2550
2549
2548
9 rows selected.
SQL>
Then update these tables:
SQL> UPDATE PSPRCSRQST SET DISTSTATUS = 7 where DISTSTATUS=4 ;
9 rows updated.
SQL> UPDATE PSPRCSQUE SET DISTSTATUS = 7 WHERE DISTSTATUS=4 ;
9 rows updated.
SQL> UPDATE PS_CDM_LIST SET DISTSTATUS = '8',TRANSFERINSTANCE = 0 WHERE DISTSTATUS NOT in ('5', '9');
9 rows updated.
SQL> commit;
Commit complete.
SQL>
And finally, from the front-end, all are now “posted”:
And the reports are all reachable: 
Of course, it won’t solve a posting problem if there is, it just tries to repost. But in such a case, useful to keep it in mind.
Nicolas.
5 comments:
I have a slightly different script to you.
insert into PSPRCSJOBSTATUS
select PRCSINSTANCE, PRCSINSTANCE, PRCSINSTANCE, 0, 7, 1, ' ', 0
from PSPRCSQUE
where DISTSTATUS = 4
and JOBINSTANCE = PRCSINSTANCE;
update PS_CDM_LIST
set DISTSTATUS = '8'
, TRANSFERINSTANCE = 0
where DISTSTATUS = 4
and PRCSINSTANCE in (
select PRCSINSTANCE from PSPRCSQUE
where DISTSTATUS = 4);
update PSPRCSRQST
set DISTSTATUS = 7
where DISTSTATUS = 4
and exists (
select 1 from PS_CDM_LIST C
where C.PRCSINSTANCE = PSPRCSRQST.PRCSINSTANCE);
update PSPRCSQUE
set DISTSTATUS = 7
where DISTSTATUS = 4
and exists (
select 1 from PS_CDM_LIST C
where C.PRCSINSTANCE = PSPRCSQUE.PRCSINSTANCE);
This only updates the Dist Status if the content has not been removed, and also accounts for Jobs.
I have a feature in my Chrome/Firefox extension that has a 1 click repost on the Process Monitor page. In the past I had a one click option to repost all unposted content which has yet to be reimplemented.
http://www.pschrome.com
Can some one tell me what ii the role of PSPRCQUE. I don't find much information about it when i look at the Process Scheduler Architecture they mentioned only about the PSPRCSRQST table.
Hi - if I hit re-send content on the JOb, its starting another process within in the JOb. is that normal? I thought re-send content is strictly used for posting reports to report repository.
thanks
Kumar
Nice shhare
Post a Comment