1 package body PER_JP_SCHOOL_LOOKUPS_PKG as
2 /* $Header: pejpschl.pkb 115.6 99/07/18 13:59:12 porting ship $ */
3 PROCEDURE insert_row(
4 p_rowid OUT VARCHAR2,
5 p_school_id IN VARCHAR2,
6 p_school_name IN VARCHAR2,
7 p_school_name_kana IN VARCHAR2,
8 p_major IN VARCHAR2,
9 p_major_kana IN VARCHAR2,
10 p_created_by IN NUMBER,
11 p_creation_date IN DATE,
12 p_last_updated_by IN NUMBER,
13 p_last_update_date IN DATE,
14 p_last_update_login IN NUMBER)
15 IS
16 l_rowid ROWID;
17 CURSOR csr_school_rowid IS
18 select rowid
19 from per_jp_school_lookups
20 where school_id=p_school_id;
21 BEGIN
22 insert into per_jp_school_lookups(
23 SCHOOL_ID,
24 SCHOOL_NAME,
25 SCHOOL_NAME_KANA,
26 MAJOR,
27 MAJOR_KANA,
28 CREATED_BY,
29 CREATION_DATE,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_DATE,
32 LAST_UPDATE_LOGIN)
33 values( p_school_id,
34 p_school_name,
35 p_school_name_kana,
36 p_major,
37 p_major_kana,
38 p_created_by,
39 p_creation_date,
40 p_last_updated_by,
41 p_last_update_date,
42 p_last_update_Login);
43
44 open csr_school_rowid;
45 fetch csr_school_rowid into l_rowid;
46 close csr_school_rowid;
47
48 p_rowid := rowidtochar(l_rowid);
49 END;
50 --
51 PROCEDURE lock_row(
52 p_rowid IN VARCHAR2,
53 p_school_id IN VARCHAR2,
54 p_school_name IN VARCHAR2,
55 p_school_name_kana IN VARCHAR2,
56 p_major IN VARCHAR2,
57 p_major_kana IN VARCHAR2)
58 IS
59 CURSOR csr_school IS
60 select *
61 from per_jp_school_lookups
62 where rowid=chartorowid(p_rowid)
63 for update of school_id nowait;
64 l_rec csr_school%ROWTYPE;
65 BEGIN
66 open csr_school;
67 fetch csr_school into l_rec;
68 if csr_school%NOTFOUND then
69 close csr_school;
70 fnd_message.set_name('FND','FORM_RECORD_DELETED');
71 fnd_message.raise_error;
72 end if;
73 close csr_school;
74
75 if (l_rec.school_id = p_school_id)
76 and ((l_rec.school_name = p_school_name) or (l_rec.school_name is NULL and p_school_name is NULL))
77 and ((l_rec.school_name_kana = p_school_name_kana) or (l_rec.school_name_kana is NULL and p_school_name_kana is NULL))
78 and ((l_rec.major = p_major) or (l_rec.major is NULL and p_major is NULL))
79 and ((l_rec.major_kana = p_major_kana) or (l_rec.major_kana is NULL and p_major_kana is NULL)) then
80 NULL;
81 else
82 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
83 fnd_message.raise_error;
84 end if;
85 END;
86 --
87 PROCEDURE update_row(
88 p_rowid IN VARCHAR2,
89 p_school_id IN VARCHAR2,
90 p_school_name IN VARCHAR2,
91 p_school_name_kana IN VARCHAR2,
92 p_major IN VARCHAR2,
93 p_major_kana IN VARCHAR2,
94 p_created_by IN NUMBER,
95 p_creation_date IN DATE,
96 p_last_updated_by IN NUMBER,
97 p_last_update_date IN DATE,
98 p_last_update_login IN NUMBER)
99 IS
100 BEGIN
101 update per_jp_school_lookups
102 set school_id = p_school_id,
103 school_name = p_school_name,
104 school_name_kana = p_school_name_kana,
105 major = p_major,
106 major_kana = p_major_kana,
107 created_by = p_created_by,
108 creation_date = p_creation_date,
109 last_updated_by = p_last_updated_by,
110 last_update_date = p_last_update_date,
111 last_update_login = p_last_update_login
112 where rowid=chartorowid(p_rowid);
113 END;
114 end;