[Home] [Help]
PACKAGE BODY: APPS.PO_AUTO_DIST_PROCESS_PVT
Source
1 PACKAGE BODY po_auto_dist_process_pvt AS
2 /* $Header: PO_AUTO_DIST_PROCESS_PVT.plb 120.29.12020000.3 2013/03/25 11:06:43 amalick ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(1000) := 'po_auto_dist_process_pvt';
5 g_log_head CONSTANT VARCHAR2(1000) := 'po.plsql.po_auto_dist_process_pvt.';
6 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
7 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
8
9 /* ----------------------------------------------------
10 ----------------- PRIVATE PROCEDURES -------------------
11 -------------------------Moved 3 Private procedures to public, by moving to spec--------------------------- */
12
13 /* ----------------------------------------------------
14 ----------------- PUBLIC PROCEDURES -------------------
15 ---------------------------------------------------- */
16 /* ============================================================================
17 NAME: derive_and_default_dists
18 DESC: Peform derivation and defaulting logic on distributions.
19 The derived attributes include:
20 - distribution_num
21 - distribution_id
22 ARGS: IN OUT : x_dists PO_AUTOCREATE_TYPES.distributions_rec_type -Record variable to hold the distributions info
23 NOTE: Use the following procedures to derive the distribution attributes
24 -PO_INTERFACE_S.create_distributions
25 CALLER OF THE PROCEDURE
26 -PO_AUTOCREATE_MAINPROC_PVT.process_distributions
27 ==============================================================================*/
28
29 PROCEDURE derive_and_default_dists(p_lines IN PO_AUTOCREATE_TYPES.lines_rec_type )
30 IS
31
32 l_progress VARCHAR2(3) := '000';
33 l_api_name VARCHAR2(30) := 'derive_dists';
34 x_distribution_num NUMBER;
35 x_order_type_lookup_code VARCHAR2(15);
36 x_sob_id NUMBER;
37 x_po_appl_id NUMBER;
38 x_gl_appl_id NUMBER;
39 /* obtain currency info to adjust precision */
40 x_precision NUMBER := 2;
41 x_ext_precision NUMBER := 5;
42 x_min_unit NUMBER := NULL;
43 x_uom_convert VARCHAR2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
44 x_po_uom VARCHAR2(25):=NULL;
45 x_conversion_rate NUMBER := 1;
46 x_gl_date_option VARCHAR2(25);
47 l_uom_conversion_rate MTL_UOM_CONVERSIONS.conversion_rate%TYPE := 1;
48 l_currency_conversion_rate PO_HEADERS_ALL.rate%TYPE := 1;
49 l_drop_ship_flag po_line_locations.drop_ship_flag%type;
50 l_from_type_lookup_code po_headers_all.TYPE_LOOKUP_CODE%type;
51 l_amount_ordered NUMBER;
52 l_rownum NUMBER;
53 l_charge_account_id NUMBER;
54 l_accrual_account_id NUMBER;
55 l_variance_account_id NUMBER;
56 l_encumbered_flag VARCHAR2(1);
57 l_budget_account_id NUMBER;
58 l_dist_id_tbl po_tbl_number;
59 l_dist_count NUMBER;
60 x_req_rate PO_REQUISITION_LINES_ALL.rate%TYPE;
61 x_req_currency_code PO_REQUISITION_LINES_ALL.currency_code%TYPE;
62 x_rate PO_REQUISITION_LINES_ALL.rate%TYPE;
63 x_kanban_card_id NUMBER:='';
64 x_accrued_flag VARCHAR2(1);
65 l_fund_source_not_known VARCHAR2(1);
66 --Bug13553227
67 l_validate_gl_period VARCHAR2(10);
68 x_gl_date date;
69 --Bug 13586217
70 l_last_dist varchar2(1);
71 j number;
72 l_orig_from_req_flag VARCHAR2(1);
73
74 CURSOR c_dis_accounts(p_intf_line_id NUMBER)
75 IS
76 SELECT charge_account_id,
77 accrual_account_id ,
78 variance_account_id,
79 encumbered_flag,
80 budget_account_id
81 FROM po_distributions_interface pdi
82 WHERE pdi.interface_header_id = po_autocreate_params.g_interface_header_id
83 AND pdi.interface_line_id = p_intf_line_id
84 ORDER BY pdi.interface_distribution_id;
85
86 BEGIN
87
88 IF g_debug_stmt THEN
89 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
90 END IF;
91
92 l_progress := '010';
93
94 FOR i IN 1..p_lines.intf_line_id_tbl.Count
95 LOOP
96
97 IF p_lines.requisition_line_id_tbl(i) IS NOT NULL then
98 -- Bug 9960752: Added another IF condition for exercised distribution
99 --Getting the value for fund_source_not_known flag
100 BEGIN
101
102 SELECT prl.fund_source_not_known
103 INTO l_fund_source_not_known
104 FROM PO_REQUISITION_LINES_ALL prl
105 WHERE prl.requisition_line_id = p_lines.requisition_line_id_tbl(i);
106
107 EXCEPTION
108 WHEN No_Data_Found THEN
109 l_fund_source_not_known := 'N';
110
111 WHEN OTHERS THEN
112 RAISE;
113
114 END;
115
116 END IF;
117
118
119 IF (p_lines.clm_option_indicator_tbl(i) = 'O' AND NVL(p_lines.clm_exercised_flag_tbl(i),'N') = 'N') -- Bug 9960752
120 OR Nvl(l_fund_source_not_known,'N') = 'Y' THEN --Adding to bypass creation of distribution at backend if fund_source_not_known=Y
121 NULL;
122 ELSE
123 IF p_lines.line_loc_id_tbl(i) IS NOT NULL THEN
124
125 l_progress := '020';
126
127 --get previous max distribution number for this shipment
128 IF po_autocreate_params.g_process_code = 'ADD_FUNDS' THEN
129 SELECT NVL(MAX(distribution_num), 0)
130 INTO x_distribution_num
131 FROM po_distributions_merge_v --Add req to mod project
132 WHERE line_location_id = p_lines.line_loc_id_tbl(i)
133 AND draft_id = po_autocreate_params.g_draft_id;
134 ELSE
135 /*Bug 13586217 replaced base table with the merge view to cater to add to mod flow */
136 SELECT NVL(MAX(distribution_num), 0)
137 INTO x_distribution_num
138 FROM po_distributions_merge_v --<Shared Proc FPJ>
139 WHERE line_location_id = p_lines.line_loc_id_tbl(i)
140 AND draft_id = po_autocreate_params.g_draft_id;
141 END IF;
142
143 l_progress:='030';
144
145 fnd_profile.get('PO_AUTOCREATE_DATE',x_gl_date_option);
146
147 l_progress := '040';
148 SELECT order_type_lookup_code
149 INTO x_order_type_lookup_code
150 FROM po_line_types
151 WHERE line_type_id = p_lines.line_type_id_tbl(i);
152
153 l_progress := '050';
154 SELECT set_of_books_id
155 INTO x_sob_id
156 FROM financials_system_params_all --<Shared Proc FPJ>
157 WHERE NVL(org_id, -99) = NVL(PO_AUTOCREATE_PARAMS.g_purchasing_ou_id, -99);
158
159 IF g_debug_stmt THEN
160 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
161 p_message => 'Create_distributions: Order type: '||x_order_type_lookup_code);
162 END IF;
163
164 --added not null check for currency
165 IF p_lines.hd_currency_code_tbl(i) IS NOT NULL THEN
166 l_progress := '060';
167 FND_CURRENCY.get_info ( currency_code => p_lines.hd_currency_code_tbl(i) -- IN
168 , PRECISION => x_precision -- OUT
169 , ext_precision => x_ext_precision -- OUT
170 , min_acct_unit => x_min_unit -- OUT
171 );
172 END IF;
173
174 l_progress := '070';
175 /* Kanban_Card_Id is copied from requisition line
176 * to po_distributions*/
177 --<SOURCING TO PO FPH>
178 --Even sourcing need to execute this when backed by a req.
179 BEGIN
180 SELECT KANBAN_CARD_ID
181 INTO x_kanban_card_id
182 FROM po_requisition_lines_all pol --<Shared Proc FPJ>
183 WHERE pol.REQUISITION_LINE_ID = p_lines.requisition_line_id_tbl(i);
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 /* Not all req has kanban id */
187 IF g_debug_stmt THEN
188 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: '||SQLERRM);
189 END IF;
190 WHEN OTHERS THEN
191 IF g_debug_unexp THEN
192 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
193 END IF;
194
195 --CLM Phase 2 changes : error handling
196 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_KANBAN_ERR',
197 x_token1_value => sqlerrm,
198 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
199
200 raise;
201 END;
202
203 l_progress := '080';
204 IF(p_lines.hd_pcard_id_tbl(i) IS NOT NULL) THEN
205 x_accrued_flag := 'Y';
206 ELSE
207 x_accrued_flag := 'N';
208 END IF;
209
210 --Bug13553227
211 l_validate_gl_period := nvl(FND_PROFILE.VALUE('PO_VALIDATE_GL_PERIOD'),'Y');
212 IF g_debug_stmt THEN
213 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
214 p_message => 'l_validate_gl_period: '||l_validate_gl_period);
215 END IF;
216
217 --The following should be done only if the po encumbrance flag is yes
218 IF ((PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag = 'Y')
219 AND (x_gl_date_option <> 'REQ GL DATE')
220 AND (PO_AUTOCREATE_PARAMS.g_sys.period_name IS NULL)) THEN
221 l_progress := '090';
222 /*Bug13553227. eliminated the sql to derive the period name.
223 calling PO_PERIODS_SV.get_period_name to make it consistent.
224 If the get_period_name does not return a period then we will call build_GL_Encumbered_Date in case of PO:validate GL period = 'Redefault'*/
225 l_progress := 50;
226 PO_PERIODS_SV.get_period_name( x_sob_id => PO_AUTOCREATE_PARAMS.g_sys.sob_id ,
227 x_gl_date => SYSDATE ,
228 x_gl_period => PO_AUTOCREATE_PARAMS.g_sys.period_name );
229
230
231
232 IF (PO_AUTOCREATE_PARAMS.g_sys.period_name IS NULL) THEN
233 l_progress := 60;
234 IF l_validate_gl_period = 'R' THEN
235
236 PO_PERIODS_SV.build_GL_Encumbered_Date(l_sob_id => PO_AUTOCREATE_PARAMS.g_sys.sob_id
237 ,x_gl_date => x_gl_date
238 ,x_gl_period => PO_AUTOCREATE_PARAMS.g_sys.period_name);
239
240 END IF;
241 END IF;
242
243
244 IF g_debug_stmt THEN
245 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
246 p_message => 'PO_AUTOCREATE_PARAMS.g_sys.period_name: '||PO_AUTOCREATE_PARAMS.g_sys.period_name);
247 END IF;
248
249
250 IF (PO_AUTOCREATE_PARAMS.g_sys.period_name IS NULL) THEN
251 l_progress := 70;
252 IF (PO_LOG.d_exc) THEN
253 PO_LOG.exc(g_log_head||l_api_name, l_progress, 'Unable to find period name for SYSDATE');
254 END IF;
255
256 --CLM Phase 2 changes : error handling
257 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_GL_PERIOD_ERR',x_token1_value => sqlerrm);
258
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260 END IF;
261
262 /*Bug13553227 end */
263 END IF;
264
265 l_progress := '100';
266 SELECT application_id
267 INTO x_po_appl_id
268 FROM fnd_application
269 WHERE application_short_name = 'PO';
270
271 l_progress := '110';
272 SELECT application_id
273 INTO x_gl_appl_id
274 FROM fnd_application
275 WHERE application_short_name = 'SQLGL';
276
277 --Conversion of req UOM to Quotation UOM should always happen if the
278 -- source document is a quote and profile 'PO: Convert Requisition UOM to Source Document UOM'
279 -- should be ignored in that case
280 IF (p_lines.from_line_id_tbl(i) IS NOT NULL) AND (PO_AUTOCREATE_PARAMS.g_document_subtype = 'STANDARD') THEN
281 l_progress := '120';
282
283 BEGIN
284 --need to check ant interface.from_line_id and intreface.from_header_id
285 SELECT poh.type_lookup_code
286 INTO l_from_type_lookup_code
287 FROM po_headers_all poh
288 WHERE poh.po_header_id=p_lines.from_header_id_tbl(i) ;
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 IF g_debug_unexp THEN
293 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
294 END IF;
295
296 --CLM Phase 2 changes : error handling
297 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_TYPE_LOOKUP_ERR',
298 x_token1_value => sqlerrm,
299 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
300
301 po_message_s.sql_error('derive_dists',l_progress,SQLCODE);
302 raise;
303 NULL;
304
305 END;
306
307 END IF;
308
309 IF (NVL(x_uom_convert,'N') = 'Y' OR (l_from_type_lookup_code = 'QUOTATION')) THEN
310 IF (PO_AUTOCREATE_PARAMS.g_document_subtype='STANDARD') AND (p_lines.from_line_id_tbl(i) IS NOT NULL) THEN
311 /* get the uom from the PO . This will be used for uom conversion */
312
313 BEGIN
314 -- Autocreating a PO that references a GA
315 l_progress := '130';
316 SELECT unit_meas_lookup_code
317 INTO x_po_uom
318 FROM po_lines_all
319 WHERE po_line_id = p_lines.from_line_id_tbl(i);
320 IF g_debug_stmt THEN
321 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'derive_dists: UOM is: '||x_po_uom);
322 END IF;
323
324 EXCEPTION
325 WHEN OTHERS THEN
326 IF g_debug_unexp THEN
327 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
328 END IF;
329 po_message_s.sql_error('CREATE_DISTRIBUTIONS',l_progress,SQLCODE);
330
331 --CLM Phase 2 changes : error handling
332 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_UOM_LOOKUP_ERR',
333 x_token1_value => sqlerrm,
334 x_token2_value => PO_AUTOCREATE_PVT.get_requisition_line_num(p_lines.requisition_line_id_tbl(i)));
335
336 raise;
337
338 END;
339
340
341 /* before inserting into the distributions table get the conversion rate to convert
342 into the BPA uom if the uom's on the req and BPA are different .
343 This conversion is done only if he Convert UOM profile option is set to Yes. */
344 IF p_lines.unit_of_measure_tbl(i) <> x_po_uom THEN
345 l_progress := '150';
346 x_conversion_rate := po_uom_s.po_uom_convert(p_lines.unit_of_measure_tbl(i),
347 x_po_uom, p_lines.item_id_tbl(i));
348 ELSE
349 l_progress := '160';
350 x_conversion_rate := 1;
351 END IF;
352
353 IF g_debug_stmt THEN
354 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
355 p_message => 'Create_distributions: Conversion rate is: '||x_conversion_rate);
356 END IF;
357
358 END IF; -- g_document_subtype
359 END IF;
360
361 --<SOURCING TO PO FPH START>
362 --Dont insert distribution record if the various account_id s are
363 --not defaulted for negotiation lines which are not backed by req for sourcing
364
365 IF g_debug_stmt THEN
366 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Sourcing to FPH-5 starts');
367 END IF;
368 l_progress := '170';
369 OPEN c_dis_accounts(p_lines.intf_line_id_tbl(i));
370 FETCH c_dis_accounts
371 INTO l_charge_account_id,
372 l_accrual_account_id,
373 l_variance_account_id,
374 l_encumbered_flag,
375 l_budget_account_id;
376
377 CLOSE c_dis_accounts;
378
379 IF (PO_AUTOCREATE_PARAMS.g_interface_source_code IN ('SOURCING','CONSUMPTION_ADVICE') AND -- CONSIGNED FPI
380 (l_charge_account_id IS NULL
381 OR l_accrual_account_id IS NULL
382 OR l_variance_account_id IS NULL
383 OR (l_encumbered_flag='Y' AND l_budget_account_id IS NULL))) THEN
384 NULL;
385 ELSE
386 l_progress := '180';
387 l_uom_conversion_rate := x_conversion_rate;
388 l_currency_conversion_rate := NVL(PO_AUTOCREATE_PARAMS.g_rate_for_req_fields,1); -- <SERVICES FPJ>
389
390 l_progress := '190';
391 --update po_distributions interface
392 UPDATE po_distributions_interface
393 SET po_distribution_id = po_distributions_s.NEXTVAL,
394 distribution_num = x_distribution_num + rownum
395 WHERE interface_header_id = p_lines.intf_header_id_tbl(i)
396 AND interface_line_id = p_lines.intf_line_id_tbl(i);
397
398 l_progress := '200';
399 update_award_distributions(p_intf_header_id => p_lines.intf_header_id_tbl(i),
400 p_intf_line_id => p_lines.intf_line_id_tbl(i));
401
402 l_progress := '210';
403 BEGIN
404 SELECT NVL(drop_ship_flag,'N')
405 INTO l_drop_ship_flag
406 FROM po_line_locations_draft_all
407 WHERE line_location_id= p_lines.line_loc_id_tbl(i)
408 AND draft_id = po_autocreate_params.g_draft_id;
409 EXCEPTION
410 WHEN OTHERS THEN
411 NULL;
412 END;
413
414 l_progress := '220';
415 BEGIN
416 SELECT pdi.amount_ordered
417 INTO l_amount_ordered
418 FROM po_distributions_interface pdi,
419 po_line_locations_draft_all poll
420 WHERE pdi.interface_header_id = p_lines.intf_header_id_tbl(i)
421 AND pdi.interface_line_id = p_lines.intf_line_id_tbl(i)
422 AND poll.line_location_id = p_lines.line_loc_id_tbl(i)
423 AND poll.draft_id = po_autocreate_params.g_draft_id;
424
425 PO_DEBUG.debug_var(g_log_head||l_api_name, l_progress, 'l_amount_ordered',l_amount_ordered);
426 PO_DEBUG.debug_var(g_log_head||l_api_name, l_progress, 'x_order_type_lookup_code',x_order_type_lookup_code);
427 PO_DEBUG.debug_var(g_log_head||l_api_name, l_progress, 'l_currency_conversion_rate',l_currency_conversion_rate);
428 PO_DEBUG.debug_var(g_log_head||l_api_name, l_progress, 'l_uom_conversion_rate',l_uom_conversion_rate);
429 PO_DEBUG.debug_var(g_log_head||l_api_name, l_progress, 'x_precision',x_precision);
430 EXCEPTION
431 WHEN OTHERS THEN
432 NULL;
433 END;
434
435 --Currency conversion
436 --No conversion for same currency.
437 l_progress := '230';
438 BEGIN
439 SELECT PRL.currency_code,
440 NVL(PRL.rate,1)
441 INTO x_req_currency_code,
442 x_req_rate
443 FROM po_requisition_lines_all PRL
444 WHERE PRL.requisition_line_id = p_lines.requisition_line_id_tbl(i);
445 EXCEPTION
446 WHEN OTHERS THEN
447 NULL;
448 END;
449
450 l_progress := '240';
451 IF ( x_req_currency_code = p_lines.hd_currency_code_tbl(i) ) THEN
452 x_rate :=x_req_rate;
453 ELSE
454 x_rate:= PO_AUTOCREATE_PARAMS.g_rate_for_req_fields;
455 END IF;
456
457 l_progress := '250';
458 SELECT COUNT(*)
459 INTO l_dist_count
460 FROM po_distributions_interface pdi,
461 po_line_locations_draft_all poll
462 WHERE pdi.interface_header_id = p_lines.intf_header_id_tbl(i)
463 AND pdi.interface_line_id = p_lines.intf_line_id_tbl(i)
464 AND poll.draft_id =po_autocreate_params.g_draft_id
465 AND poll.line_location_id = p_lines.line_loc_id_tbl(i);
466
467 IF g_debug_stmt THEN
468 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress,
469 p_message => 'Create_distributions: l_drop_ship_flag : '||l_drop_ship_flag
470 ||' l_amount_ordered : '||l_amount_ordered
471 ||' x_req_currency_code : '||x_req_currency_code
472 ||' x_req_rate : '||x_req_rate
473 ||' x_rate : '||x_rate
474 ||' l_dist_count : '||l_dist_count);
475 END IF;
476
477 l_progress := '260';
478 INSERT
479 INTO po_distributions_draft_all --<Shared Proc FPJ>
480 (
481 po_distribution_id,
482 draft_id,
483 --who columns
484 ---------------------------------------------------------------
485 last_update_date,
486 last_updated_by,
487 last_update_login,
488 creation_date,
489 created_by,
490 request_id,
491 program_application_id,
492 program_id,
493 program_update_date,
494 ---------------------------------------------------------------
495 po_header_id,
496 po_line_id,
497 line_location_id,
498 po_release_id,
499 req_distribution_id,
500 set_of_books_id,
501 code_combination_id,
502 deliver_to_location_id,
503 deliver_to_person_id,
504 quantity_ordered,
505 quantity_delivered,
506 quantity_billed,
507 quantity_cancelled,
508 amount_ordered, -- <SERVICES FPJ>
509 amount_delivered, -- <SERVICES FPJ>
510 amount_cancelled, -- <SERVICES FPJ>
511 amount_billed, -- <SERVICES FPJ>
512 rate_date,
513 rate,
514 accrued_flag,
515 encumbered_flag,
516 gl_encumbered_date,
517 gl_encumbered_period_name,
518 distribution_num,
519 destination_type_code,
520 destination_organization_id,
521 destination_subinventory,
522 budget_account_id,
523 accrual_account_id,
524 variance_account_id,
525 --< Shared Proc FPJ Start >
526 dest_charge_account_id,
527 dest_variance_account_id,
528 --< Shared Proc FPJ End >
529 wip_entity_id,
530 wip_line_id,
531 wip_repetitive_schedule_id,
532 wip_operation_seq_num,
533 wip_resource_seq_num,
534 bom_resource_id,
535 prevent_encumbrance_flag,
536 project_id,
537 task_id,
538 end_item_unit_number,
539 expenditure_type,
540 project_accounting_context,
541 destination_context,
542 expenditure_organization_id,
543 expenditure_item_date,
544 accrue_on_receipt_flag,
545 kanban_card_id,
546 tax_recovery_override_flag, --<eTax Integration R12>
547 recovery_rate,
548 award_id,
549 --togeorge 09/27/2000
550 --added oke columns
551 oke_contract_line_id,
552 oke_contract_deliverable_id,
553 org_id, --<Shared Proc FPJ>
554 distribution_type, -- <Encumbrance FPJ>
555 tax_attribute_update_code, --<eTax Integration R12>
556 --interface_distribution_ref --<ECO 5373370>
557 --partial funding attributes
558 partial_funded_flag,
559 funded_value,
560 quantity_funded,
561 amount_funded ,
562 change_in_funded_value,
563 group_line_id,
564 clm_misc_loa,
565 clm_defence_funding,
566 clm_fms_case_number,
567 clm_agency_acct_identifier,
568 change_status
569 )
570 SELECT pdi.po_distribution_id, --<GRANTS FPJ>
571 po_autocreate_params.g_draft_id,
572 --default who columns
573 ---------------------------------------------------------------
574 NVL(p_lines.last_update_date_tbl(i), sysdate),
575 NVL(p_lines.last_updated_by_tbl(i), FND_GLOBAL.user_id),
576 NVL(p_lines.last_update_login_tbl(i), FND_GLOBAL.login_id),
577 NVL(p_lines.creation_date_tbl(i), sysdate),
578 NVL(p_lines.created_by_tbl(i), FND_GLOBAL.user_id),
579 NVL(p_lines.request_id_tbl(i), FND_GLOBAL.conc_request_id),
580 NVL(p_lines.program_application_id_tbl(i), FND_GLOBAL.prog_appl_id),
581 NVL(p_lines.program_id_tbl(i), FND_GLOBAL.conc_program_id),
582 NVL(p_lines.program_update_date_tbl(i), sysdate),
583 ---------------------------------------------------------------
584 p_lines.po_header_id_tbl(i),
585 p_lines.po_line_id_tbl(i),
586 p_lines.line_loc_id_tbl(i),
587 DECODE(PO_AUTOCREATE_PARAMS.g_document_subtype,'RELEASE',p_lines.po_release_id_tbl(i),''),
588 pdi.req_distribution_id,
589 NVL(x_sob_id, pdi.set_of_books_id), --<Bug 3692789>
590 pdi.charge_account_id,
591 pdi.deliver_to_location_id,
592 -- if the drop_ship_flag is 'Y' then we
593 --need to pass null
594 DECODE(l_drop_ship_flag,'Y',NULL,pdi.deliver_to_person_id),
595 DECODE( x_order_type_lookup_code,
596 'QUANTITY', ROUND(DECODE(po_autocreate_params.g_process_code,
597 'ADD_FUNDS', (DECODE(poll.PRICE_OVERRIDE,
598 NULL,pdi.quantity_ordered,
599 0,pdi.quantity_ordered,
600 pdi.funded_value/poll.PRICE_OVERRIDE)),
601 pdi.quantity_ordered * x_conversion_rate), 15),
602 --Commenting out, as in case of grouping distribution count will exceed 1
603 /*(DECODE(l_dist_count,
604 1, poll.quantity, pdi.quantity_ordered)) * x_conversion_rate), 15),*/
605 'AMOUNT', ROUND( (DECODE(po_autocreate_params.g_process_code,
606 'ADD_FUNDS',pdi.quantity_ordered,
607 pdi.quantity_ordered) * x_conversion_rate / NVL(x_rate,1)),
608 /*DECODE(l_dist_count,
609 1, poll.quantity, pdi.quantity_ordered)) * x_conversion_rate / NVL(x_rate,1)),*/ -- <<Bug#9862575>>
610 NVL(x_ext_precision, 15) ), NULL ),
611 -- <SERVICES FPJ END>
612 0,
613 0,
614 0,
615 -- <SERVICES FPJ START>
616 DECODE ( x_order_type_lookup_code -- amount_ordered
617 , 'RATE' ,ROUND ( ( pdi.amount_ordered * l_uom_conversion_rate / l_currency_conversion_rate) , x_precision ) ,
618 'FIXED PRICE',ROUND ( ( pdi.amount_ordered / l_currency_conversion_rate) , x_precision ) ,NULL ),
619 0, -- amount_delivered
620 0, -- amount_cancelled
621 0, -- amount_billed
622 -- <SERVICES FPJ END>
623 pdi.rate_date,
624 pdi.rate,
625 x_accrued_flag,
626 'N'
627 --<Encumbrance FPJ>
628 -- If Req encumbrance is on and the profile option requests
629 -- that the Req's GL date be used, use the Req's GL date.
630 -- Otherwise, if PO enc is on, use SYSDATE.
631 -- if PO enc is not on, use NULL.
632 -- gl_encumbered_date =
633 ,
634 NVL( DECODE( PO_AUTOCREATE_PARAMS.g_sys.req_encumbrance_flag ,
635 'Y', DECODE( x_gl_date_option ,
636 'REQ GL DATE', pdi.gl_encumbered_date , NULL ) ,
637 NULL ) , DECODE( PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag , 'Y', TRUNC(SYSDATE) , NULL ) )
638 -- gl_encumbered_period_name =
639 ,
640 NVL( DECODE( PO_AUTOCREATE_PARAMS.g_sys.req_encumbrance_flag ,
641 'Y', DECODE(x_gl_date_option ,
642 'REQ GL DATE', pdi.gl_encumbered_period_name , NULL ) ,
643 NULL ) , DECODE( PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag , 'Y', PO_AUTOCREATE_PARAMS.g_sys.period_name , NULL ) ) ,
644 pdi.distribution_num, --<GRANTS FPJ>
645 pdi.destination_type_code,
646 pdi.destination_organization_id,
647 pdi.destination_subinventory,
648 pdi.budget_account_id,
649 pdi.accrual_account_id,
650 pdi.variance_account_id,
651 --< Shared Proc FPJ Start >
652 -- Copy the receiving accounts from the interface table to
653 -- the PO table.
654 pdi.dest_charge_account_id,
655 pdi.dest_variance_account_id,
656 --< Shared Proc FPJ End >
657 pdi.wip_entity_id,
658 pdi.wip_line_id,
659 pdi.wip_repetitive_schedule_id,
660 pdi.wip_operation_seq_num,
661 pdi.wip_resource_seq_num,
662 pdi.bom_resource_id
663 --<ENCUMBRANCE FPJ>
664 -- prevent_encumbrance_flag =
665 ,
666 DECODE( pdi.destination_type_code , PO_AUTOCREATE_PARAMS.g_dest_type_code_SHOP_FLOOR, 'Y' , 'N' ) ,
667 pdi.project_id,
668 pdi.task_id,
669 pdi.end_item_unit_number,
670 pdi.expenditure_type,
671 pdi.project_accounting_context,
672 pdi.destination_context,
673 pdi.expenditure_organization_id,
674 pdi.expenditure_item_date,
675 poll.accrue_on_receipt_flag, --<Bug 16542675> : Should come from shipment
676 x_kanban_card_id,
677 pdi.tax_recovery_override_flag, --<eTax integration R12>
678 DECODE(pdi.tax_recovery_override_flag, 'Y', pdi.recovery_rate, NULL), --<eTax integration R12>
679 pdi.award_id, -- OGM_0.0 changes..
680 --togeorge 09/27/2000
681 --added oke columns
682 pdi.oke_contract_line_id,
683 pdi.oke_contract_deliverable_id,
684 PO_AUTOCREATE_PARAMS.g_purchasing_ou_id, --<Shared Proc FPJ>
685 poll.shipment_type, -- <Encumbrance FPJ: join on poll.line_location_id added>
686 nvl2(PO_AUTOCREATE_PARAMS.g_calculate_tax_flag, 'CREATE', NULL), --<eTax integration R12>
687 --pdi.interface_distribution_ref --<ECO 5373370>
688 --partial funding attributes
689 pdi.partial_funded_flag,
690 pdi.funded_value,
691 DECODE( x_order_type_lookup_code,
692 'QUANTITY', DECODE(po_autocreate_params.g_process_code,
693 'ADD_FUNDS', ROUND((DECODE(poll.PRICE_OVERRIDE,
694 NULL,pdi.quantity_funded,
695 0,pdi.quantity_funded,
696 pdi.funded_value/poll.PRICE_OVERRIDE)), 15),
697 pdi.quantity_funded), pdi.quantity_funded), -- Bug 11792824 changes
698 pdi.amount_funded, --<Bug#9746497 :CLM Partial Funding Changes>
699 pdi.funded_value,
700 pdi.group_line_id,
701 pdi.clm_misc_loa,
702 pdi.clm_defence_funding,
703 pdi.clm_fms_case_number,
704 pdi.clm_agency_acct_identifier,
705 'NEW'
706 FROM po_distributions_interface pdi,
707 po_line_locations_draft_all poll
708 WHERE pdi.interface_header_id = p_lines.intf_header_id_tbl(i)
709 AND pdi.interface_line_id = p_lines.intf_line_id_tbl(i)
710 AND poll.draft_id =po_autocreate_params.g_draft_id
711 AND poll.line_location_id = p_lines.line_loc_id_tbl(i) ; --<Encumbrance FPJ>
712
713 --Line Type and Structure Changes Project
714 --Do not calibrate the distributions if the lines have linked PR references.
715 --Calibration manipulates the distribution amount from prorated amount to line amount
716
717 SELECT orig_from_req_flag INTO l_orig_from_req_flag
718 FROM po_lines_interface
719 WHERE interface_line_id = p_lines.intf_line_id_tbl(i);
720
721 IF ((NVL(po_autocreate_params.g_process_code,'X') <> 'ADD_FUNDS') AND (Nvl(l_orig_from_req_flag, 'N') IN ('N', 'Y'))) THEN
722
723 /*l_progress := '270';
724 Correct last distribution amount for any conversion and
725 rounding inaccuracies to ensure that the distribution amounts add up
726 to their corresponding shipment amount.
727 calibrate_last_dist_amount(p_lines.line_loc_id_tbl(i));
728 -- bug 10246022
729 -- Correct last distribution quantity for any conversion and
730 -- rounding inaccuracies to ensure that the distribution quantity add up
731 -- to their corresponding shipment quantity.
732 l_progress := '280';
733 calibrate_last_dist_quantity(p_lines.line_loc_id_tbl(i));*/
734
735 /*Bug 13586217 - calibrate_last_dist_quantity and calibrate_last_dist_amount should be called outside the interface line id loop,
736 since different interface line ids can have the same shipment id which
737 was creating distributions with incorrect quantity and amount*/
738 l_last_dist := 'Y';
739 IF i < p_lines.intf_line_id_tbl.Count THEN
740 j := i+1;
741 FOR k IN j..p_lines.intf_line_id_tbl.Count LOOP
742 IF p_lines.line_loc_id_tbl(i) = p_lines.line_loc_id_tbl(k) THEN
743 l_last_dist := 'N';
744 EXIT;
745 END IF;
746 END LOOP;
747 END IF;
748
749 IF l_last_dist = 'Y' THEN
750 l_progress := '270';
751 /*Correct last distribution amount for any conversion and
752 rounding inaccuracies to ensure that the distribution amounts add up
753 to their corresponding shipment amount.*/
754 calibrate_last_dist_amount(p_lines.line_loc_id_tbl(i));
755 -- bug 10246022
756 -- Correct last distribution quantity for any conversion and
757 -- rounding inaccuracies to ensure that the distribution quantity add up
758 -- to their corresponding shipment quantity.
759 l_progress := '280';
760 calibrate_last_dist_quantity(p_lines.line_loc_id_tbl(i));
761 END IF;
762 END IF;
763
764
765
766
767 l_progress := '290';
768 SELECT pod.po_distribution_id BULK COLLECT
769 INTO l_dist_id_tbl
770 FROM po_distributions_draft_all pod
771 WHERE pod.po_line_id = p_lines.po_line_id_tbl(i);
772
773 l_progress := '300';
774 FOR i IN 1..l_dist_id_tbl.COUNT
775 LOOP
776 PO_AUTOCREATE_PVT.calculate_local('PO', 'DISTRIBUTION', l_dist_id_tbl(i));
777 END LOOP;
778
779 END IF;
780 END IF;
781 END IF;
782 END LOOP;
783
784 /*Bug 13586217 - calibrate_last_dist_quantity and calibrate_last_dist_amount should be called outside the interface line id loop,
785 since different interface line ids can have the same shipment id which
786 was creating distributions with incorrect quantity and amount*/
787
788
789
790 IF g_debug_stmt THEN
791 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
792 END IF;
793
794 EXCEPTION
795 WHEN OTHERS THEN
796 IF g_debug_unexp THEN
797 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
798 END IF;
799
800 --CLM Phase 2 changes : error handling
801 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_DEFAULT_ERR',x_token1_value => sqlerrm);
802
803 PO_AUTOCREATE_PVT.wrapup;
804 raise;
805 END derive_and_default_dists;
806 /* ============================================================================
807 Name: create_payitem_dists
808 Pre-reqs:
809 PO Payitems have all been created.
810 Modifies:
811 PO_DISTRIBUTIONS_ALL
812 Locks:
813 None.
814 Function:
815 Create all distributions for all payitems for a PO Line.
816 Parameters:
817 IN:
818 p_lines PO_AUTOCREATE_TYPES.lines_rec_type
819 OUT:
820 None.
821 Notes:
822 None
823 Testing:
824 None
825 End of Comments
826 ==============================================================================*/
827 PROCEDURE create_payitem_dists(p_lines IN PO_AUTOCREATE_TYPES.lines_rec_type )
828 IS
829 d_progress NUMBER;
830 d_module VARCHAR2(70) := 'po.plsql.PO_INTERFACE_S.create_payitem_dists';
831
832 CURSOR payitem_acct_gen_cursor(p_po_line_id NUMBER)
833 IS
834 SELECT pod.po_distribution_id ,
835 pod.project_id ,
836 pod.task_id ,
837 pod.award_id ,
838 pod.expenditure_type ,
839 pod.expenditure_item_date ,
840 pod.expenditure_organization_id ,
841 pod.destination_type_code ,
842 pod.destination_organization_id ,
843 pod.destination_subinventory ,
844 pod.deliver_to_location_id ,
845 pod.deliver_to_person_id ,
846 pod.gl_encumbered_date ,
847 poll.price_override ,
848 poll.payment_type ,
849 pod.distribution_type ,
850 pod.rate
851 /*Bug 13598209 : PO_LINE_LOCATIONS_ALL & PO_DISTRIBUTIONS_ALL has still not been populated
852 Hence po_distributions_draft_all & po_line_locations_draft_all needs to be used
853 */
854 FROM po_distributions_draft_all pod,
855 po_line_locations_draft_all poll
856 WHERE poll.po_line_id = p_po_line_id
857 AND pod.line_location_id = poll.line_location_id
858 AND pod.req_distribution_id IS NULL;
859
860 payitem_acct_rec payitem_acct_gen_cursor%ROWTYPE;
861 l_api_name VARCHAR2(30) := 'create_payitem_dists';
862 l_line_loc_id_tbl po_tbl_number;
863 l_line_loc_value_basis_tbl po_tbl_varchar30;
864 l_dist_id_tbl po_tbl_number;
865 l_return_status VARCHAR2(1);
866 l_msg_count NUMBER;
867 l_msg_data VARCHAR2(2000);
868 l_gl_date_option VARCHAR2(25);
869 l_sob_id PO_REQ_DISTRIBUTIONS_ALL.set_of_books_id%TYPE;
870 -- Acct. Generator Variables Start
871 l_dest_charge_success BOOLEAN := TRUE;
872 l_dest_variance_success BOOLEAN := TRUE;
873 l_charge_success BOOLEAN := TRUE;
874 l_budget_success BOOLEAN := TRUE;
875 l_accrual_success BOOLEAN := TRUE;
876 l_variance_success BOOLEAN := TRUE;
877 l_dest_charge_account_id NUMBER;
878 l_dest_variance_account_id NUMBER;
879 l_code_combination_id NUMBER;
880 l_budget_account_id NUMBER;
881 l_accrual_account_id NUMBER;
882 l_variance_account_id NUMBER;
883 l_dest_charge_account_desc VARCHAR2(2000);
884 l_dest_variance_account_desc VARCHAR2(2000);
885 l_charge_account_desc VARCHAR2(2000);
886 l_budget_account_desc VARCHAR2(2000);
887 l_accrual_account_desc VARCHAR2(2000);
888 l_variance_account_desc VARCHAR2(2000);
889 l_dest_charge_account_flex VARCHAR2(2000);
890 l_dest_variance_account_flex VARCHAR2(2000);
891 l_charge_account_flex VARCHAR2(2000);
892 l_budget_account_flex VARCHAR2(2000);
893 l_accrual_account_flex VARCHAR2(2000);
894 l_variance_account_flex VARCHAR2(2000);
895 l_wf_itemkey VARCHAR2(80) := NULL;
896 l_new_combination BOOLEAN := FALSE;
897 l_fb_error_msg VARCHAR2(2000);
898 l_acct_api_success BOOLEAN;
899 -- Acct. Generator Variables End
900 l_agent_id NUMBER;
901 /* obtain currency info to adjust precision */
902 x_precision NUMBER := '';
903 x_ext_precision NUMBER := '';
904 x_min_unit NUMBER := '';
905 --Bug13553227
906 l_validate_gl_period VARCHAR2(10);
907 x_gl_date date;
908
909 BEGIN
910
911 IF g_debug_stmt THEN
912 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
913 END IF;
914
915 FOR i IN 1..p_lines.intf_line_id_tbl.Count
916 LOOP
917 d_progress := 0;
918 IF (PO_LOG.d_proc) THEN
919 PO_LOG.proc_begin(d_module);
920 PO_LOG.proc_begin(d_module, 'p_po_line_id', p_lines.po_line_id_tbl(i));
921 PO_LOG.proc_begin(d_module, 'p_req_line_id', p_lines.requisition_line_id_tbl(i));
922 PO_LOG.proc_begin(d_module, 'p_interface_line_id', p_lines.intf_line_id_tbl(i));
923 END IF;
924
925 d_progress := 10;
926 l_charge_success := TRUE;
927 l_budget_success := TRUE;
928 l_accrual_success := TRUE;
929 l_variance_success := TRUE;
930
931 --added not null check for currency
932 IF p_lines.hd_currency_code_tbl(i) IS NOT NULL THEN
933 d_progress := 20;
934 FND_CURRENCY.get_info ( currency_code => p_lines.hd_currency_code_tbl(i) -- IN
935 , PRECISION => x_precision -- OUT
936 , ext_precision => x_ext_precision -- OUT
937 , min_acct_unit => x_min_unit -- OUT
938 );
939 END IF;
940
941 --Bug13553227
942 l_validate_gl_period := nvl(FND_PROFILE.VALUE('PO_VALIDATE_GL_PERIOD'),'Y');
943 IF (PO_LOG.d_stmt) THEN
944 PO_LOG.stmt(d_module, d_progress, 'l_validate_gl_period', l_validate_gl_period);
945 END IF;
946
947 -- Bug 9960752: Added another IF condition for exercised distribution
948 IF p_lines.clm_option_indicator_tbl(i) = 'O' AND NVL(p_lines.clm_exercised_flag_tbl(i),'N') = 'N' THEN
949 NULL;
950 ELSE
951 IF (NVL(p_lines.clm_info_flag_tbl(i),'N')<>'Y') THEN
952 IF (p_lines.requisition_line_id_tbl(i) IS NOT NULL ) THEN
953 -- if backing req exists, use req distributions as base for creating
954 -- po distributions.
955 d_progress := 30;
956 FND_PROFILE.GET('PO_AUTOCREATE_DATE', l_gl_date_option);
957
958
959
960 IF ((PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag = 'Y')
961 AND (l_gl_date_option <> 'REQ GL DATE')
962 AND (PO_AUTOCREATE_PARAMS.g_sys.period_name IS NULL)) THEN
963 -- derive period name if it isn't already known
964 d_progress := 40;
965
966 /*Bug13553227. eliminated the sql to derive the period name.
967 calling PO_PERIODS_SV.get_period_name to make it consistent.
968 If the get_period_name does not return a period then we will call build_GL_Encumbered_Date in case of PO:validate GL period = 'Redefault'*/
969 SELECT prd.set_of_books_id
970 INTO l_sob_id
971 FROM po_req_distributions_all prd
972 WHERE prd.requisition_line_id = p_lines.requisition_line_id_tbl(i)
973 AND ROWNUM = 1;
974
975 d_progress := 50;
976 PO_PERIODS_SV.get_period_name( x_sob_id => l_sob_id ,
977 x_gl_date => SYSDATE ,
978 x_gl_period => PO_AUTOCREATE_PARAMS.g_sys.period_name );
979
980 IF (PO_AUTOCREATE_PARAMS.g_sys.period_name IS NULL) THEN
981 d_progress := 60;
982 IF l_validate_gl_period = 'R' THEN
983
984 PO_PERIODS_SV.build_GL_Encumbered_Date(l_sob_id => l_sob_id
985 ,x_gl_date => x_gl_date
986 ,x_gl_period => PO_AUTOCREATE_PARAMS.g_sys.period_name);
987
988 END IF;
989 END IF;
990
991 IF (PO_LOG.d_stmt) THEN
992 PO_LOG.stmt(d_module, d_progress, 'PO_AUTOCREATE_PARAMS.g_sys.period_name', PO_AUTOCREATE_PARAMS.g_sys.period_name);
993 END IF;
994
995 IF (PO_AUTOCREATE_PARAMS.g_sys.period_name IS NULL) THEN
996 d_progress := 70;
997 IF (PO_LOG.d_exc) THEN
998 PO_LOG.exc(d_module, d_progress, 'Unable to find period name for SYSDATE');
999 END IF;
1000
1001 --CLM Phase 2 changes : error handling
1002 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_GL_PERIOD_ERR',x_token1_value => sqlerrm);
1003
1004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1005 END IF;
1006 /*Bug13553227 end */
1007 END IF; -- if params.po_encumbrance_flag = 'Y' AND ...
1008
1009 -- create distributions from req distributions
1010 d_progress := 70;
1011 --SQL WHAT: Create payitem distributions from backing req. distributions
1012 --SQL WHY : Create all such payitem distributions in one place
1013 INSERT
1014 INTO po_distributions_draft_all
1015 (
1016 po_distribution_id ,
1017 draft_id ,
1018 last_update_date ,
1019 last_updated_by ,
1020 last_update_login ,
1021 creation_date ,
1022 created_by ,
1023 po_header_id ,
1024 po_line_id ,
1025 line_location_id ,
1026 distribution_num ,
1027 req_distribution_id ,
1028 set_of_books_id ,
1029 code_combination_id ,
1030 deliver_to_location_id ,
1031 deliver_to_person_id ,
1032 destination_type_code ,
1033 destination_organization_id ,
1034 destination_subinventory ,
1035 project_id ,
1036 task_id ,
1037 award_id ,
1038 end_item_unit_number ,
1039 expenditure_type ,
1040 project_accounting_context ,
1041 destination_context ,
1042 expenditure_organization_id ,
1043 expenditure_item_date ,
1044 rate ,
1045 rate_date ,
1046 budget_account_id ,
1047 accrual_account_id ,
1048 variance_account_id ,
1049 accrued_flag ,
1050 encumbered_flag ,
1051 prevent_encumbrance_flag ,
1052 gl_encumbered_date ,
1053 gl_encumbered_period_name ,
1054 recovery_rate ,
1055 accrue_on_receipt_flag ,
1056 kanban_card_id ,
1057 org_id ,
1058 distribution_type ,
1059 quantity_ordered ,
1060 amount_ordered ,
1061 tax_attribute_update_code, --<eTax Integration R12>
1062 --partial funding attributes
1063 partial_funded_flag,
1064 funded_value,
1065 quantity_funded,
1066 amount_funded ,
1067 change_in_funded_value
1068 )
1069 SELECT PO_DISTRIBUTIONS_S.NEXTVAL ,
1070 po_autocreate_params.g_draft_id ,
1071 p_lines.last_update_date_tbl(i) ,
1072 p_lines.last_updated_by_tbl(i) ,
1073 p_lines.last_update_login_tbl(i) ,
1074 p_lines.creation_date_tbl(i) ,
1075 p_lines.created_by_tbl(i) ,
1076 p_lines.po_header_id_tbl(i) ,
1077 p_lines.po_line_id_tbl(i) ,
1078 poll.line_location_id ,
1079 prd.distribution_num ,
1080 prd.distribution_id ,
1081 prd.set_of_books_id ,
1082 prd.code_combination_id ,
1083 prl.deliver_to_location_id ,
1084 prl.to_person_id ,
1085 prl.destination_type_code ,
1086 prl.destination_organization_id ,
1087 prl.destination_subinventory ,
1088 prd.project_id ,
1089 prd.task_id ,
1090 prd.award_id ,
1091 prd.end_item_unit_number ,
1092 prd.expenditure_type ,
1093 prd.project_accounting_context ,
1094 prl.destination_context ,
1095 prd.expenditure_organization_id ,
1096 prd.expenditure_item_date ,
1097 p_lines.hd_rate_tbl(i) ,
1098 p_lines.hd_rate_date_tbl(i) ,
1099 DECODE(poll.shipment_type, 'PREPAYMENT', NULL, prd.budget_account_id) ,
1100 prd.accrual_account_id ,
1101 prd.variance_account_id ,
1102 'N' -- accrued_flag
1103 ,
1104 'N' -- encumbered_flag
1105 ,
1106 DECODE(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'Y', DECODE(poll.shipment_type, 'PREPAYMENT', 'Y', 'N'), NULL) -- prevent_encumbrance_flag
1107 ,
1108 (
1109 CASE -- gl_encumbered_date
1110 WHEN (PO_AUTOCREATE_PARAMS.g_sys.req_encumbrance_flag = 'Y'
1111 AND l_gl_date_option = 'REQ GL DATE')
1112 THEN prd.gl_encumbered_date
1113 WHEN (PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag = 'Y')
1114 THEN TRUNC(SYSDATE)
1115 ELSE NULL
1116 END) ,
1117 (
1118 CASE -- gl_encumbered_period_name
1119 WHEN (PO_AUTOCREATE_PARAMS.g_sys.req_encumbrance_flag = 'Y'
1120 AND l_gl_date_option = 'REQ GL DATE')
1121 THEN prd.gl_encumbered_period_name
1122 WHEN (PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag = 'Y')
1123 THEN PO_AUTOCREATE_PARAMS.g_sys.period_name
1124 ELSE NULL
1125 END) ,
1126 prd.recovery_rate ,
1127 poll.accrue_on_receipt_flag ,
1128 prl.kanban_card_id ,
1129 PO_AUTOCREATE_PARAMS.g_purchasing_ou_id ,
1130 poll.shipment_type ,
1131 (
1132 CASE -- quantity_ordered
1133 WHEN poll.value_basis <> 'QUANTITY'
1134 THEN NULL
1135 WHEN poll.payment_type IN ('MILESTONE', 'DELIVERY')
1136 THEN ROUND((prd.req_line_quantity / prl.quantity) * poll.quantity, 15)
1137 WHEN poll.payment_type = 'RATE'
1138 THEN ROUND((prd.req_line_amount / prl.amount) * poll.quantity, 15)
1139 END) ,
1140 (
1141 CASE -- amount_ordered
1142 WHEN poll.value_basis <> 'FIXED PRICE'
1143 THEN NULL
1144 ELSE ROUND((prd.req_line_amount / prl.amount) * poll.amount, x_precision)
1145 END) ,
1146 nvl2(PO_AUTOCREATE_PARAMS.g_calculate_tax_flag, 'CREATE', NULL) ,--<eTax integration R12>
1147 --partial funding attributes
1148 prd.partial_funded_flag,
1149 prd.funded_value,
1150 NULL, --prd.quantity_funded,
1151 NULL, --prd.amount_funded,
1152 prd.funded_value
1153 /*Bug 13598209 : PO_LINE_LOCATIONS_ALL has still not been populated
1154 Hence PO_LINE_LOCATIONS_DRAFT_ALL needs to be used*/
1155 FROM PO_LINE_LOCATIONS_DRAFT_ALL poll ,
1156 po_req_distributions_all prd ,
1157 po_requisition_lines_all prl
1158 WHERE poll.po_line_id = p_lines.po_line_id_tbl(i)
1159 AND prd.requisition_line_id = p_lines.requisition_line_id_tbl(i)
1160 AND prl.requisition_line_id = prd.requisition_line_id
1161 AND poll.payment_type <> 'ADVANCE';
1162
1163 IF (PO_LOG.d_stmt) THEN
1164 PO_LOG.stmt(d_module, d_progress, 'Payitems distributions created from requisition distributions.');
1165 END IF;
1166
1167 ELSE
1168 -- no backing req exists; create distributions for payitems
1169 -- from scratch. Accounts are not set here - they will
1170 -- be generated and populated later.
1171 IF ((PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag = 'Y') AND (PO_AUTOCREATE_PARAMS.g_sys.period_name IS NULL)) THEN
1172 -- derive period name if it isn't already known
1173 d_progress := 80;
1174 /*Bug13553227 start */
1175 PO_PERIODS_SV.get_period_name( x_sob_id => PO_AUTOCREATE_PARAMS.g_sys.sob_id ,
1176 x_gl_date => SYSDATE ,
1177 x_gl_period => PO_AUTOCREATE_PARAMS.g_sys.period_name );
1178
1179 IF (PO_AUTOCREATE_PARAMS.g_sys.period_name IS NULL) THEN
1180 d_progress := 85;
1181 IF l_validate_gl_period = 'R' THEN
1182
1183 PO_PERIODS_SV.build_GL_Encumbered_Date(l_sob_id => PO_AUTOCREATE_PARAMS.g_sys.sob_id
1184 ,x_gl_date => x_gl_date
1185 ,x_gl_period => PO_AUTOCREATE_PARAMS.g_sys.period_name);
1186
1187 END IF;
1188 END IF;
1189
1190 IF (PO_LOG.d_stmt) THEN
1191 PO_LOG.stmt(d_module, d_progress, 'PO_AUTOCREATE_PARAMS.g_sys.period_name', PO_AUTOCREATE_PARAMS.g_sys.period_name);
1192 END IF;
1193
1194 IF (PO_AUTOCREATE_PARAMS.g_sys.period_name IS NULL) THEN
1195 d_progress := 90;
1196 IF (PO_LOG.d_exc) THEN
1197 PO_LOG.exc(d_module, d_progress, 'Unable to find period name for SYSDATE');
1198 END IF;
1199
1200 --CLM Phase 2 changes : error handling
1201 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_GL_PERIOD_ERR',x_token1_value => sqlerrm);
1202
1203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1204 END IF;
1205 /*Bug13553227 end */
1206 END IF; -- if params.po_encumbrance_flag = 'Y' AND ...
1207
1208 d_progress := 100;
1209 --SQL WHAT: Create payitem distributions in the case of no backing req.
1210 --SQL WHY : Create all such payitem distributions in one place
1211 INSERT
1212 INTO po_distributions_draft_all
1213 (
1214 po_distribution_id ,
1215 draft_id ,
1216 last_update_date ,
1217 last_updated_by ,
1218 last_update_login ,
1219 creation_date ,
1220 created_by ,
1221 po_header_id ,
1222 po_line_id ,
1223 line_location_id ,
1224 distribution_num ,
1225 req_distribution_id ,
1226 deliver_to_location_id ,
1227 deliver_to_person_id ,
1228 destination_type_code ,
1229 destination_organization_id ,
1230 destination_subinventory ,
1231 rate ,
1232 rate_date ,
1233 accrued_flag ,
1234 encumbered_flag ,
1235 prevent_encumbrance_flag ,
1236 gl_encumbered_date ,
1237 gl_encumbered_period_name ,
1238 accrue_on_receipt_flag ,
1239 org_id ,
1240 distribution_type ,
1241 project_id ,
1242 task_id ,
1243 award_id ,
1244 end_item_unit_number ,
1245 expenditure_type ,
1246 project_accounting_context ,
1247 destination_context ,
1248 expenditure_organization_id ,
1249 expenditure_item_date ,
1250 quantity_ordered ,
1251 amount_ordered ,
1252 set_of_books_id ,
1253 tax_attribute_update_code --<eTax Integration R12>
1254 --partial funding attributes
1255 ,
1256 partial_funded_flag ,
1257 funded_value ,
1258 quantity_funded ,
1259 amount_funded ,
1260 change_in_funded_value
1261 )
1262 SELECT PO_DISTRIBUTIONS_S.NEXTVAL ,
1263 po_autocreate_params.g_draft_id ,
1264 p_lines.last_update_date_tbl(i) ,
1265 p_lines.last_updated_by_tbl(i) ,
1266 p_lines.last_update_login_tbl(i) ,
1267 p_lines.creation_date_tbl(i) ,
1268 p_lines.created_by_tbl(i) ,
1269 p_lines.po_header_id_tbl(i) ,
1270 p_lines.po_line_id_tbl(i) ,
1271 poll.line_location_id ,
1272 1 -- distribution_num
1273 ,
1274 NULL -- req_distribution_id
1275 ,
1276 poll.ship_to_location_id ,
1277 NULL -- deliver_to_person_id
1278 ,
1279 'EXPENSE' -- destination_type_code
1280 ,
1281 poll.ship_to_organization_id ,
1282 NULL -- destination_subinventory
1283 ,
1284 p_lines.hd_rate_tbl(i) ,
1285 p_lines.hd_rate_date_tbl(i) ,
1286 'N' -- accrued_flag
1287 ,
1288 'N' -- encumbered_flag
1289 ,
1290 DECODE(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag,
1291 'Y', DECODE(poll.shipment_type, 'PREPAYMENT', 'Y', 'N'), NULL) -- prevent_encumbrance_flag
1292 ,
1293 DECODE(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'Y', TRUNC(SYSDATE), NULL) ,
1294 DECODE(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'Y', PO_AUTOCREATE_PARAMS.g_sys.period_name, NULL) ,
1295 poll.accrue_on_receipt_flag ,
1296 PO_AUTOCREATE_PARAMS.g_purchasing_ou_id ,
1297 poll.shipment_type ,
1298 polli.project_id ,
1299 polli.task_id ,
1300 polli.award_id ,
1301 NULL -- end_item_unit_number
1302 ,
1303 polli.expenditure_type ,
1304 NULL -- project_accounting_context
1305 ,
1306 'EXPENSE' -- destination_context
1307 ,
1308 polli.expenditure_organization_id ,
1309 polli.expenditure_item_date ,
1310 poll.quantity ,
1311 poll.amount ,
1312 PO_AUTOCREATE_PARAMS.g_sys.sob_id ,
1313 nvl2(PO_AUTOCREATE_PARAMS.g_calculate_tax_flag, 'CREATE', NULL) --<eTax integration R12>
1314 --partial funding attributes
1315 ,
1316 'N' ,
1317 NULL ,
1318 DECODE(p_lines.order_type_lookup_code_tbl(i),'QUANTITY',poll.quantity) ,
1319 DECODE(p_lines.order_type_lookup_code_tbl(i),'AMOUNT',poll.amount) ,
1320 NULL
1321 /*Bug 13598209 : PO_LINE_LOCATIONS_ALL has still not been populated
1322 Hence PO_LINE_LOCATIONS_DRAFT_ALL needs to be used*/
1323 FROM PO_LINE_LOCATIONS_DRAFT_ALL poll ,
1324 po_line_locations_interface polli
1325 WHERE poll.po_line_id = p_lines.po_line_id_tbl(i)
1326 AND poll.line_location_id = polli.line_location_id
1327 AND poll.payment_type <> 'ADVANCE';
1328
1329 IF (PO_LOG.d_stmt) THEN
1330 PO_LOG.stmt(d_module, d_progress, 'Payitems distributions created from scratch.');
1331 END IF;
1332
1333 END IF; -- if p_req_line_id IS NOT NULL
1334
1335 d_progress := 110;
1336 IF (p_lines.has_advance_flag_tbl(i) = 'Y') THEN
1337 -- create advance distributions
1338 -- logic: copy distributions from first actuals payitem
1339 d_progress := 120;
1340 --SQL WHAT: Create payitem distributions for advance payitems
1341 --SQL WHY : Create all such payitem distributions in one place
1342 INSERT
1343 INTO po_distributions_draft_all
1344 (
1345 po_distribution_id ,
1346 draft_id ,
1347 last_update_date ,
1348 last_updated_by ,
1349 last_update_login ,
1350 creation_date ,
1351 created_by ,
1352 po_header_id ,
1353 po_line_id ,
1354 line_location_id ,
1355 distribution_num ,
1356 req_distribution_id ,
1357 deliver_to_location_id ,
1358 deliver_to_person_id ,
1359 destination_type_code ,
1360 destination_organization_id ,
1361 destination_subinventory ,
1362 rate ,
1363 rate_date ,
1364 accrued_flag ,
1365 encumbered_flag ,
1366 prevent_encumbrance_flag ,
1367 gl_encumbered_date ,
1368 gl_encumbered_period_name ,
1369 accrue_on_receipt_flag ,
1370 org_id ,
1371 distribution_type ,
1372 amount_ordered ,
1373 quantity_ordered ,
1374 project_id ,
1375 task_id ,
1376 award_id ,
1377 end_item_unit_number ,
1378 expenditure_type ,
1379 project_accounting_context ,
1380 destination_context ,
1381 expenditure_organization_id ,
1382 expenditure_item_date ,
1383 set_of_books_id ,
1384 tax_attribute_update_code --<eTax Integration R12>
1385 )
1386 SELECT PO_DISTRIBUTIONS_S.NEXTVAL ,
1387 po_autocreate_params.g_draft_id ,
1388 p_lines.last_update_date_tbl(i) ,
1389 p_lines.last_updated_by_tbl(i) ,
1390 p_lines.last_update_login_tbl(i) ,
1391 p_lines.creation_date_tbl(i) ,
1392 p_lines.created_by_tbl(i) ,
1393 p_lines.po_header_id_tbl(i) ,
1394 p_lines.po_line_id_tbl(i) ,
1395 adv.line_location_id ,
1396 pod.distribution_num -- distribution_num
1397 ,
1398 NULL -- req_distribution_id
1399 ,
1400 pod.deliver_to_location_id ,
1401 pod.deliver_to_person_id ,
1402 pod.destination_type_code ,
1403 pod.destination_organization_id ,
1404 pod.destination_subinventory ,
1405 pod.rate ,
1406 pod.rate_date ,
1407 pod.accrued_flag ,
1408 pod.encumbered_flag ,
1409 DECODE(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'Y', 'Y', NULL) --prevent_enc_flag
1410 ,
1411 NULL -- gl_encumbered_date
1412 ,
1413 NULL -- gl_encumbered_period_name
1414 ,
1415 adv.accrue_on_receipt_flag ,
1416 pod.org_id ,
1417 adv.shipment_type ,
1418 ROUND( -- amount_ordered
1419 (NVL(pod.amount_ordered, deliv.price_override * pod.quantity_ordered) / NVL(deliv.amount, deliv.price_override * deliv.quantity)) * adv.amount, 15) ,
1420 NULL -- quantity_ordered
1421 ,
1422 pod.project_id ,
1423 pod.task_id ,
1424 pod.award_id ,
1425 pod.end_item_unit_number ,
1426 pod.expenditure_type ,
1427 pod.project_accounting_context ,
1428 pod.destination_context ,
1429 pod.expenditure_organization_id ,
1430 pod.expenditure_item_date ,
1431 PO_AUTOCREATE_PARAMS.g_sys.sob_id ,
1432 nvl2(PO_AUTOCREATE_PARAMS.g_calculate_tax_flag, 'CREATE', NULL) --<eTax integration R12>
1433 /*Bug 13598209 : PO_LINE_LOCATIONS_ALL has still not been populated
1434 Hence PO_LINE_LOCATIONS_DRAFT_ALL needs to be used
1435 Similarly for po_distributions_all*/
1436 FROM PO_LINE_LOCATIONS_DRAFT_ALL adv,
1437 PO_LINE_LOCATIONS_DRAFT_ALL deliv,
1438 po_distributions_draft_all pod
1439 WHERE adv.po_line_id = p_lines.po_line_id_tbl(i)
1440 AND adv.payment_type = 'ADVANCE'
1441 AND deliv.line_location_id =
1442 (SELECT poll.line_location_id
1443 FROM PO_LINE_LOCATIONS_DRAFT_ALL poll
1444 WHERE poll.po_line_id = p_lines.po_line_id_tbl(i)
1445 AND poll.shipment_type = 'STANDARD'
1446 AND poll.shipment_num =
1447 (SELECT MIN(poll2.shipment_num)
1448 FROM PO_LINE_LOCATIONS_DRAFT_ALL poll2
1449 WHERE poll2.po_line_id = poll.po_line_id
1450 AND poll2.shipment_type = 'STANDARD'
1451 )
1452 )
1453 AND pod.line_location_id = deliv.line_location_id;
1454
1455 IF (PO_LOG.d_stmt) THEN
1456 PO_LOG.stmt(d_module, d_progress, 'Advance distributions created.');
1457 END IF;
1458
1459 END IF; -- if interface.has_advance_flag
1460
1461 d_progress := 130;
1462 -- calibrate last distribution for each pay item
1463 SELECT poll.line_location_id,
1464 poll.value_basis BULK COLLECT
1465 INTO l_line_loc_id_tbl,
1466 l_line_loc_value_basis_tbl
1467 /*Bug 13598209 : PO_LINE_LOCATIONS_ALL has still not been populated
1468 Hence PO_LINE_LOCATIONS_DRAFT_ALL needs to be used
1469 */
1470 FROM po_line_locations_draft_all poll
1471 WHERE poll.po_line_id = p_lines.po_line_id_tbl(i);
1472
1473 FOR i IN 1..l_line_loc_id_tbl.COUNT
1474 LOOP
1475 d_progress := 140;
1476 IF (l_line_loc_value_basis_tbl(i) = 'FIXED PRICE') THEN
1477 calibrate_last_dist_amount(l_line_loc_id_tbl(i));
1478 ELSE
1479 d_progress := 150;
1480 calibrate_last_dist_quantity(l_line_loc_id_tbl(i));
1481 END IF;
1482 END LOOP;
1483
1484 d_progress := 160;
1485 -- now, generate accounts for all distributions that are not
1486 -- tied to a backing req. distribution
1487 OPEN payitem_acct_gen_cursor(p_lines.po_line_id_tbl(i));
1488 LOOP
1489 FETCH payitem_acct_gen_cursor INTO payitem_acct_rec;
1490 EXIT
1491 WHEN payitem_acct_gen_cursor%NOTFOUND;
1492 d_progress := 170;
1493
1494 IF (PO_LOG.d_stmt) THEN
1495 PO_LOG.stmt(d_module, d_progress, 'Calling account generator wf method.');
1496 PO_LOG.stmt(d_module, d_progress, 'payitem_acct_rec.po_distribution_id', payitem_acct_rec.po_distribution_id);
1497 END IF;
1498
1499 SELECT agent_id
1500 INTO l_agent_id
1501 FROM po_headers_draft_all
1502 WHERE po_header_id = p_lines.po_header_id_tbl(i);
1503
1504 l_acct_api_success := PO_WF_BUILD_ACCOUNT_INIT.Start_Workflow( x_purchasing_ou_id => PO_AUTOCREATE_PARAMS.g_purchasing_ou_id ,
1505 x_transaction_flow_header_id => NULL ,
1506 x_dest_charge_success => l_dest_charge_success ,
1507 x_dest_charge_account_id => l_dest_charge_account_id ,
1508 x_dest_charge_account_desc => l_dest_charge_account_desc ,
1509 x_dest_charge_account_flex => l_dest_charge_account_flex ,
1510 x_dest_variance_success => l_dest_variance_success ,
1511 x_dest_variance_account_id => l_dest_variance_account_id ,
1512 x_dest_variance_account_desc => l_dest_charge_account_desc ,
1513 x_dest_variance_account_flex => l_dest_charge_account_flex ,
1514 x_charge_success => l_charge_success ,
1515 x_budget_success => l_budget_success ,
1516 x_accrual_success => l_accrual_success ,
1517 x_variance_success => l_variance_success ,
1518 x_code_combination_id => l_code_combination_id ,
1519 x_budget_account_id => l_budget_account_id ,
1520 x_accrual_account_id => l_accrual_account_id ,
1521 x_variance_account_id => l_variance_account_id ,
1522 x_charge_account_flex => l_charge_account_flex ,
1523 x_budget_account_flex => l_budget_account_flex ,
1524 x_accrual_account_flex => l_accrual_account_flex ,
1525 x_variance_account_flex => l_variance_account_flex ,
1526 x_charge_account_desc => l_charge_account_desc ,
1527 x_budget_account_desc => l_budget_account_desc ,
1528 x_accrual_account_desc => l_accrual_account_desc ,
1529 x_variance_account_desc => l_variance_account_desc ,
1530 x_coa_id => PO_AUTOCREATE_PARAMS.g_sys.coa_id ,
1531 x_bom_resource_id => NULL ,
1532 x_bom_cost_element_id => NULL ,
1533 x_category_id => p_lines.category_id_tbl(i) ,
1534 x_item_id => p_lines.item_id_tbl(i) ,
1535 x_type_lookup_code => PO_AUTOCREATE_PARAMS.g_document_subtype, --interface.document_subtype
1536 x_line_type_id => p_lines.line_type_id_tbl(i) ,
1537 x_agent_id => l_agent_id ,--p_lines.hd_agent_id_tbl(i)
1538 x_destination_type_code => payitem_acct_rec.destination_type_code ,
1539 x_deliver_to_location_id => payitem_acct_rec.deliver_to_location_id ,
1540 x_deliver_to_person_id => payitem_acct_rec.deliver_to_person_id ,
1541 x_destination_organization_id => payitem_acct_rec.destination_organization_id ,
1542 x_destination_subinventory => payitem_acct_rec.destination_subinventory ,
1543 x_expenditure_type => payitem_acct_rec.expenditure_type ,
1544 x_expenditure_organization_id => payitem_acct_rec.expenditure_organization_id ,
1545 x_expenditure_item_date => payitem_acct_rec.expenditure_item_date ,
1546 x_project_id => payitem_acct_rec.project_id ,
1547 x_task_id => payitem_acct_rec.task_id ,
1548 x_award_id => payitem_acct_rec.award_id ,
1549 x_from_type_lookup_code => NULL ,
1550 x_from_header_id => NULL ,
1551 x_from_line_id => NULL ,
1552 x_vendor_id => p_lines.hd_vendor_id_tbl(i) ,
1553 x_vendor_site_id => p_lines.hd_vendor_site_id_tbl(i) ,
1554 x_wip_entity_id => NULL ,
1555 x_wip_entity_type => NULL ,
1556 x_wip_line_id => NULL ,
1557 x_wip_repetitive_schedule_id => NULL ,
1558 x_wip_operation_seq_num => NULL ,
1559 x_wip_resource_seq_num => NULL ,
1560 x_po_encumberance_flag => PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag ,
1561 x_gl_encumbered_date => payitem_acct_rec.gl_encumbered_date ,
1562 x_result_billable_flag => NULL ,
1563 wf_itemkey => l_wf_itemkey ,
1564 x_new_combination => l_new_combination ,
1565 header_att1 => NULL ,
1566 header_att2 => NULL ,
1567 header_att3 => NULL ,
1568 header_att4 => NULL ,
1569 header_att5 => NULL ,
1570 header_att6 => NULL ,
1571 header_att7 => NULL ,
1572 header_att8 => NULL ,
1573 header_att9 => NULL ,
1574 header_att10 => NULL ,
1575 header_att11 => NULL ,
1576 header_att12 => NULL ,
1577 header_att13 => NULL ,
1578 header_att14 => NULL ,
1579 header_att15 => NULL ,
1580 line_att1 => NULL ,
1581 line_att2 => NULL ,
1582 line_att3 => NULL ,
1583 line_att4 => NULL ,
1584 line_att5 => NULL ,
1585 line_att6 => NULL ,
1586 line_att7 => NULL ,
1587 line_att8 => NULL ,
1588 line_att9 => NULL ,
1589 line_att10 => NULL ,
1590 line_att11 => NULL ,
1591 line_att12 => NULL ,
1592 line_att13 => NULL ,
1593 line_att14 => NULL ,
1594 line_att15 => NULL ,
1595 shipment_att1 => NULL ,
1596 shipment_att2 => NULL ,
1597 shipment_att3 => NULL ,
1598 shipment_att4 => NULL ,
1599 shipment_att5 => NULL ,
1600 shipment_att6 => NULL ,
1601 shipment_att7 => NULL ,
1602 shipment_att8 => NULL ,
1603 shipment_att9 => NULL ,
1604 shipment_att10 => NULL ,
1605 shipment_att11 => NULL ,
1606 shipment_att12 => NULL ,
1607 shipment_att13 => NULL ,
1608 shipment_att14 => NULL ,
1609 shipment_att15 => NULL ,
1610 distribution_att1 => NULL ,
1611 distribution_att2 => NULL ,
1612 distribution_att3 => NULL ,
1613 distribution_att4 => NULL ,
1614 distribution_att5 => NULL ,
1615 distribution_att6 => NULL ,
1616 distribution_att7 => NULL ,
1617 distribution_att8 => NULL ,
1618 distribution_att9 => NULL ,
1619 distribution_att10 => NULL ,
1620 distribution_att11 => NULL ,
1621 distribution_att12 => NULL ,
1622 distribution_att13 => NULL ,
1623 distribution_att14 => NULL ,
1624 distribution_att15 => NULL ,
1625 FB_ERROR_MSG => l_fb_error_msg ,
1626 p_func_unit_price => ROUND(payitem_acct_rec.price_override * NVL (payitem_acct_rec.rate, 1), NVL(x_ext_precision, 15)) ,
1627 p_distribution_type => payitem_acct_rec.distribution_type ,
1628 p_payment_type => payitem_acct_rec.payment_type );
1629
1630 d_progress := 180;
1631 IF (PO_LOG.d_stmt) THEN
1632 PO_LOG.stmt(d_module, d_progress, 'Finished account generator call.');
1633 PO_LOG.stmt(d_module, d_progress, 'l_acct_api_success', l_acct_api_success);
1634 PO_LOG.stmt(d_module, d_progress, 'l_charge_success', l_charge_success);
1635 PO_LOG.stmt(d_module, d_progress, 'l_variance_success', l_variance_success);
1636 PO_LOG.stmt(d_module, d_progress, 'l_budget_success', l_budget_success);
1637 PO_LOG.stmt(d_module, d_progress, 'l_accrual_success', l_accrual_success);
1638 END IF;
1639 -- follow same behavior as with shipment distributions:
1640 -- if account generator failed, do not create distribution
1641 d_progress := 190;
1642 IF ( l_acct_api_success AND l_charge_success
1643 --Bug 5645242: Added the check to test if the charge account id generated is null or 0,
1644 --if this is the case, then we should delete the distribution record.
1645 AND (NVL(l_code_combination_id , 0) <> 0) AND l_variance_success AND l_accrual_success AND (l_budget_success OR (NVL(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'N') <> 'Y'))) THEN
1646 d_progress := 200;
1647 IF (PO_LOG.d_stmt) THEN
1648 PO_LOG.stmt(d_module, d_progress, 'Updating dist. with acct. info');
1649 END IF;
1650 --SQL WHAT: Update account information for a po distribution
1651 --SQL WHY : PO Distributions that don't have a backing req need to
1652 -- have account defaulted from account generator.
1653 UPDATE po_distributions_draft_all pod
1654 SET pod.code_combination_id = l_code_combination_id ,
1655 pod.budget_account_id = DECODE(NVL(PO_AUTOCREATE_PARAMS.g_sys.po_encumbrance_flag, 'N'), 'Y', l_budget_account_id, NULL) ,
1656 pod.accrual_account_id = l_accrual_account_id ,
1657 pod.variance_account_id = l_variance_account_id
1658 WHERE pod.po_distribution_id = payitem_acct_rec.po_distribution_id
1659 AND pod.draft_id =po_autocreate_params.g_draft_id;
1660
1661 ELSE
1662 d_progress := 210;
1663
1664 IF (PO_LOG.d_stmt) THEN
1665 PO_LOG.stmt(d_module, d_progress, 'Deleting distribution - acct. gen failure');
1666 END IF;
1667
1668 DELETE
1669 FROM po_distributions_draft_all pod
1670 WHERE pod.po_distribution_id = payitem_acct_rec.po_distribution_id
1671 AND pod.draft_id =po_autocreate_params.g_draft_id;
1672
1673 END IF; -- if l_acct_api_success AND ...
1674
1675 END LOOP; -- payitem_acct_gen_cursor loop
1676
1677 CLOSE payitem_acct_gen_cursor;
1678 d_progress := 220;
1679
1680 IF (PO_LOG.d_stmt) THEN
1681 PO_LOG.stmt(d_module, d_progress, 'Calling update_award_distributions');
1682 END IF;
1683
1684 update_award_distributions( p_lines.intf_header_id_tbl(i) ,
1685 p_lines.intf_line_id_tbl(i) ,
1686 p_table_type => 'ALL' ,
1687 p_po_line_id => p_lines.po_line_id_tbl(i) );
1688 d_progress := 230;
1689 --should this also be po_distribution_draft_all
1690 --changing it to drafts
1691 SELECT pod.po_distribution_id BULK COLLECT
1692 INTO l_dist_id_tbl
1693 FROM po_distributions_draft_all pod
1694 WHERE pod.po_line_id = p_lines.po_line_id_tbl(i)
1695 AND pod.draft_id =po_autocreate_params.g_draft_id;
1696
1697 FOR i IN 1..l_dist_id_tbl.COUNT
1698 LOOP
1699 d_progress := 240;
1700 PO_AUTOCREATE_PVT.calculate_local('PO', 'DISTRIBUTION', l_dist_id_tbl(i));
1701 END LOOP;
1702
1703 IF (PO_LOG.d_proc) THEN
1704 PO_LOG.proc_end(d_module);
1705 END IF;
1706 END IF;
1707 END IF;
1708 END LOOP;
1709
1710 IF g_debug_stmt THEN
1711 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1712 END IF;
1713
1714 EXCEPTION
1715 WHEN OTHERS THEN
1716 IF (PO_LOG.d_exc) THEN
1717 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1718 END IF;
1719 IF (payitem_acct_gen_cursor%ISOPEN) THEN
1720 CLOSE payitem_acct_gen_cursor;
1721 END IF;
1722
1723 --CLM Phase 2 changes : error handling
1724 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_DEFLT_PAYITEM_ERR',x_token1_value => sqlerrm);
1725
1726 PO_AUTOCREATE_PVT.wrapup;
1727 RAISE;
1728 END create_payitem_dists;
1729
1730 /* ============================================================================
1731 Name: update_award_distributions
1732 Pre-reqs:
1733 None
1734 Modifies:
1735 PO_DISTRIBUTIONS_INTERFACE
1736 GMS_AWARD_DISTRIBUTIONS
1737 Locks:
1738 None
1739 Function:
1740 Calls Grants Accounting API to create new award distributions lines
1741 when a requisition with distributions that reference awards is
1742 autocreated into a PO.
1743 Parameters:
1744 <Complex Work R12>: Add p_table_type and p_po_line_id
1745 p_table_type
1746 'INTERFACE' - query/update interface tables (default)
1747 'ALL - query/update _ALL tables
1748 p_po_line_id
1749 Only necessary if p_table_type = 'ALL', this is the line for
1750 which to update the award distributions for.
1751 p_intf_header_id (IN) the interface_header_id
1752 p_intf_line_id the (IN) interface_line_id
1753 Both the above parameters are Used when p_table_type is INTERFACE.
1754 Returns:
1755 None
1756 Testing:
1757 None
1758 Caller of the Procedure:
1759 -derive_and_default_dists
1760 -create_payitem_dists
1761 ==============================================================================*/
1762 PROCEDURE update_award_distributions
1763 (
1764 p_intf_header_id NUMBER ,
1765 p_intf_line_id NUMBER ,
1766 p_table_type IN VARCHAR2 DEFAULT 'INTERFACE' ,
1767 p_po_line_id IN NUMBER DEFAULT NULL )
1768 IS
1769
1770 l_api_name CONSTANT VARCHAR(30) := 'update_award_distributions';
1771 l_return_status VARCHAR2(1);
1772 l_progress VARCHAR2(4) := '000';
1773 l_gms_po_interface_obj gms_po_interface_type;
1774 l_msg_count NUMBER;
1775 l_msg_data VARCHAR2(2000);
1776 l_msg_buf VARCHAR2(2000);
1777 l_intf_header_id NUMBER;
1778 l_intf_line_id NUMBER;
1779
1780 BEGIN
1781
1782 IF g_debug_stmt THEN
1783 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1784 END IF;
1785
1786 --assign value to local variable.
1787 l_intf_header_id := p_intf_header_id;
1788 l_intf_line_id := p_intf_line_id;
1789 -- <Complex Work R12 Start>
1790
1791 IF (p_table_type = 'ALL') THEN
1792 l_progress:='010';
1793 --SQL WHAT: For distributions with award_id references, select
1794 -- the columns that Grants needs from the
1795 -- po_distributions_all table
1796 --SQL WHY : Needed to call GMS API to update award distribution
1797 -- lines table.
1798 SELECT pod.po_distribution_id,
1799 pod.distribution_num,
1800 pod.project_id,
1801 pod.task_id,
1802 pod.award_id,
1803 NULL BULK COLLECT
1804 INTO l_gms_po_interface_obj.distribution_id,
1805 l_gms_po_interface_obj.distribution_num,
1806 l_gms_po_interface_obj.project_id,
1807 l_gms_po_interface_obj.task_id,
1808 l_gms_po_interface_obj.award_set_id_in,
1809 l_gms_po_interface_obj.award_set_id_out
1810 FROM po_distributions_draft_all pod
1811 WHERE pod.po_line_id = p_po_line_id
1812 AND pod.award_id IS NOT NULL
1813 AND pod.draft_id =po_autocreate_params.g_draft_id;
1814 ELSE
1815 l_progress:='020';
1816 --SQL WHAT: For distributions with award_id references, select
1817 -- the columns that Grants needs from the
1818 -- po_distributions_interface table
1819 --SQL WHY : Need to call GMS API to update award distribution
1820 -- lines table.
1821 SELECT po_distribution_id,
1822 distribution_num,
1823 project_id,
1824 task_id,
1825 award_id,
1826 NULL BULK COLLECT
1827 INTO l_gms_po_interface_obj.distribution_id,
1828 l_gms_po_interface_obj.distribution_num,
1829 l_gms_po_interface_obj.project_id,
1830 l_gms_po_interface_obj.task_id,
1831 l_gms_po_interface_obj.award_set_id_in,
1832 l_gms_po_interface_obj.award_set_id_out
1833 FROM PO_DISTRIBUTIONS_INTERFACE
1834 WHERE interface_header_id = l_intf_header_id
1835 AND interface_line_id = l_intf_line_id
1836 AND award_id IS NOT NULL;
1837 END IF; -- if p_table_type = 'ALL'
1838
1839 -- <Complex Work R12 End>
1840 IF SQL%NOTFOUND THEN
1841 RETURN;
1842 END IF;
1843 l_progress := '030';
1844 --Create new award distribution lines in GMS_AWARDS_DISTRIBUTIONS table
1845 PO_GMS_INTEGRATION_PVT.maintain_adl ( p_api_version => 1.0,
1846 x_return_status => l_return_status,
1847 x_msg_count => l_msg_count,
1848 x_msg_data => l_msg_data,
1849 p_caller => 'AUTOCREATE',
1850 x_po_gms_interface_obj => l_gms_po_interface_obj);
1851
1852 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1853 RAISE FND_API.G_EXC_ERROR;
1854 END IF;
1855
1856 -- <Complex Work R12 Start>
1857 IF (p_table_type = 'ALL') THEN
1858 l_progress := '040';
1859 --SQL WHAT: Update po_distributions_all table with the new
1860 -- award_id's
1861 --SQL WHY : award_id's in PO tables need to be synchronized with
1862 -- award_id's in GMS tables.
1863 FORALL i IN 1..l_gms_po_interface_obj.distribution_id.COUNT
1864 UPDATE po_distributions_draft_all
1865 SET award_id = l_gms_po_interface_obj.award_set_id_out(i)
1866 WHERE po_distribution_id = l_gms_po_interface_obj.distribution_id(i)
1867 AND draft_id =po_autocreate_params.g_draft_id;
1868 ELSE
1869 l_progress := '050';
1870 --SQL WHAT: Update po_distributions_interface table with the new
1871 -- award_id's
1872 --SQL WHY : award_id's in PO tables need to be synchronized with
1873 -- award_id's in GMS tables.
1874 FORALL i IN 1..l_gms_po_interface_obj.distribution_id.COUNT
1875 UPDATE po_distributions_interface
1876 SET award_id = l_gms_po_interface_obj.award_set_id_out(i)
1877 WHERE po_distribution_id = l_gms_po_interface_obj.distribution_id(i);
1878 END IF; -- if p_table_type = 'ALL'
1879 -- <Complex Work R12 End>
1880 IF g_debug_stmt THEN
1881 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1882 END IF;
1883
1884 EXCEPTION
1885 WHEN FND_API.G_EXC_ERROR THEN
1886 IF FND_MSG_PUB.check_msg_level( p_message_level => FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1887 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME, p_procedure_name => l_api_name);
1888 END IF;
1889 IF g_debug_stmt THEN
1890 FOR i IN 1..FND_MSG_PUB.count_msg
1891 LOOP
1892 l_msg_buf := SUBSTRB(FND_MSG_PUB.get(p_msg_index => i, p_encoded => FND_API.G_FALSE), 1, 2000);
1893 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'EXCEPTION: '|| l_msg_buf);
1894 END LOOP;
1895 END IF;
1896 --CLM Phase 2 changes : error handling
1897 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_GMS_INTG_ERR',x_token1_value => sqlerrm);
1898
1899 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1900 WHEN OTHERS THEN
1901 IF g_debug_unexp THEN
1902 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1903 END IF;
1904 IF FND_MSG_PUB.check_msg_level( p_message_level => FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1905 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME, p_procedure_name => l_api_name);
1906 END IF;
1907 --CLM Phase 2 changes : error handling
1908 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_UPD_AWARD_ERR',x_token1_value => sqlerrm);
1909
1910 RAISE;
1911 END update_award_distributions;
1912 ---------------------------------------------------------------------------
1913 --Start of Comments
1914 --Name: calibrate_last_dist_amount
1915 --Pre-reqs:
1916 -- None.
1917 --Modifies:
1918 -- PO_DISTRIBUTIONS_DRAFT_ALL.AMOUNT_ORDERED
1919 --Locks:
1920 -- None.
1921 --Function:
1922 -- This procedure is used to calibrate the amount of the last distribution
1923 -- belonging to a particular PO Shipment. After going through UOM/currency
1924 -- conversion and rounding, there is a chance that the sum of the distribution
1925 -- amounts will not add up to the shipment amount, causing submission checks
1926 -- to fail. To correct this, we will recalculate the last distribution
1927 -- amount as the difference between the shipment amount and the sum of
1928 -- all other distribution amounts.
1929 --Parameters:
1930 --IN:
1931 --p_line_location_id
1932 -- ID belonging to parent shipment of the distributions which need to be
1933 -- calibrated.
1934 --Testing:
1935 -- None.
1936 --Caller of the Procedure
1937 --create_payitem_dists
1938 --End of Comments
1939 -------------------------------------------------------------------------------
1940 PROCEDURE calibrate_last_dist_amount(p_line_location_id IN NUMBER )
1941 IS
1942
1943 l_api_name VARCHAR2(30) := 'calibrate_last_dist_amount';
1944 l_log_head VARCHAR2(100) := g_log_head || l_api_name;
1945 l_progress VARCHAR2(3);
1946 l_sum_dist_amounts PO_DISTRIBUTIONS_ALL.amount_ordered%TYPE;
1947 l_last_dist_amount PO_DISTRIBUTIONS_ALL.amount_ordered%TYPE;
1948 l_last_distribution_id PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE;
1949 l_shipment_amount PO_LINE_LOCATIONS_ALL.amount%TYPE;
1950
1951 BEGIN
1952
1953 l_progress:='000';
1954 PO_DEBUG.debug_begin(l_log_head);
1955
1956 l_progress:='010';
1957 PO_DEBUG.debug_var(l_log_head,l_progress,'p_line_location_id',p_line_location_id);
1958 -- Retrieve Distribution Data =============================================
1959 --
1960 -- Get the sum of all distribution amounts
1961 -- and the ID of the last distribution.
1962 --
1963 SELECT SUM(amount_ordered) ,
1964 MAX(po_distribution_id)
1965 INTO l_sum_dist_amounts ,
1966 l_last_distribution_id
1967 FROM po_distributions_draft_all
1968 WHERE line_location_id = p_line_location_id
1969 AND draft_id =po_autocreate_params.g_draft_id;
1970
1971 l_progress:='020';
1972 PO_DEBUG.debug_var(l_log_head,l_progress,'l_sum_dist_amounts',l_sum_dist_amounts);
1973 l_progress:='030';
1974 PO_DEBUG.debug_var(l_log_head,l_progress,'l_last_distribution_id',l_last_distribution_id);
1975 -- Get the shipment amount ================================================
1976 --
1977 SELECT amount
1978 INTO l_shipment_amount
1979 FROM po_line_locations_draft_all
1980 WHERE line_location_id = p_line_location_id;
1981
1982 l_progress:='040';
1983 PO_DEBUG.debug_var(l_log_head,l_progress,'l_shipment_amount',l_shipment_amount);
1984 -- Correct the last distribution ==========================================
1985 --
1986 -- Set it to the shipment amount minus the sum of all distribution
1987 -- amounts (except the last distribution).
1988 --
1989 UPDATE po_distributions_draft_all
1990 SET amount_ordered = l_shipment_amount - (l_sum_dist_amounts - amount_ordered)
1991 WHERE po_distribution_id = l_last_distribution_id
1992 AND draft_id =po_autocreate_params.g_draft_id RETURNING amount_ordered
1993 INTO l_last_dist_amount;
1994
1995 l_progress:='050';
1996 PO_DEBUG.debug_var(l_log_head,l_progress,'l_last_dist_amount',l_last_dist_amount);
1997 --=========================================================================
1998 l_progress:='060';
1999 PO_DEBUG.debug_end(l_log_head);
2000 EXCEPTION
2001 WHEN OTHERS THEN
2002 PO_DEBUG.debug_exc ( p_log_head => l_log_head , p_progress => l_progress);
2003
2004 --CLM Phase 2 changes : error handling
2005 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_CAL_AMT',x_token1_value => sqlerrm);
2006
2007 RAISE;
2008 END calibrate_last_dist_amount;
2009 -------------------------------------------------------------------------------
2010 --Start of Comments
2011 --Name: calibrate_last_dist_quantity
2012 --Pre-reqs:
2013 -- None.
2014 --Modifies:
2015 -- PO_DISTRIBUTIONS_DRAFT_ALL.QUANTITY_ORDERED
2016 --Locks:
2017 -- None.
2018 --Function:
2019 -- This procedure is used to calibrate the quantity of the last distribution
2020 -- belonging to a particular PO Shipment. After going through UOM/currency
2021 -- conversion and rounding, there is a chance that the sum of the distribution
2022 -- quantitiess will not add up to the shipment qty, causing submission checks
2023 -- to fail. To correct this, we will recalculate the last distribution
2024 -- quantity as the difference between the shipment quantity and the sum of
2025 -- all other distribution quantities.
2026 --Parameters:
2027 --IN:
2028 --p_line_location_id
2029 -- ID belonging to parent shipment of the distributions which need to be
2030 -- calibrated.
2031 --Testing:
2032 -- None.
2033 --Caller of the Procedure
2034 --create_payitem_dists
2035 --End of Comments
2036 -------------------------------------------------------------------------------
2037 PROCEDURE calibrate_last_dist_quantity(p_line_location_id IN NUMBER )
2038 IS
2039
2040 d_module VARCHAR2(70) := 'po.plsql.PO_INTERFACE_S.calibrate_last_dist_quantity';
2041 d_progress NUMBER;
2042 l_sum_dist_quantities PO_DISTRIBUTIONS_ALL.quantity_ordered%TYPE;
2043 l_last_dist_id PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE;
2044 l_last_dist_qty PO_DISTRIBUTIONS_ALL.quantity_ordered%TYPE;
2045 l_shipment_quantity PO_LINE_LOCATIONS_ALL.quantity%TYPE;
2046
2047 BEGIN
2048
2049 d_progress := 0;
2050 IF (PO_LOG.d_proc) THEN
2051 PO_LOG.proc_begin(d_module);
2052 PO_LOG.proc_begin(d_module, 'p_line_location_id', p_line_location_id);
2053 END IF;
2054
2055 d_progress := 10;
2056 SELECT SUM(pod.quantity_ordered),
2057 MAX(pod.po_distribution_id)
2058 INTO l_sum_dist_quantities,
2059 l_last_dist_id
2060 FROM po_distributions_draft_all pod
2061 WHERE pod.line_location_id = p_line_location_id
2062 AND pod.draft_id =po_autocreate_params.g_draft_id;
2063
2064 d_progress := 20;
2065 IF (PO_LOG.d_stmt) THEN
2066 PO_LOG.stmt(d_module, d_progress, 'l_last_dist_id', l_last_dist_id);
2067 END IF;
2068 SELECT poll.quantity
2069 INTO l_shipment_quantity
2070 FROM po_line_locations_draft_all poll
2071 WHERE poll.line_location_id = p_line_location_id
2072 AND draft_id =po_autocreate_params.g_draft_id;
2073
2074 d_progress := 30;
2075 UPDATE po_distributions_draft_all pod
2076 SET pod.quantity_ordered = l_shipment_quantity - (l_sum_dist_quantities - pod.quantity_ordered)
2077 WHERE pod.po_distribution_id = l_last_dist_id
2078 AND draft_id =po_autocreate_params.g_draft_id RETURNING pod.quantity_ordered
2079 INTO l_last_dist_qty;
2080 IF (PO_LOG.d_proc) THEN
2081 PO_LOG.proc_end(d_module, 'l_last_dist_qty', l_last_dist_qty);
2082 PO_LOG.proc_end(d_module);
2083 END IF;
2084
2085 EXCEPTION
2086 WHEN OTHERS THEN
2087 IF (PO_LOG.d_exc) THEN
2088 PO_LOG.exc(d_module, d_progress, SQLCODE||SQLERRM);
2089 END IF;
2090 --CLM Phase 2 changes : error handling
2091 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_CAL_QTY',x_token1_value => sqlerrm);
2092
2093 RAISE;
2094 END calibrate_last_dist_quantity;
2095
2096 -- ACRN proj changes
2097 -------------------------------------------------------------------------------
2098 --Start of Comments
2099 --Name: Default_Acrn_Values
2100 --Pre-reqs:
2101 -- None.
2102 --Modifies:
2103 -- PO_DISTRIBUTIONS_DRAFT_ALL.ACRN
2104 --Locks:
2105 -- None.
2106 --Function:
2107 -- This procedure calls the API po_account_helper.BUILD_ACRN to populate
2108 -- the ACRN values in the po_distributions_draft_all.
2109 --Caller of the Procedure
2110 --PO_AUTOCREATE_MAINPROC_PVT.process_distributions
2111 --End of Comments
2112 -------------------------------------------------------------------------------
2113 PROCEDURE Default_Acrn_Values
2114 IS
2115 l_dist_id_tbl po_tbl_number;
2116 l_charge_acc_tbl po_tbl_number;
2117 l_ACRN_tbl po_tbl_varchar30;
2118 x_ACRN_tbl po_tbl_varchar30;
2119 L_LOA_TBL PO_TBL_VARCHAR30;
2120 L_CHANGE_STAT_TBL PO_TBL_VARCHAR30;
2121 X_RETURN_STATUS VARCHAR2(1);
2122 X_RETURN_MESSAGE PO_TBL_VARCHAR2000;
2123 X_MESSAGE_TOKEN PO_TBL_VARCHAR2000;
2124 l_progress VARCHAR2(3) := '000';
2125 l_api_name VARCHAR2(30) := 'Default_Acrn_Values';
2126 l_module VARCHAR2(40) := 'po.plsql.PO_AUTO_DIST_PROCESS_PVT';
2127
2128 BEGIN
2129
2130 l_progress := '010';
2131 --Changing the nvl N/A to null to align as per the new design of build_acrn
2132 SELECT pdd.po_distribution_id,
2133 pdd.code_combination_id,
2134 NVL(pdd.ACRN, '')
2135 , CLM_MISC_LOA,CHANGE_STATUS
2136 BULK COLLECT
2137 INTO l_dist_id_tbl,
2138 l_charge_acc_tbl,
2139 l_ACRN_tbl
2140 ,L_LOA_TBL,L_CHANGE_STAT_TBL
2141
2142 FROM po_distributions_draft_all pdd
2143 WHERE pdd.po_header_id = PO_AUTOCREATE_PARAMS.g_po_header_id
2144 AND pdd.draft_id = PO_AUTOCREATE_PARAMS.g_draft_id
2145 ORDER BY pdd.po_distribution_id ;
2146
2147 -- Calling buil_acrn API
2148 IF (PO_LOG.d_stmt) THEN
2149 PO_LOG.stmt(l_module||l_api_name, l_progress, 'calling BUILD_ACRN');
2150 END IF;
2151 l_progress := '020';
2152 --ACRN Regenerate changes begin
2153 po_account_helper.BUILD_ACRN (PO_AUTOCREATE_PARAMS.g_po_header_id,
2154 l_dist_id_tbl,
2155 l_charge_acc_tbl,
2156 l_ACRN_tbl,
2157 L_LOA_TBL,
2158 L_CHANGE_STAT_TBL,
2159 'N',
2160 x_ACRN_tbl,
2161 X_RETURN_STATUS,
2162 X_RETURN_MESSAGE,
2163 X_MESSAGE_TOKEN);
2164 --ACRN Regenerate changes End
2165 l_ACRN_tbl := x_ACRN_tbl;
2166
2167 IF (PO_LOG.d_stmt) THEN
2168 PO_LOG.stmt(l_module||l_api_name, l_progress, 'after BUILD_ACRN');
2169 END IF;
2170
2171 FOR j IN 1..l_dist_id_tbl.Count
2172 LOOP
2173 l_progress := '030';
2174 UPDATE po_distributions_draft_all
2175 SET ACRN = l_ACRN_tbl(j)
2176 WHERE po_distribution_id = l_dist_id_tbl(j)
2177 AND l_ACRN_tbl(j) <> '';
2178 END LOOP;
2179
2180 EXCEPTION
2181 WHEN OTHERS THEN
2182 IF (PO_LOG.d_exc) THEN
2183 PO_LOG.exc(l_module, l_progress, SQLCODE||SQLERRM);
2184 END IF;
2185
2186 --CLM Phase 2 changes : error handling
2187 PO_AUTOCREATE_PVT.report_error('PO_AUTO_DIST_ACRN_ERR',x_token1_value => sqlerrm);
2188
2189 RAISE;
2190 END Default_Acrn_Values;
2191 -- End ACRN
2192 END PO_AUTO_DIST_PROCESS_PVT;