SELECT /*+ LEADING(LDTB_CONTRACT_BALANCE) index(LDTB_CONTRACT_BALANCE IND01_LDTB_CON_BALANCE) index(LDTB_CONTRACT_MASTER)*/
LDTB_CONTRACT_MASTER.BRANCH
     , LDTB_CONTRACT_MASTER.PRODUCT PROD_ID
     , MITM_CUSTOMER_DEFAULT.CUST_MIS_1 OUC
     , LDTB_CONTRACT_MASTER.CURRENCY CCY
     , SUM(LDTB_CONTRACT_BALANCE.PRINCIPAL_OUTSTANDING_BAL) PRINCIPAL_DUE
     ,SUM (NVL(FN_GET_LD_ACCRUAL(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'IS',STTM_DATES.NEXT_WORKING_DAY),0) - NVL(FN_GET_LD_AMT(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'IS','PAID',LDTB_CONTRACT_MASTER.VALUE_DATE, LDTB_CONTRACT_MASTER.MATURITY_DATE),0))        INTEREST_DUE 
     ,SUM (NVL(FN_GET_LD_ACCRUAL(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'N',STTM_DATES.NEXT_WORKING_DAY) ,0)     - NVL(FN_GET_LD_AMT(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'N','PAID',LDTB_CONTRACT_MASTER.VALUE_DATE, LDTB_CONTRACT_MASTER.MATURITY_DATE),0))  OVERDUE_INTEREST
FROM LDTB_CONTRACT_MASTER     
   , LDTB_CONTRACT_PREFERENCE     
   , MITM_CUSTOMER_DEFAULT     
   , STTM_CUSTOMER         
   , CFTB_CONTRACT_INTEREST     
   , LDTB_CONTRACT_BALANCE    
   , STTM_BRANCH         
   , LDTM_PRODUCT_MASTER    
   , CSTB_CONTRACT        
   , STTM_DATES         
WHERE 
LDTB_CONTRACT_MASTER.CONTRACT_REF_NO         = CSTB_CONTRACT.CONTRACT_REF_NO
AND CSTB_CONTRACT.MODULE_CODE='LD'
AND LDTB_CONTRACT_MASTER.COUNTERPARTY         = MITM_CUSTOMER_DEFAULT.CUSTOMER
AND LDTB_CONTRACT_MASTER.COUNTERPARTY         = STTM_CUSTOMER.CUSTOMER_NO
AND LDTB_CONTRACT_PREFERENCE.CONTRACT_REF_NO= LDTB_CONTRACT_MASTER.CONTRACT_REF_NO
AND LDTB_CONTRACT_PREFERENCE.VERSION_NO     = LDTB_CONTRACT_MASTER.VERSION_NO
AND CFTB_CONTRACT_INTEREST.CONTRACT_REFERENCE_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO
AND CFTB_CONTRACT_INTEREST.COMPONENT         = LDTB_CONTRACT_MASTER.MAIN_COMP
AND CFTB_CONTRACT_INTEREST.EVENT_SEQUENCE_NO    = (SELECT MAX(Y.EVENT_SEQUENCE_NO) FROM CFTB_CONTRACT_INTEREST Y WHERE Y.CONTRACT_REFERENCE_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO)
AND LDTB_CONTRACT_BALANCE.CONTRACT_REF_NO         = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO    
--AND NVL(LDTB_CONTRACT_BALANCE.PRINCIPAL_OUTSTANDING_BAL,0) <> 0
AND (PRINCIPAL_OUTSTANDING_BAL>0 or PRINCIPAL_OUTSTANDING_BAL<0)
AND LDTM_PRODUCT_MASTER.PRODUCT         = LDTB_CONTRACT_MASTER.PRODUCT
AND STTM_BRANCH.BRANCH_CODE     = LDTB_CONTRACT_MASTER.BRANCH
AND STTM_DATES.BRANCH_CODE     = STTM_BRANCH.BRANCH_CODE 
AND LDTB_CONTRACT_MASTER.VERSION_NO     = (SELECT MAX(Z.VERSION_NO) FROM LDTBS_CONTRACT_MASTER Z WHERE Z.CONTRACT_REF_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO)
--AND (LDTB_CONTRACT_BALANCE.PRINCIPAL_OUTSTANDING_BAL <> 0
AND ((PRINCIPAL_OUTSTANDING_BAL>0 or PRINCIPAL_OUTSTANDING_BAL<0)
OR ( EXISTS ( SELECT 1 FROM LDTB_AMOUNT_DUE WHERE CONTRACT_REF_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO AND AMOUNT_DUE > (NVL(AMOUNT_SETTLED,0) + NVL(ADJUSTED_AMOUNT,0)) AND DUE_DATE <= STTM_DATES.TODAY )))
GROUP BY LDTB_CONTRACT_MASTER.BRANCH, LDTB_CONTRACT_MASTER.PRODUCT, MITM_CUSTOMER_DEFAULT.CUST_MIS_1, LDTB_CONTRACT_MASTER.CURRENCY

SET TIMING ON

SELECT FN_GET_LD_ACCRUAL(
    LDTB_CONTRACT_MASTER.CONTRACT_REF_NO
,   'N', trunc(sysdate))
FROM   LDTB_CONTRACT_MASTER
WHERE  ROWNUM <= 1000

SELECT SUM(A.TILL_DATE_ACCRUAL) 
FROM
 LDTBS_CONTRACT_ACCRUAL_HISTORY A WHERE A.CONTRACT_REF_NO = :B2 AND 
  A.VALUE_DATE <= :B1 AND A.ACC_ENTRY_PASSED = 'Y' AND A.COMPONENT IN ( 
  SELECT DISTINCT COMPONENT FROM LDTB_AMOUNT_DUE WHERE CONTRACT_REF_NO = 
  A.CONTRACT_REF_NO AND COMPONENT_TYPE IN (SUBSTR(:B3 ,1,1),SUBSTR(:B3 ,2,1)))
   AND A.EVENT_SEQ_NO = ( SELECT MAX(EVENT_SEQ_NO) FROM 
  LDTB_CONTRACT_ACCRUAL_HISTORY WHERE CONTRACT_REF_NO = A.CONTRACT_REF_NO AND 
  COMPONENT = A.COMPONENT AND ACC_ENTRY_PASSED = 'Y' AND VALUE_DATE <= :B1 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute   1469      0.09       0.10          0          0          0           0
Fetch     1469   1025.08    1114.75      19847  176061253          0        1469
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2938   1025.17    1114.85      19847  176061253          0        1469

	Select sum(a.TILL_DATE_ACCRUAL) into l_amount
	from ldtbs_contract_accrual_history a
	where a.contract_ref_no = p_contract_ref_no
	and a.VALUE_DATE <= l_accrual_till
	and a.acc_entry_passed = 'Y'
	and a.component in ( select distinct component
		from ldtb_amount_due
		where contract_ref_no = a.contract_ref_no
		and component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1)))
	and a.event_seq_no = ( select max(event_seq_no)
		from ldtb_contract_accrual_history
		where contract_ref_no = a.contract_ref_no
		and component = a.component
		and acc_entry_passed = 'Y'
		and VALUE_DATE <= l_accrual_till );

	Select sum(a.TILL_DATE_ACCRUAL) into l_amount
	from ldtbs_contract_accrual_history a
	where a.contract_ref_no = p_contract_ref_no
	and a.VALUE_DATE <= l_accrual_till
	and a.acc_entry_passed = 'Y'
	and a.component in ( select distinct component
		from ldtb_amount_due
		where contract_ref_no = a.contract_ref_no
		and component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1),substr(p_component_type,3,1)))
	and a.event_seq_no = ( select max(event_seq_no)
		from ldtb_contract_accrual_history
		where contract_ref_no = a.contract_ref_no
		and component = a.component
		and acc_entry_passed = 'Y'
		and VALUE_DATE <= l_accrual_till );

SELECT CONTRACT_REF_NO
FROM   LDTBS_CONTRACT_ACCRUAL_HISTORY
GROUP BY CONTRACT_REF_NO
HAVING COUNT(*) > 1000

SELECT CONTRACT_REF_NO, COUNT(*)
FROM LDTB_AMOUNT_DUE 
GROUP BY CONTRACT_REF_NO
HAVING COUNT(*) > 1000