eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ONT_ITM_PKG

Source


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