explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dCjfV

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Append (cost=522,625.99..548,748.72 rows=16 width=156) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=522,625.99..522,626.17 rows=15 width=123) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Sort (cost=522,625.99..522,626.02 rows=15 width=123) (actual rows= loops=)

  • Sort Key: a.hbldt_req_date, (ahiscl.ipd_mst.getwardname(a.hipnum_ward_code, a.gnum_hospital_code, NULL::character varying, NULL::character varying)), (ahiscl.bill_mst.getipdchargetypename(a.sblnum_ipd_chargetype_id, 0::numeric, 0::numeric, a.gnum_hospital_code)), (nvl(DECODE( a.hblnum_tariff_id , 1290796 , a.gstr_tariff_extra_info , 1020002 , NULL::character varying , ahiscl.bill_mst.gettariffnamewithcode(a.hblnum_tariff_id, (0)::numeric, a.gnum_hospital_code) ), DECODE( nvl(a.hblnum_tariff_id, (0)::numeric) , 0 , ahiscl.bill_mst.getbservicename(a.sblnum_bservice_id, (0)::numeric, a.gnum_hospital_code, (0)::numeric) , 1020002 , ((((((ahiscl.bill_mst.gettariffnamewithcode(a.hblnum_tariff_id, (0)::numeric, a.gnum_hospital_code))::text || ' ('::text) || (nvl(edb_ora_timestamp_tochar_immutable(a.hbldt_start_date, 'DD-MM-YYYY'::character varying), edb_ora_timestamp_tochar_immutable(a.hbldt_req_date, 'DD-MM-YYYY'::character varying)))::text) || ' to '::text) || (edb_ora_timestamp_tochar_immutable(a.hbldt_end_date, 'DD-MM-YYYY'::character varying))::text) || ')'::text) , ahiscl.bill_mst.gettariffnamewithcode(a.hblnum_tariff_id, (0)::numeric, a.gnum_hospital_code) )))
4.          

Initplan (for Sort)

5. 0.000 0.000 ↓ 0.0

Seq Scan on hblt_pataccount_dtl hblt_pataccount_dtl_4 (cost=0.00..6,250.64 rows=1 width=3) (actual rows= loops=)

  • Filter: (hblnum_pataccount_no = 331017200000946::numeric)
6. 0.000 0.000 ↓ 0.0

Limit (cost=140,293.79..140,293.80 rows=1 width=20) (actual rows= loops=)

7.          

Initplan (for Limit)

8. 0.000 0.000 ↓ 0.0

Limit (cost=6,668.50..6,668.51 rows=1 width=18) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Sort (cost=6,668.50..6,668.51 rows=1 width=18) (actual rows= loops=)

  • Sort Key: hblt_pataccount_dtl_5.gdt_entry_date
10. 0.000 0.000 ↓ 0.0

Seq Scan on hblt_pataccount_dtl hblt_pataccount_dtl_5 (cost=0.00..6,668.49 rows=1 width=18) (actual rows= loops=)

  • Filter: ((gnum_isvalid = 1::numeric) AND (gnum_hospital_code = 33101::numeric) AND (hblnum_pataccount_no = 331017200000946::numeric))
11. 0.000 0.000 ↓ 0.0

Sort (cost=133,625.29..133,625.29 rows=1 width=20) (actual rows= loops=)

  • Sort Key: hblt_billreq_tariff_dtl.hbldt_req_date
12. 0.000 0.000 ↓ 0.0

Index Scan using index_hblt_billreq_tariff_dtl on hblt_billreq_tariff_dtl (cost=0.43..133,625.28 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (hrgnum_puk = $40)
  • Filter: ((gnum_isvalid = 1::numeric) AND (sblnum_bservice_id = 21::numeric) AND (hblnum_cancel_qty = 0::numeric) AND (gnum_hospital_code = 33101::numeric))
13. 0.000 0.000 ↓ 0.0

HashAggregate (cost=163.72..376,081.25 rows=15 width=123) (actual rows= loops=)

  • Group Key: a.gnum_hospital_code, a.hipnum_ward_code, a.hblnum_group_id, a.hblnum_tariff_id, a.sblnum_bservice_id, a.sblnum_ipd_chargetype_id, a.hbldt_req_date, a.gstr_tariff_extra_info, a.hblnum_pataccount_no, a.hbldt_start_date, a.hbldt_end_date, a.hrgnum_puk, a.gnum_isvalid, a.gnum_patient_cat_code
14. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl a (cost=0.43..156.57 rows=143 width=123) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = 331017200000946::numeric)
  • Filter: ((hblnum_group_id <> 100::numeric) AND (hblnum_reciept_no > 0::numeric) AND (hblnum_tariff_id > 0::numeric) AND (hblnum_req_no > 0::numeric) AND (hblnum_tariff_id <> 1090001::numeric) AND (hblnum_group_id <> 102::numeric) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (sblnum_chargetype_id = 2::numeric) AND (gnum_hospital_code = 33101::numeric) AND (nvl(hblnum_net_amount, 0::numeric) <> 0::numeric))
15.          

SubPlan (for HashAggregate)

16. 0.000 0.000 ↓ 0.0

Limit (cost=159.64..159.64 rows=1 width=13) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=159.64..159.65 rows=2 width=13) (actual rows= loops=)

  • Sort Key: b_8.gdt_entry_date
18. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_8 (cost=0.43..159.63 rows=2 width=13) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a.hblnum_pataccount_no)
  • Filter: ((gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (sblnum_bservice_id = 21::numeric) AND (gnum_hospital_code = a.gnum_hospital_code))
19. 0.000 0.000 ↓ 0.0

Aggregate (cost=161.15..161.17 rows=1 width=5) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_9 (cost=0.43..161.08 rows=29 width=5) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a.hblnum_pataccount_no)
  • Filter: ((hblnum_tariff_id <> ALL ('{1020003,1090001}'::numeric[])) AND (hblnum_group_id <> 100::numeric) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (hblnum_group_id = 107::numeric) AND (sblnum_chargetype_id = 2::numeric) AND (gnum_hospital_code = a.gnum_hospital_code))
21. 0.000 0.000 ↓ 0.0

Aggregate (cost=161.20..161.21 rows=1 width=5) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_10 (cost=0.43..161.08 rows=46 width=5) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a.hblnum_pataccount_no)
  • Filter: ((hblnum_tariff_id <> ALL ('{1020003,1090001}'::numeric[])) AND (hblnum_group_id <> 100::numeric) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (hblnum_group_id = 124::numeric) AND (sblnum_chargetype_id = 2::numeric) AND (gnum_hospital_code = a.gnum_hospital_code))
23. 0.000 0.000 ↓ 0.0

Aggregate (cost=161.05..161.06 rows=1 width=5) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_11 (cost=0.43..160.84 rows=84 width=5) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a.hblnum_pataccount_no)
  • Filter: ((hblnum_tariff_id <> ALL ('{1020003,1090001}'::numeric[])) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (sblnum_chargetype_id = 2::numeric) AND (gnum_hospital_code = a.gnum_hospital_code) AND (hblnum_group_id <> ALL ('{107,124,100}'::numeric[])))
25. 0.000 0.000 ↓ 0.0

Seq Scan on hblt_pataccount_dtl b_12 (cost=0.00..6,668.49 rows=1 width=4) (actual rows= loops=)

  • Filter: ((gnum_isvalid = 1::numeric) AND (gnum_hospital_code = a.gnum_hospital_code) AND (hblnum_pataccount_no = a.hblnum_pataccount_no))
26. 0.000 0.000 ↓ 0.0

Aggregate (cost=484.92..484.94 rows=1 width=9) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=0.43..484.92 rows=1 width=9) (actual rows= loops=)

  • Join Filter: (b_13.hblnum_tariff_id = c_1.hblnum_tariff_id)
28. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_13 (cost=0.43..161.08 rows=4 width=36) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a.hblnum_pataccount_no)
  • Filter: ((hblnum_tariff_rate > 0::numeric) AND (hblnum_bill_qty > 0::numeric) AND (hblnum_tariff_id > 0::numeric) AND (hblnum_discount_amt <= 0::numeric) AND (gnum_isvalid = 1::numeric) AND (hblnum_net_amount = 0::numeric) AND (gnum_hospital_code = a.gnum_hospital_code))
29. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..323.72 rows=2 width=29) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on hblt_pataccount_package_dtl c_1 (cost=0.00..323.71 rows=2 width=29) (actual rows= loops=)

  • Filter: ((gnum_isvalid = 1::numeric) AND (gnum_hospital_code = a.gnum_hospital_code) AND (hblnum_pataccount_no = a.hblnum_pataccount_no))
31. 0.000 0.000 ↓ 0.0

Limit (cost=161.33..161.34 rows=1 width=12) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Sort (cost=161.33..161.34 rows=1 width=12) (actual rows= loops=)

  • Sort Key: pat_s_1.hbldt_req_date
33. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl pat_s_1 (cost=0.43..161.32 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a.hblnum_pataccount_no)
  • Filter: ((sblnum_bservice_id = ANY ('{19,20}'::numeric[])) AND (hblnum_credit_bill_flag = ANY ('{1,0}'::numeric[])) AND (gnum_patient_cat_code <> 69::numeric) AND (hblnum_client_no = ANY ('{1000,2000,1938,1940,2012,2010,2013,2011,1213}'::numeric[])))
34. 0.000 0.000 ↓ 0.0

Aggregate (cost=159.64..159.65 rows=1 width=3) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl pat_ser_1 (cost=0.43..159.63 rows=1 width=3) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a.hblnum_pataccount_no)
  • Filter: ((hblnum_amt_paid_by_client IS NOT NULL) AND (sblnum_bservice_id = ANY ('{19,20}'::numeric[])) AND (hblnum_amt_paid_by_client <> 0::numeric) AND (hblnum_status = 1::numeric) AND (gnum_isvalid = 1::numeric))
36. 0.000 0.000 ↓ 0.0

Index Scan using index_adm on hblt_pataccount_dtl hblt_pataccount_dtl_6 (cost=0.42..4,152.98 rows=1 width=3) (actual rows= loops=)

  • Index Cond: (hrgnum_puk = a.hrgnum_puk)
  • Filter: ((hblnum_pataccount_no = a.hblnum_pataccount_no) AND (gnum_isvalid = a.gnum_isvalid))
37. 0.000 0.000 ↓ 0.0

Aggregate (cost=8,468.02..8,468.04 rows=1 width=9) (actual rows= loops=)

38.          

Initplan (for Aggregate)

39. 0.000 0.000 ↓ 0.0

Index Scan using index_adm on hblt_pataccount_dtl hblt_pataccount_dtl_7 (cost=0.42..4,152.98 rows=1 width=3) (actual rows= loops=)

  • Index Cond: (hrgnum_puk = a.hrgnum_puk)
  • Filter: ((hblnum_pataccount_no = a.hblnum_pataccount_no) AND (gnum_isvalid = a.gnum_isvalid))
40. 0.000 0.000 ↓ 0.0

Index Scan using index_adm on hblt_pataccount_dtl hblt_pataccount_dtl_8 (cost=0.42..4,152.98 rows=1 width=3) (actual rows= loops=)

  • Index Cond: (hrgnum_puk = a.hrgnum_puk)
  • Filter: ((hblnum_pataccount_no = a.hblnum_pataccount_no) AND (gnum_isvalid = a.gnum_isvalid))
41. 0.000 0.000 ↓ 0.0

Result (cost=0.43..162.05 rows=1 width=9) (actual rows= loops=)

  • One-Time Filter: (a.gnum_isvalid = 1::numeric)
42. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_14 (cost=0.43..162.05 rows=1 width=9) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a.hblnum_pataccount_no)
  • Filter: ((hblnum_reciept_no > 0::numeric) AND (hblnum_tariff_id >= 0::numeric) AND (hblnum_req_no > 0::numeric) AND ($51 < hblnum_net_amount) AND (gnum_hospital_code = a.gnum_hospital_code) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (hblnum_group_id = 102::numeric) AND (hblnum_tariff_id = 1020002::numeric))
43. 0.000 0.000 ↓ 0.0

Aggregate (cost=167.28..167.29 rows=1 width=18) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_15 (cost=0.43..162.04 rows=161 width=18) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a.hblnum_pataccount_no)
  • Filter: ((hblnum_reciept_no > 0::numeric) AND (hblnum_tariff_id >= 0::numeric) AND (hblnum_req_no > 0::numeric) AND (hblnum_group_id <> 100::numeric) AND (hblnum_tariff_id <> 1090001::numeric) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (sblnum_chargetype_id = 2::numeric) AND (gnum_hospital_code = a.gnum_hospital_code))
45. 0.000 0.000 ↓ 0.0

Index Scan using index_adm on hblt_pataccount_dtl hblt_pataccount_dtl_9 (cost=0.42..4,152.98 rows=1 width=5) (actual rows= loops=)

  • Index Cond: (hrgnum_puk = a.hrgnum_puk)
  • Filter: ((gnum_isvalid = 1::numeric) AND (hblnum_pataccount_no = a.hblnum_pataccount_no))
46. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=26,122.40..26,122.55 rows=1 width=652) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Unique (cost=26,122.40..26,122.54 rows=1 width=652) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Group (cost=26,122.40..26,122.47 rows=1 width=652) (actual rows= loops=)

  • Group Key: (nvl(a_1.gstr_tariff_extra_info, DECODE( nvl(a_1.hblnum_tariff_id, (0)::numeric) , 0 , ahiscl.bill_mst.getbservicename(a_1.sblnum_bservice_id, (0)::numeric, a_1.gnum_hospital_code, (0)::numeric) , 1020002 , ((((((ahiscl.bill_mst.gettariffnamewithcode(a_1.hblnum_tariff_id, (0)::numeric, a_1.gnum_hospital_code))::text || '-'::text) || (ahiscl.ipd_mst.getwardname(a_1.hipnum_ward_code, a_1.gnum_hospital_code, NULL::character varying, NULL::character varying))::text) || '-( '::text) || (ahiscl.bill_mst.getipdchargetypename(a_1.sblnum_ipd_chargetype_id, (0)::numeric, (0)::numeric, a_1.gnum_hospital_code))::text) || ')'::text) , ahiscl.bill_mst.gettariffnamewithcode(a_1.hblnum_tariff_id, (0)::numeric, a_1.gnum_hospital_code) ))), (to_date('01-01-1970'::character varying, 'DD-MM-YYYY'::character varying)), (to_date('01-01-1970'::character varying, 'DD-MM-YYYY'::character varying)), ((((nvl(max(a_1.hblnum_tariff_rate), 0::numeric))::text || '/'::text) || (ahiscl.bill_mst.getunitname(a_1.gnum_hospital_code, max(a_1.gnum_rate_unit_code)))::text)), (1), ((nvl(sum(a_1.hblnum_net_amount), 0::numeric) / nvl(sum(a_1.hblnum_bill_qty), 0::numeric))), (ahiscl.ipd_mst.getwardname(a_1.hipnum_ward_code, a_1.gnum_hospital_code, NULL::character varying, NULL::character varying)), (ahiscl.bill_mst.getipdchargetypename(a_1.sblnum_ipd_chargetype_id, 0::numeric, 0::numeric, a_1.gnum_hospital_code)), (to_date('01-01-1970'::character varying, 'DD-MM-YYYY'::character varying)), a_1.hblnum_group_id, (nvl((SubPlan 1), 0::numeric)), (nvl((SubPlan 2), 0::numeric)), (nvl((SubPlan 3), 0::numeric)), (nvl((SubPlan 4), 0::numeric)), ((nvl(sum(a_1.hblnum_net_amount), 0::numeric) / nvl(sum(a_1.hblnum_bill_qty), 0::numeric))), ((SubPlan 5)), (nvl((SubPlan 6), 0::numeric)), (ahiscl.bill_mst.get_ipd_billing_discount(a_1.gnum_hospital_code, a_1.hblnum_pataccount_no)), ((SubPlan 7)), (nvl((SubPlan 8), 0::numeric)), (DECODE( (SubPlan 9) , 0 , 0 , (SubPlan 12) )), (ahiscl.bill_mst.getcatgrp(a_1.gnum_patient_cat_code, a_1.gnum_hospital_code)), (ahiscl.bill_mst.get_room_tariff_surcharge(1::numeric, a_1.gnum_hospital_code, a_1.hblnum_pataccount_no)), ((SubPlan 13)), ((SubPlan 14)), bed1.acc_no, (to_date(edb_ora_timestamp_tochar_immutable(bed1.stay_date, 'DD-MM-YYYY'::character varying), 'DD-MM-YYYY'::character varying))
49. 0.000 0.000 ↓ 0.0

Sort (cost=26,122.40..26,122.41 rows=1 width=652) (actual rows= loops=)

  • Sort Key: (nvl(a_1.gstr_tariff_extra_info, DECODE( nvl(a_1.hblnum_tariff_id, (0)::numeric) , 0 , ahiscl.bill_mst.getbservicename(a_1.sblnum_bservice_id, (0)::numeric, a_1.gnum_hospital_code, (0)::numeric) , 1020002 , ((((((ahiscl.bill_mst.gettariffnamewithcode(a_1.hblnum_tariff_id, (0)::numeric, a_1.gnum_hospital_code))::text || '-'::text) || (ahiscl.ipd_mst.getwardname(a_1.hipnum_ward_code, a_1.gnum_hospital_code, NULL::character varying, NULL::character varying))::text) || '-( '::text) || (ahiscl.bill_mst.getipdchargetypename(a_1.sblnum_ipd_chargetype_id, (0)::numeric, (0)::numeric, a_1.gnum_hospital_code))::text) || ')'::text) , ahiscl.bill_mst.gettariffnamewithcode(a_1.hblnum_tariff_id, (0)::numeric, a_1.gnum_hospital_code) ))), (to_date('01-01-1970'::character varying, 'DD-MM-YYYY'::character varying)), (to_date('01-01-1970'::character varying, 'DD-MM-YYYY'::character varying)), ((((nvl(max(a_1.hblnum_tariff_rate), 0::numeric))::text || '/'::text) || (ahiscl.bill_mst.getunitname(a_1.gnum_hospital_code, max(a_1.gnum_rate_unit_code)))::text)), (1), ((nvl(sum(a_1.hblnum_net_amount), 0::numeric) / nvl(sum(a_1.hblnum_bill_qty), 0::numeric))), (ahiscl.ipd_mst.getwardname(a_1.hipnum_ward_code, a_1.gnum_hospital_code, NULL::character varying, NULL::character varying)), (ahiscl.bill_mst.getipdchargetypename(a_1.sblnum_ipd_chargetype_id, 0::numeric, 0::numeric, a_1.gnum_hospital_code)), (to_date('01-01-1970'::character varying, 'DD-MM-YYYY'::character varying)), a_1.hblnum_group_id, (nvl((SubPlan 1), 0::numeric)), (nvl((SubPlan 2), 0::numeric)), (nvl((SubPlan 3), 0::numeric)), (nvl((SubPlan 4), 0::numeric)), ((nvl(sum(a_1.hblnum_net_amount), 0::numeric) / nvl(sum(a_1.hblnum_bill_qty), 0::numeric))), ((SubPlan 5)), (nvl((SubPlan 6), 0::numeric)), (ahiscl.bill_mst.get_ipd_billing_discount(a_1.gnum_hospital_code, a_1.hblnum_pataccount_no)), ((SubPlan 7)), (nvl((SubPlan 8), 0::numeric)), (DECODE( (SubPlan 9) , 0 , 0 , (SubPlan 12) )), (ahiscl.bill_mst.getcatgrp(a_1.gnum_patient_cat_code, a_1.gnum_hospital_code)), (ahiscl.bill_mst.get_room_tariff_surcharge(1::numeric, a_1.gnum_hospital_code, a_1.hblnum_pataccount_no)), ((SubPlan 13)), ((SubPlan 14)), bed1.acc_no, (to_date(edb_ora_timestamp_tochar_immutable(bed1.stay_date, 'DD-MM-YYYY'::character varying), 'DD-MM-YYYY'::character varying))
50. 0.000 0.000 ↓ 0.0

HashAggregate (cost=26,122.36..26,122.38 rows=1 width=632) (actual rows= loops=)

  • Group Key: (nvl(a_1.gstr_tariff_extra_info, DECODE( nvl(a_1.hblnum_tariff_id, (0)::numeric) , 0 , ahiscl.bill_mst.getbservicename(a_1.sblnum_bservice_id, (0)::numeric, a_1.gnum_hospital_code, (0)::numeric) , 1020002 , ((((((ahiscl.bill_mst.gettariffnamewithcode(a_1.hblnum_tariff_id, (0)::numeric, a_1.gnum_hospital_code))::text || '-'::text) || (ahiscl.ipd_mst.getwardname(a_1.hipnum_ward_code, a_1.gnum_hospital_code, NULL::character varying, NULL::character varying))::text) || '-( '::text) || (ahiscl.bill_mst.getipdchargetypename(a_1.sblnum_ipd_chargetype_id, (0)::numeric, (0)::numeric, a_1.gnum_hospital_code))::text) || ')'::text) , ahiscl.bill_mst.gettariffnamewithcode(a_1.hblnum_tariff_id, (0)::numeric, a_1.gnum_hospital_code) ))), to_date('01-01-1970'::character varying, 'DD-MM-YYYY'::character varying), to_date('01-01-1970'::character varying, 'DD-MM-YYYY'::character varying), ((((nvl(max(a_1.hblnum_tariff_rate), 0::numeric))::text || '/'::text) || (ahiscl.bill_mst.getunitname(a_1.gnum_hospital_code, max(a_1.gnum_rate_unit_code)))::text)), 1, ((nvl(sum(a_1.hblnum_net_amount), 0::numeric) / nvl(sum(a_1.hblnum_bill_qty), 0::numeric))), (ahiscl.ipd_mst.getwardname(a_1.hipnum_ward_code, a_1.gnum_hospital_code, NULL::character varying, NULL::character varying)), (ahiscl.bill_mst.getipdchargetypename(a_1.sblnum_ipd_chargetype_id, 0::numeric, 0::numeric, a_1.gnum_hospital_code)), (to_date('01-01-1970'::character varying, 'DD-MM-YYYY'::character varying)), a_1.hblnum_group_id, (nvl((SubPlan 1), 0::numeric)), (nvl((SubPlan 2), 0::numeric)), (nvl((SubPlan 3), 0::numeric)), (nvl((SubPlan 4), 0::numeric)), ((nvl(sum(a_1.hblnum_net_amount), 0::numeric) / nvl(sum(a_1.hblnum_bill_qty), 0::numeric))), ((SubPlan 5)), (nvl((SubPlan 6), 0::numeric)), (ahiscl.bill_mst.get_ipd_billing_discount(a_1.gnum_hospital_code, a_1.hblnum_pataccount_no)), ((SubPlan 7)), (nvl((SubPlan 8), 0::numeric)), (DECODE( (SubPlan 9) , 0 , 0 , (SubPlan 12) )), (ahiscl.bill_mst.getcatgrp(a_1.gnum_patient_cat_code, a_1.gnum_hospital_code)), (ahiscl.bill_mst.get_room_tariff_surcharge(1::numeric, a_1.gnum_hospital_code, a_1.hblnum_pataccount_no)), ((SubPlan 13)), ((SubPlan 14)), bed1.acc_no, to_date(edb_ora_timestamp_tochar_immutable(bed1.stay_date, 'DD-MM-YYYY'::character varying), 'DD-MM-YYYY'::character varying)
51. 0.000 0.000 ↓ 0.0

Nested Loop (cost=25,218.26..26,122.29 rows=1 width=632) (actual rows= loops=)

  • Join Filter: ((trunc(to_date(edb_ora_timestamp_tochar_immutable(bed1.stay_date, 'DD-MM-YYYY'::character varying), 'DD-MM-YYYY'::character varying)) >= trunc((trunc(a_1.hbldt_req_date)))) AND (trunc(to_date(edb_ora_timestamp_tochar_immutable(bed1.stay_date, 'DD-MM-YYYY'::character varying), 'DD-MM-YYYY'::character varying)) <= DECODE( (nvl(sum(a_1.hblnum_bill_qty), 0::numeric)) , 1 , trunc((trunc(a_1.hbldt_req_date))) , trunc(((trunc(a_1.hbldt_req_date) + (nvl(sum(a_1.hblnum_bill_qty), 0::numeric) - 1::numeric)))) )))
52. 0.000 0.000 ↓ 0.0

Sort (cost=25,218.26..25,218.27 rows=1 width=123) (actual rows= loops=)

  • Sort Key: (ahiscl.ipd_mst.getwardname(a_1.hipnum_ward_code, a_1.gnum_hospital_code, NULL::character varying, NULL::character varying)), (ahiscl.bill_mst.getipdchargetypename(a_1.sblnum_ipd_chargetype_id, 0::numeric, 0::numeric, a_1.gnum_hospital_code)), (nvl(a_1.gstr_tariff_extra_info, DECODE( nvl(a_1.hblnum_tariff_id, (0)::numeric) , 0 , ahiscl.bill_mst.getbservicename(a_1.sblnum_bservice_id, (0)::numeric, a_1.gnum_hospital_code, (0)::numeric) , 1020002 , ((((((ahiscl.bill_mst.gettariffnamewithcode(a_1.hblnum_tariff_id, (0)::numeric, a_1.gnum_hospital_code))::text || '-'::text) || (ahiscl.ipd_mst.getwardname(a_1.hipnum_ward_code, a_1.gnum_hospital_code, NULL::character varying, NULL::character varying))::text) || '-( '::text) || (ahiscl.bill_mst.getipdchargetypename(a_1.sblnum_ipd_chargetype_id, (0)::numeric, (0)::numeric, a_1.gnum_hospital_code))::text) || ')'::text) , ahiscl.bill_mst.gettariffnamewithcode(a_1.hblnum_tariff_id, (0)::numeric, a_1.gnum_hospital_code) )))
53. 0.000 0.000 ↓ 0.0

HashAggregate (cost=156.85..25,218.25 rows=1 width=123) (actual rows= loops=)

  • Group Key: a_1.gnum_hospital_code, a_1.hipnum_ward_code, a_1.hblnum_group_id, a_1.hblnum_tariff_id, a_1.sblnum_bservice_id, a_1.sblnum_ipd_chargetype_id, a_1.hbldt_req_date, a_1.gstr_tariff_extra_info, a_1.hblnum_pataccount_no, a_1.hbldt_start_date, a_1.hbldt_end_date, a_1.hrgnum_puk, a_1.gnum_isvalid, a_1.gnum_patient_cat_code
54. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl a_1 (cost=0.43..156.57 rows=5 width=123) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = 331017200000946::numeric)
  • Filter: ((hblnum_group_id <> 100::numeric) AND (hblnum_reciept_no > 0::numeric) AND (hblnum_tariff_id > 0::numeric) AND (hblnum_req_no > 0::numeric) AND (hblnum_tariff_id <> 1090001::numeric) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (sblnum_chargetype_id = 2::numeric) AND (hblnum_group_id = 102::numeric) AND (gnum_hospital_code = 33101::numeric) AND (nvl(hblnum_net_amount, 0::numeric) <> 0::numeric))
55.          

SubPlan (for HashAggregate)

56. 0.000 0.000 ↓ 0.0

Limit (cost=159.64..159.64 rows=1 width=13) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Sort (cost=159.64..159.65 rows=2 width=13) (actual rows= loops=)

  • Sort Key: b.gdt_entry_date
58. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b (cost=0.43..159.63 rows=2 width=13) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a_1.hblnum_pataccount_no)
  • Filter: ((gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (sblnum_bservice_id = 21::numeric) AND (gnum_hospital_code = a_1.gnum_hospital_code))
59. 0.000 0.000 ↓ 0.0

Aggregate (cost=161.15..161.17 rows=1 width=5) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_1 (cost=0.43..161.08 rows=29 width=5) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a_1.hblnum_pataccount_no)
  • Filter: ((hblnum_tariff_id <> ALL ('{1020003,1090001}'::numeric[])) AND (hblnum_group_id <> 100::numeric) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (hblnum_group_id = 107::numeric) AND (sblnum_chargetype_id = 2::numeric) AND (gnum_hospital_code = a_1.gnum_hospital_code))
61. 0.000 0.000 ↓ 0.0

Aggregate (cost=161.20..161.21 rows=1 width=5) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_2 (cost=0.43..161.08 rows=46 width=5) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a_1.hblnum_pataccount_no)
  • Filter: ((hblnum_tariff_id <> ALL ('{1020003,1090001}'::numeric[])) AND (hblnum_group_id <> 100::numeric) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (hblnum_group_id = 124::numeric) AND (sblnum_chargetype_id = 2::numeric) AND (gnum_hospital_code = a_1.gnum_hospital_code))
63. 0.000 0.000 ↓ 0.0

Aggregate (cost=161.05..161.06 rows=1 width=5) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_3 (cost=0.43..160.84 rows=84 width=5) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a_1.hblnum_pataccount_no)
  • Filter: ((hblnum_tariff_id <> ALL ('{1020003,1090001}'::numeric[])) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (sblnum_chargetype_id = 2::numeric) AND (gnum_hospital_code = a_1.gnum_hospital_code) AND (hblnum_group_id <> ALL ('{107,124,100}'::numeric[])))
65. 0.000 0.000 ↓ 0.0

Seq Scan on hblt_pataccount_dtl b_4 (cost=0.00..6,668.49 rows=1 width=4) (actual rows= loops=)

  • Filter: ((gnum_isvalid = 1::numeric) AND (gnum_hospital_code = a_1.gnum_hospital_code) AND (hblnum_pataccount_no = a_1.hblnum_pataccount_no))
66. 0.000 0.000 ↓ 0.0

Aggregate (cost=484.92..484.94 rows=1 width=9) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=0.43..484.92 rows=1 width=9) (actual rows= loops=)

  • Join Filter: (b_5.hblnum_tariff_id = c.hblnum_tariff_id)
68. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_5 (cost=0.43..161.08 rows=4 width=36) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a_1.hblnum_pataccount_no)
  • Filter: ((hblnum_tariff_rate > 0::numeric) AND (hblnum_bill_qty > 0::numeric) AND (hblnum_tariff_id > 0::numeric) AND (hblnum_discount_amt <= 0::numeric) AND (gnum_isvalid = 1::numeric) AND (hblnum_net_amount = 0::numeric) AND (gnum_hospital_code = a_1.gnum_hospital_code))
69. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..323.72 rows=2 width=29) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on hblt_pataccount_package_dtl c (cost=0.00..323.71 rows=2 width=29) (actual rows= loops=)

  • Filter: ((gnum_isvalid = 1::numeric) AND (gnum_hospital_code = a_1.gnum_hospital_code) AND (hblnum_pataccount_no = a_1.hblnum_pataccount_no))
71. 0.000 0.000 ↓ 0.0

Limit (cost=0.43..161.08 rows=1 width=4) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl pat_s (cost=0.43..161.08 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a_1.hblnum_pataccount_no)
  • Filter: ((sblnum_bservice_id = ANY ('{19,20}'::numeric[])) AND (hblnum_credit_bill_flag = ANY ('{1,0}'::numeric[])) AND (gnum_patient_cat_code <> 69::numeric) AND (hblnum_client_no = ANY ('{1000,2000,1938,1940,2012,2010,2013,2011}'::numeric[])))
73. 0.000 0.000 ↓ 0.0

Aggregate (cost=159.64..159.65 rows=1 width=3) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl pat_ser (cost=0.43..159.63 rows=1 width=3) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a_1.hblnum_pataccount_no)
  • Filter: ((hblnum_amt_paid_by_client IS NOT NULL) AND (sblnum_bservice_id = ANY ('{19,20}'::numeric[])) AND (hblnum_amt_paid_by_client <> 0::numeric) AND (hblnum_status = 1::numeric) AND (gnum_isvalid = 1::numeric))
75. 0.000 0.000 ↓ 0.0

Index Scan using index_adm on hblt_pataccount_dtl (cost=0.42..4,152.98 rows=1 width=3) (actual rows= loops=)

  • Index Cond: (hrgnum_puk = a_1.hrgnum_puk)
  • Filter: ((hblnum_pataccount_no = a_1.hblnum_pataccount_no) AND (gnum_isvalid = a_1.gnum_isvalid))
76. 0.000 0.000 ↓ 0.0

Aggregate (cost=8,468.02..8,468.04 rows=1 width=9) (actual rows= loops=)

77.          

Initplan (for Aggregate)

78. 0.000 0.000 ↓ 0.0

Index Scan using index_adm on hblt_pataccount_dtl hblt_pataccount_dtl_1 (cost=0.42..4,152.98 rows=1 width=3) (actual rows= loops=)

  • Index Cond: (hrgnum_puk = a_1.hrgnum_puk)
  • Filter: ((hblnum_pataccount_no = a_1.hblnum_pataccount_no) AND (gnum_isvalid = a_1.gnum_isvalid))
79. 0.000 0.000 ↓ 0.0

Index Scan using index_adm on hblt_pataccount_dtl hblt_pataccount_dtl_2 (cost=0.42..4,152.98 rows=1 width=3) (actual rows= loops=)

  • Index Cond: (hrgnum_puk = a_1.hrgnum_puk)
  • Filter: ((hblnum_pataccount_no = a_1.hblnum_pataccount_no) AND (gnum_isvalid = a_1.gnum_isvalid))
80. 0.000 0.000 ↓ 0.0

Result (cost=0.43..162.05 rows=1 width=9) (actual rows= loops=)

  • One-Time Filter: (a_1.gnum_isvalid = 1::numeric)
81. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_6 (cost=0.43..162.05 rows=1 width=9) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a_1.hblnum_pataccount_no)
  • Filter: ((hblnum_reciept_no > 0::numeric) AND (hblnum_tariff_id >= 0::numeric) AND (hblnum_req_no > 0::numeric) AND ($21 < hblnum_net_amount) AND (gnum_hospital_code = a_1.gnum_hospital_code) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (hblnum_group_id = 102::numeric) AND (hblnum_tariff_id = 1020002::numeric))
82. 0.000 0.000 ↓ 0.0

Aggregate (cost=167.28..167.29 rows=1 width=18) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Index Scan using "IDX_ACC_NO" on hblt_pataccount_service_dtl b_7 (cost=0.43..162.04 rows=161 width=18) (actual rows= loops=)

  • Index Cond: (hblnum_pataccount_no = a_1.hblnum_pataccount_no)
  • Filter: ((hblnum_reciept_no > 0::numeric) AND (hblnum_tariff_id >= 0::numeric) AND (hblnum_req_no > 0::numeric) AND (hblnum_group_id <> 100::numeric) AND (hblnum_tariff_id <> 1090001::numeric) AND (gnum_isvalid = 1::numeric) AND (hblnum_status = 1::numeric) AND (sblnum_chargetype_id = 2::numeric) AND (gnum_hospital_code = a_1.gnum_hospital_code))
84. 0.000 0.000 ↓ 0.0

Index Scan using index_adm on hblt_pataccount_dtl hblt_pataccount_dtl_3 (cost=0.42..4,152.98 rows=1 width=5) (actual rows= loops=)

  • Index Cond: (hrgnum_puk = a_1.hrgnum_puk)
  • Filter: ((gnum_isvalid = 1::numeric) AND (hblnum_pataccount_no = a_1.hblnum_pataccount_no))
85. 0.000 0.000 ↓ 0.0

Seq Scan on bed_stay_dtl bed1 (cost=0.00..903.69 rows=8 width=19) (actual rows= loops=)

  • Filter: (acc_no = 331017200000946::numeric)