11i GL Query

11i AP-GL Links


SELECT distinct b.period_name, b.line_reference_2, a.po_order_number, a.rcv_receipt_num "Receipt Number",
       a.trx_date "Receipt Date", a.trx_number_displayed "Invoice Number",
       a.accounting_date "Invoice Date", a.third_party_name "Vendor Name",
       a.third_party_number "Vendor Number", b.je_source "Source", b.SET_OF_BOOKS_ID "set_of_books_id" ,
          segment1
       || '-'
       || segment2
       || '-'
       || segment3
       || '-'
       || segment4
       || '-'
       || segment5
       || '-'
       || segment6
       || '-'
       || segment7
       || '-'
       || segment8 SEGMENT,
          segment1
       || '-'
       || segment2
       || '-'
       || segment3
       || '-'
       || segment4
       || '-'
       || segment5
       || '-'
       || segment6
       || '-'
       || segment7
       || '-'
       || segment8 segment2,
       a.currency_code, a.entered_dr, a.entered_cr, a.accounted_dr,
       a.accounted_cr, a.je_header_id, a.je_line_num, a.code_combination_id,
       a.doc_sequence_value "Vocher Number", a.gl_date "GL_Date",
       a.attribute1 "Staff Number", a.attribute2 "Project",
       a.attribute3 "Customer Num/Name", a.description "Description"
  FROM
    (SELECT ph.segment1 po_order_number, rsh.receipt_num rcv_receipt_num,
               i.invoice_date trx_date, i.invoice_num trx_number_displayed,
               aeh.accounting_date accounting_date,
               v.vendor_name third_party_name, v.segment1 third_party_number,
               ael.currency_code currency_code, ael.entered_dr entered_dr,
               ael.entered_cr entered_cr, ael.accounted_dr accounted_dr,
               ael.accounted_cr accounted_cr, r.je_header_id je_header_id,
               r.je_line_num je_line_num,
               ael.code_combination_id code_combination_id,
               200 application_id, i.gl_date, i.doc_sequence_value,
               d.description, d.attribute1, d.attribute2, d.attribute3
          FROM gl_import_references r,
               gl_je_categories jc,
               ap_ae_lines_all ael,
               ap_ae_headers_all aeh,
               ap_invoice_distributions_all d,
               ap_invoices_all i,
               ap_accounting_events_all ae,
               po_releases_all pr,
               po_headers_all ph,
               po_distributions_all pd,
               rcv_transactions rct,
               rcv_shipment_headers rsh,
               po_vendor_sites_all vs,
               po_vendors v,
               ap_tax_codes_all AT,
               gl_daily_conversion_types glct,
               fnd_document_sequences fd,
               ap_lookup_codes l1,
               ap_lookup_codes l2,
               ap_lookup_codes l3,
               ap_lookup_codes l4,
               ap_lookup_codes l5,
               ap_lookup_codes l6,
               ap_lookup_codes l7,
               ap_lookup_codes l8,
               ap_lookup_codes l9,
               ap_lookup_codes l10,
               ap_lookup_codes l11,
               ap_lookup_codes l12
         WHERE l2.lookup_code(+) = d.line_type_lookup_code
           AND l2.lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
           AND l3.lookup_code = ael.ae_line_type_code
           AND l3.lookup_type = 'AE LINE TYPE'
           AND l4.lookup_code = aeh.gl_transfer_flag || ''
           AND l4.lookup_code = aeh.gl_transfer_flag || ''
           AND l4.lookup_type = 'POSTING STATUS'
           AND l5.lookup_code = ae.event_type_code
           AND l5.lookup_type = 'EVENT TYPE'
           AND l6.lookup_code(+) = aeh.accounting_error_code
           AND l6.lookup_type(+) = 'ACCOUNTING ERROR TYPE'
           AND l7.lookup_code(+) = ael.accounting_error_code
           AND l7.lookup_type(+) = 'ACCOUNTING ERROR TYPE'
           AND l8.lookup_code = 'Invoices'
           AND l8.lookup_type = 'POSTING CATEGORY'
           AND l9.lookup_code(+) = ael.gl_transfer_error_code
           AND l9.lookup_type(+) = 'POSTING EXCEPTIONS'
           AND l10.lookup_code = 'EVENT'
           AND l10.lookup_type = 'VIEW_ACCOUNTING'
           AND l11.lookup_code = 'LINE'
           AND l11.lookup_type = 'VIEW_ACCOUNTING'
           AND l12.lookup_code = 'DIST_LINE_NUM'
           AND l12.lookup_type = 'VIEW_ACCOUNTING'
           AND jc.je_category_name = aeh.ae_category
           AND rsh.shipment_header_id(+) = rct.shipment_header_id
           AND d.rcv_transaction_id = rct.transaction_id(+)
           AND pr.po_release_id(+) = pd.po_release_id
           AND ph.po_header_id(+) = pd.po_header_id
           AND pd.po_distribution_id(+) = d.po_distribution_id
           AND ael.currency_conversion_type = glct.conversion_type(+)
           AND ael.subledger_doc_sequence_id = fd.doc_sequence_id(+)
           AND ael.third_party_sub_id = vs.vendor_site_id
           AND ael.third_party_id = v.vendor_id
          -- AND l1.lookup_code = i.invoice_type_lookup_code
          -- AND l1.lookup_type = 'INVOICE TYPE'                      
           AND AT.tax_id(+) = ael.tax_code_id
           AND ae.accounting_event_id = aeh.accounting_event_id
           AND i.invoice_id(+) = ae.source_id
          --- AND ae.source_table = 'AP_INVOICES'
        AND (ae.source_table = 'AP_INVOICES'    
               OR  ae.source_table = 'AP_CHECKS'
                OR  ae.source_table = 'AP_INVOICE_PAYMENTS')

           AND aeh.ae_header_id = ael.ae_header_id
           AND DECODE (ael.source_table,
                       'AP_INVOICE_DISTRIBUTIONS', ael.source_id,
                       NULL
                      ) = d.invoice_distribution_id(+)
           AND ael.gl_sl_link_id = r.gl_sl_link_id
        UNION ALL
        SELECT poh.segment1 po_order_number, rsh.receipt_num rcv_receipt_num,
               rct.transaction_date trx_date, NULL trx_number_displayed,
               rrs.accounting_date accounting_date,
               pov.vendor_name third_party_name,
               pov.segment1 third_party_number, rrs.currency_code,
               rrs.entered_dr, rrs.entered_cr, rrs.accounted_dr,
               rrs.accounted_cr, r.je_header_id, r.je_line_num,
               rrs.code_combination_id, 201 application_id,null, --ai.gl_date,
              null,poh.COMMENTS DESCRIPTION,null,-- ai.doc_sequence_value, aid.description, aid.attribute1,
              null,null-- aid.attribute2, aid.attribute3
          FROM gl_import_references r,
               rcv_receiving_sub_ledger rrs,
               --ap_invoice_distributions_all aid,
              -- ap_invoices_all ai,
               po_distributions_all pod,
               po_headers_all poh,
               rcv_transactions rct,
               rcv_shipment_headers rsh,
               po_vendors pov
         WHERE rrs.gl_sl_link_id = r.gl_sl_link_id
           AND rct.transaction_id = rrs.rcv_transaction_id
           AND rsh.shipment_header_id = rct.shipment_header_id
           AND pod.po_distribution_id = rrs.reference3
           AND poh.po_header_id = pod.po_header_id
          -- AND aid.po_distribution_id(+) = pod.po_distribution_id
          -- AND ai.invoice_id = aid.invoice_id
          -- AND ai.vendor_id = pov.vendor_id
 and exists (select 1
              from ap_invoice_distributions_all aid,ap_invoices_all ai
  where aid.po_distribution_id=pod.po_distribution_id
  AND ai.invoice_id = aid.invoice_id
                       AND ai.vendor_id = pov.vendor_id ) )        a,
       gl_je_journal_lines_v          b,
       gl_summary_combinations_v        c
 WHERE b.je_header_id = a.je_header_id
   AND b.line_je_line_num = a.je_line_num
   AND b.line_code_combination_id = c.code_combination_id
   AND a.code_combination_id = b.line_code_combination_id
   AND b.actual_flag = 'A'
   AND b.batch_status || '' = 'P'
   AND b.currency_code != 'STAT'
   AND (    NVL (b.effective_period_num, 0) >= :cf_period_from
        AND (NVL (b.effective_period_num, 0) <= :cf_period_to)
       )
   AND (    NVL (segment1, 0) >= :p_org_from
        AND (NVL (segment1, 0) <= :p_org_to)
       )
   AND (    NVL (segment5, 0) >= :p_account_from
        AND (NVL (segment5, 0) <= :p_account_to)
       )
and b.SET_OF_BOOKS_ID not in ( 1014,1015,1016 )                                                                                                
AND a.currency_code=decode(:p_currency_code,'INR','INR',
                                              'ALL',a.currency_code,
                                              'NON INR',decode(a.currency_code,'INR','###',a.currency_code))
UNION ALL
SELECT period_name, gl_je_journal_lines_v.line_reference_2,  TO_CHAR (NULL) po_order_number,
       TO_CHAR (NULL) rcv_receipt_num, TO_DATE (NULL) trx_date,
       TO_CHAR (NULL) trx_number_displayed, TO_DATE (NULL) accounting_date,
       TO_CHAR (NULL) third_party_name, TO_CHAR (NULL) third_party_number,
       je_source "Source", gl_je_journal_lines_v.SET_OF_BOOKS_ID "set_of_books_id" ,
          segment1
       || '-'
       || segment2
       || '-'
       || segment3
       || '-'
       || segment4
       || '-'
       || segment5
       || '-'
       || segment6
       || '-'
       || segment7
       || '-'
       || segment8 SEGMENT,
          segment1
       || '-'
       || segment2
       || '-'
       || segment3
       || '-'
       || segment4
       || '-'
       || segment5
       || '-'
       || segment6
       || '-'
       || segment7
       || '-'
       || segment8 segment2,
       gl_je_journal_lines_v.currency_code, line_entered_dr, line_entered_cr,
       line_accounted_dr, line_accounted_cr,
  JE_HEADER_ID,--TO_NUMBER (NULL) je_header_id,
       LINE_JE_LINE_NUM,--TO_NUMBER (NULL) je_line_num,
  line_code_combination_id,
       DOC_SEQUENCE_VALUE,--TO_NUMBER (NULL) doc_sequence_value,
  HEADER_EFFECTIVE_DATE,--TO_DATE (NULL) gl_date,
       LINE_ATTRIBUTE1,--TO_CHAR (NULL) attribute1,
  LINE_ATTRIBUTE2,--TO_CHAR (NULL) attribute2,
       LINE_ATTRIBUTE3,--TO_CHAR (NULL) attribute3,
  LINE_DESCRIPTION description--TO_CHAR (NULL) description
  FROM gl_je_journal_lines_v , gl_summary_combinations_v, gl_sets_of_books
 WHERE gl_summary_combinations_v.code_combination_id =
                                gl_je_journal_lines_v.line_code_combination_id
   AND actual_flag = 'A'
   AND batch_status || '' = 'P'
   AND gl_je_journal_lines_v.currency_code != 'STAT'
   AND je_source NOT IN ('Payables', 'Purchasing')  
   AND gl_je_journal_lines_v.set_of_books_id =
                                              gl_sets_of_books.set_of_books_id
   AND gl_sets_of_books.mrc_sob_type_code = 'P'
   AND (    NVL (effective_period_num, 0) >= :cf_period_from
        AND (NVL (effective_period_num, 0) <= :cf_period_to)
       )
   AND (    NVL (segment1, 0) >= :p_org_from
        AND (NVL (segment1, 0) <= :p_org_to)
       )
   AND (    NVL (segment5, 0) >= :p_account_from
        AND (NVL (segment5, 0) <= :p_account_to)
       )
and gl_je_journal_lines_v.SET_OF_BOOKS_ID not in ( 1014,1015,1016 )                                                                                              -- Added by RAVINDER on 4 Apr 07
AND gl_je_journal_lines_v.currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',gl_je_journal_lines_v.currency_code,
                                              'NON INR',decode(gl_je_journal_lines_v.currency_code,'INR','###',gl_je_journal_lines_v.currency_code)) --Added by Jayshree.D on 10-Dec-10
UNION ALL
SELECT b.period_name, b.line_reference_2, a.po_order_number, a.rcv_receipt_num "Receipt Number",
       a.trx_date "Receipt Date", a.trx_number_displayed "Invoice Number",
       a.accounting_date "Invoice Date", a.third_party_name "Vendor Name",
       a.third_party_number "Vendor Number", b.je_source "Source",  b.SET_OF_BOOKS_ID "set_of_books_id" ,
          segment1
       || '-'
       || segment2
       || '-'
       || segment3
       || '-'
       || segment4
       || '-'
       || segment5
       || '-'
       || segment6
       || '-'
       || segment7
       || '-'
       || segment8 SEGMENT,
          segment1
       || '-'
       || segment2
       || '-'
       || segment3
       || '-'
       || segment4
       || '-'
       || segment5
       || '-'
       || segment6
       || '-'
       || segment7
       || '-'
       || segment8 segment2,
       a.currency_code, a.entered_dr, a.entered_cr, a.accounted_dr,
       a.accounted_cr, a.je_header_id, a.je_line_num, a.code_combination_id,
       a.doc_sequence_value "Vocher Number", a.gl_date "GL_Date",
       a.attribute1 "Staff Number", a.attribute2 "Project",
       a.attribute3 "Customer Num/Name", a.description "Description"
  FROM (SELECT poh.segment1 po_order_number, rsh.receipt_num rcv_receipt_num,
               rct.transaction_date trx_date, NULL trx_number_displayed,
               rrs.accounting_date accounting_date,
               pov.vendor_name third_party_name,
               pov.segment1 third_party_number, rrs.currency_code,
               rrs.entered_dr, rrs.entered_cr, rrs.accounted_dr,
               rrs.accounted_cr, r.je_header_id, r.je_line_num,
               rrs.code_combination_id, 201 application_id,
               TO_DATE (NULL) gl_date, TO_NUMBER (NULL) doc_sequence_value,
               TO_CHAR (NULL) description, TO_CHAR (NULL) attribute1,
               TO_CHAR (NULL) attribute2, TO_CHAR (NULL) attribute3
          FROM gl_import_references r,
               rcv_receiving_sub_ledger rrs,
               po_distributions_all pod,
               po_headers_all poh,
               rcv_transactions rct,
               rcv_shipment_headers rsh,
               po_vendors pov
         WHERE rrs.gl_sl_link_id = r.gl_sl_link_id
           AND rct.transaction_id = rrs.rcv_transaction_id
           AND rsh.shipment_header_id = rct.shipment_header_id
           AND pod.po_distribution_id = rrs.reference3
           AND poh.po_header_id = pod.po_header_id
           AND NOT EXISTS (SELECT 1
                             FROM ap_invoice_distributions_all
                            WHERE po_distribution_id = pod.po_distribution_id)
           AND poh.vendor_id = pov.vendor_id)    a,
       gl_je_journal_lines_v      b,
       gl_summary_combinations_v  c
 WHERE b.je_header_id = a.je_header_id
   AND b.line_je_line_num = a.je_line_num
   AND b.line_code_combination_id = c.code_combination_id
   AND a.code_combination_id = b.line_code_combination_id
   AND b.actual_flag = 'A'
   AND b.batch_status || '' = 'P'
   AND b.currency_code != 'STAT'
   AND (    NVL (b.effective_period_num, 0) >= :cf_period_from
        AND (NVL (b.effective_period_num, 0) <= :cf_period_to)
       )
   AND (    NVL (segment1, 0) >= :p_org_from
        AND (NVL (segment1, 0) <= :p_org_to)
       )
   AND (    NVL (segment5, 0) >= :p_account_from
        AND (NVL (segment5, 0) <= :p_account_to)
       )
and  b.SET_OF_BOOKS_ID not in ( 1014,1015,1016 )
AND a.currency_code  =decode(:p_currency_code,'INR','INR',
                                              'ALL',a.currency_code,
                                              'NON INR',decode(a.currency_code,'INR','###',a.currency_code))
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UNION --Added by Saurabh on 06-Mar-08
SELECT period_name, gl_je_journal_lines_v.line_reference_2,  TO_CHAR (NULL) po_order_number,
       TO_CHAR (NULL) rcv_receipt_num, TO_DATE (NULL) trx_date,
       TO_CHAR (NULL) trx_number_displayed, TO_DATE (NULL) accounting_date,
       TO_CHAR (NULL) third_party_name, TO_CHAR (NULL) third_party_number,
       je_source "Source",gl_je_journal_lines_v.SET_OF_BOOKS_ID "set_of_books_id" ,
          segment1
       || '-'
       || segment2
       || '-'
       || segment3
       || '-'
       || segment4
       || '-'
       || segment5
       || '-'
       || segment6
       || '-'
       || segment7
       || '-'
       || segment8 SEGMENT,
          segment1
       || '-'
       || segment2
       || '-'
       || segment3
       || '-'
       || segment4
       || '-'
       || segment5
       || '-'
       || segment6
       || '-'
       || segment7
       || '-'
       || segment8 segment2,
       gl_je_journal_lines_v.currency_code, line_entered_dr, line_entered_cr,
       line_accounted_dr, line_accounted_cr,
  JE_HEADER_ID,--TO_NUMBER (NULL) je_header_id,
       LINE_JE_LINE_NUM,--TO_NUMBER (NULL) je_line_num,
  line_code_combination_id,
       DOC_SEQUENCE_VALUE,--TO_NUMBER (NULL) doc_sequence_value,
  HEADER_EFFECTIVE_DATE,--TO_DATE (NULL) gl_date,
       LINE_ATTRIBUTE1,--TO_CHAR (NULL) attribute1,
  LINE_ATTRIBUTE2,--TO_CHAR (NULL) attribute2,
       LINE_ATTRIBUTE3,--TO_CHAR (NULL) attribute3,
  LINE_DESCRIPTION description--TO_CHAR (NULL) description
  FROM gl_je_journal_lines_v , gl_summary_combinations_v, gl_sets_of_books
 WHERE gl_summary_combinations_v.code_combination_id = gl_je_journal_lines_v.line_code_combination_id
   AND actual_flag = 'A'
   AND batch_status || '' = 'P'
   AND gl_je_journal_lines_v.currency_code != 'STAT'
   AND je_source = 'Purchasing'
   AND gl_je_journal_lines_v.set_of_books_id =
                                              gl_sets_of_books.set_of_books_id
   AND gl_sets_of_books.mrc_sob_type_code = 'P'
   AND (    NVL (effective_period_num, 0) >= :cf_period_from
        AND (NVL (effective_period_num, 0) <= :cf_period_to)
       )
   --AND AID.ACCOUNTING_DATE between NVL (:P_Start_Date,sysdate) and NVL (:P_End_Date,sysdate)
   AND (    NVL (segment1, 0) >= :p_org_from
        AND (NVL (segment1, 0) <= :p_org_to)
       )
   AND (    NVL (segment5, 0) >= :p_account_from
        AND (NVL (segment5, 0) <= :p_account_to)
       )
   and gl_je_journal_lines_v.je_header_id in (select distinct je_header_id from gl_je_batches_headers_v where batch_name like 'Reverses%')
and gl_je_journal_lines_v.SET_OF_BOOKS_ID not in ( 1014,1015,1016 )
AND gl_je_journal_lines_v.currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',gl_je_journal_lines_v.currency_code,
                                              'NON INR',decode(gl_je_journal_lines_v.currency_code,'INR','###',gl_je_journal_lines_v.currency_code))