1 PACKAGE BODY pn_distributions_pkg
2 -- $Header: PNTDISTB.pls 120.11.12020000.2 2012/08/01 06:49:12 ppenumar ship $
3
4 AS
5
6 -------------------------------------------------------------------------------
7 -- PROCDURE : INSERT_ROW
8 -- INVOKED FROM : insert_row procedure
9 -- PURPOSE : inserts the row
10 -- HISTORY :
11 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced pn_distributions with _ALL table.
12 -- 01-DEC-05 sdmahesh o Modified the legal_entity_id update logic
13 -- 27-dec-05 piagrawa o Bug#4911362 - updated to make sure that l_org_id is not
14 -- null before being inserted into the table.
15 -- 07-MAR-06 Kiran o Bug # 5081563 - changed the query to get line #
16 -------------------------------------------------------------------------------
17 PROCEDURE insert_row (
18 x_rowid IN OUT NOCOPY VARCHAR2
19 ,x_distribution_id IN OUT NOCOPY NUMBER
20 ,x_account_id IN NUMBER
21 ,x_payment_term_id IN NUMBER
22 ,x_term_template_id IN NUMBER
23 ,x_account_class IN VARCHAR2
24 ,x_percentage IN NUMBER
25 ,x_line_number IN OUT NOCOPY NUMBER
26 ,x_last_update_date IN DATE
27 ,x_last_updated_by IN NUMBER
28 ,x_creation_date IN DATE
29 ,x_created_by IN NUMBER
30 ,x_last_update_login IN NUMBER
31 ,x_attribute_category IN VARCHAR2
32 ,x_attribute1 IN VARCHAR2
33 ,x_attribute2 IN VARCHAR2
34 ,x_attribute3 IN VARCHAR2
35 ,x_attribute4 IN VARCHAR2
36 ,x_attribute5 IN VARCHAR2
37 ,x_attribute6 IN VARCHAR2
38 ,x_attribute7 IN VARCHAR2
39 ,x_attribute8 IN VARCHAR2
40 ,x_attribute9 IN VARCHAR2
41 ,x_attribute10 IN VARCHAR2
42 ,x_attribute11 IN VARCHAR2
43 ,x_attribute12 IN VARCHAR2
44 ,x_attribute13 IN VARCHAR2
45 ,x_attribute14 IN VARCHAR2
46 ,x_attribute15 IN VARCHAR2
47 ,x_org_id IN NUMBER
48 )
49 IS
50 l_return_status VARCHAR2 (30) := NULL;
51 l_rowid VARCHAR2 (18) := NULL;
52 l_org_id pn_distributions_all.org_id%TYPE ;
53 l_vendor_site_id NUMBER;
54 l_term_le_ID NUMBER;
55
56 l_count NUMBER;
57
58 CURSOR csr_vendor_info IS
59 SELECT term.vendor_site_id AS vendor_site_id,
60 term.legal_entity_id AS legal_entity_id,
61 term.org_id AS org_id
62 FROM pn_payment_terms_all term
63 WHERE term.payment_term_id = x_payment_term_id;
64
65 l_legal_entity_id pn_payment_terms.legal_entity_id%TYPE;
66
67 CURSOR term_org (p_term_ID IN NUMBER) IS
68 SELECT org_ID
69 FROM pn_payment_terms_all
70 WHERE payment_term_id = p_term_ID;
71
72 CURSOR template_org (p_template_ID IN NUMBER) IS
73 SELECT org_ID
74 FROM pn_term_templates_all
75 WHERE term_template_id = p_template_ID;
76
77 BEGIN
78
79 -- PNP_DEBUG_PKG.debug (' PN_DISTRIBUTIONS_PKG.insert_row (+)');
80
81 /* handle the le stamping */
82 FOR rec IN csr_vendor_info LOOP
83 l_org_id := rec.org_id;
84 l_vendor_site_id := rec.vendor_site_id;
85 l_term_le_ID := rec.legal_entity_id;
86 END LOOP;
87
88 SELECT count(*) INTO l_count
89 FROM pn_distributions_all pd
90 WHERE pd.payment_term_id = x_payment_term_id;
91
92 IF pn_r12_util_pkg.is_r12 AND (l_count < 1)THEN
93 l_legal_entity_id :=
94 pn_r12_util_pkg.get_le_for_ap(
95 p_code_combination_id => x_account_id
96 ,p_location_id => l_vendor_site_id
97 ,p_org_id => l_org_id
98 );
99 IF (nvl(l_term_le_ID,-99) <> l_legal_entity_id) THEN --Bug13997924
100 UPDATE pn_payment_terms_all
101 SET legal_entity_id = l_legal_entity_id,
102 last_update_date = x_last_update_date,
103 last_updated_by = x_last_updated_by,
104 last_update_login = x_last_update_login
105 WHERE payment_term_id = x_payment_term_id;
106 END IF;
107 END IF;
108
109 IF (x_distribution_id IS NULL)
110 THEN
111 SELECT pn_distributions_s.NEXTVAL
112 INTO x_distribution_id
113 FROM DUAL;
114 END IF;
115
116 /* make sure the org ID is not null */
117 IF x_org_id IS NOT NULL THEN
118 l_org_id := x_org_id;
119 ELSE
120 IF x_payment_term_id IS NOT NULL THEN
121 FOR rec IN term_org(x_payment_term_id) LOOP
122 l_org_id := rec.org_id;
123 END LOOP;
124
125 ELSIF x_term_template_id IS NOT NULL THEN
126 FOR rec IN template_org(x_term_template_id) LOOP
127 l_org_id := rec.org_id;
128 END LOOP;
129
130 END IF;
131
132 END IF;
133
134 /* get the line # */
135 IF x_line_number IS NULL THEN
136
137 IF x_payment_term_id IS NOT NULL THEN
138
139 SELECT NVL(MAX(line_number),0) + 1
140 INTO x_line_number
141 FROM pn_distributions_all
142 WHERE payment_term_id = x_payment_term_id;
143
144 ELSIF x_term_template_id IS NOT NULL THEN
145
146 SELECT NVL(MAX(line_number),0) + 1
147 INTO x_line_number
148 FROM pn_distributions_all
149 WHERE term_template_id = x_term_template_id;
150
151 END IF;
152
153 END IF;
154
155 INSERT INTO pn_distributions_all
156 (distribution_id
157 ,account_id
158 ,payment_term_id
159 ,term_template_id
160 ,account_class
161 ,percentage
162 ,line_number
163 ,last_update_date
164 ,last_updated_by
165 ,creation_date
166 ,created_by
167 ,last_update_login
168 ,attribute_category
169 ,attribute1
170 ,attribute2
171 ,attribute3
172 ,attribute4
173 ,attribute5
174 ,attribute6
175 ,attribute7
176 ,attribute8
177 ,attribute9
178 ,attribute10
179 ,attribute11
180 ,attribute12
181 ,attribute13
182 ,attribute14
183 ,attribute15
184 ,org_id
185 )
186 VALUES (x_distribution_id
187 ,x_account_id
188 ,x_payment_term_id
189 ,x_term_template_id
190 ,x_account_class
191 ,x_percentage
192 ,x_line_number
193 ,x_last_update_date
194 ,x_last_updated_by
195 ,x_creation_date
196 ,x_created_by
197 ,x_last_update_login
198 ,x_attribute_category
199 ,x_attribute1
200 ,x_attribute2
201 ,x_attribute3
202 ,x_attribute4
203 ,x_attribute5
204 ,x_attribute6
205 ,x_attribute7
206 ,x_attribute8
207 ,x_attribute9
208 ,x_attribute10
209 ,x_attribute11
210 ,x_attribute12
211 ,x_attribute13
212 ,x_attribute14
213 ,x_attribute15
214 ,l_org_id
215 );
216
217
218 IF (SQL%NOTFOUND) THEN
219
220 RAISE NO_DATA_FOUND;
221
222 END IF;
223
224
225 --PNP_DEBUG_PKG.debug (' PN_DISTRIBUTIONS_PKG.insert_row (-)');
226 END insert_row;
227
228 -------------------------------------------------------------------------------
229 -- PROCDURE : update_row
230 -- INVOKED FROM : update_row procedure
231 -- PURPOSE : updates the row
232 -- HISTORY :
233 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced pn_distributions with _ALL table
234 -- and changed the where condition.
235 -- 01-DEC-05 sdmahesh o Modified the legal_entity_id update logic
236 -------------------------------------------------------------------------------
237 PROCEDURE update_row (
238 x_rowid IN VARCHAR2
239 ,x_distribution_id IN NUMBER
240 ,x_account_id IN NUMBER
241 ,x_payment_term_id IN NUMBER
242 ,x_term_template_id IN NUMBER
243 ,x_account_class IN VARCHAR2
244 ,x_percentage IN NUMBER
245 ,x_line_number IN NUMBER
246 ,x_last_update_date IN DATE
247 ,x_last_updated_by IN NUMBER
248 ,x_last_update_login IN NUMBER
249 ,x_attribute_category IN VARCHAR2
250 ,x_attribute1 IN VARCHAR2
251 ,x_attribute2 IN VARCHAR2
252 ,x_attribute3 IN VARCHAR2
253 ,x_attribute4 IN VARCHAR2
254 ,x_attribute5 IN VARCHAR2
255 ,x_attribute6 IN VARCHAR2
256 ,x_attribute7 IN VARCHAR2
257 ,x_attribute8 IN VARCHAR2
258 ,x_attribute9 IN VARCHAR2
259 ,x_attribute10 IN VARCHAR2
260 ,x_attribute11 IN VARCHAR2
261 ,x_attribute12 IN VARCHAR2
262 ,x_attribute13 IN VARCHAR2
263 ,x_attribute14 IN VARCHAR2
264 ,x_attribute15 IN VARCHAR2
265 ,x_lease_change_id IN NUMBER
266 )
267 IS
268
269 CURSOR csr_vendor_info IS
270 SELECT term.vendor_site_id AS vendor_site_id,
271 term.legal_entity_id AS legal_entity_id,
272 term.org_id AS org_id
273 FROM pn_payment_terms_all term
274 WHERE term.payment_term_id = x_payment_term_id;
275
276 l_legal_entity_id pn_payment_terms.legal_entity_id%TYPE;
277 l_return_status VARCHAR2 (30) := NULL;
278 l_count NUMBER;
279 BEGIN
280
281 --PNP_DEBUG_PKG.debug (' PN_DISTRIBUTIONS_PKG.update_row (+)');
282
283 SELECT count(*) INTO l_count
284 FROM pn_distributions_all dist,
285 pn_payment_terms_all term
286 WHERE dist.payment_term_id = term.payment_term_id
287 AND term.payment_term_id = x_payment_term_id
288 AND dist.distribution_id <> x_distribution_id;
289
290 IF pn_r12_util_pkg.is_r12 AND (l_count < 1)THEN
291 FOR csr_vendor_info_rec IN csr_vendor_info LOOP
292 l_legal_entity_id :=
293 pn_r12_util_pkg.get_le_for_ap(
294 p_code_combination_id => x_account_id
295 ,p_location_id => csr_vendor_info_rec.vendor_site_id
296 ,p_org_id => csr_vendor_info_rec.org_id
297 );
298 IF (nvl(csr_vendor_info_rec.legal_entity_id,-99) <> l_legal_entity_id) THEN --Bug 13997924
299 UPDATE pn_payment_terms_all
300 SET legal_entity_id = l_legal_entity_id,
301 last_update_date = x_last_update_date,
302 last_updated_by = x_last_updated_by,
303 last_update_login = x_last_update_login
304 WHERE payment_term_id = x_payment_term_id;
305 END IF;
306 END LOOP;
307 END IF;
308
309 UPDATE pn_distributions_all
310 SET account_id = x_account_id
311 ,payment_term_id = x_payment_term_id
312 ,term_template_id = x_term_template_id
313 ,account_class = x_account_class
314 ,percentage = x_percentage
315 ,line_number = x_line_number
316 ,last_update_date = x_last_update_date
317 ,last_updated_by = x_last_updated_by
318 ,last_update_login = x_last_update_login
319 ,attribute_category = x_attribute_category
320 ,attribute1 = x_attribute1
321 ,attribute2 = x_attribute2
322 ,attribute3 = x_attribute3
323 ,attribute4 = x_attribute4
324 ,attribute5 = x_attribute5
325 ,attribute6 = x_attribute6
326 ,attribute7 = x_attribute7
327 ,attribute8 = x_attribute8
328 ,attribute9 = x_attribute9
329 ,attribute10 = x_attribute10
330 ,attribute11 = x_attribute11
331 ,attribute12 = x_attribute12
332 ,attribute13 = x_attribute13
333 ,attribute14 = x_attribute14
334 ,attribute15 = x_attribute15
335 WHERE distribution_id = x_distribution_id;
336
337 IF (SQL%NOTFOUND)
338 THEN
339 RAISE NO_DATA_FOUND;
340 END IF;
341
342 IF x_lease_change_id IS NOT NULL THEN
343 UPDATE pn_payment_terms_all
344 SET lease_change_id = x_lease_change_id
345 WHERE payment_term_id = x_payment_term_id;
346 END IF;
347
348 --PNP_DEBUG_PKG.debug (' PN_DISTRIBUTIONS_PKG.update_row (-)');
349 END update_row;
350
351
352 -------------------------------------------------------------------------------
353 -- PROCDURE : lock_row
354 -- INVOKED FROM : lock_row procedure
355 -- PURPOSE : locks the row
356 -- HISTORY :
357 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced pn_distributions with _ALL table.
358 -------------------------------------------------------------------------------
359 PROCEDURE lock_row (
360 x_rowid IN VARCHAR2
361 ,x_distribution_id IN NUMBER
362 ,x_account_id IN NUMBER
363 ,x_payment_term_id IN NUMBER
364 ,x_term_template_id IN NUMBER
365 ,x_account_class IN VARCHAR2
366 ,x_percentage IN NUMBER
367 ,x_line_number IN NUMBER
368 ,x_attribute_category IN VARCHAR2
369 ,x_attribute1 IN VARCHAR2
370 ,x_attribute2 IN VARCHAR2
371 ,x_attribute3 IN VARCHAR2
372 ,x_attribute4 IN VARCHAR2
373 ,x_attribute5 IN VARCHAR2
374 ,x_attribute6 IN VARCHAR2
375 ,x_attribute7 IN VARCHAR2
376 ,x_attribute8 IN VARCHAR2
377 ,x_attribute9 IN VARCHAR2
378 ,x_attribute10 IN VARCHAR2
379 ,x_attribute11 IN VARCHAR2
380 ,x_attribute12 IN VARCHAR2
381 ,x_attribute13 IN VARCHAR2
382 ,x_attribute14 IN VARCHAR2
383 ,x_attribute15 IN VARCHAR2
384 )
385 IS
386 CURSOR c1
387 IS
388 SELECT *
389 FROM pn_distributions_all
390 WHERE distribution_id = x_distribution_id
391 FOR UPDATE OF distribution_id NOWAIT;
392
393 tlinfo c1%ROWTYPE;
394 BEGIN
395
396 --PNP_DEBUG_PKG.debug (' PN_DISTRIBUTIONS_PKG.lock_row (+)');
397 OPEN c1;
398 FETCH c1 INTO tlinfo;
399
400 IF (c1%NOTFOUND)
401 THEN
402 CLOSE c1;
403 RETURN;
404 END IF;
405
406 CLOSE c1;
407
408 IF NOT (tlinfo.distribution_id = x_distribution_id) THEN
409 pn_var_rent_pkg.lock_row_exception('DISTRIBUTION_ID',tlinfo.distribution_id);
410 END IF;
411 IF NOT (tlinfo.account_id = x_account_id) THEN
412 pn_var_rent_pkg.lock_row_exception('ACCOUNT_ID',tlinfo.account_id);
413 END IF;
414 IF NOT ( (tlinfo.payment_term_id = x_payment_term_id)
415 OR ((tlinfo.payment_term_id IS NULL) AND x_payment_term_id IS NULL)
416 ) THEN
417 pn_var_rent_pkg.lock_row_exception('PAYMENT_TERM_ID',tlinfo.payment_term_id);
418 END IF;
419 IF NOT ( (tlinfo.term_template_id = x_term_template_id)
420 OR ((tlinfo.term_template_id IS NULL) AND x_term_template_id IS NULL)
421 ) THEN
422 pn_var_rent_pkg.lock_row_exception('TERM_TEMPLATE_ID',tlinfo.term_template_id);
423 END IF;
424 IF NOT ( (tlinfo.percentage = x_percentage)
425 OR ((tlinfo.percentage IS NULL) AND x_percentage IS NULL)
426 ) THEN
427 pn_var_rent_pkg.lock_row_exception('PERCENTAGE',tlinfo.percentage);
428 END IF;
429 IF NOT ( (tlinfo.line_number = x_line_number)
430 OR ((tlinfo.line_number IS NULL) AND x_line_number IS NULL)
431 ) THEN
432 pn_var_rent_pkg.lock_row_exception('LINE_NUMBER',tlinfo.line_number);
433 END IF;
434
435 --PNP_DEBUG_PKG.debug (' PN_DISTRIBUTIONS_PKG.lock_row (-)');
436 END lock_row;
437
438
439 -------------------------------------------------------------------------------
440 -- PROCDURE : delete_row
441 -- INVOKED FROM : delete_row procedure
442 -- PURPOSE : deletes the row
443 -- HISTORY :
444 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced pn_distributions with _ALL table.
445 -------------------------------------------------------------------------------
446 PROCEDURE delete_row (x_rowid IN VARCHAR2)
447 IS
448
449 l_term_id pn_payment_terms.payment_term_id%TYPE;
450 l_legal_entity_id pn_payment_terms.legal_entity_id%TYPE;
451
452 CURSOR get_term_data(p_term_id pn_payment_terms.payment_term_id%TYPE) IS
453 SELECT term.vendor_site_id,
454 term.legal_entity_id,
455 term.org_id,
456 dist.account_id
457 FROM pn_payment_terms_all term,
458 pn_distributions_all dist
459 WHERE term.payment_term_id = dist.payment_term_id (+)
460 AND term.payment_term_id = p_term_id
461 AND rownum < 2;
462
463 BEGIN
464
465 --PNP_DEBUG_PKG.debug (' PN_DISTRIBUTIONS_PKG.delete_row (+)');
466 DELETE FROM pn_distributions_all
467 WHERE ROWID = x_rowid
468 RETURNING payment_term_id INTO l_term_id;
469
470 IF (SQL%NOTFOUND)
471 THEN
472 RAISE NO_DATA_FOUND;
473 END IF;
474
475 IF pn_r12_util_pkg.is_r12 THEN
476 FOR chk_dist_rec IN get_term_data (l_term_id) LOOP
477 IF chk_dist_rec.account_id IS NULL AND
478 chk_dist_rec.vendor_site_id IS NOT NULL THEN
479
480 l_legal_entity_id :=
481 pn_r12_util_pkg.get_le_for_ap(
482 p_code_combination_id => null
483 ,p_location_id => chk_dist_rec.vendor_site_id
484 ,p_org_id => chk_dist_rec.org_id
485 );
486
487 IF NOT ((chk_dist_rec.legal_entity_id = l_legal_entity_id) OR
488 (chk_dist_rec.legal_entity_id IS NULL AND l_legal_entity_id IS NULL)) THEN
489 UPDATE pn_payment_terms
490 SET legal_entity_id = l_legal_entity_id,
491 last_update_date = SYSDATE,
492 last_updated_by = fnd_global.user_id,
493 last_update_login = fnd_global.login_id
494 WHERE payment_term_id = l_term_id;
495 END IF;
496 END IF;
497 END LOOP;
498 END IF;
499
500 --PNP_DEBUG_PKG.debug (' PN_DISTRIBUTIONS_PKG.delete_row (-)');
501 END delete_row;
502
503 -------------------------------------------------------------------------------
504 -- PROCDURE : delete_row
505 -- INVOKED FROM : delete_row procedure
506 -- PURPOSE : deletes the row
507 -- NOTE : overloaded this procedure to take PK as In parameter
508 -- HISTORY :
509 -- 04-JUL-05 piagrawa o Bug 4284035 - Created
510 -------------------------------------------------------------------------------
511 PROCEDURE delete_row (x_distribution_id IN NUMBER)
512 IS
513
514 l_term_id pn_payment_terms.payment_term_id%TYPE;
515 l_legal_entity_id pn_payment_terms.legal_entity_id%TYPE;
516
517 CURSOR get_term_data(p_term_id pn_payment_terms.payment_term_id%TYPE) IS
518 SELECT term.vendor_site_id,
519 term.legal_entity_id,
520 term.org_id,
521 dist.account_id
522 FROM pn_payment_terms_all term,
523 pn_distributions_all dist
524 WHERE term.payment_term_id = dist.payment_term_id (+)
525 AND term.payment_term_id = p_term_id
526 AND rownum < 2;
527
528 BEGIN
529
530 --PNP_DEBUG_PKG.debug (' PN_DISTRIBUTIONS_PKG.delete_row (+)');
531 DELETE FROM pn_distributions_all
532 WHERE distribution_id = x_distribution_id
533 RETURNING payment_term_id INTO l_term_id;
534
535 IF (SQL%NOTFOUND)
536 THEN
537 RAISE NO_DATA_FOUND;
538 END IF;
539
540 IF pn_r12_util_pkg.is_r12 THEN
541 FOR chk_dist_rec IN get_term_data (l_term_id) LOOP
542 IF chk_dist_rec.account_id IS NULL AND
543 chk_dist_rec.vendor_site_id IS NOT NULL THEN
544
545 l_legal_entity_id :=
546 pn_r12_util_pkg.get_le_for_ap(
547 p_code_combination_id => null
548 ,p_location_id => chk_dist_rec.vendor_site_id
549 ,p_org_id => chk_dist_rec.org_id
550 );
551
552 IF NOT ((chk_dist_rec.legal_entity_id = l_legal_entity_id) OR
553 (chk_dist_rec.legal_entity_id IS NULL AND l_legal_entity_id IS NULL)) THEN
554 UPDATE pn_payment_terms
555 SET legal_entity_id = l_legal_entity_id,
556 last_update_date = SYSDATE,
557 last_updated_by = fnd_global.user_id,
558 last_update_login = fnd_global.login_id
559 WHERE payment_term_id = l_term_id;
560 END IF;
561 END IF;
562 END LOOP;
563 END IF;
564
565 --PNP_DEBUG_PKG.debug (' PN_DISTRIBUTIONS_PKG.delete_row (-)');
566 END delete_row;
567
568 END pn_distributions_pkg;
[Home] [Help]