[Home] [Help]
PACKAGE BODY: APPS.CSI_A_LOCATIONS_PKG
Source
1 PACKAGE BODY CSI_A_LOCATIONS_PKG as
2 /* $Header: csitlocb.pls 115.7 2002/11/12 00:22:56 rmamidip noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_A_LOCATIONS_PKG';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csitlocb.pls';
7
8 PROCEDURE Insert_Row(
9 px_ASSET_LOCATION_ID IN OUT NOCOPY NUMBER,
10 p_FA_LOCATION_ID NUMBER,
11 p_LOCATION_TABLE VARCHAR2,
12 p_LOCATION_ID NUMBER,
13 p_ACTIVE_START_DATE DATE,
14 p_ACTIVE_END_DATE DATE,
15 p_CREATED_BY NUMBER,
16 p_CREATION_DATE DATE,
17 p_LAST_UPDATED_BY NUMBER,
18 p_LAST_UPDATE_DATE DATE,
19 p_LAST_UPDATE_LOGIN NUMBER,
20 p_OBJECT_VERSION_NUMBER NUMBER)
21 IS
22 CURSOR C2 IS SELECT CSI_A_LOCATIONS_S.nextval FROM sys.dual;
23 BEGIN
24 If (px_ASSET_LOCATION_ID IS NULL) OR (px_ASSET_LOCATION_ID = FND_API.G_MISS_NUM) then
25 OPEN C2;
26 FETCH C2 INTO px_ASSET_LOCATION_ID;
27 CLOSE C2;
28 End If;
29 INSERT INTO CSI_A_LOCATIONS(
30 ASSET_LOCATION_ID,
31 FA_LOCATION_ID,
32 LOCATION_TABLE,
33 LOCATION_ID,
34 ACTIVE_START_DATE,
35 ACTIVE_END_DATE,
36 CREATED_BY,
37 CREATION_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATE_LOGIN,
41 OBJECT_VERSION_NUMBER
42 ) VALUES (
43 px_ASSET_LOCATION_ID,
44 decode( p_FA_LOCATION_ID, FND_API.G_MISS_NUM, NULL, p_FA_LOCATION_ID),
45 decode( p_LOCATION_TABLE, FND_API.G_MISS_CHAR, NULL, p_LOCATION_TABLE),
46 decode( p_LOCATION_ID, FND_API.G_MISS_NUM, NULL, p_LOCATION_ID),
47 decode( p_ACTIVE_START_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_ACTIVE_START_DATE),
48 decode( p_ACTIVE_END_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_ACTIVE_END_DATE),
49 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
50 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
51 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
52 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
53 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
54 decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER));
55 End Insert_Row;
56
57 PROCEDURE Update_Row(
58 p_ASSET_LOCATION_ID NUMBER,
59 p_FA_LOCATION_ID NUMBER,
60 p_LOCATION_TABLE VARCHAR2,
61 p_LOCATION_ID NUMBER,
62 p_ACTIVE_START_DATE DATE,
63 p_ACTIVE_END_DATE DATE,
64 p_CREATED_BY NUMBER,
65 p_CREATION_DATE DATE,
66 p_LAST_UPDATED_BY NUMBER,
67 p_LAST_UPDATE_DATE DATE,
68 p_LAST_UPDATE_LOGIN NUMBER,
69 p_OBJECT_VERSION_NUMBER NUMBER)
70 IS
71 BEGIN
72 Update CSI_A_LOCATIONS
73 SET
74 FA_LOCATION_ID = decode( p_FA_LOCATION_ID, FND_API.G_MISS_NUM, FA_LOCATION_ID, p_FA_LOCATION_ID),
75 LOCATION_TABLE = decode( p_LOCATION_TABLE, FND_API.G_MISS_CHAR, LOCATION_TABLE, p_LOCATION_TABLE),
76 LOCATION_ID = decode( p_LOCATION_ID, FND_API.G_MISS_NUM, LOCATION_ID, p_LOCATION_ID),
77 ACTIVE_START_DATE = decode( p_ACTIVE_START_DATE, FND_API.G_MISS_DATE, ACTIVE_START_DATE, p_ACTIVE_START_DATE),
78 ACTIVE_END_DATE = decode( p_ACTIVE_END_DATE, FND_API.G_MISS_DATE, ACTIVE_END_DATE, p_ACTIVE_END_DATE),
79 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
80 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
81 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
82 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
83 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
84 OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
85 where ASSET_LOCATION_ID = p_ASSET_LOCATION_ID;
86
87 If (SQL%NOTFOUND) then
88 RAISE NO_DATA_FOUND;
89 End If;
90 END Update_Row;
91
92 PROCEDURE Delete_Row(
93 p_ASSET_LOCATION_ID NUMBER)
94 IS
95 BEGIN
96 DELETE FROM CSI_A_LOCATIONS
97 WHERE ASSET_LOCATION_ID = p_ASSET_LOCATION_ID;
98 If (SQL%NOTFOUND) then
99 RAISE NO_DATA_FOUND;
100 End If;
101 END Delete_Row;
102
103 PROCEDURE Lock_Row(
104 p_ASSET_LOCATION_ID NUMBER,
105 p_FA_LOCATION_ID NUMBER,
106 p_LOCATION_TABLE VARCHAR2,
107 p_LOCATION_ID NUMBER,
108 p_ACTIVE_START_DATE DATE,
109 p_ACTIVE_END_DATE DATE,
110 p_CREATED_BY NUMBER,
111 p_CREATION_DATE DATE,
112 p_LAST_UPDATED_BY NUMBER,
113 p_LAST_UPDATE_DATE DATE,
114 p_LAST_UPDATE_LOGIN NUMBER,
115 p_OBJECT_VERSION_NUMBER NUMBER)
116 IS
117 CURSOR C IS
118 SELECT *
119 FROM CSI_A_LOCATIONS
120 WHERE ASSET_LOCATION_ID = p_ASSET_LOCATION_ID
121 FOR UPDATE of ASSET_LOCATION_ID NOWAIT;
122 Recinfo C%ROWTYPE;
123 BEGIN
124 OPEN C;
125 FETCH C INTO Recinfo;
126 If (C%NOTFOUND) then
127 CLOSE C;
128 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
129 APP_EXCEPTION.RAISE_EXCEPTION;
130 End If;
131 CLOSE C;
132 if (
133 ( Recinfo.ASSET_LOCATION_ID = p_ASSET_LOCATION_ID)
134 AND ( ( Recinfo.FA_LOCATION_ID = p_FA_LOCATION_ID)
135 OR ( ( Recinfo.FA_LOCATION_ID IS NULL )
136 AND ( p_FA_LOCATION_ID IS NULL )))
137 AND ( ( Recinfo.LOCATION_TABLE = p_LOCATION_TABLE)
138 OR ( ( Recinfo.LOCATION_TABLE IS NULL )
139 AND ( p_LOCATION_TABLE IS NULL )))
140 AND ( ( Recinfo.LOCATION_ID = p_LOCATION_ID)
141 OR ( ( Recinfo.LOCATION_ID IS NULL )
142 AND ( p_LOCATION_ID IS NULL )))
143 AND ( ( Recinfo.ACTIVE_START_DATE = p_ACTIVE_START_DATE)
144 OR ( ( Recinfo.ACTIVE_START_DATE IS NULL )
145 AND ( p_ACTIVE_START_DATE IS NULL )))
146 AND ( ( Recinfo.ACTIVE_END_DATE = p_ACTIVE_END_DATE)
147 OR ( ( Recinfo.ACTIVE_END_DATE IS NULL )
148 AND ( p_ACTIVE_END_DATE IS NULL )))
149 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
150 OR ( ( Recinfo.CREATED_BY IS NULL )
151 AND ( p_CREATED_BY IS NULL )))
152 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
153 OR ( ( Recinfo.CREATION_DATE IS NULL )
154 AND ( p_CREATION_DATE IS NULL )))
155 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
156 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
157 AND ( p_LAST_UPDATED_BY IS NULL )))
158 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
159 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
160 AND ( p_LAST_UPDATE_DATE IS NULL )))
161 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
162 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
163 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
164 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
165 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
166 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
167 ) then
168 return;
169 else
170 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
171 APP_EXCEPTION.RAISE_EXCEPTION;
172 End If;
173 END Lock_Row;
174
175 End CSI_A_LOCATIONS_PKG;
176