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