[Home] [Help]
PACKAGE BODY: APPS.GML_PO_RECV2_PKG
Source
1 PACKAGE BODY GML_PO_RECV2_PKG AS
2 /* $Header: GMLRCMVB.pls 115.12 2002/12/04 19:08:35 gmangari ship $ */
3
4 c_language_code VARCHAR2(4):= 'ENG';
5
6 /*========================================================================
7 | |
8 | PROCEDURE NAME get_oracle_id |
9 | |
10 | DESCRIPTION Procedure to get the po header id, line id, location |
11 | id, and release id from the mapping table. |
12 | |
13 | MODIFICATION HISTORY |
14 | |
15 | 10/22/97 Kenny Jiang created |
16 | 11/12/97 Kenny Jiang get po_release_id added |
17 | |
18 =========================================================================*/
19
20 PROCEDURE get_oracle_id
21 ( v_po_id IN po_ordr_hdr.po_id%TYPE,
22 v_line_id IN po_ordr_dtl.line_id%TYPE,
23 v_po_header_id OUT NOCOPY cpg_oragems_mapping.po_header_id%TYPE,
24 v_po_line_id OUT NOCOPY cpg_oragems_mapping.po_line_id%TYPE,
25 v_line_location_id OUT NOCOPY cpg_oragems_mapping.po_line_location_id%TYPE,
26 v_po_release_id OUT NOCOPY cpg_oragems_mapping.po_release_id%TYPE)
27 IS
28 CURSOR id_cur IS
29 SELECT po_header_id,
30 po_line_id,
31 po_line_location_id,
32 po_release_id
33 FROM cpg_oragems_mapping
34 WHERE po_id = v_po_id
35 AND line_id = v_line_id;
36
37 err_num NUMBER;
38 err_msg VARCHAR2(100);
39 complete_message VARCHAR2(2000);
40
41 BEGIN
42
43 OPEN id_cur;
44 FETCH id_cur INTO v_po_header_id, v_po_line_id,
45 v_line_location_id, v_po_release_id;
46 IF id_cur%NOTFOUND THEN
47 CLOSE id_cur;
48 FND_MESSAGE.set_name('GML', 'PO_ID_ERROR');
49 complete_message := FND_MESSAGE.GET;
50 raise_application_error(-20000, complete_message);
51 END IF;
52 CLOSE id_cur;
53
54 EXCEPTION
55 WHEN OTHERS THEN
56 err_num := SQLCODE;
57 err_msg := SUBSTRB(SQLERRM, 1, 100);
58 RAISE_APPLICATION_ERROR(-20000, err_msg);
59
60 END get_oracle_id;
61
62
63 /*========================================================================
64 | |
65 | PROCEDURE NAME update_header_status |
66 | |
67 | DESCRIPTION Procedure to update header status. |
68 | |
69 | MODIFICATION HISTORY |
70 | |
71 | 10/22/97 Kenny Jiang created |
72 | |
73 =========================================================================*/
74
75 PROCEDURE update_header_status
76 ( v_po_header_id IN NUMBER,
77 v_org_id IN NUMBER,
78 v_last_updated_by IN NUMBER,
79 v_last_update_date IN DATE )
80 IS
81 CURSOR line_cur IS
82 SELECT closed_code
83 FROM po_lines_all
84 WHERE po_header_id = v_po_header_id
85 AND org_id = v_org_id;
86
87 CURSOR po_cur IS
88 SELECT closed_code
89 FROM po_headers_all
90 WHERE po_header_id = v_po_header_id
91 AND org_id = v_org_id;
92
93 v_closed_code po_lines_all.closed_code%TYPE;
94 v_new_status po_headers_all.closed_code%TYPE;
95 v_old_status po_headers_all.closed_code%TYPE;
96 v_all_lines_closed BOOLEAN :=TRUE;
97
98 err_num NUMBER;
99 err_msg VARCHAR2(100);
100
101 BEGIN
102
103 OPEN line_cur;
104 FETCH line_cur INTO v_closed_code;
105
106 WHILE line_cur%FOUND
107 LOOP
108
109 /* closed_code can be OPEN, CLOSED or NULL in po_lines_all */
110
111 IF v_closed_code IS NULL OR v_closed_code = 'OPEN' THEN
112 v_all_lines_closed :=FALSE;
113 END IF;
114
115 FETCH line_cur INTO v_closed_code;
116
117 END LOOP;
118
119 IF v_all_lines_closed = TRUE THEN
120 v_new_status := 'CLOSED';
121 ELSE
122 v_new_status := 'OPEN';
123 END IF;
124
125 OPEN po_cur;
126 FETCH po_cur INTO v_old_status;
127 CLOSE po_cur;
128
129 IF v_old_status IS NULL OR
130 v_new_status <> v_old_status THEN
131 BEGIN
132 UPDATE po_headers_all
133 SET closed_code = v_new_status,
134 last_update_date= v_last_update_date,
135 last_updated_by = v_last_updated_by
136 WHERE po_header_id = v_po_header_id AND
137 org_id = v_org_id;
138 END;
139 END IF;
140
141 CLOSE line_cur;
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 err_num := SQLCODE;
146 err_msg := SUBSTRB(SQLERRM, 1, 100);
147 RAISE_APPLICATION_ERROR(-20000, err_msg);
148
149 END update_header_status;
150
151
152 /*========================================================================
153 | |
154 | PROCEDURE NAME update_line_status |
155 | |
156 | DESCRIPTION Procedure to update_line_status. |
157 | |
158 | MODIFICATION HISTORY |
159 | |
160 | 10/22/97 Kenny Jiang created |
161 | |
162 =========================================================================*/
163
164 PROCEDURE update_line_status
165 ( v_po_header_id IN NUMBER,
166 v_po_line_id IN NUMBER,
167 v_org_id IN NUMBER,
168 v_last_updated_by IN NUMBER,
169 v_last_update_date IN DATE )
170 IS
171
172 CURSOR line_location_cur IS
173 SELECT closed_code
174 FROM po_line_locations_all
175 WHERE po_header_id = v_po_header_id
176 AND po_line_id = v_po_line_id
177 AND org_id = v_org_id;
178
179 CURSOR line_cur IS
180 SELECT closed_code
181 FROM po_lines_all
182 WHERE po_header_id = v_po_header_id
183 AND po_line_id = v_po_line_id
184 AND org_id = v_org_id;
185
186 v_closed_code po_line_locations_all.closed_code%TYPE;
187 v_new_status po_lines_all.closed_code%TYPE;
188 v_old_status po_lines_all.closed_code%TYPE;
189 v_all_locations_closed BOOLEAN :=TRUE;
190
191 err_num NUMBER;
192 err_msg VARCHAR2(100);
193
194 BEGIN
195 OPEN line_location_cur;
196 FETCH line_location_cur INTO v_closed_code;
197
198 WHILE line_location_cur%FOUND
199 LOOP
200 IF v_closed_code IS NULL OR v_closed_code NOT IN ('CLOSED',
201 'FINALLY CLOSED', 'CLOSED FOR RECEIVING', 'CLOSED FOR INVOICE') THEN
202 v_all_locations_closed :=FALSE;
203 END IF;
204
205 FETCH line_location_cur INTO v_closed_code;
206 END LOOP;
207
208 IF v_all_locations_closed = TRUE THEN
209 v_new_status := 'CLOSED';
210 ELSE
211 v_new_status := 'OPEN';
212 END IF;
213
214 OPEN line_cur;
215 FETCH line_cur INTO v_old_status;
216 CLOSE line_cur;
217
218 IF v_old_status IS NULL OR v_new_status <> v_old_status THEN
219 UPDATE po_lines_all
220 SET closed_code = v_new_status,
221 last_update_date= v_last_update_date,
222 last_updated_by = v_last_updated_by
223 WHERE po_header_id = v_po_header_id AND
224 po_line_id = v_po_line_id AND
225 org_id = v_org_id;
226
227 update_header_status(v_po_header_id,
228 v_org_id,
229 v_last_updated_by,
230 v_last_update_date );
231
232 END IF;
233
234 CLOSE line_location_cur;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 err_num := SQLCODE;
239 err_msg := SUBSTRB(SQLERRM, 1, 100);
240 RAISE_APPLICATION_ERROR(-20000, err_msg);
241
242 END update_line_status;
243
244
245 /*========================================================================
246
247 PROCEDURE NAME update_release_status
248
249 DESCRIPTION Procedure to update the status in po_releases_all
250
251 MODIFICATION HISTORY
252
253 11/12/97 Kenny Jiang created
254
255 ========================================================================*/
256
257 PROCEDURE update_release_status
258 ( v_po_header_id IN NUMBER,
259 v_po_release_id IN NUMBER,
260 v_org_id IN NUMBER,
261 v_last_updated_by IN NUMBER,
262 v_last_update_date IN DATE )
263 IS
264
265 CURSOR line_location_cur IS
266 SELECT closed_code
267 FROM po_line_locations_all
268 WHERE po_header_id = v_po_header_id
269 AND po_release_id = v_po_release_id
270 AND org_id = v_org_id;
271
272 CURSOR status_cur IS
273 SELECT closed_code
274 FROM po_releases_all
275 WHERE po_header_id = v_po_header_id
276 AND po_release_id = v_po_release_id
277 AND org_id = v_org_id;
278
279 v_closed_code po_line_locations_all.closed_code%TYPE;
280 v_new_status po_lines_all.closed_code%TYPE;
281 v_old_status po_lines_all.closed_code%TYPE;
282 v_all_locations_closed BOOLEAN :=TRUE;
283
284 err_num NUMBER;
285 err_msg VARCHAR2(100);
286
287 BEGIN
288 OPEN line_location_cur;
289 FETCH line_location_cur INTO v_closed_code;
290
291 WHILE line_location_cur%FOUND
292 LOOP
293 IF v_closed_code IS NULL OR v_closed_code NOT IN ('CLOSED',
294 'FINALLY CLOSED', 'CLOSED FOR RECEIVING', 'CLOSED FOR INVOICE') THEN
295 v_all_locations_closed :=FALSE;
296 END IF;
297
298 FETCH line_location_cur INTO v_closed_code;
299 END LOOP;
300 CLOSE line_location_cur;
301
302 IF v_all_locations_closed = TRUE THEN
303 v_new_status := 'CLOSED';
304 ELSE
305 v_new_status := 'OPEN';
306 END IF;
307
308 OPEN status_cur;
309 FETCH status_cur INTO v_old_status;
310 CLOSE status_cur;
311
312 IF v_old_status IS NULL OR v_new_status <> v_old_status THEN
313 UPDATE po_releases_all
314 SET closed_code = v_new_status,
315 last_update_date= v_last_update_date,
316 last_updated_by = v_last_updated_by
317 WHERE po_header_id = v_po_header_id
318 AND po_release_id = v_po_release_id
319 AND org_id = v_org_id;
320 END IF;
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 err_num := SQLCODE;
325 err_msg := SUBSTRB(SQLERRM, 1, 100);
326 RAISE_APPLICATION_ERROR(-20000, err_msg);
327
328 END update_release_status;
329
330
331 /*========================================================================
332 | |
333 | PROCEDURE NAME update_line_locations. |
334 | |
335 | DESCRIPTION Procedure to update line locations. |
336 | |
337 | MODIFICATION HISTORY |
338 | |
339 | 10/22/97 Kenny Jiang created |
340 | 23-NOV-99 NC - modified all references to cpg_receiving_interface table
341 | which nolonger exists. |
342 | 16-DOC-99 NC - Added FND_GLOBAL.APPS_INITIALIZE . |
343 | 03-MAR-00 HW - BUG#:1222247 - changed status code |
344 =========================================================================*/
345
346 PROCEDURE update_line_locations
347 ( v_po_header_id IN cpg_oragems_mapping.po_header_id%TYPE,
348 v_po_line_id IN cpg_oragems_mapping.po_line_id%TYPE,
349 v_line_location_id IN cpg_oragems_mapping.po_line_location_id%TYPE,
350 v_po_release_id IN cpg_oragems_mapping.po_release_id%TYPE,
351 v_org_id IN gl_plcy_mst.org_id%TYPE,
352 v_po_status IN po_ordr_dtl.po_status%TYPE,
353 v_received_qty IN po_recv_dtl.recv_qty1%TYPE,
354 v_returned_qty IN po_rtrn_dtl.return_qty1%TYPE,
355 v_created_by IN po_recv_dtl.created_by%TYPE,
356 v_timestamp IN cpg_oragems_mapping.time_stamp%TYPE)
357 IS
358 v_closed_code po_line_locations_all.closed_code%TYPE;
359 v_last_updated_by po_line_locations_all.last_updated_by%TYPE;
360 v_closed_reason po_line_locations_all.closed_reason%TYPE;
361 v_closed_date po_line_locations_all.closed_date%TYPE;
362 v_closed_by po_line_locations_all.closed_by%TYPE;
363 v_close_status po_line_locations_all.closed_code%TYPE;
364 v_source_shipment_id po_line_locations_all.source_shipment_id%TYPE;
365 v_canceled VARCHAR2(1);
366
367 /* NC 12/16/99 */
368 v_user_id NUMBER;
369 v_resp_id NUMBER;
370 v_resp_appl_id NUMBER;
371
372
373 CURSOR canceled_cur IS
374 SELECT cancel_flag
375 FROM po_line_locations_all
376 WHERE line_location_id = v_line_location_id;
377
378 CURSOR close_status_cur IS
379 SELECT closed_code
380 FROM po_line_locations_all
381 WHERE line_location_id = v_line_location_id;
382
383 CURSOR po_cur IS
384 SELECT segment1
385 FROM po_headers_all
386 WHERE po_header_id = v_po_header_id;
387
388 CURSOR line_cur IS
389 SELECT line_num
390 FROM po_lines_all
391 WHERE po_line_id = v_po_line_id;
392
393 CURSOR shipment_cur IS
394 SELECT shipment_num
395 FROM po_line_locations_all
396 WHERE line_location_id = v_line_location_id;
397
398 CURSOR user_id_cur IS
399 SELECT user_id
400 FROM fnd_user
401 WHERE user_name = v_created_by;
402
403 /* NC 12/16/99 */
404 CURSOR resp_id_cur IS
405 SELECT responsibility_id
406 FROM fnd_user_resp_groups
407 WHERE user_id = v_user_id
408 AND responsibility_application_id
409 = v_resp_appl_id;
410
411 err_num NUMBER;
412 err_msg VARCHAR2(100);
413 v_complete_msg VARCHAR2(2000);
414 v_po_no VARCHAR2(20);
415 v_line_no NUMBER;
416 v_shipment_no NUMBER;
417
418 BEGIN
419
420 /* T. Ricci 12/24/98 not needed now that created_by is the user_id*/
421 /* OPEN user_id_cur;*/
422 /* FETCH user_id_cur INTO v_last_updated_by;*/
423 /* CLOSE user_id_cur; */
424
425 v_last_updated_by := v_created_by;
426
427 /* When closed_code is updated in po_line_locations_all, a trigger on
428 that table tries to fire a concurrent program. For some reason
429 the FND_GLOBAL user_id and resp_id had wrong values becoz of which
430 the concurrenct request was not getting fired( returning "CONC-Unable to
431 get oracle name") error. Hence added the following APPS_INITIALIZE call.
432 -- NC 12/16/99 */
433
434 v_user_id := v_created_by;
435 v_resp_appl_id := FND_GLOBAL.resp_appl_id;
436
437 OPEN resp_id_cur;
438 FETCH resp_id_cur INTO v_resp_id;
439 CLOSE resp_id_cur;
440
441 FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id,v_resp_appl_id);
442
443
444 IF v_po_status = 20 THEN
445 /* BUG#:1222247 - make status Closed for Receiving */
446 /* v_closed_code := 'CLOSED'; */
447 v_closed_code := 'CLOSED FOR RECEIVING';
448 v_closed_reason := 'Ordered Quantity Fully Received';
449 v_closed_date := v_timestamp;
450 v_closed_by := v_last_updated_by;
451
452 /* Each individual shipment line could be closed by GEMMS when fully received*/
453 /* or closed by Oracle inadvertently.*/
454 /* Note: In comparison, closure of a line could not be simply traced to */
455 /* a single reason or user if the line has multiple shipment lines.*/
456 ELSE /* v_po_status = 0*/
457 v_closed_code := 'OPEN';
458 v_closed_reason := NULL;
459 v_closed_date := NULL;
460 v_closed_by := NULL;
461 END IF;
462
463 UPDATE po_line_locations_all
464 SET last_update_date = v_timestamp,
465 last_updated_by = v_last_updated_by,
466 quantity_received = v_received_qty,
467 quantity_rejected = v_returned_qty
468 WHERE po_header_id = v_po_header_id
469 AND po_line_id = v_po_line_id
470 AND line_location_id = v_line_location_id
471 /* Added the OR org_id is null to allow for mult org*/
472 AND (org_id = v_org_id OR org_id is null);
473
474 /* IF it is a Blanket or a Planned PO, Update Recd qty for the Parent Line*/
475
476 SELECT source_shipment_id
477 INTO v_source_shipment_id
478 FROM po_line_locations_all
479 WHERE line_location_id = v_line_location_id;
480
481 UPDATE po_line_locations_all
482 SET last_update_date = v_timestamp,
483 last_updated_by = v_last_updated_by,
484 quantity_received = (select sum(quantity_received)
485 from po_line_locations_all
486 where source_shipment_id = v_source_shipment_id),
487 quantity_rejected = (select sum(quantity_rejected)
488 from po_line_locations_all
489 where source_shipment_id = v_source_shipment_id)
490 WHERE po_header_id = v_po_header_id
491 AND po_line_id = v_po_line_id
492 AND line_location_id = (select source_shipment_id
493 from po_line_locations_all
494 where line_location_id = v_line_location_id)
495 AND org_id = v_org_id;
496
497 OPEN canceled_cur;
498 FETCH canceled_cur INTO v_canceled;
499 CLOSE canceled_cur;
500 OPEN close_status_cur;
501 FETCH close_status_cur INTO v_close_status;
502 CLOSE close_status_cur;
503
504 IF (v_canceled= 'Y') OR (v_close_status= 'FINALLY CLOSED') THEN
505
506 OPEN shipment_cur;
507 FETCH shipment_cur INTO v_shipment_no;
508 CLOSE shipment_cur;
509
510 OPEN line_cur;
511 FETCH line_cur INTO v_line_no;
512 CLOSE line_cur;
513
514 OPEN po_cur;
515 FETCH po_cur INTO v_po_no;
516 CLOSE po_cur;
517
518
519 FND_MESSAGE.set_name('GML', 'PO_RCV_LINE_CLOSE');
520 FND_MESSAGE.set_token('v_po_no',v_po_no);
521 v_complete_msg := FND_MESSAGE.GET;
522
523 ELSE
524 /* BUG#:1222247 */
525 /* Commented following 3 lines.Prevent updating unnecessary fields */
526
527 UPDATE po_line_locations_all
528 SET closed_code = v_closed_code
529 /* closed_reason = v_closed_reason, */
530 /* closed_date = v_closed_date, */
531 /* closed_by = v_closed_by */
532 WHERE po_header_id = v_po_header_id
533 AND po_line_id = v_po_line_id
534 AND line_location_id = v_line_location_id
535 AND org_id = v_org_id;
536 END IF;
537
538 /* Update the PO Line for all types of PO's*/
539
540 /* BUG#:1222247 */
541 /* Do not call the following procedure thus the bug will prevent unnecessary */
542 /* updates of fields in po_lines and po_headers */
543
544 /*
545 update_line_status(v_po_header_id,
546 v_po_line_id,
547 v_org_id,
548 v_last_updated_by,
549 v_timestamp);
550 */
551
552 /*BUG#:1222247 leave status code in po_releases_all unchanged -- */
553 /* commented the call to update_release_status */
554
555 /* IF v_po_release_id IS NOT NULL THEN -- it's a Planned/Blanket PO shipment */
556 /* update_release_status(v_po_header_id, */
557 /* v_po_release_id, */
558 /* v_org_id, */
559 /* v_last_updated_by, */
560 /* v_timestamp); */
561 /* END IF; */
562
563 EXCEPTION
564 WHEN OTHERS THEN
565 err_num := SQLCODE;
566 err_msg := SUBSTRB(SQLERRM, 1, 100);
567 RAISE_APPLICATION_ERROR(-20000, err_msg);
568
569 END update_line_locations;
570
571 END GML_PO_RECV2_PKG;