[Home] [Help]
PACKAGE BODY: APPS.CSP_REQUIREMENT_LINES_PUB
Source
1 PACKAGE BODY CSP_REQUIREMENT_LINES_PUB AS
2 /* $Header: cspprqlb.pls 120.1 2010/12/03 23:26:29 hhaugeru noship $ */
3 -- Start of Comments
4 -- Package name : CSP_REQUIREMENT_LINES_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_REQUIREMENT_LINES_PUB';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspprqlb.pls';
13
14 -- Start of Comments
15 -- ***************** Private Conversion Routines Values -> Ids **************
16 -- Purpose
17 --
18 -- This procedure takes a public REQUIREMENT_LINES record as input. It may contain
19 -- values or ids. All values are then converted into ids and a
20 -- private REQUIREMENT_LINES record is returned for the private
21 -- API call.
22 --
23 -- Conversions:
24 --
25 -- Notes
26 --
27 -- 1. IDs take precedence over values. If both are present for a field, ID is used,
28 -- the value based parameter is ignored and a warning message is created.
29 -- 2. This is automatically generated procedure, it converts public record type to
30 -- private record type for all attributes.
31 -- Developer must manually add conversion logic to the attributes.
32 --
33 -- End of Comments
34 PROCEDURE Convert_RQL_Values_To_Ids(
35 P_RQL_Tbl IN CSP_REQUIREMENT_LINES_PUB.RQL_Tbl_Type,
36 x_pvt_RQL_tbl OUT NOCOPY CSP_REQUIREMENT_LINES_PVT.Requirement_Line_Tbl_Type
37 )
38 IS
39 l_any_errors BOOLEAN := FALSE;
40 l_pvt_RQL_rec CSP_REQUIREMENT_LINES_PVT.Requirement_Line_Rec_Type;
41 l_RQL_rec CSP_REQUIREMENT_LINES_PUB.RQL_Rec_Type;
42 l_inventory_item_id Number;
43 -- Hint: Declare cursor and local variables
44 CURSOR C_Get_Item_Id IS
45 SELECT inventory_item_id
46 FROM mtl_system_items_b
47 WHERE decode(nvl(l_RQL_rec.SEGMENT1, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT1) = nvl(SEGMENT1, '####')
48 AND decode(nvl(l_RQL_rec.SEGMENT2, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT2) = nvl(SEGMENT2, '####')
49 AND decode(nvl(l_RQL_rec.SEGMENT3, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT3) = nvl(SEGMENT3, '####')
50 AND decode(nvl(l_RQL_rec.SEGMENT4, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT4) = nvl(SEGMENT4, '####')
51 AND decode(nvl(l_RQL_rec.SEGMENT5, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT5) = nvl(SEGMENT5, '####')
52 AND decode(nvl(l_RQL_rec.SEGMENT6, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT6) = nvl(SEGMENT6, '####')
53 AND decode(nvl(l_RQL_rec.SEGMENT7, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT7) = nvl(SEGMENT7, '####')
54 AND decode(nvl(l_RQL_rec.SEGMENT8, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT8) = nvl(SEGMENT8, '####')
55 AND decode(nvl(l_RQL_rec.SEGMENT9, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT9) = nvl(SEGMENT9, '####')
56 AND decode(nvl(l_RQL_rec.SEGMENT10, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT10) = nvl(SEGMENT10, '####')
57 AND decode(nvl(l_RQL_rec.SEGMENT11, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT11) = nvl(SEGMENT11, '####')
58 AND decode(nvl(l_RQL_rec.SEGMENT12, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT12) = nvl(SEGMENT12, '####')
59 AND decode(nvl(l_RQL_rec.SEGMENT13, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT13) = nvl(SEGMENT13, '####')
60 AND decode(nvl(l_RQL_rec.SEGMENT14, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT14) = nvl(SEGMENT14, '####')
61 AND decode(nvl(l_RQL_rec.SEGMENT15, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT15) = nvl(SEGMENT15, '####')
62 AND decode(nvl(l_RQL_rec.SEGMENT16, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT16) = nvl(SEGMENT16, '####')
63 AND decode(nvl(l_RQL_rec.SEGMENT17, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT17) = nvl(SEGMENT17, '####')
64 AND decode(nvl(l_RQL_rec.SEGMENT18, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT18) = nvl(SEGMENT18, '####')
65 AND decode(nvl(l_RQL_rec.SEGMENT19, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT19) = nvl(SEGMENT19, '####')
66 AND decode(nvl(l_RQL_rec.SEGMENT20, FND_API.G_MISS_CHAR), FND_API.G_MISS_CHAR, '####', l_RQL_rec.SEGMENT20) = nvl(SEGMENT20, '####')
67 AND organization_id = cs_std.get_item_valdn_orgzn_id;
68 EXCP_USER_DEFINED EXCEPTION;
69 BEGIN
70 FOR I IN 1..p_RQL_Tbl.count LOOP
71 l_RQL_rec := P_RQL_Tbl(I);
72 If(l_RQL_rec.inventory_item_id is NOT NULL and l_RQL_rec.inventory_item_id <> FND_API.G_MISS_NUM) THEN
73 x_pvt_RQL_Tbl(I).inventory_item_id := l_RQL_rec.inventory_item_id;
74 ELSIF((l_RQL_rec.SEGMENT1 is NOT NULL and l_RQL_rec.SEGMENT1 <> FND_API.G_MISS_CHAR) OR
75 (l_RQL_rec.SEGMENT2 is NOT NULL and l_RQL_rec.SEGMENT2 <> FND_API.G_MISS_CHAR) OR
76 (l_RQL_rec.SEGMENT3 is NOT NULL and l_RQL_rec.SEGMENT3 <> FND_API.G_MISS_CHAR) OR
77 (l_RQL_rec.SEGMENT4 is NOT NULL and l_RQL_rec.SEGMENT4 <> FND_API.G_MISS_CHAR) OR
78 (l_RQL_rec.SEGMENT5 is NOT NULL and l_RQL_rec.SEGMENT5 <> FND_API.G_MISS_CHAR) OR
79 (l_RQL_rec.SEGMENT6 is NOT NULL and l_RQL_rec.SEGMENT6 <> FND_API.G_MISS_CHAR) OR
80 (l_RQL_rec.SEGMENT7 is NOT NULL and l_RQL_rec.SEGMENT7 <> FND_API.G_MISS_CHAR) OR
81 (l_RQL_rec.SEGMENT8 is NOT NULL and l_RQL_rec.SEGMENT8 <> FND_API.G_MISS_CHAR) OR
82 (l_RQL_rec.SEGMENT9 is NOT NULL and l_RQL_rec.SEGMENT9 <> FND_API.G_MISS_CHAR) OR
83 (l_RQL_rec.SEGMENT10 is NOT NULL and l_RQL_rec.SEGMENT10 <> FND_API.G_MISS_CHAR) OR
84 (l_RQL_rec.SEGMENT11 is NOT NULL and l_RQL_rec.SEGMENT11 <> FND_API.G_MISS_CHAR) OR
85 (l_RQL_rec.SEGMENT12 is NOT NULL and l_RQL_rec.SEGMENT12 <> FND_API.G_MISS_CHAR) OR
86 (l_RQL_rec.SEGMENT13 is NOT NULL and l_RQL_rec.SEGMENT13 <> FND_API.G_MISS_CHAR) OR
87 (l_RQL_rec.SEGMENT14 is NOT NULL and l_RQL_rec.SEGMENT14 <> FND_API.G_MISS_CHAR) OR
88 (l_RQL_rec.SEGMENT15 is NOT NULL and l_RQL_rec.SEGMENT15 <> FND_API.G_MISS_CHAR) OR
89 (l_RQL_rec.SEGMENT16 is NOT NULL and l_RQL_rec.SEGMENT16 <> FND_API.G_MISS_CHAR) OR
90 (l_RQL_rec.SEGMENT17 is NOT NULL and l_RQL_rec.SEGMENT17 <> FND_API.G_MISS_CHAR) OR
91 (l_RQL_rec.SEGMENT18 is NOT NULL and l_RQL_rec.SEGMENT18 <> FND_API.G_MISS_CHAR) OR
92 (l_RQL_rec.SEGMENT19 is NOT NULL and l_RQL_rec.SEGMENT19 <> FND_API.G_MISS_CHAR) OR
93 (l_RQL_rec.SEGMENT20 is NOT NULL and l_RQL_rec.SEGMENT20 <> FND_API.G_MISS_CHAR))
94 THEN
95 OPEN C_Get_Item_Id;
96 FETCH C_Get_Item_Id INTO l_inventory_item_id;
97 IF C_Get_Item_Id%NOTFOUND THEN
98 FND_MESSAGE.SET_NAME ('INV', 'INV_INVALID_ITEM');
99 FND_MSG_PUB.ADD;
100 RAISE EXCP_USER_DEFINED;
101 END IF;
102 CLOSE C_Get_Item_Id;
103 x_pvt_RQL_Tbl(I).inventory_item_id := l_inventory_item_id;
104 ELSE
105 x_pvt_RQL_Tbl(I).inventory_item_id := nvl(p_RQL_Tbl(I).inventory_item_id, NULL);
106 END IF;
107
108 -- Now copy the rest of the columns to the private record
109 -- Hint: We provide copy all columns to the private record.
110 -- Developer should delete those fields which are used by Value-Id conversion above
111 -- Hint: Developer should remove some of the following statements because of inconsistent column name between table and view.
112
113 x_pvt_RQL_Tbl(I).REQUIREMENT_LINE_ID := p_RQL_Tbl(I).REQUIREMENT_LINE_ID;
114 x_pvt_RQL_Tbl(I).CREATED_BY := p_RQL_Tbl(I).CREATED_BY;
115 x_pvt_RQL_Tbl(I).CREATION_DATE := p_RQL_Tbl(I).CREATION_DATE;
116 x_pvt_RQL_Tbl(I).LAST_UPDATED_BY := p_RQL_Tbl(I).LAST_UPDATED_BY;
117 x_pvt_RQL_Tbl(I).LAST_UPDATE_DATE := p_RQL_Tbl(I).LAST_UPDATE_DATE;
118 x_pvt_RQL_Tbl(I).LAST_UPDATE_LOGIN := p_RQL_Tbl(I).LAST_UPDATE_LOGIN;
119 x_pvt_RQL_Tbl(I).REQUIREMENT_HEADER_ID := p_RQL_Tbl(I).REQUIREMENT_HEADER_ID;
120 --x_pvt_RQL_Tbl(I).INVENTORY_ITEM_ID := p_RQL_Tbl(I).INVENTORY_ITEM_ID;
121 x_pvt_RQL_Tbl(I).UOM_CODE := p_RQL_Tbl(I).UOM_CODE;
122 x_pvt_RQL_Tbl(I).REQUIRED_QUANTITY := p_RQL_Tbl(I).REQUIRED_QUANTITY;
123 x_pvt_RQL_Tbl(I).SHIP_COMPLETE_FLAG := p_RQL_Tbl(I).SHIP_COMPLETE_FLAG;
124 x_pvt_RQL_Tbl(I).LIKELIHOOD := p_RQL_Tbl(I).LIKELIHOOD;
125 x_pvt_RQL_Tbl(I).REVISION := p_RQL_Tbl(I).REVISION;
126 x_pvt_RQL_Tbl(I).SOURCE_ORGANIZATION_ID := p_RQL_Tbl(I).SOURCE_ORGANIZATION_ID;
127 x_pvt_RQL_Tbl(I).SOURCE_SUBINVENTORY := p_RQL_Tbl(I).SOURCE_SUBINVENTORY;
128 x_pvt_RQL_Tbl(I).ORDERED_QUANTITY := p_RQL_Tbl(I).ORDERED_QUANTITY;
129 x_pvt_RQL_Tbl(I).ORDER_LINE_ID := p_RQL_Tbl(I).ORDER_LINE_ID;
130 x_pvt_RQL_Tbl(I).RESERVATION_ID := p_RQL_Tbl(I).RESERVATION_ID;
131 x_pvt_RQL_Tbl(I).ATTRIBUTE_CATEGORY := p_RQL_Tbl(I).ATTRIBUTE_CATEGORY;
132 x_pvt_RQL_Tbl(I).ATTRIBUTE1 := p_RQL_Tbl(I).ATTRIBUTE1;
133 x_pvt_RQL_Tbl(I).ATTRIBUTE2 := p_RQL_Tbl(I).ATTRIBUTE2;
134 x_pvt_RQL_Tbl(I).ATTRIBUTE3 := p_RQL_Tbl(I).ATTRIBUTE3;
135 x_pvt_RQL_Tbl(I).ATTRIBUTE4 := p_RQL_Tbl(I).ATTRIBUTE4;
136 x_pvt_RQL_Tbl(I).ATTRIBUTE5 := p_RQL_Tbl(I).ATTRIBUTE5;
137 x_pvt_RQL_Tbl(I).ATTRIBUTE6 := p_RQL_Tbl(I).ATTRIBUTE6;
138 x_pvt_RQL_Tbl(I).ATTRIBUTE7 := p_RQL_Tbl(I).ATTRIBUTE7;
139 x_pvt_RQL_Tbl(I).ATTRIBUTE8 := p_RQL_Tbl(I).ATTRIBUTE8;
140 x_pvt_RQL_Tbl(I).ATTRIBUTE9 := p_RQL_Tbl(I).ATTRIBUTE9;
141 x_pvt_RQL_Tbl(I).ATTRIBUTE10 := p_RQL_Tbl(I).ATTRIBUTE10;
142 x_pvt_RQL_Tbl(I).ATTRIBUTE11 := p_RQL_Tbl(I).ATTRIBUTE11;
143 x_pvt_RQL_Tbl(I).ATTRIBUTE12 := p_RQL_Tbl(I).ATTRIBUTE12;
144 x_pvt_RQL_Tbl(I).ATTRIBUTE13 := p_RQL_Tbl(I).ATTRIBUTE13;
145 x_pvt_RQL_Tbl(I).ATTRIBUTE14 := p_RQL_Tbl(I).ATTRIBUTE14;
146 x_pvt_RQL_Tbl(I).ATTRIBUTE15 := p_RQL_Tbl(I).ATTRIBUTE15;
147 x_pvt_RQL_Tbl(I).ARRIVAL_DATE := p_RQL_Tbl(I).ARRIVAL_DATE;
148 x_pvt_RQL_Tbl(I).ITEM_SCRATCHPAD := p_RQL_Tbl(I).ITEM_SCRATCHPAD;
149 x_pvt_RQL_Tbl(I).SHIPPING_METHOD_CODE := p_RQL_Tbl(I).SHIPPING_METHOD_CODE;
150 x_pvt_RQL_Tbl(I).LOCAL_RESERVATION_ID := p_RQL_Tbl(I).LOCAL_RESERVATION_ID;
151 x_pvt_RQL_Tbl(I).SOURCED_FROM := p_RQL_Tbl(I).SOURCED_FROM;
152
153 END LOOP;
154 -- If there is an error in conversion precessing, raise an error.
155 /* IF l_any_errors
156 THEN
157 raise FND_API.G_EXC_ERROR;
158 END IF;
159 */
160 END Convert_RQL_Values_To_Ids;
161
162 PROCEDURE Validate_Requirement_Lines(l_pvt_RQL_Tbl IN CSP_REQUIREMENT_LINES_PVT.Requirement_Line_Tbl_Type) IS
163 l_count NUMBER;
164 EXCP_USER_DEFINED EXCEPTION;
165 BEGIN
166 FOR I IN 1..l_pvt_RQL_TBL.COUNT LOOP
167 IF (l_pvt_RQL_Tbl(I).requirement_header_id IS NOT NULL
168 AND l_pvt_RQL_Tbl(I).requirement_header_id <> FND_API.G_MISS_NUM)THEN
169 BEGIN
170 SELECT count(requirement_header_id)
171 INTO l_count
172 FROM csp_requirement_headers
173 WHERE requirement_header_id = l_pvt_RQL_Tbl(I).requirement_header_id;
174 IF (l_count <= 0) THEN
175 fnd_message.set_name('CSP', 'CSP_INVALID_RQMT_HEADER');
176 fnd_message.set_token('HEADER_ID', to_char(l_pvt_RQL_Tbl(I).requirement_header_id), FALSE);
177 FND_MSG_PUB.ADD;
178 RAISE EXCP_USER_DEFINED;
179 END IF;
180 EXCEPTION
181 when no_Data_found then
182 null;
183 END;
184 END IF;
185 IF (l_pvt_RQL_Tbl(I).inventory_item_id IS NOT NULL
186 AND l_pvt_RQL_Tbl(I).inventory_item_id <> FND_API.G_MISS_NUM) THEN
187 BEGIN
188 SELECT count(inventory_item_id)
189 INTO l_count
190 FROM mtl_system_items_b
191 WHERE inventory_item_id = l_pvt_RQL_Tbl(I).inventory_item_id;
192 IF (l_count <= 0) THEN
193 FND_MESSAGE.SET_NAME ('INV', 'INV_INVALID_ITEM');
194 FND_MSG_PUB.ADD;
195 RAISE EXCP_USER_DEFINED;
196 END IF;
197 EXCEPTION
198 when no_Data_found then
199 null;
200 END;
201 END IF;
202 IF (l_pvt_RQL_Tbl(I).source_organization_id IS NOT NULL
203 AND l_pvt_RQL_Tbl(I).source_organization_id <> FND_API.G_MISS_NUM) THEN
204 BEGIN
205 SELECT count(organization_id)
206 INTO l_count
207 FROM mtl_parameters
208 WHERE organization_id = l_pvt_RQL_Tbl(I).source_organization_id;
209 IF (l_count <= 0) THEN
210 FND_MESSAGE.SET_NAME ('INV', 'INV_IOI_SOURCE_ORG_ID');
211 --FND_MESSAGE.SET_TOKEN ('PARAMETER', 'SOURCE_ORGANIZATION', FALSE);
212 FND_MSG_PUB.ADD;
213 RAISE EXCP_USER_DEFINED;
214 END IF;
215 EXCEPTION
216 when no_Data_found then
217 null;
218 END;
219 END IF;
220 IF (l_pvt_RQL_Tbl(I).uom_code IS NOT NULL
221 AND l_pvt_RQL_Tbl(I).uom_code <> FND_API.G_MISS_CHAR) THEN
222 BEGIN
223 SELECT count(uom_code)
224 INTO l_count
225 FROM mtl_item_uoms_view
226 WHERE inventory_item_id = l_pvt_RQL_Tbl(I).inventory_item_id
227 AND organization_id =
228 decode(nvl(l_pvt_RQL_Tbl(I).source_organization_id, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, organization_id, l_pvt_RQL_Tbl(I).source_organization_id)
229 AND uom_code = l_pvt_RQL_Tbl(I).uom_code;
230 IF (l_count <= 0) THEN
231 FND_MESSAGE.SET_NAME ('WSH', 'WSH_OI_INVALID_UOM');
232 FND_MSG_PUB.ADD;
233 RAISE EXCP_USER_DEFINED;
234 END IF;
235 EXCEPTION
236 when no_Data_found then
237 null;
238 END;
239 END IF;
240 IF (l_pvt_RQL_Tbl(I).revision IS NOT NULL
241 AND l_pvt_RQL_Tbl(I).revision <> FND_API.G_MISS_CHAR) THEN
242 BEGIN
243 SELECT count(revision)
244 INTO l_count
245 FROM mtl_item_revisions
246 WHERE inventory_item_id = l_pvt_RQL_Tbl(I).inventory_item_id
247 AND organization_id =
248 decode(nvl(l_pvt_RQL_Tbl(I).source_organization_id, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, organization_id, l_pvt_RQL_Tbl(I).source_organization_id)
249 AND revision = l_pvt_RQL_Tbl(I).revision;
250 IF (l_count <= 0) THEN
251 FND_MESSAGE.SET_NAME ('INV', 'INV_INT_REVCODE');
252 FND_MSG_PUB.ADD;
253 RAISE EXCP_USER_DEFINED;
254 END IF;
255 EXCEPTION
256 when no_Data_found then
257 null;
258 END;
259 END IF;
260 END LOOP;
261 END;
262
263 PROCEDURE Create_REQUIREMENT_LINES(
264 P_Api_Version_Number IN NUMBER,
265 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
266 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
267 P_RQL_Tbl IN RQL_Tbl_Type := G_MISS_RQL_Tbl,
268 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
269 X_REQUIREMENT_LINE_TBL OUT NOCOPY RQL_Tbl_Type,
270 X_Return_Status OUT NOCOPY VARCHAR2,
271 X_Msg_Count OUT NOCOPY NUMBER,
272 X_Msg_Data OUT NOCOPY VARCHAR2
273 )
274
275 IS
276 l_api_name CONSTANT VARCHAR2(30) := 'Create_REQUIREMENT_LINES';
277 l_api_version_number CONSTANT NUMBER := 1.0;
278 l_pvt_RQL_rec CSP_REQUIREMENT_LINES_PVT.Requirement_Line_Rec_Type;
279 l_pvt_RQL_tbl CSP_REQUIREMENT_LINES_PVT.Requirement_Line_Tbl_Type;
280 l_pvt_RQL_tbl1 CSP_REQUIREMENT_LINES_PVT.Requirement_Line_Tbl_Type;
281 BEGIN
282 -- Standard Start of API savepoint
283 SAVEPOINT CREATE_REQUIREMENT_LINES_PUB;
284
285 -- Standard call to check for call compatibility.
286 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
287 p_api_version_number,
288 l_api_name,
289 G_PKG_NAME)
290 THEN
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292 END IF;
293
294
295 -- Initialize message list if p_init_msg_list is set to TRUE.
296 IF FND_API.to_Boolean( p_init_msg_list )
297 THEN
298 FND_MSG_PUB.initialize;
299 END IF;
300
301
302 -- Debug Message
303 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'start');
304
305
306 -- Initialize API return status to SUCCESS
307 x_return_status := FND_API.G_RET_STS_SUCCESS;
308
309 --
310 -- API body
311 --
312
313 -- Debug Message
314 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'AS: Public API: Convert_RQL_Values_To_Ids');
315
316 -- Convert the values to ids
317 --
318 Convert_RQL_Values_To_Ids (
319 p_RQL_tbl => p_RQL_tbl,
320 x_pvt_RQL_tbl => l_pvt_RQL_tbl
321 );
322
323 Validate_Requirement_Lines(l_pvt_RQL_Tbl);
324
325 -- Calling Private package: Create_Requirement_Lines
326 -- Hint: Primary key needs to be returned
327 CSP_REQUIREMENT_LINES_PVT.Create_REQUIREMENT_LINES(
328 P_Api_Version_Number => 1.0,
329 P_Init_Msg_List => FND_API.G_FALSE,
330 P_Commit => FND_API.G_FALSE,
331 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
332 P_REQUIREMENT_LINE_Tbl => l_pvt_RQL_Tbl ,
333 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
334 X_REQUIREMENT_LINE_TBL => l_pvt_RQL_tbl1,
335 X_Return_Status => x_return_status,
336 X_Msg_Count => x_msg_count,
337 X_Msg_Data => x_msg_data);
338
339 -- Check return status from the above procedure call
340 IF x_return_status = FND_API.G_RET_STS_ERROR then
341 raise FND_API.G_EXC_ERROR;
342 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
343 raise FND_API.G_EXC_UNEXPECTED_ERROR;
344 END IF;
345
346 FOR I IN 1..l_pvt_rql_tbl.COUNT LOOP
347 x_requirement_line_tbl(I).REQUIREMENT_LINE_ID := l_pvt_Rql_tbl(I).REQUIREMENT_LINE_ID;
348 x_requirement_line_tbl(I).CREATED_BY := l_pvt_Rql_tbl(I).CREATED_BY;
349 x_requirement_line_tbl(I).CREATION_DATE := l_pvt_Rql_tbl(I).CREATION_DATE;
350 x_requirement_line_tbl(I).LAST_UPDATED_BY := l_pvt_Rql_tbl(I).LAST_UPDATED_BY;
351 x_requirement_line_tbl(I).LAST_UPDATE_DATE := l_pvt_Rql_tbl(I).LAST_UPDATE_DATE;
352 x_requirement_line_tbl(I).LAST_UPDATE_LOGIN := l_pvt_Rql_tbl(I).LAST_UPDATE_LOGIN;
353 x_requirement_line_tbl(I).REQUIREMENT_HEADER_ID := l_pvt_Rql_tbl(I).REQUIREMENT_HEADER_ID;
354 x_requirement_line_tbl(I).UOM_CODE := l_pvt_Rql_tbl(I).UOM_CODE;
355 x_requirement_line_tbl(I).REQUIRED_QUANTITY := l_pvt_Rql_tbl(I).REQUIRED_QUANTITY;
356 x_requirement_line_tbl(I).SHIP_COMPLETE_FLAG := l_pvt_Rql_tbl(I).SHIP_COMPLETE_FLAG;
357 x_requirement_line_tbl(I).LIKELIHOOD := l_pvt_Rql_tbl(I).LIKELIHOOD;
358 x_requirement_line_tbl(I).REVISION := l_pvt_Rql_tbl(I).REVISION;
359 x_requirement_line_tbl(I).SOURCE_ORGANIZATION_ID := l_pvt_Rql_tbl(I).SOURCE_ORGANIZATION_ID;
360 x_requirement_line_tbl(I).SOURCE_SUBINVENTORY := l_pvt_Rql_tbl(I).SOURCE_SUBINVENTORY;
361 x_requirement_line_tbl(I).ORDERED_QUANTITY := l_pvt_Rql_tbl(I).ORDERED_QUANTITY;
362 x_requirement_line_tbl(I).ORDER_LINE_ID := l_pvt_Rql_tbl(I).ORDER_LINE_ID;
363 x_requirement_line_tbl(I).RESERVATION_ID := l_pvt_Rql_tbl(I).RESERVATION_ID;
364 x_requirement_line_tbl(I).ATTRIBUTE_CATEGORY := l_pvt_Rql_tbl(I).ATTRIBUTE_CATEGORY;
365 x_requirement_line_tbl(I).ATTRIBUTE1 := l_pvt_Rql_tbl(I).ATTRIBUTE1;
366 x_requirement_line_tbl(I).ATTRIBUTE2 := l_pvt_Rql_tbl(I).ATTRIBUTE2;
367 x_requirement_line_tbl(I).ATTRIBUTE3 := l_pvt_Rql_tbl(I).ATTRIBUTE3;
368 x_requirement_line_tbl(I).ATTRIBUTE4 := l_pvt_Rql_tbl(I).ATTRIBUTE4;
369 x_requirement_line_tbl(I).ATTRIBUTE5 := l_pvt_Rql_tbl(I).ATTRIBUTE5;
370 x_requirement_line_tbl(I).ATTRIBUTE6 := l_pvt_Rql_tbl(I).ATTRIBUTE6;
371 x_requirement_line_tbl(I).ATTRIBUTE7 := l_pvt_Rql_tbl(I).ATTRIBUTE7;
372 x_requirement_line_tbl(I).ATTRIBUTE8 := l_pvt_Rql_tbl(I).ATTRIBUTE8;
373 x_requirement_line_tbl(I).ATTRIBUTE9 := l_pvt_Rql_tbl(I).ATTRIBUTE9;
374 x_requirement_line_tbl(I).ATTRIBUTE10 := l_pvt_Rql_tbl(I).ATTRIBUTE10;
375 x_requirement_line_tbl(I).ATTRIBUTE11 := l_pvt_Rql_tbl(I).ATTRIBUTE11;
376 x_requirement_line_tbl(I).ATTRIBUTE12 := l_pvt_Rql_tbl(I).ATTRIBUTE12;
377 x_requirement_line_tbl(I).ATTRIBUTE13 := l_pvt_Rql_tbl(I).ATTRIBUTE13;
378 x_requirement_line_tbl(I).ATTRIBUTE14 := l_pvt_Rql_tbl(I).ATTRIBUTE14;
379 x_requirement_line_tbl(I).ATTRIBUTE15 := l_pvt_Rql_tbl(I).ATTRIBUTE15;
380 x_requirement_line_tbl(I).ARRIVAL_DATE := l_pvt_Rql_tbl(I).ARRIVAL_DATE;
381 x_requirement_line_tbl(I).ITEM_SCRATCHPAD := l_pvt_Rql_tbl(I).ITEM_SCRATCHPAD;
382 x_requirement_line_tbl(I).SHIPPING_METHOD_CODE := l_pvt_Rql_tbl(I).SHIPPING_METHOD_CODE;
383 x_requirement_line_tbl(I).LOCAL_RESERVATION_ID := l_pvt_Rql_tbl(I).LOCAL_RESERVATION_ID;
384 x_requirement_line_tbl(I).SOURCED_FROM := l_pvt_Rql_tbl(I).SOURCED_FROM;
385
386 END LOOP;
387 --
388 -- End of API body.
389 --
390 -- Standard check for p_commit
391 IF FND_API.to_Boolean( p_commit )
392 THEN
393 COMMIT WORK;
394 END IF;
395
396
397 -- Debug Message
398 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'end');
399
400
401 -- Standard call to get message count and if count is 1, get message info.
402 FND_MSG_PUB.Count_And_Get
403 ( p_count => x_msg_count,
404 p_data => x_msg_data
405 );
406
407 EXCEPTION
408 WHEN FND_API.G_EXC_ERROR THEN
409 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
410 P_API_NAME => L_API_NAME
411 ,P_PKG_NAME => G_PKG_NAME
412 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
413 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
414 ,X_MSG_COUNT => X_MSG_COUNT
415 ,X_MSG_DATA => X_MSG_DATA
416 ,X_RETURN_STATUS => X_RETURN_STATUS);
417
418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
419 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
420 P_API_NAME => L_API_NAME
421 ,P_PKG_NAME => G_PKG_NAME
422 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
423 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
424 ,X_MSG_COUNT => X_MSG_COUNT
425 ,X_MSG_DATA => X_MSG_DATA
426 ,X_RETURN_STATUS => X_RETURN_STATUS);
427
428 WHEN OTHERS THEN
429 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
430 P_API_NAME => L_API_NAME
431 ,P_PKG_NAME => G_PKG_NAME
432 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
433 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
434 ,X_MSG_COUNT => X_MSG_COUNT
435 ,X_MSG_DATA => X_MSG_DATA
436 ,X_RETURN_STATUS => X_RETURN_STATUS);
437 End Create_REQUIREMENT_LINES;
438
439
440 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
441 PROCEDURE Update_REQUIREMENT_LINES(
442 P_Api_Version_Number IN NUMBER,
443 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
444 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
445 P_RQL_Tbl IN RQL_Tbl_Type := G_MISS_RQL_TBL,
446 X_Return_Status OUT NOCOPY VARCHAR2,
447 X_Msg_Count OUT NOCOPY NUMBER,
448 X_Msg_Data OUT NOCOPY VARCHAR2
449 )
450
451 IS
452 l_api_name CONSTANT VARCHAR2(30) := 'Update_REQUIREMENT_LINES';
453 l_api_version_number CONSTANT NUMBER := 1.0;
454 l_pvt_RQL_rec CSP_REQUIREMENT_LINES_PVT.Requirement_Line_Rec_Type;
455 l_pvt_RQL_Tbl CSP_REQUIREMENT_LINES_PVT.Requirement_Line_Tbl_Type;
456 BEGIN
457 -- Standard Start of API savepoint
458 SAVEPOINT UPDATE_Packlist_Headers_PUB;
459
460 -- Standard call to check for call compatibility.
461 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
462 p_api_version_number,
463 l_api_name,
464 G_PKG_NAME)
465 THEN
466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
467 END IF;
468
469
470 -- Initialize message list if p_init_msg_list is set to TRUE.
471 IF FND_API.to_Boolean( p_init_msg_list )
472 THEN
473 FND_MSG_PUB.initialize;
474 END IF;
475
476
477 -- Debug Message
478 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'start');
479
480
481 -- Initialize API return status to SUCCESS
482 x_return_status := FND_API.G_RET_STS_SUCCESS;
483
484 --
485 -- API body
486 --
487
488 -- Debug Message
489 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'AS: Public API: Convert_RQL_Values_To_Ids');
490
491 -- Convert the values to ids
492 --
493 Convert_RQL_Values_To_Ids (
494 p_RQL_Tbl => p_RQL_Tbl,
495 x_pvt_RQL_Tbl => l_pvt_RQL_Tbl
496 );
497
498 Validate_Requirement_Lines(l_pvt_RQL_Tbl);
499
500 CSP_REQUIREMENT_LINES_PVT.Update_REQUIREMENT_LINES(
501 P_Api_Version_Number => 1.0,
502 P_Init_Msg_List => FND_API.G_FALSE,
503 P_Commit => p_commit,
504 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
505 P_Requirement_Line_Tbl => l_pvt_RQL_Tbl ,
506 X_Return_Status => x_return_status,
507 X_Msg_Count => x_msg_count,
508 X_Msg_Data => x_msg_data);
509
510
511
512 -- Check return status from the above procedure call
513 IF x_return_status = FND_API.G_RET_STS_ERROR then
514 raise FND_API.G_EXC_ERROR;
515 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
516 raise FND_API.G_EXC_UNEXPECTED_ERROR;
517 END IF;
518
519 --
520 -- End of API body
521 --
522
523 -- Standard check for p_commit
524 IF FND_API.to_Boolean( p_commit )
525 THEN
526 COMMIT WORK;
527 END IF;
528
529
530 -- Debug Message
531 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'end');
532
533
534 -- Standard call to get message count and if count is 1, get message info.
535 FND_MSG_PUB.Count_And_Get
536 ( p_count => x_msg_count,
537 p_data => x_msg_data
538 );
539
540 EXCEPTION
541 WHEN FND_API.G_EXC_ERROR THEN
542 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
543 P_API_NAME => L_API_NAME
544 ,P_PKG_NAME => G_PKG_NAME
545 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
546 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
547 ,X_MSG_COUNT => X_MSG_COUNT
548 ,X_MSG_DATA => X_MSG_DATA
549 ,X_RETURN_STATUS => X_RETURN_STATUS);
550
551 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
552 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
553 P_API_NAME => L_API_NAME
554 ,P_PKG_NAME => G_PKG_NAME
555 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
556 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
557 ,X_MSG_COUNT => X_MSG_COUNT
558 ,X_MSG_DATA => X_MSG_DATA
559 ,X_RETURN_STATUS => X_RETURN_STATUS);
560
561 WHEN OTHERS THEN
562 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
563 P_API_NAME => L_API_NAME
564 ,P_PKG_NAME => G_PKG_NAME
565 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
566 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
567 ,X_MSG_COUNT => X_MSG_COUNT
568 ,X_MSG_DATA => X_MSG_DATA
569 ,X_RETURN_STATUS => X_RETURN_STATUS);
570 End Update_REQUIREMENT_LINES;
571
572
573 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
574 -- The Master delete procedure may not be needed depends on different business requirements.
575 PROCEDURE Delete_REQUIREMENT_LINES(
576 P_Api_Version_Number IN NUMBER,
577 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
578 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
579 P_RQL_Tbl IN RQL_Tbl_Type,
580 X_Return_Status OUT NOCOPY VARCHAR2,
581 X_Msg_Count OUT NOCOPY NUMBER,
582 X_Msg_Data OUT NOCOPY VARCHAR2
583 )
584
585 IS
586 l_api_name CONSTANT VARCHAR2(30) := 'Delete_REQUIREMENT_LINES';
587 l_api_version_number CONSTANT NUMBER := 1.0;
588 l_pvt_RQL_rec CSP_REQUIREMENT_LINES_PVT.Requirement_Line_Rec_Type;
589 l_pvt_RQL_Tbl CSP_REQUIREMENT_LINES_PVT.Requirement_Line_Tbl_Type;
590 l_count NUMBER;
591 EXCP_USER_DEFINED EXCEPTION;
592 BEGIN
593 -- Standard Start of API savepoint
594 SAVEPOINT DELETE_REQUIREMENT_LINES_PUB;
595
596 -- Standard call to check for call compatibility.
597 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
598 p_api_version_number,
599 l_api_name,
600 G_PKG_NAME)
601 THEN
602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
603 END IF;
604
605
606 -- Initialize message list if p_init_msg_list is set to TRUE.
607 IF FND_API.to_Boolean( p_init_msg_list )
608 THEN
609 FND_MSG_PUB.initialize;
610 END IF;
611
612
613 -- Debug Message
614 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'start');
615
616
617 -- Initialize API return status to SUCCESS
618 x_return_status := FND_API.G_RET_STS_SUCCESS;
619
620 --
621 -- API body
622 --
623
624 -- Debug Message
625 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'AS: Public API: Convert_RQL_Values_To_Ids');
626
627 -- Convert the values to ids
628 --
629 Convert_RQL_Values_To_Ids (
630 p_RQL_Tbl => p_RQL_Tbl,
631 x_pvt_RQL_Tbl => l_pvt_RQL_Tbl
632 );
633 -- Make sure there are no requirement line details before deleteing requirement lines
634 FOR I IN 1..P_RQL_TBL.COUNT LOOP
635 BEGIN
636 SELECT count(requirement_line_id)
637 INTO l_count
638 FROM csp_Req_line_details
639 where requirement_line_id = P_RQL_Tbl(I).requirement_line_id;
640
641 IF l_count > 0 THEN
642 FND_MESSAGE.SET_NAME ('CSP', 'CSP_RQMT_LINE_DELETE_ERROR');
643 FND_MESSAGE.SET_TOKEN ('PARAMETER', P_RQL_Tbl(I).requirement_line_id, FALSE);
644 FND_MSG_PUB.ADD;
645 RAISE EXCP_USER_DEFINED;
646 END IF;
647 EXCEPTION
648 WHEN NO_DATA_FOUND THEN
649 null;
650 END;
651 END LOOP;
652
653 CSP_REQUIREMENT_LINES_PVT.Delete_REQUIREMENT_LINES(
654 P_Api_Version_Number => 1.0,
655 P_Init_Msg_List => FND_API.G_FALSE,
656 P_Commit => p_commit,
657 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
658 P_Requirement_Line_Tbl => l_pvt_RQL_Tbl,
659 X_Return_Status => x_return_status,
660 X_Msg_Count => x_msg_count,
661 X_Msg_Data => x_msg_data);
662
663
664
665 -- Check return status from the above procedure call
666 IF x_return_status = FND_API.G_RET_STS_ERROR then
667 raise FND_API.G_EXC_ERROR;
668 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
669 raise FND_API.G_EXC_UNEXPECTED_ERROR;
670 END IF;
671
672 --
673 -- End of API body
674 --
675
676 -- Standard check for p_commit
677 IF FND_API.to_Boolean( p_commit )
678 THEN
679 COMMIT WORK;
680 END IF;
681
682
683 -- Debug Message
684 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'end');
685
686
687 -- Standard call to get message count and if count is 1, get message info.
688 FND_MSG_PUB.Count_And_Get
689 ( p_count => x_msg_count,
690 p_data => x_msg_data
691 );
692
693 EXCEPTION
694 WHEN FND_API.G_EXC_ERROR THEN
695 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
696 P_API_NAME => L_API_NAME
697 ,P_PKG_NAME => G_PKG_NAME
698 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
699 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
700 ,X_MSG_COUNT => X_MSG_COUNT
701 ,X_MSG_DATA => X_MSG_DATA
702 ,X_RETURN_STATUS => X_RETURN_STATUS);
703
704 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
705 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
706 P_API_NAME => L_API_NAME
707 ,P_PKG_NAME => G_PKG_NAME
708 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
709 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
710 ,X_MSG_COUNT => X_MSG_COUNT
711 ,X_MSG_DATA => X_MSG_DATA
712 ,X_RETURN_STATUS => X_RETURN_STATUS);
713
714 WHEN OTHERS THEN
715 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
716 P_API_NAME => L_API_NAME
717 ,P_PKG_NAME => G_PKG_NAME
718 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
719 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
720 ,X_MSG_COUNT => X_MSG_COUNT
721 ,X_MSG_DATA => X_MSG_DATA
722 ,X_RETURN_STATUS => X_RETURN_STATUS);
723 End Delete_REQUIREMENT_LINES;
724
725 END CSP_REQUIREMENT_LINES_PUB;