1 PACKAGE BODY CS_Charge_Core_PVT AS
2 /*$Header: csxchcrb.pls 120.1.12010000.2 2008/09/02 05:44:24 sshilpam ship $*/
3
4 /*********** Global Variables ********************************/
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_Charge_Details_PUB' ;
6
7
8 PROCEDURE Get_Source_Id(
9 p_source_code IN VARCHAR2,
10 p_source_number IN VARCHAR2,
11 p_org_id IN NUMBER,
12 x_source_id OUT NOCOPY NUMBER,
13 p_return_status OUT NOCOPY VARCHAR2) IS
14
15 BEGIN
16 -- Initialize API return status to success
17
18 p_return_status := 'S' ;
19
20
21 IF p_source_code = 'SR' THEN
22
23 IF p_source_number IS NOT NULL THEN
24 SELECT incident_id INTO x_source_id
25 FROM CS_INCIDENTS_ALL_B
26 WHERE incident_number = p_source_number;
27 ELSE
28 p_return_status := 'F' ;
29
30 END IF ;
31
32 ELSIF p_source_code = 'DR' THEN
33
34 IF p_source_number IS NOT NULL THEN
35 SELECT repair_line_id INTO x_source_id
36 FROM CSD_REPAIRS
37 WHERE repair_number = p_source_number ;
38 ELSE
39 p_return_status := 'F' ;
40 END IF ;
41
42 ELSIF p_source_code = 'SD' THEN
43
44 IF p_source_number IS NOT NULL THEN
45 SELECT debrief_header_id INTO x_source_id
46 FROM csf_debrief_headers
47 WHERE debrief_number = p_source_number ;
48 ELSE
49 p_return_status := 'F' ;
50 END IF ;
51
52 ELSE
53 p_return_status := 'F' ;
54 END IF ;
55
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 p_return_status := 'F' ;
59
60 END Get_Source_Id ;
61
62
63 -- Stubs for removed code
64 -- Stubs for removed code
65 -- Stubs for removed code
66
67 Procedure default_attributes(p_org OUT NOCOPY number,
68 x_return_status OUT NOCOPY varchar2) is
69 BEGIN null; END;
70
71
72 PROCEDURE Get_Invoice_details(
73 p_order_header_id IN NUMBER,
74 p_order_line_id IN NUMBER,
75 x_invoice_number OUT NOCOPY VARCHAR2,
76 x_invoice_date OUT NOCOPY DATE) is
77
78
79 /* Fix bug 2901318
80 CURSOR Inv_Det(p_order_header_id number) IS
81 SELECT min(TRX.TRX_NUMBER), to_char(TRX.TRX_DATE)
82 FROM OE_ORDER_HEADERS_ALL HD,
83 RA_CUSTOMER_TRX_ALL TRX,
84 OE_ORDER_LINES_ALL L,
85 RA_CUSTOMER_TRX_LINES_ALL TRXL
86 WHERE HD.HEADER_ID = L.HEADER_ID
87 AND trxl.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
88 AND trxl.INTERFACE_LINE_ATTRIBUTE1 = to_char(hd.ORDER_NUMBER)
89 -- AND trxl.INTERFACE_LINE_ATTRIBUTE2 = to_char(hd.ORDER_TYPE_ID) -- bug:2463968
90 AND trxl.INTERFACE_LINE_ATTRIBUTE6 = to_char(l.LINE_ID)
91 AND TRX.CUSTOMER_TRX_ID = TRXL.CUSTOMER_TRX_ID
92 AND HD.HEADER_ID = p_order_header_id
93 group by trx.trx_number,trx.trx_date; */
94
95 /*
96 -- Fix bug 2901318
97 CURSOR Inv_Det(p_order_header_id number) IS
98 SELECT min(TRX.TRX_NUMBER), to_char(TRX.TRX_DATE)
99 FROM OE_ORDER_HEADERS_ALL HD,
100 RA_CUSTOMER_TRX_ALL TRX,
101 RA_CUSTOMER_TRX_LINES_ALL TRXL,
102 oe_transaction_types_tl ottt
103 WHERE HD.HEADER_ID = p_order_header_id
104 and ottt.transaction_type_id = hd.ORDER_TYPE_ID
105 and trxl.sales_order = to_char(hd.ORDER_NUMBER)
106 AND trxl.INTERFACE_LINE_ATTRIBUTE2 = ottt.name
107 AND TRX.CUSTOMER_TRX_ID = TRXL.CUSTOMER_TRX_ID
108 group by trx.trx_number, trx.trx_date;
109 */
110
111 -- Added to resolve Bug # 3816254
112
113 Cursor get_order_details(p_order_header_id number) IS
114 SELECT order_number
115 FROM oe_order_headers_all
116 WHERE header_id = p_order_header_id;
117
118 Cursor get_invoice_details(p_order_header_number NUMBER,
119 p_line_id NUMBER)IS
120 SELECT TRX.TRX_NUMBER,
121 TRX.TRX_DATE
122 FROM RA_CUSTOMER_TRX_ALL TRX
126 AND TRXL.INTERFACE_LINE_ATTRIBUTE1 = to_char(p_order_header_number)
123 WHERE TRX.CUSTOMER_TRX_ID IN (SELECT TRXL.CUSTOMER_TRX_ID -- Bug 7117570 Changed = to IN
124 FROM RA_CUSTOMER_TRX_LINES_ALL TRXL
125 WHERE TRXL.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
127 AND TRXL.INTERFACE_LINE_ATTRIBUTE6 = to_char(p_line_id)
128 AND TRXL.SALES_ORDER = to_char(p_order_header_number));
129 -- Fixed bug:5104595
130 /* SELECT TRX.TRX_NUMBER,
131 TRX.TRX_DATE
132 FROM RA_CUSTOMER_TRX_LINES_ALL TRXL,
133 RA_CUSTOMER_TRX_ALL TRX
134 WHERE TRXL.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
135 AND TRXL.INTERFACE_LINE_ATTRIBUTE1 = to_char(p_order_header_number)
136 AND TRXL.INTERFACE_LINE_ATTRIBUTE6 = to_char(p_line_id)
137 AND TRX.CUSTOMER_TRX_ID = TRXL.CUSTOMER_TRX_ID; */
138
139 l_order_number NUMBER;
140
141 BEGIN
142
143 OPEN get_order_details(p_order_header_id);
144 FETCH get_order_details
145 INTO l_order_number;
146 CLOSE get_order_details;
147
148 IF l_order_number IS NOT NULL AND
149 p_order_line_id IS NOT NULL THEN
150
151 FOR v_cur in get_invoice_details(l_order_number,
152 p_order_line_id) LOOP
153 x_invoice_number := v_cur.TRX_NUMBER;
154 x_invoice_date := v_cur.TRX_DATE;
155 exit;
156
157 END LOOP;
158
159 ELSE
160 x_invoice_number := null;
161 x_invoice_date := null;
162 END IF;
163
164 /*
165 OPEN Inv_Det(p_order_header_id);
166 FETCH Inv_Det
167 INTO x_invoice_number,
168 x_invoice_date;
169 CLOSE Inv_Det;
170 */
171
172 EXCEPTION
173 When NO_DATA_FOUND THEN
174 BEGIN
175 x_invoice_number := null;
176 x_invoice_date := null;
177 END;
178 END;
179
180
181 /*
182 -- tkochend - commented out because not used
183 -- tkochend - commented out because not used
184 -- tkochend - commented out because not used
185 Procedure default_attributes(p_org OUT NOCOPY number,
186 x_return_status OUT NOCOPY varchar2) is
187 p_resp_appl_id NUMBER;
188 p_user_id NUMBER;
189 p_resp_id NUMBER;
190 p_login_id NUMBER;
191 p_org_id NUMBER;
192
193 BEGIN
194
195 -- ----------------------------------------------------------------------
196 -- FND_GLOBAL.RESP_APPL_ID, FND_GLOBAL.RESP_ID, and FND_GLOBAL.LOGIN_ID
197 -- returns -1 by default, which is an invalid value. FND_GLOBAL.USER_ID
198 -- is okay, because user ID -1 corresponds to user 'ANONYMOUS.' If
199 -- FND_GLOBAL returns -1, the variables are set to NULL instead.
200 -- ----------------------------------------------------------------------
201
202 IF ((p_resp_appl_id IS NULL) AND (FND_GLOBAL.RESP_APPL_ID <> -1)) THEN
203 -- ID is not passed in, return the default.
204 p_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
205 END IF;
206
207 IF ((p_resp_id IS NULL) AND (FND_GLOBAL.RESP_ID <> -1)) THEN
208 p_resp_id := FND_GLOBAL.RESP_ID;
209 END IF;
210
211 IF (p_user_id IS NULL) THEN
212 p_user_id := FND_GLOBAL.USER_ID;
213 END IF;
214
215 IF ((p_login_id = FND_API.G_MISS_NUM) AND
216 (FND_GLOBAL.LOGIN_ID NOT IN (-1,0))) THEN
217 p_login_id := FND_GLOBAL.LOGIN_ID;
218 ELSE
219 p_login_id := NULL;
220 END IF;
221
222 IF (p_org_id IS NULL) THEN
223 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
224 SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
225 INTO p_org_id
226 FROM dual;
227
228 IF (p_org_id IS NULL) THEN
229 p_org_id := TO_NUMBER(FND_PROFILE.Value_Specific('ORG_ID', p_user_id,
230 p_resp_id, p_resp_appl_id));
231 END IF;
232
233 IF p_org_id IS NOT NULL THEN
234
235 p_org := p_org_id;
236
237 x_return_status := 'Y';
238
239 ELSE
240 x_return_status := 'N';
241 END IF;
242
243 END IF;
244 END Default_attributes;
245
246 --End of removed code
247 --End of removed code
248 --End of removed code
249 */
250
251
252 FUNCTION Get_Ship_To_site_Id (
253 p_qte_header_id NUMBER,
254 p_qte_line_id NUMBER) RETURN NUMBER
255 IS
256 CURSOR c_ship_site1 IS
257 SELECT ship_to_org_id
258 FROM CS_ESTIMATE_DETAILS
259 WHERE incident_id = p_qte_header_id
260 AND estimate_detail_id = p_qte_line_id;
261
262 CURSOR c_ship_site2 IS
263 SELECT ship_to_org_id
264 FROM CS_ESTIMATE_DETAILS
265 WHERE incident_id = p_qte_header_id
266 AND estimate_detail_id IS NULL ;
267
268 l_ship_site_id NUMBER;
269 l_ship_site_use_id NUMBER;
270
271 CURSOR c_cust_id IS
272 SELECT account_id
273 FROM CS_INCIDENTS_ALL_B
274 WHERE incident_id = p_qte_header_id;
275
276 CURSOR C_site_use(l_cust_id NUMBER, l_ship_site_id NUMBER) IS
277 SELECT site_use_id
278 FROM hz_cust_site_uses b,
279 hz_cust_acct_sites a
280 WHERE b.cust_acct_site_id = a.cust_acct_site_id
281 AND b.site_use_code = 'SHIP_TO' --and b.primary_flag = 'Y'
282 AND a.party_site_id = l_ship_site_id
283 AND a.cust_account_id = l_cust_id;
284
285 l_cust_id NUMBER;
286 l_return_status VARCHAR2(1);
287 l_msg_count number;
291 OPEN c_ship_site1;
288 l_msg_data VARCHAR2(2000);
289
290 BEGIN
292 FETCH c_ship_site1 INTO l_ship_site_id;
293 IF c_ship_site1%FOUND THEN
294 CLOSE c_ship_site1;
295 -- Get the Customer Account from the header
296 OPEN c_cust_id;
297 FETCH c_cust_id INTO l_cust_id;
298 CLOSE c_cust_id;
299
300 -- Get the site use id from the HZ_cust_site_uses
301
302 OPEN C_site_use(l_cust_id , l_ship_site_id);
303 FETCH C_site_use into l_ship_site_use_id;
304 CLOSE C_site_use;
305
306 return l_ship_site_use_id;
307 END IF;
308 CLOSE c_ship_site1;
309 OPEN c_ship_site2;
310 FETCH c_ship_site2 INTO l_ship_site_id;
311 IF c_ship_site2%FOUND THEN
312 CLOSE c_ship_site2;
313
314 -- Get the Customer Account from the header
315 OPEN c_cust_id;
316 FETCH c_cust_id INTO l_cust_id;
317 CLOSE c_cust_id;
318
319 -- Get the site use id from the HZ_cust_site_uses
320
321 OPEN C_site_use(l_cust_id , l_ship_site_id);
322 FETCH C_site_use into l_ship_site_use_id;
323 CLOSE C_site_use;
324
325 return l_ship_site_use_id;
326
327 END IF;
328 CLOSE c_ship_site2;
329 return l_ship_site_use_id;
330 END Get_Ship_To_site_Id;
331
332 FUNCTION Get_invoice_to_party_site_id (
333 p_qte_header_id NUMBER,
334 p_qte_line_id NUMBER
335 ) RETURN NUMBER
336 IS
337 CURSOR c_inv_site1 IS
338 SELECT invoice_to_org_id
339 FROM CS_ESTIMATE_DETAILS
340 WHERE estimate_detail_id = p_qte_line_id
341 AND incident_id = p_qte_header_id;
342
343 CURSOR c_inv_site2 IS
344 SELECT invoice_to_org_id
345 FROM CS_ESTIMATE_DETAILS
346 WHERE incident_id = p_qte_header_id;
347
348
349 l_inv_site_id NUMBER;
350
351
352 l_ship_site_id NUMBER;
353 l_bill_site_use_id NUMBER;
354
355 CURSOR c_cust_id IS
356 SELECT customer_id
357 FROM CS_INCIDENTS_ALL_B
358 WHERE incident_id = p_qte_header_id;
359
360 CURSOR C_site_use(l_cust_id NUMBER, l_inv_site_id NUMBER) IS
361 SELECT site_use_id
362 FROM hz_cust_site_uses b,hz_cust_acct_sites a
363 WHERE b.cust_acct_site_id = a.cust_acct_site_id
364 AND b.site_use_code = 'BILL_TO'
365 AND a.party_site_id = l_inv_site_id
366 AND a.cust_account_id = l_cust_id;
367
368 l_cust_id NUMBER;
369
370 BEGIN
371
372 OPEN c_inv_site1;
373 FETCH c_inv_site1 INTO l_inv_site_id;
374 IF c_inv_site1%FOUND THEN
375 CLOSE c_inv_site1;
376
377 -- Get the Customer Account from the header
378 OPEN c_cust_id;
379 FETCH c_cust_id INTO l_cust_id;
380 CLOSE c_cust_id;
381
382 -- Get the site use id from the HZ_cust_site_uses
383
384 OPEN C_site_use(l_cust_id , l_inv_site_id);
385 FETCH C_site_use into l_bill_site_use_id;
386 CLOSE C_site_use;
387 return l_bill_site_use_id;
388
389 END IF;
390 CLOSE c_inv_site1;
391 OPEN c_inv_site2;
392 FETCH c_inv_site2 INTO l_inv_site_id;
393 IF c_inv_site2%FOUND THEN
394 CLOSE c_inv_site2;
395
396 -- Get the Customer Account from the header
397 OPEN c_cust_id;
398 FETCH c_cust_id INTO l_cust_id;
399 CLOSE c_cust_id;
400
401 -- Get the site use id from the HZ_cust_site_uses
402
403 OPEN C_site_use(l_cust_id , l_inv_site_id);
404 FETCH C_site_use into l_bill_site_use_id;
405 CLOSE C_site_use;
406 return l_bill_site_use_id;
407 END IF;
408 CLOSE c_inv_site2;
409 return l_bill_site_use_id;
410 END Get_invoice_to_party_site_id;
411
412 Function Number_Format(p_value_amount IN NUMBER) return VARCHAR2
413
414 IS
415
416 l_format VARCHAR2(2000);
417 l_value_amount VARCHAR2(100);
418
419 BEGIN
420
421 IF p_value_amount IS NOT NULL THEN
422 fnd_currency.build_format_mask(l_format,30,2,1);
423 l_value_amount := to_char(p_value_amount, l_format);
424
425 ELSE
426 --p_value_amount is null
427 l_value_amount := null;
428
429 END IF;
430
431
432 return l_value_amount;
433
434 END;
435
436
437 Function Get_Value_Name(p_restriction_type IN VARCHAR2,
438 p_value_object_id IN NUMBER) return VARCHAR2
439 IS
440
441 Cursor c_party_name IS
442 select party_name||'-'||party_number party_name
443 from hz_parties
444 where party_id = p_value_object_id
445 and nvl(status, 'A') = 'A';
446
447 Cursor c_incident_type IS
448 select name
449 from cs_incident_types_vl
450 where incident_type_id = p_value_object_id;
451
452 l_value_name VARCHAR2(4000) := null;
453
454 BEGIN
455
456 IF p_restriction_type = 'BILL_TO_CUSTOMER' THEN
457 OPEN c_party_name;
458 FETCH c_party_name INTO l_value_name;
459 CLOSE c_party_name;
460
461 ELSE
462 IF p_restriction_type = 'SERVICE_REQUEST_TYPE' THEN
463 OPEN c_incident_type;
464 FETCH c_incident_type INTO l_value_name;
465 CLOSE c_incident_type;
466 END IF;
467 END IF;
468
469 return l_value_name;
470
471 END Get_Value_Name;
472
473
474 END CS_Charge_Core_PVT ;
475
[Home] [Help]