Showing posts with label PeopleSoft. Show all posts
Showing posts with label PeopleSoft. Show all posts

Saturday, February 7, 2009

Import Data from Files Using Application Engine

You can use application engine to import files into tables in your PeopleSoft Database. Prior to version 8, the only way to programmatically do this was by using SQR. However, you now have the ability to create a File Layout Definition object and using Peoplecode, you can load a file directly into your database.

Application Engine is extremely powerful when utilized correctly. For example, you can utilize functionality such as peoplecode, SQL, Do While, Do Until, and Do Select. Therefore, you have the ability to manipulate the data just as you were capable of doing using SQR.

I'm going to walk through loading data from a flat file into a table using Application Engine. This example is a very simple one and is easy to follow. So lets get started!

First and foremost, you will need to create a File Layout Definition.

For this example we are using a table that we have defined in Application Designer named "N_SDN_TBL". This table is constructed as follows:

---------------------- --------------
N_SDN_ID decimal 9,10
N_SDN_NAME char 50
N_SDN_TYPE char 12
N_SDN_SNCTION_PGM char 20
N_SDN_TITLE char 180
N_SDN_VSSL_CALLSGN char 8
N_SDN_VESSEL_TYPE char 25
N_SDN_VSSLTONNAGE char 14
N_SDN_GRT_REG_TON char 8
N_SDN_VESSEL_FLAG char 40
N_SDN_VSSL_OWNER char 150
N_SDN_REMARKS char 254

When you define a File Layout, you can define it with a format of FIXED, CSV, or XML. In this Example we are using Fixed. From Application Designer, simply create a new File Layout object. Right click on the File Layout and Insert a Record. For this example we choose N_SDN_TBL. Once you choose the record App Designer will add the record and its fields to the layout.

The Next step is to adjust the start and ending positions for each field. Simply double click on a field and enter the start and ending position for this field the way it is in your flat file. Once you go through and complete this process for each field you can save your file layout and start building your application engine program.

All we need for this program are two simple steps.

The first step will be a simple SQL step to truncate the table N_SDN_TBL.Second, we add another step for PeopleCode. The PeopleCode for loading this data into the table is written below.


/**********************************************************
FUNCTION TO IMPORT DATA
**********************************************************/
Function ImportSegment(&RS2 As Rowset, &RSParent As Rowset)
Local Rowset &RS1, &RSP;
Local string &RecordName;
Local Record &REC2, &RECP;
Local SQL &SQL1;
Local integer &I, &L;
&SQL1 = CreateSQL("%Insert(:1)");
&RecordName = "RECORD." | &RS2.DBRecordName;
&REC2 = CreateRecord(@(&RecordName));
&RECP = &RSParent(1).GetRecord(@(&RecordName));
For &I = 1 To &RS2.ActiveRowCount
&RS2(&I).GetRecord(1).CopyFieldsTo(&REC2);
If (EditRecord(&REC2)) Then
&SQL1.Execute(&REC2);
&RS2(&I).GetRecord(1).CopyFieldsTo(&RECP);
For &L = 1 To &RS2.GetRow(&I).ChildCount
&RS1 = &RS2.GetRow(&I).GetRowset(&L);
If (&RS1 <> Null) Then
&RSP = &RSParent.GetRow(1).GetRowset(&L);
ImportSegment(&RS1, &RSP);
End-If;
End-For;
If &RSParent.ActiveRowCount > 0 Then
&RSParent.DeleteRow(1);
End-If;
Else
&LOGFILE.WriteRowset(&RS);
&LOGFILE.WriteLine("****Correct error in this record and delete all error messages");
&LOGFILE.WriteRecord(&REC2);
For &L = 1 To &RS2.GetRow(&I).ChildCount
&RS1 = &RS2.GetRow(&I).GetRowset(&L);
If (&RS1 <> Null) Then
&LOGFILE.WriteRowset(&RS1);
End-If;
End-For;
End-If;
End-For;
End-Function;



Rem *****************************************************************;
rem * Define our Objects *;
rem *****************************************************************;
Local File &FILE1
Local Record &REC1
Local SQL &SQL1
Local Rowset &RS1
Local integer &M


/******************************************************************/
/* LOAD A FILE CALLED SDN.FF */
/******************************************************************/

/* CREATE A HANDLE TO THE FILE AND THE LOGFILE SETTING THE ATTRIBUTES */

&FILE1 = GetFile("D:\FS800\N_INTERFACES\SDN\SDN.FF", "r", "a", %FilePath_Absolute);
&LOGFILE = GetFile("D:\FS800\N_INTERFACES\SDN\SDN.FF.err", "W", %FilePath_Absolute);

/* LOAD OUR FILELAYOUT TO THE FILE HANDLE */

&FILE1.SetFileLayout(FileLayout.N_SDN_TBL);
&LOGFILE.SetFileLayout(FileLayout.N_SDN_TBL);

/*************************************************************
CREATE A ROWSET FOR THE FILE AND ONE FOR THE RECORD THAT WE
WILL BE LOADING THEN READ EACH ROW OF THE FILE AND INSERT
IT INTO OUR TABLE
**************************************************************/

&RS1 = &FILE1.CreateRowset();
&RS = CreateRowset(Record.N_SDN_TBL);
&SQL1 = CreateSQL("%Insert(:1)");
&RS1 = &FILE1.ReadRowset();

While &RS1 <> Null;
ImportSegment(&RS1, &RS);
&RS1 = &FILE1.ReadRowset();
End-While;

&FILE1.Close();
&LOGFILE.Close();

I hope that this example was helpful in providing you a simple, quick, and easy way
to load data from a fixed field length flat file into a table. There really is no need to continue to do these types of applications using SQR. You should start programming using Application engine for any type of process that does not require a fancy report. Remember, Application Engine programs are objects that you can upgrade. SQR's are not.

Monday, January 26, 2009

Sending Mails Without SMTP Server

MCF class does not need app server SMTP server and Port settings

import PT_MCF_MAIL:SMTPSession;
import PT_MCF_MAIL:MCFEmail;
import PT_MCF_MAIL:MCFMultipart;
import PT_MCF_MAIL:MCFBodyPart;
import PT_MCF_MAIL:MCFPart;
import PT_MCF_MAIL:*;

&OuboundEmail = create PT_MCF_MAIL:MCFOutboundEmail(); &OuboundEmail.smtpserver = "";
&OuboundEmail.SMTPPort = 25;
&OuboundEmail.from = "xyz@abc.com";
&OuboundEmail.recipients = 'pqr@lmn.com';
&OuboundEmail.subject = "Test Subject";
&OuboundEmail.text='' ;
&ret = &OuboundEmail.send();

Number to Word

Converting number to words...

1.

select CASE WHEN LENGTH(1234567892) >= 10 THEN initcap(to_char(to_date(substr(ROUND(1234567892),1,3),'J'),'JSP')) ' Crore ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 9 THEN initcap(to_char(to_date(substr(ROUND(1234567892),1,2),'J'),'JSP')) ' Crore ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 8 THEN initcap(to_char(to_date(substr(ROUND(1234567892),1,1),'J'),'JSP')) ' Crore ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 7 THEN initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 6 THEN initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,1),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) < class="blsp-spelling-error" id="SPELLING_ERROR_47">initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) END as words FROM DUAL

2.

select initcap(to_char(to_date(1234567,'J'),'JSP')) from dual
This would produce the following output:
One Million Two Hundred Thirty-Four Thousand Five Hundred Sixty-Seven
This usual approach matches with Indian system till tens of thousands.

About PeopleSoft DataBase

Peoplesoft Database separated into 3 parts....

1.System Catalog
2.PeopleTools
3.Application Data

System Catalog records the information about the database and its content. means Table Names, Column Names and Index Names Etc...
it contaions Database Metadata. it tracks all objects in entie database.
Ex:-DBA_OBJECTS.

PeopleTool record the information about the defination thats make up our peopleSoft Appliacation , which means its stores record names, page names annd component names etc.... it contains metadata about the peopleSoft application installed on the database.

Application Data records the information which is entered by the customers.

Webserver presents the Data.

Appserver validates the Business Logic.

Database Server stores and updates the data.

Tuesday, January 6, 2009

Role Of Component Processor In Peoplesoft

If I had to tell someone what the most important concept they need to understand as a PeopleSoft developer, I would quickly answer. It is the Component Processor and how it allocates Buffer space, how it places information in the buffer, and how to ensure that you maximize its use.
The PeopleSoft application is an extremely powerful and highly configurable product that runs many different applications. Every application that is accessed online through the PeopleSoft Internet Architecture is part of a component. When you navigate to a particular content reference or menu item to access a particular page, the Component Processor is what performs all of the activity including executing SQL, PeopleCode, loading the component/page, and filling the buffer with the necessary data that was requested. More importantly, all of these activities happen in a specific order.
It is this overall process that every PeopleSoft developer and analyst needs to understand thoroughly. If you understand how the component processor works and how it allocates buffer space, than debugging, performance tuning, designing, and developing your PeopleSoft applications will be simple for you. However, if you miss this concept or don’t fully understand it, your design and development process and your online performance will be inadequate. In addition, it will become more difficult for you to debug your applications without understanding the component processor.
The Component Processor is the core engine of the PeopleSoft PIA and is responsible for the following:
• Builds and displays the search page so the user can select a high level key to retrieve the data.
• Performs search processing, where it will retrieve and save the search key values that were selected for the component.
• Makes calls to the database to retrieve the metadata that is needed to build the component.
• Builds the component, including all of its pages, fields, PeopleCode programs, and data, creating buffers for the component data.
• Performs any additional processing for the component or the page. • Displays the component and waits for user action.
If you build PeopleSoft applications and write PeopleCode, than you must fully understand the component processor and how it allocated buffer space and places objects and data into the buffer. Every time you access a component, the component processor queries the database and moves relevant data from the database to the application server. The Component processor manages this data according to special rules. Therefore, your Component design has a crucial impact and it is extremely important to understand how the buffers are allocated and maintained by the component processor.
There is a lot more detail to discuss in regards to the component processor and the component buffer. Too much to place in a single blog/article.

PeopleSoft Homepage Greeting

Have you ever wanted to display the database and username on every page like Peoplesoft used to do in version 7.x?
Add the following peoplecode in the application package PT_BRANDING under brandingbase to display user name/database name on every page.
Replace all occurrences of
&Greeting = &Portal.Homepage.Greeting
with
&Greeting = &Portal.Homepage.Greeting / " " / %UserId / " " / "(" / %DbName / ")"