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.EC_APPLICATION_ADVICE_PUB

Source


1 PACKAGE BODY EC_Application_Advice_PUB AS
2 -- $Header: ECPADVOB.pls 120.2 2005/09/29 11:30:37 arsriniv ship $
3 
4 PROCEDURE create_advice(
5    p_api_version_number		IN	NUMBER,
6    p_init_msg_list		IN	VARCHAR2 := FND_API.G_FALSE,
7    p_simulate			IN	VARCHAR2 := FND_API.G_FALSE,
8    p_commit			IN	VARCHAR2 := FND_API.G_FALSE,
9    p_validation_level		IN	NUMBER   := FND_API.G_VALID_LEVEL_FULL,
10    p_return_status		OUT NOCOPY	VARCHAR2,
11    p_msg_count			OUT NOCOPY	NUMBER,
12    p_msg_data			OUT NOCOPY	VARCHAR2,
13    p_communication_method	IN	VARCHAR2,
14    p_related_document_id	IN	VARCHAR2,
15    p_tp_header_id		IN	NUMBER,
16    p_tp_location_code		IN	VARCHAR2,
17    p_document_type		IN	VARCHAR2,
18    p_document_code		IN	VARCHAR2,
19    p_transaction_control1	IN	VARCHAR2 default NULL,
20    p_transaction_control2	IN	VARCHAR2 default NULL,
21    p_transaction_control3	IN	VARCHAR2 default NULL,
22    p_entity_code		IN	VARCHAR2 default NULL,
23    p_entity_name		IN	VARCHAR2 default NULL,
24    p_entity_address1		IN	VARCHAR2 default NULL,
25    p_entity_address2		IN	VARCHAR2 default NULL,
26    p_entity_address3		IN	VARCHAR2 default NULL,
27    p_entity_address4		IN	VARCHAR2 default NULL,
28    p_entity_city		IN	VARCHAR2 default NULL,
29    p_entity_postal_code		IN	VARCHAR2 default NULL,
30    p_entity_country		IN	VARCHAR2 default NULL,
31    p_entity_state		IN	VARCHAR2 default NULL,
32    p_entity_province		IN	VARCHAR2 default NULL,
33    p_entity_county		IN	VARCHAR2 default NULL,
34    p_external_reference_1	IN	VARCHAR2 default NULL,
35    p_external_reference_2	IN	VARCHAR2 default NULL,
36    p_external_reference_3	IN	VARCHAR2 default NULL,
37    p_external_reference_4	IN	VARCHAR2 default NULL,
38    p_external_reference_5	IN	VARCHAR2 default NULL,
39    p_external_reference_6	IN	VARCHAR2 default NULL,
40    p_internal_reference_1	IN	VARCHAR2 default NULL,
41    p_internal_reference_2	IN	VARCHAR2 default NULL,
42    p_internal_reference_3	IN	VARCHAR2 default NULL,
43    p_internal_reference_4	IN	VARCHAR2 default NULL,
44    p_internal_reference_5	IN	VARCHAR2 default NULL,
45    p_internal_reference_6	IN	VARCHAR2 default NULL,
46    p_advice_header_id		OUT NOCOPY	NUMBER
47 )
48 IS
49    l_api_name		CONSTANT VARCHAR2(30) := 'Create_Advice';
50    l_api_version_number	CONSTANT NUMBER	      := 1.0;
51    l_return_status		 VARCHAR2(10);
52    l_tp_exists                   VARCHAR2(1):='N';		-- Bug 2593437
53 
54 BEGIN
55 
56    p_advice_header_id := -99;		-- Bug 2593437
57 
58    begin
59      select 'Y'
60      into l_tp_exists
61      from ece_tp_details
62      where document_id='ADVO'
63      and tp_header_id= p_tp_header_id;
64    exception
65     when others then
66         null;
67    end;
68 
69   if l_tp_exists = 'Y' then                             -- Bug 2593437
70 
71    -- Standard Start of API savepoint
72 
73    SAVEPOINT Create_Advice_PVT;
74 
75    -- Standard call to check for call compatibility.
76 
77    if NOT FND_API.Compatible_API_Call
78    (
79 	l_api_version_number,
80 	p_api_version_number,
81 	l_api_name,
82 	G_PKG_NAME
83    )
84    then
85       raise FND_API.G_EXC_UNEXPECTED_ERROR;
86    end if;
87 
88    -- Initialize message list if p_init_msg_list is set to TRUE.
89 
90    if FND_API.to_Boolean( p_init_msg_list)
91    then
92       FND_MSG_PUB.initialize;
93    end if;
94 
95    -- Initialize API return status to success
96 
97    p_return_status := FND_API.G_RET_STS_SUCCESS;
98 
99 
100    select ece_advo_headers_s.nextval
101      into p_advice_header_id
102      from dual;
103 
104 /*
105    EC_Trading_Partner_PVT.Get_TP_Location_Code(
106 		p_api_version_number	=> 1.0,
107 		p_return_status		=> l_return_status,
108 		p_msg_count		=> l_msg_count,
109 		p_msg_data		=> l_msg_data,
110 		p_entity_address_id	=> p_entity_address_id,
111 		p_info_type		=> p_entity_type,
112 		p_location_code_ext	=> l_location_code_ext,
113 		p_reference_ext1	=> l_reference_ext1,
114 		p_reference_ext2	=> l_reference_ext2);
115 
116    if l_location_code_ext is NULL
117    then
118       fnd_message.set_name('EC','EC_TP_NOT_DEFINED');
119       FND_MSG_PUB.Add;
120       l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
121 --      c_error_msg := fnd_message.get;
122    else
123 
124 */
125 
126 
127 
128       insert into ECE_ADVO_HEADERS (
129 		communication_method,
130 		ADVICE_HEADER_ID,
131 		DOCUMENT_TYPE,
132 		DOCUMENT_CODE,
133 		TP_HEADER_ID,
134 		TP_LOCATION_CODE_EXT,
135 		TP_CODE,
136 		TP_NAME,
137 		TP_ADDRESS1,
138 		TP_ADDRESS2,
139 		TP_ADDRESS3,
140 		TP_ADDRESS4,
141 		TP_CITY,
142 		TP_POSTAL_CODE,
143 		TP_COUNTRY,
144 		TP_STATE,
145 		TP_PROVINCE,
146 		TP_COUNTY,
147 		RELATED_DOCUMENT_ID,
148 		EXTERNAL_REFERENCE1,
149 		EXTERNAL_REFERENCE2,
150 		EXTERNAL_REFERENCE3,
151 		EXTERNAL_REFERENCE4,
152 		EXTERNAL_REFERENCE5,
153 		EXTERNAL_REFERENCE6,
154 		INTERNAL_REFERENCE1,
155 		INTERNAL_REFERENCE2,
156 		INTERNAL_REFERENCE3,
157 		INTERNAL_REFERENCE4,
158 		INTERNAL_REFERENCE5,
159 		INTERNAL_REFERENCE6,
160 		TRANSACTION_CONTROL1,
161 		TRANSACTION_CONTROL2,
162 		TRANSACTION_CONTROL3)
163 	values (
164 		p_communication_method,
165 		p_advice_header_id,
166 		p_document_type,
167 		p_document_code,
168 		p_tp_header_id,
169 		p_tp_location_code,
170 		p_entity_code,
171 		p_entity_name,
172 		p_entity_address1,
173 		p_entity_address2,
174 		p_entity_address3,
175 		p_entity_address4,
176 		p_entity_city,
177 		p_entity_postal_code,
178 		p_entity_country,
179 		p_entity_state,
180 		p_entity_province,
181 		p_entity_county,
182 		p_related_document_id,
183 		p_external_reference_1,
184 		p_external_reference_2,
185 		p_external_reference_3,
186 		p_external_reference_4,
187 		p_external_reference_5,
188 		p_external_reference_6,
189 		p_internal_reference_1,
190 		p_internal_reference_2,
191 		p_internal_reference_3,
192 		p_internal_reference_4,
193 		p_internal_reference_5,
194 		p_internal_reference_6,
195 		p_transaction_control1,
196 		p_transaction_control2,
197 		p_transaction_control3);
198 
199 
200 
201    -- Both G_EXC_ERROR and G_EXC_UNEXPECTED_ERROR are handled in
202    -- the API exception handler.
203 
204    if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
205    then
206 
207    -- Unexpected error, abort processing.
208 
209       raise FND_API.G_EXC_UNEXPECTED_ERROR;
210 
211    elsif l_return_status = FND_API.G_RET_STS_ERROR THEN
212 
213    -- Error, abort processing
214 
215       raise FND_API.G_EXC_ERROR;
216 
217    end if;
218 
219    -- Standard check of p_simulate and p_commit parameters
220 
221    if FND_API.To_Boolean( p_simulate)
222    then
223       ROLLBACK to Create_Advice_PVT;
224 
225    elsif FND_API.To_Boolean( p_commit)
226    then
227       commit work;
228    end if;
229 
230    -- Standard call to get message count and if count is 1, get message info.
231 
232    FND_MSG_PUB.Count_And_Get
233    (
234       p_count		=> p_msg_count,
235       p_data		=> p_msg_data
236    );
237 
238   end if; 		-- Bug 2593437
239 
240 
241 EXCEPTION
242 
243    WHEN FND_API.G_EXC_ERROR THEN
244 
245       Rollback to Create_Advice_PVT;
246       p_return_status := FND_API.G_RET_STS_ERROR;
247 
248       FND_MSG_PUB.Count_And_Get
249       (
250          p_count		=> p_msg_count,
251          p_data			=> p_msg_data
252       );
253 
254    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
255 
256       Rollback to Create_Advice_PVT;
257       p_return_status := FND_API.G_RET_STS_ERROR;
258 
259       FND_MSG_PUB.Count_And_Get
260       (
261          p_count		=> p_msg_count,
262          p_data			=> p_msg_data
263       );
264 
265    WHEN OTHERS THEN
266 
267       Rollback to Create_Advice_PVT;
268       p_return_status := FND_API.G_RET_STS_ERROR;
269 
270       if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
271       then
272          FND_MSG_PUB.Add_Exc_Msg
273          (
274             G_FILE_NAME,
275             G_PKG_NAME,
276             l_api_name
277          );
278       end if;
279 
280       FND_MSG_PUB.Count_And_Get
281       (
282          p_count		=> p_msg_count,
283          p_data			=> p_msg_data
284       );
285 
286 end Create_Advice;
287 
288 
289 
290 
291 
292 PROCEDURE create_advice_line(
293    p_api_version_number		IN	NUMBER,
294    p_init_msg_list		IN	VARCHAR2 := FND_API.G_FALSE,
295    p_simulate			IN	VARCHAR2 := FND_API.G_FALSE,
296    p_commit			IN	VARCHAR2 := FND_API.G_FALSE,
297    p_validation_level		IN	NUMBER   := FND_API.G_VALID_LEVEL_FULL,
298    p_return_status		OUT NOCOPY	VARCHAR2,
299    p_msg_count			OUT NOCOPY	NUMBER,
300    p_msg_data			OUT NOCOPY	VARCHAR2,
301    p_advice_header_id		IN	NUMBER,
302    p_advice_date_time		IN	DATE,
303    p_advice_status_code		IN	VARCHAR2,
304    p_external_reference_1	IN	VARCHAR2 default NULL,
305    p_external_reference_2	IN	VARCHAR2 default NULL,
306    p_external_reference_3	IN	VARCHAR2 default NULL,
307    p_external_reference_4	IN	VARCHAR2 default NULL,
308    p_external_reference_5	IN	VARCHAR2 default NULL,
309    p_external_reference_6	IN	VARCHAR2 default NULL,
310    p_internal_reference_1	IN	VARCHAR2 default NULL,
311    p_internal_reference_2	IN	VARCHAR2 default NULL,
312    p_internal_reference_3	IN	VARCHAR2 default NULL,
313    p_internal_reference_4	IN	VARCHAR2 default NULL,
314    p_internal_reference_5	IN	VARCHAR2 default NULL,
315    p_internal_reference_6	IN	VARCHAR2 default NULL,
316    p_advo_message_code		IN	VARCHAR2 default NULL,
317    p_advo_message_desc		IN	VARCHAR2 default NULL,
318    p_advo_data_bad		IN	VARCHAR2 default NULL,
319    p_advo_data_good		IN	VARCHAR2 default NULL
320 )
321 IS
322    l_api_name		CONSTANT VARCHAR2(30) := 'Create_Advice_Line';
323    l_api_version_number	CONSTANT NUMBER	      := 1.0;
324    l_return_status		 VARCHAR2(10);
325    l_document_exists             VARCHAR2(1):='N';	-- Bug 2593437
326 
327 BEGIN
328 
329    begin			-- Bug 2593437
330      select 'Y'
331      into l_document_exists
332      from ece_advo_headers
333      where advice_header_id=p_advice_header_id;
334    exception
335     when others then
336         null;
337    end;
338 
339   if l_document_exists = 'Y' then             -- Bug 2593437
340 
341 
342    -- Standard Start of API savepoint
343 
344    SAVEPOINT Create_Advice_Line_PVT;
345 
346    -- Standard call to check for call compatibility.
347 
348    if NOT FND_API.Compatible_API_Call
349    (
350 	l_api_version_number,
351 	p_api_version_number,
352 	l_api_name,
353 	G_PKG_NAME
354    )
355    then
356       raise FND_API.G_EXC_UNEXPECTED_ERROR;
357    end if;
358 
359    -- Initialize message list if p_init_msg_list is set to TRUE.
360 
361    if FND_API.to_Boolean( p_init_msg_list)
362    then
363       FND_MSG_PUB.initialize;
364    end if;
365 
366    -- Initialize API return status to success
367 
368    p_return_status := FND_API.G_RET_STS_SUCCESS;
369 
370 
371 	insert into ECE_ADVO_DETAILS(
372 		ADVICE_DETAIL_ID   ,
373 		ADVICE_HEADER_ID   ,
374 		ADVO_DATE_TIME	   ,
375 		ADVO_STATUS_CODE   ,
376 		EXTERNAL_REFERENCE1,
377 		EXTERNAL_REFERENCE2,
378 		EXTERNAL_REFERENCE3,
379 		EXTERNAL_REFERENCE4,
380 		EXTERNAL_REFERENCE5,
381 		EXTERNAL_REFERENCE6,
382 		INTERNAL_REFERENCE1,
383 		INTERNAL_REFERENCE2,
384 		INTERNAL_REFERENCE3,
385 		INTERNAL_REFERENCE4,
386 		INTERNAL_REFERENCE5,
387 		INTERNAL_REFERENCE6,
388 		ADVO_MESSAGE_CODE  ,
389 		ADVO_MESSAGE_DESC  ,
390 		ADVO_DATA_BAD	   ,
391 		ADVO_DATA_GOOD)
392 	values(
393 		ece_advo_details_s.nextval,
394 		p_advice_header_id	 ,
395 		p_advice_date_time	 ,
396 		p_advice_status_code	 ,
397 		p_external_reference_1,
398 		p_external_reference_2,
399 		p_external_reference_3,
400 		p_external_reference_4,
401 		p_external_reference_5,
402 		p_external_reference_6,
403 		p_internal_reference_1,
404 		p_internal_reference_2,
405 		p_internal_reference_3,
406 		p_internal_reference_4,
407 		p_internal_reference_5,
408 		p_internal_reference_6,
409 		p_advo_message_code	 ,
410 		p_advo_message_desc	 ,
411 		p_advo_data_bad	 ,
412 		p_advo_data_good);
413 
414 
415 
416    -- Both G_EXC_ERROR and G_EXC_UNEXPECTED_ERROR are handled in
417    -- the API exception handler.
418 
419    if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
420    then
421 
422    -- Unexpected error, abort processing.
423 
424       raise FND_API.G_EXC_UNEXPECTED_ERROR;
425 
426    elsif l_return_status = FND_API.G_RET_STS_ERROR THEN
427 
428    -- Error, abort processing
429 
430       raise FND_API.G_EXC_ERROR;
431 
432    end if;
433 
434    -- Standard check of p_simulate and p_commit parameters
435 
436    if FND_API.To_Boolean( p_simulate)
437    then
438       ROLLBACK to Create_Advice_Line_PVT;
439 
440    elsif FND_API.To_Boolean( p_commit)
441    then
442       commit work;
443    end if;
444 
445    -- Standard call to get message count and if count is 1, get message info.
446 
447    FND_MSG_PUB.Count_And_Get
448    (
449       p_count		=> p_msg_count,
450       p_data		=> p_msg_data
451    );
452 
453   end if;		-- Bug 2593437
454 
455 EXCEPTION
456 
457    WHEN FND_API.G_EXC_ERROR THEN
458 
459       Rollback to Create_Advice_Line_PVT;
460       p_return_status := FND_API.G_RET_STS_ERROR;
461 
462       FND_MSG_PUB.Count_And_Get
463       (
464          p_count		=> p_msg_count,
465          p_data			=> p_msg_data
466       );
467 
468    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
469 
470       Rollback to Create_Advice_Line_PVT;
471       p_return_status := FND_API.G_RET_STS_ERROR;
472 
473       FND_MSG_PUB.Count_And_Get
474       (
475          p_count		=> p_msg_count,
476          p_data			=> p_msg_data
477       );
478 
479    WHEN OTHERS THEN
480 
481       Rollback to Create_Advice_Line_PVT;
482       p_return_status := FND_API.G_RET_STS_ERROR;
483 
484       if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
485       then
486          FND_MSG_PUB.Add_Exc_Msg
487          (
488             G_FILE_NAME,
489             G_PKG_NAME,
490             l_api_name
491          );
492       end if;
493 
494       FND_MSG_PUB.Count_And_Get
495       (
496          p_count		=> p_msg_count,
497          p_data			=> p_msg_data
498       );
499 
500 end Create_Advice_Line;
501 
502 
503 
504 END;
505