[Home] [Help]
PACKAGE BODY: APPS.IEC_AORECDNU_PUB
Source
1 PACKAGE BODY IEC_AORECDNU_PUB AS
2 /* $Header: IECRDPBB.pls 115.2 2004/05/18 19:56:39 minwang noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEC_AORECDNU_PUB';
5
6
7 PROCEDURE SetAORecDNU
8 (
9 p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2,
11 p_commit IN VARCHAR2,
12 p_user_id IN NUMBER,
13 p_login_id IN NUMBER DEFAULT NULL,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2,
17 p_list_entry_id IN NUMBER,
18 p_list_header_id IN NUMBER,
19 p_dnu_reason_code IN NUMBER DEFAULT NULL
20 ) AS
21 l_api_name CONSTANT VARCHAR2(30) := 'SetAORecDNU';
22 l_api_version CONSTANT NUMBER := 1.0;
23 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
24 l_return_status VARCHAR2(1);
25 l_data VARCHAR2(100);
26 l_count NUMBER;
27 l_list_entry_id NUMBER(15);
28 l_list_header_id NUMBER(15);
29 l_dnu_reason_code NUMBER(15);
30 l_returns_id NUMBER(15);
31 l_itm_cc_tz_id NUMBER;
32
33 BEGIN
34
35 SAVEPOINT set_record_donotuse_pub;
36
37 -- Preprocessing Call
38 l_list_entry_id := p_list_entry_id;
39 l_list_header_id := p_list_header_id;
40 IF(p_dnu_reason_code is not null) THEN
41 l_dnu_reason_code := p_dnu_reason_code;
42 ELSE
43 l_dnu_reason_code := 10;
44 END IF;
45
46 IEC_AORECDNU_PUB_VUHK.SetAORecDNU_pre(p_list_entry_id => l_list_entry_id
47 ,p_list_header_id => l_list_header_id
48 , p_dnu_reason_code => l_dnu_reason_code
49 , x_data => l_data
50 , x_count => l_count
51 , x_return_code => l_return_status);
52
53 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
54 RAISE FND_API.G_EXC_ERROR;
55 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57 END IF;
58
59 -- Standard call to check for call compatibility
60 IF NOT fnd_api.compatible_api_call( l_api_version
61 , p_api_version
62 , l_api_name
63 , g_pkg_name) THEN
64 RAISE fnd_api.g_exc_unexpected_error;
65 END IF;
66
67 -- Initialize message list if p_init_msg_list is set to TRUE
68 IF fnd_api.to_boolean(p_init_msg_list) THEN
69 fnd_msg_pub.initialize;
70 END IF;
71
72 -- Initialize API return status to success
73 x_return_status := fnd_api.g_ret_sts_success;
74
75 --
76 -- Validate user and login session IDs
77 --
78 IF (p_user_id IS NULL) THEN
79 iec_rlctrl_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
80 RAISE fnd_api.g_exc_error;
81 ELSE
82 iec_rlctrl_util_pvt.validate_who_info ( p_api_name => l_api_name_full
83 , p_parameter_name_usr => 'p_user_id'
84 , p_parameter_name_log => 'p_login_id'
85 , p_user_id => p_user_id
86 , p_login_id => p_login_id
87 , x_return_status => l_return_status);
88 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
89 RAISE fnd_api.g_exc_error;
90 END IF;
91 END IF;
92
93 ---------------------------------------------------------------------
94
95 -- DO ACTUAL WORK HERE
96 BEGIN
97 select returns_id,itm_cc_tz_id into l_returns_id, l_itm_cc_tz_id from iec_g_return_entries
98 where list_entry_id = p_list_entry_id and list_header_id = p_list_header_id;
99
100 update iec_g_return_entries set do_not_use_flag = 'Y',
101 do_not_use_reason = l_dnu_reason_code,
102 last_updated_by = p_user_id,
103 last_update_date = sysdate
104 where returns_id = l_returns_id;
105
106 update iec_g_mktg_item_cc_tzs set record_count = nvl(RECORD_COUNT,0)- 1,
107 last_update_date = sysdate
108 where itm_cc_tz_id = l_itm_cc_tz_id;
109
110 l_return_status := FND_API.G_RET_STS_SUCCESS;
111
112 EXCEPTION
113 WHEN NO_DATA_FOUND THEN
114 l_return_status:= FND_API.G_RET_STS_ERROR;
115 WHEN OTHERS THEN
116 l_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
117 END;
118
119 IF (l_return_status= FND_API.G_RET_STS_ERROR) THEN
120 RAISE FND_API.G_EXC_ERROR;
121 ELSIF (l_return_status= FND_API.G_RET_STS_UNEXP_ERROR) THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124
125 ---------------------------------------------------------------------
126
127 -- Post processing Call
128
129 IEC_AORECDNU_PUB_VUHK.SetAORecDNU_post(p_list_entry_id => l_list_entry_id
130 ,p_list_header_id => l_list_header_id
131 , p_dnu_reason_code => l_dnu_reason_code
132 , x_data => l_data
133 , x_count => l_count
134 , x_return_code => l_return_status);
135
136
137 IF (l_return_status= FND_API.G_RET_STS_ERROR) THEN
138 RAISE FND_API.G_EXC_ERROR;
139 ELSIF (l_return_status= FND_API.G_RET_STS_UNEXP_ERROR) THEN
140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
141 END IF;
142
143 IF fnd_api.to_boolean(p_commit) THEN
144 COMMIT WORK;
145 END IF;
146
147 -- Standard call to get message count and if count is 1, get message info
148 fnd_msg_pub.count_and_get( p_count => x_msg_count
149 , p_data => x_msg_data );
150
151 EXCEPTION
152 WHEN fnd_api.g_exc_error THEN
153 ROLLBACK TO set_record_donotuse_pub;
154 x_return_status := fnd_api.g_ret_sts_error;
155 fnd_msg_pub.count_and_get( p_count => x_msg_count
156 , p_data => x_msg_data );
157 WHEN fnd_api.g_exc_unexpected_error THEN
158 ROLLBACK TO set_record_donotuse_pub;
159 x_return_status := fnd_api.g_ret_sts_unexp_error;
160 fnd_msg_pub.count_and_get( p_count => x_msg_count
161 , p_data => x_msg_data );
162 WHEN OTHERS THEN
163 ROLLBACK TO set_record_donotuse_pub;
164 x_return_status := fnd_api.g_ret_sts_unexp_error;
165 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
166 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
167 END IF;
168 fnd_msg_pub.count_and_get( p_count => x_msg_count
169 , p_data => x_msg_data );
170
171 END SetAORecDNU;
172
173 END IEC_AORECDNU_PUB;