[Home] [Help]
PACKAGE BODY: APPS.RCV_BUSINESS_EVENTS
Source
1 PACKAGE BODY rcv_business_events AS
2 /* $Header: RCVBZEVB.pls 120.5.12020000.2 2012/07/10 09:26:32 ptkumar 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 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();--opsm change
18
19 CURSOR get_rcv_headers IS
20 SELECT DISTINCT rt.shipment_header_id,
21 GROUP_ID
22 FROM rcv_transactions rt
23 WHERE rt.request_id = p_request_id
24 AND rt.transaction_type = 'RECEIVE'
25 AND NVL(p_group_id, 0) = 0
26 UNION ALL
27 SELECT DISTINCT rt.shipment_header_id,
28 GROUP_ID
29 FROM rcv_transactions rt
30 WHERE rt.GROUP_ID = p_group_id
31 AND rt.request_id in (0, -1, p_request_id) -- for bug 8422764
32 AND rt.transaction_type = 'RECEIVE'
33 AND NVL(p_group_id, 0) <> 0;
34 --------------------------------
35 --Modified for OPSM
36 --------------------------------
37 CURSOR get_rcv_headers_rma IS
38 SELECT DISTINCT rt.shipment_header_id,rt.group_id
39 FROM rcv_transactions rt
40 WHERE rt.request_id = p_request_id
41 AND rt.transaction_type = 'DELIVER'
42 AND nvl(p_group_id,0) = 0
43 UNION ALL
44 SELECT DISTINCT rt.shipment_header_id,rt.group_id
45 FROM rcv_transactions rt
46 WHERE rt.group_id = p_group_id
47 AND rt.request_id in (0, -1, p_request_id)
48 AND rt.transaction_type = 'DELIVER'
49 AND nvl(p_group_id,0) <> 0;
50 --------------------------------
51 --Modified for OPSM
52 --------------------------------
53 BEGIN
54
55 l_event_name := 'oracle.apps.po.rcv.rcvtxn';
56 wf_event.setdispatchmode('ASYNC');
57
58 BEGIN
59 FOR grh IN get_rcv_headers LOOP
60 SELECT po_wf_itemkey_s.NEXTVAL
61 INTO l_wf_item_seq
62 FROM DUAL;
63
64 l_event_key := TO_CHAR(grh.shipment_header_id) || '-' || TO_CHAR(l_wf_item_seq);
65 --Clear the parameter list
66 --Bug 3481437 - commented out clear
67 --l_parameter_list := wf_parameter_list_t(null);
68 wf_event.addparametertolist(p_name => 'SHIPMENT_HEADER_ID',
69 p_value => grh.shipment_header_id,
70 p_parameterlist => l_parameter_list1
71 );
72 wf_event.RAISE(p_event_name => l_event_name,
73 p_event_key => l_event_key,
74 p_parameters => l_parameter_list1
75 );
76 END LOOP;
77
78 EXCEPTION
79 WHEN OTHERS THEN
80 NULL;
81 END;
82
83 ----------------------------
84 -------Modified For OPSM--
85 ----------------------------
86 BEGIN
87 For grhr in get_rcv_headers_rma loop
88 select po_wf_itemkey_s.nextval into l_wf_item_seq from dual;
89 wf_event.addparametertolist(p_name => 'SHIPMENT_HEADER_ID',
90 p_value => grhr.shipment_header_id,
91 p_parameterlist => l_parameter_list);
92 wf_event.addparametertolist(p_name => 'GROUP_ID',
93 p_value => grhr.group_id,
94 p_parameterlist => l_parameter_list);
95
96 WF_EVENT.raise(p_event_name => 'oracle.apps.po.rcv.rcvtxn.outbound'
97 ,p_event_key => l_wf_item_seq
98 ,p_parameters => l_parameter_list
99 ,p_send_date => SYSDATE );
100 end loop;
101
102 EXCEPTION
103 WHEN OTHERS THEN
104 NULL;
105 END;
106
107 -----------------------
108 ---Modified For OPSM--
109 ----------------------
110
111 -- Bugfix 5589175, The original insert statement is now divided into 2 separate insert
112 -- statements for performance reason. For online mode request_id might come as 0 or null.
113 -- Hence we first check the request_id for Batch and Imeediate request_id is not null.
114 IF p_request_id = 0 OR p_request_id IS NULL -- On line mode
115 THEN
116 INSERT INTO rcv_staging_table
117 (transaction_id,
118 team,
119 status,
120 transaction_request_id,
121 transaction_group_id,
122 creation_date,
123 created_by,
124 last_update_login,
125 request_id,
126 last_updated_by,
127 last_update_date
128 )
129 SELECT rt.transaction_id,
130 'JMF' team,
131 'PENDING' status,
132 rt.request_id transaction_request_id,
133 rt.GROUP_ID transaction_group_id,
134 SYSDATE creation_date,
135 rt.created_by,
136 NULL last_update_login,
137 NULL request_id,
138 0 last_updated_by,
139 SYSDATE last_update_date
140 FROM rcv_transactions rt,
141 rcv_shipment_lines rsl
142 WHERE rt.GROUP_ID = p_group_id
143 AND rt.shipment_line_id = rsl.shipment_line_id
144 AND rsl.osa_flag = 'Y'
145 AND ( rt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')
146 OR ( rt.transaction_type = 'CORRECT'
147 AND EXISTS(SELECT NULL
148 FROM rcv_transactions prt
149 WHERE prt.transaction_id = rt.parent_transaction_id
150 AND prt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')))
151 );
152 ELSE -- Batch and Immediate mode.
153 INSERT INTO rcv_staging_table
154 (transaction_id,
155 team,
156 status,
157 transaction_request_id,
158 transaction_group_id,
159 creation_date,
160 created_by,
161 last_update_login,
162 request_id,
163 last_updated_by,
164 last_update_date
165 )
166 SELECT rt.transaction_id,
167 'JMF' team,
168 'PENDING' status,
169 rt.request_id transaction_request_id,
170 rt.GROUP_ID transaction_group_id,
171 SYSDATE creation_date,
172 rt.created_by,
173 NULL last_update_login,
174 NULL request_id,
175 0 last_updated_by,
176 SYSDATE last_update_date
177 FROM rcv_transactions rt,
178 rcv_shipment_lines rsl
179 WHERE rt.request_id = p_request_id
180 AND ( rt.GROUP_ID = p_group_id
181 OR p_group_id = 0
182 OR p_group_id IS NULL)
183 AND rt.shipment_line_id = rsl.shipment_line_id
184 AND rsl.osa_flag = 'Y'
185 AND ( rt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')
186 OR ( rt.transaction_type = 'CORRECT'
187 AND EXISTS(SELECT NULL
188 FROM rcv_transactions prt
189 WHERE prt.transaction_id = rt.parent_transaction_id
190 AND prt.transaction_type IN('RECEIVE', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')))
191 );
192 END IF;
193 -- End of code for bugfix 5589175
194
195 EXCEPTION
196 WHEN OTHERS THEN
197 NULL; -- We don't want to fail the transaction
198 END;
199 END rcv_business_events;