using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CostingDAL.Query.Biztransaction
{
///
/// Query Builder — BizTransaction queries.
/// GB5 migration from GB4 BizTransactionQueryBuilder.
///
/// GB4 used HQL (object navigation) and string-replacement params (:param).
/// GB5 uses raw SQL with Dapper-style @Param named parameters.
///
/// Key Tables:
/// MBIZTRANSACTIONKEYS — VNO sequence keys
/// MBIZTRANSACTIONTYPE — Transaction type configuration
/// MBIZTRANSACTION — Transaction definitions
/// MBIZTRANSACTIONCLASS — Transaction class definitions
/// MBIZTRANSACTIONSUBCLASS — Transaction subclass definitions
/// MORGANIZATIONUNIT — Organization units
/// MPERIOD — Fiscal periods
/// MMODULE — Module definitions
///
/// IMPORTANT: Column names in MBIZTRANSACTIONKEYS are prefixed:
/// BIZTRANSACTIONKEYSID, BIZTRANSACTIONTYPEID,
/// BIZTRANSACTIONYEAR, BIZTRANSACTIONMONTH, BIZTRANSACTIONDAY,
/// LASTNO, PREFIX, SUFFIX, SYSTEMPREFIX, SYSTEMSUFFIX
///
public static class BizTransactionQB
{
#region VNO Generation Queries
///
/// Get VNO configuration fields from MBIZTRANSACTIONTYPE.
/// GB4 parity: GetCondition<BIZTransactionType> loaded full POCO.
/// GB5: projection query — only the fields needed for VNO generation.
///
public const string GET_BIZTRANSACTIONTYPE_FOR_VNO = @"
SELECT
ISNULL(NOGENERATIONTYPE, 0) AS NOGenerationType,
ISNULL(NOGENERATIONPERIODTYPE,0) AS NOGenerationPeriodType,
ISNULL(NUMBERSIZE, 0) AS NumberSize,
ISNULL(PREFIX, '') AS Prefix,
ISNULL(SUFFIX, '') AS Suffix,
ISNULL(SYSTEMPREFIX, '') AS SystemPrefix,
ISNULL(SYSTEMSUFFIX, '') AS SystemSuffix
FROM MBIZTRANSACTIONTYPE
WHERE BIZTRANSACTIONTYPEID = @BIZTransactionTypeId";
///
/// Yearly: BIZTRANSACTIONYEAR sentinel = -2, BIZTRANSACTIONMONTH=0, BIZTRANSACTIONDAY=0
/// GB4: GET_BIZTRANSACTIONKEYS_BASED_ON_YEAR_SQL
///
public const string GET_BIZTRANSACTIONKEYS_YEARLY = @"
SELECT
BIZTRANSACTIONKEYSID AS BIZTransactionKeysId,
LASTNO AS LastNo
FROM MBIZTRANSACTIONKEYS
WHERE BIZTRANSACTIONTYPEID = @BIZTransactionTypeId
AND BIZTRANSACTIONYEAR = -2
AND BIZTRANSACTIONMONTH = 0
AND BIZTRANSACTIONDAY = 0";
///
/// Monthly: filter by actual Year + Month, Day=0
/// GB4: GET_BIZTRANSACTIONKEYS_BASED_ON_MONTH_SQL
///
public const string GET_BIZTRANSACTIONKEYS_MONTHLY = @"
SELECT
BIZTRANSACTIONKEYSID AS BIZTransactionKeysId,
LASTNO AS LastNo
FROM MBIZTRANSACTIONKEYS
WHERE BIZTRANSACTIONTYPEID = @BIZTransactionTypeId
AND BIZTRANSACTIONYEAR = @Year
AND BIZTRANSACTIONMONTH = @Month
AND BIZTRANSACTIONDAY = 0";
///
/// Daily: filter by actual Year + Month + Day
/// GB4: GET_BIZTRANSACTIONKEYS_BASED_ON_DAY_SQL
///
public const string GET_BIZTRANSACTIONKEYS_DAILY = @"
SELECT
BIZTRANSACTIONKEYSID AS BIZTransactionKeysId,
LASTNO AS LastNo
FROM MBIZTRANSACTIONKEYS
WHERE BIZTRANSACTIONTYPEID = @BIZTransactionTypeId
AND BIZTRANSACTIONYEAR = @Year
AND BIZTRANSACTIONMONTH = @Month
AND BIZTRANSACTIONDAY = @Day";
///
/// Continuously: BIZTRANSACTIONYEAR sentinel = -1, no date filter
/// GB4: GET_BIZTRANSACTIONKEYS_FOR_CONTINUOUS_SQL
///
public const string GET_BIZTRANSACTIONKEYS_CONTINUOUS = @"
SELECT
BIZTRANSACTIONKEYSID AS BIZTransactionKeysId,
LASTNO AS LastNo
FROM MBIZTRANSACTIONKEYS
WHERE BIZTRANSACTIONTYPEID = @BIZTransactionTypeId
AND BIZTRANSACTIONYEAR = -1
AND BIZTRANSACTIONMONTH = 0
AND BIZTRANSACTIONDAY = 0";
///
/// Increment LASTNO for existing keys row.
/// GB4 parity: UPDDATE_BIZTRANSACTIONKEYS_FOR_BIZTRANSACTIONKEYSID
/// Single VNO: caller passes LastNo = existing.LastNo + 1
/// Multi VNO: caller passes LastNo = existing.LastNo + count
///
public const string UPDATE_BIZTRANSACTIONKEYS_LASTNO = @"
UPDATE MBIZTRANSACTIONKEYS
SET LASTNO = @LastNo
WHERE BIZTRANSACTIONKEYSID = @BIZTransactionKeysId";
///
/// Insert new keys row (first use of this BizType + period slot).
/// GB4 parity: Save<BIZTransactionKeys>
/// LastNo = 1 for single VNO, = count for multi VNO.
///
public const string INSERT_BIZTRANSACTIONKEYS = @"
INSERT INTO MBIZTRANSACTIONKEYS (
BIZTRANSACTIONKEYSID,
BIZTRANSACTIONTYPEID,
BIZTRANSACTIONYEAR,
BIZTRANSACTIONMONTH,
BIZTRANSACTIONDAY,
PREFIX,
SUFFIX,
SYSTEMPREFIX,
LASTNO
) VALUES (
@BIZTransactionKeysId,
@BIZTransactionTypeId,
@Year,
@Month,
@Day,
@Prefix,
@Suffix,
@SystemPrefix,
@LastNo
)";
#endregion
#region BIZTransactionType Lookup Queries
///
/// Get BIZTransactionType by OU, Class, and Period.
/// GB4 parity: GET_BIZTRANSACTIONKEYS_BASED_ON_CRITERIA (HQL)
///
public const string GET_BIZTRANSACTIONTYPE_BY_CRITERIA = @"
SELECT
BIZTRANSACTIONTYPEID AS Id
FROM MBIZTRANSACTIONTYPE
WHERE OUID = @OUId
AND BIZTRANSACTIONCLASSID = @ClassId
AND PERIODID = @PeriodId";
///
/// Get BIZTransactionType list with base fields — supports dynamic WHERE clause appending.
/// GB4 parity: GET_BIZTRANSACTIONTYPES_BASED_ON_CRITERIA (HQL with 1=1)
/// Append additional conditions after the base query.
///
public const string GET_BIZTRANSACTIONTYPES_BASE = @"
SELECT
bt.BIZTRANSACTIONTYPEID AS Id,
bt.BIZTRANSACTIONTYPECODE AS Code,
bt.BIZTRANSACTIONTYPENAME AS Name,
p.PERIODID AS PeriodId,
p.PERIODCODE AS PeriodCode,
p.PERIODNAME AS PeriodName,
ou.OUID AS OrganizationUnitId,
ou.ORGANIZATIONUNITCODE AS OrganizationUnitCode,
ou.ORGANIZATIONUNITNAME AS OrganizationUnitName
FROM MBIZTRANSACTIONTYPE bt
INNER JOIN MPERIOD p ON bt.PERIODID = p.PERIODID
INNER JOIN MORGANIZATIONUNIT ou ON bt.OUID = ou.OUID
WHERE 1 = 1";
///
/// Get BIZTransactionType by ID.
/// GB4 parity: GET_BIZTRANSACTIONTYPE_BASED_ON_ID (HQL)
///
public const string GET_BIZTRANSACTIONTYPE_BY_ID = @"
SELECT
BIZTRANSACTIONTYPEID AS Id,
BIZTRANSACTIONTYPECODE AS Code,
BIZTRANSACTIONTYPENAME AS Name,
OUID AS OUId,
BIZTRANSACTIONCLASSID AS ClassId,
BIZTRANSACTIONSUBCLASSID AS SubClassId,
PERIODID AS PeriodId
FROM MBIZTRANSACTIONTYPE
WHERE BIZTRANSACTIONTYPEID = @BIZTransactionTypeId";
///
/// Get selected quantity flags for a BIZTransactionType.
/// GB4 parity: GET_SELLECTED_BIZTRANSACTIONTYPE_DETAIL
///
public const string GET_BIZTRANSACTIONTYPE_QUANTITY_FLAGS = @"
SELECT
ISNULL(ISGOODQUANTITY, 0) AS IsGoodQuantity,
ISNULL(ISREJECTEDQUANTITY, 0) AS IsRejectedQuantity,
ISNULL(ISREWORKQUANTITY, 0) AS IsReworkQuantity,
ISNULL(ISOTHERQUANTITY, 0) AS IsOtherQuantity,
ISNULL(ITEMFROMITEMPARTYONLY, 0) AS ItemFromItemParty
FROM MBIZTRANSACTIONTYPE
WHERE BIZTRANSACTIONTYPEID = @BIZTransactionTypeId";
///
/// Get full BIZTransactionType details with all related entities.
/// GB4 parity: GET_BIZTRANSACTIONTYPE_DETAILS (large HQL query)
/// Returns all configuration flags and related entity info.
///
public const string GET_BIZTRANSACTIONTYPE_FULL_DETAILS = @"
SELECT DISTINCT
m.MODULEID AS BIZTransactionTypeModuleId,
m.MODULECODE AS BIZTransactionTypeModuleCode,
m.MODULENAME AS BIZTransactionTypeModuleName,
bt.BIZTRANSACTIONTYPEID AS BIZTransactionTypeId,
bt.BIZTRANSACTIONTYPECODE AS BIZTransactionTypeCode,
bt.BIZTRANSACTIONTYPENAME AS BIZTransactionTypeName,
b.BIZTRANSACTIONID AS BIZTransactionId,
b.BIZTRANSACTIONCODE AS BIZTransactionCode,
b.BIZTRANSACTIONNAME AS BIZTransactionName,
bsc.BIZTRANSACTIONSUBCLASSID AS BIZTransactionTypeSubClassId,
bsc.BIZTRANSACTIONSUBCLASSCODE AS BIZTransactionTypeSubClassCode,
bsc.BIZTRANSACTIONSUBCLASSNAME AS BIZTransactionTypeSubClassName,
bc.BIZTRANSACTIONCLASSID AS BIZTransactionTypeClassId,
bc.BIZTRANSACTIONCLASSCODE AS BIZTransactionTypeClassCode,
bc.BIZTRANSACTIONCLASSNAME AS BIZTransactionTypeClassName,
p.PERIODID AS PeriodId,
p.PERIODCODE AS PeriodCode,
p.PERIODNAME AS PeriodName,
bt.OUTYPE AS BIZTransactionTypeOUType,
ou.OUID AS OrganizationUnitId,
ou.ORGANIZATIONUNITCODE AS OrganizationUnitCode,
ou.ORGANIZATIONUNITNAME AS OrganizationUnitName,
bt.ORGANIZATIONGROUPID AS OrganizationGroupId,
og.ORGANIZATIONGROUPCODE AS OrganizationGroupCode,
og.ORGANIZATIONGROUPNAME AS OrganizationGroupName,
ISNULL(bt.NOGENERATIONTYPE, 0) AS BIZTransactionTypeNOGenerationType,
ISNULL(bt.NOGENERATIONPERIODTYPE,0) AS BIZTransactionTypeNOGenerationPeriodType,
ISNULL(bt.NUMBERSIZE, 0) AS BIZTransactionTypeNumberSize,
ISNULL(bt.PREFIX, '') AS BIZTransactionTypePrefix,
ISNULL(bt.SUFFIX, '') AS BIZTransactionTypeSuffix,
ISNULL(bt.SYSTEMPREFIX, '') AS BIZTransactionTypeSystemPrefix,
ISNULL(bt.SYSTEMSUFFIX, '') AS BIZTransactionTypeSystemSuffix,
bt.STOREID AS StoreId,
s.STORECODE AS StoreCode,
s.STORENAME AS StoreName,
bt.PROCESSID AS ProcessId,
pc.PROCESSCODE AS ProcessCode,
pc.PROCESSNAME AS ProcessName,
bt.WORKCENTERID AS WorkCenterId,
wc.WORKCENTERCODE AS WorkCenterCode,
wc.WORKCENTERNAME AS WorkCenterName,
bt.PARTYID AS BIZTransactionTypePartyId,
py.PARTYCODE AS BIZTransactionTypePartyCode,
py.PARTYNAME AS BIZTransactionTypePartyName,
bt.SEGMENTID AS BIZTransactionTypeSegmentId,
cc.COSTCENTERCODE AS BIZTransactionTypeSegmentCode,
cc.COSTCENTERNAME AS BIZTransactionTypeSegmentName,
bt.COUNTERID AS CounterId,
ctr.COUNTERCODE AS CounterCode,
ctr.COUNTERNAME AS CounterName,
bt.ADDITIONALCHARGEID AS AdditionalChargeId,
ac.CHARGECODE AS AdditionalChargeCode,
ac.CHARGENAME AS AdditionalChargeName,
bt.OTHERCHARGEID AS OtherChargeId,
oc.CHARGECODE AS OtherChargeCode,
oc.CHARGENAME AS OtherChargeName,
ISNULL(bt.ISMULTICURRENCY, 0) AS BIZTransactionTypeIsMultiCurrency,
ISNULL(bt.ISMULTIPLEOU, 0) AS BIZTransactionTypeIsMultipleOU,
ISNULL(bt.ISCOSTCENTER, 0) AS BIZTransactionTypeIsCostCenter,
ISNULL(bt.ISMULTIPLECOSTCATEGORY,0) AS BIZTransactionTypeIsMultipleCostCategory,
ISNULL(bt.ISSPLITCOSTCENTER, 0) AS BIZTransactionTypeIsSplitCostCenter,
ISNULL(bt.ISALLOCATION, 0) AS BIZTransactionTypeIsAllocation,
bt.ALLOCATIONTYPEID AS AllocationTypeId,
at.ALLOCATIONTYPECODE AS AllocationTypeCode,
at.ALLOCATIONTYPENAME AS AllocationTypeName,
ISNULL(bt.ALLOCATIONNATURE, 0) AS BIZTransactionTypeAllocationNature,
bt.MALLOCATIONID AS MAllocationId,
ma.ALLOCATIONNAME AS MAllocationName,
ISNULL(bt.ISMULTIPLEUOM, 0) AS BIZTransactionTypeIsMultipleUOM,
ISNULL(bt.ISPACKINGQUALITY, 0) AS BIZTransactionTypeIsPackingQuality,
ISNULL(bt.ISPACKINGDETAILS, 0) AS BIZTransactionTypeIsPackingDetails,
ISNULL(bt.ISBATCHDETAILS, 0) AS BIZTransactionTypeIsBatchDetails,
ISNULL(bt.ISBATCHCREATIONPOSSIBLE,0) AS BIZTransactionTypeIsBatchCreationPossible,
ISNULL(bt.ISSEPARATEBATCHENTRYPOSSIBLE,0) AS BIZTransactionTypeIsSeparateBatchEntryPossible,
ISNULL(bt.ISMOVEMENTTYPE, 0) AS BIZTransactionTypeIsMovementType,
ISNULL(bt.ISTRIPBILL, 0) AS BIZTransactionTypeIsTripBill,
ISNULL(bt.ISSUPPLIEMENTARY, 0) AS BIZTransactionTypeIsSuppliementary,
ISNULL(bt.ISMULTIPLESTORES, 0) AS BIZTransactionTypeIsMultipleStores,
ISNULL(bt.ISDELIVERYSCHEDULE, 0) AS BIZTransactionTypeIsDeliverySchedule,
ISNULL(bt.ISSCHEME, 0) AS BIZTransactionTypeIsScheme,
ISNULL(bt.ISFREE, 0) AS BIZTransactionTypeIsFree,
ISNULL(bt.RATEFORFREE, 0) AS BIZTransactionTypeRateForFree,
ISNULL(bt.ISEXCISETAX, 0) AS BIZTransactionTypeIsExcise,
ISNULL(bt.ISCENVAT, 0) AS BIZTransactionTypeIsCenVat,
ISNULL(bt.ISTRANSACTIONTYPECHANGEABLE,0) AS BIZTransactionTypeIsTransactionTypeChangeable,
ISNULL(bt.ISFORMDETAILS, 0) AS BIZTransactionTypeIsFormDetails,
ISNULL(bt.ACCOUNTPOSTTYPE, 0) AS BIZTransactionTypeAccountPostType,
ISNULL(bt.ACCOUNTPOSTBASIS, 0) AS BIZTransactionTypeAccountPostBasis,
ISNULL(bt.ACCOUNTVOUCHERTYPE, 0) AS BIZTransactionTypeAccountVoucherType,
bt.ACCOUNTBIZTRANSACTIONTYPEID AS AccountBizTransactionTypeId,
abt.BIZTRANSACTIONTYPECODE AS AccountBizTransactionTypeCode,
abt.BIZTRANSACTIONTYPENAME AS AccountBizTransactionTypeName,
ISNULL(bt.LEDGERTYPE, 0) AS BIZTransactionTypeLedgerType,
ISNULL(bt.NARRATIONTYPE, 0) AS BIZTransactionTypeNarrationType,
bt.FIXEDACCOUNTID AS FixedAccountId,
fa.ACCOUNTCODE AS FixedAccountCode,
fa.ACCOUNTNAME AS FixedAccountName,
ISNULL(bt.BILLACCOUNTTYPE, 0) AS BIZTransactionTypeBillAccountType,
ISNULL(bt.COSTOFSALESPOSTINGTYPE,0) AS BIZTransactionTypeCostOfSalesPostingType,
bt.COSTOFSALESACCOUNTID AS BIZTransactionTypeCostOfSalesAccountId,
ca.ACCOUNTCODE AS BIZTransactionTypeCostOfSalesAccountCode,
ca.ACCOUNTNAME AS BIZTransactionTypeCostOfSalesAccountName,
ISNULL(bt.ISCASHCREDIT, 0) AS BIZTransactionTypeIsCashCredit,
ISNULL(bt.CASHBILLACCOUNTINGTYPE,0) AS BIZTransactionTypeCashBillAccountingType,
bt.CASHACCOUNTID AS CashAccountId,
cca.ACCOUNTCODE AS CashAccountCode,
cca.ACCOUNTNAME AS CashAccountName,
ISNULL(bt.STOCKPOSTTYPE, 0) AS BIZTransactionTypeStockPostType,
ISNULL(bt.TRANSFERSTOCKPOSTTYPE, 0) AS BIZTransactionTypeTransferStockPostType,
ISNULL(bt.STOCKCHECKTYPE, 0) AS BIZTransactionTypeStockCheckType,
ISNULL(bt.ISSELECTION, 0) AS BIZTransactionTypeIsSelection,
ISNULL(bt.SELECTIONFROMTYPE, 0) AS BIZTransactionTypeSelectionFromType,
bt.SELECTIONCLASSID AS SelectionClassId,
sc.BIZTRANSACTIONCLASSCODE AS SelectionClassCode,
sc.BIZTRANSACTIONCLASSNAME AS SelectionClassName,
bt.SELECTIONSUBCLASSID AS SelectionSubClassId,
ssc.BIZTRANSACTIONSUBCLASSCODE AS SelectionSubClassCode,
ssc.BIZTRANSACTIONSUBCLASSNAME AS SelectionSubClassName,
ISNULL(bt.SELECTIONTYPE, 0) AS BIZTransactionTypeSelectionType,
ISNULL(bt.ISNONSELECTIONENTRY, 0) AS BIZTransactionTypeIsNonSelectionEntry,
ISNULL(bt.ISVARIANCEALLOWED, 0) AS BIZTransactionTypeIsVarianceAllowed,
ISNULL(bt.ISQUANTITYVARIANCE, 0) AS BIZTransactionTypeIsQuantityVariance,
ISNULL(bt.ISQUALITYVARIANCE, 0) AS BIZTransactionTypeIsQualityVariance,
ISNULL(bt.ISRATEVARIANCE, 0) AS BIZTransactionTypeIsRateVariance,
ISNULL(bt.ISPENDINGPOST, 0) AS BIZTransactionTypeIsPendingPost,
ISNULL(bt.PENDINGFORPROCESS, 0) AS BIZTransactionTypePendingForProcess,
ISNULL(bt.PENDINGFORSTOCK, 0) AS BIZTransactionTypePendingForStock,
ISNULL(bt.ISINTERCOMPANYTRACKING,0) AS BIZTransactionTypeIsInterCompanyTracking,
ISNULL(bt.ISDELIVERYTRACKING, 0) AS BIZTransactionTypeIsDeliveryTracking,
ISNULL(bt.DEFAULTDELIVERYTYPE, 0) AS BIZTransactionTypeDefaultDeliveryType,
ISNULL(bt.BACKDATEENTRYDAY, 0) AS BIZTransactionTypeBackDateEntryDay,
ISNULL(bt.BACKDATEUPDATEDAY, 0) AS BIZTransactionTypeBackDateUpdateDay,
ISNULL(bt.BACKDATEDELETEDAY, 0) AS BIZTransactionTypeBackDateDeleteDay,
ISNULL(bt.BACKDATECHECKTYPE, 0) AS BIZTransactionTypeBackDateCheckType,
ISNULL(bt.FORWARDENTRYDAY, 0) AS BIZTransactionTypeForwardEntryDay,
bt.CURRENCYID AS CurrencyId,
cur.CURRENCYCODE AS CurrencyCode,
cur.CURRENCYNAME AS CurrencyName,
ISNULL(bt.RATEPERUOMAPPLICABLE, 0) AS BIZTransactionTypeRatePerUOMApplicable,
ISNULL(bt.SKUAPPLICABLE, 0) AS BIZTransactionTypeSKUApplicable,
ISNULL(bt.ISACTUALQUANTITY, 0) AS BIZTransactionTypeIsActualQuantity,
ISNULL(bt.ISGOODQUANTITY, 0) AS BIZTransactionTypeIsGoodQuantity,
ISNULL(bt.ISREJECTEDQUANTITY, 0) AS BIZTransactionTypeIsRejectedQuantity,
ISNULL(bt.ISREWORKQUANTITY, 0) AS BIZTransactionTypeIsReworkQuantity,
ISNULL(bt.ISOTHERQUANTITY, 0) AS BIZTransactionTypeIsOtherQuantity,
ISNULL(bt.ISBATCHINHEADER, 0) AS BIZTransactionTypeIsBatchInHeader,
ISNULL(bt.FROMPACKSTATUS, 0) AS BIZTransactionTypeFromPackStatus,
ISNULL(bt.TOPACKSTATUS, 0) AS BIZTransactionTypeToPackStatus,
ISNULL(bt.PRINTTYPE, 0) AS BIZTransactionTypePrintType,
bt.LOTTYPEID AS LotTypeId,
lt.LOTTYPENAME AS LotTypeName,
bt.REPORTFORMATID AS ReportFormatId,
rf.REPORTFORMATNAME AS ReportFormatName,
bt.PARTYCRITERIACONFIGID AS PartyCriteriaConfigId,
bt.TOTALCHARGEID AS TotalChargeId,
tc.CHARGECODE AS TotalChargeCode,
tc.CHARGENAME AS TotalChargeName,
ISNULL(bt.ISDISCOUNTAPPLICABLE, 0) AS BIZTransactionTypeIsDiscountApplicable,
ISNULL(bt.STOCKVIEWTYPE, 0) AS BIZTransactionTypeStockviewType,
ISNULL(bt.ISSUETYPE, 0) AS BIZTransactionTypeIssueType,
ISNULL(bt.ISREASONAPPLICABLE, 0) AS BIZTransactionTypeIsReasonApplicable,
ISNULL(bt.ISQUALITYDETAILS, 0) AS BIZTransactionTypeIsQualityDetails,
ISNULL(bt.REJECTIONNCR, 0) AS BIZTransactionTypeRejectionNCR,
ISNULL(bt.REWORKNCR, 0) AS BIZTransactionTypeReworkNCR,
ISNULL(bt.OTHERNCR, 0) AS BIZTransactionTypeOtherNCR,
ISNULL(bt.ISCANCELAPPLICABLE, 0) AS BIZTransactionTypeIsCancelApplicable,
bt.SELECTIONBIZTRANSACTIONID AS SelectionBIZTransactionId,
sbt.BIZTRANSACTIONCODE AS SelectionBIZTransactionCode,
sbt.BIZTRANSACTIONNAME AS SelectionBIZTransactionName,
ISNULL(bt.PREVIOUSPROCESS, 0) AS BIZTransactionTypePreviousProcess,
ncr.BIZTRANSACTIONNAME AS NcrBiztransactionName,
icc.CONFIGNAME AS ItemCriteriaConfigName,
ier.REGISTERNAME AS ItemExciseRegisterName,
ISNULL(bt.PURPOSE, '') AS BIZTransactionTypePurpose,
ISNULL(cur.CURRENCYDECIMAL, 2) AS CurrencyCurrencyDecimal,
her.REGISTERNAME AS HeadExciseRegisterName,
ISNULL(bc.ISPARTYAPPLICABLE, 0) AS BizTransactionClassIsPartyApplicable,
ISNULL(bc.ISNARRATION, 0) AS BizTransactionClassIsNarration,
ISNULL(bc.ISVARIANCETRACKING, 0) AS BizTransactionClassIsVarianceTracking,
ISNULL(bc.ISACCOUNTPOST, 0) AS BizTransactionClassIsAccountPost,
ISNULL(bc.ISCHARGES, 0) AS BizTransactionClassIsCharges,
ISNULL(bc.ISPENDINGMAINTANCE, 0) AS BizTransactionClassIsPendingMaintance,
ISNULL(bc.ISSELECTION, 0) AS BizTransactionClassIsSelection,
ISNULL(bc.ISEXCISETAX, 0) AS BizTransactionClassIsExcise,
ISNULL(bc.ISDELIVERYSCHEDULE, 0) AS BizTransactionClassIsDeliverySchedule
FROM MBIZTRANSACTIONTYPE bt
INNER JOIN MBIZTRANSACTION b ON bt.BIZTRANSACTIONID = b.BIZTRANSACTIONID
INNER JOIN MBIZTRANSACTIONSUBCLASS bsc ON b.BIZTRANSACTIONSUBCLASSID = bsc.BIZTRANSACTIONSUBCLASSID
INNER JOIN MBIZTRANSACTIONCLASS bc ON bsc.BIZTRANSACTIONCLASSID = bc.BIZTRANSACTIONCLASSID
INNER JOIN MMODULE m ON bc.MODULEID = m.MODULEID
INNER JOIN MPERIOD p ON bt.PERIODID = p.PERIODID
LEFT JOIN MORGANIZATIONUNIT ou ON bt.OUID = ou.OUID
LEFT JOIN MORGANIZATIONGROUP og ON bt.ORGANIZATIONGROUPID = og.ORGANIZATIONGROUPID
LEFT JOIN MSTORE s ON bt.STOREID = s.STOREID
LEFT JOIN MPROCESS pc ON bt.PROCESSID = pc.PROCESSID
LEFT JOIN MWORKCENTER wc ON bt.WORKCENTERID = wc.WORKCENTERID
LEFT JOIN MPARTY py ON bt.PARTYID = py.PARTYID
LEFT JOIN MCOSTCENTER cc ON bt.SEGMENTID = cc.COSTCENTERID
LEFT JOIN MCOUNTER ctr ON bt.COUNTERID = ctr.COUNTERID
LEFT JOIN MCHARGE ac ON bt.ADDITIONALCHARGEID = ac.CHARGEID
LEFT JOIN MCHARGE oc ON bt.OTHERCHARGEID = oc.CHARGEID
LEFT JOIN MALLOCATIONTYPE at ON bt.ALLOCATIONTYPEID = at.ALLOCATIONTYPEID
LEFT JOIN MALLOCATION ma ON bt.MALLOCATIONID = ma.ALLOCATIONID
LEFT JOIN MACCOUNT fa ON bt.FIXEDACCOUNTID = fa.ACCOUNTID
LEFT JOIN MACCOUNT ca ON bt.COSTOFSALESACCOUNTID = ca.ACCOUNTID
LEFT JOIN MACCOUNT cca ON bt.CASHACCOUNTID = cca.ACCOUNTID
LEFT JOIN MBIZTRANSACTIONTYPE abt ON bt.ACCOUNTBIZTRANSACTIONTYPEID = abt.BIZTRANSACTIONTYPEID
LEFT JOIN MBIZTRANSACTIONCLASS sc ON bt.SELECTIONCLASSID = sc.BIZTRANSACTIONCLASSID
LEFT JOIN MBIZTRANSACTIONSUBCLASS ssc ON bt.SELECTIONSUBCLASSID = ssc.BIZTRANSACTIONSUBCLASSID
LEFT JOIN MBIZTRANSACTION sbt ON bt.SELECTIONBIZTRANSACTIONID = sbt.BIZTRANSACTIONID
LEFT JOIN MCURRENCY cur ON bt.CURRENCYID = cur.CURRENCYID
LEFT JOIN MLOTTYPE lt ON bt.LOTTYPEID = lt.LOTTYPEID
LEFT JOIN MREPORTFORMAT rf ON bt.REPORTFORMATID = rf.REPORTFORMATID
LEFT JOIN MCHARGE tc ON bt.TOTALCHARGEID = tc.CHARGEID
LEFT JOIN MBIZTRANSACTION ncr ON bt.NCRBIZTRANSACTIONID = ncr.BIZTRANSACTIONID
LEFT JOIN MCRITERIACONFIG icc ON bt.ITEMCRITERIACONFIGID = icc.CONFIGID
LEFT JOIN MEXCISEREGISTER ier ON bt.ITEMEXCISEREGISTERID = ier.REGISTERID
LEFT JOIN MEXCISEREGISTER her ON bt.HEADEXCISEREGISTERID = her.REGISTERID
WHERE 1 = 1";
#endregion
#region BIZTransactionType Based on Rights
///
/// Get BIZTransactionTypes filtered by user rights.
/// GB4 parity: GET_BIZTRANSACTIONTYPES_BASED_ON_RIGHTS
///
public const string GET_BIZTRANSACTIONTYPES_BY_RIGHTS = @"
SELECT
v.BIZTRANSACTIONTYPEID AS Id,
v.BIZTRANSACTIONTYPECODE AS Code,
v.BIZTRANSACTIONTYPENAME AS Name,
bt.SORTORDER AS SortOrder,
bt.BIZTRANSACTIONCLASSID AS BizTransactionClassId,
bt.STOREID AS StoreId,
ISNULL(s.STORENAME, '') AS StoreName
FROM MBIZTRANSACTIONTYPE bt
INNER JOIN MBIZTRANSACTIONCLASS bc ON bt.BIZTRANSACTIONCLASSID = bc.BIZTRANSACTIONCLASSID
INNER JOIN VBIZTRANSACTIONTYPE v ON bt.BIZTRANSACTIONTYPEID = v.BIZTRANSACTIONTYPEID
LEFT JOIN MADVANCERIGHTS ar ON v.BIZTRANSACTIONID = ar.BIZTRANSACTIONID
AND ar.USERID = @UserId
AND ar.OUID = @OUId
LEFT JOIN MSTORE s ON bt.STOREID = s.STOREID
WHERE bt.STATUS = 0
AND (ar.ALLOW NOT IN ('11111') OR ar.ALLOW IS NULL)
AND ((v.OUTYPE = 0 AND v.OUID = @OUId) OR v.OUTYPE = 1)";
#endregion
#region BIZTransaction Queries
///
/// Get BIZTransaction with module/class/subclass hierarchy — supports dynamic WHERE.
/// GB4 parity: GET_BIZTRANSACTION (HQL with 1=1)
///
public const string GET_BIZTRANSACTIONS_BASE = @"
SELECT
b.BIZTRANSACTIONID AS BIZTransactionId,
b.BIZTRANSACTIONCODE AS BIZTransactionCode,
b.BIZTRANSACTIONNAME AS BIZTransactionName,
bsc.BIZTRANSACTIONSUBCLASSID AS BIZTransactionSubClassId,
bsc.BIZTRANSACTIONSUBCLASSCODE AS BIZTransactionSubClassCode,
bsc.BIZTRANSACTIONSUBCLASSNAME AS BIZTransactionSubClassName,
bc.BIZTRANSACTIONCLASSID AS BIZTransactionClassId,
bc.BIZTRANSACTIONCLASSCODE AS BIZTransactionClassCode,
bc.BIZTRANSACTIONCLASSNAME AS BIZTransactionClassName,
m.MODULEID AS ModuleId,
m.MODULECODE AS ModuleCode,
m.MODULENAME AS ModuleName
FROM MBIZTRANSACTION b
INNER JOIN MBIZTRANSACTIONSUBCLASS bsc ON b.BIZTRANSACTIONSUBCLASSID = bsc.BIZTRANSACTIONSUBCLASSID
INNER JOIN MBIZTRANSACTIONCLASS bc ON bsc.BIZTRANSACTIONCLASSID = bc.BIZTRANSACTIONCLASSID
INNER JOIN MMODULE m ON bc.MODULEID = m.MODULEID
WHERE 1 = 1";
///
/// Get BIZTransactionType with Class and SubClass info.
/// GB4 parity: GET_BIZTRANSACTION_TYPE_CLASS_SUBCLASS (HQL)
///
public const string GET_BIZTRANSACTIONTYPE_CLASS_SUBCLASS = @"
SELECT
bt.BIZTRANSACTIONID AS BizTransactionId,
b.BIZTRANSACTIONCODE AS BizTransactionCode,
b.BIZTRANSACTIONNAME AS BizTransactionName,
bt.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
bt.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
bt.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
bsc.BIZTRANSACTIONSUBCLASSID AS BizTransactionSubClassId,
bsc.BIZTRANSACTIONSUBCLASSCODE AS BizTransactionSubClassCode,
bsc.BIZTRANSACTIONSUBCLASSNAME AS BizTransactionSubClassName,
bc.BIZTRANSACTIONCLASSID AS BizTransactionClassId,
bc.BIZTRANSACTIONCLASSCODE AS BizTransactionClassCode,
bc.BIZTRANSACTIONCLASSNAME AS BizTransactionClassName
FROM MBIZTRANSACTIONTYPE bt
INNER JOIN MBIZTRANSACTION b ON bt.BIZTRANSACTIONID = b.BIZTRANSACTIONID
INNER JOIN MBIZTRANSACTIONCLASS bc ON bt.BIZTRANSACTIONCLASSID = bc.BIZTRANSACTIONCLASSID
INNER JOIN MBIZTRANSACTIONSUBCLASS bsc ON bt.BIZTRANSACTIONSUBCLASSID = bsc.BIZTRANSACTIONSUBCLASSID
WHERE bt.BIZTRANSACTIONTYPEID = @BIZTransactionTypeId";
#endregion
#region BIZTransactionClass Report
///
/// Get BIZTransactionClass with report URI.
/// GB4 parity: GET_BIZTRANSACTIONCLASS_REPORT (HQL)
/// Note: Caller must replace {BaseUri} placeholder in result.
///
public const string GET_BIZTRANSACTIONCLASS_REPORT = @"
SELECT
bc.BIZTRANSACTIONCLASSID AS BIZTransactionClassId,
bc.BIZTRANSACTIONCLASSCODE AS BIZTransactionClassCode,
bc.BIZTRANSACTIONCLASSNAME AS BIZTransactionClassName,
REPLACE(r.URI, '{BaseUri}', @BaseUri) AS ReportUri
FROM MBIZTRANSACTIONCLASS bc
INNER JOIN MREPORT r ON bc.REPORTID = r.REPORTID
WHERE 1 = 1";
#endregion
#region Lock Settings Queries
///
/// Get OrganizationUnit lock date.
/// GB4 parity: GET_OVER_ALL_LOCK_DATE (HQL)
///
public const string GET_OVERALL_LOCK_DATE = @"
SELECT
OUID AS Id,
ORGANIZATIONUNITCODE AS Code,
ORGANIZATIONUNITNAME AS Name,
ISNULL(LOCKDATE, '1900-01-01') AS LockDate
FROM MORGANIZATIONUNIT
WHERE OUID = @OUId";
///
/// Get LockSetting by BIZTransaction and OU.
/// GB4 parity: GET_LOCKSETTING_BASED_ON_BIZTRANSACTIONCLASS_OU_ID (HQL)
///
public const string GET_LOCKSETTING_BY_BIZTRANSACTION_OU = @"
SELECT
LOCKSETTINGID AS Id,
BIZTRANSACTIONID AS BIZTransactionId,
OUID AS OUId,
ISNULL(LOCKFROM, '1900-01-01') AS LockFrom,
ISNULL(LOCKTO, '9999-12-31') AS LockTo
FROM MLOCKSETTING
WHERE BIZTRANSACTIONID = @BIZTransactionId
AND OUID = @OUId";
///
/// Get DayLock by Lock, OU, and Period.
/// GB4 parity: GET_DAYLOCK_BASED_ON_LOCK_OU_PERIOD_ID (HQL)
///
public const string GET_DAYLOCK_BY_LOCK_OU_PERIOD = @"
SELECT
DAYLOCKID AS Id,
LOCKID AS LockId,
OUID AS OUId,
PERIODID AS PeriodId,
ISNULL(LOCKDATE, '1900-01-01') AS LockDate
FROM MDAYLOCK
WHERE LOCKID = @LockId
AND OUID = @OUId
AND PERIODID = @PeriodId";
#endregion
#region Report Format Queries
///
/// Get report formats linked to a report.
/// GB4 parity: GET_REPORTFORMAT_NAME
///
public const string GET_REPORTFORMATS_BY_REPORT = @"
SELECT
rf.REPORTFORMATID AS Id,
rf.REPORTFILE AS Code,
rf.REPORTFORMATNAME AS Name
FROM MREPORTVSFORMATS rvf
INNER JOIN MREPORTFORMAT rf ON rvf.REPORTFORMATID = rf.REPORTFORMATID
WHERE rvf.REPORTID = @ReportId";
#endregion
#region Document Printing / Print Service
///
/// Get print service configuration for document printing.
/// GB4 parity: GET_DOCUMENTPRINTING_PRINT_SERVICE
///
public const string GET_DOCUMENT_PRINT_SERVICE = @"
SELECT
@FromDocumentNumber AS FromDocumentNumber,
@ToDocumentNumber AS ToDocumentNumber,
@FromDocumentId AS FromDocumentId,
@ToDocumentId AS ToDocumentId,
bt.BIZTRANSACTIONTYPEID AS BIZTransactionTypeId,
bt.BIZTRANSACTIONTYPECODE AS BIZTransactionTypeCode,
bt.BIZTRANSACTIONTYPENAME AS BIZTransactionTypeName,
bsc.BIZTRANSACTIONSUBCLASSID AS BIZTransactionSubClassId,
bsc.BIZTRANSACTIONSUBCLASSCODE AS BIZTransactionSubClassCode,
bsc.BIZTRANSACTIONSUBCLASSNAME AS BIZTransactionSubClassName,
bc.BIZTRANSACTIONCLASSID AS BIZTransactionClassId,
bc.BIZTRANSACTIONCLASSCODE AS BIZTransactionClassCode,
bc.BIZTRANSACTIONCLASSNAME AS BIZTransactionClassName,
bc.ENTITYID AS EntityId,
bc.REPORTID AS ReportId,
rf.REPORTFORMATID AS ReportFormatId,
rf.REPORTFORMATNAME AS ReportFormatName,
rf.REPORTFILE AS ReportFile,
rf.DATASOURCEFILENAME AS DataSourceFileName,
r.REPORTNAME AS ReportName,
r.WEBSERVICEID AS WebserviceId,
w.URITEMPLATE AS WebServiceURITemplate,
bc.MODULEID AS ModuleId
FROM MBIZTRANSACTIONTYPE bt
INNER JOIN MBIZTRANSACTIONSUBCLASS bsc ON bt.BIZTRANSACTIONSUBCLASSID = bsc.BIZTRANSACTIONSUBCLASSID
INNER JOIN MBIZTRANSACTIONCLASS bc ON bt.BIZTRANSACTIONCLASSID = bc.BIZTRANSACTIONCLASSID
INNER JOIN MREPORTFORMAT rf ON bt.REPORTFORMATID = rf.REPORTFORMATID
INNER JOIN MREPORT r ON bc.REPORTID = r.REPORTID
INNER JOIN MWEBSERVICE w ON r.WEBSERVICEID = w.WEBSERVICEID
WHERE bt.BIZTRANSACTIONTYPEID = @BIZTransactionTypeId";
#endregion
#region Special BIZTransactionType Lookups
///
/// Get WorkOrder BIZTransactionTypeId from OU Level Settings.
/// GB4 parity: GET_WORKORDER_BIZTRANSACTIONTYPE
///
public const string GET_WORKORDER_BIZTRANSACTIONTYPE = @"
SELECT
bt.BIZTRANSACTIONTYPEID AS BIZTransactionTypeId
FROM MBIZTRANSACTIONTYPE bt
INNER JOIN MOULEVELSETTING ols ON bt.BIZTRANSACTIONID = ols.WORKORDERBIZTRANSACTIONID
AND bt.OUID = ols.OUID
WHERE bt.PERIODID = @PeriodId
AND bt.OUID = @OUId";
///
/// Get SubContract BIZTransactionTypeId from OU Level Settings.
/// GB4 parity: GET_SUBCONTRACT_BIZTRANSACTIONTYPE
///
public const string GET_SUBCONTRACT_BIZTRANSACTIONTYPE = @"
SELECT
bt.BIZTRANSACTIONTYPEID AS BIZTransactionTypeId
FROM MBIZTRANSACTIONTYPE bt
INNER JOIN MOULEVELSETTING ols ON bt.BIZTRANSACTIONID = ols.SUBCONTRACTBIZTRANSACTIONID
AND bt.OUID = ols.OUID
WHERE bt.PERIODID = @PeriodId
AND bt.OUID = @OUId";
#endregion
#region Stock Account Posting Report
///
/// Get Stock Account Posting configuration report.
/// GB4 parity: GET_STOCKACCOUNTPOSTING_BIZTRANSACTIONTYPE_REPORT
///
public const string GET_STOCK_ACCOUNT_POSTING_REPORT = @"
SELECT
m.MODULENAME AS ModuleName,
bc.BIZTRANSACTIONCLASSNAME AS BIZTransactionClassName,
bt.BIZTRANSACTIONTYPEID AS BIZTransactionTypeId,
bt.BIZTRANSACTIONTYPECODE AS BIZTransactionTypeCode,
bt.BIZTRANSACTIONTYPENAME AS BIZTransactionTypeName,
CASE bt.STOCKACCOUNTPOSTING
WHEN 0 THEN 'None'
WHEN 1 THEN 'Per transaction'
WHEN 2 THEN 'Day basis'
WHEN 3 THEN 'Monthly'
ELSE '**Err***'
END AS StockAccountPosting,
st.BIZTRANSACTIONTYPECODE AS StockBIZTransactionTypeCode,
st.BIZTRANSACTIONTYPENAME AS StockBIZTransactionTypeName,
CASE bt.STOCKACCOUNTTYPE
WHEN 0 THEN 'Fixed'
WHEN 1 THEN 'Stock'
WHEN 2 THEN 'Cost'
WHEN 3 THEN 'Consumption'
WHEN 4 THEN 'WIP A/C'
WHEN 5 THEN 'WIP Stock'
WHEN 6 THEN 'Cogm'
ELSE '**Err***'
END AS StockAccountType,
fa.ACCOUNTCODE AS FixedStCode,
fa.ACCOUNTNAME AS FixedStName,
CASE bt.STOCKCONTRATYPE
WHEN 0 THEN 'Fixed'
WHEN 1 THEN 'Stock'
WHEN 2 THEN 'Cost'
WHEN 3 THEN 'Consumption'
WHEN 4 THEN 'WIP A/C'
WHEN 5 THEN 'WIP Stock'
WHEN 6 THEN 'Cogm'
ELSE '**Err***'
END AS ContraType,
ca.ACCOUNTCODE AS FixedContraCode,
ca.ACCOUNTNAME AS FixedContraName,
lc.ACCOUNTCODE AS LabourChargesCode,
lc.ACCOUNTNAME AS LabourChargesName,
lcp.ACCOUNTCODE AS LabourChargesPayableCode,
lcp.ACCOUNTNAME AS LabourChargesPayableName,
ISNULL(bt.ISSTOCKCC, 0) AS IsStockCC,
bt.STOCKCCID AS StockCCId,
scc.COSTCENTERCODE AS StockCCCode,
scc.COSTCENTERNAME AS StockCCName
FROM MBIZTRANSACTIONTYPE bt
INNER JOIN MBIZTRANSACTIONCLASS bc ON bt.BIZTRANSACTIONCLASSID = bc.BIZTRANSACTIONCLASSID
INNER JOIN MBIZTRANSACTIONTYPE st ON bt.STOCKACCOUNTBIZTRANSACTIONTYPEID = st.BIZTRANSACTIONTYPEID
INNER JOIN MMODULE m ON bc.MODULEID = m.MODULEID
LEFT JOIN MACCOUNT fa ON bt.STOCKFIXEDACCOUNTID = fa.ACCOUNTID
LEFT JOIN MACCOUNT ca ON bt.STOCKCONTRAACCOUNTID = ca.ACCOUNTID
LEFT JOIN MACCOUNT lc ON bt.LABOURCHARGESACCOUNTID = lc.ACCOUNTID
LEFT JOIN MACCOUNT lcp ON bt.LABOURCHARGESPAYABLEACCOUNTID = lcp.ACCOUNTID
LEFT JOIN MCOSTCENTER scc ON bt.STOCKCCID = scc.COSTCENTERID
WHERE bt.STOCKPOSTTYPE = 0
AND bt.PERIODID = @PeriodId";
#endregion
#region Stock Account Post Pending Selection Report
///
/// Get Stock Account Post Pending Selection configuration report.
/// GB4 parity: GET_STOCKACPOSTPENDINGSELECTIONBIZTRANSACTIONT_REPORT
///
public const string GET_STOCK_AC_POST_PENDING_SELECTION_REPORT = @"
SELECT
m.MODULECODE AS ModuleCode,
m.MODULENAME AS ModuleName,
bc.BIZTRANSACTIONCLASSCODE AS ClassCode,
bc.BIZTRANSACTIONCLASSNAME AS ClassName,
bsc.BIZTRANSACTIONSUBCLASSNAME AS BizTransactionSubClassName,
bsc.BIZTRANSACTIONSUBCLASSCODE AS BizTransactionSubClassCode,
bt.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
bt.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
bt.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
ISNULL(bt.PURPOSE, '') AS Purpose,
ou.OUID AS OUId,
ou.ORGANIZATIONUNITCODE AS OUCode,
ou.ORGANIZATIONUNITNAME AS OUName,
p.PERIODID AS PeriodId,
p.PERIODCODE AS PeriodCode,
p.PERIODNAME AS PeriodName,
CASE bt.SELECTIONFROMTYPE
WHEN 0 THEN 'Class'
WHEN 1 THEN 'SubClass'
WHEN 2 THEN 'BIZTransaction'
WHEN 3 THEN 'NONE'
ELSE '* Error *'
END AS Selection,
CASE bt.ISQUANTITYVARIANCE
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE '* Error *'
END AS Quantity,
CASE bt.ISQUALITYVARIANCE
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE '* Error *'
END AS Quality,
CASE bt.ISRATEVARIANCE
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE '* Error *'
END AS Rate,
CASE bt.ISSELECTIONREQUIRED
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE '* Error *'
END AS SelectionApplicable,
CASE bt.SELECTIONTYPE
WHEN 0 THEN 'Process'
WHEN 1 THEN 'Stock'
WHEN 2 THEN 'None'
ELSE '* Error *'
END AS SelectionType,
CASE bt.SELECTIONFROMTYPE
WHEN 0 THEN sc.BIZTRANSACTIONCLASSNAME
WHEN 1 THEN ssc.BIZTRANSACTIONSUBCLASSNAME
WHEN 2 THEN sbt.BIZTRANSACTIONNAME
ELSE '* Error *'
END AS SelectionFrom,
CASE bt.ISNONSELECTIONENTRY
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE '* Error *'
END AS NonSelection,
CASE bt.PENDINGFORSTOCK
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE '* Error *'
END AS PendStock,
CASE bt.PENDINGFORPROCESS
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE '* Error *'
END AS PendProcess,
STUFF((
SELECT DISTINCT ',' + adj.BIZTRANSACTIONTYPENAME
FROM MBIZTRANSACTIONTYPE adj
WHERE adj.OUID = bt.OUID
AND adj.SELECTIONTYPE = 0
AND (
(adj.SELECTIONBIZTRANSACTIONID = bt.BIZTRANSACTIONID AND adj.SELECTIONFROMTYPE = 2)
OR (adj.SELECTIONSUBCLASSID = bt.BIZTRANSACTIONSUBCLASSID AND adj.SELECTIONFROMTYPE = 1)
OR (adj.SELECTIONCLASSID = bt.BIZTRANSACTIONCLASSID AND adj.SELECTIONFROMTYPE = 0)
)
FOR XML PATH('')
), 1, 1, '') AS AdjustProcess,
STUFF((
SELECT DISTINCT ',' + adj.BIZTRANSACTIONTYPENAME
FROM MBIZTRANSACTIONTYPE adj
WHERE adj.OUID = bt.OUID
AND adj.SELECTIONTYPE = 1
AND (
(adj.SELECTIONBIZTRANSACTIONID = bt.BIZTRANSACTIONID AND adj.SELECTIONFROMTYPE = 2)
OR (adj.SELECTIONSUBCLASSID = bt.BIZTRANSACTIONSUBCLASSID AND adj.SELECTIONFROMTYPE = 1)
OR (adj.SELECTIONCLASSID = bt.BIZTRANSACTIONCLASSID AND adj.SELECTIONFROMTYPE = 0)
)
FOR XML PATH('')
), 1, 1, '') AS AdjustStock,
CASE bt.ISVARIANCEALLOWED
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE '* Error *'
END AS Variance,
CASE bc.STOCKPOSTTYPE
WHEN 0 THEN 'Inward'
WHEN 1 THEN 'Outward'
WHEN 2 THEN 'No post'
WHEN 3 THEN 'Inout'
WHEN 4 THEN 'InOutWaste'
ELSE '* Error *'
END AS ClassStockPostType,
CASE bc.STOCKVALUEAFFECTING
WHEN 0 THEN 'Affecting'
WHEN 1 THEN 'Costed'
WHEN 2 THEN 'None'
ELSE '* Error *'
END AS CostEffect,
CASE bc.INPUTPOSTTYPE
WHEN 0 THEN 'Add'
WHEN 1 THEN 'Less'
WHEN 2 THEN 'None'
ELSE '* Error *'
END AS InputPost,
CASE bc.OUTPUTPOSTTYPE
WHEN 0 THEN 'Add'
WHEN 1 THEN 'Less'
WHEN 2 THEN 'None'
ELSE '* Error *'
END AS OutputPost,
CASE bc.WASTEPOSTTYPE
WHEN 0 THEN 'Add'
WHEN 1 THEN 'Less'
WHEN 2 THEN 'None'
ELSE '* Error *'
END AS WastePost,
CASE bc.WIPPOSTTYPE
WHEN 0 THEN 'Add'
WHEN 1 THEN 'Less'
WHEN 2 THEN 'None'
ELSE '* Error *'
END AS WIPPost,
CASE bt.ISMOVEMENTTYPE
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE '* Error *'
END AS MovementType,
CASE bt.STOCKCHECKTYPE
WHEN 0 THEN 'Document'
WHEN 1 THEN 'Delivery'
ELSE '* Error *'
END AS StockCheck,
CASE bt.TRANSFERSTOCKPOSTTYPE
WHEN 0 THEN 'In'
WHEN 1 THEN 'Out'
WHEN 2 THEN 'In&Out'
WHEN 3 THEN 'None'
ELSE '* Error *'
END AS TransferPost,
CASE bt.ISSUETYPE
WHEN 0 THEN 'Production'
WHEN 1 THEN 'Capital'
WHEN 2 THEN 'Others'
WHEN 3 THEN 'None'
ELSE '* Error *'
END AS IssueType,
CASE bt.ACCOUNTPOSTTYPE
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
WHEN 2 THEN 'EOD'
ELSE '* Error *'
END AS AccountPost,
CASE bt.ACCOUNTPOSTBASIS
WHEN 0 THEN 'Mat.Acc'
WHEN 1 THEN 'Fixed Acc'
ELSE '* Error *'
END AS AccountBasis,
fa.ACCOUNTNAME AS AccountName,
CASE bt.BILLACCOUNTTYPE
WHEN 0 THEN 'NA'
WHEN 1 THEN 'As per Master'
WHEN 2 THEN 'NewBill'
WHEN 3 THEN 'Pending'
ELSE '* Error *'
END AS BillAllocation,
CASE bt.ISCASHCREDIT
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE '* Error *'
END AS CashCredit,
CASE bt.CASHBILLACCOUNTINGTYPE
WHEN 0 THEN 'Fixed A/c'
WHEN 1 THEN 'Party A/c'
WHEN 2 THEN 'Instrument A/c'
WHEN 3 THEN 'Cash Only'
ELSE '* Error *'
END AS CashBill,
cca.ACCOUNTNAME AS CashBillAccount,
CASE bt.COSTOFSALESPOSTINGTYPE
WHEN 0 THEN 'Material A/c'
WHEN 1 THEN 'Fixed'
ELSE '* Error *'
END AS MaterialAccountPost,
ca.ACCOUNTNAME AS MaterialAccount,
abt.BIZTRANSACTIONTYPENAME AS AccountBizTransactionTypeName
FROM MBIZTRANSACTIONTYPE bt
INNER JOIN MBIZTRANSACTIONCLASS bc ON bt.BIZTRANSACTIONCLASSID = bc.BIZTRANSACTIONCLASSID
INNER JOIN MBIZTRANSACTIONSUBCLASS bsc ON bt.BIZTRANSACTIONSUBCLASSID = bsc.BIZTRANSACTIONSUBCLASSID
INNER JOIN MMODULE m ON bc.MODULEID = m.MODULEID
INNER JOIN MORGANIZATIONUNIT ou ON bt.OUID = ou.OUID
INNER JOIN MPERIOD p ON bt.PERIODID = p.PERIODID
LEFT JOIN MACCOUNT fa ON bt.FIXEDACCOUNTID = fa.ACCOUNTID
LEFT JOIN MACCOUNT ca ON bt.COSTOFSALESACCOUNTID = ca.ACCOUNTID
LEFT JOIN MACCOUNT cca ON bt.CASHACCOUNTID = cca.ACCOUNTID
LEFT JOIN MBIZTRANSACTIONTYPE abt ON bt.ACCOUNTBIZTRANSACTIONTYPEID = abt.BIZTRANSACTIONTYPEID
LEFT JOIN MBIZTRANSACTIONCLASS sc ON bt.SELECTIONCLASSID = sc.BIZTRANSACTIONCLASSID
LEFT JOIN MBIZTRANSACTIONSUBCLASS ssc ON bt.SELECTIONSUBCLASSID = ssc.BIZTRANSACTIONSUBCLASSID
LEFT JOIN MBIZTRANSACTION sbt ON bt.SELECTIONBIZTRANSACTIONID = sbt.BIZTRANSACTIONID
WHERE bt.BIZTRANSACTIONTYPEID <> -1
AND bt.PERIODID = @PeriodId
AND bt.OUID = @OUId";
#endregion
}
}