AP Intercompany Invoice Details

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
AP Intercompany Invoice Details


When India Localization is installed, the following GST columns are also included: CGST Amount, SGST Amount, IGST Amount, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code, CGST Rate, SGST Rate, IGST Rate, GST Supplier Registration No, GST Registration No, GST PAN No, GST TAN No.
select
x.*
from
(
select
      gl.name                                    ledger
    , hou.name                                   operating_unit
    , aia.source                                 invoice_source
    , aps.vendor_name
    , aps.segment1                               vendor_num
    , assa.vendor_site_code
    , aia.invoice_num
    , xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200)
                                                 invoice_type
    , aia.invoice_date
    , xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id)
                                                 invoice_status
    , xxen_util.client_time(aia.creation_date)   invoice_creation_date
    , aida.accounting_date                       accounting_date
    , aida.period_name                           period
    , aia.invoice_amount                         invoice_amount
    , aia.total_tax_amount                       invoice_tax_amount
    , aia.amount_paid                            invoice_paid_amount
    , aia.invoice_currency_code
    , aia.exchange_rate
    , aia.exchange_rate_type
    , aia.exchange_date
    , nvl(aia.base_amount,aia.invoice_amount)    invoice_acctd_amount
    , aia.cancelled_date                         invoice_cancelled_date
    , aia.cancelled_amount                       invoice_cancelled_amount
    , aila.line_number
    , msik.product
    , msik.product_description
    , aila.description                           invoice_line_description
    , aila.quantity_invoiced
    , nvl( ( select mufm.uom_code
             from  mtl_units_of_measure mufm
             where mufm.unit_of_measure = aila.unit_meas_lookup_code
             and   rownum=1
            )
         ,  aila.unit_meas_lookup_code
         )                                       unit_meas_lookup_code
    , aila.unit_price
    , aila.amount                                line_amount
    , aida.distribution_line_number
    , gcck.concatenated_segments                 distribution_account
    , aida.amount                                distribution_amount
    , aida.base_amount                           distribution_acctd_amount
    , aia.invoice_id
    , aia.reference_1                            customer_trx_id
    , aila.reference_1                           customer_trx_line_id
&il_tax_columns_inner_amt
&il_tax_columns_inner_rate
&il_tax_columns_inner_tp
    from
      ap_invoices_all              aia
    , ap_invoice_lines_all         aila
    , ap_invoice_distributions_all aida
    , ap_suppliers                 aps
    , ap_supplier_sites_all        assa
    , hr_operating_units           hou
    , gl_ledgers                   gl
    , gl_code_combinations_kfv     gcck
    , ( select msik.inventory_item_id
             , msik.concatenated_segments product
             , msik.description           product_description
        from   mtl_system_items_b_kfv       msik
        where  msik.organization_id =
               (select mp.master_organization_id
                from   mtl_parameters mp
                where  rownum=1
               )
       )                           msik
&il_from
    where
        aia.invoice_id                   = aila.invoice_id
    and aila.invoice_id                  = aida.invoice_id (+)
    and aila.line_number                 = aida.invoice_line_number (+)
    and aia.vendor_id                    = aps.vendor_id
    and aia.vendor_site_id               = assa.vendor_site_id
    and aia.set_of_books_id              = gl.ledger_id
    and aia.org_id                       = hou.organization_id
    and aila.inventory_item_id           = msik.inventory_item_id (+)
    and aida.dist_code_combination_id    = gcck.code_combination_id (+)
    and aia.source                       = 'Intercompany'
    and aila.line_type_lookup_code       = 'ITEM'
    and nvl(aila.discarded_flag,'N')     = 'N'
    and 1=1
&il_where
) x
    order by
      x.ledger
    , x.operating_unit
    , x.vendor_name
    , x.vendor_site_code
    , x.invoice_date
    , x.invoice_num
    , x.line_number
    , x.distribution_line_number
Parameter NameSQL textValidation
Ledger
gl.name=:ledger and
hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
LOV
Operating Unit
hou.name=:operating_unit
LOV
Supplier
aps.vendor_name=:supplier_name
LOV
Invoice Number
aia.invoice_num=:invoice_number
LOV
Invoice Type
aia.invoice_type_lookup_code=xxen_util.lookup_code(:invoice_type,'INVOICE TYPE',200)
LOV
Invoice Date From
aia.invoice_date>=:invoice_date_from
Date
Invoice Date To
aia.invoice_date<:invoice_date_to+1
Date
Accounting Date From
aida.accounting_date>=:acc_date_from
Date
Accounting Date To
aida.accounting_date<:acc_date_to+1
Date
Exclude Cancelled
aia.cancelled_date is null
LOV Oracle
Download
Related Blitz Reports
SAM: GL Account Analysis (Drilldown) (with inventory and WIP) TB GL Account Analysis (Drilldown)v2 GL Account Analysis (Drilldown) (with inventory and WIP) COPY OF: TB GL Account Analysis (Drilldown)v3 GL Account Distribution Analysis TB GL Account Analysis (Drilldown)v3 GL Account Analysis AP Invoices and Lines AP Invoice Upload
Blitz Report™