[Home] [Help]
PACKAGE BODY: APPS.MSC_A2A_XML_WF
Source
1 package body MSC_A2A_XML_WF as
2 /* $Header: MSCXMLWB.pls 120.1 2011/08/11 10:56:43 vjuluri ship $*/
3
4 v_parameter_list wf_parameter_list_t;
5
6
7 -- =========== Private Functions =============
8
9 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
10 IS
11 BEGIN
12 IF fnd_global.conc_request_id > 0 THEN
13 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
14 ELSE
15 null;
16 --DBMS_OUTPUT.PUT_LINE( pBUFF);
17 END IF;
18 EXCEPTION
19 WHEN OTHERS THEN
20 RETURN;
21 END LOG_MESSAGE;
22
23 PROCEDURE SEND_XML (p_map_code IN VARCHAR2,
24 p_instance_id IN NUMBER,
25 p_document_id IN VARCHAR2 DEFAULT NULL,
26 p_parameter1 IN VARCHAR2 DEFAULT NULL,
27 p_parameter2 IN VARCHAR2 DEFAULT NULL,
28 p_parameter3 IN VARCHAR2 DEFAULT NULL,
29 p_parameter4 IN VARCHAR2 DEFAULT NULL,
30 p_parameter5 IN VARCHAR2 DEFAULT NULL) IS
31 TYPE CurTyp IS REF CURSOR;
32 c_instance CurTyp;
33 c_info CurTyp;
34
35 lv_msgid RAW(100);
36 lv_instance_code VARCHAR2(10):='';
37 lv_debug_mode PLS_INTEGER := 0;
38 lv_username VARCHAR2(512);
39 lv_password VARCHAR2(500);
40 lv_protocol_type VARCHAR2(30);
41 lv_protocol_address VARCHAR2(512);
42 l_parameter_list wf_parameter_list_t; -- for raising event
43
44 BEGIN
45
46 -- get the instance code
47 OPEN c_instance FOR
48 ' select instance_code from msc_apps_instances mai '
49 ||' where mai.instance_id = :instance_id' USING p_instance_id;
50
51 LOOP
52
53 FETCH c_instance into lv_instance_code;
54
55 EXIT WHEN c_instance%NOTFOUND;
56
57 -- get the username, password, protocol type and address
58 OPEN c_info FOR
59 ' select usr.username, usr.password,'
60 ||' hub.protocol_type, hub.protocol_address '
61 ||' FROM ecx_hubs hub, ecx_hub_users usr '
62 ||' where hub.hub_id = usr.hub_id '
63 ||' and usr.hub_entity_code = :instance_code ' USING lv_instance_code;
64 FETCH c_info into lv_username, lv_password, lv_protocol_type, lv_protocol_address;
65 CLOSE c_info;
66
67
68 -- if no record found default protocol address to Instance Code and type to 'NONE'
69 IF lv_protocol_type IS NULL THEN
70 lv_protocol_type:= 'NONE';
71 END IF;
72 IF lv_protocol_address IS NULL THEN
73 lv_protocol_address := lv_instance_code;
74 END IF;
75
76 -- get the debug level. If MRP: Debug Mode is yes, set it to 3
77 IF FND_PROFILE.VALUE('MRP_DEBUG') = 'Y' THEN
78 lv_debug_mode := 3;
79 ELSE
80 lv_debug_mode := 0;
81 END IF;
82 -- Initialize the ecx_utils.i_ret_code and i_errbuf variables;
83 ecx_utils.i_ret_code :=0;
84 ecx_utils.i_errbuf := null;
85
86 -- send the message to the queue
87 ECX_OUTBOUND.putmsg (
88 NULL,
89 NULL,
90 NULL,
91 NULL,
92 NULL,
93 p_document_id,
94 p_parameter1,
95 p_parameter2,
96 p_parameter3,
97 p_parameter4,
98 p_parameter5,
99 p_map_code,
100 NULL,
101 NULL,
102 NULL,
103 NULL,
104 NULL,
105 lv_protocol_type,
106 lv_protocol_address,
107 lv_username,
108 lv_password,
109 NULL,
110 NULL,
111 NULL,
112 p_map_code,
113 NULL,
114 lv_debug_mode,
115 NULL,
116 lv_msgid
117 );
118
119 CLOSE c_instance;
120
121 END LOOP;
122
123 -- Raise Event to which system integrators can subscribe to annd launch their workflows
124 -- after the message has been queued in the outbound queue
125
126 wf_event.AddParameterToList(p_name=>'p_map_code',
127 p_value=> 'MSC_PLANSCHDO_OAG71_OUT',
128 p_parameterlist=>l_parameter_list);
129
130 wf_event.raise( p_event_name => 'oracle.apps.msc.ascp.out',
131 p_event_key => to_char(sysdate,'YYYYMMDD HH24MISS'),
132 p_parameters => l_parameter_list);
133
134 l_parameter_list.DELETE;
135
136 -- dbms_output.put_line('SEND_XML complete ');
137 EXCEPTION
138 when others then
139 log_message ('Error in SEND_XML: '||substr(SQLERRM,1,240));
140 END SEND_XML;
141
142
143 -- public procedures -----------------
144 PROCEDURE PURGE_INTERFACE(p_map_code IN VARCHAR2, p_unique_id IN NUMBER) IS
145 BEGIN
146
147 IF p_map_code = 'MSC_POO_OAG71_OUT' THEN
148 DELETE MSC_PO_RESCHEDULE_INTERFACE
149 WHERE purchase_order_id = p_unique_id;
150
151 ELSIF p_map_code = 'MSC_REQUISITNO_OAG71_OUT' THEN
152 DELETE MSC_PO_REQUISITIONS_INTERFACE
153 WHERE source_line_id = p_unique_id;
154
155 ELSIF p_map_code = 'MSC_PRODORDERO_OAG71_OUT' THEN
156 DELETE MSC_WIP_JOB_SCHEDULE_INTERFACE
157 WHERE source_line_id = p_unique_id;
158
159 DELETE MSC_WIP_JOB_DTLS_INTERFACE
160 WHERE parent_header_id = p_unique_id;
161
162 ELSIF p_map_code = 'MSC_PRODORDERC_OAG71_OUT' THEN
163 DELETE MSC_WIP_JOB_SCHEDULE_INTERFACE
164 WHERE source_line_id = p_unique_id;
165
166 DELETE MSC_WIP_JOB_DTLS_INTERFACE
167 WHERE parent_header_id = p_unique_id;
168
169 END IF;
170
171 END;
172
173 PROCEDURE RESCHEDULE_PO(p_map_code IN VARCHAR2 DEFAULT 'MSC_POO_OAG71_OUT', p_instance_id IN NUMBER, p_purchase_order_id IN NUMBER) IS
174
175 BEGIN
176
177 SEND_XML (p_map_code , p_instance_id, NULL, p_instance_id, p_purchase_order_id);
178 IF ecx_utils.i_ret_code = 0 THEN
179 PURGE_INTERFACE(p_map_code , p_purchase_order_id);
180 END IF;
181
182 end RESCHEDULE_PO;
183
184
185 PROCEDURE CREATE_REQ(p_map_code IN VARCHAR2 DEFAULT 'MSC_REQUISITNO_OAG71_OUT', p_source_line_id IN NUMBER, p_instance_id IN NUMBER) IS
186
187 BEGIN
188
189 SEND_XML (p_map_code , p_instance_id, p_source_line_id, p_instance_id );
190 IF ecx_utils.i_ret_code = 0 THEN
191 PURGE_INTERFACE(p_map_code , p_source_line_id );
192 END IF;
193
194
195 end CREATE_REQ;
196
197
198 PROCEDURE SYNC_WORK_ORDER(p_map_code IN VARCHAR2 DEFAULT 'MSC_PRODORDERO_OAG71_OUT', p_source_line_id IN NUMBER, p_instance_id IN NUMBER) IS
199
200 BEGIN
201
202 SEND_XML (p_map_code , p_instance_id, p_source_line_id, p_instance_id );
203 IF ecx_utils.i_ret_code = 0 THEN
204 PURGE_INTERFACE(p_map_code , p_source_line_id );
205 END IF;
206
207
208 end SYNC_WORK_ORDER;
209
210 PROCEDURE CREATE_WORK_ORDER(p_map_code IN VARCHAR2 DEFAULT 'MSC_PRODORDERC_OAG71_OUT', p_source_line_id IN NUMBER, p_instance_id IN NUMBER) IS
211
212 BEGIN
213
214 SEND_XML (p_map_code , p_instance_id, p_source_line_id, p_instance_id );
215 IF ecx_utils.i_ret_code = 0 THEN
216 PURGE_INTERFACE(p_map_code , p_source_line_id );
217 END IF;
218
219
220 end CREATE_WORK_ORDER;
221
222
223 PROCEDURE PUSH_PLAN_OUTPUT (p_map_code IN VARCHAR2 DEFAULT 'MSC_PLANSCHDO_OAG71_OUT', p_compile_designator IN VARCHAR2, p_instance_id IN NUMBER, p_buy_items_only IN NUMBER) IS
224
225 BEGIN
226
227 SEND_XML (p_map_code , p_instance_id, p_compile_designator, p_instance_id ,p_buy_items_only);
228
229 end PUSH_PLAN_OUTPUT;
230
231 PROCEDURE LEGACY_RELEASE ( p_instance_id IN NUMBER) IS
232 TYPE cur IS REF CURSOR;
233 c_cur cur;
234 lv_unique_id number;
235
236 BEGIN
237
238 -- reschedule POs
239
240 OPEN c_cur FOR
241 'select distinct purchase_order_id from msc_po_reschedule_interface '
242 ||' where sr_instance_id = :instance_id' USING p_instance_id;
243
244 LOOP
245
246 FETCH c_cur INTO lv_unique_id;
247
248 EXIT WHEN c_cur%NOTFOUND;
249
250 RESCHEDULE_PO(p_map_code =>'MSC_POO_OAG71_OUT',
251 p_instance_id => p_instance_id,
252 p_purchase_order_id =>lv_unique_id );
253
254 END LOOP;
255
256 CLOSE c_cur;
257
258 -- Create Reqs
259
260 OPEN c_cur FOR
261 'select distinct source_line_id from msc_po_requisitions_interface '
262 ||' where sr_instance_id = :instance_id' USING p_instance_id;
263
264 LOOP
265
266 FETCH c_cur INTO lv_unique_id;
267
268 EXIT WHEN c_cur%NOTFOUND;
269
270 CREATE_REQ(p_map_code =>'MSC_REQUISITNO_OAG71_OUT',
271 p_source_line_id =>lv_unique_id,
272 p_instance_id => p_instance_id);
273
274 END LOOP;
275
276 CLOSE c_cur;
277
278 -- Sync Work Orders
279
280 OPEN c_cur FOR
281 'select distinct source_line_id from msc_wip_job_schedule_interface '
282 ||' where sr_instance_id = :instance_id'
283 ||' and load_type in (3,6)' USING p_instance_id;
284
285 LOOP
286
287 FETCH c_cur INTO lv_unique_id;
288
289 EXIT WHEN c_cur%NOTFOUND;
290
291 SYNC_WORK_ORDER(p_map_code =>'MSC_PRODORDERO_OAG71_OUT',
292 p_source_line_id =>lv_unique_id,
293 p_instance_id => p_instance_id);
294
295 END LOOP;
296
297 CLOSE c_cur;
298
299 -- Create Work Orders
300
301 OPEN c_cur FOR
302 'select distinct source_line_id from msc_wip_job_schedule_interface '
303 ||' where sr_instance_id = :instance_id '
304 ||' and load_type not in (3,6)' USING p_instance_id;
305
306 LOOP
307
308 FETCH c_cur INTO lv_unique_id;
309
310 EXIT WHEN c_cur%NOTFOUND;
311
312 CREATE_WORK_ORDER(p_map_code =>'MSC_PRODORDERC_OAG71_OUT',
313 p_source_line_id =>lv_unique_id,
314 p_instance_id => p_instance_id);
315
316 END LOOP;
317
318 CLOSE c_cur;
319
320 end LEGACY_RELEASE;
321
322 end MSC_A2A_XML_WF;