Solved

cost calculation

Posted on 2004-09-15
6
732 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now