Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

cost calculation

Posted on 2004-09-15
6
Medium Priority
?
764 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 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

916 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