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;
-
1 1499
Please make use of CODE tags when ever you post any source code in this forum.
MODERATOR
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Lalit Parashar |
last post by:
Hi,
I am clueless on how to integrate stored procedure in Crystal Reports using
C#. Any pointers please?
Thanks
Lalit
|
by: Craig in Boulder |
last post by:
I have been trying for over a week to get data from my stored procedure into
a Crystal Report in VS.NET 2003 with no luck. I have all sorts of code parts
but nothing that tell me how then go together. I have two Crystal.NET and
two ASP.NET books. None give a complete example. This is just crazy! The
GUI approach does not seem to work with stored procedures that have
parameters. Or maybe it's this damn SQL2004 beta. Anyway, all I want...
|
by: Learner |
last post by:
Hi Friends,
I am building the dataset programmatically by calling the stored proc
by passing a parameter and i am getting the data into the dataset. As a
test when i bind this dataset to a grid i see the data in the front. So
|
by: Learner |
last post by:
HI Friends,
I am brand new to creating Crystal reports. But overcomed a lot of
problems and so far happy what i could accomplish, calling a stored
proc with a parameter in it. And i can see the results in the Crystal
Reports in the front. Our client would like to be exported to a .rtf
file when an Export button is clicked on. I have no problem exporting
it to(actually there was a problem converting to .pdf or Excel, it goes
to the...
|
by: ZRexRider |
last post by:
Hi,
I've written reports based on stored procedures that receive parameters
and it usually seems straight forward.
Today I would like to use Crystal Reports parameter interface t prompt
for a "Date Range" and then pass the lower and upper date values to the
@StartDate and @EndDate of my SPROC's range.
Unfortunately when I try to build a report off of a SPROC havng two
| |
by: microsoft . public . sqlserver |
last post by:
I am a Web developer. I am new to VB 6.0 and Crystal Report. I have
joined an organisation where there is an application running(developed
in VB6 and crystal report 7). I have to modify one report. In two
columns I have to make minor changes i.e put and = operator. I am
encountering following problem:
1. When I open the report using Seagate crystal report for rational /
32 bit crystal Report Designer there are three columns which are...
|
by: balurajeev |
last post by:
Hi friends ,
I want to create a Crystal report in C# using Parameterised Storred Procedure
This is my Stored Procedure
ALTER proc .
(
@fname varchar(50),
@lname varchar(50),
|
by: Srilathap18783 |
last post by:
My stored proc is compiled and executed in Toad.
But if i try to attach that in crystal report i am getting an error number as
"ORA-01456: may not perform insert/delete/update operation inside a
READ ONLY transaction"
In my proc i have insert ,update and delete statements.
When i checked this error in google i got
Check SET TRANSACTION ON in my crystal reports.
|
by: mirandacascade |
last post by:
Questions toward the bottom of the post.
Situation is this:
1) Access 97
2) SQL Server 2000
3) The Access app:
a) sets up pass-thru query
b) .SQL property of querydef is a string, the contents of which
comprise the call to a stored proc
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |