singularity/CreateOrUpdateInvoice.sql at main · shuxton/singularity · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
CREATE or Alter PROCEDURE CreateOrUpdateInvoice
    @TenantId INT,
    @UniqueIdentifier UNIQUEIDENTIFIER,
    @CreatedDate DATETIME,
    @ModifiedDate DATETIME,
    @IrnNo BIGINT = NULL,
    @InvoiceNumber NVARCHAR(50),
    @IssueDate DATETIME,
    @DateOfSupply DATETIME = NULL,
    @InvoiceCurrencyCode NVARCHAR(10) = NULL,
    @CurrencyCodeOriginatingCountry NVARCHAR(10) = NULL,
    @PurchaseOrderId NVARCHAR(50) = NULL,
    @BillingReferenceId NVARCHAR(50) = NULL,
    @ContractId NVARCHAR(50) = NULL,
    @LatestDeliveryDate DATE = NULL,
    @Location NVARCHAR(255) = NULL,
    @CustomerId NVARCHAR(50) = NULL,
    @Status NVARCHAR(50) = NULL,
    @PaymentType NVARCHAR(50) = NULL,
    @IsArchived BIT = NULL,
    @TransTypeCode NVARCHAR(50) = NULL,
    @TransTypeDescription NVARCHAR(255) = NULL,
    @AdvanceReferenceNumber NVARCHAR(50) = NULL,
    @InvoiceTransactioncode NVARCHAR(50) = NULL,
    @BusinessProcessType NVARCHAR(50) = NULL,
    @InvoiceNotes NVARCHAR(MAX) = NULL,
    @XmlUuid NVARCHAR(50) = NULL,
    @InvoiceTypeCode NVARCHAR(50),
    @Language NVARCHAR(10) = NULL,
    @Source NVARCHAR(50) = NULL,
    @AccountName NVARCHAR(255) = NULL,
    @AccountNumber NVARCHAR(50) = NULL,
    @Iban NVARCHAR(50) = NULL,
    @BankName NVARCHAR(100) = NULL,
    @SwiftCode NVARCHAR(50) = NULL,
    @BranchName NVARCHAR(255) = NULL,
    @BranchAddress NVARCHAR(MAX) = NULL
AS
BEGIN
    DECLARE @TableName NVARCHAR(50);

    -- Determine the table to use based on InvoiceTypeCode
    IF @InvoiceTypeCode = 'Sales'
        SET @TableName = 'SalesInvoice';
    ELSE IF @InvoiceTypeCode = 'Credit'
        SET @TableName = 'CreditNote';
    ELSE IF @InvoiceTypeCode = 'Debit'
        SET @TableName = 'DebitNote';
    ELSE
        RAISERROR('Invalid InvoiceTypeCode', 16, 1);

    -- Check if an invoice with the given UniqueIdentifier already exists
   IF EXISTS (SELECT 1 FROM dbo.Invoice WHERE UniqueIdentifier = @UniqueIdentifier)
    BEGIN
        -- Update the master invoice table
        UPDATE dbo.Invoice
        SET InvoiceType = @InvoiceType,
            Status = @Status,
            ModifiedDate = @ModifiedDate
        WHERE UniqueIdentifier = @UniqueIdentifier;
        -- Update the existing invoice
        DECLARE @Sql NVARCHAR(MAX) = '
            UPDATE dbo.' + @TableName + '
            SET TenantId = @TenantId,
                CreatedDate = @CreatedDate,
                ModifiedDate = @ModifiedDate,
                IrnNo = @IrnNo,
                InvoiceNumber = @InvoiceNumber,
                IssueDate = @IssueDate,
                DateOfSupply = @DateOfSupply,
                InvoiceCurrencyCode = @InvoiceCurrencyCode,
                CurrencyCodeOriginatingCountry = @CurrencyCodeOriginatingCountry,
                PurchaseOrderId = @PurchaseOrderId,
                BillingReferenceId = @BillingReferenceId,
                ContractId = @ContractId,
                LatestDeliveryDate = @LatestDeliveryDate,
                Location = @Location,
                CustomerId = @CustomerId,
                Status = @Status,
                PaymentType = @PaymentType,
                IsArchived = @IsArchived,
                TransTypeCode = @TransTypeCode,
                TransTypeDescription = @TransTypeDescription,
                AdvanceReferenceNumber = @AdvanceReferenceNumber,
                InvoiceTransactioncode = @InvoiceTransactioncode,
                BusinessProcessType = @BusinessProcessType,
                InvoiceNotes = @InvoiceNotes,
                XmlUuid = @XmlUuid,
                InvoiceTypeCode = @InvoiceTypeCode,
                Language = @Language,
                Source = @Source,
                AccountName = @AccountName,
                AccountNumber = @AccountNumber,
                Iban = @Iban,
                BankName = @BankName,
                SwiftCode = @SwiftCode,
                BranchName = @BranchName,
                BranchAddress = @BranchAddress
            WHERE UniqueIdentifier = @UniqueIdentifier';

        EXEC sp_executesql @Sql,
            N'@TenantId INT, @UniqueIdentifier UNIQUEIDENTIFIER, @CreatedDate DATETIME, @ModifiedDate DATETIME, @IrnNo BIGINT, @InvoiceNumber NVARCHAR(50), @IssueDate DATETIME, @DateOfSupply DATETIME, @InvoiceCurrencyCode NVARCHAR(10), @CurrencyCodeOriginatingCountry NVARCHAR(10), @PurchaseOrderId NVARCHAR(50), @BillingReferenceId NVARCHAR(50), @ContractId NVARCHAR(50), @LatestDeliveryDate DATE, @Location NVARCHAR(255), @CustomerId NVARCHAR(50), @Status NVARCHAR(50), @PaymentType NVARCHAR(50), @IsArchived BIT, @TransTypeCode NVARCHAR(50), @TransTypeDescription NVARCHAR(255), @AdvanceReferenceNumber NVARCHAR(50), @InvoiceTransactioncode NVARCHAR(50), @BusinessProcessType NVARCHAR(50), @InvoiceNotes NVARCHAR(MAX), @XmlUuid NVARCHAR(50), @InvoiceTypeCode NVARCHAR(50), @Language NVARCHAR(10), @Source NVARCHAR(50), @AccountName NVARCHAR(255), @AccountNumber NVARCHAR(50), @Iban NVARCHAR(50), @BankName NVARCHAR(100), @SwiftCode NVARCHAR(50), @BranchName NVARCHAR(255), @BranchAddress NVARCHAR(MAX)',
            @TenantId, @UniqueIdentifier, @CreatedDate, @ModifiedDate, @IrnNo, @InvoiceNumber, @IssueDate, @DateOfSupply, @InvoiceCurrencyCode, @CurrencyCodeOriginatingCountry, @PurchaseOrderId, @BillingReferenceId, @ContractId, @LatestDeliveryDate, @Location, @CustomerId, @Status, @PaymentType, @IsArchived, @TransTypeCode, @TransTypeDescription, @AdvanceReferenceNumber, @InvoiceTransactioncode, @BusinessProcessType, @InvoiceNotes, @XmlUuid, @InvoiceTypeCode, @Language, @Source, @AccountName, @AccountNumber, @Iban, @BankName, @SwiftCode, @BranchName, @BranchAddress;
    END
    ELSE
    BEGIN

    INSERT INTO dbo.Invoice (
            UniqueIdentifier, InvoiceType, Status, CreatedDate, ModifiedDate
        ) VALUES (
            @UniqueIdentifier, @InvoiceType, @Status, @CreatedDate, @ModifiedDate
        );
        -- Insert a new invoice
        DECLARE @InsertSql NVARCHAR(MAX) = '
            INSERT INTO dbo.' + @TableName + ' (
                TenantId, UniqueIdentifier, CreatedDate, ModifiedDate, IrnNo, InvoiceNumber, IssueDate, DateOfSupply, InvoiceCurrencyCode, CurrencyCodeOriginatingCountry, PurchaseOrderId, BillingReferenceId, ContractId, LatestDeliveryDate, Location, CustomerId, Status, PaymentType, IsArchived, TransTypeCode, TransTypeDescription, AdvanceReferenceNumber, InvoiceTransactioncode, BusinessProcessType, InvoiceNotes, XmlUuid, InvoiceTypeCode, Language, Source, AccountName, AccountNumber, Iban, BankName, SwiftCode, BranchName, BranchAddress
            ) VALUES (
                @TenantId, @UniqueIdentifier, @CreatedDate, @ModifiedDate, @IrnNo, @InvoiceNumber, @IssueDate, @DateOfSupply, @InvoiceCurrencyCode, @CurrencyCodeOriginatingCountry, @PurchaseOrderId, @BillingReferenceId, @ContractId, @LatestDeliveryDate, @Location, @CustomerId, @Status, @PaymentType, @IsArchived, @TransTypeCode, @TransTypeDescription, @AdvanceReferenceNumber, @InvoiceTransactioncode, @BusinessProcessType, @InvoiceNotes, @XmlUuid, @InvoiceTypeCode, @Language, @Source, @AccountName, @AccountNumber, @Iban, @BankName, @SwiftCode, @BranchName, @BranchAddress)';

        EXEC sp_executesql @InsertSql,
            N'@TenantId INT, @UniqueIdentifier UNIQUEIDENTIFIER, @CreatedDate DATETIME, @ModifiedDate DATETIME, @IrnNo BIGINT, @InvoiceNumber NVARCHAR(50), @IssueDate DATETIME, @DateOfSupply DATETIME, @InvoiceCurrencyCode NVARCHAR(10), @CurrencyCodeOriginatingCountry NVARCHAR(10), @PurchaseOrderId NVARCHAR(50), @BillingReferenceId NVARCHAR(50), @ContractId NVARCHAR(50), @LatestDeliveryDate DATE, @Location NVARCHAR(255), @CustomerId NVARCHAR(50), @Status NVARCHAR(50), @PaymentType NVARCHAR(50), @IsArchived BIT, @TransTypeCode NVARCHAR(50), @TransTypeDescription NVARCHAR(255), @AdvanceReferenceNumber NVARCHAR(50), @InvoiceTransactioncode NVARCHAR(50), @BusinessProcessType NVARCHAR(50), @InvoiceNotes NVARCHAR(MAX), @XmlUuid NVARCHAR(50), @InvoiceTypeCode NVARCHAR(50), @Language NVARCHAR(10), @Source NVARCHAR(50), @AccountName NVARCHAR(255), @AccountNumber NVARCHAR(50), @Iban NVARCHAR(50), @BankName NVARCHAR(100), @SwiftCode NVARCHAR(50), @BranchName NVARCHAR(255), @BranchAddress NVARCHAR(MAX)',
            @TenantId, @UniqueIdentifier, @CreatedDate, @ModifiedDate, @IrnNo, @InvoiceNumber, @IssueDate, @DateOfSupply, @InvoiceCurrencyCode, @CurrencyCodeOriginatingCountry, @PurchaseOrderId, @BillingReferenceId, @ContractId, @LatestDeliveryDate, @Location, @CustomerId, @Status, @PaymentType, @IsArchived, @TransTypeCode, @TransTypeDescription, @AdvanceReferenceNumber, @InvoiceTransactioncode, @BusinessProcessType, @InvoiceNotes, @XmlUuid, @InvoiceTypeCode, @Language, @Source, @AccountName, @AccountNumber, @Iban, @BankName, @SwiftCode, @BranchName, @BranchAddress;
END
END