[Home] [Help]
PACKAGE BODY: APPS.M4R_OM_CONF_PKG
Source
1 PACKAGE BODY M4R_OM_CONF_PKG as
2 /* $Header: M4ROMCFB.pls 120.3 2006/03/02 06:10:56 kkram noship $ */
3
4
5 -- Package
6 -- M4R_OM_CONF_PKG
7 --
8 -- Purpose
9 -- Body of package M4R_OM_CONF_PKG.
10 --
11 -- History
12 -- May-14-2005 Ambuj Chaudhary Created
13
14
15
16
17
18
19
20
21 -- Start of comments
22 -- API name : GET_OM_CONF_PARAMS
23 -- Type : Private
24 -- Pre-reqs : None.
25 -- Function : Sets the necessary parameters for the ECX Send Document.
26 -- Version : Current version 1.0
27 -- Initial version 1.0
28 -- Notes : This procedure is called from workflow(M4RPOCO).
29 -- End of comments
30
31 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
32 PROCEDURE GET_OM_CONF_PARAMS(p_itemtype IN VARCHAR2,
33 p_itemkey IN VARCHAR2,
34 p_actid IN NUMBER,
35 p_funcmode IN VARCHAR2,
36 x_resultout IN OUT NOCOPY VARCHAR2) IS
37 l_transaction_type VARCHAR2(240);
38 l_transaction_subtype VARCHAR2(240);
39 l_document_direction VARCHAR2(240);
40 l_party_id NUMBER;
41 l_party_site_id NUMBER;
42 l_party_type VARCHAR2(30);
43 l_return_code PLS_INTEGER;
44 l_errmsg VARCHAR2(2000);
45 l_result BOOLEAN;
46 l_error_code NUMBER;
47 l_error_msg VARCHAR2(1000);
48 l_customer_trx_id NUMBER;
52 l_doc_transfer_id NUMBER;
49 l_proprietary_docid VARCHAR2(2000);
50 l_inv_date DATE;
51 l_canonical_date VARCHAR2(100);
53 l_document_id VARCHAR2(100);
54 l_ntfyinvc_seq NUMBER;
55 l_organization_id NUMBER;
56 l_trx_number VARCHAR2(100);
57 l_eventkey VARCHAR2(100);
58 l_transaction_type_passed_in VARCHAR2(100);
59 BEGIN
60 IF (l_debug_level <= 1) THEN
61 cln_debug_pub.Add('ENTERING GET_OM_CONF_PARAMS', 1);
62 cln_debug_pub.Add('With the following parameters:', 1);
63 cln_debug_pub.Add('itemtype:' || p_itemtype, 1);
64 cln_debug_pub.Add('itemkey:' || p_itemkey, 1);
65 cln_debug_pub.Add('actid:' || p_actid, 1);
66 cln_debug_pub.Add('funcmode:' || p_funcmode, 1);
67 cln_debug_pub.Add('resultout:' || x_resultout, 1);
68 cln_debug_pub.Add('party_id:' || l_party_id, 1);
69 cln_debug_pub.Add('party_site_id:' || l_party_site_id, 1);
70 cln_debug_pub.Add('doc_transfer_id:' || l_doc_transfer_id, 1);
71 cln_debug_pub.Add('party_type:' || l_party_type, 1);
72 END IF;
73 l_document_direction := 'OUT';
74 l_party_type := 'C';
75 l_result := FALSE;
76 -- Do nothing in cancel or timeout mode
77 IF (p_funcmode <> wf_engine.eng_run) THEN
78 x_resultout := wf_engine.eng_null;
79 return; -- do not raise the exception as it would end the workflow
80 END IF;
81 -- Retrieve Activity Attributes
82 l_party_site_id := Wf_Engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'ECX_PARTY_SITE_ID');
83 l_doc_transfer_id := Wf_Engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'ECX_DOCUMENT_ID');
84 l_transaction_type_passed_in := Wf_Engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'PARAMETER3');
85
86
87 -- Get party id, party site id, org_id
88 BEGIN
89 SELECT party_id
90 INTO l_party_id
91 FROM hz_party_sites
92 WHERE party_site_id = l_party_site_id;
93 EXCEPTION
94 WHEN OTHERS THEN
95 IF (l_debug_level <= 1) THEN
96 cln_debug_pub.Add('Exception - Query for Party ID failed', 1);
97 END IF;
98 END;
99
100 IF (l_debug_level <= 1) THEN
101 cln_debug_pub.Add('GET_OM_CONF_PARAMS: Parameter Lookups Completed', 1);
102 cln_debug_pub.Add('With the following parameters:', 1);
103 cln_debug_pub.Add('party_id:' || l_party_id, 1);
104 cln_debug_pub.Add('party_site_id:' || l_party_site_id, 1);
105 cln_debug_pub.Add('doc_transfer_id:' || l_doc_transfer_id, 1);
106 cln_debug_pub.Add('party_type:' || l_party_type, 1);
107 END IF;
108
109 IF (l_debug_level <= 1) THEN
110 cln_debug_pub.Add('XML Trading Partner Setup Check Succeeded', 1);
111 END IF;
112
113 IF (l_transaction_type_passed_in ='POI') THEN
114 l_transaction_type := 'ONT';
115 l_transaction_subtype :='POA';
116 ELSIF (l_transaction_type_passed_in = 'CHO') THEN
117 l_transaction_type := 'M4R';
118 l_transaction_subtype :='CHANGEPO_CONF';
119 ELSIF (l_transaction_type_passed_in = 'CPO') THEN
120 l_transaction_type := 'M4R';
121 l_transaction_subtype := 'CANCELPO_CONF';
122 END IF;
123
124
125 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_TRANSACTION_TYPE', l_transaction_type);
126 IF (l_debug_level <= 1) THEN
127 cln_debug_pub.Add('ECX_TRANSACTION_TYPE'|| l_transaction_type, 1);
128 END IF;
129 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_TRANSACTION_SUBTYPE', l_transaction_subtype);
130 IF (l_debug_level <= 1) THEN
131 cln_debug_pub.Add('ECX_TRANSACTION_SUBTYPE'|| l_transaction_subtype, 1);
132 END IF;
133 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_PARTY_ID', l_party_id);
134 IF (l_debug_level <= 1) THEN
135 cln_debug_pub.Add('ECX_PARTY_ID'|| l_party_id, 1);
136 END IF;
137 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_PARTY_SITE_ID', l_party_site_id);
138 IF (l_debug_level <= 1) THEN
139 cln_debug_pub.Add('ECX_PARTY_SITE_ID'|| l_party_site_id, 1);
140 END IF;
141 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_PARTY_TYPE', l_party_type);
142 IF (l_debug_level <= 1) THEN
143 cln_debug_pub.Add('ECX_PARTY_TYPE'|| l_party_type, 1);
144 END IF;
145 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_DOCUMENT_ID', l_doc_transfer_id);
146 IF (l_debug_level <= 1) THEN
147 cln_debug_pub.Add('ECX_DOCUMENT_ID'|| l_doc_transfer_id, 1);
148 END IF;
149
150
151 l_eventkey := p_itemkey;
152 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'EVENTKEY',l_eventkey);
153 IF (l_debug_level <= 1) THEN
154 cln_debug_pub.Add('EVENTKEY'|| l_eventkey, 1);
155 END IF;
156 -- Reached Here. Successful execution.
157 x_resultout := 'SUCCESS';
158 IF (l_debug_level <= 1) THEN
159 cln_debug_pub.Add('Result out '|| x_resultout, 1);
160 END IF;
161 IF (l_debug_level <= 2) THEN
162 cln_debug_pub.Add('EXITING GET_OM_CONF_PARAMS Successfully', 2);
163 END IF;
164
165 EXCEPTION
166 WHEN OTHERS THEN
167 l_error_code := SQLCODE;
168 l_error_msg := SQLERRM;
169 IF (l_debug_level <= 1) THEN
170 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
174 cln_debug_pub.Add('Result out '|| x_resultout, 1);
171 END IF;
172 x_resultout := 'ERROR';
173 IF (l_debug_level <= 1) THEN
175 END IF;
176 IF (l_debug_level <= 1) THEN
177 cln_debug_pub.Add('Exiting GET_OM_CONF_PARAMS with Error', 1);
178 END IF;
179
180 END GET_OM_CONF_PARAMS;
181
182 -- Start of comments
183 -- API name : IS_OAG_OR_ROSETTANET
184 -- Type : Private
185 -- Pre-reqs : None.
186 -- Function : Checks whether the message is OAG or Rosettanet and return True if OAG and False if RosettaNet.
187 -- Version : Current version 1.0
188 -- Initial version 1.0
189 -- Notes : This procedure is called from workflow(OEOA).
190 -- End of comments
191
192 PROCEDURE IS_OAG_OR_ROSETTANET(p_itemtype IN VARCHAR2,
193 p_itemkey IN VARCHAR2,
194 p_actid IN NUMBER,
195 p_funcmode IN VARCHAR2,
196 x_resultout IN OUT NOCOPY VARCHAR2) IS
197 L_PARTY_SITE_ID VARCHAR2(100);
198 L_PARTY_ID VARCHAR2(100);
199 L_STANDARD_CODE VARCHAR2(100);
200 l_eventkey VARCHAR2(100);
201 l_error_code NUMBER;
202 l_error_msg VARCHAR2(1000);
203 BEGIN
204 IF (l_debug_level <= 1) THEN
205 cln_debug_pub.Add('ENTERING M4R_OM_CONF_PKG.IS_OAG_OR_ROSETTANET',1);
206 cln_debug_pub.Add('With the following parameters:', 1);
207 cln_debug_pub.Add('itemtype:' || p_itemtype, 1);
208 cln_debug_pub.Add('itemkey:' || p_itemkey, 1);
209 cln_debug_pub.Add('actid:' || p_actid, 1);
210 cln_debug_pub.Add('funcmode:' || p_funcmode, 1);
211 cln_debug_pub.Add('resultout:' || x_resultout, 1);
212 END IF;
213 -- Retrieve Activity Attributes
214 l_party_site_id := Wf_Engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'ECX_PARTY_SITE_ID');
215 l_party_id := Wf_Engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'ECX_PARTY_ID');
216
217 select standard_code
218 into l_standard_code
219 from ecx_tp_details_v
220 where tp_header_id = (select tp_header_id from ecx_tp_headers
221 where party_id = l_party_id and
222 party_site_id = l_party_site_id) and
223 transaction_type ='ONT' and transaction_subtype = 'POA';
224
225 if (l_standard_code = 'OAG') then
226 x_resultout := 'COMPLETE:T'; -- Reached Here. Successful execution.
227 else
228 x_resultout := 'COMPLETE:F';
229 l_eventkey :=p_itemkey;
230 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'EVENTKEY',l_eventkey);
231 IF (l_debug_level <= 1) THEN
232 cln_debug_pub.Add('EVENTKEY: '|| l_eventkey, 1);
233 END IF;
234 end if;
235
236 IF (l_debug_level <= 2) THEN
237 cln_debug_pub.Add('EXITING IS_OAG_OR_ROSETTANET Successfully', 2);
238 END IF;
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 l_error_code := SQLCODE;
243 l_error_msg := SQLERRM;
244 IF (l_debug_level <= 1) THEN
245 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
246 END IF;
247 x_resultout := 'ERROR';
248 IF (l_debug_level <= 1) THEN
249 cln_debug_pub.Add('Result out '|| x_resultout, 1);
250 END IF;
251 IF (l_debug_level <= 1) THEN
252 cln_debug_pub.Add('Exiting IS_OAG_OR_ROSETTANET with Error', 1);
253 END IF;
254 END IS_OAG_OR_ROSETTANET;
255
256 FUNCTION UPDATE_CH_OM_EVENT_SUB(
257 p_subscription_guid IN RAW,
258 p_event IN OUT NOCOPY WF_EVENT_T
259 ) RETURN VARCHAR2
260 IS
261 l_evt_parameters wf_parameter_list_t;
262 l_xmlg_txn_type VARCHAR2(100);
263 l_xmlg_txn_subtype VARCHAR2(100);
264 l_tr_partner_id VARCHAR2(100);
265 l_tr_partner_site VARCHAR2(100);
266 l_standard VARCHAR2(100);
267 l_return_tmp VARCHAR2(100);
268 l_processing_stage VARCHAR2(100);
269 BEGIN
270 IF (l_Debug_Level <= 2) THEN
271 cln_debug_pub.Add('==========ENTERING UPDATE_CH_OM_EVENT_SUB===========', 2);
272 END IF;
273
274 l_evt_parameters := p_event.getParameterList();
275
276 IF (l_Debug_Level <= 1) THEN
277 cln_debug_pub.Add('----------- PARAMETERS OBTAINED ----------', 1);
278 END IF;
279 l_xmlg_txn_type:= WF_EVENT.getValueForParameter('XMLG_INTERNAL_TXN_TYPE',l_evt_parameters);
280 IF (l_Debug_Level <= 1) THEN
281 cln_debug_pub.Add('XMLG_INTERNAL_TXN_TYPE: '||l_xmlg_txn_type, 1);
282 END IF;
283 l_xmlg_txn_subtype:= WF_EVENT.getValueForParameter('XMLG_INTERNAL_TXN_SUBTYPE',l_evt_parameters);
284 IF (l_Debug_Level <= 1) THEN
285 cln_debug_pub.Add('XMLG_INTERNAL_TXN_SUBTYPE: '||l_xmlg_txn_subtype, 1);
286 END IF;
287 l_processing_stage:= WF_EVENT.getValueForParameter('PROCESSING_STAGE',l_evt_parameters);
288 IF (l_Debug_Level <= 1) THEN
289 cln_debug_pub.Add('PROCESSING_STAGE: '||l_processing_stage, 1);
290 END IF;
291
292 IF (l_xmlg_txn_type = 'ONT' and l_xmlg_txn_subtype = 'POA' and l_processing_stage = 'OUTBOUND_SENT') THEN
293 IF (l_Debug_Level <= 1) THEN
294 cln_debug_pub.Add('Getting the Message Standard', 1);
295 END IF;
296
297 l_tr_partner_id := WF_EVENT.getValueForParameter('TRADING_PARTNER_ID',l_evt_parameters);
298 IF (l_Debug_Level <= 1) THEN
299 cln_debug_pub.Add('Trading Partner ID ----'||l_tr_partner_id, 1);
300 END IF;
301
302 l_tr_partner_site := WF_EVENT.getValueForParameter('TRADING_PARTNER_SITE',l_evt_parameters);
303 IF (l_Debug_Level <= 1) THEN
304 cln_debug_pub.Add('Trading Partner Site ----'||l_tr_partner_site, 1);
305 END IF;
306
307 BEGIN
308 SELECT standard_code
309 into l_standard
310 FROM ecx_tp_details_v
311 WHERE tp_header_id = (SELECT tp_header_id FROM ecx_tp_headers
312 WHERE party_id = l_tr_partner_id
313 AND party_site_id = l_tr_partner_site
314 AND party_type = 'C')
315 AND transaction_type ='ONT'
316 AND transaction_subtype = 'POA';
317 EXCEPTION
318 WHEN OTHERS THEN
319 -- Nothing to do
320 IF (l_Debug_Level <= 4) THEN
321 cln_debug_pub.Add('In valid paramerers passed to the event', 4);
322 END IF;
323 END;
324
325 IF l_standard = 'ROSETTANET' THEN
326 -- For rosettan net standard this event shoudl be ignored
327 IF (l_Debug_Level <= 2) THEN
328 cln_debug_pub.Add('==========EXITING UPDATE_CH_OM_EVENT_SUB WITHOUT UPDATING COLLABORATION HISTORY ===========', 2);
329 END IF;
330 RETURN 'SUCCESS';
331 END IF;
332 END IF;
333
334 -- If the control reaches here, it mesans that collaboration history needs to be updated
335 l_return_tmp := CLN_CH_EVENT_SUBSCRIPTION_PKG.ADD_COLLABORATION_EVENT_SUB(p_subscription_guid,p_event);
336
337 IF (l_Debug_Level <= 2) THEN
338 cln_debug_pub.Add('==========EXITING UPDATE_CH_OM_EVENT_SUB===========', 2);
339 END IF;
340 RETURN 'SUCCESS';
341 END;
342
343 END M4R_OM_CONF_PKG;