SAD_BPV_INDEX_IDSAD_BPV_MSGUUIDSEQNUME=Exists in CS
I=Imported
N=New
S=Skipped
U=Updated
SAD_BPV_SBBR_VW(SQL View) |
Index Back |
|---|---|
BPV SBB ID Response Message |
| SELECT SAD_BPV_INDEX_ID ,SAD_BPV_MSGUUID , SEQNUM , EXTERNAL_SYSTEM_ID , DESCR100 , SAD_BPV_SCORE ,SAD_BPV_STAT_LBD , EXT_ORG_ID , (CASE WHEN RSP1.SAD_SBB_STATUS = ' ' THEN (CASE WHEN (( SELECT COUNT(1) FROM PS_SCC_ORG_EXT_KEY EXTKEY WHERE EXTKEY.EXTERNAL_SYSTEM_ID = RSP1.EXTERNAL_SYSTEM_ID) = 0) THEN 'N' ELSE 'E' END) ELSE RSP1.SAD_SBB_STATUS END) FROM PS_SAD_BPV_SBB_RSP RSP1 UNION SELECT ' ' ,' ' ,999 , A.EXTERNAL_SYSTEM_ID , B.DESCR50 , 100 ,' ' , B.EXT_ORG_ID ,'E' FROM PS_SCC_ORG_EXT_KEY A , PS_EXT_ORG_TBL B ,PS_SAD_BPV_SBB_NLD C WHERE A.EXT_ORG_ID =B.EXT_ORG_ID AND B. EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_EXT_ORG_TBL B1 WHERE B1.EXT_ORG_ID =B.EXT_ORG_ID AND B1.EFFDT <= %CurrentDateIn) AND C.OPRID =%OperatorId AND B.EFF_STATUS= 'A' AND ((B.OTH_NAME_SORT_SRCH = ('%' %Concat %Upper(C.DESCR50)) %Concat '%') AND (B.EXT_ORG_ID = ( SELECT DISTINCT F.EXT_ORG_ID FROM PS_ORG_LOCATION F WHERE F.NUM1 = C.NUM1 AND F.POSTAL= C.SAD_BR_POSTAL_NLD)) OR (A.EXTERNAL_SYSTEM_ID = C.EXTERNAL_SYSTEM_ID)) AND A.EXTERNAL_SYSTEM = 'SBB' AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_SCC_ORG_EXT_KEY A1 WHERE A.EXT_ORG_ID =A1.EXT_ORG_ID AND A.EXTERNAL_SYSTEM = A1.EXTERNAL_SYSTEM AND A1.EFFDT <= %CurrentDateIn) AND C.SAD_BPV_TRANS_DTTM = ( SELECT MAX(C1.SAD_BPV_TRANS_DTTM) FROM PS_SAD_BPV_SBB_NLD C1 WHERE C1.OPRID =C.OPRID AND C.SAD_BPV_INDEX_ID = C1.SAD_BPV_INDEX_ID AND C.SAD_BPV_MSGUUID = C1.SAD_BPV_MSGUUID) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SAD_BPV_INDEX_ID |
Character(40) | VARCHAR2(40) NOT NULL | Transaction Index ID |
| 2 | SAD_BPV_MSGUUID |
Character(50) | VARCHAR2(50) NOT NULL | Message ID |
| 3 | SEQNUM |
Number(3,0) | SMALLINT NOT NULL | Sequence Number |
| 4 | EXTERNAL_SYSTEM_ID | Character(20) | VARCHAR2(20) NOT NULL | External System ID |
| 5 | DESCR100 | Character(100) | VARCHAR2(100) NOT NULL | Length 100 Description |
| 6 | SAD_BPV_SCORE | Number(3,0) | SMALLINT NOT NULL | Score |
| 7 | SAD_BPV_STAT_LBD | Character(30) | VARCHAR2(30) NOT NULL | Historic Status |
| 8 | EXT_ORG_ID | Character(11) | VARCHAR2(11) NOT NULL | External Org ID |
| 9 | SAD_SBB_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
SBB BPV NLD status imported/updated
E=Exists in CS I=Imported N=New S=Skipped U=Updated |