Thursday, April 25, 2013

Sql to find out the cont ref based out of the component name

 

Sql to find out the cont ref based out of the component name

 

SELECT PORTAL_NAME,portal_reftype,PORTAL_URLTEXT FROM PSPRSMDEFN WHERE PORTAL_NAME = 'EMPLOYEE' AND PORTAL_OBJNAME like '%RUN_BI_PRNTEXT%'

 

Best Regards

Aravind Kumar R

System Analyst/Peoplesoft Practice
Birlasoft
36, Vijayaraghava Road , T. Nagar, Chennai 600 017 (INDIA)
Cell: +91 9566750166

************************************************************************************************************************************************************************************
"This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error please notify the mail.admin@birlasoft.com immediately without opening the mail and attachment . Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email
************************************************************************************************************************************************************************************

Thursday, April 18, 2013

PeopleSoft - PeopleCode for archiving the file in PSNT server using Application Engine


PeopleSoft - PeopleCode for archiving the file in PSNT server using Application Engine and Exec command

&TargetFilePath = "D:\FDEV9\Interface\Backup\Alliance\";

SQLExec("SELECT descr254,substr(descr254,instr(descr254,'\',-1)+1,length(descr254)) FROM PS_Z_AR_FILE_TMP WHERE PROCESS_INSTANCE = :1 AND FILE_SEQ = :2 AND ARCHIVE_PROCESS = 'A'", Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value, Z_AR_ALNCE_AET.FILE_SEQ.Value, &SourceFileFull, &SrsFileName);


If Not None(&SourceFileFull) Then
 
   &ArchiveResult = 1;
   &TargetFile = &TargetFilePath | &SrsFileName;
   &ArchiveResult = PutAttachment("\\10.0.24.231\", &TargetFile, &SourceFileFull);
   &ExecCommand = "cmd /c move " | &SourceFileFull | " " | &TargetFile | " " | " > C:\movefile.log 2>&1";
   rem MessageBox(0, "", 0, 0, "&ExecCommand-" | &ExecCommand);
   try
    
      &ArchiveResult = Exec(&ExecCommand, %Exec_Asynchronous + %FilePath_Absolute);
   catch Exception &ex
      rem Handle the exception;
      MessageBox(0, "", 0, 0, &ex.ToString());
   end-try;
  
   If &ArchiveResult = 0 Then
      MessageBox(0, "", 0, 0, "File archive failed for - " | &TargetFile);
   Else
      MessageBox(0, "", 0, 0, "File archived successfully to folder- " | &TargetFile);
   End-If;
Else
   MessageBox(0, "", 0, 0, "File not archived DUE TO ERROROS");
End-If;


************************************************************************************************************************************************************************************

Tuesday, April 16, 2013

SQL Check if a user has access to a PeopleSoft portal content reference/folder - PIA

SQL to check if a user has access to a PeopleSoft portal content reference/folder -PIA

 

SELECT * FROM (

SELECT A.*, C.ROLEUSER, C.ROLENAME

FROM PSPRSMPERM A, PSROLECLASS B, PSROLEUSER C

WHERE A.PORTAL_PERMTYPE = 'P' AND A.PORTAL_PERMNAME= B.CLASSID AND B.ROLENAME = C.ROLENAME

UNION

SELECT A.*, C.ROLEUSER, C.ROLENAME

FROM PSPRSMPERM A, PSROLEUSER C

WHERE A.PORTAL_PERMTYPE = 'R' AND A.PORTAL_PERMNAME = C.ROLENAME) X

WHERE X.PORTAL_NAME = 'EMPLOYEE'

and X.PORTAL_OBJNAME like '%CUSTOMER%'

AND X.ROLEUSER = 'aravindkumar'

 

Best Regards

Aravind Kumar R

System Analyst/Peoplesoft Practice
Birlasoft
36, Vijayaraghava Road , T. Nagar, Chennai 600 017 (INDIA)
Cell: +91 9566750166

************************************************************************************************************************************************************************************
"This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error please notify the mail.admin@birlasoft.com immediately without opening the mail and attachment . Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email
************************************************************************************************************************************************************************************

PeopleSoft FSCM important tables

 

PeopleSoft finance and supply chain management important tables list

 

SELECT * FROM PS_JRNL_LN_BUDG

SELECT * FROM PS_LEDGER_BUDG

SELECT * FROM PS_COMBO_DATA_TBL

SELECT * FROM PS_COMBO_DATA_BUDG

 

SELECT * FROM PS_REQ_HDR

SELECT * FROM PS_REQ_LINE

SELECT * FROM PS_REQ_LINE_SHIP

SELECT * FROM PS_REQ_LINE_DISTRIB

SELECT * FROM PS_REQ_LINE_ACCTG

 

SELECT * FROM PS_PO_HDR

SELECT * FROM PS_PO_LINE

SELECT * FROM PS_PO_LINE_SHIP

SELECT * FROM PS_PO_LINE_DISTRIB

SELECT * FROM PS_PO_LINE_ACCTG

 

SELECT * FROM PS_RECV_HDR

SELECT * FROM PS_RECV_LN

SELECT * FROM PS_RECV_LN_SHIP

SELECT * FROM PS_RECV_LN_DISTRIB

 

SELECT * FROM PS_VOUCHER

SELECT * FROM PS_VOUCHER_LINE

SELECT * FROM PS_DISTRIB_LINE

SELECT * FROM PS_VCHR_ACCTG_LINE

SELECT * FROM PS_PO_LINE_MATCHED

SELECT * FROM PS_PYMNT_ADVICE

SELECT * FROM PS_PYMNT_VCHR_XREF

SELECT * FROM PS_PAYMENT_TBL

 

 

SELECT * FROM PS_VENDOR

SELECT * FROM PS_VENDOR_LOC

SELECT * FROM PS_VENDOR_PAY

SELECT * FROM PS_VNDR_LOC_ADDR

SELECT * FROM PS_VENDOR_WTHD

SELECT * FROM PS_VENDOR_ADDR

 

--AR

SELECT * FROM PS_AR_PAYMENT_EC

SELECT * FROM PS_AR_IDCUST_EC

SELECT * FROM PS_AR_IDITEM_EC

 

SELECT * FROM PS_PAYMENT_ID_CUST

SELECT * FROM PS_PAYMENT_ID_ITEM

SELECT * FROM PS_DEPOSIT_CONTROL

SELECT * FROM PS_PAYMENT

 

 

Best Regards

Aravind Kumar R

System Analyst/Peoplesoft Practice
Birlasoft
36, Vijayaraghava Road , T. Nagar, Chennai 600 017 (INDIA)
Cell: +91 9566750166

************************************************************************************************************************************************************************************
"This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error please notify the mail.admin@birlasoft.com immediately without opening the mail and attachment . Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email
************************************************************************************************************************************************************************************

PeopleCode for inserting the AR pending item interface using Component Interface

 

Peoplesoft -- PeopleCode for inserting the AR pending item interface using Component Interface

 

Local File &fileLog;

Local ApiObject &oSession, &oDcPenditemCi;

Local ApiObject &oPendingItemCollection, &oPendingItem;

Local ApiObject &oPendingDstCollection, &oPendingDst;

Local ApiObject &oPendingVatCollection, &oPendingVat;

 

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;

 

SQLExec("select GROUP_BU,GROUP_ID,OPRID,ASSN_OPRID,GROUP_TYPE,POST_ACTION,CONTROL_AMT,CONTROL_CNT,ORIGIN_ID,%dateout(RECEIVED_DT),%dateout(ENTRY_DT),  %dateout(ACCOUNTING_DT),CONTROL_CURRENCY,FORMAT_CURRENCY,ALLOW_DST FROM PS_Z_AR_GCTL_TMP WHERE PROCESS_INSTANCE = :1 AND Z_AR_STATUS ='P'", Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value, &GROUP_BU, &GROUP_ID, &OPRID, &ASSN_OPRID, &GROUP_TYPE, &POST_ACTION, &CONTROL_AMT, &CONTROL_CNT, &ORIGIN_ID, &RECEIVED_DT, &ENTRY_DT, &ACCOUNTING_DT, &CONTROL_CURRENCY, &FORMAT_CURRENCY, &ALLOW_DST);

try

   rem ***** Set the Log File *****;

   &fileLog = GetFile("C:\temp\DC_PENDITEM_CI.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 *****;

   &oDcPenditemCi = &oSession.GetCompIntfc(CompIntfc.DC_PENDITEM_CI);

   If &oDcPenditemCi = Null Then

      errorHandler();

      throw CreateException(0, 0, "GetCompIntfc failed for pending item ");

   End-If;

   rem ***** Set the Component Interface Mode *****;

   &oDcPenditemCi.InteractiveMode = False;

   &oDcPenditemCi.GetHistoryItems = True;

   &oDcPenditemCi.EditHistoryItems = False;

   rem ***** Set Component Interface Get/Create Keys *****;

   &oDcPenditemCi.GROUP_BU = &GROUP_BU;

   &oDcPenditemCi.GROUP_ID = "NEXT";

   rem ***** Execute Create ******;

   If Not &oDcPenditemCi.Create() Then;

      rem ***** Unable to Create Component Interface for the Add keys provided. *****;

      errorHandler();

      throw CreateException(0, 0, "Create pending item failed ");

   End-If;

   rem ***** Begin: Get/Set Component Interface Properties *****;

   rem ***** Get/Set Level 0 Field Properties *****;

   &oDcPenditemCi.GROUP_BU = &GROUP_BU;

   &oDcPenditemCi.GROUP_ID = "NEXT";

   &oDcPenditemCi.OPRID = &OPRID;

   &oDcPenditemCi.ASSN_OPRID = &ASSN_OPRID;

   &oDcPenditemCi.GROUP_TYPE = "B";

   rem &oDcPenditemCi.GROUP_SEQ_NUM = 17;

   &oDcPenditemCi.POST_ACTION = "L";

   &oDcPenditemCi.CONTROL_AMT = &CONTROL_AMT;

   &oDcPenditemCi.CONTROL_CNT = &CONTROL_CNT;

   &oDcPenditemCi.ORIGIN_ID = &ORIGIN_ID;

   &oDcPenditemCi.RECEIVED_DT = &RECEIVED_DT;

   &oDcPenditemCi.ENTRY_DT = &ENTRY_DT;

   &oDcPenditemCi.ACCOUNTING_DT = &ACCOUNTING_DT;

   rem &oDcPenditemCi.DST_BAL_STATUS = "I";

   &oDcPenditemCi.FORMAT_CURRENCY = &FORMAT_CURRENCY;

   &oDcPenditemCi.ALLOW_DST = &ALLOW_DST;

   rem ***** Set/Get PENDING_ITEM Collection Field Properties -- Parent: PS_ROOT Collection *****;

   &oPendingItemCollection = &oDcPenditemCi.PENDING_ITEM;

   Local integer &i126;

   &i126 = 1;

   &SelPndRecord = CreateRecord(Record.Z_AR_PITM_TMP);

   &SQL = CreateSQL("%Selectall(:1) WHERE PROCESS_INSTANCE = :2 AND Z_AR_STATUS ='P'", Record.Z_AR_PITM_TMP, Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value);

   While &SQL.fetch(&SelPndRecord)

      &oPendingItem = &oPendingItemCollection.InsertItem(&i126);

      REM MessageBox(0, "", 0, 0, &SelPndRecord.CUST_ID.VALUE);

      &oPendingItem.GROUP_BU = &SelPndRecord.GROUP_BU.VALUE;

      &oPendingItem.GROUP_ID = "NEXT";

      &oPendingItem.BUSINESS_UNIT = &SelPndRecord.BUSINESS_UNIT.VALUE;

      &oPendingItem.CUST_ID = &SelPndRecord.CUST_ID.VALUE;

      &oPendingItem.ITEM = &SelPndRecord.ITEM.VALUE;

      &oPendingItem.ITEM_LINE = &SelPndRecord.ITEM_LINE.VALUE;

      &EntryType = &SelPndRecord.ENTRY_TYPE.VALUE;

      If &SelPndRecord.GROUP_SEQ_NUM.VALUE = 1 Then

         &EntryType = "IN";

      End-If;

      If &SelPndRecord.GROUP_SEQ_NUM.VALUE = 2 Then

         &EntryType = "CR";

      End-If;

      &oPendingItem.GROUP_SEQ_NUM = &SelPndRecord.GROUP_SEQ_NUM.VALUE;

      &oPendingItem.ENTRY_TYPE = &EntryType;

      REM &SelPndRecord.ENTRY_TYPE.VALUE;

      &oPendingItem.ENTRY_REASON = &SelPndRecord.ENTRY_REASON.VALUE;

      &oPendingItem.ENTRY_AMT = &SelPndRecord.ENTRY_AMT.VALUE;

      &oPendingItem.ENTRY_EVENT = &SelPndRecord.ENTRY_EVENT.VALUE;

      &oPendingItem.ACCOUNTING_DT = &SelPndRecord.ACCOUNTING_DT.VALUE;

      &oPendingItem.ASOF_DT = &SelPndRecord.ASOF_DT.VALUE;

      &oPendingItem.DUE_DT = &SelPndRecord.DUE_DT.VALUE;

      &oPendingItem.CR_ANALYST = &SelPndRecord.CR_ANALYST.VALUE;

      &oPendingItem.SALES_PERSON = &SelPndRecord.SALES_PERSON.VALUE;

      &oPendingItem.COLLECTOR = &SelPndRecord.COLLECTOR.VALUE;

     

      REM &oPendingItem.DISPUTE_STATUS = &SelPndRecord.DISPUTE_STATUS.VALUE;

      &oPendingItem.PO_REF = &SelPndRecord.PO_REF.VALUE;

     

      REM &oPendingItem.PO_LINE = &SelPndRecord.PO_LINE.VALUE;

      REM &oPendingItem.DOCUMENT = &SelPndRecord.DOCUMENT.VALUE;

      REM &oPendingItem.DOCUMENT_LINE = &SelPndRecord.DOCUMENT_LINE.VALUE;

      &oPendingItem.POSTED_FLAG = "N";

      &oPendingItem.PYMNT_TERMS_CD = &SelPndRecord.PYMNT_TERMS_CD.VALUE;

     

      REM &oPendingItem.DISC_AMT = &SelPndRecord.DISC_AMT.VALUE;

      REM &oPendingItem.DISC_AMT1 = &SelPndRecord.DISC_AMT1.VALUE;

      REM &oPendingItem.DISC_DT = &SelPndRecord.DISC_DT.VALUE;

      REM &oPendingItem.DISC_DT1 = &SelPndRecord.DISC_DT1.VALUE;

      REM &oPendingItem.DISC_DAYS = &SelPndRecord.DISC_DAYS.VALUE;

      REM &oPendingItem.DUE_DAYS = &SelPndRecord.DUE_DAYS.VALUE;

      REM &oPendingItem.ALLOW_DISC = &SelPndRecord.ALLOW_DISC.VALUE;

      &oPendingItem.DST_ID_AR = &SelPndRecord.DST_ID_AR.VALUE;

      &oPendingItem.ENTRY_USE_ID = &SelPndRecord.ENTRY_USE_ID.VALUE;

      &oPendingItem.DST_BAL_ITEM = &SelPndRecord.DST_BAL_ITEM.VALUE;

     

      REM &oPendingItem.COLLECTION_STATUS = &SelPndRecord.COLLECTION_STATUS.VALUE;

      REM &oPendingItem.COLLECTION_DT = &SelPndRecord.COLLECTION_DT.VALUE;

      REM &oPendingItem.BILL_OF_LADING = &SelPndRecord.BILL_OF_LADING.VALUE;

      &oPendingItem.SUBCUST_QUAL1 = &SelPndRecord.SUBCUST_QUAL1.VALUE;

      &oPendingItem.SUBCUST_QUAL2 = &SelPndRecord.SUBCUST_QUAL2.VALUE;

      &oPendingItem.ENTRY_CURRENCY = &SelPndRecord.ENTRY_CURRENCY.VALUE;

     

      REM &oPendingItem.RT_TYPE = &SelPndRecord.RT_TYPE.VALUE;

      REM &oPendingItem.RATE_MULT = &SelPndRecord.RATE_MULT.VALUE;

      REM &oPendingItem.RATE_DIV = &SelPndRecord.RATE_DIV.VALUE;

      REM &oPendingItem.ENTRY_AMT_BASE = &SelPndRecord.ENTRY_AMT_BASE.VALUE;

     

      &oPendingItem.PAYMENT_METHOD = &SelPndRecord.PAYMENT_METHOD.VALUE;

      &oPendingItem.USER_AMT7 = &SelPndRecord.USER_AMT7.VALUE;

      &oPendingItem.USER7 = &SelPndRecord.USER7.VALUE;

      &oPendingItem.USER8 = &SelPndRecord.USER8.VALUE;

      &oPendingItem.USER9 = &SelPndRecord.USER9.VALUE;

      &oPendingItem.REVALUE_FLAG = &SelPndRecord.REVALUE_FLAG.VALUE;;

      rem &oPendingItem.PC_DISTRIB_STATUS = &SelPndRecord.PC_DISTRIB_STATUS.VALUE;;

      &oPendingItem.CURRENCY_CD = &SelPndRecord.CURRENCY_CD.VALUE;

      <*

      &oPendingItem.DST_ID_REAL = &SelPndRecord.DST_ID_REAL.VALUE;

      &oPendingItem.ITEM_ACCTG_DT = &SelPndRecord.ITEM_ACCTG_DT.VALUE;

      &oPendingItem.ORDER_NO = &SelPndRecord.ORDER_NO.VALUE;

      &oPendingItem.CONTRACT_NUM = &SelPndRecord.CONTRACT_NUM.VALUE;

      &oPendingItem.BUSINESS_UNIT_BI = &SelPndRecord.BUSINESS_UNIT_BI.VALUE;

      &oPendingItem.BUSINESS_UNIT_OM = &SelPndRecord.BUSINESS_UNIT_OM.VALUE;

      &oPendingItem.ADDRESS_SEQ_NUM = &SelPndRecord.ADDRESS_SEQ_NUM.VALUE;

      &oPendingItem.LETTER_CD = &SelPndRecord.LETTER_CD.VALUE;

      &oPendingItem.CONSOL_BUS_UNIT = &SelPndRecord.CONSOL_BUS_UNIT.VALUE;

      &oPendingItem.CONSOL_INVOICE = &SelPndRecord.CONSOL_INVOICE.VALUE;

      &oPendingItem.PAYMENT_METHOD = &SelPndRecord.PAYMENT_METHOD.VALUE;

      &oPendingItem.DRAFT_FORMAT = &SelPndRecord.DRAFT_FORMAT.VALUE;

      &oPendingItem.BANK_SETID = &SelPndRecord.BANK_SETID.VALUE;

      &oPendingItem.BANK_CD = &SelPndRecord.BANK_CD.VALUE;

      &oPendingItem.BANK_ACCT_KEY = &SelPndRecord.BANK_ACCT_KEY.VALUE;

      &oPendingItem.DRAFT_TYPE = &SelPndRecord.DRAFT_TYPE.VALUE;

      &oPendingItem.DRAFT_AMT_BASE = &SelPndRecord.DRAFT_AMT_BASE.VALUE;

      &oPendingItem.DOC_TYPE = &SelPndRecord.DOC_TYPE.VALUE;

      &oPendingItem.DOC_SEQ_NBR = &SelPndRecord.DOC_SEQ_NBR.VALUE;

      &oPendingItem.DOC_SEQ_DATE = &SelPndRecord.DOC_SEQ_DATE.VALUE;

      &oPendingItem.USER_AMT1 = &SelPndRecord.USER_AMT1.VALUE;

      &oPendingItem.USER1 = &SelPndRecord.USER1.VALUE;

      &oPendingItem.USER2 = &SelPndRecord.USER2.VALUE;

      &oPendingItem.REGION_CD = &SelPndRecord.REGION_CD.VALUE;

 

      &oPendingItem.PACKSLIP_NO = &SelPndRecord.PACKSLIP_NO.VALUE;

      &oPendingItem.VAT_ADVPAY_FLG = &SelPndRecord.VAT_ADVPAY_FLG.VALUE;

      &oPendingItem.LC_ID = &SelPndRecord.LC_ID.VALUE;

      &oPendingItem.RECEIVABLE_TYPE = &SelPndRecord.RECEIVABLE_TYPE.VALUE;

      &oPendingItem.ENTITY_CODE = &SelPndRecord.ENTITY_CODE.VALUE;

      &oPendingItem.PPRC_PROMO_CD = &SelPndRecord.PPRC_PROMO_CD.VALUE;

      &oPendingItem.CLAIM_NO = &SelPndRecord.CLAIM_NO.VALUE;

      &oPendingItem.CONTROL_CURRENCY = &SelPndRecord.CONTROL_CURRENCY.VALUE;

      &oPendingItem.CONTROL_CURR_BASE = &SelPndRecord.CONTROL_CURR_BASE.VALUE;

*>

      &i126 = &i126 + 1;

   End-While;

   &oPendingItemCollection.DeleteItem(1);

   rem ***** Execute Save *****;

   If Not &oDcPenditemCi.Save() Then;

      errorHandler();

      throw CreateException(0, 0, "Pending item save failed");

   Else

      Z_AR_ALNCE_AET.GROUP_ID.Value = &oDcPenditemCi.GROUP_ID;

      Z_AR_ALNCE_AET.GROUP_BU.Value = &oDcPenditemCi.GROUP_BU;

     

      MessageBox(0, "", 0, 0, "Group ID created -" | Z_AR_ALNCE_AET.GROUP_ID.Value);

   End-If;

  

   rem ***** Execute Cancel *****;

   rem If Not &oDcPenditemCi.Cancel() Then;

   rem     errorHandler();

   rem     throw CreateException(0, 0, "Cancel failed");

   rem End-If;

catch Exception &ex

   rem Handle the exception;

   MessageBox(0, "", 0, 0, &ex.ToString());

   Z_AR_ALNCE_AET.GROUP_ID.Value = " ";

   Z_AR_ALNCE_AET.GROUP_BU.Value = " ";

end-try;

 

 

Best Regards

Aravind Kumar R

System Analyst/Peoplesoft Practice
Birlasoft
36, Vijayaraghava Road , T. Nagar, Chennai 600 017 (INDIA)
Cell: +91 9566750166

************************************************************************************************************************************************************************************
"This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error please notify the mail.admin@birlasoft.com immediately without opening the mail and attachment . Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email
************************************************************************************************************************************************************************************

PeopleCode for reading the file using the rowset and file layout objects - file with headers & lines combination

PeopleSoft – PeopleCode for reading the file using the rowset and file layout objects, the file being read contains the header and lines & this combination repeats.


Function EditRecord(&REC As Record) Returns boolean;
   Local integer &E;
   REM   &REC.ExecuteEdits(%Edit_Required + %Edit_DateRange + %Edit_YesNo + %Edit_TranslateTable + %Edit_PromptTable + %Edit_OneZero);
   &REC.ExecuteEdits(%Edit_DateRange + %Edit_YesNo + %Edit_OneZero);
   If &REC.IsEditError Then
      For &E = 1 To &REC.FieldCount
         &MYFIELD = &REC.GetField(&E);
         If &MYFIELD.EditError Then
            &MSGNUM = &MYFIELD.MessageNumber;
            &MSGSET = &MYFIELD.MessageSetNumber;
            &LOGFILE.WriteLine("****Record:" | &REC.Name | ", Field:" | &MYFIELD.Name);
            &LOGFILE.WriteLine("****" | MsgGet(&MSGSET, &MSGNUM, ""));
         End-If;
      End-For;
      MessageBox(0, "", 0, 0, "Error(s) found in file " | &FileNameForReading | " is being read");
      &FileTemp = CreateRecord(Record.Z_AR_FILE_TMP);
      &FileTemp.PROCESS_INSTANCE.VALUE = Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value;
      &FileTemp.FILE_SEQ.VALUE = Z_AR_FILE_TMP.FILE_SEQ.Value;
      &FileTemp.ARCHIVE_PROCESS.VALUE = "D"; /*if errors found in file then do not archive this file*/
      &FileTemp.update();
      Return False;
   Else
      Return True;
   End-If;
End-Function;

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
         &REC2.PROCESS_INSTANCE.Value = Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value;
         &REC2.FILE_SEQ.Value = Z_AR_ALNCE_AET.FILE_SEQ.Value;
         &REC2.Z_AR_STATUS.Value = "P";
         
         If &REC2.Name = "Z_AR_GCTL_TMP" Then
            &REC2.OPRID.Value = %OperatorId;
            &REC2.ASSN_OPRID.Value = %OperatorId;
            MessageBox(0, "", 0, 0, "ORIGIN-EEE" | &REC2.ORIGIN_ID.Value | "EEE");
         End-If;
        
         
         
         &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 * PeopleCode to Import Data                                     *;
rem *****************************************************************;
Local File &FILE1;
Local Record &REC1;
Local SQL &SQL1;
Local Rowset &RS1, &RS2;
Local integer &M;

SQLExec("SELECT DESCR254 FROM PS_Z_AR_FILE_TMP WHERE PROCESS_INSTANCE = :1 AND FILE_SEQ = :2", Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value, Z_AR_ALNCE_AET.FILE_SEQ.Value, &FileNameForReading);
MessageBox(0, "", 0, 0, &FileNameForReading | " is being read");
&FILE1 = GetFile(&FileNameForReading, "r", "a", %FilePath_Absolute);
&LOGFILE = GetFile(&FileNameForReading | ".err", "W", %FilePath_Absolute);
&FILE1.SetFileLayout(FileLayout.Z_AR_ALLN_FLO);
&LOGFILE.SetFileLayout(FileLayout.Z_AR_ALLN_FLO);
&RS1 = &FILE1.CreateRowset();
&RS = CreateRowset(Record.Z_AR_GCTL_TMP, CreateRowset(Record.Z_AR_PITM_TMP));
&SQL1 = CreateSQL("%Insert(:1)");
&RS1 = &FILE1.ReadRowset();
While &RS1 <> Null;
   ImportSegment(&RS1, &RS);
   &RS1 = &FILE1.ReadRowset();
   rem MessageBox(0, "", 0, 0, "File contains " | &RS1.RowCount | " lines to be proccessed");
End-While;

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



************************************************************************************************************************************************************************************