[Home] [Help]
PACKAGE BODY: APPS.POR_RCV_ORD_SV
Source
1 package body POR_RCV_ORD_SV as
2 /* $Header: PORRCVOB.pls 120.11 2011/01/12 02:46:16 chihchan ship $ */
3
4 /*************************************************************
5 ** Public Function :
6 ** groupPoTransaction
7 ** Description :
8 ** grouping logic to group and split into transaction lines
9 ** each transaction line is inserted to rcv transaction interface
10 ** return true if grouping is successful
11 **************************************************************/
12
13 function replaceNull(inValue in number)
14 return number
15 is
16 begin
17 if (inValue = -9999) then
18 return null;
19 end if;
20
21 return inValue;
22
23 end;
24
25 function groupPoTransaction (X_po_header_id IN rcvNumberArray,
26 X_line_location_id IN rcvNumberArray,
27 X_receipt_qty IN rcvNumberArray,
28 X_receipt_uom IN rcvVarcharArray,
29 X_receipt_date IN date,
30 X_item_id IN rcvNumberArray,
31 X_uom_class IN rcvVarcharArray,
32 X_org_id IN rcvNumberArray,
33 X_po_distribution_id IN rcvNumberArray,
34 X_group_id IN number,
35 X_caller IN varchar2,
36 X_Comments IN rcvVarcharArray,
37 X_PackingSlip IN rcvVarcharArray,
38 X_WayBillNum IN rcvVarcharArray)
39 return number
40 is
41
42
43 cursor rcv_header(p_lineLocationId number) is
44 select rsl.shipment_line_id, rsl.quantity_shipped, nvl(rsl.quantity_received,0), rsl.unit_of_measure
45 from rcv_shipment_lines rsl, rcv_shipment_headers rsh
46 where rsl.po_line_location_id = p_lineLocationId
47 and nvl(rsl.quantity_shipped, 0) > nvl(rsl.quantity_received, 0)
48 and rsl.shipment_header_id = rsh.shipment_header_id
49 and rsh.asn_type in ('ASN','ASBN');
50
51 l_lineLocationId number := 0;
52 l_qty number := 0;
53 l_toReceiveQty number := 0; -- remain to receive quantity
54 l_shippedQty number := 0;
55 l_receivedQty number := 0;
56
57 l_shippedRcvUomQty number := 0;
58 l_receivedRcvUomQty number := 0;
59 l_rcvUom varchar2(40);
60 l_userReceiptUom varchar2(40);
61 l_itemId number :=0;
62
63 l_receiptQty number := 0;
64 l_rcvLineId number := 0;
65 hasEntryForRcvLine boolean;
66 l_recorder rcvInfoTable;
67 l_index number;
68 x_progress varchar2(240);
69 x_user_org_id NUMBER;
70 x_txn_org_id NUMBER;
71
72 /* dev notes
73 1. how to remember which receipts are created?
74 do not have to, because we just need to scan for the rcv transaction table for this group id later
75 2. quantity and its related uom
76 the following quantities are first quaried from db as in a unit of rcv uom, but need to be converted according to user receiving uom X_receipt_uom
77 l_shippedQty is converted from l_shippedRcvUomQty
78 l_receivedQty is converted from l_receivedRcvUomQty
79 */
80
81 begin
82
83 -- need to construct a recorder within for every associated rcv line
84 -- so that we know how many is left for a shipment line
85 if (x_po_header_id is null) then
86 return 1;
87 end if;
88
89 x_user_org_id := MO_GLOBAL.get_current_org_id;
90
91 for i in 1..x_po_header_id.count loop
92 l_lineLocationId := x_line_location_id(i);
93 l_qty := x_receipt_qty(i);
94 l_toReceiveQty := l_qty;
95 l_userReceiptUom := X_receipt_uom(i);
96
97 x_progress := 'groupPoTransaction 000, lineLocationId=' || to_char(l_lineLocationId) || 'user entered receive; qty='|| to_char(l_toReceiveQty) ||'user entered uom=' || l_userReceiptUom;
98 asn_debug.put_line(x_progress);
99
100 /* Set OU to the the order OU */
101
102 begin
103 select org_id
104 into x_txn_org_id
105 from po_line_locations_all poll
106 where line_location_id = l_lineLocationId;
107
108 if (x_txn_org_id <> MO_GLOBAL.get_current_org_id) then
109 mo_global.set_policy_context(p_access_mode => 'S',
110 p_org_id => x_txn_org_id);
111 end if;
112 exception
113 WHEN OTHERS THEN
114 po_message_s.sql_error('Error while obtaining the org ID: ', x_progress, sqlcode);
115 end;
116
117 x_progress := 'groupPoTransaction 001, x_user_org_id=' || to_char(x_user_org_id) || 'x_txn_org_id='|| to_char(x_txn_org_id);
118
119 asn_debug.put_line(x_progress);
120
121 open rcv_header(l_lineLocationId);
122 <<outloop>>
123 loop
124 fetch rcv_header into
125 l_rcvLineId, l_shippedRcvUomQty, l_receivedRcvUomQty, l_rcvUom;
126 EXIT WHEN rcv_header%NOTFOUND;
127
128 x_progress := 'groupPoTransaction 002, l_rcvLineId=' || to_char(l_rcvLineId) || 'shippedQty; qty='|| to_char(l_shippedRcvUomQty) || 'receivedQty; qty='|| to_char(l_receivedRcvUomQty) || '; rcvUom=' ||l_rcvUom;
129 asn_debug.put_line(x_progress);
130
131 l_itemId := replaceNull(x_item_id(i));
132 if (l_userReceiptUom <> l_rcvUom) then
133 PO_UOM_S.UOM_CONVERT (l_shippedRcvUomQty,
134 l_rcvUom,
135 l_itemId,
136 l_userReceiptUom,
137 l_shippedQty);
138 PO_UOM_S.UOM_CONVERT (l_receivedRcvUomQty,
139 l_rcvUom,
140 l_itemId,
141 l_userReceiptUom,
142 l_receivedQty);
143 x_progress := 'groupPoTransaction 002.a after uom conversion, shippedQty; qty='|| to_char(l_shippedQty) || 'receivedQty; qty='|| to_char(l_receivedQty);
144 asn_debug.put_line(x_progress);
145
146 else
147 l_shippedQty := l_shippedRcvUomQty;
148 l_receivedQty := l_receivedRcvUomQty;
149 x_progress := 'groupPoTransaction 002.b same uom between rcv and po, shippedQty; qty='|| to_char(l_shippedQty) || 'receivedQty; qty='|| to_char(l_receivedQty);
150 asn_debug.put_line(x_progress);
151 end if;
152
153 hasEntryForRcvLine := false;
154
155 -- a rcv shipment line may have been debitted by another item
156 -- with same llid and different distribution, within the same batch
157
158 if (l_recorder.count > 0 ) then
159 l_index := l_recorder.FIRST;
160 x_progress := 'groupPoTransaction 003 '|| to_char(l_index);
161 asn_debug.put_line(x_progress);
162
163
164 WHILE (l_index IS NOT NULL) LOOP
165 asn_debug.put_line('l_recorder(l_index).rcv_line_id' || l_recorder(l_index).rcv_line_id);
166
167 if(l_recorder(l_index).rcv_line_id = l_rcvLineId) then
168 --l_recorder(l_index).line_location_id = l_lineLocationId and
169 hasEntryForRcvLine := true;
170 x_progress := 'groupPoTransaction 012 l_receiptQty:' || l_receiptQty ||
171 ':l_shippedQty:' || l_shippedQty || ':l_receivedQty:' || l_receivedQty ||
172 'l_recorder(l_index).used_quantity:' || l_recorder(l_index).used_quantity;
173 asn_debug.put_line(x_progress);
174 if(l_recorder(l_index).available and
175 l_shippedQty-l_receivedQty-l_recorder(l_index).used_quantity >= l_toReceiveQty) then
176 l_recorder(l_index).used_quantity := l_recorder(l_index).used_quantity + l_toReceiveQty;
177 --TODO: test txn when existing receive qty and receive qty 2nt time
178 l_receiptQty := l_toReceiveQty;
179 l_toReceiveQty := 0;
180 insert_rcv_txn_interface(x_source_type_code=>'ASN',
181 x_rcv_shipment_line_id=>l_rcvLineId,
182 x_po_header_id=>x_po_header_id(i),
183 x_line_location_id=>x_line_location_id(i) ,
184 x_receipt_qty=>l_receiptQty,
185 x_receipt_uom=>x_receipt_uom(i),
186 x_receipt_date=>x_receipt_date,
187 x_item_id=>replaceNull(x_item_id(i)),
188 x_uom_class=>x_uom_class(i),
189 x_org_id=>replaceNull(x_org_id(i)),
190 x_po_distribution_id=>x_po_distribution_id(i),
191 x_group_id=>x_group_id,
192 x_caller=>x_caller,
193 x_Comments=>x_Comments(i),
194 x_PackingSlip=>x_PackingSlip(i),
195 x_WayBillNum=>x_WayBillNum(i));
196 x_progress := 'groupPoTransaction 015 after insertion, hasEntryForRcvLine = TRUE, asn matches receiving quantity, exiting';
197 asn_debug.put_line(x_progress);
198 exit outloop;
199 else
200 l_recorder(l_index).available := false;
201 l_receiptQty := l_shippedQty-l_receivedQty-l_recorder(l_index).used_quantity;
202 x_progress := 'groupPoTransaction 016 l_receiptQty:' || l_receiptQty ||
203 ':l_shippedQty:' || l_shippedQty || ':l_receivedQty:' || l_receivedQty ||
204 'l_recorder(l_index).used_quantity:' || l_recorder(l_index).used_quantity;
205 asn_debug.put_line(x_progress);
206 if l_receiptQty > 0 then
207 l_toReceiveQty := l_toReceiveQty-l_receiptQty;
208 l_recorder(l_index).used_quantity := l_recorder(l_index).used_quantity + l_receiptQty;
209 insert_rcv_txn_interface(x_source_type_code=>'ASN',
210 x_rcv_shipment_line_id=>l_rcvLineId,
211 x_po_header_id=>x_po_header_id(i),
212 x_line_location_id=>x_line_location_id(i) ,
213 x_receipt_qty=>l_receiptQty,
214 x_receipt_uom=>x_receipt_uom(i),
215 x_receipt_date=>x_receipt_date,
216 x_item_id=>replaceNull(x_item_id(i)),
217 x_uom_class=>x_uom_class(i),
218 x_org_id=>replaceNull(x_org_id(i)),
219 x_po_distribution_id=>x_po_distribution_id(i),
220 x_group_id=>x_group_id,
221 x_caller=>x_caller,
222 x_Comments=>x_Comments(i),
223 x_PackingSlip=>x_PackingSlip(i),
224 x_WayBillNum=>x_WayBillNum(i));
225 --exit;
226 end if;
227 end if;
228 end if;
229 l_index := l_recorder.NEXT(l_index);
230 end loop;
231 end if; -- count>0
232
233
234 if (not hasEntryForRcvLine) then
235 x_progress := 'groupPoTransaction 010 first time receive this rcvLineId, l_shippedQty=' || to_char(l_shippedQty) || '; l_receivedQty='|| to_char(l_receivedQty);
236 asn_debug.put_line(x_progress);
237
238 if (l_shippedQty-l_receivedQty>0) then
239 -- first time to receive this rcv line within this batch
240 if(l_recorder.count=0) then
241 l_index := 1;
242 else
243 l_index := l_recorder.last+1;
244 end if;
245 l_recorder(l_index).rcv_line_id := l_rcvLineId;
246 l_recorder(l_index).used_quantity := 0;
247 if(l_shippedQty-l_receivedQty >= l_toReceiveQty) then
248 l_recorder(l_index).available := true;
249 l_receiptQty := l_toReceiveQty;
250 l_recorder(l_index).used_quantity := l_recorder(l_index).used_quantity + l_receiptQty;
251 l_toReceiveQty := 0;
252 x_progress := 'groupPoTransaction 014 before insertion';
253
254 asn_debug.put_line(x_progress);
255
256 insert_rcv_txn_interface(x_source_type_code=>'ASN',
257 x_rcv_shipment_line_id=>l_rcvLineId,
258 x_po_header_id=>x_po_header_id(i),
259 x_line_location_id=>x_line_location_id(i) ,
260 x_receipt_qty=>l_receiptQty,
261 x_receipt_uom=>x_receipt_uom(i),
262 x_receipt_date=>x_receipt_date,
263 x_item_id=>replaceNull(x_item_id(i)),
264 x_uom_class=>x_uom_class(i),
265 x_org_id=>replaceNull(x_org_id(i)),
266 x_po_distribution_id=>x_po_distribution_id(i),
267 x_group_id=>x_group_id,
268 x_caller=>x_caller,
269 x_Comments=>x_Comments(i),
270 x_PackingSlip=>x_PackingSlip(i),
271 x_WayBillNum=>x_WayBillNum(i));
272 x_progress := 'groupPoTransaction 015 after insertion, hasEntryForRcvLine = FALSE, asn matches receiving quantity, exiting';
273 asn_debug.put_line(x_progress);
274
275 exit;
276 else
277 l_recorder(l_index).available := false;
278 l_receiptQty := l_shippedQty-l_receivedQty-l_recorder(l_index).used_quantity;
279 x_progress := 'groupPoTransaction 017 l_receiptQty:' || l_receiptQty ||
280 ':l_shippedQty:' || l_shippedQty || ':l_receivedQty:' || l_receivedQty ||
281 'l_recorder(l_index).used_quantity:' || l_recorder(l_index).used_quantity;
282 asn_debug.put_line(x_progress);
283 if l_receiptQty > 0 then
284 l_recorder(l_index).used_quantity := l_recorder(l_index).used_quantity + l_receiptQty;
285 l_toReceiveQty := l_toReceiveQty-l_receiptQty;
286 insert_rcv_txn_interface(x_source_type_code=>'ASN',
287 x_rcv_shipment_line_id=>l_rcvLineId,
288 x_po_header_id=>x_po_header_id(i),
289 x_line_location_id=>x_line_location_id(i) ,
290 x_receipt_qty=>l_receiptQty,
291 x_receipt_uom=>x_receipt_uom(i),
292 x_receipt_date=>x_receipt_date,
293 x_item_id=>replaceNull(x_item_id(i)),
294 x_uom_class=>x_uom_class(i),
295 x_org_id=>replaceNull(x_org_id(i)),
296 x_po_distribution_id=>x_po_distribution_id(i),
297 x_group_id=>x_group_id,
298 x_caller=>x_caller,
299 x_Comments=>x_Comments(i),
300 x_PackingSlip=>x_PackingSlip(i),
301 x_WayBillNum=>x_WayBillNum(i));
302 x_progress := 'groupPoTransaction 015 after insertion, asn matches less than receiving quantity';
303 asn_debug.put_line(x_progress);
304 end if;
305 --exit;
306 end if;
307 end if;
308 end if;
309 end loop;
310 close rcv_header;
311
312 if(l_toReceiveQty > 0) then
313 x_progress := 'groupPoTransaction 020 toReceiveQty=' || to_char(l_toReceiveQty);
314
315 asn_debug.put_line(x_progress);
316
317 insert_rcv_txn_interface(x_po_header_id=>x_po_header_id(i),
318 x_line_location_id=>x_line_location_id(i) ,
319 x_receipt_qty=>l_toReceiveQty,
320 x_receipt_uom=>x_receipt_uom(i),
321 x_receipt_date=>x_receipt_date,
322 x_item_id=>replaceNull(x_item_id(i)),
323 x_uom_class=>x_uom_class(i),
324 x_org_id=>replaceNull(x_org_id(i)),
325 x_po_distribution_id=>x_po_distribution_id(i),
326 x_group_id=>x_group_id,
327 x_caller=>x_caller,
328 x_Comments=>x_Comments(i),
329 x_PackingSlip=>x_PackingSlip(i),
330 x_WayBillNum=>x_WayBillNum(i));
331 end if;
332 end loop;
333
334 x_progress := 'groupPoTransaction 030 returning successfully';
335
336 asn_debug.put_line(x_progress);
337 asn_debug.put_line('1 x_txn_org_id:' || x_txn_org_id || 'x_user_org_id:' || x_user_org_id);
338 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
339 asn_debug.put_line('3 x_txn_org_id:' || x_txn_org_id || 'x_user_org_id:' || x_user_org_id);
340 mo_global.set_policy_context(p_access_mode => 'S',
341 p_org_id => x_user_org_id);
342 asn_debug.put_line('5 x_txn_org_id:' || x_txn_org_id || 'x_user_org_id:' || x_user_org_id);
343 end if;
344 asn_debug.put_line('7 x_txn_org_id:' || x_txn_org_id || 'x_user_org_id:' || x_user_org_id);
345 return 0;
346
347 exception
348 when others then
349 -- should we just roll back?
350 x_progress := 'groupPoTransaction 040 sql exception ' || substr(SQLERRM,12,200);
351
352 asn_debug.put_line(x_progress);
353
354 delete from rcv_transactions_interface
355 where group_id = X_group_id;
356
357 PO_REQS_CONTROL_SV.commit_changes;
358
359 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
360 mo_global.set_policy_context(p_access_mode => 'S',
361 p_org_id => x_user_org_id);
362 end if;
363
364 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512),'ICX');
365 return 1;
366 end groupPoTransaction;
367
368
369
370 /*************************************************************
371 ** Private Function :
372 ** groupInternalTransaction
373 ** Description :
374 ** grouping logic to group and split into transaction lines
375 ** return a new table of records of type internalReceivingRecord
376 ** return true if grouping is successful
377 ** called by insertTransactionInterface
378 **************************************************************/
379
380
381 function groupInternalTransaction (x_req_line_id in rcvNumberArray,
382 x_receipt_qty in rcvNumberArray,
383 x_receipt_uom in rcvVarcharArray,
384 x_item_id in rcvNumberArray,
385 x_uom_class in rcvVarcharArray,
386 x_org_id in rcvNumberArray,
387 x_comments in rcvVarcharArray,
388 x_packingSlip in rcvVarcharArray,
389 x_waybillNum in rcvVarcharArray,
390 x_group_id in number,
391 x_receipt_date in date,
392 x_caller in varchar2)
393
394 return number
395
396 is
397
398 cursor rcv_header(p_reqLineId number) is
399 select shipment_line_id, quantity_shipped, nvl(quantity_received,0), unit_of_measure
400 from rcv_shipment_lines
401 where requisition_line_id = p_reqLineId
402 and nvl(quantity_shipped, 0) > nvl(quantity_received, 0);
403
404 l_reqLineId number := 0;
405 l_qty number := 0;
406 l_toReceiveQty number := 0;
407 l_shippedQty number := 0;
408 l_receivedQty number := 0;
409 l_receiptQty number := 0;
410 l_rcvLineId number := 0;
411 x_progress varchar2(240);
412
413 l_shippedRcvUomQty number := 0;
414 l_receivedRcvUomQty number := 0;
415 l_rcvUom varchar2(40);
416 l_userReceiptUom varchar2(40);
417 l_itemId number :=0;
418 x_user_org_id NUMBER;
419 x_txn_org_id NUMBER;
420
421 begin
422
423 if(x_req_line_id is null) then
424 return 1;
425 end if;
426
427 x_user_org_id := MO_GLOBAL.get_current_org_id;
428
429 for i in 1..x_req_line_id.count loop
430
431 l_reqLineId := x_req_line_id(i);
432 l_qty := x_receipt_qty(i);
433 l_toReceiveQty := l_qty;
434 l_userReceiptUom := X_receipt_uom(i);
435
436 x_progress := 'groupInternalTransaction 000, l_reqLineId=' || to_char(l_reqLineId) || 'user entered receive; qty='|| to_char(l_toReceiveQty) ||'user entered uom=' || l_userReceiptUom;
437 asn_debug.put_line(x_progress);
438 /*
439 In case of internal requisition no po is created and internal requistion can be received by the same org id user.
440 Thus it is not required to set org id context here. It has to be the same.
441 */
442 x_txn_org_id := x_user_org_id;
443
444 /* if user tries to over receive
445 then receive at max available
446 */
447
448 open rcv_header(l_reqLineId);
449 loop
450 fetch rcv_header into
451 l_rcvLineId, l_shippedRcvUomQty, l_receivedRcvUomQty, l_rcvUom;
452 EXIT WHEN rcv_header%NOTFOUND;
453
454 x_progress := 'groupInternalTransaction 001, l_rcvLineId=' || to_char(l_rcvLineId)|| 'shippedQty; qty='|| to_char(l_shippedRcvUomQty) || 'receivedQty; qty='|| to_char(l_receivedRcvUomQty) || '; rcvUom=' ||l_rcvUom;
455 asn_debug.put_line(x_progress);
456
457 l_itemId := replaceNull(x_item_id(i));
458 if (l_userReceiptUom <> l_rcvUom) then
459 PO_UOM_S.UOM_CONVERT (l_shippedRcvUomQty,
460 l_rcvUom,
461 l_itemId,
462 l_userReceiptUom,
463 l_shippedQty);
464 PO_UOM_S.UOM_CONVERT (l_receivedRcvUomQty,
465 l_rcvUom,
466 l_itemId,
467 l_userReceiptUom,
468 l_receivedQty);
469 x_progress := 'groupInternalTransaction 001.a after uom conversion, shippedQty; qty='|| to_char(l_shippedQty) || 'receivedQty; qty='|| to_char(l_receivedQty);
470 asn_debug.put_line(x_progress);
471
472 else
473 l_shippedQty := l_shippedRcvUomQty;
474 l_receivedQty := l_receivedRcvUomQty;
475 x_progress := 'groupInternalTransaction 001.b same uom between rcv and po, shippedQty; qty='|| to_char(l_shippedQty) || 'receivedQty; qty='|| to_char(l_receivedQty);
476 asn_debug.put_line(x_progress);
477 end if;
478
479
480 if( (l_shippedQty-l_receivedQty) > l_toReceiveQty) then
481 --TODO: test txn when existing receive qty and receive qty 2
482 l_receiptQty := l_toReceiveQty;
483 x_progress := 'groupInternalTransaction 002 toReceiveQty=' || to_char(l_toReceiveQty);
484
485 asn_debug.put_line(x_progress);
486
487 insert_rcv_txn_interface_ir (l_rcvLineId,
488 x_req_line_id(i),
489 l_receiptQty,
490 x_receipt_uom(i),
491 x_receipt_date,
492 replaceNull(x_item_id(i)),
493 x_uom_class(i),
494 replaceNull(x_org_id(i)),
495 x_group_id,
496 x_caller,
497 x_Comments(i),
498 x_PackingSlip(i),
499 x_WayBillNum(i));
500
501 l_toReceiveQty := 0;
502 x_progress := 'groupInternalTransaction 025 after insertion, asn matches receiving quantity, exiting';
503 asn_debug.put_line(x_progress);
504 exit;
505 else
506 l_receiptQty := l_shippedQty-l_receivedQty;
507 l_toReceiveQty := l_toReceiveQty-l_receiptQty;
508 x_progress := 'groupInternalTransaction 010 toReceiveQty=' || to_char(l_toReceiveQty);
509
510 asn_debug.put_line(x_progress);
511
512 insert_rcv_txn_interface_ir (l_rcvLineId,
513 x_req_line_id(i),
514 l_receiptQty,
515 x_receipt_uom(i),
516 x_receipt_date,
517 replaceNull(x_item_id(i)),
518 x_uom_class(i),
519 replaceNull(x_org_id(i)),
520 x_group_id,
521 x_caller,
522 x_Comments(i),
523 x_PackingSlip(i),
524 x_WayBillNum(i));
525
526
527 end if;
528 end loop;
529 close rcv_header;
530 end loop;
531
532 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
533 mo_global.set_policy_context(p_access_mode => 'S',
534 p_org_id => x_user_org_id);
535 end if;
536
537 return 0;
538
539 exception
540 when others then
541 -- should we just roll back?
542 x_progress := 'groupInternalTransaction 040 sql exception ' || substr(SQLERRM,12,200);
543
544 asn_debug.put_line(x_progress);
545
546 delete from rcv_transactions_interface
547 where group_id = X_group_id;
548
549 PO_REQS_CONTROL_SV.commit_changes;
550
551 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
552 mo_global.set_policy_context(p_access_mode => 'S',
553 p_org_id => x_user_org_id);
554 end if;
555
556 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512),'ICX');
557 return 1;
558
559 end groupInternalTransaction;
560
561
562
563 procedure INSERT_RCV_TXN_INTERFACE_IR (
564 X_rcv_shipment_line_id IN number,
565 x_req_line_id in number,
566 X_receipt_qty IN number,
567 X_receipt_uom IN varchar2,
568 X_receipt_date IN date,
569 X_item_id IN number,
570 X_uom_class IN varchar2,
571 X_org_id IN number,
572 X_group_id IN number,
573 X_caller IN varchar2,
574 X_Comments IN varchar2 default null,
575 X_PackingSlip IN varchar2 default null,
576 X_WayBillNum IN varchar2 default null)
577 as
578
579 X_user_id number :=0;
580 x_count NUMBER := -1;
581 X_web_user_id varchar2(30);
582 X_logon_id number := 0;
583 X_employee_id number := 0;
584 x_trx_proc_mode varchar2(40) := 'ONLINE';
585 X_primary_uom varchar2(25) := x_receipt_uom;
586 X_primary_qty number := x_receipt_qty;
587 l_err_message varchar2(240) := null;
588 x_progress varchar2(240);
589 l_shipped_date DATE;
590
591 begin
592 x_user_id := fnd_global.user_id;
593
594 begin
595 SELECT HR.PERSON_ID
596 INTO x_employee_id
597 FROM FND_USER FND, per_people_f HR
598 WHERE FND.USER_ID = x_user_id
599 AND FND.EMPLOYEE_ID = HR.PERSON_ID
600 AND sysdate between hr.effective_start_date AND hr.effective_end_date
601 AND ROWNUM = 1;
602 EXCEPTION
603 WHEN others THEN
604 x_employee_id := 0;
605 END;
606 IF (x_item_id IS NULL) THEN
607
608 SELECT unit_of_measure
609 INTO X_primary_uom
610 FROM mtl_units_of_measure
611 WHERE uom_class = x_uom_class
612 AND base_uom_flag = 'Y';
613 ELSE
614 SELECT primary_unit_of_measure
615 INTO X_primary_uom
616 FROM mtl_system_items
617 WHERE inventory_item_id = x_item_id
618 AND organization_id = x_org_id;
619
620 END IF;
621
622 if (X_receipt_uom <> X_primary_uom) then
623 PO_UOM_S.UOM_CONVERT (x_receipt_qty,
624 x_receipt_uom,
625 x_item_id,
626 x_primary_uom,
627 X_primary_qty);
628 else
629 X_primary_qty := X_receipt_qty;
630 end if;
631
632 x_progress := 'insert internal transaction, item_id=' || to_char(x_item_id);
633 asn_debug.put_line(x_progress);
634 x_progress := 'insert internal transaction, receipt_uom=' || x_receipt_uom || '; x_receipt_qty='|| to_char(x_receipt_qty);
635 asn_debug.put_line(x_progress);
636 x_progress := 'insert internal transaction, primary_uom= ' || X_primary_uom ||'; primary_qty=' || to_char(X_primary_qty);
637 asn_debug.put_line(x_progress);
638
639 INSERT INTO RCV_TRANSACTIONS_INTERFACE (
640 INTERFACE_TRANSACTION_ID,
641 GROUP_ID,
642 ORG_ID,
643 LAST_UPDATE_DATE,
644 LAST_UPDATED_BY,
645 CREATED_BY,
646 CREATION_DATE,
647 LAST_UPDATE_LOGIN,
648 SOURCE_DOCUMENT_CODE,
649 DESTINATION_TYPE_CODE,
650 DESTINATION_CONTEXT,
651 RECEIPT_SOURCE_CODE,
652 TRANSACTION_DATE,
653 EXPECTED_RECEIPT_DATE,
654 QUANTITY,
655 UNIT_OF_MEASURE,
656 PRIMARY_QUANTITY,
657 PRIMARY_UNIT_OF_MEASURE,
658 SHIPMENT_HEADER_ID,
659 SHIPMENT_LINE_ID,
660 EMPLOYEE_ID,
661 PO_HEADER_ID,
662 PO_RELEASE_ID,
663 PO_LINE_ID,
664 PO_LINE_LOCATION_ID,
665 PO_DISTRIBUTION_ID,
666 PO_UNIT_PRICE,
667 CURRENCY_CODE,
668 CURRENCY_CONVERSION_RATE,
669 CURRENCY_CONVERSION_TYPE,
670 CURRENCY_CONVERSION_DATE,
671 ROUTING_HEADER_ID,
672 VENDOR_ID,
673 VENDOR_SITE_ID,
674 TRANSACTION_TYPE,
675 ITEM_ID,
676 ITEM_DESCRIPTION,
677 ITEM_REVISION,
678 CATEGORY_ID,
679 VENDOR_ITEM_NUM,
680 PACKING_SLIP,
681 LOCATION_ID,
682 SHIP_TO_LOCATION_ID,
683 DELIVER_TO_PERSON_ID,
684 DELIVER_TO_LOCATION_ID,
685 FROM_ORGANIZATION_ID,
686 TO_ORGANIZATION_ID,
687 SUBINVENTORY,
688 WIP_ENTITY_ID,
689 WIP_LINE_ID,
690 WIP_REPETITIVE_SCHEDULE_ID,
691 WIP_OPERATION_SEQ_NUM,
692 WIP_RESOURCE_SEQ_NUM,
693 BOM_RESOURCE_ID,
694 PROCESSING_STATUS_CODE,
695 PROCESSING_MODE_CODE,
696 TRANSACTION_STATUS_CODE,
697 PARENT_TRANSACTION_ID,
698 INSPECTION_STATUS_CODE,
699 USE_MTL_LOT,
700 USE_MTL_SERIAL,
701 LOCATOR_ID,
702 REQUISITION_LINE_ID,
703 COMMENTS,
704 WAYBILL_AIRBILL_NUM,
705 USSGL_TRANSACTION_CODE )
706 SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
707 X_group_id,
708 MO_GLOBAL.get_current_org_id,
709 SYSDATE,
710 X_user_id,
711 X_user_id,
712 SYSDATE,
713 X_user_id,
714 'REQ',
715 RSL.DESTINATION_TYPE_CODE,
716 RSL.DESTINATION_TYPE_CODE,
717 'INTERNAL ORDER',
718 X_receipt_date,
719 RSH.EXPECTED_RECEIPT_DATE,
720 X_receipt_qty,
721 X_receipt_uom,
722 X_primary_qty,
723 X_primary_uom,
724 rsh.shipment_header_id,
725 x_rcv_shipment_line_id,
726 X_employee_id,
727 NULL,
728 NULL,
729 NULL,
730 NULL,
731 NULL,
732 TO_NUMBER(NULL),
733 NULL,
734 TO_NUMBER(NULL),
735 NULL,
736 TO_DATE(NULL),
737 RSL.ROUTING_HEADER_ID,
738 TO_NUMBER(NULL),
739 TO_NUMBER(NULL),
740 decode(x_caller,
741 'WEB','CONFIRM RECEIPT',
742 'WF','CONFIRM RECEIPT(WF)',
743 'WP4','CONFIRM RECEIPT',
744 'WP4_CONFIRM','CONFIRM RECEIPT',
745 'AUTO_RECEIVE','CONFIRM RECEIPT(WF)'),
746 RSL.ITEM_ID,
747 RSL.ITEM_DESCRIPTION,
748 RSL.ITEM_REVISION,
749 RSL.CATEGORY_ID,
750 RSL.VENDOR_ITEM_NUM,
751 X_PackingSlip,
752 RSH.SHIP_TO_LOCATION_ID,
753 RSH.SHIP_TO_LOCATION_ID,
754 rsl.deliver_to_person_id,
755 rsl.DELIVER_TO_LOCATION_ID,
756 RSL.FROM_ORGANIZATION_ID,
757 RSL.TO_ORGANIZATION_ID,
758 RSL.TO_SUBINVENTORY,
759 NULL,
760 NULL,
761 NULL,
762 NULL,
763 NULL,
764 NULL,
765 'CONFIRM RECEIPT',
766 X_trx_proc_mode,
767 'CONFIRM',
768 TO_NUMBER(NULL),
769 NULL,
770 MSI.LOT_CONTROL_CODE,
771 MSI.SERIAL_NUMBER_CONTROL_CODE,
772 to_number(NULL),
773 x_req_line_id,
774 X_Comments,
775 X_WayBillNum,
776 NULL
777 FROM RCV_SHIPMENT_HEADERS RSH,
778 RCV_SHIPMENT_LINES RSL,
779 MTL_SYSTEM_ITEMS MSI
780 WHERE RSH.RECEIPT_SOURCE_CODE <> 'VENDOR' AND
781 RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
782 MSI.ORGANIZATION_ID (+) = RSL.TO_ORGANIZATION_ID AND
783 MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID and
784 RSL.SHIPMENT_LINE_ID = x_rcv_shipment_line_id;
785
786 exception
787 when others THEN
788 x_progress := 'insert internal req transaction exception' || substr(SQLERRM,12,512);
789
790 asn_debug.put_line(x_progress);
791
792
793
794 if (x_caller = 'WP4' OR x_caller = 'WP4_CONFIRM') then
795 l_err_message := substr(SQLERRM,12,512);
796 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512),'ICX');
797 APP_EXCEPTION.RAISE_EXCEPTION;
798 end if;
799
800 end INSERT_RCV_TXN_INTERFACE_IR;
801
802
803
804 procedure INSERT_RCV_TXN_INTERFACE (X_source_type_code IN varchar2 default 'VENDOR',
805 X_rcv_shipment_line_id IN number default 0,
806 X_po_header_id IN number,
807 X_line_location_id IN number,
808 X_receipt_qty IN number,
809 X_receipt_uom IN varchar2,
810 X_receipt_date IN date,
811 X_item_id IN number,
812 X_uom_class IN varchar2,
813 X_org_id IN number,
814 X_po_distribution_id IN number,
815 X_group_id IN number,
816 X_caller IN varchar2,
817 X_Comments IN varchar2 default null,
818 X_PackingSlip IN varchar2 default null,
819 X_WayBillNum IN varchar2 default null
820 ) as
821
822 X_user_id number := 0;
823 x_count NUMBER := -1;
824 X_web_user_id varchar2(30);
825 X_logon_id number := 0;
826 X_employee_id number := 0;
827 x_trx_proc_mode varchar2(40) := 'ONLINE';
828 X_primary_uom varchar2(25);
829 X_primary_qty number;
830 X_receipt_amt number;
831 X_qty number;
832 l_err_message varchar2(240) := null;
833
834 l_destination_type_code VARCHAR2(25) := NULL;
835 l_order_type_code VARCHAR(2) := NULL;
836 l_expected_receipt_date DATE;
837 l_shipped_date DATE;
838 l_rcv_shipment_header_id NUMBER := 0;
839
840 l_po_header_id NUMBER := 0;
841 l_po_release_id NUMBER := 0;
842 l_po_line_id NUMBER := 0;
843 l_po_line_location_id NUMBER := 0;
844 l_po_distribution_id NUMBER := 0;
845 l_unit_price NUMBER := 0;
846 l_currency_code VARCHAR2(15) := NULL;
847 l_currency_conversion_rate NUMBER := NULL;
848 l_currency_conversion_type VARCHAR2(30) := NULL;
849 l_currency_conversion_date DATE;
850 l_routing_id NUMBER := 0;
851 l_vendor_id NUMBER := 0;
852 l_vendor_site_id NUMBER := 0;
853 l_item_id NUMBER := 0;
854 l_item_description VARCHAR2(240) := NULL;
855 l_item_revision VARCHAR2(3) := NULL;
856 l_item_category_id NUMBER := 0;
857 l_vendor_item_number VARCHAR2(25) := NULL;
858 l_ship_to_location_id NUMBER := 0;
859 l_deliver_to_person_id NUMBER := 0;
860 l_deliver_to_location_id NUMBER := 0;
861 l_from_organization_id NUMBER := 0;
862 l_to_organization_id NUMBER := 0;
863 l_destination_subinventory VARCHAR2(10) := NULL;
864 l_lot_control_code NUMBER := 0;
865 l_serial_number_control_code NUMBER := 0;
866 --l_req_line_id NUMBER := 0;
867 l_wip_entity_id NUMBER:= 0;
868 l_wip_line_id NUMBER:= 0;
869 l_wip_repetitive_schedule_id NUMBER:= 0;
870 l_wip_operation_seq_num NUMBER:= 0;
871 l_wip_resource_seq_num NUMBER:= 0;
872 l_bom_resource_id NUMBER:= 0;
873 l_req_distribution_id NUMBER:= NULL;
874 X_MATCHING_BASIS PO_LINES_ALL.MATCHING_BASIS%TYPE;
875 X_JOB_ID PO_LINES_ALL.JOB_ID%TYPE;
876 --Bug 8893932 PO RECEIVED FROM IPROCUREMENT DOESNT POPULATE COUNTRY OF ORIGIN
877 l_country_of_origin PO_LINE_LOCATIONS_ALL.COUNTRY_OF_ORIGIN_CODE%TYPE;
878 -- l_receipt_num varchar2(100);
879 -- l_asn_exist varchar2(8); -- probably to be renamed as sourceTypeFlag
880 -- l_receipt_source_code varchar2(100) := 'VENDOR';
881 -- x_shipment_line_id number :=0; -- to delete
882
883 begin
884 x_user_id := fnd_global.user_id;
885
886 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE x_source_type_code:' || x_source_type_code);
887
888 if (x_source_type_code = 'ASN') then
889 select shipment_header_id
890 into l_rcv_shipment_header_id
891 from rcv_shipment_lines
892 where shipment_line_id = X_rcv_shipment_line_id;
893 end if;
894
895 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE l_rcv_shipment_header_id:' || l_rcv_shipment_header_id);
896
897 begin
898 SELECT HR.PERSON_ID
899 INTO x_employee_id
900 FROM FND_USER FND, per_people_f HR
901 WHERE FND.USER_ID = x_user_id
902 AND FND.EMPLOYEE_ID = HR.PERSON_ID
903 AND sysdate between hr.effective_start_date AND hr.effective_end_date
904 AND ROWNUM = 1;
905 EXCEPTION
906 WHEN others THEN
907 x_employee_id := 0;
908 END;
909
910 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE x_employee_id :' || x_employee_id );
911
912 SELECT POL.MATCHING_BASIS, POL.JOB_ID
913 INTO X_MATCHING_BASIS, X_JOB_ID
914 FROM PO_LINES_ALL POL, PO_DISTRIBUTIONS_ALL POD
915 WHERE POL.PO_LINE_ID = POD.PO_LINE_ID
916 AND POD.PO_DISTRIBUTION_ID = X_PO_DISTRIBUTION_ID;
917
918 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE X_MATCHING_BASIS' || X_MATCHING_BASIS);
919
920 If nvl(x_matching_basis, 'QUANTITY') = 'QUANTITY' then
921
922 IF (x_item_id IS NULL) THEN
923 SELECT unit_of_measure
924 INTO X_primary_uom
925 FROM mtl_units_of_measure
926 WHERE uom_class = x_uom_class
927 AND base_uom_flag = 'Y';
928 ELSE
929 SELECT primary_unit_of_measure
930 INTO X_primary_uom
931 FROM mtl_system_items
932 WHERE inventory_item_id = x_item_id
933 AND organization_id = x_org_id;
934 END IF;
935
936 if (X_receipt_uom <> X_primary_uom) then
937 PO_UOM_S.UOM_CONVERT (x_receipt_qty,
938 x_receipt_uom,
939 x_item_id,
940 x_primary_uom,
941 X_primary_qty);
942 else
943 X_primary_qty:= X_receipt_qty;
944 end if;
945 X_qty:= X_receipt_qty;
946 else
947 X_receipt_amt := X_receipt_qty;
948 X_qty := null;
949 end if;
950
951 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE X_receipt_qty' || X_receipt_qty || ' X_receipt_amt ' || X_receipt_amt);
952
953 /* Insert the rows that were checked into RCV_TRANSACTIONS_INTERFACE */
954
955 SELECT POD.DESTINATION_TYPE_CODE,
956 'PO',
957 NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE),
958 POLL.PO_HEADER_ID,
959 POLL.PO_RELEASE_ID,
960 POLL.PO_LINE_ID,
961 POLL.LINE_LOCATION_ID,
962 POD.PO_DISTRIBUTION_ID ,
963 NVL(POLL.PRICE_OVERRIDE,POL.UNIT_PRICE),
964 POH.CURRENCY_CODE ,
965 POH.RATE,
966 POH.RATE_TYPE,
967 POH.RATE_DATE,
968 POLL.RECEIVING_ROUTING_ID,
969 POH.VENDOR_ID,
970 POH.VENDOR_SITE_ID,
971 POL.ITEM_ID,
972 SUBSTR( POL.ITEM_DESCRIPTION,1,240),
973 POL.ITEM_REVISION,
974 POL.CATEGORY_ID,
975 POL.VENDOR_PRODUCT_NUM,
976 POLL.SHIP_TO_LOCATION_ID,
977 POD.DELIVER_TO_PERSON_ID ,
978 POD.DELIVER_TO_LOCATION_ID ,
979 POH.PO_HEADER_ID,
980 POLL.SHIP_TO_ORGANIZATION_ID,
981 POD.DESTINATION_SUBINVENTORY ,
982 MSI.LOT_CONTROL_CODE,
983 MSI.SERIAL_NUMBER_CONTROL_CODE,
984 pod.wip_entity_id,
985 pod.wip_line_id,
986 pod.wip_repetitive_schedule_id,
987 pod.wip_operation_seq_num,
988 pod.wip_resource_seq_num,
989 pod.bom_resource_id,
990 --Bug 8893932 PO RECEIVED FROM IPROCUREMENT DOESNT POPULATE COUNTRY OF ORIGIN
991 POLL.COUNTRY_OF_ORIGIN_CODE
992 INTO
993 l_destination_type_code,
994 l_order_type_code,
995 l_expected_receipt_date,
996 l_po_header_id,
997 l_po_release_id,
998 l_po_line_id,
999 l_po_line_location_id,
1000 l_po_distribution_id,
1001 l_unit_price,
1002 l_currency_code,
1003 l_currency_conversion_rate,
1004 l_currency_conversion_type,
1005 l_currency_conversion_date,
1006 l_routing_id,
1007 l_vendor_id,
1008 l_vendor_site_id,
1009 l_item_id,
1010 l_item_description,
1011 l_item_revision,
1012 l_item_category_id,
1013 l_vendor_item_number,
1014 l_ship_to_location_id,
1015 l_deliver_to_person_id,
1016 l_deliver_to_location_id,
1017 l_from_organization_id,
1018 l_to_organization_id,
1019 l_destination_subinventory,
1020 l_lot_control_code,
1021 l_serial_number_control_code,
1022 l_wip_entity_id,
1023 l_wip_line_id,
1024 l_wip_repetitive_schedule_id,
1025 l_wip_operation_seq_num,
1026 l_wip_resource_seq_num,
1027 l_bom_resource_id,
1028 --Bug 8893932 PO RECEIVED FROM IPROCUREMENT DOESNT POPULATE COUNTRY OF ORIGIN
1029 l_country_of_origin
1030 FROM MTL_SYSTEM_ITEMS MSI,
1031 PO_LINES_ALL POL,
1032 PO_DISTRIBUTIONS_ALL POD,
1033 PO_HEADERS_ALL POH,
1034 PO_LINE_LOCATIONS_ALL POLL
1035 WHERE
1036 NVL(POLL.APPROVED_FLAG,'N') = 'Y' AND
1037 NVL(POLL.CANCEL_FLAG, 'N') = 'N' AND
1038 NVL(POLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED') AND
1039 POLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND
1040 POLL.RECEIVING_ROUTING_ID = 3 AND
1041 POH.PO_HEADER_ID = POLL.PO_HEADER_ID AND
1042 POL.PO_LINE_ID = POLL.PO_LINE_ID AND
1043 POD.PO_HEADER_ID = POLL.PO_HEADER_ID AND
1044 POD.PO_LINE_ID = POL.PO_LINE_ID AND
1045 POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
1046 NVL(MSI.ORGANIZATION_ID,POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID AND
1047 MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID AND
1048 POH.PO_HEADER_ID = x_po_header_id and
1049 POLL.LINE_LOCATION_ID = x_line_location_id and
1050 POD.PO_DISTRIBUTION_ID = X_po_distribution_id;
1051
1052 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE Inserting values in RCV_TRANSACTIONS_INTERFACE');
1053
1054 INSERT INTO RCV_TRANSACTIONS_INTERFACE (
1055 INTERFACE_TRANSACTION_ID,
1056 GROUP_ID,
1057 ORG_ID,
1058 LAST_UPDATE_DATE,
1059 LAST_UPDATED_BY,
1060 CREATED_BY,
1061 CREATION_DATE,
1062 LAST_UPDATE_LOGIN,
1063 SOURCE_DOCUMENT_CODE,
1064 DESTINATION_TYPE_CODE,
1065 DESTINATION_CONTEXT,
1066 RECEIPT_SOURCE_CODE,
1067 TRANSACTION_DATE,
1068 EXPECTED_RECEIPT_DATE,
1069 QUANTITY,
1070 UNIT_OF_MEASURE,
1071 PRIMARY_QUANTITY,
1072 PRIMARY_UNIT_OF_MEASURE,
1073 AMOUNT,
1074 SHIPMENT_HEADER_ID,
1075 SHIPMENT_LINE_ID,
1076 EMPLOYEE_ID,
1077 PO_HEADER_ID,
1078 PO_RELEASE_ID,
1079 PO_LINE_ID,
1080 PO_LINE_LOCATION_ID,
1081 PO_DISTRIBUTION_ID,
1082 PO_UNIT_PRICE,
1083 CURRENCY_CODE,
1084 CURRENCY_CONVERSION_RATE,
1085 CURRENCY_CONVERSION_TYPE,
1086 CURRENCY_CONVERSION_DATE,
1087 ROUTING_HEADER_ID,
1088 VENDOR_ID,
1089 VENDOR_SITE_ID,
1090 TRANSACTION_TYPE,
1091 ITEM_ID,
1092 ITEM_DESCRIPTION,
1093 ITEM_REVISION,
1094 CATEGORY_ID,
1095 VENDOR_ITEM_NUM,
1096 PACKING_SLIP,
1097 LOCATION_ID,
1098 SHIP_TO_LOCATION_ID,
1099 DELIVER_TO_PERSON_ID,
1100 DELIVER_TO_LOCATION_ID,
1101 FROM_ORGANIZATION_ID,
1102 TO_ORGANIZATION_ID,
1103 SUBINVENTORY,
1104 WIP_ENTITY_ID,
1105 WIP_LINE_ID,
1106 WIP_REPETITIVE_SCHEDULE_ID,
1107 WIP_OPERATION_SEQ_NUM,
1108 WIP_RESOURCE_SEQ_NUM,
1109 BOM_RESOURCE_ID,
1110 PROCESSING_STATUS_CODE,
1111 PROCESSING_MODE_CODE,
1112 TRANSACTION_STATUS_CODE,
1113 PARENT_TRANSACTION_ID,
1114 INSPECTION_STATUS_CODE,
1115 USE_MTL_LOT,
1116 USE_MTL_SERIAL,
1117 LOCATOR_ID,
1118 -- REQUISITION_LINE_ID,
1119 COMMENTS,
1120 WAYBILL_AIRBILL_NUM,
1121 USSGL_TRANSACTION_CODE,
1122 JOB_ID,
1123 MATCHING_BASIS,
1124 COUNTRY_OF_ORIGIN_CODE)
1125 SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
1126 X_group_id,
1127 MO_GLOBAL.get_current_org_id,
1128 SYSDATE,
1129 X_user_id,
1130 X_user_id,
1131 SYSDATE,
1132 X_user_id,
1133 l_order_type_code, -- always PO for both standard and asn
1134 l_DESTINATION_TYPE_CODE,
1135 l_DESTINATION_TYPE_CODE,
1136 'VENDOR', -- same for ASN and PO
1137 X_receipt_date,
1138 l_expected_receipt_date,
1139 X_qty,
1140 X_receipt_uom,
1141 X_primary_qty,
1142 X_primary_uom,
1143 X_receipt_amt,
1144 DECODE(x_source_type_code,'VENDOR', NULL, l_rcv_shipment_header_id),
1145 DECODE(x_source_type_code,'VENDOR', NULL, x_rcv_shipment_line_id),
1146 X_employee_id,
1147 DECODE(l_order_type_code,'PO', l_po_header_id, NULL),
1148 DECODE(l_order_type_code,'PO', l_po_release_id, NULL),
1149 DECODE(l_order_type_code,'PO', l_po_line_id, NULL),
1150 DECODE(l_order_type_code,'PO', l_po_line_location_id, NULL),
1151 DECODE(l_order_type_code,'PO', l_po_distribution_id, NULL),
1152 l_unit_price,
1153 l_currency_code,
1154 l_currency_conversion_rate,
1155 l_currency_conversion_type,
1156 l_currency_conversion_date,
1157 l_routing_id,
1158 l_vendor_id,
1159 l_vendor_site_id,
1160 decode(x_caller,
1161 'WEB','CONFIRM RECEIPT',
1162 'WF','CONFIRM RECEIPT(WF)',
1163 'WP4','CONFIRM RECEIPT',
1164 'WP4_CONFIRM','CONFIRM RECEIPT',
1165 'AUTO_RECEIVE','CONFIRM RECEIPT(WF)'), -- 'EXPRESS DIRECT' this is the transaction_type
1166 l_item_id,
1167 l_item_description,
1168 l_item_revision,
1169 l_item_category_id,
1170 l_vendor_item_number,
1171 X_PackingSlip,
1172 l_ship_to_location_id,
1173 l_ship_to_location_id,
1174 DECODE(l_order_type_code,'PO',l_deliver_to_person_id, l_deliver_to_person_id),
1175 DECODE(l_order_type_code,'PO', l_DELIVER_TO_LOCATION_ID, L_DELIVER_TO_LOCATION_ID),
1176 DECODE(l_order_type_code,'PO', NULL, l_from_organization_id),
1177 l_to_organization_id,
1178 DECODE(l_order_type_code,'PO', l_DESTINATION_SUBINVENTORY, L_DESTINATION_SUBINVENTORY),
1179 DECODE(l_order_type_code,'PO',l_WIP_ENTITY_ID, NULL),
1180 DECODE(l_order_type_code,'PO',l_WIP_LINE_ID, NULL),
1181 DECODE(l_order_type_code,'PO',l_WIP_REPETITIVE_SCHEDULE_ID, NULL),
1182 DECODE(l_order_type_code,'PO',l_WIP_OPERATION_SEQ_NUM, NULL),
1183 DECODE(l_order_type_code,'PO',l_WIP_RESOURCE_SEQ_NUM, NULL),
1184 DECODE(l_order_type_code,'PO',l_BOM_RESOURCE_ID, NULL),
1185 'CONFIRM RECEIPT', -- 'EXPRESS' this is the processing_status_code
1186 X_trx_proc_mode,
1187 'CONFIRM', -- 'EXPRESS' this is the transaction_status_code
1188 TO_NUMBER(NULL),
1189 NULL,
1190 l_lot_control_code,
1191 l_serial_number_control_code,
1192 to_number(NULL),
1193 -- to_number(NULL), -- Bug#2718763 We no longer populate the requisition line id
1194 X_Comments,
1195 X_WayBillNum,
1196 NULL,
1197 X_JOB_ID,
1198 nvl(x_matching_basis, 'QUANTITY'),
1199 l_country_of_origin
1200 FROM dual;
1201
1202
1203 exception
1204 when others THEN
1205 if (x_caller = 'WP4' OR x_caller = 'WP4_CONFIRM') then
1206 l_err_message := substr(SQLERRM,12,512);
1207 asn_debug.put_line(l_err_message);
1208 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512),'ICX');
1209 APP_EXCEPTION.RAISE_EXCEPTION;
1210 end if;
1211
1212
1213 end INSERT_RCV_TXN_INTERFACE;
1214
1215
1216 /*************************************************************
1217 ** Function :
1218 ** processTransactions
1219 ** Description :
1220 ** validates the transactions
1221 ** create or update rcv shipment header
1222 ** call the txn processor
1223 ** API called from Java layer program
1224 **************************************************************/
1225
1226 function process_transactions (X_group_id IN number,
1227 X_caller IN varchar2,
1228 X_Comments IN varchar2 default null,
1229 X_PackingSlip IN varchar2 default null,
1230 X_WayBillNum IN varchar2 default null)
1231 return number is
1232
1233 X_return_code boolean := FALSE;
1234 X_return_code_number number := 0;
1235 X_rows_succeeded number := 0;
1236 X_rows_failed number := 0;
1237 X_logonid number := 0;
1238 l_err_message varchar2(240) := null;
1239 x_column_name po_interface_errors.column_name%type;
1240 x_output_message varchar2(80) := null;
1241 x_message VARCHAR2(2000) := '';
1242 x_user_org_id NUMBER;
1243 x_txn_org_id NUMBER;
1244 begin
1245
1246 x_user_org_id := MO_GLOBAL.get_current_org_id;
1247
1248 begin
1249 select org_id
1250 into x_txn_org_id
1251 from rcv_transactions_interface rti
1252 where rti.group_id = x_group_id and rownum = 1;
1253
1254 asn_debug.put_line('process_transactions x_txn_org_id:' || x_txn_org_id);
1255
1256 if (x_txn_org_id <> MO_GLOBAL.get_current_org_id) then
1257 mo_global.set_policy_context(p_access_mode => 'S',
1258 p_org_id => x_txn_org_id);
1259 end if;
1260 exception
1261 WHEN OTHERS THEN
1262 asn_debug.put_line('Error while obtaining the org ID');
1263 end;
1264
1265 /** Validate the Input fields **/
1266 asn_debug.put_line('process_transactions Calling the val_express_transactions');
1267 rcv_express_sv.val_express_transactions(X_group_id,
1268 X_rows_succeeded,
1269 X_rows_failed);
1270 asn_debug.put_line('process_transactions 001 s=' || to_char(X_rows_succeeded));
1271 asn_debug.put_line('process_transactions 002 f=' || to_char(X_rows_failed));
1272
1273 commit;
1274
1275 if (X_rows_succeeded > 0) then
1276
1277
1278 X_return_code := create_rcv_shipment_headers(X_group_Id, X_caller,X_Comments, X_PackingSlip ,X_WayBillNum);
1279
1280
1281 if (X_return_code) then
1282 /** Bug# 7030461 -- As part of bug 3560995, commit was commented out. It gives
1283 * issues in Receiving Transaction Processor. We are inserting records into
1284 * RTI and RSH and updating the RTI.shipment_header_id with RSH.Shipment_header_id
1285 * and calling the transaction processor. Since the transaction processor runs in different
1286 * transaction, commit is necessary here.
1287 * Reverting the changes done as part of bug 3560995.
1288 **/
1289 commit;
1290 asn_debug.put_line('process_transactions 003 calling processor');
1291
1292 X_return_code_number := call_txn_processor(X_group_id, X_caller);
1293
1294
1295 else
1296
1297 X_return_code_number := 99;
1298 end if;
1299
1300
1301 else
1302 X_return_code_number := 98;
1303 end if;
1304
1305 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
1306 mo_global.set_policy_context(p_access_mode => 'S',
1307 p_org_id => x_user_org_id);
1308 end if;
1309
1310 return X_return_code_number;
1311
1312 exception
1313 when others THEN -- there's a problem with val_express_transactions
1314 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
1315 mo_global.set_policy_context(p_access_mode => 'S',
1316 p_org_id => x_user_org_id);
1317 end if;
1318 if (x_caller = 'WP4' OR x_caller ='WP4_CONFIRM') then
1319 x_message := fnd_message.get;
1320 if (x_message = '') THEN
1321 ERROR_STACK.PUSHMESSAGE(x_message,'ICX');
1322 asn_debug.put_line(x_message);
1323 ELSE
1324 ERROR_STACK.PUSHMESSAGE(substr(SQLERRM,12,512),'ICX');
1325 asn_debug.put_line(substr(SQLERRM,12,512));
1326 END IF;
1327 end if;
1328 return 97;
1329 end process_transactions;
1330
1331 /**************************************************************
1332 ** Private Function :
1333 ** processRcvShipment
1334 ** Description :
1335 ** update rcv shipment header for ASN and Internal Shipment
1336 ** creates a header for those txns that have the same vendor and to_org_id
1337 ** return true if function successful
1338 **************************************************************
1339
1340 function processRcvShipment (x_group_id in number,
1341 x_caller in varchar2,
1342 x_Comments in varchar2 default null,
1343 x_PackingSlip in varchar2 default null,
1344 x_WayBillNum in varchar2 default null,
1345 x_Ussgl_Transaction_Code in varchar2 default null)
1346 return boolean;
1347
1348
1349 */
1350
1351
1352 /****************************************************
1353 ** Function : Create_Rcv_Shipment_Header
1354 ** Description : This procedure creates a header
1355 ** for those txns that have the same vendor
1356 ** and to_org_id.
1357 *****************************************************/
1358
1359 function create_rcv_shipment_headers (X_group_id IN NUMBER,
1360 X_caller IN varchar2,
1361 X_Comments IN varchar2 default null,
1362 X_PackingSlip IN varchar2 default null,
1363 X_WayBillNum IN varchar2 default null)
1364
1365 return boolean is
1366
1367 cursor c0 is
1368 SELECT RTI.TO_ORGANIZATION_ID,
1369 RTI.VENDOR_ID, RTI.WAYBILL_AIRBILL_NUM, POD.ORG_ID
1370 FROM RCV_TRANSACTIONS_INTERFACE RTI, PO_DISTRIBUTIONS_ALL POD
1371 WHERE GROUP_ID = X_GROUP_ID AND
1372 SHIPMENT_LINE_ID IS NULL AND
1373 RTI.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
1374 GROUP BY RTI.TO_ORGANIZATION_ID, RTI.VENDOR_ID, RTI.WAYBILL_AIRBILL_NUM, POD.ORG_ID;
1375
1376 /*
1377 cursor c0 is
1378 select distinct to_organization_id,
1379 vendor_id,waybill_airbill_num
1380 from rcv_transactions_interface
1381 where group_id = X_group_id and shipment_line_id is null;
1382 -- for update of shipment_header_id;
1383 */
1384
1385 cursor c1 is
1386 select to_organization_id, shipment_header_id, comments, packing_slip,waybill_airbill_num
1387 from rcv_transactions_interface
1388 where group_id = X_group_id and shipment_line_id is not null and
1389 shipment_header_id is not null;
1390
1391 cursor c2 is
1392 select distinct shipment_header_id
1393 from rcv_transactions_interface trans
1394 where group_id = X_group_id and shipment_line_id is not null and
1395 shipment_header_id is not null;
1396
1397
1398 X_sysdate date := SYSDATE;
1399 X_userid number := 0;
1400 X_vendor_id rcv_transactions_interface.vendor_id%type;
1401 X_org_id PO_DISTRIBUTIONS_ALL.ORG_ID%type;
1402 X_to_org_id rcv_transactions_interface.to_organization_id%type;
1403 X_receipt_num rcv_shipment_headers.receipt_num%type;
1404 X_created_by rcv_shipment_headers.created_by%type;
1405 X_last_update_login rcv_shipment_headers.last_update_login%type;
1406 X_count number := 0;
1407 X_shipment_header_id rcv_shipment_headers.shipment_header_id%type;
1408 X_employee_id rcv_shipment_headers.employee_id%type := 0;
1409 X_request_id rcv_shipment_headers.request_id%type := 0;
1410
1411 X_pgm_app_id rcv_shipment_headers.program_application_id%type := 0;
1412 X_pgm_id rcv_shipment_headers.program_id%type := 0;
1413 l_err_message varchar2(240) := null;
1414 x_rcpt_count NUMBER := 1;
1415 x_organization_name VARCHAR2(60);
1416 x_supplier_name PO_VENDORS.VENDOR_NAME%TYPE;
1417 x_line_waybill_airbill_num VARCHAR2(20);
1418 x_wayairnum VARCHAR2(20);
1419 x_req_number VARCHAR2(25);
1420 x_new_comments VARCHAR2(240);
1421 x_new_packingSlip VARCHAR2(25);
1422 x_new_waybillNum VARCHAR2(20);
1423 x_asn_type VARCHAR2(25);
1424 X_vendor_site_id rcv_transactions_interface.vendor_site_id%type;
1425
1426 begin
1427
1428 X_created_by := fnd_global.user_id;
1429 x_last_update_login := fnd_global.user_id;
1430
1431 BEGIN
1432 SELECT HR.PERSON_ID
1433 INTO x_employee_id
1434 FROM FND_USER FND, per_people_f HR
1435 WHERE FND.USER_ID = X_created_by
1436 AND FND.EMPLOYEE_ID = HR.PERSON_ID
1437 AND sysdate between hr.effective_start_date AND hr.effective_end_date
1438 AND ROWNUM = 1;
1439 EXCEPTION
1440 WHEN others THEN
1441 x_employee_id := 0;
1442 END;
1443
1444
1445 open c0;
1446
1447 loop
1448 fetch c0 into X_to_org_id, x_vendor_id,x_line_waybill_airbill_num, X_org_id;
1449 exit when c0%notfound;
1450
1451 /* Get the Receipt Number */
1452
1453
1454 SELECT to_char(next_receipt_num + 1)
1455 INTO X_receipt_num
1456 FROM rcv_parameters
1457 WHERE organization_id = X_to_org_id
1458 FOR UPDATE OF next_receipt_num;
1459
1460 LOOP
1461
1462 SELECT count(*)
1463 INTO X_count
1464 FROM rcv_shipment_headers
1465 WHERE receipt_num = X_receipt_num and
1466 ship_to_org_id = X_to_org_id;
1467
1468 IF (X_count = 0) THEN
1469 update rcv_parameters
1470 set next_receipt_num = X_receipt_num
1471 where organization_id = X_to_org_id;
1472
1473 EXIT;
1474 ELSE
1475 X_receipt_num := to_char(to_number(X_receipt_num) + 1);
1476 END IF;
1477
1478 END LOOP;
1479
1480 /* Get the shipment Header id */
1481
1482 SELECT rcv_shipment_headers_s.nextval
1483 INTO X_shipment_header_id
1484 FROM sys.dual;
1485
1486
1487 /* For every unique Org_id, Vendor_id combination,
1488 ** create a header */
1489
1490 INSERT INTO RCV_SHIPMENT_HEADERS (
1491 SHIPMENT_HEADER_ID,
1492 LAST_UPDATE_DATE,
1493 LAST_UPDATED_BY,
1494 CREATION_DATE,
1495 CREATED_BY,
1496 LAST_UPDATE_LOGIN,
1497 RECEIPT_SOURCE_CODE,
1498 VENDOR_ID,
1499 ORGANIZATION_ID,
1500 SHIP_TO_ORG_ID,
1501 RECEIPT_NUM,
1502 EMPLOYEE_ID,
1503 REQUEST_ID,
1504 PROGRAM_APPLICATION_ID,
1505 PROGRAM_ID,
1506 PROGRAM_UPDATE_DATE,
1507 COMMENTS,
1508 PACKING_SLIP,
1509 WAYBILL_AIRBILL_NUM,
1510 USSGL_TRANSACTION_CODE )
1511 VALUES (
1512 X_shipment_header_id,
1513 SYSDATE,
1514 X_created_by,
1515 SYSDATE,
1516 X_created_by,
1517 X_last_update_login,
1518 'VENDOR',
1519 X_vendor_id,
1520 X_to_org_id,
1521 X_to_org_id,
1522 X_receipt_num,
1523 X_employee_id,
1524 X_request_id,
1525 X_pgm_app_id,
1526 X_pgm_id,
1527 SYSDATE,
1528 X_Comments ,
1529 X_PackingSlip,
1530 x_line_waybill_airbill_num,
1531 NULL);
1532
1533 IF x_line_waybill_airbill_num IS NULL THEN
1534 update rcv_transactions_interface
1535 set shipment_header_id = x_shipment_header_id
1536 where group_id = X_group_id
1537 and to_organization_id = x_to_org_id
1538 and vendor_id = x_vendor_id
1539 and shipment_line_id is null
1540 AND waybill_airbill_num IS NULL;
1541 x_req_number := NULL;
1542 ELSE
1543 update rcv_transactions_interface
1544 set shipment_header_id = x_shipment_header_id
1545 where group_id = X_group_id
1546 and to_organization_id = x_to_org_id
1547 and vendor_id = x_vendor_id
1548 and shipment_line_id is null
1549 AND waybill_airbill_num = x_line_waybill_airbill_num;
1550 x_req_number := NULL;
1551 END IF;
1552
1553 BEGIN
1554 SELECT distinct vendor_site_id
1555 INTO X_vendor_site_id
1556 FROM rcv_transactions_interface
1557 WHERE group_id = X_group_id and
1558 shipment_header_id = x_shipment_header_id;
1559
1560 asn_debug.put_line('vendor_site='||to_char(X_vendor_site_id));
1561 EXCEPTION
1562 WHEN others THEN
1563 X_vendor_site_id := null;
1564 END;
1565
1566 if(X_vendor_site_id is not null) then
1567 update rcv_shipment_headers
1568 set vendor_site_id = X_vendor_site_id
1569 where shipment_header_id = x_shipment_header_id;
1570 end if;
1571
1572 x_rcpt_count := x_rcpt_count +1;
1573 end loop;
1574
1575 close c0;
1576
1577 /* update intransit shipment header according to user entered info */
1578
1579 asn_debug.put_line('number of PO receipt created is ' || to_char(x_rcpt_count-1));
1580
1581 open c1;
1582
1583 loop
1584 fetch c1 into X_to_org_id, X_shipment_header_id, X_new_comments, X_new_packingSlip, X_new_waybillNum;
1585 exit when c1%notfound;
1586
1587 begin
1588 select receipt_num
1589 into X_receipt_num
1590 from rcv_shipment_headers
1591 where shipment_header_id = X_shipment_header_id and
1592 receipt_num is not null;
1593 exception
1594 when no_data_found then
1595 /* Get the Receipt Number */
1596 SELECT to_char(next_receipt_num + 1)
1597 INTO X_receipt_num
1598 FROM rcv_parameters
1599 WHERE organization_id = X_to_org_id
1600 FOR UPDATE OF next_receipt_num;
1601
1602 LOOP
1603
1604 SELECT count(*)
1605 INTO X_count
1606 FROM rcv_shipment_headers
1607 WHERE receipt_num = X_receipt_num and
1608 ship_to_org_id = X_to_org_id;
1609
1610 IF (X_count = 0) THEN
1611 update rcv_parameters
1612 set next_receipt_num = X_receipt_num
1613 where organization_id = X_to_org_id;
1614
1615 EXIT;
1616 ELSE
1617 X_receipt_num := to_char(to_number(X_receipt_num) + 1);
1618 END IF;
1619
1620 END LOOP;
1621
1622 update rcv_shipment_headers
1623 set receipt_num=X_receipt_num
1624 where shipment_header_id = X_shipment_header_id;
1625
1626 end; -- no receipt number
1627
1628 end loop;
1629
1630 close c1;
1631
1632 return TRUE;
1633
1634 exception
1635 when others then
1636
1637 if (x_caller = 'WP4' OR x_caller ='WP4_CONFIRM') THEN
1638 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512),'ICX');
1639 APP_EXCEPTION.RAISE_EXCEPTION;
1640 end if;
1641 return FALSE;
1642
1643 end create_rcv_shipment_headers;
1644
1645
1646 /*************************************************************
1647 ** Function : Call_Txn_Processor
1648 ** Description : This function calls the transaction processor
1649 ** in ONLINE mode.
1650 **************************************************************/
1651
1652 function call_txn_processor(X_group_id IN Number,
1653 X_caller IN varchar2)
1654 return number is
1655
1656 x_trx_proc_mode varchar2(40);
1657 rc number;
1658 rc1 number;
1659 delete_rows boolean := FALSE;
1660
1661 timeout number := 300;
1662 outcome varchar2(200) := NULL;
1663 message varchar2(200) := NULL;
1664 l_err_message varchar2(240) := null;
1665
1666 X_user_id number;
1667 X_resp_id number;
1668 x_appl_id NUMBER;
1669
1670 X_str varchar2(2000) := NULL;
1671 X_output_message varchar2(2000) := NULL;
1672 x_progress VARCHAR2(1000) := '';
1673 r_val1 varchar2(200) := NULL;
1674 r_val2 varchar2(200) := NULL;
1675 r_val3 varchar2(200) := NULL;
1676 r_val4 varchar2(200) := NULL;
1677 r_val5 varchar2(200) := NULL;
1678 r_val6 varchar2(200) := NULL;
1679 r_val7 varchar2(200) := NULL;
1680 r_val8 varchar2(200) := NULL;
1681 r_val9 varchar2(200) := NULL;
1682 r_val10 varchar2(200) := NULL;
1683 r_val11 varchar2(200) := NULL;
1684 r_val12 varchar2(200) := NULL;
1685 r_val13 varchar2(200) := NULL;
1686 r_val14 varchar2(200) := NULL;
1687 r_val15 varchar2(200) := NULL;
1688 r_val16 varchar2(200) := NULL;
1689 r_val17 varchar2(200) := NULL;
1690 r_val18 varchar2(200) := NULL;
1691 r_val19 varchar2(200) := NULL;
1692 r_val20 varchar2(200) := NULL;
1693 x_user_org_id NUMBER;
1694 x_txn_org_id NUMBER;
1695 begin
1696
1697 x_progress := '001 calling txn_processor for group=' || to_char(X_group_id);
1698 asn_debug.put_line(x_progress);
1699
1700 x_user_id := fnd_global.user_id;
1701 x_resp_id := fnd_global.resp_id;
1702 x_appl_id := fnd_global.resp_appl_id;
1703
1704 fnd_global.APPS_INITIALIZE (X_user_id, X_resp_id, x_appl_id);
1705
1706 -- Code for setting the org context same as the org id from PO
1707 asn_debug.put_line('call_txn_processor x_group_id:' || x_group_id);
1708
1709 x_user_org_id := MO_GLOBAL.get_current_org_id;
1710
1711 begin
1712 select org_id
1713 into x_txn_org_id
1714 from rcv_transactions_interface rti
1715 where rti.group_id = x_group_id and rownum = 1;
1716
1717 if (x_txn_org_id <> MO_GLOBAL.get_current_org_id) then
1718 mo_global.set_policy_context(p_access_mode => 'S',
1719 p_org_id => x_txn_org_id);
1720 end if;
1721 exception
1722 WHEN OTHERS THEN
1723 asn_debug.put_line('Error while obtaining the org ID');
1724 end;
1725
1726 x_trx_proc_mode := 'ONLINE';
1727
1728 if (X_trx_proc_mode = 'ONLINE') THEN
1729 x_progress := '002';
1730 asn_debug.put_line(x_progress);
1731
1732 rc := fnd_transaction.synchronous (
1733 timeout, outcome, message, 'PO', 'RCVTPO',
1734 X_trx_proc_mode, X_group_id,
1735 x_txn_org_id, NULL, NULL, NULL, NULL, NULL,
1736 NULL, NULL, NULL, NULL, NULL, NULL,
1737 NULL, NULL, NULL, NULL, NULL, NULL);
1738
1739 x_progress := '003online call return=' || to_char(rc) || '; outcome='|| outcome;
1740 asn_debug.put_line(x_progress);
1741
1742 if rc = 1 then
1743 if (por_rcv_ord_SV.check_group_id(X_group_id)) then
1744 fnd_message.set_name('FND', 'TM-TIMEOUT');
1745 x_str := fnd_message.get;
1746 fnd_message.clear;
1747
1748 FND_MESSAGE.set_name('FND','CONC-Error running standalone');
1749 fnd_message.set_token('PROGRAM', 'Receiving Transaction Manager - RCVOLTM');
1750 fnd_message.set_token('REQUEST', X_group_id);
1751 fnd_message.set_token('REASON', x_str);
1752
1753 htp.nl;
1754 x_output_message := fnd_message.get;
1755
1756 if (X_caller <> 'WP4'OR x_caller <> 'WP4_CONFIRM') then
1757 htp.teletype(x_output_message);
1758 htp.nl;
1759 ELSE
1760 asn_debug.put_line('return 1, msg=' || x_output_message);
1761 ERROR_STACK.PUSHMESSAGE( x_output_message, 'ICX');
1762 end if;
1763 end if;
1764 delete_rows := TRUE;
1765 elsif rc = 2 then
1766 IF (por_rcv_ord_SV.check_group_id(X_group_id)) THEN
1767
1768
1769 fnd_message.set_name('FND', 'TM-SVC LOCK HANDLE FAILED');
1770 x_str := fnd_message.get;
1771
1772 FND_MESSAGE.set_name('FND','CONC-Error running standalone');
1773
1774 fnd_message.set_token('PROGRAM', 'Receiving Transaction Manager - RCVOLTM');
1775 fnd_message.set_token('REQUEST', X_group_id);
1776 fnd_message.set_token('REASON', x_str);
1777
1778
1779 x_output_message := fnd_message.get;
1780 ERROR_STACK.PUSHMESSAGE( x_output_message, 'ICX');
1781 asn_debug.put_line('return 2, msg=' || x_output_message);
1782 end if;
1783 delete_rows := TRUE;
1784 elsif (rc = 3 or (outcome IN ('WARNING', 'ERROR'))) then
1785 asn_debug.put_line('return 3 from txn processor, or outcome='|| outcome);
1786 IF (por_rcv_ord_SV.check_group_id(X_group_id)) THEN
1787
1788
1789 rc1 := fnd_transaction.get_values (r_val1, r_val2, r_val3, r_val4, r_val5,
1790 r_val6, r_val7, r_val8, r_val9, r_val10,
1791 r_val11, r_val12, r_val13, r_val14, r_val15,
1792 r_val16, r_val17, r_val18, r_val19, r_val20
1793 );
1794 x_output_message := r_val1;
1795
1796 IF (r_val2 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val2; END IF;
1797 IF (r_val3 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val3; END IF;
1798 IF (r_val4 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val4; END IF;
1799 IF (r_val5 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val5; END IF;
1800 IF (r_val6 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val6; END IF;
1801 IF (r_val7 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val7; END IF;
1802 IF (r_val8 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val8; END IF;
1803 IF (r_val9 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val9; END IF;
1804 IF (r_val10 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val10; END IF;
1805 IF (r_val11 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val11; END IF;
1806 IF (r_val12 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val12; END IF;
1807 IF (r_val13 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val13; END IF;
1808 IF (r_val14 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val14; END IF;
1809 IF (r_val15 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val15; END IF;
1810 IF (r_val16 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val16; END IF;
1811 IF (r_val17 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val17; END IF;
1812 IF (r_val18 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val18; END IF;
1813 IF (r_val19 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val19; END IF;
1814 IF (r_val20 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val20; END IF;
1815
1816 ERROR_STACK.PUSHMESSAGE( x_output_message, 'ICX');
1817 asn_debug.put_line('return 3 or error, msg=' || x_output_message);
1818 /* for this error case, we change the rc
1819 so that the error case will properly passed to middle tier.
1820 set to 4 to distinguish from 3
1821 */
1822 if (rc1 = 0 and outcome IN ('WARNING', 'ERROR')) then
1823 rc := 4;
1824 elsif rc=3 then
1825 fnd_message.clear;
1826 fnd_message.set_name('ICX','ICX_POR_RCV_TXN_MGR_DOWN_ERROR');
1827 x_output_message := fnd_message.get;
1828 ERROR_STACK.PUSHMESSAGE(x_output_message,'ICX');
1829 end if;
1830
1831 END IF;
1832
1833 asn_debug.put_line('After calling transaction processor, rc='|| to_char(rc));
1834 delete_rows := TRUE;
1835
1836 elsif (rc = 0 and (outcome NOT IN ('WARNING', 'ERROR'))) then
1837
1838 if (x_caller = 'WP4') then
1839 /** Since we have received over the web, we need to clean up any open
1840 notifications for the rows that belong to this group_id **/
1841
1842 por_rcv_ord_SV.cancel_pending_notifs(x_group_id);
1843
1844 end if;
1845 x_progress := '004, return 0 from txn processor';
1846 asn_debug.put_line(x_progress);
1847 delete_rows := FALSE;
1848 commit;
1849 end if;
1850
1851
1852 elsif (X_trx_proc_mode = 'IMMEDIATE') then
1853
1854
1855 rc := fnd_request.submit_request('PO',
1856 'RVCTP',
1857 null,
1858 null,
1859 false,
1860 'IMMEDIATE',
1861 X_group_id,
1862 chr(0),
1863 NULL, NULL, NULL, NULL, NULL, NULL,
1864 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1865 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1866 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1867 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1868 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1869 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1870 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1871 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1872 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1873 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1874 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1875 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1876 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1877
1878 if (rc <= 0 or rc is NULL) then
1879 -- htp.p('The rc is: ' || to_char(rc)); htp.nl;
1880 delete_rows := TRUE;
1881 null; -- for now
1882 else
1883 commit;
1884 rc := null;
1885 end if;
1886
1887 end if;
1888
1889 /*
1890 ** Since the insert has already occurred, make sure to set the
1891 ** transaction status to error; otherwise the next query
1892 ** you do will make it look like the transactions were
1893 ** actually awaiting the transaction processor since the
1894 ** status will be 'PENDING'
1895 ** DEBUG: We should log a message in the rcv interface errors
1896 ** so if the user reviews these records, they'll know why
1897 ** they were not processed.
1898 */
1899
1900 if (delete_rows) then
1901
1902 BEGIN
1903
1904 delete from rcv_transactions_interface
1905 where group_id = X_group_id;
1906
1907
1908 PO_REQS_CONTROL_SV.commit_changes;
1909
1910 EXCEPTION
1911 WHEN OTHERS THEN
1912 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
1913 mo_global.set_policy_context(p_access_mode => 'S',
1914 p_org_id => x_user_org_id);
1915 end if;
1916 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512), 'ICX');
1917 return 95;
1918 END;
1919
1920 END if;
1921 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
1922 mo_global.set_policy_context(p_access_mode => 'S',
1923 p_org_id => x_user_org_id);
1924 end if;
1925
1926 return rc;
1927
1928 EXCEPTION
1929 when others then
1930 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
1931 mo_global.set_policy_context(p_access_mode => 'S',
1932 p_org_id => x_user_org_id);
1933 end if;
1934 x_progress := 'call txn processor exception' || substr(SQLERRM,12,512);
1935 asn_debug.put_line(x_progress);
1936 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512), 'ICX');
1937 return 94;
1938
1939 end call_txn_processor;
1940
1941 /*=============================================================
1942
1943 FUNCTION NAME: check_group_id
1944
1945 =============================================================*/
1946 FUNCTION check_group_id (x_group_id IN NUMBER) RETURN BOOLEAN IS
1947
1948 x_rec_count NUMBER := 0;
1949
1950 BEGIN
1951
1952 SELECT COUNT(1)
1953 INTO x_rec_count
1954 FROM RCV_TRANSACTIONS_INTERFACE
1955 WHERE group_id = x_group_id;
1956
1957 IF (x_rec_count = 0) THEN
1958
1959 return (FALSE);
1960
1961 ELSE
1962
1963 return (TRUE);
1964
1965 END IF;
1966
1967 EXCEPTION
1968
1969 WHEN NO_DATA_FOUND THEN RETURN(FALSE);
1970 WHEN OTHERS THEN raise;
1971
1972 END check_group_id;
1973
1974 procedure cancel_pending_notifs (x_group_id IN NUMBER) is
1975
1976 cursor C1 is
1977 select distinct nvl(pod.wf_item_key,wf.ITEM_KEY)
1978 from rcv_transactions rcv,po_line_locations_all poll,
1979 po_distributions_all pod,wf_items wf
1980 where group_id = x_group_id and
1981 poll.line_location_id = rcv.po_line_location_id AND
1982 pod.po_distribution_id = rcv.po_distribution_id
1983 AND wf.item_type = 'PORCPT' AND
1984 ( wf.ITEM_KEY LIKE (rcv.po_header_id ||';'||
1985 rcv.deliver_to_person_id || ';' ||'%')
1986 );
1987
1988 wf_item_key varchar2(2000);
1989 wf_item_type varchar2(6) := 'PORCPT';
1990
1991 begin
1992
1993 open c1;
1994 loop
1995 fetch c1 into wf_item_key ;
1996 exit when c1%notfound;
1997
1998
1999 if (por_rcv_ord_SV.notif_is_active(wf_item_type,wf_item_key)) then
2000 WF_Engine.AbortProcess(wf_item_type,wf_item_key);
2001 end if;
2002
2003 end loop;
2004
2005 end cancel_pending_notifs;
2006
2007 FUNCTION notif_is_active (wf_item_type in varchar2,
2008 wf_item_key in varchar2) RETURN BOOLEAN is
2009
2010 x_act_status varchar2(8);
2011 x_progress varchar2(100) := '001';
2012 /** this procedure is currently only called when the transaction is done
2013 via the menu on the web. Hence it is safe now to default it to WP4 **/
2014 x_caller varchar2(3) := 'WP4';
2015
2016
2017 BEGIN
2018 x_progress := 'POR_RCV_ORD_SV.is_active-001';
2019
2020 --Bug 4999072 Changed the query to reduce the memory usage
2021 SELECT WIAS.ACTIVITY_STATUS
2022 INTO x_act_status
2023 FROM WF_ITEM_ACTIVITY_STATUSES WIAS,
2024 WF_ITEMS WI,
2025 WF_PROCESS_ACTIVITIES PA
2026 WHERE WIAS.ITEM_TYPE = wf_item_type
2027 AND WIAS.ITEM_KEY = wf_item_key
2028 AND WIAS.ITEM_TYPE = WI.ITEM_TYPE
2029 AND WIAS.ITEM_KEY = WI.ITEM_KEY
2030 AND WI.ROOT_ACTIVITY=PA.ACTIVITY_NAME
2031 AND WIAS.PROCESS_ACTIVITY= PA.INSTANCE_ID;
2032
2033
2034
2035 if x_act_status not in ('COMPLETE', 'ERROR') then
2036 return TRUE;
2037 else return FALSE;
2038 end if;
2039 exception
2040 when no_data_found then
2041 return false;
2042 when others then
2043 if (x_caller = 'WP4'OR x_caller ='WP4_CONFIRM') then
2044 error_stack.pushmessage( substr(SQLERRM,12,512),'ICX');
2045 app_exception.raise_exception;
2046 else
2047 return FALSE;
2048 end if;
2049 end notif_is_active;
2050
2051 end;