[Home] [Help]
PACKAGE BODY: APPS.PO_AUTO_LINE_PROCESS_PVT
Source
1 PACKAGE BODY po_auto_line_process_pvt AS
2 /* $Header: PO_AUTO_LINE_PROCESS_PVT.plb 120.32.12020000.3 2013/02/15 10:52:40 amalick ship $ */
3
4 /* ============================================================================
5 **
6 ** NAME
7 ** PO_AUTO_LINE_PROCESS_PVT.plb
8 **
9 ** DESCRIPTION
10 ** This package contains logic for AutoCreate Line processing stage
11 ** This API calls the subroutines to handle the derivation, defaulting,
12 ** validation and insert/update of the Lines.
13 **
14 ** - Group lines in po_lines_interface
15 ** - Fetch data from po_lines_interface
16 ** - Derive Line
17 ** - Default Line
18 ** - Validate Line
19 ** - Insert into po_lines_draft_all
20 **
21 ** The procedures of this package are called from PO_AUTOCREATE_MAINPROC_PVT.process_line
22 **
23 ** HISTORY
24 ** 06/11/09 serukull Created
25 ============================================================================ */
26 g_pkg_name CONSTANT VARCHAR2(1000) := 'PO_AUTO_LINE_PROCESS_PVT';
27 g_log_head CONSTANT VARCHAR2(1000) := 'po.plsql.PO_AUTO_LINE_PROCESS_PVT.';
28 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
29 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
30 p_price_diff po_autocreate_types.price_diffs_rec_type;
31
32 /* ----------------------------------------------------
33 ----------------- PRIVATE PROCEDURES ----------------
34 ---------------------------------------------------- */
35 /* ============================================================================
36 PROCEDURE get_interface_shipto_info(
37 p_intf_line_id IN NUMBER,
38 p_destination_type_code OUT NOCOPY VARCHAR2,
39 p_ship_to_org_id OUT NOCOPY NUMBER,
40 p_deliver_to_loc_id OUT NOCOPY NUMBER )
41 =========================================================================== */
42
43 PROCEDURE get_interface_shipto_info( p_intf_line_id IN NUMBER,
44 p_destination_type_code OUT NOCOPY VARCHAR2,
45 p_ship_to_org_id OUT NOCOPY NUMBER,
46 p_deliver_to_loc_id OUT NOCOPY NUMBER
47 )
48 IS
49
50 l_api_name VARCHAR2(30) := 'get_interface_shipto_info';
51 l_log_head VARCHAR2(100) := g_log_head || l_api_name;
52 l_progress VARCHAR2(3) :='000';
53
54 CURSOR c_ship_to_info
55 IS
56 SELECT destination_type_code,
57 destination_organization_id,
58 deliver_to_location_id
59 FROM po_distributions_interface
60 WHERE interface_header_id = po_autocreate_params.x_interface_header_id
61 AND interface_line_id = p_intf_line_id
62 ORDER BY interface_distribution_id;
63
64 BEGIN
65 IF (PO_LOG.d_proc) THEN
66 PO_LOG.proc_begin(l_log_head);
67 PO_LOG.proc_begin(l_log_head, 'p_intf_line_id', p_intf_line_id);
68 PO_LOG.proc_begin(l_log_head, 'p_ship_to_org_id', p_ship_to_org_id);
69 PO_LOG.proc_begin(l_log_head, 'p_deliver_to_loc_id', p_deliver_to_loc_id);
70 END IF;
71
72 l_progress :='010';
73
74 OPEN c_ship_to_info;
75 FETCH c_ship_to_info
76 INTO p_destination_type_code,
77 p_ship_to_org_id,
78 p_deliver_to_loc_id;
79
80 CLOSE c_ship_to_info;
81
82 IF (PO_LOG.d_proc) THEN
83 PO_LOG.proc_return(l_log_head, p_ship_to_org_id);
84 PO_LOG.proc_return(l_log_head, p_deliver_to_loc_id);
85 PO_LOG.proc_end(l_log_head);
86 END IF;
87
88 EXCEPTION
89 WHEN No_Data_Found THEN
90 p_destination_type_code := NULL;
91 p_ship_to_org_id := NULL;
92 p_deliver_to_loc_id := NULL;
93 PO_DEBUG.debug_exc ( p_log_head => l_log_head , p_progress => l_progress);
94 WHEN OTHERS THEN
95 PO_DEBUG.debug_exc ( p_log_head => l_log_head , p_progress => l_progress);
96
97 --CLM Phase 2 changes : error handling
98 PO_AUTOCREATE_PVT.report_error('PO_AUTO_SHIPTO_ERR',
99 x_token1_value => sqlerrm);
100
101 RAISE;
102 END get_interface_shipto_info;
103
104 -------------------------------------------------------------------------------
105 --Start of Comments
106 --Name: GET_RATE_FOR_REQ_PRICE
107 --Pre-reqs:
108 -- None
109 --Modifies:
110 -- None
111 --Locks:
112 -- None.
113 --Function:
114 -- Get the conversion rate between PO Currency and Req Functional Currency for
115 -- Default Rate type of Purchasing Org
116 --Parameters:
117 -- IN:
118 -- p_requesting_ou_id: Requesting Operating Unit <ACHTML R12>
119 -- p_purchasing_ou_id: Purchasing Operating Unit <ACHTML R12>
120 -- p_po_currency_code: The currency in which PO will be cut
121 -- p_rate_type: The default rate type of Purchasing Operating Unit
122 -- p_rate_date: The date used to derive rate between PO and POU functional currency
123 -- OUT:
124 -- x_rate: The rate between PO currency and Requisition raising Operating Unit's functional currency
125 -- Returns NULL if POU and ROU are in same Set Of Books (implying same functional currency)
126 --Notes:
127 -- None
128 --Testing:
129 -- None
130 --End of Comments
131 -------------------------------------------------------------------------------
132 PROCEDURE get_rate_for_req_price
133 (
134 p_requesting_ou_id IN NUMBER,
135 p_purchasing_ou_id IN NUMBER,
136 p_po_currency_code IN VARCHAR2,
137 p_rate_type IN VARCHAR2,
138 p_rate_date IN DATE,
139 x_rate OUT NOCOPY NUMBER )
140 IS
141 l_req_ou_sob_id gl_sets_of_books.set_of_books_id%TYPE;
142 l_po_ou_sob_id gl_sets_of_books.set_of_books_id%TYPE;
143 l_inverse_rate_display_flag VARCHAR2 (1) := 'N';
144 l_display_rate NUMBER;
145 l_progress VARCHAR2 (3) := '000';
146 l_rate_type po_headers_interface.rate_type%TYPE;
147 l_api_name CONSTANT VARCHAR2 (30) := 'get_rate_for_req_price';
148
149 BEGIN
150
151 IF g_debug_stmt THEN
152 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
153 END IF;
154
155 l_progress := '010';
156
157 SELECT req_fsp.set_of_books_id
158 INTO l_req_ou_sob_id
159 FROM financials_system_params_all req_fsp
160 WHERE req_fsp.org_id = p_requesting_ou_id;
161
162 l_progress := '020';
163 SELECT po_fsp.set_of_books_id
164 INTO l_po_ou_sob_id
165 FROM financials_system_params_all po_fsp
166 WHERE po_fsp.org_id = p_purchasing_ou_id;
167
168 IF l_req_ou_sob_id = l_po_ou_sob_id THEN
169 l_progress := '030';
170 x_rate := NULL;
171 RETURN;
172 END IF;
173
174 IF p_rate_type IS NULL THEN
175 l_progress := '040';
176 SELECT default_rate_type
177 INTO l_rate_type
178 FROM po_system_parameters_all psp
179 WHERE psp.org_id = p_purchasing_ou_id;
180 ELSE
181 l_progress := '050';
182 l_rate_type := p_rate_type;
183 END IF;
184
185 l_progress := '060';
186 po_currency_sv.get_rate (l_req_ou_sob_id,
187 p_po_currency_code,
188 l_rate_type,
189 p_rate_date,
190 l_inverse_rate_display_flag,
191 x_rate,
192 l_display_rate );
193
194 IF g_debug_stmt THEN
195 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
196 END IF;
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 IF g_debug_unexp THEN
201 po_debug.debug_exc (p_log_head => g_log_head || l_api_name, p_progress => l_progress );
202 END IF;
203
204 END get_rate_for_req_price;
205
206 /* ----------------------------------------------------
207 ----------------- PUBLIC PROCEDURES ----------------
208 ---------------------------------------------------- */
209 /* ============================================================================
210 NAME: setup_interface_data
211 DESC: This procedure defaults all the data required into Lines interface
212 and distributions interface from the Requisition main tables.
213 ARGS: None
214 ============================================================================ */
215 PROCEDURE setup_interface_data
216 IS
217
218 l_progress VARCHAR2(3) := '000';
219 l_api_name VARCHAR2(30) := 'setup_interface_data';
220 l_ship_to_org_id HR_LOCATIONS_ALL.inventory_organization_id%TYPE;
221 l_count_dist NUMBER;
222 -- Added for Linking requisition to Modification project
223 l_clin_num_display po_lines_interface.line_num_display%TYPE;
224 l_slin_num_display po_lines_interface.line_num_display%TYPE;
225 l_slin_count NUMBER :=0;
226 l_is_first_slin VARCHAR2(1) :='Y';
227
228 CURSOR c_slin
229 IS
230 SELECT INTERFACE_LINE_ID INTERFACE_LINE_ID,
231 group_line_id group_line_id
232 FROM po_lines_interface
233 WHERE group_line_id IS NOT NULL
234 AND interface_header_id =PO_AUTOCREATE_PARAMS.x_interface_header_id;
235
236 CURSOR c_clin(p_req_line_id IN NUMBER)
237 IS
238 SELECT INTERFACE_LINE_ID
239 FROM po_lines_interface
240 WHERE 1 =1
241 AND interface_header_id=PO_AUTOCREATE_PARAMS.x_interface_header_id
242 AND REQUISITION_LINE_ID=p_req_line_id;
243
244 CURSOR c_option
245 IS
246 SELECT INTERFACE_LINE_ID,
247 CLM_BASE_LINE_NUM
248 FROM po_lines_interface
249 WHERE clm_base_line_num IS NOT NULL
250 AND interface_header_id =PO_AUTOCREATE_PARAMS.x_interface_header_id;
251
252 CURSOR c_base(p_req_line_id IN NUMBER)
253 IS
254 SELECT INTERFACE_LINE_ID
255 FROM po_lines_interface
256 WHERE 1 =1
257 AND interface_header_id=PO_AUTOCREATE_PARAMS.x_interface_header_id
258 AND REQUISITION_LINE_ID=p_req_line_id;
259
260 l_parent_line_id NUMBER;
261
262 CURSOR C_default_distribution
263 IS
264 SELECT pli.interface_header_id,
265 pli.interface_line_id,
266 pli.item_id,
267 pli.line_type_id,
268 pli.quantity,
269 pli.amount,
270 pli.category_id,
271 pli.ship_to_location_id,
272 pli.ship_to_organization_id,
273 phi.vendor_id,
274 phi.vendor_site_id,
275 phi.agent_id,
276 phi.rate,
277 phi.rate_date,
278 phi.document_subtype,
279 pli.unit_price
280 FROM po_lines_interface pli,
281 po_headers_interface phi,
282 po_line_types plt
283 WHERE phi.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
284 AND phi.interface_header_id = pli.interface_header_id
285 AND pli.requisition_line_id IS NULL
286 AND plt.line_type_id = pli.line_type_id
287 AND Nvl(PLI.orig_from_req_flag, 'N') <> 'S';
288
289 BEGIN
290
291 IF g_debug_stmt THEN
292 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
293 END IF;
294
295 IF PO_AUTOCREATE_PARAMS.g_interface_source_code ='SOURCING' THEN
296
297 --Since we allowe cancellation and finally close of reqs it's possible
298 --that the requistion reference sourcing passed to interface tables
299 --are already cancelled or finally closed.
300 --So we update the requisition line ids of such interface line
301 --records before starting the process. And treat them as non req
302 --backing negotiations.
303 --<CONSUME REQ DEMAND FPI>
304 --In FPI sourcing places the reqs back to pool at the time of splitting.
305 --By the time if some one has place these reqs on another PO document
306 --or sourcing doc autocreate should treat it as not backed by a req.
307 --Included the where clause
308 --a."prl.line_location_id is not null"
309 --b."(prl.auction_header_id<>pli.auction_header_id
310 -- and prl.auction_line_number<>pli.auction_line_number)" in the
311 --following sql.
312 l_progress := '010';
313
314 UPDATE po_lines_interface pli
315 SET pli.requisition_line_id = NULL
316 WHERE pli.interface_header_id= PO_AUTOCREATE_PARAMS.x_interface_header_id
317 AND EXISTS
318 (SELECT requisition_line_id
319 FROM po_requisition_lines_all prl
320 WHERE prl.requisition_line_id= pli.requisition_line_id
321 /* Bug : 13695551 : When autocreated from sourcing,
322 * a req line should be allowed to be placed on multiple awards.
323 */
324 AND (--prl.line_location_id IS NOT NULL OR
325 prl.cancel_flag ='Y'
326 OR prl.closed_code ='FINALLY CLOSED'
327 OR (prl.auction_header_id <>pli.auction_header_id
328 AND prl.auction_line_number <>pli.auction_line_number) )
329 );
330
331 l_progress := '020';
332 -- With drawn req lines are deleted from po_requisition_lines
333 -- table. Hence require a separate update.
334
335 IF g_debug_stmt THEN
336 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Before the update for withdrawn reqs');
337 END IF;
338
339 UPDATE po_lines_interface pli
340 SET pli.requisition_line_id = NULL
341 WHERE pli.interface_header_id= PO_AUTOCREATE_PARAMS.x_interface_header_id
342 AND NOT EXISTS
343 (SELECT requisition_line_id
344 FROM po_requisition_lines_all prl --<Shared Proc FPJ>
345 WHERE prl.requisition_line_id= pli.requisition_line_id
346 );
347
348
349 END IF;
350
351 l_progress := '030';
352 -- The following code is for REQ to PO flow.
353 -- If the call is from SOURCING, then need to set the REQUISITION_LINE_ID
354 -- to null to avoid any further processing based on this.
355 IF (PO_AUTOCREATE_PARAMS.g_document_type IN ('PO','PA'))
356 --AND PO_AUTOCREATE_PARAMS.g_interface_source_code <> 'SOURCING') -- <Bug 9896177>
357 THEN
358 l_progress:='040';
359
360 IF g_debug_stmt THEN
361 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
362 p_message => 'Before setting priced lines in interface tables');
363 END IF;
364
365 UPDATE po_lines_interface pli2
366 SET
367 (
368 line_num,
369 item_id,
370 job_id,
371 category_id,
372 item_description,
373 unit_of_measure,
374 list_price_per_unit,
375 market_price,
376 base_unit_price,
377 unit_price,
378 quantity,
379 amount,
380 taxable_flag,
381 type_1099,
382 negotiated_by_preparer_flag,
383 closed_code,
384 item_revision,
385 un_number_id,
386 hazard_class_id,
387 line_type_id,
388 vendor_product_num,
389 qty_rcv_tolerance,
390 over_tolerance_error_flag,
391 firm_flag,
392 min_release_amount,
393 price_type,
394 transaction_reason_code,
395 line_location_id,
396 need_by_date,
397 ship_to_organization_id,
398 note_to_receiver,
399 from_header_id,
400 from_line_id,
401 receipt_required_flag,
402 tax_status_indicator,
403 note_to_vendor,
404 oke_contract_header_id,
405 oke_contract_version_id,
406 secondary_unit_of_measure,
407 secondary_quantity,
408 preferred_grade,
409 drop_ship_flag,
410 vmi_flag,
411 supplier_ref_number,
412 effective_date,
413 expiration_date,
414 contractor_first_name,
415 contractor_last_name,
416 --CLM CLIN/SLIN changes START
417 line_num_display,
418 group_line_id,
419 clm_info_flag,
420 clm_option_indicator ,
421 clm_option_num ,
422 clm_option_from_date ,
423 clm_option_to_date ,
424 clm_funded_flag ,
425 clm_base_line_num ,
426 --clm clin/slin changes end
427 -- complex pricing changes start
428 contract_type,
429 cost_constraint
430 -- Complex Pricing Changes End
431 )
432 =
433 (SELECT pli.line_num,
434 NVL(pli.item_id,prl.item_id),
435 NVL(pli.job_id, prl.job_id),
436 NVL(pli.category_id,prl.category_id),
437 NVL(pli.item_description,prl.item_description),
438 NVL(pli.unit_of_measure,prl.unit_meas_lookup_code),
439 pli.list_price_per_unit,
440 pli.market_price,
441 NVL(pli.base_unit_price,prl.base_unit_price),
442 NVL(pli.unit_price,prl.unit_price),
443 DECODE ( prl.order_type_lookup_code , 'FIXED PRICE' , NULL , 'RATE' , NULL , NVL(pli.quantity,prl.quantity) ),
444 NVL(pli.amount, prl.amount),
445 pli.taxable_flag,
446 pli.type_1099,
447 NVL(pli.negotiated_by_preparer_flag,'N'),
448 DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'PO', NVL(pli.closed_code,'OPEN'), NULL),
449 NVL(pli.item_revision,prl.item_revision),
450 NVL(pli.un_number_id,prl.un_number_id),
451 NVL(pli.hazard_class_id,prl.hazard_class_id),
452 NVL(pli.line_type_id,prl.line_type_id),
453 NVL(pli.vendor_product_num,prl.suggested_vendor_product_code),
454 pli.qty_rcv_tolerance,
455 pli.over_tolerance_error_flag,
456 NVL(pli.firm_flag,'N'),
457 --<SOURCING TO PO FPH>
458 DECODE(PO_AUTOCREATE_PARAMS.g_document_type,
459 'PO', NVL(pli.min_release_amount,PO_AUTOCREATE_PARAMS.g_sys.min_rel_amount),
460 'PA',NVL(pli.min_release_amount,PO_AUTOCREATE_PARAMS.g_sys.min_rel_amount),NULL),
461 DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'PO', NVL(pli.price_type,PO_AUTOCREATE_PARAMS.g_sys.price_type_lookup_code), NULL),
462 NVL(pli.transaction_reason_code,prl.transaction_reason_code),
463 pli.line_location_id,
464 NVL(pli.need_by_date,prl.need_by_date),
465 NVL(pli.ship_to_organization_id,prl.destination_organization_id),
466 NVL(pli.note_to_receiver,prl.note_to_receiver),
467 pli.from_header_id,
468 pli.from_line_id,
469 NVL(pli.receipt_required_flag,plt.receiving_flag),
470 prl.tax_status_indicator,
471 NVL(pli.note_to_vendor, prl.note_to_vendor),
472 DECODE(pli.consigned_flag,'Y',NULL, NVL(pli.oke_contract_header_id,prl.oke_contract_header_id)),
473 DECODE(pli.consigned_flag,'Y',NULL, NVL(pli.oke_contract_version_id,prl.oke_contract_version_id)),
474 NVL(pli.secondary_unit_of_measure,prl.secondary_unit_of_measure),
475 NVL(pli.secondary_quantity,prl.secondary_quantity),
476 NVL(pli.preferred_grade,prl.preferred_grade),
477 prl.drop_ship_flag, -- <DropShip FPJ>
478 prl.vmi_flag,
479 prl.supplier_ref_number, --<CONFIG_ID FPJ>
480 -- <SERVICES FPJ START>
481 NVL(pli.effective_date, prl.assignment_start_date),
482 NVL(pli.expiration_date, prl.assignment_end_date),
483 NVL(pli.contractor_first_name, prl.candidate_first_name),
484 NVL(pli.contractor_last_name, prl.candidate_last_name)
485 -- <SERVICES FPJ END>
486 ,
487 pli.line_num_display ,
488 NVL(pli.group_line_id,prl.group_line_id) ,
489 NVL(pli.clm_info_flag, prl.clm_info_flag) ,
490 NVL(pli.clm_option_indicator, prl.clm_option_indicator) ,
491 NVL(pli.clm_option_num, prl.clm_option_num) ,
492 NVL(pli.clm_option_from_date, prl.clm_option_from_date) ,
493 NVL(pli.clm_option_to_date, prl.clm_option_to_date) ,
494 NVL(pli.clm_funded_flag, prl.clm_funded_flag) ,
495 NVL(pli.clm_base_line_num,prl.clm_base_line_num) ,
496 NVL(pli.contract_type,prl.contract_type) ,
497 NVL(pli.cost_constraint,prl.cost_constraint)
498 FROM po_lines_interface pli,
499 po_headers_interface phi,
500 po_requisition_lines_all prl,
501 po_line_types plt
502 WHERE pli.interface_line_id = pli2.interface_line_id
503 AND pli.interface_header_id = phi.interface_header_id
504 AND phi.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
505 AND pli.requisition_line_id = prl.requisition_line_id(+)
506 AND plt.line_type_id = NVL(prl.line_type_id,pli.line_type_id)
507 )
508 WHERE pli2.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
509 AND NVL(pli2.clm_info_flag,'N') <> 'Y';
510
511 l_progress:='050';
512
513 IF g_debug_stmt THEN
514 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
515 p_message => 'Before setting Info lines in interface tables');
516 END IF;
517
518 /* For Info-Lines */
519 UPDATE po_lines_interface pli2
520 SET
521 (
522 line_num,
523 item_id,
524 job_id,
525 category_id,
526 item_description,
527 unit_of_measure,
528 list_price_per_unit,
529 market_price,
530 base_unit_price,
531 unit_price,
532 quantity,
533 amount,
534 taxable_flag,
535 type_1099,
536 negotiated_by_preparer_flag,
537 closed_code,
538 item_revision,
539 un_number_id,
540 hazard_class_id,
541 line_type_id,
542 vendor_product_num,
543 qty_rcv_tolerance,
544 over_tolerance_error_flag,
545 firm_flag,
546 min_release_amount,
547 price_type,
548 transaction_reason_code,
549 line_location_id,
550 need_by_date,
551 ship_to_organization_id,
552 note_to_receiver,
553 from_header_id,
554 from_line_id,
555 tax_status_indicator,
556 note_to_vendor,
557 oke_contract_header_id,
558 oke_contract_version_id,
559 secondary_unit_of_measure,
560 secondary_quantity,
561 preferred_grade,
562 drop_ship_flag, -- <DropShip FPJ>
563 vmi_flag,
564 supplier_ref_number, --<CONFIG_ID FPJ>
565 effective_date,
566 expiration_date,
567 contractor_first_name,
568 contractor_last_name,
569 --CLM CLIN/SLIN changes START
570 line_num_display,
571 group_line_id,
572 clm_info_flag,
573 clm_option_indicator ,
574 clm_option_num ,
575 clm_option_from_date ,
576 clm_option_to_date ,
577 clm_funded_flag ,
578 clm_base_line_num
579 -- CONTRACT_TYPE -- Info lines will not have any pricing info.
580 )
581 = --CLM CLIN/SLIN changes END
582 (SELECT pli.line_num,
583 NULL,
584 NVL(pli.job_id, prl.job_id),
585 NULL,
586 NVL(pli.item_description,prl.item_description),
587 NULL,
588 pli.list_price_per_unit,
589 pli.market_price,
590 NVL(pli.base_unit_price,prl.base_unit_price), -- <FPJ Advanced Price>
591 NULL,
592 DECODE ( prl.order_type_lookup_code , 'FIXED PRICE' , NULL , 'RATE' , NULL , NVL(pli.quantity,prl.quantity) ),
593 NULL,
594 NULL,
595 pli.type_1099,
596 NVL(pli.negotiated_by_preparer_flag,'N'),
597 DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'PO', NVL(pli.closed_code,'OPEN'), NULL),
598 NULL,
599 NVL(pli.un_number_id,prl.un_number_id),
600 NVL(pli.hazard_class_id,prl.hazard_class_id),
601 NULL,
602 /*For Info Lines Line Type not required */
603 NVL(pli.vendor_product_num,prl.suggested_vendor_product_code),
604 pli.qty_rcv_tolerance,
605 pli.over_tolerance_error_flag,
606 NVL(pli.firm_flag,'N'),
607 DECODE(PO_AUTOCREATE_PARAMS.g_document_type,
608 'PO', NVL(pli.min_release_amount,PO_AUTOCREATE_PARAMS.g_sys.min_rel_amount),
609 'PA',NVL(pli.min_release_amount,PO_AUTOCREATE_PARAMS.g_sys.min_rel_amount),NULL),
610 DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'PO', NVL(pli.price_type,PO_AUTOCREATE_PARAMS.g_sys.price_type_lookup_code), NULL),
611 NVL(pli.transaction_reason_code,prl.transaction_reason_code),
612 pli.line_location_id,
613 NVL(pli.need_by_date,prl.need_by_date),
614 NVL(pli.ship_to_organization_id,prl.destination_organization_id),
615 NVL(pli.note_to_receiver,prl.note_to_receiver),
616 pli.from_header_id,
617 pli.from_line_id,
618 prl.tax_status_indicator,
619 NVL(pli.note_to_vendor, prl.note_to_vendor),
620 DECODE(pli.consigned_flag,'Y',NULL, NVL(pli.oke_contract_header_id,prl.oke_contract_header_id)),
621 DECODE(pli.consigned_flag,'Y',NULL, NVL(pli.oke_contract_version_id,prl.oke_contract_version_id)),
622 NVL(pli.secondary_unit_of_measure,prl.secondary_unit_of_measure),
623 NVL(pli.secondary_quantity,prl.secondary_quantity),
624 NVL(pli.preferred_grade,prl.preferred_grade),
625 prl.drop_ship_flag, -- <DropShip FPJ>
626 prl.vmi_flag,
627 prl.supplier_ref_number, --<CONFIG_ID FPJ>
628 -- <SERVICES FPJ START>
629 NVL(pli.effective_date, prl.assignment_start_date),
630 NVL(pli.expiration_date, prl.assignment_end_date),
631 NVL(pli.contractor_first_name, prl.candidate_first_name),
632 NVL(pli.contractor_last_name, prl.candidate_last_name) ,
633 PLI.LINE_NUM_DISPLAY ,
634 NVL(PLI.GROUP_LINE_ID,prl.GROUP_LINE_ID) ,
635 NVL(pli.CLM_INFO_FLAG, prl.CLM_INFO_FLAG) ,
636 NVL(pli.CLM_OPTION_INDICATOR, prl.CLM_OPTION_INDICATOR) ,
637 NVL(pli.CLM_OPTION_NUM, prl.CLM_OPTION_NUM) ,
638 NVL(pli.CLM_OPTION_FROM_DATE, prl.CLM_OPTION_FROM_DATE) ,
639 NVL(pli.CLM_OPTION_TO_DATE, prl.CLM_OPTION_TO_DATE) ,
640 NVL(pli.CLM_FUNDED_FLAG, prl.CLM_FUNDED_FLAG) ,
641 NVL(pli.CLM_BASE_LINE_NUM,prl.CLM_BASE_LINE_NUM)
642 -- <SERVICES FPJ END>
643 FROM po_lines_interface pli,
644 po_headers_interface phi,
645 po_requisition_lines_all prl --<Shared Proc FPJ>
646 WHERE pli.interface_line_id = pli2.interface_line_id
647 AND pli.interface_header_id = phi.interface_header_id
648 AND phi.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
649 AND pli.requisition_line_id = prl.requisition_line_id(+)
650 )
651 WHERE pli2.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
652 AND ( NVL(pli2.clm_info_flag,'N') = 'Y');
653
654 IF (PO_AUTOCREATE_PARAMS.g_interface_source_code <> 'SOURCING') THEN
655
656 l_progress :='060';
657 IF g_debug_stmt THEN
658 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
659 p_message => 'Before setting the CLIN SLIN and Options structure in Lines Interface Table');
660 END IF;
661
662 /*
663 Steps:
664 1. Get All Slins
665 2. For each Slin
666 a) Get the Group Line Id
667 b) Find the Line Id of line in Interface table whose Req Line Matches the above Group Line Id
668 c) Update Slin's Group Line Id with the Line Id from step2.
669 3. Get All options
670 4. For each Option Line.
671 a) Get the Base Line Id (clm_base_line_num)
672 b) Find the Line Id of line in Interface table whose Req Line Matches the above Base Line Id
673 c) Update Slin's clm_base_line_num with the Line Id from step2.
674 */
675
676 FOR crec IN c_slin
677 LOOP
678
679 l_progress :='061';
680 l_parent_line_id := NULL;
681
682 OPEN c_clin(crec.group_line_id);
683 FETCH c_clin INTO l_parent_line_id;
684 CLOSE c_clin;
685
686 l_progress :='062';
687 UPDATE po_lines_interface
688 SET group_line_id = l_parent_line_id
689 WHERE INTERFACE_LINE_ID = crec.INTERFACE_LINE_ID
690 AND INTERFACE_HEADER_ID = PO_AUTOCREATE_PARAMS.x_interface_header_id;
691 -- Added for Linking requisition to modification project
692
693 IF po_autocreate_params.g_process_code = 'ADD_FUNDS' THEN
694 l_progress :='063';
695 BEGIN
696 SELECT line_num_display
697 INTO l_clin_num_display
698 FROM PO_LINES_merge_v
699 WHERE PO_HEADER_ID = po_autocreate_params.g_po_header_id
700 AND draft_id = po_autocreate_params.g_draft_id
701 AND LINE_NUM =
702 (SELECT line_num
703 FROM po_lines_interface
704 WHERE interface_line_id = l_parent_line_id
705 AND INTERFACE_HEADER_ID = PO_AUTOCREATE_PARAMS.x_interface_header_id
706 );
707
708 IF l_is_first_slin = 'Y' THEN
709 l_progress :='064';
710 l_slin_num_display := pon_clo_renumber_pkg.GetNextSlinNumber('PO',po_autocreate_params.g_po_header_id,l_clin_num_display,'Y');
711 l_is_first_slin := 'N';
712 l_slin_count := To_Number(l_slin_num_display);
713 ELSE
714 l_progress :='065';
715 SELECT LPad(l_slin_count + 1,6,'0') INTO l_slin_num_display FROM dual;
716 l_slin_count := l_slin_count + 1;
717 END IF;
718
719 IF g_debug_stmt THEN
720 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
721 p_message => 'Setting line_num_display for slins of ' || l_clin_num_display || ' for slin ' || l_slin_num_display);
722 END IF;
723
724 l_progress :='066';
725 UPDATE po_lines_interface
726 SET line_num_display = l_slin_num_display
727 WHERE INTERFACE_LINE_ID = crec.INTERFACE_LINE_ID
728 AND INTERFACE_HEADER_ID = PO_AUTOCREATE_PARAMS.x_interface_header_id
729 AND clm_info_flag ='Y';
730
731 EXCEPTION
732 WHEN OTHERS THEN
733 IF g_debug_stmt THEN
734 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
735 p_message => 'Exception while setting line_num_display for ' || l_clin_num_display);
736 END IF;
737 END;
738 END IF; -- Req to modification project
739
740 END LOOP;
741
742 FOR crec IN c_option
743 LOOP
744 l_progress :='070';
745 l_parent_line_id := NULL;
746
747 OPEN c_base (crec.CLM_BASE_LINE_NUM);
748 FETCH c_base INTO l_parent_line_id;
749 CLOSE c_base;
750
751 l_progress :='071';
752 UPDATE po_lines_interface
753 SET CLM_BASE_LINE_NUM = l_parent_line_id
754 WHERE INTERFACE_LINE_ID = crec.INTERFACE_LINE_ID
755 AND INTERFACE_HEADER_ID = PO_AUTOCREATE_PARAMS.x_interface_header_id;
756
757 END LOOP;
758 END IF;
759 END IF;
760
761 -- setup distributions interface
762 IF PO_AUTOCREATE_PARAMS.g_document_type = 'PO' THEN
763 l_progress :='080';
764 IF g_debug_stmt THEN
765 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Before insert into Distribution interface');
766 END IF;
767
768 INSERT
769 INTO po_distributions_interface
770 (
771 interface_header_id,
772 interface_line_id,
773 interface_distribution_id,
774 distribution_num,
775 charge_account_id,
776 set_of_books_id,
777 quantity_ordered,
778 amount_ordered,
779 rate,
780 rate_date,
781 req_distribution_id,
782 deliver_to_location_id,
783 deliver_to_person_id,
784 encumbered_flag,
785 gl_encumbered_date,
786 gl_encumbered_period_name,
787 destination_type_code,
788 destination_organization_id,
789 destination_subinventory,
790 budget_account_id,
791 accrual_account_id,
792 variance_account_id,
793 --< Shared Proc FPJ Start >
794 dest_charge_account_id,
795 dest_variance_account_id,
796 --< Shared Proc FPJ End >
797 wip_entity_id,
798 wip_line_id,
799 wip_repetitive_schedule_id,
800 wip_operation_seq_num,
801 wip_resource_seq_num,
802 bom_resource_id,
803 prevent_encumbrance_flag,
804 project_id,
805 task_id,
806 end_item_unit_number,
807 expenditure_type,
808 project_accounting_context,
809 destination_context,
810 expenditure_organization_id,
811 expenditure_item_date,
812 tax_recovery_override_flag, --<eTax Integration R12>
813 recovery_rate,
814 recoverable_tax,
815 nonrecoverable_tax,
816 award_id,
817 oke_contract_line_id,
818 oke_contract_deliverable_id,
819 group_line_id,
820 funded_value,
821 partial_funded_flag,
822 quantity_funded, --<Bug#9746497 :CLM Partial Funding Changes>
823 amount_funded --<Bug#9746497 :CLM Partial Funding Changes>
824 )
825 SELECT pli.interface_header_id,
826 pli.interface_line_id,
827 po_distributions_interface_s.nextval,
828 prd.distribution_num,
829 prd.code_combination_id,
830 prd.set_of_books_id,
831 /* Bug : 13695551 : For clm sourcing, we donot split, so we need to prorate distribution quantities for quantity and amount based lines */
832 Decode(PO_AUTOCREATE_PARAMS.g_interface_source_code , 'SOURCING' , prd.req_line_quantity * pli.quantity/prl.quantity, prd.req_line_quantity),
833 DECODE ( PO_AUTOCREATE_PARAMS.g_interface_source_code , 'SOURCING' , prd.req_line_amount * pli.amount/prl.amount , prd.req_line_amount ),
834 phi.rate,
835 phi.rate_date,
836 prd.distribution_id,
837 prl.deliver_to_location_id,
838 prl.to_person_id,
839 prd.encumbered_flag,
840 prd.gl_encumbered_date,
841 prd.gl_encumbered_period_name,
842 prl.destination_type_code,
843 prl.destination_organization_id,
844 prl.destination_subinventory,
845 prd.budget_account_id,
846 prd.accrual_account_id,
847 prd.variance_account_id,
848 --< Shared Proc FPJ Start >
849 -- For non SPS case (common case), set Destination Accounts to NULL
850 NULL, -- dest_charge_account_id
851 NULL, -- dest_variance_account_id
852 --< Shared Proc FPJ End >
853 prl.wip_entity_id,
854 prl.wip_line_id,
855 prl.wip_repetitive_schedule_id,
856 prl.wip_operation_seq_num,
857 prl.wip_resource_seq_num,
858 prl.bom_resource_id,
859 prd.prevent_encumbrance_flag,
860 prd.project_id,
861 prd.task_id,
862 prd.end_item_unit_number,
863 prd.expenditure_type,
864 prd.project_accounting_context,
865 prl.destination_context,
866 prd.expenditure_organization_id,
867 prd.expenditure_item_date,
868 prd.tax_recovery_override_flag, --<eTax Integration R12>
869 prd.recovery_rate,
870 prd.recoverable_tax,
871 prd.nonrecoverable_tax,
872 prd.award_id,
873 DECODE(pli.consigned_flag,'Y',NULL, prd.oke_contract_line_id),
874 DECODE(pli.consigned_flag,'Y',NULL, prd.oke_contract_deliverable_id),
875 prd.info_line_id,
876 prd.funded_value,
877 prd.partial_funded_flag,
878 prd.quantity_funded, --<Bug#9746497 :CLM Partial Funding Changes>
879 prd.amount_funded --<Bug#9746497 :CLM Partial Funding Changes>
880 FROM po_requisition_lines_all prl, --<Shared Proc FPJ>
881 po_req_distributions_all prd, --<Shared Proc FPJ>
882 po_lines_interface pli,
883 po_headers_interface phi
884 WHERE prd.requisition_line_id = prl.requisition_line_id
885 AND prl.requisition_line_id = pli.requisition_line_id
886 AND pli.interface_header_id = phi.interface_header_id
887 AND phi.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
888 AND
889 /* Bug : 13695551 : From sourcing, one req distribution should be funding multiple award line distributions*/
890 ( (po_autocreate_params.g_is_clm_po ='Y' AND PO_AUTOCREATE_PARAMS.g_interface_source_code = 'SOURCING')
891 OR
892 (NOT EXISTS (SELECT 'Y'
893 FROM po_distributions_all pda
894 WHERE pda.req_distribution_id = prd.distribution_id))
895 );
896
897 IF g_debug_stmt THEN
898 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
899 p_message => 'After insert into Distribution interface');
900 END IF;
901
902 /* Option lines do not have distributions , but we still
903 do the insert. We get the deliver to information
904 from the distribution record.
905 */
906 IF po_autocreate_params.g_is_clm_po ='Y' THEN
907 l_progress :='090';
908
909 IF g_debug_stmt THEN
910 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'insert into p_dists record for Option Line');
911 END IF;
912
913 INSERT
914 INTO po_distributions_interface
915 (
916 interface_header_id,
917 interface_line_id,
918 interface_distribution_id,
919 deliver_to_location_id,
920 deliver_to_person_id,
921 destination_type_code,
922 destination_organization_id,
923 destination_subinventory,
924 destination_context
925 )
926 SELECT pli.interface_header_id,
927 pli.interface_line_id,
928 po_distributions_interface_s.nextval,
929 prl.deliver_to_location_id,
930 prl.to_person_id,
931 prl.destination_type_code,
932 prl.destination_organization_id,
933 prl.destination_subinventory,
934 prl.destination_context
935 FROM po_requisition_lines_all prl,
936 po_lines_interface pli --option
937 WHERE pli.interface_header_id = po_autocreate_params.x_interface_header_id
938 AND (( pli.clm_option_indicator = 'O' AND NVL(pli.clm_exercised_flag,'N') = 'N') -- Bug 9960752
939 OR prl.fund_source_not_known = 'Y' )
940 --CLM Phase 2 Changes : PR Lines with no distributions should be handled like
941 --option lines
942 AND NVL(pli.clm_info_flag,'N') = 'N'
943 AND prl.requisition_line_id = pli.requisition_line_id;
944
945 IF g_debug_stmt THEN
946 PO_DEBUG.debug_stmt( p_log_head => g_log_head||l_api_name, p_token => l_progress,
947 p_message => 'After insert into p_dists record for Option Line');
948 END IF;
949 END IF;
950
951 l_progress:='100';
952
953 SELECT COUNT(*)
954 INTO l_count_dist
955 FROM po_distributions_interface
956 WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
957
958 IF g_debug_stmt THEN
959 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Count from dist iterface is '||l_count_dist);
960 END IF;
961 END IF;--end RFQ , PO
962
963 -- For D.o.D case need to set the Distributions properly.
964 IF po_autocreate_params.g_is_clm_po ='Y' THEN
965
966 l_progress :='110';
967
968 UPDATE po_distributions_interface pdi
969 SET group_line_id =
970 (SELECT interface_line_id
971 FROM po_lines_interface pi
972 WHERE pi.requisition_line_id = pdi.group_line_id
973 AND pi.interface_header_id = po_autocreate_params.x_interface_header_id
974 )
975 WHERE pdi.interface_header_id = po_autocreate_params.x_interface_header_id;
976
977 IF g_debug_stmt THEN
978 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
979 p_message => 'No of Distribution records updated : ' || SQL%ROWCOUNT);
980 END IF;
981
982 END IF;
983
984 --Line Type and Structure Changes Project
985 create_pon_back_req_dist;
986
987 --default the distribution for non req backing negotiations.The above
988 --insert only takes care of the interface lines which are backed by
989 --requisitions.
990 IF (PO_AUTOCREATE_PARAMS.g_interface_source_code='SOURCING') THEN
991 IF (PO_LOG.d_stmt) THEN
992 PO_LOG.stmt(g_log_head || l_api_name, l_progress, 'Defaulting dists interface for sourcing.');
993 END IF;
994
995 l_progress:='120';
996
997 FOR i IN c_default_distribution
998 LOOP
999 -- <Complex Work R12 Start>
1000 IF (PO_LOG.d_stmt) THEN
1001 PO_LOG.stmt(g_log_head || l_api_name, 190, 'i.interface_line_id', i.interface_line_id);
1002 END IF;
1003
1004 IF (PO_AUTOCREATE_PARAMS.g_is_complex_work_po) THEN
1005 l_progress:='121';
1006 -- create complex work PO from sourcing
1007
1008 IF (i.ship_to_organization_id IS NOT NULL) THEN
1009 l_ship_to_org_id := i.ship_to_organization_id;
1010 ELSE
1011 -- SQL WHAT: derive default ship_to_organization_id
1012 -- SQL WHY: the ship_to_organization is optional from sourcing
1013 l_progress:='122';
1014
1015 BEGIN
1016 SELECT hrl.inventory_organization_id
1017 INTO l_ship_to_org_id
1018 FROM hr_locations_all hrl
1019 WHERE hrl.location_id = i.ship_to_location_id
1020 AND hrl.ship_to_site_flag = 'Y';
1021 EXCEPTION
1022 WHEN no_data_found THEN
1023 l_ship_to_org_id := NULL;
1024 END;
1025 l_ship_to_org_id := NVL(l_ship_to_org_id, PO_AUTOCREATE_PARAMS.g_sys.master_inv_org_id);
1026 END IF;
1027
1028 -- IF i.ship_to_organization_id IS NOT NULL
1029 -- SQL WHAT: insert minimal data into po_distributions_interface
1030 -- SQL WHY: this is required because the global interface cursor
1031 -- joins to the distributions interface table and uses
1032 -- some of the following fields for defaulting purposes
1033 l_progress:='123';
1034 INSERT
1035 INTO po_distributions_interface
1036 (
1037 interface_header_id ,
1038 interface_line_id ,
1039 interface_distribution_id ,
1040 destination_type_code ,
1041 deliver_to_location_id ,
1042 destination_organization_id
1043 )
1044 VALUES
1045 (
1046 i.interface_header_id ,
1047 i.interface_line_id ,
1048 PO_DISTRIBUTIONS_INTERFACE_S.nextval ,
1049 'EXPENSE' ,
1050 i.ship_to_location_id ,
1051 l_ship_to_org_id
1052 );
1053
1054 IF(PO_LOG.d_stmt) THEN
1055 PO_LOG.stmt( g_log_head || l_api_name, 190, 'Num rows inserted', SQL%ROWCOUNT);
1056 END IF;
1057
1058 ELSE
1059 -- non-complex work po from sourcing
1060 l_progress:='124';
1061 po_negotiations_sv2.default_po_dist_interface
1062 (i.interface_header_id,
1063 i.interface_line_id,
1064 i.item_id, i.category_id,
1065 i.ship_to_organization_id,
1066 i.ship_to_location_id,
1067 NULL, --deliver_to_person_id
1068 PO_AUTOCREATE_PARAMS.g_sys.sob_id,
1069 PO_AUTOCREATE_PARAMS.g_sys.coa_id,
1070 i.line_type_id,
1071 i.quantity,
1072 i.amount,
1073 i.rate,
1074 i.rate_date,
1075 i.vendor_id,
1076 i.vendor_site_id,
1077 i.agent_id,
1078 NVL(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'N'),
1079 NULL,
1080 i.document_subtype,
1081 NULL,
1082 NULL,
1083 NULL,
1084 NULL,
1085 NULL,
1086 NULL,
1087 NULL,
1088 NULL,
1089 NULL,
1090 NULL,
1091 NULL,
1092 NULL,
1093 NULL,
1094 NULL,
1095 NULL, --project_accounting_context
1096 PO_AUTOCREATE_PARAMS.g_purchasing_ou_id, --< Shared Proc FPJ >
1097 i.unit_price
1098 );
1099 -- need to populate the p_dists record from the distribution interface
1100 END IF; -- IF po_autocreate_params.g_is_complex_work_po
1101 -- <Complex Work R12 End>
1102 END LOOP;
1103 END IF;
1104
1105 IF g_debug_stmt THEN
1106 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
1107 p_message => 'END: Default Distribution for Non Req Backing negotiations');
1108 END IF;
1109
1110 IF g_debug_stmt THEN
1111 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1112 END IF;
1113
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 IF g_debug_unexp THEN
1117 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1118 END IF;
1119
1120 --CLM Phase 2 changes : error handling
1121 PO_AUTOCREATE_PVT.report_error('PO_AUTO_SETUP_INTF_DATA_ERR',x_token1_value => sqlerrm);
1122
1123 po_message_s.sql_error('SETUP_INTERFACE_DATA',l_progress,SQLCODE);
1124 PO_AUTOCREATE_PVT.wrapup();
1125 RAISE;
1126 END setup_interface_data;
1127
1128 /* ============================================================================
1129 NAME: fetch_lines
1130 DESC: Fetch line details into line record type
1131 ARGS: OUT : p_interface_header_id NUMBER
1132 p_lines PO_AUTOCREATE_TYPES.lines_rec_type
1133 - Record variable to hold the line info
1134 ============================================================================ */
1135 PROCEDURE fetch_lines( p_interface_header_id IN NUMBER,
1136 p_lines OUT NOCOPY PO_AUTOCREATE_TYPES.lines_rec_type )
1137 IS
1138 l_api_name VARCHAR2(30) := 'fetch_lines';
1139 l_progress VARCHAR2(3) := '000';
1140
1141 BEGIN
1142
1143 IF g_debug_stmt THEN
1144 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1145 END IF;
1146
1147 l_progress := '010';
1148
1149 SELECT intf_lines.interface_line_id,
1150 intf_lines.interface_header_id,
1151 intf_lines.po_header_id,
1152 NULL, -- intf_lines.po_line_id,
1153 intf_lines.action,
1154 intf_lines.document_num,
1155 intf_lines.item,
1156 intf_lines.vendor_product_num,
1157 intf_lines.supplier_part_auxid,
1158 intf_lines.item_id,
1159 intf_lines.item_revision,
1160 intf_lines.job_business_group_name,
1161 intf_lines.job_business_group_id,
1162 intf_lines.job_name,
1163 intf_lines.job_id,
1164 intf_lines.category,
1165 intf_lines.category_id,
1166 intf_lines.ip_category_name,
1167 intf_lines.ip_category_id,
1168 intf_lines.uom_code,
1169 intf_lines.unit_of_measure,
1170 intf_lines.line_type,
1171 intf_lines.line_type_id,
1172 intf_lines.un_number,
1173 intf_lines.un_number_id,
1174 intf_lines.hazard_class,
1175 intf_lines.hazard_class_id,
1176 intf_lines.template_name,
1177 intf_lines.template_id,
1178 intf_lines.item_description,
1179 intf_lines.unit_price,
1180 intf_lines.base_unit_price,
1181 intf_lines.from_header_id,
1182 intf_lines.from_line_id,
1183 intf_lines.list_price_per_unit,
1184 intf_lines.market_price,
1185 intf_lines.capital_expense_flag,
1186 intf_lines.min_release_amount,
1187 intf_lines.allow_price_override_flag,
1188 intf_lines.price_type,
1189 intf_lines.price_break_lookup_code,
1190 intf_lines.closed_code,
1191 intf_lines.quantity,
1192 intf_lines.line_num,
1193 intf_lines.shipment_num,
1194 intf_lines.price_chg_accept_flag,
1195 intf_lines.effective_date,
1196 intf_lines.expiration_date,
1197 intf_lines.line_attribute14,
1198 intf_lines.price_update_tolerance,
1199 intf_lines.line_loc_populated_flag,
1200 intf_lines.negotiated_by_preparer_flag,
1201 intf_lines.amount,
1202 intf_lines.contractor_last_name,
1203 intf_lines.contractor_first_name,
1204 intf_lines.over_tolerance_error_flag,
1205 intf_lines.not_to_exceed_price,
1206 intf_lines.po_release_id,
1207 intf_lines.release_num,
1208 intf_lines.source_shipment_id,
1209 intf_lines.contract_num,
1210 intf_lines.contract_id,
1211 intf_lines.type_1099,
1212 intf_lines.closed_by,
1213 intf_lines.closed_date,
1214 intf_lines.committed_amount,
1215 intf_lines.qty_rcv_exception_code,
1216 intf_lines.weight_uom_code,
1217 intf_lines.volume_uom_code,
1218 intf_lines.secondary_unit_of_measure,
1219 intf_lines.secondary_quantity,
1220 intf_lines.preferred_grade,
1221 intf_lines.process_code,
1222 NULL, -- parent_interface_line_id
1223 intf_lines.file_line_language,
1224 intf_lines.requisition_line_id,
1225 intf_lines.group_line_id,
1226 intf_lines.line_num_display,
1227 intf_lines.clm_info_flag,
1228 intf_lines.clm_option_indicator,
1229 intf_lines.clm_base_line_num ,
1230 intf_lines.clm_option_num ,
1231 intf_lines.clm_option_from_date ,
1232 intf_lines.clm_option_to_date ,
1233 intf_lines.clm_funded_flag ,
1234 intf_lines.contract_type ,
1235 intf_lines.cost_constraint ,
1236 intf_lines.clm_idc_type, -- clm idc type
1237 intf_lines.need_by_date ,
1238 intf_lines.transaction_reason_code,
1239 intf_lines.retainage_rate ,
1240 intf_lines.consigned_flag ,
1241 intf_lines.oke_contract_version_id,
1242 intf_lines.oke_contract_header_id ,
1243 intf_lines.note_to_vendor ,
1244 intf_lines.qty_rcv_tolerance ,
1245 intf_lines.from_line_location_id ,
1246 intf_lines.auction_header_id,
1247 intf_lines.auction_display_number ,
1248 intf_lines.auction_line_number ,
1249 intf_lines.bid_number,
1250 intf_lines.bid_line_number,
1251 intf_lines.supplier_ref_number,
1252 intf_lines.max_retainage_amount,
1253 intf_lines.progress_payment_rate,
1254 intf_lines.recoupment_rate ,
1255 intf_lines.catalog_name ,
1256 intf_lines.firm_flag,
1257 intf_lines.drop_ship_flag,
1258 intf_lines.tax_code_id,
1259 intf_lines.transaction_flow_header_id,
1260 intf_lines.receipt_required_flag,
1261 intf_lines.note_to_receiver,
1262 intf_lines.vmi_flag,
1263 intf_lines.ship_to_organization_id,
1264 intf_lines.ship_to_location_id,
1265 intf_lines.promised_date,
1266 NVL2(intf_lines.advance_amount, 'Y', 'N'),
1267 intf_lines.advance_amount ,
1268 NVL(intf_lines.line_loc_populated_flag, 'N'),
1269 intf_lines.price_discount ,
1270 intf_lines.terms_id ,
1271 intf_lines.Shipment_Type ,
1272 -- standard who columns
1273 intf_lines.last_updated_by,
1274 intf_lines.last_update_date,
1275 intf_lines.last_update_login,
1276 intf_lines.creation_date,
1277 intf_lines.created_by,
1278 intf_lines.request_id,
1279 intf_lines.program_application_id,
1280 intf_lines.program_id,
1281 intf_lines.program_update_date,
1282 -- attributes read from headers
1283 intf_headers.draft_id,
1284 intf_headers.action,
1285 intf_headers.po_header_id,
1286 draft_headers.vendor_id,
1287 draft_headers.vendor_site_id,
1288 draft_headers.min_release_amount,
1289 draft_headers.start_date,
1290 draft_headers.end_date,
1291 draft_headers.global_agreement_flag,
1292 draft_headers.currency_code,
1293 draft_headers.created_language,
1294 draft_headers.style_id,
1295 draft_headers.rate_type,
1296 draft_headers.rate_date,
1297 draft_headers.rate,
1298 intf_headers.pcard_id,
1299 -- txn table columns
1300 NULL, -- order_type_lookup_code
1301 NULL, -- purchase_basis
1302 NULL, -- matching_basis
1303 NULL, -- unordered_flag
1304 NULL, -- cancel_flag
1305 NULL, -- quantity_committed
1306 NULL, -- tax_attribute_update_code
1307 FND_API.g_false, -- error_flag_tbl
1308 FND_API.g_false, -- need_to_reject_flag_tbl
1309 FND_API.g_false, -- create_line_loc_tbl
1310 -1, -- group_num
1311 NULL, -- origin_line_num
1312 FND_API.g_false, -- match_line_found
1313 NULL , -- allow_desc_update_flag_tbl
1314 NULL, -- destination_type_code_tbl
1315 NULL, -- dest_organization_id_tbl
1316 NULL, -- deliver_to_loc_id_tbl
1317 NULL, -- rc_enforce_shipto_loc_code_tbl
1318 NULL, -- rc_subst_receipt_flag_tbl
1319 NULL, -- rc_receiving_routing_id_tbl
1320 NULL, -- rc_qty_rcv_tolerance_tbl
1321 NULL, -- rc_qty_rcv_exception_code_tbl
1322 NULL, -- rc_days_early_recpt_tbl
1323 NULL, -- rc_days_late_recpt_tbl
1324 NULL, -- rc_receipt_days_excep_code_tbl
1325 NULL, -- it_list_price_per_unit_tbl
1326 NULL, -- it_market_price_tbl
1327 NULL, -- it_taxable_flag_tbl
1328 NULL, -- it_unit_meas_lookup_code_tbl
1329 NULL, -- it_inspect_req_flag_tbl
1330 NULL, -- it_receipt_req_flag_tbl
1331 NULL, -- it_invoice_tolerance_tbl
1332 NULL, -- it_rcv_tolerance_tbl
1333 NULL, -- it_secondary_uom_code_tbl
1334 NULL, -- it_grade_control_flag_tbl
1335 NULL, -- rate_for_req_fields_tbl
1336 NULL, -- taxable_flag_tbl
1337 NULL, -- org_id__tbl
1338 NULL, -- po_line_loc_id
1339 --CLM specific columns
1340 intf_lines.clm_min_total_amount,
1341 intf_lines.clm_max_total_amount,
1342 intf_lines.clm_min_total_quantity,
1343 intf_lines.clm_max_total_quantity,
1344 intf_lines.clm_min_order_amount,
1345 intf_lines.clm_max_order_amount,
1346 intf_lines.clm_min_order_quantity,
1347 intf_lines.clm_max_order_quantity,
1348 intf_lines.clm_total_amount_ordered,
1349 intf_lines.clm_total_quantity_ordered,
1350 intf_lines.clm_period_perf_end_date,
1351 intf_lines.clm_period_perf_start_date,
1352 intf_lines.clm_exercised_flag, -- Bug 9960752
1353 intf_lines.clm_exercised_date, -- Bug 9960752
1354 --CLM Phase4 Changes
1355 intf_lines.clm_exhibit_name,
1356 intf_lines.clm_delivery_event_code,
1357 intf_lines.clm_delivery_period,
1358 intf_lines.clm_promise_period,
1359 intf_lines.clm_pop_duration,
1360 intf_lines.clm_delivery_period_uom,
1361 intf_lines.clm_promise_period_uom,
1362 intf_lines.clm_pop_duration_uom
1363
1364 BULK COLLECT
1365 INTO p_lines.intf_line_id_tbl,
1366 p_lines.intf_header_id_tbl,
1367 p_lines.po_header_id_tbl,
1368 p_lines.po_line_id_tbl,
1369 p_lines.action_tbl,
1370 p_lines.document_num_tbl,
1371 p_lines.item_tbl,
1372 p_lines.vendor_product_num_tbl,
1373 p_lines.supplier_part_auxid_tbl,
1374 p_lines.item_id_tbl,
1375 p_lines.item_revision_tbl,
1376 p_lines.job_business_group_name_tbl,
1377 p_lines.job_business_group_id_tbl,
1378 p_lines.job_name_tbl,
1379 p_lines.job_id_tbl,
1380 p_lines.category_tbl,
1381 p_lines.category_id_tbl,
1382 p_lines.ip_category_tbl,
1383 p_lines.ip_category_id_tbl,
1384 p_lines.uom_code_tbl,
1385 p_lines.unit_of_measure_tbl,
1386 p_lines.line_type_tbl,
1387 p_lines.line_type_id_tbl,
1388 p_lines.un_number_tbl,
1389 p_lines.un_number_id_tbl,
1390 p_lines.hazard_class_tbl,
1391 p_lines.hazard_class_id_tbl,
1392 p_lines.template_name_tbl,
1393 p_lines.template_id_tbl,
1394 p_lines.item_desc_tbl,
1395 p_lines.unit_price_tbl,
1396 p_lines.base_unit_price_tbl,
1397 p_lines.from_header_id_tbl,
1398 p_lines.from_line_id_tbl,
1399 p_lines.list_price_per_unit_tbl,
1400 p_lines.market_price_tbl,
1401 p_lines.capital_expense_flag_tbl,
1402 p_lines.min_release_amount_tbl,
1403 p_lines.allow_price_override_flag_tbl,
1404 p_lines.price_type_tbl,
1405 p_lines.price_break_lookup_code_tbl,
1406 p_lines.closed_code_tbl,
1407 p_lines.quantity_tbl,
1408 p_lines.line_num_tbl,
1409 p_lines.shipment_num_tbl,
1410 p_lines.price_chg_accept_flag_tbl,
1411 p_lines.effective_date_tbl,
1412 p_lines.expiration_date_tbl,
1413 p_lines.attribute14_tbl,
1414 p_lines.price_update_tolerance_tbl,
1415 p_lines.line_loc_populated_flag_tbl,
1416 p_lines.negotiated_flag_tbl,
1417 p_lines.amount_tbl,
1418 p_lines.contractor_last_name_tbl,
1419 p_lines.contractor_first_name_tbl,
1420 p_lines.over_tolerance_err_flag_tbl,
1421 p_lines.not_to_exceed_price_tbl,
1422 p_lines.po_release_id_tbl,
1423 p_lines.release_num_tbl,
1424 p_lines.source_shipment_id_tbl,
1425 p_lines.contract_num_tbl,
1426 p_lines.contract_id_tbl,
1427 p_lines.type_1099_tbl,
1428 p_lines.closed_by_tbl,
1429 p_lines.closed_date_tbl,
1430 p_lines.committed_amount_tbl,
1431 p_lines.qty_rcv_exception_code_tbl,
1432 p_lines.weight_uom_code_tbl,
1433 p_lines.volume_uom_code_tbl,
1434 p_lines.secondary_unit_of_meas_tbl,
1435 p_lines.secondary_quantity_tbl,
1436 p_lines.preferred_grade_tbl,
1437 p_lines.process_code_tbl,
1438 p_lines.parent_interface_line_id_tbl,
1439 p_lines.file_line_language_tbl,
1440 p_lines.requisition_line_id_tbl,
1441 p_lines.group_line_id_tbl,
1442 p_lines.line_num_display_tbl,
1443 p_lines.clm_info_flag_tbl,
1444 p_lines.clm_option_indicator_tbl,
1445 p_lines.clm_base_line_num_tbl,
1446 p_lines.clm_option_num_tbl,
1447 p_lines.clm_option_from_date_tbl,
1448 p_lines.clm_option_to_date_tbl,
1449 p_lines.clm_funded_flag_tbl,
1450 p_lines.contract_type_tbl,
1451 p_lines.cost_constraint_tbl,
1452 p_lines.clm_idc_type_tbl,
1453 p_lines.need_by_date_tbl,
1454 p_lines.transaction_reason_code_tbl,
1455 p_lines.retainage_rate_tbl,
1456 p_lines.consigned_flag_tbl,
1457 p_lines.oke_contract_version_id_tbl,
1458 p_lines.oke_contract_header_id_tbl,
1459 p_lines.note_to_vendor_tbl,
1460 p_lines.qty_rcv_tolerance_tbl,
1461 p_lines.from_line_location_id_tbl,
1462 p_lines.auction_header_id_tbl,
1463 p_lines.auction_display_number_tbl,
1464 p_lines.auction_line_number_tbl,
1465 p_lines.bid_number_tbl,
1466 p_lines.bid_line_number_tbl ,
1467 p_lines.supplier_ref_number_tbl ,
1468 p_lines.max_retainage_amount_tbl,
1469 p_lines.progress_payment_rate_tbl,
1470 p_lines.recoupment_rate_tbl ,
1471 p_lines.catalog_name_tbl ,
1472 p_lines.firm_status_lookup_code_tbl,
1473 p_lines.drop_ship_flag_tbl,
1474 p_lines.tax_code_id_tbl,
1475 p_lines.txn_flow_header_id_tbl,
1476 p_lines.receipt_required_flag_tbl,
1477 p_lines.note_to_receiver_tbl,
1478 p_lines.vmi_flag_tbl,
1479 p_lines.line_ship_to_org_id_tbl,
1480 p_lines.line_ship_to_loc_id_tbl,
1481 p_lines.promised_date_tbl,
1482 p_lines.has_advance_flag_tbl,
1483 p_lines.advance_amount_tbl,
1484 p_lines.poll_intf_pop_flag_tbl,
1485 p_lines.price_discount_tbl,
1486 p_lines.terms_id_tbl,
1487 p_lines.Shipment_Type_tbl,
1488 -- standard who columns
1489 p_lines.last_updated_by_tbl,
1490 p_lines.last_update_date_tbl,
1491 p_lines.last_update_login_tbl,
1492 p_lines.creation_date_tbl,
1493 p_lines.created_by_tbl,
1494 p_lines.request_id_tbl,
1495 p_lines.program_application_id_tbl,
1496 p_lines.program_id_tbl,
1497 p_lines.program_update_date_tbl,
1498 -- attributes read from headers
1499 p_lines.draft_id_tbl,
1500 p_lines.hd_action_tbl,
1501 p_lines.hd_po_header_id_tbl,
1502 p_lines.hd_vendor_id_tbl,
1503 p_lines.hd_vendor_site_id_tbl,
1504 p_lines.hd_min_release_amount_tbl,
1505 p_lines.hd_start_date_tbl,
1506 p_lines.hd_end_date_tbl,
1507 p_lines.hd_global_agreement_flag_tbl,
1508 p_lines.hd_currency_code_tbl,
1509 p_lines.hd_created_language_tbl,
1510 p_lines.hd_style_id_tbl,
1511 p_lines.hd_rate_type_tbl,
1512 p_lines.hd_rate_date_tbl,
1513 p_lines.hd_rate_tbl,
1514 p_lines.hd_pcard_id_tbl,
1515 -- txn table columns
1516 p_lines.order_type_lookup_code_tbl,
1517 p_lines.purchase_basis_tbl,
1518 p_lines.matching_basis_tbl,
1519 p_lines.unordered_flag_tbl,
1520 p_lines.cancel_flag_tbl,
1521 p_lines.quantity_committed_tbl,
1522 p_lines.tax_attribute_update_code_tbl,
1523 p_lines.error_flag_tbl,
1524 p_lines.need_to_reject_flag_tbl,
1525 p_lines.create_line_loc_tbl,
1526 p_lines.group_num_tbl,
1527 p_lines.origin_line_num_tbl,
1528 p_lines.match_line_found_tbl,
1529 p_lines.allow_desc_update_flag_tbl,
1530 p_lines.destination_type_code_tbl,
1531 p_lines.dest_organization_id_tbl,
1532 p_lines.deliver_to_loc_id_tbl,
1533 p_lines.rc_enforce_shipto_loc_code_tbl,
1534 p_lines.rc_subst_receipt_flag_tbl,
1535 p_lines.rc_receiving_routing_id_tbl,
1536 p_lines.rc_qty_rcv_tolerance_tbl,
1537 p_lines.rc_qty_rcv_exception_code_tbl,
1538 p_lines.rc_days_early_recpt_tbl,
1539 p_lines.rc_days_late_recpt_tbl,
1540 p_lines.rc_receipt_days_excep_code_tbl,
1541 p_lines.it_list_price_per_unit_tbl,
1542 p_lines.it_market_price_tbl,
1543 p_lines.it_taxable_flag_tbl,
1544 p_lines.it_unit_meas_lookup_code_tbl,
1545 p_lines.it_inspect_req_flag_tbl,
1546 p_lines.it_receipt_req_flag_tbl,
1547 p_lines.it_invoice_tolerance_tbl,
1548 p_lines.it_rcv_tolerance_tbl,
1549 p_lines.it_secondary_uom_code_tbl,
1550 p_lines.it_grade_control_flag_tbl,
1551 p_lines.rate_for_req_fields_tbl,
1552 p_lines.taxable_flag_tbl,
1553 p_lines.org_id_tbl,
1554 p_lines.line_loc_id_tbl,
1555 --CLM specific columns
1556 p_lines.clm_min_total_amount_tbl,
1557 p_lines.clm_max_total_amount_tbl,
1558 p_lines.clm_min_total_quantity_tbl,
1559 p_lines.clm_max_total_quantity_tbl,
1560 p_lines.clm_min_order_amount_tbl,
1561 p_lines.clm_max_order_amount_tbl,
1562 p_lines.clm_min_order_quantity_tbl,
1563 p_lines.clm_max_order_quantity_tbl,
1564 p_lines.clm_total_amount_ordered_tbl,
1565 p_lines.clm_total_quantity_ordered_tbl,
1566 p_lines.clm_period_perf_end_date_tbl,
1567 p_lines.clm_period_perf_start_date_tbl,
1568 p_lines.clm_exercised_flag_tbl, -- Bug 9960752
1569 p_lines.clm_exercised_date_tbl, -- Bug 9960752
1570 --CLM Phase4 Changes
1571 p_lines.clm_exhibit_name_tbl,
1572 p_lines.clm_delivery_event_code_tbl,
1573 p_lines.clm_delivery_period_tbl,
1574 p_lines.clm_promise_period_tbl,
1575 p_lines.clm_pop_duration_tbl,
1576 p_lines.clm_delivery_period_uom_tbl,
1577 p_lines.clm_promise_period_uom_tbl,
1578 p_lines.clm_pop_duration_uom_tbl
1579 FROM po_lines_interface intf_lines,
1580 po_headers_interface intf_headers,
1581 po_headers_draft_all draft_headers
1582 WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
1583 AND intf_headers.draft_id = draft_headers.draft_id
1584 AND intf_headers.po_header_id = draft_headers.po_header_id
1585 AND intf_lines.interface_header_id = p_interface_header_id
1586 ORDER BY intf_lines.line_num,
1587 NVL(intf_lines.shipment_num,0),
1588 intf_lines.unit_price,
1589 intf_lines.interface_line_id;
1590
1591 l_progress := '020';
1592
1593 IF g_debug_stmt THEN
1594 PO_DEBUG.debug_stmt( p_log_head => g_log_head||l_api_name ,p_token => l_progress ,p_message => 'No of Records Fetched : ' || p_lines.intf_line_id_tbl.Count);
1595 END IF;
1596
1597 IF g_debug_stmt THEN
1598 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1599 END IF;
1600
1601 EXCEPTION
1602 WHEN OTHERS THEN
1603 IF g_debug_unexp THEN
1604 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1605 END IF;
1606 --CLM Phase 2 changes : error handling
1607 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_FETCH_ERR',x_token1_value => sqlerrm);
1608
1609 po_message_s.sql_error('FETCH_LINES',l_progress,SQLCODE);
1610 PO_AUTOCREATE_PVT.wrapup();
1611 RAISE;
1612 END fetch_lines;
1613
1614 /* ============================================================================
1615 NAME: derive_and_default_lines
1616 DESC: Peform derive and defaulting on line records.
1617 ARGS: IN OUT : p_lines PO_AUTOCREATE_TYPES.lines_rec_type
1618 ALGM: Following attributes are derived / defaulted for each line record.
1619 -- To be verified.
1620 * Based on the requisition OU/Currency and the PO OU/Currency
1621 determine the rate(s).
1622 * Derive the line type attributes
1623 * If the source document / document to be added has different UOM,
1624 convert the quantity based on the UOM conversion.
1625 * Default the item attributes (list price, market price and etc)
1626 * Default the negotiated_by_preparer_flag based on the source
1627 document and caller
1628 * Convert the secondary quantity/uom
1629 * derive the retainage rate.
1630 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1631 NOTE: Use the following procedures to derive the lines attributes:
1632 - RCV_CORE_S.get_receiving_controls
1633 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1634 ==============================================================================*/
1635
1636 PROCEDURE derive_and_default_lines(p_lines IN OUT NOCOPY po_autocreate_types.lines_rec_type )
1637 IS
1638
1639 g_calculate_tax_flag VARCHAR2(100);
1640 l_api_name VARCHAR2 (30) := 'derive_and_default_lines';
1641 l_progress VARCHAR2 (3) := '000';
1642 l_requesting_ou_id po_requisition_lines_all.org_id%TYPE;
1643 l_requisition_id_tbl NUMBER;
1644 l_price_break_id NUMBER;
1645 l_cc_unit_price NUMBER;
1646 l_cc_base_unit_price NUMBER;
1647 l_cc_amount NUMBER;
1648 --<INVCONV R12 START>
1649 x_secondary_unit_def MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
1650 x_secondary_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
1651 x_secondary_quantity_def PO_LINES.SECONDARY_QUANTITY%TYPE;
1652 x_preferred_grade_def MTL_GRADES.GRADE_CODE%TYPE;
1653 l_quantity_temp PO_LINES.QUANTITY%TYPE;
1654 --<INVCONV R12 END>
1655 l_base_unit_price po_lines.base_unit_price%TYPE := NULL; -- <FPJ Advanced Price>
1656 l_contractor_status PO_REQUISITION_LINES_ALL.contractor_status%TYPE;
1657 l_negotiated_by_preparer_flag po_lines_all.negotiated_by_preparer_flag%type; --<DBI FPJ>
1658 l_type_lookup_code po_headers_all.type_lookup_code%type; --<DBI FPJ>
1659 l_global_agreement_flag po_headers_all.global_agreement_flag%type; --<DBI FPJ>
1660 l_needby_prf VARCHAR2(1);
1661 l_shipto_prf VARCHAR2(1);
1662 x_quote_header_id NUMBER := NULL;
1663 x_quote_line_id NUMBER := NULL;
1664 l_routing_name VARCHAR2 (100);
1665 g_chktype_TRACKING_QTY_IND CONSTANT MTL_SYSTEM_ITEMS_B.TRACKING_QUANTITY_IND%TYPE := 'PS';
1666 l_manual_price_change_flag po_lines_all.manual_price_change_flag%TYPE := NULL;
1667 l_from_type_lookup_code PO_HEADERS.type_lookup_code%type;
1668 l_uom_convert VARCHAR2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
1669 l_ga_uom PO_LINES.unit_meas_lookup_code%TYPE;
1670 l_quantity_in_ga_uom PO_LINES_INTERFACE.quantity%TYPE;
1671 l_conversion_rate NUMBER :=1;
1672 l_outsourced_assembly po_line_locations_all.outsourced_assembly%type;
1673 l_retainage_rate PO_VENDOR_SITES_ALL.retainage_rate%type;
1674 -- <SERVICES FPJ START>
1675 l_job_long_description PO_REQUISITION_LINES_ALL.job_long_description%TYPE;
1676 l_who_rec PO_NEGOTIATIONS_SV2.who_rec_type;
1677 l_return_status VARCHAR2(1);
1678 l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
1679 l_purchase_basis1 PO_LINE_TYPES_B.purchase_basis%TYPE;
1680 l_matching_basis PO_LINE_TYPES_B.matching_basis%TYPE;
1681 l_category_id PO_LINE_TYPES_B.category_id%TYPE;
1682 l_unit_meas_lookup_code PO_LINE_TYPES_B.unit_of_measure%TYPE;
1683 l_unit_price PO_LINE_TYPES_B.unit_price%TYPE;
1684 l_outside_operation_flag PO_LINE_TYPES_B.outside_operation_flag%TYPE;
1685 l_receiving_flag PO_LINE_TYPES_B.receiving_flag%TYPE;
1686 l_receive_close_tolerance PO_LINE_TYPES_B.receive_close_tolerance%TYPE;
1687 -- <SERVICES FPJ END>
1688 l_db_quantity NUMBER;
1689 l_min_shipment_num NUMBER;
1690 l_need_by_date DATE;
1691 l_ship_to_org NUMBER;
1692 l_ship_to_loc NUMBER;
1693 /* obtain currency info to adjust precision */
1694 x_precision NUMBER := '';
1695 x_ext_precision NUMBER := '';
1696 x_min_unit NUMBER := '';
1697 x_unit_price po_lines.unit_price%TYPE := NULL;
1698 x_purchase_basis VARCHAR2 (100);
1699 x_po_line_id NUMBER;
1700 x_po_line_type_id NUMBER;
1701 x_line_num po_lines.line_num%type;
1702 x_po_item_id NUMBER;
1703 x_order_type_lookup_code VARCHAR2(25);
1704 l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE;
1705 x_po_item_revision po_lines.item_revision%type;
1706 x_po_unit_meas_lookup_code po_lines.unit_meas_lookup_code%type;
1707 x_po_unit_price NUMBER;
1708 x_po_transaction_reason_code po_lines.transaction_reason_code%type;
1709 x_price_break_lookup_code po_lines.price_break_lookup_code%type;
1710 x_quantity NUMBER := '';
1711 --<SOURCING TO PO FPH START>
1712 x_column1 VARCHAR2(10);
1713 x_result VARCHAR2(7);
1714 update_req_pool_fail EXCEPTION;
1715 x_hazard_class_id NUMBER :=NULL;
1716 x_un_number_id NUMBER :=NULL;
1717 x_unit_of_measure po_line_types.unit_of_measure%type:=NULL;
1718 --The following flag indicates whether copying the attachments from (all)the
1719 --sourcing entities need to be suppressed due to the grouping of lines.
1720 x_attch_suppress_flag VARCHAR2(1) :='N';
1721 l_enhanced_pricing_flag po_doc_style_headers.enhanced_pricing_flag%type;
1722 l_pricing_call_src VARCHAR2(5);
1723 l_param_taxable_flag VARCHAR2(1);-- params.taxable_flag
1724 l_price_diff_idx NUMBER := 1;
1725 --autocreate grouping start
1726 x_results PO_VALIDATION_RESULTS_TYPE;
1727 l_lock_exception EXCEPTION;
1728 l_entity_name_tbl PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
1729 l_pk1_tbl PO_TBL_VARCHAR240 := PO_TBL_VARCHAR240();
1730 l_pk2_tbl PO_TBL_VARCHAR240 := PO_TBL_VARCHAR240();
1731 l_pk3_tbl PO_TBL_VARCHAR240 := PO_TBL_VARCHAR240();
1732 l_pk4_tbl PO_TBL_VARCHAR240 := PO_TBL_VARCHAR240();
1733 --autocreate grouping end
1734
1735 CURSOR c_price_diff (p_source_entity_type IN VARCHAR2,p_source_entity_id IN NUMBER)
1736 IS
1737 SELECT PO_PRICE_DIFF_INTERFACE_S.NEXTVAL price_diff_interface_id ,
1738 PD.price_differential_num ,
1739 PD.price_type ,
1740 DECODE ( PD.entity_type , 'REQ LINE' , PD.multiplier , 'PO LINE' , PD.multiplier , 'PRICE BREAK' , PD.min_multiplier , 'BLANKET LINE' , PD.min_multiplier ) multiplier ,
1741 PD.enabled_flag
1742 FROM po_price_differentials PD
1743 WHERE PD.entity_type = p_source_entity_type
1744 AND PD.entity_id = p_source_entity_id
1745 AND NVL(PD.enabled_flag,'N') = 'Y';
1746
1747 l_source_entity_type PO_PRICE_DIFFERENTIALS.entity_type%TYPE;
1748 l_source_entity_id PO_PRICE_DIFFERENTIALS.entity_id%TYPE;
1749
1750 CURSOR c_price_break(p_line_num NUMBER,p_intf_line_id NUMBER)
1751 IS
1752 SELECT interface_line_id,
1753 need_by_date
1754 FROM po_lines_interface
1755 WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
1756 AND line_num = p_line_num
1757 AND shipment_num IS NOT NULL
1758 AND interface_line_id <> p_intf_line_id
1759 ORDER BY shipment_num;
1760
1761 x_pb_intf_line_id NUMBER;
1762 x_pb_ship_to_loc NUMBER;
1763 x_pb_destination_type_code VARCHAR2(30);
1764 l_match_line_num NUMBER;
1765 l_match_line_found BOOLEAN := FALSE;
1766
1767 BEGIN
1768 IF g_debug_stmt THEN
1769 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1770 PO_DEBUG.debug_stmt( p_log_head => g_log_head||l_api_name, p_token => l_progress,
1771 p_message => 'Document Type : '|| PO_AUTOCREATE_PARAMS.g_document_type ||
1772 ' Document_subtype : '|| PO_AUTOCREATE_PARAMS.g_document_subtype ||
1773 ' Interface Lines Count ' || p_lines.intf_line_id_tbl.Count );
1774 END IF;
1775
1776 l_progress := '010';
1777
1778 IF (PO_AUTOCREATE_PARAMS.g_document_type = 'PO' AND PO_AUTOCREATE_PARAMS.g_document_subtype IN ('STANDARD', 'PLANNED', 'RELEASE')) THEN
1779 g_calculate_tax_flag := 'Y';
1780 END IF;
1781
1782 l_progress := '020' ;
1783
1784 FOR i IN 1 .. p_lines.intf_line_id_tbl.Count
1785 LOOP
1786
1787 x_quantity := NULL;
1788 x_unit_of_measure := NULL;
1789 l_enhanced_pricing_flag := NULL;
1790 l_pricing_call_src := NULL;
1791 l_outsourced_assembly := NULL;
1792 x_po_line_id := NULL;
1793 x_un_number_id := NULL;
1794 x_unit_price := NULL;
1795 l_base_unit_price := NULL;
1796 l_price_break_id := NULL;
1797 x_hazard_class_id := NULL;
1798 l_source_entity_type := NULL;
1799 l_source_entity_id := NULL;
1800 l_order_type_lookup_code := NULL;
1801 l_purchase_basis1 := NULL;
1802 l_matching_basis := NULL;
1803 l_category_id := NULL;
1804 l_unit_meas_lookup_code := NULL;
1805 l_unit_price := NULL;
1806 l_outside_operation_flag := NULL;
1807 l_receiving_flag := NULL;
1808 l_receive_close_tolerance := NULL;
1809 l_type_lookup_code := NULL;
1810 l_global_agreement_flag := NULL;
1811 l_negotiated_by_preparer_flag := NULL;
1812 x_secondary_uom := NULL;
1813 x_secondary_unit_def := NULL;
1814 x_preferred_grade_def := NULL;
1815 l_retainage_rate := NULL;
1816 l_db_quantity := NULL;
1817 l_ship_to_loc := NULL;
1818 l_ship_to_org := NULL;
1819 l_need_by_date := NULL;
1820 l_ga_uom := NULL;
1821 l_from_type_lookup_code := NULL;
1822 l_routing_name := NULL;
1823 x_order_type_lookup_code := NULL;
1824 l_purchase_basis := NULL;
1825 x_precision := NULL;
1826 x_ext_precision := NULL;
1827 x_min_unit := NULL;
1828 x_po_line_id := NULL;
1829 x_po_line_type_id := NULL;
1830 x_line_num := NULL;
1831 x_po_item_id := NULL;
1832 x_po_item_revision := NULL;
1833 x_po_unit_meas_lookup_code := NULL;
1834 l_base_unit_price := NULL;
1835 x_po_unit_price := NULL;
1836 x_po_transaction_reason_code := NULL;
1837 x_price_break_lookup_code := NULL;
1838 l_manual_price_change_flag := NULL;
1839 l_requesting_ou_id := NULL;
1840 l_conversion_rate := NULL;
1841 l_match_line_num := NULL;
1842 l_match_line_found := FALSE;
1843 x_pb_intf_line_id := NULL;
1844 x_pb_ship_to_loc := NULL;
1845
1846 -- Get the Ship to org and deliver to location id details:
1847 IF (NVL(p_lines.clm_info_flag_tbl(i),'N') ='N') THEN
1848 l_progress := '030';
1849 get_interface_shipto_info(p_lines.intf_line_id_tbl(i) ,
1850 p_lines.destination_type_code_tbl(i) ,
1851 p_lines.dest_organization_id_tbl(i) ,
1852 p_lines.deliver_to_loc_id_tbl(i));
1853 END IF;
1854
1855 l_progress := '040';
1856
1857 -- <ACHTML R12 START>
1858 -- Determine the Requesting OU from the current requisition line.
1859 -- If there is no backing req, take the globally determined Requesting OU.
1860 IF (p_lines.requisition_line_id_tbl (i) IS NOT NULL) THEN
1861 l_progress := '050';
1862 BEGIN
1863 l_progress := '051';
1864 SELECT prl.org_id
1865 INTO l_requesting_ou_id
1866 FROM po_requisition_lines_all prl
1867 WHERE prl.requisition_line_id = p_lines.requisition_line_id_tbl (i);
1868 EXCEPTION
1869 WHEN OTHERS THEN
1870 IF g_debug_unexp THEN
1871 po_debug.debug_exc (p_log_head => g_log_head || l_api_name, p_progress => l_progress );
1872 END IF;
1873 PO_AUTOCREATE_PVT.wrapup();
1874 po_message_s.sql_error('CREATE_LINE',l_progress,SQLCODE);
1875 RAISE;
1876 END;
1877 ELSE
1878 l_progress := '052';
1879 l_requesting_ou_id := PO_AUTOCREATE_PARAMS.g_hdr_requesting_ou_id;
1880 END IF;
1881
1882 -- Determine the currency conversion rate for the current line.
1883 -- If called from Sourcing, simply use the rate specified in the interface
1884 -- table.
1885 IF ( PO_AUTOCREATE_PARAMS.g_interface_source_code <> 'SOURCING'
1886 AND PO_AUTOCREATE_PARAMS.g_purchasing_ou_id <> l_requesting_ou_id
1887 AND PO_AUTOCREATE_PARAMS.g_document_subtype = 'STANDARD'
1888 AND NVL (p_lines.clm_info_flag_tbl (i), 'N') <> 'Y' ) THEN
1889 l_progress := '060';
1890 get_rate_for_req_price (p_requesting_ou_id => l_requesting_ou_id,
1891 p_purchasing_ou_id => PO_AUTOCREATE_PARAMS.g_purchasing_ou_id,
1892 p_po_currency_code => p_lines.hd_currency_code_tbl(i),
1893 p_rate_type => p_lines.hd_rate_type_tbl(i),
1894 p_rate_date => p_lines.hd_rate_date_tbl(i),
1895 x_rate => p_lines.rate_for_req_fields_tbl(i) );
1896 END IF;
1897
1898 IF (p_lines.rate_for_req_fields_tbl(i) IS NULL) THEN
1899 p_lines.rate_for_req_fields_tbl(i) := NVL (PO_AUTOCREATE_PARAMS.g_rate_for_req_fields, 1);
1900 END IF;
1901 /* initialize values */
1902 x_quantity := p_lines.quantity_tbl(i);
1903 x_unit_of_measure := p_lines.unit_of_measure_tbl(i); --uom_code_tbl
1904
1905 IF (p_lines.line_type_id_tbl (i) IS NOT NULL) THEN
1906 l_progress := '070';
1907 SELECT order_type_lookup_code,
1908 purchase_basis
1909 INTO x_order_type_lookup_code,
1910 l_purchase_basis
1911 FROM po_line_types
1912 WHERE line_type_id = p_lines.line_type_id_tbl (i);
1913
1914 IF g_debug_stmt THEN
1915 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
1916 p_message => 'x_order_type_lookup_code: '|| x_order_type_lookup_code ||' l_purchase_basis :' || l_purchase_basis);
1917 END IF;
1918 END IF;
1919
1920 l_progress := '080';
1921
1922 IF p_lines.hd_currency_code_tbl(i) IS NOT NULL THEN
1923 fnd_currency.get_info (p_lines.hd_currency_code_tbl(i),
1924 x_precision,
1925 x_ext_precision,
1926 x_min_unit);
1927 END IF;
1928
1929 /*Bug 13855350 : While autocreating from BWC, clm_idc_type is stamped as null,
1930 when called from sourcing if outcome is award value is IDC_NA
1931 else appropriate value from picklist is chosen
1932 Hence the default value for award is IDC_NA*/
1933 if (PO_AUTOCREATE_PARAMS.g_interface_source_code <> 'SOURCING') then
1934 p_lines.clm_idc_type_tbl(i) := nvl(p_lines.clm_idc_type_tbl(i),'IDC_NA');
1935 end if;
1936
1937 IF(PO_AUTOCREATE_PARAMS.g_document_subtype='STANDARD' OR PO_AUTOCREATE_PARAMS.g_document_subtype='PLANNED'
1938 OR PO_AUTOCREATE_PARAMS.g_document_subtype = 'BLANKET') THEN
1939
1940 BEGIN
1941 l_progress := '090';
1942
1943 SELECT po_line_id,
1944 line_type_id,
1945 line_num,
1946 item_id,
1947 item_revision,
1948 unit_meas_lookup_code,
1949 base_unit_price, -- <FPJ Advanced Price>
1950 unit_price,
1951 transaction_reason_code,
1952 price_break_lookup_code,
1953 manual_price_change_flag
1954 INTO x_po_line_id,
1955 x_po_line_type_id,
1956 x_line_num,
1957 x_po_item_id,
1958 x_po_item_revision,
1959 x_po_unit_meas_lookup_code,
1960 l_base_unit_price, -- <FPJ Advanced Price>
1961 x_po_unit_price,
1962 x_po_transaction_reason_code,
1963 x_price_break_lookup_code,
1964 l_manual_price_change_flag
1965 FROM PO_LINES_draft_all
1966 WHERE PO_HEADER_ID = po_autocreate_params.g_po_header_id
1967 AND LINE_NUM = p_lines.line_num_tbl(i)
1968 AND draft_id =po_autocreate_params.g_draft_id;
1969
1970 p_lines.po_line_id_tbl(i) := x_po_line_id;
1971 p_lines.po_header_id_tbl(i) := po_autocreate_params.g_po_header_id;
1972 IF g_debug_stmt THEN
1973 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Matching Line Found : '|| x_po_line_id);
1974 END IF;
1975
1976 EXCEPTION
1977 WHEN NO_DATA_FOUND THEN
1978 IF g_debug_stmt THEN
1979 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
1980 p_message => 'NO_DATA_FOUND: '||SQLERRM);
1981 END IF;
1982 WHEN OTHERS THEN
1983 IF g_debug_stmt THEN
1984 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
1985 p_message => SQLERRM);
1986 END IF;
1987 END;
1988 END IF;
1989
1990 l_progress := '100';
1991
1992 IF (NVL(p_lines.clm_info_flag_tbl(i),'N')<>'Y') THEN
1993
1994 l_progress := '100';
1995
1996 RCV_CORE_S.get_receiving_controls ( p_order_type_lookup_code => l_order_type_lookup_code ,
1997 p_purchase_basis => l_purchase_basis ,
1998 p_line_location_id => NULL ,
1999 p_item_id => p_lines.item_id_tbl(i) ,
2000 p_org_id => NVL(p_lines.dest_organization_id_tbl(i),
2001 po_autocreate_params.g_sys.master_inv_org_id) ,
2002 p_vendor_id => p_lines.hd_vendor_id_tbl(i) ,
2003 p_drop_ship_flag => p_lines.drop_ship_flag_tbl(i) ,
2004 x_enforce_ship_to_loc_code => p_lines.rc_enforce_shipto_loc_code_tbl(i) ,
2005 x_allow_substitute_receipts => p_lines.rc_subst_receipt_flag_tbl(i) ,
2006 x_routing_id => p_lines.rc_receiving_routing_id_tbl(i) ,
2007 x_routing_name => l_routing_name ,
2008 x_qty_rcv_tolerance => p_lines.rc_qty_rcv_tolerance_tbl(i) ,
2009 x_qty_rcv_exception_code => p_lines.rc_qty_rcv_exception_code_tbl(i) ,
2010 x_days_early_receipt_allowed => p_lines.rc_days_early_recpt_tbl(i) ,
2011 x_days_late_receipt_allowed => p_lines.rc_days_late_recpt_tbl(i) ,
2012 x_receipt_days_exception_code => p_lines.rc_receipt_days_excep_code_tbl(i) );
2013
2014 IF g_debug_stmt THEN
2015 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'After Getting receiving controls');
2016 END IF;
2017 END IF;
2018
2019 -- When autocreating a PO that references a GA, and the req line and
2020 -- GA line have different UOM's, convert to the GA's UOM if the
2021 -- UOM Convert profile is Yes. If UOM Convert is No, do not create
2022 -- this line.
2023 IF (p_lines.from_line_id_tbl(i) IS NOT NULL) AND (PO_AUTOCREATE_PARAMS.g_document_subtype = 'STANDARD') THEN
2024
2025 l_progress := '110';
2026
2027 BEGIN
2028 SELECT pol.unit_meas_lookup_code,
2029 poh.type_lookup_code
2030 INTO l_ga_uom,
2031 l_from_type_lookup_code
2032 FROM po_lines_all pol,
2033 po_headers_all poh
2034 WHERE pol.po_line_id = p_lines.from_line_id_tbl(i)
2035 AND poh.po_header_id =p_lines.from_header_id_tbl(i)
2036 AND poh.po_header_id =pol.po_header_id;
2037 EXCEPTION
2038 WHEN OTHERS THEN
2039 IF g_debug_unexp THEN
2040 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2041 END IF;
2042
2043 --CLM Phase 2 changes : error handling
2044 PO_AUTOCREATE_PVT.report_error('PO_AUTO_SRC_DOC_ERR',
2045 x_token1_value => sqlerrm,
2046 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
2047
2048 po_message_s.sql_error('CREATE_LINE',l_progress,SQLCODE);
2049 PO_AUTOCREATE_PVT.wrapup();
2050 raise;
2051 END;
2052
2053 l_progress := '120';
2054
2055 IF g_debug_stmt THEN
2056 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2057 p_message => 'req uom: '||p_lines.unit_of_measure_tbl(i)||
2058 ' uom: '||l_ga_uom ||
2059 'document type: ' || l_from_type_lookup_code);
2060 END IF;
2061
2062 IF p_lines.unit_of_measure_tbl(i) <> l_ga_uom THEN
2063 l_progress := '130';
2064
2065 IF (NVL(l_uom_convert,'N') = 'Y' OR l_from_type_lookup_code='QUOTATION') THEN
2066 -- Convert to the GA's UOM
2067 l_conversion_rate := po_uom_s.po_uom_convert(p_lines.unit_of_measure_tbl(i), l_ga_uom, p_lines.item_id_tbl(i));
2068 x_quantity := ROUND(x_quantity * l_conversion_rate , 15);
2069 x_unit_of_measure := l_ga_uom;
2070
2071 ELSE -- UOM Convert is No, so do not create this line.
2072
2073 IF g_debug_stmt THEN
2074 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2075 p_message => 'Create_line: Requisition UOM is different from GA UOM, and the Convert UOM profile is No. This PO line will not be created.');
2076 END IF;
2077 RETURN;
2078 END IF;
2079 END IF; -- interface.unit_meas_lookup_code <> l_ga_uom
2080 END IF; -- interface.from_line_id IS NOT NULL ...
2081
2082 --Enhanced Pricing Start: Check if pricing enhanced for the current style and set l_pricing_call_src>
2083 BEGIN
2084 l_progress := '140';
2085
2086 SELECT NVL(SH.enhanced_pricing_flag,'N')
2087 INTO l_enhanced_pricing_flag
2088 FROM po_doc_style_headers SH
2089 WHERE SH.style_id = p_lines.hd_style_id_tbl(i);
2090
2091 EXCEPTION
2092 WHEN OTHERS THEN
2093 l_enhanced_pricing_flag := 'N';
2094 END;
2095
2096 --l_pricing_call_src is used to distinguish pricing calls from auto creation.
2097 --Also it is assumed that
2098 IF (l_enhanced_pricing_flag = 'Y') THEN
2099 l_pricing_call_src := 'AUTO';
2100 ELSE
2101 l_pricing_call_src := NULL;
2102 END IF;
2103
2104 --<Enhanced Pricing End: >
2105 /* If item is not null get list price and taxable flag */
2106 IF(p_lines.item_id_tbl(i) IS NOT NULL) THEN
2107 l_progress :='150';
2108
2109 p_lines.it_list_price_per_unit_tbl(i) := p_lines.unit_price_tbl(i);
2110 l_outsourced_assembly := po_core_s.get_outsourced_assembly(p_lines.item_id_tbl(i),
2111 p_lines.dest_organization_id_tbl(i));
2112 /* made the receive close and invoice close tolerance to be picked up
2113 * from the lowest existing level by splitting the select.
2114 */
2115 /*
2116 Prior to the fix we were getting the values of receipt required
2117 flag and inspection required flag of the item/master org to
2118 default in the autocreated document and were not considering the
2119 values defined at item/destination organization.
2120 Now, we derive the values from the item/destination organization
2121 and if it is not defined at the item/destination organization
2122 level, then we derive the values from the item/master organization.
2123 */
2124 p_lines.it_list_price_per_unit_tbl(i) :=NULL;
2125 p_lines.it_market_price_tbl(i) :=NULL;
2126 p_lines.it_taxable_flag_tbl(i) :=NULL;
2127 p_lines.it_unit_meas_lookup_code_tbl(i) :=NULL;
2128 p_lines.it_inspect_req_flag_tbl(i) :=NULL;
2129 p_lines.it_receipt_req_flag_tbl(i) :=NULL;
2130 p_lines.it_invoice_tolerance_tbl(i) :=NULL;
2131 p_lines.it_rcv_tolerance_tbl(i) :=NULL;
2132 p_lines.it_secondary_uom_code_tbl(i) := NULL; --<INVCONV R12>
2133 p_lines.it_grade_control_flag_tbl(i) :=NULL;
2134
2135 BEGIN
2136 l_progress :='160';
2137 SELECT msi.invoice_close_tolerance,
2138 msi.receive_close_tolerance,
2139 msi.inspection_required_flag,
2140 msi.receipt_required_flag
2141 INTO p_lines.it_invoice_tolerance_tbl(i),
2142 p_lines.it_rcv_tolerance_tbl(i),
2143 p_lines.it_inspect_req_flag_tbl(i),
2144 p_lines.it_receipt_req_flag_tbl(i)
2145 FROM mtl_system_items msi
2146 WHERE msi.inventory_item_id = p_lines.item_id_tbl(i)
2147 AND msi.organization_id = p_lines.dest_organization_id_tbl(i);
2148 EXCEPTION
2149 WHEN no_data_found THEN
2150 IF g_debug_stmt THEN
2151 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: '||SQLERRM);
2152 END IF;
2153 WHEN OTHERS THEN
2154 IF g_debug_unexp THEN
2155 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2156 END IF;
2157
2158 --CLM Phase 2 changes : error handling
2159 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_ITEM_ERR',
2160 x_token1_value => sqlerrm,
2161 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
2162
2163 PO_AUTOCREATE_PVT.wrapup();
2164 po_message_s.sql_error('Get Item/Org defaults',l_progress,SQLCODE);
2165 raise;
2166 END;
2167
2168 BEGIN
2169 l_progress := '170';
2170
2171 SELECT DECODE(x_order_type_lookup_code, 'QUANTITY', msi.list_price_per_unit/NVL(p_lines.hd_rate_tbl(i),1), 1), --<Shared Proc FPJ>
2172 DECODE(x_order_type_lookup_code, 'QUANTITY', msi.market_price /NVL(p_lines.hd_rate_tbl(i),1), 1), --<Shared Proc FPJ>
2173 msi.taxable_flag,
2174 msi.primary_uom_code,
2175 NVL(p_lines.it_inspect_req_flag_tbl(i),msi.inspection_required_flag),
2176 NVL(p_lines.it_receipt_req_flag_tbl(i),msi.receipt_required_flag),
2177 NVL(p_lines.it_invoice_tolerance_tbl(i),msi.invoice_close_tolerance),
2178 NVL(p_lines.it_rcv_tolerance_tbl(i),msi.receive_close_tolerance),
2179 DECODE(msi.tracking_quantity_ind, g_chktype_TRACKING_QTY_IND, msi.secondary_uom_code,NULL),--<INVCONV R12>
2180 NVL(msi.grade_control_flag,'N') --<INVCONV R12>
2181 INTO p_lines.it_list_price_per_unit_tbl(i),
2182 p_lines.it_market_price_tbl(i),
2183 p_lines.it_taxable_flag_tbl(i),
2184 p_lines.it_unit_meas_lookup_code_tbl(i),
2185 p_lines.it_inspect_req_flag_tbl(i),
2186 p_lines.it_receipt_req_flag_tbl(i),
2187 p_lines.it_invoice_tolerance_tbl(i),
2188 p_lines.it_rcv_tolerance_tbl(i),
2189 p_lines.it_secondary_uom_code_tbl(i), --<INVCONV R12>
2190 p_lines.it_grade_control_flag_tbl(i) --<INVCONV R12>
2191 FROM mtl_system_items msi
2192 WHERE msi.inventory_item_id = p_lines.item_id_tbl(i)
2193 AND msi.organization_id = po_autocreate_params.g_sys.master_inv_org_id;
2194
2195 IF g_debug_stmt THEN
2196 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2197 p_message => 'Item List Price : ' || p_lines.it_list_price_per_unit_tbl(i)
2198 ||'Item Market Price : ' || p_lines.it_market_price_tbl(i) );
2199 END IF;
2200 EXCEPTION
2201 WHEN no_data_found THEN
2202 IF g_debug_stmt THEN
2203 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: '||SQLERRM);
2204 END IF;
2205 WHEN OTHERS THEN
2206 IF g_debug_unexp THEN
2207 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2208 END IF;
2209
2210 --CLM Phase 2 changes : error handling
2211 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_ITEM_ERR',
2212 x_token1_value => sqlerrm,
2213 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
2214 PO_AUTOCREATE_PVT.wrapup();
2215 po_message_s.sql_error('Get Item/Master org defaults',l_progress,SQLCODE);
2216 raise;
2217 END;
2218 ELSE
2219 /* this case will reach when the item_id is null */
2220 /* We need to initialize market_price also */
2221 /*In case of One-time items i.e Item_id is NUll , the
2222 list price needed to be reinitialized.
2223 */
2224 p_lines.it_market_price_tbl(i) := '';
2225 p_lines.it_taxable_flag_tbl(i) := '';
2226 p_lines.it_unit_meas_lookup_code_tbl(i) := '';
2227 p_lines.it_inspect_req_flag_tbl(i) := '';
2228 p_lines.it_receipt_req_flag_tbl(i) := '';
2229 p_lines.it_invoice_tolerance_tbl(i) := '';
2230 p_lines.it_rcv_tolerance_tbl(i) := '';
2231 p_lines.it_list_price_per_unit_tbl(i) := '';
2232 p_lines.it_secondary_uom_code_tbl(i) := ''; --<INVCONV R12>
2233 p_lines.it_grade_control_flag_tbl(i) := ''; --<INVCONV R12>
2234 END IF; -- item id not null
2235
2236 BEGIN
2237 l_progress := '180';
2238
2239 SELECT NVL(p_lines.it_rcv_tolerance_tbl(i),receipt_close),
2240 NVL(p_lines.it_receipt_req_flag_tbl(i),receiving_flag)
2241 INTO p_lines.it_rcv_tolerance_tbl(i),
2242 p_lines.it_receipt_req_flag_tbl(i)
2243 FROM po_line_types_v
2244 WHERE line_type_id = p_lines.line_type_id_tbl(i);
2245
2246 EXCEPTION
2247 WHEN no_data_found THEN
2248 IF g_debug_stmt THEN
2249 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: '||SQLERRM);
2250 END IF;
2251 WHEN OTHERS THEN
2252 IF g_debug_unexp THEN
2253 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2254 END IF;
2255
2256 --CLM Phase 2 changes : error handling
2257 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_ONE_TIME_ITEM_ERR',
2258 x_token1_value => sqlerrm,
2259 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
2260
2261 PO_AUTOCREATE_PVT.wrapup();
2262
2263 po_message_s.sql_error('Get Line type default',l_progress,SQLCODE);
2264 raise;
2265 END;
2266
2267 /* Select receipt required flag,inspection required flag
2268 at vendor level before system option level to complete the
2269 default logic
2270 */
2271 BEGIN
2272 l_progress := '190';
2273 SELECT NVL(p_lines.it_inspect_req_flag_tbl(i), vendor.INSPECTION_REQUIRED_FLAG),
2274 NVL(p_lines.it_receipt_req_flag_tbl(i), vendor.RECEIPT_REQUIRED_FLAG)
2275 INTO p_lines.it_inspect_req_flag_tbl(i),
2276 p_lines.it_receipt_req_flag_tbl(i)
2277 FROM po_vendors vendor
2278 WHERE vendor.vendor_id = p_lines.hd_vendor_id_tbl(i);
2279 EXCEPTION
2280 WHEN no_data_found THEN
2281 IF g_debug_stmt THEN
2282 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: '||SQLERRM);
2283 END IF;
2284 WHEN OTHERS THEN
2285 IF g_debug_unexp THEN
2286 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2287 END IF;
2288
2289 --CLM Phase 2 changes : error handling
2290 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_VENDOR_ERR',
2291 x_token1_value => sqlerrm,
2292 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
2293
2294 PO_AUTOCREATE_PVT.wrapup();
2295 po_message_s.sql_error('Get vendor default',l_progress,SQLCODE);
2296 raise;
2297 END;
2298
2299 l_progress := '200';
2300 /* Select receipt required flag,inspection required flag
2301 receipt close tolerance and insp close tolerance
2302 also from po system parameters if not defined at above level
2303 */
2304 BEGIN
2305
2306 SELECT NVL(p_lines.it_inspect_req_flag_tbl(i), posp.INSPECTION_REQUIRED_FLAG),
2307 NVL(p_lines.it_receipt_req_flag_tbl(i), posp.RECEIVING_FLAG),
2308 NVL(p_lines.it_invoice_tolerance_tbl(i), posp.INVOICE_CLOSE_TOLERANCE),
2309 NVL(p_lines.it_rcv_tolerance_tbl(i), posp.RECEIVE_CLOSE_TOLERANCE)
2310 INTO p_lines.it_inspect_req_flag_tbl(i),
2311 p_lines.it_receipt_req_flag_tbl(i),
2312 p_lines.it_invoice_tolerance_tbl(i),
2313 p_lines.it_rcv_tolerance_tbl(i)
2314 FROM po_system_parameters_all posp --<Shared Proc FPJ>
2315 WHERE NVL(org_id, -99) = NVL(PO_AUTOCREATE_PARAMS.g_purchasing_ou_id, -99); --<Shared Proc FPJ>
2316
2317 EXCEPTION
2318 WHEN no_data_found THEN
2319 IF g_debug_stmt THEN
2320 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: '||SQLERRM);
2321 END IF;
2322 WHEN OTHERS THEN
2323 IF g_debug_unexp THEN
2324 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2325 END IF;
2326
2327 --CLM Phase 2 changes : error handling
2328 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_SYTEM_ERR',
2329 x_token1_value => sqlerrm,
2330 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
2331
2332 PO_AUTOCREATE_PVT.wrapup();
2333 po_message_s.sql_error('Get po system default',l_progress,SQLCODE);
2334 raise;
2335 END;
2336
2337 -- If not defined at po system option level also then
2338
2339 IF (p_lines.it_inspect_req_flag_tbl(i) IS NULL) THEN
2340 p_lines.it_inspect_req_flag_tbl(i) := 'N';
2341 END IF;
2342 IF (p_lines.it_receipt_req_flag_tbl(i) IS NULL) THEN
2343 p_lines.it_receipt_req_flag_tbl(i) := 'N';
2344 END IF;
2345 IF (p_lines.it_invoice_tolerance_tbl(i) IS NULL) THEN
2346 p_lines.it_invoice_tolerance_tbl(i) := '0';
2347 END IF;
2348 IF (p_lines.it_rcv_tolerance_tbl(i) IS NULL) THEN
2349 p_lines.it_rcv_tolerance_tbl(i) := '0';
2350 END IF;
2351 IF (x_order_type_lookup_code = 'QUANTITY') THEN
2352 --<Shared Proc FPJ>
2353 p_lines.it_list_price_per_unit_tbl(i) := NVL( p_lines.it_list_price_per_unit_tbl(i), (p_lines.unit_price_tbl(i) / p_lines.rate_for_req_fields_tbl(i)) );
2354 -- <SERVICES FPJ START>
2355 ELSIF ( x_order_type_lookup_code = 'AMOUNT' ) THEN
2356 p_lines.it_list_price_per_unit_tbl(i) := 1;
2357 ELSE -- ( x_order_type_lookup_code IN ('FIXED PRICE','RATE') )
2358 p_lines.it_list_price_per_unit_tbl(i) := NULL;
2359 END IF;
2360 IF (p_lines.item_id_tbl(i) IS NULL) THEN
2361 p_lines.it_taxable_flag_tbl(i) := '';
2362 p_lines.it_unit_meas_lookup_code_tbl(i) := '';
2363 END IF;
2364
2365 l_progress := '210';
2366 IF g_debug_stmt THEN
2367 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2368 p_message => 'Create_line: line taxable_flag: '||p_lines.it_taxable_flag_tbl(i));
2369 END IF;
2370
2371 -- Perform currency conversion on price/amount/quantity.
2372 IF (NVL(p_lines.clm_info_flag_tbl(i),'N') <> 'Y') THEN
2373 l_cc_unit_price := p_lines.unit_price_tbl(i);
2374 l_cc_base_unit_price := p_lines.base_unit_price_tbl(i);
2375 l_cc_amount := p_lines.amount_tbl(i);
2376 l_progress := '220';
2377
2378 do_currency_conversion( p_order_type_lookup_code => x_order_type_lookup_code,
2379 p_interface_source_code => PO_AUTOCREATE_PARAMS.g_interface_source_code,
2380 p_rate => p_lines.rate_for_req_fields_tbl(i),
2381 p_po_currency_code => p_lines.hd_currency_code_tbl(i),
2382 p_requisition_line_id => p_lines.requisition_line_id_tbl(i),
2383 x_quantity => x_quantity, -- IN/OUT
2384 x_unit_price => l_cc_unit_price,-- IN/OUT
2385 x_base_unit_price => l_cc_base_unit_price,-- IN/OUT
2386 x_amount => l_cc_amount-- IN/OUT
2387 );
2388 l_progress := '230';
2389 p_lines.unit_price_tbl(i) := l_cc_unit_price;
2390 p_lines.base_unit_price_tbl(i) := l_cc_base_unit_price;
2391 p_lines.amount_tbl(i) := l_cc_amount;
2392
2393 END IF;
2394
2395 /* if line does not exist */
2396 IF(x_po_line_id IS NULL) THEN
2397 l_progress := '230';
2398
2399 IF g_debug_stmt THEN
2400 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Create_line: PO line does not exist');
2401 END IF;
2402
2403 /* set neg by preparer flag */
2404 p_lines.negotiated_flag_tbl(i) := 'N';
2405
2406 --<SOURCING TO PO FPH >
2407 --sourcing populates the unit price in bidder's currency, so we are
2408 -- not converting the currency. And sourcing does not have
2409 --list_price_per_unit and market price storred in their system,
2410 --so dont do the following for sourcing
2411 IF PO_AUTOCREATE_PARAMS.g_interface_source_code <>'SOURCING' THEN
2412 l_progress := '240';
2413 IF (p_lines.it_unit_meas_lookup_code_tbl(i) = p_lines.unit_of_measure_tbl(i)) THEN
2414 IF (p_lines.it_list_price_per_unit_tbl(i) <> '') THEN
2415 IF (p_lines.it_list_price_per_unit_tbl(i) > p_lines.unit_price_tbl(i)) THEN
2416 p_lines.negotiated_flag_tbl(i) := 'Y';
2417 END IF;
2418 END IF;
2419 END IF;
2420 END IF;
2421
2422 --<SOURCING TO PO FPH START>
2423 --default un_number_id,hazard_class_id from item attributes when
2424 --not backed by a req. Also default UOM for amount based lines for this
2425 --condition.
2426 IF PO_AUTOCREATE_PARAMS.g_interface_source_code IN ('SOURCING','CONSUMPTION_ADVICE') THEN -- CONSIGNED FPI
2427 l_progress := '250';
2428
2429 IF p_lines.requisition_line_id_tbl(i) IS NULL THEN
2430
2431 BEGIN
2432 l_progress:='260';
2433 SELECT un_number_id,
2434 hazard_class_id
2435 INTO x_un_number_id,
2436 x_hazard_class_id
2437 FROM mtl_system_items
2438 WHERE inventory_item_id = p_lines.item_id_tbl(i)
2439 AND organization_id = po_autocreate_params.g_sys.master_inv_org_id;
2440 EXCEPTION
2441 WHEN no_data_found THEN
2442 IF g_debug_stmt THEN
2443 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: '||SQLERRM);
2444 END IF;
2445 WHEN OTHERS THEN
2446 IF g_debug_unexp THEN
2447 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2448 END IF;
2449 RAISE;
2450 END;
2451
2452 IF x_order_type_lookup_code = 'AMOUNT' THEN
2453 BEGIN
2454 l_progress:='270';
2455 SELECT unit_of_measure
2456 INTO x_unit_of_measure
2457 FROM po_line_types
2458 WHERE line_type_id= p_lines.line_type_id_tbl(i);
2459 EXCEPTION
2460 WHEN OTHERS THEN
2461 IF g_debug_unexp THEN
2462 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2463 END IF;
2464 RAISE;
2465 END;
2466 ELSE
2467 x_unit_of_measure := p_lines.unit_of_measure_tbl(i);
2468 END IF;
2469
2470 ELSE
2471 l_progress:='280';
2472 x_un_number_id := p_lines.un_number_id_tbl(i);
2473 x_hazard_class_id := p_lines.hazard_class_id_tbl(i);
2474 x_unit_of_measure := p_lines.unit_of_measure_tbl(i);
2475
2476 -- <Bug 9896177> If UOM is null in the requisition line, then
2477 -- get it from Line Types Setup, for non-info lines.
2478 IF x_unit_of_measure IS NULL AND NVL(p_lines.clm_info_flag_tbl(i),'N') = 'N' THEN
2479 BEGIN
2480 l_progress:='285';
2481 SELECT unit_of_measure
2482 INTO x_unit_of_measure
2483 FROM po_line_types
2484 WHERE line_type_id = p_lines.line_type_id_tbl(i);
2485 EXCEPTION
2486 WHEN OTHERS THEN
2487 IF g_debug_unexp THEN
2488 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2489 END IF;
2490 RAISE;
2491 END;
2492 END IF;
2493 END IF;
2494 END IF;
2495
2496 IF( PO_AUTOCREATE_PARAMS.g_document_subtype='STANDARD'
2497 OR PO_AUTOCREATE_PARAMS.g_document_subtype='PLANNED'
2498 OR PO_AUTOCREATE_PARAMS.g_document_subtype='BLANKET' ) THEN
2499 l_progress :='290';
2500
2501 SELECT po_lines_s.nextval INTO x_po_line_id FROM sys.dual;
2502
2503 l_progress:='300';
2504 -- Call the Pricing API only when...
2505 --
2506 -- 1) Autocreating a Standard PO or a BlANKET and pricing is enhanced for the style selected
2507 -- 2) Source Document exists or the pricing is enhanced for the style selected
2508 -- 3) Not a Consumption Advice
2509 -- 4) Requisition Line's Contractor Status is not 'ASSIGNED'
2510 -- ( if the contractor status is 'ASSIGNED',
2511 -- then we take the price directly from the Requisition Line )
2512 --
2513 -- 5) Not a complex work PO <Complex Work R12>
2514 l_contractor_status := PO_SERVICES_PVT.get_contractor_status(p_lines.requisition_line_id_tbl(i));
2515 -- Enhanced Pricing: Enable pricing call for BLANKET document subtype if pricing enhanced for the style selected
2516 IF ( (PO_AUTOCREATE_PARAMS.g_document_subtype = 'STANDARD' OR ( PO_AUTOCREATE_PARAMS.g_document_subtype = 'BLANKET' AND l_enhanced_pricing_flag = 'Y') )
2517 AND ( p_lines.from_line_id_tbl(i) IS NOT NULL OR p_lines.contract_id_tbl(i) IS NOT NULL OR l_enhanced_pricing_flag = 'Y' )
2518 AND (NVL(p_lines.clm_info_flag_tbl(i),'N')<> 'Y')
2519 AND ( PO_AUTOCREATE_PARAMS.g_interface_source_code <> 'CONSUMPTION_ADVICE' )
2520 AND (NOT po_autocreate_params.g_is_complex_work_po) -- <Complex Work R12>
2521 AND ( l_contractor_status IS NULL OR l_contractor_status <> 'ASSIGNED' ) ) THEN
2522
2523 l_progress := '310';
2524 -- <SERVICES FPJ START>
2525 --
2526 PO_SOURCING2_SV.get_break_price ( p_api_version => 1.0 ,
2527 p_order_quantity => x_quantity ,
2528 p_ship_to_org => p_lines.dest_organization_id_tbl(i) ,
2529 p_ship_to_loc => po_autocreate_mainproc_pvt.get_ship_to_loc(p_lines.deliver_to_loc_id_tbl(i)) ,
2530 p_po_line_id => p_lines.from_line_id_tbl(i) ,
2531 p_cum_flag => FALSE ,
2532 p_need_by_date => p_lines.need_by_date_tbl(i) ,
2533 p_line_location_id => NULL ,
2534 p_contract_id => p_lines.contract_id_tbl(i) ,
2535 p_org_id => PO_AUTOCREATE_PARAMS.g_purchasing_ou_id ,
2536 p_supplier_id => p_lines.hd_vendor_id_tbl(i) ,
2537 p_supplier_site_id => p_lines.hd_vendor_site_id_tbl(i) ,
2538 p_creation_date => p_lines.creation_date_tbl(i) ,
2539 p_order_header_id => po_autocreate_params.g_po_header_id ,
2540 p_order_line_id => x_po_line_id ,
2541 p_line_type_id => p_lines.line_type_id_tbl(i) ,
2542 p_item_revision => p_lines.item_revision_tbl(i) ,
2543 p_item_id => p_lines.item_id_tbl(i) ,
2544 p_category_id => p_lines.category_id_tbl(i) ,
2545 p_supplier_item_num => p_lines.vendor_product_num_tbl(i) ,
2546 p_in_price => p_lines.base_unit_price_tbl(i) ,
2547 p_uom => x_unit_of_measure ,
2548 p_currency_code => p_lines.hd_currency_code_tbl(i) ,
2549 p_pricing_call_src => l_pricing_call_src ,
2550 x_base_unit_price => l_base_unit_price ,
2551 x_price_break_id => l_price_break_id ,
2552 x_price => x_unit_price ,
2553 x_return_status => l_return_status ,
2554 p_req_line_price => p_lines.unit_price_tbl(i) );
2555 -- <SERVICES FPJ END>
2556 IF g_debug_stmt THEN
2557 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2558 p_message => 'Break Unit Price: '||x_unit_price
2559 || ' Price Break Id : ' || l_price_break_id
2560 || ' Base Unit Price : '|| l_base_unit_price );
2561 END IF;
2562
2563 -- Treat 0 price as null price
2564 l_progress := '320';
2565 IF (x_unit_price = 0) THEN
2566 x_unit_price := NULL;
2567 END IF;
2568 x_unit_price := NVL(x_unit_price, p_lines.unit_price_tbl(i));
2569 l_base_unit_price := NVL(l_base_unit_price, p_lines.base_unit_price_tbl(i));
2570 IF g_debug_stmt THEN
2571 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2572 p_message => 'Break Unit Price: '||x_unit_price
2573 || ' Price Break Id : ' || l_price_break_id
2574 || ' Base Unit Price : '|| l_base_unit_price );
2575 END IF;
2576
2577 ELSE
2578 l_progress := '330';
2579 x_unit_price := p_lines.unit_price_tbl(i);
2580 -- <FPJ Advanced Price START>
2581 l_base_unit_price := p_lines.base_unit_price_tbl(i);
2582 -- <FPJ Advanced Price END>
2583 END IF;
2584
2585 /* GA FPI end */
2586 -- <SERVICES FPJ START> If we are Autocreating a Standard PO,
2587 -- then setup the Interface tables to copy over Price Differentials.
2588 IF ( PO_AUTOCREATE_PARAMS.g_document_subtype = 'STANDARD'
2589 AND PO_AUTOCREATE_PARAMS.g_interface_source_code NOT IN ('SOURCING', 'CONSUMPTION_ADVICE')
2590 AND (NOT po_autocreate_params.g_is_complex_work_po)) THEN
2591
2592 l_progress := '340';
2593
2594 -- Determine where to get the Price Differentials from.
2595 IF ( PO_SERVICES_PVT.get_contractor_status(p_lines.requisition_line_id_tbl(i)) = 'ASSIGNED' ) THEN
2596
2597 l_source_entity_type := 'REQ LINE';
2598 l_source_entity_id := p_lines.requisition_line_id_tbl(i);
2599
2600 ELSIF ( po_price_differentials_pvt.allows_price_differentials(p_lines.requisition_line_id_tbl(i)) ) THEN
2601 l_progress := '350';
2602 IF ( l_price_break_id IS NOT NULL ) THEN
2603 l_source_entity_type := 'PRICE BREAK';
2604 l_source_entity_id := l_price_break_id;
2605 ELSIF ( p_lines.from_line_id_tbl(i) IS NOT NULL ) THEN
2606 l_source_entity_type := 'BLANKET LINE';
2607 l_source_entity_id := p_lines.from_line_id_tbl(i);
2608 END IF;
2609
2610 END IF;
2611 IF g_debug_stmt THEN
2612 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2613 p_message => 'Before Inserting into the Price Diff table ' ||
2614 ' l_source_entity_type : '||l_source_entity_type ||
2615 ' l_source_entity_id : ' || l_source_entity_id );
2616 END IF;
2617
2618 l_progress := '360';
2619
2620 FOR c_rec IN c_price_diff(l_source_entity_type,l_source_entity_id)
2621 LOOP
2622 p_price_diff.intf_price_diff_id_tbl(l_price_diff_idx) := c_rec.price_diff_interface_id;
2623 p_price_diff.price_diff_num_tbl(l_price_diff_idx) := c_rec.price_differential_num;
2624 p_price_diff.intf_header_id_tbl(l_price_diff_idx) := p_lines.intf_header_id_tbl(i);
2625 p_price_diff.intf_line_id_tbl(l_price_diff_idx) := p_lines.intf_line_id_tbl(i);
2626 p_price_diff.entity_type_tbl(l_price_diff_idx) := 'PO LINE';
2627 p_price_diff.price_type_tbl(l_price_diff_idx) := c_rec.price_type;
2628 p_price_diff.min_multiplier_tbl(l_price_diff_idx) := NULL;
2629 p_price_diff.max_multiplier_tbl(l_price_diff_idx) := NULL;
2630 p_price_diff.multiplier_tbl(l_price_diff_idx) := c_rec.multiplier;
2631 p_price_diff.enabled_flag_tbl(l_price_diff_idx) := c_rec.enabled_flag;
2632 p_price_diff.process_status_tbl(l_price_diff_idx) := 'ACCEPTED';
2633 p_price_diff.ln_po_line_id_tbl(l_price_diff_idx) := x_po_line_id;
2634 l_price_diff_idx := l_price_diff_idx + 1;
2635 END LOOP;
2636
2637 END IF;
2638 --
2639 -- <SERVICES FPJ END>
2640 l_progress := '370';
2641
2642 IF g_debug_stmt THEN
2643 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Create_line: Line id: '||x_po_line_id);
2644 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Create_line: Header_id : '||po_autocreate_params.g_po_header_id);
2645 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Create_line: Line number: '||p_lines.line_num_tbl(i));
2646 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Create_line: TRX RSON CODE : '||p_lines.transaction_reason_code_tbl(i));
2647 END IF;
2648
2649
2650 -- <SERVICES FPJ START>
2651 -- Retrieve the values for order_type_lookup_code, purchase_basis
2652 -- and matching_basis
2653 IF NVL(p_lines.clm_info_flag_tbl(i),'N') <> 'Y' THEN
2654 l_progress := '380';
2655 PO_LINE_TYPES_SV.get_line_type_def( p_lines.line_type_id_tbl(i),
2656 l_order_type_lookup_code,
2657 l_purchase_basis1,
2658 l_matching_basis,
2659 l_category_id,
2660 l_unit_meas_lookup_code,
2661 l_unit_price,
2662 l_outside_operation_flag,
2663 l_receiving_flag,
2664 l_receive_close_tolerance);
2665 END IF;
2666 -- <SERVICES FPJ END>
2667 --<DBI FPJ Start>
2668
2669 BEGIN
2670 IF PO_AUTOCREATE_PARAMS.g_interface_source_code='SOURCING' THEN
2671
2672 l_negotiated_by_preparer_flag := 'Y';
2673
2674 ELSIF p_lines.from_header_id_tbl(i) IS NOT NULL THEN
2675
2676 l_progress := '390';
2677
2678 SELECT type_lookup_code,
2679 global_agreement_flag
2680 INTO l_type_lookup_code,
2681 l_global_agreement_flag
2682 FROM po_headers_all
2683 WHERE po_header_id=p_lines.from_header_id_tbl(i);
2684
2685 -- if the source document is global agreement.
2686 IF l_type_lookup_code='BLANKET' AND l_global_agreement_flag='Y' THEN
2687 l_progress := '391';
2688 SELECT negotiated_by_preparer_flag
2689 INTO l_negotiated_by_preparer_flag
2690 FROM po_lines_all
2691 WHERE po_line_id=p_lines.from_line_id_tbl(i);
2692 --if the source document is quotation.
2693 ELSIF l_type_lookup_code ='QUOTATION' THEN
2694 l_progress := '392';
2695 l_negotiated_by_preparer_flag := 'Y';
2696 -- if the source document is contract or otherwise
2697 ELSE
2698 l_progress := '393';
2699 SELECT negotiated_by_preparer_flag
2700 INTO l_negotiated_by_preparer_flag
2701 FROM po_requisition_lines_all
2702 WHERE requisition_line_id=p_lines.requisition_line_id_tbl(i);
2703 END IF;
2704
2705 ELSE
2706
2707 l_progress := '400';
2708
2709 SELECT negotiated_by_preparer_flag
2710 INTO l_negotiated_by_preparer_flag
2711 FROM po_requisition_lines_all
2712 WHERE requisition_line_id=p_lines.requisition_line_id_tbl(i);
2713
2714 END IF;
2715
2716 EXCEPTION
2717 WHEN OTHERS THEN
2718 IF g_debug_stmt THEN
2719 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2720 p_message => 'EXCEPTION caught; SQL Code is '||SQLCODE||'; Error is '||SQLERRM);
2721 END IF;
2722 l_negotiated_by_preparer_flag:=NULL;
2723 END;
2724
2725 --<INVCONV R12 START>
2726 -- IF secondary quantity is null and item is dual uom control , default the secondary qty.
2727 IF ( PO_AUTOCREATE_PARAMS.g_document_subtype ='STANDARD'
2728 OR PO_AUTOCREATE_PARAMS.g_document_subtype='PLANNED'
2729 OR PO_AUTOCREATE_PARAMS.g_document_type = 'PA') THEN
2730 l_progress := '410';
2731 IF p_lines.item_id_tbl(i) IS NOT NULL THEN
2732 l_progress := '420';
2733 IF p_lines.it_secondary_uom_code_tbl(i) IS NOT NULL THEN
2734 IF ( p_lines.secondary_quantity_tbl(i) IS NULL
2735 OR po_autocreate_params.g_sys.master_inv_org_id <> p_lines.dest_organization_id_tbl(i) ) THEN
2736 l_progress := '430';
2737 PO_UOM_S.get_secondary_uom( p_lines.item_id_tbl(i),
2738 p_lines.dest_organization_id_tbl(i),
2739 x_secondary_uom,
2740 x_secondary_unit_def);
2741
2742 IF PO_AUTOCREATE_PARAMS.g_document_type <> 'PA' THEN
2743 l_progress := '440';
2744 PO_UOM_S.uom_convert (x_quantity,
2745 x_unit_of_measure,
2746 p_lines.item_id_tbl(i),
2747 x_secondary_unit_def,
2748 x_secondary_quantity_def) ;
2749
2750 IF p_lines.dest_organization_id_tbl(i) = po_autocreate_params.g_sys.master_inv_org_id THEN
2751 l_progress := '450';
2752 p_lines.secondary_unit_of_meas_tbl(i) := x_secondary_unit_def ;
2753 p_lines.secondary_quantity_tbl(i) := x_secondary_quantity_def ;
2754 END IF;
2755
2756 ELSE
2757 x_secondary_quantity_def := NULL ;
2758 END IF;
2759
2760 ELSE
2761 l_progress := '460';
2762 x_secondary_unit_def := p_lines.secondary_unit_of_meas_tbl(i);
2763 x_secondary_quantity_def := p_lines.secondary_quantity_tbl(i);
2764 END IF;
2765
2766 ELSE -- IF item.secondary_uom_code IS NOT NULL
2767 l_progress := '470';
2768 x_secondary_unit_def := NULL;
2769 x_secondary_quantity_def := NULL ;
2770 END IF;
2771
2772 IF p_lines.it_grade_control_flag_tbl(i) = 'N' AND p_lines.preferred_grade_tbl(i) IS NOT NULL THEN
2773 x_preferred_grade_def := NULL ;
2774 ELSE
2775 x_preferred_grade_def := p_lines.preferred_grade_tbl(i) ;
2776 END IF;
2777
2778 ELSE -- IF interface.item_id is not null
2779
2780 l_progress := '480';
2781 x_secondary_unit_def := NULL;
2782 x_secondary_quantity_def := NULL ;
2783 x_preferred_grade_def := NULL;
2784
2785 END IF;
2786
2787 ELSE
2788
2789 x_secondary_unit_def := NULL;
2790 x_secondary_quantity_def := NULL ;
2791 x_preferred_grade_def := NULL;
2792
2793 END IF;
2794 --<INVCONV R12 END>
2795 IF(p_lines.hd_vendor_site_id_tbl(i) IS NOT NULL)THEN
2796 l_progress := '490';
2797 SELECT retainage_rate
2798 INTO l_retainage_rate
2799 FROM po_vendor_sites_all
2800 WHERE vendor_site_id = p_lines.hd_vendor_site_id_tbl(i);
2801 END IF;
2802
2803 l_progress := '500';
2804 /* Initialize line record here:*/
2805 p_lines.po_line_id_tbl(i) := x_po_line_id;
2806 p_lines.po_header_id_tbl(i) := po_autocreate_params.g_po_header_id;
2807 p_lines.unit_of_measure_tbl(i) := x_unit_of_measure;
2808 p_lines.base_unit_price_tbl(i) := l_base_unit_price ;
2809 p_lines.unit_price_tbl(i) := x_unit_price ; --interface.unit_price,
2810 p_lines.taxable_flag_tbl(i) := NVL(p_lines.it_taxable_flag_tbl(i),l_param_taxable_flag);
2811 p_lines.negotiated_flag_tbl(i) := l_negotiated_by_preparer_flag;
2812 p_lines.secondary_unit_of_meas_tbl(i) := x_secondary_unit_def;
2813 p_lines.secondary_quantity_tbl(i) := x_secondary_quantity_def;
2814 p_lines.preferred_grade_tbl(i) := x_preferred_grade_def;
2815 p_lines.org_id_tbl(i) := PO_AUTOCREATE_PARAMS.g_purchasing_ou_id;
2816 p_lines.order_type_lookup_code_tbl(i) := l_order_type_lookup_code;
2817 p_lines.purchase_basis_tbl(i) := l_purchase_basis1;
2818 p_lines.matching_basis_tbl(i) := l_matching_basis;
2819 p_lines.retainage_rate_tbl(i) := NVL(p_lines.retainage_rate_tbl(i), l_retainage_rate);
2820
2821 l_progress := '510';
2822 /* Bug 9915090 - In Case of IDVs quantity should be populated with extended price / offer price. */
2823 SELECT DECODE(PO_AUTOCREATE_PARAMS.g_document_type,
2824 'RFQ', NULL,
2825 DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,
2826 'SOURCING',NULL, ROUND (p_lines.it_list_price_per_unit_tbl(i),NVL(x_ext_precision,15)))) ,
2827 DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING',NULL, ROUND(p_lines.it_market_price_tbl(i),NVL(x_ext_precision,15))) ,
2828 DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'RFQ', 1,'PA',x_quantity, x_quantity) ,
2829 nvl2(g_calculate_tax_flag, 'CREATE', NULL) ,
2830 DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'RFQ', NULL,p_lines.type_1099_tbl(i)) ,
2831 DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING', x_un_number_id,p_lines.un_number_id_tbl(i)) ,
2832 DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING', x_hazard_class_id,p_lines.hazard_class_id_tbl(i)) ,
2833 DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.contract_id_tbl(i)) ,
2834 DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'RFQ', NULL, p_lines.qty_rcv_tolerance_tbl(i)) ,
2835 DECODE(PO_AUTOCREATE_PARAMS.g_document_type, 'RFQ', NULL, p_lines.qty_rcv_exception_code_tbl(i)) ,
2836 DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,NVL(p_lines.from_header_id_tbl(i),x_quote_header_id)) ,
2837 DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,NVL(p_lines.from_line_id_tbl(i),x_quote_line_id)) ,
2838 DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,l_price_break_id) ,
2839 DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING',NULL,p_lines.note_to_vendor_tbl(i)) ,
2840 DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.oke_contract_header_id_tbl(i)) ,
2841 DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.oke_contract_version_id_tbl(i)) ,
2842 DECODE ( PO_AUTOCREATE_PARAMS.g_document_type,
2843 'PA' , DECODE ( x_order_type_lookup_code,
2844 'AMOUNT' , NULL, p_lines.quantity_tbl(i), NULL)) ,
2845 DECODE ( PO_AUTOCREATE_PARAMS.g_document_type,
2846 'PA' , DECODE ( x_order_type_lookup_code ,
2847 'QUANTITY', NULL, p_lines.committed_amount_tbl (i)), NULL) ,
2848 DECODE(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING',p_lines.price_break_lookup_code_tbl(i), NULL)
2849 INTO p_lines.list_price_per_unit_tbl(i) ,
2850 p_lines.market_price_tbl(i) ,
2851 p_lines.quantity_tbl(i) ,
2852 p_lines.tax_attribute_update_code_tbl(i) ,
2853 p_lines.type_1099_tbl(i) ,
2854 p_lines.un_number_id_tbl(i) ,
2855 p_lines.hazard_class_id_tbl(i) ,
2856 p_lines.contract_id_tbl(i) ,
2857 p_lines.qty_rcv_tolerance_tbl(i) ,
2858 p_lines.over_tolerance_err_flag_tbl(i) ,
2859 p_lines.from_header_id_tbl(i) ,
2860 p_lines.from_line_id_tbl(i) ,
2861 p_lines.from_line_location_id_tbl(i) ,
2862 p_lines.note_to_vendor_tbl(i) ,
2863 p_lines.oke_contract_header_id_tbl(i) ,
2864 p_lines.oke_contract_version_id_tbl(i) ,
2865 p_lines.quantity_committed_tbl(i) ,
2866 p_lines.committed_amount_tbl(i) ,
2867 p_lines.price_break_lookup_code_tbl(i)
2868 FROM dual;
2869
2870 IF g_debug_stmt THEN
2871 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
2872 p_token => l_progress, p_message => 'Before Insert into Lines Draft '
2873 ||' List Price : ' || p_lines.list_price_per_unit_tbl(i)
2874 ||' Market Price : ' || p_lines.market_price_tbl(i)
2875 ||' Unit Price : ' || p_lines.unit_price_tbl(i));
2876 END IF;
2877 l_progress := '520';
2878 INSERT
2879 INTO po_lines_draft_all
2880 (
2881 draft_id,
2882 po_line_id,
2883 last_update_date,
2884 last_updated_by,
2885 po_header_id,
2886 line_num,
2887 creation_date,
2888 created_by,
2889 last_update_login,
2890 item_id,
2891 job_id,
2892 category_id,
2893 item_description,
2894 unit_meas_lookup_code,
2895 list_price_per_unit,
2896 market_price,
2897 base_unit_price,
2898 unit_price,
2899 quantity,
2900 amount,
2901 taxable_flag,
2902 type_1099,
2903 negotiated_by_preparer_flag,
2904 closed_code,
2905 item_revision,
2906 un_number_id,
2907 hazard_class_id,
2908 contract_id,
2909 line_type_id,
2910 vendor_product_num,
2911 qty_rcv_tolerance,
2912 over_tolerance_error_flag,
2913 firm_status_lookup_code,
2914 min_release_amount,
2915 price_type_lookup_code,
2916 transaction_reason_code,
2917 from_header_id,
2918 from_line_id,
2919 from_line_location_id,
2920 note_to_vendor,
2921 oke_contract_header_id,
2922 oke_contract_version_id,
2923 secondary_unit_of_measure,
2924 secondary_quantity,
2925 preferred_grade,
2926 auction_header_id,
2927 auction_line_number,
2928 auction_display_number,
2929 bid_number,
2930 bid_line_number,
2931 quantity_committed,
2932 committed_amount,
2933 price_break_lookup_code,
2934 supplier_ref_number,
2935 org_id,
2936 start_date,
2937 expiration_date,
2938 contractor_first_name,
2939 contractor_last_name,
2940 order_type_lookup_code,
2941 purchase_basis,
2942 matching_basis,
2943 retainage_rate,
2944 max_retainage_amount,
2945 progress_payment_rate,
2946 recoupment_rate,
2947 tax_attribute_update_code,
2948 ip_category_id,
2949 supplier_part_auxid,
2950 catalog_name,
2951 line_num_display,
2952 group_line_id,
2953 clm_info_flag,
2954 clm_option_indicator,
2955 clm_option_num,
2956 clm_option_from_date,
2957 clm_option_to_date,
2958 clm_funded_flag,
2959 clm_base_line_num,
2960 contract_type,
2961 cost_constraint,
2962 clm_idc_type,
2963 clm_min_total_amount,
2964 clm_max_total_amount,
2965 clm_min_total_quantity,
2966 clm_max_total_quantity,
2967 clm_min_order_amount,
2968 clm_max_order_amount,
2969 clm_min_order_quantity,
2970 clm_max_order_quantity,
2971 clm_total_amount_ordered,
2972 clm_total_quantity_ordered,
2973 clm_exercised_flag, -- Bug 9960752
2974 clm_exercised_date, -- Bug 9960752
2975 change_status,
2976 --CLM Phase4 Changes
2977 clm_exhibit_name,
2978 clm_delivery_event_code
2979 )
2980 VALUES
2981 (
2982 PO_AUTOCREATE_PARAMS.g_draft_id ,
2983 p_lines.po_line_id_tbl(i) ,
2984 P_lines.last_update_date_tbl(i) ,
2985 p_lines.last_updated_by_tbl(i) ,
2986 p_lines.po_header_id_tbl(i) ,
2987 p_lines.line_num_tbl(i) ,
2988 p_lines.creation_date_tbl(i) ,
2989 p_lines.created_by_tbl(i) ,
2990 p_lines.last_update_login_tbl(i) ,
2991 p_lines.item_id_tbl(i) ,
2992 p_lines.job_id_tbl(i) ,
2993 p_lines.category_id_tbl(i) ,
2994 p_lines.item_desc_tbl(i) ,
2995 p_lines.unit_of_measure_tbl(i) ,
2996 p_lines.list_price_per_unit_tbl(i) ,
2997 p_lines.market_price_tbl(i) ,
2998 p_lines.base_unit_price_tbl(i) ,
2999 p_lines.unit_price_tbl(i) ,
3000 p_lines.quantity_tbl(i) ,
3001 p_lines.amount_tbl(i) ,
3002 p_lines.taxable_flag_tbl(i) ,
3003 p_lines.type_1099_tbl(i) ,
3004 p_lines.negotiated_flag_tbl(i) ,
3005 p_lines.closed_code_tbl(i) ,
3006 p_lines.item_revision_tbl(i) ,
3007 p_lines.un_number_id_tbl(i) ,
3008 p_lines.hazard_class_id_tbl(i) ,
3009 p_lines.contract_id_tbl(i) ,
3010 p_lines.line_type_id_tbl(i) ,
3011 p_lines.vendor_product_num_tbl(i) ,
3012 p_lines.qty_rcv_tolerance_tbl(i) ,
3013 p_lines.over_tolerance_err_flag_tbl(i) ,
3014 p_lines.firm_status_lookup_code_tbl(i) ,
3015 p_lines.min_release_amount_tbl(i) ,
3016 p_lines.price_type_tbl(i) ,
3017 p_lines.transaction_reason_code_tbl(i) ,
3018 p_lines.from_header_id_tbl(i) ,
3019 p_lines.from_line_id_tbl(i) ,
3020 p_lines.from_line_location_id_tbl(i) ,
3021 p_lines.note_to_vendor_tbl(i) ,
3022 p_lines.oke_contract_header_id_tbl(i) ,
3023 p_lines.oke_contract_version_id_tbl(i) ,
3024 p_lines.secondary_unit_of_meas_tbl(i) ,
3025 p_lines.secondary_quantity_tbl(i) ,
3026 p_lines.preferred_grade_tbl(i) ,
3027 p_lines.auction_header_id_tbl(i) ,
3028 p_lines.auction_line_number_tbl(i) ,
3029 p_lines.auction_display_number_tbl(i) ,
3030 p_lines.bid_number_tbl(i) ,
3031 p_lines.bid_line_number_tbl(i) ,
3032 p_lines.quantity_committed_tbl(i) ,
3033 p_lines.committed_amount_tbl(i) ,
3034 p_lines.price_break_lookup_code_tbl(i) ,
3035 p_lines.supplier_ref_number_tbl(i) ,
3036 p_lines.org_id_tbl(i) --PO_AUTOCREATE_PARAMS.g_purchasing_ou_id
3037 ,
3038 p_lines.effective_date_tbl(i) ,
3039 p_lines.expiration_date_tbl(i) ,
3040 p_lines.contractor_first_name_tbl(i) ,
3041 p_lines.contractor_last_name_tbl(i) ,
3042 p_lines.order_type_lookup_code_tbl(i) ,
3043 p_lines.purchase_basis_tbl(i) ,
3044 p_lines.matching_basis_tbl(i) ,
3045 p_lines.retainage_rate_tbl(i) ,
3046 p_lines.max_retainage_amount_tbl(i) ,
3047 p_lines.progress_payment_rate_tbl(i) ,
3048 p_lines.recoupment_rate_tbl(i) ,
3049 p_lines.tax_attribute_update_code_tbl(i) ,
3050 p_lines.ip_category_id_tbl(i) ,
3051 p_lines.supplier_part_auxid_tbl(i) ,
3052 p_lines.catalog_name_tbl(i) ,
3053 p_lines.line_num_display_tbl(i) ,
3054 p_lines.group_line_id_tbl(i) ,
3055 p_lines.clm_info_flag_tbl(i) ,
3056 p_lines.clm_option_indicator_tbl(i) ,
3057 p_lines.clm_option_num_tbl(i) ,
3058 p_lines.clm_option_from_date_tbl(i) ,
3059 p_lines.clm_option_to_date_tbl(i) ,
3060 p_lines.clm_funded_flag_tbl(i) ,
3061 p_lines.clm_base_line_num_tbl(i) ,
3062 p_lines.contract_type_tbl(i) ,
3063 p_lines.cost_constraint_tbl(i) ,
3064 p_lines.clm_idc_type_tbl(i) ,
3065 p_lines.clm_min_total_amount_tbl(i) ,
3066 p_lines.clm_max_total_amount_tbl(i) ,
3067 p_lines.clm_min_total_quantity_tbl(i) ,
3068 p_lines.clm_max_total_quantity_tbl(i) ,
3069 p_lines.clm_min_order_amount_tbl(i) ,
3070 p_lines.clm_max_order_amount_tbl(i) ,
3071 p_lines.clm_min_order_quantity_tbl(i) ,
3072 p_lines.clm_max_order_quantity_tbl(i) ,
3073 p_lines.clm_total_amount_ordered_tbl(i) ,
3074 p_lines.clm_total_quantity_ordered_tbl(i) ,
3075 p_lines.clm_exercised_flag_tbl(i) -- Bug 9960752
3076 ,
3077 p_lines.clm_exercised_date_tbl(i) -- Bug 9960752
3078 ,
3079 'NEW',
3080 --CLM Phase4 Changes
3081 p_lines.clm_exhibit_name_tbl(i),
3082 p_lines.clm_delivery_event_code_tbl(i)
3083 );
3084
3085 IF( PO_AUTOCREATE_PARAMS.g_document_subtype IN ('STANDARD','PLANNED','BLANKET') ) THEN
3086 PO_AUTOCREATE_PVT.calculate_local( PO_AUTOCREATE_PARAMS.g_document_subtype, 'LINE', p_lines.po_line_id_tbl(i));
3087 END IF;
3088
3089 END IF;
3090
3091 ELSE
3092
3093 IF g_debug_stmt
3094 THEN
3095 PO_DEBUG.debug_stmt( p_log_head => g_log_head||l_api_name
3096 ,p_token => l_progress
3097 ,p_message => 'Adding to PO line id : '|| x_po_line_id );
3098
3099 END IF;
3100
3101
3102 /*Autocreate grouping. In case of ADD_TO_MOD action, there can be concurrent modifications.
3103 Hence lock has to taken on the current modification to which the line is grouped to*/
3104 IF PO_AUTOCREATE_PARAMS.g_mode = 'ADD' AND PO_AUTOCREATE_PARAMS.g_is_mod_exists THEN -- this is a Add PR lines to mod case.
3105
3106 l_entity_name_tbl.EXTEND(1);
3107 l_pk1_tbl.EXTEND(1);
3108 l_pk2_tbl.EXTEND(1);
3109 l_pk3_tbl.EXTEND(1);
3110 l_pk4_tbl.EXTEND(1);
3111
3112 l_entity_name_tbl(1) := PO_DRAFTS_PVT.G_LOCK_LINE_ENTITY;
3113 l_pk1_tbl(1) := x_po_line_id;
3114 l_pk2_tbl(1) := NULL;
3115 l_pk3_tbl(1) := NULL;
3116 l_pk4_tbl(1) := NULL;
3117
3118 PO_DRAFTS_PVT.lock_entities(
3119 p_entity_name_tbl => l_entity_name_tbl,
3120 p_draft_id => PO_AUTOCREATE_PARAMS.g_draft_id,
3121 p_pk1_tbl => l_pk1_tbl,
3122 p_pk2_tbl => l_pk2_tbl,
3123 p_pk3_tbl => l_pk3_tbl,
3124 p_pk4_tbl => l_pk4_tbl,
3125 x_return_status => l_return_status,
3126 x_results => x_results
3127 );
3128
3129
3130 IF l_return_status = 'E' THEN
3131 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
3132 p_token => l_progress,
3133 p_message => 'Derive_and_default_lines: Error while obtaining lock');
3134 Raise l_lock_exception;
3135 END IF;
3136
3137 END IF;
3138 /*Autocreate grouping end*/
3139
3140 IF (PO_AUTOCREATE_PARAMS.g_document_type = 'PO') THEN
3141 IF (PO_AUTOCREATE_PARAMS.g_document_subtype='STANDARD')
3142 AND nvl(l_manual_price_change_flag, 'N') <> 'Y'
3143 AND (p_lines.from_line_id_tbl(i) IS NOT NULL OR
3144 -- <FPJ Advanced Price START>
3145 p_lines.contract_id_tbl(i) IS NOT NULL OR
3146 l_enhanced_pricing_flag = 'Y'
3147 ) --Enhanced Pricing: Enable pricing call if pricing enhanced for the style selected
3148 -- <FPJ Advanced Price END>
3149 AND PO_AUTOCREATE_PARAMS.g_interface_source_code <> 'CONSUMPTION_ADVICE' THEN
3150
3151 l_progress := '540';
3152 BEGIN
3153 select pl.quantity
3154 into l_db_quantity
3155 from po_lines_draft_all pl --<Shared Proc FPJ>
3156 where pl.po_line_id = x_po_line_id
3157 AND pl.draft_id=po_autocreate_params.g_draft_id;
3158
3159 IF g_debug_stmt
3160 THEN
3161 PO_DEBUG.debug_stmt( p_log_head => g_log_head||l_api_name
3162 ,p_token => l_progress
3163 ,p_message => 'Quantity on the Line : '|| l_db_quantity );
3164
3165 END IF;
3166 exception
3167 when others then
3168 IF g_debug_stmt THEN
3169 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
3170 p_token => l_progress,
3171 p_message => 'EXCEPTION caught; SQL Code is '||SQLCODE||'; Error is '||SQLERRM);
3172 END IF;
3173 end;
3174
3175 IF po_autocreate_params.g_mode='ADD' THEN
3176 l_progress := '550';
3177 -- We get the pricing criteria from the min shipment if the grouping
3178 -- profiles are set such that multiple shipments get created when
3179 -- need by or ship to info are different on different lines.
3180
3181 PO_SOURCING2_SV.get_min_shipment_num(x_po_line_id,l_min_shipment_num);
3182
3183 l_progress := '560';
3184 BEGIN
3185 select poll.ship_to_location_id,
3186 poll.ship_to_organization_id,
3187 poll.need_by_date
3188 into l_ship_to_loc,
3189 l_ship_to_org,
3190 l_need_by_date
3191 from po_line_locations_draft_all poll
3192 where poll.po_line_id = x_po_line_id
3193 AND poll.draft_id= po_autocreate_params.g_draft_id
3194 and poll.shipment_num = l_min_shipment_num;
3195 EXCEPTION
3196 when others then
3197 IF g_debug_stmt THEN
3198 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
3199 p_token => l_progress,
3200 p_message => 'EXCEPTION caught; SQL Code is '||SQLCODE||'; Error is '||SQLERRM);
3201 END IF;
3202 END;
3203 END IF;
3204
3205 l_progress := '565';
3206
3207 BEGIN
3208 OPEN c_price_break(p_lines.line_num_tbl(i),p_lines.intf_line_id_tbl(i));
3209 FETCH c_price_break INTO x_pb_intf_line_id,l_need_by_date;
3210 CLOSE c_price_break;
3211
3212 IF (Nvl(p_lines.clm_info_flag_tbl(i),'N') ='N') THEN
3213 get_interface_shipto_info(x_pb_intf_line_id
3214 ,x_pb_destination_type_code
3215 ,l_ship_to_org
3216 ,x_pb_ship_to_loc);
3217 l_ship_to_loc := po_autocreate_mainproc_pvt.get_ship_to_loc(x_pb_ship_to_loc);
3218 END IF;
3219
3220 EXCEPTION
3221 WHEN OTHERS THEN
3222 IF g_debug_stmt THEN
3223 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
3224 p_token => l_progress,
3225 p_message => 'EXCEPTION caught; SQL Code is '||SQLCODE||'; Error is '||SQLERRM);
3226 END IF;
3227 END;
3228
3229 -- Get the profile option values to determine grouping criteria
3230
3231 l_needby_prf := fnd_profile.value('PO_NEED_BY_GROUPING');
3232 l_shipto_prf := fnd_profile.value('PO_SHIPTO_GROUPING');
3233
3234 IF nvl(l_needby_prf,'Y') = 'Y' THEN
3235 l_need_by_date := p_lines.need_by_date_tbl(i);
3236 END IF;
3237
3238 l_progress := '570';
3239
3240 IF nvl(l_shipto_prf,'Y') = 'Y' AND (Nvl(p_lines.clm_info_flag_tbl(i),'N')<> 'Y') THEN
3241 l_ship_to_org := p_lines.dest_organization_id_tbl(i);
3242 l_ship_to_loc := po_autocreate_mainproc_pvt.get_ship_to_loc(p_lines.deliver_to_loc_id_tbl(i));
3243 END IF;
3244
3245 l_progress := '580';
3246 -- <FPJ Advanced Price START>
3247 PO_SOURCING2_SV.get_break_price
3248 ( p_api_version => 1.0
3249 , p_order_quantity => x_quantity + nvl(l_db_quantity,0)
3250 , p_ship_to_org => l_ship_to_org
3251 , p_ship_to_loc => l_ship_to_loc
3252 , p_po_line_id => p_lines.from_line_id_tbl(i)
3253 , p_cum_flag => FALSE
3254 , p_need_by_date => l_need_by_date
3255 , p_line_location_id => NULL
3256 , p_contract_id => p_lines.contract_id_tbl(i)
3257 , p_org_id => PO_AUTOCREATE_PARAMS.g_purchasing_ou_id
3258 , p_supplier_id => p_lines.hd_vendor_id_tbl(i)
3259 , p_supplier_site_id => p_lines.hd_vendor_site_id_tbl(i)
3260 , p_creation_date => p_lines.creation_date_tbl(i)
3261 , p_order_header_id => po_autocreate_params.g_po_header_id
3262 , p_order_line_id => x_po_line_id
3263 , p_line_type_id => p_lines.line_type_id_tbl(i)
3264 , p_item_revision => p_lines.item_revision_tbl(i)
3265 , p_item_id => p_lines.item_id_tbl(i)
3266 , p_category_id => p_lines.category_id_tbl(i)
3267 , p_supplier_item_num => p_lines.vendor_product_num_tbl(i)
3268 , p_in_price => p_lines.base_unit_price_tbl(i)
3269 , p_uom => x_unit_of_measure
3270 , p_currency_code => p_lines.hd_currency_code_tbl(i)
3271 , p_pricing_call_src => l_pricing_call_src
3272 , x_base_unit_price => l_base_unit_price
3273 , x_price_break_id => l_price_break_id
3274 , x_price => x_unit_price
3275 , x_return_status => l_return_status
3276 );
3277 -- <FPJ Advanced Price END>
3278
3279 IF g_debug_stmt THEN
3280 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
3281 p_token => l_progress,
3282 p_message => 'Break Unit Price: '||x_unit_price
3283 || ' Price Break Id : ' || l_price_break_id
3284 || ' Base Unit Price : '|| l_base_unit_price
3285 );
3286 END IF;
3287
3288 IF (x_unit_price = 0) THEN
3289 x_unit_price := NULL;
3290 END IF;
3291
3292 UPDATE po_lines_draft_all
3293 SET unit_price= nvl(x_unit_price, unit_price)
3294 ,base_unit_price = nvl(l_base_unit_price, base_unit_price)
3295 ,from_line_location_id = l_price_break_id
3296
3297 WHERE
3298 draft_id = po_autocreate_params.g_draft_id
3299 AND po_line_id = x_po_line_id;
3300
3301 --- Need to modify the following code to update the proper table. -- serukull
3302 --All the shipments which have been created need to get the
3303 --new price as on the line for Standard POs.
3304 UPDATE po_line_locations_draft_all
3305 SET price_override = nvl(x_unit_price, price_override)
3306 WHERE po_line_id = x_po_line_id
3307 AND draft_id = po_autocreate_params.g_draft_id;
3308
3309 END IF;
3310
3311 l_progress := '590';
3312
3313 -- This update should not happen for RELEASES as this would update the BLANKET with REQ price
3314 -- because x_po_line_id is the blanket line_id for 'Release'
3315 -- Introducing the 'If' statement for checking that its not a release
3316 IF (PO_AUTOCREATE_PARAMS.g_document_subtype <> 'RELEASE') THEN
3317
3318 /** If FSP org and item combination is dual uom control, update the po lines secondary quantity
3319 with the default conversion based on the PO lines quantity **/
3320
3321 --<INVCONV R12> update secondary quantity/uom to null
3322
3323 IF g_debug_stmt
3324 THEN
3325 PO_DEBUG.debug_stmt( p_log_head => g_log_head||l_api_name
3326 ,p_token => l_progress
3327 ,p_message => 'Before updating the Lines draft Quantity : '|| x_quantity );
3328
3329 END IF;
3330
3331 /* CLM Autocreate grouping start
3332 The quantity/amount should be the aggregate of all the matched lines quantity/amount
3333 */
3334
3335 UPDATE po_lines_draft_all plda
3336 SET plda.quantity = Decode(plda.matching_basis,
3337 'QUANTITY', (nvl(plda.quantity,0) + nvl(x_quantity,0)),
3338 plda.quantity)
3339 ,plda.amount = Decode(plda.matching_basis,
3340 --<Bug 16321620> : Added a missing comma to avoid compilation issues
3341 'AMOUNT', (nvl(plda.amount,0) + nvl(p_lines.amount_tbl(i),0)),
3342 plda.amount)
3343 ,plda.closed_code ='OPEN'
3344 ,plda.closed_date = NULL
3345 ,plda.closed_by = NULL
3346 ,plda.secondary_quantity = NULL
3347 ,plda.secondary_unit_of_measure = NULL
3348 WHERE 1=1
3349 AND plda.draft_id = po_autocreate_params.g_draft_id
3350 AND plda.po_line_id = x_po_line_id
3351 RETURNING quantity INTO l_quantity_temp;
3352
3353 --<INVCONV R12 START>
3354 IF p_lines.it_secondary_uom_code_tbl(i) IS NOT NULL and l_quantity_temp > 0 THEN
3355 SELECT unit_of_measure
3356 INTO x_secondary_unit_def
3357 FROM mtl_units_of_measure
3358 WHERE uom_code = p_lines.it_secondary_uom_code_tbl(i) ;
3359
3360 PO_UOM_S.uom_convert (l_quantity_temp,
3361 x_unit_of_measure,
3362 p_lines.item_id_tbl(i),
3363 x_secondary_unit_def,
3364 x_secondary_quantity_def) ;
3365
3366 UPDATE po_lines_draft_all
3367 SET secondary_quantity = x_secondary_quantity_def,
3368 secondary_unit_of_measure = x_secondary_unit_def
3369 WHERE po_line_id = x_po_line_id
3370 AND draft_id=po_autocreate_params.g_draft_id;
3371 END IF;
3372 --<INVCONV R12 END>
3373 IF g_debug_stmt
3374 THEN
3375 PO_DEBUG.debug_stmt( p_log_head => g_log_head||l_api_name
3376 ,p_token => l_progress
3377 ,p_message => 'After updating the Lines draft Quantity : '|| l_quantity_temp );
3378
3379 END IF;
3380
3381 -- Added for Linking Requisition to modification project
3382 IF (po_autocreate_params.g_is_clm_po ='Y' AND po_autocreate_params.g_process_code = 'ADD_FUNDS') THEN
3383 UPDATE po_Lines_draft_all
3384 SET clm_exercised_flag = 'Y',
3385 clm_exercised_date = SYSDATE,
3386 change_status = 'UPDATE'
3387 WHERE po_line_id = x_po_line_id
3388 AND draft_id=po_autocreate_params.g_draft_id
3389 AND clm_option_indicator = 'O'
3390 AND Nvl(clm_exercised_flag,'N')='N';
3391 END IF;
3392
3393 END IF; --Release check for update
3394 END IF;
3395 END IF;
3396 END LOOP;
3397
3398 /*CLM Phase-2 Autocreate grouping*/
3399 --Stamping back the po_line_id back to the interface table
3400 FORALL i IN 1 .. p_lines.intf_line_id_tbl.Count
3401 UPDATE po_lines_interface
3402 SET po_line_id = p_lines.po_line_id_tbl(i)
3403 WHERE
3404 interface_line_id = p_lines.intf_line_id_tbl(i);
3405
3406 po_autocreate_params.g_clm_source_document_id := Nvl(p_lines.from_header_id_tbl(1),p_lines.contract_id_tbl(1));
3407
3408 FOR i IN 1 .. p_lines.intf_line_id_tbl.Count
3409 LOOP
3410 IF ( po_autocreate_params.g_clm_source_document_id = p_lines.from_header_id_tbl(i)) THEN
3411 po_autocreate_params.g_clm_source_document_id := p_lines.from_header_id_tbl(i);
3412 ELSIF (po_autocreate_params.g_clm_source_document_id = p_lines.contract_id_tbl(i)) THEN
3413 po_autocreate_params.g_clm_source_document_id := p_lines.contract_id_tbl(i);
3414 ELSE
3415 po_autocreate_params.g_clm_source_document_id := NULL;
3416 EXIT;
3417 END IF;
3418 END LOOP;
3419
3420 l_progress := '700';
3421 po_autocreate_params.g_number_records_processed := p_lines.po_line_id_tbl.count;
3422
3423 IF g_debug_stmt
3424 THEN
3425 PO_DEBUG.debug_stmt ( p_log_head => g_log_head||l_api_name
3426 ,p_token => l_progress
3427 ,p_message => 'po_autocreate_params.g_number_records_processed :'|| po_autocreate_params.g_number_records_processed );
3428
3429 END IF;
3430
3431 IF g_debug_stmt THEN
3432 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
3433 END IF;
3434
3435
3436 EXCEPTION
3437
3438 --autocreate grouping start
3439 WHEN l_lock_exception THEN
3440 IF g_debug_unexp THEN
3441 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name,
3442 p_progress => l_progress);
3443 END IF;
3444 po_message_s.sql_error('DERIVE_AND_DEFAULT_LINES',l_progress,sqlcode);
3445 PO_AUTOCREATE_PVT.wrapup();
3446 RAISE;
3447 --autocreate grouping end
3448
3449 WHEN OTHERS THEN
3450 IF g_debug_unexp THEN
3451 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name,
3452 p_progress => l_progress);
3453 END IF;
3454 po_message_s.sql_error('DERIVE_AND_DEFAULT_LINES',l_progress,sqlcode);
3455 PO_AUTOCREATE_PVT.wrapup();
3456 RAISE;
3457
3458 END derive_and_default_lines;
3459
3460 /* ============================================================================
3461 NAME: merge_to_price_diff_draft
3462 DESC: For Rate Based Temp Labour lines, populate the Price Differentials.
3463 ARGS: IN OUT : p_lines PO_AUTOCREATE_TYPES.lines_rec_type
3464 ALGM: This is required only if
3465 - The line is Rate Based Temp Labour line
3466 - The line is being created new
3467 This will create the default Price Differentials draft record.
3468 =========================================================================== */
3469 PROCEDURE merge_to_price_diff_draft( p_lines IN OUT NOCOPY PO_AUTOCREATE_TYPES.lines_rec_type)
3470 IS
3471
3472 l_progress VARCHAR2(3) := '000';
3473 l_api_name VARCHAR2(30) := 'merge_to_price_diff_draft';
3474
3475 BEGIN
3476
3477 IF g_debug_stmt THEN
3478 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
3479 END IF;
3480
3481 l_progress := '001';
3482
3483 IF p_price_diff.intf_price_diff_id_tbl IS NOT NULL THEN
3484 FORALL i IN 1 .. p_price_diff.intf_price_diff_id_tbl.Count
3485
3486 INSERT
3487 INTO PO_PRICE_DIFF_DRAFT
3488 (
3489 draft_id ,
3490 price_differential_id ,
3491 price_differential_num ,
3492 entity_type ,
3493 entity_id ,
3494 price_type ,
3495 enabled_flag ,
3496 min_multiplier ,
3497 max_multiplier ,
3498 multiplier ,
3499 last_update_date ,
3500 last_updated_by ,
3501 last_update_login ,
3502 creation_date ,
3503 created_by
3504 )
3505 VALUES
3506 (
3507 PO_AUTOCREATE_PARAMS.g_draft_id ,
3508 p_price_diff.intf_price_diff_id_tbl(i) ,
3509 p_price_diff.price_diff_num_tbl(i) ,
3510 p_price_diff.entity_type_tbl(i) ,
3511 p_price_diff.entity_id_tbl(i) ,
3512 p_price_diff.price_type_tbl(i) ,
3513 p_price_diff.enabled_flag_tbl(i) ,
3514 p_price_diff.min_multiplier_tbl(i) ,
3515 p_price_diff.max_multiplier_tbl(i) ,
3516 p_price_diff.multiplier_tbl(i) ,
3517 SYSDATE ,
3518 FND_GLOBAL.user_id ,
3519 FND_GLOBAL.login_id ,
3520 SYSDATE ,
3521 FND_GLOBAL.user_id
3522 );
3523 END IF;
3524
3525 l_progress := '002';
3526
3527 IF g_debug_stmt THEN
3528 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
3529 END IF;
3530
3531 EXCEPTION
3532 WHEN OTHERS THEN
3533 IF g_debug_unexp THEN
3534 PO_DEBUG.debug_exc( p_log_head => g_log_head||l_api_name, p_progress => l_progress);
3535 END IF;
3536
3537 --CLM Phase 2 changes : error handling
3538 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_MERGE_DIFF_ERR',x_token1_value => sqlerrm);
3539
3540 PO_MESSAGE_S.sql_error('PO_AUTO_LINE_PROCESS_PVT.merge_to_price_diff_draft', l_progress , SQLCODE);
3541 RAISE;
3542
3543 END merge_to_price_diff_draft;
3544
3545 /* ============================================================================
3546 NAME: merge_to_attr_values_draft
3547 DESC: For Rate Based Temp Labour lines, populate the Price Differentials.
3548 ARGS: IN OUT : p_lines PO_AUTOCREATE_TYPES.lines_rec_type
3549 ALGM: This is required only if
3550 - The line is being created new
3551 - The document is a GBPA
3552 This will create the attribute values draft record based on
3553 item defaults, requisition and Sourcing document.
3554 This also manages the attribute values tlp records as well.
3555 Sourcing inserts records into these interface tables.
3556 =========================================================================== */
3557 PROCEDURE merge_to_attr_values_draft(p_lines IN OUT NOCOPY PO_AUTOCREATE_TYPES.lines_rec_type)
3558 IS
3559 l_progress VARCHAR2(3) := '000';
3560 l_api_name VARCHAR2(30) := 'MERGE_TO_ATTR_VALUES_DRAFT';
3561 -- <Unified Catalog R12>
3562 g_ATTR_VALUES_NULL_ID CONSTANT NUMBER := PO_ATTRIBUTE_VALUES_PVT.g_ATTR_VALUES_NULL_ID;
3563
3564 BEGIN
3565
3566 IF g_debug_stmt THEN
3567 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
3568 END IF;
3569
3570 --<Unified Catalog R12 START>
3571 -- For intergration with Sourcing, during blanket creation from Renegotiate flow,
3572 -- we need to import all attributes for each line.
3573 -- to facilitate bulk update, we are storing the newly created po_line on
3574 -- po_attr_values_interface and po_attr_values_interface_tlp
3575
3576 IF(PO_AUTOCREATE_PARAMS.g_document_subtype = 'BLANKET' AND PO_AUTOCREATE_PARAMS.g_interface_source_code = 'SOURCING') THEN
3577
3578 l_progress:='001';
3579
3580 IF g_debug_stmt THEN
3581 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
3582 p_message =>'Updating PO_LINE_IDs in attribute interface tables: '||p_lines.po_line_id_tbl.COUNT);
3583 END IF;
3584
3585 --SQL What: Update the PO_LINE_ID on PO_ATTR_VALUES_INTERFACE table
3586 --SQL Why : To facilitate bulk update of attributes, later in the flow
3587 --SQL Join: interface_header_id, interface_line_id
3588
3589 FORALL i IN p_lines.po_line_id_tbl.FIRST.. p_lines.po_line_id_tbl.LAST
3590 UPDATE PO_ATTR_VALUES_INTERFACE
3591 SET po_line_id = p_lines.po_line_id_tbl(i),
3592 req_template_name = TO_CHAR(g_ATTR_VALUES_NULL_ID),
3593 req_template_line_num = TO_CHAR(g_ATTR_VALUES_NULL_ID),
3594 inventory_item_id = NVL(inventory_item_id, g_ATTR_VALUES_NULL_ID)
3595 WHERE po_attr_values_interface.interface_header_id = p_lines.intf_header_id_tbl(i)
3596 AND po_attr_values_interface.interface_line_id = p_lines.intf_line_id_tbl(i);
3597
3598 IF g_debug_stmt THEN
3599 PO_DEBUG.debug_stmt(p_log_head=>g_log_head||l_api_name,p_token=>l_progress,p_message=>'Number of PO_ATTR_VALUES_INTERFACE rows updated='||SQL% rowcount);
3600 END IF;
3601
3602 l_progress:='010';
3603 --SQL What: Update the PO_LINE_ID on PO_ATTR_VALUES_TLP_INTERFACE table
3604 --SQL Why : To facilitate bulk update of attributes, later in the flow
3605 --SQL Join: interface_header_id, interface_line_id
3606 FORALL i IN p_lines.po_line_id_tbl.FIRST.. p_lines.po_line_id_tbl.LAST
3607 UPDATE PO_ATTR_VALUES_TLP_INTERFACE
3608 SET po_line_id = p_lines.po_line_id_tbl(i),
3609 req_template_name = TO_CHAR(g_ATTR_VALUES_NULL_ID),
3610 req_template_line_num = TO_CHAR(g_ATTR_VALUES_NULL_ID),
3611 inventory_item_id = NVL(inventory_item_id, g_ATTR_VALUES_NULL_ID)
3612 WHERE po_attr_values_tlp_interface.interface_header_id = p_lines.intf_header_id_tbl(i)
3613 AND po_attr_values_tlp_interface.interface_line_id = p_lines.intf_line_id_tbl(i);
3614
3615 IF g_debug_stmt THEN
3616 PO_DEBUG.debug_stmt(p_log_head=>g_log_head||l_api_name,p_token=>l_progress,
3617 p_message=>'Number of PO_ATTR_VALUES_TLP_INTERFACE rows updated='||SQL% rowcount);
3618 END IF;
3619
3620 l_progress := '020';
3621 INSERT
3622 INTO PO_ATTRIBUTE_VALUES_DRAFT
3623 (
3624 draft_id,
3625 ATTACHMENT_URL,
3626 ATTRIBUTE_VALUES_ID,
3627 AVAILABILITY,
3628 CREATED_BY,
3629 CREATION_DATE,
3630 INVENTORY_ITEM_ID,
3631 IP_CATEGORY_ID,
3632 LAST_UPDATED_BY,
3633 LAST_UPDATE_DATE,
3634 LAST_UPDATE_LOGIN,
3635 LEAD_TIME,
3636 MANUFACTURER_PART_NUM,
3637 MANUFACTURER_URL,
3638 NUM_BASE_ATTRIBUTE1,
3639 NUM_BASE_ATTRIBUTE10,
3640 NUM_BASE_ATTRIBUTE100,
3641 NUM_BASE_ATTRIBUTE11,
3642 NUM_BASE_ATTRIBUTE12,
3643 NUM_BASE_ATTRIBUTE13,
3644 NUM_BASE_ATTRIBUTE14,
3645 NUM_BASE_ATTRIBUTE15,
3646 NUM_BASE_ATTRIBUTE16,
3647 NUM_BASE_ATTRIBUTE17,
3648 NUM_BASE_ATTRIBUTE18,
3649 NUM_BASE_ATTRIBUTE19,
3650 NUM_BASE_ATTRIBUTE2,
3651 NUM_BASE_ATTRIBUTE20,
3652 NUM_BASE_ATTRIBUTE21,
3653 NUM_BASE_ATTRIBUTE22,
3654 NUM_BASE_ATTRIBUTE23,
3655 NUM_BASE_ATTRIBUTE24,
3656 NUM_BASE_ATTRIBUTE25,
3657 NUM_BASE_ATTRIBUTE26,
3658 NUM_BASE_ATTRIBUTE27,
3659 NUM_BASE_ATTRIBUTE28,
3660 NUM_BASE_ATTRIBUTE29,
3661 NUM_BASE_ATTRIBUTE3,
3662 NUM_BASE_ATTRIBUTE30,
3663 NUM_BASE_ATTRIBUTE31,
3664 NUM_BASE_ATTRIBUTE32,
3665 NUM_BASE_ATTRIBUTE33,
3666 NUM_BASE_ATTRIBUTE34,
3667 NUM_BASE_ATTRIBUTE35,
3668 NUM_BASE_ATTRIBUTE36,
3669 NUM_BASE_ATTRIBUTE37,
3670 NUM_BASE_ATTRIBUTE38,
3671 NUM_BASE_ATTRIBUTE39,
3672 NUM_BASE_ATTRIBUTE4,
3673 NUM_BASE_ATTRIBUTE40,
3674 NUM_BASE_ATTRIBUTE41,
3675 NUM_BASE_ATTRIBUTE42,
3676 NUM_BASE_ATTRIBUTE43,
3677 NUM_BASE_ATTRIBUTE44,
3678 NUM_BASE_ATTRIBUTE45,
3679 NUM_BASE_ATTRIBUTE46,
3680 NUM_BASE_ATTRIBUTE47,
3681 NUM_BASE_ATTRIBUTE48,
3682 NUM_BASE_ATTRIBUTE49,
3683 NUM_BASE_ATTRIBUTE5,
3684 NUM_BASE_ATTRIBUTE50,
3685 NUM_BASE_ATTRIBUTE51,
3686 NUM_BASE_ATTRIBUTE52,
3687 NUM_BASE_ATTRIBUTE53,
3688 NUM_BASE_ATTRIBUTE54,
3689 NUM_BASE_ATTRIBUTE55,
3690 NUM_BASE_ATTRIBUTE56,
3691 NUM_BASE_ATTRIBUTE57,
3692 NUM_BASE_ATTRIBUTE58,
3693 NUM_BASE_ATTRIBUTE59,
3694 NUM_BASE_ATTRIBUTE6,
3695 NUM_BASE_ATTRIBUTE60,
3696 NUM_BASE_ATTRIBUTE61,
3697 NUM_BASE_ATTRIBUTE62,
3698 NUM_BASE_ATTRIBUTE63,
3699 NUM_BASE_ATTRIBUTE64,
3700 NUM_BASE_ATTRIBUTE65,
3701 NUM_BASE_ATTRIBUTE66,
3702 NUM_BASE_ATTRIBUTE67,
3703 NUM_BASE_ATTRIBUTE68,
3704 NUM_BASE_ATTRIBUTE69,
3705 NUM_BASE_ATTRIBUTE7,
3706 NUM_BASE_ATTRIBUTE70,
3707 NUM_BASE_ATTRIBUTE71,
3708 NUM_BASE_ATTRIBUTE72,
3709 NUM_BASE_ATTRIBUTE73,
3710 NUM_BASE_ATTRIBUTE74,
3711 NUM_BASE_ATTRIBUTE75,
3712 NUM_BASE_ATTRIBUTE76,
3713 NUM_BASE_ATTRIBUTE77,
3714 NUM_BASE_ATTRIBUTE78,
3715 NUM_BASE_ATTRIBUTE79,
3716 NUM_BASE_ATTRIBUTE8,
3717 NUM_BASE_ATTRIBUTE80,
3718 NUM_BASE_ATTRIBUTE81,
3719 NUM_BASE_ATTRIBUTE82,
3720 NUM_BASE_ATTRIBUTE83,
3721 NUM_BASE_ATTRIBUTE84,
3722 NUM_BASE_ATTRIBUTE85,
3723 NUM_BASE_ATTRIBUTE86,
3724 NUM_BASE_ATTRIBUTE87,
3725 NUM_BASE_ATTRIBUTE88,
3726 NUM_BASE_ATTRIBUTE89,
3727 NUM_BASE_ATTRIBUTE9,
3728 NUM_BASE_ATTRIBUTE90,
3729 NUM_BASE_ATTRIBUTE91,
3730 NUM_BASE_ATTRIBUTE92,
3731 NUM_BASE_ATTRIBUTE93,
3732 NUM_BASE_ATTRIBUTE94,
3733 NUM_BASE_ATTRIBUTE95,
3734 NUM_BASE_ATTRIBUTE96,
3735 NUM_BASE_ATTRIBUTE97,
3736 NUM_BASE_ATTRIBUTE98,
3737 NUM_BASE_ATTRIBUTE99,
3738 NUM_CAT_ATTRIBUTE1,
3739 NUM_CAT_ATTRIBUTE10,
3740 NUM_CAT_ATTRIBUTE11,
3741 NUM_CAT_ATTRIBUTE12,
3742 NUM_CAT_ATTRIBUTE13,
3743 NUM_CAT_ATTRIBUTE14,
3744 NUM_CAT_ATTRIBUTE15,
3745 NUM_CAT_ATTRIBUTE16,
3746 NUM_CAT_ATTRIBUTE17,
3747 NUM_CAT_ATTRIBUTE18,
3748 NUM_CAT_ATTRIBUTE19,
3749 NUM_CAT_ATTRIBUTE2,
3750 NUM_CAT_ATTRIBUTE20,
3751 NUM_CAT_ATTRIBUTE21,
3752 NUM_CAT_ATTRIBUTE22,
3753 NUM_CAT_ATTRIBUTE23,
3754 NUM_CAT_ATTRIBUTE24,
3755 NUM_CAT_ATTRIBUTE25,
3756 NUM_CAT_ATTRIBUTE26,
3757 NUM_CAT_ATTRIBUTE27,
3758 NUM_CAT_ATTRIBUTE28,
3759 NUM_CAT_ATTRIBUTE29,
3760 NUM_CAT_ATTRIBUTE3,
3761 NUM_CAT_ATTRIBUTE30,
3762 NUM_CAT_ATTRIBUTE31,
3763 NUM_CAT_ATTRIBUTE32,
3764 NUM_CAT_ATTRIBUTE33,
3765 NUM_CAT_ATTRIBUTE34,
3766 NUM_CAT_ATTRIBUTE35,
3767 NUM_CAT_ATTRIBUTE36,
3768 NUM_CAT_ATTRIBUTE37,
3769 NUM_CAT_ATTRIBUTE38,
3770 NUM_CAT_ATTRIBUTE39,
3771 NUM_CAT_ATTRIBUTE4,
3772 NUM_CAT_ATTRIBUTE40,
3773 NUM_CAT_ATTRIBUTE41,
3774 NUM_CAT_ATTRIBUTE42,
3775 NUM_CAT_ATTRIBUTE43,
3776 NUM_CAT_ATTRIBUTE44,
3777 NUM_CAT_ATTRIBUTE45,
3778 NUM_CAT_ATTRIBUTE46,
3779 NUM_CAT_ATTRIBUTE47,
3780 NUM_CAT_ATTRIBUTE48,
3781 NUM_CAT_ATTRIBUTE49,
3782 NUM_CAT_ATTRIBUTE5,
3783 NUM_CAT_ATTRIBUTE50,
3784 NUM_CAT_ATTRIBUTE6,
3785 NUM_CAT_ATTRIBUTE7,
3786 NUM_CAT_ATTRIBUTE8,
3787 NUM_CAT_ATTRIBUTE9,
3788 ORG_ID,
3789 PICTURE,
3790 PO_LINE_ID,
3791 PROGRAM_APPLICATION_ID,
3792 PROGRAM_ID,
3793 PROGRAM_UPDATE_DATE,
3794 REQUEST_ID,
3795 REQ_TEMPLATE_LINE_NUM,
3796 REQ_TEMPLATE_NAME,
3797 SUPPLIER_URL,
3798 TEXT_BASE_ATTRIBUTE1,
3799 TEXT_BASE_ATTRIBUTE10,
3800 TEXT_BASE_ATTRIBUTE100,
3801 TEXT_BASE_ATTRIBUTE11,
3802 TEXT_BASE_ATTRIBUTE12,
3803 TEXT_BASE_ATTRIBUTE13,
3804 TEXT_BASE_ATTRIBUTE14,
3805 TEXT_BASE_ATTRIBUTE15,
3806 TEXT_BASE_ATTRIBUTE16,
3807 TEXT_BASE_ATTRIBUTE17,
3808 TEXT_BASE_ATTRIBUTE18,
3809 TEXT_BASE_ATTRIBUTE19,
3810 TEXT_BASE_ATTRIBUTE2,
3811 TEXT_BASE_ATTRIBUTE20,
3812 TEXT_BASE_ATTRIBUTE21,
3813 TEXT_BASE_ATTRIBUTE22,
3814 TEXT_BASE_ATTRIBUTE23,
3815 TEXT_BASE_ATTRIBUTE24,
3816 TEXT_BASE_ATTRIBUTE25,
3817 TEXT_BASE_ATTRIBUTE26,
3818 TEXT_BASE_ATTRIBUTE27,
3819 TEXT_BASE_ATTRIBUTE28,
3820 TEXT_BASE_ATTRIBUTE29,
3821 TEXT_BASE_ATTRIBUTE3,
3822 TEXT_BASE_ATTRIBUTE30,
3823 TEXT_BASE_ATTRIBUTE31,
3824 TEXT_BASE_ATTRIBUTE32,
3825 TEXT_BASE_ATTRIBUTE33,
3826 TEXT_BASE_ATTRIBUTE34,
3827 TEXT_BASE_ATTRIBUTE35,
3828 TEXT_BASE_ATTRIBUTE36,
3829 TEXT_BASE_ATTRIBUTE37,
3830 TEXT_BASE_ATTRIBUTE38,
3831 TEXT_BASE_ATTRIBUTE39,
3832 TEXT_BASE_ATTRIBUTE4,
3833 TEXT_BASE_ATTRIBUTE40,
3834 TEXT_BASE_ATTRIBUTE41,
3835 TEXT_BASE_ATTRIBUTE42,
3836 TEXT_BASE_ATTRIBUTE43,
3837 TEXT_BASE_ATTRIBUTE44,
3838 TEXT_BASE_ATTRIBUTE45,
3839 TEXT_BASE_ATTRIBUTE46,
3840 TEXT_BASE_ATTRIBUTE47,
3841 TEXT_BASE_ATTRIBUTE48,
3842 TEXT_BASE_ATTRIBUTE49,
3843 TEXT_BASE_ATTRIBUTE5,
3844 TEXT_BASE_ATTRIBUTE50,
3845 TEXT_BASE_ATTRIBUTE51,
3846 TEXT_BASE_ATTRIBUTE52,
3847 TEXT_BASE_ATTRIBUTE53,
3848 TEXT_BASE_ATTRIBUTE54,
3849 TEXT_BASE_ATTRIBUTE55,
3850 TEXT_BASE_ATTRIBUTE56,
3851 TEXT_BASE_ATTRIBUTE57,
3852 TEXT_BASE_ATTRIBUTE58,
3853 TEXT_BASE_ATTRIBUTE59,
3854 TEXT_BASE_ATTRIBUTE6,
3855 TEXT_BASE_ATTRIBUTE60,
3856 TEXT_BASE_ATTRIBUTE61,
3857 TEXT_BASE_ATTRIBUTE62,
3858 TEXT_BASE_ATTRIBUTE63,
3859 TEXT_BASE_ATTRIBUTE64,
3860 TEXT_BASE_ATTRIBUTE65,
3861 TEXT_BASE_ATTRIBUTE66,
3862 TEXT_BASE_ATTRIBUTE67,
3863 TEXT_BASE_ATTRIBUTE68,
3864 TEXT_BASE_ATTRIBUTE69,
3865 TEXT_BASE_ATTRIBUTE7,
3866 TEXT_BASE_ATTRIBUTE70,
3867 TEXT_BASE_ATTRIBUTE71,
3868 TEXT_BASE_ATTRIBUTE72,
3869 TEXT_BASE_ATTRIBUTE73,
3870 TEXT_BASE_ATTRIBUTE74,
3871 TEXT_BASE_ATTRIBUTE75,
3872 TEXT_BASE_ATTRIBUTE76,
3873 TEXT_BASE_ATTRIBUTE77,
3874 TEXT_BASE_ATTRIBUTE78,
3875 TEXT_BASE_ATTRIBUTE79,
3876 TEXT_BASE_ATTRIBUTE8,
3877 TEXT_BASE_ATTRIBUTE80,
3878 TEXT_BASE_ATTRIBUTE81,
3879 TEXT_BASE_ATTRIBUTE82,
3880 TEXT_BASE_ATTRIBUTE83,
3881 TEXT_BASE_ATTRIBUTE84,
3882 TEXT_BASE_ATTRIBUTE85,
3883 TEXT_BASE_ATTRIBUTE86,
3884 TEXT_BASE_ATTRIBUTE87,
3885 TEXT_BASE_ATTRIBUTE88,
3886 TEXT_BASE_ATTRIBUTE89,
3887 TEXT_BASE_ATTRIBUTE9,
3888 TEXT_BASE_ATTRIBUTE90,
3889 TEXT_BASE_ATTRIBUTE91,
3890 TEXT_BASE_ATTRIBUTE92,
3891 TEXT_BASE_ATTRIBUTE93,
3892 TEXT_BASE_ATTRIBUTE94,
3893 TEXT_BASE_ATTRIBUTE95,
3894 TEXT_BASE_ATTRIBUTE96,
3895 TEXT_BASE_ATTRIBUTE97,
3896 TEXT_BASE_ATTRIBUTE98,
3897 TEXT_BASE_ATTRIBUTE99,
3898 TEXT_CAT_ATTRIBUTE1,
3899 TEXT_CAT_ATTRIBUTE10,
3900 TEXT_CAT_ATTRIBUTE11,
3901 TEXT_CAT_ATTRIBUTE12,
3902 TEXT_CAT_ATTRIBUTE13,
3903 TEXT_CAT_ATTRIBUTE14,
3904 TEXT_CAT_ATTRIBUTE15,
3905 TEXT_CAT_ATTRIBUTE16,
3906 TEXT_CAT_ATTRIBUTE17,
3907 TEXT_CAT_ATTRIBUTE18,
3908 TEXT_CAT_ATTRIBUTE19,
3909 TEXT_CAT_ATTRIBUTE2,
3910 TEXT_CAT_ATTRIBUTE20,
3911 TEXT_CAT_ATTRIBUTE21,
3912 TEXT_CAT_ATTRIBUTE22,
3913 TEXT_CAT_ATTRIBUTE23,
3914 TEXT_CAT_ATTRIBUTE24,
3915 TEXT_CAT_ATTRIBUTE25,
3916 TEXT_CAT_ATTRIBUTE26,
3917 TEXT_CAT_ATTRIBUTE27,
3918 TEXT_CAT_ATTRIBUTE28,
3919 TEXT_CAT_ATTRIBUTE29,
3920 TEXT_CAT_ATTRIBUTE3,
3921 TEXT_CAT_ATTRIBUTE30,
3922 TEXT_CAT_ATTRIBUTE31,
3923 TEXT_CAT_ATTRIBUTE32,
3924 TEXT_CAT_ATTRIBUTE33,
3925 TEXT_CAT_ATTRIBUTE34,
3926 TEXT_CAT_ATTRIBUTE35,
3927 TEXT_CAT_ATTRIBUTE36,
3928 TEXT_CAT_ATTRIBUTE37,
3929 TEXT_CAT_ATTRIBUTE38,
3930 TEXT_CAT_ATTRIBUTE39,
3931 TEXT_CAT_ATTRIBUTE4,
3932 TEXT_CAT_ATTRIBUTE40,
3933 TEXT_CAT_ATTRIBUTE41,
3934 TEXT_CAT_ATTRIBUTE42,
3935 TEXT_CAT_ATTRIBUTE43,
3936 TEXT_CAT_ATTRIBUTE44,
3937 TEXT_CAT_ATTRIBUTE45,
3938 TEXT_CAT_ATTRIBUTE46,
3939 TEXT_CAT_ATTRIBUTE47,
3940 TEXT_CAT_ATTRIBUTE48,
3941 TEXT_CAT_ATTRIBUTE49,
3942 TEXT_CAT_ATTRIBUTE5,
3943 TEXT_CAT_ATTRIBUTE50,
3944 TEXT_CAT_ATTRIBUTE6,
3945 TEXT_CAT_ATTRIBUTE7,
3946 TEXT_CAT_ATTRIBUTE8,
3947 TEXT_CAT_ATTRIBUTE9,
3948 THUMBNAIL_IMAGE,
3949 UNSPSC,
3950 LAST_UPDATED_PROGRAM
3951 )
3952 SELECT PO_AUTOCREATE_PARAMS.g_draft_id,
3953 ATTACHMENT_URL,
3954 PO_ATTRIBUTE_VALUES_S.nextval,
3955 AVAILABILITY,
3956 CREATED_BY,
3957 CREATION_DATE,
3958 INVENTORY_ITEM_ID,
3959 IP_CATEGORY_ID,
3960 LAST_UPDATED_BY,
3961 LAST_UPDATE_DATE,
3962 LAST_UPDATE_LOGIN,
3963 LEAD_TIME,
3964 MANUFACTURER_PART_NUM,
3965 MANUFACTURER_URL,
3966 NUM_BASE_ATTRIBUTE1,
3967 NUM_BASE_ATTRIBUTE10,
3968 NUM_BASE_ATTRIBUTE100,
3969 NUM_BASE_ATTRIBUTE11,
3970 NUM_BASE_ATTRIBUTE12,
3971 NUM_BASE_ATTRIBUTE13,
3972 NUM_BASE_ATTRIBUTE14,
3973 NUM_BASE_ATTRIBUTE15,
3974 NUM_BASE_ATTRIBUTE16,
3975 NUM_BASE_ATTRIBUTE17,
3976 NUM_BASE_ATTRIBUTE18,
3977 NUM_BASE_ATTRIBUTE19,
3978 NUM_BASE_ATTRIBUTE2,
3979 NUM_BASE_ATTRIBUTE20,
3980 NUM_BASE_ATTRIBUTE21,
3981 NUM_BASE_ATTRIBUTE22,
3982 NUM_BASE_ATTRIBUTE23,
3983 NUM_BASE_ATTRIBUTE24,
3984 NUM_BASE_ATTRIBUTE25,
3985 NUM_BASE_ATTRIBUTE26,
3986 NUM_BASE_ATTRIBUTE27,
3987 NUM_BASE_ATTRIBUTE28,
3988 NUM_BASE_ATTRIBUTE29,
3989 NUM_BASE_ATTRIBUTE3,
3990 NUM_BASE_ATTRIBUTE30,
3991 NUM_BASE_ATTRIBUTE31,
3992 NUM_BASE_ATTRIBUTE32,
3993 NUM_BASE_ATTRIBUTE33,
3994 NUM_BASE_ATTRIBUTE34,
3995 NUM_BASE_ATTRIBUTE35,
3996 NUM_BASE_ATTRIBUTE36,
3997 NUM_BASE_ATTRIBUTE37,
3998 NUM_BASE_ATTRIBUTE38,
3999 NUM_BASE_ATTRIBUTE39,
4000 NUM_BASE_ATTRIBUTE4,
4001 NUM_BASE_ATTRIBUTE40,
4002 NUM_BASE_ATTRIBUTE41,
4003 NUM_BASE_ATTRIBUTE42,
4004 NUM_BASE_ATTRIBUTE43,
4005 NUM_BASE_ATTRIBUTE44,
4006 NUM_BASE_ATTRIBUTE45,
4007 NUM_BASE_ATTRIBUTE46,
4008 NUM_BASE_ATTRIBUTE47,
4009 NUM_BASE_ATTRIBUTE48,
4010 NUM_BASE_ATTRIBUTE49,
4011 NUM_BASE_ATTRIBUTE5,
4012 NUM_BASE_ATTRIBUTE50,
4013 NUM_BASE_ATTRIBUTE51,
4014 NUM_BASE_ATTRIBUTE52,
4015 NUM_BASE_ATTRIBUTE53,
4016 NUM_BASE_ATTRIBUTE54,
4017 NUM_BASE_ATTRIBUTE55,
4018 NUM_BASE_ATTRIBUTE56,
4019 NUM_BASE_ATTRIBUTE57,
4020 NUM_BASE_ATTRIBUTE58,
4021 NUM_BASE_ATTRIBUTE59,
4022 NUM_BASE_ATTRIBUTE6,
4023 NUM_BASE_ATTRIBUTE60,
4024 NUM_BASE_ATTRIBUTE61,
4025 NUM_BASE_ATTRIBUTE62,
4026 NUM_BASE_ATTRIBUTE63,
4027 NUM_BASE_ATTRIBUTE64,
4028 NUM_BASE_ATTRIBUTE65,
4029 NUM_BASE_ATTRIBUTE66,
4030 NUM_BASE_ATTRIBUTE67,
4031 NUM_BASE_ATTRIBUTE68,
4032 NUM_BASE_ATTRIBUTE69,
4033 NUM_BASE_ATTRIBUTE7,
4034 NUM_BASE_ATTRIBUTE70,
4035 NUM_BASE_ATTRIBUTE71,
4036 NUM_BASE_ATTRIBUTE72,
4037 NUM_BASE_ATTRIBUTE73,
4038 NUM_BASE_ATTRIBUTE74,
4039 NUM_BASE_ATTRIBUTE75,
4040 NUM_BASE_ATTRIBUTE76,
4041 NUM_BASE_ATTRIBUTE77,
4042 NUM_BASE_ATTRIBUTE78,
4043 NUM_BASE_ATTRIBUTE79,
4044 NUM_BASE_ATTRIBUTE8,
4045 NUM_BASE_ATTRIBUTE80,
4046 NUM_BASE_ATTRIBUTE81,
4047 NUM_BASE_ATTRIBUTE82,
4048 NUM_BASE_ATTRIBUTE83,
4049 NUM_BASE_ATTRIBUTE84,
4050 NUM_BASE_ATTRIBUTE85,
4051 NUM_BASE_ATTRIBUTE86,
4052 NUM_BASE_ATTRIBUTE87,
4053 NUM_BASE_ATTRIBUTE88,
4054 NUM_BASE_ATTRIBUTE89,
4055 NUM_BASE_ATTRIBUTE9,
4056 NUM_BASE_ATTRIBUTE90,
4057 NUM_BASE_ATTRIBUTE91,
4058 NUM_BASE_ATTRIBUTE92,
4059 NUM_BASE_ATTRIBUTE93,
4060 NUM_BASE_ATTRIBUTE94,
4061 NUM_BASE_ATTRIBUTE95,
4062 NUM_BASE_ATTRIBUTE96,
4063 NUM_BASE_ATTRIBUTE97,
4064 NUM_BASE_ATTRIBUTE98,
4065 NUM_BASE_ATTRIBUTE99,
4066 NUM_CAT_ATTRIBUTE1,
4067 NUM_CAT_ATTRIBUTE10,
4068 NUM_CAT_ATTRIBUTE11,
4069 NUM_CAT_ATTRIBUTE12,
4070 NUM_CAT_ATTRIBUTE13,
4071 NUM_CAT_ATTRIBUTE14,
4072 NUM_CAT_ATTRIBUTE15,
4073 NUM_CAT_ATTRIBUTE16,
4074 NUM_CAT_ATTRIBUTE17,
4075 NUM_CAT_ATTRIBUTE18,
4076 NUM_CAT_ATTRIBUTE19,
4077 NUM_CAT_ATTRIBUTE2,
4078 NUM_CAT_ATTRIBUTE20,
4079 NUM_CAT_ATTRIBUTE21,
4080 NUM_CAT_ATTRIBUTE22,
4081 NUM_CAT_ATTRIBUTE23,
4082 NUM_CAT_ATTRIBUTE24,
4083 NUM_CAT_ATTRIBUTE25,
4084 NUM_CAT_ATTRIBUTE26,
4085 NUM_CAT_ATTRIBUTE27,
4086 NUM_CAT_ATTRIBUTE28,
4087 NUM_CAT_ATTRIBUTE29,
4088 NUM_CAT_ATTRIBUTE3,
4089 NUM_CAT_ATTRIBUTE30,
4090 NUM_CAT_ATTRIBUTE31,
4091 NUM_CAT_ATTRIBUTE32,
4092 NUM_CAT_ATTRIBUTE33,
4093 NUM_CAT_ATTRIBUTE34,
4094 NUM_CAT_ATTRIBUTE35,
4095 NUM_CAT_ATTRIBUTE36,
4096 NUM_CAT_ATTRIBUTE37,
4097 NUM_CAT_ATTRIBUTE38,
4098 NUM_CAT_ATTRIBUTE39,
4099 NUM_CAT_ATTRIBUTE4,
4100 NUM_CAT_ATTRIBUTE40,
4101 NUM_CAT_ATTRIBUTE41,
4102 NUM_CAT_ATTRIBUTE42,
4103 NUM_CAT_ATTRIBUTE43,
4104 NUM_CAT_ATTRIBUTE44,
4105 NUM_CAT_ATTRIBUTE45,
4106 NUM_CAT_ATTRIBUTE46,
4107 NUM_CAT_ATTRIBUTE47,
4108 NUM_CAT_ATTRIBUTE48,
4109 NUM_CAT_ATTRIBUTE49,
4110 NUM_CAT_ATTRIBUTE5,
4111 NUM_CAT_ATTRIBUTE50,
4112 NUM_CAT_ATTRIBUTE6,
4113 NUM_CAT_ATTRIBUTE7,
4114 NUM_CAT_ATTRIBUTE8,
4115 NUM_CAT_ATTRIBUTE9,
4116 ORG_ID,
4117 PICTURE,
4118 PO_LINE_ID,
4119 PROGRAM_APPLICATION_ID,
4120 PROGRAM_ID,
4121 PROGRAM_UPDATE_DATE,
4122 REQUEST_ID,
4123 REQ_TEMPLATE_LINE_NUM,
4124 REQ_TEMPLATE_NAME,
4125 SUPPLIER_URL,
4126 TEXT_BASE_ATTRIBUTE1,
4127 TEXT_BASE_ATTRIBUTE10,
4128 TEXT_BASE_ATTRIBUTE100,
4129 TEXT_BASE_ATTRIBUTE11,
4130 TEXT_BASE_ATTRIBUTE12,
4131 TEXT_BASE_ATTRIBUTE13,
4132 TEXT_BASE_ATTRIBUTE14,
4133 TEXT_BASE_ATTRIBUTE15,
4134 TEXT_BASE_ATTRIBUTE16,
4135 TEXT_BASE_ATTRIBUTE17,
4136 TEXT_BASE_ATTRIBUTE18,
4137 TEXT_BASE_ATTRIBUTE19,
4138 TEXT_BASE_ATTRIBUTE2,
4139 TEXT_BASE_ATTRIBUTE20,
4140 TEXT_BASE_ATTRIBUTE21,
4141 TEXT_BASE_ATTRIBUTE22,
4142 TEXT_BASE_ATTRIBUTE23,
4143 TEXT_BASE_ATTRIBUTE24,
4144 TEXT_BASE_ATTRIBUTE25,
4145 TEXT_BASE_ATTRIBUTE26,
4146 TEXT_BASE_ATTRIBUTE27,
4147 TEXT_BASE_ATTRIBUTE28,
4148 TEXT_BASE_ATTRIBUTE29,
4149 TEXT_BASE_ATTRIBUTE3,
4150 TEXT_BASE_ATTRIBUTE30,
4151 TEXT_BASE_ATTRIBUTE31,
4152 TEXT_BASE_ATTRIBUTE32,
4153 TEXT_BASE_ATTRIBUTE33,
4154 TEXT_BASE_ATTRIBUTE34,
4155 TEXT_BASE_ATTRIBUTE35,
4156 TEXT_BASE_ATTRIBUTE36,
4157 TEXT_BASE_ATTRIBUTE37,
4158 TEXT_BASE_ATTRIBUTE38,
4159 TEXT_BASE_ATTRIBUTE39,
4160 TEXT_BASE_ATTRIBUTE4,
4161 TEXT_BASE_ATTRIBUTE40,
4162 TEXT_BASE_ATTRIBUTE41,
4163 TEXT_BASE_ATTRIBUTE42,
4164 TEXT_BASE_ATTRIBUTE43,
4165 TEXT_BASE_ATTRIBUTE44,
4166 TEXT_BASE_ATTRIBUTE45,
4167 TEXT_BASE_ATTRIBUTE46,
4168 TEXT_BASE_ATTRIBUTE47,
4169 TEXT_BASE_ATTRIBUTE48,
4170 TEXT_BASE_ATTRIBUTE49,
4171 TEXT_BASE_ATTRIBUTE5,
4172 TEXT_BASE_ATTRIBUTE50,
4173 TEXT_BASE_ATTRIBUTE51,
4174 TEXT_BASE_ATTRIBUTE52,
4175 TEXT_BASE_ATTRIBUTE53,
4176 TEXT_BASE_ATTRIBUTE54,
4177 TEXT_BASE_ATTRIBUTE55,
4178 TEXT_BASE_ATTRIBUTE56,
4179 TEXT_BASE_ATTRIBUTE57,
4180 TEXT_BASE_ATTRIBUTE58,
4181 TEXT_BASE_ATTRIBUTE59,
4182 TEXT_BASE_ATTRIBUTE6,
4183 TEXT_BASE_ATTRIBUTE60,
4184 TEXT_BASE_ATTRIBUTE61,
4185 TEXT_BASE_ATTRIBUTE62,
4186 TEXT_BASE_ATTRIBUTE63,
4187 TEXT_BASE_ATTRIBUTE64,
4188 TEXT_BASE_ATTRIBUTE65,
4189 TEXT_BASE_ATTRIBUTE66,
4190 TEXT_BASE_ATTRIBUTE67,
4191 TEXT_BASE_ATTRIBUTE68,
4192 TEXT_BASE_ATTRIBUTE69,
4193 TEXT_BASE_ATTRIBUTE7,
4194 TEXT_BASE_ATTRIBUTE70,
4195 TEXT_BASE_ATTRIBUTE71,
4196 TEXT_BASE_ATTRIBUTE72,
4197 TEXT_BASE_ATTRIBUTE73,
4198 TEXT_BASE_ATTRIBUTE74,
4199 TEXT_BASE_ATTRIBUTE75,
4200 TEXT_BASE_ATTRIBUTE76,
4201 TEXT_BASE_ATTRIBUTE77,
4202 TEXT_BASE_ATTRIBUTE78,
4203 TEXT_BASE_ATTRIBUTE79,
4204 TEXT_BASE_ATTRIBUTE8,
4205 TEXT_BASE_ATTRIBUTE80,
4206 TEXT_BASE_ATTRIBUTE81,
4207 TEXT_BASE_ATTRIBUTE82,
4208 TEXT_BASE_ATTRIBUTE83,
4209 TEXT_BASE_ATTRIBUTE84,
4210 TEXT_BASE_ATTRIBUTE85,
4211 TEXT_BASE_ATTRIBUTE86,
4212 TEXT_BASE_ATTRIBUTE87,
4213 TEXT_BASE_ATTRIBUTE88,
4214 TEXT_BASE_ATTRIBUTE89,
4215 TEXT_BASE_ATTRIBUTE9,
4216 TEXT_BASE_ATTRIBUTE90,
4217 TEXT_BASE_ATTRIBUTE91,
4218 TEXT_BASE_ATTRIBUTE92,
4219 TEXT_BASE_ATTRIBUTE93,
4220 TEXT_BASE_ATTRIBUTE94,
4221 TEXT_BASE_ATTRIBUTE95,
4222 TEXT_BASE_ATTRIBUTE96,
4223 TEXT_BASE_ATTRIBUTE97,
4224 TEXT_BASE_ATTRIBUTE98,
4225 TEXT_BASE_ATTRIBUTE99,
4226 TEXT_CAT_ATTRIBUTE1,
4227 TEXT_CAT_ATTRIBUTE10,
4228 TEXT_CAT_ATTRIBUTE11,
4229 TEXT_CAT_ATTRIBUTE12,
4230 TEXT_CAT_ATTRIBUTE13,
4231 TEXT_CAT_ATTRIBUTE14,
4232 TEXT_CAT_ATTRIBUTE15,
4233 TEXT_CAT_ATTRIBUTE16,
4234 TEXT_CAT_ATTRIBUTE17,
4235 TEXT_CAT_ATTRIBUTE18,
4236 TEXT_CAT_ATTRIBUTE19,
4237 TEXT_CAT_ATTRIBUTE2,
4238 TEXT_CAT_ATTRIBUTE20,
4239 TEXT_CAT_ATTRIBUTE21,
4240 TEXT_CAT_ATTRIBUTE22,
4241 TEXT_CAT_ATTRIBUTE23,
4242 TEXT_CAT_ATTRIBUTE24,
4243 TEXT_CAT_ATTRIBUTE25,
4244 TEXT_CAT_ATTRIBUTE26,
4245 TEXT_CAT_ATTRIBUTE27,
4246 TEXT_CAT_ATTRIBUTE28,
4247 TEXT_CAT_ATTRIBUTE29,
4248 TEXT_CAT_ATTRIBUTE3,
4249 TEXT_CAT_ATTRIBUTE30,
4250 TEXT_CAT_ATTRIBUTE31,
4251 TEXT_CAT_ATTRIBUTE32,
4252 TEXT_CAT_ATTRIBUTE33,
4253 TEXT_CAT_ATTRIBUTE34,
4254 TEXT_CAT_ATTRIBUTE35,
4255 TEXT_CAT_ATTRIBUTE36,
4256 TEXT_CAT_ATTRIBUTE37,
4257 TEXT_CAT_ATTRIBUTE38,
4258 TEXT_CAT_ATTRIBUTE39,
4259 TEXT_CAT_ATTRIBUTE4,
4260 TEXT_CAT_ATTRIBUTE40,
4261 TEXT_CAT_ATTRIBUTE41,
4262 TEXT_CAT_ATTRIBUTE42,
4263 TEXT_CAT_ATTRIBUTE43,
4264 TEXT_CAT_ATTRIBUTE44,
4265 TEXT_CAT_ATTRIBUTE45,
4266 TEXT_CAT_ATTRIBUTE46,
4267 TEXT_CAT_ATTRIBUTE47,
4268 TEXT_CAT_ATTRIBUTE48,
4269 TEXT_CAT_ATTRIBUTE49,
4270 TEXT_CAT_ATTRIBUTE5,
4271 TEXT_CAT_ATTRIBUTE50,
4272 TEXT_CAT_ATTRIBUTE6,
4273 TEXT_CAT_ATTRIBUTE7,
4274 TEXT_CAT_ATTRIBUTE8,
4275 TEXT_CAT_ATTRIBUTE9,
4276 THUMBNAIL_IMAGE,
4277 UNSPSC,
4278 'AUTOCREATE_BACKEND_FOR_SOURCING'
4279 FROM PO_ATTR_VALUES_INTERFACE
4280 WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
4281
4282 IF g_debug_stmt THEN
4283 PO_DEBUG.debug_stmt(p_log_head=>g_log_head||l_api_name ,p_token=>l_progress ,p_message=>'Number of rows inserted in PO_ATTRIBUTE_VALUES table='||SQL%ROWCOUNT );
4284 END IF;
4285
4286 l_progress := '030';
4287 --insert tlp records from interface table
4288
4289 INSERT
4290 INTO PO_ATTRIBUTE_VALUES_TLP_DRAFT
4291 (
4292 draft_id,
4293 ALIAS,
4294 ATTRIBUTE_VALUES_TLP_ID,
4295 COMMENTS,
4296 CREATED_BY,
4297 CREATION_DATE,
4298 DESCRIPTION,
4299 INVENTORY_ITEM_ID,
4300 IP_CATEGORY_ID,
4301 LANGUAGE,
4302 LAST_UPDATED_BY,
4303 LAST_UPDATE_DATE,
4304 LAST_UPDATE_LOGIN,
4305 LONG_DESCRIPTION,
4306 MANUFACTURER,
4307 ORG_ID,
4308 PO_LINE_ID,
4309 PROGRAM_APPLICATION_ID,
4310 PROGRAM_ID,
4311 PROGRAM_UPDATE_DATE,
4312 REQUEST_ID,
4313 REQ_TEMPLATE_LINE_NUM,
4314 REQ_TEMPLATE_NAME,
4315 TL_TEXT_BASE_ATTRIBUTE1,
4316 TL_TEXT_BASE_ATTRIBUTE10,
4317 TL_TEXT_BASE_ATTRIBUTE100,
4318 TL_TEXT_BASE_ATTRIBUTE11,
4319 TL_TEXT_BASE_ATTRIBUTE12,
4320 TL_TEXT_BASE_ATTRIBUTE13,
4321 TL_TEXT_BASE_ATTRIBUTE14,
4322 TL_TEXT_BASE_ATTRIBUTE15,
4323 TL_TEXT_BASE_ATTRIBUTE16,
4324 TL_TEXT_BASE_ATTRIBUTE17,
4325 TL_TEXT_BASE_ATTRIBUTE18,
4326 TL_TEXT_BASE_ATTRIBUTE19,
4327 TL_TEXT_BASE_ATTRIBUTE2,
4328 TL_TEXT_BASE_ATTRIBUTE20,
4329 TL_TEXT_BASE_ATTRIBUTE21,
4330 TL_TEXT_BASE_ATTRIBUTE22,
4331 TL_TEXT_BASE_ATTRIBUTE23,
4332 TL_TEXT_BASE_ATTRIBUTE24,
4333 TL_TEXT_BASE_ATTRIBUTE25,
4334 TL_TEXT_BASE_ATTRIBUTE26,
4335 TL_TEXT_BASE_ATTRIBUTE27,
4336 TL_TEXT_BASE_ATTRIBUTE28,
4337 TL_TEXT_BASE_ATTRIBUTE29,
4338 TL_TEXT_BASE_ATTRIBUTE3,
4339 TL_TEXT_BASE_ATTRIBUTE30,
4340 TL_TEXT_BASE_ATTRIBUTE31,
4341 TL_TEXT_BASE_ATTRIBUTE32,
4342 TL_TEXT_BASE_ATTRIBUTE33,
4343 TL_TEXT_BASE_ATTRIBUTE34,
4344 TL_TEXT_BASE_ATTRIBUTE35,
4345 TL_TEXT_BASE_ATTRIBUTE36,
4346 TL_TEXT_BASE_ATTRIBUTE37,
4347 TL_TEXT_BASE_ATTRIBUTE38,
4348 TL_TEXT_BASE_ATTRIBUTE39,
4349 TL_TEXT_BASE_ATTRIBUTE4,
4350 TL_TEXT_BASE_ATTRIBUTE40,
4351 TL_TEXT_BASE_ATTRIBUTE41,
4352 TL_TEXT_BASE_ATTRIBUTE42,
4353 TL_TEXT_BASE_ATTRIBUTE43,
4354 TL_TEXT_BASE_ATTRIBUTE44,
4355 TL_TEXT_BASE_ATTRIBUTE45,
4356 TL_TEXT_BASE_ATTRIBUTE46,
4357 TL_TEXT_BASE_ATTRIBUTE47,
4358 TL_TEXT_BASE_ATTRIBUTE48,
4359 TL_TEXT_BASE_ATTRIBUTE49,
4360 TL_TEXT_BASE_ATTRIBUTE5,
4361 TL_TEXT_BASE_ATTRIBUTE50,
4362 TL_TEXT_BASE_ATTRIBUTE51,
4363 TL_TEXT_BASE_ATTRIBUTE52,
4364 TL_TEXT_BASE_ATTRIBUTE53,
4365 TL_TEXT_BASE_ATTRIBUTE54,
4366 TL_TEXT_BASE_ATTRIBUTE55,
4367 TL_TEXT_BASE_ATTRIBUTE56,
4368 TL_TEXT_BASE_ATTRIBUTE57,
4369 TL_TEXT_BASE_ATTRIBUTE58,
4370 TL_TEXT_BASE_ATTRIBUTE59,
4371 TL_TEXT_BASE_ATTRIBUTE6,
4372 TL_TEXT_BASE_ATTRIBUTE60,
4373 TL_TEXT_BASE_ATTRIBUTE61,
4374 TL_TEXT_BASE_ATTRIBUTE62,
4375 TL_TEXT_BASE_ATTRIBUTE63,
4376 TL_TEXT_BASE_ATTRIBUTE64,
4377 TL_TEXT_BASE_ATTRIBUTE65,
4378 TL_TEXT_BASE_ATTRIBUTE66,
4379 TL_TEXT_BASE_ATTRIBUTE67,
4380 TL_TEXT_BASE_ATTRIBUTE68,
4381 TL_TEXT_BASE_ATTRIBUTE69,
4382 TL_TEXT_BASE_ATTRIBUTE7,
4383 TL_TEXT_BASE_ATTRIBUTE70,
4384 TL_TEXT_BASE_ATTRIBUTE71,
4385 TL_TEXT_BASE_ATTRIBUTE72,
4386 TL_TEXT_BASE_ATTRIBUTE73,
4387 TL_TEXT_BASE_ATTRIBUTE74,
4388 TL_TEXT_BASE_ATTRIBUTE75,
4389 TL_TEXT_BASE_ATTRIBUTE76,
4390 TL_TEXT_BASE_ATTRIBUTE77,
4391 TL_TEXT_BASE_ATTRIBUTE78,
4392 TL_TEXT_BASE_ATTRIBUTE79,
4393 TL_TEXT_BASE_ATTRIBUTE8,
4394 TL_TEXT_BASE_ATTRIBUTE80,
4395 TL_TEXT_BASE_ATTRIBUTE81,
4396 TL_TEXT_BASE_ATTRIBUTE82,
4397 TL_TEXT_BASE_ATTRIBUTE83,
4398 TL_TEXT_BASE_ATTRIBUTE84,
4399 TL_TEXT_BASE_ATTRIBUTE85,
4400 TL_TEXT_BASE_ATTRIBUTE86,
4401 TL_TEXT_BASE_ATTRIBUTE87,
4402 TL_TEXT_BASE_ATTRIBUTE88,
4403 TL_TEXT_BASE_ATTRIBUTE89,
4404 TL_TEXT_BASE_ATTRIBUTE9,
4405 TL_TEXT_BASE_ATTRIBUTE90,
4406 TL_TEXT_BASE_ATTRIBUTE91,
4407 TL_TEXT_BASE_ATTRIBUTE92,
4408 TL_TEXT_BASE_ATTRIBUTE93,
4409 TL_TEXT_BASE_ATTRIBUTE94,
4410 TL_TEXT_BASE_ATTRIBUTE95,
4411 TL_TEXT_BASE_ATTRIBUTE96,
4412 TL_TEXT_BASE_ATTRIBUTE97,
4413 TL_TEXT_BASE_ATTRIBUTE98,
4414 TL_TEXT_BASE_ATTRIBUTE99,
4415 TL_TEXT_CAT_ATTRIBUTE1,
4416 TL_TEXT_CAT_ATTRIBUTE10,
4417 TL_TEXT_CAT_ATTRIBUTE11,
4418 TL_TEXT_CAT_ATTRIBUTE12,
4419 TL_TEXT_CAT_ATTRIBUTE13,
4420 TL_TEXT_CAT_ATTRIBUTE14,
4421 TL_TEXT_CAT_ATTRIBUTE15,
4422 TL_TEXT_CAT_ATTRIBUTE16,
4423 TL_TEXT_CAT_ATTRIBUTE17,
4424 TL_TEXT_CAT_ATTRIBUTE18,
4425 TL_TEXT_CAT_ATTRIBUTE19,
4426 TL_TEXT_CAT_ATTRIBUTE2,
4427 TL_TEXT_CAT_ATTRIBUTE20,
4428 TL_TEXT_CAT_ATTRIBUTE21,
4429 TL_TEXT_CAT_ATTRIBUTE22,
4430 TL_TEXT_CAT_ATTRIBUTE23,
4431 TL_TEXT_CAT_ATTRIBUTE24,
4432 TL_TEXT_CAT_ATTRIBUTE25,
4433 TL_TEXT_CAT_ATTRIBUTE26,
4434 TL_TEXT_CAT_ATTRIBUTE27,
4435 TL_TEXT_CAT_ATTRIBUTE28,
4436 TL_TEXT_CAT_ATTRIBUTE29,
4437 TL_TEXT_CAT_ATTRIBUTE3,
4438 TL_TEXT_CAT_ATTRIBUTE30,
4439 TL_TEXT_CAT_ATTRIBUTE31,
4440 TL_TEXT_CAT_ATTRIBUTE32,
4441 TL_TEXT_CAT_ATTRIBUTE33,
4442 TL_TEXT_CAT_ATTRIBUTE34,
4443 TL_TEXT_CAT_ATTRIBUTE35,
4444 TL_TEXT_CAT_ATTRIBUTE36,
4445 TL_TEXT_CAT_ATTRIBUTE37,
4446 TL_TEXT_CAT_ATTRIBUTE38,
4447 TL_TEXT_CAT_ATTRIBUTE39,
4448 TL_TEXT_CAT_ATTRIBUTE4,
4449 TL_TEXT_CAT_ATTRIBUTE40,
4450 TL_TEXT_CAT_ATTRIBUTE41,
4451 TL_TEXT_CAT_ATTRIBUTE42,
4452 TL_TEXT_CAT_ATTRIBUTE43,
4453 TL_TEXT_CAT_ATTRIBUTE44,
4454 TL_TEXT_CAT_ATTRIBUTE45,
4455 TL_TEXT_CAT_ATTRIBUTE46,
4456 TL_TEXT_CAT_ATTRIBUTE47,
4457 TL_TEXT_CAT_ATTRIBUTE48,
4458 TL_TEXT_CAT_ATTRIBUTE49,
4459 TL_TEXT_CAT_ATTRIBUTE5,
4460 TL_TEXT_CAT_ATTRIBUTE50,
4461 TL_TEXT_CAT_ATTRIBUTE6,
4462 TL_TEXT_CAT_ATTRIBUTE7,
4463 TL_TEXT_CAT_ATTRIBUTE8,
4464 TL_TEXT_CAT_ATTRIBUTE9,
4465 LAST_UPDATED_PROGRAM
4466 )
4467 SELECT PO_AUTOCREATE_PARAMS.g_draft_id,
4468 ALIAS,
4469 PO_ATTRIBUTE_VALUES_TLP_S.nextval,
4470 COMMENTS,
4471 CREATED_BY,
4472 CREATION_DATE,
4473 DESCRIPTION,
4474 INVENTORY_ITEM_ID,
4475 IP_CATEGORY_ID,
4476 LANGUAGE,
4477 LAST_UPDATED_BY,
4478 LAST_UPDATE_DATE,
4479 LAST_UPDATE_LOGIN,
4480 LONG_DESCRIPTION,
4481 MANUFACTURER,
4482 ORG_ID,
4483 PO_LINE_ID,
4484 PROGRAM_APPLICATION_ID,
4485 PROGRAM_ID,
4486 PROGRAM_UPDATE_DATE,
4487 REQUEST_ID,
4488 REQ_TEMPLATE_LINE_NUM,
4489 REQ_TEMPLATE_NAME,
4490 TL_TEXT_BASE_ATTRIBUTE1,
4491 TL_TEXT_BASE_ATTRIBUTE10,
4492 TL_TEXT_BASE_ATTRIBUTE100,
4493 TL_TEXT_BASE_ATTRIBUTE11,
4494 TL_TEXT_BASE_ATTRIBUTE12,
4495 TL_TEXT_BASE_ATTRIBUTE13,
4496 TL_TEXT_BASE_ATTRIBUTE14,
4497 TL_TEXT_BASE_ATTRIBUTE15,
4498 TL_TEXT_BASE_ATTRIBUTE16,
4499 TL_TEXT_BASE_ATTRIBUTE17,
4500 TL_TEXT_BASE_ATTRIBUTE18,
4501 TL_TEXT_BASE_ATTRIBUTE19,
4502 TL_TEXT_BASE_ATTRIBUTE2,
4503 TL_TEXT_BASE_ATTRIBUTE20,
4504 TL_TEXT_BASE_ATTRIBUTE21,
4505 TL_TEXT_BASE_ATTRIBUTE22,
4506 TL_TEXT_BASE_ATTRIBUTE23,
4507 TL_TEXT_BASE_ATTRIBUTE24,
4508 TL_TEXT_BASE_ATTRIBUTE25,
4509 TL_TEXT_BASE_ATTRIBUTE26,
4510 TL_TEXT_BASE_ATTRIBUTE27,
4511 TL_TEXT_BASE_ATTRIBUTE28,
4512 TL_TEXT_BASE_ATTRIBUTE29,
4513 TL_TEXT_BASE_ATTRIBUTE3,
4514 TL_TEXT_BASE_ATTRIBUTE30,
4515 TL_TEXT_BASE_ATTRIBUTE31,
4516 TL_TEXT_BASE_ATTRIBUTE32,
4517 TL_TEXT_BASE_ATTRIBUTE33,
4518 TL_TEXT_BASE_ATTRIBUTE34,
4519 TL_TEXT_BASE_ATTRIBUTE35,
4520 TL_TEXT_BASE_ATTRIBUTE36,
4521 TL_TEXT_BASE_ATTRIBUTE37,
4522 TL_TEXT_BASE_ATTRIBUTE38,
4523 TL_TEXT_BASE_ATTRIBUTE39,
4524 TL_TEXT_BASE_ATTRIBUTE4,
4525 TL_TEXT_BASE_ATTRIBUTE40,
4526 TL_TEXT_BASE_ATTRIBUTE41,
4527 TL_TEXT_BASE_ATTRIBUTE42,
4528 TL_TEXT_BASE_ATTRIBUTE43,
4529 TL_TEXT_BASE_ATTRIBUTE44,
4530 TL_TEXT_BASE_ATTRIBUTE45,
4531 TL_TEXT_BASE_ATTRIBUTE46,
4532 TL_TEXT_BASE_ATTRIBUTE47,
4533 TL_TEXT_BASE_ATTRIBUTE48,
4534 TL_TEXT_BASE_ATTRIBUTE49,
4535 TL_TEXT_BASE_ATTRIBUTE5,
4536 TL_TEXT_BASE_ATTRIBUTE50,
4537 TL_TEXT_BASE_ATTRIBUTE51,
4538 TL_TEXT_BASE_ATTRIBUTE52,
4539 TL_TEXT_BASE_ATTRIBUTE53,
4540 TL_TEXT_BASE_ATTRIBUTE54,
4541 TL_TEXT_BASE_ATTRIBUTE55,
4542 TL_TEXT_BASE_ATTRIBUTE56,
4543 TL_TEXT_BASE_ATTRIBUTE57,
4544 TL_TEXT_BASE_ATTRIBUTE58,
4545 TL_TEXT_BASE_ATTRIBUTE59,
4546 TL_TEXT_BASE_ATTRIBUTE6,
4547 TL_TEXT_BASE_ATTRIBUTE60,
4548 TL_TEXT_BASE_ATTRIBUTE61,
4549 TL_TEXT_BASE_ATTRIBUTE62,
4550 TL_TEXT_BASE_ATTRIBUTE63,
4551 TL_TEXT_BASE_ATTRIBUTE64,
4552 TL_TEXT_BASE_ATTRIBUTE65,
4553 TL_TEXT_BASE_ATTRIBUTE66,
4554 TL_TEXT_BASE_ATTRIBUTE67,
4555 TL_TEXT_BASE_ATTRIBUTE68,
4556 TL_TEXT_BASE_ATTRIBUTE69,
4557 TL_TEXT_BASE_ATTRIBUTE7,
4558 TL_TEXT_BASE_ATTRIBUTE70,
4559 TL_TEXT_BASE_ATTRIBUTE71,
4560 TL_TEXT_BASE_ATTRIBUTE72,
4561 TL_TEXT_BASE_ATTRIBUTE73,
4562 TL_TEXT_BASE_ATTRIBUTE74,
4563 TL_TEXT_BASE_ATTRIBUTE75,
4564 TL_TEXT_BASE_ATTRIBUTE76,
4565 TL_TEXT_BASE_ATTRIBUTE77,
4566 TL_TEXT_BASE_ATTRIBUTE78,
4567 TL_TEXT_BASE_ATTRIBUTE79,
4568 TL_TEXT_BASE_ATTRIBUTE8,
4569 TL_TEXT_BASE_ATTRIBUTE80,
4570 TL_TEXT_BASE_ATTRIBUTE81,
4571 TL_TEXT_BASE_ATTRIBUTE82,
4572 TL_TEXT_BASE_ATTRIBUTE83,
4573 TL_TEXT_BASE_ATTRIBUTE84,
4574 TL_TEXT_BASE_ATTRIBUTE85,
4575 TL_TEXT_BASE_ATTRIBUTE86,
4576 TL_TEXT_BASE_ATTRIBUTE87,
4577 TL_TEXT_BASE_ATTRIBUTE88,
4578 TL_TEXT_BASE_ATTRIBUTE89,
4579 TL_TEXT_BASE_ATTRIBUTE9,
4580 TL_TEXT_BASE_ATTRIBUTE90,
4581 TL_TEXT_BASE_ATTRIBUTE91,
4582 TL_TEXT_BASE_ATTRIBUTE92,
4583 TL_TEXT_BASE_ATTRIBUTE93,
4584 TL_TEXT_BASE_ATTRIBUTE94,
4585 TL_TEXT_BASE_ATTRIBUTE95,
4586 TL_TEXT_BASE_ATTRIBUTE96,
4587 TL_TEXT_BASE_ATTRIBUTE97,
4588 TL_TEXT_BASE_ATTRIBUTE98,
4589 TL_TEXT_BASE_ATTRIBUTE99,
4590 TL_TEXT_CAT_ATTRIBUTE1,
4591 TL_TEXT_CAT_ATTRIBUTE10,
4592 TL_TEXT_CAT_ATTRIBUTE11,
4593 TL_TEXT_CAT_ATTRIBUTE12,
4594 TL_TEXT_CAT_ATTRIBUTE13,
4595 TL_TEXT_CAT_ATTRIBUTE14,
4596 TL_TEXT_CAT_ATTRIBUTE15,
4597 TL_TEXT_CAT_ATTRIBUTE16,
4598 TL_TEXT_CAT_ATTRIBUTE17,
4599 TL_TEXT_CAT_ATTRIBUTE18,
4600 TL_TEXT_CAT_ATTRIBUTE19,
4601 TL_TEXT_CAT_ATTRIBUTE2,
4602 TL_TEXT_CAT_ATTRIBUTE20,
4603 TL_TEXT_CAT_ATTRIBUTE21,
4604 TL_TEXT_CAT_ATTRIBUTE22,
4605 TL_TEXT_CAT_ATTRIBUTE23,
4606 TL_TEXT_CAT_ATTRIBUTE24,
4607 TL_TEXT_CAT_ATTRIBUTE25,
4608 TL_TEXT_CAT_ATTRIBUTE26,
4609 TL_TEXT_CAT_ATTRIBUTE27,
4610 TL_TEXT_CAT_ATTRIBUTE28,
4611 TL_TEXT_CAT_ATTRIBUTE29,
4612 TL_TEXT_CAT_ATTRIBUTE3,
4613 TL_TEXT_CAT_ATTRIBUTE30,
4614 TL_TEXT_CAT_ATTRIBUTE31,
4615 TL_TEXT_CAT_ATTRIBUTE32,
4616 TL_TEXT_CAT_ATTRIBUTE33,
4617 TL_TEXT_CAT_ATTRIBUTE34,
4618 TL_TEXT_CAT_ATTRIBUTE35,
4619 TL_TEXT_CAT_ATTRIBUTE36,
4620 TL_TEXT_CAT_ATTRIBUTE37,
4621 TL_TEXT_CAT_ATTRIBUTE38,
4622 TL_TEXT_CAT_ATTRIBUTE39,
4623 TL_TEXT_CAT_ATTRIBUTE4,
4624 TL_TEXT_CAT_ATTRIBUTE40,
4625 TL_TEXT_CAT_ATTRIBUTE41,
4626 TL_TEXT_CAT_ATTRIBUTE42,
4627 TL_TEXT_CAT_ATTRIBUTE43,
4628 TL_TEXT_CAT_ATTRIBUTE44,
4629 TL_TEXT_CAT_ATTRIBUTE45,
4630 TL_TEXT_CAT_ATTRIBUTE46,
4631 TL_TEXT_CAT_ATTRIBUTE47,
4632 TL_TEXT_CAT_ATTRIBUTE48,
4633 TL_TEXT_CAT_ATTRIBUTE49,
4634 TL_TEXT_CAT_ATTRIBUTE5,
4635 TL_TEXT_CAT_ATTRIBUTE50,
4636 TL_TEXT_CAT_ATTRIBUTE6,
4637 TL_TEXT_CAT_ATTRIBUTE7,
4638 TL_TEXT_CAT_ATTRIBUTE8,
4639 TL_TEXT_CAT_ATTRIBUTE9,
4640 'AUTOCREATE_BACKEND_FOR_SOURCING'
4641 FROM PO_ATTR_VALUES_TLP_INTERFACE
4642 WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
4643
4644 --<Unified Catalog R12 END>
4645 IF g_debug_stmt THEN
4646 PO_DEBUG.debug_stmt(p_log_head=>g_log_head||l_api_name ,p_token=>l_progress ,p_message=>'Number of rows inserted in PO_ATTRIBUTE_VALUES_TLP table='||SQL%rowcount );
4647 END IF;
4648
4649 IF g_debug_stmt THEN
4650 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
4651 END IF;
4652 END IF;
4653
4654 EXCEPTION
4655 WHEN OTHERS THEN
4656 IF g_debug_unexp THEN
4657 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
4658 END IF;
4659
4660 --CLM Phase 2 changes : error handling
4661 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_MERGE_ATTR_ERR',x_token1_value => sqlerrm);
4662
4663 po_autocreate_pvt.wrapup;
4664 PO_MESSAGE_S.sql_error ( 'PO_AUTO_LINE_PROCESS_PVT.merge_to_attr_values_draft', l_progress , SQLCODE );
4665 RAISE;
4666 END merge_to_attr_values_draft;
4667
4668 -------------------------------------------------------------------------------
4669 --Start of Comments
4670 --Name: do_currency_conversion
4671 --Pre-reqs:
4672 -- None.
4673 --Modifies:
4674 -- None.
4675 --Locks:
4676 -- None.
4677 --Function:
4678 -- This procedure performs currency conversion on the input quantity,
4679 -- unit_price, or amount. Which of the previous values to convert depends
4680 -- on the order_type_lookup_code and the interface_source_code.
4681 --
4682 -- If the Req line currency is the same as the new PO currency, we will take
4683 -- the Req's currency_<unit_price/amount> directly so that conversion
4684 -- calculations will not have to be performed again. Otherwise, we will
4685 -- perform the conversion using the input rate.
4686 --
4687 --Parameters:
4688 --IN:
4689 --p_order_type_lookup_code
4690 -- Value Basis of the Requisition/PO line.
4691 --p_interface_source_code
4692 -- Interface Source Code of the current Autocreate session.
4693 --p_rate
4694 -- Currency conversion rate to convert Req Currency to PO Currency.
4695 --p_po_currency_code
4696 -- Currency code of the to-be-created PO.
4697 --p_requisition_line_id
4698 -- Unique ID of the Requisition line being Autocreated.
4699 -- (May be NULL if coming from Sourcing).
4700 --IN OUT:
4701 --x_quantity
4702 -- Quantity to be converted.
4703 --x_unit_price
4704 -- Unit Price to be converted.
4705 --x_base_unit_price
4706 -- Base Unit Price to be converted.
4707 --x_amount
4708 -- Amount to be converted.
4709 --Testing:
4710 -- None.
4711 --End of Comments
4712 -------------------------------------------------------------------------------
4713 PROCEDURE do_currency_conversion
4714 (
4715 p_order_type_lookup_code IN VARCHAR2 ,
4716 p_interface_source_code IN VARCHAR2 ,
4717 p_rate IN NUMBER ,
4718 p_po_currency_code IN VARCHAR2 ,
4719 p_requisition_line_id IN NUMBER ,
4720 x_quantity IN OUT NOCOPY NUMBER ,
4721 x_unit_price IN OUT NOCOPY NUMBER ,
4722 x_base_unit_price IN OUT NOCOPY NUMBER ,
4723 x_amount IN OUT NOCOPY NUMBER )
4724 IS
4725
4726 l_api_name VARCHAR2(30) := 'do_currency_conversion';
4727 l_log_head VARCHAR2(100) := g_log_head || l_api_name;
4728 l_progress VARCHAR2(3);
4729 l_precision FND_CURRENCIES.precision%TYPE;
4730 l_ext_precision FND_CURRENCIES.extended_precision%TYPE;
4731 l_min_acct_unit FND_CURRENCIES.minimum_accountable_unit%TYPE;
4732 l_req_currency_code PO_REQUISITION_LINES_ALL.currency_code%TYPE;
4733 l_req_ou_currency_code GL_SETS_OF_BOOKS.currency_code%TYPE;
4734 l_req_unit_price PO_REQUISITION_LINES_ALL.unit_price%TYPE;
4735 l_req_currency_unit_price PO_REQUISITION_LINES_ALL.currency_unit_price%TYPE;
4736 l_req_amount PO_REQUISITION_LINES_ALL.amount%TYPE;
4737 l_req_currency_amount PO_REQUISITION_LINES_ALL.currency_amount%TYPE;
4738 l_req_rate PO_REQUISITION_LINES_ALL.rate%TYPE;
4739 l_retrun_status VARCHAR2(10);
4740
4741 BEGIN
4742
4743 l_progress:='000';
4744 PO_DEBUG.debug_begin(l_log_head);
4745
4746 -- Initialize Variables ===================================================
4747 -- Get the precision/extended precision for the PO Currency.
4748
4749 IF p_po_currency_code IS NOT NULL THEN
4750 l_progress:='010';
4751 FND_CURRENCY.get_info ( currency_code => p_po_currency_code ,
4752 PRECISION => l_precision ,
4753 ext_precision => l_ext_precision ,
4754 min_acct_unit => l_min_acct_unit );
4755 END IF;
4756
4757 -- Convert ================================================================
4758 l_progress:='020';
4759
4760 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_type_lookup_code',p_order_type_lookup_code);
4761
4762 -- For 'Amount' based lines, we need to convert the quantity since
4763 -- quantity acts like amount.
4764 --
4765 IF ( p_order_type_lookup_code = 'AMOUNT' ) THEN
4766 l_progress :='020';
4767 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Performing currency conversion on quantity.');
4768 -- No conversion for same currency.
4769 /* Added Exception Block to make sure that Solicitations, which are not backed up by requisitions
4770 shouldn't throw no data found exceptions. */
4771 BEGIN
4772
4773 SELECT PRL.currency_code,
4774 NVL(PRL.rate,1)
4775 INTO l_req_currency_code,
4776 l_req_rate
4777 FROM po_requisition_lines_all PRL
4778 WHERE PRL.requisition_line_id = p_requisition_line_id;
4779
4780 IF ( l_req_currency_code = p_po_currency_code ) THEN
4781 l_progress :='030';
4782 x_quantity := ROUND ( x_quantity/l_req_rate, NVL(l_ext_precision, 15) );
4783 ELSE
4784 l_progress :='040';
4785 x_quantity := ROUND ( x_quantity/p_rate, NVL(l_ext_precision, 15) );
4786 END IF;
4787
4788 EXCEPTION
4789 WHEN OTHERS THEN
4790 PO_DEBUG.debug_exc ( p_log_head => l_log_head , p_progress => l_progress);
4791 END;
4792
4793 -- For all other line types, convert the Price/Amount.
4794 --
4795 ELSE -- ( p_order_type_lookup_code IN ('QUANTITY','FIXED PRICE','RATE') )
4796 -- If coming from Sourcing, however, do not perform any conversion as
4797 -- Sourcing already populates converted value in the interface table.
4798 --
4799 IF ( p_interface_source_code IN ('SOURCING','CONSUMPTION_ADVICE') ) THEN
4800
4801 l_progress :='050';
4802 PO_DEBUG.debug_stmt(l_log_head,l_progress,'p_interface_source_code IN (SOURCING, CONSUMPTION ADVICE) - no currency conversion performed');
4803
4804 ELSE -- ( p_interface_source_code NOT IN ('SOURCING','CONSUMPTION_ADVICE') )
4805 -- Retrieve information from the backing Requisition Line.
4806 -- Join to financials_system_params_all and gl_sets_of_books to
4807 -- retrieve the value of l_req_ou_currency_code, the functional currency of ROU
4808 l_progress :='060';
4809 SELECT PRL.currency_code ,
4810 GSB.currency_code ,
4811 PRL.unit_price ,
4812 NVL(PRL.currency_unit_price, PRL.unit_price) ,
4813 PRL.amount ,
4814 NVL(PRL.currency_amount, PRL.amount)
4815 INTO l_req_currency_code ,
4816 l_req_ou_currency_code ,
4817 l_req_unit_price ,
4818 l_req_currency_unit_price ,
4819 l_req_amount ,
4820 l_req_currency_amount
4821 FROM po_requisition_lines_all PRL,
4822 financials_system_params_all FSP,
4823 gl_sets_of_books GSB
4824 WHERE PRL.requisition_line_id = p_requisition_line_id
4825 AND NVL(PRL.org_id, -99) = NVL(FSP.org_id, -99)
4826 AND FSP.set_of_books_id = GSB.set_of_books_id;
4827 -- If the Req and PO Currency are the same, then simply take the
4828 -- currency_<unit_price/amount> from the Req to avoid having to
4829 -- perform another conversion.
4830 --
4831 -- If the ROU currency and PO Currency are the same, then
4832 -- simply take the unit_price/amount from the Req to avoid conversion
4833 --
4834 -- If the Req and PO Currency are different, then convert the
4835 -- unit_price/amount to the PO Currency using the specified rate.
4836 --
4837 IF ( l_req_currency_code = p_po_currency_code ) THEN
4838 l_progress :='070';
4839 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Req and PO Currency equivalent ('
4840 || p_po_currency_code
4841 || ') - taking currency_unit_price/amount directly from the Req Line.');
4842
4843 x_unit_price := l_req_currency_unit_price;
4844 x_amount := l_req_currency_amount;
4845 ELSIF (l_req_ou_currency_code = p_po_currency_code) THEN
4846 l_progress := '080';
4847 PO_DEBUG.debug_stmt(l_log_head,l_progress,'ROU Currency and PO Currency equivalent ('
4848 || p_po_currency_code
4849 || ') - taking unit_price/amount directory from the Req line');
4850
4851 x_unit_price := l_req_unit_price;
4852 x_amount := l_req_amount;
4853 ELSE
4854 l_progress:='090';
4855 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Req ('
4856 || l_req_currency_code
4857 || ')/ ROU ('
4858 || l_req_ou_currency_code
4859 || ') and PO ('
4860 || p_po_currency_code
4861 || ') Currency different - performing currency conversion and rounding.');
4862
4863 x_unit_price := ROUND(l_req_unit_price/p_rate, NVL(l_ext_precision, 15));
4864 x_amount := ROUND(l_req_amount /p_rate, l_precision);
4865 END IF; -- currency_code
4866
4867 x_base_unit_price := ROUND(x_base_unit_price/p_rate, NVL(l_ext_precision, 15));
4868
4869 END IF; -- p_interface_source_code
4870
4871 END IF; -- p_order_type_lookup_code
4872
4873 l_progress:='100';
4874 PO_DEBUG.debug_end(l_log_head);
4875 EXCEPTION
4876 WHEN OTHERS THEN
4877 PO_DEBUG.debug_exc ( p_log_head => l_log_head , p_progress => l_progress);
4878 RAISE;
4879 --CLM Phase 2 changes : error handling
4880 PO_AUTOCREATE_PVT.report_error('PO_AUTO_CURRENCY_CONV_ERR',x_token1_value => sqlerrm);
4881
4882 END do_currency_conversion;
4883
4884
4885 -------------------------------------------------------------------------------
4886 --Start of Comments
4887 --Name: create_pon_back_req_dist;
4888
4889 --Function:
4890 -- This procedure create distributions using the proration logic for Line having Linked PR ref.
4891 -------------------------------------------------------------------------------
4892
4893 PROCEDURE create_pon_back_req_dist
4894
4895 IS
4896
4897 l_api_name CONSTANT VARCHAR2(30) := 'create_pon_back_req_dist';
4898 l_log_head VARCHAR2(100) := g_log_head || l_api_name;
4899 l_progress VARCHAR2(3);
4900
4901 l_total_dist_amount NUMBER;
4902 l_total_req_line_amount NUMBER;
4903 l_auction_header_id NUMBER;
4904 l_dist_interface_id NUMBER;
4905
4906 l_count NUMBER;
4907 l_line_type VARCHAR2(100);
4908 l_unit_price NUMBER;
4909
4910 BEGIN
4911
4912 l_progress:='000';
4913 PO_DEBUG.debug_begin(l_log_head);
4914
4915 /*pon_award allocations allocated_funds amount will have total funds_remaining from req lines for that bid line.
4916 So each distribution will have this funded amount divided in the ratio of distribution
4917 i.e if the req line has 2 dist with funds_remaining as d1 and d2 and allocated_funds is f1 then funded value for this distribution
4918 is f1*(d1/(d1+d2))
4919 Also
4920 If total funds available to use(TFU) from req_lines in greater than total funds required(TFR) by the bid line
4921 then we should use TFR *(d1/d1+d2)
4922 else
4923 TFU*(di/d1+d2)*/
4924
4925 BEGIN
4926 SELECT PLI.auction_header_id INTO l_auction_header_id
4927 FROM po_lines_interface pli,pon_award_allocations paa
4928 WHERE PLI.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
4929 AND pli.auction_header_id = paa.auction_header_id
4930 AND paa.is_linked_pr_line_yn = 'Y'
4931 AND ROWNUM <2;
4932 EXCEPTION
4933 WHEN No_Data_Found THEN
4934 l_auction_header_id := NULL;
4935 END;
4936
4937 l_progress :='020';
4938 PO_DEBUG.debug_stmt(l_log_head,l_progress,'l_auction_header_id: '||l_auction_header_id);
4939
4940 --Line Stucture Enabled does not check for funtion security here.
4941 IF(l_auction_header_id IS NOT NULL AND PON_CLM_UTIL_PKG.get_line_structure_enabled(p_auction_header_id => l_auction_header_id) = 'Y') THEN
4942
4943 INSERT INTO po_distributions_interface
4944 (interface_header_id,
4945 interface_line_id,
4946 interface_distribution_id,
4947 distribution_num,
4948 charge_account_id,
4949 set_of_books_id,
4950 rate,
4951 rate_date,
4952 req_distribution_id,
4953 deliver_to_location_id,
4954 deliver_to_person_id,
4955 encumbered_flag,
4956 destination_type_code,
4957 destination_organization_id,
4958 destination_subinventory,
4959 budget_account_id,
4960 accrual_account_id,
4961 variance_account_id,
4962 wip_entity_id,
4963 wip_line_id,
4964 wip_repetitive_schedule_id,
4965 wip_operation_seq_num,
4966 wip_resource_seq_num,
4967 bom_resource_id,
4968 prevent_encumbrance_flag,
4969 project_id,
4970 task_id,
4971 end_item_unit_number,
4972 expenditure_type,
4973 project_accounting_context,
4974 destination_context,
4975 expenditure_organization_id,
4976 expenditure_item_date,
4977 tax_recovery_override_flag,
4978 recovery_rate,
4979 recoverable_tax,
4980 nonrecoverable_tax,
4981 award_id,
4982 oke_contract_line_id,
4983 oke_contract_deliverable_id,
4984 group_line_id,
4985 funded_value,
4986 quantity_ordered,
4987 amount_ordered,
4988 quantity_funded,
4989 amount_funded
4990 )
4991 SELECT phi.interface_header_id,
4992 PLI.interface_line_id,
4993 po_distributions_interface_s.NEXTVAL,
4994 ROWNUM ,
4995 prd.code_combination_id,
4996 prd.set_of_books_id,
4997 phi.rate,
4998 phi.rate_date,
4999 prd.distribution_id,
5000 prd.deliver_to_location_id,
5001 prl.to_person_id,
5002 'N',
5003 prd.destination_type_code,
5004 prd.destination_organization_id,
5005 prd.destination_subinventory,
5006 prd.budget_account_id,
5007 prd.accrual_account_id,
5008 prd.variance_account_id,
5009 prd.wip_entity_id,
5010 prd.wip_line_id,
5011 prd.wip_repetitive_schedule_id,
5012 prd.wip_operation_seq_num,
5013 prd.wip_resource_seq_num,
5014 prd.bom_resource_id,
5015 prd.prevent_encumbrance_flag,
5016 prd.project_id,
5017 prd.task_id,
5018 prd.end_item_unit_number,
5019 prd.expenditure_type,
5020 prd.project_accounting_context,
5021 prd.destination_context,
5022 prd.expenditure_organization_id,
5023 prd.expenditure_item_date,
5024 prd.tax_recovery_override_flag,
5025 prd.recovery_rate,
5026 prd.recoverable_tax,
5027 prd.nonrecoverable_tax,
5028 prd.award_id,
5029 decode(pli.consigned_flag,'Y',null, prd.oke_contract_line_id),
5030 decode(pli.consigned_flag,'Y',null, prd.oke_contract_deliverable_id),
5031 prd.info_line_id,
5032 paa.allocated_funds * (prd.funds_remaining/ req_dist_amt.tot_funds_remaining),
5033 Decode(plb.matching_basis, 'QUANTITY',(paa.allocated_funds * (prd.funds_remaining/ req_dist_amt.tot_funds_remaining))/ pli.unit_price,
5034 null), --quantity_ordered,
5035 Decode(plb.matching_basis, 'AMOUNT',(paa.allocated_funds * (prd.funds_remaining/ req_dist_amt.tot_funds_remaining)),
5036 NULL ), --amount_ordered,
5037
5038 Decode(plb.matching_basis, 'QUANTITY',(paa.allocated_funds * (prd.funds_remaining/ req_dist_amt.tot_funds_remaining))/ pli.unit_price,
5039 null),--quantity_funded,
5040 Decode(plb.matching_basis, 'AMOUNT',(paa.allocated_funds * (prd.funds_remaining/ req_dist_amt.tot_funds_remaining)),
5041 NULL ) --amount_funded
5042 FROM po_lines_interface pli,
5043 po_headers_interface phi,
5044 pon_award_allocations paa,
5045 po_clmreq_dist_details_v prd,
5046 (SELECT requisition_line_id, Sum(prd.funds_remaining) AS tot_funds_remaining
5047 FROM po_clmreq_dist_details_v prd
5048 GROUP BY prd.requisition_line_id) req_dist_amt,
5049 po_requisition_lines_all prl,
5050 po_line_types_b plb
5051 WHERE phi.interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id
5052 AND pli.interface_header_id = phi.interface_header_id
5053 AND Nvl(pli.orig_from_req_flag, 'N') = 'S'
5054 AND pli.bid_number = paa.bid_number
5055 AND pli.bid_line_number = paa.bid_line_number
5056 AND paa.orig_req_line_id = prd.requisition_line_id
5057 AND prl.requisition_line_id = paa.orig_req_line_id
5058 AND Nvl(paa.is_linked_pr_line_yn, 'N') = 'Y'
5059 AND pli.line_type_id = plb.line_type_id
5060 AND req_dist_amt.requisition_line_id = paa.orig_req_line_id
5061 AND prd.funds_remaining > 0;
5062
5063 l_count := SQL%ROWCOUNT;
5064 PO_DEBUG.debug_stmt(l_log_head,l_progress,'lines inserted into distributions interface'||l_count);
5065
5066 END IF;
5067 l_progress:='50';
5068 PO_DEBUG.debug_end(l_log_head);
5069
5070 EXCEPTION
5071 WHEN OTHERS THEN
5072 PO_DEBUG.debug_exc ( p_log_head => l_log_head , p_progress => l_progress);
5073 RAISE;
5074 --CLM Phase 2 changes : error handling
5075 PO_AUTOCREATE_PVT.report_error('create_pon_back_req_dist',x_token1_value => sqlerrm);
5076
5077 END create_pon_back_req_dist;
5078 END PO_AUTO_LINE_PROCESS_PVT;