SQL query grouping of total posted amount

User_6S20Z

Hello,

How can i group total sum posted amount?

Thanks

Neelam S-Oracle

Hi,

Please could you share the SQL that you have for running the Query?

Is this something you are looking for in the result from Query Manager or from nVision report?

Gans-Oracle

Hello User_6S20Z,

1. Please confirm your database platform and version
2. Please confirm if you are running the query through the application or SQL query tool
3. Provide the details of how you are doing this through application (if running from applications)

Thanks

User_6S20Z

1. Please confirm your database platform and version - SQL Server 2012
2. Please confirm if you are running the query through the application or SQL query tool - Query developed in Application
3. Provide the details of how you are doing this through application (if running from applications) - The query developed is used in nVsion reporting.

This is the query

SELECT A.BUSINESS_UNIT 

 , B.LEDGER 

 , B.ACCOUNT 

 , B.ALTACCT 

 , B.DEPTID 

 , B.OPERATING_UNIT 

 , B.PRODUCT 

 , B.FUND_CODE 

 , B.CLASS_FLD 

 , B.PROGRAM_CODE 

 , B.BUDGET_REF 

 , B.AFFILIATE 

 , B.AFFILIATE_INTRA1 

 , B.AFFILIATE_INTRA2 

 , B.CHARTFIELD1 

 , B.CHARTFIELD2 

 , B.CHARTFIELD3 

 , B.BOOK_CODE 

 , B.PROJECT_ID 

 , B.GL_ADJUST_TYPE 

 , B.CURRENCY_CD 

 , B.STATISTICS_CODE 

 , A.FISCAL_YEAR 

 , A.ACCOUNTING_PERIOD 

 , SUM(B.MONETARY_AMOUNT ) 

 , SUM(B.FOREIGN_AMOUNT ) 

 FROM PS_JRNL_HEADER A 

 , PS_JRNL_LN B 

 WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT 

  AND A.JOURNAL_ID = B.JOURNAL_ID 

  AND A.JOURNAL_DATE = B.JOURNAL_DATE 

  AND A.UNPOST_SEQ = B.UNPOST_SEQ 

  AND A.JRNL_HDR_STATUS IN ('P','U','V') 

  AND B.LEDGER IN ('STAT_LOC','IFRS17_LOC','CASH_LOC') ) 

 GROUP BY A.BUSINESS_UNIT , B.LEDGER , B.ACCOUNT , B.ALTACCT, B.DEPTID , B.OPERATING_UNIT , B.PRODUCT , B.FUND_CODE , B.CLASS_FLD , B.PROGRAM_CODE , B.BUDGET_REF , B.AFFILIATE , B.AFFILIATE_INTRA1 , B.AFFILIATE_INTRA2 , B.CHARTFIELD1 , B.CHARTFIELD2 , B.CHARTFIELD3 , B.PROJECT_ID , B.BOOK_CODE , B.GL_ADJUST_TYPE , B.CURRENCY_CD , B.STATISTICS_CODE , A.FISCAL_YEAR , A.ACCOUNTING_PERIOD 

 UNION 

 SELECT C.BUSINESS_UNIT 

 , C.LEDGER 

 , C.ACCOUNT 

 , C.ALTACCT 

 , C.DEPTID 

 , C.OPERATING_UNIT 

 , C.PRODUCT 

 , C.FUND_CODE 

 , C.CLASS_FLD 

 , C.PROGRAM_CODE 

 , C.BUDGET_REF 

 , C.AFFILIATE 

 , C.AFFILIATE_INTRA1 

 , C.AFFILIATE_INTRA2 

 , C.CHARTFIELD1 

 , C.CHARTFIELD2 

 , C.CHARTFIELD3 

 , C.BOOK_CODE 

 , C.PROJECT_ID 

 , C.GL_ADJUST_TYPE 

 , C.CURRENCY_CD 

 , C.STATISTICS_CODE 

 , C.FISCAL_YEAR 

 , C.ACCOUNTING_PERIOD 

 , SUM( C.POSTED_TOTAL_AMT) 

 , SUM( C.POSTED_TRAN_AMT) 

 FROM PS_LEDGER C 

 WHERE C.LEDGER IN ('STAT_LOC','IFRS17_LOC','CASH_LOC') 

  AND C.ACCOUNTING_PERIOD <> 0 

 GROUP BY C.BUSINESS_UNIT, C.LEDGER, C.ACCOUNT, C.ALTACCT, C.DEPTID, C.OPERATING_UNIT, C.PRODUCT, C.FUND_CODE, C.CLASS_FLD, C.PROGRAM_CODE, C.BUDGET_REF, C.AFFILIATE, C.AFFILIATE_INTRA1, C.AFFILIATE_INTRA2, C.CHARTFIELD1, C.CHARTFIELD2, C.CHARTFIELD3, C.BOOK_CODE, C.PROJECT_ID, C.GL_ADJUST_TYPE, C.CURRENCY_CD, C.STATISTICS_CODE, C.FISCAL_YEAR, C.ACCOUNTING_PERIOD

Akash A-Oracle

Hello User,

I see you want to again group based on Column 7 : Partner reporting unit. This query is already grouped and giving you output.

What I suggest, you create this as View using above SQL, and later you can group based on View.

I hope that helps.

Thanks,

Akash

To help others best identify with your community profile, we recommend that all users update their community username and picture/avatar. Please refer to these instructions: https://community.oracle.com/mosc/kb/articles/11-how-to-edit-your-profile-and-notification-preferences