The link below the PeopleCode implementation of the connected query.
http://mfinchps.blogspot.com/2011/05/how-to-launch-connected-query-xml.html
Wednesday, January 25, 2012
Connected Query Feature
Forgot to post this for a long time. The below description I took it from PeopleBooks. Why to destroy its beauty :)
Starts here
Connected Query enables you to create a single XML file based on a set of queries with parent-child relationships.
A connected query is a hierarchical object built with existing PeopleSoft queries. A parent query can nest n levels of child queries and any child query can have m sibling queries within a hierarchy. No artificial limits exist for n and m, but typical use involves a single parent-child relationship or a few levels of nesting. Fields in a child query are mapped to related fields in the immediate parent query.
A connected query returns a hierarchical data set in which data returned by child queries is filtered by the results of its immediate parent query.
Connected queries are used to analyze data, supply to other systems with PeopleSoft data using Web Services, and create XML Publisher reports that use connected query as a Data Source.
Connected queries are significantly useful:
• To simplify task for a business user.
• User create a set of linked simple queries to collect data from multiple tables instead of creating a complicated query with multiple equal, outer joins, and grouping conditions.
• When two or more SQL statements are linked with left outer join to the same query.
• Connected Query enables you to collect data similar to query performing left outer joins. In contrast to query, Connected Query enables you to link multiple queries to a single parent query.
• As a tool in the process of replacing Crystal reports (that use subreports) with XML Publisher reports.
Crystal reports can include the main reports and a set of subreports that could be combined in the same template. Using Connected Query, you can replace a Crystal reports having main reports and sub-reports with XML Publisher reports.
• When outputs need to be in structured XML.
A simple query (including a query with multiple joins) creates a tabular plain layout. To have a hierarchical output, you need to use multiple grouping conditions that involve complicated logic and is not always straight forward. Connected Query processing creates a hierarchical output where a single row of data from parent query results in a set of rows in a child query. This data is processed row by row and produces structured data. It has similarity with SQR and Application Engine nested loops processing.
Instead of using Application Engine or SQR to collect data for file processing by XML Publisher, use Connected Query as the data collection tool because no conditional logic involves in this process. However, use SQR for processes with high volume of data or when high performance is required. You can quickly create a single parent-child relationship using the Connected Query Quick Start component (CQ_WIZARD), and create complex connected queries using the Connected Query Manager (PSCONQRS).
A connected query is a hierarchical object built with existing PeopleSoft queries. A parent query can nest n levels of child queries and any child query can have m sibling queries within a hierarchy. No artificial limits exist for n and m, but typical use involves a single parent-child relationship or a few levels of nesting. Fields in a child query are mapped to related fields in the immediate parent query.
A connected query returns a hierarchical data set in which data returned by child queries is filtered by the results of its immediate parent query.
Connected queries are used to analyze data, supply to other systems with PeopleSoft data using Web Services, and create XML Publisher reports that use connected query as a Data Source.
Connected queries are significantly useful:
• To simplify task for a business user.
• User create a set of linked simple queries to collect data from multiple tables instead of creating a complicated query with multiple equal, outer joins, and grouping conditions.
• When two or more SQL statements are linked with left outer join to the same query.
• Connected Query enables you to collect data similar to query performing left outer joins. In contrast to query, Connected Query enables you to link multiple queries to a single parent query.
• As a tool in the process of replacing Crystal reports (that use subreports) with XML Publisher reports.
Crystal reports can include the main reports and a set of subreports that could be combined in the same template. Using Connected Query, you can replace a Crystal reports having main reports and sub-reports with XML Publisher reports.
• When outputs need to be in structured XML.
A simple query (including a query with multiple joins) creates a tabular plain layout. To have a hierarchical output, you need to use multiple grouping conditions that involve complicated logic and is not always straight forward. Connected Query processing creates a hierarchical output where a single row of data from parent query results in a set of rows in a child query. This data is processed row by row and produces structured data. It has similarity with SQR and Application Engine nested loops processing.
Instead of using Application Engine or SQR to collect data for file processing by XML Publisher, use Connected Query as the data collection tool because no conditional logic involves in this process. However, use SQR for processes with high volume of data or when high performance is required. You can quickly create a single parent-child relationship using the Connected Query Quick Start component (CQ_WIZARD), and create complex connected queries using the Connected Query Manager (PSCONQRS).
Friday, January 20, 2012
PSQuery drilling feature
For those who havn't tried this feature:
After creating the query, go to Expressions tab and click on the Add Expression.
Search keys when clicked on it will give you the URL keys scroll which you selects as the parameters passed to the target component.
After creating the query, go to Expressions tab and click on the Add Expression.
Search keys when clicked on it will give you the URL keys scroll which you selects as the parameters passed to the target component.
Map URl to Query coloumns: to hyperlink the field of your wish in the query result
In the above query result you can see the emplid KU0007 is hyperlinked.
In the above query result you can see the emplid KU0007 is hyperlinked.
Auto Increment: Best practice
I came across this function recently and found it quite useful and a best coding practice.
If you have a counter setup and wanted to increment the counter manually in the code, rather than writing a sqlexec increment step you can use this delivered functions: GetNextNumber,GetNextNumberWithGaps and GetNextNumberWithGapsCommit.
GetNextNumber:
Use the GetNextNumber function to increment the value in a record for the field you specify by one and returns that value. You might use this function to increment an employee ID field by one when you are adding a new employee. If the new value generated exceeds max_number, a negative value is returned and the field value isn't incremented.
GetNextNumberWithGaps:
Use the GetNextNumberWithGaps function to determine the highest value in a table for the field you specify, and return that value plus increment
GetNextNumberWithGapsCommit:
Use the GetNextNumberWithGapsCommit function to return the sequence number value plus increment for the given field residing in the given record. This function also enables you to specify a SQL Where clause as part of the function for maintaining multiple sequence numbers in a single record.
This function is typically used for obtaining a new sequence number for the application, for example, getting a new Purchase Order number to be used in the application transaction.
Use this function instead of the GetNextNumberWithGaps function. The GetNextNumberWithGaps function is very restrictive in its usage. The GetNextNumberWithGapsCommit function can be used in any event. The sequence number (record.field ) is incremented right away and it doesn't hold any database internal row lock beyond the execution of this function.
If you have a counter setup and wanted to increment the counter manually in the code, rather than writing a sqlexec increment step you can use this delivered functions: GetNextNumber,GetNextNumberWithGaps and GetNextNumberWithGapsCommit.
GetNextNumber:
Use the GetNextNumber function to increment the value in a record for the field you specify by one and returns that value. You might use this function to increment an employee ID field by one when you are adding a new employee. If the new value generated exceeds max_number, a negative value is returned and the field value isn't incremented.
GetNextNumberWithGaps:
Use the GetNextNumberWithGaps function to determine the highest value in a table for the field you specify, and return that value plus increment
GetNextNumberWithGapsCommit:
Use the GetNextNumberWithGapsCommit function to return the sequence number value plus increment for the given field residing in the given record. This function also enables you to specify a SQL Where clause as part of the function for maintaining multiple sequence numbers in a single record.
This function is typically used for obtaining a new sequence number for the application, for example, getting a new Purchase Order number to be used in the application transaction.
Use this function instead of the GetNextNumberWithGaps function. The GetNextNumberWithGaps function is very restrictive in its usage. The GetNextNumberWithGapsCommit function can be used in any event. The sequence number (record.field ) is incremented right away and it doesn't hold any database internal row lock beyond the execution of this function.
Wednesday, January 4, 2012
AAWS Response error
Has anyone outhere experience this issue. You were able to save/submit the application using delivered AAWS SAD_ADMISSION web service. But in response we should get the application number for the tracking purpose. This is the bug currently we are facing. Any thoughts...
Subscribe to:
Posts (Atom)