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