SQL for Savings Bonds Interface

Here is a sample of a savings bond outbound interface.
(This would be your population query within a AE step)

SELECT NVL(A.EMPLID
, ' ')
, L.CHECK_DT
, 'B'
, NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN DED.SSN ELSE (
SELECT NID2.NATIONAL_ID
FROM PS_DEP_BENEF_NID NID2
WHERE NID2.EMPLID = A.EMPLID
AND NID2.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
FROM PS_NAMES NAM
WHERE NAM.EMPLID = DED.EMPLID
AND NAM.EFFDT = (
SELECT MAX(NAM_ED.EFFDT)
FROM PS_NAMES NAM_ED
WHERE NAM.EMPLID = NAM_ED.EMPLID
AND NAM.NAME_TYPE = NAM_ED.NAME_TYPE
AND NAM_ED.EFFDT <= SYSDATE) ) ELSE (
SELECT NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
FROM PS_DEPENDENT_BENEF NAM
WHERE NAM.EMPLID = A.EMPLID
AND NAM.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT (CASE WHEN AD.COUNTRY = 'USA' THEN '0' ELSE '1' END)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT >= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT (CASE WHEN DEPAD.COUNTRY = 'USA' THEN '0' ELSE '1' END)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), '0') , (CASE WHEN B.BOND_DLVY_ID IN ('EE', ' ') THEN '0' ELSE '1' END) ,
NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS1
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS1
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN DED.NAME ELSE (
SELECT NAM.NAME
FROM PS_DEPENDENT_BENEF NAM
WHERE NAM.EMPLID = A.EMPLID
AND NAM.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS2
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS2
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS3
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS3
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.CITY
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.CITY
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.STATE
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.STATE
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT SUBSTR(AD.POSTAL
,1
,5)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT SUBSTR(DEPAD.POSTAL
,1
,5)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT SUBSTR(AD.POSTAL
,7
,10)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT SUBSTR(DEPAD.POSTAL
,7
,10)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , B.BOND_OTH_REG_TYPE, (CASE WHEN B.BOND_OTH_REG_TYPE = 'C' THEN 'OR'
WHEN B.BOND_OTH_REG_TYPE = 'B' THEN 'POD' ELSE ' ' END) , NVL((CASE WHEN B.BOND_OTH_REG_TYPE IN ('C', 'B')
AND B.BOND_OTH_REG_ID IN ('EE') THEN (
SELECT NVL(NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
, ' ')
FROM PS_NAMES NAM
, PS_ASU_DED_INTRFAC DED2
WHERE B.EMPLID = DED2.EMPLID
AND DED2.DEDCD = 'SAVBND'
AND NAM.EMPLID = B.EMPLID
AND NAM.EFFDT = (
SELECT MAX(NAM_ED.EFFDT)
FROM PS_NAMES NAM_ED
WHERE NAM.EMPLID = NAM_ED.EMPLID
AND NAM.NAME_TYPE = NAM_ED.NAME_TYPE
AND NAM_ED.EFFDT <= SYSDATE) ) WHEN B.BOND_OTH_REG_TYPE IN ('C','B')
AND B.BOND_OTH_REG_ID NOT IN ('EE')THEN (
SELECT NVL(D.FIRST_NAME||' '||substr(D.MIDDLE_NAME
, 1
,1)||' '||D.LAST_NAME
, ' ')
FROM PS_DEPENDENT_BENEF D
WHERE D.DEPENDENT_BENEF = B.BOND_OTH_REG_ID
AND B.EMPLID = D.EMPLID) END), ' ') , (CASE WHEN B.BOND_ID = '100' THEN '4' WHEN B.BOND_ID = '200' THEN '5' WHEN B.BOND_ID =
'500' THEN '6' WHEN B.BOND_ID = '1K' THEN '7' WHEN B.BOND_ID = '5K' THEN '8' WHEN B.BOND_ID = '10K' THEN '9' ELSE '0' END) ,
L.BOND_PURCH_UNITS, A.COMPANY, NVL(A.LOCATION, ' ') , NVL(DED.SSN, ' '), DED.PAY_END_DT, DED.PAY_END_DT, ' ', ' ', '9999999999'

FROM PS_ASU_DED_INTRFAC DED, PS_JOB A, PS_BOND_SPEC_DATA B, PS_BOND_LOG L
WHERE A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL_RCD = A.EMPL_RCD
AND A1.EFFDT <= SYSDATE)
AND A.EFFSEQ = (
SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)
AND A.EMPL_STATUS IN ('A','L','P','S')
AND DED.EMPLID = A.EMPLID
AND DED.DEDCD = 'SAVBND' /* Add your dedcd here */
AND DED.DED_CLASS = 'A'
AND A.JOB_INDICATOR = 'P'
AND B.EMPLID = A.EMPLID
AND B.COMPANY = 'Your Company Here' /* Add your company here */
AND B.EFFDT = (
SELECT MAX(B1.EFFDT)
FROM PS_BOND_SPEC_DATA B1
WHERE B1.EMPLID = B.EMPLID
AND B1.COMPANY = B.COMPANY
AND B1.PRIORITY = B.PRIORITY
AND B1.EFFDT <= SYSDATE)
AND B.EMPLID = L.EMPLID
AND B.BOND_ID = L.BOND_ID
AND B.BOND_OWNER_ID = L.BOND_OWNER_ID
AND L.BOND_PURCH_UNITS >= 1
AND L.PAY_END_DT = DED.PAY_END_DT