Skip to content

oracleappshelp.com

Node.JS Blogs

  • MongoDB
  • Oracle R12
  • SOA Suite 12c
  • ADF 12c
  • Interview Questions
  • Node.js
  • ReactJS

Oracle EBS R12 SQL to get AP and AGIS Invoices

» Oracle Apps » AP- Account Payables » Oracle EBS R12 SQL to get AP and AGIS Invoices

Categories

  • ADF Issues
  • Angular JS
  • AP- Account Payables
  • Architecture
  • cloud computing
  • Databases
  • Devops
  • FA – Fixed Assets
  • Interview Questions
  • Java
  • Java Basics
  • Java Springs
  • JavaScript
  • JDeveloper
  • MongoDB tutorial
  • Node.js tutorial
  • OM- Order Managment
  • Oracle ADF
  • Oracle Apps
  • Oracle General Ledger
  • Oracle Receivables
  • Oracle SOA
  • ReactJS Tutorial
  • SQL Server
  • System Administrator
  • WebLogic
  • XML Tutorial

Popular Tutorials

  • MongoDB
  • Oracle R12
  • SOA Suite 12c
  • ADF 12c
  • Interview Questions
  • Node.js
  • ReactJS

Pages

  • MongoDB Tutorial for beginners
  • Oracle ADF 12c Tutorials for beginners
  • Oracle EBS R12 Tutorials
  • Oracle SOA Suite 12c Tutorials for beginners
  • React JS Programming Tutorial for beginners
  • RSS Feed
  • Style Components in React JS

Oracle EBS R12 SQL to get AP and AGIS Invoices

20 Nov 2017 oracleappsadmin

The below given SQL Query will retrieve the set of data for AP and AGIS Invoices with consolidated and non consolidated batches based on the AGIS Batch Source Name , Org_id and Flex Value Set Name defined

SELECT AII.INVOICE_ID,
AII.GROUP_ID,
AII.INVOICE_NUM,
AII.INVOICE_AMOUNT,
AII.ORG_ID AP_ORG_ID,
RCTA.ATTRIBUTE9,
FTR.TRANSACTION_LE_ID,
FTR.TP_LE_ID,
FTH.TRX_ID,
RCTA.ORG_ID AR_ORG_ID,
RT.NAME,
FTH.BATCH_ID,
AII.SOURCE,
FTH.TO_LEDGER_ID
FROM AP_INVOICES_INTERFACE AII,
FUN_TRX_HEADERS FTH,
RA_CUSTOMER_TRX_ALL RCTA,
FUN_SUPPLIER_MAPS FSM,
FUN_TRADE_RELATIONS FTR,
RA_BATCH_SOURCES_ALL RBS,
RA_TERMS RT,
FUN_DIST_LINES FDL,
AP_INVOICE_LINES_INTERFACE AILI
WHERE     FTH.TRX_ID = AII.GROUP_ID
AND RCTA.TRX_NUMBER = AII.INVOICE_NUM
AND aii.SOURCE = :<enter_source_name>
AND FSM.RELATION_ID = FTR.RELATION_ID
AND RBS.NAME = 'Global Intercompany'
AND RCTA.BATCH_SOURCE_ID = RBS.BATCH_SOURCE_ID
AND RCTA.ORG_ID = RBS.ORG_ID
AND RCTA.TERM_ID = RT.TERM_ID(+)
AND FSM.VENDOR_ID = AII.VENDOR_ID
AND FSM.VENDOR_SITE_ID = AII.VENDOR_SITE_ID
AND RCTA.ORG_ID = FTR.TRANSACTION_ORG_ID
AND AII.ORG_ID = FTR.TP_ORG_ID
AND AII.WORKFLOW_FLAG IS NULL
AND aii.org_id = :<enter_org_id>
AND AII.INVOICE_ID = AILI.INVOICE_ID
AND FDL.DIST_NUMBER = AILI.LINE_NUMBER
AND AII.GROUP_ID = FTH.TRX_ID
AND FTH.TRX_ID = FDL.TRX_ID
AND FDL.DIST_TYPE_FLAG = 'L'
AND FDL.PARTY_TYPE_FLAG = 'R'
AND FDL.AUTO_GENERATE_FLAG = 'N'
GROUP BY AII.INVOICE_ID,
AII.GROUP_ID,
AII.INVOICE_NUM,
AII.INVOICE_AMOUNT,
FTH.ATTRIBUTE1,
AII.ORG_ID,
RCTA.ATTRIBUTE9,
FTH.ATTRIBUTE2,
FTH.ATTRIBUTE12,
FTR.TRANSACTION_LE_ID,
FTR.TP_LE_ID,
FTH.TRX_ID,
RCTA.ORG_ID,
RT.NAME,
FTH.BATCH_ID,
AII.SOURCE,
FTH.TO_LEDGER_ID,
AII.INVOICE_CURRENCY_CODE,
AII.GL_DATE
ORDER BY AII.ORG_ID, AII.INVOICE_ID;
AP- Account Payables AGIS and AP Invoices for Consolidated batches, AGIS and AP Invoices for Non Consolidated batches, AGIS R12 Reconciliation issues with AP Invoice mapping, AP and AGIS Invoice mapping in R12, Oracle Apps AGIS R12 invoice mappiing with AP

Share this on ...

  • No Related Posts Yet!

Meta

  • Register
  • Log in

Archives

  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • July 2019
  • June 2019
  • May 2019
  • May 2018
  • April 2018
  • November 2017
  • April 2017
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • June 2014
  • May 2014
  • March 2014
  • September 2013
  • May 2013
  • September 2012
  • June 2012

Archives

  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • July 2019
  • June 2019
  • May 2019
  • May 2018
  • April 2018
  • November 2017
  • April 2017
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • June 2014
  • May 2014
  • March 2014
  • September 2013
  • May 2013
  • September 2012
  • June 2012

Archives

  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • July 2019
  • June 2019
  • May 2019
  • May 2018
  • April 2018
  • November 2017
  • April 2017
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • June 2014
  • May 2014
  • March 2014
  • September 2013
  • May 2013
  • September 2012
  • June 2012

Powered by WordPress. Theme: Brawny by Webulous Themes

Recent Posts

  • How to create legal entity registration in Oracle Fusion
  • Query to get organization structure details in Oracle Fusion General Ledger
  • Assign Legal Entities and Balancing segments in Oracle Fusion
  • Create Legal Entity in Oracle Fusion
  • How to Create Implementation Project in Oracle Fusion
  • Purchase Requisition BPM Approval Workflow Tables in Oracle Fusion
  • Query to get Oracle Fusion Payables Cloud approver comments
  • How to create implementation user in Oracle Fusion
  • Functional Setup Manager in Oracle fusion
  • Top 50 AJAX Interview Questions and Answers