1 PACKAGE BODY XNB_UTIL_PVT AS
2 /* $Header: XNBVUTYB.pls 120.6 2006/06/25 13:39:46 pselvam noship $ */
3
4
5 PROCEDURE update_cln
6 (
7 p_doc_status VARCHAR2,
8 p_app_ref_id VARCHAR2,
9 p_orig_ref VARCHAR2,
10 p_intl_ctrl_no NUMBER,
11 p_msg_data VARCHAR2
12 )
13 AS
14
15 l_key VARCHAR2(250);
16 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
17 l_doc_status VARCHAR2(20);
18 l_msg_txt VARCHAR2(250);
19
20 BEGIN
21
22 ---------------------------------------------------------------------------------------
23 -- Assigns the Lookup values for the Document Status returned in the CBOD map
24 --
25 -----------------------------------------------------------------------------------------
26
27 IF p_doc_status = '00' THEN
28 l_doc_status := 'SUCCESS';
29 l_msg_txt := 'XNB_CLN_MSG_ACCEPTED';
30 ELSE
31 l_doc_status := 'ERROR';
32 l_msg_txt := 'XNB_CLN_MSG_REJECTED';
33 END IF;
34
35 -----------------------------------------------------------------------------------------
36 --Assign the values for the collaboration parameters and raise the event
37 --
38 -----------------------------------------------------------------------------------------
39
40 wf_event.addparametertolist (
41 p_name =>'DOCUMENT_STATUS',
42 p_value => l_doc_status,
43 p_parameterlist => l_parameter_list
44 );
45
46 wf_event.addparametertolist (
47 p_name =>'MESSAGE_TEXT',
48 p_value =>l_msg_txt,
49 p_parameterlist => l_parameter_list
50 );
51
52 wf_event.addparametertolist (
53 p_name =>'REFERENCE_ID',
54 p_value => p_app_ref_id,
55 p_parameterlist => l_parameter_list
56 );
57
58 wf_event.addparametertolist (
59 p_name =>'ORIGINATOR_REFERENCE',
60 p_value => p_orig_ref,
61 p_parameterlist => l_parameter_list
62 );
63
64 wf_event.addparametertolist (
65 p_name =>'XMLG_INTERNAL_CONTROL_NUMBER',
66 p_value =>p_intl_ctrl_no,
67 p_parameterlist => l_parameter_list
68 );
69
70 wf_event.addparametertolist (
71 p_name =>'MSG_DATA',
72 p_value =>p_msg_data,
73 p_parameterlist => l_parameter_list
74 );
75
76
77 -----------------------------------------------------------------------------------------
78 -- Generate the key value and raise the event to update the collaboration
79 --
80 -----------------------------------------------------------------------------------------
81
82 l_key := 'XNB'||'COLL_UPDATE'|| p_orig_ref||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
83 wf_event.raise (
84 p_event_name => 'oracle.apps.cln.ch.collaboration.update',
85 p_event_key => l_key,
86 p_parameters => l_parameter_list
87 );
88 --End of the Function
89 END update_cln;
90
91 /***** Private API which checks whether collaboration exists for the given document */
92 /* number and a given trading partner */
93
94 Function check_collaboration_doc_status (
95 p_doc_no NUMBER,
96 p_collab_type VARCHAR2
97 )
98 RETURN NUMBER
99 AS
100
101 -----------------------------------------------------------------------------------------
102 -- Cursor to retrieve the trading partner codes
103 --
104 -----------------------------------------------------------------------------------------
105 cursor l_tp_codes is
106 SELECT SOURCE_TP_LOCATION_CODE
107 FROM ECX_TP_DETAILS_V
108 WHERE TRANSACTION_TYPE = 'XNB' AND TRANSACTION_SUBTYPE = 'CBODI';
109
110 l_tp_code ecx_tp_details.source_tp_location_code%TYPE;
111 l_num NUMBER;
112
113 begin
114
115 open l_tp_codes;
116 fetch l_tp_codes into l_tp_code ;
117
118 while (l_tp_codes%FOUND) LOOP
119
120 select COUNT(clndtl.collaboration_dtl_id)
121 into l_num
122 from cln_coll_hist_hdr clnhdr,
123 cln_coll_hist_dtl clndtl
127 and clnhdr.document_no = p_doc_no
124 where
125 clnhdr.application_id = 881
126 and clnhdr.collaboration_type = p_collab_type
128 and clnhdr.collaboration_id = clndtl.collaboration_id
129 and clndtl.collaboration_document_type = 'CONFIRM_BOD'
130 and clndtl.originator_reference = l_tp_code
131 and clndtl.document_status = 'SUCCESS';
132
133 If (l_num = 0) then
134 RETURN 2;
135 end if;
136
137 fetch l_tp_codes into l_tp_code;
138 END LOOP;
139
140 return 1;
141
142 EXCEPTION
143
144 WHEN NO_DATA_FOUND THEN
145 RETURN 2;
146
147 WHEN OTHERS THEN
148 RETURN -1;
149
150 END check_collaboration_doc_status;
151
152
153
154 /* Function: Check Document Collaboration Status */
155 FUNCTION check_doc_collaboration_status (
156 p_doc_no NUMBER,
157 p_collab_type VARCHAR2
158 )
159 RETURN NUMBER
160 /*----------------------------------------------------------------------------------------------**
161 Note: This procedure will be used by the Item Batch Export Conc Pgm and the IB Ownership Change **
162 publish. In turn calls check_cln_billapp_doc_status to complete the logic of cln check. **
163 /*----------------------------------------------------------------------------------------------**
164 This procedure checks the confirmation status of a given document (Sales Order, Account etc) **
165 in the respective collaborations, as confirmed by ALL billing applications (XMLG Source TPs)**
166 A document is identified by the XMLG Internal and External Txn types and the Doc Number **
167 A Document publish status is assumed successful if EVERY Hub user set in XMLG setup has sent**
168 ATLEAST ONE ConfirmBOD with status SUCCESS for the doc **
169 Arguments: p_doc_no - Document Number **
170 p_int_txn_type - XMLG Internal Transaction Type for the document publish **
171 p_int_txn_sub_type - XMLG Internal Transaction Subtype for the document publish **
172 Returns : Number, with value **
173 1 - if EVERY XMLG TP has sent ATLEAST ONE Confirm BOD with status SUCCESS for the doc **
174 2 - if atleast one of the TPs is yet to confirm success of a publish of the doc **
175 OR if there is no collaboration for the given document number **
176 -1 - If there was an error during the check. **
177 -----------------------------------------------------------------------------------------------*/
178 AS
179
180 CURSOR l_tp_codes IS
181 SELECT SOURCE_TP_LOCATION_CODE
182 FROM ECX_TP_DETAILS_V
183 WHERE TRANSACTION_TYPE = 'XNB' AND TRANSACTION_SUBTYPE = 'CBODI';
184
185 l_tp_code ecx_tp_details.source_tp_location_code%TYPE;
186 l_success_stat NUMBER; --Success status from each trading partner
187 BEGIN
188
189 ---------------------------------------------------------------
190 -- Retrieve all hub users from the XMLG Hub User setup for XNB,
191 -- excluding the hub user representing XNB.
192 ---------------------------------------------------------------
193 OPEN l_tp_codes;
194 FETCH l_tp_codes INTO l_tp_code ;
195
196 WHILE (l_tp_codes%FOUND) LOOP
197 l_success_stat := check_cln_billapp_doc_status(
198 p_doc_no => p_doc_no,
199 p_collab_type => p_collab_type,
200 p_tp_loc_code => l_tp_code
201 );
202
203 IF (l_success_stat = 0) THEN -- This hub user is yet to confirm success
204 RETURN 2; -- Exit with return status 2.
205 ELSIF ( l_success_stat = -1 ) THEN
206 RETURN -1; -- There was an error during the check
207 END IF;
208
209 FETCH l_tp_codes INTO l_tp_code;
210 END LOOP;
211
212 RETURN 1; --All billing applications have confirmed success for the doc.
213 EXCEPTION
214 WHEN NO_DATA_FOUND THEN
215 RETURN 2; -- This status should ideally be an error.
216 -- There are no hub users setup on the XML Gateway.
217 -- An application error message should be thrown here.
218 WHEN OTHERS THEN
219 RETURN -1;
220 --End of procedure.
221 END check_doc_collaboration_status;
222
223
224 /* Function: Check Collaboration for Billing Application Document Status*/
225 FUNCTION check_cln_billapp_doc_status (
226 p_doc_no NUMBER,
227 p_collab_type VARCHAR2,
228 p_tp_loc_code VARCHAR2
229 )
230 RETURN NUMBER
231 /*----------------------------------------------------------------------------------------------**
232 This procedure checks the confirmation status of a given document (Sales Order, Account etc) **
233 in the respective collaborations, as confirmed by a given billing application. **
234 A Billing Application is identified by the Source Trading Partner Location code set for the **
235 for the inbound transaction in XML Gateway. **
239 Arguments: p_doc_no - Document Number **
236 A document is identified by the XMLG Internal and External Txn types and the Doc Number **
237 A Document publish status is assumed successful if EVERY Hub user set in XMLG setup has sent**
238 ATLEAST ONE ConfirmBOD with status SUCCESS for the doc **
240 p_int_txn_type - XMLG Internal Transaction Type for the document publish **
241 p_int_txn_sub_type - XMLG Internal Transaction Subtype for the document publish **
242 p_tp_loc_code - TP Location Code representing the given billing application **
243 Returns : Number, with value **
244 0 - If the given billing application is yet to confirm the document with SUCCESS **
245 1 - If the given billing application has already confirmed the document with SUCCESS **
246 -1 - If there was an error during the check **
247 -----------------------------------------------------------------------------------------------*/
248 AS
249 l_num NUMBER;
250 l_ret_stat NUMBER ; --return status.
251 BEGIN
252
253 l_ret_stat := -1;
254
255 --Check the number of Successful ConfirmBODs sent by the given billing app.
256 SELECT COUNT(clndtl.collaboration_dtl_id)
257 INTO l_num
258 FROM cln_coll_hist_hdr clnhdr,
259 cln_coll_hist_dtl clndtl
260 WHERE
261 clnhdr.application_id = 881
262 AND clnhdr.collaboration_type = p_collab_type
263 AND clnhdr.document_no = p_doc_no
264 AND clnhdr.collaboration_id = clndtl.collaboration_id
265 AND clndtl.collaboration_document_type = 'CONFIRM_BOD'
266 AND clndtl.originator_reference = p_tp_loc_code
267 AND clndtl.document_status = 'SUCCESS';
268
269 IF (l_num = 0) THEN
270 l_ret_stat := 0; --Zero Success ConfirmBODs
271 ELSE
272 l_ret_stat := 1; --There was atleast one Success ConfirmBOD received
273 END IF;
274
275 RETURN l_ret_stat;
276 EXCEPTION
277 WHEN OTHERS THEN --There was an error. Return -1.
278 RETURN l_ret_stat;
279 --End of procedure
280 END check_cln_billapp_doc_status;
281
282
283 ------------------------------------------------------------------------------------
284 /* Program to return the Flag for the qualifier */
285 /* Account Update Functionality */
286
287
288 PROCEDURE return_qualifier
289 (
290 p_event_name IN VARCHAR2,
291 p_event_param IN VARCHAR2,
292 p_transaction_id IN VARCHAR2,
293 x_qualifier OUT NOCOPY VARCHAR2
294 )
295 AS
296 BEGIN
297 /*C547_XNB - Obsolete Billing Preference information*/
298 /*
299 IF p_event_name = 'oracle.apps.ar.hz.BillingPreference.create' AND p_event_param = p_transaction_id THEN
300 x_qualifier := 'I';
301 return;
302
303 ELSIF p_event_name = 'oracle.apps.ar.hz.BillingPreference.update' AND p_event_param = p_transaction_id THEN
304 x_qualifier := 'U';
305 xnb_debug.log('return_qualifier','Flag'||x_qualifier||'Value');
306 return;
307 */
308 IF p_event_name = 'oracle.apps.ar.hz.CustAcctRelate.create' AND p_event_param = p_transaction_id THEN
309 x_qualifier := 'I';
310 return;
311
312 ELSIF p_event_name = 'oracle.apps.ar.hz.CustAcctRelate.update' AND p_event_param = p_transaction_id THEN
313 x_qualifier := 'U';
314 return;
315
316 ELSIF p_event_name = 'oracle.apps.ar.hz.ContactPoint.update' AND p_event_param = p_transaction_id THEN
317 x_qualifier := 'U';
318 return;
319
320 ELSIF p_event_name = 'oracle.apps.ar.hz.CustomerProfile.update' AND p_event_param = p_transaction_id THEN
321 x_qualifier := 'U';
322 return;
323
324 ELSIF p_event_name = 'oracle.apps.ar.hz.CustProfileAmt.create' AND p_event_param = p_transaction_id THEN
325 x_qualifier := 'I';
326 return;
327
328 ELSIF p_event_name = 'oracle.apps.ar.hz.CustProfileAmt.update' AND p_event_param = p_transaction_id THEN
329 x_qualifier := 'U';
330 return;
331
332 END IF;
333
334 return;
335
336 END return_qualifier;
337
338 ------------------------------------------------------------------------------------
339 /* Program to return the ship_to_contact details of an OrderLine */
340 /* R12 : Enhanced Salesorder Functionality */
341
342 /* ST Bug Fix: 5165987 : Ship To Contact Point Issue
343 PROCEDURE return_ship_to_contact
344 (
345 p_ship_to_contact_id IN NUMBER,
346 x_person_identifier OUT NOCOPY VARCHAR2,
347 x_person_title OUT NOCOPY VARCHAR2,
348 x_person_pre_name_adjunct OUT NOCOPY VARCHAR2,
349 x_person_first_name OUT NOCOPY VARCHAR2,
350 x_person_middle_name OUT NOCOPY VARCHAR2,
351 x_person_last_name OUT NOCOPY VARCHAR2,
352 x_person_name_suffix OUT NOCOPY VARCHAR2,
353 x_salutation OUT NOCOPY VARCHAR2,
354 x_email_address OUT NOCOPY VARCHAR2,
355 x_phone_line_type OUT NOCOPY VARCHAR2,
356 x_phone_country_code OUT NOCOPY VARCHAR2,
357 x_phone_area_code OUT NOCOPY VARCHAR2,
358 x_phone_number OUT NOCOPY VARCHAR2
359 )
360 AS
361
362 BEGIN
363 SELECT
364 C.person_identifier,
365 C.person_title,
366 C.person_pre_name_adjunct,
367 C.person_first_name,
368 C.person_middle_name,
369 C.person_last_name,
370 C.person_name_suffix,
374 D.phone_country_code,
371 C.salutation,
372 D.email_address,
373 D.phone_line_type,
375 D.phone_area_code,
376 D.phone_number
377 INTO
378 x_person_identifier,
379 x_person_title,
380 x_person_pre_name_adjunct,
381 x_person_first_name,
382 x_person_middle_name,
383 x_person_last_name,
384 x_person_name_suffix,
385 x_salutation,
386 x_email_address,
387 x_phone_line_type,
388 x_phone_country_code,
389 x_phone_area_code,
390 x_phone_number
391 FROM
392 hz_cust_account_roles A,
393 hz_relationships B,
394 hz_parties C,
395 hz_contact_points D
396 WHERE
397 A.cust_account_role_id = p_ship_to_contact_id
398 and A.party_id = B.party_id
399 and B.directional_flag = 'F'
400 and B.subject_id = C.party_id
401 and B.party_id = D.owner_table_id(+)
402 and D.owner_table_name(+) = 'HZ_PARTIES';
403
404 RETURN;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 RETURN;
409
410
411 END return_ship_to_contact;
412 */
413
414 ------------------------------------------------------------------------------------
415 /* Program to return Address details from site_use_id */
416 /* R12 : Enhanced Salesorder Functionality */
417
418
419 PROCEDURE return_address_from_usageid
420 (
421 p_site_use_id IN NUMBER,
422 x_address_line OUT NOCOPY VARCHAR2,
423 x_country OUT NOCOPY VARCHAR2,
424 x_state OUT NOCOPY VARCHAR2,
425 x_county OUT NOCOPY VARCHAR2,
426 x_city OUT NOCOPY VARCHAR2,
427 x_postal_code OUT NOCOPY VARCHAR2
428
429 )
430 AS
431
432 BEGIN
433 SELECT locations.address1||DECODE(locations.address2
434 , NULL
435 , NULL
436 , ';'||locations.address2|| DECODE(locations.address3
437 , NULL
438 , NULL
439 , ';'||locations.address3|| DECODE(locations.address4
440 , NULL
441 , NULL
442 , ';'||locations.address4))) address_line,
443 locations.country,
444 locations.state,
445 locations.county,
446 locations.city,
447 locations.postal_code
448 INTO
449 x_address_line,
450 x_country,
451 x_state,
452 x_county,
453 x_city,
454 x_postal_code
455
456 FROM
457 hz_cust_site_uses_all site_uses,
458 hz_cust_acct_sites_all acct_sites,
459 hz_party_sites party_sites,
460 hz_locations locations
461 WHERE
462 site_uses.site_use_id = p_site_use_id
463 and acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id
464 and acct_sites.party_site_id = party_sites.party_site_id
465 and party_sites.location_id = locations.location_id;
466
467
468 RETURN;
469
470 EXCEPTION
471 WHEN OTHERS THEN
472 RETURN;
473
474 END return_address_from_usageid;
475
476
477 --End of the Package
478 END xnb_util_pvt;
[Home] [Help]