1 PACKAGE CN_GET_COMM_SUMM_DATA_PVT AUTHID CURRENT_USER AS
2 /*$Header: cnvcomms.pls 120.4 2006/03/13 01:04:18 sjustina noship $*/
3
4 TYPE comm_summ_rec_type IS RECORD
5 (srp_plan_assign_id NUMBER,
6 role_name VARCHAR2(80),
7 plan_name VARCHAR2(80),
8 start_date DATE,
9 end_date DATE,
10 ytd_total_earnings NUMBER,
11 ptd_total_earnings NUMBER,
12 salesrep_id NUMBER);
13
14 TYPE comm_summ_tbl_type IS TABLE OF comm_summ_rec_type
15 INDEX BY binary_integer;
16
17 TYPE salesrep_tbl_type IS TABLE OF NUMBER
18 INDEX BY binary_integer;
19
20 TYPE group_code_tbl_type IS TABLE OF VARCHAR2(30)
21 INDEX BY binary_integer;
22 /**
23 Types created by Sarah
24 */
25 TYPE pe_info_rec_type IS RECORD
26 (
27 srp_plan_assign_id NUMBER,
28 quota_group_code VARCHAR2(30),
29 x_annual_quota NUMBER,
30 x_pct_annual_quota NUMBER,
31 x_ytd_target NUMBER,
32 x_ytd_credit NUMBER,
33 x_ytd_earnings NUMBER,
34 x_ptd_target NUMBER,
35 x_ptd_credit NUMBER,
36 x_ptd_earnings NUMBER,
37 x_itd_unachieved_quota NUMBER,
38 x_itd_tot_target NUMBER);
39
40 TYPE pe_info_tbl_type IS TABLE OF pe_info_rec_type
41 INDEX BY binary_integer;
42
43 TYPE salesrep_info_rec_type IS RECORD
44 (
45 x_name VARCHAR2(360),
46 x_emp_num VARCHAR2(30),
47 x_cost_center VARCHAR2(30),
48 x_charge_to_cost_center VARCHAR2(30),
49 x_analyst_name VARCHAR2(100),
50 x_salesrep_id NUMBER);
51
52 TYPE salesrep_info_tbl_type IS TABLE OF salesrep_info_rec_type
53 INDEX BY binary_integer;
54
55 TYPE pe_ptd_credit_info IS RECORD
56 (
57 quota_id NUMBER,
58 x_ptd_credit NUMBER);
59
60 TYPE pe_ptd_credit_tbl_type IS TABLE OF pe_ptd_credit_info
61 INDEX BY binary_integer;
62 /**
63 Types created by Sarah
64 */
65
66 -- gets all salesreps under given analyst
67 PROCEDURE Get_Salesrep_List
68 (p_first IN NUMBER,
69 p_last IN NUMBER,
70 p_period_id IN NUMBER,
71 p_analyst_id IN NUMBER,
72 p_org_id IN NUMBER,
73 x_total_rows OUT NOCOPY NUMBER,
74 x_salesrep_tbl OUT NOCOPY salesrep_tbl_type);
75
76 -- gets salesrep info
77 PROCEDURE Get_Salesrep_Info
78 (p_salesrep_id IN NUMBER,
79 p_org_id IN NUMBER,
80 x_name OUT NOCOPY VARCHAR2,
81 x_emp_num OUT NOCOPY VARCHAR2,
82 x_cost_center OUT NOCOPY VARCHAR2,
83 x_charge_to_cost_center OUT NOCOPY VARCHAR2,
84 x_analyst_name OUT NOCOPY VARCHAR2);
85
86 -- gets comm summ report for given rep - one rec for each plan assigned
87 PROCEDURE Get_Quota_Summary
88 (p_salesrep_id IN NUMBER,
89 p_period_id IN NUMBER,
90 p_credit_type_id IN NUMBER,
91 p_org_id IN NUMBER,
92 x_result_tbl OUT NOCOPY comm_summ_tbl_type);
93 /**
94 Procs created by Sarah
95 */
96 PROCEDURE Get_Quota_Manager_Summary
97 (
98 p_period_id IN NUMBER,
99 p_credit_type_id IN NUMBER,
100 p_org_id IN NUMBER,
101 x_result_tbl OUT NOCOPY comm_summ_tbl_type);
102
103 PROCEDURE Get_Quota_Analyst_Summary
104 (
105 p_period_id IN NUMBER,
106 p_credit_type_id IN NUMBER,
107 p_org_id IN NUMBER,
108 p_analyst_id IN NUMBER,
109 x_result_tbl OUT NOCOPY comm_summ_tbl_type);
110
111 /**
112 Procs created by Sarah
113 */
114 -- gets info for each plan assign and quota group
115 PROCEDURE Get_Pe_Info
116 (p_srp_plan_assign_id IN NUMBER,
117 p_period_id IN NUMBER,
118 p_credit_type_id IN NUMBER,
119 p_quota_group_code IN VARCHAR2,
120 p_quota_id IN NUMBER := NULL ,
121 p_org_id IN NUMBER,
122 x_annual_quota OUT NOCOPY NUMBER,
123 x_pct_annual_quota OUT NOCOPY NUMBER,
124 x_ytd_target OUT NOCOPY NUMBER,
125 x_ytd_credit OUT NOCOPY NUMBER,
126 x_ytd_earnings OUT NOCOPY NUMBER,
127 x_ptd_target OUT NOCOPY NUMBER,
128 x_ptd_credit OUT NOCOPY NUMBER,
129 x_ptd_earnings OUT NOCOPY NUMBER,
130 x_itd_unachieved_quota OUT NOCOPY NUMBER,
131 x_itd_tot_target OUT NOCOPY NUMBER
132 );
133
134 /**
135 Procs created by Sarah
136 */
137 PROCEDURE Get_Salesrep_Pe_Info
138 (
139 p_salesrep_id in number,
140 p_period_id IN NUMBER,
141 p_credit_type_id IN NUMBER,
142 p_org_id IN NUMBER,
143 x_result_tbl OUT NOCOPY pe_info_tbl_type
144 );
145
146 PROCEDURE Get_Manager_Pe_Info
147 (
148 p_period_id IN NUMBER,
149 p_credit_type_id IN NUMBER,
150 p_org_id IN NUMBER,
151 x_result_tbl OUT NOCOPY pe_info_tbl_type
152 );
153
154 PROCEDURE Get_Analyst_Pe_Info
155 (
156 p_period_id IN NUMBER,
157 p_credit_type_id IN NUMBER,
158 p_org_id IN NUMBER,
159 p_analyst_id IN NUMBER,
160 x_result_tbl OUT NOCOPY pe_info_tbl_type
161 );
162
163 PROCEDURE Get_Salesrep_Details
164 (p_salesrep_id in number,
165 p_org_id in number,
166 x_result_tbl out nocopy salesrep_info_tbl_type);
167
168 PROCEDURE Get_Manager_Details
169 (p_org_id in number,
170 x_result_tbl out nocopy salesrep_info_tbl_type);
171
172 PROCEDURE Get_Analyst_Details
173 (
174 p_org_id in number,
175 p_analyst_id in number,
176 x_result_tbl out nocopy salesrep_info_tbl_type);
177 /**
178 Procs created by Sarah
179 */
180 -- get list of all quota groups
181 PROCEDURE Get_Group_Codes
182 (p_org_id IN NUMBER,
183 x_result_tbl OUT NOCOPY group_code_tbl_type);
184
185 PROCEDURE Get_Ptd_Credit
186 (p_salesrep_id IN NUMBER,
187 p_payrun_id IN NUMBER,
188 p_org_id IN NUMBER,
189 x_result_tbl IN OUT NOCOPY pe_ptd_credit_tbl_type
190 );
191
192 FUNCTION GET_CONVERSION_TYPE(p_org_id IN NUMBER) RETURN VARCHAR2;
193
194 END CN_GET_COMM_SUMM_DATA_PVT;
[Home] [Help]