SELECT b.bus_unit,
       b.reg_no,
       b.pt_code,
       b.md_code,
       a.pt_reg_date
FROM   a_pt_bill_master a,
       a_pt_ordreg b
WHERE  ( a.bus_unit = b.bus_unit
         --AND  ORD_DATE >= :P_DATE_FROM  
         AND a.reg_no = b.reg_no
         AND a.pt_code = b.pt_code
         AND b.ord_status <> 'V'
         AND ( ( b.md_code BETWEEN :P_MD_CODE_FROM AND :P_MD_CODE_TO )
                OR ( :P_MD_CODE_FROM IS NULL
                     AND :P_MD_CODE_TO IS NULL ) )
         AND ( ( Trunc(b.ord_end_date) BETWEEN :P_DATE_FROM AND :P_DATE_TO
                 AND Nvl(b.dntl_xray_lab_flag, 'A') <> 'L' )
                OR ( b.dntl_xray_lab_flag = 'L'
                     AND Trunc(b.ord_date) BETWEEN :P_DATE_FROM AND :P_DATE_TO )
                OR ( :P_DATE_FROM IS NULL
                     AND :P_DATE_TO IS NULL ) )
         --AND B.AS_APPLICATION <> 'INPT'
         AND Nvl(b.ord_bill_amount, 0) >= 0 )
       AND ( :BUS_UNIT = b.bus_unit )
       AND ( :MD_CODE2 = b.md_code )
GROUP  BY b.bus_unit,
          b.reg_no,
          b.pt_code,
          b.md_code,
          a.pt_reg_date  

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.42          0          0          0           0
Execute    111      0.00       0.14          0          0          0           0
Fetch      111     20.34    1402.81     646924    1294948          0         281
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      223     20.36    1403.37     646924    1294948          0         281

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 675  (ULTGNP)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (GROUP BY)
      0    NESTED LOOPS
      0     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                'A_PT_ORDREG'
      0      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 
                 'TEMP1_A_PT_ORDRDEG' (NON-UNIQUE)
      0     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                'A_PT_BILL_MASTER'
      0      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                 'PK_A_PT_BILL_MASTER' (UNIQUE)

INDEX_NAME	COLUMN_NAME	COLUMN_POSITION
A_PT_ORDREG_NDX5	PT_CODE	                2
A_PT_ORDREG_NDX5	REG_NO	                3
A_PT_ORDREG_NDX5	BUS_UNIT	        1
TEMP1_A_PT_ORDRDEG	BUS_UNIT	        1
TEMP1_A_PT_ORDRDEG	MD_CODE	                2
IDX9_DONE_SRVCS	        BUS_UNIT	        1
IDX9_DONE_SRVCS	        ORD_END_DATE	        2 
IDX9_DONE_SRVCS	        CASH_FLAG	        3
IDX9_DONE_SRVCS	        BILL_FLAG	        4
A_PT_ORDREG	        PT_CODE	                1
A_PT_ORDREG	        ORDER_NO	        2
A_PT_ORDREG	        SERVICE_TYPE_CODE       3
A_PT_ORDREG	        SERVICE_CODE	        4
A_PT_ORDREG_NDX4	BUS_UNIT	        1
A_PT_ORDREG_NDX4	REG_NO	                2
A_PT_ORDREG_NDX4	ORDER_NO	        3
IDX6_A_PT_ORDREG	BUS_UNIT	        1
IDX6_A_PT_ORDREG	REG_NO	                2
A_PT_ORDREG_NEW	        BUS_UNIT	        1
A_PT_ORDREG_NEW	        ORD_DATE	        2
A_PT_ORDREG_NEW	        ORD_STATUS	        3
A_PT_ORDREG_NEW	        MIS_FLAG	        4
A_PT_ORDREG_NEW	        PRIOR_APPROVAL_FLAG 	5
A_PT_ORDREG_NEW	        CASH_FLAG	        6
TEMP_A_PT_ORDREG	BUS_UNIT	        1
TEMP_A_PT_ORDREG	ORD_DATE	        2
IDX8_A_PT_ORDREG	BUS_UNIT	        1
IDX8_A_PT_ORDREG	DNTL_PROCESS_FLAG 	2
IDX8_A_PT_ORDREG	ORD_DATE	        3
IDX8_A_PT_ORDREG	ORD_STATUS	        4
PK_A_PT_ORDREG	        BUS_UNIT	        1
PK_A_PT_ORDREG	        ORDER_NO	        2
PK_A_PT_ORDREG	        SEQ_NO	                3
WASI_A_PT_ORDREG	BUS_UNIT	        1
WASI_A_PT_ORDREG	ORD_DATE	        2
WASI_A_PT_ORDREG	HOSP_DEPT_CODE	        3
WASI_A_PT_ORDREG	BILL_FLAG	        4
WASI_A_PT_ORDREG	CASH_FLAG	        5
WASI_A_PT_ORDREG	ORD_STATUS	        6
DNT_LAB_A_PT_ORDREG	BUS_UNIT	        1
DNT_LAB_A_PT_ORDREG	ORD_DATE	        2
DNT_LAB_A_PT_ORDREG	DNTL_XRAY_LAB_FLAG 	3
DNT_LAB_A_PT_ORDREG	DNTL_LAB_CHRGS	        4
DNT_LAB_A_PT_ORDREG	MIS_FLAG	        5
DNT_LAB_A_PT_ORDREG	DNTL_PROCESS_FLAG	6
DNT_LAB_A_PT_ORDREG	CASH_FLAG	        7
APT_DEDUCTIBLE	        BUS_UNIT	        1
APT_DEDUCTIBLE	        MD_CODE	                2
APT_DEDUCTIBLE	        PT_CODE	                3

AND ( ( b.md_code BETWEEN :P_MD_CODE_FROM AND :P_MD_CODE_TO )
                OR ( :P_MD_CODE_FROM IS NULL
                     AND :P_MD_CODE_TO IS NULL ) )

 AND ( :MD_CODE2 = b.md_code )

...DECODE(dntl_xray_lab_flag, 'L', ord_date, ord_end_date)
      BETWEEN :P_DATE_FROM and :P_DATE_TO

...DECODE(dntl_xray_lab_flag, 'L', ord_date, ord_end_date)
      BETWEEN :P_DATE_FROM and :P_DATE_TO + 1

SELECT B.BUS_UNIT , B.REG_NO ,  B.PT_CODE, B.MD_CODE, A.PT_REG_DATE
FROM    A_PT_BILL_MASTER A , A_PT_ORDREG B
    WHERE ( ( A.BUS_UNIT = B.BUS_UNIT  
AND A.REG_NO = B.REG_NO  
AND A.PT_CODE = B.PT_CODE  
AND B.ORD_STATUS <> 'V'
AND ((B.MD_CODE BETWEEN :P_MD_CODE_FROM AND :P_MD_CODE_TO) OR(:P_MD_CODE_FROM IS NULL AND :P_MD_CODE_TO IS NULL))   
AND
(
(B.ORD_END_DATE >= :P_DATE_FROM  AND B.ORD_END_DATE < (:P_DATE_TO+1) AND NVL(B.DNTL_XRAY_LAB_FLAG,'A') <> 'L')
OR (B.DNTL_XRAY_LAB_FLAG = 'L'  AND
B.ORD_DATE >= :P_DATE_FROM  AND B.ORD_DATE < :P_DATE_TO+1)
)
AND NVL(B.ORD_BILL_AMOUNT,0) >= 0 ) ) AND ( :BUS_UNIT = B.BUS_UNIT) AND ( :MD_CODE2 = B.MD_CODE)  GROUP BY B.BUS_UNIT , B.REG_NO, B.PT_CODE,B.MD_CODE,A.PT_REG_DATE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.25          0          0          0           0
Execute    111      0.00       0.00          0          0          0           0
Fetch      111     16.47     849.57     551975    1297181          0         246
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      223     16.47     849.82     551975    1297181          0         246

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 675  (ULTGNP)

Rows     Row Source Operation
-------  ---------------------------------------------------
    601  SORT GROUP BY 
    601   NESTED LOOPS 
1234982    TABLE ACCESS BY INDEX ROWID A_PT_ORDREG 
1235093     INDEX RANGE SCAN (object id 5302)
    601    TABLE ACCESS BY INDEX ROWID A_PT_BILL_MASTER 
    601     INDEX UNIQUE SCAN (object id 5249)