SELECT &ACCT_FLEX_BAL_SEG COMPANY,
FALU.MEANING ASSET_TYPE,
DECODE(FAAH.ASSET_TYPE, 'CIP', FACB.CIP_COST_ACCT,
FACB.ASSET_COST_ACCT) ACCOUNT,
&ACCT_FLEX_COST_SEG COST_CENTER,
FAAD.ASSET_NUMBER || ' - ' || FAAD.DESCRIPTION ASSET,
FATH.TRANSACTION_HEADER_ID TR_NUMBER,
DECODE(FADJ.DEBIT_CREDIT_FLAG, 'CR', 0, 'DR', 1) IN_OUT,
FATH.TRANSACTION_DATE_ENTERED TR_DATE_ENT,
FADJ.ADJUSTMENT_AMOUNT * DECODE(FADJ.DEBIT_CREDIT_FLAG,
'DR', 1, 'CR', -1, 0) COST,
FATH.DATE_EFFECTIVE FATH_DATE,
fnd_flex_xml_publisher_apis.process_kff_combination_1('company_dsp', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMPANY_DSP,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_dsp', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') COST_CENTER_DSP
FROM FA_TRANSACTION_HEADERS FATH,
FA_ADJUSTMENTS FADJ,
FA_DISTRIBUTION_HISTORY FADH,
FA_CATEGORY_BOOKS FACB,
GL_CODE_COMBINATIONS DHCC,
FA_ASSET_HISTORY FAAH,
FA_ADDITIONS FAAD,
FA_LOOKUPS FALU
WHERE FATH.TRANSACTION_TYPE_CODE in ('TRANSFER','UNIT ADJUSTMENT') AND
FATH.BOOK_TYPE_CODE = :P_BOOK AND
FATH.DATE_EFFECTIVE BETWEEN
:PERIOD1_POD AND nvl(:PERIOD2_PCD, SYSDATE)
AND
FADJ.TRANSACTION_HEADER_ID = FATH.TRANSACTION_HEADER_ID AND
FADJ.ASSET_ID = FATH.ASSET_ID AND
FADJ.BOOK_TYPE_CODE = FATH.BOOK_TYPE_CODE AND
FADJ.SOURCE_TYPE_CODE = 'TRANSFER' AND
FADJ.ADJUSTMENT_TYPE in ('COST', 'CIP COST')
AND
(fadh.distribution_id in
(select dh1.distribution_id
from fa_distribution_history dh1
WHERE (DH1.TRANSACTION_HEADER_ID_OUT = FATH.TRANSACTION_HEADER_ID
OR DH1.TRANSACTION_HEADER_ID_IN = FATH.TRANSACTION_HEADER_ID)
AND
1 <
(SELECT count( distinct dh2.code_combination_id)
FROM FA_DISTRIBUTION_HISTORY DH2
WHERE (DH2.TRANSACTION_HEADER_ID_OUT = FATH.TRANSACTION_HEADER_ID
OR DH2.TRANSACTION_HEADER_ID_IN = FATH.TRANSACTION_HEADER_ID)))
OR FATH.TRANSACTION_TYPE_CODE = 'UNIT ADJUSTMENT')
AND
FADH.DISTRIBUTION_ID = FADJ.DISTRIBUTION_ID
AND
DHCC.CODE_COMBINATION_ID = FADH.CODE_COMBINATION_ID
AND
FAAH.ASSET_ID = FATH.ASSET_ID AND
FATH.DATE_EFFECTIVE >= FAAH.DATE_EFFECTIVE AND
FATH.DATE_EFFECTIVE < NVL(FAAH.DATE_INEFFECTIVE,SYSDATE)
AND
FACB.CATEGORY_ID = FAAH.CATEGORY_ID AND
FACB.BOOK_TYPE_CODE = FATH.BOOK_TYPE_CODE
AND
FAAD.ASSET_ID = FAAH.ASSET_ID
AND
FALU.LOOKUP_CODE = FAAH.ASSET_TYPE AND
FALU.LOOKUP_TYPE = 'ASSET TYPE'
order by ASSET |