1 package body SY_ORGN_MST_PKG as
2 /* $Header: gmaorgnb.pls 115.3 2002/10/31 19:51:02 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ORGN_CODE in VARCHAR2,
6 X_ATTRIBUTE12 in VARCHAR2,
7 X_ATTRIBUTE13 in VARCHAR2,
8 X_ATTRIBUTE14 in VARCHAR2,
9 X_ATTRIBUTE15 in VARCHAR2,
10 X_ATTRIBUTE16 in VARCHAR2,
11 X_ATTRIBUTE17 in VARCHAR2,
12 X_ATTRIBUTE18 in VARCHAR2,
13 X_ATTRIBUTE19 in VARCHAR2,
14 X_ATTRIBUTE20 in VARCHAR2,
15 X_ATTRIBUTE21 in VARCHAR2,
16 X_ATTRIBUTE22 in VARCHAR2,
17 X_ATTRIBUTE23 in VARCHAR2,
18 X_ATTRIBUTE24 in VARCHAR2,
19 X_ATTRIBUTE25 in VARCHAR2,
20 X_ATTRIBUTE26 in VARCHAR2,
21 X_ATTRIBUTE27 in VARCHAR2,
22 X_ATTRIBUTE28 in VARCHAR2,
23 X_ATTRIBUTE29 in VARCHAR2,
24 X_ATTRIBUTE30 in VARCHAR2,
25 X_ATTRIBUTE_CATEGORY in VARCHAR2,
26 X_PARENT_ORGN_CODE in VARCHAR2,
27 X_CO_CODE in VARCHAR2,
28 X_PLANT_IND in NUMBER,
29 X_ADDR_ID in NUMBER,
30 X_ORGANIZATION_ID in NUMBER,
31 X_TAXLOC_CODE in VARCHAR2,
32 X_TEXT_CODE in NUMBER,
33 X_DELETE_MARK in NUMBER,
34 X_TRANS_CNT in NUMBER,
35 X_POC_IND in NUMBER,
36 X_RESOURCE_WHSE_CODE in VARCHAR2,
37 X_SY_ORGN_MST_ORGN_CODE in VARCHAR2,
38 X_SY_ORGN_MST_DOC_TYPE in VARCHAR2,
39 X_ATTRIBUTE1 in VARCHAR2,
40 X_ATTRIBUTE2 in VARCHAR2,
41 X_ATTRIBUTE3 in VARCHAR2,
42 X_ATTRIBUTE4 in VARCHAR2,
43 X_ATTRIBUTE5 in VARCHAR2,
44 X_ATTRIBUTE6 in VARCHAR2,
45 X_ATTRIBUTE7 in VARCHAR2,
46 X_ATTRIBUTE8 in VARCHAR2,
47 X_ATTRIBUTE9 in VARCHAR2,
48 X_ATTRIBUTE10 in VARCHAR2,
49 X_ATTRIBUTE11 in VARCHAR2,
50 X_ORGN_NAME in VARCHAR2,
51 X_CREATION_DATE in DATE,
52 X_CREATED_BY in NUMBER,
53 X_LAST_UPDATE_DATE in DATE,
54 X_LAST_UPDATED_BY in NUMBER,
55 X_LAST_UPDATE_LOGIN in NUMBER,
56 X_MFG_CALENDAR_ID in NUMBER
57
58 ) is
59 cursor C is select ROWID from SY_ORGN_MST_B
60 where ORGN_CODE = X_ORGN_CODE
61 ;
62 begin
63 insert into SY_ORGN_MST_B (
64 ATTRIBUTE12,
65 ATTRIBUTE13,
66 ATTRIBUTE14,
67 ATTRIBUTE15,
68 ATTRIBUTE16,
69 ATTRIBUTE17,
70 ATTRIBUTE18,
71 ATTRIBUTE19,
72 ATTRIBUTE20,
73 ATTRIBUTE21,
74 ATTRIBUTE22,
75 ATTRIBUTE23,
76 ATTRIBUTE24,
77 ATTRIBUTE25,
78 ATTRIBUTE26,
79 ATTRIBUTE27,
80 ATTRIBUTE28,
81 ATTRIBUTE29,
82 ATTRIBUTE30,
83 ATTRIBUTE_CATEGORY,
84 ORGN_CODE,
85 PARENT_ORGN_CODE,
86 CO_CODE,
87 PLANT_IND,
88 ADDR_ID,
89 ORGANIZATION_ID,
90 TAXLOC_CODE,
91 TEXT_CODE,
92 DELETE_MARK,
93 TRANS_CNT,
94 POC_IND,
95 RESOURCE_WHSE_CODE,
96 SY_ORGN_MST_ORGN_CODE,
97 SY_ORGN_MST_DOC_TYPE,
98 ATTRIBUTE1,
99 ATTRIBUTE2,
100 ATTRIBUTE3,
101 ATTRIBUTE4,
102 ATTRIBUTE5,
103 ATTRIBUTE6,
104 ATTRIBUTE7,
105 ATTRIBUTE8,
106 ATTRIBUTE9,
107 ATTRIBUTE10,
108 ATTRIBUTE11,
109 CREATION_DATE,
110 CREATED_BY,
111 LAST_UPDATE_DATE,
112 LAST_UPDATED_BY,
113 LAST_UPDATE_LOGIN,
114 MFG_CALENDAR_ID
115 ) values (
116 X_ATTRIBUTE12,
117 X_ATTRIBUTE13,
118 X_ATTRIBUTE14,
119 X_ATTRIBUTE15,
120 X_ATTRIBUTE16,
121 X_ATTRIBUTE17,
122 X_ATTRIBUTE18,
123 X_ATTRIBUTE19,
124 X_ATTRIBUTE20,
125 X_ATTRIBUTE21,
126 X_ATTRIBUTE22,
127 X_ATTRIBUTE23,
128 X_ATTRIBUTE24,
129 X_ATTRIBUTE25,
130 X_ATTRIBUTE26,
131 X_ATTRIBUTE27,
132 X_ATTRIBUTE28,
133 X_ATTRIBUTE29,
134 X_ATTRIBUTE30,
135 X_ATTRIBUTE_CATEGORY,
136 X_ORGN_CODE,
137 X_PARENT_ORGN_CODE,
138 X_CO_CODE,
139 X_PLANT_IND,
140 X_ADDR_ID,
141 X_ORGANIZATION_ID,
142 X_TAXLOC_CODE,
143 X_TEXT_CODE,
144 X_DELETE_MARK,
145 X_TRANS_CNT,
146 X_POC_IND,
147 X_RESOURCE_WHSE_CODE,
148 X_SY_ORGN_MST_ORGN_CODE,
149 X_SY_ORGN_MST_DOC_TYPE,
150 X_ATTRIBUTE1,
151 X_ATTRIBUTE2,
152 X_ATTRIBUTE3,
153 X_ATTRIBUTE4,
154 X_ATTRIBUTE5,
155 X_ATTRIBUTE6,
156 X_ATTRIBUTE7,
157 X_ATTRIBUTE8,
158 X_ATTRIBUTE9,
159 X_ATTRIBUTE10,
160 X_ATTRIBUTE11,
161 X_CREATION_DATE,
162 X_CREATED_BY,
163 X_LAST_UPDATE_DATE,
164 X_LAST_UPDATED_BY,
165 X_LAST_UPDATE_LOGIN,
166 X_MFG_CALENDAR_ID
167 );
168
169 insert into SY_ORGN_MST_TL (
170 LAST_UPDATE_DATE,
171 LAST_UPDATED_BY,
172 LAST_UPDATE_LOGIN,
173 ORGN_CODE,
174 CREATED_BY,
175 CREATION_DATE,
176 ORGN_NAME,
177 LANGUAGE,
178 SOURCE_LANG
179 ) select
180 X_LAST_UPDATE_DATE,
181 X_LAST_UPDATED_BY,
182 X_LAST_UPDATE_LOGIN,
183 X_ORGN_CODE,
184 X_CREATED_BY,
185 X_CREATION_DATE,
186 X_ORGN_NAME,
187 L.LANGUAGE_CODE,
188 userenv('LANG')
189 from FND_LANGUAGES L
190 where L.INSTALLED_FLAG in ('I', 'B')
191 and not exists
192 (select NULL
193 from SY_ORGN_MST_TL T
194 where T.ORGN_CODE = X_ORGN_CODE
195 and T.LANGUAGE = L.LANGUAGE_CODE);
196
197 open c;
198 fetch c into X_ROWID;
199 if (c%notfound) then
200 close c;
201 raise no_data_found;
202 end if;
203 close c;
204
205 end INSERT_ROW;
206
207 procedure LOCK_ROW (
208 X_ORGN_CODE in VARCHAR2,
209 X_ATTRIBUTE12 in VARCHAR2,
210 X_ATTRIBUTE13 in VARCHAR2,
211 X_ATTRIBUTE14 in VARCHAR2,
212 X_ATTRIBUTE15 in VARCHAR2,
213 X_ATTRIBUTE16 in VARCHAR2,
214 X_ATTRIBUTE17 in VARCHAR2,
215 X_ATTRIBUTE18 in VARCHAR2,
216 X_ATTRIBUTE19 in VARCHAR2,
217 X_ATTRIBUTE20 in VARCHAR2,
218 X_ATTRIBUTE21 in VARCHAR2,
219 X_ATTRIBUTE22 in VARCHAR2,
220 X_ATTRIBUTE23 in VARCHAR2,
221 X_ATTRIBUTE24 in VARCHAR2,
222 X_ATTRIBUTE25 in VARCHAR2,
223 X_ATTRIBUTE26 in VARCHAR2,
224 X_ATTRIBUTE27 in VARCHAR2,
225 X_ATTRIBUTE28 in VARCHAR2,
226 X_ATTRIBUTE29 in VARCHAR2,
227 X_ATTRIBUTE30 in VARCHAR2,
228 X_ATTRIBUTE_CATEGORY in VARCHAR2,
229 X_PARENT_ORGN_CODE in VARCHAR2,
230 X_CO_CODE in VARCHAR2,
231 X_PLANT_IND in NUMBER,
232 X_ADDR_ID in NUMBER,
233 X_ORGANIZATION_ID in NUMBER,
234 X_TAXLOC_CODE in VARCHAR2,
235 X_TEXT_CODE in NUMBER,
236 X_DELETE_MARK in NUMBER,
237 X_TRANS_CNT in NUMBER,
238 X_POC_IND in NUMBER,
239 X_RESOURCE_WHSE_CODE in VARCHAR2,
240 X_SY_ORGN_MST_ORGN_CODE in VARCHAR2,
241 X_SY_ORGN_MST_DOC_TYPE in VARCHAR2,
242 X_ATTRIBUTE1 in VARCHAR2,
243 X_ATTRIBUTE2 in VARCHAR2,
244 X_ATTRIBUTE3 in VARCHAR2,
245 X_ATTRIBUTE4 in VARCHAR2,
246 X_ATTRIBUTE5 in VARCHAR2,
247 X_ATTRIBUTE6 in VARCHAR2,
248 X_ATTRIBUTE7 in VARCHAR2,
249 X_ATTRIBUTE8 in VARCHAR2,
250 X_ATTRIBUTE9 in VARCHAR2,
251 X_ATTRIBUTE10 in VARCHAR2,
252 X_ATTRIBUTE11 in VARCHAR2,
253 X_ORGN_NAME in VARCHAR2,
254 X_MFG_CALENDAR_ID NUMBER
255 ) is
256 cursor c is select
257 ATTRIBUTE12,
258 ATTRIBUTE13,
259 ATTRIBUTE14,
260 ATTRIBUTE15,
261 ATTRIBUTE16,
262 ATTRIBUTE17,
263 ATTRIBUTE18,
264 ATTRIBUTE19,
265 ATTRIBUTE20,
266 ATTRIBUTE21,
267 ATTRIBUTE22,
268 ATTRIBUTE23,
269 ATTRIBUTE24,
270 ATTRIBUTE25,
271 ATTRIBUTE26,
272 ATTRIBUTE27,
273 ATTRIBUTE28,
274 ATTRIBUTE29,
275 ATTRIBUTE30,
276 ATTRIBUTE_CATEGORY,
277 PARENT_ORGN_CODE,
278 CO_CODE,
279 PLANT_IND,
280 ADDR_ID,
281 ORGANIZATION_ID,
282 TAXLOC_CODE,
283 TEXT_CODE,
284 DELETE_MARK,
285 TRANS_CNT,
286 POC_IND,
287 RESOURCE_WHSE_CODE,
288 SY_ORGN_MST_ORGN_CODE,
289 SY_ORGN_MST_DOC_TYPE,
290 ATTRIBUTE1,
291 ATTRIBUTE2,
292 ATTRIBUTE3,
293 ATTRIBUTE4,
294 ATTRIBUTE5,
295 ATTRIBUTE6,
296 ATTRIBUTE7,
297 ATTRIBUTE8,
298 ATTRIBUTE9,
299 ATTRIBUTE10,
300 ATTRIBUTE11 ,
301 MFG_CALENDAR_ID
302 from SY_ORGN_MST_B
303 where ORGN_CODE = X_ORGN_CODE
304 for update of ORGN_CODE nowait;
305 recinfo c%rowtype;
306
307 cursor c1 is select
308 ORGN_NAME,
309 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
310 from SY_ORGN_MST_TL
311 where ORGN_CODE = X_ORGN_CODE
312 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
313 for update of ORGN_CODE nowait;
314 begin
315 open c;
316 fetch c into recinfo;
317 if (c%notfound) then
318 close c;
319 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
320 app_exception.raise_exception;
321 end if;
322 close c;
323 if ( ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
324 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
325 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
326 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
327 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
328 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
329 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
330 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
331 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
332 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
333 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
334 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
335 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
336 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
337 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
338 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
339 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
340 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
341 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
342 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
343 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
344 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
345 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
346 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
347 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
348 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
349 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
350 OR ((recinfo.ATTRIBUTE25 is null) AND (X_ATTRIBUTE25 is null)))
351 AND ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
352 OR ((recinfo.ATTRIBUTE26 is null) AND (X_ATTRIBUTE26 is null)))
353 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
354 OR ((recinfo.ATTRIBUTE27 is null) AND (X_ATTRIBUTE27 is null)))
355 AND ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
356 OR ((recinfo.ATTRIBUTE28 is null) AND (X_ATTRIBUTE28 is null)))
357 AND ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
358 OR ((recinfo.ATTRIBUTE29 is null) AND (X_ATTRIBUTE29 is null)))
359 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
360 OR ((recinfo.ATTRIBUTE30 is null) AND (X_ATTRIBUTE30 is null)))
361 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
362 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
363 AND (recinfo.PARENT_ORGN_CODE = X_PARENT_ORGN_CODE)
364 AND (recinfo.CO_CODE = X_CO_CODE)
365 AND (recinfo.PLANT_IND = X_PLANT_IND)
366 AND ((recinfo.ADDR_ID = X_ADDR_ID)
367 OR ((recinfo.ADDR_ID is null) AND (X_ADDR_ID is null)))
368 AND ((recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
369 OR ((recinfo.ORGANIZATION_ID is null) AND (X_ORGANIZATION_ID is null)))
370 AND ((recinfo.TAXLOC_CODE = X_TAXLOC_CODE)
371 OR ((recinfo.TAXLOC_CODE is null) AND (X_TAXLOC_CODE is null)))
372 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
373 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
374 AND (recinfo.DELETE_MARK = X_DELETE_MARK)
375 AND (recinfo.TRANS_CNT = X_TRANS_CNT)
376 AND (recinfo.POC_IND = X_POC_IND)
377 AND ((recinfo.RESOURCE_WHSE_CODE = X_RESOURCE_WHSE_CODE)
378 OR ((recinfo.RESOURCE_WHSE_CODE is null) AND (X_RESOURCE_WHSE_CODE is null)))
379 AND ((recinfo.SY_ORGN_MST_ORGN_CODE = X_SY_ORGN_MST_ORGN_CODE)
380 OR ((recinfo.SY_ORGN_MST_ORGN_CODE is null) AND (X_SY_ORGN_MST_ORGN_CODE is null)))
381 AND ((recinfo.SY_ORGN_MST_DOC_TYPE = X_SY_ORGN_MST_DOC_TYPE)
382 OR ((recinfo.SY_ORGN_MST_DOC_TYPE is null) AND (X_SY_ORGN_MST_DOC_TYPE is null)))
383 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
384 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
385 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
386 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
387 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
388 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
389 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
390 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
391 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
392 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
393 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
394 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
395 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
396 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
397 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
398 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
399 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
400 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
401 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
402 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
403 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
404 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
405 AND ((recinfo.MFG_CALENDAR_ID = X_MFG_CALENDAR_ID)
406 OR ((recinfo.MFG_CALENDAR_ID is null) AND (X_MFG_CALENDAR_ID is null)))
407 ) then
408 null;
409 else
410 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
411 app_exception.raise_exception;
412 end if;
413
414 for tlinfo in c1 loop
415 if (tlinfo.BASELANG = 'Y') then
416 if ( (tlinfo.ORGN_NAME = X_ORGN_NAME)
417 ) then
418 null;
419 else
420 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
421 app_exception.raise_exception;
422 end if;
423 end if;
424 end loop;
425 return;
426 end LOCK_ROW;
427
428 procedure UPDATE_ROW (
429 X_ORGN_CODE in VARCHAR2,
430 X_ATTRIBUTE12 in VARCHAR2,
436 X_ATTRIBUTE18 in VARCHAR2,
431 X_ATTRIBUTE13 in VARCHAR2,
432 X_ATTRIBUTE14 in VARCHAR2,
433 X_ATTRIBUTE15 in VARCHAR2,
434 X_ATTRIBUTE16 in VARCHAR2,
435 X_ATTRIBUTE17 in VARCHAR2,
437 X_ATTRIBUTE19 in VARCHAR2,
438 X_ATTRIBUTE20 in VARCHAR2,
439 X_ATTRIBUTE21 in VARCHAR2,
440 X_ATTRIBUTE22 in VARCHAR2,
441 X_ATTRIBUTE23 in VARCHAR2,
442 X_ATTRIBUTE24 in VARCHAR2,
443 X_ATTRIBUTE25 in VARCHAR2,
444 X_ATTRIBUTE26 in VARCHAR2,
445 X_ATTRIBUTE27 in VARCHAR2,
446 X_ATTRIBUTE28 in VARCHAR2,
447 X_ATTRIBUTE29 in VARCHAR2,
448 X_ATTRIBUTE30 in VARCHAR2,
449 X_ATTRIBUTE_CATEGORY in VARCHAR2,
450 X_PARENT_ORGN_CODE in VARCHAR2,
451 X_CO_CODE in VARCHAR2,
452 X_PLANT_IND in NUMBER,
453 X_ADDR_ID in NUMBER,
454 X_ORGANIZATION_ID in NUMBER,
455 X_TAXLOC_CODE in VARCHAR2,
456 X_TEXT_CODE in NUMBER,
457 X_DELETE_MARK in NUMBER,
458 X_TRANS_CNT in NUMBER,
459 X_POC_IND in NUMBER,
460 X_RESOURCE_WHSE_CODE in VARCHAR2,
461 X_SY_ORGN_MST_ORGN_CODE in VARCHAR2,
462 X_SY_ORGN_MST_DOC_TYPE in VARCHAR2,
463 X_ATTRIBUTE1 in VARCHAR2,
464 X_ATTRIBUTE2 in VARCHAR2,
465 X_ATTRIBUTE3 in VARCHAR2,
466 X_ATTRIBUTE4 in VARCHAR2,
467 X_ATTRIBUTE5 in VARCHAR2,
468 X_ATTRIBUTE6 in VARCHAR2,
469 X_ATTRIBUTE7 in VARCHAR2,
470 X_ATTRIBUTE8 in VARCHAR2,
471 X_ATTRIBUTE9 in VARCHAR2,
472 X_ATTRIBUTE10 in VARCHAR2,
473 X_ATTRIBUTE11 in VARCHAR2,
474 X_ORGN_NAME in VARCHAR2,
475 X_LAST_UPDATE_DATE in DATE,
476 X_LAST_UPDATED_BY in NUMBER,
477 X_LAST_UPDATE_LOGIN in NUMBER,
478 X_MFG_CALENDAR_ID in NUMBER
479 ) is
480 begin
481 update SY_ORGN_MST_B set
482 ATTRIBUTE12 = X_ATTRIBUTE12,
483 ATTRIBUTE13 = X_ATTRIBUTE13,
484 ATTRIBUTE14 = X_ATTRIBUTE14,
485 ATTRIBUTE15 = X_ATTRIBUTE15,
486 ATTRIBUTE16 = X_ATTRIBUTE16,
487 ATTRIBUTE17 = X_ATTRIBUTE17,
488 ATTRIBUTE18 = X_ATTRIBUTE18,
489 ATTRIBUTE19 = X_ATTRIBUTE19,
490 ATTRIBUTE20 = X_ATTRIBUTE20,
491 ATTRIBUTE21 = X_ATTRIBUTE21,
492 ATTRIBUTE22 = X_ATTRIBUTE22,
493 ATTRIBUTE23 = X_ATTRIBUTE23,
494 ATTRIBUTE24 = X_ATTRIBUTE24,
495 ATTRIBUTE25 = X_ATTRIBUTE25,
496 ATTRIBUTE26 = X_ATTRIBUTE26,
497 ATTRIBUTE27 = X_ATTRIBUTE27,
498 ATTRIBUTE28 = X_ATTRIBUTE28,
499 ATTRIBUTE29 = X_ATTRIBUTE29,
500 ATTRIBUTE30 = X_ATTRIBUTE30,
501 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
502 PARENT_ORGN_CODE = X_PARENT_ORGN_CODE,
503 CO_CODE = X_CO_CODE,
504 PLANT_IND = X_PLANT_IND,
505 ADDR_ID = X_ADDR_ID,
506 ORGANIZATION_ID = X_ORGANIZATION_ID,
507 TAXLOC_CODE = X_TAXLOC_CODE,
508 TEXT_CODE = X_TEXT_CODE,
509 DELETE_MARK = X_DELETE_MARK,
510 TRANS_CNT = X_TRANS_CNT,
511 POC_IND = X_POC_IND,
512 RESOURCE_WHSE_CODE = X_RESOURCE_WHSE_CODE,
513 SY_ORGN_MST_ORGN_CODE = X_SY_ORGN_MST_ORGN_CODE,
514 SY_ORGN_MST_DOC_TYPE = X_SY_ORGN_MST_DOC_TYPE,
515 ATTRIBUTE1 = X_ATTRIBUTE1,
516 ATTRIBUTE2 = X_ATTRIBUTE2,
517 ATTRIBUTE3 = X_ATTRIBUTE3,
518 ATTRIBUTE4 = X_ATTRIBUTE4,
519 ATTRIBUTE5 = X_ATTRIBUTE5,
520 ATTRIBUTE6 = X_ATTRIBUTE6,
521 ATTRIBUTE7 = X_ATTRIBUTE7,
522 ATTRIBUTE8 = X_ATTRIBUTE8,
523 ATTRIBUTE9 = X_ATTRIBUTE9,
524 ATTRIBUTE10 = X_ATTRIBUTE10,
525 ATTRIBUTE11 = X_ATTRIBUTE11,
526 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
527 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
528 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN ,
529 MFG_CALENDAR_ID = X_MFG_CALENDAR_ID
533 raise no_data_found;
530 where ORGN_CODE = X_ORGN_CODE;
531
532 if (sql%notfound) then
534 end if;
535
536 update SY_ORGN_MST_TL set
537 ORGN_NAME = X_ORGN_NAME,
538 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
539 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
540 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
541 SOURCE_LANG = userenv('LANG')
542 where ORGN_CODE = X_ORGN_CODE
543 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
544
545 if (sql%notfound) then
546 raise no_data_found;
547 end if;
548 end UPDATE_ROW;
549
550 procedure DELETE_ROW (
551 X_ORGN_CODE in VARCHAR2
552 ) is
553 begin
554 /*****************
555 delete from SY_ORGN_MST_TL
556 where ORGN_CODE = X_ORGN_CODE;
557
558 if (sql%notfound) then
559 raise no_data_found;
560 end if;
561
562 ************************ */
563
564 UPDATE SY_ORGN_MST_B set delete_mark = 1
565 where ORGN_CODE = X_ORGN_CODE;
566
567 if (sql%notfound) then
568 raise no_data_found;
569 end if;
570 end DELETE_ROW;
571
572 procedure ADD_LANGUAGE
573 is
574 begin
575 delete from SY_ORGN_MST_TL T
576 where not exists
577 (select NULL
578 from SY_ORGN_MST_B B
579 where B.ORGN_CODE = T.ORGN_CODE
580 );
581
582 update SY_ORGN_MST_TL T set (
583 ORGN_NAME
584 ) = (select
585 B.ORGN_NAME
586 from SY_ORGN_MST_TL B
587 where B.ORGN_CODE = T.ORGN_CODE
588 and B.LANGUAGE = T.SOURCE_LANG)
589 where (
590 T.ORGN_CODE,
591 T.LANGUAGE
592 ) in (select
593 SUBT.ORGN_CODE,
594 SUBT.LANGUAGE
595 from SY_ORGN_MST_TL SUBB, SY_ORGN_MST_TL SUBT
596 where SUBB.ORGN_CODE = SUBT.ORGN_CODE
597 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
598 and (SUBB.ORGN_NAME <> SUBT.ORGN_NAME
599 ));
600
601 insert into SY_ORGN_MST_TL (
602 LAST_UPDATE_DATE,
603 LAST_UPDATED_BY,
604 LAST_UPDATE_LOGIN,
605 ORGN_CODE,
606 CREATED_BY,
607 CREATION_DATE,
608 ORGN_NAME,
609 LANGUAGE,
610 SOURCE_LANG
611 ) select
612 B.LAST_UPDATE_DATE,
613 B.LAST_UPDATED_BY,
614 B.LAST_UPDATE_LOGIN,
615 B.ORGN_CODE,
616 B.CREATED_BY,
617 B.CREATION_DATE,
618 B.ORGN_NAME,
619 L.LANGUAGE_CODE,
620 B.SOURCE_LANG
621 from SY_ORGN_MST_TL B, FND_LANGUAGES L
622 where L.INSTALLED_FLAG in ('I', 'B')
623 and B.LANGUAGE = userenv('LANG')
624 and not exists
625 (select NULL
626 from SY_ORGN_MST_TL T
627 where T.ORGN_CODE = B.ORGN_CODE
628 and T.LANGUAGE = L.LANGUAGE_CODE);
629 end ADD_LANGUAGE;
630
631 end SY_ORGN_MST_PKG;
[Home] [Help]