1 PACKAGE BODY PO_REQS_INQ_SV as
2 /* $Header: POXRQVRB.pls 115.9 2003/12/12 04:17:15 sbull ship $*/
3 /*============================= PO_REQS_INQ_SV ===============================*/
4
5
6 /*===========================================================================
7
8 FUNCTION NAME: get_po_number
9
10 ===========================================================================*/
11 FUNCTION get_po_number (X_po_header_id IN NUMBER)
12 RETURN VARCHAR2 IS
13
14 X_progress VARCHAR2(3) := NULL;
15 X_po_number PO_HEADERS.segment1%TYPE := NULL;
16
17 BEGIN
18
19 X_progress := '005';
20
21 IF X_po_header_id is NULL THEN
22 return (NULL);
23 ELSE
24
25 SELECT POH.segment1
26 INTO X_po_number
27 FROM PO_HEADERS_ALL POH -- FPI GA
28 WHERE POH.PO_HEADER_ID = X_po_header_id;
29
30 return(x_po_number);
31
32 END IF;
33
34 EXCEPTION
35
36 WHEN NO_DATA_FOUND THEN
37 return(NULL);
38
39 WHEN OTHERS THEN
40 return(NULL);
41
42
43 END get_po_number;
44
45 /*===========================================================================
46
47 FUNCTION NAME: get_reserved_flag
48
49 ===========================================================================*/
50 FUNCTION get_reserved_flag (x_requisition_header_id IN NUMBER)
51 RETURN VARCHAR2 IS
52
53 X_progress VARCHAR2(3) := NULL;
54 X_reserved_flag VARCHAR2(3);
55
56 BEGIN
57
58 X_progress := '005';
59 x_reserved_flag := NULL;
60
61 --<Encumbrance FPJ>
62 PO_CORE_S.should_display_reserved(
63 p_doc_type => PO_CORE_S.g_doc_type_REQUISITION
64 , p_doc_level => PO_CORE_S.g_doc_level_HEADER
65 , p_doc_level_id => x_requisition_header_id
66 , x_display_reserved_flag => x_reserved_flag
67 );
68
69 return(x_reserved_flag);
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 return(NULL);
74
75 END get_reserved_flag;
76
77
78 /*===========================================================================
79
80 FUNCTION NAME: get_shipped_quantity
81
82 ===========================================================================*/
83 --Bug# 1392077
84 --Toju George 08/31/2000
85 --Modified the procedure to replace req_num and line_num with ids.
86 /*FUNCTION get_shipped_quantity (x_requisition_num IN VARCHAR2,
87 x_line_num IN VARCHAR2)
88 RETURN NUMBER IS
89 */
90 FUNCTION get_shipped_quantity (x_requisition_header_id IN VARCHAR2,
91 x_requisition_line_id IN VARCHAR2)
92 RETURN NUMBER IS
93 X_progress VARCHAR2(3) := NULL;
94 X_shipped_quantity NUMBER := 0;
95 X_order_source_id NUMBER;
96
97 BEGIN
98
99 /*
100 ** Get shipped quantity based on internal sales order
101 */
102 X_progress := '005';
103 X_shipped_quantity := 0;
104
105 /* replacing the select statement with the new OE API. */
106 select order_source_id
107 into x_order_source_id
108 from po_system_parameters;
109
110 --Bug# 1392077
111 --Toju George 08/31/2000
112 --Modified the call to procedure to replace req_num and line_num with ids.
113
114 /* X_shipped_quantity := OE_ORDER_IMPORT_INTEROP_PUB.Get_Shipped_Qty(x_order_source_id,
115 x_requisition_num,
116 x_line_num);
117 */
118 X_shipped_quantity := OE_ORDER_IMPORT_INTEROP_PUB.Get_Shipped_Qty(x_order_source_id,
119 x_requisition_header_id,
120 x_requisition_line_id);
121
122 /* SELECT decode(SOL.shipped_quantity,NULL,0,SOL.shipped_quantity)
123 INTO X_shipped_quantity
124 FROM SO_LINES SOL, SO_HEADERS SOH,
125 PO_SYSTEM_PARAMETERS POSP
126 WHERE SOH.original_system_reference = X_requisition_num
127 AND SOH.original_system_source_code =
128 to_char(POSP.order_source_id)
129 AND SOH.header_id = SOL.header_id
130 AND SOL.original_system_line_reference = X_line_num;
131 */
132 Return(X_shipped_quantity);
133
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 return(0);
137
138 WHEN OTHERS THEN
139 return(0);
140 po_message_s.sql_error('get_shipped_quantity', X_progress, sqlcode);
141 RAISE;
142
143 END get_shipped_quantity;
144
145 /*===========================================================================
146
147 PROCEDURE NAME: po_req_header_inq_wrapper
148
149 ===========================================================================*/
150 PROCEDURE po_req_header_inq_wrapper (x_req_num IN NUMBER,
151 x_preparer IN NUMBER,
152 x_req_header_amount OUT NOCOPY NUMBER,
153 x_reserved_flag OUT NOCOPY VARCHAR2,
154 x_preparer_name OUT NOCOPY VARCHAR2 ) IS
155 BEGIN
156
157 IF x_preparer IS NOT NULL THEN
158 x_preparer_name := PO_INQ_SV.GET_PERSON_NAME(x_preparer);
159 END IF;
160
161 IF x_req_num IS NOT NULL THEN
162 x_reserved_flag := PO_REQS_INQ_SV.GET_RESERVED_FLAG (x_req_num);
163 x_req_header_amount := PO_CORE_S.GET_TOTAL('E', x_req_num);
164 END IF;
165
166
167 END po_req_header_inq_wrapper;
168
169 /*===========================================================================
170
171 PROCEDURE NAME: po_req_line_inq_wrapper
172
173 ===========================================================================*/
174 PROCEDURE po_req_line_inq_wrapper (x_to_person_id IN NUMBER,
175 x_suggested_buyer_id IN NUMBER,
176 x_item_id IN NUMBER,
177 x_wip_entity_id IN NUMBER,
178 x_wip_operation_seq_num IN NUMBER,
179 x_wip_resource_seq_num IN NUMBER,
180 x_destination_organization_id IN NUMBER,
181 x_wip_repetitive_schedule_id IN NUMBER,
182 x_quantity IN NUMBER,
183 x_purchasing_agent_id IN NUMBER,
184 x_preparer_id IN NUMBER,
185 x_blanket_po_header_id IN NUMBER,
186 x_source_type_code IN VARCHAR2,
187 x_line_location_id IN NUMBER,
188 x_segment1 IN VARCHAR2,
189 x_line_num IN NUMBER,
190 x_requsition_line_id IN NUMBER,
191
192 v_requestor OUT NOCOPY VARCHAR2,
193 v_suggested_buyer OUT NOCOPY VARCHAR2,
194 v_assembly_quantity OUT NOCOPY NUMBER,
195 v_resource_quantity OUT NOCOPY NUMBER,
196 v_wip_operation_code OUT NOCOPY VARCHAR2,
197 v_bom_department_code OUT NOCOPY VARCHAR2,
198 v_purchasing_agent_name_dsp OUT NOCOPY VARCHAR2,
199 v_preparer_name OUT NOCOPY VARCHAR2,
200 v_blanket_po_num_dsp OUT NOCOPY VARCHAR2,
201 v_order_num OUT NOCOPY VARCHAR2,
202 v_req_line_amount OUT NOCOPY NUMBER,
203 --Bug# 1392077
204 --Toju George 08/31/2000
205 x_requisition_header_id IN VARCHAR2) IS
206 BEGIN
207
208 v_requestor :=NULL;
209 v_suggested_buyer:=NULL;
210 v_purchasing_agent_name_dsp :=NULL;
211 v_preparer_name :=NULL;
212 v_blanket_po_num_dsp :=NULL;
213 v_assembly_quantity := 0;
214 v_resource_quantity := 0;
215 v_wip_operation_code := NULL;
216 v_bom_department_code := NULL;
217
218
219 IF x_to_person_id IS NOT NULL THEN
220 v_requestor := PO_INQ_SV.GET_PERSON_NAME(x_to_person_id);
221 END IF;
222
223 IF x_suggested_buyer_id IS NOT NULL THEN
224 v_suggested_buyer := PO_INQ_SV.GET_PERSON_NAME(x_suggested_buyer_id);
225 END IF;
226
227 IF x_wip_entity_id IS NOT NULL THEN
228
229 v_assembly_quantity := NVL(PO_INQ_SV.GET_ASSEMBLY_QUANTITY(x_item_id,
230 x_wip_entity_id,
231 x_wip_operation_seq_num,
232 x_wip_resource_seq_num,
233 x_destination_organization_id,
234 x_wip_repetitive_schedule_id,
235 x_quantity),0);
236
237 v_resource_quantity := NVL(PO_INQ_SV.GET_RESOURCE_QUANTITY(x_item_id,
238 x_wip_entity_id,
239 x_wip_operation_seq_num,
240 x_wip_resource_seq_num,
241 x_destination_organization_id,
242 x_wip_repetitive_schedule_id,
243 x_quantity),0);
244
245 v_wip_operation_code := PO_INQ_SV.GET_WIP_OPERATION_CODE(x_wip_entity_id,
246 x_wip_operation_seq_num,
247 x_destination_organization_id,
248 x_wip_repetitive_schedule_id);
249
250 v_bom_department_code := PO_INQ_SV.GET_BOM_DEPARTMENT_CODE(x_wip_entity_id,
251 x_wip_operation_seq_num,
252 x_destination_organization_id,
253 x_wip_repetitive_schedule_id);
254
255 END IF;
256
257 IF x_purchasing_agent_id IS NOT NULL THEN
258 v_purchasing_agent_name_dsp := PO_INQ_SV.GET_PERSON_NAME(x_purchasing_agent_id);
259 END IF;
260
261 IF x_preparer_id IS NOT NULL THEN
262 v_preparer_name := PO_INQ_SV.GET_PERSON_NAME(x_preparer_id);
263 END IF;
264
265 IF x_blanket_po_header_id IS NOT NULL THEN
266 v_blanket_po_num_dsp := PO_REQS_INQ_SV.GET_PO_NUMBER(x_blanket_po_header_id);
267
268 END IF;
269
270
271 IF (x_source_type_code = 'VENDOR') THEN
272 v_order_num := PO_INQ_SV.GET_PO_NUMBER(x_line_location_id);
273 ELSE
274
275 --Bug# 1392077
276 --Toju George 08/31/2000
277 --Modified the call to procedure to replace req_num and line_num with ids.
278 /* v_order_num := PO_INQ_SV.GET_SO_NUMBER(x_segment1,x_line_num);*/
279
280 v_order_num := PO_INQ_SV.GET_SO_NUMBER(x_requisition_header_id,x_requsition_line_id);
281 END IF;
282
283 -- SERVICES FPJ
284 -- Changed to the new function which handles both services and goods lines
285
286 v_req_line_amount := PO_REQS_INQ_SV.get_req_amount('I',x_requsition_line_id);
287
288
289 END po_req_line_inq_wrapper;
290 /*===========================================================================
291
292 PROCEDURE NAME: po_req_dist_inq_wrapper
293
294 ===========================================================================*/
295 PROCEDURE po_req_dist_inq_wrapper (x_to_person_id IN NUMBER,
296 x_suggested_buyer_id IN NUMBER,
297 x_preparer_id IN NUMBER,
298 x_distribution_id IN NUMBER,
299
300 v_requestor OUT NOCOPY VARCHAR2,
301 v_suggested_buyer OUT NOCOPY VARCHAR2,
302 v_preparer_name OUT NOCOPY VARCHAR2,
303 v_req_distribution_amount OUT NOCOPY NUMBER) IS
304
305 BEGIN
306
307 IF x_to_person_id IS NOT NULL THEN
308 v_requestor := PO_INQ_SV.GET_PERSON_NAME(x_to_person_id);
309 END IF;
310
311
312 IF x_suggested_buyer_id IS NOT NULL THEN
313 v_suggested_buyer := PO_INQ_SV.GET_PERSON_NAME(x_suggested_buyer_id);
314 END IF;
315
316
317 IF x_preparer_id IS NOT NULL THEN
318 v_preparer_name := PO_INQ_SV.GET_PERSON_NAME(x_preparer_id);
319 END IF;
320
321 -- SERVICES FPJ
322 -- Changed to the new function which handles both services and goods lines
323
324 v_req_distribution_amount := PO_REQS_INQ_SV.get_req_amount ('J' , x_distribution_id);
325
326 END po_req_dist_inq_wrapper;
327
328 /*===========================================================================
329
330 PROCEDURE NAME: get_reqln_pq_lookups
331 Bug# 1934593
332 This procedure fetches the lookup values removed from the view
333 po_requisition_lines_inq_v as part of the performance fix
334
335 ===========================================================================*/
336 PROCEDURE get_reqln_pq_lookups( x_auth_status_code in varchar2,
337 x_tran_reason_code in varchar2,
338 x_src_doc_type_code in varchar2,
339 x_dest_type_code in varchar2,
340 x_src_type_code in varchar2,
341 x_auth_status_dsp in out NOCOPY varchar2,
342 x_tran_reason_dsp in out NOCOPY varchar2,
343 x_src_doc_type_dsp in out NOCOPY varchar2,
344 x_dest_type_dsp in out NOCOPY varchar2,
345 x_src_type_dsp in out NOCOPY varchar2) is
346
347 x_progress varchar2(3) := '000';
348
349 cursor c_lkp(x_lookup_type in varchar2,x_lookup_code in varchar2) is
350 select displayed_field
351 from po_lookup_codes
352 where lookup_type = x_lookup_type
353 and lookup_code = x_lookup_code;
354
355 BEGIN
356
357 x_progress := '001';
358 if x_auth_status_code is not null then
359 open c_lkp('AUTHORIZATION STATUS',nvl(x_auth_status_code,'INCOMPLETE'));
360 fetch c_lkp into x_auth_status_dsp;
361 if c_lkp%NOTFOUND then
362 x_auth_status_dsp := NULL;
363 end if;
364 close c_lkp;
365 end if;
366
367 x_progress := '002';
368 if x_tran_reason_code is not null then
369 open c_lkp('TRANSACTION REASON', x_tran_reason_code);
370 fetch c_lkp into x_tran_reason_dsp;
371 if c_lkp%notfound then
372 x_tran_reason_dsp := NULL;
373 end if;
374 close c_lkp;
375 end if;
376
377 x_progress := '003';
378 if x_src_doc_type_code is not null then
379 open c_lkp('SOURCE DOCUMENT TYPE',x_src_doc_type_code);
380 fetch c_lkp into x_src_doc_type_dsp;
381 if c_lkp%notfound then
382 x_src_doc_type_dsp := NULL;
383 end if;
384 close c_lkp;
385 end if;
386
387 x_progress := '004';
388 if x_dest_type_code is not null then
389 open c_lkp('DESTINATION TYPE',x_dest_type_code);
390 fetch c_lkp into x_dest_type_dsp;
391 if c_lkp%notfound then
392 x_dest_type_dsp := NULL;
393 end if;
394 close c_lkp;
395 end if;
396
397 x_progress := '005';
398 if x_src_type_code is not null then
399 open c_lkp('REQUISITION SOURCE TYPE', x_src_type_code);
400 fetch c_lkp into x_src_type_dsp;
401 if c_lkp%notfound then
402 x_src_type_dsp := NULL;
403 end if;
404 close c_lkp;
405 end if;
406
407 EXCEPTION
408 WHEN OTHERS THEN
409 po_message_s.sql_error('po_reqs_inq_sv.get_reqln_pq_lookups', x_progress, sqlcode);
410 raise;
411
412 END get_reqln_pq_lookups;
413
414 /*===========================================================================
415
416 FUNCTION NAME: get_ga_info
417
418 ===========================================================================*/
419 PROCEDURE get_ga_info (X_po_header_id IN NUMBER,
420 X_ga_flag IN OUT NOCOPY VARCHAR2,
421 X_owning_org_name IN OUT NOCOPY VARCHAR2 ) IS
422
423 X_progress VARCHAR2(3) := NULL;
424 X_owning_org_id NUMBER;
425
426 BEGIN
427
428 X_progress := '001';
429
430 IF X_po_header_id is not NULL THEN
431
432 SELECT POH.global_agreement_flag, POH.org_id
433 INTO X_ga_flag, X_owning_org_id
434 FROM PO_HEADERS_ALL POH
435 WHERE POH.PO_HEADER_ID = X_po_header_id;
436
437 SELECT name
438 INTO X_owning_org_name
439 FROM hr_organization_units
440 WHERE organization_id = X_owning_org_id;
441
442 END IF;
443
444 EXCEPTION
445
446 WHEN NO_DATA_FOUND THEN
447 X_ga_flag := 'N';
448 X_owning_org_name := null;
449
450 WHEN OTHERS THEN
451 X_ga_flag := 'N';
452 X_owning_org_name := null;
453
454
455 END get_ga_info;
456
457 ---------------------------------------------------------------------------------------------
458 --Start of Comments
459 --SERVICES FPJ
460 --Name: get_req_amount
461 --
462 --Pre-reqs: None
463 --
464 --Modifies: None
465 --
466 --Locks: None
467 --
468 --Function: This function gets the amount from the database for service lines and
469 -- calculates the amount from price and qty for other lines
470 --
471 --Parameters:
472 --IN:
473 -- p_mode
474 -- A value of 'I' or 'J' is passed depending on the calling level(I for line, J for
475 -- distribution
476 -- p_req_entity_id
477 -- Requisition line id or distribution id based on the calling level
478 --OUT:
479 -- Returns the req line or distribution amount
480 --
481 --Testing: -
482 --End of Comments
483 -------------------------------------------------------------------------------------------------
484 FUNCTION get_req_amount (p_mode IN VARCHAR2,
485 p_req_entity_id IN NUMBER)
486 RETURN NUMBER IS
487
488 l_progress VARCHAR2(3) := NULL;
489 l_value_basis PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
490 l_req_amount PO_REQUISITION_LINES_ALL.amount%TYPE;
491
492 BEGIN
493
494 l_progress := '010';
495
496 IF p_mode = 'I' THEN
497
498 -- Sql What : Gets the line type corresponding to the req line id
499 -- Sql Why : This is used to determine the amount
500
501 l_progress := '020';
502
503 Select plt.order_type_lookup_code
504 Into l_value_basis
505 From po_line_types_b plt,
506 po_requisition_lines_all prl --<Shared Proc FPJ>
507 Where plt.line_type_id = prl.line_type_id
508 And prl.requisition_line_id = p_req_entity_id;
509
510
511 ELSE
512
513 -- Sql What : Gets the line type corresponding to the req distribution id
514 -- Sql Why : This is used to determine the amount
515
516 l_progress := '030';
517
518 Select plt.order_type_lookup_code
519 Into l_value_basis
520 From po_line_types_b plt,
521 po_requisition_lines_all prl, --<Shared Proc FPJ>
522 po_req_distributions_all prd --<Shared Proc FPJ>
523 Where plt.line_type_id = prl.line_type_id
524 And prl.requisition_line_id = prd.requisition_line_id
525 And prd.distribution_id = p_req_entity_id;
526
527 END IF;
528
529 IF l_value_basis in ('FIXED PRICE','RATE') THEN
530
531 IF p_mode = 'I' THEN
532
533 -- Sql What : Gets the amount on the req Line
534 -- Sql Why : To return to the post-query procedure on the summary form
535
536 l_progress := '040';
537
538 Select prl.amount
539 Into l_req_amount
540 From po_requisition_lines_all prl --<Shared Proc FPJ>
541 Where prl.requisition_line_id = p_req_entity_id;
542
543 ELSE
544 -- Sql What : Gets the amount on the req distribution
545 -- Sql Why : To return to the post-query procedure on the summary form
546
547 l_progress := '050';
548
549 Select prd.req_line_amount
550 Into l_req_amount
551 From po_req_distributions_all prd --<Shared Proc FPJ>
552 Where prd.distribution_id = p_req_entity_id;
553
554 END IF;
555
556 ELSE
557 l_progress := '060';
558
559 l_req_amount := PO_CORE_S.GET_TOTAL(p_mode,p_req_entity_id);
560
561 END IF;
562
563 Return l_req_amount;
564
565 EXCEPTION
566 WHEN OTHERS THEN
567 po_message_s.sql_error('po_reqs_inq_sv.get_req_amount', l_progress, sqlcode);
568 raise;
569 END get_req_amount;
570
571 END PO_REQS_INQ_SV;
[Home] [Help]