Identifying Asynchronous Integration Points

Every time we do any sort of upgrade on Tools or Application I find myself writing this sql to identify what Async Transactions we are using so I can add them to my test plan.  So here is how I identify my Integration Broker messages that I need to test.

SELECT A.ROUTINGDEFNNAME 
 , A.ib_operationname
 , A.VERSIONNAME
 , A.SENDERNODENAME
 , A.RECEIVERNODENAME 
  FROM PSIBRTNGDEFN A 
 WHERE EFF_STATUS = 'A' 
   AND EXISTS (
 SELECT 'X' 
  FROM PSOPRVERDFN_VW X 
 WHERE a.ib_operationname = X.ib_operationname 
   AND a.versionname = X.versionname 
   AND X.ACTIVE_FLAG = 'A') 
   AND EXISTS (
 SELECT 'X' 
  FROM PSAPMSGPUBHDR X 
 WHERE a.ib_operationname = X.ib_operationname);

I select of the routing table where the service operation is active and them I look for actual transactions that have been previously published.  Now I do run the risk of missing an infrequent transaction that might be configured, but I have never actually encountered an upgrade related issue regarding asynchronous message in PeopleSoft.  So I am pretty happy with this approach.

More Common Table Expressions!

Maybe I am beating a dead horse here, but I use CTE's daily.  Today I was asked to look into self service address changes that were pending approval to provide a list of address changes that have not been processed.

First I created a SQL statement that allowed me to identify active employee's with pending address changes on the HR_ADDR_CHG_DAT.

This is what I came up with:

SELECT *
  FROM PS_HR_ADDR_CHG_DAT A
 WHERE ACTION_DT_SS = (
 SELECT MAX(B.ACTION_DT_SS)
  FROM PS_HR_ADDR_CHG_DAT B
 WHERE A.EMPLID = B.EMPLID
   AND A.ADDRESS_TYPE = B.ADDRESS_TYPE
   AND B.ACTION_DT_SS <= TO_DATE(SYSDATE))
   AND A.EFFSEQ = (
 SELECT MAX(Z.EFFSEQ)
  FROM PS_HR_ADDR_CHG_DAT Z
 WHERE A.EMPLID = Z.EMPLID
   AND A.ADDRESS_TYPE = Z.ADDRESS_TYPE
   AND A.ACTION_DT_SS = Z.ACTION_DT_SS)
   AND A.ADDRESS_TYPE = 'HOME'
   AND A.SS_STAT_INDICATOR = 'M'
   AND EXISTS(
 SELECT 'X'
  FROM PS_EMPLOYEES X
 WHERE A.EMPLID = X.EMPLID )
 Then I knew I wanted to compare the results of this SQL with current address data that can be found in the PS_ADDRESSES4_VW.  So rather then joining this record into the SQL above I used a CTE to create a dynamic view and joined the current address record to the CTE.

 WITH ADDR_CHG AS (
 SELECT *
  FROM PS_HR_ADDR_CHG_DAT A
 WHERE ACTION_DT_SS = (
 SELECT MAX(B.ACTION_DT_SS)
  FROM PS_HR_ADDR_CHG_DAT B
 WHERE A.EMPLID = B.EMPLID
   AND A.ADDRESS_TYPE = B.ADDRESS_TYPE
   AND B.ACTION_DT_SS <= TO_DATE(SYSDATE))
   AND A.EFFSEQ = (
 SELECT MAX(Z.EFFSEQ)
  FROM PS_HR_ADDR_CHG_DAT Z
 WHERE A.EMPLID = Z.EMPLID
   AND A.ADDRESS_TYPE = Z.ADDRESS_TYPE
   AND A.ACTION_DT_SS = Z.ACTION_DT_SS)
   AND A.ADDRESS_TYPE = 'HOME'
   AND A.SS_STAT_INDICATOR = 'M'
   AND EXISTS(
 SELECT 'X'
  FROM PS_ZZ_IDM_DATA X
 WHERE A.EMPLID = X.EMPLID2
   AND X.HR_STATUS IN ('A','L')))
 SELECT SS.EMPLID
 , SS.effseq
 , SS.ADDRESS_TYPE
 , SS.ADDRESS1 SS_ADDRESS1
 , CUR.ADDRESS1
 , SS.ADDRESS2 SS_ADDRESS2
 , CUR.ADDRESS2
 , SS.CITY SS_CITY
 , CUR.CITY
 , SS.POSTAL SS_POSTAL
 , CUR.POSTAL
 , SS.STATE SS_STATE
 , CUR.STATE
  FROM ADDR_CHG SS
  , PS_ADDRESSES4_VW CUR
 WHERE CUR.EMPLID = SS.EMPLID
   AND CUR.ADDRESS_TYPE = 'HOME'
   AND (UPPER(SS.ADDRESS1) <> UPPER(CUR.ADDRESS1)
    OR UPPER(SS.ADDRESS2) <> UPPER(CUR.ADDRESS2)
    OR UPPER(SS.CITY) <> UPPER(CUR.CITY)
    OR UPPER(SS.STATE) <> UPPER(CUR.STATE)
    OR SS.POSTAL <> CUR.POSTAL)


 This simplifies the SQL in my opinion.

Using CTEs to simplyfy SQL

Common Table Expressions, CTE for short, is a powerful tool that I use in views, debugging, testing and simplyfying SQL.

CTE Structure

With CTE (EMPLID, DEPARTMENT) AS ( SELECT EMPLID, DEPTID FROM PS_CURRENT_JOB WHERE JOBCODE = 'SUPER')
SELECT * FROM CTE


The CTE above would return everyone from the current job record where the JOBCODE is 'SUPER'.  This not very useful example, but it does show the syntax.

So how can CTE's simply sql?  For instance, if you were writing a process that needs to derive an value to be used in the selection and in the criteria, if you did not use a CTE then your SQL could look like this below:

select 
A.Emplid,
A.benfit_plan,
M.MAX_ANNUAL_CONTRIB,
A.ANNUAL_PLEDGE
CASE A.CNTRL_BENEFIT_PLAN WHEN ' ' THEN 0 ELSE CASE A.ZZ_HSA_YEARS WHEN 1 THEN DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB1 , I.Zz_Hsa_Fam1)/2 ELSE DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB2 , I.Zz_Hsa_Fam2/2) END END as UMB_CONTRIB
FROM PS_ZZ_UMB_TBL A., PS_ZZ_HSA_LIMITS M, PS_ZZ_INSTALLATION I
WHERE A.EMPLID = M.EMPLID AND A.EMPL_RCD = M.EMPL_RCD
 AND M.MAX_ANNUAL_CONTRIB < ( CASE A.CNTRL_BENEFIT_PLAN WHEN ' ' THEN 0 ELSE CASE A.ZZ_HSA_YEARS WHEN 1 THEN DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB1 , I.Zz_Hsa_Fam1)/2 ELSE DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB2 , I.Zz_Hsa_Fam2/2) END END + A.ANNUAL_PLEDGE)


The problem with this sql is that you have two copies of the same case statement.   Here is how you can use a CTE to only manage one case statement.



With CTE (EMPLID, BENEFIT_PLAN, MAX_ANNUAL_CONTRIB, ANNUAL_PLEDGE UMB_CONTRIB) AS ( select 
A.Emplid,
A.benfit_plan,
M.MAX_ANNUAL_CONTRIB,
A.ANNUAL_PLEDGE
CASE A.CNTRL_BENEFIT_PLAN WHEN ' ' THEN 0 ELSE CASE A.ZZ_HSA_YEARS WHEN 1 THEN DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB1 , I.Zz_Hsa_Fam1)/2 ELSE DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB2 , I.Zz_Hsa_Fam2/2) END END as UMB_CONTRIB
FROM PS_ZZ_UMB_TBL A., PS_ZZ_HSA_LIMITS M, PS_ZZ_INSTALLATION I
WHERE A.EMPLID = M.EMPLID AND A.EMPL_RCD = M.EMPL_RCD)
SELECT EMPLID, BENEFIT_PLAN, UMB_CONTRIB FROM CTE WHERE  MAX_ANNUAL_CONTRIB < ( ANNUAL_PLEDGE + UMB_CONTRIB)