Invoices/Payments Posted

SQLs to find out posted Invoices and Payments in last 7 days

------------------------------------------------------------------------------------------
Invoices Posted in Last 7 Days
------------------------------------------------------------------------------------------
SELECT AEH.set_of_books_id        set_of_books_id
      ,GLC.user_je_category_name  journal_category
      ,AEH.accounting_date
      ,ALC.displayed_field        event_type
      ,PV.vendor_name
      ,AI.invoice_num
      ,DECODE(AI.doc_sequence_id, NULL, AI.voucher_num, AI.doc_sequence_value) voucher_num
      ,AEL.ae_line_number         line_num
      ,ALC2.displayed_field       line_type
      ,AEL.code_combination_id    account_ccid
      ,AEL.currency_code
      ,AEL.entered_dr
      ,AEL.entered_cr
      ,AEL.accounted_dr
      ,AEL.accounted_cr
      ,AEH.gl_transfer_flag
      ,( SELECT name
           FROM APPS.hr_all_organization_units
          WHERE organization_id = ai.org_id) org_name
      ,AI.org_id
  FROM APPS.ap_accounting_events_all AAE
      ,APPS.ap_ae_headers_all        AEH
      ,APPS.ap_ae_lines_all          AEL
      ,APPS.po_vendors               PV
      ,APPS.ap_invoices_all          AI
      ,APPS.gl_je_categories         GLC
      ,APPS.ap_lookup_codes          ALC
      ,APPS.ap_lookup_codes          ALC2
WHERE  AAE.accounting_event_id = AEH.accounting_event_id
  AND  AEH.ae_header_id        = AEL.ae_header_id
  AND  AAE.source_id           = AI.invoice_id
  AND  AAE.org_id              = AI.org_id
  AND  AI.vendor_id            = PV.vendor_id
  AND  GLC.je_category_name    = AEH.ae_category
  AND  ALC.lookup_type         = 'EVENT TYPE'
  AND  ALC.lookup_code         = AAE.event_type_code
  AND  ALC2.lookup_type        = 'AE LINE TYPE'
  AND  ALC2.lookup_code        = AEL.ae_line_type_code
  AND  AEH.AE_Category         = 'Purchase Invoices'
  AND  ael.ae_line_type_code   = 'LIABILITY'
  AND  AEH.accounting_date > SYSDATE - 7
  AND  AI.org_id IN (12,34,56,78,90)
ORDER BY aeh.ae_category, AI.org_id, AEH.accounting_date

------------------------------------------------------------------------------------------
Payments Posted in Last 7 Days
------------------------------------------------------------------------------------------

SELECT AEH.set_of_books_id        set_of_books_id
      ,GLC.user_je_category_name  journal_category
      ,AEH.accounting_date
      ,ALC.displayed_field        event_type
      ,PV.vendor_name
      ,AI.invoice_num       
      ,DECODE(AI.doc_sequence_id, NULL, AI.voucher_num, AI.doc_sequence_value) voucher_num
      ,AEL.ae_line_number         line_num
      ,ALC2.displayed_field       line_type
      ,AEL.code_combination_id    account_ccid
      ,AEL.currency_code          
      ,AEL.entered_dr
      ,AEL.entered_cr
      ,AEL.accounted_dr
      ,AEL.accounted_cr
      ,AEH.gl_transfer_flag
      ,( SELECT name
           FROM APPS.hr_all_organization_units
          WHERE organization_id = ai.org_id) org_name
      ,AI.org_id
  FROM APPS.ap_accounting_events_all AAE
      ,APPS.ap_ae_headers_all        AEH
      ,APPS.ap_ae_lines_all          AEL
      ,APPS.po_vendors               PV
      ,APPS.ap_invoices_all          AI
      ,APPS.ap_invoice_payments_all  AIP
      ,APPS.gl_je_categories         GLC
      ,APPS.ap_lookup_codes          ALC
      ,APPS.ap_lookup_codes          ALC2
WHERE  AAE.accounting_event_id = AEH.accounting_event_id
  AND  AEH.ae_header_id        = AEL.ae_header_id
  AND  AAE.source_id           = AIP.check_id
  AND  AIP.invoice_id          = AI.invoice_id
  AND  AIP.accounting_event_id = AEH.accounting_event_id
  AND  AIP.invoice_payment_id  = AEL.source_id
  AND  AAE.org_id              = AI.org_id
  AND  AI.vendor_id            = PV.vendor_id
  AND  GLC.je_category_name    = AEH.ae_category
  AND  ALC.lookup_type         = 'EVENT TYPE'
  AND  ALC.lookup_code         = AAE.event_type_code
  AND  ALC2.lookup_type        = 'AE LINE TYPE'
  AND  ALC2.lookup_code        = AEL.ae_line_type_code
  AND  AEH.AE_Category         = 'Payments'
  AND  ael.ae_line_type_code   = 'LIABILITY'
  AND  AEH.accounting_date > SYSDATE - 7
  AND  AI.org_id IN (12,34,56,78,90)
ORDER BY aeh.ae_category, AI.org_id, AEH.accounting_date, AI.invoice_id

------------------------------------------------------------------------------------------
R11i Payables Transfer To GL Reference Field Mappings [ID 186230.1] 
------------------------------------------------------------------------------------------
For Invoices: 
------------------------------------------------------------------------------------------
                     AP_AE_LINES_ALL  GL_INTERFACE     GL_IMPORT_REFERENCES  GL_JE_LINES
------------------------------------------------------------------------------------------
Vendor Name          reference1       reference21      reference_1           reference_1
Invoice ID           reference2       reference22      reference_2           reference_2
Inv Dist Line Num    reference3       reference23      reference_3           reference_3
Blank                reference4       reference24      reference_4           reference_4
Invoice Number       reference5       reference25      reference_5           reference_5
Category             reference6       reference26      reference_6           reference_6
Set of Books ID      reference7       reference27      reference_7           reference_7
blank                reference8       reference28      reference_8           reference_8
blank                reference9       reference29      reference_9           reference_9
Line Type            reference10      reference30      reference_10          reference_10
------------------------------------------------------------------------------------------ 
For Payments: 
------------------------------------------------------------------------------------------
                     AP_AE_LINES_ALL  GL_INTERFACE     GL_IMPORT_REFERENCES  GL_JE_LINES
------------------------------------------------------------------------------------------
Vendor Name          reference1       reference21      reference_1           reference_1
Invoice ID           reference2       reference22      reference_2           reference_2
Check ID             reference3       reference23      reference_3           reference_3
Check Number         reference4       reference24      reference_4           reference_4
Invoice Number       reference5       reference25      reference_5           reference_5
Category             reference6       reference26      reference_6           reference_6
Set of Books ID      reference7       reference27      reference_7           reference_7
Inv Dist Line Num    reference8       reference28      reference_8           reference_8
Invoice Payment ID   reference9       reference29      reference_9           reference_9
Line Type            reference10      reference30      reference_10          reference_10
------------------------------------------------------------------------------------------