GPFR_DUCS_TR_VW

(SQL View)
 Index Back

TRC Monthly table

Trc Monthly Table

SELECT H.GPFR_DUCS_CD , H.GPFR_DUCS_TYPE , %Substring(E.GPFR_DUCS_R1_ID,1,9) , %Substring(A.GPFR_DUCS_R1_ID,1,20) , A.GPFR_DUCS_R1_NAME1 , %Substring(A.GPFR_DUCS_R2_REF1,1,20) , H.GPFR_DUCS_NUMBER , H.GPFR_ROW_NUM , H.GPFR_ROW_NUM2 , H.GPFR_DUCS_REFPER , H.GPFR_DUCS_R3_AMT , H.GPFR_DUCS_R3_CUR , F.GPFR_DUCS_R3_AMT , F.GPFR_DUCS_R3_CUR FROM PS_GPFR_DUCS_R A , PS_GPFR_DUCS_DEB B , PS_GPFR_DUCS_GEN D , PS_GPFR_DUCS_R E , PS_GPFR_DUCS_R F , PS_GPFR_DUCS_DEB G , PS_GPFR_DUCS_SUM H WHERE A.GPFR_DUCS_NUMBER=B.GPFR_DUCS_NUMBER AND A.GPFR_DUCS_CD=B.GPFR_DUCS_CD AND A.GPFR_DUCS_TYPE=B.GPFR_DUCS_TYPE AND A.GPFR_DUCS_REG_FLG=B.GPFR_DUCS_REG_FLG AND A.GPFR_DUCS_TYPE='901' AND B.GPFR_DUCS_DEB_SLIP IN ('913', '914') AND B.GPFR_DUCS_DEB_TEST <> '1' AND A.GPFR_ROW_NUM=B.GPFR_ROW_NUM AND A.GPFR_DUCS_RECORD='EN1' AND A.GPFR_DUCS_NUMBER=D.GPFR_DUCS_NUMBER AND A.GPFR_DUCS_CD=D.GPFR_DUCS_CD AND A.GPFR_DUCS_TYPE=D.GPFR_DUCS_TYPE AND A.GPFR_DUCS_REG_FLG=D.GPFR_DUCS_REG_FLG AND A.GPFR_ROW_NUM=D.GPFR_ROW_NUM AND E.GPFR_DUCS_NUMBER=D.GPFR_DUCS_NUMBER AND E.GPFR_DUCS_CD=D.GPFR_DUCS_CD AND E.GPFR_DUCS_TYPE=D.GPFR_DUCS_TYPE AND E.GPFR_DUCS_REG_FLG=D.GPFR_DUCS_REG_FLG AND E.GPFR_ROW_NUM=D.GPFR_ROW_NUM AND E.GPFR_DUCS_RECORD='DT1' AND F.GPFR_DUCS_NUMBER=G.GPFR_DUCS_NUMBER AND F.GPFR_DUCS_CD=G.GPFR_DUCS_CD AND F.GPFR_DUCS_TYPE=G.GPFR_DUCS_TYPE AND F.GPFR_DUCS_REG_FLG=G.GPFR_DUCS_REG_FLG AND F.GPFR_DUCS_TYPE='901' AND G.GPFR_DUCS_DEB_SLIP IN ('915', '916') AND F.GPFR_ROW_NUM=G.GPFR_ROW_NUM AND F.GPFR_DUCS_RECORD='EN1' AND F.GPFR_DUCS_R1_ID=A.GPFR_DUCS_R1_ID AND H.GPFR_DUCS_NUMBER=F.GPFR_DUCS_NUMBER AND H.GPFR_DUCS_CD=F.GPFR_DUCS_CD AND H.GPFR_DUCS_TYPE=F.GPFR_DUCS_TYPE AND H.GPFR_DUCS_REG_FLG=F.GPFR_DUCS_REG_FLG AND H.GPFR_ROW_NUM=F.GPFR_ROW_NUM AND H.GPFR_ROW_NUM2=F.GPFR_ROW_NUM2 AND H.GPFR_DUCS_REFPER=D.GPFR_DUCS_GEN_DAT1 AND B.GPFR_ASS_DATETIME IN ( SELECT MAX(MB.GPFR_ASS_DATETIME) FROM PS_GPFR_DUCS_R MA , PS_GPFR_DUCS_DEB MB , PS_GPFR_DUCS_GEN MD WHERE MA.GPFR_DUCS_NUMBER=MB.GPFR_DUCS_NUMBER AND MA.GPFR_DUCS_CD=MB.GPFR_DUCS_CD AND MA.GPFR_DUCS_TYPE=MB.GPFR_DUCS_TYPE AND MA.GPFR_DUCS_REG_FLG=MB.GPFR_DUCS_REG_FLG AND MA.GPFR_DUCS_TYPE='901' AND MB.GPFR_DUCS_DEB_SLIP IN ('913', '914') AND MB.GPFR_DUCS_DEB_TEST <> '1' AND MA.GPFR_ROW_NUM=MB.GPFR_ROW_NUM AND MA.GPFR_DUCS_RECORD='EN1' AND MA.GPFR_DUCS_NUMBER=MD.GPFR_DUCS_NUMBER AND MA.GPFR_DUCS_CD=MD.GPFR_DUCS_CD AND MA.GPFR_DUCS_TYPE=MD.GPFR_DUCS_TYPE AND MA.GPFR_DUCS_REG_FLG=MD.GPFR_DUCS_REG_FLG AND MA.GPFR_ROW_NUM=MD.GPFR_ROW_NUM AND MA.GPFR_DUCS_R1_ID=A.GPFR_DUCS_R1_ID AND MB.END_DT=B.END_DT) AND G.GPFR_ASS_DATETIME IN ( SELECT MAX(MG.GPFR_ASS_DATETIME) FROM PS_GPFR_DUCS_R MF , PS_GPFR_DUCS_DEB MG WHERE MF.GPFR_DUCS_NUMBER=MG.GPFR_DUCS_NUMBER AND MF.GPFR_DUCS_CD=MG.GPFR_DUCS_CD AND MF.GPFR_DUCS_TYPE=MG.GPFR_DUCS_TYPE AND MF.GPFR_DUCS_REG_FLG=MG.GPFR_DUCS_REG_FLG AND MF.GPFR_DUCS_TYPE='901' AND MG.GPFR_DUCS_DEB_SLIP IN ('915', '916') AND MF.GPFR_ROW_NUM=MG.GPFR_ROW_NUM AND MF.GPFR_DUCS_RECORD='EN1' AND MF.GPFR_DUCS_R1_ID=A.GPFR_DUCS_R1_ID AND MG.END_DT=G.END_DT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 GPFR_DUCS_CD Character(10) VARCHAR2(10) NOT NULL GPFR: DUCS Code
2 GPFR_DUCS_TYPE Character(3) VARCHAR2(3) NOT NULL GPFR: DUCS Type
3 SIREN_CD_FRA Character(9) VARCHAR2(9) NOT NULL SIREN Code
4 SIRET_CD Character(20) VARCHAR2(20) NOT NULL SIRET
5 GPFR_DUCS_R1_NAME1 Character(35) VARCHAR2(35) NOT NULL Name for the Declared Unit
6 URSSAF_NM_FRA Character(20) VARCHAR2(20) NOT NULL URSSAF #
7 GPFR_DUCS_NUMBER Number(10,0) DECIMAL(10) NOT NULL DUCS Process Instance
8 GPFR_ROW_NUM Number(5,0) INTEGER NOT NULL Used as counter for row or column number
9 GPFR_ROW_NUM2 Number(5,0) INTEGER NOT NULL Used as counter for row or column number (level2). Used in the DUCS/BDA/TRC reports.
10 GPFR_DUCS_REFPER Character(6) VARCHAR2(6) NOT NULL TRC Reference Period
11 GPFR_DUCS_R3_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Amount 1 for the EN3 DUCS Record. Used in the SUM record.
12 GPFR_DUCS_R3_CUR Character(3) VARCHAR2(3) NOT NULL GPFR: Currency
13 GPFR_DUCS_R3_AMT2 Signed Number(20,6) DECIMAL(18,6) NOT NULL Amount 2 for the EN3 DUCS Record
14 GPFR_DUCS_R3_CUR2 Character(3) VARCHAR2(3) NOT NULL Currency of the declaration