FA Insurance Values Detail - draft

Description
Categories: BI Publisher
Application: Assets
Source: Insurance Values Detail Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASINSVR_XML
DB package: FA_FASINSVR_XMLP_PKG
SELECT	bks.book_type_code, 
	null	                   acct_flex,
	&c_cat_flex_seg		cat_flex,
	&c_loc_flex_seg		locn_flex,
	FMP.calculation_method,
	PVO.vendor_name insurance_company,
	FAD.asset_number,
	FAD.description,
	FIP.policy_number,
	BKS.life_in_months	asset_life,
	to_char(BKS.date_placed_in_service,'DD-MON-YYYY') 
				date_placed_in_service,
	bks.cost,
	round(nvl(FIP.base_insurance_value,0)    * (fdh.units_assigned/fad.current_units),2) base_insurance_value,
	round(nvl(FIP.current_insurance_value,0)   * (fdh.units_assigned/fad.current_units),2) insurance_Value,
	round(FIP.insured_amount  * (fdh.units_assigned/fad.current_units),2) insured_amount,
	--NULL last_indexation_date,
	to_char(FIV.indexation_date,'DD-MON-RRRR'),
	round((nvl(FIP.insured_amount,0)  * (fdh.units_assigned/fad.current_units))
		- (nvl(FIP.current_insurance_value,0)   * (fdh.units_assigned/fad.current_units)),2)
				insurance_coverage, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_bal_desc', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'DESCRIPTION') CF_BAL_DESC, 
	FA_FASINSVR_XMLP_PKG.cf_no_data_foundformula() CF_NO_DATA_FOUND
FROM	FA_BOOKS		BKS,
	FA_DISTRIBUTION_HISTORY    FDH,
	FA_ADDITIONS		FAD,
	PO_VENDORS		PVO,
	GL_CODE_COMBINATIONS 	GCC,
	FA_CATEGORIES		CAT,
	FA_LOCATIONS	 	LOC,
	FA_INS_VALUES 	FIV,
	FA_INS_VALUES              FIV2,
	 FA_INS_MST_POLS FMP,
	FA_INS_POLICIES 	FIP	
WHERE     gcc.code_combination_id  = fdh.code_combination_id
and           fdh.asset_id = fad.asset_id
and            fdh.date_ineffective is null
and    	fad.asset_category_id =  cat.category_id
and            loc.location_id = fdh.location_id
and	fad.asset_id = bks.asset_id
and 	bks.asset_id = fip.asset_id
and            bks.period_counter_fully_retired is null
and     	bks.date_effective <= 	:TO_CLOSE_DATE
and          nvl(bks.date_ineffective ,:TO_CLOSE_DATE + 1) >= :FROM_OPEN_DATE
and	bks.transaction_header_id_in   =
                 (select  max(bks1.transaction_header_id_in )
                  from fa_books bks1
                  where  bks1.period_counter_fully_retired is null
	 and     bks1.asset_id  = fip.asset_id
	 and    bks1.date_effective<= 	:TO_CLOSE_DATE
	 and     nvl(bks1.date_ineffective ,:TO_CLOSE_DATE + 1) >= :FROM_OPEN_DATE
                   and     bks1.book_type_code   = :P_ASSET_BOOK)
and     	bks.book_type_code  = fip.book_type_code
and           bks.book_type_code = :P_ASSET_BOOK
and           fiv.asset_policy_id  = fip.asset_policy_id
and           fiv.asset_id = fip.asset_id
and           fiv2.asset_policy_id = fip.asset_policy_id
and           fiv2.asset_id = fip.asset_id
and          fiv2.indexation_date = (select max(fiv3.indexation_date)
			      from fa_ins_values fiv3
			  where fiv3.asset_policy_id = fiv2.asset_policy_id
			    and fiv3.asset_id = fiv2.asset_id)
and          fiv.indexation_date = (select max(fiv3.indexation_date)
			      from fa_ins_values fiv3
			    where fiv3.asset_policy_id = fiv.asset_policy_id
			    and fiv3.asset_id = fiv.asset_id
			    and fiv3.indexation_year <= :P_YEAR )
and           fip.asset_policy_id = fmp.asset_policy_id
&c_where_old_ins_data
and &c_acct_flex_bal_where
and  &c_where_cat_flex 
 and &c_where_locn_flex
&c_where_asset_number
&c_where_ins_company
&c_where_cal_method
AND 	pvo.vendor_id = fmp.vendor_id
UNION
SELECT	bks.book_type_code, 
	&c_acct_flex_bal_seg	                   acct_flex,
	&c_cat_flex_seg		cat_flex,
	&c_loc_flex_seg		locn_flex,
	FMP.calculation_method,
	PVO.vendor_name insurance_company,
	FAD.asset_number,
	FAD.description,
	FIP.policy_number,
	BKS.life_in_months	asset_life,
	to_char(BKS.date_placed_in_service,'DD-MON-YYYY') 
				date_placed_in_service,
	bks.cost,
	round(nvl(FIP.base_insurance_value,0)    * (fdh.units_assigned/fad.current_units),2) base_insurance_value,
	round(nvl(FIP.current_insurance_value,0)   * (fdh.units_assigned/fad.current_units),2) insurance_Value,
	round(FIP.insured_amount  * (fdh.units_assigned/fad.current_units),2) insured_amount,
	NULL last_indexation_date,
	round((nvl(FIP.insured_amount,0)  * (fdh.units_assigned/fad.current_units))
		- (nvl(FIP.current_insurance_value,0)   * (fdh.units_assigned/fad.current_units)),2)
				insurance_coverage,
				fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_bal_desc', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'DESCRIPTION') CF_BAL_DESC, 
	FA_FASINSVR_XMLP_PKG.cf_no_data_foundformula() CF_NO_DATA_FOUND
FROM	FA_BOOKS		BKS,
	FA_DISTRIBUTION_HISTORY    FDH,
	FA_ADDITIONS		FAD,
	PO_VENDORS		PVO,
	GL_CODE_COMBINATIONS 	GCC,
	FA_CATEGORIES		CAT,
	FA_LOCATIONS	 	LOC,
	 FA_INS_MST_POLS FMP,
	FA_INS_POLICIES 	FIP	
WHERE     gcc.code_combination_id  = fdh.code_combination_id
and	fdh.asset_id = fad.asset_id
and            fdh.date_ineffective is null
and            bks.book_type_code = :P_ASSET_BOOK
and    	fad.asset_category_id =  cat.category_id
and            loc.location_id = fdh.location_id
and	fad.asset_id = bks.asset_id
and 	bks.asset_id = fip.asset_id
and            bks.period_counter_fully_retired is null
and            bks.date_placed_in_service < :CALENDAR_OPEN_DATE
and     	bks.date_effective <= 	:TO_CLOSE_DATE
and          nvl(bks.date_ineffective ,:TO_CLOSE_DATE + 1) >= :FROM_OPEN_DATE
and	bks.transaction_header_id_in   =
                 (
select  max(bks1.transaction_header_id_in )
from fa_books bks1
                  where   bks1.asset_id  = fip.asset_id
	 and            bks1.period_counter_fully_retired is null
                   and            bks1.date_placed_in_service < :CALENDAR_OPEN_DATE
	 and     bks1.date_effective <= 	:TO_CLOSE_DATE
	 and     nvl(bks1.date_ineffective ,:TO_CLOSE_DATE + 1) >= :FROM_OPEN_DATE
                   and     bks1.book_type_code   = :P_ASSET_BOOK)
and     	bks.book_type_code  = fip.book_type_code
and           fip.asset_policy_id = fmp.asset_policy_id
and           fip.last_indexation_date is null
and &c_acct_flex_bal_where
and  &c_where_cat_flex
and &c_where_locn_flex
&c_where_asset_number
&c_where_ins_company
&c_where_cal_method
AND 	pvo.vendor_id = fmp.vendor_id 
 and bks.book_type_code=:Asset_book
Parameter NameSQL textValidation
Account Flex Structure
 
Number
To Asset Number
 
LOV Oracle
From Asset Number
 
LOV Oracle
To Asset Category
 
From Asset Category
 
To Asset Location
 
From Asset Location
 
Year
 
LOV Oracle
To Insurance Company
 
LOV Oracle
From Insurance Company
 
LOV Oracle
To Calculation Method
 
LOV Oracle
From Calculation Method
 
LOV Oracle
To Company
 
From Company
 
Asset Book
 
LOV Oracle