Hi everybody,
We have added the new DAC TaxSpecialMentions to manage the notions of legal notices on ARInvoices.The link between the ARInvoice and the records of ths new DAC is done with TaxTaxTran where the new custom field SpectalMentionsID has been added.
Below the data model.
Objectives to be achieved
We have created a new report to display all the texts of the legal mentions for a given ARInvoice.
Below, the query we have created.
Issue:
When the reports runs, no data are found whereas they should be present. I catched the query and I realized that one of the join criteria is not properly built : the one dealing with [SpecialMentionsID] that is used to join ARTaxTran and TaxSpecialMentions.
We should have
( [ARTaxTran_TaxTran].[SpecialMentionsID] = [TaxSpecialMentions].[SpecialMentionsID])
but we have
( NULL = [TaxSpecialMentions].[SpecialMentionsID])
Below the query with more details :
SELECT [ARInvoice_ARRegister].[DocType] AS [DocType], ..., [TaxSpecialMentions].[SpecialMentionsID] AS [SpecialMentionsID]
FROM (
[ARInvoice] [ARInvoice_ARInvoice]
INNER JOIN [ARRegister] [ARInvoice_ARRegister] ON ( [ARInvoice_ARRegister].[CompanyID] = 3) AND [ARInvoice_ARRegister].[DeletedDatabaseRecord] = 0 AND ( [ARInvoice_ARRegister].[BranchID] IS NULL OR ( [ARInvoice_ARRegister].[BranchID] IN ( 5, 2, 3, 4, 6, 7, 8))) AND [ARInvoice_ARInvoice].[DocType] = [ARInvoice_ARRegister].[DocType] AND [ARInvoice_ARInvoice].[RefNbr] = [ARInvoice_ARRegister].[RefNbr]
)
INNER JOIN [TaxTran] [ARTaxTran_TaxTran] ON ( [ARInvoice_ARRegister].[DocType] = [ARTaxTran_TaxTran].[TranType] AND [ARInvoice_ARRegister].[RefNbr] = [ARTaxTran_TaxTran].[RefNbr]) AND ( [ARTaxTran_TaxTran].[CompanyID] = 3) AND ( [ARTaxTran_TaxTran].[Module] = 'AR')
INNER JOIN [TaxSpecialMentions] [TaxSpecialMentions] ON ( [TaxSpecialMentions].[CompanyID] = 3) AND ( NULL = [TaxSpecialMentions].[SpecialMentionsID])
WHERE ( [ARInvoice_ARRegister].[DocType] = 'INV' AND [ARInvoice_ARRegister].[RefNbr] = '003175') AND ( [ARInvoice_ARInvoice].[CompanyID] = 3)
ORDER BY ((SELECT [TaxSpecialMentionsKvExt].[FieldName] as '@type', COALESCE(CONVERT(VARCHAR(30), ValueNumeric), CONVERT(VARCHAR(23), ValueDate, 121), ValueString, ValueText, N'') as '*' FROM [TaxSpecialMentionsKvExt] [TaxSpecialMentionsKvExt] WHERE ( [TaxSpecialMentionsKvExt].[CompanyID] = 3) AND [TaxSpecialMentionsKvExt].[RecordID] = [TaxSpecialMentions].[NoteID] FOR XML PATH('v')) ) OPTION(OPTIMIZE FOR UNKNOWN)/* TX.20.CG.01 */How to fix that ?
