--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
     ----------------------------------------------

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'))

orclz>
orclz> set lin 200
orclz> set pages 1000
orclz>
orclz>
orclz> alter session set statistics_level=all;

Session altered.

orclz> select * from emp join dept using (deptno) where ename='MILLER';

         DEPTNO           EMPNO ENAME      JOB                   MGR HIREDATE                        SAL            COMM DNAME          LOC
--------------- --------------- ---------- --------- --------------- ------------------- --------------- --------------- -------------- -------------
             10            7934 MILLER     CLERK                7782 1982-01-23:00:00:00            1300                 ACCOUNTING     NEW YORK

orclz> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0anq4nkgpgjzq, child number 0
-------------------------------------
select * from emp join dept using (deptno) where ename='MILLER'

Plan hash value: 3625962092

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  NESTED LOOPS                |         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   NESTED LOOPS               |         |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    TABLE ACCESS FULL         | EMP     |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("EMP"."ENAME"='MILLER')
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
   - this is an adaptive plan


27 rows selected.

orclz>

select * from table(dbms_xplan.display_cursor(format=>'allstats last +cost adaptive'));