ALTER PROCEDURE [SUN_SAGE2XML]

@EFFECTIVE_FROM DATETIME = '6 Dec 2015',
@EFFECTIVE_TO DATETIME = '10 Dec 2015',
@USERNAME VARCHAR(20) = 'ws_xproc',
@PASSWORD VARCHAR(20) = 'gUstath3Re' 

AS

SELECT DISTINCT

T.Reference 'INVOICE_NUMBER',

'<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://pnpportal.co.za/ServiceEanUcc" xmlns:stan="http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader">
  <soapenv:Header>
    <ser:Security>
      <ser:UsernameToken>
        <ser:Username>' + ISNULL(@USERNAME,'invpass') + '</ser:Username>
        <ser:Password>' + ISNULL(@PASSWORD,'test') + '</ser:Password>
      </ser:UsernameToken>
    </ser:Security>
  </soapenv:Header>
  <soapenv:Body>
<sh:StandardBusinessDocument xmlns:sh="http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader" xmlns:eanucc="urn:ean.ucc:2" xmlns:pay="urn:ean.ucc:pay:2" xmlns:xsi="http://www.w3.org/20
01/XMLSchema-instance" xsi:schemaLocation="urn:ean.ucc:2 ../Schemas/InvoiceProxy.xsd http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader ../Schemas/sbdh/StandardBusinessDocumentHeader.xsd">
  <sh:StandardBusinessDocumentHeader>
    <sh:HeaderVersion>1.0</sh:HeaderVersion> 
    <sh:Sender>
      <sh:Identifier Authority="EAN.UCC">' + '6009900156101' + '</sh:Identifier>
    </sh:Sender>
    <sh:Receiver>
      <sh:Identifier Authority="EAN.UCC">' + REPLACE(C.Post5,'GLN ','') + '</sh:Identifier>
    </sh:Receiver>
    <sh:DocumentIdentification>
      <sh:Standard>EAN.UCC</sh:Standard>
      <sh:TypeVersion>2.5</sh:TypeVersion>
      <sh:InstanceIdentifier>100002</sh:InstanceIdentifier>
      <sh:Type>INVOICE</sh:Type>
      <sh:MultipleType>false</sh:MultipleType>
      <sh:CreationDateAndTime>' + convert(varchar(50),getdate(),127) + '</sh:CreationDateAndTime>
    </sh:DocumentIdentification>
  </sh:StandardBusinessDocumentHeader>
  <eanucc:message>
    <entityIdentification>
      <uniqueCreatorIdentification>' + T.Reference + '</uniqueCreatorIdentification>
      <contentOwner>
        <gln>6009900156101</gln>
      </contentOwner>
    </entityIdentification>
  <eanucc:transaction>
    <entityIdentification>
      <uniqueCreatorIdentification>' + T.Reference + '</uniqueCreatorIdentification>
      <contentOwner>
        <gln>6009900156101</gln>
      </contentOwner>
    </entityIdentification>
  <command>' + '
    <eanucc:documentCommand>
      <documentCommandHeader type="ADD">
        <entityIdentification>
          <uniqueCreatorIdentification>' + T.Reference + '</uniqueCreatorIdentification>
          <contentOwner> 
            <gln>6009900156101</gln>
          </contentOwner>
        </entityIdentification>
      </documentCommandHeader>
      <documentCommandOperand> 
        <pay:invoice creationDateTime="' + convert(varchar(50),T.TxDate,127) + '" documentStatus="COPY"> 
          <contentVersion> 
            <versionIdentification>2.5</versionIdentification> 
          </contentVersion>
          <documentStructureVersion>
            <versionIdentification>2.5</versionIdentification>
          </documentStructureVersion>
          <invoiceIdentification>
            <uniqueCreatorIdentification>' + T.Reference + '</uniqueCreatorIdentification>
            <contentOwner>
              <gln>6009900156101</gln>
            </contentOwner>
          </invoiceIdentification>
          <invoiceCurrency>
            <currencyISOCode>ZAR</currencyISOCode>
          </invoiceCurrency>
          <invoiceType>INVOICE</invoiceType>
          <countryOfSupplyOfGoods>
            <countryISOCode>ZA</countryISOCode> 
          </countryOfSupplyOfGoods>
          <shipTo>
            <gln>' + REPLACE(C.Post5,'GLN ','') + '</gln>
          </shipTo>
          <buyer> 
            <partyIdentification>
              <gln>' + REPLACE(C.Post5,'GLN ','') + '</gln>
            </partyIdentification>
          </buyer>
          <seller>
            <partyIdentification>
              <gln>6009900156101</gln>
            </partyIdentification>
            <extension>
              <vat:vATInvoicePartyExtension xmlns:vat="urn:ean.ucc:pay:vat:2" xsi:schemaLocation="urn:ean.ucc:pay:vat:2 ../Schemas/Invoice_VATExtensionProxy.xsd">
                <vATRegistrationNumber>' + C.[Tax_Number] + '</vATRegistrationNumber>
			  </vat:vATInvoicePartyExtension>
            </extension>
          </seller>' + 
		  
		  (SELECT
          '<invoiceLineItem number="' + convert(varchar(10),ROW_NUMBER() OVER (ORDER BY P.id)) + '">
            <tradeItemIdentification>
              <gtin>' + Bar_Code + '</gtin>
              <additionalTradeItemIdentification>
                <additionalTradeItemIdentificationValue>' + Code + '</additionalTradeItemIdentificationValue>
                <additionalTradeItemIdentificationType>SUPPLIER_ASSIGNED</additionalTradeItemIdentificationType>
              </additionalTradeItemIdentification>
            </tradeItemIdentification>
            <invoicedQuantity>
              <value>' + convert(varchar(40),P.Quantity) + '</value>
              <unitOfMeasure>
                <measurementUnitCodeValue>EA</measurementUnitCodeValue>
              </unitOfMeasure>
            </invoicedQuantity>
            <transferOfOwnershipDate>' + convert(varchar(50),P.TxDate,127) + '</transferOfOwnershipDate> 
            <amountInclusiveAllowancesCharges>' + convert(varchar(20),ActualSalesValue) + '</amountInclusiveAllowancesCharges>
            <itemDescription>
              <text>' + LTRIM(RTRIM(S.Description_1)) + '</text>
            </itemDescription>
            <itemPriceInclusiveAllowancesCharges>' + convert(varchar(20),[ActualSalesValue]) + '</itemPriceInclusiveAllowancesCharges>
            <invoiceLineItemAfterTaxes>
              <amountInclusiveAllowancesCharges>' + convert(varchar(20),[ActualSalesValue] * 1.14) + '</amountInclusiveAllowancesCharges>
            </invoiceLineItemAfterTaxes>
            <invoiceLineTaxInformation>
              <dutyTaxFeeType>VALUE_ADDED_TAX</dutyTaxFeeType>
              <taxableAmount>' + convert(varchar(20),[ActualSalesValue]) + '</taxableAmount>
              <taxAmount>' + convert(varchar(20),([ActualSalesValue] * 1.14) - [ActualSalesValue]) + '</taxAmount>
              <extension>
                <vat:vATTaxInformationExtension xmlns:vat="urn:ean.ucc:pay:vat:2" xsi:schemaLocation="urn:ean.ucc:pay:vat:2 ../Schemas/Invoice_VATExtensionProxy.xsd">
                  <rate>14</rate>
                  <vATCategory>STANDARD_RATE</vATCategory>
                </vat:vATTaxInformationExtension>
              </extension>
            </invoiceLineTaxInformation>
            <extension>
              <invoicedWeight> 
                <value/>
                <unitOfMeasure>
                  <measurementUnitCodeValue/>
                </unitOfMeasure>
              </invoicedWeight> 
            </extension>
          </invoiceLineItem>'
		  FROM _bvSTTransactionsPivot P
		  inner join STKITEM S ON S.StockLink = P.StockLink
		  where P.Reference = T.Reference FOR XML PATH('')) +

        '<invoiceTotals>
          <totalInvoiceAmount>' + convert(varchar(50),Outstanding + Tax_Amount) + '</totalInvoiceAmount>
          <totalLineAmountInclusiveAllowancesCharges>' + convert(varchar(20),Outstanding) + '</totalLineAmountInclusiveAllowancesCharges>
          <totalTaxAmount>' + convert(varchar(20),Tax_Amount) + '</totalTaxAmount>
        </invoiceTotals>
        <orderIdentification>
          <uniqueCreatorIdentification>' + ExtOrderNum + '</uniqueCreatorIdentification>
          <contentOwner>
            <gln>' + REPLACE(C.Post5,'GLN ','') + '</gln>
          </contentOwner>
         </orderIdentification>
        </pay:invoice>
       </documentCommandOperand>
      </eanucc:documentCommand>
     </command>
    </eanucc:transaction>
   </eanucc:message>
  </sh:StandardBusinessDocument>
 </soapenv:Body>
</soapenv:Envelope>' 'XML_DATA'

from _bvARTransactionsFull T
inner join _bvARAccountsFull F ON F.DCLink = T.LinkAccCode
inner join CLIENT C ON C.DCLink = F.DCLink
where T.TxDate>=@EFFECTIVE_FROM
and T.TxDate<=@EFFECTIVE_TO
and T.TrCode = 'INV'
and AccountLink=6960