Description:
Hello,
Sorry for huge tables, but I couldn't reproduce this bag on smaller tables.
We create four tables: ARRegiter, ARTran, Note and PreferencesGeneral and then insert one row into each of them expect ARTran, so ARTran contains no data. But query
SELECT `ARTran`.*
FROM `ARRegister` `ARInvoice_ARRegister`
LEFT JOIN `ARTran` `ARTran` ON `ARTran`.`CompanyID` = 2 AND `ARInvoice_ARRegister`.`RefNbr` = `ARTran`.`RefNbr`
LEFT JOIN `Note` `InvoiceNote` ON `InvoiceNote`.`CompanyID` IN (1, 2) AND 8 = ASCII(SUBSTRING(`InvoiceNote`.`CompanyMask`, 1, 1)) & 8 AND `ARInvoice_ARRegister`.`NoteID` = `InvoiceNote`.`NoteID`
INNER JOIN `PreferencesGeneral` `PreferencesGeneral` ON `PreferencesGeneral`.`CompanyID` IN (1, 2) AND 8 = ASCII(SUBSTRING(`PreferencesGeneral`.`CompanyMask`, 1, 1)) & 8
WHERE `ARInvoice_ARRegister`.`DocType` = 'INV' AND `ARInvoice_ARRegister`.`RefNbr` = 'AR004767' AND `ARInvoice_ARRegister`.`CompanyID` = 2
returns non-null results in ARTran's fields.
If we remove JOIN of Note or PreferencesGeneral table, everything is OK, ARTran's fields are all nulls.
I couldn't find workaround for this problem.
How to repeat:
CREATE DATABASE IF NOT EXISTS `ETest2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ETest2`;
CREATE TABLE `ARRegister` (
`CompanyID` int NOT NULL DEFAULT '0',
`BranchID` int NOT NULL,
`DocType` char(3) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`RefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`BatchNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`BatchSeq` smallint DEFAULT NULL,
`CustomerID` int NOT NULL,
`CustomerLocationID` int NOT NULL,
`ARAccountID` int NOT NULL,
`ARSubID` int NOT NULL,
`PrepaymentAccountID` int DEFAULT NULL,
`PrepaymentSubID` int DEFAULT NULL,
`DocDate` datetime NOT NULL COMMENT 'sy_smalldate;',
`OrigDocDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
`DueDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
`DocDesc` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`CuryInfoID` bigint NOT NULL,
`CuryOrigDocAmt` decimal(19,4) NOT NULL,
`OrigDocAmt` decimal(19,4) NOT NULL,
`CuryDocBal` decimal(19,4) NOT NULL,
`DocBal` decimal(19,4) NOT NULL,
`CuryInitDocBal` decimal(19,4) NOT NULL,
`InitDocBal` decimal(19,4) NOT NULL,
`CuryDiscBal` decimal(19,4) NOT NULL,
`DiscBal` decimal(19,4) NOT NULL,
`CuryDiscTaken` decimal(19,4) NOT NULL,
`DiscTaken` decimal(19,4) NOT NULL,
`CuryChargeAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`ChargeAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`OpenDoc` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
`Released` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
`Hold` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
`DisableAutomaticTaxCalculation` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
`TaxCalcMode` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`IsTaxValid` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`IsTaxSaved` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`IsTaxPosted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`NonTaxable` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`Status` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`LineCntr` int NOT NULL,
`AdjCntr` int NOT NULL,
`DRSchedCntr` int DEFAULT NULL,
`CuryID` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`RGOLAmt` decimal(19,4) NOT NULL,
`CuryRoundDiff` decimal(19,4) NOT NULL,
`RoundDiff` decimal(19,4) NOT NULL,
`ScheduleID` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`ImpRefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`StatementDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
`tstamp` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`CreatedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
`CreatedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`CreatedDateTime` datetime(6) NOT NULL,
`LastModifiedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
`LastModifiedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`LastModifiedDateTime` datetime(6) NOT NULL,
`Voided` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`Scheduled` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`PendingProcessing` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`CuryOrigDiscAmt` decimal(19,4) NOT NULL,
`OrigDiscAmt` decimal(19,4) NOT NULL,
`FinPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`TranPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`ClosedTranPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`ClosedFinPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`ClosedDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
`SalesPersonID` int DEFAULT NULL,
`DeletedDatabaseRecord` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`OrigModule` char(2) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`OrigDocType` char(3) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`OrigRefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`HasPPDTaxes` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`PendingPPD` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`IsMigratedRecord` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`PaymentsByLinesAllowed` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`ApproverID` int DEFAULT NULL,
`ApproverWorkGroupID` int DEFAULT NULL,
`Approved` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
`Rejected` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`DontApprove` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
`RetainageAcctID` int DEFAULT NULL,
`RetainageSubID` int DEFAULT NULL,
`RetainageApply` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`IsRetainageDocument` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`IsRetainageReversing` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`DefRetainagePct` decimal(9,6) NOT NULL DEFAULT '0.000000',
`CuryLineRetainageTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`LineRetainageTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryRetainageTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RetainageTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryRetainageReleased` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RetainageReleased` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryRetainageUnreleasedAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RetainageUnreleasedAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryRetainageUnpaidTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RetainageUnpaidTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryRetainedTaxTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RetainedTaxTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryRetainedDiscTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RetainedDiscTotal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`IsCancellation` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`IsCorrection` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`IsUnderCorrection` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`Canceled` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`PendingPayment` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`NoteID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
`ExternalRef` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`RefNoteID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
PRIMARY KEY (`CompanyID`,`DocType`,`RefNbr`),
UNIQUE KEY `ARRegister_NoteID` (`CompanyID`,`NoteID`),
KEY `ARRegister_CompanyID_CuryID_CuryDocBal` (`CompanyID`,`CuryID`,`CuryDocBal`),
KEY `ARRegister_Correction` (`CompanyID`,`IsCorrection`,`DocType`,`RefNbr`),
KEY `ARRegister_CreatedByID_CreatedDateTime` (`CreatedByID`,`CreatedDateTime`),
KEY `ARRegister_CreatedDateTime` (`CreatedDateTime`),
KEY `ARRegister_CustomerID_OpenDoc_Released_DueDate` (`CompanyID`,`CustomerID`,`Released`,`OpenDoc`,`DueDate`),
KEY `ARRegister_ExternalRefNbr` (`CompanyID`,`ExternalRef`),
KEY `ARRegister_N1` (`DocDate`,`CustomerID`),
KEY `ARRegister_Origin` (`CompanyID`,`BranchID`,`OrigDocType`,`OrigRefNbr`,`Released`,`DeletedDatabaseRecord`),
KEY `ARRegister_OrigRefNbr` (`OrigRefNbr`,`OrigDocType`),
KEY `ARRegister_ScheduleID_Index` (`CompanyID`,`ScheduleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `ARTran` (
`CompanyID` int NOT NULL DEFAULT '0',
`BranchID` int NOT NULL,
`TranType` char(3) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`RefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`LineNbr` int NOT NULL,
`SortOrder` int DEFAULT NULL,
`AccountID` int NOT NULL,
`SubID` int NOT NULL,
`ExpenseAccrualAccountID` int DEFAULT NULL,
`ExpenseAccrualSubID` int DEFAULT NULL,
`ExpenseAccountID` int DEFAULT NULL,
`ExpenseSubID` int DEFAULT NULL,
`AccrueCost` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`CostBasis` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`CuryInfoID` bigint NOT NULL,
`CuryTranAmt` decimal(19,4) DEFAULT NULL,
`TranAmt` decimal(19,4) DEFAULT NULL,
`CuryCashDiscBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CashDiscBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryOrigRetainageAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`OrigRetainageAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryRetainageBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RetainageBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryOrigTranAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`OrigTranAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryTranBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`TranBal` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryTaxableAmt` decimal(19,4) DEFAULT NULL,
`TaxableAmt` decimal(19,4) DEFAULT NULL,
`CuryTaxAmt` decimal(19,4) DEFAULT NULL,
`TaxAmt` decimal(19,4) DEFAULT NULL,
`CuryOrigTaxableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`OrigTaxableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryOrigTaxAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`OrigTaxAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryRetainedTaxableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RetainedTaxableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryRetainedTaxAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RetainedTaxAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`Qty` decimal(25,6) DEFAULT NULL,
`BaseQty` decimal(25,6) DEFAULT NULL,
`CuryAccruedCost` decimal(19,6) NOT NULL DEFAULT '0.000000',
`AccruedCost` decimal(19,6) NOT NULL DEFAULT '0.000000',
`TranCost` decimal(19,4) NOT NULL DEFAULT '0.0000',
`TranCostOrig` decimal(19,4) NOT NULL DEFAULT '0.0000',
`IsTranCostFinal` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`CuryExtPrice` decimal(19,6) DEFAULT NULL,
`ExtPrice` decimal(19,6) DEFAULT NULL,
`CuryLineAmt` decimal(19,6) DEFAULT NULL,
`LineAmt` decimal(19,6) DEFAULT NULL,
`DiscountsAppliedToLine` longblob,
`OrigLineNbr` int DEFAULT NULL,
`OrigGroupDiscountRate` decimal(21,18) NOT NULL,
`OrigDocumentDiscountRate` decimal(21,18) NOT NULL,
`GroupDiscountRate` decimal(21,18) DEFAULT NULL,
`DocumentDiscountRate` decimal(21,18) NOT NULL,
`TranClass` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`DrCr` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`TranDate` datetime NOT NULL COMMENT 'sy_smalldate;',
`OrigInvoiceDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
`TranDesc` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`Released` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
`InventoryID` int DEFAULT NULL,
`SiteID` int DEFAULT NULL,
`ReasonCode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`UOM` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`LineType` char(2) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`TaxCategoryID` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`AvalaraCustomerUsageType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`tstamp` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`CreatedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
`CreatedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`CreatedDateTime` datetime(6) NOT NULL,
`LastModifiedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
`LastModifiedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`LastModifiedDateTime` datetime(6) NOT NULL,
`CuryUnitPrice` decimal(19,6) NOT NULL DEFAULT '0.000000',
`UnitPrice` decimal(19,6) NOT NULL DEFAULT '0.000000',
`SalesPersonID` int DEFAULT NULL,
`EmployeeID` int DEFAULT NULL,
`CommnPct` decimal(9,6) NOT NULL DEFAULT '0.000000',
`CommnAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryCommnAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`Commissionable` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
`FinPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`TranPeriodID` char(6) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`DeferredCode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`ScheduleID` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`DefScheduleID` int DEFAULT NULL,
`CustomerID` int NOT NULL,
`ProjectID` int DEFAULT NULL,
`TaskID` int DEFAULT NULL,
`CostCodeID` int DEFAULT NULL,
`CaseID_obsolete` int DEFAULT NULL,
`CaseCD` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`PMDeltaOption` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`Date` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
`TaxID` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`ExpenseDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
`SOOrderType` char(2) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`SOOrderNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`SOOrderLineNbr` int DEFAULT NULL,
`SOOrderLineOperation` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`SOOrderSortOrder` int DEFAULT NULL,
`SOShipmentType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`SOShipmentNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`SOShipmentLineNbr` int DEFAULT NULL,
`DiscountID` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`DiscountSequenceID` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`CuryDiscAmt` decimal(19,4) DEFAULT NULL,
`DiscPct` decimal(9,6) DEFAULT NULL,
`DiscAmt` decimal(19,4) DEFAULT NULL,
`ManualPrice` tinyint(1) DEFAULT '0' COMMENT 'sy_boolean;',
`AutomaticDiscountsDisabled` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`ManualDisc` tinyint(1) DEFAULT '0' COMMENT 'sy_boolean;',
`DisableAutomaticTaxCalculation` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`SkipLineDiscounts` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`IsRUTROTDeductible` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`RUTROTWorkType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`RUTROTWorkTypeID` int DEFAULT NULL,
`RUTROTItemType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`CuryRUTROTAvailableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`CuryRUTROTTaxAmountDeductible` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RUTROTAvailableAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RUTROTTaxAmountDeductible` decimal(19,4) NOT NULL DEFAULT '0.0000',
`DRTermStartDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
`DRTermEndDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
`CuryUnitPriceDR` decimal(19,6) DEFAULT NULL,
`DiscPctDR` decimal(9,6) DEFAULT NULL,
`RetainagePct` decimal(9,6) NOT NULL DEFAULT '0.000000',
`CuryRetainageAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`RetainageAmt` decimal(19,4) NOT NULL DEFAULT '0.0000',
`OrigDocType` char(3) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`OrigRefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`NoteID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
`SubItemID` int DEFAULT NULL,
`InvtMult` smallint NOT NULL DEFAULT '0',
`LocationID` int DEFAULT NULL,
`LotSerialNbr` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`ExpireDate` datetime DEFAULT NULL COMMENT 'sy_smalldate;',
`PlanID` bigint DEFAULT NULL,
`OrigInvoiceType` char(3) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`OrigInvoiceNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`OrigInvoiceLineNbr` int DEFAULT NULL,
`InvtDocType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`InvtRefNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`IsStockItem` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
`IsCancellation` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`Canceled` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`SubstitutionRequired` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`AssociatedOrderLineNbr` int DEFAULT NULL,
`GiftMessage` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`BlanketType` char(2) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`BlanketNbr` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`BlanketLineNbr` int DEFAULT NULL,
`BlanketSplitLineNbr` int DEFAULT NULL,
`SOOrderLineSign` smallint DEFAULT NULL,
PRIMARY KEY (`CompanyID`,`TranType`,`RefNbr`,`LineNbr`),
UNIQUE KEY `ARTran_NoteID` (`CompanyID`,`NoteID`),
KEY `ARTran_CuryInfoID` (`CuryInfoID`),
KEY `ARTran_OrigInvoiceType_OrigInvoiceNbr` (`CompanyID`,`OrigInvoiceType`,`OrigInvoiceNbr`),
KEY `ARTran_ProjectID` (`CompanyID`,`BranchID`,`ProjectID`,`TaskID`,`AccountID`,`InventoryID`,`CostCodeID`),
KEY `ARTran_RefNbr_LineType` (`CompanyID`,`RefNbr`,`LineType`),
KEY `ARTran_RevenueSchedule` (`CompanyID`,`CustomerID`,`ScheduleID`),
KEY `ARTran_SOLine` (`SOOrderType`,`SOOrderNbr`,`SOOrderLineNbr`),
KEY `ARTran_SortOrder` (`CompanyID`,`TranType`,`RefNbr`,`SortOrder`,`LineNbr`),
KEY `ARTran_SOShipLine` (`SOShipmentNbr`,`SOShipmentType`,`SOShipmentLineNbr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `Note` (
`CompanyID` int NOT NULL DEFAULT '0',
`oldNoteID` bigint DEFAULT NULL,
`ExternalKey` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`NoteText` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`NotePopupText` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
`GraphType` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`EntityType` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`CompanyMask` varbinary(32) NOT NULL DEFAULT 0xAA COMMENT 'df_MaskAA;',
`NoteID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '00112233-4455-6677-8899-AABBCCDDEEFF' COMMENT 'sy_guid;',
PRIMARY KEY (`NoteID`,`CompanyID`),
KEY `ExternalKey_Ind` (`ExternalKey`(191)),
KEY `Note_REVERSED` (`CompanyID`,`NoteID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `PreferencesGeneral` (
`CompanyID` int NOT NULL DEFAULT '0',
`MaxUploadSize` int NOT NULL,
`MapViewer` int DEFAULT NULL,
`TimeZone` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`GridActionsText` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
`GridFastFilterCondition` int NOT NULL,
`GridFastFilterMaxLength` int NOT NULL,
`GetLinkTemplate` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
`HeaderFont` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`HeaderFontSize` smallint DEFAULT NULL,
`HeaderFontColor` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`HeaderFillColor` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`HeaderFontType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`BodyFont` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`BodyFontSize` smallint DEFAULT NULL,
`BodyFontColor` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`BodyFillColor` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`BodyFontType` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`Border` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
`BorderColor` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`HiddenSkip` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
`EditorFontName` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`EditorFontSize` int DEFAULT NULL,
`SpellCheck` tinyint(1) DEFAULT NULL COMMENT 'sy_boolean;',
`Theme` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`HomePage` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
`HelpPage` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
`UseMLSearch` tinyint(1) NOT NULL COMMENT 'sy_boolean;',
`DeletingMLEventsMode` int NOT NULL DEFAULT '0',
`MLEventsRetentionAge` int NOT NULL,
`PortalHomePage` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
`DashboardPortalHomePage` char(36) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'sy_guid;',
`PortalExternalAccessLink` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`PersonNameFormat` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`CreatedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
`CreatedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`CreatedDateTime` datetime(6) NOT NULL,
`LastModifiedByID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
`LastModifiedByScreenID` char(8) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`LastModifiedDateTime` datetime(6) NOT NULL,
`CompanyMask` varbinary(32) NOT NULL DEFAULT 0xAA COMMENT 'df_MaskAA;',
`tstamp` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`NoteID` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 'sy_guid;',
`ApplyToEmptyCells` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sy_boolean;',
`AddressLookupPluginID` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`CompanyID`),
UNIQUE KEY `PreferencesGeneral_NoteID` (`CompanyID`,`NoteID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `ARRegister` VALUES (2,16,'INV','AR004767','AR002930',0,4986,5029,1154,467,NULL,NULL,'2017-01-05 00:00:00',NULL,'2017-02-04 00:00:00','Contract Billing 38SUPPORTP: Support contract for Westwood Manor.',650577,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0,1,0,NULL,'T',0,0,0,0,'C',0,0,NULL,'USD',0.0000,0.0000,0.0000,NULL,NULL,NULL,'2023-08-10 20:13:00.194153','b5344897-037e-4d58-b5c3-1bdfd0f47bf9','CT501000','2017-01-03 14:06:59.747000','b5344897-037e-4d58-b5c3-1bdfd0f47bf9','AR509900','2017-01-03 14:25:24.257000',0,0,0,0.0000,0.0000,'201701','201701','201701','201701','2017-02-01 00:00:00',38,0,'AR',NULL,NULL,0,0,0,0,NULL,NULL,1,0,NULL,NULL,NULL,0,0,0,0.000000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0,0,0,0,0,'92bbb8cc-e7d1-e611-946d-126b3844335e',NULL,NULL);
INSERT INTO `Note` VALUES (2,NULL,NULL,'',NULL,'PX.Objects.AR.ARInvoiceEntry','PX.Objects.AR.ARInvoice',x'AA','92bbb8cc-e7d1-e611-946d-126b3844335e');
INSERT INTO `PreferencesGeneral` VALUES (2,25000,0,'GMTE0000U',1,6,100,NULL,'Calibri',11,'White','Navy','1','Calibri',11,'Black','Transparent','1',0,'Transparent',1,'Arial',2,0,'Default',NULL,'e66a3027-ff57-46a4-bf51-5cdf7c0723ac',1,0,90,NULL,NULL,NULL,'LEGACY','b5344897-037e-4d58-b5c3-1bdfd0f47bf9','CS209000','2008-01-31 00:00:00.000000','b5344897-037e-4d58-b5c3-1bdfd0f47bf9','SM202550','2015-05-18 17:30:23.810000',x'AA','2022-08-13 08:55:56.378301','5a0c3b2c-d87f-e411-beca-00b56d0561c2',0,NULL);
SELECT `ARTran`.*
FROM `ARRegister` `ARInvoice_ARRegister`
LEFT JOIN `ARTran` `ARTran` ON `ARTran`.`CompanyID` = 2 AND `ARInvoice_ARRegister`.`RefNbr` = `ARTran`.`RefNbr`
LEFT JOIN `Note` `InvoiceNote` ON `InvoiceNote`.`CompanyID` IN (1, 2) AND 8 = ASCII(SUBSTRING(`InvoiceNote`.`CompanyMask`, 1, 1)) & 8 AND `ARInvoice_ARRegister`.`NoteID` = `InvoiceNote`.`NoteID`
INNER JOIN `PreferencesGeneral` `PreferencesGeneral` ON `PreferencesGeneral`.`CompanyID` IN (1, 2) AND 8 = ASCII(SUBSTRING(`PreferencesGeneral`.`CompanyMask`, 1, 1)) & 8
WHERE `ARInvoice_ARRegister`.`DocType` = 'INV' AND `ARInvoice_ARRegister`.`RefNbr` = 'AR004767' AND `ARInvoice_ARRegister`.`CompanyID` = 2
