[Home] [Help]
PACKAGE BODY: APPS.PJM_INQUIRY
Source
1 package body PJM_INQUIRY as
2 /* $Header: PJMWINQB.pls 120.6 2006/08/28 20:31:44 yliou noship $ */
3
4 --
5 -- Private Global Variables
6 --
7 G_Yes VARCHAR2(80) := NULL;
8 G_No VARCHAR2(80) := NULL;
9
10 FUNCTION get_req_total
11 (p_header_id number) return number is
12 X_req_total number;
13
14 BEGIN
15 SELECT nvl(SUM(decode(quantity,
16 null,
17 amount,
18 (quantity * unit_price)
19 )
20 ), 0)
21 into X_req_total
22 FROM po_requisition_lines_all
23 WHERE requisition_header_id = p_header_id and
24 nvl(cancel_flag,'N') <> 'Y' and -- Bug 554452 Ignore cancelled lines
25 nvl(MODIFIED_BY_AGENT_FLAG, 'N') = 'N' and -- Bug 574676
26 nvl(CLOSED_CODE, 'OPEN') <> 'FINALLY CLOSED'; -- Bug 574676
27
28 RETURN (X_req_total);
29
30 EXCEPTION
31 WHEN OTHERS then
32 X_req_total := 0;
33 END get_req_total;
34
35
36 FUNCTION BR_Type
37 ( X_Org_Id IN NUMBER
38 , X_Sybtype IN VARCHAR2
39 ) return VARCHAR2 IS
40
41 L_Type_Name VARCHAR2(80);
42
43 BEGIN
44
45 SELECT T.TYPE_NAME into L_Type_Name
46 from PO_DOCUMENT_TYPES_ALL_TL T
47 WHERE T.LANGUAGE = userenv('LANG')
48 and T.DOCUMENT_TYPE_CODE = 'RELEASE'
49 AND T.DOCUMENT_SUBTYPE = X_Sybtype
50 and T.org_id = X_Org_Id;
51
52 RETURN ( L_Type_Name );
53
54 EXCEPTION
55 WHEN OTHERS THEN
56 RETURN ( NULL );
57
58 END BR_Type;
59
60
61 FUNCTION Vendor_Contact
62 ( X_Contact_Id IN NUMBER
63 ) return VARCHAR2 IS
64
65 L_Contact_Name VARCHAR2(360);
66
67 BEGIN
68
69 SELECT DECODE(VC.LAST_NAME, NULL, NULL, VC.LAST_NAME||', '||VC.FIRST_NAME)
70 into L_Contact_Name
71 from PO_VENDOR_CONTACTS VC
72 where VC.VENDOR_CONTACT_ID = X_Contact_id;
73
74 RETURN ( L_Contact_Name );
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 RETURN ( NULL );
79
80 END Vendor_Contact;
81
82
83 FUNCTION Item_Number
84 ( X_Item_Id IN NUMBER
85 , X_Organization_Id IN NUMBER
86 ) return VARCHAR2 IS
87
88 L_Item_Number VARCHAR2(40);
89
90 BEGIN
91
92 SELECT segment1 into L_Item_Number
93 from mtl_system_items_b
94 where inventory_item_id = X_Item_Id
95 and organization_id = X_Organization_Id;
96
97 RETURN ( L_Item_Number );
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 RETURN ( NULL );
102
103 END Item_Number;
104
105
106 FUNCTION Payment_Term
107 ( X_Term_Id IN NUMBER
108 ) return VARCHAR2 IS
109
110 L_Payment_Term VARCHAR2(50);
111
112 BEGIN
113
114 SELECT NAME into L_Payment_Term
115 from AP_TERMS_TL B
116 WHERE term_id = X_Term_Id
117 AND LANGUAGE = userenv('LANG');
118
119 RETURN ( L_Payment_Term );
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 RETURN ( NULL );
124
125 END Payment_Term;
126
127
128 FUNCTION People_Name
129 ( X_Person_Id IN NUMBER
130 ) return VARCHAR2 IS
131
132 L_Full_Name VARCHAR2(240);
133
134 BEGIN
135
136 SELECT full_name into L_Full_Name
137 from PER_ALL_PEOPLE_F
138 where person_id = X_Person_Id
139 AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE
140 AND EFFECTIVE_END_DATE;
141
142 RETURN ( L_Full_Name );
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 RETURN ( NULL );
147
148 END People_Name;
149
150
151 FUNCTION OE_Lookup
152 ( X_Lookup_Code IN VARCHAR2
153 , X_Lookup_Type IN VARCHAR2
154 ) return VARCHAR2 IS
155
156 L_meaning VARCHAR2(80);
157
158 BEGIN
159
160 SELECT meaning into L_meaning
161 from OE_LOOKUPS
162 where Lookup_Code = X_Lookup_Code
163 and Lookup_Type = X_Lookup_Type;
164
165 RETURN ( L_meaning );
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 RETURN ( NULL );
170
171 END OE_Lookup;
172
173
174 FUNCTION Get_Lookup
175 ( X_Lookup_Code IN VARCHAR2
176 , X_Lookup_Type IN VARCHAR2
177 ) return VARCHAR2 IS
178
179 L_Displayed_Filed VARCHAR2(80);
180
181 BEGIN
182
183 SELECT DISPLAYED_FIELD into L_Displayed_filed
184 from po_lookup_codes
185 where Lookup_Code = X_Lookup_Code
186 and Lookup_Type = X_Lookup_Type;
187
188 RETURN ( L_Displayed_Filed );
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 RETURN ( NULL );
193
194 END Get_Lookup;
195
196
197 FUNCTION Location_Code
198 ( X_Location_Id IN NUMBER
199 ) return VARCHAR2 IS
200
201 L_Location_Code VARCHAR2(60);
202
203 BEGIN
204
205 SELECT location_code into L_Location_Code
206 from hr_locations_all_tl
207 where location_id = X_Location_Id
208 and language = userenv('LANG');
209
210 RETURN ( L_Location_Code );
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 RETURN ( NULL );
215
216 END Location_Code;
217
218
219 FUNCTION Vendor_Site
220 ( X_Vendor_Id IN NUMBER
221 , X_Vendor_Site_Id IN NUMBER
222 ) return VARCHAR2 IS
223
224 L_Vendor_Site VARCHAR2(15);
225
226 BEGIN
227
228 SELECT VENDOR_SITE_CODE into L_Vendor_Site
229 from PO_VENDOR_SITES_ALL
230 where vendor_id = X_Vendor_id
231 and vendor_site_id = X_Vendor_Site_Id;
232
233 RETURN ( L_Vendor_Site );
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 RETURN ( NULL );
238
239 END Vendor_Site;
240
241
242 FUNCTION Vendor_Name
243 ( X_Vendor_Id IN NUMBER
244 ) return VARCHAR2 IS
245
246 L_Vendor_Name VARCHAR2(360);
247
248 BEGIN
249
250 SELECT vendor_name into L_Vendor_Name
251 from po_vendors
252 where vendor_id = X_Vendor_id;
253
254 RETURN ( L_Vendor_Name );
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 RETURN ( NULL );
259
260 END Vendor_Name;
261
262
263 FUNCTION PO_Type
264 ( X_Org_Id IN NUMBER
265 ) return VARCHAR2 IS
266
267 L_Type_Name VARCHAR2(80);
268
269 BEGIN
270
271 SELECT T.TYPE_NAME into L_Type_Name
272 from PO_DOCUMENT_TYPES_ALL_TL T
273 WHERE T.LANGUAGE = userenv('LANG')
274 and T.DOCUMENT_TYPE_CODE IN ('PO', 'PA')
275 AND T.DOCUMENT_SUBTYPE = 'STANDARD'
276 and T.org_id = X_Org_Id;
277
278 RETURN ( L_Type_Name );
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 RETURN ( NULL );
283
284 END PO_Type;
285
286
287 FUNCTION OE_Order_Total
288 ( X_Header_ID IN NUMBER
289 ) return number IS
290
291 L_Order_Total number;
292
293 BEGIN
294
295 -- Bug 5465876: RMA order should show negative amount
296 SELECT SUM( decode(line_category_code,'RETURN',
297 (-1)*(NVL(ordered_quantity,0)-NVL(cancelled_quantity,0)),
298 (NVL(ordered_quantity,0)-NVL(cancelled_quantity,0))
299 )
300 * NVL(unit_selling_price , 0) )
301 INTO L_Order_Total
302 FROM oe_order_lines_all
303 WHERE header_id = X_Header_ID;
304
305 RETURN ( L_Order_Total );
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 RETURN ( NULL );
310
311 END OE_Order_Total;
312
313
314 FUNCTION OE_Org_Address
315 ( X_Org_ID IN NUMBER
316 , X_Org_Type IN VARCHAR2
317 ) return varchar2 IS
318
319 L_Org_Address varchar2(2000);
320
321 BEGIN
322
323 IF ( X_Org_Type = 'BILL TO' ) THEN
324
325 SELECT decode(loc.city , null , null , loc.city || ', ')
326 || decode(loc.postal_code , null , null , loc.postal_code || ', ')
327 || decode(loc.country , null , null , loc.country)
328 INTO L_Org_Address
329 FROM hz_cust_site_uses_all site
330 , hz_locations loc
331 , hz_party_sites party_site
332 , hz_cust_acct_sites_all acct_site
333 WHERE site.site_use_id = X_Org_ID
334 AND site.cust_acct_site_id = acct_site.cust_acct_site_id
335 AND acct_site.party_site_id = party_site.party_site_id
336 AND party_site.location_id = loc.location_id
337 AND nvl(site.org_id, -99 ) = nvl( acct_site.org_id, -99 );
338
339 ELSIF ( X_Org_Type = 'SHIP TO' ) THEN
340
341 SELECT decode(loc.city , null , null , loc.city || ', ')
342 || decode(loc.postal_code , null , null , loc.postal_code || ', ')
343 || decode(loc.country , null , null , loc.country)
344 INTO L_Org_Address
345 FROM hz_cust_site_uses_all site
346 , hz_locations loc
347 , hz_party_sites party_site
348 , hz_cust_acct_sites_all acct_site
349 WHERE site.site_use_id = X_Org_ID
350 AND site.cust_acct_site_id = acct_site.cust_acct_site_id
351 AND acct_site.party_site_id = party_site.party_site_id
352 AND party_site.location_id = loc.location_id
353 AND nvl(site.org_id, -99 ) = nvl( acct_site.org_id, -99 );
354 END IF;
355
356 RETURN ( L_Org_Address );
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 RETURN ( NULL );
361
362 END OE_Org_Address;
363
364 --
365 -- Note : The cached values are shared between Yes_No and
366 -- Sys_Yes_No as the text is extremely unlikely to
367 -- differ between the two lookups.
368 --
369 FUNCTION Yes_No
370 ( X_Lookup_Code IN VARCHAR2
371 ) return varchar2 IS
372
373 CURSOR c IS
374 SELECT meaning
375 FROM fnd_lookups
376 WHERE lookup_type = 'YES_NO'
377 AND lookup_code = X_Lookup_Code;
378
379 BEGIN
380
381 IF ( X_Lookup_Code = 'Y' ) THEN
382 IF ( G_Yes IS NULL ) THEN
383 OPEN c;
384 FETCH c INTO G_Yes;
385 CLOSE c;
386 END IF;
387 RETURN ( G_Yes );
388 ELSIF ( X_Lookup_Code = 'N' ) THEN
389 IF ( G_No IS NULL ) THEN
390 OPEN c;
391 FETCH c INTO G_No;
392 CLOSE c;
393 END IF;
394 RETURN ( G_No );
395 ELSE
396 RETURN ( NULL );
397 END IF;
398
399 EXCEPTION
400 WHEN OTHERS THEN
401 IF ( c%ISOPEN ) THEN
402 CLOSE c;
403 END IF;
404 RETURN ( NULL );
405
406 END Yes_No;
407
408
409 FUNCTION Sys_Yes_No
410 ( X_Lookup_Code IN NUMBER
411 ) return varchar2 IS
412
413 CURSOR c IS
414 SELECT meaning
415 FROM mfg_lookups
416 WHERE lookup_type = 'SYS_YES_NO'
417 AND lookup_code = X_Lookup_Code;
418
419 BEGIN
420
421 IF ( X_Lookup_Code = 'Y' ) THEN
422 IF ( G_Yes IS NULL ) THEN
423 OPEN c;
424 FETCH c INTO G_Yes;
425 CLOSE c;
426 END IF;
427 RETURN ( G_Yes );
428 ELSIF ( X_Lookup_Code = 'N' ) THEN
429 IF ( G_No IS NULL ) THEN
430 OPEN c;
431 FETCH c INTO G_No;
432 CLOSE c;
433 END IF;
434 RETURN ( G_No );
435 ELSE
436 RETURN ( NULL );
437 END IF;
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 IF ( c%ISOPEN ) THEN
442 CLOSE c;
443 END IF;
444 RETURN ( NULL );
445
446 END Sys_Yes_No;
447
448
449 FUNCTION Locator_Control
450 ( X_Lookup_Code IN NUMBER
451 ) return varchar2 IS
452
453 L_Return_Value VARCHAR2(80);
454
455 BEGIN
456
457 SELECT meaning
458 INTO L_Return_Value
459 FROM mfg_lookups
460 WHERE lookup_type = 'MTL_LOCATION_CONTROL'
461 AND lookup_code = X_Lookup_Code;
462
463 RETURN ( L_Return_Value );
464
465 EXCEPTION
466 WHEN OTHERS THEN
467 RETURN ( NULL );
468
469 END Locator_Control;
470
471
472 FUNCTION Component_Serial
473 ( X_Organization_ID IN NUMBER
474 , X_Wip_Entity_ID IN NUMBER
475 , X_Op_Seq_Num IN NUMBER
476 , X_Item_ID IN NUMBER
477 ) return varchar2 is
478
479 L_Return_Value VARCHAR2(4000);
480
481 CURSOR c IS
482 SELECT mut.serial_number
483 FROM mtl_unit_transactions mut
484 , mtl_material_transactions mmt
485 WHERE mmt.inventory_item_id = X_Item_ID
486 AND mmt.organization_id = X_Organization_ID
487 AND mmt.transaction_source_type_id = 5
488 AND mmt.transaction_source_id = X_Wip_Entity_ID
489 AND mmt.operation_seq_num = X_Op_Seq_Num
490 AND mmt.transaction_type_id in (35 , 38 , 43 , 48)
491 AND mut.transaction_id = mmt.transaction_id
492 AND mut.inventory_item_id = X_Item_ID
493 AND mut.organization_id = X_Organization_ID
494 AND mut.transaction_source_type_id = 5
495 AND mut.transaction_source_id = X_Wip_Entity_ID
496 GROUP BY mut.serial_number
497 HAVING sum(sign(mmt.primary_quantity)) < 0
498 ORDER BY mut.serial_number;
499
500 BEGIN
501
502 L_Return_Value := NULL;
503
504 FOR crec IN c LOOP
505 IF L_Return_Value IS NULL THEN
506 L_Return_Value := crec.serial_number;
507 ELSE
508 L_Return_Value := L_Return_Value || ' , ' || crec.serial_number;
509 END IF;
510 END LOOP;
511
512 RETURN( L_Return_Value );
513
514 EXCEPTION
515 WHEN OTHERS THEN
516 RETURN ( NULL );
517
518 END Component_Serial;
519
520
521 function TRANSACTION_SOURCE_NAME
522 ( X_Trx_Src_Type_ID IN NUMBER
523 , X_Trx_Source_ID IN NUMBER
524 ) return varchar2 is
525
526 CURSOR c1 IS
527 SELECT segment1
528 FROM po_headers_all
529 WHERE po_header_id = X_Trx_Source_ID;
530
531 CURSOR c2 IS
532 SELECT substr(concatenated_segments , 1 , 240)
533 FROM mtl_sales_orders_kfv
534 WHERE sales_order_id = X_Trx_Source_ID;
535
536 CURSOR c3 IS
537 SELECT k_number_disp
538 FROM oke_k_headers_v
539 WHERE k_header_id = X_Trx_Source_ID;
540
541 Trx_Source_Name VARCHAR2(4000);
542
543 BEGIN
544
545 IF ( X_Trx_Src_Type_ID = 1 ) THEN
546 OPEN c1;
547 FETCH c1 INTO Trx_Source_Name;
548 CLOSE c1;
549 ELSIF ( X_Trx_Src_Type_ID in ( 2 , 8 , 12 ) ) THEN
550 OPEN c2;
551 FETCH c2 INTO Trx_Source_Name;
552 CLOSE c2;
553 ELSIF ( X_Trx_Src_Type_ID = 16 ) THEN
554 OPEN c3;
555 FETCH c3 INTO Trx_Source_Name;
556 CLOSE c3;
557 ELSE
558 Trx_Source_Name := NULL;
559 END IF;
560
561 RETURN( Trx_Source_Name );
562
563 EXCEPTION
564 WHEN OTHERS THEN
565 RETURN( NULL );
566 END TRANSACTION_SOURCE_NAME;
567
568
569 end PJM_INQUIRY;