Forum Stats

  • 3,751,469 Users
  • 2,250,366 Discussions
  • 7,867,434 Comments

Discussions

ROW DATA DUPLICATE 34 TIMES IN REPORT BUILDER 10G

User_8ZVFA
User_8ZVFA Member Posts: 2 Green Ribbon
edited Apr 16, 2021 3:32AM in Reports

Hello everyone, i am getting problem with my query.

I've been modified the query and getting duplicates row 34 times.

The value of cost that I get is 55.390.000 AND the value of cost (from original query) is 1.635.000.

If i count 55.390.000 : 1.635.000 = 34 times. and the output in reports, its duplicates 34 times.

But i dont know which query that makes it duplicates. Please help me. I'll show you my modified query below.


Notes : Im using REPORTS BUILDER 10G for output report, and TOAD for ORACLE 9.0.1 for compile query

________________________________________________________________________________________________

  1. SELECT/+ ordered /
  2. ad.asset_number || ' - ' || ad.description asset_num_desc,
  3. &ACCT_FLEX_BAL_SEG comp_code,
  4. falu.meaning asset_type,
  5. DECODE (ah.asset_type,
  6. 'CIP', cb.cip_cost_acct,
  7. cb.asset_cost_acct) account,
  8. dhcc.segment4 Division,
  9. dhcc.segment6 Depo,
  10. &ACCT_FLEX_COST_SEG cost_center,
  11. ad.asset_number,
  12. ret.date_retired,
  13. th.transaction_type_code,
  14. th.book_type_code,
  15. th.asset_id,
  16. books.date_placed_in_service,
  17. SUM(DECODE(aj.adjustment_type, 'COST', 1, 'CIP COST', 1, 0)
  18. DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0)
  19. aj.adjustment_amount) cost,
  20. SUM(DECODE(aj.adjustment_type, 'NBV RETIRED', -1, 0)
  21. DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0)
  22. aj.adjustment_amount) nbv,
  23. / round(decode(ret.units, null,
  24. (decode(th.transaction_type_code, 'REINSTATEMENT',
  25. -ret.proceeds_of_sale, ret.proceeds_of_sale)
  26. (dh.units_assigned / ah.units)),
  27. (decode(th.transaction_type_code, 'REINSTATEMENT',
  28. -ret.proceeds_of_sale, ret.proceeds_of_sale)
  29. nvl(-dh.transaction_units,dh.units_assigned) / ret.units)), 4) proceeds, /
  30. SUM(DECODE(aj.adjustment_type, 'PROCEEDS CLR', 1, 'PROCEEDS', 1, 0)
  31. DECODE(aj.debit_credit_flag, 'DR', 1, 'CR', -1, 0)
  32. aj.adjustment_amount) proceeds,
  33. SUM(DECODE(aj.adjustment_type, 'REMOVALCOST', -1, 0)
  34. DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0)
  35. aj.adjustment_amount) removal,
  36. SUM(DECODE(aj.adjustment_type,'REVAL RSV RET',1,0)
  37. DECODE(aj.debit_credit_flag, 'DR',-1,'CR',1,0)
  38. aj.adjustment_amount) reval_rsv_ret,
  39. th.transaction_header_id,
  40. DECODE (th.transaction_type_code,
  41. 'REINSTATEMENT', '*','PARTIAL RETIREMENT','P',
  42. TO_CHAR(NULL)) code
  43. FROM
  44. fa_transaction_headers th,
  45. fa_additions ad,
  46. fa_books books,
  47. fa_retirements ret,
  48. fa_adjustments aj,
  49. fa_distribution_history dh,
  50. gl_code_combinations dhcc,
  51. fa_asset_history ah,
  52. fa_category_books cb,
  53. fa_lookups falu
  54. WHERE
  55. th.date_effective >= :PERIOD1_POD AND
  56. th.date_effective <= :PERIOD2_PCD AND
  57. th.transaction_key = 'R'
  58. AND
  59. ret.asset_id = books.asset_id AND
  60. DECODE (th.transaction_type_code,
  61. 'REINSTATEMENT', ret.transaction_header_id_out,
  62. ret.transaction_header_id_in) = th.transaction_header_id
  63. AND
  64. ad.asset_id = th.asset_id
  65. AND
  66. aj.asset_id = ret.asset_id
  67. AND aj.adjustment_type NOT IN (SELECT 'PROCEEDS' FROM fa_adjustments aj1
  68. WHERE aj1.book_type_code = aj.book_type_code
  69. AND aj1.asset_id = aj.asset_id
  70. AND aj1.transaction_header_id = aj.transaction_header_id
  71. AND aj1.adjustment_type = 'PROCEEDS CLR')
  72. AND aj.transaction_header_id = th.transaction_header_id
  73. AND
  74. ah.asset_id = ad.asset_id AND
  75. ah.date_effective <= th.date_effective AND
  76. NVL(ah.date_ineffective, th.date_effective+1)
  77. > th.date_effective
  78. AND
  79. falu.lookup_code = ah.asset_type AND
  80. falu.lookup_type = 'ASSET TYPE'
  81. AND
  82. books.transaction_header_id_out
  83. = th.transaction_header_id AND
  84. books.asset_id = ad.asset_id
  85. AND
  86. cb.category_id = ah.category_id
  87. AND
  88. dh.distribution_id = aj.distribution_id
  89. / AND (dh.date_effective <= th.date_effective
  90. OR nvl(dh.date_ineffective, th.date_effective+1) >= th.date_effective)
  91. AND th.book_type_code = dh.book_type_code /
  92. AND th.asset_id = dh.asset_id
  93. AND
  94. dhcc.code_combination_id = dh.code_combination_id
  95. GROUP BY
  96. ad.asset_number,
  97. falu.meaning,
  98. &ACCT_FLEX_BAL_SEG,
  99. dhcc.segment4,
  100. dhcc.segment6,
  101. &ACCT_FLEX_COST_SEG,
  102. th.transaction_type_code,
  103. th.book_type_code,
  104. th.asset_id,
  105. cb.asset_cost_acct,
  106. cb.cip_cost_acct,
  107. ad.description,
  108. books.date_placed_in_service,
  109. ret.date_retired,
  110. th.transaction_header_id,
  111. ah.asset_type,
  112. ret.gain_loss_amount
  113. ORDER BY 1,2,3,4,5,6, 7, 8


____________________________________________________________________________________________________

and this is the original.

I delete the parameter of book, because thats the request from client. They want to show the all data (not by book)


___________________________________________________________________________________________________

  1. SELECT /*+ ordered */
  2. &ACCT_FLEX_BAL_SEG comp_code,
  3. falu.meaning asset_type,
  4. DECODE (ah.asset_type,
  5. 'CIP', cb.cip_cost_acct,
  6. cb.asset_cost_acct) account,
  7. dhcc.segment4 Division,
  8. dhcc.segment5 Depo,
  9. &ACCT_FLEX_COST_SEG cost_center,
  10. ad.asset_number,
  11. ret.date_retired,
  12. ad.asset_number || ' - ' || ad.description asset_num_desc,
  13. th.transaction_type_code,
  14. th.asset_id,
  15. books.date_placed_in_service,
  16. SUM(DECODE(aj.adjustment_type, 'COST', 1, 'CIP COST', 1, 0) *
  17. DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
  18. aj.adjustment_amount) cost,
  19. SUM(DECODE(aj.adjustment_type, 'NBV RETIRED', -1, 0) *
  20. DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
  21. aj.adjustment_amount) nbv,
  22. /* round(decode(ret.units, null,
  23. (decode(th.transaction_type_code, 'REINSTATEMENT',
  24. -ret.proceeds_of_sale, ret.proceeds_of_sale)
  25. * (dh.units_assigned / ah.units)),
  26. (decode(th.transaction_type_code, 'REINSTATEMENT',
  27. -ret.proceeds_of_sale, ret.proceeds_of_sale)
  28. * nvl(-dh.transaction_units,dh.units_assigned) / ret.units)), 4) proceeds, */
  29. SUM(DECODE(aj.adjustment_type, 'PROCEEDS CLR', 1, 'PROCEEDS', 1, 0) *
  30. DECODE(aj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) *
  31. aj.adjustment_amount) proceeds,
  32. SUM(DECODE(aj.adjustment_type, 'REMOVALCOST', -1, 0) *
  33. DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
  34. aj.adjustment_amount) removal,
  35. SUM(DECODE(aj.adjustment_type,'REVAL RSV RET',1,0)*
  36. DECODE(aj.debit_credit_flag, 'DR',-1,'CR',1,0)*
  37. aj.adjustment_amount) reval_rsv_ret,
  38. th.transaction_header_id,
  39. DECODE (th.transaction_type_code,
  40. 'REINSTATEMENT', '*','PARTIAL RETIREMENT','P',
  41. TO_CHAR(NULL)) code
  42. FROM
  43. fa_transaction_headers th,
  44. fa_additions ad,
  45. fa_books books,
  46. fa_retirements ret,
  47. fa_adjustments aj,
  48. fa_distribution_history dh,
  49. gl_code_combinations dhcc,
  50. fa_asset_history ah,
  51. fa_category_books cb,
  52. fa_lookups falu
  53. WHERE
  54. th.date_effective >= :PERIOD1_POD AND
  55. th.date_effective <= :PERIOD2_PCD AND
  56. th.book_type_code = :P_BOOK AND
  57. th.transaction_key = 'R'
  58. AND
  59. ret.book_type_code = :P_BOOK AND
  60. ret.asset_id = books.asset_id AND
  61. DECODE (th.transaction_type_code,
  62. 'REINSTATEMENT', ret.transaction_header_id_out,
  63. ret.transaction_header_id_in) = th.transaction_header_id
  64. AND
  65. ad.asset_id = th.asset_id
  66. AND
  67. aj.asset_id = ret.asset_id AND
  68. aj.book_type_code = :P_BOOK
  69. AND aj.adjustment_type NOT IN (SELECT 'PROCEEDS' FROM fa_adjustments aj1
  70. WHERE aj1.book_type_code = aj.book_type_code
  71. AND aj1.asset_id = aj.asset_id
  72. AND aj1.transaction_header_id = aj.transaction_header_id
  73. AND aj1.adjustment_type = 'PROCEEDS CLR')
  74. AND aj.transaction_header_id = th.transaction_header_id
  75. AND
  76. ah.asset_id = ad.asset_id AND
  77. ah.date_effective <= th.date_effective AND
  78. NVL(ah.date_ineffective, th.date_effective+1)
  79. > th.date_effective
  80. AND
  81. falu.lookup_code = ah.asset_type AND
  82. falu.lookup_type = 'ASSET TYPE'
  83. AND
  84. books.transaction_header_id_out
  85. = th.transaction_header_id AND
  86. books.book_type_code = :P_BOOK AND
  87. books.asset_id = ad.asset_id
  88. AND
  89. cb.category_id = ah.category_id AND
  90. cb.book_type_code = :P_BOOK
  91. AND
  92. dh.distribution_id = aj.distribution_id
  93. /* AND (dh.date_effective <= th.date_effective
  94. OR nvl(dh.date_ineffective, th.date_effective+1) >= th.date_effective)
  95. AND th.book_type_code = dh.book_type_code */
  96. AND th.asset_id = dh.asset_id
  97. AND
  98. dhcc.code_combination_id = dh.code_combination_id
  99. GROUP BY
  100. falu.meaning,
  101. &ACCT_FLEX_BAL_SEG,
  102. dhcc.segment4,
  103. dhcc.segment5,
  104. &ACCT_FLEX_COST_SEG,
  105. th.transaction_type_code,
  106. th.asset_id,
  107. cb.asset_cost_acct,
  108. cb.cip_cost_acct,
  109. ad.asset_number,
  110. ad.description,
  111. books.date_placed_in_service,
  112. ret.date_retired,
  113. th.transaction_header_id,
  114. ah.asset_type,
  115. ret.gain_loss_amount
  116. ORDER BY 1,2,3,4,5,6, 7, 8


Answers

  • User_8ZVFA
    User_8ZVFA Member Posts: 2 Green Ribbon

    [UPDATE]

    I've found whats wrong with my query that cause value cost : 55.590.000


    But in report builder 10g, the output still show row duplicates data


    Please help. I dont know whats wrong with the setting in reports builder 10g