SQL for Project Consolidation/Merge Check

========================================================================================
                  Project Details Quary :
========================================================================================
Step 1 : Identify No. of objects that are not present in the consolidated project
Step 2 : Object Count of all Project & consolidated Project
Step 3 : Project dependancy with each other

==========================================================================
Step 1 : Identify No. of objects that are not present in the consolidated project
==========================================================================
/*No. of objects that are not present in the consolidated project*/
SELECT   Count(objecttype) FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_CR_681_SR', 'UNBLR_TAM_CR681_PRINT')  -- List of Induvisual Project
 and OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  Not IN (
select Distinct OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  FullObject  FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_BATCHIV_2_23FEB2012'))  -- Consolidated Project
/*No. of objects (Object Typewise) that are not present in the consolidated project*/
SELECT  case objecttype
WHEN 0 THEN 'Record'
WHEN 2  THEN 'Field'
WHEN 5 THEN 'Page'
WHEN 30 THEN  'SQL'
WHEN 25 THEN  'Message Cateloge'
WHEN 44 THEN 'Page Peoplecode'
WHEN 46 THEN  'Component Peoplecode'
WHEN 4 THEN 'Translate Value'
WHEN 8 THEN 'Record Peoplecode'
WHEN 47 THEN 'Component Record peoplecode'
WHEN 48 THEN  'Component Record Field Peoplecode'
WHEN 58 THEN  'Application Package Peoplecode'
else
'Others :'|| objecttype
End objecttype,
 Count(objecttype) FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_CR_623','UNBLR_TAM_CR_631','UNBLR_TAM_CR635', 'UNBKK_CR638','UNBKK_CR639', 'UNBLR_TAM_CR_643','UNBLR_TAM_CR_644','UNBLR_TAM_CR649','UNBLR_TAM_CR650','UNBLR_TAM_CR_661'
 ,'UNBLR_TAM_CR_662','UNBLR_TAM_CR665','UNBLR_TAM_CR668','UNBLR_TAM_CR_669','UNBLR_TAM_CR671','UNBLR_TAM_CR_673','UNBLR_TAM_CR674'
 ,'UNBLR_TAM_CR_675','UNBLR_TAM_CR677','UNBLR_TAM_CR_680','UNBLR_TAM_CR_691','UNBLR_TAM_CR_640','UNBLR_TAM_CR_618','UNBLR_TAM_DEF_641','UNBLR_TAM_DEF_637') -- List of Induvisual Project
 and OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  Not IN (
select Distinct OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  FullObject  FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_BATCHIV_28NOV2011')) -- Consolidated Project
Group By  objecttype

/* List of objects that are not present in the consolidated project*/
select Distinct PROJECTNAME,
 case objecttype
WHEN 0 THEN 'Record'
WHEN 2  THEN 'Field'
WHEN 5 THEN 'Page'
WHEN 30 THEN  'SQL'
WHEN 25 THEN  'Message Cateloge'
WHEN 44 THEN 'Page Peoplecode'
WHEN 46 THEN  'Component Peoplecode'
WHEN 4 THEN 'Translate Value'
WHEN 8 THEN 'Record Peoplecode'
WHEN 47 THEN 'Component Record peoplecode'
WHEN 48 THEN  'Component Record Field Peoplecode'
WHEN 58 THEN  'Application Package Peoplecode'
else
'Others'|| objecttype
End objecttype,
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4  FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_CR_623','UNBLR_TAM_CR_631','UNBLR_TAM_CR635', 'UNBKK_CR638','UNBKK_CR639', 'UNBLR_TAM_CR_643','UNBLR_TAM_CR_644','UNBLR_TAM_CR649','UNBLR_TAM_CR650','UNBLR_TAM_CR_661'
 ,'UNBLR_TAM_CR_662','UNBLR_TAM_CR665','UNBLR_TAM_CR668','UNBLR_TAM_CR_669','UNBLR_TAM_CR671','UNBLR_TAM_CR_673','UNBLR_TAM_CR674'
 ,'UNBLR_TAM_CR_675','UNBLR_TAM_CR677','UNBLR_TAM_CR_680','UNBLR_TAM_CR_691','UNBLR_TAM_CR_640','UNBLR_TAM_CR_618','UNBLR_TAM_DEF_641','UNBLR_TAM_DEF_637')  -- List of Induvisual Project
 and OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  Not IN (
select Distinct OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  FullObject  FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_BATCHIV_28NOV2011')) -- Consolidated Project

==========================================================================
Step 2 : Object Count of all Project & consolidated Project
==========================================================================

/* Object Count of all Project & consolidated Project*/
--------------------------------------------------------
SELECT  case A.objecttype
WHEN 0 THEN 'Record'
WHEN 2  THEN 'Field'
WHEN 5 THEN 'Page'
WHEN 30 THEN  'SQL'
WHEN 25 THEN  'Message Cateloge'
WHEN 44 THEN 'Page Peoplecode'
WHEN 46 THEN  'Component Peoplecode'
WHEN 4 THEN 'Translate Value'
WHEN 8 THEN 'Record Peoplecode'
WHEN 47 THEN 'Component Record peoplecode'
WHEN 48 THEN  'Component Record Field Peoplecode'
WHEN 58 THEN  'Application Package Peoplecode'
else
'Others'
End objecttype, ProjectCount, ConsolidateProjectCount FROm
(SELECT   objecttype,
 Count(objecttype) ProjectCount   FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN  ('UNBLR_TAM_CR_629','UNBLR_TAM_CR_664_664B','UNBLR_TAM_DEF_671', 'UNBLR_TAM_DEF_673','UNBLR_TAM_DEF_674','UNBLR_TAM_DEF_675','UNBLR_TAM_DEF_678')  -- List of Induvisual Project
Group By  objecttype) A, (SELECT   objecttype,
 Count(objecttype) ConsolidateProjectCount FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_BATCHIV_5_27MAR2012') -- Consolidated Project
Group By  objecttype) B
WHERE A.objecttype = B.objecttype(+)

==========================================================================
Step 3 : Project dependancy with each other
==========================================================================
/*Project dependancy with each other*/
-------------------------------------
SELECT  A.PROJECTNAME, B.PROJECTNAME ConflictingProject,  Count(A.objecttype) FROM PSPROJECTITEM A, PSPROJECTITEM B
 WHERE  A.PROJECTNAME IN ('UNBLR_TAM_CR_629','UNBLR_TAM_CR_664_664B','UNBLR_TAM_DEF_671', 'UNBLR_TAM_DEF_673','UNBLR_TAM_DEF_674','UNBLR_TAM_DEF_675','UNBLR_TAM_DEF_678')  -- List of Induvisual Project
  AND  B.PROJECTNAME IN  ('UNBLR_TAM_CR_629','UNBLR_TAM_CR_664_664B','UNBLR_TAM_DEF_671', 'UNBLR_TAM_DEF_673','UNBLR_TAM_DEF_674','UNBLR_TAM_DEF_675','UNBLR_TAM_DEF_678')  -- List of Induvisual Project
 and A.OBJECTVALUE1||'.'||A.OBJECTVALUE2||'.'||A.OBJECTVALUE3||'.'||A.OBJECTVALUE4  =  B.OBJECTVALUE1||'.'||B.OBJECTVALUE2||'.'||B.OBJECTVALUE3||'.'||B.OBJECTVALUE4
 and A.PROJECTNAME <> B.PROJECTNAME
 Group BY A.PROJECTNAME, B.PROJECTNAME

Finding Menu Path

Here are some good SQL's to have in your library.

When a process name is known:

SELECT DISTINCT
PRCS.PRCSTYPE,
PRCS.PRCSNAME,
PRCS.DESCR,
PAGE.PNLGRPNAME as Component,
'Home > ' || RTRIM(M.MENUGROUP) || ' > ' || RTRIM(M.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as Location
FROM PSMENUDEFN M,
PSMENUITEM ITEM,
PS_PRCSDEFNPNL PAGE,
PS_PRCSDEFN PRCS
WHERE M.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PRCS.PRCSNAME = 'PY_PULL_COST'


When a component name is known:

SELECT DISTINCT PRCS.PRCSTYPE , PRCS.PRCSNAME , PRCS.DESCR ,
PAGE.PNLGRPNAME as Component , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PS_PRCSDEFNPNL PAGE , PS_PRCSDEFN PRCS
WHERE MENU.MENUNAME = ITEM.MENUNAMEAND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PAGE.PNLGRPNAME = 'component-name'

When a record name is known:

SELECT DISTINCT PFLD.RECNAME ,
PFLD.PNLNAME as Page , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP , PSPNLFIELD PFLD
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = PFLD.PNLNAME
AND PFLD.RECNAME = 'record-name'

When a page name is known:

SELECT DISTINCT COMP.PNLNAME as Page , 'Home > ' ||
RTRIM(MENU.MENUGROUP) || ' > ' || RTRIM(MENU.MENULABEL) || ' > ' ||
RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = 'page-name'

PS Listing of Payroll / HR tables

Here is a great sample SQL for capturing PS Payroll and HR tables within PS.

select a.RECNAME, decode(a.SQLTABLENAME, ' ', 'PS_' || a.recname, a.sqltablename) table_name, a.rectype, a.OBJECTOWNERID, xlat.xlatlongname, num_rows
from psrecdefn a, psxlatitem xlat, all_tables b
where a.objectownerid in ('AWFA', 'FGL', 'HBA', 'HBN', 'HEB', 'HER', 'HFSA', 'HHP', 'HHR', 'HMCF', 'HPY', 'HRAM', 'HRAT', 'HSP', 'HTC', 'HTL', 'HTLA', 'HTLI', 'HTLR', 'HTLX', 'HRAM','HRAT','HSP' )
and xlat.fieldname = 'OBJECTOWNERID'
and xlat.fieldvalue = a.OBJECTOWNERID
and xlat.effdt = (select max(xlat1.effdt) from psxlatitem xlat1
where xlat1.fieldname = xlat.fieldname
and xlat1.fieldvalue = xlat.fieldvalue)
and b.table_name = decode(a.SQLTABLENAME, ' ', 'PS_' || a.recname, a.sqltablename)
and b.NUM_ROWS > 0

SQL Quaries for Calender and dates

/* Days week calender */
SELECT INITCAP(TRIM(TO_CHAR(dat,   'month'))) || ', ' || TO_CHAR(SYSDATE,   'yyyy') MONTH,
  MAX(DECODE(TO_CHAR(dat,   'd'),   2,   TO_CHAR(dat,   'dd'))) mon,
  MAX(DECODE(TO_CHAR(dat,   'd'),   3,   TO_CHAR(dat,   'dd'))) tue,
  MAX(DECODE(TO_CHAR(dat,   'd'),   4,   TO_CHAR(dat,   'dd'))) wed,
  MAX(DECODE(TO_CHAR(dat,   'd'),   5,   TO_CHAR(dat,   'dd'))) thu,
  MAX(DECODE(TO_CHAR(dat,   'd'),   6,   TO_CHAR(dat,   'dd'))) fri,
  MAX(DECODE(TO_CHAR(dat,   'd'),   7,   TO_CHAR(dat,   'dd'))) sat,
  MAX(DECODE(TO_CHAR(dat,   'd'),   1,   TO_CHAR(dat,   'dd'))) sun
FROM
  (SELECT TRUNC(SYSDATE,    'y') + ROWNUM -1 dat,
     TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'iw') woy
   FROM user_objects
   WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE,    'y'),    12) -TRUNC(SYSDATE,    'y')))
GROUP BY TO_CHAR(dat,   'month'),
  woy,
  TO_CHAR(dat,   'mm')
  
/*First and Last day of all month of Current year*/ 
    select MON ,First_day,last_day(First_day) last_day
    from ( 
SELECT TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'MM') mon ,
      TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'DD') DAy,
      TRUNC(SYSDATE,    'y') + ROWNUM -1 First_day
   FROM user_objects
   WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE,    'y'),    12) -TRUNC(SYSDATE,    'y'))) cal
   where  DAY=01                                                                                                                                                            
/*First and Last day of all month of Current Fiscal year*/ 
    select MON ,First_day,last_day(First_day) last_day
    from ( 
SELECT TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'MM') mon ,
      TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'DD') DAy,
      TRUNC(SYSDATE,    'y') + ROWNUM -1 First_day
   FROM user_objects
   WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE,    'y'),    15) -TRUNC(SYSDATE,    'y'))
   ) cal
   where  DAY=01 and First_day >(ADD_MONTHS(TRUNC(SYSDATE,    'y'),    2) + 30)

/*Calender*/
SELECT TRUNC(SYSDATE,    'y') + ROWNUM -1 dat,
     TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'iw') woy
   FROM PS_RS_SO_LINE

/*Get a specific Month*/
Select ADD_MONTHS(TRUNC(SYSDATE,    'y'),    2) from DUAL

/*Calender ( all date of current year)*/
   SELECT TRUNC(SYSDATE,    'y') + ROWNUM -1 dat,
     TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'iw') woy
   FROM PS_RS_SO_LINE
   WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE,    'y'),    12) -TRUNC(SYSDATE,    'y'))

/* First day of current year*/
   Select TRUNC(SYSDATE,    'y') from DUAL

Find the Menu Navigation for PeopleSoft Components or Processes

If you're like me, there are a lot of times when you need to make changes to a particular component or perhaps you need to run a particular process. You might know the component or processess name, but you have no idea how to navigate to the particular component or process through the PeopleSoft navigation.


Here is a great tip for a method to finding the portal hierarchy path to a particular component. Let's say that we are trying to find the JOB_DATA component in HCM. Yes, I know most people know the navigation for this one. However, this is just an example, so bare with me. You can use any component name that you desire. I am using JOB_DATA for simplicity. If you have access to run a SQL, below is a piece of code that uses the CONNECT BY PRIOR command that will help you to find the navigation.


select lpad(' ', 10*level-10) || PS.PORTAL_LABEL

from PSPRSMDEFN PSstart with PS.PORTAL_URI_SEG2 = 'JOB_DATA'

and PS.PORTAL_URI_SEG3 in ('GBL','USA')

connect by prior PS.PORTAL_PRNTOBJNAME = PS.PORTAL_OBJNAME

and PS.PORTAL_NAME = 'EMPLOYEE'




This SQL gives the following results:




Job Data

Job Information

Workforce Administration

Root

CONNECT BY PRIOR is a condition that identifies the relationship between parent rows and child rows of the hierarchy. Here is the syntax.


Syntax:

SELECT...
[START WITH initial_condition]
CONNECT BY [nocycle] PRIOR recurse_condition
[ORDER SIBLINGS BY order_by_clause]




Key:

START WITH : The row(s) to be used as the root of the hierarchy

CONNECT BY : Condition that identifies the relationship between parent and child rows of the hierarchy

NOCYCLE : Do not circle around loops (where the current row has a child which is also its ancestor.)

ORDER SIBLINGS BY : Preserve ordering of the hierarchical query then apply the order_by_clause to the sibling rows

Now, let's say that you are looking for the navigation of a particular process name, you can use the SQL (slightly modified) to get the results you are looking for. Let's look for the navigation to the process named 'PSPPYRUN.'


select lpad(' ', 10*level-10) || PS.PORTAL_LABEL
from PSPRSMDEFN PS
start with PS.PORTAL_URI_SEG2 in
(select PSB.PNLGRPNAME from PS_PRCSDEFNPNL PSB where PSB.PRCSNAME = 'PSPPYRUN')
and PS.PORTAL_URI_SEG3 in ('GBL','USA')
connect by prior PS.PORTAL_PRNTOBJNAME = PS.PORTAL_OBJNAME
and PS.PORTAL_NAME = 'EMPLOYEE'




This SQL returns the following results:




Calculate Pay

Run Payroll

Payroll Processing

North American Payroll

Root

If you have a bit more time, you can create a PeopleSoft component/page where you can select the process or the component from a list and press a button to get the results in a grid. I'll leave that up to you. I hope this helps you become more efficient.







If you have any tips or other suggestions on this topic, please add your comments to this thread.