soapUI Error when loading WSDL - SAD_ADMISSIONS.1.wsdl or SCC_USERREG.1.wsdl - The value '' is an invalid name

Please reference My Oracle Support KM doc for workaround:   
AAWS - After Applying CS 9 Bundle 24, soapUI 4.0.1 Generates Error - The value '' is an invalid name (Doc ID 1419303.1)

This issue has been reported in Bug 13739421.

Workaround

1. Navigate to Set Up SACR > System Administration > Entity > Entity Registry.  Retrieve the General Materials Attachment, check Embed, then save.
2. Select Return to Search and retrieve Work Experience Attachment.  Check Embed, then save.
3. Select Return to Search and retrieve Constituent.  Click Generate XSD, make a copy of the XSD or save to your desktop for reference.

Review the schema for SCC_ENTITY_CONSTITUENT, as it will not be updated until a save. Test the new XSD using soapUI via the XSD downloaded. This should work as one option for testing. Do the same test using the WSDL URL from Provide Web Service. Edit schema for SCC_ENTITY_CONSTITUENT, delete the schema and copy the new schema (XSD) in. Re-test and the URL should now work for soapUI 4.0.1.

AAWS (Admissions Application Web Service) Sample XML - SCC_LOV / SCC_GET_LOV

Here is a great soapUI sample request message for SCC_LOV.

NOTE:  You may need to enter in a different Username and Password for your environment.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://xmlns.oracle.com/Enterprise/HCM/services"> 
   <soapenv:Header xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <wsse:Security soap:mustUnderstand="1" 
     xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" 
     xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
     <wsse:UsernameToken>
                 <wsse:Username>PS</wsse:Username>
                 <wsse:Password>PS</wsse:Password>
     </wsse:UsernameToken>
   </wsse:Security>
   </soapenv:Header>  
   <soapenv:Body>
      <ser:SCC_LOV_REQ>
         <ser:LOVS>
            <ser:LOV name="?">
               <ser:FIELDNAME>INSTITUTION</ser:FIELDNAME>
               <ser:RECORDNAME>ACAD_PROG_TBL</ser:RECORDNAME>
               <ser:LOVCONTEXT></ser:LOVCONTEXT>
               <ser:KEYS>
                  <!--1 or more repetitions:-->
                  <ser:KEY>
                     <ser:FIELDNAME></ser:FIELDNAME>
                     <ser:FIELDVALUE></ser:FIELDVALUE>
                  </ser:KEY>
               </ser:KEYS>
            </ser:LOV>
         </ser:LOVS>
      </ser:SCC_LOV_REQ>
   </soapenv:Body>
</soapenv:Envelope>

SQL to find duplicates - Sample PS_PERS_NID

Here is a sample SQL of how one could find multiple rows.

Example: Using HAVING count(*) > 1

SELECT A.EMPLID,
COUNT(A.EMPLID) AS NumOccurrences
FROM PS_PERS_NID A
GROUP BY A.EMPLID
HAVING ( COUNT(A.EMPLID) > 1 )

All rows within PS_PERS_NID should have a primary row.
Adding primary

SELECT A.EMPLID,
COUNT(A.EMPLID) AS NumOccurrences
FROM PS_PERS_NID A
WHERE A.PRIMARY_NID = 'Y'
GROUP BY A.EMPLID
HAVING ( COUNT(A.EMPLID) > 1 )

Campus Solutions - Row level Security Components

Here is a sample SQL that will provide the components for CS Row Level security.  You can then reference the records within App Desginer and create *.dms scripts for exporting the user security.


SELECT a.portal_name,
       e.portal_label    AS parent4_folder,
       d.portal_label    AS parent3_folder,
       c.portal_label    AS parent2_folder,
       b.portal_label    AS parent_folder,
       a.portal_label    AS component,
       a.portal_uri_seg2 AS component_dbname
FROM   psprsmdefn a
       left join psprsmdefn b
         ON b.portal_name = a.portal_name
            AND b.portal_objname = a.portal_prntobjname
       left join psprsmdefn c
         ON c.portal_name = b.portal_name
            AND c.portal_objname = b.portal_prntobjname
       left join psprsmdefn d
         ON d.portal_name = c.portal_name
            AND d.portal_objname = c.portal_prntobjname
       left join psprsmdefn e
         ON e.portal_name = d.portal_name
            AND e.portal_objname = d.portal_prntobjname
WHERE  a.portal_reftype = 'C'
       AND e.portal_label LIKE 'Set Up SACR'
       AND d.portal_label LIKE 'Security';

Please also review this posting from PeoplesoftTipster.com

http://peoplesofttipster.com/2007/04/12/moving-peoplesoft-config-between-environments/#more-15

There are a number of tools you could use to achieve this, be it data mover, raw SQL or even an SQR or App Engine. All of these would need to be custom written. An alternative solution is to use the Manage Configuration Set functionality. It’s a delivered component (at least on more recent versions of the Application) that lets you choose the modules you wish to export config for, and it builds the DMS files (Data Mover Scripts) for you.

Sample Navigation:

http://<name:port>/psp/<environment>/EMPLOYEE/HRMS/c/EOLT_IMPLEMENTATION.EOLT_CONFIG_OPT.GBL
 
or go to PeopleTools > Portal > Structure and Content, drill down into Enterprise Components and edit Manage Implementations to not hide it from portal navigation (checkbox on first page).

Find Navigation Path or BreadCrumbs for a Component in Peoplesoft / Oracle

There have been some great blog posts for this type of query.  Here are a few that I like

From CompShack - http://www.compshack.com/breadcrumb/breadcrumb-sql-to-find-navigation-path-for-component-pia

From Peoplesoft Tipster - http://peoplesofttipster.com/2007/12/09/portal-navigation/


/* Provide navigation location for component */
SELECT a.portal_name,
       e.portal_label AS parent4_folder,
       d.portal_label AS parent3_folder,
       c.portal_label AS parent2_folder,
       b.portal_label AS parent_folder,
       a.portal_label AS component
FROM   psprsmdefn a
       left join psprsmdefn b
         ON b.portal_name = a.portal_name
            AND b.portal_objname = a.portal_prntobjname
       left join psprsmdefn c
         ON c.portal_name = b.portal_name
            AND c.portal_objname = b.portal_prntobjname
       left join psprsmdefn d
         ON d.portal_name = c.portal_name
            AND d.portal_objname = c.portal_prntobjname
       left join psprsmdefn e
         ON e.portal_name = d.portal_name
            AND e.portal_objname = d.portal_prntobjname
WHERE  a.portal_reftype = 'C'
       -- Parameter to set:  (In this case I used the component - ADM_APPL_MAINTNCE)
       AND a.portal_uri_seg2 = 'ADM_APPL_MAINTNCE' 

Alliance 2012

Looking forward to attending and presenting at the HEUG Alliance Conference 2012 in Nashville!

Here is the technical track session I will be presenting with a great colleague Andrew Cesario.

Wed, Mar 21, 2012 (09:15 AM - 10:15 AM)

     Technical

30009:Campus Solutions AAWS: A Technical Overview

Why are my Request Messages in Latin? Using SOAPUI

I have been scratching my head on this one for days and it was a simple setting.  I did change the value many times but didn't realize that I needed to reload the WSDL definition for the changes to take effect.  :-)

The sample values (in Latin) are created because of the soapUI Preferences.
If you dislike having these sample values or having to adjust them by hand, you can turn them off by ticking off WSDL Settings->Sample Values checkbox. When you add the WSDL again, the new sample requests will have no sample values and just a question mark. "? "

Settings:  Make sure this is unchecked.

 

When you uncheck the setting, you will have just the ? mark.

PeopleTools Table Reference

Here is a great Peopletools reference for delivered tools tables.

http://www.go-faster.co.uk/peopletools/#PT

AAWS - Admissions Application Web Services - Info

My Oracle Support KM docs to review: 

CS 9.0 Bundle #21 Functional Documentation and Additional Features April 2011 (Doc ID 1314754.1)

Specific files to download:
1. CS_Bundle_21_Manage_Admission_Transactions.pdf (1.85 MB)
2. AAWS Users Guide.pdf (541 KB)
3. AAWS Developers Guide.pdf (1.11 MB)
 
Within Oracle/Peoplesoft there are 9 different web services provided with AAWS and 2 for Payment Processing.

There are six services for Application Processing:

• Create Application - SAD_CREATEAPPL
• Get Application - SAD_GETAPPL
• Get Application(s) - SAD_GETAPPLS
• Get Attachment - SAD_GETATTACH
• Save Application - SAD_SAVEAPPL
• Submit Application - SAD_SUBMITAPPL

There are three for User Registration:

• User Authentication - SCC_USERREG_AUTHENTICATE
• User Registration - SCC_USERREG_CREATEACCT
• Get a List of Values - SCC_GET_LOV

JDBI - Making Clean APIs without enbedded SQL

I found this article and information interesting.  http://jdbi.org/  and http://jdbi.org/getting_jdbi/

Not having to embed SQL directly into Java.  By using this, one could create a packaged stored procedure, which would then make a cleaner API.

SQL Formatter

I have used this website for many years now.  It is a great site to format SQL and Text.  Check it out!

http://www.dpriver.com/pp/sqlformat.htm

Book Review - Oracle Peoplesoft Enterprise Financial Management 9.1 Implementation

  I have started to read and review this reference on Oracle Peoplesoft Enterprise Financial Management 9.1 Implementation.  The preliminary view thus far is optimistic and it appears to be a great reference for understanding Financial Management.  The author provides some outstanding screen shots and functional design items to reference.  The book's layout is organized and has great flow between chapters.



The functional and technical items covered are as follows listed by Chapter:

• Chapter 1 - PeopleSoft Financials Fundamentals 
• Chapter 2 - PeopleSoft Security
• Chapter 3: PeopleSoft Billing Module
• Chapter 4: PeopleSoft Accounts Receivable Module
• Chapter 5: PeopleSoft Asset Management Module
• Chapter 6: PeopleSoft Accounts Payable Module
• Chapter 7: PeopleSoft General Ledger Module
• Chapter 8: PeopleSoft Expenses Module
• Chapter 9: PeopleSoft Commitment Control

Book Link to Publisher - http://link.packtpub.com/GnLy0X

Campus Solutions / HCM Split documentation - List of KM docs on My Oracle Support

Great listing of Knowledge Management docs to review within My Oracle Support concerning the CS/HCM Split. (Campus Solutions Split from HCM)

http://support.oracle.com


CS/HR Split

• Campus Solutions to HCM Integration:  A Case Study on Integration Using the Owner Subscriber Option (Doc ID 1297158.1)

• Campus Solutions CS - HCM Integration Support Released! (Doc ID 1267773.1)

• CS Bundle #19 Functional Documentation for Campus Solutions 9.0 & Feature Pack 4(Doc ID 1259484.1)

• Campus Solutions Announces Support and Documentation Availability for the Subscriber Only Model of the CS-HCM Separate Instance Integration (Doc ID 1320568.1)

• Campus Solutions Announces Support and Documentation Availability for the Subscriber Only Model of the CS-HCM Separate Instance Integration (Doc ID 1320568.1) 

• CS/HCM Subscriber Only or Subscriber/Subscriber KM doc - (Doc ID 1412093.1)

Please note that HR core maintenance will still need to be applied to Campus Solutions.

How to Connect to a Oracle/Peoplesoft Database using PHP

Entry from within tnsnames.ora file

ORA_SERVICE = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME=ora_service)))

 This is sample script to connect and execute a query:

$db_conn["host"] = "ORA_SERVICE"; # service name in the tnsnames.ora file 
$db_conn["user"] = "myuser"; # username 
$db_conn["pass"] = "mypass"; # password 
$db_conn["library"] = "OCI"; 
 
$connect_id = ocilogon($db_conn["user"], $db_conn["pass"], $db_conn["host"]); 
 
$query = "SELECT * FROM table"; 
$statement = ociparse($connect_id, $query); 
ociexecute($statement); 
 
$result = array(); while(ocifetchinto($statement, $tmp, OCI_ASSOC + OCI_RETURN_NULLS + OCI_RETURN_LOBS)) { 
  array_push($result, $tmp); } 
 
ocifreestatement($statement); 
 
var_dump($result); # result is here 

Please also review this connection link for PHP - http://www.php.net/manual/en/function.oci-connect.php

SQL Query to find all Records within a Specific Peoplesoft Component

I found this query on CompShack and wanted to also post on here.  I have used the following sample: 'ADM_APPL_MAINTNCE'

This query would be useful when you do a CNTL+J and are wanting to know the table names without going into App Designer.

SELECT DISTINCT (recname)
FROM psrecdefn
WHERE recname IN
(SELECT DISTINCT (recname)
FROM pspnlfield
WHERE pnlname IN
(SELECT DISTINCT (b.pnlname)
 FROM pspnlgroup a, pspnlfield b
WHERE (   a.pnlname = b.pnlname OR a.pnlname =b.subpnlname)
AND a.pnlgrpname = 'ADM_APPL_MAINTNCE' -- specify your component name)
AND recname <> ' ')
UNION
SELECT DISTINCT (recname)
FROM pspnlfield
WHERE pnlname IN
(SELECT DISTINCT (b.subpnlname)
 FROM pspnlgroup a,pspnlfield b
WHERE (a.pnlname = b.pnlname OR a.pnlname = b.subpnlname )
AND a.pnlgrpname = 'ADM_APPL_MAINTNCE') -- specify your component name)
AND recname <> ' ')
AND rectype in ('0')  -- specify record type, default is 0 for table
--AND rectype in ('0', '1', '2')  -- specify all types
ORDER BY recname ASC