--UPDATE FROM 8.13.10.0 TO 8.13.10.1
--Views for B2G e-invoicing Germany

CREATE OR REPLACE VIEW V8_EEI_FOLIO_TOT
AS 
select
ZPIL_ZINV_ID_S ZINV_ID,
nvl(InvoiceGross,0) InvoiceGross,
nvl(InvoiceNet,0) InvoiceNet,
(nvl(InvoiceGross,0)-nvl(InvoiceNet,0)) InvoiceTaxTotal,
(select 
nvl(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) SPAY_CL  -- 16.11.2020 HHS-196 add field to calculate prepaid and payable Amount
 from 
 zpos,zpil 
 where 
 ZPIL.ZPIL_ZPOS_ID=ZPOS.ZPOS_ID 
 and
 ZPIL.ZPIL_ZINV_ID=ZPIL_ZINV_ID_S
and
(zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2)
SPAY_CL
from
(
SELECT
ZPIL_ZINV_ID ZPIL_ZINV_ID_S,
SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_grossunitprice*ZPOS.zpos_quantity, NULL), DECODE(ZPOS.ZPOS_CDT,5,0, zpos.zpos_grossunitprice*ZPOS.zpos_quantity))) InvoiceGross,
SUM(DECODE(ZPOS.ZPOS_CDT,5,(DECODE(ZPOS.ZPOS_PAIDOUT,1,(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY),0)),(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY))) InvoiceNet
FROM
ZPOS, ZPIL
WHERE
ZPOS_CDT<>2
and (zpos_zall_id IS NULL OR zpos_zall_id=0)
AND NVL(ZPOS.ZPOS_HIDDEN,0)=0
AND ZPIL_ZPOS_ID=ZPOS_ID
group by ZPIL_ZINV_ID
UNION ALL
SELECT
ZPI2_ZINV_ID ZPIL_ZINV_ID_S,
SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_grossunitprice*ZPOS.zpos_quantity, NULL), DECODE(ZPOS.ZPOS_CDT,5,0, zpos.zpos_grossunitprice*zpos.zpos_quantity))) InvoiceGross,
SUM(DECODE(ZPOS.ZPOS_CDT,5,(DECODE(ZPOS.ZPOS_PAIDOUT,1,(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY),0)),(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY))) InvoiceNet
FROM
ZPOS, ZPI2
WHERE
ZPOS_CDT<>2
and (zpos_zall_id IS NULL OR zpos_zall_id=0)
AND NVL(ZPOS.ZPOS_HIDDEN,0)=0
AND ZPI2_ZPOS_ID=ZPOS_ID
group by ZPI2_ZINV_ID
UNION ALL
SELECT
ZPI2_ZINV_ID ZPIL_ZINV_ID_S,
SUM(DECODE(zpo2.zpo2_cdt,5,DECODE(zpo2.zpo2_paidout,1, -zpo2.zpo2_grossunitprice*zpo2.zpo2_quantity, NULL), DECODE(zpo2.zpo2_CDT,5,0, zpo2.zpo2_grossunitprice*zpo2.zpo2_quantity))) InvoiceGross,
SUM(DECODE(zpo2.zpo2_CDT,5,(DECODE(zpo2.zpo2_PAIDOUT,1,(zpo2.zpo2_UNITPRICE*zpo2.zpo2_QUANTITY),0)),(zpo2.zpo2_UNITPRICE*zpo2.zpo2_QUANTITY))) InvoiceNet
FROM
ZPO2, ZPI2
WHERE
ZPO2_CDT<>2
and (zpo2_zall_id IS NULL OR zpo2_zall_id=0)
AND NVL(ZPO2.ZPO2_HIDDEN,0)=0
AND ZPI2_ZPO2_ID=ZPO2_ID
group by ZPI2_ZINV_ID
)
where INVOICEGROSS is not null and INVOICENET is not NULL;


CREATE OR REPLACE VIEW V8_LR_PEPPOL_XML_FOL_HEA
AS 
  select
to_char (2.1) UBLVersionID,            --The version of the  UBL standard applied for this messageIdentifies. The earliest version of the UBL 2 schema for this document type that defines all of the elements that might be encountered in the current instance.--
to_char (1.9) CustomizationID,        -- De version of the OHNL applied for this message-- Identifies a user-defined customization of UBL for a specific use.--
to_char ('NL')  ProfileID,            --Identifies a user-defined profile of the customization of UBL being used. In this case NL as xml is for the Netherlands. Need to see how to makes this flexible.
         -- above field suggests to put direclty in report as changes per country.
zinv_number ID,                       --Invoice Number. An identifier for this document, assigned by the sender.--
to_char (zinv_date, 'YYYY-MM-DD') IssueDate, -- Invoice Date The date, assigned by the sender, on which this document was issued.
to_char (ZINV.ZINV_PCDATE, 'HH:MM:SS') IssueTime, --The time, assigned by the sender, at which this document was issued.--
to_char(null) DueDate, --The date on which Invoice is due.         
          /* A code signifying the type of the Invoice.Need to investigate !!
            For netherlands onlyn 380, 38 4and 389 for Invoices Only  381 bij Credit Notes. Suggest to put direcly in report.*/  
CASE zinv_role WHEN 0 THEN 'INVOICE' WHEN 1 THEN 'DEPOSIT FOLIO' WHEN 2 THEN 'CITYLEDGER FOLIO' WHEN 3 THEN 'CREDIT NOTE' WHEN 31 THEN 'FAKTURE KOREKTA' WHEN 10 THEN 'FATTURA RIEPILOGATIVA' WHEN 4 THEN 'EGYPTIAN A/R INVOICE' WHEN 5 THEN 'AR RICEVUTA FISCALE' WHEN 6 THEN 'INFO/PROFORMA INVOICE' END InvoiceTypeCode, 
to_char(null) Note,  -- Optional: Free-form text pertinent to this document, conveying information that is not contained explicitly in other structures..-- 
to_char(null) Note2,
TO_CHAR(NULL) OrderReference, --In situations where the customer provides an order number, the order number should be provided in the invoice to allow matching. Peppol specs 
to_char (zinv_date, 'YYYY-MM-DD') TaxPointDate, --The date of the Invoice, used to indicate the point at which tax becomes applicable.Need to investigate
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) DocumentCurrencyCode, --A code signifying the default currency for this document.
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) TaxCurrencyCode, --A code signifying the currency used for tax amounts in the Invoice.--
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) PricingCurrencyCode, --A code signifying the currency used for prices in the Invoice.
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) PaymentCurrencyCode, --A code signifying the currency used for payment in the Invoice.
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) PaymentAlternativeCurrencyCode, --A code signifying the alternative currency used for payment in the Invoice.
to_char(null) StartDate,--TBD
to_char(null) EndDate, --TBD
to_char(null) AccountingCostCode,
to_char(null) BuyerReference,
to_char(null) OrderReferenceID,
to_char(null) SalesOrderID,
to_char(null) ProjectReferenceID,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='HotelCode') SupplierID, --KBO of supplier Unique number when registering at Kruispuntbank van ondernemingen. Belgium. Can be same as vat number but without BE prefix. Could be all UDEF_VAT TBD. 
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hotelid') SupplierName, --Took license code for this field.
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='HotelAddress') SupplierStreetName,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hotelcity') SupplierCityName,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hotelzipcode') SupplierPostalZone,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hotelcountry')  SupplierIdentificationCode,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='HotelTaxNumber') SupplierCompanyID,
'VAT' SupplierTaxSchemeID,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hotelid')  SupplierRegistrationName,
to_char(null) SupplierContactName,
to_char(null) SupplierContactTelephone,
to_char(null) SupplierContactElectronicMail,
(select xcms_type from xcms where xcms_id=zinv_xcms_id) xcms_type,
(select  xcms_name1 from xcms where xcms_id=zinv_xcms_id)  xcms_name1,
(select  xcms_name2 from xcms where xcms_id=zinv_xcms_id)  xcms_name2,
(select  xcms_name3 from xcms where xcms_id=zinv_xcms_id)  xcms_name3,
(select name from V8_EDITOR_NAM b where B.GUEST_ID=zinv_xcms_id) CustomerName,
(select xadr_street1 from xadr where xadr_id=zinv_xadr_id)  CustomerStreetName,
(select xadr_city from xadr where xadr_id=zinv_xadr_id)  CustomerCityName,
(select xadr_zip from xadr where xadr_id=zinv_xadr_id)  CustomerPostalZone,
to_char((select XMNR_VALUE from XMNR where XMNR_XMTY_ID = (select XMTY_ID from XMTY where XMTY_ROLE = 3 ) and XMNR_XCMS_ID = zwin_invoice_to_xcms_id)) CustomerIdentificationCode,
decode(zwin_zfac_id,null,(select name6 from v8_rep_name,yres where xcms_id=yres_xcms_id and yres_id=zwin_yres_id),(select name6 from v8_rep_name,zfac where xcms_id=zfac_xcms_id and zfac_id=zwin_zfac_id)) CustomerRegistrationName,
(select zdco_shortdesc from zdco, zpos Z1 where zdco_id=Z1.zpos_zdco_id and Z1.zpos_id=( select max(Z2.zpos_id) from zpos Z2,zpil I2 where Z2.zpos_id=I2.zpil_zpos_id and Z2.zpos_cdt=5 and I2.zpil_zinv_id=zinv_id)) PaymentMeansCode,
to_char(null) PayeeFinancialAccountID,
to_char(null) PaymentTerms,
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) LineExtensionAmountcurrencyID,
(select InvoiceNet from V8_EEI_FOLIO_TOT where V8_EEI_FOLIO_TOT.zinv_id=zinv.zinv_id) LineExtensionAmount,
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) TaxExclusiveAmountcurrencyID,
(select InvoiceNet from V8_EEI_FOLIO_TOT where V8_EEI_FOLIO_TOT.zinv_id=zinv.zinv_id) TaxExclusiveAmount,
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) TaxInclusiveAmountcurrencyID,
(select InvoiceGross from V8_EEI_FOLIO_TOT where V8_EEI_FOLIO_TOT.zinv_id=zinv.zinv_id) TaxInclusiveAmount,
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) PayableAmountcurrencyID,
(select InvoiceGross from V8_EEI_FOLIO_TOT where V8_EEI_FOLIO_TOT.zinv_id=zinv.zinv_id) PayableAmount,
zinv.*        
from
zinv,zwin
where
zinv_zwin_id=zwin_id;



create or replace view V8_LR_PEPPOL_XML_FOL_DET
as
select
zpil_zinv_id zinv_id,
--to_char(null)  InvoiceLineID,
1  InvoicedQuantity,
gross_amount LineExtensionAmount,
net_amount LineExtensionAmountNet,
tax_amount LineExtensionAmountVat,
zdco_numericdesc  ItemCode,
zpos_descript  ItemName,
to_char(null) ClassifiedTaxCategoryID,
to_char(null)  ClassifiedTaxCategoryPercent,
to_char(null) TaxSchemeID,
to_number(null)  PriceAmount
from 
v8_rep_zpos_gross_net,zpil
where
zpos_id=zpil_zpos_id;

create or replace view V8_LR_PEPPOL_XML_FOL_TAX
as
select
zinv_id zinv_id,
to_char(null) TaxAmountcurrencyIDTot,
to_number(null) TaxAmountTot,
to_char(null) TaxableAmountcurrencyID,
to_number(null) TaxableAmount,
to_char(null) TaxAmountcurrencyID,
to_number(null) TaxAmount,
to_char(null) TaxCategoryID,
to_char(null) TaxCategoryPercent,
to_char(null) TaxScheme
from 
zinv;

CREATE OR REPLACE VIEW V8_LR_DE_PEPPOL_XML_FOL_HEA
AS 
select
to_char (2.1) UBLVersionID,            --The version of the  UBL standard applied for this messageIdentifies. The earliest version of the UBL 2 schema for this document type that defines all of the elements that might be encountered in the current instance.--
to_char (1.9) CustomizationID,        -- De version of the OHNL applied for this message-- Identifies a user-defined customization of UBL for a specific use.--
to_char ('DE')  ProfileID,            --Identifies a user-defined profile of the customization of UBL being used. In this case NL as xml is for the Netherlands. Need to see how to makes this flexible.
         -- above field suggests to put direclty in report as changes per country.
zinv_number ID,                       --Invoice Number. An identifier for this document, assigned by the sender.--
to_char (zinv_date, 'YYYY-MM-DD') IssueDate, -- Invoice Date The date, assigned by the sender, on which this document was issued.
to_char (ZINV.ZINV_PCDATE, 'HH:MM:SS') IssueTime, --The time, assigned by the sender, at which this document was issued.--
to_char(zinv.zinv_date+to_number((select nvl(wuss_value,'0') from wuss where upper(wuss_name)='UDEF_XRECHNUNG_DUEDAYS_AFTER_INVOICE')),'YYYY-MM-DD')  DueDate, --The date on which Invoice is due. 
          /* A code signifying the type of the Invoice.Need to investigate !!
            For netherlands onlyn 380, 38 4and 389 for Invoices Only  381 bij Credit Notes. Suggest to put direcly in report.*/  
CASE zinv_role WHEN 0 THEN 'INVOICE' WHEN 1 THEN 'DEPOSIT FOLIO' WHEN 2 THEN 'CITYLEDGER FOLIO' WHEN 3 THEN 'CREDIT NOTE' WHEN 31 THEN 'FAKTURE KOREKTA' WHEN 10 THEN 'FATTURA RIEPILOGATIVA' WHEN 4 THEN 'EGYPTIAN A/R INVOICE' WHEN 5 THEN 'AR RICEVUTA FISCALE' WHEN 6 THEN 'INFO/PROFORMA INVOICE' END InvoiceTypeCode, 
zwin_freetext1 Note,  -- Optional: Free-form text pertinent to this document, conveying information that is not contained explicitly in other structures..-- 
zwin_freetext2 Note2,
TO_CHAR(NULL) OrderReference, --In situations where the customer provides an order number, the order number should be provided in the invoice to allow matching. Peppol specs 
to_char (zinv_date, 'YYYY-MM-DD') TaxPointDate, --The date of the Invoice, used to indicate the point at which tax becomes applicable.Need to investigate
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) DocumentCurrencyCode, --A code signifying the default currency for this document.
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) TaxCurrencyCode, --A code signifying the currency used for tax amounts in the Invoice.--
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) PricingCurrencyCode, --A code signifying the currency used for prices in the Invoice.
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) PaymentCurrencyCode, --A code signifying the currency used for payment in the Invoice.
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) PaymentAlternativeCurrencyCode, --A code signifying the alternative currency used for payment in the Invoice.
 to_char((DECODE(NVL(ZWIN.ZWIN_ZFAC_ID,0),0
    ,(SELECT TRUNC(YRES.YRES_EXPARRTIME) FROM YRES WHERE YRES.YRES_ID=ZWIN.ZWIN_YRES_ID)
        ,DECODE((SELECT YRES.YRES_ID FROM YRES WHERE YRES.YRES_NOTCLOSED_ZFAC_ID=ZWIN.ZWIN_ZFAC_ID),NULL
               ,(SELECT TRUNC(ZFAC.ZFAC_EXPVALIDFROM) FROM ZFAC WHERE ZFAC.ZFAC_ID=ZWIN.ZWIN_ZFAC_ID)
               ,(SELECT TRUNC(YRES.YRES_EXPARRTIME) FROM YRES WHERE YRES.YRES_ID=(SELECT YRES.YRES_ID FROM YRES WHERE YRES.YRES_NOTCLOSED_ZFAC_ID=ZWIN.ZWIN_ZFAC_ID))            
  ))),'YYYY-MM-DD')  StartDate,--TBD
 to_char((DECODE(NVL(ZWIN.ZWIN_ZFAC_ID,0),0
    ,(SELECT TRUNC(YRES.YRES_EXPDEPTIME) FROM YRES WHERE YRES.YRES_ID=ZWIN.ZWIN_YRES_ID)
        ,DECODE((SELECT YRES.YRES_ID FROM YRES WHERE YRES.YRES_NOTCLOSED_ZFAC_ID=ZWIN.ZWIN_ZFAC_ID),NULL
               ,(SELECT TRUNC(ZFAC.ZFAC_EXPVALIDUNTIL) FROM ZFAC WHERE ZFAC.ZFAC_ID=ZWIN.ZWIN_ZFAC_ID)
               ,(SELECT TRUNC(YRES.YRES_EXPDEPTIME) FROM YRES WHERE YRES.YRES_ID=(SELECT YRES.YRES_ID FROM YRES WHERE YRES.YRES_NOTCLOSED_ZFAC_ID=ZWIN.ZWIN_ZFAC_ID))            
  ))),'YYYY-MM-DD') EndDate, --TBD
to_char(null) AccountingCostCode,
(select xmnr_value from xmnr,xmty where xmnr_xmty_id=xmty_id and xmty_role=3 and xmnr_xcms_id=zwin_invoice_to_xcms_id) BuyerReference,
to_char(null) OrderReferenceID,
to_char(null) SalesOrderID,
to_char(null) ProjectReferenceID,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='HotelCode') SupplierID, --KBO of supplier Unique number when registering at Kruispuntbank van ondernemingen. Belgium. Can be same as vat number but without BE prefix. Could be all UDEF_VAT TBD. 
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hotelid') SupplierName, --Took license code for this field.
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='HotelAddress') SupplierStreetName,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hotelcity') SupplierCityName,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hotelzipcode') SupplierPostalZone,
--(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hotelcountry')  SupplierIdentificationCode,
(select XCOU.XCOU_ISO2 from xcou where upper(XCOU.XCOU_LONGDESC)=(select upper(wuss_value) from wuss where wuss_xcms_id=0 and wuss_name='Hotelcountry')) SupplierIdentificationCode,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='HotelTaxNumber') SupplierCompanyID,
'VAT' SupplierTaxSchemeID,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hotelid')  SupplierRegistrationName,
(select wuss_value from wuss where upper(wuss_name)='UDEF_XRECHNUNG_RESPONSIBLE_NAME')  SupplierContactName, 
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hoteltel') SupplierContactTelephone,
(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='Hotelemail') SupplierContactElectronicMail,
(select xcms_type from xcms where xcms_id=zinv_xcms_id) xcms_type,
(select  xcms_name1 from xcms where xcms_id=zinv_xcms_id)  xcms_name1,
(select  xcms_name2 from xcms where xcms_id=zinv_xcms_id)  xcms_name2,
(select  xcms_name3 from xcms where xcms_id=zinv_xcms_id)  xcms_name3,
(select name from V8_EDITOR_NAM b where B.GUEST_ID=zinv_xcms_id) CustomerName,
(select xadr_street1 from xadr where xadr_id=zinv_xadr_id)  CustomerStreetName,
(select xadr_city from xadr where xadr_id=zinv_xadr_id)  CustomerCityName,
(select xadr_zip from xadr where xadr_id=zinv_xadr_id)  CustomerPostalZone,
(select XCOU.XCOU_ISO2 from xadr,xcou where XADR.XADR_XCOU_ID=XCOU.XCOU_ID and  xadr_id=zinv_xadr_id) CustomerIdentificationCode,
decode(zwin_zfac_id,null,(select name6 from v8_rep_name,yres where xcms_id=yres_xcms_id and yres_id=zwin_yres_id),(select name6 from v8_rep_name,zfac where xcms_id=zfac_xcms_id and zfac_id=zwin_zfac_id)) CustomerRegistrationName,
(select zdco_shortdesc from zdco, zpos Z1 where zdco_id=Z1.zpos_zdco_id and Z1.zpos_id=( select max(Z2.zpos_id) from zpos Z2,zpil I2 where Z2.zpos_id=I2.zpil_zpos_id and Z2.zpos_cdt=5 and I2.zpil_zinv_id=zinv_id)) PaymentMeansCode,
(select wuss_value from wuss where wuss_name='HotelbankIBAN') PayeeFinancialAccountID,
(select wuss_value from wuss where wuss_name='Hotelid') PayeeFinancialAccountName,
(select wuss_value from wuss where wuss_name='HotelbankBIC') PayeeFinancialAccountBIC,
to_char(null) PaymentTerms,
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) LineExtensionAmountcurrencyID,
to_char(round((select InvoiceNet from V8_EEI_FOLIO_TOT where V8_EEI_FOLIO_TOT.zinv_id=zinv.zinv_id),2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') LineExtensionAmount,
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) TaxExclusiveAmountcurrencyID,
to_char(round((select InvoiceNet from V8_EEI_FOLIO_TOT where V8_EEI_FOLIO_TOT.zinv_id=zinv.zinv_id),2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') TaxExclusiveAmount,
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) TaxInclusiveAmountcurrencyID,
to_char(round((select InvoiceGross from V8_EEI_FOLIO_TOT where V8_EEI_FOLIO_TOT.zinv_id=zinv.zinv_id),2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') TaxInclusiveAmount,
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) PayableAmountcurrencyID,
to_char(round((select SPAY_CL from V8_EEI_FOLIO_TOT where V8_EEI_FOLIO_TOT.zinv_id=zinv.zinv_id),2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') PayableAmount, 
(select zcur_iso3 from zcur, wuss where wuss_name = 'BaseCurrency' and wuss.wuss_value = zcur_id) PrepaidAmountcurrencyID, 
to_char(round((select (InvoiceGross-SPAY_CL) from V8_EEI_FOLIO_TOT where V8_EEI_FOLIO_TOT.zinv_id=zinv.zinv_id),2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') PrepaidAmount,
zinv.* 
from
zinv,zwin
where
zinv_zwin_id=zwin_id;



create or replace view V8_LR_DE_PEPPOL_XML_FOL_DET
as
select
zpil_zinv_id zinv_id,
--to_char(null)  InvoiceLineID,
1  InvoicedQuantity,
to_char(round(GNV.gross_amount,2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') LineExtensionAmount,
to_char(round(GNV.net_amount,2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') LineExtensionAmountNet,
to_char(round(GNV.tax_amount,2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') LineExtensionAmountVat,
GNV.zdco_numericdesc  ItemCode,
GNV.zpos_descript  ItemName,
decode(GNV.tax_amount,0,'Z','S') ClassifiedTaxCategoryID,
--(select ztcd.ztcd_name from zpos Z2,ztcd where Z2.zpos_taxlink_id=zpos.zpos_taxlink_id and Z2.zpos_cdt=2 and Z2.zpos_ztcd_id=ztcd.ztcd_id) ClassifiedTaxCategoryID,
ltrim((select decode(instr(ztcd_udf,'.',instr(ztcd_udf,'*',1,1),1),0,null,substr(ztcd_udf,instr(ztcd_udf,'.',instr(ztcd_udf,'*',1,1),1)+1,2)) from zpos Z2,ztcd where Z2.zpos_taxlink_id=zpos.zpos_taxlink_id and Z2.zpos_cdt=2 and Z2.zpos_ztcd_id=ztcd.ztcd_id),0)  ClassifiedTaxCategoryPercent,
'VAT' TaxSchemeID,
--(select ztxc.ztxc_einvoice_vatcode from zpos Z2,ztcd, ztxc where Z2.zpos_taxlink_id=zpos.zpos_taxlink_id and Z2.zpos_cdt=2 and Z2.zpos_ztcd_id=ztcd.ztcd_id and ztcd.ztcd_ztxc_id=ztxc.ztxc_id) TaxSchemeID,
to_char(round(GNV.net_amount,2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') PriceAmount
from 
v8_rep_zpos_gross_net GNV,zpil,zpos
where
GNV.zpos_id=zpil_zpos_id
and GNV.zpos_id=zpos.zpos_id
and gnv.zpos_cdt=1;

create or replace view V8_LR_DE_PEPPOL_XML_FOL_TAX
as
select
ZINV_ID_S ZINV_ID,
(select zcur_iso3 from zcur where zcur_id=(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='BaseCurrency')) TaxAmountcurrencyIDTot,
to_char(round((select sum(taxv2.tax_amount) from V8_REP_ZPOS_TAX taxv2,zpil zpil2 where zpil2.zpil_zinv_id=ZINV_ID_S and taxv2.zpos_id=zpil2.zpil_zpos_id),2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') TaxAmountTot,
(select zcur_iso3 from zcur where zcur_id=(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='BaseCurrency')) TaxableAmountcurrencyID,
to_char(round(NET_AMOUNT_S,2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') TaxableAmount,
(select zcur_iso3 from zcur where zcur_id=(select wuss_value from wuss where wuss_xcms_id=0 and wuss_name='BaseCurrency')) TaxAmountcurrencyID,
to_char(round(TAX_AMOUNT_S,2),'999999990D99','NLS_NUMERIC_CHARACTERS=''.,''') TaxAmount,
decode(TAX_AMOUNT_S,0,'Z','S') TaxCategoryID,
ltrim(decode(instr(ztcd_udf,'.',instr(ztcd_udf,'*',1,1),1),0,null,substr(ztcd_udf,instr(ztcd_udf,'.',instr(ztcd_udf,'*',1,1),1)+1,2)),0) TaxCategoryPercent,
--(select ztxc.ztxc_einvoice_vatcode from ztxc where ztxc.ztxc_id=ztcd_ztxc_id) TaxScheme,
'VAT' TaxScheme,
ztcd.*
from
(
select 
zpil.zpil_zinv_id ZINV_ID_S,
zpos.zpos_ztcd_id ZTCD_ID_S,
sum(taxv.net_amount) NET_AMOUNT_S,
sum(taxv.tax_amount) TAX_AMOUNT_S
from 
V8_REP_ZPOS_TAX TAXV,zpil,zpos
where
TAXV.zpos_id=zpil_zpos_id
and TAXV.zpos_id=zpos.zpos_id
group by zpil_zinv_id,zpos.zpos_ztcd_id
) TV, ztcd
where
TV.ZTCD_ID_S=ztcd.ztcd_id;

CREATE OR REPLACE FORCE VIEW V8_EEI_PEPPOL_XML_FOL_HEA
as
select * from V8_LR_PEPPOL_XML_FOL_HEA;

