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