Please someone help me.
I am facing the problem with the reports. The reports show many duplicates row. Im using REPORT BUILDER 10G.
Here's are my query.
I think there's something wrong with the setting report builder but i dont know
SELECT/*+ ordered */
ad.asset_number || ' - ' || ad.description asset_num_desc,
&ACCT_FLEX_BAL_SEG comp_code,
falu.meaning asset_type,
DECODE (ah.asset_type,
'CIP', cb.cip_cost_acct,
cb.asset_cost_acct) account,
dhcc.segment4 Division,
dhcc.segment6 Depo,
&ACCT_FLEX_COST_SEG cost_center,
ad.asset_number,
ret.date_retired,
th.transaction_type_code,
th.book_type_code,
th.asset_id,
books.date_placed_in_service,
SUM(DECODE(aj.adjustment_type, 'COST', 1, 'CIP COST', 1, 0) *
DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount) cost,
SUM(DECODE(aj.adjustment_type, 'NBV RETIRED', -1, 0) *
DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount) nbv,
/* round(decode(ret.units, null,
(decode(th.transaction_type_code, 'REINSTATEMENT',
-ret.proceeds_of_sale, ret.proceeds_of_sale)
* (dh.units_assigned / ah.units)),
(decode(th.transaction_type_code, 'REINSTATEMENT',
-ret.proceeds_of_sale, ret.proceeds_of_sale)
* nvl(-dh.transaction_units,dh.units_assigned) / ret.units)), 4) proceeds, */
SUM(DECODE(aj.adjustment_type, 'PROCEEDS CLR', 1, 'PROCEEDS', 1, 0) *
DECODE(aj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) *
aj.adjustment_amount) proceeds,
SUM(DECODE(aj.adjustment_type, 'REMOVALCOST', -1, 0) *
DECODE(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount) removal,
SUM(DECODE(aj.adjustment_type,'REVAL RSV RET',1,0)*
DECODE(aj.debit_credit_flag, 'DR',-1,'CR',1,0)*
aj.adjustment_amount) reval_rsv_ret,
th.transaction_header_id,
DECODE (th.transaction_type_code,
'REINSTATEMENT', '*','PARTIAL RETIREMENT','P',
TO_CHAR(NULL)) code
FROM
fa_transaction_headers th,
fa_additions ad,
fa_books books,
fa_retirements ret,
fa_adjustments aj,
fa_distribution_history dh,
gl_code_combinations dhcc,
fa_asset_history ah,
fa_category_books cb,
fa_lookups falu
WHERE
th.date_effective >= :PERIOD1_POD AND
th.date_effective <= :PERIOD2_PCD AND
th.transaction_key = 'R'
AND
ret.asset_id = books.asset_id AND
DECODE (th.transaction_type_code,
'REINSTATEMENT', ret.transaction_header_id_out,
ret.transaction_header_id_in) = th.transaction_header_id
AND
ad.asset_id = th.asset_id
AND
aj.asset_id = ret.asset_id
AND aj.adjustment_type NOT IN (SELECT 'PROCEEDS' FROM fa_adjustments aj1
WHERE aj1.book_type_code = aj.book_type_code
AND aj1.asset_id = aj.asset_id
AND aj1.transaction_header_id = aj.transaction_header_id
AND aj1.adjustment_type = 'PROCEEDS CLR')
AND aj.transaction_header_id = th.transaction_header_id
AND
ah.asset_id = ad.asset_id AND
ah.date_effective <= th.date_effective AND
NVL(ah.date_ineffective, th.date_effective+1)
> th.date_effective
AND
falu.lookup_code = ah.asset_type AND
falu.lookup_type = 'ASSET TYPE'
AND
books.transaction_header_id_out
= th.transaction_header_id AND
books.asset_id = ad.asset_id
AND
cb.category_id = ah.category_id
AND
dh.distribution_id = aj.distribution_id
/* AND (dh.date_effective <= th.date_effective
OR nvl(dh.date_ineffective, th.date_effective+1) >= th.date_effective)
AND th.book_type_code = dh.book_type_code */
AND th.asset_id = dh.asset_id
AND
dhcc.code_combination_id = dh.code_combination_id
GROUP BY
ad.asset_number,
falu.meaning,
&ACCT_FLEX_BAL_SEG,
dhcc.segment4,
dhcc.segment6,
&ACCT_FLEX_COST_SEG,
th.transaction_type_code,
th.book_type_code,
th.asset_id,
cb.asset_cost_acct,
cb.cip_cost_acct,
ad.description,
books.date_placed_in_service,
ret.date_retired,
th.transaction_header_id,
ah.asset_type,
ret.gain_loss_amount
ORDER BY 1,2,3,4,5,6, 7, 8