[Home] [Help]
PACKAGE BODY: APPS.GML_PO_RECV1_PKG
Source
1 PACKAGE BODY GML_PO_RECV1_PKG AS
2 /* $Header: GMLRECVB.pls 115.9 2002/12/04 19:10:12 gmangari ship $ */
3
4
5 /*========================================================================+
6 | PROCEDURE store_id |
7 | |
8 | DESCRIPTION The procedure stores the po id and line id into the |
9 | package variables, which will be used by sum_recv |
10 | procedure later. |
11 | |
12 | MODIFICATION HISTORY |
13 | 20-NOV-97 Kenny Created. |
14 | 23-NOV-99 NC - changed the variable names from v_ to G_ |
15 | |
16 +========================================================================*/
17
18 PROCEDURE store_id(p_po_id IN NUMBER, p_poline_id IN NUMBER) AS
19 BEGIN
20
21 G_po_id := p_po_id;
22 G_poline_id := p_poline_id;
23
24
25 IF (G_po_id IS NULL) OR (G_po_id = 0) THEN
26 G_stock_ind := 1;
27 ELSE
28 G_stock_ind := 0;
29 END IF;
30
31 END;
32
33
34 /*=======================================================================+
35 | PROCEDURE get_no |
36 | |
37 | DESCRIPTION This procedure obtains the po number and line number |
38 | from the po id and line id. |
39 | |
40 | |
41 | MODIFICATION HISTORY |
42 | 20-NOV-97 Kenny ---- Created. |
43 | |
44 +========================================================================*/
45
46 PROCEDURE get_no(v_po_no OUT NOCOPY VARCHAR2, v_line_no OUT NOCOPY NUMBER)
47 AS
48 CURSOR po_no_cur IS
49 SELECT po_no
50 FROM po_ordr_hdr
51 WHERE po_id = G_po_id;
52
53 CURSOR line_no_cur IS
54 SELECT line_no
55 FROM po_ordr_dtl
56 WHERE line_id = G_poline_id;
57 BEGIN
58 OPEN po_no_cur;
59 FETCH po_no_cur INTO v_po_no;
60 CLOSE po_no_cur;
61 OPEN line_no_cur;
62 FETCH line_no_cur INTO v_line_no;
63 CLOSE line_no_cur;
64 END;
65
66
67 /*========================================================================+
68 | PROCEDURE check_mapping |
69 | |
70 | |
71 | DESCRIPTION This procedure checks if the particular po line |
72 | exists in mapping table and if so it's not finally |
73 | closed and not canceled. |
74 | |
75 | MODIFICATION HISTORY |
76 | 20-NOV-97 Kenny ---- Created. |
77 | |
78 +========================================================================*/
79
80 FUNCTION check_mapping
81 RETURN BOOLEAN
82 IS
83 CURSOR status_cur IS
84 SELECT po_status
85 FROM cpg_oragems_mapping
86 WHERE po_id = G_po_id
87 AND line_id = G_poline_id;
88
89 v_status cpg_oragems_mapping.po_status%TYPE;
90
91 err_num NUMBER;
92 err_msg VARCHAR2(100);
93
94 BEGIN
95
96 OPEN status_cur;
97 FETCH status_cur INTO v_status;
98 CLOSE status_cur;
99
100 IF G_stock_ind = 1 THEN
101 RETURN TRUE;
102 END IF;
103
104 /*cursor not found is taken care of here*/
105 IF (v_status IS NULL) OR (v_status IN ('FINALLY CLOSED','CANCELLED')) THEN /* PO does not exist or canceled or finally closed*/
106 RETURN FALSE;
107 ELSE
108 RETURN TRUE;
109 END IF;
110
111 EXCEPTION
112
113 WHEN OTHERS THEN
114 err_num := SQLCODE;
115 err_msg := SUBSTRB(SQLERRM, 1, 100);
116 RAISE_APPLICATION_ERROR(-20000, err_msg);
117
118 END check_mapping;
119
120
121 /*========================================================================+
122 | PROCEDURE sum_recv |
123 | |
124 | DESCRIPTION This procedure calculates the net received quantity |
125 | and the net returned quantity for a particular PO line |
126 | and insert into the receiving interface table. |
127 | |
128 | MODIFICATION HISTORY |
129 | 20-NOV-97 Kenny ---- Created. |
130 | 26-JAN-99 T.Ricci removed and recv_status <> -1 in where for who |
131 | column select BUG#795134 |
132 | 23-NOV-99 NC deleted insert into cpg_receiving_interface.This table |
133 | nolonger exists in 11i.Instead, storing the data in global|
134 | vars which will be used later. |
135 | 29-NOV-99 NC -ve received_qty's being sent to apps side if a receipt|
136 | is voided after a return is made against it.Changed the |
137 | received_qty to zero ( instead of a -ve number) in such |
138 | cases. |
139 | 04-FEB-2000 PB Bug# 1094230 - a voided return should be excluded form|
140 | the total returned quantity. |
141 | If the received UOM is different that the Order UOM |
142 | then the necessary conversions need to be made.Same |
143 | applies to returned quantity |
144 +========================================================================*/
145
146
147 PROCEDURE sum_recv AS
148 v_po_no po_ordr_hdr.po_no%TYPE;
149 v_line_no po_ordr_dtl.line_no%TYPE;
150 v_org_id gl_plcy_mst.org_id%TYPE;
151 v_orgn_code po_ordr_hdr.orgn_code%TYPE;
152 v_returned_qty po_rtrn_dtl.return_qty1%TYPE;
153 v_received_qty po_recv_dtl.recv_qty1%TYPE;
154 v_status po_ordr_dtl.po_status%TYPE;
155 v_actual_received_qty NUMBER;
156 v_created_by po_recv_dtl.created_by%TYPE;
157 v_last_updated_by po_recv_dtl.last_updated_by%TYPE;
158 v_last_update_login po_recv_dtl.last_update_login%TYPE;
159
160 v_order_um1 VARCHAR2(4);
161
162 x_total_recv_qty NUMBER := 0 ;
163 x_recv_ordr_qty NUMBER := 0 ;
164 x_recv_qty NUMBER;
165 x_recv_um VARCHAR2(4);
166 x_item_id NUMBER;
167
168 x_total_rtrn_qty NUMBER := 0 ;
169 x_rtrn_ordr_qty NUMBER := 0 ;
170 x_rtrn_qty NUMBER;
171 x_rtrn_um VARCHAR2(4);
172
173
174
175 err_num NUMBER;
176 err_msg VARCHAR2(100);
177
178 CURSOR po_org_cur(vc_po_id NUMBER) IS
179 SELECT po_no, orgn_code
180 FROM po_ordr_hdr
181 WHERE po_id = vc_po_id;
182
183 CURSOR line_no_cur(vc_po_id NUMBER, vc_line_id NUMBER) IS
184 SELECT line_no,ORDER_UM1, item_id
185 FROM po_ordr_dtl
186 WHERE po_id = vc_po_id AND
187 line_id = vc_line_id;
188
189 CURSOR status_cur(vc_po_id NUMBER, vc_line_id NUMBER) IS
190 SELECT po_status
191 FROM po_ordr_dtl
192 WHERE po_id = vc_po_id AND
193 line_id = vc_line_id;
194
195 CURSOR org_cur IS
196 SELECT g.org_id
197 FROM gl_plcy_mst g, sy_orgn_mst o
198 WHERE o.orgn_code = v_orgn_code
199 AND o.co_code = g.co_code;
200
201 /* Added these 2 cursors for Bug# 1094230 */
202 CURSOR recv_qty_cur IS
203 SELECT NVL(sum(recv_qty1), 0),recv_um1
204 FROM po_recv_dtl
205 WHERE po_id = G_po_id
206 AND poline_id = G_poline_id
207 AND recv_status <> -1
208 GROUP BY recv_um1;
209
210 CURSOR rtrn_qty_cur IS
211 SELECT NVL(SUM(return_qty1), 0),return_um1
212 FROM po_rtrn_dtl dtl , po_rtrn_hdr hdr
213 WHERE dtl.po_id = G_po_id
214 AND dtl.poline_id = G_poline_id
215 AND dtl.return_id = hdr.return_id
216 AND hdr.delete_mark <> 1
217 GROUP BY return_um1;
218
219
220 BEGIN
221
222
223 IF G_stock_ind = 0 THEN
224 OPEN po_org_cur(G_po_id);
225 FETCH po_org_cur INTO v_po_no, v_orgn_code;
226 CLOSE po_org_cur;
227
228 /* obtain the line_no corresponding to the given po_id and line_id*/
229 /* from the po_ordr_dtl table*/
230
231 OPEN line_no_cur(G_po_id, G_poline_id);
232 FETCH line_no_cur INTO v_line_no,v_order_um1,x_item_id;
233 CLOSE line_no_cur;
234
235 OPEN org_cur;
236 FETCH org_cur INTO v_org_id;
237 CLOSE org_cur;
238
239
240 /*Begin Bug# 1094230*/
241 -----------------------------------------------------------------------------
242 BEGIN
243 OPEN rtrn_qty_cur;
244 FETCH rtrn_qty_cur into x_rtrn_qty,x_rtrn_um;
245 LOOP
246 x_rtrn_ordr_qty := 0 ;
247
248 if x_rtrn_um <> v_order_um1
249 then
250 x_rtrn_ordr_qty := GMICUOM.uom_conversion
251 (x_item_id,0,
252 x_rtrn_qty,
253 x_rtrn_um,
254 v_order_um1,0);
255
256 if x_rtrn_ordr_qty < 0
257 then
258 x_rtrn_ordr_qty := x_rtrn_qty;
259 end if;
260
261 else
262 x_rtrn_ordr_qty := x_rtrn_qty;
263 end if;
264
265 x_total_rtrn_qty := x_total_rtrn_qty + nvl(x_rtrn_ordr_qty,0);
266
267
268 FETCH rtrn_qty_cur into x_rtrn_qty,x_rtrn_um;
269
270 if (rtrn_qty_cur%NOTFOUND)
271 then
272 exit;
273 end if;
274 END LOOP;
275 CLOSE rtrn_qty_cur;
276 END;
277
278
279 BEGIN
280 OPEN recv_qty_cur;
281 FETCH recv_qty_cur into x_recv_qty,x_recv_um;
282 LOOP
283 x_recv_ordr_qty := 0 ;
284
285 if x_recv_um <> v_order_um1
286 then
287 x_recv_ordr_qty := GMICUOM.uom_conversion
288 (x_item_id,0,
289 x_recv_qty,
290 x_recv_um,
291 v_order_um1,0);
292
293 if x_recv_ordr_qty < 0
294 then
295 x_recv_ordr_qty := x_recv_qty;
296 end if;
297
298 else
299 x_recv_ordr_qty := x_recv_qty;
300 end if;
301
302 x_total_recv_qty := x_total_recv_qty + nvl(x_recv_ordr_qty,0);
303
304
305 FETCH recv_qty_cur into x_recv_qty,x_recv_um;
306
307 if (recv_qty_cur%NOTFOUND)
308 then
309 exit;
310 end if;
311 END LOOP;
312 CLOSE recv_qty_cur;
313 END;
314 /*End Bug 1094230 */
315 ----------------------------------------------------------------------------------
316
317
318 /*Bug# 1094230 */
319 /*
320 SELECT NVL(SUM(return_qty1), 0)
321 INTO v_returned_qty
322 FROM po_rtrn_dtl dtl , po_rtrn_hdr hdr
323 WHERE dtl.po_id = G_po_id
324 AND dtl.poline_id = G_poline_id
325 AND dtl.return_id = hdr.return_id;
326
327
328 SELECT NVL(sum(recv_qty1), 0)
329 INTO v_received_qty
330 FROM po_recv_dtl
331 WHERE po_id = G_po_id
332 AND poline_id = G_poline_id
333 AND recv_status <> -1;
334 */
335
336
337
338 SELECT created_by, last_updated_by, last_update_login
339 INTO v_created_by, v_last_updated_by, v_last_update_login
340 FROM po_recv_dtl
341 WHERE po_id = G_po_id
342 AND poline_id = G_poline_id
343 /* AND recv_status <> -1 */
344 AND last_update_date = (select max(last_update_date) from po_recv_dtl
345 WHERE po_id = G_po_id
346 AND poline_id = G_poline_id);
347 /* AND recv_status <> -1); */
348
349 OPEN status_cur(G_po_id, G_poline_id);
350 FETCH status_cur INTO v_status;
351 CLOSE status_cur;
352
353 /*Bug# 1094230 */
354 /*v_actual_received_qty := v_received_qty - v_returned_qty;*/
355 v_actual_received_qty := x_total_recv_qty - x_total_rtrn_qty;
356
357 /* NC - 29-NOV-99 */
358 IF (v_actual_received_qty < 0 ) THEN
359 v_actual_received_qty := 0;
360 END IF;
361
362 /* NC -copy the data into the global variables. */
363
364 G_po_no := v_po_no;
365 G_line_no := v_line_no;
366 G_org_id := v_org_id;
367 G_po_status := v_status;
368 G_actual_received_qty := v_actual_received_qty;
372
369 G_returned_qty := x_total_rtrn_qty;
370 G_po_status := v_status;
371 G_created_by := v_created_by;
373 END IF;
374
375 EXCEPTION
376
377 WHEN OTHERS THEN
378 err_num := SQLCODE;
379 err_msg := SUBSTRB(SQLERRM, 1, 100);
380 RAISE_APPLICATION_ERROR(-20000, err_msg);
381 END;
382
383 /*========================================================================
384 | |
385 | PROCEDURE NAME recv_mv |
386 | |
387 | DESCRIPTION Procedure to move records from receiving interface |
388 | table to Oracle base tables |.
389 | |
390 | MODIFICATION HISTORY |
391 | |
392 | 10/22/97 Kenny Jiang created |
393 | 23-NOV-99 NC - Removed the cursor and all references to cpg_receiving_|
394 | interface table; and changed the code accordingly. |
395 | 01-DEC-99 NC - Added the If condition for stock_ind |
396 | |
397 =========================================================================*/
398
399 PROCEDURE recv_mv
400 IS
401
402
403 v_po_header_id cpg_oragems_mapping.po_header_id%TYPE;
404 v_po_line_id cpg_oragems_mapping.po_line_id%TYPE;
405 v_line_location_id cpg_oragems_mapping.po_line_location_id%TYPE;
406 v_po_release_id cpg_oragems_mapping.po_release_id%TYPE;
407
408 err_num NUMBER;
409 err_msg VARCHAR2(100);
410
411 BEGIN
412
413 IF G_stock_ind = 0 THEN
414 GML_PO_RECV2_PKG.get_oracle_id(G_po_id,
415 G_poline_id,
416 v_po_header_id,
417 v_po_line_id,
418 v_line_location_id,
419 v_po_release_id );
420
421 GML_PO_RECV2_PKG.update_line_locations (v_po_header_id,
422 v_po_line_id,
423 v_line_location_id,
424 v_po_release_id,
425 G_org_id,
426 G_po_status,
427 G_actual_received_qty,
428 G_returned_qty,
429 G_created_by,
430 SYSDATE);
431 END IF;
432
433 EXCEPTION
434 WHEN OTHERS THEN
435 err_num := SQLCODE;
436 err_msg := SUBSTRB(SQLERRM, 1, 100);
437 RAISE_APPLICATION_ERROR(-20000, err_msg);
438
439 END recv_mv;
440
441 END GML_PO_RECV1_PKG;