Need to modify stored proc for crystal report. Important.

[macupryk]

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

PROCEDURE GetSalesReportBySalesRep(
              pcCompanyId IN CUST_BAL_D.COMPANY_ID%TYPE,
              pcYearNo        IN CUST_BAL_D.YEAR_NO%TYPE,
              pcPeriodNo   IN COMPANY_DET.PERIOD_NO%TYPE,
              pCurSalesReportByCust OUT tmcSalesReportByCust
)
IS
cLastYearNo CUST_BAL_D.YEAR_NO%TYPE;
 
 
BEGIN
 
       cLastYearNo := TO_NUMBER(pcYearNo) - 1;
 
  OPEN pCurSalesReportByCust FOR
 
****************> Just this select statement needs to be modified.
              SELECT CU.CU_CUST_CODE AS CU_CUST_CODE,
                           CU.CU_NAME,
                         CU.POSTAL_CODE,
                         NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,pcYearNo,pcPeriodNo,'MONTH'),0) AS CURRENT_MTD,
                         NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,pcYearNo,pcPeriodNo,'YTD'),0) AS CURRENT_YTD,
                         NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,cLastYearNo,pcPeriodNo,'MONTH'),0) AS LAST_MTD,
                         NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,cLastYearNo,pcPeriodNo,'YTD'),0) AS LAST_YTD
                FROM CUSTOMER_D CU
               WHERE CU.COMPANY_ID = pcCompanyId
               ORDER BY 5 DESC;
 
 
END GetSalesReportBySalesRep;
 
----------------------------------------------------------------------------------------
PROCEDURE GetSalesReportByCustomer(
              pcCompanyId IN CUST_BAL_D.COMPANY_ID%TYPE,
              pcYearNo        IN CUST_BAL_D.YEAR_NO%TYPE,
              pcPeriodNo   IN COMPANY_DET.PERIOD_NO%TYPE,
              pCurSalesReportByCust OUT tmcSalesReportByCust
)
IS
cLastYearNo CUST_BAL_D.YEAR_NO%TYPE;
 
 
BEGIN
 
       cLastYearNo := TO_NUMBER(pcYearNo) - 1;
 
 
       OPEN pCurSalesReportByCust FOR
               SELECT CU.CU_CUST_CODE AS CU_CUST_CODE,
                           CU.CU_NAME,
                         CU.POSTAL_CODE,
                         NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,pcYearNo,pcPeriodNo,'MONTH'),0) AS CURRENT_MTD,
                         NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,pcYearNo,pcPeriodNo,'YTD'),0) AS CURRENT_YTD,
                         NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,cLastYearNo,pcPeriodNo,'MONTH'),0) AS LAST_MTD,
                         NVL(GetColumnAmount(CU.COMPANY_ID,CU.CU_CUST_CODE,cLastYearNo,pcPeriodNo,'YTD'),0) AS LAST_YTD
                FROM CUSTOMER_D CU
               WHERE CU.COMPANY_ID = pcCompanyId
               ORDER BY 5 DESC;
 
 
END GetSalesReportByCustomer;
 
 
 
==================================================================================================
CREATE TABLE CUSTOMERS_PURCHASES_DETAILS ( 
  COMPANY_ID            CHAR (2)      NOT NULL, 
  CUSTOMER_ID           CHAR (12)     NOT NULL, 
  DISCOUNT_BASE         NUMBER (5,2)  NOT NULL, 
  DISCOUNT_ADD          NUMBER (5,2)  NOT NULL, 
  PERCENT_TRANSPORT     NUMBER (5,2)  NOT NULL, 
  FOB_CODE              CHAR (12), 
  DEFAULT_ORDER_STATUS  CHAR (2), 
  COMMENTS_TERMS        CHAR (30), 
  INTERNAL_COMMENTS     VARCHAR2 (500), 
  SALE_REP_ID           CHAR (4), 
  COMMISSION_PC         NUMBER (5,2)  DEFAULT 0    NOT NULL, 
  CONSTRAINT PK_CUSTOMERS_PURCHASES_DETAILS
  PRIMARY KEY ( COMPANY_ID, CUSTOMER_ID ) 
    USING INDEX 
     TABLESPACE JT_CORE PCTFREE 10
     STORAGE ( INITIAL 2600K NEXT 512K PCTINCREASE 0 )) 
 TABLESPACE JT_CORE
   PCTFREE 10   PCTUSED 40
   INITRANS 1   MAXTRANS 255
 STORAGE ( 
   INITIAL 2600K NEXT 512K PCTINCREASE 0
   MINEXTENTS 2 MAXEXTENTS 2147483645 )
   NOCACHE; 
 
 
==================================================================================================
CREATE TABLE SALES_REP ( 
  COMPANY_ID           CHAR (2)      NOT NULL, 
  SALES_REP_ID         CHAR (4)      NOT NULL, 
  REP_NAME             VARCHAR2 (30)  NOT NULL, 
  BRANCH               CHAR (5), 
  ACTIVE_YN            CHAR (1)      DEFAULT 'Y', 
  INITIALS             CHAR (3), 
  ADDRESS1             VARCHAR2 (30), 
  ADDRESS2             VARCHAR2 (30), 
  ADDRESS3             VARCHAR2 (30), 
  ADDRESS4             VARCHAR2 (30), 
  ADDRESS5             VARCHAR2 (30), 
  POSTAL_CODE          CHAR (10), 
  CITY_ID              NUMBER (4), 
  TELEPHONE1           VARCHAR2 (20), 
  TELEPHONE2           VARCHAR2 (20), 
  FAX1                 VARCHAR2 (20), 
  FAX2                 VARCHAR2 (20), 
  PAGETTE              VARCHAR2 (20), 
  MOBILE               VARCHAR2 (20), 
  EMAIL                VARCHAR2 (100), 
  CREATION_DATE        DATE, 
  CREATION_USER_ID     CHAR (8), 
  LAST_UPDATE          DATE, 
  LAST_UPDATE_USER_ID  CHAR (8), 
  PLANT_ID             CHAR (4)      DEFAULT 'MAIN'  NOT NULL, 
  CONSTRAINT PK_SALES_REP
  PRIMARY KEY ( COMPANY_ID, SALES_REP_ID ) 
    USING INDEX 
     TABLESPACE JT_CORE PCTFREE 10
     STORAGE ( INITIAL 2600K NEXT 512K PCTINCREASE 0 )) 
 TABLESPACE JT_CORE
   PCTFREE 10   PCTUSED 40
   INITRANS 1   MAXTRANS 255
 STORAGE ( 
   INITIAL 2600K NEXT 512K PCTINCREASE 0
   MINEXTENTS 2 MAXEXTENTS 2147483645 )
   NOCACHE;
 

[amitpatel66]

Please make use of CODE tags when ever you post any source code in this forum.

MODERATOR