By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,710 Members | 1,626 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,710 IT Pros & Developers. It's quick & easy.

Need to modify stored proc for crystal report. Important.

P: 24
Need to modify the stored proc where we can add the REP_NAME. to an existing crystal report.
I will also need to order by REP_NAME and group by REP_NAME.
Many customer id's can belong to one Sales REP. I need help first on modify the stored proc below.

I must modify the procedure below and the two tables attached as code snippet can help get the
REP_NAME.
================================================== =============================
Instead by Customer we will do by SALESREP.

All this is the same
Expand|Select|Wrap|Line Numbers
  1. PROCEDURE GetSalesReportBySalesRep(
  2.               pcCompanyId IN CUST_BAL_D.COMPANY_ID%TYPE,
  3.               pcYearNo        IN CUST_BAL_D.YEAR_NO%TYPE,
  4.               pcPeriodNo   IN COMPANY_DET.PERIOD_NO%TYPE,
  5.               pCurSalesReportByCust OUT tmcSalesReportByCust
  6. )
  7. IS
  8. cLastYearNo CUST_BAL_D.YEAR_NO%TYPE;
  9.  
  10.  
  11. BEGIN
  12.  
  13.        cLastYearNo := TO_NUMBER(pcYearNo) - 1;
  14.  
  15.   OPEN pCurSalesReportByCust FOR
  16.  
  17. ****************> Just this select statement needs to be modified.
  18.               SELECT CU.CU_CUST_CODE AS CU_CUST_CODE,
  19.                            CU.CU_NAME,
  20.                          CU.POSTAL_CODE,
  21.                          NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,pcYearNo,pcPeriodNo,'MONTH'),0) AS CURRENT_MTD,
  22.                          NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,pcYearNo,pcPeriodNo,'YTD'),0) AS CURRENT_YTD,
  23.                          NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,cLastYearNo,pcPeriodNo,'MONTH'),0) AS LAST_MTD,
  24.                          NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,cLastYearNo,pcPeriodNo,'YTD'),0) AS LAST_YTD
  25.                 FROM CUSTOMER_D CU
  26.                WHERE CU.COMPANY_ID = pcCompanyId
  27.                ORDER BY 5 DESC;
  28.  
  29.  
  30. END GetSalesReportBySalesRep;
  31.  
  32. ----------------------------------------------------------------------------------------
  33. PROCEDURE GetSalesReportByCustomer(
  34.               pcCompanyId IN CUST_BAL_D.COMPANY_ID%TYPE,
  35.               pcYearNo        IN CUST_BAL_D.YEAR_NO%TYPE,
  36.               pcPeriodNo   IN COMPANY_DET.PERIOD_NO%TYPE,
  37.               pCurSalesReportByCust OUT tmcSalesReportByCust
  38. )
  39. IS
  40. cLastYearNo CUST_BAL_D.YEAR_NO%TYPE;
  41.  
  42.  
  43. BEGIN
  44.  
  45.        cLastYearNo := TO_NUMBER(pcYearNo) - 1;
  46.  
  47.  
  48.        OPEN pCurSalesReportByCust FOR
  49.                SELECT CU.CU_CUST_CODE AS CU_CUST_CODE,
  50.                            CU.CU_NAME,
  51.                          CU.POSTAL_CODE,
  52.                          NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,pcYearNo,pcPeriodNo,'MONTH'),0) AS CURRENT_MTD,
  53.                          NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,pcYearNo,pcPeriodNo,'YTD'),0) AS CURRENT_YTD,
  54.                          NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,cLastYearNo,pcPeriodNo,'MONTH'),0) AS LAST_MTD,
  55.                          NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,cLastYearNo,pcPeriodNo,'YTD'),0) AS LAST_YTD
  56.                 FROM CUSTOMER_D CU
  57.                WHERE CU.COMPANY_ID = pcCompanyId
  58.                ORDER BY 5 DESC;
  59.  
  60.  
  61. END GetSalesReportByCustomer;
  62.  
  63.  
  64.  
  65. ==================================================================================================
  66. CREATE TABLE CUSTOMERS_PURCHASES_DETAILS ( 
  67.   COMPANY_ID            CHAR (2)      NOT NULL, 
  68.   CUSTOMER_ID           CHAR (12)     NOT NULL, 
  69.   DISCOUNT_BASE         NUMBER (5,2)  NOT NULL, 
  70.   DISCOUNT_ADD          NUMBER (5,2)  NOT NULL, 
  71.   PERCENT_TRANSPORT     NUMBER (5,2)  NOT NULL, 
  72.   FOB_CODE              CHAR (12), 
  73.   DEFAULT_ORDER_STATUS  CHAR (2), 
  74.   COMMENTS_TERMS        CHAR (30), 
  75.   INTERNAL_COMMENTS     VARCHAR2 (500), 
  76.   SALE_REP_ID           CHAR (4), 
  77.   COMMISSION_PC         NUMBER (5,2)  DEFAULT 0    NOT NULL, 
  78.   CONSTRAINT PK_CUSTOMERS_PURCHASES_DETAILS
  79.   PRIMARY KEY ( COMPANY_ID, CUSTOMER_ID ) 
  80.     USING INDEX 
  81.      TABLESPACE JT_CORE PCTFREE 10
  82.      STORAGE ( INITIAL 2600K NEXT 512K PCTINCREASE 0 )) 
  83.  TABLESPACE JT_CORE
  84.    PCTFREE 10   PCTUSED 40
  85.    INITRANS 1   MAXTRANS 255
  86.  STORAGE ( 
  87.    INITIAL 2600K NEXT 512K PCTINCREASE 0
  88.    MINEXTENTS 2 MAXEXTENTS 2147483645 )
  89.    NOCACHE; 
  90.  
  91.  
  92. ==================================================================================================
  93. CREATE TABLE SALES_REP ( 
  94.   COMPANY_ID           CHAR (2)      NOT NULL, 
  95.   SALES_REP_ID         CHAR (4)      NOT NULL, 
  96.   REP_NAME             VARCHAR2 (30)  NOT NULL, 
  97.   BRANCH               CHAR (5), 
  98.   ACTIVE_YN            CHAR (1)      DEFAULT 'Y', 
  99.   INITIALS             CHAR (3), 
  100.   ADDRESS1             VARCHAR2 (30), 
  101.   ADDRESS2             VARCHAR2 (30), 
  102.   ADDRESS3             VARCHAR2 (30), 
  103.   ADDRESS4             VARCHAR2 (30), 
  104.   ADDRESS5             VARCHAR2 (30), 
  105.   POSTAL_CODE          CHAR (10), 
  106.   CITY_ID              NUMBER (4), 
  107.   TELEPHONE1           VARCHAR2 (20), 
  108.   TELEPHONE2           VARCHAR2 (20), 
  109.   FAX1                 VARCHAR2 (20), 
  110.   FAX2                 VARCHAR2 (20), 
  111.   PAGETTE              VARCHAR2 (20), 
  112.   MOBILE               VARCHAR2 (20), 
  113.   EMAIL                VARCHAR2 (100), 
  114.   CREATION_DATE        DATE, 
  115.   CREATION_USER_ID     CHAR (8), 
  116.   LAST_UPDATE          DATE, 
  117.   LAST_UPDATE_USER_ID  CHAR (8), 
  118.   PLANT_ID             CHAR (4)      DEFAULT 'MAIN'  NOT NULL, 
  119.   CONSTRAINT PK_SALES_REP
  120.   PRIMARY KEY ( COMPANY_ID, SALES_REP_ID ) 
  121.     USING INDEX 
  122.      TABLESPACE JT_CORE PCTFREE 10
  123.      STORAGE ( INITIAL 2600K NEXT 512K PCTINCREASE 0 )) 
  124.  TABLESPACE JT_CORE
  125.    PCTFREE 10   PCTUSED 40
  126.    INITRANS 1   MAXTRANS 255
  127.  STORAGE ( 
  128.    INITIAL 2600K NEXT 512K PCTINCREASE 0
  129.    MINEXTENTS 2 MAXEXTENTS 2147483645 )
  130.    NOCACHE;
  131.  
Dec 18 '07 #1
Share this Question
Share on Google+
1 Reply


amitpatel66
Expert 100+
P: 2,367
Please make use of CODE tags when ever you post any source code in this forum.

MODERATOR
Dec 18 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.