1 PACKAGE BODY po_autocreate_mainproc_pvt AS
2 /* $Header: PO_AUTOCREATE_MAINPROC_PVT.plb 120.12.12020000.4 2013/02/22 11:27:44 amalick ship $ */
3
4 /* -------------------------------------------------------
5 ---------------- PRIVATE PROCEDURES -------------------
6 ------------------------------------------------------- */
7
8 /* ============================================================================
9 NAME: process_headers
10 DESC: Handle the logic to derive, default, validate records from
11 po_headers_interface table and insert records into
12 po_headers_draft_all table.
13 PO_AUTO_HEADER_PROCESS_PVT.fetch_headers
14 PO_AUTO_HEADER_PROCESS_PVT.derive_and_default_headers
15 PO_AUTO_HEADER_PROCESS_PVT.validate_headers
16 PO_AUTO_HEADER_PROCESS_PVT.merge_to_headers_draft
17 PO_AUTO_HEADER_PROCESS_PVT.merge_to_org_assign_draft
18 When the autocreate processing starts we could be in any of the following
19 scenarios.
20 I. The process will create a new document.
21 II. The process will add lines to an already exisitng document.
22 The document might not have been approved yet.
23 III. The process might add lines lines to an already approved document.
24 There might not be any draft.
25 IV. The process might add lines lines to an already approved document.
26 There might be a draft already existing.
27 ============================================================================== */
28 g_pkg_name CONSTANT VARCHAR2(1000) := 'po_autocreate_mainproc_pvt';
29 g_log_head CONSTANT VARCHAR2(1000) := 'po.plsql.PO_AUTOCREATE_MAINPROC_PVT.';
30 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
31 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
32 x_lines PO_AUTOCREATE_TYPES.lines_rec_type;
33
34 --<Bug 16308668>
35 PROCEDURE sync_complex_pricing(p_po_line_id_tbl IN PO_TBL_NUMBER,
36 p_style_id IN NUMBER);
37
38 PROCEDURE sync_uda(p_po_line_id_tbl IN PO_TBL_NUMBER DEFAULT NULL,
39 p_line_location_id_tbl IN PO_TBL_NUMBER DEFAULT NULL,
40 p_level IN VARCHAR2,
41 p_style_id IN NUMBER);
42
43 /* ============================================================================
44 NAME: merge_to_drafts
45 DESC: Transfer the matched transaction lines to drafts table after grouping
46 ARGS: None
47 ============================================================================ */
48 PROCEDURE merge_to_drafts
49 IS
50 l_api_name VARCHAR2(30) := 'merge_to_drafts';
51 l_progress VARCHAR2(3) := '000';
52 po_line_id_tbl PO_TBL_NUMBER;
53 po_line_loc_id_tbl PO_TBL_NUMBER;
54 draft_id_tbl PO_TBL_NUMBER;
55 delete_flag_tbl PO_TBL_VARCHAR1;
56 x_record_already_exist_tbl PO_TBL_VARCHAR1;
57 po_dist_id_tbl PO_TBL_NUMBER;
58 l_style_id NUMBER;
59 BEGIN
60
61 IF g_debug_stmt THEN
62 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
63 END IF;
64
65 l_progress := '010';
66 /* Get the matching lines and transfer the required records
67 * to draft table So that the derive_and_defaults works fine.
68 * Not Exists clause is not required in the following SQL
69 * as procedure po_lines_draft_pkg.sync_draft_from_txn has
70 * the required check. But still we check to be on safe side.
71 */
72 SELECT pla.po_line_id ,
73 po_autocreate_params.g_draft_id ,
74 'N' -- Need to check with Vinod.
75 BULK COLLECT
76 INTO po_line_id_tbl ,
77 draft_id_tbl ,
78 delete_flag_tbl
79 FROM po_lines_interface pli ,
80 po_lines_all pla ,
81 po_headers_interface phi
82 WHERE PLI.interface_header_id = po_autocreate_params.g_interface_header_id
83 AND phi.interface_header_id = PLI.interface_header_id
84 AND PLA.po_header_id = PhI.po_header_id
85 AND PLI.line_num = PLA.line_num
86 AND NOT EXISTS
87 (SELECT 'Y'
88 FROM po_lines_draft_all pld
89 WHERE pld.draft_id = po_autocreate_params.g_draft_id
90 AND pld.po_line_id = PLA.po_line_id
91 );
92
93 --<Bug 16308668>
94 SELECT style_id INTO l_style_id
95 FROM po_headers_all
96 WHERE po_header_id = po_autocreate_params.g_po_header_id;
97
98 IF g_debug_stmt THEN
99 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
100 p_token => l_progress,
101 p_message => 'Style Id : '||l_style_id);
102 END IF;
103
104 IF g_debug_stmt THEN
105 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'No of Matched Line Records : '||po_line_id_tbl.Count);
106 END IF;
107
108 l_progress := '020';
109 IF po_line_id_tbl.Count > 0 THEN
110 po_lines_draft_pkg.sync_draft_from_txn (p_po_line_id_tbl => po_line_id_tbl ,p_draft_id_tbl => draft_id_tbl ,p_delete_flag_tbl => delete_flag_tbl ,x_record_already_exist_tbl => x_record_already_exist_tbl );
111 --<Bug 16308668>
112 sync_complex_pricing(po_line_id_tbl, l_style_id);
113 sync_uda(p_po_line_id_tbl => po_line_id_tbl,
114 p_level => 'LINE',
115 p_style_id => l_style_id);
116 END IF;
117
118 IF g_debug_stmt THEN
119 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'After Sync Line Draft From Transaction');
120 END IF;
121
122 l_progress := '030';
123 SELECT plla.line_location_id ,
124 po_autocreate_params.g_draft_id ,
125 'N' -- Need to check with Vinod.
126 BULK COLLECT
127 INTO po_line_loc_id_tbl ,
128 draft_id_tbl ,
129 delete_flag_tbl
130 FROM TABLE(po_line_id_tbl) pl ,
131 po_line_locations_all plla ,
132 po_lines_interface PLI ,
133 po_headers_interface phi
134 WHERE PLI.interface_header_id = po_autocreate_params.g_interface_header_id
135 AND phi.interface_header_id = PLI.interface_header_id
136 AND plla.po_header_id = PhI.po_header_id
137 AND PLI.shipment_num = PLlA.shipment_num
138 AND plla.po_line_id = pl.column_value
139 AND NOT EXISTS
140 (SELECT 'Y'
141 FROM po_line_locations_draft_all plld
142 WHERE plld.draft_id = po_autocreate_params.g_draft_id
143 AND plld.line_location_id = plla.line_location_id
144 );
145
146 IF g_debug_stmt THEN
147 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'No of Matched Shipment Records : '||po_line_loc_id_tbl.Count );
148 END IF;
149
150 l_progress := '040';
151 PO_LINE_LOCATIONS_DRAFT_PKG.sync_draft_from_txn (p_line_location_id_tbl => po_line_loc_id_tbl ,
152 p_draft_id_tbl => draft_id_tbl ,
153 p_delete_flag_tbl => delete_flag_tbl ,
154 x_record_already_exist_tbl => x_record_already_exist_tbl );
155 --<Bug 16308668>
156 IF po_line_loc_id_tbl.Count > 0 THEN
157 sync_uda(p_line_location_id_tbl => po_line_loc_id_tbl,
158 p_level => 'SHIPMENT',
159 p_style_id => l_style_id);
160 END IF;
161
162 IF g_debug_stmt THEN
163 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
164 p_message => 'After Sync Line Locations Draft From Transaction');
165 END IF;
166
167 /*CLM Phase-2 Changes*/
168 --Syncing Distributions Draft From Transaction
169 l_progress := '050';
170
171 IF g_debug_stmt THEN
172 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
173 p_message => 'Before Sync Distributions Draft From Transaction');
174 END IF;
175
176 SELECT pod.po_distribution_id ,
177 po_autocreate_params.g_draft_id ,
178 'N' -- Need to check with Vinod.
179 BULK COLLECT
180 INTO po_dist_id_tbl ,
181 draft_id_tbl ,
182 delete_flag_tbl
183 FROM po_distributions_all pod,
184 TABLE(po_line_loc_id_tbl) pll
185 WHERE pod.line_location_id = pll.column_value
186 AND NOT EXISTS
187 (SELECT 'Y'
188 FROM po_distributions_draft_all podd
189 WHERE podd.draft_id = po_autocreate_params.g_draft_id
190 AND podd.po_distribution_id = pod.po_distribution_id
191 );
192
193 l_progress := '060';
194 PO_DISTRIBUTIONS_DRAFT_PKG.sync_draft_from_txn (p_po_distribution_id_tbl => po_dist_id_tbl,
195 p_draft_id_tbl => draft_id_tbl ,
196 p_delete_flag_tbl => delete_flag_tbl ,
197 x_record_already_exist_tbl => x_record_already_exist_tbl );
198
199 IF g_debug_stmt THEN
200 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
201 p_message => 'After Sync Distributions Draft From Transaction');
202 END IF;
203 /*CLM Phase-2 Ends*/
204
205 IF g_debug_stmt THEN
206 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
207 END IF;
208 EXCEPTION
209 WHEN OTHERS THEN
210 IF g_debug_unexp THEN
211 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
212 END IF;
213
214 --CLM Phase 2 changes : error handling
215 PO_AUTOCREATE_PVT.report_error('PO_AUTO_MERGE_TO_DRAFTS_ERR',x_token1_value => sqlerrm);
216
217 PO_AUTOCREATE_PVT.wrapup;
218 po_message_s.sql_error('MERGE TO DRAFTS',l_progress,SQLCODE);
219 RAISE;
220 END merge_to_drafts;
221
222 /* ============================================================================
223 NAME: GET_SHIPMENT_NUM
224 DESC: Get Shipment Number
225 ARGS: x_interface_header_id IN number
226 ALGR:
227 ==========================================================================*/
228 PROCEDURE get_shipment_num
229 (
230 x_need_by_date IN DATE,
231 x_deliver_to_location_id IN NUMBER,
232 x_destination_org_id IN NUMBER,
233 x_po_line_id IN NUMBER,
234 x_po_line_num IN NUMBER,
235 x_requisition_line_id IN NUMBER,
236 x_interface_header_id IN NUMBER,
237 x_po_shipment_num IN OUT NOCOPY NUMBER,
238 x_note_to_receiver IN VARCHAR2,
239 x_preferred_grade IN VARCHAR2,
240 x_vmi_flag IN VARCHAR2, -- VMI FPH
241 x_consigned_flag IN VARCHAR2, -- CONSIGNED FPI
242 x_drop_ship_flag IN VARCHAR2, -- <DropShip FPJ>
243 x_intf_receipt_req_flag IN VARCHAR2,
244 x_create_new_line OUT NOCOPY VARCHAR2 )
245 IS -- GA FPI
246 x_ship_to_location_id NUMBER;
247 x_receipt_required_flag VARCHAR2(1);
248 x_so_line_id NUMBER := '';
249 x_so_line_id_from_shipment NUMBER := '';
250 x_so_line_id_from_req_line NUMBER := '';
251 x_line_location_to_check NUMBER := '';
252 x_req_line_to_check NUMBER := '';
253 x_check_doc_sub_type VARCHAR2(25);
254 l_needby_prf VARCHAR2(1);
255 l_shipto_prf VARCHAR2(1);
256 l_api_name CONSTANT VARCHAR2(30) := 'get_shipment_num';
257 l_progress VARCHAR2(3) := '000';
258 l_po_line_id NUMBER;
259 l_item_receipt_required_flag VARCHAR2(1); -- item.receipt_required_flag,
260 l_intf_receipt_required_flag VARCHAR2(1) := x_intf_receipt_req_flag; --interface.receipt_required_flag
261 l_intf_txn_FLOW_HEADER_ID NUMBER; -- INTERFACE.TRANSACTION_FLOW_HEADER_ID
262 BEGIN
263
264 IF g_debug_stmt THEN
265 PO_DEBUG.debug_begin(p_log_head => g_log_head || l_api_name);
266 END IF;
267
268 /* Get the profile option values to determine grouping criteria */
269 l_needby_prf := fnd_profile.value('PO_NEED_BY_GROUPING');
270 l_shipto_prf := fnd_profile.value('PO_SHIPTO_GROUPING');
271
272 /* OE drop ship requirement
273 ** Do not consolidate any shipments linked to a sales order
274 ** Neither add them to existing shipments
275 ** or let other shipments add to them
276 */
277 l_progress := '010';
278 -- Do not group shipments if the req has a one-time location.
279 IF(has_one_time_location(x_requisition_line_id))THEN
280 x_po_shipment_num := -1;
281 IF PO_AUTOCREATE_PARAMS.g_document_type = 'PO' AND x_drop_ship_flag = 'Y' THEN
282 x_create_new_line := 'Y';
283 END IF;
284 IF g_debug_stmt THEN
285 PO_DEBUG.debug_stmt(p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'One-time location. Shipment num = -1');
286 END IF;
287 RETURN;
288 END IF;
289
290 x_ship_to_location_id := -1;
291 l_progress := '020';
292 x_ship_to_location_id := get_ship_to_loc(x_deliver_to_location_id); -- FPI
293 x_po_shipment_num := -1;
294
295 IF((PO_AUTOCREATE_PARAMS.g_document_subtype = 'STANDARD' OR PO_AUTOCREATE_PARAMS.g_document_subtype = 'PLANNED') AND PO_AUTOCREATE_PARAMS.g_document_type = 'PO')THEN
296 l_progress := '030';
297 /* Consigned FPI start : split the following select to determine if a new line
298 is to be created or just a new shipment */
299
300 IF x_po_line_id IS NOT NULL THEN
301 l_progress := '040';
302 BEGIN
303 -- SQL WHAT : compares the existing shipment values with the values from the req
304 -- SQL WHY : to create a new line if the need by is different based on the profile
305 SELECT PLL.shipment_num,
306 PLL.line_location_id
307 INTO x_po_shipment_num,
308 x_line_location_to_check
309 FROM PO_LINE_LOCATIONS_MERGE_V PLL --<Shared Proc FPJ>
310 WHERE PLL.PO_LINE_ID = x_po_line_id
311 AND pll.draft_id =PO_AUTOCREATE_PARAMS.g_draft_id
312 AND((TO_CHAR(PLL.need_by_date - (to_number(SUBSTR(TO_CHAR(PLL.need_by_date, 'DD-MM-YYYY HH24:MI:SS'), 18, 2)) / 86400), 'DD-MM-YYYY HH24:MI:SS') =
313 TO_CHAR(x_need_by_date - (to_number(SUBSTR(TO_CHAR(x_need_by_date, 'DD-MM-YYYY HH24:MI:SS'), 18, 2)) / 86400), 'DD-MM-YYYY HH24:MI:SS') )
314 OR (PLL.need_by_date IS NULL
315 AND x_need_by_date IS NULL) )
316 AND ROWNUM = 1;
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 IF g_debug_stmt THEN
320 PO_DEBUG.debug_stmt(p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: ' || SQLERRM);
321 END IF;
322 x_po_shipment_num := -1;
323 IF NVL(l_needby_prf, 'Y') = 'Y' THEN
324 x_create_new_line := 'Y';
325 END IF;
326 WHEN OTHERS THEN
327 IF g_debug_unexp THEN
328 PO_DEBUG.debug_exc(p_log_head => g_log_head || l_api_name, p_progress => l_progress);
329 END IF;
330 -- wrapup(x_interface_header_id);
331 RAISE;
332 END;
333
334 IF x_po_shipment_num <> -1 AND NVL(x_create_new_line, 'N') <> 'Y' THEN
335 l_progress := '050';
336 BEGIN
337 -- SQL WHAT : compares the existing shipment values with the values from the req
338 -- SQL WHY : to create a new line if ship to is different based on the value of the
339 -- profile
340 SELECT PLL.shipment_num,
341 PLL.line_location_id
342 INTO x_po_shipment_num,
343 x_line_location_to_check
344 FROM PO_LINE_LOCATIONS_MERGE_V PLL --<Shared Proc FPJ>
345 WHERE PLL.PO_LINE_ID = x_po_line_id
346 AND draft_id =PO_AUTOCREATE_PARAMS.g_draft_id
347 AND PLL.SHIP_TO_LOCATION_ID = x_ship_to_location_id
348 AND PLL.SHIP_TO_ORGANIZATION_ID = x_destination_org_id
349 AND ROWNUM = 1;
350 EXCEPTION
351 WHEN NO_DATA_FOUND THEN
352 IF g_debug_stmt THEN
353 PO_DEBUG.debug_stmt(p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: ' || SQLERRM);
354 END IF;
355 x_po_shipment_num := -1;
356 IF NVL(l_shipto_prf, 'Y') = 'Y' THEN
357 x_create_new_line := 'Y';
358 END IF;
359 WHEN OTHERS THEN
360 IF g_debug_unexp THEN
361 PO_DEBUG.debug_exc(p_log_head => g_log_head || l_api_name, p_progress => l_progress);
362 END IF;
363 --wrapup(x_interface_header_id);
364 RAISE;
365 END;
366 END IF;
367
368 IF x_po_shipment_num <> -1 AND NVL(x_create_new_line, 'N') <> 'Y' THEN
369 l_progress := '060';
370 BEGIN
371 -- SQL WHAT : compares the exixting shipment values with the values from the req
372 -- SQL WHY : to create a new line if its a drop ship line or consigned flag is
373 -- different
374 SELECT PLL.shipment_num,
375 PLL.line_location_id
376 INTO x_po_shipment_num,
377 x_line_location_to_check
378 FROM PO_LINE_LOCATIONS_MERGE_V PLL --<Shared Proc FPJ>
379 WHERE PLL.PO_LINE_ID = x_po_line_id
380 AND pll.draft_id =PO_AUTOCREATE_PARAMS.g_draft_id
381 AND NVL(PLL.drop_ship_flag, 'N') <> 'Y' --<DropShip FPJ> cannot add to Drop Ship Shipments
382 AND NVL(PLL.CONSIGNED_FLAG, 'N') = NVL(x_consigned_flag, 'N')
383 AND ROWNUM = 1;
384 EXCEPTION
385 WHEN NO_DATA_FOUND THEN
386 IF g_debug_stmt THEN
387 PO_DEBUG.debug_stmt(p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: ' || SQLERRM);
388 END IF;
389 x_po_shipment_num := -1;
390 x_create_new_line := 'Y';
391 WHEN OTHERS THEN
392 IF g_debug_unexp THEN
393 PO_DEBUG.debug_exc(p_log_head => g_log_head || l_api_name, p_progress => l_progress);
394 END IF;
395 --wrapup(x_interface_header_id);
396 RAISE;
397 END;
398 END IF;
399 END IF; -- end of po line id not null check
400
401 IF x_po_shipment_num <> -1 THEN
402 l_progress := '070';
403 BEGIN
404 -- SQL WHAT : compares the exixting shipment values with the values from the req
405 -- SQL WHY : if the above values match then we need to determine if we need to create
406 -- a new shipment or not
407 SELECT PLL.shipment_num
408 INTO x_po_shipment_num
409 FROM PO_LINE_LOCATIONS_MERGE_V PLL, --<Shared Proc FPJ>
410 PO_REQUISITION_LINES_ALL PRL, --<Shared Proc FPJ>
411 PO_SYSTEM_PARAMETERS_ALL PSP --<Shared Proc FPJ>
412 WHERE PLL.LINE_LOCATION_ID = x_line_location_to_check
413 AND pll.draft_id =PO_AUTOCREATE_PARAMS.g_draft_id
414 AND PRL.REQUISITION_LINE_ID = x_requisition_line_id
415 AND rtrim(NVL(PLL.note_to_receiver, '99')) = rtrim(NVL(x_note_to_receiver, '99'))
416 AND PLL.SHIPMENT_TYPE IN('STANDARD', 'SCHEDULED', 'BLANKET')
417 AND NVL(PLL.ENCUMBERED_FLAG, 'N') = 'N'
418 AND NVL(PLL.CANCEL_FLAG, 'N') = 'N'
419 AND NVL(psp.org_id, -99) = NVL(po_autocreate_params.g_purchasing_ou_id, -99)
420 AND PLL.ACCRUE_ON_RECEIPT_FLAG = DECODE(l_intf_txn_flow_header_id, NULL, --<Shared Proc FPJ>
421 DECODE(prl.destination_type_code,
422 'EXPENSE', DECODE(psp.expense_accrual_code,
423 'PERIOD END', 'N', DECODE(NVL(l_item_receipt_required_flag,
424 NVL(l_intf_receipt_required_flag,
425 NVL(PO_AUTOCREATE_PARAMS.g_vendor_receipt_req_flag,
426 NVL(PO_AUTOCREATE_PARAMS.g_sys.receiving_flag, 'N')))),
427 'N', 'N', 'Y')), 'Y'), 'Y') --<Shared Proc FPJ>
428 AND ( (PLL.PREFERRED_GRADE IS NULL
429 AND x_preferred_grade IS NULL)
430 OR (PLL.PREFERRED_GRADE = x_preferred_grade) )
431 AND NVL(PLL.VMI_FLAG, 'N') = NVL(x_vmi_flag, 'N') -- VMI
432 AND ROWNUM = 1;
433
434 EXCEPTION
435 WHEN NO_DATA_FOUND THEN
436 IF g_debug_stmt THEN
437 PO_DEBUG.debug_stmt(p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: ' || SQLERRM);
438 END IF;
439 x_po_shipment_num := -1;
440 WHEN OTHERS THEN
441 IF g_debug_unexp THEN
442 PO_DEBUG.debug_exc(p_log_head => g_log_head || l_api_name, p_progress => l_progress);
443 END IF;
444 --wrapup(x_interface_header_id);
445 RAISE;
446 END;
447
448 END IF;
449 /* Consigned FPI end */
450 /* OE Drop Ship
451 ** Make sure not to add to dropship related shipment.
452 */
453 IF x_po_shipment_num <> -1 THEN
454 l_progress := '080';
455 x_so_line_id_from_shipment := OE_DROP_SHIP_GRP.PO_Line_Location_Is_Drop_Ship(x_line_location_to_check);
456 IF x_so_line_id_from_shipment IS NOT NULL THEN
457 x_po_shipment_num := -1;
458 END IF;
459 END IF;
460 END IF;
461
462 IF(x_po_shipment_num = -1) AND (PO_AUTOCREATE_PARAMS.g_document_type = 'PO')THEN
463 /*
464 ** Get the receipt required flag that
465 ** will be inserted for the shipment.
466 */
467 l_progress := '130';
468 BEGIN
469 SELECT DECODE(PRL.destination_type_code,
470 'EXPENSE', DECODE(NVL(msi.receipt_required_flag,
471 NVL(plt.receiving_flag,
472 NVL(pov.receipt_required_flag,
473 NVL(psp.receiving_flag, 'N')))),
474 'N', 'N', DECODE(psp.expense_accrual_code,
475 'PERIOD END', 'N', 'Y')),
476 'INVENTORY', 'Y', 'SHOP FLOOR', 'Y')
477 INTO x_receipt_required_flag
478 FROM po_lines_interface pli,
479 po_headers_interface phi,
480 po_requisition_lines_all prl, --<Shared Proc FPJ>
481 mtl_system_items msi,
482 po_line_types plt,
483 po_vendors pov,
484 po_system_parameters_all psp, --<Shared Proc FPJ>
485 financials_system_params_all fsp --<Shared Proc FPJ>
486 WHERE 1 =1
487 AND pli.item_id = msi.inventory_item_id( + )
488 AND NVL(msi.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
489 --AND pli.line_type_id = plt.line_type_id
490 --CLM Phase-2 Autocreate Change
491 AND prl.line_type_id = plt.line_type_id
492 AND PLT.order_type_lookup_code NOT IN('RATE', 'FIXED PRICE')
493 AND phi.vendor_id = pov.vendor_id( + )
494 --AND phi.interface_header_id = pli.interface_header_id
495 --CLM Phase-2 Autocreate Change
496 AND phi.interface_header_id = po_autocreate_params.g_interface_header_id
497 AND pli.interface_header_id = phi.interface_header_id
498 AND prl.requisition_line_id = pli.requisition_line_id
499 AND pli.requisition_line_id = x_requisition_line_id
500 AND NVL(psp.org_id, -99) = NVL(po_autocreate_params.g_purchasing_ou_id, -99) --<Shared Proc FPJ>
501 AND NVL(fsp.org_id, -99) = NVL(po_autocreate_params.g_purchasing_ou_id, -99); --<Shared Proc FPJ>
502
503 EXCEPTION
504 WHEN NO_DATA_FOUND THEN
505 IF g_debug_stmt THEN
506 PO_DEBUG.debug_stmt(p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: ' || SQLERRM);
507 END IF;
508 WHEN OTHERS THEN
509 IF g_debug_unexp THEN
510 PO_DEBUG.debug_exc(p_log_head => g_log_head || l_api_name, p_progress => l_progress);
511 END IF;
512
513 RAISE;
514 END;
515
516 l_progress := '140';
517 IF(PO_AUTOCREATE_PARAMS.g_document_type = 'PO')THEN
518 BEGIN
519 /*
520 ** See if a record that has just been inserted into the
521 ** interface table matches the shipment you are trying to create.
522 */
523 SELECT PLI.shipment_num,
524 PLI.requisition_line_id
525 INTO x_po_shipment_num,
526 x_req_line_to_check
527 FROM PO_LINES_INTERFACE PLI,
528 PO_REQUISITION_LINES_ALL PRL, --<Shared Proc FPJ>
529 --bug 1942696 hr_location changes to reflect the new view
530 MTL_SYSTEM_ITEMS MSI,
531 PO_LINE_TYPES PLT,
532 PO_SYSTEM_PARAMETERS_ALL PSP, --<Shared Proc FPJ>
533 FINANCIALS_SYSTEM_PARAMS_ALL FSP, --<Shared Proc FPJ>
534 PO_VENDORS POV,
535 PO_HEADERS_INTERFACE PHI
536 WHERE PLI.LINE_NUM = x_po_line_num
537 AND PLI.shipment_num IS NOT NULL
538 AND NVL(psp.org_id,-99) = NVL(po_autocreate_params.g_purchasing_ou_id, -99) --<Shared Proc FPJ>
539 AND NVL(fsp.org_id,-99) = NVL(po_autocreate_params.g_purchasing_ou_id, -99) --<Shared Proc FPJ>
540 AND PLI.item_id = MSI.inventory_item_id( + )
541 AND NVL(MSI.organization_id, FSP.inventory_organization_id) = FSP.inventory_organization_id
542 AND PLI.line_type_id = PLT.line_type_id
543 AND PHI.vendor_id = POV.vendor_id( + )
544 AND PLI.interface_header_id = PHI.interface_header_id
545 AND PRL.REQUISITION_LINE_ID <> x_requisition_line_id
546 AND PRL.requisition_line_id = PLI.requisition_line_id
547 AND((TO_CHAR(PLI.need_by_date - (to_number(SUBSTR(TO_CHAR(PLI.need_by_date, 'DD-MM-YYYY HH24:MI:SS'), 18, 2)) / 86400), 'DD-MM-YYYY HH24:MI:SS') =
548 TO_CHAR(x_need_by_date - (to_number(SUBSTR(TO_CHAR(x_need_by_date, 'DD-MM-YYYY HH24:MI:SS'), 18, 2)) / 86400), 'DD-MM-YYYY HH24:MI:SS') )
549 OR (PLI.need_by_date IS NULL
550 AND x_need_by_date IS NULL) )
551 AND NVL(PLI.drop_ship_flag, 'N') <> 'Y' --<DropShip FPJ> cannot add to Drop Ship Shipments
552 AND rtrim(NVL(PLI.note_to_receiver, '99')) = rtrim(NVL(x_note_to_receiver, '99'))
553 AND EXISTS
554 (SELECT 'x'
555 FROM HR_LOCATIONS HRL
556 WHERE PRL.deliver_to_location_id = HRL.location_id
557 AND NVL(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
558
559 UNION ALL
560
561 SELECT 'x'
562 FROM HZ_LOCATIONS HZ
563 WHERE PRL.deliver_to_location_id = HZ.location_id
564 AND HZ.location_id = x_ship_to_location_id
565 )
566 AND PRL.destination_organization_id = x_destination_org_id
567 AND DECODE(PRL.destination_type_code,
568 'EXPENSE', DECODE(NVL(msi.receipt_required_flag,
569 NVL(plt.receiving_flag,
570 NVL(pov.receipt_required_flag,
571 NVL(psp.receiving_flag, 'N')))),
572 'N', 'N', DECODE(psp.expense_accrual_code,
573 'PERIOD END', 'N', 'Y')),
574 'INVENTORY', 'Y', 'SHOP FLOOR', 'Y') = x_receipt_required_flag
575 AND ( (PLI.PREFERRED_GRADE IS NULL
576 AND x_preferred_grade IS NULL)
577 OR (PLI.PREFERRED_GRADE= x_preferred_grade) )
578 AND NVL(PLI.VMI_FLAG, 'N') = NVL(x_vmi_flag, 'N') -- VMI FPH
579 AND NVL(PLI.CONSIGNED_FLAG, 'N') = NVL(x_consigned_flag, 'N') --CONSIGNED FPI
580 AND ROWNUM = 1;
581
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 IF g_debug_stmt THEN
585 PO_DEBUG.debug_stmt(p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: ' || SQLERRM);
586 END IF;
587 x_po_shipment_num := -1;
588
589 WHEN OTHERS THEN
590 IF g_debug_unexp THEN
591 PO_DEBUG.debug_exc(p_log_head => g_log_head || l_api_name, p_progress => l_progress);
592 END IF;
593 --wrapup(x_interface_header_id);
594 RAISE;
595 END;
596
597 /* Make sure that the potential shipment is related
598 ** to drop ship
599 */
600 IF x_po_shipment_num <> -1 THEN
601 l_progress := '150';
602 x_so_line_id_from_req_line := OE_DROP_SHIP_GRP.PO_Line_Location_Is_Drop_Ship(x_req_line_to_check);
603 IF x_so_line_id_from_req_line IS NOT NULL THEN
604 x_po_shipment_num := -1;
605 END IF;
606 END IF;
607 ELSE
608 /* not PO type */
609 l_progress := '160';
610 BEGIN
611 /*
612 ** See if a record that has just been inserted into the
613 ** interface table matches the shipment you are trying to create.
614 */
615 SELECT PLI.shipment_num
616 INTO x_po_shipment_num
617 FROM PO_LINES_INTERFACE PLI,
618 PO_REQUISITION_LINES_ALL PRL, --<Shared Proc FPJ>
619 MTL_SYSTEM_ITEMS MSI,
620 PO_LINE_TYPES PLT,
621 PO_SYSTEM_PARAMETERS_ALL PSP, --<Shared Proc FPJ>
622 FINANCIALS_SYSTEM_PARAMS_ALL FSP, --<Shared Proc FPJ>
623 PO_VENDORS POV,
624 PO_HEADERS_INTERFACE PHI
625 WHERE PLI.LINE_NUM = x_po_line_num
626 AND PLI.shipment_num IS NOT NULL
627 AND PLI.item_id = MSI.inventory_item_id
628 AND MSI.organization_id = FSP.inventory_organization_id
629 AND PLI.line_type_id = PLT.line_type_id
630 AND PHI.vendor_id = POV.vendor_id( + )
631 AND PLI.interface_header_id = PHI.interface_header_id
632 AND PRL.REQUISITION_LINE_ID = x_requisition_line_id
633 AND NVL(psp.org_id, -99) = NVL(po_autocreate_params.g_purchasing_ou_id, -99) --<Shared Proc FPJ>
634 AND NVL(fsp.org_id, -99) = NVL(po_autocreate_params.g_purchasing_ou_id, -99) --<Shared Proc FPJ>
635 --Bug 4599140 (included the following OR condition so that the SQL works correctly
636 --for null need by date)
637 AND((TO_CHAR(PLI.need_by_date - (to_number(SUBSTR(TO_CHAR(PLI.need_by_date, 'DD-MM-YYYY HH24:MI:SS'), 18, 2)) / 86400), 'DD-MM-YYYY HH24:MI:SS') =
638 TO_CHAR(x_need_by_date - (to_number(SUBSTR(TO_CHAR(x_need_by_date, 'DD-MM-YYYY HH24:MI:SS'), 18, 2)) / 86400), 'DD-MM-YYYY HH24:MI:SS') )
639 OR (PLI.need_by_date IS NULL
640 AND x_need_by_date IS NULL) )
641 AND NVL(PLI.drop_ship_flag, 'N') <> 'Y' --<DropShip FPJ> cannot add to Drop Ship Shipments
642 AND rtrim(NVL(PLI.note_to_receiver, '99')) = rtrim(NVL(x_note_to_receiver, '99'))
643 AND EXISTS
644 (SELECT 'x'
645 FROM HR_LOCATIONS HRL
646 WHERE PRL.deliver_to_location_id = HRL.location_id
647 AND NVL(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
648
649 UNION ALL
650
651 SELECT 'x'
652 FROM HZ_LOCATIONS HZ
653 WHERE PRL.deliver_to_location_id = HZ.location_id
654 AND HZ.location_id = x_ship_to_location_id
655 )
656 AND ( (PLI.PREFERRED_GRADE IS NULL
657 AND x_preferred_grade IS NULL)
658 OR (PLI.PREFERRED_GRADE = x_preferred_grade) )
659 AND ROWNUM = 1
660 ORDER BY shipment_num;
661
662 EXCEPTION
663 WHEN NO_DATA_FOUND THEN
664 IF g_debug_stmt THEN
665 PO_DEBUG.debug_stmt(p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: ' || SQLERRM);
666 END IF;
667 x_po_shipment_num := -1;
668
669 WHEN OTHERS THEN
670 IF g_debug_unexp THEN
671 PO_DEBUG.debug_exc(p_log_head => g_log_head || l_api_name, p_progress => l_progress);
672 END IF;
673 RAISE;
674 END;
675 END IF;
676 --end of change
677 END IF; -- end of grouping
678
679 IF PO_AUTOCREATE_PARAMS.g_document_type = 'PO' AND x_drop_ship_flag = 'Y' THEN
680 x_po_shipment_num := -1;
681 END IF;
682
683 IF g_debug_stmt THEN
684 PO_DEBUG.debug_end(p_log_head => g_log_head || l_api_name);
685 END IF;
686
687 EXCEPTION
688
689 WHEN NO_DATA_FOUND THEN
690 IF g_debug_stmt THEN
691 PO_DEBUG.debug_stmt(p_log_head => g_log_head || l_api_name, p_token => l_progress || '_main', p_message => 'NO_DATA_FOUND: ' || SQLERRM);
692 END IF;
693
694 WHEN OTHERS THEN
695 IF g_debug_unexp THEN
696 PO_DEBUG.debug_exc(p_log_head => g_log_head || l_api_name, p_progress => l_progress);
697 END IF;
698
699 --CLM Phase 2 changes : error handling
700 PO_AUTOCREATE_PVT.report_error('PO_AUTO_GET_SHIP_NUM_ERR',x_token1_value => sqlerrm);
701
702 po_message_s.sql_error('get_shipment_num', l_progress, SQLCODE);
703 RAISE;
704
705 END get_shipment_num;
706
707 PROCEDURE update_shipment
708 (
709 x_interface_header_id IN NUMBER,
710 x_po_shipment_num IN NUMBER,
711 x_po_line_num IN NUMBER,
712 x_requisition_line_id IN NUMBER,
713 x_po_line_id IN NUMBER,
714 x_document_num IN VARCHAR2,
715 x_release_num IN NUMBER,
716 x_create_new_line IN VARCHAR2,
717 x_row_id IN VARCHAR2 DEFAULT NULL )
718 IS
719 x_shipment_num NUMBER;
720 x_int_shipment_num NUMBER;
721 x_line_num NUMBER;
722 x_int_line_num NUMBER;
723 l_api_name CONSTANT VARCHAR2 (30) := 'update_shipment';
724 l_progress VARCHAR2 (3) := '000';
725 BEGIN
726
727 IF g_debug_stmt THEN
728 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
729 END IF;
730
731 /* Shipment Exists */
732 IF (x_po_shipment_num <> -1) THEN
733 /*
734 ** Shipment exists.
735 ** A shipment associated with the purchase order
736 ** line matches the requisition line information.
737 */
738 IF x_requisition_line_id IS NOT NULL THEN
739 l_progress := '010';
740 UPDATE po_lines_interface
741 SET shipment_num = x_po_shipment_num
742 WHERE interface_header_id = x_interface_header_id
743 AND requisition_line_id = x_requisition_line_id;
744 ELSE
745 l_progress := '015';
746 UPDATE po_lines_interface
747 SET shipment_num = x_po_shipment_num
748 WHERE interface_header_id = x_interface_header_id
749 AND ROWID = x_row_id;
750 END IF;
751
752 ELSE
753 /* Shipment does not exist */
754 /*
755 ** Get the maximum shipment number in the po tables
756 */
757 l_progress := '020';
758 IF ( PO_AUTOCREATE_PARAMS.g_document_subtype = 'STANDARD'
759 OR PO_AUTOCREATE_PARAMS.g_document_subtype = 'PLANNED'
760 OR PO_AUTOCREATE_PARAMS.g_document_type = 'RFQ' ) THEN
761 /* GA FPI start : if create new line parameter is 'Y' then we need to reset
762 the shipment number and create a new line with one shipment */
763 IF NVL (x_create_new_line, 'N') = 'Y' AND PO_AUTOCREATE_PARAMS.g_document_subtype = 'STANDARD' THEN
764 x_int_shipment_num := 0;
765 x_shipment_num := 0;
766 l_progress := '030';
767
768 SELECT NVL (MAX (line_num), 0)
769 INTO x_line_num
770 FROM po_headers_all ph, --<Shared Proc FPJ>
771 po_lines_all pl --<Shared Proc FPJ>
772 WHERE pl.po_header_id = ph.po_header_id
773 AND ph.segment1 = x_document_num
774 AND NVL (ph.org_id, -99) = NVL (po_autocreate_params.g_purchasing_ou_id, -99);
775 --<Shared Proc FPJ>
776
777 l_progress := '040';
778 SELECT NVL (MAX (line_num), 0)
779 INTO x_int_line_num
780 FROM po_lines_interface pli
781 WHERE pli.interface_header_id = x_interface_header_id;
782
783 IF (x_line_num >= x_int_line_num) THEN
784 x_line_num := x_line_num;
785 ELSE
786 x_line_num := x_int_line_num;
787 END IF;
788
789 l_progress := '050';
790 UPDATE po_lines_interface
791 SET line_num = x_line_num + 1
792 WHERE interface_header_id = x_interface_header_id
793 AND requisition_line_id = x_requisition_line_id;
794
795 ELSE
796
797 l_progress := '060';
798 SELECT NVL (MAX (shipment_num), 0)
799 INTO x_shipment_num
800 FROM po_line_locations_all poll --<Shared Proc FPJ>
801 WHERE poll.po_line_id = x_po_line_id
802 AND poll.shipment_type IN ('STANDARD', 'PLANNED', 'RFQ');
803
804 -- We now check to see if the line_num matches as well by
805 -- appending an AND condition in the WHERE clause.
806 l_progress := '070';
807 /*
808 ** Get the max shipment number already
809 ** assigned in the interface table.
810 */
811 SELECT NVL (MAX (shipment_num), 0)
812 INTO x_int_shipment_num
813 FROM po_lines_interface pli
814 WHERE pli.interface_header_id = x_interface_header_id
815 AND pli.line_num = x_po_line_num;
816 END IF; -- create new line
817
818 ELSE
819
820 l_progress := '080';
821 SELECT NVL (MAX (shipment_num), 0)
822 INTO x_shipment_num
823 FROM po_headers_all ph, --<Shared Proc FPJ>
824 po_line_locations_all poll, --<Shared Proc FPJ>
825 po_releases_all pr --<Shared Proc FPJ>
826 WHERE ph.po_header_id = poll.po_header_id
827 AND ph.segment1 = x_document_num
828 AND pr.po_header_id = ph.po_header_id
829 AND pr.release_num = x_release_num
830 AND ph.type_lookup_code = 'BLANKET'
831 AND poll.po_release_id = pr.po_release_id
832 AND NVL (pr.org_id, -99) = NVL (po_autocreate_params.g_purchasing_ou_id, -99)
833 --<Shared Proc FPJ>
834 AND NVL (ph.org_id, -99) = NVL (po_autocreate_params.g_purchasing_ou_id, -99);
835 --<Shared Proc FPJ>
836
837 l_progress := '090';
838 /*
839 ** Get the max shipment number already
840 ** assigned in the interface table.
841 */
842 SELECT NVL (MAX (shipment_num), 0)
843 INTO x_int_shipment_num
844 FROM po_lines_interface pli
845 WHERE pli.interface_header_id = x_interface_header_id;
846
847 END IF;
848
849 l_progress := '100';
850 IF (x_shipment_num >= x_int_shipment_num) THEN
851 x_shipment_num := x_shipment_num;
852 ELSE
853 x_shipment_num := x_int_shipment_num;
854 END IF;
855
856 IF x_requisition_line_id IS NOT NULL THEN
857
858 l_progress := '110';
859 UPDATE po_lines_interface
860 SET shipment_num = x_shipment_num + 1
861 WHERE interface_header_id = x_interface_header_id
862 AND requisition_line_id = x_requisition_line_id;
863
864 ELSE
865
866 l_progress := '120';
867 UPDATE po_lines_interface
868 SET shipment_num = x_shipment_num + 1
869 WHERE interface_header_id = x_interface_header_id
870 AND ROWID = x_row_id;
871
872 END IF;
873 END IF;
874
875 /* Shipment Exists */
876 IF g_debug_stmt THEN
877 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
878 END IF;
879
880 EXCEPTION
881 WHEN NO_DATA_FOUND THEN
882 IF g_debug_stmt THEN
883 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress || '_main', p_message => 'NO_DATA_FOUND: ' || SQLERRM );
884 END IF;
885 WHEN OTHERS THEN
886 IF g_debug_unexp THEN
887 po_debug.debug_exc (p_log_head => g_log_head || l_api_name, p_progress => l_progress );
888 END IF;
889 po_message_s.sql_error ('update_shipment', l_progress, SQLCODE);
890
891 --CLM Phase 2 changes : error handling
892 PO_AUTOCREATE_PVT.report_error('PO_AUTO_UPD_SHIP_NUM_ERR',x_token1_value => sqlerrm);
893
894 RAISE;
895 END update_shipment;
896
897 /**
898 * PUBLIC PROCEDURES
899 */
900
901 /* ============================================================================
902 NAME: process_headers
903 DESC: Handle the logic to derive, default, validate records from
904 po_headers_interface table and insert records into
905 po_headers_draft_all table.
906 PO_AUTO_HEADER_PROCESS_PVT.fetch_headers
907 PO_AUTO_HEADER_PROCESS_PVT.derive_and_default_headers
908 PO_AUTO_HEADER_PROCESS_PVT.validate_headers
909 PO_AUTO_HEADER_PROCESS_PVT.merge_to_headers_draft
910 PO_AUTO_HEADER_PROCESS_PVT.merge_to_org_assign_draft
911 When the autocreate processing starts we could be in any of the following
912 scenarios.
913 I. The process will create a new document.
914 II. The process will add lines to an already exisitng document.
915 The document might not have been approved yet.
916 III. The process might add lines lines to an already approved document.
917 There might not be any draft.
918 IV. The process might add lines lines to an already approved document.
919 There might be a draft already existing.
920 CALLER : PO_AUTOCREATE_MAINPROC_PVT.process
921 ============================================================================== */
922
923 PROCEDURE process_headers
924 IS
925
926 l_api_name VARCHAR2(30) := 'process_headers';
927 l_progress VARCHAR2(3) := '000';
928 x_headers PO_AUTOCREATE_TYPES.headers_rec_type;
929
930 BEGIN
931
932 IF g_debug_stmt THEN
933 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
934 END IF;
935
936 l_progress := '010';
937 PO_AUTO_HEADER_PROCESS_PVT.fetch_headers( p_interface_header_id => PO_AUTOCREATE_PARAMS.x_interface_header_id, x_headers => x_headers );
938
939 l_progress := '020';
940 PO_AUTO_HEADER_PROCESS_PVT.derive_and_default_headers(x_headers);
941
942 l_progress := '030';
943 PO_AUTO_HEADER_PROCESS_PVT.validate_header(x_headers);
944
945 l_progress := '040';
946 PO_AUTO_HEADER_PROCESS_PVT.merge_to_headers_draft(x_headers);
947
948 l_progress := '050';
949 PO_AUTO_HEADER_PROCESS_PVT.merge_to_org_assign_draft(x_headers);
950
951 l_progress := '060';
952
953 IF (PO_AUTOCREATE_PARAMS.g_document_subtype IN ('STANDARD','PLANNED','BLANKET')) THEN
954 PO_AUTOCREATE_PVT.calculate_local(PO_AUTOCREATE_PARAMS.g_document_subtype, 'HEADER', x_headers.po_header_id);
955 END IF;
956
957 IF g_debug_stmt THEN
958 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'End of Process');
959 END IF;
960
961 IF g_debug_stmt THEN
962 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
963 END IF;
964
965 EXCEPTION
966
967 WHEN OTHERS THEN
968 IF g_debug_unexp THEN
969 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
970 END IF;
971
972 --CLM Phase 2 error reporting
973 PO_AUTOCREATE_PVT.report_error('PO_AUTO_HEADER_ERR',x_token1_value => sqlerrm);
974
975 po_message_s.sql_error('PROCESS_HEADERS',l_progress,SQLCODE);
976 PO_AUTOCREATE_PVT.wrapup();
977 RAISE;
978 END process_headers;
979
980 /* ============================================================================
981 NAME: group_interface_records
982 DESC: Group the interface lines
983 RULES: When this procedure is called we might be in the following
984 scenario's
985 * Depending on whether the flow is called from Automatic Mode or
986 Manual Mode, the line number may or may not exist in the
987 interface. if it exists, it must be honored.
988 * The expected grouping could be REQUISITION meaning there is no
989 grouping at-all required OR it could be DEFAULT expecting the
990 defaulting grouping to take place.
991 * The document being created might be a NEW DOCUMENT in which case,
992 the interface lines should be grouped with itself. It could be
993 ADD TO DOCUMENT in which case the interface line must be grouped
994 with the main document AND also with the other interface lines.
995 * Grouping rules
996 For Line:
997 1. For Complex Purchase Order, do not group the lines
998 2. If the grouping is REQUISITION do not group the lines.
999 3. Group the req line with an EXISTING PO line base on the
1000 following attributes:
1001 - ITEM_ID
1002 - ITEM_DESCRIPTION
1003 - ITEM_REVISION
1004 - UNIT_MEAS_LOOKUP_CODE
1005 - PREFERRED_GRADE
1006 - FROM_HEADER_ID
1007 - FROM_LINE_ID
1008 - NEED_BY_DATE (IF "PO: Use Need-by Date for Default Autocreate Grouping" is set to Yes)
1009 - SHIP_TO_LOCATION (IF "PO: Use Ship-to for Default Autocreate Grouping" is set to Yes)
1010 - TRANSACTION_REASON_CODE
1011 - CONTRACT_ID
1012 - SUPPLIER_REF_NUMBER
1013 - VENDOR_PRODUCT_NUM
1014 - OKE_CONTRACT_HEADER_ID
1015 - OKE_CONTRACT_VERSION_ID
1016 - BID_NUMBER
1017 - BID_LINE_NUMBER
1018 4. Group the req line with other group lines in the INTERFACE
1019 table base on the following attributes:
1020 - ITEM_ID
1021 - ITEM_DESCRIPTION
1022 - ITEM_REVISION
1023 - UNIT_MEAS_LOOKUP_CODE
1024 - PREFERRED_GRADE
1025 - FROM_HEADER_ID
1026 - FROM_LINE_ID
1027 - NEED_BY_DATE (IF "PO: Use Need-by Date for Default Autocreate Grouping" is set to Yes)
1028 - SHIP_TO_LOCATION (IF "PO: Use Ship-to for Default Autocreate Grouping" is set to Yes)
1029 - CONSIGNED_FLAG *** (Possibly a bug ???)
1030 - TRANSACTION_REASON_CODE
1031 - CONTRACT_ID
1032 - SUPPLIER_REF_NUMBER
1033 - VENDOR_PRODUCT_NUM
1034 - OKE_CONTRACT_HEADER_ID
1035 - OKE_CONTRACT_VERSION_ID
1036 - BID_NUMBER
1037 - BID_LINE_NUMBER
1038 For Shipments:
1039 1. For Complex Purchase Order, do not group the pay items.
1040 2. Group shipments based on the following attribytes:
1041 - NEED_BY_DATE
1042 - SHIP_TO_LOCATION
1043 - CONSIGNED_FLAG
1044 If those values match, then we will create a new shipment
1045 based on the following attributes:
1046 - NOTE_TO_RECEIVER
1047 - SHIPMENT_TYPE
1048 - ENCUMBERED_FLAG
1049 - PREFERRED_GRADE
1050 - VMI_FLAG
1051 * After matching the lines the interface record is updated with the
1052 line number and the shipment number. Rest of the processing is
1053 deferred to the line processing and shipment processing routines.
1054 ALGM:
1055 IF the mode is 'ADD'
1056 Next_Line_Num := Get the latest line number from document + 1
1057 ELSE
1058 Next_Line_Num := 1;
1059 END IF;
1060 LOOP for all the lines interface records with LINE NUM as NULL
1061 IF mod is 'ADD'
1062 Try to match the interface record with document.
1063 IF matching line found
1064 LINE_NUM := matching line number
1065 END IF;
1066 END IF;
1067 IF the LINE_NUM is still null
1068 --This means there is no matching line found on the document
1069 --OR this is not an ADD TO case.
1070 Try to match the already processed interface records.
1071 IF matching line found
1072 LINE_NUM := matching interface line number
1073 ELSE
1074 LINE_NUM := Next_Line_Num;
1075 Next_Line_Num := Next_Line_Num+1;
1076 END IF;
1077 END IF;
1078 UPDATE the interface with the LINE_NUM;
1079 END LOOP
1080 * Matching logic should take care of all the rules mentioned above.
1081 Repeat the same steps for even shipments. But try match the
1082 shipments ONLY for the stamped LINE_NUM.
1083 ============================================================================== */
1084 PROCEDURE group_interface_records
1085 IS
1086
1087 l_progress VARCHAR2 (3) := '000';
1088 l_api_name VARCHAR2 (30) := 'group_interface_records';
1089 x_interface_header_id NUMBER := po_autocreate_params.g_interface_header_id;
1090 x_is_complex_work_po BOOLEAN := po_autocreate_params.g_is_complex_work_po;
1091 x_line_num NUMBER;
1092 x_shipment_num NUMBER;
1093 x_document_num VARCHAR2 (30);
1094 x_release_num NUMBER;
1095 x_document_type_code VARCHAR2 (25);
1096 x_document_subtype VARCHAR2 (25);
1097 x_group_code VARCHAR2 (30);
1098 x_action VARCHAR2 (25);
1099 x_mode VARCHAR2 (25);
1100 x_requisition_line_id NUMBER;
1101 x_interface_line_num NUMBER;
1102 x_item_id NUMBER;
1103 x_item_description VARCHAR2 (240);
1104 x_line_type_id NUMBER;
1105 x_item_revision VARCHAR2 (3);
1106 x_unit_meas_lookup_code VARCHAR2 (25);
1107 x_transaction_reason_code VARCHAR2 (25);
1108 x_need_by_date DATE;
1109 x_note_to_receiver po_requisition_lines_all.note_to_receiver%TYPE;
1110 x_oke_contract_header_id NUMBER;
1111 x_oke_contract_version_id NUMBER;
1112 x_vendor_product_num VARCHAR2 (30);
1113 x_deliver_to_location_id NUMBER;
1114 x_destination_org_id NUMBER;
1115 x_ship_to_location_id NUMBER;
1116 x_po_line_num NUMBER;
1117 x_po_line_id NUMBER;
1118 x_po_shipment_num NUMBER;
1119 x_num_interface_lines NUMBER := 1;
1120 x_int_shipment_num NUMBER;
1121 x_int_line_num NUMBER;
1122 x_secondary_unit_of_measure mtl_units_of_measure.unit_of_measure%TYPE;
1123 x_preferred_grade mtl_grades.grade_code%TYPE;
1124 x_count NUMBER := 0;
1125 --<SOURCING TO PO FPH START>
1126 x_bid_number NUMBER;
1127 x_bid_line_number NUMBER;
1128 x_row_id VARCHAR2 (25) := NULL;
1129 --<SOURCING TO PO FPH END>
1130 x_line_num_display VARCHAR2 (100);
1131 x_group_line_id NUMBER;
1132 x_clm_info_flag VARCHAR2 (1);
1133 x_clm_option_indicator VARCHAR2 (1);
1134 x_clm_option_num NUMBER;
1135 x_clm_option_from_date DATE;
1136 x_clm_option_to_date DATE;
1137 x_clm_funded_flag VARCHAR2 (1);
1138 x_clm_base_line_num NUMBER;
1139 l_int_line_num NUMBER;
1140 l_is_priced_slin VARCHAR2 (1);
1141 x_vmi_flag po_lines_interface.vmi_flag%TYPE;
1142 x_drop_ship_flag po_lines_interface.drop_ship_flag%TYPE;
1143 x_source_doc_id NUMBER;
1144 x_source_doc_line_id NUMBER;
1145 x_consigned_flag VARCHAR2 (1) := 'N';
1146 x_create_new_line VARCHAR2 (1) := 'N';
1147 l_supplier_ref_number po_lines_interface.supplier_ref_number%TYPE;
1148 l_contract_id po_lines_all.contract_id%TYPE;
1149 l_needby_prf VARCHAR2 (1);
1150 l_shipto_prf VARCHAR2 (1);
1151 x_contract_type VARCHAR2 (100);
1152 x_receipt_req_flag VARCHAR2(1);
1153 /*
1154 ** Order by interface_line_id.
1155 ** The front end will always load the lines in the correct order.
1156 ** The front end will load it either by (item_id, unit_price,
1157 ** need_by_date, requisition_line_id) or by the order in which
1158 ** the user selects.
1159 ** DEBUG. For now from the front end the users will not be able to
1160 ** determine the order in which they want to lines to be placed.
1161 ** removed order by interface_line_id and replaced it with the
1162 ** above order by.
1163 */
1164 CURSOR interface_lines_temp
1165 IS
1166 SELECT pli.requisition_line_id
1167 FROM po_lines_interface pli
1168 WHERE pli.interface_header_id = x_interface_header_id
1169 ORDER BY pli.requisition_line_id;
1170 CURSOR intf_sourcing_lines
1171 IS
1172 SELECT ROWID
1173 FROM po_lines_interface pli
1174 WHERE interface_header_id = po_autocreate_params.g_interface_header_id
1175 ORDER BY bid_line_number;
1176
1177 l_sourcing_temp_num NUMBER :=1;
1178 CURSOR interface_lines
1179 IS
1180 SELECT pli.action,
1181 pli.requisition_line_id,
1182 pli.line_num,
1183 pli.item_id,
1184 pli.item_description,
1185 pli.line_type_id,
1186 pli.item_revision,
1187 pli.unit_of_measure,
1188 pli.transaction_reason_code,
1189 pli.need_by_date,
1190 pli.note_to_receiver,
1191 pli.oke_contract_header_id,
1192 pli.oke_contract_version_id,
1193 pli.vendor_product_num,
1194 pld.deliver_to_location_id,
1195 pld.destination_organization_id,
1196 pli.secondary_unit_of_measure,
1197 pli.preferred_grade,
1198 pli.bid_number,
1199 pli.bid_line_number,
1200 pli.rowid,
1201 pli.vmi_flag,
1202 pli.drop_ship_flag,
1203 pli.from_header_id,
1204 pli.from_line_id,
1205 pli.consigned_flag,
1206 pli.contract_id,
1207 pli.supplier_ref_number ,
1208 pli.line_num_display ,
1209 pli.group_line_id ,
1210 pli.clm_info_flag ,
1211 pli.clm_option_indicator ,
1212 pli.clm_option_num ,
1213 pli.clm_option_from_date ,
1214 pli.clm_option_to_date ,
1215 pli.clm_funded_flag ,
1216 pli.clm_base_line_num ,
1217 PLI.contract_Type ,
1218 pli.receipt_required_flag
1219 FROM po_lines_interface pli,
1220 po_distributions_interface pld
1221 WHERE pli.interface_header_id =x_interface_header_id
1222 AND pli.interface_line_id =pld.interface_line_id
1223 AND pld.interface_distribution_id =
1224 (SELECT MIN(pdi2.interface_distribution_id)
1225 FROM po_distributions_interface pdi2
1226 WHERE pdi2.interface_line_id = pli.interface_line_id
1227 )
1228
1229 UNION
1230
1231 SELECT pli.action,
1232 pli.requisition_line_id,
1233 pli.line_num,
1234 pli.item_id,
1235 pli.item_description,
1236 pli.line_type_id,
1237 pli.item_revision,
1238 pli.unit_of_measure,
1239 pli.transaction_reason_code,
1240 pli.need_by_date,
1241 pli.note_to_receiver,
1242 pli.oke_contract_header_id,
1243 pli.oke_contract_version_id,
1244 pli.vendor_product_num,
1245 NULL,
1246 NULL,
1247 pli.secondary_unit_of_measure,
1248 pli.preferred_grade,
1249 pli.bid_number,
1250 pli.bid_line_number,
1251 pli.rowid,
1252 pli.vmi_flag,
1253 pli.drop_ship_flag,
1254 pli.from_header_id,
1255 pli.from_line_id,
1256 pli.consigned_flag,
1257 pli.contract_id,
1258 pli.supplier_ref_number ,
1259 pli.line_num_display ,
1260 pli.group_line_id ,
1261 pli.clm_info_flag ,
1262 pli.clm_option_indicator ,
1263 pli.clm_option_num ,
1264 pli.clm_option_from_date ,
1265 pli.clm_option_to_date ,
1266 pli.clm_funded_flag ,
1267 pli.clm_base_line_num ,
1268 PLI.contract_Type ,
1269 pli.receipt_required_flag
1270 FROM po_lines_interface pli
1271 WHERE pli.interface_header_id=x_interface_header_id
1272 AND NOT EXISTS
1273 (SELECT 'x'
1274 FROM po_distributions_interface pdi2
1275 WHERE pdi2.interface_line_id = pli.interface_line_id
1276 )
1277 ORDER BY 4, --pli.item_id,
1278 5, --pli.item_description,
1279 --pli.unit_price,
1280 10,--pli.need_by_date,
1281 2; --pli.requisition_line_id;
1282 --<INVCONV R12 START>
1283 l_grade_control_flag mtl_system_items.grade_control_flag%TYPE;
1284 l_line_grade mtl_grades.grade_code%TYPE;
1285 --<INVCONV R12 END>
1286 l_max_iface_line_num NUMBER; -- <Complex Work R12>
1287
1288 BEGIN
1289
1290 IF g_debug_stmt THEN
1291 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
1292 END IF;
1293
1294 l_needby_prf := fnd_profile.value('PO_NEED_BY_GROUPING');
1295 l_shipto_prf := fnd_profile.value('PO_SHIPTO_GROUPING');
1296
1297 IF g_debug_stmt THEN
1298 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
1299 p_message => 'PO: Use Need-by Date for Default Autocreate grouping : ' || l_needby_prf);
1300 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
1301 p_message => 'PO: Use Ship-to Organization and Location for Default Autocreate grouping : ' || l_shipto_prf);
1302 END IF;
1303
1304 -- Fetch the Document Type Code, Doc Sub type, groupe_code, and action
1305 SELECT phi.document_num,
1306 phi.document_type_code,
1307 phi.document_subtype,
1308 phi.group_code,
1309 phi.action
1310 INTO x_document_num,
1311 x_document_type_code,
1312 x_document_subtype,
1313 x_group_code,
1314 x_mode
1315 FROM po_headers_interface phi
1316 WHERE phi.interface_header_id = x_interface_header_id;
1317
1318 l_progress := '020';
1319 IF g_debug_stmt THEN
1320 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
1321 p_message => 'Mode is:' || PO_AUTOCREATE_PARAMS.g_mode ||' g_group_code '|| PO_AUTOCREATE_PARAMS.g_group_code );
1322 END IF;
1323
1324 IF (x_document_type_code IN ('PO', 'PA')) THEN
1325 IF (PO_AUTOCREATE_PARAMS.g_group_code = 'REQUISITION') THEN
1326 IF (PO_AUTOCREATE_PARAMS.g_mode = 'NEW') THEN
1327 -- Create a new PO with Req. lines in
1328 -- the same order as on the requisition.
1329 -- The interface table will hold the requisition line id
1330 -- that we need to get the req line number from.
1331 -- We need to update the shipment number to 1.
1332
1333 l_progress := '030';
1334 IF (x_document_subtype IN ('STANDARD', 'PLANNED')) THEN
1335 -- For Standard and Planned POs, the line number
1336 -- will be the same as the req line number if the profile
1337 -- is set to 'Y' otherwise use sequential numbers
1338 IF (fnd_profile.VALUE ('PO_USE_REQ_NUM_IN_AUTOCREATE') = 'Y' AND po_autocreate_params.g_interface_source_code <> 'SOURCING') THEN
1339
1340 -- use requisition numbers
1341 l_progress := '035';
1342 UPDATE po_lines_interface pli
1343 SET pli.line_num =
1344 (SELECT prl.line_num
1345 FROM po_requisition_lines_all prl
1346 WHERE prl.requisition_line_id = pli.requisition_line_id
1347 )
1348 WHERE pli.interface_header_id = x_interface_header_id
1349 AND pli.line_num IS NULL
1350 AND pli.shipment_num IS NULL;
1351
1352 l_progress := '036';
1353 UPDATE po_lines_interface pli
1354 SET pli.shipment_num = 1
1355 WHERE pli.interface_header_id = x_interface_header_id
1356 AND pli.shipment_num IS NULL
1357 AND NVL (pli.clm_info_flag, 'N') ='N'
1358 -- Shipments are only for Priced lines
1359 ;
1360 ELSE
1361 -- use sequential numbers
1362 l_progress := '040';
1363 IF po_autocreate_params.g_interface_source_code <> 'SOURCING' THEN
1364
1365 OPEN interface_lines_temp;
1366 LOOP
1367 x_count := x_count + 1;
1368 FETCH interface_lines_temp INTO x_requisition_line_id;
1369 EXIT
1370 WHEN interface_lines_temp%NOTFOUND;
1371
1372 l_progress := '050';
1373 UPDATE po_lines_interface pli
1374 SET pli.line_num = x_count
1375 WHERE pli.requisition_line_id = x_requisition_line_id
1376 AND pli.interface_header_id = x_interface_header_id
1377 AND pli.line_num IS NULL
1378 AND pli.shipment_num IS NULL;
1379
1380 l_progress := '051';
1381 UPDATE po_lines_interface pli
1382 SET pli.shipment_num = 1
1383 WHERE pli.requisition_line_id = x_requisition_line_id
1384 AND pli.interface_header_id = x_interface_header_id
1385 AND pli.shipment_num IS NULL
1386 AND NVL (pli.clm_info_flag, 'N') = 'N' -- Priced line
1387 ;
1388 -- Code is required to populate the LINE_NUM_DISPLAY column.
1389 -- Numbering is different for CLINS, INFO slins and Priced Slins
1390 END LOOP;
1391 CLOSE interface_lines_temp;
1392
1393 ELSE
1394 l_progress := '052';
1395 FOR l_rec IN intf_sourcing_lines
1396 LOOP
1397
1398 UPDATE po_lines_interface
1399 SET line_num = l_sourcing_temp_num
1400 WHERE rowid = l_rec.ROWID;
1401 UPDATE po_lines_interface
1402 SET shipment_num = 1
1403 WHERE rowid = l_rec.ROWID
1404 AND shipment_num IS NULL
1405 AND NVL (clm_info_flag, 'N') = 'N';
1406
1407 l_sourcing_temp_num := l_sourcing_temp_num + 1;
1408 END LOOP;
1409 END IF; -- po_autocreate_params.g_interface_source_code <> 'SOURCING
1410 END IF; -- if fnd_profile.value(PO_USE_REQ_NUM...) = 'Y'
1411 END IF; -- x_document_subtype IN ('STANDARD', 'PLANNED')
1412
1413 ELSE
1414 -- mode = 'ADD'
1415 -- add to a po with the same order as on the req.
1416 IF (x_document_subtype IN ('STANDARD', 'PLANNED')) THEN
1417 -- The inteface table will hold the requisition line id that we
1418 -- will use to get the line number. Select the maximum line number
1419 -- that exists on the purchase order. Update the line number in
1420 -- the interface talbe to be the req. line number + max po line num.
1421 -- Shipment num should be 1.
1422 l_progress := '120';
1423
1424 IF g_debug_stmt THEN
1425 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'Group_interface_lines: mode is :' || PO_AUTOCREATE_PARAMS.g_mode );
1426 END IF;
1427
1428 SELECT NVL (MAX (pl.line_num), 0)
1429 INTO x_line_num
1430 FROM po_headers_all ph,
1431 po_lines_merge_v pl
1432 WHERE pl.po_header_id = ph.po_header_id
1433 AND pl.draft_id = PO_AUTOCREATE_PARAMS.g_draft_id
1434 AND ph.segment1 = x_document_num
1435 AND ph.type_lookup_code = x_document_subtype
1436 AND NVL (ph.org_id, -99) = NVL (po_autocreate_params.g_purchasing_ou_id, -99);
1437
1438 l_progress := '130';
1439 OPEN interface_lines_temp;
1440 LOOP
1441 l_progress := '140';
1442 x_count := x_count + 1;
1443 FETCH interface_lines_temp INTO x_requisition_line_id;
1444 EXIT
1445 WHEN interface_lines_temp%NOTFOUND;
1446
1447 l_progress := '150';
1448 UPDATE po_lines_interface pli
1449 SET pli.line_num = x_line_num + x_count
1450 WHERE pli.requisition_line_id = x_requisition_line_id
1451 AND pli.interface_header_id = x_interface_header_id
1452 AND pli.line_num IS NULL
1453 AND pli.shipment_num IS NULL;
1454
1455 l_progress := '151';
1456 UPDATE po_lines_interface pli
1457 SET pli.shipment_num = 1
1458 WHERE pli.requisition_line_id = x_requisition_line_id
1459 AND pli.interface_header_id = x_interface_header_id
1460 AND pli.shipment_num IS NULL
1461 AND NVL (pli.clm_info_flag, 'N') = 'N'
1462 -- Shipments are for only Priced lines
1463 ;
1464 END LOOP;
1465 CLOSE interface_lines_temp;
1466 END IF;
1467 END IF; -- mode is NEW/ADD
1468
1469 ELSE
1470
1471 -- g_group_code = 'DEFAULT'
1472 IF (x_document_subtype IN ('STANDARD', 'PLANNED', 'BLANKET')) THEN
1473
1474 l_progress := '230';
1475 IF g_debug_stmt THEN
1476 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'Before open interface: Grouping is default' );
1477 END IF;
1478 -- <Complex Work R12 Start>
1479 -- Get maximum line number in interface table, to be used later
1480 SELECT NVL (MAX (pli.line_num), 0)
1481 INTO l_max_iface_line_num
1482 FROM po_lines_interface pli
1483 WHERE pli.interface_header_id = x_interface_header_id;
1484
1485 -- <Complex Work R12 End>
1486 OPEN interface_lines;
1487 LOOP
1488 l_progress := '240';
1489 x_po_line_id := NULL;
1490 x_po_line_num := NULL;
1491 l_int_line_num := NULL;
1492 l_is_priced_slin := NULL;
1493 FETCH interface_lines
1494 INTO x_action,
1495 x_requisition_line_id,
1496 x_interface_line_num,
1497 x_item_id,
1498 x_item_description,
1499 x_line_type_id,
1500 x_item_revision,
1501 x_unit_meas_lookup_code,
1502 x_transaction_reason_code,
1503 x_need_by_date,
1504 x_note_to_receiver,
1505 x_oke_contract_header_id,
1506 x_oke_contract_version_id,
1507 x_vendor_product_num,
1508 x_deliver_to_location_id,
1509 x_destination_org_id,
1510 x_secondary_unit_of_measure,
1511 x_preferred_grade,
1512 x_bid_number,
1513 x_bid_line_number,
1514 x_row_id,
1515 x_vmi_flag,
1516 -- VMI FPH
1517 x_drop_ship_flag, -- <DropShip FPJ>
1518 x_source_doc_id, -- FPI GA
1519 x_source_doc_line_id,
1520 -- FPI GA
1521 x_consigned_flag, -- CONSIGNED FPI
1522 l_contract_id, -- <GC FPJ>
1523 l_supplier_ref_number --<CONFIG_ID FPJ>
1524 ,
1525 x_line_num_display,
1526 x_group_line_id,
1527 x_clm_info_flag,
1528 x_clm_option_indicator,
1529 x_clm_option_num,
1530 x_clm_option_from_date,
1531 x_clm_option_to_date,
1532 x_clm_funded_flag,
1533 x_clm_base_line_num,
1534 x_contract_type,
1535 x_receipt_req_flag;
1536 -- Need to derive the following columns.
1537 -- x_deliver_to_location_id,
1538 -- x_destination_org_id,
1539 EXIT
1540 WHEN interface_lines%NOTFOUND;
1541
1542 -- <Complex Work R12 Start>: Do not group from sourcing
1543 IF (x_is_complex_work_po) THEN
1544 -- for complex work, we do not want to group lines.
1545 -- for complex PO's directly from requisitions, the group code
1546 -- will be set to REQUISITION and so lines will not be grouped.
1547 -- when coming from sourcing, however, the group type is set
1548 -- to DEFAULT.
1549 -- Do not group; simply add 1 to each successive interface line
1550 UPDATE po_lines_interface pli
1551 SET pli.line_num = l_max_iface_line_num + 1
1552 WHERE pli.ROWID = x_row_id
1553 AND pli.line_num IS NULL;
1554
1555 IF (SQL%ROWCOUNT > 0) THEN
1556 l_max_iface_line_num := l_max_iface_line_num + 1;
1557 END IF;
1558
1559 /*Bug 13588855: The shipment_num also needs to be stamped as 1
1560 for all priced lines in the po_lines_interface table
1561 */
1562
1563 UPDATE po_lines_interface pli
1564 SET pli.shipment_num = 1
1565 WHERE pli.interface_header_id = x_interface_header_id
1566 AND pli.shipment_num IS NULL
1567 AND NVL (pli.clm_info_flag, 'N') = 'N';
1568
1569 /*Bug 13588855 ends*/
1570 -- <Complex Work R12 End>
1571
1572 ELSIF (x_action = 'NEW') THEN
1573
1574 -- line number should be loaded into the interface. In general,
1575 -- the shipment number should be equal to 1. The only time it
1576 -- will not be is if the user attempts to place two or more
1577 -- req lines to the same po line and the shipments are identical.
1578 l_progress := '250';
1579
1580 IF g_debug_stmt THEN
1581 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'Before get_shipment_num' );
1582 END IF;
1583
1584 -- As info lines and option lines will not have any shipments
1585 -- set only line number.
1586 IF (x_clm_info_flag = 'Y') THEN
1587
1588 l_progress := '251';
1589 SELECT NVL (MAX (line_num), 0)
1590 INTO l_int_line_num
1591 FROM po_lines_interface pli
1592 WHERE pli.interface_header_id = x_interface_header_id;
1593
1594 l_progress := '252';
1595 UPDATE po_lines_interface
1596 SET line_num = l_int_line_num + 1
1597 WHERE interface_header_id = x_interface_header_id
1598 AND requisition_line_id = x_requisition_line_id
1599 AND line_num IS NULL; --Updating line nums only when they are null, else honor the line nums from interface table
1600
1601 END IF;
1602
1603 IF ( NVL (x_clm_info_flag, 'N') = 'N' ) THEN
1604
1605 l_progress := '253';
1606 get_shipment_num (x_need_by_date,
1607 x_deliver_to_location_id,
1608 x_destination_org_id,
1609 x_po_line_id,
1610 x_po_line_num,
1611 x_requisition_line_id,
1612 x_interface_header_id,
1613 x_po_shipment_num,
1614 x_note_to_receiver,
1615 x_preferred_grade,
1616 NULL, -- VMI FPH
1617 x_consigned_flag,
1618 x_drop_ship_flag,-- <DropShip FPJ>
1619 x_receipt_req_flag,
1620 x_create_new_line ); -- FPI GA
1621
1622 x_po_line_num := x_interface_line_num;
1623
1624 l_progress := '260';
1625 update_shipment (x_interface_header_id,
1626 x_po_shipment_num,
1627 x_po_line_num,
1628 x_requisition_line_id,
1629 x_po_line_id,
1630 x_document_num,
1631 x_release_num,
1632 x_create_new_line ); -- FPI GA
1633
1634 END IF;
1635
1636 ELSIF (x_action = 'ADD') THEN
1637
1638 -- user wants to add a requisition line to a particular PO line.
1639 -- check if a shipment exists that we can add to, otherwise
1640 -- get the next highest shipment number.
1641 l_progress := '270';
1642 IF g_debug_stmt THEN
1643 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'x_action = ' || x_action );
1644 END IF;
1645
1646 --We need to retrieve the po_line_id for the
1647 -- line the user picked in the manual build process.
1648 -- Otherwise we will not be able the shipments
1649 -- Added an AND condition to match_type_lookup_code
1650 -- Passing the value of line_num in x_po_line_num
1651 IF ( (x_interface_line_num IS NOT NULL) AND (x_document_num IS NOT NULL) ) THEN
1652 l_progress := '280';
1653
1654 BEGIN
1655 SELECT pol.po_line_id,
1656 pol.line_num
1657 INTO x_po_line_id,
1658 x_po_line_num
1659 FROM po_lines_merge_v pol,
1660 po_headers_all poh
1661 WHERE poh.segment1 = x_document_num
1662 AND pol.draft_id = PO_AUTOCREATE_PARAMS.g_draft_id
1663 AND pol.line_num = x_interface_line_num
1664 AND poh.type_lookup_code = x_document_subtype
1665 AND poh.po_header_id = pol.po_header_id
1666 AND NVL (poh.org_id, -99) = NVL (po_autocreate_params.g_purchasing_ou_id, -99);
1667
1668 --<Shared Proc FPJ>
1669 -- In the manual option when a Req. Line is added to the PO
1670 -- and PO has the same Item, the PO Line is matched and
1671 -- the line num is defaulted to the Po Line matched in the PO.
1672 -- If we change the line num defaulted and add a new line to
1673 -- the PO the shipment# was not populated when the PO was
1674 -- created. The is because we assume that the line_num
1675 -- populated will always exist in the PO, which is wrong.
1676 -- Handling the exception for NO_DATA_FOUND will resolve the
1677 -- Issue
1678 EXCEPTION
1679 WHEN NO_DATA_FOUND THEN
1680 IF g_debug_stmt THEN
1681 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: ' || SQLERRM );
1682 END IF;
1683 WHEN OTHERS THEN
1684 IF g_debug_unexp THEN
1685 po_debug.debug_exc (p_log_head => g_log_head || l_api_name, p_progress => l_progress );
1686 END IF;
1687
1688 --CLM Phase 2 changes : error handling
1689 PO_AUTOCREATE_PVT.report_error('PO_AUTO_MATCH_LINE_ERR',x_token1_value => sqlerrm);
1690
1691 RAISE;
1692 END;
1693 END IF;
1694
1695 -- if interface_line_num and x_document_num are not null
1696 l_progress := '290';
1697 IF ( NVL (x_clm_info_flag, 'N') = 'N' -- priced line
1698 ) THEN
1699 l_progress := '291';
1700 get_shipment_num (x_need_by_date,
1701 x_deliver_to_location_id,
1702 x_destination_org_id,
1703 x_po_line_id,
1704 x_po_line_num,
1705 x_requisition_line_id,
1706 x_interface_header_id,
1707 x_po_shipment_num,
1708 x_note_to_receiver,
1709 x_preferred_grade,
1710 NULL, -- VMI FPH
1711 x_consigned_flag,
1712 x_drop_ship_flag,-- <DropShip FPJ>
1713 x_receipt_req_flag,
1714 x_create_new_line );-- FPI GA
1715
1716 l_progress := '292';
1717 update_shipment (x_interface_header_id,
1718 x_po_shipment_num,
1719 x_po_line_num,
1720 x_requisition_line_id,
1721 x_po_line_id,
1722 x_document_num,
1723 x_release_num,
1724 x_create_new_line );
1725 --CLIN
1726 END IF;
1727 ELSE
1728 -- action = NULL (R10 logic)
1729 -- Check to see if this line matches another line already on PO
1730 -- If the line matches, update interface line num with PO line num
1731 -- If the shipment matches, update interface table w/ ship. num
1732 -- If the the shipment does not match, update table w/ number = 1
1733 -- If the line does not match, get the next highest line number
1734
1735 l_progress := '310';
1736
1737 IF g_debug_stmt THEN
1738 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'x_action = ' || x_action );
1739 END IF;
1740
1741 --<SOURCING TO PO FPH>: allow lines grouping for blankets also
1742 IF (PO_AUTOCREATE_PARAMS.g_document_type IN ('PO', 'PA')) THEN
1743 --<INVCONV R12 START>
1744 -- Item could have different grade control for FSP and ship to org.
1745 -- If (shipment)grade is present , compare the grade at line level
1746 -- only if item is grade control for the FSP org.
1747 IF x_preferred_grade IS NOT NULL THEN
1748
1749 BEGIN
1750 SELECT grade_control_flag
1751 INTO l_grade_control_flag
1752 FROM mtl_system_items
1753 WHERE inventory_item_id = x_item_id
1754 AND organization_id = po_autocreate_params.g_sys.def_inv_org_id;
1755
1756 EXCEPTION
1757 WHEN OTHERS THEN
1758 l_grade_control_flag := 'N';
1759 END;
1760
1761 IF l_grade_control_flag = 'Y' THEN
1762 l_line_grade := x_preferred_grade;
1763 ELSE
1764 l_line_grade := NULL;
1765 END IF;
1766
1767 ELSE
1768 l_line_grade := NULL;
1769 END IF; -- x_preferred_grade is not null
1770
1771 --<INVCONV R12 END>
1772 BEGIN
1773 l_progress := '320';
1774 -- SQL What: Querying for an existing line on the PO
1775 -- that matches the requisition line that we are trying to add.
1776 -- SQL Why: Want to group matching lines onto PO documents
1777 -- SQL Join: business logic for combining two lines
1778 -- Need to match only if the line is a priced normal clin
1779 SELECT line_num,
1780 po_line_id
1781 INTO x_po_line_num,
1782 x_po_line_id
1783 FROM po_lines_merge_v pol2,
1784 po_headers_all poh,
1785 po_line_types_b plt -- <SERVICES FPJ>
1786 WHERE poh.segment1 = x_document_num
1787 AND pol2.draft_id = PO_AUTOCREATE_PARAMS.g_draft_id
1788 AND poh.po_header_id = pol2.po_header_id
1789 AND NVL (poh.org_id, -99) = NVL (po_autocreate_params.g_purchasing_ou_id, -99)
1790 --<Shared Proc FPJ>
1791 AND poh.type_lookup_code = x_document_subtype
1792 -- <SERVICES FPJ START> Any new Service line types should
1793 -- cause the SELECT to fail (i.e. should not be matched).
1794 --
1795 AND pol2.line_type_id = plt.line_type_id
1796 AND plt.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')
1797 --
1798 -- <SERVICES FPJ END>
1799 AND NVL (pol2.clm_info_flag, 'N') = 'N'
1800 AND pol2.group_line_id IS NULL
1801 AND pol2.clm_base_line_num IS NULL
1802 AND pol2.line_num =
1803 (SELECT
1804 /*+ NO_UNNEST */
1805 MIN (line_num)
1806 FROM po_lines_all pol
1807 --<Shared Proc FPJ>
1808 WHERE pol.po_header_id = poh.po_header_id
1809 AND NVL (cancel_flag, 'N') = 'N'
1810 AND line_type_id = x_line_type_id
1811 AND NVL (x_clm_info_flag, 'N') = 'N'
1812 AND x_group_line_id IS NULL
1813 AND x_clm_base_line_num IS NULL
1814 AND NVL (pol.item_id, -1) = NVL (x_item_id, -1)
1815 -- bgu, For one timeitem
1816 AND NVL (pol.item_description, 'null' ) = NVL (x_item_description, 'null' )
1817 AND ( ( item_revision IS NULL
1818 AND x_item_revision IS NULL )
1819 OR item_revision = x_item_revision )
1820 AND unit_meas_lookup_code = x_unit_meas_lookup_code
1821 --<INVCONV R12 START>
1822 -- replace x_preferred_grade to l_line_grade and removed secondary unit comparison.
1823 AND ( ( pol.preferred_grade IS NULL
1824 AND l_line_grade IS NULL )
1825 OR (pol.preferred_grade = l_line_grade ) )
1826 --<INVCONV R12 END>
1827 AND
1828 /* FPI GA start */
1829 ( ( pol.from_header_id IS NULL
1830 AND x_source_doc_id IS NULL )
1831 OR (pol.from_header_id = x_source_doc_id ) )
1832 AND ( ( pol.from_line_id IS NULL
1833 AND x_source_doc_line_id IS NULL )
1834 OR (pol.from_line_id = x_source_doc_line_id ) )
1835 /* FPI GA end */
1836 AND ( transaction_reason_code IS NULL
1837 OR transaction_reason_code = NVL (x_transaction_reason_code, transaction_reason_code ) )
1838 AND TRUNC (NVL (pol.expiration_date, SYSDATE + 1 ) ) >= TRUNC (SYSDATE)
1839 AND NVL (pol.oke_contract_header_id, -1 ) = NVL (x_oke_contract_header_id, -1 )
1840 AND NVL (pol.oke_contract_version_id, -1 ) = NVL (x_oke_contract_version_id, -1 )
1841 AND NVL (pol.vendor_product_num, -1 ) = NVL (x_vendor_product_num, -1 )
1842 AND NVL (pol.bid_number, -1) = NVL (x_bid_number, -1)
1843 AND NVL (pol.bid_line_number, -1) = NVL (x_bid_line_number, -1)
1844 -- <GC FPJ START>
1845 AND ( ( pol.contract_id IS NULL
1846 AND l_contract_id IS NULL )
1847 OR (pol.contract_id = l_contract_id ) )
1848 -- <GC FPJ END>
1849 --<CONFIG_ID FPJ START>
1850 AND ( ( pol.supplier_ref_number IS NULL
1851 AND l_supplier_ref_number IS NULL )
1852 OR (pol.supplier_ref_number = l_supplier_ref_number ) )
1853 --<CONFIG_ID FPJ END>
1854 );
1855
1856 EXCEPTION
1857 WHEN NO_DATA_FOUND THEN
1858 IF g_debug_stmt THEN
1859 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
1860 p_message => 'NO_DATA_FOUND: No match to po line: Doc type = ' || PO_AUTOCREATE_PARAMS.g_document_type );
1861 END IF;
1862 x_po_line_num := -1;
1863 WHEN OTHERS THEN
1864 IF g_debug_unexp THEN
1865 po_debug.debug_exc (p_log_head => g_log_head || l_api_name, p_progress => l_progress );
1866 END IF;
1867 --wrapup(x_interface_header_id);
1868 --CLM Phase 2 changes : error handling
1869 PO_AUTOCREATE_PVT.report_error('PO_AUTO_MATCH_LINE_ERR',x_token1_value => sqlerrm);
1870
1871 RAISE;
1872 END;
1873 END IF; -- PO/PA
1874
1875 -- Check to see if there is a line in the interface table
1876 -- that matches the line we are attempting to add
1877 IF (x_po_line_num = -1) THEN
1878 --<SOURCING TO PO FPH>: allow lines grouping for blankets also
1879 IF (PO_AUTOCREATE_PARAMS.g_document_type IN ('PO', 'PA')) THEN
1880 l_progress := '340';
1881 BEGIN
1882 x_ship_to_location_id := get_ship_to_loc (x_deliver_to_location_id);
1883 l_progress := '350';
1884 -- SQL What: Querying for a requisition line in the
1885 -- interface table that matches the requisition line
1886 -- that we are trying to add.
1887 -- SQL Why: Want to group matching lines onto PO documents.
1888 -- SQL Join: business logic for combining two lines
1889 SELECT MIN (pli.line_num)
1890 INTO x_po_line_num
1891 FROM po_lines_interface pli,
1892 po_requisition_lines_all prl,
1893 po_line_types_b plt -- <SERVICES FPJ>
1894 WHERE pli.interface_header_id = x_interface_header_id
1895 AND pli.line_num IS NOT NULL
1896 AND prl.requisition_line_id <> x_requisition_line_id
1897 AND prl.requisition_line_id = pli.requisition_line_id
1898 AND pli.line_type_id = x_line_type_id
1899 -- <SERVICES FPJ START> Any new Service line types should
1900 -- cause the SELECT to fail (i.e. should not be matched).
1901 --
1902 AND pli.line_type_id = plt.line_type_id
1903 AND plt.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')
1904 --
1905 -- <SERVICES FPJ END>
1906 --autocreate grouping start...Need to match only priced CLINs. Not info CLINs, SLINs or option lines
1907 AND NVL (pli.clm_info_flag, 'N') ='N'
1908 AND pli.group_line_id IS NULL
1909 AND pli.clm_base_line_num IS NULL
1910 AND NVL (x_clm_info_flag, 'N') ='N'
1911 AND x_group_line_id IS NULL
1912 AND x_clm_base_line_num IS NULL
1913 --autocreate grouping end
1914 AND NVL (pli.item_id, -1) = NVL (x_item_id, -1)
1915 AND NVL (pli.item_description, 'null') = NVL (x_item_description, 'null')
1916 AND ( ( pli.item_revision IS NULL
1917 AND x_item_revision IS NULL )
1918 OR pli.item_revision = x_item_revision )
1919 AND pli.unit_of_measure = x_unit_meas_lookup_code
1920 --<INVCONV R12 START>
1921 -- replace x_preferred_grade to l_line_grade and
1922 -- removed secondary unit comparison.
1923 AND ( ( pli.preferred_grade IS NULL
1924 AND l_line_grade IS NULL )
1925 OR (pli.preferred_grade = l_line_grade) )
1926 --<INVCONV R12 END>
1927 -- FPI GA start
1928 AND ( ( pli.from_header_id IS NULL
1929 AND x_source_doc_id IS NULL )
1930 OR (pli.from_header_id = x_source_doc_id) )
1931 AND ( ( pli.from_line_id IS NULL
1932 AND x_source_doc_line_id IS NULL )
1933 OR (pli.from_line_id = x_source_doc_line_id ) )
1934 AND ( NVL (l_needby_prf, 'Y') = 'N'
1935 OR ( ( pli.need_by_date IS NULL
1936 AND x_need_by_date IS NULL )
1937 OR (TO_CHAR ( pli.need_by_date - ( TO_NUMBER (SUBSTR (TO_CHAR (pli.need_by_date, 'DD-MM-YYYY HH24:MI:SS' ), 18, 2 ) ) / 86400 ), 'DD-MM-YYYY HH24:MI:SS' ) =
1938 TO_CHAR ( x_need_by_date - ( TO_NUMBER (SUBSTR (TO_CHAR (x_need_by_date, 'DD-MM-YYYY HH24:MI:SS' ), 18, 2 ) ) / 86400 ), 'DD-MM-YYYY HH24:MI:SS' ) ) ) )
1939 AND ( NVL (l_shipto_prf, 'Y') = 'N'
1940 OR EXISTS
1941 (SELECT 'x'
1942 FROM hr_locations hrl
1943 WHERE prl.deliver_to_location_id = hrl.location_id
1944 AND NVL (hrl.ship_to_location_id, hrl.location_id ) = x_ship_to_location_id
1945
1946 UNION ALL
1947
1948 SELECT 'x'
1949 FROM hz_locations hz
1950 WHERE prl.deliver_to_location_id = hz.location_id
1951 AND hz.location_id = x_ship_to_location_id
1952 ) )
1953 AND ( NVL (l_shipto_prf, 'Y') = 'N'
1954 OR ( ( pli.ship_to_organization_id IS NULL
1955 AND x_destination_org_id IS NULL )
1956 OR (pli.ship_to_organization_id = x_destination_org_id ) ) )
1957 -- FPI GA end
1958 -- CONSIGNED FPI start
1959 AND ( ( pli.consigned_flag IS NULL
1960 AND x_consigned_flag IS NULL )
1961 OR (pli.consigned_flag = x_consigned_flag ) )
1962 -- CONSIGNED FPI End
1963 AND ( pli.transaction_reason_code IS NULL
1964 OR pli.transaction_reason_code = NVL (x_transaction_reason_code, pli.transaction_reason_code ) )
1965 AND NVL (pli.oke_contract_header_id, -1) = NVL (x_oke_contract_header_id, -1)
1966 AND NVL (pli.oke_contract_version_id, -1) = NVL (x_oke_contract_version_id, -1)
1967 AND NVL (pli.vendor_product_num, -1) = NVL (x_vendor_product_num, -1)
1968 AND NVL (pli.bid_number, -1) = NVL (x_bid_number, -1)
1969 AND NVL (pli.bid_line_number, -1) = NVL (x_bid_line_number, -1)
1970 /*autocreate grouping - Changed the condition from NVL (pli.orig_from_req_flag, 'Y') <> 'N'
1971 to NVL (pli.orig_from_req_flag, 'Y') = 'Y' so that the lines that are soft linked(pli.orig_from_req_flag = 'S') are excluded*/
1972 AND NVL (pli.orig_from_req_flag, 'Y') = 'Y'
1973 -- <GC FPJ START>
1974 AND ( ( pli.contract_id IS NULL
1975 AND l_contract_id IS NULL )
1976 OR (pli.contract_id = l_contract_id) )
1977 -- <GC FPJ END>
1978 --<CONFIG_ID FPJ START>
1979 AND ( ( pli.supplier_ref_number IS NULL
1980 AND l_supplier_ref_number IS NULL )
1981 OR (pli.supplier_ref_number = l_supplier_ref_number ) )
1982 --<CONFIG_ID FPJ END>
1983 ;
1984 EXCEPTION
1985 WHEN NO_DATA_FOUND THEN
1986 IF g_debug_stmt THEN
1987 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
1988 p_message => 'NO_DATA_FOUND: No match to po line in Interface- Doc type = ' || PO_AUTOCREATE_PARAMS.g_document_type );
1989 END IF;
1990 x_po_line_num := -1;
1991 WHEN OTHERS THEN
1992 IF g_debug_unexp THEN
1993 po_debug.debug_exc (p_log_head => g_log_head || l_api_name, p_progress => l_progress );
1994 END IF;
1995 --CLM Phase 2 changes : error handling
1996 PO_AUTOCREATE_PVT.report_error('PO_AUTO_MATCH_LINE_REQ_ERR',x_token1_value => sqlerrm);
1997 RAISE;
1998 END;
1999 END IF; -- PO/PA
2000 END IF; -- if x_po_line_num = -1 (interface table)
2001
2002 IF g_debug_stmt THEN
2003 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
2004 p_message => 'At group Interface Lines: x_po_line_num:'||x_po_line_num );
2005 END IF;
2006
2007 IF (x_po_line_num <> -1) THEN
2008 -- a line matches
2009 l_progress := '370';
2010 --<SOURCING TO PO FPH>
2011 -- We need to use get_shipment_num only for those negotiations
2012 -- with backing req.
2013 IF (x_requisition_line_id IS NOT NULL) THEN
2014 -- backing req line exists
2015 -- Since get_shipment_num will need it, we update the
2016 -- line number here.
2017 IF g_debug_stmt THEN
2018 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
2019 p_message => 'Before update line_num: Line Id:'||x_requisition_line_id||' Line Num:'||x_po_line_num );
2020 END IF;
2021
2022 UPDATE po_lines_interface pli
2023 SET pli.line_num = x_po_line_num
2024 WHERE pli.interface_header_id = x_interface_header_id
2025 AND pli.requisition_line_id = x_requisition_line_id;
2026
2027 l_progress := '380';
2028 -- if a shipment matches, get the shipment number
2029 get_shipment_num (x_need_by_date,
2030 x_deliver_to_location_id,
2031 x_destination_org_id,
2032 x_po_line_id,
2033 x_po_line_num,
2034 x_requisition_line_id,
2035 x_interface_header_id,
2036 x_po_shipment_num,
2037 x_note_to_receiver,
2038 x_preferred_grade,
2039 NULL, -- VMI FPH
2040 x_consigned_flag,
2041 x_drop_ship_flag,
2042 x_receipt_req_flag,
2043 x_create_new_line ); -- FPI GA
2044
2045 IF g_debug_stmt THEN
2046 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'Before update_shipment' );
2047 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
2048 p_message => 'x_create_new_line:'||x_create_new_line||
2049 'x_po_line_num:'||x_po_line_num||
2050 'x_po_shipment_num:'||x_po_shipment_num );
2051 END IF;
2052
2053 l_progress := '380';
2054 update_shipment (x_interface_header_id,
2055 x_po_shipment_num,
2056 x_po_line_num,
2057 x_requisition_line_id,
2058 x_po_line_id,
2059 x_document_num,
2060 x_release_num,
2061 x_create_new_line ); -- FPI GA
2062
2063 ELSE
2064 -- no backing requisition line
2065 --< SOURCING TO PO FPH >
2066 --Assign max line number+1 from interface table when not backed
2067 --by a req and the shipment num would be 1. There can't be two
2068 --similar negotiation lines not backed by a req, having the
2069 --same bid number and bid line number. If that happens we don't
2070 --group them to a single line. Also no need to select from
2071 --po_lines table as we are not supporting add to functionality.
2072
2073 l_progress := '400';
2074 UPDATE po_lines_interface pli2
2075 SET
2076 (
2077 pli2.line_num,
2078 pli2.shipment_num
2079 )
2080 =
2081 (SELECT (NVL (MAX (pli.line_num), 0) + 1),
2082 1
2083 FROM po_lines_interface pli
2084 WHERE pli.interface_header_id = x_interface_header_id
2085 )
2086 WHERE pli2.ROWID = x_row_id;
2087
2088 END IF;
2089
2090 ELSE
2091
2092 -- a matching line does not exist
2093 -- Get the max line number on the purchase order and
2094 -- update the interface line number with that number + 1.
2095 -- The shipment number should be 1.
2096
2097 l_progress := '410';
2098 IF g_debug_stmt THEN
2099 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
2100 p_message => 'Group_interface_lines: Line does not exist' );
2101 END IF;
2102
2103 SELECT NVL (MAX (pl.line_num), 0)
2104 INTO x_line_num
2105 FROM po_headers_all ph,
2106 po_lines_merge_v pl
2107 WHERE pl.po_header_id = ph.po_header_id
2108 AND pl.draft_id = PO_AUTOCREATE_PARAMS.g_draft_id
2109 AND ph.segment1 = x_document_num
2110 AND NVL (ph.org_id, -99) = NVL (po_autocreate_params.g_purchasing_ou_id, -99)
2111 AND ph.type_lookup_code = x_document_subtype;
2112
2113 -- Get the max line number already assigne in the interface table
2114 l_progress := '420';
2115 IF g_debug_stmt THEN
2116 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
2117 p_message => 'Before select max line_num from po_lines_interface' );
2118 END IF;
2119
2120 SELECT NVL (MAX (pli.line_num), 0)
2121 INTO l_int_line_num
2122 FROM po_lines_interface pli
2123 WHERE pli.interface_header_id = x_interface_header_id;
2124
2125 IF (x_line_num >= l_int_line_num) THEN
2126 x_line_num := x_line_num;
2127 ELSE
2128 x_line_num := l_int_line_num;
2129 END IF;
2130
2131 l_progress := '430';
2132
2133 --<SOURCING TO PO FPH>: when req line id is null, use x_row_id
2134 IF g_debug_stmt THEN
2135 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
2136 p_message => 'Sourcing to PO: Update Line Num : x_line_num : '|| x_line_num );
2137 END IF;
2138
2139 IF (x_requisition_line_id IS NOT NULL) THEN
2140 UPDATE po_lines_interface pli
2141 SET pli.line_num = x_line_num + 1
2142 WHERE pli.interface_header_id = x_interface_header_id
2143 AND pli.requisition_line_id = x_requisition_line_id;
2144 UPDATE po_lines_interface pli
2145 SET pli.shipment_num = 1
2146 WHERE pli.interface_header_id = x_interface_header_id
2147 AND pli.requisition_line_id = x_requisition_line_id
2148 AND NVL (pli.clm_info_flag, 'N') = 'N'
2149 -- Priced Line
2150 AND pli.clm_base_line_num IS NULL;
2151 -- Normal/Base Line
2152 ELSE
2153 -- no backing req line; use rowid
2154 UPDATE po_lines_interface pli
2155 SET pli.line_num = x_line_num + 1,
2156 pli.shipment_num = 1
2157 WHERE pli.ROWID = x_row_id;
2158 END IF; -- if x_requisition_id is not null
2159
2160 x_num_interface_lines := x_num_interface_lines + 1;
2161 END IF; -- matching line vs. no matching line
2162 END IF; -- Action Type Code
2163 END LOOP;
2164 CLOSE interface_lines;
2165 END IF; -- If STANDARD/PLANNED/RELEASE
2166 END IF; -- of same as REQUISITION mode
2167 END IF; -- of PO mode
2168
2169 IF g_debug_stmt THEN
2170 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
2171 END IF;
2172
2173 EXCEPTION
2174 WHEN NO_DATA_FOUND THEN
2175 IF g_debug_stmt THEN
2176 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'Exception block: NO_DATA_FOUND: ' || SQLERRM );
2177 END IF;
2178 NULL;
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 || '_main' );
2182 END IF;
2183
2184 --CLM Phase 2 changes : error handling
2185 PO_AUTOCREATE_PVT.report_error('PO_AUTO_GRP_RECORDS_ERR',x_token1_value => sqlerrm);
2186
2187 po_message_s.sql_error ('GROUP INTERFACE RECORDS', l_progress, SQLCODE);
2188 PO_AUTOCREATE_PVT.wrapup();
2189 RAISE;
2190 END group_interface_records;
2191
2192 /* ============================================================================
2193 NAME: process_lines
2194 DESC: Handle the logic to derive, default, validate records from po_lines_interface
2195 table and insert records into po_lines_draft_all table.
2196 PO_AUTO_LINE_PROCESS_PVT.fetch_lines
2197 PO_AUTO_LINE_PROCESS_PVT.derive_and_default_lines
2198 PO_AUTO_LINE_PROCESS_PVT.validate_lines (Is this required??)
2199 PO_AUTO_LINE_PROCESS_PVT.merge_to_lines_draft
2200 PO_AUTO_LINE_PROCESS_PVT.merge_to_price_diff_draft
2201 PO_AUTO_LINE_PROCESS_PVT.merge_to_attributes_draft
2202 (process attribute values and attribute tlp)
2203 PO_AUTO_LINE_PROCESS_PVT.hanlde_line_attachments
2204 EXMP: After the grouping the document being processed might in the
2205 following state. The Line number is followed by the quantity
2206 on that record.
2207 DOCUMENT DRAFT INTERFACE
2208 ======== ===== =========
2209 1 (10) .......1 (12) A - 1 (5)
2210 2 (10) B - 2 (5)
2211 3 (10) .......3 (13) C - 3 (5)
2212 4 (10) D - 2 (5)
2213 5 (10) E - 6 (5)
2214 F - 1 (5)
2215 G - 6 (5)
2216 - We are in ADD TO document flow.
2217 - The document is already approved document.
2218 - Each of the line in the document has 10 qty.
2219 - A Draft is created for the document already. The Line #1 qty is changed
2220 to 12 and the Line #3 qty is changed to 13.
2221 - The interface has six requisition lines 5 qty each.
2222 - The Line A and F matches to the existing document line 1 (which is in draft)
2223 - The Line B and D matches to the existing document line 2 (which is NOT in draft)
2224 - The Line C matches to the existing document line 3 (which is in draft)
2225 - The Line E does not match any line in the document.
2226 - The line G matches to the line E in the interface.
2227 CALLER : PO_AUTOCREATE_MAINPROC_PVT.process
2228 ============================================================================== */
2229
2230 PROCEDURE process_lines
2231 IS
2232
2233 l_api_name VARCHAR2(30) := 'process_lines';
2234 l_progress VARCHAR2(3) := '000';
2235
2236 BEGIN
2237
2238 IF g_debug_stmt THEN
2239 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2240 END IF;
2241
2242 l_progress := '010';
2243 PO_AUTO_LINE_PROCESS_PVT.setup_interface_data;
2244
2245 l_progress := '020';
2246 IF (po_autocreate_params.g_document_type <> 'PA') THEN
2247 group_interface_records;
2248 END IF;
2249
2250 IF PO_AUTOCREATE_PARAMS.g_mode = 'ADD' THEN
2251 l_progress := '030';
2252 merge_to_drafts;
2253 END IF;
2254
2255 l_progress := '040';
2256 PO_AUTO_LINE_PROCESS_PVT.fetch_lines( p_interface_header_id => PO_AUTOCREATE_PARAMS.x_interface_header_id, p_lines => x_lines );
2257
2258 l_progress := '050';
2259 PO_AUTO_LINE_PROCESS_PVT.derive_and_default_lines(p_lines => x_lines) ;
2260
2261 l_progress := '060';
2262 PO_AUTO_LINE_PROCESS_PVT.merge_to_price_diff_draft(p_lines => x_lines);
2263
2264 l_progress := '070';
2265 PO_AUTO_LINE_PROCESS_PVT.merge_to_attr_values_draft(p_lines => x_lines);
2266
2267 IF g_debug_stmt THEN
2268 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
2269 p_message => 'After calling all the procedures from PO_AUTO_LINE_PROCESS_PVT');
2270 END IF;
2271
2272 IF g_debug_stmt THEN
2273 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2274 END IF;
2275
2276 EXCEPTION
2277
2278 WHEN OTHERS THEN
2279 IF g_debug_unexp THEN
2280 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2281 END IF;
2282
2283 --CLM Phase 2 changes : error handling
2284 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_ERR',x_token1_value => sqlerrm);
2285
2286 po_message_s.sql_error('PROCESS_LINES',l_progress,SQLCODE);
2287 PO_AUTOCREATE_PVT.wrapup();
2288 RAISE;
2289
2290 END process_lines;
2291
2292 /* ============================================================================
2293 NAME: process_line_locations
2294 DESC: Handle the logic to derive, default, validate records from po_line_locations_interface
2295 table and insert records into po_line_locations_draft_all table.
2296 PO_AUTO_LINE_LOC_PROCESS_PVT.fetch_line_loc
2297 PO_AUTO_LINE_LOC_PROCESS_PVT.derive_and_default_line_loc : most for standard po / complex order
2298 (Country of Origin,Inspection Required,Receipt required,match option)
2299 PO_AUTO_LINE_LOC_PROCESS_PVT.validate_line_loc (??)
2300 PO_AUTO_LINE_LOC_PROCESS_PVT.merge_to_line_loc_draft
2301 (Shipments, Pay Items, Price Breaks they can be handled separetly);
2302 CALLER : PO_AUTOCREATE_MAINPROC_PVT.process
2303 ============================================================================== */
2304 PROCEDURE process_line_locations
2305 IS
2306
2307 l_api_name VARCHAR2(30) := 'PROCESS_LINE_LOCATIONS';
2308 l_progress VARCHAR2(3) := '000';
2309
2310 BEGIN
2311
2312 IF g_debug_stmt THEN
2313 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2314 END IF;
2315
2316 IF po_autocreate_params.g_document_subtype = 'BLANKET' THEN
2317
2318 IF (po_autocreate_params.g_interface_source_code = 'SOURCING') THEN
2319
2320 l_progress := '010';
2321 PO_AUTO_LINE_LOC_PROCESS_PVT.create_pricebreak_draft(x_lines);
2322
2323 END IF;
2324
2325 -- <Complex Work R12 Start>
2326 ELSIF (po_autocreate_params.g_is_complex_work_po) THEN
2327
2328 l_progress := '020';
2329 PO_AUTO_LINE_LOC_PROCESS_PVT.create_payitem_draft(x_lines);
2330
2331 -- <Complex Work R12 End>
2332 ELSE
2333
2334 l_progress := '030';
2335 PO_AUTO_LINE_LOC_PROCESS_PVT.create_shipment_draft(x_lines);
2336
2337 END IF;
2338
2339 l_progress := '040';
2340 PO_AUTO_LINE_LOC_PROCESS_PVT.update_req_lines(x_lines);
2341
2342 IF g_debug_stmt THEN
2343 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'End of Line Locations Process');
2344 END IF;
2345
2346 IF g_debug_stmt THEN
2347 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2348 END IF;
2349
2350 EXCEPTION
2351
2352 WHEN OTHERS THEN
2353 IF g_debug_unexp THEN
2354 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2355 END IF;
2356
2357 --CLM Phase 2 changes : error handling
2358 PO_AUTOCREATE_PVT.report_error('PO_AUTO_LINE_LOC_ERR',x_token1_value => sqlerrm);
2359
2360 po_message_s.sql_error('PROCESS_LINE_LOCATIONS',l_progress,SQLCODE);
2361 PO_AUTOCREATE_PVT.wrapup();
2362 RAISE;
2363 END process_line_locations;
2364
2365 /* ============================================================================
2366 NAME: process_distributions
2367 DESC: Handle the logic to derive, default, validate records from po_distributions_interface
2368 table and insert records into po_distributions_draft_all table.
2369 PO_AUTO_DIST_PROCESS_PVT.fetch_dists
2370 PO_AUTO_DIST_PROCESS_PVT.derive_and_default_dists
2371 PO_AUTO_LINE_LOC_PROCESS_PVT.validate_dists (??)
2372 PO_AUTO_LINE_LOC_PROCESS_PVT.merge_to_dists_draft
2373 CALLER : PO_AUTOCREATE_MAINPROC_PVT.process
2374 ============================================================================== */
2375 PROCEDURE process_distributions
2376 IS
2377
2378 l_api_name VARCHAR2(30) := 'process_distributions';
2379 l_progress VARCHAR2(3) := '000';
2380 x_dists PO_AUTOCREATE_TYPES.distributions_rec_type;
2381
2382 BEGIN
2383
2384 IF g_debug_stmt THEN
2385 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2386 END IF;
2387
2388 IF (po_autocreate_params.g_is_complex_work_po) THEN
2389
2390 l_progress := '010';
2391 po_auto_dist_process_pvt.create_payitem_dists( p_lines => x_lines );
2392 IF g_debug_stmt THEN
2393 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'create_payitem_dists');
2394 END IF;
2395
2396 ELSE
2397
2398 l_progress := '020';
2399 po_auto_dist_process_pvt.derive_and_default_dists( p_lines => x_lines);
2400 IF g_debug_stmt THEN
2401 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'derive_and_default_dists');
2402 END IF;
2403
2404 END IF;
2405
2406 IF g_debug_stmt THEN
2407 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2408 END IF;
2409
2410 -- ACRN proj changes
2411 l_progress := '030';
2412 -- Calling Default_Acrn_Values API
2413 po_auto_dist_process_pvt.Default_Acrn_Values;
2414
2415 IF g_debug_stmt THEN
2416 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2417 END IF;
2418 -- ACRN proj end
2419
2420 EXCEPTION
2421 WHEN OTHERS THEN
2422 IF g_debug_unexp THEN
2423 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2424 END IF;
2425
2426 --CLM Phase 2 changes : error handling
2427 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_ERR',x_token1_value => sqlerrm);
2428
2429 po_message_s.sql_error('process_distributions',l_progress,SQLCODE);
2430 PO_AUTOCREATE_PVT.wrapup();
2431 RAISE;
2432 END process_distributions;
2433
2434 /* ----------------------------------------------------
2435 ----------------- PUBLIC PROCEDURES ----------------
2436 ---------------------------------------------------- */
2437 /* ============================================================================
2438 NAME: process
2439 DESC: Main Procedure for the AutoCreate Main Processing logic which does the following
2440 - process_headers
2441 - group_interface_records
2442 - process_lines
2443 - process_line_locations
2444 - process_distributions
2445 Caller : PO_AUTOCREATE_PVT.create_po
2446 ==============================================================================*/
2447 PROCEDURE process
2448 IS
2449
2450 l_api_name VARCHAR2(30) := 'process';
2451 l_progress VARCHAR2(3) := '000';
2452
2453 BEGIN
2454
2455 IF g_debug_stmt THEN
2456 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2457 END IF;
2458
2459 -- process each entity from upper to lower level
2460 l_progress := '010';
2461
2462 process_headers;
2463
2464 l_progress := '020';
2465 process_lines;
2466
2467 l_progress := '030';
2468 process_line_locations;
2469
2470 l_progress := '040';
2471 process_distributions;
2472
2473 IF g_debug_stmt THEN
2474 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'End of Process');
2475 END IF;
2476
2477 EXCEPTION
2478 WHEN OTHERS THEN
2479
2480 IF g_debug_unexp THEN
2481 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2482 END IF;
2483
2484 -- CLM Phase 2 Changes : Error Handling
2485 PO_AUTOCREATE_PVT.report_error('PO_AUTO_MAINPROC_ERR',x_token1_value => sqlerrm);
2486
2487 po_message_s.sql_error('PROCESS',l_progress,SQLCODE);
2488 PO_AUTOCREATE_PVT.wrapup();
2489 RAISE;
2490
2491 END process;
2492
2493 /**
2494 * Function: has_one_time_location
2495 * Effects: Checks if the requisition line p_req_line_id has a one-time
2496 * location.
2497 * Returns: TRUE if the requisition line has a one-time location
2498 * FALSE otherwise
2499 */
2500 FUNCTION has_one_time_location (p_req_line_id IN NUMBER)
2501 RETURN BOOLEAN
2502 IS
2503 l_api_name CONSTANT VARCHAR2 (30) := 'has_one_time_location';
2504 l_flag VARCHAR2 (1);
2505 l_progress VARCHAR2 (3) := '000';
2506
2507 BEGIN
2508
2509 l_progress := '000';
2510
2511 IF (p_req_line_id IS NOT NULL) THEN
2512
2513 -- Query if this req line has a one-time location attachment, which
2514 -- indicates that the req line is for a one-time location.
2515 SELECT 'Y'
2516 INTO l_flag
2517 FROM fnd_attached_documents
2518 WHERE entity_name = 'REQ_LINES'
2519 AND pk1_value = TO_CHAR (p_req_line_id)
2520 AND pk2_value = 'ONE_TIME_LOCATION'
2521 AND ROWNUM = 1;
2522
2523 IF g_debug_stmt THEN
2524 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
2525 p_message => 'Req line ' || p_req_line_id || ' has one-time attachment' );
2526 END IF;
2527
2528 RETURN TRUE;
2529
2530 ELSE
2531
2532 IF g_debug_stmt THEN
2533 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress, p_message => 'Req line is null' );
2534 END IF;
2535
2536 RETURN FALSE;
2537
2538 END IF;
2539
2540 EXCEPTION
2541
2542 WHEN NO_DATA_FOUND THEN
2543
2544 IF g_debug_stmt THEN
2545 po_debug.debug_stmt (p_log_head => g_log_head || l_api_name, p_token => l_progress,
2546 p_message => 'NO_DATA_FOUND: No one-time attachment for req line ' || p_req_line_id );
2547 END IF;
2548
2549 RETURN FALSE;
2550
2551 WHEN OTHERS THEN
2552
2553 IF g_debug_unexp THEN
2554 po_debug.debug_exc (p_log_head => g_log_head || l_api_name, p_progress => l_progress );
2555 END IF;
2556
2557 RETURN FALSE;
2558
2559 END has_one_time_location;
2560
2561 /* ============================================================================
2562 NAME: get_ship_to_loc
2563 DESC: This function returns the exact HR/HZ Ship to location for the
2564 given location.
2565 ARGS: p_deliver_to_loc_id IN Deliver to Location
2566 ============================================================================ */
2567 FUNCTION get_ship_to_loc(p_deliver_to_loc_id IN NUMBER)
2568 RETURN NUMBER
2569 IS
2570
2571 l_ship_to_location_id NUMBER;
2572 l_found BOOLEAN := FALSE;
2573 d_module VARCHAR2 (100) := 'po.plsql.PO_AUTOCREATE_MAINPROC_PVT.get_ship_to_loc';
2574 d_progress NUMBER;
2575
2576 BEGIN
2577
2578 d_progress := 0;
2579
2580 IF (po_log.d_proc) THEN
2581 po_log.proc_begin (d_module);
2582 po_log.proc_begin (d_module, 'p_deliver_to_loc_id', p_deliver_to_loc_id);
2583 END IF;
2584
2585 d_progress := 10;
2586
2587 BEGIN
2588
2589 SELECT NVL (hrl.ship_to_location_id, hrl.location_id)
2590 INTO l_ship_to_location_id
2591 FROM hr_locations_all hrl
2592 WHERE hrl.location_id = p_deliver_to_loc_id;
2593
2594 l_found := TRUE;
2595
2596 EXCEPTION
2597
2598 WHEN NO_DATA_FOUND THEN
2599
2600 IF (po_log.d_stmt) THEN
2601 po_log.stmt (d_module, d_progress, 'No data found in hr_locations.' );
2602 END IF;
2603
2604 END;
2605
2606 d_progress := 20;
2607
2608 IF (NOT l_found) THEN
2609
2610 BEGIN
2611
2612 SELECT hzl.location_id
2613 INTO l_ship_to_location_id
2614 FROM hz_locations hzl
2615 WHERE hzl.location_id = p_deliver_to_loc_id;
2616
2617 l_found := TRUE;
2618
2619 EXCEPTION
2620
2621 WHEN NO_DATA_FOUND THEN
2622 IF (po_log.d_stmt) THEN
2623 po_log.stmt (d_module, d_progress, 'No data found in hz_locations.' );
2624 END IF;
2625
2626 END;
2627
2628 END IF; -- if not l_found
2629
2630 IF (NOT l_found) THEN
2631 RAISE NO_DATA_FOUND;
2632 END IF;
2633
2634 IF (po_log.d_proc) THEN
2635 po_log.proc_return (d_module, l_ship_to_location_id);
2636 po_log.proc_end (d_module);
2637 END IF;
2638
2639 RETURN l_ship_to_location_id;
2640
2641 EXCEPTION
2642 WHEN OTHERS THEN
2643
2644 IF (po_log.d_exc) THEN
2645 po_log.exc (d_module, d_progress, SQLCODE || SQLERRM);
2646 END IF;
2647
2648 --CLM Phase 2 changes : error handling
2649 PO_AUTOCREATE_PVT.report_error('PO_AUTO_SHIP_TO_LOC_ERR',x_token1_value => sqlerrm);
2650
2651 RAISE;
2652
2653 END get_ship_to_loc;
2654
2655 --<Bug 16308668>
2656 /* ============================================================================
2657 NAME: sync_complex_pricing
2658 DESC: This function syncs up the complex pricing AG from base award to Mod
2659 when a sync_draft_from_txn occurs
2660 ARGS: p_style_id IN Style id of the document.
2661 ============================================================================ */
2662 PROCEDURE sync_complex_pricing(
2663 p_po_line_id_tbl IN PO_TBL_NUMBER,
2664 p_style_id IN NUMBER)
2665 IS
2666 l_api_name VARCHAR2(30) := 'sync_complex_pricing';
2667 l_progress VARCHAR2(3) := '000';
2668 l_style_id NUMBER;
2669 l_src_template_id NUMBER;
2670 l_target_template_id NUMBER;
2671 from_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2672 to_pk_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2673 l_to_pk1_value NUMBER;
2674 l_attr_group_tbl PO_TBL_VARCHAR30;
2675 x_return_status VARCHAR2(1);
2676 x_msg_count NUMBER;
2677 x_msg_data VARCHAR2(1000);
2678
2679 BEGIN
2680 IF g_debug_stmt THEN
2681 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2682 END IF;
2683
2684 l_progress := '020';
2685 IF g_debug_stmt THEN
2686 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name , p_token => l_progress , p_message => 'Style ID :' || p_style_id );
2687 END IF;
2688
2689 l_progress := '030';
2690 l_target_template_id := PO_UDA_AUTOCREATE_PKG.get_uda_template_id
2691 (p_functional_area => 'PURCHASING' ,
2692 p_doc_type => 'STANDARD' ,
2693 p_doc_level => 'LINE' ,
2694 p_doc_style_id => p_style_id );
2695 IF g_debug_stmt THEN
2696 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name , p_token => l_progress , p_message => 'Target Tempalte ID :' || l_target_template_id );
2697 END IF;
2698
2699 l_progress := '040';
2700 FOR i IN 1..p_po_line_id_tbl.Count
2701 LOOP
2702 l_progress := '080';
2703 l_src_template_id := PO_UDA_AUTOCREATE_PKG.get_template_id ('PO_LINES_ALL' ,
2704 'PO_LINE_ID' ,
2705 p_po_line_id_tbl(i) ,
2706 NULL,
2707 NULL);
2708 IF g_debug_stmt THEN
2709 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name , p_token => l_progress , p_message => 'l_src_template_id' || l_src_template_id );
2710 END IF;
2711
2712 from_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY ( EGO_COL_NAME_VALUE_PAIR_OBJ
2713 ('PO_LINE_ID',
2714 p_po_line_id_tbl(i) ),
2715 EGO_COL_NAME_VALUE_PAIR_OBJ
2716 ( 'DRAFT_ID', -1) );
2717 IF g_debug_stmt THEN
2718 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name , p_token => l_progress , p_message => 'PO_LINE_ID : ' || p_po_line_id_tbl(i) );
2719 END IF;
2720
2721 l_progress := '100';
2722
2723 SELECT DISTINCT ag.attr_group_name BULK COLLECT
2724 INTO l_attr_group_tbl
2725 FROM po_lines_all_ext_b ple ,
2726 ego_attr_groups_v ag ,
2727 po_uda_ag_template_usages tu
2728 WHERE ple.po_line_id = p_po_line_id_tbl(i)
2729 AND ple.attr_group_id = ag.attr_group_id
2730 AND ag.attr_group_id = tu.attribute_group_id
2731 AND tu.template_id = l_src_template_id
2732 AND tu.attribute_category = 'PRICING';
2733
2734 IF g_debug_stmt THEN
2735 po_debug.debug_var(p_log_head => g_log_head||l_api_name , p_progress => l_progress , p_name => 'l_attr_group_tbl' , p_value => l_attr_group_tbl );
2736 END IF;
2737
2738 l_progress := '120';
2739 to_pk_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY ( EGO_COL_NAME_VALUE_PAIR_OBJ
2740 ( 'PO_LINE_ID', p_po_line_id_tbl(i) ),
2741 EGO_COL_NAME_VALUE_PAIR_OBJ
2742 ( 'DRAFT_ID',
2743 po_autocreate_params.g_draft_id));
2744 IF l_attr_group_tbl.Count > 0
2745 AND l_src_template_id IS NOT NULL
2746 AND l_target_template_id IS NOT NULL
2747 THEN
2748 l_progress := '140';
2749 po_uda_data_util.autocreate_user_attrs
2750 ( from_template_id => l_src_template_id ,
2751 to_template_id => l_target_template_id ,
2752 from_pk_col_value_pairs => from_pk_col_value_pairs ,
2753 to_pk_col_value_pairs => to_pk_col_value_pairs ,
2754 copy_attribute_groups => 'SPECIFIC' ,
2755 attribute_group_table => l_attr_group_tbl ,
2756 p_commit => fnd_api.g_false ,
2757 x_return_status => x_return_status ,
2758 x_msg_count => x_msg_count ,
2759 x_msg_data => x_msg_data );
2760 IF g_debug_stmt THEN
2761 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name ,
2762 p_token => l_progress ,
2763 p_message => 'After Calling PO_UDA_DATA_UTIL.AutoCreate_User_Attrs Return Status ' || x_return_status ||
2764 ' Message Count :' || x_msg_count ||' Message Data: '|| x_msg_data );
2765 END IF;
2766
2767 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2768 RAISE FND_API.G_EXC_ERROR;
2769 END IF;
2770
2771 END IF;
2772 END LOOP;
2773
2774 IF g_debug_stmt THEN
2775 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2776 END IF;
2777
2778 EXCEPTION
2779 WHEN FND_API.G_EXC_ERROR THEN
2780 l_progress := '150';
2781 fnd_msg_pub.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2782 IF (PO_LOG.d_exc) THEN
2783 PO_LOG.exc(g_log_head||l_api_name, l_progress, x_msg_data );
2784 PO_DEBUG.debug_end(g_log_head||l_api_name);
2785 END IF;
2786
2787 WHEN OTHERS THEN
2788 IF g_debug_unexp THEN
2789 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2790 END IF;
2791 END sync_complex_pricing;
2792
2793
2794 --<Bug 16308668>
2795 /* ============================================================================
2796 NAME: sync_uda
2797 DESC: This function syncs up the all AG from base award to Mod at a given entity
2798 when a sync_draft_from_txn occurs
2799 ARGS: p_style_id IN Style id of the document.
2800 p_level IN Entity level.
2801 ============================================================================ */
2802 PROCEDURE sync_uda(
2803 p_po_line_id_tbl IN PO_TBL_NUMBER DEFAULT NULL,
2804 p_line_location_id_tbl IN PO_TBL_NUMBER DEFAULT NULL,
2805 p_level IN VARCHAR2,
2806 p_style_id IN NUMBER)
2807 IS
2808 l_api_name VARCHAR2(30) := 'sync_uda';
2809 l_progress VARCHAR2(3) := '000';
2810 x_attr_group_tbl PO_TBL_VARCHAR30;
2811 l_template_id NUMBER;
2812 l_msg_count NUMBER;
2813 l_msg_data VARCHAR2(4000);
2814 l_return_status VARCHAR2(1):= NULL;
2815
2816 BEGIN
2817 IF g_debug_stmt THEN
2818 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2819 END IF;
2820
2821 l_progress := '010';
2822
2823 IF g_debug_stmt THEN
2824 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'p_style_id : '||p_style_id);
2825 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Entity Level : '||p_level);
2826 END IF;
2827
2828 l_progress := '020';
2829 l_template_id := PO_UDA_AUTOCREATE_PKG.get_uda_template_id
2830 (p_functional_area => 'PURCHASING' ,
2831 p_doc_type => 'STANDARD' ,
2832 p_doc_level => p_level ,
2833 p_doc_style_id => p_style_id );
2834 IF g_debug_stmt THEN
2835 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'l_template_id : '||l_template_id);
2836 END IF;
2837
2838 IF p_level ='LINE' THEN
2839 l_progress := '040';
2840 FOR i IN 1..p_po_line_id_tbl.Count
2841 LOOP
2842 l_progress := '050';
2843 IF g_debug_stmt THEN
2844 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Line Id : '||p_po_line_id_tbl(i));
2845 END IF;
2846
2847 SELECT DISTINCT ag.attr_group_name BULK COLLECT
2848 INTO x_attr_group_tbl
2849 FROM po_lines_all_ext_b ple ,
2850 ego_attr_groups_v ag ,
2851 po_uda_ag_template_usages tu
2852 WHERE ple.po_line_id = p_po_line_id_tbl(i)
2853 AND ple.attr_group_id = ag.attr_group_id
2854 AND ag.attr_group_id = tu.attribute_group_id
2855 AND tu.template_id = ple.uda_template_id
2856 AND tu.attribute_category <> 'PRICING';
2857
2858 IF x_attr_group_tbl.Count > 0 THEN
2859 l_progress := '060';
2860 PO_UDA_DATA_UTIL.Copy_User_Attrs
2861 ( x_template_id => l_template_id,
2862 x_from_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY
2863 (EGO_COL_NAME_VALUE_PAIR_OBJ( 'PO_LINE_ID' ,
2864 p_po_line_id_tbl(i)),
2865 EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' ,
2866 -1) ),
2867 x_to_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY
2868 (EGO_COL_NAME_VALUE_PAIR_OBJ( 'PO_LINE_ID' ,
2869 p_po_line_id_tbl(i)),
2870 EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' ,
2871 po_autocreate_params.g_draft_id)),
2872 x_copy_attribute_groups => 'SPECIFIC',
2873 x_attribute_group_table => x_attr_group_tbl,
2874 x_return_status => l_return_status,
2875 x_msg_count => l_msg_count,
2876 x_msg_data => l_msg_data );
2877 IF g_debug_stmt THEN
2878 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2879 ,p_token => l_progress
2880 ,p_message => 'After Calling PO_UDA_DATA_UTIL.Copy_User_Attrs for LINE '|| l_return_status ||
2881 ' Message Count :' || l_msg_count ||' Message Data: '|| l_msg_data );
2882 END IF;
2883
2884 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2885 RAISE FND_API.G_EXC_ERROR;
2886 END IF;
2887 END IF;
2888 END LOOP;
2889 ELSIF p_level ='SHIPMENT' THEN
2890 l_progress := '070';
2891 FOR i IN 1..p_line_location_id_tbl.Count
2892 LOOP
2893 l_progress := '080';
2894 IF g_debug_stmt THEN
2895 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Line Location Id : '||p_line_location_id_tbl(i));
2896 END IF;
2897
2898 SELECT DISTINCT ag.attr_group_name BULK COLLECT
2899 INTO x_attr_group_tbl
2900 FROM po_line_locations_all_ext_b plle ,
2901 ego_attr_groups_v ag ,
2902 po_uda_ag_template_usages tu
2903 WHERE plle.line_location_id = p_line_location_id_tbl(i)
2904 AND plle.attr_group_id = ag.attr_group_id
2905 AND ag.attr_group_id = tu.attribute_group_id
2906 AND tu.template_id = plle.uda_template_id;
2907
2908 IF x_attr_group_tbl.Count > 0 THEN
2909 l_progress := '090';
2910 PO_UDA_DATA_UTIL.Copy_User_Attrs
2911 ( x_template_id => l_template_id,
2912 x_from_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY
2913 (EGO_COL_NAME_VALUE_PAIR_OBJ( 'LINE_LOCATION_ID',
2914 p_line_location_id_tbl(i)),
2915 EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' ,
2916 -1) ),
2917 x_to_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY
2918 (EGO_COL_NAME_VALUE_PAIR_OBJ( 'LINE_LOCATION_ID',
2919 p_line_location_id_tbl(i)),
2920 EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' ,
2921 po_autocreate_params.g_draft_id)),
2922 x_copy_attribute_groups => 'SPECIFIC',
2923 x_attribute_group_table => x_attr_group_tbl,
2924 x_return_status => l_return_status,
2925 x_msg_count => l_msg_count,
2926 x_msg_data => l_msg_data );
2927 IF g_debug_stmt THEN
2928 po_debug.debug_stmt(p_log_head => g_log_head||l_api_name
2929 ,p_token => l_progress
2930 ,p_message => 'After Calling PO_UDA_DATA_UTIL.Copy_User_Attrs for LINE '|| l_return_status ||
2931 ' Message Count :' || l_msg_count ||' Message Data: '|| l_msg_data );
2932 END IF;
2933
2934 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2935 RAISE FND_API.G_EXC_ERROR;
2936 END IF;
2937 END IF;
2938 END LOOP;
2939 END IF;
2940 IF g_debug_stmt THEN
2941 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2942 END IF;
2943 EXCEPTION
2944 WHEN FND_API.G_EXC_ERROR THEN
2945 l_progress := '100';
2946 fnd_msg_pub.Count_And_Get(p_count => l_msg_count, p_data => l_msg_data);
2947 IF (PO_LOG.d_exc) THEN
2948 PO_LOG.exc(g_log_head||l_api_name, l_progress, l_msg_data );
2949 PO_DEBUG.debug_end(g_log_head||l_api_name);
2950 END IF;
2951 WHEN OTHERS THEN
2952 IF g_debug_unexp THEN
2953 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2954 END IF;
2955 END sync_uda;
2956
2957
2958 END PO_AUTOCREATE_MAINPROC_PVT;
[Home] [Help]