[Home] [Help]
PACKAGE BODY: APPS.FWK_TBX_SEED
Source
1 package body fwk_tbx_seed as
2 /* $Header: fwktbx_seedb.pls 120.2 2005/12/18 20:57:28 nigoel noship $ */
3
4 procedure insert_lookup_type(p_lookup_type varchar2,
5 p_display_name varchar2,
6 p_description varchar2)
7 is
8 begin
9
10 insert into fwk_tbx_lookup_types_tl (
11 lookup_type,
12 display_name,
13 description,
14 language,
15 source_lang
16 )
17 select p_lookup_type,
18 p_display_name,
19 p_description,
20 'US',
21 'US'
22 from dual
23 where not exists
24 (select null
25 from fwk_tbx_lookup_types_tl t
26 where t.lookup_type = p_lookup_type
27 and t.language = 'US');
28
29
30 end insert_lookup_type;
31
32
33
34
35 procedure insert_lookup_code(p_lookup_type varchar2,
36 p_lookup_code varchar2,
37 p_meaning varchar2,
38 p_description varchar2,
39 p_start_date date,
40 p_end_date date)
41 is
42 begin
43
44 insert into fwk_tbx_lookup_codes_b (
45 lookup_type,
46 lookup_code,
47 start_date_active,
48 end_date_active
49 )
50 select p_lookup_type,
51 p_lookup_code,
52 p_start_date,
53 p_end_date
54 from dual
55 where not exists (select null
56 from fwk_tbx_lookup_codes_b
57 where lookup_type = p_lookup_type
58 and lookup_code = p_lookup_code);
59
60 insert into fwk_tbx_lookup_codes_tl (
61 lookup_type,
62 lookup_code,
63 meaning,
64 description,
65 language,
66 source_lang
67 )
68 select p_lookup_type,
69 p_lookup_code,
70 p_meaning,
71 p_description,
72 'US',
73 'US'
74 from dual
75 where not exists
76 (select null
77 from fwk_tbx_lookup_codes_tl t
78 where t.lookup_type = p_lookup_type
79 and t.lookup_code = p_lookup_code
80 and t.language = 'US');
81
82
83 end insert_lookup_code;
84
85 procedure update_lookup_type(p_lookup_type varchar2,
86 p_display_name varchar2,
87 p_description varchar2)
88 is
89 begin
90
91 update fwk_tbx_lookup_types_tl set
92 lookup_type = p_lookup_type,
93 display_name = p_display_name,
94 description = p_description,
95 source_lang = userenv('LANG')
96 where lookup_type = p_lookup_type
97 and userenv('LANG') in (language,source_lang);
98
99 if (sql%notfound) then
100 raise no_data_found;
101 end if;
102
103 end update_lookup_type;
104
105
106 procedure update_lookup_code(p_lookup_type varchar2,
107 p_lookup_code varchar2,
108 p_meaning varchar2,
109 p_description varchar2,
110 p_start_date date,
111 p_end_date date)
112 is
113 begin
114
115 update fwk_tbx_lookup_codes_b set
116 lookup_type = p_lookup_type,
117 lookup_code = p_lookup_code,
118 start_date_active = p_start_date,
119 end_date_active = p_end_date
120 where lookup_type = p_lookup_type
121 and lookup_code = p_lookup_code;
122
123 if (sql%notfound) then
124 raise no_data_found;
125 end if;
126
127 update fwk_tbx_lookup_codes_tl set
128 lookup_type = p_lookup_type,
129 lookup_code = p_lookup_code,
130 meaning = p_meaning,
131 description = p_description,
132 source_lang = userenv('LANG')
133 where lookup_type = p_lookup_type
134 and lookup_code = p_lookup_code
135 and userenv('LANG') in (language,source_lang);
136
137 if (sql%notfound) then
138 raise no_data_found;
139 end if;
140
141 end update_lookup_code;
142
143
144 procedure delete_lookup_type(p_lookup_type varchar2)
145 is
146 begin
147
148 delete from fwk_tbx_lookup_types_tl
149 where lookup_type = p_lookup_type;
150
151 if (sql%notfound) then
152 raise no_data_found;
153 end if;
154
155 end delete_lookup_type;
156
157
158 procedure delete_lookup_code(p_lookup_type varchar2,
159 p_lookup_code varchar2)
160 is
161 begin
162
163 delete from fwk_tbx_lookup_codes_b
164 where lookup_type = p_lookup_type
165 and lookup_code = p_lookup_code;
166
167 if (sql%notfound) then
168 raise no_data_found;
169 end if;
170
171 delete from fwk_tbx_lookup_codes_tl
172 where lookup_type = p_lookup_type
173 and lookup_code = p_lookup_code;
174
175 if (sql%notfound) then
176 raise no_data_found;
177 end if;
178
179 end delete_lookup_code;
180
181
182 procedure insert_employee(p_employee_id number,
183 p_title varchar2,
184 p_first_name varchar2,
185 p_middle_names varchar2,
186 p_last_name varchar2,
187 p_email_address varchar2,
188 p_manager_id number,
189 p_position_code varchar2,
190 p_salary number,
191 p_start_date date,
192 p_end_date date)
193 is
194 begin
195
196 insert into fwk_tbx_employees
197 (employee_id,
198 title,
199 first_name,
200 middle_names,
201 last_name,
202 full_name,
203 email_address,
204 manager_id,
205 position_code,
206 salary,
207 start_date,
208 end_date,
209 last_update_date,
210 last_updated_by,
211 creation_date,
212 created_by)
213 select p_employee_id,
214 p_title,
215 p_first_name,
216 p_middle_names,
217 p_last_name,
218 p_last_name||', '||p_first_name||' '||p_middle_names,
219 p_email_address,
220 p_manager_id,
221 p_position_code,
222 p_salary,
223 p_start_date,
224 p_end_date,
225 sysdate,
226 0,
227 sysdate,
228 0
229 from dual
230 where not exists (select null
231 from fwk_tbx_employees
232 where employee_id = p_employee_id);
233
234 end insert_employee;
235
236
237 procedure insert_address(p_address_id number,
238 p_address_name varchar2,
239 p_address_line_1 varchar2,
240 p_address_line_2 varchar2,
241 p_address_line_3 varchar2,
242 p_description varchar2,
243 p_email_address varchar2,
244 p_country varchar2,
245 p_town_or_city varchar2,
246 p_postal_code varchar2,
247 p_start_date date,
248 p_end_date date,
249 p_telephone_number_1 varchar2,
250 p_telephone_number_2 varchar2,
251 p_telephone_number_3 varchar2)
252 is
253 begin
254
255 insert into fwk_tbx_addresses
256 (address_id,
257 address_name,
258 address_line_1,
259 address_line_2,
260 address_line_3,
261 description,
262 email_address,
263 country,
264 town_or_city,
265 postal_code,
266 start_date,
267 end_date,
268 telephone_number_1,
269 telephone_number_2,
270 telephone_number_3,
271 last_update_date,
272 last_updated_by,
273 creation_date,
274 created_by)
275 select p_address_id,
276 p_address_name,
277 p_address_line_1,
278 p_address_line_2,
279 p_address_line_3,
280 p_description,
281 p_email_address,
282 p_country,
283 p_town_or_city,
284 p_postal_code,
285 p_start_date,
286 p_end_date,
287 p_telephone_number_1,
288 p_telephone_number_2,
289 p_telephone_number_3,
290 sysdate,
291 0,
292 sysdate,
293 0
294 from dual
295 where not exists (select null
296 from fwk_tbx_addresses
297 where address_id = p_address_id);
298
299 end insert_address;
300
301
302 procedure insert_supplier(p_supplier_id number,
303 p_name varchar2,
304 p_on_hold_flag varchar2,
305 p_start_date date,
306 p_end_date date)
307 is
308 begin
309
310 insert into fwk_tbx_suppliers
311 (supplier_id,
312 name,
313 on_hold_flag,
314 start_date,
315 end_date,
316 last_update_date,
317 last_updated_by,
318 creation_date,
319 created_by)
320 select p_supplier_id,
321 p_name,
322 p_on_hold_flag,
323 p_start_date,
324 p_end_date,
325 sysdate,
326 0,
327 sysdate,
328 0
329 from dual
330 where not exists (select null
331 from fwk_tbx_suppliers
332 where supplier_id = p_supplier_id);
333
334 end insert_supplier;
335
336
337 procedure insert_supplier_site(p_supplier_site_id number,
338 p_supplier_id number,
339 p_site_name varchar2,
340 p_payment_terms_code varchar2,
341 p_carrier_code varchar2,
342 p_purchasing_site_flag varchar2,
343 p_address_id number,
344 p_start_date date,
345 p_end_date date)
346 is
347 begin
348
349 insert into fwk_tbx_supplier_sites
350 (supplier_id,
351 supplier_site_id,
352 site_name,
353 payment_terms_code,
354 carrier_code,
355 purchasing_site_flag,
356 address_id,
357 start_date,
358 end_date,
359 last_update_date,
360 last_updated_by,
361 creation_date,
362 created_by)
363 select p_supplier_id,
364 p_supplier_site_id,
365 p_site_name,
366 p_payment_terms_code,
367 p_carrier_code,
368 p_purchasing_site_flag,
369 p_address_id,
370 p_start_date,
371 p_end_date,
372 sysdate,
373 0,
374 sysdate,
375 0
376 from dual
377 where not exists (select null
378 from fwk_tbx_supplier_sites
379 where supplier_site_id = p_supplier_site_id);
380
381 end insert_supplier_site;
382
383
384 procedure insert_item(p_item_id number,
385 p_item_description varchar2,
386 p_start_date_active date,
387 p_end_date_active date,
388 p_enabled_flag varchar2,
389 p_summary_flag varchar2,
390 p_segment1 varchar2,
391 p_segment2 varchar2,
392 p_segment3 varchar2,
393 p_segment4 varchar2,
394 p_fwkitem_id number,
395 p_fwkitem_structure_id number)
396 is
397 begin
398
399 insert into fwk_tbx_items
400 (item_id,
401 item_description,
402 start_date_active,
403 end_date_active,
404 enabled_flag,
405 summary_flag,
406 segment1,
407 segment2,
408 segment3,
409 segment4,
410 creation_date,
411 created_by,
412 last_update_date,
413 last_updated_by,
414 fwkitem_id,
415 fwkitem_structure_id)
416 select p_item_id,
417 p_item_description,
418 p_start_date_active,
419 p_end_date_active,
420 p_enabled_flag,
421 p_summary_flag,
422 p_segment1,
423 p_segment2,
424 p_segment3,
425 p_segment4,
426 sysdate,
427 0,
428 sysdate,
429 0,
430 p_fwkitem_id,
431 p_fwkitem_structure_id
432 from dual
433 where not exists (select null
434 from fwk_tbx_items
435 where item_id = p_item_id);
436
437 end insert_item;
438
439
440
441 procedure insert_po_header(p_header_id number,
442 p_description varchar2,
443 p_status_code varchar2,
444 p_confirm_flag varchar2,
445 p_supplier_id number,
446 p_supplier_site_id number,
447 p_currency_code varchar2,
448 p_buyer_id number,
449 p_payment_terms_code varchar2,
450 p_carrier_code varchar2,
451 p_ship_to_address_id number,
452 p_bill_to_address_id number,
453 p_rate number)
454 is
455 begin
456
457 insert into fwk_tbx_po_headers
458 (header_id,
459 description,
460 status_code,
461 confirm_flag,
462 supplier_id,
463 supplier_site_id,
464 currency_code,
465 buyer_id,
469 bill_to_address_id,
466 payment_terms_code,
467 carrier_code,
468 ship_to_address_id,
470 rate,
471 creation_date,
472 created_by,
473 last_update_date,
474 last_updated_by)
475 select p_header_id,
476 p_description,
477 p_status_code,
478 p_confirm_flag,
479 p_supplier_id,
480 p_supplier_site_id,
481 p_currency_code,
482 p_buyer_id,
483 p_payment_terms_code,
484 p_carrier_code,
485 p_ship_to_address_id,
486 p_bill_to_address_id,
487 p_rate,
488 sysdate,
489 0,
490 sysdate,
491 0
492 from dual
493 where not exists (select null
494 from fwk_tbx_po_headers
495 where header_id = p_header_id);
496
497
498 end insert_po_header;
499
500
501
502 procedure insert_po_line(p_line_id number,
503 p_header_id number,
504 p_line_number number,
505 p_item_id number,
506 p_item_description varchar2,
507 p_unit_of_measure varchar2,
508 p_quantity number,
509 p_unit_price number)
510 is
511 begin
512
513 insert into fwk_tbx_po_lines
514 (line_id,
515 header_id,
516 line_number,
517 item_id,
518 item_description,
519 unit_of_measure,
520 quantity,
521 unit_price,
522 last_update_date,
523 last_updated_by,
524 creation_date,
525 created_by)
526 select p_line_id,
527 p_header_id,
528 p_line_number,
529 p_item_id,
530 p_item_description,
531 p_unit_of_measure,
532 p_quantity,
533 p_unit_price,
534 sysdate,
535 0,
536 sysdate,
537 0
538 from dual
539 where not exists (select null
540 from fwk_tbx_po_lines
541 where line_id = p_line_id);
542
543 end insert_po_line;
544
545
546 procedure insert_po_shipment(p_shipment_id number,
547 p_line_id number,
548 p_shipment_number number,
549 p_need_by_date date,
550 p_promise_date date,
551 p_receipt_quantity number,
552 p_order_quantity number,
553 p_ship_to_address_id number,
554 p_receipt_date date)
555 is
556 begin
557
558 insert into fwk_tbx_po_shipments
559 (shipment_id,
560 line_id,
561 shipment_number,
562 need_by_date,
563 promise_date,
564 receipt_quantity,
565 order_quantity,
566 ship_to_address_id,
567 receipt_date,
568 last_update_date,
569 last_updated_by,
570 creation_date,
571 created_by)
572 select p_shipment_id,
573 p_line_id,
574 p_shipment_number,
575 p_need_by_date,
576 p_promise_date,
577 p_receipt_quantity,
578 p_order_quantity,
579 p_ship_to_address_id,
580 p_receipt_date,
581 sysdate,
582 0,
583 sysdate,
584 0
585 from dual
586 where not exists (select null
587 from fwk_tbx_po_shipments
588 where shipment_id = p_shipment_id);
589
590 end insert_po_shipment;
591
592
593 procedure insert_item_ccids(p_fwkitem_id number,
594 p_fwkitem_structure_id number,
595 p_summary_flag varchar2,
596 p_enabled_flag varchar2,
600 p_segment4 varchar2,
597 p_segment1 varchar2,
598 p_segment2 varchar2,
599 p_segment3 varchar2,
601 p_segment5 varchar2,
602 p_segment6 varchar2,
603 p_segment7 varchar2,
604 p_segment8 varchar2,
605 p_segment9 varchar2,
606 p_segment10 varchar2)
607 is
608 begin
609
610 insert into fwk_tbx_item_ccids (
611 FWKITEM_ID,
612 FWKITEM_STRUCTURE_ID,
613 SUMMARY_FLAG,
614 ENABLED_FLAG,
615 SEGMENT1,
616 SEGMENT2,
617 SEGMENT3,
618 SEGMENT4,
619 SEGMENT5,
620 SEGMENT6,
621 SEGMENT7,
622 SEGMENT8,
623 SEGMENT9,
624 SEGMENT10,
625 start_date_active,
626 end_date_active,
627 creation_date,
628 created_by,
629 last_update_login,
630 last_update_date,
631 last_updated_by
632 )
633 select p_fwkitem_id,
634 p_fwkitem_structure_id,
635 p_summary_flag,
636 p_enabled_flag,
637 p_segment1,
638 p_segment2,
639 p_segment3,
640 p_segment4,
641 p_segment5,
642 p_segment6,
643 p_segment7,
644 p_segment8,
645 p_segment9,
646 p_segment10,
647 sysdate,
648 to_date(null),
649 sysdate,
650 0,
651 0,
652 sysdate,
653 0
654 from dual
655 where not exists (select null
656 from fwk_tbx_item_ccids
657 where fwkitem_id = p_fwkitem_id);
658
659 end insert_item_ccids;
660
661
662 procedure insert_project_header(p_project_id number,
663 p_name varchar2,
664 p_start_date date,
665 p_completion_date date,
666 p_start_from date,
667 p_end_to date,
668 p_task_type varchar2,
669 p_text_right varchar2)
670 is
671 begin
672 INSERT INTO FWK_TBX_PROJECT_HEADERS
673 ( PROJECT_ID,
674 NAME,
675 START_DATE,
676 COMPLETION_DATE,
677 START_FROM,
678 END_TO,
679 TASK_TYPE,
680 TEXT_RIGHT,
681 LAST_UPDATE_DATE,
682 LAST_UPDATED_BY,
683 CREATION_DATE,
684 CREATED_BY )
685 SELECT p_project_id,
686 p_name,
687 p_start_date,
688 p_completion_date,
689 p_start_from,
690 p_end_to,
691 p_task_type,
692 p_text_right,
693 sysdate,
694 0,
695 sysdate,
696 0
697 FROM DUAL
698 WHERE not exists (SELECT null
699 FROM FWK_TBX_PROJECT_HEADERS
700 WHERE PROJECT_ID = p_project_id);
701
702 end insert_project_header;
703
704 procedure insert_project_detail(p_project_id number,
705 p_top_task_id number,
706 p_task_id number,
707 p_task_number varchar2,
708 p_task_name varchar2,
709 p_start_from date,
710 p_end_to date,
711 p_task_type varchar2,
712 p_text_right varchar2)
713 is
714 begin
715 INSERT INTO FWK_TBX_PROJECT_DETAILS
716 ( PROJECT_ID,
717 TOP_TASK_ID,
718 TASK_ID,
719 TASK_NUMBER,
720 TASK_NAME,
721 START_FROM,
722 END_TO,
723 TASK_TYPE,
724 TEXT_RIGHT,
725 LAST_UPDATE_DATE,
726 LAST_UPDATED_BY,
727 CREATION_DATE,
728 CREATED_BY )
729 SELECT p_project_id,
730 p_top_task_id,
731 p_task_id,
732 p_task_number,
733 p_task_name,
734 p_start_from,
735 p_end_to,
736 p_task_type,
737 p_text_right,
738 sysdate,
739 0,
740 sysdate,
741 0
742 FROM DUAL
743 WHERE not exists (SELECT null
744 FROM FWK_TBX_PROJECT_DETAILS
745 WHERE TASK_ID = p_task_id);
746
747 end insert_project_detail;
748
749 end fwk_tbx_seed;