1 PACKAGE BODY XLE_Histories_PKG AS
2 /* $Header: xlehistb.pls 120.1 2005/07/26 17:10:23 shijain ship $ */
3
4 PROCEDURE Insert_Row(
5 x_history_id IN OUT NOCOPY NUMBER,
6 p_source_table IN VARCHAR2 DEFAULT NULL,
7 p_source_id IN NUMBER DEFAULT NULL,
8 p_source_column_name IN VARCHAR2 DEFAULT NULL,
9 p_source_column_value IN VARCHAR2 DEFAULT NULL,
10 p_effective_from IN DATE DEFAULT NULL,
11 p_effective_to IN DATE DEFAULT NULL,
12 p_comment IN VARCHAR2 DEFAULT NULL,
13 p_last_update_date IN DATE DEFAULT NULL,
14 p_last_updated_by IN NUMBER DEFAULT NULL,
15 p_creation_date IN DATE DEFAULT NULL,
16 p_created_by IN NUMBER DEFAULT NULL,
17 p_last_update_login IN NUMBER DEFAULT NULL,
18 p_object_version_number IN NUMBER
19 ) IS
20 BEGIN
21 INSERT INTO xle_histories (
22 history_id,
23 source_table,
24 source_id,
25 source_column_name,
26 source_column_value,
27 effective_from,
28 effective_to,
29 comments,
30 last_update_date,
31 last_updated_by,
32 creation_date,
33 created_by,
34 last_update_login,
35 object_version_number
36 ) VALUES (
37 xle_histories_s.NEXTVAL,
38 DECODE(p_source_table, FND_API.G_MISS_CHAR, NULL, p_source_table),
39 DECODE(p_source_id, FND_API.G_MISS_NUM, NULL, p_source_id),
40 DECODE(p_source_column_name, FND_API.G_MISS_CHAR, NULL, p_source_column_name),
41 DECODE(p_source_column_value, FND_API.G_MISS_CHAR, NULL, p_source_column_value),
42 DECODE(p_effective_from, NULL, XLE_UTILITY_PUB.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_effective_from),
43 DECODE(p_effective_to, FND_API.G_MISS_DATE, NULL, p_effective_to),
44 DECODE(p_comment, FND_API.G_MISS_CHAR, NULL, p_comment),
45 XLE_UTILITY_PUB.LAST_UPDATE_DATE,
46 XLE_UTILITY_PUB.LAST_UPDATED_BY,
47 XLE_UTILITY_PUB.CREATION_DATE,
48 XLE_UTILITY_PUB.CREATED_BY,
49 XLE_UTILITY_PUB.LAST_UPDATE_LOGIN,
50 DECODE(p_object_version_number, FND_API.G_MISS_NUM, NULL, p_object_version_number)
51 ) RETURNING
52 history_id
53 INTO
54 x_history_id;
55 END Insert_Row;
56
57 PROCEDURE Update_Row(
58 p_history_id IN NUMBER,
59 p_source_table IN VARCHAR2 DEFAULT NULL,
60 p_source_id IN NUMBER DEFAULT NULL,
61 p_source_column_name IN VARCHAR2 DEFAULT NULL,
62 p_source_column_value IN VARCHAR2 DEFAULT NULL,
63 p_effective_from IN DATE DEFAULT NULL,
64 p_effective_to IN DATE DEFAULT NULL,
65 p_comment IN VARCHAR2 DEFAULT NULL,
66 p_last_update_date IN DATE DEFAULT NULL,
67 p_last_updated_by IN NUMBER DEFAULT NULL,
68 p_last_update_login IN NUMBER DEFAULT NULL,
69 p_object_version_number IN NUMBER
70 ) IS
71 BEGIN
72 UPDATE xle_histories SET
73 source_table = DECODE(p_source_table, NULL, source_table, FND_API.G_MISS_CHAR, NULL, p_source_table),
74 source_id = DECODE(p_source_id, NULL, source_id, FND_API.G_MISS_NUM, NULL, p_source_id),
75 source_column_name = DECODE(p_source_column_name, NULL, source_column_name, FND_API.G_MISS_CHAR, NULL, p_source_column_name),
76 source_column_value = DECODE(p_source_column_value, NULL, source_column_value, FND_API.G_MISS_CHAR, NULL, p_source_column_value),
77 effective_from = DECODE(p_effective_from, NULL, effective_from, FND_API.G_MISS_DATE, NULL, p_effective_from),
78 effective_to = DECODE(p_effective_to, NULL, effective_to, FND_API.G_MISS_DATE, NULL, p_effective_to),
79 comments = DECODE(p_comment, NULL, comments, FND_API.G_MISS_CHAR, NULL, p_comment),
80 last_update_date = XLE_UTILITY_PUB.LAST_UPDATE_DATE,
81 last_updated_by = XLE_UTILITY_PUB.LAST_UPDATED_BY,
82 last_update_login = XLE_UTILITY_PUB.LAST_UPDATE_LOGIN,
83 object_version_number= DECODE(p_object_version_number, NULL, object_version_number, FND_API.G_MISS_NUM, NULL, p_object_version_number)
84 WHERE history_id = p_history_id;
85
86 IF (sql%notfound) THEN
87 RAISE no_data_found;
88 END IF;
89 END Update_Row;
90
91 PROCEDURE Delete_Row(p_history_id IN NUMBER) IS
92 BEGIN
93 DELETE FROM xle_histories
94 WHERE history_id = p_history_id;
95
96 IF (sql%notfound) THEN
97 RAISE no_data_found;
98 END IF;
99 END Delete_Row;
100
101 PROCEDURE Lock_Row(
102 p_history_id IN NUMBER,
103 p_source_table IN VARCHAR2 DEFAULT NULL,
104 p_source_id IN NUMBER DEFAULT NULL,
105 p_source_column_name IN VARCHAR2 DEFAULT NULL,
106 p_source_column_value IN VARCHAR2 DEFAULT NULL,
107 p_effective_from IN DATE DEFAULT NULL,
108 p_effective_to IN DATE DEFAULT NULL,
109 p_comment IN VARCHAR2 DEFAULT NULL,
110 p_last_update_date IN DATE DEFAULT NULL,
111 p_last_updated_by IN NUMBER DEFAULT NULL,
112 p_creation_date IN DATE DEFAULT NULL,
113 p_created_by IN NUMBER DEFAULT NULL,
114 p_last_update_login IN NUMBER DEFAULT NULL,
115 p_object_version_number IN NUMBER
116 ) IS
117 CURSOR C IS
118 SELECT * FROM xle_histories
119 WHERE history_id = p_history_id
120 FOR UPDATE OF history_id NOWAIT;
121 Recinfo C%ROWTYPE;
122 BEGIN
123 OPEN C;
124 FETCH C INTO Recinfo;
125 IF (C%NOTFOUND) THEN
126 CLOSE C;
127 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
128 APP_EXCEPTION.Raise_Exception;
129 END IF;
130 CLOSE C;
131
132 IF (
133 (Recinfo.history_id = p_history_id)
134 AND ( (Recinfo.source_table = p_source_table)
135 OR ( (Recinfo.source_table IS NULL)
136 AND (p_source_table IS NULL)))
137 AND ( (Recinfo.source_id= p_source_id)
138 OR ( (Recinfo.source_id IS NULL)
139 AND (p_source_id IS NULL)))
140 AND ( (Recinfo.source_column_name = p_source_column_name)
141 OR ( (Recinfo.source_column_name IS NULL)
142 AND (p_source_column_name IS NULL)))
143 AND ( (Recinfo.source_column_value = p_source_column_value)
144 OR ( (Recinfo.source_column_value IS NULL)
145 AND (p_source_column_value IS NULL)))
146 AND ( (Recinfo.effective_from = p_effective_from)
147 OR ( (Recinfo.effective_from IS NULL)
148 AND (p_effective_from IS NULL)))
149 AND ( (Recinfo.effective_to = p_effective_to)
150 OR ( (Recinfo.effective_to IS NULL)
151 AND (p_effective_to IS NULL)))
152 AND ( (Recinfo.comments = p_comment)
153 OR ( (Recinfo.comments IS NULL)
154 AND (p_comment IS NULL)))
155 AND ( (Recinfo.last_update_date = p_last_update_date)
156 OR ( (Recinfo.last_update_date IS NULL)
157 AND (p_last_update_date IS NULL)))
158 AND ( (Recinfo.last_updated_by = p_last_updated_by)
159 OR ( (Recinfo.last_updated_by IS NULL)
160 AND (p_last_updated_by IS NULL)))
161 AND ( (Recinfo.creation_date = p_creation_date)
162 OR ( (Recinfo.creation_date IS NULL)
163 AND (p_creation_date IS NULL)))
164 AND ( (Recinfo.created_by = p_created_by)
165 OR ( (Recinfo.created_by IS NULL)
166 AND (p_created_by IS NULL)))
167 AND ( (Recinfo.last_update_login = p_last_update_login)
168 OR ( (Recinfo.last_update_login IS NULL)
169 AND (p_last_update_login IS NULL)))
170 AND ( (Recinfo.object_version_number = p_object_version_number)
171 OR ( (Recinfo.object_version_number IS NULL)
172 AND (p_object_version_number IS NULL)))
173 ) THEN
174 RETURN;
175 ELSE
176 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
177 APP_EXCEPTION.Raise_Exception;
178 END IF;
179 END Lock_Row;
180
181 END XLE_Histories_PKG;
182
[Home] [Help]