You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Dynamic Views - PeopleSoft V8

1 reply [Last post]
stv_mlm's picture
User offline. Last seen 11 years 3 weeks ago. Offline
Joined: 06/22/2009
Posts: 1

I have created a dynamic view (to be used as a prompt) in our PeopleSoft Version 9 environment to pull back values based on what is getting returned from a business interlink. Since our Version 9 environment is not available yet, we need to apply something similar to our PeopleSoft Version 8 environment. When I tried to apply this same logic, it is saying that the SQLText is not a valid property of the field class. I was just wondering if there is something equivalent to this SQLText property in Version 8 or how I should go about doing something similar.

PeopleCode from Version 9:

      Evaluate %Component
      When = Component.FHIM_FH_INVOICE
         /*Logic to loop through the interlink*/
         &ROWSDoc = &ODOCRESULTDoc.GetDoc("ROWS");
         
         &ROWDoc = &ROWSDoc.GetDoc("ROW");
         &NumRows = &ROWSDoc.GetCount("ROW");
         &BU_LIST = "";
         
         For &i = 1 To &NumRows
            
            &NumCols = &ROWDoc.GetCount("COL");
            For &j = 1 To &NumCols
               
               &ret = &ROWDoc.GetValue("COL", &COL);
               
               /*Populate the &BU_LIST variable to be used with the Select Statement for the 
               dynamic view*/
               If None(&BU_LIST) Then
                  &BU_LIST = "'" | &COL | "'";
               Else
                  &BU_LIST = &BU_LIST | ", '" | &COL | "'";
               End-If;
               
            End-For;
            &ret = &ROWDoc.GetNextDoc();
         End-For;
         
         /*Build the Select Statement for the Dynamic view*/
         
         /*If there are no items in the queue, pull back no business units*/
         If None(&BU_LIST) Then
            &BU_LIST = "''";
         End-If;
         &BU_SELECT = "SELECT DISTINCT BUSINESS_UNIT FROM PS_FHIM_Q_SOURCE WHERE 
         BUSINESS_UNIT IN (" | &BU_LIST | ")";
         
         /*Assign the SQL for the Dynamic view*/
         GetRecord(Record.FHIM_INV_WRK).GetField(Field.BUSINESS_UNIT).SqlText = &BU_SELECT;
         Break;
      When-Other
         Break;
      End-Evaluate;

Lepa's picture
User offline. Last seen 1 year 39 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Dynamic Views - PeopleSoft V8

You are right, SqlText doesn't work in PS8 and there isn't anything similar to it, sorry :( - BUT if all what you are trying to do is create the below select, then i would suggest using the "Select" and "SelectNew" methods.

SELECT DISTINCT BUSINESS_UNIT FROM PS_FHIM_Q_SOURCE WHERE 
         BUSINESS_UNIT IN (" | &BU_LIST | ")"

you can create a regular view that has all BUs from PS_FHIM_Q_SOURCE and then using PeopleCode you can filter what data you want to show to the user.

Example:

&rs.Select(Record.You_view, " WHERE BUSINESS_UNIT IN (" | &BU_LIST | ")");

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog