Solved

cost calculation

Posted on 2004-09-15
6
750 Views
Last Modified: 2008-02-01
Oracle 9i

I have table as below

GL_BALANCE

SET_OF_BOOKS   CODE_ID  DEBIT  CREDIT   SEGMENT1 SEGMENT2     AL_FLAG  PERIOD
2                         1051        200                    500000      111000           A            SEP-04
2                         1052                    100        500000      111000           A            SEP-04
2                         1051        200                    500000      112000           A            SEP-04
2                         1052          50                    500000      112000           B            SEP-04
2                         1040        100                    600000      111000           B            SEP-04
2                         1040                      50        600000      111000           A           SEP-04
2                         1040        300                    600000      112000           A           JAN-04
2                         1030        200                    700000      111000          B            FEB-04
2                         1020                    100        700000      112000           A          FEB-04

CODE COMBINATION

CODE ID            SEGMENT1      SEGMENT2
1051                  500000           111000
1052                  500000           111000
1040                  500000           112000
1040                  600000           111000
1040                  600000           112000
1030                  700000           111000
1030                  700000           112000

The result table should be like this

Function P Table

SEGMENT2          COSF        SGA       COMMON    FROMDATE     TODATE
111000               .52           .32         .16             01-AUG-2004 31-AUG-2004
111000               .30           .20         .10             01-JAN-2004  31-JAN-2004
112000               .47           .22         .17             01-SEP-2004  30-SEP-2004
112000               .20           .10         .05             01-JAN-2004  31-JAN-2004

Overall Picture
The user will input Month and Year (AUG,2004)

It should fetch data from GL_BALANCE table joining CODE_ID from CODE COMBINATION table
it will calculate net_debit_credit (DEBIT-CREDIT) segment wise. from JAN 2004 to JUL 2004 where A_FLAG (GL_BALANCE) is 'B'. and for AUG 2004 it should pick up data where A_FLAG(GL_BALANCE) is 'A'

One Exception is : if user enter input (JAN,2004)
then only flag with 'B' in A_FLAG (GL_BALANCE) should be picked up.

example :

Total Cost upto JULY = 10000(COSF 5000+SGA 3000+COMMON 2000)
Total cost upto AUG =  15000(COSF 8000+SGA 5000+COMMON 2000)
COSF = 0.52 (5000+8000) / (10000+15000)
SGA  = 0.32  (3000+5000) / (10000+15000)
COMMON = 0.16 (2000+2000) / (10000+15000)

Also note that SEGMENT1 field
500000 is always COSF
600000 is always SGA
700000 is always COMMON

I guess PL/SQL procedure would be good idea .

Thanks
Dave
0
Comment
Question by:frinpd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 

Author Comment

by:frinpd
ID: 12069711
One change..

GL BALANCE table don't have field SEGMENT1 and SEGMENT2 field .. that's why use CODE COMBINATION table to get SEGMENT1 and SEGMENT2
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 12069745

What have you coded up to now?

0
 

Author Comment

by:frinpd
ID: 12069775
I have made this two query which identify the SEGMENT1 value and SEGMENT2 value
but i don't understand how to put this into loop so it will calculate for each month and count COSF,SGA,COMMON.

select period_name,segment1,sum(period_net_dr-period_net_cr) net from gl_balances glb,gl_code_combinations_kfv gcc
where glb.code_combination_id = gcc.code_combination_id
and period_name = 'SEP-04'
group by period_name,segment1

select segment1,period_name,segment2,sum(netdr) from
(
select period_name,segment1,segment2,period_net_dr,period_net_cr,(period_net_dr - period_net_cr) netdr,gl.code_combination_id
from gl_code_combinations_kfv gcc,gl_balances gl
where gcc.code_combination_id =
gl.code_combination_id
and set_of_books_id = 2
and actual_flag= 'A'
and period_name = 'SEP-04'
group by period_name,segment1,segment2,period_net_dr,period_net_cr,gl.code_combination_id
)
group by segment2,period_name,segment1
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:frinpd
ID: 12070316
I have combined all table in to one.

GL_TEMP

SQL> desc temp_gl;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SEGMENT1                                           VARCHAR2(25)
 SEGMENT2                                           VARCHAR2(25)
 PERIOD_NAME                                        VARCHAR2(15)
 ACTUAL_FLAG                                        VARCHAR2(1)
 PERIOD_NET_DR                                      NUMBER
 PERIOD_NET_CR                                      NUMBER

SEGMENT1    SEGMENT2   PERIOD_NAME  ACTUAL_FLAG   DR       CR
500000         1110000       SEP-04              A                     100      
500000         1120000       SEP-04            A                                 100
600000         1110000      SEP-04              A                      50  
600000         1120000      SEP-04              A                                   50
700000         1110000      SEP-04             B                        100
700000         1120000      SEP-04            A                                   100

TABLE to Dump data

Func P Table

SEGMENT2     COSF                SGA              COMMON        FROMDATE    TODATE
111000           .50                   .40               .20                 01-SEP-2004  30-SEP-2004
112000           .20                   .10               .40                 "                    "

COSF is SEGMENT1 VALUE 500000
SGA is SEGMENT1 VALUE   600000
COMMON  is SEGMENT1 VALUE   700000

COSF = .50 = (5000+8000) / (10000 + 15000)

0
 

Author Comment

by:frinpd
ID: 12123370
Close issue please.. problem solved by myself
0
 

Author Comment

by:frinpd
ID: 12144769
close issue .. solved by myself
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question