FA Asset Inventory - draft

Description
Categories: BI Publisher
Application: Assets
Source: Asset Inventory Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS410_XML
DB package: FA_FAS410_XMLP_PKG
select --&ACCT_FLEX_BAL_SEG			COMP_CODE,
--       &ACCT_FLEX_COST_SEG				COST_CTR,
       EMP.FULL_NAME				OWNER,
--       &LOC_FLEX_ALL_SEG				LOCATION,
       AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION		ASSET,
                     sum(DH.UNITS_ASSIGNED)				UNITS,
       AD.SERIAL_NUMBER				SERIAL,
       AD.TAG_NUMBER				TAG,
              sum(decode(dd.deprn_source_code, 
	'B', dd.addition_cost_to_clear, dd.cost))		COST,
              sum(dd.deprn_reserve)				RESERVE,
      decode(greatest(BOOKS.DATE_PLACED_IN_SERVICE, 
		NVL(:P_FROM_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),
	least(BOOKS.DATE_PLACED_IN_SERVICE, 
		NVL(:P_TO_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),
	1, 0)                new,
        decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E','')		ASSET_TYPE, 
/*	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION , */
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
	FA_FAS410_XMLP_PKG.as_nbvformula(:AS_COST, :AS_RESERVE) AS_NBV
/*This part is not required, so Commented during DT Fix
FROM_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),
	least(BOOKS.DATE_PLACED_IN_SERVICE, 
		NVL(:P_TO_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),
	1, 0)                new,
        decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E','')		ASSET_TYPE*/
FROM    
    fa_deprn_detail dd,
    per_people_f          emp,
    fa_additions            ad,    
    fa_locations            loc,
    gl_code_combinations    cc,
    fa_books                books,
    fa_distribution_history dh
WHERE    
    dh.book_type_code      = :P_BOOK    AND
    dh.assigned_to         = emp.person_id(+)      AND
    dh.date_effective between nvl(emp.effective_start_date,dh.date_effective)
                                        and nvl(emp.effective_end_date,dh.date_effective) AND
/*Commented, to be removed later
    &ACCT_FLEX_COST_SEG between 
	NVL(:P_START_CC,  &ACCT_FLEX_COST_SEG)  AND
	NVL(:P_END_CC ,  &ACCT_FLEX_COST_SEG) AND
*/
--Added during DT Fix
    fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') between 
	NVL(:P_START_CC,  fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'))  AND
	NVL(:P_END_CC ,  fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')) AND
--End of DT Fix
    dh.asset_id            = ad.asset_id            AND    
    dh.location_id         = loc.location_id        AND
    dh.code_combination_id = cc.code_combination_id AND    
    dh.date_ineffective is null
AND  
    dd.asset_id = dh.asset_id             AND
    dd.book_type_code = dh.book_type_code      AND
    dd.distribution_id = dh.distribution_id      AND
    dd.period_counter = 
   (SELECT    max (dd2.period_counter)
    FROM    fa_deprn_detail dd2
    WHERE	dd2.book_type_code = books.book_type_code and
        	dd2.asset_id = books.asset_id and
	dd2.distribution_id = dd.distribution_id)
AND
   books.book_type_code  = :P_BOOK        AND    
   books.asset_id = dh.asset_id and 
   books.date_placed_in_service between
     nvl(:P_FROM_DATE, books.date_placed_in_service) and
     nvl(:P_TO_DATE, books.date_placed_in_service) AND
   books.period_counter_fully_retired is null   AND    
    books.date_ineffective is null
/* Commented during DT Fix
group by &acct_flex_bal_seg, &acct_flex_cost_seg,emp.full_name,&loc_flex_all_seg,AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,dh.units_assigned,ad.serial_number,ad.tag_number,decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E',''),decode(greatest(BOOKS.DATE_PLACED_IN_SERVICE, 
End of DT Fix*/
group by fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'), fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'), emp.full_name, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE'), AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,dh.units_assigned,ad.serial_number,ad.tag_number,decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E',''),decode(greatest(BOOKS.DATE_PLACED_IN_SERVICE, 
		NVL(:P_FROM_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),
	least(BOOKS.DATE_PLACED_IN_SERVICE, 
		NVL(:P_TO_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),
	1, 0),decode(dd.deprn_source_code, 
	'B', dd.addition_cost_to_clear, dd.cost),dd.deprn_reserve
UNION ALL
select --&ACCT_FLEX_BAL_SEG				COMP_CODE,
       --&ACCT_FLEX_COST_SEG				COST_CTR,
       EMP.FULL_NAME				OWNER,
       --&LOC_FLEX_ALL_SEG				LOCATION,
       AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION		ASSET,
              sum(decode(LU.LOOKUP_CODE,
	'ADDITION COST',
decode(adj.source_type_code,'TRANSFER',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED,
                            'RETIREMENT',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED,
                            'CIP RETIREMENT',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED,
                            'RECLASS',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED,0),	0))					UNITS,
       AD.SERIAL_NUMBER				SERIAL,
       AD.TAG_NUMBER				TAG,
	sum(DECODE(LU.LOOKUP_CODE,
	'ADDITION COST', 
	DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', 1, -1) *
	ADJ.ADJUSTMENT_AMOUNT,0))		COST,
	sum(DECODE(LU.LOOKUP_CODE,
		'DEPRECIATION RESERVE', 
	DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', -1, 1) *
	ADJ.ADJUSTMENT_AMOUNT, 0)	)	RESERVE,
      decode(greatest(BOOKS.DATE_PLACED_IN_SERVICE, 
		NVL(:P_FROM_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),
	least(BOOKS.DATE_PLACED_IN_SERVICE, 
		NVL(:P_TO_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),
	1, 0)                new,
        decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E','')		ASSET_TYPE, 
/*	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION , */
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1, 
	FA_FAS410_XMLP_PKG.as_nbvformula(:AS_COST, :AS_RESERVE) AS_NBV
/*This part is not required, so Commented during DT Fix
FROM_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),
	least(BOOKS.DATE_PLACED_IN_SERVICE, 
		NVL(:P_TO_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),
	1, 0)                new,
        decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E','')		ASSET_TYPE*/
FROM    
    per_people_f            emp,
    fa_additions            ad,    
    fa_locations            loc,
    gl_code_combinations    cc,
    fa_books                books,
    fa_adjustments	adj,
    fa_distribution_history dh,
    fa_lookups 	lu
WHERE    
    dh.book_type_code      = :P_BOOK    AND
    dh.assigned_to         = emp.person_id(+)      AND
    dh.date_effective between nvl(emp.effective_start_date,dh.date_effective)
                                       and   nvl(emp.effective_end_date,dh.date_effective)  AND
/*Commented, to be removed Later
	&ACCT_FLEX_COST_SEG between 
	NVL(:P_START_CC,  &ACCT_FLEX_COST_SEG)  AND
	NVL(:P_END_CC ,  &ACCT_FLEX_COST_SEG) AND
*/
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') between 
	NVL(:P_START_CC,  fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'))  AND
	NVL(:P_END_CC ,  fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')) AND
    dh.asset_id            = ad.asset_id            AND    
    dh.location_id         = loc.location_id        AND
    dh.code_combination_id = cc.code_combination_id AND    
    dh.date_ineffective is null
AND
	lu.lookup_type = 'JOURNAL ENTRIES' and
	((adj.adjustment_type in ('COST','CIP COST') and
	  lu.lookup_code = 'ADDITION COST') or
	 (adj.adjustment_type = 'RESERVE' and
	  lu.lookup_code = 'DEPRECIATION RESERVE'))
AND
	adj.source_type_code not in ('DEPRECIATION',
		'ADDITION', 'CIP ADDITION') and
 	adj.book_type_code = :P_BOOK and
	adj.asset_id = dh.asset_id and
	adj.distribution_id = dh.distribution_id and
	adj.period_counter_created = :CUR_PERIOD_PC
AND
   books.book_type_code  = :P_BOOK        AND    
   books.asset_id = dh.asset_id and
   books.date_placed_in_service between
       nvl(:P_FROM_DATE, books.date_placed_in_service) and
       nvl(:P_TO_DATE, books.date_placed_in_service) AND
    books.period_counter_fully_retired is null   AND    
    books.date_ineffective is null
--	group by &acct_flex_bal_seg, &acct_flex_cost_seg,emp.full_name,&loc_flex_all_seg,AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,dh.units_assigned,ad.serial_number,ad.tag_number,decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E',''),decode(greatest(BOOKS.DATE_PLACED_IN_SERVICE, 
	group by fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'), fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'), emp.full_name, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE'),
	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,
	--dh.units_assigned,
	decode(LU.LOOKUP_CODE,'ADDITION COST',decode(adj.source_type_code,'TRANSFER',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED,                          'RETIREMENT',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED,                           'CIP RETIREMENT',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED,                      'RECLASS',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED,0),0),
	ad.serial_number,
	ad.tag_number,
	decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E',''),
	decode(greatest(BOOKS.DATE_PLACED_IN_SERVICE, 		NVL(:P_FROM_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),	least(BOOKS.DATE_PLACED_IN_SERVICE, 		NVL(:P_TO_DATE, BOOKS.DATE_PLACED_IN_SERVICE)),	1, 0),
	DECODE(LU.LOOKUP_CODE,
	'ADDITION COST', 
	DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', 1, -1) *
	ADJ.ADJUSTMENT_AMOUNT,0),DECODE(LU.LOOKUP_CODE,
		'DEPRECIATION RESERVE', 
	DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', -1, 1) *
	ADJ.ADJUSTMENT_AMOUNT, 0)
/*Commented during DT Fix
ORDER BY  1,2,3,4,5*/
ORDER BY  10, 11, 1, 12, 2
Parameter Name SQL text Validation
Acct Flex Structure
 
Number
To Date Placed in Service
 
Date
From Date Placed in Service
 
Date
To Cost Center
 
From Cost Center
 
Book
 
LOV Oracle
Ask a question