To get the directory Path

To get the directory Path

select directory_path from dba_directories where directory_name='XXXXXX_EXP' and owner='SYS';

SQR Report stuck at status "Posting"

SQR Report stuck at status "Posting":

Things to Check before Troubleshooting:
================================================

1. Web domain must be up and running(for PSA 9.1).

2. Application Messaging server must be up and running.

Troubleshooting steps:
=================================================

1. Run the below SQL on the particular environment (XXDEV,XXTST,XXACT,XXPRD).

select * from ps_cdm_list where prcsinstance=xxxxxxx; -- modify the xxxxx with process instance number

2. check for OUTPUTDIR from the above query results, showing something like

For PSA 9.1: XXDEV/20140307/262044

For PSA 8: 03190701123F6B2B703362A9140A301269AEB9CF98950C51B28153CB9B35AFF47A44E5875699FAB8B4DFC1197E9E33

3. Go to report repository and check for the above directories.

4. If the file present in the directory,please go ahead and update the status to posted( in PSA 9.1) or Success (in PSA 8).

SQL to update from backend:
----------------------------
PSA 9.1:
============

To update distribution status to posted from posting:

update psprcsque set DISTSTATUS='5' where prcsinstance=418387;
update psprcsrqst set DISTSTATUS='5' where prcsinstance=418387;
update psprcsque set DISTSTATUS='5' where prcsinstance=418387;

COMMIT;


PSA 8:
===========

For PSNT:
----------
update psprcsrqst  set  runstatus= '14' , servernamerun =  'PSNT6'  where prcsinstance in  ('7086041');
update psprcsque   set  runstatus= '14' , servernamerun =  'PSNT6'   where prcsinstance in  ('7086041');
update ps_cdm_list set  diststatus='3', transferinstance= 0   where prcsinstance in  ('7086041');

For PSUNX:
-----------
update psprcsrqst  set  runstatus= '14' , servernamerun =  'PSUNX'  where prcsinstance in  ('8065483');
update psprcsque   set  runstatus= '14' , servernamerun =  'PSUNX'   where prcsinstance in  ('8065483');
update ps_cdm_list set  diststatus='3', transferinstance= 0   where prcsinstance in  ('8065483');

COMMIT;