AP_COMP_FLDS_V1

(SQL View)
 Index Back

Component Page Fields

cmh 10/26/2011 Finds all pages in component, then all fields on these pages. Record substitution is supported for subpages. Secondary pages are included also. Supports 8 levels of page nesting.

SELECT A.PNLGRPNAME PNLGRPNAME ,CASE WHEN C.PNLNAME IS NULL THEN B.PNLNAME WHEN D.PNLNAME IS NULL THEN C.PNLNAME WHEN E.PNLNAME IS NULL THEN D.PNLNAME WHEN F.PNLNAME IS NULL THEN E.PNLNAME WHEN G.PNLNAME IS NULL THEN F.PNLNAME WHEN H.PNLNAME IS NULL THEN G.PNLNAME ELSE H.PNLNAME END PNLNAME ,CASE WHEN C.PNLFLDID IS NULL THEN B.PNLFLDID WHEN D.PNLFLDID IS NULL THEN C.PNLFLDID WHEN E.PNLFLDID IS NULL THEN D.PNLFLDID WHEN F.PNLFLDID IS NULL THEN E.PNLFLDID WHEN G.PNLFLDID IS NULL THEN F.PNLFLDID WHEN H.PNLFLDID IS NULL THEN G.PNLFLDID ELSE H.PNLFLDID END PNLFLDID ,CASE WHEN C.FIELDTYPE IS NULL THEN B.FIELDTYPE WHEN D.FIELDTYPE IS NULL THEN C.FIELDTYPE WHEN E.FIELDTYPE IS NULL THEN D.FIELDTYPE WHEN F.FIELDTYPE IS NULL THEN E.FIELDTYPE WHEN G.FIELDTYPE IS NULL THEN F.FIELDTYPE WHEN H.FIELDTYPE IS NULL THEN G.FIELDTYPE ELSE H.FIELDTYPE END FIELDTYPE ,CASE WHEN C.LBLTEXT IS NULL THEN B.LBLTEXT WHEN D.LBLTEXT IS NULL THEN C.LBLTEXT WHEN E.LBLTEXT IS NULL THEN D.LBLTEXT WHEN F.LBLTEXT IS NULL THEN E.LBLTEXT WHEN G.LBLTEXT IS NULL THEN F.LBLTEXT WHEN H.LBLTEXT IS NULL THEN G.LBLTEXT ELSE H.LBLTEXT END LBLTEXT ,CASE WHEN C.FIELDUSE IS NULL THEN B.FIELDUSE WHEN D.FIELDUSE IS NULL THEN C.FIELDUSE WHEN E.FIELDUSE IS NULL THEN D.FIELDUSE WHEN F.FIELDUSE IS NULL THEN E.FIELDUSE WHEN G.FIELDUSE IS NULL THEN F.FIELDUSE WHEN H.FIELDUSE IS NULL THEN G.FIELDUSE ELSE H.FIELDUSE END FIELDUSE ,CASE WHEN C.RECNAME IS NULL THEN B.RECNAME WHEN D.RECNAME IS NULL THEN C.RECNAME WHEN E.RECNAME IS NULL THEN D.RECNAME WHEN F.RECNAME IS NULL THEN E.RECNAME WHEN G.RECNAME IS NULL THEN F.RECNAME WHEN H.RECNAME IS NULL THEN G.RECNAME ELSE H.RECNAME END RECNAME ,CASE WHEN C.FIELDNAME IS NULL THEN B.FIELDNAME WHEN D.FIELDNAME IS NULL THEN C.FIELDNAME WHEN E.FIELDNAME IS NULL THEN D.FIELDNAME WHEN F.FIELDNAME IS NULL THEN E.FIELDNAME WHEN G.FIELDNAME IS NULL THEN F.FIELDNAME WHEN H.FIELDNAME IS NULL THEN G.FIELDNAME ELSE H.FIELDNAME END FIELDNAME , %TrimSubstr(A.PNLGRPNAME %Concat ' : ' %Concat B.PNLNAME %Concat ' ' %Concat %NumToChar(B.FIELDTYPE) %Concat CASE WHEN C.PNLNAME IS NULL THEN '' ELSE ' > ' %Concat C.PNLNAME %Concat ' ' %Concat %NumToChar(C.FIELDTYPE) %Concat CASE WHEN D.PNLNAME IS NULL THEN '' ELSE ' > ' %Concat D.PNLNAME %Concat ' ' %Concat %NumToChar(D.FIELDTYPE) %Concat CASE WHEN E.PNLNAME IS NULL THEN '' ELSE ' > ' %Concat E.PNLNAME %Concat ' ' %Concat %NumToChar(E.FIELDTYPE) %Concat CASE WHEN F.PNLNAME IS NULL THEN '' ELSE ' > ' %Concat F.PNLNAME %Concat ' ' %Concat %NumToChar(F.FIELDTYPE) %Concat CASE WHEN G.PNLNAME IS NULL THEN '' ELSE ' > ' %Concat G.PNLNAME %Concat ' ' %Concat %NumToChar(G.FIELDTYPE) %Concat CASE WHEN H.PNLNAME IS NULL THEN '' ELSE ' > ' %Concat H.PNLNAME %Concat ' ' %Concat %NumToChar(H.FIELDTYPE) END END END END END END,1,254) PATHNAME , CASE B.FIELDTYPE WHEN 18 THEN B.SUBPNLNAME WHEN 21 THEN B.SUBPNLNAME ELSE CASE C.FIELDTYPE WHEN 18 THEN C.SUBPNLNAME WHEN 21 THEN C.SUBPNLNAME ELSE CASE D.FIELDTYPE WHEN 18 THEN D.SUBPNLNAME WHEN 21 THEN D.SUBPNLNAME ELSE CASE E.FIELDTYPE WHEN 18 THEN E.SUBPNLNAME WHEN 21 THEN E.SUBPNLNAME ELSE CASE F.FIELDTYPE WHEN 18 THEN F.SUBPNLNAME WHEN 21 THEN F.SUBPNLNAME ELSE CASE G.FIELDTYPE WHEN 18 THEN G.SUBPNLNAME WHEN 21 THEN G.SUBPNLNAME ELSE ' ' END END END END END END SUBPNLNAME , CASE G.FIELDTYPE WHEN 18 THEN G.SUBPNLNAME WHEN 21 THEN G.SUBPNLNAME ELSE CASE F.FIELDTYPE WHEN 18 THEN F.SUBPNLNAME WHEN 21 THEN F.SUBPNLNAME ELSE CASE E.FIELDTYPE WHEN 18 THEN E.SUBPNLNAME WHEN 21 THEN E.SUBPNLNAME ELSE CASE D.FIELDTYPE WHEN 18 THEN D.SUBPNLNAME WHEN 21 THEN D.SUBPNLNAME ELSE CASE C.FIELDTYPE WHEN 18 THEN C.SUBPNLNAME WHEN 21 THEN C.SUBPNLNAME ELSE CASE B.FIELDTYPE WHEN 18 THEN B.SUBPNLNAME WHEN 21 THEN B.SUBPNLNAME ELSE ' ' END END END END END END DTL_PNLNAME , %Substring(CASE WHEN H.FIELDTYPE = 11 THEN H.PNLNAME WHEN G.FIELDTYPE = 11 THEN G.PNLNAME WHEN F.FIELDTYPE = 11 AND H.FIELDTYPE IS NULL THEN F.PNLNAME WHEN E.FIELDTYPE = 11 AND G.FIELDTYPE IS NULL THEN E.PNLNAME WHEN D.FIELDTYPE = 11 AND F.FIELDTYPE IS NULL THEN D.PNLNAME WHEN C.FIELDTYPE = 11 AND E.FIELDTYPE IS NULL THEN C.PNLNAME WHEN B.FIELDTYPE = 11 AND D.FIELDTYPE IS NULL THEN B.PNLNAME ELSE CASE WHEN F.PNLNAME IS NOT NULL THEN F.PNLNAME WHEN E.PNLNAME IS NOT NULL THEN E.PNLNAME WHEN D.PNLNAME IS NOT NULL THEN D.PNLNAME WHEN C.PNLNAME IS NOT NULL THEN C.PNLNAME ELSE B.PNLNAME END END,1,18) SBP_PAGE_NAME , CASE WHEN G.FIELDTYPE = 11 AND G.RECNAME <> ' ' AND H.RECNAME = ( SELECT MIN(RECNAME) FROM PSPNLFIELD WHERE PNLNAME = G.SUBPNLNAME AND PNLFLDID = 1) THEN G.RECNAME WHEN F.FIELDTYPE = 11 AND H.FIELDTYPE IS NULL AND F.RECNAME <> ' ' AND G.RECNAME = ( SELECT MIN(RECNAME) FROM PSPNLFIELD WHERE PNLNAME = F.SUBPNLNAME AND PNLFLDID = 1) THEN F.RECNAME WHEN E.FIELDTYPE = 11 AND G.FIELDTYPE IS NULL AND E.RECNAME <> ' ' AND F.RECNAME = ( SELECT MIN(RECNAME) FROM PSPNLFIELD WHERE PNLNAME = E.SUBPNLNAME AND PNLFLDID = 1) THEN E.RECNAME WHEN D.FIELDTYPE = 11 AND F.FIELDTYPE IS NULL AND D.RECNAME <> ' ' AND E.RECNAME = ( SELECT MIN(RECNAME) FROM PSPNLFIELD WHERE PNLNAME = D.SUBPNLNAME AND PNLFLDID = 1) THEN D.RECNAME WHEN C.FIELDTYPE = 11 AND E.FIELDTYPE IS NULL AND C.RECNAME <> ' ' AND D.RECNAME = ( SELECT MIN(RECNAME) FROM PSPNLFIELD WHERE PNLNAME = C.SUBPNLNAME AND PNLFLDID = 1) THEN C.RECNAME WHEN B.FIELDTYPE = 11 AND D.FIELDTYPE IS NULL AND B.RECNAME <> ' ' AND C.RECNAME = ( SELECT MIN(RECNAME) FROM PSPNLFIELD WHERE PNLNAME = B.SUBPNLNAME AND PNLFLDID = 1) THEN B.RECNAME ELSE CASE WHEN H.RECNAME IS NOT NULL THEN H.RECNAME WHEN G.RECNAME IS NOT NULL THEN G.RECNAME WHEN F.RECNAME IS NOT NULL THEN F.RECNAME WHEN E.RECNAME IS NOT NULL THEN E.RECNAME WHEN D.RECNAME IS NOT NULL THEN D.RECNAME WHEN C.RECNAME IS NOT NULL THEN C.RECNAME ELSE B.RECNAME END END RECNAME_PARENT, %Substring(A.PNLNAME,1,18) PAGE_NAME FROM PSPNLGROUP A , PSPNLFIELD B LEFT OUTER JOIN PSPNLFIELD C ON C.PNLNAME = B.SUBPNLNAME LEFT OUTER JOIN PSPNLFIELD D ON D.PNLNAME = C.SUBPNLNAME LEFT OUTER JOIN PSPNLFIELD E ON E.PNLNAME = D.SUBPNLNAME LEFT OUTER JOIN PSPNLFIELD F ON F.PNLNAME = E.SUBPNLNAME LEFT OUTER JOIN PSPNLFIELD G ON G.PNLNAME = F.SUBPNLNAME LEFT OUTER JOIN PSPNLFIELD H ON H.PNLNAME = G.SUBPNLNAME WHERE B.PNLNAME = A.PNLNAME AND CASE B.FIELDTYPE WHEN 18 THEN ( SELECT 1 FROM PSPNLFIELD WHERE PNLNAME = B.PNLNAME AND SUBPNLNAME = B.SUBPNLNAME AND FIELDTYPE = 21 ) ELSE NULL END IS NULL AND CASE C.FIELDTYPE WHEN 18 THEN ( SELECT 1 FROM PSPNLFIELD WHERE PNLNAME = C.PNLNAME AND SUBPNLNAME = C.SUBPNLNAME AND FIELDTYPE = 21 ) ELSE NULL END IS NULL AND CASE D.FIELDTYPE WHEN 18 THEN ( SELECT 1 FROM PSPNLFIELD WHERE PNLNAME = D.PNLNAME AND SUBPNLNAME = D.SUBPNLNAME AND FIELDTYPE = 21 ) ELSE NULL END IS NULL AND CASE E.FIELDTYPE WHEN 18 THEN ( SELECT 1 FROM PSPNLFIELD WHERE PNLNAME = E.PNLNAME AND SUBPNLNAME = E.SUBPNLNAME AND FIELDTYPE = 21 ) ELSE NULL END IS NULL AND CASE F.FIELDTYPE WHEN 18 THEN ( SELECT 1 FROM PSPNLFIELD WHERE PNLNAME = F.PNLNAME AND SUBPNLNAME = F.SUBPNLNAME AND FIELDTYPE = 21 ) ELSE NULL END IS NULL AND CASE G.FIELDTYPE WHEN 18 THEN ( SELECT 1 FROM PSPNLFIELD WHERE PNLNAME = G.PNLNAME AND SUBPNLNAME = G.SUBPNLNAME AND FIELDTYPE = 21 ) ELSE NULL END IS NULL

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PNLGRPNAME Character(18) VARCHAR2(18) NOT NULL Component Name
2 PNLNAME Character(30) VARCHAR2(30) NOT NULL Page Name (see PSPNLDEFN).
3 PNLFLDID Number(4,0) SMALLINT NOT NULL Page Field ID
4 FIELDTYPE Number(2,0) SMALLINT NOT NULL Field Type (from PSST0101 Ref.)
5 LBLTEXT Character(60) VARCHAR2(60) NOT NULL Label Text
6 FIELDUSE Number(10,0) DECIMAL(10) NOT NULL Panel Field Use
7 RECNAME Character(15) VARCHAR2(15) NOT NULL Record (Table) Name (see PSRECDEFN).
8 FIELDNAME Character(18) VARCHAR2(18) NOT NULL Field Name (see PSDBFIELD).
9 PATHNAME Character(254) VARCHAR2(254) NOT NULL PATHNAME
10 SUBPNLNAME Character(18) VARCHAR2(18) NOT NULL SubPanel Name
11 DTL_PNLNAME Character(18) VARCHAR2(18) NOT NULL Tree Structure Detail Panel/Page name
12 SPB_PAGE_NAME Character(18) VARCHAR2(18) NOT NULL Page Name
13 RECNAME_PARENT Character(15) VARCHAR2(15) NOT NULL Parent Record. If this comes from a sub-record, this column has the name of the sub-record, otherwise it has the same value as RECNAME.
14 PAGE_NAME Character(18) VARCHAR2(18) NOT NULL Page Name