[Home] [Help]
PACKAGE BODY: APPS.ONT_ITM_PKG
Source
1 PACKAGE BODY ONT_ITM_PKG AS
2 /* $Header: OEXVITMB.pls 120.29.12020000.2 2013/03/28 11:59:19 sahvivek ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ONT_ITM_PKG';
5 G_Dp_Service_Flag VARCHAR2(1) := 'N'; --bug 5009103
6
7
8 /*-----------------------------------------------------+
9 | Name : Process_ITM_Request |
10 | Parameters : IN p_line_rec |
11 | OUT NOCOPY x_return_status |
12 | x_result_out |
13 | Description : This Procedure is called from Work |
14 | Flow for Performing Screening |
15 | |
16 +-----------------------------------------------------*/
17
18 PROCEDURE Process_ITM_Request
19 (p_line_rec IN OE_ORDER_PUB.line_rec_type
20 ,x_return_status OUT NOCOPY VARCHAR2
21 ,x_result_out OUT NOCOPY VARCHAR2
22 )
23
24 IS
25 l_api_name CONSTANT VARCHAR2(30) := 'Process_ITM_Request';
26 l_master_organization_id NUMBER;
27 l_return_status VARCHAR2(1000);
28 l_result_out VARCHAR2(2400);
29
30 --
31 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
32 --
33 BEGIN
34 SAVEPOINT CREATE_REQUEST_PUB;
35
36 IF l_debug_level > 0 THEN
37 OE_DEBUG_PUB.ADD('Starting process_itm_request...'||'for line id:'||p_line_rec.line_id);
38 END IF;
39
40 SELECT master_organization_id
41 INTO l_master_organization_id
42 FROM mtl_parameters
43 WHERE organization_id = p_line_rec.ship_from_org_id;
44
45 Create_Request(
46 p_master_organization_id => l_master_organization_id ,
47 p_line_rec => p_line_rec ,
48 x_return_status => l_return_status);
49
50 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
51
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53 x_result_out := OE_GLOBALS.G_WFR_COMPLETE;
54
55 -- The Flow Status is updated to Export Compliance Screening
56
57 IF l_debug_level > 0 THEN
58 OE_DEBUG_PUB.Add('Before calling update flow status to...'|| 'AWAITING EXPORT SCREENING' ) ;
59 END IF;
60
61 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
62 (p_line_id => p_line_rec.line_id
63 ,p_flow_status_code => 'AWAITING_EXPORT_SCREENING'
64 ,x_return_status => l_return_status
65 );
66
67 IF l_debug_level > 0 THEN
68 OE_DEBUG_PUB.Add('Return status from flow status api '|| l_return_status,1);
69 END IF;
70
71 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
72 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
73 END IF;
74
75 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
76 x_return_status := FND_API.G_RET_STS_ERROR;
77
78 IF l_debug_level > 0 THEN
79 oe_debug_pub.add( 'NOT ABLE TO CREATE REQUEST...' , 1 ) ;
80 END IF;
81
82 -- The Flow Status is updated to Export Compliance Screening
83
84 OE_Order_WF_Util.Update_Flow_Status_Code
85 (p_line_id => p_line_rec.line_id
86 ,p_flow_status_code => 'EXPORT_SCREENING_DATA_ERROR'
87 ,x_return_status => l_return_status
88 );
89
90 IF l_debug_level > 0 THEN
91 OE_DEBUG_PUB.Add('Return status from flow status api '|| l_return_status,1);
92 END IF;
93
94 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
95 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
96 END IF;
97
98 ELSE
99 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101
102 END IF; -- End of checking l_return_status
103
104 IF l_debug_level > 0 THEN
105 OE_DEBUG_PUB.Add('Ending process_itm_request...'|| 'for line id:'||p_line_rec.line_id);
106 END IF;
107
108 EXCEPTION
109 WHEN FND_API.G_EXC_ERROR THEN
110 ROLLBACK TO CREATE_REQUEST_PUB;
111 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
112 ROLLBACK TO CREATE_REQUEST_PUB;
113 WHEN OTHERS THEN
114 ROLLBACK TO CREATE_REQUEST_PUB;
115 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
116 FND_MSG_PUB.Add_Exc_Msg
117 (G_PKG_NAME,
118 l_api_name
119 );
120 END IF;
121 END Process_ITM_Request;
122
123 -- Function added for bug5140692
124 -- Function changed to procedure for Bug 9583024, and three new out parameters added
125 PROCEDURE Get_Contact_name
126 (
127 p_contact_id IN NUMBER,
128 x_contact_name OUT NOCOPY VARCHAR2,
129 x_contact_hz_id OUT NOCOPY NUMBER,
130 x_contact_number OUT NOCOPY VARCHAR2)
131 IS
132
133 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
134
135 BEGIN
136 IF l_debug_level > 0 THEN
137 oe_debug_pub.add('OEXVITMB.pls: Inside Get_Contact_name, contact_id='||p_contact_id,3);
138 END IF;
139
140 SELECT PARTY.PARTY_NAME, party.party_id, party.party_number
141 INTO x_contact_name, x_contact_hz_id, x_contact_number --Bug 9583024
142 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
143 HZ_PARTIES PARTY,
144 HZ_CUST_ACCOUNTS ACCT,
145 HZ_RELATIONSHIPS REL,
146 HZ_PARTIES REL_PARTY
147 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
148 AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
149 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
150 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
151 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
152 AND REL.SUBJECT_ID = PARTY.PARTY_ID
153 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
154 AND REL.OBJECT_ID = ACCT.PARTY_ID
155 AND ACCT.CUST_ACCOUNT_ID = ACCT_ROLE.CUST_ACCOUNT_ID;
156
157
158 IF l_debug_level > 0 THEN
159 oe_debug_pub.add('OEXVITMB.pls: Contact name ='||x_contact_name,5);
160 oe_debug_pub.add('OEXVITMB.pls: Contact Hz id ='||x_contact_hz_id,5);
161 oe_debug_pub.add('OEXVITMB.pls: Contact Hz number ='||x_contact_number,5);
162 oe_debug_pub.add('Exiting Get_Contact_name',3);
163 END IF;
164
165
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168 IF l_debug_level > 0 THEN
169 oe_debug_pub.add('OEXVITMB: NO_DATA_FOUND exception..',3 );
170 END IF;
171 Raise;
172 WHEN OTHERS THEN
173 IF l_debug_level > 0 THEN
174 oe_debug_pub.add('OEXVITMB: OTHERS exception in Get_Contact_name...',3 );
175 END IF;
176 Raise;
177 END Get_Contact_name;
178
179 /*-----------------------------------------------------+
180 | Name : Get_Address |
181 | Parameters : IN p_source_id |
182 | p_source_type |
183 | p_contact_id |
184 | OUT NOCOPY x_party_name |
185 | x_alternate_name bug 4231894 |
186 | x_address |
187 | x_city |
188 | x_state |
189 | x_country |
190 | x_postal_code |
191 | x_phone |
192 | x_email |
193 | x_fax |
194 | x_url |
195 | x_contact_person |
196 | x_hz_party_id |
197 | x_hz_party_number |
198 | x_party_site_number |
199 | x_contact_hz_party_id |
200 | x_contact_hz_party_number |
201 | x_return_status |
202 | Description : This Procedure gets the address |
203 | details of sources |
204 | |
205 +-----------------------------------------------------*/
206
207 PROCEDURE Get_Address (
208 p_source_id IN NUMBER
209 ,p_source_type IN VARCHAR2
210 ,p_contact_id IN NUMBER
211 ,p_org_id_passed IN VARCHAR2
212 ,x_party_name OUT NOCOPY VARCHAR2
213 ,x_alternate_name OUT NOCOPY VARCHAR2 -- bug4231894
214 ,x_address1 OUT NOCOPY VARCHAR2 -- bug7485980
215 ,x_address2 OUT NOCOPY VARCHAR2 -- bug7485980
216 ,x_address3 OUT NOCOPY VARCHAR2 -- bug7485980
217 ,x_address4 OUT NOCOPY VARCHAR2 -- bug7485980
218 ,x_city OUT NOCOPY VARCHAR2
219 ,x_state OUT NOCOPY VARCHAR2
220 ,x_country OUT NOCOPY VARCHAR2
221 ,x_postal_code OUT NOCOPY VARCHAR2
222 ,x_phone OUT NOCOPY VARCHAR2
223 ,x_email OUT NOCOPY VARCHAR2
224 ,x_fax OUT NOCOPY VARCHAR2
225 ,x_url OUT NOCOPY VARCHAR2
226 ,x_contact_person OUT NOCOPY VARCHAR2
227 ,x_hz_party_id OUT NOCOPY NUMBER -- Bug 9583024
228 ,x_hz_party_number OUT NOCOPY VARCHAR2 -- Bug 9583024
229 ,x_party_site_number OUT NOCOPY VARCHAR2 -- Bug 9583024
230 ,x_contact_hz_party_id OUT NOCOPY NUMBER -- Bug 9583024
231 ,x_contact_hz_party_number OUT NOCOPY VARCHAR2 -- Bug 9583024
232 ,x_return_status OUT NOCOPY VARCHAR2
233 )
234 IS
235 l_api_name CONSTANT VARCHAR2(30) := 'Get_Address';
236 c_fax VARCHAR2(1000) default null;
237 c_email VARCHAR2(1000) default null;
238 c_phone VARCHAR2(1000) default null;
239 c_contact_name VARCHAR2(1000) default null;
240 l_source_type VARCHAR2(35);
241
242 -- This Cursor is used for getting the Address Details for the
243 -- Ship_to_org_id details. The same cursor is used for ship_to,
244 -- deliver_to,Invoice_to.
245 -- NULL values are passed into the State column.
246 -- Modified the address cursors to remove concatenation of , in addresses for bug 7485890
247 -- Modified the cursor for Bug 9583024
248 CURSOR C_GET_ADDRESS (cp_source_id NUMBER, cp_source_type VARCHAR2)
249 IS
250 SELECT
251 site_uses.site_use_id source_id,
252 decode(cp_source_type, 'END_CUSTOMER', cp_source_type, site_uses.site_use_code) source_type, --Bug 9583024
253 party.party_name party_name,
254 loc.address1 address1,
255 loc.address2 address2,
256 loc.address3 address3,
257 loc.address4 address4,
258 -- ltrim(rtrim(loc.address1||','||loc.address2||
259 -- ','||loc.address3||','||loc.address4)) address,
260 loc.city city,
261 loc.state state,
262 loc.country country,
263 loc.postal_code postal_code,
264 decode(party.party_type,'PERSON',hp.person_name_phonetic, party.organization_name_phonetic ) alternate_name,
265 party.party_id hz_party_id, -- Bug 9583024
266 party.party_number hz_party_number , -- Bug 9583024
267 party_site.party_site_number party_site_number -- Bug 9583024
268 FROM
269 hz_cust_site_uses_all site_uses,
270 hz_cust_acct_sites_all acct_site,
271 hz_party_sites party_site,
272 hz_locations loc,
273 hz_cust_accounts cust_acct,
274 hz_parties party,
275 hz_Person_profiles hp
276 WHERE
277 site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
278 AND acct_site.cust_account_id = cust_acct.cust_account_id
279 AND party.party_id = cust_acct.party_id
280 AND acct_site.party_site_id = party_site.party_site_id
281 AND loc.location_id = party_site.location_id
282 AND site_uses.site_use_code = decode(cp_source_type, 'END_CUSTOMER', site_uses.site_use_code, cp_source_type) --Bug 9583024
283 AND site_uses.site_use_id = cp_source_id
284 AND hp.party_id(+) = party.party_id -- bug 4231894
285 AND rownum = 1;
286
287 CURSOR C_SHIP_FROM_ADDRESS_SF (cp_ship_from_org_id NUMBER)
288 IS
289 SELECT
290 hu.organization_id source_id,
291 'SHIP_FROM' source_type,
292 hu.name party_name,
293 hl.address_line_1 address1,
294 hl.address_line_2 address2,
295 hl.address_line_3 address3,
296 -- ltrim(rtrim(hl.address_line_1||','||
297 -- hl.address_line_2||','||hl.address_line_3)) address,
298 hl.town_or_city city,
299 hl.region_2 state,
300 hl.country country,
301 hl.postal_code postal_code,
302 hl.telephone_number_1 phone,
303 NULL email,
304 NULL fax,
305 NULL url
306 FROM
307 hr_all_organization_units hu,
308 hr_locations hl
309 WHERE
310 hl.location_id = hu.location_id
311 AND hu.organization_id = cp_ship_from_org_id;
312
313 -- This cursor is specifically for the Sold_to Details
314 -- Cursor modified for Bug 9583024
315 CURSOR C_SOLD_TO_ADDRESS(cp_sold_to_org_id NUMBER)
316 IS
317 SELECT
318 cust_acct.cust_account_id source_id,
319 'SOLD_TO' source_type,
320 party.party_name party_name,
321 loc.address1 address1,
322 loc.address2 address2,
323 loc.address3 address3,
324 loc.address4 address4,
325 -- ltrim(rtrim(loc.address1||','||loc.address2
326 -- ||','||loc.address3||','||loc.address4)) address,
327 loc.city city,
328 loc.state state,
329 loc.country country,
330 loc.postal_code postal_code,
331 decode(party.party_type,'ORGANIZATION',party.organization_name_phonetic
332 ,hp.person_name_phonetic) alternate_name, -- bug4231894
333 party.party_id hz_party_id , -- Bug 9583024
334 party.party_number hz_party_number , -- Bug 9583024
335 party_site.party_site_number party_site_number -- Bug 9583024
336 FROM
337 hz_parties party,
338 hz_cust_accounts cust_acct,
339 hz_locations loc,
340 hz_party_sites party_site,
341 hz_Person_profiles hp
342 WHERE
343 party.party_id = cust_acct.party_id
344 AND cust_acct.cust_account_id = cp_sold_to_org_id
345 AND party_site.party_id = party.party_id
346 AND loc.location_id = party_site.location_id
347 AND hp.party_id(+) = party.party_id -- bug 4231894
348 AND rownum = 1;
349
350 --Added for Bug 9583024
351 l_contact_name HZ_PARTIES.PARTY_NAME%TYPE;
352 l_contact_hz_id HZ_PARTIES.PARTY_ID%TYPE;
353 l_contact_number HZ_PARTIES.PARTY_NUMBER%TYPE;
354 --
355 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
356 --
357 BEGIN
358
359 IF l_debug_level > 0 THEN
360 oe_debug_pub.add('Inside get address , source_type_code is '||p_source_type||' ,p_source_id='||p_source_id||' ,p_contact_id='||p_contact_id,4); -- bug 4231894
361 END IF;
362
363 IF (p_source_type IN ('INTERMED_SHIP_TO','SHIP_TO',
364 'BILL_TO','DELIVER_TO', 'END_CUSTOMER')) THEN --Bug 9583024
365 IF p_source_type = 'INTERMED_SHIP_TO' THEN
366 l_source_type := 'SHIP_TO';
367 ELSE
368 l_source_type := p_source_type;
369 END IF;
370
371 FOR c_address IN C_GET_ADDRESS(p_source_id,l_source_type)
372 LOOP
373 x_party_name := c_address.party_name;
374 x_alternate_name := c_address.alternate_name; -- bug 4231894
375 x_address1 := c_address.address1;
376 x_address2 := c_address.address2;
377 x_address3 := c_address.address3;
378 x_address4 := c_address.address4;
379 x_city := c_address.city;
380 -- Bug 5009103, The state would be passed
381 IF G_Dp_Service_Flag <> 'Y' THEN
382 x_state := c_address.state;
383 END IF; -- 5009103 ends
384 x_postal_code := c_address.postal_code;
385 x_country := c_address.country;
386
387 -- Bug 9583024 : START
388 IF p_contact_id IS NOT NULL THEN --bug5140692
389 IF l_debug_level > 0 THEN
390 oe_debug_pub.add('Before calling Get_Contact_name...' ) ;
391 END IF;
392
393 Get_Contact_name(p_contact_id, l_contact_name , l_contact_hz_id, l_contact_number);
394
395 IF l_debug_level > 0 THEN
396 OE_DEBUG_PUB.Add('After calling Get_Contact_name ...');
397 END IF;
398
399 x_contact_person := l_contact_name;
400 x_contact_hz_party_id := l_contact_hz_id;
401 x_contact_hz_party_number := l_contact_number;
402 END IF;
403
404 x_hz_party_id := c_address.hz_party_id;
405 x_hz_party_number := c_address.hz_party_number;
406 x_party_site_number := c_address.party_site_number;
407 -- Bug 9583024 : END
408 END LOOP;
409 END IF;
410
411 IF (p_source_type ='SHIP_FROM') THEN
412 FOR c_sf_address in C_SHIP_FROM_ADDRESS_SF(p_source_id)
413 LOOP
414 x_party_name := c_sf_address.party_name;
415 x_alternate_name := NULL; -- bug 4231894
416 x_address1 := c_sf_address.address1;
417 x_address2 := c_sf_address.address2;
418 x_address3 := c_sf_address.address3;
419 x_address4 := NULL;
420 x_city := c_sf_address.city;
421 IF G_Dp_Service_Flag <> 'Y' THEN --bug 5009103
422 x_state := c_sf_address.state;
423 END IF;
424 x_postal_code:= c_sf_address.postal_code;
425 x_country := c_sf_address.country;
426 END LOOP;
427 END IF;
428
429 IF (p_source_type ='SOLD_TO') THEN
430 -- begin : 9130718
431 IF (p_org_id_passed = 'Y') THEN
432 /*
433 (Only)If there is no customer location entered on the order
434 header, we will pick any site irrespective of usage type.
435 */
436 FOR c_sold in C_SOLD_TO_ADDRESS(p_source_id)
437 LOOP
438 x_party_name := c_sold.party_name;
439 x_alternate_name := c_sold.alternate_name; -- bug 4231894
440 x_address1 := c_sold.address1;
441 x_address2 := c_sold.address2;
442 x_address3 := c_sold.address3;
443 x_address4 := c_sold.address4;
444 x_city := c_sold.city;
445 IF G_Dp_Service_Flag <> 'Y' THEN --bug 5009103
446 x_state := c_sold.state;
447 END IF;
448 x_postal_code:= c_sold.postal_code;
449 x_country := c_sold.country;
450
451 IF p_contact_id IS NOT NULL THEN --bug5140692
452
453 -- Bug 9583024 : START
454 IF l_debug_level > 0 THEN
455 oe_debug_pub.add('Before calling Get_Contact_name...' ) ;
456 END IF;
457
458 Get_Contact_name(p_contact_id, l_contact_name , l_contact_hz_id, l_contact_number);
459
460 IF l_debug_level > 0 THEN
461 OE_DEBUG_PUB.Add('After calling Get_Contact_name ...');
462 END IF;
463
464 x_contact_person := l_contact_name;
465 x_contact_hz_party_id := l_contact_hz_id;
466 x_contact_hz_party_number := l_contact_number;
467 END IF;
468
469 x_hz_party_id := c_sold.hz_party_id ;
470 x_hz_party_number := c_sold.hz_party_number ;
471 x_party_site_number := c_sold.party_site_number ;
472 -- Bug 9583024 : END
473 END LOOP;
474 ELSE
475 -- end : 9130718
476 FOR c_sold in C_GET_ADDRESS(p_source_id,p_source_type) -- bug 7261101
477 LOOP
478 x_party_name := c_sold.party_name;
479 x_alternate_name := c_sold.alternate_name; -- bug 4231894
480 x_address1 := c_sold.address1;
481 x_address2 := c_sold.address2;
482 x_address3 := c_sold.address3;
483 x_address4 := c_sold.address4;
484 x_city := c_sold.city;
485 IF G_Dp_Service_Flag <> 'Y' THEN --bug 5009103
486 x_state := c_sold.state;
487 END IF;
488 x_postal_code:= c_sold.postal_code;
489 x_country := c_sold.country;
490 IF p_contact_id IS NOT NULL THEN --bug5140692
491
492 -- Bug 9583024 : START
493 IF l_debug_level > 0 THEN
494 oe_debug_pub.add('Before calling Get_Contact_name...' ) ;
495 END IF;
496
497 Get_Contact_name(p_contact_id, l_contact_name , l_contact_hz_id, l_contact_number);
498
499 IF l_debug_level > 0 THEN
500 OE_DEBUG_PUB.Add('After calling Get_Contact_name ...');
501 END IF;
502 x_contact_person := l_contact_name;
503
504 x_contact_hz_party_id := l_contact_hz_id;
505 x_contact_hz_party_number := l_contact_number;
506 END IF;
507
508 x_hz_party_id := c_sold.hz_party_id ;
509 x_hz_party_number := c_sold.hz_party_number ;
510 x_party_site_number := c_sold.party_site_number ;
511 -- Bug 9583024 : END
512 END LOOP;
513 END IF;
514 END IF;
515
516 IF l_debug_level > 0 THEN
517 oe_debug_pub.add('Exiting get_address',4);
518 END IF;
519 EXCEPTION
520 WHEN OTHERS THEN
521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
522 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
523 FND_MSG_PUB.Add_Exc_Msg
524 (G_PKG_NAME,
525 l_api_name
526 );
527 END IF;
528
529 END Get_Address;
530
531
532 /*-----------------------------------------------------+
533 | Name : Update_Process_Flag |
534 | Parameters : IN p_line_id |
535 | IN p_header_id |
536 | |
537 | Description : This Procedure checks whether any |
538 | requests exists for the line id |
539 | with process flag not equal to 4 |
540 | and calls Update_Process_Flag. |
541 +-----------------------------------------------------*/
542
543 PROCEDURE Update_Process_Flag(
544 p_header_id IN NUMBER, -- Bug 11797031
545 p_line_id IN NUMBER
546 ) IS
547
548 l_request_control_id_list WSH_ITM_UTIL.CONTROL_ID_LIST;
549 x_return_status VARCHAR2(30);
550
551 --
552 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
553 --
554 BEGIN
555
556 IF l_debug_level > 0 THEN
557 oe_debug_pub.add('Entering update process flag..' , 4 ) ;
558 END IF;
559
560 SELECT request_control_id
561 BULK COLLECT
562 INTO l_request_control_id_list
563 FROM WSH_ITM_REQUEST_CONTROL
564 WHERE application_id = 660
565 AND original_system_line_reference = p_line_id
566 AND original_system_reference = p_header_id -- Bug 11797031
567 AND Process_flag <> 4;
568
569 IF l_request_control_id_list.count > 0 THEN
570
571 WSH_ITM_UTIL.Update_process_Flag(
572 l_request_control_id_list,
573 4,
574 x_return_status);
575 END IF;
576
577
578 IF l_debug_level > 0 THEN
579 oe_debug_pub.add('Update process flag returned with ..'|| x_return_status ,1);
580 oe_debug_pub.add('Exiting update process flag..',4 ) ;
581 END IF;
582
583 Exception
584 WHEN NO_DATA_FOUND THEN
585 NULL;
586 END Update_Process_Flag;
587
588
589 /*-----------------------------------------------------+
590 | Name : Init_Address_Table |
591 | Parameters : |
592 | |
593 | Description : This Procedure initializes Address |
594 | table |
595 +-----------------------------------------------------*/
596
597 PROCEDURE Init_Address_Table
598 IS
599 I INTEGER;
600 --
601 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
602 --
603 BEGIN
604
605 FOR I in 1..Address_Table.COUNT
606 LOOP
607
608 Address_Table(I).add_source_type := NULL;
609 Address_Table(I).add_source_orgid := NULL;
610 Address_Table(I).add_contact_id := NULL;
611 Address_Table(I).add_party_name := NULL;
612 Address_Table(I).add_alternate_name := NULL; -- bug 4231894
613 Address_Table(I).add_party_address1 := NULL; -- bug 7485980
614 Address_Table(I).add_party_address2 := NULL; -- bug 7485980
615 Address_Table(I).add_party_address3 := NULL; -- bug 7485980
616 Address_Table(I).add_party_address4 := NULL; -- bug 7485980
617 Address_Table(I).add_party_city := NULL;
618 Address_Table(I).add_party_state := NULL;
619 Address_Table(I).add_party_country := NULL;
620 Address_Table(I).add_party_postal_code := NULL;
621 Address_Table(I).add_party_phone := NULL;
622 Address_Table(I).add_party_email := NULL;
623 Address_Table(I).add_party_fax := NULL;
624 Address_Table(I).add_party_url := NULL;
625 Address_Table(I).add_party_contact_name := NULL;
626 --Added for Bug 9583024
627 Address_Table(I).add_HZ_PARTY_ID := NULL;
628 Address_Table(I).add_HZ_PARTY_NUMBER := NULL;
629 Address_Table(I).add_PARTY_SITE_NUMBER := NULL;
630 Address_Table(I).add_contact_hz_party_id := NULL;
631 Address_Table(I).add_contact_hz_party_number := NULL;
632 END LOOP;
633 END;
634
635
636 /*-----------------------------------------------------+
637 | Name : Create_Request |
638 | Parameters : IN p_master_organization_id |
639 | p_line_rec |
640 | OUT NOCOPY x_return_status |
641 | |
642 | Description : This Procedure inserts records into |
643 | Request interface tables |
644 | |
645 +-----------------------------------------------------*/
646
647
648
649 PROCEDURE Create_Request
650 ( p_master_organization_id IN NUMBER
651 , p_line_rec IN OE_ORDER_PUB.line_rec_type
652 , x_return_status OUT NOCOPY VARCHAR2
653 )
654 IS
655 l_api_name CONSTANT VARCHAR2(30) := 'Create_Request';
656 l_request_control_id NUMBER;
657 l_request_set_id NUMBER;
658 l_party_id NUMBER;
659 x_party_name VARCHAR2(2000);
660 x_alternate_name VARCHAR2(320); -- bug 4231894
661 x_party_address1 VARCHAR2(2000); -- bug 7485980
662 x_party_address2 VARCHAR2(2000); -- bug 7485980
663 x_party_address3 VARCHAR2(2000); -- bug 7485980
664 x_party_address4 VARCHAR2(2000); -- bug 7485980
665 x_party_city VARCHAR2(60);
666 x_party_state VARCHAR2(60);
667 x_party_country VARCHAR2(60);
668 x_postal_code VARCHAR2(60);
669 x_phone VARCHAR2(80);
670 x_email VARCHAR2(2000);
671 x_url VARCHAR2(2000);
672 x_fax VARCHAR2(80);
673 l_service_types VARCHAR2(50);
674 l_addl_country_name VARCHAR2(5);
675 x_contact_name VARCHAR2(360);
676 x_service_tbl WSH_ITM_UTIL.SERVICE_TBL_TYPE;
677 x_supports_combination_flag VARCHAR2(1);
678 l_Generic_Service_Flag VARCHAR2(1);
679 I INTEGER;
680 J INTEGER;
681 l_rec_count INTEGER;
682 l_return_status VARCHAR2(30);
683 l_order_number NUMBER;
684 l_order_type VARCHAR2(30);
685 l_cust_po_number VARCHAR2(50);
686 l_transactional_curr_code VARCHAR2(15);
687 l_conversion_type_code VARCHAR2(30);
688 l_conversion_rate NUMBER;
689 l_ordered_date DATE;
690 l_organization_code VARCHAR2(3);
691
692 -- Bug 9583024 : Cursor to Check for GTM Flows
693 CURSOR cur_check_gtm_flows IS
694 SELECT decode (wits.value, 'FALSE', 'N', null, 'N', 'TRUE', 'Y', 'N')
695 FROM wsh_itm_parameter_setups_b wits
696 WHERE wits.PARAMETER_NAME = 'WSH_ITM_INTG_GTM';
697
698 -- 4380792 Commented the below unused type code variable
699 -- and added term name variable
700 --l_payment_type_code VARCHAR2(30);
701 l_payment_term_name VARCHAR2(15);
702 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
703 l_sold_to_contact_id NUMBER; -- BUG 5140692
704
705 l_product_code VARCHAR2(100); -- BUG 5408161
706 l_Item_type VARCHAR2(80); -- BUG 5408161
707 l_order_line_number VARCHAR2(100); --Bug 5647666
708 l_sold_to_site_use_id NUMBER; --Bug 7261101
709 l_org_id_passed VARCHAR2(1) := 'N'; -- Bug 9130718
710 -- Added for Bug 9583024
711 x_hz_party_id HZ_PARTIES.PARTY_ID%TYPE;
712 x_hz_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
713 x_party_site_number HZ_PARTY_SITES.PARTY_SITE_NUMBER%TYPE;
714 x_contact_hz_party_id HZ_PARTIES.PARTY_ID%TYPE;
715 x_contact_hz_party_number HZ_PARTIES.PARTY_NUMBER%TYPE;
716 l_itm_intg_gtm VARCHAR2(1) := 'N';
717
718 BEGIN
719
720 l_Generic_Service_Flag := 'N';
721 IF l_debug_level > 0 THEN
722 oe_debug_pub.add('Start creating request...' ) ;
723 END IF;
724
725
726 -- Update Process Flag for requests with Errors
727
728 Update_Process_Flag(p_line_rec.header_id, p_line_rec.line_id); -- Bug 11797031
729
730 x_return_status := FND_API.G_RET_STS_SUCCESS;
731
732
733 -- Check for Ship From Org Id and Ship To Org Id
734 -- Ship From/To Org_id is Mandatory for processing.
735
736 IF p_line_rec.ship_from_org_id IS NULL OR
737 p_line_rec.ship_to_org_id IS NULL
738 THEN
739
740 IF l_debug_level > 0 THEN
741 oe_debug_pub.add('Ship from and ship to orgid are mandatory' , 1 ) ;
742 END IF;
743
744 FND_MESSAGE.SET_NAME('ONT','OE_ECS_MISSING_SRC_DEST');
745
746 IF p_line_rec.ship_from_org_id IS NULL THEN
747 FND_MESSAGE.SET_TOKEN('SRC_DEST',
748 OE_ORDER_UTIL.Get_Attribute_Name('SHIP_FROM_ORG_ID'));
749 END IF;
750
751 IF p_line_rec.ship_to_org_id IS NULL THEN
752 FND_MESSAGE.SET_TOKEN('SRC_DEST',
753 OE_ORDER_UTIL.Get_Attribute_Name('SHIP_TO_ORG_ID'));
754 END IF;
755
756 OE_MSG_PUB.Add;
757 x_return_status := FND_API.G_RET_STS_ERROR;
758 RETURN;
759 END IF;
760
761 -- Check whether Additional Country Check is needed
762 -- Get the Service type Code
763 -- Moved this call before building the address table for bug 5009103
764 IF l_debug_level > 0 THEN
765 oe_debug_pub.add('Before calling get service details...' ) ;
766 END IF;
767
768 WSH_ITM_UTIL.Get_Service_Details(660,
769 p_master_organization_id,
770 p_line_rec.ship_from_org_id,
771 x_service_tbl,
772 x_supports_combination_flag,
773 l_return_status);
774
775 IF l_debug_level > 0 THEN
776 OE_DEBUG_PUB.Add('Get service details returned with ...'|| l_return_status);
777 END IF;
778
779 -- Check for Denied Party Service
780
781 FOR l_serv IN 1..x_service_tbl.COUNT
782 LOOP
783 IF x_service_tbl(l_serv).Service_Type_Code = 'OM_EXPORT_COMPLIANCE' AND
784 OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
785 l_Generic_Service_flag := 'Y';
786 l_service_types := x_service_tbl(l_serv).Service_Type_Code;
787
788 -- Generic Screening only if it is setup.
789 EXIT;
790 END IF;
791
792 IF x_service_tbl(l_serv).Service_Type_Code = 'DP' THEN
793 l_addl_country_name := x_service_tbl(l_serv).Addl_Country_Code;
794 l_service_types := x_service_tbl(l_serv).Service_Type_Code;
795 G_Dp_Service_flag := 'Y';
796 END IF;
797 END LOOP;
798 -- Return if service is not available.
799 IF G_Dp_service_flag = 'N' AND l_Generic_Service_Flag = 'N'THEN
800 IF l_debug_level > 0 THEN
801 oe_debug_pub.add('No Service is Available...');
802 END IF;
803 x_return_status := FND_API.G_RET_STS_ERROR;
804 RETURN;
805 END IF;
806
807 -- Initialize the Address Table
808 Init_Address_Table();
809
810 -- gtm flow check Bug 9583024
811 OPEN cur_check_gtm_flows;
812 FETCH cur_check_gtm_flows into l_itm_intg_gtm;
813 CLOSE cur_check_gtm_flows;
814
815 IF l_debug_level > 0 THEN
816 oe_debug_pub.add('GTM Integration enabled ' || l_itm_intg_gtm);
817 oe_debug_pub.add('end_customer_id '||to_char(p_line_rec.end_customer_id));
818 oe_debug_pub.add('end_customer_site_use_id '||to_char(p_line_rec.end_customer_site_use_id));
819 oe_debug_pub.add('end_customer_contact_id '||to_char(p_line_rec.end_customer_contact_id));
820 END IF;
821
822 -- Fill Address table with Source types
823 Address_table(1).add_source_type := 'SHIP_FROM';
824 Address_table(2).add_source_type := 'SHIP_TO';
825 Address_table(3).add_source_type := 'DELIVER_TO';
826 Address_table(4).add_source_type := 'BILL_TO';
827 Address_table(5).add_source_type := 'SOLD_TO';
828 Address_table(6).add_source_type := 'INTERMED_SHIP_TO';
829 -- Bug 9583024
830 IF (l_itm_intg_gtm = 'Y' ) then
831 Address_table(7).add_source_type := 'END_CUSTOMER';
832 END IF;
833
834 -- Fill Address table with Source Org Ids
835
836 -- Bug 7261101
837 BEGIN
838 SELECT sold_to_site_use_id
839 INTO l_sold_to_site_use_id
840 FROM oe_order_headers_all
841 WHERE header_id = p_line_rec.header_id;
842
843 IF l_sold_to_site_use_id IS NULL THEN
844 l_sold_to_site_use_id := p_line_rec.sold_to_org_id; -- Bug 9130718
845 l_org_id_passed := 'Y'; -- Bug 9130718
846 END IF;
847 EXCEPTION
848 WHEN OTHERS THEN
849 l_sold_to_site_use_id := NULL;
850 END;
851 --Bug 7261101
852
853 Address_table(1).add_source_orgid := p_line_rec.ship_from_org_id;
854 Address_table(2).add_source_orgid := p_line_rec.ship_to_org_id;
855 Address_table(3).add_source_orgid := p_line_rec.deliver_to_org_id;
856 Address_table(4).add_source_orgid := p_line_rec.invoice_to_org_id;
857 Address_table(5).add_source_orgid := l_sold_to_site_use_id; -- Bug 7261101
858 Address_table(6).add_source_orgid := p_line_rec.intermed_ship_to_org_id;
859 -- Bug 9583024
860 IF (l_itm_intg_gtm = 'Y' ) then
861 Address_table(7).add_source_orgid := p_line_rec.end_customer_site_use_id;
862 END IF;
863
864
865
866 BEGIN --bug 5140692
867 SELECT sold_to_contact_id
868 INTO l_sold_to_contact_id
869 FROM oe_order_headers_all
870 WHERE header_id = p_line_rec.header_id;
871 EXCEPTION
872 WHEN OTHERS THEN
873 l_sold_to_contact_id := NULL;
874 END;
875
876 -- Fill Address table with Contact Org Ids
877 Address_table(1).add_contact_id := NULL;
878 Address_table(2).add_contact_id := p_line_rec.ship_to_contact_id;
879 Address_table(3).add_contact_id := p_line_rec.deliver_to_contact_id;
880 Address_table(4).add_contact_id := p_line_rec.invoice_to_contact_id;
881 Address_table(5).add_contact_id := l_sold_to_contact_id; --bug 5140692
882 Address_table(6).add_contact_id := p_line_rec.intermed_ship_to_contact_id;
883 -- Bug 9583024
884 IF (l_itm_intg_gtm = 'Y' ) then
885 Address_table(7).add_contact_id := p_line_rec.end_customer_contact_id;
886 END IF;
887
888
889 -- Get Address details for Source types
890
891 FOR I in 1..Address_table.COUNT
892 -- Modified the call to procedure as signature has been changed Bug 7485980
893 LOOP
894
895 Get_Address(
896 Address_table(I).add_source_orgid,
897 Address_table(I).add_source_type,
898 Address_table(I).add_contact_id,
899 l_org_id_passed, --Bug 9130718
900 x_party_name,
901 x_alternate_name, -- bug 4231894
902 x_party_address1, -- bug 7485980
903 x_party_address2, -- bug 7485980
904 x_party_address3, -- bug 7485980
905 x_party_address4, -- bug 7485980
906 x_party_city,
907 x_party_state,
908 x_party_country,
909 x_postal_code,
910 x_phone,
911 x_email,
912 x_fax,
913 x_url,
914 x_contact_name,
915 x_hz_party_id , -- Bug 9583024
916 x_hz_party_number , -- Bug 9583024
917 x_party_site_number , -- Bug 9583024
918 x_contact_hz_party_id , -- Bug 9583024
919 x_contact_hz_party_number , -- Bug 9583024
920 x_return_status);
921
922 -- Fill Address table with details
923
924 Address_table(I).add_party_name := x_party_name;
925 Address_table(I).add_alternate_name := x_alternate_name; --bug 4231894
926 Address_table(I).add_party_address1 := x_party_address1; --bug 7485980
927 Address_table(I).add_party_address2 := x_party_address2; --bug 7485980
928 Address_table(I).add_party_address3 := x_party_address3; --bug 7485980
929 Address_table(I).add_party_address4 := x_party_address4; --bug 7485980
930 Address_table(I).add_party_city := x_party_city;
931 Address_table(I).add_party_state := x_party_state;
932 Address_table(I).add_party_country := x_party_country;
933 Address_table(I).add_party_postal_code := x_postal_code;
934 Address_table(I).add_party_phone := x_phone;
935 Address_table(I).add_party_email := x_email;
936 Address_table(I).add_party_fax := x_fax;
937 Address_table(I).add_party_url := x_url;
938 Address_table(I).add_party_contact_name := x_contact_name;
939 -- Bug 9583024
940 Address_Table(I).add_hz_party_id := x_hz_party_id ;
941 Address_Table(I).add_hz_party_number := x_hz_party_number ;
942 Address_Table(I).add_party_site_number := x_party_site_number ;
943 Address_Table(I).add_contact_hz_party_id := x_contact_hz_party_id ;
944 Address_Table(I).add_contact_hz_party_number := x_contact_hz_party_number ;
945
946
947 IF l_debug_level > 0 THEN
948 OE_DEBUG_PUB.Add(Address_table(i).add_source_type ||'_country code:'||x_party_country,3);
949 OE_DEBUG_PUB.Add(Address_table(i).add_source_type ||' party:'||x_party_name,3);
950 OE_DEBUG_PUB.Add(Address_table(i).add_source_type ||' state:'||x_party_state,3);-- bug 5009103
951 END IF;
952 END LOOP; -- Loop for getting Address details
953
954 -- Check For Ship From and Ship To Party names
955 -- Both should not be NULL.
956
957 IF Address_table(1).add_party_name IS NULL OR
958 Address_table(2).add_party_name IS NULL
959 THEN
960
961 FND_MESSAGE.SET_NAME('ONT','OE_ECS_INVALID_PARTY_ADDR');
962
963 IF Address_table(1).add_party_name IS NULL THEN
964 FND_MESSAGE.SET_TOKEN('PARTY',
965 OE_ORDER_UTIL.Get_Attribute_Name('SHIP_FROM_ORG_ID'));
966 END IF;
967
968 IF Address_table(2).add_party_name IS NULL THEN
969 FND_MESSAGE.SET_TOKEN('PARTY',
970 OE_ORDER_UTIL.Get_Attribute_Name('SHIP_TO_ORG_ID'));
971 END IF;
972
973 OE_MSG_PUB.Add;
974
975 IF l_debug_level > 0 THEN
976 oe_debug_pub.add('Ship from and customer party name are mandatory' , 1 ) ;
977 END IF;
978
979 x_return_status := FND_API.G_RET_STS_ERROR;
980 RETURN;
981 END IF;
982
983 -- Inserting Records in to wsh_itm_request_control
984
985 -- If parent Country Check is needed we will insert one more record for
986 -- additional country check with country name
987
988
989 IF l_Addl_Country_Name IS NOT NULL THEN
990 l_rec_count := 2;
991 SELECT wsh_itm_request_set_s.NEXTVAL
992 INTO l_request_set_id
993 FROM dual;
994 IF l_debug_level > 0 THEN
995 oe_debug_pub.add('Addl country name:'||l_addl_country_name);
996 END IF;
997 ELSE
998 l_rec_count := 1;
999 END IF;
1000
1001
1002
1003 FOR I in 1..l_rec_count LOOP
1004
1005 SELECT wsh_itm_request_control_s.NEXTVAL
1006 INTO l_request_control_id
1007 FROM dual;
1008
1009
1010 -- Retrieve the Header Information
1011
1012 -- 4380792 Modified the below SQL to fetch
1013 -- Payment Terms Name instead of payment type code.
1014 /*
1015 SELECT order_number,order_type,cust_po_number,
1016 transactional_curr_code,conversion_type_code,
1017 conversion_rate,ordered_date, terms
1018 INTO l_order_number,l_order_type,l_cust_po_number,
1019 l_transactional_curr_code,l_conversion_type_code,
1020 l_conversion_rate,l_ordered_date,l_payment_term_name
1021 FROM oe_order_headers_v
1022 WHERE header_id = p_line_rec.header_id; */
1023
1024 -- Fixed for SQLperf, SQL Repository ID 14882287
1025 SELECT h.order_number, ot.name, h.cust_po_number, h.transactional_curr_code,
1026 h.conversion_type_code, h.conversion_rate, h.ordered_date, term.name
1027 INTO l_order_number, l_order_type, l_cust_po_number, l_transactional_curr_code,
1028 l_conversion_type_code, l_conversion_rate, l_ordered_date, l_payment_term_name
1029 FROM oe_order_headers h, oe_transaction_types_tl ot, ra_terms_tl term
1030 WHERE h.header_id = p_line_rec.header_id
1031 AND h.order_type_id = ot.transaction_type_id
1032 AND ot.language = userenv('LANG')
1033 AND h.payment_term_id = term.term_id(+)
1034 AND term.Language(+) = userenv('LANG');
1035
1036 -- Retrieve Organization Code
1037
1038 SELECT organization_code
1039 INTO l_organization_code
1040 FROM mtl_parameters
1041 WHERE organization_id = p_line_rec.ship_from_org_id;
1042
1043 --Bug 5647666
1044 -- Get concatenated order_line_number
1045 -- LINE_NUMBER.SHIPMENT_NUMBER.OPTION_NUMBER.COMPONENT_NUMBER.SERVICE_NUMBER
1046
1047 l_order_line_number := OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER(p_line_rec.line_id);
1048
1049 -- If Parent country check is needed we will change the SHIP FROM
1050 -- Country name to parent country
1051 -- Source Org_id is entered as -1
1052
1053 IF I = 2 THEN
1054 Address_table(1).add_source_orgid := -1;
1055 Address_table(1).add_party_address1 := NULL; --bug 7485980
1056 Address_table(1).add_party_address2 := NULL; --bug 7485980
1057 Address_table(1).add_party_address3 := NULL; --bug 7485980
1058 Address_table(1).add_party_address4 := NULL; --bug 7485980
1059 Address_table(1).add_party_city := NULL;
1060 Address_table(1).add_party_state := NULL;
1061 Address_table(1).add_party_country := l_addl_country_name;
1062 Address_table(1).add_party_postal_code := NULL;
1063 Address_table(1).add_party_contact_name := NULL;
1064 Address_table(1).add_party_phone := NULL;
1065 Address_table(1).add_party_email := NULL;
1066 Address_table(1).add_party_fax := NULL;
1067 Address_table(1).add_party_url := NULL;
1068 -- Bug 9583024
1069 Address_Table(1).add_HZ_PARTY_ID := NULL;
1070 Address_Table(1).add_HZ_PARTY_NUMBER := NULL;
1071 Address_Table(1).add_PARTY_SITE_NUMBER := NULL;
1072 Address_Table(1).add_contact_hz_party_id := NULL;
1073 Address_Table(1).add_contact_hz_party_number := NULL;
1074 END IF;
1075
1076 INSERT INTO WSH_ITM_REQUEST_CONTROL (
1077 REQUEST_CONTROL_ID,
1078 REQUEST_SET_ID,
1079 APPLICATION_ID,
1080 MASTER_ORGANIZATION_ID,
1081 ORGANIZATION_CODE,
1082 APPLICATION_USER_ID,
1083 SERVICE_TYPE_CODE,
1084 TRANSACTION_DATE,
1085 SHIP_FROM_COUNTRY_CODE,
1086 SHIP_TO_COUNTRY_CODE,
1087 ORIGINAL_SYSTEM_REFERENCE,
1088 ORIGINAL_SYSTEM_LINE_REFERENCE,
1089 PROCESS_FLAG,
1090 RESPONSE_HEADER_ID,
1091 DEBUG_FLAG,
1092 ONLINE_FLAG,
1093 ATTRIBUTE1_NAME,
1094 ATTRIBUTE2_NAME,
1095 ATTRIBUTE3_NAME,
1096 ATTRIBUTE4_NAME,
1097 ATTRIBUTE5_NAME,
1098 ATTRIBUTE6_NAME,
1099 ATTRIBUTE7_NAME,
1100 ATTRIBUTE8_NAME,
1101 ATTRIBUTE9_NAME,
1102 ATTRIBUTE10_NAME,
1103 ATTRIBUTE11_NAME,
1104 ATTRIBUTE12_NAME,
1105 ATTRIBUTE13_NAME,
1106 ATTRIBUTE14_NAME,
1107 ATTRIBUTE15_NAME,
1108 ATTRIBUTE1_VALUE,
1109 ATTRIBUTE2_VALUE,
1110 ATTRIBUTE3_VALUE,
1111 ATTRIBUTE4_VALUE,
1112 ATTRIBUTE5_VALUE,
1113 ATTRIBUTE6_VALUE,
1114 ATTRIBUTE7_VALUE,
1115 ATTRIBUTE8_VALUE,
1116 ATTRIBUTE9_VALUE,
1117 ATTRIBUTE10_VALUE,
1118 ATTRIBUTE11_VALUE,
1119 ATTRIBUTE12_VALUE,
1120 ATTRIBUTE13_VALUE,
1121 ATTRIBUTE14_VALUE,
1122 ATTRIBUTE15_VALUE,
1123 ORDER_NUMBER,
1124 ORDER_TYPE,
1125 OPERATING_UNIT,
1126 CUST_PO_NUM ,
1127 TRANSACTIONAL_CURR_CODE ,
1128 CONVERSION_TYPE_CODE,
1129 CONVERSION_RATE,
1130 ORDERED_DATE,
1131 SHIPPING_METHOD_CODE ,
1132 REQUEST_DATE,
1133 FREIGHT_TERMS_CODE,
1134 PAYMENT_NAME,
1135 PAYMENT_TERM_ID,
1136 ORDERED_QUANTITY,
1137 ORDERED_QUANTITY_UOM, --bug 3640122
1138 LINE_NUMBER,
1139 ORDER_LINE_NUMBER, --Bug 5647666
1140 UNIT_LIST_PRICE,
1141 UNIT_SELLING_PRICE,
1142 TRIGGERING_POINT,
1143 CREATION_DATE,
1144 CREATED_BY,
1145 LAST_UPDATED_BY,
1146 LAST_UPDATE_DATE,
1147 LAST_UPDATE_LOGIN,
1148 ORGANIZATION_ID, --Added for bug 6639636
1149 TOP_MODEL_LINE_ID, -- Added for ER 6490366
1150 ATO_LINE_ID, -- Bug 9670588
1151 LINK_TO_LINE_ID, -- Bug 9670588
1152 SPLIT_FROM_LINE_ID -- Bug 9670588
1153 )
1154 VALUES (
1155 l_request_control_id,
1156 l_request_set_id,
1157 660,
1158 p_master_organization_id,
1159 l_organization_code,
1160 FND_GLOBAL.USER_ID,
1161 l_service_types,
1162 sysdate,
1163 Address_table(1).add_party_country,
1164 Address_table(2).add_party_country,
1165 p_line_rec.header_id,
1166 p_line_rec.line_id,
1167 0,
1168 null,
1169 null,
1170 null,
1171 null,
1172 null,
1173 null,
1174 null,
1175 null,
1176 null,
1177 null,
1178 null,
1179 null,
1180 null,
1181 null,
1182 null,
1183 null,
1184 null,
1185 null,
1186 null,
1187 null,
1188 null,
1189 null,
1190 null,
1191 null,
1192 null,
1193 null,
1194 null,
1195 null,
1196 null,
1197 null,
1198 null,
1199 null,
1200 null,
1201 l_order_number,
1202 l_order_type,
1203 p_line_rec.org_id,
1204 l_cust_po_number,
1205 l_transactional_curr_code,
1206 l_conversion_type_code,
1207 l_conversion_rate,
1208 l_ordered_date,
1209 p_line_rec.shipping_method_code,
1210 p_line_rec.request_date,
1211 p_line_rec.freight_terms_code,
1212 l_payment_term_name,
1213 p_line_rec.payment_term_id,
1214 p_line_rec.ordered_quantity,
1215 p_line_rec.order_quantity_uom,--bug 3640122
1216 p_line_rec.line_number,
1217 l_order_line_number, --Bug 5647666
1218 p_line_rec.unit_list_price,
1219 p_line_rec.unit_selling_price,
1220 'ORDER_SCHEDULING',
1221 sysdate,
1222 FND_GLOBAL.USER_ID,
1223 FND_GLOBAL.USER_ID,
1224 sysdate,
1225 FND_GLOBAL.USER_ID,
1226 p_line_rec.ship_from_org_id, --Added for bug 6639636
1227 p_line_rec.top_model_line_id, -- Added for bug 6490366
1228 p_line_rec.ato_line_id, --bug 9670588
1229 p_line_rec.link_to_line_id , -- Bug 9670588
1230 p_line_rec.split_from_line_id -- Bug 9670588
1231 );
1232
1233
1234
1235 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1236
1237 IF l_debug_level > 0 THEN
1238 oe_debug_pub.add('Inserted record in to wsh_itm_request_control...' , 1 ) ;
1239 oe_debug_pub.add('WSH_ITM_ITEMS INVENTORY_ITEM_ID :'||p_line_rec.inventory_item_id , 1 ) ;
1240 oe_debug_pub.add('WSH_ITM_ITEMS ORGANIZATION_CODE :'||l_organization_code , 1 ) ;
1241 END IF;
1242
1243 -- BUG 5408161
1244 -- Retrieve concatenated_segments ('ProductCode' in WSH_ITM_ITEMS table)
1245 SELECT concatenated_segments into l_product_code
1246 FROM mtl_system_items_vl
1247 WHERE inventory_item_id = p_line_rec.inventory_item_id
1248 AND organization_id = p_line_rec.ship_from_org_id; --l_organization_code ;
1249
1250
1251 -- Bug 5739175
1252 BEGIN
1253 -- Retrieve Item_type
1254 Select MEANING INTO l_Item_type
1255 FROM MTL_SYSTEM_ITEMS_B items, FND_LOOKUP_VALUES FLV
1256 WHERE items.INVENTORY_ITEM_ID = p_line_rec.inventory_item_id
1257 AND items.ORGANIZATION_ID = p_line_rec.ship_from_org_id --l_organization_code
1258 AND FLV.LOOKUP_CODE = items.ITEM_TYPE AND FLV.LOOKUP_TYPE = 'ITEM_TYPE'
1259 AND FLV.VIEW_APPLICATION_ID = 3
1260 AND FLV.LANGUAGE = userenv('LANG')
1261 AND FLV.ENABLED_FLAG = 'Y' ;
1262
1263 EXCEPTION
1264 WHEN OTHERS THEN
1265 l_Item_type := null;
1266 END;
1267
1268 INSERT INTO WSH_ITM_ITEMS (
1269 ITEM_ID,
1270 REQUEST_CONTROL_ID,
1271 INVENTORY_ITEM_ID,
1272 ORGANIZATION_CODE,
1273 OPERATING_UNIT,
1274 PRODUCT_CODE,
1275 ITEM_TYPE,
1276 CREATION_DATE,
1277 CREATED_BY,
1278 LAST_UPDATED_BY,
1279 LAST_UPDATE_DATE,
1280 LAST_UPDATE_LOGIN
1281 )
1282 VALUES (
1283 wsh_itm_items_s.NEXTVAL,
1284 l_request_control_id,
1285 p_line_rec.inventory_item_id,
1286 l_organization_code,
1287 p_line_rec.org_id,
1288 l_product_code,
1289 l_Item_type,
1290 sysdate,
1291 FND_GLOBAL.USER_ID,
1292 FND_GLOBAL.USER_ID,
1293 sysdate,
1294 FND_GLOBAL.USER_ID
1295 );
1296
1297 IF l_debug_level > 0 THEN
1298 oe_debug_pub.add('Inserted record in to wsh_itm_items...' , 1 ) ;
1299 END IF;
1300
1301 END IF;
1302
1303 -- Inserting Records in to wsh_itm_parties
1304 -- Records inserted are equal to number of source types(parties)
1305
1306 FOR J in 1..Address_table.COUNT LOOP
1307
1308
1309 IF Address_table(J).add_source_orgid IS NOT NULL THEN
1310
1311 SELECT wsh_itm_parties_s.NEXTVAL
1312 INTO l_party_id
1313 FROM dual;
1314
1315 INSERT INTO WSH_ITM_PARTIES (
1316 PARTY_ID,
1317 REQUEST_CONTROL_ID,
1318 ORIGINAL_SYSTEM_REFERENCE,
1319 ORIGINAL_SYSTEM_LINE_REFERENCE,
1320 SOURCE_ORG_ID,
1321 PARTY_TYPE,
1322 PARTY_NAME,
1323 ALTERNATE_NAME, -- BUG 4231894
1324 PARTY_ADDRESS1,
1325 PARTY_ADDRESS2,
1326 PARTY_ADDRESS3,
1327 PARTY_ADDRESS4,
1328 PARTY_ADDRESS5,
1329 PARTY_CITY,
1330 PARTY_STATE,
1331 PARTY_COUNTRY_CODE,
1332 PARTY_COUNTRY_NAME,
1333 POSTAL_CODE,
1334 CONTACT_NAME,
1335 PHONE,
1336 EMAIL,
1337 FAX,
1338 WEB,
1339 ATTRIBUTE1_NAME,
1340 ATTRIBUTE2_NAME,
1341 ATTRIBUTE3_NAME,
1342 ATTRIBUTE4_NAME,
1343 ATTRIBUTE5_NAME,
1344 ATTRIBUTE6_NAME,
1345 ATTRIBUTE7_NAME,
1346 ATTRIBUTE8_NAME,
1347 ATTRIBUTE9_NAME,
1348 ATTRIBUTE10_NAME,
1349 ATTRIBUTE11_NAME,
1350 ATTRIBUTE12_NAME,
1351 ATTRIBUTE13_NAME,
1352 ATTRIBUTE14_NAME,
1353 ATTRIBUTE15_NAME,
1354 ATTRIBUTE1_VALUE,
1355 ATTRIBUTE2_VALUE,
1356 ATTRIBUTE3_VALUE,
1357 ATTRIBUTE4_VALUE,
1358 ATTRIBUTE5_VALUE,
1359 ATTRIBUTE6_VALUE,
1360 ATTRIBUTE7_VALUE,
1361 ATTRIBUTE8_VALUE,
1362 ATTRIBUTE9_VALUE,
1363 ATTRIBUTE10_VALUE,
1364 ATTRIBUTE11_VALUE,
1365 ATTRIBUTE12_VALUE,
1366 ATTRIBUTE13_VALUE,
1367 ATTRIBUTE14_VALUE,
1368 ATTRIBUTE15_VALUE,
1369 CREATION_DATE,
1370 CREATED_BY,
1371 LAST_UPDATED_BY,
1372 LAST_UPDATE_DATE,
1373 LAST_UPDATE_LOGIN,
1374 -- Bug 9583024
1375 HZ_PARTY_ID,
1376 HZ_PARTY_NUMBER ,
1377 PARTY_SITE_NUMBER ,
1378 CONTACT_HZ_PARTY_ID ,
1379 CONTACT_HZ_PARTY_NUMBER
1380 )
1381 VALUES (
1382 l_party_id,
1383 l_request_control_id,
1384 p_line_rec.header_id,
1385 p_line_rec.line_id,
1386 Address_table(J).add_source_orgid,
1387 Address_table(J).add_source_type,
1388 Address_table(J).add_party_name,
1389 Address_table(J).add_alternate_name, --bug 4231894
1390 Address_table(J).add_party_address1,
1391 Address_table(J).add_party_address2,
1392 Address_table(J).add_party_address3,
1393 Address_table(J).add_party_address4,
1394 null,
1395 Address_table(J).add_party_city,
1396 Address_table(J).add_party_state,
1397 Address_table(J).add_party_country,
1398 Address_table(J).add_party_country,
1399 Address_table(J).add_party_postal_code,
1400 Address_table(J).add_party_contact_name,
1401 Address_table(J).add_party_phone,
1402 Address_table(J).add_party_email,
1403 Address_table(J).add_party_fax,
1404 Address_table(J).add_party_url,
1405 null,
1406 null,
1407 null,
1408 null,
1409 null,
1410 null,
1411 null,
1412 null,
1413 null,
1414 null,
1415 null,
1416 null,
1417 null,
1418 null,
1419 null,
1420 null,
1421 null,
1422 null,
1423 null,
1424 null,
1425 null,
1426 null,
1427 null,
1428 null,
1429 null,
1430 null,
1431 null,
1432 null,
1433 null,
1434 null,
1435 sysdate,
1436 FND_GLOBAL.USER_ID,
1437 FND_GLOBAL.USER_ID,
1438 sysdate,
1439 FND_GLOBAL.USER_ID,
1440 -- Bug 9583024
1441 Address_Table(J).add_hz_party_id ,
1442 Address_Table(J).add_hz_party_number ,
1443 Address_Table(J).add_party_site_number ,
1444 Address_Table(J).add_contact_hz_party_id,
1445 Address_Table(J).add_contact_hz_party_number
1446 );
1447
1448 IF l_debug_level > 0 THEN
1449 oe_debug_pub.add( 'INSERTED '||J||' RECORDS IN TO WSH_ITM_PARTIES...' ) ;
1450 END IF;
1451
1452 END IF; -- Check for Org id
1453
1454 END LOOP; -- Loop for inserting records in to Itm Parties
1455
1456 END LOOP; -- Loop for inserting records in to Request Control
1457
1458
1459 IF l_debug_level > 0 THEN
1460 oe_debug_pub.add( 'END CREATING REQUEST...' ) ;
1461 END IF;
1462 x_return_status := FND_API.G_RET_STS_SUCCESS;
1463
1464 EXCEPTION
1465 WHEN FND_API.G_EXC_ERROR THEN
1466 x_return_status := FND_API.G_RET_STS_ERROR;
1467 oe_debug_pub.add( 'END CREATING REQUEST...'||sqlerrm ) ;
1468 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1470 oe_debug_pub.add( 'END CREATING REQUEST...'||sqlerrm ) ;
1471 WHEN OTHERS THEN
1472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1473 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1474 FND_MSG_PUB.Add_Exc_Msg
1475 (G_PKG_NAME,
1476 l_api_name
1477 );
1478 END IF;
1479 oe_debug_pub.add( 'END CREATING REQUEST...'||sqlerrm ) ;
1480
1481 END Create_Request;
1482
1483 /*-----------------------------------------------------+
1484 | Name : WSH_ITM_ONT |
1485 | Parameters : IN p_master_organization_id |
1486 | p_line_rec |
1487 | |
1488 | Description : This is a Call Back Procedure |
1489 | called by Generic Adapter |
1490 | |
1491 +-----------------------------------------------------*/
1492
1493 PROCEDURE WSH_ITM_ONT (
1494 p_request_control_id IN NUMBER default null
1495 ,p_request_set_id IN NUMBER default null
1496 ,p_status_code IN VARCHAR2 default null
1497 ) IS
1498
1499 CURSOR C_Resp_Lines(cp_response_header_id NUMBER) IS
1500 SELECT wl.Error_Text,wl.denied_party_flag,wp.Party_name
1501 FROM wsh_itm_response_lines wl,
1502 wsh_itm_parties wp
1503 WHERE wl.Response_header_id = cp_response_header_id
1504 AND wp.party_id = wl.party_id;
1505
1506 CURSOR C_Get_Responses(cp_request_control_id NUMBER,
1507 cp_request_set_id NUMBER)
1508 IS
1509 SELECT request_control_id,response_header_id,organization_id,
1510 nvl(original_system_line_reference,0) line_id,
1511 nvl(original_system_reference,0) header_id --bug 4503620
1512 FROM wsh_itm_request_control wrc
1513 WHERE request_control_id = nvl(cp_request_control_id,0)
1514 AND wrc.application_id = 660
1515 UNION
1516 SELECT request_control_id,response_header_id,organization_id,
1517 nvl(original_system_line_reference,0) line_id,
1518 nvl(original_system_reference,0) header_id --bug 4503620
1519 FROM wsh_itm_request_control wrc
1520 WHERE request_set_id = nvl(cp_request_set_id,0)
1521 AND wrc.application_id = 660;
1522
1523
1524 l_api_name CONSTANT VARCHAR2(30) := 'Response_API';
1525
1526 l_request_control_id NUMBER;
1527 l_response_header_id NUMBER;
1528 l_services WSH_ITM_RESPONSE_PKG.SrvTabTyp;
1529 l_return_status VARCHAR2(35);
1530 l_error_text VARCHAR2(2000);
1531 -- Commented for bug 8762350
1532 /*
1533 l_denied_party_flag VARCHAR2(1);
1534 l_line_id NUMBER;
1535 l_header_id NUMBER; -- bug 4503620
1536 l_top_model_line_id NUMBER;
1537 l_line_rec OE_ORDER_PUB.line_rec_type;
1538 l_services WSH_ITM_RESPONSE_PKG.SrvTabTyp;
1539 l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
1540 l_return_status VARCHAR2(35);
1541 l_data_error VARCHAR2(1);
1542 l_system_error VARCHAR2(1);
1543 l_activity_complete VARCHAR2(1);
1544 l_hold_applied VARCHAR2(1);
1545 l_dp_hold_flag VARCHAR2(1);
1546 l_gen_hold_flag VARCHAR2(1);
1547 l_interpreted_value VARCHAR2(30);
1548 p_return_status VARCHAR2(30);
1549 l_result_out VARCHAR2(30);
1550 l_msg_count NUMBER := 0;
1551 l_msg_data VARCHAR2(2000);
1552 l_error_text VARCHAR2(2000);
1553 l_dummy VARCHAR2(10);
1554 l_org_id NUMBER;
1555 l_serv INTEGER;
1556 l_activity VARCHAR2(30); -- Bug 7688120*/
1557
1558 l_line_id NUMBER;
1559 l_header_id NUMBER; -- bug 4503620
1560 l_org_id NUMBER;
1561 l_activity VARCHAR2(250);
1562 l_interpreted_value VARCHAR2(30);
1563
1564 --
1565 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1566 --
1567 BEGIN
1568
1569 SAVEPOINT RESPONSE_API;
1570
1571 OE_DEBUG_PUB.Add('Entering WSH_ITM_ONT...');
1572
1573 -- Begin : 8762350
1574 /* This API will now be used only to notify the workflow that ITM request
1575 has been completed. The actual updation of line with the screening
1576 results will be done by the next activity in the workflow (added via
1577 this bug).
1578 All the existing code in this API has been moved to :
1579 oe_export_compliance_wf.update_screening_result(...)
1580 Line will not be progressed from the export_screening_compliance if
1581 it is in system error.
1582 */
1583
1584 Oe_debug_pub.ADD('Req control ID / Req set ID / Status code : ' ||
1585 p_request_control_id || '/' || p_request_set_id ||
1586 '/' || p_status_code);
1587
1588 -- Get the line and header ID
1589
1590 FOR c_responses IN C_Get_Responses(p_request_control_id,p_request_set_id)
1591 LOOP
1592 l_line_id := c_responses.line_id;
1593 l_header_id := c_responses.header_id; --bug 4503620
1594 END LOOP;
1595
1596 -- Get the Org Id
1597
1598 SELECT ORG_ID
1599 INTO l_org_id
1600 FROM oe_order_lines_all
1601 WHERE line_id = l_line_id;
1602
1603 -- MOAC change DBMS_APPLICATION_INFO.Set_Client_Info(l_org_id);
1604 mo_global.set_policy_context('S',l_org_id);
1605
1606 IF p_status_code = 'OVERRIDE' THEN
1607
1608 IF l_debug_level > 0 THEN
1609 oe_debug_pub.add('Overriding export compliance screening.');
1610 END IF;
1611
1612 -- Bug 7688120
1613 SELECT wpa.activity_name -- Bug 10361575
1614 INTO l_activity
1615 FROM wf_item_activity_statuses wias, wf_process_activities wpa
1616 WHERE wias.item_type = 'OEOL'
1617 AND wias.item_key = To_Char(l_line_id)
1618 AND wias.process_activity = wpa.instance_id
1619 AND wias.activity_status = 'NOTIFIED';
1620
1621 IF l_activity in ('EXPORT_COMPLIANCE_SCREENING', 'EXPORT_COMPLIANCE_ELIGIBLE') THEN -- Bug 10361575
1622 WF_ENGINE.CompleteActivityInternalName(
1623 'OEOL',
1624 to_char(l_line_id),
1625 l_activity,
1626 'OVERRIDE' );
1627 ELSE
1628 IF l_debug_level > 0 THEN
1629 oe_debug_pub.add('No action taken, notified activity is '||l_activity||'.'); -- Bug 10361575
1630 END IF;
1631 END IF;
1632
1633 RETURN;
1634
1635 END IF;
1636
1637 -- Get the response and check for system errors
1638
1639 FOR c_responses IN C_Get_Responses(p_request_control_id,p_request_set_id)
1640 LOOP
1641 l_request_control_id := c_responses.request_control_id;
1642 l_response_header_id := c_responses.response_header_id;
1643
1644 WSH_ITM_RESPONSE_PKG.ONT_RESPONSE_ANALYSER (
1645 p_request_control_id => l_request_control_id,
1646 x_interpreted_value => l_interpreted_value,
1647 x_SrvTab => l_services,
1648 x_return_status => l_return_status
1649 );
1650
1651 IF l_debug_level > 0 THEN
1652 OE_DEBUG_PUB.Add('Response analyser return status :' || l_return_status, 1);
1653 OE_DEBUG_PUB.Add('Response Interpreted value :' || l_interpreted_value, 1);
1654 END IF;
1655
1656 -- Do not progress the workflow if the line is in system error.
1657
1658 IF l_interpreted_value = 'SYSTEM' THEN
1659
1660 -- Check for errors in Response Headers.
1661 SELECT error_text
1662 INTO l_error_text
1663 FROM wsh_itm_response_headers
1664 WHERE response_header_id = l_response_header_id;
1665
1666 FND_MESSAGE.SET_NAME('ONT', 'OE_ECS_SYSTEM_ERROR');
1667 FND_MESSAGE.SET_TOKEN('ERRORTEXT', l_error_text);
1668 OE_MSG_PUB.Add;
1669
1670 -- Check for errors in Response lines
1671
1672 FOR c_error IN c_resp_lines(l_response_header_id)
1673 LOOP
1674 BEGIN
1675 l_error_text := c_error.error_text;
1676 FND_MESSAGE.SET_NAME('ONT', 'OE_ECS_SYSTEM_ERROR');
1677 FND_MESSAGE.SET_TOKEN('ERRORTEXT', l_error_text);
1678 OE_MSG_PUB.Add;
1679 END;
1680 END LOOP;
1681
1682 RETURN;
1683
1684 END IF; -- system error
1685
1686 END LOOP;
1687
1688 WF_ENGINE.CompleteActivityInternalName(
1689 'OEOL',
1690 to_char(l_line_id),
1691 'EXPORT_COMPLIANCE_SCREENING',
1692 'COMPLETE' );
1693
1694 OE_DEBUG_PUB.Add('Leaving WSH_ITM_ONT...');
1695
1696 /*
1697
1698 -- This select statement is used to lock the Top Model Line.
1699 -- After the Top Model Line is Locked we go ahead the Lock the
1700 -- individual Line.
1701
1702 SELECT top_model_line_id
1703 INTO l_top_model_line_id
1704 FROM oe_order_lines
1705 WHERE line_id = l_line_id;
1706
1707 BEGIN --bug 4503620
1708 IF l_top_model_line_id IS NOT NULL THEN
1709 SELECT '1'
1710 INTO l_dummy
1711 FROM oe_order_lines_all
1712 WHERE line_id= l_top_model_line_id
1713 FOR UPDATE; --Commented for bug 6415831 --nowait;
1714 END IF;
1715
1716 -- Wait until the lock on the row is released and then
1717 -- lock the row
1718
1719 SELECT '1'
1720 INTO l_dummy
1721 FROM oe_order_lines_all
1722 WHERE line_id= l_line_id
1723 FOR UPDATE; --Commented for bug 6415831 --nowait;
1724 EXCEPTION
1725 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1726 IF l_debug_level > 0 THEN
1727 oe_debug_pub.add('OEXVITMB.pls: unable to lock the line',1);
1728 END IF;
1729 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1730 OE_MSG_PUB.set_msg_context(
1731 p_entity_code => 'LINE'
1732 ,p_entity_id => l_line_id
1733 ,p_header_id => l_header_id
1734 ,p_line_id => l_line_id);
1735
1736 fnd_message.set_name('ONT', 'OE_LINE_LOCKED');
1737 OE_MSG_PUB.Add;
1738 OE_MSG_PUB.Save_API_Messages;
1739 END IF;
1740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1741 END; --bug 4503620 ends
1742
1743 OE_LINE_UTIL.Query_Row ( p_line_id => l_line_id,
1744 x_line_rec => l_line_rec);
1745
1746
1747 -- Check whether the Line is cancelled
1748
1749 IF l_line_rec.cancelled_flag = 'Y' THEN
1750 IF l_debug_level > 0 THEN
1751 oe_debug_pub.add('The line '||to_char(l_line_id)||'is already cancelled.',1);
1752 END IF;
1753 RETURN;
1754 END IF;
1755
1756 OE_MSG_PUB.set_msg_context(
1757 p_entity_code => 'LINE'
1758 ,p_entity_id => l_line_rec.line_id
1759 ,p_header_id => l_line_rec.header_id
1760 ,p_line_id => l_line_rec.line_id
1761 ,p_order_source_id => l_line_rec.order_source_id
1762 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
1763 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
1764 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
1765 ,p_change_sequence => l_line_rec.change_sequence
1766 ,p_source_document_type_id => l_line_rec.source_document_type_id
1767 ,p_source_document_id => l_line_rec.source_document_id
1768 ,p_source_document_line_id => l_line_rec.source_document_line_id
1769 );
1770
1771
1772 -- If the user has choosen to override Screening
1773
1774 IF p_status_code = 'OVERRIDE' THEN
1775
1776 IF l_debug_level > 0 THEN
1777 oe_debug_pub.add('Override screening for line id:'||l_line_id,3);
1778 END IF;
1779
1780 -- Update Work flow Status Code
1781
1782 OE_ORDER_WF_UTIL.Update_Flow_Status_Code (
1783 p_line_id => l_line_id,
1784 p_flow_status_code => 'EXPORT_SCREENING_COMPLETED',
1785 x_return_status => l_return_status
1786 );
1787
1788
1789 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1790 OE_STANDARD_WF.Save_Messages;
1791 OE_STANDARD_WF.Clear_Msg_Context;
1792 APP_EXCEPTION.Raise_Exception;
1793 END IF;
1794
1795 -- Bug 7688120
1796 SELECT wpa.instance_label INTO l_activity
1797 FROM wf_item_activity_statuses wias, wf_process_activities wpa
1798 WHERE wias.item_type = 'OEOL'
1799 AND wias.item_key = To_Char(l_line_id)
1800 AND wias.process_activity = wpa.instance_id
1801 AND wias.activity_status = 'NOTIFIED';
1802
1803 WF_ENGINE.CompleteActivityInternalName(
1804 'OEOL',
1805 to_char(l_line_id),
1806 l_activity, -- Bug 7688120
1807 'OVERRIDE' );
1808
1809
1810 RETURN;
1811 END IF;
1812
1813 -- Calling Response Analyser
1814
1815 FOR c_responses IN C_Get_Responses(p_request_control_id,p_request_set_id)
1816 LOOP
1817 BEGIN
1818
1819 l_request_control_id := c_responses.request_control_id;
1820 l_response_header_id := c_responses.response_header_id;
1821
1822 WSH_ITM_RESPONSE_PKG.ONT_RESPONSE_ANALYSER (
1823 p_request_control_id => l_request_control_id,
1824 x_interpreted_value => l_interpreted_value,
1825 x_SrvTab => l_services,
1826 x_return_status => l_return_status
1827 );
1828
1829
1830
1831 IF l_debug_level > 0 THEN
1832 OE_DEBUG_PUB.Add('Response analyser return status :'|| l_return_status,1);
1833 END IF;
1834
1835 -- Check for System or Data errors.
1836
1837 IF l_interpreted_value = 'SYSTEM' OR l_interpreted_value = 'DATA' THEN
1838
1839 -- Check for errors in Response Headers.
1840
1841 SELECT error_text
1842 INTO l_error_text
1843 FROM wsh_itm_response_headers
1844 WHERE response_header_id = l_response_header_id;
1845
1846 IF l_interpreted_value = 'DATA' THEN
1847 l_data_error := 'Y';
1848 FND_MESSAGE.SET_NAME('ONT','OE_ECS_DATA_ERROR');
1849 ELSE
1850 l_system_error := 'Y';
1851 FND_MESSAGE.SET_NAME('ONT','OE_ECS_SYSTEM_ERROR');
1852 END IF;
1853
1854 FND_MESSAGE.SET_TOKEN('ERRORTEXT',l_error_text);
1855 OE_MSG_PUB.Add;
1856
1857
1858 -- Check for errors in Response lines
1859
1860 FOR c_error IN c_resp_lines(l_response_header_id)
1861 LOOP
1862 BEGIN
1863 l_error_text := c_error.error_text;
1864
1865 IF l_interpreted_value = 'DATA' THEN
1866 FND_MESSAGE.SET_NAME('ONT','OE_ECS_DATA_ERROR');
1867 ELSE
1868 FND_MESSAGE.SET_NAME('ONT','OE_ECS_SYSTEM_ERROR');
1869 END IF;
1870
1871 FND_MESSAGE.SET_TOKEN('ERRORTEXT',l_error_text);
1872 OE_MSG_PUB.Add;
1873 END;
1874 END LOOP;
1875 END IF; --Check for System or Data Errors
1876
1877 -- Get the Parties Denied.
1878
1879 FOR c_resplines IN c_resp_lines(l_response_header_id)
1880 LOOP
1881 BEGIN
1882 IF c_resplines.denied_party_flag = 'Y' THEN
1883 FND_MESSAGE.SET_NAME('ONT','OE_ECS_DENIED_PARTY');
1884 FND_MESSAGE.SET_TOKEN('DENIEDPARTY',
1885 c_resplines.party_name);
1886 OE_MSG_PUB.Add;
1887 IF l_debug_level > 0 THEN
1888 OE_DEBUG_PUB.Add('Party Name:'||c_resplines.party_name||',denied');
1889 END IF;
1890 END IF;
1891 END;
1892 END LOOP;
1893
1894 -- Check for Denied Party Service
1895
1896
1897 FOR l_serv IN 1..l_services.COUNT
1898 LOOP
1899 IF l_debug_level > 0 THEN
1900 OE_DEBUG_PUB.Add('Service Result'||l_services(l_serv).Service_Result,1);
1901 END IF;
1902
1903 IF l_services(l_serv).Service_Type = 'DP' THEN
1904 l_dp_hold_flag := l_services(l_serv).Service_Result;
1905 END IF;
1906 IF l_services(l_serv).Service_Type = 'OM_EXPORT_COMPLIANCE' AND
1907 OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1908 l_gen_hold_flag := l_services(l_serv).Service_Result;
1909 END IF;
1910 END LOOP;
1911
1912 l_hold_applied := l_dp_hold_flag;
1913
1914 IF l_interpreted_value = 'SUCCESS' THEN
1915 l_activity_complete := 'Y';
1916 END IF;
1917 END;
1918 END LOOP;
1919
1920 -- Progress Work Flow to Next Stage
1921
1922 IF l_debug_level > 0 THEN
1923 oe_debug_pub.add('Progress Work Flow to Next Stage...',1);
1924 END IF;
1925
1926
1927 -- If one response has system error and other has data error we
1928 -- consider that line has system error. If both the responses has
1929 -- data error we consider thata line has data error.
1930
1931 IF l_system_error = 'Y' THEN
1932 NULL;
1933
1934 ELSIF l_data_error='Y' THEN
1935
1936 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
1937 (p_line_id => l_line_id,
1938 p_flow_status_code => 'EXPORT_SCREENING_DATA_ERROR',
1939 x_return_status => l_return_status
1940 );
1941
1942 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1943 OE_STANDARD_WF.Save_Messages;
1944 OE_STANDARD_WF.Clear_Msg_Context;
1945 APP_EXCEPTION.Raise_Exception;
1946 END IF;
1947
1948 WF_ENGINE.CompleteActivityInternalName('OEOL',
1949 to_char(l_line_id),
1950 'EXPORT_COMPLIANCE_SCREENING',
1951 'INCOMPLETE');
1952
1953 ELSIF l_gen_hold_flag = 'Y' AND
1954 OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1955
1956 OE_DEBUG_PUB.Add('Generic Hold!!!');
1957
1958 -- The Hold_Id of the Generic Hold has been
1959 -- seeded as 23.
1960
1961
1962 l_hold_source_rec.hold_entity_code := 'O';
1963 l_hold_source_rec.hold_id := 23;
1964 l_hold_source_rec.hold_entity_id := l_line_rec.header_id;
1965 l_hold_source_rec.line_id := l_line_rec.line_id;
1966
1967 OE_HOLDS_PUB.Apply_Holds
1968 ( p_api_version => 1.0
1969 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1970 , p_hold_source_rec => l_hold_source_rec
1971 , x_return_status => l_return_status
1972 , x_msg_count => l_msg_count
1973 , x_msg_data => l_msg_data
1974 );
1975
1976 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1977 RAISE FND_API.G_EXC_ERROR;
1978 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1980 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1981 IF l_debug_level > 0 THEN
1982 oe_debug_pub.add('Applied Generic hold on line:'|| l_line_rec.line_id,1);
1983 END IF;
1984 END IF;
1985
1986 OE_Order_WF_Util.Update_Flow_Status_Code
1987 (p_line_id => l_line_id,
1988 p_flow_status_code => 'EXPORT_SCREENING_COMPLETED',
1989 x_return_status => l_return_status
1990 );
1991
1992 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1993 OE_STANDARD_WF.Save_Messages;
1994 OE_STANDARD_WF.Clear_Msg_Context;
1995 APP_EXCEPTION.Raise_Exception;
1996 END IF;
1997
1998 WF_ENGINE.CompleteActivityInternalName('OEOL',
1999 to_char(l_line_id),
2000 'EXPORT_COMPLIANCE_SCREENING',
2001 'HOLD_APPLIED');
2002
2003 ELSIF l_hold_applied = 'Y' THEN
2004
2005 -- Check whether Denied party hold needs to be applied
2006
2007 -- The Hold_Id of the Denied Party Hold has been
2008 -- seeded as 21.
2009
2010
2011 l_hold_source_rec.hold_entity_code := 'O';
2012 l_hold_source_rec.hold_id := 21;
2013 l_hold_source_rec.hold_entity_id := l_line_rec.header_id;
2014 l_hold_source_rec.line_id := l_line_rec.line_id;
2015
2016 OE_HOLDS_PUB.Apply_Holds
2017 ( p_api_version => 1.0
2018 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
2019 , p_hold_source_rec => l_hold_source_rec
2020 , x_return_status => l_return_status
2021 , x_msg_count => l_msg_count
2022 , x_msg_data => l_msg_data
2023 );
2024
2025 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2026 RAISE FND_API.G_EXC_ERROR;
2027 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2028 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2029 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2030 IF l_debug_level > 0 THEN
2031 oe_debug_pub.add('Applied denied party hold on line:'|| l_line_rec.line_id,1);
2032 END IF;
2033 END IF;
2034
2035 OE_Order_WF_Util.Update_Flow_Status_Code
2036 (p_line_id => l_line_id,
2037 p_flow_status_code => 'EXPORT_SCREENING_COMPLETED',
2038 x_return_status => l_return_status
2039 );
2040
2041 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2042 OE_STANDARD_WF.Save_Messages;
2043 OE_STANDARD_WF.Clear_Msg_Context;
2044 APP_EXCEPTION.Raise_Exception;
2045 END IF;
2046
2047 WF_ENGINE.CompleteActivityInternalName('OEOL',
2048 to_char(l_line_id),
2049 'EXPORT_COMPLIANCE_SCREENING',
2050 'HOLD_APPLIED');
2051
2052 ELSIF l_activity_complete ='Y' THEN
2053
2054 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
2055 (p_line_id => l_line_id,
2056 p_flow_status_code => 'EXPORT_SCREENING_COMPLETED',
2057 x_return_status => l_return_status
2058 );
2059
2060 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2061 OE_STANDARD_WF.Save_Messages;
2062 OE_STANDARD_WF.Clear_Msg_Context;
2063 APP_EXCEPTION.Raise_Exception;
2064 END IF;
2065
2066 WF_ENGINE.CompleteActivityInternalName('OEOL',
2067 to_char(l_line_id),
2068 'EXPORT_COMPLIANCE_SCREENING',
2069 'COMPLETE');
2070
2071 END IF;
2072
2073
2074 OE_MSG_PUB.SAVE_MESSAGES(l_line_rec.line_id);
2075
2076 IF l_debug_level > 0 THEN
2077 oe_debug_pub.add('Exiting response api',1);
2078 END IF; */
2079
2080 -- End : 8762350
2081
2082 EXCEPTION
2083 WHEN OTHERS THEN
2084 ROLLBACK TO RESPONSE_API;
2085 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2086 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2087 FND_MSG_PUB.Add_Exc_Msg
2088 (G_PKG_NAME,
2089 l_api_name
2090 );
2091 END IF;
2092
2093 END WSH_ITM_ONT;
2094
2095 END ONT_ITM_PKG;