1 PACKAGE BODY po_autocreate_grouping_pvt AS
2 /* $Header: PO_AUTOCREATE_GROUPING_PVT.plb 120.40.12020000.8 2013/05/20 11:14:17 akyanama 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_check_neg_line_numbers CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_neg_line_numbers');
13 D_lines_match CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'lines_match');
14 D_group_req_lines CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_req_lines');
15 D_group_neg_req_lines CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_neg_req_lines');
16 D_group_clm_req_lines CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_clm_req_lines');
17 D_group_by_clm_req_seq_num CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_by_clm_req_seq_num');
18 D_group_by_clm_req_line_num CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_by_clm_req_line_num');
19 D_get_req_line_delivery_info CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_req_line_delivery_info');
20 D_check_delivery_info CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_delivery_info');
21 D_lines_info_match CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'lines_info_match');
22 D_get_req_line_info CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_req_line_info');
23 D_get_po_line_info CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_po_line_info');
24 D_check_line_info CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_line_info');
25 D_lines_delivery_info_match CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'lines_delivery_info_match');
26 D_group_by_requisition_line CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_by_requisition_line_num');
27 D_group_by_requisition_seq_num CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_by_requisition_seq_num');
28 D_group_by_default CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'group_by_default');
29 D_has_same_req_header CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'has_same_req_header');
30 D_match_add_to_po_lines CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'match_add_to_po_lines');
31 D_find_matching_builder_index CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'find_matching_builder_index');
32 D_find_matching_builder_line CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'find_matching_builder_line_num');
33 D_req_lines_match CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'req_lines_match');
34 D_get_max_po_line_num CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_max_po_line_num');
35 D_get_max_clm_po_line_num CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_max_clm_po_line_num');
36 D_get_consigned_flag_tbl CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_consigned_flag_tbl');
37 D_find_matching_po_line_num CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'find_matching_po_line_num');
38 D_default_info_slin_numbers CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'default_info_slin_numbers');
39 D_check_mod_lock_availability CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_mod_lock_availability');
40 D_clm_group_by_default CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'clm_group_by_default');
41 D_clm_match_add_to_po_lines CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'clm_match_add_to_po_lines');
42 D_clm_find_match_builder_line CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'clm_find_match_builder_linenum');
43 D_get_line_num_disp_for_clin CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_line_num_disp_for_clin');
44 D_RenumberSlins CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'RenumberSlins');
45 --bug 13622501
46 D_get_po_linenum_for_shipment CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_po_linenum_for_shipment');
47 -- bug#16097884
48 D_check_item_description CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_item_description');
49
50
51 /*=========================================================================*/
52 /*===================== SPECIFICATIONS (PRIVATE) ==========================*/
53 /*=========================================================================*/
54
55 CURSOR po_delivery_info_csr(p_po_line_id_to_compare IN NUMBER, p_draft_id IN NUMBER)
56 IS
57 SELECT need_by_date,
58 ship_to_location_id,
59 ship_to_organization_id,
60 consigned_flag
61 FROM po_line_locations_merge_v
62 WHERE po_line_id = p_po_line_id_to_compare
63 AND draft_id = p_draft_id; --Autocreate grouping
64
65 FUNCTION get_req_line_delivery_info(
66 p_req_line_id IN NUMBER,
67 p_supplier_id IN NUMBER,
68 p_site_id IN NUMBER
69 ) RETURN PO_DELIVERY_INFO_CSR%ROWTYPE;
70
71 PROCEDURE check_delivery_info(
72 p_need_by_grouping_profile IN VARCHAR2,
73 p_ship_to_grouping_profile IN VARCHAR2,
74 p_delivery_one IN PO_DELIVERY_INFO_CSR%ROWTYPE,
75 p_delivery_two IN PO_DELIVERY_INFO_CSR%ROWTYPE,
76 x_message_code OUT NOCOPY VARCHAR2,
77 x_token_name OUT NOCOPY VARCHAR2,
78 x_token_value OUT NOCOPY VARCHAR2
79 );
80
81 PROCEDURE lines_info_match(
82 p_agreement_id IN NUMBER,
83 p_req_line_id IN NUMBER,
84 p_req_line_id_to_compare IN NUMBER,
85 p_po_line_id_to_compare IN NUMBER,
86 p_draft_id IN NUMBER :=-1, --Autocreate grouping
87 p_relax_item_desc_check IN VARCHAR2, --Autocreate grouping
88 x_message_code OUT NOCOPY VARCHAR2,
89 x_token_name OUT NOCOPY VARCHAR2,
90 x_token_value OUT NOCOPY VARCHAR2
91 );
92
93 TYPE line_info IS RECORD(
94 item_id PO_REQUISITION_LINES_ALL.item_id%TYPE,
95 item_description PO_REQUISITION_LINES_ALL.item_description%TYPE,
96 item_revision PO_REQUISITION_LINES_ALL.item_revision%TYPE,
97 order_type_lookup_code PO_REQUISITION_LINES_ALL.order_type_lookup_code%TYPE,
98 purchase_basis PO_REQUISITION_LINES_ALL.purchase_basis%TYPE,
99 matching_basis PO_REQUISITION_LINES_ALL.matching_basis%TYPE,
100 preferred_grade PO_REQUISITION_LINES_ALL.preferred_grade%TYPE,
101 unit_meas_lookup_code PO_REQUISITION_LINES_ALL.unit_meas_lookup_code%TYPE,
102 transaction_reason PO_REQUISITION_LINES_ALL.transaction_reason_code%TYPE,
103 contract_id PO_REQUISITION_LINES_ALL.blanket_po_header_id%TYPE,
104 source_document_id PO_REQUISITION_LINES_ALL.blanket_po_header_id%TYPE,
105 source_document_line_id PO_LINES_ALL.po_line_id%TYPE,
106 cancel_flag PO_LINES_ALL.cancel_flag%TYPE,
107 closed_code PO_LINES_ALL.closed_code%TYPE,
108 supplier_ref_number PO_REQUISITION_LINES_ALL.supplier_ref_number%TYPE
109 ,group_line_id PO_REQUISITION_LINES_ALL.GROUP_LINE_ID%TYPE
110 ,clm_info_flag PO_REQUISITION_LINES_ALL.CLM_INFO_FLAG%TYPE
111 ,CLM_BASE_LINE_NUM PO_REQUISITION_LINES_ALL.CLM_BASE_LINE_NUM%TYPE,
112 category_id PO_REQUISITION_LINES_ALL.category_id%TYPE --bugfix#16097884
113 );
114
115 FUNCTION get_req_line_info(p_req_line_id IN NUMBER) RETURN LINE_INFO;
116
117 FUNCTION get_po_line_info(p_po_line_id IN NUMBER, p_draft_id IN NUMBER) RETURN LINE_INFO; --Autocreate grouping
118
119 PROCEDURE check_line_info(
120 p_agreement_id IN NUMBER,
121 p_line_one IN LINE_INFO,
122 p_line_two IN LINE_INFO,
123 p_relax_item_desc_check IN VARCHAR2, --Autocreate grouping
124 x_message_code OUT NOCOPY VARCHAR2,
125 x_token_name OUT NOCOPY VARCHAR2,
126 x_token_value OUT NOCOPY VARCHAR2
127 );
128
129 PROCEDURE lines_delivery_info_match(
130 p_supplier_id IN NUMBER,
131 p_site_id IN NUMBER,
132 p_req_line_id IN NUMBER,
133 p_req_line_id_to_compare IN NUMBER,
134 p_po_line_id_to_compare IN NUMBER,
135 p_draft_id IN NUMBER, --Autocreate grouping
136 x_message_code OUT NOCOPY VARCHAR2,
137 x_token_name OUT NOCOPY VARCHAR2,
138 x_token_value OUT NOCOPY VARCHAR2
139 );
140
141 FUNCTION group_by_requisition_line_num
142 ( p_req_line_num_tbl IN PO_TBL_NUMBER
143 , p_po_line_num_tbl IN PO_TBL_NUMBER
144 , p_start_index IN NUMBER
145 , p_end_index IN NUMBER
146 ) RETURN PO_TBL_NUMBER;
147
148 FUNCTION group_by_clm_req_line_num
149 ( p_req_line_num_tbl IN PO_TBL_VARCHAR100
150 , p_po_line_num_tbl IN PO_TBL_VARCHAR100
151 , p_start_index IN NUMBER
152 , p_end_index IN NUMBER
153 ) RETURN PO_TBL_VARCHAR100;
154
155 FUNCTION group_by_requisition_seq_num
156 ( p_po_line_num_tbl IN PO_TBL_NUMBER
157 , p_add_to_po_header_id IN NUMBER
158 , p_start_index IN NUMBER
159 , p_end_index IN NUMBER
160 ) RETURN PO_TBL_NUMBER;
161
162 FUNCTION group_by_clm_req_seq_num
163 ( p_po_line_num_tbl IN PO_TBL_VARCHAR100
164 , p_req_line_id_tbl IN PO_TBL_NUMBER
165 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
166 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
167 , p_add_to_po_header_id IN NUMBER
168 , p_draft_id IN NUMBER --Autocreate grouping
169 , p_start_index IN NUMBER
170 , p_end_index IN NUMBER
171 ) RETURN PO_TBL_VARCHAR100;
172
173
174 FUNCTION group_by_default
175 ( p_req_line_id_tbl IN PO_TBL_NUMBER
176 , p_po_line_num_tbl IN PO_TBL_NUMBER
177 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
178 , p_add_to_po_header_id IN NUMBER
179 , p_builder_agreement_id IN NUMBER
180 , p_start_index IN NUMBER
181 , p_end_index IN NUMBER
182 ) RETURN PO_TBL_NUMBER;
183
184 FUNCTION has_same_req_header
185 ( p_req_line_id_tbl IN PO_TBL_NUMBER
186 ) RETURN BOOLEAN;
187
188 PROCEDURE match_add_to_po_lines
189 ( p_req_line_id_tbl IN PO_TBL_NUMBER
190 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
191 , p_add_to_po_header_id IN NUMBER
192 , p_draft_id IN NUMBER --Autocreate grouping
193 , p_builder_agreement_id IN NUMBER
194 , p_start_index IN NUMBER
195 , p_end_index IN NUMBER
196 , x_req_line_id_tbl OUT NOCOPY PO_TBL_NUMBER
197 , x_po_line_num_tbl OUT NOCOPY PO_TBL_NUMBER
198 );
199
200 Procedure clm_match_add_to_po_lines
201 ( p_req_line_id_tbl IN PO_TBL_NUMBER
202 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
203 , p_add_to_po_header_id IN NUMBER
204 , p_draft_id IN NUMBER --Autocreate grouping
205 , p_builder_agreement_id IN NUMBER
206 , p_start_index IN NUMBER
207 , p_end_index IN NUMBER
208 , x_req_line_id_tbl OUT NOCOPY PO_TBL_NUMBER
209 , x_po_line_num_tbl OUT NOCOPY PO_TBL_NUMBER
210 , x_po_line_num_disp_tbl OUT NOCOPY PO_TBL_VARCHAR100
211 );
212
213 FUNCTION find_matching_builder_line_num
214 ( p_current_index IN NUMBER
215 , p_req_line_id_tbl IN PO_TBL_NUMBER
216 , p_po_line_num_tbl IN PO_TBL_NUMBER
217 , p_builder_agreement_id IN NUMBER
218 ) RETURN NUMBER;
219
220 FUNCTION req_lines_match
221 ( p_agreement_id IN NUMBER
222 , p_req_line_id_1 IN NUMBER
223 , p_req_line_id_2 IN NUMBER
224 ) RETURN BOOLEAN;
225
226 FUNCTION get_max_clm_po_line_num
227 ( p_po_line_num_tbl IN PO_TBL_VARCHAR100
228 , p_po_header_id IN NUMBER := NULL
229 , p_draft_id IN NUMBER := -1 --Autocreate grouping
230 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
231 ) RETURN VARCHAR2;
232
233 FUNCTION get_max_clm_sol_line_num
234 ( p_neg_line_num_tbl IN PO_TBL_VARCHAR100
235 , p_draft_id IN NUMBER := NULL
236 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
237 ) RETURN VARCHAR2;
238
239 FUNCTION get_consigned_flag_tbl
240 ( p_req_line_id_tbl IN PO_TBL_NUMBER
241 , p_builder_org_id IN NUMBER
242 , p_builder_supplier_id IN NUMBER
243 , p_builder_site_id IN NUMBER
244 ) RETURN PO_TBL_VARCHAR1;
245
246 FUNCTION find_matching_po_line_num
247 ( p_req_line_id IN NUMBER
248 , p_comparison_tbl IN PO_TBL_NUMBER
249 , p_po_line_num_tbl IN PO_TBL_NUMBER
250 ) RETURN NUMBER;
251
252 --Adding the two new functions clm_find_match_builder_linenum and clm_group_by_default which will be used
253 --for grouping in DEFAULT mode
254
255 FUNCTION clm_find_match_builder_linenum
256 (
257 p_current_index IN NUMBER
258 , p_req_line_id_tbl IN PO_TBL_NUMBER
259 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
260 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
261 , p_req_option_flag_tbl IN PO_TBL_VARCHAR1
262 , p_po_line_num_tbl IN PO_TBL_NUMBER
263 , p_builder_agreement_id IN NUMBER
264 )
265 RETURN NUMBER;
266
267 Procedure clm_group_by_default
268 (
269 p_req_line_id_tbl IN PO_TBL_NUMBER
270 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
271 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
272 , p_req_option_flag_tbl IN PO_TBL_VARCHAR1
273 , p_po_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
274 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
275 , p_add_to_po_header_id IN NUMBER
276 , p_draft_id IN NUMBER --Autocreate grouping
277 , p_builder_agreement_id IN NUMBER
278 , p_start_index IN NUMBER
279 , p_end_index IN NUMBER
280 , p_po_line_num_tbl IN OUT NOCOPY PO_TBL_NUMBER
281 );
282
283 Procedure clm_group_sol_by_default
284 (
285 p_req_line_id_tbl IN PO_TBL_NUMBER
286 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
287 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
288 , p_req_option_flag_tbl IN PO_TBL_VARCHAR1
289 , p_neg_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
290 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
291 , p_add_to_neg_header_id IN NUMBER
292 , p_draft_id IN NUMBER --Autocreate grouping
293 , p_builder_agreement_id IN NUMBER
294 , p_start_index IN NUMBER
295 , p_end_index IN NUMBER
296 , p_neg_line_num_tbl IN OUT NOCOPY PO_TBL_NUMBER
297 );
298
299
300
301 /*=========================================================================*/
302 /*========================== BODY (PUBLIC) ================================*/
303 /*=========================================================================*/
304
305 -------------------------------------------------------------------------------
306 --Start of Comments
307 --Name: get_line_action_tbl
308 --Pre-reqs:
309 -- None
310 --Modifies:
311 -- None
312 --Locks:
313 -- None
314 --Function:
315 -- Gets a table with corresponding 'NEW' or 'ADD' actions for each requisition
316 -- line in the document builder. Action is 'NEW' if autocreating a new line and
317 -- 'ADD' if adding to an existing PO line. For the latter to be true, the
318 -- line's PO line number has to be the same as one of the PO's line numbers.
319 --Parameters:
320 --IN:
321 --p_po_line_number_tbl
322 -- The table of PO line numbers for all the requistion lines in the document
323 -- builder.
324 --p_add_to_po_header_id
325 -- The header ID of the PO for the Add To PO case. If New PO, this parameter
326 -- will be null and the table passed out will have 'NEW' for each requisition
327 -- line in the document builder.
328 --Returns:
329 -- Table of 'NEW' or 'ADD' actions corresponding to each of the input
330 -- requisition lines.
331 --Notes:
332 -- None
333 --Testing:
334 -- None
335 --End of Comments
336 -------------------------------------------------------------------------------
337 FUNCTION get_line_action_tbl(
338 p_po_line_number_tbl IN PO_TBL_NUMBER,
339 p_add_to_po_header_id IN NUMBER,
340 p_draft_id IN NUMBER --CLM Autogrouping
341 ) RETURN PO_TBL_VARCHAR5
342 IS
343 d_mod CONSTANT VARCHAR2(100) := D_get_line_action_tbl;
344 d_position NUMBER := 0;
345
346 l_num_lines NUMBER;
347 l_line_action_tbl PO_TBL_VARCHAR5;
348 BEGIN
349 IF (PO_LOG.d_proc) THEN
350 PO_LOG.proc_begin(d_mod);
351 PO_LOG.proc_begin(d_mod,'p_add_to_po_header_id',p_add_to_po_header_id);
352 END IF;
353
354 -- Get the number of lines being passed in.
355 l_num_lines := p_po_line_number_tbl.COUNT;
356
357 -- Initialize table indicating whether lines are being added to existing PO
358 -- lines.
359 l_line_action_tbl := PO_TBL_VARCHAR5();
360 l_line_action_tbl.EXTEND(l_num_lines);
361
362 -- For each line in the doc builder, set the line action to 'ADD' if adding
363 -- to an existing PO line and 'NEW' if creating a new PO line.
364 FOR i IN 1..l_num_lines
365 LOOP
366 BEGIN
367 SELECT 'ADD'
368 INTO l_line_action_tbl(i)
369 FROM po_lines_merge_v
370 WHERE po_header_id = p_add_to_po_header_id
371 AND line_num = p_po_line_number_tbl(i)
372 AND nvl(draft_id,-1) = nvl(p_draft_id,-1);
373 EXCEPTION
374 WHEN NO_DATA_FOUND THEN
375 l_line_action_tbl(i) := 'NEW';
376 END;
377 END LOOP;
378 IF (PO_LOG.d_proc) THEN
379 PO_LOG.proc_return(d_mod, l_line_action_tbl);
380 END IF;
381
382 RETURN l_line_action_tbl;
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 IF (PO_LOG.d_exc) THEN
387 PO_LOG.exc(d_mod, d_position, 'An error occured in get_line_action_tbl');
388 END IF;
389
390 RAISE;
391 END get_line_action_tbl;
392
393 -------------------------------------------------------------------------------
394 --Start of Comments
395 --Name: check_po_line_numbers
396 --Pre-reqs:
397 -- None
398 --Modifies:
399 -- None
400 --Locks:
401 -- None
402 --Function:
403 -- Checks all requisition lines in the document builder to see if their PO line
404 -- numbers are valid. Returns tables with error messages corresponding to each
405 -- line in the doc builder, null if there are no error messages.
406 --Parameters:
407 --IN:
408 --p_style_id
409 -- The ID of the style specified in the document builder.
410 --p_agreement_id
411 -- The ID of the agreement specified in the document builder.
412 --p_supplier_id
413 -- The ID of the supplier specified in the document builder.
414 --p_site_id
415 -- The ID of the site specified in the document builder.
416 --p_req_line_id_tbl
417 -- The table of req line IDs for all the requistion lines in the document
418 -- builder.
419 --p_po_line_number_tbl
420 -- The table of PO line numbers for all the requistion lines in the document
421 -- builder.
422 --p_add_to_po_header_id
423 -- The header ID of the PO for the Add To PO case. If New PO, this parameter
424 -- will be null.
425 --OUT:
426 --x_message_code_tbl
427 -- Table of error message codes corresponding to each of the input requisition
428 -- lines.
429 --x_token_name_tbl
430 -- Table of error message token names corresponding to each of the input
431 -- requisition lines.
432 --x_token_value_tbl
433 -- Table of error message token values corresponding to each of the input
434 -- requisition lines.
435 --Notes:
436 -- None
437 --Testing:
438 -- None
439 --End of Comments
440 -------------------------------------------------------------------------------
441 PROCEDURE check_po_line_numbers(
442 p_style_id IN NUMBER,
443 p_agreement_id IN NUMBER,
444 p_supplier_id IN NUMBER,
445 p_site_id IN NUMBER,
446 p_req_line_id_tbl IN PO_TBL_NUMBER,
447 p_po_line_number_tbl IN PO_TBL_NUMBER,
448 p_add_to_po_header_id IN NUMBER,
449 p_draft_id IN NUMBER, --Autocreate grouping
450 x_message_code_tbl OUT NOCOPY PO_TBL_VARCHAR30,
451 x_token_name_tbl OUT NOCOPY PO_TBL_VARCHAR30,
452 x_token_value_tbl OUT NOCOPY PO_TBL_VARCHAR2000
453 )
454 IS
455 d_mod CONSTANT VARCHAR2(100) := D_check_po_line_numbers;
456 d_position NUMBER := 0;
457
458 l_num_lines NUMBER;
459 l_key NUMBER;
460 l_req_line_id PO_REQUISITION_LINES_ALL.requisition_line_id%TYPE;
461 l_po_line_num PO_LINES_ALL.line_num%TYPE;
462 l_req_line_id_to_compare PO_REQUISITION_LINES_ALL.requisition_line_id%TYPE;
463 l_po_line_id_to_compare PO_LINES_ALL.po_line_id%TYPE;
464 l_progress_payment_flag PO_DOC_STYLE_HEADERS.progress_payment_flag%TYPE;
465 l_message_code VARCHAR2(30) := NULL;
466 l_token_name VARCHAR2(30) := NULL;
467 l_token_value VARCHAR2(2000) := NULL;
468 l_line_combined_flag_tbl PO_TBL_VARCHAR1;
469 l_line_combined_flag VARCHAR2(1);
470
471 CURSOR req_line_ids_csr(
472 c_key IN NUMBER,
473 c_po_line_num IN varchar2,
474 c_current_req_line_id IN NUMBER
475 ) IS
476 SELECT index_num1 -- requisition line ID
477 FROM po_session_gt
478 WHERE key = c_key
479 AND index_num2 = c_po_line_num
480 AND index_num1 <> c_current_req_line_id;
481 BEGIN
482 IF (PO_LOG.d_proc) THEN
483 PO_LOG.proc_begin(d_mod);
484 PO_LOG.proc_begin(d_mod,'p_style_id',p_style_id);
485 PO_LOG.proc_begin(d_mod,'p_agreement_id',p_agreement_id);
486 PO_LOG.proc_begin(d_mod,'p_supplier_id',p_supplier_id);
487 PO_LOG.proc_begin(d_mod,'p_site_id',p_site_id);
488 PO_LOG.proc_begin(d_mod,'p_req_line_id_tbl',p_req_line_id_tbl);
489 PO_LOG.proc_begin(d_mod,'p_po_line_number_tbl',p_po_line_number_tbl);
490 PO_LOG.proc_begin(d_mod,'p_add_to_po_header_id',p_add_to_po_header_id);
491 END IF;
492
493 -- Get the number of lines being passed in.
494 l_num_lines := p_req_line_id_tbl.COUNT;
495
496 -- Initialize error message values to be returned.
497 x_message_code_tbl := PO_TBL_VARCHAR30();
498 x_message_code_tbl.EXTEND(l_num_lines);
499 x_token_name_tbl := PO_TBL_VARCHAR30();
500 x_token_name_tbl.EXTEND(l_num_lines);
501 x_token_value_tbl := PO_TBL_VARCHAR2000();
502 x_token_value_tbl.EXTEND(l_num_lines);
503
504 -- Initialize table of flags indicating whether lines are combined.
505 l_line_combined_flag_tbl := PO_TBL_VARCHAR1();
506 l_line_combined_flag_tbl.EXTEND(l_num_lines);
507
508 -- Get a new session key for use with the temp table.
509 l_key := PO_CORE_S.get_session_gt_nextval;
510
511 -- Insert req line ID and PO line num of all lines into temp table.
512 FORALL i IN 1..l_num_lines
513 INSERT INTO po_session_gt(
514 key, -- unique key
515 index_num1, -- req line ID
516 index_num2 -- PO line num
517 )
518 VALUES (l_key, p_req_line_id_tbl(i), p_po_line_number_tbl(i));
519
520 -- <BUG 4922298 START>
521 -- Wrap SQL statement with exception block to handle null style ID.
522 -- Determine whether progress payment is enabled.
523 BEGIN
524 SELECT progress_payment_flag
525 INTO l_progress_payment_flag
526 FROM po_doc_style_headers
527 WHERE style_id = p_style_id;
528 EXCEPTION
529 WHEN NO_DATA_FOUND THEN
530 l_progress_payment_flag := null;
531 END;
532 -- <BUG 4922298 END>
533
534 -- If progress payment is enabled, update l_line_combined_flag_tbl with flag
535 -- indicating whether each line is combined with another req or PO line.
536 IF (NVL(l_progress_payment_flag, 'N') = 'Y')
537 THEN
538 -- If req line is being combined with another req or PO line, set flag to
539 -- 'Y'. Else, set flag to 'N'.
540 SELECT NVL(
541 (SELECT 'Y'
542 FROM dual
543 WHERE EXISTS(
544 -- Select all doc builder requisition lines that have the same PO line
545 -- number as the current line in the loop.
546 SELECT 'doc builder lines with same PO line number'
547 FROM po_session_gt POSGT2
548 WHERE POSGT2.index_num1 <> POSGT.index_num1 -- Not the current line
549 AND POSGT2.index_num2 = POSGT.index_num2 -- Same PO line number
550 )
551 OR EXISTS(
552 -- Select all PO lines that have the same PO line number as the
553 -- current line in the loop.
554 SELECT 'PO lines with same PO line number'
555 FROM po_lines_all
556 WHERE po_header_id = p_add_to_po_header_id
557 AND line_num = POSGT.index_num2 -- Same PO line number
558 )),
559 'N' -- NVL to 'N' if no other req/PO lines with same PO line number
560 )
561 BULK COLLECT INTO l_line_combined_flag_tbl
562 FROM po_session_gt POSGT
563 WHERE key = l_key;
564 END IF;
565
566 FOR i IN 1..l_num_lines -- Loop through all doc builder req lines
567 LOOP
568 -- Initialize the message code to NULL for each iteration
569 l_message_code := NULL;
570
571 l_req_line_id := p_req_line_id_tbl(i);
572 l_po_line_num := p_po_line_number_tbl(i);
573 l_line_combined_flag := l_line_combined_flag_tbl(i);
574
575 -- If there is no error yet, check if the PO line number is NULL.
576 IF (l_message_code IS NULL AND l_po_line_num IS NULL)
577 THEN
578 l_message_code := 'PO_ALL_NOT_NULL';
579 l_token_name := NULL;
580 l_token_value := NULL;
581 END IF;
582
583 -- If there is no error yet, check if the PO line number is less than or
584 -- equal to 0.
585 IF (l_message_code IS NULL AND l_po_line_num <= 0)
586 THEN
587 l_message_code := 'PO_ALL_ENTER_VALUE_GT_ZERO';
588 l_token_name := NULL;
589 l_token_value := NULL;
590 END IF;
591
592 -- If there is no error yet, check if progress payment is enabled and if
593 -- lines are being combined.
594 IF (l_message_code IS NULL
595 AND l_progress_payment_flag = 'Y'
596 AND l_line_combined_flag = 'Y')
597 THEN
598 l_message_code := 'PO_ALL_CANT_COMB_PROGRESSPAY';
599 l_token_name := NULL;
600 l_token_value := NULL;
601 END IF;
602
603 IF (l_message_code IS NULL) -- If there is no error yet
604 THEN
605 -- Check if all req lines with the same PO line number match.
606 OPEN req_line_ids_csr(l_key, l_po_line_num, l_req_line_id);
607 LOOP
608 FETCH req_line_ids_csr INTO l_req_line_id_to_compare;
609 EXIT WHEN req_line_ids_csr%NOTFOUND;
610 lines_match(
611 p_agreement_id,
612 p_supplier_id,
613 p_site_id,
614 l_req_line_id,
615 l_req_line_id_to_compare,
616 NULL, -- No PO line ID to compare
617 NULL, --draft_id. Autocreate grouping ,this will be passed as null since the po line id to compare is also null
618 'Y', --p_relax_item_desc_check Autocreate grouping
619 l_message_code,
620 l_token_name,
621 l_token_value
622 );
623
624 -- If any req lines with the same PO line number don't match, no need
625 -- to check other req lines since only show one error message.
626 EXIT WHEN l_message_code IS NOT NULL;
627 END LOOP;
628 CLOSE req_line_ids_csr;
629
630 -- Check for PO line mismatch if all req lines match
631 IF (l_message_code IS NULL)
632 THEN
633 -- If PO line with the same PO line number exists, check if matches
634 --autocreate grouping, need to pass in the draft_id and get the po_line_id_to_compare from po_lines_merge_v, so that 'add to mod' case is taken care of.
635 BEGIN
636 SELECT po_line_id
637 INTO l_po_line_id_to_compare
638 FROM po_lines_merge_v
639 WHERE po_header_id = p_add_to_po_header_id
640 AND draft_id = p_draft_id
641 AND line_num = l_po_line_num;
642
643 lines_match(
644 p_agreement_id,
645 p_supplier_id,
646 p_site_id,
647 l_req_line_id,
648 NULL, -- No req line ID to compare
649 l_po_line_id_to_compare,
650 p_draft_id, --Autocreate grouping
651 'Y', --p_relax_item_desc_check Autocreate grouping
652 l_message_code,
653 l_token_name,
654 l_token_value
655 );
656 EXCEPTION
657 WHEN NO_DATA_FOUND THEN
658 NULL; -- No need to check if no PO line with same PO line number
659 END;
660 END IF; -- Check for PO line mismatch if all req lines match
661 END IF; -- If there is no error yet
662
663 -- If there is a mismatch, set error message
664 IF (l_message_code IS NOT NULL)
665 THEN
666 x_message_code_tbl(i) := l_message_code;
667 x_token_name_tbl(i) := l_token_name;
668 x_token_value_tbl(i) := l_token_value;
669 END IF; -- If there is a mismatch, set error message
670 END LOOP; -- Loop through all doc builder req lines
671
672 -- Clean up temp table
673 DELETE FROM po_session_gt
674 WHERE key = l_key;
675
676 IF (PO_LOG.d_proc) THEN
677 PO_LOG.proc_end(d_mod, 'x_message_code_tbl', x_message_code_tbl);
678 PO_LOG.proc_end(d_mod, 'x_token_name_tbl', x_token_name_tbl);
679 PO_LOG.proc_end(d_mod, 'x_token_value_tbl', x_token_value_tbl);
680 END IF;
681
682 EXCEPTION
683 WHEN OTHERS THEN
684 -- Clean up temp table
685 DELETE FROM po_session_gt
686 WHERE key = l_key;
687
688 -- Close cursor if open
689 IF (req_line_ids_csr%ISOPEN)
690 THEN
691 CLOSE req_line_ids_csr;
692 END IF;
693
694 IF (PO_LOG.d_exc) THEN
695 PO_LOG.exc(d_mod, d_position, 'An error occured in check_po_line_numbers');
696 END IF;
697
698 RAISE;
699 END check_po_line_numbers;
700
701
702 PROCEDURE check_neg_line_numbers(
703 p_req_line_id_tbl IN PO_TBL_NUMBER,
704 p_sol_line_number_display_tbl IN PO_TBL_VARCHAR30,
705 p_sol_line_number_tbl IN PO_TBL_NUMBER,
706 p_draft_id IN NUMBER, --Autocreate grouping
707 x_message_code_tbl OUT NOCOPY PO_TBL_VARCHAR30,
708 x_token_name_tbl OUT NOCOPY PO_TBL_VARCHAR30,
709 x_token_value_tbl OUT NOCOPY PO_TBL_VARCHAR2000
710 )
711 IS
712 d_mod CONSTANT VARCHAR2(100) := D_check_neg_line_numbers;
713 d_position NUMBER := 0;
714
715 l_num_lines NUMBER;
716 l_req_line_id PO_REQUISITION_LINES_ALL.requisition_line_id%TYPE;
717 l_sol_line_num_disp PON_AUCTION_ITEM_PRICES_ALL.LINE_NUM_DISPLAY%TYPE;
718 l_sol_line_num PON_AUCTION_ITEM_PRICES_ALL.LINE_NUMBER%TYPE;
719 l_message_code VARCHAR2(30) := NULL;
720 l_token_name VARCHAR2(30) := NULL;
721 l_token_value VARCHAR2(2000) := NULL;
722 req_line_type_id PO_REQUISITION_LINES_ALL.line_type_id%TYPE;
723 req_group_line_id PO_REQUISITION_LINES_ALL.line_type_id%TYPE;
724 sol_line_type_id PON_AUCTION_ITEM_PRICES_ALL.line_type_id%TYPE;
725 req_item_id PO_REQUISITION_LINES_ALL.item_id%TYPE;
726 sol_item_id PON_AUCTION_ITEM_PRICES_ALL.item_id%TYPE;
727 req_item_revision PO_REQUISITION_LINES_ALL.item_revision%TYPE;
728 sol_item_revision PON_AUCTION_ITEM_PRICES_ALL.item_revision%TYPE;
729 req_category_id PO_REQUISITION_LINES_ALL.category_id%TYPE;
730 sol_category_id PON_AUCTION_ITEM_PRICES_ALL.category_id%TYPE;
731 sol_group_line_id PON_AUCTION_ITEM_PRICES_ALL.group_line_id%TYPE;
732 sol_clm_info_flag PON_AUCTION_ITEM_PRICES_ALL.clm_info_flag%TYPE;
733 sol_clm_option_indicator PON_AUCTION_ITEM_PRICES_ALL.clm_option_indicator%TYPE;
734 sol_contract_type PON_AUCTION_HEADERS_ALL.contract_type%TYPE;
735 req_line_ip_category_id PO_LINES_ALL.ip_category_id%TYPE;
736 sol_ip_category_id PON_AUCTION_ITEM_PRICES_ALL.ip_category_id%TYPE;
737 sol_uom_code VARCHAR2(10);
738 req_line_uom VARCHAR2(10);
739 req_ship_to_location_id hr_locations.ship_to_location_id%TYPE;
740 message_suffix VARCHAR2(2);
741 sol_ship_to_location_id PON_AUCTION_ITEM_PRICES_ALL.ship_to_location_id%TYPE;
742 sol_line_orig_code PON_AUCTION_ITEM_PRICES_ALL.LINE_ORIGINATION_CODE%TYPE;
743 BEGIN
744 IF (PO_LOG.d_proc) THEN
745 PO_LOG.proc_begin(d_mod);
746 PO_LOG.proc_begin(d_mod,'p_req_line_id_tbl',p_req_line_id_tbl);
747 PO_LOG.proc_begin(d_mod,'p_sol_line_number_tbl',p_sol_line_number_tbl);
748 END IF;
749
750 -- Get the number of lines being passed in.
751 l_num_lines := p_req_line_id_tbl.COUNT;
752
753 -- Initialize error message values to be returned.
754 x_message_code_tbl := PO_TBL_VARCHAR30();
755 x_message_code_tbl.EXTEND(l_num_lines);
756 x_token_name_tbl := PO_TBL_VARCHAR30();
757 x_token_name_tbl.EXTEND(l_num_lines);
758 x_token_value_tbl := PO_TBL_VARCHAR2000();
759 x_token_value_tbl.EXTEND(l_num_lines);
760
761 BEGIN
762 SELECT paha.contract_type,pon_auction_pkg.get_message_suffix(doc.internal_name)
763 INTO sol_contract_type,message_suffix
764 FROM pon_auction_headers_all paha, pon_auc_doctypes doc
765 WHERE paha.auction_header_id =p_draft_id
766 and paha.doctype_id = doc.doctype_id;
767
768 EXCEPTION
769 WHEN no_data_found THEN
770 RETURN;
771 END;
772
773
774 FOR i IN 1..l_num_lines -- Loop through all doc builder req lines
775 LOOP
776 -- Initialize the message code to NULL for each iteration
777 l_message_code := NULL;
778
779 l_req_line_id := p_req_line_id_tbl(i);
780 l_sol_line_num_disp := p_sol_line_number_display_tbl(i);
781 l_sol_line_num := p_sol_line_number_tbl(i);
782
783 SELECT prl.line_type_id, nvl(prl.item_id, -1), nvl(prl.item_revision, -1), nvl(prl.category_id, -1)
784 , nvl(pol.ip_category_id, -1)
785 ,nvl(decode(plt.order_type_lookup_code, 'AMOUNT', '1', mom.uom_code), 'NULL')
786 ,prl.group_line_id,
787 NVL((SELECT nvl(ship_to_location_id,location_id) FROM hr_locations WHERE location_id = prl.deliver_to_location_id), -1)
788 INTO req_line_type_id, req_item_id, req_item_revision, req_category_id
789 ,req_line_ip_category_id
790 ,req_line_uom
791 ,req_group_line_id
792 ,req_ship_to_location_id
793 FROM po_requisition_lines_all prl,po_line_types_b plt, mtl_units_of_measure mom, po_lines_all pol
794 WHERE prl.requisition_line_id = l_req_line_id
795 AND prl.line_type_id = plt.line_type_id
796 AND mom.unit_of_measure (+) = prl.unit_meas_lookup_code
797 AND pol.po_header_id(+) = prl.blanket_po_header_id AND pol.line_num(+) = prl.blanket_po_line_num;
798
799 IF(req_group_line_id IS NOT null) then
800 l_message_code := null;
801 l_token_name := NULL;
802 l_token_value := NULL;
803 CONTINUE;
804 END IF;
805
806
807 IF (PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_draft_id) = 0) THEN
808
809 BEGIN
810
811 SELECT paip.line_type_id, nvl(paip.item_id, -1), nvl(paip.item_revision, -1), nvl(paip.category_id, -1), paip.group_line_id,
812 Nvl(paip.clm_info_flag , 'N'),
813 nvl(paip.clm_option_indicator, 'B')
814 ,nvl(paip.ip_category_id, -1)
815 ,paip.uom_code
816 ,paip.ship_to_location_id
817 ,paip.line_origination_code
818 INTO sol_line_type_id, sol_item_id, sol_item_revision, sol_category_id, sol_group_line_id,
819 sol_clm_info_flag,sol_clm_option_indicator
820 ,sol_ip_category_id
821 ,sol_uom_code
822 ,sol_ship_to_location_id
823 ,sol_line_orig_code
824 FROM pon_auction_item_prices_all paip
825 WHERE paip.line_number =l_sol_line_num
826 AND paip.auction_header_id =p_draft_id;
827
828 EXCEPTION
829
830 WHEN no_data_found THEN
831 l_message_code := null;
832 l_token_name := NULL;
833 l_token_value := NULL;
834 CONTINUE;
835 END;
836 END IF;
837
838 IF (PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_draft_id) = 1) THEN
839
840 BEGIN
841 SELECT paip.line_type_id, nvl(paip.item_id, -1), nvl(paip.item_revision, -1), nvl(paip.category_id, -1), paip.group_line_id,
842 Nvl(paip.clm_info_flag , 'N'),
843 nvl(paip.clm_option_indicator, 'B')
844 ,nvl(paip.ip_category_id, -1)
845 ,paip.uom_code
846 ,paip.ship_to_location_id
847 ,paip.line_origination_code
848 INTO sol_line_type_id, sol_item_id, sol_item_revision, sol_category_id, sol_group_line_id,
849 sol_clm_info_flag,sol_clm_option_indicator
850 ,sol_ip_category_id
851 ,sol_uom_code
852 ,sol_ship_to_location_id
853 ,sol_line_orig_code
854 FROM pon_auction_item_prices_all paip
855 WHERE paip.line_num_display =l_sol_line_num_disp
856 AND paip.auction_header_id =p_draft_id;
857
858
859 EXCEPTION
860
861 WHEN no_data_found THEN
862 l_message_code := null;
863 l_token_name := NULL;
864 l_token_value := NULL;
865 CONTINUE;
866 END;
867
868 END IF;
869
870
871 -- If there is no error yet, check if the PO line number is NULL.
872 IF (l_message_code IS NULL AND ((l_sol_line_num_disp IS NULL AND PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_draft_id) = 1)
873 OR (l_sol_line_num IS NULL AND PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_draft_id) = 0)
874 ))
875 THEN
876 l_message_code := 'PO_ALL_NOT_NULL';
877 l_token_name := NULL;
878 l_token_value := NULL;
879 END IF;
880
881 -- If there is no error yet, check if the PO line number is less than or
882 -- equal to 0.
883 /*IF (l_message_code IS NULL AND l_sol_line_num_disp <= 0)
884 THEN
885 l_message_code := 'PO_ALL_ENTER_VALUE_GT_ZERO';
886 l_token_name := NULL;
887 l_token_value := NULL;
888 END IF;*/
889
890 IF (req_line_type_id <> sol_line_type_id)
891 THEN
892
893 l_message_code := 'PO_LINE_TYPE_ID_MATCH'||message_suffix;
894 l_token_name := NULL;
895 l_token_value := NULL;
896 END IF;
897
898 IF (req_item_id <> sol_item_id)
899 THEN
900 l_message_code := 'PO_ITEM_ID_MATCH'||message_suffix;
901 l_token_name := NULL;
902 l_token_value := NULL;
903 END IF;
904
905 IF (req_item_revision <> sol_item_revision)
906 THEN
907 l_message_code := 'PO_ITEM_REVISION_MATCH'||message_suffix;
908 l_token_name := NULL;
909 l_token_value := NULL;
910 END IF;
911
912 IF (req_category_id <> sol_category_id)
913 THEN
914 l_message_code := 'PO_CATEGORY_ID_MATCH'||message_suffix;
915 l_token_name := NULL;
916 l_token_value := NULL;
917 END IF;
918
919 IF (sol_contract_type <> 'STANDARD' AND req_line_ip_category_id <> sol_ip_category_id)
920 THEN
921 l_message_code := 'PO_IP_CATEGORY_MATCH'||message_suffix;
922 l_token_name := NULL;
923 l_token_value := NULL;
924 END IF;
925
926 IF (req_line_uom <> sol_uom_code)
927 THEN
928 l_message_code := 'PO_UOM_MATCH'||message_suffix;
929 l_token_name := NULL;
930 l_token_value := NULL;
931 END IF;
932 --Bug 13644122
933 --Added Ship to Location and Line Origination Code in validation
934 IF (req_ship_to_location_id <> sol_ship_to_location_id)
935 THEN
936 l_message_code := 'PO_SHIP_TO_LOC_ID_MATCH'||message_suffix;
937 l_token_name := NULL;
938 l_token_value := NULL;
939 END IF;
940
941 IF (sol_line_orig_code <> 'REQUISITION')
942 THEN
943 l_message_code := 'PO_LINE_ORIG_CODE_MATCH'||message_suffix;
944 l_token_name := NULL;
945 l_token_value := NULL;
946 END IF;
947
948 IF (sol_group_line_id IS NOT NULL AND (PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_draft_id) = 1))
949 THEN
950 l_message_code := 'PO_INVALID_CLIN_MATCH'||message_suffix;
951 l_token_name := NULL;
952 l_token_value := NULL;
953 END IF;
954
955 IF(sol_clm_info_flag <> 'N' AND (PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_draft_id) = 1))
956 THEN
957 l_message_code := 'PO_INFO_LINE_MATCH'||message_suffix;
958 l_token_name := NULL;
959 l_token_value := NULL;
960 END IF;
961
962 IF(sol_clm_option_indicator = 'O' AND (PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_draft_id) = 1))
963 THEN
964 l_message_code := 'PO_OPTION_LINE_MATCH'||message_suffix;
965 l_token_name := NULL;
966 l_token_value := NULL;
967 END IF;
968
969
970 IF (l_message_code IS NOT NULL)
971 THEN
972 x_message_code_tbl(i) := l_message_code;
973 x_token_name_tbl(i) := l_token_name;
974 x_token_value_tbl(i) := l_token_value;
975 END IF; -- If there is a mismatch, set error message
976
977 END LOOP;
978
979 EXCEPTION
980 WHEN OTHERS THEN
981
982 IF (PO_LOG.d_exc) THEN
983 PO_LOG.exc(d_mod, d_position, 'An error occured in check_neg_line_numbers');
984 END IF;
985
986 RAISE;
987 END check_neg_line_numbers;
988
989
990 -------------------------------------------------------------------------------
991 --Start of Comments
992 --Name: lines_match
993 --Pre-reqs:
994 -- None
995 --Modifies:
996 -- None
997 --Locks:
998 -- None
999 --Function:
1000 -- Checks whether a requisition line and a requisition/PO line match and
1001 -- returns an error message indicating the result.
1002 --Parameters:
1003 --IN:
1004 --p_agreement_id
1005 -- The ID of the agreement specified in the document builder.
1006 --p_supplier_id
1007 -- The ID of the supplier specified in the document builder.
1008 --p_site_id
1009 -- The ID of the site specified in the document builder.
1010 --p_req_line_id
1011 -- The ID of the first requisition line to compare with.
1012 --p_req_line_id_to_compare
1013 -- If the second line to compare with is a requisition line, this variable
1014 -- holds the ID of that requisition line. The second line is either a
1015 -- requisition or PO line, so this variable may or may not be null.
1016 --p_po_line_id_to_compare
1017 -- If the second line to compare with is a PO line, this variable holds the ID
1018 -- of that PO line. The second line is either a requisition or PO line, so
1019 -- this variable may or may not be null.
1020 --OUT:
1021 --x_message_code
1022 -- The error message code corresponding to whether the two lines match or not.
1023 --x_token_name
1024 -- The error message token name corresponding to whether the two lines match
1025 -- or not.
1026 --x_token_value
1027 -- The error message token value corresponding to whether the two lines match
1028 -- or not.
1029 --Notes:
1030 -- Between the input parameters p_req_line_id_to_compare and
1031 -- p_po_line_id_to_compare, one of them has to be null and one of them has to
1032 -- have a value.
1033 --Testing:
1034 -- None
1035 --End of Comments
1036 -------------------------------------------------------------------------------
1037 PROCEDURE lines_match(
1038 p_agreement_id IN NUMBER,
1039 p_supplier_id IN NUMBER,
1040 p_site_id IN NUMBER,
1041 p_req_line_id IN NUMBER,
1042 p_req_line_id_to_compare IN NUMBER,
1043 p_po_line_id_to_compare IN NUMBER,
1044 p_draft_id IN NUMBER := -1, --Autocreate grouping
1045 p_relax_item_desc_check IN VARCHAR2, --Autocreate grouping
1046 x_message_code OUT NOCOPY VARCHAR2,
1047 x_token_name OUT NOCOPY VARCHAR2,
1048 x_token_value OUT NOCOPY VARCHAR2
1049 )
1050 IS
1051 d_mod CONSTANT VARCHAR2(100) := D_lines_match;
1052 d_position NUMBER := 0;
1053
1054 l_token_value VARCHAR2(2000);
1055 BEGIN
1056 IF (PO_LOG.d_proc) THEN
1057 PO_LOG.proc_begin(d_mod);
1058 PO_LOG.proc_begin(d_mod,'p_agreement_id',p_agreement_id);
1059 PO_LOG.proc_begin(d_mod,'p_supplier_id',p_supplier_id);
1060 PO_LOG.proc_begin(d_mod,'p_site_id',p_site_id);
1061 PO_LOG.proc_begin(d_mod,'p_req_line_id',p_req_line_id);
1062 PO_LOG.proc_begin(d_mod,'p_req_line_id_to_compare',p_req_line_id_to_compare);
1063 PO_LOG.proc_begin(d_mod,'p_po_line_id_to_compare',p_po_line_id_to_compare);
1064 END IF;
1065
1066 lines_info_match(
1067 p_agreement_id,
1068 p_req_line_id,
1069 p_req_line_id_to_compare,
1070 p_po_line_id_to_compare,
1071 p_draft_id, --Autocreate grouping
1072 p_relax_item_desc_check, --Autocreate grouping
1073 x_message_code,
1074 x_token_name,
1075 x_token_value
1076 );
1077
1078 -- If there is a line-level error, no need to check shipment-level attributes
1079 IF x_message_code IS NOT NULL THEN
1080 RETURN;
1081 END IF;
1082
1083 lines_delivery_info_match(
1084 p_supplier_id,
1085 p_site_id,
1086 p_req_line_id,
1087 p_req_line_id_to_compare,
1088 p_po_line_id_to_compare,
1089 p_draft_id, --Autocreate grouping
1090 x_message_code,
1091 x_token_name,
1092 x_token_value
1093 );
1094
1095 IF (PO_LOG.d_proc) THEN
1096 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
1097 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
1098 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
1099 END IF;
1100 EXCEPTION
1101 WHEN OTHERS THEN
1102 IF (PO_LOG.d_exc) THEN
1103 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_match');
1104 END IF;
1105
1106 RAISE;
1107 END lines_match;
1108
1109 -------------------------------------------------------------------------------
1110 --Start of Comments
1111 --Name: group_req_lines
1112 --Pre-reqs: None
1113 --Modifies:
1114 --Locks:
1115 -- None
1116 --Function:
1117 -- Defaults the PO line numbers for requisition lines in the Autocreate
1118 -- Document Builder based on either the 'Requisition' or 'Default' grouping
1119 -- method.
1120 --Parameters:
1121 --IN:
1122 --p_req_line_id_tbl
1123 -- Table of requisition line IDs representing the Autocreate Document Builder.
1124 --p_req_line_num_tbl
1125 -- Table of requisition line numbers for the req lines in the Doc Builder.
1126 --p_po_line_num_tbl
1127 -- Table of PO line numbers currently assigned to the Document Builder
1128 -- requisition lines.
1129 --p_add_to_po_header_id
1130 -- The ID of the PO to which the requisition lines are being added
1131 -- (will be null when creating a new PO).
1132 --p_builder_agreement_id
1133 -- The ID of the Global Agreement for which the PO will be created.
1134 --p_builder_supplier_id
1135 -- The ID of the Supplier for which the PO will be created.
1136 --p_builder_site_id
1137 -- The ID of the Site for which the PO will be created.
1138 --p_builder_org_id
1139 -- The ID of the Operating Unit for which the PO will be created.
1140 --p_start_index
1141 -- The index of the first requisition line in the input table which
1142 -- should have a PO line number calculated (default value of 1
1143 -- if not specified).
1144 --p_end_index
1145 -- The index of the last requisition line in the input table which
1146 -- should have a PO line number calculated (default value of last
1147 -- index in the table if not specified).
1148 --p_grouping_method
1149 -- Grouping method; possible values are 'DEFAULT' or 'REQUISITION'.
1150 --Returns:
1151 -- Table of PO line numbers corresponding to each of the input requisition
1152 -- lines.
1153 --Notes:
1154 -- N/A
1155 --Testing:
1156 -- N/A
1157 --End of Comments
1158 -------------------------------------------------------------------------------
1159 FUNCTION group_req_lines
1160 (
1161 p_req_line_id_tbl IN PO_TBL_NUMBER
1162 , p_req_line_num_tbl IN PO_TBL_NUMBER
1163 , p_po_line_num_tbl IN PO_TBL_NUMBER
1164 , p_add_to_po_header_id IN NUMBER
1165 , p_builder_agreement_id IN NUMBER
1166 , p_builder_supplier_id IN NUMBER
1167 , p_builder_site_id IN NUMBER
1168 , p_builder_org_id IN NUMBER
1169 , p_start_index IN NUMBER
1170 , p_end_index IN NUMBER
1171 , p_grouping_method IN VARCHAR2
1172 )
1173 RETURN PO_TBL_NUMBER
1174 IS
1175 l_start_index NUMBER;
1176 l_end_index NUMBER;
1177
1178 l_consigned_flag_tbl PO_TBL_VARCHAR1;
1179 l_po_line_num_tbl PO_TBL_NUMBER := p_po_line_num_tbl;
1180 x_po_line_num_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1181
1182 d_mod CONSTANT VARCHAR2(100) := D_group_req_lines;
1183 d_position NUMBER := 0;
1184
1185 l_grouping_method VARCHAR2(100) := 'REQUISITION';
1186
1187 BEGIN
1188
1189 l_grouping_method := p_grouping_method;
1190
1191 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
1192
1193 -- Initialize start and end indices to be first and last
1194 -- indices in the input req line table (if they were not
1195 -- specified in the input parameters).
1196 --
1197 l_start_index := nvl(p_start_index, 1);
1198 l_end_index := nvl(p_end_index, p_req_line_id_tbl.COUNT);
1199
1200 d_position := 5;
1201
1202 IF PO_LOG.d_stmt THEN
1203 PO_LOG.stmt(d_mod,d_position,'l_start_index',l_start_index);
1204 PO_LOG.stmt(d_mod,d_position,'l_end_index',l_end_index);
1205 PO_LOG.stmt(d_mod,d_position,'p_grouping_method',p_grouping_method);
1206 END IF;
1207
1208 -- Clear PO line numbers from input table for those indices
1209 -- which will be defaulted.
1210 --
1211 FOR i IN l_start_index..l_end_index LOOP
1212 l_po_line_num_tbl(i) := NULL;
1213 END LOOP;
1214
1215 d_position := 10;
1216
1217 -- Call separate grouping procedures depending on grouping method.
1218 --
1219 IF ( l_grouping_method = 'REQUISITION' ) THEN
1220
1221 d_position := 20;
1222
1223 -- Use req line numbers for the PO line numbers iff...
1224 -- (a) "PO: Use Requisition Line Numbers for Autocreate" is set, and
1225 -- (b) we are creating a new PO, and
1226 -- (c) all req lines in the Doc Builder come from the same req.
1227 --
1228 IF ( ( FND_PROFILE.value('PO_USE_REQ_NUM_IN_AUTOCREATE') = 'Y' )
1229 AND ( p_add_to_po_header_id IS NULL )
1230 AND ( has_same_req_header(p_req_line_id_tbl) ) )
1231 THEN
1232 d_position := 30;
1233
1234 x_po_line_num_tbl := group_by_requisition_line_num
1235 ( p_req_line_num_tbl
1236 , l_po_line_num_tbl
1237 , l_start_index
1238 , l_end_index
1239 );
1240 -- Else, just sequentially number the req lines starting from
1241 -- the max line number currently in the Doc Builder or on the
1242 -- PO being added to.
1243 --
1244 ELSE
1245 d_position := 40;
1246
1247 x_po_line_num_tbl := group_by_requisition_seq_num
1248 ( l_po_line_num_tbl
1249 , p_add_to_po_header_id
1250 , l_start_index
1251 , l_end_index
1252 );
1253 END IF;
1254
1255 ELSE -- ( p_grouping_method = 'DEFAULT' )
1256
1257 d_position := 50;
1258
1259 -- Derive the Consigned Flag for each requisition line. It is too
1260 -- difficult to derive the flag in the query, so we will get it
1261 -- here by calling an API for each req line.
1262 --
1263 l_consigned_flag_tbl := get_consigned_flag_tbl ( p_req_line_id_tbl
1264 , p_builder_org_id
1265 , p_builder_supplier_id
1266 , p_builder_site_id
1267 );
1268 x_po_line_num_tbl := group_by_default ( p_req_line_id_tbl
1269 , l_po_line_num_tbl
1270 , l_consigned_flag_tbl
1271 , p_add_to_po_header_id
1272 , p_builder_agreement_id
1273 , l_start_index
1274 , l_end_index
1275 );
1276 END IF;
1277
1278 d_position := 60;
1279
1280 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,x_po_line_num_tbl); END IF;
1281
1282 return (x_po_line_num_tbl);
1283
1284 EXCEPTION
1285
1286 WHEN OTHERS THEN
1287 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
1288 RAISE;
1289
1290 END group_req_lines;
1291
1292 -------------------------------------------------------------------------------
1293 --Start of Comments
1294 --Name: group_req_lines Overloaded function
1295 --Pre-reqs: None
1296 --Modifies:
1297 --Locks:
1298 -- None
1299 --Function:
1300 -- Defaults the PO line number (CLM Line Number) for requisition lines in the Autocreate
1301 -- Document Builder based on either the 'Requisition' or 'Default' grouping
1302 -- method.
1303 --Parameters:
1304 --IN:
1305 --p_req_line_id_tbl
1306 -- Table of requisition line IDs representing the Autocreate Document Builder.
1307 --p_req_line_num_tbl
1308 -- Table of requisition line numbers (CLM Line Number) for the req lines in the Doc Builder.
1309 --p_po_line_num_tbl
1310 -- Table of PO line numbers (CLM Line Number) currently assigned to the Document Builder
1311 -- requisition lines.
1312 --p_add_to_po_header_id
1313 -- The ID of the PO to which the requisition lines are being added
1314 -- (will be null when creating a new PO).
1315 --p_builder_agreement_id
1316 -- The ID of the Global Agreement for which the PO will be created.
1317 --p_builder_supplier_id
1318 -- The ID of the Supplier for which the PO will be created.
1319 --p_builder_site_id
1320 -- The ID of the Site for which the PO will be created.
1321 --p_builder_org_id
1322 -- The ID of the Operating Unit for which the PO will be created.
1323 --p_start_index
1324 -- The index of the first requisition line in the input table which
1325 -- should have a PO line number calculated (default value of 1
1326 -- if not specified).
1327 --p_end_index
1328 -- The index of the last requisition line in the input table which
1329 -- should have a PO line number calculated (default value of last
1330 -- index in the table if not specified).
1331 --p_grouping_method
1332 -- Grouping method; possible values are 'DEFAULT' or 'REQUISITION'.
1333 --Returns:
1334 -- Table of PO line numbers (CLM Line Number) corresponding to each of the input requisition
1335 -- lines.
1336 --Notes:
1337 -- N/A
1338 --Testing:
1339 -- N/A
1340 --End of Comments
1341 -------------------------------------------------------------------------------
1342 Procedure group_clm_req_lines
1343 (
1344 p_req_line_id_tbl IN PO_TBL_NUMBER
1345 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
1346 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
1347 , p_req_option_flag_tbl IN PO_TBL_VARCHAR1
1348 , p_req_line_num_tbl IN PO_TBL_VARCHAR100
1349 , p_po_line_num_tbl IN OUT NOCOPY PO_TBL_NUMBER
1350 , p_po_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
1351 , p_add_to_po_header_id IN NUMBER
1352 , p_draft_id IN NUMBER --Autocreate grouping
1353 , p_builder_agreement_id IN NUMBER
1354 , p_builder_supplier_id IN NUMBER
1355 , p_builder_site_id IN NUMBER
1356 , p_builder_org_id IN NUMBER
1357 , p_start_index IN NUMBER
1358 , p_end_index IN NUMBER
1359 , p_grouping_method IN VARCHAR2
1360 )
1361 IS
1362 l_start_index NUMBER;
1363 l_end_index NUMBER;
1364
1365 l_consigned_flag_tbl PO_TBL_VARCHAR1;
1366 l_po_line_num_disp_tbl PO_TBL_VARCHAR100 := p_po_line_num_disp_tbl;
1367 x_po_line_num_disp_tbl PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
1368 x_po_line_num_tbl PO_TBL_NUMBER := p_po_line_num_tbl;
1369
1370 d_mod CONSTANT VARCHAR2(100) := D_group_clm_req_lines ;
1371 d_position NUMBER := 0;
1372 l_grouping_method VARCHAR2(50);
1373 l_req_group_line_id_tbl PO_TBL_NUMBER := p_req_group_line_id_tbl;
1374 l_renumber_flag PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
1375 l_line_num NUMBER := 1;
1376 l_max_line_num NUMBER := 0;
1377
1378
1379 BEGIN
1380
1381 l_grouping_method := p_grouping_method;
1382
1383
1384 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
1385
1386 -- Initialize start and end indices to be first and last
1387 -- indices in the input req line table (if they were not
1388 -- specified in the input parameters).
1389 --
1390 l_start_index := nvl(p_start_index, 1);
1391 l_end_index := nvl(p_end_index, p_req_line_id_tbl.COUNT);
1392 l_renumber_flag.extend(p_req_line_id_tbl.Count);
1393
1394 d_position := 5;
1395
1396 IF PO_LOG.d_stmt THEN
1397 PO_LOG.stmt(d_mod,d_position,'l_start_index',l_start_index);
1398 PO_LOG.stmt(d_mod,d_position,'l_end_index',l_end_index);
1399 PO_LOG.stmt(d_mod,d_position,'p_grouping_method',p_grouping_method);
1400 END IF;
1401
1402 d_position := 10;
1403 -- Clear PO line numbers from input table for those indices
1404 -- which will be defaulted.
1405 --
1406 FOR i IN l_start_index..l_end_index LOOP
1407 p_po_line_num_tbl(i) := NULL;
1408 END LOOP;
1409
1410 -- Call separate grouping procedures depending on grouping method.
1411
1412 IF ( l_grouping_method = 'REQUISITION' ) THEN
1413
1414 d_position := 20;
1415
1416 -- Use req line numbers for the PO line numbers iff...
1417 -- (a) "PO: Use Requisition Line Numbers for Autocreate" is set, and
1418 -- (b) we are creating a new PO, and
1419 -- (c) all req lines in the Doc Builder come from the same req.
1420 --
1421 IF ( ( FND_PROFILE.value('PO_USE_REQ_NUM_IN_AUTOCREATE') = 'Y' )
1422 AND ( p_add_to_po_header_id IS NULL )
1423 AND ( has_same_req_header(p_req_line_id_tbl) ) )
1424 THEN
1425 d_position := 30;
1426
1427 x_po_line_num_disp_tbl := group_by_clm_req_line_num
1428 ( p_req_line_num_tbl
1429 , l_po_line_num_disp_tbl
1430 , l_start_index
1431 , l_end_index
1432 );
1433
1434 l_line_num := 1;
1435 --Stamping the internal line nums
1436 FOR i IN l_start_index..l_end_index
1437 LOOP
1438 x_po_line_num_tbl(i) := l_line_num;
1439 l_line_num := l_line_num + 1;
1440 END LOOP;
1441
1442 -- Else, just sequentially number the req lines starting from
1443 -- the max line number currently in the Doc Builder or on the
1444 -- PO being added to.
1445 --
1446 ELSE
1447 d_position := 40;
1448
1449 x_po_line_num_disp_tbl := group_by_clm_req_seq_num
1450 ( l_po_line_num_disp_tbl
1451 , p_req_line_id_tbl
1452 , p_req_group_line_id_tbl
1453 , p_req_clm_info_flag_tbl
1454 , p_add_to_po_header_id
1455 , p_draft_id --Autocreate grouping
1456 , l_start_index
1457 , l_end_index
1458 );
1459 --Fetching the max internal line num available across all open draft mods
1460 l_max_line_num := get_max_po_line_num
1461 ( p_po_line_num_tbl
1462 , p_add_to_po_header_id
1463 );
1464 l_line_num := l_max_line_num + 1;
1465 --Stamping the internal line nums
1466 FOR i IN l_start_index..l_end_index
1467 LOOP
1468 x_po_line_num_tbl(i) := l_line_num;
1469 l_line_num := l_line_num + 1;
1470 END LOOP;
1471 END IF;
1472 p_po_line_num_disp_tbl := x_po_line_num_disp_tbl;
1473 p_po_line_num_tbl := x_po_line_num_tbl;
1474
1475 ELSE -- ( p_grouping_method = 'DEFAULT' )
1476
1477 --Removing the commented out code for p_grouping_method = 'DEFAULT'
1478
1479 /* Autocreate grouping start */
1480 d_position := 50;
1481
1482 -- Derive the Consigned Flag for each requisition line. It is too
1483 -- difficult to derive the flag in the query, so we will get it
1484 -- here by calling an API for each req line.
1485 --
1486 l_consigned_flag_tbl := get_consigned_flag_tbl ( p_req_line_id_tbl
1487 , p_builder_org_id
1488 , p_builder_supplier_id
1489 , p_builder_site_id
1490 );
1491
1492 -- Derive the internal po_line_num after grouping
1493 clm_group_by_default ( p_req_line_id_tbl
1494 , p_req_group_line_id_tbl
1495 , p_req_clm_info_flag_tbl
1496 , p_req_option_flag_tbl
1497 , p_po_line_num_disp_tbl --IN OUT
1498 , l_consigned_flag_tbl
1499 , p_add_to_po_header_id
1500 , p_draft_id --Autocreate grouping
1501 , p_builder_agreement_id
1502 , l_start_index
1503 , l_end_index
1504 , p_po_line_num_tbl --IN OUT
1505 );
1506
1507 IF PO_LOG.d_stmt THEN
1508 PO_LOG.stmt(d_mod,d_position,'p_po_line_num_tbl',p_po_line_num_tbl);
1509 END IF;
1510
1511 -- In case of default po line number flow,renumber flag is passed as Y
1512 -- for all the lines in the range ( between p_start_index and p_end_index )
1513 FOR i IN l_start_index..l_end_index LOOP
1514 l_renumber_flag(i) := 'Y';
1515 END LOOP;
1516
1517 -- Stamp po_line_num_display for alll priced clins
1518 get_line_num_disp_for_clin( p_po_line_num_tbl
1519 , p_req_line_id_tbl
1520 , p_req_option_flag_tbl
1521 , p_draft_id
1522 , p_req_group_line_id_tbl
1523 , p_req_clm_info_flag_tbl
1524 , p_add_to_po_header_id
1525 , p_start_index
1526 , p_end_index
1527 , p_po_line_num_disp_tbl
1528 );
1529
1530 d_position := 55;
1531
1532 IF PO_LOG.d_stmt THEN
1533 PO_LOG.stmt(d_mod,d_position,'p_po_line_num_disp_tbl',p_po_line_num_disp_tbl);
1534 END IF;
1535
1536 --Renumber all Slins based on the po_line_num and
1537 --po_line_num_disp(for clins) derived above
1538
1539 RenumberSlins( p_req_line_id_tbl
1540 , p_req_group_line_id_tbl
1541 , p_po_line_num_tbl
1542 , p_po_line_num_disp_tbl --IN OUT
1543 , p_req_clm_info_flag_tbl
1544 , l_renumber_flag
1545 , p_start_index
1546 , p_end_index
1547 , p_add_to_po_header_id
1548 , p_draft_id
1549 );
1550
1551 d_position := 58;
1552
1553 IF PO_LOG.d_stmt THEN
1554 PO_LOG.stmt(d_mod,d_position,'p_po_line_num_disp_tbl',p_po_line_num_disp_tbl);
1555 END IF;
1556
1557 /* Autocreate grouping end */
1558 END IF;
1559
1560
1561 d_position := 60;
1562
1563 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,p_po_line_num_tbl); END IF;
1564
1565
1566 EXCEPTION
1567
1568 WHEN OTHERS THEN
1569 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
1570 RAISE;
1571
1572 END group_clm_req_lines;
1573
1574
1575 -------------------------------------------------------------------------------
1576 --Start of Comments
1577 --Name: group_clm_sol_req_lines Overloaded function
1578 --Pre-reqs: None
1579 --Modifies:
1580 --Locks:
1581 -- None
1582 --Function:
1583 -- Defaults the Solicitation line number (CLM Line Number) for requisition lines in the Autocreate
1584 -- Document Builder based on 'Default' grouping
1585 -- method.
1586 --Parameters:
1587 --IN:
1588 --p_req_line_id_tbl
1589 -- Table of requisition line IDs representing the Autocreate Document Builder.
1590 --p_req_line_num_tbl
1591 -- Table of requisition line numbers (CLM Line Number) for the req lines in the Doc Builder.
1592 --p_neg_line_num_tbl
1593 -- Table of Solicitation line numbers (CLM Line Number) currently assigned to the Document Builder
1594 -- requisition lines.
1595 --p_add_to_neg_header_id
1596 -- The ID of the SOL to which the requisition lines are being added.
1597 --p_builder_agreement_id
1598 -- The ID of the Global Agreement for which the SOL will be created.
1599 --p_builder_supplier_id
1600 -- The ID of the Supplier for which the SOL will be created.
1601 --p_builder_site_id
1602 -- The ID of the Site for which the SOL will be created.
1603 --p_builder_org_id
1604 -- The ID of the Operating Unit for which the SOL will be created.
1605 --p_start_index
1606 -- The index of the first requisition line in the input table which
1607 -- should have a SOL line number calculated (default value of 1
1608 -- if not specified).
1609 --p_end_index
1610 -- The index of the last requisition line in the input table which
1611 -- should have a SOL line number calculated (default value of last
1612 -- index in the table if not specified).
1613 --p_grouping_method
1614 -- Grouping method; possible values are 'DEFAULT' or 'REQUISITION'.
1615 --Returns:
1616 -- Table of SOL line numbers (CLM Line Number) corresponding to each of the input requisition
1617 -- lines.
1618 --Notes:
1619 -- N/A
1620 --Testing:
1621 -- N/A
1622 --End of Comments
1623 -------------------------------------------------------------------------------
1624 Procedure group_clm_sol_req_lines
1625 (
1626 p_req_line_id_tbl IN PO_TBL_NUMBER
1627 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
1628 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
1629 , p_req_option_flag_tbl IN PO_TBL_VARCHAR1
1630 , p_req_line_num_tbl IN PO_TBL_VARCHAR100
1631 , p_neg_line_num_tbl IN OUT NOCOPY PO_TBL_NUMBER
1632 , p_neg_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
1633 , p_add_to_neg_header_id IN NUMBER
1634 , p_draft_id IN NUMBER --Autocreate grouping
1635 , p_builder_agreement_id IN NUMBER
1636 , p_builder_supplier_id IN NUMBER
1637 , p_builder_site_id IN NUMBER
1638 , p_builder_org_id IN NUMBER
1639 , p_start_index IN NUMBER
1640 , p_end_index IN NUMBER
1641 , p_grouping_method IN VARCHAR2
1642 )
1643 IS
1644 l_start_index NUMBER;
1645 l_end_index NUMBER;
1646
1647 l_consigned_flag_tbl PO_TBL_VARCHAR1;
1648 l_po_line_num_disp_tbl PO_TBL_VARCHAR100 := p_neg_line_num_disp_tbl;
1649 x_po_line_num_disp_tbl PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
1650 x_po_line_num_tbl PO_TBL_NUMBER := p_neg_line_num_tbl;
1651
1652 d_mod CONSTANT VARCHAR2(100) := D_group_clm_req_lines ;
1653 d_position NUMBER := 0;
1654 l_grouping_method VARCHAR2(50);
1655 l_req_group_line_id_tbl PO_TBL_NUMBER := p_req_group_line_id_tbl;
1656 l_renumber_flag PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
1657 l_line_num NUMBER := 1;
1658 l_max_line_num NUMBER := 0;
1659
1660
1661 BEGIN
1662
1663 l_grouping_method := p_grouping_method;
1664
1665
1666 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
1667
1668 -- Initialize start and end indices to be first and last
1669 -- indices in the input req line table (if they were not
1670 -- specified in the input parameters).
1671 --
1672 l_start_index := nvl(p_start_index, 1);
1673 l_end_index := nvl(p_end_index, p_req_line_id_tbl.COUNT);
1674 l_renumber_flag.extend(p_req_line_id_tbl.Count);
1675
1676 d_position := 5;
1677
1678 IF PO_LOG.d_stmt THEN
1679 PO_LOG.stmt(d_mod,d_position,'l_start_index',l_start_index);
1680 PO_LOG.stmt(d_mod,d_position,'l_end_index',l_end_index);
1681 PO_LOG.stmt(d_mod,d_position,'p_grouping_method',p_grouping_method);
1682 END IF;
1683
1684 d_position := 10;
1685 -- Clear PO line numbers from input table for those indices
1686 -- which will be defaulted.
1687 --
1688 FOR i IN l_start_index..l_end_index LOOP
1689 p_neg_line_num_tbl(i) := NULL;
1690 END LOOP;
1691
1692 -- Call separate grouping procedures depending on grouping method.
1693
1694
1695 d_position := 20;
1696
1697
1698 l_consigned_flag_tbl := get_consigned_flag_tbl ( p_req_line_id_tbl
1699 , p_builder_org_id
1700 , p_builder_supplier_id
1701 , p_builder_site_id
1702 );
1703
1704 -- Derive the internal po_line_num after grouping
1705 clm_group_sol_by_default ( p_req_line_id_tbl
1706 , p_req_group_line_id_tbl
1707 , p_req_clm_info_flag_tbl
1708 , p_req_option_flag_tbl
1709 , p_neg_line_num_disp_tbl --IN OUT
1710 , l_consigned_flag_tbl
1711 , p_add_to_neg_header_id
1712 , p_draft_id --Autocreate grouping
1713 , p_builder_agreement_id
1714 , l_start_index
1715 , l_end_index
1716 , p_neg_line_num_tbl --IN OUT
1717 );
1718
1719 IF PO_LOG.d_stmt THEN
1720 PO_LOG.stmt(d_mod,d_position,'p_neg_line_num_tbl',p_neg_line_num_tbl);
1721 END IF;
1722
1723 -- In case of default po line number flow,renumber flag is passed as Y
1724 -- for all the lines in the range ( between p_start_index and p_end_index )
1725 FOR i IN l_start_index..l_end_index LOOP
1726 l_renumber_flag(i) := 'Y';
1727 END LOOP;
1728
1729 -- Stamp po_line_num_display for alll priced clins
1730 get_sol_line_num_disp_for_clin( p_neg_line_num_tbl
1731 , p_req_line_id_tbl
1732 , p_req_option_flag_tbl
1733 , p_draft_id
1734 , p_req_group_line_id_tbl
1735 , p_req_clm_info_flag_tbl
1736 , p_add_to_neg_header_id
1737 , p_start_index
1738 , p_end_index
1739 , p_neg_line_num_disp_tbl
1740 );
1741
1742 d_position := 25;
1743
1744 IF PO_LOG.d_stmt THEN
1745 PO_LOG.stmt(d_mod,d_position,'p_neg_line_num_disp_tbl',p_neg_line_num_disp_tbl);
1746 END IF;
1747
1748 --Renumber all Slins based on the po_line_num and
1749 --po_line_num_disp(for clins) derived above
1750
1751 RenumberSlins( p_req_line_id_tbl
1752 , p_req_group_line_id_tbl
1753 , p_neg_line_num_tbl
1754 , p_neg_line_num_disp_tbl --IN OUT
1755 , p_req_clm_info_flag_tbl
1756 , l_renumber_flag
1757 , p_start_index
1758 , p_end_index
1759 , p_add_to_neg_header_id
1760 , p_draft_id
1761 );
1762
1763 d_position := 28;
1764
1765 IF PO_LOG.d_stmt THEN
1766 PO_LOG.stmt(d_mod,d_position,'p_neg_line_num_disp_tbl',p_neg_line_num_disp_tbl);
1767 END IF;
1768
1769 d_position := 30;
1770
1771 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,p_neg_line_num_tbl); END IF;
1772
1773
1774 EXCEPTION
1775
1776 WHEN OTHERS THEN
1777 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
1778 RAISE;
1779
1780 END group_clm_sol_req_lines;
1781
1782 -------------------------------------------------------------------------------
1783 --Start of Comments
1784 --Name: group_neg__req_lines Overloaded function
1785 --Pre-reqs: None
1786 --Modifies:
1787 --Locks:
1788 -- None
1789 --Function:
1790 -- Defaults the Negotiation line number for requisition lines in the Autocreate
1791 -- Document Builder based on 'Default' grouping
1792 -- method.
1793 --Parameters:
1794 --IN:
1795 --p_req_line_id_tbl
1796 -- Table of requisition line IDs representing the Autocreate Document Builder.
1797 --p_req_line_num_tbl
1798 -- Table of requisition line numbers for the req lines in the Doc Builder.
1799 --p_neg_line_num_tbl
1800 -- Table of Solicitation line numbers currently assigned to the Document Builder
1801 -- requisition lines.
1802 --p_add_to_neg_header_id
1803 -- The ID of the Neg to which the requisition lines are being added.
1804 --p_builder_agreement_id
1805 -- The ID of the Global Agreement for which the Neg will be created.
1806 --p_builder_supplier_id
1807 -- The ID of the Supplier for which the Neg will be created.
1808 --p_builder_site_id
1809 -- The ID of the Site for which the Neg will be created.
1810 --p_builder_org_id
1811 -- The ID of the Operating Unit for which the Neg will be created.
1812 --p_start_index
1813 -- The index of the first requisition line in the input table which
1814 -- should have a Neg line number calculated (default value of 1
1815 -- if not specified).
1816 --p_end_index
1817 -- The index of the last requisition line in the input table which
1818 -- should have a Neg line number calculated (default value of last
1819 -- index in the table if not specified).
1820 --p_grouping_method
1821 -- Grouping method; possible values are 'DEFAULT' or 'REQUISITION'.
1822 --Returns:
1823 -- Table of Neg line numbers corresponding to each of the input requisition
1824 -- lines.
1825 --Notes:
1826 -- N/A
1827 --Testing:
1828 -- N/A
1829 --End of Comments
1830 -------------------------------------------------------------------------------
1831 Procedure group_neg_req_lines
1832 (
1833 p_req_line_id_tbl IN PO_TBL_NUMBER
1834 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
1835 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
1836 , p_req_option_flag_tbl IN PO_TBL_VARCHAR1
1837 , p_req_line_num_tbl IN PO_TBL_VARCHAR100
1838 , p_neg_line_num_tbl IN OUT NOCOPY PO_TBL_NUMBER
1839 , p_neg_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
1840 , p_add_to_neg_header_id IN NUMBER
1841 , p_draft_id IN NUMBER --Autocreate grouping
1842 , p_builder_agreement_id IN NUMBER
1843 , p_builder_supplier_id IN NUMBER
1844 , p_builder_site_id IN NUMBER
1845 , p_builder_org_id IN NUMBER
1846 , p_start_index IN NUMBER
1847 , p_end_index IN NUMBER
1848 , p_grouping_method IN VARCHAR2
1849 )
1850 IS
1851 l_start_index NUMBER;
1852 l_end_index NUMBER;
1853
1854 l_consigned_flag_tbl PO_TBL_VARCHAR1;
1855 l_po_line_num_disp_tbl PO_TBL_VARCHAR100 := p_neg_line_num_disp_tbl;
1856 x_po_line_num_disp_tbl PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
1857 x_po_line_num_tbl PO_TBL_NUMBER := p_neg_line_num_tbl;
1858
1859 d_mod CONSTANT VARCHAR2(100) := D_group_neg_req_lines ;
1860 d_position NUMBER := 0;
1861 l_grouping_method VARCHAR2(50);
1862 l_req_group_line_id_tbl PO_TBL_NUMBER := p_req_group_line_id_tbl;
1863 l_renumber_flag PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
1864 l_line_num NUMBER := 1;
1865 l_max_line_num NUMBER := 0;
1866
1867
1868 BEGIN
1869
1870 l_grouping_method := p_grouping_method;
1871
1872
1873 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
1874
1875 -- Initialize start and end indices to be first and last
1876 -- indices in the input req line table (if they were not
1877 -- specified in the input parameters).
1878 --
1879 l_start_index := nvl(p_start_index, 1);
1880 l_end_index := nvl(p_end_index, p_req_line_id_tbl.COUNT);
1881 l_renumber_flag.extend(p_req_line_id_tbl.Count);
1882
1883 d_position := 5;
1884
1885 IF PO_LOG.d_stmt THEN
1886 PO_LOG.stmt(d_mod,d_position,'l_start_index',l_start_index);
1887 PO_LOG.stmt(d_mod,d_position,'l_end_index',l_end_index);
1888 PO_LOG.stmt(d_mod,d_position,'p_grouping_method',p_grouping_method);
1889 END IF;
1890
1891 d_position := 10;
1892 -- Clear PO line numbers from input table for those indices
1893 -- which will be defaulted.
1894 --
1895 FOR i IN l_start_index..l_end_index LOOP
1896 p_neg_line_num_tbl(i) := NULL;
1897 END LOOP;
1898
1899 -- Call separate grouping procedures depending on grouping method.
1900
1901
1902 d_position := 20;
1903
1904
1905 l_consigned_flag_tbl := get_consigned_flag_tbl ( p_req_line_id_tbl
1906 , p_builder_org_id
1907 , p_builder_supplier_id
1908 , p_builder_site_id
1909 );
1910
1911 -- Derive the internal neg_line_num after grouping
1912 clm_group_sol_by_default ( p_req_line_id_tbl
1913 , p_req_group_line_id_tbl
1914 , p_req_clm_info_flag_tbl
1915 , p_req_option_flag_tbl
1916 , p_neg_line_num_disp_tbl --IN OUT
1917 , l_consigned_flag_tbl
1918 , p_add_to_neg_header_id
1919 , p_draft_id --Autocreate grouping
1920 , p_builder_agreement_id
1921 , l_start_index
1922 , l_end_index
1923 , p_neg_line_num_tbl --IN OUT
1924 );
1925
1926 IF PO_LOG.d_stmt THEN
1927 PO_LOG.stmt(d_mod,d_position,'p_neg_line_num_tbl',p_neg_line_num_tbl);
1928 END IF;
1929
1930 -- In case of default po line number flow,renumber flag is passed as Y
1931 -- for all the lines in the range ( between p_start_index and p_end_index )
1932 FOR i IN l_start_index..l_end_index LOOP
1933 l_renumber_flag(i) := 'Y';
1934 END LOOP;
1935
1936
1937 d_position := 30;
1938
1939 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,p_neg_line_num_tbl); END IF;
1940
1941
1942 EXCEPTION
1943
1944 WHEN OTHERS THEN
1945 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
1946 RAISE;
1947
1948 END group_neg_req_lines;
1949
1950
1951
1952 /* This new procedure is based on the existing procedure group_by_default but has additional parameters. The logic is as follows:
1953
1954 1)The match_add_to_po_lines procedure is called which finds if there is a matching PO line for every Requisition line and
1955 constructs a table of PR line ids that have a match and a corresponding table of the PO line num.
1956 The match logic takes care of the match criteria and also excludes SLINs, info lines and option lines.
1957 No change needs to be done to this procedure as it already has the necessary logic.
1958 This matching is done only when the action selected is 'ADD' and not 'NEW'
1959
1960 2)If the action is 'ADD' then find_matching_po_line_num function is called. No change needs to be done here too.
1961
1962 3)If the action is 'NEW' then clm_find_matching_builder_line_num is called. this is a new function based on the existing find_matching_builder_line_num.
1963 This will find the matching req lines that are selected and are in the interface.
1964 We call this only if the present requisition line that is being checked for match
1965 + is not a SLIN
1966 + is not a info line
1967 + is not a option line
1968 This condition will take care of the following grouping rules mentioned in the FDD,
1969 ? Priced CLIN Line without SLIN Can Be Grouped If the Grouping Criteria Match
1970 ? Single Priced CLIN with Info SLINs Holding the Funds Can Be Grouped If the Grouping Criteria Match
1971 ? Info CLIN with Priced SLINs Are Not Grouped
1972 ? Option Lines Are Not Grouped
1973
1974 We only group the CLINs and add the SLINs to the grouped CLIN and options are alse not grouped. So we will look for matching lines only for CLINs
1975
1976
1977 */
1978
1979 Procedure clm_group_by_default
1980 (
1981 p_req_line_id_tbl IN PO_TBL_NUMBER
1982 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
1983 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
1984 , p_req_option_flag_tbl IN PO_TBL_VARCHAR1
1985 , p_po_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
1986 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
1987 , p_add_to_po_header_id IN NUMBER
1988 , p_draft_id IN NUMBER --Autocreate grouping
1989 , p_builder_agreement_id IN NUMBER
1990 , p_start_index IN NUMBER
1991 , p_end_index IN NUMBER
1992 , p_po_line_num_tbl IN OUT NOCOPY PO_TBL_NUMBER
1993 )
1994 IS
1995
1996 l_max_line_num NUMBER;
1997 l_matching_index NUMBER;
1998 l_po_line_num NUMBER;
1999 x_po_line_num_tbl PO_TBL_NUMBER := p_po_line_num_tbl;
2000
2001 l_add_to_po_req_line_id_tbl PO_TBL_NUMBER;
2002 l_add_to_po_line_num_tbl PO_TBL_NUMBER;
2003 l_max_slin_num_disp_tbl PO_TBL_VARCHAR100;
2004
2005 d_mod CONSTANT VARCHAR2(100) := D_clm_group_by_default;
2006 d_position NUMBER := 0;
2007 l_po_line_num_disp_tbl PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
2008 l_index NUMBER := 1;
2009
2010 BEGIN
2011
2012 --x_po_line_num_disp_tbl.extend(p_po_line_num_tbl.Count);
2013 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
2014
2015 -- Initialize the max PO line number.
2016
2017 l_max_line_num := get_max_po_line_num ( p_po_line_num_tbl
2018 , p_add_to_po_header_id
2019 , p_draft_id
2020 );
2021
2022
2023 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'l_max_line_num',l_max_line_num); END IF;
2024
2025 d_position := 10;
2026
2027 -- Get nested tables of requisition lines and their corresponding
2028 -- matching PO line numbers from the PO being added to.
2029 --
2030
2031 IF p_add_to_po_header_id IS NOT NULL
2032 then
2033 clm_match_add_to_po_lines (p_req_line_id_tbl
2034 , p_consigned_flag_tbl
2035 , p_add_to_po_header_id
2036 , p_draft_id --Autocreate grouping
2037 , p_builder_agreement_id
2038 , p_start_index
2039 , p_end_index
2040 , l_add_to_po_req_line_id_tbl -- OUT
2041 , l_add_to_po_line_num_tbl -- OUT
2042 , l_po_line_num_disp_tbl
2043 );
2044
2045 END IF;
2046
2047
2048 d_position := 20;
2049
2050
2051
2052 -- Loop through input nested table of req lines.
2053 --
2054 FOR i IN p_start_index..p_end_index LOOP
2055
2056
2057 l_po_line_num := NULL;
2058
2059 d_position := 30;
2060
2061 -- Find a matching line on the PO being added to (if one exists).
2062 --
2063
2064 IF ( p_add_to_po_header_id IS NOT NULL )
2065 THEN
2066 IF (p_req_group_line_id_tbl(i) IS NULL /*CLIN*/)
2067 AND (NVL(p_req_clm_info_flag_tbl(i),'N') = 'N' /*not an info line*/)
2068 AND (nvl(p_req_option_flag_tbl(i),'B') <> 'O' /*not an option line*/)
2069 --Bug 13818326 Added NVL condition for option flag
2070 THEN
2071
2072 l_po_line_num := find_matching_po_line_num
2073 ( p_req_line_id => p_req_line_id_tbl(i)
2074 , p_comparison_tbl => l_add_to_po_req_line_id_tbl
2075 , p_po_line_num_tbl => l_add_to_po_line_num_tbl
2076 );
2077 IF l_po_line_num IS NOT NULL
2078 THEN
2079 p_po_line_num_disp_tbl(i) := l_po_line_num_disp_tbl(l_index);
2080 l_index := l_index + 1;
2081 END IF;
2082
2083
2084 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'Add To PO Line Num',l_po_line_num); END IF;
2085 END IF;
2086 END IF;
2087
2088 d_position := 40;
2089
2090 -- If no line was found on the PO being added to, search for any
2091 -- matching lines before the current line in the Doc Builder.
2092 --
2093 IF ( l_po_line_num IS NULL )
2094 THEN
2095 /* Autocreate grouping start */
2096 IF (p_req_group_line_id_tbl(i) IS NULL /*CLIN*/)
2097 AND (NVL(p_req_clm_info_flag_tbl(i),'N') = 'N' /*not an info line*/)
2098 AND (nvl(p_req_option_flag_tbl(i),'B') <> 'O' /*not an option line*/)
2099 --Bug 13818326 Added NVL condition for option flag
2100 THEN
2101 l_po_line_num := clm_find_match_builder_linenum
2102 ( p_current_index => i
2103 , p_req_line_id_tbl => p_req_line_id_tbl
2104 , p_req_group_line_id_tbl => p_req_group_line_id_tbl
2105 , p_req_clm_info_flag_tbl => p_req_clm_info_flag_tbl
2106 , p_req_option_flag_tbl => p_req_option_flag_tbl
2107 , p_po_line_num_tbl => x_po_line_num_tbl
2108 , p_builder_agreement_id => p_builder_agreement_id
2109 );
2110 /* Autocreate grouping end */
2111
2112 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'Builder Line Num',l_po_line_num); END IF;
2113 END IF;
2114 END IF;
2115
2116 d_position := 50;
2117
2118 -- If no matching lines found in the PO or the Doc Builder,
2119 -- set the PO line num to the next line number and then set
2120 -- the new max line number.
2121 --
2122 IF ( l_po_line_num IS NULL )
2123 THEN
2124 l_max_line_num := l_max_line_num + 1;
2125 l_po_line_num := l_max_line_num;
2126
2127 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'Max Line Num',l_po_line_num); END IF;
2128 END IF;
2129
2130
2131 d_position := 60;
2132 x_po_line_num_tbl(i) := l_po_line_num;
2133
2134 END LOOP;
2135
2136 d_position := 70;
2137
2138
2139 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,x_po_line_num_tbl); END IF;
2140 p_po_line_num_tbl := x_po_line_num_tbl;
2141
2142 EXCEPTION
2143
2144 WHEN OTHERS THEN
2145 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
2146 RAISE;
2147
2148 END clm_group_by_default;
2149
2150 /* This new procedure is based on the existing procedure group_by_default but has additional parameters. The logic is as follows:
2151
2152 1)The match_add_to_po_lines procedure is called which finds if there is a matching PO line for every Requisition line and
2153 constructs a table of PR line ids that have a match and a corresponding table of the PO line num.
2154 The match logic takes care of the match criteria and also excludes SLINs, info lines and option lines.
2155 No change needs to be done to this procedure as it already has the necessary logic.
2156 This matching is done only when the action selected is 'ADD' and not 'NEW'
2157
2158 2)If the action is 'ADD' then find_matching_po_line_num function is called. No change needs to be done here too.
2159
2160 3)If the action is 'NEW' then clm_find_matching_builder_line_num is called. this is a new function based on the existing find_matching_builder_line_num.
2161 This will find the matching req lines that are selected and are in the interface.
2162 We call this only if the present requisition line that is being checked for match
2163 + is not a SLIN
2164 + is not a info line
2165 + is not a option line
2166 This condition will take care of the following grouping rules mentioned in the FDD,
2167 ? Priced CLIN Line without SLIN Can Be Grouped If the Grouping Criteria Match
2168 ? Single Priced CLIN with Info SLINs Holding the Funds Can Be Grouped If the Grouping Criteria Match
2169 ? Info CLIN with Priced SLINs Are Not Grouped
2170 ? Option Lines Are Not Grouped
2171
2172 We only group the CLINs and add the SLINs to the grouped CLIN and options are alse not grouped. So we will look for matching lines only for CLINs
2173
2174
2175 */
2176
2177 Procedure clm_group_sol_by_default
2178 (
2179 p_req_line_id_tbl IN PO_TBL_NUMBER
2180 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
2181 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
2182 , p_req_option_flag_tbl IN PO_TBL_VARCHAR1
2183 , p_neg_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
2184 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
2185 , p_add_to_neg_header_id IN NUMBER
2186 , p_draft_id IN NUMBER --Autocreate grouping
2187 , p_builder_agreement_id IN NUMBER
2188 , p_start_index IN NUMBER
2189 , p_end_index IN NUMBER
2190 , p_neg_line_num_tbl IN OUT NOCOPY PO_TBL_NUMBER
2191 )
2192 IS
2193
2194 l_max_line_num NUMBER;
2195 l_matching_index NUMBER;
2196 l_neg_line_num NUMBER;
2197 x_neg_line_num_tbl PO_TBL_NUMBER := p_neg_line_num_tbl;
2198
2199 l_add_to_neg_req_line_id_tbl PO_TBL_NUMBER;
2200 l_add_to_neg_line_num_tbl PO_TBL_NUMBER;
2201 l_max_slin_num_disp_tbl PO_TBL_VARCHAR100;
2202
2203 d_mod CONSTANT VARCHAR2(100) := D_clm_group_by_default;
2204 d_position NUMBER := 0;
2205 l_neg_line_num_disp_tbl PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
2206 l_index NUMBER := 1;
2207
2208 BEGIN
2209
2210 --x_po_line_num_disp_tbl.extend(p_po_line_num_tbl.Count);
2211 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
2212
2213 -- Initialize the max PO line number.
2214
2215 SELECT Max(LINE_NUMBER) INTO l_max_line_num FROM pon_auction_item_prices_all WHERE AUCTION_HEADER_ID = p_draft_id;
2216
2217
2218
2219 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'l_max_line_num',l_max_line_num); END IF;
2220
2221 d_position := 10;
2222
2223
2224 -- Loop through input nested table of req lines.
2225 --
2226 FOR i IN p_start_index..p_end_index LOOP
2227
2228
2229 l_neg_line_num := NULL;
2230
2231 d_position := 30;
2232
2233 /* Autocreate grouping start */
2234 IF (p_req_group_line_id_tbl(i) IS NULL /*CLIN*/)
2235 AND (NVL(p_req_clm_info_flag_tbl(i),'N') = 'N' /*not an info line*/)
2236 AND (nvl(p_req_option_flag_tbl(i),'B') <> 'O' /*not an option line*/)
2237 --Bug 13818326 Added NVL condition for option flag
2238 THEN
2239 l_neg_line_num := clm_find_match_builder_linenum
2240 ( p_current_index => i
2241 , p_req_line_id_tbl => p_req_line_id_tbl
2242 , p_req_group_line_id_tbl => p_req_group_line_id_tbl
2243 , p_req_clm_info_flag_tbl => p_req_clm_info_flag_tbl
2244 , p_req_option_flag_tbl => p_req_option_flag_tbl
2245 , p_po_line_num_tbl => x_neg_line_num_tbl
2246 , p_builder_agreement_id => p_builder_agreement_id
2247 );
2248 /* Autocreate grouping end */
2249
2250 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'Builder Line Num',l_neg_line_num); END IF;
2251 END IF;
2252
2253
2254 d_position := 50;
2255
2256 -- If no matching lines found in the PO or the Doc Builder,
2257 -- set the PO line num to the next line number and then set
2258 -- the new max line number.
2259 --
2260 IF ( l_neg_line_num IS NULL )
2261 THEN
2262 l_max_line_num := l_max_line_num + 1;
2263 l_neg_line_num := l_max_line_num;
2264
2265 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'Max Line Num',l_neg_line_num); END IF;
2266 END IF;
2267
2268
2269 d_position := 60;
2270 x_neg_line_num_tbl(i) := l_neg_line_num;
2271
2272 END LOOP;
2273
2274 d_position := 70;
2275
2276
2277 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,x_neg_line_num_tbl); END IF;
2278 p_neg_line_num_tbl := x_neg_line_num_tbl;
2279
2280 EXCEPTION
2281
2282 WHEN OTHERS THEN
2283 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
2284 RAISE;
2285
2286 END clm_group_sol_by_default;
2287
2288
2289 /* This is new function that is based out of the existing find_matching_builder_line_num.
2290 This calls the existing function req_lines_match which determines if two req lines can be grouped. This function will be called only if the line being compared also is not a SLIN, not an info line and not an option line.
2291 The req_lines_match will call subsequent apis to compare the grouping criteria for both the lines and determine if they can be matched. since the grouping criteria remain unchanged for CLM and commercial docs, these apis can remain unchanged.
2292 */
2293
2294 FUNCTION clm_find_match_builder_linenum
2295 (
2296 p_current_index IN NUMBER
2297 , p_req_line_id_tbl IN PO_TBL_NUMBER
2298 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
2299 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
2300 , p_req_option_flag_tbl IN PO_TBL_VARCHAR1
2301 , p_po_line_num_tbl IN PO_TBL_NUMBER
2302 , p_builder_agreement_id IN NUMBER
2303 )
2304 RETURN NUMBER
2305 IS
2306 d_mod CONSTANT VARCHAR2(100) := D_clm_find_match_builder_line;
2307 d_position NUMBER := 0;
2308
2309 BEGIN
2310
2311 -- Loop from beginning of the Doc Builder up to (but not including)
2312 -- the current line to look for a match.
2313 --
2314 FOR i IN 1..(p_current_index - 1)
2315 LOOP
2316 /* Autocreate grouping start*/
2317 IF (p_req_group_line_id_tbl(i) IS NULL /*CLIN*/)
2318 AND (NVL(p_req_clm_info_flag_tbl(i),'N') = 'N' /*not an info line*/)
2319 AND (nvl(p_req_option_flag_tbl(i),'B') <> 'O' /*not an option line*/)
2320 --Bug 13818326 Added NVL condition for option flag
2321 /* Autocreate grouping end*/
2322 THEN
2323 IF ( ( req_lines_match ( p_builder_agreement_id
2324 , p_req_line_id_tbl(p_current_index)
2325 , p_req_line_id_tbl(i) )
2326 )
2327 AND ( p_po_line_num_tbl(i) IS NOT NULL ) )
2328 THEN
2329 return (p_po_line_num_tbl(i)); -- return PO line num
2330 END IF;
2331 END IF;
2332
2333 END LOOP;
2334
2335 return (NULL); -- if loop completes, no matching line was found
2336
2337 EXCEPTION
2338
2339 WHEN OTHERS THEN
2340 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
2341 RAISE;
2342
2343 END clm_find_match_builder_linenum;
2344
2345 /*=========================================================================*/
2346 /*========================== BODY (PRIVATE) ===============================*/
2347 /*=========================================================================*/
2348
2349 -------------------------------------------------------------------------------
2350 --Start of Comments
2351 --Name: lines_delivery_info_match
2352 --Pre-reqs:
2353 -- None
2354 --Modifies:
2355 -- None
2356 --Locks:
2357 -- None
2358 --Function:
2359 -- Gets the delivery info for a requisition line and a requisition/PO line and
2360 -- checks whether they match at the delivery info level. Returns an error
2361 -- message indicating the result.
2362 --Parameters:
2363 --IN:
2364 --p_supplier_id
2365 -- The ID of the supplier specified in the document builder.
2366 --p_site_id
2367 -- The ID of the site specified in the document builder.
2368 --p_req_line_id
2369 -- The ID of the first requisition line to compare with.
2370 --p_req_line_id_to_compare
2371 -- If the second line to compare with is a requisition line, this variable
2372 -- holds the ID of that requisition line. The second line is either a
2373 -- requisition or PO line, so this variable may or may not be null.
2374 --p_po_line_id_to_compare
2375 -- If the second line to compare with is a PO line, this variable holds the ID
2376 -- of that PO line. The second line is either a requisition or PO line, so
2377 -- this variable may or may not be null.
2378 --OUT:
2379 --x_message_code
2380 -- The error message code corresponding to whether the two lines match at the
2381 -- delivery info level or not.
2382 --x_token_name
2383 -- The error message token name corresponding to whether the two lines match
2384 -- at the delivery info level or not.
2385 --x_token_value
2386 -- The error message token value corresponding to whether the two lines match
2387 -- at the delivery info level or not.
2388 --Notes:
2389 -- Between the input parameters p_req_line_id_to_compare and
2390 -- p_po_line_id_to_compare, one of them has to be null and one of them has to
2391 -- have a value.
2392 --Testing:
2393 -- None
2394 --End of Comments
2395 -------------------------------------------------------------------------------
2396 PROCEDURE lines_delivery_info_match(
2397 p_supplier_id IN NUMBER,
2398 p_site_id IN NUMBER,
2399 p_req_line_id IN NUMBER,
2400 p_req_line_id_to_compare IN NUMBER,
2401 p_po_line_id_to_compare IN NUMBER,
2402 p_draft_id IN NUMBER, --Autocreate grouping
2403 x_message_code OUT NOCOPY VARCHAR2,
2404 x_token_name OUT NOCOPY VARCHAR2,
2405 x_token_value OUT NOCOPY VARCHAR2
2406 )
2407 IS
2408 d_mod CONSTANT VARCHAR2(100) := D_lines_delivery_info_match;
2409 d_position NUMBER := 0;
2410
2411 l_token_value VARCHAR2(2000);
2412
2413 -- Shipment-level attributes
2414 l_need_by_grouping_profile VARCHAR2(1);
2415 l_ship_to_grouping_profile VARCHAR2(1);
2416 delivery_one PO_DELIVERY_INFO_CSR%ROWTYPE;
2417 delivery_two PO_DELIVERY_INFO_CSR%ROWTYPE;
2418
2419 BEGIN
2420 IF (PO_LOG.d_proc) THEN
2421 PO_LOG.proc_begin(d_mod);
2422 PO_LOG.proc_begin(d_mod,'p_supplier_id',p_supplier_id);
2423 PO_LOG.proc_begin(d_mod,'p_site_id',p_site_id);
2424 PO_LOG.proc_begin(d_mod,'p_req_line_id',p_req_line_id);
2425 PO_LOG.proc_begin(d_mod,'p_req_line_id_to_compare',p_req_line_id_to_compare);
2426 PO_LOG.proc_begin(d_mod,'p_po_line_id_to_compare',p_po_line_id_to_compare);
2427 END IF;
2428
2429 -- Get shipment-level profiles
2430 l_need_by_grouping_profile := fnd_profile.value('PO_NEED_BY_GROUPING');
2431 l_ship_to_grouping_profile := fnd_profile.value('PO_SHIPTO_GROUPING');
2432
2433 -- Retrieve all shipment-level attributes for p_req_line_id
2434 delivery_one := get_req_line_delivery_info(
2435 p_req_line_id,
2436 p_supplier_id,
2437 p_site_id
2438 );
2439
2440 IF (p_req_line_id_to_compare IS NOT NULL) -- if second line is a req line
2441 THEN
2442 -- Retrieve all shipment-level attributes for p_req_line_id_to_compare
2443 delivery_two := get_req_line_delivery_info(
2444 p_req_line_id_to_compare,
2445 p_supplier_id,
2446 p_site_id
2447 );
2448
2449 -- Check shipment-level attributes for mismatch
2450 check_delivery_info(
2451 l_need_by_grouping_profile,
2452 l_ship_to_grouping_profile,
2453 delivery_one,
2454 delivery_two,
2455 x_message_code,
2456 x_token_name,
2457 x_token_value
2458 );
2459 ELSE -- if second line is a PO line
2460 -- Retrieve all shipment-level attributes for p_po_line_id_to_compare
2461 OPEN po_delivery_info_csr(p_po_line_id_to_compare, p_draft_id); --Autocreate grouping, adding draft_id parameter
2462 LOOP
2463 FETCH po_delivery_info_csr INTO
2464 delivery_two;
2465 EXIT WHEN po_delivery_info_csr%NOTFOUND;
2466
2467 -- Check shipment-level attributes for mismatch
2468 check_delivery_info(
2469 l_need_by_grouping_profile,
2470 l_ship_to_grouping_profile,
2471 delivery_one,
2472 delivery_two,
2473 x_message_code,
2474 x_token_name,
2475 x_token_value
2476 );
2477
2478 -- Exit the for loop if a matching shipment is found.
2479 IF (x_message_code IS NULL)
2480 THEN
2481 EXIT;
2482 END IF;
2483 END LOOP;
2484 CLOSE po_delivery_info_csr;
2485 END IF; -- if second line is a PO line
2486
2487 IF (PO_LOG.d_proc) THEN
2488 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
2489 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
2490 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
2491 END IF;
2492
2493 EXCEPTION
2494 WHEN OTHERS THEN
2495 -- Close cursor if open
2496 IF (po_delivery_info_csr%ISOPEN)
2497 THEN
2498 CLOSE po_delivery_info_csr;
2499 END IF;
2500
2501 IF (PO_LOG.d_exc) THEN
2502 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_delivery_info_match');
2503 END IF;
2504
2505 RAISE;
2506 END lines_delivery_info_match;
2507
2508 -------------------------------------------------------------------------------
2509 --Start of Comments
2510 --Name: check_delivery_info
2511 --Pre-reqs:
2512 -- None
2513 --Modifies:
2514 -- None
2515 --Locks:
2516 -- None
2517 --Function:
2518 -- Checks whether a requisition line and a requisition/PO line match at the
2519 -- delivery info level and returns an error message indicating the result.
2520 --Parameters:
2521 --IN:
2522 --p_need_by_grouping_profile
2523 -- The profile indicating whether need-by-date should be considered when
2524 -- checking if two lines match.
2525 --p_ship_to_grouping_profile
2526 -- The profile indicating whether shipping information should be considered
2527 -- when checking if two lines match.
2528 --p_delivery_one
2529 -- The delivery information for the first line.
2530 --p_delivery_two
2531 -- The delivery information for the second line.
2532 --OUT:
2533 --x_message_code
2534 -- The error message code corresponding to whether the two lines match at the
2535 -- delivery info level or not.
2536 --x_token_name
2537 -- The error message token name corresponding to whether the two lines match
2538 -- at the delivery info level or not.
2539 --x_token_value
2540 -- The error message token value corresponding to whether the two lines match
2541 -- at the delivery info level or not.
2542 --Notes:
2543 -- None
2544 --Testing:
2545 -- None
2546 --End of Comments
2547 -------------------------------------------------------------------------------
2548 PROCEDURE check_delivery_info(
2549 p_need_by_grouping_profile IN VARCHAR2,
2550 p_ship_to_grouping_profile IN VARCHAR2,
2551 p_delivery_one IN PO_DELIVERY_INFO_CSR%ROWTYPE,
2552 p_delivery_two IN PO_DELIVERY_INFO_CSR%ROWTYPE,
2553 x_message_code OUT NOCOPY VARCHAR2,
2554 x_token_name OUT NOCOPY VARCHAR2,
2555 x_token_value OUT NOCOPY VARCHAR2
2556 )
2557 IS
2558 d_mod CONSTANT VARCHAR2(100) := D_check_delivery_info;
2559 d_position NUMBER := 0;
2560 BEGIN
2561 IF (PO_LOG.d_proc) THEN
2562 PO_LOG.proc_begin(d_mod);
2563 PO_LOG.proc_begin(d_mod,'p_need_by_grouping_profile',p_need_by_grouping_profile);
2564 PO_LOG.proc_begin(d_mod,'p_ship_to_grouping_profile',p_ship_to_grouping_profile);
2565 END IF;
2566
2567 -- Initialize message code, token name, and token value to NULL
2568 x_message_code := NULL;
2569 x_token_name := NULL;
2570 x_token_value := NULL;
2571
2572 -- Check need-by date attributes
2573 IF ((NVL(p_need_by_grouping_profile, 'Y') = 'Y')
2574 AND NOT PO_CORE_S.is_equal_minutes(
2575 p_delivery_one.need_by_date,
2576 p_delivery_two.need_by_date))
2577 THEN
2578 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
2579 x_token_name := 'REASON_FOR_DIFF';
2580 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_NEED_BY');
2581
2582 IF (PO_LOG.d_proc) THEN
2583 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
2584 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
2585 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
2586 END IF;
2587
2588 RETURN;
2589 END IF;
2590
2591 -- Check ship-to attributes
2592 IF (NVL(p_ship_to_grouping_profile, 'Y') = 'Y')
2593 THEN
2594 -- Check ship-to organization ID attributes
2595 IF (NOT PO_CORE_S.is_equal(
2596 p_delivery_one.ship_to_organization_id,
2597 p_delivery_two.ship_to_organization_id))
2598 THEN
2599 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
2600 x_token_name := 'REASON_FOR_DIFF';
2601 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_DEL_ORG');
2602
2603 IF (PO_LOG.d_proc) THEN
2604 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
2605 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
2606 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
2607 END IF;
2608
2609 RETURN;
2610 END IF;
2611
2612 -- Check ship-to location ID attributes
2613 IF (NOT PO_CORE_S.is_equal(
2614 p_delivery_one.ship_to_location_id,
2615 p_delivery_two.ship_to_location_id))
2616 THEN
2617 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
2618 x_token_name := 'REASON_FOR_DIFF';
2619 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_DEL_LOC');
2620
2621 IF (PO_LOG.d_proc) THEN
2622 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
2623 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
2624 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
2625 END IF;
2626
2627 RETURN;
2628 END IF;
2629 END IF;
2630
2631 -- Check consigned flag attributes which are set to 'N' if NULL
2632 IF (NOT PO_CORE_S.is_equal(
2633 NVL(p_delivery_one.consigned_flag, 'N'),
2634 NVL(p_delivery_two.consigned_flag, 'N')))
2635 THEN
2636 x_message_code := 'PO_ALL_CANT_COMB_CONSIGNED';
2637 x_token_name := NULL;
2638 x_token_value := NULL;
2639
2640 IF (PO_LOG.d_proc) THEN
2641 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
2642 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
2643 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
2644 END IF;
2645
2646 RETURN;
2647 END IF;
2648
2649 IF (PO_LOG.d_proc) THEN
2650 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
2651 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
2652 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
2653 END IF;
2654
2655 EXCEPTION
2656 WHEN OTHERS THEN
2657 IF (PO_LOG.d_exc) THEN
2658 PO_LOG.exc(d_mod, d_position, 'An error occured in check_delivery_info');
2659 END IF;
2660
2661 RAISE;
2662 END check_delivery_info;
2663
2664 -------------------------------------------------------------------------------
2665 --Start of Comments
2666 --Name: get_req_line_delivery_info
2667 --Pre-reqs:
2668 -- None
2669 --Modifies:
2670 -- None
2671 --Locks:
2672 -- None
2673 --Function:
2674 -- Gets the delivery info for a requisition line.
2675 --Parameters:
2676 --IN:
2677 --p_req_line_id
2678 -- The ID of the requisition line to get the delivery info of.
2679 --p_supplier_id
2680 -- The ID of the supplier specified in the document builder.
2681 --p_site_id
2682 -- The ID of the site specified in the document builder.
2683 --RETURNS:
2684 -- The delivery info for the req line.
2685 --Notes:
2686 -- None
2687 --Testing:
2688 -- None
2689 --End of Comments
2690 -------------------------------------------------------------------------------
2691 FUNCTION get_req_line_delivery_info(
2692 p_req_line_id IN NUMBER,
2693 p_supplier_id IN NUMBER,
2694 p_site_id IN NUMBER
2695 ) RETURN PO_DELIVERY_INFO_CSR%ROWTYPE
2696 IS
2697 d_mod CONSTANT VARCHAR2(100) := D_get_req_line_delivery_info;
2698 d_position NUMBER := 0;
2699
2700 l_req_line_delivery_info PO_DELIVERY_INFO_CSR%ROWTYPE;
2701 l_item_id PO_REQUISITION_LINES_ALL.item_id%TYPE;
2702 BEGIN
2703 IF (PO_LOG.d_proc) THEN
2704 PO_LOG.proc_begin(d_mod);
2705 PO_LOG.proc_begin(d_mod,'p_req_line_id',p_req_line_id);
2706 PO_LOG.proc_begin(d_mod,'p_supplier_id',p_supplier_id);
2707 PO_LOG.proc_begin(d_mod,'p_site_id',p_site_id);
2708 END IF;
2709
2710 SELECT PRL.item_id,
2711 PRL.need_by_date,
2712 PRL.destination_organization_id,
2713 PRL.deliver_to_location_id
2714 INTO l_item_id,
2715 l_req_line_delivery_info.need_by_date,
2716 l_req_line_delivery_info.ship_to_organization_id,
2717 l_req_line_delivery_info.ship_to_location_id
2718 FROM po_requisition_lines_all PRL
2719 WHERE PRL.requisition_line_id = p_req_line_id;
2720
2721 l_req_line_delivery_info.consigned_flag :=
2722 PO_THIRD_PARTY_STOCK_GRP.get_consigned_flag(
2723 NULL, --bug 5976612
2724 l_item_id, -- p_item_id
2725 p_supplier_id, -- p_supplier_id
2726 p_site_id, -- p_site_id
2727 l_req_line_delivery_info.ship_to_organization_id -- p_inv_org_id --bug 5976612
2728 );
2729
2730 /* Bug 5976612
2731 Added the 'NULL' parameter in the beginning in the place of org id.
2732 Moved the l_req_line_delivery_info.ship_to_organization_id parameter to end. This is inventory org id.
2733 These changes are driven by the changes done to the function PO_THIRD_PARTY_STOCK_GRP.get_consigned_flag. */
2734
2735 IF (PO_LOG.d_proc) THEN
2736 PO_LOG.proc_end(d_mod);
2737 END IF;
2738
2739 RETURN l_req_line_delivery_info;
2740
2741 EXCEPTION
2742 WHEN OTHERS THEN
2743 IF (PO_LOG.d_exc) THEN
2744 PO_LOG.exc(d_mod, d_position, 'An error occured in get_req_line_delivery_info');
2745 END IF;
2746
2747 RAISE;
2748 END get_req_line_delivery_info;
2749
2750 -------------------------------------------------------------------------------
2751 --Start of Comments
2752 --Name: lines_info_match
2753 --Pre-reqs:
2754 -- None
2755 --Modifies:
2756 -- None
2757 --Locks:
2758 -- None
2759 --Function:
2760 -- Checks whether a requisition line and a requisition/PO line match at the
2761 -- line info level and returns an error message indicating the result.
2762 --Parameters:
2763 --IN:
2764 --p_agreement_id
2765 -- The ID of the agreement specified in the document builder.
2766 --p_req_line_id
2767 -- The ID of the first requisition line to compare with.
2768 --p_req_line_id_to_compare
2769 -- If the second line to compare with is a requisition line, this variable
2770 -- holds the ID of that requisition line. The second line is either a
2771 -- requisition or PO line, so this variable may or may not be null.
2772 --p_po_line_id_to_compare
2773 -- If the second line to compare with is a PO line, this variable holds the ID
2774 -- of that PO line. The second line is either a requisition or PO line, so
2775 -- this variable may or may not be null.
2776 --OUT:
2777 --x_message_code
2778 -- The error message code corresponding to whether the two lines match at the
2779 -- line info level or not.
2780 --x_token_name
2781 -- The error message token name corresponding to whether the two lines match
2782 -- at the line info level or not.
2783 --x_token_value
2784 -- The error message token value corresponding to whether the two lines match
2785 -- at the line info level or not.
2786 --Notes:
2787 -- Between the input parameters p_req_line_id_to_compare and
2788 -- p_po_line_id_to_compare, one of them has to be null and one of them has to
2789 -- have a value.
2790 --Testing:
2791 -- None
2792 --End of Comments
2793 -------------------------------------------------------------------------------
2794 PROCEDURE lines_info_match(
2795 p_agreement_id IN NUMBER,
2796 p_req_line_id IN NUMBER,
2797 p_req_line_id_to_compare IN NUMBER,
2798 p_po_line_id_to_compare IN NUMBER,
2799 p_draft_id IN NUMBER, --Autocreate grouping
2800 p_relax_item_desc_check IN VARCHAR2,
2801 x_message_code OUT NOCOPY VARCHAR2,
2802 x_token_name OUT NOCOPY VARCHAR2,
2803 x_token_value OUT NOCOPY VARCHAR2
2804 )
2805 IS
2806 d_mod CONSTANT VARCHAR2(100) := D_lines_info_match;
2807 d_position NUMBER := 0;
2808
2809 l_token_value VARCHAR2(2000);
2810
2811 l_line_one LINE_INFO;
2812 l_line_two LINE_INFO;
2813 BEGIN
2814 IF (PO_LOG.d_proc) THEN
2815 PO_LOG.proc_begin(d_mod);
2816 PO_LOG.proc_begin(d_mod,'p_agreement_id',p_agreement_id);
2817 PO_LOG.proc_begin(d_mod,'p_req_line_id',p_req_line_id);
2818 PO_LOG.proc_begin(d_mod,'p_req_line_id_to_compare',p_req_line_id_to_compare);
2819 PO_LOG.proc_begin(d_mod,'p_po_line_id_to_compare',p_po_line_id_to_compare);
2820 END IF;
2821
2822 -- Retrieve all lines matching attributes for p_req_line_id
2823 l_line_one := get_req_line_info(p_req_line_id);
2824
2825 -- Retrieve lines matching attributes for second line
2826 IF (p_req_line_id_to_compare IS NOT NULL) -- if second line is a req line
2827 THEN
2828 -- Retrieve all lines matching attributes for p_req_line_id_to_compare
2829 l_line_two := get_req_line_info(p_req_line_id_to_compare);
2830 ELSE -- if second line is a PO line
2831 -- Retrieve all line-level attributes for p_po_line_id_to_compare
2832 l_line_two := get_po_line_info(p_po_line_id_to_compare, p_draft_id ); --Autocreate grouping
2833 END IF;
2834
2835 -- Check line-level attributes
2836 check_line_info(
2837 p_agreement_id,
2838 l_line_one,
2839 l_line_two,
2840 p_relax_item_desc_check, --Autocreate grouping
2841 x_message_code,
2842 x_token_name,
2843 x_token_value
2844 );
2845
2846 IF (PO_LOG.d_proc) THEN
2847 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
2848 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
2849 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
2850 END IF;
2851
2852 EXCEPTION
2853 WHEN OTHERS THEN
2854 IF (PO_LOG.d_exc) THEN
2855 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_info_match');
2856 END IF;
2857
2858 RAISE;
2859 END lines_info_match;
2860
2861 -------------------------------------------------------------------------------
2862 --Start of Comments
2863 --Name: get_req_line_info
2864 --Pre-reqs:
2865 -- None
2866 --Modifies:
2867 -- None
2868 --Locks:
2869 -- None
2870 --Function:
2871 -- Gets the line info for a requisition line.
2872 --Parameters:
2873 --IN:
2874 --p_req_line_id
2875 -- The ID of the requisition line to get the line info of.
2876 --RETURNS:
2877 -- The line info for the req line.
2878 --Notes:
2879 -- None
2880 --Testing:
2881 -- None
2882 --End of Comments
2883 -------------------------------------------------------------------------------
2884 FUNCTION get_req_line_info(p_req_line_id IN NUMBER) RETURN LINE_INFO
2885 IS
2886 d_mod CONSTANT VARCHAR2(100) := D_get_req_line_info;
2887 d_position NUMBER := 0;
2888
2889 l_req_line_info LINE_INFO;
2890 BEGIN
2891 IF (PO_LOG.d_proc) THEN
2892 PO_LOG.proc_begin(d_mod);
2893 PO_LOG.proc_begin(d_mod,'p_req_line_id',p_req_line_id);
2894 END IF;
2895
2896 SELECT PRL.item_id,
2897 PRL.item_description,
2898 PRL.item_revision,
2899 PRL.order_type_lookup_code,
2900 PRL.purchase_basis,
2901 PRL.matching_basis,
2902 PRL.preferred_grade,
2903 PRL.unit_meas_lookup_code,
2904 PRL.transaction_reason_code,
2905 DECODE(
2906 PRL.document_type_code,
2907 'CONTRACT',
2908 PRL.blanket_po_header_id,
2909 NULL
2910 ), -- contract ID
2911 DECODE(
2912 PRL.document_type_code,
2913 'CONTRACT',
2914 NULL,
2915 PRL.blanket_po_header_id
2916 ), -- source document ID
2917 DECODE(
2918 PRL.document_type_code,
2919 'CONTRACT',
2920 NULL,
2921 SRC_DOC_LINE.po_line_id
2922 ), -- source document line ID
2923 NULL, -- cancel flag N/A for req line
2924 NULL, -- closed code N/A for req line
2925 PRL.supplier_ref_number
2926 , PRL.GROUP_LINE_ID
2927 , PRL.CLM_INFO_FLAG
2928 , PRL.CLM_BASE_LINE_NUM
2929 , PRL.category_id --bugfix#16097884
2930 INTO l_req_line_info
2931 FROM po_requisition_lines_all PRL,
2932 po_lines_all SRC_DOC_LINE
2933 WHERE PRL.requisition_line_id = p_req_line_id
2934 AND SRC_DOC_LINE.po_header_id(+) = PRL.blanket_po_header_id
2935 AND SRC_DOC_LINE.line_num(+) = PRL.blanket_po_line_num;
2936
2937 IF (PO_LOG.d_proc) THEN
2938 PO_LOG.proc_end(d_mod);
2939 END IF;
2940
2941 RETURN l_req_line_info;
2942
2943 EXCEPTION
2944 WHEN OTHERS THEN
2945 IF (PO_LOG.d_exc) THEN
2946 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_info_match');
2947 END IF;
2948
2949 RAISE;
2950 END get_req_line_info;
2951
2952 -------------------------------------------------------------------------------
2953 --Start of Comments
2954 --Name: get_po_line_info
2955 --Pre-reqs:
2956 -- None
2957 --Modifies:
2958 -- None
2959 --Locks:
2960 -- None
2961 --Function:
2962 -- Gets the line info for a PO line.
2963 --Parameters:
2964 --IN:
2965 --p_po_line_id
2966 -- The ID of the PO line to get the line info of.
2967 --RETURNS:
2968 -- The line info for the PO line.
2969 --Notes:
2970 -- None
2971 --Testing:
2972 -- None
2973 --End of Comments
2974 -------------------------------------------------------------------------------
2975 FUNCTION get_po_line_info(p_po_line_id IN NUMBER, p_draft_id IN NUMBER) RETURN LINE_INFO --Autocreate grouping
2976 IS
2977 d_mod CONSTANT VARCHAR2(100) := D_get_po_line_info;
2978 d_position NUMBER := 0;
2979
2980 l_po_line_info LINE_INFO;
2981 BEGIN
2982 IF (PO_LOG.d_proc) THEN
2983 PO_LOG.proc_begin(d_mod);
2984 PO_LOG.proc_begin(d_mod,'p_po_line_id',p_po_line_id);
2985 END IF;
2986
2987 SELECT item_id,
2988 item_description,
2989 item_revision,
2990 order_type_lookup_code,
2991 purchase_basis,
2992 matching_basis,
2993 preferred_grade,
2994 unit_meas_lookup_code,
2995 transaction_reason_code,
2996 contract_id,
2997 from_header_id, -- source document ID
2998 from_line_id, -- source document line ID
2999 cancel_flag,
3000 closed_code,
3001 supplier_ref_number
3002 ,GROUP_LINE_ID
3003 ,CLM_INFO_FLAG
3004 ,CLM_BASE_LINE_NUM
3005 ,category_id --bugfix#16097884
3006 INTO l_po_line_info
3007 FROM po_lines_merge_v /* changing to po_lines_merge_v and passing in the draft_id, for the 'add to mods' flow*/
3008 WHERE po_line_id = p_po_line_id
3009 AND draft_id = p_draft_id;
3010
3011 IF (PO_LOG.d_proc) THEN
3012 PO_LOG.proc_end(d_mod);
3013 END IF;
3014
3015 RETURN l_po_line_info;
3016
3017 EXCEPTION
3018 WHEN OTHERS THEN
3019 IF (PO_LOG.d_exc) THEN
3020 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_info_match');
3021 END IF;
3022
3023 RAISE;
3024 END get_po_line_info;
3025
3026 -------------------------------------------------------------------------------
3027 --Start of Comments
3028 --Name: check_line_info
3029 --Pre-reqs:
3030 -- None
3031 --Modifies:
3032 -- None
3033 --Locks:
3034 -- None
3035 --Function:
3036 -- Checks whether a requisition line and a requisition/PO line match at the
3037 -- line info level and returns an error message indicating the result.
3038 --Parameters:
3039 --IN:
3040 --p_agreement_id
3041 -- The ID of the agreement specified in the document builder.
3042 --p_line_one
3043 -- The line information for the first line.
3044 --p_line_two
3045 -- The line information for the second line.
3046 --OUT:
3047 --x_message_code
3048 -- The error message code corresponding to whether the two lines match at the
3049 -- line info level or not.
3050 --x_token_name
3051 -- The error message token name corresponding to whether the two lines match
3052 -- at the line info level or not.
3053 --x_token_value
3054 -- The error message token value corresponding to whether the two lines match
3055 -- at the line info level or not.
3056 --Notes:
3057 -- None
3058 --Testing:
3059 -- None
3060 --End of Comments
3061 -------------------------------------------------------------------------------
3062 PROCEDURE check_line_info(
3063 p_agreement_id IN NUMBER,
3064 p_line_one IN LINE_INFO,
3065 p_line_two IN LINE_INFO,
3066 p_relax_item_desc_check IN VARCHAR2, --Autocreate grouping
3067 x_message_code OUT NOCOPY VARCHAR2,
3068 x_token_name OUT NOCOPY VARCHAR2,
3069 x_token_value OUT NOCOPY VARCHAR2
3070 )
3071 IS
3072 d_mod CONSTANT VARCHAR2(100) := D_check_line_info;
3073 d_position NUMBER := 0;
3074 BEGIN
3075 IF (PO_LOG.d_proc) THEN
3076 PO_LOG.proc_begin(d_mod);
3077 PO_LOG.proc_begin(d_mod,'p_agreement_id',p_agreement_id);
3078 END IF;
3079
3080 -- Initialize message code, token name, and token value to NULL
3081 x_message_code := NULL;
3082 x_token_name := NULL;
3083 x_token_value := NULL;
3084
3085
3086 /*CLM autocreate grouping start. In case of CLM grouping, the item description matching rule can be relaxed during the validation.
3087 The user can change the description but still the lines can match. This falg is passed from check_po_line_numbers which is called duing the validation*/
3088 --<Bug 16706158> : Cleaning up the below code. No if else required as botht are firing the same code.
3089 IF (NOT PO_CORE_S.is_equal(p_line_one.item_id, p_line_two.item_id))
3090 THEN
3091 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
3092 x_token_name := 'REASON_FOR_DIFF';
3093 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_ITEMS');
3094
3095 IF (PO_LOG.d_proc) THEN
3096 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3097 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3098 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3099 END IF;
3100
3101 RETURN;
3102 END IF;
3103
3104 --<Bug 16706158> : p_relax_item_desc_check will be 'N' when we are adding lines to doc builder. In such a case it is expected
3105 --not to relax the item description check and do not group lines with different description. While hitting create this value is
3106 --passsed as Y and the check is skipped, allowing the system to honor user's choice to override description and group the lines
3107 IF NVL(p_relax_item_desc_check,'N') = 'N' THEN
3108
3109 --One time items with different description.
3110 IF (p_line_one.item_id IS NULL AND p_line_two.item_id IS NULL
3111 AND NOT PO_CORE_S.is_equal(p_line_one.item_description,
3112 p_line_two.item_description))
3113 THEN
3114 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
3115 x_token_name := 'REASON_FOR_DIFF';
3116 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_ITEMS');
3117
3118 IF (PO_LOG.d_proc) THEN
3119 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3120 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3121 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3122 END IF;
3123
3124 RETURN;
3125 END IF; --One time items with different description
3126 END IF; --p_relax_item_desc_check = 'N'
3127 --<Bug 16706158 Ends>
3128
3129 -- Bugfix#16097884
3130 -- 1) Removed Item Description check for one time items
3131 -- 2) Added Check, Category Id should be same to group/addto
3132 -- between Req and PO for Autocreate Process.
3133
3134 IF (NOT PO_CORE_S.is_equal(
3135 p_line_one.category_id,
3136 p_line_two.category_id))
3137 THEN
3138 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
3139 x_token_name := 'REASON_FOR_DIFF';
3140 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_CATEGORY_ID');
3141
3142 IF (PO_LOG.d_proc) THEN
3143 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3144 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3145 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3146 END IF;
3147
3148 RETURN;
3149 END IF;
3150
3151 IF (NOT PO_CORE_S.is_equal(
3152 p_line_one.item_revision,
3153 p_line_two.item_revision))
3154 THEN
3155 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
3156 x_token_name := 'REASON_FOR_DIFF';
3157 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_ITEM_REVISION');
3158
3159 IF (PO_LOG.d_proc) THEN
3160 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3161 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3162 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3163 END IF;
3164
3165 RETURN;
3166 END IF;
3167
3168 --BUG 5641147 : Donot group for RATE and FIXED PRICE lines
3169 IF p_line_two.order_type_lookup_code IN ('RATE','FIXED PRICE')
3170 OR
3171 (NOT PO_CORE_S.is_equal(
3172 p_line_one.order_type_lookup_code,
3173 p_line_two.order_type_lookup_code
3174 )
3175 OR NOT PO_CORE_S.is_equal(
3176 p_line_one.purchase_basis,
3177 p_line_two.purchase_basis
3178 )
3179 OR NOT PO_CORE_S.is_equal(
3180 p_line_one.matching_basis,
3181 p_line_two.matching_basis))
3182 THEN
3183 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
3184 x_token_name := 'REASON_FOR_DIFF';
3185 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_LINE_TYPE');
3186
3187 IF (PO_LOG.d_proc) THEN
3188 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3189 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3190 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3191 END IF;
3192
3193 RETURN;
3194 END IF;
3195
3196 IF (NOT PO_CORE_S.is_equal(
3197 p_line_one.preferred_grade,
3198 p_line_two.preferred_grade))
3199 THEN
3200 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
3201 x_token_name := 'REASON_FOR_DIFF';
3202 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_PREF_GRADE');
3203
3204 IF (PO_LOG.d_proc) THEN
3205 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3206 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3207 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3208 END IF;
3209
3210 RETURN;
3211 END IF;
3212
3213 IF (NOT PO_CORE_S.is_equal(
3214 p_line_one.unit_meas_lookup_code,
3215 p_line_two.unit_meas_lookup_code))
3216 THEN
3217 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
3218 x_token_name := 'REASON_FOR_DIFF';
3219 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_PRI_UOM');
3220
3221 IF (PO_LOG.d_proc) THEN
3222 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3223 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3224 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3225 END IF;
3226
3227 RETURN;
3228 END IF;
3229
3230 IF (NOT PO_CORE_S.is_equal(
3231 p_line_one.transaction_reason,
3232 p_line_two.transaction_reason))
3233 THEN
3234 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
3235 x_token_name := 'REASON_FOR_DIFF';
3236 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_TRANS_REASON');
3237
3238 IF (PO_LOG.d_proc) THEN
3239 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3240 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3241 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3242 END IF;
3243
3244 RETURN;
3245 END IF;
3246
3247 -- Bug 14186599, allow autocreate Grouping,if the the new Requsition has no CPA reference
3248 -- and the PO line to which the Req line is being added has a CPA reference.
3249 IF (p_line_one.contract_id IS NOT NULL AND NOT PO_CORE_S.is_equal(p_line_one.contract_id, p_line_two.contract_id))
3250 THEN
3251 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
3252 x_token_name := 'REASON_FOR_DIFF';
3253 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_CONTRACT');
3254
3255 IF (PO_LOG.d_proc) THEN
3256 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3257 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3258 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3259 END IF;
3260
3261 RETURN;
3262 END IF;
3263
3264 -- Bug 14186599, allow autocreate Grouping,if the the new Requsition has no BPA reference
3265 -- and the PO line to which the Req line is being added has a Source Document.
3266 IF (p_agreement_id IS NULL
3267 AND p_line_one.source_document_id IS NOT null
3268 AND (NOT PO_CORE_S.is_equal(
3269 p_line_one.source_document_id,
3270 p_line_two.source_document_id
3271 )
3272 OR NOT PO_CORE_S.is_equal(
3273 p_line_one.source_document_line_id,
3274 p_line_two.source_document_line_id)))
3275 THEN
3276 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
3277 x_token_name := 'REASON_FOR_DIFF';
3278 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_SRC_NUM');
3279
3280 IF (PO_LOG.d_proc) THEN
3281 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3282 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3283 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3284 END IF;
3285
3286 RETURN;
3287 END IF;
3288
3289 IF (p_line_two.cancel_flag = 'Y')
3290 THEN
3291 x_message_code := 'PO_ALL_CANT_COMB_CANCLD_LINE';
3292 x_token_name := NULL;
3293 x_token_value := NULL;
3294
3295 IF (PO_LOG.d_proc) THEN
3296 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3297 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3298 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3299 END IF;
3300
3301 RETURN;
3302 END IF;
3303
3304 IF (p_line_two.closed_code = 'FINALLY CLOSED')
3305 THEN
3306 x_message_code := 'PO_ALL_CANT_COMB_FCLOSED_LINE';
3307 x_token_name := NULL;
3308 x_token_value := NULL;
3309
3310 IF (PO_LOG.d_proc) THEN
3311 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3312 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3313 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3314 END IF;
3315
3316 RETURN;
3317 END IF;
3318
3319 IF (NOT PO_CORE_S.is_equal(
3320 p_line_one.supplier_ref_number,
3321 p_line_two.supplier_ref_number))
3322 THEN
3323 x_message_code := 'PO_ALL_LINE_CANNOT_BE_COMB_STP';
3324 x_token_name := 'REASON_FOR_DIFF';
3325 x_token_value := FND_MESSAGE.GET_STRING('PO', 'PO_BW_SUPPLIER_REF_NUMBER');
3326
3327 IF (PO_LOG.d_proc) THEN
3328 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3329 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3330 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3331 END IF;
3332
3333 RETURN;
3334 END IF;
3335
3336 -- CLM changes start
3337 -- Only CLINS can be grouped
3338 IF p_line_one.group_line_id IS NOT NULL
3339 OR p_line_two.group_line_id IS NOT NULL
3340
3341
3342 THEN
3343 x_message_code := 'PO_ALL_CANT_COMB_SLIN';
3344 x_token_name := NULL;
3345 x_token_value := NULL;
3346
3347
3348 IF (PO_LOG.d_proc) THEN
3349 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3350 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3351 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3352 END IF;
3353
3354 RETURN;
3355 END IF;
3356
3357 IF (PO_LOG.d_proc) THEN
3358 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3359 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3360 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3361 END IF;
3362
3363 -- Only PRICED Lines can be grouped
3364 IF Nvl(p_line_one.clm_info_flag,'N') = 'Y'
3365 OR Nvl(p_line_two.clm_info_flag,'N') = 'Y'
3366 THEN
3367
3368 x_message_code := 'PO_ALL_CANT_COMB_INFO_LINE';
3369 x_token_name := NULL;
3370 x_token_value := NULL;
3371
3372
3373 IF (PO_LOG.d_proc) THEN
3374 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3375 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3376 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3377 END IF;
3378
3379 RETURN;
3380 END IF;
3381
3382 --Only base lines can be grouped.
3383 IF p_line_one.CLM_BASE_LINE_NUM IS NOT NULL
3384 OR p_line_two.CLM_BASE_LINE_NUM IS NOT NULL
3385 THEN
3386
3387 x_message_code := 'PO_ALL_CANT_COMB_OPTION';
3388 x_token_name := NULL;
3389 x_token_value := NULL;
3390
3391
3392 IF (PO_LOG.d_proc) THEN
3393 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3394 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3395 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3396 END IF;
3397
3398 RETURN;
3399 END IF;
3400 -- CLM changes END
3401
3402 IF (PO_LOG.d_proc) THEN
3403 PO_LOG.proc_end(d_mod, 'x_message_code', x_message_code);
3404 PO_LOG.proc_end(d_mod, 'x_token_name', x_token_name);
3405 PO_LOG.proc_end(d_mod, 'x_token_value', x_token_value);
3406 END IF;
3407
3408
3409
3410
3411 EXCEPTION
3412 WHEN OTHERS THEN
3413 IF (PO_LOG.d_exc) THEN
3414 PO_LOG.exc(d_mod, d_position, 'An error occured in lines_info_match');
3415 END IF;
3416
3417 RAISE;
3418 END check_line_info;
3419
3420 -------------------------------------------------------------------------------
3421 --Start of Comments
3422 --Name: group_by_requisition_line_num
3423 --Pre-reqs: None
3424 --Modifies:
3425 --Locks:
3426 -- None
3427 --Function:
3428 -- Perform 'REQUISITION' grouping using the requisition line numbers.
3429 -- If the req line number has already been used as the PO line number
3430 -- for a previous req line, then NULL will be assigned.
3431 --Parameters:
3432 --IN:
3433 --p_req_line_num_tbl
3434 -- Table of requisition line numbers for the req lines in the Doc Builder.
3435 --p_po_line_num_tbl
3436 -- Table of PO line numbers currently assigned to the Document Builder
3437 -- requisition lines.
3438 --p_start_index
3439 -- The index of the first requisition line in the input table which
3440 -- should have a PO line number calculated (default value of 1
3441 -- if not specified).
3442 --p_end_index
3443 -- The index of the last requisition line in the input table which
3444 -- should have a PO line number calculated (default value of last
3445 -- index in the table if not specified).
3446 --Returns:
3447 -- Table of PO line numbers corresponding to each of the input requisition
3448 -- lines.
3449 --Notes:
3450 -- N/A
3451 --Testing:
3452 -- N/A
3453 --End of Comments
3454 -------------------------------------------------------------------------------
3455 FUNCTION group_by_requisition_line_num
3456 (
3457 p_req_line_num_tbl IN PO_TBL_NUMBER
3458 , p_po_line_num_tbl IN PO_TBL_NUMBER
3459 , p_start_index IN NUMBER
3460 , p_end_index IN NUMBER
3461 )
3462 RETURN PO_TBL_NUMBER
3463 IS
3464 x_po_line_num_tbl PO_TBL_NUMBER := p_po_line_num_tbl;
3465
3466 -- Bug 13778496 change the index type of NUM_INDEX_TBL_TYPE from PLS_INTEGER
3467 -- to VARCHAR2(40) in order to resolve the fraction requisition line num and
3468 -- generate fraction value of po line num
3469 TYPE NUM_INDEX_TBL_TYPE IS TABLE OF VARCHAR2(1) INDEX BY VARCHAR2(40);
3470
3471 l_line_num_used_tbl NUM_INDEX_TBL_TYPE;
3472
3473 d_mod CONSTANT VARCHAR2(100) := D_group_by_requisition_line;
3474 d_position NUMBER := 0;
3475
3476 BEGIN
3477
3478 IF PO_LOG.d_proc THEN
3479 PO_LOG.proc_begin(d_mod,'p_req_line_num_tbl',p_req_line_num_tbl);
3480 PO_LOG.proc_begin(d_mod,'p_po_line_num_tbl',p_po_line_num_tbl);
3481 PO_LOG.proc_begin(d_mod,'p_start_index',p_start_index);
3482 PO_LOG.proc_begin(d_mod,'p_end_index',p_end_index);
3483 END IF;
3484
3485 -- Initialize the l_line_num_used_tbl to indicate which line
3486 -- numbers are already assigned. We will mark a 'Y' in the associative
3487 -- array at the index with the value of the used line number.
3488 --
3489 FOR i IN 1..(p_start_index - 1)
3490 LOOP
3491 IF ( p_po_line_num_tbl(i) IS NOT NULL )
3492 THEN
3493 -- Bug 13778496,add to_char function
3494 l_line_num_used_tbl(TO_CHAR(p_po_line_num_tbl(i))) := 'Y';
3495 END IF;
3496 END LOOP;
3497
3498 d_position := 10;
3499
3500 -- Loop through and assign req line numbers as the PO line numbers
3501 -- if no other req line before already has that PO line number
3502 -- assigned.
3503 --
3504 FOR i IN p_start_index..p_end_index
3505 LOOP
3506 -- Bug 13778496,add to_char function
3507 IF ( l_line_num_used_tbl.EXISTS(TO_CHAR(p_req_line_num_tbl(i))) )
3508 THEN
3509 x_po_line_num_tbl(i) := NULL;
3510 ELSE
3511 x_po_line_num_tbl(i) := p_req_line_num_tbl(i);
3512 -- Bug 13778496,add to_char function
3513 l_line_num_used_tbl(TO_CHAR(x_po_line_num_tbl(i)) ) := 'Y';
3514 END IF;
3515 END LOOP;
3516
3517 d_position := 50;
3518 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,x_po_line_num_tbl); END IF;
3519
3520 return (x_po_line_num_tbl);
3521
3522 EXCEPTION
3523
3524 WHEN OTHERS THEN
3525 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
3526 RAISE;
3527
3528 END group_by_requisition_line_num;
3529
3530 -------------------------------------------------------------------------------
3531 --Start of Comments
3532 --Name: group_by_clm_req_line_num (CLM Line Number)
3533 --Pre-reqs: None
3534 --Modifies:
3535 --Locks:
3536 -- None
3537 --Function:
3538 -- Perform 'REQUISITION' grouping using the requisition line numbers.
3539 -- If the req line number has already been used as the PO line number
3540 -- for a previous req line, then NULL will be assigned.
3541 --Parameters:
3542 --IN:
3543 --p_req_line_num_tbl
3544 -- Table of requisition line numbers for the req lines in the Doc Builder.
3545 --p_po_line_num_tbl
3546 -- Table of PO line numbers currently assigned to the Document Builder
3547 -- requisition lines.
3548 --p_start_index
3549 -- The index of the first requisition line in the input table which
3550 -- should have a PO line number calculated (default value of 1
3551 -- if not specified).
3552 --p_end_index
3553 -- The index of the last requisition line in the input table which
3554 -- should have a PO line number calculated (default value of last
3555 -- index in the table if not specified).
3556 --Returns:
3557 -- Table of PO line numbers corresponding to each of the input requisition
3558 -- lines.
3559 --Notes:
3560 -- N/A
3561 --Testing:
3562 -- N/A
3563 --End of Comments
3564 -------------------------------------------------------------------------------
3565 FUNCTION group_by_clm_req_line_num
3566 (
3567 p_req_line_num_tbl IN PO_TBL_VARCHAR100
3568 , p_po_line_num_tbl IN PO_TBL_VARCHAR100
3569 , p_start_index IN NUMBER
3570 , p_end_index IN NUMBER
3571 )
3572 RETURN PO_TBL_VARCHAR100
3573 IS
3574 x_po_line_num_tbl PO_TBL_VARCHAR100 := p_po_line_num_tbl;
3575
3576 TYPE NUM_INDEX_TBL_TYPE IS TABLE OF VARCHAR2(1) INDEX BY VARCHAR2(100);
3577 l_line_num_used_tbl NUM_INDEX_TBL_TYPE;
3578
3579 d_mod CONSTANT VARCHAR2(100) := D_group_by_clm_req_line_num;
3580 d_position NUMBER := 0;
3581
3582 BEGIN
3583
3584 IF PO_LOG.d_proc THEN
3585 PO_LOG.proc_begin(d_mod,'p_req_line_num_tbl',p_req_line_num_tbl);
3586 PO_LOG.proc_begin(d_mod,'p_po_line_num_tbl',p_po_line_num_tbl);
3587 PO_LOG.proc_begin(d_mod,'p_start_index',p_start_index);
3588 PO_LOG.proc_begin(d_mod,'p_end_index',p_end_index);
3589 END IF;
3590
3591 -- Initialize the l_line_num_used_tbl to indicate which line
3592 -- numbers are already assigned. We will mark a 'Y' in the associative
3593 -- array at the index with the value of the used line number.
3594 --
3595 FOR i IN 1..(p_start_index - 1)
3596 LOOP
3597 IF ( p_po_line_num_tbl(i) IS NOT NULL )
3598 THEN
3599 l_line_num_used_tbl(p_po_line_num_tbl(i)) := 'Y';
3600 END IF;
3601 END LOOP;
3602
3603 d_position := 10;
3604
3605 -- Loop through and assign req line numbers as the PO line numbers
3606 -- if no other req line before already has that PO line number
3607 -- assigned.
3608 --
3609 FOR i IN p_start_index..p_end_index
3610 LOOP
3611 IF ( l_line_num_used_tbl.EXISTS(p_req_line_num_tbl(i)) )
3612 THEN
3613 x_po_line_num_tbl(i) := NULL;
3614 ELSE
3615 x_po_line_num_tbl(i) := p_req_line_num_tbl(i);
3616 l_line_num_used_tbl(x_po_line_num_tbl(i)) := 'Y';
3617 END IF;
3618 END LOOP;
3619
3620 d_position := 50;
3621 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,x_po_line_num_tbl); END IF;
3622
3623 return (x_po_line_num_tbl);
3624
3625 EXCEPTION
3626
3627 WHEN OTHERS THEN
3628 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
3629 RAISE;
3630
3631 END group_by_clm_req_line_num;
3632
3633
3634 -------------------------------------------------------------------------------
3635 --Start of Comments
3636 --Name: group_by_requisition_seq_num
3637 --Pre-reqs: None
3638 --Modifies:
3639 --Locks:
3640 -- None
3641 --Function:
3642 -- Perform 'REQUISITION' grouping using the sequential line numbers
3643 -- starting after the maximum line number currently in the Doc Builder
3644 -- or on the PO being added to.
3645 --Parameters:
3646 --IN:
3647 --p_po_line_num_tbl
3648 -- Table of PO line numbers currently assigned to the Document Builder
3649 -- requisition lines.
3650 --p_add_to_po_header_id
3651 -- The ID of the PO to which the requisition lines are being added
3652 -- (will be null when creating a new PO).
3653 --p_start_index
3654 -- The index of the first requisition line in the input table which
3655 -- should have a PO line number calculated (default value of 1
3656 -- if not specified).
3657 --p_end_index
3658 -- The index of the last requisition line in the input table which
3659 -- should have a PO line number calculated (default value of last
3660 -- index in the table if not specified).
3661 --Returns:
3662 -- Table of PO line numbers corresponding to each of the input requisition
3663 -- lines.
3664 --Notes:
3665 -- N/A
3666 --Testing:
3667 -- N/A
3668 --End of Comments
3669 -------------------------------------------------------------------------------
3670 FUNCTION group_by_requisition_seq_num
3671 (
3672 p_po_line_num_tbl IN PO_TBL_NUMBER
3673 , p_add_to_po_header_id IN NUMBER
3674 , p_start_index IN NUMBER
3675 , p_end_index IN NUMBER
3676 )
3677 RETURN PO_TBL_NUMBER
3678 IS
3679 x_po_line_num_tbl PO_TBL_NUMBER := p_po_line_num_tbl;
3680
3681 l_max_line_num NUMBER;
3682 l_next_line_num NUMBER;
3683
3684 d_mod CONSTANT VARCHAR2(100) := D_group_by_requisition_seq_num;
3685 d_position NUMBER := 0;
3686
3687 BEGIN
3688
3689 -- Get the max line number from either the PO or the other
3690 -- lines in the Document Builder.
3691 --
3692 l_max_line_num := get_max_po_line_num ( p_po_line_num_tbl
3693 , p_add_to_po_header_id );
3694
3695 -- Loop through and assign line numbers sequentially to each
3696 -- req line starting from the max line number derived above.
3697 --
3698 l_next_line_num := l_max_line_num + 1;
3699
3700 FOR i IN p_start_index..p_end_index
3701 LOOP
3702 x_po_line_num_tbl(i) := l_next_line_num;
3703 l_next_line_num := l_next_line_num + 1;
3704 END LOOP;
3705
3706 return (x_po_line_num_tbl);
3707
3708 EXCEPTION
3709
3710 WHEN OTHERS THEN
3711 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
3712 RAISE;
3713
3714 END group_by_requisition_seq_num;
3715
3716 -------------------------------------------------------------------------------
3717 --Start of Comments
3718 --Name: group_by_requisition_seq_num (CLM Line Number)
3719 --Pre-reqs: None
3720 --Modifies:
3721 --Locks:
3722 -- None
3723 --Function:
3724 -- Perform 'REQUISITION' grouping using the sequential line numbers
3725 -- starting after the maximum line number currently in the Doc Builder
3726 -- or on the PO being added to.
3727 --Parameters:
3728 --IN:
3729 --p_po_line_num_tbl
3730 -- Table of PO line numbers currently assigned to the Document Builder
3731 -- requisition lines.
3732 --p_add_to_po_header_id
3733 -- The ID of the PO to which the requisition lines are being added
3734 -- (will be null when creating a new PO).
3735 --p_start_index
3736 -- The index of the first requisition line in the input table which
3737 -- should have a PO line number calculated (default value of 1
3738 -- if not specified).
3739 --p_end_index
3740 -- The index of the last requisition line in the input table which
3741 -- should have a PO line number calculated (default value of last
3742 -- index in the table if not specified).
3743 --Returns:
3744 -- Table of PO line numbers corresponding to each of the input requisition
3745 -- lines.
3746 --Notes:
3747 -- N/A
3748 --Testing:
3749 -- N/A
3750 --End of Comments
3751 -------------------------------------------------------------------------------
3752 FUNCTION group_by_clm_req_seq_num
3753 ( p_po_line_num_tbl IN PO_TBL_VARCHAR100
3754 , p_req_line_id_tbl IN PO_TBL_NUMBER
3755 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
3756 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
3757 , p_add_to_po_header_id IN NUMBER
3758 , p_draft_id IN NUMBER --Autocreate grouping
3759 , p_start_index IN NUMBER
3760 , p_end_index IN NUMBER
3761 ) RETURN PO_TBL_VARCHAR100
3762 IS
3763 x_po_line_num_tbl PO_TBL_VARCHAR100 := p_po_line_num_tbl;
3764
3765 l_max_line_num VARCHAR2(100);
3766 l_next_line_num VARCHAR2(100);
3767
3768
3769 l_next_info_slin_num varchar2(100);
3770 l_next_priced_slin_num varchar2(100);
3771
3772 d_mod CONSTANT VARCHAR2(100) := D_group_by_clm_req_seq_num;
3773 d_position NUMBER := 0;
3774
3775
3776 BEGIN
3777
3778 -- Get the max line number from either the PO or the other
3779 -- lines in the Document Builder.
3780 --
3781 l_max_line_num := get_max_clm_po_line_num ( p_po_line_num_tbl
3782 , p_add_to_po_header_id
3783 , p_draft_id --Autocreate grouping
3784 ,p_req_group_line_id_tbl);
3785
3786 -- Loop through and assign line numbers sequentially to each
3787 -- req line starting from the max line number derived above.
3788
3789 -- Make a call to PON_CLO_RENUMBER_PKG to get the next CLIN number
3790 l_next_line_num := PON_CLO_RENUMBER_PKG.INCREMENT_CLIN_NUMBER(l_max_line_num);
3791
3792 -- First initialize the CLIN numbers
3793 FOR i IN p_start_index..p_end_index
3794 LOOP
3795 IF p_req_group_line_id_tbl(i) is null then
3796
3797 x_po_line_num_tbl(i) := l_next_line_num;
3798
3799 l_next_info_slin_num := l_next_line_num ||'01';
3800 l_next_priced_slin_num := l_next_line_num ||'AA';
3801
3802 -- Make a call to PON_CLO_RENUMBER_PKG to get the next CLIN number
3803
3804 l_next_line_num := PON_CLO_RENUMBER_PKG.INCREMENT_CLIN_NUMBER(l_next_line_num);
3805
3806 For j IN p_start_index..p_end_index
3807 Loop
3808
3809 if p_req_group_line_id_tbl(j) is not null and p_req_group_line_id_tbl(j) = p_req_line_id_tbl(i) then
3810 IF p_req_clm_info_flag_tbl(j)='Y' then
3811
3812 x_po_line_num_tbl(j) := l_next_info_slin_num;
3813 -- Make a call to package to get next info slin number
3814 l_next_info_slin_num := PON_CLO_RENUMBER_PKG.INCREMENT_INFO_SLIN_NUMBER(l_next_info_slin_num);
3815
3816 ELSE
3817 x_po_line_num_tbl(j) := l_next_priced_slin_num;
3818 -- Make a call to package to get next priced slin number
3819 l_next_priced_slin_num := PON_CLO_RENUMBER_PKG.INCREMENT_PRICED_SLIN_NUMBER(l_next_priced_slin_num);
3820 END IF;
3821
3822 end if;
3823 end loop;
3824
3825 END IF;
3826 END LOOP;
3827
3828
3829
3830 return (x_po_line_num_tbl);
3831
3832 EXCEPTION
3833
3834 WHEN OTHERS THEN
3835 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
3836 RAISE;
3837
3838 END group_by_clm_req_seq_num;
3839
3840 -------------------------------------------------------------------------------
3841 --Start of Comments
3842 --Name: group_by_default
3843 --Pre-reqs: None
3844 --Modifies:
3845 --Locks:
3846 -- None
3847 --Function:
3848 -- Defaults the PO line numbers for requisition lines in the Autocreate
3849 -- Document Builder using the 'Default' grouping method.
3850 --Parameters:
3851 --IN:
3852 --p_req_line_id_tbl
3853 -- Table of requisition line IDs representing the Autocreate Document Builder.
3854 --p_po_line_num_tbl
3855 -- Table of PO line numbers currently assigned to the Document Builder
3856 -- requisition lines. The PO line numbers for req lines within the
3857 -- start and end index (i.e. those for which defaulting will be performed)
3858 -- must be nulled out.
3859 --p_consigned_flag_tbl
3860 -- Table of Consigned Flag values corresponding to each req line in
3861 -- p_req_line_id_tbl.
3862 --p_add_to_po_header_id
3863 -- The ID of the PO to which the requisition lines are being added
3864 -- (will be null when creating a new PO).
3865 --p_builder_agreement_id
3866 -- The ID of the Global Agreement for which the PO will be created.
3867 --p_start_index
3868 -- The index of the first requisition line in the input table which
3869 -- should have a PO line number calculated (default value of 1
3870 -- if not specified).
3871 --p_end_index
3872 -- The index of the last requisition line in the input table which
3873 -- should have a PO line number calculated (default value of last
3874 -- index in the table if not specified).
3875 --Returns:
3876 -- Table of PO line numbers corresponding to each of the input requisition
3877 -- lines.
3878 --Notes:
3879 -- N/A
3880 --Testing:
3881 -- N/A
3882 --End of Comments
3883 -------------------------------------------------------------------------------
3884 FUNCTION group_by_default
3885 (
3886 p_req_line_id_tbl IN PO_TBL_NUMBER
3887 , p_po_line_num_tbl IN PO_TBL_NUMBER
3888 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
3889 , p_add_to_po_header_id IN NUMBER
3890 , p_builder_agreement_id IN NUMBER
3891 , p_start_index IN NUMBER
3892 , p_end_index IN NUMBER
3893 )
3894 RETURN PO_TBL_NUMBER
3895 IS
3896 l_max_line_num NUMBER := 0;
3897 l_matching_index NUMBER;
3898 l_po_line_num NUMBER;
3899 x_po_line_num_tbl PO_TBL_NUMBER := p_po_line_num_tbl;
3900
3901 l_add_to_po_req_line_id_tbl PO_TBL_NUMBER;
3902 l_add_to_po_line_num_tbl PO_TBL_NUMBER;
3903
3904 d_mod CONSTANT VARCHAR2(100) := D_group_by_default;
3905 d_position NUMBER := 0;
3906
3907 BEGIN
3908
3909 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
3910
3911 -- Initialize the max PO line number.
3912 --
3913 l_max_line_num := get_max_po_line_num ( p_po_line_num_tbl
3914 , p_add_to_po_header_id );
3915
3916 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'l_max_line_num',l_max_line_num); END IF;
3917
3918 d_position := 10;
3919
3920 -- Get nested tables of requisition lines and their corresponding
3921 -- matching PO line numbers from the PO being added to.
3922 --
3923 match_add_to_po_lines ( p_req_line_id_tbl
3924 , p_consigned_flag_tbl
3925 , p_add_to_po_header_id
3926 , -1 --Autocreate grouping, draft_id - in case of non-clm documents this will always be -1
3927 , p_builder_agreement_id
3928 , p_start_index
3929 , p_end_index
3930 , l_add_to_po_req_line_id_tbl -- OUT
3931 , l_add_to_po_line_num_tbl -- OUT
3932 );
3933 d_position := 20;
3934
3935 -- Loop through input nested table of req lines.
3936 --
3937 FOR i IN p_start_index..p_end_index LOOP
3938
3939 l_po_line_num := NULL;
3940
3941 d_position := 30;
3942
3943 -- Find a matching line on the PO being added to (if one exists).
3944 --
3945 IF ( p_add_to_po_header_id IS NOT NULL )
3946 THEN
3947 l_po_line_num := find_matching_po_line_num
3948 ( p_req_line_id => p_req_line_id_tbl(i)
3949 , p_comparison_tbl => l_add_to_po_req_line_id_tbl
3950 , p_po_line_num_tbl => l_add_to_po_line_num_tbl
3951 );
3952 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'Add To PO Line Num',l_po_line_num); END IF;
3953 END IF;
3954
3955 d_position := 40;
3956
3957 -- If no line was found on the PO being added to, search for any
3958 -- matching lines before the current line in the Doc Builder.
3959 --
3960 IF ( l_po_line_num IS NULL )
3961 THEN
3962 l_po_line_num := find_matching_builder_line_num
3963 ( p_current_index => i
3964 , p_req_line_id_tbl => p_req_line_id_tbl
3965 , p_po_line_num_tbl => x_po_line_num_tbl
3966 , p_builder_agreement_id => p_builder_agreement_id
3967 );
3968 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'Builder Line Num',l_po_line_num); END IF;
3969 END IF;
3970
3971 d_position := 50;
3972
3973 -- If no matching lines found in the PO or the Doc Builder,
3974 -- set the PO line num to the next line number and then set
3975 -- the new max line number.
3976 --
3977 IF ( l_po_line_num IS NULL )
3978 THEN
3979 l_max_line_num := l_max_line_num + 1;
3980 l_po_line_num := l_max_line_num;
3981
3982 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'Max Line Num',l_po_line_num); END IF;
3983 END IF;
3984
3985 d_position := 60;
3986
3987 x_po_line_num_tbl(i) := l_po_line_num;
3988
3989 END LOOP;
3990
3991 d_position := 70;
3992
3993 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,x_po_line_num_tbl); END IF;
3994
3995 return (x_po_line_num_tbl);
3996
3997 EXCEPTION
3998
3999 WHEN OTHERS THEN
4000 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
4001 RAISE;
4002
4003 END group_by_default;
4004
4005 -------------------------------------------------------------------------------
4006 --Start of Comments
4007 --Name: has_same_req_header
4008 --Pre-reqs: None
4009 --Modifies:
4010 --Locks:
4011 -- None
4012 --Function:
4013 -- Determines if all the requisition lines in the input list belong to the
4014 -- same requisition header.
4015 --Parameters:
4016 --IN:
4017 --p_req_line_id_tbl
4018 -- Table of requisition line IDs representing the Autocreate Document Builder.
4019 --Returns:
4020 -- TRUE if all req lines in the input nested table belong to the same
4021 -- requisition header; FALSE otherwise.
4022 --Notes:
4023 -- N/A
4024 --Testing:
4025 -- N/A
4026 --End of Comments
4027 -------------------------------------------------------------------------------
4028 FUNCTION has_same_req_header
4029 (
4030 p_req_line_id_tbl IN PO_TBL_NUMBER
4031 )
4032 RETURN BOOLEAN
4033 IS
4034 l_req_header_id NUMBER;
4035 l_first_req_header_id NUMBER;
4036
4037 d_mod CONSTANT VARCHAR2(100) := D_has_same_req_header;
4038 d_position NUMBER := 0;
4039
4040 BEGIN
4041
4042 -- Loop through all req lines and compare their req headers
4043 -- with each other.
4044 --
4045 FOR i IN 1..p_req_line_id_tbl.COUNT LOOP
4046
4047 d_position := 10;
4048
4049 SELECT requisition_header_id
4050 INTO l_req_header_id
4051 FROM po_requisition_lines_all
4052 WHERE requisition_line_id = p_req_line_id_tbl(i);
4053
4054 d_position := 20;
4055
4056 -- If first line, initialize variable for first line.
4057 -- Otherwise, check if the current req header matches
4058 -- that of the first req header.
4059 --
4060 IF ( l_first_req_header_id IS NULL ) THEN
4061
4062 l_first_req_header_id := l_req_header_id;
4063
4064 ELSIF ( l_req_header_id <> l_first_req_header_id ) THEN
4065
4066 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,FALSE); END IF;
4067 return (FALSE); -- return false if header does not match
4068
4069 END IF;
4070
4071 END LOOP;
4072
4073 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,TRUE); END IF;
4074 return (TRUE);
4075
4076 EXCEPTION
4077
4078 WHEN OTHERS THEN
4079 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
4080 RAISE;
4081
4082 END has_same_req_header;
4083
4084 -------------------------------------------------------------------------------
4085 --Start of Comments
4086 --Name: match_add_to_po_lines
4087 --Pre-reqs: None
4088 --Modifies:
4089 --Locks:
4090 -- None
4091 --Function:
4092 -- Constructs a table of requisition line IDs representing the subset of
4093 -- the input requisition lines that match at least one line on the input PO.
4094 -- Also constructs a corresponding table of PO line numbers from the
4095 -- mached PO lines.
4096 --Parameters:
4097 --IN:
4098 --p_req_line_id_tbl
4099 -- Table of requisition line IDs representing the Autocreate Document Builder.
4100 --p_consigned_flag_tbl
4101 -- Table of Consigned Flag values corresponding to each req line in
4102 -- p_req_line_id_tbl.
4103 --p_add_to_po_header_id
4104 -- The ID of the PO to which the requisition lines are being added
4105 -- (will be null when creating a new PO).
4106 --p_builder_agreement_id
4107 -- The ID of the Global Agreement for which the PO will be created.
4108 --p_start_index
4109 -- The index of the first requisition line in the input table which
4110 -- should have a PO line number calculated (default value of 1
4111 -- if not specified).
4112 --p_end_index
4113 -- The index of the last requisition line in the input table which
4114 -- should have a PO line number calculated (default value of last
4115 -- index in the table if not specified).
4116 --OUT:
4117 --x_req_line_id_tbl
4118 -- Table of requisition line IDs that match at least one line on the PO.
4119 --x_po_line_num_tbl
4120 -- Table of PO line numbers representing the lines on the PO which match
4121 -- to each of the requisition lines in x_req_line_id_tbl.
4122 --Notes:
4123 -- N/A
4124 --Testing:
4125 -- N/A
4126 --End of Comments
4127 -------------------------------------------------------------------------------
4128 PROCEDURE match_add_to_po_lines
4129 (
4130 p_req_line_id_tbl IN PO_TBL_NUMBER
4131 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
4132 , p_add_to_po_header_id IN NUMBER
4133 , p_draft_id IN NUMBER --Autocreate grouping
4134 , p_builder_agreement_id IN NUMBER
4135 , p_start_index IN NUMBER
4136 , p_end_index IN NUMBER
4137 , x_req_line_id_tbl OUT NOCOPY PO_TBL_NUMBER
4138 , x_po_line_num_tbl OUT NOCOPY PO_TBL_NUMBER
4139 )
4140 IS
4141 l_key NUMBER;
4142
4143 l_need_by_grouping_profile
4144 FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
4145 l_ship_to_grouping_profile
4146 FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
4147
4148 d_mod CONSTANT VARCHAR2(100) := D_match_add_to_po_lines;
4149 d_position NUMBER := 0;
4150
4151 BEGIN
4152
4153 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
4154
4155 -- Return immediately if there is no Add To PO.
4156 --
4157 IF ( p_add_to_po_header_id IS NULL )
4158 THEN
4159 x_req_line_id_tbl := PO_TBL_NUMBER();
4160 x_po_line_num_tbl := PO_TBL_NUMBER();
4161 return;
4162 END IF;
4163
4164 d_position := 10;
4165
4166 -- Retrieve a new GT table key.
4167 --
4168 l_key := PO_CORE_S.get_session_gt_nextval;
4169
4170 -- Get Profile Option values to be used in query.
4171 --
4172 l_need_by_grouping_profile := FND_PROFILE.value('PO_NEED_BY_GROUPING');
4173 l_ship_to_grouping_profile := FND_PROFILE.value('PO_SHIPTO_GROUPING');
4174
4175 d_position := 20;
4176 IF PO_LOG.d_stmt THEN
4177 PO_LOG.stmt(d_mod,d_position,'l_key',l_key);
4178 PO_LOG.stmt(d_mod,d_position,'l_need_by_grouping_profile',l_need_by_grouping_profile);
4179 PO_LOG.stmt(d_mod,d_position,'l_ship_to_grouping_profile',l_ship_to_grouping_profile);
4180 END IF;
4181
4182 -- Bulk insert matching lines into GT table.
4183 --
4184 FORALL i IN p_start_index..p_end_index
4185
4186 INSERT INTO po_session_gt
4187 ( key
4188 , num1
4189 , num2
4190 )
4191 SELECT DISTINCT
4192 l_key
4193 , prl.requisition_line_id
4194 , pol.line_num
4195 FROM po_requisition_lines_all prl
4196 , po_lines_merge_v pol
4197 , po_line_locations_merge_v pll
4198 , po_lines_merge_v src_line
4199 WHERE pol.po_header_id = p_add_to_po_header_id
4200 AND pol.draft_id = p_draft_id
4201 AND pll.po_line_id = pol.po_line_id
4202 AND pll.draft_id = pol.draft_id
4203 AND prl.requisition_line_id = p_req_line_id_tbl(i)
4204 AND prl.group_line_id IS NULL
4205 AND prl.clm_base_line_num IS NULL
4206 AND Nvl(prl.clm_info_flag ,'N')='N'
4207 AND pol.group_line_id IS NULL
4208 AND pol.clm_base_line_num IS NULL
4209 AND Nvl(pol.clm_info_flag ,'N')='N'
4210 AND decode ( prl.item_id
4211 , pol.item_id, 1, 0) = 1
4212 AND ((prl.item_id IS NOT NULL OR pol.item_id IS NOT NULL)
4213 OR decode(
4214 prl.item_description,
4215 pol.item_description, 1, 0) = 1)
4216 AND decode ( prl.item_revision
4217 , pol.item_revision, 1, 0) = 1
4218 AND decode ( prl.line_type_id
4219 , pol.line_type_id, 1, 0) = 1
4220 AND decode ( prl.preferred_grade
4221 , pol.preferred_grade, 1, 0) = 1
4222 AND decode ( prl.unit_meas_lookup_code
4223 , pol.unit_meas_lookup_code, 1, 0) = 1
4224 AND decode ( prl.transaction_reason_code
4225 , pol.transaction_reason_code, 1, 0) = 1
4226 AND decode ( prl.supplier_ref_number
4227 , pol.supplier_ref_number, 1, 0) = 1
4228 AND ( ( l_need_by_grouping_profile = 'N' )
4229 OR ( decode ( trunc(prl.need_by_date,'MI')
4230 , trunc(pll.need_by_date,'MI'), 1, 0) = 1 ) )
4231 AND ( ( l_ship_to_grouping_profile = 'N' )
4232 OR ( decode ( prl.destination_organization_id
4233 , pll.ship_to_organization_id, 1, 0) = 1 ) )
4234 AND ( ( prl.document_type_code <> 'CONTRACT' )
4235 OR ( decode ( prl.blanket_po_header_id
4236 , pol.contract_id, 1, 0) = 1 ) )
4237 AND src_line.po_header_id (+) = prl.blanket_po_header_id
4238 AND src_line.line_num (+) = prl.blanket_po_line_num
4239 AND ( ( p_builder_agreement_id IS NOT NULL )
4240 OR ( ( decode ( prl.blanket_po_header_id
4241 , pol.from_header_id, 1, 0) = 1 )
4242 AND ( decode ( src_line.po_line_id
4243 , pol.from_line_id, 1, 0) = 1 ) ) )
4244 AND nvl(pol.cancel_flag, 'N') <> 'Y'
4245 AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
4246 AND nvl(pll.consigned_flag, 'N') = p_consigned_flag_tbl(i);
4247
4248 d_position := 30;
4249
4250 -- Clean up GT table and return the results into the output tables.
4251 --
4252 DELETE FROM po_session_gt
4253 WHERE key = l_key
4254 RETURNING num1, num2
4255 BULK COLLECT INTO x_req_line_id_tbl, x_po_line_num_tbl;
4256
4257 IF PO_LOG.d_proc THEN
4258 PO_LOG.proc_end(d_mod,'x_req_line_id_tbl',x_req_line_id_tbl);
4259 PO_LOG.proc_end(d_mod,'x_po_line_num_tbl',x_po_line_num_tbl);
4260 END IF;
4261
4262 EXCEPTION
4263
4264 WHEN OTHERS THEN
4265 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
4266 RAISE;
4267
4268 END match_add_to_po_lines;
4269
4270
4271 -------------------------------------------------------------------------------
4272
4273 Procedure clm_match_add_to_po_lines
4274 (
4275 p_req_line_id_tbl IN PO_TBL_NUMBER
4276 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
4277 , p_add_to_po_header_id IN NUMBER
4278 , p_draft_id IN NUMBER --Autocreate grouping
4279 , p_builder_agreement_id IN NUMBER
4280 , p_start_index IN NUMBER
4281 , p_end_index IN NUMBER
4282 , x_req_line_id_tbl OUT NOCOPY PO_TBL_NUMBER
4283 , x_po_line_num_tbl OUT NOCOPY PO_TBL_NUMBER
4284 , x_po_line_num_disp_tbl OUT NOCOPY PO_TBL_VARCHAR100
4285 )
4286 IS
4287 l_key NUMBER;
4288 l_po_line_num_disp_tbl PO_TBL_VARCHAR100 := PO_TBL_VARCHAR100();
4289
4290 l_need_by_grouping_profile
4291 FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
4292 l_ship_to_grouping_profile
4293 FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
4294
4295 d_mod CONSTANT VARCHAR2(100) := D_clm_match_add_to_po_lines;
4296 d_position NUMBER := 0;
4297
4298 BEGIN
4299
4300 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
4301
4302 -- Return immediately if there is no Add To PO.
4303
4304
4305 IF ( p_add_to_po_header_id IS NULL )
4306 THEN
4307 x_req_line_id_tbl := PO_TBL_NUMBER();
4308 x_po_line_num_tbl := PO_TBL_NUMBER();
4309 x_po_line_num_disp_tbl := PO_TBL_VARCHAR100();
4310 RETURN;
4311 END IF;
4312
4313 d_position := 10;
4314
4315 -- Retrieve a new GT table key.
4316 --
4317 l_key := PO_CORE_S.get_session_gt_nextval;
4318
4319 -- Get Profile Option values to be used in query.
4320 --
4321 l_need_by_grouping_profile := FND_PROFILE.value('PO_NEED_BY_GROUPING');
4322 l_ship_to_grouping_profile := FND_PROFILE.value('PO_SHIPTO_GROUPING');
4323
4324
4325
4326 d_position := 20;
4327 IF PO_LOG.d_stmt THEN
4328 PO_LOG.stmt(d_mod,d_position,'l_key',l_key);
4329 PO_LOG.stmt(d_mod,d_position,'l_need_by_grouping_profile',l_need_by_grouping_profile);
4330 PO_LOG.stmt(d_mod,d_position,'l_ship_to_grouping_profile',l_ship_to_grouping_profile);
4331 END IF;
4332
4333 -- Bulk insert matching lines into GT table.
4334 --
4335 FORALL i IN p_start_index..p_end_index
4336
4337 INSERT INTO po_session_gt
4338 ( key
4339 , num1
4340 , num2
4341 , char1
4342 )
4343 SELECT DISTINCT
4344 l_key
4345 , prl.requisition_line_id
4346 , pol.line_num
4347 , pol.line_num_display
4348 FROM po_requisition_lines_all prl
4349 , po_lines_merge_v pol
4350 , po_line_locations_merge_v pll
4351 , po_lines_merge_v src_line
4352 WHERE pol.po_header_id = p_add_to_po_header_id
4353 AND nvl(pol.draft_id,-1) = nvl(p_draft_id,-1)
4354 AND pll.po_line_id = pol.po_line_id
4355 AND nvl(pll.draft_id,-1) = nvl(pol.draft_id,-1)
4356 AND prl.requisition_line_id = p_req_line_id_tbl(i)
4357 AND prl.group_line_id IS NULL
4358 AND prl.clm_base_line_num IS NULL
4359 AND Nvl(prl.clm_info_flag ,'N')='N'
4360 AND pol.group_line_id IS NULL
4361 AND pol.clm_base_line_num IS NULL
4362 AND Nvl(pol.clm_info_flag ,'N')='N'
4363 AND decode ( Nvl(prl.item_id,-1)
4364 , Nvl(pol.item_id,-1), 1, 0) = 1
4365 AND ((prl.item_id IS NOT NULL OR pol.item_id IS NOT NULL)
4366 OR decode(
4367 prl.item_description,
4368 pol.item_description, 1, 0) = 1)
4369 AND decode ( prl.item_revision
4370 , pol.item_revision, 1, 0) = 1
4371 AND decode ( prl.line_type_id
4372 , pol.line_type_id, 1, 0) = 1
4373 AND decode ( prl.preferred_grade
4374 , pol.preferred_grade, 1, 0) = 1
4375 AND decode ( prl.unit_meas_lookup_code
4376 , pol.unit_meas_lookup_code, 1, 0) = 1
4377 AND decode ( prl.transaction_reason_code
4378 , pol.transaction_reason_code, 1, 0) = 1
4379 AND decode ( prl.supplier_ref_number
4380 , pol.supplier_ref_number, 1, 0) = 1
4381 AND ( ( l_need_by_grouping_profile = 'N' )
4382 OR ( decode ( trunc(prl.need_by_date,'MI')
4383 , trunc(pll.need_by_date,'MI'), 1, 0) = 1 ) )
4384 AND ( ( l_ship_to_grouping_profile = 'N' )
4385 OR ( decode ( prl.destination_organization_id
4386 , pll.ship_to_organization_id, 1, 0) = 1 ) )
4387 AND ( ( prl.document_type_code <> 'CONTRACT' )
4388 OR ( decode ( prl.blanket_po_header_id
4389 , pol.contract_id, 1, 0) = 1 ) )
4390 AND src_line.po_header_id (+) = prl.blanket_po_header_id
4391 AND src_line.line_num (+) = prl.blanket_po_line_num
4392 AND ( ( p_builder_agreement_id IS NOT NULL )
4393 OR ( ( decode ( prl.blanket_po_header_id
4394 , pol.from_header_id, 1, 0) = 1 )
4395 AND ( decode ( src_line.po_line_id
4396 , pol.from_line_id, 1, 0) = 1 ) ) )
4397 AND nvl(pol.cancel_flag, 'N') <> 'Y'
4398 AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
4399 AND nvl(pll.consigned_flag, 'N') = p_consigned_flag_tbl(i);
4400
4401 d_position := 30;
4402
4403 -- Clean up GT table and return the results into the output tables.
4404 --
4405
4406 DELETE FROM po_session_gt
4407 WHERE key = l_key
4408 RETURNING num1, num2, char1
4409 BULK COLLECT INTO x_req_line_id_tbl, x_po_line_num_tbl, x_po_line_num_disp_tbl;
4410
4411 IF PO_LOG.d_proc THEN
4412 PO_LOG.proc_end(d_mod,'x_req_line_id_tbl',x_req_line_id_tbl);
4413 PO_LOG.proc_end(d_mod,'x_po_line_num_tbl',x_po_line_num_tbl);
4414 PO_LOG.proc_end(d_mod,'x_po_line_num_disp_tbl',x_po_line_num_disp_tbl);
4415 END IF;
4416
4417 EXCEPTION
4418
4419 WHEN OTHERS THEN
4420 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
4421 RAISE;
4422
4423 END clm_match_add_to_po_lines;
4424
4425 -------------------------------------------------------------------------------
4426
4427 --Start of Comments
4428 --Name: find_matching_builder_line_num
4429 --Pre-reqs: None
4430 --Modifies:
4431 --Locks:
4432 -- None
4433 --Function:
4434 -- Finds a matching line in the input table with index less than the
4435 -- given current index and its PO Line Number column populated.
4436 --Parameters:
4437 --IN:
4438 --p_current_index
4439 -- The index in the nested table of the requisition line to match. The
4440 -- search will be conducted only for indices less than this value.
4441 --p_req_line_id_tbl
4442 -- Nested table of requisition line IDs.
4443 --p_po_line_num_tbl
4444 -- Nested table of PO line numbers.
4445 --p_builder_agreement_id
4446 -- Global Agreement ID specified in the Doc Builder.
4447 --Returns:
4448 -- PO line number of the matching requisition line.
4449 --Notes:
4450 -- N/A
4451 --Testing:
4452 -- N/A
4453 --End of Comments
4454 -------------------------------------------------------------------------------
4455 FUNCTION find_matching_builder_line_num
4456 (
4457 p_current_index IN NUMBER
4458 , p_req_line_id_tbl IN PO_TBL_NUMBER
4459 , p_po_line_num_tbl IN PO_TBL_NUMBER
4460 , p_builder_agreement_id IN NUMBER
4461 )
4462 RETURN NUMBER
4463 IS
4464 d_mod CONSTANT VARCHAR2(100) := D_find_matching_builder_line;
4465 d_position NUMBER := 0;
4466
4467 BEGIN
4468
4469 -- Loop from beginning of the Doc Builder up to (but not including)
4470 -- the current line to look for a match.
4471 --
4472 FOR i IN 1..(p_current_index - 1)
4473 LOOP
4474 IF ( ( req_lines_match ( p_builder_agreement_id
4475 , p_req_line_id_tbl(p_current_index)
4476 , p_req_line_id_tbl(i) )
4477 )
4478 AND ( p_po_line_num_tbl(i) IS NOT NULL ) )
4479 THEN
4480 return (p_po_line_num_tbl(i)); -- return PO line num
4481 END IF;
4482
4483 END LOOP;
4484
4485 return (NULL); -- if loop completes, no matching line was found
4486
4487 EXCEPTION
4488
4489 WHEN OTHERS THEN
4490 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
4491 RAISE;
4492
4493 END find_matching_builder_line_num;
4494
4495 -------------------------------------------------------------------------------
4496 --Start of Comments
4497 --Name: req_lines_match
4498 --Pre-reqs: None
4499 --Modifies:
4500 --Locks:
4501 -- None
4502 --Function:
4503 -- Determines if two requisition lines can be grouped.
4504 --Parameters:
4505 --IN:
4506 --p_agreement_id
4507 -- The ID of the Global Agreement for which the PO will be created.
4508 --p_req_line_id_1
4509 -- First requisition line to compare.
4510 --p_req_line_id_2
4511 -- Second requisition line to compare.
4512 --Returns:
4513 -- TRUE if all pertinent attributes match; FALSE otherwise.
4514 --Notes:
4515 -- N/A
4516 --Testing:
4517 -- N/A
4518 --End of Comments
4519 -------------------------------------------------------------------------------
4520 FUNCTION req_lines_match
4521 (
4522 p_agreement_id IN NUMBER
4523 , p_req_line_id_1 IN NUMBER
4524 , p_req_line_id_2 IN NUMBER
4525 )
4526 RETURN BOOLEAN
4527 IS
4528 l_message_code VARCHAR2(30);
4529 l_token_name VARCHAR2(30);
4530 l_token_value VARCHAR2(2000);
4531
4532 l_result BOOLEAN;
4533
4534 d_mod CONSTANT VARCHAR2(100) := D_req_lines_match;
4535 d_position NUMBER := 0;
4536
4537 BEGIN
4538
4539 IF PO_LOG.d_proc THEN
4540 PO_LOG.proc_begin(d_mod,'p_agreement_id',p_agreement_id);
4541 PO_LOG.proc_begin(d_mod,'p_req_line_id_1',p_req_line_id_1);
4542 PO_LOG.proc_begin(d_mod,'p_req_line_id_2',p_req_line_id_2);
4543 END IF;
4544
4545 -- Make a call to lines_match to determine if all relevant attributes
4546 -- match. Note that we do not need Supplier/Site since we do not need
4547 -- to derive the Consigned Flag when comparing two req lines.
4548 --
4549 lines_match ( p_agreement_id => p_agreement_id
4550 , p_supplier_id => null
4551 , p_site_id => null
4552 , p_req_line_id => p_req_line_id_1
4553 , p_req_line_id_to_compare => p_req_line_id_2
4554 , p_po_line_id_to_compare => null
4555 , p_draft_id => null /* Autocreate grouping...p_draft_id created for the 'add to mods' flow. From req_lines_match this will always be null as p_po_line_id_to_compare is null too */
4556 , p_relax_item_desc_check => 'N' -- Autocreate grouping - this will be always 'N' from req_lines_match
4557 , x_message_code => l_message_code
4558 , x_token_name => l_token_name
4559 , x_token_value => l_token_value
4560 );
4561 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'l_message_code',l_message_code); END IF;
4562
4563 IF ( l_message_code IS NULL )
4564 THEN
4565 l_result := TRUE;
4566 ELSE
4567 l_result := FALSE;
4568 END IF;
4569
4570 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,l_result); END IF;
4571
4572 return (l_result);
4573
4574 EXCEPTION
4575
4576 WHEN OTHERS THEN
4577 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
4578 RAISE;
4579
4580 END req_lines_match;
4581
4582 -------------------------------------------------------------------------------
4583 --Start of Comments
4584 --Name: get_max_po_line_num
4585 --Pre-reqs: None
4586 --Modifies:
4587 --Locks:
4588 -- None
4589 --Function:
4590 -- Retrieves the maximum PO line number value from either the input
4591 -- PO or the Document Builder.
4592 --Parameters:
4593 --IN:
4594 --p_po_line_num_tbl
4595 -- Table of PO line numbers to check for maximum.
4596 --p_po_header_id
4597 -- ID of PO to check for maximum line number.
4598 --Returns:
4599 -- Maximum PO line number between the input PO and PO line number table.
4600 --Notes:
4601 -- N/A
4602 --Testing:
4603 -- N/A
4604 --End of Comments
4605 -------------------------------------------------------------------------------
4606 FUNCTION get_max_po_line_num
4607 (
4608 p_po_line_num_tbl IN PO_TBL_NUMBER
4609 , p_po_header_id IN NUMBER := NULL
4610 , p_draft_id IN NUMBER := -1
4611 )
4612 RETURN NUMBER
4613 IS
4614 x_max_po_line_num NUMBER := 0;
4615
4616 d_mod CONSTANT VARCHAR2(100) := D_get_max_po_line_num;
4617 d_position NUMBER := 0;
4618 --Removing hard coding
4619 --l_draft_id NUMBER := -1;
4620 l_draft_id NUMBER := p_draft_id;
4621
4622 BEGIN
4623
4624 IF PO_LOG.d_proc THEN
4625 PO_LOG.proc_begin(d_mod,'p_po_line_num_tbl',p_po_line_num_tbl);
4626 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
4627 END IF;
4628
4629 -- Find max line number for the input PO.
4630 --
4631 IF ( p_po_header_id IS NOT NULL )
4632 THEN
4633
4634 SELECT nvl(max(line_num), 0)
4635 INTO x_max_po_line_num
4636 FROM po_lines_merge_v
4637 WHERE po_header_id = p_po_header_id;
4638 --AND nvl(draft_id,-1) = nvl(l_draft_id,-1);
4639
4640 END IF;
4641
4642 -- Find max line number in the nested table of line numbers.
4643 --
4644 FOR i IN 1..p_po_line_num_tbl.COUNT
4645 LOOP
4646 IF ( p_po_line_num_tbl(i) > x_max_po_line_num )
4647 THEN
4648 x_max_po_line_num := p_po_line_num_tbl(i);
4649 END IF;
4650 END LOOP;
4651
4652 -- Return max PO line number.
4653
4654 return (x_max_po_line_num);
4655
4656 EXCEPTION
4657
4658 WHEN OTHERS THEN
4659 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
4660 RAISE;
4661
4662 END get_max_po_line_num;
4663
4664 -------------------------------------------------------------------------------
4665 --Start of Comments
4666 --Name: get_max_po_line_num CLM number
4667 --Pre-reqs: None
4668 --Modifies:
4669 --Locks:
4670 -- None
4671 --Function:
4672 -- Retrieves the maximum PO line number value from either the input
4673 -- PO or the Document Builder.
4674 --Parameters:
4675 --IN:
4676 --p_po_line_num_tbl
4677 -- Table of PO line numbers to check for maximum.
4678 --p_po_header_id
4679 -- ID of PO to check for maximum line number.
4680 --Returns:
4681 -- Maximum PO line number between the input PO and PO line number table.
4682 --Notes:
4683 -- N/A
4684 --Testing:
4685 -- N/A
4686 --End of Comments
4687 -------------------------------------------------------------------------------
4688 FUNCTION get_max_clm_po_line_num
4689 (
4690 p_po_line_num_tbl IN PO_TBL_VARCHAR100
4691 , p_po_header_id IN NUMBER := NULL
4692 , p_draft_id IN NUMBER := -1
4693 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
4694 )
4695 RETURN VARCHAR2
4696 IS
4697 x_max_po_line_num VARCHAR2(100) := '0000';
4698
4699 d_mod CONSTANT VARCHAR2(100) := D_get_max_clm_po_line_num;
4700 d_position NUMBER := 0;
4701
4702 BEGIN
4703
4704 IF PO_LOG.d_proc THEN
4705 PO_LOG.proc_begin(d_mod,'p_po_line_num_tbl',p_po_line_num_tbl);
4706 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
4707 END IF;
4708
4709 -- Find max CLIN number for the input PO.
4710 --
4711 IF ( p_po_header_id IS NOT NULL )
4712 THEN
4713 SELECT nvl(max(line_num_display), '0000')
4714 INTO x_max_po_line_num
4715 FROM po_lines_merge_v
4716 WHERE po_header_id = p_po_header_id
4717 --AND draft_id = p_draft_id
4718 and group_line_id is null
4719 and CLM_EXHIBIT_NAME IS null; --Bug 16572476
4720 END IF;
4721
4722 -- Find max CLIN number in the nested table of line numbers.
4723 --
4724 FOR i IN 1..p_po_line_num_tbl.COUNT
4725 LOOP
4726 -- As CLINs will have only maximum of 4 chars.
4727 /* Bug: 16572476
4728 In case of PAR ELINs, when the ELIN line is selected in DWB.
4729 It will be considered to find out the max clin number. It should
4730 not be considered. So skipping ELIN lines by finding out if the line number
4731 is having characters or not. ELIN Numbers will have characters
4732 */
4733 IF ((TRIM(TRANSLATE(p_po_line_num_tbl(i),'+-.0123456789',' ')) IS NULL) -- Check whether it is a number or char
4734 AND p_po_line_num_tbl(i) > x_max_po_line_num and p_req_group_line_id_tbl(i) is null)
4735 THEN
4736 x_max_po_line_num := p_po_line_num_tbl(i);
4737 END IF;
4738 END LOOP;
4739
4740 -- Return max PO line number.
4741 --
4742 return (x_max_po_line_num);
4743
4744 EXCEPTION
4745
4746 WHEN OTHERS THEN
4747 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
4748 RAISE;
4749
4750 END get_max_clm_po_line_num;
4751
4752 -------------------------------------------------------------------------------
4753 --Start of Comments
4754 --Name: get_max_po_line_num CLM number
4755 --Pre-reqs: None
4756 --Modifies:
4757 --Locks:
4758 -- None
4759 --Function:
4760 -- Retrieves the maximum PO line number value from either the input
4761 -- PO or the Document Builder.
4762 --Parameters:
4763 --IN:
4764 --p_po_line_num_tbl
4765 -- Table of PO line numbers to check for maximum.
4766 --p_po_header_id
4767 -- ID of PO to check for maximum line number.
4768 --Returns:
4769 -- Maximum PO line number between the input PO and PO line number table.
4770 --Notes:
4771 -- N/A
4772 --Testing:
4773 -- N/A
4774 --End of Comments
4775 -------------------------------------------------------------------------------
4776 FUNCTION get_max_clm_sol_line_num
4777 (
4778 p_neg_line_num_tbl IN PO_TBL_VARCHAR100
4779 , p_draft_id IN NUMBER := NULL
4780 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
4781 )
4782 RETURN VARCHAR2
4783 IS
4784 x_max_po_line_num VARCHAR2(100) := '0000';
4785
4786 d_mod CONSTANT VARCHAR2(100) := D_get_max_clm_po_line_num;
4787 d_position NUMBER := 0;
4788
4789 BEGIN
4790
4791 IF PO_LOG.d_proc THEN
4792 PO_LOG.proc_begin(d_mod,'p_po_line_num_tbl',p_neg_line_num_tbl);
4793 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_draft_id);
4794 END IF;
4795
4796 -- Find max CLIN number for the input PO.
4797 --
4798 IF ( p_draft_id IS NOT NULL )
4799 THEN
4800 SELECT nvl(max(line_num_display), '0000')
4801 INTO x_max_po_line_num
4802 FROM pon_auction_item_prices_all
4803 WHERE auction_header_id = p_draft_id
4804 and group_line_id is null;
4805 END IF;
4806
4807 -- Find max CLIN number in the nested table of line numbers.
4808 --
4809 FOR i IN 1..p_neg_line_num_tbl.COUNT
4810 LOOP
4811 -- As CLINs will have only maximum of 4 chars.
4812 IF ( p_neg_line_num_tbl(i) > x_max_po_line_num and p_req_group_line_id_tbl(i) is null)
4813 THEN
4814 x_max_po_line_num := p_neg_line_num_tbl(i);
4815 END IF;
4816 END LOOP;
4817
4818 -- Return max PO line number.
4819 --
4820 return (x_max_po_line_num);
4821
4822 EXCEPTION
4823
4824 WHEN OTHERS THEN
4825 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
4826 RAISE;
4827
4828 END get_max_clm_sol_line_num ;
4829
4830 -------------------------------------------------------------------------------
4831 --Start of Comments
4832 --Name: get_consigned_flag_tbl
4833 --Pre-reqs: None
4834 --Modifies:
4835 --Locks:
4836 -- None
4837 --Function:
4838 -- Constructs a table of the Consigned Flags corresponding to each
4839 -- requisition line in p_req_line_id_tbl.
4840 --Parameters:
4841 --IN:
4842 --p_req_line_id_tbl
4843 -- Table of requisition lines to find the Consigned Flag for.
4844 --p_builder_org_id
4845 -- ID of the operating unit in which the PO will be created.
4846 --p_builder_supplier_id
4847 -- The ID of the Supplier for which the PO will be created.
4848 --p_builder_site_id
4849 -- The ID of the Site for which the PO will be created.
4850 --Returns:
4851 -- Table of Consigned Flags corresponding each requisition line in
4852 -- p_req_line_id_tbl.
4853 --Notes:
4854 -- N/A
4855 --Testing:
4856 -- N/A
4857 --End of Comments
4858 -------------------------------------------------------------------------------
4859 FUNCTION get_consigned_flag_tbl
4860 (
4861 p_req_line_id_tbl IN PO_TBL_NUMBER
4862 , p_builder_org_id IN NUMBER
4863 , p_builder_supplier_id IN NUMBER
4864 , p_builder_site_id IN NUMBER
4865 )
4866 RETURN PO_TBL_VARCHAR1
4867 IS
4868 l_item_id NUMBER;
4869 x_consigned_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
4870
4871 d_mod CONSTANT VARCHAR2(100) := D_get_consigned_flag_tbl;
4872 d_position NUMBER := 0;
4873
4874 BEGIN
4875
4876 IF PO_LOG.d_proc THEN
4877 PO_LOG.proc_begin(d_mod,'p_req_line_id_tbl',p_req_line_id_tbl);
4878 PO_LOG.proc_begin(d_mod,'p_builder_org_id',p_builder_org_id);
4879 PO_LOG.proc_begin(d_mod,'p_builder_supplier_id',p_builder_supplier_id);
4880 PO_LOG.proc_begin(d_mod,'p_builder_site_id',p_builder_site_id);
4881 END IF;
4882
4883 x_consigned_flag_tbl.EXTEND(p_req_line_id_tbl.COUNT);
4884
4885 FOR i IN 1..p_req_line_id_tbl.COUNT LOOP
4886
4887 SELECT item_id
4888 INTO l_item_id
4889 FROM po_requisition_lines_all
4890 WHERE requisition_line_id = p_req_line_id_tbl(i);
4891
4892 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,d_position,'l_item_id',l_item_id); END IF;
4893
4894 x_consigned_flag_tbl(i) := PO_THIRD_PARTY_STOCK_GRP.get_consigned_flag
4895 ( p_builder_org_id
4896 , l_item_id
4897 , p_builder_supplier_id
4898 , p_builder_site_id
4899 ,NULL --Bug 5976612
4900 );
4901 /* Bug 5976612
4902 Added the 'NULL' parameter in the end for the inv org id.
4903 These changes are driven by the changes done to the function PO_THIRD_PARTY_STOCK_GRP.get_consigned_flag. */
4904
4905 END LOOP;
4906
4907 IF PO_LOG.d_proc THEN PO_LOG.proc_return(d_mod,x_consigned_flag_tbl); END IF;
4908
4909 return (x_consigned_flag_tbl);
4910
4911 EXCEPTION
4912
4913 WHEN OTHERS THEN
4914 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
4915 RAISE;
4916
4917 END get_consigned_flag_tbl;
4918
4919 -------------------------------------------------------------------------------
4920 --Start of Comments
4921 --Name: find_matching_po_line_num
4922 --Pre-reqs: None
4923 --Modifies:
4924 --Locks:
4925 -- None
4926 --Function:
4927 -- Finds a given ID in a table (p_comparison_tbl) and returns the
4928 -- corresponding value from p_po_line_num_tbl for that index.
4929 --Parameters:
4930 --IN:
4931 --p_req_line_id
4932 -- Requisition line ID to search for.
4933 --p_comparison_tbl
4934 -- Table of requisition line IDs.
4935 --p_po_line_num_tbl
4936 -- Table of PO line numbers corresponding to p_comparison_tbl.
4937 --Returns:
4938 -- Returns the PO line number at the index for which the comparison table
4939 -- matches the input req line ID. If the req line ID is not found in the
4940 -- comparison table, returns NULL.
4941 -- p_req_line_id_tbl.
4942 --Notes:
4943 -- N/A
4944 --Testing:
4945 -- N/A
4946 --End of Comments
4947 -------------------------------------------------------------------------------
4948 FUNCTION find_matching_po_line_num
4949 (
4950 p_req_line_id IN NUMBER
4951 , p_comparison_tbl IN PO_TBL_NUMBER
4952 , p_po_line_num_tbl IN PO_TBL_NUMBER
4953 )
4954 RETURN NUMBER
4955 IS
4956 d_mod CONSTANT VARCHAR2(100) := D_find_matching_po_line_num;
4957 d_position NUMBER := 0;
4958
4959 BEGIN
4960
4961 FOR i IN 1..p_comparison_tbl.COUNT
4962 LOOP
4963 IF ( p_req_line_id = p_comparison_tbl(i) )
4964 THEN
4965 return (p_po_line_num_tbl(i));
4966 END IF;
4967 END LOOP;
4968
4969 return (null);
4970
4971 EXCEPTION
4972
4973 WHEN OTHERS THEN
4974 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
4975 RAISE;
4976
4977 END find_matching_po_line_num;
4978
4979 -------------------------------------------------------------------------------
4980 --Start of Comments
4981 --Name: default_info_slin_numbers
4982 --Pre-reqs: None
4983 --Modifies:
4984 --Locks:
4985 -- None
4986 --Function:
4987 -- Defaults the Info Slin Numbers based on the provided Priced CLIN
4988 -- line num and header id.
4989 --Parameters:
4990 --IN:
4991 --p_req_line_id
4992 -- Requisition line ID
4993 --p_req_group_line_id_tbl
4994 -- Table of requisition Group Line IDs.
4995 --p_po_line_num_tbl
4996 -- Table of PO CLM line numbers
4997 --p_req_group_line_id_tbl
4998 -- Table of requisition Group Line IDs.
4999 --p_po_line_num
5000 -- Po Line Number
5001 --p_po_header_id
5002 -- Po Header Id
5003 --Returns:
5004 -- Returns the CLM Po Line Numbers.
5005 --Notes:
5006 -- N/A
5007 --Testing:
5008 -- N/A
5009 --End of Comments
5010 -------------------------------------------------------------------------------
5011
5012 FUNCTION default_info_slin_numbers (
5013 p_req_line_id IN NUMBER,
5014 p_req_group_line_id_tbl IN PO_TBL_NUMBER,
5015 p_po_line_num_tbl IN PO_TBL_VARCHAR100,
5016 p_po_line_num IN NUMBER,
5017 p_po_header_id IN NUMBER)
5018 RETURN PO_TBL_VARCHAR100
5019 IS
5020 l_is_info_fund_req_line VARCHAR2(1) := 'N';
5021 l_is_option_line VARCHAR2(1) :='N';
5022 l_max_slin_num VARCHAR2(10) := NULL;
5023 l_po_line_num_tbl PO_TBL_VARCHAR100 := p_po_line_num_tbl;
5024 l_current_slin VARCHAR2(10) := NULL;
5025 l_po_line_id NUMBER;
5026 d_mod CONSTANT VARCHAR2(100) := D_default_info_slin_numbers;
5027 d_position NUMBER := 0;
5028 BEGIN
5029
5030 BEGIN
5031 SELECT 'Y' INTO l_is_info_fund_req_line
5032 FROM po_requisition_lines_all PRL
5033 WHERE
5034 prl.REQUISITION_LINE_ID = p_req_line_id and
5035 nvl(prl.clm_info_flag, 'N') = 'N' AND prl.group_line_id IS NULL AND EXISTS (SELECT 1
5036 FROM po_req_distributions_all prd1 WHERE prd1.requisition_line_id = prl.REQUISITION_LINE_ID
5037 AND prd1.info_line_id IS NOT NULL AND ROWNUM = 1);
5038 EXCEPTION
5039 WHEN No_Data_Found THEN
5040 RETURN l_po_line_num_tbl;
5041 END;
5042
5043 BEGIN
5044 SELECT po_line_id, Decode(clm_option_indicator, 'O', 'Y', 'N')
5045 INTO l_po_line_id, l_is_option_line
5046 FROM po_lines_merge_v
5047 WHERE po_header_id = p_po_header_id AND
5048 line_num = p_po_line_num AND
5049 draft_id <> -1;
5050 EXCEPTION
5051 WHEN No_Data_Found THEN
5052 RETURN l_po_line_num_tbl;
5053 END;
5054
5055 IF l_is_option_line ='Y' THEN
5056 FOR i IN 1..p_req_group_line_id_tbl.Count LOOP
5057 IF (p_req_group_line_id_tbl(i) IS NOT NULL AND p_req_group_line_id_tbl(i) = p_req_line_id) THEN
5058 l_po_line_num_tbl(i) := NULL;
5059 END IF;
5060 END LOOP;
5061 ELSE
5062 SELECT Max(line_num_display)
5063 INTO l_max_slin_num
5064 FROM po_lines_merge_v
5065 WHERE group_line_id = l_po_line_id AND
5066 draft_id <> -1 AND
5067 Nvl(clm_info_flag, 'N') = 'Y' AND
5068 group_line_id IS NOT NULL;
5069
5070 l_current_slin := l_max_slin_num;
5071
5072 FOR i IN 1..p_req_group_line_id_tbl.Count LOOP
5073 IF (p_req_group_line_id_tbl(i) IS NOT NULL AND p_req_group_line_id_tbl(i) = p_req_line_id) THEN
5074 l_current_slin := PON_CLO_RENUMBER_PKG.INCREMENT_INFO_SLIN_NUMBER(l_current_slin);
5075 l_po_line_num_tbl(i) := l_current_slin;
5076 END IF;
5077 END LOOP;
5078 END IF;
5079
5080 RETURN l_po_line_num_tbl;
5081 EXCEPTION
5082
5083 WHEN OTHERS THEN
5084 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,d_position); END IF;
5085 RAISE;
5086 END default_info_slin_numbers;
5087
5088 -------------------------------------------------------------------------------
5089 --Start of Comments
5090 --Name: check_mod_lock_availability
5091 --Function:
5092 -- Checks if a lock can be procured in case of adding a PR lines to existing Modification, since concurrent mods are feasible.
5093 --Parameters:
5094 --IN:
5095 --p_po_line_number_tbl
5096 -- The table of PO line numbers for all the requistion lines in the document
5097 -- builder.
5098 --p_add_to_po_header_id
5099 -- The header ID of the PO for the Add To PO case. If New PO, this parameter
5100 -- will be null.
5101 --p_draft_id
5102 -- The draft ID of the mod for the Add To PO/Mod case. If Add to PO case, this will be -1
5103 --OUT:
5104 --x_message_code_tbl
5105 -- Table of error message codes corresponding to each of the input requisition
5106 -- lines.
5107 --x_token_name_tbl
5108 -- Table of error message token names corresponding to each of the input
5109 -- requisition lines.
5110 --x_token_value_tbl
5111 -- Table of error message token values corresponding to each of the input
5112 -- requisition lines.
5113 --End of Comments
5114 -------------------------------------------------------------------------------
5115 PROCEDURE check_mod_lock_availability(
5116 p_po_line_number_tbl IN PO_TBL_NUMBER,
5117 p_add_to_po_header_id IN NUMBER,
5118 p_draft_id IN NUMBER, --Autocreate grouping
5119 x_message_code_tbl OUT NOCOPY PO_TBL_VARCHAR30,
5120 x_token_name_tbl OUT NOCOPY PO_TBL_VARCHAR30,
5121 x_token_value_tbl OUT NOCOPY PO_TBL_VARCHAR2000
5122 )
5123 IS
5124 d_mod CONSTANT VARCHAR2(100) := D_check_mod_lock_availability;
5125 d_position NUMBER := 0;
5126 l_lock_available VARCHAR2(1);
5127 l_num_lines NUMBER;
5128
5129 BEGIN
5130
5131 IF (PO_LOG.d_proc) THEN
5132 PO_LOG.proc_begin(d_mod,'p_po_line_number_tbl',p_po_line_number_tbl);
5133 PO_LOG.proc_begin(d_mod,'p_add_to_po_header_id',p_add_to_po_header_id);
5134 PO_LOG.proc_begin(d_mod,'p_draft_id',p_draft_id);
5135 END IF;
5136
5137 -- Get the number of lines being passed in.
5138 l_num_lines := p_po_line_number_tbl.COUNT;
5139 -- Initialize error message values to be returned.
5140 x_message_code_tbl := PO_TBL_VARCHAR30();
5141 x_message_code_tbl.EXTEND(l_num_lines);
5142 x_token_name_tbl := PO_TBL_VARCHAR30();
5143 x_token_name_tbl.EXTEND(l_num_lines);
5144 x_token_value_tbl := PO_TBL_VARCHAR2000();
5145 x_token_value_tbl.EXTEND(l_num_lines);
5146
5147
5148 /*We need to check for lock availability only when the action is 'Add PR line to Modifications'
5149 and if there is a req line that matches with a modification line. the condition p_add_to_po_header_id IS NOT NULL
5150 AND p_draft_id <> -1 identifies if the action is add PR line to mod */
5151
5152 IF p_add_to_po_header_id IS NOT NULL AND p_draft_id <> -1 THEN
5153 FOR i IN 1..p_po_line_number_tbl.COUNT LOOP
5154
5155 /*SQL to check if some other modification is already holding a full lock on this line*/
5156
5157 SELECT 'Y'
5158 INTO l_lock_available
5159 FROM dual
5160 WHERE NOT EXISTS (SELECT 'Another Mod has F lock'
5161 FROM po_entity_locks poel,
5162 po_lines_merge_v plm
5163 WHERE plm.po_line_id = poel.entity_pk1
5164 AND plm.po_header_id = p_add_to_po_header_id
5165 AND plm.draft_id = p_draft_id
5166 AND plm.line_num = p_po_line_number_tbl(i)
5167 AND poel.entity_name = 'PO_LINE'
5168 AND poel.lock_by_draft_id <> p_draft_id
5169 AND poel.lock_type = 'F');
5170
5171 /*set the message that lock is not available and exit the loop. Once a lock unavailability is identified, not necessary to check other lines*/
5172 IF NVL(l_lock_available, 'Y') <> 'Y' THEN
5173 x_message_code_tbl(i) := 'PO_ALL_LOCK_NOT_AVAIL';
5174 x_token_name_tbl(i) := null;
5175 x_token_value_tbl(i) := null;
5176 EXIT;
5177 END IF; --NVL(l_lock_available, 'Y') <> 'Y'
5178
5179 END LOOP; --FOR i IN 1..p_po_line_number_tbl.COUNT
5180 END IF; --IF p_add_to_po_header_id IS NOT NULL AND p_draft_id <> -1
5181
5182
5183 IF (PO_LOG.d_proc) THEN
5184 PO_LOG.proc_end(d_mod, 'x_message_code_tbl', x_message_code_tbl);
5185 PO_LOG.proc_end(d_mod, 'x_token_name_tbl', x_token_name_tbl);
5186 PO_LOG.proc_end(d_mod, 'x_token_value_tbl', x_token_value_tbl);
5187 END IF;
5188
5189 EXCEPTION
5190 WHEN OTHERS THEN
5191
5192 IF (PO_LOG.d_exc) THEN
5193 PO_LOG.exc(d_mod, d_position, 'An error occured in check_mod_lock_availability');
5194 END IF;
5195
5196 RAISE;
5197 END check_mod_lock_availability;
5198
5199
5200 PROCEDURE get_line_num_disp_for_clin
5201 ( p_po_line_num_tbl IN OUT NOCOPY PO_TBL_NUMBER
5202 , p_req_line_id_tbl IN PO_TBL_NUMBER
5203 , p_req_option_flag_tbl IN PO_TBL_VARCHAR1
5204 , p_draft_id IN NUMBER
5205 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
5206 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
5207 , p_add_to_po_header_id IN NUMBER
5208 , p_start_index IN NUMBER
5209 , p_end_index IN NUMBER
5210 , p_po_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
5211 )IS
5212
5213 d_position NUMBER := 0;
5214 d_mod CONSTANT VARCHAR2(100) := D_get_line_num_disp_for_clin;
5215
5216 l_max_line_num_disp VARCHAR2(100);
5217 l_next_clin_num_disp VARCHAR2(100);
5218 flag NUMBER :=0;
5219 l_action_tbl PO_TBL_VARCHAR5 := PO_TBL_VARCHAR5();
5220
5221 BEGIN
5222
5223 -- Get the max line number from either the PO or the other
5224 -- lines in the Document Builder.
5225 IF (PO_LOG.d_proc) THEN
5226 PO_LOG.proc_begin(d_mod,'p_po_line_num_disp_tbl',p_po_line_num_disp_tbl);
5227 PO_LOG.proc_begin(d_mod,'p_po_line_num_tbl',p_po_line_num_tbl);
5228 END IF;
5229
5230 l_max_line_num_disp := get_max_clm_po_line_num ( p_po_line_num_disp_tbl
5231 , p_add_to_po_header_id
5232 , p_draft_id --Autocreate grouping
5233 ,p_req_group_line_id_tbl);
5234
5235 IF PO_LOG.d_stmt THEN
5236 PO_LOG.stmt(d_mod,d_position,'l_max_line_num_disp',l_max_line_num_disp);
5237
5238 END IF;
5239
5240 -- Get the next line num display for a CLIN
5241 l_next_clin_num_disp := PON_CLO_RENUMBER_PKG.INCREMENT_CLIN_NUMBER(l_max_line_num_disp);
5242
5243 l_action_tbl := PO_AUTOCREATE_GROUPING_PVT.get_line_action_tbl(p_po_line_num_tbl,
5244 p_add_to_po_header_id,
5245 p_draft_id);
5246
5247 d_position := 10;
5248
5249 IF (PO_LOG.d_proc) THEN
5250 PO_LOG.proc_begin(d_mod,'l_next_clin_num_disp',l_action_tbl);
5251 PO_LOG.proc_begin(d_mod,'l_action_tbl',l_action_tbl);
5252 END IF;
5253
5254
5255 --Loop through every clin
5256 FOR i IN p_start_index..p_end_index
5257 LOOP
5258 IF (p_req_group_line_id_tbl(i) IS NULL /*CLIN*/)
5259 --AND (NVL(p_req_clm_info_flag_tbl(i),'N') = 'N' /*not an info line*/)
5260 THEN
5261
5262 flag := 0;
5263 --Loop to check whether line num for clin has occurred before.
5264 --If it has occurred before flag sets to 1
5265 FOR y IN p_start_index..(i-1)
5266 LOOP
5267 IF p_po_line_num_tbl(i) = p_po_line_num_tbl(y)
5268 THEN
5269 flag := 1;
5270 EXIT WHEN flag = 1;
5271 END IF;
5272 END LOOP;
5273
5274 IF (flag =0 OR i = p_start_index) AND l_action_tbl(i)='NEW'
5275 THEN
5276 --Stamp the line num display for the clin to next available line num disp
5277 p_po_line_num_disp_tbl(i) := l_next_clin_num_disp;
5278
5279 FOR x IN p_start_index..p_end_index
5280 LOOP
5281
5282 IF p_po_line_num_tbl(i) = p_po_line_num_tbl(x)
5283 THEN
5284
5285 p_po_line_num_disp_tbl(x) := p_po_line_num_disp_tbl(i);
5286 END IF;
5287
5288 END LOOP;
5289
5290 l_next_clin_num_disp := PON_CLO_RENUMBER_PKG.INCREMENT_CLIN_NUMBER(l_next_clin_num_disp);
5291
5292 END IF;
5293 END IF;
5294 END LOOP;
5295
5296 IF PO_LOG.d_proc THEN
5297 PO_LOG.proc_end(d_mod,'p_po_line_num_disp_tbl',p_po_line_num_disp_tbl);
5298 END IF;
5299
5300
5301 END get_line_num_disp_for_clin;
5302
5303 PROCEDURE get_sol_line_num_disp_for_clin
5304 ( p_neg_line_num_tbl IN OUT NOCOPY PO_TBL_NUMBER
5305 , p_req_line_id_tbl IN PO_TBL_NUMBER
5306 , p_req_option_flag_tbl IN PO_TBL_VARCHAR1
5307 , p_draft_id IN NUMBER
5308 , p_req_group_line_id_tbl IN PO_TBL_NUMBER
5309 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
5310 , p_add_to_neg_header_id IN NUMBER
5311 , p_start_index IN NUMBER
5312 , p_end_index IN NUMBER
5313 , p_neg_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
5314 )IS
5315
5316 d_position NUMBER := 0;
5317 d_mod CONSTANT VARCHAR2(100) := D_get_line_num_disp_for_clin;
5318
5319 l_max_line_num_disp VARCHAR2(100);
5320 l_next_clin_num_disp VARCHAR2(100);
5321 flag NUMBER :=0;
5322 l_action_tbl PO_TBL_VARCHAR5 := PO_TBL_VARCHAR5();
5323
5324 BEGIN
5325
5326 -- Get the max line number from either the PO or the other
5327 -- lines in the Document Builder.
5328 IF (PO_LOG.d_proc) THEN
5329 PO_LOG.proc_begin(d_mod,'p_neg_line_num_disp_tbl',p_neg_line_num_disp_tbl);
5330 PO_LOG.proc_begin(d_mod,'p_neg_line_num_tbl',p_neg_line_num_tbl);
5331 END IF;
5332
5333 l_max_line_num_disp := get_max_clm_sol_line_num ( p_neg_line_num_disp_tbl
5334 , p_draft_id
5335 ,p_req_group_line_id_tbl);
5336
5337 IF PO_LOG.d_stmt THEN
5338 PO_LOG.stmt(d_mod,d_position,'l_max_line_num_disp',l_max_line_num_disp);
5339
5340 END IF;
5341
5342 -- Get the next line num display for a CLIN
5343 l_next_clin_num_disp := PON_CLO_RENUMBER_PKG.INCREMENT_CLIN_NUMBER(l_max_line_num_disp);
5344
5345 l_action_tbl := PO_AUTOCREATE_GROUPING_PVT.get_line_action_tbl(p_neg_line_num_tbl,
5346 p_add_to_neg_header_id,
5347 p_draft_id);
5348
5349 d_position := 10;
5350
5351 IF (PO_LOG.d_proc) THEN
5352 PO_LOG.proc_begin(d_mod,'l_next_clin_num_disp',l_action_tbl);
5353 PO_LOG.proc_begin(d_mod,'l_action_tbl',l_action_tbl);
5354 END IF;
5355
5356
5357 --Loop through every clin
5358 FOR i IN p_start_index..p_end_index
5359 LOOP
5360 IF (p_req_group_line_id_tbl(i) IS NULL /*CLIN*/)
5361 --AND (NVL(p_req_clm_info_flag_tbl(i),'N') = 'N' /*not an info line*/)
5362 THEN
5363
5364 flag := 0;
5365 --Loop to check whether line num for clin has occurred before.
5366 --If it has occurred before flag sets to 1
5367 FOR y IN p_start_index..(i-1)
5368 LOOP
5369 IF p_neg_line_num_tbl(i) = p_neg_line_num_tbl(y)
5370 THEN
5371 flag := 1;
5372 EXIT WHEN flag = 1;
5373 END IF;
5374 END LOOP;
5375
5376 IF (flag =0 OR i = p_start_index) AND l_action_tbl(i)='NEW'
5377 THEN
5378 --Stamp the line num display for the clin to next available line num disp
5379 p_neg_line_num_disp_tbl(i) := l_next_clin_num_disp;
5380
5381 FOR x IN p_start_index..p_end_index
5382 LOOP
5383
5384 IF p_neg_line_num_tbl(i) = p_neg_line_num_tbl(x)
5385 THEN
5386
5387 p_neg_line_num_disp_tbl(x) := p_neg_line_num_disp_tbl(i);
5388 END IF;
5389
5390 END LOOP;
5391
5392 l_next_clin_num_disp := PON_CLO_RENUMBER_PKG.INCREMENT_CLIN_NUMBER(l_next_clin_num_disp);
5393
5394 END IF;
5395 END IF;
5396 END LOOP;
5397
5398 IF PO_LOG.d_proc THEN
5399 PO_LOG.proc_end(d_mod,'p_po_line_num_disp_tbl',p_neg_line_num_disp_tbl);
5400 END IF;
5401
5402
5403 END get_sol_line_num_disp_for_clin;
5404
5405
5406 -------------------------------------------------------------------------------
5407
5408 Procedure RenumberSlins
5409 (
5410 p_source_line_id_tbl IN PO_TBL_NUMBER
5411 , p_source_group_line_id_tbl IN PO_TBL_NUMBER
5412 , p_po_line_num_tbl IN PO_TBL_NUMBER
5413 , p_po_line_num_disp_tbl IN OUT NOCOPY PO_TBL_VARCHAR100
5414 , p_req_clm_info_flag_tbl IN PO_TBL_VARCHAR1
5415 , p_renumber_flag IN PO_TBL_VARCHAR1
5416 , p_start_index IN NUMBER default null
5417 , p_end_index IN NUMBER default null
5418 , p_add_to_po_header_id IN NUMBER default null
5419 , p_draft_id IN NUMBER default NULL
5420 , p_orig_line_num IN NUMBER DEFAULT -1
5421 , p_edit_line_num IN NUMBER DEFAULT -1
5422 )IS
5423
5424 d_position NUMBER := 0;
5425 d_mod CONSTANT VARCHAR2(100) := D_RenumberSlins;
5426 l_start_index NUMBER := p_start_index;
5427 l_end_index NUMBER := p_end_index;
5428 l_session_key NUMBER;
5429 l_action_tbl PO_TBL_VARCHAR5 := PO_TBL_VARCHAR5();
5430 l_next_slin_num VARCHAR2(100);
5431 l_next_info_slin_num VARCHAR2(100);
5432 l_next_priced_slin_num VARCHAR2(100);
5433 l_renumber_flag PO_TBL_VARCHAR1 := p_renumber_flag;
5434
5435 BEGIN
5436
5437 If p_start_index is null then
5438 l_start_index := 1;
5439 end if;
5440
5441 if p_end_index is null then
5442 l_end_index := p_source_line_id_tbl.Count;
5443 end if;
5444
5445 if l_end_index is null or l_end_index = 0 or l_end_index < l_start_index THEN
5446 return;
5447 end if;
5448
5449 IF (PO_LOG.d_proc) THEN
5450 PO_LOG.proc_begin(d_mod,'l_start_index',l_start_index);
5451 PO_LOG.proc_begin(d_mod,'l_end_index',l_end_index);
5452 PO_LOG.proc_begin(d_mod,'p_orig_line_num',p_orig_line_num);
5453 PO_LOG.proc_begin(d_mod,'p_edit_line_num',p_edit_line_num);
5454 END IF;
5455
5456
5457 p_po_line_num_disp_tbl.extend(l_end_index - l_start_index + 1);
5458 -- get the session key
5459 select PO_SESSION_GT_S.nextval into l_session_key from dual;
5460
5461 -- get the action whether the line is being added(match case) or new line
5462 l_action_tbl := PO_AUTOCREATE_GROUPING_PVT.get_line_action_tbl(p_po_line_num_tbl,
5463 p_add_to_po_header_id,
5464 p_draft_id);
5465
5466 IF PO_LOG.d_stmt THEN
5467 PO_LOG.stmt(d_mod,d_position,'l_session_key',l_session_key);
5468 PO_LOG.stmt(d_mod,d_position,'l_action_tbl',l_action_tbl);
5469 END IF;
5470
5471 /* Mapping for gt table
5472 num1 - source line id ( req line id or interface line id)
5473 num2 - group line id in the source
5474 num3 - internal po line numbers
5475 num4 - po line id
5476 char1 - display po line numbers
5477 char2 - action - adding to po line(matching case) or creating new po line.
5478 char3 - renumber flag - to be populated as Y for the clins whose info slins
5479 you want to renumber.
5480 char4 - next info slin display num for the clin in case of ADD action lines
5481 char5 - info line flag
5482 char6 - next priced slin display num for the clin in case of ADD action lines
5483 index_num1 - index, this is inserted to ensure correct order of retrieval
5484 */
5485
5486 -- now do the bulk insert
5487 -- When the line_num_display are edited on the details page
5488 IF p_orig_line_num <> -1 AND p_edit_line_num <> -1
5489 THEN
5490 -- Renumber flag needs to be set 'Y' only for po_line_num which match
5491 -- p_orig_line_num OR p_edit_line_num
5492 FOR i IN l_start_index..l_end_index LOOP
5493 IF p_po_line_num_tbl(i) = p_orig_line_num OR p_po_line_num_tbl(i) = p_edit_line_num THEN
5494 l_renumber_flag(i) := 'Y';
5495 END IF;
5496 END LOOP;
5497
5498 FOR i IN l_start_index..l_end_index LOOP
5499 insert into po_session_gt (key, num1, num2, num3, char1, char2, char3, char5, index_num1)
5500 values ( l_session_key,
5501 p_source_line_id_tbl(i)
5502 , p_source_group_line_id_tbl(i)
5503 , p_po_line_num_tbl(i)
5504 , p_po_line_num_disp_tbl(i)
5505 , l_action_tbl(i)
5506 , l_renumber_flag(i)
5507 , nvl(p_req_clm_info_flag_tbl(i), 'N')
5508 , i
5509 );
5510 END LOOP;
5511
5512 ELSE
5513 FOR i in l_start_index..l_end_index loop
5514 insert into po_session_gt (key, num1, num2, num3, char1, char2, char3, char5, index_num1)
5515 values ( l_session_key,
5516 p_source_line_id_tbl(i)
5517 , p_source_group_line_id_tbl(i)
5518 , p_po_line_num_tbl(i)
5519 , p_po_line_num_disp_tbl(i)
5520 , l_action_tbl(i)
5521 , nvl(p_renumber_flag(i), 'Y')
5522 , nvl(p_req_clm_info_flag_tbl(i), 'N')
5523 , i
5524 );
5525 END LOOP;
5526
5527 END IF;
5528
5529 -- this is for add case
5530 if p_add_to_po_header_id is not null THEN
5531
5532 --Setting the po_line_id of the clins where action is 'ADD'
5533 UPDATE po_session_gt
5534 SET num4 = (select po_line_id
5535 FROM PO_LINES_MERGE_V
5536 where PO_HEADER_ID = p_add_to_po_header_id
5537 AND LINE_NUM_DISPLAY = char1
5538 AND ROWNUM=1)
5539 where num2 is null -- group line id
5540 and char2 = 'ADD' -- action
5541 and char3 = 'Y' -- renumber flag
5542 and index_num1 between l_start_index and l_end_index
5543 and key = l_session_key;
5544
5545 update po_session_gt
5546 set
5547 char4 = PO_LINES_DRAFT_PVT.get_next_slin_num(num4, --po_line_id
5548 NULL,
5549 char1,--line_num_display
5550 'Y', --clmInfoFlag
5551 -9999),--Invalid Draft Id
5552 char6 = PO_LINES_DRAFT_PVT.get_next_slin_num(num4, --po_line_id
5553 NULL,
5554 char1,--line_num_display
5555 'N', --clmInfoFlag
5556 -9999)--Invalid Draft Id
5557
5558 where num2 is null -- group line id
5559 and char2 = 'ADD' -- action
5560 and char3 = 'Y' -- renumber flag
5561 and index_num1 between l_start_index and l_end_index
5562 and key = l_session_key;
5563 end if;
5564
5565 -- for new cases
5566 update po_session_gt
5567 set char4 = char1 || '01',
5568 char6 = char1 || 'AA'
5569 where num2 is null -- group line id
5570 and char2 = 'NEW' -- action
5571 and char3 = 'Y' -- renumber flag
5572 and index_num1 between l_start_index and l_end_index
5573 and key = l_session_key;
5574
5575 /*
5576 now , actual logic
5577 get distinct po line numbers (internal) in the given set for the priced clins
5578 (renumber flag = 'Y' and index_num1 between p_start_index and p_start_index)
5579 for each distinct po line num, loop through all slins, in the order of the
5580 index and assign next info slin
5581 */
5582
5583 for po_line_num in (select distinct num3, char4, char6 -- char4 is the next slin number, previous logic ensure that a given value of num3 will have same char4
5584 from po_session_gt
5585 where key = l_session_key
5586 and char3 = 'Y' -- renumber flag
5587 and index_num1 between l_start_index and l_end_index
5588 --and char5 = 'N' --priced
5589 and num2 is null -- clin
5590 )
5591 loop
5592 l_next_info_slin_num := po_line_num.char4;
5593 l_next_priced_slin_num := po_line_num.char6;
5594 -- loop through the list of slins pointing to clins with the same line number
5595 for rec in (select slins.num1, slins.char5 from po_session_gt clins, po_session_gt slins
5596 where clins.key = l_session_key
5597 and slins.key = l_session_key
5598 AND clins.num3 = po_line_num.num3 -- po line number
5599 and slins.num2 = clins.num1 -- slins.group line id = clins.line id
5600 order by slins.index_num1
5601 )
5602 LOOP
5603 IF rec.char5 = 'Y' THEN -- info slin
5604 l_next_slin_num := l_next_info_slin_num;
5605 l_next_info_slin_num := pon_clo_renumber_pkg.INCREMENT_INFO_SLIN_NUMBER(l_next_info_slin_num);
5606
5607 ELSE --priced slin
5608 l_next_slin_num := l_next_priced_slin_num;
5609 l_next_priced_slin_num := pon_clo_renumber_pkg.INCREMENT_PRICED_SLIN_NUMBER(l_next_priced_slin_num);
5610 END IF;
5611
5612 update po_session_gt
5613 set char1 = l_next_slin_num
5614 where key = l_session_key
5615 and num1 = rec.num1;
5616
5617 end loop;
5618
5619 end loop;
5620
5621 -- now select back in order
5622 select char1
5623 bulk collect into p_po_line_num_disp_tbl
5624 from po_session_gt
5625 where key = l_session_key
5626 order by index_num1; -- order is important
5627
5628 IF PO_LOG.d_proc THEN
5629 PO_LOG.proc_end(d_mod,'p_po_line_num_disp_tbl',p_po_line_num_disp_tbl);
5630 END IF;
5631
5632 end RenumberSlins;
5633
5634 -------------------------------------------------------------------------------
5635
5636 FUNCTION get_edited_po_line_num (
5637 p_draft_id IN NUMBER,
5638 p_add_to_po_header_id IN NUMBER,
5639 p_po_line_num_disp IN VARCHAR2)
5640 RETURN Number
5641 IS
5642
5643 l_line_num Number;
5644
5645 BEGIN
5646
5647 SELECT line_num
5648 INTO l_line_num
5649 FROM po_lines_merge_v
5650 WHERE nvl(draft_id,-1) = nvl(p_draft_id,-1)
5651 --Bug 13552726 : While creating new modification the draft_id was null
5652 --Hence the query returned no value. The line_num was not set and no
5653 --validations were fired.
5654 AND po_header_id = p_add_to_po_header_id
5655 AND line_num_display = p_po_line_num_disp;
5656
5657 RETURN l_line_num;
5658
5659 EXCEPTION
5660 WHEN No_Data_Found THEN
5661 l_line_num := NULL;
5662
5663 RETURN l_line_num;
5664
5665 END get_edited_po_line_num;
5666
5667 /*bug 13622501 start
5668 New procedure to update the po line num and po line num display based on the shipment selected during the 'ADD_FUNDS' case.*/
5669 -------------------------------------------------------------------------------
5670 --Start of Comments
5671 --Name: get_po_linenum_for_shipment
5672 --Function:
5673 --Derives the po_line_num and po_line_num_display for the shipment num selected in ADD_FUNDS case
5674 --Parameters:
5675 --IN:
5676 --p_po_shipment_id
5677 -- The line_location_id of the shipment to which the funds will be added.
5678 -- builder.
5679 --p_draft_id
5680 -- The draft ID of the mod for the Add funds To PO/Mod case. If Add funds to PO case, this will be -1
5681 --x_po_line_num
5682 -- The po line number of the PO line to which the shipment belongs
5683 --x_po_line_num_disp
5684 --The po line number of the PO line to which the shipment belongs
5685 --End of Comments
5686 -------------------------------------------------------------------------------
5687
5688 PROCEDURE get_po_linenum_for_shipment
5689 (
5690 p_po_shipment_id IN NUMBER
5691 , p_draft_id IN NUMBER
5692 , x_po_line_num OUT NOCOPY NUMBER
5693 , x_po_line_num_disp OUT NOCOPY VARCHAR2
5694 )
5695 IS
5696
5697 d_mod CONSTANT VARCHAR2(100) := D_get_po_linenum_for_shipment ;
5698 d_position NUMBER := 0;
5699
5700 BEGIN
5701
5702 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
5703
5704 d_position := 10;
5705 BEGIN
5706 SELECT line_num, line_num_display
5707 INTO x_po_line_num, x_po_line_num_disp
5708 FROM po_lines_merge_v plm,
5709 po_line_locations_merge_v pllm
5710 WHERE plm.po_line_id = pllm.po_line_id
5711 AND plm.draft_id = pllm.draft_id
5712 AND pllm.line_location_id = p_po_shipment_id
5713 AND pllm.draft_id = p_draft_id;
5714 EXCEPTION
5715 WHEN no_data_found THEN
5716 x_po_line_num := null;
5717 x_po_line_num_disp := null;
5718 END;
5719
5720 IF PO_LOG.d_proc THEN PO_LOG.proc_begin(d_mod); END IF;
5721 IF PO_LOG.d_stmt THEN
5722 PO_LOG.stmt(d_mod,d_position,'x_po_line_num',x_po_line_num);
5723 PO_LOG.stmt(d_mod,d_position,'x_po_line_num_disp',x_po_line_num_disp);
5724 END IF;
5725 END get_po_linenum_for_shipment;
5726 /*bug 13622501 end */
5727
5728 -------------------------------------------------------------------------------
5729 --Start of Comments
5730 --Name: check_item_description
5731 --Pre-reqs:
5732 -- None
5733 --Modifies:
5734 -- None
5735 --Locks:
5736 -- None
5737 --Function:
5738 -- Checks whether a requisition line and a PO line having same Item Description
5739 -- for one time Item Based Lines and returns the result during the Autocreate
5740 -- process in manual mode.
5741 --Parameters:
5742 --IN:
5743 --p_po_header_id
5744 -- The ID of the add to purchase order specified in the document builder.
5745 --p_po_line_num
5746 -- The ID of the po line number specified in the document builder of req line.
5747 --p_req_line_id
5748 -- The ID of the requisition line added for autocreate to a PO.
5749 --OUT:
5750 --x_same_item_desc
5751 -- The result code corresponding to whether the two lines item desc match or not.
5752 --Notes:
5753 -- Added as part of bug fix 16097884
5754 --Testing:
5755 -- None
5756 --End of Comments
5757 -------------------------------------------------------------------------------
5758
5759 PROCEDURE check_item_description(
5760 p_po_header_id IN NUMBER,
5761 p_po_line_num IN NUMBER,
5762 p_req_line_id IN NUMBER,
5763 x_same_item_desc OUT NOCOPY VARCHAR2
5764 )
5765 IS
5766 d_mod CONSTANT VARCHAR2(100) := D_check_item_description;
5767 d_position NUMBER := 0;
5768
5769 l_req_line LINE_INFO;
5770 l_po_line LINE_INFO;
5771 l_po_item_id PO_LINES_ALL.ITEM_ID%TYPE;
5772 l_po_item_desc PO_LINES_ALL.ITEM_DESCRIPTION%TYPE;
5773
5774 BEGIN
5775
5776 IF (PO_LOG.d_proc) THEN
5777 PO_LOG.proc_begin(d_mod);
5778 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
5779 PO_LOG.proc_begin(d_mod,'p_po_line_num',p_po_line_num);
5780 PO_LOG.proc_begin(d_mod,'p_req_line_id',p_req_line_id);
5781 END IF;
5782
5783 x_same_item_desc := 'N';
5784
5785 d_position := 10;
5786 -- Fetch the po line id
5787 BEGIN
5788 SELECT item_id, item_description
5789 INTO l_po_item_id, l_po_item_desc
5790 FROM po_lines_all
5791 WHERE po_header_id = p_po_header_id
5792 AND line_num = p_po_line_num;
5793 EXCEPTION
5794 WHEN NO_DATA_FOUND THEN
5795 -- REQ added with New Line Added to PO during Autocreate
5796 x_same_item_desc := 'Y';
5797 RETURN;
5798 END;
5799
5800 d_position := 20;
5801 --- Get the Req Line Details
5802 l_req_line := get_req_line_info(p_req_line_id);
5803
5804 d_position := 30;
5805 IF (l_req_line.item_id IS NULL
5806 AND l_po_item_id IS NULL
5807 AND PO_CORE_S.is_equal(
5808 l_req_line.item_description,
5809 l_po_item_desc))
5810 THEN
5811 x_same_item_desc := 'Y';
5812 END IF;
5813
5814 EXCEPTION
5815 WHEN OTHERS THEN
5816 IF (PO_LOG.d_exc) THEN
5817 PO_LOG.exc(d_mod, d_position, 'An error occured in check_item_description');
5818 END IF;
5819 RAISE;
5820
5821 END check_item_description;
5822
5823 END PO_AUTOCREATE_GROUPING_PVT;
5824
[Home] [Help]