1 PACKAGE BODY PO_ORGS_SV as
2 /* $Header: POXCOO2B.pls 120.1.12010000.3 2008/09/24 07:12:02 cvardia ship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_RVCTP_ENABLE_TRACE'),'N');
6 /*===========================================================================
7
8 PROCEDURE NAME : get_org_info()
9
10 ===========================================================================*/
11
12 PROCEDURE get_org_info(X_org_id IN NUMBER, X_set_of_books_id IN NUMBER,
13 X_org_code IN OUT NOCOPY varchar2,
14 X_org_name IN OUT NOCOPY varchar2 ) IS
15
16
17 X_progress varchar2(3) := '';
18
19
20
21 begin
22
23 X_progress := '010';
24
25 /* Get the Org Code and name for a specific Org ID and Set Of Books
26 ** Please Note that this select DOES NOT fetch the ORG_ID back
27 ** as it is already a part of the where clause. R10 code always
28 ** selects it into a bind variable. This means that the procedure
29 ** that calls this api should take care of that logic if necessary */
30
31 select ood.organization_code,
32 ood.organization_name
33 into X_org_code,
34 X_org_name
35 from org_organization_definitions ood
36 where ood.organization_id(+) = X_org_id;
37
38 exception
39
40 when no_data_found then
41 X_org_code := '';
42 X_org_name := '';
43 when others then
44 po_message_s.sql_error('get_org_info',X_progress,sqlcode);
45 raise;
46
47
48 end get_org_info;
49
50
51
55
52 /*===========================================================================
53
54 PROCEDURE NAME : val_dest_org()
56 ===========================================================================*/
57
58 FUNCTION val_dest_org( X_org_id IN NUMBER,
59 X_item_id IN NUMBER,
60 X_item_rev IN VARCHAR2,
61 X_dest_type IN VARCHAR2,
62 X_sob_id IN NUMBER)
63 RETURN BOOLEAN IS
64
65 X_progress varchar2(3) := '';
66 x_org_count NUMBER := 0;
67
68 BEGIN
69
70 /*
71 ** Stop processing if org is null.
72 */
73
74 IF (x_org_id is null) THEN
75 return (FALSE);
76
77 END IF;
78
79
80 /*
81 ** Validate that the org is currently
82 ** active in the current set of books.
83 */
84
85 x_progress := '010';
86
87 SELECT count(1)
88 INTO x_org_count
89 FROM org_organization_definitions ood
90 WHERE ood.set_of_books_id = x_sob_id
91 AND ood.organization_id = x_org_id
92 AND nvl(ood.disable_date, trunc(sysdate + 1)) > trunc(sysdate);
93
94
95 IF (x_org_count = 0) THEN
96 return (FALSE);
97
98 END IF;
99
100 x_org_count := 0;
101
102 /* Validation for destination type 'INVENTORY */
103
104 -- BUG#7395502
105 -- Modified the SQL's for Better Performance
106 -- SQL ID : 28306002 , 28305956 ,28305980
107
108 IF (x_dest_type = 'INVENTORY') THEN
109
110 x_progress := '020';
111
112 IF ( x_item_id is not null ) THEN
113 SELECT count(1)
114 INTO x_org_count
115 FROM mtl_system_items msi
116 WHERE msi.inventory_item_id = x_item_id
117 AND msi.stock_enabled_flag = 'Y'
118 AND msi.purchasing_enabled_flag = 'Y'
119 AND msi.organization_id = x_org_id;
120
121 ELSE
122 SELECT count(1)
123 INTO x_org_count
124 FROM mtl_system_items msi
125 WHERE msi.stock_enabled_flag = 'Y'
126 AND msi.purchasing_enabled_flag = 'Y'
127 AND msi.organization_id = x_org_id
128 AND ROWNUM < 2;
129 END IF;
130
131 IF (x_org_count = 0) THEN
132 return (FALSE);
133
134 END IF;
135
136 /* Validation for destination type 'EXPENSE' */
137
138 ELSIF (x_dest_type = 'EXPENSE') THEN
139
140 x_progress := '030';
141
142 IF ( x_item_id is not null ) THEN
143 SELECT count(1)
144 INTO x_org_count
145 FROM mtl_system_items msi
146 WHERE msi.inventory_item_id = x_item_id
147 AND msi.organization_id = x_org_id;
148 ELSE
149 SELECT count(1)
150 INTO x_org_count
151 FROM mtl_system_items msi
152 WHERE msi.organization_id = x_org_id
153 AND ROWNUM < 2;
154 END IF;
155
156 IF (x_org_count = 0) THEN
157 return (FALSE);
158
159 END IF;
160
161 /* Validation for destination type 'SHOP FLOOR' */
162
163 ELSIF (x_dest_type = 'SHOP FLOOR') THEN
164
165 x_progress := '040';
166
167 IF ( x_item_id is not null ) THEN
168 SELECT count(1)
169 INTO x_org_count
170 FROM mtl_system_items msi
171 WHERE msi.inventory_item_id = x_item_id
172 AND msi.organization_id = x_org_id
173 AND msi.outside_operation_flag = 'Y';
174 ELSE
175 SELECT count(1)
176 INTO x_org_count
177 FROM mtl_system_items msi
178 WHERE msi.organization_id = x_org_id
179 AND msi.outside_operation_flag = 'Y'
180 AND ROWNUM < 2;
181
182 END IF;
183 IF (x_org_count = 0) THEN
184 return (FALSE);
185
186 END IF;
187
188 END IF;
189
190 /*
191 ** Validate that the item revision
192 ** is valid for the org.
193 */
194
195 x_org_count := 0;
196 x_progress := '050';
197
198 SELECT count(1)
199 INTO x_org_count
200 FROM mtl_item_revisions mir
201 WHERE mir.revision = nvl(x_item_rev, mir.revision)
202 AND mir.inventory_item_id = x_item_id
203 AND mir.organization_id = x_org_id;
204
205 IF (x_org_count = 0) THEN
206 return (FALSE);
207
208 END IF;
209
210 return (TRUE);
211
212 exception
213 when others then
214 po_message_s.sql_error('val_dest_org',X_progress,sqlcode);
215 raise;
216
217 end val_dest_org;
218
219
220
221 /*===========================================================================
222
223 PROCEDURE NAME : val_source_org()
224
225 ===========================================================================*/
226
227 FUNCTION val_source_org(X_src_org_id IN NUMBER,
228 X_dest_org_id IN NUMBER,
229 X_dest_type IN VARCHAR2,
230 X_item_id IN VARCHAR2,
231 X_mrp_planned_item IN VARCHAR2,
232 X_sob_id IN NUMBER)
233 RETURN BOOLEAN IS
234
235 X_progress varchar2(3) := '';
236 x_org_count NUMBER := 0;
240
237 x_intransit_type mtl_interorg_parameters.intransit_type%type;
238
239 BEGIN
241 /*
242 ** Stop processing if src org is null.
243 */
244
245 IF (x_src_org_id is null) THEN
246 return (FALSE);
247
248 END IF;
249
250 /*
251 ** Stop processing if destination org
252 ** is null.
253 */
254
255 IF (x_dest_org_id is null) THEN
256 return (FALSE);
257
258 END IF;
259
260 /*
261 ** Validate that the org is currently
262 ** active in the current set of books.
263 */
264
265 x_progress := '010';
266
267 SELECT count(1)
268 INTO x_org_count
269 FROM org_organization_definitions ood
270 WHERE ood.set_of_books_id = x_sob_id
271 AND ood.organization_id = x_src_org_id
272 AND nvl(ood.disable_date, trunc(sysdate + 1)) > trunc(sysdate);
273
274
275 IF (x_org_count = 0) THEN
276 return (FALSE);
277
278 END IF;
279
280
281 /*
282 ** Validate that if the item is a planned
283 ** item then the source and destination orgs
284 ** cannot be the same. Display the message
285 ** PO_RQ_INV_SOURCE_SAME_AS_DEST when this is
286 ** is the case.
287 */
288
289 IF (x_mrp_planned_item = 'Y') THEN
290 IF (x_src_org_id = x_dest_org_id) THEN
291 po_message_s.app_error('PO_RQ_INV_SOURCE_SAME_AS_DEST');
292
293 return (FALSE);
294
295 END IF;
296 END IF;
297
298
299 /* Validate that the item is stock enabled
300 ** and internal order enabled in the source
301 ** organization.
302 */
303
304 x_progress := '020';
305
306 SELECT count(1)
307 INTO x_org_count
308 FROM mtl_system_items msi
309 WHERE msi.organization_id = x_src_org_id
310 AND msi.inventory_item_id = x_item_id
311 AND msi.stock_enabled_flag = 'Y'
312 AND msi.internal_order_enabled_flag = 'Y';
313
314 IF (x_org_count = 0) THEN
315 return (FALSE);
316
317 END IF;
318
319
320 /*
321 ** Validate that there is a row in
322 ** mtl_interorg_parameters for the source
323 ** and destination organization combination.
324 */
325
326 x_org_count := 0;
327 x_progress := '030';
328
329 SELECT mip.intransit_type
330 INTO x_intransit_type
331 FROM mtl_interorg_parameters mip
332 WHERE mip.from_organization_id = x_src_org_id
333 AND mip.to_organization_id = x_dest_org_id;
334
335
336
337 IF ((x_intransit_type = 1) AND
338
339 (x_dest_type <> 'EXPENSE')) THEN
340
341 /* Bug# 4446916, We need to allow for Source having serial control as
342 * 'At Sales Order Issue' to destination having serial control as
343 * 'At Receipt or Predefine'. Removed the 6( 'At Sales Order Issue') in code
344 * OR (ms1.serial_number_control_code IN (1,6)
345 */
346
347 SELECT count(1)
348 INTO x_org_count
349 FROM mtl_system_items ms1,
350 mtl_system_items ms2
351 WHERE ms1.inventory_item_id = x_item_id
352 AND ms1.organization_id = x_src_org_id
353 AND ms2.inventory_item_id = x_item_id
354 AND ms2.organization_id = x_dest_org_id
355 AND ((ms1.lot_control_code = 1 AND
356 ms2.lot_control_code = 2)
357 OR (ms1.serial_number_control_code IN (1)
358 AND ms2.serial_number_control_code IN (2,3,5))
359 OR (ms1.revision_qty_control_code = 1
360 AND ms2.revision_qty_control_code = 2));
361
362 IF (x_org_count = 1) THEN
363 po_message_s.app_error('PO_RQ_INV_LOOSER_TIGHTER');
364
365 return (FALSE);
366
367 END IF;
368 END IF;
369
370 return (TRUE);
371
372 exception
373 when no_data_found then
374 return (FALSE);
375 when others then
376 po_message_s.sql_error('val_source_org',X_progress,sqlcode);
377 raise;
378
379 end val_source_org;
380
381
382 -- ksareddy - performance fix 2506961 - rewrote derive_org_info to not use dynamic sql
383 /*===========================================================================
384
385 PROCEDURE NAME : derive_org_info()
386
387 ===========================================================================*/
388 PROCEDURE derive_org_info (
389 p_org_record IN OUT NOCOPY rcv_shipment_object_sv.Organization_id_record_type) IS
390
391 cid INTEGER;
392 x_org_code p_org_record.organization_code%TYPE;
393
394 /* Fix for Bug 2813343.
395 Replaced p_org_record.organization_code%TYPE
396 with p_org_record.organization_id%TYPE for
397 the variable x_org_id.
398 */
399 x_org_id p_org_record.organization_id%TYPE;
400
401 BEGIN
402
403 IF p_org_record.organization_code IS NULL and
404 p_org_record.organization_id IS NULL THEN
405
406 p_org_record.error_record.error_status := 'W';
407 RETURN;
408
409 END IF;
410
411 IF p_org_record.organization_code IS NOT NULL and
412 p_org_record.organization_id IS NOT NULL THEN
413
414 p_org_record.error_record.error_status := 'S';
415 RETURN;
416 END IF;
420 FROM mtl_parameters
417 IF p_org_record.organization_id IS NOT NULL and
418 p_org_record.organization_code IS NOT NULL THEN
419 SELECT organization_code, organization_id INTO x_org_code, x_org_id
421 WHERE organization_code = p_org_record.organization_code
422 AND organization_id = p_org_record.organization_id;
423 p_org_record.error_record.error_status := 'S';
424 p_org_record.organization_id := x_org_id;
425 p_org_record.organization_code := x_org_code;
426 RETURN;
427 END IF;
428
429 IF p_org_record.organization_code IS NOT NULL THEN
430 SELECT organization_code, organization_id INTO x_org_code, x_org_id
431 FROM mtl_parameters
432 WHERE organization_code = p_org_record.organization_code;
433 p_org_record.error_record.error_status := 'S';
434 p_org_record.organization_id := x_org_id;
435 p_org_record.organization_code := x_org_code;
436 RETURN;
437 END IF;
438
439 IF p_org_record.organization_id IS NOT NULL THEN
440 SELECT organization_code, organization_id INTO x_org_code, x_org_id
441 FROM mtl_parameters
442 WHERE organization_id = p_org_record.organization_id;
443 p_org_record.error_record.error_status := 'S';
444 p_org_record.organization_id := x_org_id;
445 p_org_record.organization_code := x_org_code;
446 RETURN;
447 END IF;
448
449 EXCEPTION
450 WHEN no_data_found THEN
451 p_org_record.error_record.error_status := 'W';
452 WHEN others THEN
453 p_org_record.error_record.error_status := 'U';
454 p_org_record.error_record.error_message := sqlerrm;
455 IF (g_asn_debug = 'Y') THEN
456 asn_debug.put_line(p_org_record.error_record.error_message);
457 END IF;
458
459 END derive_org_info;
460
461 /*===========================================================================
462
463 PROCEDURE NAME : validate_org_info()
464
465 ===========================================================================*/
466
467 PROCEDURE validate_org_info (
468 p_org_record IN OUT NOCOPY rcv_shipment_object_sv.Organization_id_record_type) IS
469
470 /* bao */
471 x_organization_id NUMBER;
472 x_organization_code VARCHAR2(3);
473 x_sysdate DATE := sysdate;
474 x_is_cached BOOLEAN := FALSE;
475
476 /*
477 X_cid INTEGER;
478 X_rows_processed INTEGER;
479 X_sql_str VARCHAR2(2000);
480
481 X_org_code_null BOOLEAN := TRUE;
482 X_org_id_null BOOLEAN := TRUE;
483
484 X_user_definition_enable_date DATE;
485 X_disable_date DATE;
486 X_sysdate DATE := sysdate;
487 */
488 BEGIN
489
490 /* bao */
491 IF (p_org_record.organization_code IS NULL AND
492 p_org_record.organization_id IS NULL) THEN
493 dbms_output.put_line('All Blanks');
494 p_org_record.error_record.error_message := 'All blanks';
495 p_org_record.error_record.error_status := 'E';
496 RETURN;
497 END IF;
498
499 x_organization_code := p_org_record.organization_code;
500
501 IF (p_org_record.organization_id IS NULL) THEN
502 SELECT organization_id
503 INTO x_organization_id
504 FROM mtl_parameters
505 WHERE organization_code = p_org_record.organization_code;
506 ELSE
507 x_organization_id := p_org_record.organization_id;
508 END IF;
509
510 IF (x_date_table.EXISTS(x_organization_id) AND
511 x_date_table(x_organization_id).v_organization_code =
512 NVL(x_organization_code, x_date_table(x_organization_id).v_organization_code)) THEN
513 x_is_cached := TRUE;
514 END IF;
515
516 IF (NOT x_is_cached) THEN
517 IF (x_organization_code IS NULL) THEN
518 SELECT user_definition_enable_date,
519 disable_date,
520 organization_code
521 INTO x_date_table(x_organization_id).v_enable_date,
522 x_date_table(x_organization_id).v_disable_date,
523 x_date_table(x_organization_id).v_organization_code
524 FROM org_organization_definitions
525 WHERE organization_id = x_organization_id;
526 ELSE
527 SELECT user_definition_enable_date,
528 disable_date,
529 organization_code
530 INTO x_date_table(x_organization_id).v_enable_date,
531 x_date_table(x_organization_id).v_disable_date,
532 x_date_table(x_organization_id).v_organization_code
533 FROM org_organization_definitions
534 WHERE organization_id = x_organization_id AND
535 organization_code = x_organization_code;
536 END IF;
537 END IF;
538
539 IF NOT x_sysdate BETWEEN
540 NVL(x_date_table(x_organization_id).v_enable_date, X_sysdate-1) AND
541 NVL(x_date_table(x_organization_id).v_disable_date, X_sysdate+1) THEN
542
543 dbms_output.put_line('Not Active');
544 p_org_record.error_record.error_status := 'E';
545 p_org_record.error_record.error_message := 'ORG_DISABLED';
546 END IF;
547
548 EXCEPTION
549 WHEN NO_DATA_FOUND THEN
550 dbms_output.put_line('Invalid Organization Code');
551 p_org_record.error_record.error_status := 'E';
552 p_org_record.error_record.error_message := 'ORG_ID';
553 WHEN TOO_MANY_ROWS THEN
554 dbms_output.put_line('Too many rows');
555 p_org_record.error_record.error_status := 'E';
556 p_org_record.error_record.error_message := 'TOOMANYROWS';
560 IF (g_asn_debug = 'Y') THEN
557 WHEN OTHERS THEN
558 p_org_record.error_record.error_status := 'U';
559 p_org_record.error_record.error_message := sqlerrm;
561 asn_debug.put_line(p_org_record.error_record.error_message);
562 END IF;
563
564
565
566
567
568 /*
569 X_sql_str := 'SELECT user_definition_enable_date, disable_date FROM org_organization_definitions WHERE ';
570
571 IF p_org_record.organization_code IS NULL and
572 p_org_record.organization_id IS NULL THEN
573
574 dbms_output.put_line('All Blanks');
575 p_org_record.error_record.error_message := 'All blanks';
576 p_org_record.error_record.error_status := 'E';
577 RETURN;
578
579 END IF;
580
581 IF p_org_record.organization_code IS NOT NULL THEN
582
583 X_sql_str := X_sql_str || ' organization_code = :v_org_code and';
584 X_org_code_null := FALSE;
585
586 END IF;
587
588 IF p_org_record.organization_id IS NOT NULL THEN
589
590 X_sql_str := X_sql_str || ' organization_id = :v_org_id and';
591 X_org_id_null := FALSE;
592
593 END IF;
594
595 X_sql_str := substr(X_sql_str,1,length(X_sql_str)-3);
596
597 dbms_output.put_line(substr(X_sql_str,1,255));
598 dbms_output.put_line(substr(X_sql_str,256,255));
599 dbms_output.put_line(substr(X_sql_str,513,255));
600
601 X_cid := dbms_sql.open_cursor;
602
603 dbms_sql.parse(X_cid, X_sql_str , dbms_sql.native);
604
605 dbms_sql.define_column(X_cid,1,X_user_definition_enable_date);
606 dbms_sql.define_column(X_cid,2,X_disable_date);
607
608 IF NOT X_org_code_null THEN
609
610 dbms_sql.bind_variable(X_cid,'v_org_code',p_org_record.organization_code);
611
612 END IF;
613
614 IF NOT X_org_id_null THEN
615
616 dbms_sql.bind_variable(X_cid,'v_org_id',p_org_record.organization_id);
617
618 END IF;
619
620 X_rows_processed := dbms_sql.execute_and_fetch(X_cid);
621
622 IF X_rows_processed = 1 THEN
623
624 dbms_sql.column_value(X_cid,1,X_user_definition_enable_date);
625 dbms_sql.column_value(X_cid,2,X_disable_date);
626 */
627 /* Check whether organization is Active */
628 /*
629 IF NOT X_sysdate BETWEEN
630 nvl(X_user_definition_enable_date,X_sysdate - 1) and
631 nvl(X_disable_date,X_sysdate + 1) THEN
632
633 dbms_output.put_line('Not Active');
634 p_org_record.error_record.error_status := 'E';
635 p_org_record.error_record.error_message := 'ORG_DISABLED';
636
637 IF dbms_sql.is_open(X_cid) THEN
638 dbms_sql.close_cursor(X_cid);
639 END IF;
640
641 RETURN;
642
643 END IF;
644
645 p_org_record.error_record.error_status := 'S';
646 p_org_record.error_record.error_message := NULL;
647
648
649 ELSIF X_rows_processed = 0 THEN
650
651 dbms_output.put_line('Invalid Organization Code');
652 p_org_record.error_record.error_status := 'E';
653 p_org_record.error_record.error_message := 'ORG_ID';
654
655 IF dbms_sql.is_open(X_cid) THEN
656 dbms_sql.close_cursor(X_cid);
657 END IF;
658
659 RETURN;
660
661 ELSE
662
663 dbms_output.put_line('Too many rows');
664 p_org_record.error_record.error_status := 'E';
665 p_org_record.error_record.error_message := 'TOOMANYROWS';
666
667 IF dbms_sql.is_open(X_cid) THEN
668 dbms_sql.close_cursor(X_cid);
669 END IF;
670
671 RETURN;
672
673 END IF;
674
675 IF dbms_sql.is_open(X_cid) THEN
676 dbms_sql.close_cursor(X_cid);
677 END IF;
678
679
680 EXCEPTION
681 WHEN others THEN
682 IF dbms_sql.is_open(X_cid) THEN
683 dbms_sql.close_cursor(X_cid);
684 END IF;
685 p_org_record.error_record.error_status := 'U';
686 p_org_record.error_record.error_message := sqlerrm;
687 IF (g_asn_debug = 'Y') THEN
688 asn_debug.put_line(p_org_record.error_record.error_message);
689 END IF;
690 */
691 END validate_org_info;
692
693 END PO_ORGS_SV;
[Home] [Help]