[Home] [Help]
PACKAGE BODY: APPS.POR_RCV_ORD_SV
Source
1 package body POR_RCV_ORD_SV as
2 /* $Header: PORRCVOB.pls 120.5.12010000.2 2008/08/02 14:39:41 kkram 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
877 -- l_receipt_num varchar2(100);
878 -- l_asn_exist varchar2(8); -- probably to be renamed as sourceTypeFlag
879 -- l_receipt_source_code varchar2(100) := 'VENDOR';
880 -- x_shipment_line_id number :=0; -- to delete
881
882 begin
883 x_user_id := fnd_global.user_id;
884
885 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE x_source_type_code:' || x_source_type_code);
886
887 if (x_source_type_code = 'ASN') then
888 select shipment_header_id
889 into l_rcv_shipment_header_id
890 from rcv_shipment_lines
891 where shipment_line_id = X_rcv_shipment_line_id;
892 end if;
893
894 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE l_rcv_shipment_header_id:' || l_rcv_shipment_header_id);
895
896 begin
897 SELECT HR.PERSON_ID
898 INTO x_employee_id
899 FROM FND_USER FND, per_people_f HR
900 WHERE FND.USER_ID = x_user_id
901 AND FND.EMPLOYEE_ID = HR.PERSON_ID
902 AND sysdate between hr.effective_start_date AND hr.effective_end_date
903 AND ROWNUM = 1;
904 EXCEPTION
905 WHEN others THEN
906 x_employee_id := 0;
907 END;
908
909 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE x_employee_id :' || x_employee_id );
910
911 SELECT POL.MATCHING_BASIS, POL.JOB_ID
912 INTO X_MATCHING_BASIS, X_JOB_ID
913 FROM PO_LINES_ALL POL, PO_DISTRIBUTIONS_ALL POD
914 WHERE POL.PO_LINE_ID = POD.PO_LINE_ID
915 AND POD.PO_DISTRIBUTION_ID = X_PO_DISTRIBUTION_ID;
916
917 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE X_MATCHING_BASIS' || X_MATCHING_BASIS);
918
919 If nvl(x_matching_basis, 'QUANTITY') = 'QUANTITY' then
920
921 IF (x_item_id IS NULL) THEN
922 SELECT unit_of_measure
923 INTO X_primary_uom
924 FROM mtl_units_of_measure
925 WHERE uom_class = x_uom_class
926 AND base_uom_flag = 'Y';
927 ELSE
928 SELECT primary_unit_of_measure
929 INTO X_primary_uom
930 FROM mtl_system_items
931 WHERE inventory_item_id = x_item_id
932 AND organization_id = x_org_id;
933 END IF;
934
935 if (X_receipt_uom <> X_primary_uom) then
936 PO_UOM_S.UOM_CONVERT (x_receipt_qty,
937 x_receipt_uom,
938 x_item_id,
939 x_primary_uom,
940 X_primary_qty);
941 else
942 X_primary_qty:= X_receipt_qty;
943 end if;
944 X_qty:= X_receipt_qty;
945 else
946 X_receipt_amt := X_receipt_qty;
947 X_qty := null;
948 end if;
949
950 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE X_receipt_qty' || X_receipt_qty || ' X_receipt_amt ' || X_receipt_amt);
951
952 /* Insert the rows that were checked into RCV_TRANSACTIONS_INTERFACE */
953
954 SELECT POD.DESTINATION_TYPE_CODE,
955 'PO',
956 NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE),
957 POLL.PO_HEADER_ID,
958 POLL.PO_RELEASE_ID,
959 POLL.PO_LINE_ID,
960 POLL.LINE_LOCATION_ID,
961 POD.PO_DISTRIBUTION_ID ,
962 NVL(POLL.PRICE_OVERRIDE,POL.UNIT_PRICE),
963 POH.CURRENCY_CODE ,
964 POH.RATE,
965 POH.RATE_TYPE,
966 POH.RATE_DATE,
967 POLL.RECEIVING_ROUTING_ID,
968 POH.VENDOR_ID,
969 POH.VENDOR_SITE_ID,
970 POL.ITEM_ID,
971 SUBSTR( POL.ITEM_DESCRIPTION,1,240),
972 POL.ITEM_REVISION,
973 POL.CATEGORY_ID,
974 POL.VENDOR_PRODUCT_NUM,
975 POLL.SHIP_TO_LOCATION_ID,
976 POD.DELIVER_TO_PERSON_ID ,
977 POD.DELIVER_TO_LOCATION_ID ,
978 POH.PO_HEADER_ID,
979 POLL.SHIP_TO_ORGANIZATION_ID,
980 POD.DESTINATION_SUBINVENTORY ,
981 MSI.LOT_CONTROL_CODE,
982 MSI.SERIAL_NUMBER_CONTROL_CODE,
983 pod.wip_entity_id,
984 pod.wip_line_id,
985 pod.wip_repetitive_schedule_id,
986 pod.wip_operation_seq_num,
987 pod.wip_resource_seq_num,
988 pod.bom_resource_id
989 INTO
990 l_destination_type_code,
991 l_order_type_code,
992 l_expected_receipt_date,
993 l_po_header_id,
994 l_po_release_id,
995 l_po_line_id,
996 l_po_line_location_id,
997 l_po_distribution_id,
998 l_unit_price,
999 l_currency_code,
1000 l_currency_conversion_rate,
1001 l_currency_conversion_type,
1002 l_currency_conversion_date,
1003 l_routing_id,
1004 l_vendor_id,
1005 l_vendor_site_id,
1006 l_item_id,
1007 l_item_description,
1008 l_item_revision,
1009 l_item_category_id,
1010 l_vendor_item_number,
1011 l_ship_to_location_id,
1012 l_deliver_to_person_id,
1013 l_deliver_to_location_id,
1014 l_from_organization_id,
1015 l_to_organization_id,
1016 l_destination_subinventory,
1017 l_lot_control_code,
1018 l_serial_number_control_code,
1019 l_wip_entity_id,
1020 l_wip_line_id,
1021 l_wip_repetitive_schedule_id,
1022 l_wip_operation_seq_num,
1023 l_wip_resource_seq_num,
1024 l_bom_resource_id
1025 FROM MTL_SYSTEM_ITEMS MSI,
1026 PO_LINES_ALL POL,
1027 PO_DISTRIBUTIONS_ALL POD,
1028 PO_HEADERS_ALL POH,
1029 PO_LINE_LOCATIONS_ALL POLL
1030 WHERE
1031 NVL(POLL.APPROVED_FLAG,'N') = 'Y' AND
1032 NVL(POLL.CANCEL_FLAG, 'N') = 'N' AND
1033 NVL(POLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED') AND
1034 POLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND
1035 POLL.RECEIVING_ROUTING_ID = 3 AND
1036 POH.PO_HEADER_ID = POLL.PO_HEADER_ID AND
1037 POL.PO_LINE_ID = POLL.PO_LINE_ID AND
1038 POD.PO_HEADER_ID = POLL.PO_HEADER_ID AND
1039 POD.PO_LINE_ID = POL.PO_LINE_ID AND
1040 POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
1041 NVL(MSI.ORGANIZATION_ID,POLL.SHIP_TO_ORGANIZATION_ID) = POLL.SHIP_TO_ORGANIZATION_ID AND
1042 MSI.INVENTORY_ITEM_ID (+) = POL.ITEM_ID AND
1043 POH.PO_HEADER_ID = x_po_header_id and
1044 POLL.LINE_LOCATION_ID = x_line_location_id and
1045 POD.PO_DISTRIBUTION_ID = X_po_distribution_id;
1046
1047 asn_debug.put_line('POR_RCV_ORD_SV.INSERT_RCV_TXN_INTERFACE Inserting values in RCV_TRANSACTIONS_INTERFACE');
1048
1049 INSERT INTO RCV_TRANSACTIONS_INTERFACE (
1050 INTERFACE_TRANSACTION_ID,
1051 GROUP_ID,
1052 ORG_ID,
1053 LAST_UPDATE_DATE,
1054 LAST_UPDATED_BY,
1055 CREATED_BY,
1056 CREATION_DATE,
1057 LAST_UPDATE_LOGIN,
1058 SOURCE_DOCUMENT_CODE,
1059 DESTINATION_TYPE_CODE,
1060 DESTINATION_CONTEXT,
1061 RECEIPT_SOURCE_CODE,
1062 TRANSACTION_DATE,
1063 EXPECTED_RECEIPT_DATE,
1064 QUANTITY,
1065 UNIT_OF_MEASURE,
1066 PRIMARY_QUANTITY,
1067 PRIMARY_UNIT_OF_MEASURE,
1068 AMOUNT,
1069 SHIPMENT_HEADER_ID,
1070 SHIPMENT_LINE_ID,
1071 EMPLOYEE_ID,
1072 PO_HEADER_ID,
1073 PO_RELEASE_ID,
1074 PO_LINE_ID,
1075 PO_LINE_LOCATION_ID,
1076 PO_DISTRIBUTION_ID,
1077 PO_UNIT_PRICE,
1078 CURRENCY_CODE,
1079 CURRENCY_CONVERSION_RATE,
1080 CURRENCY_CONVERSION_TYPE,
1081 CURRENCY_CONVERSION_DATE,
1082 ROUTING_HEADER_ID,
1083 VENDOR_ID,
1084 VENDOR_SITE_ID,
1085 TRANSACTION_TYPE,
1086 ITEM_ID,
1087 ITEM_DESCRIPTION,
1088 ITEM_REVISION,
1089 CATEGORY_ID,
1090 VENDOR_ITEM_NUM,
1091 PACKING_SLIP,
1092 LOCATION_ID,
1093 SHIP_TO_LOCATION_ID,
1094 DELIVER_TO_PERSON_ID,
1095 DELIVER_TO_LOCATION_ID,
1096 FROM_ORGANIZATION_ID,
1097 TO_ORGANIZATION_ID,
1098 SUBINVENTORY,
1099 WIP_ENTITY_ID,
1100 WIP_LINE_ID,
1101 WIP_REPETITIVE_SCHEDULE_ID,
1102 WIP_OPERATION_SEQ_NUM,
1103 WIP_RESOURCE_SEQ_NUM,
1104 BOM_RESOURCE_ID,
1105 PROCESSING_STATUS_CODE,
1106 PROCESSING_MODE_CODE,
1107 TRANSACTION_STATUS_CODE,
1108 PARENT_TRANSACTION_ID,
1109 INSPECTION_STATUS_CODE,
1110 USE_MTL_LOT,
1111 USE_MTL_SERIAL,
1112 LOCATOR_ID,
1113 -- REQUISITION_LINE_ID,
1114 COMMENTS,
1115 WAYBILL_AIRBILL_NUM,
1116 USSGL_TRANSACTION_CODE,
1117 JOB_ID,
1118 MATCHING_BASIS)
1119 SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
1120 X_group_id,
1121 MO_GLOBAL.get_current_org_id,
1122 SYSDATE,
1123 X_user_id,
1124 X_user_id,
1125 SYSDATE,
1126 X_user_id,
1127 l_order_type_code, -- always PO for both standard and asn
1128 l_DESTINATION_TYPE_CODE,
1129 l_DESTINATION_TYPE_CODE,
1130 'VENDOR', -- same for ASN and PO
1131 X_receipt_date,
1132 l_expected_receipt_date,
1133 X_qty,
1134 X_receipt_uom,
1135 X_primary_qty,
1136 X_primary_uom,
1137 X_receipt_amt,
1138 DECODE(x_source_type_code,'VENDOR', NULL, l_rcv_shipment_header_id),
1139 DECODE(x_source_type_code,'VENDOR', NULL, x_rcv_shipment_line_id),
1140 X_employee_id,
1141 DECODE(l_order_type_code,'PO', l_po_header_id, NULL),
1142 DECODE(l_order_type_code,'PO', l_po_release_id, NULL),
1143 DECODE(l_order_type_code,'PO', l_po_line_id, NULL),
1144 DECODE(l_order_type_code,'PO', l_po_line_location_id, NULL),
1145 DECODE(l_order_type_code,'PO', l_po_distribution_id, NULL),
1146 l_unit_price,
1147 l_currency_code,
1148 l_currency_conversion_rate,
1149 l_currency_conversion_type,
1150 l_currency_conversion_date,
1151 l_routing_id,
1152 l_vendor_id,
1153 l_vendor_site_id,
1154 decode(x_caller,
1155 'WEB','CONFIRM RECEIPT',
1156 'WF','CONFIRM RECEIPT(WF)',
1157 'WP4','CONFIRM RECEIPT',
1158 'WP4_CONFIRM','CONFIRM RECEIPT',
1159 'AUTO_RECEIVE','CONFIRM RECEIPT(WF)'), -- 'EXPRESS DIRECT' this is the transaction_type
1160 l_item_id,
1161 l_item_description,
1162 l_item_revision,
1163 l_item_category_id,
1164 l_vendor_item_number,
1165 X_PackingSlip,
1166 l_ship_to_location_id,
1167 l_ship_to_location_id,
1168 DECODE(l_order_type_code,'PO',l_deliver_to_person_id, l_deliver_to_person_id),
1169 DECODE(l_order_type_code,'PO', l_DELIVER_TO_LOCATION_ID, L_DELIVER_TO_LOCATION_ID),
1170 DECODE(l_order_type_code,'PO', NULL, l_from_organization_id),
1171 l_to_organization_id,
1172 DECODE(l_order_type_code,'PO', l_DESTINATION_SUBINVENTORY, L_DESTINATION_SUBINVENTORY),
1173 DECODE(l_order_type_code,'PO',l_WIP_ENTITY_ID, NULL),
1174 DECODE(l_order_type_code,'PO',l_WIP_LINE_ID, NULL),
1175 DECODE(l_order_type_code,'PO',l_WIP_REPETITIVE_SCHEDULE_ID, NULL),
1176 DECODE(l_order_type_code,'PO',l_WIP_OPERATION_SEQ_NUM, NULL),
1177 DECODE(l_order_type_code,'PO',l_WIP_RESOURCE_SEQ_NUM, NULL),
1178 DECODE(l_order_type_code,'PO',l_BOM_RESOURCE_ID, NULL),
1179 'CONFIRM RECEIPT', -- 'EXPRESS' this is the processing_status_code
1180 X_trx_proc_mode,
1181 'CONFIRM', -- 'EXPRESS' this is the transaction_status_code
1182 TO_NUMBER(NULL),
1183 NULL,
1184 l_lot_control_code,
1185 l_serial_number_control_code,
1186 to_number(NULL),
1187 -- to_number(NULL), -- Bug#2718763 We no longer populate the requisition line id
1188 X_Comments,
1189 X_WayBillNum,
1190 NULL,
1191 X_JOB_ID,
1192 nvl(x_matching_basis, 'QUANTITY')
1193 FROM dual;
1194
1195
1196 exception
1197 when others THEN
1198 if (x_caller = 'WP4' OR x_caller = 'WP4_CONFIRM') then
1199 l_err_message := substr(SQLERRM,12,512);
1200 asn_debug.put_line(l_err_message);
1201 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512),'ICX');
1202 APP_EXCEPTION.RAISE_EXCEPTION;
1203 end if;
1204
1205
1206 end INSERT_RCV_TXN_INTERFACE;
1207
1208
1209 /*************************************************************
1210 ** Function :
1211 ** processTransactions
1212 ** Description :
1213 ** validates the transactions
1214 ** create or update rcv shipment header
1215 ** call the txn processor
1216 ** API called from Java layer program
1217 **************************************************************/
1218
1219 function process_transactions (X_group_id IN number,
1220 X_caller IN varchar2,
1221 X_Comments IN varchar2 default null,
1222 X_PackingSlip IN varchar2 default null,
1223 X_WayBillNum IN varchar2 default null)
1224 return number is
1225
1226 X_return_code boolean := FALSE;
1227 X_return_code_number number := 0;
1228 X_rows_succeeded number := 0;
1229 X_rows_failed number := 0;
1230 X_logonid number := 0;
1231 l_err_message varchar2(240) := null;
1232 x_column_name po_interface_errors.column_name%type;
1233 x_output_message varchar2(80) := null;
1234 x_message VARCHAR2(2000) := '';
1235 x_user_org_id NUMBER;
1236 x_txn_org_id NUMBER;
1237 begin
1238
1239 x_user_org_id := MO_GLOBAL.get_current_org_id;
1240
1241 begin
1242 select org_id
1243 into x_txn_org_id
1244 from rcv_transactions_interface rti
1245 where rti.group_id = x_group_id and rownum = 1;
1246
1247 asn_debug.put_line('process_transactions x_txn_org_id:' || x_txn_org_id);
1248
1249 if (x_txn_org_id <> MO_GLOBAL.get_current_org_id) then
1250 mo_global.set_policy_context(p_access_mode => 'S',
1251 p_org_id => x_txn_org_id);
1252 end if;
1253 exception
1254 WHEN OTHERS THEN
1255 asn_debug.put_line('Error while obtaining the org ID');
1256 end;
1257
1258 /** Validate the Input fields **/
1259 asn_debug.put_line('process_transactions Calling the val_express_transactions');
1260 rcv_express_sv.val_express_transactions(X_group_id,
1261 X_rows_succeeded,
1262 X_rows_failed);
1263 asn_debug.put_line('process_transactions 001 s=' || to_char(X_rows_succeeded));
1264 asn_debug.put_line('process_transactions 002 f=' || to_char(X_rows_failed));
1265
1266 commit;
1267
1268 if (X_rows_succeeded > 0) then
1269
1270
1271 X_return_code := create_rcv_shipment_headers(X_group_Id, X_caller,X_Comments, X_PackingSlip ,X_WayBillNum);
1272
1273
1274 if (X_return_code) then
1275 /** Bug# 7030461 -- As part of bug 3560995, commit was commented out. It gives
1276 * issues in Receiving Transaction Processor. We are inserting records into
1277 * RTI and RSH and updating the RTI.shipment_header_id with RSH.Shipment_header_id
1278 * and calling the transaction processor. Since the transaction processor runs in different
1279 * transaction, commit is necessary here.
1280 * Reverting the changes done as part of bug 3560995.
1281 **/
1282 commit;
1283 asn_debug.put_line('process_transactions 003 calling processor');
1284
1285 X_return_code_number := call_txn_processor(X_group_id, X_caller);
1286
1287
1288 else
1289
1290 X_return_code_number := 99;
1291 end if;
1292
1293
1294 else
1295 X_return_code_number := 98;
1296 end if;
1297
1298 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
1299 mo_global.set_policy_context(p_access_mode => 'S',
1300 p_org_id => x_user_org_id);
1301 end if;
1302
1303 return X_return_code_number;
1304
1305 exception
1306 when others THEN -- there's a problem with val_express_transactions
1307 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
1308 mo_global.set_policy_context(p_access_mode => 'S',
1309 p_org_id => x_user_org_id);
1310 end if;
1311 if (x_caller = 'WP4' OR x_caller ='WP4_CONFIRM') then
1312 x_message := fnd_message.get;
1313 if (x_message = '') THEN
1314 ERROR_STACK.PUSHMESSAGE(x_message,'ICX');
1315 asn_debug.put_line(x_message);
1316 ELSE
1317 ERROR_STACK.PUSHMESSAGE(substr(SQLERRM,12,512),'ICX');
1318 asn_debug.put_line(substr(SQLERRM,12,512));
1319 END IF;
1320 end if;
1321 return 97;
1322 end process_transactions;
1323
1324 /**************************************************************
1325 ** Private Function :
1326 ** processRcvShipment
1327 ** Description :
1328 ** update rcv shipment header for ASN and Internal Shipment
1329 ** creates a header for those txns that have the same vendor and to_org_id
1330 ** return true if function successful
1331 **************************************************************
1332
1333 function processRcvShipment (x_group_id in number,
1334 x_caller in varchar2,
1335 x_Comments in varchar2 default null,
1336 x_PackingSlip in varchar2 default null,
1337 x_WayBillNum in varchar2 default null,
1338 x_Ussgl_Transaction_Code in varchar2 default null)
1339 return boolean;
1340
1341
1342 */
1343
1344
1345 /****************************************************
1346 ** Function : Create_Rcv_Shipment_Header
1347 ** Description : This procedure creates a header
1348 ** for those txns that have the same vendor
1349 ** and to_org_id.
1350 *****************************************************/
1351
1352 function create_rcv_shipment_headers (X_group_id IN NUMBER,
1353 X_caller IN varchar2,
1354 X_Comments IN varchar2 default null,
1355 X_PackingSlip IN varchar2 default null,
1356 X_WayBillNum IN varchar2 default null)
1357
1358 return boolean is
1359
1360 cursor c0 is
1361 SELECT RTI.TO_ORGANIZATION_ID,
1362 RTI.VENDOR_ID, RTI.WAYBILL_AIRBILL_NUM, POD.ORG_ID
1363 FROM RCV_TRANSACTIONS_INTERFACE RTI, PO_DISTRIBUTIONS_ALL POD
1364 WHERE GROUP_ID = X_GROUP_ID AND
1365 SHIPMENT_LINE_ID IS NULL AND
1366 RTI.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
1367 GROUP BY RTI.TO_ORGANIZATION_ID, RTI.VENDOR_ID, RTI.WAYBILL_AIRBILL_NUM, POD.ORG_ID;
1368
1369 /*
1370 cursor c0 is
1371 select distinct to_organization_id,
1372 vendor_id,waybill_airbill_num
1373 from rcv_transactions_interface
1374 where group_id = X_group_id and shipment_line_id is null;
1375 -- for update of shipment_header_id;
1376 */
1377
1378 cursor c1 is
1379 select to_organization_id, shipment_header_id, comments, packing_slip,waybill_airbill_num
1380 from rcv_transactions_interface
1381 where group_id = X_group_id and shipment_line_id is not null and
1382 shipment_header_id is not null;
1383
1384 cursor c2 is
1385 select distinct shipment_header_id
1386 from rcv_transactions_interface trans
1387 where group_id = X_group_id and shipment_line_id is not null and
1388 shipment_header_id is not null;
1389
1390
1391 X_sysdate date := SYSDATE;
1392 X_userid number := 0;
1393 X_vendor_id rcv_transactions_interface.vendor_id%type;
1394 X_org_id PO_DISTRIBUTIONS_ALL.ORG_ID%type;
1395 X_to_org_id rcv_transactions_interface.to_organization_id%type;
1396 X_receipt_num rcv_shipment_headers.receipt_num%type;
1397 X_created_by rcv_shipment_headers.created_by%type;
1398 X_last_update_login rcv_shipment_headers.last_update_login%type;
1399 X_count number := 0;
1400 X_shipment_header_id rcv_shipment_headers.shipment_header_id%type;
1401 X_employee_id rcv_shipment_headers.employee_id%type := 0;
1402 X_request_id rcv_shipment_headers.request_id%type := 0;
1403
1404 X_pgm_app_id rcv_shipment_headers.program_application_id%type := 0;
1405 X_pgm_id rcv_shipment_headers.program_id%type := 0;
1406 l_err_message varchar2(240) := null;
1407 x_rcpt_count NUMBER := 1;
1408 x_organization_name VARCHAR2(60);
1409 x_supplier_name PO_VENDORS.VENDOR_NAME%TYPE;
1410 x_line_waybill_airbill_num VARCHAR2(20);
1411 x_wayairnum VARCHAR2(20);
1412 x_req_number VARCHAR2(25);
1413 x_new_comments VARCHAR2(240);
1414 x_new_packingSlip VARCHAR2(25);
1415 x_new_waybillNum VARCHAR2(20);
1416 x_asn_type VARCHAR2(25);
1417 X_vendor_site_id rcv_transactions_interface.vendor_site_id%type;
1418
1419 begin
1420
1421 X_created_by := fnd_global.user_id;
1422 x_last_update_login := fnd_global.user_id;
1423
1424 BEGIN
1425 SELECT HR.PERSON_ID
1426 INTO x_employee_id
1427 FROM FND_USER FND, per_people_f HR
1428 WHERE FND.USER_ID = X_created_by
1429 AND FND.EMPLOYEE_ID = HR.PERSON_ID
1430 AND sysdate between hr.effective_start_date AND hr.effective_end_date
1431 AND ROWNUM = 1;
1432 EXCEPTION
1433 WHEN others THEN
1434 x_employee_id := 0;
1435 END;
1436
1437
1438 open c0;
1439
1440 loop
1441 fetch c0 into X_to_org_id, x_vendor_id,x_line_waybill_airbill_num, X_org_id;
1442 exit when c0%notfound;
1443
1444 /* Get the Receipt Number */
1445
1446
1447 SELECT to_char(next_receipt_num + 1)
1448 INTO X_receipt_num
1449 FROM rcv_parameters
1450 WHERE organization_id = X_to_org_id
1451 FOR UPDATE OF next_receipt_num;
1452
1453 LOOP
1454
1455 SELECT count(*)
1456 INTO X_count
1457 FROM rcv_shipment_headers
1458 WHERE receipt_num = X_receipt_num and
1459 ship_to_org_id = X_to_org_id;
1460
1461 IF (X_count = 0) THEN
1462 update rcv_parameters
1463 set next_receipt_num = X_receipt_num
1464 where organization_id = X_to_org_id;
1465
1466 EXIT;
1467 ELSE
1468 X_receipt_num := to_char(to_number(X_receipt_num) + 1);
1469 END IF;
1470
1471 END LOOP;
1472
1473 /* Get the shipment Header id */
1474
1475 SELECT rcv_shipment_headers_s.nextval
1476 INTO X_shipment_header_id
1477 FROM sys.dual;
1478
1479
1480 /* For every unique Org_id, Vendor_id combination,
1481 ** create a header */
1482
1483 INSERT INTO RCV_SHIPMENT_HEADERS (
1484 SHIPMENT_HEADER_ID,
1485 LAST_UPDATE_DATE,
1486 LAST_UPDATED_BY,
1487 CREATION_DATE,
1488 CREATED_BY,
1489 LAST_UPDATE_LOGIN,
1490 RECEIPT_SOURCE_CODE,
1491 VENDOR_ID,
1492 ORGANIZATION_ID,
1493 SHIP_TO_ORG_ID,
1494 RECEIPT_NUM,
1495 EMPLOYEE_ID,
1496 REQUEST_ID,
1497 PROGRAM_APPLICATION_ID,
1498 PROGRAM_ID,
1499 PROGRAM_UPDATE_DATE,
1500 COMMENTS,
1501 PACKING_SLIP,
1502 WAYBILL_AIRBILL_NUM,
1503 USSGL_TRANSACTION_CODE )
1504 VALUES (
1505 X_shipment_header_id,
1506 SYSDATE,
1507 X_created_by,
1508 SYSDATE,
1509 X_created_by,
1510 X_last_update_login,
1511 'VENDOR',
1512 X_vendor_id,
1513 X_to_org_id,
1514 X_to_org_id,
1515 X_receipt_num,
1516 X_employee_id,
1517 X_request_id,
1518 X_pgm_app_id,
1519 X_pgm_id,
1520 SYSDATE,
1521 X_Comments ,
1522 X_PackingSlip,
1523 x_line_waybill_airbill_num,
1524 NULL);
1525
1526 IF x_line_waybill_airbill_num IS NULL THEN
1527 update rcv_transactions_interface
1528 set shipment_header_id = x_shipment_header_id
1529 where group_id = X_group_id
1530 and to_organization_id = x_to_org_id
1531 and vendor_id = x_vendor_id
1532 and shipment_line_id is null
1533 AND waybill_airbill_num IS NULL;
1534 x_req_number := NULL;
1535 ELSE
1536 update rcv_transactions_interface
1537 set shipment_header_id = x_shipment_header_id
1538 where group_id = X_group_id
1539 and to_organization_id = x_to_org_id
1540 and vendor_id = x_vendor_id
1541 and shipment_line_id is null
1542 AND waybill_airbill_num = x_line_waybill_airbill_num;
1543 x_req_number := NULL;
1544 END IF;
1545
1546 BEGIN
1547 SELECT distinct vendor_site_id
1548 INTO X_vendor_site_id
1549 FROM rcv_transactions_interface
1550 WHERE group_id = X_group_id and
1551 shipment_header_id = x_shipment_header_id;
1552
1553 asn_debug.put_line('vendor_site='||to_char(X_vendor_site_id));
1554 EXCEPTION
1555 WHEN others THEN
1556 X_vendor_site_id := null;
1557 END;
1558
1559 if(X_vendor_site_id is not null) then
1560 update rcv_shipment_headers
1561 set vendor_site_id = X_vendor_site_id
1562 where shipment_header_id = x_shipment_header_id;
1563 end if;
1564
1565 x_rcpt_count := x_rcpt_count +1;
1566 end loop;
1567
1568 close c0;
1569
1570 /* update intransit shipment header according to user entered info */
1571
1572 asn_debug.put_line('number of PO receipt created is ' || to_char(x_rcpt_count-1));
1573
1574 open c1;
1575
1576 loop
1577 fetch c1 into X_to_org_id, X_shipment_header_id, X_new_comments, X_new_packingSlip, X_new_waybillNum;
1578 exit when c1%notfound;
1579
1580 begin
1581 select receipt_num
1582 into X_receipt_num
1583 from rcv_shipment_headers
1584 where shipment_header_id = X_shipment_header_id and
1585 receipt_num is not null;
1586 exception
1587 when no_data_found then
1588 /* Get the Receipt Number */
1589 SELECT to_char(next_receipt_num + 1)
1590 INTO X_receipt_num
1591 FROM rcv_parameters
1592 WHERE organization_id = X_to_org_id
1593 FOR UPDATE OF next_receipt_num;
1594
1595 LOOP
1596
1597 SELECT count(*)
1598 INTO X_count
1599 FROM rcv_shipment_headers
1600 WHERE receipt_num = X_receipt_num and
1601 ship_to_org_id = X_to_org_id;
1602
1603 IF (X_count = 0) THEN
1604 update rcv_parameters
1605 set next_receipt_num = X_receipt_num
1606 where organization_id = X_to_org_id;
1607
1608 EXIT;
1609 ELSE
1610 X_receipt_num := to_char(to_number(X_receipt_num) + 1);
1611 END IF;
1612
1613 END LOOP;
1614
1615 update rcv_shipment_headers
1616 set receipt_num=X_receipt_num
1617 where shipment_header_id = X_shipment_header_id;
1618
1619 end; -- no receipt number
1620
1621 end loop;
1622
1623 close c1;
1624
1625 return TRUE;
1626
1627 exception
1628 when others then
1629
1630 if (x_caller = 'WP4' OR x_caller ='WP4_CONFIRM') THEN
1631 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512),'ICX');
1632 APP_EXCEPTION.RAISE_EXCEPTION;
1633 end if;
1634 return FALSE;
1635
1636 end create_rcv_shipment_headers;
1637
1638
1639 /*************************************************************
1640 ** Function : Call_Txn_Processor
1641 ** Description : This function calls the transaction processor
1642 ** in ONLINE mode.
1643 **************************************************************/
1644
1645 function call_txn_processor(X_group_id IN Number,
1646 X_caller IN varchar2)
1647 return number is
1648
1649 x_trx_proc_mode varchar2(40);
1650 rc number;
1651 delete_rows boolean := FALSE;
1652
1653 timeout number := 300;
1654 outcome varchar2(200) := NULL;
1655 message varchar2(200) := NULL;
1656 l_err_message varchar2(240) := null;
1657
1658 X_user_id number;
1659 X_resp_id number;
1660 x_appl_id NUMBER;
1661
1662 X_str varchar2(2000) := NULL;
1663 X_output_message varchar2(2000) := NULL;
1664 x_progress VARCHAR2(1000) := '';
1665 r_val1 varchar2(200) := NULL;
1666 r_val2 varchar2(200) := NULL;
1667 r_val3 varchar2(200) := NULL;
1668 r_val4 varchar2(200) := NULL;
1669 r_val5 varchar2(200) := NULL;
1670 r_val6 varchar2(200) := NULL;
1671 r_val7 varchar2(200) := NULL;
1672 r_val8 varchar2(200) := NULL;
1673 r_val9 varchar2(200) := NULL;
1674 r_val10 varchar2(200) := NULL;
1675 r_val11 varchar2(200) := NULL;
1676 r_val12 varchar2(200) := NULL;
1677 r_val13 varchar2(200) := NULL;
1678 r_val14 varchar2(200) := NULL;
1679 r_val15 varchar2(200) := NULL;
1680 r_val16 varchar2(200) := NULL;
1681 r_val17 varchar2(200) := NULL;
1682 r_val18 varchar2(200) := NULL;
1683 r_val19 varchar2(200) := NULL;
1684 r_val20 varchar2(200) := NULL;
1685 x_user_org_id NUMBER;
1686 x_txn_org_id NUMBER;
1687 begin
1688
1689 x_progress := '001 calling txn_processor for group=' || to_char(X_group_id);
1690 asn_debug.put_line(x_progress);
1691
1692 x_user_id := fnd_global.user_id;
1693 x_resp_id := fnd_global.resp_id;
1694 x_appl_id := fnd_global.resp_appl_id;
1695
1696 fnd_global.APPS_INITIALIZE (X_user_id, X_resp_id, x_appl_id);
1697
1698 -- Code for setting the org context same as the org id from PO
1699 asn_debug.put_line('call_txn_processor x_group_id:' || x_group_id);
1700
1701 x_user_org_id := MO_GLOBAL.get_current_org_id;
1702
1703 begin
1704 select org_id
1705 into x_txn_org_id
1706 from rcv_transactions_interface rti
1707 where rti.group_id = x_group_id and rownum = 1;
1708
1709 if (x_txn_org_id <> MO_GLOBAL.get_current_org_id) then
1710 mo_global.set_policy_context(p_access_mode => 'S',
1711 p_org_id => x_txn_org_id);
1712 end if;
1713 exception
1714 WHEN OTHERS THEN
1715 asn_debug.put_line('Error while obtaining the org ID');
1716 end;
1717
1718 x_trx_proc_mode := 'ONLINE';
1719
1720 if (X_trx_proc_mode = 'ONLINE') THEN
1721 x_progress := '002';
1722 asn_debug.put_line(x_progress);
1723
1724 rc := fnd_transaction.synchronous (
1725 timeout, outcome, message, 'PO', 'RCVTPO',
1726 X_trx_proc_mode, X_group_id,
1727 x_txn_org_id, NULL, NULL, NULL, NULL, NULL,
1728 NULL, NULL, NULL, NULL, NULL, NULL,
1729 NULL, NULL, NULL, NULL, NULL, NULL);
1730
1731 x_progress := '003online call return=' || to_char(rc) || '; outcome='|| outcome;
1732 asn_debug.put_line(x_progress);
1733
1734 if rc = 1 then
1735 if (por_rcv_ord_SV.check_group_id(X_group_id)) then
1736 fnd_message.set_name('FND', 'TM-TIMEOUT');
1737 x_str := fnd_message.get;
1738 fnd_message.clear;
1739
1740 FND_MESSAGE.set_name('FND','CONC-Error running standalone');
1741 fnd_message.set_token('PROGRAM', 'Receiving Transaction Manager - RCVOLTM');
1742 fnd_message.set_token('REQUEST', X_group_id);
1743 fnd_message.set_token('REASON', x_str);
1744
1745 htp.nl;
1746 x_output_message := fnd_message.get;
1747
1748 if (X_caller <> 'WP4'OR x_caller <> 'WP4_CONFIRM') then
1749 htp.teletype(x_output_message);
1750 htp.nl;
1751 ELSE
1752 asn_debug.put_line('return 1, msg=' || x_output_message);
1753 ERROR_STACK.PUSHMESSAGE( x_output_message, 'ICX');
1754 end if;
1755 end if;
1756 delete_rows := TRUE;
1757 elsif rc = 2 then
1758 IF (por_rcv_ord_SV.check_group_id(X_group_id)) THEN
1759
1760
1761 fnd_message.set_name('FND', 'TM-SVC LOCK HANDLE FAILED');
1762 x_str := fnd_message.get;
1763
1764 FND_MESSAGE.set_name('FND','CONC-Error running standalone');
1765
1766 fnd_message.set_token('PROGRAM', 'Receiving Transaction Manager - RCVOLTM');
1767 fnd_message.set_token('REQUEST', X_group_id);
1768 fnd_message.set_token('REASON', x_str);
1769
1770
1771 x_output_message := fnd_message.get;
1772 ERROR_STACK.PUSHMESSAGE( x_output_message, 'ICX');
1773 asn_debug.put_line('return 2, msg=' || x_output_message);
1774 end if;
1775 delete_rows := TRUE;
1776 elsif (rc = 3 or (outcome IN ('WARNING', 'ERROR'))) then
1777 asn_debug.put_line('return 3 from txn processor, or outcome='|| outcome);
1778 IF (por_rcv_ord_SV.check_group_id(X_group_id)) THEN
1779
1780
1781 rc := fnd_transaction.get_values (r_val1, r_val2, r_val3, r_val4, r_val5,
1782 r_val6, r_val7, r_val8, r_val9, r_val10,
1783 r_val11, r_val12, r_val13, r_val14, r_val15,
1784 r_val16, r_val17, r_val18, r_val19, r_val20
1785 );
1786 x_output_message := r_val1;
1787
1788 IF (r_val2 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val2; END IF;
1789 IF (r_val3 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val3; END IF;
1790 IF (r_val4 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val4; END IF;
1791 IF (r_val5 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val5; END IF;
1792 IF (r_val6 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val6; END IF;
1793 IF (r_val7 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val7; END IF;
1794 IF (r_val8 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val8; END IF;
1795 IF (r_val9 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val9; END IF;
1796 IF (r_val10 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val10; END IF;
1797 IF (r_val11 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val11; END IF;
1798 IF (r_val12 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val12; END IF;
1799 IF (r_val13 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val13; END IF;
1800 IF (r_val14 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val14; END IF;
1801 IF (r_val15 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val15; END IF;
1802 IF (r_val16 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val16; END IF;
1803 IF (r_val17 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val17; END IF;
1804 IF (r_val18 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val18; END IF;
1805 IF (r_val19 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val19; END IF;
1806 IF (r_val20 IS NOT NULL) THEN x_output_message := x_output_message || fnd_global.local_chr(10) || r_val20; END IF;
1807
1808 ERROR_STACK.PUSHMESSAGE( x_output_message, 'ICX');
1809 asn_debug.put_line('return 3 or error, msg=' || x_output_message);
1810 /* for this error case, we change the rc
1811 so that the error case will properly passed to middle tier.
1812 set to 4 to distinguish from 3
1813 */
1814 if (rc = 0 and outcome IN ('WARNING', 'ERROR')) then
1815 rc := 4;
1816 end if;
1817
1818 END IF;
1819
1820 asn_debug.put_line('After calling transaction processor, rc='|| to_char(rc));
1821 delete_rows := TRUE;
1822
1823 elsif (rc = 0 and (outcome NOT IN ('WARNING', 'ERROR'))) then
1824
1825 if (x_caller = 'WP4') then
1826 /** Since we have received over the web, we need to clean up any open
1827 notifications for the rows that belong to this group_id **/
1828
1829 por_rcv_ord_SV.cancel_pending_notifs(x_group_id);
1830
1831 end if;
1832 x_progress := '004, return 0 from txn processor';
1833 asn_debug.put_line(x_progress);
1834 delete_rows := FALSE;
1835 commit;
1836 end if;
1837
1838
1839 elsif (X_trx_proc_mode = 'IMMEDIATE') then
1840
1841
1842 rc := fnd_request.submit_request('PO',
1843 'RVCTP',
1844 null,
1845 null,
1846 false,
1847 'IMMEDIATE',
1848 X_group_id,
1849 chr(0),
1850 NULL, NULL, NULL, NULL, NULL, NULL,
1851 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1852 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1853 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1854 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1855 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1856 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1857 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1858 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1859 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1860 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1861 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1862 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1863 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1864
1865 if (rc <= 0 or rc is NULL) then
1866 -- htp.p('The rc is: ' || to_char(rc)); htp.nl;
1867 delete_rows := TRUE;
1868 null; -- for now
1869 else
1870 commit;
1871 rc := null;
1872 end if;
1873
1874 end if;
1875
1876 /*
1877 ** Since the insert has already occurred, make sure to set the
1878 ** transaction status to error; otherwise the next query
1879 ** you do will make it look like the transactions were
1880 ** actually awaiting the transaction processor since the
1881 ** status will be 'PENDING'
1882 ** DEBUG: We should log a message in the rcv interface errors
1883 ** so if the user reviews these records, they'll know why
1884 ** they were not processed.
1885 */
1886
1887 if (delete_rows) then
1888
1889 BEGIN
1890
1891 delete from rcv_transactions_interface
1892 where group_id = X_group_id;
1893
1894
1895 PO_REQS_CONTROL_SV.commit_changes;
1896
1897 EXCEPTION
1898 WHEN OTHERS THEN
1899 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
1900 mo_global.set_policy_context(p_access_mode => 'S',
1901 p_org_id => x_user_org_id);
1902 end if;
1903 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512), 'ICX');
1904 return 95;
1905 END;
1906
1907 END if;
1908 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
1909 mo_global.set_policy_context(p_access_mode => 'S',
1910 p_org_id => x_user_org_id);
1911 end if;
1912
1913 return rc;
1914
1915 EXCEPTION
1916 when others then
1917 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
1918 mo_global.set_policy_context(p_access_mode => 'S',
1919 p_org_id => x_user_org_id);
1920 end if;
1921 x_progress := 'call txn processor exception' || substr(SQLERRM,12,512);
1922 asn_debug.put_line(x_progress);
1923 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512), 'ICX');
1924 return 94;
1925
1926 end call_txn_processor;
1927
1928 /*=============================================================
1929
1930 FUNCTION NAME: check_group_id
1931
1932 =============================================================*/
1933 FUNCTION check_group_id (x_group_id IN NUMBER) RETURN BOOLEAN IS
1934
1935 x_rec_count NUMBER := 0;
1936
1937 BEGIN
1938
1939 SELECT COUNT(1)
1940 INTO x_rec_count
1941 FROM RCV_TRANSACTIONS_INTERFACE
1942 WHERE group_id = x_group_id;
1943
1944 IF (x_rec_count = 0) THEN
1945
1946 return (FALSE);
1947
1948 ELSE
1949
1950 return (TRUE);
1951
1952 END IF;
1953
1954 EXCEPTION
1955
1956 WHEN NO_DATA_FOUND THEN RETURN(FALSE);
1957 WHEN OTHERS THEN raise;
1958
1959 END check_group_id;
1960
1961 procedure cancel_pending_notifs (x_group_id IN NUMBER) is
1962
1963 cursor C1 is
1964 select distinct nvl(pod.wf_item_key, rcv.po_header_id ||';'||
1965 rcv.deliver_to_person_id || ';' ||
1966 to_char(nvl(poll.promised_date,
1967 poll.need_by_date), 'DD-MON-YYYY'))
1968 from rcv_transactions rcv,po_line_locations_all poll,
1969 po_distributions_all pod
1970 where group_id = x_group_id and
1971 poll.line_location_id = rcv.po_line_location_id AND
1972 pod.po_distribution_id = rcv.po_distribution_id;
1973
1974 wf_item_key varchar2(2000);
1975 wf_item_type varchar2(6) := 'PORCPT';
1976
1977 begin
1978
1979 open c1;
1980 loop
1981 fetch c1 into wf_item_key ;
1982 exit when c1%notfound;
1983
1984
1985 if (por_rcv_ord_SV.notif_is_active(wf_item_type,wf_item_key)) then
1986 WF_Engine.AbortProcess(wf_item_type,wf_item_key);
1987 end if;
1988
1989 end loop;
1990
1991 end cancel_pending_notifs;
1992
1993 FUNCTION notif_is_active (wf_item_type in varchar2,
1994 wf_item_key in varchar2) RETURN BOOLEAN is
1995
1996 x_act_status varchar2(8);
1997 x_progress varchar2(100) := '001';
1998 /** this procedure is currently only called when the transaction is done
1999 via the menu on the web. Hence it is safe now to default it to WP4 **/
2000 x_caller varchar2(3) := 'WP4';
2001
2002
2003 BEGIN
2004 x_progress := 'POR_RCV_ORD_SV.is_active-001';
2005
2006 --Bug 4999072 Changed the query to reduce the memory usage
2007 SELECT WIAS.ACTIVITY_STATUS
2008 INTO x_act_status
2009 FROM WF_ITEM_ACTIVITY_STATUSES WIAS,
2010 WF_ITEMS WI,
2011 WF_PROCESS_ACTIVITIES PA
2012 WHERE WIAS.ITEM_TYPE = wf_item_type
2013 AND WIAS.ITEM_KEY = wf_item_key
2014 AND WIAS.ITEM_TYPE = WI.ITEM_TYPE
2015 AND WIAS.ITEM_KEY = WI.ITEM_KEY
2016 AND WI.ROOT_ACTIVITY=PA.ACTIVITY_NAME
2017 AND WIAS.PROCESS_ACTIVITY= PA.INSTANCE_ID;
2018
2019
2020
2021 if x_act_status not in ('COMPLETE', 'ERROR') then
2022 return TRUE;
2023 else return FALSE;
2024 end if;
2025 exception
2026 when no_data_found then
2027 return false;
2028 when others then
2029 if (x_caller = 'WP4'OR x_caller ='WP4_CONFIRM') then
2030 error_stack.pushmessage( substr(SQLERRM,12,512),'ICX');
2031 app_exception.raise_exception;
2032 else
2033 return FALSE;
2034 end if;
2035 end notif_is_active;
2036
2037 end;