E_JUD_R_BRA_VW

(SQL View)
 Index Back

Range with Jud Gathering

Range with Jud Gathering.

SELECT A.ESTABID ,A.EFFDT AS start_date , B.EFFDT AS end_date , A.FPAS_CD_BRA , A.THIRD_PARTY_CD_BRA ,C.ENT_CODE_BRA ,CASE WHEN C.ENT_CODE_BRA IS NOT NULL THEN 'Y' END FROM %Table(ESTAB_TBL_BRA) B , %Table(ESTAB_TBL_BRA) A LEFT OUTER JOIN %Table(FPAS_JUD_C_BRA) C ON A.ESTABID = C.ESTABID AND A.EFFDT = C.EFFDT AND A.COUNTY_CD_BRA = C.COUNTY_CD_BRA WHERE B.EFFDT = ( SELECT MIN (B1.EFFDT) FROM %Table(ESTAB_TBL_BRA) B1 WHERE B1.EFFDT > A.EFFDT AND A.ESTABID=B1.ESTABID) UNION SELECT A.ESTABID , A.EFFDT AS start_date , %DateIn('2999-12-31') AS end_date , A.FPAS_CD_BRA , A.THIRD_PARTY_CD_BRA ,C.ENT_CODE_BRA ,CASE WHEN C.ENT_CODE_BRA IS NOT NULL THEN 'Y' END FROM %Table(ESTAB_TBL_BRA) B , %Table(ESTAB_TBL_BRA) A LEFT OUTER JOIN %Table(FPAS_JUD_C_BRA) C ON A.ESTABID = C.ESTABID AND A.EFFDT = C.EFFDT AND A.COUNTY_CD_BRA = C.COUNTY_CD_BRA WHERE A.ESTABID = B.ESTABID AND A.EFFDT = ( SELECT MAX (A1.EFFDT) FROM %Table(ESTAB_TBL_BRA) A1 WHERE A.ESTABID = A1.ESTABID) AND A.FPAS_CD_BRA = B.FPAS_CD_BRA AND A.THIRD_PARTY_CD_BRA = B.THIRD_PARTY_CD_BRA

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
2 START_DATE Date(10) DATE Start Date for Gen Standing PO
3 END_DATE Date(10) DATE End Date
4 FPAS_CD_BRA Number(3,0) SMALLINT NOT NULL FPAS Social Assistant & Prevision Code
5 THIRD_PARTY_CD_BRA Number(4,0) SMALLINT NOT NULL Third Party Code
6 ENT_CODE_BRA Character(4) VARCHAR2(4) NOT NULL Entity Code.
7 JUD_COLL_BRA Character(1) VARCHAR2(1) NOT NULL Judicial Gathering.