SAD_BPV_INDEX_IDSAD_BPV_MSGUUIDSEQNUME=Exists in CS
I=Imported
N=New
S=Skipped
U=Updated
SAD_BPV_RSLT_VW(SQL View) |
Index Back |
|---|---|
SBB Identification Srch Result |
| SELECT DISTINCT SAD_BPV_INDEX_ID , SAD_BPV_MSGUUID , SEQNUM , EXTERNAL_SYSTEM_ID , ( SELECT DESCR50 FROM PS_SAD_BPV_EXNM_VW EXNM WHERE EXNM.EXTERNAL_SYSTEM_ID = RSP1.EXTERNAL_SYSTEM_ID) , DESCR100 , (CASE WHEN RSP1.EXT_ORG_ID = ' ' THEN ( SELECT EXT_ORG_ID FROM PS_SAD_BPV_EXNM_VW EXNM WHERE EXNM.EXTERNAL_SYSTEM_ID = RSP1.EXTERNAL_SYSTEM_ID) ELSE RSP1.EXT_ORG_ID END) , (CASE WHEN RSP1.SAD_SBB_STATUS = ' ' THEN (CASE WHEN (( SELECT COUNT(1) FROM PS_SAD_BPV_EXNM_VW EXTKEY WHERE EXTKEY.EXTERNAL_SYSTEM_ID = RSP1.EXTERNAL_SYSTEM_ID) = 0) THEN 'N' ELSE 'E' END) ELSE RSP1.SAD_SBB_STATUS END), ( SELECT OPRID FROM PS_SAD_BPV_SBB_NLD WHERE SAD_BPV_INDEX_ID = RSP1.SAD_BPV_INDEX_ID) FROM PS_SAD_BPV_SBB_RSP RSP1 WHERE (RSP1.SAD_BPV_INDEX_ID) IN ( SELECT RSP2.SAD_BPV_INDEX_ID FROM PS_SAD_BPV_SBB_NLD RSP2 WHERE RSP2.SAD_BPV_TRANS_STAT = 'P' AND RSP2.SAD_BPV_TRANS_DTTM = ( SELECT MAX(RSP3.SAD_BPV_TRANS_DTTM) FROM PS_SAD_BPV_SBB_NLD RSP3 WHERE RSP3.SAD_BPV_TRANS_DTTM <= %CurrentDateTimeIn)) UNION SELECT DISTINCT ' ' ,' ' , 999 , A.EXTERNAL_SYSTEM_ID , B.DESCR50 , ( SELECT DISTINCT RSP4.DESCR100 FROM PS_SAD_BPV_SBB_RSP RSP4 WHERE RSP4.SAD_BPV_INDEX_ID = C.SAD_BPV_INDEX_ID AND RSP4.EXTERNAL_SYSTEM_ID = A.EXTERNAL_SYSTEM_ID) , B.EXT_ORG_ID ,'E' , C.OPRID 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 B.EFF_STATUS= 'A' AND A.EXTERNAL_SYSTEM = ( SELECT SCC_EXTERNAL_SYS FROM PS_SAD_BPVINST_NLD) 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 ((B.OTH_NAME_SORT_SRCH LIKE ('%' %Concat REPLACE(UPPER(C.DESCR50),' ','') %Concat '%')) AND (B.EXT_ORG_ID IN ( SELECT DISTINCT F.EXT_ORG_ID FROM PS_SAD_BPV_OLD_NLD F WHERE F.ADDRESS_TYPE = ( SELECT ADDRESS_TYPE FROM PS_SAD_BPVINST_NLD ) AND (F.NUM1 LIKE ('%' %Concat C.NUM1 %Concat '%' )) AND C.NUM1 > ' ' AND (REPLACE(F.POSTAL ,' ' ,'') LIKE ('%' %Concat REPLACE(C.SAD_BR_POSTAL_NLD ,' ' ,'') %Concat '%')))) OR (A.EXTERNAL_SYSTEM_ID LIKE ('%' %Concat UPPER(C.EXTERNAL_SYSTEM_ID) %Concat '%' ))) 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 C1.SAD_BPV_TRANS_DTTM <= %CurrentDateTimeIn) AND A.EXTERNAL_SYSTEM_ID NOT IN ( SELECT DISTINCT EXTERNAL_SYSTEM_ID FROM PS_SAD_BPV_SBB_RSP RSP1 WHERE (RSP1.SAD_BPV_INDEX_ID) IN ( SELECT RSP2.SAD_BPV_INDEX_ID FROM PS_SAD_BPV_SBB_NLD RSP2 WHERE RSP2.SAD_BPV_TRANS_STAT = 'P' AND RSP2.SAD_BPV_TRANS_DTTM = ( SELECT MAX(RSP3.SAD_BPV_TRANS_DTTM) FROM PS_SAD_BPV_SBB_NLD RSP3 WHERE RSP3.SAD_BPV_TRANS_DTTM <= %CurrentDateTimeIn))) |
| # | 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 | DESCR100_2 | Character(100) | VARCHAR2(100) NOT NULL | Description |
| 7 | EXT_ORG_ID | Character(11) | VARCHAR2(11) NOT NULL | External Org ID |
| 8 | 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 |
| 9 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |