SELECT book_type_code,
asset_id,
asset_number,
description,
transnum,
AVG (to_from) to_from,
segment1,
segment2,
segment3,
location_segment,
transaction_date,
assigned_to,
ccid,
SUM (cost) cost,
SUM (deprn_reserve) deprn_reserve,
AVG (units) units,
trans_period_name
FROM ( SELECT th.book_type_code,
ad.asset_id,
ad.asset_number,
ad.description,
th.transaction_header_id transnum,
DECODE (th.transaction_header_id,
dh.transaction_header_id_in, 1,
dh.transaction_header_id_out, 0)
to_from,
--ACCT_FLEX_ACCT_SEG GL_ACCOUNT,
--ACCT_FLEX_BAL_SEG COMP_CODE,
--ACCT_FLEX_COST_SEG COST_CENTER,
-- LOC_FLEX_ALL_SEG LOCATION,
ascc.segment1,
ascc.segment2,
ascc.segment3,
loc.segment1
|| '-'
|| loc.segment2
|| '-'
|| loc.segment3
|| '-'
|| loc.segment4
|| '-'
|| loc.segment5
|| '-'
|| loc.segment6
|| '-'
|| loc.segment7
location_segment,
th.transaction_date_entered transaction_date,
dh.assigned_to assigned_to,
ascc.code_combination_id ccid,
SUM (
cadj.adjustment_amount
* DECODE (cadj.debit_credit_flag, 'CR', -1, 'DR', 1))
cost,
0 deprn_reserve,
SUM (
DISTINCT DECODE (th.transaction_header_id,
dh.transaction_header_id_in, 1,
dh.transaction_header_id_out, -1)
* dh.units_assigned)
units,
apps_fa_util_api.
get_deprn_period_name_by_date (th.book_type_code,
th.date_effective)
trans_period_name
FROM fa_locations loc,
fa_additions ad,
gl_code_combinations ascc,
fa_distribution_history dh,
fa_transaction_headers th,
fa_adjustments cadj
WHERE --th.book_type_code = p_book
th.transaction_type_code = 'TRANSFER'
---AND TH.DATE_EFFECTIVE >=
:PERIOD1_POD AND
TH.DATE_EFFECTIVE <= :PERIOD1_PCD
--AND nvl(TH.MASS_REFERENCE_ID, 0) =
NVL(:P_MASS_REF_ID, nvl(TH.MASS_REFERENCE_ID, 0))
AND (th.transaction_header_id = dh.transaction_header_id_in
OR th.transaction_header_id =
dh.transaction_header_id_out)
AND ad.asset_id = th.asset_id
AND loc.location_id = dh.location_id
AND ascc.code_combination_id = dh.code_combination_id
AND cadj.book_type_code = th.book_type_code ---:p_book
AND cadj.asset_id = th.asset_id
AND cadj.distribution_id = dh.distribution_id
AND cadj.transaction_header_id = th.transaction_header_id
AND cadj.source_type_code = 'TRANSFER'
AND cadj.adjustment_type IN ('COST', 'CIP COST')
--LP_WHERE_CLAUSE1
GROUP BY th.book_type_code,
th.transaction_header_id,
DECODE (th.transaction_header_id,
dh.transaction_header_id_in, 1,
dh.transaction_header_id_out, 0),
dh.distribution_id,
--ACCT_FLEX_ACCT_SEG,
--ACCT_FLEX_BAL_SEG,
--ACCT_FLEX_COST_SEG,
--LOC_FLEX_ALL_SEG,
ascc.segment1,
ascc.segment2,
ascc.segment3,
loc.segment1
|| '-'
|| loc.segment2
|| '-'
|| loc.segment3
|| '-'
|| loc.segment4
|| '-'
|| loc.segment5
|| '-'
|| loc.segment6
|| '-'
|| loc.segment7,
ad.asset_number,
ad.description,
th.transaction_date_entered,
dh.assigned_to,
ascc.code_combination_id,
ad.asset_id,
apps_fa_util_api.
get_deprn_period_name_by_date (th.book_type_code,
th.date_effective)
UNION
SELECT th.book_type_code,
ad.asset_id,
ad.asset_number,
ad.description,
th.transaction_header_id transnum,
DECODE (th.transaction_header_id,
dh.transaction_header_id_in, 1,
dh.transaction_header_id_out, 0)
to_from,
--ACCT_FLEX_ACCT_SEG GL_ACCOUNT,
--ACCT_FLEX_BAL_SEG COMP_CODE,
--ACCT_FLEX_COST_SEG COST_CENTER,
--LOC_FLEX_ALL_SEG LOCATION,
ascc.segment1,
ascc.segment2,
ascc.segment3,
loc.segment1
|| '-'
|| loc.segment2
|| '-'
|| loc.segment3
|| '-'
|| loc.segment4
|| '-'
|| loc.segment5
|| '-'
|| loc.segment6
|| '-'
|| loc.segment7
location_segment,
th.transaction_date_entered start_date,
dh.assigned_to assigned_to,
ascc.code_combination_id ccid,
0 cost,
SUM (
NVL (radj.adjustment_amount, 0)
* DECODE (NVL (radj.debit_credit_flag, 'CR'),
'CR', 1,
'DR', -1))
deprn_reserve,
SUM (
DISTINCT DECODE (th.transaction_header_id,
dh.transaction_header_id_in, 1,
dh.transaction_header_id_out, -1)
* dh.units_assigned)
units,
apps_fa_util_api.
get_deprn_period_name_by_date (th.book_type_code,
th.date_effective)
trans_period_name
FROM fa_locations loc,
fa_additions ad,
gl_code_combinations ascc,
fa_distribution_history dh,
fa_transaction_headers th,
fa_adjustments radj
WHERE --th.book_type_code = p_book
th.transaction_type_code = 'TRANSFER'
--- AND
TH.DATE_EFFECTIVE >= :PERIOD1_POD AND TH.DATE_EFFECTIVE <=
:PERIOD1_PCD
--AND nvl(TH.MASS_REFERENCE_ID, 0) =
NVL(:P_MASS_REF_ID, nvl(TH.MASS_REFERENCE_ID, 0))
AND (th.transaction_header_id = dh.transaction_header_id_in
OR th.transaction_header_id =
dh.transaction_header_id_out)
AND ad.asset_id = th.asset_id
AND loc.location_id = dh.location_id
AND ascc.code_combination_id = dh.code_combination_id
AND radj.book_type_code = th.book_type_code ---:p_book
AND radj.asset_id = th.asset_id
AND radj.distribution_id = dh.distribution_id
AND radj.source_type_code = 'TRANSFER'
AND radj.adjustment_type = 'RESERVE'
AND radj.transaction_header_id = th.transaction_header_id
---LP_WHERE_CLAUSE2
GROUP BY th.book_type_code,
th.transaction_header_id,
DECODE (th.transaction_header_id,
dh.transaction_header_id_in, 1,
dh.transaction_header_id_out, 0),
dh.distribution_id,
--ACCT_FLEX_ACCT_SEG,
--ACCT_FLEX_BAL_SEG,
--ACCT_FLEX_COST_SEG,
--LOC_FLEX_ALL_SEG,
ascc.segment1,
ascc.segment2,
ascc.segment3,
loc.segment1
|| '-'
|| loc.segment2
|| '-'
|| loc.segment3
|| '-'
|| loc.segment4
|| '-'
|| loc.segment5
|| '-'
|| loc.segment6
|| '-'
|| loc.segment7,
ad.asset_id,
ad.asset_number,
ad.description,
th.transaction_date_entered,
dh.assigned_to,
ascc.code_combination_id,
apps_fa_util_api.
get_deprn_period_name_by_date (th.book_type_code,
th.date_effective)
ORDER BY 1,
3,
5,
6,
7,
8,
9,
10)
---WHERE book_type_code = :book_type_code_
GROUP BY book_type_code,
asset_id,
asset_number,
description,
transnum,
segment1,
segment2,
segment3,
location_segment,
transaction_date,
assigned_to,
ccid,
trans_period_name
ORDER BY 1,
3,
5,
6,
7,
8,
9,
10;
No comments:
Post a Comment