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 Asset Additions 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 Mass Additions Invoice Split - draft
Blitz Report™

Blitz Report™ provides multiple benefits: