Thursday, December 09, 2010

Component Interface Does Not Validate Record Edits (Against Prompt table) When Using Create

If you are creating Component interface by default it will not enforce the prompt values specified on Add search record and will let you input any values even though they are not valid. This is an issue when inserting new rows using ExcelToCI as it allows the user to enter invalid values without giving any error message.

To avoid this issue, open up the Add mode Search record for the component and go to Record field properties for the search fields and check Search Edit check box.  This will enforce the use of valid values when adding a new row using Component interface used in ExcelToCI. By default Search Edit is not checked. 

Here is the description of this field in PeopleBooks.

Search Edit    Enabled only if Search Key is selected. Selecting this option enforces the required property and table edits on the search page. It also enforces these edits under circumstances where the search page would normally be bypassed. With this option, the user no longer has the ability to perform partial searches on this field.

See the Resolution

E-CI: Component Interface Does Not Validate Record Edits (Against Prompt table) When Using Create [ID 664377.1]

If you want the partial search for this field to be enabled, then do the following.

Add the Search field as a read/write property in CI.

SQLExec : Return: 8015 - Bind value is too long

You get this error in an online page or while running a Application engine program. This error happens when you try to insert more than 254 characters in a long field using sqlexec and do not use %TextIn meta sql.

Resolution

Use %TextIn meta-sql for the bind variable that is used for inserting into a long field. For e.g. %TextIn(:1)

%TextIn is documented in peoplebooks and is mandatory for all insertions/update of LongChar fields using sqlexec for all database platforms.

Here are some resolutions that discusses this issue in Metalink – Oracle support site.

E-AE Application Engine PeopleCode Step with SQLExec Receives Error; return code 8015 "Bind value is too long" [ID 889806.1]

E-PC:"Bind value is too long" Error When Using SQLExec to Insert into Long Char Field [ID 620874.1]

Friday, December 03, 2010

Portal Content Reference Navigation Path

Very often you need to find out what is the Navigation Path for a given component name in PeopleSoft portal or HRMS or Financials or CRM?

If you are using 8.50 and 9.1 then you can find this information using Enterprise Components –> Find Object Navigation. You can search by component name, page name, Secondary Page name or Content Reference Name. Please note that you must be using application version 9.1 or higher. Just upgrading peopletools to 8.50 or higher will not enable this functionality.

 

image

Another alternative way is to use SQL. However this requires creating a PL/SQL function and is only applicable for ORACLE database only. Other database platforms may need to write their own functions to implement this functionality.

PL/SQL source code for the Function:

CREATE OR replace FUNCTION fx_get_portal_map (l_portal_name    VARCHAR2,
                                              l_portal_reftype VARCHAR2,
                                              l_portal_objname VARCHAR2,
                                              l_level          NUMBER,
                                              l_type           VARCHAR2,
l_count_max      INTEGER DEFAULT 10)
RETURN VARCHAR2
IS
  pl_count              INTEGER := 0;
  pl_portal_objname     psprsmdefn.portal_objname%TYPE := l_portal_objname;
  pl_portal_seq_num     psprsmdefn.portal_seq_num%TYPE := 0;
  pl_portal_label       psprsmdefn.portal_label%TYPE := ' ';
  pl_portal_prntobjname psprsmdefn.portal_prntobjname%TYPE := ' ';
  CURSOR cur_1 IS
    SELECT portal_prntobjname,
           portal_label,
           portal_seq_num
    FROM   psprsmdefn
    WHERE  portal_name = l_portal_name
           AND portal_reftype = l_portal_reftype
           AND portal_objname = pl_portal_objname;
BEGIN
  WHILE pl_count <> l_level LOOP
      pl_count := pl_count + 1;

      EXIT WHEN pl_count > l_count_max;

      OPEN cur_1;

      FETCH cur_1 INTO pl_portal_prntobjname, pl_portal_label, pl_portal_seq_num
      ;

      IF cur_1%found THEN
        pl_portal_objname := pl_portal_prntobjname;
      ELSE
        pl_portal_label := ' ';

        pl_portal_seq_num := 0;

        EXIT WHEN cur_1%notfound;
      END IF;

      CLOSE cur_1;
  END LOOP;

  IF l_type = 'S' THEN
    RETURN pl_portal_seq_num;
  ELSE
    RETURN pl_portal_label;
  END IF;
END; -- Function FX_GET_PORTAL_MAP

SQL Query to Get the Navigation. Note you can uncomment the portal_uri_seg2 to query for a specific component.

SELECT a.portal_objname,
       a.portal_linkobjname,
       a.portal_seq_num                              seq,
       Ltrim(Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L')
             ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L')
       || Decode(a.portal_label, ' ', '',
                                 ' > '
                                 || a.portal_label)) navigation,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,1,'L') LABEL1,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,2,'L') LABEL2,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,3,'L') LABEL3,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,4,'L') LABEL4,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,5,'L') LABEL5,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,6,'L') LABEL6,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,7,'L') LABEL7,
       a.portal_uri_seg1                             menuname,
       a.portal_uri_seg2                             component,
       a.portal_uri_seg3                             market,
       a.portal_urltext
FROM   psprsmdefn a
WHERE  a.portal_name = 'EMPLOYEE'
       AND a.portal_reftype = 'C'
       AND a.portal_prntobjname <> ' '
--AND A.PORTAL_URI_SEG2 = 'COMPONENT_NAME'
ORDER  BY Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'S')
          ||
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L')