[Home] [Help]
PACKAGE BODY: APPS.HZ_LOC_ASSIGNMENTS_PKG
Source
1 PACKAGE BODY HZ_LOC_ASSIGNMENTS_PKG AS
2 /*$Header: ARHTLATB.pls 115.4 2002/11/21 19:44:33 sponnamb ship $ */
3
4 PROCEDURE Insert_Row (
5 X_LOCATION_ID IN NUMBER,
6 X_LOC_ID IN NUMBER,
7 X_ORG_ID IN NUMBER,
8 X_OBJECT_VERSION_NUMBER IN NUMBER,
9 X_CREATED_BY_MODULE IN VARCHAR2,
10 X_APPLICATION_ID IN NUMBER
11 ) IS
12
13 BEGIN
14
15 INSERT INTO HZ_LOC_ASSIGNMENTS (
16 LOCATION_ID,
17 LOC_ID,
18 ORG_ID,
19 CREATED_BY,
20 CREATION_DATE,
21 LAST_UPDATE_LOGIN,
22 LAST_UPDATE_DATE,
23 LAST_UPDATED_BY,
24 REQUEST_ID,
25 PROGRAM_APPLICATION_ID,
26 PROGRAM_ID,
27 PROGRAM_UPDATE_DATE,
28 OBJECT_VERSION_NUMBER,
29 CREATED_BY_MODULE,
30 APPLICATION_ID
31 )
32 VALUES (
33 DECODE( X_LOCATION_ID, FND_API.G_MISS_NUM, NULL, X_LOCATION_ID ),
34 DECODE( X_LOC_ID, FND_API.G_MISS_NUM, NULL, X_LOC_ID ),
35 DECODE( X_ORG_ID, FND_API.G_MISS_NUM, NULL, X_ORG_ID ),
36 HZ_UTILITY_V2PUB.CREATED_BY,
37 HZ_UTILITY_V2PUB.CREATION_DATE,
38 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
39 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
40 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
41 HZ_UTILITY_V2PUB.REQUEST_ID,
42 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
43 HZ_UTILITY_V2PUB.PROGRAM_ID,
44 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
45 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
46 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
47 DECODE( X_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
48 ) ;
49
50 END Insert_Row;
51
52
53 PROCEDURE Update_Row (
54 X_Rowid IN OUT NOCOPY VARCHAR2,
55 X_LOCATION_ID IN NUMBER,
56 X_LOC_ID IN NUMBER,
57 X_ORG_ID IN NUMBER,
58 X_OBJECT_VERSION_NUMBER IN NUMBER,
59 X_CREATED_BY_MODULE IN VARCHAR2,
60 X_APPLICATION_ID IN NUMBER
61 ) IS
62
63 BEGIN
64
65 UPDATE HZ_LOC_ASSIGNMENTS SET
66 LOCATION_ID = DECODE( X_LOCATION_ID, NULL, LOCATION_ID, FND_API.G_MISS_NUM, NULL, X_LOCATION_ID ),
67 LOC_ID = DECODE( X_LOC_ID, NULL, LOC_ID, FND_API.G_MISS_NUM, NULL, X_LOC_ID ),
68 ORG_ID = DECODE( X_ORG_ID, NULL, ORG_ID, FND_API.G_MISS_NUM, NULL, X_ORG_ID ),
69 CREATED_BY = CREATED_BY,
70 CREATION_DATE = CREATION_DATE,
71 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
72 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
73 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
74 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
75 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
76 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
77 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
78 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
79 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
80 APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
81 WHERE ROWID = X_RowId;
82
83 IF ( SQL%NOTFOUND ) THEN
84 RAISE NO_DATA_FOUND;
85 END IF;
86
87 END Update_Row;
88
89
90 PROCEDURE Lock_Row (
91 X_Rowid IN OUT NOCOPY VARCHAR2,
92 X_LOCATION_ID IN NUMBER,
93 X_LOC_ID IN NUMBER,
94 X_ORG_ID IN NUMBER,
95 X_CREATED_BY IN NUMBER,
96 X_CREATION_DATE IN DATE,
97 X_LAST_UPDATE_LOGIN IN NUMBER,
98 X_LAST_UPDATE_DATE IN DATE,
99 X_LAST_UPDATED_BY IN NUMBER,
100 X_REQUEST_ID IN NUMBER,
101 X_PROGRAM_APPLICATION_ID IN NUMBER,
102 X_PROGRAM_ID IN NUMBER,
103 X_PROGRAM_UPDATE_DATE IN DATE,
104 X_OBJECT_VERSION_NUMBER IN NUMBER,
105 X_CREATED_BY_MODULE IN VARCHAR2,
106 X_APPLICATION_ID IN NUMBER
107 ) IS
108
109 CURSOR C IS
110 SELECT * FROM HZ_LOC_ASSIGNMENTS
111 WHERE ROWID = x_Rowid
112 FOR UPDATE NOWAIT;
113 Recinfo C%ROWTYPE;
114
115 BEGIN
116
117 OPEN C;
118 FETCH C INTO Recinfo;
119 IF ( C%NOTFOUND ) THEN
120 CLOSE C;
121 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
122 APP_EXCEPTION.RAISE_EXCEPTION;
123 END IF;
124 CLOSE C;
125
126 IF (
127 ( ( Recinfo.LOCATION_ID = X_LOCATION_ID )
128 OR ( ( Recinfo.LOCATION_ID IS NULL )
129 AND ( X_LOCATION_ID IS NULL ) ) )
130 AND ( ( Recinfo.LOC_ID = X_LOC_ID )
131 OR ( ( Recinfo.LOC_ID IS NULL )
132 AND ( X_LOC_ID IS NULL ) ) )
133 AND ( ( Recinfo.ORG_ID = X_ORG_ID )
134 OR ( ( Recinfo.ORG_ID IS NULL )
135 AND ( X_ORG_ID IS NULL ) ) )
136 AND ( ( Recinfo.CREATED_BY = X_CREATED_BY )
137 OR ( ( Recinfo.CREATED_BY IS NULL )
138 AND ( X_CREATED_BY IS NULL ) ) )
139 AND ( ( Recinfo.CREATION_DATE = X_CREATION_DATE )
140 OR ( ( Recinfo.CREATION_DATE IS NULL )
141 AND ( X_CREATION_DATE IS NULL ) ) )
142 AND ( ( Recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
143 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
144 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
145 AND ( ( Recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
146 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
147 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
148 AND ( ( Recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY )
149 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
150 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
151 AND ( ( Recinfo.REQUEST_ID = X_REQUEST_ID )
152 OR ( ( Recinfo.REQUEST_ID IS NULL )
153 AND ( X_REQUEST_ID IS NULL ) ) )
154 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID )
155 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
156 AND ( X_PROGRAM_APPLICATION_ID IS NULL ) ) )
157 AND ( ( Recinfo.PROGRAM_ID = X_PROGRAM_ID )
158 OR ( ( Recinfo.PROGRAM_ID IS NULL )
159 AND ( X_PROGRAM_ID IS NULL ) ) )
160 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE )
161 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
162 AND ( X_PROGRAM_UPDATE_DATE IS NULL ) ) )
163 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER )
164 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
165 AND ( X_OBJECT_VERSION_NUMBER IS NULL ) ) )
166 AND ( ( Recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE )
167 OR ( ( Recinfo.CREATED_BY_MODULE IS NULL )
168 AND ( X_CREATED_BY_MODULE IS NULL ) ) )
169 AND ( ( Recinfo.APPLICATION_ID = X_APPLICATION_ID )
170 OR ( ( Recinfo.APPLICATION_ID IS NULL )
171 AND ( X_APPLICATION_ID IS NULL ) ) )
172 ) THEN
173 RETURN;
174 ELSE
175 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
176 APP_EXCEPTION.RAISE_EXCEPTION;
177 END IF;
178
179 END Lock_Row;
180
181
182 PROCEDURE Select_Row (
183 X_LOCATION_ID IN OUT NOCOPY NUMBER,
184 X_ORG_ID IN OUT NOCOPY NUMBER,
185 X_LOC_ID OUT NOCOPY NUMBER,
186 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
187 X_APPLICATION_ID OUT NOCOPY NUMBER
188 ) IS
189
190 BEGIN
191
192 SELECT
193 NVL( LOCATION_ID, FND_API.G_MISS_NUM ),
194 NVL( LOC_ID, FND_API.G_MISS_NUM ),
195 NVL( ORG_ID, FND_API.G_MISS_NUM ),
196 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
197 NVL( APPLICATION_ID, FND_API.G_MISS_NUM )
198 INTO
199 X_LOCATION_ID,
200 X_LOC_ID,
201 X_ORG_ID,
202 X_CREATED_BY_MODULE,
203 X_APPLICATION_ID
204 FROM HZ_LOC_ASSIGNMENTS
205 WHERE LOCATION_ID = X_LOCATION_ID
206 AND ORG_ID = X_ORG_ID;
207
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
211 FND_MESSAGE.SET_TOKEN( 'RECORD', 'loc_assignment_rec');
212 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_LOCATION_ID ) );
213 FND_MSG_PUB.ADD;
214 RAISE FND_API.G_EXC_ERROR;
215
216 END Select_Row;
217
218 PROCEDURE Delete_Row (
219 X_LOCATION_ID IN NUMBER
220 ) IS
221
222 BEGIN
223
224 DELETE FROM HZ_LOC_ASSIGNMENTS
225 WHERE LOCATION_ID = X_LOCATION_ID;
226
227 IF ( SQL%NOTFOUND ) THEN
228 RAISE NO_DATA_FOUND;
229 END IF;
230
231 END Delete_Row;
232
233 END HZ_LOC_ASSIGNMENTS_PKG;