1 PACKAGE BODY Gmf_Glsynch AS
2 /* $Header: gmfsyncb.pls 115.10 2002/11/11 00:46:04 rseshadr ship $ */
3 /* Package variable will be used in cursor fetches */
4 DummyN NUMBER := 0;
5
6 /* This procedure will insert exceptions into the sy_excp_tbl.
7 The interface_id and co_code will always be -99 and ' ' respectively.*/
8
9 PROCEDURE Write_Exception(
10 pi_table_code in varchar2,
11 pi_key_name in varchar2,
12 pi_message_code in varchar2,
13 pi_col1 in varchar2,
14 pi_col2 in varchar2,
15 pi_col3 in varchar2,
16 pi_col4 in varchar2,
17 pi_col5 in varchar2,
18 pi_key_value in varchar2) IS
19 message_args GMF_MSG_PKG.SubstituteTabTyp;
20 message_id number;
21 message_text varchar2(512);
22 error_status number;
23 BEGIN
24 Gmf_Session_Vars.FOUND_ERRORS := 'Y';
25 <<GL_LOG_TRIGGER_ERROR>>
26 IF Gmf_Session_Vars.GL_LOG_TRIGGER_ERROR = 1 THEN
27 INSERT into sy_excp_tbl(
28 software_code,
29 table_code ,
30 message_code ,
31 exception_date ,
32 col1,
33 col2,
34 col3,
35 col4,
36 col5,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 CREATED_BY,
40 CREATION_DATE,
41 interface_id ,
42 co_code,
43 key_value)
44 VALUES(
45 'ORAFIN',
46 substrb(pi_table_code, 1, 16),
47 substrb(pi_message_code, 1, 32),
48 sysdate,
49 substrb(pi_key_name || ' `' || pi_key_value || '`', 1, 64),
50 substrb(pi_col2, 1, 64),
51 substrb(pi_col3, 1, 64),
52 substrb(pi_col4, 1, 64),
53 substrb(pi_col5, 1, 64),
54 sysdate,
55 Gmf_session_vars.last_updated_by,
56 Gmf_session_vars.last_updated_by,
57 sysdate,
58 -99,
59 decode(pi_table_code, 'Customers', Gmf_Session_Vars.GL_EXCP_CO_CODE, NULL),
60 substrb(pi_key_value, 1, 256));
61 ELSE
62 BEGIN
63 message_args (1) := substrb(pi_key_name || ' ''' || pi_key_value || '''', 1, 64);
64 message_args (2) :=substrb(pi_col2, 1, 64);
65 message_args (3) :=substrb(pi_col3, 1, 64);
66 message_args (4) :=substrb(pi_col4, 1, 64);
67 message_args (5) :=substrb(pi_col5, 1, 64);
68
69 GMF_MSG_PKG.get_msg_from_code(message_id,
70 pi_message_code,
71 message_text,
72 gmf_session_vars.last_updated_by,
73 message_args,
74 error_status);
75
76 Gmf_Session_Vars.ERROR_TEXT := substrb(message_text,1,512);
77
78 EXCEPTION
79 /*
80 When there is no user defined in the GEMMS for the
81 corresponding user of Oracle APPS an exception will be
82 raised. In such case an error will be diaplayed in the
83 default language of ORAF user which exists when
84 integrating Oracle Apps and GEMMS.
85 */
86
87 WHEN others THEN
88 BEGIN
89 GMF_MSG_PKG.get_msg_from_code(message_id,
90 pi_message_code,
91 message_text,
92 'ORAF',
93 message_args,
94 error_status);
95
96 Gmf_Session_Vars.ERROR_TEXT := substrb(message_text,1,512);
97 EXCEPTION
98 When others THEN
99 Gmf_Session_Vars.ERROR_TEXT:=pi_message_code;
100 END;
101 END;
102 END IF; /*GL_LOG_TRIGGER_ERROR*/
103 EXCEPTION
104 WHEN others THEN
105 null;
106 END; /*Write_Exception*/
107
108 /* This procedure will delete exceptions from the sy_excp_tbl. */
109
110 PROCEDURE Delete_Exception(
111 pi_table_code in varchar2,
112 pi_key_value in varchar2) IS
113 BEGIN
114 DELETE FROM sy_excp_tbl
115 WHERE key_value = pi_key_value
116 AND table_code = pi_table_code;
117
118 EXCEPTION
119 WHEN others THEN
120 null;
121 END; /* Delete_Exception */
122
123
124
125 /* This procedure will check if the field_value is NULL.
126 If it is NULL, it logs an error into the sy_excp_tbl. */
127
128 PROCEDURE Check_Required(
129 pi_table_name in varchar2,
130 pi_key_name in varchar2,
131 pi_key_value in varchar2,
132 pi_field_name in varchar2,
133 pi_field_value in varchar2) IS
134 BEGIN
135 IF pi_field_value is NULL THEN
136 write_exception(pi_table_name, pi_key_name,
137 'GL_MISSING_VAL', ' ',
138 pi_field_name, pi_field_value,
139 ' ', ' ', pi_key_value);
140 RAISE Gmf_Session_Vars.ex_error_found;
141 END IF;
142 END; /* Check_Required */
143
144
145 /* This procedure will check if the field_value is
146 in Upper case. If not, it logs an error into the
147 sy_excp_tbl. */
148
149 PROCEDURE Check_Case(
150 pi_table_name in varchar2,
151 pi_key_name in varchar2,
152 pi_key_value in varchar2,
153 pi_field_name in varchar2,
154 pi_field_value in varchar2) IS
155 BEGIN
156 IF pi_field_value <> UPPER(pi_field_value) THEN
157 write_exception(pi_table_name, pi_key_name,
158 'GL_INVALID_CASE', ' ',
159 pi_field_value, pi_field_name,
160 ' ', ' ',pi_key_value);
161 RAISE Gmf_Session_Vars.ex_error_found;
162 END IF;
163 END; /* Check_Case */
164
165
166 /* This procedure will check if the length the field_value
167 is greater than field_length. If yes, it logs an error
168 into the sy_excp_tbl. */
169
170 PROCEDURE Check_Length(
171 pi_table_name in varchar2,
172 pi_key_name in varchar2,
173 pi_key_value in varchar2,
174 pi_field_name in varchar2,
175 pi_field_value in varchar2,
176 pi_field_length in number) IS
177 BEGIN
178 IF (LENGTHB(pi_field_value) > pi_field_length) THEN
179 write_exception(pi_table_name, pi_key_name,
180 'GL_INVALID_LEN', ' ',
181 pi_field_value, pi_field_name,
182 pi_field_length, ' ', pi_key_value);
183 RAISE Gmf_Session_Vars.ex_error_found;
184 END IF;
185 END; /* Check_Length */
186
187
188 /* This function validates that the Currency Codes exists
189 in gl_curr_mst table */
190
191 PROCEDURE Validate_Currency(
192 pi_table_name in varchar2,
193 pi_key_name in varchar2,
194 pi_key_value in varchar2,
195 pi_field_name in varchar2,
196 pi_field_value in varchar2) IS
197 CURSOR C_Val_Currency (v_apps_currency_code in varchar2) IS
198 SELECT 1
199 FROM gl_curr_mst
200 WHERE currency_code = v_apps_currency_code;
201 BEGIN
202 IF pi_field_value IS NOT NULL THEN
203 IF C_Val_Currency%ISOPEN THEN
204 CLOSE C_Val_Currency;
205 END IF;
206
207 OPEN C_Val_Currency (pi_field_value);
208 FETCH C_Val_Currency INTO DummyN;
209 IF C_Val_Currency%NOTFOUND THEN
210 write_exception(pi_table_name, pi_key_name,
211 'GL_INVALID_VAL', ' ',
212 pi_field_value, pi_field_name,
213 ' ', ' ',pi_key_value);
214 IF C_Val_Currency%ISOPEN THEN
215 CLOSE C_Val_Currency;
216 END IF;
217 RAISE Gmf_Session_Vars.ex_error_found;
218 END IF;
219 IF C_Val_Currency%ISOPEN THEN
220 CLOSE C_Val_Currency;
221 END IF;
222 END IF;
223 END; /* Validate_Currency */
224
225
226 /* This procedure will check if the field_value contains
227 multiple delimiters.
228 If yes, it logs an error into the sy_excp_tbl. */
229
230 PROCEDURE Check_Multiple_Delim(
231 pi_table_name in varchar2,
232 pi_key_name in varchar2,
233 pi_key_value in varchar2,
234 pi_field_name in varchar2,
235 pi_field_value in varchar2,
236 pi_delim in varchar2) IS
237 BEGIN
238 IF instr(pi_field_value,pi_delim,instr(pi_field_value, pi_delim)+1) > 0 THEN
239 write_exception(pi_table_name, pi_key_name,
240 'GL_INVALID_DELIM', ' ',
241 pi_field_value, pi_field_name,
242 ' ', ' ',pi_key_value);
243 RAISE Gmf_Session_Vars.ex_error_found;
244 END IF;
245 END; /* Check_Multiple_delimiters */
246
247 /* This function validates that the Rate Type Codes
248 exists in gl_rate_typ table */
249
250 PROCEDURE Validate_Rate_Type(
251 pi_table_name in varchar2,
252 pi_key_name in varchar2,
253 pi_key_value in varchar2,
254 pi_field_name in varchar2,
255 pi_field_value in varchar2) IS
256 CURSOR C_Val_Rate_Type (v_apps_rate_type_code in varchar2) IS
257 SELECT 1
258 FROM gl_rate_typ
259 WHERE rate_type_code = v_apps_rate_type_code;
260 BEGIN
261 IF pi_field_value IS NOT NULL THEN
262 IF C_Val_Rate_Type%ISOPEN THEN
263 CLOSE C_Val_Rate_Type;
264 END IF;
265 OPEN C_Val_Rate_Type (pi_field_value);
266 FETCH C_Val_Rate_Type INTO DummyN;
267 IF C_Val_Rate_Type%NOTFOUND THEN
268 write_exception(pi_table_name, pi_key_name,
269 'GL_INVALID_VAL', ' ',
270 pi_field_value, pi_field_name,
271 ' ', ' ',pi_key_value);
272 RAISE Gmf_Session_Vars.ex_error_found;
273 IF C_Val_Rate_Type%ISOPEN THEN
274 CLOSE C_Val_Rate_Type;
275 END IF;
276 END IF;
277 IF C_Val_Rate_Type%ISOPEN THEN
278 CLOSE C_Val_Rate_Type;
279 END IF;
280 END IF;
281 END; /* Validate_Rate_Type */
282
283 /* This function validates that the Terms Codes
284 exists in op_term_mst table */
285
286 PROCEDURE Validate_Terms_Code(
287 pi_table_name in varchar2,
288 pi_key_name in varchar2,
289 pi_key_value in varchar2,
290 pi_field_name in varchar2,
291 pi_field_value in out NOCOPY varchar2) IS
292 CURSOR C_Val_Terms_Code (v_of_terms_code in varchar2,
293 v_terms_code out varchar2) IS
294 SELECT terms_code
295 FROM op_term_mst
296 WHERE of_terms_code = v_of_terms_code;
297 v_temp_terms_code op_term_mst.terms_code%TYPE;
298 BEGIN
299 IF pi_field_value IS NOT NULL THEN
300 IF C_Val_Terms_Code%ISOPEN THEN
301 CLOSE C_Val_Terms_Code;
302 END IF;
303 OPEN C_Val_Terms_Code (pi_field_value, v_temp_terms_code);
304 FETCH C_Val_Terms_Code INTO v_temp_terms_code;
305 IF C_Val_Terms_Code%NOTFOUND THEN
306 write_exception(pi_table_name, pi_key_name,
307 'GL_INVALID_VAL', ' ',
308 pi_field_value, pi_field_name,
309 ' ', ' ',pi_key_value);
310 IF C_Val_Terms_Code%ISOPEN THEN
311 CLOSE C_Val_Terms_Code;
312 END IF;
313 RAISE Gmf_Session_Vars.ex_error_found;
314 END IF;
315 pi_field_value := v_temp_terms_code;
316 IF C_Val_Terms_Code%ISOPEN THEN
317 CLOSE C_Val_Terms_Code;
318 END IF;
319 END IF;
320 END; /* Validate_Terms_Code */
321
322 /* This function validates that the shipper code
323 exists in op_ship_mst table */
324
325 PROCEDURE Validate_Shipper_Code(
326 pi_table_name in varchar2,
327 pi_key_name in varchar2,
328 pi_key_value in varchar2,
329 pi_field_name in varchar2,
330 pi_field_value in out NOCOPY varchar2) IS
331 CURSOR C_Val_Shipper_Code (v_of_shipper_code in varchar2,
332 v_shipper_code out varchar2) IS
333 SELECT shipper_code
334 FROM op_ship_mst
335 WHERE of_shipper_code = v_of_shipper_code;
336 v_temp_shipper_code op_ship_mst.shipper_code%TYPE;
337 BEGIN
338 IF pi_field_value IS NOT NULL THEN
339 IF C_Val_Shipper_Code%ISOPEN THEN
340 CLOSE C_Val_Shipper_Code;
341 END IF;
342 OPEN C_Val_Shipper_Code (pi_field_value, v_temp_shipper_code);
343 FETCH C_Val_Shipper_Code INTO v_temp_shipper_code;
344 IF C_Val_Shipper_Code%NOTFOUND THEN
345 write_exception(pi_table_name, pi_key_name,
346 'GL_INVALID_VAL', ' ',
347 pi_field_value, pi_field_name,
348 ' ', ' ',pi_key_value);
349 IF C_Val_Shipper_Code%ISOPEN THEN
350 CLOSE C_Val_Shipper_Code;
351 END IF;
352 RAISE Gmf_Session_Vars.ex_error_found;
353 END IF;
354 pi_field_value := v_temp_shipper_code;
355 IF C_Val_Shipper_Code%ISOPEN THEN
356 CLOSE C_Val_Shipper_Code;
357 END IF;
358 END IF;
359 END; /* Validate_Shipper_Code */
360
361 /* This function validates that the FOB Codes
362 exists in op_fobc_mst table */
363
364 PROCEDURE Validate_FOB_Code(
365 pi_table_name in varchar2,
366 pi_key_name in varchar2,
367 pi_key_value in varchar2,
368 pi_field_name in varchar2,
369 pi_field_value in out NOCOPY varchar2) IS
370 CURSOR C_Val_FOB_Code (v_of_fob_code in varchar2,
371 v_fob_code out varchar2) IS
372 SELECT fob_code
373 FROM op_fobc_mst
374 WHERE of_fob_code = v_of_fob_code;
375 v_temp_fob_code op_fobc_mst.fob_code%TYPE;
376 BEGIN
377 IF pi_field_value IS NOT NULL THEN
378 IF C_Val_FOB_Code%ISOPEN THEN
379 CLOSE C_Val_FOB_Code;
380 END IF;
381 OPEN C_Val_FOB_Code (pi_field_value, v_temp_fob_code);
382 FETCH C_Val_FOB_Code INTO v_temp_fob_code;
383 IF C_Val_FOB_Code%NOTFOUND THEN
384 write_exception(pi_table_name, pi_key_name,
385 'GL_INVALID_VAL', ' ',
386 pi_field_value, pi_field_name,
387 ' ', ' ',pi_key_value);
388 IF C_Val_FOB_Code%ISOPEN THEN
389 CLOSE C_Val_FOB_Code;
390 END IF;
391 RAISE Gmf_Session_Vars.ex_error_found;
392 END IF;
393 pi_field_value := v_temp_fob_code;
394 IF C_Val_FOB_Code%ISOPEN THEN
395 CLOSE C_Val_FOB_Code;
396 END IF;
397 END IF;
398 END; /* Validate_FOB_Code */
399
400 /* This function validates that the Frtbill Method
401 exists in op_frgt_mth table */
402
403 PROCEDURE Validate_Frtbill_Mthd(
404 pi_table_name in varchar2,
405 pi_key_name in varchar2,
406 pi_key_value in varchar2,
407 pi_field_name in varchar2,
408 pi_field_value in out NOCOPY varchar2) IS
409 CURSOR C_Val_Frtbill_Mthd (v_of_Frtbill_Mthd in varchar2,
410 v_Frtbill_Mthd out varchar2) IS
411 SELECT Frtbill_Mthd
412 FROM op_frgt_mth
413 WHERE of_Frtbill_Mthd = v_of_Frtbill_Mthd;
414 v_temp_Frtbill_Mthd op_frgt_mth.Frtbill_Mthd%TYPE;
415 BEGIN
416 IF pi_field_value IS NOT NULL THEN
417 IF C_Val_Frtbill_Mthd%ISOPEN THEN
418 CLOSE C_Val_Frtbill_Mthd;
419 END IF;
420 OPEN C_Val_Frtbill_Mthd (pi_field_value, v_temp_Frtbill_Mthd);
421 FETCH C_Val_Frtbill_Mthd INTO v_temp_Frtbill_Mthd;
422 IF C_Val_Frtbill_Mthd%NOTFOUND THEN
423 write_exception(pi_table_name, pi_key_name,
424 'GL_INVALID_VAL', ' ',
425 pi_field_value, pi_field_name,
426 ' ', ' ',pi_key_value);
427 IF C_Val_Frtbill_Mthd%ISOPEN THEN
428 CLOSE C_Val_Frtbill_Mthd;
429 END IF;
430 RAISE Gmf_Session_Vars.ex_error_found;
431 END IF;
432 pi_field_value := v_temp_Frtbill_Mthd;
433 IF C_Val_Frtbill_Mthd%ISOPEN THEN
434 CLOSE C_Val_Frtbill_Mthd;
435 END IF;
436 END IF;
437 END; /* Validate_Frtbill_Mthd */
438
439 /* This function validates that the Rate Type Codes
440 exists in gl_rate_typ table */
441
442 PROCEDURE Validate_Vendgl_Class(
443 pi_table_name in varchar2,
444 pi_key_name in varchar2,
445 pi_key_value in varchar2,
446 pi_field_name in varchar2,
447 pi_field_value in varchar2) IS
448 CURSOR C_Val_Vendgl_Class (v_apps_Vendgl_Class in varchar2) IS
449 SELECT 1
450 FROM po_vgld_cls
451 WHERE Vendgl_Class = v_apps_Vendgl_Class;
452 BEGIN
453 IF pi_field_value IS NOT NULL THEN
454 IF C_Val_Vendgl_Class%ISOPEN THEN
455 CLOSE C_Val_Vendgl_Class;
456 END IF;
457 OPEN C_Val_Vendgl_Class (pi_field_value);
458 FETCH C_Val_Vendgl_Class INTO DummyN;
459 IF C_Val_Vendgl_Class%NOTFOUND THEN
460 write_exception(pi_table_name, pi_key_name,
461 'GL_INVALID_VAL', ' ',
462 pi_field_value, pi_field_name,
463 ' ', ' ',pi_key_value);
464 IF C_Val_Vendgl_Class%ISOPEN THEN
465 CLOSE C_Val_Vendgl_Class;
466 END IF;
467 RAISE Gmf_Session_Vars.ex_error_found;
468 END IF;
469 IF C_Val_Vendgl_Class%ISOPEN THEN
470 CLOSE C_Val_Vendgl_Class;
471 END IF;
472 END IF;
473 END; /* Validate_Vendgl_Class */
474
475 /* This function validates that the Slsrep Code
476 exists in op_slsr_mst table */
477
478 PROCEDURE Validate_Slsrep_Code(
479 pi_table_name in varchar2,
480 pi_key_name in varchar2,
481 pi_key_value in varchar2,
482 pi_field_name in varchar2,
483 pi_field_value in varchar2) IS
484 CURSOR C_Val_Slsrep_Code (v_apps_Slsrep_Code in varchar2) IS
485 SELECT 1
486 FROM op_slsr_mst
487 WHERE Slsrep_Code = v_apps_Slsrep_Code;
488 BEGIN
489 IF pi_field_value IS NOT NULL THEN
490 IF C_Val_Slsrep_Code%ISOPEN THEN
491 CLOSE C_Val_Slsrep_Code;
492 END IF;
493 OPEN C_Val_Slsrep_Code (pi_field_value);
494 FETCH C_Val_Slsrep_Code INTO DummyN;
495 IF C_Val_Slsrep_Code%NOTFOUND THEN
496 write_exception(pi_table_name, pi_key_name,
497 'GL_INVALID_VAL', ' ',
498 pi_field_value, pi_field_name,
499 ' ', ' ',pi_key_value);
500 IF C_Val_Slsrep_Code%ISOPEN THEN
501 CLOSE C_Val_Slsrep_Code;
502 END IF;
503 RAISE Gmf_Session_Vars.ex_error_found;
504 END IF;
505 IF C_Val_Slsrep_Code%ISOPEN THEN
506 CLOSE C_Val_Slsrep_Code;
507 END IF;
508 END IF;
509 END; /* Validate_Slsrep_Code */
510
511 /* This function validates that the cust_class
512 exists in op_cust_cls table */
513
514 PROCEDURE Validate_Cust_Class(
515 pi_table_name in varchar2,
516 pi_key_name in varchar2,
517 pi_key_value in varchar2,
518 pi_field_name in varchar2,
519 pi_field_value in varchar2) IS
520 CURSOR C_Val_Cust_Class (v_apps_Cust_Class in varchar2) IS
521 SELECT 1
522 FROM op_cust_cls
523 WHERE Cust_Class = v_apps_Cust_Class;
524 BEGIN
525 IF pi_field_value IS NOT NULL THEN
526 IF C_Val_Cust_Class%ISOPEN THEN
527 CLOSE C_Val_Cust_Class;
528 END IF;
529 OPEN C_Val_Cust_Class (pi_field_value);
530 FETCH C_Val_Cust_Class INTO DummyN;
531 IF C_Val_Cust_Class%NOTFOUND THEN
532 write_exception(pi_table_name, pi_key_name,
533 'GL_INVALID_VAL', ' ',
534 pi_field_value, pi_field_name,
535 ' ', ' ',pi_key_value);
536 IF C_Val_Cust_Class%ISOPEN THEN
537 CLOSE C_Val_Cust_Class;
538 END IF;
539 RAISE Gmf_Session_Vars.ex_error_found;
540 END IF;
541 IF C_Val_Cust_Class%ISOPEN THEN
542 CLOSE C_Val_Cust_Class;
543 END IF;
544 END IF;
545 END; /* Validate_Cust_Class */
546
547 /* This function validates that the Custgl Class
548 exists in op_cgld_cls table */
549
550 PROCEDURE Validate_Custgl_Class(
551 pi_table_name in varchar2,
552 pi_key_name in varchar2,
553 pi_key_value in varchar2,
554 pi_field_name in varchar2,
555 pi_field_value in varchar2) IS
556 CURSOR C_Val_Custgl_Class (v_apps_Custgl_Class in varchar2) IS
557 SELECT 1
558 FROM op_cgld_cls
559 WHERE Custgl_Class = v_apps_Custgl_Class;
560 BEGIN
561 IF pi_field_value IS NOT NULL THEN
562 IF C_Val_Custgl_Class%ISOPEN THEN
563 CLOSE C_Val_Custgl_Class;
564 END IF;
565 OPEN C_Val_Custgl_Class (pi_field_value);
566 FETCH C_Val_Custgl_Class INTO DummyN;
567 IF C_Val_Custgl_Class%NOTFOUND THEN
568 write_exception(pi_table_name, pi_key_name,
569 'GL_INVALID_VAL', ' ',
570 pi_field_value, pi_field_name,
571 ' ', ' ',pi_key_value);
572 IF C_Val_Custgl_Class%ISOPEN THEN
573 CLOSE C_Val_Custgl_Class;
574 END IF;
575 RAISE Gmf_Session_Vars.ex_error_found;
576 END IF;
577 IF C_Val_Custgl_Class%ISOPEN THEN
578 CLOSE C_Val_Custgl_Class;
579 END IF;
580 END IF;
581 END; /* Validate_Custgl_Class */
582
583 /* This function validates that the custprice_class
584 exists in op_cprc_cls table */
585
586 PROCEDURE Validate_Custprice_Class(
587 pi_table_name in varchar2,
588 pi_key_name in varchar2,
589 pi_key_value in varchar2,
590 pi_field_name in varchar2,
591 pi_field_value in varchar2) IS
592 CURSOR C_Val_Custprice_Class (v_apps_Custprice_Class in varchar2) IS
593 SELECT 1
594 FROM op_cprc_cls
595 WHERE Custprice_Class = v_apps_Custprice_Class;
596 BEGIN
597 IF pi_field_value IS NOT NULL THEN
598 IF C_Val_Custprice_Class%ISOPEN THEN
599 CLOSE C_Val_Custprice_Class;
600 END IF;
601 OPEN C_Val_Custprice_Class (pi_field_value);
602 FETCH C_Val_Custprice_Class INTO DummyN;
603 IF C_Val_Custprice_Class%NOTFOUND THEN
604 write_exception(pi_table_name, pi_key_name,
605 'GL_INVALID_VAL', ' ',
606 pi_field_value, pi_field_name,
607 ' ', ' ',pi_key_value);
608 IF C_Val_Custprice_Class%ISOPEN THEN
609 CLOSE C_Val_Custprice_Class;
610 END IF;
611 RAISE Gmf_Session_Vars.ex_error_found;
612 END IF;
613 IF C_Val_Custprice_Class%ISOPEN THEN
614 CLOSE C_Val_Custprice_Class;
615 END IF;
616 END IF;
617 END; /* Validate_Custprice_Class */
618
619 /* This function validates that the taxloc_code
620 exists in tx_tloc_cds table */
621
622 PROCEDURE Validate_Taxloc_Code(
623 pi_table_name in varchar2,
624 pi_key_name in varchar2,
625 pi_key_value in varchar2,
626 pi_field_name in varchar2,
627 pi_field_value in varchar2) IS
628 CURSOR C_Val_Taxloc_Code (v_apps_Taxloc_Code in varchar2) IS
629 SELECT 1
630 FROM tx_tloc_cds
631 WHERE Taxloc_Code = v_apps_Taxloc_Code;
632 BEGIN
633 IF pi_field_value IS NOT NULL THEN
634 IF C_Val_Taxloc_Code%ISOPEN THEN
635 CLOSE C_Val_Taxloc_Code;
636 END IF;
637 OPEN C_Val_Taxloc_Code (pi_field_value);
638 FETCH C_Val_Taxloc_Code INTO DummyN;
639 IF C_Val_Taxloc_Code%NOTFOUND THEN
640 write_exception(pi_table_name, pi_key_name,
641 'GL_INVALID_VAL', ' ',
642 pi_field_value, pi_field_name,
643 ' ', ' ',pi_key_value);
644 IF C_Val_Taxloc_Code%ISOPEN THEN
645 CLOSE C_Val_Taxloc_Code;
646 END IF;
647 RAISE Gmf_Session_Vars.ex_error_found;
648 END IF;
649 IF C_Val_Taxloc_Code%ISOPEN THEN
650 CLOSE C_Val_Taxloc_Code;
651 END IF;
652 END IF;
653 END; /* Validate_Taxloc_Code */
654
655 /* This function validates that the taxcalc_code
656 exists in tx_calc_mst table */
657
658 PROCEDURE Validate_Taxcalc_Code(
659 pi_table_name in varchar2,
660 pi_key_name in varchar2,
661 pi_key_value in varchar2,
662 pi_field_name in varchar2,
663 pi_field_value in varchar2) IS
664 CURSOR C_Val_Taxcalc_Code (v_apps_Taxcalc_Code in varchar2) IS
665 SELECT 1
666 FROM tx_calc_mst
667 WHERE Taxcalc_Code = v_apps_Taxcalc_Code;
668 BEGIN
669 IF pi_field_value IS NOT NULL THEN
670 IF C_Val_Taxcalc_Code%ISOPEN THEN
671 CLOSE C_Val_Taxcalc_Code;
672 END IF;
673 OPEN C_Val_Taxcalc_Code (pi_field_value);
674 FETCH C_Val_Taxcalc_Code INTO DummyN;
675 IF C_Val_Taxcalc_Code%NOTFOUND THEN
676 write_exception(pi_table_name, pi_key_name,
677 'GL_INVALID_VAL', ' ',
678 pi_field_value, pi_field_name,
679 ' ', ' ',pi_key_value);
680 IF C_Val_Taxcalc_Code%ISOPEN THEN
681 CLOSE C_Val_Taxcalc_Code;
682 END IF;
683 RAISE Gmf_Session_Vars.ex_error_found;
684 END IF;
685 IF C_Val_Taxcalc_Code%ISOPEN THEN
686 CLOSE C_Val_Taxcalc_Code;
687 END IF;
688 END IF;
689 END; /* Validate_Taxcalc_Code */
690
691 /* This function validates that the taxcalc_code
692 exists in ic_whse_mst table */
693
694 PROCEDURE Validate_Whse_Code(
695 pi_table_name in varchar2,
696 pi_key_name in varchar2,
697 pi_key_value in varchar2,
698 pi_field_name in varchar2,
699 pi_field_value in varchar2) IS
700 CURSOR C_Val_Whse_Code (v_apps_Whse_Code in varchar2) IS
701 SELECT 1
702 FROM ic_whse_mst
703 WHERE Whse_Code = v_apps_Whse_Code;
704 BEGIN
705 IF pi_field_value IS NOT NULL THEN
706 IF C_Val_Whse_Code%ISOPEN THEN
707 CLOSE C_Val_Whse_Code;
708 END IF;
709 OPEN C_Val_Whse_Code (pi_field_value);
710 FETCH C_Val_Whse_Code INTO DummyN;
711 IF C_Val_Whse_Code%NOTFOUND THEN
712 write_exception(pi_table_name, pi_key_name,
713 'GL_INVALID_VAL', ' ',
714 pi_field_value, pi_field_name,
715 ' ', ' ',pi_key_value);
716 IF C_Val_Whse_Code%ISOPEN THEN
717 CLOSE C_Val_Whse_Code;
718 END IF;
719 RAISE Gmf_Session_Vars.ex_error_found;
720 END IF;
721 IF C_Val_Whse_Code%ISOPEN THEN
722 CLOSE C_Val_Whse_Code;
723 END IF;
724 END IF;
725 END; /* Validate_Whse_Code */
726
727 PROCEDURE Save_Addr(
728 pi_addr_id in out NOCOPY number,
729 pi_addr1 in varchar2,
730 pi_addr2 in varchar2,
731 pi_addr3 in varchar2,
732 pi_addr4 in varchar2,
733 pi_ora_addr4 in varchar2,
734 pi_province in varchar2,
735 pi_county in varchar2,
736 pi_state_code in varchar2,
737 pi_country_code in varchar2,
738 pi_postal_code in varchar2,
739 pi_pseudo_key in varchar2,
740 pi_date_modified in date,
741 pi_modified_by in varchar2,
742 pi_date_added in varchar2,
743 pi_added_by in varchar2) IS
744 BEGIN
745 IF pi_addr_id <> 0 and pi_addr_id is not null THEN
746 UPDATE sy_addr_mst
747 SET
748 addr1 = nvl(pi_addr1,' '),
749 addr2 = nvl(pi_addr2,' '),
750 addr3 = nvl(pi_addr3,' '),
751 addr4 = nvl(pi_addr4,' '),
752 ora_addr4 = nvl(pi_ora_addr4,' '),
753 province = nvl(pi_province,' '),
754 county = nvl(pi_county,' '),
755 state_code = nvl(pi_state_code,' '),
756 country_code = nvl(pi_country_code,' '),
757 postal_code = nvl(pi_postal_code,' '),
758 pseudo_key = pi_pseudo_key,
759 last_update_date = nvl(pi_date_modified, to_date(2440588,'J')),
760 last_updated_by = nvl(pi_modified_by,0),
761 creation_date = nvl(pi_date_added, to_date(2440588,'J')),
762 created_by = nvl(pi_added_by,0)
763 WHERE
764 addr_id = pi_addr_id;
765 END IF;
766
767 IF SQL%NOTFOUND OR pi_addr_id = 0 or pi_addr_id is NULL THEN
768 SELECT GEM5_address_id_s.nextval INTO pi_addr_id FROM dual;
769 INSERT into sy_addr_mst(
770 addr_id,
771 addr1,
772 addr2,
773 addr3,
774 addr4,
775 ora_addr4,
776 province,
777 county,
778 state_code,
779 country_code,
780 postal_code,
781 pseudo_key,
782 last_update_date,
783 last_updated_by,
784 creation_date,
785 created_by,
786 comments,
787 delete_mark,
788 trans_cnt)
789 values(
790 pi_addr_id,
791 nvl(pi_addr1, ' '),
792 nvl(pi_addr2, ' '),
793 nvl(pi_addr3, ' '),
794 nvl(pi_addr4, ' '),
795 nvl(pi_ora_addr4, ' '),
796 nvl(pi_province, ' '),
797 nvl(pi_county, ' '),
798 nvl(pi_state_code, ' '),
799 nvl(pi_country_code, ' '),
800 nvl(pi_postal_code, ' '),
801 pi_pseudo_key,
802 nvl(pi_date_modified, to_date(2440588,'J')),
803 nvl(pi_modified_by,0),
804 nvl(pi_date_added, to_date(2440588,'J')),
805 nvl(pi_added_by,0),
806 ' ',
807 0,
808 0);
809 END IF;
810 /* Insert the state_code if it is not already there in sy_geog_mst */
811 IF pi_state_code is not NULL THEN
812 INSERT into SY_GEOG_MST(
813 geog_type,
814 geog_code,
815 geog_desc,
816 delete_mark,
817 trans_cnt,
818 creation_date,
819 created_by,
820 last_update_date,
821 last_updated_by)
822 SELECT
823 2,
824 pi_state_code,
825 pi_state_code,
826 0,
827 0,
828 nvl(pi_date_added, to_date(2440588,'J')),
829 nvl(pi_added_by,0),
830 nvl(pi_date_modified, to_date(2440588,'J')),
831 nvl(pi_modified_by,0)
832 FROM SYS.DUAL
833 WHERE not exists(
834 SELECT 1 FROM SY_GEOG_MST
835 WHERE geog_type = 2 and geog_code = pi_state_code);
836 END IF;
837 /* Insert the Country Code if it is not already there in sy_geog_mst */
838 IF pi_country_code is not NULL THEN
839 INSERT into SY_GEOG_MST(
840 geog_type,
841 geog_code,
842 geog_desc,
843 delete_mark,
844 trans_cnt,
845 creation_date,
846 created_by,
847 last_update_date,
848 last_updated_by)
849 SELECT
850 1,
851 pi_country_code,
852 territory_short_name,
853 0,
854 0,
855 nvl(pi_date_added, to_date(2440588,'J')),
856 nvl(pi_added_by,0),
857 nvl(pi_date_modified, to_date(2440588,'J')),
858 nvl(pi_modified_by,0)
859 FROM fnd_territories_vl
860 WHERE territory_code = pi_country_code and
861 not exists(
862 SELECT 1 FROM SY_GEOG_MST
863 WHERE geog_type = 1 and geog_code = pi_country_code);
864 END IF;
865 END; /* Save_Adddr */
866
867
868 FUNCTION Validate_Terms_Code(pi_field_value in varchar2) return boolean IS
869 CURSOR C_Val_Terms_Code (v_of_terms_code in varchar2) IS
870 SELECT 1
871 FROM op_term_mst
872 WHERE of_terms_code = v_of_terms_code;
873 BEGIN
874 IF pi_field_value IS NOT NULL THEN
875 IF C_Val_Terms_Code%ISOPEN THEN
876 CLOSE C_Val_Terms_Code;
877 END IF;
878 OPEN C_Val_Terms_Code (pi_field_value);
879 FETCH C_Val_Terms_Code INTO DummyN;
880 IF C_Val_Terms_Code%NOTFOUND THEN
881 IF C_Val_Terms_Code%ISOPEN THEN
882 CLOSE C_Val_Terms_Code;
883 END IF;
884 return FALSE;
885 END IF;
886 IF C_Val_Terms_Code%ISOPEN THEN
887 CLOSE C_Val_Terms_Code;
888 END IF;
889 return TRUE;
890 END IF;
891 return TRUE;
892 END; /* Validate_Terms_Code */
893
894
895 FUNCTION Validate_Shipper_Code(pi_field_value varchar2) return boolean IS
896 CURSOR C_Val_Shipper_Code (v_of_shipper_code in varchar2) IS
897 SELECT 1
898 FROM op_ship_mst
899 WHERE of_shipper_code = v_of_shipper_code;
900 BEGIN
901 IF pi_field_value IS NOT NULL THEN
902 IF C_Val_Shipper_Code%ISOPEN THEN
903 CLOSE C_Val_Shipper_Code;
904 END IF;
905 OPEN C_Val_Shipper_Code (pi_field_value);
906 FETCH C_Val_Shipper_Code INTO DummyN;
907 IF C_Val_Shipper_Code%NOTFOUND THEN
908 IF C_Val_Shipper_Code%ISOPEN THEN
909 CLOSE C_Val_Shipper_Code;
910 END IF;
911 return FALSE;
912 END IF;
913 IF C_Val_Shipper_Code%ISOPEN THEN
914 CLOSE C_Val_Shipper_Code;
915 END IF;
916 return TRUE;
917 END IF;
918 return TRUE;
919 END; /* Validate_Shipper_Code */
920
921
922 FUNCTION Validate_FOB_Code(pi_field_value varchar2) return boolean IS
923 CURSOR C_Val_FOB_Code (v_of_fob_code in varchar2) IS
924 SELECT 1
925 FROM op_fobc_mst
926 WHERE of_fob_code = v_of_fob_code;
927 BEGIN
928 IF pi_field_value IS NOT NULL THEN
929 IF C_Val_FOB_Code%ISOPEN THEN
930 CLOSE C_Val_FOB_Code;
931 END IF;
932 OPEN C_Val_FOB_Code (pi_field_value);
933 FETCH C_Val_FOB_Code INTO DummyN;
934 IF C_Val_FOB_Code%NOTFOUND THEN
935 IF C_Val_FOB_Code%ISOPEN THEN
936 CLOSE C_Val_FOB_Code;
937 END IF;
938 return FALSE;
939 END IF;
940 IF C_Val_FOB_Code%ISOPEN THEN
941 CLOSE C_Val_FOB_Code;
942 END IF;
943 return TRUE;
944 END IF;
945 return TRUE;
946 END; /* Validate_FOB_Code */
947
948
949 FUNCTION Validate_Slsrep_Code(pi_field_value varchar2) return boolean IS
950 CURSOR C_Val_Slsrep_Code (v_apps_Slsrep_Code in varchar2) IS
951 SELECT 1
952 FROM op_slsr_mst
953 WHERE Slsrep_Code = v_apps_Slsrep_Code;
954 BEGIN
955 IF pi_field_value IS NOT NULL THEN
956 IF C_Val_Slsrep_Code%ISOPEN THEN
957 CLOSE C_Val_Slsrep_Code;
958 END IF;
959 OPEN C_Val_Slsrep_Code (pi_field_value);
960 FETCH C_Val_Slsrep_Code INTO DummyN;
961 IF C_Val_Slsrep_Code%NOTFOUND THEN
962 IF C_Val_Slsrep_Code%ISOPEN THEN
963 CLOSE C_Val_Slsrep_Code;
964 END IF;
965 return FALSE;
966 END IF;
967 IF C_Val_Slsrep_Code%ISOPEN THEN
968 CLOSE C_Val_Slsrep_Code;
969 END IF;
970 return TRUE;
971 END IF;
972 return TRUE;
973 END; /* Validate_Slsrep_Code */
974
975
976 END; /* Gmf_Glsynch package body */
[Home] [Help]