1 PACKAGE BODY CSI_COUNTER_READING_LOCK_PKG as
2 /* $Header: csitcrlb.pls 120.2 2006/02/06 14:42:28 epajaril noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_COUNTER_READING_LOCK_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csitcrlb.pls';
6
7 PROCEDURE Insert_Row(
8 px_READING_LOCK_ID IN OUT NOCOPY NUMBER
9 ,p_COUNTER_ID NUMBER
10 ,p_READING_LOCK_DATE DATE
11 ,p_OBJECT_VERSION_NUMBER NUMBER
12 ,p_LAST_UPDATE_DATE DATE
13 ,p_LAST_UPDATED_BY NUMBER
14 ,p_CREATION_DATE DATE
15 ,p_CREATED_BY NUMBER
16 ,p_LAST_UPDATE_LOGIN NUMBER
17 ,p_SOURCE_GROUP_REF_ID NUMBER
18 ,p_SOURCE_GROUP_REF VARCHAR2
19 ,p_SOURCE_HEADER_REF_ID NUMBER
20 ,p_SOURCE_HEADER_REF VARCHAR2
21 ,p_SOURCE_LINE_REF_ID NUMBER
22 ,p_SOURCE_LINE_REF VARCHAR2
23 ,p_SOURCE_DIST_REF_ID1 NUMBER
24 ,p_SOURCE_DIST_REF_ID2 NUMBER
25 ) IS
26
27 CURSOR C1 IS
28 SELECT CSI_CTR_READING_LOCKS_S.nextval
29 FROM dual;
30 BEGIN
31 IF (px_READING_LOCK_ID IS NULL) OR (px_READING_LOCK_ID = FND_API.G_MISS_NUM) THEN
32 OPEN C1;
33 FETCH C1 INTO px_READING_LOCK_ID;
34 CLOSE C1;
35 END IF;
36
37 INSERT INTO CSI_COUNTER_READING_LOCKS(
38 READING_LOCK_ID
39 ,COUNTER_ID
40 ,READING_LOCK_DATE
41 ,OBJECT_VERSION_NUMBER
42 ,LAST_UPDATE_DATE
43 ,LAST_UPDATED_BY
44 ,CREATION_DATE
45 ,CREATED_BY
46 ,LAST_UPDATE_LOGIN
47 ,SOURCE_GROUP_REF_ID
48 ,SOURCE_GROUP_REF
49 ,SOURCE_HEADER_REF_ID
50 ,SOURCE_HEADER_REF
51 ,SOURCE_LINE_REF_ID
52 ,SOURCE_LINE_REF
53 ,SOURCE_DIST_REF_ID1
54 ,SOURCE_DIST_REF_ID2
55 )
56 VALUES(
57 px_READING_LOCK_ID
58 ,decode(p_COUNTER_ID, FND_API.G_MISS_NUM, NULL, p_COUNTER_ID)
59 ,decode(p_READING_LOCK_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_READING_LOCK_DATE)
60 ,decode(p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
61 ,decode(p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
62 ,decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
63 ,decode(p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
64 ,decode(p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
65 ,decode(p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
66 ,decode(p_SOURCE_GROUP_REF_ID, FND_API.G_MISS_NUM, NULL, p_SOURCE_GROUP_REF_ID)
67 ,decode(p_SOURCE_GROUP_REF, FND_API.G_MISS_CHAR, NULL, p_SOURCE_GROUP_REF)
68 ,decode(p_SOURCE_HEADER_REF_ID, FND_API.G_MISS_NUM, NULL, p_SOURCE_HEADER_REF_ID)
69 ,decode(p_SOURCE_HEADER_REF, FND_API.G_MISS_CHAR, NULL, p_SOURCE_HEADER_REF)
70 ,decode(p_SOURCE_LINE_REF_ID, FND_API.G_MISS_NUM, NULL, p_SOURCE_LINE_REF_ID)
71 ,decode(p_SOURCE_LINE_REF, FND_API.G_MISS_CHAR, NULL, p_SOURCE_LINE_REF)
72 ,decode(p_SOURCE_DIST_REF_ID1, FND_API.G_MISS_NUM, NULL, p_SOURCE_DIST_REF_ID1)
73 ,decode(p_SOURCE_DIST_REF_ID2, FND_API.G_MISS_NUM, NULL, p_SOURCE_DIST_REF_ID2)
74 );
75 END Insert_Row;
76
77 PROCEDURE Update_Row(
78 p_READING_LOCK_ID NUMBER
79 ,p_COUNTER_ID NUMBER
80 ,p_READING_LOCK_DATE DATE
81 ,p_OBJECT_VERSION_NUMBER NUMBER
82 ,p_LAST_UPDATE_DATE DATE
83 ,p_LAST_UPDATED_BY NUMBER
84 ,p_CREATION_DATE DATE
85 ,p_CREATED_BY NUMBER
86 ,p_LAST_UPDATE_LOGIN NUMBER
87 ,p_SOURCE_GROUP_REF_ID NUMBER
88 ,p_SOURCE_GROUP_REF VARCHAR2
89 ,p_SOURCE_HEADER_REF_ID NUMBER
90 ,p_SOURCE_HEADER_REF VARCHAR2
91 ,p_SOURCE_LINE_REF_ID NUMBER
92 ,p_SOURCE_LINE_REF VARCHAR2
93 ,p_SOURCE_DIST_REF_ID1 NUMBER
94 ,p_SOURCE_DIST_REF_ID2 NUMBER
95 ) IS
96 BEGIN
97 UPDATE CSI_COUNTER_READING_LOCKS
98 SET
99 COUNTER_ID = decode(p_COUNTER_ID, NULL, COUNTER_ID, FND_API.G_MISS_NUM, NULL, p_COUNTER_ID)
100 ,READING_LOCK_DATE = decode(p_READING_LOCK_DATE, NULL, READING_LOCK_DATE, FND_API.G_MISS_DATE, NULL, p_READING_LOCK_DATE)
101 ,OBJECT_VERSION_NUMBER = decode(p_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
102 ,LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE, NULL, LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE)
103 ,LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY, NULL, LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
104 ,CREATION_DATE = decode(p_CREATION_DATE, NULL, CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE)
105 ,CREATED_BY = decode(p_CREATED_BY, NULL, CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
106 ,LAST_UPDATE_LOGIN = decode(p_LAST_UPDATE_LOGIN, NULL, LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
107 ,SOURCE_GROUP_REF_ID = decode(p_SOURCE_GROUP_REF_ID, NULL, SOURCE_GROUP_REF_ID, FND_API.G_MISS_NUM, NULL, p_SOURCE_GROUP_REF_ID)
108 ,SOURCE_GROUP_REF = decode(p_SOURCE_GROUP_REF, NULL, SOURCE_GROUP_REF, FND_API.G_MISS_CHAR, NULL, p_SOURCE_GROUP_REF)
109 ,SOURCE_HEADER_REF_ID = decode(p_SOURCE_HEADER_REF_ID, NULL, SOURCE_HEADER_REF_ID, FND_API.G_MISS_NUM, NULL, p_SOURCE_HEADER_REF_ID)
110 ,SOURCE_HEADER_REF = decode(p_SOURCE_HEADER_REF, NULL, SOURCE_HEADER_REF, FND_API.G_MISS_CHAR, NULL, p_SOURCE_HEADER_REF)
111 ,SOURCE_LINE_REF_ID = decode(p_SOURCE_LINE_REF_ID, NULL, SOURCE_LINE_REF_ID, FND_API.G_MISS_NUM, NULL, p_SOURCE_LINE_REF_ID)
112 ,SOURCE_LINE_REF = decode(p_SOURCE_LINE_REF, NULL, SOURCE_LINE_REF, FND_API.G_MISS_CHAR, NULL, p_SOURCE_LINE_REF)
113 ,SOURCE_DIST_REF_ID1 = decode(p_SOURCE_DIST_REF_ID1, NULL, SOURCE_DIST_REF_ID1, FND_API.G_MISS_NUM, NULL, p_SOURCE_DIST_REF_ID1)
114 ,SOURCE_DIST_REF_ID2 = decode(p_SOURCE_DIST_REF_ID2, NULL, SOURCE_DIST_REF_ID2, FND_API.G_MISS_NUM, NULL, p_SOURCE_DIST_REF_ID2)
115 WHERE READING_LOCK_ID = p_READING_LOCK_ID;
116
117 If (SQL%NOTFOUND) then
118 RAISE NO_DATA_FOUND;
119 End If;
120 END Update_Row;
121
122 PROCEDURE Lock_Row(
123 p_READING_LOCK_ID NUMBER
124 ,p_COUNTER_ID NUMBER
125 ,p_READING_LOCK_DATE DATE
126 ,p_OBJECT_VERSION_NUMBER NUMBER
127 ,p_LAST_UPDATE_DATE DATE
128 ,p_LAST_UPDATED_BY NUMBER
129 ,p_CREATION_DATE DATE
130 ,p_CREATED_BY NUMBER
131 ,p_LAST_UPDATE_LOGIN NUMBER
132 ,p_SOURCE_GROUP_REF_ID NUMBER
133 ,p_SOURCE_GROUP_REF VARCHAR2
134 ,p_SOURCE_HEADER_REF_ID NUMBER
135 ,p_SOURCE_HEADER_REF VARCHAR2
136 ,p_SOURCE_LINE_REF_ID NUMBER
137 ,p_SOURCE_LINE_REF VARCHAR2
138 ,p_SOURCE_DIST_REF_ID1 NUMBER
139 ,p_SOURCE_DIST_REF_ID2 NUMBER
140 ) IS
141
142 CURSOR C1 IS
143 SELECT *
144 FROM CSI_COUNTER_READING_LOCKS
145 WHERE READING_LOCK_ID = p_READING_LOCK_ID
146 FOR UPDATE of READING_LOCK_ID NOWAIT;
147 Recinfo C1%ROWTYPE;
148 BEGIN
149 OPEN C1;
150 FETCH C1 INTO Recinfo;
151 IF (C1%NOTFOUND) THEN
152 CLOSE C1;
153 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
154 APP_EXCEPTION.RAISE_EXCEPTION;
155 END IF;
156 CLOSE C1;
157
158 IF (
159 (Recinfo.READING_LOCK_ID = p_READING_LOCK_ID)
160 AND ((Recinfo.COUNTER_ID = p_COUNTER_ID) OR ((Recinfo.COUNTER_ID IS NULL) AND (p_COUNTER_ID IS NULL)))
161 AND ((Recinfo.READING_LOCK_DATE = p_READING_LOCK_DATE) OR ((Recinfo.READING_LOCK_DATE IS NULL) AND (p_READING_LOCK_DATE IS NULL)))
162 AND ((Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER) OR ((Recinfo.OBJECT_VERSION_NUMBER IS NULL) AND (p_OBJECT_VERSION_NUMBER IS NULL)))
163 AND ((Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE) OR ((Recinfo.LAST_UPDATE_DATE IS NULL) AND (p_LAST_UPDATE_DATE IS NULL)))
164 AND ((Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY) OR ((Recinfo.LAST_UPDATED_BY IS NULL) AND (p_LAST_UPDATED_BY IS NULL)))
165 AND ((Recinfo.CREATION_DATE = p_CREATION_DATE) OR ((Recinfo.CREATION_DATE IS NULL) AND (p_CREATION_DATE IS NULL)))
166 AND ((Recinfo.CREATED_BY = p_CREATED_BY) OR ((Recinfo.CREATED_BY IS NULL) AND (p_CREATED_BY IS NULL)))
167 AND ((Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN) OR ((Recinfo.LAST_UPDATE_LOGIN IS NULL) AND (p_LAST_UPDATE_LOGIN IS NULL)))
168 AND ((Recinfo.SOURCE_GROUP_REF_ID = p_SOURCE_GROUP_REF_ID) OR ((Recinfo.SOURCE_GROUP_REF_ID IS NULL) AND (p_SOURCE_GROUP_REF_ID IS NULL)))
169 AND ((Recinfo.SOURCE_GROUP_REF = p_SOURCE_GROUP_REF) OR ((Recinfo.SOURCE_GROUP_REF IS NULL) AND (p_SOURCE_GROUP_REF IS NULL)))
170 AND ((Recinfo.SOURCE_HEADER_REF_ID = p_SOURCE_HEADER_REF_ID) OR ((Recinfo.SOURCE_HEADER_REF_ID IS NULL) AND (p_SOURCE_HEADER_REF_ID IS NULL)))
171 AND ((Recinfo.SOURCE_HEADER_REF = p_SOURCE_HEADER_REF) OR ((Recinfo.SOURCE_HEADER_REF IS NULL) AND (p_SOURCE_HEADER_REF IS NULL)))
172 AND ((Recinfo.SOURCE_LINE_REF_ID = p_SOURCE_LINE_REF_ID) OR ((Recinfo.SOURCE_LINE_REF_ID IS NULL) AND (p_SOURCE_LINE_REF_ID IS NULL)))
173 AND ((Recinfo.SOURCE_LINE_REF = p_SOURCE_LINE_REF) OR ((Recinfo.SOURCE_LINE_REF IS NULL) AND (p_SOURCE_LINE_REF IS NULL)))
174 AND ((Recinfo.SOURCE_DIST_REF_ID1 = p_SOURCE_DIST_REF_ID1) OR ((Recinfo.SOURCE_DIST_REF_ID1 IS NULL) AND (p_SOURCE_DIST_REF_ID1 IS NULL)))
175 AND ((Recinfo.SOURCE_DIST_REF_ID2 = p_SOURCE_DIST_REF_ID2) OR ((Recinfo.SOURCE_DIST_REF_ID2 IS NULL) AND (p_SOURCE_DIST_REF_ID2 IS NULL)))
176 ) THEN
177 return;
178 ELSE
179 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
180 APP_EXCEPTION.RAISE_EXCEPTION;
181 END IF;
182 END Lock_Row;
183
184 PROCEDURE Delete_Row(
185 p_READING_LOCK_ID NUMBER
186 ) IS
187 BEGIN
188 DELETE FROM CSI_COUNTER_READING_LOCKS
189 WHERE READING_LOCK_ID = p_READING_LOCK_ID;
190
191 If (SQL%NOTFOUND) then
192 RAISE NO_DATA_FOUND;
193 End If;
194 END Delete_Row;
195
196 End CSI_COUNTER_READING_LOCK_PKG;
[Home] [Help]