namespace AccountsDAL.Query.PartyBranch { public static class PartyBranchQB { // Display Code/Name joins for every FK on MPARTYBRANCH, confirmed via legacy GB4 NHibernate // mappings (D:\GB4 Service\DAL\{MMDAL,LogisticsDAL,PayrollDAL,AccountsDAL}\HibernateMapFile\ // {PartyPriceCategory,PartyTaxType,Route,PaymentTerm,Employee,TermsSet,Insurance, // MRPPartyType,DistributionList}.hbm.xml) since none of these master tables had an existing // GB5 join to crib column names from. All LEFT JOINs (not INNER, unlike legacy) since we // can't confirm every master table seeds a -1 "not applicable" placeholder row. // MTERMSSET has no code column in the legacy schema — only PurchaseTermsSetName/ // SalesTermsSetName are joined, *TermsSetCode is left unpopulated. // MINSURANCE has no code/name columns at all — only InsurancePolicyNumber is joined. public const string GET_PARTY_BRANCH = @" SELECT PB.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, PB.PARTYBRANCHSHORTNAME AS PartyBranchShortName, PB.PARTYBRANCHTYPE AS PartyBranchType, PB.ISEOU AS PartyBranchIsEOU, PB.ISEXCISEAPPLICABLE AS PartyBranchIsExciseApplicable, PB.ISCUSTOMERPRODUCT AS PartyBranchIsCustomerProduct, PB.ISSALESAPPLICABLE AS PartyBranchIsSalesApplicable, PB.ISPURCHASEAPPLICALBE AS PartyBranchIsPurchaseApplicable, PB.INDUSTRYTYPE AS PartyBranchIndustryType, PB.INSURANCETYPE AS PartyBranchInsuranceType, PB.FREIGHTTYPE AS PartyBranchFreightType, PB.STANDARDREGIONID AS PartyBranchStandardRegionId, PB.SEQUENCENUMBER AS PartyBranchSequenceNumber, PB.PARTYID AS PartyId, ISNULL(P.PARTYCODE, 'NONE') AS PartyCode, ISNULL(P.PARTYNAME, 'NONE') AS PartyName, ISNULL(P.ACCOUNTLINKID, -1) AS PartyBranchPartyAccountLinkId, ISNULL(P.ACCOUNTGROUPID, -1) AS PartyBranchPartyAccountGroupId, ISNULL(P.CONTROLACCOUNTID, -1) AS PartyBranchPartyControlAccountId, ISNULL(P.VERSION, 0) AS PartyBranchPartyPartyVersion, PB.TENANTID AS TenantId, PB.DEFAULTADDRESSID AS DefaultAddressId, ISNULL(AD.ADDRESSLINE1, 'NONE') AS DefaultAddressLine1, ISNULL(AD.ADDRESSLINE2, 'NONE') AS DefaultAddressLine2, ISNULL(AD.ADDRESSLINE3, 'NONE') AS DefaultAddressLine3, ISNULL(AD.MOBILE, 'NONE') AS DefaultAddressMobile, PB.ROUTEID AS RouteId, ISNULL(RT.ROUTECODE, 'NONE') AS RouteRouteCode, ISNULL(RT.ROUTENAME, 'NONE') AS RouteRouteName, PB.TAXTYPEID AS TaxTypeId, ISNULL(TT.PARTYTAXTYPECODE, 'NONE') AS TaxTypeCode, ISNULL(TT.PARTYTAXTYPENAME, 'NONE') AS TaxTypeName, PB.CURRENCYID AS CurrencyId, ISNULL(CUR.CURRENCYCODE, 'NONE') AS CurrencyCode, ISNULL(CUR.CURRENCYNAME, 'NONE') AS CurrencyName, PB.INCHARGEID AS InchargeId, ISNULL(INC.EMPLOYEECODE, 'NONE') AS InchargeCode, ISNULL(INC.EMPLOYEENAME, 'NONE') AS InchargeName, PB.PURCHASEPRICELISTID AS PurchasePriceListId, ISNULL(PPL.PARTYPRICECATEGORYCODE, 'NONE') AS PurchasePriceListCode, ISNULL(PPL.PARTYPRICECATEGORYNAME, 'NONE') AS PurchasePriceListName, PB.SALESPRICELISTID AS SalesPriceListId, ISNULL(SPL.PARTYPRICECATEGORYCODE, 'NONE') AS SalesPriceListCode, ISNULL(SPL.PARTYPRICECATEGORYNAME, 'NONE') AS SalesPriceListName, PB.AGENTID AS AgentId, ISNULL(AGT.PARTYCODE, 'NONE') AS AgentCode, ISNULL(AGT.PARTYNAME, 'NONE') AS AgentName, PB.TRANSPORTERID AS TransporterId, ISNULL(TRN.PARTYCODE, 'NONE') AS TransporterCode, ISNULL(TRN.PARTYNAME, 'NONE') AS TransporterName, PB.SALESPAYMENTTERMID AS SalesPaymentTermId, ISNULL(SPT.PAYMENTTERMCODE, 'NONE') AS SalesPaymentTermCode, ISNULL(SPT.PAYMENTTERMNAME, 'NONE') AS SalesPaymentTermName, PB.PURCHASEPAYMENTTERMID AS PurchasePaymentTermId, ISNULL(PPT.PAYMENTTERMCODE, 'NONE') AS PurchasePaymentTermCode, ISNULL(PPT.PAYMENTTERMNAME, 'NONE') AS PurchasePaymentTermName, PB.SALESINCHARGEID AS SalesInchargeId, ISNULL(SI.EMPLOYEECODE, 'NONE') AS SalesInchargeCode, ISNULL(SI.EMPLOYEENAME, 'NONE') AS SalesInchargeName, PB.PURCHASETERMSSETID AS PurchaseTermsSetId, ISNULL(PTS.TERMSSETNAME, 'NONE') AS PurchaseTermsSetName, PB.SALESTERMSSETID AS SalesTermsSetId, ISNULL(STS.TERMSSETNAME, 'NONE') AS SalesTermsSetName, PB.INSURANCEID AS InsuranceId, ISNULL(INS.POLICYNUMBER, 'NONE') AS InsurancePolicyNumber, PB.MRPPARTYTYPEID AS MRPPartyTypeId, ISNULL(MRP.MRPPARTYTYPECODE, 'NONE') AS MRPPartyTypeCode, ISNULL(MRP.MRPPARTYTYPENAME, 'NONE') AS MRPPartyTypeName, PB.DISTRIBUTIONLISTID AS DistributionListId, ISNULL(DL.DISTRIBUTIONLISTCODE, 'NONE') AS DistributionListCode, ISNULL(DL.DISTRIBUTIONLISTNAME, 'NONE') AS DistributionListName, PB.ROUTEIDS AS PartyBranchRouteIds, -- STRING_SPLIT/STRING_AGG need SQL Server 2017+/compat level 140+ — some environments (e.g. QC) -- run an older compat level where those are flagged as an invalid object name. FOR XML PATH + STUFF is the -- pre-2016-compatible equivalent (comma-padded LIKE avoids partial-number false positives). ( SELECT ISNULL(STUFF(( SELECT ',' + RT2.ROUTENAME FROM MROUTE RT2 WHERE ',' + CAST(PB.ROUTEIDS AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(RT2.ROUTEID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchRouteNames, PB.CREDITDAYS AS PartyBranchCreditDays, PB.NOOFBILLS AS PartyBranchNoOfBills, PB.LINKEDPARTIES AS PartyBranchLinkedParties, ( SELECT ISNULL(STUFF(( SELECT ',' + PB2.PARTYBRANCHNAME FROM MPARTYBRANCH PB2 WHERE ',' + CAST(PB.LINKEDPARTIES AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(PB2.PARTYBRANCHID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchLinkedPartyNames, PB.PROJECTMANAGERIDS AS PartyBranchProjectManagerIds, ( SELECT ISNULL(STUFF(( SELECT ',' + E1.EMPLOYEENAME FROM MEMPLOYEE E1 WHERE ',' + CAST(PB.PROJECTMANAGERIDS AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(E1.EMPLOYEEID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchProjectManagerNames, PB.ACCOUNTMANAGERIDS AS PartyBranchAccountManagerIds, ( SELECT ISNULL(STUFF(( SELECT ',' + E2.EMPLOYEENAME FROM MEMPLOYEE E2 WHERE ',' + CAST(PB.ACCOUNTMANAGERIDS AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(E2.EMPLOYEEID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchAccountManagerNames, PB.MSMEREGNO AS PartyBranchMSMERegNo, PB.REGISTRATION AS PartyBranchRegistration, PB.PARTYINFO AS PartyBranchPartyInfo, ( SELECT ISNULL(STUFF(( SELECT ',' + G.GCMNAME FROM MGCM G WHERE ',' + CAST(PB.PARTYINFO AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(G.GCMID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchPartyInfoName, PB.ISSUBCONTRACT AS PartyBranchIsSubContract, PB.SORTORDER AS PartyBranchSortOrder, PB.STATUS AS PartyBranchStatus, PB.VERSION AS PartyBranchVersion, PB.SOURCETYPE AS PartyBranchSourceType, PB.CREATEDBYID AS PartyBranchCreatedById, PB.CREATEDON AS PartyBranchCreatedOn, PB.MODIFIEDBYID AS PartyBranchModifiedById, PB.MODIFIEDON AS PartyBranchModifiedOn FROM MPARTYBRANCH PB LEFT JOIN MPARTY P ON P.PARTYID = PB.PARTYID LEFT JOIN MADDRESS AD ON AD.ADDRESSID = PB.DEFAULTADDRESSID LEFT JOIN MROUTE RT ON RT.ROUTEID = PB.ROUTEID LEFT JOIN MPARTYTAXTYPE TT ON TT.PARTYTAXTYPEID = PB.TAXTYPEID LEFT JOIN MCURRENCY CUR ON CUR.CURRENCYID = PB.CURRENCYID LEFT JOIN MEMPLOYEE INC ON INC.EMPLOYEEID = PB.INCHARGEID LEFT JOIN MPARTYPRICECATEGORY PPL ON PPL.PARTYPRICECATEGORYID = PB.PURCHASEPRICELISTID LEFT JOIN MPARTYPRICECATEGORY SPL ON SPL.PARTYPRICECATEGORYID = PB.SALESPRICELISTID LEFT JOIN MPARTY AGT ON AGT.PARTYID = PB.AGENTID LEFT JOIN MPARTY TRN ON TRN.PARTYID = PB.TRANSPORTERID LEFT JOIN MPAYMENTTERM SPT ON SPT.PAYMENTTERMID = PB.SALESPAYMENTTERMID LEFT JOIN MPAYMENTTERM PPT ON PPT.PAYMENTTERMID = PB.PURCHASEPAYMENTTERMID LEFT JOIN MEMPLOYEE SI ON SI.EMPLOYEEID = PB.SALESINCHARGEID LEFT JOIN MTERMSSET PTS ON PTS.TERMSSETID = PB.PURCHASETERMSSETID LEFT JOIN MTERMSSET STS ON STS.TERMSSETID = PB.SALESTERMSSETID LEFT JOIN MINSURANCE INS ON INS.INSURANCEID = PB.INSURANCEID LEFT JOIN MMRPPARTYTYPE MRP ON MRP.MRPPARTYTYPEID = PB.MRPPARTYTYPEID LEFT JOIN MDISTRIBUTIONLIST DL ON DL.DISTRIBUTIONLISTID = PB.DISTRIBUTIONLISTID WHERE PB.PARTYBRANCHID = @PartyBranchId;"; // Full-DTO party branch list — same column set/joins as GET_PARTY_BRANCH (returns // PartyBranchDTO, not PartyBranchListDTO — that's an unrelated pre-existing wide report DTO) // but for every matching row instead of one, criteria-filtered like GET_ACCOUNT_LIST rather // than paged — no FirstNumber/MaxResult, fixed ORDER BY only. public const string GET_PARTYBRANCH_LIST = @" SELECT PB.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, PB.PARTYBRANCHSHORTNAME AS PartyBranchShortName, PB.PARTYBRANCHTYPE AS PartyBranchType, PB.ISEOU AS PartyBranchIsEOU, PB.ISEXCISEAPPLICABLE AS PartyBranchIsExciseApplicable, PB.ISCUSTOMERPRODUCT AS PartyBranchIsCustomerProduct, PB.ISSALESAPPLICABLE AS PartyBranchIsSalesApplicable, PB.ISPURCHASEAPPLICALBE AS PartyBranchIsPurchaseApplicable, PB.INDUSTRYTYPE AS PartyBranchIndustryType, PB.INSURANCETYPE AS PartyBranchInsuranceType, PB.FREIGHTTYPE AS PartyBranchFreightType, PB.STANDARDREGIONID AS PartyBranchStandardRegionId, PB.SEQUENCENUMBER AS PartyBranchSequenceNumber, PB.PARTYID AS PartyId, ISNULL(P.PARTYCODE, 'NONE') AS PartyCode, ISNULL(P.PARTYNAME, 'NONE') AS PartyName, ISNULL(P.ACCOUNTLINKID, -1) AS PartyBranchPartyAccountLinkId, ISNULL(P.ACCOUNTGROUPID, -1) AS PartyBranchPartyAccountGroupId, ISNULL(P.CONTROLACCOUNTID, -1) AS PartyBranchPartyControlAccountId, ISNULL(P.VERSION, 0) AS PartyBranchPartyPartyVersion, PB.TENANTID AS TenantId, PB.DEFAULTADDRESSID AS DefaultAddressId, ISNULL(AD.ADDRESSLINE1, 'NONE') AS DefaultAddressLine1, ISNULL(AD.ADDRESSLINE2, 'NONE') AS DefaultAddressLine2, ISNULL(AD.ADDRESSLINE3, 'NONE') AS DefaultAddressLine3, ISNULL(AD.MOBILE, 'NONE') AS DefaultAddressMobile, PB.ROUTEID AS RouteId, ISNULL(RT.ROUTECODE, 'NONE') AS RouteRouteCode, ISNULL(RT.ROUTENAME, 'NONE') AS RouteRouteName, PB.TAXTYPEID AS TaxTypeId, ISNULL(TT.PARTYTAXTYPECODE, 'NONE') AS TaxTypeCode, ISNULL(TT.PARTYTAXTYPENAME, 'NONE') AS TaxTypeName, PB.CURRENCYID AS CurrencyId, ISNULL(CUR.CURRENCYCODE, 'NONE') AS CurrencyCode, ISNULL(CUR.CURRENCYNAME, 'NONE') AS CurrencyName, PB.INCHARGEID AS InchargeId, ISNULL(INC.EMPLOYEECODE, 'NONE') AS InchargeCode, ISNULL(INC.EMPLOYEENAME, 'NONE') AS InchargeName, PB.PURCHASEPRICELISTID AS PurchasePriceListId, ISNULL(PPL.PARTYPRICECATEGORYCODE, 'NONE') AS PurchasePriceListCode, ISNULL(PPL.PARTYPRICECATEGORYNAME, 'NONE') AS PurchasePriceListName, PB.SALESPRICELISTID AS SalesPriceListId, ISNULL(SPL.PARTYPRICECATEGORYCODE, 'NONE') AS SalesPriceListCode, ISNULL(SPL.PARTYPRICECATEGORYNAME, 'NONE') AS SalesPriceListName, PB.AGENTID AS AgentId, ISNULL(AGT.PARTYCODE, 'NONE') AS AgentCode, ISNULL(AGT.PARTYNAME, 'NONE') AS AgentName, PB.TRANSPORTERID AS TransporterId, ISNULL(TRN.PARTYCODE, 'NONE') AS TransporterCode, ISNULL(TRN.PARTYNAME, 'NONE') AS TransporterName, PB.SALESPAYMENTTERMID AS SalesPaymentTermId, ISNULL(SPT.PAYMENTTERMCODE, 'NONE') AS SalesPaymentTermCode, ISNULL(SPT.PAYMENTTERMNAME, 'NONE') AS SalesPaymentTermName, PB.PURCHASEPAYMENTTERMID AS PurchasePaymentTermId, ISNULL(PPT.PAYMENTTERMCODE, 'NONE') AS PurchasePaymentTermCode, ISNULL(PPT.PAYMENTTERMNAME, 'NONE') AS PurchasePaymentTermName, PB.SALESINCHARGEID AS SalesInchargeId, ISNULL(SI.EMPLOYEECODE, 'NONE') AS SalesInchargeCode, ISNULL(SI.EMPLOYEENAME, 'NONE') AS SalesInchargeName, PB.PURCHASETERMSSETID AS PurchaseTermsSetId, ISNULL(PTS.TERMSSETNAME, 'NONE') AS PurchaseTermsSetName, PB.SALESTERMSSETID AS SalesTermsSetId, ISNULL(STS.TERMSSETNAME, 'NONE') AS SalesTermsSetName, PB.INSURANCEID AS InsuranceId, ISNULL(INS.POLICYNUMBER, 'NONE') AS InsurancePolicyNumber, PB.MRPPARTYTYPEID AS MRPPartyTypeId, ISNULL(MRP.MRPPARTYTYPECODE, 'NONE') AS MRPPartyTypeCode, ISNULL(MRP.MRPPARTYTYPENAME, 'NONE') AS MRPPartyTypeName, PB.DISTRIBUTIONLISTID AS DistributionListId, ISNULL(DL.DISTRIBUTIONLISTCODE, 'NONE') AS DistributionListCode, ISNULL(DL.DISTRIBUTIONLISTNAME, 'NONE') AS DistributionListName, PB.ROUTEIDS AS PartyBranchRouteIds, -- STRING_SPLIT/STRING_AGG need SQL Server 2017+/compat level 140+ — some environments (e.g. QC) -- run an older compat level where those are flagged as an invalid object name. FOR XML PATH + STUFF is the -- pre-2016-compatible equivalent (comma-padded LIKE avoids partial-number false positives). ( SELECT ISNULL(STUFF(( SELECT ',' + RT2.ROUTENAME FROM MROUTE RT2 WHERE ',' + CAST(PB.ROUTEIDS AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(RT2.ROUTEID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchRouteNames, PB.CREDITDAYS AS PartyBranchCreditDays, PB.NOOFBILLS AS PartyBranchNoOfBills, PB.LINKEDPARTIES AS PartyBranchLinkedParties, ( SELECT ISNULL(STUFF(( SELECT ',' + PB2.PARTYBRANCHNAME FROM MPARTYBRANCH PB2 WHERE ',' + CAST(PB.LINKEDPARTIES AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(PB2.PARTYBRANCHID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchLinkedPartyNames, PB.PROJECTMANAGERIDS AS PartyBranchProjectManagerIds, ( SELECT ISNULL(STUFF(( SELECT ',' + E1.EMPLOYEENAME FROM MEMPLOYEE E1 WHERE ',' + CAST(PB.PROJECTMANAGERIDS AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(E1.EMPLOYEEID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchProjectManagerNames, PB.ACCOUNTMANAGERIDS AS PartyBranchAccountManagerIds, ( SELECT ISNULL(STUFF(( SELECT ',' + E2.EMPLOYEENAME FROM MEMPLOYEE E2 WHERE ',' + CAST(PB.ACCOUNTMANAGERIDS AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(E2.EMPLOYEEID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchAccountManagerNames, PB.MSMEREGNO AS PartyBranchMSMERegNo, PB.REGISTRATION AS PartyBranchRegistration, PB.PARTYINFO AS PartyBranchPartyInfo, ( SELECT ISNULL(STUFF(( SELECT ',' + G.GCMNAME FROM MGCM G WHERE ',' + CAST(PB.PARTYINFO AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(G.GCMID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchPartyInfoName, PB.ISSUBCONTRACT AS PartyBranchIsSubContract, PB.SORTORDER AS PartyBranchSortOrder, PB.STATUS AS PartyBranchStatus, PB.VERSION AS PartyBranchVersion, PB.SOURCETYPE AS PartyBranchSourceType, PB.CREATEDBYID AS PartyBranchCreatedById, PB.CREATEDON AS PartyBranchCreatedOn, PB.MODIFIEDBYID AS PartyBranchModifiedById, PB.MODIFIEDON AS PartyBranchModifiedOn FROM MPARTYBRANCH PB LEFT JOIN MPARTY P ON P.PARTYID = PB.PARTYID LEFT JOIN MADDRESS AD ON AD.ADDRESSID = PB.DEFAULTADDRESSID LEFT JOIN MROUTE RT ON RT.ROUTEID = PB.ROUTEID LEFT JOIN MPARTYTAXTYPE TT ON TT.PARTYTAXTYPEID = PB.TAXTYPEID LEFT JOIN MCURRENCY CUR ON CUR.CURRENCYID = PB.CURRENCYID LEFT JOIN MEMPLOYEE INC ON INC.EMPLOYEEID = PB.INCHARGEID LEFT JOIN MPARTYPRICECATEGORY PPL ON PPL.PARTYPRICECATEGORYID = PB.PURCHASEPRICELISTID LEFT JOIN MPARTYPRICECATEGORY SPL ON SPL.PARTYPRICECATEGORYID = PB.SALESPRICELISTID LEFT JOIN MPARTY AGT ON AGT.PARTYID = PB.AGENTID LEFT JOIN MPARTY TRN ON TRN.PARTYID = PB.TRANSPORTERID LEFT JOIN MPAYMENTTERM SPT ON SPT.PAYMENTTERMID = PB.SALESPAYMENTTERMID LEFT JOIN MPAYMENTTERM PPT ON PPT.PAYMENTTERMID = PB.PURCHASEPAYMENTTERMID LEFT JOIN MEMPLOYEE SI ON SI.EMPLOYEEID = PB.SALESINCHARGEID LEFT JOIN MTERMSSET PTS ON PTS.TERMSSETID = PB.PURCHASETERMSSETID LEFT JOIN MTERMSSET STS ON STS.TERMSSETID = PB.SALESTERMSSETID LEFT JOIN MINSURANCE INS ON INS.INSURANCEID = PB.INSURANCEID LEFT JOIN MMRPPARTYTYPE MRP ON MRP.MRPPARTYTYPEID = PB.MRPPARTYTYPEID LEFT JOIN MDISTRIBUTIONLIST DL ON DL.DISTRIBUTIONLISTID = PB.DISTRIBUTIONLISTID WHERE (@SearchText IS NULL OR @SearchText = '' OR PB.PARTYBRANCHCODE LIKE '%' + @SearchText + '%' OR PB.PARTYBRANCHNAME LIKE '%' + @SearchText + '%') AND (@StatusEquals IS NULL OR PB.STATUS = @StatusEquals) AND (@StatusNotEquals IS NULL OR PB.STATUS <> @StatusNotEquals) ORDER BY PB.PARTYBRANCHCODE;"; public const string GET_PARTY_BRANCHES_ON_PARTY = @" SELECT PB.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, PB.PARTYBRANCHSHORTNAME AS PartyBranchShortName, PB.PARTYBRANCHTYPE AS PartyBranchType, PB.ISEOU AS PartyBranchIsEOU, PB.ISEXCISEAPPLICABLE AS PartyBranchIsExciseApplicable, PB.ISCUSTOMERPRODUCT AS PartyBranchIsCustomerProduct, PB.ISSALESAPPLICABLE AS PartyBranchIsSalesApplicable, PB.ISPURCHASEAPPLICALBE AS PartyBranchIsPurchaseApplicable, PB.INDUSTRYTYPE AS PartyBranchIndustryType, PB.INSURANCETYPE AS PartyBranchInsuranceType, PB.FREIGHTTYPE AS PartyBranchFreightType, PB.STANDARDREGIONID AS PartyBranchStandardRegionId, PB.SEQUENCENUMBER AS PartyBranchSequenceNumber, PB.PARTYID AS PartyId, PB.DEFAULTADDRESSID AS DefaultAddressId, PB.ROUTEID AS RouteId, ISNULL(RT.ROUTECODE, 'NONE') AS RouteRouteCode, ISNULL(RT.ROUTENAME, 'NONE') AS RouteRouteName, PB.TAXTYPEID AS TaxTypeId, ISNULL(TT.PARTYTAXTYPECODE, 'NONE') AS TaxTypeCode, ISNULL(TT.PARTYTAXTYPENAME, 'NONE') AS TaxTypeName, PB.CURRENCYID AS CurrencyId, ISNULL(CUR.CURRENCYCODE, 'NONE') AS CurrencyCode, ISNULL(CUR.CURRENCYNAME, 'NONE') AS CurrencyName, PB.INCHARGEID AS InchargeId, ISNULL(INC.EMPLOYEECODE, 'NONE') AS InchargeCode, ISNULL(INC.EMPLOYEENAME, 'NONE') AS InchargeName, PB.PURCHASEPRICELISTID AS PurchasePriceListId, ISNULL(PPL.PARTYPRICECATEGORYCODE, 'NONE') AS PurchasePriceListCode, ISNULL(PPL.PARTYPRICECATEGORYNAME, 'NONE') AS PurchasePriceListName, PB.SALESPRICELISTID AS SalesPriceListId, ISNULL(SPL.PARTYPRICECATEGORYCODE, 'NONE') AS SalesPriceListCode, ISNULL(SPL.PARTYPRICECATEGORYNAME, 'NONE') AS SalesPriceListName, PB.AGENTID AS AgentId, ISNULL(AGT.PARTYCODE, 'NONE') AS AgentCode, ISNULL(AGT.PARTYNAME, 'NONE') AS AgentName, PB.TRANSPORTERID AS TransporterId, ISNULL(TRN.PARTYCODE, 'NONE') AS TransporterCode, ISNULL(TRN.PARTYNAME, 'NONE') AS TransporterName, PB.SALESPAYMENTTERMID AS SalesPaymentTermId, ISNULL(SPT.PAYMENTTERMCODE, 'NONE') AS SalesPaymentTermCode, ISNULL(SPT.PAYMENTTERMNAME, 'NONE') AS SalesPaymentTermName, PB.PURCHASEPAYMENTTERMID AS PurchasePaymentTermId, ISNULL(PPT.PAYMENTTERMCODE, 'NONE') AS PurchasePaymentTermCode, ISNULL(PPT.PAYMENTTERMNAME, 'NONE') AS PurchasePaymentTermName, PB.SALESINCHARGEID AS SalesInchargeId, ISNULL(SI.EMPLOYEECODE, 'NONE') AS SalesInchargeCode, ISNULL(SI.EMPLOYEENAME, 'NONE') AS SalesInchargeName, PB.PURCHASETERMSSETID AS PurchaseTermsSetId, ISNULL(PTS.TERMSSETNAME, 'NONE') AS PurchaseTermsSetName, PB.SALESTERMSSETID AS SalesTermsSetId, ISNULL(STS.TERMSSETNAME, 'NONE') AS SalesTermsSetName, PB.INSURANCEID AS InsuranceId, ISNULL(INS.POLICYNUMBER, 'NONE') AS InsurancePolicyNumber, PB.MRPPARTYTYPEID AS MRPPartyTypeId, ISNULL(MRP.MRPPARTYTYPECODE, 'NONE') AS MRPPartyTypeCode, ISNULL(MRP.MRPPARTYTYPENAME, 'NONE') AS MRPPartyTypeName, PB.DISTRIBUTIONLISTID AS DistributionListId, ISNULL(DL.DISTRIBUTIONLISTCODE, 'NONE') AS DistributionListCode, ISNULL(DL.DISTRIBUTIONLISTNAME, 'NONE') AS DistributionListName, PB.ROUTEIDS AS PartyBranchRouteIds, -- STRING_SPLIT/STRING_AGG need SQL Server 2017+/compat level 140+ — some environments (e.g. QC) -- run an older compat level where those are flagged as an invalid object name. FOR XML PATH + STUFF is the -- pre-2016-compatible equivalent (comma-padded LIKE avoids partial-number false positives). ( SELECT ISNULL(STUFF(( SELECT ',' + RT2.ROUTENAME FROM MROUTE RT2 WHERE ',' + CAST(PB.ROUTEIDS AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(RT2.ROUTEID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchRouteNames, PB.CREDITDAYS AS PartyBranchCreditDays, PB.NOOFBILLS AS PartyBranchNoOfBills, PB.LINKEDPARTIES AS PartyBranchLinkedParties, PB.PROJECTMANAGERIDS AS PartyBranchProjectManagerIds, ( SELECT ISNULL(STUFF(( SELECT ',' + E1.EMPLOYEENAME FROM MEMPLOYEE E1 WHERE ',' + CAST(PB.PROJECTMANAGERIDS AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(E1.EMPLOYEEID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchProjectManagerNames, PB.ACCOUNTMANAGERIDS AS PartyBranchAccountManagerIds, ( SELECT ISNULL(STUFF(( SELECT ',' + E2.EMPLOYEENAME FROM MEMPLOYEE E2 WHERE ',' + CAST(PB.ACCOUNTMANAGERIDS AS VARCHAR(MAX)) + ',' LIKE '%,' + CAST(E2.EMPLOYEEID AS VARCHAR(20)) + ',%' FOR XML PATH('') ), 1, 1, ''), 'NONE') ) AS PartyBranchAccountManagerNames, PB.MSMEREGNO AS PartyBranchMSMERegNo, PB.REGISTRATION AS PartyBranchRegistration, PB.PARTYINFO AS PartyBranchPartyInfo, PB.ISSUBCONTRACT AS PartyBranchIsSubContract, PB.SORTORDER AS PartyBranchSortOrder, PB.STATUS AS PartyBranchStatus, PB.VERSION AS PartyBranchVersion, PB.SOURCETYPE AS PartyBranchSourceType, PB.CREATEDBYID AS PartyBranchCreatedById, PB.CREATEDON AS PartyBranchCreatedOn, PB.MODIFIEDBYID AS PartyBranchModifiedById, PB.MODIFIEDON AS PartyBranchModifiedOn FROM MPARTYBRANCH PB LEFT JOIN MROUTE RT ON RT.ROUTEID = PB.ROUTEID LEFT JOIN MPARTYTAXTYPE TT ON TT.PARTYTAXTYPEID = PB.TAXTYPEID LEFT JOIN MCURRENCY CUR ON CUR.CURRENCYID = PB.CURRENCYID LEFT JOIN MEMPLOYEE INC ON INC.EMPLOYEEID = PB.INCHARGEID LEFT JOIN MPARTYPRICECATEGORY PPL ON PPL.PARTYPRICECATEGORYID = PB.PURCHASEPRICELISTID LEFT JOIN MPARTYPRICECATEGORY SPL ON SPL.PARTYPRICECATEGORYID = PB.SALESPRICELISTID LEFT JOIN MPARTY AGT ON AGT.PARTYID = PB.AGENTID LEFT JOIN MPARTY TRN ON TRN.PARTYID = PB.TRANSPORTERID LEFT JOIN MPAYMENTTERM SPT ON SPT.PAYMENTTERMID = PB.SALESPAYMENTTERMID LEFT JOIN MPAYMENTTERM PPT ON PPT.PAYMENTTERMID = PB.PURCHASEPAYMENTTERMID LEFT JOIN MEMPLOYEE SI ON SI.EMPLOYEEID = PB.SALESINCHARGEID LEFT JOIN MTERMSSET PTS ON PTS.TERMSSETID = PB.PURCHASETERMSSETID LEFT JOIN MTERMSSET STS ON STS.TERMSSETID = PB.SALESTERMSSETID LEFT JOIN MINSURANCE INS ON INS.INSURANCEID = PB.INSURANCEID LEFT JOIN MMRPPARTYTYPE MRP ON MRP.MRPPARTYTYPEID = PB.MRPPARTYTYPEID LEFT JOIN MDISTRIBUTIONLIST DL ON DL.DISTRIBUTIONLISTID = PB.DISTRIBUTIONLISTID WHERE PB.PARTYID = @PartyId ORDER BY PB.SORTORDER, PB.PARTYBRANCHNAME;"; public const string GET_SELECTLIST_PARTY_BRANCH = @" WITH PagedPartyBranch AS ( SELECT PB.PARTYBRANCHID AS Id, PB.PARTYBRANCHCODE AS Code, PB.PARTYBRANCHNAME AS Name, PB.PARTYID AS PartyId, ROW_NUMBER() OVER (ORDER BY PB.PARTYBRANCHNAME) AS RowNum FROM MPARTYBRANCH PB WHERE PB.STATUS <> 2 AND (@PartyId <= 0 OR PB.PARTYID = @PartyId) AND (@SearchText IS NULL OR @SearchText = '' OR PB.PARTYBRANCHCODE LIKE '%' + @SearchText + '%' OR PB.PARTYBRANCHNAME LIKE '%' + @SearchText + '%') ) SELECT Id, Code, Name, PartyId FROM PagedPartyBranch WHERE @MaxResult < 0 OR RowNum BETWEEN (@FirstNumber + 1) AND (@FirstNumber + @MaxResult) ORDER BY RowNum;"; // MPARTYBRANCH FK columns below are NOT NULL per legacy NHibernate mapping // (D:\GB4 Service\DAL\AccountsDAL\HibernateMapFile\PartyBranch.hbm.xml — all many-to-one // associations declare not-null="true"): CURRENCYID, PURCHASEPRICELISTID, SALESPRICELISTID, // TAXTYPEID, AGENTID, TRANSPORTERID, SALESPAYMENTTERMID, PURCHASEPAYMENTTERMID, // SALESINCHARGEID, DEFAULTADDRESSID, PURCHASETERMSSETID, SALESTERMSSETID, ROUTEID, // INSURANCEID, MRPPARTYTYPEID, DISTRIBUTIONLISTID, INCHARGEID, PARTYID. public const string SAVE_PARTY_BRANCH = @" INSERT INTO MPARTYBRANCH ( PARTYBRANCHID, PARTYBRANCHCODE, PARTYBRANCHNAME, PARTYBRANCHSHORTNAME, SEQUENCENUMBER, PARTYID, DEFAULTADDRESSID, ROUTEID, ROUTEIDS, TAXTYPEID, STANDARDREGIONID, CURRENCYID, INCHARGEID, PURCHASEPRICELISTID, SALESPRICELISTID, AGENTID, TRANSPORTERID, SALESPAYMENTTERMID, PURCHASEPAYMENTTERMID, SALESINCHARGEID, PURCHASETERMSSETID, SALESTERMSSETID, INSURANCEID, MRPPARTYTYPEID, DISTRIBUTIONLISTID, CREDITDAYS, NOOFBILLS, LINKEDPARTIES, PROJECTMANAGERIDS, ACCOUNTMANAGERIDS, MSMEREGNO, REGISTRATION, PARTYINFO, ISSUBCONTRACT, PARTYBRANCHTYPE, ISEOU, ISEXCISEAPPLICABLE, ISCUSTOMERPRODUCT, ISSALESAPPLICABLE, ISPURCHASEAPPLICALBE, INDUSTRYTYPE, INSURANCETYPE, FREIGHTTYPE, SORTORDER, STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON ) VALUES ( @PartyBranchId, @PartyBranchCode, @PartyBranchName, @PartyBranchShortName, @PartyBranchSequenceNumber, @PartyId, @DefaultAddressId, @RouteId, @PartyBranchRouteIds, @TaxTypeId, @PartyBranchStandardRegionId, @CurrencyId, @InchargeId, @PurchasePriceListId, @SalesPriceListId, @AgentId, @TransporterId, @SalesPaymentTermId, @PurchasePaymentTermId, @SalesInchargeId, @PurchaseTermsSetId, @SalesTermsSetId, @InsuranceId, @MRPPartyTypeId, @DistributionListId, @PartyBranchCreditDays, @PartyBranchNoOfBills, @PartyBranchLinkedParties, @PartyBranchProjectManagerIds, @PartyBranchAccountManagerIds, @PartyBranchMSMERegNo, @PartyBranchRegistration, @PartyBranchPartyInfo, @PartyBranchIsSubContract, @PartyBranchType, @PartyBranchIsEOU, @PartyBranchIsExciseApplicable, @PartyBranchIsCustomerProduct, @PartyBranchIsSalesApplicable, @PartyBranchIsPurchaseApplicable, @PartyBranchIndustryType, @PartyBranchInsuranceType, @PartyBranchFreightType, @PartyBranchSortOrder, @PartyBranchStatus, @PartyBranchVersion, @PartyBranchSourceType, @PartyBranchCreatedById, @PartyBranchCreatedOn, @PartyBranchModifiedById, @PartyBranchModifiedOn );"; public const string UPDATE_PARTY_BRANCH = @" UPDATE MPARTYBRANCH SET PARTYBRANCHCODE = @PartyBranchCode, PARTYBRANCHNAME = @PartyBranchName, PARTYBRANCHSHORTNAME = @PartyBranchShortName, SEQUENCENUMBER = @PartyBranchSequenceNumber, PARTYID = @PartyId, STANDARDREGIONID = @PartyBranchStandardRegionId, DEFAULTADDRESSID = @DefaultAddressId, ROUTEID = @RouteId, ROUTEIDS = @PartyBranchRouteIds, TAXTYPEID = @TaxTypeId, CURRENCYID = @CurrencyId, INCHARGEID = @InchargeId, PURCHASEPRICELISTID = @PurchasePriceListId, SALESPRICELISTID = @SalesPriceListId, AGENTID = @AgentId, TRANSPORTERID = @TransporterId, SALESPAYMENTTERMID = @SalesPaymentTermId, PURCHASEPAYMENTTERMID = @PurchasePaymentTermId, SALESINCHARGEID = @SalesInchargeId, PURCHASETERMSSETID = @PurchaseTermsSetId, SALESTERMSSETID = @SalesTermsSetId, INSURANCEID = @InsuranceId, MRPPARTYTYPEID = @MRPPartyTypeId, DISTRIBUTIONLISTID = @DistributionListId, CREDITDAYS = @PartyBranchCreditDays, NOOFBILLS = @PartyBranchNoOfBills, LINKEDPARTIES = @PartyBranchLinkedParties, PROJECTMANAGERIDS = @PartyBranchProjectManagerIds, ACCOUNTMANAGERIDS = @PartyBranchAccountManagerIds, MSMEREGNO = @PartyBranchMSMERegNo, REGISTRATION = @PartyBranchRegistration, PARTYINFO = @PartyBranchPartyInfo, ISSUBCONTRACT = @PartyBranchIsSubContract, PARTYBRANCHTYPE = @PartyBranchType, ISEOU = @PartyBranchIsEOU, ISEXCISEAPPLICABLE = @PartyBranchIsExciseApplicable, ISCUSTOMERPRODUCT = @PartyBranchIsCustomerProduct, ISSALESAPPLICABLE = @PartyBranchIsSalesApplicable, ISPURCHASEAPPLICALBE = @PartyBranchIsPurchaseApplicable, INDUSTRYTYPE = @PartyBranchIndustryType, INSURANCETYPE = @PartyBranchInsuranceType, FREIGHTTYPE = @PartyBranchFreightType, SORTORDER = @PartyBranchSortOrder, STATUS = @PartyBranchStatus, VERSION = @PartyBranchVersion, SOURCETYPE = @PartyBranchSourceType, MODIFIEDBYID = @PartyBranchModifiedById, MODIFIEDON = @PartyBranchModifiedOn WHERE PARTYBRANCHID = @PartyBranchId;"; // PartyBranch's configurable (MADDONFIELDS-driven) addon block. Mirrors the read-only // query already used by PartyDetailDAL for the inventory-lookup screen (PartyDetailQB // GET_PARTYBRANCH_ADDON_FIELDS / GET_PARTYBRANCH_ADDON_BASED_ON_ID) — duplicated here // rather than cross-referenced so PartyBranchDAL stays self-contained, matching how every // other module (Employee/PayRevision addon) owns its own QB constants rather than sharing // a central "addon DAL". Only MADDONFIELDS-registered column names are ever selected/written, // never the caller's raw JSON keys — those only decide which registered columns get a value. public const string GET_PARTYBRANCH_ADDON_FIELDS = @" SELECT FIELDNAME AS DBObjectFieldsName FROM MADDONFIELDS WHERE ENTITYID = @EntityId AND STATUS = 1"; public const string GET_PARTYBRANCH_ADDON = @" SELECT {0} FROM MPARTYBRANCHADDON WHERE PARTYBRANCHID = @PartyBranchId"; public const string PARTYBRANCH_ADDON_EXISTS = @" SELECT COUNT(1) FROM MPARTYBRANCHADDON WHERE PARTYBRANCHID = @PartyBranchId"; public const string DELETE_PARTY_BRANCH_ACCOUNT_OU = @" DELETE FROM MPARTYACCOUNTOU WHERE PARTYBRANCHID = @PartyBranchId;"; public const string DELETE_PARTY_BRANCH = @" DELETE FROM MPARTYBRANCH WHERE PARTYBRANCHID = @PartyBranchId;"; // Legacy GB4 PartyBranch.svc/SiteDetails — an EAV parameter-set ("site details") attached to a // PartyBranch via MPARTYBRANCHINFO (effective-dated header, WEFROM/WETO) and MPARTYBRANCHINFODETAIL // (one row per parameter). Confirmed live schema for both tables via INFORMATION_SCHEMA earlier // this session — MPARTYBRANCHINFO has TENANTID, MPARTYBRANCHINFODETAIL does not. Only rows whose // effective window fully spans "today" are returned (@TodayFrom/@TodayTo computed server-side in // the DAL as UTC 00:00:00.000/23:59:59.000, matching GB4's exact TodayFrom/TodayTo computation). public const string GET_PARTY_BRANCH_SITE_DETAILS = @" SELECT D.SLNO AS Slno, D.PARAMETERID AS ParameterId, ISNULL(P.PARAMETERCODE,'NONE') AS ParameterCode, ISNULL(P.PARAMETERNAME,'NONE') AS ParameterName, ISNULL(D.INPUTVALUE, 0) AS InputValue, D.INPUTTEXT AS InputText, ISNULL(D.INPUTDATE, '1800-01-01') AS InputDate, ISNULL(D.FINALVALUE, 0) AS FinalValue, D.UOMID AS UOMId, ISNULL(U.UOMCODE,'NONE') AS UOMCode, ISNULL(U.UOMNAME,'NONE') AS UOMName, H.PARTYBRANCHINFOID AS PartyBranchInfoId, H.PARAMETERSETID AS PartyBranchInfoParameterSetId FROM MPARTYBRANCHINFO H INNER JOIN MPARTYBRANCHINFODETAIL D ON D.PARTYBRANCHINFOID = H.PARTYBRANCHINFOID LEFT JOIN MPARAMETER P ON P.PARAMETERID = D.PARAMETERID LEFT JOIN MUOM U ON U.UOMID = D.UOMID WHERE H.PARTYBRANCHID = @PartyBranchId AND H.WEFROM <= @TodayFrom AND H.WETO >= @TodayTo AND H.TENANTID = @TenantId;"; // Legacy GB4 GetPartyBranch's conditional TDS/Account enrichment: "if the branch's owning Party // is not itself flagged as an account (ISACCOUNT = 0), look up the linked MACCOUNT row (same id // as the Party, per the established Party->Account auto-link convention) and surface its TDS // settings." The ISACCOUNT = 0 gate is baked into the WHERE clause so the query itself returns // no row (not just null TDS fields) whenever the party IS its own account — matching GB4's exact // "skip the whole block" behavior for that case, at which point PartyBranchDTO's own field // initializers (TDSCategoryId=-1, AccountIsTDSApplicable=1, AccountIsCompany=1, TDSState='NONE') // already match GB4's fallback values with no further code needed. public const string GET_PARTY_BRANCH_ACCOUNT_TDS = @" SELECT TOP 1 ISNULL(A.TDSCATEGORYID, -1) AS TDSCategoryId, ISNULL(TC.TDSCATEGORYCODE,'NONE') AS TDSCategoryCode, ISNULL(TC.TDSCATEGORYNAME,'NONE') AS TDSCategoryName, ISNULL(A.ISTDSAPPLICABLE, 1) AS AccountIsTDSApplicable, ISNULL(A.ISCOMPANY, 1) AS AccountIsCompany, ISNULL(A.TDSSTATE,'NONE') AS AccountTDSState FROM MPARTY P INNER JOIN MACCOUNT A ON A.ACCOUNTID = P.PARTYID LEFT JOIN MTDSCATEGORY TC ON TC.TDSCATEGORYID = A.TDSCATEGORYID WHERE P.PARTYID = @PartyId AND P.ISACCOUNT = 0;"; } }