SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 13 11:36:46 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE payoutpercentage (
  2  productgroup  VARCHAR2(5),
  3  position      NUMBER(1),
  4  payoutpct     NUMBER(3))
  5  PCTFREE 0
  6  PCTUSED 99;

Table created.

SQL>
SQL> INSERT INTO payoutpercentage VALUES ('ACC', 0, 5);

1 row created.

SQL> INSERT INTO payoutpercentage VALUES ('ACC', 1, 10);

1 row created.

SQL> INSERT INTO payoutpercentage VALUES ('ACC', 2, 15);

1 row created.

SQL> INSERT INTO payoutpercentage VALUES ('ACC', 3, 20);

1 row created.

SQL>
SQL> CREATE TABLE accessoryhistory (
  2  dbid       VARCHAR2(1) DEFAULT ('W'),
  3  statustype VARCHAR2(3),
  4  sku        VARCHAR2(21),
  5  quantity   NUMBER(10,0) DEFAULT 1,
  6  saleprice  NUMBER(19,4) DEFAULT 0,
  7  compdate   DATE DEFAULT SYSDATE,
  8  store      NUMBER(10,0) DEFAULT 0)
  9  PCTFREE 0
 10  PCTUSED 99;

Table created.

SQL>
SQL> INSERT INTO accessoryhistory VALUES ('W', 'ACT', 'ABCD', 6, 100, SYSDATE, 2);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'DEA', '987B', 3, 100, SYSDATE, 1);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'ACT', 'ABCD', 1, 100, SYSDATE, 3);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'DEA', 'RXRX', 2, 100, SYSDATE, 3);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'ACT', 'ABCD', 9, 100, SYSDATE, 2);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'DEA', 'QV23', 7, 100, SYSDATE, 1);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'ACT', 'ABCD', 1, 100, SYSDATE, 4);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'DEA', 'QV23', 3, 100, SYSDATE, 5);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'ACT', 'ABCD', 2, 100, SYSDATE, 2);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'DEA', 'RXRX', 1, 100, SYSDATE, 4);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'ACT', 'RXRX', 1, 100, SYSDATE, 5);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'DEA', 'QV23', 3, 100, SYSDATE, 1);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'ACT', '987B', 1, 100, SYSDATE, 2);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'DEA', 'ABCD', 3, 100, SYSDATE, 1);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'ACT', 'RXRX', 4, 100, SYSDATE, 2);

1 row created.

SQL> INSERT INTO accessoryhistory VALUES ('W', 'DEA', 'QV23', 3, 100, SYSDATE, 3);

1 row created.

SQL>
SQL> CREATE TABLE store (
  2  store      NUMBER(10,0) DEFAULT 0,
  3  name       VARCHAR2(60),
  4  areadir    NUMBER(10,0) DEFAULT 0,
  5  areamgr    NUMBER(10,0) DEFAULT 0,
  6  storemgr1  NUMBER(10,0) DEFAULT 0,
  7  storemgr2  NUMBER(10,0) DEFAULT 0,
  8  asstmgr1   NUMBER(10,0) DEFAULT 0,
  9  asstmgr2   NUMBER(10,0) DEFAULT 0,
 10  asstmgr3   NUMBER(10,0) DEFAULT 0);

Table created.

SQL>
SQL> INSERT INTO store VALUES (1, 'San Francisco', 100, 200, 301, 302, 401, 402, 403);

1 row created.

SQL> INSERT INTO store VALUES (2, 'Denver', 131, 225, 310, 0, 447, 407, 0);

1 row created.

SQL> INSERT INTO store VALUES (3, 'Chicago', 155, 222, 311, 354, 401, 402, 403);

1 row created.

SQL> INSERT INTO store VALUES (4, 'London', 178, 0, 301, 0, 377, 402, 403);

1 row created.

SQL> INSERT INTO store VALUES (5, 'Ankara', 0, 298, 301, 390, 401, 402, 403);

1 row created.

SQL>
SQL> CREATE TABLE sku (
  2  dbid          VARCHAR2(1) DEFAULT ('W'),
  3  sku           VARCHAR2(21),
  4  productgroup  VARCHAR2(5) DEFAULT ('ACC'),
  5  name          VARCHAR2(30));

Table created.

SQL>
SQL> INSERT INTO sku VALUES ('W', 'ABCD', 'ACC', 'Phone');

1 row created.

SQL> INSERT INTO sku VALUES ('W', 'RXRX', 'ACC', 'Microphone');

1 row created.

SQL> INSERT INTO sku VALUES ('W', 'QV23', 'ACC', 'Speaker');

1 row created.

SQL> INSERT INTO sku VALUES ('W', '987B', 'ACC', 'Bracket');

1 row created.

SQL> INSERT INTO sku VALUES ('C', 'ABCD', 'ACC', 'Antenna');

1 row created.

SQL> -- DECODE in the WHERE clause
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2   posn  PLS_INTEGER := 0;
  3   empid PLS_INTEGER := 178;
  4   x     NUMBER;
  5  BEGIN
  6    SELECT NVL(SUM(ah.quantity * ah.saleprice * ap.payoutpct), 0)
  7    INTO x
  8    FROM accessoryhistory ah, payoutpercentage ap,
  9    sku s, store st
 10    WHERE empid = DECODE(posn,
 11                            0, st.areadir,
 12                            1, st.areamgr,
 13                            2, NVL(st.storemgr1, st.storemgr2),
 14                            3, NVL(st.asstmgr1, NVL(st.asstmgr2,
 15                         st.asstmgr3)))
 16    AND ah.statustype IN ('ACT', 'DEA')
 17    AND ah.store = st.store
 18    AND s.dbid = ah.dbid
 19    AND s.sku = ah.sku
 20    AND ap.productgroup = s.productgroup
 21    AND ap.position = posn;
 22
 23    dbms_output.put_line(x);
 24  END;
 25  /
1000

PL/SQL procedure successfully completed.

SQL>

Download Code