FA Annual Additions - draft

➜ Back to Library
Description
Categories: BI Publisher
Application: Assets
Source: Annual Additions Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASANADD_XML
DB package: FA_FASANADD_XMLP_PKG
Run FA Annual Additions - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Book 
 
 LOV Oracle
From Date 
 
 Date
To Date 
 
 Date
Download
Related Blitz Reports
FA Asset Retirements FA Form 4797 - Ordinary Gains and Losses - draft FA Form 4797 - Sales or Exchanges of Property - draft FA Form 4797 - Gain From Disposition of 1250 Property - draft FA Retired Assets Without Property Classes - draft FA Asset Book Details FA Property Tax Report- Not Supported: Reserved For Future Use FA Asset Reclassification FA Mass Additions Invoice Split - draft
Blitz Report™

Blitz Report™ provides multiple benefits: