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