SAD_BPV_KCAC_SR

(SQL View)
 Index Back

BPC Accreditation SR

FS 15062004

SELECT A.EXT_ORG_ID , C.EXTERNAL_SYSTEM_ID , A.HEGIS_CODE , A.START_DATE , A.END_DATE , A.SAD_INTSHP_APP_NLD , B.DESCR FROM PS_SAD_ACC_ORG_NLD A , PS_HEGIS_CODE_TBL B ,PS_SCC_ORG_EXT_KEY C WHERE A.SAD_INTSHP_APP_NLD IN ('AC','IB' ) AND A.START_DATE <= %CurrentDateIn AND (A.END_DATE IS NULL OR A.END_DATE >= %CurrentDateIn) AND A.HEGIS_CODE = B.HEGIS_CODE AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_HEGIS_CODE_TBL B_ED WHERE B.HEGIS_CODE = B_ED.HEGIS_CODE AND B_ED.EFFDT <= %CurrentDateIn) AND C.EXT_ORG_ID = A.EXT_ORG_ID AND C.EXTERNAL_SYSTEM = ( SELECT SCC_EXTERNAL_SYS FROM PS_SAD_BPVINST_NLD) AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_SCC_ORG_EXT_KEY C1 WHERE C.EXT_ORG_ID =C1.EXT_ORG_ID AND C.EXTERNAL_SYSTEM=C1.EXTERNAL_SYSTEM AND C1.EFFDT <= A.END_DATE)

  • Related Language Record: SADBPVKCACSRLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID

    Prompt Table: EXT_ORG_TBL

    2 EXTERNAL_SYSTEM_ID Character(20) VARCHAR2(20) NOT NULL External System ID
    3 HEGIS_CODE Character(8) VARCHAR2(8) NOT NULL HEGIS Code

    Prompt Table: SAD_BPV_HEG_SRC

    4 START_DATE Date(10) DATE NOT NULL Start Date for Gen Standing PO
    5 END_DATE Date(10) DATE End Date
    6 SAD_INTSHP_APP_NLD Character(2) VARCHAR2(2) NOT NULL Approved by nat. organization
    AC=Accredited
    IC=In consideration
    NA=Not accredited
    NM=Nominated
    RQ=AccreditationRequested
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description