Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 29 August 2019

Query to Get Assets Financial Information in Oracle Apps R12


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

Share this article :

0 comments:

Post a Comment