Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 'Document' (RDOC)

Yefer Bastidas 1 Reputation point
2022-02-16T02:07:29.47+00:00

how to identify the field that brings more than one value? when I run the store procedure I don't see the error

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,800 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,557 questions

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.7K Reputation points MVP
    2022-02-17T22:30:13.773+00:00

    I think that beast needs a serious overhaul. As Viorel points out there are a number of subqueries that are likely to be able return more than one row. For instance: 

    , (select Rate from PCH5 where AbsEntry = T.DocEntry  and BaseType ='N'  AND WTAmnt > 0  ) AS '% RET FUENTE'

    Is there a business rule which says that there can only be one row with BaseType = N and WTAmnt > 0 for a single AbsEntry? I don't know anything about these tables, but it seems unlikely to me.

    If you want to know which subquery that is misbehaving this time, you can go in and add TOP 1 to each of these subqueries, until you don't get the error. The last you added TOP 1 to was the last sub-Select that returned duplicates.

    But note that TOP 1 is not a fix for the query, because that only means that the query will return some arbitrary result which makes little sense. Rather you need to work with those subqueries and get an understanding of what they are supposed to return.

    1 person found this answer helpful.
  2. Olaf Helper 41,001 Reputation points
    2022-02-16T07:32:02.44+00:00

    how to identify the field that brings more than one value?
    Subquery returned more than 1 value

    Not a "field" returns more the one value, it's a subquery, as the error message already & clearly say.
    Without knowing your SQL code/database design, no one can say.

    Error message normally return the line number of the SQL code, where the error occurs, so check that one.

    0 comments
  3. Tom Phillips 17,716 Reputation points
    2022-02-16T12:46:45.823+00:00

    Something in your Proc is using a sub-query to return a value and getting multiple. Without the source to your proc it is impossible to guess.

    But somewhere in your proc you have code like:
    (SELECT x from y where z=xxx)
    This is returning >1 row under some circumstances

    0 comments
  4. Erland Sommarskog 101.7K Reputation points MVP
    2022-02-16T23:12:40.813+00:00

    There is no reason to look for a "field". This error informs you that a query is incorrectly written. A scalar subquery should always return at most one row, and often this is constrained by querying on the primary key.

    So look at the procedure name and line number to find the bad query. This should be in the error message.

    Not that the procedure may in fact be a triggerr.

    0 comments
  5. Yefer Bastidas 1 Reputation point
    2022-02-17T00:54:15.423+00:00

    This is my SQL
    /****** Object: StoredProcedure [dbo].[SYP_SP_SRI_ATS_REPORTE_COMPRAS] Script Date: 2/16/2022 7:52:08 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[SYP_SP_SRI_ATS_REPORTE_COMPRAS]
    @FECHA_INI DATETIME,
    @FECHA_FIN DATETIME
    AS
    BEGIN

    SELECT T.DocEntry, T7.SeriesName, T.DocNum, T.NumAtCard, T1.LineNum
    , T1.U_SYP_CODIDTRD AS 'SUST. TRIB'
    , T6.U_SYP_CODCOM AS 'TIP. IDENTF'
    , T5.LicTradNum AS 'IDENTIFICACION'
    , T.CardName AS 'RAZON SOCIAL '
    , T5.U_SYP_TCONTRIB AS 'TIP CONTRIB'
    , T5.U_SYP_PARTREL AS'PART. RELC'
    , T5.U_SYP_TIPPROV as 'TIP SUJETO'
    , T.U_SYP_MDTD AS 'TIP. DOC'
    , U_SYP_SERIESUC AS 'ESTB. DOC'
    , U_SYP_MDSD AS 'PTO. EMI. DOC'
    , RIGHT(REPLICATE('0', 9) + CAST(U_SYP_MDCD AS VARCHAR(9)), 9) AS 'SEC. DOC'
    , CASE WHEN ISNULL((RTRIM(LTRIM("U_SYP_NROAUTO"))),'') = '' THEN 'SINAUTORIZACION' ELSE RTRIM(LTRIM("U_SYP_NROAUTO")) END AS 'AUTORIZACION'
    , CAST(T.TaxDate AS DATE ) 'FCH EMIS.'
    , CAST(T.DocDate AS DATE )'FCH REGIS.'
    , CAST(ISNULL
    ((SELECT TOP 1 U_SYP_FECHAS FROM [@SYP_CCODAUT] C1 WHERE C1.U_SYP_NROID = T.CardCode AND C1.U_SYP_TIPDOC = T.U_SYP_MDTD AND C1.U_SYP_SERIESUC = T.U_SYP_SERIESUC
    AND C1.U_SYP_SERIEVTA = T.U_SYP_MDSD AND T.TaxDate >= C1.U_SYP_FECINI AND T.U_SYP_NROAUTO = C1.U_SYP_NROAUT ),T.TaxDate) AS DATE ) 'FCH VENCI'
    , 'BASE %0' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImponible%' THEN T2.BaseSum
    ELSE 0 END
    , 'BASE GRAV 12%' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImpGrav12%' THEN T2.BaseSum
    ELSE 0 END
    , 'BASE GRAV 14%' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImpGrav14%' THEN T2.BaseSum
    ELSE 0 END
    , 'BASE NO OBJETO' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseNoGraIva%' THEN T2.BaseSum
    ELSE 0 END
    , 'BASE EXCENTA IVA' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImpExe%' THEN T2.BaseSum
    ELSE 0 END
    , 'MONTO IVA 12%' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImpGrav12%' THEN T2.TaxSum
    ELSE 0 END
    , 'MONTO IVA 14%' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImpGrav14%' THEN T2.TaxSum
    ELSE 0 END

       ,CAST(CASE WHEN (SELECT SUM(C5.LineTotal) FROM PCH1 C5 WHERE T.DocEntry = C5.DocEntry AND C5.U_SYP_APLICA_ICE = 'SI') > 0 AND T1.U_SYP_APLICA_ICE = 'SI' THEN 
        (T1.LineTotal * ISNULL(T.U_SYP_ICE,0) )/ (SELECT SUM(C5.LineTotal) FROM PCH1 C5 WHERE T.DocEntry = C5.DocEntry AND C5.U_SYP_APLICA_ICE = 'SI')
     ELSE  0 END AS DECIMAL(24,6)) AS 'MONTO ICE'
   , ISNULL((SELECT B1.U_SYP_CODSRI FROM PCH5 C5 LEFT  JOIN OWHT B1 ON B1.WTCode = C5.WTCode WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = C5.WTCode AND C5.BaseType = 'V' ),'') AS 'COD RET IVA'
   , CAST(T2.TaxSum  * ISNULL((SELECT C5.RATE FROM PCH5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 10),0.00)/100 AS DECIMAL(24,6)) AS 'RET 10%'
   , CAST(T2.TaxSum  * ISNULL((SELECT C5.RATE FROM PCH5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 20),0.00)/100 AS DECIMAL(24,6)) AS 'RET 20%'
   , CAST(T2.TaxSum  * ISNULL((SELECT C5.RATE FROM PCH5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 30),0.00)/100 AS DECIMAL(24,6)) AS 'RET 30%'
   , CAST(T2.TaxSum  * ISNULL((SELECT C5.RATE FROM PCH5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 50),0.00)/100 AS DECIMAL(24,6)) AS 'RET 50%'
   , CAST(T2.TaxSum  * ISNULL((SELECT C5.RATE FROM PCH5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 70),0.00)/100 AS DECIMAL(24,6)) AS 'RET 70%'
   , CAST(T2.TaxSum  * ISNULL((SELECT C5.RATE FROM PCH5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 100),0.00)/100 AS DECIMAL(24,6)) AS 'RET 100%'
    ,CASE WHEN ( SELECT DISTINCT COUNT (C5.RATE) FROM PCH5 C5 LEFT JOIN OWHT B5 ON B5.WTCode = C5.WTCode WHERE T.DocEntry = C5.AbsEntry  AND C5.BaseType = 'N' AND WTAmnt > 0  ) = 2 THEN CAST(T10.TaxbleAmnt AS DECIMAL(24,6))
     ELSE CAST(T2.BaseSum AS DECIMAL(24,6)) END 'BASE IMP'
    --,CAST(T10.TaxbleAmnt AS DECIMAL(24,6)) AS 'BASE IMP'
     --select * from pch5 where absentry=18736
   , (select RE.U_SYP_CODSRI from PCH5 S INNER JOIN OWHT RE ON RE.WTCode = S.WTCode where S.AbsEntry = T.DocEntry and S.BaseType ='N' AND S.WTAmnt > 0 ) AS 'COD RET FUENTE'
   , (select Rate from PCH5 where AbsEntry = T.DocEntry  and BaseType ='N'  AND WTAmnt > 0  ) AS '% RET FUENTE'
   , CAST(T2.BaseSum * ISNULL((select Rate from pch5 where AbsEntry = T.DocEntry  and BaseType ='N'  AND WTAmnt > 0  ),0.00)/100 AS DECIMAL(24,6)) AS 'VALOR RETENIDO'
   , T.U_SYP_TipoPago 'TIPO DE PAGO'
   , CASE WHEN T.U_SYP_TipoPago = '01' THEN 'NA' ELSE T.U_SYP_PAISP END  'PAIS DE PAGO'
   , CASE WHEN T.U_SYP_TipoPago = '01' THEN 'NA' ELSE '' END  'PARAISO FISCAL'
   , CASE WHEN T.U_SYP_TipoPago = '01' THEN 'NA' ELSE ISNULL(T.U_SYP_ADTPAGO,'NO') END 'ADOBLE TRIB EN PAGO'
   , CASE WHEN T.U_SYP_TipoPago = '01' THEN 'NA' ELSE ISNULL(T.U_SYP_PESRET,'NO') END 'SUJE. RET'
   , T7.Remark 'TIPO DE PROVISION'
   , CASE WHEN T1.BASETYPE = '20' THEN 'Entr. Mercancias' ELSE CASE WHEN T1.BASETYPE = '22' THEN 'Pedido ' ELSE '' END END  AS 'ORIGEN'
   , CASE WHEN T1.ObjType = '18' THEN 'Fact. Proveedores' ELSE '' END  AS 'DOCUMENTO SAP'
   , T.DocNum 'NRO. SAP'
   , CASE WHEN T.U_SYP_MDTD = '41' OR U_SYP_TPDOCCERT != '07' THEN NULL ELSE CAST(ISNULL(T.DOCDATE,'') AS DATETIME) END 'FCH. RET'
   , CASE WHEN T.U_SYP_MDTD = '41' OR U_SYP_TPDOCCERT != '07' THEN NULL ELSE U_SYP_SUCCERT END AS 'ESTB. RET.'
   , CASE WHEN T.U_SYP_MDTD = '41' OR U_SYP_TPDOCCERT != '07' THEN NULL ELSE U_SYP_SERTRET END AS 'PTO. EMIS. RET'
   , CASE WHEN T.U_SYP_MDTD = '41' OR U_SYP_TPDOCCERT != '07' THEN NULL ELSE RIGHT(REPLICATE('0', 9) + CAST(U_SYP_CORCERT AS VARCHAR(9)), 9) END AS 'SEC. RET'
   , CASE WHEN T.U_SYP_MDTD = '41' OR U_SYP_TPDOCCERT != '07' THEN NULL ELSE (CASE WHEN ISNULL((RTRIM(LTRIM(T.U_SYP_NROAUTOC))),'') ='' THEN 'SINAUTORIZACION' ELSE RTRIM(LTRIM(T.U_SYP_NROAUTOC)) END)  END AS 'AUT. RET.'
   , (SELECT U_NAME FROM OUSR WHERE USERID = T.UserSign) AS 'USUARIO' 
   , CASE WHEN T5.U_SYP_TCONTRIB = '08' THEN 'SI' ELSE 'NO' END  AS 'RISE'
   , ISNULL(T.U_SYP_FORMAP,'') 'FORMA PAGO 1' 
   , ISNULL(T.U_SYP_FORMAPAGO2,'') 'FORMA PAGO 2'
   , CASE WHEN  T1.BASETYPE = '20' THEN T8.AcctCode ELSE T1.AcctCode END AS 'CTA CONTABLE'
   , 'A' 'TIPO OPERAC'
   , ISNULL(U_SYP_COMPS_IVA,'') 'COMPENS. IVA'
   , CASE WHEN T.U_SYP_MDTD = '05'  THEN T.U_SYP_MDTO ELSE  NULL END    'DOC MODIFICADO'
   , CASE WHEN T.U_SYP_MDTD = '05'  THEN T.U_SYP_SERIESUCO  ELSE  NULL END  'ESTB MODIFICADO'
   , CASE WHEN T.U_SYP_MDTD = '05'  THEN T.U_SYP_MDSO ELSE  NULL END    'PTO MODIFICADO'
   , CASE WHEN T.U_SYP_MDTD = '05'  THEN RIGHT(REPLICATE('0', 9) + CAST(T.U_SYP_MDCO AS VARCHAR(9)), 9) ELSE    NULL END AS 'SEC MODIFICADO'
   , CASE WHEN T.U_SYP_MDTD = '05'  THEN T.U_SYP_NROAUTOO ELSE NULL END 'AUTO MODIFICADO'
   , T.U_SYP_FACREEMBOLSO 'COD SAP REEMB'
   , T.U_SYP_TIPOREGI 'TIPO REG' 
   , CASE WHEN T.U_SYP_TIPOREGI = '01' THEN T.U_SYP_PAISPAGOGEN ELSE NULL END 'PAIS PAG GEN'
   , CASE WHEN T.U_SYP_TIPOREGI IN ('02','03') THEN ISNULL(T.U_SYP_PAISPAG_PARFIS,'000') ELSE NULL END 'PAIS PAG PAR FIS'
   , T.U_SYP_DENOPAGO 'DENO PAGO'
   , '' 'FECHA PAGO DIV'
   , NULL 'IMP RENTA SOC'
   , NULL 'ANO UTIL DIV'
   , NULL 'NUM CAJAS BANANO'
   , NULL 'PRECIO CAJAS BANANO'
   , CASE WHEN (SELECT DISTINCT COUNT(B1.U_SYP_CODSRI) FROM PCH5 C5 LEFT  JOIN OWHT B1 ON B1.WTCode = C5.WTCode WHERE T.DocEntry = C5.AbsEntry AND C5.BaseType = 'N'  )>0 THEN 'SI' ELSE 'NO' END AS 'APLICA RET FTE'
   From   OPCH T
        Inner JOIN PCH1 T1 ON T.DocEntry = T1.DocEntry
        INNER JOIN PCH4 T2 ON T.DocEntry = T2.DocEntry And T1.LineNum = T2.LineNum
        LEFT JOIN PCH5 T10 ON T.Docentry=T10.AbsEntry
        and T1.U_SYP_CODIGO_RET=T10.WtCode
         AND T10.BaseType IN ('N')
        --select * from pch4 where docentry=18736
        --select * from pch5 where absentry=18736
        LEFT  JOIN [@SYP_IVA_FE_ATS] T4 ON T4.Code = T2.StaCode
        --select * from [@SYP_IVA_FE_ATS]
        INNER JOIN OCRD T5 ON T.CardCode = T5.CardCode
              LEFT JOIN [@SYP_TIPIDENT] T6 ON T5.U_SYP_BPTD = T6.Code
        INNER JOIN NNM1 T7 ON T.Series = T7.Series
        LEFT JOIN PDN1 T8 ON T8.DocEntry = T1.BaseEntry AND T8.LineNum = T1.BaseLine AND T8.ObjType = T1.BaseType
        INNER JOIN [@SYP_TPODOC] T9 ON T9.CODE = T.U_SYP_MDTD 
                                    AND ISNULL(T9.U_SYP_DIN,'N') = 'N' 
                                    AND ISNULL(T9.U_SYP_ESTADO,'N') = 'Y' 
                                    AND ISNULL(T9.U_SYP_REGCOM ,'N') = 'Y'
                                    AND ISNULL(T9.U_SYP_REGINT ,'N') = 'N'

    Where T.CANCELED = 'N'
    AND T.U_SYP_STATUS IN ( 'V' )
    -- AND T.DOCENTRY=18736
    -- and t.docentry =20359
    AND T.DocDate BETWEEN @FECHA_INI AND @FECHA_FIN

    UNION ALL

    SELECT T.DocEntry, T7.SeriesName, T.DocNum, T.NumAtCard, T1.LineNum
    , T1.U_SYP_CODIDTRD 'SUST. TRIB'
    , T6.U_SYP_CODCOM AS 'TIP. IDENTF'
    , T5.LicTradNum AS 'IDENTIFICACION'
    , T.CardName AS 'RAZON SOCIAL '
    , T5.U_SYP_TCONTRIB AS 'TIP CONTRIB'
    , T5.U_SYP_PARTREL 'PART. RELC'
    , T5.U_SYP_TIPPROV as 'TIP SUJETO'
    , T.U_SYP_MDTD 'TIP. DOC'
    , U_SYP_SERIESUC AS 'ESTB. DOC'
    , U_SYP_MDSD AS 'PTO. EMI. DOC'
    , RIGHT(REPLICATE('0', 9) + CAST(U_SYP_MDCD AS VARCHAR(9)), 9) AS 'SE. DOC'
    , CASE WHEN ISNULL((RTRIM(LTRIM(U_SYP_NROAUTO))),'') = '' THEN 'SINAUTORIZACION' ELSE RTRIM(LTRIM(U_SYP_NROAUTO)) END AS "AUTORIZACION"
    , CAST(T.TaxDate AS DATE ) 'FCH EMIS.'
    , CAST(T.DocDate AS DATE )'FCH REGIS.'
    , CAST(ISNULL((SELECT TOP 1 U_SYP_FECHAS FROM [@SYP_CCODAUT] C1 WHERE C1.U_SYP_NROID = T.CardCode AND C1.U_SYP_TIPDOC = T.U_SYP_MDTD AND C1.U_SYP_SERIESUC = T.U_SYP_SERIESUC AND C1.U_SYP_SERIEVTA = T.U_SYP_MDSD AND T.TaxDate >= C1.U_SYP_FECINI AND T.U_SYP_NROAUTO = C1.U_SYP_NROAUT ),T.TaxDate) AS DATE ) 'FCH VENCI'
    , 'BASE %0' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImponible%' THEN -1 * T2.BaseSum
    ELSE 0 END
    , 'BASE GRAV 12%' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImpGrav12%' THEN -1 * T2.BaseSum
    ELSE 0 END
    , 'BASE GRAV 14%' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImpGrav14%' THEN -1 * T2.BaseSum
    ELSE 0 END
    , 'BASE NO OBJETO' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseNoGraIva%' THEN -1 * T2.BaseSum
    ELSE 0 END
    , 'BASE EXCENTA IVA' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImpExe%' THEN -1 * T2.BaseSum
    ELSE 0 END
    , 'MONTO IVA 12%' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImpGrav12%' THEN -1 * T2.TAXSUM
    ELSE 0 END
    , 'MONTO IVA 14%' = CASE WHEN T4.U_SYP_COLATS LIKE '%baseImpGrav14%' THEN -1 * T2.TAXSUM
    ELSE 0 END

       , CAST(CASE WHEN (SELECT SUM(C5.LineTotal) FROM RPC1 C5 WHERE T.DocEntry = C5.DocEntry AND C5.U_SYP_APLICA_ICE = 'SI') > 0 AND T1.U_SYP_APLICA_ICE = 'SI' THEN 
     -1 * (T1.LineTotal * ISNULL(T.U_SYP_ICE,0) )/ (SELECT SUM(C5.LineTotal) FROM RPC1 C5 WHERE T.DocEntry = C5.DocEntry AND C5.U_SYP_APLICA_ICE = 'SI')
     ELSE  0 END AS DECIMAL(24,6)) as 'MONTO ICE'
   , 'N/A' 'COD RET IVA'
   , CAST(-1 * T2.TaxSum  * ISNULL((SELECT C5.RATE FROM RPC5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 10),0.00)/100 AS DECIMAL(24,6)) AS 'RET 10%'
   , CAST(-1 * T2.TaxSum  * ISNULL((SELECT C5.RATE FROM RPC5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 20),0.00)/100 AS DECIMAL(24,6)) AS 'RET 20%'
   , CAST(-1 * T2.TaxSum  * ISNULL((SELECT C5.RATE FROM RPC5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 30),0.00)/100 AS DECIMAL(24,6)) AS 'RET 30%'
   , CAST(-1 * T2.TaxSum  * ISNULL((SELECT C5.RATE FROM RPC5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 50),0.00)/100 AS DECIMAL(24,6)) AS 'RET 50%'
   , CAST(-1 * T2.TaxSum  * ISNULL((SELECT C5.RATE FROM RPC5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 70),0.00)/100 AS DECIMAL(24,6)) AS 'RET 70%'
   , CAST(-1 * T2.TaxSum  * ISNULL((SELECT C5.RATE FROM RPC5 C5 WHERE T.DocEntry = C5.AbsEntry AND T1.U_SYP_RET_IVA  = WTCode AND C5.BaseType = 'V' AND C5.RATE = 100),0.00)/100 AS DECIMAL(24,6)) AS 'RET 100%'
   , CAST(-1 * T2.BaseSum AS DECIMAL(24,6)) AS 'BASE IMP'
   ,  NULL AS 'COD RET FUENTE'
   ,  NULL AS '% RET FUENTE'
   ,  CAST( 0  AS DECIMAL(24,6)) AS 'VALOR RETENIDO'
   ,  '01' AS 'TIPO DE PAGO'
   , 'NA' AS 'PAIS DE PAGO'
   , 'NA' AS 'PARAISO FISCAL'
   , 'NA' AS 'ADOBLE TRIB EN PAGO'
   , 'NA' AS 'SUJE. RET'
   , T7.Remark AS 'TIPO DE PROVISION'
   , CASE WHEN T1.BASETYPE = '20' THEN 'Entr. Mercancias' ELSE CASE WHEN T1.BASETYPE = '22' THEN 'Pedido ' ELSE '' END END  AS 'ORIGEN'
   , CASE WHEN T1.ObjType = '18' THEN 'Fact. Proveedores' ELSE '' END  AS 'DOCUMENTO SAP'
   , T.DocNum AS 'NRO. SAP'
   , '' 'FCH. RET'
   , NULL AS 'ESTB. RET.'
   , NULL AS 'PTO. EMIS. RET'
   , NULL AS 'SEC. RET'
   , NULL AS 'AUT. RET.'
   , (SELECT U_NAME FROM OUSR WHERE USERID = T.UserSign) AS 'USUARIO' 
   , CASE WHEN T5.U_SYP_TCONTRIB = '08' THEN 'SI' ELSE 'NO' END  AS 'RISE'
   , ISNULL(T.U_SYP_FORMAP,'') AS 'FORMA PAGO 1' 
   , ISNULL(T.U_SYP_FORMAPAGO2,'') AS 'FORMA PAGO 2'
   , CASE WHEN  T1.BASETYPE = '20' THEN T8.AcctCode ELSE T1.AcctCode END AS 'CTA CONTABLE'
   , 'S' 'TIPO OPERAC'
   , ISNULL(U_SYP_COMPS_IVA,'') 'COMPENS. IVA'
   , CASE WHEN T.U_SYP_MDTD = '04'  THEN T.U_SYP_MDTO ELSE  NULL END    'DOC MODIFICADO'
   , CASE WHEN T.U_SYP_MDTD = '04'  THEN T.U_SYP_SERIESUCO  ELSE  NULL END  'ESTB MODIFICADO'
   , CASE WHEN T.U_SYP_MDTD = '04'  THEN T.U_SYP_MDSO ELSE  NULL END    'PTO MODIFICADO'
   , CASE WHEN T.U_SYP_MDTD = '04'  THEN RIGHT(REPLICATE('0', 9) + CAST(T.U_SYP_MDCO AS VARCHAR(9)), 9) ELSE    NULL END AS 'SEC MODIFICADO'
   , CASE WHEN T.U_SYP_MDTD = '04'  THEN T.U_SYP_NROAUTOO ELSE NULL END 'AUTO MODIFICADO'
   , NULL 'COD SAP REEMB'
   , T.U_SYP_TIPOREGI AS 'TIPO REG' 
   , NULL AS 'PAIS PAG GEN'
   , NULL AS 'PAIS PAG PAR FIS'
   , T.U_SYP_DENOPAGO 'DENO PAGO'
   , '' 'FECHA PAGO DIV'
   , NULL 'IMP RENTA SOC'
   , NULL 'ANO UTIL DIV'
   , NULL 'NUM CAJAS BANANO'
   , NULL 'PRECIO CAJAS BANANO'
   , 'NO' AS 'APLICA RET FTE'
   From   ORPC T
        INNER JOIN RPC1 T1 ON T.DocEntry = T1.DocEntry
        INNER JOIN RPC4 T2 ON T.DocEntry = T2.DocEntry And T1.LineNum = T2.LineNum
        LEFT  JOIN [@SYP_IVA_FE_ATS] T4 ON T4.Code = T2.StaCode
        INNER JOIN OCRD T5 ON T.CardCode = T5.CardCode
              LEFT JOIN [@SYP_TIPIDENT] T6 ON T5.U_SYP_BPTD = T6.Code
        INNER JOIN NNM1 T7 ON T.Series = T7.Series
        LEFT JOIN PDN1 T8 ON T8.DocEntry = T1.BaseEntry AND T8.LineNum = T1.BaseLine AND T8.ObjType = T1.BaseType
        INNER JOIN [@SYP_TPODOC] T9 ON T9.CODE = T.U_SYP_MDTD 
                                    AND ISNULL(T9.U_SYP_DIN,'N') = 'N' 
                                    AND ISNULL(T9.U_SYP_ESTADO,'N') = 'Y' 
                                    AND ISNULL(T9.U_SYP_REGCOM ,'N') = 'Y'
                                    AND ISNULL(T9.U_SYP_REGINT ,'N') = 'N'

    Where T.CANCELED = 'N'
    AND T.U_SYP_STATUS IN ( 'V' )
    AND T.DocDate BETWEEN @FECHA_INI AND @FECHA_FIN