FA Asset Retirements By Cost Center - draft

Description
Categories: BI Publisher
Application: Assets
Source: Asset Retirements By Cost Center Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS441_XML
DB package: FA_FAS441_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_CODE1, 
	--&ACCT_FLEX_COST_SEG                        cost_ctr,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1,
    nvl(emp.full_name, ' ')                         name,
    --&LOC_FLEX_ALL_SEG                          Location,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
        decode(ah.asset_type,'CIP',cb.cip_cost_acct,
                cb.asset_cost_acct)               account,
        ad.asset_number || ' - ' || ad.description asset_num,
        ret.date_retired   date_retired,
ROUND(decode(ret.units, NULL, 
        (decode(th.transaction_type_code, 'REINSTATEMENT',
          -ret.cost_retired,  ret.cost_retired)
 * (dh.units_assigned /ah.units)),
        (decode(th.transaction_type_code, 'REINSTATEMENT',
          -ret.cost_retired,  ret.cost_retired)
           * -dh.transaction_units / ret.units)) ,:PRECISION) cost,
ROUND(decode(ret.units, NULL, 
        (decode(th.transaction_type_code, 'REINSTATEMENT',
          -ret.nbv_retired,  ret.nbv_retired)
 * (dh.units_assigned /ah.units)),
        (decode(th.transaction_type_code, 'REINSTATEMENT',
          -ret.nbv_retired,  ret.nbv_retired)
           * -dh.transaction_units / ret.units)),:PRECISION) nbv,
ROUND(decode(ret.units, NULL, 
        (decode(th.transaction_type_code, 'REINSTATEMENT',
          -ret.proceeds_of_sale,  ret.proceeds_of_sale)
 * (dh.units_assigned /ah.units)),
        (decode(th.transaction_type_code, 'REINSTATEMENT',
          -ret.proceeds_of_sale,  ret.proceeds_of_sale)
           * -dh.transaction_units / ret.units)),:PRECISION) proceeds,
ROUND(decode(ret.units, NULL, 
        (decode(th.transaction_type_code, 'REINSTATEMENT',
          -ret.gain_loss_amount,  ret.gain_loss_amount)
 * (dh.units_assigned /ah.units)),
        (decode(th.transaction_type_code, 'REINSTATEMENT',
          -ret.gain_loss_amount,  ret.gain_loss_amount)
           * -dh.transaction_units / ret.units)),:PRECISION) gain_loss,
decode(th.transaction_type_code, 'REINSTATEMENT', '*', NULL) 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, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.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*/
FROM    
        fa_additions                ad,
        gl_code_combinations        dhcc,
        fa_asset_history            ah,    
        fa_category_books           cb,
        per_all_people_f               emp,
        fa_locations                loc,
       fa_distribution_history     dh,
        fa_retirements              ret,
        fa_transaction_headers      th
WHERE     
/*        &ACCT_FLEX_COST_SEG
        between nvl(:P_START_CC,&ACCT_FLEX_COST_SEG) AND 
		nvl(:P_END_CC,&ACCT_FLEX_COST_SEG)*/
        fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.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#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.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#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'))
AND
        th.date_effective     >= :Period1_POD  AND
        th.date_effective     <= :Period1_PCD  AND
        th.book_type_code = :p_Book  AND
        th.transaction_key = 'R'
AND
        th.transaction_header_id = decode(th.transaction_type_code,
                'REINSTATEMENT', ret.transaction_header_id_out,
                ret.transaction_header_id_in)
AND
        ad.asset_id = th.asset_id
AND
        cb.category_id = ah.category_id            AND
        cb.book_type_code = :P_Book  AND
        ah.asset_id = ad.asset_id            AND
        ah.date_effective <= th.date_effective        AND
        nvl(ah.date_ineffective, th.date_effective+1) > th.date_effective
AND
        dh.asset_id = th.asset_id                AND
        dh.book_type_code = :distribution_source_book  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
        ret.book_type_code = :P_Book
AND
        dh.asset_id = ret.asset_id 
AND
        dhcc.code_combination_id = dh.code_combination_id        
AND    
        dh.location_id = loc.location_id
AND
        dh.assigned_to = emp.person_id(+)
AND  trunc(sysdate) between emp.effective_start_date(+) and emp.effective_end_date(+)
ORDER BY 
/*        &ACCT_FLEX_BAL_SEG,
        &ACCT_FLEX_COST_SEG,
        emp.full_name,
        &LOC_FLEX_ALL_SEG,
        decode(ah.asset_type,'CIP',cb.cip_cost_acct,
                cb.asset_cost_acct),
        ad.asset_number*/
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
Parameter Name SQL text Validation
Acct Flex Structure
 
Number
To Cost Center
 
From Cost Center
 
Period
 
LOV Oracle
Book
 
LOV Oracle