[Home] [Help]
PACKAGE BODY: APPS.WSH_REPORT_QUANTITIES
Source
1 PACKAGE BODY WSH_REPORT_QUANTITIES AS
2 /* $Header: WSHUTRQB.pls 115.6 99/07/16 08:24:09 porting shi $ */
3
4
5 -- NAME: populate_temp_table
6 -- DESC: populated the temporary table with all the lines and their shipped
7 -- quantity this commits the records on creation
8 -- ARGS: report_id = must be a unique id, usual the request id of a conc prog.
9 -- p_mode = either PAK ir INV. calculates extra values if in one of
10 -- these.
11 -- p_departure_id
12 -- p_delivery_id
13 -- p_order_line
14 -- p_asn = will limit the Ship Qty to to this asn or greater
15 -- p_upd_ship = Update shipping flag: use this to reflect whether
16 -- you want the sq to be calculated only if update shipping
17 -- has run. Therefore if 'Y' then it will return zero if
18 -- update shipping has not run otherwise it return the
19 -- SC quantity.
20 -- p_debug = Flag to turn debugging information ON or OFF
21 --
22 PROCEDURE POPULATE_TEMP_TABLE (p_report_id IN NUMBER,
23 p_mode IN VARCHAR2 DEFAULT NULL,
24 p_departure_id IN NUMBER DEFAULT NULL,
25 p_delivery_id IN NUMBER DEFAULT NULL,
26 p_order_line IN NUMBER DEFAULT NULL,
27 p_asn IN NUMBER DEFAULT NULL,
28 p_upd_ship IN VARCHAR2 DEFAULT 'N',
29 p_debug IN VARCHAR2 DEFAULT 'OFF') is
30 BEGIN
31
32 DECLARE
33
34 l_dep_id NUMBER:= NULL;
35 l_shipped_td NUMBER:= NULL;
36 l_already_shipped NUMBER:= NULL;
37 l_sql_statement VARCHAR2(2000);
38 l_cursor INTEGER;
39 l_records_fetched NUMBER;
40
41 l_f_dep_id NUMBER;
42 l_f_del_id NUMBER;
43 l_f_asn NUMBER;
44 l_f_pick_line_id NUMBER;
45 l_f_line_id NUMBER;
46 l_f_comp_code VARCHAR2(1000);
47 l_f_comp_ratio NUMBER;
48 l_f_comp_seq_id NUMBER;
49 l_f_unit_code VARCHAR2(30);
50 l_f_warehouse_id NUMBER;
51 l_f_item_id NUMBER;
52 l_f_cust_item_id NUMBER;
53 l_f_ship_to_contact_id NUMBER;
54 l_f_shipped_qty NUMBER;
55
56 CURSOR SHIPPED_QUANTITIES (l_asn_num IN NUMBER,
57 l_pl_id IN NUMBER) IS
58 SELECT NVL(SUM(DECODE(p_upd_ship,'Y',
59 DECODE(PH.STATUS_CODE,'PENDING',0,'OPEN',0, PLD.SHIPPED_QUANTITY), PLD.SHIPPED_QUANTITY)), 0) SHIPPED_TD,
60 NVL(SUM(DECODE(D.ASN_SEQ_NUMBER,l_asn_num,0, DECODE(p_upd_ship,'Y', DECODE(PH.STATUS_CODE,'PENDING',0,'OPEN',0, PLD. SHIPPED_QUANTITY), PLD.SHIPPED_QUANTITY))), 0) ALREADY_SHIPPED
61 FROM SO_PICKING_HEADERS_ALL PH
62 , SO_PICKING_LINES_ALL PL2
63 , SO_PICKING_LINES_ALL PL1
64 , SO_PICKING_LINE_DETAILS PLD
65 , WSH_DELIVERIES D
66 WHERE PH.PICKING_HEADER_ID = PL2.PICKING_HEADER_ID
67 AND PL2.PICKING_LINE_ID = PLD.PICKING_LINE_ID
68 AND PL1.PICKING_LINE_ID = l_pl_id
69 AND PL1.ORDER_LINE_ID = PL2.ORDER_LINE_ID
70 AND D.DELIVERY_ID = PLD.DELIVERY_ID
71 AND DECODE(l_asn_num,NULL,-1,D.ASN_SEQ_NUMBER) <= nvl(l_asn_num,-1);
72
73 BEGIN
74
75 IF p_debug = 'ON' THEN
76
77 --dbms_output.enable(1000000);
78 --dbms_output.put_line('POPULATE_TEMP_TABLE Parameters: '||
79 -- 'Report Id: ' || p_report_id ||', '||
80 -- 'Mode: ' || p_mode ||', '||
81 -- 'Departure Id: '|| p_departure_id ||', '||
82 -- 'Delivery Id: ' || p_delivery_id ||', '||
83 -- 'Line Id: ' || p_order_line ||', '||
84 -- 'ASN: ' || p_asn ||', '||
85 -- 'Update Ship: ' || p_upd_ship );
86 null;
87
88 END IF;
89
90 -- If asn is given, select only those lines in this asn else select all
91 -- picking line details since, and including, this ASN but group by dep,
92 -- del/asn. We may have the same item in different parts of the model so
93 -- group by component code
94
95 -- Building main picking lines cursor to resolve a performance problem with
96 -- the old cursor statement which was causing a full table scan on
97 -- SO_PICKING_LINE_DETAILS
98
99 l_sql_statement := 'SELECT PLD.DEPARTURE_ID'||
100 ', PLD.DELIVERY_ID'||
101 ', D.ASN_SEQ_NUMBER'||
102 ', PL.PICKING_LINE_ID'||
103 ', PL.ORDER_LINE_ID'||
104 ', PL.COMPONENT_CODE'||
105 ', PL.COMPONENT_RATIO'||
106 ', PL.COMPONENT_SEQUENCE_ID'||
107 ', PL.UNIT_CODE'||
108 ', PL.WAREHOUSE_ID'||
109 ', PL.INVENTORY_ITEM_ID'||
110 ', PL.CUSTOMER_ITEM_ID'||
111 ', PL.SHIP_TO_CONTACT_ID'||
112 ', NVL(SUM(DECODE(:p_upd_ship,''Y'','||
113 ' DECODE(PH.STATUS_CODE,''PENDING'',0,''OPEN'',0, PLD.SHIPPED_QUANTITY),'||
114 ' PLD.SHIPPED_QUANTITY)),'||
115 ' 0)'||
116 'FROM WSH_DELIVERIES D'||
117 ', SO_PICKING_HEADERS_ALL PH'||
118 ', SO_PICKING_LINES_ALL PL'||
119 ', SO_PICKING_LINE_DETAILS PLD '||
120 'WHERE PH.PICKING_HEADER_ID = PL.PICKING_HEADER_ID '||
121 'AND PL.PICKING_LINE_ID = PLD.PICKING_LINE_ID '||
122 'AND PLD.DELIVERY_ID = D.DELIVERY_ID ';
123
124 -- Only attach the bind variables where clause statements if the variables
125 -- are not null
126
127 IF (p_departure_id IS NOT NULL) THEN
128 l_sql_statement := l_sql_statement ||
129 'AND PLD.DEPARTURE_ID = :p_departure_id ';
130 END IF;
131
132 IF (p_delivery_id IS NOT NULL) THEN
133 l_sql_statement := l_sql_statement ||
134 'AND PLD.DELIVERY_ID = :p_delivery_id ';
135 END IF;
136
137 IF (p_order_line IS NOT NULL) THEN
138 l_sql_statement := l_sql_statement ||
139 'AND PL.ORDER_LINE_ID = :p_order_line ';
140 END IF;
141
142 IF (p_asn IS NOT NULL) THEN
143
144 -- If the mode is ORDERLINE then we are being called by Automotive
145 -- and they require to view shipments that happened for any ASN
146 -- following the current one.
147 --
148 -- If the mode is not ORDERLINE include the passed ASN shipments into
149 -- the quantity calculations
150
151 IF (p_mode = 'ORDERLINE') THEN
152 l_sql_statement := l_sql_statement ||
153 'AND D.ASN_SEQ_NUMBER > :p_asn ';
154 ELSE
155 l_sql_statement := l_sql_statement ||
156 'AND D.ASN_SEQ_NUMBER >= :p_asn ';
157 END IF;
158
159 END IF;
160
161 l_sql_statement := l_sql_statement ||
162 'GROUP BY PLD.DEPARTURE_ID'||
163 ', PLD.DELIVERY_ID'||
164 ', D.ASN_SEQ_NUMBER'||
165 ', PL.PICKING_LINE_ID '||
166 ', PL.ORDER_LINE_ID '||
167 ', PL.COMPONENT_CODE'||
168 ', PL.COMPONENT_RATIO'||
169 ', PL.COMPONENT_SEQUENCE_ID'||
170 ', PL.UNIT_CODE'||
171 ', PL.WAREHOUSE_ID'||
172 ', PL.INVENTORY_ITEM_ID'||
173 ', PL.CUSTOMER_ITEM_ID'||
174 ', PL.SHIP_TO_CONTACT_ID '||
175 'ORDER BY PL.ORDER_LINE_ID';
176
177 IF p_debug = 'ON' THEN
178
179 -- Print SQL statement executed
180
181 --dbms_output.enable(1000000);
182 --dbms_output.put_line(substr(l_sql_statement,1,255));
183 --dbms_output.put_line(substr(l_sql_statement,256,255));
184 --dbms_output.put_line(substr(l_sql_statement,511,255));
185 --dbms_output.put_line(substr(l_sql_statement,766,255));
186 --dbms_output.put_line(substr(l_sql_statement,1021,255));
187 --dbms_output.put_line(substr(l_sql_statement,1276,255));
188 null;
189
190 END IF;
191
192 l_cursor := dbms_sql.open_cursor;
193 dbms_sql.parse(l_cursor,l_sql_statement,dbms_sql.v7);
194
195 dbms_sql.bind_variable(l_cursor,'p_upd_ship',p_upd_ship);
196
197 IF (p_departure_id IS NOT NULL) THEN
198 dbms_sql.bind_variable(l_cursor,'p_departure_id',p_departure_id);
199 END IF;
200
201 IF (p_delivery_id IS NOT NULL) THEN
202 dbms_sql.bind_variable(l_cursor,'p_delivery_id',p_delivery_id);
203 END IF;
204
205 IF (p_order_line IS NOT NULL) THEN
206 dbms_sql.bind_variable(l_cursor,'p_order_line',p_order_line);
207 END IF;
208
209 IF (p_asn IS NOT NULL) THEN
210 dbms_sql.bind_variable(l_cursor,'p_asn',p_asn);
211 END IF;
212
213 dbms_sql.define_column(l_cursor,1,l_f_dep_id);
214 dbms_sql.define_column(l_cursor,2,l_f_del_id);
215 dbms_sql.define_column(l_cursor,3,l_f_asn);
216 dbms_sql.define_column(l_cursor,4,l_f_pick_line_id);
217 dbms_sql.define_column(l_cursor,5,l_f_line_id);
218 dbms_sql.define_column(l_cursor,6,l_f_comp_code,1000);
219 dbms_sql.define_column(l_cursor,7,l_f_comp_ratio);
220 dbms_sql.define_column(l_cursor,8,l_f_comp_seq_id);
221 dbms_sql.define_column(l_cursor,9,l_f_unit_code,30);
222 dbms_sql.define_column(l_cursor,10,l_f_warehouse_id);
223 dbms_sql.define_column(l_cursor,11,l_f_item_id);
224 dbms_sql.define_column(l_cursor,12,l_f_cust_item_id);
225 dbms_sql.define_column(l_cursor,13,l_f_ship_to_contact_id);
226 dbms_sql.define_column(l_cursor,14,l_f_shipped_qty);
227
228 l_records_fetched := dbms_sql.execute(l_cursor);
229
230 WHILE (dbms_sql.fetch_rows(l_cursor) > 0) LOOP
231
232 dbms_sql.column_value(l_cursor,1,l_f_dep_id);
233 dbms_sql.column_value(l_cursor,2,l_f_del_id);
234 dbms_sql.column_value(l_cursor,3,l_f_asn);
235 dbms_sql.column_value(l_cursor,4,l_f_pick_line_id);
236 dbms_sql.column_value(l_cursor,5,l_f_line_id);
237 dbms_sql.column_value(l_cursor,6,l_f_comp_code);
238 dbms_sql.column_value(l_cursor,7,l_f_comp_ratio);
239 dbms_sql.column_value(l_cursor,8,l_f_comp_seq_id);
240 dbms_sql.column_value(l_cursor,9,l_f_unit_code);
241 dbms_sql.column_value(l_cursor,10,l_f_warehouse_id);
242 dbms_sql.column_value(l_cursor,11,l_f_item_id);
243 dbms_sql.column_value(l_cursor,12,l_f_cust_item_id);
244 dbms_sql.column_value(l_cursor,13,l_f_ship_to_contact_id);
245 dbms_sql.column_value(l_cursor,14,l_f_shipped_qty);
246
247 OPEN SHIPPED_QUANTITIES (l_f_asn, l_f_pick_line_id);
248 FETCH SHIPPED_QUANTITIES INTO l_shipped_td, l_already_shipped;
249 CLOSE SHIPPED_QUANTITIES;
250
251 INSERT INTO WSH_REPORT_TEMP
252 ( REPORT_TEMP_ID
253 , DEPARTURE_ID
254 , DELIVERY_ID
255 , SHIPPED_FLAG
256 , LINE_ID
257 , ITEM_INDENTATION
258 , COMPONENT_CODE
259 , COMPONENT_RATIO
260 , COMPONENT_SEQUENCE_ID
261 , ORGANIZATION_ID
262 , INVENTORY_ITEM_ID
263 , CUSTOMER_ITEM_ID
264 , SHIP_TO_CONTACT_ID
265 , SHIPPED_QUANTITY
266 , TOTAL_SHIPPED_TODATE
267 , TOTAL_ALREADY_SHIPPED
268 , QUANTITY_TO_INVOICE
269 , UNIT_OF_MEASURE
270 , CREATION_DATE
271 , CREATED_BY
272 , LAST_UPDATE_DATE
273 , LAST_UPDATED_BY)
274 VALUES
275 ( p_report_id
276 , l_f_dep_id
277 , l_f_del_id
278 , 'Y'
279 , l_f_line_id
280 , NVL(LENGTH(TRANSLATE(l_f_comp_code,'X1234567890','X')),0)+1
281 , l_f_comp_code
282 , l_f_comp_ratio
283 , l_f_comp_seq_id
284 , l_f_warehouse_id
285 , l_f_item_id
286 , l_f_cust_item_id
287 , l_f_ship_to_contact_id
288 , l_f_shipped_qty
289 , l_shipped_td
290 , l_already_shipped
291 , l_f_shipped_qty
292 , l_f_unit_code
293 , SYSDATE
294 , FND_GLOBAL.USER_ID
295 , SYSDATE
296 , FND_GLOBAL.USER_ID);
297
298 -- For this picking line, add the order line to temp table if it hasn't
299 -- already been added
300 --
301 -- NOTE: this could be an area for improving performance - we may want
302 -- to call this out of the loop
303
304 -- BUG 787126 : Adding p_mode to INSERT_ORDER_LINE to restrict ATO explosion
305 -- only for PACK SLIP
306 INSERT_ORDER_LINE(p_report_id, l_f_dep_id, l_f_del_id,l_f_line_id,p_mode);
307
308 -- Departure_id may have been null when called so assign it here
309
310 l_dep_id := l_f_dep_id;
311
312 END LOOP;
313
314 dbms_sql.close_cursor(l_cursor);
315
316 -- Update any detail, line and header attributes
317 -- Index on the INVENTORY_ITEM_ID for SO_LINE_DETAILS table is turned off
318 -- deliberately to make sure the index on LINE_ID is used.
319
320 UPDATE WSH_REPORT_TEMP R
321 SET
322 ( R.CONFIGURATION_ITEM_FLAG
323 , R.REQUIRED_FOR_REVENUE_FLAG
324 , R.COMPONENT_RATIO
325 , R.ORDERED_QUANTITY
326 , R.SELLING_PRICE
327 , R.ORDER_NUMBER
328 , R.PURCHASE_ORDER_NUM
329 , R.CURRENCY_CODE ) =
330 ( SELECT MAX(LD.CONFIGURATION_ITEM_FLAG)
331 , MAX(LD.REQUIRED_FOR_REVENUE_FLAG)
332 , DECODE(R.COMPONENT_RATIO,'',MAX(LD.COMPONENT_RATIO),
333 R.COMPONENT_RATIO)
334 , L.ORDERED_QUANTITY * NVL(R.COMPONENT_RATIO,1)
335 , L.SELLING_PRICE
336 , H.ORDER_NUMBER
337 , H.PURCHASE_ORDER_NUM
338 , H.CURRENCY_CODE
339 FROM SO_LINE_DETAILS LD
340 , SO_HEADERS_ALL H
341 , SO_LINES_ALL L
342 WHERE H.HEADER_ID = L.HEADER_ID
343 AND L.LINE_ID = LD.LINE_ID
344 AND LD.LINE_ID = R.LINE_ID
345 AND LD.INVENTORY_ITEM_ID+0 = R.INVENTORY_ITEM_ID
346 GROUP BY L.ORDERED_QUANTITY * NVL(R.COMPONENT_RATIO,1)
347 , L.SELLING_PRICE
348 , H.ORDER_NUMBER
349 , H.PURCHASE_ORDER_NUM
350 , H.CURRENCY_CODE )
351 , R.INCLUDE_ON_SHIP_DOCS =
352 ( SELECT BOM.INCLUDE_ON_SHIP_DOCS
353 FROM BOM_INVENTORY_COMPONENTS BOM
354 WHERE BOM.COMPONENT_SEQUENCE_ID = R.COMPONENT_SEQUENCE_ID
355 AND p_mode IN ('PAK','INV'))
356 WHERE R.REPORT_TEMP_ID = p_report_id
357 AND R.DEPARTURE_ID = l_dep_id;
358
359
360 ADD_NON_SHIP_LINES(p_report_id);
361 SET_SHIPPED_QUANTITY(p_report_id);
362
363 -- BUG : 787126 : Commenting out the following code as we want
364 -- to print the config items and option classes.
365 /*
366 IF p_mode = 'PAK' THEN
367
368 -- Dont print any config items (the report prints the actual model
369 -- instead) or option classes on the pack slip
370
371 UPDATE WSH_REPORT_TEMP R
372 SET R.INCLUDE_ON_SHIP_DOCS = 0
373 WHERE R.REPORT_TEMP_ID = p_report_id
374 AND R.DEPARTURE_ID = l_dep_id
375 AND (R.CONFIGURATION_ITEM_FLAG = 'Y'
376 OR
377 EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS M
378 WHERE M.INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID
379 AND M.ORGANIZATION_ID = R.ORGANIZATION_ID
380 AND M.ITEM_TYPE IN ('AOC','POC','OC')));
381
382 END IF;
383 */
384
385 -- BUG : 787126 : We do not want to print the quantities, UOM for
386 -- configuration items. So set them to NULL
387
388 IF p_mode = 'PAK' THEN
389
390 -- Dont print any config items (the report prints the actual model
391 -- instead) or option classes on the pack slip
392
393 UPDATE WSH_REPORT_TEMP R
394 SET R.ORDERED_QUANTITY = NULL,
395 R.SHIPPED_QUANTITY = NULL,
396 R.TOTAL_ALREADY_SHIPPED = NULL,
397 R.TOTAL_SHIPPED_TODATE = NULL,
398 R.QUANTITY_TO_INVOICE = NULL,
399 R.UNIT_OF_MEASURE = NULL
400 WHERE R.REPORT_TEMP_ID = p_report_id
404 END IF;
401 AND R.DEPARTURE_ID = l_dep_id
402 AND R.CONFIGURATION_ITEM_FLAG = 'Y';
403
405
406 -- End BUG 787126
407
408 IF p_mode = 'INV' THEN
409
410 -- Assign fields for commercial invoice report only
411
412 SET_INVOICE_QUANTITY(p_report_id);
413
414 -- Dont print any config items (the report prints the actual model
415 -- instead)
416
417 UPDATE WSH_REPORT_TEMP
418 SET INCLUDE_ON_SHIP_DOCS = 0
419 WHERE REPORT_TEMP_ID = p_report_id
420 AND DEPARTURE_ID = l_dep_id
421 AND CONFIGURATION_ITEM_FLAG = 'Y';
422
423 END IF;
424
425 IF p_debug = 'ON' THEN
426
427 FOR DREC IN (SELECT * FROM WSH_REPORT_TEMP
428 WHERE REPORT_TEMP_ID = p_report_id) LOOP
429
430 --dbms_output.enable(1000000);
431 --dbms_output.put_line('Order Number: '||DREC.order_number);
432 --dbms_output.put_line('Purchase Order Num: '||
433 -- DREC.purchase_order_num);
434 --dbms_output.put_line('Currency Code: '||DREC.currency_code);
435 --dbms_output.put_line('Line Id: '||DREC.line_id);
436 --dbms_output.put_line('Include On Ship Docs: '||
437 -- DREC.include_on_ship_docs);
438 --dbms_output.put_line('Configuration Item Flag: '||
439 -- DREC.configuration_item_flag);
440 --dbms_output.put_line('Required For Revenue Flag: '||
441 -- DREC.required_for_revenue_flag);
442 --dbms_output.put_line('Ordered Quantity: '||DREC.ordered_quantity);
443 --dbms_output.put_line('Shipped Quantity: '||DREC.Shipped_quantity);
444 --dbms_output.put_line('Total Shipped Todate: '||
445 -- DREC.total_shipped_todate);
446 --dbms_output.put_line('Total Already Shipped: '||
447 -- DREC.total_already_shipped);
448 --dbms_output.put_line('Inventory Item Id: '||DREC.inventory_item_id);
449 --dbms_output.put_line('Component Code: '||DREC.component_code);
450 --dbms_output.put_line('Component Ratio: '||DREC.component_ratio);
451 --dbms_output.put_line('Component Sequence Id: '||
452 -- DREC.component_sequence_id);
453 --dbms_output.put_line('Child Rfr Flag: '||DREC.child_rfr_flag);
454 --dbms_output.put_line('Quantity To Invoice: '||
455 -- DREC.quantity_to_invoice);
456 --dbms_output.put_line('Unit Of Measure: '||DREC.unit_of_measure);
457 --dbms_output.put_line('Customer Item Id: '||DREC.customer_item_id);
458 --dbms_output.put_line('Ship To Contact Id: '||
459 -- DREC.ship_to_contact_id);
460 --dbms_output.put_line('Shipped Flag: '||DREC.shipped_flag);
461 --dbms_output.put_line('Organization Id: '||DREC.organization_id);
462 --dbms_output.put_line('Item Indentation: '||DREC.item_indentation);
463 --dbms_output.put_line('Departure Id: '||DREC.departure_id);
464 --dbms_output.put_line('Delivery Id: '||DREC.delivery_id);
465 null;
466
467 END LOOP;
468
469 END IF;
470
471 EXCEPTION WHEN OTHERS THEN
472
473 -- Check if cursor for picking lines is open and if it is close it
474
475 IF dbms_sql.is_open(l_cursor) THEN
476 dbms_sql.close_cursor(l_cursor);
477 END IF;
478
479 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
480 FND_MESSAGE.Set_Token('PACKAGE','WSH_REPORT_QUANTITIES.populate_temp_table');
481 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
482 APP_EXCEPTION.Raise_Exception;
483
484 END;
485
486
487 END POPULATE_TEMP_TABLE;
488
489
490
491 PROCEDURE ADD_NON_SHIP_LINES (p_report_id IN NUMBER,
492 p_debug IN VARCHAR2 DEFAULT 'OFF') IS
493
494 -- insert any items that are in so_line_details only: ie non option components
495 -- which are not shippable but a mandatory part of the configuration
496 -- (eg kits in a model, models in a model)
497 -- These are required so as to complete the bill stucture without which the
498 -- bottomup shipped_quantity calulation will not work as well as for printing
499 -- the complete bill
500
501 BEGIN
502
503 DECLARE
504
505 CURSOR NON_SHIP_LINES (l_rep_id IN NUMBER) IS
506 SELECT DISTINCT RT.DEPARTURE_ID
507 , RT.DELIVERY_ID
508 , LD.LINE_ID
509 , LD.COMPONENT_CODE
510 , LD.COMPONENT_RATIO
511 , LD.COMPONENT_SEQUENCE_ID
512 , NVL(LENGTH(TRANSLATE(LD.COMPONENT_CODE,'X1234567890','X')),0)+1
513 ITEM_INDENTATION -- strips all numerics and counts hyphen
514 , LD.WAREHOUSE_ID
515 , LD.INVENTORY_ITEM_ID
516 , LD.CUSTOMER_ITEM_ID
517 , LD.CONFIGURATION_ITEM_FLAG
518 , LD.REQUIRED_FOR_REVENUE_FLAG
519 , LD.UNIT_CODE
520 FROM SO_LINE_DETAILS LD
521 , WSH_REPORT_TEMP RT
522 WHERE LD.LINE_ID = RT.LINE_ID
523 AND LD.SHIPPABLE_FLAG = 'N'
524 AND RT.REPORT_TEMP_ID = l_rep_id
525 -- make sure the non shipable line detail is not an order line.
526 AND NOT EXISTS (SELECT 'ORDERED LINE'
527 FROM SO_LINES_ALL L
528 WHERE L.LINE_ID = LD.LINE_ID
529 AND L.COMPONENT_CODE = LD.COMPONENT_CODE);
530 /**** always select non shippable lines irrespective if any component lines where pick releases
531 and exists (select 'shipped component line'
532 from so_picking_lines_all
533 where order_line_id in (select line_id from wsh_report_temp where report_temp_id= rep_id)
537
534 and component_code != ld.component_code
535 and component_code like ld.component_code||'%')
536 ***/
538 BEGIN
539
540 IF p_debug = 'ON' THEN
541
542 --dbms_output.enable(1000000);
543 --dbms_output.put_line('ADD_NON_SHIP_LINES Parameters: '||
544 -- 'Report Id: ' || p_report_id);
545 null;
546
547 END IF;
548
549 FOR NSLINE IN NON_SHIP_LINES(p_report_id) LOOP
550
551 INSERT INTO WSH_REPORT_TEMP
552 ( REPORT_TEMP_ID
553 , DEPARTURE_ID
554 , DELIVERY_ID
555 , SHIPPED_FLAG
556 , LINE_ID
557 , ITEM_INDENTATION
558 , ORGANIZATION_ID
559 , INVENTORY_ITEM_ID
560 , CUSTOMER_ITEM_ID
561 , CONFIGURATION_ITEM_FLAG
562 , REQUIRED_FOR_REVENUE_FLAG
563 , COMPONENT_CODE
564 , COMPONENT_RATIO
565 , COMPONENT_SEQUENCE_ID
566 , UNIT_OF_MEASURE
567 , CREATION_DATE
568 , CREATED_BY
569 , LAST_UPDATE_DATE
570 , LAST_UPDATED_BY)
571 VALUES
572 ( p_report_id
573 , NSLINE.departure_id
574 , NSLINE.delivery_id
575 , 'N'
576 , NSLINE.line_id
577 , NSLINE.item_indentation
578 , NSLINE.warehouse_id
579 , NSLINE.inventory_item_id
580 , NSLINE.customer_item_id
581 , NSLINE.configuration_item_flag
582 , NSLINE.required_for_revenue_flag
583 , NSLINE.component_code
584 , NSLINE.component_ratio
585 , NSLINE.component_sequence_id
586 , NSLINE.unit_code
587 , SYSDATE
588 , FND_GLOBAL.USER_ID
589 , SYSDATE
590 , FND_GLOBAL.USER_ID);
591
592 END LOOP;
593
594 EXCEPTION WHEN OTHERS THEN
595 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
596 FND_MESSAGE.Set_Token('PACKAGE','WSH_REPORT_QUANTITIES.add_non_ship_lines');
597 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
598 APP_EXCEPTION.Raise_Exception;
599
600 END;
601
602 END ADD_NON_SHIP_LINES;
603
604 PROCEDURE SET_SHIPPED_QUANTITY (p_report_id IN NUMBER,
605 p_debug IN VARCHAR2 DEFAULT 'OFF') IS
606
607 -- Example: Kit with order qty = 10 sent in 3 deliveries/asn
608 -- below diagram shows shipped quantities:
609 -- Del1 Del2 Del3
610 --
611 -- Kit: 2 1 4 2+1+4 = 7 total
612 -- / \ / \ / \ this is wrong
613 -- Items: 2 5 3 1 5 4
614
615 -- ShippedQty 2 5 5 6 10 10
616 -- AlreadyShipd 0 0 2 5 5 6
617 -- for the KIT
618 -- shipped qty todt 2 5 10
619 -- alreadyShpd 0 2 5
620 -- difference = 2 3 5 2+3+5 = 10
621 -- this is correct
622
623
624 BEGIN
625
626 DECLARE
627
628 CURSOR MAX_LEVELS (l_rep_id IN NUMBER) IS
629 SELECT MAX(ITEM_INDENTATION)
630 FROM WSH_REPORT_TEMP
631 WHERE REPORT_TEMP_ID = l_rep_id;
632
633 l_i NUMBER;
634 l_max_l NUMBER;
635
636 BEGIN
637
638 IF p_debug = 'ON' THEN
639
640 --dbms_output.enable(1000000);
641 --dbms_output.put_line('SET_SHIPPED_QUANTITY Parameters: '||
642 -- 'Report Id: ' || p_report_id);
643 null;
644
645 END IF;
646
647 -- shipped_quantity = min(total_shipped_to_date) - min(already_shipped)
648 -- where to_date and already are relative to the ASN_SEQ_NUMBER
649 -- which in turn is relative to the delivery.date_closed
650 --
651 -- note: the shipped_qty for the bottom of the BOM (the picking lines) is
652 -- already set (it was selected from the picking lines). So only set the qty
653 -- if it doesnt already exist
654 -- also propagate the ship_to_contact_id up the BOM
655
656 OPEN MAX_LEVELS (p_report_id);
657 FETCH MAX_LEVELS INTO l_max_l;
658 CLOSE MAX_LEVELS;
659
660 IF l_max_l > 1 THEN
661
662 FOR l_i IN REVERSE 1..l_max_l LOOP
663
664 UPDATE WSH_REPORT_TEMP R
665 SET ( R.TOTAL_SHIPPED_TODATE
666 , R.TOTAL_ALREADY_SHIPPED
667 , R.SHIPPED_QUANTITY
668 , R.SHIP_TO_CONTACT_ID ) =
669 ( SELECT MIN(S.TOTAL_SHIPPED_TODATE/S.COMPONENT_RATIO)
670 , MIN(S.TOTAL_ALREADY_SHIPPED/S.COMPONENT_RATIO)
671 , MIN(S.TOTAL_SHIPPED_TODATE/S.COMPONENT_RATIO) -
672 MIN(S.TOTAL_ALREADY_SHIPPED/S.COMPONENT_RATIO)
673 , MIN(S.SHIP_TO_CONTACT_ID)
674 FROM WSH_REPORT_TEMP S
675 WHERE S.REPORT_TEMP_ID = R.REPORT_TEMP_ID
676 AND S.DELIVERY_ID = R.DELIVERY_ID
677 AND S.ITEM_INDENTATION = R.ITEM_INDENTATION + 1
678 AND S.COMPONENT_CODE LIKE R. COMPONENT_CODE||'%')
679 WHERE R.ITEM_INDENTATION = l_i
680 AND R.REPORT_TEMP_ID = p_report_id
681 AND R.SHIPPED_QUANTITY IS NULL;
682
686
683 END LOOP;
684
685 END IF;
687 EXCEPTION WHEN OTHERS THEN
688 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
689 FND_MESSAGE.Set_Token('PACKAGE','WSH_REPORT_QUANTITIES.set_shipped_quantity');
690 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
691 APP_EXCEPTION.Raise_Exception;
692 END;
693
694 END SET_SHIPPED_QUANTITY;
695
696 PROCEDURE SET_INVOICE_QUANTITY (p_report_id IN NUMBER,
697 p_debug IN VARCHAR2 DEFAULT 'OFF') IS
698 BEGIN
699 DECLARE
700
701 CURSOR MAX_LEVELS (l_rep_id IN NUMBER) IS
702 SELECT MAX(ITEM_INDENTATION)-1
703 FROM WSH_REPORT_TEMP
704 WHERE REPORT_TEMP_ID = l_rep_id;
705
706 l_i NUMBER;
707 l_max_l NUMBER;
708
709 BEGIN
710
711 IF p_debug = 'ON' THEN
712
713 --dbms_output.enable(1000000);
714 --dbms_output.put_line('SET_INVOICE_QUANTITY Parameters: '||
715 -- 'Report Id: ' || p_report_id);
716 null;
717
718 END IF;
719
720 OPEN MAX_LEVELS (p_report_id);
721 FETCH MAX_LEVELS INTO l_max_l;
722 CLOSE MAX_LEVELS;
723
724
725 IF l_max_l > 0 THEN
726
727 FOR l_i IN REVERSE 1..l_max_l LOOP
728
729 UPDATE WSH_REPORT_TEMP R
730 SET ( R.QUANTITY_TO_INVOICE, R.CHILD_RFR_FLAG) =
731 ( SELECT MIN (S.TOTAL_SHIPPED_TODATE/S.COMPONENT_RATIO) -
732 MIN (S.TOTAL_ALREADY_SHIPPED/S.COMPONENT_RATIO)
733 , 'Y'
734 FROM WSH_REPORT_TEMP S
735 WHERE S.REPORT_TEMP_ID = R.REPORT_TEMP_ID
736 AND S.DELIVERY_ID = R.DELIVERY_ID
737 AND S.ITEM_INDENTATION = R.ITEM_INDENTATION + 1
738 AND (S.REQUIRED_FOR_REVENUE_FLAG = 'Y'
739 OR
740 S.CHILD_RFR_FLAG = 'Y')
741 AND S.COMPONENT_CODE LIKE R.COMPONENT_CODE||'%')
742 WHERE R.ITEM_INDENTATION = l_i
743 AND R.QUANTITY_TO_INVOICE IS NULL
744 AND R.REPORT_TEMP_ID = p_report_id;
745
746 -- do the same select again but for not required for revenue
747 -- note: the only difference between these 2 is wether it select Y or N
748 -- unfortunately we cant use decode because of the group function min()
749
750 UPDATE WSH_REPORT_TEMP R
751 SET ( R.QUANTITY_TO_INVOICE, R.CHILD_RFR_FLAG) =
752 ( SELECT MIN (S.TOTAL_SHIPPED_TODATE/S.COMPONENT_RATIO) -
753 MIN (S.TOTAL_ALREADY_SHIPPED/S.COMPONENT_RATIO)
754 , 'N'
755 FROM WSH_REPORT_TEMP S
756 WHERE S.REPORT_TEMP_ID = R.REPORT_TEMP_ID
757 AND S.DELIVERY_ID = R.DELIVERY_ID
758 AND S.ITEM_INDENTATION = R.ITEM_INDENTATION + 1
759 AND S.COMPONENT_CODE LIKE R.COMPONENT_CODE||'%')
760 WHERE R.ITEM_INDENTATION = l_i
761 AND R.REPORT_TEMP_ID = p_report_id
762 AND R.QUANTITY_TO_INVOICE IS NULL;
763
764 END LOOP;
765 END IF;
766
767 EXCEPTION WHEN OTHERS THEN
768 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
769 FND_MESSAGE.Set_Token('PACKAGE','WSH_REPORT_QUANTITIES.set_invoice_quantity');
770 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
771 APP_EXCEPTION.Raise_Exception;
772 END;
773 END SET_INVOICE_QUANTITY;
774
775
776
777 -- NAME: insert_order_line
778 -- DESC:
779 -- This inserts any order lines for a picking line
780 -- if they havent already been added to the temp table.
781 -- If the order line being added has a link_to_id,
782 -- then this traverses up the link-to relationship.
783
784 PROCEDURE INSERT_ORDER_LINE (p_report_id IN NUMBER,
785 p_departure_id IN NUMBER,
786 p_delivery_id IN NUMBER,
787 p_line_id IN NUMBER,
788 p_mode IN VARCHAR2 DEFAULT NULL,
789 p_debug IN VARCHAR2 DEFAULT 'OFF') IS
790 BEGIN
791 DECLARE
792
793 CURSOR ORDER_LINES (l_rep_id IN NUMBER,
794 l_del_id IN NUMBER,
795 l_id IN NUMBER) IS
796 SELECT L.LINK_TO_LINE_ID
797 , L.COMPONENT_CODE
798 , L.COMPONENT_SEQUENCE_ID
799 , L.WAREHOUSE_ID
800 , L.INVENTORY_ITEM_ID
801 , L.CUSTOMER_ITEM_ID
802 , L.ORDERED_QUANTITY
803 , L.SELLING_PRICE
804 , L.UNIT_CODE
805 , NVL(L.ATO_FLAG,'N')
806 FROM SO_LINES_ALL L
807 WHERE L.LINE_ID = l_id
808 AND L.LINE_TYPE_CODE IN ('DETAIL','REGULAR')
809 AND NOT EXISTS (SELECT 'ALREADY EXISTS IN TEMP TABLE'
810 FROM WSH_REPORT_TEMP T
811 WHERE T.REPORT_TEMP_ID = l_rep_id
812 AND T.LINE_ID = L.LINE_ID
813 AND T.DELIVERY_ID = l_del_id
814 AND T.SHIPPED_FLAG = 'N');
815
816 l_component_code VARCHAR2(1000);
817 l_component_sequence_id NUMBER;
818 l_customer_item_id NUMBER;
819 l_departure_id NUMBER;
820 l_warehouse_id NUMBER;
821 l_item_id NUMBER;
822 l_link_line NUMBER;
823 l_ordered_quantity NUMBER;
824 l_selling_price NUMBER;
825 l_unit_code VARCHAR2(3);
826 l_ato_flag VARCHAR2(1); -- 787126
827
831 -- Since we want to explode the ATO Items
828 BEGIN
829
830 -- BUG 787126 : Added ATO_FLAG to the ORDER_LINES Cursor
832
833 IF p_debug = 'ON' THEN
834
835 --dbms_output.enable(1000000);
836 --dbms_output.put_line('INSERT_ORDER_LINE Parameters: ' ||
837 -- 'Report Id: ' || p_report_id ||', '||
838 -- 'Departure Id: ' || p_departure_id ||', '||
839 -- 'Delivery Id: ' || p_delivery_id ||', '||
840 -- 'Line Id: ' || p_line_id);
841 null;
842
843 END IF;
844
845 OPEN ORDER_LINES(p_report_id, p_delivery_id, p_line_id);
846 FETCH ORDER_LINES INTO l_link_line, l_component_code, l_component_sequence_id,
847 l_warehouse_id, l_item_id, l_customer_item_id, l_ordered_quantity,
848 l_selling_price, l_unit_code,l_ato_flag;
849
850
851 IF ORDER_LINES%FOUND THEN
852 INSERT INTO WSH_REPORT_TEMP
853 ( REPORT_TEMP_ID
854 , DEPARTURE_ID
855 , DELIVERY_ID
856 , SHIPPED_FLAG
857 , LINE_ID
858 , COMPONENT_CODE
859 , COMPONENT_SEQUENCE_ID
860 , ITEM_INDENTATION
861 , ORGANIZATION_ID
862 , INVENTORY_ITEM_ID
863 , CUSTOMER_ITEM_ID
864 , ORDERED_QUANTITY
865 , SELLING_PRICE
866 , UNIT_OF_MEASURE
867 , CREATION_DATE
868 , CREATED_BY
869 , LAST_UPDATE_DATE
870 , LAST_UPDATED_BY)
871 SELECT p_report_id
872 , p_departure_id
873 , p_delivery_id
874 , 'N'
875 , p_line_id
876 , l_component_code
877 , l_component_sequence_id
878 , NVL(LENGTH(TRANSLATE(l_component_code,'X1234567890','X')),0)+1
879 , l_warehouse_id
880 , l_item_id
881 , l_customer_item_id
882 , l_ordered_quantity
883 , l_selling_price
884 , l_unit_code
885 , SYSDATE
886 , FND_GLOBAL.user_id
887 , SYSDATE
888 , FND_GLOBAL.user_id
889 FROM DUAL
890 WHERE NOT EXISTS
891 (SELECT 'ALREADY EXISTS IN TEMP TABLE'
892 FROM WSH_REPORT_TEMP
893 WHERE REPORT_TEMP_ID = p_report_id
894 AND LINE_ID = p_line_id
895 AND DELIVERY_ID = p_delivery_id
896 AND INVENTORY_ITEM_ID = l_item_id);
897
898 CLOSE ORDER_LINES;
899
900 -- BUG 787126 : Explode the ATO Model top down using ATO_LINE_ID
901
902 IF (l_ato_flag = 'Y' and p_mode = 'PAK') THEN
903 INSERT_ATO_COMPONENTS(p_report_id, p_departure_id, p_delivery_id, p_line_id,p_mode);
904 END IF;
905
906 -- END BUG 787126
907
908 -- EXPLODE the PTO items bottom up using link to line id
909
910 IF l_link_line IS NOT NULL THEN
911 INSERT_ORDER_LINE (p_report_id, p_departure_id, p_delivery_id, l_link_line,p_mode);
912 END IF;
913
914 ELSE
915 CLOSE ORDER_LINES;
916 END IF;
917
918 EXCEPTION WHEN OTHERS THEN
919 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
920 FND_MESSAGE.Set_Token('PACKAGE','WSH_REPORT_QUANTITIES.insert_order_line');
921 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
922 APP_EXCEPTION.Raise_Exception;
923 END;
924 END INSERT_ORDER_LINE;
925
926 -- BUG 787126 : New Procedure
927 -- NAME: insert_ato_components
928 -- DESC:
929 -- This inserts the ATO components for the order line
930
931 PROCEDURE INSERT_ATO_COMPONENTS (p_report_id IN NUMBER,
932 p_departure_id IN NUMBER,
933 p_delivery_id IN NUMBER,
934 p_line_id IN NUMBER,
935 p_mode IN VARCHAR2 DEFAULT NULL,
936 p_debug IN VARCHAR2 DEFAULT 'OFF') IS
937 BEGIN
938 DECLARE
939
940 CURSOR ORDER_LINES (l_rep_id IN NUMBER,
941 l_del_id IN NUMBER,
942 l_id IN NUMBER) IS
943 SELECT L.LINK_TO_LINE_ID
944 , L.COMPONENT_CODE
945 , L.COMPONENT_SEQUENCE_ID
946 , L.WAREHOUSE_ID
947 , L.INVENTORY_ITEM_ID
948 , L.CUSTOMER_ITEM_ID
949 , L.ORDERED_QUANTITY
950 , L.SELLING_PRICE
951 , L.UNIT_CODE
952 , L.LINE_ID
953 FROM SO_LINES_ALL L
954 WHERE L.ATO_LINE_ID = l_id
955 AND L.LINE_TYPE_CODE IN ('DETAIL','REGULAR')
956 AND NOT EXISTS (SELECT 'ALREADY EXISTS IN TEMP TABLE'
957 FROM WSH_REPORT_TEMP T
958 WHERE T.REPORT_TEMP_ID = l_rep_id
959 AND T.LINE_ID = L.LINE_ID
960 AND T.DELIVERY_ID = l_del_id
961 AND T.SHIPPED_FLAG = 'N');
962
963 l_component_code VARCHAR2(1000);
964 l_component_sequence_id NUMBER;
965 l_customer_item_id NUMBER;
966 l_departure_id NUMBER;
967 l_warehouse_id NUMBER;
968 l_item_id NUMBER;
969 l_link_line NUMBER;
970 l_ordered_quantity NUMBER;
971 l_selling_price NUMBER;
972 l_unit_code VARCHAR2(3);
973 l_line_id NUMBER;
974
975 BEGIN
976
977
981 --dbms_output.put_line('INSERT_ATO_COMPONENTS Parameters: ' ||
978 IF p_debug = 'ON' THEN
979
980 --dbms_output.enable(1000000);
982 -- 'Report Id: ' || p_report_id ||', '||
983 -- 'Departure Id: ' || p_departure_id ||', '||
984 -- 'Delivery Id: ' || p_delivery_id ||', '||
985 -- 'Line Id: ' || p_line_id);
986 null;
987
988 END IF;
989
990 OPEN ORDER_LINES(p_report_id, p_delivery_id, p_line_id);
991 LOOP
992 FETCH ORDER_LINES INTO l_link_line, l_component_code, l_component_sequence_id,
993 l_warehouse_id, l_item_id, l_customer_item_id, l_ordered_quantity,
994 l_selling_price, l_unit_code,l_line_id;
995
996
997 EXIT WHEN ORDER_LINES%NOTFOUND;
998
999 INSERT INTO WSH_REPORT_TEMP
1000 ( REPORT_TEMP_ID
1001 , DEPARTURE_ID
1002 , DELIVERY_ID
1003 , SHIPPED_FLAG
1004 , LINE_ID
1005 , COMPONENT_CODE
1006 , COMPONENT_SEQUENCE_ID
1007 , ITEM_INDENTATION
1008 , ORGANIZATION_ID
1009 , INVENTORY_ITEM_ID
1010 , CUSTOMER_ITEM_ID
1011 , ORDERED_QUANTITY
1012 , SELLING_PRICE
1013 , UNIT_OF_MEASURE
1014 , CREATION_DATE
1015 , CREATED_BY
1016 , LAST_UPDATE_DATE
1017 , LAST_UPDATED_BY)
1018 SELECT p_report_id
1019 , p_departure_id
1020 , p_delivery_id
1021 , 'N'
1022 , l_line_id
1023 , l_component_code
1024 , l_component_sequence_id
1025 , NVL(LENGTH(TRANSLATE(l_component_code,'X1234567890','X')),0)+1
1026 , l_warehouse_id
1027 , l_item_id
1028 , l_customer_item_id
1029 , l_ordered_quantity
1030 , l_selling_price
1031 , l_unit_code
1032 , SYSDATE
1033 , FND_GLOBAL.user_id
1034 , SYSDATE
1035 , FND_GLOBAL.user_id
1036 FROM DUAL
1037 WHERE NOT EXISTS
1038 (SELECT 'ALREADY EXISTS IN TEMP TABLE'
1039 FROM WSH_REPORT_TEMP
1040 WHERE REPORT_TEMP_ID = p_report_id
1041 AND LINE_ID = l_line_id
1042 AND DELIVERY_ID = p_delivery_id
1043 AND INVENTORY_ITEM_ID = l_item_id);
1044
1045
1046 -- EXPLODE the PTO items bottom up using link to line id
1047
1048 IF l_link_line IS NOT NULL THEN
1049 INSERT_ORDER_LINE (p_report_id, p_departure_id, p_delivery_id, l_link_line, p_mode);
1050 END IF;
1051
1052 END LOOP;
1053
1054 CLOSE ORDER_LINES;
1055
1056 EXCEPTION WHEN OTHERS THEN
1057 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1058 FND_MESSAGE.Set_Token('PACKAGE','WSH_REPORT_QUANTITIES.insert_ato_components');
1059 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1060 APP_EXCEPTION.Raise_Exception;
1061 END;
1062 END INSERT_ATO_COMPONENTS;
1063
1064 -- NAME: lines_shipped_quantity
1065 -- DESC: returns the shipped quantity for a particular so_line in the
1066 -- given asn and there after
1067 -- this rollbacks all rows it created in the temp table at end.
1068 -- ARGS: p_order_line = so_lines.line_id
1069 -- p_item_id = item_id
1070 -- p_asn = asn sequence number
1071 -- p_upd_ship = Update shipping flag: use this to reflect whether
1072 -- you want the sq to be calculated only if update shipping
1073 -- has run. Therefore if 'Y' then it will return zero if
1074 -- update shipping has not run otherwise it return the
1075 -- SC quantity.
1076 -- p_debug = Flag to turn debugging information ON or OFF
1077 --
1078 --
1079 --
1080 FUNCTION LINE_SHIPPED_QUANTITY (p_order_line IN NUMBER,
1081 p_item_id IN NUMBER,
1082 p_asn IN NUMBER,
1083 p_upd_ship IN VARCHAR2 DEFAULT 'N',
1084 p_debug IN VARCHAR2 DEFAULT 'OFF') RETURN NUMBER IS
1085 BEGIN
1086 DECLARE
1087
1088 l_sq NUMBER;
1089 CURSOR SHIPPED_QUANTITY (l_rep_id IN NUMBER,
1090 l_ord_line_id IN NUMBER,
1091 l_item_id IN NUMBER) IS
1092 SELECT SUM(SHIPPED_QUANTITY)
1093 FROM WSH_REPORT_TEMP
1094 WHERE REPORT_TEMP_ID = l_rep_id
1095 AND LINE_ID = l_ord_line_id
1096 AND INVENTORY_ITEM_ID = l_item_id;
1097
1098 BEGIN
1099
1100 IF p_debug = 'ON' THEN
1101
1102 --dbms_output.enable(1000000);
1103 --dbms_output.put_line('INSERT_ORDER_LINE Parameters: ' ||
1104 -- 'ASN: ' || p_asn ||', '||
1105 -- 'Line Id: ' || p_order_line ||', '||
1106 -- 'Item Id: ' || p_item_id ||', '||
1107 -- 'Update Ship: ' || p_upd_ship);
1108 null;
1109
1110 END IF;
1111
1112 SAVEPOINT START_OF_FUNCTION;
1113
1114 POPULATE_TEMP_TABLE (p_report_id => -100,
1115 p_mode => 'ORDERLINE',
1116 p_order_line => p_order_line,
1117 p_asn => p_asn,
1118 p_upd_ship => p_upd_ship,
1122 FETCH SHIPPED_QUANTITY INTO l_sq;
1119 p_debug => p_debug );
1120
1121 OPEN SHIPPED_QUANTITY (-100, p_order_line, p_item_id);
1123 CLOSE SHIPPED_QUANTITY;
1124
1125 ROLLBACK TO SAVEPOINT START_OF_FUNCTION;
1126
1127 RETURN(l_sq);
1128
1129 EXCEPTION WHEN OTHERS THEN
1130 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1131 FND_MESSAGE.Set_Token('PACKAGE','WSH_REPORT_QUANTITIES.line_shipped_quantity');
1132 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1133 APP_EXCEPTION.Raise_Exception;
1134 END;
1135
1136 END LINE_SHIPPED_QUANTITY;
1137
1138 PROCEDURE DELETE_REPORT (p_report_id IN NUMBER) IS
1139 BEGIN
1140
1141 DELETE FROM WSH_REPORT_TEMP
1142 WHERE REPORT_TEMP_ID = p_report_id
1143 OR CREATION_DATE < sysdate - 2;
1144
1145 END DELETE_REPORT;
1146
1147 end WSH_REPORT_QUANTITIES;