[Home] [Help]
PACKAGE BODY: APPS.CSP_PICKLIST_SERIAL_LOTS_PKG
Source
1 PACKAGE BODY CSP_PICKLIST_SERIAL_LOTS_PKG AS
2 /* $Header: cspttslb.pls 115.4 2002/11/26 07:11:21 hhaugeru ship $ */
3 -- Start of Comments
4 -- Package name : CSP_PICKLIST_SERIAL_LOTS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_PICKLIST_SERIAL_LOTS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspttslb.pls';
13
14 PROCEDURE Insert_Row(
15 px_PICKLIST_SERIAL_LOT_ID IN OUT NOCOPY NUMBER,
16 p_CREATED_BY NUMBER,
17 p_CREATION_DATE DATE,
18 p_LAST_UPDATED_BY NUMBER,
19 p_LAST_UPDATE_DATE DATE,
20 p_LAST_UPDATE_LOGIN NUMBER,
21 p_PICKLIST_LINE_ID NUMBER,
22 p_ORGANIZATION_ID NUMBER,
23 p_INVENTORY_ITEM_ID NUMBER,
24 p_QUANTITY NUMBER,
25 p_LOT_NUMBER VARCHAR2,
26 p_SERIAL_NUMBER VARCHAR2)
27
28 IS
29 CURSOR C2 IS SELECT CSP_PICKLIST_SERIAL_LOTS_S1.nextval FROM sys.dual;
30 BEGIN
31 If (px_PICKLIST_SERIAL_LOT_ID IS NULL) OR (px_PICKLIST_SERIAL_LOT_ID = FND_API.G_MISS_NUM) then
32 OPEN C2;
33 FETCH C2 INTO px_PICKLIST_SERIAL_LOT_ID;
34 CLOSE C2;
35 End If;
36 INSERT INTO CSP_PICKLIST_SERIAL_LOTS(
37 PICKLIST_SERIAL_LOT_ID,
38 CREATED_BY,
39 CREATION_DATE,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_DATE,
42 LAST_UPDATE_LOGIN,
43 PICKLIST_LINE_ID,
44 ORGANIZATION_ID,
45 INVENTORY_ITEM_ID,
46 QUANTITY,
47 LOT_NUMBER,
48 SERIAL_NUMBER
49 ) VALUES (
50 px_PICKLIST_SERIAL_LOT_ID,
51 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
52 decode(p_CREATION_DATE,fnd_api.g_miss_date,to_date(null),p_creation_date),
53 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
54 decode(p_LAST_UPDATE_DATE,fnd_api.g_miss_date,to_date(null),p_last_update_date),
55 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
56 decode( p_PICKLIST_LINE_ID, FND_API.G_MISS_NUM, NULL, p_PICKLIST_LINE_ID),
57 decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, p_ORGANIZATION_ID),
58 decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_INVENTORY_ITEM_ID),
59 decode( p_QUANTITY, FND_API.G_MISS_NUM, NULL, p_QUANTITY),
60 decode( p_LOT_NUMBER, FND_API.G_MISS_CHAR, NULL, p_LOT_NUMBER),
61 decode( p_SERIAL_NUMBER, FND_API.G_MISS_CHAR, NULL, p_SERIAL_NUMBER));
62 End Insert_Row;
63
64 PROCEDURE Update_Row(
65 p_PICKLIST_SERIAL_LOT_ID NUMBER,
66 p_CREATED_BY NUMBER,
67 p_CREATION_DATE DATE,
68 p_LAST_UPDATED_BY NUMBER,
69 p_LAST_UPDATE_DATE DATE,
70 p_LAST_UPDATE_LOGIN NUMBER,
71 p_PICKLIST_LINE_ID NUMBER,
72 p_ORGANIZATION_ID NUMBER,
73 p_INVENTORY_ITEM_ID NUMBER,
74 p_QUANTITY NUMBER,
75 p_LOT_NUMBER VARCHAR2,
76 p_SERIAL_NUMBER VARCHAR2)
77
78 IS
79 BEGIN
80 Update CSP_PICKLIST_SERIAL_LOTS
81 SET
82 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
83 CREATION_DATE = decode(p_CREATION_DATE,fnd_api.g_miss_date,creation_date,p_creation_date),
84 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
85 LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE,fnd_api.g_miss_date,last_update_date,p_last_update_date),
86 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
87 PICKLIST_LINE_ID = decode( p_PICKLIST_LINE_ID, FND_API.G_MISS_NUM, PICKLIST_LINE_ID, p_PICKLIST_LINE_ID),
88 ORGANIZATION_ID = decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, ORGANIZATION_ID, p_ORGANIZATION_ID),
89 INVENTORY_ITEM_ID = decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, INVENTORY_ITEM_ID, p_INVENTORY_ITEM_ID),
90 QUANTITY = decode( p_QUANTITY, FND_API.G_MISS_NUM, QUANTITY, p_QUANTITY),
91 LOT_NUMBER = decode( p_LOT_NUMBER, FND_API.G_MISS_CHAR, LOT_NUMBER, p_LOT_NUMBER),
92 SERIAL_NUMBER = decode( p_SERIAL_NUMBER, FND_API.G_MISS_CHAR, SERIAL_NUMBER, p_SERIAL_NUMBER)
93 where PICKLIST_SERIAL_LOT_ID = p_PICKLIST_SERIAL_LOT_ID;
94
95 If (SQL%NOTFOUND) then
96 RAISE NO_DATA_FOUND;
97 End If;
98 END Update_Row;
99
100 PROCEDURE Delete_Row(
101 p_PICKLIST_SERIAL_LOT_ID NUMBER)
102 IS
103 BEGIN
104 DELETE FROM CSP_PICKLIST_SERIAL_LOTS
105 WHERE PICKLIST_SERIAL_LOT_ID = p_PICKLIST_SERIAL_LOT_ID;
106 If (SQL%NOTFOUND) then
107 RAISE NO_DATA_FOUND;
108 End If;
109 END Delete_Row;
110
111 PROCEDURE Lock_Row(
112 p_PICKLIST_SERIAL_LOT_ID NUMBER,
113 p_CREATED_BY NUMBER,
114 p_CREATION_DATE DATE,
115 p_LAST_UPDATED_BY NUMBER,
116 p_LAST_UPDATE_DATE DATE,
117 p_LAST_UPDATE_LOGIN NUMBER,
118 p_PICKLIST_LINE_ID NUMBER,
119 p_ORGANIZATION_ID NUMBER,
120 p_INVENTORY_ITEM_ID NUMBER,
121 p_QUANTITY NUMBER,
122 p_LOT_NUMBER VARCHAR2,
123 p_SERIAL_NUMBER VARCHAR2)
124
125 IS
126 CURSOR C IS
127 SELECT *
128 FROM CSP_PICKLIST_SERIAL_LOTS
129 WHERE PICKLIST_SERIAL_LOT_ID = p_PICKLIST_SERIAL_LOT_ID
130 FOR UPDATE of PICKLIST_SERIAL_LOT_ID NOWAIT;
131 Recinfo C%ROWTYPE;
132 BEGIN
133 OPEN C;
134 FETCH C INTO Recinfo;
135 If (C%NOTFOUND) then
136 CLOSE C;
137 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
138 APP_EXCEPTION.RAISE_EXCEPTION;
139 End If;
140 CLOSE C;
141 if (
142 ( Recinfo.PICKLIST_SERIAL_LOT_ID = p_PICKLIST_SERIAL_LOT_ID)
143 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
144 OR ( ( Recinfo.CREATED_BY IS NULL )
145 AND ( p_CREATED_BY IS NULL )))
146 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
147 OR ( ( Recinfo.CREATION_DATE IS NULL )
148 AND ( p_CREATION_DATE IS NULL )))
149 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
150 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
151 AND ( p_LAST_UPDATED_BY IS NULL )))
152 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
153 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
154 AND ( p_LAST_UPDATE_DATE IS NULL )))
155 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
156 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
157 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
158 AND ( ( Recinfo.PICKLIST_LINE_ID = p_PICKLIST_LINE_ID)
159 OR ( ( Recinfo.PICKLIST_LINE_ID IS NULL )
160 AND ( p_PICKLIST_LINE_ID IS NULL )))
161 AND ( ( Recinfo.ORGANIZATION_ID = p_ORGANIZATION_ID)
162 OR ( ( Recinfo.ORGANIZATION_ID IS NULL )
163 AND ( p_ORGANIZATION_ID IS NULL )))
164 AND ( ( Recinfo.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID)
165 OR ( ( Recinfo.INVENTORY_ITEM_ID IS NULL )
166 AND ( p_INVENTORY_ITEM_ID IS NULL )))
167 AND ( ( Recinfo.QUANTITY = p_QUANTITY)
168 OR ( ( Recinfo.QUANTITY IS NULL )
169 AND ( p_QUANTITY IS NULL )))
170 AND ( ( Recinfo.LOT_NUMBER = p_LOT_NUMBER)
171 OR ( ( Recinfo.LOT_NUMBER IS NULL )
172 AND ( p_LOT_NUMBER IS NULL )))
173 AND ( ( Recinfo.SERIAL_NUMBER = p_SERIAL_NUMBER)
174 OR ( ( Recinfo.SERIAL_NUMBER IS NULL )
175 AND ( p_SERIAL_NUMBER 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 End CSP_PICKLIST_SERIAL_LOTS_PKG;