In Table PSAUTHITEM
AUTHORIZEDACTIONS Codes : the Authorized Actions
1: Add
2: Update Display
3: Add/Update/Display
4: Update/Display All
5: Add/Update/Display All
6: Update/Display/Update/Display All
7: Add/Update/Display/Update/Display All
8: Correction
9: Add/Correction
10: Update/Display / Correction
11: Add/Update/Display/Correction
12: Update/Display All/Correction
13: Add/Update/Display All/Correction
14: Update/Display/Update/Display All/Correction
15: Add/Update/Display/Update/Display All/Correction
128: Data Entry
For all the authorized actions for a given menu item for a given operator, these numbers are added together and stored in the authorizedactions column.
For example, if the value for authorizedactions column is 143, the operator is authorized for all the actions. (1+2+4+8+128) .
As another example, if the value is 7, the operator is authorized for the actions Add, Update/Display, Update/Display All (1+2+4).
2010-05-26
Howto get the names of ROLE with a specified Permission List
It will be a very boring work if you want to search for the Roles one by one to see which role has the Permission List wanted.
SQL helps:
SQL helps:
select ROLENAME from PSROLECLASS where CLASSID='[the_permission_list]';Then we can grant user the role from the result.
insert into PSROLEUSER values ('[user_id]', '[role_name]', '[dynamic_sw]')======== To overview all permission lists assigned to a USER. ========
select RU.ROLEUSER, RU.ROLENAME, RC.CLASSID from PSROLEUSER RU, PSROLECLASS RC where RU.ROLENAME=RC.ROLENAME and RU.ROLEUSER='PS'
Monitoring Integration Broker Messages
To monitor these PUB messages, we can have a look in the tables below:
The SQLs I am using to detect failed messages are:
The STATUSSTRING can be replaced by PUBSTATUS, with its Status Codes:
0 - Error, 1 - New , 2 - Started, 3 - Working,
4 - Done, 5 - Retry, 6 - Timeout, 7 - Edited, 8 - Canceled
I believe it is not hard at all to jump from PUB messages to SUB messages. :)
- PSAPMSGPUBCON
- PSAPMSGSUBCON
- PSIBLOGHDR
- PSAPMSGPUBHDR
The SQLs I am using to detect failed messages are:
or, if want to see more:select * from PSAPMSGPUBCON where STATUSSTRING='ERROR'
select * from PSAPMSGPUBHDR HDR, PSAPMSGPUBCON CON where HDR.IBTRANSACTIONID=CON.IBPUBTRANSACTID and CON.STATUSSTRING not in ('DONE', 'CNCLD')
The STATUSSTRING can be replaced by PUBSTATUS, with its Status Codes:
0 - Error, 1 - New , 2 - Started, 3 - Working,
4 - Done, 5 - Retry, 6 - Timeout, 7 - Edited, 8 - Canceled
I believe it is not hard at all to jump from PUB messages to SUB messages. :)
2010-05-19
PeopleSoft Meta-Tables
[original: http://www.compshack.com/wiki/peoplesoft/peopletools/peoplesoft-peopletools-meta-tables]
This is an attempt to list PeopleSoft meta-tables along with some kind of description for every table. The list will be broken into categories (pages, records, components, and so forth). Please feel free to add to or correct the list, this is a wiki page, so jump in!
Try it out:
PSPROJECTITEM table stores objects inserted into your Application Designer project.
Try it out:
PSPRSMPERM: Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.
PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values.
PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).
PSRECFIELD: Stores records with all their fields (sub-records are not expanded)
PSRECFIELDALL: Stores records with all their fields (sub-records are expanded)
PSINDEXDEFN: Contains 1 row per index defined for a table.
PSKEYDEFN: Containes 1 row per key field defined for an index.
PSDBFIELD: You got it, stores information about fields.
PSDBFLDLABL: Stores field label information.
PS_PRCSDEFN: Process definitions table. The record stores processes that can run within the Process Scheduler. Security information such as components and process groups are also stored on this table.
PSMSGCATLANG: language table.
Previous PeopleSoft message catalog tables:
PS_MESSAGE_CATALOG: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
MESSAGE_SET_TBL: Message set description table.
PSMENUITEM: List the menu with all components attached to it.
PSPNLGROUP: This table will give you information regarding a specific component along with the names of pages attached to it.
PSPNLFIELD: Stores all items used by each page definition.
PSAUTHITEM: Page Permissions. This table stores the information about the page level access for a permission list.
PSROLECLASS: Role Classes table. A many to many relationship table between Roles and Permission Lists.
PSROLEDEFN: This table stores information about Peoplesoft Role definitions. Users get permissions to PeopleSoft objects through Roles, which are assigned Permission Lists.
PSROLEUSER: This table stores information about the Users in Peoplesoft and the roles assigned to them.
PSCLASSDEFN: Permissions List definitions table. Permission list name can be found under Field Name CLASSID.
PSOPRDEFN: Users/Operator definition table. This table stores information about PeopleSoft users. This is the core table for User Profile Manager.
PSOPRCLS: Users/Operator and Perm list mapping Table. This table stores information about PeopleSoft users and the permission lists attached to those users.
A User gets these permission lists indirectly through the roles which are attached to the user
Here is an example query post that uses all of the above security tables!
PSQRYFIELD: Stores all fields used in a query (both the fields in the Select and Where clause).
PSQRYCRITERIA: Stores criteria query fields. You can get the name of the fields by joining the PSQRYFIELD table.
PSQRYEXPR: Stores query expressions.
PSQRYBIND: Stores query bind variables.
PSQRYRECORD: Stores all records used in all aspects of query creation
PSQRYSELECT: Stores all SELECT requirements by select type. Example would be sub select, join, ect.
PSQRYLINK: Stores the relationships to child queries.
PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher)
PSQRYSTATS: Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).
PSSQLDESCR: Stores SQL objects descriptions, and description long.
PSSQLTEXTDEFN: Stores actual SQL text. You can filter by SQLTYPE field to get SQL objects of interest such as Views SQLs and Application Engine SQLs.
PSAEAPPLSTATE: Stores application engine STATE records and a flag to indicate if the record is the default STATE record.
PSAESECTDEFN: Application engine section information and also stores last user id to update a specific section.
PSAESECTDTLDEFN: AE section along with descriptions and wither the section is active or not.
PSAEAPPLTEMPTBL: If your application engine uses Temp tables it will show on this record.
PSAESTEPDEFN: Steps in application engines are stored in this table.
PSAESTMTDEFN: Stores your application engine actions and along with their types, such as "Do Select" and so on.
PSAESTEPMSGDEFN: Application engine message action definition table.
AEREQUESTTBL: Application Engine request table behind the AE run control page.
AEREQUESTPARM: Application Engine request parameters table behind the AE run control page.
PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).
PSPRCSRQST: This record contains the process request information to run a process request.
PS_PMN_PRCSLIST: A view to list all process requests in the Process Monitor except for "Delete" (runstatus = 2) process requests.
PSCHGCTLLOCK: Description as explained by PeopleSoft "This table contains a a row for every object that is currently locked by any user. When the user requests to lock an object in the Application Designer, first this table is searched to see if the object is locked by another user. If it is not found, a row is inserted into the table. When the user requests to unlock an object, the row in this table is deleted."
Visit this post to see how could you make use of this table.
PSMAPFIELD: Stores Field mapping of Activity
PS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online.
This is an attempt to list PeopleSoft meta-tables along with some kind of description for every table. The list will be broken into categories (pages, records, components, and so forth). Please feel free to add to or correct the list, this is a wiki page, so jump in!
PeopleSoft Projects
PSPROJECTDEFN table stores information about projects created in Application Designer.Try it out:
SELECT * FROM PSPROJECTDEFN
WHERE PROJECTNAME = 'Your_Project_name';
WHERE PROJECTNAME = 'Your_Project_name';
Try it out:
SELECT * FROM PSPROJECTITEM
WHERE PROJECTNAME = 'Your_Project_name';
WHERE PROJECTNAME = 'Your_Project_name';
Portal Structure
PSPRSMDEFN is a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component. Take a closer look on how this is done!.PSPRSMPERM: Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.
XLAT Tables
XLATTABLE: Stores translate values (PeopleSoft version prior to 8.4).PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values.
PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).
Record & Field Tables
PSRECDEFN: Stores informations about tables. One row for each table. Field count and record type are two fields that are stored on this table.CASE RECTYPE
WHEN 0 THEN 'Table'
WHEN 1 THEN 'View'
WHEN 2 THEN 'Derived'
WHEN 3 THEN 'Sub Record'
WHEN 5 THEN 'Dynamic View'
WHEN 6 THEN 'Query View'
WHEN 7 THEN 'Temporary Table'
ELSE TO_CHAR(RECTYPE)
END CASE
WHEN 0 THEN 'Table'
WHEN 1 THEN 'View'
WHEN 2 THEN 'Derived'
WHEN 3 THEN 'Sub Record'
WHEN 5 THEN 'Dynamic View'
WHEN 6 THEN 'Query View'
WHEN 7 THEN 'Temporary Table'
ELSE TO_CHAR(RECTYPE)
END CASE
PSRECFIELDALL: Stores records with all their fields (sub-records are expanded)
PSINDEXDEFN: Contains 1 row per index defined for a table.
PSKEYDEFN: Containes 1 row per key field defined for an index.
PSDBFIELD: You got it, stores information about fields.
CASE FIELDTYPE
WHEN 0 THEN 'Character'
WHEN 1 THEN 'Long Character'
WHEN 2 THEN 'Number'
WHEN 3 THEN 'Signed Number'
WHEN 4 THEN 'Date'
WHEN 5 THEN 'Time'
WHEN 6 THEN 'DateTime'
WHEN 8 THEN 'Image'
WHEN 9 THEN 'Image Reference'
ELSE TO_CHAR(FIELDTYPE)
END CASE
WHEN 0 THEN 'Character'
WHEN 1 THEN 'Long Character'
WHEN 2 THEN 'Number'
WHEN 3 THEN 'Signed Number'
WHEN 4 THEN 'Date'
WHEN 5 THEN 'Time'
WHEN 6 THEN 'DateTime'
WHEN 8 THEN 'Image'
WHEN 9 THEN 'Image Reference'
ELSE TO_CHAR(FIELDTYPE)
END CASE
Process Definition Table(s)
PS_PRCSDEFNPNL: Stores the process definition name, process type(sqr report, application engine...), and the component name associated with the process definition.PS_PRCSDEFN: Process definitions table. The record stores processes that can run within the Process Scheduler. Security information such as components and process groups are also stored on this table.
Message Catalog Tables
PSMSGCATDEFN: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.PSMSGCATLANG: language table.
-- Example
SELECT * FROM PSMSGCATDEFN
WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM < TO_DATE('05-DEC-07', 'DD-MON-YY')
ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;
-- This will return messages that has been last update/added between 2 specific dates.
SELECT * FROM PSMSGCATDEFN
WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM < TO_DATE('05-DEC-07', 'DD-MON-YY')
ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;
-- This will return messages that has been last update/added between 2 specific dates.
Previous PeopleSoft message catalog tables:
PS_MESSAGE_CATALOG: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
MESSAGE_SET_TBL: Message set description table.
-- Example
SELECT * FROM PS_MESSAGE_CATALOG
WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM < TO_DATE('05-DEC-07', 'DD-MON-YY')
ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;
-- This will return messages that has been last update/added between 2 specific dates.
SELECT * FROM PS_MESSAGE_CATALOG
WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM < TO_DATE('05-DEC-07', 'DD-MON-YY')
ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;
-- This will return messages that has been last update/added between 2 specific dates.
Menu Tables
PSMENUDEFN: Store Menu related information. No related component info on this table.PSMENUITEM: List the menu with all components attached to it.
Component Tables
PSPNLGRPDEFN: Stores component related information only.PSPNLGROUP: This table will give you information regarding a specific component along with the names of pages attached to it.
Pages
PSPNLDEFN: Stores pages definitions.PSPNLFIELD: Stores all items used by each page definition.
Security
PSPRSMPERM: Portal Structure Permissions.PSAUTHITEM: Page Permissions. This table stores the information about the page level access for a permission list.
PSROLECLASS: Role Classes table. A many to many relationship table between Roles and Permission Lists.
PSROLEDEFN: This table stores information about Peoplesoft Role definitions. Users get permissions to PeopleSoft objects through Roles, which are assigned Permission Lists.
PSROLEUSER: This table stores information about the Users in Peoplesoft and the roles assigned to them.
PSCLASSDEFN: Permissions List definitions table. Permission list name can be found under Field Name CLASSID.
PSOPRDEFN: Users/Operator definition table. This table stores information about PeopleSoft users. This is the core table for User Profile Manager.
PSOPRCLS: Users/Operator and Perm list mapping Table. This table stores information about PeopleSoft users and the permission lists attached to those users.
A User gets these permission lists indirectly through the roles which are attached to the user
Here is an example query post that uses all of the above security tables!
URL Definitions
PSURLDEFN: Stores URL definitions. Here is the path to create URL definitions in PeopleSoft Root >> PeopleTools >> Utilities >> Administration >> URLsApplication Classes
PSAPPCLASSDEFN: Application Class Definitions table. You can use field PACKAGEROOT to search for a specific Application Package.PeopleSoft Query Tables
PSQRYDEFN: Stores query related info.PSQRYFIELD: Stores all fields used in a query (both the fields in the Select and Where clause).
PSQRYCRITERIA: Stores criteria query fields. You can get the name of the fields by joining the PSQRYFIELD table.
PSQRYEXPR: Stores query expressions.
PSQRYBIND: Stores query bind variables.
PSQRYRECORD: Stores all records used in all aspects of query creation
PSQRYSELECT: Stores all SELECT requirements by select type. Example would be sub select, join, ect.
PSQRYLINK: Stores the relationships to child queries.
PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher)
PSQRYSTATS: Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).
SQL Objects
PSSQLDEFN: Stores SQL object definitions.PSSQLDESCR: Stores SQL objects descriptions, and description long.
PSSQLTEXTDEFN: Stores actual SQL text. You can filter by SQLTYPE field to get SQL objects of interest such as Views SQLs and Application Engine SQLs.
-- When SQL type is:
0 = Stand alone SQL objects
1 = Application engine SQL
2 = Views SQLs
0 = Stand alone SQL objects
1 = Application engine SQL
2 = Views SQLs
Application Engines
PSAEAPPLDEFN: Table that stores Application Engine program definitions.PSAEAPPLSTATE: Stores application engine STATE records and a flag to indicate if the record is the default STATE record.
PSAESECTDEFN: Application engine section information and also stores last user id to update a specific section.
PSAESECTDTLDEFN: AE section along with descriptions and wither the section is active or not.
PSAEAPPLTEMPTBL: If your application engine uses Temp tables it will show on this record.
PSAESTEPDEFN: Steps in application engines are stored in this table.
PSAESTMTDEFN: Stores your application engine actions and along with their types, such as "Do Select" and so on.
PSAESTEPMSGDEFN: Application engine message action definition table.
AEREQUESTTBL: Application Engine request table behind the AE run control page.
AEREQUESTPARM: Application Engine request parameters table behind the AE run control page.
PeopleCode Tables
PSPCMNAME: PeopleCode Reference table.PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).
Process Request Tables
PSPRCSQUE: This record contains the process request information to run a process request.PSPRCSRQST: This record contains the process request information to run a process request.
PS_PMN_PRCSLIST: A view to list all process requests in the Process Monitor except for "Delete" (runstatus = 2) process requests.
Other Useful Tables
PSSTATUS: Stores PeopleSoft information such as PS Tools release version and the UNICODE_ENABLED boolean flag where a value of 1 indicates the DB is to be treated by Tools as a UNICODE DB.PSCHGCTLLOCK: Description as explained by PeopleSoft "This table contains a a row for every object that is currently locked by any user. When the user requests to lock an object in the Application Designer, first this table is searched to see if the object is locked by another user. If it is not found, a row is inserted into the table. When the user requests to unlock an object, the row in this table is deleted."
Visit this post to see how could you make use of this table.
PSMAPFIELD: Stores Field mapping of Activity
PS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online.
2010-05-12
HRMS Security Update
In order to apply security settings for newly created account, run the following three AE's -
SCRTY_CLSUPD - Refresh SJT_CLASS_ALL
SCRTY_OPRCLS
SCRTY_SJTUPD
We created a job which runs the following nightly. It is mySCRTY_OPRCLS
SCRTY_SJTUPD
understanding that they need to run in this sequence.
Since these deal with row-level security, and since they rebuild the SJT
tables, if they are run while people are on the system, those people
will temporarily lose their ability to look at employees.
----------------- more -----------
HR_PERSDATA Application Engine - Updates the PERSONAL_DATA snapshot table with future dated entries as they become effective.
NAME_DISPLAY Application Engine - Refreshes the NAME_DISPLAY and NAME_FORMAL fields in all records with the NAME_GBL_SBR. Use when the criteria used to change these fields is changed.
PER099 Application Engine - Fill EMPLOYEES Table.
PTAF_NEM Application Engine - PTAF_NEM - Approval Framework Escalation App Engine. AWE Workflow escalation process.
SCRTY_OPRCLS Application Engine - SCRTY_OPRCLS - Updates the SJT_OPR_CLS security Join Table.
SCRTY_SJTDLY Application Engine - Nightly update for SJT Records - Update of all effdt rows that have become current as of this data.
HRS_AM Application Engine - Applicant Search Auto-Match -Recruiter Applicant Search auto-match process.
HRS_DUPE_PRC Application Engine - Find Applicant Duplicates - Finds applicant duplicates given certain criteria.
HRS_JOB_AGNT Application Engine - Job Agent - Run the Job Posting Search Agent process.
HRS_JSCH_IDX Application Engine -Verity based Job Posting Index build process.
HRS035 SQR Report - Application Analysis (USA).
PER506 SQR Report - Department Tbl & Departmental - This report lists any departments that you have created in the Department Tree but have not yet added to the departmental security tree, It also lists any changes in departments since the effective date of the current security tree.
PER900 SQR Report - Core HR Data Integrity Audit. - Audit of the CORE HR Tables for Data Relationship Integrity.
PSBARUN - Cobal Job - For the Benefit Administration - scheduled to run twice each night with some custom pre and post processes.
NAME_DISPLAY Application Engine - Refreshes the NAME_DISPLAY and NAME_FORMAL fields in all records with the NAME_GBL_SBR. Use when the criteria used to change these fields is changed.
PER099 Application Engine - Fill EMPLOYEES Table.
PTAF_NEM Application Engine - PTAF_NEM - Approval Framework Escalation App Engine. AWE Workflow escalation process.
SCRTY_OPRCLS Application Engine - SCRTY_OPRCLS - Updates the SJT_OPR_CLS security Join Table.
SCRTY_SJTDLY Application Engine - Nightly update for SJT Records - Update of all effdt rows that have become current as of this data.
HRS_AM Application Engine - Applicant Search Auto-Match -Recruiter Applicant Search auto-match process.
HRS_DUPE_PRC Application Engine - Find Applicant Duplicates - Finds applicant duplicates given certain criteria.
HRS_JOB_AGNT Application Engine - Job Agent - Run the Job Posting Search Agent process.
HRS_JSCH_IDX Application Engine -Verity based Job Posting Index build process.
HRS035 SQR Report - Application Analysis (USA).
PER506 SQR Report - Department Tbl & Departmental - This report lists any departments that you have created in the Department Tree but have not yet added to the departmental security tree, It also lists any changes in departments since the effective date of the current security tree.
PER900 SQR Report - Core HR Data Integrity Audit. - Audit of the CORE HR Tables for Data Relationship Integrity.
PSBARUN - Cobal Job - For the Benefit Administration - scheduled to run twice each night with some custom pre and post processes.
Troubleshooting PT 8.48 PT 8.49 Integration Broker
http://iambic.homeip.net/site/doku.php/tech/psoft/ib_troubleshooting
====== Troubleshooting PT 8.48 PT 8.49 Integration Broker ======
Troubleshooting guidance for publication process, subscription process, and other possible problems.
===== Publication Process Problems =====
Possible publication process problems include:\\
* Publication contract is not created.
* Publication contract is in NEW status.
* Publication contract stays in RETRY status.
* Publication contract is in WORKING status.
* Publication contract is in TIMEOUT status.
* Publication contract is in ERROR status.
==== Pub Contract Is Not Created ====
* No publication PeopleCode exists.
* Publication PeopleCode is incorrect.
* No outbound routings exists for the service operation.
==== Pub Contract Is in NEW Status ====
* Sending queue is paused.
* Publication Dispatcher crashed or was brought down.
* Sending node is paused.
* Previous service operation had a status of Retry, Error, or Timeout.
* Sending domain is not active.
* After an unsuccessful ping, a row may be added to the table PSNODESDOWN; which will hold up the message queue; query the table PSNODESDOWN.
==== Pub Contract Stays in RETRY Status ====
* The remote node cannot be pinged successfully; the publication contract will be processed when the remote node comes back up.
* No publication handler is available, either because it crashed or it was brought down.
* Receiving Node URL is incorrect in integrationGateway.properties file.
==== Pub Contract Is in WORKING Status ====
* The publication handler processing the contract is on another machine and either the machine or the domain is down. Processing should continue when the pub/sub system on the other machine comes back up.
* Single threading on the application server is slowing processing.
==== Pub Contract Is in TIMEOUT Status ====
* An exception occurred on the target application server (look in APPSRV.LOG file for details); verify that:
* The reply is incorrectly routed; check Gateway for correct machine address of target node.
* Bad XML syntax.
==== Pub Contract Is in ERROR Status ====
* Receiving node user profile not authorized to service operation.
* Inbound routing is not set up on the receiving system.
* Service operation is not active on the receiving system.
* Service operation has not been granted security access on the receiving system.
* The source node is not defined in the target database.
* Handler PeopleCode is bad.
* Remote application server is down.
* Receiving Node is not defined in the integrationGateway.properties file.
* Receiving PeopleSoft node has not been added to single signon.
* Service operation version on target is not active.
===== Subscription Process Problems =====
Possible subscription process problems include:\\
* Subscription contract is not created.
* Subscription contract is in NEW status.
* Subscription contract is in STARTED status.
* Subscription contract is in WORKING status.
* Subscription contract is in ERROR status.
* Subscription contract is in EDIT status.
==== Sub Contract Is not Created ====
* No handler exists for the service operation.
* Service operation handler is missing method.
* Queue routing rules not set up properly.
==== Sub Contract Is in NEW Status ====
* Application Server down.
* Pub/Sub processes not configured on Application Server domain.
* The Subscription Dispatcher has crashed or has been brought down.
* Receiving queue is paused.
* Receiving node is paused.
* Previous service operation had errors or timed out.
* No row was inserted into PSAPMSGSUBPRCID, To insert a row enter the following SQL statement in your Query tool use the following statement:
==== Sub Contract Is in STARTED Status ====
* Subscription Handler is down.
* Target component is not valid.
==== Sub Contract Is in WORKING Status ====
* Subscription Handler crashed while processing message.
==== Sub Contract Is in ERROR Status ====
* Queue property if Ordered enables subscription contracts to go in random order, which causes FULLSYNC service operations to error out when the transaction is subscribed before the header.
* Service operation handler PeopleCode errors exist.
* Application data errors exist.
==== Sub Contract Is in EDIT Status ====
Possible cause is that the XML was edited and is not yet resubmitted for processing.
===== Other Possible Problems =====
Other possible problems include:\\
* Cannot find service operation in Service Operation Monitor.
* Service operations are being processed in an incorrect order.
* Service operation is not created.
* Service operation instance stays in NEW status.
* Service operation instance stays in STARTED status.
* Service operation instance stays in WORKING status.
* Unable to ping a node.
* Queue is PAUSED.
==== Cannot Find Service Operation in Service Operation Monitor ====
* Possible causes is that filtering is set in the Service Operation Monitor.
==== Service Operations are Being Processed in an Incorrect Order ====
* Possible cause is that the queue was partitioned and the resulting subqueues do not match what was assumed for the ordering of the service operations.
==== Service Operation Instance not Created ====
* Possible cause is that the service operation is inactive.
==== Service Operation Instance Stays in NEW Status ====
* The Application server is down.
* Pub/sub services are not configured on the Application Server domain.
* The Message Dispatcher crashed or was brought down.
* The item is not at the top of the queue; all service operations with the same queue or subqueue are in the same queue.
==== Service Operation Instance Stays in STARTED Status ====
* All Message Handlers crashed or were brought down; processing will resume when Message Handlers come back up.
* The Message dispatcher processing the message is on another machine, and either the machine or the application server domain is down.
==== Service Operation Instance Stays in WORKING Status ====
* Message Broker Handler crashed.
* The Message Handler processing the message is on another machine, and either the machine or the application server domain is down.
* The Message Handler working on the message is blocked. The service will time out, and the Message Dispatcher will retry the message.
==== Unable to Ping a Node ====
* The web server for the Gateway is down.
* The Gateway is not configured properly.
* The application server for the node is down.
* The Gateway URL is incorrect; verify that the URL is correct.
* Copy URL in browser address; you should see: \\
==== Queue Is PAUSED ====
Possible cause is that some queues are delivered as paused. Change the status to Run for the service operations to process from New to Working.
===== Unable to find a Routing corresponding to the incoming request message =====
You are sending an async service operation from node A to target node B. The nodes are pingable. The routing on target database and source database exist and are correct. The same service operation works fine if you use and "ANY TO LOCAL" routing.
The "ANY TO LOCAL" routing type uses a virtual node ANY when node passwords fail and eliminates node authentication from the equation. If "ANY TO LOCAL" works, but a standard point to point routing fails, reset the node authentication option to NONE rather than password and try again. If this works, then the node passwords were incorrect. Resetting these should resolve the problem.
Note: You can also see this error if the Message name does not match the //externalservice// operation name. **They have to Match.**
===== Unable to ping nodes. "Issuing node is not a trusted node" or "PSFT Authentication token failed for Node PSFT_HR" =====
Application server log shows:
Starting with Peopletools 8.48 Integration Broker requires that nodes be //trusted// on both sides before pinging or sending messages.
To fix this, navigate to Peopletools --> Security Objects --> Single Signon --> "Trust Authentication Tokens issued by these Nodes" and add the appropriate node names on each side.
====== Troubleshooting PT 8.48 PT 8.49 Integration Broker ======
Troubleshooting guidance for publication process, subscription process, and other possible problems.
===== Publication Process Problems =====
Possible publication process problems include:\\
* Publication contract is not created.
* Publication contract is in NEW status.
* Publication contract stays in RETRY status.
* Publication contract is in WORKING status.
* Publication contract is in TIMEOUT status.
* Publication contract is in ERROR status.
==== Pub Contract Is Not Created ====
* No publication PeopleCode exists.
* Publication PeopleCode is incorrect.
* No outbound routings exists for the service operation.
==== Pub Contract Is in NEW Status ====
* Sending queue is paused.
* Publication Dispatcher crashed or was brought down.
* Sending node is paused.
* Previous service operation had a status of Retry, Error, or Timeout.
* Sending domain is not active.
* After an unsuccessful ping, a row may be added to the table PSNODESDOWN; which will hold up the message queue; query the table PSNODESDOWN.
==== Pub Contract Stays in RETRY Status ====
* The remote node cannot be pinged successfully; the publication contract will be processed when the remote node comes back up.
* No publication handler is available, either because it crashed or it was brought down.
* Receiving Node URL is incorrect in integrationGateway.properties file.
==== Pub Contract Is in WORKING Status ====
* The publication handler processing the contract is on another machine and either the machine or the domain is down. Processing should continue when the pub/sub system on the other machine comes back up.
* Single threading on the application server is slowing processing.
==== Pub Contract Is in TIMEOUT Status ====
* An exception occurred on the target application server (look in APPSRV.LOG file for details); verify that:
* The reply is incorrectly routed; check Gateway for correct machine address of target node.
* Bad XML syntax.
==== Pub Contract Is in ERROR Status ====
* Receiving node user profile not authorized to service operation.
* Inbound routing is not set up on the receiving system.
* Service operation is not active on the receiving system.
* Service operation has not been granted security access on the receiving system.
* The source node is not defined in the target database.
* Handler PeopleCode is bad.
* Remote application server is down.
* Receiving Node is not defined in the integrationGateway.properties file.
* Receiving PeopleSoft node has not been added to single signon.
* Service operation version on target is not active.
===== Subscription Process Problems =====
Possible subscription process problems include:\\
* Subscription contract is not created.
* Subscription contract is in NEW status.
* Subscription contract is in STARTED status.
* Subscription contract is in WORKING status.
* Subscription contract is in ERROR status.
* Subscription contract is in EDIT status.
==== Sub Contract Is not Created ====
* No handler exists for the service operation.
* Service operation handler is missing method.
* Queue routing rules not set up properly.
==== Sub Contract Is in NEW Status ====
* Application Server down.
* Pub/Sub processes not configured on Application Server domain.
* The Subscription Dispatcher has crashed or has been brought down.
* Receiving queue is paused.
* Receiving node is paused.
* Previous service operation had errors or timed out.
* No row was inserted into PSAPMSGSUBPRCID, To insert a row enter the following SQL statement in your Query tool use the following statement:
insert into PSAPMSGSUBPRCID values(0) ==== Sub Contract Is in STARTED Status ====
* Subscription Handler is down.
* Target component is not valid.
==== Sub Contract Is in WORKING Status ====
* Subscription Handler crashed while processing message.
==== Sub Contract Is in ERROR Status ====
* Queue property if Ordered enables subscription contracts to go in random order, which causes FULLSYNC service operations to error out when the transaction is subscribed before the header.
* Service operation handler PeopleCode errors exist.
* Application data errors exist.
==== Sub Contract Is in EDIT Status ====
Possible cause is that the XML was edited and is not yet resubmitted for processing.
===== Other Possible Problems =====
Other possible problems include:\\
* Cannot find service operation in Service Operation Monitor.
* Service operations are being processed in an incorrect order.
* Service operation is not created.
* Service operation instance stays in NEW status.
* Service operation instance stays in STARTED status.
* Service operation instance stays in WORKING status.
* Unable to ping a node.
* Queue is PAUSED.
==== Cannot Find Service Operation in Service Operation Monitor ====
* Possible causes is that filtering is set in the Service Operation Monitor.
==== Service Operations are Being Processed in an Incorrect Order ====
* Possible cause is that the queue was partitioned and the resulting subqueues do not match what was assumed for the ordering of the service operations.
==== Service Operation Instance not Created ====
* Possible cause is that the service operation is inactive.
==== Service Operation Instance Stays in NEW Status ====
* The Application server is down.
* Pub/sub services are not configured on the Application Server domain.
* The Message Dispatcher crashed or was brought down.
* The item is not at the top of the queue; all service operations with the same queue or subqueue are in the same queue.
==== Service Operation Instance Stays in STARTED Status ====
* All Message Handlers crashed or were brought down; processing will resume when Message Handlers come back up.
* The Message dispatcher processing the message is on another machine, and either the machine or the application server domain is down.
==== Service Operation Instance Stays in WORKING Status ====
* Message Broker Handler crashed.
* The Message Handler processing the message is on another machine, and either the machine or the application server domain is down.
* The Message Handler working on the message is blocked. The service will time out, and the Message Dispatcher will retry the message.
==== Unable to Ping a Node ====
* The web server for the Gateway is down.
* The Gateway is not configured properly.
* The application server for the node is down.
* The Gateway URL is incorrect; verify that the URL is correct.
* Copy URL in browser address; you should see: \\
PeopleSoft Integration Gateway
PeopleSoft Listening Connector
Status: ACTIVE
==== Queue Is PAUSED ====
Possible cause is that some queues are delivered as paused. Change the status to Run for the service operations to process from New to Working.
===== Unable to find a Routing corresponding to the incoming request message =====
You are sending an async service operation from node A to target node B. The nodes are pingable. The routing on target database and source database exist and are correct. The same service operation works fine if you use and "ANY TO LOCAL" routing.
The "ANY TO LOCAL" routing type uses a virtual node ANY when node passwords fail and eliminates node authentication from the equation. If "ANY TO LOCAL" works, but a standard point to point routing fails, reset the node authentication option to NONE rather than password and try again. If this works, then the node passwords were incorrect. Resetting these should resolve the problem.
Note: You can also see this error if the Message name does not match the //externalservice// operation name. **They have to Match.**
===== Unable to ping nodes. "Issuing node is not a trusted node" or "PSFT Authentication token failed for Node PSFT_HR" =====
Application server log shows:
PSAPPSRV.6475 (32) [10/17/06 22:09:45 VP1@JavaClient IntegrationSvc](3) PeopleSoft Token authentication failed: issuing node PSFT_HR is not a trusted node : PS@JavaClientStarting with Peopletools 8.48 Integration Broker requires that nodes be //trusted// on both sides before pinging or sending messages.
To fix this, navigate to Peopletools --> Security Objects --> Single Signon --> "Trust Authentication Tokens issued by these Nodes" and add the appropriate node names on each side.
2010-05-06
Peoplesoft HRMS Foundation tables
Peoplesoft HRMS Foundation tables
Peoplesoft Foundation tables can be broadly categorized into
1. Organizational tables
2. Personal Information tables
3. Job Tables
1. Organizational Tables
a. Enter company information.
Use the Company component to enter information about a single company or multiple companies in your organization, from the corporate address to general ledger accounts, tax information, and payroll processing information.
Table names: LEGAL_TYPE_TBL
COMPANY_TABLE1
COMPANY_TABLE2_GBL
COMPANY_TABLE3_GB
COMPANY_TABLE4_GBL
b. Define business units.
Table names: a. BUS_UNIT_TBL_HR
b. BUS_UNIT_TBL_HR2
c. BUS_UNIT_OPT_HR
d. BUS_UNIT_TBL_GL
Description: a. Add or update business b. Identify business units in other PeopleSoft applications that are related to a business unit. c. Set system defaults such as Company, Country, and Currency for a specific SetID. d. Review GL business units.
c. Enter agency information.
Use the Agency component (AGENCY_TABLE) to enter information about a single agency or sub-agencies in your organization, from the central agency address to general ledger accounts, tax information, and payroll processing information.
Table names: GVT_COMPANY_TBL7
GVT_COMPANY_TBL8
GVT_COMPANY_TBL9
d. Establish locations.
Use the Locations component to establish physical locations in your organization, such as corporate headquarters, branch offices, remote sales offices, and so forth.
Table names: LOCATION_TABLE1
LOC_BU_SEC
BUS_PHONE_SEC
LOCATION_TBL2_GBL
e. Maintain departments.
After you define company and location data for your enterprise, use the Departments component to define business entities in your organization.
Table names: DEPARTMENT_TBL_GBL
DEPARTMENT_TBL_CA
f. Define establishments.
You use the Establishment component to define distinct physical places of business (establishments) within your company, to enter address information, and to enter regulatory reporting information
Table names: ESTAB_TBL1_GBL
ESTAB_TBL2_GBL
CONTROLLED_ESTABS
g. Set up company locations.
Table names: COMP_LOC_TBL
h. Set up primary permission list preferences
Set predefined TableSet sharing and system-wide defaults for each of your primary permission lists and Set Payroll System and system-wide defaults for each of your permission lists.
Table names: OPR_DEF_TBL_HR
OPR_DEF_TBL_HR2
i. Set up person of interest types
Table Name: POI_TYPE_TBL
Description: This is about tracking Non-employees in an organization.
j. Set up holiday schedules
Table Name: HOLIDAY_SCHED_TBL
Description: Designate holidays for payroll processing.
2. Personal Information Tables
a. Define national ID types.
Table name: NID_TYPE_TABLE
Description: Assign a national ID type to a country code and provide a default or a dummy national ID for a country to use when a person or applicant ID is unavailable.
b. Define address types.
Table name: ADDR_TYPE_TBL
Description: Enter address types and the order in which they are available.
c. Set up additional name information.
Table name: a. NAME_FORMAT_TBL
b. NAME_TYPE_TBL
c. NAME_PREFIX_TABLE
d. NAME_SUFFIX_TABLE
e. TITLE_TBL
Description: a. Define name format types to use on name pages throughout HRMS.
b. Define name types and the order in which they are available.
c. Enter name prefixes that you’ll use when you record name information.
d. Enter name suffixes to use to record name information.
e. Enter titles for use with names.
d. Define citizen status codes.
Table name: CITIZEN_STATUS
Description: Define the citizenship statuses you’ll need to track for various countries. For example, you may need to distinguish between a native, a naturalized citizen, or a permanent resident of a particular country.
3. Job Tables
a. Define job families.
Create job families to group similar jobs.
Table name: JOB_FAMILY_TABLE
b. Classify jobs.
In HRMS, you identify jobs by job codes, and you maintain information about jobs independent of the person or group performing that job.
Table name: JOBCODE_TBL1_GBL
JOBCODE_TBL2_GBL
JOBCODE_BU
JOBCD_COMP_RATE
JOBCD_NON_BASE
c. Define job tasks.
Define job task codes, such as Data Entry, and assign it to a Job Code on the Job Code Task Table. On the Job Code Task Table, indicate the importance and frequency of the task.
Table name: JOB_TASK_TABLE
JOBCODE_TASK_TABLE
d. Set up pay groups.
When implementing a payroll system for use with HRMS, one of your major tasks is to set up pay groups. For example, you might create different pay groups for employees with different sets of benefits or earnings.
While pay groups may seem specific to payroll processing, you also need this information for Human Resources, to set up job records and benefit programs.
Table name: PAYGROUP_TABLE1
PAYGROUP_TABLE2
PAYGROUP_TBL3
Integration Broker:: manipulate the stuck messages
PSAPMSGPUBHDR - Message Instance Queue
- Youre probably not stuck here.
PSAPMSGPUBCON - Pub Contract Queue
- Youre probably stuck here.
- Shut down messaging servers.
- Update the status.
- Bring up messaging servers.
- Youre probably not stuck here.
PSAPMSGPUBCON - Pub Contract Queue
- Youre probably stuck here.
- Shut down messaging servers.
- Update the status.
- Bring up messaging servers.
Subscribe to:
Posts (Atom)