SELECT
                                               rtv.CLAIM_ID,
                                                rtv.UCR "UCR",
                                                rtv.DOL "DOL",
             rtv.NAME_OF_ASSURED "Name of Assured",
             rtv.DESCRIPTION_OF_LOSS "Description of Loss",                                               rtv.LEADING "Leading",                                                rtv.STATUS_DECODE "Status",
                                            cmv.movement_type "Mvmt Type",

                                                TO_CHAR(cmv.movement_date, 'YYYY/MM/DD') "Mvmt Date",

                                                TO_CHAR(cmv.movement_paid, '9,999,999,990') "Mvmt Paid (client Base)",

                                                TO_CHAR(cmv.movement_outstanding, '9,999,999,990') "Mvmt O/S (client Base)"

                                    FROM

                                                xcssys.claim_movement_view cmv,

                                                xcssys.report_template_view rtv

                                    WHERE

                                                rtv.claim_id = cmv.claim_id AND

                                                rtv.client_name = 'Axis' AND

                                                rtv.status like 'O' AND

                                                (cmv.movement_paid >= 1000 OR

                                                cmv.movement_type = 'Narrative')

                                    ORDER BY

                                                cmv.claim_id ASC, cmv.movement_date DESC


Following is xplain plan....

OPERATION                      OPTIONS         OBJECT_NAME

------------------------------ --------------- -------------

SELECT STATEMENT

SORT                           ORDER BY

HASH JOIN

VIEW                                           CLAIM_TOTALS

SORT                           GROUP BY

HASH JOIN                      OUTER

TABLE ACCESS                   FULL            CLAIM

VIEW                                           CLAIM_FINANCI

                                               MARY_VIEW

 

SORT                           GROUP BY

 

OPERATION                      OPTIONS         OBJECT_NAME

------------------------------ --------------- -------------

NESTED LOOPS                   OUTER

HASH JOIN

TABLE ACCESS                   FULL            EXCHANGE_RATE

HASH JOIN

TABLE ACCESS                   FULL            FINANCIALS

HASH JOIN

TABLE ACCESS                   FULL            CLAIM

HASH JOIN

TABLE ACCESS                   FULL            MARKET_LINE

TABLE ACCESS                   FULL            TRANSACTION

VIEW

 

OPERATION                      OPTIONS         OBJECT_NAME

------------------------------ --------------- -------------

SORT                           GROUP BY

HASH JOIN                      OUTER

HASH JOIN                      OUTER

FILTER

HASH JOIN                      OUTER

HASH JOIN                      OUTER

HASH JOIN                      OUTER

TABLE ACCESS                   FULL            CLAIM

TABLE ACCESS                   FULL            CLAIM_EXPERTS

TABLE ACCESS                   FULL            EXPERT_PAYMEN

TABLE ACCESS                   FULL            FINANCIALS

 

OPERATION                      OPTIONS         OBJECT_NAME

------------------------------ --------------- -------------

TABLE ACCESS                   FULL            TRANSACTION

TABLE ACCESS                   FULL            MARKET_LINE

HASH JOIN

HASH JOIN

TABLE ACCESS                   FULL            CLAIM

TABLE ACCESS                   FULL            POLICY

VIEW                                           CLAIM_MOVEMEN

SORT                           UNIQUE

UNION-ALL

NESTED LOOPS

HASH JOIN

 

OPERATION                      OPTIONS         OBJECT_NAME

------------------------------ --------------- -------------

TABLE ACCESS                   FULL            EXCHANGE_RATE

HASH JOIN

TABLE ACCESS                   FULL            MARKET_LINE

HASH JOIN

TABLE ACCESS                   FULL            FINANCIALS

TABLE ACCESS                   FULL            TRANSACTION

INDEX                          UNIQUE SCAN     CLAIM_IDX

NESTED LOOPS

HASH JOIN

TABLE ACCESS                   FULL            EXCHANGE_RATE

HASH JOIN

 

OPERATION                      OPTIONS         OBJECT_NAME

------------------------------ --------------- -------------

TABLE ACCESS                   FULL            MARKET_LINE

HASH JOIN

TABLE ACCESS                   FULL            FINANCIALS

TABLE ACCESS                   FULL            TRANSACTION

INDEX                          UNIQUE SCAN     CLAIM_IDX

NESTED LOOPS

HASH JOIN

HASH JOIN

VIEW                                           EXPERT_MOVEME

                                               W

 

 

OPERATION                      OPTIONS         OBJECT_NAME

------------------------------ --------------- -------------

FILTER

SORT                           GROUP BY

HASH JOIN                      OUTER

HASH JOIN                      OUTER

HASH JOIN                      OUTER

HASH JOIN                      OUTER

HASH JOIN                      OUTER

TABLE ACCESS                   FULL            CLAIM

TABLE ACCESS                   FULL            CLAIM_EXPERTS

TABLE ACCESS                   FULL            EXPERT_PAYMEN

TABLE ACCESS                   FULL            FINANCIALS

 

OPERATION                      OPTIONS         OBJECT_NAME

------------------------------ --------------- -------------

TABLE ACCESS                   FULL            TRANSACTION

TABLE ACCESS                   FULL            MARKET_LINE

TABLE ACCESS                   FULL            EXCHANGE_RATE

TABLE ACCESS                   FULL            FINANCIALS

TABLE ACCESS                   FULL            TRANSACTION

INDEX                          UNIQUE SCAN     CLAIM_IDX

TABLE ACCESS                   FULL            NOTES_HISTORY