1 PACKAGE BODY OE_PC_RSET_SEL_COLS_PKG AS
2 /* $Header: OEXPCRSB.pls 120.0 2005/06/01 02:24:54 appldev noship $ */
3
4 ---------------------------------------------------
5 PROCEDURE Insert_Row(
6 x_rowid in out NOCOPY /* file.sql.39 change */ varchar2
7 ,x_record_set_id in number
8 ,x_column_name in varchar2
9 ,x_created_by in number
10 ,x_creation_date in date
11 ,x_last_updated_by in number
12 ,x_last_update_date in date
13 ,x_last_update_login in number
14 )
15 Is
16 CURSOR CROWID IS SELECT rowid FROM oe_pc_rset_sel_cols
17 WHERE record_set_id = x_record_set_id
18 AND column_name = x_column_name;
19
20 Begin
21
22 INSERT INTO oe_pc_rset_sel_cols (
23 record_set_id
24 ,column_name
25 ,created_by
26 ,creation_date
27 ,last_updated_by
28 ,last_update_date
29 ,last_update_login
30 )
31 Values
32 (
33 x_record_set_id
34 ,x_column_name
35 ,x_created_by
36 ,x_creation_date
37 ,x_last_updated_by
38 ,x_last_update_date
39 ,x_last_update_login
40 );
41
42 Open CROWID;
43 Fetch CROWID into x_rowid;
44 if (CROWID%NOTFOUND) then
45 CLOSE CROWID;
46 RAISE NO_DATA_FOUND;
47 end if;
48 CLOSE CROWID;
49
50 UPDATE oe_pc_rsets
51 SET
52 last_updated_by = x_last_updated_by
53 ,last_update_date = x_last_update_date
54 ,last_update_login = x_last_update_login
55 WHERE record_set_id = x_record_set_id ;
56
57
58 End Insert_Row;
59 ------------------------------------------
60 PROCEDURE Lock_Row(
61 x_rowid in varchar2
62 ,x_record_set_id in number
63 ,x_column_name in varchar2
64 ,x_created_by in number
65 ,x_creation_date in date
66 ,x_last_updated_by in number
67 ,x_last_update_date in date
68 ,x_last_update_login in number
69 )
70 Is
71 CURSOR C IS
72 SELECT *
73 FROM oe_pc_rset_sel_cols
74 WHERE rowid = x_rowid
75 FOR UPDATE OF column_name NOWAIT;
76
77 Recinfo C%ROWTYPE;
78 Begin
79 Open C;
80 Fetch C into Recinfo;
81 if (C%NOTFOUND) then
82 Close C;
83 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
84 APP_EXCEPTION.Raise_Exception;
85 end if;
86 Close C;
87 if (
88 (Recinfo.record_set_id = x_record_set_id)
89 AND (rtrim(Recinfo.column_name) = x_column_name)
90 AND (Recinfo.created_by = x_created_by)
91 AND (Recinfo.creation_date = x_creation_date)
92 AND (Recinfo.last_updated_by = x_last_updated_by)
93 AND (Recinfo.last_update_date = x_last_update_date)
94 AND ( (Recinfo.last_update_login = x_last_update_login)
95 OR ( (recinfo.last_update_login IS NULL)
96 AND(x_last_update_login IS NULL)))
97 ) then
98 return;
99 else
100 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
101 APP_EXCEPTION.Raise_Exception;
102 end if;
103 End Lock_Row;
104
105 --------------------------------------------------------------------
106 PROCEDURE Update_Row(
107 x_rowid in varchar2
108 ,x_record_set_id in number
109 ,x_column_name in varchar2
110 ,x_created_by in number
111 ,x_creation_date in date
112 ,x_last_updated_by in number
113 ,x_last_update_date in date
114 ,x_last_update_login in number
115 )
116 Is
117 Begin
118 UPDATE oe_pc_rset_sel_cols
119 SET
120 column_name = x_column_name
121 ,last_updated_by = x_last_updated_by
122 ,last_update_date = x_last_update_date
123 ,last_update_login = x_last_update_login
124 WHERE rowid = x_rowid;
125
126 --update the timestamp on the record sets table
127 UPDATE oe_pc_rsets
128 SET
129 last_updated_by = x_last_updated_by
130 ,last_update_date = x_last_update_date
131 ,last_update_login = x_last_update_login
132 WHERE record_set_id = x_record_set_id ;
133
134 End Update_Row;
135 -------------------------------------------------
136
137 PROCEDURE Delete_Row(
138 x_rowid in varchar2
139 )
140 Is
141 Begin
142
143 DELETE FROM oe_pc_rset_sel_cols
144 WHERE rowid = x_rowid;
145 if (SQL%NOTFOUND) then
146 RAISE NO_DATA_FOUND;
147 end if;
148
149 End Delete_Row;
150 -------------------------------------------------------------------
151 PROCEDURE Check_Unique(
152 x_rowid in varchar2
153 ,x_record_Set_id in number
154 ,x_column_name in varchar2
155 )
156 Is
157 dummy number;
158 Begin
159 -- column names name should be unique within a record set
160 SELECT count(1)
161 INTO dummy
162 FROM oe_pc_rset_sel_cols
163 WHERE record_set_id = x_record_set_id
164 AND column_name = x_column_name
165 AND ((x_rowid IS null) OR (rowid <> x_rowid));
166
167 if (dummy >= 1) then
168 fnd_message.set_name('ONT', 'OE_PC_RS_DUP_COLUMN_NAME');
169 app_exception.raise_exception;
170 end if;
171 End Check_Unique;
172 -----------------------------------------------------------------------
173 END OE_PC_RSET_SEL_COLS_PKG;
[Home] [Help]