SELECT
DQ.DocumentType AS DocumentType,
P.ID AS DocID,
MAX(P.Firm_ID) AS Firm_ID,
MAX(F.VATIdentNumber) as VATIdentNumber,
MAX(RCD.VATDate$DATE) AS VATDate$DATE,
SUM(A.LocalTAmountWithoutVAT) as LocalAmount,
SUM(A.TAmountWithoutVAT) as Amount,
MAX(DQ.Code || '-' || CAST(P.OrdNumber AS VARCHAR(10)) || '/' || PE.Code) AS DocNumber,
DA.Code as DRCCode,
SUM(A.DRCQuantity) as DRCQuantity,
MAX(DA.Qunit) AS DRCUnit,
MAX(DA.Name) as DRCName,
MAX(DA.ID) as DRC_ID
FROM
ReceivedInvoices2 A
JOIN ReceivedInvoices P ON P.ID=A.Parent_ID
JOIN ReverseChargeDeclarations RCD ON RCD.SDocumentType='04' AND RCD.SDocument_ID=P.ID
JOIN Firms F on F.ID = P.Firm_ID
JOIN Periods PE ON PE.ID=P.Period_ID
JOIN DocQueues DQ ON DQ.ID=P.DocQueue_ID
LEFT JOIN DRCArticles DA ON DA.ID=A.DRCArticle_ID
WHERE
(P.TradeType = 1) and (P.IsReverseChargeDeclared='A')
and (A.VATMode = 1)
and (RCD.VATDate$Date >= {$DATE_FROM} and RCD.VATDate$Date < {$DATE_TO})
and P.VATDocument = 'A'
{ANDWHERE}
GROUP BY DQ.DocumentType, P.ID, DA.Code
union all
SELECT
DQ.DocumentType AS DocumentType,
P.ID AS DocID,
MAX(P.Firm_ID) AS Firm_ID,
MAX(F.VATIdentNumber) as VATIdentNumber,
MAX(RCD.VATDate$DATE) AS VATDate$DATE,
SUM(A.LocalTAmountWithoutVAT) as LocalAmount,
SUM(A.TAmountWithoutVAT) as Amount,
MAX(DQ.Code || '-' || CAST(P.OrdNumber AS VARCHAR(10)) || '/' || PE.Code) AS DocNumber,
DA.Code as DRCCode,
SUM(A.DRCQuantity) as DRCQuantity,
MAX(DA.Qunit) AS DRCUnit,
MAX(DA.Name) as DRCName,
MAX(DA.ID) as DRC_ID
FROM
CashPaid2 A
JOIN CashPaid P ON P.ID=A.Parent_ID
JOIN ReverseChargeDeclarations RCD ON RCD.SDocumentType='06' AND RCD.SDocument_ID=P.ID
JOIN Firms F on F.ID = P.Firm_ID
JOIN Periods PE ON PE.ID=P.Period_ID
JOIN DocQueues DQ ON DQ.ID=P.DocQueue_ID
LEFT JOIN DRCArticles DA ON DA.ID=A.DRCArticle_ID
WHERE
(P.TradeType = 1) and (P.IsReverseChargeDeclared='A')
and (A.VATMode = 1)
and (RCD.VATDate$Date >= {$DATE_FROM} and RCD.VATDate$Date < {$DATE_TO})
and P.VATDocument = 'A'
{ANDWHERE}
GROUP BY DQ.DocumentType, P.ID, DA.Code
union all
SELECT
DQ.DocumentType AS DocumentType,
P.ID AS DocID,
MAX(P.Firm_ID) AS Firm_ID,
MAX(F.VATIdentNumber) as VATIdentNumber,
MAX(RCD.VATDate$DATE) AS VATDate$DATE,
SUM(-1*A.LocalTAmountWithoutVAT) as LocalAmount,
SUM(-1*A.TAmountWithoutVAT) as Amount,
MAX(DQ.Code || '-' || CAST(P.OrdNumber AS VARCHAR(10)) || '/' || PE.Code) AS DocNumber,
DA.Code as DRCCode,
SUM(-1*A.DRCQuantity) as DRCQuantity,
MAX(DA.Qunit) AS DRCUnit,
MAX(DA.Name) as DRCName,
MAX(DA.ID) as DRC_ID
FROM
ReceivedCreditNotes2 A
JOIN ReceivedCreditNotes P ON P.ID=A.Parent_ID
JOIN ReverseChargeDeclarations RCD ON RCD.SDocumentType='61' AND RCD.SDocument_ID=P.ID
JOIN Firms F on F.ID = P.Firm_ID
JOIN Periods PE ON PE.ID=P.Period_ID
JOIN DocQueues DQ ON DQ.ID=P.DocQueue_ID
LEFT JOIN DRCArticles DA ON DA.ID=A.DRCArticle_ID
WHERE
(P.TradeType = 1) and (P.IsReverseChargeDeclared='A')
and (A.VATMode = 1)
and (RCD.VATDate$Date >= {$DATE_FROM} and RCD.VATDate$Date < {$DATE_TO})
and P.VATDocument = 'A'
{ANDWHERE}
GROUP BY DQ.DocumentType, P.ID, DA.Code
union all
SELECT
DQ.DocumentType AS DocumentType,
P.ID AS DocID,
MAX(P.Firm_ID) AS Firm_ID,
MAX(F.VATIdentNumber) as VATIdentNumber,
MAX(RCD.VATDate$DATE) AS VATDate$DATE,
SUM(-1*A.LocalTAmountWithoutVAT) as LocalAmount,
SUM(-1*A.TAmountWithoutVAT) as Amount,
MAX(DQ.Code || '-' || CAST(P.OrdNumber AS VARCHAR(10)) || '/' || PE.Code) AS DocNumber,
DA.Code as DRCCode,
SUM(-1*A.DRCQuantity) as DRCQuantity,
MAX(DA.Qunit) AS DRCUnit,
MAX(DA.Name) as DRCName,
MAX(DA.ID) as DRC_ID
FROM
RefundedCashPaid2 A
JOIN RefundedCashPaid P ON P.ID=A.Parent_ID
JOIN ReverseChargeDeclarations RCD ON RCD.SDocumentType='08' AND RCD.SDocument_ID=P.ID
JOIN Firms F on F.ID = P.Firm_ID
JOIN Periods PE ON PE.ID=P.Period_ID
JOIN DocQueues DQ ON DQ.ID=P.DocQueue_ID
LEFT JOIN DRCArticles DA ON DA.ID=A.DRCArticle_ID
WHERE
(P.TradeType = 1) and (P.IsReverseChargeDeclared='A')
and (A.VATMode = 1)
and (RCD.VATDate$Date >= {$DATE_FROM} and RCD.VATDate$Date < {$DATE_TO})
and P.VATDocument = 'A'
{ANDWHERE}
GROUP BY DQ.DocumentType, P.ID, DA.Code
ORDER BY 4,5,1,2