[Home] [Help]
PACKAGE BODY: APPS.OKC_CA_UTIL_PVT
Source
1 package body okc_ca_util_pvt as
2 /* $Header: OKCCAUTLB.pls 120.7 2006/04/26 15:53 muteshev noship $ */
3
4 function bsa_subtype( p_order_number in number)
5 return oe_transaction_types_tl.name%type
6 as
7 result oe_transaction_types_tl.name%type;
8 begin
9 select t.name into result
10 from
11 oe_blanket_headers_all h,
12 oe_transaction_types_all a,
13 oe_transaction_types_tl t
14 where t.transaction_type_id = h.order_type_id
15 and a.transaction_type_id = t.transaction_type_id
16 and t.language = USERENV('LANG')
17 and a.sales_document_type_code = 'B'
18 and h.order_number = p_order_number;
19 return result;
20 exception when others then return null;
21 end;
22
23 function so_subtype( p_order_number in number)
24 return oe_transaction_types_tl.name%type
25 as
26 result oe_transaction_types_tl.name%type;
27 begin
28 select t.name into result
29 from
30 oe_order_headers_all h,
31 oe_transaction_types_all a,
32 oe_transaction_types_tl t
33 where t.transaction_type_id = h.order_type_id
34 and a.transaction_type_id = t.transaction_type_id
35 and t.language = USERENV('LANG')
36 and a.sales_document_type_code = 'O'
37 and h.order_number = p_order_number;
38 return result;
39 exception when others then return null;
40 end;
41
42 function article_title( p_article_version_id in number)
43 return okc_articles_all.article_title%type
44 as
45 result okc_articles_all.article_title%type;
46 begin
47 select article_title into result
48 from okc_articles_all a, okc_article_versions v
49 where a.article_id = v.article_id
50 and article_version_id = p_article_version_id;
51 return result;
52 exception when others then return null;
53 end;
54
55 function display_name( p_article_version_id in number)
56 return okc_articles_all.article_title%type
57 as
58 result okc_articles_all.article_title%type;
59 begin
60 select nvl(display_name, article_title) into result
61 from okc_articles_all a, okc_article_versions v
62 where a.article_id = v.article_id
63 and article_version_id = p_article_version_id;
64 return result;
65 exception when others then return null;
66 end;
67
68 function article_version_number( p_article_version_id in number)
69 return okc_article_versions.article_version_number%type
70 as
71 result okc_article_versions.article_version_number%type;
72 begin
73 select article_version_number into result
74 from okc_articles_all a, okc_article_versions v
75 where a.article_id = v.article_id
76 and article_version_id = p_article_version_id;
77 return result;
78 exception when others then return null;
79 end;
80
81 function article_id( p_article_version_id in number)
82 return okc_article_versions.article_id%type
83 as
84 result okc_article_versions.article_id%type;
85 begin
86 select article_id into result
87 from okc_article_versions
88 where article_version_id = p_article_version_id;
89 return result;
90 exception when others then return null;
91 end;
92
93 function org_id( p_article_version_id in number)
94 return okc_articles_all.org_id%type
95 as
96 result okc_articles_all.org_id%type;
97 begin
98 select org_id into result
99 from okc_articles_all a, okc_article_versions v
100 where a.article_id = v.article_id
101 and article_version_id = p_article_version_id;
102 return result;
103 exception when others then return null;
104 end;
105
106 function latest_article_version_id( p_article_version_id in number,
107 p_org_id in number)
108 return okc_article_versions.article_version_id%type
109 as
110 result okc_article_versions.article_version_id%type;
111 l_article_id okc_articles_all.article_id%type;
112 l_org_id okc_articles_all.org_id%type;
113 begin
114 begin
115 select a.article_id, org_id into l_article_id, l_org_id
116 from okc_articles_all a, okc_article_versions v
117 where a.article_id = v.article_id
118 and article_version_id = p_article_version_id;
119 exception when others then return null;
120 end;
121 if p_org_id = l_org_id then
122 begin
123 select article_version_id into result
124 from okc_article_versions
125 where article_id = l_article_id
126 and article_status in ('APPROVED','ON_HOLD')
127 and (start_date, article_version_number) = (
128 select
129 max(start_date),
130 max(article_version_number)
131 from
132 okc_article_versions
133 where article_id = l_article_id
134 and article_status in ('APPROVED','ON_HOLD')
135 );
136 exception when others then return null;
137 end;
138 else
139 begin
140 select article_version_id into result
141 from okc_article_versions
142 where article_id = l_article_id
143 and global_yn = 'Y'
144 and article_status in ('APPROVED','ON_HOLD')
145 and (start_date, article_version_number) = (
146 select
147 max(start_date),
148 max(article_version_number)
149 from
150 okc_article_versions
151 where article_id = l_article_id
152 and article_status in ('APPROVED','ON_HOLD')
153 and exists (
154 select 1 from okc_article_adoptions
155 where global_article_version_id = article_version_id
156 and local_org_id = p_org_id
157 and adoption_type = 'ADOPTED'
158 and adoption_status = 'APPROVED'
159 )
160 );
161 exception when others then return null;
162 end;
163 end if;
164 return result;
165 exception when others then return null;
166 end;
167
168 function doc_type( p_code in varchar2)
169 return okc_bus_doc_types_tl.name%type
170 as
171 result okc_bus_doc_types_tl.name%type;
172 begin
173 select name into result
174 from okc_bus_doc_types_tl
175 where document_type = p_code
176 and language = userenv('LANG');
177 return result;
178 exception when others then return null;
179 end;
180
181 function neg_supplier( p_auction_header_id in number)
182 return hz_parties.party_name%type
183 as
184 result hz_parties.party_name%type;
185 begin
186 select party_name into result
187 from hz_parties p,
188 pon_bid_headers b
189 where trading_partner_id = party_id
190 and bid_status IN ('ACTIVE','DISQUALIFIED')
191 and auction_header_id = p_auction_header_id;
192 return result;
193 exception when TOO_MANY_ROWS then
194 return fnd_message.get_string('OKC','OKC_REP_MULTIPLE_PARTIES');
195 end;
196
197 procedure remove_results( p_search_id in number) as
198 begin
199 delete okc_ca_documents_gt
200 where srch_id = p_search_id;
201 exception when others then null;
202 end;
203
204 procedure remove_included( p_search_id in number) as
205 begin
206 delete okc_ca_art_versions_gt
207 where article_srch_id = p_search_id
208 and article_flag = 'S';
209 exception when others then null;
210 end;
211
212 procedure include_alternates( p_search_id in number,
213 p_alternates in varchar2
214 ) as
215 begin
216 if p_alternates = 'Y' THEN
217 merge into okc_ca_art_versions_gt gt
218 using (
219 select
220 article_srch_id,
221 art.article_id,
222 article_version_id,
223 art.org_id,
224 article_title,
225 article_type,
226 article_version_number,
227 decode(global_yn, 'Y', 'GLOBAL', nvl(v.adoption_type, 'LOCAL')) adoption_type,
228 org_name
229 from
230 okc_articles_all art,
231 okc_article_versions v,
232 ( select
233 orgu.organization_id org_id,
234 orgu.name org_name
235 from
236 hr_organization_information orgi,
237 hr_operating_units orgu
238 where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
239 and orgi.organization_id = orgu.organization_id
240 ) org,
241 ( select
242 target_article_id,
243 org_id,
244 article_srch_id
245 from
246 okc_article_relatns_all,
247 okc_ca_art_versions_gt
248 where source_article_id = article_id
249 and org_id = article_org_id
250 and article_srch_id = p_search_id
251 and article_flag = 'U'
252 ) r
253 where art.article_id = v.article_id
254 and art.article_id = r.target_article_id
255 and art.org_id = r.org_id
256 and article_status in ('APPROVED','ON_HOLD')
257 and (start_date, article_version_number) = (
258 select
259 max(start_date),
260 max(article_version_number)
261 from
262 okc_article_versions
263 where article_id = art.article_id
264 and article_status in ('APPROVED','ON_HOLD')
265 )
266 and org.org_id = art.org_id
267 union all
268 select
269 article_srch_id,
270 v.article_id,
271 v.article_version_id,
272 a.local_org_id,
273 article_title,
274 article_type,
275 article_version_number,
276 'ADOPTED' adoption_type,
277 org_name
278 from
279 okc_articles_all art,
280 okc_article_adoptions a,
281 okc_article_versions v,
282 ( select
283 orgu.organization_id org_id,
284 orgu.name org_name
285 from
286 hr_organization_information orgi,
287 hr_operating_units orgu
288 where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
289 and orgi.organization_id = orgu.organization_id
290 ) org,
291 ( select
292 target_article_id,
293 org_id,
294 article_srch_id
295 from
296 okc_article_relatns_all,
297 okc_ca_art_versions_gt
298 where source_article_id = article_id
299 and org_id = article_org_id
300 and article_srch_id = p_search_id
301 and article_flag = 'U'
302 ) r
303 where art.article_id = v.article_id
304 and a.global_article_version_id = v.article_version_id
305 and v.article_id = r.target_article_id
306 and a.local_org_id = r.org_id
307 and article_status in ('APPROVED','ON_HOLD')
308 and a.adoption_type = 'ADOPTED'
309 and adoption_status = 'APPROVED'
310 and global_yn = 'Y'
311 and (start_date, article_version_number) = (
312 select
313 max(start_date),
314 max(article_version_number)
315 from
316 okc_article_versions v1,
317 okc_article_adoptions a1
318 where global_yn = 'Y'
319 and v1.article_version_id = a1.global_article_version_id
320 and v1.article_status in ('APPROVED','ON_HOLD')
321 and a1.adoption_type = 'ADOPTED'
322 and a1.adoption_status = 'APPROVED'
323 and a1.local_org_id = r.org_id
324 and v1.article_id = v.article_id)
325 and org.org_id = r.org_id
326 ) alt
327 ON (
328 gt.article_srch_id = alt.article_srch_id
329 and gt.article_id = alt.article_id
330 and gt.article_version_id = alt.article_version_id
331 and gt.article_org_id = alt.org_id
332 )
333 when matched then
334 update set gt.object_version_number = gt.object_version_number+1
335 when not matched then
336 insert (
337 gt.article_srch_id,
338 gt.article_id,
339 gt.article_version_id,
340 gt.article_org_id,
341 gt.article_flag,
342 gt.article_title,
343 gt.article_type,
344 gt.article_standard_yn,
345 gt.article_version_number,
346 gt.article_adoption_type,
347 gt.article_org_name,
348 gt.std_article_title,
349 gt.std_article_version_number,
350 gt.object_version_number)
351 values (
352 alt.article_srch_id,
353 alt.article_id,
354 alt.article_version_id,
355 alt.org_id,
356 'S',
357 alt.article_title,
358 alt.article_type,
359 'Y',
360 alt.article_version_number,
361 alt.adoption_type,
362 alt.org_name,
363 null,null,1);
364 end if;
365 exception when others then null;
366 end;
367
368 procedure include_adoptions( p_search_id in number,
369 p_adoptions in varchar2
370 ) as
371 begin
372 if p_adoptions = 'Y' THEN
373 merge into okc_ca_art_versions_gt gt
374 using (
375 select
376 article_srch_id,
377 article_id,
378 article_version_id,
379 a.local_org_id,
380 article_title,
381 article_type,
382 article_version_number,
383 'ADOPTED' adoption_type,
384 org_name
385 from
386 okc_article_adoptions a,
387 okc_ca_art_versions_gt t,
388 ( select
389 orgu.organization_id org_id,
390 orgu.name org_name
391 from
392 hr_organization_information orgi,
393 hr_operating_units orgu
394 where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
395 and orgi.organization_id = orgu.organization_id
396 ) org
397 where article_srch_id = p_search_id
398 -- and article_flag = 'U'
399 and global_article_version_id = article_version_id
400 and article_adoption_type = 'GLOBAL'
401 and a.adoption_type = 'ADOPTED'
402 and a.adoption_status = 'APPROVED'
403 and article_standard_yn = 'Y'
404 and org.org_id = a.local_org_id
405 ) ado
406 on (
407 gt.article_srch_id = ado.article_srch_id
408 and gt.article_id = ado.article_id
409 and gt.article_version_id = ado.article_version_id
410 and gt.article_org_id = ado.local_org_id
411 )
412 when matched then
413 update set gt.object_version_number = gt.object_version_number+1
414 when not matched then
415 insert (
416 gt.article_srch_id,
417 gt.article_id,
418 gt.article_version_id,
419 gt.article_org_id,
420 gt.article_flag,
421 gt.article_title,
422 gt.article_type,
423 gt.article_standard_yn,
424 gt.article_version_number,
425 gt.article_adoption_type,
426 gt.article_org_name,
427 gt.std_article_title,
428 gt.std_article_version_number,
429 gt.object_version_number)
430 values (
431 ado.article_srch_id,
432 ado.article_id,
433 ado.article_version_id,
434 ado.local_org_id,
435 'S',
436 ado.article_title,
437 ado.article_type,
438 'Y',
439 ado.article_version_number,
440 ado.adoption_type,
441 ado.org_name,
442 null,null,1);
443 end if;
444 exception when others then null;
445 end;
446
447 procedure include_all_versions( p_search_id in number,
448 p_all_versions in varchar2
449 ) as
450 begin
451 if p_all_versions = 'Y' THEN
452 merge into okc_ca_art_versions_gt gt
453 using (
454 select
455 article_srch_id,
456 t.article_id,
457 a.article_version_id,
458 t.article_org_id,
459 t.article_title,
460 t.article_type,
461 a.article_version_number,
462 article_adoption_type,
463 org_name
464 from
465 okc_article_versions a,
466 okc_ca_art_versions_gt t,
467 ( select
468 orgu.organization_id org_id,
469 orgu.name org_name
470 from
471 hr_organization_information orgi,
472 hr_operating_units orgu
473 where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
474 and orgi.organization_id = orgu.organization_id
475 ) org
476 where t.article_srch_id = p_search_id
477 and a.article_id = t.article_id
478 and article_adoption_type in ('LOCAL','GLOBAL','LOCALIZED')
479 and article_status in ('APPROVED','ON_HOLD')
480 and org.org_id = article_org_id
481 union all
482 select
483 article_srch_id,
484 t.article_id,
485 a.article_version_id,
486 t.article_org_id,
487 t.article_title,
488 t.article_type,
489 a.article_version_number,
490 'ADOPTED' adoption_type,
491 org_name
492 from
493 okc_article_versions a,
494 okc_ca_art_versions_gt t,
495 ( select
496 orgu.organization_id org_id,
497 orgu.name org_name
498 from
499 hr_organization_information orgi,
500 hr_operating_units orgu
501 where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
502 and orgi.organization_id = orgu.organization_id
503 ) org
504 where t.article_srch_id = p_search_id
505 and a.article_id = t.article_id
506 and t.article_adoption_type = 'ADOPTED'
507 and exists (
508 select 1
509 from okc_article_adoptions
510 where local_org_id = t.article_org_id
511 and global_article_version_id = a.article_version_id
512 and adoption_type = 'ADOPTED'
513 and adoption_status = 'APPROVED'
514 )
515 and org.org_id = article_org_id
516 ) ver
517 on (
518 gt.article_srch_id = ver.article_srch_id
519 and gt.article_id = ver.article_id
520 and gt.article_version_id = ver.article_version_id
521 and gt.article_org_id = ver.article_org_id
522 )
523 when matched then
524 update set gt.object_version_number = gt.object_version_number+1
525 when not matched then
526 insert (
527 gt.article_srch_id,
528 gt.article_id,
529 gt.article_version_id,
530 gt.article_org_id,
531 gt.article_flag,
532 gt.article_title,
533 gt.article_type,
534 gt.article_standard_yn,
535 gt.article_version_number,
536 gt.article_adoption_type,
537 gt.article_org_name,
538 gt.std_article_title,
539 gt.std_article_version_number,
540 gt.object_version_number)
541 values (
542 ver.article_srch_id,
543 ver.article_id,
544 ver.article_version_id,
545 ver.article_org_id,
546 'S',
547 ver.article_title,
548 ver.article_type,
549 'Y',
550 ver.article_version_number,
551 ver.article_adoption_type,
552 ver.org_name,
553 null,null,1);
554 end if;
555 exception when others then null;
556 end;
557
558 procedure include_non_standard( p_search_id in number,
559 p_non_standard in varchar2
560 ) as
561 begin
562 if p_non_standard in ( 'A', 'N' ) then
563 merge into okc_ca_art_versions_gt gt
564 using (
565 select
566 article_srch_id,
567 a.article_id,
568 v.article_version_id,
569 a.org_id,
570 a.article_title,
571 a.article_type,
572 v.article_version_number,
573 'NON-STANDARD' adoption_type,
574 org_name,
575 t.article_title std_article_title,
576 t.article_version_number std_article_version_number
577 from
578 okc_articles_all a,
579 okc_article_versions v,
580 okc_ca_art_versions_gt t,
581 ( select
582 orgu.organization_id org_id,
583 orgu.name org_name
584 from
585 hr_organization_information orgi,
586 hr_operating_units orgu
587 where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
588 and orgi.organization_id = orgu.organization_id
589 ) org
590 where a.article_id = v.article_id
591 and standard_yn = 'N'
592 and article_srch_id = p_search_id
593 and t.article_version_id = std_article_version_id
594 and t.article_org_id = org.org_id
595 and t.article_org_id = a.org_id
596 ) non
597 on (
598 gt.article_srch_id = non.article_srch_id
599 and gt.article_id = non.article_id
600 and gt.article_version_id = non.article_version_id
601 and gt.article_org_id = non.org_id
602 and gt.article_standard_yn = 'N'
603 )
604 when matched then
605 update set gt.object_version_number = gt.object_version_number+1
606 when not matched then
607 insert (
608 gt.article_srch_id,
609 gt.article_id,
610 gt.article_version_id,
611 gt.article_org_id,
612 gt.article_flag,
613 gt.article_title,
614 gt.article_type,
615 gt.article_standard_yn,
616 gt.article_version_number,
617 gt.article_adoption_type,
618 gt.article_org_name,
619 gt.std_article_title,
620 gt.std_article_version_number,
621 gt.object_version_number)
622 values (
623 non.article_srch_id,
624 non.article_id,
625 non.article_version_id,
626 non.org_id,
627 'S',
628 non.article_title,
629 non.article_type,
630 'N',
631 non.article_version_number,
632 null,
633 -- non.adoption_type,
634 non.org_name,
635 non.std_article_title,
636 non.std_article_version_number,
637 1);
638 end if;
639 exception when others then null;
640 end;
641
642 procedure include_articles( p_search_id in number,
643 p_alternates in varchar2,
644 p_adoptions in varchar2,
645 p_all_versions in varchar2,
646 p_non_standard in varchar2
647 ) as
648 begin
649 remove_included(p_search_id);
650 include_alternates(p_search_id, p_alternates);
651 include_all_versions(p_search_id, p_all_versions);
652 include_adoptions(p_search_id, p_adoptions);
653 include_non_standard(p_search_id, p_non_standard);
654 exception when others then null;
655 end;
656
657 function article_number( p_article_id in number)
658 return okc_articles_all.article_number%type
659 as
660 result okc_articles_all.article_number%type;
661 begin
662 select nvl(art.article_number, decode(std_article_version_id, null, art.article_number,
663 (select
664 a1.article_number
665 from
666 okc_articles_all a,
667 okc_article_versions v,
668 okc_article_versions v1,
669 okc_articles_all a1
670 where
671 a.article_id = v.article_id
672 and v.std_article_version_id = v1.article_version_id
673 and v1.article_id = a1.article_id
674 and a.article_id = art.article_id)
675 )) article_number into result
676 from okc_articles_all art,
677 okc_article_versions ver
678 where art.article_id = ver.article_id
679 and art.article_id = p_article_id
680 and rownum <= 1;
681 return result;
682 exception when others then return null;
683 end;
684
685 end;