Uses of Connected Query in BI publisher

Here are the top 5 uses for the connected query

1. Simplify the writing of reports for business users.  A business user can 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.

2. For duplicating the left outer join functionality.  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.

3. Replace Crystal Reports that contains Sub Queries with an XML Publisher report.   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.

4. When the output of your Queries needs to be in a hierarchical and structured XML file.

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.

5. As an alternate Data Source for XML Publisher. Instead of using Application Engine or SQR to collect data for file processing by XML Publisher, you can create a Connected Query to use as the data source because no conditional logic is involved in this process.

Connected Query comes with a Quick Start wizard like page that walks you through the process quickly and easily along with a Connected Query Manager, Viewer, and Scheduler.

In order to use a Connected Query you will need to get access to the pages through the Permission List PTPT2700.

File Layout Disadvantages and Advantages in PeopleSoft with CI

File layout could be used for bulk uploading of data from other third party 
systems to PeopleSoft systems, you know typically there will be no high 
level validation is like one of the disadvantage. you can achieve the 
integration of file lay out through application engine as well SQR also. 
But, as per my knowledge, using file layout is couldn't be a better choice 
rather than using a Component Interface. CI is could be preferred because 
it provides a high level  business validation. 

- File layout is a proprietor tool(peoplesoft object),it does the system 
validations like translate values,prompt values ,yes or no values ,required 
values and boolean values .This is the advantage of file layout . 
- The file layout is does not do the business validations like deptid and 
effdt and age fileds validations etc,.(its related to people code). This is 
the disadvantage of file layout. 

Example:I have found one disadvantage to the file layout and there maybe a way to work around this, but I did not find one. We receive a file from an external application and they were sending us data that was longer than the delivered field length for say address1 field, which I believe is 50 chars, and sometimes we would get over 50 chars and it was causing the AE program to fail, it was not truncating the data but failing, I was having to login in the middle of the night and either fix the data, delete the record so that we could import the file and process the data. I finally wrote a SQR to read in the file before hand and correct any field length issues and write out a new file that was read in by the AE using file layout. Not the most desired thing to do, but when people were putting in crazy stuff in the address1 field, what are you going to do, if they can't figure that out, I can't figure it out for them.

FL doesn't have any security layer to access database exither to read or 
load . But CI has that security layer for both read and load, at the same 
time Business validations can be triggered automatically from CI, no 
additional code required to this just invoke CI, But any specific 
a validation you need to do i n FL , you need to write the code accordingly 

Inbound process in peoplesoft

Q:  how to export data from CSV file to multiple application tables by using filelayout and application engine with code ?

Ans: You can use App Engine,File Layout and Component Interface concept for this. 

Steps: 

1) Create Component Interface for the component in which your application tables exists 
2) Now write app engine program. 
Steps in AE: 
a) Read the data to staging table by using file layout. 
b) after loading the data, use CI concept to load the data in to your application tables.

Q: My exact requirement is to upload the data from CSV file to Peoplesoft 

Delivered tables using staging tbale,file layout and app engine. For this requirement, I have created a staging table, file layout and when I dragged file layout into app engine Peoplecode action then I ran the process data will upload to the staging table only. 

But in this I need to send data into 4 application tables from this staging table. 

Can you please suggest what actions would be needed for this requirement? 

Ans:After loading the data in to the staging table by using file layout concept, do the following 

1) Create the CI for your component (in which your application tables exists). 
2) Test the component interface after giving the security. 
3) Create one more step (people code action) after the file layout coding in the App Engine. 
4) Drag and drop the CI in to the people code action. 
5) Get the staging table values one by one into state record and assign these values to the your component rec fields (i.e your application tables). 

Or alternatively , after step 4 above, there could be one peoplecode action where there is a loop through to upload data from your staging tables to App tables by using the classic methods of inserting data using peoplecode.

Do Actions in an Application Engine: A Closer Look

Do Actions in an Application Engine: A Closer Look

I have sometimes found the Do actions in an Application Engine to be pretty confusing – especially the nature of iterations that happen with each of these actions. I thought it would be informative to share some insights into the working of these actions in an AE program.

Consider the following scenario:

If we have a select statement as below: 

Select emplid from PS_JOB where action_dt = sysdate order by emplid.

Suppose executing this query on your DB returns 10 rows. To understand the working of the Do actions, if I embed this query in an AE and print the emplid in a file, how many EMPLIDs will be printed in case of each Do actions?

My AE step would looks like this:

The Do When in the above diagram can be replaced by Do While and Do Select, while checking for the number of iterations. The Do Actions will contain the SQL select statement as mentioned before and the Peoplecode will write the Emplid value to a File. In case of Do Until, Peoplecode will precede Do Until (as Do Until is the last action).

Coming back to our original question.

How many times will the EMPLID be written in the file in each of these cases??

Do When: Do When is a loop entry criteria. This will always be executed once and only once as long as the SQL statement fetches a row. Thus, with Do Until, one row will be printed. This is pretty straight forward.

Do While: All those of you who thought that the Do While loop will iterate 10 times will need to rethink the working of this loop. Do While will c

ontinue executing until at least one row is fetched. The background execution logic of a Do While loop is as follows: Every time, the AE executes the SQL select statement in the Do While Action, if it returns a row it will proceed to further actions, if not the step will be terminated. What is to be understood here is that, in the absence of a loop termination criteria (or in other words logic to discard already selected rows) the Select stat

ement inside a Do While will always fetch the same row. This will mean that a query like the one we have written (select emplid from ps_job) will go into an infinite loop, as there is no termination criterion for this SQL. Thus, this will go into an infinite loop and the same EMPLID (the first Id returned by the query) will be printed in the file. Always keep in mind that a loop termination criterion is absolutely necessary in case of a Do While.

Do Until: Do Until is uncomplicated. It will execute until a row is returned by the query and this ensures that the step is executed atleast once. As the query returns a row, the loop gets terminated after the first iteration itself.

Do Select: Do Select is the most commonly used Do action. But I left Do Select for the last because even this is kind of tricky like Do While. I do not know how many of you have noticed the various types of a Do Select action:

What concerns us out here is the difference between Select/Fetch and Reselect. Will there be any difference if I run the Do Select in these two different modes?

Yes, the working of the Do Select is completely different in these two cases. In case of a Select/Fetch the loop will iterate 10 times, while for Reselect an infinite loop is triggered (exactly similar to a Do While). The reason lies in the difference in working of these two Do Select types. Select/Fetch hits the Db just once, fetches all the values of the Select statement and puts it in a cursor and moves through the cursor during each iteration. This allows a sequential processing which is so integral to row by row processing in an Application Engine. While Reselectqueries the DB for each iteration and picks up the first row returned by the select statement and puts it in a cursor. Thus, at any given point of time there will be only one row in the cursor of a Reselect type Do Select action. Moreover, Reselect will be slower than a Select/Fetch due to the DB trips involved in the former. Why does Reselect go into an infinite loop? This happens because for each iteration, the same SQL select statement is fired and the row returned first is buffered into the cursor (unlike a Select/Fetch where the SQL is executed against the DB once and then the AE moves successively through the cursor and terminates once it reaches the end of the cursor). Thus, in case of a Do Select of Reselect type, we will get a result similar to Do While – an infinite loop with the same EMPLID printed.

So what is the difference between a Do Select of Reselect type and a Do While? Remember that Reselect is used when the Restart function of an AE is turned on. A Select/Fetch Do Select does not execute commits for the entire action and thus will not be useful in case of a restart. This deficiency is overcome by the Reselect type, where commits are executed during the Step execution. At hindsight, I would always recommend to use theRestartable function (which is similar to Select/Fetch but with commits turned on) over the Reselect option. Coming to the difference between a Do Select (Reselect) and a Do While boils down the elementary question – why do we have two separate actions if they perform the same function? Do While and Do Select are fundamentally different, the first is similar to a FOR loop (executes for a definite number of times), while the latter is similar to a WHILE loop (executes till a condition is true). That is exactly the reason why a Do While comes before a Do Select.