[Home] [Help]
PACKAGE BODY: APPS.PORCPTWF
Source
1 PACKAGE BODY PORCPTWF AS
2 /* $Header: PORCPWFB.pls 120.17.12000000.3 2007/10/22 04:29:07 pilamuru ship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 -- Added parameter to check if distribution data updation is to be skipped
8 Function Populate_Order_Info(itemtype in varchar2,
9 itemkey in varchar2,
10 skip_distribution_updation in varchar2 default 'N') Return number;
11
12
13 /*===========================================================================
14 PROCEDURE NAME: Select_Orders
15
16 DESCRIPTION: This server procedure is defined as a concurrent
17 PL/SQL executable program and is scheduled to run
18 from the Concurrent Manager at a regular intervals
19 (e.g. every day).
20
21 This procedure does the following:
22 - Open a cursor on RCV_CONFIRM_RECEIPT_V table to
23 select open PO shipments. The records are grouped
24 by PO number, Requester ID and Due date.
25
26 - For each unique PO number, Requester ID and Due date
27 it calls the Start_Rcpt_Process to initiate the
28 Confirm Receipt workflow process.
29
30 CHANGE HISTORY: WLAU 1/15/1997 Created
31 WLAU 2/25/1997 Added WF_PURGE.total to delete
32 the completed WF activities
33 ===========================================================================*/
34
35 TYPE rcpt_record IS RECORD (
36
37 line_number NUMBER,
38 expected_qty NUMBER,
39 quantity_received NUMBER,
40 ordered_qty NUMBER,
41 unit_of_measure VARCHAR2(25),
42 item_description VARCHAR2(240),
43 currency_code VARCHAR2(15),
44 unit_price NUMBER,
45 po_distribution_id NUMBER);
46
47 /*===========================================================================
48 FUNCTION NAME: get_txn_error_message
49
50 DESCRIPTION: Get the Receiving transaction processor error message
51
52 CHANGE HISTORY: nwang 10/8/1998 Created
53 svasamse 28-May-05 This method is modified to
54 return the message name instead of
55 message text. JRAD Notification
56 enhancement.
57 ===========================================================================*/
58
59 PROCEDURE get_txn_error_message(x_group_id IN number,
60 x_RCV_txns_rc IN number,
61 x_rcv_trans_status IN OUT NOCOPY varchar2,
62 x_message_token IN OUT NOCOPY varchar2) IS
63
64 BEGIN
65 x_message_token := NULL;
66
67 IF x_RCV_txns_rc = 1 THEN
68
69 -- Receiving Transaction Manager was timed out
70 x_rcv_trans_status:= 'RCV_RCPT_NO_RCV_MANAGER';
71
72 ELSIF x_RCV_txns_rc = 2 THEN
73
74 -- Receiving Transaction Manager is not active
75 x_rcv_trans_status:= 'RCV_RCPT_NO_RCV_MANAGER';
76
77 ELSIF x_RCV_txns_rc = 99 THEN
78
79 -- Create Receiving Shipment Headers failed
80 x_rcv_trans_status:= 'RCV_RCPT_CREATE_RCVSHIP_FAILED';
81
82 ELSIF x_RCV_txns_rc = 98 THEN
83
84 -- Receiving Transaction Interface records validation failed
85 x_rcv_trans_status:= 'RCV_RCPT_VALIDATION_FAILED';
86
87 BEGIN
88
89 -- Try to get the retrieve the first error message
90 SELECT int_err.error_message_name
91 INTO x_message_token
92 FROM po_interface_errors int_err
93 WHERE int_err.batch_id = x_group_id
94 AND int_err.interface_transaction_id =
95 (SELECT MIN(int_err2.interface_transaction_id)
96 FROM po_interface_errors int_err2
97 WHERE int_err2.batch_id = x_group_id);
98
99 EXCEPTION
100 when no_data_found then
101 x_message_token := null;
102 END;
103
104 ELSE
105
106 -- Receiving Transaction Manager failed, return code ...
107 x_message_token := to_char(x_RCV_txns_rc);
108 x_rcv_trans_status := 'RCV_RCPT_RCV_MGR_ERROR';
109
110 END IF;
111
112 END get_txn_error_message;
113
114
115 /*===========================================================================
116 PROCEDURE NAME: Process_Auto_Receive
117
118 DESCRIPTION: This procedure auto-receives the shipments that have
119 auto_receive_flag = 'Y'
120
121 CHANGE HISTORY: NWANG 10/7/1998 Created
122 ===========================================================================*/
123
124 PROCEDURE Process_Auto_Receive(x_po_header_id IN NUMBER,
125 x_requester_id IN NUMBER,
126 x_exp_receipt_date IN DATE) IS
127
128 x_group_id NUMBER;
129 x_inserted_txn BOOLEAN;
130 x_insert_txns_count NUMBER := 0;
131 x_RCV_txns_rc NUMBER := 0;
132 x_line_location_id NUMBER;
133 x_po_distribution_id NUMBER;
134 x_expected_receipt_qty NUMBER;
135 x_primary_uom VARCHAR2(25);
136 x_primary_uom_class VARCHAR2(10);
137 x_item_id NUMBER;
138 x_org_id NUMBER;
139 x_rcv_trans_status VARCHAR2(500) := NULL;
140 x_allow_inv_dest_receipts Varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
141 x_insert_txns_status NUMBER;
142 x_auto_receive_flag VARCHAR2(1);
143
144 t_po_header_id rcvNumberArray;
145 t_line_location_id rcvNumberArray;
146 t_expected_receipt_qty rcvNumberArray;
147 t_ordered_uom rcvVarcharArray;
148 -- bug 4672728 - the non translated UOM values.
149 t_ordered_uom_non_tl rcvVarcharArray;
150 t_item_id rcvNumberArray;
151 t_primary_uom_class rcvVarcharArray;
152 t_org_id rcvNumberArray;
153 t_po_distribution_id rcvNumberArray;
154 t_Comments rcvVarcharArray;
155 t_PackingSlip rcvVarcharArray;
156 t_WayBillNum rcvVarcharArray;
157
158 type select_shipments_Cursor is ref cursor ;
159 Porcpt_Shipment select_shipments_Cursor;
160
161 x_message_token VARCHAR2(2000);
162
163 BEGIN
164
165 SELECT rcv_interface_groups_s.nextval
166 INTO x_group_id
167 FROM sys.dual;
168
169 if x_allow_inv_dest_receipts is NULL then
170 x_allow_inv_dest_receipts := 'N';
171 end if;
172
173 -- <R12 Confirm Receipt and JRAD Conversion Start>
174 -- added condition to retrive the inovice matched lines
175 if x_allow_inv_dest_receipts = 'N' then
176
177 OPEN Porcpt_Shipment for
178 SELECT rcv.po_header_id,
179 po_line_location_id,
180 expected_receipt_qty,
181 primary_uom,
182 -- Bug 4672728
183 primary_uom_non_tl,
184 item_id,
185 primary_uom_class,
186 to_organization_id,
187 po_distribution_id,
188 null,
189 null,
190 null
191 FROM POR_RCV_ALL_ITEMS_V1 rcv
192 WHERE ((expected_receipt_date is not NULL
193 AND trunc(rcv.expected_receipt_date + 1) <=
194 trunc(SYSDATE))
195 OR EXISTS (SELECT 1 FROM ap_holds aph
196 WHERE aph.line_location_id = rcv.po_line_location_id
197 AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
198 AND aph.release_lookup_code IS NULL
199 AND rcv.quantity_invoiced > quantity_delivered
200 AND rcv.quantity_invoiced <= ordered_qty))
201 AND NVL(receipt_required_flag,'N') = 'Y'
202 AND destination_type_code = 'EXPENSE'
203 AND requestor_id is not NULL
204 AND expected_receipt_qty > 0
205 AND po_header_ID = x_po_header_ID
206 AND requestor_ID = x_requester_ID;
207 else
208 OPEN Porcpt_Shipment for
209 SELECT po_header_id,
210 po_line_location_id,
211 expected_receipt_qty,
212 primary_uom,
213 -- Bug 4672728
214 primary_uom_non_tl,
215 item_id,
216 primary_uom_class,
217 to_organization_id,
218 po_distribution_id,
219 null,
220 null,
221 null
222 FROM POR_RCV_ALL_ITEMS_V1 rcv
223 WHERE ((expected_receipt_date is not NULL
224 AND trunc(rcv.expected_receipt_date + 1) <=
225 trunc(SYSDATE))
226 OR EXISTS (SELECT 1 FROM ap_holds aph
227 WHERE aph.line_location_id = rcv.po_line_location_id
228 AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
229 AND aph.release_lookup_code IS NULL
230 AND rcv.quantity_invoiced > quantity_delivered
231 AND rcv.quantity_invoiced <= ordered_qty))
232 AND NVL(receipt_required_flag,'N') = 'Y'
233 AND requestor_id is not NULL
234 AND expected_receipt_qty > 0
235 AND po_header_ID = x_po_header_ID
236 AND requestor_ID = x_requester_ID;
237
238 end if; /** AllowInvDest Receipt Check **/
239 -- <R12 Confirm Receipt and JRAD Conversion End>
240
241 FETCH porcpt_Shipment BULK COLLECT into t_po_header_id,
242 t_line_location_id,
243 t_expected_receipt_qty,
244 t_ordered_uom,
245 -- Bug 4672728
246 t_ordered_uom_non_tl,
247 t_item_id,
248 t_primary_uom_class,
249 t_org_id,
250 t_po_distribution_id,
251 t_Comments,
252 t_PackingSlip,
253 t_WayBillNum;
254
255 x_insert_txns_status := POR_RCV_ORD_SV.groupPoTransaction( t_po_header_id,
256 t_line_location_id,
257 t_expected_receipt_qty,
258 -- Bug 4672728 - We need to pass the
259 -- non translated UOM for standard UOM conversion
260 --t_ordered_uom,
261 t_ordered_uom_non_tl,
262 SYSDATE,
263 t_item_id,
264 t_primary_uom_class,
265 t_org_id,
266 t_po_distribution_id,
267 x_group_id,
268 'AUTO_RECEIVE',
269 t_Comments,
270 t_PackingSlip,
271 t_WayBillNum);
272
273 CLOSE Porcpt_Shipment;
274
275 IF x_insert_txns_status = 0 THEN
276 x_RCV_txns_rc := por_rcv_ord_sv.process_transactions(X_group_id, 'AUTO_RECEIVE');
277
278 IF x_RCV_txns_rc is NOT NULL AND
279 x_RCV_txns_rc > 0 THEN
280
281 get_txn_error_message(x_group_id, x_RCV_txns_rc, x_rcv_trans_status, x_message_token);
282 /* IF (x_rcv_trans_status = 'RCV_RCPT_VALIDATION_FAILED') then
283 fnd_message.set_name ('PO',x_rcv_trans_status);
284 x_rcv_trans_status:= fnd_message.get;
285 fnd_message.set_name ('PO',x_message_token);
286 x_rcv_trans_status := x_rcv_trans_status || fnd_message.get;
287 ELSE IF (x_rcv_trans_status = 'RCV_RCPT_RCV_MGR_ERROR') then
288 fnd_message.set_name ('PO',x_rcv_trans_status);
289 x_rcv_trans_status:= fnd_message.get || x_message_token;
290 END IF;
291 ash_debug.debug('process auto receive' , x_rcv_trans_status);
292 */
293
294 END IF;
295
296 END IF;
297 END Process_Auto_Receive;
298
299 /*===========================================================================
300 PROCEDURE NAME: Select_Orders
301
302 DESCRIPTION: This server procedure is defined as a concurrent
303 PL/SQL executable program and is scheduled to run
304 from the Concurrent Manager at a regular intervals
305 (e.g. every day).
306
307 This procedure does the following:
308 - Open a cursor on RCV_CONFIRM_RECEIPT_V table to
309 select open PO shipments. The records are grouped
310 by PO number, Requester ID and Due date.
311
312 - For each unique PO number, Requester ID and Due date
313 it calls the Start_Rcpt_Process to initiate the
314 Confirm Receipt workflow process.
315
316 CHANGE HISTORY: WLAU 1/15/1997 Created
317 WLAU 2/25/1997 Added WF_PURGE.total to delete
318 the completed WF activities
319 ===========================================================================*/
320
321 PROCEDURE Select_Orders IS
322
323
324 -- Define cursor for selecting records to start the Purchasing
325 -- Confirm Receipt workflow process. Records are retrieved from
326 -- the RCV_CONFIRM_RECEIPT_V view which is shared by the
327 -- Receive Orders Web Page.
328
329 type select_orders_Cursor is ref cursor ;
330
331 Porcpt_c select_orders_Cursor;
332
333 x_po_header_id NUMBER;
334 x_po_distribution_id NUMBER;
335 x_requester_id NUMBER;
336 x_exp_receipt_date DATE;
337 x_WF_ItemKey VARCHAR2(100);
338 x_WF_ItemKey_save VARCHAR2(100);
339 x_auto_receive_flag VARCHAR2(1);
340 x_WF_process_exists BOOLEAN;
341 x_allow_inv_dest_receipts Varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
342 x_wf_itemtype varchar2(6) := 'PORCPT';
343 x_revision_num number;
344 x_internal_req_rcpt Varchar2(1) := FND_PROFILE.value('POR_INTERNAL_CONFIRM_RECEIPT');
345 x_sys_date DATE;
346 x_item_key_count number;
347 x_po_num_rel_num POR_RCV_ALL_ITEMS_V1.PO_NUM_REL_NUM%type;
348
349 BEGIN
350
351 x_WF_ItemKey := ' ';
352 x_WF_ItemKey_save := ' ';
353
354 -- Call WF purge API to remove any existing Confirm Receipts WF items
355 -- which are completed with an END_DATE less than or equal to
356 -- SYSDATE (default). This is to ensure that the Selection
357 -- process can start the workflow process for the same item key value.
358 --
359 -- The WF purge API will not remove any WF items which are still
360 -- opened with END_DATE = NULL;
361
362
363 WF_PURGE.total ('PORCPT');
364
365 -- Start the Confirm Receipts workflow Selection process
366
367
368 if x_allow_inv_dest_receipts is NULL then
369 x_allow_inv_dest_receipts := 'N';
370 end if;
371
372
373 -- Start the Confirm Receipts workflow Selection process
374
375 if x_allow_inv_dest_receipts = 'N' then
376 -- <R12 Confirm Receipt and JRAD Conversion Start>
377 -- Modified the query to retrive the lines if an invoice
378 -- is matched for the line. Joined with AP_HOLDS
379 OPEN Porcpt_c for
380 SELECT rcv.po_header_ID, rcv.requestor_ID,
381 rcv.expected_receipt_date, rcv.revision_num,
382 rcv.po_distribution_id, po_num_rel_num
383 FROM POR_RCV_ALL_ITEMS_V1 rcv
384 WHERE ( (rcv.expected_receipt_date is not NULL
385 AND trunc(rcv.expected_receipt_date + 1) <=
386 trunc(SYSDATE)
387 )
388 OR EXISTS (SELECT 1 FROM ap_holds aph
389 WHERE aph.line_location_id = rcv.po_line_location_id
390 AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
391 AND aph.release_lookup_code IS NULL
392 AND rcv.quantity_invoiced > quantity_delivered
393 AND rcv.quantity_invoiced <= ordered_qty
394 )
395 )
396 AND NVL(receipt_required_flag,'N') = 'Y'
397 AND destination_type_code = 'EXPENSE'
398 AND requestor_ID is not NULL
399 AND expected_receipt_qty > 0
400 GROUP BY rcv.po_header_ID, rcv.requestor_ID,
401 rcv.expected_receipt_date, rcv.revision_num,
402 rcv.po_distribution_id, po_num_rel_num;
403 else
404 OPEN Porcpt_c for
405 SELECT rcv.po_header_ID, rcv.requestor_ID,
406 rcv.expected_receipt_date, rcv.revision_num,
407 rcv.po_distribution_id, po_num_rel_num
408 FROM POR_RCV_ALL_ITEMS_V1 rcv
409 WHERE ( (rcv.expected_receipt_date is not NULL
410 AND trunc(rcv.expected_receipt_date + 1) <=
411 trunc(SYSDATE)
412 )
413 OR EXISTS (SELECT 1 FROM ap_holds aph
414 WHERE aph.line_location_id = rcv.po_line_location_id
415 AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
416 AND aph.release_lookup_code IS NULL
417 AND rcv.quantity_invoiced > quantity_delivered
418 AND rcv.quantity_invoiced <= ordered_qty
419 )
420 )
421 AND NVL(receipt_required_flag,'N') = 'Y'
422 AND requestor_ID is not NULL
423 AND expected_receipt_qty > 0
424 GROUP BY rcv.po_header_ID, rcv.requestor_ID,
425 rcv.expected_receipt_date, rcv.revision_num,
426 rcv.po_distribution_id, po_num_rel_num;
427 -- <R12 Confirm Receipt and JRAD Conversion End>
428 end if; /** AllowInvDest Receipt Check */
429
430 LOOP
431
432 FETCH Porcpt_c into x_po_header_id,
433 x_requester_id,
434 x_exp_receipt_date,
435 x_revision_num,
436 x_po_distribution_id,
437 x_po_num_rel_num;
438
439 -- Contruct Confirm Receipt workflow Item Key in the combination of:
440 -- PO_Header_ID + Requester_ID + Sysdate
441 --
442
443 EXIT WHEN Porcpt_c%NOTFOUND;
444
445
446 BEGIN
447 select PORL.auto_receive_flag
448 into x_auto_receive_flag
449 from PO_REQUISITION_LINES PORL,
450 PO_REQ_DISTRIBUTIONS PORD,
451 PO_DISTRIBUTIONS pod
452 where PORD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID AND
453 PORD.REQUISITION_LINE_ID = PORL.requisition_line_id AND
454 POD.PO_DISTRIBUTION_ID = x_po_distribution_id;
455
456
457 EXCEPTION
458 when no_data_found then
459 x_auto_receive_flag := null;
460 END;
461
462 IF Porcpt_c%FOUND AND
463 (NVL(x_auto_receive_flag,'N') = 'Y') THEN
464
465
466 -- Process the auto receive shipments
467
468 Process_Auto_Receive(x_po_header_id,
469 x_requester_id,
470 x_exp_receipt_date);
471 ELSE
472 select sysdate into x_sys_date from dual;
473
474 x_WF_ItemKey := to_char(x_po_header_id) || ';' ||
475 to_char(x_requester_id) || ';' ||
476 to_char(x_sys_date,'DD-MON-YYYY:HH24:MI');
477 IF Porcpt_c%FOUND AND
478 x_WF_ItemKey <> x_WF_ItemKey_save THEN
479
480 -- <R12 Confirm Receipt and JRAD Conversion Start>
481
482 -- Requirement - We should not send the notification if
483 -- there are any active processes for the po_header_id and
484 -- requester_id combination.
485 SELECT count(1) into x_item_key_count
486 FROM wf_items
487 WHERE item_type = 'PORCPT'
488 AND item_key like x_po_header_id||';'|| x_requester_id||';%'
489 AND END_DATE is null;
490
491 -- <R12 Confirm Receipt and JRAD Conversion End>
492
493 IF x_item_key_count = 0 THEN
494
495 -- Workflow item does not exist
496 -- Invoke the Confirm Receive workflow starting procedure
497 -- for every unique workflow Item key.
498
499 -- <R12 Confirm Receipt and JRAD Conversion Start>
500
501 -- Clean the po distribtions table
502 -- set the wf_item_key column with null value
503 -- if there are any existing distribtion lines with
504 -- old match.
505 update po_distributions
506 set wf_item_key = ''
507 where po_header_id = x_po_header_ID
508 and wf_item_key like x_po_header_ID||';'||x_requester_id||';%';
509
510 -- <R12 Confirm Receipt and JRAD Conversion End>
511 PORCPTWF.Start_Rcpt_Process(x_po_header_id,
512 x_requester_id,
513 x_exp_receipt_date,
514 x_wf_itemkey,
515 x_revision_num,
516 'N',
517 '-1',
518 x_po_num_rel_num);
519
520
521 COMMIT;
522
523 END IF;
524
525 -- Save the ItemKey for the next comparison
526 x_WF_ItemKey_Save := x_WF_ItemKey;
527
528 END IF;
529
530 END IF;
531
532
533 EXIT WHEN Porcpt_c%NOTFOUND;
534
535 END LOOP;
536
537 CLOSE porcpt_c;
538
539
540
541 if x_internal_req_rcpt is not null and x_internal_req_rcpt = 'Y' then
542 Select_Internal_Orders;
543 end if;
544
545 EXCEPTION
546 WHEN NO_DATA_FOUND THEN
547 wf_core.context ('PORCPTWF','Select_Orders','No data found');
548 WHEN OTHERS THEN
549 wf_core.context ('PORCPTWF','Select_Orders','SQL error ' || sqlcode);
550 RAISE;
551
552
553 END Select_Orders;
554
555
556
557 /*===========================================================================
558 PROCEDURE NAME: Process_Auto_Receive_Internal
559
560 DESCRIPTION: This procedure auto-receives the shipments for Internal Order
561 that have auto_receive_flag = 'Y'
562
563 CHANGE HISTORY: ASABADRA 03/05/2002 Created
564 ===========================================================================*/
565 PROCEDURE Process_Auto_Receive_Internal (x_header_id IN NUMBER,
566 x_requester_id IN NUMBER,
567 x_exp_receipt_date IN DATE) IS
568
569 x_group_id NUMBER;
570 x_inserted_txn_status NUMBER := 0;
571 x_RCV_txns_rc NUMBER := 0;
572 x_rcv_trans_status VARCHAR2(500) := NULL;
573 x_allow_inv_dest_receipts Varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
574
575 t_req_line_id rcvNumberArray;
576 t_expected_receipt_qty rcvNumberArray;
577 t_ordered_uom rcvVarcharArray;
578 t_item_id rcvNumberArray;
579 t_primary_uom_class rcvVarcharArray;
580 t_org_id rcvNumberArray;
581 t_waybillNum rcvVarcharArray;
582 t_comments rcvVarcharArray;
583 t_packingSlip rcvVarcharArray;
584
585
586
587 type select_shipments_Cursor is ref cursor ;
588 Porcpt_Shipment select_shipments_Cursor;
589
590 x_message_token VARCHAR2(2000);
591
592 BEGIN
593
594 SELECT rcv_interface_groups_s.nextval
595 INTO x_group_id
596 FROM sys.dual;
597
598 if x_allow_inv_dest_receipts is NULL then
599 x_allow_inv_dest_receipts := 'N';
600 end if;
601
602 if x_allow_inv_dest_receipts = 'N' then
603 OPEN Porcpt_Shipment for
604 SELECT REQUISITION_LINE_ID,
605 EXPECTED_RECEIPT_QTY,
606 PRIMARY_UOM,
607 ITEM_ID,
608 PRIMARY_UOM_CLASS,
609 TO_ORGANIZATION_ID,
610 COMMENTS,
611 PACKING_SLIP,
612 WAYBILL_AIRBILL_NUM
613 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
614 WHERE expected_receipt_date is not NULL
615 AND expected_receipt_date = x_exp_receipt_date
616 AND destination_type_code = 'EXPENSE'
617 AND requestor_id is not NULL
618 AND expected_receipt_qty > 0
619 AND so_header_ID = x_header_ID
620 AND requestor_ID = x_requester_ID;
621 else
622 OPEN Porcpt_Shipment for
623 SELECT REQUISITION_LINE_ID,
624 EXPECTED_RECEIPT_QTY,
625 PRIMARY_UOM,
626 ITEM_ID,
627 PRIMARY_UOM_CLASS,
628 TO_ORGANIZATION_ID,
629 COMMENTS,
630 PACKING_SLIP,
631 WAYBILL_AIRBILL_NUM
632 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
633 WHERE expected_receipt_date is not NULL
634 AND expected_receipt_date = x_exp_receipt_date
635 AND requestor_id is not NULL
636 AND expected_receipt_qty > 0
637 AND so_header_ID = x_header_ID
638 AND requestor_ID = x_requester_ID;
639
640 end if; /** AllowInvDest Receipt Check **/
641
642 FETCH porcpt_Shipment BULK COLLECT into t_req_line_id,
643 t_expected_receipt_qty,
644 t_ordered_uom,
645 t_item_id,
646 t_primary_uom_class,
647 t_org_id,
648 t_comments,
649 t_packingSlip,
650 t_waybillNum;
651
652 CLOSE Porcpt_Shipment;
653
654 --ash_debug.debug('process auto receive' , '01');
655
656
657 x_inserted_txn_status := POR_RCV_ORD_SV.groupInternalTransaction (t_req_line_id,
658 t_expected_receipt_qty,
659 t_ordered_uom,
660 t_item_id,
661 t_primary_uom_class,
662 t_org_id,
663 t_comments,
664 t_packingSlip,
665 t_waybillNum,
666 x_group_id,
667 x_exp_receipt_date,
668 'WP4_CONFIRM');
669
670 --ash_debug.debug('process auto receive x_inserted_txn_status' , x_inserted_txn_status);
671
672
673
674 IF x_inserted_txn_status = 0 THEN
675
676 x_RCV_txns_rc := por_rcv_ord_sv.process_transactions(X_group_id, 'AUTO_RECEIVE');
677
678 IF x_RCV_txns_rc is NOT NULL AND
679 x_RCV_txns_rc > 0 THEN
680
681 get_txn_error_message(x_group_id, x_RCV_txns_rc, x_rcv_trans_status, x_message_token);
682 /* IF (x_rcv_trans_status = 'RCV_RCPT_VALIDATION_FAILED') then
683 fnd_message.set_name ('PO',x_rcv_trans_status);
684 x_rcv_trans_status:= fnd_message.get;
685 fnd_message.set_name ('PO',x_message_token);
686 x_rcv_trans_status := x_rcv_trans_status || fnd_message.get;
687 ELSE IF (x_rcv_trans_status = 'RCV_RCPT_RCV_MGR_ERROR') then
688 fnd_message.set_name ('PO',x_rcv_trans_status);
689 x_rcv_trans_status:= fnd_message.get || x_message_token;
690 END IF;
691 ash_debug.debug('process auto receive' , x_rcv_trans_status);
692 */
693
694 END IF;
695
696 END IF;
697 END Process_Auto_Receive_Internal;
698
699
700 PROCEDURE Select_Internal_Orders IS
701
702
703 -- Define cursor for selecting records to start the Purchasing
704 -- Confirm Receipt workflow process. Records are retrieved from
705 -- the RCV_CONFIRM_RECEIPT_V view which is shared by the
706 -- Receive Orders Web Page.
707
708 type select_orders_Cursor is ref cursor ;
709
710 Porcpt_c select_orders_Cursor;
711
712 x_header_id NUMBER;
713 x_requisition_line_id NUMBER;
714 x_requisition_header_id NUMBER;
715
716 --po_header_id NUMBER;
717 --po_distribution_id NUMBER;
718 x_requester_id NUMBER;
719 x_exp_receipt_date DATE;
720 x_WF_ItemKey VARCHAR2(100);
721 x_WF_ItemKey_save VARCHAR2(100);
722 x_auto_receive_flag VARCHAR2(1);
723 x_WF_process_exists BOOLEAN;
724 x_allow_inv_dest_receipts Varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
725 ssp_version VARCHAR2(30) := FND_PROFILE.value('POR_SSP_VERSION');
726 x_ssp_version_gt4 VARCHAR2(10) := 'Y';
727 x_wf_itemtype varchar2(6) := 'PORCPT';
728 x_revision_num number;
729 x_item_key_count number;
730
731 BEGIN
732
733 po_debug.set_file_io(true);
734
735 x_WF_ItemKey := ' ';
736 x_WF_ItemKey_save := ' ';
737
738 -- Call WF purge API to remove any existing Confirm Receipts WF items
739 -- which are completed with an END_DATE less than or equal to
740 -- SYSDATE (default). This is to ensure that the Selection
741 -- process can start the workflow process for the same item key value.
742 --
743 -- The WF purge API will not remove any WF items which are still
744 -- opened with END_DATE = NULL;
745
746
747 WF_PURGE.total ('PORCPT');
748
749 -- Start the Confirm Receipts workflow Selection process
750
751 if x_allow_inv_dest_receipts is NULL then
752 x_allow_inv_dest_receipts := 'N';
753 end if;
754
755 -- Start the Confirm Receipts workflow Selection process
756
757 if x_allow_inv_dest_receipts = 'N' then
758
759 OPEN Porcpt_c for
760 SELECT so_header_ID,
761 requestor_ID,
762 expected_receipt_date,
763 requisition_line_ID,
764 requisition_header_ID
765 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
766 WHERE expected_receipt_date is not NULL
767 AND trunc(expected_receipt_date + 1) <= trunc(SYSDATE)
768 AND destination_type_code = 'EXPENSE'
769 AND requestor_ID is not NULL
770 AND expected_receipt_qty > 0
771 GROUP BY so_header_ID, requestor_ID, expected_receipt_date, requisition_line_ID, requisition_header_ID;
772
773 else
774
775 OPEN Porcpt_c for
776 SELECT so_header_ID,
777 requestor_ID,
778 expected_receipt_date,
779 requisition_line_ID,
780 requisition_header_ID
781 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
782 WHERE expected_receipt_date is not NULL
783 AND trunc(expected_receipt_date + 1) <= trunc(SYSDATE)
784 AND requestor_ID is not NULL
785 AND expected_receipt_qty > 0
786 GROUP BY so_header_ID, requestor_ID, expected_receipt_date, requisition_line_ID, requisition_header_ID;
787
788 end if; /** AllowInvDest Receipt Check */
789
790
791
792 LOOP
793
794 FETCH Porcpt_c into x_header_id,
795 x_requester_id,
796 x_exp_receipt_date,
797 x_requisition_line_ID,
798 x_requisition_header_ID;
799
800
801 -- Contruct Confirm Receipt workflow Item Key in the combination of:
802 -- PO_Header_ID + Requester_ID + Due_Date
803 --
804
805 EXIT WHEN Porcpt_c%NOTFOUND;
806
807 --ash_debug.debug('select order :in the loop',x_header_id || ':' || x_requisition_line_ID);
808
809 BEGIN
810 select PORL.auto_receive_flag
811 into x_auto_receive_flag
812 from PO_REQUISITION_LINES PORL
813 where PORL.requisition_line_id = x_requisition_line_ID;
814
815
816 EXCEPTION
817 when no_data_found then
818 x_auto_receive_flag := null;
819 END;
820
821 --ash_debug.debug('select order :auto rec flag',x_auto_receive_flag);
822
823
824 IF Porcpt_c%FOUND AND
825 (NVL(x_auto_receive_flag,'N') = 'Y') THEN
826
827 --ash_debug.debug('select order :auto rec flag 1',x_auto_receive_flag);
828
829 -- Process the auto receive shipments
830
831 Process_Auto_Receive_Internal(x_header_id,
832 x_requester_id,
833 x_exp_receipt_date);
834
835 ELSE
836
837 --ash_debug.debug('select order :auto rec else loop 2',x_auto_receive_flag);
838
839 x_WF_ItemKey := to_char(x_header_id) || ';' ||
840 to_char(x_requester_id) || ';' ||
841 to_char(x_exp_receipt_date,'DD-MON-YYYY');
842
843 IF Porcpt_c%FOUND AND
844 x_WF_ItemKey <> x_WF_ItemKey_save THEN
845
846 -- Check if there is any active workflow process running
847 -- for the so_header_id and requester_id combination
848 SELECT count(1) into x_item_key_count
849 FROM wf_items
850 WHERE item_type = 'PORCPT'
851 AND item_key like x_header_id||';'|| x_requester_id||';%'
852 AND END_DATE is null;
853
854 --ash_debug.debug('select order : value of x_WF_process_exists ','1');
855
856 IF x_item_key_count = 0 THEN
857 -- Workflow item does not exist
858 -- Invoke the Confirm Receive workflow starting procedure
859 -- for every unique workflow Item key.
860
861 -- ash_debug.debug('select order : Just before Start_Rcpt_Process','2');
862
863 PORCPTWF.Start_Rcpt_Process(x_header_id,
864 x_requester_id,
865 x_exp_receipt_date,
866 x_wf_itemkey,
867 x_revision_num,
868 'Y',
869 x_requisition_header_id);
870
871
872 COMMIT;
873
874 END IF;
875
876 -- Save the ItemKey for the next comparison
877 x_WF_ItemKey_Save := x_WF_ItemKey;
878
879 END IF;
880
881 END IF;
882
883
884 EXIT WHEN Porcpt_c%NOTFOUND;
885
886 END LOOP;
887
888 CLOSE porcpt_c;
889
890 EXCEPTION
891 WHEN NO_DATA_FOUND THEN
892 wf_core.context ('PORCPTWF','Select_Internal_Orders','No data found');
893 WHEN OTHERS THEN
894 wf_core.context ('PORCPTWF','Select_Internal_Orders','SQL error ' || sqlcode);
895 RAISE;
896
897
898 END Select_Internal_Orders;
899
900
901
902
903 /*===========================================================================
904 PROCEDURE NAME: Start_Rcpt_Process
905
906 DESCRIPTION: This procedure creates and starts the Confirm Receipt
907 workflow process.
908
909 CHANGE HISTORY: WLAU 1/15/1997 Created
910 ===========================================================================*/
911
912 PROCEDURE Start_Rcpt_Process (x_header_id IN NUMBER,
913 x_requester_id IN NUMBER,
914 x_exp_receipt_date IN DATE,
915 x_WF_ItemKey IN VARCHAR2,
916 x_revision_num IN NUMBER,
917 x_is_int_req IN VARCHAR2 default 'N',
918 x_req_header_id IN NUMBER default '-1',
919 x_po_num_rel_num IN VARCHAR2 default null) IS
920
921
922 l_ItemType VARCHAR2(100) := 'PORCPT';
923 l_ItemKey VARCHAR2(100) := x_WF_ItemKey;
924
925 x_requester_username WF_USERS.NAME%TYPE;
926 x_requester_disp_name WF_USERS.DISPLAY_NAME%TYPE;
927 x_buyer_username WF_USERS.NAME%TYPE;
928 x_buyer_disp_name WF_USERS.DISPLAY_NAME%TYPE;
929 x_buyer_id NUMBER := 0;
930 x_org_id NUMBER;
931
932 x_requester_current BOOLEAN ;
933 dummy VARCHAR2(1);
934 BEGIN
935
936 -- ash_debug.debug('Start_Rcpt_Process','0');
937
938
939
940 wf_engine.createProcess ( ItemType => l_ItemType,
941 ItemKey => l_ItemKey,
942 process => 'PO_CONFIRM_RECEIPT' );
943
944 -- ash_debug.debug('Start_Rcpt_Process','1');
945
946 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => l_ItemType,
947 itemkey => l_itemkey,
948 aname => 'WF_ITEM_KEY',
949 avalue => l_ItemKey);
950
951 x_org_id := findOrgId(x_header_id,x_is_int_req);
952
953 setOrgCtx(x_org_id);
954
955 -- ash_debug.debug('Start_Rcpt_Process org id',x_org_id);
956 -- ash_debug.debug('Start_Rcpt_Process int req',x_is_int_req);
957
958 if x_is_int_req = 'Y' then
959
960 wf_engine.SetItemAttrNumber ( itemtype => l_ItemType,
961 itemkey => l_itemkey,
962 aname => 'SO_HEADER_ID',
963 avalue => x_header_id );
964
965 wf_engine.SetItemAttrNumber ( itemtype => l_ItemType,
966 itemkey => l_itemkey,
967 aname => 'REQ_HEADER_ID',
968 avalue => x_req_header_id);
969
970 else --x_is_int_req = 'N'
971
972 wf_engine.SetItemAttrNumber (itemtype => l_ItemType,
973 itemkey => l_itemkey,
974 aname => 'PO_HEADER_ID',
975 avalue => x_header_id );
976
977 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => l_itemtype,
978 itemkey => l_itemkey,
979 aname => 'PO_NUM_REL_NUM',
980 avalue => x_po_num_rel_num );
981
982 end if;
983
984 wf_engine.SetItemAttrNumber ( itemtype => l_ItemType,
985 itemkey => l_itemkey,
986 aname => 'REQUESTER_ID',
987 avalue => x_requester_id );
988
989 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
990 itemkey => l_itemkey,
991 aname => 'IS_INT_REQ',
992 avalue => x_is_int_req );
993
994 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
995 itemkey => l_itemkey,
996 aname => 'ORG_ID',
997 avalue => x_org_id );
998
999
1000 wf_engine.SetItemAttrDate ( itemtype => l_ItemType,
1001 itemkey => l_itemkey,
1002 aname => 'DUE_DATE',
1003 avalue => x_exp_receipt_date );
1004
1005 -- ash_debug.debug('Start_Rcpt_Process int req','5');
1006
1007 wf_directory.GetUserName ( p_orig_system => 'PER',
1008 p_orig_system_id => x_requester_id,
1009 p_name => x_requester_username,
1010 p_display_name => x_requester_disp_name);
1011
1012 -- ash_debug.debug('Start_Rcpt_Process int req','6');
1013
1014 /* Bug 1490215
1015 * If the requester is not an employee then we will not have any
1016 * value in the x_requester_username. In this case the notification
1017 * can go to the buyer.
1018 */
1019
1020 /* Bug: 2820973 In addition to above we need to make sure by checking if
1021 the requester is an active person in HR table. If the requester is
1022 not an active employee then the notification should go to the buyer.
1023 */
1024
1025 Begin
1026
1027 Select 'X'
1028 Into dummy
1029 From per_workforce_current_x
1030 Where person_id = x_requester_id;
1031
1032 x_requester_current := TRUE;
1033
1034 Exception
1035 when no_data_found then
1036 x_requester_current := FALSE;
1037 End;
1038
1039 if x_is_int_req = 'N' then
1040
1041 if (x_requester_username is null) or (x_requester_current = FALSE) then
1042
1043 select agent_id
1044 into x_buyer_id
1045 from po_headers
1046 where po_header_id=x_header_id;
1047
1048 wf_directory.GetUserName ( p_orig_system => 'PER',
1049 p_orig_system_id => x_buyer_id,
1050 p_name => x_buyer_username,
1051 p_display_name => x_buyer_disp_name);
1052 x_requester_username := x_buyer_username;
1053 x_requester_disp_name := x_buyer_disp_name;
1054
1055 end if;
1056
1057 end if;
1058
1059
1060 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
1061 itemkey => l_itemkey,
1062 aname => 'REQUESTER_USERNAME',
1063 avalue => x_requester_username );
1064
1065 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
1066 itemkey => l_itemkey,
1067 aname => 'REQUESTER_DISP_NAME',
1068 avalue => x_requester_disp_name );
1069
1070 if x_is_int_req = 'N' then
1071
1072 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
1073 itemkey => l_itemkey,
1074 aname => 'PO_REVISION_NUM',
1075 avalue => x_revision_num );
1076 end if;
1077
1078 -- ash_debug.debug('Start_Rcpt_Process int req','7');
1079
1080 wf_engine.StartProcess ( ItemType => l_ItemType,
1081 ItemKey => l_ItemKey );
1082
1083 -- ash_debug.debug('Start_Rcpt_Process int req','8');
1084
1085
1086 EXCEPTION
1087
1088 WHEN NO_DATA_FOUND THEN
1089 wf_core.context ('PORCPTWF','Start_Rcpt_Process','No data found');
1090 WHEN OTHERS THEN
1091 wf_core.context ('PORCPTWF','Start_Rcpt_Process','SQL error ' || sqlcode);
1092 RAISE;
1093
1094
1095 END Start_Rcpt_Process;
1096
1097
1098
1099 /*===========================================================================
1100 PROCEDURE NAME: Get_Order_Info
1101
1102 DESCRIPTION: This procedure retrieves the purchase order and
1103 requisition information from the RCV_CONFIRM_RECEIPT_V
1104 table using the PO_HEADER_ID, EXPECTED_RECEIPT_DATE and
1105 REQUESTER_ID as search criteria. The retrieved data
1106 are used for sending the notification message which
1107 contains the order header and order line data.
1108
1109 CHANGE HISTORY: WLAU 1/15/1997 Created
1110 WLAU 4/22/1997 475711 - Changed Quantity_
1111 Received to Quantity_Delivered
1112 ===========================================================================*/
1113
1114 PROCEDURE Get_Order_Info ( itemtype in varchar2,
1115 itemkey in varchar2,
1116 actid in number,
1117 funmode in varchar2,
1118 result out NOCOPY varchar2 ) IS
1119
1120
1121 x_buyer_username WF_USERS.NAME%TYPE;
1122 x_buyer_disp_name WF_USERS.DISPLAY_NAME%TYPE;
1123 x_po_header_id NUMBER;
1124 x_requester_id NUMBER;
1125
1126 -- Header info
1127 --
1128 x_po_number VARCHAR2(20):= NULL;
1129 x_supplier_name PO_VENDORS.VENDOR_NAME%TYPE := NULL;
1130 x_exp_receipt_date DATE;
1131 x_note_to_receiver PO_HEADERS_ALL.NOTE_TO_RECEIVER%TYPE :=NULL;
1132 x_buyer_id NUMBER := 0;
1133
1134 -- line info
1135 --
1136 x_line_disp NUMBER := 0;
1137 x_total_lines NUMBER := 0;
1138
1139 x_qty_rcv_text VARCHAR2(250):= 'Quantity to be received: ';
1140 x_qty_rcvd_todate_text VARCHAR2(250):= 'Quantity received to date: ';
1141 x_on_req_text VARCHAR2(250):= 'on Requisition';
1142
1143 x_revision_num number;
1144 x_org_id number;
1145
1146 x_allow_inv_dest_receipts Varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
1147
1148
1149 BEGIN
1150
1151
1152 IF ( funmode = 'RUN' ) THEN
1153 --
1154 --
1155 x_po_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1156 itemkey => itemkey,
1157 aname => 'PO_HEADER_ID');
1158
1159 x_org_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1160 itemkey => itemkey,
1161 aname => 'ORG_ID');
1162 setOrgCtx(x_org_id);
1163
1164
1165 x_requester_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1166 itemkey => itemkey,
1167 aname => 'REQUESTER_ID');
1168
1169 x_exp_receipt_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
1170 itemkey => itemkey,
1171 aname => 'DUE_DATE');
1172
1173
1174 -- Retrieve the purchase order header info.
1175
1176 SELECT poh.segment1,
1177 pov.vendor_name,
1178 poh.agent_id,
1179 poh.note_to_receiver
1180 INTO x_po_number,
1181 x_supplier_name,
1182 x_buyer_id,
1183 x_note_to_receiver
1184 FROM PO_HEADERS poh,
1185 PO_VENDORS pov
1186 WHERE po_header_id = x_po_header_id
1187 AND poh.vendor_id = pov.vendor_id (+);
1188
1189
1190
1191
1192 -- Retrieve buyer username and display name.
1193 -- Store them to the workflow process
1194
1195 wf_engine.SetItemAttrText ( itemtype => Itemtype,
1196 itemkey => Itemkey,
1197 aname => 'PO_NUMBER',
1198 avalue => x_po_number );
1199
1200 wf_engine.SetItemAttrNumber ( itemtype => ItemType,
1201 itemkey => Itemkey,
1202 aname => 'BUYER_ID',
1203 avalue => x_buyer_id );
1204
1205 wf_directory.GetUserName ( p_orig_system => 'PER',
1206 p_orig_system_id => x_buyer_id,
1207 p_name => x_buyer_username,
1208 p_display_name => x_buyer_disp_name);
1209
1210 wf_engine.SetItemAttrText ( itemtype => Itemtype,
1211 itemkey => Itemkey,
1212 aname => 'BUYER_USERNAME',
1213 avalue => x_buyer_username );
1214
1215 wf_engine.SetItemAttrText ( itemtype => Itemtype,
1216 itemkey => Itemkey,
1217 aname => 'BUYER_DISP_NAME',
1218 avalue => x_buyer_disp_name );
1219
1220
1221 wf_engine.SetItemAttrText ( itemtype => Itemtype,
1222 itemkey => Itemkey,
1223 aname => 'SUPPLIER_DISP_NAME',
1224 avalue => x_supplier_name );
1225
1226
1227 wf_engine.SetItemAttrText ( itemtype => Itemtype,
1228 itemkey => Itemkey,
1229 aname => 'NOTE_TO_RECEIVER',
1230 avalue => x_note_to_receiver );
1231
1232 -- bug 471462
1233 -- Retrieve translatable text to be constructed in the notifiction body.
1234 --
1235
1236 fnd_message.set_name ('PO','RCV_RCPT_QTY_RCV');
1237 x_qty_rcv_text := ' ' || fnd_message.get;
1238
1239 fnd_message.set_name ('PO','RCV_RCPT_QTY_RCVD_TODATE');
1240 x_qty_rcvd_todate_text := ' ' || fnd_message.get;
1241
1242 fnd_message.set_name ('PO','RCV_RCPT_ON_REQ');
1243 x_on_req_text := ' ' || fnd_message.get;
1244
1245 -- <R12 Confirm Receipt and JRAD Notificaion>
1246 -- Inovking the call to populate_order_info() procedure (new)
1247 -- instead of executing the code below.
1248 -- The procedure gets the list of distributions match
1249 -- either the need by date or invoice match criteria
1250 -- and populates the respective item attributes
1251
1252 x_total_lines := populate_order_info(itemtype, itemKey);
1253
1254
1255 ELSIF ( funmode = 'CANCEL' ) THEN
1256 --
1257 NULL;
1258 --
1259 END if;
1260
1261
1262
1263
1264 EXCEPTION
1265 WHEN NO_DATA_FOUND THEN
1266
1267 wf_core.context ('PORCPTWF','Get_Order_Info','No data found');
1268
1269 WHEN OTHERS THEN
1270
1271 wf_core.context ('PORCPTWF','Get_Order_Info','SQL error ' || sqlcode);
1272
1273 RAISE;
1274
1275
1276 END Get_Order_Info;
1277
1278
1279
1280 PROCEDURE Is_Internal_Requisition( itemtype in varchar2,
1281 itemkey in varchar2,
1282 actid in number,
1283 funmode in varchar2,
1284 result out NOCOPY varchar2 ) IS
1285
1286 x_is_int_req VARCHAR2(1);
1287
1288
1289 begin
1290
1291 --ash_debug.debug('Is_Internal_Req','1');
1292
1293 x_is_int_req := wf_engine.GetItemAttrText(itemtype,itemkey,'IS_INT_REQ');
1294 -- ash_debug.debug('Is_Internal_Req value of int req ',x_is_int_req);
1295
1296
1297 if x_is_int_req = 'Y' then
1298 -- ash_debug.debug('Is_Internal_Req value of int req 1',x_is_int_req);
1299 result := 'COMPLETE:Y';
1300 return;
1301 else
1302 -- ash_debug.debug('Is_Internal_Req value of int req 2',x_is_int_req);
1303
1304 result := 'COMPLETE:N';
1305 return;
1306 end if;
1307
1308 END Is_Internal_Requisition;
1309
1310
1311
1312 PROCEDURE Get_Internal_Order_Info( itemtype in varchar2,
1313 itemkey in varchar2,
1314 actid in number,
1315 funmode in varchar2,
1316 result out NOCOPY varchar2 ) IS
1317
1318
1319 x_requester_id NUMBER;
1320 x_org_id NUMBER;
1321
1322 -- Header info
1323 x_so_number VARCHAR2(20):= NULL;
1324 x_so_header_id NUMBER;
1325 x_exp_receipt_date DATE;
1326 x_note_to_receiver PO_HEADERS_ALL.NOTE_TO_RECEIVER%TYPE :=NULL;
1327
1328 -- line info
1329 x_line_disp NUMBER := 0;
1330 x_total_lines NUMBER := 0;
1331
1332 x_qty_rcv_text VARCHAR2(250):= 'Quantity to be received: ';
1333 x_qty_rcvd_todate_text VARCHAR2(250):= 'Quantity received to date: ';
1334 x_on_req_text VARCHAR2(250):= 'on Requisition';
1335
1336
1337 x_revision_num number;
1338
1339
1340 x_allow_inv_dest_receipts Varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
1341
1342
1343 BEGIN
1344
1345
1346 IF ( funmode = 'RUN' ) THEN
1347 --
1348 --
1349 x_so_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1350 itemkey => itemkey,
1351 aname => 'SO_HEADER_ID');
1352
1353 x_org_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1354 itemkey => itemkey,
1355 aname => 'ORG_ID');
1356 setOrgCtx(x_org_id);
1357
1358 x_requester_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1359 itemkey => itemkey,
1360 aname => 'REQUESTER_ID');
1361
1362 x_exp_receipt_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
1363 itemkey => itemkey,
1364 aname => 'DUE_DATE');
1365
1366
1367 select ORDER_NUMBER
1368 into x_so_number
1369 from oe_order_headers_all osh
1370 where osh.HEADER_ID = x_so_header_id;
1371
1372
1373 -- ash_debug.debug('get_internal_order order_number',x_so_number);
1374
1375
1376 -- Retrieve buyer username and display name.
1377 -- Store them to the workflow process
1378
1379 wf_engine.SetItemAttrText ( itemtype => Itemtype,
1380 itemkey => Itemkey,
1381 aname => 'SO_NUMBER',
1382 avalue => x_so_number );
1383
1384 fnd_message.set_name ('PO','RCV_RCPT_QTY_RCV');
1385 x_qty_rcv_text := ' ' || fnd_message.get;
1386
1387 fnd_message.set_name ('PO','RCV_RCPT_QTY_RCVD_TODATE');
1388 x_qty_rcvd_todate_text := ' ' || fnd_message.get;
1389
1390 fnd_message.set_name ('PO','RCV_RCPT_ON_REQ');
1391 x_on_req_text := ' ' || fnd_message.get;
1392
1393
1394
1395
1396 ELSIF ( funmode = 'CANCEL' ) THEN
1397 --
1398 NULL;
1399 --
1400 END if;
1401
1402
1403
1404
1405 EXCEPTION
1406 WHEN NO_DATA_FOUND THEN
1407
1408 wf_core.context ('PORCPTWF','Get_Internal_Order_Info','No data found');
1409
1410 WHEN OTHERS THEN
1411
1412 wf_core.context ('PORCPTWF','Get_Internal_Order_Info','SQL error ' || sqlcode);
1413
1414 RAISE;
1415
1416
1417 END Get_Internal_Order_Info;
1418
1419
1420
1421 /*===========================================================================
1422 PROCEDURE NAME: Get_Rcv_Order_URL
1423
1424 DESCRIPTION: This procedure retrieves the URL of the Receive Orders
1425 web page. The URL provides a direct link from the
1426 workflow notification message to the Web Application
1427 Receive Orders web page for processing 'Partial/Over'
1428 receipt functions.
1429
1430 CHANGE HISTORY: WLAU 1/15/1997 Created
1431 ===========================================================================*/
1432
1433
1434 PROCEDURE Get_Rcv_Order_URL ( itemtype in varchar2,
1435 itemkey in varchar2,
1436 actid in number,
1437 funmode in varchar2,
1438 result out NOCOPY varchar2 ) IS
1439
1440
1441 x_requester_ID NUMBER;
1442 x_po_header_ID NUMBER;
1443 x_exp_receipt_date DATE;
1444 x_Rcv_Order_URL VARCHAR2(1000);
1445 x_org_id NUMBER;
1446 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
1447
1448 BEGIN
1449
1450 IF ( funmode = 'RUN' ) THEN
1451 --
1452 x_requester_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1453 itemkey => itemkey,
1454 aname => 'REQUESTER_ID');
1455
1456 x_po_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1457 itemkey => itemkey,
1458 aname => 'PO_HEADER_ID');
1459
1460 x_org_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1461 itemkey => itemkey,
1462 aname => 'ORG_ID');
1463
1464 setOrgCtx(x_org_id);
1465
1466 x_exp_receipt_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
1467 itemkey => itemkey,
1468 aname => 'DUE_DATE');
1469
1470
1471 x_Rcv_Order_url := l_base_href || '/OA_HTML/OA.jsp?OAFunc=ICX_POR_LAUNCH_IP' || '&' || 'porOrderHeaderId=' ||to_char(x_po_header_id) || '&' || 'porMode=confirmReceipt' ;
1472
1473 IF (x_requester_id is not null) THEN
1474 x_Rcv_Order_url := x_Rcv_Order_url || '&' || 'porRequesterId=' ||to_char(x_requester_id) || '&';
1475 END IF;
1476
1477 IF (x_exp_receipt_date is not null) THEN
1478 x_Rcv_Order_url := x_Rcv_Order_url || 'porExpectedDate=' || to_char(x_exp_receipt_date,'DD-MON-YYYY') || '&';
1479 END IF;
1480
1481 x_Rcv_Order_url := x_Rcv_Order_url || 'porOrderTypeCode=PO' || '&'
1482 || 'porDestOrgId=' || to_char(x_org_id)||'&'||'NtfId=-'||'&'||'#NID-';
1483
1484 --
1485 wf_engine.SetItemAttrText ( itemtype => itemtype,
1486 itemkey => itemkey,
1487 aname => 'RCV_ORDERS_URL',
1488 avalue => x_Rcv_Order_URL );
1489 --
1490 ELSIF ( funmode = 'CANCEL' ) THEN
1491 --
1492 null;
1493 --
1494 END IF;
1495
1496
1497 EXCEPTION
1498 WHEN NO_DATA_FOUND THEN
1499 wf_core.context ('PORCPTWF','Get_Rcv_Order_URL','No data found');
1500
1501 WHEN OTHERS THEN
1502 wf_core.context ('PORCPTWF','Get_Rcv_Order_URL','SQL error ' || sqlcode);
1503
1504 RAISE;
1505
1506
1507 END Get_Rcv_Order_URL;
1508
1509 PROCEDURE Get_Rcv_Int_Order_URL ( itemtype in varchar2,
1510 itemkey in varchar2,
1511 actid in number,
1512 funmode in varchar2,
1513 result out NOCOPY varchar2 ) IS
1514
1515 x_req_header_ID NUMBER;
1516 x_requester_ID NUMBER;
1517 x_so_header_ID NUMBER;
1518 x_exp_receipt_date DATE;
1519 x_Rcv_Order_URL VARCHAR2(1000);
1520 x_org_id NUMBER;
1521 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
1522
1523 BEGIN
1524
1525 IF ( funmode = 'RUN' ) THEN
1526 --
1527 x_requester_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1528 itemkey => itemkey,
1529 aname => 'REQUESTER_ID');
1530
1531 x_so_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1532 itemkey => itemkey,
1533 aname => 'SO_HEADER_ID');
1534
1535 x_req_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1536 itemkey => itemkey,
1537 aname => 'REQ_HEADER_ID');
1538
1539 x_org_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1540 itemkey => itemkey,
1541 aname => 'ORG_ID');
1542
1543
1544 -- Setup the organization context for the multi-org environment
1545
1546 setOrgCtx(x_org_id);
1547
1548 x_exp_receipt_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
1549 itemkey => itemkey,
1550 aname => 'DUE_DATE');
1551
1552 x_Rcv_Order_url := l_base_href || '/OA_HTML/OA.jsp?OAFunc=ICX_POR_LAUNCH_IP' || '&' || 'porOrderHeaderId=' || to_char(x_so_header_id) || '&' || 'porMode=confirmReceipt' ;
1553
1554 IF (x_requester_id is not null) THEN
1555 x_Rcv_Order_url := x_Rcv_Order_url || '&' || 'porRequesterId=' ||to_char(x_requester_id) || '&';
1556 END IF;
1557
1558 IF (x_exp_receipt_date is not null) THEN
1559 x_Rcv_Order_url := x_Rcv_Order_url || 'porExpectedDate=' || to_char(x_exp_receipt_date,'DD-MON-YYYY') || '&';
1560 END IF;
1561
1562 x_Rcv_Order_url := x_Rcv_Order_url || 'porOrderTypeCode=REQ' || '&'
1563 || 'porDestOrgId=' || to_char(x_org_id)||'&'||'NtfId=-'||'&'||'#NID-';
1564
1565 -- ash_debug.debug('Get_Rcv_Int_Order_URL org id',x_org_id);
1566 -- ash_debug.debug('Get_Rcv_Int_Order_URL org id',x_Rcv_Order_url);
1567 --
1568 wf_engine.SetItemAttrText ( itemtype => itemtype,
1569 itemkey => itemkey,
1570 aname => 'RCV_ORDERS_URL',
1571 avalue => x_Rcv_Order_URL );
1572 --
1573 ELSIF ( funmode = 'CANCEL' ) THEN
1574 --
1575 null;
1576 --
1577 END IF;
1578
1579
1580 EXCEPTION
1581 WHEN NO_DATA_FOUND THEN
1582 wf_core.context ('PORCPTWF','Get_Rcv_Int_Order_URL','No data found');
1583
1584 WHEN OTHERS THEN
1585 wf_core.context ('PORCPTWF','Get_Rcv_Int_Order_URL','SQL error ' || sqlcode);
1586
1587 RAISE;
1588
1589
1590 END Get_Rcv_Int_Order_URL;
1591
1592
1593 PROCEDURE initialize(x_requester_username in VARCHAR2, x_org_id IN NUMBER) IS
1594
1595 x_resp_id NUMBER := -1;
1596 x_user_id NUMBER := -1;
1597 x_resp_appl_id NUMBER := -1;
1598 /* Bug 6277620 - FP of 6054138 */
1599 x_ip_resp_appl_id NUMBER := 178;
1600 x_po_resp_appl_id NUMBER := 201;
1601
1602 BEGIN
1603 begin
1604 /* Changed the method signature to accept the requester username
1605 instead of the employee id. The requester username is set to the buyer's
1606 username if the requester is not a user. Otherwise, it is set to the requester's
1607 username.
1608 */
1609 SELECT FND.user_id
1610 INTO x_user_id
1611 FROM FND_USER FND
1612 WHERE FND.USER_NAME = x_requester_username
1613 AND FND.START_DATE < sysdate
1614 AND nvl(FND.END_DATE, sysdate + 1) >= sysdate
1615 AND ROWNUM = 1;
1616 EXCEPTION
1617 WHEN OTHERS THEN
1618 x_user_id := -1;
1619 END;
1620
1621 BEGIN
1622 select MIN(fr.responsibility_id)
1623 into x_resp_id
1624 from fnd_user_resp_groups fur,
1625 fnd_responsibility_vl fr,
1626 financials_system_parameters fsp
1627 where fur.user_id = x_user_id
1628 and fur.responsibility_application_id in (x_ip_resp_appl_id, x_po_resp_appl_id)
1629 and fur.responsibility_id = fr.responsibility_id
1630 and fr.start_date < sysdate
1631 and nvl(fr.end_date, sysdate +1) >= sysdate
1632 and fur.start_date < sysdate
1633 and nvl(fur.end_date, sysdate +1) >= Sysdate
1634 AND nvl(fnd_profile.value_specific('ORG_ID', NULL,
1635 fr.responsibility_id, fur.responsibility_application_id),-1) = nvl(x_org_id,-1)
1636 and nvl(fsp.org_id,-1) = nvl(x_org_id,-1)
1637 and nvl(fsp.business_group_id,-1) =
1638 nvl(fnd_profile.value_specific('PER_BUSINESS_GROUP_ID', NULL,
1639 fr.responsibility_id, fur.responsibility_application_id),-1);
1640 EXCEPTION
1641 when others then
1642 x_resp_id := -1;
1643 END;
1644 /* Bug 6277620- FP of 6054138 - Select the ip/po responsibility first and if not found then look for custom responsibilities*/
1645 if (x_resp_id = -1) THEN
1646 BEGIN
1647 select MIN(fr.responsibility_id)
1648 into x_resp_id
1649 from fnd_user_resp_groups fur,
1650 fnd_responsibility_vl fr,
1651 financials_system_parameters fsp
1652 where fur.user_id = x_user_id
1653 and fur.responsibility_id = fr.responsibility_id
1654 and fr.start_date < sysdate
1655 and nvl(fr.end_date, sysdate +1) >= sysdate
1656 and fur.start_date < sysdate
1657 and nvl(fur.end_date, sysdate +1) >= Sysdate
1658 AND nvl(fnd_profile.value_specific('ORG_ID', NULL,
1659 fr.responsibility_id, fur.responsibility_application_id),-1) = nvl(x_org_id,-1)
1660 and nvl(fsp.org_id,-1) = nvl(x_org_id,-1)
1661 and nvl(fsp.business_group_id,-1) =
1662 nvl(fnd_profile.value_specific('PER_BUSINESS_GROUP_ID', NULL,
1663 fr.responsibility_id, fur.responsibility_application_id),-1);
1664 EXCEPTION
1665 when others then
1666 x_resp_id := -1;
1667 END;
1668 END IF;
1669
1670 if(x_resp_id <> -1) then
1671 BEGIN
1672 SELECT MIN(responsibility_application_id)
1673 INTO x_resp_appl_id
1674 FROM fnd_user_resp_groups fur,
1675 fnd_responsibility_vl fr,
1676 financials_system_parameters fsp
1677 WHERE
1678 fur.responsibility_id = fr.responsibility_id and
1679 fr.responsibility_id = x_resp_id and
1680 fur.user_id = x_user_id and
1681 fr.start_date < sysdate and
1682 nvl(fr.end_date, sysdate +1) >= sysdate and
1683 fur.start_date < sysdate and
1684 nvl(fur.end_date, sysdate +1) >= Sysdate AND
1685 nvl(fnd_profile.value_specific('ORG_ID', NULL, fr.responsibility_id,
1686 fur.responsibility_application_id),-1) = nvl(x_org_id,-1) and
1687 nvl(fsp.org_id,-1) = nvl(x_org_id,-1) and
1688 nvl(fsp.business_group_id,-1) =
1689 nvl(fnd_profile.value_specific('PER_BUSINESS_GROUP_ID', NULL,
1690 fr.responsibility_id, fur.responsibility_application_id),-1);
1691 EXCEPTION
1692 WHEN OTHERS THEN
1693 x_resp_appl_id := -1;
1694 END;
1695 end if;
1696
1697 FND_GLOBAL.APPS_INITIALIZE(x_user_id,x_resp_id,x_resp_appl_id);
1698
1699
1700 END initialize;
1701
1702
1703
1704 /*===========================================================================
1705 PROCEDURE NAME: Process_Rcv_Trans
1706
1707 DESCRIPTION: This procedure processes the Receiving Transaction
1708 interface when the workflow notification is reponsed
1709 as 'Fully Received'.
1710
1711 It checks to ensure that the shipment(s) is/are still
1712 opened. It invokes the Receiving Transaction interface
1713 procedure to insert the receipt records into the
1714 receiving transaction interface table.
1715
1716 The Receiving Transaction Manager is then called in
1717 'ON-LINE' mode to process the receipt records immediately.
1718
1719 If there are errors returned from the Receiving
1720 Transaction Manager, the error status is set the
1721 workflow item attribute for notifying the buyer and
1722 requester of the error.
1723
1724
1725 CHANGE HISTORY: WLAU 1/15/1997 Created
1726 ===========================================================================*/
1727
1728
1729 PROCEDURE Process_Rcv_Trans ( itemtype in varchar2,
1730 itemkey in varchar2,
1731 actid in number,
1732 funmode in varchar2,
1733 result out NOCOPY varchar2 ) IS
1734 TYPE shipment_orders_cursor IS ref CURSOR;
1735 Porcpt_Shipment shipment_orders_cursor;
1736
1737 x_group_id NUMBER;
1738 x_inserted_txn BOOLEAN;
1739 x_insert_txns_count NUMBER := 0;
1740 x_RCV_txns_rc NUMBER := 0;
1741 x_exp_receipt_date DATE;
1742 x_po_header_id NUMBER;
1743 x_requester_id NUMBER;
1744
1745 x_org_id NUMBER;
1746 x_requester_username WF_USERS.NAME%TYPE; --Using the username in the initialize api
1747 x_rcv_trans_status VARCHAR2(500) := NULL;
1748 X_tmp_count NUMBER;
1749 X_tmp_count1 NUMBER;
1750 X_tmp_approve VARCHAR2(20);
1751 x_allow_inv_dest_receipts Varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
1752 x_insert_txns_status NUMBER;
1753
1754 t_po_header_id rcvNumberArray;
1755 t_line_location_id rcvNumberArray;
1756 t_expected_receipt_qty rcvNumberArray;
1757 t_ordered_uom rcvVarcharArray;
1758 t_item_id rcvNumberArray;
1759 t_primary_uom_class rcvVarcharArray;
1760 t_org_id rcvNumberArray;
1761 t_po_distribution_id rcvNumberArray;
1762 t_Comments rcvVarcharArray;
1763 t_PackingSlip rcvVarcharArray;
1764 t_WayBillNum rcvVarcharArray;
1765
1766 x_progress varchar2(1000):= '001';
1767 x_ntf_trigerred_date varchar2(40);
1768 x_message_token VARCHAR2(2000);
1769
1770 BEGIN
1771
1772 IF ( funmode = 'RUN' ) THEN
1773 --
1774 x_po_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1775 itemkey => itemkey,
1776 aname => 'PO_HEADER_ID');
1777
1778 -- Setup the organization context for the multi-org environment
1779
1780 x_org_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1781 itemkey => itemkey,
1782 aname => 'ORG_ID');
1783
1784 -- Setup the organization context for the multi-org environment
1785
1786 setOrgCtx(x_org_id);
1787
1788 --Use the requester username to be passed to the initalize api
1789 x_requester_username := wf_engine.GetItemAttrText ( itemtype => itemtype,
1790 itemkey => itemkey,
1791 aname => 'REQUESTER_USERNAME');
1792 initialize(x_requester_username,x_org_id);
1793
1794 /** rewrite after initialize **/
1795 x_allow_inv_dest_receipts := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
1796
1797 x_requester_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1798 itemkey => itemkey,
1799 aname => 'REQUESTER_ID');
1800
1801
1802 x_exp_receipt_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
1803 itemkey => itemkey,
1804 aname => 'DUE_DATE');
1805
1806 -- <R12 Confirm Receipt and JRAD Notificaion>
1807 -- To retrieve notification triggered date
1808 x_ntf_trigerred_date := PO_WF_UTIL_PKG.GetItemAttrText(
1809 itemtype => itemtype,
1810 itemkey => itemkey,
1811 aname => 'NTF_TRIGGERED_DATE');
1812
1813 SELECT rcv_interface_groups_s.nextval
1814 INTO x_group_id
1815 FROM sys.dual;
1816
1817
1818 if x_allow_inv_dest_receipts is NULL then
1819 x_allow_inv_dest_receipts := 'N';
1820 end if;
1821
1822 if x_allow_inv_dest_receipts = 'N' then
1823
1824 OPEN Porcpt_Shipment for
1825 SELECT po_header_id,
1826 po_line_location_id,
1827 expected_receipt_qty,
1828 primary_uom,
1829 item_id,
1830 primary_uom_class,
1831 to_organization_id,
1832 po_distribution_id,
1833 null,
1834 null,
1835 null
1836 FROM POR_RCV_ALL_ITEMS_V1 rcv
1837 WHERE ((expected_receipt_date is not NULL
1838 AND trunc(to_date(to_char(expected_receipt_date+1), 'DD/MM/YYYY'))<=
1839 trunc(to_date(x_ntf_trigerred_date, 'DD/MM/YYYY')))
1840 OR EXISTS (SELECT 1 FROM ap_holds aph
1841 WHERE aph.line_location_id = rcv.po_line_location_id
1842 AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
1843 AND aph.release_lookup_code IS NULL
1844 AND rcv.quantity_invoiced > quantity_delivered
1845 AND rcv.quantity_invoiced <= ordered_qty ))
1846 AND NVL(receipt_required_flag,'N') = 'Y'
1847 AND destination_type_code = 'EXPENSE'
1848 AND requestor_id is not NULL
1849 AND expected_receipt_qty > 0
1850 AND po_header_ID = x_po_header_ID
1851 AND requestor_ID = x_requester_ID;
1852 else
1853 OPEN Porcpt_Shipment for
1854 SELECT po_header_id,
1855 po_line_location_id,
1856 expected_receipt_qty,
1857 primary_uom,
1858 item_id,
1859 primary_uom_class,
1860 to_organization_id,
1861 po_distribution_id,
1862 null,
1863 null,
1864 null
1865 FROM POR_RCV_ALL_ITEMS_V1 rcv
1866 WHERE ((expected_receipt_date is not NULL
1867 AND trunc(to_date(to_char(expected_receipt_date+1), 'DD/MM/YYYY'))<=
1868 trunc(to_date(x_ntf_trigerred_date, 'DD/MM/YYYY')))
1869 OR EXISTS (SELECT 1 FROM ap_holds aph
1870 WHERE aph.line_location_id = rcv.po_line_location_id
1871 AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
1872 AND aph.release_lookup_code IS NULL
1873 AND rcv.quantity_invoiced > quantity_delivered
1874 AND rcv.quantity_invoiced <= ordered_qty ))
1875 AND NVL(receipt_required_flag,'N') = 'Y'
1876 AND requestor_id is not NULL
1877 AND expected_receipt_qty > 0
1878 AND po_header_ID = x_po_header_ID
1879 AND requestor_ID = x_requester_ID;
1880
1881 end if; /** AllowInvDest Receipt Check **/
1882
1883 FETCH porcpt_Shipment BULK COLLECT into t_po_header_id,
1884 t_line_location_id,
1885 t_expected_receipt_qty,
1886 t_ordered_uom,
1887 t_item_id,
1888 t_primary_uom_class,
1889 t_org_id,
1890 t_po_distribution_id,
1891 t_Comments,
1892 t_PackingSlip,
1893 t_WayBillNum;
1894
1895 CLOSE Porcpt_Shipment;
1896
1897 for i in 1..t_po_header_id.count loop
1898 x_progress := 'poheaderid*' || to_char(t_po_header_id(i)) || '*t_line_location_id*' || to_char(t_line_location_id(i)) || '*ex_rcpt_qty*' || t_expected_receipt_qty(i) || '*uom*' || t_ordered_uom(i)
1899 || '*itemid*' || to_char(t_item_id(i)) || '*uom_class*' || t_primary_uom_class(i) || '*org_id*' || to_char(t_org_id(i)) || '*t_po_distribution_id*' || to_char(t_po_distribution_id(i))
1900 || '*comments*' || t_comments(i) || '*pkgSlip*' || t_packingSlip(i) || '*waybillnum*' || t_waybillNum(i);
1901
1902 x_progress := x_progress || '*x_group_id*' || to_char(x_group_id);
1903
1904 IF (g_po_wf_debug = 'Y') THEN
1905 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1906 END IF;
1907
1908 end loop;
1909
1910
1911 x_insert_txns_status := POR_RCV_ORD_SV.groupPoTransaction( t_po_header_id,
1912 t_line_location_id,
1913 t_expected_receipt_qty,
1914 t_ordered_uom,
1915 SYSDATE,
1916 t_item_id,
1917 t_primary_uom_class,
1918 t_org_id,
1919 t_po_distribution_id,
1920 x_group_id,
1921 'WP4_CONFIRM',
1922 t_Comments,
1923 t_PackingSlip,
1924 t_WayBillNum);
1925
1926 IF x_insert_txns_status = 0 THEN
1927 x_RCV_txns_rc := por_rcv_ord_sv.process_transactions(X_group_id, 'WF');
1928
1929 -- At least one of the receiving transactions inserted
1930
1931 IF x_RCV_txns_rc is NULL OR
1932 x_RCV_txns_rc = 0 THEN
1933
1934 -- Clean the po distribtions table
1935 update po_distributions
1936 set wf_item_key = ''
1937 where po_header_id = x_po_header_ID
1938 and wf_item_key like x_po_header_ID||';'||x_requester_id||';%';
1939
1940 RESULT := 'PASSED';
1941
1942 ELSE
1943 get_txn_error_message(x_group_id, x_RCV_txns_rc, x_rcv_trans_status, x_message_token);
1944
1945 wf_engine.SetItemAttrText ( itemtype => Itemtype,
1946 itemkey => Itemkey,
1947 aname => 'RCV_TRANS_STATUS',
1948 avalue => x_rcv_trans_status );
1949
1950 wf_engine.SetItemAttrText ( itemtype => Itemtype,
1951 itemkey => Itemkey,
1952 aname => 'RCV_ERR_MSG_TOKEN',
1953 avalue => x_message_token );
1954
1955 RESULT := 'FAILED';
1956
1957 END IF;
1958
1959
1960 ELSE
1961
1962 -- 513490 change where condition to expected_receipt_qty = 0 to
1963 -- see if the shipment has already been fully received
1964
1965 -- will come down here only if either all shipments are fully received
1966 -- or some shipments are fully received while the rest of shipment
1967 -- require reapproval
1968
1969 /* bug 601806 */
1970
1971 if (x_allow_inv_dest_receipts = 'N') then
1972 SELECT count(*)
1973 INTO x_tmp_count
1974 FROM POR_RCV_ALL_ITEMS_V1
1975 WHERE expected_receipt_date is not NULL
1976 AND NVL(receipt_required_flag,'N') = 'Y'
1977 AND destination_type_code = 'EXPENSE'
1978 AND requestor_id is not NULL
1979 AND po_header_ID = x_po_header_ID;
1980
1981 SELECT count(*)
1982 INTO x_tmp_count1
1983 FROM POR_RCV_ALL_ITEMS_V1 rcv
1984 WHERE ((expected_receipt_date is not NULL
1985 AND trunc(to_date(to_char(expected_receipt_date+1), 'DD/MM/YYYY'))<=
1986 trunc(to_date(x_ntf_trigerred_date, 'DD/MM/YYYY')))
1987 OR EXISTS (SELECT 1 FROM ap_holds aph
1988 WHERE aph.line_location_id = rcv.po_line_location_id
1989 AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
1990 AND aph.release_lookup_code IS NULL
1991 AND rcv.quantity_invoiced > quantity_delivered
1992 AND rcv.quantity_invoiced <= ordered_qty ))
1993 AND NVL(receipt_required_flag,'N') = 'Y'
1994 AND destination_type_code = 'EXPENSE'
1995 AND requestor_id is not NULL
1996 AND expected_receipt_qty = 0
1997 AND po_header_ID = x_po_header_ID
1998 AND requestor_ID = x_requester_ID;
1999 else
2000 SELECT count(*)
2001 INTO x_tmp_count
2002 FROM POR_RCV_ALL_ITEMS_V1
2003 WHERE expected_receipt_date is not NULL
2004 AND NVL(receipt_required_flag,'N') = 'Y'
2005 AND requestor_id is not NULL
2006 AND po_header_ID = x_po_header_ID;
2007
2008 SELECT count(*)
2009 INTO x_tmp_count1
2010 FROM POR_RCV_ALL_ITEMS_V1 rcv
2011 WHERE ((expected_receipt_date is not NULL
2012 AND trunc(to_date(to_char(expected_receipt_date+1), 'DD/MM/YYYY'))<=
2013 trunc(to_date(x_ntf_trigerred_date, 'DD/MM/YYYY')))
2014 OR EXISTS (SELECT 1 FROM ap_holds aph
2015 WHERE aph.line_location_id = rcv.po_line_location_id
2016 AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
2017 AND aph.release_lookup_code IS NULL
2018 AND rcv.quantity_invoiced > quantity_delivered
2019 AND rcv.quantity_invoiced <= ordered_qty ))
2020 AND NVL(receipt_required_flag,'N') = 'Y'
2021 AND requestor_id is not NULL
2022 AND expected_receipt_qty = 0
2023 AND po_header_ID = x_po_header_ID
2024 AND requestor_ID = x_requester_ID;
2025 end if;
2026
2027 IF (x_tmp_count1 > 0) THEN
2028 -- will come down here if all the eligible shipments
2029 -- have been fully received order has already been received
2030 x_rcv_trans_status := 'RCV_RCPT_ORDER_RECEIVED';
2031
2032 ELSIF (x_tmp_count = 0) THEN
2033 -- if it doesn't satify four basic criteria
2034 -- 1. make the RCV_CONFIRM_RECEIPT_V
2035 -- 2. receipt_required
2036 -- 3. destination_type_code = 'EXPENSE'
2037 -- 4. expected_receipt_date and requestor_id not NULL
2038 -- then, it doesn't qualify for confirm receipt
2039 x_rcv_trans_status := 'RCV_RCPT_APPROVAL_FAILED';
2040
2041 ELSIF (x_tmp_count > 0) THEN
2042 -- either the requestor or the expected_receipt_date has changed
2043 x_rcv_trans_status := 'RCV_RCPT_RQTR_DATE_CHANGED';
2044
2045 ELSE
2046 -- Insert to Receiving Transaction Interface failed
2047 x_rcv_trans_status := 'RCV_RCPT_INSERT_FAILED';
2048 END IF;
2049
2050 wf_engine.SetItemAttrText ( itemtype => Itemtype,
2051 itemkey => Itemkey,
2052 aname => 'RCV_TRANS_STATUS',
2053 avalue => x_rcv_trans_status );
2054
2055 RESULT :='FAILED';
2056
2057 END IF;
2058
2059 ELSIF ( funmode = 'CANCEL' ) THEN
2060 --
2061 null;
2062 --
2063 END IF;
2064
2065
2066 EXCEPTION
2067 WHEN NO_DATA_FOUND THEN
2068 wf_core.context ('PORCPTWF','Process_Rcv_Trans','No data found');
2069
2070 WHEN OTHERS THEN
2071 wf_core.context ('PORCPTWF','Process_Rcv_Trans','SQL error ' || sqlcode);
2072
2073 RAISE;
2074
2075
2076 END Process_Rcv_Trans;
2077
2078
2079 /*===========================================================================
2080 PROCEDURE NAME: Open_RCV_Orders
2081
2082 DESCRIPTION: This procedure provides a direct link to the Web Application
2083 Receive Orders web page when user clicks the web page URL
2084 from the workflow notification.
2085
2086
2087 CHANGE HISTORY: WLAU 1/15/1997 Created
2088 ===========================================================================*/
2089
2090
2091 PROCEDURE Open_RCV_Orders (p1 in varchar2,
2092 p2 in varchar2,
2093 p3 in varchar2,
2094 p11 in varchar2 ) IS
2095
2096
2097 l_param VARCHAR2(1000) := '';
2098 l_session_id NUMBER;
2099
2100 BEGIN
2101
2102 -- Construct the Receive Orders Web page parameters
2103 -- Note: ~ is translated to = sign.
2104
2105 --htp.p ('Begin Open_RCV_orders p1= ' || icx_call.decrypt (p1));
2106 --htp.p ('Begin Open_RCV_orders p2= ' || icx_call.decrypt (p2));
2107 --htp.p ('Begin Open_RCV_orders p3= ' || icx_call.decrypt (p3));
2108
2109 -- bug 475711
2110 -- set date format to be the same in Web date format mask
2111
2112 IF icx_sec.validateSession THEN
2113
2114
2115 l_param := 'W*178*ICX_EMPLOYEES*178*ICX_RCV_CONFIRM_RECEIPT*' ||
2116 'PO_HEADER_ID ~ ' || icx_call.decrypt (p1) ||
2117 ' and REQUESTOR_ID ~ ' || icx_call.decrypt (p2) ||
2118 ' and DESTINATION_TYPE_CODE ~ '|| '''EXPENSE''' ||
2119 ' and EXPECTED_RECEIPT_DATE ~ '''|| to_char(to_date(icx_call.decrypt (p3),'DD/MM/YYYY'),icx_sec.getID(icx_sec.pv_date_format)) ||'''' ||
2120 '**]';
2121
2122
2123
2124 l_session_id := icx_sec.getID (icx_sec.PV_Session_ID);
2125
2126 IF icx_call.decrypt(p11) is not NULL THEN
2127
2128 -- Set multi-org context with the correct organization id
2129 icx_sec.set_org_context(l_session_id, icx_call.decrypt(p11));
2130 END IF;
2131
2132 IF l_session_id is NULL THEN
2133
2134 OracleOn.IC (Y => icx_call.encrypt2 (l_param,-999));
2135
2136 ELSE
2137
2138 OracleOn.IC (Y => icx_call.encrypt2 (l_param,l_session_id));
2139
2140 END IF;
2141
2142 END IF;
2143
2144 EXCEPTION
2145
2146 WHEN OTHERS THEN
2147
2148 -- htp.p (SQLERRM);
2149 wf_core.context ('PORCPTWF','Get_Requester_Manager','SQL error ' || sqlcode);
2150
2151 RAISE;
2152
2153
2154 END Open_RCV_Orders;
2155
2156 /*===========================================================================
2157 FUNCTION NAME: FindOrgId
2158
2159 DESCRIPTION: This procedure return the correct org_id if workflow
2160 is running under the multi-org environment.
2161
2162
2163 CHANGE HISTORY: WLAU 2/11/1997 Created
2164 ===========================================================================*/
2165 FUNCTION findOrgId(x_header_id IN number,
2166 x_is_int_req IN VARCHAR2 default 'N' ) return number is
2167
2168 cursor chkmtlorg is
2169 select multi_org_flag
2170 from fnd_product_groups
2171 where rownum < 2;
2172
2173 cursor getorgid is
2174 select org_id
2175 from po_headers_all
2176 where po_header_id = x_header_id;
2177
2178 cursor getorgid_int is
2179 select org_id
2180 from oe_order_headers_all
2181 where header_id = x_header_id;
2182
2183 chk_flg varchar2(5);
2184 v_org_id number := NULL;
2185
2186 BEGIN
2187
2188 open chkmtlorg;
2189 fetch chkmtlorg into chk_flg;
2190 close chkmtlorg;
2191
2192 if chk_flg is not NULL and chk_flg = 'Y' then
2193
2194 if x_is_int_req = 'Y' then
2195 open getorgid_int;
2196 fetch getorgid_int into v_org_id;
2197 close getorgid_int;
2198 else
2199 open getorgid;
2200 fetch getorgid into v_org_id;
2201 close getorgid;
2202 end if;
2203
2204 return v_org_id;
2205
2206 else
2207
2208 return NULL;
2209
2210 end if;
2211
2212 END findOrgId;
2213
2214 function po_revised ( x_po_header_id IN number , x_revision_num IN number,x_wf_itemtype IN varchar2 ,x_wf_itemkey IN varchar2)
2215 return boolean is
2216 old_rev_num number;
2217 x_item_exists varchar2(1) := 'Y';
2218 begin
2219 begin
2220 /** get the attribute for that notification.**/
2221 old_rev_num := wf_engine.GetItemAttrNumber ( itemtype => x_wf_itemtype,
2222 itemkey => x_wf_itemkey,
2223 aname => 'PO_REVISION_NUM');
2224 x_item_exists := 'Y';
2225 exception
2226 when others then
2227 /** This will handle the case where the notifications have been created without the revision_num attribute **/
2228 x_item_exists := 'N';
2229 old_rev_num := -1;
2230 end;
2231
2232 /** compare the attributes. **/
2233 if old_rev_num <> x_revision_num then
2234 return TRUE;
2235 else
2236 return FALSE;
2237 end if;
2238 exception
2239 when no_data_found then
2240 return TRUE;
2241 when others then
2242 wf_core.context ('PORCPTWF','po_revised','SQL error ' || sqlcode);
2243 raise;
2244
2245 end po_revised;
2246
2247
2248
2249 PROCEDURE purge_Orders IS
2250
2251 type purge_orders_Cursor is ref cursor ;
2252
2253 Porcpt_c purge_orders_Cursor;
2254
2255
2256
2257 x_po_header_id NUMBER;
2258 x_requester_id NUMBER;
2259 x_exp_receipt_date DATE;
2260 x_WF_ItemKey VARCHAR2(100);
2261 x_WF_ItemKey_save VARCHAR2(100);
2262 x_auto_receive_flag VARCHAR2(1);
2263 x_WF_process_exists BOOLEAN;
2264 x_allow_inv_dest_receipts Varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
2265
2266 x_wf_itemtype varchar2(6) := 'PORCPT';
2267 x_revision_num number;
2268
2269 BEGIN
2270
2271
2272 x_WF_ItemKey := ' ';
2273 x_WF_ItemKey_save := ' ';
2274
2275 -- Call WF purge API to remove any existing Confirm Receipts WF items
2276 -- which are completed with an END_DATE less than or equal to
2277 -- SYSDATE (default). This is to ensure that the Selection
2278 -- process can start the workflow process for the same item key value.
2279 --
2280 -- The WF purge API will not remove any WF items which are still
2281 -- opened with END_DATE = NULL;
2282
2283
2284 WF_PURGE.total ('PORCPT');
2285
2286
2287 if x_allow_inv_dest_receipts is NULL then
2288 x_allow_inv_dest_receipts := 'N';
2289 end if;
2290
2291 -- Start the Confirm Receipts workflow Selection process
2292 -- Open cursor for selecting records to that need to be cleaned up.
2293 -- Records are retrieved from POR_RECEIVE_ORDERS_V or RCV_CONFIRM_RECEIPT_V
2294 -- which are shared by the Receive Orders Web Pages
2295
2296 if x_allow_inv_dest_receipts = 'N' then
2297
2298 OPEN Porcpt_c for
2299 SELECT po_header_ID, requestor_ID,
2300 expected_receipt_date,
2301 revision_num
2302 FROM POR_RCV_ALL_ITEMS_V1
2303 WHERE expected_receipt_date is not NULL
2304 AND expected_receipt_date + 1 <= SYSDATE
2305 AND NVL(receipt_required_flag,'N') = 'Y'
2306 AND destination_type_code = 'EXPENSE'
2307 AND requestor_ID is not NULL
2308 AND expected_receipt_qty > 0
2309 GROUP BY po_header_ID, requestor_ID, expected_receipt_date, revision_num;
2310
2311 else
2312 OPEN Porcpt_c for
2313 SELECT po_header_ID, requestor_ID, expected_receipt_date, revision_num
2314 FROM POR_RCV_ALL_ITEMS_V1
2315 WHERE expected_receipt_date is not NULL
2316 AND expected_receipt_date + 1 <= SYSDATE
2317 AND NVL(receipt_required_flag,'N') = 'Y'
2318 AND requestor_ID is not NULL
2319 AND expected_receipt_qty > 0
2320 GROUP BY po_header_ID, requestor_ID, expected_receipt_date, revision_num;
2321
2322 end if; /** AllowInvDest Receipt Check **/
2323
2324 LOOP
2325
2326 FETCH Porcpt_c into x_po_header_id,
2327 x_requester_id,
2328 x_exp_receipt_date,
2329 x_revision_num;
2330
2331 -- Contruct Confirm Receipt workflow Item Key in the combination of:
2332 -- PO_Header_ID + Requester_ID + Due_Date
2333 --
2334
2335
2336
2337 x_WF_ItemKey := to_char(x_po_header_id) || ';' ||
2338 to_char(x_requester_id) || ';' ||
2339 to_char(x_exp_receipt_date,'DD-MON-YYYY');
2340
2341 IF Porcpt_c%FOUND AND
2342 x_WF_ItemKey <> x_WF_ItemKey_save THEN
2343
2344 begin
2345
2346 -- Call Workflow to check if the itemkey already exists
2347 -- Note: WF_item.item_exist is for internal use. This procedure
2348 -- will be replaced by an API in a later Workflow release.
2349
2350 x_WF_process_exists := WF_item.item_exist ('PORCPT',
2351 x_WF_ItemKey);
2352
2353 IF x_WF_process_exists THEN
2354
2355 -- Workflow item exists and is still opened
2356 -- Bypass this one
2357
2358
2359 /** If the revision number of the PO is different then cancel the
2360 open notifications ; we need to remove the ELSE below since we
2361 need to start_recpt_process for such cases too. **/
2362 if po_revised(x_po_header_id, x_revision_num , x_wf_itemtype,x_WF_ItemKey ) then
2363 --This will abort and purge the process
2364 wf_purge.items(itemtype => x_WF_ItemType,
2365 itemkey => x_WF_ItemKey,
2366 enddate => sysdate,
2367 docommit => true,
2368 force => true);
2369
2370 COMMIT;
2371
2372 end if;
2373
2374 END IF;
2375
2376 EXCEPTION
2377 when others then
2378 wf_core.context ('PORCPTWF','purge_Orders','SQL error ' || sqlcode);
2379 IF (g_po_wf_debug = 'Y') THEN
2380 po_wf_debug_pkg.insert_debug(x_wf_itemtype,x_wf_itemkey,'purge_orders SQL error ' || sqlcode || ' error message: ' || substr(sqlerrm,1,512));
2381 END IF;
2382 END;
2383
2384 -- Save the ItemKey for the next comparison
2385 x_WF_ItemKey_Save := x_WF_ItemKey;
2386
2387 END IF;
2388
2389
2390
2391
2392 EXIT WHEN Porcpt_c%NOTFOUND;
2393
2394 END LOOP;
2395
2396 CLOSE porcpt_c;
2397
2398 EXCEPTION
2399 WHEN NO_DATA_FOUND THEN
2400 wf_core.context ('PORCPTWF','purge_orders','No data found');
2401 WHEN OTHERS THEN
2402 wf_core.context ('PORCPTWF','purge_Orders','SQL error ' || sqlcode);
2403 RAISE;
2404
2405
2406 END purge_Orders;
2407
2408
2409 FUNCTION get_count(x_item_type IN VARCHAR2,
2410 x_item_key IN VARCHAR2,
2411 skip_distribution_updation in varchar2 default 'N') RETURN NUMBER IS
2412
2413 x_count NUMBER := 0;
2414 x_org_id NUMBER;
2415 x_requester_id NUMBER;
2416 x_exp_receipt_date DATE;
2417 x_allow_inv_dest_receipts varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
2418 x_is_int_req VARCHAR2(1);
2419 x_so_header_id NUMBER;
2420 x_po_header_id NUMBER;
2421
2422 BEGIN
2423
2424
2425 x_org_id := wf_engine.GetItemAttrNumber( itemtype => x_item_type,
2426 itemkey => x_item_key,
2427 aname => 'ORG_ID');
2428
2429 -- Setup the organization context for the multi-org environment
2430 setOrgCtx(x_org_id);
2431
2432 x_requester_id := wf_engine.GetItemAttrNumber( itemtype => x_item_type,
2433 itemkey => x_item_key,
2434 aname => 'REQUESTER_ID');
2435
2436 x_exp_receipt_date := wf_engine.GetItemAttrDate ( itemtype => x_item_type,
2437 itemkey => x_item_key,
2438 aname => 'DUE_DATE');
2439
2440 x_is_int_req := wf_engine.GetItemAttrText ( itemtype => x_item_type,
2441 itemkey => x_item_key,
2442 aname => 'IS_INT_REQ');
2443 begin
2444
2445 if x_is_int_req = 'Y' then
2446
2447 x_so_header_id := wf_engine.GetItemAttrNumber( itemtype => x_item_type,
2448 itemkey => x_item_key,
2449 aname => 'SO_HEADER_ID');
2450 -- ash_debug.debug('get_count x_so_header_id ', x_so_header_id);
2451
2452 if x_allow_inv_dest_receipts = 'N' then
2453 SELECT COUNT(*) INTO
2454 X_COUNT
2455 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
2456 WHERE expected_receipt_date is not NULL
2457 AND expected_receipt_date = x_exp_receipt_date
2458 AND NVL(receipt_required_flag,'N') = 'Y'
2459 AND destination_type_code = 'EXPENSE'
2460 AND requestor_ID is not NULL
2461 AND expected_receipt_qty > 0
2462 AND so_header_ID = x_so_header_id
2463 AND requestor_ID = x_requester_id;
2464 else
2465 SELECT COUNT(*)
2466 INTO X_COUNT
2467 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
2468 WHERE expected_receipt_date is not NULL
2469 AND expected_receipt_date = x_exp_receipt_date
2470 AND NVL(receipt_required_flag,'N') = 'Y'
2471 AND requestor_ID is not NULL
2472 AND expected_receipt_qty > 0
2473 AND so_header_ID = x_so_header_id
2474 AND requestor_ID = x_requester_id;
2475 end if;
2476
2477 -- ash_debug.debug('get_count x_so_header_id ', x_count);
2478
2479 else
2480
2481 x_po_header_id := wf_engine.GetItemAttrNumber( itemtype => x_item_type,
2482 itemkey => x_item_key,
2483 aname => 'PO_HEADER_ID');
2484
2485 -- <R12 Confirm Receipt and JRAD Conversion>
2486 -- Removing the query execution from this place and
2487 -- calling populate_order_info function to populate the
2488 -- latest distributions list.
2489
2490 x_count := populate_order_info(x_item_type, x_item_key, skip_distribution_updation);
2491
2492 end if; -- is internal item
2493
2494 RETURN x_count;
2495
2496 EXCEPTION
2497 WHEN OTHERS THEN
2498 RETURN 0;
2499 END;
2500 END get_count;
2501
2502 PROCEDURE DOES_ITEM_EXIST (itemtype in varchar2,
2503 itemkey in varchar2,
2504 actid in number,
2505 funcmode in varchar2,
2506 resultout out NOCOPY varchar2) is
2507
2508 x_proress varchar2(100):= '001';
2509 x_resultout varchar2(30);
2510 x_count NUMBER := 0;
2511 x_disp_count NUMBER :=0;
2512 x_skip_distribution_updation varchar2(2);
2513
2514 BEGIN
2515 -- Get the activity attribute text. This value would be 'Y' only if called from receive upto
2516 -- amount invoiced notification. Pass the value toto get_count which would get percolated down
2517 x_skip_distribution_updation := wf_engine.GetActivityAttrText(itemtype,
2518 itemkey,
2519 actid,
2520 'IS_FROM_RCV_UPTO_AMT_INVOICED');
2521
2522 x_count := get_count(itemtype,itemkey,x_skip_distribution_updation);
2523
2524 -- ash_debug.debug('does_item_exist x_count ' , x_count);
2525
2526 IF x_count = 0 THEN
2527 resultout := wf_engine.eng_completed || ':' || 'N';
2528 x_resultout := 'N';
2529 ELSE /* the reminder should have an updated line count */
2530
2531 -- ash_debug.debug('does_item_exist x_count 1' , x_count);
2532
2533 resultout := wf_engine.eng_completed || ':' || 'Y';
2534 x_resultout := 'Y';
2535
2536 -- ash_debug.debug('does_item_exist resultut ' , resultout);
2537 END IF;
2538 -- ash_debug.debug('does_item_exist resultout ' , resultout);
2539
2540 EXCEPTION
2541
2542 WHEN OTHERS THEN
2543 WF_CORE.context('PORCPTWF' , 'DOES_ITEM_EXIST', 'ERROR IN DOES_ITEM_EXIST');
2544 RAISE;
2545 END does_item_exist;
2546
2547
2548 FUNCTION get_req_number(x_po_distribution_id IN NUMBER) RETURN VARCHAR2 is
2549 x_req_number VARCHAR2(20);
2550
2551 BEGIN
2552
2553
2554 select prh.segment1
2555 INTO x_req_number
2556 from po_requisition_headers prh,
2557 po_requisition_lines prl,po_distributions pod,
2558 po_req_distributions pord
2559 where pod.po_distribution_id = x_po_distribution_id and
2560 pord.distribution_id = pod.req_distribution_id and
2561 pord.requisition_line_id = prl.requisition_line_id and
2562 prl.requisition_header_id = prh.requisition_header_id;
2563
2564 RETURN x_req_number;
2565
2566 EXCEPTION
2567 WHEN OTHERS THEN
2568 RETURN NULL;
2569
2570 END get_req_number;
2571
2572
2573 PROCEDURE Get_Rcv_Internal_Order_URL ( itemtype in varchar2,
2574 itemkey in varchar2,
2575 actid in number,
2576 funmode in varchar2,
2577 result out NOCOPY varchar2 ) IS
2578
2579
2580 x_requester_ID NUMBER;
2581 x_header_ID NUMBER;
2582 x_exp_receipt_date DATE;
2583 x_Rcv_Order_URL VARCHAR2(1000);
2584 x_org_id NUMBER;
2585 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
2586
2587 BEGIN
2588
2589 IF ( funmode = 'RUN' ) THEN
2590 --
2591 x_requester_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2592 itemkey => itemkey,
2593 aname => 'REQUESTER_ID');
2594
2595 x_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2596 itemkey => itemkey,
2597 aname => 'SO_HEADER_ID');
2598
2599 x_org_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2600 itemkey => itemkey,
2601 aname => 'ORG_ID');
2602 setOrgCtx(x_org_id);
2603
2604 x_exp_receipt_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
2605 itemkey => itemkey,
2606 aname => 'DUE_DATE');
2607
2608
2609 x_Rcv_Order_url := l_base_href || '/OA_HTML/OA.jsp?OAFunc=ICX_POR_LAUNCH_IP' || '&' || 'porOrderHeaderId=' || to_char(x_header_id) || '&' || 'porMode=confirmReceipt' ;
2610
2611 IF (x_requester_id is not null) THEN
2612 x_Rcv_Order_url := x_Rcv_Order_url || '&' || 'porRequesterId=' ||to_char(x_requester_id) || '&';
2613 END IF;
2614
2615 IF (x_exp_receipt_date is not null) THEN
2616 x_Rcv_Order_url := x_Rcv_Order_url || 'porExpectedDate=' || to_char(x_exp_receipt_date,'DD-MON-YYYY') || '&';
2617 END IF;
2618
2619 x_Rcv_Order_url := x_Rcv_Order_url || 'porOrderTypeCode=REQ' || '&'
2620 || 'porDestOrgId=' || to_char(x_org_id);
2621
2622 wf_engine.SetItemAttrText ( itemtype => itemtype,
2623 itemkey => itemkey,
2624 aname => 'RCV_ORDERS_URL',
2625 avalue => x_Rcv_Order_URL );
2626 --
2627 ELSIF ( funmode = 'CANCEL' ) THEN
2628 --
2629 null;
2630 --
2631 END IF;
2632
2633
2634 EXCEPTION
2635 WHEN NO_DATA_FOUND THEN
2636 wf_core.context ('PORCPTWF','Get_Rcv_Internal_Order_URL','No data found');
2637
2638 WHEN OTHERS THEN
2639 wf_core.context ('PORCPTWF','Get_Rcv_Internal_Order_URL','SQL error ' || sqlcode);
2640
2641 RAISE;
2642
2643
2644 END Get_Rcv_Internal_Order_URL;
2645
2646
2647 /*===========================================================================
2648 PROCEDURE NAME: Get_Requester_Manager
2649
2650 DESCRIPTION: This procedure determines who the requester's manager is.
2651 The manger's username is used to notify the manager
2652 of the workflow timeout function.
2653
2654
2655 CHANGE HISTORY: WLAU 1/15/1997 Created
2656 ASABADRA 03/01/2002 Get org id from parameter and
2657 use the org id to set context
2658 ===========================================================================*/
2659
2660
2661 PROCEDURE Get_Requester_Manager (itemtype in varchar2,
2662 itemkey in varchar2,
2663 actid in number,
2664 funmode in varchar2,
2665 result out NOCOPY varchar2 ) IS
2666
2667 x_org_id NUMBER;
2668 x_requester_id NUMBER;
2669 x_manager_id NUMBER;
2670 x_manager_username WF_USERS.NAME%TYPE;
2671 x_manager_disp_name WF_USERS.DISPLAY_NAME%TYPE;
2672
2673 x_requester_current BOOLEAN ;
2674 dummy VARCHAR2(1);
2675
2676 BEGIN
2677
2678
2679 IF ( funmode = 'RUN' ) THEN
2680
2681 x_org_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2682 itemkey => itemkey,
2683 aname => 'ORG_ID');
2684
2685 -- Setup the organization context for the multi-org environment
2686 setOrgCtx(x_org_id);
2687
2688 /* Bug: 2820973 Check if the requester is an active employee. If yes then get
2689 the requester's manager and send the time out notification. If the requester
2690 is not an active employee then the notification had been sent to the buyer
2691 and in that case get the buyer's manager for time out notification.
2692 */
2693
2694 x_requester_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2695 itemkey => itemkey,
2696 aname => 'REQUESTER_ID');
2697 Begin
2698
2699 Select 'X'
2700 Into dummy
2701 From per_workforce_current_x
2702 Where person_id = x_requester_id;
2703
2704 x_requester_current := TRUE;
2705
2706 Exception
2707 when no_data_found then
2708 x_requester_current := FALSE;
2709 End;
2710
2711 If (x_requester_current = FALSE) then
2712 x_requester_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2713 itemkey => itemkey,
2714 aname => 'BUYER_ID');
2715 Else
2716 x_requester_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2717 itemkey => itemkey,
2718 aname => 'REQUESTER_ID');
2719 End if;
2720
2721 Begin
2722
2723 Select cwk.supervisor_id
2724 into x_manager_id
2725 From per_workforce_current_x cwk
2726 Where cwk.person_id = x_requester_id;
2727
2728 Exception
2729 When no_data_found then null;
2730 End;
2731
2732 wf_engine.SetItemAttrNumber ( itemtype => ItemType,
2733 itemkey => itemkey,
2734 aname => 'MANAGER_ID',
2735 avalue => x_manager_id );
2736
2737
2738 wf_directory.GetUserName ( p_orig_system => 'PER',
2739 p_orig_system_id => x_manager_id,
2740 p_name => x_manager_username,
2741 p_display_name => x_manager_disp_name);
2742
2743 wf_engine.SetItemAttrText ( itemtype => itemtype,
2744 itemkey => itemkey,
2745 aname => 'MANAGER_USERNAME',
2746 avalue => x_manager_username );
2747
2748 wf_engine.SetItemAttrText ( itemtype => itemtype,
2749 itemkey => itemkey,
2750 aname => 'MANAGER_DISP_NAME',
2751 avalue => x_manager_disp_name );
2752
2753
2754 ELSIF ( funmode = 'CANCEL' ) THEN
2755 --
2756 null;
2757 --
2758 END IF;
2759
2760 END Get_Requester_Manager;
2761
2762
2763 PROCEDURE Process_Rcv_Trans_Int ( itemtype in varchar2,
2764 itemkey in varchar2,
2765 actid in number,
2766 funmode in varchar2,
2767 result out NOCOPY varchar2 ) IS
2768 TYPE shipment_orders_cursor IS ref CURSOR;
2769 Porcpt_Shipment shipment_orders_cursor;
2770
2771 x_group_id NUMBER;
2772 x_RCV_txns_rc NUMBER := 0;
2773
2774 x_exp_receipt_date DATE;
2775 x_header_id NUMBER;
2776 x_requester_id NUMBER;
2777 x_requester_username WF_USERS.NAME%TYPE; --Use the requester username to be passed to initialize
2778 x_rcv_trans_status VARCHAR2(500) := NULL;
2779 X_tmp_count NUMBER;
2780 X_tmp_count1 NUMBER;
2781 X_tmp_approve VARCHAR2(20);
2782 x_allow_inv_dest_receipts Varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
2783
2784 x_inserted_txn_status NUMBER;
2785 x_org_id NUMBER;
2786
2787 t_req_line_id rcvNumberArray;
2788 t_expected_receipt_qty rcvNumberArray;
2789 t_ordered_uom rcvVarcharArray;
2790 t_item_id rcvNumberArray;
2791 t_primary_uom_class rcvVarcharArray;
2792 t_org_id rcvNumberArray;
2793 t_waybillNum rcvVarcharArray;
2794 t_comments rcvVarcharArray;
2795 t_packingSlip rcvVarcharArray;
2796 x_requestor_id NUMBER;
2797
2798 x_progress varchar2(1000):= '001';
2799 x_message_token VARCHAR2(2000);
2800
2801 BEGIN
2802
2803 IF ( funmode = 'RUN' ) THEN
2804 --
2805
2806 x_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2807 itemkey => itemkey,
2808 aname => 'SO_HEADER_ID');
2809 -- Setup the organization context for the multi-org environment
2810
2811 x_org_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2812 itemkey => itemkey,
2813 aname => 'ORG_ID');
2814
2815 -- Use the requester username to be passed to initialize
2816 x_requester_username := wf_engine.GetItemAttrText ( itemtype => itemtype,
2817 itemkey => itemkey,
2818 aname => 'REQUESTER_USERNAME');
2819
2820 PORCPTWF.initialize(x_requester_username,x_org_id);
2821
2822 /** rewrite after initialize **/
2823 x_allow_inv_dest_receipts := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
2824
2825
2826 x_requester_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2827 itemkey => itemkey,
2828 aname => 'REQUESTER_ID');
2829
2830
2831 x_exp_receipt_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
2832 itemkey => itemkey,
2833 aname => 'DUE_DATE');
2834
2835
2836 SELECT rcv_interface_groups_s.nextval
2837 INTO x_group_id
2838 FROM sys.dual;
2839
2840
2841 if x_allow_inv_dest_receipts is NULL then
2842 x_allow_inv_dest_receipts := 'N';
2843 end if;
2844
2845 if x_allow_inv_dest_receipts = 'N' then
2846 OPEN Porcpt_Shipment for
2847 SELECT REQUISITION_LINE_ID,
2848 EXPECTED_RECEIPT_QTY,
2849 ORDERED_UOM,
2850 ITEM_ID,
2851 PRIMARY_UOM_CLASS,
2852 TO_ORGANIZATION_ID,
2853 COMMENTS,
2854 PACKING_SLIP,
2855 WAYBILL_AIRBILL_NUM
2856 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
2857 WHERE expected_receipt_date is not NULL
2858 AND expected_receipt_date = x_exp_receipt_date
2859 AND destination_type_code = 'EXPENSE'
2860 AND requestor_id is not NULL
2861 AND expected_receipt_qty > 0
2862 AND so_header_ID = x_header_ID
2863 AND requestor_ID = x_requester_ID;
2864 else
2865 OPEN Porcpt_Shipment for
2866 SELECT REQUISITION_LINE_ID,
2867 EXPECTED_RECEIPT_QTY,
2868 ORDERED_UOM,
2869 ITEM_ID,
2870 PRIMARY_UOM_CLASS,
2871 TO_ORGANIZATION_ID,
2872 COMMENTS,
2873 PACKING_SLIP,
2874 WAYBILL_AIRBILL_NUM
2875 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
2876 WHERE expected_receipt_date is not NULL
2877 AND expected_receipt_date = x_exp_receipt_date
2878 AND requestor_id is not NULL
2879 AND expected_receipt_qty > 0
2880 AND so_header_ID = x_header_ID
2881 AND requestor_ID = x_requester_ID;
2882
2883 end if; /** AllowInvDest Receipt Check **/
2884
2885 FETCH porcpt_Shipment BULK COLLECT into t_req_line_id,
2886 t_expected_receipt_qty,
2887 t_ordered_uom,
2888 t_item_id,
2889 t_primary_uom_class,
2890 t_org_id,
2891 t_comments,
2892 t_packingSlip,
2893 t_waybillNum;
2894
2895 CLOSE Porcpt_Shipment;
2896
2897 for i in 1..t_req_line_id.count loop
2898 x_progress := 'reqlineid*' || to_char(t_req_line_id(i)) || '*ex_rcpt_qty*' || t_expected_receipt_qty(i)
2899 || '*uom*' || t_ordered_uom(i) || '*itemid*' || to_char(t_item_id(i)) || '*uom_class*' || t_primary_uom_class(i) || '*org_id*' || to_char(t_org_id(i)) || '*comments*' || t_comments(i)
2900 || '*pkgSlip*' || t_packingSlip(i) || '*waybillnum*' || t_waybillNum(i);
2901
2902 x_progress := x_progress || '*x_group_id*' || to_char(x_group_id);
2903
2904 IF (g_po_wf_debug = 'Y') THEN
2905 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2906 END IF;
2907 end loop;
2908
2909 x_inserted_txn_status := POR_RCV_ORD_SV.groupInternalTransaction (t_req_line_id,
2910 t_expected_receipt_qty,
2911 t_ordered_uom,
2912 t_item_id,
2913 t_primary_uom_class,
2914 t_org_id,
2915 t_comments,
2916 t_packingSlip,
2917 t_waybillNum,
2918 x_group_id,
2919 SYSDATE,
2920 'WP4_CONFIRM');
2921
2922 IF x_inserted_txn_status = 0 THEN
2923
2924 x_RCV_txns_rc := por_rcv_ord_sv.process_transactions(X_group_id, 'WF');
2925
2926
2927 -- At least one of the receiving transactions inserted
2928
2929 IF x_RCV_txns_rc is NULL OR
2930 x_RCV_txns_rc = 0 THEN
2931
2932 RESULT := 'PASSED';
2933
2934 ELSE
2935 get_txn_error_message(x_group_id, x_RCV_txns_rc, x_rcv_trans_status, x_message_token);
2936
2937 wf_engine.SetItemAttrText ( itemtype => Itemtype,
2938 itemkey => Itemkey,
2939 aname => 'RCV_TRANS_STATUS',
2940 avalue => x_rcv_trans_status );
2941
2942 wf_engine.SetItemAttrText ( itemtype => Itemtype,
2943 itemkey => Itemkey,
2944 aname => 'RCV_ERR_MSG_TOKEN',
2945 avalue => x_message_token );
2946
2947 RESULT := 'FAILED';
2948
2949 END IF;
2950
2951 ELSE
2952
2953 if (x_allow_inv_dest_receipts = 'N') then
2954 SELECT count(*)
2955 INTO x_tmp_count
2956 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
2957 WHERE expected_receipt_date is not NULL
2958 AND NVL(receipt_required_flag,'N') = 'Y'
2959 AND destination_type_code = 'EXPENSE'
2960 AND requestor_id is not NULL
2961 AND so_header_ID = x_header_ID;
2962
2963 SELECT count(*)
2964 INTO x_tmp_count1
2965 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
2966 WHERE expected_receipt_date is not NULL
2967 AND expected_receipt_date = x_exp_receipt_date
2968 AND NVL(receipt_required_flag,'N') = 'Y'
2969 AND destination_type_code = 'EXPENSE'
2970 AND requestor_id is not NULL
2971 AND expected_receipt_qty = 0
2972 AND so_header_ID = x_header_ID
2973 AND requestor_ID = x_requester_id;
2974 else
2975 SELECT count(*)
2976 INTO x_tmp_count
2977 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
2978 WHERE expected_receipt_date is not NULL
2979 AND NVL(receipt_required_flag,'N') = 'Y'
2980 AND requestor_id is not NULL
2981 AND so_header_ID = x_header_ID;
2982
2983 SELECT count(*)
2984 INTO x_tmp_count1
2985 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
2986 WHERE expected_receipt_date is not NULL
2987 AND expected_receipt_date = x_exp_receipt_date
2988 AND NVL(receipt_required_flag,'N') = 'Y'
2989 AND requestor_id is not NULL
2990 AND expected_receipt_qty = 0
2991 AND so_header_ID = x_header_ID
2992 AND requestor_ID = X_REQUESTER_ID;
2993 end if;
2994
2995 IF (x_tmp_count1 > 0) THEN
2996 -- will come down here if all the eligible shipments
2997 -- have been fully received order has already been received
2998 x_rcv_trans_status := 'RCV_RCPT_ORDER_RECEIVED';
2999
3000 ELSIF (x_tmp_count = 0) THEN
3001 -- if it doesn't satify four basic criteria
3002 -- 1. make the RCV_CONFIRM_RECEIPT_V
3003 -- 2. receipt_required
3004 -- 3. destination_type_code = 'EXPENSE'
3005 -- 4. expected_receipt_date and requestor_id not NULL
3006 -- then, it doesn't qualify for confirm receipt
3007
3008 x_rcv_trans_status := 'RCV_RCPT_APPROVAL_FAILED';
3009
3010 ELSIF (x_tmp_count > 0) THEN
3011 -- either the requestor or the expected_receipt_date has changed
3012 x_rcv_trans_status := 'RCV_RCPT_RQTR_DATE_CHANGED';
3013
3014 ELSE
3015 -- Insert to Receiving Transaction Interface failed
3016 x_rcv_trans_status := 'RCV_RCPT_INSERT_FAILED';
3017
3018 END IF;
3019
3020 wf_engine.SetItemAttrText ( itemtype => Itemtype,
3021 itemkey => Itemkey,
3022 aname => 'RCV_TRANS_STATUS',
3023 avalue => x_rcv_trans_status );
3024
3025 RESULT :='FAILED';
3026
3027 END IF;
3028
3029 ELSIF ( funmode = 'CANCEL' ) THEN
3030 --
3031 null;
3032 --
3033 END IF;
3034
3035
3036 EXCEPTION
3037 WHEN NO_DATA_FOUND THEN
3038 wf_core.context ('PORCPTWF','Process_Rcv_Trans_Int','No data found');
3039
3040 WHEN OTHERS THEN
3041 wf_core.context ('PORCPTWF','Process_Rcv_Trans_Int','SQL error ' || sqlcode);
3042
3043 RAISE;
3044
3045
3046 END Process_Rcv_Trans_Int;
3047
3048 PROCEDURE generate_html_header(x_item_type IN VARCHAR2,
3049 x_item_key IN VARCHAR2 ,
3050 x_count IN NUMBER,
3051 x_document IN OUT NOCOPY VARCHAR2) IS
3052
3053 l_document VARCHAR2(32000) := '';
3054 x_number VARCHAR2(20):= NULL;
3055 x_supplier_name PO_VENDORS.VENDOR_NAME%TYPE := NULL;
3056 x_due_date DATE;
3057 x_buyer_name WF_USERS.DISPLAY_NAME%TYPE := NULL;
3058 x_note_to_receiver PO_HEADERS_ALL.NOTE_TO_RECEIVER%TYPE :=NULL;
3059 x_disp_count NUMBER := 0;
3060 x_confirm_rcpt_inst VARCHAR2(2000);
3061 x_is_int_req VARCHAR2(1);
3062
3063 nl VARCHAR2(1) := fnd_global.newline;
3064 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
3065 BEGIN
3066
3067 x_is_int_req := wf_engine.GetItemAttrText( itemtype => x_item_type,
3068 itemkey => x_item_key,
3069 aname => 'IS_INT_REQ');
3070
3071 if x_is_int_req is NULL then
3072 x_is_int_req := 'N';
3073 end if;
3074
3075 if x_is_int_req = 'Y' then
3076
3077 x_number := wf_engine.GetItemAttrText( itemtype => x_item_type,
3078 itemkey => x_item_key,
3079 aname => 'SO_NUMBER');
3080
3081 else -- Purchase Requisition
3082
3083
3084 x_number := wf_engine.GetItemAttrText( itemtype => x_item_type,
3085 itemkey => x_item_key,
3086 aname => 'PO_NUMBER');
3087
3088 x_buyer_name := wf_engine.GetItemAttrText(itemtype => x_item_type,
3089 itemkey => x_item_key,
3090 aname => 'BUYER_DISP_NAME');
3091
3092
3093 x_supplier_name := wf_engine.GetItemAttrText( itemtype => x_item_type,
3094 itemkey => x_item_key,
3095 aname => 'SUPPLIER_DISP_NAME');
3096
3097 end if;
3098
3099 x_note_to_receiver := wf_engine.GetItemAttrText( itemtype => x_item_type,
3100 itemkey => x_item_key,
3101 aname => 'NOTE_TO_RECEIVER');
3102
3103 x_due_date := wf_engine.GetItemAttrDate( itemtype => x_item_type,
3104 itemkey => x_item_key,
3105 aname => 'DUE_DATE');
3106
3107
3108 IF x_count > 5 THEN
3109 x_disp_count := 5;
3110 ELSE
3111 x_disp_count := x_count;
3112 END IF;
3113
3114
3115
3116 l_document := l_document || '<TABLE width="90%" border=0 cellpadding=0 cellspacing=0 SUMMARY="">';
3117
3118
3119 if x_is_int_req = 'Y' then
3120
3121 l_document := l_document || '<tr><td class=fieldtitle align=right nowrap>' || fnd_message.get_string('ICX','ICX_POR_PRMPT_ORDER_TYPE') || ' </td> <td align=left class=fielddatabold > '
3122 || fnd_message.get_string('ICX','ICX_POR_INT_ORDER_TYPE') || ' </td> </tr>' || nl;
3123
3124 l_document := l_document || '<tr><td class=fieldtitle align=right nowrap>' || fnd_message.get_string('ICX','ICX_POR_PRMPT_SO_NUMBER') || ' </td> <td align=left class=fielddatabold > '
3125 || x_number|| ' </td> </tr>' || nl;
3126
3127
3128 else -- purchase requisition
3129
3130 l_document := l_document || '<tr><td class=fieldtitle align=right nowrap>' || fnd_message.get_string('ICX','ICX_POR_PRMPT_PO_NUMBER') || ' </td> <td align=left class=fielddatabold > '
3131 || x_number|| ' </td> </tr>' || nl;
3132
3133 l_document := l_document || '<tr><td class=fieldtitle align=right nowrap>' || fnd_message.get_string('ICX','ICX_POR_PRMPT_SUPPLIER') || ' </td> <td align=left class=fielddatabold > '
3134 || x_supplier_name || ' </td> </tr>' || nl;
3135
3136 end if;
3137
3138 l_document := l_document || '<tr><td class=fieldtitle nowrap align=right> ' || fnd_message.get_string('ICX','ICX_POR_PRMPT_DUE_DATE') || ' </td> ';
3139
3140 l_document := l_document || '<td align=left class=fielddatabold > ' || to_char(x_due_date,'DD-MON-YYYY') || '</td> </tr>' || nl;
3141
3142 if x_is_int_req = 'N' then -- purchase
3143
3144 l_document := l_document || '<tr><td class=fieldtitle nowrap align=right>' || fnd_message.get_string('ICX','ICX_POR_PRMPT_BUYER_DISP') || ' </td> <td align=left class=fielddatabold > ' || x_buyer_name || ' </td> </tr>' || nl;
3145
3146 end if;
3147
3148 l_document := l_document || '<TR><TD colspan=2 height=20><img src=' || l_base_href || '/OA_MEDIA/PORTRANS.gif ALT=""></TD></TR><P>' || nl;
3149
3150 l_document := l_document || '<tr><td class=fieldtitle nowrap align=right>' || fnd_message.get_string('ICX','ICX_POR_NOTE_TO_RCV') || ' </td> <td class=fielddatabold align=left>' || x_note_to_receiver || ' </td> </tr> ' || nl;
3151
3152 l_document := l_document || '<TR><TD colspan=2 height=20><img src=' || l_base_href || '/OA_MEDIA/PORTRANS.gif ALT=""></TD></TR><P>' || nl;
3153
3154 l_document := l_document || '<TR><TD></TD><TD class=subheader1> ' || fnd_message.get_string('ICX','ICX_POR_ITEMS_TO_RECEIVE') || '</TD></TR>' || nl;
3155 l_document := l_document || '<TR><TD></TD><TD colspan=2 height=1 bgcolor=#cccc99><img src=' || l_base_href || '/OA_MEDIA/FNDITPNT.gif ALT=""></TD></TR>' || nl;
3156 l_document := l_document || '<TR><TD colspan=2 height=20><img src=' || l_base_href || '/OA_MEDIA/PORTRANS.gif ALT=""></TD></TR>' || nl;
3157
3158
3159 fnd_message.set_name('ICX','ICX_POR_CONFIRM_RCPT_INSTR');
3160 fnd_message.set_token('LINES_DISP',to_char(x_disp_count));
3161 fnd_message.set_token('TOTAL_LINES',to_char(x_count));
3162
3163 x_confirm_rcpt_inst := fnd_message.get;
3164
3165 l_document := l_document || '<TR><TD></TD> <TD class=instructiontext>' || x_confirm_rcpt_inst || ' </TD></TR> ';
3166
3167 l_document := l_document || '<TR><TD colspan=2 height=20><img src=' || l_base_href || '/OA_MEDIA/PORTRANS.gif ALT=""></TD></TR>' || nl;
3168
3169 x_document := x_document || l_document;
3170
3171 END;
3172
3173 PROCEDURE get_receipt_lines(x_header_id IN NUMBER,
3174 x_requester_id IN NUMBER,
3175 x_exp_receipt_date IN DATE,
3176 x_is_int_req IN VARCHAR2,
3177 x_document IN OUT NOCOPY VARCHAR2) IS
3178
3179 type select_line_info_Cursor is ref cursor ;
3180 Porcpt_LineInfo select_line_info_Cursor;
3181 l_document VARCHAR2(32000) := '';
3182 i NUMBER:=0;
3183 l_rcpt_record rcpt_record ;
3184 x_req_number VARCHAR2(20);
3185
3186 x_allow_inv_dest_receipts varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
3187 NL VARCHAR2(1) := fnd_global.newline;
3188
3189 BEGIN
3190
3191 if x_is_int_req = 'Y' then
3192
3193 if x_allow_inv_dest_receipts = 'N' then
3194 OPEN Porcpt_LineInfo for
3195 SELECT so_line_number,
3196 expected_receipt_qty,
3197 quantity_delivered,
3198 ordered_qty,
3199 primary_uom,
3200 item_description,
3201 currency_code,
3202 unit_price,
3203 req_number
3204 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
3205 WHERE expected_receipt_date is not NULL
3206 AND trunc(expected_receipt_date) = trunc(x_exp_receipt_date)
3207 AND NVL(receipt_required_flag,'N') = 'Y'
3208 AND destination_type_code = 'EXPENSE'
3209 AND requestor_ID is not NULL
3210 AND expected_receipt_qty > 0
3211 AND so_header_ID = x_header_id
3212 AND requestor_ID = x_requester_id
3213 ORDER BY so_line_number;
3214
3215 else
3216 OPEN Porcpt_LineInfo for
3217 SELECT so_line_number,
3218 expected_receipt_qty,
3219 quantity_delivered,
3220 ordered_qty,
3221 primary_uom,
3222 item_description,
3223 currency_code,
3224 unit_price,
3225 req_number
3226 FROM POR_CONFIRM_INTERNAL_RECEIPT_V
3227 WHERE expected_receipt_date is not NULL
3228 AND trunc(expected_receipt_date) = trunc(x_exp_receipt_date)
3229 AND NVL(receipt_required_flag,'N') = 'Y'
3230 AND requestor_ID is not NULL
3231 AND expected_receipt_qty > 0
3232 AND so_header_ID = x_header_id
3233 AND requestor_ID = x_requester_id
3234 ORDER BY so_line_number;
3235
3236 end if;
3237
3238
3239 else -- purchase requisition
3240
3241 if x_allow_inv_dest_receipts = 'N' then
3242 OPEN Porcpt_LineInfo for
3243 SELECT po_line_number,
3244 expected_receipt_qty,
3245 quantity_delivered,
3246 ordered_qty,
3247 primary_uom,
3248 item_description,
3249 currency_code,
3250 unit_price,
3251 po_distribution_id
3252 FROM POR_RCV_ALL_ITEMS_V1
3253 WHERE expected_receipt_date is not NULL
3254 AND trunc(expected_receipt_date) = trunc(x_exp_receipt_date)
3255 AND NVL(receipt_required_flag,'N') = 'Y'
3256 AND destination_type_code = 'EXPENSE'
3257 AND requestor_ID is not NULL
3258 AND expected_receipt_qty > 0
3259 AND po_header_ID = x_header_id
3260 AND requestor_ID = x_requester_id
3261 ORDER BY po_line_number;
3262
3263 else
3264 OPEN Porcpt_LineInfo for
3265 SELECT po_line_number,
3266 expected_receipt_qty,
3267 quantity_delivered,
3268 ordered_qty,
3269 primary_uom,
3270 item_description,
3271 currency_code,
3272 unit_price,
3273 po_distribution_id
3274 FROM POR_RCV_ALL_ITEMS_V1
3275 WHERE expected_receipt_date is not NULL
3276 AND trunc(expected_receipt_date) = trunc(x_exp_receipt_date)
3277 AND NVL(receipt_required_flag,'N') = 'Y'
3278 AND requestor_ID is not NULL
3279 AND expected_receipt_qty > 0
3280 AND po_header_ID = x_header_id
3281 AND requestor_ID = x_requester_id
3282 ORDER BY po_line_number;
3283
3284 end if;
3285
3286 end if; -- check for internal requisition
3287
3288 LOOP
3289 FETCH porcpt_lineinfo INTO l_rcpt_record;
3290
3291 if x_is_int_req = 'N' then
3292
3293 x_req_number := get_req_number(l_rcpt_record.po_distribution_id);
3294
3295 else
3296
3297 x_req_number := l_rcpt_record.po_distribution_id;
3298 end if;
3299
3300
3301 EXIT WHEN porcpt_lineinfo%notfound;
3302
3303 i := i + 1;
3304
3305 l_document := l_document || '<TR>' || NL;
3306
3307 l_document := l_document || '<TD class=tabledata align=left headers="catLine_1">' ||
3308 nvl(to_char(l_rcpt_record.line_number), ' ') || '</TD>' || NL;
3309
3310
3311 l_document := l_document || '<TD class=tabledata align=left headers="itemDesc_1">' ||
3312 nvl(l_rcpt_record.item_description, ' ') || '</TD>' || NL;
3313
3314 l_document := l_document || '<TD class=tabledata align=left headers="catUnit_1">' ||
3315 nvl(l_rcpt_record.unit_of_measure, ' ') || '</TD>' || NL;
3316
3317 l_document := l_document || '<TD class=tabledata align=left headers="qtyReceived_1">' ||
3318 nvl(to_char(l_rcpt_record.quantity_received), ' ') || '</TD>' || NL;
3319
3320
3321 l_document := l_document || '<TD class=tabledata align=left headers="qtyOrdered_1">' ||
3322 nvl(to_char(l_rcpt_record.ordered_qty), ' ') || '</TD>' || NL;
3323
3324 l_document := l_document || '<TD class=tabledata align=left headers="currency_1">' ||
3325 nvl(l_rcpt_record.currency_code, ' ') || '</TD>' || NL;
3326
3327 l_document := l_document || '<TD class=tabledata align=left headers="catPrice_1">' ||
3328 nvl(to_char(l_rcpt_record.unit_price), ' ') || '</TD>' || NL;
3329
3330
3331 l_document := l_document || '<TD class=tabledata align=left headers="order_1">' ||
3332 nvl(x_req_number, ' ') || '</TD>' || NL;
3333
3334
3335 l_document := l_document || '</TR>' || NL;
3336
3337 exit when i = 5;
3338
3339 END LOOP;
3340 l_document := l_document || '</TABLE>' || NL;
3341 x_document := l_document;
3342
3343 EXCEPTION
3344 WHEN OTHERS THEN
3345 RAISE;
3346
3347 END;
3348
3349
3350 PROCEDURE GET_PO_RCV_NOTIF_MSG(document_id in varchar2,
3351 display_type in varchar2,
3352 document in out NOCOPY varchar2,
3353 document_type in out NOCOPY varchar2) IS
3354
3355 l_item_type wf_items.item_type%TYPE;
3356 l_item_key wf_items.item_key%TYPE;
3357
3358 l_document VARCHAR2(32000) := '';
3359 l_document_1 VARCHAR2(32000) := '';
3360 l_document_2 VARCHAR2(32000) := '';
3361 x_header_id NUMBER;
3362 x_requester_id NUMBER;
3363 x_exp_receipt_date DATE;
3364 x_count NUMBER;
3365 l_rcv_items_url VARCHAR2(1000) := '';
3366
3367 x_is_int_req VARCHAR2(1);
3368 x_org_id NUMBER;
3369 l_temp VARCHAR2(100);
3370 l_display_url VARCHAR2(20) := 'Y';
3371
3372 type select_line_info_Cursor is ref cursor ;
3373 Porcpt_LineInfo select_line_info_Cursor;
3374
3375 x_allow_inv_dest_receipts Varchar2(20) := FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
3376
3377 NL VARCHAR2(1) := fnd_global.newline;
3378 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
3379
3380
3381 BEGIN
3382
3383 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
3384 l_temp := substr(document_id, instr(document_id, ':') + 1,
3385 length(document_id) - 2);
3386 l_item_key := substr(l_temp, 1, instr(l_temp, ':') - 1);
3387 l_display_url := substr(l_temp, instr(l_temp, ':') + 1,
3388 length(l_temp) - 2);
3389
3390
3391 x_is_int_req := wf_engine.GetItemAttrText( itemtype => l_item_type,
3392 itemkey => l_item_key,
3393 aname => 'IS_INT_REQ');
3394
3395 -- ash_debug.debug('GET_PO_RCV_NOTIF_MSG value of IS_INT_REQ', x_is_int_req);
3396
3397 if x_is_int_req = 'Y' then
3398
3399 x_header_id := wf_engine.GetItemAttrNumber( itemtype => l_item_type,
3400 itemkey => l_item_key,
3401 aname => 'SO_HEADER_ID');
3402
3403 else
3404
3405 x_header_id := PO_WF_UTIL_PKG.GetItemAttrNumber( itemtype => l_item_type,
3406 itemkey => l_item_key,
3407 aname => 'PO_NUM_REL_NUM');
3408 -- aname => 'PO_NUMBER');
3409
3410 end if;
3411
3412 -- ash_debug.debug('GET_PO_RCV_NOTIF_MSG value of header_is', x_header_id);
3413
3414 x_org_id := wf_engine.GetItemAttrNumber( itemtype => l_item_type,
3415 itemkey => l_item_key,
3416 aname => 'ORG_ID');
3417
3418 setOrgCtx(x_org_id);
3419
3420 -- ash_debug.debug('GET_PO_RCV_NOTIF_MSG value of org_id', x_org_id);
3421
3422 x_requester_id := wf_engine.GetItemAttrNumber( itemtype => l_item_type,
3423 itemkey => l_item_key,
3424 aname => 'REQUESTER_ID');
3425
3426 x_exp_receipt_date := wf_engine.GetItemAttrDate ( itemtype => l_item_type,
3427 itemkey => l_item_key,
3428 aname => 'DUE_DATE');
3429
3430
3431
3432 x_count := get_count(l_item_type,l_item_key);
3433
3434 -- ash_debug.debug('GET_PO_RCV_NOTIF_MSG value of x_count', x_count);
3435
3436 l_rcv_items_url := wf_engine.GetItemAttrText (itemtype => l_item_type,
3437 itemkey => l_item_key,
3438 aname => 'RCV_ORDERS_URL');
3439
3440 l_rcv_items_url := '<a href="'|| l_rcv_items_url || '">' ||
3441 fnd_message.get_string('ICX', 'ICX_POR_GO_TO_RCV') || '</a>';
3442
3443
3444 --ash_debug.debug('GET_PO_RCV_NOTIF_MSG value of l_rcv_items_url', l_rcv_items_url);
3445 --ash_debug.debug('GET_PO_RCV_NOTIF_MSG value of display_type', display_type);
3446
3447 if (display_type = 'text/html') then
3448
3449 -- style sheet
3450 IF (x_count > 0) then
3451 --l_document := '<base href="' || l_base_href || '">' || nl || '<!---CONFIRM RECEIPT NOTIFICATION-->';
3452 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_href || '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
3453
3454
3455 generate_html_header(l_item_type,l_item_key,x_count,l_document);
3456
3457 --ash_debug.debug('GET_PO_RCV_NOTIF_MSG doc1:', l_document);
3458
3459
3460 l_document := l_document || '<TR><TD></TD><TD><TABLE border=0 width=100% cellpadding=5 cellspacing=1 summary="' || fnd_message.get_string('ICX','ICX_POR_TBL_CONFIRM_RECEIPT') ||'">' || nl;
3461
3462
3463 l_document := l_document || '<TH class="tableheader" align=left id="catLine_1" >' ||
3464 fnd_message.get_string('ICX','ICX_POR_CAT_LINE') || ' </TH>' || NL;
3465
3466
3467 l_document := l_document || NL;
3468 l_document := l_document || '<TH class="tableheader" align=left id="itemDesc_1" >' ||
3469 fnd_message.get_string('ICX','ICX_POR_PRMPT_ITEM_DESCRIPTION') ||
3470 ' </TH>' || NL;
3471
3472 l_document := l_document || '<TH class="tableheader" align=left id="catUnit_1" >' ||
3473 fnd_message.get_string('ICX','ICX_POR_CAT_UNIT') || ' </TH>' || NL;
3474
3475 l_document := l_document || '<TH class="tableheader" align=left id="qtyReceived_1" >' ||
3476 fnd_message.get_string('ICX','ICX_POR_PRMPT_QTY_RECEIVED') || ' </TH>' || NL;
3477
3478 l_document := l_document || '<TH class="tableheader" align=left id="qtyOrdered_1" >' ||
3479 fnd_message.get_string('ICX','ICX_POR_PRMPT_QTY_ORDERED') || ' </TH>' || NL;
3480
3481 l_document := l_document || '<TH class="tableheader" align=left id="currency_1" >' ||
3482 fnd_message.get_string('ICX','ICX_POR_PRMPT_CURRENCY') || ' </TH>' || NL;
3483
3484 l_document := l_document || '<TH class="tableheader" align=left id="catPrice_1" >' ||
3485 fnd_message.get_string('ICX','ICX_POR_CAT_PRICE') || ' </TH>' || NL;
3486
3487 l_document := l_document || '<TH class="tableheader" align=left id="order_1" >' ||
3488 fnd_message.get_string('ICX','ICX_POR_PRMPT_ORDER') || ' </TH>' || NL;
3489
3490 l_document := l_document || '</TR>' ;
3491 l_document_1 := NULL;
3492
3493 --ash_debug.debug('GET_PO_RCV_NOTIF_MSG doc2:', l_document);
3494
3495 get_receipt_lines(x_header_id,x_requester_id,x_exp_receipt_date,x_is_int_req,l_document_1);
3496
3497 l_document_1:= l_document_1 || '<TR><TD></TD><TD colspan=8 height=20><img src=' || l_base_href || '/OA_MEDIA/PORTRANS.gif ALT=""></TD></TR>' || nl;
3498
3499
3500 l_document_1 := l_document_1 || '<TR><TD></TD><TD class=instructiontext colspan=8> ' || fnd_message.get_string('ICX','icx_por_prmpt_confirm_note') || ' </TD></TR>'|| nl;
3501
3502 l_document_1 := l_document_1 || '<TR><TD></TD><TD colspan=2 height=20><img src=' || l_base_href || '/OA_MEDIA/PORTRANS.gif ALT=""></TD></TR>' || nl;
3503
3504 IF (l_display_url = 'Y') then
3505 l_document_1:= l_document_1 || '<tr> <td></td><td colspan=2 align=left > ' || l_rcv_items_url || '</td></tr>' || NL;
3506 END IF;
3507
3508
3509 l_document := l_document || l_document_1 || '</P></TR> ' ;
3510
3511 l_document := l_document || '<TR><TD></TD><TD colspan=2 height=20><img src=' || l_base_href || '/OA_MEDIA/PORTRANS.gif ALT=""></TD></TR>' || nl;
3512
3513
3514 l_document := l_document || '</TABLE> ' || nl;
3515
3516 ELSE /* x_count =0 */
3517
3518 l_document := l_document || '<TABLE width="90%" border=0 cellpadding=0 cellspacing=0 SUMMARY="">' || nl;
3519 l_document := l_document || '<TR> <TD class=confirmationtext align=left>' || fnd_message.get_string('ICX','ICX_POR_CONFIRM_NO_ACTION_REQD') || ' </TD></tr> </table>' || NL;
3520
3521
3522 END IF; /* end of x_count */
3523
3524
3525 end if;
3526
3527 document := l_document;
3528 EXCEPTION
3529 WHEN OTHERS THEN
3530 wf_core.context ('PORCPTWF','GET_PO_RCV_NOTIF','SQL error ' || sqlcode);
3531
3532 END;
3533
3534
3535 procedure setOrgCtx (x_org_id IN NUMBER) is
3536
3537 begin
3538
3539 if x_org_id is not NULL then
3540 PO_MOAC_UTILS_PVT.set_org_context(x_org_id);
3541 end if;
3542
3543 end setOrgCtx;
3544
3545 /*===========================================================================
3546 PROCEDURE NAME: Populate_Order_Info
3547
3548 DESCRIPTION: This function is to populate the laster distribution list
3549
3550 CHANGE HISTORY: SVASAMSE 13/05/2003 Created
3551 ===========================================================================*/
3552 Function Populate_Order_Info(itemtype in varchar2,
3553 itemkey in varchar2,
3554 skip_distribution_updation in varchar2 default 'N') Return number
3555 IS
3556 x_allow_inv_dest_receipts Varchar2(20) :=
3557 FND_PROFILE.value('POR_ALLOW_INV_DEST_RECEIPTS');
3558 x_po_header_id NUMBER;
3559 x_org_id NUMBER;
3560 x_requester_id NUMBER;
3561 x_exp_receipt_date DATE;
3562 x_dist_id NUMBER;
3563 x_qty_inv NUMBER;
3564 l_ntf_trig VARCHAR2(25);
3565 conf_item_key VARCHAR2(100);
3566 line_count NUMBER;
3567 x_qty_rec NUMBER;
3568
3569 type select_line_info_Cursor is ref cursor ;
3570 Porcpt_LineInfo select_line_info_Cursor;
3571
3572 BEGIN
3573 x_po_header_id := wf_engine.GetItemAttrNumber(
3574 itemtype => itemtype,
3575 itemkey => itemkey,
3576 aname => 'PO_HEADER_ID');
3577
3578 x_org_id := wf_engine.GetItemAttrNumber(
3579 itemtype => itemtype,
3580 itemkey => itemkey,
3581 aname => 'ORG_ID');
3582 setOrgCtx(x_org_id);
3583
3584 x_requester_id := wf_engine.GetItemAttrNumber(
3585 itemtype => itemtype,
3586 itemkey => itemkey,
3587 aname => 'REQUESTER_ID');
3588
3589 x_exp_receipt_date := wf_engine.GetItemAttrDate (
3590 itemtype => itemtype,
3591 itemkey => itemkey,
3592 aname => 'DUE_DATE');
3593
3594 if x_allow_inv_dest_receipts is NULL then
3595 x_allow_inv_dest_receipts := 'N';
3596 end if;
3597
3598 if x_allow_inv_dest_receipts = 'N' then
3599
3600 OPEN Porcpt_LineInfo for
3601
3602 -- query to retrieve only the distribution
3603 -- id's and invoiced quantity. And adding new query
3604 -- to retrieve the Invoiced matched lines.
3605
3606 SELECT rcv.po_distribution_id,
3607 rcv.quantity_invoiced,
3608 rcv.quantity_delivered
3609 FROM POR_RCV_ALL_ITEMS_V1 rcv
3610 WHERE rcv.po_header_ID = x_po_header_ID
3611 AND rcv.requestor_ID = x_requester_ID
3612 AND ((rcv.expected_receipt_date is not null
3613 AND trunc(rcv.expected_receipt_date + 1) <= trunc(SYSDATE))
3614 OR EXISTS (SELECT 1 FROM ap_holds aph
3615 WHERE aph.line_location_id = rcv.po_line_location_id
3616 AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
3617 AND aph.release_lookup_code IS NULL
3618 AND rcv.quantity_invoiced > quantity_delivered
3619 AND rcv.quantity_invoiced <= ordered_qty))
3620 AND NVL(rcv.receipt_required_flag,'N') = 'Y'
3621 AND rcv.destination_type_code = 'EXPENSE'
3622 AND rcv.expected_receipt_qty > 0
3623 ORDER BY po_distribution_id;
3624 else
3625 OPEN Porcpt_LineInfo for
3626
3627 -- query to retrieve only the distribution
3628 -- id's and invoiced quantity. And adding new query
3629 -- to retrieve the Invoiced matched lines.
3630
3631 SELECT rcv.po_distribution_id,
3632 rcv.quantity_invoiced,
3633 rcv.quantity_delivered
3634 FROM POR_RCV_ALL_ITEMS_V1 rcv
3635 WHERE rcv.po_header_ID = x_po_header_ID
3636 AND rcv.requestor_ID = x_requester_ID
3637 AND ((rcv.expected_receipt_date is not null
3638 AND trunc(rcv.expected_receipt_date + 1) <= trunc(SYSDATE))
3639 OR EXISTS (SELECT 1 FROM ap_holds aph
3640 WHERE aph.line_location_id = rcv.po_line_location_id
3641 AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
3642 AND aph.release_lookup_code IS NULL
3643 AND rcv.quantity_invoiced > quantity_delivered
3644 AND rcv.quantity_invoiced <= ordered_qty))
3645 AND NVL(rcv.receipt_required_flag,'N') = 'Y'
3646 AND rcv.expected_receipt_qty > 0
3647 ORDER BY po_distribution_id;
3648
3649 end if; /** inv dest receipts allowed check **/
3650
3651 FOR I IN 1..200 LOOP
3652 FETCH Porcpt_LineInfo
3653 INTO x_dist_id, x_qty_inv, x_qty_rec;
3654
3655 EXIT WHEN Porcpt_LineInfo%NOTFOUND;
3656
3657 -- If atleast one line has invoice match then we should
3658 -- display the 'Receive Up To Amount Invoiced' button.
3659 -- The variable l_ntf_trig is set to INV_MATCH if qty is > 0
3660 -- By default the value of it is 'NBD_TRIG'
3661 If (x_qty_inv > x_qty_rec ) then
3662 l_ntf_trig := 'INV_MATCH';
3663 end if;
3664
3665 -- update the distribution data only if the skip validation param is not Y
3666 if (skip_distribution_updation <> 'Y') then
3667 -- Get the wf_item_key item attribute value
3668 conf_item_key := PO_WF_UTIL_PKG.GetItemAttrText(
3669 itemtype => itemtype,
3670 itemkey => itemkey,
3671 aname => 'WF_ITEM_KEY');
3672
3673 -- Update the PO_DISTRIBUTIONS_ALL table with this
3674 -- item key value and qty invoiced value
3675 UPDATE PO_DISTRIBUTIONS_ALL
3676 SET WF_ITEM_KEY = conf_item_key,
3677 invoiced_val_in_ntfn = x_qty_inv
3678 WHERE po_distribution_id = x_dist_id;
3679 end if;
3680
3681 line_count := line_count+1;
3682
3683 END LOOP;
3684 CLOSE Porcpt_LineInfo;
3685
3686 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
3687 itemkey => itemkey,
3688 aname => 'NTF_TRIGGERED_BY',
3689 avalue => l_ntf_trig);
3690
3691 -- set the current date. This is used to verify for need by
3692 -- date trigerred lines during 'Receive in full' action
3693 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
3694 itemkey => itemkey,
3695 aname => 'NTF_TRIGGERED_DATE',
3696 avalue => sysdate);
3697 return line_count;
3698 END Populate_Order_Info;
3699
3700 /*===========================================================================
3701 PROCEDURE NAME: Process_rcv_amt_billed
3702
3703 DESCRIPTION: This procedure processes the Receiving Transaction
3704 interface when the workflow notification is reponsed
3705 as 'Receive Upto Amount Billed'.
3706
3707 It checks to ensure that the shipment(s) is/are still
3708 opened. It invokes the Receiving Transaction interface
3709 procedure to insert the receipt records into the
3710 receiving transaction interface table.
3711
3712 The Receiving Transaction Manager is then called in
3713 'ON-LINE' mode to process the receipt records immediately.
3714
3715 If there are errors returned from the Receiving
3716 Transaction Manager, the error status is set the
3717 workflow item attribute for notifying the buyer and
3718 requester of the error.
3719
3720 CHANGE HISTORY: SVASAMSE 13/05/2003 Created
3721 ===========================================================================*/
3722
3723 PROCEDURE Process_rcv_amt_billed(itemtype in varchar2,
3724 itemkey in varchar2,
3725 actid in number,
3726 funmode in varchar2,
3727 result out NOCOPY varchar2)
3728 IS
3729 TYPE shipment_orders_cursor IS ref CURSOR;
3730 Porcpt_Shipment shipment_orders_cursor;
3731
3732 x_group_id NUMBER;
3733 x_RCV_txns_rc NUMBER := 0;
3734 x_po_header_id NUMBER;
3735 x_requester_id NUMBER;
3736
3737 x_org_id NUMBER;
3738 x_requester_username WF_USERS.NAME%TYPE; -- Use the requester username to be passed to initialize
3739 x_rcv_trans_status VARCHAR2(500) := NULL;
3740 x_insert_txns_status NUMBER;
3741
3742 t_po_header_id rcvNumberArray;
3743 t_line_location_id rcvNumberArray;
3744 t_expected_receipt_qty rcvNumberArray;
3745 t_ordered_uom rcvVarcharArray;
3746 t_item_id rcvNumberArray;
3747 t_primary_uom_class rcvVarcharArray;
3748 t_org_id rcvNumberArray;
3749 t_po_distribution_id rcvNumberArray;
3750 t_Comments rcvVarcharArray;
3751 t_PackingSlip rcvVarcharArray;
3752 t_WayBillNum rcvVarcharArray;
3753
3754 x_message_token VARCHAR2(2000);
3755 x_progress varchar2(1000):= '001';
3756 BEGIN
3757
3758 IF ( funmode = 'RUN' ) THEN
3759 --
3760 x_po_header_id := wf_engine.GetItemAttrNumber(
3761 itemtype => itemtype,
3762 itemkey => itemkey,
3763 aname => 'PO_HEADER_ID');
3764
3765 -- Setup the organization context for the multi-org environment
3766
3767 x_org_id := wf_engine.GetItemAttrNumber(
3768 itemtype => itemtype,
3769 itemkey => itemkey,
3770 aname => 'ORG_ID');
3771
3772 -- Setup the organization context for the multi-org environment
3773 setOrgCtx(x_org_id);
3774 --Use the requester username to be passed to initialize
3775 x_requester_username := wf_engine.GetItemAttrText ( itemtype => itemtype,
3776 itemkey => itemkey,
3777 aname => 'REQUESTER_USERNAME');
3778
3779 PORCPTWF.initialize(x_requester_username,x_org_id);
3780
3781 /** rewrite after initialize **/
3782 x_requester_id := wf_engine.GetItemAttrNumber(
3783 itemtype => itemtype,
3784 itemkey => itemkey,
3785 aname => 'REQUESTER_ID');
3786
3787 SELECT rcv_interface_groups_s.nextval
3788 INTO x_group_id
3789 FROM sys.dual;
3790
3791 OPEN Porcpt_Shipment for
3792 SELECT po_header_id,
3793 po_line_location_id,
3794 decode(SIGN(invoiced_val_in_ntfn-quantity_delivered),1,
3795 (invoiced_val_in_ntfn-quantity_delivered),0)
3796 expected_receipt_qty,
3797 primary_uom,
3798 item_id,
3799 primary_uom_class,
3800 to_organization_id,
3801 po_distribution_id,
3802 null,
3803 null,
3804 null
3805 FROM POR_RCV_ALL_ITEMS_V1
3806 WHERE po_header_ID = x_po_header_ID
3807 AND wf_item_key = itemKey;
3808
3809 FETCH porcpt_Shipment BULK COLLECT into t_po_header_id,
3810 t_line_location_id,
3811 t_expected_receipt_qty,
3812 t_ordered_uom,
3813 t_item_id,
3814 t_primary_uom_class,
3815 t_org_id,
3816 t_po_distribution_id,
3817 t_Comments,
3818 t_PackingSlip,
3819 t_WayBillNum;
3820
3821 CLOSE Porcpt_Shipment;
3822
3823 for i in 1..t_po_header_id.count loop
3824 x_progress := 'poheaderid*' ||to_char(t_po_header_id(i))
3825 || '*t_line_location_id*' ||to_char(t_line_location_id(i))
3826 || '*ex_rcpt_qty*' || t_expected_receipt_qty(i)
3827 || '*uom*' || t_ordered_uom(i)
3828 || '*itemid*' || to_char(t_item_id(i))
3829 || '*uom_class*' || t_primary_uom_class(i)
3830 || '*org_id*' || to_char(t_org_id(i))
3831 || '*t_po_distribution_id*' || to_char(t_po_distribution_id(i))
3832 || '*comments*' || t_comments(i)
3833 || '*pkgSlip*' || t_packingSlip(i)
3834 || '*waybillnum*' || t_waybillNum(i);
3835 x_progress := x_progress || '*x_group_id*' || to_char(x_group_id);
3836
3837 IF (g_po_wf_debug = 'Y') THEN
3838 po_wf_debug_pkg.insert_debug(itemtype, itemkey, x_progress);
3839 END IF;
3840 end loop;
3841
3842 x_insert_txns_status := POR_RCV_ORD_SV.groupPoTransaction(
3843 t_po_header_id,
3844 t_line_location_id,
3845 t_expected_receipt_qty,
3846 t_ordered_uom,
3847 SYSDATE,
3848 t_item_id,
3849 t_primary_uom_class,
3850 t_org_id,
3851 t_po_distribution_id,
3852 x_group_id,
3853 'WP4_CONFIRM',
3854 t_Comments,
3855 t_PackingSlip,
3856 t_WayBillNum);
3857
3858 IF x_insert_txns_status = 0 THEN
3859 x_RCV_txns_rc := por_rcv_ord_sv.process_transactions(X_group_id, 'WF');
3860
3861 -- At least one of the receiving transactions inserted
3862 IF x_RCV_txns_rc is NULL OR x_RCV_txns_rc = 0 THEN
3863 RESULT := 'PASSED';
3864
3865 -- Clean the po distribtions table
3866 update po_distributions
3867 set wf_item_key = ''
3868 where wf_item_key = itemKey;
3869
3870 ELSE
3871 get_txn_error_message(x_group_id, x_RCV_txns_rc, x_rcv_trans_status, x_message_token);
3872
3873 wf_engine.SetItemAttrText ( itemtype => Itemtype,
3874 itemkey => Itemkey,
3875 aname => 'RCV_TRANS_STATUS',
3876 avalue => x_rcv_trans_status );
3877
3878 wf_engine.SetItemAttrText ( itemtype => Itemtype,
3879 itemkey => Itemkey,
3880 aname => 'RCV_ERR_MSG_TOKEN',
3881 avalue => x_message_token );
3882 RESULT := 'FAILED';
3883 END IF;
3884 ELSE
3885
3886 x_rcv_trans_status := 'RCV_RCPT_INSERT_FAILED';
3887 wf_engine.SetItemAttrText(itemtype => Itemtype,
3888 itemkey => Itemkey,
3889 aname => 'RCV_TRANS_STATUS',
3890 avalue => x_rcv_trans_status );
3891 RESULT := 'FAILED';
3892 END IF;
3893 ELSIF ( funmode = 'CANCEL' ) THEN
3894 null;
3895 END IF;
3896 END Process_rcv_amt_billed;
3897
3898 /*===========================================================================
3899 PROCEDURE NAME: Restart_rcpt_process
3900
3901 DESCRIPTION: This procedure is to restart the confirm receipt
3902 workflow process.
3903
3904 CHANGE HISTORY: SVASAMSE 13/05/2005 Created
3905 ===========================================================================*/
3906 PROCEDURE Restart_rcpt_process(itemtype in varchar2,
3907 itemkey in varchar2,
3908 actid in number,
3909 funmode in varchar2,
3910 result out NOCOPY varchar2)
3911 IS
3912 x_po_header_id number;
3913 x_requester_id number;
3914 x_sys_date date;
3915 x_exp_receipt_date date;
3916 x_WF_ItemKey varchar2(240);
3917 x_revision_num number;
3918 x_po_num_rel_num POR_RCV_ALL_ITEMS_V1.PO_NUM_REL_NUM%type;
3919 BEGIN
3920 x_po_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3921 itemkey => itemkey,
3922 aname => 'PO_HEADER_ID');
3923
3924 x_requester_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3925 itemkey => itemkey,
3926 aname => 'REQUESTER_ID');
3927
3928 x_exp_receipt_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
3929 itemkey => itemkey,
3930 aname => 'DUE_DATE');
3931
3932 x_revision_num := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3933 itemkey => itemkey,
3934 aname => 'PO_REVISION_NUM');
3935
3936 x_po_num_rel_num := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3937 itemkey => itemkey,
3938 aname => 'PO_NUM_REL_NUM');
3939 select sysdate
3940 into x_sys_date
3941 from dual;
3942
3943 -- Create a new item key
3944 x_WF_ItemKey := to_char(x_po_header_id) || ';' ||
3945 to_char(x_requester_id) || ';' ||
3946 to_char(x_sys_date,'DD-MON-YYYY:HH24:MI');
3947
3948 -- Start the Rcpt Process with the new item key
3949 Start_Rcpt_Process(x_po_header_id, x_requester_id, x_exp_receipt_date,
3950 x_WF_ItemKey, x_revision_num, 'N', '-1', x_po_num_rel_num);
3951
3952 END Restart_rcpt_process;
3953
3954 /*===========================================================================
3955 PROCEDURE NAME: Does_invoice_match_exist
3956
3957 DESCRIPTION: This procedure is used to check if there are invoice
3958 matched lines in the notification.
3959 Retruns true if the invoice match exists.
3960
3961 CHANGE HISTORY: SVASAMSE 13/05/2005 Created
3962 ===========================================================================*/
3963 PROCEDURE Does_invoice_match_exist(itemtype in varchar2,
3964 itemkey in varchar2,
3965 actid in number,
3966 funmode in varchar2,
3967 resultout out NOCOPY varchar2)
3968 IS
3969 l_ntf_trig_by varchar2(25);
3970 begin
3971
3972 l_ntf_trig_by := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3973 itemkey => itemkey,
3974 aname => 'NTF_TRIGGERED_BY');
3975
3976 If l_ntf_trig_by = 'INV_MATCH' then
3977 resultout := wf_engine.eng_completed || ':' || 'Y';
3978 else
3979 resultout := wf_engine.eng_completed || ':' || 'N';
3980 end if;
3981
3982 END Does_invoice_match_exist;
3983
3984 END PORCPTWF;