1 PACKAGE CN_GET_SRP_DATA_PVT AUTHID CURRENT_USER AS
2 /*$Header: cnvsfgts.pls 115.6 2002/11/21 21:18:29 hlchen ship $*/
3
4 TYPE srp_data_rec_type IS RECORD
5 (srp_id NUMBER,
6 name CN_SRP_HR_DATA.NAME%TYPE,
7 emp_num CN_SRP_HR_DATA.EMP_NUM%TYPE,
8 start_date DATE,
9 end_date DATE,
10 cost_center VARCHAR2(30),
11 comp_group_id NUMBER,
12 comp_group_name CN_QM_MGR_SRP_GROUPS.GROUP_NAME%TYPE,
13 job_code VARCHAR2(240),
14 job_title VARCHAR2(240),
15 disc_job_title VARCHAR2(80),
16 role_id NUMBER,
17 role_name CN_QM_MGR_SRP_GROUPS.ROLE_NAME%TYPE);
18
19 TYPE srp_data_tbl_type IS TABLE OF srp_data_rec_type
20 INDEX BY binary_integer;
21
22 -- Get_Srp_List returns a list of all the salesreps
23 PROCEDURE Get_Srp_List
24 (x_srp_data OUT NOCOPY srp_data_tbl_type);
25
26 -- Search_Srp_Data returns all the salesreps from cn_srp_hr_data along with
27 -- their current job title (using p_date) and comp group assignment. You can
28 -- search over four criteria (name, job title, emp num, and comp group).
29 -- Search is case insensitive and nulls are returned (for % query).
30 PROCEDURE Search_Srp_Data
31 (p_range_low IN NUMBER,
32 p_range_high IN NUMBER,
33 p_date IN DATE,
34 p_search_name IN VARCHAR2 := '%',
35 p_search_job IN VARCHAR2 := '%',
36 p_search_emp_num IN VARCHAR2 := '%',
37 p_search_group IN VARCHAR2 := '%',
38 p_order_by IN NUMBER := 1,
39 p_order_dir IN VARCHAR2 := 'ASC',
40 x_total_rows OUT NOCOPY NUMBER,
41 x_srp_data OUT NOCOPY srp_data_tbl_type);
42
43 -- Get_Srp_Data returns the salesrep information for a given salesrep
44 PROCEDURE Get_Srp_Data
45 (p_srp_id IN NUMBER,
46 x_srp_data OUT NOCOPY srp_data_tbl_type);
47
48 -- Get_Managers returns all the managers assigned to a given salesrep and
49 -- comp group for a given date
50 PROCEDURE Get_Managers
51 (p_srp_id IN NUMBER,
52 p_date IN DATE,
53 p_comp_group_id IN NUMBER,
54 x_srp_data OUT NOCOPY srp_data_tbl_type);
55
56 END CN_GET_SRP_DATA_PVT;