DBA Data[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;