1 PACKAGE JTF_TTY_MAINTAIN_NA_PVT AUTHID CURRENT_USER AS
2 /* $Header: jtftmnas.pls 120.5 2006/05/24 07:50:38 chchandr noship $ */
3 -- Start of Comments
4 -- PURPOSE
5 -- For handling Admin Excel Export functionalities like Add Org To
6 -- a territory group, update sales team, transfer
7 -- NOTES
8 -- ORACLE INTERNAL USE ONLY: NOT for customer use
9 --
10 -- HISTORY
11 -- 02/27/04 SGKUMAR Created
12 -- 07/07/04 SGKUMAR Added new data types to support new logic
13 -- for better performance
14 -- End of Comments
15 ----
16 -- RECORD TYPES
17
18 TYPE SALESREP_RSC_REC_TYPE IS RECORD(
19 resource_id NUMBER,
20 group_id NUMBER,
21 role_code VARCHAR2(300),
22 mgr_resource_id NUMBER,
23 mgr_group_id NUMBER,
24 mgr_role_code VARCHAR2(300),
25 resource_type VARCHAR2(300),
26 RESOURCE_ATT1 VARCHAR2(300),
27 RESOURCE_ATT2 VARCHAR2(300),
28 RESOURCE_ATT3 VARCHAR2(300),
29 RESOURCE_ATT4 VARCHAR2(300),
30 RESOURCE_ATT5 VARCHAR2(300),
31 RESOURCE_START_DATE DATE,
32 RESOURCE_END_DATE DATE,
33 attribute1 VARCHAR2(300),
34 attribute2 VARCHAR2(300),
35 attribute3 VARCHAR2(300),
36 attribute4 VARCHAR2(300),
37 attribute5 VARCHAR2(300),
38 attribute6 VARCHAR2(300),
39 attribute7 VARCHAR2(300),
40 attribute8 VARCHAR2(300)
41 );
42
43
44 TYPE SALESREP_RSC_OWNERS_REC_TYPE IS RECORD(
45 owner_resource_id NUMBER,
46 owner_group_id NUMBER,
47 owner_role_code VARCHAR2(300),
48 group_id NUMBER
49 );
50
51
52 TYPE OWNER_RSC_REC_TYPE IS RECORD(
53 resource_id jtf_tty_terr_grp_owners.resource_id%TYPE,
54 group_id jtf_tty_terr_grp_owners.rsc_group_id%TYPE,
55 role_code jtf_tty_terr_grp_owners.rsc_role_code%TYPE,
56 delete_flag VARCHAR2(1)
57 );
58 TYPE OWNER_RSC_TBL_TYPE IS TABLE OF OWNER_RSC_REC_TYPE;
59 TYPE SALESREP_RSC_TBL_TYPE IS TABLE OF SALESREP_RSC_REC_TYPE;
60 TYPE SALESREP_RSC_OWNERS_TBL_TYPE IS TABLE OF SALESREP_RSC_OWNERS_REC_TYPE;
61 TYPE EXCEL_SALESREP_RSC_RECTYP IS RECORD(
62 resource_name VARCHAR2(300),
63 group_name VARCHAR2(300),
64 role_name VARCHAR2(300),
65 RESOURCE_ATT1 VARCHAR2(300),
66 RESOURCE_ATT2 VARCHAR2(300),
67 RESOURCE_ATT3 VARCHAR2(300),
68 RESOURCE_ATT4 VARCHAR2(300),
69 RESOURCE_ATT5 VARCHAR2(300),
70 RESOURCE_START_DATE DATE,
71 RESOURCE_END_DATE DATE
72
73 );
74
75 TYPE EXCEL_SALESREP_RSC_TBLTYP IS TABLE OF EXCEL_SALESREP_RSC_RECTYP;
76
77 TYPE AFFECTED_PARTY_REC_TYPE IS RECORD(
78 party_id NUMBER,
79 named_account_id NUMBER,
80 terr_group_account_id NUMBER,
81 attribute1 VARCHAR2(300),
82 attribute2 VARCHAR2(300),
83 attribute3 VARCHAR2(300),
84 attribute4 VARCHAR2(300),
85 attribute5 VARCHAR2(300),
86 attribute6 VARCHAR2(300),
87 attribute7 VARCHAR2(300),
88 attribute8 VARCHAR2(300)
89 );
90
91 TYPE AFFECTED_PARTY_TBL_TYPE IS TABLE OF AFFECTED_PARTY_REC_TYPE;
92
93 PROCEDURE POPULATE_ADMIN_EXCEL_DATA(
94 P_PARTY_NUMBER IN VARCHAR2,
95 P_NAMED_ACCOUNT IN VARCHAR2,
96 P_SITE_TYPE IN VARCHAR2,
97 P_TRADE_NAME IN VARCHAR2,
98 P_DUNS IN VARCHAR2,
99 P_GU_DUNS IN VARCHAR2,
100 P_GU_NAME IN VARCHAR2,
101 P_DU_DUNS IN VARCHAR2,
102 P_DU_NAME IN VARCHAR2,
103 P_CITY IN VARCHAR2,
104 P_STATE IN VARCHAR2,
105 P_POSTAL_CODE IN VARCHAR2,
106 P_TERRITORY_GROUP IN VARCHAR2,
107 P_TO_TERRITORY_GROUP IN VARCHAR2,
108 P_DELETE_FLAG IN VARCHAR2,
109 P_RESOURCE1_NAME IN VARCHAR2,
110 P_GROUP1_NAME IN VARCHAR2,
111 P_ROLE1_NAME IN VARCHAR2,
112 P_RESOURCE2_NAME IN VARCHAR2,
113 P_GROUP2_NAME IN VARCHAR2,
114 P_ROLE2_NAME IN VARCHAR2,
115 P_RESOURCE3_NAME IN VARCHAR2,
116 P_GROUP3_NAME IN VARCHAR2,
117 P_ROLE3_NAME IN VARCHAR2,
118 P_RESOURCE4_NAME IN VARCHAR2,
119 P_GROUP4_NAME IN VARCHAR2,
120 P_ROLE4_NAME IN VARCHAR2,
121 P_RESOURCE5_NAME IN VARCHAR2,
122 P_GROUP5_NAME IN VARCHAR2,
123 P_ROLE5_NAME IN VARCHAR2,
124 P_RESOURCE6_NAME IN VARCHAR2,
125 P_GROUP6_NAME IN VARCHAR2,
126 P_ROLE6_NAME IN VARCHAR2,
127 P_RESOURCE7_NAME IN VARCHAR2,
128 P_GROUP7_NAME IN VARCHAR2,
129 P_ROLE7_NAME IN VARCHAR2,
130 P_RESOURCE8_NAME IN VARCHAR2,
131 P_GROUP8_NAME IN VARCHAR2,
132 P_ROLE8_NAME IN VARCHAR2,
133 P_RESOURCE9_NAME IN VARCHAR2,
134 P_GROUP9_NAME IN VARCHAR2,
135 P_ROLE9_NAME IN VARCHAR2,
136 P_RESOURCE10_NAME IN VARCHAR2,
137 P_GROUP10_NAME IN VARCHAR2,
138 P_ROLE10_NAME IN VARCHAR2,
139 P_RESOURCE11_NAME IN VARCHAR2,
140 P_GROUP11_NAME IN VARCHAR2,
141 P_ROLE11_NAME IN VARCHAR2,
142 P_RESOURCE12_NAME IN VARCHAR2,
143 P_GROUP12_NAME IN VARCHAR2,
144 P_ROLE12_NAME IN VARCHAR2,
145 P_RESOURCE13_NAME IN VARCHAR2,
146 P_GROUP13_NAME IN VARCHAR2,
147 P_ROLE13_NAME IN VARCHAR2,
148 P_RESOURCE14_NAME IN VARCHAR2,
149 P_GROUP14_NAME IN VARCHAR2,
150 P_ROLE14_NAME IN VARCHAR2,
151 P_RESOURCE15_NAME IN VARCHAR2,
152 P_GROUP15_NAME IN VARCHAR2,
153 P_ROLE15_NAME IN VARCHAR2,
154 P_RESOURCE16_NAME IN VARCHAR2,
155 P_GROUP16_NAME IN VARCHAR2,
156 P_ROLE16_NAME IN VARCHAR2,
157 P_RESOURCE17_NAME IN VARCHAR2,
158 P_GROUP17_NAME IN VARCHAR2,
159 P_ROLE17_NAME IN VARCHAR2,
160 P_RESOURCE18_NAME IN VARCHAR2,
161 P_GROUP18_NAME IN VARCHAR2,
162 P_ROLE18_NAME IN VARCHAR2,
163 P_RESOURCE19_NAME IN VARCHAR2,
164 P_GROUP19_NAME IN VARCHAR2,
165 P_ROLE19_NAME IN VARCHAR2,
166 P_RESOURCE20_NAME IN VARCHAR2,
167 P_GROUP20_NAME IN VARCHAR2,
168 P_ROLE20_NAME IN VARCHAR2,
169 P_RESOURCE21_NAME IN VARCHAR2,
170 P_GROUP21_NAME IN VARCHAR2,
171 P_ROLE21_NAME IN VARCHAR2,
172 P_RESOURCE22_NAME IN VARCHAR2,
173 P_GROUP22_NAME IN VARCHAR2,
174 P_ROLE22_NAME IN VARCHAR2,
175 P_RESOURCE23_NAME IN VARCHAR2,
176 P_GROUP23_NAME IN VARCHAR2,
177 P_ROLE23_NAME IN VARCHAR2,
178 P_RESOURCE24_NAME IN VARCHAR2,
179 P_GROUP24_NAME IN VARCHAR2,
180 P_ROLE24_NAME IN VARCHAR2,
181 P_RESOURCE25_NAME IN VARCHAR2,
182 P_GROUP25_NAME IN VARCHAR2,
183 P_ROLE25_NAME IN VARCHAR2,
184 P_RESOURCE26_NAME IN VARCHAR2,
185 P_GROUP26_NAME IN VARCHAR2,
186 P_ROLE26_NAME IN VARCHAR2,
187 P_RESOURCE27_NAME IN VARCHAR2,
188 P_GROUP27_NAME IN VARCHAR2,
189 P_ROLE27_NAME IN VARCHAR2,
190 P_RESOURCE28_NAME IN VARCHAR2,
191 P_GROUP28_NAME IN VARCHAR2,
192 P_ROLE28_NAME IN VARCHAR2,
193 P_RESOURCE29_NAME IN VARCHAR2,
194 P_GROUP29_NAME IN VARCHAR2,
195 P_ROLE29_NAME IN VARCHAR2,
196 P_RESOURCE30_NAME IN VARCHAR2,
197 P_GROUP30_NAME IN VARCHAR2,
198 P_ROLE30_NAME IN VARCHAR2,
199 P_PARTY_SITE_ID IN VARCHAR2,
200 P_SALES_GROUP IN VARCHAR2,
201 P_SALES_ROLE IN VARCHAR2,
202 P_PHONETIC_NAME IN VARCHAR2,
203 P_IDENTIFYING_ADDRESS IN VARCHAR2,
204 P_RES1_ATT1 IN VARCHAR2,
205 P_RES2_ATT1 IN VARCHAR2,
206 P_RES3_ATT1 IN VARCHAR2,
207 P_RES4_ATT1 IN VARCHAR2,
208 P_RES5_ATT1 IN VARCHAR2,
209 P_RES6_ATT1 IN VARCHAR2,
210 P_RES7_ATT1 IN VARCHAR2,
211 P_RES8_ATT1 IN VARCHAR2,
212 P_RES9_ATT1 IN VARCHAR2,
213 P_RES10_ATT1 IN VARCHAR2,
214 P_RES11_ATT1 IN VARCHAR2,
215 P_RES12_ATT1 IN VARCHAR2,
216 P_RES13_ATT1 IN VARCHAR2,
217 P_RES14_ATT1 IN VARCHAR2,
218 P_RES15_ATT1 IN VARCHAR2,
219 P_RES16_ATT1 IN VARCHAR2,
220 P_RES17_ATT1 IN VARCHAR2,
221 P_RES18_ATT1 IN VARCHAR2,
222 P_RES19_ATT1 IN VARCHAR2,
223 P_RES20_ATT1 IN VARCHAR2,
224 P_RES21_ATT1 IN VARCHAR2,
225 P_RES22_ATT1 IN VARCHAR2,
226 P_RES23_ATT1 IN VARCHAR2,
227 P_RES24_ATT1 IN VARCHAR2,
228 P_RES25_ATT1 IN VARCHAR2,
229 P_RES26_ATT1 IN VARCHAR2,
230 P_RES27_ATT1 IN VARCHAR2,
231 P_RES28_ATT1 IN VARCHAR2,
232 P_RES29_ATT1 IN VARCHAR2,
233 P_RES30_ATT1 IN VARCHAR2,
234 P_RES1_ATT2 IN VARCHAR2,
235 P_RES2_ATT2 IN VARCHAR2,
236 P_RES3_ATT2 IN VARCHAR2,
237 P_RES4_ATT2 IN VARCHAR2,
238 P_RES5_ATT2 IN VARCHAR2,
239 P_RES6_ATT2 IN VARCHAR2,
240 P_RES7_ATT2 IN VARCHAR2,
241 P_RES8_ATT2 IN VARCHAR2,
242 P_RES9_ATT2 IN VARCHAR2,
243 P_RES10_ATT2 IN VARCHAR2,
244 P_RES11_ATT2 IN VARCHAR2,
245 P_RES12_ATT2 IN VARCHAR2,
246 P_RES13_ATT2 IN VARCHAR2,
247 P_RES14_ATT2 IN VARCHAR2,
248 P_RES15_ATT2 IN VARCHAR2,
249 P_RES16_ATT2 IN VARCHAR2,
250 P_RES17_ATT2 IN VARCHAR2,
251 P_RES18_ATT2 IN VARCHAR2,
252 P_RES19_ATT2 IN VARCHAR2,
253 P_RES20_ATT2 IN VARCHAR2,
254 P_RES21_ATT2 IN VARCHAR2,
255 P_RES22_ATT2 IN VARCHAR2,
256 P_RES23_ATT2 IN VARCHAR2,
257 P_RES24_ATT2 IN VARCHAR2,
258 P_RES25_ATT2 IN VARCHAR2,
259 P_RES26_ATT2 IN VARCHAR2,
260 P_RES27_ATT2 IN VARCHAR2,
261 P_RES28_ATT2 IN VARCHAR2,
262 P_RES29_ATT2 IN VARCHAR2,
263 P_RES30_ATT2 IN VARCHAR2,
264 P_RES1_ATT3 IN VARCHAR2,
265 P_RES2_ATT3 IN VARCHAR2,
266 P_RES3_ATT3 IN VARCHAR2,
267 P_RES4_ATT3 IN VARCHAR2,
268 P_RES5_ATT3 IN VARCHAR2,
269 P_RES6_ATT3 IN VARCHAR2,
270 P_RES7_ATT3 IN VARCHAR2,
271 P_RES8_ATT3 IN VARCHAR2,
272 P_RES9_ATT3 IN VARCHAR2,
273 P_RES10_ATT3 IN VARCHAR2,
274 P_RES11_ATT3 IN VARCHAR2,
275 P_RES12_ATT3 IN VARCHAR2,
276 P_RES13_ATT3 IN VARCHAR2,
277 P_RES14_ATT3 IN VARCHAR2,
278 P_RES15_ATT3 IN VARCHAR2,
279 P_RES16_ATT3 IN VARCHAR2,
280 P_RES17_ATT3 IN VARCHAR2,
281 P_RES18_ATT3 IN VARCHAR2,
282 P_RES19_ATT3 IN VARCHAR2,
283 P_RES20_ATT3 IN VARCHAR2,
284 P_RES21_ATT3 IN VARCHAR2,
285 P_RES22_ATT3 IN VARCHAR2,
286 P_RES23_ATT3 IN VARCHAR2,
287 P_RES24_ATT3 IN VARCHAR2,
288 P_RES25_ATT3 IN VARCHAR2,
289 P_RES26_ATT3 IN VARCHAR2,
290 P_RES27_ATT3 IN VARCHAR2,
291 P_RES28_ATT3 IN VARCHAR2,
292 P_RES29_ATT3 IN VARCHAR2,
293 P_RES30_ATT3 IN VARCHAR2,
294 P_RES1_ATT4 IN VARCHAR2,
295 P_RES2_ATT4 IN VARCHAR2,
296 P_RES3_ATT4 IN VARCHAR2,
297 P_RES4_ATT4 IN VARCHAR2,
298 P_RES5_ATT4 IN VARCHAR2,
299 P_RES6_ATT4 IN VARCHAR2,
300 P_RES7_ATT4 IN VARCHAR2,
301 P_RES8_ATT4 IN VARCHAR2,
302 P_RES9_ATT4 IN VARCHAR2,
303 P_RES10_ATT4 IN VARCHAR2,
304 P_RES11_ATT4 IN VARCHAR2,
305 P_RES12_ATT4 IN VARCHAR2,
306 P_RES13_ATT4 IN VARCHAR2,
307 P_RES14_ATT4 IN VARCHAR2,
308 P_RES15_ATT4 IN VARCHAR2,
309 P_RES16_ATT4 IN VARCHAR2,
310 P_RES17_ATT4 IN VARCHAR2,
311 P_RES18_ATT4 IN VARCHAR2,
312 P_RES19_ATT4 IN VARCHAR2,
313 P_RES20_ATT4 IN VARCHAR2,
314 P_RES21_ATT4 IN VARCHAR2,
315 P_RES22_ATT4 IN VARCHAR2,
316 P_RES23_ATT4 IN VARCHAR2,
317 P_RES24_ATT4 IN VARCHAR2,
318 P_RES25_ATT4 IN VARCHAR2,
319 P_RES26_ATT4 IN VARCHAR2,
320 P_RES27_ATT4 IN VARCHAR2,
321 P_RES28_ATT4 IN VARCHAR2,
322 P_RES29_ATT4 IN VARCHAR2,
323 P_RES30_ATT4 IN VARCHAR2,
324 P_RES1_ATT5 IN VARCHAR2,
325 P_RES2_ATT5 IN VARCHAR2,
326 P_RES3_ATT5 IN VARCHAR2,
327 P_RES4_ATT5 IN VARCHAR2,
328 P_RES5_ATT5 IN VARCHAR2,
329 P_RES6_ATT5 IN VARCHAR2,
330 P_RES7_ATT5 IN VARCHAR2,
331 P_RES8_ATT5 IN VARCHAR2,
332 P_RES9_ATT5 IN VARCHAR2,
333 P_RES10_ATT5 IN VARCHAR2,
334 P_RES11_ATT5 IN VARCHAR2,
335 P_RES12_ATT5 IN VARCHAR2,
336 P_RES13_ATT5 IN VARCHAR2,
337 P_RES14_ATT5 IN VARCHAR2,
338 P_RES15_ATT5 IN VARCHAR2,
339 P_RES16_ATT5 IN VARCHAR2,
340 P_RES17_ATT5 IN VARCHAR2,
341 P_RES18_ATT5 IN VARCHAR2,
342 P_RES19_ATT5 IN VARCHAR2,
343 P_RES20_ATT5 IN VARCHAR2,
344 P_RES21_ATT5 IN VARCHAR2,
345 P_RES22_ATT5 IN VARCHAR2,
346 P_RES23_ATT5 IN VARCHAR2,
347 P_RES24_ATT5 IN VARCHAR2,
348 P_RES25_ATT5 IN VARCHAR2,
349 P_RES26_ATT5 IN VARCHAR2,
350 P_RES27_ATT5 IN VARCHAR2,
351 P_RES28_ATT5 IN VARCHAR2,
352 P_RES29_ATT5 IN VARCHAR2,
353 P_RES30_ATT5 IN VARCHAR2,
354 P_RES1_START_DATE IN DATE,
355 P_RES2_START_DATE IN DATE,
356 P_RES3_START_DATE IN DATE,
357 P_RES4_START_DATE IN DATE,
358 P_RES5_START_DATE IN DATE,
359 P_RES6_START_DATE IN DATE,
360 P_RES7_START_DATE IN DATE,
361 P_RES8_START_DATE IN DATE,
362 P_RES9_START_DATE IN DATE,
363 P_RES10_START_DATE IN DATE,
367 P_RES14_START_DATE IN DATE,
364 P_RES11_START_DATE IN DATE,
365 P_RES12_START_DATE IN DATE,
366 P_RES13_START_DATE IN DATE,
368 P_RES15_START_DATE IN DATE,
369 P_RES16_START_DATE IN DATE,
370 P_RES17_START_DATE IN DATE,
371 P_RES18_START_DATE IN DATE,
372 P_RES19_START_DATE IN DATE,
373 P_RES20_START_DATE IN DATE,
374 P_RES21_START_DATE IN DATE,
375 P_RES22_START_DATE IN DATE,
376 P_RES23_START_DATE IN DATE,
377 P_RES24_START_DATE IN DATE,
378 P_RES25_START_DATE IN DATE,
379 P_RES26_START_DATE IN DATE,
380 P_RES27_START_DATE IN DATE,
381 P_RES28_START_DATE IN DATE,
382 P_RES29_START_DATE IN DATE,
383 P_RES30_START_DATE IN DATE,
384 P_RES1_END_DATE IN DATE,
385 P_RES2_END_DATE IN DATE,
386 P_RES3_END_DATE IN DATE,
387 P_RES4_END_DATE IN DATE,
388 P_RES5_END_DATE IN DATE,
389 P_RES6_END_DATE IN DATE,
390 P_RES7_END_DATE IN DATE,
391 P_RES8_END_DATE IN DATE,
392 P_RES9_END_DATE IN DATE,
393 P_RES10_END_DATE IN DATE,
394 P_RES11_END_DATE IN DATE,
395 P_RES12_END_DATE IN DATE,
396 P_RES13_END_DATE IN DATE,
397 P_RES14_END_DATE IN DATE,
398 P_RES15_END_DATE IN DATE,
399 P_RES16_END_DATE IN DATE,
400 P_RES17_END_DATE IN DATE,
401 P_RES18_END_DATE IN DATE,
402 P_RES19_END_DATE IN DATE,
403 P_RES20_END_DATE IN DATE,
407 P_RES24_END_DATE IN DATE,
404 P_RES21_END_DATE IN DATE,
405 P_RES22_END_DATE IN DATE,
406 P_RES23_END_DATE IN DATE,
408 P_RES25_END_DATE IN DATE,
409 P_RES26_END_DATE IN DATE,
410 P_RES27_END_DATE IN DATE,
411 P_RES28_END_DATE IN DATE,
412 P_RES29_END_DATE IN DATE,
413 P_RES30_END_DATE IN DATE,
414 P_ATTRIBUTE1 IN VARCHAR2,
415 P_ATTRIBUTE2 IN VARCHAR2,
416 P_ATTRIBUTE3 IN VARCHAR2,
417 P_ATTRIBUTE4 IN VARCHAR2,
421 P_ATTRIBUTE8 IN VARCHAR2,
418 P_ATTRIBUTE5 IN VARCHAR2,
419 P_ATTRIBUTE6 IN VARCHAR2,
420 P_ATTRIBUTE7 IN VARCHAR2,
422 P_ATTRIBUTE9 IN VARCHAR2,
423 P_ATTRIBUTE10 IN VARCHAR2,
424 P_ATTRIBUTE11 IN VARCHAR2,
425 P_ATTRIBUTE12 IN VARCHAR2,
426 P_ATTRIBUTE13 IN VARCHAR2,
427 P_ATTRIBUTE14 IN VARCHAR2,
428 P_ATTRIBUTE15 IN VARCHAR2,
429 P_START_DATE IN DATE,
430 P_END_DATE IN DATE
431 );
432
433 end JTF_TTY_MAINTAIN_NA_PVT;
[Home] [Help]