[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_DIST_PROCESS_PVT
Source
1 PACKAGE BODY po_pdoi_dist_process_pvt AS
2 /* $Header: PO_PDOI_DIST_PROCESS_PVT.plb 120.30.12020000.5 2013/05/30 11:55:32 gjyothi ship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_DIST_PROCESS_PVT');
6
7 g_sys_accrual_account_id NUMBER;
8 g_mtl_accrual_account_id_tbl DBMS_SQL.NUMBER_TABLE;
9 g_mtl_variance_account_id_tbl DBMS_SQL.NUMBER_TABLE;
10
11 --------------------------------------------------------------------------
12 ---------------------- PRIVATE PROCEDURES PROTOTYPE ----------------------
13 --------------------------------------------------------------------------
14 PROCEDURE derive_ship_to_ou_id
15 (
16 p_key IN po_session_gt.key%TYPE,
17 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
18 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
19 x_ship_to_ou_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
20 );
21
22 PROCEDURE derive_deliver_to_loc_id
23 (
24 p_key IN po_session_gt.key%TYPE,
25 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
26 p_deliver_to_loc_tbl IN PO_TBL_VARCHAR100,
27 x_deliver_to_loc_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
28 );
29
30 PROCEDURE derive_deliver_to_person_id
31 (
32 p_key IN po_session_gt.key%TYPE,
33 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
34 p_person_name_tbl IN PO_TBL_VARCHAR2000,
35 x_person_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
36 );
37
38 PROCEDURE derive_dest_type_code
39 (
40 p_key IN po_session_gt.key%TYPE,
41 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
42 p_dest_type_tbl IN PO_TBL_VARCHAR30,
43 x_dest_type_code_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
44 );
45
46 PROCEDURE derive_dest_org_id
47 (
48 p_key IN po_session_gt.key%TYPE,
49 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
50 p_dest_org_tbl IN PO_TBL_VARCHAR100,
51 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
52 x_dest_org_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
53 );
54
55 PROCEDURE derive_wip_entity_id
56 (
57 p_key IN po_session_gt.key%TYPE,
58 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
59 p_wip_entity_tbl IN PO_TBL_VARCHAR2000,
60 p_dest_org_id_tbl IN PO_TBL_NUMBER,
61 p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
62 x_wip_entity_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
63 );
64
65 PROCEDURE derive_wip_line_id
66 (
67 p_key IN po_session_gt.key%TYPE,
68 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
69 p_wip_line_code_tbl IN PO_TBL_VARCHAR30,
70 p_dest_org_id_tbl IN PO_TBL_NUMBER,
71 p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
72 x_wip_line_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
73 );
74
75 PROCEDURE derive_ship_to_ou_coa_id
76 (
77 p_key IN po_session_gt.key%TYPE,
78 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
79 p_dest_org_id_tbl IN PO_TBL_NUMBER,
80 p_txn_flow_header_id_tbl IN PO_TBL_NUMBER,
81 p_dest_charge_account_id_tbl IN PO_TBL_NUMBER,
82 x_ship_to_ou_coa_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
83 );
84
85 PROCEDURE derive_bom_resource_id
86 (
87 p_key IN po_session_gt.key%TYPE,
88 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
89 p_bom_resource_code_tbl IN PO_TBL_VARCHAR30,
90 p_dest_org_id_tbl IN PO_TBL_NUMBER,
91 x_bom_resource_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
92 );
93
94 PROCEDURE validate_null_for_project_info
95 (
96 p_index IN NUMBER,
97 x_dists IN OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
98 );
99
100 PROCEDURE derive_project_info
101 (
102 p_key IN po_session_gt.key%TYPE,
103 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
104 p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
105 x_dists IN OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
106 );
107
108 PROCEDURE derive_project_id
109 (
110 p_key IN po_session_gt.key%TYPE,
111 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
112 p_project_tbl IN PO_TBL_VARCHAR30,
113 p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
114 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
115 p_ship_to_ou_id_tbl IN PO_TBL_NUMBER,
116 p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
117 x_project_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
118 );
119
120 PROCEDURE derive_task_id
121 (
122 p_key IN po_session_gt.key%TYPE,
123 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
124 p_task_tbl IN PO_TBL_VARCHAR2000, --- Bug#16505809
125 p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
126 p_project_id_tbl IN PO_TBL_NUMBER,
127 p_ship_to_ou_id_tbl IN PO_TBL_NUMBER,
128 p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
129 x_task_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
130 );
131
132 PROCEDURE derive_expenditure_type
133 (
134 p_key IN po_session_gt.key%TYPE,
135 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
136 p_expenditure_tbl IN PO_TBL_VARCHAR100,
137 p_project_id_tbl IN PO_TBL_NUMBER,
138 p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
139 x_expenditure_type_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
140 );
141
142 PROCEDURE derive_expenditure_org_id
143 (
144 p_key IN po_session_gt.key%TYPE,
145 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
146 p_expenditure_org_tbl IN PO_TBL_VARCHAR100,
147 p_project_id_tbl IN PO_TBL_NUMBER,
148 p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
149 x_expenditure_org_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
150 );
151
152 PROCEDURE add_account_segment_clause
153 ( p_segment_name IN VARCHAR2,
154 p_segment_value IN VARCHAR2,
155 x_sql IN OUT NOCOPY VARCHAR2
156 );
157
158
159 PROCEDURE get_item_status
160 (
161 p_key IN po_session_gt.key%TYPE,
162 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
163 p_item_id_tbl IN PO_TBL_NUMBER,
164 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
165 x_item_status_tbl OUT NOCOPY PO_TBL_VARCHAR1
166 );
167
168 PROCEDURE default_account_ids
169 (
170 p_dest_type_code IN VARCHAR2,
171 p_dest_org_id IN NUMBER,
172 p_dest_subinventory IN VARCHAR2,
173 p_item_id IN NUMBER,
174 p_po_encumbrance_flag IN VARCHAR2,
175 p_charge_account_id IN NUMBER,
176 x_accrual_account_id IN OUT NOCOPY NUMBER,
177 x_budget_account_id IN OUT NOCOPY NUMBER,
178 x_variance_account_id IN OUT NOCOPY NUMBER,
179 x_entity_type IN NUMBER, /* Encumbrance Project */
180 x_wip_entity_id IN NUMBER /* Encumbrance Project */
181 );
182
183 PROCEDURE populate_error_flag
184 (
185 x_results IN po_validation_results_type,
186 x_dists IN OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
187 );
188
189 --------------------------------------------------------------------------
190 ---------------------- PUBLIC PROCEDURES ---------------------------------
191 --------------------------------------------------------------------------
192
193 -----------------------------------------------------------------------
194 --Start of Comments
195 --Name: open_dists
196 --Function:
197 -- Open cursor for query.
198 -- This query retrieves the distribution attributes and related header,
199 -- line and location attributes for processing
200 --Parameters:
201 --IN:
202 -- p_max_intf_dist_id
203 -- maximal interface_distribution_id processed so far
204 -- The query will only retrieve the distribution records which have
205 -- not been processed
206 --IN OUT:
207 -- x_dists_csr
208 -- cursor variable to hold pointer to current processing row in the result
209 -- set returned by the query
210 --OUT:
211 --End of Comments
212 ------------------------------------------------------------------------
213 PROCEDURE open_dists
214 (
215 p_max_intf_dist_id IN NUMBER,
216 x_dists_csr OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type
217 ) IS
218
219 d_api_name CONSTANT VARCHAR2(30) := 'open_dists';
220 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
221 d_position NUMBER;
222
223 BEGIN
224 d_position := 0;
225
226 IF (PO_LOG.d_proc) THEN
227 PO_LOG.proc_begin(d_module, 'p_max_intf_dist_id', p_max_intf_dist_id);
228 END IF;
229
230 OPEN x_dists_csr FOR
231 SELECT /*+ INDEX (intf_dists PO_DISTRIBUTIONS_INTERFACE_N1) */
232 intf_dists.interface_distribution_id,
233 intf_dists.interface_header_id,
234 intf_dists.interface_line_id,
235 intf_dists.interface_line_location_id,
236 intf_dists.po_distribution_id,
237 intf_dists.distribution_num,
238 intf_dists.deliver_to_location,
239 intf_dists.deliver_to_location_id,
240 intf_dists.deliver_to_person_full_name,
241 intf_dists.deliver_to_person_id,
242 intf_dists.destination_type,
243 intf_dists.destination_type_code,
244 intf_dists.destination_organization,
245 intf_dists.destination_organization_id,
246 intf_dists.wip_entity,
247 intf_dists.wip_entity_id,
248 intf_dists.wip_line_code,
249 intf_dists.wip_line_id,
250 intf_dists.bom_resource_code,
251 intf_dists.bom_resource_id,
252 intf_dists.charge_account,
253 intf_dists.charge_account_id,
254 intf_dists.dest_charge_account_id,
255 intf_dists.project_accounting_context,
256 intf_dists.award_number,
257 intf_dists.award_id,
258 intf_dists.project,
259 intf_dists.project_id,
260 intf_dists.task,
261 intf_dists.task_id,
262 intf_dists.expenditure,
263 intf_dists.expenditure_type,
264 intf_dists.expenditure_organization,
265 intf_dists.expenditure_organization_id,
266 intf_dists.expenditure_item_date,
267 intf_dists.end_item_unit_number,
268 intf_dists.destination_context,
269 intf_dists.gl_encumbered_date,
270 intf_dists.gl_encumbered_period_name,
271 intf_dists.variance_account_id,
272 intf_dists.accrual_account_id,
273 intf_dists.budget_account_id,
274 intf_dists.dest_variance_account_id,
275 intf_dists.destination_subinventory,
276 intf_dists.amount_ordered,
277 intf_dists.quantity_ordered,
278 intf_dists.wip_repetitive_schedule_id,
279 intf_dists.wip_operation_seq_num,
280 intf_dists.wip_resource_seq_num,
281 intf_dists.prevent_encumbrance_flag,
282 intf_dists.recovery_rate,
283 intf_dists.tax_recovery_override_flag,
284 intf_dists.charge_account_segment1,
285 intf_dists.charge_account_segment2,
286 intf_dists.charge_account_segment3,
287 intf_dists.charge_account_segment4,
288 intf_dists.charge_account_segment5,
289 intf_dists.charge_account_segment6,
290 intf_dists.charge_account_segment7,
291 intf_dists.charge_account_segment8,
292 intf_dists.charge_account_segment9,
293 intf_dists.charge_account_segment10,
294 intf_dists.charge_account_segment11,
295 intf_dists.charge_account_segment12,
296 intf_dists.charge_account_segment13,
297 intf_dists.charge_account_segment14,
298 intf_dists.charge_account_segment15,
299 intf_dists.charge_account_segment16,
300 intf_dists.charge_account_segment17,
301 intf_dists.charge_account_segment18,
302 intf_dists.charge_account_segment19,
303 intf_dists.charge_account_segment20,
304 intf_dists.charge_account_segment21,
305 intf_dists.charge_account_segment22,
306 intf_dists.charge_account_segment23,
307 intf_dists.charge_account_segment24,
308 intf_dists.charge_account_segment25,
309 intf_dists.charge_account_segment26,
310 intf_dists.charge_account_segment27,
311 intf_dists.charge_account_segment28,
312 intf_dists.charge_account_segment29,
313 intf_dists.charge_account_segment30,
314 intf_dists.attribute1,
315 intf_dists.attribute2,
316 intf_dists.attribute3,
317 intf_dists.attribute4,
318 intf_dists.attribute5,
319 intf_dists.attribute6,
320 intf_dists.attribute7,
321 intf_dists.attribute8,
322 intf_dists.attribute9,
323 intf_dists.attribute10,
324 intf_dists.attribute11,
325 intf_dists.attribute12,
326 intf_dists.attribute13,
327 intf_dists.attribute14,
328 intf_dists.attribute15,
329
330 -- CLM Partial Funding Changes
331 NVL(intf_dists.partial_funded_flag,'N'),
332 intf_dists.funded_value,
333
334 -- ACRN proj
335 intf_dists.ACRN,
336
337 -- standard who columns
338 intf_dists.last_updated_by,
339 intf_dists.last_update_date,
340 intf_dists.last_update_login,
341 intf_dists.creation_date,
342 intf_dists.created_by,
343 intf_dists.request_id,
344 intf_dists.program_application_id,
345 intf_dists.program_id,
346 intf_dists.program_update_date,
347
348 -- attributes read from line location record
349 draft_locs.ship_to_organization_id,
350 draft_locs.line_location_id,
351 draft_locs.shipment_type,
352 draft_locs.transaction_flow_header_id,
353 draft_locs.accrue_on_receipt_flag,
354 draft_locs.need_by_date,
355 draft_locs.promised_date,
356 draft_locs.price_override,
357 draft_locs.outsourced_assembly,
358 draft_locs.attribute1,
359 draft_locs.attribute2,
360 draft_locs.attribute3,
361 draft_locs.attribute4,
362 draft_locs.attribute5,
363 draft_locs.attribute6,
364 draft_locs.attribute7,
365 draft_locs.attribute8,
366 draft_locs.attribute9,
367 draft_locs.attribute10,
368 draft_locs.attribute11,
369 draft_locs.attribute12,
370 draft_locs.attribute13,
371 draft_locs.attribute14,
372 draft_locs.attribute15,
373
374 -- attributes read from line record
375 Nvl(intf_locs.value_basis, draft_lines.order_type_lookup_code), -- PDOI for Complex PO Project
376 draft_lines.purchase_basis,
377 draft_lines.item_id,
378 draft_lines.category_id,
379 draft_lines.line_type_id,
380 draft_lines.po_line_id,
381 draft_lines.attribute1,
382 draft_lines.attribute2,
383 draft_lines.attribute3,
384 draft_lines.attribute4,
385 draft_lines.attribute5,
386 draft_lines.attribute6,
387 draft_lines.attribute7,
388 draft_lines.attribute8,
389 draft_lines.attribute9,
390 draft_lines.attribute10,
391 draft_lines.attribute11,
392 draft_lines.attribute12,
393 draft_lines.attribute13,
394 draft_lines.attribute14,
395 draft_lines.attribute15,
396
397 -- attributes read from header record
398 intf_headers.draft_id,
399 NVL(draft_headers.agent_id, txn_headers.agent_id),
400 draft_lines.po_header_id,
401 NVL(draft_headers.rate_date, txn_headers.rate_date),
402 NVL(draft_headers.rate, txn_headers.rate),
403 NVL(draft_headers.type_lookup_code, txn_headers.type_lookup_code),
404 NVL(draft_headers.vendor_id, txn_headers.vendor_id),
405 NVL(draft_headers.attribute1, txn_headers.attribute1),
406 NVL(draft_headers.attribute2, txn_headers.attribute2),
407 NVL(draft_headers.attribute3, txn_headers.attribute3),
408 NVL(draft_headers.attribute4, txn_headers.attribute4),
409 NVL(draft_headers.attribute5, txn_headers.attribute5),
410 NVL(draft_headers.attribute6, txn_headers.attribute6),
411 NVL(draft_headers.attribute7, txn_headers.attribute7),
412 NVL(draft_headers.attribute8, txn_headers.attribute8),
413 NVL(draft_headers.attribute9, txn_headers.attribute9),
414 NVL(draft_headers.attribute10, txn_headers.attribute10),
415 NVL(draft_headers.attribute11, txn_headers.attribute11),
416 NVL(draft_headers.attribute12, txn_headers.attribute12),
417 NVL(draft_headers.attribute13, txn_headers.attribute13),
418 NVL(draft_headers.attribute14, txn_headers.attribute14),
419 NVL(draft_headers.attribute15, txn_headers.attribute15),
420
421 -- CLM Partial Funding Changes
422 draft_headers.style_id,
423
424 -- set initial value for error_flag
425 FND_API.g_FALSE,
426
427 NULL, -- gms_txn_required_flag
428 NULL, -- tax_attribute_update_code
429 NULL, -- ship_to_ou_coa_id_tbl
430 NULL, -- award_set_id (bug5201306)
431 --clm pdoi integration
432 intf_dists.clm_defence_funding,
433 intf_dists.clm_fms_case_number,
434 intf_dists.clm_agency_acct_identifier,
435 intf_dists.group_line_id,
436 intf_dists.quantity_funded,
437 intf_dists.amount_funded,
438 intf_dists.clm_misc_loa
439
440 FROM po_distributions_interface intf_dists,
441 po_line_locations_interface intf_locs,
442 po_headers_interface intf_headers,
443 po_line_locations_draft_all draft_locs,
444 po_lines_draft_all draft_lines,
445 po_headers_draft_all draft_headers,
446 po_headers_all txn_headers
447 WHERE intf_dists.interface_line_location_id =
448 intf_locs.interface_line_location_id
449 AND intf_dists.interface_header_id = intf_headers.interface_header_id
450 AND intf_locs.line_location_id = draft_locs.line_location_id
451 AND intf_headers.draft_id = draft_locs.draft_id
452 AND draft_locs.po_line_id = draft_lines.po_line_id
453 AND draft_locs.draft_id = draft_lines.draft_id
454 AND draft_lines.po_header_id = draft_headers.po_header_id(+)
455 AND draft_lines.draft_id = draft_headers.draft_id(+)
456 AND draft_lines.po_header_id = txn_headers.po_header_id(+)
457 AND intf_dists.processing_id = PO_PDOI_PARAMS.g_processing_id
458 AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
459 AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
460 AND intf_dists.interface_distribution_id > p_max_intf_dist_id
461
462 ORDER BY intf_dists.interface_distribution_id;
463
464 IF (PO_LOG.d_proc) THEN
465 PO_LOG.proc_end (d_module);
466 END IF;
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 PO_MESSAGE_S.add_exc_msg
471 (
472 p_pkg_name => d_pkg_name,
473 p_procedure_name => d_api_name || '.' || d_position
474 );
475 RAISE;
476 END open_dists;
477
478 -----------------------------------------------------------------------
479 --Start of Comments
480 --Name: fetch_dists
481 --Function:
482 -- fetch results in batch
483 --Parameters:
484 --IN:
485 --IN OUT:
486 --x_dists_csr
487 -- cursor variable that hold pointers to currently processing row
488 --x_dists
489 -- record variable to hold distribution info within a batch
490 --OUT:
491 --End of Comments
492 ------------------------------------------------------------------------
493 PROCEDURE fetch_dists
494 (
495 x_dists_csr IN OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type,
496 x_dists OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
497 ) IS
498
499 d_api_name CONSTANT VARCHAR2(30) := 'fetch_dists';
500 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
501 d_position NUMBER;
502
503 BEGIN
504 d_position := 0;
505
506 IF (PO_LOG.d_proc) THEN
507 PO_LOG.proc_begin(d_module);
508 END IF;
509
510 FETCH x_dists_csr BULK COLLECT INTO
511 x_dists.intf_dist_id_tbl,
512 x_dists.intf_header_id_tbl,
513 x_dists.intf_line_id_tbl,
514 x_dists.intf_line_loc_id_tbl,
515 x_dists.po_dist_id_tbl,
516 x_dists.dist_num_tbl,
517 x_dists.deliver_to_loc_tbl,
518 x_dists.deliver_to_loc_id_tbl,
519 x_dists.deliver_to_person_name_tbl,
520 x_dists.deliver_to_person_id_tbl,
521 x_dists.dest_type_tbl,
522 x_dists.dest_type_code_tbl,
523 x_dists.dest_org_tbl,
524 x_dists.dest_org_id_tbl,
525 x_dists.wip_entity_tbl,
526 x_dists.wip_entity_id_tbl,
527 x_dists.wip_line_code_tbl,
528 x_dists.wip_line_id_tbl,
529 x_dists.bom_resource_code_tbl,
530 x_dists.bom_resource_id_tbl,
531 x_dists.charge_account_tbl,
532 x_dists.charge_account_id_tbl,
533 x_dists.dest_charge_account_id_tbl,
534 x_dists.project_accounting_context_tbl,
535 x_dists.award_num_tbl,
536 x_dists.award_id_tbl,
537 x_dists.project_tbl,
538 x_dists.project_id_tbl,
539 x_dists.task_tbl,
540 x_dists.task_id_tbl,
541 x_dists.expenditure_tbl,
542 x_dists.expenditure_type_tbl,
543 x_dists.expenditure_org_tbl,
544 x_dists.expenditure_org_id_tbl,
545 x_dists.expenditure_item_date_tbl,
546 x_dists.end_item_unit_number_tbl,
547 x_dists.dest_context_tbl,
548 x_dists.gl_encumbered_date_tbl,
549 x_dists.gl_encumbered_period_tbl,
550 x_dists.variance_account_id_tbl,
551 x_dists.accrual_account_id_tbl,
552 x_dists.budget_account_id_tbl,
553 x_dists.dest_variance_account_id_tbl,
554 x_dists.dest_subinventory_tbl,
555 x_dists.amount_ordered_tbl,
556 x_dists.quantity_ordered_tbl,
557 x_dists.wip_rep_schedule_id_tbl,
558 x_dists.wip_operation_seq_num_tbl,
559 x_dists.wip_resource_seq_num_tbl,
560 x_dists.prevent_encumbrance_flag_tbl,
561 x_dists.recovery_rate_tbl,
562 x_dists.tax_recovery_override_flag_tbl,
563 x_dists.account_segment1_tbl,
564 x_dists.account_segment2_tbl,
565 x_dists.account_segment3_tbl,
566 x_dists.account_segment4_tbl,
567 x_dists.account_segment5_tbl,
568 x_dists.account_segment6_tbl,
569 x_dists.account_segment7_tbl,
570 x_dists.account_segment8_tbl,
571 x_dists.account_segment9_tbl,
572 x_dists.account_segment10_tbl,
573 x_dists.account_segment11_tbl,
574 x_dists.account_segment12_tbl,
575 x_dists.account_segment13_tbl,
576 x_dists.account_segment14_tbl,
577 x_dists.account_segment15_tbl,
578 x_dists.account_segment16_tbl,
579 x_dists.account_segment17_tbl,
580 x_dists.account_segment18_tbl,
581 x_dists.account_segment19_tbl,
582 x_dists.account_segment20_tbl,
583 x_dists.account_segment21_tbl,
584 x_dists.account_segment22_tbl,
585 x_dists.account_segment23_tbl,
586 x_dists.account_segment24_tbl,
587 x_dists.account_segment25_tbl,
588 x_dists.account_segment26_tbl,
589 x_dists.account_segment27_tbl,
590 x_dists.account_segment28_tbl,
591 x_dists.account_segment29_tbl,
592 x_dists.account_segment30_tbl,
593 x_dists.dist_attribute1_tbl,
594 x_dists.dist_attribute2_tbl,
595 x_dists.dist_attribute3_tbl,
596 x_dists.dist_attribute4_tbl,
597 x_dists.dist_attribute5_tbl,
598 x_dists.dist_attribute6_tbl,
599 x_dists.dist_attribute7_tbl,
600 x_dists.dist_attribute8_tbl,
601 x_dists.dist_attribute9_tbl,
602 x_dists.dist_attribute10_tbl,
603 x_dists.dist_attribute11_tbl,
604 x_dists.dist_attribute12_tbl,
605 x_dists.dist_attribute13_tbl,
606 x_dists.dist_attribute14_tbl,
607 x_dists.dist_attribute15_tbl,
608
609 -- CLM Partial Funding Changes
610 x_dists.partial_funded_flag_tbl,
611 x_dists.funded_value_tbl,
612
613 -- ACRN proj
614 x_dists.ACRN_tbl,
615
616 -- standard who columns
617 x_dists.last_updated_by_tbl,
618 x_dists.last_update_date_tbl,
619 x_dists.last_update_login_tbl,
620 x_dists.creation_date_tbl,
621 x_dists.created_by_tbl,
622 x_dists.request_id_tbl,
623 x_dists.program_application_id_tbl,
624 x_dists.program_id_tbl,
625 x_dists.program_update_date_tbl,
626
627 -- attributes read from line location record
628 x_dists.loc_ship_to_org_id_tbl,
629 x_dists.loc_line_loc_id_tbl,
630 x_dists.loc_shipment_type_tbl,
631 x_dists.loc_txn_flow_header_id_tbl,
632 x_dists.loc_accrue_on_receipt_flag_tbl,
633 x_dists.loc_need_by_date_tbl,
634 x_dists.loc_promised_date_tbl,
635 x_dists.loc_price_override_tbl,
636 x_dists.loc_outsourced_assembly_tbl,
637 x_dists.loc_attribute1_tbl,
638 x_dists.loc_attribute2_tbl,
639 x_dists.loc_attribute3_tbl,
640 x_dists.loc_attribute4_tbl,
641 x_dists.loc_attribute5_tbl,
642 x_dists.loc_attribute6_tbl,
643 x_dists.loc_attribute7_tbl,
644 x_dists.loc_attribute8_tbl,
645 x_dists.loc_attribute9_tbl,
646 x_dists.loc_attribute10_tbl,
647 x_dists.loc_attribute11_tbl,
648 x_dists.loc_attribute12_tbl,
649 x_dists.loc_attribute13_tbl,
650 x_dists.loc_attribute14_tbl,
651 x_dists.loc_attribute15_tbl,
652
653 -- attributes read from line record
654 x_dists.ln_order_type_lookup_code_tbl,
655 x_dists.ln_purchase_basis_tbl,
656 x_dists.ln_item_id_tbl,
657 x_dists.ln_category_id_tbl,
658 x_dists.ln_line_type_id_tbl,
659 x_dists.ln_po_line_id_tbl,
660 x_dists.ln_attribute1_tbl,
661 x_dists.ln_attribute2_tbl,
662 x_dists.ln_attribute3_tbl,
663 x_dists.ln_attribute4_tbl,
664 x_dists.ln_attribute5_tbl,
665 x_dists.ln_attribute6_tbl,
666 x_dists.ln_attribute7_tbl,
667 x_dists.ln_attribute8_tbl,
668 x_dists.ln_attribute9_tbl,
669 x_dists.ln_attribute10_tbl,
670 x_dists.ln_attribute11_tbl,
671 x_dists.ln_attribute12_tbl,
672 x_dists.ln_attribute13_tbl,
673 x_dists.ln_attribute14_tbl,
674 x_dists.ln_attribute15_tbl,
675
676 -- attributes read from header record
677 x_dists.draft_id_tbl,
678 x_dists.hd_agent_id_tbl,
679 x_dists.hd_po_header_id_tbl,
680 x_dists.hd_rate_date_tbl,
681 x_dists.hd_rate_tbl,
682 x_dists.hd_type_lookup_code_tbl,
683 x_dists.hd_vendor_id_tbl,
684 x_dists.hd_attribute1_tbl,
685 x_dists.hd_attribute2_tbl,
686 x_dists.hd_attribute3_tbl,
687 x_dists.hd_attribute4_tbl,
688 x_dists.hd_attribute5_tbl,
689 x_dists.hd_attribute6_tbl,
690 x_dists.hd_attribute7_tbl,
691 x_dists.hd_attribute8_tbl,
692 x_dists.hd_attribute9_tbl,
693 x_dists.hd_attribute10_tbl,
694 x_dists.hd_attribute11_tbl,
695 x_dists.hd_attribute12_tbl,
696 x_dists.hd_attribute13_tbl,
697 x_dists.hd_attribute14_tbl,
698 x_dists.hd_attribute15_tbl,
699
700 -- CLM Partial Funding Changes
701 x_dists.hd_style_id_tbl,
702
703 -- set initial value for error_flag
704 x_dists.error_flag_tbl,
705
706 x_dists.gms_txn_required_flag_tbl,
707 x_dists.tax_attribute_update_code_tbl,
708 x_dists.ship_to_ou_coa_id_tbl,
709 x_dists.award_set_id_tbl, -- bug5201306
710
711 --clm pdoi integration
712 x_dists.clm_defence_funding_tbl,
713 x_dists.clm_fms_case_number_tbl,
714 x_dists.clm_agency_acct_identifier_tbl,
715 x_dists.group_line_id_tbl,
716 x_dists.quantity_funded_tbl,
717 x_dists.amount_funded_tbl,
718 x_dists.clm_misc_loa_tbl
719
720 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
721
722 IF (PO_LOG.d_proc) THEN
723 PO_LOG.proc_end (d_module);
724 END IF;
725
726 EXCEPTION
727 WHEN OTHERS THEN
728 PO_MESSAGE_S.add_exc_msg
729 (
730 p_pkg_name => d_pkg_name,
731 p_procedure_name => d_api_name || '.' || d_position
732 );
733 RAISE;
734 END fetch_dists;
735
736 -----------------------------------------------------------------------
737 --Start of Comments
738 --Name: derive_dists
739 --Function:
740 -- perform derive logic on distribution records read in one batch;
741 -- derivation errors are handled all together after the
742 -- derivation logic
743 -- The derived attributes include:
744 -- ship_to_ou_id, deliver_to_location_id
745 -- deliver_to_person_id, destination_type_code
746 -- destination_organization_id, wip_entity_id
747 -- wip_line_id, ship_to_ou_coa_id,
748 -- bom_resource_id
749 -- charge_account_id, dest_charge_account_id
750 -- award_id
751 -- project_id, task_id
752 -- expenditure_type_code, expenditure_organziation_id
753 -- expenditure_item_date
754 --Parameters:
755 --IN:
756 --IN OUT:
757 --x_dists
758 -- variable to hold all the distribution attribute values in one batch;
759 -- derivation source and result are both placed inside the variable
760 --OUT:
761 --End of Comments
762 ------------------------------------------------------------------------
763 PROCEDURE derive_dists
764 (
765 x_dists IN OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
766 ) IS
767
768 d_api_name CONSTANT VARCHAR2(30) := 'derive_dists';
769 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
770 d_position NUMBER;
771
772 -- key value used to identify rows in temp table
773 l_key po_session_gt.key%TYPE;
774
775 -- table to hold index
776 l_index_tbl DBMS_SQL.NUMBER_TABLE;
777
778 -- table to mark rows for which derivation will be performed on project fields
779 l_derive_project_info_row_tbl DBMS_SQL.NUMBER_TABLE;
780
781 -- variable to hold results for award id derivation logic API
782 l_msg_count NUMBER;
783 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
784 l_return_status VARCHAR2(1);
785 BEGIN
786 d_position := 0;
787
788 IF (PO_LOG.d_proc) THEN
789 PO_LOG.proc_begin(d_module);
790 END IF;
791
792 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_DIST_DERIVE);
793
794 -- set key value in temp table which is shared by all derivation logic
795 l_key := PO_CORE_S.get_session_gt_nextval;
796
797 -- initialize index table which is used by all derivation logic
798 PO_PDOI_UTL.generate_ordered_num_list
799 (
800 p_size => x_dists.rec_count,
801 x_num_list => l_index_tbl
802 );
803
804 --derive ship_to_ou_id from ship_to_organization_id
805 derive_ship_to_ou_id
806 (
807 p_key => l_key,
808 p_index_tbl => l_index_tbl,
809 p_ship_to_org_id_tbl => x_dists.loc_ship_to_org_id_tbl,
810 x_ship_to_ou_id_tbl => x_dists.ship_to_ou_id_tbl
811 );
812
813 d_position := 10;
814
815 -- derive deliver_to_location_id from deliver_to_location
816 derive_deliver_to_loc_id
817 (
818 p_key => l_key,
819 p_index_tbl => l_index_tbl,
820 p_deliver_to_loc_tbl => x_dists.deliver_to_loc_tbl,
821 x_deliver_to_loc_id_tbl => x_dists.deliver_to_loc_id_tbl
822 );
823
824 d_position := 20;
825
826 -- derive deliver_to_person_id from deliver_to_person_full_name
827 derive_deliver_to_person_id
828 (
829 p_key => l_key,
830 p_index_tbl => l_index_tbl,
831 p_person_name_tbl => x_dists.deliver_to_person_name_tbl,
832 x_person_id_tbl => x_dists.deliver_to_person_id_tbl
833 );
834
835 d_position := 30;
836
837 -- derive destination_type_code from destination_type
838 derive_dest_type_code
839 (
840 p_key => l_key,
841 p_index_tbl => l_index_tbl,
842 p_dest_type_tbl => x_dists.dest_type_tbl,
843 x_dest_type_code_tbl => x_dists.dest_type_code_tbl
844 );
845
846 d_position := 40;
847
848 -- derive destination_organization_id from destination_organization
849 derive_dest_org_id
850 (
851 p_key => l_key,
852 p_index_tbl => l_index_tbl,
853 p_dest_org_tbl => x_dists.dest_org_tbl,
854 p_ship_to_org_id_tbl => x_dists.loc_ship_to_org_id_tbl,
855 x_dest_org_id_tbl => x_dists.dest_org_id_tbl
856 );
857
858 d_position := 50;
859
860 -- derive wip attributes if WIP is installed
861 IF (PO_PDOI_PARAMS.g_product.wip_installed = FND_API.g_TRUE) THEN
862 -- derive wip_entity_id from wip_entity
863 derive_wip_entity_id
864 (
865 p_key => l_key,
866 p_index_tbl => l_index_tbl,
867 p_wip_entity_tbl => x_dists.wip_entity_tbl,
868 p_dest_org_id_tbl => x_dists.dest_org_id_tbl,
869 p_dest_type_code_tbl => x_dists.dest_type_code_tbl,
870 x_wip_entity_id_tbl => x_dists.wip_entity_id_tbl
871 );
872
873 d_position := 60;
874
875 -- derive wip_line_id from wip_line_code
876 derive_wip_line_id
877 (
878 p_key => l_key,
879 p_index_tbl => l_index_tbl,
880 p_wip_line_code_tbl => x_dists.wip_line_code_tbl,
881 p_dest_org_id_tbl => x_dists.dest_org_id_tbl,
882 p_dest_type_code_tbl => x_dists.dest_type_code_tbl,
883 x_wip_line_id_tbl => x_dists.wip_line_id_tbl
884 );
885 END IF;
886
887 d_position := 70;
888
889 -- derive ship_to_ou_coa_id from destination_organization_id
890 -- this value will be used to derive destination charge account id
891 derive_ship_to_ou_coa_id
892 (
893 p_key => l_key,
894 p_index_tbl => l_index_tbl,
895 p_dest_org_id_tbl => x_dists.dest_org_id_tbl,
896 p_txn_flow_header_id_tbl => x_dists.loc_txn_flow_header_id_tbl,
897 p_dest_charge_account_id_tbl => x_dists.dest_charge_account_id_tbl,
898 x_ship_to_ou_coa_id_tbl => x_dists.ship_to_ou_coa_id_tbl
899 );
900
901 d_position := 80;
902
903 -- derive bom_resource_id from bom_resource_code
904 derive_bom_resource_id
905 (
906 p_key => l_key,
907 p_index_tbl => l_index_tbl,
908 p_bom_resource_code_tbl => x_dists.bom_resource_code_tbl,
909 p_dest_org_id_tbl => x_dists.dest_org_id_tbl,
910 x_bom_resource_id_tbl => x_dists.bom_resource_id_tbl
911 );
912
913 d_position := 90;
914
915 -- derive logic for account information
916 -- the logic will be performed on row base
917 FOR i IN 1..x_dists.rec_count
918 LOOP
919 IF (PO_LOG.d_stmt) THEN
920 PO_LOG.stmt(d_module, d_position, 'derive index', i);
921 END IF;
922
923 -- derive charge_account_id
924 IF (x_dists.charge_account_id_tbl(i) IS NULL AND
925 x_dists.loc_txn_flow_header_id_tbl(i) IS NULL) THEN
926
927 derive_account_id
928 (p_account_number => x_dists.charge_account_tbl(i),
929 p_chart_of_accounts_id => PO_PDOI_PARAMS.g_sys.coa_id,
930 p_account_segment1 => x_dists.account_segment1_tbl(i),
931 p_account_segment2 => x_dists.account_segment2_tbl(i),
932 p_account_segment3 => x_dists.account_segment3_tbl(i),
933 p_account_segment4 => x_dists.account_segment4_tbl(i),
934 p_account_segment5 => x_dists.account_segment5_tbl(i),
935 p_account_segment6 => x_dists.account_segment6_tbl(i),
936 p_account_segment7 => x_dists.account_segment7_tbl(i),
937 p_account_segment8 => x_dists.account_segment8_tbl(i),
938 p_account_segment9 => x_dists.account_segment9_tbl(i),
939 p_account_segment10 => x_dists.account_segment10_tbl(i),
940 p_account_segment11 => x_dists.account_segment11_tbl(i),
941 p_account_segment12 => x_dists.account_segment12_tbl(i),
942 p_account_segment13 => x_dists.account_segment13_tbl(i),
943 p_account_segment14 => x_dists.account_segment14_tbl(i),
944 p_account_segment15 => x_dists.account_segment15_tbl(i),
945 p_account_segment16 => x_dists.account_segment16_tbl(i),
946 p_account_segment17 => x_dists.account_segment17_tbl(i),
947 p_account_segment18 => x_dists.account_segment18_tbl(i),
948 p_account_segment19 => x_dists.account_segment19_tbl(i),
949 p_account_segment20 => x_dists.account_segment20_tbl(i),
950 p_account_segment21 => x_dists.account_segment21_tbl(i),
951 p_account_segment22 => x_dists.account_segment22_tbl(i),
952 p_account_segment23 => x_dists.account_segment23_tbl(i),
953 p_account_segment24 => x_dists.account_segment24_tbl(i),
954 p_account_segment25 => x_dists.account_segment25_tbl(i),
955 p_account_segment26 => x_dists.account_segment26_tbl(i),
956 p_account_segment27 => x_dists.account_segment27_tbl(i),
957 p_account_segment28 => x_dists.account_segment28_tbl(i),
958 p_account_segment29 => x_dists.account_segment29_tbl(i),
959 p_account_segment30 => x_dists.account_segment30_tbl(i),
960 x_account_id => x_dists.charge_account_id_tbl(i)
961 );
962
963 IF (PO_LOG.d_stmt) THEN
964 PO_LOG.stmt(d_module, d_position, 'derived charge account id',
965 x_dists.charge_account_id_tbl(i));
966 END IF;
967 END IF;
968
969 d_position := 100;
970
971 -- derive dest_charge_account_id
972 IF (x_dists.dest_charge_account_id_tbl(i) IS NULL AND
973 x_dists.loc_txn_flow_header_id_tbl(i) IS NOT NULL) THEN
974
975 derive_account_id
976 (p_account_number => x_dists.charge_account_tbl(i),
977 p_chart_of_accounts_id => x_dists.ship_to_ou_coa_id_tbl(i),
978 p_account_segment1 => x_dists.account_segment1_tbl(i),
979 p_account_segment2 => x_dists.account_segment2_tbl(i),
980 p_account_segment3 => x_dists.account_segment3_tbl(i),
981 p_account_segment4 => x_dists.account_segment4_tbl(i),
982 p_account_segment5 => x_dists.account_segment5_tbl(i),
983 p_account_segment6 => x_dists.account_segment6_tbl(i),
984 p_account_segment7 => x_dists.account_segment7_tbl(i),
985 p_account_segment8 => x_dists.account_segment8_tbl(i),
986 p_account_segment9 => x_dists.account_segment9_tbl(i),
987 p_account_segment10 => x_dists.account_segment10_tbl(i),
988 p_account_segment11 => x_dists.account_segment11_tbl(i),
989 p_account_segment12 => x_dists.account_segment12_tbl(i),
990 p_account_segment13 => x_dists.account_segment13_tbl(i),
991 p_account_segment14 => x_dists.account_segment14_tbl(i),
992 p_account_segment15 => x_dists.account_segment15_tbl(i),
993 p_account_segment16 => x_dists.account_segment16_tbl(i),
994 p_account_segment17 => x_dists.account_segment17_tbl(i),
995 p_account_segment18 => x_dists.account_segment18_tbl(i),
996 p_account_segment19 => x_dists.account_segment19_tbl(i),
997 p_account_segment20 => x_dists.account_segment20_tbl(i),
998 p_account_segment21 => x_dists.account_segment21_tbl(i),
999 p_account_segment22 => x_dists.account_segment22_tbl(i),
1000 p_account_segment23 => x_dists.account_segment23_tbl(i),
1001 p_account_segment24 => x_dists.account_segment24_tbl(i),
1002 p_account_segment25 => x_dists.account_segment25_tbl(i),
1003 p_account_segment26 => x_dists.account_segment26_tbl(i),
1004 p_account_segment27 => x_dists.account_segment27_tbl(i),
1005 p_account_segment28 => x_dists.account_segment28_tbl(i),
1006 p_account_segment29 => x_dists.account_segment29_tbl(i),
1007 p_account_segment30 => x_dists.account_segment30_tbl(i),
1008 x_account_id => x_dists.dest_charge_account_id_tbl(i)
1009 );
1010
1011 IF (PO_LOG.d_stmt) THEN
1012 PO_LOG.stmt(d_module, d_position, 'derived dest charge account id',
1013 x_dists.dest_charge_account_id_tbl(i));
1014 END IF;
1015 END IF;
1016
1017 d_position := 110;
1018
1019 -- valiadte and derive project related info
1020 IF (x_dists.ln_order_type_lookup_code_tbl(i) = 'FIXED PRICE' AND
1021 x_dists.ln_purchase_basis_tbl(i) = 'SERVICES'AND
1022 PO_PDOI_PARAMS.g_product.project_11510_installed = FND_API.g_FALSE)
1023 OR
1024 (x_dists.ln_order_type_lookup_code_tbl(i) = 'TEMP LABOR' AND
1025 PO_PDOI_PARAMS.g_product.project_cwk_installed = FND_API.g_FALSE) THEN
1026
1027 d_position := 120;
1028
1029 IF (PO_LOG.d_stmt) THEN
1030 PO_LOG.stmt(d_module, d_position, 'all project info need to be empty');
1031 END IF;
1032
1033 -- validate all project fields should be null
1034 validate_null_for_project_info
1035 (
1036 p_index => i,
1037 x_dists => x_dists
1038 );
1039
1040 x_dists.project_accounting_context_tbl(i) := 'N';
1041 x_dists.gms_txn_required_flag_tbl(i) := 'N';
1042 x_dists.award_id_tbl(i) := NULL;
1043 ELSE
1044
1045 d_position := 130;
1046
1047 -- derive project fields if enabled
1048 IF (PO_PDOI_PARAMS.g_product.pa_installed = FND_API.g_TRUE AND
1049 x_dists.project_accounting_context_tbl(i) = 'Y') THEN
1050 IF (PO_LOG.d_stmt) THEN
1051 PO_LOG.stmt(d_module, d_position, 'project info needs to be derived');
1052 END IF;
1053
1054 -- mark the line to do the derivation logic later
1055 l_derive_project_info_row_tbl(i) := i;
1056 END IF;
1057
1058 -- set correct values on gms_txn_required_flag and award_id
1059 -- depending on current context
1060 IF (PO_PDOI_PARAMS.g_product.gms_enabled = FND_API.g_FALSE AND
1061 (x_dists.award_num_tbl(i) IS NOT NULL OR
1062 x_dists.award_id_tbl(i) IS NOT NULL))
1063 OR
1064 (x_dists.project_accounting_context_tbl(i) <> 'Y')
1065 OR
1066 (x_dists.dest_type_code_tbl(i) <> 'EXPENSE') THEN
1067 IF (PO_LOG.d_stmt) THEN
1068 PO_LOG.stmt(d_module, d_position, 'set gms_txn_required_flag to N');
1069 END IF;
1070
1071 x_dists.gms_txn_required_flag_tbl(i) := 'N';
1072 x_dists.award_id_tbl(i) := NULL;
1073 ELSE
1074 IF (PO_LOG.d_stmt) THEN
1075 PO_LOG.stmt(d_module, d_position, 'set gms_txn_required_flag to Y');
1076 END IF;
1077
1078 x_dists.gms_txn_required_flag_tbl(i) := 'Y';
1079 END IF;
1080
1081 -- derive award_id from award_num
1082 IF (PO_PDOI_PARAMS.g_product.gms_enabled = FND_API.g_TRUE AND
1083 x_dists.gms_txn_required_flag_tbl(i) = 'Y' AND
1084 x_dists.award_num_tbl(i) IS NOT NULL AND
1085 x_dists.award_id_tbl(i) IS NULL) THEN
1086
1087 d_position := 140;
1088
1089 -- call GMS API to derive the award_id
1090 x_dists.award_id_tbl(i) :=
1091 GMS_PO_API_GRP.get_award_id
1092 (
1093 p_api_version => 1.0,
1094 p_commit => FND_API.g_FALSE,
1095 p_init_msg_list => FND_API.g_TRUE,
1096 p_validation_level => FND_API.g_VALID_LEVEL_FULL,
1097 x_msg_count => l_msg_count,
1098 x_msg_data => l_msg_data,
1099 x_return_status => l_return_status,
1100 p_award_number => x_dists.award_num_tbl(i)
1101 );
1102
1103 IF (PO_LOG.d_stmt) THEN
1104 PO_LOG.stmt(d_module, d_position, 'l_return_status', l_return_status);
1105 PO_LOG.stmt(d_module, d_position, 'award_id', x_dists.award_id_tbl(i));
1106 END IF;
1107
1108 -- check return status to see whether derivation is successful
1109 IF (l_return_status <> FND_API.g_RET_STS_SUCCESS) THEN
1110 -- insert error
1111 PO_PDOI_ERR_UTL.add_fatal_error
1112 (
1113 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1114 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1115 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1116 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1117 p_error_message_name => 'PO_PDOI_GMS_ERROR',
1118 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1119 p_column_name => 'AWARD_NUMBER',
1120 p_column_value => x_dists.award_num_tbl(i),
1121 p_error_message => l_msg_data
1122 );
1123
1124 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1125 x_dists.gms_txn_required_flag_tbl(i) := 'N';
1126 END IF;
1127 END IF;
1128 END IF; -- IF (x_dists.ln_order_type_lookup_code_tbl(i) = 'FIXED PRICE' AND
1129 END LOOP;
1130
1131 d_position := 150;
1132
1133 -- perform derive logic on project fields in batch mode
1134 -- the logic will be performed only for rows marked in l_derive_project_info_row_tbl
1135 derive_project_info
1136 (
1137 p_key => l_key,
1138 p_index_tbl => l_index_tbl,
1139 p_derive_row_tbl => l_derive_project_info_row_tbl,
1140 x_dists => x_dists
1141 );
1142
1143 d_position := 160;
1144
1145 -- handle all derivation errors
1146 FOR i IN 1..x_dists.rec_count
1147 LOOP
1148 IF (PO_LOG.d_stmt) THEN
1149 PO_LOG.stmt(d_module, d_position, 'index', i);
1150 END IF;
1151
1152 -- derivation error for deliver_to_location_id
1153 IF (x_dists.deliver_to_loc_tbl(i) IS NOT NULL AND
1154 x_dists.deliver_to_loc_id_tbl(i) IS NULL) THEN
1155 IF (PO_LOG.d_stmt) THEN
1156 PO_LOG.stmt(d_module, d_position, 'deliver to loc id derivation failed');
1157 PO_LOG.stmt(d_module, d_position, 'deliver to loc', x_dists.deliver_to_loc_tbl(i));
1158 END IF;
1159
1160 PO_PDOI_ERR_UTL.add_fatal_error
1161 (
1162 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1163 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1164 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1165 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1166 p_error_message_name => 'PO_PDOI_INVALID_DEL_LOCATION',
1167 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1168 p_column_name => 'DELIVER_TO_LOCATION',
1169 p_column_value => x_dists.deliver_to_loc_tbl(i),
1170 p_token1_name => 'DELIVER_TO_LOCATION',
1171 p_token1_value => x_dists.deliver_to_loc_tbl(i)
1172 );
1173
1174 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1175 END IF;
1176
1177 -- derivation error for deliver_to_person_id
1178 IF (x_dists.deliver_to_person_name_tbl(i) IS NOT NULL AND
1179 x_dists.deliver_to_person_id_tbl(i) IS NULL) THEN
1180 IF (PO_LOG.d_stmt) THEN
1181 PO_LOG.stmt(d_module, d_position, 'deliver to person id derivation failed');
1182 PO_LOG.stmt(d_module, d_position, 'deliver to person name',
1183 x_dists.deliver_to_person_name_tbl(i));
1184 END IF;
1185
1186 PO_PDOI_ERR_UTL.add_fatal_error
1187 (
1188 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1189 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1190 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1191 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1192 p_error_message_name => 'PO_PDOI_INVALID_DEL_PERSON',
1193 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1194 p_column_name => 'DELIVER_TO_PERSON',
1195 p_column_value => x_dists.deliver_to_person_name_tbl(i),
1196 p_token1_name => 'DELIVER_TO_PERSON',
1197 p_token1_value => x_dists.deliver_to_person_name_tbl(i)
1198 );
1199
1200 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1201 END IF;
1202
1203 -- derivation error for destination_type_code
1204 IF (x_dists.dest_type_tbl(i) IS NOT NULL AND
1205 x_dists.dest_type_code_tbl(i) IS NULL) THEN
1206 IF (PO_LOG.d_stmt) THEN
1207 PO_LOG.stmt(d_module, d_position, 'dest type code derivation failed');
1208 PO_LOG.stmt(d_module, d_position, 'dest type',
1209 x_dists.dest_type_tbl(i));
1210 END IF;
1211
1212 PO_PDOI_ERR_UTL.add_fatal_error
1213 (
1214 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1215 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1216 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1217 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1218 p_error_message_name => 'PO_PDOI_INVALID_DEST_TYPE',
1219 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1220 p_column_name => 'DESTINATION_TYPE',
1221 p_column_value => x_dists.dest_type_tbl(i),
1222 p_token1_name => 'DESTINATION_TYPE',
1223 p_token1_value => x_dists.dest_type_tbl(i)
1224 );
1225
1226 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1227 END IF;
1228
1229 -- derivation error for destination_organization_id
1230 IF (x_dists.dest_org_tbl(i) IS NOT NULL AND
1231 x_dists.dest_org_id_tbl(i) IS NULL) THEN
1232 IF (PO_LOG.d_stmt) THEN
1233 PO_LOG.stmt(d_module, d_position, 'dest org id derivation failed');
1234 PO_LOG.stmt(d_module, d_position, 'dest org',
1235 x_dists.dest_org_tbl(i));
1236 END IF;
1237
1238 PO_PDOI_ERR_UTL.add_fatal_error
1239 (
1240 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1241 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1242 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1243 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1244 p_error_message_name => 'PO_PDOI_INVALID_DEST_ORG',
1245 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1246 p_column_name => 'DESTINATION_ORGANIZATION',
1247 p_column_value => x_dists.dest_org_tbl(i),
1248 p_token1_name => 'DESTINATION_ORGANIZATION',
1249 p_token1_value => x_dists.dest_org_tbl(i)
1250 );
1251
1252 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1253 END IF;
1254
1255 IF (PO_PDOI_PARAMS.g_product.wip_installed = FND_API.g_TRUE AND
1256 x_dists.dest_type_code_tbl(i) = 'SHOP FLOOR') THEN
1257 -- derivation error for wip_entity_id
1258 IF (x_dists.wip_entity_tbl(i) IS NOT NULL AND
1259 x_dists.wip_entity_id_tbl(i) IS NULL) THEN
1260 IF (PO_LOG.d_stmt) THEN
1261 PO_LOG.stmt(d_module, d_position, 'wip entity id derivation failed');
1262 PO_LOG.stmt(d_module, d_position, 'wip entity',
1263 x_dists.wip_entity_tbl(i));
1264 END IF;
1265
1266 PO_PDOI_ERR_UTL.add_fatal_error
1267 (
1268 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1269 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1270 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1271 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1272 p_error_message_name => 'PO_PDOI_INVALID_WIP_ENTITY',
1273 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1274 p_column_name => 'WIP_ENTITY',
1275 p_column_value => x_dists.wip_entity_tbl(i),
1276 p_token1_name => 'WIP_ENTITY',
1277 p_token1_value => x_dists.wip_entity_tbl(i)
1278 );
1279
1280 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1281 END IF;
1282
1283 -- derivation error for wip_line_id
1284 IF (x_dists.wip_line_code_tbl(i) IS NOT NULL AND
1285 x_dists.wip_line_id_tbl(i) IS NULL) THEN
1286 IF (PO_LOG.d_stmt) THEN
1287 PO_LOG.stmt(d_module, d_position, 'wip line id derivation failed');
1288 PO_LOG.stmt(d_module, d_position, 'wip line code',
1289 x_dists.wip_line_code_tbl(i));
1290 END IF;
1291
1292 PO_PDOI_ERR_UTL.add_fatal_error
1293 (
1294 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1295 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1296 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1297 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1298 p_error_message_name => 'PO_PDOI_INVALID_WIP_LINE',
1299 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1300 p_column_name => 'WIP_LINE_CODE',
1301 p_column_value => x_dists.wip_line_code_tbl(i),
1302 p_token1_name => 'WIP_LINE_CODE',
1303 p_token1_value => x_dists.wip_line_code_tbl(i)
1304 );
1305
1306 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1307 END IF;
1308 END IF;
1309
1310 -- derivation error for bom_resource_id
1311 IF (x_dists.bom_resource_code_tbl(i) IS NOT NULL AND
1312 x_dists.bom_resource_id_tbl(i) IS NULL) THEN
1313 IF (PO_LOG.d_stmt) THEN
1314 PO_LOG.stmt(d_module, d_position, 'bom resource id derivation failed');
1315 PO_LOG.stmt(d_module, d_position, 'bom resource code',
1316 x_dists.bom_resource_code_tbl(i));
1317 END IF;
1318
1319 PO_PDOI_ERR_UTL.add_fatal_error
1320 (
1321 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1322 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1323 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1324 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1325 p_error_message_name => 'PO_PDOI_INVALID_BOM_RESOURCE',
1326 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1327 p_column_name => 'BOM_RESOURCE_CODE',
1328 p_column_value => x_dists.bom_resource_code_tbl(i),
1329 p_token1_name => 'BOM_RESOURCE_CODE',
1330 p_token1_value => x_dists.bom_resource_code_tbl(i)
1331 );
1332
1333 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1334 END IF;
1335
1336 -- derivation error for ship_to_ou_coa_id
1337 IF (x_dists.dest_charge_account_id_tbl(i) IS NULL AND
1338 x_dists.loc_txn_flow_header_id_tbl(i) IS NOT NULL AND
1339 x_dists.ship_to_ou_coa_id_tbl(i) = -1) THEN
1340 IF (PO_LOG.d_stmt) THEN
1341 PO_LOG.stmt(d_module, d_position, 'ship_to ou coa id derivation failed');
1342 END IF;
1343
1344 PO_PDOI_ERR_UTL.add_fatal_error
1345 (
1346 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1347 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1348 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1349 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1350 p_error_message_name => 'PO_PDOI_INVALID_DEST_ORG',
1351 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1352 p_column_name => 'DESTINATION_ORGANIZATION',
1353 p_column_value => x_dists.dest_org_tbl(i),
1354 p_token1_name => 'DESTINATION_ORGANIZATION',
1355 p_token1_value => x_dists.dest_org_tbl(i)
1356 );
1357
1358 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1359 END IF;
1360
1361 -- check derivation error for project fields
1362 IF (l_derive_project_info_row_tbl.EXISTS(i)) THEN
1363 IF (PO_LOG.d_stmt) THEN
1364 PO_LOG.stmt(d_module, d_position, 'l_derive_project_info_row',
1365 l_derive_project_info_row_tbl.EXISTS(i));
1366 END IF;
1367
1368 -- derivation error for project_id
1369 IF (x_dists.project_tbl(i) IS NOT NULL AND
1370 x_dists.project_id_tbl(i) IS NULL) THEN
1371 IF (PO_LOG.d_stmt) THEN
1372 PO_LOG.stmt(d_module, d_position, 'project id derivation failed');
1373 PO_LOG.stmt(d_module, d_position, 'project',
1374 x_dists.project_tbl(i));
1375 END IF;
1376
1377 PO_PDOI_ERR_UTL.add_fatal_error
1378 (
1379 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1380 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1381 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1382 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1383 p_error_message_name => 'PO_PDOI_INVALID_PROJECT',
1384 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1385 p_column_name => 'PROJECT',
1386 p_column_value => x_dists.project_tbl(i),
1387 p_token1_name => 'PROJECT',
1388 p_token1_value => x_dists.project_tbl(i)
1389 );
1390
1391 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1392 END IF;
1393
1394 -- derivation error for task_id
1395 IF (x_dists.project_id_tbl(i) IS NOT NULL AND
1396 x_dists.task_tbl(i) IS NOT NULL AND
1397 x_dists.task_id_tbl(i) IS NULL) THEN
1398 IF (PO_LOG.d_stmt) THEN
1399 PO_LOG.stmt(d_module, d_position, 'task id derivation failed');
1400 PO_LOG.stmt(d_module, d_position, 'task',
1401 x_dists.task_tbl(i));
1402 END IF;
1403
1404 PO_PDOI_ERR_UTL.add_fatal_error
1405 (
1406 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1407 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1408 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1409 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1410 p_error_message_name => 'PO_PDOI_INVALID_TASK',
1411 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1412 p_column_name => 'TASK',
1413 p_column_value => x_dists.task_tbl(i),
1414 p_token1_name => 'TASK',
1415 p_token1_value => x_dists.task_tbl(i)
1416 );
1417
1418 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1419 END IF;
1420
1421 -- derivation error for expenditure_type
1422 IF (x_dists.project_id_tbl(i) IS NOT NULL AND
1423 x_dists.expenditure_tbl(i) IS NOT NULL AND
1424 x_dists.expenditure_type_tbl(i) IS NULL) THEN
1425 IF (PO_LOG.d_stmt) THEN
1426 PO_LOG.stmt(d_module, d_position, 'expenditure type derivation failed');
1427 PO_LOG.stmt(d_module, d_position, 'expenditure',
1428 x_dists.expenditure_tbl(i));
1429 END IF;
1430
1431 PO_PDOI_ERR_UTL.add_fatal_error
1432 (
1433 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1434 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1435 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1436 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1437 p_error_message_name => 'PO_PDOI_INVALID_EXPEND_TYPE',
1438 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1439 p_column_name => 'EXPENDITURE',
1440 p_column_value => x_dists.expenditure_tbl(i),
1441 p_token1_name => 'EXPENDITURE',
1442 p_token1_value => x_dists.expenditure_tbl(i)
1443 );
1444
1445 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1446 END IF;
1447
1448 -- derivation error for expenditure_organization_id
1449 IF (x_dists.project_id_tbl(i) IS NOT NULL AND
1450 x_dists.expenditure_org_tbl(i) IS NOT NULL AND
1451 x_dists.expenditure_org_id_tbl(i) IS NULL) THEN
1452 IF (PO_LOG.d_stmt) THEN
1453 PO_LOG.stmt(d_module, d_position, 'expenditure org id derivation failed');
1454 PO_LOG.stmt(d_module, d_position, 'expenditure org',
1455 x_dists.expenditure_org_tbl(i));
1456 END IF;
1457
1458 PO_PDOI_ERR_UTL.add_fatal_error
1459 (
1460 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1461 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1462 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1463 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1464 p_error_message_name => 'PO_PDOI_INVALID_EXPEND_ORG',
1465 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1466 p_column_name => 'EXPENDITURE_ORGANIZATION',
1467 p_column_value => x_dists.expenditure_org_tbl(i),
1468 p_token1_name => 'EXPENDITURE_ORGANIZATION',
1469 p_token1_value => x_dists.expenditure_org_tbl(i)
1470 );
1471
1472 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1473 END IF;
1474
1475 -- derivation error on expenditure_item_date
1476 IF (x_dists.project_id_tbl(i) IS NOT NULL AND
1477 x_dists.expenditure_org_id_tbl(i) IS NOT NULL AND
1478 x_dists.expenditure_item_date_tbl(i) IS NULL) THEN
1479 IF (PO_LOG.d_stmt) THEN
1480 PO_LOG.stmt(d_module, d_position, 'expenditure item date derivation failed');
1481 END IF;
1482
1483 PO_PDOI_ERR_UTL.add_fatal_error
1484 (
1485 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1486 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1487 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1488 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1489 p_error_message_name => 'PO_PDOI_INVALID_EXPEND_DATE',
1490 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1491 p_column_name => 'EXPENDITURE',
1492 p_column_value => x_dists.expenditure_tbl(i),
1493 p_token1_name => 'EXPENDITURE',
1494 p_token1_value => x_dists.expenditure_tbl(i)
1495 );
1496
1497 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1498 END IF;
1499 END IF;
1500 END LOOP;
1501
1502 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_DIST_DERIVE);
1503
1504 IF (PO_LOG.d_proc) THEN
1505 PO_LOG.proc_end (d_module);
1506 END IF;
1507
1508 EXCEPTION
1509 WHEN OTHERS THEN
1510 PO_MESSAGE_S.add_exc_msg
1511 (
1512 p_pkg_name => d_pkg_name,
1513 p_procedure_name => d_api_name || '.' || d_position
1514 );
1515 RAISE;
1516 END derive_dists;
1517
1518 -----------------------------------------------------------------------
1519 --Start of Comments
1520 --Name: default_dists
1521 --Function:
1522 -- perform default logic on distribution records within one batch;
1523 --Parameters:
1524 --IN:
1525 --IN OUT:
1526 --x_dists
1527 -- variable to hold all the distribution attribute values in one batch;
1528 -- default result are saved inside the variable
1529 --OUT:
1530 --End of Comments
1531 ------------------------------------------------------------------------
1532 PROCEDURE default_dists
1533 (
1534 x_dists IN OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
1535 ) IS
1536
1537 d_api_name CONSTANT VARCHAR2(30) := 'default_dists';
1538 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1539 d_position NUMBER;
1540
1541 -- key value used to identify row in temp table
1542 l_key po_session_gt.key%TYPE;
1543
1544 -- table to hold index
1545 l_index_tbl DBMS_SQL.NUMBER_TABLE;
1546
1547 -- flag to indicate whether po encumbrance is enabled
1548 l_po_encumbrance_flag VARCHAR2(1);
1549
1550 -- variable to hold results for GMS API call
1551 l_msg_count NUMBER;
1552 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1553 l_return_status VARCHAR2(1);
1554
1555 -- variables used to call API to get gl_encumbered_period_name
1556 l_period_year_tbl PO_TBL_NUMBER;
1557 l_period_num_tbl PO_TBL_NUMBER;
1558 l_quarter_num_tbl PO_TBL_NUMBER;
1559 l_invalid_period_flag VARCHAR2(1);
1560
1561 -- variable used in workflow API call
1562 l_charge_success BOOLEAN := TRUE;
1563 l_budget_success BOOLEAN := TRUE;
1564 l_accrual_success BOOLEAN := TRUE;
1565 l_variance_success BOOLEAN := TRUE;
1566 l_charge_account_flex VARCHAR2(2000);
1567 l_budget_account_flex VARCHAR2(2000);
1568 l_accrual_account_flex VARCHAR2(2000);
1569 l_variance_account_flex VARCHAR2(2000);
1570 l_charge_account_desc VARCHAR2(2000);
1571 l_budget_account_desc VARCHAR2(2000);
1572 l_accrual_account_desc VARCHAR2(2000);
1573 l_variance_account_desc VARCHAR2(2000);
1574 l_dest_charge_success BOOLEAN := TRUE;
1575 l_dest_variance_success BOOLEAN := TRUE;
1576 l_dest_charge_account_desc VARCHAR2(2000);
1577 l_dest_variance_account_desc VARCHAR2(2000);
1578 l_dest_charge_account_flex VARCHAR2(2000);
1579 l_dest_variance_account_flex VARCHAR2(2000);
1580 l_wf_itemkey VARCHAR2(80) := NULL;
1581 l_new_ccid_generated BOOLEAN := FALSE;
1582 l_fb_error_msg VARCHAR2(2000);
1583 l_bom_cost_element_id NUMBER := NULL;
1584 l_result_billable_flag VARCHAR2(5) := NULL;
1585 l_from_type_lookup_code VARCHAR2(5) := NULL;
1586 l_from_header_id NUMBER := NULL;
1587 l_from_line_id NUMBER := NULL;
1588 l_wip_entity_type VARCHAR2(25) := NULL;
1589
1590 l_success BOOLEAN;
1591 l_entity_type NUMBER;
1592 --Bug 12855747
1593 l_gl_enc_period_name VARCHAR2(30);
1594 BEGIN
1595 d_position := 0;
1596
1597 IF (PO_LOG.d_proc) THEN
1598 PO_LOG.proc_begin(d_module);
1599 END IF;
1600
1601 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_DIST_DEFAULT);
1602
1603 -- pick a new key from temp table which will be used in all derive logic
1604 l_key := PO_CORE_S.get_session_gt_nextval;
1605
1606 -- initialize index table which is used by all derivation logic
1607 PO_PDOI_UTL.generate_ordered_num_list
1608 (
1609 p_size => x_dists.rec_count,
1610 x_num_list => l_index_tbl
1611 );
1612
1613 -- read value from system parameter
1614 l_po_encumbrance_flag := PO_PDOI_PARAMS.g_sys.po_encumbrance_flag;
1615
1616 -- get item_status for each distribution
1617 get_item_status
1618 (
1619 p_key => l_key,
1620 p_index_tbl => l_index_tbl,
1621 p_item_id_tbl => x_dists.ln_item_id_tbl,
1622 p_ship_to_org_id_tbl => x_dists.loc_ship_to_org_id_tbl,
1623 x_item_status_tbl => x_dists.item_status_tbl
1624 );
1625
1626 d_position := 10;
1627
1628 -- default values for each distribution record
1629 x_dists.prevent_encumbrance_flag_tbl.EXTEND(x_dists.rec_count);
1630 FOR i IN 1..x_dists.rec_count
1631 LOOP
1632 IF (PO_LOG.d_stmt) THEN
1633 PO_LOG.stmt(d_module, d_position, 'index', i);
1634 END IF;
1635
1636 -- default distribution_num
1637 IF (x_dists.dist_num_tbl(i) IS NULL OR
1638 x_dists.dist_num_unique_tbl(i) = FND_API.g_FALSE) THEN
1639 x_dists.dist_num_tbl(i) :=
1640 PO_PDOI_MAINPROC_UTL_PVT.get_next_dist_num(x_dists.loc_line_loc_id_tbl(i));
1641 END IF;
1642
1643 -- set distribution_id
1644 x_dists.po_dist_id_tbl(i) :=
1645 PO_PDOI_MAINPROC_UTL_PVT.get_next_dist_id;
1646
1647 /* bug <8565566> Need to re-initialize the following boolean values for each distributions record comes for processing
1648 <start> */
1649 l_charge_success := TRUE;
1650 l_budget_success := TRUE;
1651 l_accrual_success := TRUE;
1652 l_variance_success := TRUE;
1653 l_dest_charge_success := TRUE;
1654 l_dest_variance_success := TRUE;
1655 l_new_ccid_generated := FALSE;
1656 /*<end> bug 8565566 */
1657
1658 -- default destination_type_code and destination_context from item status
1659 IF (x_dists.dest_type_code_tbl(i) IS NULL) THEN
1660 IF (x_dists.item_status_tbl(i) = 'O') THEN
1661 x_dists.dest_type_code_tbl(i) := 'SHOP FLOOR';
1662 x_dists.dest_context_tbl(i) := 'SHOP FLOOR';
1663 ELSIF (x_dists.item_status_tbl(i) = 'E') THEN
1664 x_dists.dest_type_code_tbl(i) := 'INVENTORY';
1665 x_dists.dest_context_tbl(i) := 'INVENTORY';
1666 ELSE
1667 x_dists.dest_type_code_tbl(i) := 'EXPENSE';
1668 x_dists.dest_context_tbl(i) := 'EXPENSE';
1669 END IF;
1670 END IF;
1671
1672 IF (PO_LOG.d_stmt) THEN
1673 PO_LOG.stmt(d_module, d_position, 'dist num', x_dists.dist_num_tbl(i));
1674 PO_LOG.stmt(d_module, d_position, 'dist id', x_dists.po_dist_id_tbl(i));
1675 PO_LOG.stmt(d_module, d_position, 'item status', x_dists.item_status_tbl(i));
1676 PO_LOG.stmt(d_module, d_position, 'dest type code', x_dists.dest_type_code_tbl(i));
1677 END IF;
1678
1679 d_position := 20;
1680
1681 -- set value for prevent_encumbrance_flag based on destination_type_code
1682 /* For Encumbrance Project - To enable Encumbrance for Destination type - Shop Floor and WIP entity type - EAM
1683 Retriving entity_type and Setting the prevent_encumbrance_flag to 'Y' if destination type = shop floor
1684 and wip_entity_type != 6 ( '6' is for EAM jobs) */
1685
1686 IF (x_dists.dest_type_code_tbl(i) = 'SHOP FLOOR') THEN
1687
1688 BEGIN /* added for Encumbrance Project */
1689 SELECT entity_type
1690 INTO l_entity_type
1691 FROM wip_entities
1692 WHERE wip_entity_id = x_dists.wip_entity_id_tbl(i) ;
1693 EXCEPTION
1694 WHEN OTHERS THEN
1695 l_entity_type := 1; /*if exception, setting entity type to a value <> 6*/
1696 END;
1697
1698 /* Condition added for Encumbrance Project */
1699 IF (l_entity_type <> 6 OR x_dists.loc_shipment_type_tbl(i) = 'PREPAYMENT') THEN
1700 -- PDOI for Complex PO Project
1701 x_dists.prevent_encumbrance_flag_tbl(i) := 'Y';
1702 ELSE
1703 x_dists.prevent_encumbrance_flag_tbl(i) := 'N';
1704 END IF;
1705 END IF;
1706
1707 -- set gl_encumbered_date and gl_encumbered_period_name based on
1708 -- po_encumbrance_flag
1709 -- If the flag is g_TRUE, default gl_encumbered_period
1710 -- later in bulk mode
1711
1712 -- bug4907624
1713 -- Compare l_po_encumbrance_flag to 'Y' rather than FND_API.G_TRUE
1714
1715 IF (l_po_encumbrance_flag = 'Y') THEN
1716 x_dists.gl_encumbered_date_tbl(i) :=
1717 NVL(x_dists.gl_encumbered_date_tbl(i), sysdate);
1718 ELSE
1719 x_dists.gl_encumbered_date_tbl(i) := NULL;
1720 x_dists.gl_encumbered_period_tbl(i) := NULL;
1721 END IF;
1722
1723 -- set project fields to null if not applicable
1724 IF (x_dists.project_accounting_context_tbl(i) <> 'Y') THEN
1725 x_dists.project_id_tbl(i) := NULL;
1726 x_dists.task_id_tbl(i) := NULL;
1727 x_dists.expenditure_type_tbl(i) := NULL;
1728 x_dists.expenditure_org_id_tbl(i) := NULL;
1729 x_dists.expenditure_item_date_tbl(i) := NULL;
1730 x_dists.award_id_tbl(i) := NULL;
1731 END IF;
1732
1733 -- clear expenditure related fields for 'INVENTORY' type
1734 IF (x_dists.dest_type_code_tbl(i) = 'INVENTORY') THEN
1735 x_dists.expenditure_type_tbl(i):= NULL;
1736 x_dists.expenditure_org_id_tbl(i) := NULL;
1737 x_dists.expenditure_item_date_tbl(i) := NULL;
1738 x_dists.award_id_tbl(i) := NULL;
1739 END IF;
1740
1741 -- If all of the PATEO fields are NULL's then there is no need to call the
1742 -- GMS validation API.
1743 IF (x_dists.project_id_tbl(i) IS NULL AND
1744 x_dists.task_id_tbl(i) IS NULL AND
1745 x_dists.expenditure_type_tbl(i) IS NULL AND
1746 x_dists.expenditure_org_id_tbl(i) IS NULL AND
1747 x_dists.expenditure_item_date_tbl(i) IS NULL AND
1748 x_dists.award_id_tbl(i) IS NULL) THEN
1749 IF (PO_LOG.d_stmt) THEN
1750 PO_LOG.stmt(d_module, d_position, 'set gms_txn_required_flag to N');
1751 END IF;
1752
1753 x_dists.gms_txn_required_flag_tbl(i) := 'N';
1754 END IF;
1755
1756 -- validate the Award transaction and then create the ADL
1757 IF (PO_PDOI_PARAMS.g_product.gms_enabled = FND_API.g_TRUE AND
1758 x_dists.gms_txn_required_flag_tbl(i) = 'Y') THEN
1759
1760 d_position := 30;
1761
1762 GMS_PO_API_GRP.validate_transaction
1763 (
1764 p_api_version => 1.0,
1765 p_commit => FND_API.g_FALSE,
1766 p_init_msg_list => FND_API.g_TRUE,
1767 p_validation_level => FND_API.g_VALID_LEVEL_FULL,
1768 x_msg_count => l_msg_count,
1769 x_msg_data => l_msg_data,
1770 x_return_status => l_return_status,
1771 p_project_id => x_dists.project_id_tbl(i),
1772 p_task_id => x_dists.task_id_tbl(i),
1773 p_award_id => x_dists.award_id_tbl(i),
1774 p_expenditure_type => x_dists.expenditure_type_tbl(i),
1775 p_expenditure_item_date => x_dists.expenditure_item_date_tbl(i),
1776 p_calling_module => 'PO_PDOI_DIST_PROCESS_PVT.default_dists'
1777 );
1778
1779 IF (PO_LOG.d_stmt) THEN
1780 PO_LOG.stmt(d_module, d_position, 'return status', l_return_status);
1781 END IF;
1782
1783 IF (l_return_status <> FND_API.g_RET_STS_SUCCESS) THEN
1784 PO_PDOI_ERR_UTL.add_fatal_error
1785 (
1786 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1787 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1788 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1789 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1790 p_error_message_name => 'PO_PDOI_GMS_ERROR',
1791 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1792 p_column_name => 'AWARD_NUMBER',
1793 p_column_value => x_dists.award_num_tbl(i),
1794 p_error_message => l_msg_data
1795 );
1796
1797 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1798 ELSE
1799
1800 d_position := 40;
1801
1802 IF (PO_LOG.d_stmt) THEN
1803 PO_LOG.stmt(d_module, d_position, 'award id', x_dists.award_id_tbl(i));
1804 END IF;
1805
1806 IF (x_dists.award_id_tbl(i) IS NOT NULL) THEN
1807 x_dists.award_set_id_tbl(i) := GMS_PO_API_GRP.get_new_award_set_id();
1808
1809 GMS_PO_API_GRP.create_pdoi_adls
1810 (
1811 p_api_version => 1.0,
1812 p_commit => FND_API.g_FALSE,
1813 p_init_msg_list => FND_API.g_TRUE,
1814 p_validation_level => FND_API.g_VALID_LEVEL_FULL,
1815 x_msg_count => l_msg_count,
1816 x_msg_data => l_msg_data,
1817 x_return_status => l_return_status,
1818 p_distribution_id => x_dists.po_dist_id_tbl(i),
1819 p_distribution_num => x_dists.dist_num_tbl(i),
1820 p_project_id => x_dists.project_id_tbl(i),
1821 p_task_id => x_dists.task_id_tbl(i),
1822 p_award_id => x_dists.award_id_tbl(i),
1823 p_award_set_id => x_dists.award_set_id_tbl(i) -- bug5201306
1824 );
1825
1826 IF (PO_LOG.d_stmt) THEN
1827 PO_LOG.stmt(d_module, d_position, 'return status', l_return_status);
1828 END IF;
1829
1830 -- insert error if failed
1831 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1832 PO_PDOI_ERR_UTL.add_fatal_error
1833 (
1834 p_interface_header_id => x_dists.intf_header_id_tbl(i),
1835 p_interface_line_id => x_dists.intf_line_id_tbl(i),
1836 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
1837 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
1838 p_error_message_name => 'PO_PDOI_GMS_ERROR',
1839 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
1840 p_column_name => 'AWARD_NUMBER',
1841 p_column_value => x_dists.award_num_tbl(i),
1842 p_error_message => l_msg_data
1843 );
1844
1845 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
1846 END IF;
1847 END IF; -- IF (x_dists.award_id_tbl(i) IS NOT NULL)
1848 END IF; -- IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1849 END IF; -- IF (PO_PDOI_PARAMS.g_product.gms_enabled = FND_API.g_TRUE AND ..
1850
1851 d_position := 50;
1852
1853 IF (PO_LOG.d_stmt) THEN
1854 PO_LOG.stmt(d_module, d_position, 'dest type code',
1855 x_dists.dest_type_code_tbl(i));
1856 END IF;
1857
1858 -- bug 4284077:
1859 -- if the account_ids have been provided in the interface table,
1860 -- we will use them
1861
1862 /*
1863 -- bug 4284077: comment out the code
1864
1865 -- set to NULL before workflow since values of these attributes
1866 -- in the interface table are ignored
1867 x_dists.variance_account_id_tbl(i) := NULL;
1868 x_dists.accrual_account_id_tbl(i) := NULL;
1869 x_dists.budget_account_id_tbl(i) := NULL;
1870 x_dists.dest_variance_account_id_tbl(i) := NULL;
1871 IF (x_dists.dest_type_code_tbl(i) <> 'EXPENSE') THEN
1872 x_dists.charge_account_id_tbl(i) := NULL;
1873 x_dists.dest_charge_account_id_tbl(i) := NULL;
1874 END IF;
1875 */
1876
1877 /* Bug 10421902 This is to fetch the billable flag from pa_tasks.
1878 This flag would be later used in the account generator workflow */
1879 BEGIN
1880 IF x_dists.project_id_tbl(i) IS NOT NULL
1881 AND x_dists.task_id_tbl(i) IS NOT NULL THEN
1882 SELECT billable_flag
1883 INTO l_result_billable_flag
1884 -- FROM pa_tasks Bug 16863607
1885 FROM pa_tasks_expend_v
1886 WHERE task_id = x_dists.task_id_tbl(i)
1887 AND project_id = x_dists.project_id_tbl(i);
1888 END IF;
1889 EXCEPTION
1890 WHEN OTHERS THEN
1891 l_result_billable_flag := NULL;
1892 END;
1893
1894 --Start of code changes for the bug 12933412
1895 /*
1896 IF (x_dists.charge_account_id_tbl(i) IS NOT NULL) THEN
1897 default_account_ids
1898 (
1899 p_dest_type_code => x_dists.dest_type_code_tbl(i),
1900 p_dest_org_id => x_dists.dest_org_id_tbl(i),
1901 p_dest_subinventory => x_dists.dest_subinventory_tbl(i),
1902 p_item_id => x_dists.ln_item_id_tbl(i),
1903 p_po_encumbrance_flag => l_po_encumbrance_flag,
1904 p_charge_account_id => x_dists.charge_account_id_tbl(i),
1905 x_accrual_account_id => x_dists.accrual_account_id_tbl(i),
1906 x_budget_account_id => x_dists.budget_account_id_tbl(i),
1907 x_variance_account_id => x_dists.variance_account_id_tbl(i),
1908 x_entity_type => l_entity_type,
1909 x_wip_entity_id => x_dists.wip_entity_id_tbl(i)
1910 );
1911 ELSE
1912 */
1913 d_position := 60;
1914
1915 -- generate account
1916 l_success := PO_WF_BUILD_ACCOUNT_INIT.Start_Workflow
1917 (
1918 x_purchasing_ou_id => PO_PDOI_PARAMS.g_request.org_id,
1919 x_transaction_flow_header_id => x_dists.loc_txn_flow_header_id_tbl(i),
1920 x_dest_charge_success => l_dest_charge_success,
1921 x_dest_variance_success => l_dest_variance_success,
1922 x_dest_charge_account_id => x_dists.dest_charge_account_id_tbl(i),
1923 x_dest_variance_account_id => x_dists.dest_variance_account_id_tbl(i),
1924 x_dest_charge_account_desc => l_dest_charge_account_desc,
1925 x_dest_variance_account_desc => l_dest_variance_account_desc,
1926 x_dest_charge_account_flex => l_dest_charge_account_flex,
1927 x_dest_variance_account_flex => l_dest_variance_account_flex,
1928 x_charge_success => l_charge_success,
1929 x_budget_success => l_budget_success,
1930 x_accrual_success => l_accrual_success,
1931 x_variance_success => l_variance_success,
1932 x_code_combination_id => x_dists.charge_account_id_tbl(i),
1933 x_budget_account_id => x_dists.budget_account_id_tbl(i),
1934 x_accrual_account_id => x_dists.accrual_account_id_tbl(i),
1935 x_variance_account_id => x_dists.variance_account_id_tbl(i),
1936 x_charge_account_flex => l_charge_account_flex,
1937 x_budget_account_flex => l_budget_account_flex,
1938 x_accrual_account_flex => l_accrual_account_flex,
1939 x_variance_account_flex => l_variance_account_flex,
1940 x_charge_account_desc => l_charge_account_desc,
1941 x_budget_account_desc => l_budget_account_desc,
1942 x_accrual_account_desc => l_accrual_account_desc,
1943 x_variance_account_desc => l_variance_account_desc,
1944 x_coa_id => PO_PDOI_PARAMS.g_sys.coa_id,
1945 x_bom_resource_id => x_dists.bom_resource_id_tbl(i),
1946 x_bom_cost_element_id => l_bom_cost_element_id,
1947 x_category_id => x_dists.ln_category_id_tbl(i),
1948 x_destination_type_code => x_dists.dest_type_code_tbl(i),
1949 x_deliver_to_location_id => x_dists.deliver_to_loc_id_tbl(i),
1950 x_destination_organization_id => x_dists.dest_org_id_tbl(i),
1951 x_destination_subinventory => x_dists.dest_subinventory_tbl(i),
1952 x_expenditure_type => x_dists.expenditure_type_tbl(i),
1953 x_expenditure_organization_id => x_dists.expenditure_org_id_tbl(i),
1954 x_expenditure_item_date => x_dists.expenditure_item_date_tbl(i),
1955 x_item_id => x_dists.ln_item_id_tbl(i),
1956 x_line_type_id => x_dists.ln_line_type_id_tbl(i),
1957 x_result_billable_flag => l_result_billable_flag,
1958 x_agent_id => x_dists.hd_agent_id_tbl(i),
1959 x_project_id => x_dists.project_id_tbl(i),
1960 x_from_type_lookup_code => l_from_type_lookup_code,
1961 x_from_header_id => l_from_header_id,
1962 x_from_line_id => l_from_line_id,
1963 x_task_id => x_dists.task_id_tbl(i),
1964 x_deliver_to_person_id => x_dists.deliver_to_person_id_tbl(i),
1965 x_type_lookup_code => x_dists.hd_type_lookup_code_tbl(i),
1966 x_vendor_id => x_dists.hd_vendor_id_tbl(i),
1967 x_wip_entity_id => x_dists.wip_entity_id_tbl(i),
1968 x_wip_entity_type => l_wip_entity_type,
1969 x_wip_line_id => x_dists.wip_line_id_tbl(i),
1970 x_wip_repetitive_schedule_id => x_dists.wip_rep_schedule_id_tbl(i),
1971 x_wip_operation_seq_num => x_dists.wip_operation_seq_num_tbl(i),
1972 x_wip_resource_seq_num => x_dists.wip_resource_seq_num_tbl(i),
1973 x_po_encumberance_flag => PO_PDOI_PARAMS.g_sys.po_encumbrance_flag,
1974 x_gl_encumbered_date => x_dists.gl_encumbered_date_tbl(i),
1975 wf_itemkey => l_wf_itemkey,
1976 x_new_combination => l_new_ccid_generated,
1977 header_att1 => x_dists.hd_attribute1_tbl(i),
1978 header_att2 => x_dists.hd_attribute2_tbl(i),
1979 header_att3 => x_dists.hd_attribute3_tbl(i),
1980 header_att4 => x_dists.hd_attribute4_tbl(i),
1981 header_att5 => x_dists.hd_attribute5_tbl(i),
1982 header_att6 => x_dists.hd_attribute6_tbl(i),
1983 header_att7 => x_dists.hd_attribute7_tbl(i),
1984 header_att8 => x_dists.hd_attribute8_tbl(i),
1985 header_att9 => x_dists.hd_attribute9_tbl(i),
1986 header_att10 => x_dists.hd_attribute10_tbl(i),
1987 header_att11 => x_dists.hd_attribute11_tbl(i),
1988 header_att12 => x_dists.hd_attribute12_tbl(i),
1989 header_att13 => x_dists.hd_attribute13_tbl(i),
1990 header_att14 => x_dists.hd_attribute14_tbl(i),
1991 header_att15 => x_dists.hd_attribute15_tbl(i),
1992 line_att1 => x_dists.ln_attribute1_tbl(i),
1993 line_att2 => x_dists.ln_attribute1_tbl(i),
1994 line_att3 => x_dists.ln_attribute1_tbl(i),
1995 line_att4 => x_dists.ln_attribute1_tbl(i),
1996 line_att5 => x_dists.ln_attribute1_tbl(i),
1997 line_att6 => x_dists.ln_attribute1_tbl(i),
1998 line_att7 => x_dists.ln_attribute1_tbl(i),
1999 line_att8 => x_dists.ln_attribute1_tbl(i),
2000 line_att9 => x_dists.ln_attribute1_tbl(i),
2001 line_att10 => x_dists.ln_attribute1_tbl(i),
2002 line_att11 => x_dists.ln_attribute1_tbl(i),
2003 line_att12 => x_dists.ln_attribute1_tbl(i),
2004 line_att13 => x_dists.ln_attribute1_tbl(i),
2005 line_att14 => x_dists.ln_attribute1_tbl(i),
2006 line_att15 => x_dists.ln_attribute15_tbl(i),
2007 shipment_att1 => x_dists.loc_attribute1_tbl(i),
2008 shipment_att2 => x_dists.loc_attribute2_tbl(i),
2009 shipment_att3 => x_dists.loc_attribute3_tbl(i),
2010 shipment_att4 => x_dists.loc_attribute4_tbl(i),
2011 shipment_att5 => x_dists.loc_attribute5_tbl(i),
2012 shipment_att6 => x_dists.loc_attribute6_tbl(i),
2013 shipment_att7 => x_dists.loc_attribute7_tbl(i),
2014 shipment_att8 => x_dists.loc_attribute8_tbl(i),
2015 shipment_att9 => x_dists.loc_attribute9_tbl(i),
2016 shipment_att10 => x_dists.loc_attribute10_tbl(i),
2017 shipment_att11 => x_dists.loc_attribute11_tbl(i),
2018 shipment_att12 => x_dists.loc_attribute12_tbl(i),
2019 shipment_att13 => x_dists.loc_attribute13_tbl(i),
2020 shipment_att14 => x_dists.loc_attribute14_tbl(i),
2021 shipment_att15 => x_dists.loc_attribute15_tbl(i),
2022 distribution_att1 => x_dists.dist_attribute1_tbl(i),
2023 distribution_att2 => x_dists.dist_attribute2_tbl(i),
2024 distribution_att3 => x_dists.dist_attribute3_tbl(i),
2025 distribution_att4 => x_dists.dist_attribute4_tbl(i),
2026 distribution_att5 => x_dists.dist_attribute5_tbl(i),
2027 distribution_att6 => x_dists.dist_attribute6_tbl(i),
2028 distribution_att7 => x_dists.dist_attribute7_tbl(i),
2029 distribution_att8 => x_dists.dist_attribute8_tbl(i),
2030 distribution_att9 => x_dists.dist_attribute9_tbl(i),
2031 distribution_att10 => x_dists.dist_attribute10_tbl(i),
2032 distribution_att11 => x_dists.dist_attribute11_tbl(i),
2033 distribution_att12 => x_dists.dist_attribute12_tbl(i),
2034 distribution_att13 => x_dists.dist_attribute13_tbl(i),
2035 distribution_att14 => x_dists.dist_attribute14_tbl(i),
2036 distribution_att15 => x_dists.dist_attribute15_tbl(i),
2037 fb_error_msg => l_fb_error_msg,
2038 p_distribution_type => NULL,
2039 p_payment_type => NULL,
2040 x_award_id => x_dists.award_id_tbl(i), /* 10089606 bug NULL */
2041 x_vendor_site_id => NULL,
2042 p_func_unit_price => x_dists.loc_price_override_tbl(i) * NVL(x_dists.hd_rate_tbl(i), 1)
2043 );
2044
2045 --END IF;
2046 --End of code changes for the bug 12933412
2047
2048 IF (PO_LOG.d_stmt) THEN
2049 PO_LOG.stmt(d_module, d_position, 'l_success', l_success);
2050 PO_LOG.stmt(d_module, d_position, 'l_po_encumbrance_flag',
2051 l_po_encumbrance_flag);
2052 PO_LOG.stmt(d_module, d_position, 'l_charge_success', l_charge_success);
2053 PO_LOG.stmt(d_module, d_position, 'charge_account_id',
2054 x_dists.charge_account_id_tbl(i));
2055 PO_LOG.stmt(d_module, d_position, 'l_budget_success', l_budget_success);
2056 PO_LOG.stmt(d_module, d_position, 'l_accrual_success', l_accrual_success);
2057 PO_LOG.stmt(d_module, d_position, 'l_variance_success', l_variance_success);
2058 END IF;
2059
2060 d_position := 70;
2061
2062 -- after workflow, check result
2063 IF (l_po_encumbrance_flag = 'N' OR
2064 x_dists.dest_type_code_tbl(i) = 'SHOP FLOOR') then
2065 l_budget_success := TRUE;
2066 END IF;
2067
2068 IF (l_charge_success <> TRUE OR
2069 NVL(x_dists.charge_account_id_tbl(i), 0) = 0) THEN
2070 IF (x_dists.dest_type_code_tbl(i) = 'EXPENSE' AND
2071 l_charge_success = FALSE) THEN
2072 PO_PDOI_ERR_UTL.add_fatal_error
2073 (
2074 p_interface_header_id => x_dists.intf_header_id_tbl(i),
2075 p_interface_line_id => x_dists.intf_line_id_tbl(i),
2076 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
2077 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
2078 p_error_message_name => 'PO_PDOI_CHARGE_FAILED',
2079 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
2080 p_column_name => 'CHARGE_ACCOUNT_ID',
2081 p_column_value => x_dists.charge_account_id_tbl(i)
2082 );
2083
2084 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
2085 END IF;
2086 ELSIF (l_budget_success <> TRUE) THEN
2087 PO_PDOI_ERR_UTL.add_fatal_error
2088 (
2089 p_interface_header_id => x_dists.intf_header_id_tbl(i),
2090 p_interface_line_id => x_dists.intf_line_id_tbl(i),
2091 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
2092 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
2093 p_error_message_name => 'PO_PDOI_BUDGET_FAILED',
2094 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
2095 p_column_name => 'BUDGET_ACCOUNT_ID',
2096 p_column_value => x_dists.budget_account_id_tbl(i)
2097 );
2098
2099 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
2100 ELSIF (l_accrual_success <> TRUE) THEN
2101 PO_PDOI_ERR_UTL.add_fatal_error
2102 (
2103 p_interface_header_id => x_dists.intf_header_id_tbl(i),
2104 p_interface_line_id => x_dists.intf_line_id_tbl(i),
2105 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
2106 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
2107 p_error_message_name => 'PO_PDOI_ACCRUAL_FAILED',
2108 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
2109 p_column_name => 'ACCRUAL_ACCOUNT_ID',
2110 p_column_value => x_dists.accrual_account_id_tbl(i)
2111 );
2112
2113 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
2114 ELSIF (l_variance_success <> TRUE) THEN
2115 PO_PDOI_ERR_UTL.add_fatal_error
2116 (
2117 p_interface_header_id => x_dists.intf_header_id_tbl(i),
2118 p_interface_line_id => x_dists.intf_line_id_tbl(i),
2119 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
2120 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
2121 p_error_message_name => 'PO_PDOI_VARIANCE_FAILED',
2122 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
2123 p_column_name => 'VARIANCE_ACCOUNT_ID',
2124 p_column_value => x_dists.variance_account_id_tbl(i)
2125 );
2126
2127 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
2128 ELSE
2129 NULL;
2130 END IF;
2131
2132 d_position := 80;
2133
2134 IF (l_po_encumbrance_flag = 'N' OR
2135 Nvl(x_dists.prevent_encumbrance_flag_tbl(i),'N') = 'Y') THEN -- PDOI for Complex PO Project
2136 x_dists.budget_account_id_tbl(i) := NULL;
2137 END IF;
2138
2139 -- default tax related columns
2140 x_dists.tax_attribute_update_code_tbl(i) := 'CREATE';
2141
2142 IF (x_dists.recovery_rate_tbl(i) IS NOT NULL) THEN
2143 x_dists.tax_recovery_override_flag_tbl(i) := 'Y';
2144 ELSE
2145 x_dists.tax_recovery_override_flag_tbl(i) := NULL;
2146 END IF;
2147
2148 --Bug 12855747
2149 --Tracking GL Encumbered Date not in Open Accounting Period
2150 -- Budgetary Action in an encumbrance enabled environment.
2151
2152 IF (l_po_encumbrance_flag = 'Y') THEN
2153 po_periods_sv.get_period_name(PO_PDOI_PARAMS.g_sys.sob_id,
2154 nvl( x_dists.gl_encumbered_date_tbl(i),sysdate),
2155 l_gl_enc_period_name);
2156
2157 IF (l_gl_enc_period_name IS NULL) THEN
2158 PO_PDOI_ERR_UTL.add_fatal_error
2159 (
2160 p_interface_header_id => x_dists.intf_header_id_tbl(i),
2161 p_interface_line_id => x_dists.intf_line_id_tbl(i),
2162 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(i),
2163 p_interface_distribution_id => x_dists.intf_dist_id_tbl(i),
2164 p_error_message_name => 'PO_PO_ENTER_OPEN_GL_DATE',
2165 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
2166 p_column_name => 'GL_ENCUMBERED_DATE',
2167 p_column_value => x_dists.gl_encumbered_date_tbl(i)
2168 );
2169
2170 x_dists.error_flag_tbl(i) := FND_API.g_TRUE;
2171 END IF;
2172 END IF;
2173
2174 END LOOP;
2175
2176 d_position := 90;
2177
2178 -- call bulk API to default gl_encumbered_period_name
2179 IF (l_po_encumbrance_flag = 'Y') THEN
2180 -- GL Date Project#Start: If the profile PO: Validate GL Period has been
2181 -- set to Redefault, default the distribution's GL date.
2182
2183 IF Nvl(FND_PROFILE.VALUE('PO_VALIDATE_GL_PERIOD'), 'Y') = 'R' THEN
2184 PO_PERIODS_SV.get_gl_date(x_sob_id => PO_PDOI_PARAMS.g_sys.sob_id,
2185 x_gl_date => x_dists.gl_encumbered_date_tbl);
2186 END IF;
2187 -- GL Date Project#End
2188
2189 PO_PERIODS_SV.get_period_info(p_roll_logic => NULL,
2190 p_set_of_books_id => PO_PDOI_PARAMS.g_sys.sob_id,
2191 p_date_tbl => x_dists.gl_encumbered_date_tbl,
2192 x_period_name_tbl => x_dists.gl_encumbered_period_tbl,
2193 x_period_year_tbl => l_period_year_tbl,
2194 x_period_num_tbl => l_period_num_tbl,
2195 x_quarter_num_tbl => l_quarter_num_tbl,
2196 x_invalid_period_flag => l_invalid_period_flag);
2197 END IF;
2198
2199 d_position := 100;
2200
2201 -- call utility method to default standard who columns
2202 PO_PDOI_MAINPROC_UTL_PVT.default_who_columns
2203 (
2204 x_last_update_date_tbl => x_dists.last_update_date_tbl,
2205 x_last_updated_by_tbl => x_dists.last_updated_by_tbl,
2206 x_last_update_login_tbl => x_dists.last_update_login_tbl,
2207 x_creation_date_tbl => x_dists.creation_date_tbl,
2208 x_created_by_tbl => x_dists.created_by_tbl,
2209 x_request_id_tbl => x_dists.request_id_tbl,
2210 x_program_application_id_tbl => x_dists.program_application_id_tbl,
2211 x_program_id_tbl => x_dists.program_id_tbl,
2212 x_program_update_date_tbl => x_dists.program_update_date_tbl
2213 );
2214
2215 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_DIST_DEFAULT);
2216
2217 IF (PO_LOG.d_proc) THEN
2218 PO_LOG.proc_end (d_module);
2219 END IF;
2220
2221 EXCEPTION
2222 WHEN OTHERS THEN
2223 PO_MESSAGE_S.add_exc_msg
2224 (
2225 p_pkg_name => d_pkg_name,
2226 p_procedure_name => d_api_name || '.' || d_position
2227 );
2228 RAISE;
2229 END default_dists;
2230
2231 -----------------------------------------------------------------------
2232 --Start of Comments
2233 --Name: validate_dists
2234 --Function:
2235 -- validate distribution attributes read within a batch
2236 --Parameters:
2237 --IN:
2238 --IN OUT:
2239 --x_dists
2240 -- The record contains the values to be validated.
2241 -- If there is error(s) on any attribute of the distribution row,
2242 -- corresponding value in error_flag_tbl will be set with value
2243 -- FND_API.g_TRUE.
2244 --OUT:
2245 --End of Comments
2246 ------------------------------------------------------------------------
2247 PROCEDURE validate_dists
2248 (
2249 x_dists IN OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
2250 ) IS
2251
2252 d_api_name CONSTANT VARCHAR2(30) := 'validate_dists';
2253 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2254 d_position NUMBER;
2255
2256 l_distributions PO_DISTRIBUTIONS_VAL_TYPE := PO_DISTRIBUTIONS_VAL_TYPE();
2257 l_result_type VARCHAR2(30);
2258 l_results po_validation_results_type;
2259 l_parameter_name_tbl PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
2260 l_parameter_value_tbl PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
2261
2262 BEGIN
2263 d_position := 0;
2264
2265 IF (PO_LOG.d_proc) THEN
2266 PO_LOG.proc_begin(d_module);
2267 END IF;
2268
2269 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_DIST_VALIDATE);
2270
2271 l_distributions.interface_id := x_dists.intf_dist_id_tbl;
2272 l_distributions.amount_ordered := x_dists.amount_ordered_tbl;
2273 l_distributions.line_order_type_lookup_code := x_dists.ln_order_type_lookup_code_tbl;
2274 l_distributions.line_purchase_basis := x_dists.ln_purchase_basis_tbl; -- bug 7644072
2275 l_distributions.quantity_ordered := x_dists.quantity_ordered_tbl;
2276 l_distributions.destination_organization_id := x_dists.dest_org_id_tbl;
2277 l_distributions.ship_to_organization_id := x_dists.loc_ship_to_org_id_tbl;
2278 l_distributions.deliver_to_location_id := x_dists.deliver_to_loc_id_tbl;
2279 l_distributions.deliver_to_person_id := x_dists.deliver_to_person_id_tbl;
2280 l_distributions.destination_type_code := x_dists.dest_type_code_tbl;
2281 l_distributions.distribution_type := x_dists.loc_shipment_type_tbl; -- PDOI for Complex PO Project
2282 l_distributions.line_item_id := x_dists.ln_item_id_tbl;
2283 l_distributions.po_header_id := x_dists.hd_po_header_id_tbl;
2284 l_distributions.accrue_on_receipt_flag := x_dists.loc_accrue_on_receipt_flag_tbl;
2285 l_distributions.destination_subinventory := x_dists.dest_subinventory_tbl;
2286 l_distributions.wip_entity_id := x_dists.wip_entity_id_tbl;
2287 l_distributions.wip_repetitive_schedule_id := x_dists.wip_rep_schedule_id_tbl;
2288 l_distributions.prevent_encumbrance_flag := x_dists.prevent_encumbrance_flag_tbl;
2289 l_distributions.code_combination_id := x_dists.charge_account_id_tbl;
2290 l_distributions.gl_encumbered_date := x_dists.gl_encumbered_date_tbl;
2291 l_distributions.budget_account_id := x_dists.budget_account_id_tbl;
2292 l_distributions.accrual_account_id := x_dists.accrual_account_id_tbl;
2293 l_distributions.variance_account_id := x_dists.variance_account_id_tbl;
2294 l_distributions.dest_variance_account_id := x_dists.dest_variance_account_id_tbl;
2295 l_distributions.project_accounting_context := x_dists.project_accounting_context_tbl;
2296 l_distributions.project_id := x_dists.project_id_tbl;
2297 l_distributions.task_id := x_dists.task_id_tbl;
2298 l_distributions.expenditure_type := x_dists.expenditure_type_tbl;
2299 l_distributions.expenditure_organization_id := x_dists.expenditure_org_id_tbl;
2300 l_distributions.header_need_by_date := x_dists.loc_need_by_date_tbl;
2301 l_distributions.promised_date := x_dists.loc_promised_date_tbl;
2302 l_distributions.expenditure_item_date := x_dists.expenditure_item_date_tbl;
2303 l_distributions.hdr_agent_id := x_dists.hd_agent_id_tbl;
2304 l_distributions.loc_outsourced_assembly := x_dists.loc_outsourced_assembly_tbl;
2305 l_distributions.transaction_flow_header_id := x_dists.loc_txn_flow_header_id_tbl;
2306 l_distributions.tax_recovery_override_flag := x_dists.tax_recovery_override_flag_tbl;
2307
2308 -- ACRN proj
2309 l_distributions.ACRN := x_dists.ACRN_tbl;
2310
2311 -- CLM Partial Funding Changes
2312 l_distributions.partial_funded_flag := x_dists.partial_funded_flag_tbl;
2313 l_distributions.funded_value := x_dists.funded_value_tbl;
2314 l_distributions.style_id := x_dists.hd_style_id_tbl;
2315 l_distributions.clm_defence_funding := x_dists.clm_defence_funding_tbl;
2316 /*CLM PDOI Integration*/
2317 l_distributions.intf_line_id := x_dists.intf_line_id_tbl;
2318
2319 d_position := 10;
2320
2321 l_parameter_name_tbl.EXTEND(5);
2322 l_parameter_value_tbl.EXTEND(5);
2323
2324 l_parameter_name_tbl(1) := 'CHART_OF_ACCOUNT_ID';
2325 l_parameter_value_tbl(1) := PO_PDOI_PARAMS.g_sys.coa_id;
2326
2327 l_parameter_name_tbl(2) := 'PO_ENCUMBRANCE_FLAG';
2328 l_parameter_value_tbl(2) := PO_PDOI_PARAMS.g_sys.po_encumbrance_flag;
2329
2330 l_parameter_name_tbl(3) := 'OPERATING_UNIT';
2331 l_parameter_value_tbl(3) := PO_PDOI_PARAMS.g_request.org_id;
2332
2333 l_parameter_name_tbl(4) := 'EXPENSE_ACCRUAL_CODE';
2334 l_parameter_value_tbl(4) := PO_PDOI_PARAMS.g_sys.expense_accrual_code;
2335
2336 l_parameter_name_tbl(5) := 'ALLOW_TAX_RATE_OVERRIDE';
2337 l_parameter_value_tbl(5) := PO_PDOI_PARAMS.g_profile.allow_tax_rate_override;
2338
2339 PO_VALIDATIONS.validate_pdoi
2340 (
2341 p_distributions => l_distributions,
2342 p_parameter_name_tbl => l_parameter_name_tbl,
2343 p_parameter_value_tbl => l_parameter_value_tbl,
2344 x_result_type => l_result_type,
2345 x_results => l_results
2346 );
2347
2348 IF (PO_LOG.d_stmt) THEN
2349 PO_LOG.stmt(d_module, d_position, 'vaidate dists returns :' || l_result_type);
2350 END IF;
2351
2352 d_position := 20;
2353
2354 IF l_result_type = po_validations.c_result_type_failure THEN
2355 IF (PO_LOG.d_stmt) THEN
2356 PO_LOG.stmt(d_module, d_position, 'vaidate dists returns');
2357 END IF;
2358
2359 PO_PDOI_ERR_UTL.process_val_type_errors
2360 (
2361 x_results => l_results,
2362 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
2363 p_distributions => x_dists
2364 );
2365
2366 d_position := 30;
2367
2368 populate_error_flag
2369 (
2370 x_results => l_results,
2371 x_dists => x_dists
2372 );
2373 END IF;
2374
2375 IF l_result_type = po_validations.c_result_type_fatal THEN
2376 IF (PO_LOG.d_stmt) THEN
2377 PO_LOG.stmt(d_module, d_position, 'vaidate dists return fatal');
2378 END IF;
2379
2380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2381 END IF;
2382
2383 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_DIST_VALIDATE);
2384
2385 IF (PO_LOG.d_proc) THEN
2386 PO_LOG.proc_end (d_module);
2387 END IF;
2388
2389 EXCEPTION
2390 WHEN OTHERS THEN
2391 PO_MESSAGE_S.add_exc_msg
2392 (
2393 p_pkg_name => d_pkg_name,
2394 p_procedure_name => d_api_name || '.' || d_position
2395 );
2396 RAISE;
2397 END validate_dists;
2398
2399 -------------------------------------------------------------------------
2400 --------------------- PRIVATE PROCEDURES --------------------------------
2401 -------------------------------------------------------------------------
2402
2403 -----------------------------------------------------------------------
2404 --Start of Comments
2405 --Name: derive_ship_to_ou_id
2406 --Function:
2407 -- perform logic to derive ship_to_ou_id from ship_to_org_id
2408 -- in batch mode
2409 --Parameters:
2410 --IN:
2411 --p_key
2412 -- identifier in the temp table on the derived result
2413 --p_index_tbl
2414 -- indexes of the records
2415 --p_ship_to_org_tbl
2416 -- list of ship_to_org_tbl in the batch.
2417 --IN OUT:
2418 --x_ship_to_ou_id_tbl
2419 -- contains the derived result if original value is null;
2420 -- original value will not be changed if it is not null
2421 --OUT:
2422 --End of Comments
2423 ------------------------------------------------------------------------
2424 PROCEDURE derive_ship_to_ou_id
2425 (
2426 p_key IN po_session_gt.key%TYPE,
2427 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2428 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
2429 x_ship_to_ou_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2430 ) IS
2431
2432 d_api_name CONSTANT VARCHAR2(30) := 'derive_ship_to_ou_id';
2433 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2434 d_position NUMBER;
2435
2436 -- variable to hold derived result
2437 l_index_tbl PO_TBL_NUMBER;
2438 l_result_tbl PO_TBL_NUMBER;
2439 BEGIN
2440 d_position := 0;
2441
2442 IF (PO_LOG.d_proc) THEN
2443 PO_LOG.proc_begin(d_module, 'p_ship_to_org_id_tbl', p_ship_to_org_id_tbl);
2444 PO_LOG.proc_begin(d_module, 'x_ship_to_ou_id_tbl', x_ship_to_ou_id_tbl);
2445 END IF;
2446
2447 x_ship_to_ou_id_tbl := PO_TBL_NUMBER();
2448 x_ship_to_ou_id_tbl.EXTEND(p_index_tbl.COUNT);
2449
2450 -- query database to get derived result in batch mode
2451 FORALL i IN 1..p_index_tbl.COUNT
2452 INSERT INTO po_session_gt(key, num1, num2)
2453 SELECT p_key,
2454 p_index_tbl(i),
2455 TO_NUMBER(org_info.org_information3)
2456 FROM hr_organization_information org_info,
2457 mtl_parameters param
2458 WHERE param.organization_id = p_ship_to_org_id_tbl(i)
2459 AND param.organization_id = org_info.organization_id
2460 AND org_info.org_information_context = 'Accounting Information';
2461
2462 d_position := 10;
2463
2464 -- retrieve values from temp table and delete the records at the
2465 -- same time
2466 DELETE FROM po_session_gt
2467 WHERE key = p_key
2468 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2469
2470 d_position := 20;
2471
2472 -- set value back to x_ship_to_ou_id_tbl
2473 FOR i IN 1..l_index_tbl.COUNT
2474 LOOP
2475 IF (PO_LOG.d_stmt) THEN
2476 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2477 PO_LOG.stmt(d_module, d_position, 'new ship_to ou id',
2478 l_result_tbl(i));
2479 END IF;
2480
2481 x_ship_to_ou_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2482 END LOOP;
2483
2484 IF (PO_LOG.d_proc) THEN
2485 PO_LOG.proc_end (d_module);
2486 END IF;
2487
2488 EXCEPTION
2489 WHEN OTHERS THEN
2490 PO_MESSAGE_S.add_exc_msg
2491 (
2492 p_pkg_name => d_pkg_name,
2493 p_procedure_name => d_api_name || '.' || d_position
2494 );
2495 RAISE;
2496 END derive_ship_to_ou_id;
2497
2498 -----------------------------------------------------------------------
2499 --Start of Comments
2500 --Name: derive_deliver_to_loc_id
2501 --Function:
2502 -- perform logic to derive deliver_to_location_id from
2503 -- deliver_to_location in batch mode
2504 --Parameters:
2505 --IN:
2506 --p_key
2507 -- identifier in the temp table on the derived result
2508 --p_index_tbl
2509 -- indexes of the records
2510 --p_deliver_to_loc_tbl
2511 -- list of deliver_to_location values within the batch
2512 --IN OUT:
2513 --x_deliver_to_loc_id_tbl
2514 -- contains the derived result if original value is null;
2515 -- original value will not be changed if it is not null
2516 --OUT:
2517 --End of Comments
2518 ------------------------------------------------------------------------
2519 PROCEDURE derive_deliver_to_loc_id
2520 (
2521 p_key IN po_session_gt.key%TYPE,
2522 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2523 p_deliver_to_loc_tbl IN PO_TBL_VARCHAR100,
2524 x_deliver_to_loc_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2525 ) IS
2526
2527 d_api_name CONSTANT VARCHAR2(30) := 'derive_deliver_to_loc_id';
2528 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2529 d_position NUMBER;
2530
2531 -- variable to hold derived result
2532 l_index_tbl PO_TBL_NUMBER;
2533 l_result_tbl PO_TBL_NUMBER;
2534 BEGIN
2535 d_position := 0;
2536
2537 IF (PO_LOG.d_proc) THEN
2538 PO_LOG.proc_begin(d_module, 'p_deliver_to_loc_tbl',
2539 p_deliver_to_loc_tbl);
2540 PO_LOG.proc_begin(d_module, 'x_deliver_to_loc_id_tbl',
2541 x_deliver_to_loc_id_tbl);
2542 END IF;
2543
2544 -- query database to get derived result in batch mode
2545 FORALL i IN 1..p_index_tbl.COUNT
2546 INSERT INTO po_session_gt(key, num1, num2)
2547 SELECT p_key,
2548 p_index_tbl(i),
2549 location_id
2550 FROM hr_locations
2551 WHERE p_deliver_to_loc_tbl(i) IS NOT NULL
2552 AND x_deliver_to_loc_id_tbl(i) IS NULL
2553 AND location_code = p_deliver_to_loc_tbl(i);
2554
2555 d_position := 10;
2556
2557 -- retrieve values from temp table and delete the records at the
2558 -- same time
2559 DELETE FROM po_session_gt
2560 WHERE key = p_key
2561 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2562
2563 d_position := 20;
2564
2565 -- set value back to x_deliver_to_loc_id_tbl
2566 FOR i IN 1..l_index_tbl.COUNT
2567 LOOP
2568 IF (PO_LOG.d_stmt) THEN
2569 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2570 PO_LOG.stmt(d_module, d_position, 'new deliver_to loc id',
2571 l_result_tbl(i));
2572 END IF;
2573
2574 x_deliver_to_loc_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2575 END LOOP;
2576
2577 IF (PO_LOG.d_proc) THEN
2578 PO_LOG.proc_end (d_module);
2579 END IF;
2580
2581 EXCEPTION
2582 WHEN OTHERS THEN
2583 PO_MESSAGE_S.add_exc_msg
2584 (
2585 p_pkg_name => d_pkg_name,
2586 p_procedure_name => d_api_name || '.' || d_position
2587 );
2588 RAISE;
2589 END derive_deliver_to_loc_id;
2590
2591 -----------------------------------------------------------------------
2592 --Start of Comments
2593 --Name: derive_deliver_to_person_id
2594 --Function:
2595 -- perform logic to derive deliver_to_person_id from
2596 -- deliver_to_person_full_name in batch mode
2597 --Parameters:
2598 --IN:
2599 --p_key
2600 -- identifier in the temp table on the derived result
2601 --p_index_tbl
2602 -- indexes of the records
2603 --p_person_name_tbl
2604 -- list of deliver_to_person_full_name values in the batch
2605 --IN OUT:
2606 --x_person_id_tbl
2607 -- contains the derived result if original value is null;
2608 -- original value will not be changed if it is not null
2609 --OUT:
2610 --End of Comments
2611 ------------------------------------------------------------------------
2612 PROCEDURE derive_deliver_to_person_id
2613 (
2614 p_key IN po_session_gt.key%TYPE,
2615 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2616 p_person_name_tbl IN PO_TBL_VARCHAR2000,
2617 x_person_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2618 ) IS
2619
2620 d_api_name CONSTANT VARCHAR2(30) := 'derive_deliver_to_person_id';
2621 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2622 d_position NUMBER;
2623
2624 -- variable to hold derived result
2625 l_index_tbl PO_TBL_NUMBER;
2626 l_result_tbl PO_TBL_NUMBER;
2627 BEGIN
2628 d_position := 0;
2629
2630 IF (PO_LOG.d_proc) THEN
2631 PO_LOG.proc_begin(d_module, 'p_person_name_tbl', p_person_name_tbl);
2632 PO_LOG.proc_begin(d_module, 'x_person_id_tbl', x_person_id_tbl);
2633 END IF;
2634
2635 -- query database to get derived result in batch mode
2636 FORALL i IN 1..p_index_tbl.COUNT
2637 INSERT INTO po_session_gt(key, num1, num2)
2638 SELECT p_key,
2639 p_index_tbl(i),
2640 employee_id
2641 FROM hr_employees_all_v
2642 WHERE p_person_name_tbl(i) IS NOT NULL
2643 AND x_person_id_tbl(i) IS NULL
2644 AND full_name = p_person_name_tbl(i);
2645
2646 d_position := 10;
2647
2648 -- retrieve values from temp table and delete the records at the
2649 -- same time
2650 DELETE FROM po_session_gt
2651 WHERE key = p_key
2652 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2653
2654 d_position := 20;
2655
2656 -- set value back to x_person_id_tbl
2657 FOR i IN 1..l_index_tbl.COUNT
2658 LOOP
2659 IF (PO_LOG.d_stmt) THEN
2660 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2661 PO_LOG.stmt(d_module, d_position, 'new deliver_to person id',
2662 l_result_tbl(i));
2663 END IF;
2664
2665 x_person_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2666 END LOOP;
2667
2668 IF (PO_LOG.d_proc) THEN
2669 PO_LOG.proc_end (d_module);
2670 END IF;
2671
2672 EXCEPTION
2673 WHEN OTHERS THEN
2674 PO_MESSAGE_S.add_exc_msg
2675 (
2676 p_pkg_name => d_pkg_name,
2677 p_procedure_name => d_api_name || '.' || d_position
2678 );
2679 RAISE;
2680 END derive_deliver_to_person_id;
2681
2682 -----------------------------------------------------------------------
2683 --Start of Comments
2684 --Name: derive_dest_type_code
2685 --Function:
2686 -- perform logic to derive destination_type_code from
2687 -- destination_type in batch mode
2688 --Parameters:
2689 --IN:
2690 --p_key
2691 -- identifier in the temp table on the derived result
2692 --p_index_tbl
2693 -- indexes of the records
2694 --p_dest_type_tbl
2695 -- list of destination_type values in the batch
2696 --IN OUT:
2697 --x_dest_type_code_tbl
2698 -- contains the derived result if original value is null;
2699 -- original value will not be changed if it is not null
2700 --OUT:
2701 --End of Comments
2702 ------------------------------------------------------------------------
2703 PROCEDURE derive_dest_type_code
2704 (
2705 p_key IN po_session_gt.key%TYPE,
2706 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2707 p_dest_type_tbl IN PO_TBL_VARCHAR30,
2708 x_dest_type_code_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
2709 ) IS
2710
2711 d_api_name CONSTANT VARCHAR2(30) := 'derive_dest_type_code';
2712 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2713 d_position NUMBER;
2714
2715 -- variable to hold derived result
2716 l_index_tbl PO_TBL_NUMBER;
2717 l_result_tbl PO_TBL_VARCHAR25;
2718 BEGIN
2719 d_position := 0;
2720
2721 IF (PO_LOG.d_proc) THEN
2722 PO_LOG.proc_begin(d_module, 'p_dest_type_tbl', p_dest_type_tbl);
2723 PO_LOG.proc_begin(d_module, 'x_dest_type_code_tbl', x_dest_type_code_tbl);
2724 END IF;
2725
2726 -- query database to get derived result in batch mode
2727 FORALL i IN 1..p_index_tbl.COUNT
2728 INSERT INTO po_session_gt(key, num1, char1)
2729 SELECT p_key,
2730 p_index_tbl(i),
2731 lookup_code
2732 FROM po_destination_types_all_v
2733 WHERE p_dest_type_tbl(i) IS NOT NULL
2734 AND x_dest_type_code_tbl(i) IS NULL
2735 AND displayed_field = p_dest_type_tbl(i);
2736
2737 d_position := 10;
2738
2739 -- retrieve values from temp table and delete the records at the
2740 -- same time
2741 DELETE FROM po_session_gt
2742 WHERE key = p_key
2743 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2744
2745 d_position := 20;
2746
2747 -- set value back to x_dest_type_code_tbl
2748 FOR i IN 1..l_index_tbl.COUNT
2749 LOOP
2750 IF (PO_LOG.d_stmt) THEN
2751 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2752 PO_LOG.stmt(d_module, d_position, 'new dest type code',
2753 l_result_tbl(i));
2754 END IF;
2755
2756 x_dest_type_code_tbl(l_index_tbl(i)) := l_result_tbl(i);
2757 END LOOP;
2758
2759 IF (PO_LOG.d_proc) THEN
2760 PO_LOG.proc_end (d_module);
2761 END IF;
2762
2763 EXCEPTION
2764 WHEN OTHERS THEN
2765 PO_MESSAGE_S.add_exc_msg
2766 (
2767 p_pkg_name => d_pkg_name,
2768 p_procedure_name => d_api_name || '.' || d_position
2769 );
2770 RAISE;
2771 END derive_dest_type_code;
2772
2773 -----------------------------------------------------------------------
2774 --Start of Comments
2775 --Name: derive_dest_org_id
2776 --Function:
2777 -- perform logic to derive destination_oragnization_id from
2778 -- destination_organization in batch mode
2779 --Parameters:
2780 --IN:
2781 --p_key
2782 -- identifier in the temp table on the derived result
2783 --p_index_tbl
2784 -- indexes of the records
2785 --p_dest_org_tbl
2786 -- list of destination_organization values in the batch
2787 --p_ship_to_org_id_tbl
2788 -- list of ship_to_organization_id values in the batch
2789 --IN OUT:
2790 --x_dest_org_id_tbl
2791 -- contains the derived result if original value is null;
2792 -- original value will not be changed if it is not null
2793 --OUT:
2794 --End of Comments
2795 ------------------------------------------------------------------------
2796 PROCEDURE derive_dest_org_id
2797 (
2798 p_key IN po_session_gt.key%TYPE,
2799 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2800 p_dest_org_tbl IN PO_TBL_VARCHAR100,
2801 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
2802 x_dest_org_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2803 ) IS
2804
2805 d_api_name CONSTANT VARCHAR2(30) := 'derive_dest_org_id';
2806 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2807 d_position NUMBER;
2808
2809 -- variable to hold derived result
2810 l_index_tbl PO_TBL_NUMBER;
2811 l_result_tbl PO_TBL_NUMBER;
2812 BEGIN
2813 d_position := 0;
2814
2815 IF (PO_LOG.d_proc) THEN
2816 PO_LOG.proc_begin(d_module, 'p_dest_org_tbl', p_dest_org_tbl);
2817 PO_LOG.proc_begin(d_module, 'p_ship_to_org_id_tbl', p_ship_to_org_id_tbl);
2818 PO_LOG.proc_begin(d_module, 'x_dest_org_id_tbl', x_dest_org_id_tbl);
2819 END IF;
2820
2821 -- query database to get derived result in batch mode
2822 FORALL i IN 1..p_index_tbl.COUNT
2823 INSERT INTO po_session_gt(key, num1, num2)
2824 SELECT p_key,
2825 p_index_tbl(i),
2826 organization_id
2827 FROM org_organization_definitions
2828 WHERE p_dest_org_tbl(i) IS NOT NULL
2829 AND x_dest_org_id_tbl(i) IS NULL
2830 AND organization_code = p_dest_org_tbl(i)
2831 AND TRUNC(sysdate) < NVL(disable_date, TRUNC(sysdate+1))
2832 AND inventory_enabled_flag = 'Y';
2833
2834 d_position := 10;
2835
2836 -- set destination_organization_id as ship_to_organization
2837 -- if destination_organization is null
2838 FORALL i IN 1..p_index_tbl.COUNT
2839 INSERT INTO po_session_gt(key, num1, num2)
2840 SELECT p_key,
2841 p_index_tbl(i),
2842 p_ship_to_org_id_tbl(i)
2843 FROM dual
2844 WHERE p_dest_org_tbl (i) IS NULL
2845 AND x_dest_org_id_tbl(i) IS NULL;
2846
2847 d_position := 20;
2848
2849 -- retrieve values from temp table and delete the records at the
2850 -- same time
2851 DELETE FROM po_session_gt
2852 WHERE key = p_key
2853 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2854
2855 d_position := 30;
2856
2857 -- set value back to x_dest_org_id_tbl
2858 FOR i IN 1..l_index_tbl.COUNT
2859 LOOP
2860 IF (PO_LOG.d_stmt) THEN
2861 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2862 PO_LOG.stmt(d_module, d_position, 'new dest org id',
2863 l_result_tbl(i));
2864 END IF;
2865
2866 x_dest_org_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2867 END LOOP;
2868
2869 IF (PO_LOG.d_proc) THEN
2870 PO_LOG.proc_end (d_module);
2871 END IF;
2872
2873 EXCEPTION
2874 WHEN OTHERS THEN
2875 PO_MESSAGE_S.add_exc_msg
2876 (
2877 p_pkg_name => d_pkg_name,
2878 p_procedure_name => d_api_name || '.' || d_position
2879 );
2880 RAISE;
2881 END derive_dest_org_id;
2882
2883 -----------------------------------------------------------------------
2884 --Start of Comments
2885 --Name: derive_wip_entity_id
2886 --Function:
2887 -- perform logic to derive wip_entity_id from
2888 -- wip_entity in batch mode
2889 --Parameters:
2890 --IN:
2891 --p_key
2892 -- identifier in the temp table on the derived result
2893 --p_index_tbl
2894 -- indexes of the records
2895 --p_wip_entity_tbl
2896 -- list of wip_entity values within the batch
2897 --p_dest_org_id_tbl
2898 -- list of destination_organization_id values within the batch
2899 --p_dest_type_code_tbl
2900 -- list of destination_type_code values within the batch
2901 --IN OUT:
2902 --x_wip_entity_id_tbl
2903 -- contains the derived result if original value is null;
2904 -- original value will not be changed if it is not null
2905 --OUT:
2906 --End of Comments
2907 ------------------------------------------------------------------------
2908 PROCEDURE derive_wip_entity_id
2909 (
2910 p_key IN po_session_gt.key%TYPE,
2911 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2912 p_wip_entity_tbl IN PO_TBL_VARCHAR2000,
2913 p_dest_org_id_tbl IN PO_TBL_NUMBER,
2914 p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
2915 x_wip_entity_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2916 ) IS
2917
2918 d_api_name CONSTANT VARCHAR2(30) := 'derive_wip_entity_id';
2919 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2920 d_position NUMBER;
2921
2922 -- variable to hold derived result
2923 l_index_tbl PO_TBL_NUMBER;
2924 l_result_tbl PO_TBL_NUMBER;
2925 BEGIN
2926 d_position := 0;
2927
2928 IF (PO_LOG.d_proc) THEN
2929 PO_LOG.proc_begin(d_module, 'p_dest_org_id_tbl', p_dest_org_id_tbl);
2930 PO_LOG.proc_begin(d_module, 'p_dest_org_id_tbl', p_dest_org_id_tbl);
2931 PO_LOG.proc_begin(d_module, 'p_dest_type_code_tbl', p_dest_type_code_tbl);
2932 PO_LOG.proc_begin(d_module, 'x_wip_entity_id_tbl', x_wip_entity_id_tbl);
2933 END IF;
2934
2935 -- query database to get derived result in batch mode
2936 FORALL i IN 1..p_index_tbl.COUNT
2937 INSERT INTO po_session_gt(key, num1, num2)
2938 SELECT p_key,
2939 p_index_tbl(i),
2940 wip_entity_id
2941 FROM wip_entities
2942 WHERE p_wip_entity_tbl(i) IS NOT NULL
2943 AND x_wip_entity_id_tbl(i) IS NULL
2944 AND p_dest_type_code_tbl(i) = 'SHOP FLOOR'
2945 AND wip_entity_name = p_wip_entity_tbl(i)
2946 AND organization_id = p_dest_org_id_tbl(i);
2947
2948 d_position := 10;
2949
2950 -- retrieve values from temp table and delete the records at the
2951 -- same time
2952 DELETE FROM po_session_gt
2953 WHERE key = p_key
2954 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2955
2956 d_position := 20;
2957
2958 -- set value back to x_wip_entity_id_tbl
2959 FOR i IN 1..l_index_tbl.COUNT
2960 LOOP
2961 IF (PO_LOG.d_stmt) THEN
2962 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2963 PO_LOG.stmt(d_module, d_position, 'new wip entity id',
2964 l_result_tbl(i));
2965 END IF;
2966
2967 x_wip_entity_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2968 END LOOP;
2969
2970 IF (PO_LOG.d_proc) THEN
2971 PO_LOG.proc_end (d_module);
2972 END IF;
2973
2974 EXCEPTION
2975 WHEN OTHERS THEN
2976 PO_MESSAGE_S.add_exc_msg
2977 (
2978 p_pkg_name => d_pkg_name,
2979 p_procedure_name => d_api_name || '.' || d_position
2980 );
2981 RAISE;
2982 END derive_wip_entity_id;
2983
2984 -----------------------------------------------------------------------
2985 --Start of Comments
2986 --Name: derive_wip_line_id
2987 --Function:
2988 -- perform logic to derive wip_line_id from
2989 -- wip_line_code in batch mode
2990 --Parameters:
2991 --IN:
2992 --p_key
2993 -- identifier in the temp table on the derived result
2994 --p_index_tbl
2995 -- indexes of the records
2996 --p_wip_line_code_tbl
2997 -- list of wip_line_code values within the batch
2998 --p_dest_org_id_tbl
2999 -- list of destination_organization_id values within the batch
3000 --p_dest_type_code_tbl
3001 -- list of destination_type_code values within the batch
3002 --IN OUT:
3003 --x_wip_line_id_tbl
3004 -- contains the derived result if original value is null;
3005 -- original value will not be changed if it is not null
3006 --OUT:
3007 --End of Comments
3008 ------------------------------------------------------------------------
3009 PROCEDURE derive_wip_line_id
3010 (
3011 p_key IN po_session_gt.key%TYPE,
3012 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3013 p_wip_line_code_tbl IN PO_TBL_VARCHAR30,
3014 p_dest_org_id_tbl IN PO_TBL_NUMBER,
3015 p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
3016 x_wip_line_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
3017 ) IS
3018
3019 d_api_name CONSTANT VARCHAR2(30) := 'derive_wip_line_id';
3020 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3021 d_position NUMBER;
3022
3023 -- variable to hold derived result
3024 l_index_tbl PO_TBL_NUMBER;
3025 l_result_tbl PO_TBL_NUMBER;
3026 BEGIN
3027 d_position := 0;
3028
3029 IF (PO_LOG.d_proc) THEN
3030 PO_LOG.proc_begin(d_module, 'p_wip_line_code_tbl', p_wip_line_code_tbl);
3031 PO_LOG.proc_begin(d_module, 'p_dest_org_id_tbl', p_dest_org_id_tbl);
3032 PO_LOG.proc_begin(d_module, 'p_dest_type_code_tbl', p_dest_type_code_tbl);
3033 PO_LOG.proc_begin(d_module, 'x_wip_line_id_tbl', x_wip_line_id_tbl);
3034 END IF;
3035
3036 -- query database to get derived result in batch mode
3037 FORALL i IN 1..p_index_tbl.COUNT
3038 INSERT INTO po_session_gt(key, num1, num2)
3039 SELECT p_key,
3040 p_index_tbl(i),
3041 line_id
3042 FROM wip_lines
3043 WHERE p_wip_line_code_tbl(i) IS NOT NULL
3044 AND x_wip_line_id_tbl(i) IS NULL
3045 AND p_dest_type_code_tbl(i) = 'SHOP FLOOR'
3046 AND line_code = p_wip_line_code_tbl(i)
3047 AND organization_id = p_dest_org_id_tbl(i);
3048
3049 d_position := 10;
3050
3051 -- retrieve values from temp table and delete the records at the
3052 -- same time
3053 DELETE FROM po_session_gt
3054 WHERE key = p_key
3055 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3056
3057 d_position := 20;
3058
3059 -- set value back to x_wip_line_id_tbl
3060 FOR i IN 1..l_index_tbl.COUNT
3061 LOOP
3062 IF (PO_LOG.d_stmt) THEN
3063 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3064 PO_LOG.stmt(d_module, d_position, 'new wip line id',
3065 l_result_tbl(i));
3066 END IF;
3067
3068 x_wip_line_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
3069 END LOOP;
3070
3071 IF (PO_LOG.d_proc) THEN
3072 PO_LOG.proc_end (d_module);
3073 END IF;
3074
3075 EXCEPTION
3076 WHEN OTHERS THEN
3077 PO_MESSAGE_S.add_exc_msg
3078 (
3079 p_pkg_name => d_pkg_name,
3080 p_procedure_name => d_api_name || '.' || d_position
3081 );
3082 RAISE;
3083 END derive_wip_line_id;
3084
3085 -----------------------------------------------------------------------
3086 --Start of Comments
3087 --Name: derive_ship_to_ou_coa_id
3088 --Function:
3089 -- perform logic to derive ship_to_ou_coa_id in batch mode
3090 --Parameters:
3091 --IN:
3092 --p_key
3093 -- identifier in the temp table on the derived result
3094 --p_index_tbl
3095 -- indexes of the records
3096 --p_dest_org_id_tbl
3097 -- list of destination_organization_id values within the batch
3098 --p_txn_flow_header_id_tbl
3099 -- list of transaction_flow_header_id values within the batch
3100 --p_dest_charge_account_id_tbl
3101 -- list of charge_account_id values within the batch
3102 --IN OUT:
3103 --x_ship_to_ou_coa_id_tbl
3104 -- contains the derived result if original value is null;
3105 -- original value will not be changed if it is not null
3106 --OUT:
3107 --End of Comments
3108 ------------------------------------------------------------------------
3109 PROCEDURE derive_ship_to_ou_coa_id
3110 (
3111 p_key IN po_session_gt.key%TYPE,
3112 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3113 p_dest_org_id_tbl IN PO_TBL_NUMBER,
3114 p_txn_flow_header_id_tbl IN PO_TBL_NUMBER,
3115 p_dest_charge_account_id_tbl IN PO_TBL_NUMBER,
3116 x_ship_to_ou_coa_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
3117 ) IS
3118
3119 d_api_name CONSTANT VARCHAR2(30) := 'derive_ship_to_ou_coa_id';
3120 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3121 d_position NUMBER;
3122
3123 -- variable to hold derived result
3124 l_index_tbl PO_TBL_NUMBER;
3125 l_result_tbl PO_TBL_NUMBER;
3126 BEGIN
3127 d_position := 0;
3128
3129 IF (PO_LOG.d_proc) THEN
3130 PO_LOG.proc_begin(d_module, 'p_dest_org_id_tbl', p_dest_org_id_tbl);
3131 PO_LOG.proc_begin(d_module, 'p_txn_flow_header_id_tbl',
3132 p_txn_flow_header_id_tbl);
3133 PO_LOG.proc_begin(d_module, 'p_dest_charge_account_id_tbl',
3134 p_dest_charge_account_id_tbl);
3135 PO_LOG.proc_begin(d_module, 'x_ship_to_ou_coa_id_tbl',
3136 x_ship_to_ou_coa_id_tbl);
3137 END IF;
3138
3139 -- query database to get derived result in batch mode
3140 FORALL i IN 1..p_index_tbl.COUNT
3141 INSERT INTO po_session_gt(key, num1, num2)
3142 SELECT p_key,
3143 p_index_tbl(i),
3144 sob.chart_of_accounts_id
3145 FROM gl_sets_of_books sob,
3146 hr_organization_information org_info,
3147 mtl_parameters param
3148 WHERE p_txn_flow_header_id_tbl(i) IS NOT NULL
3149 AND p_dest_charge_account_id_tbl(i) IS NULL
3150 AND param.organization_id = p_dest_org_id_tbl(i)
3151 AND param.organization_id = org_info.organization_id
3152 AND org_info.org_information_context = 'Accounting Information'
3153 AND TO_NUMBER(org_info.org_information1) = sob.set_of_books_id;
3154
3155 d_position := 10;
3156
3157 -- retrieve values from temp table and delete the records at the
3158 -- same time
3159 DELETE FROM po_session_gt
3160 WHERE key = p_key
3161 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3162
3163 d_position := 20;
3164
3165 -- set value back to x_wip_line_id_tbl
3166 FOR i IN 1..l_index_tbl.COUNT
3167 LOOP
3168 IF (PO_LOG.d_stmt) THEN
3169 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3170 PO_LOG.stmt(d_module, d_position, 'new ship_to ou coa id',
3171 l_result_tbl(i));
3172 END IF;
3173
3174 x_ship_to_ou_coa_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
3175 END LOOP;
3176
3177 IF (PO_LOG.d_proc) THEN
3178 PO_LOG.proc_end (d_module);
3179 END IF;
3180
3181 EXCEPTION
3182 WHEN OTHERS THEN
3183 PO_MESSAGE_S.add_exc_msg
3184 (
3185 p_pkg_name => d_pkg_name,
3186 p_procedure_name => d_api_name || '.' || d_position
3187 );
3188 RAISE;
3189 END derive_ship_to_ou_coa_id;
3190
3191 -----------------------------------------------------------------------
3192 --Start of Comments
3193 --Name: derive_bom_resource_id
3194 --Function:
3195 -- perform logic to derive bom_resource_id from
3196 -- bom_resource_code in batch mode
3197 --Parameters:
3198 --IN:
3199 --p_key
3200 -- identifier in the temp table on the derived result
3201 --p_index_tbl
3202 -- indexes of the records
3203 --p_bom_resource_code_tbl
3204 -- list of bom_resource_code values within the batch
3205 --p_dest_org_id_tbl
3206 -- list of destination_organziation_id values within the batch
3207 --IN OUT:
3208 --x_bom_resource_id_tbl
3209 -- contains the derived result if original value is null;
3210 -- original value will not be changed if it is not null
3211 --OUT:
3212 --End of Comments
3213 ------------------------------------------------------------------------
3214 PROCEDURE derive_bom_resource_id
3215 (
3216 p_key IN po_session_gt.key%TYPE,
3217 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3218 p_bom_resource_code_tbl IN PO_TBL_VARCHAR30,
3219 p_dest_org_id_tbl IN PO_TBL_NUMBER,
3220 x_bom_resource_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
3221 ) IS
3222
3223 d_api_name CONSTANT VARCHAR2(30) := 'derive_bom_resource_id';
3224 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3225 d_position NUMBER;
3226
3227 -- variable to hold derived result
3228 l_index_tbl PO_TBL_NUMBER;
3229 l_result_tbl PO_TBL_NUMBER;
3230 BEGIN
3231 d_position := 0;
3232
3233 IF (PO_LOG.d_proc) THEN
3234 PO_LOG.proc_begin(d_module, 'p_bom_resource_code_tbl',
3235 p_bom_resource_code_tbl);
3236 PO_LOG.proc_begin(d_module, 'p_dest_org_id_tbl', p_dest_org_id_tbl);
3237 PO_LOG.proc_begin(d_module, 'x_bom_resource_id_tbl',
3238 x_bom_resource_id_tbl);
3239 END IF;
3240
3241 -- query database to get derived result in batch mode
3242 FORALL i IN 1..p_index_tbl.COUNT
3243 INSERT INTO po_session_gt(key, num1, num2)
3244 SELECT p_key,
3245 p_index_tbl(i),
3246 resource_id
3247 FROM bom_resources
3248 WHERE p_bom_resource_code_tbl(i) IS NOT NULL
3249 AND x_bom_resource_id_tbl(i) IS NULL
3250 AND resource_code = p_bom_resource_code_tbl(i)
3251 AND organization_id = p_dest_org_id_tbl(i);
3252
3253 d_position := 10;
3254
3255 -- retrieve values from temp table and delete the records at the
3256 -- same time
3257 DELETE FROM po_session_gt
3258 WHERE key = p_key
3259 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3260
3261 d_position := 20;
3262
3263 -- set value back to x_bom_resource_id_tbl
3264 FOR i IN 1..l_index_tbl.COUNT
3265 LOOP
3266 IF (PO_LOG.d_stmt) THEN
3267 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3268 PO_LOG.stmt(d_module, d_position, 'new bom resource id',
3269 l_result_tbl(i));
3270 END IF;
3271
3272 x_bom_resource_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
3273 END LOOP;
3274
3275 IF (PO_LOG.d_proc) THEN
3276 PO_LOG.proc_end (d_module);
3277 END IF;
3278
3279 EXCEPTION
3280 WHEN OTHERS THEN
3281 PO_MESSAGE_S.add_exc_msg
3282 (
3283 p_pkg_name => d_pkg_name,
3284 p_procedure_name => d_api_name || '.' || d_position
3285 );
3286 RAISE;
3287 END derive_bom_resource_id;
3288
3289
3290 -----------------------------------------------------------------------
3291 --Start of Comments
3292 --Name: derive_account_id
3293 --Function:
3294 -- Derive code_combination_id based of account segments
3295 --Parameters:
3296 --IN:
3297 --p_account_number
3298 -- reserved
3299 --p_chart_of_accounts_id
3300 -- chart of account id
3301 --p_account_segment1-30
3302 -- account segment values
3303 --IN OUT:
3304 --OUT:
3305 --x_account_id
3306 -- account id if one can be derived.
3307 --End of Comments
3308 ------------------------------------------------------------------------
3309 PROCEDURE derive_account_id
3310 ( p_account_number IN VARCHAR2,
3311 p_chart_of_accounts_id IN NUMBER,
3312 p_account_segment1 IN VARCHAR2, p_account_segment2 IN VARCHAR2,
3313 p_account_segment3 IN VARCHAR2, p_account_segment4 IN VARCHAR2,
3314 p_account_segment5 IN VARCHAR2, p_account_segment6 IN VARCHAR2,
3315 p_account_segment7 IN VARCHAR2, p_account_segment8 IN VARCHAR2,
3316 p_account_segment9 IN VARCHAR2, p_account_segment10 IN VARCHAR2,
3317 p_account_segment11 IN VARCHAR2, p_account_segment12 IN VARCHAR2,
3318 p_account_segment13 IN VARCHAR2, p_account_segment14 IN VARCHAR2,
3319 p_account_segment15 IN VARCHAR2, p_account_segment16 IN VARCHAR2,
3320 p_account_segment17 IN VARCHAR2, p_account_segment18 IN VARCHAR2,
3321 p_account_segment19 IN VARCHAR2, p_account_segment20 IN VARCHAR2,
3322 p_account_segment21 IN VARCHAR2, p_account_segment22 IN VARCHAR2,
3323 p_account_segment23 IN VARCHAR2, p_account_segment24 IN VARCHAR2,
3324 p_account_segment25 IN VARCHAR2, p_account_segment26 IN VARCHAR2,
3325 p_account_segment27 IN VARCHAR2, p_account_segment28 IN VARCHAR2,
3326 p_account_segment29 IN VARCHAR2, p_account_segment30 IN VARCHAR2,
3327 x_account_id OUT NOCOPY NUMBER
3328 ) IS
3329
3330 d_api_name CONSTANT VARCHAR2(30) := 'derive_account_id';
3331 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3332 d_position NUMBER;
3333
3334 l_sql VARCHAR2(4000);
3335 BEGIN
3336 d_position := 0;
3337
3338 IF (PO_LOG.d_proc) THEN
3339 PO_LOG.proc_begin (d_module);
3340 END IF;
3341
3342 IF (p_account_segment1 IS NULL AND p_account_segment2 IS NULL AND
3343 p_account_segment3 IS NULL AND p_account_segment4 IS NULL AND
3344 p_account_segment5 IS NULL AND p_account_segment6 IS NULL AND
3345 p_account_segment7 IS NULL AND p_account_segment8 IS NULL AND
3346 p_account_segment9 IS NULL AND p_account_segment10 IS NULL AND
3347 p_account_segment11 IS NULL AND p_account_segment12 IS NULL AND
3348 p_account_segment13 IS NULL AND p_account_segment14 IS NULL AND
3349 p_account_segment15 IS NULL AND p_account_segment16 IS NULL AND
3350 p_account_segment17 IS NULL AND p_account_segment18 IS NULL AND
3351 p_account_segment19 IS NULL AND p_account_segment20 IS NULL AND
3352 p_account_segment21 IS NULL AND p_account_segment22 IS NULL AND
3353 p_account_segment23 IS NULL AND p_account_segment24 IS NULL AND
3354 p_account_segment25 IS NULL AND p_account_segment26 IS NULL AND
3355 p_account_segment27 IS NULL AND p_account_segment28 IS NULL AND
3356 p_account_segment29 IS NULL AND p_account_segment30 IS NULL) THEN
3357
3358 -- No segment has been provided
3359 RETURN;
3360 END IF;
3361
3362 d_position := 10;
3363
3364
3365 -- bug5010268
3366 -- Chagned p_chart_of_accounts_id to bind variable
3367 l_sql := 'SELECT GCC.code_combination_id FROM gl_code_combinations GCC ' ||
3368 'WHERE GCC.chart_of_accounts_id = :p_chart_of_accounts_id ';
3369
3370 add_account_segment_clause('segment1', p_account_segment1, l_sql);
3371 add_account_segment_clause('segment2', p_account_segment2, l_sql);
3372 add_account_segment_clause('segment3', p_account_segment3, l_sql);
3373 add_account_segment_clause('segment4', p_account_segment4, l_sql);
3374 add_account_segment_clause('segment5', p_account_segment5, l_sql);
3375 add_account_segment_clause('segment6', p_account_segment6, l_sql);
3376 add_account_segment_clause('segment7', p_account_segment7, l_sql);
3377 add_account_segment_clause('segment8', p_account_segment8, l_sql);
3378 add_account_segment_clause('segment9', p_account_segment9, l_sql);
3379 add_account_segment_clause('segment10', p_account_segment10, l_sql);
3380 add_account_segment_clause('segment11', p_account_segment11, l_sql);
3381 add_account_segment_clause('segment12', p_account_segment12, l_sql);
3382 add_account_segment_clause('segment13', p_account_segment13, l_sql);
3383 add_account_segment_clause('segment14', p_account_segment14, l_sql);
3384 add_account_segment_clause('segment15', p_account_segment15, l_sql);
3385 add_account_segment_clause('segment16', p_account_segment16, l_sql);
3386 add_account_segment_clause('segment17', p_account_segment17, l_sql);
3387 add_account_segment_clause('segment18', p_account_segment18, l_sql);
3388 add_account_segment_clause('segment19', p_account_segment19, l_sql);
3389 add_account_segment_clause('segment20', p_account_segment20, l_sql);
3390 add_account_segment_clause('segment21', p_account_segment21, l_sql);
3391 add_account_segment_clause('segment22', p_account_segment22, l_sql);
3392 add_account_segment_clause('segment23', p_account_segment23, l_sql);
3393 add_account_segment_clause('segment24', p_account_segment24, l_sql);
3394 add_account_segment_clause('segment25', p_account_segment25, l_sql);
3395 add_account_segment_clause('segment26', p_account_segment26, l_sql);
3396 add_account_segment_clause('segment27', p_account_segment27, l_sql);
3397 add_account_segment_clause('segment28', p_account_segment28, l_sql);
3398 add_account_segment_clause('segment29', p_account_segment29, l_sql);
3399 add_account_segment_clause('segment30', p_account_segment30, l_sql);
3400
3401 d_position := 20;
3402
3403 IF (PO_LOG.d_stmt) THEN
3404 --PO_LOG.stmt(d_module, ' stmt to generate acct id: ' || l_sql);
3405 --< Shared Proc 14223789 Start >
3406 PO_LOG.stmt(d_module, d_position, ' stmt to generate acct id: ', l_sql);
3407 --< Shared Proc 14223789 End >
3408 END IF;
3409
3410 BEGIN
3411
3412 -- bug5010268
3413 -- Bind p_chart_of_accounts_id as well
3414 EXECUTE IMMEDIATE l_sql INTO x_account_id
3415 USING p_chart_of_accounts_id,
3416 p_account_segment1, p_account_segment2,
3417 p_account_segment3, p_account_segment4,
3418 p_account_segment5, p_account_segment6,
3419 p_account_segment7, p_account_segment8,
3420 p_account_segment9, p_account_segment10,
3421 p_account_segment11, p_account_segment12,
3422 p_account_segment13, p_account_segment14,
3423 p_account_segment15, p_account_segment16,
3424 p_account_segment17, p_account_segment18,
3425 p_account_segment19, p_account_segment20,
3426 p_account_segment21, p_account_segment22,
3427 p_account_segment23, p_account_segment24,
3428 p_account_segment25, p_account_segment26,
3429 p_account_segment27, p_account_segment28,
3430 p_account_segment29, p_account_segment30;
3431 EXCEPTION
3432 WHEN NO_DATA_FOUND THEN
3433 IF (PO_LOG.d_stmt) THEN
3434 --PO_LOG.stmt(d_module, 'cannot find account id based on segments provided');
3435 --< Shared Proc 14223789 Start >
3436 PO_LOG.stmt(d_module, d_position, 'cannot find account id based on segments provided');
3437 --< Shared Proc 14223789 End >
3438 END IF;
3439
3440 x_account_id := NULL;
3441 END;
3442
3443 IF (PO_LOG.d_proc) THEN
3444 PO_LOG.proc_end (d_module);
3445 END IF;
3446
3447 EXCEPTION
3448 WHEN OTHERS THEN
3449 PO_MESSAGE_S.add_exc_msg
3450 (
3451 p_pkg_name => d_pkg_name,
3452 p_procedure_name => d_api_name || '.' || d_position
3453 );
3454 RAISE;
3455 END derive_account_id;
3456
3457 -----------------------------------------------------------------------
3458 --Start of Comments
3459 --Name: add_account_segment_clause
3460 --Function:
3461 -- append where clause containing the comparison of a particular segment
3462 -- to the overall sql statement
3463 --Parameters:
3464 --IN:
3465 --p_segment_name
3466 -- segment name
3467 --p_segment_value
3468 -- segment value
3469 --x_sql
3470 -- sql to append where clause to.
3471 --IN OUT:
3472 --OUT:
3473 --x_account_id
3474 -- account id if one can be derived.
3475 --End of Comments
3476 ------------------------------------------------------------------------
3477 PROCEDURE add_account_segment_clause
3478 ( p_segment_name IN VARCHAR2,
3479 p_segment_value IN VARCHAR2,
3480 x_sql IN OUT NOCOPY VARCHAR2
3481 ) IS
3482
3483 d_api_name CONSTANT VARCHAR2(30) := 'append_account_segment_clause';
3484 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3485 d_position NUMBER;
3486
3487 BEGIN
3488
3489 IF (p_segment_value IS NOT NULL) THEN
3490 x_sql := x_sql || ' AND GCC.' || p_segment_name || ' = :' || p_segment_name;
3491 ELSE
3492 -- if value is null, originally we do not need bind variable. However,
3493 -- to make coding simple we are still appending a dummy NVL operation
3494 -- just to make sure that we always have the same number of bind variables.
3495
3496 x_sql := x_sql || ' AND NVL(:' || p_segment_name || ', GCC.' ||
3497 p_segment_name || ') IS NULL';
3498 END IF;
3499
3500 EXCEPTION
3501 WHEN OTHERS THEN
3502 PO_MESSAGE_S.add_exc_msg
3503 (
3504 p_pkg_name => d_pkg_name,
3505 p_procedure_name => d_api_name || '.' || d_position
3506 );
3507 RAISE;
3508 END add_account_segment_clause;
3509
3510
3511 -----------------------------------------------------------------------
3512 --Start of Comments
3513 --Name: validate_null_for_project_info
3514 --Function:
3515 -- validate whether the project related fields are null when this
3516 -- procedure is called. If any of the field is not null, an error
3517 -- will be inserted into the error table
3518 --Parameters:
3519 --IN:
3520 --p_index
3521 -- index of distribution row within the batch
3522 --IN OUT:
3523 --x_dists
3524 -- record of tables to hold all distribution attribute values
3525 -- within the batch
3526 --OUT:
3527 --End of Comments
3528 ------------------------------------------------------------------------
3529 PROCEDURE validate_null_for_project_info
3530 (
3531 p_index IN NUMBER,
3532 x_dists IN OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
3533 ) IS
3534
3535 d_api_name CONSTANT VARCHAR2(30) := 'validate_null_for_project_info';
3536 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3537 d_position NUMBER;
3538
3539 l_error_msg VARCHAR2(30);
3540 BEGIN
3541 d_position := 0;
3542
3543 IF (PO_LOG.d_proc) THEN
3544 PO_LOG.proc_begin(d_module);
3545 END IF;
3546
3547 IF (x_dists.project_id_tbl(p_index) IS NOT NULL OR
3548 x_dists.project_tbl(p_index) IS NOT NULL OR
3549 x_dists.task_id_tbl(p_index) IS NOT NULL OR
3550 x_dists.task_tbl(p_index) IS NOT NULL OR
3551 x_dists.expenditure_tbl(p_index) IS NOT NULL OR
3552 x_dists.expenditure_type_tbl(p_index) IS NOT NULL OR
3553 NVL(x_dists.project_accounting_context_tbl(p_index), 'N') = 'Y' OR
3554 x_dists.expenditure_org_tbl(p_index) IS NOT NULL OR
3555 x_dists.expenditure_org_id_tbl(p_index) IS NOT NULL OR
3556 x_dists.expenditure_item_date_tbl(p_index) IS NOT NULL OR
3557 x_dists.end_item_unit_number_tbl(p_index) IS NOT NULL) THEN
3558
3559 d_position := 10;
3560
3561 -- add errors
3562 IF (x_dists.ln_purchase_basis_tbl(p_index) = 'SERVICES') THEN
3563 l_error_msg := 'PO_SVC_PA_11I10_NOT_ENABLED';
3564 ELSE
3565 l_error_msg := 'PO_SVC_PA_FPM_NOT_ENABLED';
3566 END IF;
3567
3568 IF (PO_LOG.d_stmt) THEN
3569 PO_LOG.stmt(d_module, d_position, 'error_msg', l_error_msg);
3570 END IF;
3571
3572 PO_PDOI_ERR_UTL.add_fatal_error
3573 (
3574 p_interface_header_id => x_dists.intf_header_id_tbl(p_index),
3575 p_interface_line_id => x_dists.intf_line_id_tbl(p_index),
3576 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(p_index),
3577 p_interface_distribution_id => x_dists.intf_dist_id_tbl(p_index),
3578 p_error_message_name => l_error_msg,
3579 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
3580 p_column_name => 'PROJECT_ID',
3581 p_column_value => x_dists.project_id_tbl(p_index)
3582 );
3583
3584 x_dists.error_flag_tbl(p_index) := FND_API.g_TRUE;
3585 END IF;
3586
3587 d_position := 20;
3588
3589 IF (x_dists.award_id_tbl(p_index) IS NOT NULL OR
3590 x_dists.award_num_tbl(p_index) IS NOT NULL) THEN
3591 -- add errors
3592 IF (x_dists.ln_purchase_basis_tbl(p_index) = 'SERVICES') THEN
3593 l_error_msg := 'PO_SVC_GMS_11I10_NOT_ENABLED';
3594 ELSE
3595 l_error_msg := 'PO_SVC_GMS_FPM_NOT_ENABLED';
3596 END IF;
3597
3598 IF (PO_LOG.d_stmt) THEN
3599 PO_LOG.stmt(d_module, d_position, 'error_msg', l_error_msg);
3600 END IF;
3601
3602 PO_PDOI_ERR_UTL.add_fatal_error
3603 (
3604 p_interface_header_id => x_dists.intf_header_id_tbl(p_index),
3605 p_interface_line_id => x_dists.intf_line_id_tbl(p_index),
3606 p_interface_line_location_id => x_dists.intf_line_loc_id_tbl(p_index),
3607 p_interface_distribution_id => x_dists.intf_dist_id_tbl(p_index),
3608 p_error_message_name => l_error_msg,
3609 p_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
3610 p_column_name => 'AWARD_ID',
3611 p_column_value => x_dists.award_id_tbl(p_index)
3612 );
3613
3614 x_dists.error_flag_tbl(p_index) := FND_API.g_TRUE;
3615 END IF;
3616
3617 IF (PO_LOG.d_proc) THEN
3618 PO_LOG.proc_end (d_module);
3619 END IF;
3620
3621 EXCEPTION
3622 WHEN OTHERS THEN
3623 PO_MESSAGE_S.add_exc_msg
3624 (
3625 p_pkg_name => d_pkg_name,
3626 p_procedure_name => d_api_name || '.' || d_position
3627 );
3628 RAISE;
3629 END validate_null_for_project_info;
3630
3631 -----------------------------------------------------------------------
3632 --Start of Comments
3633 --Name: derive_project_info
3634 --Function:
3635 -- derive project related fields when this procedure is called
3636 -- the derived fields include:
3637 -- project_id, task_id,
3638 -- expenditure_type, expenditure_organization_id
3639 --Parameters:
3640 --IN:
3641 --p_key
3642 -- identifier in the temp table on the derived result
3643 --p_index_tbl
3644 -- indexes of the records
3645 --p_derive_row_tbl
3646 -- table to mark for which row project fields should be
3647 -- derived.
3648 --IN OUT:
3649 --x_dists
3650 -- record of tables to hold all distribution attribute values
3651 -- within the batch
3652 --OUT:
3653 --End of Comments
3654 ------------------------------------------------------------------------
3655 PROCEDURE derive_project_info
3656 (
3657 p_key IN po_session_gt.key%TYPE,
3658 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3659 p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
3660 x_dists IN OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
3661 ) IS
3662
3663 d_api_name CONSTANT VARCHAR2(30) := 'derive_project_info';
3664 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3665 d_position NUMBER;
3666
3667 l_index NUMBER;
3668 BEGIN
3669 d_position := 0;
3670
3671 IF (PO_LOG.d_proc) THEN
3672 PO_LOG.proc_begin(d_module);
3673
3674 PO_LOG.proc_begin(d_module, 'p_derive_row_tbl.COUNT', p_derive_row_tbl.COUNT);
3675 l_index := p_derive_row_tbl.FIRST;
3676 WHILE (l_index IS NOT NULL)
3677 LOOP
3678 PO_LOG.proc_begin(d_module, 'need to derive project info for line', l_index);
3679 l_index := p_derive_row_tbl.NEXT(l_index);
3680 END LOOP;
3681 END IF;
3682
3683 -- derive project id
3684 derive_project_id
3685 (
3686 p_key => p_key,
3687 p_index_tbl => p_index_tbl,
3688 p_project_tbl => x_dists.project_tbl,
3689 p_dest_type_code_tbl => x_dists.dest_type_code_tbl,
3690 p_ship_to_org_id_tbl => x_dists.loc_ship_to_org_id_tbl,
3691 p_ship_to_ou_id_tbl => x_dists.ship_to_ou_id_tbl,
3692 p_derive_row_tbl => p_derive_row_tbl,
3693 x_project_id_tbl => x_dists.project_id_tbl
3694 );
3695
3696 d_position := 10;
3697
3698 -- derive task id
3699 derive_task_id
3700 (
3701 p_key => p_key,
3702 p_index_tbl => p_index_tbl,
3703 p_task_tbl => x_dists.task_tbl,
3704 p_dest_type_code_tbl => x_dists.dest_type_code_tbl,
3705 p_project_id_tbl => x_dists.project_id_tbl,
3706 p_ship_to_ou_id_tbl => x_dists.ship_to_ou_id_tbl,
3707 p_derive_row_tbl => p_derive_row_tbl,
3708 x_task_id_tbl => x_dists.task_id_tbl
3709 );
3710
3711 d_position := 20;
3712
3713 -- derive expenditure type
3714 derive_expenditure_type
3715 (
3716 p_key => p_key,
3717 p_index_tbl => p_index_tbl,
3718 p_expenditure_tbl => x_dists.expenditure_tbl,
3719 p_project_id_tbl => x_dists.project_id_tbl,
3720 p_derive_row_tbl => p_derive_row_tbl,
3721 x_expenditure_type_tbl => x_dists.expenditure_type_tbl
3722 );
3723
3724 d_position := 30;
3725
3726 -- derive expenditure organiation id
3727 derive_expenditure_org_id
3728 (
3729 p_key => p_key,
3730 p_index_tbl => p_index_tbl,
3731 p_expenditure_org_tbl => x_dists.expenditure_org_tbl,
3732 p_project_id_tbl => x_dists.project_id_tbl,
3733 p_derive_row_tbl => p_derive_row_tbl,
3734 x_expenditure_org_id_tbl => x_dists.expenditure_org_id_tbl
3735 );
3736
3737 IF (PO_LOG.d_proc) THEN
3738 PO_LOG.proc_end (d_module);
3739 END IF;
3740
3741 EXCEPTION
3742 WHEN OTHERS THEN
3743 PO_MESSAGE_S.add_exc_msg
3744 (
3745 p_pkg_name => d_pkg_name,
3746 p_procedure_name => d_api_name || '.' || d_position
3747 );
3748 RAISE;
3749 END derive_project_info;
3750
3751 -----------------------------------------------------------------------
3752 --Start of Comments
3753 --Name: derive_project_id
3754 --Function:
3755 -- perform logic to derive project_id from project in batch mode
3756 --Parameters:
3757 --IN:
3758 --p_key
3759 -- identifier in the temp table on the derived result
3760 --p_index_tbl
3761 -- indexes of the records
3762 --p_project_tbl
3763 -- list of project values within the batch
3764 --p_dest_type_code_tbl
3765 -- list of destination_type_code values within the batch
3766 --p_ship_to_org_id_tbl
3767 -- list of ship_to_organization_id values within the batch
3768 --p_ship_to_ou_id_tbl
3769 -- list of ship_to_ou_id values within the batch
3770 --p_derive_row_tbl
3771 -- table to mark rows for which project derivation logic
3772 -- needs to be performed
3773 --IN OUT:
3774 --x_project_id_tbl
3775 -- contains the derived result if original value is null;
3776 -- original value will not be changed if it is not null
3777 --OUT:
3778 --End of Comments
3779 ------------------------------------------------------------------------
3780 PROCEDURE derive_project_id
3781 (
3782 p_key IN po_session_gt.key%TYPE,
3783 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3784 p_project_tbl IN PO_TBL_VARCHAR30,
3785 p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
3786 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
3787 p_ship_to_ou_id_tbl IN PO_TBL_NUMBER,
3788 p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
3789 x_project_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
3790 ) IS
3791
3792 d_api_name CONSTANT VARCHAR2(30) := 'derive_project_id';
3793 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3794 d_position NUMBER;
3795
3796 -- variable to hold derived result
3797 l_index_tbl PO_TBL_NUMBER;
3798 l_result_tbl PO_TBL_NUMBER;
3799 BEGIN
3800 d_position := 0;
3801
3802 IF (PO_LOG.d_proc) THEN
3803 PO_LOG.proc_begin(d_module, 'p_project_tbl', p_project_tbl);
3804 PO_LOG.proc_begin(d_module, 'p_dest_type_code_tbl', p_dest_type_code_tbl);
3805 PO_LOG.proc_begin(d_module, 'p_ship_to_org_id_tbl', p_ship_to_org_id_tbl);
3806 PO_LOG.proc_begin(d_module, 'p_ship_to_ou_id_tbl', p_ship_to_ou_id_tbl);
3807 PO_LOG.proc_begin(d_module, 'x_project_id_tbl', x_project_id_tbl);
3808 END IF;
3809
3810 -- query database to get derived result in batch mode
3811 -- query different views based on value of destination_type_code
3812 FORALL i IN INDICES OF p_derive_row_tbl
3813 INSERT INTO po_session_gt(key, num1, num2)
3814 SELECT p_key,
3815 p_index_tbl(i),
3816 project_id
3817 FROM pa_projects_expend_v
3818 WHERE p_project_tbl(i) IS NOT NULL
3819 AND x_project_id_tbl(i) IS NULL
3820 AND p_dest_type_code_tbl(i) = 'EXPENSE'
3821 AND project_name = p_project_tbl(i);
3822
3823 d_position := 10;
3824
3825 FORALL i IN INDICES OF p_derive_row_tbl
3826 INSERT INTO po_session_gt(key, num1, num2)
3827 SELECT p_key,
3828 p_index_tbl(i),
3829 project_id
3830 FROM pjm_projects_org_ou_v
3831 WHERE p_project_tbl(i) IS NOT NULL
3832 AND x_project_id_tbl(i) IS NULL
3833 AND p_dest_type_code_tbl(i) <> 'EXPENSE'
3834 AND project_name = p_project_tbl(i)
3835 AND inventory_organization_id = p_ship_to_org_id_tbl(i)
3836 AND NVL(org_id, p_ship_to_ou_id_tbl(i)) = p_ship_to_ou_id_tbl(i);
3837
3838 d_position := 20;
3839
3840 -- retrieve values from temp table and delete the records at the
3841 -- same time
3842 DELETE FROM po_session_gt
3843 WHERE key = p_key
3844 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3845
3846 d_position := 30;
3847
3848 -- set value back to x_project_id_tbl
3849 FOR i IN 1..l_index_tbl.COUNT
3850 LOOP
3851 IF (PO_LOG.d_stmt) THEN
3852 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3853 PO_LOG.stmt(d_module, d_position, 'new project id',
3854 l_result_tbl(i));
3855 END IF;
3856
3857 x_project_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
3858 END LOOP;
3859
3860 IF (PO_LOG.d_proc) THEN
3861 PO_LOG.proc_end (d_module);
3862 END IF;
3863
3864 EXCEPTION
3865 WHEN OTHERS THEN
3866 PO_MESSAGE_S.add_exc_msg
3867 (
3868 p_pkg_name => d_pkg_name,
3869 p_procedure_name => d_api_name || '.' || d_position
3870 );
3871 RAISE;
3872 END derive_project_id;
3873
3874 -----------------------------------------------------------------------
3875 --Start of Comments
3876 --Name: derive_task_id
3877 --Function:
3878 -- perform logic to derive task_id from task in batch mode
3879 --Parameters:
3880 --IN:
3881 --p_key
3882 -- identifier in the temp table on the derived result
3883 --p_index_tbl
3884 -- indexes of the records
3885 --p_task_tbl
3886 -- list of task values within the batch
3887 --p_dest_type_code_tbl
3888 -- list of destination_type_code values within the batch
3889 --p_project_id_tbl
3890 -- list of project_id values within the batch
3891 --p_ship_to_ou_id_tbl
3892 -- list of ship_to_ou_id values within the batch
3893 --p_derive_row_tbl
3894 -- table to mark rows for which project derivation logic
3895 -- needs to be performed
3896 --IN OUT:
3897 --x_task_id_tbl
3898 -- contains the derived result if original value is null;
3899 -- original value will not be changed if it is not null
3900 --OUT:
3901 --End of Comments
3902 ------------------------------------------------------------------------
3903 PROCEDURE derive_task_id
3904 (
3905 p_key IN po_session_gt.key%TYPE,
3906 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3907 p_task_tbl IN PO_TBL_VARCHAR2000, --- Bug#16505809
3908 p_dest_type_code_tbl IN PO_TBL_VARCHAR30,
3909 p_project_id_tbl IN PO_TBL_NUMBER,
3910 p_ship_to_ou_id_tbl IN PO_TBL_NUMBER,
3911 p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
3912 x_task_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
3913 ) IS
3914
3915 d_api_name CONSTANT VARCHAR2(30) := 'derive_task_id';
3916 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3917 d_position NUMBER;
3918
3919 -- variable to hold derived result
3920 l_index_tbl PO_TBL_NUMBER;
3921 l_result_tbl PO_TBL_NUMBER;
3922 BEGIN
3923 d_position := 0;
3924
3925 IF (PO_LOG.d_proc) THEN
3926 PO_LOG.proc_begin(d_module, 'p_task_tbl', p_task_tbl);
3927 PO_LOG.proc_begin(d_module, 'p_dest_type_code_tbl', p_dest_type_code_tbl);
3928 PO_LOG.proc_begin(d_module, 'p_project_id_tbl', p_project_id_tbl);
3929 PO_LOG.proc_begin(d_module, 'p_ship_to_ou_id_tbl', p_ship_to_ou_id_tbl);
3930 PO_LOG.proc_begin(d_module, 'x_task_id_tbl', x_task_id_tbl);
3931 END IF;
3932
3933 -- query database to get derived result in batch mode
3934 -- query different views based on value of destination_type_code
3935 FORALL i IN INDICES OF p_derive_row_tbl
3936 INSERT INTO po_session_gt(key, num1, num2)
3937 SELECT p_key,
3938 p_index_tbl(i),
3939 task_id
3940 FROM pa_tasks_expend_v
3941 WHERE p_task_tbl(i) IS NOT NULL
3942 AND x_task_id_tbl(i) IS NULL
3943 AND p_dest_type_code_tbl(i) = 'EXPENSE'
3944 AND project_id = p_project_id_tbl(i)
3945 AND task_number = p_task_tbl(i);
3946
3947 d_position := 10;
3948
3949 FORALL i IN INDICES OF p_derive_row_tbl
3950 INSERT INTO po_session_gt(key, num1, num2)
3951 SELECT p_key,
3952 p_index_tbl(i),
3953 task_id
3954 FROM pa_tasks_all_expend_v
3955 WHERE p_task_tbl(i) IS NOT NULL
3956 AND x_task_id_tbl(i) IS NULL
3957 AND p_dest_type_code_tbl(i) <> 'EXPENSE'
3958 AND project_id = p_project_id_tbl(i)
3959 AND task_number = p_task_tbl(i)
3960 AND NVL(expenditure_org_id, p_ship_to_ou_id_tbl(i)) = p_ship_to_ou_id_tbl(i);
3961
3962 d_position := 20;
3963
3964 -- retrieve values from temp table and delete the records at the
3965 -- same time
3966 DELETE FROM po_session_gt
3967 WHERE key = p_key
3968 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3969
3970 d_position := 30;
3971
3972 -- set value back to x_task_id_tbl
3973 FOR i IN 1..l_index_tbl.COUNT
3974 LOOP
3975 IF (PO_LOG.d_stmt) THEN
3976 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3977 PO_LOG.stmt(d_module, d_position, 'new task id',
3978 l_result_tbl(i));
3979 END IF;
3980
3981 x_task_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
3982 END LOOP;
3983
3984 IF (PO_LOG.d_proc) THEN
3985 PO_LOG.proc_end (d_module);
3986 END IF;
3987
3988 EXCEPTION
3989 WHEN OTHERS THEN
3990 PO_MESSAGE_S.add_exc_msg
3991 (
3992 p_pkg_name => d_pkg_name,
3993 p_procedure_name => d_api_name || '.' || d_position
3994 );
3995 RAISE;
3996 END derive_task_id;
3997
3998 -----------------------------------------------------------------------
3999 --Start of Comments
4000 --Name: derive_expenditure_type
4001 --Function:
4002 -- perform logic to derive expenditure_type from expenditure in batch mode
4003 --Parameters:
4004 --IN:
4005 --p_key
4006 -- identifier in the temp table on the derived result
4007 --p_index_tbl
4008 -- indexes of the records
4009 --p_expenditure_tbl
4010 -- list of expenditure values within the batch
4011 --p_project_id_tbl
4012 -- list of project_id values within the batch
4013 --p_derive_row_tbl
4014 -- table to mark rows for which project derivation logic
4015 -- needs to be performed
4016 --IN OUT:
4017 --x_expenditure_type_tbl
4018 -- contains the derived result if original value is null;
4019 -- original value will not be changed if it is not null
4020 --OUT:
4021 --End of Comments
4022 ------------------------------------------------------------------------
4023 PROCEDURE derive_expenditure_type
4024 (
4025 p_key IN po_session_gt.key%TYPE,
4026 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4027 p_expenditure_tbl IN PO_TBL_VARCHAR100,
4028 p_project_id_tbl IN PO_TBL_NUMBER,
4029 p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
4030 x_expenditure_type_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
4031 ) IS
4032
4033 d_api_name CONSTANT VARCHAR2(30) := 'derive_expenditure_type';
4034 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
4035 d_position NUMBER;
4036
4037 -- variable to hold derived result
4038 l_index_tbl PO_TBL_NUMBER;
4039 l_result_tbl PO_TBL_VARCHAR30;
4040 BEGIN
4041 d_position := 0;
4042
4043 IF (PO_LOG.d_proc) THEN
4044 PO_LOG.proc_begin(d_module, 'p_expenditure_tbl', p_expenditure_tbl);
4045 PO_LOG.proc_begin(d_module, 'p_project_id_tbl', p_project_id_tbl);
4046 PO_LOG.proc_begin(d_module, 'x_expenditure_type_tbl', x_expenditure_type_tbl);
4047 END IF;
4048
4049 -- query database to get derived result in batch mode
4050 FORALL i IN INDICES OF p_derive_row_tbl
4051 INSERT INTO po_session_gt(key, num1, char1)
4052 SELECT p_key,
4053 p_index_tbl(i),
4054 expenditure_type
4055 FROM pa_expenditure_types_v
4056 WHERE p_expenditure_tbl(i) IS NOT NULL
4057 AND x_expenditure_type_tbl(i) IS NULL
4058 AND p_project_id_tbl(i) IS NOT NULL
4059 AND description = p_expenditure_tbl(i);
4060
4061 d_position := 10;
4062
4063 -- retrieve values from temp table and delete the records at the
4064 -- same time
4065 DELETE FROM po_session_gt
4066 WHERE key = p_key
4067 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4068
4069 d_position := 20;
4070
4071 -- set value back to x_expenditure_type_tbl
4072 FOR i IN 1..l_index_tbl.COUNT
4073 LOOP
4074 IF (PO_LOG.d_stmt) THEN
4075 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
4076 PO_LOG.stmt(d_module, d_position, 'new expenditure type',
4077 l_result_tbl(i));
4078 END IF;
4079
4080 x_expenditure_type_tbl(l_index_tbl(i)) := l_result_tbl(i);
4081 END LOOP;
4082
4083 IF (PO_LOG.d_proc) THEN
4084 PO_LOG.proc_end (d_module);
4085 END IF;
4086
4087 EXCEPTION
4088 WHEN OTHERS THEN
4089 PO_MESSAGE_S.add_exc_msg
4090 (
4091 p_pkg_name => d_pkg_name,
4092 p_procedure_name => d_api_name || '.' || d_position
4093 );
4094 RAISE;
4095 END derive_expenditure_type;
4096
4097 -----------------------------------------------------------------------
4098 --Start of Comments
4099 --Name: derive_expenditure_org_id
4100 --Function:
4101 -- perform logic to derive expenditure_organization_id from
4102 -- expenditure_organization in batch mode
4103 --Parameters:
4104 --IN:
4105 --p_key
4106 -- identifier in the temp table on the derived result
4107 --p_index_tbl
4108 -- indexes of the records
4109 --p_expenditure_org_tbl
4110 -- list of expenditure_organization values within the batch
4111 --p_project_id_tbl
4112 -- list of project_id values within the batch
4113 --p_derive_row_tbl
4114 -- table to mark rows for which project derivation logic
4115 -- needs to be performed
4116 --IN OUT:
4117 --x_expenditure_org_id_tbl
4118 -- contains the derived result if original value is null;
4119 -- original value will not be changed if it is not null
4120 --OUT:
4121 --End of Comments
4122 ------------------------------------------------------------------------
4123 PROCEDURE derive_expenditure_org_id
4124 (
4125 p_key IN po_session_gt.key%TYPE,
4126 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4127 p_expenditure_org_tbl IN PO_TBL_VARCHAR100,
4128 p_project_id_tbl IN PO_TBL_NUMBER,
4129 p_derive_row_tbl IN DBMS_SQL.NUMBER_TABLE,
4130 x_expenditure_org_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4131 ) IS
4132
4133 d_api_name CONSTANT VARCHAR2(30) := 'derive_expenditure_org_id';
4134 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
4135 d_position NUMBER;
4136
4137 -- variable to hold derived result
4138 l_index_tbl PO_TBL_NUMBER;
4139 l_result_tbl PO_TBL_NUMBER;
4140 BEGIN
4141 d_position := 0;
4142
4143 IF (PO_LOG.d_proc) THEN
4144 PO_LOG.proc_begin(d_module, 'p_expenditure_org_tbl', p_expenditure_org_tbl);
4145 PO_LOG.proc_begin(d_module, 'p_project_id_tbl', p_project_id_tbl);
4146 PO_LOG.proc_begin(d_module, 'x_expenditure_org_id_tbl',
4147 x_expenditure_org_id_tbl);
4148 END IF;
4149
4150 -- query database to get derived result in batch mode
4151 FORALL i IN INDICES OF p_derive_row_tbl
4152 INSERT INTO po_session_gt(key, num1, num2)
4153 SELECT p_key,
4154 p_index_tbl(i),
4155 organization_id
4156 FROM pa_organizations_expend_v
4157 WHERE p_expenditure_org_tbl(i) IS NOT NULL
4158 AND x_expenditure_org_id_tbl(i) IS NULL
4159 AND p_project_id_tbl(i) IS NOT NULL
4160 AND name = p_expenditure_org_tbl(i)
4161 AND active_flag = 'Y';
4162
4163 d_position := 10;
4164
4165 -- retrieve values from temp table and delete the records at the
4166 -- same time
4167 DELETE FROM po_session_gt
4168 WHERE key = p_key
4169 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4170
4171 d_position := 20;
4172
4173 -- set value back to x_expenditure_org_tbl
4174 FOR i IN 1..l_index_tbl.COUNT
4175 LOOP
4176 IF (PO_LOG.d_stmt) THEN
4177 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
4178 PO_LOG.stmt(d_module, d_position, 'new expenditure org id',
4179 l_result_tbl(i));
4180 END IF;
4181
4182 x_expenditure_org_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
4183 END LOOP;
4184
4185 IF (PO_LOG.d_proc) THEN
4186 PO_LOG.proc_end (d_module);
4187 END IF;
4188
4189 EXCEPTION
4190 WHEN OTHERS THEN
4191 PO_MESSAGE_S.add_exc_msg
4192 (
4193 p_pkg_name => d_pkg_name,
4194 p_procedure_name => d_api_name || '.' || d_position
4195 );
4196 RAISE;
4197 END derive_expenditure_org_id;
4198
4199 -----------------------------------------------------------------------
4200 --Start of Comments
4201 --Name: get_item_status
4202 --Function:
4203 -- extract item_status for each distribution row
4204 --Parameters:
4205 --IN:
4206 --p_key
4207 -- identifier in the temp table on the derived result
4208 --p_index_tbl
4209 -- indexes of the records
4210 --p_item_id_tbl
4211 -- list of item_id values within the batch
4212 --p_ship_to_org_id_tbl
4213 -- list of ship_to_organization_id values within the batcj
4214 --IN OUT:
4215 --x_item_status_tbl
4216 -- returned status extracted from the query
4217 --OUT:
4218 --End of Comments
4219 ------------------------------------------------------------------------
4220 PROCEDURE get_item_status
4221 (
4222 p_key IN po_session_gt.key%TYPE,
4223 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4224 p_item_id_tbl IN PO_TBL_NUMBER,
4225 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
4226 x_item_status_tbl OUT NOCOPY PO_TBL_VARCHAR1
4227 ) IS
4228
4229 d_api_name CONSTANT VARCHAR2(30) := 'get_item_status';
4230 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
4231 d_position NUMBER;
4232
4233 -- variable to hold derived result
4234 l_index_tbl PO_TBL_NUMBER;
4235 l_result_tbl PO_TBL_VARCHAR1;
4236 BEGIN
4237 d_position := 0;
4238
4239 IF (PO_LOG.d_proc) THEN
4240 PO_LOG.proc_begin(d_module, 'p_item_id_tbl', p_item_id_tbl);
4241 PO_LOG.proc_begin(d_module, 'p_ship_to_org_id_tbl', p_ship_to_org_id_tbl);
4242 END IF;
4243
4244 -- initialize out parameter
4245 x_item_status_tbl := PO_TBL_VARCHAR1();
4246 x_item_status_tbl.EXTEND(p_index_tbl.COUNT);
4247
4248 FORALL i IN 1..p_index_tbl.COUNT
4249 INSERT INTO po_session_gt(key, num1, char1)
4250 SELECT p_key,
4251 p_index_tbl(i),
4252 DECODE(outside_operation_flag,'Y','O',
4253 DECODE(stock_enabled_flag,'Y','E','D'))
4254 FROM mtl_system_items
4255 WHERE organization_id = p_ship_to_org_id_tbl(i)
4256 AND inventory_item_id = p_item_id_tbl(i);
4257
4258 d_position := 10;
4259
4260 -- retrieve values from temp table and delete the records at the
4261 -- same time
4262 DELETE FROM po_session_gt
4263 WHERE key = p_key
4264 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4265
4266 d_position := 20;
4267
4268 -- set value back to x_item_status_tbl
4269 FOR i IN 1..l_index_tbl.COUNT
4270 LOOP
4271 IF (PO_LOG.d_stmt) THEN
4272 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
4273 PO_LOG.stmt(d_module, d_position, 'item status',
4274 l_result_tbl(i));
4275 END IF;
4276
4277 x_item_status_tbl(l_index_tbl(i)) := l_result_tbl(i);
4278 END LOOP;
4279
4280 IF (PO_LOG.d_proc) THEN
4281 PO_LOG.proc_end (d_module);
4282 END IF;
4283
4284 EXCEPTION
4285 WHEN OTHERS THEN
4286 PO_MESSAGE_S.add_exc_msg
4287 (
4288 p_pkg_name => d_pkg_name,
4289 p_procedure_name => d_api_name || '.' || d_position
4290 );
4291 RAISE;
4292 END get_item_status;
4293
4294 -----------------------------------------------------------------------
4295 --Start of Comments
4296 --Name: default_account_ids
4297 --Function:
4298 -- Set default values for accrual_account_id, variance_account_id and
4299 -- budget_account_id
4300 --Parameters:
4301 --IN:
4302 --p_dest_type_code
4303 -- destination type code for the distribution record
4304 --p_dest_org_id
4305 -- destination organization id for the distribution record
4306 --p_dest_subinventory
4307 -- destination subinventory for the distribution record
4308 --p_item_id
4309 -- line level inventory item id
4310 --p_po_encumbrance_flag
4311 -- flag which indicates whether encumbrance is enabled on PO
4312 --p_charge_account_id
4313 -- charge account id for the distribution record
4314 --IN OUT:
4315 --x_accrual_account_id
4316 -- accrual account id which is to be defaulted if empty
4317 --x_budget_account_id
4318 -- budget account id which is to be defaulted if empty
4319 --x_variance_account_id
4320 -- variance account id which is to be defaulted if empty
4321 --OUT:
4322 --End of Comments
4323 ------------------------------------------------------------------------
4324 PROCEDURE default_account_ids
4325 (
4326 p_dest_type_code IN VARCHAR2,
4327 p_dest_org_id IN NUMBER,
4328 p_dest_subinventory IN VARCHAR2,
4329 p_item_id IN NUMBER,
4330 p_po_encumbrance_flag IN VARCHAR2,
4331 p_charge_account_id IN NUMBER,
4332 x_accrual_account_id IN OUT NOCOPY NUMBER,
4333 x_budget_account_id IN OUT NOCOPY NUMBER,
4334 x_variance_account_id IN OUT NOCOPY NUMBER,
4335 x_entity_type IN NUMBER, /* Encumbrance Project */
4336 x_wip_entity_id IN NUMBER /* Encumbrance Project */
4337 ) IS
4338
4339 d_api_name CONSTANT VARCHAR2(30) := 'default_account_ids';
4340 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
4341 d_position NUMBER;
4342 l_acct NUMBER;
4343 l_api_version NUMBER := 1;
4344 l_return_status VARCHAR2(100) := NULL;
4345 l_msg_count NUMBER;
4346 l_msg_data VARCHAR2(500);
4347
4348 BEGIN
4349 d_position := 0;
4350
4351 IF (PO_LOG.d_proc) THEN
4352 PO_LOG.proc_begin(d_module, 'p_dest_type_code', p_dest_type_code);
4353 PO_LOG.proc_begin(d_module, 'p_dest_org_id', p_dest_org_id);
4354 PO_LOG.proc_begin(d_module, 'p_dest_subinventory', p_dest_subinventory);
4355 PO_LOG.proc_begin(d_module, 'p_item_id', p_item_id);
4356 PO_LOG.proc_begin(d_module, 'p_po_encumbrance_flag', p_po_encumbrance_flag);
4357 PO_LOG.proc_begin(d_module, 'p_charge_account_id', p_charge_account_id);
4358 PO_LOG.proc_begin(d_module, 'x_accrual_account_id', x_accrual_account_id);
4359 PO_LOG.proc_begin(d_module, 'x_budget_account_id', x_budget_account_id);
4360 PO_LOG.proc_begin(d_module, 'x_variance_account_id', x_variance_account_id);
4361 END IF;
4362
4363 IF (p_dest_type_code = 'EXPENSE') THEN
4364
4365 d_position := 10;
4366
4367 IF (x_accrual_account_id IS NULL) THEN
4368
4369 d_position := 20;
4370
4371 IF (g_sys_accrual_account_id IS NULL) THEN
4372
4373 d_position := 30;
4374
4375 SELECT accrued_code_combination_id
4376 INTO g_sys_accrual_account_id
4377 FROM po_system_parameters;
4378 END IF;
4379
4380 x_accrual_account_id := g_sys_accrual_account_id;
4381 END IF;
4382
4383 IF x_budget_account_id IS NULL THEN
4384 x_budget_account_id := p_charge_account_id;
4385 END IF;
4386
4387 IF x_variance_account_id IS NULL THEN
4388 x_variance_account_id := p_charge_account_id;
4389 END IF;
4390 ELSIF (p_dest_type_code IN ('SHOP FLOOR','INVENTORY')) THEN
4391
4392 d_position := 40;
4393
4394 -- default accrual_account_id and variance_account_id
4395 IF (x_accrual_account_id IS NULL OR
4396 x_variance_account_id IS NULL) AND
4397 p_dest_org_id IS NOT NULL THEN
4398
4399 d_position := 50;
4400
4401 IF (NOT g_mtl_accrual_account_id_tbl.EXISTS(p_dest_org_id)) THEN
4402
4403 d_position := 60;
4404 SELECT ap_accrual_account, invoice_price_var_account
4405 INTO g_mtl_accrual_account_id_tbl(p_dest_org_id),
4406 g_mtl_variance_account_id_tbl(p_dest_org_id)
4407 FROM mtl_parameters
4408 WHERE organization_id = p_dest_org_id;
4409 END IF;
4410
4411 IF (x_accrual_account_id IS NULL) THEN
4412 x_accrual_account_id := g_mtl_accrual_account_id_tbl(p_dest_org_id);
4413 END IF;
4414
4415 IF (x_variance_account_id IS NULL) THEN
4416 x_variance_account_id := g_mtl_variance_account_id_tbl(p_dest_org_id);
4417 END IF;
4418 END IF;
4419
4420 -- default budget_account_id only when encumbrance is enabled
4421 IF (p_po_encumbrance_flag = 'Y' AND x_budget_account_id IS NULL) THEN
4422 /* IF (p_dest_type_code = 'SHOP FLOOR') THEN
4423 x_budget_account_id := P_charge_account_id; Code commented for Encumbrance Project */
4424
4425 /* - For Encumbrance Project - To enable Encumbrance for Destination type Shop Floor and WIP entity type EAM */
4426 /* Start */
4427
4428 IF (p_dest_type_code = 'SHOP FLOOR') and ( x_entity_type = 6) then
4429
4430 -- Calling Costing API
4431
4432 CST_EAMCOST_PUB.get_account
4433 (p_wip_entity_id => x_wip_entity_id,
4434 p_item_id => p_item_id,
4435 p_account_name => 'ENCUMBRANCE',
4436 p_api_version => l_api_version,
4437 x_acct => l_acct,
4438 x_return_status => l_return_status,
4439 x_msg_count => l_msg_count,
4440 x_msg_data => l_msg_data );
4441
4442
4443
4444 IF (l_return_status = 'S' AND l_acct IS NOT NULL ) then
4445 x_budget_account_id := l_acct;
4446 END IF;
4447
4448 ELSIF (p_dest_type_code = 'SHOP FLOOR') and ( x_entity_type <> 6) THEN
4449
4450 x_budget_account_id := P_charge_account_id;
4451
4452 /* End */
4453
4454 ELSE -- p_destination_type_code = 'INVENTORY'
4455
4456 d_position := 70;
4457
4458 -- first try to get it from mtl_item_sub_inventories, then mtl_secondary_
4459 -- inventories, then mtl_system_items and finally mtl_parameters
4460 -- Bug6811980 (including the exception block)
4461 BEGIN
4462 SELECT NVL(misi.encumbrance_account, msci.encumbrance_account)
4463 INTO x_budget_account_id
4464 FROM mtl_item_sub_inventories misi,
4465 mtl_secondary_inventories msci
4466 WHERE misi.organization_id = p_dest_org_id
4467 AND misi.inventory_item_id = p_item_id
4468 AND misi.secondary_inventory = p_dest_subinventory
4469 AND msci.organization_id = p_dest_org_id
4470 AND msci.secondary_inventory_name = p_dest_subinventory
4471 AND p_dest_subinventory is not NULL;
4472 EXCEPTION
4473 WHEN NO_DATA_FOUND THEN
4474 null;
4475 END;
4476
4477 -- Bug6811980 (including the exception block)
4478 BEGIN
4479 IF (x_budget_account_id IS NULL) THEN
4480 d_position := 80;
4481
4482 SELECT NVL(msi.encumbrance_account, mp.encumbrance_account)
4483 INTO x_budget_account_id
4484 FROM mtl_system_items msi,
4485 mtl_parameters mp
4486 WHERE p_item_id = msi.inventory_item_id
4487 AND p_dest_org_id = msi.organization_id
4488 AND mp.organization_id = p_dest_org_id;
4489 END IF;
4490 EXCEPTION
4491 WHEN NO_DATA_FOUND THEN
4492 null;
4493 END;
4494
4495 IF (x_budget_account_id IS NULL) THEN
4496 x_budget_account_id := p_charge_account_id;
4497 END IF;
4498 END IF;
4499 END IF;
4500 END IF;
4501
4502 IF (PO_LOG.d_proc) THEN
4503 PO_LOG.proc_end (d_module);
4504 END IF;
4505
4506 EXCEPTION
4507 WHEN OTHERS THEN
4508 PO_MESSAGE_S.add_exc_msg
4509 (
4510 p_pkg_name => d_pkg_name,
4511 p_procedure_name => d_api_name || '.' || d_position
4512 );
4513 RAISE;
4514 END default_account_ids;
4515
4516 -----------------------------------------------------------------------
4517 --Start of Comments
4518 --Name: populate_error_flag
4519 --Function:
4520 -- corresponding value in error_flag_tbl will be set with value FND_API.G_FALSE.
4521 --Parameters:
4522 --IN:
4523 --x_results
4524 -- The validation results that contains the errored line information.
4525 --IN OUT:
4526 --x_dists
4527 -- The record contains the values to be validated.
4528 -- If there is error(s) on any attribute of the price differential row,
4529 -- corresponding value in error_flag_tbl will be set with value
4530 -- FND_API.g_TRUE.
4531 --OUT:
4532 --End of Comments
4533 ------------------------------------------------------------------------
4534 PROCEDURE populate_error_flag
4535 (
4536 x_results IN po_validation_results_type,
4537 x_dists IN OUT NOCOPY PO_PDOI_TYPES.distributions_rec_type
4538 ) IS
4539
4540 d_api_name CONSTANT VARCHAR2(30) := 'populate_error_flag';
4541 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
4542 d_position NUMBER;
4543
4544 l_index_tbl DBMS_SQL.number_table;
4545
4546 BEGIN
4547 d_position := 0;
4548
4549 IF (PO_LOG.d_proc) THEN
4550 PO_LOG.proc_begin(d_module);
4551 END IF;
4552
4553 FOR i IN 1 .. x_dists.intf_dist_id_tbl.COUNT LOOP
4554 l_index_tbl(x_dists.intf_dist_id_tbl(i)) := i;
4555 END LOOP;
4556
4557 d_position := 10;
4558
4559 FOR i IN 1 .. x_results.entity_id.COUNT LOOP
4560 IF x_results.result_type(i) = po_validations.c_result_type_failure THEN
4561 IF (PO_LOG.d_stmt) THEN
4562 PO_LOG.stmt(d_module, d_position, 'error on index',
4563 l_index_tbl(x_results.entity_id(i)));
4564 END IF;
4565
4566 x_dists.error_flag_tbl(l_index_tbl(x_results.entity_id(i))) := FND_API.g_TRUE;
4567 END IF;
4568 END LOOP;
4569
4570 IF (PO_LOG.d_proc) THEN
4571 PO_LOG.proc_end (d_module);
4572 END IF;
4573
4574 EXCEPTION
4575 WHEN OTHERS THEN
4576 PO_MESSAGE_S.add_exc_msg
4577 (
4578 p_pkg_name => d_pkg_name,
4579 p_procedure_name => d_api_name || '.' || d_position
4580 );
4581 RAISE;
4582 END populate_error_flag;
4583
4584 END PO_PDOI_DIST_PROCESS_PVT;