1 PACKAGE BODY PO_AUTOCREATE_GROUPING_PVT AS
2 /* $Header: PO_AUTOCREATE_GROUPING_PVT.plb 120.10.12000000.2 2007/04/17 06:57:48 vdurbhak ship $ */
3
4 --=============================================
5 -- GLOBAL VARIABLES
6 --=============================================
7
8 D_PACKAGE_BASE CONSTANT VARCHAR2(50) := PO_LOG.get_package_base('PO_AUTOCREATE_GROUPING_PVT');
9
10 D_get_line_action_tbl CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_line_action_tbl');
11 D_check_po_line_numbers CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_po_line_numbers');
12 D_lines_match CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'lines_match');
13 D_group_req_lines CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_req_lines');
14 D_get_req_line_delivery_info CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_req_line_delivery_info');
15 D_check_delivery_info CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_delivery_info');
16 D_lines_info_match CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'lines_info_match');
17 D_get_req_line_info CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_req_line_info');
18 D_get_po_line_info CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_po_line_info');
19 D_check_line_info CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_line_info');
20 D_lines_delivery_info_match CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'lines_delivery_info_match');
21 D_group_by_requisition_line CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_by_requisition_line_num');
22 D_group_by_requisition_seq_num CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_by_requisition_seq_num');
23 D_group_by_default CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_by_default');
24 D_has_same_req_header CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'has_same_req_header');
25 D_match_add_to_po_lines CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'match_add_to_po_lines');
26 D_find_matching_builder_index CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'find_matching_builder_index');
27 D_find_matching_builder_line CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'find_matching_builder_line_num');
28 D_req_lines_match CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'req_lines_match');
29 D_get_max_po_line_num CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_max_po_line_num');
30 D_get_consigned_flag_tbl CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_consigned_flag_tbl');
31 D_find_matching_po_line_num CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'find_matching_po_line_num');
32
33 /*=========================================================================*/
34 /*===================== SPECIFICATIONS (PRIVATE) ==========================*/
35 /*=========================================================================*/
36
37 CURSOR po_delivery_info_csr(p_po_line_id_to_compare IN NUMBER)
38 IS
39 SELECT need_by_date,
40 ship_to_location_id,
41 ship_to_organization_id,
42 consigned_flag
43 FROM po_line_locations_all
44 WHERE po_line_id = p_po_line_id_to_compare;
45
46 FUNCTION get_req_line_delivery_info(
47 p_req_line_id IN NUMBER,
48 p_supplier_id IN NUMBER,
49 p_site_id IN NUMBER
50 ) RETURN PO_DELIVERY_INFO_CSR%ROWTYPE;
51
52 PROCEDURE check_delivery_info(
53 p_need_by_grouping_profile IN VARCHAR2,
54 p_ship_to_grouping_profile IN VARCHAR2,
55 p_delivery_one IN PO_DELIVERY_INFO_CSR%ROWTYPE,
56 p_delivery_two IN PO_DELIVERY_INFO_CSR%ROWTYPE,
57 x_message_code OUT NOCOPY VARCHAR2,
58 x_token_name OUT NOCOPY VARCHAR2,
59 x_token_value OUT NOCOPY VARCHAR2
60 );
61
62 PROCEDURE lines_info_match(
63 p_agreement_id IN NUMBER,
64 p_req_line_id IN NUMBER,
65 p_req_line_id_to_compare IN NUMBER,
66 p_po_line_id_to_compare IN NUMBER,
67 x_message_code OUT NOCOPY VARCHAR2,
68 x_token_name OUT NOCOPY VARCHAR2,
69 x_token_value OUT NOCOPY VARCHAR2
70 );
71
72 TYPE line_info IS RECORD(
73 item_id PO_REQUISITION_LINES_ALL.item_id%TYPE,
74 item_description PO_REQUISITION_LINES_ALL.item_description%TYPE,
75 item_revision PO_REQUISITION_LINES_ALL.item_revision%TYPE,
76 order_type_lookup_code PO_REQUISITION_LINES_ALL.order_type_lookup_code%TYPE,
77 purchase_basis PO_REQUISITION_LINES_ALL.purchase_basis%TYPE,
78 matching_basis PO_REQUISITION_LINES_ALL.matching_basis%TYPE,
79 preferred_grade PO_REQUISITION_LINES_ALL.preferred_grade%TYPE,
80 unit_meas_lookup_code PO_REQUISITION_LINES_ALL.unit_meas_lookup_code%TYPE,
81 transaction_reason PO_REQUISITION_LINES_ALL.transaction_reason_code%TYPE,
82 contract_id PO_REQUISITION_LINES_ALL.blanket_po_header_id%TYPE,
83 source_document_id PO_REQUISITION_LINES_ALL.blanket_po_header_id%TYPE,
84 source_document_line_id PO_LINES_ALL.po_line_id%TYPE,
85 cancel_flag PO_LINES_ALL.cancel_flag%TYPE,
86 closed_code PO_LINES_ALL.closed_code%TYPE,
87 supplier_ref_number PO_REQUISITION_LINES_ALL.supplier_ref_number%TYPE
88 );
89
90 FUNCTION get_req_line_info(p_req_line_id IN NUMBER) RETURN LINE_INFO;
91
92 FUNCTION get_po_line_info(p_po_line_id IN NUMBER) RETURN LINE_INFO;
93
94 PROCEDURE check_line_info(
95 p_agreement_id IN NUMBER,
96 p_line_one IN LINE_INFO,
97 p_line_two IN LINE_INFO,
98 x_message_code OUT NOCOPY VARCHAR2,
99 x_token_name OUT NOCOPY VARCHAR2,
100 x_token_value OUT NOCOPY VARCHAR2
101 );
102
103 PROCEDURE lines_delivery_info_match(
104 p_supplier_id IN NUMBER,
105 p_site_id IN NUMBER,
106 p_req_line_id IN NUMBER,
107 p_req_line_id_to_compare IN NUMBER,
108 p_po_line_id_to_compare IN NUMBER,
109 x_message_code OUT NOCOPY VARCHAR2,
110 x_token_name OUT NOCOPY VARCHAR2,
111 x_token_value OUT NOCOPY VARCHAR2
112 );
113
114 FUNCTION group_by_requisition_line_num
115 ( p_req_line_num_tbl IN PO_TBL_NUMBER
116 , p_po_line_num_tbl IN PO_TBL_NUMBER
117 , p_start_index IN NUMBER
118 , p_end_index IN NUMBER
119 ) RETURN PO_TBL_NUMBER;
120
121 FUNCTION group_by_requisition_seq_num
122 ( p_po_line_num_tbl IN PO_TBL_NUMBER
123 , p_add_to_po_header_id IN NUMBER
124 , p_start_index IN NUMBER
125 , p_end_index IN NUMBER
126 ) RETURN PO_TBL_NUMBER;
127
128 FUNCTION group_by_default
129 ( p_req_line_id_tbl IN PO_TBL_NUMBER
130 , p_po_line_num_tbl IN PO_TBL_NUMBER
131 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
132 , p_add_to_po_header_id IN NUMBER
133 , p_builder_agreement_id IN NUMBER
134 , p_start_index IN NUMBER
135 , p_end_index IN NUMBER
136 ) RETURN PO_TBL_NUMBER;
137
138 FUNCTION has_same_req_header
139 ( p_req_line_id_tbl IN PO_TBL_NUMBER
140 ) RETURN BOOLEAN;
141
142 PROCEDURE match_add_to_po_lines
143 ( p_req_line_id_tbl IN PO_TBL_NUMBER
144 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
145 , p_add_to_po_header_id IN NUMBER
146 , p_builder_agreement_id IN NUMBER
147 , p_start_index IN NUMBER
148 , p_end_index IN NUMBER
149 , x_req_line_id_tbl OUT NOCOPY PO_TBL_NUMBER
150 , x_po_line_num_tbl OUT NOCOPY PO_TBL_NUMBER
151 );
152
153 FUNCTION find_matching_builder_line_num
154 ( p_current_index IN NUMBER
155 , p_req_line_id_tbl IN PO_TBL_NUMBER
156 , p_po_line_num_tbl IN PO_TBL_NUMBER
157 , p_builder_agreement_id IN NUMBER
158 ) RETURN NUMBER;
159
160 FUNCTION req_lines_match
161 ( p_agreement_id IN NUMBER
162 , p_req_line_id_1 IN NUMBER
163 , p_req_line_id_2 IN NUMBER
164 ) RETURN BOOLEAN;
165
166 FUNCTION get_max_po_line_num
167 ( p_po_line_num_tbl IN PO_TBL_NUMBER
168 , p_po_header_id IN NUMBER := NULL
169 ) RETURN NUMBER;
170
171 FUNCTION get_consigned_flag_tbl
172 ( p_req_line_id_tbl IN PO_TBL_NUMBER
173 , p_builder_org_id IN NUMBER
174 , p_builder_supplier_id IN NUMBER
175 , p_builder_site_id IN NUMBER
176 ) RETURN PO_TBL_VARCHAR1;
177
178 FUNCTION find_matching_po_line_num
179 ( p_req_line_id IN NUMBER
180 , p_comparison_tbl IN PO_TBL_NUMBER
181 , p_po_line_num_tbl IN PO_TBL_NUMBER
182 ) RETURN NUMBER;
183
184 /*=========================================================================*/
185 /*========================== BODY (PUBLIC) ================================*/
186 /*=========================================================================*/
187
188 -------------------------------------------------------------------------------
189 --Start of Comments
190 --Name: get_line_action_tbl
191 --Pre-reqs:
192 -- None
193 --Modifies:
194 -- None
195 --Locks:
196 -- None
197 --Function:
198 -- Gets a table with corresponding 'NEW' or 'ADD' actions for each requisition
199 -- line in the document builder. Action is 'NEW' if autocreating a new line and
200 -- 'ADD' if adding to an existing PO line. For the latter to be true, the
201 -- line's PO line number has to be the same as one of the PO's line numbers.
202 --Parameters:
203 --IN:
204 --p_po_line_number_tbl
205 -- The table of PO line numbers for all the requistion lines in the document
206 -- builder.
207 --p_add_to_po_header_id
208 -- The header ID of the PO for the Add To PO case. If New PO, this parameter
209 -- will be null and the table passed out will have 'NEW' for each requisition
210 -- line in the document builder.
211 --Returns:
212 -- Table of 'NEW' or 'ADD' actions corresponding to each of the input
213 -- requisition lines.
214 --Notes:
215 -- None
216 --Testing:
217 -- None
218 --End of Comments
219 -------------------------------------------------------------------------------
220 FUNCTION get_line_action_tbl(
221 p_po_line_number_tbl IN PO_TBL_NUMBER,
222 p_add_to_po_header_id IN NUMBER
223 ) RETURN PO_TBL_VARCHAR5
224 IS
225 d_mod CONSTANT VARCHAR2(100) := D_get_line_action_tbl;
226 d_position NUMBER := 0;
227
228 l_num_lines NUMBER;
229 l_line_action_tbl PO_TBL_VARCHAR5;
230 BEGIN
231 IF (PO_LOG.d_proc) THEN
232 PO_LOG.proc_begin(d_mod);
233 PO_LOG.proc_begin(d_mod,'p_add_to_po_header_id',p_add_to_po_header_id);
234 END IF;
235
236 -- Get the number of lines being passed in.
237 l_num_lines := p_po_line_number_tbl.COUNT;
238
239 -- Initialize table indicating whether lines are being added to existing PO
240 -- lines.
241 l_line_action_tbl := PO_TBL_VARCHAR5();
242 l_line_action_tbl.EXTEND(l_num_lines);
243
244 -- For each line in the doc builder, set the line action to 'ADD' if adding
245 -- to an existing PO line and 'NEW' if creating a new PO line.
246 FOR i IN 1..l_num_lines
247 LOOP
248 BEGIN
249 SELECT 'ADD'
250 INTO l_line_action_tbl(i)
251 FROM po_lines_all
252 WHERE po_header_id = p_add_to_po_header_id
253 AND line_num = p_po_line_number_tbl(i);
254 EXCEPTION
255 WHEN NO_DATA_FOUND THEN
256 l_line_action_tbl(i) := 'NEW';
257 END;
258 END LOOP;
259 IF (PO_LOG.d_proc) THEN
260 PO_LOG.proc_return(d_mod, l_line_action_tbl);
261 END IF;
262
263 RETURN l_line_action_tbl;
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 IF (PO_LOG.d_exc) THEN
268 PO_LOG.exc(d_mod, d_position, 'An error occured in get_line_action_tbl');
269 END IF;
270
271 RAISE;
272 END get_line_action_tbl;
273
274 -------------------------------------------------------------------------------
275 --Start of Comments
276 --Name: check_po_line_numbers
277 --Pre-reqs:
278 -- None
279 --Modifies:
280 -- None
281 --Locks:
282 -- None
283 --Function:
284 -- Checks all requisition lines in the document builder to see if their PO line
285 -- numbers are valid. Returns tables with error messages corresponding to each
286 -- line in the doc builder, null if there are no error messages.
287 --Parameters:
288 --IN:
289 --p_style_id
290 -- The ID of the style specified in the document builder.
291 --p_agreement_id
292 -- The ID of the agreement specified in the document builder.
293 --p_supplier_id
294 -- The ID of the supplier specified in the document builder.
295 --p_site_id
296 -- The ID of the site specified in the document builder.
297 --p_req_line_id_tbl
298 -- The table of req line IDs for all the requistion lines in the document
299 -- builder.
300 --p_po_line_number_tbl
301 -- The table of PO line numbers for all the requistion lines in the document
302 -- builder.
303 --p_add_to_po_header_id
304 -- The header ID of the PO for the Add To PO case. If New PO, this parameter
305 -- will be null.
306 --OUT:
307 --x_message_code_tbl
308 -- Table of error message codes corresponding to each of the input requisition
309 -- lines.
310 --x_token_name_tbl
311 -- Table of error message token names corresponding to each of the input
312 -- requisition lines.
313 --x_token_value_tbl
314 -- Table of error message token values corresponding to each of the input
315 -- requisition lines.
316 --Notes:
317 -- None
318 --Testing:
319 -- None
320 --End of Comments
321 -------------------------------------------------------------------------------
322 PROCEDURE check_po_line_numbers(
323 p_style_id IN NUMBER,
324 p_agreement_id IN NUMBER,
325 p_supplier_id IN NUMBER,
326 p_site_id IN NUMBER,
327 p_req_line_id_tbl IN PO_TBL_NUMBER,
328 p_po_line_number_tbl IN PO_TBL_NUMBER,
329 p_add_to_po_header_id IN NUMBER,
330 x_message_code_tbl OUT NOCOPY PO_TBL_VARCHAR30,
331 x_token_name_tbl OUT NOCOPY PO_TBL_VARCHAR30,
332 x_token_value_tbl OUT NOCOPY PO_TBL_VARCHAR2000
333 )
334 IS
335 d_mod CONSTANT VARCHAR2(100) := D_check_po_line_numbers;
336 d_position NUMBER := 0;
337
338 l_num_lines NUMBER;
339 l_key NUMBER;
340 l_req_line_id PO_REQUISITION_LINES_ALL.requisition_line_id%TYPE;
341 l_po_line_num PO_LINES_ALL.line_num%TYPE;
342 l_req_line_id_to_compare PO_REQUISITION_LINES_ALL.requisition_line_id%TYPE;
343 l_po_line_id_to_compare PO_LINES_ALL.po_line_id%TYPE;
344 l_progress_payment_flag PO_DOC_STYLE_HEADERS.progress_payment_flag%TYPE;
345 l_message_code VARCHAR2(30) := NULL;
346 l_token_name VARCHAR2(30) := NULL;
347 l_token_value VARCHAR2(2000) := NULL;
348 l_line_combined_flag_tbl PO_TBL_VARCHAR1;
349 l_line_combined_flag VARCHAR2(1);
350
351 CURSOR req_line_ids_csr(
352 c_key IN NUMBER,
353 c_po_line_num IN varchar2,
354 c_current_req_line_id IN NUMBER
355 ) IS
356 SELECT index_num1 -- requisition line ID
357 FROM po_session_gt
358 WHERE key = c_key
359 AND index_num2 = c_po_line_num
360 AND index_num1 <> c_current_req_line_id;
361 BEGIN
362 IF (PO_LOG.d_proc) THEN
363 PO_LOG.proc_begin(d_mod);
364 PO_LOG.proc_begin(d_mod,'p_style_id',p_style_id);
365 PO_LOG.proc_begin(d_mod,'p_agreement_id',p_agreement_id);
366 PO_LOG.proc_begin(d_mod,'p_supplier_id',p_supplier_id);
367 PO_LOG.proc_begin(d_mod,'p_site_id',p_site_id);
368 PO_LOG.proc_begin(d_mod,'p_req_line_id_tbl',p_req_line_id_tbl);
369 PO_LOG.proc_begin(d_mod,'p_po_line_number_tbl',p_po_line_number_tbl);
370 PO_LOG.proc_begin(d_mod,'p_add_to_po_header_id',p_add_to_po_header_id);
371 END IF;
372
373 -- Get the number of lines being passed in.
374 l_num_lines := p_req_line_id_tbl.COUNT;
375
376 -- Initialize error message values to be returned.
377 x_message_code_tbl := PO_TBL_VARCHAR30();
378 x_message_code_tbl.EXTEND(l_num_lines);
379 x_token_name_tbl := PO_TBL_VARCHAR30();
380 x_token_name_tbl.EXTEND(l_num_lines);
381 x_token_value_tbl := PO_TBL_VARCHAR2000();
382 x_token_value_tbl.EXTEND(l_num_lines);
383
384 -- Initialize table of flags indicating whether lines are combined.
385 l_line_combined_flag_tbl := PO_TBL_VARCHAR1();
386 l_line_combined_flag_tbl.EXTEND(l_num_lines);
387
388 -- Get a new session key for use with the temp table.
389 l_key := PO_CORE_S.get_session_gt_nextval;
390
391 -- Insert req line ID and PO line num of all lines into temp table.
392 FORALL i IN 1..l_num_lines
393 INSERT INTO po_session_gt(
394 key, -- unique key
395 index_num1, -- req line ID
396 index_num2 -- PO line num
397 )
398 VALUES (l_key, p_req_line_id_tbl(i), p_po_line_number_tbl(i));
399
400 -- <BUG 4922298 START>
401 -- Wrap SQL statement with exception block to handle null style ID.
402 -- Determine whether progress payment is enabled.
403 BEGIN
404 SELECT progress_payment_flag
405 INTO l_progress_payment_flag
406 FROM po_doc_style_headers
407 WHERE style_id = p_style_id;
408 EXCEPTION
409 WHEN NO_DATA_FOUND THEN
410 l_progress_payment_flag := null;
411 END;
412 -- <BUG 4922298 END>
413
414 -- If progress payment is enabled, update l_line_combined_flag_tbl with flag
415 -- indicating whether each line is combined with another req or PO line.
416 IF (NVL(l_progress_payment_flag, 'N') = 'Y')
417 THEN
418 -- If req line is being combined with another req or PO line, set flag to
419 -- 'Y'. Else, set flag to 'N'.
420 SELECT NVL(
421 (SELECT 'Y'
422 FROM dual
423 WHERE EXISTS(
424 -- Select all doc builder requisition lines that have the same PO line
425 -- number as the current line in the loop.
426 SELECT 'doc builder lines with same PO line number'
427 FROM po_session_gt POSGT2
428 WHERE POSGT2.index_num1 <> POSGT.index_num1 -- Not the current line
429 AND POSGT2.index_num2 = POSGT.index_num2 -- Same PO line number
430 )
431 OR EXISTS(
432 -- Select all PO lines that have the same PO line number as the
433 -- current line in the loop.
434 SELECT 'PO lines with same PO line number'
435 FROM po_lines_all
436 WHERE po_header_id = p_add_to_po_header_id
437 AND line_num = POSGT.index_num2 -- Same PO line number
438 )),
439 'N' -- NVL to 'N' if no other req/PO lines with same PO line number
440 )
441 BULK COLLECT INTO l_line_combined_flag_tbl
442 FROM po_session_gt POSGT
443 WHERE key = l_key;
444 END IF;
445
446 FOR i IN 1..l_num_lines -- Loop through all doc builder req lines
447 LOOP
448 -- Initialize the message code to NULL for each iteration
449 l_message_code := NULL;
450
451 l_req_line_id := p_req_line_id_tbl(i);
452 l_po_line_num := p_po_line_number_tbl(i);
453 l_line_combined_flag := l_line_combined_flag_tbl(i);
454
455 -- If there is no error yet, check if the PO line number is NULL.
456 IF (l_message_code IS NULL AND l_po_line_num IS NULL)
457 THEN
458 l_message_code := 'PO_ALL_NOT_NULL';
459 l_token_name := NULL;
460 l_token_value := NULL;
461 END IF;
462
463 -- If there is no error yet, check if the PO line number is less than or
464 -- equal to 0.
465 IF (l_message_code IS NULL AND l_po_line_num <= 0)
466 THEN
467 l_message_code := 'PO_ALL_ENTER_VALUE_GT_ZERO';
468 l_token_name := NULL;
469 l_token_value := NULL;
470 END IF;
471
472 -- If there is no error yet, check if progress payment is enabled and if
473 -- lines are being combined.
474 IF (l_message_code IS NULL
475 AND l_progress_payment_flag = 'Y'
476 AND l_line_combined_flag = 'Y')
477 THEN
478 l_message_code := 'PO_ALL_CANT_COMB_PROGRESSPAY';
479 l_token_name := NULL;
480 l_token_value := NULL;
481 END IF;
482
483 IF (l_message_code IS NULL) -- If there is no error yet
484 THEN
485 -- Check if all req lines with the same PO line number match.
486 OPEN req_line_ids_csr(l_key, l_po_line_num, l_req_line_id);
487 LOOP
488 FETCH req_line_ids_csr INTO l_req_line_id_to_compare;
489 EXIT WHEN req_line_ids_csr%NOTFOUND;
490 lines_match(
491 p_agreement_id,
492 p_supplier_id,
493 p_site_id,
494 l_req_line_id,
495 l_req_line_id_to_compare,
496 NULL, -- No PO line ID to compare
497 l_message_code,
498 l_token_name,
499 l_token_value
500 );
501
502 -- If any req lines with the same PO line number don't match, no need
503 -- to check other req lines since only show one error message.
504 EXIT WHEN l_message_code IS NOT NULL;
505 END LOOP;
506 CLOSE req_line_ids_csr;
507
508 -- Check for PO line mismatch if all req lines match
509 IF (l_message_code IS NULL)
510 THEN
511 -- If PO line with the same PO line number exists, check if matches
512 BEGIN
513 SELECT po_line_id
514 INTO l_po_line_id_to_compare
515 FROM po_lines_all
516 WHERE po_header_id = p_add_to_po_header_id
517 AND line_num = l_po_line_num;
518
519 lines_match(
520 p_agreement_id,
521 p_supplier_id,
522 p_site_id,
523 l_req_line_id,
524 NULL, -- No req line ID to compare
525 l_po_line_id_to_compare,
526 l_message_code,
527 l_token_name,
528 l_token_value
529 );
530 EXCEPTION
531 WHEN NO_DATA_FOUND THEN
532 NULL; -- No need to check if no PO line with same PO line number
533 END;
534 END IF; -- Check for PO line mismatch if all req lines match
535 END IF; -- If there is no error yet
536
537 -- If there is a mismatch, set error message
538 IF (l_message_code IS NOT NULL)
539 THEN
540 x_message_code_tbl(i) := l_message_code;
541 x_token_name_tbl(i) := l_token_name;
542 x_token_value_tbl(i) := l_token_value;
543 END IF; -- If there is a mismatch, set error message
544 END LOOP; -- Loop through all doc builder req lines
545
546 -- Clean up temp table
547 DELETE FROM po_session_gt
548 WHERE key = l_key;
549
550 IF (PO_LOG.d_proc) THEN
551 PO_LOG.proc_end(d_mod, 'x_message_code_tbl', x_message_code_tbl);
552 PO_LOG.proc_end(d_mod, 'x_token_name_tbl', x_token_name_tbl);
553 PO_LOG.proc_end(d_mod, 'x_token_value_tbl', x_token_value_tbl);
554 END IF;
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 -- Clean up temp table
559 DELETE FROM po_session_gt
560 WHERE key = l_key;
561
562 -- Close cursor if open
563 IF (req_line_ids_csr%ISOPEN)
564 THEN
565 CLOSE req_line_ids_csr;
566 END IF;
567
568 IF (PO_LOG.d_exc) THEN
569 PO_LOG.exc(d_mod, d_position, 'An error occured in check_po_line_numbers');
570 END IF;
571
572 RAISE;
573 END check_po_line_numbers;
574
575 -------------------------------------------------------------------------------
576 --Start of Comments
577 --Name: lines_match
578 --Pre-reqs:
579 -- None
580 --Modifies:
581 -- None
582 --Locks:
583 -- None
584 --Function:
585 -- Checks whether a requisition line and a requisition/PO line match and
586 -- returns an error message indicating the result.
587 --Parameters:
588 --IN:
589 --p_agreement_id
590 -- The ID of the agreement specified in the document builder.
591 --p_supplier_id
592 -- The ID of the supplier specified in the document builder.
593 --p_site_id
594 -- The ID of the site specified in the document builder.
595 --p_req_line_id
596 -- The ID of the first requisition line to compare with.
597 --p_req_line_id_to_compare
598 -- If the second line to compare with is a requisition line, this variable
599 -- holds the ID of that requisition line. The second line is either a
600 -- requisition or PO line, so this variable may or may not be null.
601 --p_po_line_id_to_compare
602 -- If the second line to compare with is a PO line, this variable holds the ID
603 -- of that PO line. The second line is either a requisition or PO line, so
604 -- this variable may or may not be null.
605 --OUT:
606 --x_message_code
607 -- The error message code corresponding to whether the two lines match or not.
608 --x_token_name
609 -- The error message token name corresponding to whether the two lines match
610 -- or not.
611 --x_token_value
612 -- The error message token value corresponding to whether the two lines match
613 -- or not.
614 --Notes:
615 -- Between the input parameters p_req_line_id_to_compare and
616 -- p_po_line_id_to_compare, one of them has to be null and one of them has to
617 -- have a value.
618 --Testing:
619 -- None
620 --End of Comments
621 -------------------------------------------------------------------------------
622 PROCEDURE lines_match(
623 p_agreement_id IN NUMBER,
624 p_supplier_id IN NUMBER,
625 p_site_id IN NUMBER,
626 p_req_line_id IN NUMBER,
627 p_req_line_id_to_compare IN NUMBER,
628 p_po_line_id_to_compare IN NUMBER,
629 x_message_code OUT NOCOPY VARCHAR2,
630 x_token_name OUT NOCOPY VARCHAR2,
631 x_token_value OUT NOCOPY VARCHAR2
632 )
633 IS
634 d_mod CONSTANT VARCHAR2(100) := D_lines_match;
635 d_position NUMBER := 0;
636
637 l_token_value VARCHAR2(2000);
638 BEGIN
639 IF (PO_LOG.d_proc) THEN
640 PO_LOG.proc_begin(d_mod);
641 PO_LOG.proc_begin(d_mod,'p_agreement_id',p_agreement_id);
642 PO_LOG.proc_begin(d_mod,'p_supplier_id',p_supplier_id);
643 PO_LOG.proc_begin(d_mod,'p_site_id',p_site_id);
644 PO_LOG.proc_begin(d_mod,'p_req_line_id',p_req_line_id);
645 PO_LOG.proc_begin(d_mod,'p_req_line_id_to_compare',p_req_line_id_to_compare);
646 PO_LOG.proc_begin(d_mod,'p_po_line_id_to_compare',p_po_line_id_to_compare);
647 END IF;
648
649 lines_info_match(
650 p_agreement_id,
651 p_req_line_id,
652 p_req_line_id_to_compare,
653 p_po_line_id_to_compare,
654 x_message_code,
655 x_token_name,
656 x_token_value
657 );
658
659 -- If there is a line-level error, no need to check shipment-level attributes
660 IF x_message_code IS NOT NULL THEN
661 RETURN;
662 END IF;
663
664 lines_delivery_info_match(
665 p_supplier_id,
666 p_site_id,
667 p_req_line_id,
668 p_req_line_id_to_compare,
669 p_po_line_id_to_compare,
670 x_message_code,
671 x_token_name,
672 x_token_value
673 );
674
675 IF (PO_LOG.d_proc) THEN
676 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
677 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
678 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
679 END IF;
680 EXCEPTION
681 WHEN OTHERS THEN
682 IF (PO_LOG.d_exc) THEN
683 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_match');
684 END IF;
685
686 RAISE;
687 END lines_match;
688
689 -------------------------------------------------------------------------------
690 --Start of Comments
691 --Name: group_req_lines
692 --Pre-reqs: None
693 --Modifies:
694 --Locks:
695 -- None
696 --Function:
697 -- Defaults the PO line numbers for requisition lines in the Autocreate
698 -- Document Builder based on either the 'Requisition' or 'Default' grouping
699 -- method.
700 --Parameters:
701 --IN:
702 --p_req_line_id_tbl
703 -- Table of requisition line IDs representing the Autocreate Document Builder.
704 --p_req_line_num_tbl
705 -- Table of requisition line numbers for the req lines in the Doc Builder.
706 --p_po_line_num_tbl
707 -- Table of PO line numbers currently assigned to the Document Builder
708 -- requisition lines.
709 --p_add_to_po_header_id
710 -- The ID of the PO to which the requisition lines are being added
711 -- (will be null when creating a new PO).
712 --p_builder_agreement_id
713 -- The ID of the Global Agreement for which the PO will be created.
714 --p_builder_supplier_id
715 -- The ID of the Supplier for which the PO will be created.
716 --p_builder_site_id
717 -- The ID of the Site for which the PO will be created.
718 --p_builder_org_id
719 -- The ID of the Operating Unit for which the PO will be created.
720 --p_start_index
721 -- The index of the first requisition line in the input table which
722 -- should have a PO line number calculated (default value of 1
723 -- if not specified).
724 --p_end_index
725 -- The index of the last requisition line in the input table which
726 -- should have a PO line number calculated (default value of last
727 -- index in the table if not specified).
728 --p_grouping_method
729 -- Grouping method; possible values are 'DEFAULT' or 'REQUISITION'.
730 --Returns:
731 -- Table of PO line numbers corresponding to each of the input requisition
732 -- lines.
733 --Notes:
734 -- N/A
735 --Testing:
736 -- N/A
737 --End of Comments
738 -------------------------------------------------------------------------------
739 FUNCTION group_req_lines
740 (
741 p_req_line_id_tbl IN PO_TBL_NUMBER
742 , p_req_line_num_tbl IN PO_TBL_NUMBER
743 , p_po_line_num_tbl IN PO_TBL_NUMBER
744 , p_add_to_po_header_id IN NUMBER
745 , p_builder_agreement_id IN NUMBER
746 , p_builder_supplier_id IN NUMBER
747 , p_builder_site_id IN NUMBER
748 , p_builder_org_id IN NUMBER
749 , p_start_index IN NUMBER
750 , p_end_index IN NUMBER
751 , p_grouping_method IN VARCHAR2
752 )
753 RETURN PO_TBL_NUMBER
754 IS
755 l_start_index NUMBER;
756 l_end_index NUMBER;
757
758 l_consigned_flag_tbl PO_TBL_VARCHAR1;
759 l_po_line_num_tbl PO_TBL_NUMBER := p_po_line_num_tbl;
760 x_po_line_num_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
761
762 d_mod CONSTANT VARCHAR2(100) := D_group_req_lines;
763 d_position NUMBER := 0;
764
765 BEGIN
766
767 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
768
769 -- Initialize start and end indices to be first and last
770 -- indices in the input req line table (if they were not
771 -- specified in the input parameters).
772 --
773 l_start_index := nvl(p_start_index, 1);
774 l_end_index := nvl(p_end_index, p_req_line_id_tbl.COUNT);
775
776 d_position := 5;
777
778 IF PO_LOG.d_stmt THEN
779 PO_LOG.stmt(d_mod,d_position,'l_start_index',l_start_index);
780 PO_LOG.stmt(d_mod,d_position,'l_end_index',l_end_index);
781 PO_LOG.stmt(d_mod,d_position,'p_grouping_method',p_grouping_method);
782 END IF;
783
784 -- Clear PO line numbers from input table for those indices
785 -- which will be defaulted.
786 --
787 FOR i IN l_start_index..l_end_index LOOP
788 l_po_line_num_tbl(i) := NULL;
789 END LOOP;
790
791 d_position := 10;
792
793 -- Call separate grouping procedures depending on grouping method.
794 --
795 IF ( p_grouping_method = 'REQUISITION' ) THEN
796
797 d_position := 20;
798
799 -- Use req line numbers for the PO line numbers iff...
800 -- (a) "PO: Use Requisition Line Numbers for Autocreate" is set, and
801 -- (b) we are creating a new PO, and
802 -- (c) all req lines in the Doc Builder come from the same req.
803 --
804 IF ( ( FND_PROFILE.value('PO_USE_REQ_NUM_IN_AUTOCREATE') = 'Y' )
805 AND ( p_add_to_po_header_id IS NULL )
806 AND ( has_same_req_header(p_req_line_id_tbl) ) )
807 THEN
808 d_position := 30;
809
810 x_po_line_num_tbl := group_by_requisition_line_num
811 ( p_req_line_num_tbl
812 , l_po_line_num_tbl
813 , l_start_index
814 , l_end_index
815 );
816 -- Else, just sequentially number the req lines starting from
817 -- the max line number currently in the Doc Builder or on the
818 -- PO being added to.
819 --
820 ELSE
821 d_position := 40;
822
823 x_po_line_num_tbl := group_by_requisition_seq_num
824 ( l_po_line_num_tbl
825 , p_add_to_po_header_id
826 , l_start_index
827 , l_end_index
828 );
829 END IF;
830
831 ELSE -- ( p_grouping_method = 'DEFAULT' )
832
833 d_position := 50;
834
835 -- Derive the Consigned Flag for each requisition line. It is too
836 -- difficult to derive the flag in the query, so we will get it
837 -- here by calling an API for each req line.
838 --
839 l_consigned_flag_tbl := get_consigned_flag_tbl ( p_req_line_id_tbl
840 , p_builder_org_id
841 , p_builder_supplier_id
842 , p_builder_site_id
843 );
844 x_po_line_num_tbl := group_by_default ( p_req_line_id_tbl
845 , l_po_line_num_tbl
846 , l_consigned_flag_tbl
847 , p_add_to_po_header_id
848 , p_builder_agreement_id
849 , l_start_index
850 , l_end_index
851 );
852 END IF;
853
854 d_position := 60;
855
856 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,x_po_line_num_tbl); END IF;
857
858 return (x_po_line_num_tbl);
859
860 EXCEPTION
861
862 WHEN OTHERS THEN
863 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
864 RAISE;
865
866 END group_req_lines;
867
868 /*=========================================================================*/
869 /*========================== BODY (PRIVATE) ===============================*/
870 /*=========================================================================*/
871
872 -------------------------------------------------------------------------------
873 --Start of Comments
874 --Name: lines_delivery_info_match
875 --Pre-reqs:
876 -- None
877 --Modifies:
878 -- None
879 --Locks:
880 -- None
881 --Function:
882 -- Gets the delivery info for a requisition line and a requisition/PO line and
883 -- checks whether they match at the delivery info level. Returns an error
884 -- message indicating the result.
885 --Parameters:
886 --IN:
887 --p_supplier_id
888 -- The ID of the supplier specified in the document builder.
889 --p_site_id
890 -- The ID of the site specified in the document builder.
891 --p_req_line_id
892 -- The ID of the first requisition line to compare with.
893 --p_req_line_id_to_compare
894 -- If the second line to compare with is a requisition line, this variable
895 -- holds the ID of that requisition line. The second line is either a
896 -- requisition or PO line, so this variable may or may not be null.
897 --p_po_line_id_to_compare
898 -- If the second line to compare with is a PO line, this variable holds the ID
899 -- of that PO line. The second line is either a requisition or PO line, so
900 -- this variable may or may not be null.
901 --OUT:
902 --x_message_code
903 -- The error message code corresponding to whether the two lines match at the
904 -- delivery info level or not.
905 --x_token_name
906 -- The error message token name corresponding to whether the two lines match
907 -- at the delivery info level or not.
908 --x_token_value
909 -- The error message token value corresponding to whether the two lines match
910 -- at the delivery info level or not.
911 --Notes:
912 -- Between the input parameters p_req_line_id_to_compare and
913 -- p_po_line_id_to_compare, one of them has to be null and one of them has to
914 -- have a value.
915 --Testing:
916 -- None
917 --End of Comments
918 -------------------------------------------------------------------------------
919 PROCEDURE lines_delivery_info_match(
920 p_supplier_id IN NUMBER,
921 p_site_id IN NUMBER,
922 p_req_line_id IN NUMBER,
923 p_req_line_id_to_compare IN NUMBER,
924 p_po_line_id_to_compare IN NUMBER,
925 x_message_code OUT NOCOPY VARCHAR2,
926 x_token_name OUT NOCOPY VARCHAR2,
927 x_token_value OUT NOCOPY VARCHAR2
928 )
929 IS
930 d_mod CONSTANT VARCHAR2(100) := D_lines_delivery_info_match;
931 d_position NUMBER := 0;
932
933 l_token_value VARCHAR2(2000);
934
935 -- Shipment-level attributes
936 l_need_by_grouping_profile VARCHAR2(1);
937 l_ship_to_grouping_profile VARCHAR2(1);
938 delivery_one PO_DELIVERY_INFO_CSR%ROWTYPE;
939 delivery_two PO_DELIVERY_INFO_CSR%ROWTYPE;
940
941 BEGIN
942 IF (PO_LOG.d_proc) THEN
943 PO_LOG.proc_begin(d_mod);
944 PO_LOG.proc_begin(d_mod,'p_supplier_id',p_supplier_id);
945 PO_LOG.proc_begin(d_mod,'p_site_id',p_site_id);
946 PO_LOG.proc_begin(d_mod,'p_req_line_id',p_req_line_id);
947 PO_LOG.proc_begin(d_mod,'p_req_line_id_to_compare',p_req_line_id_to_compare);
948 PO_LOG.proc_begin(d_mod,'p_po_line_id_to_compare',p_po_line_id_to_compare);
949 END IF;
950
951 -- Get shipment-level profiles
952 l_need_by_grouping_profile := fnd_profile.value('PO_NEED_BY_GROUPING');
953 l_ship_to_grouping_profile := fnd_profile.value('PO_SHIPTO_GROUPING');
954
955 -- Retrieve all shipment-level attributes for p_req_line_id
956 delivery_one := get_req_line_delivery_info(
957 p_req_line_id,
958 p_supplier_id,
959 p_site_id
960 );
961
962 IF (p_req_line_id_to_compare IS NOT NULL) -- if second line is a req line
963 THEN
964 -- Retrieve all shipment-level attributes for p_req_line_id_to_compare
965 delivery_two := get_req_line_delivery_info(
966 p_req_line_id_to_compare,
967 p_supplier_id,
968 p_site_id
969 );
970
971 -- Check shipment-level attributes for mismatch
972 check_delivery_info(
973 l_need_by_grouping_profile,
974 l_ship_to_grouping_profile,
975 delivery_one,
976 delivery_two,
977 x_message_code,
978 x_token_name,
979 x_token_value
980 );
981 ELSE -- if second line is a PO line
982 -- Retrieve all shipment-level attributes for p_po_line_id_to_compare
983 OPEN po_delivery_info_csr(p_po_line_id_to_compare);
984 LOOP
985 FETCH po_delivery_info_csr INTO
986 delivery_two;
987 EXIT WHEN po_delivery_info_csr%NOTFOUND;
988
989 -- Check shipment-level attributes for mismatch
990 check_delivery_info(
991 l_need_by_grouping_profile,
992 l_ship_to_grouping_profile,
993 delivery_one,
994 delivery_two,
995 x_message_code,
996 x_token_name,
997 x_token_value
998 );
999
1000 -- Exit the for loop if a matching shipment is found.
1001 IF (x_message_code IS NULL)
1002 THEN
1003 EXIT;
1004 END IF;
1005 END LOOP;
1006 CLOSE po_delivery_info_csr;
1007 END IF; -- if second line is a PO line
1008
1009 IF (PO_LOG.d_proc) THEN
1010 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1011 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1012 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1013 END IF;
1014
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 -- Close cursor if open
1018 IF (po_delivery_info_csr%ISOPEN)
1019 THEN
1020 CLOSE po_delivery_info_csr;
1021 END IF;
1022
1023 IF (PO_LOG.d_exc) THEN
1024 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_delivery_info_match');
1025 END IF;
1026
1027 RAISE;
1028 END lines_delivery_info_match;
1029
1030 -------------------------------------------------------------------------------
1031 --Start of Comments
1032 --Name: check_delivery_info
1033 --Pre-reqs:
1034 -- None
1035 --Modifies:
1036 -- None
1037 --Locks:
1038 -- None
1039 --Function:
1040 -- Checks whether a requisition line and a requisition/PO line match at the
1041 -- delivery info level and returns an error message indicating the result.
1042 --Parameters:
1043 --IN:
1044 --p_need_by_grouping_profile
1045 -- The profile indicating whether need-by-date should be considered when
1046 -- checking if two lines match.
1047 --p_ship_to_grouping_profile
1048 -- The profile indicating whether shipping information should be considered
1049 -- when checking if two lines match.
1050 --p_delivery_one
1051 -- The delivery information for the first line.
1052 --p_delivery_two
1053 -- The delivery information for the second line.
1054 --OUT:
1055 --x_message_code
1056 -- The error message code corresponding to whether the two lines match at the
1057 -- delivery info level or not.
1058 --x_token_name
1059 -- The error message token name corresponding to whether the two lines match
1060 -- at the delivery info level or not.
1061 --x_token_value
1062 -- The error message token value corresponding to whether the two lines match
1063 -- at the delivery info level or not.
1064 --Notes:
1065 -- None
1066 --Testing:
1067 -- None
1068 --End of Comments
1069 -------------------------------------------------------------------------------
1070 PROCEDURE check_delivery_info(
1071 p_need_by_grouping_profile IN VARCHAR2,
1072 p_ship_to_grouping_profile IN VARCHAR2,
1073 p_delivery_one IN PO_DELIVERY_INFO_CSR%ROWTYPE,
1074 p_delivery_two IN PO_DELIVERY_INFO_CSR%ROWTYPE,
1075 x_message_code OUT NOCOPY VARCHAR2,
1076 x_token_name OUT NOCOPY VARCHAR2,
1077 x_token_value OUT NOCOPY VARCHAR2
1078 )
1079 IS
1080 d_mod CONSTANT VARCHAR2(100) := D_check_delivery_info;
1081 d_position NUMBER := 0;
1082 BEGIN
1083 IF (PO_LOG.d_proc) THEN
1084 PO_LOG.proc_begin(d_mod);
1085 PO_LOG.proc_begin(d_mod,'p_need_by_grouping_profile',p_need_by_grouping_profile);
1086 PO_LOG.proc_begin(d_mod,'p_ship_to_grouping_profile',p_ship_to_grouping_profile);
1087 END IF;
1088
1089 -- Initialize message code, token name, and token value to NULL
1090 x_message_code := NULL;
1091 x_token_name := NULL;
1092 x_token_value := NULL;
1093
1094 -- Check need-by date attributes
1095 IF ((NVL(p_need_by_grouping_profile, 'Y') = 'Y')
1096 AND NOT PO_CORE_S.is_equal_minutes(
1097 p_delivery_one.need_by_date,
1098 p_delivery_two.need_by_date))
1099 THEN
1100 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1101 x_token_name := 'REASON_FOR_DIFF';
1102 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_NEED_BY');
1103
1104 IF (PO_LOG.d_proc) THEN
1105 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1106 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1107 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1108 END IF;
1109
1110 RETURN;
1111 END IF;
1112
1113 -- Check ship-to attributes
1114 IF (NVL(p_ship_to_grouping_profile, 'Y') = 'Y')
1115 THEN
1116 -- Check ship-to organization ID attributes
1117 IF (NOT PO_CORE_S.is_equal(
1118 p_delivery_one.ship_to_organization_id,
1119 p_delivery_two.ship_to_organization_id))
1120 THEN
1121 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1122 x_token_name := 'REASON_FOR_DIFF';
1123 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_DEL_ORG');
1124
1125 IF (PO_LOG.d_proc) THEN
1126 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1127 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1128 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1129 END IF;
1130
1131 RETURN;
1132 END IF;
1133
1134 -- Check ship-to location ID attributes
1135 IF (NOT PO_CORE_S.is_equal(
1136 p_delivery_one.ship_to_location_id,
1137 p_delivery_two.ship_to_location_id))
1138 THEN
1139 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1140 x_token_name := 'REASON_FOR_DIFF';
1141 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_DEL_LOC');
1142
1143 IF (PO_LOG.d_proc) THEN
1144 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1145 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1146 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1147 END IF;
1148
1149 RETURN;
1150 END IF;
1151 END IF;
1152
1153 -- Check consigned flag attributes which are set to 'N' if NULL
1154 IF (NOT PO_CORE_S.is_equal(
1155 NVL(p_delivery_one.consigned_flag, 'N'),
1156 NVL(p_delivery_two.consigned_flag, 'N')))
1157 THEN
1158 x_message_code := 'PO_ALL_CANT_COMB_CONSIGNED';
1159 x_token_name := NULL;
1160 x_token_value := NULL;
1161
1162 IF (PO_LOG.d_proc) THEN
1163 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1164 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1165 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1166 END IF;
1167
1168 RETURN;
1169 END IF;
1170
1171 IF (PO_LOG.d_proc) THEN
1172 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1173 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1174 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1175 END IF;
1176
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 IF (PO_LOG.d_exc) THEN
1180 PO_LOG.exc(d_mod, d_position, 'An error occured in check_delivery_info');
1181 END IF;
1182
1183 RAISE;
1184 END check_delivery_info;
1185
1186 -------------------------------------------------------------------------------
1187 --Start of Comments
1188 --Name: get_req_line_delivery_info
1189 --Pre-reqs:
1190 -- None
1191 --Modifies:
1192 -- None
1193 --Locks:
1194 -- None
1195 --Function:
1196 -- Gets the delivery info for a requisition line.
1197 --Parameters:
1198 --IN:
1199 --p_req_line_id
1200 -- The ID of the requisition line to get the delivery info of.
1201 --p_supplier_id
1202 -- The ID of the supplier specified in the document builder.
1203 --p_site_id
1204 -- The ID of the site specified in the document builder.
1205 --RETURNS:
1206 -- The delivery info for the req line.
1207 --Notes:
1208 -- None
1209 --Testing:
1210 -- None
1211 --End of Comments
1212 -------------------------------------------------------------------------------
1213 FUNCTION get_req_line_delivery_info(
1214 p_req_line_id IN NUMBER,
1215 p_supplier_id IN NUMBER,
1216 p_site_id IN NUMBER
1217 ) RETURN PO_DELIVERY_INFO_CSR%ROWTYPE
1218 IS
1219 d_mod CONSTANT VARCHAR2(100) := D_get_req_line_delivery_info;
1220 d_position NUMBER := 0;
1221
1222 l_req_line_delivery_info PO_DELIVERY_INFO_CSR%ROWTYPE;
1223 l_item_id PO_REQUISITION_LINES_ALL.item_id%TYPE;
1224 BEGIN
1225 IF (PO_LOG.d_proc) THEN
1226 PO_LOG.proc_begin(d_mod);
1227 PO_LOG.proc_begin(d_mod,'p_req_line_id',p_req_line_id);
1228 PO_LOG.proc_begin(d_mod,'p_supplier_id',p_supplier_id);
1229 PO_LOG.proc_begin(d_mod,'p_site_id',p_site_id);
1230 END IF;
1231
1232 SELECT PRL.item_id,
1233 PRL.need_by_date,
1234 PRL.destination_organization_id,
1235 PRL.deliver_to_location_id
1236 INTO l_item_id,
1237 l_req_line_delivery_info.need_by_date,
1238 l_req_line_delivery_info.ship_to_organization_id,
1239 l_req_line_delivery_info.ship_to_location_id
1240 FROM po_requisition_lines_all PRL
1241 WHERE PRL.requisition_line_id = p_req_line_id;
1242
1243 l_req_line_delivery_info.consigned_flag :=
1244 PO_THIRD_PARTY_STOCK_GRP.get_consigned_flag(
1245 NULL, --bug 5976612
1246 l_item_id, -- p_item_id
1247 p_supplier_id, -- p_supplier_id
1248 p_site_id, -- p_site_id
1249 l_req_line_delivery_info.ship_to_organization_id -- p_inv_org_id --bug 5976612
1250 );
1251
1252 /* Bug 5976612
1253 Added the 'NULL' parameter in the beginning in the place of org id.
1254 Moved the l_req_line_delivery_info.ship_to_organization_id parameter to end. This is inventory org id.
1255 These changes are driven by the changes done to the function PO_THIRD_PARTY_STOCK_GRP.get_consigned_flag. */
1256
1257 IF (PO_LOG.d_proc) THEN
1258 PO_LOG.proc_end(d_mod);
1259 END IF;
1260
1261 RETURN l_req_line_delivery_info;
1262
1263 EXCEPTION
1264 WHEN OTHERS THEN
1265 IF (PO_LOG.d_exc) THEN
1266 PO_LOG.exc(d_mod, d_position, 'An error occured in get_req_line_delivery_info');
1267 END IF;
1268
1269 RAISE;
1270 END get_req_line_delivery_info;
1271
1272 -------------------------------------------------------------------------------
1273 --Start of Comments
1274 --Name: lines_info_match
1275 --Pre-reqs:
1276 -- None
1277 --Modifies:
1278 -- None
1279 --Locks:
1280 -- None
1281 --Function:
1282 -- Checks whether a requisition line and a requisition/PO line match at the
1283 -- line info level and returns an error message indicating the result.
1284 --Parameters:
1285 --IN:
1286 --p_agreement_id
1287 -- The ID of the agreement specified in the document builder.
1288 --p_req_line_id
1289 -- The ID of the first requisition line to compare with.
1290 --p_req_line_id_to_compare
1291 -- If the second line to compare with is a requisition line, this variable
1292 -- holds the ID of that requisition line. The second line is either a
1293 -- requisition or PO line, so this variable may or may not be null.
1294 --p_po_line_id_to_compare
1295 -- If the second line to compare with is a PO line, this variable holds the ID
1296 -- of that PO line. The second line is either a requisition or PO line, so
1297 -- this variable may or may not be null.
1298 --OUT:
1299 --x_message_code
1300 -- The error message code corresponding to whether the two lines match at the
1301 -- line info level or not.
1302 --x_token_name
1303 -- The error message token name corresponding to whether the two lines match
1304 -- at the line info level or not.
1305 --x_token_value
1306 -- The error message token value corresponding to whether the two lines match
1307 -- at the line info level or not.
1308 --Notes:
1309 -- Between the input parameters p_req_line_id_to_compare and
1310 -- p_po_line_id_to_compare, one of them has to be null and one of them has to
1311 -- have a value.
1312 --Testing:
1313 -- None
1314 --End of Comments
1315 -------------------------------------------------------------------------------
1316 PROCEDURE lines_info_match(
1317 p_agreement_id IN NUMBER,
1318 p_req_line_id IN NUMBER,
1319 p_req_line_id_to_compare IN NUMBER,
1320 p_po_line_id_to_compare IN NUMBER,
1321 x_message_code OUT NOCOPY VARCHAR2,
1322 x_token_name OUT NOCOPY VARCHAR2,
1323 x_token_value OUT NOCOPY VARCHAR2
1324 )
1325 IS
1326 d_mod CONSTANT VARCHAR2(100) := D_lines_info_match;
1327 d_position NUMBER := 0;
1328
1329 l_token_value VARCHAR2(2000);
1330
1331 l_line_one LINE_INFO;
1332 l_line_two LINE_INFO;
1333 BEGIN
1334 IF (PO_LOG.d_proc) THEN
1335 PO_LOG.proc_begin(d_mod);
1336 PO_LOG.proc_begin(d_mod,'p_agreement_id',p_agreement_id);
1337 PO_LOG.proc_begin(d_mod,'p_req_line_id',p_req_line_id);
1338 PO_LOG.proc_begin(d_mod,'p_req_line_id_to_compare',p_req_line_id_to_compare);
1339 PO_LOG.proc_begin(d_mod,'p_po_line_id_to_compare',p_po_line_id_to_compare);
1340 END IF;
1341
1342 -- Retrieve all lines matching attributes for p_req_line_id
1343 l_line_one := get_req_line_info(p_req_line_id);
1344
1345 -- Retrieve lines matching attributes for second line
1346 IF (p_req_line_id_to_compare IS NOT NULL) -- if second line is a req line
1347 THEN
1348 -- Retrieve all lines matching attributes for p_req_line_id_to_compare
1349 l_line_two := get_req_line_info(p_req_line_id_to_compare);
1350 ELSE -- if second line is a PO line
1351 -- Retrieve all line-level attributes for p_po_line_id_to_compare
1352 l_line_two := get_po_line_info(p_po_line_id_to_compare);
1353 END IF;
1354
1355 -- Check line-level attributes
1356 check_line_info(
1357 p_agreement_id,
1358 l_line_one,
1359 l_line_two,
1360 x_message_code,
1361 x_token_name,
1362 x_token_value
1363 );
1364
1365 IF (PO_LOG.d_proc) THEN
1366 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1367 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1368 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1369 END IF;
1370
1371 EXCEPTION
1372 WHEN OTHERS THEN
1373 IF (PO_LOG.d_exc) THEN
1374 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_info_match');
1375 END IF;
1376
1377 RAISE;
1378 END lines_info_match;
1379
1380 -------------------------------------------------------------------------------
1381 --Start of Comments
1382 --Name: get_req_line_info
1383 --Pre-reqs:
1384 -- None
1385 --Modifies:
1386 -- None
1387 --Locks:
1388 -- None
1389 --Function:
1390 -- Gets the line info for a requisition line.
1391 --Parameters:
1392 --IN:
1393 --p_req_line_id
1394 -- The ID of the requisition line to get the line info of.
1395 --RETURNS:
1396 -- The line info for the req line.
1397 --Notes:
1398 -- None
1399 --Testing:
1400 -- None
1401 --End of Comments
1402 -------------------------------------------------------------------------------
1403 FUNCTION get_req_line_info(p_req_line_id IN NUMBER) RETURN LINE_INFO
1404 IS
1405 d_mod CONSTANT VARCHAR2(100) := D_get_req_line_info;
1406 d_position NUMBER := 0;
1407
1408 l_req_line_info LINE_INFO;
1409 BEGIN
1410 IF (PO_LOG.d_proc) THEN
1411 PO_LOG.proc_begin(d_mod);
1412 PO_LOG.proc_begin(d_mod,'p_req_line_id',p_req_line_id);
1413 END IF;
1414
1415 SELECT PRL.item_id,
1416 PRL.item_description,
1417 PRL.item_revision,
1418 PRL.order_type_lookup_code,
1419 PRL.purchase_basis,
1420 PRL.matching_basis,
1421 PRL.preferred_grade,
1422 PRL.unit_meas_lookup_code,
1423 PRL.transaction_reason_code,
1424 DECODE(
1425 PRL.document_type_code,
1426 'CONTRACT',
1427 PRL.blanket_po_header_id,
1428 NULL
1429 ), -- contract ID
1430 DECODE(
1431 PRL.document_type_code,
1432 'CONTRACT',
1433 NULL,
1434 PRL.blanket_po_header_id
1435 ), -- source document ID
1436 DECODE(
1437 PRL.document_type_code,
1438 'CONTRACT',
1439 NULL,
1440 SRC_DOC_LINE.po_line_id
1441 ), -- source document line ID
1442 NULL, -- cancel flag N/A for req line
1443 NULL, -- closed code N/A for req line
1444 PRL.supplier_ref_number
1445 INTO l_req_line_info
1446 FROM po_requisition_lines_all PRL,
1447 po_lines_all SRC_DOC_LINE
1448 WHERE PRL.requisition_line_id = p_req_line_id
1449 AND SRC_DOC_LINE.po_header_id(+) = PRL.blanket_po_header_id
1450 AND SRC_DOC_LINE.line_num(+) = PRL.blanket_po_line_num;
1451
1452 IF (PO_LOG.d_proc) THEN
1453 PO_LOG.proc_end(d_mod);
1454 END IF;
1455
1456 RETURN l_req_line_info;
1457
1458 EXCEPTION
1459 WHEN OTHERS THEN
1460 IF (PO_LOG.d_exc) THEN
1461 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_info_match');
1462 END IF;
1463
1464 RAISE;
1465 END get_req_line_info;
1466
1467 -------------------------------------------------------------------------------
1468 --Start of Comments
1469 --Name: get_po_line_info
1470 --Pre-reqs:
1471 -- None
1472 --Modifies:
1473 -- None
1474 --Locks:
1475 -- None
1476 --Function:
1477 -- Gets the line info for a PO line.
1478 --Parameters:
1479 --IN:
1480 --p_po_line_id
1481 -- The ID of the PO line to get the line info of.
1482 --RETURNS:
1483 -- The line info for the PO line.
1484 --Notes:
1485 -- None
1486 --Testing:
1487 -- None
1488 --End of Comments
1489 -------------------------------------------------------------------------------
1490 FUNCTION get_po_line_info(p_po_line_id IN NUMBER) RETURN LINE_INFO
1491 IS
1492 d_mod CONSTANT VARCHAR2(100) := D_get_po_line_info;
1493 d_position NUMBER := 0;
1494
1495 l_po_line_info LINE_INFO;
1496 BEGIN
1497 IF (PO_LOG.d_proc) THEN
1498 PO_LOG.proc_begin(d_mod);
1499 PO_LOG.proc_begin(d_mod,'p_po_line_id',p_po_line_id);
1500 END IF;
1501
1502 SELECT item_id,
1503 item_description,
1504 item_revision,
1505 order_type_lookup_code,
1506 purchase_basis,
1507 matching_basis,
1508 preferred_grade,
1509 unit_meas_lookup_code,
1510 transaction_reason_code,
1511 contract_id,
1512 from_header_id, -- source document ID
1513 from_line_id, -- source document line ID
1514 cancel_flag,
1515 closed_code,
1516 supplier_ref_number
1517 INTO l_po_line_info
1518 FROM po_lines_all
1519 WHERE po_line_id = p_po_line_id;
1520
1521 IF (PO_LOG.d_proc) THEN
1522 PO_LOG.proc_end(d_mod);
1523 END IF;
1524
1525 RETURN l_po_line_info;
1526
1527 EXCEPTION
1528 WHEN OTHERS THEN
1529 IF (PO_LOG.d_exc) THEN
1530 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_info_match');
1531 END IF;
1532
1533 RAISE;
1534 END get_po_line_info;
1535
1536 -------------------------------------------------------------------------------
1537 --Start of Comments
1538 --Name: check_line_info
1539 --Pre-reqs:
1540 -- None
1541 --Modifies:
1542 -- None
1543 --Locks:
1544 -- None
1545 --Function:
1546 -- Checks whether a requisition line and a requisition/PO line match at the
1547 -- line info level and returns an error message indicating the result.
1548 --Parameters:
1549 --IN:
1550 --p_agreement_id
1551 -- The ID of the agreement specified in the document builder.
1552 --p_line_one
1553 -- The line information for the first line.
1554 --p_line_two
1555 -- The line information for the second line.
1556 --OUT:
1557 --x_message_code
1558 -- The error message code corresponding to whether the two lines match at the
1559 -- line info level or not.
1560 --x_token_name
1561 -- The error message token name corresponding to whether the two lines match
1562 -- at the line info level or not.
1563 --x_token_value
1564 -- The error message token value corresponding to whether the two lines match
1565 -- at the line info level or not.
1566 --Notes:
1567 -- None
1568 --Testing:
1569 -- None
1570 --End of Comments
1571 -------------------------------------------------------------------------------
1572 PROCEDURE check_line_info(
1573 p_agreement_id IN NUMBER,
1574 p_line_one IN LINE_INFO,
1575 p_line_two IN LINE_INFO,
1576 x_message_code OUT NOCOPY VARCHAR2,
1577 x_token_name OUT NOCOPY VARCHAR2,
1578 x_token_value OUT NOCOPY VARCHAR2
1579 )
1580 IS
1581 d_mod CONSTANT VARCHAR2(100) := D_check_line_info;
1582 d_position NUMBER := 0;
1583 BEGIN
1584 IF (PO_LOG.d_proc) THEN
1585 PO_LOG.proc_begin(d_mod);
1586 PO_LOG.proc_begin(d_mod,'p_agreement_id',p_agreement_id);
1587 END IF;
1588
1589 -- Initialize message code, token name, and token value to NULL
1590 x_message_code := NULL;
1591 x_token_name := NULL;
1592 x_token_value := NULL;
1593
1594 -- Check line-level attributes. If any mismatches are found, set error
1595 -- message values and skip the rest of the checks.
1596 IF (NOT PO_CORE_S.is_equal(p_line_one.item_id, p_line_two.item_id)
1597 OR (p_line_one.item_id IS NULL AND p_line_two.item_id IS NULL
1598 AND NOT PO_CORE_S.is_equal(
1599 p_line_one.item_description,
1600 p_line_two.item_description)))
1601 THEN
1602 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1603 x_token_name := 'REASON_FOR_DIFF';
1604 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_ITEMS');
1605
1606 IF (PO_LOG.d_proc) THEN
1607 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1608 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1609 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1610 END IF;
1611
1612 RETURN;
1613 END IF;
1614
1615 IF (NOT PO_CORE_S.is_equal(
1616 p_line_one.item_revision,
1617 p_line_two.item_revision))
1618 THEN
1619 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1620 x_token_name := 'REASON_FOR_DIFF';
1621 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_ITEM_REVISION');
1622
1623 IF (PO_LOG.d_proc) THEN
1624 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1625 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1626 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1627 END IF;
1628
1629 RETURN;
1630 END IF;
1631
1632 --BUG 5641147 : Donot group for RATE and FIXED PRICE lines
1633 IF p_line_two.order_type_lookup_code IN ('RATE','FIXED PRICE')
1634 OR
1635 (NOT PO_CORE_S.is_equal(
1636 p_line_one.order_type_lookup_code,
1637 p_line_two.order_type_lookup_code
1638 )
1639 OR NOT PO_CORE_S.is_equal(
1640 p_line_one.purchase_basis,
1641 p_line_two.purchase_basis
1642 )
1643 OR NOT PO_CORE_S.is_equal(
1644 p_line_one.matching_basis,
1645 p_line_two.matching_basis))
1646 THEN
1647 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1648 x_token_name := 'REASON_FOR_DIFF';
1649 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_LINE_TYPE');
1650
1651 IF (PO_LOG.d_proc) THEN
1652 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1653 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1654 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1655 END IF;
1656
1657 RETURN;
1658 END IF;
1659
1660 IF (NOT PO_CORE_S.is_equal(
1661 p_line_one.preferred_grade,
1662 p_line_two.preferred_grade))
1663 THEN
1664 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1665 x_token_name := 'REASON_FOR_DIFF';
1666 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_PREF_GRADE');
1667
1668 IF (PO_LOG.d_proc) THEN
1669 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1670 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1671 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1672 END IF;
1673
1674 RETURN;
1675 END IF;
1676
1677 IF (NOT PO_CORE_S.is_equal(
1678 p_line_one.unit_meas_lookup_code,
1679 p_line_two.unit_meas_lookup_code))
1680 THEN
1681 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1682 x_token_name := 'REASON_FOR_DIFF';
1683 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_PRI_UOM');
1684
1685 IF (PO_LOG.d_proc) THEN
1686 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1687 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1688 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1689 END IF;
1690
1691 RETURN;
1692 END IF;
1693
1694 IF (NOT PO_CORE_S.is_equal(
1695 p_line_one.transaction_reason,
1696 p_line_two.transaction_reason))
1697 THEN
1698 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1699 x_token_name := 'REASON_FOR_DIFF';
1700 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_TRANS_REASON');
1701
1702 IF (PO_LOG.d_proc) THEN
1703 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1704 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1705 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1706 END IF;
1707
1708 RETURN;
1709 END IF;
1710
1711 IF (NOT PO_CORE_S.is_equal(p_line_one.contract_id, p_line_two.contract_id))
1712 THEN
1713 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1714 x_token_name := 'REASON_FOR_DIFF';
1715 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_CONTRACT');
1716
1717 IF (PO_LOG.d_proc) THEN
1718 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1719 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1720 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1721 END IF;
1722
1723 RETURN;
1724 END IF;
1725
1726 IF (p_agreement_id IS NULL
1727 AND (NOT PO_CORE_S.is_equal(
1728 p_line_one.source_document_id,
1729 p_line_two.source_document_id
1730 )
1731 OR NOT PO_CORE_S.is_equal(
1732 p_line_one.source_document_line_id,
1733 p_line_two.source_document_line_id)))
1734 THEN
1735 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1736 x_token_name := 'REASON_FOR_DIFF';
1737 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_SRC_NUM');
1738
1739 IF (PO_LOG.d_proc) THEN
1740 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1741 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1742 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1743 END IF;
1744
1745 RETURN;
1746 END IF;
1747
1748 IF (p_line_two.cancel_flag = 'Y')
1749 THEN
1750 x_message_code := 'PO_ALL_CANT_COMB_CANCLD_LINE';
1751 x_token_name := NULL;
1752 x_token_value := NULL;
1753
1754 IF (PO_LOG.d_proc) THEN
1755 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1756 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1757 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1758 END IF;
1759
1760 RETURN;
1761 END IF;
1762
1763 IF (p_line_two.closed_code = 'FINALLY CLOSED')
1764 THEN
1765 x_message_code := 'PO_ALL_CANT_COMB_FCLOSED_LINE';
1766 x_token_name := NULL;
1767 x_token_value := NULL;
1768
1769 IF (PO_LOG.d_proc) THEN
1770 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1771 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1772 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1773 END IF;
1774
1775 RETURN;
1776 END IF;
1777
1778 IF (NOT PO_CORE_S.is_equal(
1779 p_line_one.supplier_ref_number,
1780 p_line_two.supplier_ref_number))
1781 THEN
1782 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
1783 x_token_name := 'REASON_FOR_DIFF';
1784 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_SUPPLIER_REF_NUMBER');
1785
1786 IF (PO_LOG.d_proc) THEN
1787 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1788 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1789 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1790 END IF;
1791
1792 RETURN;
1793 END IF;
1794
1795 IF (PO_LOG.d_proc) THEN
1796 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1797 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1798 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1799 END IF;
1800
1801 EXCEPTION
1802 WHEN OTHERS THEN
1803 IF (PO_LOG.d_exc) THEN
1804 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_info_match');
1805 END IF;
1806
1807 RAISE;
1808 END check_line_info;
1809
1810 -------------------------------------------------------------------------------
1811 --Start of Comments
1812 --Name: group_by_requisition_line_num
1813 --Pre-reqs: None
1814 --Modifies:
1815 --Locks:
1816 -- None
1817 --Function:
1818 -- Perform 'REQUISITION' grouping using the requisition line numbers.
1819 -- If the req line number has already been used as the PO line number
1820 -- for a previous req line, then NULL will be assigned.
1821 --Parameters:
1822 --IN:
1823 --p_req_line_num_tbl
1824 -- Table of requisition line numbers for the req lines in the Doc Builder.
1825 --p_po_line_num_tbl
1826 -- Table of PO line numbers currently assigned to the Document Builder
1827 -- requisition lines.
1828 --p_start_index
1829 -- The index of the first requisition line in the input table which
1830 -- should have a PO line number calculated (default value of 1
1831 -- if not specified).
1832 --p_end_index
1833 -- The index of the last requisition line in the input table which
1834 -- should have a PO line number calculated (default value of last
1835 -- index in the table if not specified).
1836 --Returns:
1837 -- Table of PO line numbers corresponding to each of the input requisition
1838 -- lines.
1839 --Notes:
1840 -- N/A
1841 --Testing:
1842 -- N/A
1843 --End of Comments
1844 -------------------------------------------------------------------------------
1845 FUNCTION group_by_requisition_line_num
1846 (
1847 p_req_line_num_tbl IN PO_TBL_NUMBER
1848 , p_po_line_num_tbl IN PO_TBL_NUMBER
1849 , p_start_index IN NUMBER
1850 , p_end_index IN NUMBER
1851 )
1852 RETURN PO_TBL_NUMBER
1853 IS
1854 x_po_line_num_tbl PO_TBL_NUMBER := p_po_line_num_tbl;
1855
1856 TYPE NUM_INDEX_TBL_TYPE IS TABLE OF VARCHAR2(1) INDEX BY PLS_INTEGER;
1857 l_line_num_used_tbl NUM_INDEX_TBL_TYPE;
1858
1859 d_mod CONSTANT VARCHAR2(100) := D_group_by_requisition_line;
1860 d_position NUMBER := 0;
1861
1862 BEGIN
1863
1864 IF PO_LOG.d_proc THEN
1865 PO_LOG.proc_begin(d_mod,'p_req_line_num_tbl',p_req_line_num_tbl);
1866 PO_LOG.proc_begin(d_mod,'p_po_line_num_tbl',p_po_line_num_tbl);
1867 PO_LOG.proc_begin(d_mod,'p_start_index',p_start_index);
1868 PO_LOG.proc_begin(d_mod,'p_end_index',p_end_index);
1869 END IF;
1870
1871 -- Initialize the l_line_num_used_tbl to indicate which line
1872 -- numbers are already assigned. We will mark a 'Y' in the associative
1873 -- array at the index with the value of the used line number.
1874 --
1875 FOR i IN 1..(p_start_index - 1)
1876 LOOP
1877 IF ( p_po_line_num_tbl(i) IS NOT NULL )
1878 THEN
1879 l_line_num_used_tbl(p_po_line_num_tbl(i)) := 'Y';
1880 END IF;
1881 END LOOP;
1882
1883 d_position := 10;
1884
1885 -- Loop through and assign req line numbers as the PO line numbers
1886 -- if no other req line before already has that PO line number
1887 -- assigned.
1888 --
1889 FOR i IN p_start_index..p_end_index
1890 LOOP
1891 IF ( l_line_num_used_tbl.EXISTS(p_req_line_num_tbl(i)) )
1892 THEN
1893 x_po_line_num_tbl(i) := NULL;
1894 ELSE
1895 x_po_line_num_tbl(i) := p_req_line_num_tbl(i);
1896 l_line_num_used_tbl(x_po_line_num_tbl(i)) := 'Y';
1897 END IF;
1898 END LOOP;
1899
1900 d_position := 50;
1901 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,x_po_line_num_tbl); END IF;
1902
1903 return (x_po_line_num_tbl);
1904
1905 EXCEPTION
1906
1907 WHEN OTHERS THEN
1908 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
1909 RAISE;
1910
1911 END group_by_requisition_line_num;
1912
1913 -------------------------------------------------------------------------------
1914 --Start of Comments
1915 --Name: group_by_requisition_seq_num
1916 --Pre-reqs: None
1917 --Modifies:
1918 --Locks:
1919 -- None
1920 --Function:
1921 -- Perform 'REQUISITION' grouping using the sequential line numbers
1922 -- starting after the maximum line number currently in the Doc Builder
1923 -- or on the PO being added to.
1924 --Parameters:
1925 --IN:
1926 --p_po_line_num_tbl
1927 -- Table of PO line numbers currently assigned to the Document Builder
1928 -- requisition lines.
1929 --p_add_to_po_header_id
1930 -- The ID of the PO to which the requisition lines are being added
1931 -- (will be null when creating a new PO).
1932 --p_start_index
1933 -- The index of the first requisition line in the input table which
1934 -- should have a PO line number calculated (default value of 1
1935 -- if not specified).
1936 --p_end_index
1937 -- The index of the last requisition line in the input table which
1938 -- should have a PO line number calculated (default value of last
1939 -- index in the table if not specified).
1940 --Returns:
1941 -- Table of PO line numbers corresponding to each of the input requisition
1942 -- lines.
1943 --Notes:
1944 -- N/A
1945 --Testing:
1946 -- N/A
1947 --End of Comments
1948 -------------------------------------------------------------------------------
1949 FUNCTION group_by_requisition_seq_num
1950 (
1951 p_po_line_num_tbl IN PO_TBL_NUMBER
1952 , p_add_to_po_header_id IN NUMBER
1953 , p_start_index IN NUMBER
1954 , p_end_index IN NUMBER
1955 )
1956 RETURN PO_TBL_NUMBER
1957 IS
1958 x_po_line_num_tbl PO_TBL_NUMBER := p_po_line_num_tbl;
1959
1960 l_max_line_num NUMBER;
1961 l_next_line_num NUMBER;
1962
1963 d_mod CONSTANT VARCHAR2(100) := D_group_by_requisition_seq_num;
1964 d_position NUMBER := 0;
1965
1966 BEGIN
1967
1968 -- Get the max line number from either the PO or the other
1969 -- lines in the Document Builder.
1970 --
1971 l_max_line_num := get_max_po_line_num ( p_po_line_num_tbl
1972 , p_add_to_po_header_id );
1973
1974 -- Loop through and assign line numbers sequentially to each
1975 -- req line starting from the max line number derived above.
1976 --
1977 l_next_line_num := l_max_line_num + 1;
1978
1979 FOR i IN p_start_index..p_end_index
1980 LOOP
1981 x_po_line_num_tbl(i) := l_next_line_num;
1982 l_next_line_num := l_next_line_num + 1;
1983 END LOOP;
1984
1985 return (x_po_line_num_tbl);
1986
1987 EXCEPTION
1988
1989 WHEN OTHERS THEN
1990 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
1991 RAISE;
1992
1993 END group_by_requisition_seq_num;
1994
1995 -------------------------------------------------------------------------------
1996 --Start of Comments
1997 --Name: group_by_default
1998 --Pre-reqs: None
1999 --Modifies:
2000 --Locks:
2001 -- None
2002 --Function:
2003 -- Defaults the PO line numbers for requisition lines in the Autocreate
2004 -- Document Builder using the 'Default' grouping method.
2005 --Parameters:
2006 --IN:
2007 --p_req_line_id_tbl
2008 -- Table of requisition line IDs representing the Autocreate Document Builder.
2009 --p_po_line_num_tbl
2010 -- Table of PO line numbers currently assigned to the Document Builder
2011 -- requisition lines. The PO line numbers for req lines within the
2012 -- start and end index (i.e. those for which defaulting will be performed)
2013 -- must be nulled out.
2014 --p_consigned_flag_tbl
2015 -- Table of Consigned Flag values corresponding to each req line in
2016 -- p_req_line_id_tbl.
2017 --p_add_to_po_header_id
2018 -- The ID of the PO to which the requisition lines are being added
2019 -- (will be null when creating a new PO).
2020 --p_builder_agreement_id
2021 -- The ID of the Global Agreement for which the PO will be created.
2022 --p_start_index
2023 -- The index of the first requisition line in the input table which
2024 -- should have a PO line number calculated (default value of 1
2025 -- if not specified).
2026 --p_end_index
2027 -- The index of the last requisition line in the input table which
2028 -- should have a PO line number calculated (default value of last
2029 -- index in the table if not specified).
2030 --Returns:
2031 -- Table of PO line numbers corresponding to each of the input requisition
2032 -- lines.
2033 --Notes:
2034 -- N/A
2035 --Testing:
2036 -- N/A
2037 --End of Comments
2038 -------------------------------------------------------------------------------
2039 FUNCTION group_by_default
2040 (
2041 p_req_line_id_tbl IN PO_TBL_NUMBER
2042 , p_po_line_num_tbl IN PO_TBL_NUMBER
2043 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
2044 , p_add_to_po_header_id IN NUMBER
2045 , p_builder_agreement_id IN NUMBER
2046 , p_start_index IN NUMBER
2047 , p_end_index IN NUMBER
2048 )
2049 RETURN PO_TBL_NUMBER
2050 IS
2051 l_max_line_num NUMBER := 0;
2052 l_matching_index NUMBER;
2053 l_po_line_num NUMBER;
2054 x_po_line_num_tbl PO_TBL_NUMBER := p_po_line_num_tbl;
2055
2056 l_add_to_po_req_line_id_tbl PO_TBL_NUMBER;
2057 l_add_to_po_line_num_tbl PO_TBL_NUMBER;
2058
2059 d_mod CONSTANT VARCHAR2(100) := D_group_by_default;
2060 d_position NUMBER := 0;
2061
2062 BEGIN
2063
2064 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
2065
2066 -- Initialize the max PO line number.
2067 --
2068 l_max_line_num := get_max_po_line_num ( p_po_line_num_tbl
2069 , p_add_to_po_header_id );
2070
2071 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'l_max_line_num',l_max_line_num); END IF;
2072
2073 d_position := 10;
2074
2075 -- Get nested tables of requisition lines and their corresponding
2076 -- matching PO line numbers from the PO being added to.
2077 --
2078 match_add_to_po_lines ( p_req_line_id_tbl
2079 , p_consigned_flag_tbl
2080 , p_add_to_po_header_id
2081 , p_builder_agreement_id
2082 , p_start_index
2083 , p_end_index
2084 , l_add_to_po_req_line_id_tbl -- OUT
2085 , l_add_to_po_line_num_tbl -- OUT
2086 );
2087 d_position := 20;
2088
2089 -- Loop through input nested table of req lines.
2090 --
2091 FOR i IN p_start_index..p_end_index LOOP
2092
2093 l_po_line_num := NULL;
2094
2095 d_position := 30;
2096
2097 -- Find a matching line on the PO being added to (if one exists).
2098 --
2099 IF ( p_add_to_po_header_id IS NOT NULL )
2100 THEN
2101 l_po_line_num := find_matching_po_line_num
2102 ( p_req_line_id => p_req_line_id_tbl(i)
2103 , p_comparison_tbl => l_add_to_po_req_line_id_tbl
2104 , p_po_line_num_tbl => l_add_to_po_line_num_tbl
2105 );
2106 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'Add To PO Line Num',l_po_line_num); END IF;
2107 END IF;
2108
2109 d_position := 40;
2110
2111 -- If no line was found on the PO being added to, search for any
2112 -- matching lines before the current line in the Doc Builder.
2113 --
2114 IF ( l_po_line_num IS NULL )
2115 THEN
2116 l_po_line_num := find_matching_builder_line_num
2117 ( p_current_index => i
2118 , p_req_line_id_tbl => p_req_line_id_tbl
2119 , p_po_line_num_tbl => x_po_line_num_tbl
2120 , p_builder_agreement_id => p_builder_agreement_id
2121 );
2122 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'Builder Line Num',l_po_line_num); END IF;
2123 END IF;
2124
2125 d_position := 50;
2126
2127 -- If no matching lines found in the PO or the Doc Builder,
2128 -- set the PO line num to the next line number and then set
2129 -- the new max line number.
2130 --
2131 IF ( l_po_line_num IS NULL )
2132 THEN
2133 l_max_line_num := l_max_line_num + 1;
2134 l_po_line_num := l_max_line_num;
2135
2136 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'Max Line Num',l_po_line_num); END IF;
2137 END IF;
2138
2139 d_position := 60;
2140
2141 x_po_line_num_tbl(i) := l_po_line_num;
2142
2143 END LOOP;
2144
2145 d_position := 70;
2146
2147 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,x_po_line_num_tbl); END IF;
2148
2149 return (x_po_line_num_tbl);
2150
2151 EXCEPTION
2152
2153 WHEN OTHERS THEN
2154 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
2155 RAISE;
2156
2157 END group_by_default;
2158
2159 -------------------------------------------------------------------------------
2160 --Start of Comments
2161 --Name: has_same_req_header
2162 --Pre-reqs: None
2163 --Modifies:
2164 --Locks:
2165 -- None
2166 --Function:
2167 -- Determines if all the requisition lines in the input list belong to the
2168 -- same requisition header.
2169 --Parameters:
2170 --IN:
2171 --p_req_line_id_tbl
2172 -- Table of requisition line IDs representing the Autocreate Document Builder.
2173 --Returns:
2174 -- TRUE if all req lines in the input nested table belong to the same
2175 -- requisition header; FALSE otherwise.
2176 --Notes:
2177 -- N/A
2178 --Testing:
2179 -- N/A
2180 --End of Comments
2181 -------------------------------------------------------------------------------
2182 FUNCTION has_same_req_header
2183 (
2184 p_req_line_id_tbl IN PO_TBL_NUMBER
2185 )
2186 RETURN BOOLEAN
2187 IS
2188 l_req_header_id NUMBER;
2189 l_first_req_header_id NUMBER;
2190
2191 d_mod CONSTANT VARCHAR2(100) := D_has_same_req_header;
2192 d_position NUMBER := 0;
2193
2194 BEGIN
2195
2196 -- Loop through all req lines and compare their req headers
2197 -- with each other.
2198 --
2199 FOR i IN 1..p_req_line_id_tbl.COUNT LOOP
2200
2201 d_position := 10;
2202
2203 SELECT requisition_header_id
2204 INTO l_req_header_id
2205 FROM po_requisition_lines_all
2206 WHERE requisition_line_id = p_req_line_id_tbl(i);
2207
2208 d_position := 20;
2209
2210 -- If first line, initialize variable for first line.
2211 -- Otherwise, check if the current req header matches
2212 -- that of the first req header.
2213 --
2214 IF ( l_first_req_header_id IS NULL ) THEN
2215
2216 l_first_req_header_id := l_req_header_id;
2217
2218 ELSIF ( l_req_header_id <> l_first_req_header_id ) THEN
2219
2220 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,FALSE); END IF;
2221 return (FALSE); -- return false if header does not match
2222
2223 END IF;
2224
2225 END LOOP;
2226
2227 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,TRUE); END IF;
2228 return (TRUE);
2229
2230 EXCEPTION
2231
2232 WHEN OTHERS THEN
2233 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
2234 RAISE;
2235
2236 END has_same_req_header;
2237
2238 -------------------------------------------------------------------------------
2239 --Start of Comments
2240 --Name: match_add_to_po_lines
2241 --Pre-reqs: None
2242 --Modifies:
2243 --Locks:
2244 -- None
2245 --Function:
2246 -- Constructs a table of requisition line IDs representing the subset of
2247 -- the input requisition lines that match at least one line on the input PO.
2248 -- Also constructs a corresponding table of PO line numbers from the
2249 -- mached PO lines.
2250 --Parameters:
2251 --IN:
2252 --p_req_line_id_tbl
2253 -- Table of requisition line IDs representing the Autocreate Document Builder.
2254 --p_consigned_flag_tbl
2255 -- Table of Consigned Flag values corresponding to each req line in
2256 -- p_req_line_id_tbl.
2257 --p_add_to_po_header_id
2258 -- The ID of the PO to which the requisition lines are being added
2259 -- (will be null when creating a new PO).
2260 --p_builder_agreement_id
2261 -- The ID of the Global Agreement for which the PO will be created.
2262 --p_start_index
2263 -- The index of the first requisition line in the input table which
2264 -- should have a PO line number calculated (default value of 1
2265 -- if not specified).
2266 --p_end_index
2267 -- The index of the last requisition line in the input table which
2268 -- should have a PO line number calculated (default value of last
2269 -- index in the table if not specified).
2270 --OUT:
2271 --x_req_line_id_tbl
2272 -- Table of requisition line IDs that match at least one line on the PO.
2273 --x_po_line_num_tbl
2274 -- Table of PO line numbers representing the lines on the PO which match
2275 -- to each of the requisition lines in x_req_line_id_tbl.
2276 --Notes:
2277 -- N/A
2278 --Testing:
2279 -- N/A
2280 --End of Comments
2281 -------------------------------------------------------------------------------
2282 PROCEDURE match_add_to_po_lines
2283 (
2284 p_req_line_id_tbl IN PO_TBL_NUMBER
2285 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
2286 , p_add_to_po_header_id IN NUMBER
2287 , p_builder_agreement_id IN NUMBER
2288 , p_start_index IN NUMBER
2289 , p_end_index IN NUMBER
2290 , x_req_line_id_tbl OUT NOCOPY PO_TBL_NUMBER
2291 , x_po_line_num_tbl OUT NOCOPY PO_TBL_NUMBER
2292 )
2293 IS
2294 l_key NUMBER;
2295
2296 l_need_by_grouping_profile
2297 FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
2298 l_ship_to_grouping_profile
2299 FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
2300
2301 d_mod CONSTANT VARCHAR2(100) := D_match_add_to_po_lines;
2302 d_position NUMBER := 0;
2303
2304 BEGIN
2305
2306 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
2307
2308 -- Return immediately if there is no Add To PO.
2309 --
2310 IF ( p_add_to_po_header_id IS NULL )
2311 THEN
2312 x_req_line_id_tbl := PO_TBL_NUMBER();
2313 x_po_line_num_tbl := PO_TBL_NUMBER();
2314 return;
2315 END IF;
2316
2317 d_position := 10;
2318
2319 -- Retrieve a new GT table key.
2320 --
2321 l_key := PO_CORE_S.get_session_gt_nextval;
2322
2323 -- Get Profile Option values to be used in query.
2324 --
2325 l_need_by_grouping_profile := FND_PROFILE.value('PO_NEED_BY_GROUPING');
2326 l_ship_to_grouping_profile := FND_PROFILE.value('PO_SHIPTO_GROUPING');
2327
2328 d_position := 20;
2329 IF PO_LOG.d_stmt THEN
2330 PO_LOG.stmt(d_mod,d_position,'l_key',l_key);
2331 PO_LOG.stmt(d_mod,d_position,'l_need_by_grouping_profile',l_need_by_grouping_profile);
2332 PO_LOG.stmt(d_mod,d_position,'l_ship_to_grouping_profile',l_ship_to_grouping_profile);
2333 END IF;
2334
2335 -- Bulk insert matching lines into GT table.
2336 --
2337 FORALL i IN p_start_index..p_end_index
2338
2339 INSERT INTO po_session_gt
2340 ( key
2341 , num1
2342 , num2
2343 )
2344 SELECT DISTINCT
2345 l_key
2346 , prl.requisition_line_id
2347 , pol.line_num
2348 FROM po_requisition_lines_all prl
2349 , po_lines_all pol
2350 , po_line_locations_all pll
2351 , po_lines_all src_line
2352 WHERE pol.po_header_id = p_add_to_po_header_id
2353 AND pll.po_line_id = pol.po_line_id
2354 AND prl.requisition_line_id = p_req_line_id_tbl(i)
2355 AND decode ( prl.item_id
2356 , pol.item_id, 1, 0) = 1
2357 AND ((prl.item_id IS NOT NULL OR pol.item_id IS NOT NULL)
2358 OR decode(
2359 prl.item_description,
2360 pol.item_description, 1, 0) = 1)
2361 AND decode ( prl.item_revision
2362 , pol.item_revision, 1, 0) = 1
2363 AND decode ( prl.line_type_id
2364 , pol.line_type_id, 1, 0) = 1
2365 AND decode ( prl.preferred_grade
2366 , pol.preferred_grade, 1, 0) = 1
2367 AND decode ( prl.unit_meas_lookup_code
2368 , pol.unit_meas_lookup_code, 1, 0) = 1
2369 AND decode ( prl.transaction_reason_code
2370 , pol.transaction_reason_code, 1, 0) = 1
2371 AND decode ( prl.supplier_ref_number
2372 , pol.supplier_ref_number, 1, 0) = 1
2373 AND ( ( l_need_by_grouping_profile = 'N' )
2374 OR ( decode ( trunc(prl.need_by_date,'MI')
2375 , trunc(pll.need_by_date,'MI'), 1, 0) = 1 ) )
2376 AND ( ( l_ship_to_grouping_profile = 'N' )
2377 OR ( decode ( prl.destination_organization_id
2378 , pll.ship_to_organization_id, 1, 0) = 1 ) )
2379 AND ( ( prl.document_type_code <> 'CONTRACT' )
2380 OR ( decode ( prl.blanket_po_header_id
2381 , pol.contract_id, 1, 0) = 1 ) )
2382 AND src_line.po_header_id (+) = prl.blanket_po_header_id
2383 AND src_line.line_num (+) = prl.blanket_po_line_num
2384 AND ( ( p_builder_agreement_id IS NOT NULL )
2385 OR ( ( decode ( prl.blanket_po_header_id
2386 , pol.from_header_id, 1, 0) = 1 )
2387 AND ( decode ( src_line.po_line_id
2388 , pol.from_line_id, 1, 0) = 1 ) ) )
2389 AND nvl(pol.cancel_flag, 'N') <> 'Y'
2390 AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2391 AND nvl(pll.consigned_flag, 'N') = p_consigned_flag_tbl(i);
2392
2393 d_position := 30;
2394
2395 -- Clean up GT table and return the results into the output tables.
2396 --
2397 DELETE FROM po_session_gt
2398 WHERE key = l_key
2399 RETURNING num1, num2
2400 BULK COLLECT INTO x_req_line_id_tbl, x_po_line_num_tbl;
2401
2402 IF PO_LOG.d_proc THEN
2403 PO_LOG.proc_end(d_mod,'x_req_line_id_tbl',x_req_line_id_tbl);
2404 PO_LOG.proc_end(d_mod,'x_po_line_num_tbl',x_po_line_num_tbl);
2405 END IF;
2406
2407 EXCEPTION
2408
2409 WHEN OTHERS THEN
2410 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
2411 RAISE;
2412
2413 END match_add_to_po_lines;
2414
2415
2416 -------------------------------------------------------------------------------
2417 --Start of Comments
2418 --Name: find_matching_builder_line_num
2419 --Pre-reqs: None
2420 --Modifies:
2421 --Locks:
2422 -- None
2423 --Function:
2424 -- Finds a matching line in the input table with index less than the
2425 -- given current index and its PO Line Number column populated.
2426 --Parameters:
2427 --IN:
2428 --p_current_index
2429 -- The index in the nested table of the requisition line to match. The
2430 -- search will be conducted only for indices less than this value.
2431 --p_req_line_id_tbl
2432 -- Nested table of requisition line IDs.
2433 --p_po_line_num_tbl
2434 -- Nested table of PO line numbers.
2435 --p_builder_agreement_id
2436 -- Global Agreement ID specified in the Doc Builder.
2437 --Returns:
2438 -- PO line number of the matching requisition line.
2439 --Notes:
2440 -- N/A
2441 --Testing:
2442 -- N/A
2443 --End of Comments
2444 -------------------------------------------------------------------------------
2445 FUNCTION find_matching_builder_line_num
2446 (
2447 p_current_index IN NUMBER
2448 , p_req_line_id_tbl IN PO_TBL_NUMBER
2449 , p_po_line_num_tbl IN PO_TBL_NUMBER
2450 , p_builder_agreement_id IN NUMBER
2451 )
2452 RETURN NUMBER
2453 IS
2454 d_mod CONSTANT VARCHAR2(100) := D_find_matching_builder_line;
2455 d_position NUMBER := 0;
2456
2457 BEGIN
2458
2459 -- Loop from beginning of the Doc Builder up to (but not including)
2460 -- the current line to look for a match.
2461 --
2462 FOR i IN 1..(p_current_index - 1)
2463 LOOP
2464 IF ( ( req_lines_match ( p_builder_agreement_id
2465 , p_req_line_id_tbl(p_current_index)
2466 , p_req_line_id_tbl(i) )
2467 )
2468 AND ( p_po_line_num_tbl(i) IS NOT NULL ) )
2469 THEN
2470 return (p_po_line_num_tbl(i)); -- return PO line num
2471 END IF;
2472
2473 END LOOP;
2474
2475 return (NULL); -- if loop completes, no matching line was found
2476
2477 EXCEPTION
2478
2479 WHEN OTHERS THEN
2480 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
2481 RAISE;
2482
2483 END find_matching_builder_line_num;
2484
2485 -------------------------------------------------------------------------------
2486 --Start of Comments
2487 --Name: req_lines_match
2488 --Pre-reqs: None
2489 --Modifies:
2490 --Locks:
2491 -- None
2492 --Function:
2493 -- Determines if two requisition lines can be grouped.
2494 --Parameters:
2495 --IN:
2496 --p_agreement_id
2497 -- The ID of the Global Agreement for which the PO will be created.
2498 --p_req_line_id_1
2499 -- First requisition line to compare.
2500 --p_req_line_id_2
2501 -- Second requisition line to compare.
2502 --Returns:
2503 -- TRUE if all pertinent attributes match; FALSE otherwise.
2504 --Notes:
2505 -- N/A
2506 --Testing:
2507 -- N/A
2508 --End of Comments
2509 -------------------------------------------------------------------------------
2510 FUNCTION req_lines_match
2511 (
2512 p_agreement_id IN NUMBER
2513 , p_req_line_id_1 IN NUMBER
2514 , p_req_line_id_2 IN NUMBER
2515 )
2516 RETURN BOOLEAN
2517 IS
2518 l_message_code VARCHAR2(30);
2519 l_token_name VARCHAR2(30);
2520 l_token_value VARCHAR2(2000);
2521
2522 l_result BOOLEAN;
2523
2524 d_mod CONSTANT VARCHAR2(100) := D_req_lines_match;
2525 d_position NUMBER := 0;
2526
2527 BEGIN
2528
2529 IF PO_LOG.d_proc THEN
2530 PO_LOG.proc_begin(d_mod,'p_agreement_id',p_agreement_id);
2531 PO_LOG.proc_begin(d_mod,'p_req_line_id_1',p_req_line_id_1);
2532 PO_LOG.proc_begin(d_mod,'p_req_line_id_2',p_req_line_id_2);
2533 END IF;
2534
2535 -- Make a call to lines_match to determine if all relevant attributes
2536 -- match. Note that we do not need Supplier/Site since we do not need
2537 -- to derive the Consigned Flag when comparing two req lines.
2538 --
2539 lines_match ( p_agreement_id => p_agreement_id
2540 , p_supplier_id => null
2541 , p_site_id => null
2542 , p_req_line_id => p_req_line_id_1
2543 , p_req_line_id_to_compare => p_req_line_id_2
2544 , p_po_line_id_to_compare => null
2545 , x_message_code => l_message_code
2546 , x_token_name => l_token_name
2547 , x_token_value => l_token_value
2548 );
2549 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'l_message_code',l_message_code); END IF;
2550
2551 IF ( l_message_code IS NULL )
2552 THEN
2553 l_result := TRUE;
2554 ELSE
2555 l_result := FALSE;
2556 END IF;
2557
2558 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,l_result); END IF;
2559
2560 return (l_result);
2561
2562 EXCEPTION
2563
2564 WHEN OTHERS THEN
2565 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
2566 RAISE;
2567
2568 END req_lines_match;
2569
2570 -------------------------------------------------------------------------------
2571 --Start of Comments
2572 --Name: get_max_po_line_num
2573 --Pre-reqs: None
2574 --Modifies:
2575 --Locks:
2576 -- None
2577 --Function:
2578 -- Retrieves the maximum PO line number value from either the input
2579 -- PO or the Document Builder.
2580 --Parameters:
2581 --IN:
2582 --p_po_line_num_tbl
2583 -- Table of PO line numbers to check for maximum.
2584 --p_po_header_id
2585 -- ID of PO to check for maximum line number.
2586 --Returns:
2587 -- Maximum PO line number between the input PO and PO line number table.
2588 --Notes:
2589 -- N/A
2590 --Testing:
2591 -- N/A
2592 --End of Comments
2593 -------------------------------------------------------------------------------
2594 FUNCTION get_max_po_line_num
2595 (
2596 p_po_line_num_tbl IN PO_TBL_NUMBER
2597 , p_po_header_id IN NUMBER := NULL
2598 )
2599 RETURN NUMBER
2600 IS
2601 x_max_po_line_num NUMBER := 0;
2602
2603 d_mod CONSTANT VARCHAR2(100) := D_get_max_po_line_num;
2604 d_position NUMBER := 0;
2605
2606 BEGIN
2607
2608 IF PO_LOG.d_proc THEN
2609 PO_LOG.proc_begin(d_mod,'p_po_line_num_tbl',p_po_line_num_tbl);
2610 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
2611 END IF;
2612
2613 -- Find max line number for the input PO.
2614 --
2615 IF ( p_po_header_id IS NOT NULL )
2616 THEN
2617 SELECT nvl(max(line_num), 0)
2618 INTO x_max_po_line_num
2619 FROM po_lines_all
2620 WHERE po_header_id = p_po_header_id;
2621 END IF;
2622
2623 -- Find max line number in the nested table of line numbers.
2624 --
2625 FOR i IN 1..p_po_line_num_tbl.COUNT
2626 LOOP
2627 IF ( p_po_line_num_tbl(i) > x_max_po_line_num )
2628 THEN
2629 x_max_po_line_num := p_po_line_num_tbl(i);
2630 END IF;
2631 END LOOP;
2632
2633 -- Return max PO line number.
2634 --
2635 return (x_max_po_line_num);
2636
2637 EXCEPTION
2638
2639 WHEN OTHERS THEN
2640 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
2641 RAISE;
2642
2643 END get_max_po_line_num;
2644
2645 -------------------------------------------------------------------------------
2646 --Start of Comments
2647 --Name: get_consigned_flag_tbl
2648 --Pre-reqs: None
2649 --Modifies:
2650 --Locks:
2651 -- None
2652 --Function:
2653 -- Constructs a table of the Consigned Flags corresponding to each
2654 -- requisition line in p_req_line_id_tbl.
2655 --Parameters:
2656 --IN:
2657 --p_req_line_id_tbl
2658 -- Table of requisition lines to find the Consigned Flag for.
2659 --p_builder_org_id
2660 -- ID of the operating unit in which the PO will be created.
2661 --p_builder_supplier_id
2662 -- The ID of the Supplier for which the PO will be created.
2663 --p_builder_site_id
2664 -- The ID of the Site for which the PO will be created.
2665 --Returns:
2666 -- Table of Consigned Flags corresponding each requisition line in
2667 -- p_req_line_id_tbl.
2668 --Notes:
2669 -- N/A
2670 --Testing:
2671 -- N/A
2672 --End of Comments
2673 -------------------------------------------------------------------------------
2674 FUNCTION get_consigned_flag_tbl
2675 (
2676 p_req_line_id_tbl IN PO_TBL_NUMBER
2677 , p_builder_org_id IN NUMBER
2678 , p_builder_supplier_id IN NUMBER
2679 , p_builder_site_id IN NUMBER
2680 )
2681 RETURN PO_TBL_VARCHAR1
2682 IS
2683 l_item_id NUMBER;
2684 x_consigned_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
2685
2686 d_mod CONSTANT VARCHAR2(100) := D_get_consigned_flag_tbl;
2687 d_position NUMBER := 0;
2688
2689 BEGIN
2690
2691 IF PO_LOG.d_proc THEN
2692 PO_LOG.proc_begin(d_mod,'p_req_line_id_tbl',p_req_line_id_tbl);
2693 PO_LOG.proc_begin(d_mod,'p_builder_org_id',p_builder_org_id);
2694 PO_LOG.proc_begin(d_mod,'p_builder_supplier_id',p_builder_supplier_id);
2695 PO_LOG.proc_begin(d_mod,'p_builder_site_id',p_builder_site_id);
2696 END IF;
2697
2698 x_consigned_flag_tbl.EXTEND(p_req_line_id_tbl.COUNT);
2699
2700 FOR i IN 1..p_req_line_id_tbl.COUNT LOOP
2701
2702 SELECT item_id
2703 INTO l_item_id
2704 FROM po_requisition_lines_all
2705 WHERE requisition_line_id = p_req_line_id_tbl(i);
2706
2707 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'l_item_id',l_item_id); END IF;
2708
2709 x_consigned_flag_tbl(i) := PO_THIRD_PARTY_STOCK_GRP.get_consigned_flag
2710 ( p_builder_org_id
2711 , l_item_id
2712 , p_builder_supplier_id
2713 , p_builder_site_id
2714 ,NULL --Bug 5976612
2715 );
2716 /* Bug 5976612
2717 Added the 'NULL' parameter in the end for the inv org id.
2718 These changes are driven by the changes done to the function PO_THIRD_PARTY_STOCK_GRP.get_consigned_flag. */
2719
2720 END LOOP;
2721
2722 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,x_consigned_flag_tbl); END IF;
2723
2724 return (x_consigned_flag_tbl);
2725
2726 EXCEPTION
2727
2728 WHEN OTHERS THEN
2729 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
2730 RAISE;
2731
2732 END get_consigned_flag_tbl;
2733
2734 -------------------------------------------------------------------------------
2735 --Start of Comments
2736 --Name: find_matching_po_line_num
2737 --Pre-reqs: None
2738 --Modifies:
2739 --Locks:
2740 -- None
2741 --Function:
2742 -- Finds a given ID in a table (p_comparison_tbl) and returns the
2743 -- corresponding value from p_po_line_num_tbl for that index.
2744 --Parameters:
2745 --IN:
2746 --p_req_line_id
2747 -- Requisition line ID to search for.
2748 --p_comparison_tbl
2749 -- Table of requisition line IDs.
2750 --p_po_line_num_tbl
2751 -- Table of PO line numbers corresponding to p_comparison_tbl.
2752 --Returns:
2753 -- Returns the PO line number at the index for which the comparison table
2754 -- matches the input req line ID. If the req line ID is not found in the
2755 -- comparison table, returns NULL.
2756 -- p_req_line_id_tbl.
2757 --Notes:
2758 -- N/A
2759 --Testing:
2760 -- N/A
2761 --End of Comments
2762 -------------------------------------------------------------------------------
2763 FUNCTION find_matching_po_line_num
2764 (
2765 p_req_line_id IN NUMBER
2766 , p_comparison_tbl IN PO_TBL_NUMBER
2767 , p_po_line_num_tbl IN PO_TBL_NUMBER
2768 )
2769 RETURN NUMBER
2770 IS
2771 d_mod CONSTANT VARCHAR2(100) := D_find_matching_po_line_num;
2772 d_position NUMBER := 0;
2773
2774 BEGIN
2775
2776 FOR i IN 1..p_comparison_tbl.COUNT
2777 LOOP
2778 IF ( p_req_line_id = p_comparison_tbl(i) )
2779 THEN
2780 return (p_po_line_num_tbl(i));
2781 END IF;
2782 END LOOP;
2783
2784 return (null);
2785
2786 EXCEPTION
2787
2788 WHEN OTHERS THEN
2789 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
2790 RAISE;
2791
2792 END find_matching_po_line_num;
2793
2794 END PO_AUTOCREATE_GROUPING_PVT;
[Home] [Help]