SELECT DISTINCT a.asset_number,
a.description,
a.asset_type,
d.segment1 asset_key,
c.segment1 major_category,
c.segment2 minor_category,
c.segment3 sub_minor_category,
b.deprn_method_code,
b.life_in_months/12 life,
b.book_type_code,
b.date_placed_in_service,
b.depreciate_flag,
b.cost,
h.units_assigned UNITS,
g.segment1 COMPANY,
g.segment2 DEPARTMENT,
g.segment3 ACCOUNT,
l.segment1 country,
l.segment2 statebuildingfloor,
l.segment3 cityLocationDepartment,
L.SEGMENT4 BUILDING
FROM fa_additions a,
fa_books_v b,
fa_categories c,
fa_asset_keywords d,
gl_code_combinations g,
fa_distribution_history h,
fa_locations l
WHERE a.asset_id = b.asset_id
AND a.asset_id = h.asset_id
AND a.asset_category_id = c.category_id
AND h.code_combination_id = g.code_combination_id
AND h.location_id = l.location_id
AND a.asset_key_ccid = d.CODE_COMBINATION_ID
--AND b.book_type_code LIKE '%' --Specific Book type
AND H.TRANSACTION_HEADER_ID_OUT IS NULL

SELECT DISTINCT
       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
  FROM fa_categories a,
       FA_CATEGORY_BOOK_DEFAULTS b,
       fa_category_books c,
       gl_code_combinations gl1,
       gl_code_combinations gl2,
       gl_code_combinations gl3
 WHERE     a.category_id = b.category_id
       AND c.category_id = b.category_id
       AND c.category_id = a.category_id
       AND a.ENABLED_FLAG = 'Y'
       AND c.ASSET_COST_ACCOUNT_CCID = gl1.CODE_COMBINATION_ID
       AND c.ASSET_CLEARING_ACCOUNT_CCID = gl2.CODE_COMBINATION_ID
       AND c.RESERVE_ACCOUNT_CCID = gl3.CODE_COMBINATION_ID
       AND b.book_type_code = c.book_type_code

SELECT fab.asset_number asset,
       fat.description asset_name,
       fab.asset_type,
       fai.invoice_number,
       fai.po_number,
       (SELECT pla.line_num
        FROM   po_lines_all pla,
               po_distributions_all pda,
               ap_invoice_distributions_all aida
        WHERE  pda.po_line_id = pla.po_line_id
        AND    aida.po_distribution_id = pda.po_distribution_id
        AND    aida.invoice_distribution_id = fai.invoice_distribution_id)
                                                                    po_line_num,
       (SELECT replace(pla.item_description, chr(10) , ' ')
        FROM   po_lines_all pla,
               po_distributions_all pda,
               ap_invoice_distributions_all aida
        WHERE  pda.po_line_id = pla.po_line_id
        AND    aida.po_distribution_id = pda.po_distribution_id
        AND    aida.invoice_distribution_id = fai.invoice_distribution_id)
                                                            po_line_description,
       aps.vendor_name,
       aps.segment1 supplier_number,
       replace(fai.description, chr(10) , ' ')description,
       fai.fixed_assets_cost line_amount,
       fai.invoice_line_number invoice_line,
       fai.ap_distribution_line_number fa_dist_line,
       fai.deleted_flag active,
       fai.payables_batch_name source_batch,
       fai.project_id project_number,
       fai.task_id task_number
FROM   fa_additions_b fab,
       fa_additions_tl fat,
       fa_asset_invoices fai,
       ap_suppliers aps,
       fa_books fb
WHERE  fab.asset_id = fat.asset_id
AND    fai.asset_id = fat.asset_id
AND    aps.vendor_id(+) = fai.po_vendor_id
AND    fb.asset_id = fai.asset_id
AND    fai.date_ineffective IS NULL
AND    fb.date_ineffective IS NULL