--2. ap with no TAX invoice line
 --    UNION ALL
     SELECT (/*SELECT DESCRIPTION
               FROM apps.FND_LOOKUP_VALUES_VL
              WHERE     lookup_type = 'XXAR_UK_TAX_QT_MT'
                    AND lookup_code = TO_CHAR (ap.GL_DATE, 'MON-YYYY'))
                QT,*/  --commented by Leo Chen
             SELECT lu.DESCRIPTION as QT
            from apps.FND_LOOKUP_VALUES_VL aa
           LEFT JOIN apps.FND_LOOKUP_VALUES_VL lu
           ON lu.lookup_type = 'XXAR_UK_TAX_QT_MT'
           AND lu.lookup_code = TO_CHAR (ap.GL_DATE, 'MON-YYYY')),
            TO_CHAR (ap.GL_DATE, 'MON-YYYY')
                MT,
            'PI'
                TYPE,
            CASE
                --9.2
                WHEN UPPER (a3.description) = 'VAT ONLY' THEN 'BOX4' -- net amount = tax
                WHEN EXCEPTION_CODE IN ('A', 'D') THEN 'BOX4'
                WHEN EXCEPTION_CODE IN ('F', 'J') THEN 'NOBOX'
                ELSE 'NOBOX'
            END
                VATBOX,
            CASE
                WHEN UPPER (a3.description) = 'VAT ONLY'
                THEN
                    'NOBOX'                                -- net amount = tax
                WHEN EXCEPTION_CODE IN ('B',
                                        'D',
                                        'E',
                                        'G')
                THEN
                    'BOX7'
                WHEN EXCEPTION_CODE IN ('F', 'J', 'N')
                THEN
                    'NOBOX'
                ELSE
                    'BOX7'
            END
                NETBOX,
            DECODE (INPUT_TAX_CLASSIFICATION_CODE,
                    'T1 GB VAT - 20', 'T0 GB VAT - 0',
                    'VATSTAND', 'T0 GB VAT - 0',
                    'T5 GB VAT - 5', 'T0 GB VAT - 0',
                    INPUT_TAX_CLASSIFICATION_CODE)
                TAX_RATE_CODE,
            aps.vendor_name
                TP_NAME,
            ap.invoice_num
                TRX_NUM,
            --clean wrong tax classification
            AP.GL_DATE,
            'GBP'
                Func_CURR,
            a3.description,
            CASE
                --exception 001 --3/6
                --9.2
                WHEN UPPER (a3.description) = 'VAT ONLY'
                THEN
                    0                                      -- net amount = tax
                WHEN     TO_CHAR (ap.GL_DATE, 'MON-YYYY') = 'NOV-2020'
                     AND zLD.TRX_currency_code = 'EUR'
                THEN
                    ROUND (zLD.line_amt * 0.8997758, 2)
                ELSE
                    ROUND (
                        (NVL (zLD.currency_conversion_rate, 1) * zLD.line_amt),
                        2)
            END
                NET_FUNC_AMT,
            CASE
                WHEN UPPER (a3.description) = 'VAT ONLY'
                THEN
                    ROUND (
                        (NVL (zLD.currency_conversion_rate, 1) * zLD.line_amt),
                        2)
                --222
                /* ROUND (
                                                                (NVL (zLD.currency_conversion_rate, 1) * zLD.line_amt),
                                                                2)
                                                                */
                -- net amount = tax
                ELSE
                    0
            END
                AS TAX_FUNC_AMT,
            A3.LINE_NUMBER,
            CASE
                WHEN AP.invoice_num IN ('3804',
                                        '8001051137',
                                        '34971123',
                                        '34971318')
                THEN
                    'T24'
                WHEN ap.invoice_num IN ('90914') AND ap.org_id = 126
                THEN
                    'T23'
                ELSE
                    'NOREVERSE'
            END
                REVERSE_FLAG,
            CASE
                WHEN ap.invoice_num IN ('90914') AND ap.org_id = 126 THEN 'C'
                ELSE ex.EXCEPTION_CODE
            END
                EX_CODE,
            CASE
                WHEN ap.invoice_num IN ('90914') AND ap.org_id = 126
                THEN
                    'T23'
                ELSE
                    ex.to_tax
            END
                EX_TO,
            aps.vendor_id
                TP_ID
       FROM zx_lines_det_factors  zld,
            ap_invoices_all       ap,
            ap_suppliers          aps,
            ap_invoice_lines_all  a3,
            (SELECT *
               FROM XXEBTAX_EXCEPTION_TAB
              WHERE org_id = fnd_global.org_id) ex
      WHERE     AP.vendor_id = aps.vendor_id
            AND zld.trx_id = ap.invoice_id
            AND zld.INTERNAL_ORGANIZATION_ID = fnd_global.org_id
            AND ap.org_id = fnd_global.org_id
            AND a3.invoice_id = aP.invoice_id
            AND ZLD.trx_line_id = a3.line_number
         /*   AND (NOT EXISTS
                     (SELECT trx_id, trx_line_id
                        FROM zx_lines zl
                       WHERE     zld.trx_id = zl.trx_id
                             AND zld.trx_line_id = zl.trx_line_id
                             AND zl.INTERNAL_ORGANIZATION_ID =
                                 fnd_global.org_id))*/    --updated by Leo Chen
            AND (zld.trx_id,zld.trx_line_id) NOT IN (
              SELECT trx_id, trx_line_id
              FROM zx_lines zl
              WHERE zl.INTERNAL_ORGANIZATION_ID =fnd_global.org_id
              AND trx_id IS NOT NULL
              AND trx_line_id IS NOT NULL)
            AND batch_id <> 2160103
            AND zld.line_amt <> 0
            AND AP.INVOICE_NUM NOT IN
                    ('SCCUKLTD1', '002P523506', '1410011398DD')
            AND CANCELLED_DATE IS NULL
            AND ex.trx_num(+) = ap.invoice_num
            AND ex.line_num(+) = a3.line_number
            AND ex.org_id(+) = ap.org_id
     --ORDER BY type,trx_num, LINE_NUMBER
     ----------------------------------------------