[Home] [Help]
PACKAGE BODY: APPS.POS_VIEW_RECEIPTS_GRP
Source
1 PACKAGE BODY POS_VIEW_RECEIPTS_GRP AS
2 /* $Header: POSGRCPB.pls 120.8 2006/03/15 23:17:55 bgopired noship $*/
3
4 /* Logic in this procedure is same as RCV_INVOICE_MATCHING_SV.get_quantities.
5 since we can not directly use the RCV function as it references secured sysnonyms,
6 same logic is incorporated here. Also, we are interested only in Return and Rejcted
7 quantities */
8
9 PROCEDURE get_quantities(
10 top_transaction_id IN NUMBER,
11 rtv_txn_qty IN OUT NOCOPY NUMBER,
12 rejected_txn_qty IN OUT NOCOPY NUMBER) IS
13
14 X_progress VARCHAR2(3) := '000';
15
16 X_primary_uom VARCHAR2(25) := '';
17 X_txn_uom VARCHAR2(25) := '';
18 X_po_uom VARCHAR2(25) := '';
19 X_pr_to_txn_rate NUMBER := 1;
20 X_pr_to_po_rate NUMBER := 1;
21 X_po_to_txn_rate NUMBER := 1;
22 X_item_id NUMBER := 0;
23 X_line_location_id NUMBER := 0;
24 X_received_quantity NUMBER := 0;
25 X_corrected_quantity NUMBER := 0;
26 X_delivered_quantity NUMBER := 0;
27 X_rtv_quantity NUMBER := 0;
28 X_accepted_quantity NUMBER := 0;
29 X_rejected_quantity NUMBER := 0;
30
31 v_primary_uom VARCHAR2(25) := '';
32 v_po_uom VARCHAR2(25) := '';
33 v_txn_uom VARCHAR2(25) := '';
34 v_txn_id NUMBER := 0;
35 v_primary_quantity NUMBER := 0;
36 v_transaction_type VARCHAR2(25) := '';
37 v_parent_id NUMBER := 0;
38 v_parent_type VARCHAR2(25) := '';
39 v_shipment_line_id NUMBER := 0;
40 v_line_location_id NUMBER := 0;
41
42 grand_parent_type VARCHAR2(25) := '';
43 grand_parent_id NUMBER := 0;
44
45 /* This cursor recursively query up all the children of the
46 ** top transaction (RECEIVE or MATCH)
47 */
48
49 CURSOR c_txn_history (c_transaction_id NUMBER) IS
50 SELECT
51 transaction_id,
52 primary_quantity,
53 primary_unit_of_measure,
54 unit_of_measure,
55 source_doc_unit_of_measure,
56 transaction_type,
57 shipment_line_id,
58 po_line_location_id,
59 parent_transaction_id
60 FROM
61 rcv_transactions
62 START WITH transaction_id = c_transaction_id
63 CONNECT BY parent_transaction_id = PRIOR transaction_id;
64
65 BEGIN
66 -- return if invalid input parameters
67
68 IF top_transaction_id IS NULL THEN
69 RETURN;
70 END IF;
71
72 OPEN c_txn_history(top_transaction_id);
73
74 X_progress := '001';
75 LOOP
76 FETCH c_txn_history INTO v_txn_id,
77 v_primary_quantity,
78 v_primary_uom,
79 v_txn_uom,
80 v_po_uom,
81 v_transaction_type,
82 v_shipment_line_id,
83 v_line_location_id,
84 v_parent_id;
85
86 EXIT WHEN c_txn_history%NOTFOUND;
87
88 X_progress := '002';
89
90 IF v_transaction_type = 'RECEIVE' OR v_transaction_type = 'MATCH' THEN
91
92 /* Find out the item_id for UOM conversion */
93 SELECT item_id INTO X_item_id
94 FROM rcv_shipment_lines
95 WHERE shipment_line_id = v_shipment_line_id;
96
97 X_received_quantity := v_primary_quantity;
98 X_line_location_id := v_line_location_id;
99 X_primary_uom := v_primary_uom;
100 X_txn_uom := v_txn_uom;
101 X_po_uom := v_po_uom;
102 ELSIF v_transaction_type = 'RETURN TO VENDOR' THEN
103
104 SELECT transaction_type INTO v_parent_type
105 FROM rcv_transactions
106 WHERE transaction_id = v_parent_id;
107
108 if v_parent_type = 'ACCEPT' THEN
109 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
110 end if;
111
112 if v_parent_type = 'REJECT' THEN
113 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
114 end if;
115 X_rtv_quantity := X_rtv_quantity + v_primary_quantity;
116
117 ELSIF v_transaction_type = 'DELIVER' THEN
118 X_delivered_quantity := X_delivered_quantity + v_primary_quantity;
119
120 ELSIF v_transaction_type = 'ACCEPT' THEN
121
122 SELECT transaction_type INTO v_parent_type
123 FROM rcv_transactions
124 WHERE transaction_id = v_parent_id;
125
126 if v_parent_type <> 'ACCEPT' THEN
127 X_accepted_quantity := X_accepted_quantity + v_primary_quantity;
128 end if;
129
130 if v_parent_type = 'REJECT' THEN
131 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
132 end if;
133
134 ELSIF v_transaction_type = 'REJECT' THEN
135
136 SELECT transaction_type INTO v_parent_type
137 FROM rcv_transactions
138 WHERE transaction_id = v_parent_id;
139
140 if v_parent_type <> 'REJECT' then
141 X_rejected_quantity := X_rejected_quantity + v_primary_quantity;
142 end if;
143 if v_parent_type = 'ACCEPT' then
144 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
145 end if;
146
147 ELSIF v_transaction_type = 'RETURN TO RECEIVING' THEN
148 X_delivered_quantity := X_delivered_quantity - v_primary_quantity;
149
150 ELSIF v_transaction_type = 'CORRECT' THEN
151
152 /* The correction function is based on parent transaction type */
153
154 SELECT transaction_type,parent_transaction_id
155 INTO v_parent_type,grand_parent_id
156 FROM rcv_transactions
157 WHERE transaction_id = v_parent_id;
158
159 BEGIN
160 SELECT transaction_type INTO grand_parent_type
161 FROM rcv_transactions
162 WHERE transaction_id = grand_parent_id;
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN
165 NULL;
166 END;
167
168 IF v_parent_type = 'RECEIVE' OR v_parent_type = 'MATCH' THEN
169 X_corrected_quantity := X_corrected_quantity + v_primary_quantity;
170 ELSIF v_parent_type = 'RETURN TO VENDOR' THEN
171 if grand_parent_type = 'ACCEPT' THEN
172 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
173 end if;
174
175 if grand_parent_type = 'REJECT' THEN
176 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
177 end if;
178 X_rtv_quantity := X_rtv_quantity + v_primary_quantity;
179
180 ELSIF v_parent_type = 'DELIVER' THEN
181 X_delivered_quantity := X_delivered_quantity + v_primary_quantity;
182
183 ELSIF v_parent_type = 'ACCEPT' THEN
184
185 if grand_parent_type = 'REJECT' THEN
186 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
187 end if;
188
189 if grand_parent_type <> 'ACCEPT' THEN
190 X_accepted_quantity := X_accepted_quantity + v_primary_quantity;
191 end if;
192
193 ELSIF v_parent_type = 'REJECT' THEN
194 if grand_parent_type = 'ACCEPT' THEN
195 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
196 end if;
197
198 if grand_parent_type <> 'REJECT' THEN
199 X_rejected_quantity := X_rejected_quantity + v_primary_quantity;
200 end if;
201
202 ELSIF v_parent_type = 'RETURN TO RECEIVING' THEN
203
204 X_delivered_quantity := X_delivered_quantity - v_primary_quantity;
205
206 END IF;
207 END IF;
208
209 END LOOP;
210
211 CLOSE c_txn_history;
212
213 X_progress := '003';
214
215 X_progress := '004';
216
217 /* Get UOM conversion rates */
218
219 X_pr_to_po_rate := po_uom_s.po_uom_convert(X_primary_uom, X_po_uom, X_item_id);
220 X_pr_to_txn_rate := po_uom_s.po_uom_convert(X_primary_uom, X_txn_uom, X_item_id);
221 X_po_to_txn_rate := po_uom_s.po_uom_convert(X_po_uom, X_txn_uom, X_item_id);
222
223
224 rtv_txn_qty := X_pr_to_txn_rate * X_rtv_quantity;
225 rejected_txn_qty := X_pr_to_txn_rate * X_rejected_quantity;
226
227 rtv_txn_qty := round(rtv_txn_qty,15);
228 rejected_txn_qty := round(rejected_txn_qty,15);
229
230 EXCEPTION
231
232 when others then
233 po_message_s.sql_error('get_transaction_quantities', X_progress, sqlcode);
234 raise;
235
236 END get_quantities;
237
238
239 /*To get the LPN/Lot/Serial information for the shipment_line: */
240
241 FUNCTION is_LpnLotSerial_Exist (p_rcv_shipment_line_id NUMBER) RETURN NUMBER IS
242 isLPN NUMBER;
243 isLot NUMBER;
244 isSerial NUMBER;
245 isWms VARCHAR2(1);
246 lExecFunc VARCHAR2(30);
247 BEGIN
248 /* Find if WMS is installed or not */
249 lExecFunc := POS_ASN_CREATE_PVT.check_wms_install(1, isWms);
250 IF (isWms <> 'S') THEN
251 RETURN 0;
252 END IF;
253
254
255 /* Find if lpn exists for the given shipment_line_id */
256 SELECT lpn_id into isLPN
257 FROM rcv_transactions rt
258 WHERE shipment_line_id = p_rcv_shipment_line_id
259 AND transaction_type = 'RECEIVE';
260
261 IF (isLPN is not null) THEN
262 /* LPN exists for this shipment line */
263 RETURN 1;
264 END IF;
265
266 /* No LPN; Find the lot or serial defined for this item */
267
268 SELECT lot_control_code, serial_number_control_code into isLot, isSerial
269 FROM mtl_system_items msi, rcv_shipment_lines rsl
270 WHERE msi.organization_id = rsl.to_organization_id
271 AND msi.inventory_item_id = rsl.item_id
272 AND rsl.shipment_line_id = p_rcv_shipment_line_id;
273
274 IF (isLot > 1 OR isSerial > 1) THEN
275 /* Either lot or serial defined */
276 RETURN 1;
277 ELSE
278 /* No lot or serial defined */
279 RETURN 0;
280 END IF;
281
282 EXCEPTION WHEN NO_DATA_FOUND THEN
283 RETURN 0;
284 END;
285
286
287
288 PROCEDURE get_po_info (
289 p_shipment_header_id IN NUMBER,
290 p_po_switch OUT NOCOPY VARCHAR2,
291 p_po_number OUT NOCOPY VARCHAR2,
292 p_po_header_id OUT NOCOPY VARCHAR2,
293 p_release_id OUT NOCOPY VARCHAR2)
294 IS
295 po_num2 VARCHAR2(40);
296 header_id2 VARCHAR2(40);
297 release_id2 VARCHAR2(40);
298
299 -- Declare cursor to retrieve the PO number for Supplier View.
300 CURSOR po_cursor(l_shipment_header_id number) IS
301 SELECT DISTINCT(ph.segment1||'-'||pr.release_num), ph.po_header_id, pr.po_release_id
302 FROM rcv_shipment_lines rsl, po_headers_all ph, po_releases_all pr
303 WHERE rsl.shipment_header_id= l_shipment_header_id
304 AND rsl.po_header_id = ph.po_header_id
305 AND rsl.po_release_id = pr.po_release_id
306 AND ph.type_lookup_code = 'BLANKET'
307 UNION ALL
308 SELECT DISTINCT ph.segment1, ph.po_header_id, null
309 FROM rcv_shipment_lines rsl, po_headers_all ph
310 WHERE rsl.shipment_header_id= l_shipment_header_id
311 AND rsl.po_header_id = ph.po_header_id
312 AND ph.type_lookup_code = 'STANDARD';
313 Begin
314 OPEN po_cursor(p_shipment_header_id);
315
316 FETCH po_cursor INTO p_po_number, p_po_header_id, p_release_id;
317 if (po_cursor%NOTFOUND) then
318 -- no pos
319 p_po_switch := 'Po_No';
320 else
321 --atleast one po
322 FETCH po_cursor INTO po_num2, header_id2, release_id2;
323 if (po_cursor%NOTFOUND) then
324 --exactly one PO
325 p_po_switch := 'PO_Single';
326 else
327 -- multiple POs
328 p_po_switch := 'PO_Multiple';
329 --p_po_number := 'Multiple'; --Pass the FND Message
330 p_po_number := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
331 end if;
332 end if;
333
334 CLOSE po_cursor;
335
336 EXCEPTION WHEN OTHERS THEN
337 p_po_switch := 'Po_Excep';
338 End get_po_info;
339
340
341 PROCEDURE get_invoice_info (
342 p_shipment_header_id IN NUMBER,
343 p_invoice_switch OUT NOCOPY VARCHAR2,
344 p_invoice_number OUT NOCOPY VARCHAR2,
345 p_invoice_id OUT NOCOPY VARCHAR2) IS
346
347 inv_num2 VARCHAR2(40);
348 header_id2 VARCHAR2(40);
349
350 -- Declare cursor to retrieve the Invoice number for Supplier View.
351 CURSOR inv_cursor(l_shipment_header_id number) IS
352 SELECT DISTINCT ap.invoice_num,ap.invoice_id
353 FROM ap_invoices_all ap, ap_invoice_lines_all al, rcv_transactions rt
354 WHERE ap.invoice_id = al.invoice_id
355 AND al.rcv_transaction_id = rt.transaction_id
356 AND rt.shipment_header_id = l_shipment_header_id;
357
358 Begin
359 OPEN inv_cursor(p_shipment_header_id);
360
361 FETCH inv_cursor INTO p_invoice_number, p_invoice_id;
362 if (inv_cursor%NOTFOUND) then
363 -- no invoices
364 p_invoice_switch := 'Inv_No';
365 else
366 --atleast one Invoice
367 FETCH inv_cursor INTO inv_num2, header_id2;
368 if (inv_cursor%NOTFOUND) then
369 --exactly one Invoice
370 p_invoice_switch := 'Inv_Single';
371 else
372 -- multiple Invoices
373 p_invoice_switch := 'Inv_Multiple';
374 --p_invoice_number := 'Multiple'; --Pass the FND Message
375 p_invoice_number := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
376 end if;
377 end if;
378
379 CLOSE inv_cursor;
380
381 EXCEPTION WHEN OTHERS THEN
382 p_invoice_switch := 'inv_Excep';
383 End get_invoice_info;
384
385
386 PROCEDURE get_invoice_info_for_line (
387 p_shipment_line_id IN NUMBER,
388 p_invoice_switch OUT NOCOPY VARCHAR2,
389 p_invoice_number OUT NOCOPY VARCHAR2,
390 p_invoice_id OUT NOCOPY VARCHAR2) IS
391
392 inv_num2 VARCHAR2(40);
393 header_id2 VARCHAR2(40);
394
395 -- Declare cursor to retrieve the Invoice number for Supplier View.
396 CURSOR inv_cursor(l_shipment_line_id number) IS
397 SELECT DISTINCT ap.invoice_num,ap.invoice_id
398 FROM ap_invoices_all ap, ap_invoice_lines_all al, rcv_transactions rt
399 WHERE ap.invoice_id = al.invoice_id
400 AND al.rcv_transaction_id = rt.transaction_id
401 AND rt.shipment_line_id = l_shipment_line_id;
402
403 Begin
404 OPEN inv_cursor(p_shipment_line_id);
405
406 FETCH inv_cursor INTO p_invoice_number, p_invoice_id;
407 if (inv_cursor%NOTFOUND) then
408 -- no invoices
409 p_invoice_switch := 'Inv_No';
410 else
411 --atleast one Invoice
412 FETCH inv_cursor INTO inv_num2, header_id2;
413 if (inv_cursor%NOTFOUND) then
414 --exactly one Invoice
415 p_invoice_switch := 'Inv_Single';
416 else
417 -- multiple Invoices
418 p_invoice_switch := 'Inv_Multiple';
419 --p_invoice_number := 'Multiple'; --Pass the FND Message
420 p_invoice_number := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
421 end if;
422 end if;
423
424 CLOSE inv_cursor;
425
426 EXCEPTION WHEN OTHERS THEN
427 p_invoice_switch := 'inv_Excep';
428 End get_invoice_info_for_line;
429
430 END POS_VIEW_RECEIPTS_GRP;
431