SELECTDISTINCT a.segment1 || '.'|| a.segment2 CATEGORY,
a.segment1 major_category, a.segment2 minor_category,
gl1.segment1
|| '.'
|| gl1.segment2
|| '.'
|| gl1.segment3
|| '.'
|| gl1.segment4
|| '.'
|| gl1.segment5 asset_cost,
gl2.segment1
|| '.'
|| gl2.segment2
|| '.'
|| gl2.segment3
|| '.'
|| gl2.segment4
|| '.'
|| gl2.segment5 asset_cost_clearing,
c.deprn_expense_acct,
gl3.segment1
|| '.'
|| gl3.segment2
|| '.'
|| gl3.segment3
|| '.'
|| gl3.segment4
|| '.'
|| gl3.segment5 deprn_reserve_account,
deprn_method,life_in_months, (life_in_months /12) life,
prorate_convention_code
FROMfa_categories a,
fa_category_book_defaults b,
fa_category_books c,
gl_code_combinations gl1,
gl_code_combinations gl2,
gl_code_combinations gl3
WHEREa.category_id =b.category_id
ANDc.category_id =b.category_id
ANDc.category_id =a.category_id
ANDa.enabled_flag ='Y'
ANDc.asset_cost_account_ccid =gl1.code_combination_id
ANDc.asset_clearing_account_ccid =gl2.code_combination_id
ANDc.reserve_account_ccid =gl3.code_combination_id
--AND c.WIP_COST_ACCOUNT_CCID = gl4.CODE_COMBINATION_ID
--AND c.WIP_CLEARING_ACCOUNT_CCID = gl5.CODE_COMBINATION_ID
ANDb.book_type_code =c.book_type_code
ANDc.book_type_code ='CORP BOOK'
--FA YTD Depreciation
--FA For getting YTD Depreciation..etc...
SELECTfbv.book_type_code
,fab.asset_number,fab.asset_id
,fab.tag_number
,fbv.date_placed_in_service
,fab.description
,fcb.segment1||'.'||fcb.segment2 asset_category
,fcb.segment1||'.'||fcb.segment2 asset_category1
,fbv.costasset_cost
,fbv.original_cost original_asset_cost
,fbv.life_in_months asset_life
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'ACCUM')accum_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'YTD')ytd_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'RUN')dep_this_run
,gcc.segment2 department_no
,(SELECTppx.full_name FROM per_people_x ppx WHEREppx.person_id = fdh.assigned_to ANDROWNUM = 1)custodian
,(SELECTppx.employee_number FROMper_people_x ppx WHERE ppx.person_id =fdh.assigned_to ANDROWNUM = 1)employee_number
,(SELECTpo_number FROM fa_invoice_details_v WHEREasset_id = fbv.asset_id ANDROWNUM = 1)po_number
,(SELECTinvoice_number FROM fa_invoice_details_v WHEREasset_id = fbv.asset_id ANDROWNUM = 1)invoice_number
,(SELECTvendor_number FROM fa_invoice_details_v WHEREasset_id = fbv.asset_id ANDROWNUM = 1)vendor_number
,(SELECTvendor_name FROM fa_invoice_details_v WHEREasset_id = fbv.asset_id ANDROWNUM = 1)vendor_name
,fab.serial_number
,fl.segment3||'.'||fl.segment4 location_flexfield
,fab.attribute1 tax_major_category
,fab.attribute2 tax_minor_category
,(SELECTretirement_type_code FROM fa_retirements WHEREretirement_id = fdh.retirement_id ANDROWNUM = 1)retirement_type
,(SELECTsegment1||'.'||segment2 FROMfa_asset_keywords WHERE code_combination_id =fab.asset_key_ccid ANDROWNUM = 1)asset_key
,fdp.period_name
,fab.attribute3 acquisition_date
,fab.manufacturer_name
,fbv.retirement_pending_flag
FROMapps.fa_additions_v fab
,apps.fa_books_v fbv
,apps.fa_categories_b fcb
,apps.fa_deprn_periods fdp
,apps.fa_distribution_history fdh
,apps.gl_code_combinations gcc
,apps.fa_locations fl
WHERE1=1
ANDfab.asset_id = fbv.asset_id
ANDfcb.category_id =fab.asset_category_id
ANDfbv.transaction_header_id_out ISNULL
ANDfdp.book_type_code =fbv.book_type_code
ANDfdh.asset_id = fbv.asset_id
ANDfdh.code_combination_id =gcc.code_combination_id
ANDfdh.location_id=fl.location_id
ANDfbv.transaction_header_id_out ISNULL
ANDfdh.transaction_header_id_out ISNULL
/
SELECTfnd_id_flex_segments.application_column_name,
fnd_id_flex_segments.segment_name,fnd_id_flex_segments.segment_num,
fnd_flex_values.flex_value_set_id,
fnd_flex_values.flex_value AS SEGMENT,
fnd_flex_values_tl.description,fnd_id_flex_segments.application_id,fnd_flex_values.PARENT_FLEX_VALUE_LOW asparent
FROMapplsys.fnd_flex_values fnd_flex_values,
applsys.fnd_flex_values_tl fnd_flex_values_tl,
applsys.fnd_id_flex_segments fnd_id_flex_segments
WHEREfnd_flex_values.flex_value_id =fnd_flex_values_tl.flex_value_id
ANDfnd_flex_values.flex_value_set_id =
fnd_id_flex_segments.flex_value_set_id
ANDfnd_id_flex_segments.flex_value_set_id =1008035
-- AND fnd_flex_values.flex_value <> 'T'
ANDfnd_id_flex_segments.application_id ='140'
ANDfnd_id_flex_segments.segment_num =3
/
/* Formatted on 2013/02/18 23:21 (Formatter Plus v4.8.8) */
SELECTfa_additions.asset_id,disc_gl_set_of_books.set_of_books_id,
disc_gl_set_of_books.description AS set_of_books,
disc_gl_set_of_books.set_of_book_currency,fa_additions.asset_number,
fa_additions.tag_number,fa_additions.description,
fa_additions.manufacturer_name,fa_additions.serial_number,
fa_additions.model_number,fa_categories_b.segment1,
fa_categories_b.segment2,fa_categories_b.segment3,
fa_books.book_type_code,
ROUND( fa_books.COST
* fa_distribution_history.units_assigned
/fa_additions.current_units,
0
)COST,
fa_books.date_effective,fa_books.date_ineffective,
fa_books.date_placed_in_service,fa_books.deprn_start_date,
fa_books.original_cost,fa_additions.current_units,
fa_distribution_history.units_assigned,fa_books.life_in_months,
fa_employees.employee_number,fa_employees.NAME employee_name,
fa_locations.segment1 "STATE", fa_locations.segment2 "CITY",
fa_locations.segment3 "SITE", fa_locations.segment4 "LOCATOR",
fa_category_books.asset_cost_acct "ASSET_ACCOUNT_ID",
segment2.description AS "ASSET_ACCOUNT",fa_book_controls.book_class,
disc_ccid_dsc_mv.account_type,disc_ccid_dsc_mv.code_combination_id,
disc_ccid_dsc_mv.gl_seg1,disc_ccid_dsc_mv.gl_seg2,
disc_ccid_dsc_mv.gl_seg3,disc_ccid_dsc_mv.gl_seg4,
disc_ccid_dsc_mv.gl_seg5,disc_ccid_dsc_mv.gl_seg6,
disc_ccid_dsc_mv.gl_seg7,disc_ccid_dsc_mv.gl_seg8,
disc_ccid_dsc_mv.gl_seg9,disc_ccid_dsc_mv.gl_seg10,
disc_ccid_dsc_mv.gl_seg11,disc_ccid_dsc_mv.gl_name_seg1,
disc_ccid_dsc_mv.gl_name_seg2,disc_ccid_dsc_mv.gl_name_seg3,
disc_ccid_dsc_mv.gl_name_seg4,disc_ccid_dsc_mv.gl_name_seg5,
disc_ccid_dsc_mv.gl_name_seg6,disc_ccid_dsc_mv.gl_name_seg7,
disc_ccid_dsc_mv.gl_name_seg8,disc_ccid_dsc_mv.gl_name_seg9,
disc_ccid_dsc_mv.gl_name_seg10,disc_ccid_dsc_mv.gl_name_seg11,
fa_books.date_placed_in_service "DATE_IN",
--TO_DATE(FA_DISTRIBUTION_HISTORY.DATE_INEFFECTIVE,'DD-MON-RRRR') "DATE_INEFFECTIVE",
--FA_BOOKS.DATE_INEFFECTIVE
NULL"DATE_IN_MONTH",fa_deprn_summary.deprn_amount,
fa_deprn_summary.ytd_deprn,fa_deprn_summary.deprn_reserve,
fa_deprn_summary.deprn_source_code,
fa_deprn_summary.addition_cost_to_clear adjusted_cost,
fa_deprn_periods.period_name
-- disc_fa_invoice_details.vendor_name,
-- disc_fa_invoice_details.invoice_number,
-- disc_fa_invoice_details.invoice_date,
-- disc_fa_invoice_details.fixed_assets_cost AS fa_cost_by_invoice
FROM apps.fa_additions fa_additions,
fa.fa_book_controls fa_book_controls,
fa.fa_books fa_books,
fa.fa_categories_b fa_categories_b,
fa.fa_category_books fa_category_books,
fa.fa_distribution_history fa_distribution_history,
gl.gl_code_combinations gl_code_combinations,
apps.fa_employees fa_employees,
fa.fa_locations fa_locations,
apps.disc_ccid_dsc_mv disc_ccid_dsc_mv,
apps.disc_segment2 segment2,
apps.disc_gl_set_of_books disc_gl_set_of_books,
-- disc_gl_month in_month,
fa.fa_deprn_detail fa_deprn_summary,
fa.fa_deprn_periods fa_deprn_periods
-- disc_fa_invoice_details disc_fa_invoice_details
WHERE fa_books.book_type_code = fa_book_controls.book_type_code
ANDfa_books.asset_id =fa_additions.asset_id
ANDfa_books.date_ineffective ISNULL
ANDfa_distribution_history.book_type_code =fa_books.book_type_code
ANDfa_distribution_history.asset_id =fa_additions.asset_id
ANDfa_distribution_history.date_ineffective ISNULL
ANDfa_categories_b.category_id =fa_additions.asset_category_id
ANDfa_category_books.book_type_code =
fa_distribution_history.book_type_code
ANDfa_category_books.category_id =fa_additions.asset_category_id
ANDgl_code_combinations.code_combination_id =
fa_distribution_history.code_combination_id
ANDfa_employees.employee_id(+)= fa_distribution_history.assigned_to
ANDfa_locations.location_id =fa_distribution_history.location_id
ANDgl_code_combinations.code_combination_id =
disc_ccid_dsc_mv.code_combination_id
ANDsegment2.SEGMENT = fa_category_books.asset_cost_acct
ANDdisc_gl_set_of_books.set_of_books_id =fa_book_controls.set_of_books_id
-- AND fa_books.date_placed_in_service BETWEEN in_month.start_date AND in_month.end_date
ANDfa_deprn_summary.asset_id =fa_distribution_history.asset_id
AND( fa_deprn_periods.period_counter = fa_deprn_summary.period_counter
ANDfa_deprn_periods.book_type_code =fa_deprn_summary.book_type_code
)
ANDfa_deprn_summary.distribution_id =
fa_distribution_history.distribution_id
-- AND fa_books.asset_id = disc_fa_invoice_details.asset_id(+)
-- AND fa_deprn_summary.deprn_source_code = 'D'
--and fa_additions.asset_id = '10000767';
No comments:
Post a Comment