FA Tax Retirements - draft

Description
Categories: BI Publisher
Application: Assets
Source: Tax Retirements Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS520_XML
DB package: FA_FAS520_XMLP_PKG
SELECT 	&ACCT_FLEX_BAL_SEG			comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE,
	th.book_type_code				book,
	th.transaction_type_code			tt,
	th.asset_id					asset,
	fy.fiscal_year					fiscal_year,
	cb.asset_cost_acct				account,
	ad.asset_number || ' - ' || ad.description	no,
	books.date_placed_in_service dt,
	ret.date_retired		dr,
	round(sum(decode(ret.units, NULL,
	  ret.cost_retired * (dh.units_assigned /ah.units),
	  ret.cost_retired * abs(dh.transaction_units) / ret.units)) , :PRECISION)
							cost,
	Round(sum(decode(ret.units, NULL,
	  ret.nbv_retired * (dh.units_assigned /ah.units),
	  ret.nbv_retired * abs(dh.transaction_units) / ret.units))	, :PRECISION)
							nbv,
	Round(sum(decode(ret.units, NULL,
	  ret.proceeds_of_sale * (dh.units_assigned /ah.units),
	  nvl(ret.proceeds_of_sale, 0.00) * 
	  abs(dh.transaction_units) / ret.units)), :PRECISION)
							proceeds,
	Round(sum(decode(ret.units, NULL,
	  nvl(ret.itc_recaptured, 0.00)  * (dh.units_assigned /ah.units),
	  nvl(ret.itc_recaptured, 0.00) * 
	  abs(dh.transaction_units) / ret.units)), :PRECISION)
							itc,
	Round(sum(decode(ret.units, NULL,
	  nvl(ret.gain_loss_amount,0.00)* (dh.units_assigned /ah.units),
	  nvl(ret.gain_loss_amount, 0.00) * 
	  abs(dh.transaction_units) / ret.units)), :PRECISION)
							gl,
	decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT',
		'*', NULL)
							code
FROM	
	fa_asset_history			ah,
	fa_retirements				ret,
	fa_additions				ad,
	fa_books				books,
	fa_category_books			cb,
	fa_fiscal_year				fy,
	fa_transaction_headers			th,
	fa_distribution_history			dh,
	gl_code_combinations			dhcc
WHERE 	
        th.date_effective >= :PERIOD1_POD AND 
		th.date_effective <= :PERIOD2_PCD AND
		th.book_type_code = UPPER(:P_BOOK) AND
		th.transaction_type_code = 'FULL RETIREMENT'
AND
	th.transaction_header_id = ret.transaction_header_id_in
AND
	ad.asset_id = th.asset_id
AND
	books.transaction_header_id_out = th.transaction_header_id	AND
	books.book_type_code = UPPER(:P_BOOK)AND books.asset_id = th.asset_id
AND	
	cb.category_id = ah.category_id					AND
	cb.book_type_code = UPPER(:P_BOOK) AND
	ah.asset_id = ad.asset_id				AND
	ah.date_effective <= th.date_effective			AND
	(ah.date_ineffective is null				OR
	ah.date_ineffective > th.date_effective)		
AND
	books.date_placed_in_service between fy.start_date and fy.end_date
AND
        fy.fiscal_year_name = :Fiscal_Year_Name AND
	dh.book_type_code = :Distribution_Source_Book AND
	dh.asset_id = th.asset_id				AND
(	dh.retirement_id = ret.retirement_id
or (ret.date_effective >= dh.date_effective       and
    ret.date_effective <= nvl(dh.date_ineffective,sysdate)
    and ret.units is null))
AND
dhcc.code_combination_id = dh.code_combination_id
GROUP BY
	&ACCT_FLEX_BAL_SEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	th.book_type_code,
	th.transaction_type_code,
	th.asset_id,
	fy.fiscal_year,
	cb.asset_cost_acct,
	ad.asset_number,
	ad.description,	
	books.date_placed_in_service,
	ret.date_retired,
	decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT',
		'*', NULL)
union
SELECT 	&ACCT_FLEX_BAL_SEG			comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE,
	th.book_type_code				book,
	th.transaction_type_code			tt,
	th.asset_id					asset,
	fy.fiscal_year					fiscal_year,
	cb.asset_cost_acct				account,
	ad.asset_number || ' - ' || ad.description	no,
	books.date_placed_in_service dt,
	ret.date_retired		dr,
	ROUND(sum(decode(ret.units, NULL,
	  (-1) * ret.cost_retired * (dh.units_assigned /ah.units),
	  (-1) * ret.cost_retired * abs(dh.transaction_units) / ret.units)),  :PRECISION)
							cost,
	ROUND(sum(decode(ret.units, NULL,
	  (-1) * ret.nbv_retired * (dh.units_assigned /ah.units),
	  (-1) * ret.nbv_retired * abs(dh.transaction_units) / ret.units)), :PRECISION)
							nbv,
	ROUND(sum(decode(ret.units, NULL,
	  (-1) * ret.proceeds_of_sale * (dh.units_assigned /ah.units),
	  (-1) * nvl(ret.proceeds_of_sale, 0.00) * 
	  abs(dh.transaction_units) / ret.units)), :PRECISION)
							proceeds,
	ROUND(sum(decode(ret.units, NULL,
	  (-1) * nvl(ret.itc_recaptured, 0.00)  * (dh.units_assigned /ah.units),
	  (-1) * nvl(ret.itc_recaptured, 0.00) * 
	  abs(dh.transaction_units) / ret.units)), :PRECISION)
							itc,
	ROUND(sum(decode(ret.units, NULL,
	  (-1) * nvl(ret.gain_loss_amount,0.00) 
	* (dh.units_assigned /ah.units),
	  (-1) * nvl(ret.gain_loss_amount, 0.00) *
	  abs(dh.transaction_units) / ret.units)), :PRECISION)
							gl,
	decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT',
		'*', NULL)
							code
FROM	
	fa_asset_history			ah,
	fa_retirements				ret,
	fa_additions				ad,
	fa_books				books,
	fa_category_books			cb,
	fa_fiscal_year				fy,
	fa_transaction_headers			th,
	fa_distribution_history			dh,
	gl_code_combinations			dhcc
WHERE 	
	th.date_effective >= :PERIOD1_POD AND
	th.date_effective <= :PERIOD2_PCD AND
	th.book_type_code 	= UPPER(:P_BOOK) AND
	th.transaction_type_code = 'REINSTATEMENT'
AND
	th.transaction_header_id = ret.transaction_header_id_out
AND
	ad.asset_id = th.asset_id
AND
	books.transaction_header_id_out = th.transaction_header_id	AND
	books.book_type_code = UPPER(:P_BOOK)
	AND books.asset_id = th.asset_id
AND	
	cb.category_id = ah.category_id					AND
	cb.book_type_code = UPPER(:P_BOOK)AND
	ah.asset_id = ad.asset_id				AND
	ah.date_effective <= th.date_effective			AND
	(ah.date_ineffective is null				OR
	ah.date_ineffective > th.date_effective)		
AND
	books.date_placed_in_service				between
	fy.start_date						and
	fy.end_date
AND
        fy.fiscal_year_name =:Fiscal_Year_Name AND
		dh.book_type_code = :Distribution_Source_Book AND
		dh.asset_id = th.asset_id				AND
(	dh.retirement_id = ret.retirement_id
or (ret.date_effective >= dh.date_effective       and
    ret.date_effective <= nvl(dh.date_ineffective,sysdate)
    and ret.units is null))
AND
dhcc.code_combination_id = dh.code_combination_id
GROUP BY
	&ACCT_FLEX_BAL_SEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	th.book_type_code,
	th.transaction_type_code,
	th.asset_id,
	fy.fiscal_year,
	cb.asset_cost_acct,
	ad.asset_number,
	ad.description,	
	books.date_placed_in_service,
	ret.date_retired,
	decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT',
		'*', NULL)
union
SELECT 	&ACCT_FLEX_BAL_SEG			comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE,
	th.book_type_code				book,
	th.transaction_type_code			tt,
	th.asset_id					asset,
	fy.fiscal_year					fiscal_year,
	cb.asset_cost_acct				account,
	ad.asset_number || ' - ' || ad.description	no,
	books.date_placed_in_service dt,
	ret.date_retired		dr,
	ROUND(sum(decode(ret.units, NULL,
	  ret.cost_retired * (dh.units_assigned /ah.units),
	  ret.cost_retired * abs(dh.transaction_units) / ret.units)), :PRECISION)
							cost,
	ROUND(sum(decode(ret.units, NULL,
	  ret.nbv_retired * (dh.units_assigned /ah.units),
	  ret.nbv_retired * abs(dh.transaction_units) / ret.units))	, :PRECISION)
							nbv,
	ROUND(sum(decode(ret.units, NULL,
	  ret.proceeds_of_sale * (dh.units_assigned /ah.units),
	  nvl(ret.proceeds_of_sale, 0.00) * 
	  abs(dh.transaction_units) / ret.units)), :PRECISION)
							proceeds,
	ROUND(sum(decode(ret.units, NULL,
	  nvl(ret.itc_recaptured, 0.00)  * (dh.units_assigned /ah.units),
	  nvl(ret.itc_recaptured, 0.00) * 
	  abs(dh.transaction_units) / ret.units)), :PRECISION)
							itc,
	ROUND(sum(decode(ret.units, NULL,
	  nvl(ret.gain_loss_amount,0.00)* (dh.units_assigned /ah.units),
	  nvl(ret.gain_loss_amount, 0.00) * 
	  abs(dh.transaction_units) / ret.units)), :PRECISION)
							gl,
	decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT',
		'*', NULL)
							code
FROM	
	fa_asset_history			ah,
	fa_retirements				ret,
	fa_additions				ad,
	fa_books				books,
	fa_category_books			cb,
	fa_fiscal_year				fy,
	fa_transaction_headers			th,
	fa_distribution_history			dh,
	gl_code_combinations			dhcc
WHERE 	
	th.date_effective >= :PERIOD1_POD AND
	th.date_effective <= :PERIOD2_PCD AND 
	th.book_type_code=  UPPER(:P_BOOK) AND
	th.transaction_type_code = 'PARTIAL RETIREMENT'
AND
	th.transaction_header_id = ret.transaction_header_id_in
AND
	ad.asset_id = th.asset_id
AND
	books.transaction_header_id_out = th.transaction_header_id	AND
	books.book_type_code =UPPER(:P_BOOK)AND
	books.asset_id = th.asset_id
AND	
	cb.category_id = ah.category_id					AND
	cb.book_type_code = UPPER(:P_BOOK)
	AND
	ah.asset_id = ad.asset_id				AND
	ah.date_effective <= th.date_effective			AND
	(ah.date_ineffective is null				OR
	ah.date_ineffective > th.date_effective)		
AND
	books.date_placed_in_service				between
	fy.start_date						and
	fy.end_date
AND
        fy.fiscal_year_name =:Fiscal_Year_Name 
		AND	dh.book_type_code = :Distribution_Source_Book and
		dh.asset_id = th.asset_id				AND
(	dh.retirement_id = ret.retirement_id
or (ret.date_effective >= dh.date_effective       and
    ret.date_effective <= nvl(dh.date_ineffective,sysdate)
    and ret.units is null))
AND
dhcc.code_combination_id = dh.code_combination_id
GROUP BY
	&ACCT_FLEX_BAL_SEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') ,
	th.book_type_code,
	th.transaction_type_code,
	th.asset_id,
	fy.fiscal_year,
	cb.asset_cost_acct,
	ad.asset_number,
	ad.description,	
	books.date_placed_in_service,
	ret.date_retired,
	decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT','*', NULL)
ORDER BY 1,2,6,7,8,9,10,11,12,13,14,15,16,3,4,5
--1,5,6,7
Parameter Name SQL text Validation
To Period
 
LOV Oracle
From Period
 
LOV Oracle
Book
 
LOV Oracle
Ask a question