--UPDATE FROM 8.13.8.1 TO 8.13.9.0

--Run only for hotels in Poland.
--Views for Poland 9009 JPK Invoice Report
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION Format_Inv_Number (InvNumber   IN VARCHAR2,
                                              InvDate     IN DATE,
                                              PFormat     IN VARCHAR2)
   RETURN VARCHAR2
IS
   TPrefix   VARCHAR2 (50);
   TSuffix   VARCHAR2 (50);
   TNumber   NUMBER;
BEGIN
   TNumber := INSTR (UPPER (PFormat), 'X');
   TPrefix := SUBSTR (PFormat, 1, TNumber - 1);
   TSuffix := SUBSTR (PFormat, TNumber + 1);
   
   IF REGEXP_SUBSTR (TPrefix, '''([[:alnum:]]+\.?+\-?+\/?){2,12}''?')
         IS NOT NULL
   THEN
      BEGIN
         TPrefix :=
            REGEXP_REPLACE (
               TPrefix,
               '''([[:alnum:]]+\.?+\-?+\/?){2,12}''?',
               TO_CHAR (
                  InvDate,
                  REPLACE (
                     REGEXP_SUBSTR (TPrefix,
                                    '''([[:alnum:]]+\.?+\-?+\/?){2,12}''?'),
                     CHR (39))));
      END;
   END IF;

   IF REGEXP_SUBSTR (TSuffix, '''([[:alnum:]]+\.?+\-?+\/?){2,12}''?')
         IS NOT NULL
   THEN
      BEGIN
         TSuffix :=
            REGEXP_REPLACE (
               TSuffix,
               '''([[:alnum:]]+\.?+\-?+\/?){2,12}''?',
               TO_CHAR (
                  InvDate,
                  REPLACE (
                     REGEXP_SUBSTR (TSuffix,
                                    '''([[:alnum:]]+\.?+\-?+\/?){2,12}''?'),
                     CHR (39))));
      END;
   END IF;

   RETURN (TPrefix || TO_CHAR (InvNumber) || TSuffix);
END;
/

CREATE OR REPLACE FUNCTION getGlobalSettingForExp(GSName in varchar2)
RETURN varchar2 IS
    RetVal varchar2(60 char);
BEGIN
    select CONVERT(wuss_value, 'UTF8') into RetVal from wuss where wuss_name = GsName;
    return RetVal;
EXCEPTION
    WHEN OTHERS THEN
        return '';
END;
/

CREATE OR REPLACE FUNCTION getPolishFullHotelAddress
RETURN varchar2 IS
    RetVal varchar2(600 char);
BEGIN
    return 
        getPolishGS('Ulica') || ' ' || 
        getPolishGS('NrDomu') || ', ' ||
        getPolishGS('KodPocztowy') || ' ' ||
        getPolishGS('Miejscowosc');
END;
/

CREATE OR REPLACE FUNCTION getPolishGS(FieldName in varchar2, NoXMLConvert in NUMBER := 0)
RETURN varchar2 IS
    RetVal varchar2(60 char);
BEGIN
    IF NoXMLConvert >= 1 THEN
        return getGlobalSettingForExp('UDEF_JPK_'||FieldName);
    ELSE
        return DBMS_XMLGEN.CONVERT(getGlobalSettingForExp('UDEF_JPK_'||FieldName), 0);
    END IF;    
END;
/

CREATE OR REPLACE FUNCTION getValueWPackage(zposid in number, UnitOrGross in varchar2)
RETURN number IS
    RetVal number;
BEGIN
if upper(SUBSTR(UnitOrGross, 1 , 1)) = 'U' then
 SELECT SUM(zpos_unitprice) into RetVal FROM zpos WHERE zpos_cdt =1 and zpos_internalsplit_zpos_id = zposid    
 GROUP BY zpos_internalsplit_zpos_id;
else
SELECT SUM(zpos_grossunitprice) into RetVal FROM zpos WHERE zpos_cdt =1 and zpos_internalsplit_zpos_id = zposid    
 GROUP BY zpos_internalsplit_zpos_id;
end if;
    return RetVal;
END;
/


CREATE OR REPLACE FORCE VIEW V8_EXP_POLAND_PODMIOT1
(
   PELNANAZWA,
   NIP,
   REGON,
   KODKRAJU,
   WOJEWODZTWO,
   POWIAT,
   GMINA,
   ULICA,
   NRDOMU,
   NRLOKALU,
   MIEJSCOWOSC,
   KODPOCZTOWY,
   POCZTA
)
AS
   SELECT                                                         -- Full Name
         getPolishGS ('PelnaNazwa') PelnaNazwa,
          -- VAT ID of the Invoice issuer
          getPolishGS ('NIP') NIP,
          -- National Business Registry Number
          getPolishGS ('REGON') REGON,
          -- Country Code. For Poland always: PL
          'PL' KodKraju,
          -- State
          getPolishGS ('Wojewodztwo') Wojewodztwo,
          -- County
          getPolishGS ('Powiat') Powiat,
          -- Community
          getPolishGS ('Gmina') Gmina,
          -- Street
          getPolishGS ('Ulica') Ulica,
          -- Building number
          getPolishGS ('NrDomu') NrDomu,
          -- Flat number. Usually to be omited.
          getPolishGS ('NrLokalu') NrLokalu,
          -- City
          getPolishGS ('Miejscowosc') Miejscowosc,
          -- Zip Code
          getPolishGS ('KodPocztowy') KodPocztowy,
          -- Post Office
          getPolishGS ('Poczta') Poczta
     FROM DUAL;
/

CREATE OR REPLACE FORCE VIEW V8_EXP_POLAND_INVXMPTCOUNT
(
   ZINV_ID,
   COUNTEXEMPTEDPOSTINGS
)
AS
     SELECT zpil_zinv_id ZINV_ID, COUNT (zpil_id) CountExemptedPostings
       FROM zpil
      WHERE zpil_zpos_id IN
               (SELECT zpos_id
                  FROM zpos
                 WHERE zpos_zdco_id IN
                          (SELECT zdtl_zdco_id
                             FROM zdtl
                            WHERE zdtl_ztxc_id IN (SELECT ztxc_id
                                                     FROM ztxc
                                                    WHERE ztxc_sftx_order = 7)))
   GROUP BY zpil_zinv_id;
/

 CREATE OR REPLACE FORCE VIEW "V8_EXP_POLAND_INVOICES_BASE" ("ZINV_ID", "INVOICEDATE", "P_1", "INVOICENUMBER", "P_3A", "P_3B", "P_3C", "P_3D", "P_4A", "P_4B", "P_5A", "P_5B", "P_6", "P_13_1", "P_14_1", "P_13_2", "P_14_2", "P_13_3", "P_14_3", "P_13_4", "P_14_4", "P_13_5", "P_13_6", "P_13_7", "P_15", "P_16", "P_17", "P_18", "P_18A", "EXEMPTCOUNT", "P_20", "P_21", "P_22", "P_23", "P_106E_2", "P_106E_3", "RODZAJFAKTURY", "PRZYCZYNAKOREKTY", "NRFAKORYGOWANEJ", "OKRESFAKORYGOWANEJ", "NRFAZALICZKOWEJ", "FOLIOTYPE", "TAXEXEMPTIONTYPE", "TAXEXEMPTIONDESC") AS 
  SELECT zinvMain.zinv_id,
          zinvMain.zinv_date InvoiceDate,
          -- Invoice Issue Date
          TO_CHAR (zinvMain.zinv_date, 'yyyy-mm-dd') P_1,
          zinvMain.zinv_number InvoiceNumber,
          -- Guest Full Name or Company Name to whom invoice is issued
          DBMS_XMLGEN.CONVERT (
             CONVERT (Customer.xcms_name1 || ' ' || Customer.xcms_name3,
                      'UTF8'),
             0)
             P_3A,
          -- Full address of Guest or Company to whom invoice is issued
          DBMS_XMLGEN.CONVERT (
             CONVERT (
                   Customeraddr.xadr_street1
                || ' '
                || Customeraddr.xadr_street2
                || ' '
                || Customeraddr.xadr_street3
                || ', '
                || Customeraddr.xadr_zip
                || ' '
                || NVL (Customeraddr.xadr_city, ' '),
                'UTF8'),
             0)
             P_3B,
          -- Name of the Invoice issuer (property)
          getPolishGS ('PelnaNazwa') P_3C,
          -- Full address of the Invoice issuer
          getPolishFullHotelAddress P_3D,
          -- Constant: Always PL
          'PL' P_4A,
          -- VAT ID of the Invoice issuer (property)
          getPolishGS ('NIP') P_4B,
          -- Country Code of the Invoice receiver. If tag P_5B is omitted (because it is empty) this tag should be omitted as well
          CASE xcou_iso2 WHEN 'GR' THEN 'EL' ELSE xcou_iso2 END P_5A,
          --  VAT ID of the Invoice receiver. Following pattern is allowed: [1-9]((\d[1-9])|([1-9]\d))\d{7} If entry is empty whole tag should be omitted.
          REGEXP_REPLACE (
             NVL (
                (SELECT xmnr_value
                   FROM xmnr, xmty
                  WHERE     xmnr_xcms_id = zinvMain.zinv_xcms_id
                        AND xmnr_xmty_id = xmty_id
                        AND xmty_role = 3),
                ''),
             '[^[:digit:]]+')
             P_5B,
          -- Departure date. If this date is not know -> Invoice issue date
          CASE
             WHEN (yres_actdeptime IS NOT NULL AND zfac_actvaliduntil IS NULL)
             THEN
                TO_CHAR (yres_actdeptime, 'yyyy-mm-dd')
             WHEN (zfac_actvaliduntil IS NOT NULL AND yres_actdeptime IS NULL)
             THEN
                TO_CHAR (zfac_actvaliduntil, 'yyyy-mm-dd')
             ELSE
                TO_CHAR (zinvMain.zinv_date, 'yyyy-mm-dd')
          END
             P_6,
          -- Sum of all netto values with main vat rate (highest: currently 23% (or 22% in case of historical data before tax rate change [corrections])
          NVL (
             (SELECT TRIM (
                        TO_CHAR (SUM (InvSum.NetPrice_Num), '9999999990.99'))
                FROM v8_exp_Poland_InvSum InvSum
               WHERE     InvSum.zinv_id = zinvMain.zinv_id
                     AND (InvSum.VATRate IN (22, 23))),
             '0.00')
             P_13_1,
          -- Sum of VAT values for main vat rate (highest: currently 23% (or 22% in case of historical data before tax rate change [corrections])
          NVL (
             (SELECT TRIM (
                        TO_CHAR (SUM (InvSum.TaxValue_Num), '9999999990.99'))
                FROM v8_exp_Poland_InvSum InvSum
               WHERE     InvSum.zinv_id = zinvMain.zinv_id
                     AND (InvSum.VATRate IN (22, 23))),
             '0.00')
             P_14_1,
          -- Sum of all netto values with first reduced vat rate (currently 8% (or 7% in case of historical data before tax rate change [corrections])
          NVL (
             (SELECT TRIM (
                        TO_CHAR (SUM (InvSum.NetPrice_Num), '9999999990.99'))
                FROM v8_exp_Poland_InvSum InvSum
               WHERE     InvSum.zinv_id = zinvMain.zinv_id
                     AND (InvSum.VATRate IN (8, 7))),
             '0.00')
             P_13_2,
          -- VAT values for first reduced vat rate (currently 8% (or 7% in case of historical data before tax rate change [corrections])
          NVL (
             (SELECT TRIM (
                        TO_CHAR (SUM (InvSum.TaxValue_Num), '9999999990.99'))
                FROM v8_exp_Poland_InvSum InvSum
               WHERE     InvSum.zinv_id = zinvMain.zinv_id
                     AND (InvSum.VATRate IN (8, 7))),
             '0.00')
             P_14_2,
          -- Sum of all netto values with second reduced vat rate (currently 5% (or 3% in case of historical data before tax rate change [corrections])
          NVL (
             (SELECT TRIM (
                        TO_CHAR (SUM (InvSum.NetPrice_Num), '9999999990.99'))
                FROM v8_exp_Poland_InvSum InvSum
               WHERE     InvSum.zinv_id = zinvMain.zinv_id
                     AND (InvSum.VATRate IN (5, 3))),
             '0.00')
             P_13_3,
          -- VAT values for second reduced vat rate (currently 5% (or 3% in case of historical data before tax rate change [corrections])
          NVL (
             (SELECT TRIM (
                        TO_CHAR (SUM (InvSum.TaxValue_Num), '9999999990.99'))
                FROM v8_exp_Poland_InvSum InvSum
               WHERE     InvSum.zinv_id = zinvMain.zinv_id
                     AND (InvSum.VATRate IN (5, 3))),
             '0.00')
             P_14_3,
          -- Sum of all netto values with third reduced vat rate (currently it is reserved only)
          0 P_13_4,
          -- VAT values for third reduced vat rate (currently it is reserved only)
          0 P_14_4,
          -- Sum of all netto values with 4th reduced vat rate (currently it is reserved only)
          0 P_13_5,
          -- Sum of all netto values with 0% vat rate (should be read from configuration)
          0 P_13_6,
          -- Sum of all netto values with exempted vat rate (should be read from configuration)
          0 P_13_7,
          -- Total gross amount
          NVL (
             (SELECT TRIM (
                        TO_CHAR (SUM (InvSum.GrossPrice_Num),
                                 '9999999990.99'))
                FROM v8_exp_Poland_InvSum InvSum
               WHERE InvSum.zinv_id = zinvMain.zinv_id),
             '0.00')
             P_15,
          -- Always false
          'false' P_16,
          -- Always false
          'false' P_17,
          -- Always false
          'false' P_18,
          -- Always false
          'false' P_18A,
          --Default: false. See additional explanation in FRD - Section: Special handling of Folios containing Tax Exempt postings
          (SELECT CountExemptedPostings
             FROM v8_exp_Poland_InvXmptCount xcinv
            WHERE xcinv.zinv_id = zinvMain.zinv_id)
             ExemptCount,
          -- Always false
          'false' P_20,
          -- Always false
          'false' P_21,
          -- Always false
          'false' P_22,
          -- Always false
          'false' P_23,
          -- Always false
          'false' P_106E_2,
          -- Always false
          'false' P_106E_3,
          -- Invice Type. Allowed entries: 'VAT' - regular invoice, 'KOREKTA' - correction invoice, 'ZAL' - prepayment invoice, 'POZ' - No longer used(Voided Invoices)
          CASE
             WHEN    (    yres_id IS NOT NULL
                      AND (   zinvMain.zinv_date < to_date(yres_actarrtime)
                           OR yres_actarrtime IS NULL))
                  OR (    zfac_id IS NOT NULL
                      AND (   zinvMain.zinv_date < to_date(zfac_actvalidfrom)
                           OR zfac_actvalidfrom IS NULL))
             THEN
                'ZAL'
             --ADDED BY PIOTR
             WHEN ZINN.ZINN_TYPE = 15
             THEN
                'KOREKTA'
             -----------------------------------
             WHEN zinvMain.zinv_void_zinv_id IS NULL
             THEN
                'VAT'
             --COMMENTED BY PIOTR
             /*WHEN zinvMain.zinv_void_zinv_id IS NOT NULL
             THEN
                'ANULOWANA'*/
             -----------------------------------
          END
             AS RodzajFaktury,
          -- If this is a correction invoice - reason for correction. Otherwise should be omitted
          CASE
             --CONDITION CHANGED BY PIOTR (ORIGINAL IN COMMENTS)
             WHEN zinvMain.zinv_zinn_id IN (SELECT zinn.zinn_id
                                              FROM zinn
                                             WHERE ZINN.ZINN_TYPE = 15) --zinvMain.zinv_void_zinv_id IS NOT NULL
             -----------------------------------
             THEN
                -- ADDED BY PIOTR conversion to UTF8
                DBMS_XMLGEN.CONVERT (
                   CONVERT ( (SELECT zink_reason
                                FROM zink
                               WHERE zink_zinv_id = zinvMain.zinv_id),
                            'UTF8'),
                   0)
             ELSE
                ''
          END
             PrzyczynaKorekty,
		  -- Prepayment INVOICE
		  CASE
             WHEN    (    yres_id IS NOT NULL
                      AND (   zinvMain.zinv_date < to_date(yres_actarrtime)
                           OR yres_actarrtime IS NULL))
                  OR (    zfac_id IS NOT NULL
                      AND (   zinvMain.zinv_date < to_date(zfac_actvalidfrom)
                           OR zfac_actvalidfrom IS NULL))
             THEN
				zinvMain.zinv_number
			 ELSE
				''
		  END
			 NrFaZaliczkowej,
          -- If this is a correction invoice - Invoice nuber of invoice which is being corrected. Otherwise should be omitted
          --CHANGED BY PIOTR (ORIGINAL IN COMMENTS)
          CASE
             WHEN zinvMain.zinv_zinn_id IN (SELECT zinn.zinn_id
                                              FROM zinn
                                             WHERE ZINN.ZINN_TYPE = 15) --zinvMain.zinv_void_zinv_id IS NOT NULL
             THEN
                --CHANGED BY PIOTR
                Format_Inv_Number (
                   (SELECT K.ZINV_NUMBER
                      FROM ZINV K
                     WHERE K.zinv_id = zinvMain.zinv_correcting_zinv_id),
                   (SELECT K.ZINV_DATE
                      FROM ZINV K
                     WHERE K.zinv_id = zinvMain.zinv_correcting_zinv_id),
                   getPolishGS ('INV_PREFSUF', 1))
             ELSE
                ''
          END
             NrFaKorygowanej,
          -- Related period for corretion (?? I suppose this is the date of the correction)
          --PIOTR: THIS IS WRONGLY ASSUMED
          CASE
             WHEN zinvMain.zinv_zinn_id IN (SELECT zinn.zinn_id
                                              FROM zinn
                                             WHERE ZINN.ZINN_TYPE = 15) --zinvMain.zinv_void_zinv_id IS NOT NULL
             THEN
                --CHANGED BY PIOTR (ORIGINAL IN COMMENTS)
                TO_CHAR (
                   (SELECT K.ZINV_DATE
                      FROM ZINV K
                     WHERE K.zinv_id = zinvMain.zinv_correcting_zinv_id) --zinvMain.zinv_date
                                                                        ----------------------------
                   ,
                   'yyyy-mm-dd')
             ELSE
                ''
          END
             OkresFaKorygowanej,
          -- Invoice type taken from zfst
          SUBSTR (zfst_longdesc, 1, 1) FolioType,
          -- Tax Exemption type
          CASE
             WHEN (yres_id IS NOT NULL) AND (txRes.ztxx_id IS NOT NULL)
             THEN
                SUBSTR (txRes.ztxx_shortdesc, 1, 1)
             WHEN (zfac_id IS NOT NULL) AND (txFA.ztxx_id IS NOT NULL)
             THEN
                SUBSTR (txFA.ztxx_shortdesc, 1, 1)
             ELSE
                ''
          END
             TaxExemptionType,
                    -- Tax Exemption Description
          CASE
             WHEN (yres_id IS NOT NULL) AND (txRes.ztxx_id IS NOT NULL)
             THEN
                txRes.ztxx_longdesc
             WHEN (zfac_id IS NOT NULL) AND (txFA.ztxx_id IS NOT NULL)
             THEN
                txFA.ztxx_longdesc
             ELSE
                ''
          END
             TaxExemptionDesc
     FROM zinv zinvMain,
          xcms Customer,
          xadr CustomerAddr,
          xcou,
          zwin,
          zfac,
          yres,
          zfst,
          ztxx txRes,
          ztxx txFA,
          zinv zinvVoid,
          zinn
    WHERE     Customer.xcms_id = zinvMain.zinv_xcms_id
         AND CustomerAddr.xadr_id = zinvMain.zinv_xadr_id
         -- AND CustomerAddr.xadr_xcms_id = zinvMain.zinv_xcms_id
         -- AND CustomerAddr.xadr_primary = 1
          AND CustomerAddr.xadr_xcou_id = xcou_id(+)
          AND zinvMain.zinv_void_zinv_id = zinvVoid.zinv_id(+)
          AND zinvMain.zinv_zwin_id = zwin_id
          AND zinvMain.zinv_zfst_id = zfst_id(+)
          AND zwin_zfac_id = zfac_id(+)
          AND zwin_yres_id = yres_id(+)
          AND yres_ztxx_id = txRes.ztxx_id(+)
          AND zfac_ztxx_id = txFA.ztxx_id(+)
          --ADDED BY PIOTR TO GET ZINN REFERENCE
          AND ZINVMAIN.ZINV_ZINN_ID = ZINN.ZINN_ID
          AND ZINN.ZINN_TYPE <> 13         -- PIOTR TO REMOVE VOIDING INVOICES
          AND ZINN.ZINN_TYPE <> 2 -- PIOTR TO REMOVE INVOICES WITH USER DEFINABLE NUMBER CYCLES
          AND NOT EXISTS(SELECT 1 FROM ZINV H 
                         WHERE ZINVMAIN.ZINV_ID = H.ZINV_VOID_ZINV_ID OR 
                         (zinvMain.zinv_void_zinv_id is not null)) -- SARAVARA CONDITION ADDED TO REMOVE VOID INVOICES
---------------------------------------
;
/

CREATE OR REPLACE FORCE VIEW V8_EXP_POLAND_INVOICEDATA
(
   ZINV_ID,
   ZINV_VOID_ZINV_ID,
   INVOICEDATE,
   INVOICENUMBER,
   FOLIOTYPE,
   ZTCD_ID,
   DESCRIPTION,
   QTY,
   UNITOFMEASURE,
   VATRATE,
   NETPRICE_NUM,
   NETUNITPRICE_NUM,
   TAXVALUE_NUM,
   GROSSUNITPRICE_NUM,
   GROSSPRICE_NUM,
   NETPRICE,
   NETUNITPRICE,
   TAXVALUE,
   GROSSUNITPRICE,
   GROSSPRICE,
   ZARR_ID,
   ZDCO_ID,
   POSTDATE,
   ZARR_LONGDESC,
   ZDCO_LONGDESC,
   ZDCO_DEPOSIT
)
AS
     SELECT zinv_id,
            zinv_void_zinv_id,
            zinv_date InvoiceDate,
            zinv_number InvoiceNumber,
            SUBSTR (zfst_longdesc, 1, 1) FolioType,
            ztcd_id,
            DBMS_XMLGEN.CONVERT (CONVERT (znet.zpos_descript, 'UTF8'), 0)
               Description,
            znet.zpos_quantity Qty,
            'usl' UnitOfMeasure,
            TO_NUMBER (
               NVL (
                  REPLACE (REGEXP_SUBSTR (ztcd_udf, '\d*[\.\,]\d+|\d+'),
                           ',',
                           '.'),
                  '0'))
               VATRate,
            --znet.zpos_unitprice * znet.zpos_quantity NetPrice_Num,
            getValueWPackage (znet.zpos_id, 'Unit') * znet.zpos_quantity
               NetPrice_Num,
            --znet.zpos_unitprice NetUnitprice_Num,
            getValueWPackage (znet.zpos_id, 'Unit') NetUnitprice_Num,
              --ztax.zpos_unitprice * ztax.zpos_quantity TaxValue_Num,
              getValueWPackage (znet.zpos_id, 'Gross') * znet.zpos_quantity
            - getValueWPackage (znet.zpos_id, 'Unit') * znet.zpos_quantity
               TaxValue_Num,
            --              znet.zpos_unitprice * znet.zpos_quantity
            --            + ztax.zpos_unitprice * ztax.zpos_quantity
            --               GrossPrice_Num,
            getValueWPackage (znet.zpos_id, 'Gross') GrossUnitPrice_Num,
            getValueWPackage (znet.zpos_id, 'Gross') * znet.zpos_quantity
               GrossPrice_Num,
            TRIM (
               TO_CHAR (                               /*znet.zpos_unitprice*/
                  getValueWPackage (znet.zpos_id, 'Unit') * znet.zpos_quantity,
                  '9999999990.99'))
               NetPrice,
            TRIM (
               TO_CHAR (                               /*znet.zpos_unitprice*/
                        ABS (getValueWPackage (znet.zpos_id, 'Unit')),
                        '9999999990.99'))
               NetUnitprice,
            TRIM (
               TO_CHAR (                              /*ztax.zpos_unitprice */
                      ztax.zpos_quantity
                    * getValueWPackage (znet.zpos_id, 'Gross')
                  -   getValueWPackage (znet.zpos_id, 'Unit')
                    * znet.zpos_quantity,
                  '9999999990.99'))
               TaxValue,
            TO_CHAR ( /*znet.zpos_unitprice * znet.zpos_quantity
                    + ztax.zpos_unitprice * ztax.zpos_quantity*/
                     getValueWPackage (znet.zpos_id, 'Gross'), '9999999990.99')
               GrossUnitPrice,
            TRIM (
               TO_CHAR (
                  /*znet.zpos_unitprice * znet.zpos_quantity
                + ztax.zpos_unitprice * ztax.zpos_quantity*/
                  getValueWPackage (znet.zpos_id, 'Gross') * znet.zpos_quantity,
                  '9999999990.99'))
               GrossPrice,
            znet.zpos_zarr_id zarr_id,
            znet.zpos_zdco_id zdco_id,
            znet.zpos_postdate Postdate,
            NVL (zarr_longdesc, zdco_longdesc) zarr_longdesc, --changed by Piotr --zarr_longdesc
            zdco_longdesc,
            zdco_deposit
       FROM zpos znet,
            zpos ztax,
            zinv,
            zpil,
            ztcd,
            zfst,
            zarr,
            zdco,
            zinn
      WHERE     zinv_id = zpil_zinv_id
            AND znet.zpos_id = zpil_zpos_id
            AND ztax.zpos_ztcd_id = ztcd_id
            AND znet.zpos_zarr_id = zarr_id(+)
            AND znet.zpos_zdco_id = zdco_id
            AND znet.zpos_cdt = 1
            AND ZTAX.ZPOS_CDT = 2
            AND znet.zpos_taxlink_id = ztax.zpos_taxlink_id
            AND zinv_zfst_id = zfst_id
            AND ZNET.ZPOS_INTERNALSPLIT_ZPOS_ID = ZNET.ZPOS_ID
            --ADDED BY PIOTR TO GET ZINN REFERENCE
            AND ZINV.ZINV_ZINN_ID = ZINN.ZINN_ID
            AND ZINN.ZINN_TYPE <> 13       -- PIOTR TO REMOVE VOIDING INVOICES
            AND ZINN.ZINN_TYPE <> 2 -- PIOTR TO REMOVE INVOICES WITH USER DEFINABLE NUMBER CYCLES
   ---------------------------------------
   ORDER BY zinv_id, znet.zpos_id;
/

CREATE OR REPLACE FORCE VIEW V8_EXP_POLAND_FAKTURAWIERSZ
(
   INVOICEDATE,
   INVOICENUMBER,
   P_2B,
   P_7,
   P_8A,
   P_8B,
   NETPRICE_NUM,
   GROSSPRICE_NUM,
   P_9A,
   P_9B,
   P_10,
   P_11,
   P_11A,
   P_12
)
AS
   SELECT InvoiceDate,
          TO_NUMBER (InvoiceNumber) InvoiceNumber, --ADDED BY PIOTR TO GET PROPER SORTING
          /*DECODE (
             zinv_void_zinv_id,
             NULL, Format_Inv_Number (InvoiceNumber,
                                      InvoiceDate,
                                      getPolishGS ('INV_PREFSUF', 1)),
             Format_Inv_Number (InvoiceNumber,
                                InvoiceDate,
                                getPolishGS ('KOR_PREFSUF', 1)))*/
          DECODE (
             FOLIOTYPE,
             'K', Format_Inv_Number (InvoiceNumber,
                                     InvoiceDate,
                                     getPolishGS ('KOR_PREFSUF', 1)),
             Format_Inv_Number (InvoiceNumber,
                                InvoiceDate,
                                getPolishGS ('INV_PREFSUF', 1)))
             P_2B,
          CONVERT (Description, 'UTF8') P_7,
          CONVERT (UnitOfMeasure, 'UTF8') P_8A,
          Qty P_8B,
          NetPrice_Num,
          GrossPrice_Num,
          TRIM (TO_CHAR (NetUnitPrice_Num, '9999999990.99')) P_9A,
          TRIM (TO_CHAR (GrossUnitPrice_Num, '9999999990.99')) P_9B,
          '0.00' P_10,
          TRIM (TO_CHAR (NetPrice_Num, '9999999990.99')) P_11,
          TRIM (TO_CHAR (GrossPrice_Num, '9999999990.99')) P_11A,
          VATRate P_12
     FROM v8_exp_Poland_InvoiceData
    WHERE FolioType NOT IN ('2',
                            '3',
                            '4',
                            '5')
   UNION ALL
     SELECT InvoiceDate,
            TO_NUMBER (InvoiceNumber) InvoiceNumber, --ADDED BY PIOTR TO GET PROPER SORTING
            DECODE (
               zinv_void_zinv_id,
               NULL, Format_Inv_Number (InvoiceNumber,
                                        InvoiceDate,
                                        getPolishGS ('INV_PREFSUF', 1)),
               Format_Inv_Number (InvoiceNumber,
                                  InvoiceDate,
                                  getPolishGS ('KOR_PREFSUF', 1)))
               P_2B,
            CONVERT (zdco_longdesc, 'UTF8') P_7,
            CONVERT (UnitOfMeasure, 'UTF8') P_8A,
            Qty P_8B,
            SUM (NetPrice_Num) NetPrice_Num,
            SUM (GrossPrice_Num) GrossPrice_Num,
            TRIM (TO_CHAR (SUM (NetUnitPrice_Num), '9999999990.99')) P_9A,
            TRIM (TO_CHAR (SUM (GrossUnitPrice_Num), '9999999990.99')) P_9B,
            '0.00' P_10,
            TRIM (TO_CHAR (SUM (NetPrice_Num), '9999999990.99')) P_11,
            TRIM (TO_CHAR (SUM (GrossPrice_Num), '9999999990.99')) P_11A,
            VATRate P_12
       FROM v8_exp_Poland_InvoiceData
      WHERE FolioType = '2'
   GROUP BY Postdate,
            zdco_id,
            zdco_longdesc,
            InvoiceDate,
            InvoiceNumber,
            UnitOfMeasure,
            Qty,
            VatRate,
            zinv_void_zinv_id
   UNION ALL
     SELECT InvoiceDate,
            TO_NUMBER (InvoiceNumber) InvoiceNumber, --ADDED BY PIOTR TO GET PROPER SORTING
            DECODE (
               zinv_void_zinv_id,
               NULL, Format_Inv_Number (InvoiceNumber,
                                        InvoiceDate,
                                        getPolishGS ('INV_PREFSUF', 1)),
               Format_Inv_Number (InvoiceNumber,
                                  InvoiceDate,
                                  getPolishGS ('KOR_PREFSUF', 1)))
               P_2B,
            CONVERT (zdco_longdesc, 'UTF8') P_7,
            CONVERT (UnitOfMeasure, 'UTF8') P_8A,
            Qty P_8B,
            SUM (NetPrice_Num) NetPrice_Num,
            SUM (GrossPrice_Num) GrossPrice_Num,
            TRIM (TO_CHAR (SUM (NetUnitPrice_Num), '9999999990.99')) P_9A,
            TRIM (TO_CHAR (SUM (GrossUnitPrice_Num), '9999999990.99')) P_9B,
            '0.00' P_10,
            TRIM (TO_CHAR (SUM (NetPrice_Num), '9999999990.99')) P_11,
            TRIM (TO_CHAR (SUM (GrossPrice_Num), '9999999990.99')) P_11A,
            VATRate P_12
       FROM v8_exp_Poland_InvoiceData
      WHERE FolioType = '3'
   GROUP BY zdco_id,
            zdco_longdesc,
            InvoiceDate,
            InvoiceNumber,
            UnitOfMeasure,
            Qty,
            VatRate,
            zinv_void_zinv_id
   UNION ALL
     SELECT InvoiceDate,
            TO_NUMBER (InvoiceNumber) InvoiceNumber, --ADDED BY PIOTR TO GET PROPER SORTING
            DECODE (
               zinv_void_zinv_id,
               NULL, Format_Inv_Number (InvoiceNumber,
                                        InvoiceDate,
                                        getPolishGS ('INV_PREFSUF', 1)),
               Format_Inv_Number (InvoiceNumber,
                                  InvoiceDate,
                                  getPolishGS ('KOR_PREFSUF', 1)))
               P_2B,
            CONVERT (zarr_longdesc, 'UTF8') P_7,
            CONVERT (UnitOfMeasure, 'UTF8') P_8A,
            Qty P_8B,
            SUM (NetPrice_Num) NetPrice_Num,
            SUM (GrossPrice_Num) GrossPrice_Num,
            TRIM (TO_CHAR (SUM (NetUnitPrice_Num), '9999999990.99')) P_9A,
            TRIM (TO_CHAR (SUM (GrossUnitPrice_Num), '9999999990.99')) P_9B,
            '0.00' P_10,
            TRIM (TO_CHAR (SUM (NetPrice_Num), '9999999990.99')) P_11,
            TRIM (TO_CHAR (SUM (GrossPrice_Num), '9999999990.99')) P_11A,
            VATRate P_12
       FROM v8_exp_Poland_InvoiceData
      WHERE FolioType = '4'
   GROUP BY Postdate,
            zarr_id,
            zarr_longdesc,
            InvoiceDate,
            InvoiceNumber,
            UnitOfMeasure,
            Qty,
            VatRate,
            zinv_void_zinv_id
   UNION ALL
     SELECT InvoiceDate,
            TO_NUMBER (InvoiceNumber) InvoiceNumber, --ADDED BY PIOTR TO GET PROPER SORTING
            DECODE (
               zinv_void_zinv_id,
               NULL, Format_Inv_Number (InvoiceNumber,
                                        InvoiceDate,
                                        getPolishGS ('INV_PREFSUF', 1)),
               Format_Inv_Number (InvoiceNumber,
                                  InvoiceDate,
                                  getPolishGS ('KOR_PREFSUF', 1)))
               P_2B,
            CONVERT (zarr_longdesc, 'UTF8') P_7,
            CONVERT (UnitOfMeasure, 'UTF8') P_8A,
            Qty P_8B,
            SUM (NetPrice_Num) NetPrice_Num,
            SUM (GrossPrice_Num) GrossPrice_Num,
            TRIM (TO_CHAR (SUM (NetUnitPrice_Num), '9999999990.99')) P_9A,
            TRIM (TO_CHAR (SUM (GrossUnitPrice_Num), '9999999990.99')) P_9B,
            '0.00' P_10,
            TRIM (TO_CHAR (SUM (NetPrice_Num), '9999999990.99')) P_11,
            TRIM (TO_CHAR (SUM (GrossPrice_Num), '9999999990.99')) P_11A,
            VATRate P_12
       FROM v8_exp_Poland_InvoiceData
      WHERE FolioType = '5'
   GROUP BY zarr_id,
            zarr_longdesc,
            InvoiceDate,
            InvoiceNumber,
            UnitOfMeasure,
            Qty,
            VatRate,
            zinv_void_zinv_id
   ORDER BY InvoiceDate, InvoiceNumber             --CHANGED BY PIOTR --P_2B;
;
/

CREATE OR REPLACE FORCE VIEW V8_EXP_POLAND_INVOICES
(
   P_1,
   P_106E_2,
   P_106E_3,
   P_13_1,
   P_13_2,
   P_13_3,
   P_13_4,
   P_13_5,
   P_13_6,
   P_13_7,
   P_14_1,
   P_14_2,
   P_14_3,
   P_14_4,
   P_15,
   P_16,
   P_17,
   P_18,
   P_18A,
   P_20,
   P_21,
   P_22,
   P_23,
   P_3A,
   P_3B,
   P_3C,
   P_3D,
   P_4A,
   P_4B,
   P_5A,
   P_5B,
   P_6,
   RODZAJFAKTURY,
   TAXEXEMPTIONTYPE,
   ZINV_ID,
   EXEMPTCOUNT,
   FOLIOTYPE,
   INVOICEDATE,
   INVOICENUMBER,
   NRFAKORYGOWANEJ,
   OKRESFAKORYGOWANEJ,
   NRFAZALICZKOWEJ,
   PRZYCZYNAKOREKTY,
   P_2A,
   P_19,
   P_19A,
   P_19B,
   P_19C
)
AS
     SELECT P_1,
            P_106E_2,
            P_106E_3,
            P_13_1,
            P_13_2,
            P_13_3,
            P_13_4,
            P_13_5,
            P_13_6,
            P_13_7,
            P_14_1,
            P_14_2,
            P_14_3,
            P_14_4,
            P_15,
            P_16,
            P_17,
            P_18,
			P_18A,
            P_20,
            P_21,
			P_22,
            P_23,
            P_3A,
            P_3B,
            P_3C,
            P_3D,
            P_4A,
            P_4B,
            P_5A,
            P_5B,
            P_6,
            RODZAJFAKTURY,
            TAXEXEMPTIONTYPE,
            ZINV_ID,
            EXEMPTCOUNT,
            FOLIOTYPE,
            INVOICEDATE,
            INVOICENUMBER,
            NRFAKORYGOWANEJ,
            OKRESFAKORYGOWANEJ,
			NRFAZALICZKOWEJ,
            PRZYCZYNAKOREKTY,
            CASE RodzajFaktury
               WHEN 'KOREKTA'
               THEN
                  Format_Inv_Number (InvoiceNumber,
                                     InvoiceDate,
                                     getPolishGS ('KOR_PREFSUF', 1))
               ELSE
                  Format_Inv_Number (InvoiceNumber,
                                     InvoiceDate,
                                     getPolishGS ('INV_PREFSUF', 1))
            END
               P_2A,
            --Default: false. See additional explanation in FRD - Section: Special handling of Folios containing Tax Exempt postings
            CASE WHEN 0 < EXEMPTCOUNT THEN 'true' ELSE 'false' END P_19,
            CASE
               WHEN 0 < EXEMPTCOUNT
               THEN
                  CASE
                     WHEN TAXEXEMPTIONTYPE = 'A' THEN TaxExemptionDesc
                     ELSE 'false'
                  END
               ELSE
                  'false'
            END
               P_19A,
            CASE
               WHEN 0 < EXEMPTCOUNT
               THEN
                  CASE
                     WHEN TAXEXEMPTIONTYPE = 'B' THEN TaxExemptionDesc
                     ELSE 'false'
                  END
               ELSE
                  'false'
            END
               P_19B,
            CASE
               WHEN 0 < EXEMPTCOUNT
               THEN
                  CASE
                     WHEN TAXEXEMPTIONTYPE = 'C' THEN TaxExemptionDesc
                     ELSE 'false'
                  END
               ELSE
                  'false'
            END
               P_19C
       FROM v8_exp_Poland_Invoices_Base
   ORDER BY InvoiceDate, TO_NUMBER (InvoiceNumber) --CHANGED BY PIOTR --InvoiceNumber;
;
/

CREATE OR REPLACE FORCE VIEW V8_EXP_POLAND_INVSUM
(
   ZINV_ID,
   ZTCD_ID,
   VATRATE,
   NETPRICE_NUM,
   TAXVALUE_NUM,
   GROSSPRICE_NUM,
   NETPRICE,
   TAXVALUE,
   GROSSPRICE
)
AS
     SELECT zinv_id,
            ztcd_id,
            VATRate,
            SUM (NetPrice_Num) NetPrice_Num,
            SUM (TaxValue_Num) TaxValue_Num,
            SUM (GrossPrice_Num) GrossPrice_Num,
            TRIM (TO_CHAR (SUM (NetPrice_Num), '9999999990.99')) NetPrice,
            TRIM (TO_CHAR (SUM (TaxValue_Num), '9999999990.99')) TaxValue,
            TRIM (TO_CHAR (SUM (GrossPrice_Num), '9999999990.99')) GrossPrice
       FROM v8_exp_Poland_InvoiceData
   GROUP BY zinv_id, ztcd_id, VATRate
   ORDER BY zinv_id;
/

CREATE OR REPLACE FORCE VIEW V8_EXP_POLAND_CHECK
(
   P_1,
   P_2A,
   ROWS_QTY,
   P_3A,
   P_5B,
   NIP_VALIDATION,
   HEADERNETTO,
   HEADERVAT,
   P_15,
   INVHEADERINTEGRITYCHECK,
   NETTOHR_INTERGRITYCHECK,
   BRUTTOHR_INTERGRITYCHECK
)
AS
   WITH nq2
           AS (  SELECT P_2B,
                        INVOICEDATE,
                        COUNT (*) ROWS_QTY,
                        SUM (TO_NUMBER (p_11, '9999999990.99')) P_11,
                        SUM (TO_NUMBER (p_11A, '9999999990.99')) P_11A
                   FROM V8_EXP_POLAND_FAKTURAWIERSZ
               GROUP BY P_2B, INVOICEDATE),
        nq1
           AS (  SELECT P_1,
                        P_2A,
                        P_3A,
                        INVOICEDATE,
                        SUM (
                             TO_NUMBER (p_13_1, '9999999990.99')
                           + TO_NUMBER (p_13_2, '9999999990.99')
                           + TO_NUMBER (p_13_3, '9999999990.99')
                           + TO_NUMBER (p_13_4, '9999999990.99')
                           + TO_NUMBER (p_13_5, '9999999990.99')
                           + TO_NUMBER (p_13_6, '9999999990.99')
                           + TO_NUMBER (p_13_7, '9999999990.99'))
                           HeaderNetto,
                        SUM (
                             TO_NUMBER (p_14_1, '9999999990.99')
                           + TO_NUMBER (p_14_2, '9999999990.99')
                           + TO_NUMBER (p_14_3, '9999999990.99')
                           + TO_NUMBER (p_14_4, '9999999990.99'))
                           HeaderVAT,
                        SUM (TO_NUMBER (p_15, '9999999990.99')) p_15,
                        SUM (
                             TO_NUMBER (p_13_1, '9999999990.99')
                           + TO_NUMBER (p_13_2, '9999999990.99')
                           + TO_NUMBER (p_13_3, '9999999990.99')
                           + TO_NUMBER (p_13_4, '9999999990.99')
                           + TO_NUMBER (p_13_5, '9999999990.99')
                           + TO_NUMBER (p_13_6, '9999999990.99')
                           + TO_NUMBER (p_13_7, '9999999990.99')
                           + TO_NUMBER (p_14_1, '9999999990.99')
                           + TO_NUMBER (p_14_2, '9999999990.99')
                           + TO_NUMBER (p_14_3, '9999999990.99')
                           + TO_NUMBER (p_14_4, '9999999990.99')
                           - TO_NUMBER (p_15, '9999999990.99'))
                           InvHeaderIntegrityCheck,
                        P_5A,
                        P_5B
                   FROM V8_EXP_POLAND_INVOICES
               GROUP BY P_1,
                        P_2A,
                        P_3A,
                        INVOICEDATE,
                        P_5A,
                        P_5B)
   SELECT nq1.P_1,
          nq1.P_2A,
          NQ2.ROWS_QTY,                        -- show qty of rows per invoice
          nq1.P_3A,
          nq1.P_5B,
          CASE
             WHEN LENGTH (nq1.P_5B) <> 10 AND P_5A = 'PL'
             THEN
                'Possible wrong entry for NIP'
             ELSE
                NULL
          END
             NIP_VALIDATION,                         -- check Polish NIP Entry
          nq1.HeaderNetto,
          nq1.HeaderVAT,
          nq1.P_15,
          nq1.InvHeaderIntegrityCheck, --Check difference between sum of net values + sum of VAT against Total Gross on header level
          nq1.HeaderNetto - nq2.p_11 NettoHR_IntergrityCheck, -- check difference between sum of net values on the rows level against sum of net values on header level
          nq1.P_15 - nq2.p_11a BruttoHR_IntergrityCheck -- check difference between sum of gross values on the rows level against sum of gross values on header level
     FROM nq1
          INNER JOIN nq2
             ON nq1.P_2A = nq2.P_2B AND nq1.invoicedate = nq2.invoicedate;
/