[Home] [Help]
PACKAGE BODY: APPS.PO_AUTO_LINE_LOC_PROCESS_PVT
Source
1 PACKAGE body po_auto_line_loc_process_pvt AS
2 /* $Header: PO_AUTO_LINE_LOC_PROCESS_PVT.plb 120.17.12020000.3 2013/02/10 17:14:46 vegajula ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(1000) := 'PO_AUTO_LINE_LOC_PROCESS_PVT';
5 g_log_head CONSTANT VARCHAR2(1000) := 'po.plsql.PO_AUTO_LINE_LOC_PROCESS_PVT.';
6 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
7 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
8
9 /* ============================================================================
10 **
11 ** NAME
12 ** PO_AUTO_LINE_LOC_PROCESS_PVT.plb
13 **
14 ** DESCRIPTION
15 ** This package contains logic for AutoCreate Line Location processing stage
16 ** This API calls the subroutines to handle the derivation, defaulting,
17 ** validation and insert/update of the Shipments.
18 **
19 **
20 ** The procedures of this package are called from PO_AUTOCREATE_MAINPROC_PVT.process_line_locations
21 **
22 ** HISTORY
23 ** 10/11/09 bisdas Created
24 ==============================================================================*/
25
26 /* ============================================================================
27 Name: create_shipment_draft
28 Pre-reqs:
29 PO Line has been created
30 Modifies:
31 PO_LINE_LOCATIONS_DRAFT_ALL
32 Locks:
33 None
34 Function:
35 Derives,deaults the shipment info from available lines information and
36 inserts/updates the PO_LINE_LOCATIONS_DRAFT_ALL.
37 Parameters:
38 p_lines IN OUT Derived Line data after lines processing
39 Returns:
40 None
41 Testing:
42 None
43 Caller of the Procedure:
44 PO_AUTOCREATE_MAINPROC_PVT.process_line_locations
45 ==============================================================================*/
46
47 PROCEDURE create_shipment_draft(p_lines IN OUT NOCOPY PO_AUTOCREATE_TYPES.lines_rec_type)
48 IS
49
50 p_line_locs po_autocreate_types.line_locs_rec_type;
51 l_outsourced_assembly NUMBER;
52
53 idx NUMBER :=1;
54
55 x_ship_to_location_id number:= 0;
56 x_price number; /* used to get release price from PA */
57 x_price_break_type varchar2(25) := '';
58 x_doctype varchar2(25) := ''; /* used for call to update close state */
59 x_return_code varchar2(25) := ''; /* used for call to update close state */
60 x_item_org_taxable_flag mtl_system_items.taxable_flag%type := NULL;
61 x_ship_to_org_taxable_flag mtl_system_items.taxable_flag%type := NULL;
62 x_return_taxable_flag mtl_system_items.taxable_flag%type := NULL;
63
64 /* For converting qty if line_type is not quantity based */
65 x_order_type_lookup_code varchar2(25) :='';
66 x_quantity number :=0;
67 l_conversion_rate number :=1;
68
69 /* obtain currency info to adjust precision */
70 x_precision number :='';
71 x_ext_precision number :='';
72 x_min_unit number :='';
73
74 /* Additional tax variables for R11 tax defaulting functionality */
75 x_tax_code_id ap_tax_codes.tax_id%type;
76 x_tax_type ap_tax_codes.tax_type%type;
77 x_description ap_tax_codes.description%type;
78 x_allow_tax_code_override_flag gl_tax_option_accounts.allow_tax_code_override_flag%type;
79
80 /* Parameters for supporting OE callback for maintaining so_drop_ship_source */
81 x_p_api_version number:='';
82 x_p_return_status varchar2(1):='';
83 x_p_msg_count number:='';
84 x_p_msg_data varchar2(2000):='';
85 x_p_req_header_id NUMBER:='';
86 x_p_req_line_id NUMBER:='';
87 --x_p_interface_source_code varchar2(25);
88 --x_p_interface_source_line_id number:='';
89 x_p_po_header_id number:='';
90 x_p_po_line_id number:='';
91 x_p_line_location_id number:='';
92 x_requisition_header_id number:='';
93 x_p_po_release_id number:='';
94
95 /* Variable to get the qty in the already existing shipment */
96 x_ship_qty number := 0;
97 x_tax_user_override_flag VARCHAR2(1);
98
99 x_country_of_origin_code VARCHAR2(2);
100 x_tax_status VARCHAR2(10);
101 x_tax_status_indicator po_requisition_lines.tax_status_indicator%type;
102
103 l_encode VARCHAR2(2000);
104 x_po_uom varchar2(25):=null;
105 x_temp_uom varchar2(25):=null;
106 x_temp_item_id number:=null;
107 x_closed_reason po_line_locations.closed_reason%TYPE;
108 x_uom_convert varchar2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
109
110
111 l_promised_date DATE;
112 l_po_promised_def_prf VARCHAR2(1) := fnd_profile.value('PO_NEED_BY_PROMISE_DEFAULTING');
113
114
115 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SHIPMENT_DRAFT';
116 l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
117 l_manual_price_change_flag po_line_locations_all.manual_price_change_flag%TYPE := NULL; --bug 3495772
118
119
120 l_from_type_lookup_code po_headers_all.type_lookup_code%TYPE;
121
122 --<INVCONV R12 START>
123 x_shipment_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
124 x_secondary_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
125 x_secondary_quantity PO_LINES.SECONDARY_QUANTITY%TYPE;
126 x_secondary_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
127 --<INVCONV R12 END>
128
129 l_matching_basis PO_LINE_TYPES.matching_basis%TYPE; -- <Complex Work R12>
130 x_line_location_id NUMBER;
131
132 l_cc_unit_price number;
133 l_cc_base_unit_price number;
134 l_cc_amount number;
135
136 --autocreate grouping start
137 x_results PO_VALIDATION_RESULTS_TYPE;
138 l_lock_exception EXCEPTION;
139 l_entity_name_tbl PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
140 l_pk1_tbl PO_TBL_VARCHAR240 := PO_TBL_VARCHAR240();
141 l_pk2_tbl PO_TBL_VARCHAR240 := PO_TBL_VARCHAR240();
142 l_pk3_tbl PO_TBL_VARCHAR240 := PO_TBL_VARCHAR240();
143 l_pk4_tbl PO_TBL_VARCHAR240 := PO_TBL_VARCHAR240();
144 l_return_status VARCHAR2(1);
145 --autocreate grouping end
146
147 BEGIN
148
149 IF g_debug_stmt THEN
150 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
151 END IF;
152
153 l_progress :='001';
154
155 IF g_debug_stmt THEN
156 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
157 p_message => 'Num of lines:'||p_lines.intf_line_id_tbl.Count);
158 END IF;
159
160 FOR i IN 1.. p_lines.intf_line_id_tbl.Count
161 LOOP
162
163 l_outsourced_assembly := 2;
164 x_precision := NULL;
165 x_ext_precision := NULL;
166 x_min_unit := NULL;
167 l_from_type_lookup_code := NULL;
168 x_po_uom := NULL;
169 x_quantity := NULL;
170 x_shipment_uom := NULL;
171 l_conversion_rate := NULL;
172 x_line_location_id := NULL;
173 x_price := NULL;
174 x_return_code := NULL;
175 x_doctype := NULL;
176 x_secondary_quantity := NULL;
177 x_secondary_unit_of_measure := NULL;
178 x_secondary_uom_code := NULL;
179 x_item_org_taxable_flag := NULL;
180 x_return_taxable_flag := NULL;
181 x_country_of_origin_code := NULL;
182 x_closed_reason := NULL;
183 l_promised_date := NULL;
184
185 IF NVL(p_lines.clm_info_flag_tbl(i),'N') = 'N' AND p_lines.shipment_num_tbl(i) IS NOT NULL THEN
186
187 IF g_debug_stmt THEN
188 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Inside CLM shipment processing');
189 END IF;
190
191 IF p_lines.item_id_tbl(i) IS NOT NULL THEN
192
193 l_progress :='005';
194 l_outsourced_assembly := po_core_s.get_outsourced_assembly(p_lines.item_id_tbl(i), p_lines.dest_organization_id_tbl(i));
195
196 END IF;
197
198 x_line_location_id:=NULL;
199 x_quantity := p_lines.quantity_tbl(i);
200 x_temp_uom := p_lines.unit_of_measure_tbl(i);
201 x_temp_item_id := p_lines.item_id_tbl(i);
202
203 l_progress :='010';
204 SELECT plt.order_type_lookup_code,
205 plt.matching_basis
206 INTO x_order_type_lookup_code,
207 l_matching_basis
208 FROM po_line_types plt
209 WHERE plt.line_type_id = p_lines.line_type_id_tbl(i);
210
211 l_progress :='020';
212 IF p_lines.hd_currency_code_tbl(i) IS NOT NULL THEN
213 fnd_currency.get_info(p_lines.hd_currency_code_tbl(i), x_precision, x_ext_precision, x_min_unit );
214 END IF;
215
216 -- Conversion of req UOM to Quotation UOM should always happen if the
217 -- source document is a quote and profile 'PO: Convert Requisition UOM to Source Document UOM'
218 -- should be ignored in that case
219
220 IF g_debug_stmt THEN
221 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
222 p_message => 'from line id :'||p_lines.from_line_id_tbl(i)||'from header id:'||p_lines.from_header_id_tbl(i));
223 END IF;
224
225 l_progress :='030';
226 IF (p_lines.from_line_id_tbl(i) IS NOT NULL) AND (po_autocreate_params.g_document_subtype = 'STANDARD') THEN
227 l_progress := '040';
228
229 BEGIN
230 SELECT poh.type_lookup_code
231 INTO l_from_type_lookup_code
232 FROM po_headers_all poh
233 WHERE poh.po_header_id=p_lines.from_header_id_tbl(i) ;
234
235 IF g_debug_stmt THEN
236 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'l_from_type_lookup_code :'||l_from_type_lookup_code);
237 END IF;
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 IF g_debug_unexp THEN
242 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
243 END IF;
244
245 --CLM Phase 2 changes : error handling
246 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_LOC_LOOKUP_ERR',
247 x_token1_value => sqlerrm,
248 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
249
250 raise;
251 END;
252 END IF;
253
254 -- got the source document type, now compare it and if required do the UOM conversion
255 IF g_debug_stmt THEN
256 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'x_uom_convert:'||x_uom_convert);
257 END IF;
258
259 IF (NVL(x_uom_convert,'N') = 'Y') THEN
260 -- Convert UOM when autocreating a PO that references a GA
261 IF ((po_autocreate_params.g_document_subtype='STANDARD') AND (p_lines.from_line_id_tbl(i) IS NOT NULL)) THEN
262 /* Get the uom from the PO . This will be used for uom conversion */
263 BEGIN
264 --Autocreating a PO that references a GA
265 l_progress := '050';
266 SELECT unit_meas_lookup_code
267 INTO x_po_uom
268 FROM po_lines_draft_all
269 WHERE po_line_id = p_lines.from_line_id_tbl(i)
270 AND draft_id =po_autocreate_params.g_draft_id;
271 EXCEPTION
272 WHEN OTHERS THEN
273 IF g_debug_unexp THEN
274 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
275 END IF;
276 po_message_s.sql_error('CREATE_SHIPMENTS',l_progress,SQLCODE);
277
278 --CLM Phase 2 changes : error handling
279 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_LOC_UOM_ERR',
280 x_token1_value => sqlerrm,
281 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
282
283 PO_AUTOCREATE_PVT.wrapup();
284 raise;
285 END;
286
287 /* before inserting the quantity into the shipments table convert the quantity
288 into the BPA uom if the uom's on the req and BPA are different .
289 This conversion is done only if the Convert UOM profile option is set to Yes. */
290 IF g_debug_stmt THEN
291 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Create_shipment: UOM: '||x_temp_uom);
292 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Create_shipment: item id: '||x_temp_item_id);
293 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Create_shipment: PO UOM: '||x_po_uom);
294 END IF;
295
296 l_progress := '060';
297 IF ( ( p_lines.unit_of_measure_tbl(i) <> x_po_uom ) AND ( x_order_type_lookup_code IN ('QUANTITY','AMOUNT') ) ) THEN -- <SERVICES FPJ>
298 -- use the po_uom_convert procedure and round 15
299 l_conversion_rate := po_uom_s.po_uom_convert(p_lines.unit_of_measure_tbl(i), x_po_uom, p_lines.item_id_tbl(i));
300 x_quantity := ROUND(x_quantity * l_conversion_rate , 15);
301 x_shipment_uom := x_po_uom ; --<INVCONV R12>
302
303 IF g_debug_stmt THEN
304 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
305 p_message => 'Create_shipment: Converted Qty: '||x_quantity);
306 END IF;
307 END IF;
308 END IF;
309 END IF;
310
311 /*
312 ** Get the ship to location id associated with the
313 ** deliver to location. This may then used to
314 ** get the tax name, if the tax system parameters are
315 ** set up to retrieve the tax code based on ship-to location.
316 */
317
318 x_ship_to_location_id := po_autocreate_mainproc_pvt.get_ship_to_loc(p_lines.deliver_to_loc_id_tbl(i)); -- FPI
319
320 IF(po_autocreate_params.g_document_subtype='STANDARD' OR po_autocreate_params.g_document_subtype='PLANNED' )THEN
321 l_progress := '070';
322
323 BEGIN
324 SELECT poll.line_location_id,
325 poll.secondary_unit_of_measure --<INVCONV R12>
326 INTO x_line_location_id,
327 x_secondary_unit_of_measure
328 FROM po_line_locations_draft_all poll, --<Shared Proc FPJ>
329 po_lines_draft_all pol --<Shared Proc FPJ>
330 WHERE poll.po_header_id = p_lines.po_header_id_tbl(i)
331 AND pol.draft_id =po_autocreate_params.g_draft_id
332 AND poll.po_line_id = p_lines.po_line_id_tbl(i)
333 AND poll.shipment_num = p_lines.shipment_num_tbl(i)
334 AND pol.line_num = p_lines.line_num_tbl(i)
335 AND poll.shipment_type IN ('STANDARD','PLANNED', 'RFQ')
336 --<Bug 14185466 Starts>
337 AND pol.po_line_id = poll.po_line_id
338 AND pol.draft_id = poll.draft_id;
339 --<Bug 14185466 Ends>
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 IF g_debug_stmt THEN
344 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: '||SQLERRM);
345 END IF;
346 WHEN OTHERS THEN
347 IF g_debug_unexp THEN
348 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
349 END IF;
350
351 --CLM Phase 2 changes : error handling
352 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_LOC_SEC_UOM_ERR',
353 x_token1_value => sqlerrm,
354 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
355
356 raise;
357 END;
358 END IF;
359
360 IF (po_autocreate_params.g_interface_source_code = 'CONSUMPTION_ADVICE') THEN
361 x_price := p_lines.unit_price_tbl(i);
362 ELSE
363 l_progress := '080';
364
365 BEGIN
366 SELECT unit_price
367 INTO x_price
368 FROM po_lines_draft_all --<Shared Proc FPJ>
369 WHERE po_line_id=p_lines.po_line_id_tbl(i)
370 AND draft_id =po_autocreate_params.g_draft_id;
371
372 EXCEPTION
373 WHEN OTHERS THEN
374 IF g_debug_stmt THEN
375 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
376 p_message => 'EXCEPTION caught; SQL Code is '||SQLCODE||'; Error is '||SQLERRM);
377 END IF;
378 x_price := NULL;
379 END;
380 END IF;
381
382 IF(x_line_location_id is not null) THEN
383
384 /*Autocreate grouping. In case of ADD_TO_MOD action, there can be concurrent modifications.
385 Hence lock has to be taken on the current modification to which the line is grouped to*/
386 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.
387
388 l_entity_name_tbl.EXTEND(1);
389 l_pk1_tbl.EXTEND(1);
390 l_pk2_tbl.EXTEND(1);
391 l_pk3_tbl.EXTEND(1);
392 l_pk4_tbl.EXTEND(1);
393
394 l_entity_name_tbl(1) := PO_DRAFTS_PVT.G_LOCK_SHIPMENT_ENTITY;
395 l_pk1_tbl(1) := x_line_location_id;
396 l_pk2_tbl(1) := NULL;
397 l_pk3_tbl(1) := NULL;
398 l_pk4_tbl(1) := NULL;
399
400 PO_DRAFTS_PVT.lock_entities(
401 p_entity_name_tbl => l_entity_name_tbl,
402 p_draft_id => PO_AUTOCREATE_PARAMS.g_draft_id,
403 p_pk1_tbl => l_pk1_tbl,
404 p_pk2_tbl => l_pk2_tbl,
405 p_pk3_tbl => l_pk3_tbl,
406 p_pk4_tbl => l_pk4_tbl,
407 x_return_status => l_return_status,
408 x_results => x_results
409 );
410
411
412
413 IF l_return_status = 'E' THEN
414 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
415 p_token => l_progress,
416 p_message => 'Create_shipment: Error while obtaining lock');
417 Raise l_lock_exception;
418 END IF;
419
420 END IF;
421 /*Autocreate grouping end*/
422
423 /*
424 ** Update everything except closed_code
425 */
426 l_progress:='120';
427 IF g_debug_stmt THEN
428 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
429 p_token => l_progress,
430 p_message => 'Create_shipment: shipment exist');
431 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
432 p_token => l_progress,
433 p_message => 'Create_shipment: Update PO line locations');
434 END IF;
435
436 /* Quantity conversion for foreign currency was not happening for
437 amount based lines when we try to add to existing shipment line */
438 IF ( x_order_type_lookup_code = 'QUANTITY' ) THEN
439 x_quantity := round(x_quantity,15);
440 ELSIF ( x_order_type_lookup_code = 'AMOUNT' ) THEN
441
442 l_progress:='110';
443 -- No conversion for same currency
444 l_cc_unit_price := p_lines.unit_price_tbl(i);
445 l_cc_base_unit_price := p_lines.base_unit_price_tbl(i);
446 l_cc_amount := p_lines.amount_tbl(i);
447 l_progress:='120';
448
449 po_auto_line_process_pvt.do_currency_conversion( p_order_type_lookup_code => 'AMOUNT',
450 p_interface_source_code => po_autocreate_params.g_interface_source_code,
451 p_rate => p_lines.rate_for_req_fields_tbl(i), -- <ACHTML R12>
452 p_po_currency_code => p_lines.hd_currency_code_tbl(i),
453 p_requisition_line_id => p_lines.requisition_line_id_tbl(i),
454 x_quantity => x_quantity,-- IN/OUT
455 x_unit_price => l_cc_unit_price,-- IN/OUT
456 x_base_unit_price => l_cc_base_unit_price,-- IN/OUT
457 x_amount => l_cc_amount-- IN/OUT
458 );
459
460 p_lines.unit_price_tbl(i) := l_cc_unit_price;
461 p_lines.base_unit_price_tbl(i) := l_cc_base_unit_price;
462 p_lines.amount_tbl(i) := l_cc_amount;
463
464 END IF;
465 --<INVCONV R12 START>
466 --If item is dual uom control and secondary quantity is NULL, derive it
467 l_progress:='130';
468 IF x_secondary_unit_of_measure IS NOT NULL THEN
469 IF p_lines.secondary_quantity_tbl(i) IS NULL THEN
470 PO_UOM_S.uom_convert (x_quantity,
471 NVL(x_shipment_uom,
472 p_lines.unit_of_measure_tbl(i)),
473 p_lines.item_id_tbl(i),
474 x_secondary_unit_of_measure ,
475 x_secondary_quantity) ;
476 ELSE
477 X_secondary_quantity := p_lines.secondary_quantity_tbl(i) ;
478 END IF;
479 ELSE
480 x_secondary_quantity := NULL ;
481 END IF;
482
483 l_progress:='140';
484 --<INVCONV R12 END>
485 --<INVCONV R12> replace interface.secondary_quantity with x_secondary_quantity
486 UPDATE po_line_locations_draft_all --<Shared Proc FPJ>
487 SET quantity = quantity + x_quantity,
488 secondary_quantity = secondary_quantity + x_secondary_quantity,
489 approved_flag = DECODE(approved_flag, 'N','N', 'R'),
490 last_update_date = p_lines.last_update_date_tbl(i),
491 last_update_login = p_lines.last_update_login_tbl(i),
492 last_updated_by = p_lines.last_updated_by_tbl(i),
493 price_override = DECODE(po_autocreate_params.g_document_type, 'RFQ', price_override, DECODE( NVL(x_price, -1), -1, price_override,
494 -- Use precision in rounding
495 ROUND(x_price, NVL(x_ext_precision,15)))),
496 -- Setting tax_attribute_update_code to update for
497 -- add_to cases.
498 tax_attribute_update_code = NVL(tax_attribute_update_code, NVL2(po_autocreate_params.g_calculate_tax_flag, 'UPDATE', NULL))
499 WHERE line_location_id = x_line_location_id
500 AND draft_id = po_autocreate_params.g_draft_id;
501 /*
502 ** OE Callback function for maintaining so_drop_ship_sources table
503 */
504 /*
505 ** OE redesign. No Shipments linked to sales order will be combined
506 ** Therefore, no need to do call back for update shipment
507 ** Removed oe callback.
508 */
509 /*
510 ** Prepare to call pocupdate_close: - call auto close.
511 */
512 -- Need to change - draft? serukull or move code to post proc?
513 IF (po_autocreate_params.g_document_type = 'PO') THEN
514 IF (po_autocreate_params.g_mode = 'ADD') THEN
515 IF (po_autocreate_params.g_document_type = 'PO') THEN
516 IF (po_autocreate_params.g_document_subtype = 'RELEASE') THEN
517 NULL;
518 ELSE
519 x_doctype := 'PO';
520 IF NOT po_actions.close_po(p_lines.po_header_id_tbl(i),
521 x_doctype,
522 po_autocreate_params.g_document_subtype,
523 p_lines.po_line_id_tbl(i),
524 x_line_location_id,
525 'CLOSE',
526 '',
527 'PO',
528 'N',
529 x_return_code,
530 'Y') THEN
531 po_message_s.sql_error('CLOSE_PO',l_progress,SQLCODE);
532 END IF;
533 END IF;
534 l_progress := '150';
535 IF g_debug_stmt THEN
536 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
537 p_message => 'Create shipment: Before calling Auto close');
538 END IF;
539 IF NOT po_actions.close_po(p_lines.po_header_id_tbl(i),
540 x_doctype,
541 po_autocreate_params.g_document_subtype,
542 p_lines.po_line_id_tbl(i),
543 x_line_location_id,
544 'CLOSE',
545 '',
546 'PO',
547 'N',
548 x_return_code,
549 'Y') THEN
550 po_message_s.sql_error('CLOSE_PO',l_progress,SQLCODE);
551 END IF;
552 END IF;
553 END IF;
554 END IF;
555
556 ELSIF(x_line_location_id IS NULL) THEN
557 l_progress := '160';
558
559 IF (po_autocreate_params.g_document_type = 'PO') THEN
560 /*
561 ** Prepare to call pocupdate_close: - call manual close
562 ** for the line level.
563 */
564 IF (po_autocreate_params.g_mode = 'ADD') THEN
565 IF (po_autocreate_params.g_document_subtype = 'RELEASE') THEN
566 NULL;
567 ELSE
568 x_doctype := 'PO';
569 IF NOT po_actions.close_po(p_lines.po_header_id_tbl(i),
570 x_doctype, po_autocreate_params.g_document_subtype,
571 p_lines.po_line_id_tbl(i),
572 x_line_location_id,
573 'CLOSE',
574 '',
575 'PO',
576 'N',
577 x_return_code,
578 'N') THEN
579 l_progress := '170';
580 po_message_s.sql_error('CLOSE_PO',l_progress,SQLCODE);
581 END IF;
582 END IF;
583 l_progress := '180';
584 IF NOT po_actions.close_po(p_lines.po_header_id_tbl(i),
585 x_doctype,
586 po_autocreate_params.g_document_subtype,
587 p_lines.po_line_id_tbl(i),
588 x_line_location_id,
589 'CLOSE',
590 '',
591 'PO',
592 'N',
593 x_return_code,
594 'N') THEN
595 po_message_s.sql_error('CLOSE_PO',l_progress,SQLCODE);
596 END IF;
597 END IF;
598 END IF;
599
600 l_progress:='190';
601 IF g_debug_stmt THEN
602 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
603 p_message => 'create shipment: Shipment does not exist');
604 END IF;
605
606 l_progress := '200';
607 /*
608 * Adjust quantity for foreign currecny
609 */
610 IF ( x_order_type_lookup_code = 'QUANTITY' ) THEN -- <SERVICES FPJ>
611 l_progress := '210';
612 x_quantity := ROUND(x_quantity,15);
613 ELSIF ( x_order_type_lookup_code = 'AMOUNT' ) THEN -- <SERVICES FPJ>
614 l_progress := '220';
615 l_cc_unit_price := p_lines.unit_price_tbl(i);
616 l_cc_base_unit_price := p_lines.base_unit_price_tbl(i);
617 l_cc_amount := p_lines.amount_tbl(i);
618 po_auto_line_process_pvt.do_currency_conversion( p_order_type_lookup_code => 'AMOUNT',
619 p_interface_source_code => po_autocreate_params.g_interface_source_code,
620 p_rate => p_lines.rate_for_req_fields_tbl(i), -- <ACHTML R12>
621 p_po_currency_code => p_lines.hd_currency_code_tbl(i),
622 p_requisition_line_id => p_lines.requisition_line_id_tbl(i),
623 x_quantity => x_quantity, -- IN/OUT
624 x_unit_price => l_cc_unit_price,-- IN/OUT
625 x_base_unit_price => l_cc_base_unit_price,-- IN/OUT
626 x_amount => l_cc_amount-- IN/OUT
627 );
628 p_lines.unit_price_tbl(i) := l_cc_unit_price;
629 p_lines.base_unit_price_tbl(i) := l_cc_base_unit_price;
630 p_lines.amount_tbl(i) := l_cc_amount;
631
632 END IF;
633 /*
634 ** Create a new shipment.
635 */
636 l_progress:='230';
637 IF g_debug_stmt THEN
638 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
639 p_message => 'Create shipment: Create a new shipment');
640 END IF;
641
642 l_progress:='240';
643 SELECT po_line_locations_s.nextval INTO x_line_location_id FROM sys.dual;
644
645 -- Get the taxable_flag based on the following priority
646 -- 1. preferences (global.po_taxable_flag)
647 -- 2. ship_to_org (x_ship_to_org_taxable_flag)
648 -- 2. item-org (po_lines.taxable_flag)
649 -- 3. PO default (po_startup_values.taxable_flag)
650 l_progress :='250';
651 x_item_org_taxable_flag := p_lines.it_taxable_flag_tbl(i);
652
653 IF (po_autocreate_params.g_interface_source_code = 'CONSUMPTION_ADVICE') THEN
654 -- tax_code_id from the interface table is used for consumption advice
655 l_progress :='260';
656 IF (NVL(p_lines.tax_code_id_tbl(i), -1) = -1) THEN
657 -- FPI inventory code was populating tax_code_id = -1 in some cases
658 -- for consumption advice. Never insert -1.
659 p_lines.tax_code_id_tbl(i) := NULL;
660 x_return_taxable_flag := 'N';
661 ELSE
662 x_return_taxable_flag := 'Y';
663 END IF;
664
665 ELSE
666 l_progress :='280';
667 IF (x_tax_code_id IS NOT NULL) THEN
668 x_return_taxable_flag := 'Y';
669 ELSE
670 x_return_taxable_flag := 'N';
671 END IF;
672
673 END IF;
674
675 l_progress:='290';
676 po_coo_s.get_default_country_of_origin( p_lines.item_id_tbl(i),
677 p_lines.dest_organization_id_tbl(i),
678 p_lines.hd_vendor_id_tbl(i),
679 p_lines.hd_vendor_site_id_tbl(i),
680 x_country_of_origin_code);
681
682 IF p_lines.hd_currency_code_tbl(i) IS NOT NULL THEN
683 l_progress:='300';
684 fnd_currency.get_info(p_lines.hd_currency_code_tbl(i),
685 x_precision,
686 x_ext_precision,
687 x_min_unit );
688 END IF;
689 l_progress := '310';
690
691 IF g_debug_stmt THEN
692 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
693 p_message => 'Create shipment: Before insert into po line locations');
694 END IF;
695
696 IF p_lines.consigned_flag_tbl(i) = 'Y' THEN
697 x_closed_reason := fnd_message.get_string('PO', 'PO_SUP_CONS_CLOSED_REASON');
698 ELSE
699 x_closed_reason := NULL;
700 END IF;
701
702 IF po_autocreate_params.g_document_type <> 'RFQ' AND l_promised_date IS NULL AND NVL(l_po_promised_def_prf, 'N') = 'Y' THEN
703 l_promised_date := p_lines.NEED_BY_DATE_tbl(i);
704 END IF;
705
706 l_progress := '320';
707 IF g_debug_stmt THEN
708 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
709 p_message => 'interface.trxn_flow_header_id='||p_lines.txn_flow_header_id_tbl(i));
710 END IF;
711
712 IF p_lines.item_id_tbl(i) IS NOT NULL THEN
713 IF p_lines.secondary_quantity_tbl(i) IS NULL THEN
714 l_progress := '330';
715 PO_UOM_S.get_secondary_uom( p_lines.item_id_tbl(i),
716 p_lines.dest_organization_id_tbl(i),
717 x_secondary_uom_code,
718 x_secondary_unit_of_measure);
719
720 IF x_secondary_unit_of_measure IS NOT NULL AND x_quantity > 0 THEN
721 l_progress := '340';
722 PO_UOM_S.uom_convert (x_quantity,
723 NVL(x_shipment_uom, p_lines.unit_of_measure_tbl(i)),
724 p_lines.item_id_tbl(i),
725 x_secondary_unit_of_measure ,
726 x_secondary_quantity) ;
727 ELSE
728 X_secondary_quantity := NULL;
729 END IF;
730 ELSE
731 X_secondary_quantity := p_lines.secondary_quantity_tbl(i);
732 X_secondary_unit_of_measure := p_lines.secondary_unit_of_meas_tbl(i);
733 END IF;
734 ELSE
735 X_secondary_quantity := NULL;
736 X_secondary_unit_of_measure := NULL;
737 END IF;
738
739 l_progress := '350';
740 INSERT
741 INTO po_line_locations_draft_all --<Shared Proc FPJ>
742 (
743 draft_id,
744 line_location_id,
745 last_update_date,
746 last_updated_by,
747 po_header_id,
748 creation_date,
749 created_by,
750 last_update_login,
751 po_line_id,
752 quantity,
753 quantity_received,
754 quantity_accepted,
755 quantity_rejected,
756 quantity_billed,
757 quantity_cancelled,
758 amount,
759 amount_received,
760 amount_accepted,
761 amount_rejected,
762 amount_billed,
763 amount_cancelled,
764 ship_to_location_id,
765 need_by_date,
766 promised_date,
767 from_header_id,
768 from_line_id,
769 note_to_receiver,
770 approved_flag,
771 po_release_id,
772 closed_code,
773 closed_reason,
774 price_override,
775 encumbered_flag,
776 shipment_type,
777 shipment_num,
778 inspection_required_flag,
779 receipt_required_flag,
780 days_early_receipt_allowed,
781 days_late_receipt_allowed,
782 enforce_ship_to_location_code,
783 ship_to_organization_id,
784 invoice_close_tolerance,
785 receive_close_tolerance,
786 accrue_on_receipt_flag,
787 allow_substitute_receipts_flag,
788 receiving_routing_id,
789 qty_rcv_tolerance,
790 qty_rcv_exception_code,
791 receipt_days_exception_code,
792 terms_id,
793 ship_via_lookup_code,
794 freight_terms_lookup_code,
795 fob_lookup_code,
796 unit_meas_lookup_code,
797 last_accept_date,
798 match_option,
799 country_of_origin_code,
800 secondary_unit_of_measure,
801 secondary_quantity,
802 preferred_grade,
803 secondary_quantity_received,
804 secondary_quantity_accepted,
805 secondary_quantity_rejected,
806 secondary_quantity_cancelled,
807 vmi_flag, -- VMI FPH
808 drop_ship_flag, -- <DropShip FPJ>
809 consigned_flag, -- CONSIGNED FPI
810 transaction_flow_header_id, --<Shared Proc FPJ>
811 org_id --<Shared Proc FPJ>
812 ,
813 closed_for_receiving_date ,
814 closed_for_invoice_date ,
815 value_basis ,
816 matching_basis ,
817 outsourced_assembly ,
818 tax_attribute_update_code --<eTax Integration R12>
819 ,
820 clm_period_perf_end_date ,
821 clm_period_perf_start_date,
822 --CLM Phase4 Changes
823 clm_delivery_period,
824 clm_promise_period,
825 clm_pop_duration,
826 clm_delivery_period_uom,
827 clm_promise_period_uom,
828 clm_pop_duration_uom
829 )
830 VALUES
831 (
832 po_autocreate_params.g_draft_id,
833 x_line_location_id,
834 p_lines.last_update_date_tbl(i),
835 p_lines.last_updated_by_tbl(i),
836 p_lines.po_header_id_tbl(i),
837 p_lines.creation_date_tbl(i),
838 p_lines.created_by_tbl(i),
839 p_lines.last_update_login_tbl(i),
840 p_lines.po_line_id_tbl(i),
841 x_quantity, --interface.quantity,
842 0,
843 0,
844 0,
845 0,
846 0,
847 p_lines.amount_tbl(i), -- amount -- <SERVICES FPJ>
848 0, -- amount_received -- <SERVICES FPJ>
849 0, -- amount_accepted -- <SERVICES FPJ>
850 0, -- amount_rejected -- <SERVICES FPJ>
851 0, -- amount_billed -- <SERVICES FPJ>
852 0, -- amount_cancelled -- <SERVICES FPJ>
853 x_ship_to_location_id,
854 p_lines.need_by_date_tbl(i),
855 l_promised_date,
856 DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.from_header_id_tbl(i)),
857 DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.from_line_id_tbl(i)),
858 p_lines.note_to_receiver_tbl(i),
859 DECODE(po_autocreate_params.g_document_type, 'RFQ', '', 'N'),
860 '',
861 DECODE(p_lines.consigned_flag_tbl(i),
862 'Y', 'CLOSED FOR INVOICE',
863 DECODE(po_autocreate_params.g_interface_source_code,
864 'CONSUMPTION_ADVICE', 'CLOSED FOR RECEIVING' ,
865 DECODE(po_autocreate_params.g_document_type, 'RFQ', '', 'OPEN'))),
866 x_closed_reason,
867 NVL(x_price,p_lines.unit_price_tbl(i)),
868 DECODE(po_autocreate_params.g_document_type, 'RFQ', '', 'N'),
869 DECODE(po_autocreate_params.g_document_type,
870 'RFQ', 'RFQ',
871 DECODE(po_autocreate_params.g_document_subtype,'RELEASE','BLANKET',po_autocreate_params.g_document_subtype)),
872 p_lines.shipment_num_tbl(i),
873 DECODE(po_autocreate_params.g_interface_source_code,
874 'CONSUMPTION_ADVICE', 'N' ,
875 DECODE(p_lines.consigned_flag_tbl(i),
876 'Y', 'N',
877 DECODE(p_lines.drop_ship_flag_tbl(i),
878 'Y', 'N',
879 DECODE(x_order_type_lookup_code,
880 'FIXED PRICE','N',
881 'RATE','N',
882 DECODE(po_autocreate_params.g_document_type,
883 'RFQ', NVL(p_lines.it_inspect_req_flag_tbl(i),
884 NVL(po_autocreate_params.g_sys.inspection_required_flag,'N')),
885 NVL(p_lines.it_inspect_req_flag_tbl(i),
886 NVL(po_autocreate_params.g_vendor_inspect_req_flag,
887 NVL(po_autocreate_params.g_sys.inspection_required_flag,'N'))))) ) ) ),
888 DECODE(po_autocreate_params.g_interface_source_code,
889 'CONSUMPTION_ADVICE', 'N' , -- CONSIGNED FPI
890 DECODE(p_lines.consigned_flag_tbl(i),
891 'Y', 'N',
892 DECODE(po_autocreate_params.g_document_type,
893 'RFQ', NVL(p_lines.receipt_required_flag_tbl(i),
894 NVL(p_lines.receipt_required_flag_tbl(i),
895 NVL(po_autocreate_params.g_sys.receiving_flag,'N'))),
896 NVL(p_lines.it_receipt_req_flag_tbl(i), NVL(p_lines.receipt_required_flag_tbl(i),
897 NVL(po_autocreate_params.g_vendor_receipt_req_flag, NVL(po_autocreate_params.g_sys.receiving_flag,'N'))))
898 ) ) ),
899 DECODE(po_autocreate_params.g_document_type, 'RFQ', '', p_lines.rc_days_early_recpt_tbl(i)),
900 DECODE(po_autocreate_params.g_document_type, 'RFQ', '', p_lines.rc_days_late_recpt_tbl(i)),
901 DECODE(po_autocreate_params.g_document_type, 'RFQ', '', p_lines.rc_enforce_shipto_loc_code_tbl(i)),
902 p_lines.dest_organization_id_tbl(i), -- ship to org
903 DECODE(p_lines.consigned_flag_tbl(i),
904 'Y', 100 , -- CONSIGNED FPI
905 (DECODE(po_autocreate_params.g_document_type,
906 'RFQ', '',
907 (DECODE(p_lines.hd_pcard_id_tbl(i),
908 NULL, NVL(p_lines.it_invoice_tolerance_tbl(i),
909 po_autocreate_params.g_sys.invoice_close_tolerance), 100))))),
910 DECODE(po_autocreate_params.g_interface_source_code,
911 'CONSUMPTION_ADVICE', 100 , -- CONSIGNED FPI
912 (DECODE(po_autocreate_params.g_document_type,
913 'RFQ', '', NVL(p_lines.it_rcv_tolerance_tbl(i),
914 po_autocreate_params.g_sys.receive_close_tolerance)))),
915 DECODE(p_lines.txn_flow_header_id_tbl(i), NULL, --<Shared Proc FPJ>
916 DECODE(p_lines.consigned_flag_tbl(i),
917 'Y', 'N' , -- CONSIGNED FPI
918 DECODE(po_autocreate_params.g_document_type,
919 'RFQ', '',
920 DECODE( p_lines.hd_pcard_id_tbl(i),
921 NULL, DECODE(p_lines.destination_type_code_tbl(i),
922 'EXPENSE',DECODE(NVL(p_lines.it_receipt_req_flag_tbl(i),
923 NVL(p_lines.receipt_required_flag_tbl(i),
924 NVL(po_autocreate_params.g_vendor_receipt_req_flag,
925 NVL(po_autocreate_params.g_sys.receiving_flag,'N')))),
926 'N', 'N',
927 DECODE(po_autocreate_params.g_sys.expense_accrual_code,
928 'PERIOD END', 'N', 'Y')),
929 'Y'),'N'))), 'Y'), --<Shared Proc FPJ>
930 DECODE(po_autocreate_params.g_document_type, 'RFQ','', p_lines.rc_subst_receipt_flag_tbl(i)),
931 DECODE(po_autocreate_params.g_document_type, 'RFQ', '', p_lines.rc_receiving_routing_id_tbl(i)),
932 p_lines.rc_qty_rcv_tolerance_tbl(i),
933 p_lines.rc_qty_rcv_exception_code_tbl(i),
934 DECODE(po_autocreate_params.g_document_type, 'RFQ', '', p_lines.rc_receipt_days_excep_code_tbl(i)),
935 '',
936 '',
937 '',
938 '',
939 NVL(x_po_uom,p_lines.unit_of_measure_tbl(i)),
940 DECODE(po_autocreate_params.g_document_type,'RFQ',to_date(NULL),l_promised_date+p_lines.rc_days_late_recpt_tbl(i)),
941 DECODE(po_autocreate_params.g_interface_source_code,
942 'CONSUMPTION_ADVICE', 'P' , -- CONSIGNED FPI
943 DECODE(p_lines.consigned_flag_tbl(i),
944 'Y', 'P',
945 DECODE(po_autocreate_params.g_document_type,
946 'RFQ', '',
947 po_autocreate_params.g_vendor_invoice_match_option) ) ),
948 x_country_of_origin_code,
949 x_secondary_unit_of_measure,
950 x_secondary_quantity,
951 p_lines.preferred_grade_tbl(i),
952 DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
953 DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
954 DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
955 DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
956 p_lines.vmi_flag_tbl(i) , -- VMI FPH
957 p_lines.drop_ship_flag_tbl(i), -- <DropShip FPJ>
958 p_lines.consigned_flag_tbl(i), -- CONSIGNED FPI
959 p_lines.txn_flow_header_id_tbl(i), --<Shared Proc FPJ>
960 po_autocreate_params.g_purchasing_ou_id --<Shared Proc FPJ>
961 ,
962 DECODE(po_autocreate_params.g_interface_source_code,'CONSUMPTION_ADVICE', sysdate,NULL) --- Closed_for_receiving_date
963 ,
964 DECODE(p_lines.consigned_flag_tbl(i), 'Y', sysdate,NULL ) --- Closed_for_invoice_date
965 ,
966 x_order_type_lookup_code ,
967 l_matching_basis ,
968 l_outsourced_assembly --<SHIKYU R12>
969 ,
970 nvl2(po_autocreate_params.g_calculate_tax_flag, 'CREATE', NULL) --<eTax Integration R12>
971 ,
972 p_lines.clm_period_perf_end_date_tbl(i) ,
973 p_lines.clm_period_perf_start_date_tbl(i),
974 --CLM Phase4 Changes
975 p_lines.clm_delivery_period_tbl(i),
976 p_lines.clm_promise_period_tbl(i),
977 p_lines.clm_pop_duration_tbl(i),
978 p_lines.clm_delivery_period_uom_tbl(i),
979 p_lines.clm_promise_period_uom_tbl(i),
980 p_lines.clm_pop_duration_uom_tbl(i)
981 );
982
983 l_progress := '360';
984 IF g_debug_stmt THEN
985 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
986 p_message => 'Create shipment: After insert into po line locations DRAFT');
987 END IF;
988 END IF;
989
990 idx := idx + 1;
991 p_lines.line_loc_id_tbl(i) := x_line_location_id;
992
993 END IF;
994
995 END LOOP;
996
997 IF g_debug_stmt THEN
998 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
999 END IF;
1000
1001 EXCEPTION
1002
1003 --autocreate grouping start
1004 WHEN l_lock_exception THEN
1005 IF g_debug_unexp THEN
1006 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name,
1007 p_progress => l_progress);
1008 END IF;
1009 po_message_s.sql_error('CREATE_SHIPMENT_DRAFT',l_progress,sqlcode);
1010 PO_AUTOCREATE_PVT.wrapup();
1011 RAISE;
1012 --autocreate grouping end
1013 WHEN OTHERS THEN
1014 IF g_debug_unexp THEN
1015 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1016 END IF;
1017
1018 --CLM Phase 2 changes : error handling
1019 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_LOC_DEFAULT_ERR',x_token1_value => sqlerrm);
1020
1021 --ROLLBACK to create_price_break_pvt;
1022 RAISE;
1023
1024 END create_shipment_draft;
1025
1026 -------------------------------------------------------------------------------
1027 --Start of Comments
1028 --Name: create_payitems_draft
1029 --Pre-reqs:
1030 -- PO Line has been created.
1031 --Modifies:
1032 -- PO_LINE_LOCATIONS_DRAFT
1033 --Locks:
1034 -- None.
1035 --Function:
1036 -- Create all payitems for a PO Line. If PO_LINE_LOCATIONS_INTERFACE is
1037 -- populated, use that information. Otherwise, create a default
1038 -- payitem. Also create DELIVERY and ADVANCE payitems as necessary.
1039 --Parameters:
1040 -- IN OUT: p_lines Lines data
1041 --Notes:
1042 -- None
1043 --Testing:
1044 -- None
1045 --End of Comments
1046 ---------------------------------------------------------------------------
1047 PROCEDURE create_payitem_draft(p_lines IN OUT NOCOPY PO_AUTOCREATE_TYPES.lines_rec_type)
1048 IS
1049
1050 d_progress NUMBER;
1051 d_module VARCHAR2(100) := 'po.plsql.po_auto_line_loc_process_pvt.create_payitem_draft';
1052
1053 l_po_header_id PO_LINES_ALL.po_header_id%TYPE;
1054 l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
1055 l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
1056 l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
1057 l_line_quantity PO_LINES_ALL.quantity%TYPE;
1058 l_line_amount PO_LINES_ALL.amount%TYPE;
1059 l_line_purchase_basis PO_LINES_ALL.purchase_basis%TYPE;
1060 l_payment_type PO_LINE_LOCATIONS_ALL.payment_type%TYPE;
1061 l_shipment_type PO_LINE_LOCATIONS_ALL.shipment_type%TYPE;
1062 l_payitem_quantity PO_LINE_LOCATIONS_ALL.quantity%TYPE;
1063 l_payitem_amount PO_LINE_LOCATIONS_ALL.amount%TYPE;
1064 l_payitem_price PO_LINE_LOCATIONS_ALL.price_override%TYPE;
1065 l_req_tax_code_id PO_REQUISITION_LINES_ALL.tax_code_id%TYPE;
1066 l_req_tax_user_override_flag PO_REQUISITION_LINES_ALL.tax_user_override_flag%TYPE;
1067 l_req_tax_status_indicator PO_REQUISITION_LINES_ALL.tax_status_indicator%TYPE;
1068 l_tax_name AP_TAX_CODES.name%TYPE;
1069 l_tax_code_id AP_TAX_CODES.tax_id%TYPE;
1070 l_tax_type AP_TAX_CODES.tax_type%TYPE;
1071 l_tax_description AP_TAX_CODES.description%TYPE;
1072 l_allow_tax_code_override_flag GL_TAX_OPTION_ACCOUNTS.allow_tax_code_override_flag%TYPE;
1073 l_isFinancing BOOLEAN;
1074 l_ship_to_location_id NUMBER;
1075 l_payitem_tax_code_id_tbl po_tbl_number;
1076 l_payitems_created NUMBER;
1077 l_routing_name RCV_ROUTING_HEADERS.routing_name%TYPE;
1078 l_line_loc_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE;
1079 l_po_promised_def_prf VARCHAR2(1) := FND_PROFILE.value('PO_NEED_BY_PROMISE_DEFAULTING');
1080 l_country_of_origin_code VARCHAR2(2);
1081 l_tax_status VARCHAR2(10);
1082 l_encoded_msg VARCHAR2(2000);
1083 l_advance_desc VARCHAR2(240);
1084 --<eTax integration R12 Start >
1085 l_return_status VARCHAR2(1);
1086 l_msg_count NUMBER;
1087 l_msg_data VARCHAR2(2000);
1088 g_line_requesting_ou_id NUMBER;
1089 x_line_loc_id_tbl po_tbl_number;
1090 x_line_location_id NUMBER;
1091 --<eTax integration R12 End>
1092
1093 CURSOR poll_interface_cursor(p_interface_line_id NUMBER)
1094 IS
1095 SELECT polli.interface_line_location_id,
1096 polli.quantity,
1097 polli.amount,
1098 polli.ship_to_location_id,
1099 polli.need_by_date,
1100 polli.promised_date,
1101 polli.price_override,
1102 polli.shipment_type,
1103 polli.shipment_num,
1104 polli.ship_to_organization_id,
1105 polli.value_basis,
1106 polli.matching_basis,
1107 polli.payment_type,
1108 polli.description,
1109 polli.work_approver_id,
1110 polli.bid_payment_id,
1111 polli.unit_of_measure
1112 FROM po_line_locations_interface polli
1113 WHERE polli.interface_line_id = p_interface_line_id
1114 ORDER BY polli.shipment_num;
1115
1116 line_location_rec poll_interface_cursor%ROWTYPE;
1117 TYPE rcv_controls_type
1118 IS
1119 RECORD
1120 (
1121 enforce_ship_to_location_code VARCHAR2 (25) := NULL,
1122 allow_substitute_receipts_flag VARCHAR2 (1),
1123 receiving_routing_id NUMBER,
1124 qty_rcv_tolerance NUMBER,
1125 qty_rcv_exception_code VARCHAR2 (25),
1126 days_early_receipt_allowed NUMBER,
1127 days_late_receipt_allowed NUMBER,
1128 receipt_days_exception_code VARCHAR2 (25) );
1129 payitem_rcv_ctl_rec rcv_controls_type;
1130
1131 BEGIN
1132
1133 IF (PO_LOG.d_proc) THEN
1134 PO_LOG.proc_begin(d_module);
1135 END IF;
1136
1137 d_progress := 0;
1138
1139 x_line_loc_id_tbl := po_tbl_number();
1140 l_payitem_tax_code_id_tbl := po_tbl_number();
1141 l_payitems_created := 0;
1142
1143 FOR i IN 1.. p_lines.intf_line_id_tbl.Count
1144 LOOP
1145 l_payment_type := NULL;
1146 l_payitem_quantity := NULL;
1147 l_payitem_amount := NULL;
1148 l_payitem_price := NULL;
1149 l_line_unit_price :=NULL;
1150 l_line_amount :=NULL;
1151 l_advance_desc := NULL;
1152 l_req_tax_code_id := NULL;
1153 l_req_tax_user_override_flag := NULL;
1154 l_req_tax_status_indicator := NULL;
1155
1156 IF (PO_LOG.d_stmt) THEN
1157 PO_LOG.stmt(d_module, d_progress, 'Interface_line_id', p_lines.intf_line_id_tbl(i));
1158 PO_LOG.stmt(d_module, d_progress, 'Po_line_id', p_lines.po_line_id_tbl(i));
1159 PO_LOG.stmt(d_module, d_progress, 'Clm Info Flag', p_lines.clm_info_flag_tbl(i));
1160 po_Log.stmt(d_module, d_progress, 'Shipment Num', p_lines.shipment_num_tbl(i));
1161 END IF;
1162
1163 IF p_lines.clm_info_flag_tbl(i)='N' AND p_lines.shipment_num_tbl(i) IS NOT NULL THEN
1164 d_progress := 20;
1165
1166 SELECT pol.order_type_lookup_code ,
1167 pol.matching_basis ,
1168 pol.po_header_id ,
1169 pol.unit_price ,
1170 pol.quantity ,
1171 pol.amount ,
1172 pol.purchase_basis
1173 INTO l_line_value_basis ,
1174 l_line_matching_basis ,
1175 l_po_header_id ,
1176 l_line_unit_price ,
1177 l_line_quantity ,
1178 l_line_amount ,
1179 l_line_purchase_basis
1180 FROM po_lines_draft_all pol
1181 WHERE pol.po_line_id = p_lines.po_line_id_tbl(i)
1182 AND draft_id = po_autocreate_params.g_draft_id;
1183
1184 d_progress := 30;
1185 l_isFinancing := PO_COMPLEX_WORK_PVT.is_financing_po( p_po_header_id => l_po_header_id,
1186 p_draft_id => po_autocreate_params.g_draft_id );
1187
1188 IF (PO_LOG.d_stmt) THEN
1189 PO_LOG.stmt(d_module, d_progress, 'l_isFinancing', l_isFinancing);
1190 END IF;
1191
1192 d_progress := 40;
1193
1194 IF (p_lines.poll_intf_pop_flag_tbl(i) = 'N') THEN
1195 d_progress := 50;
1196
1197 -- If line locations interface is not populated, then we are either
1198 -- autocreating from requisition or there are no payitems negotiated
1199 -- in sourcing for this line. We need to populate data for the default
1200 -- payitem in the interface tables.
1201
1202 PO_COMPLEX_WORK_PVT.get_default_payitem_info( p_po_header_id => l_po_header_id ,
1203 p_draft_id => po_autocreate_params.g_draft_id ,
1204 p_po_line_id => p_lines.po_line_id_tbl(i) ,
1205 p_line_value_basis => l_line_value_basis ,
1206 p_line_matching_basis => l_line_matching_basis ,
1207 p_line_qty => ROUND(l_line_quantity, 15) ,
1208 p_line_amt => l_line_amount ,
1209 p_line_price => l_line_unit_price ,
1210 x_payment_type => l_payment_type ,
1211 x_payitem_qty => l_payitem_quantity ,
1212 x_payitem_amt => l_payitem_amount ,
1213 x_payitem_price => l_payitem_price );
1214
1215 d_progress := 60;
1216
1217 --SQL WHAT: Insert information for default payitem into
1218 -- po_line_locations interface table
1219 --SQL WHY : We will use line_locations_interface as a
1220 -- common entry point for payitems - whether they come from
1221 -- sourcing or we default them from scratch here.
1222 INSERT
1223 INTO po_line_locations_interface
1224 (
1225 interface_line_location_id ,
1226 interface_header_id ,
1227 interface_line_id ,
1228 quantity ,
1229 amount ,
1230 price_override ,
1231 shipment_type ,
1232 payment_type ,
1233 shipment_num ,
1234 need_by_date ,
1235 promised_date
1236 )
1237 VALUES
1238 (
1239 PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL ,
1240 p_lines.intf_header_id_tbl(i) ,
1241 p_lines.intf_line_id_tbl(i) ,
1242 l_payitem_quantity ,
1243 l_payitem_amount ,
1244 l_payitem_price ,
1245 NULL ,
1246 l_payment_type ,
1247 1 ,
1248 p_lines.need_by_date_tbl(i) ,
1249 p_lines.promised_date_tbl(i)
1250 /* 11840142 added promised date */
1251 );
1252 END IF; -- interface.poll_interface_pop_flag = 'N'
1253
1254 d_progress := 60;
1255 IF(l_isFinancing) THEN
1256
1257 d_progress := 70;
1258 -- if financing case, create actual delivery payitem
1259 -- this payitem has a shipment_type of STANDARD and payment_type of DELIVERY
1260 --SQL WHAT: Insert information for delivery payitem into
1261 -- po_line_locations_interface table
1262 --SQL WHY : We will use line_locations_interface as a
1263 -- common entry point for payitems, including ones we create
1264 -- behind the scenes
1265 INSERT
1266 INTO po_line_locations_interface
1267 (
1268 interface_line_location_id ,
1269 interface_header_id ,
1270 interface_line_id ,
1271 quantity ,
1272 amount ,
1273 price_override ,
1274 payment_type ,
1275 shipment_type ,
1276 description ,
1277 shipment_num ,
1278 need_by_date ,
1279 promised_date
1280 )
1281 VALUES
1282 (
1283 PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL ,
1284 p_lines.intf_header_id_tbl(i) ,
1285 p_lines.intf_line_id_tbl(i) ,
1286 l_line_quantity ,
1287 l_line_amount ,
1288 l_line_unit_price ,
1289 'DELIVERY' ,
1290 'STANDARD' ,
1291 p_lines.item_desc_tbl(i) ,
1292 1 ,
1293 p_lines.need_by_date_tbl(i) ,
1294 p_lines.promised_date_tbl(i)
1295 );
1296 END IF; -- if l_isFinancing
1297
1298 d_progress := 80;
1299 IF(PO_LOG.d_stmt) THEN
1300 PO_LOG.stmt(d_module, d_progress, 'has_advance_flag', p_lines.has_advance_flag_tbl(i));
1301 END IF;
1302
1303 IF(p_lines.has_advance_flag_tbl(i) = 'Y') THEN
1304
1305 d_progress := 90;
1306
1307 -- if line has advance, create financing payitem to represent it.
1308 -- the payitem has shipment_type of PREPAYMENT and payment_type of ADVANCE
1309 -- get default advance description
1310 FND_MESSAGE.set_name('PO', 'PO_CWPUI_ADVANCE_DESC_PREFIX');
1311 FND_MESSAGE.set_token(token => 'LINE_DESC', value => p_lines.item_desc_tbl(i));
1312
1313 l_advance_desc := substrb(FND_MESSAGE.get, 1, 240);
1314
1315 --SQL WHAT: Insert information for advance payitem into
1316 -- po_line_locations_interface table
1317 --SQL WHY : We will use line_locations_interface as a
1318 -- common entry point for payitems, including ones we create
1319 -- behind the scenes
1320 INSERT
1321 INTO po_line_locations_interface
1322 (
1323 interface_line_location_id ,
1324 interface_header_id ,
1325 interface_line_id ,
1326 quantity ,
1327 amount ,
1328 price_override ,
1329 payment_type ,
1330 shipment_type ,
1331 description ,
1332 shipment_num ,
1333 need_by_date
1334 )
1335 VALUES
1336 (
1337 PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL ,
1338 p_lines.intf_header_id_tbl(i) ,
1339 p_lines.intf_line_id_tbl(i) ,
1340 NULL ,
1341 p_lines.advance_amount_tbl(i) ,
1342 NULL ,
1343 'ADVANCE' ,
1344 'PREPAYMENT' ,
1345 l_advance_desc ,
1346 0 ,
1347 NULL
1348 );
1349 END IF; -- if interface.has_advance_flag = 'Y'
1350
1351 d_progress := 100;
1352 -- at this point, line_locations_interface has been populated with
1353 -- all necessary payitems - whether they come from sourcing or were
1354 -- created above from line information.
1355 -- we now update all rows in the line interface, filling in
1356 -- columns as necessary. Some columns may have already been filled in;
1357 -- for those columns, we do a NULL check first (using NVL).
1358 l_ship_to_location_id := po_autocreate_mainproc_pvt.get_ship_to_loc(p_lines.deliver_to_loc_id_tbl(i));
1359
1360 IF(l_isFinancing) THEN
1361 l_shipment_type := 'PREPAYMENT';
1362 ELSE
1363 l_shipment_type := 'STANDARD';
1364 END IF;
1365
1366 d_progress := 110;
1367 --SQL WHAT: Default/update values for scratch payitems in interface table
1368 --SQL WHY : This allows us to update all new payitems' values at once,
1369 -- including payitems from sourcing or ones we've created in autocreate
1370 UPDATE po_line_locations_interface polli
1371 SET polli.value_basis = DECODE(polli.payment_type,
1372 'RATE', 'QUANTITY',
1373 'LUMPSUM', 'FIXED PRICE',
1374 'MILESTONE', l_line_value_basis,
1375 'ADVANCE', 'FIXED PRICE',
1376 'DELIVERY', l_line_value_basis, polli.value_basis),
1377 polli.matching_basis = DECODE(polli.payment_type,
1378 'RATE', 'QUANTITY',
1379 'LUMPSUM', 'AMOUNT',
1380 'MILESTONE', l_line_matching_basis,
1381 'ADVANCE', 'AMOUNT',
1382 'DELIVERY', l_line_matching_basis, polli.matching_basis),
1383 polli.ship_to_location_id = NVL(polli.ship_to_location_id, l_ship_to_location_id),
1384 polli.ship_to_organization_id = NVL(polli.ship_to_organization_id, p_lines.dest_organization_id_tbl(i)),
1385 polli.promised_date = NVL(polli.promised_date, DECODE(NVL(l_po_promised_def_prf, 'N'),
1386 'Y', polli.need_by_date, polli.promised_date)),
1387 polli.shipment_type = NVL(polli.shipment_type, l_shipment_type),
1388 polli.description = NVL(polli.description, p_lines.item_desc_tbl(i)),
1389 polli.unit_of_measure = NVL(polli.unit_of_measure, p_lines.unit_of_measure_tbl(i))
1390 WHERE polli.interface_line_id = p_lines.intf_line_id_tbl(i);
1391
1392 d_progress := 120;
1393 IF (p_lines.requisition_line_id_tbl(i) IS NOT NULL) THEN
1394 -- This piece of code will not be executed in the CLM flow
1395 d_progress := 130;
1396 SELECT prl.tax_code_id ,
1397 NVL(prl.tax_user_override_flag,'N') ,
1398 NVL(prl.tax_status_indicator,'SYSTEM') ,
1399 NVL(prl.org_id, po_autocreate_params.g_hdr_requesting_ou_id)
1400 INTO l_req_tax_code_id ,
1401 l_req_tax_user_override_flag ,
1402 l_req_tax_status_indicator ,
1403 g_line_requesting_ou_id
1404 FROM po_requisition_lines_all prl
1405 WHERE prl.requisition_line_id = p_lines.requisition_line_id_tbl(i);
1406
1407 l_tax_code_id := l_req_tax_code_id;
1408 ELSE
1409 d_progress := 140;
1410 l_req_tax_status_indicator := 'SYSTEM';
1411 g_line_requesting_ou_id := po_autocreate_params.g_hdr_requesting_ou_id;
1412 END IF; -- if interface.requisition_line_id IS NOT NULL
1413
1414 d_progress := 150;
1415 -- now iterate over the rows in the interface table
1416 -- for each row, get the default tax, receiving controls, and country of
1417 -- origin before inserting a new row into po_line_locations_all
1418 OPEN poll_interface_cursor(p_lines.intf_line_id_tbl(i));
1419 LOOP
1420 FETCH poll_interface_cursor INTO line_location_rec;
1421 EXIT
1422 WHEN poll_interface_cursor%NOTFOUND;
1423
1424 d_progress := 160;
1425
1426 IF (PO_LOG.d_stmt) THEN
1427 PO_LOG.stmt(d_module, d_progress, 'Getting receiving controls.');
1428 END IF;
1429
1430 RCV_CORE_S.get_receiving_controls( p_order_type_lookup_code => line_location_rec.value_basis ,
1431 p_purchase_basis => l_line_purchase_basis ,
1432 p_line_location_id => NULL ,
1433 p_item_id => p_lines.item_id_tbl(i) ,
1434 p_org_id => line_location_rec.ship_to_organization_id ,
1435 p_vendor_id => p_lines.hd_vendor_id_tbl(i) ,
1436 p_drop_ship_flag => p_lines.drop_ship_flag_tbl(i) ,
1437 p_payment_type => line_location_rec.payment_type ,
1438 x_enforce_ship_to_loc_code => payitem_rcv_ctl_rec.enforce_ship_to_location_code ,
1439 x_allow_substitute_receipts => payitem_rcv_ctl_rec.allow_substitute_receipts_flag ,
1440 x_routing_id => payitem_rcv_ctl_rec.receiving_routing_id ,
1441 x_routing_name => l_routing_name ,
1442 x_qty_rcv_tolerance => payitem_rcv_ctl_rec.qty_rcv_tolerance ,
1443 x_qty_rcv_exception_code => payitem_rcv_ctl_rec.qty_rcv_exception_code ,
1444 x_days_early_receipt_allowed => payitem_rcv_ctl_rec.days_early_receipt_allowed ,
1445 x_days_late_receipt_allowed => payitem_rcv_ctl_rec.days_late_receipt_allowed ,
1446 x_receipt_days_exception_code => payitem_rcv_ctl_rec.receipt_days_exception_code );
1447
1448 d_progress := 200;
1449 IF (PO_LOG.d_stmt) THEN
1450 PO_LOG.stmt(d_module, d_progress, 'Getting default country of origin');
1451 END IF;
1452
1453 po_coo_s.get_default_country_of_origin( x_item_id => p_lines.item_id_tbl(i) ,
1454 x_ship_to_org_id => line_location_rec.ship_to_organization_id ,
1455 x_vendor_id => p_lines.hd_vendor_id_tbl(i) ,
1456 x_vendor_site_id => p_lines.hd_vendor_site_id_tbl(i) ,
1457 x_country_of_origin => l_country_of_origin_code );
1458
1459 d_progress := 210;
1460
1461 IF (PO_LOG.d_stmt) THEN
1462 PO_LOG.stmt(d_module, d_progress, 'Inserting payitem into po_line_locations_all');
1463 END IF;
1464
1465 -- insert payitem into po_line_locations_all
1466 --SQL WHAT: Insert payitem, using info in po_line_locations_interface
1467 --SQL WHY : This allows us to insert all payitems in one location.
1468 INSERT
1469 INTO po_line_locations_draft_all
1470 (
1471 draft_id ,
1472 line_location_id ,
1473 last_update_date ,
1474 last_updated_by ,
1475 po_header_id ,
1476 creation_date ,
1477 created_by ,
1478 last_update_login ,
1479 po_line_id ,
1480 quantity ,
1481 quantity_received ,
1482 quantity_accepted ,
1483 quantity_rejected ,
1484 quantity_billed ,
1485 quantity_cancelled ,
1486 quantity_financed ,
1487 amount ,
1488 amount_received ,
1489 amount_accepted ,
1490 amount_rejected ,
1491 amount_billed ,
1492 amount_cancelled ,
1493 amount_financed ,
1494 ship_to_location_id ,
1495 need_by_date ,
1496 promised_date ,
1497 from_header_id ,
1498 from_line_id ,
1499 note_to_receiver ,
1500 approved_flag ,
1501 po_release_id ,
1502 closed_code ,
1503 closed_reason ,
1504 price_override ,
1505 encumbered_flag ,
1506 taxable_flag ,
1507 tax_code_id ,
1508 tax_user_override_flag ,
1509 shipment_type ,
1510 shipment_num ,
1511 inspection_required_flag ,
1512 receipt_required_flag ,
1513 days_early_receipt_allowed ,
1514 days_late_receipt_allowed ,
1515 enforce_ship_to_location_code ,
1516 ship_to_organization_id ,
1517 invoice_close_tolerance ,
1518 receive_close_tolerance ,
1519 accrue_on_receipt_flag ,
1520 allow_substitute_receipts_flag ,
1521 receiving_routing_id ,
1522 qty_rcv_tolerance ,
1523 qty_rcv_exception_code ,
1524 receipt_days_exception_code ,
1525 terms_id ,
1526 ship_via_lookup_code ,
1527 freight_terms_lookup_code ,
1528 fob_lookup_code ,
1529 unit_meas_lookup_code ,
1530 last_accept_date ,
1531 match_option ,
1532 country_of_origin_code ,
1533 vmi_flag ,
1534 drop_ship_flag ,
1535 consigned_flag ,
1536 transaction_flow_header_id ,
1537 org_id ,
1538 closed_for_receiving_date ,
1539 closed_for_invoice_date ,
1540 value_basis ,
1541 matching_basis ,
1542 payment_type ,
1543 description ,
1544 work_approver_id ,
1545 bid_payment_id ,
1546 outsourced_assembly ,
1547 tax_attribute_update_code --<eTax Integration R12>
1548 ,
1549 clm_period_perf_end_date ,
1550 clm_period_perf_start_date,
1551 --CLM Phase4 Changes
1552 clm_delivery_period,
1553 clm_promise_period,
1554 clm_pop_duration,
1555 clm_delivery_period_uom,
1556 clm_promise_period_uom,
1557 clm_pop_duration_uom
1558 )
1559 VALUES
1560 (
1561 po_autocreate_params.g_draft_id ,
1562 PO_LINE_LOCATIONS_S.nextval ,
1563 p_lines.last_update_date_tbl(i) ,
1564 p_lines.last_updated_by_tbl(i) ,
1565 p_lines.po_header_id_tbl(i) ,
1566 p_lines.creation_date_tbl(i) ,
1567 p_lines.created_by_tbl(i) ,
1568 p_lines.last_update_login_tbl(i) ,
1569 p_lines.po_line_id_tbl(i) ,
1570 line_location_rec.quantity -- quantity
1571 ,
1572 0 -- quantity_received
1573 ,
1574 0 -- quantity_accepted
1575 ,
1576 0 -- quantity_rejected
1577 ,
1578 0 -- quantity_billed
1579 ,
1580 0 -- quantity_cancelled
1581 ,
1582 0 -- quantity_financed
1583 ,
1584 line_location_rec.amount -- amount
1585 ,
1586 0 -- amount_received
1587 ,
1588 0 -- amount_accepted
1589 ,
1590 0 -- amount_rejected
1591 ,
1592 0 -- amount_billed
1593 ,
1594 0 -- amount_cancelled
1595 ,
1596 0 -- amount_financed
1597 ,
1598 line_location_rec.ship_to_location_id ,
1599 line_location_rec.need_by_date ,
1600 line_location_rec.promised_date ,
1601 DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.from_header_id_tbl(i)) ,
1602 DECODE(p_lines.consigned_flag_tbl(i),'Y',NULL,p_lines.from_line_id_tbl(i)) ,
1603 p_lines.note_to_receiver_tbl(i) ,
1604 'N' -- approved_flag
1605 ,
1606 NULL -- po_release_d
1607 ,
1608 'OPEN' -- closed_code
1609 ,
1610 NULL -- closed_reason
1611 ,
1612 line_location_rec.price_override ,
1613 'N' -- encumbered_flag
1614 ,
1615 NVL2(l_tax_code_id, 'Y', 'N') -- taxable_flag
1616 ,
1617 l_tax_code_id ,
1618 l_req_tax_user_override_flag ,
1619 line_location_rec.shipment_type ,
1620 line_location_rec.shipment_num ,
1621 'N' -- inspection_required_flag
1622 ,
1623 DECODE(line_location_rec.value_basis, -- receipt_required_flag
1624 'FIXED_PRICE', 'N',
1625 COALESCE(p_lines.it_receipt_req_flag_tbl(i),
1626 po_autocreate_params.g_vendor_receipt_req_flag,
1627 po_autocreate_params.g_sys.receiving_flag, 'N')) ,
1628 payitem_rcv_ctl_rec.days_early_receipt_allowed ,
1629 payitem_rcv_ctl_rec.days_late_receipt_allowed ,
1630 payitem_rcv_ctl_rec.enforce_ship_to_location_code ,
1631 line_location_rec.ship_to_organization_id ,
1632 COALESCE(p_lines.it_invoice_tolerance_tbl(i), po_autocreate_params.g_sys.invoice_close_tolerance, 100) ,
1633 DECODE(line_location_rec.payment_type,
1634 'MILESTONE', 0, COALESCE(p_lines.it_rcv_tolerance_tbl(i), po_autocreate_params.g_sys.receive_close_tolerance, 100)) ,
1635 DECODE(line_location_rec.shipment_type, -- acrrue_on_receipt_flag
1636 'PREPAYMENT', 'N',
1637 DECODE(COALESCE(p_lines.it_receipt_req_flag_tbl(i),
1638 p_lines.receipt_required_flag_tbl(i),
1639 po_autocreate_params.g_vendor_receipt_req_flag,
1640 po_autocreate_params.g_sys.receiving_flag, 'N'),
1641 'N', 'N',
1642 DECODE(po_autocreate_params.g_sys.expense_accrual_code,
1643 'PERIOD END', 'N', 'Y'))) ,
1644 payitem_rcv_ctl_rec.allow_substitute_receipts_flag ,
1645 payitem_rcv_ctl_rec.receiving_routing_id ,
1646 payitem_rcv_ctl_rec.qty_rcv_tolerance ,
1647 payitem_rcv_ctl_rec.qty_rcv_exception_code ,
1648 payitem_rcv_ctl_rec.receipt_days_exception_code ,
1649 NULL -- terms_id
1650 ,
1651 NULL -- ship_via_lookup_code
1652 ,
1653 NULL -- freight_terms_lookup_code
1654 ,
1655 NULL -- fob_lookup_code
1656 ,
1657 line_location_rec.unit_of_measure -- unit_meas_lookup_code
1658 ,
1659 line_location_rec.promised_date -- last_accept_date
1660 + payitem_rcv_ctl_rec.days_late_receipt_allowed ,
1661 po_autocreate_params.g_vendor_invoice_match_option ,
1662 l_country_of_origin_code ,
1663 NULL -- vmi_flag
1664 ,
1665 NULL -- drop_ship_flag
1666 ,
1667 NULL -- consigned_flag
1668 ,
1669 p_lines.txn_flow_header_id_tbl(i) ,
1670 po_autocreate_params.g_purchasing_ou_id ,
1671 NULL -- closed_for_receiving_date
1672 ,
1673 NULL -- closed_for_invoice_date
1674 ,
1675 line_location_rec.value_basis ,
1676 line_location_rec.matching_basis ,
1677 line_location_rec.payment_type ,
1678 line_location_rec.description ,
1679 line_location_rec.work_approver_id ,
1680 line_location_rec.bid_payment_id ,
1681 2 -- outsourced_assembly
1682 ,
1683 nvl2(po_autocreate_params.g_calculate_tax_flag, 'CREATE', NULL) --<eTax Integration R12>
1684 ,
1685 p_lines.clm_period_perf_end_date_tbl(i) ,
1686 p_lines.clm_period_perf_start_date_tbl(i),
1687 --CLM Phase4 Changes
1688 p_lines.clm_delivery_period_tbl(i),
1689 p_lines.clm_promise_period_tbl(i),
1690 p_lines.clm_pop_duration_tbl(i),
1691 p_lines.clm_delivery_period_uom_tbl(i),
1692 p_lines.clm_promise_period_uom_tbl(i),
1693 p_lines.clm_pop_duration_uom_tbl(i)
1694 )
1695 RETURNING line_location_id
1696 INTO l_line_loc_id;
1697
1698 d_progress := 220;
1699 IF(PO_LOG.d_stmt) THEN
1700 PO_LOG.stmt(d_module, d_progress, 'Inserted payitem.');
1701 PO_LOG.stmt(d_module, d_progress, 'l_line_loc_id', l_line_loc_id);
1702 END IF;
1703
1704 d_progress := 230;
1705
1706 -- create link between interface row and transaction row
1707 UPDATE po_line_locations_interface polli
1708 SET polli.line_location_id = l_line_loc_id
1709 WHERE polli.interface_line_location_id = line_location_rec.interface_line_location_id;
1710
1711 d_progress := 240;
1712 -- Bug9441838: Added variable to pass sequence current value
1713 SELECT PO_LINE_LOCATIONS_S.currval
1714 INTO x_line_location_id
1715 FROM sys.dual;
1716
1717 IF (PO_AUTOCREATE_PARAMS.g_document_subtype IN ('STANDARD','PLANNED','BLANKET')) THEN
1718 d_progress := 250;
1719 PO_AUTOCREATE_PVT.calculate_local(PO_AUTOCREATE_PARAMS.g_document_subtype, 'SHIPMENT', x_line_location_id);
1720 END IF;
1721
1722 d_progress := 260;
1723
1724 l_payitems_created := l_payitems_created + 1;
1725 x_line_loc_id_tbl.EXTEND;
1726 x_line_loc_id_tbl(l_payitems_created) := l_line_loc_id;
1727 l_payitem_tax_code_id_tbl.EXTEND;
1728 l_payitem_tax_code_id_tbl(l_payitems_created) := l_tax_code_id;
1729 p_lines.line_loc_id_tbl(i) := l_line_loc_id;
1730
1731 -- set x_line_location_id to id of first actual (STANDARD) payitem
1732 IF ((x_line_location_id IS NULL) AND (line_location_rec.shipment_type = 'STANDARD')) THEN
1733 x_line_location_id := l_line_loc_id;
1734 END IF;
1735
1736 END LOOP; -- poll_interface_cursor loop
1737
1738 CLOSE poll_interface_cursor;
1739
1740 d_progress := 300;
1741
1742 END IF;
1743 END LOOP;
1744
1745 IF (PO_LOG.d_proc) THEN
1746 PO_LOG.proc_end(d_module, 'x_line_loc_id_tbl', x_line_loc_id_tbl);
1747 PO_LOG.proc_end(d_module);
1748 END IF;
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 IF (PO_LOG.d_exc) THEN
1752 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1753 END IF;
1754 IF (poll_interface_cursor%ISOPEN) THEN
1755 CLOSE poll_interface_cursor;
1756 END IF;
1757
1758 --CLM Phase 2 changes : error handling
1759 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_LOC_PAYITEM_ERR',x_token1_value => sqlerrm);
1760
1761 PO_AUTOCREATE_PVT.wrapup();
1762 RAISE;
1763 END create_payitem_draft;
1764
1765 /* ============================================================================
1766 Name: create_pricebreak_draft
1767 Pre-reqs:
1768 PO Line has been created
1769 Modifies:
1770 PO_LINE_LOCATIONS_DRAFT_ALL
1771 Locks:
1772 None
1773 Function:
1774 Derives,deaults the Price Breaks info from available lines information and
1775 inserts/updates the PO_LINE_LOCATIONS_DRAFT_ALL.
1776 Parameters:
1777 p_lines IN OUT Derived Line data after lines processing
1778 Returns:
1779 None
1780 Testing:
1781 None
1782 Caller of the Procedure:
1783 PO_AUTOCREATE_MAINPROC_PVT.process_line_locations
1784 ==============================================================================*/
1785 PROCEDURE create_pricebreak_draft(p_lines IN OUT NOCOPY PO_AUTOCREATE_TYPES.lines_rec_type)
1786 IS
1787
1788 l_row_id VARCHAR2(18) := NULL;
1789 l_progress VARCHAR2(3) := '000';
1790 l_api_name VARCHAR2(30) := 'create_pricebreak_draft';
1791 unexpected_create_pb_err EXCEPTION;
1792 l_ship_org_id_line mtl_system_items.organization_id%type;
1793 l_ship_org_code VARCHAR2(3);
1794 l_ship_org_name VARCHAR2(60);
1795 l_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE; -- <Complex Work R12>
1796 l_outsourced_assembly NUMBER;
1797 x_line_location_id NUMBER;
1798 l_tax_attribute_update_code PO_LINE_LOCATIONS_ALL.tax_attribute_update_code%type; --<eTax Integration R12>
1799
1800 BEGIN
1801
1802 l_progress := '010';
1803
1804 FOR i IN 1.. p_lines.intf_line_id_tbl.Count
1805 LOOP
1806 -- un-initialize variables so that next run of loop will have correct values.
1807 l_value_basis := NULL;
1808 l_ship_org_id_line := NULL;
1809 l_ship_org_id_line := NULL;
1810 l_ship_org_code := NULL;
1811 l_ship_org_name := NULL;
1812 l_outsourced_assembly := 2;
1813
1814 IF p_lines.clm_info_flag_tbl(i)='N' AND p_lines.shipment_num_tbl(i) IS NOT NULL AND p_lines.shipment_type_tbl(i) = 'PRICE BREAK' THEN
1815 IF p_lines.item_id_tbl(i) IS NOT NULL THEN
1816 l_progress := '020';
1817 l_outsourced_assembly := po_core_s.get_outsourced_assembly(p_lines.item_id_tbl(i), p_lines.dest_organization_id_tbl(i));
1818 END IF;
1819
1820 -- Standard start of API savepoint
1821 SAVEPOINT create_price_break_pvt;
1822
1823 IF g_debug_stmt THEN
1824 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1825 END IF;
1826
1827 BEGIN
1828 l_progress := '030';
1829 SELECT po_line_locations_s.nextval INTO x_line_location_id FROM sys.dual;
1830
1831 EXCEPTION
1832 WHEN OTHERS THEN
1833 IF g_debug_unexp THEN
1834 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1835 END IF;
1836
1837 po_message_s.sql_error('Exception of create_price_break()', l_progress,SQLCODE);
1838 FND_MSG_PUB.Add;
1839 RAISE unexpected_create_pb_err;
1840 END;
1841
1842
1843 -- Check that price break ship_to_organization_id and ship_to_location_id match
1844 -- (if both are provided)
1845 IF (p_lines.line_ship_to_org_id_tbl(i) IS NOT NULL
1846 AND p_lines.line_ship_to_loc_id_tbl(i) IS NOT NULL
1847 AND po_autocreate_params.g_sys.sob_id IS NOT NULL) THEN
1848 l_progress := '050';
1849
1850 po_locations_s.get_loc_org(p_lines.line_ship_to_loc_id_tbl(i),
1851 po_autocreate_params.g_sys.sob_id,
1852 l_ship_org_id_line,
1853 l_ship_org_code,
1854 l_ship_org_name);
1855 -- if the orgs do match raise an error
1856
1857 IF (l_ship_org_id_line <> p_lines.line_ship_to_org_id_tbl(i)) THEN
1858 --Create an error code 4 for price break ship_to_loc and ship_to_org do not match
1859 po_autocreate_params.g_sourcing_errorcode := 3;
1860 END IF;
1861 END IF;
1862
1863 /*check ship_loc and ship_org */
1864 l_progress := '060';
1865 --Call the row handler for po_line_location9in file POXP1PSB.pls to insert the row
1866 -- <Complex Work R12 Start>
1867 -- Get value basis from line
1868 SELECT pol.order_type_lookup_code
1869 INTO l_value_basis
1870 FROM po_lines_draft_all pol
1871 WHERE pol.po_line_id = p_lines.po_line_id_tbl(i);
1872
1873 l_progress := '070';
1874 -- <Complex Work R12 End>
1875 --<eTax Integration R12>
1876 IF p_lines.Shipment_Type_tbl(i) IN ('STANDARD', 'PLANNED', 'BLANKET', 'SCHEDULED') THEN
1877 l_tax_attribute_update_code := 'CREATE';
1878 END IF;
1879
1880 BEGIN
1881 INSERT
1882 INTO po_line_locations_draft_all
1883 (
1884 draft_id,
1885 line_location_id,
1886 last_update_date,
1887 last_updated_by,
1888 po_header_id,
1889 po_line_id,
1890 last_update_login,
1891 creation_date,
1892 created_by,
1893 quantity,
1894 quantity_received,
1895 quantity_accepted,
1896 quantity_rejected,
1897 quantity_billed,
1898 quantity_cancelled,
1899 unit_meas_lookup_code,
1900 po_release_id,
1901 ship_to_location_id,
1902 ship_via_lookup_code,
1903 need_by_date,
1904 promised_date,
1905 last_accept_date,
1906 price_override,
1907 encumbered_flag,
1908 encumbered_date,
1909 fob_lookup_code,
1910 freight_terms_lookup_code,
1911 taxable_flag,
1912 calculate_tax_flag,
1913 from_header_id,
1914 from_line_id,
1915 from_line_location_id,
1916 start_date,
1917 end_date,
1918 lead_time,
1919 lead_time_unit,
1920 price_discount,
1921 terms_id,
1922 approved_flag,
1923 approved_date,
1924 closed_flag,
1925 cancel_flag,
1926 cancelled_by,
1927 cancel_date,
1928 cancel_reason,
1929 firm_status_lookup_code,
1930 attribute_category,
1931 attribute1,
1932 attribute2,
1933 attribute3,
1934 attribute4,
1935 attribute5,
1936 attribute6,
1937 attribute7,
1938 attribute8,
1939 attribute9,
1940 attribute10,
1941 attribute11,
1942 attribute12,
1943 attribute13,
1944 attribute14,
1945 attribute15,
1946 inspection_required_flag,
1947 receipt_required_flag,
1948 qty_rcv_tolerance,
1949 qty_rcv_exception_code,
1950 enforce_ship_to_location_code,
1951 allow_substitute_receipts_flag,
1952 days_early_receipt_allowed,
1953 days_late_receipt_allowed,
1954 receipt_days_exception_code,
1955 invoice_close_tolerance,
1956 receive_close_tolerance,
1957 ship_to_organization_id,
1958 shipment_num,
1959 source_shipment_id,
1960 shipment_type,
1961 closed_code,
1962 government_context,
1963 receiving_routing_id,
1964 accrue_on_receipt_flag,
1965 closed_reason,
1966 closed_date,
1967 closed_by,
1968 global_attribute_category,
1969 global_attribute1,
1970 global_attribute2,
1971 global_attribute3,
1972 global_attribute4,
1973 global_attribute5,
1974 global_attribute6,
1975 global_attribute7,
1976 global_attribute8,
1977 global_attribute9,
1978 global_attribute10,
1979 global_attribute11,
1980 global_attribute12,
1981 global_attribute13,
1982 global_attribute14,
1983 global_attribute15,
1984 global_attribute16,
1985 global_attribute17,
1986 global_attribute18,
1987 global_attribute19,
1988 global_attribute20,
1989 country_of_origin_code,
1990 match_option,
1991 note_to_receiver,
1992 secondary_unit_of_measure,
1993 secondary_quantity,
1994 preferred_grade,
1995 secondary_quantity_received,
1996 secondary_quantity_accepted,
1997 secondary_quantity_rejected,
1998 secondary_quantity_cancelled,
1999 consigned_flag,
2000 /* CONSIGNED FPI */
2001 amount, -- <SERVICES FPJ>
2002 transaction_flow_header_id, --< Shared Proc FPJ >
2003 manual_price_change_flag --< Manual Price Override FPJ >
2004 ,
2005 shipment_closed_date ,
2006 closed_for_receiving_date ,
2007 closed_for_invoice_date ,
2008 Org_Id -- <R12.MOAC>
2009 ,
2010 value_basis -- <Complex Work R12>
2011 ,
2012 matching_basis -- <Complex Work R12>
2013 ,
2014 outsourced_assembly --<SHIKYU R12>
2015 ,
2016 tax_attribute_update_code --<eTax Integration R12>
2017 ,
2018 clm_period_perf_end_date ,
2019 clm_period_perf_start_date,
2020 --CLM Phase4 Changes
2021 clm_delivery_period,
2022 clm_promise_period,
2023 clm_pop_duration,
2024 clm_delivery_period_uom,
2025 clm_promise_period_uom,
2026 clm_pop_duration_uom
2027 )
2028 VALUES
2029 (
2030 po_autocreate_params.g_draft_id,
2031 x_Line_Location_Id,
2032 p_lines.last_update_date_tbl(i),
2033 p_lines.last_updated_by_tbl(i),
2034 p_lines.Po_Header_Id_tbl(i),
2035 p_lines.po_Line_Id_tbl(i),
2036 p_lines.Last_Update_Login_tbl(i),
2037 p_lines.creation_Date_tbl(i),
2038 p_lines.created_By_tbl(i),
2039 p_lines.quantity_tbl(i),
2040 0, --quantity_received
2041 0, --Quantity_Accepted
2042 0, --Quantity_Rejected
2043 0, --Quantity_Billed
2044 0, --Quantity_Cancelled,
2045 p_lines.unit_of_measure_tbl(i), --unit of measure
2046 NULL, -- release_id
2047 p_lines.line_Ship_To_Loc_Id_tbl(i),
2048 po_autocreate_params.g_vendor_Ship_Via_Lookup_Code,
2049 NULL, --Need_By_Date
2050 NULL, --Promised_Date
2051 NULL, --Last_Accept_Date
2052 p_lines.unit_price_tbl(i), --Price_override
2053 'N', --Encumbered flag
2054 NULL, --Encumbered_Date
2055 NULL, --Fob_Lookup_Code
2056 NULL, --Freight_Terms_Lookup_Code
2057 'N', --Taxable_Flag
2058 NULL, --Calculate Tax Flag
2059 NULL, --X_From_Header_Id
2060 NULL, --X_From_Line_Id
2061 NULL, --X_From_Line_Location_Id
2062 p_lines.effective_date_tbl(i), --X_Start_Date
2063 p_lines.expiration_date_tbl(i), --X_End_Date
2064 NULL, --X_Lead_Time,
2065 NULL, --X_Lead_Time_Unit,
2066 p_lines.Price_Discount_tbl(i),
2067 p_lines.Terms_Id_tbl(i),
2068 NULL, --X_Approved_Flag,
2069 NULL, --X_Approved_Date,
2070 'N', --X_Closed_Flag,
2071 'N', --X_Cancel_Flag,
2072 NULL, --X_Cancelled_By,
2073 NULL, --X_Cancel_Date,
2074 NULL, --X_Cancel_Reason,
2075 'N', --X_Firm_Status_Lookup_Code,
2076 NULL, --X_Attribute_Category,
2077 NULL, --X_Attribute1,
2078 NULL, --X_Attribute2,
2079 NULL, --X_Attribute3,
2080 NULL, --X_Attribute4,
2081 NULL, --X_Attribute5,
2082 NULL, --X_Attribute6,
2083 NULL, --X_Attribute7,
2084 NULL, --X_Attribute8,
2085 NULL, --X_Attribute9,
2086 NULL, --X_Attribute10,
2087 NULL, --X_Attribute11,
2088 NULL, --X_Attribute12,
2089 NULL, --X_Attribute13,
2090 NULL, --X_Attribute14,
2091 NULL, --X_Attribute15,
2092 'N', --X_Inspection_Required_Flag,
2093 'N', --X_Receipt_Required_Flag,
2094 NULL, --X_Qty_Rcv_Tolerance,
2095 NULL, --X_Qty_Rcv_Exception_Code,
2096 'NONE', --X_Enforce_Ship_To_Location,
2097 NULL, --X_Allow_Substitute_Receipts,
2098 NULL, --X_Days_Early_Receipt_Allowed,
2099 NULL, --X_Days_Late_Receipt_Allowed,
2100 NULL, --X_Receipt_Days_Exception_Code,
2101 NULL, --X_Invoice_Close_Tolerance,
2102 NULL, --X_Receive_Close_Tolerance,
2103 p_lines.line_Ship_To_Org_Id_tbl(i),
2104 p_lines.Shipment_Num_tbl(i),
2105 NULL, --X_Source_Shipment_Id,
2106 p_lines.Shipment_Type_tbl(i),
2107 'OPEN', --X_Closed_Code,
2108 NULL, --X_Government_Context,
2109 NULL, --X_Receiving_Routing_Id,
2110 NULL, --X_Accrue_On_Receipt_Flag,
2111 NULL, --X_Closed_Reason,
2112 NULL, --X_Closed_Date,
2113 NULL, --X_Closed_By,
2114 NULL, --X_Global_Attribute_Category,
2115 NULL, --X_Global_Attribute1,
2116 NULL, --X_Global_Attribute2,
2117 NULL, --X_Global_Attribute3,
2118 NULL, --X_Global_Attribute4,
2119 NULL, --X_Global_Attribute5,
2120 NULL, --X_Global_Attribute6,
2121 NULL, --X_Global_Attribute7,
2122 NULL, --X_Global_Attribute8,
2123 NULL, --X_Global_Attribute9,
2124 NULL, --X_Global_Attribute10,
2125 NULL, --X_Global_Attribute11,
2126 NULL, --X_Global_Attribute12,
2127 NULL, --X_Global_Attribute13,
2128 NULL, --X_Global_Attribute14,
2129 NULL, --X_Global_Attribute15,
2130 NULL, --X_Global_Attribute16,
2131 NULL, --X_Global_Attribute17,
2132 NULL, --X_Global_Attribute18,
2133 NULL, --X_Global_Attribute19,
2134 NULL, --X_Global_Attribute20,
2135 NULL, --X_Country_of_Origin_Code,
2136 'P', --invoice option
2137 NULL, --X_note_to_receiver,
2138 NULL, --X_Secondary_Unit_Of_Measure,
2139 NULL, --X_Secondary_Quantity,
2140 NULL, --X_Preferred_Grade,
2141 NULL, --X_Secondary_Quantity_Received,
2142 NULL, --X_Secondary_Quantity_Accepted,
2143 NULL, --X_Secondary_Quantity_Rejected,
2144 NULL, --X_Secondary_Quantity_Cancelled,
2145 NULL, --X_Consigned_Flag -- <SERVICES FPJ>
2146 p_lines.amount_tbl(i), --X_Amount -- <SERVICES FPJ>
2147 NULL, -- p_transaction_flow_header_id
2148 NULL, -- p_manual_price_change_flag
2149 NULL, --shipment Closed Code
2150 NULL, -- closed for receiving Date
2151 NULL, -- closed for invoice date
2152 p_lines.org_id_tbl(i), -- <R12 MOAC>
2153 l_value_basis, -- <Complex Work R12>
2154 NULL, -- <Complex Work R12>: matching basis
2155 l_outsourced_assembly --<SHIKYU R12>
2156 ,
2157 l_tax_attribute_update_code ,
2158 p_lines.clm_period_perf_end_date_tbl(i) ,
2159 p_lines.clm_period_perf_start_date_tbl(i),
2160 --CLM Phase4 Changes
2161 p_lines.clm_delivery_period_tbl(i),
2162 p_lines.clm_promise_period_tbl(i),
2163 p_lines.clm_pop_duration_tbl(i),
2164 p_lines.clm_delivery_period_uom_tbl(i),
2165 p_lines.clm_promise_period_uom_tbl(i),
2166 p_lines.clm_pop_duration_uom_tbl(i)
2167 );
2168
2169 l_progress := '080';
2170 -- <SERVICES FPJ START> Insert Price Break Price Differentials into
2171 -- main table from the interface table.
2172 --
2173 PO_PRICE_DIFFERENTIALS_PVT.create_from_interface
2174 (
2175 p_entity_id => x_line_location_id , p_interface_line_id => p_lines.intf_line_id_tbl(i)
2176 );
2177
2178 EXCEPTION
2179 WHEN OTHERS THEN
2180 IF g_debug_unexp THEN
2181 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2182 END IF;
2183
2184 po_message_s.sql_error('Exception of create_price_break()', l_progress, SQLCODE);
2185 FND_MSG_PUB.Add;
2186 RAISE unexpected_create_pb_err;
2187 END;
2188 END IF; -- <SERVICES FPJ END>
2189 END LOOP;
2190
2191 IF g_debug_stmt THEN
2192 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2193 END IF;
2194
2195 EXCEPTION
2196 WHEN unexpected_create_pb_err THEN
2197 IF g_debug_unexp THEN
2198 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2199 END IF;
2200
2201 --CLM Phase 2 changes : error handling
2202 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_LOC_PB_ERR',x_token1_value => sqlerrm);
2203
2204 PO_AUTOCREATE_PVT.wrapup;
2205 RAISE;
2206 --ROLLBACK to create_price_break_pvt;
2207
2208 WHEN OTHERS THEN
2209 IF g_debug_unexp THEN
2210 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2211 END IF;
2212
2213 --ROLLBACK to create_price_break_pvt;
2214 --CLM Phase 2 changes : error handling
2215 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_LOC_PB_ERR',x_token1_value => sqlerrm);
2216
2217 PO_AUTOCREATE_PVT.wrapup;
2218 RAISE;
2219 END create_pricebreak_draft;
2220
2221 /* ============================================================================
2222 Name: update_req_lines
2223 Pre-reqs:
2224 PO shipment has been created
2225 Modifies:
2226 PO_REQUISITION_LINES_ALL
2227 Locks:
2228 None
2229 Function:
2230 Updates the Requistion lines' Line Location Id, Reqs in Pool flag for autocreated lines.
2231 Parameters:
2232 p_lines IN Derived Line data after lines processing
2233 p_lines.requistion_line_id_tbl
2234 p_lines.line_location_id_tbl are used.
2235 Returns:
2236 None
2237 Testing:
2238 None
2239 Caller of the Procedure:
2240 PO_AUTOCREATE_MAINPROC_PVT.process_line_locations
2241 ==============================================================================*/
2242 PROCEDURE update_req_lines(p_lines IN PO_AUTOCREATE_TYPES.lines_rec_type)
2243 IS
2244 l_progress VARCHAR2(3) := '000';
2245 l_api_name VARCHAR2(30) := 'update_req_lines';
2246 l_bid_number NUMBER;
2247
2248 TYPE number_table IS TABLE OF po_requisition_lines_all.requisition_line_id%TYPE index by binary_integer;
2249
2250 l_req_line_ids_tbl number_table;
2251 l_return_status VARCHAR2(1);
2252 l_error_msg VARCHAR2(1000);
2253 l_count NUMBER;
2254 l_requisition_header_id NUMBER;
2255
2256 BEGIN
2257 IF g_debug_stmt THEN
2258 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2259 END IF;
2260
2261 l_progress := '010';
2262
2263 --Updation of backing req and stamping of line location id.
2264 -- we need to stamp the requisition_lines table with the
2265 -- line_loc_id of the shipment and the po_line_id of the po line
2266 -- which will help in finding req link to PO for Info lines.
2267
2268 FOR i IN 1..p_lines.requisition_line_id_tbl.Count
2269 LOOP
2270 /* Bug : 13695551 : From Sourcing, when one req line is awarded to multiple suppliers, populate
2271 * po_line_id, line_location_id to -1.
2272 */
2273 UPDATE po_requisition_lines_all l
2274 SET
2275 -- po_line_id = p_lines.po_line_id_tbl(i)),
2276 po_line_id = Decode(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING',
2277 Decode(Nvl(po_line_id, -999),-999, p_lines.po_line_id_tbl(i),-1), p_lines.po_line_id_tbl(i)),
2278 --line_location_id = p_lines.line_loc_id_tbl(i),
2279 line_location_id = Decode(PO_AUTOCREATE_PARAMS.g_interface_source_code,'SOURCING',
2280 Decode(Nvl(line_location_id, -999),-999, p_lines.line_loc_id_tbl(i),-1), p_lines.line_loc_id_tbl(i)),
2281 -- Bug: 13948625, update linked_po_count, should be equal to number of req dist if autocreated.
2282 -- Updating count to 1 for Info lines(Funded and NonFunded) and Option lines.
2283 -- Updating count to number of distributions for Priced lines
2284 linked_po_count = Decode((SELECT Count(1) FROM po_req_distributions_all d
2285 WHERE d.requisition_line_id = l.requisition_line_id
2286 AND d.info_line_id IS NULL),
2287 0,1,
2288 (SELECT Count(1) FROM po_req_distributions_all d WHERE d.requisition_line_id = l.requisition_line_id
2289 AND d.info_line_id IS NULL)),
2290 --<CONSUME REQ DEMAND PFI START>
2291 reqs_in_pool_flag = 'N',
2292 --<CONSUME REQ DEMAND PFI END>
2293 last_update_date = p_lines.last_update_date_tbl(i),
2294 last_updated_by = p_lines.last_updated_by_tbl(i),
2295 last_update_login = p_lines.last_update_login_tbl(i)
2296 WHERE requisition_line_id = p_lines.requisition_line_id_tbl(i);
2297
2298 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
2299 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2300 p_message => 'Upadte the req lines with '||p_lines.line_loc_id_tbl(i));
2301 END IF;
2302 -- added for Linking requisition to modification project
2303 IF (po_autocreate_params.g_is_clm_po ='Y' AND po_autocreate_params.g_process_code = 'ADD_FUNDS') THEN
2304
2305 l_progress := '020';
2306 UPDATE po_requisition_lines_all
2307 SET po_draft_id = po_autocreate_params.g_draft_id,
2308 LINKED_TO_FUND = 'Y'
2309 WHERE requisition_line_id = p_lines.requisition_line_id_tbl(i);
2310
2311 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
2312 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2313 p_message => 'Mod: Upadte the req lines with '||p_lines.po_line_id_tbl(i));
2314 END IF;
2315
2316 END IF;
2317 --For getting bid number to pull reqs out of poool.
2318 IF l_bid_number IS NULL THEN
2319 l_bid_number := p_lines.bid_number_tbl(i);
2320 END IF;
2321
2322 END LOOP;
2323
2324 --Line Type and Structure Changes
2325 --Update the po_requisition_lines_all for all the linked prs when creating the PO from Sourcing
2326
2327 l_progress := '030';
2328
2329 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
2330 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2331 p_message => 'l_bid_number: ' || l_bid_number);
2332 END IF;
2333
2334
2335 UPDATE po_requisition_lines_all prl
2336 SET PO_LINE_ID = -1,
2337 LINE_LOCATION_ID = -1,
2338 PO_DRAFT_ID = NULL,
2339 LINKED_PO_COUNT = Nvl(LINKED_PO_COUNT,0) + (SELECT Count(*) FROM pon_award_allocations paa1
2340 WHERE paa1.bid_number = l_bid_number
2341 AND paa1.IS_LINKED_PR_LINE_YN = 'Y'
2342 AND prl.REQUISITION_LINE_ID = paa1.ORIG_REQ_LINE_ID),
2343 last_update_date = SYSDATE,
2344 last_updated_by = FND_GLOBAL.USER_ID,
2345 last_update_login = FND_GLOBAL.LOGIN_ID
2346 WHERE prl.REQUISITION_LINE_ID IN (SELECT paa.ORIG_REQ_LINE_ID
2347 FROM pon_award_allocations paa
2348 WHERE paa.bid_number = l_bid_number
2349 AND paa.IS_LINKED_PR_LINE_YN = 'Y')
2350 returning REQUISITION_LINE_ID BULK COLLECT INTO l_req_line_ids_tbl;
2351
2352 l_count := SQL%ROWCOUNT;
2353
2354 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
2355 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2356 p_message => 'Updated the req lines with Po Line Ref. l_count: ' || l_count);
2357 END IF;
2358
2359 IF l_count > 0 THEN
2360 FOR i in 1..l_req_line_ids_tbl.COUNT LOOP
2361
2362 SELECT requisition_header_id INTO l_requisition_header_id
2363 FROM po_requisition_lines_all
2364 WHERE requisition_line_id = l_req_line_ids_tbl(i);
2365
2366 l_progress := '040';
2367
2368 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
2369 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2370 p_message => 'Before update statment l_count: row i is : ' || i || ' header id : ' || l_requisition_header_id);
2371 END IF;
2372
2373 UPDATE po_requisition_lines_all
2374 SET reqs_in_pool_flag = null,
2375 last_update_date = SYSDATE,
2376 last_updated_by = FND_GLOBAL.USER_ID,
2377 last_update_login = FND_GLOBAL.LOGIN_ID
2378
2379 WHERE requisition_header_id = l_requisition_header_id
2380 AND Nvl(reqs_in_pool_flag, 'N') = 'Y'
2381 AND ((requisition_line_id = l_req_line_ids_tbl(i))
2382 OR (group_line_id = l_req_line_ids_tbl(i))
2383 OR (requisition_line_id IN (SELECT group_line_id from po_requisition_lines_all
2384 WHERE requisition_line_id = l_req_line_ids_tbl(i)
2385 AND requisition_header_id = l_requisition_header_id))
2386 OR (group_line_id IN (SELECT group_line_id from po_requisition_lines_all
2387 WHERE requisition_line_id = l_req_line_ids_tbl(i)
2388 AND requisition_header_id = l_requisition_header_id))
2389 OR (clm_base_line_num = l_req_line_ids_tbl(i))
2390 OR (clm_base_line_num IN (SELECT group_line_id from po_requisition_lines_all
2391 WHERE requisition_line_id = l_req_line_ids_tbl(i)
2392 AND requisition_header_id = l_requisition_header_id))
2393 OR (group_line_id IN (SELECT requisition_line_id FROM po_requisition_lines_all
2394 WHERE clm_base_line_num = l_req_line_ids_tbl(i)
2395 AND requisition_header_id = l_requisition_header_id))
2396 OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
2397 WHERE clm_base_line_num = l_req_line_ids_tbl(i)
2398 AND requisition_header_id = l_requisition_header_id))
2399 OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
2400 WHERE clm_base_line_num = l_req_line_ids_tbl(i)
2401 AND requisition_header_id = l_requisition_header_id))
2402 OR (clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
2403 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
2404 WHERE requisition_line_id = l_req_line_ids_tbl(i)
2405 AND requisition_header_id = l_requisition_header_id )
2406 AND requisition_header_id = l_requisition_header_id))
2407 OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
2408 WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
2409 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
2410 WHERE requisition_line_id = l_req_line_ids_tbl(i)
2411 AND requisition_header_id = l_requisition_header_id)
2412 AND requisition_header_id = l_requisition_header_id)
2413 AND requisition_header_id = l_requisition_header_id))
2414 OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
2415 WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
2416 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
2417 WHERE requisition_line_id = l_req_line_ids_tbl(i)
2418 AND requisition_header_id = l_requisition_header_id)
2419 AND requisition_header_id = l_requisition_header_id)
2420 AND requisition_header_id = l_requisition_header_id)));
2421
2422
2423 END LOOP;
2424 END IF;
2425
2426 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
2427 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2428 p_message => 'Update the req in pool');
2429 END IF;
2430
2431 IF g_debug_stmt THEN
2432 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2433 END IF;
2434 EXCEPTION
2435 WHEN OTHERS THEN
2436 IF g_debug_unexp THEN
2437 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2438 END IF;
2439
2440 --CLM Phase 2 changes : error handling
2441 PO_AUTOCREATE_PVT.report_error('PO_AUTO_UPD_REQ_LINE_ERR',x_token1_value => sqlerrm);
2442
2443 PO_AUTOCREATE_PVT.wrapup;
2444 raise;
2445 END update_req_lines;
2446 END PO_AUTO_LINE_LOC_PROCESS_PVT;