1 Package Body OE_PO_CALLBACK_UTIL As
2 /* $Header: OEXDPOCB.pls 115.0 99/07/26 11:07:23 porting shi $ */
3 --
4 -- The following are global variables.
5 --
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_PO_CALLBACK_UTIL';
8 --
9 G_OE_SOURCE_CODE Varchar2(25);
10 G_DROP_SHIP_SOURCE_ID Number;
11 G_HEADER_ID Number;
12 G_LINE_ID Number;
13 G_ORG_ID Number;
14 G_DESTINATION_ORGANIZATION_ID Number;
15 G_REQUISITION_HEADER_ID Number;
16 G_REQUISITION_LINE_ID Number;
17 G_PO_HEADER_ID Number;
18 G_PO_LINE_ID Number;
19 --
20 -- Function to validate the existence of a drop ship source id. When PO
21 -- calls this call back, a drop ship source id has to exist. There can
22 -- be duplicates on this source id if PO splits a requisition or a PO.
23 --
24 Function Valid_Drop_Ship_Source_ID(P_Drop_Ship_Source_ID In Number)
25 Return Boolean Is
26 Cursor L_Drop_Ship_Source_ID_Csr Is
27 Select Count(*)
28 From SO_DROP_SHIP_SOURCES
29 Where Drop_Ship_Source_ID = P_Drop_Ship_Source_ID ;
30 --
31 L_Drop_Ship_Source_Count Number(4);
32 Begin
33 Open L_Drop_Ship_Source_ID_Csr ;
34 Fetch L_Drop_Ship_Source_ID_Csr Into L_Drop_Ship_Source_Count ;
35 If L_Drop_Ship_Source_Count > 0 Then
36 Return True;
37 Else
38 Return False;
39 End If;
40 End Valid_Drop_Ship_Source_ID;
41 --
42 Procedure Insert_SO_Drop_Ship_Sources (P_Drop_Ship_Source_ID In Number,
43 P_Header_ID In Number,
44 P_Line_ID In Number,
45 P_Org_ID In Number,
46 P_Destination_Organization_ID In Number,
47 P_Requisition_Header_ID In Number,
48 P_Requisition_Line_ID In Number,
49 P_PO_Header_ID In Number,
50 P_PO_Line_ID In Number,
51 P_Line_Location_ID In Number, P_PO_Release_ID In Number
52 Default Null)
53 IS
54 Begin
55
56 Insert Into SO_Drop_Ship_Sources
57 (
58 Drop_Ship_Source_ID,
59 Header_ID,
60 Line_ID,
61 Org_ID,
62 Destination_Organization_ID,
63 Requisition_Header_ID,
64 Requisition_Line_ID,
65 PO_Header_ID,
66 PO_Line_ID,
67 Line_Location_ID,
68 PO_Release_ID,
69 Creation_Date,
70 Created_By,
71 Last_Update_Date,
72 Last_Updated_By
73 )
74 Values
75 (
76 P_Drop_Ship_Source_ID,
77 P_Header_ID,
78 P_Line_ID,
79 P_Org_ID,
80 P_Destination_Organization_ID,
81 P_Requisition_Header_ID,
82 P_Requisition_Line_ID,
83 P_PO_Header_ID,
84 P_PO_Line_ID,
85 P_Line_Location_ID,
86 P_PO_Release_ID,
87 Trunc(Sysdate),
88 Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1),
89 Trunc(Sysdate),
90 Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1)
91 );
92 End Insert_SO_Drop_Ship_Sources;
93 --
94 -- Update_Req_Info is an OE procedure that is called by Oracle Purchasing to
95 -- update requisition information for a drop shipped line. This procedure is
96 -- called in the Requisition Import (ReqImport) process of Oracle Purchasing
97 --
98 Procedure Update_Req_Info(P_API_Version In Number,
99 P_Return_Status Out Varchar2,
100 P_Msg_Count Out Number,
101 P_MSG_Data Out Varchar2,
102 P_Interface_Source_Code In Varchar2,
103 P_Interface_Source_Line_ID In Number,
104 P_Requisition_Header_ID In Number,
105 P_Requisition_Line_ID In Number) Is
106
107 L_API_Name Constant Varchar2(30) := 'UPDATE_REQ_INFO';
108 L_API_Version Constant Number := 1.0;
109 L_SQLCODE Number;
110 L_SQLERRM Varchar2(2000);
111
112 Cursor L_SO_Drop_Ship_Source_CSR (P_Interface_Source_Line_ID In Number)Is
113 Select Drop_Ship_Source_ID,
114 Header_ID,
115 Line_ID,
116 Org_ID,
117 Destination_Organization_ID,
118 Requisition_Header_ID,
119 Requisition_Line_ID,
120 PO_Header_ID,
121 PO_Line_ID
122 From SO_Drop_Ship_Sources
123 Where Drop_Ship_source_ID = P_Interface_Source_Line_ID
124 For Update of Requisition_Header_ID;
125 --
126 Begin
127 --
128 SavePoint Update_Req_Info_GRP;
129 --
130 IF Not FND_API.Compatible_API_Call (L_API_Version,
131 P_API_Version,
132 L_API_Name,
133 G_PKG_Name) Then
134 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
135 End If;
136 --
137 FND_PROFILE.Get('SO_SOURCE_CODE',G_OE_SOURCE_CODE); -- Returns OE's profile
138 -- value for SO_SOURCE_CODE
139 If P_Interface_Source_Code = G_OE_Source_Code Then
140 Null;
141 Else -- Interface source code is not an OE interface source code
142 P_Return_Status := FND_API.G_RET_STS_SUCCESS;
143 Return;
144 End If;
145 --
146 If Valid_Drop_Ship_Source_Id(P_INTERFACE_SOURCE_LINE_ID) Then
147 Null;
148 Else -- The drop ship source ID does not exist. Serious Error!
149 Rollback to Update_Req_INfo_GRP;
150 P_Return_Status := FND_API.G_RET_STS_ERROR;
151 Return;
152 End If;
153 --
154 Open L_SO_Drop_Ship_Source_Csr(P_INTERFACE_SOURCE_LINE_ID);
155 Fetch L_SO_Drop_Ship_Source_Csr Into G_Drop_Ship_Source_ID,
156 G_Header_ID,
157 G_Line_ID,
158 G_Org_ID,
159 G_Destination_Organization_ID,
160 G_Requisition_Header_ID,
161 G_Requisition_Line_ID,
162 G_PO_Header_ID,
163 G_PO_Line_ID;
164 --
165 If L_SO_Drop_Ship_Source_Csr%Found Then
166 If G_Requisition_Header_ID Is Null Then -- Requisition being updated for the
167 -- first time
168 Update SO_Drop_Ship_Sources
169 Set Requisition_Header_ID = P_Requisition_Header_ID,
170 Requisition_LIne_ID = P_Requisition_Line_ID,
171 Last_Update_Date = Trunc(Sysdate),
172 Last_Updated_By = Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1)
173 Where Current of L_SO_Drop_Ship_SOurce_Csr;
174 Else -- Another requisition for same order line.
175 Insert_SO_Drop_Ship_Sources ( P_Drop_Ship_Source_ID => G_Drop_Ship_Source_ID,
176 P_Header_ID => G_Header_ID,
177 P_Line_ID => G_Line_ID,
178 P_Org_ID => G_Org_ID,
179 P_Destination_Organization_ID =>
180 G_Destination_Organization_ID,
181 P_Requisition_Header_ID =>
182 P_Requisition_Header_ID,
183 P_Requisition_LIne_ID => P_Requisition_Line_ID,
184 P_PO_Header_ID => Null,
185 P_PO_Line_ID => Null,
186 P_Line_Location_ID => Null);
187 End If;
188 End If;
189 Close L_SO_Drop_Ship_Source_Csr;
190 P_Return_Status := FND_API.G_RET_STS_SUCCESS;
191 Exception
192 When Others Then
193 Rollback to Update_Req_INfo_Grp;
194 P_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
195 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) Then
196 FND_MSG_PUB.Add_Exc_Msg(G_PKG_Name,L_API_Name);
197 End If;
198 FND_MSG_PUB.Count_And_Get (P_Count => P_MSG_Count,
199 P_Data => P_MSG_Data);
200 End Update_Req_Info;
201 --
202 --
203 -- Update_All_Reqs_In_Process is an OE procedure that is called by
204 -- Oracle Purchasing to update requisition information for a drop shipped line.
205 -- This procedure is called in the Requisition Import (ReqImport) process of
206 -- Oracle Purchasing.
207 Procedure Update_All_Reqs_In_Process(P_API_Version In Number,
208 P_Return_Status Out Varchar2,
209 P_Msg_Count Out Number,
210 P_MSG_Data Out Varchar2,
211 P_Requisition_Header_ID In Number,
212 P_Request_Id In Number,
213 P_Process_Flag In Varchar2) Is
214
215 L_API_Name Constant Varchar2(30) := 'UPDATE_ALL_REQS_IN_PROCESS';
216 L_API_Version Constant Number := 1.0;
217
218 L_Interface_Source_Code Varchar2(25);
219 L_Interface_Source_Line_Id Number;
220 L_Requisition_Header_Id Number;
221 L_Requisition_Line_Id Number;
222
223 L_Return_Status Varchar2(1);
224 L_Msg_Count Number;
225 L_MSG_Data Varchar2(1000);
226
227 Cursor L_PO_Req_Interface_CSR Is
228 Select Interface_Source_COde,Interface_source_line_id,
229 requisition_header_id,requisition_line_id
230 from po_requisitions_interface
231 WHERE requisition_header_id = P_Requisition_Header_ID
232 AND process_flag = P_Process_Flag
233 AND request_id = P_Request_Id;
234 Begin
235 --
236 SavePoint UPDATE_ALL_REQS_IN_PROCESS;
237 --
238 IF Not FND_API.Compatible_API_Call (L_API_Version,
239 P_API_Version,
240 L_API_Name,
241 G_PKG_Name) Then
242 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
243 End If;
244 --
245 Open L_PO_Req_Interface_CSR;
246 Loop
247 Fetch L_PO_Req_Interface_CSR Into L_Interface_Source_Code,
248 L_Interface_Source_Line_Id,
249 L_Requisition_Header_Id,
250 L_Requisition_Line_Id;
251 If L_PO_Req_Interface_CSR%NotFound Then
252 P_Return_Status := FND_API.G_RET_STS_SUCCESS;
253 Exit;
254 End If;
255
256 Update_Req_Info(1.0,
257 L_Return_Status,
258 L_Msg_Count,
259 L_MSG_Data,
260 L_Interface_Source_Code,
261 L_Interface_Source_Line_ID,
262 L_Requisition_Header_ID,
263 L_Requisition_Line_ID);
264 P_Return_Status := L_Return_Status ;
265 If L_Return_Status = FND_API.G_RET_STS_SUCCESS Then
266 Null;
267 Else
268 Close L_PO_Req_Interface_CSR;
269 Exit;
270 End If;
271 End Loop;
272 Close L_PO_Req_Interface_CSR;
273
274 Exception
275 When Others Then
276 Rollback to UPDATE_ALL_REQS_IN_PROCESS;
277 P_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
278 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) Then
279 FND_MSG_PUB.Add_Exc_Msg(G_PKG_Name,L_API_Name);
280 End If;
281 FND_MSG_PUB.Count_And_Get (P_Count => P_MSG_Count,
282 P_Data => P_MSG_Data);
283 End Update_All_Reqs_In_Process;
284
285 --
286 -- Update_PO_Info is an OE procedure that is called by Oracle Purchasing to
287 -- update purchase order information for a drop shipped line. This procedure
288 -- is called in the Auto create process of Oracle Purchasing
289 Procedure Update_PO_Info ( P_API_Version In Number,
290 P_Return_Status Out Varchar2,
291 P_Msg_Count Out Number,
292 P_MSG_Data Out Varchar2,
293 P_Req_Header_ID In Number,
294 P_Req_Line_ID In Number,
295 P_PO_Header_Id In Number,
296 P_PO_Line_Id In Number,
297 P_Line_Location_ID In Number,
298 P_PO_Release_ID In Number
299 Default Null) Is
300
301 L_API_Name Constant Varchar2(30) := 'UPDATE_PO_INFO';
302 L_API_Version Constant Number := 1.0;
303
304 Cursor L_SO_Drop_Ship_Source_CSR (P_Req_Line_ID In Number,
305 P_Req_Header_Id In Number)Is
306 Select Drop_Ship_Source_ID,
307 Header_ID,
308 Line_ID,
309 Org_ID,
310 Destination_Organization_ID,
311 Requisition_Header_ID,
312 Requisition_Line_ID,
313 PO_Header_ID,
314 PO_Line_ID
315 From SO_Drop_Ship_Sources
316 Where Requisition_Line_ID = P_Req_Line_ID
317 And Requisition_Header_ID = P_Req_Header_ID
318 For Update of PO_Header_ID;
319 Begin
320 SavePoint Update_PO_Info_GRP;
321 IF Not FND_API.Compatible_API_Call (L_API_Version,
322 P_API_Version,
323 L_API_Name,
324 G_PKG_Name) THen
325 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
326 End If;
327 --
328 Open L_SO_Drop_Ship_SOurce_Csr(P_REQ_LINE_ID,P_REQ_HEADER_ID);
329 Fetch L_So_Drop_Ship_Source_Csr Into G_Drop_Ship_Source_ID,
330 G_Header_ID,
331 G_Line_ID,
332 G_Org_ID,
333 G_Destination_ORganization_ID,
334 G_Requisition_Header_ID,
335 G_Requisition_Line_ID,
336 G_PO_Header_ID,
337 G_PO_Line_ID;
338 If L_SO_Drop_Ship_Source_CSR%Found Then -- P_Req_Line_Id Is a drop ship
339 -- line id
340 If G_Requisition_Header_ID Is Not Null -- PO being updated for the
341 And G_PO_Header_ID Is Null Then -- first time
342 Update SO_Drop_Ship_Sources
343 Set PO_Header_ID = P_PO_Header_ID,
344 PO_Line_ID = P_PO_Line_ID,
345 Line_Location_ID = P_Line_Location_ID,
346 PO_Release_ID = Nvl(P_PO_Release_Id,PO_Release_ID),
347 Last_Update_Date = Trunc(Sysdate),
348 Last_Updated_By = Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1)
349 Where Current of L_SO_Drop_Ship_Source_Csr;
350 Else -- Another PO for same order line.
351 Insert_SO_Drop_Ship_Sources (P_Drop_Ship_Source_ID => G_Drop_Ship_Source_ID,
352 P_Header_ID => G_Header_ID,
353 P_Line_ID => G_Line_ID,
354 P_Org_ID => G_Org_ID,
355 P_Destination_Organization_ID =>
356 G_Destination_Organization_ID,
357 P_Requisition_Header_ID =>
358 G_Requisition_Header_ID,
359 P_Requisition_Line_ID => G_Requisition_Line_ID,
360 P_PO_Header_ID => P_PO_Header_ID,
361 P_PO_Line_ID => P_PO_Line_ID,
362 P_Line_Location_ID => P_Line_Location_ID,
363 P_PO_Release_Id => P_PO_Release_ID);
364 End If;
365 End If;
366 Close L_SO_Drop_Ship_Source_Csr;
367 P_Return_Status := FND_API.G_RET_STS_SUCCESS;
368 Exception
369 When Others Then
370 Rollback to Update_PO_Info_GRP;
371 P_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
372 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) Then
373 FND_MSG_PUB.Add_Exc_Msg(G_PKG_Name,L_API_Name);
374 End If;
375 FND_MSG_PUB.Count_And_Get (P_Count => P_MSG_Count,
376 P_Data => P_MSG_Data);
377 End Update_PO_Info;
378 --
379 Function Req_Line_Is_Drop_Ship(P_Req_Line_Id In Number)
380 Return Number Is
381 L_Line_Id Number;
382 Cursor L_Drop_Ship_Csr Is
383 Select Line_Id
384 From SO_Drop_Ship_Sources
385 Where Requisition_Line_ID = P_Req_Line_ID;
386 Begin
387 Open L_Drop_Ship_Csr;
388
389 Fetch L_Drop_Ship_Csr Into L_Line_Id;
390
391 If L_Drop_Ship_Csr%NotFound Then -- Req Line Id is not a "drop Ship" Req
392
393 Close L_Drop_Ship_Csr;
394 Return Null;
395
396 Else -- Req Line Id is a "drop Ship" Req
397
398 Close L_Drop_Ship_Csr;
399 Return L_Line_Id;
400
401 End If;
402
403 End Req_Line_Is_Drop_Ship;
404 --
405 Function PO_Line_Location_Is_Drop_Ship(P_PO_Line_Location_Id In Number)
406 Return Number Is
407 L_Line_Id Number;
408 Cursor L_Drop_Ship_Csr Is
409 Select Line_Id
410 From SO_Drop_Ship_Sources
411 Where Line_Location_Id = P_PO_Line_Location_ID;
412 Begin
413 Open L_Drop_Ship_Csr;
414
415 Fetch L_Drop_Ship_Csr Into L_Line_Id;
416
417 If L_Drop_Ship_Csr%NotFound Then -- PO Line Location Id is not a "drop Ship"
418 -- Line location
419
420 Close L_Drop_Ship_Csr;
421 Return Null;
422
423 Else -- PO Line Location Id is a "drop Ship" Line Location
424
425 Close L_Drop_Ship_Csr;
426 Return L_Line_Id;
427
428 End If;
429
430 End PO_Line_Location_Is_Drop_Ship;
431
432 End OE_PO_CALLBACK_UTIL;
[Home] [Help]