[Home] [Help]
PACKAGE BODY: APPS.RCV_BUSINESS_EVENTS
Source
1 PACKAGE BODY rcv_business_events AS
2 /* $Header: RCVBZEVB.pls 120.2.12000000.4 2007/03/02 05:48:34 sgumaste ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'RCV_BUSINESS_EVENTS';
4 g_debug CONSTANT VARCHAR2(1) := NVL(fnd_profile.VALUE('AFLOG_ENABLED'), 'N');
5 g_log_head CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name;
6
7 PROCEDURE raise_receive_txn(
8 p_group_id NUMBER,
9 p_request_id NUMBER
10 ) IS
11 l_wf_item_seq NUMBER;
12 l_event_name VARCHAR2(100);
13 l_event_key VARCHAR2(100);
14 l_parameter_list1 wf_parameter_list_t := wf_parameter_list_t();
15 l_parameter_list2 wf_parameter_list_t := wf_parameter_list_t();
16 l_osa_flag VARCHAR2(1); --Shikyu project
17
18 CURSOR get_rcv_headers IS
19 SELECT DISTINCT rt.shipment_header_id,
20 GROUP_ID
21 FROM rcv_transactions rt
22 WHERE rt.request_id = p_request_id
23 AND rt.transaction_type = 'RECEIVE'
24 AND NVL(p_group_id, 0) = 0
25 UNION ALL
26 SELECT DISTINCT rt.shipment_header_id,
27 GROUP_ID
28 FROM rcv_transactions rt
29 WHERE rt.GROUP_ID = p_group_id
30 AND rt.request_id = p_request_id
31 AND rt.transaction_type = 'RECEIVE'
32 AND NVL(p_group_id, 0) <> 0;
33 BEGIN
34
35 l_event_name := 'oracle.apps.po.rcv.rcvtxn';
36 wf_event.setdispatchmode('ASYNC');
37
38 BEGIN
39 FOR grh IN get_rcv_headers LOOP
40 SELECT po_wf_itemkey_s.NEXTVAL
41 INTO l_wf_item_seq
42 FROM DUAL;
43
44 l_event_key := TO_CHAR(grh.shipment_header_id) || '-' || TO_CHAR(l_wf_item_seq);
45 --Clear the parameter list
46 --Bug 3481437 - commented out clear
47 --l_parameter_list := wf_parameter_list_t(null);
48 wf_event.addparametertolist(p_name => 'SHIPMENT_HEADER_ID',
49 p_value => grh.shipment_header_id,
50 p_parameterlist => l_parameter_list1
51 );
52 wf_event.RAISE(p_event_name => l_event_name,
53 p_event_key => l_event_key,
54 p_parameters => l_parameter_list1
55 );
56 END LOOP;
57
58 EXCEPTION
59 WHEN OTHERS THEN
60 NULL;
61 END;
62
63 -- Bugfix 5589175, The original insert statement is now divided into 2 separate insert
64 -- statements for performance reason. For online mode request_id might come as 0 or null.
65 -- Hence we first check the request_id for Batch and Imeediate request_id is not null.
66 IF p_request_id = 0 OR p_request_id IS NULL -- On line mode
67 THEN
68 INSERT INTO rcv_staging_table
69 (transaction_id,
70 team,
71 status,
72 transaction_request_id,
73 transaction_group_id,
74 creation_date,
75 created_by,
76 last_update_login,
77 request_id,
78 last_updated_by,
79 last_update_date
80 )
81 SELECT rt.transaction_id,
82 'JMF' team,
83 'PENDING' status,
84 rt.request_id transaction_request_id,
85 rt.GROUP_ID transaction_group_id,
86 SYSDATE creation_date,
87 rt.created_by,
88 NULL last_update_login,
89 NULL request_id,
90 0 last_updated_by,
91 SYSDATE last_update_date
92 FROM rcv_transactions rt,
93 rcv_shipment_lines rsl
94 WHERE rt.GROUP_ID = p_group_id
95 AND rt.shipment_line_id = rsl.shipment_line_id
96 AND rsl.osa_flag = 'Y'
97 AND ( rt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')
98 OR ( rt.transaction_type = 'CORRECT'
99 AND EXISTS(SELECT NULL
100 FROM rcv_transactions prt
101 WHERE prt.transaction_id = rt.parent_transaction_id
102 AND prt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')))
103 );
104 ELSE -- Batch and Immediate mode.
105 INSERT INTO rcv_staging_table
106 (transaction_id,
107 team,
108 status,
109 transaction_request_id,
110 transaction_group_id,
111 creation_date,
112 created_by,
113 last_update_login,
114 request_id,
115 last_updated_by,
116 last_update_date
117 )
118 SELECT rt.transaction_id,
119 'JMF' team,
120 'PENDING' status,
121 rt.request_id transaction_request_id,
122 rt.GROUP_ID transaction_group_id,
123 SYSDATE creation_date,
124 rt.created_by,
125 NULL last_update_login,
126 NULL request_id,
127 0 last_updated_by,
128 SYSDATE last_update_date
129 FROM rcv_transactions rt,
130 rcv_shipment_lines rsl
131 WHERE rt.request_id = p_request_id
132 AND ( rt.GROUP_ID = p_group_id
133 OR p_group_id = 0
134 OR p_group_id IS NULL)
135 AND rt.shipment_line_id = rsl.shipment_line_id
136 AND rsl.osa_flag = 'Y'
137 AND ( rt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')
138 OR ( rt.transaction_type = 'CORRECT'
139 AND EXISTS(SELECT NULL
140 FROM rcv_transactions prt
141 WHERE prt.transaction_id = rt.parent_transaction_id
142 AND prt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')))
143 );
144 END IF;
145 -- End of code for bugfix 5589175
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 NULL; -- We don't want to fail the transaction
150 END;
151 END rcv_business_events;