[Home] [Help]
PACKAGE BODY: APPS.RCV_SHIPMENT_HEADERS_PKG
Source
1 PACKAGE BODY RCV_SHIPMENT_HEADERS_PKG as
2 /* $Header: RCVTISHB.pls 120.2 2006/03/15 03:46:29 atiwari noship $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Shipment_Header_Id IN OUT NOCOPY NUMBER,
7 X_Last_Update_Date DATE,
8 X_Last_Updated_By NUMBER,
9 X_Creation_Date DATE,
10 X_Created_By NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Receipt_Source_Code VARCHAR2,
13 X_Vendor_Id NUMBER,
14 X_Vendor_Site_Id NUMBER,
15 X_Organization_Id NUMBER,
16 X_ship_to_org_id NUMBER,
17 X_Shipment_Num VARCHAR2,
18 X_Receipt_Num IN OUT NOCOPY VARCHAR2,
19 X_Ship_To_Location_Id NUMBER,
20 X_Bill_Of_Lading VARCHAR2,
21 X_Packing_Slip VARCHAR2,
22 X_Shipped_Date DATE,
23 X_Freight_Carrier_Code VARCHAR2,
24 X_Expected_Receipt_Date DATE,
25 X_Employee_Id NUMBER,
26 X_Num_Of_Containers NUMBER,
27 X_Waybill_Airbill_Num VARCHAR2,
28 X_Comments VARCHAR2,
29 X_Attribute_Category VARCHAR2,
30 X_Attribute1 VARCHAR2,
31 X_Attribute2 VARCHAR2,
32 X_Attribute3 VARCHAR2,
33 X_Attribute4 VARCHAR2,
34 X_Attribute5 VARCHAR2,
35 X_Attribute6 VARCHAR2,
36 X_Attribute7 VARCHAR2,
37 X_Attribute8 VARCHAR2,
38 X_Attribute9 VARCHAR2,
39 X_Attribute10 VARCHAR2,
40 X_Attribute11 VARCHAR2,
41 X_Attribute12 VARCHAR2,
42 X_Attribute13 VARCHAR2,
43 X_Attribute14 VARCHAR2,
44 X_Attribute15 VARCHAR2,
45 X_Ussgl_Transaction_Code VARCHAR2,
46 X_Government_Context VARCHAR2,
47 X_Request_Id NUMBER,
48 X_Program_Application_Id NUMBER,
49 X_Program_Id NUMBER,
50 X_Program_Update_Date DATE,
51 X_customer_id NUMBER,
52 X_customer_site_id NUMBER
53
54 ) IS
55 CURSOR C IS SELECT rowid FROM RCV_SHIPMENT_HEADERS
56 WHERE shipment_header_id = X_Shipment_Header_Id;
57
58 CURSOR C2 IS SELECT rcv_shipment_headers_s.nextval FROM sys.dual;
59
60 X_RECEIPT_CODE VARCHAR2(25);
61 X_TEMP_RECEIPT_NUM VARCHAR(30);
62 X_RECEIPT_EXISTS NUMBER := 0;
63
64 BEGIN
65 if (X_Shipment_Header_Id is NULL) then
66 OPEN C2;
67 FETCH C2 INTO X_Shipment_Header_Id;
68 CLOSE C2;
69 end if;
70
71
72 select user_defined_receipt_num_code
73 into x_receipt_code
74 from rcv_parameters
75 where organization_id = x_ship_to_org_id;
76
77
78 /* hvadlamu : to make the receipt numbers unique across orgs
79 SELECT USER_DEFINED_RECEIPT_NUM_CODE
80 INTO X_RECEIPT_CODE
81 FROM PO_SYSTEM_PARAMETERS; */
82
83 IF (NVL(X_RECEIPT_CODE, 'MANUAL') = 'AUTOMATIC') THEN
84
85 /*
86 ** Bug#4913999 - START
87 ** Changed the code to get the next unique receipt number
88 ** when the receipt number in receiving options form is not unique as in
89 ** the case of ROI rather than displaying message to the user
90 */
91
92 select to_char(next_receipt_num + 1)
93 into X_temp_receipt_num
94 from rcv_parameters
95 where organization_id = x_ship_to_org_id
96 FOR UPDATE OF next_receipt_num;
97
98 LOOP
99 SELECT COUNT(*)
100 INTO X_receipt_exists
101 FROM rcv_shipment_headers rsh
102 WHERE receipt_num = X_temp_receipt_num
103 AND ship_to_org_id = x_ship_to_org_id;
104
105 IF X_receipt_exists = 0 THEN
106
107 update rcv_parameters
108 set next_receipt_num = X_temp_receipt_num
109 where organization_id = x_ship_to_org_id;
110
111 EXIT;
112 ELSE
113 X_temp_receipt_num := TO_CHAR(TO_NUMBER(X_temp_receipt_num) + 1);
114 END IF;
115 END LOOP;
116 /* Bug#4913999 - END */
117
118 X_receipt_num := X_temp_receipt_num;
119
120 ELSE
121
122 X_temp_receipt_num := X_receipt_num;
123
124 END IF;
125
126 INSERT INTO RCV_SHIPMENT_HEADERS(
127 shipment_header_id,
128 last_update_date,
129 last_updated_by,
130 creation_date,
131 created_by,
132 last_update_login,
133 receipt_source_code,
134 vendor_id,
135 vendor_site_id,
136 organization_id,
137 ship_to_org_id,
138 shipment_num,
139 receipt_num,
140 ship_to_location_id,
141 bill_of_lading,
142 packing_slip,
143 shipped_date,
144 freight_carrier_code,
145 expected_receipt_date,
146 employee_id,
147 num_of_containers,
148 waybill_airbill_num,
149 comments,
150 attribute_category,
151 attribute1,
152 attribute2,
153 attribute3,
154 attribute4,
155 attribute5,
156 attribute6,
157 attribute7,
158 attribute8,
159 attribute9,
160 attribute10,
161 attribute11,
162 attribute12,
163 attribute13,
164 attribute14,
165 attribute15,
166 ussgl_transaction_code,
167 government_context,
168 request_id,
169 program_application_id,
170 program_id,
171 program_update_date,
172 customer_id,
173 customer_site_id
174 ) VALUES (
175 X_Shipment_Header_Id,
176 X_Last_Update_Date,
177 X_Last_Updated_By,
178 X_Creation_Date,
179 X_Created_By,
180 X_Last_Update_Login,
181 X_Receipt_Source_Code,
182 X_Vendor_Id,
183 X_Vendor_Site_Id,
184 X_Organization_Id,
185 X_Ship_to_org_id,
186 X_Shipment_Num,
187 X_Temp_Receipt_Num,
188 X_Ship_To_Location_Id,
189 X_Bill_Of_Lading,
190 X_Packing_Slip,
191 X_Shipped_Date,
192 X_Freight_Carrier_Code,
193 X_Expected_Receipt_Date,
194 X_Employee_Id,
195 X_Num_Of_Containers,
196 X_Waybill_Airbill_Num,
197 X_Comments,
198 X_Attribute_Category,
199 X_Attribute1,
200 X_Attribute2,
201 X_Attribute3,
202 X_Attribute4,
203 X_Attribute5,
204 X_Attribute6,
205 X_Attribute7,
206 X_Attribute8,
207 X_Attribute9,
208 X_Attribute10,
209 X_Attribute11,
210 X_Attribute12,
211 X_Attribute13,
212 X_Attribute14,
213 X_Attribute15,
214 X_Ussgl_Transaction_Code,
215 X_Government_Context,
216 X_Request_Id,
217 X_Program_Application_Id,
221 X_customer_site_id
218 X_Program_Id,
219 X_Program_Update_Date,
220 X_customer_id,
222 );
223
224 OPEN C;
225 FETCH C INTO X_Rowid;
226 if (C%NOTFOUND) then
227 CLOSE C;
228 Raise NO_DATA_FOUND;
229 end if;
230 CLOSE C;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 RAISE;
235
236 END Insert_Row;
237
238
239
240 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
241 X_Shipment_Header_Id NUMBER,
242 X_Receipt_Source_Code VARCHAR2,
243 X_Vendor_Id NUMBER,
244 X_Vendor_Site_Id NUMBER,
245 X_Organization_Id NUMBER,
246 X_Shipment_Num VARCHAR2,
247 X_Receipt_Num VARCHAR2,
248 X_Ship_To_Location_Id NUMBER,
249 X_Bill_Of_Lading VARCHAR2,
250 X_Packing_Slip VARCHAR2,
251 X_Shipped_Date DATE,
252 X_Freight_Carrier_Code VARCHAR2,
253 X_Expected_Receipt_Date DATE,
254 X_Employee_Id NUMBER,
255 X_Num_Of_Containers NUMBER,
256 X_Waybill_Airbill_Num VARCHAR2,
257 X_Comments VARCHAR2,
258 X_Attribute_Category VARCHAR2,
259 X_Attribute1 VARCHAR2,
260 X_Attribute2 VARCHAR2,
261 X_Attribute3 VARCHAR2,
262 X_Attribute4 VARCHAR2,
263 X_Attribute5 VARCHAR2,
264 X_Attribute6 VARCHAR2,
265 X_Attribute7 VARCHAR2,
266 X_Attribute8 VARCHAR2,
267 X_Attribute9 VARCHAR2,
268 X_Attribute10 VARCHAR2,
269 X_Attribute11 VARCHAR2,
270 X_Attribute12 VARCHAR2,
271 X_Attribute13 VARCHAR2,
272 X_Attribute14 VARCHAR2,
273 X_Attribute15 VARCHAR2,
274 X_Ussgl_Transaction_Code VARCHAR2,
275 X_Government_Context VARCHAR2,
276 X_Request_Id NUMBER,
277 X_Program_Application_Id NUMBER,
278 X_Program_Id NUMBER,
279 X_Program_Update_Date DATE,
280 X_customer_id NUMBER,
281 X_customer_site_id NUMBER
282
283 ) IS
284 CURSOR C IS
285 SELECT *
286 FROM RCV_SHIPMENT_HEADERS
287 WHERE rowid = X_Rowid
288 FOR UPDATE of Shipment_Header_Id NOWAIT;
289 Recinfo C%ROWTYPE;
290 BEGIN
291 OPEN C;
292 FETCH C INTO Recinfo;
293 if (C%NOTFOUND) then
294 CLOSE C;
295 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
296 APP_EXCEPTION.Raise_Exception;
297 end if;
298 CLOSE C;
299 if (
300
301 (Recinfo.shipment_header_id = X_Shipment_Header_Id)
302 AND (Recinfo.receipt_source_code = X_Receipt_Source_Code)
303 AND ( (Recinfo.vendor_id = X_Vendor_Id)
304 OR ( (Recinfo.vendor_id IS NULL)
305 AND (X_Vendor_Id IS NULL)))
306 AND ( (Recinfo.vendor_site_id = X_Vendor_Site_Id)
307 OR ( (Recinfo.vendor_site_id IS NULL)
308 AND (X_Vendor_Site_Id IS NULL)))
309 AND ( (Recinfo.organization_id = X_Organization_Id)
310 OR ( (Recinfo.organization_id IS NULL)
311 AND (X_Organization_Id IS NULL)))
312 AND ( (Recinfo.shipment_num = X_Shipment_Num)
313 OR ( (Recinfo.shipment_num IS NULL)
314 AND (X_Shipment_Num IS NULL)))
315 AND ( (Recinfo.receipt_num = X_Receipt_Num)
316 OR ( (Recinfo.receipt_num IS NULL)
317 AND (X_Receipt_Num IS NULL)))
318 AND ( (Recinfo.ship_to_location_id = X_Ship_To_Location_Id)
319 OR ( (Recinfo.ship_to_location_id IS NULL)
320 AND (X_Ship_To_Location_Id IS NULL)))
321 AND ( (Recinfo.bill_of_lading = X_Bill_Of_Lading)
322 OR ( (Recinfo.bill_of_lading IS NULL)
323 AND (X_Bill_Of_Lading IS NULL)))
324 AND ( (Recinfo.packing_slip = X_Packing_Slip)
325 OR ( (Recinfo.packing_slip IS NULL)
326 AND (X_Packing_Slip IS NULL)))
327 AND ( (Recinfo.shipped_date = X_Shipped_Date)
328 OR ( (Recinfo.shipped_date IS NULL)
329 AND (X_Shipped_Date IS NULL)))
330 AND ( (Recinfo.freight_carrier_code = X_Freight_Carrier_Code)
331 OR ( (Recinfo.freight_carrier_code IS NULL)
332 AND (X_Freight_Carrier_Code IS NULL)))
333 AND ( (Recinfo.expected_receipt_date = X_Expected_Receipt_Date)
334 OR ( (Recinfo.expected_receipt_date IS NULL)
335 AND (X_Expected_Receipt_Date IS NULL)))
336 AND ( (Recinfo.employee_id = X_Employee_Id)
337 OR ( (Recinfo.employee_id IS NULL)
341 AND (X_Num_Of_Containers IS NULL)))
338 AND (X_Employee_Id IS NULL)))
339 AND ( (Recinfo.num_of_containers = X_Num_Of_Containers)
340 OR ( (Recinfo.num_of_containers IS NULL)
342 AND ( (Recinfo.waybill_airbill_num = X_Waybill_Airbill_Num)
343 OR ( (Recinfo.waybill_airbill_num IS NULL)
344 AND (X_Waybill_Airbill_Num IS NULL)))
345 AND ( (Recinfo.comments = X_Comments)
346 OR ( (Recinfo.comments IS NULL)
347 AND (X_Comments IS NULL)))
348 AND ( (Recinfo.attribute_category = X_Attribute_Category)
349 OR ( (Recinfo.attribute_category IS NULL)
350 AND (X_Attribute_Category IS NULL)))
351 AND ( (Recinfo.attribute1 = X_Attribute1)
352 OR ( (Recinfo.attribute1 IS NULL)
353 AND (X_Attribute1 IS NULL)))
354 AND ( (Recinfo.attribute2 = X_Attribute2)
355 OR ( (Recinfo.attribute2 IS NULL)
356 AND (X_Attribute2 IS NULL)))
357 AND ( (Recinfo.attribute3 = X_Attribute3)
358 OR ( (Recinfo.attribute3 IS NULL)
359 AND (X_Attribute3 IS NULL)))
360 AND ( (Recinfo.attribute4 = X_Attribute4)
361 OR ( (Recinfo.attribute4 IS NULL)
362 AND (X_Attribute4 IS NULL)))
363 AND ( (Recinfo.attribute5 = X_Attribute5)
364 OR ( (Recinfo.attribute5 IS NULL)
365 AND (X_Attribute5 IS NULL)))
366 AND ( (Recinfo.attribute6 = X_Attribute6)
367 OR ( (Recinfo.attribute6 IS NULL)
368 AND (X_Attribute6 IS NULL)))
369 AND ( (Recinfo.attribute7 = X_Attribute7)
370 OR ( (Recinfo.attribute7 IS NULL)
371 AND (X_Attribute7 IS NULL)))
372 AND ( (Recinfo.attribute8 = X_Attribute8)
373 OR ( (Recinfo.attribute8 IS NULL)
374 AND (X_Attribute8 IS NULL)))
375 AND ( (Recinfo.attribute9 = X_Attribute9)
376 OR ( (Recinfo.attribute9 IS NULL)
377 AND (X_Attribute9 IS NULL)))
378 AND ( (Recinfo.attribute10 = X_Attribute10)
379 OR ( (Recinfo.attribute10 IS NULL)
380 AND (X_Attribute10 IS NULL)))
381 AND ( (Recinfo.attribute11 = X_Attribute11)
382 OR ( (Recinfo.attribute11 IS NULL)
383 AND (X_Attribute11 IS NULL)))
384 AND ( (Recinfo.attribute12 = X_Attribute12)
385 OR ( (Recinfo.attribute12 IS NULL)
386 AND (X_Attribute12 IS NULL)))
387 AND ( (Recinfo.attribute13 = X_Attribute13)
388 OR ( (Recinfo.attribute13 IS NULL)
389 AND (X_Attribute13 IS NULL)))
390 AND ( (Recinfo.attribute14 = X_Attribute14)
391 OR ( (Recinfo.attribute14 IS NULL)
392 AND (X_Attribute14 IS NULL)))
393 AND ( (Recinfo.attribute15 = X_Attribute15)
394 OR ( (Recinfo.attribute15 IS NULL)
395 AND (X_Attribute15 IS NULL)))
396 AND ( (Recinfo.ussgl_transaction_code = X_Ussgl_Transaction_Code)
397 OR ( (Recinfo.ussgl_transaction_code IS NULL)
398 AND (X_Ussgl_Transaction_Code IS NULL)))
399 AND ( (Recinfo.government_context = X_Government_Context)
400 OR ( (Recinfo.government_context IS NULL)
401 AND (X_Government_Context IS NULL)))
402 AND ( (Recinfo.request_id = X_Request_Id)
403 OR ( (Recinfo.request_id IS NULL)
404 AND (X_Request_Id IS NULL)))
405 AND ( (Recinfo.program_application_id = X_Program_Application_Id)
406 OR ( (Recinfo.program_application_id IS NULL)
407 AND (X_Program_Application_Id IS NULL)))
408 AND ( (Recinfo.program_id = X_Program_Id)
409 OR ( (Recinfo.program_id IS NULL)
410 AND (X_Program_Id IS NULL)))
411 AND ( (Recinfo.program_update_date = X_Program_Update_Date)
412 OR ( (Recinfo.program_update_date IS NULL)
413 AND (X_Program_Update_Date IS NULL)))
414 AND ( (Recinfo.customer_id = X_Customer_Id)
415 OR ( (Recinfo.customer_id IS NULL)
416 AND (X_Customer_Id IS NULL)))
417 AND ( (Recinfo.customer_site_id = X_Customer_Site_Id)
418 OR ( (Recinfo.customer_site_id IS NULL)
419 AND (X_Customer_Site_Id IS NULL)))
420 ) then
421 return;
422 else
423 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
424 APP_EXCEPTION.RAISE_EXCEPTION;
425 end if;
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 RAISE;
430
431 END Lock_Row;
432
433
434
435 PROCEDURE Update_Row(X_Rowid VARCHAR2,
436 X_Shipment_Header_Id NUMBER,
437 X_Last_Update_Date DATE,
438 X_Last_Updated_By NUMBER,
439 X_Last_Update_Login NUMBER,
440 X_Receipt_Source_Code VARCHAR2,
441 X_Vendor_Id NUMBER,
445 X_Shipment_Num VARCHAR2,
442 X_Vendor_Site_Id NUMBER,
443 X_Organization_Id NUMBER,
444 X_ship_to_org_id NUMBER,
446 X_Receipt_Num VARCHAR2,
447 X_Ship_To_Location_Id NUMBER,
448 X_Bill_Of_Lading VARCHAR2,
449 X_Packing_Slip VARCHAR2,
450 X_Shipped_Date DATE,
451 X_Freight_Carrier_Code VARCHAR2,
452 X_Expected_Receipt_Date DATE,
453 X_Employee_Id NUMBER,
454 X_Num_Of_Containers NUMBER,
455 X_Waybill_Airbill_Num VARCHAR2,
456 X_Comments VARCHAR2,
457 X_Attribute_Category VARCHAR2,
458 X_Attribute1 VARCHAR2,
459 X_Attribute2 VARCHAR2,
460 X_Attribute3 VARCHAR2,
461 X_Attribute4 VARCHAR2,
462 X_Attribute5 VARCHAR2,
463 X_Attribute6 VARCHAR2,
464 X_Attribute7 VARCHAR2,
465 X_Attribute8 VARCHAR2,
466 X_Attribute9 VARCHAR2,
467 X_Attribute10 VARCHAR2,
468 X_Attribute11 VARCHAR2,
469 X_Attribute12 VARCHAR2,
470 X_Attribute13 VARCHAR2,
471 X_Attribute14 VARCHAR2,
472 X_Attribute15 VARCHAR2,
473 X_Ussgl_Transaction_Code VARCHAR2,
474 X_Government_Context VARCHAR2,
475 X_Request_Id NUMBER,
476 X_Program_Application_Id NUMBER,
477 X_Program_Id NUMBER,
478 X_Program_Update_Date DATE,
479 X_customer_id NUMBER,
480 X_customer_site_id NUMBER
481
482 ) IS
483
484 BEGIN
485 UPDATE RCV_SHIPMENT_HEADERS
486 SET
487 shipment_header_id = X_Shipment_Header_Id,
488 last_update_date = X_Last_Update_Date,
492 vendor_id = X_Vendor_Id,
489 last_updated_by = X_Last_Updated_By,
490 last_update_login = X_Last_Update_Login,
491 receipt_source_code = X_Receipt_Source_Code,
493 vendor_site_id = X_Vendor_Site_Id,
494 organization_id = X_Organization_Id,
495 ship_to_org_id = X_Ship_to_org_id,
496 shipment_num = X_Shipment_Num,
497 receipt_num = X_Receipt_Num,
498 ship_to_location_id = X_Ship_To_Location_Id,
499 bill_of_lading = X_Bill_Of_Lading,
500 packing_slip = X_Packing_Slip,
501 shipped_date = X_Shipped_Date,
502 freight_carrier_code = X_Freight_Carrier_Code,
503 expected_receipt_date = X_Expected_Receipt_Date,
504 employee_id = X_Employee_Id,
505 num_of_containers = X_Num_Of_Containers,
506 waybill_airbill_num = X_Waybill_Airbill_Num,
507 comments = X_Comments,
508 attribute_category = X_Attribute_Category,
509 attribute1 = X_Attribute1,
510 attribute2 = X_Attribute2,
511 attribute3 = X_Attribute3,
512 attribute4 = X_Attribute4,
513 attribute5 = X_Attribute5,
514 attribute6 = X_Attribute6,
515 attribute7 = X_Attribute7,
516 attribute8 = X_Attribute8,
517 attribute9 = X_Attribute9,
518 attribute10 = X_Attribute10,
519 attribute11 = X_Attribute11,
520 attribute12 = X_Attribute12,
521 attribute13 = X_Attribute13,
522 attribute14 = X_Attribute14,
523 attribute15 = X_Attribute15,
524 ussgl_transaction_code = X_Ussgl_Transaction_Code,
525 government_context = X_Government_Context,
526 request_id = X_Request_Id,
527 program_application_id = X_Program_Application_Id,
528 program_id = X_Program_Id,
529 program_update_date = X_Program_Update_Date,
530 customer_id = X_Customer_Id,
531 customer_site_id = X_Customer_Site_Id
532 WHERE rowid = X_rowid;
533
534 if (SQL%NOTFOUND) then
535 Raise NO_DATA_FOUND;
536 end if;
537
538 EXCEPTION
539 WHEN OTHERS THEN
540 RAISE;
541
542 END Update_Row;
543
544 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
545
546 BEGIN
547
548 DELETE FROM RCV_SHIPMENT_HEADERS
549 WHERE rowid = X_Rowid;
550
551 if (SQL%NOTFOUND) then
552 Raise NO_DATA_FOUND;
553 end if;
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 RAISE;
558
559 END Delete_Row;
560
561 END RCV_SHIPMENT_HEADERS_PKG;