[Home] [Help]
PACKAGE BODY: APPS.PA_SALESREPS
Source
1 PACKAGE BODY pa_salesreps AS
2 /* $Header: PAXITSCB.pls 120.7 2008/04/09 11:32:36 rdegala ship $ */
3 -----------------------------
4 -- PROCEDURE IMPLEMENTATIONS
5 --
6
7
8 PROCEDURE process_project(pj_id number,
9 req_id number) IS
10 ----------------------------
11 -- LOCAL CURSOR DECLARATION
12 --
13
14
15 CURSOR GetCredits IS
16 SELECT c.credit_type_code,
17 c.salesrep_id,
18 c.credit_percentage,
19 p.segment1 proj_num,
20 pt.cc_prvdr_flag ic_flag,
21 (select 'Y' from dual where exists (select 1 from pa_project_customers pc
22 where pc.project_id = p.project_id
23 and pc.bill_another_project_flag='Y')) ip_flag /* Added for bug 6603869*/
24 FROM pa_credit_receivers c,
25 pa_projects p,
26 so_sales_credit_types sc,
27 pa_project_types pt
28 WHERE c.project_id = p.project_id
29 AND c.project_id = pj_id
30 AND c.transfer_to_ar_flag = 'Y'
31 AND c.credit_type_code = sc.name
32 AND sc.enabled_flag = 'Y'
33 AND p.project_type = pt.project_type
34 ORDER BY c.credit_type_code, c.credit_percentage;
35
36 credit_rec GetCredits%ROWTYPE;
37
38 /* Added for Bug 2627331 starts here */
39
40 CURSOR GetDefaultCredits IS
41 SELECT a.salesrep_id,
42 c.segment1 proj_num,
43 I.sales_credit_type_code credit_type,
44 pt.cc_prvdr_flag ic_flag
45 FROM ra_salesreps a,
46 pa_project_players b,
47 pa_projects c,
48 pa_implementations I,
49 pa_project_types pt
50 WHERE c.project_id = b.project_id
51 AND b.project_id = pj_id
52 AND b.project_role_type = 'PROJECT MANAGER'
53 AND b.person_id = a.person_id
54 AND trunc(SYSDATE) BETWEEN b.Start_Date_Active (+)
55 AND NVL(b.End_Date_Active (+), trunc(SYSDATE))
56 AND c.project_type = pt.project_type;
57
58 credit_def_rec GetDefaultCredits%ROWTYPE;
59 /* Added for Bug 2627331 ends here */
60
61 CURSOR GetILine_Normal(project_number VARCHAR2) IS
62 SELECT interface_line_context,
63 interface_line_attribute1,
64 interface_line_attribute2,
65 interface_line_attribute3,
66 interface_line_attribute4,
67 interface_line_attribute5,
68 interface_line_attribute6,
69 interface_line_attribute7,
70 r.allow_sales_credit_flag
71 FROM
72 ra_interface_lines i,
73 pa_draft_invoices inv,
74 pa_implementations p,
75 ra_batch_sources r
76 WHERE
77 inv.project_id = pj_id
78 -- AND inv.draft_invoice_num = to_number(interface_line_attribute2) commented for bug 5330841
79 AND to_char(inv.draft_invoice_num) = trim(interface_line_attribute2) /* Added for bug 5330841 */
80 AND inv.request_id = req_id
81 AND i.interface_line_attribute1 = project_number
82 AND i.batch_source_name = r.name
83 AND r.batch_source_id = p.invoice_batch_source_id
84 AND i.interface_status IS NULL
85 ;
86
87 CURSOR GetILine_IC(project_number VARCHAR2) IS
88 SELECT interface_line_context,
89 interface_line_attribute1,
90 interface_line_attribute2,
91 interface_line_attribute3,
92 interface_line_attribute4,
93 interface_line_attribute5,
94 interface_line_attribute6,
95 interface_line_attribute7,
96 r.allow_sales_credit_flag
97 FROM
98 ra_interface_lines i,
99 pa_draft_invoices inv,
100 pa_implementations p,
101 ra_batch_sources r
102 WHERE
103 inv.project_id = pj_id
104 -- AND inv.draft_invoice_num = to_number(interface_line_attribute2)commented for bug 5330841
105 AND to_char(inv.draft_invoice_num) = trim(interface_line_attribute2) /* Added for bug 5330841 */
106 AND inv.request_id = req_id
107 AND i.interface_line_attribute1 = project_number
108 AND i.batch_source_name = r.name
109 AND r.batch_source_id = nvl(p.cc_ic_ar_batch_source_id,0)
110 AND i.interface_status IS NULL
111 ;
112
113 int_rec GetILine_Normal%ROWTYPE;
114 transfer CHAR(1);
115 v_cnt NUMBER :=0 ;
116 BEGIN
117 FOR credit_rec IN GetCredits LOOP
118 v_cnt := 1;
119 IF (credit_rec.ic_flag = 'N' and NVL(credit_rec.ip_flag,'N') = 'N' ) THEN /* Modified for bug 6603869*/
120 FOR int_rec IN GetILine_Normal(credit_rec.proj_num) LOOP
121 IF (int_rec.allow_sales_credit_flag = 'Y') THEN
122 INSERT INTO ra_interface_salescredits
123 (interface_line_context,
124 interface_line_attribute1,
125 interface_line_attribute2,
126 interface_line_attribute3,
127 interface_line_attribute4,
128 interface_line_attribute5,
129 interface_line_attribute6,
130 interface_line_attribute7,
131 salesrep_id,
132 sales_credit_type_name,
133 sales_credit_percent_split,
134 org_id)
135 VALUES (int_rec.interface_line_context,
136 int_rec.interface_line_attribute1,
137 int_rec.interface_line_attribute2,
138 int_rec.interface_line_attribute3,
139 int_rec.interface_line_attribute4,
140 int_rec.interface_line_attribute5,
141 int_rec.interface_line_attribute6,
142 int_rec.interface_line_attribute7,
143 credit_rec.salesrep_id,
144 credit_rec.credit_type_code,
145 credit_rec.credit_percentage,
146 PA_MOAC_UTILS.GET_CURRENT_ORG_ID);
147 commit;
148 END IF;
149 END LOOP;
150 ELSE
151 FOR int_rec IN GetILine_IC(credit_rec.proj_num) LOOP
152 IF (int_rec.allow_sales_credit_flag = 'Y') THEN
153 INSERT INTO ra_interface_salescredits
154 (interface_line_context,
155 interface_line_attribute1,
156 interface_line_attribute2,
157 interface_line_attribute3,
158 interface_line_attribute4,
159 interface_line_attribute5,
160 interface_line_attribute6,
161 interface_line_attribute7,
162 salesrep_id,
163 sales_credit_type_name,
164 sales_credit_percent_split,
165 org_id)
166 VALUES (int_rec.interface_line_context,
167 int_rec.interface_line_attribute1,
168 int_rec.interface_line_attribute2,
169 int_rec.interface_line_attribute3,
170 int_rec.interface_line_attribute4,
171 int_rec.interface_line_attribute5,
172 int_rec.interface_line_attribute6,
173 int_rec.interface_line_attribute7,
174 credit_rec.salesrep_id,
175 credit_rec.credit_type_code,
176 credit_rec.credit_percentage,
177 PA_MOAC_UTILS.GET_CURRENT_ORG_ID);
178 commit;
179 END IF;
180 END LOOP;
181 /* Added for bug 6958686 */
182 IF (NVL(credit_rec.ip_flag,'N') = 'Y' ) THEN
183 FOR int_rec IN GetILine_Normal(credit_rec.proj_num) LOOP
184 IF (int_rec.allow_sales_credit_flag = 'Y') THEN
185 INSERT INTO ra_interface_salescredits
186 (interface_line_context,
187 interface_line_attribute1,
188 interface_line_attribute2,
189 interface_line_attribute3,
190 interface_line_attribute4,
191 interface_line_attribute5,
192 interface_line_attribute6,
193 interface_line_attribute7,
194 salesrep_id,
195 sales_credit_type_name,
196 sales_credit_percent_split,
197 org_id)
198 VALUES (int_rec.interface_line_context,
199 int_rec.interface_line_attribute1,
200 int_rec.interface_line_attribute2,
201 int_rec.interface_line_attribute3,
202 int_rec.interface_line_attribute4,
203 int_rec.interface_line_attribute5,
204 int_rec.interface_line_attribute6,
205 int_rec.interface_line_attribute7,
206 credit_rec.salesrep_id,
207 credit_rec.credit_type_code,
208 credit_rec.credit_percentage,
209 PA_MOAC_UTILS.GET_CURRENT_ORG_ID);
210 commit;
211 END IF;
212 END LOOP;
213 END IF;
214 /* End of fix for bug 6958686 */
215 END IF;
216 END LOOP;
217
218 /* Added for Bug 2627331 starts here */
219 If v_cnt = 0 THEN
220 FOR credit_def_rec IN GetDefaultCredits LOOP
221 IF (credit_def_rec.ic_flag = 'N' and NVL(credit_rec.ip_flag,'N') = 'N') THEN /* Modified for bug 6603869*/
222 FOR int_rec IN GetILine_Normal(credit_def_rec.proj_num) LOOP
223 IF (int_rec.allow_sales_credit_flag = 'Y') THEN
224 INSERT INTO ra_interface_salescredits
225 (interface_line_context,
226 interface_line_attribute1,
227 interface_line_attribute2,
228 interface_line_attribute3,
229 interface_line_attribute4,
230 interface_line_attribute5,
231 interface_line_attribute6,
232 interface_line_attribute7,
233 salesrep_id,
234 sales_credit_type_name,
235 sales_credit_percent_split,
236 org_id)
237 VALUES (int_rec.interface_line_context,
238 int_rec.interface_line_attribute1,
239 int_rec.interface_line_attribute2,
240 int_rec.interface_line_attribute3,
241 int_rec.interface_line_attribute4,
242 int_rec.interface_line_attribute5,
243 int_rec.interface_line_attribute6,
244 int_rec.interface_line_attribute7,
245 credit_def_rec.salesrep_id,
246 credit_def_rec.credit_type,
247 100,
248 PA_MOAC_UTILS.GET_CURRENT_ORG_ID);
249 commit;
250 END IF;
251 END LOOP;
252 ELSE
253 FOR int_rec IN GetILine_IC(credit_def_rec.proj_num) LOOP
254 IF (int_rec.allow_sales_credit_flag = 'Y') THEN
255 INSERT INTO ra_interface_salescredits
256 (interface_line_context,
257 interface_line_attribute1,
258 interface_line_attribute2,
259 interface_line_attribute3,
260 interface_line_attribute4,
261 interface_line_attribute5,
262 interface_line_attribute6,
263 interface_line_attribute7,
264 salesrep_id,
265 sales_credit_type_name,
266 sales_credit_percent_split,
267 org_id)
268 VALUES (int_rec.interface_line_context,
269 int_rec.interface_line_attribute1,
270 int_rec.interface_line_attribute2,
271 int_rec.interface_line_attribute3,
272 int_rec.interface_line_attribute4,
273 int_rec.interface_line_attribute5,
274 int_rec.interface_line_attribute6,
275 int_rec.interface_line_attribute7,
276 credit_def_rec.salesrep_id,
277 credit_def_rec.credit_type,
278 100,
279 PA_MOAC_UTILS.GET_CURRENT_ORG_ID);
280 commit;
281 END IF;
282 END LOOP;
283 /* Added for bug 6958686 */
284 IF (NVL(credit_rec.ip_flag,'N') = 'Y' ) THEN
285 FOR int_rec IN GetILine_Normal(credit_rec.proj_num) LOOP
286 IF (int_rec.allow_sales_credit_flag = 'Y') THEN
287 INSERT INTO ra_interface_salescredits
288 (interface_line_context,
289 interface_line_attribute1,
290 interface_line_attribute2,
291 interface_line_attribute3,
292 interface_line_attribute4,
293 interface_line_attribute5,
294 interface_line_attribute6,
295 interface_line_attribute7,
296 salesrep_id,
297 sales_credit_type_name,
298 sales_credit_percent_split,
299 org_id)
300 VALUES (int_rec.interface_line_context,
301 int_rec.interface_line_attribute1,
302 int_rec.interface_line_attribute2,
303 int_rec.interface_line_attribute3,
304 int_rec.interface_line_attribute4,
305 int_rec.interface_line_attribute5,
306 int_rec.interface_line_attribute6,
307 int_rec.interface_line_attribute7,
308 credit_rec.salesrep_id,
309 credit_rec.credit_type_code,
310 credit_rec.credit_percentage,
311 PA_MOAC_UTILS.GET_CURRENT_ORG_ID);
312 commit;
313 END IF;
314 END LOOP;
315 END IF;
316 /* End of fix for bug 6958686 */
317 END IF;
318 END LOOP;
319 END IF;
320 /* Added for Bug 2627331 ends here */
321 END process_project;
322
323 -- Procedure to validate Sales credit type code
324
325 PROCEDURE validate_sales_credit_type ( pj_id IN number,
326 rej_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
327 IS
328 Cursor get_transfer_status
329 is
330 SELECT r.allow_sales_credit_flag
331 FROM ra_batch_sources r,
332 pa_implementations i
333 WHERE i.invoice_batch_source_id = r.batch_source_id;
334
335 l_transfer varchar2(1);
336 l_dummy varchar2(1);
337 l_step number;
338 l_ord_mgmt_installed_flag VARCHAR2(1);/*Added for credit reciever change*/
339
340 BEGIN
341
342 --Check whether Sales credit info is to be passed to AR or Not
343
344 open get_transfer_status;
345
346 fetch get_transfer_status into l_transfer;
347
348 close get_transfer_status;
349
350 /*Added for credit reciever change */
351 l_ord_mgmt_installed_flag :=PA_INSTALL.is_ord_mgmt_installed();
352
353 -- If sales credit info is to be passed to AR
354 IF l_ord_mgmt_installed_flag = 'Y' THEN
355
356 If l_transfer = 'Y'
357 Then
358
359 /*Added for credit reciever change */
360 l_step :=0;
361
362 select 'x'
363 into l_dummy
364 from dual
365 where exists ( select 1
366 from pa_implementations i,
367 so_sales_credit_types sc
368 where i.sales_credit_type_code = sc.name
369 and sc.enabled_flag = 'Y'
370 UNION ALL
371 select 1
372 from pa_credit_receivers
373 WHERE project_id = pj_id
374 AND transfer_to_ar_flag = 'Y'
375 AND ROWNUM = 1 );
376
377
378 l_step := 10;
379
380 SELECT 'x'
381 INTO l_dummy
382 FROM pa_credit_receivers
383 WHERE project_id = pj_id
384 AND transfer_to_ar_flag = 'Y'
385 AND ROWNUM = 1 ;
386
387 l_step := 20;
388
389 SELECT 'x'
390 INTO l_dummy
391 FROM sys.dual
392 WHERE exists ( select 'x'
393 from pa_credit_receivers c,
394 so_sales_credit_types sc
395 where c.project_id = pj_id
396 and c.credit_type_code = sc.name
397 and c.transfer_to_ar_flag = 'Y'
398 and sc.enabled_flag = 'Y' );
399
400 /* Commented for Bug 5376080
401 l_step := 30 ;
402 select 'x'
403 into l_dummy
404 from dual
405 where exists ( select 1
406 from pa_Credit_receivers
407 where project_id = pj_id
408 AND transfer_to_Ar_flag = 'Y'
409 AND sysdate between start_date_Active and end_date_Active
410 ); */
411
412 rej_code := NULL ;
413
414
415 End If;/*End of l_transfer='Y'*/
416
417 ELSE /*Order management is not installed */
418
419 If l_transfer = 'Y'
420 Then
421
422 l_step :=0;
423
424 select 'x'
425 into l_dummy
426 from dual
427 where exists ( select 1
428 from pa_implementations i,
429 pa_lookups pa
430 where pa.lookup_type='CREDIT TYPE'
431 and i.sales_credit_type_code = pa.lookup_code
432 and pa.enabled_flag = 'Y'
433 UNION ALL
434 select 1
435 from pa_credit_receivers
436 WHERE project_id = pj_id
437 AND transfer_to_ar_flag = 'Y'
438 AND ROWNUM = 1 );
439
440 l_step := 10;
441
442 SELECT 'x'
443 INTO l_dummy
444 FROM pa_credit_receivers
445 WHERE project_id = pj_id
446 AND transfer_to_ar_flag = 'Y'
447 AND ROWNUM = 1 ;
448
449 l_step := 20;
450
451 SELECT 'x'
452 INTO l_dummy
453 FROM sys.dual
454 WHERE exists ( select 'x'
455 from pa_credit_receivers c,
456 pa_lookups pa
457 where c.project_id = pj_id
458 and pa.lookup_type='CREDIT TYPE'
459 and c.credit_type_code = pa.lookup_code
460 and c.transfer_to_ar_flag = 'Y'
461 and pa.enabled_flag = 'Y' );
462
463 /* Commented for Bug 5376080
464 l_step := 30 ;
465
466 select 'x'
467 into l_dummy
468 from dual
469 where exists ( select 1
470 from pa_Credit_receivers
471 where project_id = pj_id
472 AND transfer_to_Ar_flag = 'Y'
473 AND sysdate between start_date_active and end_date_Active
474 ); */
475
476 rej_code := NULL ;
477
478
479 End If;
480
481 END IF;/*Order management installed*/
482
483
484 EXCEPTION
485
486 When NO_DATA_FOUND
487 Then
488 if l_step =0
489 Then
490 rej_code :='PA_INV_SALES_CREDIT';
491 elsif l_step = 20
492 Then
493 rej_code := 'PA_DISAB_CRD_TYP';
494
495 /* Commented for Bug 5376080
496 Elsif l_step =30
497 then
498 rej_code := 'PA_CREDIT_RECEIVER_END_DATED'; */
499 Else
500 rej_code := NULL;
501 End if;
502
503 END validate_sales_credit_type;
504
505
506 END pa_salesreps;