[Home] [Help]
PACKAGE BODY: APPS.ASO_ORDER_FEEDBACK_PUB
Source
1 PACKAGE BODY ASO_ORDER_FEEDBACK_PUB AS
2 /* $Header: asopomfb.pls 120.2 2010/05/05 12:43:48 rassharm ship $ */
3
4
5 -- ---------------------------------------------------------
6 -- Define global variables
7 -- ---------------------------------------------------------
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ASO_ORDER_FEEDBACK_PUB';
9 G_USER CONSTANT VARCHAR2(30) := FND_GLOBAL.USER_ID;
10
11
12 -- ---------------------------------------------------------
13 -- Define Procedures
14 -- ---------------------------------------------------------
15
16 --------------------------------------------------------------------------
17
18 -- Start of comments
19 -- API name : UPDATE_NOTICE
20 -- Type : Public
21 -- Function : This API is the PUBLIC API that is invoked by Order Manager
22 -- to communicate any changes (inserts/updates/deletes) to the
23 -- Order Entities to Oracle Order Capture application.
24 -- Pre-reqs : None.
25 --
26 -- Standard IN Parameters:
27 -- p_api_version IN NUMBER Required
28 -- p_init_msg_list IN VARCHAR2 Optional
29 -- Default = FND_API.G_FALSE
30 -- p_commit IN VARCHAR2 Optional
31 -- Default = FND_API.G_FALSE
32 --
33 -- Standard OUT NOCOPY /* file.sql.39 change */ Parameters:
34 -- x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2(1)
35 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
36 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2(2000)
37 --
38 -- UPDATE_NOTICE API specific IN Parameters:
39 --
40 -- ALL PARAMETERS ARE OPTIONAL
41 --
42 -- p_header_rec IN OE_Order_PUB.Header_Rec_Type OPTIONAL
43 -- Default := OE_Order_PUB.G_MISS_HEADER_REC
44 -- p_old_header_rec IN OE_Order_PUB.Header_Rec_Type
45 -- Default := OE_ORDER_PUB.G_MISS_HEADER_REC
46 -- p_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type
47 -- Default := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL
48 -- p_old_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type
49 -- Default := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL
50 -- p_Header_price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type
51 -- Default := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL
52 -- p_old_Header_Price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type
53 -- Default := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL
54 -- p_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type
55 -- Default := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL
56 -- p_old_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type
57 -- Default := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL
58 -- p_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
59 -- Default := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL
60 -- p_old_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
61 -- Default := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL
62 -- p_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type
63 -- Default := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL
64 -- p_old_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type
65 -- Default := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL
66 -- p_line_tbl IN OE_Order_PUB.Line_Tbl_Type
67 -- Default := OE_ORDER_PUB.G_MISS_LINE_TBL
68 -- p_old_line_tbl IN OE_Order_PUB.Line_Tbl_Type
69 -- Default := OE_ORDER_PUB.G_MISS_LINE_TBL
70 -- p_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type
71 -- Default := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL
72 -- p_old_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type
73 -- Default := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL
74 -- p_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type
75 -- Default := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL
76 -- p_old_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type
77 -- Default := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL
78 -- p_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type
79 -- Default := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL
80 -- p_old_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type
81 -- Default := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL
82 -- p_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
83 -- Default := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL
84 -- p_old_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
85 -- Default := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL
86 -- p_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type
87 -- Default := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL
88 -- p_old_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type
89 -- Default := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL
90 -- p_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type
91 -- Default := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL
92 -- p_old_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type
93 -- Default := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL
94 -- p_action_request_tbl IN OE_Order_PUB.Request_Tbl_Type
95 -- Default := OE_ORDER_PUB.G_MISS_REQUEST_TBL
96 --
97 --
98 -- UPDATE_NOTICE API specific OUT NOCOPY /* file.sql.39 change */ Parameters:
99 -- none
100 --
101 -- Version : Current version 1.0
102 -- Initial version 1.0
103 --
104 -- End of comments
105 --------------------------------------------------------------------------
106
107 PROCEDURE UPDATE_NOTICE
108 (
109 p_api_version IN NUMBER,
110 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
111 p_commit IN VARCHAR2 := FND_API.G_FALSE,
112 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
113 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
114 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
115 p_header_rec IN OE_Order_PUB.Header_Rec_Type :=
116 OE_Order_PUB.G_MISS_HEADER_REC,
117 p_old_header_rec IN OE_Order_PUB.Header_Rec_Type :=
118 OE_Order_PUB.G_MISS_HEADER_REC,
119 p_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type :=
120 OE_Order_PUB.G_MISS_HEADER_ADJ_TBL,
121 p_old_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type :=
122 OE_Order_PUB.G_MISS_HEADER_ADJ_TBL,
123 p_Header_price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type :=
124 OE_Order_PUB.G_MISS_HEADER_PRICE_ATT_TBL,
125 p_old_Header_Price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type :=
126 OE_Order_PUB.G_MISS_HEADER_PRICE_ATT_TBL,
127 p_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type :=
128 OE_Order_PUB.G_MISS_HEADER_ADJ_ATT_TBL,
129 p_old_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type :=
130 OE_Order_PUB.G_MISS_HEADER_ADJ_ATT_TBL,
131 p_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type :=
132 OE_Order_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL,
133 p_old_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type :=
134 OE_Order_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL,
135 p_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type :=
136 OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL,
137 p_old_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type :=
138 OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL,
139 p_line_tbl IN OE_Order_PUB.Line_Tbl_Type :=
140 OE_Order_PUB.G_MISS_LINE_TBL,
141 p_old_line_tbl IN OE_Order_PUB.Line_Tbl_Type :=
142 OE_Order_PUB.G_MISS_LINE_TBL,
143 p_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type :=
144 OE_Order_PUB.G_MISS_LINE_ADJ_TBL,
145 p_old_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type :=
146 OE_Order_PUB.G_MISS_LINE_ADJ_TBL,
147 p_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type :=
148 OE_Order_PUB.G_MISS_LINE_PRICE_ATT_TBL,
149 p_old_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type :=
150 OE_Order_PUB.G_MISS_LINE_PRICE_ATT_TBL,
151 p_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type :=
152 OE_Order_PUB.G_MISS_LINE_ADJ_ATT_TBL,
153 p_old_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type :=
154 OE_Order_PUB.G_MISS_LINE_ADJ_ATT_TBL,
155 p_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type :=
156 OE_Order_PUB.G_MISS_LINE_ADJ_ASSOC_TBL,
157 p_old_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type :=
158 OE_Order_PUB.G_MISS_LINE_ADJ_ASSOC_TBL,
159 p_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type :=
160 OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL,
161 p_old_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type :=
162 OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL,
163 p_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type :=
164 OE_Order_PUB.G_MISS_LOT_SERIAL_TBL,
165 p_old_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type :=
166 OE_Order_PUB.G_MISS_LOT_SERIAL_TBL,
167 p_action_request_tbl IN OE_Order_PUB.Request_Tbl_Type :=
168 OE_Order_PUB.G_MISS_REQUEST_TBL
169 ) IS
170 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_NOTICE';
171 l_api_version CONSTANT NUMBER := 1.0;
172
173 -- 3042254
174 l_header_rec OE_Order_PUB.Header_Rec_Type;
175 l_old_header_rec OE_Order_PUB.Header_Rec_Type;
176 l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
177 l_old_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
178 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
179 l_old_Header_Price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
180 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
181 l_old_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
182 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
183 l_old_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
184 l_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
185 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
186 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
187 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
188 l_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
189 l_old_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
190 l_Line_Price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
191 l_old_Line_Price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
192 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
193 l_old_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
194 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
195 l_old_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
196 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
197 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
198 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
199 l_old_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
200 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type;
201
202 BEGIN
203
204 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
205
206 -- Standard Start of API savepoint
207
208 SAVEPOINT UPDATE_NOTICE_PUB;
209
210 -- Standard call to check for call compatibility.
211
212 IF NOT FND_API.Compatible_API_Call (l_api_version ,
213 p_api_version ,
214 l_api_name ,
215 G_PKG_NAME ) THEN
216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217 END IF;
218
219 -- Initialize message list if p_init_msg_list is set to TRUE.
220
221 IF FND_API.to_Boolean( p_init_msg_list ) THEN
222 FND_MSG_PUB.initialize;
223 END IF;
224
225 -- Initialize API return status to success
226
227 x_return_status := FND_API.G_RET_STS_SUCCESS;
228
229 -- UPDATE_NOTICE API specific input parameter validation logic
230 -- none
231
232 -- API Body
233
234 -- 3042254
235 -- mapping to local variables
236 l_header_rec := p_header_rec ;
237 l_old_header_rec := p_old_header_rec ;
238 l_Header_Adj_tbl := p_Header_Adj_tbl ;
239 l_old_Header_Adj_tbl := p_old_Header_Adj_tbl ;
240 l_Header_price_Att_tbl := p_Header_price_Att_tbl ;
241 l_old_Header_Price_Att_tbl := p_old_Header_Price_Att_tbl;
242 l_Header_Adj_Att_tbl := p_Header_Adj_Att_tbl ;
243 l_old_Header_Adj_Att_tbl := p_old_Header_Adj_Att_tbl ;
244 l_Header_Adj_Assoc_tbl := p_Header_Adj_Assoc_tbl ;
245 l_old_Header_Adj_Assoc_tbl := p_old_Header_Adj_Assoc_tbl;
246 l_Header_Scredit_tbl := p_Header_Scredit_tbl ;
247 l_old_Header_Scredit_tbl := p_old_Header_Scredit_tbl ;
248 l_line_tbl := p_line_tbl ;
249 l_old_line_tbl := p_old_line_tbl ;
250 l_Line_Adj_tbl := p_Line_Adj_tbl ;
251 l_old_Line_Adj_tbl := p_old_Line_Adj_tbl ;
252 l_Line_Price_Att_tbl := p_Line_Price_Att_tbl ;
253 l_old_Line_Price_Att_tbl := p_old_Line_Price_Att_tbl ;
254 l_Line_Adj_Att_tbl := p_Line_Adj_Att_tbl ;
255 l_old_Line_Adj_Att_tbl := p_old_Line_Adj_Att_tbl ;
256 l_Line_Adj_Assoc_tbl := p_Line_Adj_Assoc_tbl ;
257 l_old_Line_Adj_Assoc_tbl := p_old_Line_Adj_Assoc_tbl ;
258 l_Line_Scredit_tbl := p_Line_Scredit_tbl ;
259 l_old_Line_Scredit_tbl := p_old_Line_Scredit_tbl ;
260 l_Lot_Serial_tbl := p_Lot_Serial_tbl ;
261 l_old_Lot_Serial_tbl := p_old_Lot_Serial_tbl ;
262 l_action_request_tbl := p_action_request_tbl ;
263
264 -- call user hooks
265 -- customer pre processing
266
267 IF (JTF_USR_HKS.ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C'))
268 THEN
269 ASO_ORDER_FEEDBACK_CUHK.Update_Notice_PRE(
270 p_header_rec => l_header_rec ,
271 p_old_header_rec => l_old_header_rec ,
272 p_Header_Adj_tbl => l_Header_Adj_tbl ,
273 p_old_Header_Adj_tbl => l_old_Header_Adj_tbl ,
274 p_Header_price_Att_tbl => l_Header_price_Att_tbl ,
275 p_old_Header_Price_Att_tbl => l_old_Header_Price_Att_tbl,
276 p_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl ,
277 p_old_Header_Adj_Att_tbl => l_old_Header_Adj_Att_tbl ,
278 p_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl ,
279 p_old_Header_Adj_Assoc_tbl => l_old_Header_Adj_Assoc_tbl,
280 p_Header_Scredit_tbl => l_Header_Scredit_tbl ,
281 p_old_Header_Scredit_tbl => l_old_Header_Scredit_tbl ,
282 p_line_tbl => l_line_tbl ,
283 p_old_line_tbl => l_old_line_tbl ,
284 p_Line_Adj_tbl => l_Line_Adj_tbl ,
285 p_old_Line_Adj_tbl => l_old_Line_Adj_tbl ,
286 p_Line_Price_Att_tbl => l_Line_Price_Att_tbl ,
287 p_old_Line_Price_Att_tbl => l_old_Line_Price_Att_tbl ,
288 p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl ,
289 p_old_Line_Adj_Att_tbl => l_old_Line_Adj_Att_tbl ,
290 p_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl ,
294 p_Lot_Serial_tbl => l_Lot_Serial_tbl ,
291 p_old_Line_Adj_Assoc_tbl => l_old_Line_Adj_Assoc_tbl ,
292 p_Line_Scredit_tbl => l_Line_Scredit_tbl ,
293 p_old_Line_Scredit_tbl => l_old_Line_Scredit_tbl ,
295 p_old_Lot_Serial_tbl => l_old_Lot_Serial_tbl ,
296 p_action_request_tbl => l_action_request_tbl ,
297 x_return_status => x_return_status ,
298 x_msg_count => x_msg_count ,
299 x_msg_data => x_msg_data
300 );
301
302 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
303 THEN
304 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
305 THEN
306 FND_MESSAGE.Set_Name('ASO', 'ERROR_RETURNED');
307 FND_MESSAGE.Set_Token('API', 'ASO_ORDER_FEEDBACK_CUHK.Update_Notice_PRE', FALSE);
308 FND_MSG_PUB.ADD;
309 END IF;
310 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
311 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
312 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
313 RAISE FND_API.G_EXC_ERROR;
314 END IF;
315 END IF;
316
317 END IF; -- customer pre processing
318
319 -- vertical hook
320 IF (JTF_USR_HKS.ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'V'))
321 THEN
322
323 ASO_ORDER_FEEDBACK_VUHK.Update_Notice_PRE(
324 p_header_rec => l_header_rec ,
325 p_old_header_rec => l_old_header_rec ,
326 p_Header_Adj_tbl => l_Header_Adj_tbl ,
327 p_old_Header_Adj_tbl => l_old_Header_Adj_tbl ,
328 p_Header_price_Att_tbl => l_Header_price_Att_tbl ,
329 p_old_Header_Price_Att_tbl => l_old_Header_Price_Att_tbl,
330 p_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl ,
331 p_old_Header_Adj_Att_tbl => l_old_Header_Adj_Att_tbl ,
332 p_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl ,
333 p_old_Header_Adj_Assoc_tbl => l_old_Header_Adj_Assoc_tbl,
334 p_Header_Scredit_tbl => l_Header_Scredit_tbl ,
335 p_old_Header_Scredit_tbl => l_old_Header_Scredit_tbl ,
336 p_line_tbl => l_line_tbl ,
337 p_old_line_tbl => l_old_line_tbl ,
338 p_Line_Adj_tbl => l_Line_Adj_tbl ,
339 p_old_Line_Adj_tbl => l_old_Line_Adj_tbl ,
340 p_Line_Price_Att_tbl => l_Line_Price_Att_tbl ,
341 p_old_Line_Price_Att_tbl => l_old_Line_Price_Att_tbl ,
342 p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl ,
343 p_old_Line_Adj_Att_tbl => l_old_Line_Adj_Att_tbl ,
344 p_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl ,
345 p_old_Line_Adj_Assoc_tbl => l_old_Line_Adj_Assoc_tbl ,
346 p_Line_Scredit_tbl => l_Line_Scredit_tbl ,
347 p_old_Line_Scredit_tbl => l_old_Line_Scredit_tbl ,
348 p_Lot_Serial_tbl => l_Lot_Serial_tbl ,
349 p_old_Lot_Serial_tbl => l_old_Lot_Serial_tbl ,
350 p_action_request_tbl => l_action_request_tbl ,
351 x_return_status => x_return_status ,
352 x_msg_count => x_msg_count ,
353 x_msg_data => x_msg_data
354 );
355
356 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
357 THEN
358 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
359 THEN
360 FND_MESSAGE.Set_Name('ASO', 'ERROR_RETURNED');
361 FND_MESSAGE.Set_Token('API', 'ASO_ORDER_FEEDBACK_VUHK.Update_Notice_PRE', FALSE);
362 FND_MSG_PUB.ADD;
363 END IF;
364 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369 END IF;
370
371 END IF; -- vertical hook
372 -- end of 3042254.
373
374 ASO_ORDER_FEEDBACK_UPDATE_PVT.UPDATE_NOTICE(
375 p_api_version => 1.0,
376 p_init_msg_list => FND_API.G_FALSE,
377 p_commit => FND_API.G_FALSE,
378 x_return_status => x_return_status,
379 x_msg_count => x_msg_count,
380 x_msg_data => x_msg_data,
381 p_queue_type => 'OF_QUEUE',
382 p_header_rec => p_header_rec,
383 p_old_header_rec => p_old_header_rec,
384 p_header_adj_tbl => p_header_adj_tbl,
385 p_old_header_adj_tbl => p_old_header_adj_tbl,
386 p_header_price_att_tbl => p_header_price_att_tbl,
387 p_old_header_price_att_tbl => p_old_header_price_att_tbl,
388 p_Header_Adj_Att_tbl => p_Header_Adj_Att_tbl,
389 p_old_Header_Adj_Att_tbl => p_old_Header_Adj_Att_tbl,
390 p_Header_Adj_Assoc_tbl => p_Header_Adj_Assoc_tbl,
391 p_old_Header_Adj_Assoc_tbl => p_old_Header_Adj_Assoc_tbl,
392 p_Header_Scredit_tbl => p_Header_Scredit_tbl,
393 p_old_Header_Scredit_tbl => p_old_Header_Scredit_tbl,
394 p_line_tbl => p_line_tbl,
395 p_old_line_tbl => p_old_line_tbl,
396 p_Line_Adj_tbl => p_Line_Adj_tbl,
397 p_old_Line_Adj_tbl => p_old_Line_Adj_tbl,
398 p_Line_Price_Att_tbl => p_Line_Price_Att_tbl,
399 p_old_Line_Price_Att_tbl => p_old_Line_Price_Att_tbl,
400 p_Line_Adj_Att_tbl => p_Line_Adj_Att_tbl,
404 p_Line_Scredit_tbl => p_Line_Scredit_tbl,
401 p_old_Line_Adj_Att_tbl => p_old_Line_Adj_Att_tbl,
402 p_Line_Adj_Assoc_tbl => p_Line_Adj_Assoc_tbl,
403 p_old_Line_Adj_Assoc_tbl => p_old_Line_Adj_Assoc_tbl,
405 p_old_Line_Scredit_tbl => p_old_Line_Scredit_tbl,
406 p_Lot_Serial_tbl => p_Lot_Serial_tbl,
407 p_old_Lot_Serial_tbl => p_old_Lot_Serial_tbl,
408 p_action_request_tbl => p_action_request_tbl
409 );
410
411
412 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
413 ROLLBACK TO UPDATE_NOTICE_PUB;
414 RETURN;
415 END IF;
416
417 -- 3042254
418 -- call user hooks
419 -- customer post processing
420
421 IF (JTF_USR_HKS.ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C'))
422 THEN
423 ASO_ORDER_FEEDBACK_CUHK.Update_Notice_POST(
424 p_header_rec => l_header_rec ,
425 p_old_header_rec => l_old_header_rec ,
426 p_Header_Adj_tbl => l_Header_Adj_tbl ,
427 p_old_Header_Adj_tbl => l_old_Header_Adj_tbl ,
428 p_Header_price_Att_tbl => l_Header_price_Att_tbl ,
429 p_old_Header_Price_Att_tbl => l_old_Header_Price_Att_tbl,
430 p_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl ,
431 p_old_Header_Adj_Att_tbl => l_old_Header_Adj_Att_tbl ,
432 p_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl ,
433 p_old_Header_Adj_Assoc_tbl => l_old_Header_Adj_Assoc_tbl,
434 p_Header_Scredit_tbl => l_Header_Scredit_tbl ,
435 p_old_Header_Scredit_tbl => l_old_Header_Scredit_tbl ,
436 p_line_tbl => l_line_tbl ,
437 p_old_line_tbl => l_old_line_tbl ,
438 p_Line_Adj_tbl => l_Line_Adj_tbl ,
439 p_old_Line_Adj_tbl => l_old_Line_Adj_tbl ,
440 p_Line_Price_Att_tbl => l_Line_Price_Att_tbl ,
441 p_old_Line_Price_Att_tbl => l_old_Line_Price_Att_tbl ,
442 p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl ,
443 p_old_Line_Adj_Att_tbl => l_old_Line_Adj_Att_tbl ,
444 p_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl ,
445 p_old_Line_Adj_Assoc_tbl => l_old_Line_Adj_Assoc_tbl ,
446 p_Line_Scredit_tbl => l_Line_Scredit_tbl ,
447 p_old_Line_Scredit_tbl => l_old_Line_Scredit_tbl ,
448 p_Lot_Serial_tbl => l_Lot_Serial_tbl ,
449 p_old_Lot_Serial_tbl => l_old_Lot_Serial_tbl ,
450 p_action_request_tbl => l_action_request_tbl ,
451 x_return_status => x_return_status ,
452 x_msg_count => x_msg_count ,
453 x_msg_data => x_msg_data
454 );
455
456 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
457 THEN
458 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
459 THEN
460 FND_MESSAGE.Set_Name('ASO', 'ERROR_RETURNED');
461 FND_MESSAGE.Set_Token('API', 'ASO_ORDER_FEEDBACK_CUHK.Update_Notice_POST', FALSE);
462 FND_MSG_PUB.ADD;
463 END IF;
464 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
465 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
466 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
467 RAISE FND_API.G_EXC_ERROR;
468 END IF;
469 END IF;
470
471 END IF; -- customer pre processing
472
473 -- vertical hook
474 IF (JTF_USR_HKS.ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'V'))
475 THEN
476
477 ASO_ORDER_FEEDBACK_VUHK.Update_Notice_POST(
478 p_header_rec => l_header_rec ,
479 p_old_header_rec => l_old_header_rec ,
480 p_Header_Adj_tbl => l_Header_Adj_tbl ,
481 p_old_Header_Adj_tbl => l_old_Header_Adj_tbl ,
482 p_Header_price_Att_tbl => l_Header_price_Att_tbl ,
483 p_old_Header_Price_Att_tbl => l_old_Header_Price_Att_tbl,
484 p_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl ,
485 p_old_Header_Adj_Att_tbl => l_old_Header_Adj_Att_tbl ,
486 p_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl ,
487 p_old_Header_Adj_Assoc_tbl => l_old_Header_Adj_Assoc_tbl,
488 p_Header_Scredit_tbl => l_Header_Scredit_tbl ,
489 p_old_Header_Scredit_tbl => l_old_Header_Scredit_tbl ,
490 p_line_tbl => l_line_tbl ,
491 p_old_line_tbl => l_old_line_tbl ,
492 p_Line_Adj_tbl => l_Line_Adj_tbl ,
493 p_old_Line_Adj_tbl => l_old_Line_Adj_tbl ,
494 p_Line_Price_Att_tbl => l_Line_Price_Att_tbl ,
495 p_old_Line_Price_Att_tbl => l_old_Line_Price_Att_tbl ,
496 p_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl ,
497 p_old_Line_Adj_Att_tbl => l_old_Line_Adj_Att_tbl ,
498 p_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl ,
499 p_old_Line_Adj_Assoc_tbl => l_old_Line_Adj_Assoc_tbl ,
500 p_Line_Scredit_tbl => l_Line_Scredit_tbl ,
501 p_old_Line_Scredit_tbl => l_old_Line_Scredit_tbl ,
502 p_Lot_Serial_tbl => l_Lot_Serial_tbl ,
503 p_old_Lot_Serial_tbl => l_old_Lot_Serial_tbl ,
504 p_action_request_tbl => l_action_request_tbl ,
505 x_return_status => x_return_status ,
506 x_msg_count => x_msg_count ,
507 x_msg_data => x_msg_data
508 );
509
510 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
511 THEN
512 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
513 THEN
517 END IF;
514 FND_MESSAGE.Set_Name('ASO', 'ERROR_RETURNED');
515 FND_MESSAGE.Set_Token('API', 'ASO_ORDER_FEEDBACK_VUHK.Update_Notice_POST', FALSE);
516 FND_MSG_PUB.ADD;
518 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
519 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
520 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
521 RAISE FND_API.G_EXC_ERROR;
522 END IF;
523 END IF;
524
525 END IF; -- vertical hook
526 -- end of 3042254.
527
528
529 -- End of API Body
530
531 -- Standard check of p_commit.
532
533 IF FND_API.To_Boolean( p_commit ) THEN
534 COMMIT WORK;
535 END IF;
536
537 -- Standard call to get message count and if count is 1, get message info.
538
539 FND_MSG_PUB.Count_And_Get
540 (p_count => x_msg_count ,
541 p_data => x_msg_data
542 );
543
544 EXCEPTION
545
546 WHEN FND_API.G_EXC_ERROR THEN
547 ROLLBACK TO UPDATE_NOTICE_PUB;
548 x_return_status := FND_API.G_RET_STS_ERROR ;
549 FND_MSG_PUB.Count_And_Get
550 (p_count => x_msg_count ,
551 p_data => x_msg_data
552 );
553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
554 ROLLBACK TO UPDATE_NOTICE_PUB;
555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
556 FND_MSG_PUB.Count_And_Get
557 (p_count => x_msg_count ,
558 p_data => x_msg_data
559 );
560 WHEN OTHERS THEN
561 ROLLBACK TO UPDATE_NOTICE_PUB;
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
563 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
564 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
565 END IF;
566 FND_MSG_PUB.Count_And_Get
567 (p_count => x_msg_count ,
568 p_data => x_msg_data
569 );
570 END UPDATE_NOTICE;
571
572
573 --------------------------------------------------------------------------
574
575 -- Start of comments
576 -- API name : GET_NOTICE
577 -- Type : Public
578 -- Function : This API is the PUBLIC API that is invoked by CRM Apps
579 -- to get the data regarding changes (inserts/updates/deletes) to the
580 -- Order Entities communicated by the Order Management application.
581 -- Pre-reqs : None.
582 --
583 -- Standard IN Parameters:
584 -- p_api_version IN NUMBER Required
585 -- p_init_msg_list IN VARCHAR2 Optional
586 -- Default = FND_API.G_FALSE
587 -- p_commit IN VARCHAR2 Optional
588 -- Default = FND_API.G_FALSE
589 --
590 -- Standard OUT NOCOPY /* file.sql.39 change */ Parameters:
591 -- x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2(1)
592 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
593 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2(2000)
594 --
595 -- GET_NOTICE API specific IN Parameters:
596 -- p_app_short_name IN VARCHAR2 Required
597 -- p_wait IN NUMBER Optional
598 -- Default = DBMS_AQ.NO_WAIT
599 --
600 -- GET_NOTICE API specific OUT NOCOPY /* file.sql.39 change */ Parameters:
601 --
602 -- x_no_more_messages OUT NOCOPY /* file.sql.39 change */ VARCHAR2
603 -- x_header_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Rec_Type
604 -- x_old_header_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Rec_Type
605 -- x_Header_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Tbl_Type
606 -- x_old_Header_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Tbl_Type
607 -- x_Header_price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Price_Att_Tbl_Type
608 -- x_old_Header_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Price_Att_Tbl_Type
609 -- x_Header_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Att_Tbl_Type
610 -- x_old_Header_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Att_Tbl_Type
611 -- x_Header_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
612 -- x_old_Header_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
613 -- x_Header_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Scredit_Tbl_Type
614 -- x_old_Header_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Scredit_Tbl_Type
615 -- x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Tbl_Type
616 -- x_old_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Tbl_Type
617 -- x_Line_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Tbl_Type
618 -- x_old_Line_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Tbl_Type
619 -- x_Line_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Price_Att_Tbl_Type
620 -- x_old_Line_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Price_Att_Tbl_Type
621 -- x_Line_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Att_Tbl_Type
622 -- x_old_Line_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Att_Tbl_Type
623 -- x_Line_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
624 -- x_old_Line_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
625 -- x_Line_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Scredit_Tbl_Type
626 -- x_old_Line_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Scredit_Tbl_Type
630 --
627 -- x_Lot_Serial_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Lot_Serial_Tbl_Type
628 -- x_old_Lot_Serial_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Lot_Serial_Tbl_Type
629 -- x_action_request_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Request_Tbl_Type
631 --
632 -- Version : Current version 1.0
633 -- Initial version 1.0
634 --
635 -- End of comments
636 --------------------------------------------------------------------------
637
638 PROCEDURE GET_NOTICE
639 (
640 p_api_version IN NUMBER,
641 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
642 p_commit IN VARCHAR2 := FND_API.G_FALSE,
643 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
644 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
645 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
646 p_app_short_name IN VARCHAR2,
647 p_wait IN NUMBER := DBMS_AQ.NO_WAIT,
648 p_deq_condition IN VARCHAR2 DEFAULT NULL, /* Bug 9410311 */
649 x_no_more_messages OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
650 x_header_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Rec_Type,
651 x_old_header_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Rec_Type,
652 x_Header_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Tbl_Type,
653 x_old_Header_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Tbl_Type,
654 x_Header_price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Price_Att_Tbl_Type,
655 x_old_Header_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Price_Att_Tbl_Type,
656 x_Header_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Att_Tbl_Type,
657 x_old_Header_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Att_Tbl_Type,
658 x_Header_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Assoc_Tbl_Type,
659 x_old_Header_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Assoc_Tbl_Type,
660 x_Header_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Scredit_Tbl_Type,
661 x_old_Header_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Scredit_Tbl_Type,
662 x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Tbl_Type,
663 x_old_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Tbl_Type,
664 x_Line_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Tbl_Type,
665 x_old_Line_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Tbl_Type,
666 x_Line_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Price_Att_Tbl_Type,
667 x_old_Line_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Price_Att_Tbl_Type,
668 x_Line_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Att_Tbl_Type,
669 x_old_Line_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Att_Tbl_Type,
670 x_Line_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Assoc_Tbl_Type,
671 x_old_Line_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Assoc_Tbl_Type,
672 x_Line_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Scredit_Tbl_Type,
673 x_old_Line_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Scredit_Tbl_Type,
674 x_Lot_Serial_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Lot_Serial_Tbl_Type,
675 x_old_Lot_Serial_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Lot_Serial_Tbl_Type,
676 x_action_request_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Request_Tbl_Type
677 ) IS
678 l_api_name CONSTANT VARCHAR2(30) := 'GET_NOTICE';
679 l_api_version CONSTANT NUMBER := 1.0;
680
681 BEGIN
682
683 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
684
685 -- Standard Start of API savepoint
686
687 SAVEPOINT GET_NOTICE_PUB;
688
689 -- Standard call to check for call compatibility.
690
691 IF NOT FND_API.Compatible_API_Call (l_api_version ,
692 p_api_version ,
693 l_api_name ,
694 G_PKG_NAME ) THEN
695 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
696 END IF;
697
698 -- Initialize message list if p_init_msg_list is set to TRUE.
699
700 IF FND_API.to_Boolean( p_init_msg_list ) THEN
701 FND_MSG_PUB.initialize;
702 END IF;
703
704 -- Initialize API return status to success
705
706 x_return_status := FND_API.G_RET_STS_SUCCESS;
707
708 -- GET_NOTICE API specific input parameter validation logic
709
710 -- need to check that p_app_short_name (required param) is passed in
711
712 ASO_ORDER_FEEDBACK_UTIL.Check_Reqd_Param
713 (
714 p_app_short_name,
715 'p_app_short_name',
716 l_api_name
717 );
718
719 -- need to check that p_app_short_name is a valid application short name
720
721 ASO_ORDER_FEEDBACK_UTIL.Check_LookupCode
722 (
723 'ASO_ORDER_FEEDBACK_CRM_APPS',
724 p_app_short_name,
725 'p_app_short_name',
726 l_api_name
727 );
728
729 -- API Body
730
731
732 ASO_ORDER_FEEDBACK_GET_PVT.GET_NOTICE(
733 p_api_version => 1.0,
734 p_init_msg_list => FND_API.G_FALSE,
738 x_msg_data => x_msg_data,
735 p_commit => FND_API.G_FALSE,
736 x_return_status => x_return_status,
737 x_msg_count => x_msg_count,
739 p_app_short_name => p_app_short_name,
740 p_queue_type => 'OF_QUEUE',
741 p_wait => p_wait,
742 p_deq_condition => p_deq_condition, -- bug 9410311
743 x_no_more_messages => x_no_more_messages,
744 x_header_rec => x_header_rec,
745 x_old_header_rec => x_old_header_rec,
746 x_header_adj_tbl => x_header_adj_tbl,
747 x_old_header_adj_tbl => x_old_header_adj_tbl,
748 x_header_price_att_tbl => x_header_price_att_tbl,
749 x_old_header_price_att_tbl => x_old_header_price_att_tbl,
750 x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl,
751 x_old_Header_Adj_Att_tbl => x_old_Header_Adj_Att_tbl,
752 x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl,
753 x_old_Header_Adj_Assoc_tbl => x_old_Header_Adj_Assoc_tbl,
754 x_Header_Scredit_tbl => x_Header_Scredit_tbl,
755 x_old_Header_Scredit_tbl => x_old_Header_Scredit_tbl,
756 x_line_tbl => x_line_tbl,
757 x_old_line_tbl => x_old_line_tbl,
758 x_Line_Adj_tbl => x_Line_Adj_tbl,
759 x_old_Line_Adj_tbl => x_old_Line_Adj_tbl,
760 x_Line_Price_Att_tbl => x_Line_Price_Att_tbl,
761 x_old_Line_Price_Att_tbl => x_old_Line_Price_Att_tbl,
762 x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl,
763 x_old_Line_Adj_Att_tbl => x_old_Line_Adj_Att_tbl,
764 x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl,
765 x_old_Line_Adj_Assoc_tbl => x_old_Line_Adj_Assoc_tbl,
766 x_Line_Scredit_tbl => x_Line_Scredit_tbl,
767 x_old_Line_Scredit_tbl => x_old_Line_Scredit_tbl,
768 x_Lot_Serial_tbl => x_Lot_Serial_tbl,
769 x_old_Lot_Serial_tbl => x_old_Lot_Serial_tbl,
770 x_action_request_tbl => x_action_request_tbl
771 );
772
773 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
774 ROLLBACK TO GET_NOTICE_PUB;
775 RETURN;
776 END IF;
777
778 -- End of API Body
779
780 -- Standard check of p_commit.
781
782 IF FND_API.To_Boolean( p_commit ) THEN
783 COMMIT WORK;
784 END IF;
785
786 -- Standard call to get message count and if count is 1, get message info.
787
788 FND_MSG_PUB.Count_And_Get
789 (p_count => x_msg_count ,
790 p_data => x_msg_data
791 );
792
793 EXCEPTION
794
795 WHEN FND_API.G_EXC_ERROR THEN
796 ROLLBACK TO GET_NOTICE_PUB;
797 x_return_status := FND_API.G_RET_STS_ERROR ;
798 FND_MSG_PUB.Count_And_Get
799 (p_count => x_msg_count ,
800 p_data => x_msg_data
801 );
802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
803 ROLLBACK TO GET_NOTICE_PUB;
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
805 FND_MSG_PUB.Count_And_Get
806 (p_count => x_msg_count ,
807 p_data => x_msg_data
808 );
809 WHEN OTHERS THEN
810 ROLLBACK TO GET_NOTICE_PUB;
811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
812 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
813 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
814 END IF;
815 FND_MSG_PUB.Count_And_Get
816 (p_count => x_msg_count ,
817 p_data => x_msg_data
818 );
819 END GET_NOTICE;
820
821
822 --------------------------------------------------------------------------
823
824 -- Start of comments
825 -- API name : HANDLE_EXCEPTION
826 -- Type : Public
827 -- Function : This API is the PUBLIC API that is invoked by consumer apps
828 -- to enqueue a failed message in the Order Feedback Exception
829 -- Queue. Consumers may use the GET_EXCEPTION public API
830 -- to subsequently dequeue these failed message. If p_commit is
831 -- set to true the message is enqueued in an immediate mode and
832 -- is immediately available in for dequeueing by the
833 -- GET_EXCEPTION API, otherwise the message is available in
834 -- the exception queue only after the calling application commits.
835 -- Pre-reqs : None.
836 --
837 -- Standard IN Parameters:
838 -- p_api_version IN NUMBER Required
839 -- p_init_msg_list IN VARCHAR2 Optional
840 -- Default = FND_API.G_FALSE
841 -- p_commit IN VARCHAR2 Optional
842 -- Default = FND_API.G_FALSE
843 --
844 -- Standard OUT NOCOPY /* file.sql.39 change */ Parameters:
845 -- x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2(1)
846 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
847 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2(2000)
848 --
849 -- HANDLE_EXCEPTION API specific IN Parameters:
850
851 -- p_app_short_name IN VARCHAR2 Required
852 --
853 -- ALL PARAMETERS BELOW ARE OPTIONAL
854 --
855 -- p_header_rec IN OE_Order_PUB.Header_Rec_Type
856 -- Default = OE_Order_PUB.G_MISS_HEADER_REC
860 -- Default = OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL
857 -- p_old_header_rec IN OE_Order_PUB.Header_Rec_Type
858 -- Default = OE_ORDER_PUB.G_MISS_HEADER_REC
859 -- p_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type
861 -- p_old_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type
862 -- Default = OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL
863 -- p_Header_price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type
864 -- Default = OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL
865 -- p_old_Header_Price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type
866 -- Default = OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL
867 -- p_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type
868 -- Default = OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL
869 -- p_old_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type
870 -- Default = OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL
871 -- p_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
872 -- Default = OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL
873 -- p_old_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
874 -- Default = OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL
875 -- p_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type
876 -- Default = OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL
877 -- p_old_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type
878 -- Default = OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL
879 -- p_line_tbl IN OE_Order_PUB.Line_Tbl_Type
880 -- Default = OE_ORDER_PUB.G_MISS_LINE_TBL
881 -- p_old_line_tbl IN OE_Order_PUB.Line_Tbl_Type
882 -- Default = OE_ORDER_PUB.G_MISS_LINE_TBL
883 -- p_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type
884 -- Default = OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL
885 -- p_old_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type
886 -- Default = OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL
887 -- p_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type
888 -- Default = OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL
889 -- p_old_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type
890 -- Default = OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL
891 -- p_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type
892 -- Default = OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL
893 -- p_old_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type
894 -- Default = OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL
895 -- p_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
896 -- Default = OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL
897 -- p_old_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
898 -- Default = OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL
899 -- p_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type
900 -- Default = OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL
901 -- p_old_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type
902 -- Default = OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL
903 -- p_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type
904 -- Default = OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL
905 -- p_old_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type
906 -- Default = OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL
907 -- p_action_request_tbl IN OE_Order_PUB.Request_Tbl_Type
908 -- Default = OE_ORDER_PUB.G_MISS_REQUEST_TBL
909 --
910 --
911 -- HANDLE_EXCEPTION API specific OUT NOCOPY /* file.sql.39 change */ Parameters:
912 -- none
913 --
914 -- Version : Current version 1.0
915 -- Initial version 1.0
916 --
917 -- End of comments
918 --------------------------------------------------------------------------
919
920
921 PROCEDURE HANDLE_EXCEPTION
922 (
923 p_api_version IN NUMBER,
924 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
925 p_commit IN VARCHAR2 := FND_API.G_FALSE,
926 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
927 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
928 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
929 p_app_short_name IN VARCHAR2,
930 p_header_rec IN OE_Order_PUB.Header_Rec_Type :=
931 OE_Order_PUB.G_MISS_HEADER_REC,
932 p_old_header_rec IN OE_Order_PUB.Header_Rec_Type :=
933 OE_Order_PUB.G_MISS_HEADER_REC,
934 p_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type :=
935 OE_Order_PUB.G_MISS_HEADER_ADJ_TBL,
936 p_old_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type :=
937 OE_Order_PUB.G_MISS_HEADER_ADJ_TBL,
938 p_Header_price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type :=
939 OE_Order_PUB.G_MISS_HEADER_PRICE_ATT_TBL,
940 p_old_Header_Price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type :=
941 OE_Order_PUB.G_MISS_HEADER_PRICE_ATT_TBL,
942 p_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type :=
946 p_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type :=
943 OE_Order_PUB.G_MISS_HEADER_ADJ_ATT_TBL,
944 p_old_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type :=
945 OE_Order_PUB.G_MISS_HEADER_ADJ_ATT_TBL,
947 OE_Order_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL,
948 p_old_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type :=
949 OE_Order_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL,
950 p_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type :=
951 OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL,
952 p_old_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type :=
953 OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL,
954 p_line_tbl IN OE_Order_PUB.Line_Tbl_Type :=
955 OE_Order_PUB.G_MISS_LINE_TBL,
956 p_old_line_tbl IN OE_Order_PUB.Line_Tbl_Type :=
957 OE_Order_PUB.G_MISS_LINE_TBL,
958 p_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type :=
959 OE_Order_PUB.G_MISS_LINE_ADJ_TBL,
960 p_old_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type :=
961 OE_Order_PUB.G_MISS_LINE_ADJ_TBL,
962 p_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type :=
963 OE_Order_PUB.G_MISS_LINE_PRICE_ATT_TBL,
964 p_old_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type :=
965 OE_Order_PUB.G_MISS_LINE_PRICE_ATT_TBL,
966 p_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type :=
967 OE_Order_PUB.G_MISS_LINE_ADJ_ATT_TBL,
968 p_old_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type :=
969 OE_Order_PUB.G_MISS_LINE_ADJ_ATT_TBL,
970 p_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type :=
971 OE_Order_PUB.G_MISS_LINE_ADJ_ASSOC_TBL,
972 p_old_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type :=
973 OE_Order_PUB.G_MISS_LINE_ADJ_ASSOC_TBL,
974 p_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type :=
975 OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL,
976 p_old_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type :=
977 OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL,
978 p_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type :=
979 OE_Order_PUB.G_MISS_LOT_SERIAL_TBL,
980 p_old_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type :=
981 OE_Order_PUB.G_MISS_LOT_SERIAL_TBL,
982 p_action_request_tbl IN OE_Order_PUB.Request_Tbl_Type :=
983 OE_Order_PUB.G_MISS_REQUEST_TBL
984 ) IS
985 l_api_name CONSTANT VARCHAR2(30) := 'HANDLE_EXCEPTION';
986 l_api_version CONSTANT NUMBER := 1.0;
987
988 BEGIN
989
990 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
991
992 -- Standard Start of API savepoint
993
994 SAVEPOINT HANDLE_EXCEPTION_PUB;
995
996 -- Standard call to check for call compatibility.
997
998 IF NOT FND_API.Compatible_API_Call (l_api_version ,
999 p_api_version ,
1000 l_api_name ,
1001 G_PKG_NAME ) THEN
1002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1003 END IF;
1004
1005 -- Initialize message list if p_init_msg_list is set to TRUE.
1006
1007 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1008 FND_MSG_PUB.initialize;
1009 END IF;
1010
1011 -- Initialize API return status to success
1012
1013 x_return_status := FND_API.G_RET_STS_SUCCESS;
1014
1015 -- HANDLE_EXCEPTION API specific input parameter validation logic
1016 -- need to check that p_app_short_name (required param) is passed in
1017
1018 ASO_ORDER_FEEDBACK_UTIL.Check_Reqd_Param
1019 (
1020 p_app_short_name,
1021 'p_app_short_name',
1022 l_api_name
1023 );
1024
1025 -- need to check that p_app_short_name is a valid application short name
1026
1027 ASO_ORDER_FEEDBACK_UTIL.Check_LookupCode
1028 (
1029 'ASO_ORDER_FEEDBACK_CRM_APPS',
1030 p_app_short_name,
1031 'p_app_short_name',
1032 l_api_name
1033 );
1034
1035
1036 -- API Body
1037
1038
1039 ASO_ORDER_FEEDBACK_UPDATE_PVT.UPDATE_NOTICE(
1040 p_api_version => 1.0,
1041 p_init_msg_list => FND_API.G_FALSE,
1042 p_commit => FND_API.G_FALSE,
1043 x_return_status => x_return_status,
1044 x_msg_count => x_msg_count,
1045 x_msg_data => x_msg_data,
1046 p_app_short_name => p_app_short_name,
1047 p_queue_type => 'OF_EXCP_QUEUE',
1048 p_header_rec => p_header_rec,
1049 p_old_header_rec => p_old_header_rec,
1050 p_header_adj_tbl => p_header_adj_tbl,
1051 p_old_header_adj_tbl => p_old_header_adj_tbl,
1052 p_header_price_att_tbl => p_header_price_att_tbl,
1053 p_old_header_price_att_tbl => p_old_header_price_att_tbl,
1054 p_Header_Adj_Att_tbl => p_Header_Adj_Att_tbl,
1055 p_old_Header_Adj_Att_tbl => p_old_Header_Adj_Att_tbl,
1059 p_old_Header_Scredit_tbl => p_old_Header_Scredit_tbl,
1056 p_Header_Adj_Assoc_tbl => p_Header_Adj_Assoc_tbl,
1057 p_old_Header_Adj_Assoc_tbl => p_old_Header_Adj_Assoc_tbl,
1058 p_Header_Scredit_tbl => p_Header_Scredit_tbl,
1060 p_line_tbl => p_line_tbl,
1061 p_old_line_tbl => p_old_line_tbl,
1062 p_Line_Adj_tbl => p_Line_Adj_tbl,
1063 p_old_Line_Adj_tbl => p_old_Line_Adj_tbl,
1064 p_Line_Price_Att_tbl => p_Line_Price_Att_tbl,
1065 p_old_Line_Price_Att_tbl => p_old_Line_Price_Att_tbl,
1066 p_Line_Adj_Att_tbl => p_Line_Adj_Att_tbl,
1067 p_old_Line_Adj_Att_tbl => p_old_Line_Adj_Att_tbl,
1068 p_Line_Adj_Assoc_tbl => p_Line_Adj_Assoc_tbl,
1069 p_old_Line_Adj_Assoc_tbl => p_old_Line_Adj_Assoc_tbl,
1070 p_Line_Scredit_tbl => p_Line_Scredit_tbl,
1071 p_old_Line_Scredit_tbl => p_old_Line_Scredit_tbl,
1072 p_Lot_Serial_tbl => p_Lot_Serial_tbl,
1073 p_old_Lot_Serial_tbl => p_old_Lot_Serial_tbl,
1074 p_action_request_tbl => p_action_request_tbl
1075 );
1076
1077
1078 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1079 ROLLBACK TO HANDLE_EXCEPTION_PUB;
1080 RETURN;
1081 END IF;
1082
1083 -- End of API Body
1084
1085 -- Standard check of p_commit.
1086
1087 IF FND_API.To_Boolean( p_commit ) THEN
1088 COMMIT WORK;
1089 END IF;
1090
1091 -- Standard call to get message count and if count is 1, get message info.
1092
1093 FND_MSG_PUB.Count_And_Get
1094 (p_count => x_msg_count ,
1095 p_data => x_msg_data
1096 );
1097
1098 EXCEPTION
1099
1100 WHEN FND_API.G_EXC_ERROR THEN
1101 ROLLBACK TO HANDLE_EXCEPTION_PUB;
1102 x_return_status := FND_API.G_RET_STS_ERROR ;
1103 FND_MSG_PUB.Count_And_Get
1104 (p_count => x_msg_count ,
1105 p_data => x_msg_data
1106 );
1107 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1108 ROLLBACK TO HANDLE_EXCEPTION_PUB;
1109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1110 FND_MSG_PUB.Count_And_Get
1111 (p_count => x_msg_count ,
1112 p_data => x_msg_data
1113 );
1114 WHEN OTHERS THEN
1115 ROLLBACK TO HANDLE_EXCEPTION_PUB;
1116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1117 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1118 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
1119 END IF;
1120 FND_MSG_PUB.Count_And_Get
1121 (p_count => x_msg_count ,
1122 p_data => x_msg_data
1123 );
1124 END HANDLE_EXCEPTION;
1125
1126 --------------------------------------------------------------------------
1127
1128 -- Start of comments
1129 -- API name : GET_EXCEPTION
1130 -- Type : Public
1131 -- Function : This API is the PUBLIC API that is invoked by CRM Apps
1132 -- to get the data from the Order Feedback Exception Queue.
1133 -- The messages from the Order Feedback Exception Queue may
1134 -- be either retrieved in the browse mode or the remove mode.
1135 -- Pre-reqs : Data must have been enqueued by using the HANDLE_EXCEPTION
1136 -- PUBLIC API.
1137 --
1138 -- Standard IN Parameters:
1139 -- p_api_version IN NUMBER Required
1140 -- p_init_msg_list IN VARCHAR2 Optional
1141 -- Default = FND_API.G_FALSE
1142 -- p_commit IN VARCHAR2 Optional
1143 -- Default = FND_API.G_FALSE
1144 --
1145 -- Standard OUT NOCOPY /* file.sql.39 change */ Parameters:
1146 -- x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2(1)
1147 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1148 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2(2000)
1149 --
1150 -- GET_EXCEPTION API specific IN Parameters:
1151 -- p_app_short_name IN VARCHAR2 Required
1152 -- p_wait IN NUMBER Optional
1153 -- Default = DBMS_AQ.NO_WAIT
1154 -- p_dequeue_mode IN VARCHAR2 Optional
1155 -- Default = DBMS_AQ.REMOVE
1156 -- p_navigation IN VARCHAR2 Optional
1157 -- DEFAULT = DBMS_AQ.FIRST_MESSAGE,
1158 --
1159 -- GET_EXCEPTION API specific OUT NOCOPY /* file.sql.39 change */ Parameters:
1160 --
1161 -- x_no_more_messages OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1162 -- x_header_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Rec_Type
1163 -- x_old_header_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Rec_Type
1164 -- x_Header_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Tbl_Type
1165 -- x_old_Header_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Tbl_Type
1166 -- x_Header_price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Price_Att_Tbl_Type
1167 -- x_old_Header_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Price_Att_Tbl_Type
1168 -- x_Header_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Att_Tbl_Type
1169 -- x_old_Header_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Att_Tbl_Type
1170 -- x_Header_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
1171 -- x_old_Header_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
1172 -- x_Header_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Scredit_Tbl_Type
1176 -- x_Line_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Tbl_Type
1173 -- x_old_Header_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Scredit_Tbl_Type
1174 -- x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Tbl_Type
1175 -- x_old_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Tbl_Type
1177 -- x_old_Line_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Tbl_Type
1178 -- x_Line_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Price_Att_Tbl_Type
1179 -- x_old_Line_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Price_Att_Tbl_Type
1180 -- x_Line_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Att_Tbl_Type
1181 -- x_old_Line_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Att_Tbl_Type
1182 -- x_Line_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
1183 -- x_old_Line_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
1184 -- x_Line_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Scredit_Tbl_Type
1185 -- x_old_Line_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Scredit_Tbl_Type
1186 -- x_Lot_Serial_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Lot_Serial_Tbl_Type
1187 -- x_old_Lot_Serial_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Lot_Serial_Tbl_Type
1188 -- x_action_request_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Request_Tbl_Type
1189 --
1190 --
1191 -- Version : Current version 1.0
1192 -- Initial version 1.0
1193 --
1194 -- End of comments
1195 --------------------------------------------------------------------------
1196
1197 PROCEDURE GET_EXCEPTION
1198 (
1199 p_api_version IN NUMBER,
1200 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1201 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1202 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1203 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1204 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1205 p_app_short_name IN VARCHAR2,
1206 p_wait IN NUMBER := DBMS_AQ.NO_WAIT,
1207 p_dequeue_mode IN VARCHAR2 := DBMS_AQ.REMOVE,
1208 p_navigation IN VARCHAR2 := DBMS_AQ.FIRST_MESSAGE,
1209 x_no_more_messages OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1210 x_header_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Rec_Type,
1211 x_old_header_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Rec_Type,
1212 x_Header_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Tbl_Type,
1213 x_old_Header_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Tbl_Type,
1214 x_Header_price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Price_Att_Tbl_Type,
1215 x_old_Header_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Price_Att_Tbl_Type,
1216 x_Header_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Att_Tbl_Type,
1217 x_old_Header_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Att_Tbl_Type,
1218 x_Header_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Assoc_Tbl_Type,
1219 x_old_Header_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Assoc_Tbl_Type,
1220 x_Header_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Scredit_Tbl_Type,
1221 x_old_Header_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Scredit_Tbl_Type,
1222 x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Tbl_Type,
1223 x_old_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Tbl_Type,
1224 x_Line_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Tbl_Type,
1225 x_old_Line_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Tbl_Type,
1226 x_Line_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Price_Att_Tbl_Type,
1227 x_old_Line_Price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Price_Att_Tbl_Type,
1228 x_Line_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Att_Tbl_Type,
1229 x_old_Line_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Att_Tbl_Type,
1230 x_Line_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Assoc_Tbl_Type,
1231 x_old_Line_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Assoc_Tbl_Type,
1232 x_Line_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Scredit_Tbl_Type,
1233 x_old_Line_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Scredit_Tbl_Type,
1234 x_Lot_Serial_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Lot_Serial_Tbl_Type,
1235 x_old_Lot_Serial_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Lot_Serial_Tbl_Type,
1236 x_action_request_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Request_Tbl_Type
1237 ) IS
1238 l_api_name CONSTANT VARCHAR2(30) := 'GET_EXCEPTION';
1239 l_api_version CONSTANT NUMBER := 1.0;
1240
1241 BEGIN
1242
1243 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
1244 -- Standard Start of API savepoint
1245
1246 SAVEPOINT GET_EXCEPTION_PUB;
1247
1248 -- Standard call to check for call compatibility.
1249
1250 IF NOT FND_API.Compatible_API_Call (l_api_version ,
1251 p_api_version ,
1252 l_api_name ,
1256
1253 G_PKG_NAME ) THEN
1254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1255 END IF;
1257 -- Initialize message list if p_init_msg_list is set to TRUE.
1258
1259 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1260 FND_MSG_PUB.initialize;
1261 END IF;
1262
1263 -- Initialize API return status to success
1264
1265 x_return_status := FND_API.G_RET_STS_SUCCESS;
1266
1267 -- GET_EXCEPTION API specific input parameter validation logic
1268
1269 -- need to check that p_app_short_name (required param) is passed in
1270
1271 ASO_ORDER_FEEDBACK_UTIL.Check_Reqd_Param
1272 (
1273 p_app_short_name,
1274 'p_app_short_name',
1275 l_api_name
1276 );
1277
1278 -- need to check that p_app_short_name is a valid application short name
1279
1280 ASO_ORDER_FEEDBACK_UTIL.Check_LookupCode
1281 (
1282 'ASO_ORDER_FEEDBACK_CRM_APPS',
1283 p_app_short_name,
1284 'p_app_short_name',
1285 l_api_name
1286 );
1287
1288 -- API Body
1289
1290
1291 ASO_ORDER_FEEDBACK_GET_PVT.GET_NOTICE(
1292 p_api_version => 1.0,
1293 p_init_msg_list => FND_API.G_FALSE,
1294 p_commit => FND_API.G_FALSE,
1295 x_return_status => x_return_status,
1296 x_msg_count => x_msg_count,
1297 x_msg_data => x_msg_data,
1298 p_app_short_name => p_app_short_name,
1299 p_wait => p_wait,
1300 p_dequeue_mode => p_dequeue_mode,
1301 p_navigation => p_navigation,
1302 p_queue_type => 'OF_EXCP_QUEUE',
1303 x_no_more_messages => x_no_more_messages,
1304 x_header_rec => x_header_rec,
1305 x_old_header_rec => x_old_header_rec,
1306 x_header_adj_tbl => x_header_adj_tbl,
1307 x_old_header_adj_tbl => x_old_header_adj_tbl,
1308 x_header_price_att_tbl => x_header_price_att_tbl,
1309 x_old_header_price_att_tbl => x_old_header_price_att_tbl,
1310 x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl,
1311 x_old_Header_Adj_Att_tbl => x_old_Header_Adj_Att_tbl,
1312 x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl,
1313 x_old_Header_Adj_Assoc_tbl => x_old_Header_Adj_Assoc_tbl,
1314 x_Header_Scredit_tbl => x_Header_Scredit_tbl,
1315 x_old_Header_Scredit_tbl => x_old_Header_Scredit_tbl,
1316 x_line_tbl => x_line_tbl,
1317 x_old_line_tbl => x_old_line_tbl,
1318 x_Line_Adj_tbl => x_Line_Adj_tbl,
1319 x_old_Line_Adj_tbl => x_old_Line_Adj_tbl,
1320 x_Line_Price_Att_tbl => x_Line_Price_Att_tbl,
1321 x_old_Line_Price_Att_tbl => x_old_Line_Price_Att_tbl,
1322 x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl,
1323 x_old_Line_Adj_Att_tbl => x_old_Line_Adj_Att_tbl,
1324 x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl,
1325 x_old_Line_Adj_Assoc_tbl => x_old_Line_Adj_Assoc_tbl,
1326 x_Line_Scredit_tbl => x_Line_Scredit_tbl,
1327 x_old_Line_Scredit_tbl => x_old_Line_Scredit_tbl,
1328 x_Lot_Serial_tbl => x_Lot_Serial_tbl,
1329 x_old_Lot_Serial_tbl => x_old_Lot_Serial_tbl,
1330 x_action_request_tbl => x_action_request_tbl
1331 );
1332
1333 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1334 ROLLBACK TO GET_EXCEPTION_PUB;
1335 RETURN;
1336 END IF;
1337
1338 -- End of API Body
1339
1340 -- Standard check of p_commit.
1341
1342 IF FND_API.To_Boolean( p_commit ) THEN
1343 COMMIT WORK;
1344 END IF;
1345
1346 -- Standard call to get message count and if count is 1, get message info.
1347
1348 FND_MSG_PUB.Count_And_Get
1349 (p_count => x_msg_count ,
1350 p_data => x_msg_data
1351 );
1352
1353 EXCEPTION
1354
1355 WHEN FND_API.G_EXC_ERROR THEN
1356 ROLLBACK TO GET_EXCEPTION_PUB;
1357 x_return_status := FND_API.G_RET_STS_ERROR ;
1358 FND_MSG_PUB.Count_And_Get
1359 (p_count => x_msg_count ,
1360 p_data => x_msg_data
1361 );
1362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1363 ROLLBACK TO GET_EXCEPTION_PUB;
1364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1365 FND_MSG_PUB.Count_And_Get
1366 (p_count => x_msg_count ,
1367 p_data => x_msg_data
1368 );
1369 WHEN OTHERS THEN
1370 ROLLBACK TO GET_EXCEPTION_PUB;
1371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1372 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1373 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
1374 END IF;
1375 FND_MSG_PUB.Count_And_Get
1376 (p_count => x_msg_count ,
1377 p_data => x_msg_data
1378 );
1379 END GET_EXCEPTION;
1380
1381 END ASO_ORDER_FEEDBACK_PUB;