XML_DLT_LCTN_VW

(SQL View)
 Index Back

Delta Accident Location View

Delta Accident Location View. Retrieving data from Incident Details.

SELECT A.EMPLID , A.ABS_BEGIN_DT , A.DLT_OTHR_COMP , A.DLT_LOCATN_FLG_ESP , CASE WHEN A.DLT_OTHR_COMP ='Y' THEN A.DLT_COMP_NM_ESP ELSE B.COMPANY_DESCR END , CASE WHEN A.DLT_OTHR_COMP ='Y' THEN A.FISCAL_ID_CD3_ESP ELSE B.FISCAL_ID_CD END , CASE WHEN A.DLT_OTHR_COMP ='Y' THEN C.COUNTRY ELSE B.COUNTRY END , CASE WHEN A.DLT_OTHR_COMP ='Y' THEN C.ADDR_FIELD1 %Concat ' ' %Concat C.ADDRESS1 %Concat ' ' %Concat C.ADDR_FIELD2 ELSE B.ADDR_FIELD1 %Concat ' ' %Concat B.ADDRESS1 %Concat ' ' %Concat B.ADDR_FIELD2 END , CASE WHEN A.DLT_OTHR_COMP ='Y' THEN C.CITY ELSE B.CITY END , CASE WHEN A.DLT_OTHR_COMP ='Y' THEN C.STATE ELSE B.STATE END , CASE WHEN A.DLT_OTHR_COMP ='Y' THEN C.POSTAL ELSE B.POSTAL END , CASE WHEN A.DLT_OTHR_COMP ='Y' THEN A.DLT_PHONE_ESP ELSE B.PHONE END , CASE WHEN A.DLT_OTHR_COMP ='Y' THEN A.DLT_NBR_EMPL2_ESP ELSE A.DLT_NBR_EMPL2_ESP END , CASE WHEN A.DLT_OTHR_COMP ='Y' THEN A.DLT_SSN_EMPLER_ESP ELSE A.SSN_EMPLOYER END , CASE WHEN A.DLT_OTHR_COMP ='Y' THEN A.DLT_INDUSTRYCD_ESP ELSE A.INDSTRY_CD_ESP END FROM PS_INJURY_ILL_ESP A , PS_INCIDENT_DATA C , PS_ALL_CCC_ESP_VW B WHERE A.SSN_EMPLOYER = B.SSN_EMPLOYER AND B.EFFDT = ( SELECT MAX (BED.EFFDT) FROM PS_ALL_CCC_ESP_VW BED WHERE BED.SSN_EMPLOYER = B.SSN_EMPLOYER AND BED.EFFDT <= A.ABS_BEGIN_DT) AND A.INCIDENT_NBR = C.INCIDENT_NBR

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EFFDT Date(10) DATE Effective Date

Default Value: %date

3 DLT_OTHR_COMP Character(1) VARCHAR2(1) NOT NULL External Company

Y/N Table Edit

Default Value: N

4 DLT_LOCATN_FLG_ESP Character(1) VARCHAR2(1) NOT NULL In Location Facilities

Y/N Table Edit

Default Value: N

5 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
6 FISCAL_ID_CD Character(10) VARCHAR2(10) NOT NULL Fiscal Identification Code
7 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
8 ADDRESS100 Character(100) VARCHAR2(100) NOT NULL Address 1
9 CITY Character(30) VARCHAR2(30) NOT NULL City
10 STATE Character(6) VARCHAR2(6) NOT NULL State
11 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
12 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
13 DLT_NBR_OF_EMPL Number(6,0) INTEGER NOT NULL Number of Employees
14 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
15 INDSTRY_CD_ESP Character(5) VARCHAR2(5) NOT NULL Industry Activity Code

Prompt Table: INDSTRY_ACT_ESP