[Home] [Help]
PACKAGE BODY: APPS.OE_HEADER_SCREDIT_UTIL
Source
1 PACKAGE BODY OE_Header_Scredit_Util AS
2 /* $Header: OEXUHSCB.pls 120.0.12000000.2 2007/04/13 12:25:46 sgoli ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Header_Scredit_Util';
7 G_ACTIVATE_ORCL_CUSTOMIZATION VARCHAR2(1):= NVL(FND_PROFILE.VALUE('ONT_ACTIVATE_ORACLE_CUSTOMIZATION'),'N');
8 G_ORG_ID NUMBER;
9 FUNCTION G_MISS_OE_AK_HSCREDIT_REC
10 RETURN OE_AK_HEADER_SCREDITS_V%ROWTYPE IS
11 l_rowtype_rec OE_AK_HEADER_SCREDITS_V%ROWTYPE;
12 --
13 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
14 --
15 BEGIN
16
17 l_rowtype_rec.ATTRIBUTE1 := FND_API.G_MISS_CHAR;
18 l_rowtype_rec.ATTRIBUTE10 := FND_API.G_MISS_CHAR;
19 l_rowtype_rec.ATTRIBUTE11 := FND_API.G_MISS_CHAR;
20 l_rowtype_rec.ATTRIBUTE12 := FND_API.G_MISS_CHAR;
21 l_rowtype_rec.ATTRIBUTE13 := FND_API.G_MISS_CHAR;
22 l_rowtype_rec.ATTRIBUTE14 := FND_API.G_MISS_CHAR;
23 l_rowtype_rec.ATTRIBUTE15 := FND_API.G_MISS_CHAR;
24 l_rowtype_rec.ATTRIBUTE2 := FND_API.G_MISS_CHAR;
25 l_rowtype_rec.ATTRIBUTE3 := FND_API.G_MISS_CHAR;
26 l_rowtype_rec.ATTRIBUTE4 := FND_API.G_MISS_CHAR;
27 l_rowtype_rec.ATTRIBUTE5 := FND_API.G_MISS_CHAR;
28 l_rowtype_rec.ATTRIBUTE6 := FND_API.G_MISS_CHAR;
29 l_rowtype_rec.ATTRIBUTE7 := FND_API.G_MISS_CHAR;
30 l_rowtype_rec.ATTRIBUTE8 := FND_API.G_MISS_CHAR;
31 l_rowtype_rec.ATTRIBUTE9 := FND_API.G_MISS_CHAR;
32 l_rowtype_rec.CONTEXT := FND_API.G_MISS_CHAR;
33 l_rowtype_rec.CREATED_BY := FND_API.G_MISS_NUM;
34 l_rowtype_rec.CREATION_DATE := FND_API.G_MISS_DATE;
35 l_rowtype_rec.DB_FLAG := FND_API.G_MISS_CHAR;
36 l_rowtype_rec.DW_UPDATE_ADVICE_FLAG := FND_API.G_MISS_CHAR;
37 l_rowtype_rec.HEADER_ID := FND_API.G_MISS_NUM;
38 l_rowtype_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
39 l_rowtype_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
40 l_rowtype_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
41 l_rowtype_rec.LINE_ID := FND_API.G_MISS_NUM;
42 l_rowtype_rec.OPERATION := FND_API.G_MISS_CHAR;
43 l_rowtype_rec.PERCENT := FND_API.G_MISS_NUM;
44 l_rowtype_rec.RETURN_STATUS := FND_API.G_MISS_CHAR;
45 l_rowtype_rec.SALESREP_ID := FND_API.G_MISS_NUM;
46 l_rowtype_rec.sales_credit_type_id := FND_API.G_MISS_NUM;
47 l_rowtype_rec.SALES_CREDIT_ID := FND_API.G_MISS_NUM;
48 l_rowtype_rec.WH_UPDATE_DATE := FND_API.G_MISS_DATE;
49
50 RETURN l_rowtype_rec;
51
52 END G_MISS_OE_AK_HSCREDIT_REC;
53
54 PROCEDURE API_Rec_To_Rowtype_Rec
55 ( p_HEADER_SCREDIT_rec IN OE_Order_PUB.HEADER_SCREDIT_Rec_Type
56 , x_rowtype_rec IN OUT NOCOPY OE_AK_HEADER_SCREDITS_V%ROWTYPE
57 ) IS
58 --
59 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
60 --
61 BEGIN
62
63 x_rowtype_rec.ATTRIBUTE1 := p_header_scredit_rec.ATTRIBUTE1;
64 x_rowtype_rec.ATTRIBUTE10 := p_header_scredit_rec.ATTRIBUTE10;
65 x_rowtype_rec.ATTRIBUTE11 := p_header_scredit_rec.ATTRIBUTE11;
66 x_rowtype_rec.ATTRIBUTE12 := p_header_scredit_rec.ATTRIBUTE12;
67 x_rowtype_rec.ATTRIBUTE13 := p_header_scredit_rec.ATTRIBUTE13;
68 x_rowtype_rec.ATTRIBUTE14 := p_header_scredit_rec.ATTRIBUTE14;
69 x_rowtype_rec.ATTRIBUTE15 := p_header_scredit_rec.ATTRIBUTE15;
70 x_rowtype_rec.ATTRIBUTE2 := p_header_scredit_rec.ATTRIBUTE2;
71 x_rowtype_rec.ATTRIBUTE3 := p_header_scredit_rec.ATTRIBUTE3;
72 x_rowtype_rec.ATTRIBUTE4 := p_header_scredit_rec.ATTRIBUTE4;
73 x_rowtype_rec.ATTRIBUTE5 := p_header_scredit_rec.ATTRIBUTE5;
74 x_rowtype_rec.ATTRIBUTE6 := p_header_scredit_rec.ATTRIBUTE6;
75 x_rowtype_rec.ATTRIBUTE7 := p_header_scredit_rec.ATTRIBUTE7;
76 x_rowtype_rec.ATTRIBUTE8 := p_header_scredit_rec.ATTRIBUTE8;
77 x_rowtype_rec.ATTRIBUTE9 := p_header_scredit_rec.ATTRIBUTE9;
78 x_rowtype_rec.CONTEXT := p_header_scredit_rec.CONTEXT;
79 x_rowtype_rec.CREATED_BY := p_header_scredit_rec.CREATED_BY;
80 x_rowtype_rec.CREATION_DATE := p_header_scredit_rec.CREATION_DATE;
81 x_rowtype_rec.DB_FLAG := p_header_scredit_rec.DB_FLAG;
82 x_rowtype_rec.DW_UPDATE_ADVICE_FLAG := p_header_scredit_rec.DW_UPDATE_ADVICE_FLAG;
83 x_rowtype_rec.HEADER_ID := p_header_scredit_rec.HEADER_ID;
84 x_rowtype_rec.LAST_UPDATED_BY := p_header_scredit_rec.LAST_UPDATED_BY;
85 x_rowtype_rec.LAST_UPDATE_DATE := p_header_scredit_rec.LAST_UPDATE_DATE;
86 x_rowtype_rec.LAST_UPDATE_LOGIN := p_header_scredit_rec.LAST_UPDATE_LOGIN;
87 x_rowtype_rec.LINE_ID := p_header_scredit_rec.LINE_ID;
88 x_rowtype_rec.OPERATION := p_header_scredit_rec.OPERATION;
89 x_rowtype_rec.PERCENT := p_header_scredit_rec.PERCENT;
90 x_rowtype_rec.RETURN_STATUS := p_header_scredit_rec.RETURN_STATUS;
91 x_rowtype_rec.SALESREP_ID := p_header_scredit_rec.SALESREP_ID;
92 x_rowtype_rec.sales_credit_type_id := p_header_scredit_rec.sales_credit_type_id;
93 x_rowtype_rec.SALES_CREDIT_ID := p_header_scredit_rec.SALES_CREDIT_ID;
94 x_rowtype_rec.WH_UPDATE_DATE := p_header_scredit_rec.WH_UPDATE_DATE;
95
96 END API_Rec_To_RowType_Rec;
97
98
99 PROCEDURE Rowtype_Rec_To_API_Rec
100 ( p_record IN OE_AK_HEADER_SCREDITS_V%ROWTYPE
101 , x_api_rec IN OUT NOCOPY OE_Order_PUB.HEADER_SCREDIT_Rec_Type
102 ) IS
103 --
104 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
105 --
106 BEGIN
107
108 x_api_rec.ATTRIBUTE1 := p_record.ATTRIBUTE1;
109 x_api_rec.ATTRIBUTE10 := p_record.ATTRIBUTE10;
110 x_api_rec.ATTRIBUTE11 := p_record.ATTRIBUTE11;
111 x_api_rec.ATTRIBUTE12 := p_record.ATTRIBUTE12;
112 x_api_rec.ATTRIBUTE13 := p_record.ATTRIBUTE13;
113 x_api_rec.ATTRIBUTE14 := p_record.ATTRIBUTE14;
114 x_api_rec.ATTRIBUTE15 := p_record.ATTRIBUTE15;
115 x_api_rec.ATTRIBUTE2 := p_record.ATTRIBUTE2;
116 x_api_rec.ATTRIBUTE3 := p_record.ATTRIBUTE3;
117 x_api_rec.ATTRIBUTE4 := p_record.ATTRIBUTE4;
118 x_api_rec.ATTRIBUTE5 := p_record.ATTRIBUTE5;
119 x_api_rec.ATTRIBUTE6 := p_record.ATTRIBUTE6;
120 x_api_rec.ATTRIBUTE7 := p_record.ATTRIBUTE7;
121 x_api_rec.ATTRIBUTE8 := p_record.ATTRIBUTE8;
122 x_api_rec.ATTRIBUTE9 := p_record.ATTRIBUTE9;
123 x_api_rec.CONTEXT := p_record.CONTEXT;
124 x_api_rec.CREATED_BY := p_record.CREATED_BY;
125 x_api_rec.CREATION_DATE := p_record.CREATION_DATE;
126 x_api_rec.DB_FLAG := p_record.DB_FLAG;
127 x_api_rec.DW_UPDATE_ADVICE_FLAG := p_record.DW_UPDATE_ADVICE_FLAG;
128 x_api_rec.HEADER_ID := p_record.HEADER_ID;
129 x_api_rec.LAST_UPDATED_BY := p_record.LAST_UPDATED_BY;
130 x_api_rec.LAST_UPDATE_DATE := p_record.LAST_UPDATE_DATE;
131 x_api_rec.LAST_UPDATE_LOGIN := p_record.LAST_UPDATE_LOGIN;
132 x_api_rec.LINE_ID := p_record.LINE_ID;
133 x_api_rec.OPERATION := p_record.OPERATION;
134 x_api_rec.PERCENT := p_record.PERCENT;
135 x_api_rec.RETURN_STATUS := p_record.RETURN_STATUS;
136 x_api_rec.SALESREP_ID := p_record.SALESREP_ID;
137 x_api_rec.sales_credit_type_id := p_record.sales_credit_type_id;
138 x_api_rec.SALES_CREDIT_ID := p_record.SALES_CREDIT_ID;
139 x_api_rec.WH_UPDATE_DATE := p_record.WH_UPDATE_DATE;
140
141 END Rowtype_Rec_To_API_Rec;
142
143 -- Procedure Clear_Dependent_Attr
144
145 PROCEDURE Clear_Dependent_Attr
146 ( p_attr_id IN NUMBER := FND_API.G_MISS_NUM
147 , p_x_Header_Scredit_rec IN OUT NOCOPY OE_AK_HEADER_SCREDITS_V%ROWTYPE
148 , p_old_Header_Scredit_rec IN OE_AK_HEADER_SCREDITS_V%ROWTYPE :=
149 G_MISS_OE_AK_HSCREDIT_REC
150 )
151 IS
152 l_index NUMBER :=0;
153 l_src_attr_tbl OE_GLOBALS.NUMBER_Tbl_Type;
154 l_dep_attr_tbl OE_GLOBALS.NUMBER_Tbl_Type;
155 --
156 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
157 --
158 BEGIN
159
160 -- Load out record
161
162
163
164 -- If attr_id is missing compare old and new records and for
165 -- every changed attribute clear its dependent fields.
166
167 IF p_attr_id = FND_API.G_MISS_NUM THEN
168
169 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute1,p_old_Header_Scredit_rec.attribute1)
170 THEN
171 l_index := l_index + 1;
172 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE1;
173 END IF;
174
175 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute10,p_old_Header_Scredit_rec.attribute10)
176 THEN
177 l_index := l_index + 1;
178 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE10;
179 END IF;
180
181 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute11,p_old_Header_Scredit_rec.attribute11)
182 THEN
183 l_index := l_index + 1;
184 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE11;
185 END IF;
186
187 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute12,p_old_Header_Scredit_rec.attribute12)
188 THEN
189 l_index := l_index + 1;
190 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE12;
191 END IF;
192
193 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute13,p_old_Header_Scredit_rec.attribute13)
194 THEN
195 l_index := l_index + 1;
196 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE13;
197 END IF;
198
199 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute14,p_old_Header_Scredit_rec.attribute14)
200 THEN
201 l_index := l_index + 1;
202 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE14;
203 END IF;
204
205 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute15,p_old_Header_Scredit_rec.attribute15)
206 THEN
207 l_index := l_index + 1;
208 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE15;
209 END IF;
210
211 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute2,p_old_Header_Scredit_rec.attribute2)
212 THEN
213 l_index := l_index + 1;
214 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE2;
215 END IF;
216
217 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute3,p_old_Header_Scredit_rec.attribute3)
218 THEN
219 l_index := l_index + 1;
220 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE3;
221 END IF;
222
223 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute4,p_old_Header_Scredit_rec.attribute4)
224 THEN
225 l_index := l_index + 1;
226 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE4;
227 END IF;
228
229 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute5,p_old_Header_Scredit_rec.attribute5)
230 THEN
231 l_index := l_index + 1;
232 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE5;
233 END IF;
234
235 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute6,p_old_Header_Scredit_rec.attribute6)
236 THEN
237 l_index := l_index + 1;
238 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE6;
239 END IF;
240
241 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute7,p_old_Header_Scredit_rec.attribute7)
242 THEN
243 l_index := l_index + 1;
244 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE7;
245 END IF;
246
247 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute8,p_old_Header_Scredit_rec.attribute8)
248 THEN
249 l_index := l_index + 1;
250 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE8;
251 END IF;
252
253 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute9,p_old_Header_Scredit_rec.attribute9)
254 THEN
255 l_index := l_index + 1;
256 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE9;
257 END IF;
258
259 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.context,p_old_Header_Scredit_rec.context)
260 THEN
261 l_index := l_index + 1;
262 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_CONTEXT;
263 END IF;
264
265 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.created_by,p_old_Header_Scredit_rec.created_by)
266 THEN
267 l_index := l_index + 1;
268 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_CREATED_BY;
269 END IF;
270
271 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.creation_date,p_old_Header_Scredit_rec.creation_date)
272 THEN
273 l_index := l_index + 1;
274 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_CREATION_DATE;
275 END IF;
276
277 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.dw_update_advice_flag,p_old_Header_Scredit_rec.dw_update_advice_flag)
278 THEN
279 l_index := l_index + 1;
280 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_DW_UPDATE_ADVICE;
281 END IF;
282
283 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.header_id,p_old_Header_Scredit_rec.header_id)
284 THEN
285 l_index := l_index + 1;
286 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_HEADER;
287 END IF;
288
289 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.last_updated_by,p_old_Header_Scredit_rec.last_updated_by)
290 THEN
291 l_index := l_index + 1;
292 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_LAST_UPDATED_BY;
293 END IF;
294
295 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.last_update_date,p_old_Header_Scredit_rec.last_update_date)
296 THEN
297 l_index := l_index + 1;
298 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_LAST_UPDATE_DATE;
299 END IF;
300
301 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.last_update_login,p_old_Header_Scredit_rec.last_update_login)
302 THEN
303 l_index := l_index + 1;
304 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_LAST_UPDATE_LOGIN;
305 END IF;
306
307 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.line_id,p_old_Header_Scredit_rec.line_id)
308 THEN
309 l_index := l_index + 1;
310 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_LINE;
311 END IF;
312
313 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.percent,p_old_Header_Scredit_rec.percent)
314 THEN
315 l_index := l_index + 1;
316 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_PERCENT;
317 END IF;
318
319
320 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.salesrep_id,p_old_Header_Scredit_rec.salesrep_id)
321 THEN
322 l_index := l_index + 1;
323 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_SALESREP;
324 END IF;
325 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.sales_credit_type_id,p_old_Header_Scredit_rec.sales_credit_type_id)
326 THEN
327 l_index := l_index + 1;
328 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_sales_credit_type;
329 END IF;
330
331 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.sales_credit_id,p_old_Header_Scredit_rec.sales_credit_id)
332 THEN
333 l_index := l_index + 1;
334 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_SALES_CREDIT;
335 END IF;
336
337 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.wh_update_date,p_old_Header_Scredit_rec.wh_update_date)
338 THEN
339 l_index := l_index + 1;
340 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_WH_UPDATE_DATE;
341 END IF;
342
343 ELSIF p_attr_id = G_ATTRIBUTE1 THEN
344 l_index := l_index + 1;
345 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE1;
346 ELSIF p_attr_id = G_ATTRIBUTE10 THEN
347 l_index := l_index + 1;
348 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE10;
349 ELSIF p_attr_id = G_ATTRIBUTE11 THEN
350 l_index := l_index + 1;
351 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE11;
352 ELSIF p_attr_id = G_ATTRIBUTE12 THEN
353 l_index := l_index + 1;
354 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE12;
355 ELSIF p_attr_id = G_ATTRIBUTE13 THEN
356 l_index := l_index + 1;
357 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE13;
358 ELSIF p_attr_id = G_ATTRIBUTE14 THEN
359 l_index := l_index + 1;
360 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE14;
361 ELSIF p_attr_id = G_ATTRIBUTE15 THEN
362 l_index := l_index + 1;
363 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE15;
364 ELSIF p_attr_id = G_ATTRIBUTE2 THEN
365 l_index := l_index + 1;
366 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE2;
367 ELSIF p_attr_id = G_ATTRIBUTE3 THEN
368 l_index := l_index + 1;
369 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE3;
370 ELSIF p_attr_id = G_ATTRIBUTE4 THEN
371 l_index := l_index + 1;
372 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE4;
373 ELSIF p_attr_id = G_ATTRIBUTE5 THEN
374 l_index := l_index + 1;
375 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE5;
376 ELSIF p_attr_id = G_ATTRIBUTE6 THEN
377 l_index := l_index + 1;
378 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE6;
379 ELSIF p_attr_id = G_ATTRIBUTE7 THEN
380 l_index := l_index + 1;
381 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE7;
382 ELSIF p_attr_id = G_ATTRIBUTE8 THEN
383 l_index := l_index + 1;
384 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE8;
385 ELSIF p_attr_id = G_ATTRIBUTE9 THEN
386 l_index := l_index + 1;
387 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE9;
388 ELSIF p_attr_id = G_CONTEXT THEN
389 l_index := l_index + 1;
390 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_CONTEXT;
391 ELSIF p_attr_id = G_CREATED_BY THEN
392 l_index := l_index + 1;
393 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_CREATED_BY;
394 ELSIF p_attr_id = G_CREATION_DATE THEN
395 l_index := l_index + 1;
396 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_CREATION_DATE;
397 ELSIF p_attr_id = G_DW_UPDATE_ADVICE THEN
398 l_index := l_index + 1;
399 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_DW_UPDATE_ADVICE;
400 ELSIF p_attr_id = G_HEADER THEN
401 l_index := l_index + 1;
402 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_HEADER;
403 ELSIF p_attr_id = G_LAST_UPDATED_BY THEN
404 l_index := l_index + 1;
405 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_LAST_UPDATED_BY;
406 ELSIF p_attr_id = G_LAST_UPDATE_DATE THEN
407 l_index := l_index + 1;
408 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_LAST_UPDATE_DATE;
409 ELSIF p_attr_id = G_LAST_UPDATE_LOGIN THEN
410 l_index := l_index + 1;
411 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_LAST_UPDATE_LOGIN;
412 ELSIF p_attr_id = G_LINE THEN
413 l_index := l_index + 1;
414 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_LINE;
415 ELSIF p_attr_id = G_PERCENT THEN
416 l_index := l_index + 1;
417 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_PERCENT;
418 ELSIF p_attr_id = G_SALESREP THEN
419 l_index := l_index + 1;
420 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_SALESREP;
421 ELSIF p_attr_id = G_sales_credit_type THEN
422 l_index := l_index + 1;
423 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_sales_credit_type;
424 ELSIF p_attr_id = G_SALES_CREDIT THEN
425 l_index := l_index + 1;
426 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_SALES_CREDIT;
427 ELSIF p_attr_id = G_WH_UPDATE_DATE THEN
428 l_index := l_index + 1;
429 l_src_attr_tbl(l_index) := OE_HEADER_SCREDIT_UTIL.G_WH_UPDATE_DATE;
430 END IF;
431
432 If l_src_attr_tbl.COUNT <> 0 THEN
433
434 OE_Dependencies.Mark_Dependent
435 (p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_SCREDIT,
436 p_source_attr_tbl => l_src_attr_tbl,
437 p_dep_attr_tbl => l_dep_attr_tbl);
438
439 FOR I IN 1..l_dep_attr_tbl.COUNT LOOP
440 IF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE1 THEN
441 p_x_Header_Scredit_rec.ATTRIBUTE1 := FND_API.G_MISS_NUM;
442 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE10 THEN
443 p_x_Header_Scredit_rec.ATTRIBUTE10 := FND_API.G_MISS_CHAR;
444 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE11 THEN
445 p_x_Header_Scredit_rec.ATTRIBUTE11 := FND_API.G_MISS_CHAR;
446 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE12 THEN
447 p_x_Header_Scredit_rec.ATTRIBUTE12 := FND_API.G_MISS_CHAR;
448 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE13 THEN
449 p_x_Header_Scredit_rec.ATTRIBUTE13 := FND_API.G_MISS_CHAR;
450 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE14 THEN
451 p_x_Header_Scredit_rec.ATTRIBUTE14 := FND_API.G_MISS_CHAR;
452 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE15 THEN
453 p_x_Header_Scredit_rec.ATTRIBUTE15 := FND_API.G_MISS_CHAR;
454 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE2 THEN
455 p_x_Header_Scredit_rec.ATTRIBUTE2 := FND_API.G_MISS_CHAR;
456 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE3 THEN
457 p_x_Header_Scredit_rec.ATTRIBUTE3 := FND_API.G_MISS_CHAR;
458 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE4 THEN
459 p_x_Header_Scredit_rec.ATTRIBUTE4 := FND_API.G_MISS_CHAR;
460 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE5 THEN
461 p_x_Header_Scredit_rec.ATTRIBUTE5 := FND_API.G_MISS_CHAR;
462 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE6 THEN
463 p_x_Header_Scredit_rec.ATTRIBUTE6 := FND_API.G_MISS_CHAR;
464 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE7 THEN
465 p_x_Header_Scredit_rec.ATTRIBUTE7 := FND_API.G_MISS_CHAR;
466 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE8 THEN
467 p_x_Header_Scredit_rec.ATTRIBUTE8 := FND_API.G_MISS_CHAR;
468 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_ATTRIBUTE9 THEN
469 p_x_Header_Scredit_rec.ATTRIBUTE9 := FND_API.G_MISS_CHAR;
470 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_CONTEXT THEN
471 p_x_Header_Scredit_rec.CONTEXT := FND_API.G_MISS_CHAR;
472 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_CREATED_BY THEN
473 p_x_Header_Scredit_rec.CREATED_BY := FND_API.G_MISS_NUM;
474 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_CREATION_DATE THEN
475 p_x_Header_Scredit_rec.CREATION_DATE := FND_API.G_MISS_DATE;
476 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_DW_UPDATE_ADVICE THEN
477 p_x_Header_Scredit_rec.DW_UPDATE_ADVICE_FLAG := FND_API.G_MISS_CHAR;
478 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_HEADER THEN
479 p_x_Header_Scredit_rec.HEADER_ID := FND_API.G_MISS_NUM;
480 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_LAST_UPDATED_BY THEN
481 p_x_Header_Scredit_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
482 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_LAST_UPDATE_DATE THEN
483 p_x_Header_Scredit_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
484 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_LAST_UPDATE_LOGIN THEN
485 p_x_Header_Scredit_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
486 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_LINE THEN
487 p_x_Header_Scredit_rec.LINE_ID := FND_API.G_MISS_NUM;
488 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_PERCENT THEN
489 p_x_Header_Scredit_rec.PERCENT := FND_API.G_MISS_NUM;
490 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_SALESREP THEN
491 p_x_Header_Scredit_rec.SALESREP_ID := FND_API.G_MISS_NUM;
492 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_sales_credit_type THEN
493 p_x_Header_Scredit_rec.sales_credit_type_id := FND_API.G_MISS_NUM;
494 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_SALES_CREDIT THEN
495 p_x_Header_Scredit_rec.SALES_CREDIT_ID := FND_API.G_MISS_NUM;
496 ELSIF l_dep_attr_tbl(I) = OE_HEADER_SCREDIT_UTIL.G_WH_UPDATE_DATE THEN
497 p_x_Header_Scredit_rec.WH_UPDATE_DATE := FND_API.G_MISS_DATE;
498 END IF;
499 END LOOP;
500 END IF;
501 END Clear_Dependent_Attr;
502
503 PROCEDURE Clear_Dependent_Attr
504 ( p_attr_id IN NUMBER := FND_API.G_MISS_NUM
505 , p_x_Header_Scredit_rec IN OUT NOCOPY OE_Order_PUB.Header_Scredit_Rec_Type
506 , p_old_Header_Scredit_rec IN OE_Order_PUB.Header_Scredit_Rec_Type :=
507 OE_Order_PUB.G_MISS_HEADER_SCREDIT_REC
508 )
509 IS
510 l_Header_Scredit_rec OE_AK_HEADER_SCREDITS_V%ROWTYPE;
511 l_old_Header_Scredit_rec OE_AK_HEADER_SCREDITS_V%ROWTYPE;
512 --
513 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
514 --
515 BEGIN
516
517 API_Rec_To_Rowtype_Rec(p_x_Header_Scredit_rec, l_Header_Scredit_rec);
518 API_Rec_To_Rowtype_Rec(p_old_Header_Scredit_rec, l_old_Header_Scredit_rec);
519
520 Clear_Dependent_Attr
521 (p_attr_id => p_attr_id
522 ,p_x_Header_Scredit_rec => l_Header_Scredit_rec
523 ,p_old_Header_Scredit_rec => l_old_Header_Scredit_rec
524 );
525
526 Rowtype_Rec_To_API_Rec(l_Header_Scredit_rec,p_x_Header_Scredit_rec);
527
528 END Clear_Dependent_Attr;
529
530 -- Procedure Apply_Attribute_Changes
531
532 PROCEDURE Apply_Attribute_Changes
533 ( p_x_Header_Scredit_rec IN OUT NOCOPY OE_Order_PUB.Header_Scredit_Rec_Type
534 , p_old_Header_Scredit_rec IN OE_Order_PUB.Header_Scredit_Rec_Type :=
535 OE_Order_PUB.G_MISS_HEADER_SCREDIT_REC
536 )
537 IS
538 l_return_status Varchar2(10);
539 --SG{
540 l_sg_date DATE;
541 l_out Varchar2(240);
542 l_status Varchar2(30);
543 --SG}
544 --
545 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
546 --
547 BEGIN
548
549 -- Load out record
550
551
552 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute1,p_old_Header_Scredit_rec.attribute1)
553 THEN
554 NULL;
555 END IF;
556
557 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute10,p_old_Header_Scredit_rec.attribute10)
558 THEN
559 NULL;
560 END IF;
561
562 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute11,p_old_Header_Scredit_rec.attribute11)
563 THEN
564 NULL;
565 END IF;
566
567 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute12,p_old_Header_Scredit_rec.attribute12)
568 THEN
569 NULL;
570 END IF;
571
572 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute13,p_old_Header_Scredit_rec.attribute13)
573 THEN
574 NULL;
575 END IF;
576
577 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute14,p_old_Header_Scredit_rec.attribute14)
578 THEN
579 NULL;
580 END IF;
581
582 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute15,p_old_Header_Scredit_rec.attribute15)
583 THEN
584 NULL;
585 END IF;
586
587 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute2,p_old_Header_Scredit_rec.attribute2)
588 THEN
589 NULL;
590 END IF;
591
592 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute3,p_old_Header_Scredit_rec.attribute3)
593 THEN
594 NULL;
595 END IF;
596
597 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute4,p_old_Header_Scredit_rec.attribute4)
598 THEN
599 NULL;
600 END IF;
601
602 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute5,p_old_Header_Scredit_rec.attribute5)
603 THEN
604 NULL;
605 END IF;
606
607 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute6,p_old_Header_Scredit_rec.attribute6)
608 THEN
609 NULL;
610 END IF;
611
612 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute7,p_old_Header_Scredit_rec.attribute7)
613 THEN
614 NULL;
615 END IF;
616
617 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute8,p_old_Header_Scredit_rec.attribute8)
618 THEN
619 NULL;
620 END IF;
621
622 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.attribute9,p_old_Header_Scredit_rec.attribute9)
623 THEN
624 NULL;
625 END IF;
626
627 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.context,p_old_Header_Scredit_rec.context)
628 THEN
629 NULL;
630 END IF;
631
632 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.created_by,p_old_Header_Scredit_rec.created_by)
633 THEN
634 NULL;
635 END IF;
636
637 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.creation_date,p_old_Header_Scredit_rec.creation_date)
638 THEN
639 NULL;
640 END IF;
641
642 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.dw_update_advice_flag,p_old_Header_Scredit_rec.dw_update_advice_flag)
643 THEN
644 NULL;
645 END IF;
646
647 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.header_id,p_old_Header_Scredit_rec.header_id)
648 THEN
649 NULL;
650 END IF;
651
652 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.last_updated_by,p_old_Header_Scredit_rec.last_updated_by)
653 THEN
654 NULL;
655 END IF;
656
657 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.last_update_date,p_old_Header_Scredit_rec.last_update_date)
658 THEN
659 NULL;
660 END IF;
661
662 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.last_update_login,p_old_Header_Scredit_rec.last_update_login)
663 THEN
664 NULL;
665 END IF;
666
667 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.line_id,p_old_Header_Scredit_rec.line_id)
668 THEN
669 NULL;
670 END IF;
671
672 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.percent,p_old_Header_Scredit_rec.percent)
673 THEN
674 -- Add delayed request to validate quota percent sums up to 100
675 OE_Delayed_Requests_Pvt.Log_Request
676 (p_entity_code=>OE_GLOBALS.G_ENTITY_Header_Scredit
677 ,p_entity_id=>p_x_Header_Scredit_rec.sales_credit_id
678 , p_requesting_entity_code=>OE_GLOBALS.G_ENTITY_Header_Scredit
679 ,p_requesting_entity_id=>p_x_Header_Scredit_rec.sales_credit_id
680 ,p_request_type=>OE_GLOBALS.G_CHECK_HSC_QUOTA_TOTAL
681 ,p_param1 => to_char(p_x_Header_Scredit_rec.header_id)
682 ,x_return_status =>l_return_status);
683 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
685 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
686 RAISE FND_API.G_EXC_ERROR;
687 END IF;
688 END IF;
689
690 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.sales_credit_type_id,p_old_Header_Scredit_rec.sales_credit_type_id)
691 THEN
692 -- Add delayed request to validate quota percent sums up to 100
693 OE_Delayed_Requests_Pvt.Log_Request
694 (p_entity_code=>OE_GLOBALS.G_ENTITY_Header_Scredit
695 ,p_entity_id=>p_x_Header_Scredit_rec.sales_credit_id
696 , p_requesting_entity_code=>OE_GLOBALS.G_ENTITY_Header_Scredit
697 ,p_requesting_entity_id=>p_x_Header_Scredit_rec.sales_credit_id
698 ,p_request_type=>OE_GLOBALS.G_CHECK_HSC_QUOTA_TOTAL
699 ,p_param1 => to_char(p_x_Header_Scredit_rec.header_id)
700 ,x_return_status =>l_return_status);
701 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
703 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
704 RAISE FND_API.G_EXC_ERROR;
705 END IF;
706 END IF;
707
708 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.salesrep_id,p_old_Header_Scredit_rec.salesrep_id)
709 THEN
710 NULL;
711 --SG{
712
713
714 IF OE_ORDER_CACHE.G_HEADER_REC.header_id IS NULL THEN
715 --header not available in cache, load info to cache
716 IF p_x_Header_Scredit_rec.Header_Id IS NOT NULL THEN
717 OE_ORDER_CACHE.Load_Order_Header(p_x_Header_Scredit_rec.Header_Id);
718 ELSE
719 oe_debug_pub.add(' Warning:Null header_id for header sales credits');
720 END IF;
721 END IF;
722
723 IF OE_ORDER_CACHE.G_HEADER_REC.booked_flag = 'Y' THEN
724 l_sg_date := OE_ORDER_CACHE.G_HEADER_REC.booked_date;
725 ELSE
726 l_sg_date := OE_ORDER_CACHE.G_HEADER_REC.ordered_date;
727 END IF;
728
729 --5620190: Added the IF condition, so that Get_Sales_Group id is called only if
730 --the record doesn't have a sales group id, which could have been populated
731 --when copying order.
732 oe_debug_pub.add('Before get_sales_group:'||p_x_Header_Scredit_rec.sales_group_id);
733 IF p_x_Header_Scredit_rec.sales_group_id IS NULL THEN
734 Get_Sales_Group(p_date => l_sg_date,
735 p_sales_rep_id =>p_x_Header_Scredit_rec.salesrep_id,
736 x_sales_group_id=>p_x_Header_Scredit_rec.sales_group_id,
737 --x_sales_group =>l_out,
738 x_return_status =>l_status);
739 END IF;
740 --SG}
741 END IF;
742
743 --SG{
744 IF nvl(p_x_Header_Scredit_rec.sales_group_updated_flag,'N') <> 'Y'
745 AND nvl(p_x_Header_Scredit_rec.salesrep_id,FND_API.G_MISS_NUM)<>FND_API.G_MISS_NUM
746 THEN
747
748 IF OE_ORDER_CACHE.G_HEADER_REC.header_id IS NULL THEN
749 --header not available in cache, load info to cache
750 IF p_x_Header_Scredit_rec.Header_Id IS NOT NULL THEN
751 OE_ORDER_CACHE.Load_Order_Header(p_x_Header_Scredit_rec.Header_Id);
752 ELSE
753 oe_debug_pub.add(' Warning:Null header_id for header sales credits');
754 END IF;
755 END IF;
756
757 IF OE_ORDER_CACHE.G_HEADER_REC.booked_flag = 'Y' THEN
758 l_sg_date := OE_ORDER_CACHE.G_HEADER_REC.booked_date;
759 ELSE
760 l_sg_date := OE_ORDER_CACHE.G_HEADER_REC.ordered_date;
761 END IF;
762 oe_debug_pub.add('Before getting sales group');
763 Get_Sales_Group(p_date => l_sg_date,
764 p_sales_rep_id =>p_x_Header_Scredit_rec.salesrep_id,
765 x_sales_group_id=>p_x_Header_Scredit_rec.sales_group_id,
766 --x_sales_group =>l_out,
767 x_return_status =>l_status);
768 END IF;
769 --SG}
770
771
772 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.sales_credit_type_id,p_old_Header_Scredit_rec.sales_credit_type_id)
773 THEN
774 NULL;
775 END IF;
776
777 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.sales_credit_id,p_old_Header_Scredit_rec.sales_credit_id)
778 THEN
779 NULL;
780 END IF;
781
782 IF NOT OE_GLOBALS.Equal(p_x_Header_Scredit_rec.wh_update_date,p_old_Header_Scredit_rec.wh_update_date)
783 THEN
784 NULL;
785 END IF;
786
787 END Apply_Attribute_Changes;
788
789 -- Procedure Complete_Record
790
791 PROCEDURE Complete_Record
792 ( p_x_Header_Scredit_rec IN OUT NOCOPY OE_Order_PUB.Header_Scredit_Rec_Type
793 , p_old_Header_Scredit_rec IN OE_Order_PUB.Header_Scredit_Rec_Type
794 )
795 IS
796 --
797 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
798 --
799 BEGIN
800
801 IF p_x_Header_Scredit_rec.attribute1 = FND_API.G_MISS_CHAR THEN
802 p_x_Header_Scredit_rec.attribute1 := p_old_Header_Scredit_rec.attribute1;
803 END IF;
804
805 IF p_x_Header_Scredit_rec.attribute10 = FND_API.G_MISS_CHAR THEN
806 p_x_Header_Scredit_rec.attribute10 := p_old_Header_Scredit_rec.attribute10;
807 END IF;
808
809 IF p_x_Header_Scredit_rec.attribute11 = FND_API.G_MISS_CHAR THEN
810 p_x_Header_Scredit_rec.attribute11 := p_old_Header_Scredit_rec.attribute11;
811 END IF;
812
813 IF p_x_Header_Scredit_rec.attribute12 = FND_API.G_MISS_CHAR THEN
814 p_x_Header_Scredit_rec.attribute12 := p_old_Header_Scredit_rec.attribute12;
815 END IF;
816
817 IF p_x_Header_Scredit_rec.attribute13 = FND_API.G_MISS_CHAR THEN
818 p_x_Header_Scredit_rec.attribute13 := p_old_Header_Scredit_rec.attribute13;
819 END IF;
820
821 IF p_x_Header_Scredit_rec.attribute14 = FND_API.G_MISS_CHAR THEN
822 p_x_Header_Scredit_rec.attribute14 := p_old_Header_Scredit_rec.attribute14;
823 END IF;
824
825 IF p_x_Header_Scredit_rec.attribute15 = FND_API.G_MISS_CHAR THEN
826 p_x_Header_Scredit_rec.attribute15 := p_old_Header_Scredit_rec.attribute15;
827 END IF;
828
829 IF p_x_Header_Scredit_rec.attribute2 = FND_API.G_MISS_CHAR THEN
830 p_x_Header_Scredit_rec.attribute2 := p_old_Header_Scredit_rec.attribute2;
831 END IF;
832
833 IF p_x_Header_Scredit_rec.attribute3 = FND_API.G_MISS_CHAR THEN
834 p_x_Header_Scredit_rec.attribute3 := p_old_Header_Scredit_rec.attribute3;
835 END IF;
836
837 IF p_x_Header_Scredit_rec.attribute4 = FND_API.G_MISS_CHAR THEN
838 p_x_Header_Scredit_rec.attribute4 := p_old_Header_Scredit_rec.attribute4;
839 END IF;
840
841 IF p_x_Header_Scredit_rec.attribute5 = FND_API.G_MISS_CHAR THEN
842 p_x_Header_Scredit_rec.attribute5 := p_old_Header_Scredit_rec.attribute5;
843 END IF;
844
845 IF p_x_Header_Scredit_rec.attribute6 = FND_API.G_MISS_CHAR THEN
846 p_x_Header_Scredit_rec.attribute6 := p_old_Header_Scredit_rec.attribute6;
847 END IF;
848
849 IF p_x_Header_Scredit_rec.attribute7 = FND_API.G_MISS_CHAR THEN
850 p_x_Header_Scredit_rec.attribute7 := p_old_Header_Scredit_rec.attribute7;
851 END IF;
852
853 IF p_x_Header_Scredit_rec.attribute8 = FND_API.G_MISS_CHAR THEN
854 p_x_Header_Scredit_rec.attribute8 := p_old_Header_Scredit_rec.attribute8;
855 END IF;
856
857 IF p_x_Header_Scredit_rec.attribute9 = FND_API.G_MISS_CHAR THEN
858 p_x_Header_Scredit_rec.attribute9 := p_old_Header_Scredit_rec.attribute9;
859 END IF;
860
861 IF p_x_Header_Scredit_rec.context = FND_API.G_MISS_CHAR THEN
862 p_x_Header_Scredit_rec.context := p_old_Header_Scredit_rec.context;
863 END IF;
864
865 IF p_x_Header_Scredit_rec.created_by = FND_API.G_MISS_NUM THEN
866 p_x_Header_Scredit_rec.created_by := p_old_Header_Scredit_rec.created_by;
867 END IF;
868
869 IF p_x_Header_Scredit_rec.creation_date = FND_API.G_MISS_DATE THEN
870 p_x_Header_Scredit_rec.creation_date := p_old_Header_Scredit_rec.creation_date;
871 END IF;
872
873 IF p_x_Header_Scredit_rec.dw_update_advice_flag = FND_API.G_MISS_CHAR THEN
874 p_x_Header_Scredit_rec.dw_update_advice_flag := p_old_Header_Scredit_rec.dw_update_advice_flag;
875 END IF;
876
877 IF p_x_Header_Scredit_rec.header_id = FND_API.G_MISS_NUM THEN
878 p_x_Header_Scredit_rec.header_id := p_old_Header_Scredit_rec.header_id;
879 END IF;
880
881 IF p_x_Header_Scredit_rec.last_updated_by = FND_API.G_MISS_NUM THEN
882 p_x_Header_Scredit_rec.last_updated_by := p_old_Header_Scredit_rec.last_updated_by;
883 END IF;
884
885 IF p_x_Header_Scredit_rec.last_update_date = FND_API.G_MISS_DATE THEN
886 p_x_Header_Scredit_rec.last_update_date := p_old_Header_Scredit_rec.last_update_date;
887 END IF;
888
889 IF p_x_Header_Scredit_rec.last_update_login = FND_API.G_MISS_NUM THEN
890 p_x_Header_Scredit_rec.last_update_login := p_old_Header_Scredit_rec.last_update_login;
891 END IF;
892
893 IF p_x_Header_Scredit_rec.line_id = FND_API.G_MISS_NUM THEN
894 p_x_Header_Scredit_rec.line_id := p_old_Header_Scredit_rec.line_id;
895 END IF;
896
897 IF p_x_Header_Scredit_rec.percent = FND_API.G_MISS_NUM THEN
898 p_x_Header_Scredit_rec.percent := p_old_Header_Scredit_rec.percent;
899 END IF;
900
901
902 IF p_x_Header_Scredit_rec.salesrep_id = FND_API.G_MISS_NUM THEN
903 p_x_Header_Scredit_rec.salesrep_id := p_old_Header_Scredit_rec.salesrep_id;
904 END IF;
905 IF p_x_Header_Scredit_rec.sales_credit_type_id = FND_API.G_MISS_NUM THEN
906 p_x_Header_Scredit_rec.sales_credit_type_id := p_old_Header_Scredit_rec.sales_credit_type_id;
907 END IF;
908
909 IF p_x_Header_Scredit_rec.sales_credit_id = FND_API.G_MISS_NUM THEN
910 p_x_Header_Scredit_rec.sales_credit_id := p_old_Header_Scredit_rec.sales_credit_id;
911 END IF;
912
913 IF p_x_Header_Scredit_rec.wh_update_date = FND_API.G_MISS_DATE THEN
914 p_x_Header_Scredit_rec.wh_update_date := p_old_Header_Scredit_rec.wh_update_date;
915 END IF;
916
917
918
919 END Complete_Record;
920
921 -- Procedure Convert_Miss_To_Null
922
923 PROCEDURE Convert_Miss_To_Null
924 ( p_x_Header_Scredit_rec IN OUT NOCOPY OE_Order_PUB.Header_Scredit_Rec_Type
925 )
926 IS
927
928 --
929 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
930 --
931 BEGIN
932
933 IF p_x_Header_Scredit_rec.attribute1 = FND_API.G_MISS_CHAR THEN
934 p_x_Header_Scredit_rec.attribute1 := NULL;
935 END IF;
936
937 IF p_x_Header_Scredit_rec.attribute10 = FND_API.G_MISS_CHAR THEN
938 p_x_Header_Scredit_rec.attribute10 := NULL;
939 END IF;
940
941 IF p_x_Header_Scredit_rec.attribute11 = FND_API.G_MISS_CHAR THEN
942 p_x_Header_Scredit_rec.attribute11 := NULL;
943 END IF;
944
945 IF p_x_Header_Scredit_rec.attribute12 = FND_API.G_MISS_CHAR THEN
946 p_x_Header_Scredit_rec.attribute12 := NULL;
947 END IF;
948
949 IF p_x_Header_Scredit_rec.attribute13 = FND_API.G_MISS_CHAR THEN
950 p_x_Header_Scredit_rec.attribute13 := NULL;
951 END IF;
952
953 IF p_x_Header_Scredit_rec.attribute14 = FND_API.G_MISS_CHAR THEN
954 p_x_Header_Scredit_rec.attribute14 := NULL;
955 END IF;
956
957 IF p_x_Header_Scredit_rec.attribute15 = FND_API.G_MISS_CHAR THEN
958 p_x_Header_Scredit_rec.attribute15 := NULL;
959 END IF;
960
961 IF p_x_Header_Scredit_rec.attribute2 = FND_API.G_MISS_CHAR THEN
962 p_x_Header_Scredit_rec.attribute2 := NULL;
963 END IF;
964
965 IF p_x_Header_Scredit_rec.attribute3 = FND_API.G_MISS_CHAR THEN
966 p_x_Header_Scredit_rec.attribute3 := NULL;
967 END IF;
968
969 IF p_x_Header_Scredit_rec.attribute4 = FND_API.G_MISS_CHAR THEN
970 p_x_Header_Scredit_rec.attribute4 := NULL;
971 END IF;
972
973 IF p_x_Header_Scredit_rec.attribute5 = FND_API.G_MISS_CHAR THEN
974 p_x_Header_Scredit_rec.attribute5 := NULL;
975 END IF;
976
977 IF p_x_Header_Scredit_rec.attribute6 = FND_API.G_MISS_CHAR THEN
978 p_x_Header_Scredit_rec.attribute6 := NULL;
979 END IF;
980
981 IF p_x_Header_Scredit_rec.attribute7 = FND_API.G_MISS_CHAR THEN
982 p_x_Header_Scredit_rec.attribute7 := NULL;
983 END IF;
984
985 IF p_x_Header_Scredit_rec.attribute8 = FND_API.G_MISS_CHAR THEN
986 p_x_Header_Scredit_rec.attribute8 := NULL;
987 END IF;
988
989 IF p_x_Header_Scredit_rec.attribute9 = FND_API.G_MISS_CHAR THEN
990 p_x_Header_Scredit_rec.attribute9 := NULL;
991 END IF;
992
993 IF p_x_Header_Scredit_rec.context = FND_API.G_MISS_CHAR THEN
994 p_x_Header_Scredit_rec.context := NULL;
995 END IF;
996
997 IF p_x_Header_Scredit_rec.created_by = FND_API.G_MISS_NUM THEN
998 p_x_Header_Scredit_rec.created_by := NULL;
999 END IF;
1000
1001 IF p_x_Header_Scredit_rec.creation_date = FND_API.G_MISS_DATE THEN
1002 p_x_Header_Scredit_rec.creation_date := NULL;
1003 END IF;
1004
1005 IF p_x_Header_Scredit_rec.dw_update_advice_flag = FND_API.G_MISS_CHAR THEN
1006 p_x_Header_Scredit_rec.dw_update_advice_flag := NULL;
1007 END IF;
1008
1009 IF p_x_Header_Scredit_rec.header_id = FND_API.G_MISS_NUM THEN
1010 p_x_Header_Scredit_rec.header_id := NULL;
1011 END IF;
1012
1013 IF p_x_Header_Scredit_rec.last_updated_by = FND_API.G_MISS_NUM THEN
1014 p_x_Header_Scredit_rec.last_updated_by := NULL;
1015 END IF;
1016
1017 IF p_x_Header_Scredit_rec.last_update_date = FND_API.G_MISS_DATE THEN
1018 p_x_Header_Scredit_rec.last_update_date := NULL;
1019 END IF;
1020
1021 IF p_x_Header_Scredit_rec.last_update_login = FND_API.G_MISS_NUM THEN
1022 p_x_Header_Scredit_rec.last_update_login := NULL;
1023 END IF;
1024
1025 IF p_x_Header_Scredit_rec.line_id = FND_API.G_MISS_NUM THEN
1026 p_x_Header_Scredit_rec.line_id := NULL;
1027 END IF;
1028
1029 IF p_x_Header_Scredit_rec.percent = FND_API.G_MISS_NUM THEN
1030 p_x_Header_Scredit_rec.percent := NULL;
1031 END IF;
1032
1033
1034 IF p_x_Header_Scredit_rec.salesrep_id = FND_API.G_MISS_NUM THEN
1035 p_x_Header_Scredit_rec.salesrep_id := NULL;
1036 END IF;
1037 IF p_x_Header_Scredit_rec.sales_credit_type_id = FND_API.G_MISS_NUM THEN
1038 p_x_Header_Scredit_rec.sales_credit_type_id := NULL;
1039 END IF;
1040
1041 IF p_x_Header_Scredit_rec.sales_credit_id = FND_API.G_MISS_NUM THEN
1042 p_x_Header_Scredit_rec.sales_credit_id := NULL;
1043 END IF;
1044
1045 IF p_x_Header_Scredit_rec.wh_update_date = FND_API.G_MISS_DATE THEN
1046 p_x_Header_Scredit_rec.wh_update_date := NULL;
1047 END IF;
1048
1049
1050
1051 END Convert_Miss_To_Null;
1052
1053 -- Procedure Update_Row
1054
1055 PROCEDURE Update_Row
1056 ( p_Header_Scredit_rec IN OUT NOCOPY OE_Order_PUB.Header_Scredit_Rec_Type
1057 )
1058 IS
1059 l_lock_control NUMBER;
1060 --added for notification framework
1061 l_index NUMBER;
1062 l_return_status VARCHAR2(1);
1063
1064
1065 --
1066 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1067 --
1068 BEGIN
1069
1070 SELECT lock_control
1071 INTO l_lock_control
1072 FROM OE_SALES_CREDITS
1073 WHERE sales_credit_id = p_Header_Scredit_rec.sales_credit_id;
1074
1075 l_lock_control := l_lock_control + 1;
1076
1077 --calling notification framework to update global picture
1078 --check code release level first. Notification framework is at Pack H level
1079 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
1080 IF l_debug_level > 0 THEN
1081 oe_debug_pub.add( 'JFC: SALES_CREDIT_ID' || P_HEADER_SCREDIT_REC.SALES_CREDIT_ID ) ;
1082 END IF;
1083 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
1084 p_Hdr_scr_rec =>p_header_scredit_rec,
1085 p_hdr_scr_id => p_header_scredit_rec.sales_credit_id,
1086 x_index => l_index,
1087 x_return_status => l_return_status);
1088 IF l_debug_level > 0 THEN
1089 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_SCREDIT_UTIL.UPDATE_ROW IS: ' || L_RETURN_STATUS ) ;
1090 END IF;
1091 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1092 IF l_debug_level > 0 THEN
1093 oe_debug_pub.add( 'EVENT NOTIFY - UNEXPECTED ERROR' ) ;
1094 END IF;
1095 IF l_debug_level > 0 THEN
1096 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDIT_UTIL.UPDATE_ROW' , 1 ) ;
1097 END IF;
1098 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1099 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1100 IF l_debug_level > 0 THEN
1101 oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_SCREDIT_UTIL.UPDATE_ROW' ) ;
1102 END IF;
1103 IF l_debug_level > 0 THEN
1104 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDIT_UTIL.UPDATE_ROW' , 1 ) ;
1105 END IF;
1106 RAISE FND_API.G_EXC_ERROR;
1107 END IF;
1108 END IF; /*code_release_level*/
1109 -- notification framework end
1110
1111 UPDATE OE_SALES_CREDITS
1112 SET ATTRIBUTE1 = p_Header_Scredit_rec.attribute1
1113 , ATTRIBUTE10 = p_Header_Scredit_rec.attribute10
1114 , ATTRIBUTE11 = p_Header_Scredit_rec.attribute11
1115 , ATTRIBUTE12 = p_Header_Scredit_rec.attribute12
1116 , ATTRIBUTE13 = p_Header_Scredit_rec.attribute13
1117 , ATTRIBUTE14 = p_Header_Scredit_rec.attribute14
1118 , ATTRIBUTE15 = p_Header_Scredit_rec.attribute15
1119 , ATTRIBUTE2 = p_Header_Scredit_rec.attribute2
1120 , ATTRIBUTE3 = p_Header_Scredit_rec.attribute3
1121 , ATTRIBUTE4 = p_Header_Scredit_rec.attribute4
1122 , ATTRIBUTE5 = p_Header_Scredit_rec.attribute5
1123 , ATTRIBUTE6 = p_Header_Scredit_rec.attribute6
1124 , ATTRIBUTE7 = p_Header_Scredit_rec.attribute7
1125 , ATTRIBUTE8 = p_Header_Scredit_rec.attribute8
1126 , ATTRIBUTE9 = p_Header_Scredit_rec.attribute9
1127 , CONTEXT = p_Header_Scredit_rec.context
1128 , CREATED_BY = p_Header_Scredit_rec.created_by
1129 , CREATION_DATE = p_Header_Scredit_rec.creation_date
1130 , DW_UPDATE_ADVICE_FLAG = p_Header_Scredit_rec.dw_update_advice_flag
1131 , HEADER_ID = p_Header_Scredit_rec.header_id
1132 , LAST_UPDATED_BY = p_Header_Scredit_rec.last_updated_by
1133 , LAST_UPDATE_DATE = p_Header_Scredit_rec.last_update_date
1134 , LAST_UPDATE_LOGIN = p_Header_Scredit_rec.last_update_login
1135 , LINE_ID = p_Header_Scredit_rec.line_id
1136 , PERCENT = p_Header_Scredit_rec.percent
1137 , SALESREP_ID = p_Header_Scredit_rec.salesrep_id
1138 , sales_credit_type_id = p_Header_Scredit_rec.sales_credit_type_id
1139 , SALES_CREDIT_ID = p_Header_Scredit_rec.sales_credit_id
1140 , WH_UPDATE_DATE = p_Header_Scredit_rec.wh_update_date
1141 , LOCK_CONTROL = l_lock_control
1142 --SG{
1143 , sales_group_id = p_Header_Scredit_rec.sales_group_id
1144 , sales_group_updated_flag = p_Header_Scredit_rec.sales_group_updated_flag
1145 --SG}
1146 WHERE SALES_CREDIT_ID = p_Header_Scredit_rec.sales_credit_id
1147 ;
1148
1149 p_Header_Scredit_rec.lock_control := l_lock_control;
1150
1151 EXCEPTION
1152
1153 WHEN OTHERS THEN
1154
1155 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1156 THEN
1157 oe_msg_pub.Add_Exc_Msg
1158 ( G_PKG_NAME
1159 , 'Update_Row'
1160 );
1161 END IF;
1162
1163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1164
1165 END Update_Row;
1166
1167 -- Procedure Insert_Row
1168
1169 PROCEDURE Insert_Row
1170 ( p_Header_Scredit_rec IN OUT NOCOPY OE_Order_PUB.Header_Scredit_Rec_Type
1171 )
1172 IS
1173 l_lock_control NUMBER:= 1;
1174 l_index NUMBER;
1175 l_return_status VARCHAR2(1);
1176 --
1177 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1178 --
1179 BEGIN
1180 IF l_debug_level > 0 THEN
1181 oe_debug_pub.add( 'ENTERING OE_HEADER_SCREDIT_UTIL.INSERT_ROW' , 1 ) ;
1182 END IF;
1183
1184 INSERT INTO OE_SALES_CREDITS
1185 ( ATTRIBUTE1
1186 , ATTRIBUTE10
1187 , ATTRIBUTE11
1188 , ATTRIBUTE12
1189 , ATTRIBUTE13
1190 , ATTRIBUTE14
1191 , ATTRIBUTE15
1192 , ATTRIBUTE2
1193 , ATTRIBUTE3
1194 , ATTRIBUTE4
1195 , ATTRIBUTE5
1196 , ATTRIBUTE6
1197 , ATTRIBUTE7
1198 , ATTRIBUTE8
1199 , ATTRIBUTE9
1200 , CONTEXT
1201 , CREATED_BY
1202 , CREATION_DATE
1203 , DW_UPDATE_ADVICE_FLAG
1204 , HEADER_ID
1205 , LAST_UPDATED_BY
1206 , LAST_UPDATE_DATE
1207 , LAST_UPDATE_LOGIN
1208 , LINE_ID
1209 , PERCENT
1210 , SALESREP_ID
1211 , sales_credit_type_id
1212 , SALES_CREDIT_ID
1213 , WH_UPDATE_DATE
1214 , ORIG_SYS_CREDIT_REF
1215 --SG{
1216 , sales_group_id
1217 , sales_group_updated_flag
1218 --SG}
1219 , LOCK_CONTROL
1220 )
1221 VALUES
1222 ( p_Header_Scredit_rec.attribute1
1223 , p_Header_Scredit_rec.attribute10
1224 , p_Header_Scredit_rec.attribute11
1225 , p_Header_Scredit_rec.attribute12
1226 , p_Header_Scredit_rec.attribute13
1227 , p_Header_Scredit_rec.attribute14
1228 , p_Header_Scredit_rec.attribute15
1229 , p_Header_Scredit_rec.attribute2
1230 , p_Header_Scredit_rec.attribute3
1231 , p_Header_Scredit_rec.attribute4
1232 , p_Header_Scredit_rec.attribute5
1233 , p_Header_Scredit_rec.attribute6
1234 , p_Header_Scredit_rec.attribute7
1235 , p_Header_Scredit_rec.attribute8
1236 , p_Header_Scredit_rec.attribute9
1237 , p_Header_Scredit_rec.context
1238 , p_Header_Scredit_rec.created_by
1239 , p_Header_Scredit_rec.creation_date
1240 , p_Header_Scredit_rec.dw_update_advice_flag
1241 , p_Header_Scredit_rec.header_id
1242 , p_Header_Scredit_rec.last_updated_by
1243 , p_Header_Scredit_rec.last_update_date
1244 , p_Header_Scredit_rec.last_update_login
1245 , p_Header_Scredit_rec.line_id
1246 , p_Header_Scredit_rec.percent
1247 , p_Header_Scredit_rec.salesrep_id
1248 , p_Header_Scredit_rec.sales_credit_type_id
1249 , p_Header_Scredit_rec.sales_credit_id
1250 , p_Header_Scredit_rec.wh_update_date
1251 , p_Header_Scredit_rec.orig_sys_credit_ref
1252 --SG{
1253 , p_header_scredit_rec.sales_group_id
1254 , p_header_scredit_rec.sales_group_updated_flag
1255 --SG}
1256 , l_lock_control
1257 );
1258
1259 p_Header_Scredit_rec.lock_control := l_lock_control;
1260
1261 --calling notification framework to update global picture
1262 --check code release level first. Notification framework is at Pack H level
1263 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
1264 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
1265 p_old_hdr_scr_rec => NULL,
1266 p_Hdr_scr_rec =>p_header_scredit_rec,
1267 p_hdr_scr_id => p_header_scredit_rec.sales_credit_id,
1268 x_index => l_index,
1269 x_return_status => l_return_status);
1270 IF l_debug_level > 0 THEN
1271 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_SCREDIT_UTIL.INSERT_ROW IS: ' || L_RETURN_STATUS ) ;
1272 END IF;
1273 IF l_debug_level > 0 THEN
1274 oe_debug_pub.add( 'RETURNED INDEX IS: ' || L_INDEX , 1 ) ;
1275 END IF;
1276
1277 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1278 IF l_debug_level > 0 THEN
1279 oe_debug_pub.add( 'EVENT NOTIFY - UNEXPECTED ERROR' ) ;
1280 END IF;
1281 IF l_debug_level > 0 THEN
1282 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDIT_UTIL.INSERT_ROW' , 1 ) ;
1283 END IF;
1284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1285 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1286 IF l_debug_level > 0 THEN
1287 oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_SCREDIT_UTIL.INSERT_ROW' ) ;
1288 END IF;
1289 IF l_debug_level > 0 THEN
1290 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDIT_UTIL.INSERT_ROW' , 1 ) ;
1291 END IF;
1292 RAISE FND_API.G_EXC_ERROR;
1293 END IF;
1294 END IF; /*code_release_level*/
1295 -- notification framework end
1296
1297 IF l_debug_level > 0 THEN
1298 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDIT_UTIL.INSERT_ROW' , 1 ) ;
1299 END IF;
1300
1301 EXCEPTION
1302
1303 WHEN OTHERS THEN
1304
1305 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1306 THEN
1307 oe_msg_pub.Add_Exc_Msg
1308 ( G_PKG_NAME
1309 , 'Insert_Row'
1310 );
1311 END IF;
1312
1313 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1314
1315 END Insert_Row;
1316
1317 -- Procedure Delete_Row
1318
1319 PROCEDURE Delete_Row
1320 ( p_sales_credit_id IN NUMBER :=
1321 FND_API.G_MISS_NUM
1322 , p_header_id IN NUMBER :=
1323 FND_API.G_MISS_NUM
1324 )
1325 IS
1326 l_return_status VARCHAR2(30);
1327 CURSOR sales_credit IS
1328 SELECT sales_credit_id
1329 FROM OE_SALES_CREDITS
1330 WHERE HEADER_ID = p_header_id;
1331 -- added for notification framework
1332 l_new_header_scredit_rec OE_Order_PUB.Header_Scredit_Rec_Type;
1333 l_index NUMBER;
1334 --
1335 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1336 --
1337 BEGIN
1338 IF l_debug_level > 0 THEN
1339 oe_debug_pub.add( 'ENTERING OE_HEADER_SCREDIT_UTIL.DELETE_ROW' , 1 ) ;
1340 END IF;
1341
1342 IF p_header_id <> FND_API.G_MISS_NUM
1343 THEN
1344 FOR l_scr IN sales_credit LOOP
1345
1346 --added notification framework
1347 --check code release level first. Notification framework is at Pack H level
1348 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
1349 /* Set the operation on the record so that globals are updated as well */
1350 l_new_header_scredit_rec.operation := OE_GLOBALS.G_OPR_DELETE;
1351 l_new_header_scredit_rec.sales_credit_id := l_scr.sales_credit_id;
1352
1353 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
1354 p_Hdr_scr_rec =>l_new_header_scredit_rec,
1355 p_hdr_scr_id => l_scr.sales_credit_id,
1356 x_index => l_index,
1357 x_return_status => l_return_status);
1358 IF l_debug_level > 0 THEN
1359 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_SCREDIT_UTIL.DELETE_ROW IS: ' || L_RETURN_STATUS ) ;
1360 END IF;
1361 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1362 IF l_debug_level > 0 THEN
1363 oe_debug_pub.add( 'EVENT NOTIFY - UNEXPECTED ERROR' ) ;
1364 END IF;
1365 IF l_debug_level > 0 THEN
1366 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDIT_UTIL.DELETE_ROW' , 1 ) ;
1367 END IF;
1368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1369 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1370 IF l_debug_level > 0 THEN
1371 oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_SCREDIT_UTIL.DELETE_ROW' ) ;
1372 END IF;
1373 IF l_debug_level > 0 THEN
1374 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDIT_UTIL.DELETE_ROW' , 1 ) ;
1375 END IF;
1376 RAISE FND_API.G_EXC_ERROR;
1377 END IF;
1378 END IF; /*code_release_level*/
1379 -- notification framework end
1380
1381 OE_Delayed_Requests_Pvt.Delete_Reqs_for_deleted_entity(
1382 p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_SCREDIT,
1383 p_entity_id => l_scr.sales_credit_id,
1384 x_return_status => l_return_status
1385 );
1386 END LOOP;
1387
1388 /* Start Audit Trail */
1389 DELETE FROM OE_SALES_CREDIT_HISTORY
1390 WHERE HEADER_ID = p_header_id;
1391 /* End Audit Trail */
1392
1393 DELETE FROM OE_SALES_CREDITS
1394 WHERE HEADER_ID = p_header_id;
1395 ELSE
1396 --added notification framework
1397 --check code release level first. Notification framework is at Pack H level
1398 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
1399 /* Set the operation on the record so that globals are updated as well */
1400 l_new_header_scredit_rec.operation := OE_GLOBALS.G_OPR_DELETE;
1401 l_new_header_scredit_rec.sales_credit_id := p_sales_credit_id;
1402 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
1403 p_Hdr_scr_rec =>l_new_header_scredit_rec,
1404 p_hdr_scr_id => p_sales_credit_id,
1405 x_index => l_index,
1406 x_return_status => l_return_status);
1407 IF l_debug_level > 0 THEN
1408 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_SCREDIT_UTIL.DELETE_ROW IS: ' || L_RETURN_STATUS ) ;
1409 END IF;
1410
1411 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1412 IF l_debug_level > 0 THEN
1413 oe_debug_pub.add( 'EVENT NOTIFY - UNEXPECTED ERROR' ) ;
1414 END IF;
1415 IF l_debug_level > 0 THEN
1416 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDIT_UTIL.DELETE_ROW' , 1 ) ;
1417 END IF;
1418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1419 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1420 IF l_debug_level > 0 THEN
1421 oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_SCREDIT_UTIL.DELETE_ROW' ) ;
1422 END IF;
1423 IF l_debug_level > 0 THEN
1424 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDIT_UTIL.DELETE_ROW' , 1 ) ;
1425 END IF;
1426 RAISE FND_API.G_EXC_ERROR;
1427 END IF;
1428 END IF; /*code_release_level*/
1429 -- notification framework end
1430
1431 OE_Delayed_Requests_Pvt.Delete_Reqs_for_deleted_entity(
1432 p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_SCREDIT,
1433 p_entity_id => p_sales_credit_id,
1434 x_return_status => l_return_status
1435 );
1436
1437 /* Start Audit Trail (modified for 11.5.10) */
1438 DELETE FROM OE_SALES_CREDIT_HISTORY
1439 WHERE SALES_CREDIT_ID = p_sales_credit_id
1440 AND NVL(AUDIT_FLAG, 'Y') = 'Y'
1441 AND NVL(VERSION_FLAG, 'N') = 'N'
1442 AND NVL(PHASE_CHANGE_FLAG, 'N') = 'N';
1443
1444 UPDATE OE_SALES_CREDIT_HISTORY
1445 SET AUDIT_FLAG = 'N'
1446 WHERE SALES_CREDIT_ID = p_sales_credit_id
1447 AND NVL(AUDIT_FLAG, 'Y') = 'Y'
1448 AND (NVL(VERSION_FLAG, 'N') = 'Y'
1449 OR NVL(PHASE_CHANGE_FLAG, 'N') = 'Y');
1450 /* End Audit Trail */
1451
1452 DELETE FROM OE_SALES_CREDITS
1453 WHERE SALES_CREDIT_ID = p_sales_credit_id
1454 ;
1455 END IF;
1456
1457 EXCEPTION
1458
1459 WHEN OTHERS THEN
1460
1461 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1462 THEN
1463 oe_msg_pub.Add_Exc_Msg
1464 ( G_PKG_NAME
1465 , 'Delete_Row'
1466 );
1467 END IF;
1468
1469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1470
1471 END Delete_Row;
1472
1473 -- Procedure Query_Row
1474 PROCEDURE Query_Row
1475 ( p_sales_credit_id IN NUMBER,
1476 x_Header_Scredit_rec IN OUT NOCOPY OE_Order_PUB.Header_Scredit_Rec_Type
1477 )
1478 IS
1479 x_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
1480 --
1481 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1482 --
1483 BEGIN
1484
1485 Query_Rows
1486 ( p_sales_credit_id => p_sales_credit_id,
1487 x_Header_Scredit_tbl => x_Header_Scredit_tbl
1488 );
1489 x_Header_Scredit_rec := x_Header_Scredit_tbl(1);
1490
1491 END Query_Row;
1492
1493
1494
1495 -- Procedure Query_Rows
1496
1497 --
1498
1499 Procedure Query_Rows
1500 ( p_sales_credit_id IN NUMBER :=
1501 FND_API.G_MISS_NUM
1502 , p_header_id IN NUMBER :=
1503 FND_API.G_MISS_NUM
1504 , x_header_scredit_tbl IN OUT NOCOPY OE_Order_PUB.Header_Scredit_Tbl_Type
1505 )
1506 IS
1507 l_count NUMBER;
1508
1509 CURSOR l_Header_Scredit_csr_s IS
1510 SELECT ATTRIBUTE1
1511 , ATTRIBUTE10
1512 , ATTRIBUTE11
1513 , ATTRIBUTE12
1514 , ATTRIBUTE13
1515 , ATTRIBUTE14
1516 , ATTRIBUTE15
1517 , ATTRIBUTE2
1518 , ATTRIBUTE3
1519 , ATTRIBUTE4
1520 , ATTRIBUTE5
1521 , ATTRIBUTE6
1522 , ATTRIBUTE7
1523 , ATTRIBUTE8
1524 , ATTRIBUTE9
1525 , CONTEXT
1526 , CREATED_BY
1527 , CREATION_DATE
1528 , DW_UPDATE_ADVICE_FLAG
1529 , HEADER_ID
1530 , LAST_UPDATED_BY
1531 , LAST_UPDATE_DATE
1532 , LAST_UPDATE_LOGIN
1533 , LINE_ID
1534 , PERCENT
1535 , SALESREP_ID
1536 , sales_credit_type_id
1537 , SALES_CREDIT_ID
1538 , WH_UPDATE_DATE
1539 --SG {
1540 , SALES_GROUP_ID
1541 , SALES_GROUP_UPDATED_FLAG
1542 --SG }
1543 , LOCK_CONTROL
1544 FROM OE_SALES_CREDITS
1545 WHERE SALES_CREDIT_ID = p_sales_credit_id;
1546
1547 CURSOR l_Header_Scredit_csr_h IS
1548 SELECT ATTRIBUTE1
1549 , ATTRIBUTE10
1550 , ATTRIBUTE11
1551 , ATTRIBUTE12
1552 , ATTRIBUTE13
1553 , ATTRIBUTE14
1554 , ATTRIBUTE15
1555 , ATTRIBUTE2
1556 , ATTRIBUTE3
1557 , ATTRIBUTE4
1558 , ATTRIBUTE5
1559 , ATTRIBUTE6
1560 , ATTRIBUTE7
1561 , ATTRIBUTE8
1562 , ATTRIBUTE9
1563 , CONTEXT
1564 , CREATED_BY
1565 , CREATION_DATE
1566 , DW_UPDATE_ADVICE_FLAG
1567 , HEADER_ID
1568 , LAST_UPDATED_BY
1569 , LAST_UPDATE_DATE
1570 , LAST_UPDATE_LOGIN
1571 , LINE_ID
1572 , PERCENT
1573 , SALESREP_ID
1574 , sales_credit_type_id
1575 , SALES_CREDIT_ID
1576 , WH_UPDATE_DATE
1577 --SG {
1578 , SALES_GROUP_ID
1579 , SALES_GROUP_UPDATED_FLAG
1580 --SG }
1581 , LOCK_CONTROL
1582 FROM OE_SALES_CREDITS
1583 WHERE HEADER_ID = p_header_id
1584 AND LINE_ID IS NULL;
1585
1586 l_implicit_rec l_header_scredit_csr_s%ROWTYPE;
1587 l_entity NUMBER;
1588
1589 --
1590 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1591 --
1592 BEGIN
1593
1594 IF
1595 (p_sales_credit_id IS NOT NULL
1596 AND
1597 p_sales_credit_id <> FND_API.G_MISS_NUM)
1598 AND
1599 (p_header_id IS NOT NULL
1600 AND
1601 p_header_id <> FND_API.G_MISS_NUM)
1602 THEN
1603 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1604 THEN
1605 oe_msg_pub.Add_Exc_Msg
1606 ( G_PKG_NAME
1607 , 'Query Rows'
1608 , 'Keys are mutually exclusive: sales_credit_id = '|| p_sales_credit_id || ', header_id = '|| p_header_id
1609 );
1610 END IF;
1611
1612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1613
1614 END IF;
1615
1616 IF nvl(p_sales_credit_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
1617 l_entity := 1;
1618 OPEN l_header_scredit_csr_s;
1619 ELSIF nvl(p_header_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
1620 l_entity := 2;
1621 OPEN l_header_scredit_csr_h;
1622 END IF;
1623
1624 -- Loop over fetched records
1625 l_count := 1;
1626 LOOP
1627 IF l_entity = 1 THEN
1628 FETCH l_header_scredit_csr_s INTO l_implicit_rec;
1629 EXIT WHEN l_header_scredit_csr_s%NOTFOUND;
1630 ELSIF l_entity = 2 THEN
1631 FETCH l_header_scredit_csr_h INTO l_implicit_rec;
1632 EXIT WHEN l_header_scredit_csr_h%NOTFOUND;
1633 ELSE
1634 EXIT;
1635 END IF;
1636
1637 x_header_scredit_tbl(l_count).attribute1 := l_implicit_rec.ATTRIBUTE1;
1638 x_header_scredit_tbl(l_count).attribute10 := l_implicit_rec.ATTRIBUTE10;
1639 x_header_scredit_tbl(l_count).attribute11 := l_implicit_rec.ATTRIBUTE11;
1640 x_header_scredit_tbl(l_count).attribute12 := l_implicit_rec.ATTRIBUTE12;
1641 x_header_scredit_tbl(l_count).attribute13 := l_implicit_rec.ATTRIBUTE13;
1642 x_header_scredit_tbl(l_count).attribute14 := l_implicit_rec.ATTRIBUTE14;
1643 x_header_scredit_tbl(l_count).attribute15 := l_implicit_rec.ATTRIBUTE15;
1644 x_header_scredit_tbl(l_count).attribute2 := l_implicit_rec.ATTRIBUTE2;
1645 x_header_scredit_tbl(l_count).attribute3 := l_implicit_rec.ATTRIBUTE3;
1646 x_header_scredit_tbl(l_count).attribute4 := l_implicit_rec.ATTRIBUTE4;
1647 x_header_scredit_tbl(l_count).attribute5 := l_implicit_rec.ATTRIBUTE5;
1648 x_header_scredit_tbl(l_count).attribute6 := l_implicit_rec.ATTRIBUTE6;
1649 x_header_scredit_tbl(l_count).attribute7 := l_implicit_rec.ATTRIBUTE7;
1650 x_header_scredit_tbl(l_count).attribute8 := l_implicit_rec.ATTRIBUTE8;
1651 x_header_scredit_tbl(l_count).attribute9 := l_implicit_rec.ATTRIBUTE9;
1652 x_header_scredit_tbl(l_count).context := l_implicit_rec.CONTEXT;
1653 x_header_scredit_tbl(l_count).created_by := l_implicit_rec.CREATED_BY;
1654 x_header_scredit_tbl(l_count).creation_date := l_implicit_rec.CREATION_DATE;
1655 x_header_scredit_tbl(l_count).dw_update_advice_flag := l_implicit_rec.DW_UPDATE_ADVICE_FLAG;
1656 x_header_scredit_tbl(l_count).header_id := l_implicit_rec.HEADER_ID;
1657 x_header_scredit_tbl(l_count).last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
1658 x_header_scredit_tbl(l_count).last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
1659 x_header_scredit_tbl(l_count).last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
1660 x_header_scredit_tbl(l_count).line_id := l_implicit_rec.LINE_ID;
1661 x_header_scredit_tbl(l_count).percent := l_implicit_rec.PERCENT;
1662 x_header_scredit_tbl(l_count).salesrep_id := l_implicit_rec.SALESREP_ID;
1663 x_header_scredit_tbl(l_count).sales_credit_type_id := l_implicit_rec.sales_credit_type_id;
1664 x_header_scredit_tbl(l_count).sales_credit_id := l_implicit_rec.SALES_CREDIT_ID;
1665 x_header_scredit_tbl(l_count).wh_update_date := l_implicit_rec.WH_UPDATE_DATE;
1666 --SG{
1667 x_header_scredit_tbl(l_count).sales_group_id := l_implicit_rec.sales_group_id;
1668 x_header_scredit_tbl(l_count).sales_group_updated_flag:=l_implicit_rec.sales_group_updated_flag;
1669 --SG}
1670 x_header_scredit_tbl(l_count).lock_control := l_implicit_rec.LOCK_CONTROL;
1671
1672 l_count := l_count + 1;
1673 END LOOP;
1674
1675 IF l_entity = 1 THEN
1676 CLOSE l_header_scredit_csr_s;
1677 ELSIF l_entity = 2 THEN
1678 CLOSE l_header_scredit_csr_h;
1679 END IF;
1680
1681 -- PK sent and no rows found
1682
1683 IF
1684 (p_sales_credit_id IS NOT NULL
1685 AND
1686 p_sales_credit_id <> FND_API.G_MISS_NUM)
1687 AND
1688 (x_Header_Scredit_tbl.COUNT = 0)
1689 THEN
1690 RAISE NO_DATA_FOUND;
1691 END IF;
1692
1693
1694 -- Return fetched table
1695
1696
1697 EXCEPTION
1698
1699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1700
1701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1702
1703 WHEN OTHERS THEN
1704
1705 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1706 THEN
1707 oe_msg_pub.Add_Exc_Msg
1708 ( G_PKG_NAME
1709 , 'Query_Rows'
1710 );
1711 END IF;
1712
1713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1714
1715 END Query_Rows;
1716
1717 -- Procedure lock_Row
1718 --
1719
1720 PROCEDURE Lock_Row
1721 ( x_return_status OUT NOCOPY VARCHAR2
1722 , p_x_Header_Scredit_rec IN OUT NOCOPY OE_Order_PUB.Header_Scredit_Rec_Type
1723 , p_sales_credit_id IN NUMBER
1724 := FND_API.G_MISS_NUM
1725 )
1726 IS
1727 l_sales_credit_id NUMBER;
1728 l_Header_Scredit_rec OE_Order_PUB.Header_Scredit_Rec_Type;
1729 l_lock_control NUMBER;
1730 --
1731 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1732 --
1733 BEGIN
1734
1735 IF l_debug_level > 0 THEN
1736 oe_debug_pub.add( 'ENTERING OE_HEADER_SCREDIT_UTIL.LOCK_ROW' , 1 ) ;
1737 END IF;
1738
1739 SAVEPOINT Lock_Row;
1740
1741 l_lock_control := NULL;
1742
1743 -- Retrieve the primary key.
1744 IF p_sales_credit_id <> FND_API.G_MISS_NUM THEN
1745 l_sales_credit_id := p_sales_credit_id;
1746 ELSE
1747 l_sales_credit_id := p_x_header_scredit_rec.sales_credit_id;
1748 l_lock_control := p_x_header_scredit_rec.lock_control;
1749 END IF;
1750
1751 SELECT sales_credit_id
1752 INTO l_sales_credit_id
1753 FROM oe_sales_credits
1754 WHERE sales_credit_id = l_sales_credit_id
1755 FOR UPDATE NOWAIT;
1756
1757 IF l_debug_level > 0 THEN
1758 oe_debug_pub.add( 'SELECTED FOR UPDATE' , 1 ) ;
1759 END IF;
1760
1761 OE_Header_Scredit_Util.Query_Row
1762 (p_sales_credit_id => l_sales_credit_id
1763 ,x_header_scredit_rec => p_x_header_scredit_rec );
1764
1765
1766 -- If lock_control is null / missing, then return the locked record.
1767
1768 IF l_lock_control is null OR
1769 l_lock_control <> FND_API.G_MISS_NUM THEN
1770
1771 -- Set return status
1772 x_return_status := FND_API.G_RET_STS_SUCCESS;
1773 p_x_header_scredit_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1774
1775 RETURN;
1776
1777 END IF;
1778
1779 -- Row locked. If the whole record is passed, then
1780 -- Compare IN attributes to DB attributes.
1781
1782 IF OE_GLOBALS.Equal(p_x_Header_Scredit_rec.lock_control,
1783 l_lock_control)
1784 THEN
1785
1786 -- Row has not changed. Set out parameter.
1787
1788 p_x_Header_Scredit_rec := l_Header_Scredit_rec;
1789
1790 -- Set return status
1791
1792 x_return_status := FND_API.G_RET_STS_SUCCESS;
1793 p_x_Header_Scredit_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1794
1795 ELSE
1796
1797 -- Row has changed by another user.
1798
1799 x_return_status := FND_API.G_RET_STS_ERROR;
1800 p_x_Header_Scredit_rec.return_status := FND_API.G_RET_STS_ERROR;
1801
1802 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1803 THEN
1804
1805 -- Release the lock
1806 ROLLBACK TO Lock_Row;
1807
1808 FND_MESSAGE.SET_NAME('ONT','OE_LOCK_ROW_CHANGED');
1809 oe_msg_pub.Add;
1810
1811 END IF;
1812
1813 END IF;
1814
1815 EXCEPTION
1816
1817 WHEN NO_DATA_FOUND THEN
1818
1819 x_return_status := FND_API.G_RET_STS_ERROR;
1820 p_x_Header_Scredit_rec.return_status := FND_API.G_RET_STS_ERROR;
1821
1822 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1823 THEN
1824
1825 FND_MESSAGE.SET_NAME('ONT','OE_LOCK_ROW_DELETED');
1826 oe_msg_pub.Add;
1827
1828 END IF;
1829 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1830
1831 x_return_status := FND_API.G_RET_STS_ERROR;
1832 p_x_Header_Scredit_rec.return_status := FND_API.G_RET_STS_ERROR;
1833
1834 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1835 THEN
1836
1837 FND_MESSAGE.SET_NAME('ONT','OE_LOCK_ROW_ALREADY_LOCKED');
1838 oe_msg_pub.Add;
1839
1840 END IF;
1841 WHEN OTHERS THEN
1842
1843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1844 p_x_Header_Scredit_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1845
1846 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1847 THEN
1848 oe_msg_pub.Add_Exc_Msg
1849 ( G_PKG_NAME
1850 , 'Lock_Row'
1851 );
1852 END IF;
1853
1854 END Lock_Row;
1855
1856 PROCEDURE Lock_Rows
1857 ( p_sales_credit_id IN NUMBER :=
1858 FND_API.G_MISS_NUM
1859 , p_header_id IN NUMBER :=
1860 FND_API.G_MISS_NUM
1861 , x_header_scredit_tbl OUT NOCOPY OE_Order_PUB.header_scredit_Tbl_Type
1862 , x_return_status OUT NOCOPY VARCHAR2
1863 )
1864 IS
1865 CURSOR lock_hdr_scredits(p_header_id NUMBER) IS
1866 SELECT sales_credit_id
1867 FROM oe_sales_credits
1868 WHERE header_id = p_header_id
1869 FOR UPDATE NOWAIT;
1870
1871 l_sales_credit_id NUMBER;
1872 --
1873 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1874 --
1875 BEGIN
1876
1877 IF l_debug_level > 0 THEN
1878 oe_debug_pub.add( 'ENTERING OE_HEADER_SCREDITS_UTIL.LOCK_ROWS' , 1 ) ;
1879 END IF;
1880
1881 IF (p_sales_credit_id IS NOT NULL AND
1882 p_sales_credit_id <> FND_API.G_MISS_NUM) AND
1883 (p_header_id IS NOT NULL AND
1884 p_header_id <> FND_API.G_MISS_NUM)
1885 THEN
1886 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1887 THEN
1888 OE_MSG_PUB.Add_Exc_Msg
1889 ( G_PKG_NAME
1890 , 'Lock Rows'
1891 , 'Keys are mutually exclusive: sales_credit_id = '||
1892 p_sales_credit_id || ', header_id = '|| p_header_id );
1893 END IF;
1894
1895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1896
1897 END IF;
1898
1899 IF p_sales_credit_id <> FND_API.G_MISS_NUM THEN
1900
1901 SELECT sales_credit_id
1902 INTO l_sales_credit_id
1903 FROM OE_SALES_CREDITS
1904 WHERE sales_credit_id = p_sales_credit_id
1905 FOR UPDATE NOWAIT;
1906
1907 END IF;
1908
1909 -- people should not pass in null header_id unnecessarily,
1910 -- if they already passed in line_id.
1911
1912 BEGIN
1913
1914 IF p_header_id <> FND_API.G_MISS_NUM THEN
1915
1916 SAVEPOINT LOCK_ROWS;
1917 OPEN lock_hdr_scredits(p_header_id);
1918
1919 LOOP
1920 FETCH lock_hdr_scredits INTO l_sales_credit_id;
1921 EXIT WHEN lock_hdr_scredits%NOTFOUND;
1922 END LOOP;
1923
1924 CLOSE lock_hdr_scredits;
1925
1926 END IF;
1927
1928 EXCEPTION
1929 WHEN OTHERS THEN
1930 ROLLBACK TO LOCK_ROWS;
1931
1932 IF lock_hdr_scredits%ISOPEN THEN
1933 CLOSE lock_hdr_scredits;
1934 END IF;
1935
1936 RAISE;
1937 END;
1938
1939 -- locked all
1940
1941 OE_Header_Scredit_Util.Query_Rows
1942 (p_sales_credit_id => p_sales_credit_id
1943 ,p_header_id => p_header_id
1944 ,x_header_scredit_tbl => x_header_scredit_tbl
1945 );
1946
1947 x_return_status := FND_API.G_RET_STS_SUCCESS;
1948
1949 IF l_debug_level > 0 THEN
1950 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDITS_UTIL.LOCK_ROWS' , 1 ) ;
1951 END IF;
1952
1953 EXCEPTION
1954 WHEN NO_DATA_FOUND THEN
1955
1956 x_return_status := FND_API.G_RET_STS_ERROR;
1957
1958 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1959 THEN
1960 fnd_message.set_name('ONT','OE_LOCK_ROW_DELETED');
1961 OE_MSG_PUB.Add;
1962 END IF;
1963
1964 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1965
1966 x_return_status := FND_API.G_RET_STS_ERROR;
1967 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1968 THEN
1969 fnd_message.set_name('ONT','OE_LOCK_ROW_ALREADY_LOCKED');
1970 OE_MSG_PUB.Add;
1971 END IF;
1972
1973 WHEN OTHERS THEN
1974
1975 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1976
1977 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1978 THEN
1979 OE_MSG_PUB.Add_Exc_Msg
1980 ( G_PKG_NAME
1981 , 'Lock_Rows'
1982 );
1983 END IF;
1984
1985 END Lock_Rows;
1986
1987
1988 -- Function Get_Values
1989
1990 FUNCTION Get_Values
1991 ( p_Header_Scredit_rec IN OE_Order_PUB.Header_Scredit_Rec_Type
1992 , p_old_Header_Scredit_rec IN OE_Order_PUB.Header_Scredit_Rec_Type :=
1993 OE_Order_PUB.G_MISS_HEADER_SCREDIT_REC
1994 )RETURN OE_Order_PUB.Header_Scredit_Val_Rec_Type
1995 IS
1996 l_Header_Scredit_val_rec OE_Order_PUB.Header_Scredit_Val_Rec_Type;
1997 --
1998 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1999 --
2000 --sg{
2001 l_sg_name Varchar2(60);
2002 --sg}
2003 BEGIN
2004
2005 IF (p_Header_Scredit_rec.salesrep_id IS NULL OR
2006 p_Header_Scredit_rec.salesrep_id <> FND_API.G_MISS_NUM) AND
2007 NOT OE_GLOBALS.Equal(p_Header_Scredit_rec.salesrep_id,
2008 p_old_Header_Scredit_rec.salesrep_id)
2009 THEN
2010 l_Header_Scredit_val_rec.salesrep := OE_Id_To_Value.Salesrep
2011 ( p_salesrep_id => p_Header_Scredit_rec.salesrep_id
2012 );
2013 END IF;
2014
2015 IF (p_Header_Scredit_rec.sales_credit_type_id IS NULL OR
2016 p_Header_Scredit_rec.sales_credit_type_id <> FND_API.G_MISS_NUM) AND
2017 NOT OE_GLOBALS.Equal(p_Header_Scredit_rec.sales_credit_type_id,
2018 p_old_Header_Scredit_rec.sales_credit_type_id)
2019 THEN
2020 l_Header_Scredit_val_rec.sales_credit_type := OE_Id_To_Value.sales_credit_type
2021 ( p_sales_credit_type_id => p_Header_Scredit_rec.sales_credit_type_id
2022 );
2023 END IF;
2024
2025 --SG{
2026 If p_Header_Scredit_rec.sales_group_id IS NOT NULL Then
2027 Begin
2028 /*Select group_name into l_sg_name
2029 From jtf_rs_groups_vl
2030 Where Group_Id=p_Header_Scredit_rec.sales_group_id;
2031 l_Header_Scredit_val_rec.sales_group:=l_sg_name;*/
2032
2033 l_Header_Scredit_val_rec.sales_group:=OE_Id_To_Value.get_sales_group_name(p_Header_Scredit_rec.sales_group_id);
2034 Exception
2035 When no_data_found Then
2036 l_Header_Scredit_val_rec.sales_group:='Group name not available';
2037 When others then
2038 Oe_Debug_Pub.add('OEXUHSCB.pls--get_values:'||SQLERRM);
2039 End;
2040 End If;
2041 --SG}
2042
2043 RETURN l_Header_Scredit_val_rec;
2044
2045 END Get_Values;
2046
2047 -- Procedure Get_Ids
2048
2049 PROCEDURE Get_Ids
2050 ( p_x_Header_Scredit_rec IN OUT NOCOPY OE_Order_PUB.Header_Scredit_Rec_Type
2051 , p_Header_Scredit_val_rec IN OE_Order_PUB.Header_Scredit_Val_Rec_Type
2052 )
2053 IS
2054
2055 --
2056 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2057 --
2058 BEGIN
2059
2060 -- initialize return_status.
2061
2062 p_x_Header_Scredit_rec.return_status := FND_API.G_RET_STS_SUCCESS;
2063
2064 -- initialize l_Header_Scredit_rec.
2065
2066
2067
2068 IF p_Header_Scredit_val_rec.salesrep <> FND_API.G_MISS_CHAR
2069 THEN
2070
2071 IF p_x_Header_Scredit_rec.salesrep_id <> FND_API.G_MISS_NUM THEN
2072
2073
2074
2075 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2076 THEN
2077
2078 FND_MESSAGE.SET_NAME('ONT','OE_BOTH_VAL_AND_ID_EXIST');
2079 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','salesrep');
2080 oe_msg_pub.Add;
2081
2082 END IF;
2083
2084 ELSE
2085
2086 p_x_Header_Scredit_rec.salesrep_id := OE_Value_To_Id.salesrep
2087 ( p_salesrep => p_Header_Scredit_val_rec.salesrep
2088 );
2089
2090 IF p_x_Header_Scredit_rec.salesrep_id = FND_API.G_MISS_NUM THEN
2091 p_x_Header_Scredit_rec.return_status := FND_API.G_RET_STS_ERROR;
2092 END IF;
2093
2094 END IF;
2095
2096 END IF;
2097
2098 IF p_Header_Scredit_val_rec.sales_credit_type <> FND_API.G_MISS_CHAR
2099 THEN
2100
2101 IF p_x_Header_Scredit_rec.sales_credit_type_id <> FND_API.G_MISS_NUM THEN
2102
2103
2104 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2105 THEN
2106
2107 FND_MESSAGE.SET_NAME('ONT','OE_BOTH_VAL_AND_ID_EXIST');
2108 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sales_credit_type');
2109 oe_msg_pub.Add;
2110
2111 END IF;
2112
2113 ELSE
2114
2115 p_x_Header_Scredit_rec.sales_credit_type_id := OE_Value_To_Id.sales_credit_type
2116 ( p_sales_credit_type => p_Header_Scredit_val_rec.sales_credit_type
2117 );
2118
2119 IF p_x_Header_Scredit_rec.sales_credit_type_id = FND_API.G_MISS_NUM THEN
2120 p_x_Header_Scredit_rec.return_status := FND_API.G_RET_STS_ERROR;
2121 END IF;
2122
2123 END IF;
2124
2125 END IF;
2126
2127
2128
2129
2130 END Get_Ids;
2131
2132 /* Start AuditTrail */
2133 PROCEDURE Pre_Write_Process
2134 ( p_x_header_scredit_rec IN OUT NOCOPY OE_ORDER_PUB.header_scredit_rec_type,
2135 p_old_header_scredit_rec IN OE_ORDER_PUB.header_scredit_rec_type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_REC )
2136 IS
2137 l_return_status varchar2(30);
2138 --
2139 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2140 --
2141 BEGIN
2142
2143 --11.5.10 Versioning/Audit Trail updates
2144 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' AND
2145 OE_GLOBALS.G_ROLL_VERSION <> 'N' THEN
2146 IF OE_GLOBALS.G_REASON_CODE IS NULL AND
2147 OE_GLOBALS.G_CAPTURED_REASON IN ('V','A') THEN
2148 IF p_x_header_scredit_rec.change_reason <> FND_API.G_MISS_CHAR THEN
2149 OE_GLOBALS.G_REASON_CODE := p_x_header_scredit_rec.change_reason;
2150 OE_GLOBALS.G_REASON_COMMENTS := p_x_header_scredit_rec.change_comments;
2151 OE_GLOBALS.G_CAPTURED_REASON := 'Y';
2152 ELSE
2153 IF l_debug_level > 0 THEN
2154 OE_DEBUG_PUB.add('Reason code missing for versioning', 1);
2155 END IF;
2156 IF OE_GLOBALS.G_UI_FLAG THEN
2157 raise FND_API.G_EXC_ERROR;
2158 END IF;
2159 END IF;
2160 END IF;
2161
2162 --log delayed request
2163 IF l_debug_level > 0 THEN
2164 oe_debug_pub.add('log versioning request',1);
2165 END IF;
2166 OE_Delayed_Requests_Pvt.Log_Request(p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
2167 p_entity_id => p_x_header_scredit_rec.header_id,
2168 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_HEADER_SCREDIT,
2169 p_requesting_entity_id => p_x_header_scredit_rec.sales_credit_id,
2170 p_request_type => OE_GLOBALS.G_VERSION_AUDIT,
2171 x_return_status => l_return_status);
2172 END IF;
2173
2174 IF (p_x_header_scredit_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
2175
2176 IF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG = 'Y' THEN
2177 IF l_debug_level > 0 THEN
2178 oe_debug_pub.add( 'AUDIT TRAIL , CHANGE ATTRIBUTE REQUIRES REASON' , 1 ) ;
2179 END IF;
2180 IF (p_x_header_scredit_rec.change_reason IS NULL OR
2181 p_x_header_scredit_rec.change_reason = FND_API.G_MISS_CHAR OR
2182 NOT OE_Validate.Change_Reason_Code(p_x_header_scredit_rec.change_reason)) THEN
2183 -- bug 3636884, defaulting reason from group API
2184 IF OE_GLOBALS.G_DEFAULT_REASON THEN
2185 if l_debug_level > 0 then
2186 oe_debug_pub.add('Defaulting Audit Reason for Order Sales Credit', 1);
2187 end if;
2188 p_x_header_scredit_rec.change_reason := 'SYSTEM';
2189 ELSE
2190 IF l_debug_level > 0 THEN
2191 OE_DEBUG_PUB.add('Reason code for change is missing or invalid', 1);
2192 END IF;
2193 fnd_message.set_name('ONT','OE_AUDIT_REASON_RQD');
2194 fnd_message.set_token('OBJECT','ORDER SALES CREDIT');
2195 oe_msg_pub.add;
2196 raise FND_API.G_EXC_ERROR;
2197 END IF;
2198 END IF;
2199 END IF;
2200 IF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG = 'Y' OR
2201 OE_GLOBALS.G_AUDIT_HISTORY_RQD_FLAG = 'Y' THEN
2202 IF l_debug_level > 0 THEN
2203 oe_debug_pub.add( 'OEXUHSCB:Calling oe_order_chg_pvt.recordhscredithist') ;
2204 END IF;
2205 --11.5.10 Versioning/Audit Trail updates
2206 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
2207 OE_Versioning_Util.Capture_Audit_Info(p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_SCREDIT,
2208 p_entity_id => p_x_header_scredit_rec.sales_credit_id,
2209 p_hist_type_code => 'UPDATE');
2210 --log delayed request
2211 OE_Delayed_Requests_Pvt.Log_Request(p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
2212 p_entity_id => p_x_header_scredit_rec.header_id,
2213 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_HEADER_SCREDIT,
2214 p_requesting_entity_id => p_x_header_scredit_rec.sales_credit_id,
2215 p_request_type => OE_GLOBALS.G_VERSION_AUDIT,
2216 x_return_status => l_return_status);
2217
2218 OE_GLOBALS.G_AUDIT_HISTORY_RQD_FLAG := 'N';
2219 ELSE
2220 OE_CHG_ORDER_PVT.RecordHScreditHist
2221 (p_header_scredit_id => p_x_header_scredit_rec.sales_credit_id,
2222 p_header_scredit_rec => null,
2223 p_hist_type_code => 'UPDATE',
2224 p_reason_code => p_x_header_scredit_rec.change_reason,
2225 p_comments => p_x_header_scredit_rec.change_comments,
2226 p_wf_activity_code => null,
2227 p_wf_result_code => null,
2228 x_return_status => l_return_status);
2229 END IF;
2230 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2231 IF l_debug_level > 0 THEN
2232 oe_debug_pub.add( 'INSERTING HEADER SCREDIT HISTORY CAUSED ERROR' , 1 ) ;
2233 END IF;
2234 if l_return_status = FND_API.G_RET_STS_ERROR then
2235 raise FND_API.G_EXC_ERROR;
2236 else
2237 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2238 end if;
2239 end if;
2240
2241 END IF;
2242 END IF;
2243
2244 END Pre_Write_Process;
2245 /* End AuditTrail */
2246
2247
2248 Procedure Calculate (p_header_id In Number,
2249 p_salesrep_id_tbl In salesrep_id_tbl_type,
2250 x_sales_credit_tbl OUT NOCOPY sales_credit_tbl_type,
2251 x_return_status OUT NOCOPY VARCHAR2)
2252 Is
2253 i PLS_INTEGER;
2254 plsql_block Varchar2(2000);
2255 l_nlp Number;
2256 --
2257 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2258 --
2259 Begin
2260 x_return_status := FND_API.G_RET_STS_SUCCESS;
2261
2262 --sample on how to populate the out. Values are hardcoded for testing purpose
2263 /* i:= p_salesrep_id_tbl.First;
2264 While i Is Not Null Loop
2265 x_sales_credit_tbl(i).salesrep_id :=p_salesrep_id_tbl(i).salesrep_id;
2266 x_sales_credit_tbl(i).sales_credit_id :=p_salesrep_id_tbl(i).sales_credit_id;
2267 x_sales_credit_tbl(i).sales_credit_pct:=25;
2268 i:= p_salesrep_id_tbl.Next(i);
2269 End Loop;
2270
2271 i:=p_salesrep_id_tbl.last + 1;
2272
2273 x_sales_credit_tbl(i).sales_credit_id :=NULL;
2274 x_sales_credit_tbl(i).sales_credit_pct:=25;
2275 ---
2276
2277 --Code Removed under jgould direction. Oracle IT has canceled this project.
2278
2279 If G_ACTIVATE_ORCL_CUSTOMIZATION = 'Y' Then
2280 OE_SALES_CREDIT_HOOK.Calculate(p_header_id =>p_header_id,
2281 p_sales_rep_id_tbl=>p_salesrep_id_tbl,
2282 x_sales_credit_tbl=>x_sales_credit_tbl,
2283 x_return_status =>x_return_status);
2284 End If;
2285 */
2286
2287 End;
2288
2289
2290 PROCEDURE DFLT_Hscredit_Primary_Srep
2291 ( p_header_id IN Number
2292 ,p_SalesRep_id IN Number
2293 ,x_return_status OUT NOCOPY Varchar2
2294 ) IS
2295 l_sales_credits_count Number;
2296 l_sales_credit_id Number;
2297
2298 CURSOR C_HSC_COUNT(p_header_id Number) IS
2299 SELECT count(sales_credit_id), max(sales_credit_id)
2300 FROM oe_sales_credits sc,
2301 oe_sales_credit_types sct
2302 WHERE header_id = p_header_id
2303 AND sct.sales_credit_type_id = sc.sales_credit_type_id
2304 AND sct.quota_flag = 'Y'
2305 AND line_id is null;
2306
2307 CURSOR C_SCRTYPE IS
2308 SELECT nvl(Sales_Credit_Type_id,1)
2309 FROM ra_salesreps
2310 WHERE salesrep_id = p_salesrep_id;
2311
2312 /* Changed the above cursor definition to fix the bug 1822931 */
2313
2314
2315 l_scredit_type_id number;
2316 l_Header_Scredit_rec OE_Order_PUB.Header_Scredit_Rec_Type;
2317 l_old_Header_Scredit_rec OE_Order_PUB.Header_Scredit_Rec_Type;
2318 l_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2319 l_old_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2320 l_control_rec OE_GLOBALS.Control_Rec_Type;
2321 l_return_status VARCHAR2(30);
2322 x_msg_count NUMBER;
2323 x_msg_data VARCHAR2(2000);
2324 l_header_id NUMBER;
2325 --
2326 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2327 --
2328 Begin
2329
2330 IF l_debug_level > 0 THEN
2331 oe_debug_pub.add( 'ENTERING OE_HEADER_SCREDIT_UTIL.DFLT_HSCREDIT_PRIMARY_SREP' , 1 ) ;
2332 END IF;
2333
2334 BEGIN
2335 SELECT header_id INTO l_header_id
2336 FROM oe_order_headers_all
2337 WHERE header_id = p_header_id;
2338 EXCEPTION
2339 WHEN NO_DATA_FOUND THEN
2340 IF l_debug_level > 0 THEN
2341 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDIT_UTIL.DFLT_HSCREDIT_PRIMARY_SREP,header not created' , 1 ) ;
2342 END IF;
2343 RETURN;
2344 END;
2345
2346 -- Check if the order has multiple revenue sales credits and if so
2347 -- prompt the user to use the sales credits window to update sales credits
2348 -- for the order header
2349 OPEN C_HSC_COUNT(p_header_id);
2350 FETCH C_HSC_COUNT INTO l_sales_credits_count, l_sales_credit_id;
2351 CLOSE C_HSC_COUNT;
2352 OPEN C_SCRTYPE;
2353 FETCH C_SCRTYPE INTO l_scredit_Type_id;
2354 CLOSE C_SCRTYPE;
2355 -- Commented to fix bug 1589196 Begin
2356 /* IF l_sales_credits_count > 1 THEN
2357
2358 fnd_message.set_name('ONT','OE_TOO_MANY_HSCREDIT');
2359 OE_MSG_PUB.Add;
2360 RAISE FND_API.G_EXC_ERROR;
2361
2362 ELSIF l_sales_credits_count = 1 THEN */
2363 -- update with new salesrep
2364 -- Commented to fix bug 1589196 End
2365 IF l_sales_credits_count = 1 THEN
2366 OE_Header_Scredit_Util.Lock_Row
2367 ( p_sales_credit_id => l_sales_credit_id
2368 , p_x_Header_Scredit_rec => l_Header_Scredit_rec
2369 , x_return_status => l_return_status
2370 );
2371
2372 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2374 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2375 RAISE FND_API.G_EXC_ERROR;
2376 END IF;
2377
2378 -- Populate Header_Scredit table
2379
2380 /* Start Audit Trail */
2381
2382 l_Header_Scredit_rec.change_reason := 'SYSTEM';
2383 l_Header_Scredit_rec.change_comments := 'Delayed Request, Change in Header Salesperson';
2384
2385 /* End Audit Trail */
2386
2387 l_Header_Scredit_tbl(1) := l_Header_Scredit_rec;
2388
2389 -- Set Operation. to update existing row
2390 l_Header_Scredit_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
2391
2392 l_old_Header_Scredit_tbl := l_Header_Scredit_tbl;
2393
2394 -- Update the row with new sales rep
2395 l_Header_Scredit_tbl(1).salesrep_id := p_salesrep_id;
2396
2397 /* Added the following line to fix the bug 1822931 */
2398 l_Header_Scredit_tbl(1).sales_credit_type_id := l_scredit_type_id;
2399
2400 -- Set control flags.
2401
2402 l_control_rec.controlled_operation := TRUE;
2403 l_control_rec.validate_entity := TRUE;
2404 l_control_rec.write_to_DB := TRUE;
2405
2406 l_control_rec.default_attributes := FALSE;
2407 l_control_rec.change_attributes := TRUE;
2408 l_control_rec.process := FALSE;
2409 -- Instruct API to retain its caches
2410
2411 l_control_rec.clear_api_cache := FALSE;
2412 l_control_rec.clear_api_requests := FALSE;
2413 --commented to fix bug 1589196 Begin
2414 -- ELSE
2415 -- insert for the new sales rep
2416 -- Set control flags.
2417 --commented to fix bug 1589196 End
2418 ELSIF l_sales_credits_count = 0 THEN
2419 l_control_rec.controlled_operation := TRUE;
2420 l_control_rec.validate_entity := TRUE;
2421 l_control_rec.write_to_DB := TRUE;
2422
2423 l_control_rec.default_attributes := TRUE;
2424 l_control_rec.change_attributes := TRUE;
2425 l_control_rec.process := FALSE;
2426
2427 -- Instruct API to retain its caches
2428
2429 l_control_rec.clear_api_cache := FALSE;
2430 l_control_rec.clear_api_requests := FALSE;
2431
2432 l_Header_Scredit_rec := OE_Order_PUB.G_MISS_HEADER_SCREDIT_REC;
2433 l_Header_Scredit_rec.operation := OE_GLOBALS.G_OPR_CREATE;
2434 l_Header_Scredit_rec.Header_id := p_header_id;
2435 l_Header_Scredit_rec.SalesRep_Id := p_salesrep_id;
2436 l_Header_Scredit_rec.Sales_credit_type_id := l_scredit_type_id;
2437
2438 --code removed under direction from jgould. Oralce It has cancel this project
2439 --G_ACTIVATE_ORCL_CUSTOMIZATION = 'Y' and OE_GLOBALS.G_UI_FLAG Then
2440 --l_Header_Scredit_rec.percent := 0;
2441 --Else
2442 l_Header_Scredit_rec.percent := 100;
2443 --End If;
2444
2445 l_Header_Scredit_tbl(1) := l_Header_Scredit_rec;
2446 l_old_Header_Scredit_tbl(1) := OE_Order_PUB.G_MISS_HEADER_SCREDIT_REC;
2447
2448 END IF;
2449 -- at this stage its either a insert or update
2450 -- Call OE_Order_PVT.Process_order
2451
2452 -- Set recursion mode.
2453 -- OE_GLOBALS.G_RECURSION_MODE := 'Y';
2454
2455 OE_ORDER_PVT.Header_Scredits
2456 (p_validation_level => FND_API.G_VALID_LEVEL_FULL
2457 ,p_control_rec => l_control_rec
2458 ,p_x_Header_Scredit_tbl => l_Header_Scredit_tbl
2459 ,p_x_old_Header_Scredit_tbl => l_old_Header_Scredit_tbl
2460 ,x_return_status => l_return_status);
2461
2462 -- Reset recursion mode.
2463 -- OE_GLOBALS.G_RECURSION_MODE := 'N';
2464
2465 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2467 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2468 RAISE FND_API.G_EXC_ERROR;
2469 END IF;
2470
2471 /*comment out for notification framework*/
2472 /*
2473 OE_ORDER_PVT.Process_Requests_And_notify
2474 ( p_process_requests => FALSE
2475 ,p_notify => TRUE
2476 ,x_return_status => l_return_status
2477 ,p_Header_Scredit_tbl => l_Header_Scredit_tbl
2478 ,p_old_Header_scredit_tbl => l_old_Header_Scredit_tbl);
2479
2480 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2482 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2483 RAISE FND_API.G_EXC_ERROR;
2484 END IF;
2485 */
2486
2487 IF l_sales_credits_count = 1 THEN -- issue message update with new salesrep
2488 fnd_message.set_name('ONT','OE_OE_UPDATED_ORDER_CREDIT');
2489 OE_MSG_PUB.Add;
2490 END IF;
2491
2492 IF l_debug_level > 0 THEN
2493 oe_debug_pub.add( 'EXITING OE_HEADER_SCREDIT_UTIL.DFLT_HSCREDIT_PRIMARY_SREP' , 1 ) ;
2494 END IF;
2495
2496 EXCEPTION
2497
2498 WHEN FND_API.G_EXC_ERROR THEN
2499
2500 x_return_status := FND_API.G_RET_STS_ERROR;
2501
2502 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2503
2504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2505
2506 WHEN OTHERS THEN
2507
2508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2509
2510 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2511 THEN
2512 OE_MSG_PUB.Add_Exc_Msg
2513 ( G_PKG_NAME
2514 , 'DFLT_Hscredit_Primary_Srep'
2515 );
2516 END IF;
2517 End DFLT_Hscredit_Primary_Srep;
2518
2519 --SG{
2520 Procedure Get_Sales_Group(p_date IN DATE:=NULL,
2521 p_sales_rep_id IN NUMBER,
2522 x_sales_group_id OUT NOCOPY NUMBER,
2523 --x_sales_group OUT NOCOPY VARCHAR2,
2524 x_return_status OUT NOCOPY VARCHAR2) AS
2525 Begin
2526 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2527
2528
2529 G_ORG_ID := OE_GLOBALS.G_ORG_ID;
2530
2531 IF G_ORG_ID IS NULL THEN
2532 oe_debug_pub.add('OE_GLOBALS.G_ORG_ID IS null');
2533 END IF;
2534
2535 Begin
2536 x_sales_group_id:=jtf_rs_integration_pub.get_default_sales_group(p_sales_rep_id,g_org_id,p_date);
2537 Exception
2538 When others Then
2539 oe_debug_pub.add('Setting sales group id to null:'||SQLERRM);
2540 x_sales_group_id:=null;
2541 End;
2542
2543 Exception
2544 When Others Then
2545 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2546 End;
2547
2548 /*************************************
2549 Redefault sales group as of input date
2550 **************************************/
2551
2552 Procedure Redefault_Sales_Group(p_header_id IN NUMBER,
2553 p_date IN DATE) AS
2554 Cursor none_fixed_sales_group IS
2555 Select salesrep_id,
2556 sales_credit_id,
2557 sales_group_id
2558 From OE_SALES_CREDITS
2559 Where Sales_Group_Updated_Flag = 'N'
2560 And header_id = p_header_id;
2561
2562 l_sales_credit_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
2563 l_sales_group_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
2564
2565 j number:=1;
2566 l_status Varchar2(30);
2567 l_sales_group_id Number;
2568 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2569 Begin
2570
2571 if l_debug_level > 0 then
2572 oe_debug_pub.add('Entering redefault sales group');
2573 end if;
2574
2575 For i In none_fixed_sales_group Loop
2576 Get_Sales_Group(p_date =>p_date
2577 ,p_sales_rep_id=>i.salesrep_id
2578 ,x_sales_group_id=>l_sales_group_id
2579 ,x_return_status =>l_status);
2580
2581 if l_debug_level > 0 then
2582 oe_debug_pub.add(' sales credit id passed:'||i.salesrep_id);
2583 oe_debug_pub.add(' date passed:'||p_date);
2584 oe_debug_pub.add(' return status from get_sales_group:'||l_status);
2585 end if;
2586
2587 If l_status = FND_API.G_RET_STS_SUCCESS
2588 and nvl(l_sales_group_id,-101) <> nvl(i.sales_group_id,-101) Then
2589 if l_debug_level > 0 then
2590 oe_debug_pub.add(' l_sales_group_id_tbl index j:'||j);
2591 oe_debug_pub.add(' old sales group id:'||i.sales_credit_id);
2592 oe_debug_pub.add(' new sales group id:'||l_sales_group_id);
2593 end if;
2594 l_sales_group_id_tbl(j):=l_sales_group_id;
2595 l_sales_credit_id_tbl(j):=i.sales_credit_id;
2596 if l_debug_level > 0 then
2597 oe_debug_pub.add(' group id in tbl:'||l_sales_group_id_tbl(j));
2598 oe_debug_pub.add(' sales credit id in tbl:'||l_sales_credit_id_tbl(j));
2599 end if;
2600 j:=j+1;
2601 End If;
2602 End Loop;
2603
2604 IF l_sales_credit_id_tbl.count > 0 THEN
2605 FORALL i in l_sales_credit_id_tbl.FIRST .. l_sales_credit_id_tbl.LAST
2606 UPDATE OE_SALES_CREDITS
2607 SET Sales_Group_Id = l_sales_group_id_tbl(i)
2608 WHERE Sales_Credit_Id = l_sales_credit_id_tbl(i);
2609 END IF;
2610
2611 if l_debug_level > 0 then
2612 oe_debug_pub.add('Leaving redefault sales group');
2613 end If;
2614 Exception
2615 When Others Then
2616 Oe_Debug_Pub.add('Exception occured OE_Header_Scredit_Util.Redefaut..:'||SQLERRM);
2617 End;
2618 --SG}
2619
2620
2621 END OE_Header_Scredit_Util;