FA Asset Transfer Reconciliation - draft

Description
Categories: BI Publisher
Application: Assets
Source: Asset Transfer Reconciliation Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS431_XML
DB package: FA_FAS431_XMLP_PKG
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
Parameter Name SQL text Validation
To Period
 
LOV Oracle
From Period
 
LOV Oracle
Book
 
LOV Oracle
Ask a question