FA Insurance Data - draft

Description
Categories: BI Publisher
Application: Assets
Source: Insurance Data Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASINSDR_XML
DB package: FA_FASINSDR_XMLP_PKG
SELECT	gcc.segment1,
	FAD.asset_number,
	FAD.description,
	PVO.vendor_name,
	FIP.policy_number,
	FMP.calculation_method,
	FIP.base_insurance_value * (fah.units_assigned/fad.current_units) base_insurance_value,
	INX.price_index_name, 
	FIL.policy_line,
	FIL.insurance_category,
	FIL.hazard_meaning   hazard_class ,
	--added
FA_FASINSDR_XMLP_PKG.C_1Formula C_NO_DATA_SETUP
FROM	FA_ADDITIONS_V		FAD,
	FA_DISTRIBUTION_HISTORY   FAH,
	PO_VENDORS		PVO,
	GL_CODE_COMBINATIONS 	GCC,
	FA_CATEGORIES		CAT,
	FA_PRICE_INDEXES	 	INX,
	FA_LOCATIONS	 	LOC,
                   FA_INS_MST_POLS FMP,
	FA_INS_POLICIES 	FIP,
	FA_INS_LINES_V	FIL ,
	FA_BOOKS		BKS
WHERE    fad.asset_id = fip.asset_id
AND 	fah.asset_id = fip.asset_id
AND        BKS.asset_id = fip.asset_id
AND       bks.book_type_code = :P_ASSET_BOOK
and        BKS.date_ineffective is null
and      bks.period_counter_fully_retired is null
/* &c_acct_flex_bal_where
 &c_where_cat_flex
  &c_where_locn_flex */
 and &c_acct_flex_bal_where
and  &c_where_cat_flex
 and   &c_where_locn_flex
AND 	fad.asset_number between nvl(:P_ASSET_NUMBER_FROM,fad.asset_number)
	and  nvl(:P_ASSET_NUMBER_TO,fad.asset_number)
AND         FMP.asset_policy_id = FIP.asset_policy_id
AND          FIL.asset_policy_id = FIP.asset_policy_id
AND          fil.asset_id = FIP.asset_id
AND 	pvo.vendor_id = fmp.vendor_id
AND          inx.price_index_id (+) = fip.current_price_index_id
and            fad.asset_category_id = cat.category_id
and            fah.location_id = loc.location_id
and            fah.code_combination_id = gcc.code_combination_id  
and            fah.book_type_code = fip.book_type_code
and    	fip.book_type_code  = :P_ASSET_BOOK
and            fah.date_ineffective is null
ORDER BY gcc.segment1,FAD.asset_number,PVO.vendor_name,
	FIP.policy_number,
	FMP.calculation_method,base_insurance_value,INX.price_index_name, 
	FIL.policy_line
Parameter Name SQL text Validation
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
 
To Company
 
From Company
 
Asset Book
 
LOV Oracle