SELECT &ACCT_FLEX_BAL_SEG COMP_CODE,
CB.ASSET_COST_ACCT ASSET_ACCOUNT,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ASSET_NUMBER,
Round(sum(BOOKS.ORIGINAL_COST * nvl(DH.UNITS_ASSIGNED,AH.UNITS) /
AH.UNITS), :PRECISION) ORIGINAL_COST,
BOOKS.DATE_PLACED_IN_SERVICE IN_SERVICE,
fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP
FROM FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_ADDITIONS AD,
FA_BOOK_CONTROLS BC,
FA_TRANSACTION_HEADERS THADD,
FA_TRANSACTION_HEADERS THTRANSIN,
FA_BOOKS BOOKS,
GL_CODE_COMBINATIONS DHCC,
FA_DISTRIBUTION_HISTORY DH
WHERE
BOOKS.BOOK_TYPE_CODE = :P_BOOK AND
BOOKS.DATE_PLACED_IN_SERVICE >= :P_START_DATE AND
BOOKS.DATE_PLACED_IN_SERVICE <= :P_END_DATE AND
THADD.TRANSACTION_TYPE_CODE = 'ADDITION'AND
THADD.BOOK_TYPE_CODE = :P_BOOK AND
THADD.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND
AD.ASSET_ID = BOOKS.ASSET_ID
AND
AH.ASSET_ID = BOOKS.ASSET_ID AND
AH.DATE_EFFECTIVE <= THADD.DATE_EFFECTIVE AND
nvl(AH.DATE_INEFFECTIVE,sysdate)> THADD.DATE_EFFECTIVE
AND
CB.CATEGORY_ID = AH.CATEGORY_ID AND
CB.BOOK_TYPE_CODE = :P_BOOK AND
DH.BOOK_TYPE_CODE = :Distribution_Source_Book AND
DH.ASSET_ID = THADD.ASSET_ID
AND
THTRANSIN.BOOK_TYPE_CODE = THADD.BOOK_TYPE_CODE AND
THTRANSIN.ASSET_ID = THADD.ASSET_ID AND
THTRANSIN.TRANSACTION_TYPE_CODE = decode(BC.BOOK_CLASS,'CORPORATE', 'TRANSFER IN','ADDITION') AND
THTRANSIN.DATE_EFFECTIVE >= DH.DATE_EFFECTIVE AND
THTRANSIN.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE,SYSDATE) AND
THTRANSIN.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE
AND
DH.ASSET_ID NOT IN
(Select thx.asset_id
From fa_transaction_headers thx,
fa_transaction_headers thadd2,
fa_asset_history ahx,
fa_transaction_headers thtrans
Where thtrans.asset_id = ahx.asset_id
and thtrans.transaction_type_code in ('TRANSFER IN', 'TRANSFER IN/VOID', 'UNIT ADJUSTMENT', 'TRANSFER')
and thtrans.book_type_code = thx.book_type_code
and thtrans.date_effective between ahx.date_effective and nvl(ahx.date_ineffective, sysdate)
and ahx.asset_id = dh.asset_id
and ahx.asset_type = 'CIP'
and thx.transaction_header_id < thadd2.transaction_header_id
and thadd2.book_type_code = thx.book_type_code
and thadd2.asset_id = thx.asset_id
and thadd2.transaction_type_code = 'ADDITION'
and thx.asset_id = thadd.asset_id
and thx.book_type_code = thadd.book_type_code
and thx.transaction_type_code in ('UNIT ADJUSTMENT', 'TRANSFER IN', 'TRANSFER IN/VOID', 'TRANSFER') )
AND
DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
GROUP BY
&ACCT_FLEX_BAL_SEG,fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
CB.ASSET_COST_ACCT,
AD.ASSET_NUMBER,
AD.DESCRIPTION,
BOOKS.DATE_PLACED_IN_SERVICE
UNION
Select &ACCT_FLEX_BAL_SEG,
CB.ASSET_COST_ACCT,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,
Round(sum(BOOKS.ORIGINAL_COST * nvl(DH.UNITS_ASSIGNED,AH.UNITS) /
AH.UNITS),:PRECISION) ,
BOOKS.DATE_PLACED_IN_SERVICE,
fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP
FROM
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_ADDITIONS AD,
FA_TRANSACTION_HEADERS THADD,
FA_BOOKS BOOKS,
GL_CODE_COMBINATIONS DHCC,
FA_DISTRIBUTION_HISTORY DH
WHERE
BOOKS.BOOK_TYPE_CODE = :P_BOOK AND
BOOKS.DATE_PLACED_IN_SERVICE >= :P_START_DATE AND
BOOKS.DATE_PLACED_IN_SERVICE <= :P_END_DATE
AND
THADD.TRANSACTION_TYPE_CODE = 'ADDITION' AND
THADD.BOOK_TYPE_CODE = :P_BOOK AND
THADD.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND
AD.ASSET_ID = BOOKS.ASSET_ID
AND
AH.ASSET_ID = BOOKS.ASSET_ID AND
AH.DATE_EFFECTIVE <= THADD.DATE_EFFECTIVE AND
nvl(AH.DATE_INEFFECTIVE,sysdate)> THADD.DATE_EFFECTIVE
AND
CB.CATEGORY_ID = AH.CATEGORY_ID AND
CB.BOOK_TYPE_CODE = :P_BOOK AND
DH.ASSET_ID = AD.ASSET_ID AND
EXISTS
( Select NULL
From fa_transaction_headers thx,
fa_transaction_headers thadd2,
fa_asset_history ahx,
fa_transaction_headers thtrans,
fa_distribution_history dhx
Where dh.transaction_header_id_in = thx.transaction_header_id
and thadd2.asset_id = dh.asset_id
and ahx.asset_id = thadd2.asset_id
and thtrans.asset_id = ahx.asset_id
and dhx.asset_id = thadd2.asset_id
and thadd2.asset_id = thx.asset_id
and thtrans.transaction_type_code in ('TRANSFER IN',
'TRANSFER IN/VOID', 'UNIT ADJUSTMENT', 'TRANSFER','RECLASS')
and thtrans.book_type_code = thx.book_type_code
and thtrans.date_effective
between ahx.date_effective and nvl(ahx.date_ineffective, sysdate)
and ahx.asset_type = 'CIP'
and thx.transaction_header_id < thadd2.transaction_header_id
and thx.date_effective between dhx.date_effective and nvl(dhx.date_ineffective, sysdate)
and dhx.asset_id = dh.asset_id
and thadd2.date_effective between dhx.date_effective and nvl(dhx.date_ineffective, sysdate)
and thadd2.book_type_code = thx.book_type_code
and thadd2.transaction_type_code = 'ADDITION'
and thx.asset_id = thadd.asset_id
and thx.book_type_code = thadd.book_type_code
and thx.transaction_type_code in ('UNIT ADJUSTMENT',
'TRANSFER IN', 'TRANSFER IN/VOID', 'TRANSFER','RECLASS')
)
and exists (Select 1
From FA_TRANSACTION_HEADERS THDIS,
FA_TRANSACTION_HEADERS THDIS2
Where DH.TRANSACTION_HEADER_ID_IN = THDIS.TRANSACTION_HEADER_ID
AND (
(DH.TRANSACTION_HEADER_ID_OUT IS NULL
AND THDIS2.TRANSACTION_HEADER_ID = DH.TRANSACTION_HEADER_ID_IN)
OR (
(DH.TRANSACTION_HEADER_ID_OUT IS NOT NULL
AND THDIS.TRANSACTION_TYPE_CODE
in ('TRANSFER IN', 'TRANSFER', 'UNIT ADJUSTMENT', 'RECLASS')
AND DH.TRANSACTION_HEADER_ID_OUT = THDIS2.TRANSACTION_HEADER_ID
AND THDIS2.TRANSACTION_TYPE_CODE not in ('TRANSFER OUT'))
AND (DH.TRANSACTION_HEADER_ID_OUT IS NOT NULL
and dh.distribution_id in (select dhx.distribution_id
from fa_distribution_history dhx,
fa_transaction_headers theff
where theff.transaction_type_code = 'ADDITION'
and theff.book_type_code = :P_BOOK
and theff.asset_id = dh.asset_id
and theff.date_effective between dhx.date_effective and nvl(dhx.date_ineffective,sysdate)
and dhx.book_type_code = dh.book_type_code
and dhx.asset_id = dh.asset_id )
AND THDIS.TRANSACTION_TYPE_CODE
in ('TRANSFER IN', 'TRANSFER', 'UNIT ADJUSTMENT','RECLASS')
AND DH.TRANSACTION_HEADER_ID_OUT = THDIS2.TRANSACTION_HEADER_ID
AND THDIS2.TRANSACTION_TYPE_CODE not in ('TRANSFER OUT')) )
OR (DH.TRANSACTION_HEADER_ID_OUT IS NOT NULL
AND THDIS.TRANSACTION_TYPE_CODE = 'TRANSFER IN/VOID'
AND DH.TRANSACTION_HEADER_ID_OUT = THDIS2.TRANSACTION_HEADER_ID
AND THDIS2.TRANSACTION_TYPE_CODE not in ('UNIT ADJUSTMENT',
'TRANSFER', 'TRANSFER OUT', 'TRANSFER IN', 'RECLASS','TRANSFER IN/VOID'))
)
)
AND DH.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK
AND DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
GROUP BY
&ACCT_FLEX_BAL_SEG,fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
CB.ASSET_COST_ACCT,
AD.ASSET_NUMBER,
AD.DESCRIPTION,
BOOKS.DATE_PLACED_IN_SERVICE
ORDER BY 1,2,3 |
