The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

HP PPM SQLPLUS Report

Highlighted
Sharon Herbst
Occasional Advisor

HP PPM SQLPLUS Report

I have posted another thread on HP Custom reports but wanted to ask a specific question about a custom report we are working on. Here are the details. We are trying to have a report that is scheduled for our users via sqlplus. When creating this report it seems to hang. Any suggetions?

Here is what I have so far:

) PPM Version: 7.1.0 and SP: 5

2) This issue is for all Custom Reports

3) We are trying to build Custom Report in "Report Types". In Commands Section of the Query, "Sql query is placed". But it is not connecting to Sqlplus.

4) We would like to know where we can place the Sql Query.

5) No. It's a new report.

6) This issue for all Users.

7) This issue is on all machines.

8) There is no server log file to it.



Sql Query:

SELECT DISTINCT p.package_id PACKAGE_ID, p.description, p.user_data2 Tower_Approval,e.environment_name,w.workflow_name,

TO_CHAR(MAX(ec.LAST_MIGRATION_DATE),'MM-DD-YYYY') Last_Migrationdate

FROM KDLV_PACKAGE_LINES pl

, KDLV_PACKAGES p

, KENV_ENVIRONMENTS e

, KENV_ENV_CONTENTS ec

, KWFL_WORKFLOWS w

WHERE p.package_id = pl.package_id

AND pl.package_line_id = ec.last_package_line_id

AND e.environment_id IN ('30204')

--AND E.ENVIRONMENT_NAME = &P_ENV_NAME

AND e.environment_id = ec.environment_id

AND w.workflow_id = p.workflow_id

AND p.workflow_id = '30563'

AND p.STATUS_CODE = 'IN_PROGRESS'

GROUP BY p.package_id, p.DESCRIPTION, e.environment_name,ec.last_migration_date, w.workflow_name, p.user_data2

ORDER BY Tower_approval



ksc_simple_respond “sqlplus ppmproddb/ppmhp08@dkfngd





Ksc_simple_respond is the command which connects to sqlplus in HP tool for Object types. But that command is not working for Report type.
1 REPLY
Erik Cole
Honored Contributor

Re: HP PPM SQLPLUS Report

Hi Sharon,

You can place your SQL script in the ITG_HOME/scripts directory, then call it from PPM with a report command such as:

ksc_local_exec [AS.ORACLE_HOME]/bin/[AS.SQLPLUS] [AS.DB_USERNAME]/[AS.DB_PASSWORD]@[AS.DB_CONNECTION_STRING] @./scripts/your_sql_script.sql "[AS.REPORT_DIR]" "[RP.FILENAME]" "[P.P_SHOW_COLUMNS]"

Note the passed parameters at the end of the command should correspond to the fields you define in the report setup.
//Add this to "OnDomLoad" event