[Home] [Help]
PACKAGE BODY: APPS.CSP_EXCESS_LST_SERIAL_LOTS_PKG
Source
1 PACKAGE BODY CSP_EXCESS_LST_SERIAL_LOTS_PKG as
2 /* $Header: cspteslb.pls 115.6 2003/02/27 23:44:20 ajosephg ship $ */
3 -- Start of Comments
4 -- Package name : CSP_EXCESS_LST_SERIAL_LOTS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_EXCESS_LST_SERIAL_LOTS_PKG';
10 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspteslb.pls';
11 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
12 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
13 PROCEDURE Insert_Row(
14 px_EXCESS_LIST_SERIAL_LOT_ID IN OUT NOCOPY NUMBER,
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_EXCESS_LINE_ID NUMBER,
21 p_QUANTITY NUMBER,
22 p_LOT_NUMBER VARCHAR2,
23 p_SERIAL_NUMBER VARCHAR2,
24 p_REVISION VARCHAR2,
25 p_LOCATOR_ID NUMBER)
26 IS
27 CURSOR C2 IS SELECT CSP_EXCESS_LIST_SERIAL_LOTS_S1.nextval FROM sys.dual;
28 BEGIN
29 If (px_EXCESS_LIST_SERIAL_LOT_ID IS NULL) OR (px_EXCESS_LIST_SERIAL_LOT_ID = FND_API.G_MISS_NUM) then
30 OPEN C2;
31 FETCH C2 INTO px_EXCESS_LIST_SERIAL_LOT_ID;
32 CLOSE C2;
33 End If;
34 INSERT INTO CSP_EXCESS_LIST_SERIAL_LOTS(
35 EXCESS_LIST_SERIAL_LOT_ID,
36 CREATED_BY,
37 CREATION_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATE_LOGIN,
41 EXCESS_LINE_ID,
42 QUANTITY,
43 LOT_NUMBER,
44 SERIAL_NUMBER,
45 REVISION,
46 LOCATOR_ID
47 ) VALUES (
48 px_EXCESS_LIST_SERIAL_LOT_ID,
49 G_USER_ID,
50 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
51 G_USER_ID,
52 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
53 G_LOGIN_ID,
54 decode( p_EXCESS_LINE_ID, FND_API.G_MISS_NUM, NULL, p_EXCESS_LINE_ID),
55 decode( p_QUANTITY, FND_API.G_MISS_NUM, NULL, p_QUANTITY),
56 decode( p_LOT_NUMBER, FND_API.G_MISS_CHAR, NULL, p_LOT_NUMBER),
57 decode( p_SERIAL_NUMBER, FND_API.G_MISS_CHAR, NULL, p_SERIAL_NUMBER),
58 decode( p_REVISION, FND_API.G_MISS_CHAR, NULL, p_REVISION),
59 decode( p_LOCATOR_ID, FND_API.G_MISS_NUM, NULL, p_LOCATOR_ID));
60
61 End Insert_Row;
62 PROCEDURE Update_Row(
63 p_EXCESS_LIST_SERIAL_LOT_ID NUMBER,
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_EXCESS_LINE_ID NUMBER,
70 p_QUANTITY NUMBER,
71 p_LOT_NUMBER VARCHAR2,
72 p_SERIAL_NUMBER VARCHAR2,
73 p_REVISION VARCHAR2,
74 p_LOCATOR_ID NUMBER)
75
76 IS
77 BEGIN
78 Update CSP_EXCESS_LIST_SERIAL_LOTS
79 SET 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 = G_USER_ID,
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 = G_LOGIN_ID,
84 EXCESS_LINE_ID = decode( p_EXCESS_LINE_ID, FND_API.G_MISS_NUM, EXCESS_LINE_ID, p_EXCESS_LINE_ID),
85 QUANTITY = decode( p_QUANTITY, FND_API.G_MISS_NUM, QUANTITY, p_QUANTITY),
86 LOT_NUMBER = decode( p_LOT_NUMBER, FND_API.G_MISS_CHAR, LOT_NUMBER, p_LOT_NUMBER),
87 SERIAL_NUMBER = decode( p_SERIAL_NUMBER, FND_API.G_MISS_CHAR, SERIAL_NUMBER, p_SERIAL_NUMBER),
88 REVISION = decode( p_REVISION, FND_API.G_MISS_CHAR, REVISION, p_REVISION),
89 LOCATOR_ID = decode( p_LOCATOR_ID, FND_API.G_MISS_NUM, LOCATOR_ID, p_LOCATOR_ID)
90 where EXCESS_LIST_SERIAL_LOT_ID = p_EXCESS_LIST_SERIAL_LOT_ID;
91 If (SQL%NOTFOUND) then
92 RAISE NO_DATA_FOUND;
93 End If;
94 END Update_Row;
95 PROCEDURE Delete_Row(
96 p_EXCESS_LIST_SERIAL_LOT_ID NUMBER)
97 IS
98 BEGIN
99 DELETE FROM CSP_EXCESS_LIST_SERIAL_LOTS
100 WHERE EXCESS_LIST_SERIAL_LOT_ID = p_EXCESS_LIST_SERIAL_LOT_ID;
101 If (SQL%NOTFOUND) then
102 RAISE NO_DATA_FOUND;
103 End If;
104 END Delete_Row;
105 PROCEDURE Lock_Row(
106 p_EXCESS_LIST_SERIAL_LOT_ID NUMBER,
107 p_CREATED_BY NUMBER,
108 p_CREATION_DATE DATE,
109 p_LAST_UPDATED_BY NUMBER,
110 p_LAST_UPDATE_DATE DATE,
111 p_LAST_UPDATE_LOGIN NUMBER,
112 p_EXCESS_LINE_ID NUMBER,
113 p_QUANTITY NUMBER,
114 p_LOT_NUMBER VARCHAR2,
115 p_SERIAL_NUMBER VARCHAR2,
116 p_REVISION VARCHAR2,
117 p_LOCATOR_ID NUMBER)
118
119 IS
120 CURSOR C IS
121 SELECT *
122 FROM CSP_EXCESS_LIST_SERIAL_LOTS
123 WHERE EXCESS_LIST_SERIAL_LOT_ID = p_EXCESS_LIST_SERIAL_LOT_ID
124 FOR UPDATE of EXCESS_LIST_SERIAL_LOT_ID NOWAIT;
125 Recinfo C%ROWTYPE;
126 BEGIN
127 OPEN C;
128 FETCH C INTO Recinfo;
129 If (C%NOTFOUND) then
130 CLOSE C;
131 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
132 APP_EXCEPTION.RAISE_EXCEPTION;
133 End If;
134 CLOSE C;
135 if (
136 ( Recinfo.EXCESS_LIST_SERIAL_LOT_ID = p_EXCESS_LIST_SERIAL_LOT_ID)
137 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
138 OR ( ( Recinfo.CREATED_BY IS NULL )
139 AND ( p_CREATED_BY IS NULL )))
140 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
141 OR ( ( Recinfo.CREATION_DATE IS NULL )
142 AND ( p_CREATION_DATE IS NULL )))
143 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
144 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
145 AND ( p_LAST_UPDATED_BY IS NULL )))
146 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
147 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
148 AND ( p_LAST_UPDATE_DATE IS NULL )))
149 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
150 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
151 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
152 AND ( ( Recinfo.EXCESS_LINE_ID = p_EXCESS_LINE_ID)
153 OR ( ( Recinfo.EXCESS_LINE_ID IS NULL )
154 AND ( p_EXCESS_LINE_ID IS NULL )))
155 AND ( ( Recinfo.QUANTITY = p_QUANTITY)
156 OR ( ( Recinfo.QUANTITY IS NULL )
157 AND ( p_QUANTITY IS NULL )))
158 AND ( ( Recinfo.LOT_NUMBER = p_LOT_NUMBER)
159 OR ( ( Recinfo.LOT_NUMBER IS NULL )
160 AND ( p_LOT_NUMBER IS NULL )))
161 AND ( ( Recinfo.SERIAL_NUMBER = p_SERIAL_NUMBER)
162 OR ( ( Recinfo.SERIAL_NUMBER IS NULL )
163 AND ( p_SERIAL_NUMBER IS NULL )))
164 AND ( ( Recinfo.REVISION = p_REVISION)
165 OR ( ( Recinfo.REVISION IS NULL )
166 AND ( p_REVISION IS NULL )))
167 AND ( ( Recinfo.LOCATOR_ID = p_LOCATOR_ID)
168 OR ( ( Recinfo.LOCATOR_ID IS NULL )
169 AND ( p_LOCATOR_ID IS NULL )))
170 ) then
171 return;
172 else
173 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
174 APP_EXCEPTION.RAISE_EXCEPTION;
175 End If;
176 END Lock_Row;
177 End CSP_EXCESS_LST_SERIAL_LOTS_PKG;