Skip to main content

AP TO GL - PREPAY QUERY

AP TO GL - PREPAY QUERY

select
gps.period_name,
gjl.reference_2,
(select  distinct pha.segment1  from ap_invoice_lines_all ailla, po_headers_all pha where ailla.invoice_id=aia.invoice_id and ailla.po_header_id=pha.po_header_id) po_order_number,

(select distinct rsh.receipt_num
from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
where rsh.shipment_header_id=rsl.shipment_header_id
and rsl.shipment_header_id=rt.shipment_header_id
and rsl.shipment_line_id=rt.shipment_line_id
and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null) ) "Receipt Number",
(select distinct rt.transaction_date
from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
where rsh.shipment_header_id=rsl.shipment_header_id
and rsl.shipment_header_id=rt.shipment_header_id
and rsl.shipment_line_id=rt.shipment_line_id
and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null)) "Receipt Date",
        aia.invoice_num   "Invoice Number",
        aia.gl_date   "Invoice Date",
        aps.vendor_name "Vendor Name",
        aps.segment1 "Vendor Number",
        gjh.je_source "Source",
        gjh.ledger_id "set_of_books_id" ,
        cc.concatenated_segments Segment,
        cc.concatenated_segments segment2,
        aia.payment_currency_code currency_code,
--       xal.entered_dr,
--       xal.entered_cr,
--       xal.accounted_dr,
--       xal.accounted_cr,
     
       XDL.UNROUNDED_ENTERED_DR entered_dr,
       XDL.UNROUNDED_ENTERED_CR entered_cr,
       XDL.UNROUNDED_ACCOUNTED_DR accounted_dr,
       XDL.UNROUNDED_ACCOUNTED_CR accounted_cr,
     
     
       gir.je_header_id,
       gir.je_line_num,
       cc.code_combination_id,
       --gps.application_id,
       --
       aia.doc_sequence_value "Vocher Number"
          ,aia.gl_date "GL_Date"
          ,NULL "Staff Number"
          ,null "Project"
          ,null "Customer Num/Name"
          ,xal.description "Description"
FROM ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
ap_prepay_app_dists apad,
xla_distribution_links xdl,
xla_ae_lines xal,
xla_ae_headers xah,
gl_import_references gir,
gl_je_lines gjl,
gl_je_headers gjh, gl_code_combinations_kfv cc,
    gl_period_statuses gps,
    apps.ap_suppliers aps
where aia.invoice_id=aila.invoice_id
and aila.invoice_id=aida.invoice_id
and aila.line_number=aida.invoice_line_number
--and aia.invoice_id=88734
and aida.line_type_lookup_code='PREPAY'
and aida.invoice_distribution_id= apad.prepay_app_distribution_id
and apad.amount<>0
and apad.prepay_app_dist_id=xdl.source_distribution_id_num_1
and xdl.applied_to_source_id_num_1 in (aila.prepay_invoice_id,aila.invoice_id)
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and xal.ae_header_id=xah.ae_header_id
and xal.gl_sl_link_id= gir.gl_sl_link_id
and xal.gl_sl_link_table= gir.gl_sl_link_table
    and cc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
    and cc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
    and aia.VENDOR_ID=aps.VENDOR_ID  
and gir.je_header_id=gjl.je_header_id
and gir.je_line_num=gjl.je_line_num
and gjl.je_header_id=gjh.je_header_id
AND gjh.ledger_id=gps.ledger_id
  AND  101=gps.application_id     
  and gps.set_of_books_id=gjh.ledger_id
     and gjh.je_source ='Payables'
  and gjh.Ledger_ID not in ( 1014,1015,1016 )
AND gjh.period_name       = gps.period_name
    --and cc.segment5 in (:P_MIN_FLEX,:P_MAX_FLEX)
    and ( nvl(xal.accounted_cr,0)<>0 or nvl(xal.accounted_dr,0)<>0)
  
    --and aia.org_id=:P_ORG
    and xah.je_category_name='Purchase Invoices'
  AND (    NVL (gps.effective_period_num, 0) >=:cf_period_from
        AND (NVL (gps.effective_period_num, 0) <=:cf_period_to)
             )
                AND (    NVL (cc.segment5, 0) >= :p_account_from
        AND (NVL (cc.segment5, 0) <= :p_account_to)
       )
         
     AND (    NVL (cc.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (cc.segment1, 0) <= :P_COMPANY_TO)
       )
       AND aia.payment_currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',aia.payment_currency_code,
                                              'NON INR',decode(aia.payment_currency_code,'INR','###',aia.payment_currency_code))
                                              order by 6;

Comments

Popular posts from this blog

R12 Oracle E-Business Tax Configuration Guide

Applies to: Oracle E-Business Tax - Version: 12.0 to 12.2 Information in this document applies to any platform. Abstract R12 Oracle E-Business Tax Configuration Guide Document History R12 Oracle E-Business Tax Configuration 1. Tax Basic Tax Configuration Tax Definition: comprises the tax data that you set up for each tax regime and tax that your company or institution is subject to. The Tax Authority designates the regulations and rates that apply to the tax regime. Required Task List a) External Dependencies Create First Party: Legal Entity and Establishments Create Reporting and Collecting Tax Authorities b) Tax Configuration Create Tax Authorities Party Tax Profiles Create Tax Regimes Create First Party Legal Entity Party Tax Profile Create Tax Create Tax Status Create Tax Jurisdictions Tax Rate Responsibility: Tax Manager Path: Tax Managers -> Tax Managers -> Home Complete the following setup tasks: -Legal Entities and Establishments -Reporting and Collecting Tax Authorities ...

Create Accounting for a Payment generates errors 95333 and 95359

Create Accounting for a Payment generates errors 95333 and 95359 Error: 95333: A conversion rate does not exist to convert USD to AUD for the conversion type Corporate and conversion date 20-MAR-09 for line -25. Please use the Daily Rates form in General Ledger to enter a conversion rate for these currencies, conversion date and conversion type. 95359: There is no accounted amount for the subledger journal entry line. Please inform your system administrator or support representative that: The source assigned to the accounting attribute Accounted Amount has no value for extract line number 88547. Please make sure the source assigned to the accounting attribute Accounted Amount has a valid value, or assign a different source to this accounting attribute. Solution: 1. Specify a conversion rate for the currencies and conversion date mentioned in the error message 95333 Navigation under the General Ledger responsibility: Setup > Currencies > Currency Rates Manager > Daily Rates ...

R12: Payables Holds Explained – Why They are Applied and How to Remove

Invoice Validation checks the matching, tax, period status, exchange rate, and distribution information for invoices you enter. If an invoice has a hold, you can release the hold by correcting the exception that caused Invoice Validation to apply the hold by updating the invoice or the purchase order, or changing the invoice tolerances. Then resubmit Invoice Validation to release the hold. In the Invoice Holds tab, you can manually release certain invoice holds, even if you have not resolved the matching error condition. Here are some of the regular holds and explanation Account Holds Payables uses holds with this type to identify invoices with invalid or incorrect accounts. An invalid account prevents payment of an invoice and prevents Payables from creating accounting entries for an invoice or payment. Dist Acct Invalid:  Invoice distribution distributed to invalid account. To release the holds you must correct account and resubmit invoice validation. ERV Acct Invalid: ...