Blog pro de Jean-Baptiste HEREN - Peoplesoft

Finding one component location in the peoplesoft portal

Jean-Baptiste HEREN — Thu, 26 May 2011 11:50:00 +0200

Just replace the 'MY_COMPONENT_GBL' parameter in the following sql script. This works for MS SQl Server. You can add more levels by adding Outer Joins.

[SQL]
/* Where is my component in the portal */
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 :
  AND A.PORTAL_URI_SEG2 = 'MY_COMPONENT_GBL'

Thanks to François R. for the following code which will do the same recursively. (Oracle only)

[SQL]
SELECT DISTINCT RTRIM(REVERSE
(SYS_CONNECT_BY_PATH(REVERSE
((SELECT B.PORTAL_LABEL FROM PSPRSMDEFNLANG B 
WHERE A.PORTAL_NAME = B.PORTAL_NAME
AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME
AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE
AND B.LANGUAGE_CD = 'FRA')), ' > ')), ' > ') CHEMIN
FROM PSPRSMDEFN A
WHERE A.PORTAL_NAME = 'EMPLOYEE'
AND A.PORTAL_PRNTOBJNAME = 'PORTAL_ROOT_OBJECT'
START WITH PORTAL_URI_SEG2 = 'MY_COMPONENT'
CONNECT BY PRIOR A.PORTAL_PRNTOBJNAME = A.PORTAL_OBJNAME;

Peoplesoft Component interface HOWTO

Jean-Baptiste HEREN — Thu, 13 Jan 2011 06:12:00 +0100

Here is a tutorial explaining how you can create a function containing one Component INterface, and make use of it from any peolplecode.

Following example takes the PO Receipt Component to make automatic receipts from a barcode screen.

1- Create a new Component Interface with Application designer

In application Designer, Select File->New->Component Interface

Select The Component you want to access to

Answer YES to following question

Now your componet has been created, you can just save IT

2- Write Peoplecode for you CI

2.1- Drag and drop the Component Interface to the Peoplecode text window. Il will create a template to adapt:

[vb]
Local File &fileLog; 
Local ApiObject &oSession, &oMYCINAMEPoRecv; 
 
Function errorHandler() 
	Local ApiObject &oPSMessageCollection, &oPSMessage; 
	Local number &i; 
	Local String &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType; 
 
	&oPSMessageCollection = &oSession.PSMessages; 
	For &i = 1 To &oPSMessageCollection.Count 
		&oPSMessage = &oPSMessageCollection.Item(&i); 
		&sErrMsgSetNum = &oPSMessage.MessageSetNumber; 
		&sErrMsgNum = &oPSMessage.MessageNumber; 
		&sErrMsgText = &oPSMessage.Text; 
		&fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText); 
	End-For; 
	rem ***** Delete the Messages from the collection *****; 
	&oPSMessageCollection.DeleteAll(); 
End-Function; 
 
try 
	rem ***** Set the Log File *****; 
	&fileLog = GetFile("C:\temp\MY_CI_NAME.log", "w", "a", %FilePath_Absolute); 
	&fileLog.WriteLine("Begin"); 
	rem ***** Get current PeopleSoft Session *****; 
	&oSession = %Session; 
 
	rem ***** Set the PeopleSoft Session Error Message Mode *****; 
	rem ***** 0 - None *****; 
	rem ***** 1 - PSMessage Collection only (default) *****; 
	rem ***** 2 - Message Box only *****; 
	rem ***** 3 - Both collection and message box *****; 
	&oSession.PSMessagesMode = 1; 
 
	rem ***** Get the Component Interface *****; 
	&oMYCINAMEPoRecv = &oSession.GetCompIntfc(CompIntfc.MY_CI_NAME_RECV); 
	If &oMYCINAMEPoRecv = Null Then 
		errorHandler(); 
	throw CreateException(0, 0, "GetCompIntfc failed"); 
	End-If; 
 
	rem ***** Set the Component Interface Mode *****; 
	&oMYCINAMEPoRecv.InteractiveMode = False; 
	&oMYCINAMEPoRecv.GetHistoryItems = True; 
	&oMYCINAMEPoRecv.EditHistoryItems = False; 
 
	rem ***** Set Component Interface Get/Create Keys *****; 
 
	rem ***** Execute Get *****; 
	If Not &oMYCINAMEPoRecv.Get() Then 
		rem ***** No rows exist for the specified keys.*****; 
		errorHandler(); 
		throw CreateException(0, 0, "Get failed"); 
	End-If; 
  
	rem ***** Begin: Get/Set Component Interface Properties *****; 
	rem ***** Get/Set Level 0 Field Properties *****; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.INV_LOT_ID = " | &oMYCINAMEPoRecv.INV_LOT_ID); 
	rem &oMYCINAMEPoRecv.INV_LOT_ID = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.INV_ITEM_ID = " | &oMYCINAMEPoRecv.INV_ITEM_ID); 
	rem &oMYCINAMEPoRecv.INV_ITEM_ID = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.SERIAL_ID = " | &oMYCINAMEPoRecv.SERIAL_ID); 
	rem &oMYCINAMEPoRecv.SERIAL_ID = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.STORAGE_AREA = " | &oMYCINAMEPoRecv.STORAGE_AREA); 
	rem &oMYCINAMEPoRecv.STORAGE_AREA = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.STOR_LEVEL_1 = " | &oMYCINAMEPoRecv.STOR_LEVEL_1); 
	rem &oMYCINAMEPoRecv.STOR_LEVEL_1 = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.STOR_LEVEL_2 = " | &oMYCINAMEPoRecv.STOR_LEVEL_2); 
	rem &oMYCINAMEPoRecv.STOR_LEVEL_2 = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.STOR_LEVEL_3 = " | &oMYCINAMEPoRecv.STOR_LEVEL_3); 
	rem &oMYCINAMEPoRecv.STOR_LEVEL_3 = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.STOR_LEVEL_4 = " | &oMYCINAMEPoRecv.STOR_LEVEL_4); 
	rem &oMYCINAMEPoRecv.STOR_LEVEL_4 = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.CONTAINER_ID = " | &oMYCINAMEPoRecv.CONTAINER_ID); 
	rem &oMYCINAMEPoRecv.CONTAINER_ID = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.UNIT_OF_MEASURE = " | &oMYCINAMEPoRecv.UNIT_OF_MEASURE); 
	rem &oMYCINAMEPoRecv.UNIT_OF_MEASURE = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.RECEIVER_ID = " | &oMYCINAMEPoRecv.RECEIVER_ID); 
	rem &oMYCINAMEPoRecv.RECEIVER_ID = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.QTY = " | &oMYCINAMEPoRecv.QTY); 
	rem &oMYCINAMEPoRecv.QTY = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.SCHED_NBR = " | &oMYCINAMEPoRecv.SCHED_NBR); 
	rem &oMYCINAMEPoRecv.SCHED_NBR = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.SETID = " | &oMYCINAMEPoRecv.SETID); 
	rem &oMYCINAMEPoRecv.SETID = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.SHIPTO_ID = " | &oMYCINAMEPoRecv.SHIPTO_ID); 
	rem &oMYCINAMEPoRecv.SHIPTO_ID = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.VENDOR_ID = " | &oMYCINAMEPoRecv.VENDOR_ID); 
	rem &oMYCINAMEPoRecv.VENDOR_ID = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.SHIPMENT_NO = " | &oMYCINAMEPoRecv.SHIPMENT_NO); 
	rem &oMYCINAMEPoRecv.SHIPMENT_NO = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.CARRIER_ID = " | &oMYCINAMEPoRecv.CARRIER_ID); 
	rem &oMYCINAMEPoRecv.CARRIER_ID = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.BILL_OF_LADING = " | &oMYCINAMEPoRecv.BILL_OF_LADING); 
	rem &oMYCINAMEPoRecv.BILL_OF_LADING = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.BUSINESS_UNIT_PO = " | &oMYCINAMEPoRecv.BUSINESS_UNIT_PO); 
	rem &oMYCINAMEPoRecv.BUSINESS_UNIT_PO = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.PO_ID = " | &oMYCINAMEPoRecv.PO_ID); 
	rem &oMYCINAMEPoRecv.PO_ID = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.LINE_NBR = " | &oMYCINAMEPoRecv.LINE_NBR); 
	rem &oMYCINAMEPoRecv.LINE_NBR = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.BUSINESS_UNIT_IN = " | &oMYCINAMEPoRecv.BUSINESS_UNIT_IN); 
	rem &oMYCINAMEPoRecv.BUSINESS_UNIT_IN = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.VENDOR_LOT_ID = " | &oMYCINAMEPoRecv.VENDOR_LOT_ID); 
	rem &oMYCINAMEPoRecv.VENDOR_LOT_ID = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.TAG_NUMBER = " | &oMYCINAMEPoRecv.TAG_NUMBER); 
	rem &oMYCINAMEPoRecv.TAG_NUMBER = [*]; 
	&fileLog.WriteLine("&oMYCINAMEPoRecv.TRANSACTION_CODE = " | &oMYCINAMEPoRecv.TRANSACTION_CODE); 
	rem &oMYCINAMEPoRecv.TRANSACTION_CODE = [*]; 
	rem ***** End: Get/Set Component Interface Properties *****; 
 
	rem ***** Execute Save *****; 
	rem If Not &oMYCINAMEPoRecv.Save() Then; 
	rem 	errorHandler(); 
	rem 	throw CreateException(0, 0, "Save failed"); 
	rem End-If; 
 
	rem ***** Execute Cancel *****; 
	rem If Not &oMYCINAMEPoRecv.Cancel() Then; 
	rem 	errorHandler(); 
	rem 	throw CreateException(0, 0, "Cancel failed"); 
	rem End-If; 
 
catch exception &ex 
	rem Handle the exception; 
	&fileLog.WriteLine(&ex.ToString()); 
end-try; 
&fileLog.WriteLine("End"); 
&fileLog.Close();

2.2- Make changes so that you can include the CI code in a single function

This way, your CI will be easyer to use from any application, with a single function call.

Change the errorHandler function to get File & Session as a parameter:

[vb]
Function errorHandler(&fileLog As File, &oSession As ApiObject)
   Local ApiObject &oPSMessageCollection, &oPSMessage;
   Local number &i;
   Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;
   
   &oPSMessageCollection = &oSession.PSMessages;
   For &i = 1 To &oPSMessageCollection.Count
      &oPSMessage = &oPSMessageCollection.Item(&i);
      &sErrMsgSetNum = &oPSMessage.MessageSetNumber;
      &sErrMsgNum = &oPSMessage.MessageNumber;
      &sErrMsgText = &oPSMessage.Text;
      &fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);
   End-For;
   rem ***** Delete the Messages from the collection *****;
   &oPSMessageCollection.DeleteAll();
End-Function;

Create Functions containing Your CI operations, so that it will be easier to integrate in any peoplecode program.

[vb]
// Function returns a rowset from MY_BCODE_TBL corresponding to &barcode parameter
Function getBarcodeRecord(&barcode As string) Returns Record
   Local Rowset &RwsBarcode;
   Local Record &REcBarcode;
   
   &RwsBarcode = CreateRowset(Record.MY_BCODE_TBL);
   &RwsBarcode.Fill("WHERE RECV_CARD_ID = :1", &barcode);
   
   &REcBarcode = CreateRecord(Record.MY_BCODE_TBL);
   &REcBarcode = &RwsBarcode(1).GetRecord(1);
   
   Return &REcBarcode;
End-Function;

// This Function is scheduling a job
Function RunJobRecv(&barcode As string) Returns boolean
   Local boolean &success = False;
   Local ProcessRequest &prcsrqst;
   Local JavaObject &Obj;
   Local Record &RecRECVLOAD;
   Local Record &RecRECVPUSH;
   Local Record &RecINPSSTGE;
   
   // Select data using barcode ID - start;
   Local Record &REcBarcode;
   &REcBarcode = getBarcodeRecord(&barcode);
   // Select data using barcode ID - end;
   
   &jobname = "MYRECVJOB";
   
   // Create Data In Needed Run Control Parameters;
   // 01 - RECVLOAD;
   &RecRECVLOAD = CreateRecord(Record.RUN_CNTL_RECVLD);
   &RecRECVLOAD.OPRID.Value = %UserId;
   &RecRECVLOAD.RUN_CNTL_ID.Value = &jobname;
   &RecRECVLOAD.RECVLOAD_OPT.Value = "2";
   &RecRECVLOAD.BUSINESS_UNIT.Value = &REcBarcode.BUSINESS_UNIT_IN.Value;
   &RecRECVLOAD.WHERE_CLAUSE.Value = "BUSINESS_UNIT = '" | &RecRECVLOAD.BUSINESS_UNIT.Value | "'";
   If Not &RecRECVLOAD.Insert() Then
      &RecRECVLOAD.Update();
   End-If;
   
   // 02 - RUN_PO_RECEIPT;
   &RecRECVPUSH = CreateRecord(Record.RECV_RUN_CNTL);
   &RecRECVPUSH.OPRID.Value = %UserId;
   &RecRECVPUSH.RUN_CNTL_ID.Value = &jobname;
   &RecRECVPUSH.RECEIVER_ID.Value = "";
   &RecRECVPUSH.BUSINESS_UNIT.Value = &REcBarcode.BUSINESS_UNIT_IN.Value;
   If Not &RecRECVPUSH.Insert() Then
      &RecRECVPUSH.Update();
   End-If;
   
   REM Schedule the Job;
   &prcsrqst = CreateProcessRequest("PSJob", &jobname);
   &prcsrqst.RunControlID = &jobname;
   &prcsrqst.JobName = &jobname;
   &prcsrqst.Schedule();
   
   If &prcsrqst.Status = 0 Then
      /* Schedule succeeded. */
      &success = True;
   Else
      /* Process (job) not scheduled, do error processing */
      &success = False;
   End-If;
   /*
   &Obj = CreateJavaObject("java.lang.Thread");
   &Obj.start();
   
   &RunStatus = "0";
   While &RunStatus <> "9" And
         &RunStatus <> "3" And
         &RunStatus <> "1" And
         &RunStatus <> "10"
      REM Directly use the Java sleep() Method for 5 seconds;
      &Obj.sleep(5000);
      SQLExec("SELECT RUNSTATUS FROM PSPRCSRQST WHERE PRCSINSTANCE = :1", &prcsrqst.ProcessInstance, &RunStatus);
      REM MessageBox(0, "", 0, 0, "Sleep(), Runstatus = %1", &RunStatus);
   End-While;
   &Obj.stop();
   
   rem WriteLog("Process MYRECVJOB is Finished");
   
   If &prcsrqst.Status = 10  Or
         &prcsrqst.Status = 3  Then
      REM Process your errors;
      &success = False;
   Else
      REM Process your success;
      &success = True;
   End-If;
   */
   Return &success;
End-Function;

// Adds data to a log table
Function LogBarcode(&barcode As string, &qty As number, &transaction_code As string) Returns boolean
   Local Record &RecLog;
   &RecLog = CreateRecord(Record.PH_RECV_BCD_LOG);
   &RecLog.RECV_CARD_ID.Value = &barcode;
   &RecLog.QTY.Value = &qty;
   &RecLog.TRANSACTION_CODE.Value = &transaction_code;
   &RecLog.DT_TIMESTAMP.Value = %Datetime;
   &RecLog.OPRID.Value = %UserId;
   
   Return &RecLog.Insert();
End-Function;

// ErrorHandler Function
Function errorHandler(&fileLog As File, &oSession As ApiObject)
   Local ApiObject &oPSMessageCollection, &oPSMessage;
   Local number &i;
   Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;
   
   &oPSMessageCollection = &oSession.PSMessages;
   For &i = 1 To &oPSMessageCollection.Count
      &oPSMessage = &oPSMessageCollection.Item(&i);
      &sErrMsgSetNum = &oPSMessage.MessageSetNumber;
      &sErrMsgNum = &oPSMessage.MessageNumber;
      &sErrMsgText = &oPSMessage.Text;
      &fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);
   End-For;
   rem ***** Delete the Messages from the collection *****;
   &oPSMessageCollection.DeleteAll();
End-Function;

/* Jean-Baptiste HEREN
   This function Gets Barcode and Qty from the barcode entry screen- Transaction 103;
 - Then it Uses a Component Interface to simulate the manual entry on;
   The Data Exchange > Create Transactions > Receiving > Purchasing Receiving;*/
Function WriteReceipt(&barcode As string, &qty As number) Returns boolean
   
   Local File &fileLog;
   Local ApiObject &oSession, &oMYCINAMEPoRecv;
   // Jean-Baptiste HEREN - Select data using barcode ID - start;
   Local Record &REcBarcode;
   &REcBarcode = getBarcodeRecord(&barcode);
   // Jean-Baptiste HEREN - Select data using barcode ID - end;
   
   try
      rem ***** Set the Log File *****;
      If %UserId = "jean-baptiste.heren" Then
         &fileLog = GetFile("\\MyMachine\temp\CI\MY_CI_NAME_RECV_WriteReceipt.log", "w", "a", %FilePath_Absolute);
      Else
         &fileLog = GetFile("MY_CI_NAME_RECV.log", "w", "a", %FilePath_Relative);
      End-If;
      &fileLog.WriteLine("Begin");
      rem ***** Get current PeopleSoft Session *****;
      &oSession = %Session;
      
      rem ***** Set the PeopleSoft Session Error Message Mode *****;
      rem ***** 0 - None *****;
      rem ***** 1 - PSMessage Collection only (default) *****;
      rem ***** 2 - Message Box only *****;
      rem ***** 3 - Both collection and message box *****;
      &oSession.PSMessagesMode = 1;
      
      rem ***** Get the Component Interface *****;
      &oMYCINAMEPoRecv = &oSession.GetCompIntfc(CompIntfc.MY_CI_NAME_RECV);
      If &oMYCINAMEPoRecv = Null Then
         errorHandler(&fileLog, &oSession);
         throw CreateException(0, 0, "GetCompIntfc failed");
      End-If;
      
      rem ***** Set the Component Interface Mode *****;
      &oMYCINAMEPoRecv.InteractiveMode = True;
      &oMYCINAMEPoRecv.GetHistoryItems = True;
      &oMYCINAMEPoRecv.EditHistoryItems = False;
      
      rem ***** Set Component Interface Get/Create Keys *****;
      
      rem ***** Execute Get *****;
      If Not &oMYCINAMEPoRecv.Get() Then
         rem ***** No rows exist for the specified keys.*****;
         errorHandler(&fileLog, &oSession);
         throw CreateException(0, 0, "Get failed");
      End-If;
      
      
      rem ***** Begin: Get/Set Component Interface Properties *****;
      rem ***** Get/Set Level 0 Field Properties *****;
      
      // Jean-Baptiste HEREN - Write the session related to creation of a new reception line;
      &transaction_code = "0103";
      &oMYCINAMEPoRecv.TRANSACTION_CODE = &transaction_code;
      // Jean-Baptiste HEREN - Fill with the barcode ID data;
      &oMYCINAMEPoRecv.SETID = &REcBarcode.SETID.Value;
      &oMYCINAMEPoRecv.SHIPTO_ID = &REcBarcode.SHIPTO_ID.Value;
      &oMYCINAMEPoRecv.VENDOR_ID = &REcBarcode.VENDOR_ID.Value;
      &oMYCINAMEPoRecv.BUSINESS_UNIT_IN = &REcBarcode.BUSINESS_UNIT_IN.Value;
      &oMYCINAMEPoRecv.CARRIER_ID = &REcBarcode.CARRIER_ID.Value;
      &oMYCINAMEPoRecv.BILL_OF_LADING = &REcBarcode.BILL_OF_LADING.Value;
      &oMYCINAMEPoRecv.BUSINESS_UNIT_PO = &REcBarcode.BUSINESS_UNIT_PO.Value;
      &oMYCINAMEPoRecv.PO_ID = &REcBarcode.PO_ID.Value;
      &oMYCINAMEPoRecv.LINE_NBR = &REcBarcode.LINE_NBR.Value;
      &oMYCINAMEPoRecv.UNIT_OF_MEASURE = &REcBarcode.UNIT_OF_MEASURE.Value;
      REM Still use Quantyty from Barcode screen;
      &oMYCINAMEPoRecv.QTY = &qty;
      rem ***** End: Get/Set Component Interface Properties *****;
      
      rem ***** Execute Save *****;
      If Not &oMYCINAMEPoRecv.Save() Then;
         errorHandler(&fileLog, &oSession);
         &success = False;
         throw CreateException(0, 0, "Save failed");
      Else
         &success = True;
         If Not LogBarcode(&barcode, &qty, &transaction_code) Then
            &fileLog.WriteLine("LogBarcode Failed");
         End-If;
      End-If;
      
      rem ***** Execute Cancel *****;
      rem If Not &oMYCINAMEPoRecv.Cancel() Then;
      rem 	errorHandler(&fileLog, &oSession);
      rem 	throw CreateException(0, 0, "Cancel failed");
      rem End-If;
      
   catch Exception &ex
      rem Handle the exception;
      &fileLog.WriteLine(&ex.ToString());
   end-try;
   &fileLog.WriteLine("End");
   &fileLog.Close();
   Return &success;
End-Function;

/* Jean-Baptiste HEREN
   This function Closes the Receipt session - Transaction 105;
   - It Uses a Component Interface to simulate the manual entry on;
     The Data Exchange > Create Transactions > Receiving > Purchasing Receiving;*/
Function CloseReceipt(&barcode) Returns boolean
   Local File &fileLog;
   Local ApiObject &oSession, &oMYCINAMEPoRecv;
   // Jean-Baptiste HEREN - Select data using barcode ID - start;
   Local Record &REcBarcode;
   &REcBarcode = getBarcodeRecord(&barcode);
   // Jean-Baptiste HEREN - Select data using barcode ID - end;
   
   try
      rem ***** Set the Log File *****;
      If %UserId = "jean-baptiste.heren" Then
         &fileLog = GetFile("\\MyMachine\temp\CI\MY_CI_NAME_RECV_CloseReceipt.log", "w", "a", %FilePath_Absolute);
      Else
         &fileLog = GetFile("MY_CI_NAME_RECV.log", "w", "a", %FilePath_Relative);
      End-If;
      &fileLog.WriteLine("Begin");
      rem ***** Get current PeopleSoft Session *****;
      &oSession = %Session;
      
      rem ***** Set the PeopleSoft Session Error Message Mode *****;
      rem ***** 0 - None *****;
      rem ***** 1 - PSMessage Collection only (default) *****;
      rem ***** 2 - Message Box only *****;
      rem ***** 3 - Both collection and message box *****;
      &oSession.PSMessagesMode = 1;
      
      rem ***** Get the Component Interface *****;
      &oMYCINAMEPoRecv = &oSession.GetCompIntfc(CompIntfc.MY_CI_NAME_RECV);
      If &oMYCINAMEPoRecv = Null Then
         errorHandler(&fileLog, &oSession);
         throw CreateException(0, 0, "GetCompIntfc failed");
      End-If;
      
      rem ***** Set the Component Interface Mode *****;
      &oMYCINAMEPoRecv.InteractiveMode = True;
      &oMYCINAMEPoRecv.GetHistoryItems = True;
      &oMYCINAMEPoRecv.EditHistoryItems = False;
      
      rem ***** Set Component Interface Get/Create Keys *****;
      
      rem ***** Execute Get *****;
      If Not &oMYCINAMEPoRecv.Get() Then
         rem ***** No rows exist for the specified keys.*****;
         errorHandler(&fileLog, &oSession);
         throw CreateException(0, 0, "Get failed");
      End-If;
      
      
      rem ***** Begin: Get/Set Component Interface Properties *****;
      rem ***** Get/Set Level 0 Field Properties *****;
      
      // Jean-Baptiste HEREN - Write the session related to creation of a new reception line;
      &qty = 0;
      &transaction_code = "0105";
      
      &oMYCINAMEPoRecv.TRANSACTION_CODE = &transaction_code;
      REM JBH - Fill with the barcode ID data.value;
      &oMYCINAMEPoRecv.SETID = &REcBarcode.SETID.Value;
      &oMYCINAMEPoRecv.SHIPTO_ID = &REcBarcode.SHIPTO_ID.Value;
      REM &oMYCINAMEPoRecv.VENDOR_ID = &REcBarcode.VENDOR_ID.Value;
      REM &oMYCINAMEPoRecv.BUSINESS_UNIT_IN = &REcBarcode.BUSINESS_UNIT_IN.value;
      &oMYCINAMEPoRecv.CARRIER_ID = &REcBarcode.CARRIER_ID.Value;
      &oMYCINAMEPoRecv.BILL_OF_LADING = &REcBarcode.BILL_OF_LADING.Value;
      REM &oMYCINAMEPoRecv.BUSINESS_UNIT_PO = &REcBarcode.BUSINESS_UNIT_PO.value;
      REM &oMYCINAMEPoRecv.PO_ID = &REcBarcode.PO_ID.value;
      REM &oMYCINAMEPoRecv.LINE_NBR = &REcBarcode.LINE_NBR.value;
      REM &oMYCINAMEPoRecv.UNIT_OF_MEASURE = &REcBarcode.UNIT_OF_MEASURE.value;
      REM Still use Quantyty from Barcode screen;
      REM &oMYCINAMEPoRecv.QTY = &qty;
      rem ***** End: Get/Set Component Interface Properties *****;
      
      rem ***** Execute Save *****;
	  // Jean-Baptiste HEREN - Usually, you want you CI to save your component at end, so uncomment the SAVE part
      If Not &oMYCINAMEPoRecv.Save() Then;
         errorHandler(&fileLog, &oSession);
         &success = False;
         throw CreateException(0, 0, "Save failed");
      Else
         &success = True;
         If Not LogBarcode(&barcode, &qty, &transaction_code) Then
            &fileLog.WriteLine("LogBarcode Failed");
         End-If;
      End-If;
      
      rem ***** Execute Cancel *****;
      rem If Not &oMYCINAMEPoRecv.Cancel() Then;
      rem 	errorHandler(&fileLog, &oSession);
      rem 	throw CreateException(0, 0, "Cancel failed");
      rem End-If;
      
   catch Exception &ex
      rem Handle the exception;
      &fileLog.WriteLine(&ex.ToString());
   end-try;
   &fileLog.WriteLine("End");
   &fileLog.Close();
   Return &success;
End-Function;

3- Write a program witch is using your CI

This kind of code is usually called from a Fast entry screen or External Interface

[vb]
// JBH - Declare Custom Functions Using Component Interface;
Declare Function WriteReceipt PeopleCode MY_RECV_TABLE.RECV_CARD_ID FieldFormula;
Declare Function CloseReceipt PeopleCode MY_RECV_TABLE.RECV_CARD_ID FieldFormula;

REM Run the Component Interface;
If All(MY_RECV_TABLE.RECV_CARD_ID.Value) And
      All(MY_RECV_TABLE.QTY.Value) Then
   MY_RECV_TABLE.RECV_CARD_ID.Value = RTrim(MY_RECV_TABLE.RECV_CARD_ID.Value);
   REM Check That This Barcode has not already been used;
   SQLExec("SELECT 1 FROM PS_PH_RECV_BCD_LOG WHERE RECV_CARD_ID = :1", MY_RECV_TABLE.RECV_CARD_ID.Value, &result);
   If &result <> 1 Then
      // Here we call our Component Interface
      If Not WriteReceipt(MY_RECV_TABLE.RECV_CARD_ID.Value, MY_RECV_TABLE.QTY.Value) Then
         WinMessage("Reception Failed!");
      Else
         SetCursorPos(%Page, MY_RECV_TABLE.RECV_CARD_ID, CurrentRowNumber());
         REM Save The succesfully recorded barcode;
         MY_RECV_TABLE.RECV_CARD_ID_OK.Value = MY_RECV_TABLE.RECV_CARD_ID.Value;
         REM Clean Screen;
         MY_RECV_TABLE.RECV_CARD_ID.Value = "";
         MY_RECV_TABLE.QTY.Value = 0;
      End-If;
   Else
      WinMessage("Card ID has already been scanned !");
      SetCursorPos(%Page, MY_RECV_TABLE.RECV_CARD_ID, CurrentRowNumber());
      REM Clean Screen;
      MY_RECV_TABLE.RECV_CARD_ID.Value = "";
      MY_RECV_TABLE.QTY.Value = 0;
   End-If;
End-If;

Graphic box en SQR

Jean-Baptiste HEREN — Tue, 07 Dec 2010 11:31:00 +0100

Voici comment imprimer une boite (un cadre) avec SQR Report.

!* Graphic Box
!* GRAPHIC (a, b, c) BOX d e f
!* a = line
!* b = colonn
!* c = width (largeur)
!* d = high (hauteur)
!* e = Thickness (épaisseur du trait)
!* f = Background opacity (Opacité du fond) (Grisé)

Exemple de boite avec Cadre & Grisée

[sql]
graphic  (#num_ligne,#col_gauche,#largeur_cellule) box #hauteur_cellule #epaisseur_trait #opacite_fond

Peoplesoft Auto Numbering

Jean-Baptiste HEREN — Fri, 20 Nov 2009 12:02:00 +0100

Peoplesoft Auto numbering allow to manage a counter for a specific ID. This definition allows to describe ID's size, beginning Segment and eventually diffrent parameter depending on SETID (like Order, Receipt, RMA ID's).

This post describes the diffrerent steps to :

Create a new Peoplesoft page & component, place it in a menu's folder an register in portal.
Setup Autonumbering on this new page's data.

1- Setup application designer

We will setup auto add new and modified objects to the current project. This is a very important thing to do when you make changes to PeopleSoft objects so that you will never forget anything ;when migrate your project to another environment.
In application designer; go in Tools -> Options menu.

2- Create the new field that will be used as an ID

This new Field MY_FIELD_ID will contain the generated ID. Set a comfortable size (10 to 20 CHARS Upper) and save it.

3- Create the Record witch will contain ID and related data

In Peoplesoft, Records are just Tables (or view). When you come from the Relational Database univers, It is quite confusing at the beginning, but each Editor has his own Language...
Create a new record for storing your ID and data.

Add you new Field as ID & Key, with some attached data.I you do not plan to use The Business Unit Key, you can keep only the Autonum Field as a Key.

4- Set record Group

Record Groups allow to link Each SETIDs with one Business Units, depending on Record, by using record groups.

This step is needed for auto numbering to work well, except if you prefer to "hard code" Directly the SETID in autonumbering function as described in the peoplecode exemple at the end of this post.

Create record group (or take an existing one)

Add your record(s)

Update Set Control Group (in this example, we use only the SHARE SETID)

UPDATE
PS_SET_CNTRL_GROUP
SET
SETID = 'SHARE' // or your SETID
FROM
PS_SET_CNTRL_GROUP
WHERE
REC_GROUP_ID = 'MY_01' // You Record Group

UPDATE
PS_SET_CNTRL_REC
SET
SETID = 'SHARE' // or your SETID
FROM
PS_SET_CNTRL_REC
WHERE
REC_GROUP_ID = 'MY_01' // You Record Group

5- Create one page to add/display data

Add You fields on the page & Save.

6- create the component witch contains the page

Set Component's Properties

Add your page(s) in it and set an Item Label as it will appear as the title of your page in tab.

7- Create the menu & Portal Folder witch contains the component

If you don't already have an available menu, create a menu and Bar Item.

Deploy the components tab in the project View & Drag the component to the menu

Change Menu Item Label. This label will appear as the menu entry text in the portal. Save.

Now we need In PIA, navigate to Peopletools -> Portal -> Structure & content.

Navigate as needed and select the add folder link, write name (Keep it Simple) and description then save.

Add the new folder to the Project (Think about Migration)

8- Register the component in the portal

This operation will create a content reference in the portal, to the component we have created, so that it is available in the portal.

Open your component in Application Designer and click on register component wizard button.

Select the menu you have created, as it contains our new component.

Select the Folder name you created, set a label and description. Default Content reference name is OK.

Then check your parameters and validate.

Now your content reference is created and available in the portal. You can checkit in the menu folder you specified.

9- Setup the standard auto numbering Type and ID

Two tables will be populated:
SELECT * FROM PS_AUTO_NUMFLD_TBL
SELECT * FROM PS_AUTO_NUM_TBL

Create a new translate value on NUM_TYPE Field for use in AUTO_NUMFLD_TBL. we will use this translate value when we will create the a new Auto Numbering number Type in the portal.

Add the new Auto numbering Entry

Create this definition for each SETID you need.

10- Write Peoplecode to manage the auto numbering on our ID

1- RECORDNAME.AUTONUMFIELD.Searchinit

If %Mode = "A" Then
MY_RECORD_TBL.MY_FIELD_ID.Value = "NEXT";
MY_RECORD_TBL.MY_FIELD_ID.Enabled = False;
End-If;

2- Create a Sequence Record View

Create a new record (view) with following structure and SQL code. This view will be called by the function AutoNumberCommit in next step.

SELECT A.SETID
, A.BEG_SEQ
, A.DESCR
FROM PS_AUTO_NUM_TBL A
WHERE A.NUM_TYPE='XXXX'

3- RECORDNAME.AUTONUMFIELD.Save PreChange

Declare Function AutoNumberCommit PeopleCode FUNCLIB_ORDENT.ORDER_NO FieldFormula;

REM Function AutoNumberCommit(&setidStr, &businessUnitStr, &recNameStr, &numTypeStr, &begSeqStr, &zeroPadB, &finalNbrStr) Returns boolean;

If Not (RecordDeleted(MY_RECORD_TBL.MY_FIELD_ID)) Then
   If MY_RECORD_TBL.MY_FIELD_ID.Value = "NEXT" Then
      &NUMBER_TYPE = "XXXX";
      &ZERO_PAD = "Y";
      &USERID = %UserId;
      &BEG_SEQ = "BEG";
      &RECORD_GROUP = Record.PH_KANBAN_AN_VW;
AutoNumberCommit(" ", MY_RECORD_TBL.BUSINESS_UNIT, &RECORD_GROUP, &NUMBER_TYPE, &BEG_SEQ, &ZERO_PAD, &FINAL_NUM);
//You can also set directly the SETID if you prefer.
//&continue = AutoNumberCommit("SHARE", " ", &RECORD_GROUP, &NUMBER_TYPE, &BEG_SEQ, &ZERO_PAD, &FINAL_NUM);
      MY_RECORD_TBL.MY_FIELD_ID.Value = &FINAL_NUM;
   End-If;
End-If;

11- Test onscreen

Now you should be able to test your screen by adding new value.

Saving your page will call the SavePreChange peoplecode containing AutoNumberCommit function and save your data with resulting ID.

Peoplecode File attachment

Jean-Baptiste HEREN — Fri, 13 Nov 2009 22:47:00 +0100

Peoplesoft allows File attachment from the web client. Here is a little HowTo.

// Peoplesoft permet assez facilement d'utiliser la fonction classique des interfaces web visant à "uploader" un fichier. Voici un aperçu de la méthode.

1- Add Needed Special Fields to your applications's record

First You will have to add two fields containing Filename to you current application's record.
Usually you add it to the primary record (aka MY_SCREEN_RECORD in following peoplecode) :

ATTACHSYSFILENAME (The system file name.)
ATTACHUSERFILE (The user file name).

This can be done by adding the standard FILE_ATTACH_SBR subrecord.

Then add three buttons to you page :

ADD : usually ATTACHADD field
VIEW : usually ATTACHVIEW field
DELETE: usually ATTACHDELETE field

You can use the standard work record FILE_ATTACH_WRK or just create one with your own fields, like in this example (more flexible).

2- (Optional) Create record to save File Informations & Data

If you want to use the database to store the file data, you will have to create a record containing this data.
This record must have the following Structure :
for example : MY_RECORD_STK

To achieve this, you can use thr standard subrecord FILE_ATTACH_SBR.

3- Add needed peoplecode & associate it with buttons

Here are peoplecodes examples, to be placed on Fieldchange Events, for the three different actions Add, view and remove buttons.

- Add Attachment

/*******************************************************************************/
/* Declarations */
Local string &URLDestination, &DirAndFilename, &FileType, &UserFile;
Local number &MaxSize;

/* Affectations */
/* here we set file destination - we can use a record but it is quite better to use an ftp server if possible ftp://ftp.my.address/my_folder */
&URLDestination = "record://MY_RECORD_STK";
&SUBDIRECTORY = "";
&DirAndFilename = "";
&FileType = "";
&UserFile = "";
&MaxSize = 0; /* no limit */

/* Check mandatory Field (file attahment decription) */
If None(MY_SCREEN_RECORD.DESCR254) Then
   MessageBox(0, "", 0, 0, "Fields are mandatory ");
   Return;
End-If;

/* Use reference IDs as filename, just for unicity */
&ATTACHUSERFILE = MY_SCREEN_RECORD.MY_UNIQUE_ID.Value;

/* reemove special characters */
&ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, " ", "_");
&ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, ";", "_");
&ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "+", "_");
&ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "%", "_");
&ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "&", "_");
&ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "'", "_");
&ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "!", "_");
&ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "@", "_");
&ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "#", "_");
&ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "$", "_");

/* Attaching file */
&return = AddAttachment(&URLDestination, &ATTACHUSERFILE, &FileType, &ATTACHSYSFILE, &MaxSize);

If &return = %Attachment_Success /* 0 */ Then
   Record.MY_SCREEN_RECORD.FLAG.Value = "Y";
   Record.MY_SCREEN_RECORD.ATTACHSYSFILENAME.Value = &ATTACHUSERFILE | &ATTACHSYSFILE;
   DoSaveNow();
Else
   MY_SCREEN_RECORD.FLAG.Value = "N";
   Evaluate &return
   When = 7 /* %Attachment_DestSystNotFound */
      MessageBox(0, "", 0, 0, "Cannot locate destination system '" | &URLDestination | "'. (Return code " | &return | " )");
      Break;
   When = 8 /* %Attachment_DestSystFailedLogin */
      MessageBox(0, "", 0, 0, "Unable to login to destination system for ftp. (Return code " | &return | " )");
      Break;
   When-Other
      MessageBox(0, "", 0, 0, "Attachment Failed : Error " | &return);
   End-Evaluate;
End-If;

- View attachment

/*******************************************************************************/
Local string &URL_ID, &ATTACHSYSFILENAME, &ATTACHUSERFILE;

&URL_ID = "record://MY_RECORD_STK";
&ATTACHSYSFILENAME = Record.MY_SCREEN_RECORD.ATTACHSYSFILENAME.Value;
&ATTACHUSERFILE = Record.MY_SCREEN_RECORD.ATTACHSYSFILENAME.Value;
&MESSAGE_LVL = 1;

&RETCODE = ViewAttachment(&URL_ID, &ATTACHSYSFILENAME, &ATTACHUSERFILE);

/* Error Messages */
If (&RETCODE = %Attachment_Cancelled) Then
      MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 3, "AddAttachment cancelled");
End-If;
If (&RETCODE = %Attachment_Failed) Then
   MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 19, "ViewAttachment failed");
End-If;
If (&RETCODE = %Attachment_FileTransferFailed) Then
   MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 21, "ViewAttachment failed: File Transfer did not succeed");
End-If;
/* following error messsage only in iclient mode */
If (&RETCODE = %Attachment_NoDiskSpaceAppServ) Then
   MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 22, "ViewAttachment failed: No disk space on the app server");
End-If;
/* following error messsage only in iclient mode */
If (&RETCODE = %Attachment_NoDiskSpaceWebServ) Then
   MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 23, "ViewAttachment failed: No disk space on the web server");
End-If;
If (&RETCODE = %Attachment_FileExceedsMaxSize) Then
   MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 24, "ViewAttachment failed: File exceeds the max size");
End-If;
If (&RETCODE = %Attachment_DestSystNotFound) Then
   MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 25, "ViewAttachment failed: Cannot locate destination system for ftp");
End-If;
If (&RETCODE = %Attachment_DestSysFailedLogin) Then
   MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 26, "ViewAttachment failed: Unable to login into destination system for ftp");
End-If;

- Delete attachment

/*******************************************************************************/
Declare Function delete_attachment PeopleCode FILE_ATTACH_WRK.ATTACHDELETE FieldChange;

Function my_delete_attachment() Returns integer
   Local string &URL_ID, &ATTACHSYSFILENAME, &ATTACHUSERFILE;

   &URL_ID = "record://MY_RECORD_STK";
   &ATTACHSYSFILENAME = Record.MY_SCREEN_RECORD.ATTACHSYSFILENAME.Value;
   &ATTACHUSERFILE = Record.MY_SCREEN_RECORD.ATTACHSYSFILENAME.Value;
   &MESSAGE_LVL = 1;

   delete_attachment(&URL_ID, &ATTACHSYSFILENAME, &ATTACHUSERFILE, &MESSAGE_LVL, &RETCODE);

   If &RETCODE = 0 Then
      MY_SCREEN_RECORD.FLAG.Value = "N";
      MY_SCREEN_RECORD.ATTACHSYSFILENAME = "";
   End-If;

   Return &RETCODE;
End-Function;

/* Main */
&return = my_delete_attachment();
DoSaveNow();

Déplacer un fichier avec Peoplecode

Jean-Baptiste HEREN — Wed, 04 Nov 2009 21:39:00 +0100

Pour faire court voici un exemple d'utilisation de la commande exec() et d'un fichier batch pour déplacer un fichier.

/********************************/

/* Initialisation des variables */

/********************************/

Local File &File;

&file_dir = UNE_TABLE_AET.FILE_DIRECTORY;

&file_name = UNE_TABLE_AET.FILE_NAME;

&new_file_dir = UNE_TABLE_AET.FILE_DIR_SERVER;

&new_file_name = &file_name | "_" | UNE_TABLE_AET.PROCESS_INSTANCE;

&cheminbatch = &new_file_dir | UNE_TABLE_AET.PROCESS_INSTANCE | ".bat ";

&chemin = &file_dir | &file_name;

&cheminnouveau = &new_file_dir | &new_file_name;

&commandcp = "move " | &chemin | " " | &cheminnouveau;

&commandrm = "del " | &chemin;

&commandrm2 = "del " | &cheminbatch;

/* Ecriture d'un BATCH script de déplacement & renommage du fichier */

&File = GetFile(&cheminbatch, "W", "A", %FilePath_Absolute);

&File.WriteLine(&commandcp);

&File.WriteLine(&commandrm);

&File.WriteLine(&commandrm2);

&File.Close();

/* création d'un fichier log.*/

REM &cheminbatch = &cheminbatch | " > " | &new_file_dir | UNE_TABLE_AET.PROCESS_INSTANCE | ".log";

/* Exécution de du batch */

If Exec(&cheminbatch, %Exec_Synchronous + %FilePath_Absolute) >= 0 Then

MessageBox(0, "", 0, 0, "Déplacement du fichier de %1 à %2 réussi", &chemin, &cheminnouveau);

End-If;

Peoplesoft : Nomenclatures à plat en sql

Jean-Baptiste HEREN — Wed, 04 Jun 2008 11:40:00 +0200

Voici un petit exemple de code sql, permettant d'obtenir la nomenclature à plat telle qu'elle est affichée dans les écrans de consultation des nomenclatures de production.

Cette requête utilise la fonction coalesce(), qui permet d'obtenir le premier élément non nul d'une liste. Testé avec succès sous SQL server 2000 & 2005.





/******************************************************************/

/* Get the Flat BOM with simple
Query                            
*/

/* This example get the Current BOM Revision with Deepness of 5  
*/

/******************************************************************/



SELECT

BUSINESS_UNIT,

ROOT_ITEM,

-- The coalesce instruction returns the first not null Element from the listed
parameters.

COALESCE(Level5,Level4,Level3,Level2,Level1) AS Level,

COALESCE(INV_ITEM_ID5,INV_ITEM_ID4,INV_ITEM_ID3,INV_ITEM_ID2,INV_ITEM_ID1) AS
ITEM_ID,

COALESCE(POS_NBR5,POS_NBR4,POS_NBR3,POS_NBR2,POS_NBR1) AS POS_NBR,

COALESCE(QTY_PER5,QTY_PER4,QTY_PER3,QTY_PER2,QTY_PER1) AS QTY_PER

FROM

(

-- Select all Leafs : Level5

SELECT DISTINCT

A.BUSINESS_UNIT,

A.INV_ITEM_ID as ROOT_ITEM,

CASE WHEN A.INV_ITEM_ID is null THEN null ELSE 1 END AS Level1,

A.COMPONENT_ID AS INV_ITEM_ID1,

A.POS_NBR AS POS_NBR1,

A.QTY_PER AS QTY_PER1,

CASE WHEN B.INV_ITEM_ID is null THEN null ELSE 2 END AS Level2,

B.COMPONENT_ID AS INV_ITEM_ID2,

B.POS_NBR AS POS_NBR2,

B.QTY_PER AS QTY_PER2,

CASE WHEN C.INV_ITEM_ID is null THEN null ELSE 3 END AS Level3,

C.COMPONENT_ID AS INV_ITEM_ID3,

C.POS_NBR AS POS_NBR3,

C.QTY_PER AS QTY_PER3,

CASE WHEN D.INV_ITEM_ID is null THEN null ELSE 4 END AS Level4,

D.COMPONENT_ID AS INV_ITEM_ID4,

D.POS_NBR AS POS_NBR4,

D.QTY_PER AS QTY_PER4,

CASE WHEN E.INV_ITEM_ID is null THEN null ELSE 5 END AS Level5,

E.COMPONENT_ID AS INV_ITEM_ID5,

E.POS_NBR AS POS_NBR5,

E.QTY_PER AS QTY_PER5

FROM PS_EN_BOM_COMPS A

  LEFT OUTER JOIN PS_EN_BOM_COMPS B ON

       A.BUSINESS_UNIT = B.BUSINESS_UNIT

   AND A.COMPONENT_ID = B.INV_ITEM_ID

   and A.BOM_STATE    = B.BOM_STATE

   and A.BOM_TYPE     = B.BOM_TYPE

   and A.BOM_CODE     = B.BOM_CODE

  LEFT OUTER JOIN PS_EN_BOM_COMPS C ON

       C.BUSINESS_UNIT = B.BUSINESS_UNIT

   AND C.INV_ITEM_ID   = B.COMPONENT_ID

   and C.BOM_STATE    = B.BOM_STATE

   and C.BOM_TYPE     = B.BOM_TYPE

   and C.BOM_CODE     = B.BOM_CODE

  LEFT OUTER JOIN PS_EN_BOM_COMPS D ON

       D.BUSINESS_UNIT = C.BUSINESS_UNIT

   AND D.INV_ITEM_ID   = C.COMPONENT_ID

   and D.BOM_STATE    = C.BOM_STATE

   and D.BOM_TYPE     = C.BOM_TYPE

   and D.BOM_CODE     = C.BOM_CODE

  LEFT OUTER JOIN PS_EN_BOM_COMPS E ON

       E.BUSINESS_UNIT = D.BUSINESS_UNIT

   AND E.INV_ITEM_ID   = D.COMPONENT_ID

   and E.BOM_STATE    = D.BOM_STATE

   and E.BOM_TYPE     = D.BOM_TYPE

   and E.BOM_CODE     = D.BOM_CODE

WHERE A.BOM_STATE = 'PR'

  and A.BOM_TYPE  = 'PR'

  and A.BOM_CODE  = 1

  AND (getdate() BETWEEN A.DATE_IN_EFFECT AND A.DATE_OBSOLETE)

  AND (getdate() BETWEEN B.DATE_IN_EFFECT AND B.DATE_OBSOLETE)

  AND (getdate() BETWEEN C.DATE_IN_EFFECT AND C.DATE_OBSOLETE)

  AND (getdate() BETWEEN D.DATE_IN_EFFECT AND D.DATE_OBSOLETE)

  AND (getdate() BETWEEN E.DATE_IN_EFFECT AND E.DATE_OBSOLETE)

UNION

-- Select all Leafs : Level4

SELECT DISTINCT

A.BUSINESS_UNIT,

A.INV_ITEM_ID as ROOT_ITEM,

CASE WHEN A.INV_ITEM_ID is null THEN null ELSE 1 END AS Level1,

A.COMPONENT_ID AS INV_ITEM_ID1,

A.POS_NBR AS POS_NBR1,

A.QTY_PER AS QTY_PER1,

CASE WHEN B.INV_ITEM_ID is null THEN null ELSE 2 END AS Level2,

B.COMPONENT_ID AS INV_ITEM_ID2,

B.POS_NBR AS POS_NBR2,

B.QTY_PER AS QTY_PER2,

CASE WHEN C.INV_ITEM_ID is null THEN null ELSE 3 END AS Level3,

C.COMPONENT_ID AS INV_ITEM_ID3,

C.POS_NBR AS POS_NBR3,

C.QTY_PER AS QTY_PER3,

CASE WHEN D.INV_ITEM_ID is null THEN null ELSE 4 END AS Level4,

D.COMPONENT_ID AS INV_ITEM_I4,

D.POS_NBR AS POS_NBR4,

D.QTY_PER AS QTY_PER4,

NULL AS Level5,

NULL AS INV_ITEM_ID5,

NULL AS POS_NBR5,

NULL AS QTY_PER5

FROM PS_EN_BOM_COMPS A

  LEFT OUTER JOIN PS_EN_BOM_COMPS B ON

       A.BUSINESS_UNIT = B.BUSINESS_UNIT

   AND A.COMPONENT_ID = B.INV_ITEM_ID

   and A.BOM_STATE    = B.BOM_STATE

   and A.BOM_TYPE     = B.BOM_TYPE

   and A.BOM_CODE     = B.BOM_CODE

  LEFT OUTER JOIN PS_EN_BOM_COMPS C ON

       C.BUSINESS_UNIT = B.BUSINESS_UNIT

   AND C.INV_ITEM_ID   = B.COMPONENT_ID

   and C.BOM_STATE    = B.BOM_STATE

   and C.BOM_TYPE     = B.BOM_TYPE

   and C.BOM_CODE     = B.BOM_CODE

  LEFT OUTER JOIN PS_EN_BOM_COMPS D ON

       D.BUSINESS_UNIT = C.BUSINESS_UNIT

   AND D.INV_ITEM_ID   = C.COMPONENT_ID

   and D.BOM_STATE    = C.BOM_STATE

   and D.BOM_TYPE     = C.BOM_TYPE

   and D.BOM_CODE     = C.BOM_CODE

WHERE A.BOM_STATE = 'PR'

  and A.BOM_TYPE  = 'PR'

  and A.BOM_CODE  = 1

  AND (getdate() BETWEEN A.DATE_IN_EFFECT AND A.DATE_OBSOLETE)

  AND (getdate() BETWEEN B.DATE_IN_EFFECT AND B.DATE_OBSOLETE)

  AND (getdate() BETWEEN C.DATE_IN_EFFECT AND C.DATE_OBSOLETE)

  AND (getdate() BETWEEN D.DATE_IN_EFFECT AND D.DATE_OBSOLETE)

UNION

-- Select all Level3

SELECT DISTINCT

A.BUSINESS_UNIT,

A.INV_ITEM_ID as ROOT_ITEM,

CASE WHEN A.INV_ITEM_ID is null THEN null ELSE 1 END AS Level1,

A.COMPONENT_ID AS INV_ITEM_ID1,

A.POS_NBR AS POS_NBR1,

A.QTY_PER AS QTY_PER1,

CASE WHEN B.INV_ITEM_ID is null THEN null ELSE 2 END AS Level2,

B.COMPONENT_ID AS INV_ITEM_ID2,

B.POS_NBR AS POS_NBR2,

B.QTY_PER AS QTY_PER2,

CASE WHEN C.INV_ITEM_ID is null THEN null ELSE 3 END AS Level3,

C.COMPONENT_ID AS INV_ITEM_ID3,

C.POS_NBR AS POS_NBR3,

C.QTY_PER AS QTY_PER3,

NULL AS Level4,

NULL AS INV_ITEM_ID4,

NULL AS POS_NBR4,

NULL AS QTY_PER4,

NULL AS Level5,

NULL AS INV_ITEM_ID5,

NULL AS POS_NBR5,

NULL AS QTY_PER5

FROM PS_EN_BOM_COMPS A

  LEFT OUTER JOIN PS_EN_BOM_COMPS B ON

       A.BUSINESS_UNIT = B.BUSINESS_UNIT

   AND A.COMPONENT_ID = B.INV_ITEM_ID

   and A.BOM_STATE    = B.BOM_STATE

   and A.BOM_TYPE     = B.BOM_TYPE

   and A.BOM_CODE     = B.BOM_CODE

  LEFT OUTER JOIN PS_EN_BOM_COMPS C ON

       C.BUSINESS_UNIT = B.BUSINESS_UNIT

   AND C.INV_ITEM_ID   = B.COMPONENT_ID

   and C.BOM_STATE    = B.BOM_STATE

   and C.BOM_TYPE     = B.BOM_TYPE

   and C.BOM_CODE     = B.BOM_CODE

WHERE A.BOM_STATE = 'PR'

  and A.BOM_TYPE  = 'PR'

  and A.BOM_CODE  = 1

  AND (getdate() BETWEEN A.DATE_IN_EFFECT AND A.DATE_OBSOLETE)

  AND (getdate() BETWEEN B.DATE_IN_EFFECT AND B.DATE_OBSOLETE)

  AND (getdate() BETWEEN C.DATE_IN_EFFECT AND C.DATE_OBSOLETE)

UNION

-- Select all Level2

SELECT DISTINCT

A.BUSINESS_UNIT,

A.INV_ITEM_ID as ROOT_ITEM,

CASE WHEN A.INV_ITEM_ID is null THEN null ELSE 1 END AS Level1,

A.COMPONENT_ID AS INV_ITEM_ID1,

A.POS_NBR AS POS_NBR1,

A.QTY_PER AS QTY_PER1,

CASE WHEN B.INV_ITEM_ID is null THEN null ELSE 2 END AS Level2,

B.COMPONENT_ID AS INV_ITEM_ID2,

B.POS_NBR AS POS_NBR2,

B.QTY_PER AS QTY_PER2,

NULL AS Level3,

NULL AS INV_ITEM_ID3,

NULL AS POS_NBR3,

NULL AS QTY_PER3,

NULL AS Level4,

NULL AS INV_ITEM_ID4,

NULL AS POS_NBR4,

NULL AS QTY_PER4,

NULL AS Level5,

NULL AS INV_ITEM_ID5,

NULL AS POS_NBR5,

NULL AS QTY_PER5

FROM PS_EN_BOM_COMPS A

  LEFT OUTER JOIN PS_EN_BOM_COMPS B ON

       A.BUSINESS_UNIT = B.BUSINESS_UNIT

   AND A.COMPONENT_ID = B.INV_ITEM_ID

   and A.BOM_STATE    = B.BOM_STATE

   and A.BOM_TYPE     = B.BOM_TYPE

   and A.BOM_CODE     = B.BOM_CODE

   AND (getdate() BETWEEN A.DATE_IN_EFFECT AND A.DATE_OBSOLETE)

   AND (getdate() BETWEEN B.DATE_IN_EFFECT AND B.DATE_OBSOLETE)

WHERE A.BOM_STATE = 'PR'

  and A.BOM_TYPE  = 'PR'

  and A.BOM_CODE  = 1

UNION

-- Select all Level1

SELECT DISTINCT

A.BUSINESS_UNIT,

A.INV_ITEM_ID as ROOT_ITEM,

CASE WHEN A.INV_ITEM_ID is null THEN null ELSE 1 END AS Level1,

A.COMPONENT_ID AS INV_ITEM_ID1,

A.POS_NBR AS POS_NBR1,

A.QTY_PER AS QTY_PER1,

NULL AS Level2,

NULL AS INV_ITEM_ID2,

NULL AS POS_NBR2,

NULL AS QTY_PER2,

NULL AS Level3,

NULL AS INV_ITEM_ID3,

NULL AS POS_NBR3,

NULL AS QTY_PER3,

NULL AS Level4,

NULL AS INV_ITEM_ID4,

NULL AS POS_NBR4,

NULL AS QTY_PER4,

NULL AS Level5,

NULL AS INV_ITEM_ID5,

NULL AS POS_NBR5,

NULL AS QTY_PER5

FROM PS_EN_BOM_COMPS A

WHERE A.BOM_STATE = 'PR'

  and A.BOM_TYPE  = 'PR'

  and A.BOM_CODE  = 1

  AND (getdate() BETWEEN A.DATE_IN_EFFECT AND A.DATE_OBSOLETE)

) Z

WHERE Z.BUSINESS_UNIT = :1

  AND Z.ROOT_ITEM = :2

ORDER BY
INV_ITEM_ID1,INV_ITEM_ID2,INV_ITEM_ID3,INV_ITEM_ID4,INV_ITEM_ID5

Peoplesoft data mover

Jean-Baptiste HEREN — Mon, 31 Mar 2008 17:56:00 +0200

Data mover est un outil très pratique, permettant l'export et l'import de données dans des tables. Cela peut servir à copier des éléments de paramétrage ou des jeux de tests d'une base à l'autre.

L'interface

On ne peut pas faire plus simple. Il s'agit d'une fenêtre de saisie disposant d'un bouton Exécution (Feu tricolore)

Le Principe

On se connecte à la base Source et on lance un script d'export
On se connecte à la base cible et on lance un script d'import

Exemple de script d'export

SET OUTPUT c:\temp\data\TABLE_HDR.dat;
SET LOG c:\temp\data\TABLE_HDR_EXPORT.log;

-- Export Tables
EXPORT PS_TABLE_HDR;
EXPORT PS_TABLE_LINE WHERE KEY='TEST';

Exemple de script d'import

SET INPUT c:\temp\data\TABLE_HDR.dat;
SET LOG c:\temp\data\TABLE_HDR__IMPORT.log;

-- Export Tables
DELETE FROM PS_TABLE_HDR;
IMPORT PS_TABLE_HDR;

DELETE FROM PS_TABLE_LINE WHERE KEY='TEST';;
IMPORT PS_TABLE_LINE;

Imprimer une étiquette sur Zebra en Sqr

Jean-Baptiste HEREN — Thu, 20 Mar 2008 06:01:00 +0100

Voici un morceau de code illustrant comment générer un programme ZPL2 en sqr, afin d'imprimer une étiquette sur une imprimante Zebra. L'exemple ci-après imprime une étiquette contenant du texte, deux codes barre et un cadre.

Les "imprimantes codes barre" ne sont pas des imprimantes classiques. Pour obtenir un résultat de leur part, il faut leur transmettre des instructions dans un langage spécifique. Il s'agit donc dans cet exemple, de transmettre à l'imprimante, un fichier contenant un programme compréhensible par celle-ci. Les Zebra utilisent un langage de balises appelé ZPL. Les instructions se présente sous la forme suivante : ^XXparm0,parm1,parm3 Où XX est le nom de l'instruction, et parmx sont les paramètres. Pour plus de détails sur ce langage, vous trouverez facilement la documentations sur le site de Zebra.

L'unité utilisée pour les coordonnées sur l'étiquette est le pixel. Tout dépend donc de la résolution de l'imprimante, généralement définie en points par pouce (DPI). Ici j'utilise une fonction qui se charge de la conversion de cm à pixel, en utilisant un dpi paramétré à 300.

!----------------------------------------------------------------------!
! Author : Jean-Baptiste Heren                                         !
! Procedure print-zebra-tag                                   !
!----------------------------------------------------------------------!
! Description: Print Tags on a Zebra Printer                           !
!----------------------------------------------------------------------!
begin-procedure print-zebra-tag

! Set Filename
let $filepath = RTRIM($filepath,' ')
if substr($filepath,length($filepath),1) <> '\'
    let $filepath = $filepath || '\'
end-if

let $filename = $barcode_value || '.txt'
let $filename = $filepath || $filename

! Open File
open $filename as 1 for-writing record=1000:vary status=#filestat
if #filestat <> 0
    SHOW 'Could not Open File ' $filename
else
       ! Get Positions in Pixels
       do cm-to-pixels(5 ,#YLABEL)
       do cm-to-pixels(18 ,#XLABEL)
       ! cols
       do cm-to-pixels(0.5,#XCOL0)
       do cm-to-pixels(3 ,#XCOL1)
       do cm-to-pixels(6 ,#XCOL2)
       do cm-to-pixels(12 ,#XCOL3)
       ! Lines
       do cm-to-pixels(1.5,#YGZONE)
       do cm-to-pixels(0.1,#YLINE6)
       do cm-to-pixels(0.3,#YLINE5)
       do cm-to-pixels(1 ,#YLINE4)
       do cm-to-pixels(1.8,#YLINE3)
       do cm-to-pixels(2.6,#YLINE2)
       do cm-to-pixels(3.4,#YLINE1)
       do cm-to-pixels(4.3,#YLINE0)

       let $label_format = '^XA^LRN^FWR^CF0,52,39^LH20,20^PON'
       let $heading       = '^FO'|| TO_CHAR(#YLINE0) ||','|| TO_CHAR(#XCOL0) || '^CF0,52,39^FD' || 'Address : ' || $LOCAL_ADDRESS || ' Tel: '|| $LOCAL_PHONE || ' Fax: ' || $LOCAL_FAX ||'^FS'
       let $line1_1       = '^FO'|| TO_CHAR(#YLINE1) ||','|| TO_CHAR(#XCOL1) || '^CF0,65,52^FD' || 'Ref :' || '^FS'
       let $line1_2       = '^FO'|| TO_CHAR(#YLINE1) ||','|| TO_CHAR(#XCOL2) || '^CF0,65,52^FD' || $inv_item_id || ', Rev ' || $revision || '^FS'
       let $line1_3       = '^FO'|| TO_CHAR(#YLINE1) ||','|| TO_CHAR(#XCOL3) || '^B3,,110,Y^FD' || $inv_item_id || '^FS'
       let $line2_1       = '^FO'|| TO_CHAR(#YLINE2) ||','|| TO_CHAR(#XCOL1) || '^CF0,52,39^FD' || 'Designation :' || '^FS'
       let $line2_2       = '^FO'|| TO_CHAR(#YLINE2) ||','|| TO_CHAR(#XCOL2) || '^CF0,52,39^FD' || $ITEM_DESCR || '^FS'
       let $line3_1       = '^FO'|| TO_CHAR(#YLINE3) ||','|| TO_CHAR(#XCOL1) || '^CF0,52,39^FD' || 'Quantite :' || '^FS'
       let $line3_2       = '^FO'|| TO_CHAR(#YLINE3) ||','|| TO_CHAR(#XCOL2) || '^CF0,52,39^FD' || To_CHAR(#QtyOnTag) || '^FS'
       let $line4_1       = '^FO'|| TO_CHAR(#YLINE4) ||','|| TO_CHAR(#XCOL1) || '^CF0,52,39^FD' || 'N. Lot :' || '^FS'
       let $line4_2       = '^FO'|| TO_CHAR(#YLINE4) ||','|| TO_CHAR(#XCOL2) || '^CF0,52,39^FD' || $barcode_value || '^FS'
       let $line4_3       = '^FO'|| TO_CHAR(#YLINE4) ||','|| TO_CHAR(#XCOL3) || '^B3,,110,Y^FD' || $barcode_value || '^FS'
       let $line5_1       = '^FO'|| TO_CHAR(#YLINE5) ||','|| TO_CHAR(#XCOL1) || '^CF0,52,39^FD' || 'Date :' || '^FS'
       let $line5_2       = '^FO'|| TO_CHAR(#YLINE5) ||','|| TO_CHAR(#XCOL2) || '^CF0,52,39^FD' || $SysDateTime || '^FS'
       ! Graphics
       let $graphic_box   = '^FO0,0^GB' || TO_CHAR(#YLABEL) || ','|| TO_CHAR(#XLABEL) || ',5^FS'
       let $graphic_line = '^FO0,0^GB0,' || TO_CHAR(#YLABEL) || ',5^FS'
       let $label_end     = '^XZ'

       let $LINE = $label_format || $heading || $line1_1 || $line1_2 || $line1_3 || $line2_1 || $line2_2 || $line3_1 || $line3_2 || $line4_1 || $line4_2 || $line4_3 || $line5_1 || $line5_2 || $graphic_box || $graphic_line || $label_end
      ! Write File
      #DEBUG SHOW 'Zebra data : ' $LINE
      write 1 from $LINE
end-if
CLOSE 1
end-if
end-procedure

!----------------------------------------------------------------------!
! Author : Jean-Baptiste Heren                                         !
! Procedure To convert cm to pixels using a DPI fixed parameter        !
! Cette procédure convertit des cm en Pixels suivant un dpi paramétré !
!----------------------------------------------------------------------!
begin-procedure cm-to-pixels(#cm, :#pixels)
! This Could be also a parameter
let #dpi = 300
! Conversion
let #inches = #cm *0.39
! Rounding
let #pixels = round(#dpi * #inches,0) ! Integer divide
end-procedure

Ecrire un fichier avec Peoplecode

Jean-Baptiste HEREN — Mon, 25 Feb 2008 20:10:00 +0000

Avec Peoplesoft, il existe plusieurs moyens de créer un fichier. Ceux-ci sont très utiles dans le développement d'interfaces avec d'autres logiciels ou appareils spécifiques.

Je présente ici deux solutions. La première laisse la main au développeur pour l'écriture des enregistrements. La seconde utilise une définition de colonages basée sur un Record.

1- Accéder au fichier

Dans les deux cas, nous commencerons par définir Notre Fichier, puis l'ouvrir en écriture.

Local File &myFile;
Local String &FileName, &FilePath;

&FilePath = "/temp/";
&FileName = "test.csv";

&myFile = GetFile(&FilePath | &FileName, "W", %FilePath_Absolute);

2- Ecriture "Manuelle"

Dans ce cas, nous pouvons simplement écrire des lignes formatées manuellement dans une chaîne de caractères.

Local String &ligne;
Local SQL &mySql;
Local Boolean &result;

&result = &mySql.Fetch(&FIELD1, &FIELD2, &FIELD3, &FIELD4, &FIELD5);

&Ligne = &FIELD1 | ";" | &FIELD2 | ";" | &FIELD3 | ";" | &FIELD4 | ";" | &FIELD5;
&myFile.writeLine(&Ligne);

&mySql.close();
&myFile.Close();

3- Utilisation d'un FileLayout

Le FileLayout est un "objet" peoplesoft qui permet de définir des formats de fichiers en utilisant des records. L'avantage du couple Record / FileLayout réside dans le fait que lors des opérations de lecture/écriture, le développeur n'a pas à se soucier du format. Il est tés facile de passer une ligne de fichier directement dans une ligne de Record, et inversement.

Pour créer un FileLayout :

File / new /FileLayout
Glisser Déposer le record désiré dans la fenêtre du FileLayout
Définir le format au niveau Global (double clic sur le nom du FileLayout)
Définir le format du Segment (douvle clic sur le nom du Record)
Sauvegarde

Utilisation du FileLayout en Peoplecode.

Il suffit d'appliquer le format à un fichier défini précédemment.

&myFile.SetFileLayout(FileLayout.MYRECORD_LAYOUT);

Une fois ce format défini, nous pouvons passer aux opérations de lecture/écriture, en utilisant les Records et Rowsets.

3.1 - Ecriture ligne par ligne

Sélection des données via un objet sql et utilisation d'un Record pour stocker le résultat.

Local SQL &mySql;
Local Record &myRecord;
Local Boolean &result;

&mySql = GetSQL(SQL.THE_SQL_OBJECT, &PARAM1, &PARAM2);
REM Boucle sur chaque Ligne Résultante;
While &mySql.Fetch(&MyRecord)
REM écriture du record;
&myFile.writeRecord(&myRecord);
end-While;

&mySql.close();
&myFile.close();

3.2 - Ecriture du Rowset en une fois

Sélection d'un ensemble de données dans un Rowset.

Local Rowset &myRowset = CreateRowset(Record.MYRECORD);
&myRowset.Fill("WHERE FIELD1 = :1 AND FIELD2 = :2", &PARAM1, &PARAM2);

REM Ecriture de l'ensemble des lignes sélectionnées;
&myFile.WriteRowset(&myRowset, True);

REM Fremeture du Fichier;
&myFile.close();

4- Pour conclure

Il est intéressant de noter que la lecture d'un fichier suit les mêmes règles que l'écriture. Il suffira d'utiliser par exemple, les méthodes ReadLine() ou ReadRowset().

Ecrire un fichier plat en sqr

Jean-Baptiste HEREN — Tue, 19 Feb 2008 20:42:00 +0100

Voici les trois étapes nécessaires pour l'écriture d'un fichier plat en sqr:

Ouverture : Instruction OPEN
Ecriture: Instruction WRITE
Fermeture : Instruction CLOSE

Dans cet exemple, Nous créons un fichier text.txt dans le dossier /temp, et y écrivons deux chaines de caractères, en précisant pour chaque 'colonne', sa taille fixe.

! Init FileName and File Number
let $File_name = '/temp/text.txt'
move 1 to #FILE_NO

! Put Something to $values
let $value1 = 'foo1'
let $value2 = 'foo2'

! 1- Create the File and attach it to the specified File Number
OPEN $File_name as #FILE_NO for-writing record=20000:VARY
                                       status=#filestat
! 2- Write one line (aka record)
write #FILE_NO from
     $Value1:16 ! fixed zize to 16 chars
     $Value2:4 ! fixed zize to 4 chars

! 3 - close File
Close #FILE_NO

Biensûr, ils est courant de placer une boucle de type BEGIN-SQL/END-SQL autour de l'étape 2, sachant que chaque appel à la commande WRITE crée une nouvelle ligne dans le fichier.

Fonction sleep() pour Peoplecode

Jean-Baptiste HEREN — Tue, 29 Jan 2008 22:58:00 +0100

En Peoplecode pur, il n'existe pas de fonction sleep() ou de wait(). Voici donc un fonction qui utilise une méthode Java pour arriver à ses fins.

Function Sleep(&ms)
   Local JavaObject &javaThread;
   try
      &javaThread = GetJavaClass("java.lang.Thread");
      // Java Sleep takes milliseconds.

      &javaThread.sleep(&ms);
      &javaThread = Null;
   catch Exception &Ex
      &javaThread = Null;
   end-try;
End-Function;

Peoplecode : Lancer un traitement et en attendre le résultat.

Jean-Baptiste HEREN — Tue, 29 Jan 2008 22:55:00 +0100

Voici comment en Peoplecode, il est possible d'exécuter d'autres traitements et d'attendre leur fin avant de poursuivre.

Local ProcessRequest &prcsrqst;
Local JavaObject &Obj;

      &prcsrqst = CreateProcessRequest("Application Engine", "TEST");
      &prcsrqst.RunControlID = TEST_AET.RUN_CNTL_ID.Value;
      &prcsrqst.Schedule();

      &Obj = CreateJavaObject("java.lang.Thread");
      &Obj.start();

      &RunStatus = "0";
      While &RunStatus <> "9" And
            &RunStatus <> "3" And
            &RunStatus <> "1" And
            &RunStatus <> "10"
         REM Directly use the Java sleep() Method for 5 seconds;
         &Obj.sleep(5000);
         SQLExec("SELECT RUNSTATUS FROM PSPRCSRQST WHERE PRCSINSTANCE = :1", &prcsrqst.ProcessInstance, &RunStatus);
         REM MessageBox(0, "", 0, 0, "Sleep(), Runstatus = %1", &RunStatus);
      End-While;
      &Obj.stop();

      rem WriteLog("Process TEST is Finished");

      If &prcsrqst.Status = 10 /* No success */ Or
            &prcsrqst.Status = 3 /*( Error)*/ Then
         /* Process your errors */
         Exit (1);
      Else
        /* Process your success */
      End-If;

Squelette Sqr Report

Jean-Baptiste HEREN — Fri, 25 Jan 2008 14:15:00 +0100

SQR est un langage assez fastidieux à déboger, et l'on se laisse souvent prendre aux petits pièges comme par exemple, la nécessité d'indenter les ordres sql pour qu'ils fonctionnent...

Voici donc un squelette de programme sqr. J'espère que comme pour moi, il vous évitera d'obtenir des erreurs avant même d'avoir commencé à programmer :).

Vous trouverez joint à ce post, le fameux fichier WORDFILE.TXT permettant la coloration syntaxique de SQR dans l'éditeur UltraEdit.

#INCLUDE 'SETENV.SQC'

BEGIN-SETUP

#INCLUDE 'SETUP01.SQC'

END-SETUP

!-------------------------------------------

! Begin-Heading

! Standard Section ran on each pages

!-------------------------------------------

BEGIN-HEADING 6

print 'header' ()

END-HEADING

!-------------------------------------------

! Begin-Footing

! Standard Section ran on each pages

!-------------------------------------------

BEGIN-FOOTING 4

last-page () ' /' ' ' ! "Page 12 of 25.

END-FOOTING

!-------------------------------------------

! Begin Report

! Main section of program execution (standard for Reports).

!-------------------------------------------

BEGIN-REPORT

! We have to manualy manage the line position

let #line = 1

let #col = 1

let $mytext1 = 'test1'

let $mytext2 = 'test2'

! Print my text1

print $mytext1 (#line, #col)

! We have to manualy manage the line position

ADD 1 TO #line

ADD 10 TO #col

! Print my text2

print $mytext2 (#line, #col)

ADD 1 TO #line

do MY-REPORT

END-REPORT

!-------------------------------------------

! Your Custom Procedure

! Put here the main part of your Report

!-------------------------------------------

BEGIN-PROCEDURE MY-REPORT

print 'page1' (#line, #col)

! Create new page

new-page

MOVE 1 TO #line

MOVE 1 TO #col

print 'page2' (#line, #col)

END-PROCEDURE !MY-REPORT

Purges des tables de LANG pour les pages

Jean-Baptiste HEREN — Fri, 25 Jan 2008 14:04:00 +0100

Il vous est certainement arrivé de trouver une page en Français fortement différente de ce qu'elle est en Anglais. Ceci est souvent dû au fait que les développeurs précédents on édité les pages, directement en Français.

Le script SQL suivant permet de visualiser ces définitions, puis de les supprimer afin de retrouver le même rendu dans toutes les langues.

1- Quitter Application designer

2- Lancer le script en remplaçant le TM_INT_DRT_PNL par le nom de ta page (SELECT avant le DELETE histoire de voir ce que tu vas supprimer)

3- Ouvrir la page dans application designer (En Anglais), faire une petite modif et la sauvegarder.

SELECT * FROM PSPNLDEFNLANG

WHERE PNLNAME = 'TM_INT_DRT_PNL';

SELECT * FROM PSPNLBTNLANG

WHERE PNLNAME = 'TM_INT_DRT_PNL';

SELECT * FROM PSPNLFIELDLANG

WHERE PNLNAME = 'TM_INT_DRT_PNL';

SELECT * FROM PSPNLHTMLLANG

WHERE PNLNAME = 'TM_INT_DRT_PNL';

// Attention tout de même à ne pas lancer cette purge si vous n'êtes pas sûrs de vous ...

DELETE FROM PSPNLDEFNLANG

WHERE PNLNAME = 'TM_INT_DRT_PNL';

DELETE FROM PSPNLBTNLANG

WHERE PNLNAME = 'TM_INT_DRT_PNL';

DELETE FROM PSPNLFIELDLANG

WHERE PNLNAME = 'TM_INT_DRT_PNL';

DELETE FROM PSPNLHTMLLANG

WHERE PNLNAME = 'TM_INT_DRT_PNL';

commit;