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 AGIS Matched or unmatched Balances at Account level

» Oracle Apps » Oracle Receivables » Oracle EBS R12 SQL to get AGIS Matched or unmatched Balances at Account level

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 AGIS Matched or unmatched Balances at Account level

20 Nov 2017 oracleappsadmin

Please find below the SQL Query to identify the Intercompany Balances for a particular Account whether they have been MATCHED or UNMATCHED to resolve the Reconciliation Issues.

SELECT DISTINCT
A.SEGMENT1 INITIATOR_RECEIVABLE,
A.SEGMENT5 RECEIPIENT_RECEIVABLE,
(   A.SEGMENT1|| ‘.’|| A.SEGMENT2|| ‘.’|| A.SEGMENT3|| ‘.’|| A.SEGMENT4|| ‘.’|| A.SEGMENT5|| ‘.’|| A.SEGMENT6|| ‘.’|| A.SEGMENT7) REC_ACCOUNT,
(A.RECEIVABLES),
B.SEGMENT1 INITIATOR_PAYABLES,
B.SEGMENT5 RECEIPIENT_PAYABLES,
(   B.SEGMENT1|| ‘.’|| B.SEGMENT2|| ‘.’|| B.SEGMENT3|| ‘.’|| B.SEGMENT4|| ‘.’|| B.SEGMENT5|| ‘.’|| B.SEGMENT6|| ‘.’|| B.SEGMENT7) PAY_ACCOUNT,
(B.PAYABLES),
(ABS (A.RECEIVABLES) – ABS (B.PAYABLES)) DIFFERENCE,
DECODE ( (ABS (A.RECEIVABLES) – ABS (B.PAYABLES)),
0, ‘Matched’,
‘Not Matched’) MATCHED_UNMATCHED
FROM ( (SELECT GCC1.SEGMENT1,
GCC1.SEGMENT2,
GCC1.SEGMENT3,
GCC1.SEGMENT4,
GCC1.SEGMENT5,
GCC1.SEGMENT6,
GCC1.SEGMENT7,
GCC1.CODE_COMBINATION_ID,
(PERIOD_NET_DR – PERIOD_NET_CR) RECEIVABLES,
GL.LEDGER_ID,
GB.CURRENCY_CODE,
GL.CURRENCY_CODE GL_CURR
FROM GL_BALANCES GB,
GL_CODE_COMBINATIONS GCC1,
GL_LEDGERS GL,
FND_FLEX_VALUES FFV
WHERE     GB.CODE_COMBINATION_ID = GCC1.CODE_COMBINATION_ID
AND GB.LEDGER_ID = GL.LEDGER_ID
AND GB.CURRENCY_CODE = GL.CURRENCY_CODE
AND FFV.FLEX_VALUE = GCC1.SEGMENT1
AND SEGMENT4 = ‘<ENTER_ACCOUNT_DTLS>’
AND SEGMENT6 = ‘<ENTER_INTERCOMAPNY_DTLS>’
AND SEGMENT5 <> ‘000’
AND PERIOD_NAME = ‘DEC-15’
AND GB.CURRENCY_CODE = FFV.ATTRIBUTE16) A),
(SELECT GCC2.SEGMENT1,
GCC2.SEGMENT2,
GCC2.SEGMENT3,
GCC2.SEGMENT4,
GCC2.SEGMENT5,
GCC2.SEGMENT6,
GCC2.SEGMENT7,
GCC2.CODE_COMBINATION_ID,
(PERIOD_NET_DR – PERIOD_NET_CR) PAYABLES,
GL.LEDGER_ID,
GB.CURRENCY_CODE,
GL.CURRENCY_CODE GL_CURR
FROM GL_BALANCES GB, GL_CODE_COMBINATIONS GCC2, GL_LEDGERS GL
WHERE     GB.CODE_COMBINATION_ID = GCC2.CODE_COMBINATION_ID
AND GB.LEDGER_ID = GL.LEDGER_ID
AND GB.CURRENCY_CODE = GL.CURRENCY_CODE
AND SEGMENT4 = ‘<ENTER_ACCOUNT_DTLS>’
AND SEGMENT6 = ‘<ENTER_INTERCOMAPNY_DTLS>’
AND SEGMENT5 <> ‘000’
AND PERIOD_NAME = ‘DEC-15’) B
WHERE     A.SEGMENT1 = B.SEGMENT5
AND B.SEGMENT1 = A.SEGMENT5
AND A.CURRENCY_CODE = B.CURRENCY_CODE
AND A.CURRENCY_CODE = A.GL_CURR
AND A.LEDGER_ID = B.LEDGER_ID

Oracle Receivables AGIS Intercompany balances, AGIS R12 Reconciliation issues, AGIS R12 unmatched intercompany account details, Oracle Apps AGIS R12 Reconciliation issues

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