[Home] [Help]
PACKAGE BODY: APPS.CSI_T_UTILITIES_PVT
Source
1 package body csi_t_utilities_pvt as
2 /* $Header: csivtulb.pls 120.4 2006/03/16 03:23:46 srsarava noship $ */
3
4
5 PROCEDURE debug(
6 p_message IN varchar2)
7 IS
8 BEGIN
9 csi_t_gen_utility_pvt.add(p_message);
10 END debug;
11
12 PROCEDURE build_instance_id_list(
13 p_txn_line_detial_tbl in csi_t_datastructures_grp.txn_line_detail_tbl,
14 x_instance_id_list OUT NOCOPY varchar2,
15 x_return_status OUT NOCOPY varchar2)
16 IS
17 l_instance_id_list varchar2(1000);
18 l_hit_count number := 0;
19
20 BEGIN
21
22 l_instance_id_list := ' (' ;
23
24 FOR l_ind in p_txn_line_detial_tbl.FIRST .. p_txn_line_detial_tbl.LAST
25 LOOP
26
27 IF p_txn_line_detial_tbl(l_ind).instance_exists_flag = 'Y' THEN
28
29 l_hit_count := l_hit_count + 1;
30
31 l_instance_id_list := l_instance_id_list||
32 to_char(p_txn_line_detial_tbl(l_ind).instance_id)||',';
33 END IF;
34
35 END LOOP;
36
37 l_instance_id_list := rtrim(l_instance_id_list, ',')||')';
38
39 IF l_hit_count > 0 THEN
40 x_instance_id_list := l_instance_id_list;
41 ELSE
42 x_instance_id_list := null;
43 END IF;
44
45 END build_instance_id_list;
46
47 PROCEDURE build_txn_line_id_list(
48 p_txn_line_detial_tbl in csi_t_datastructures_grp.txn_line_detail_tbl,
49 x_txn_line_id_list OUT NOCOPY varchar2,
50 x_return_status OUT NOCOPY varchar2)
51 IS
52
53 l_txn_line_id_list varchar2(1000) := null;
54
55 BEGIN
56
57 IF p_txn_line_detial_tbl.COUNT > 0 THEN
58
59 l_txn_line_id_list := ' (' ;
60
61 FOR l_ind in p_txn_line_detial_tbl.FIRST .. p_txn_line_detial_tbl.LAST
62 LOOP
63 l_txn_line_id_list := l_txn_line_id_list||
64 to_char(p_txn_line_detial_tbl(l_ind).transaction_line_id)||',';
65 END LOOP;
66
67 l_txn_line_id_list := rtrim(l_txn_line_id_list, ',')||')';
68
69 x_txn_line_id_list := l_txn_line_id_list;
70
71 END IF;
72
73 END build_txn_line_id_list;
74
75 PROCEDURE build_party_dtl_id_list(
76 p_txn_party_detial_tbl in csi_t_datastructures_grp.txn_party_detail_tbl,
77 x_party_dtl_id_list OUT NOCOPY varchar2,
78 x_return_status OUT NOCOPY varchar2)
79 IS
80 l_party_dtl_id_list varchar2(512);
81
82 BEGIN
83
84 l_party_dtl_id_list := ' (' ;
85
86 FOR l_ind in p_txn_party_detial_tbl.FIRST .. p_txn_party_detial_tbl.LAST
87 LOOP
88 l_party_dtl_id_list := l_party_dtl_id_list||
89 to_char(p_txn_party_detial_tbl(l_ind).txn_party_detail_id)||',';
90 END LOOP;
91
92 l_party_dtl_id_list := rtrim(l_party_dtl_id_list, ',')||')';
93
94 x_party_dtl_id_list := l_party_dtl_id_list;
95
96 END build_party_dtl_id_list;
97
98 PROCEDURE build_line_dtl_id_list(
99 p_txn_line_detial_tbl in csi_t_datastructures_grp.txn_line_detail_tbl,
100 x_line_dtl_id_list OUT NOCOPY varchar2,
101 x_return_status OUT NOCOPY varchar2)
102 IS
103 l_line_dtl_id_list varchar2(512);
104
105 BEGIN
106
107 l_line_dtl_id_list := ' (' ;
108
109 FOR l_ind in p_txn_line_detial_tbl.FIRST .. p_txn_line_detial_tbl.LAST
110 LOOP
111 l_line_dtl_id_list := l_line_dtl_id_list||
112 to_char(p_txn_line_detial_tbl(l_ind).txn_line_detail_id)||',';
113 END LOOP;
114
115 l_line_dtl_id_list := rtrim(l_line_dtl_id_list, ',')||')';
116
117 x_line_dtl_id_list := l_line_dtl_id_list;
118
119 END build_line_dtl_id_list;
120
121 /* This routine merges the transaction details tables */
122 PROCEDURE merge_tables(
123 px_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
124 px_pty_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
125 px_pty_acct_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
126 px_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
127 px_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
128 px_ext_attrib_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
129 px_txn_systems_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl,
130 --
131 p_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
132 p_pty_dtl_tbl IN csi_t_datastructures_grp.txn_party_detail_tbl,
133 p_pty_acct_tbl IN csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
134 p_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
135 p_org_assgn_tbl IN csi_t_datastructures_grp.txn_org_assgn_tbl,
136 p_ext_attrib_tbl IN csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
137 p_txn_systems_tbl IN csi_t_datastructures_grp.txn_systems_tbl)
138 IS
139
140 l_line_ind binary_integer;
141 l_pty_ind binary_integer;
142 l_pa_ind binary_integer;
143 l_ii_ind binary_integer;
144 l_oa_ind binary_integer;
145 l_ea_ind binary_integer;
146 l_sys_ind binary_integer;
147
148 BEGIN
149
150 l_line_ind := px_line_dtl_tbl.COUNT;
151
152 IF p_line_dtl_tbl.COUNT > 0 THEN
153 FOR l_ind in p_line_dtl_tbl.FIRST..p_line_dtl_tbl.LAST
154 LOOP
155
156 l_line_ind := l_line_ind + 1;
157 px_line_dtl_tbl(l_line_ind) := p_line_dtl_tbl(l_ind);
158
159 END LOOP;
160 END IF;
161
162 l_pty_ind := px_pty_dtl_tbl.COUNT;
163 IF p_pty_dtl_tbl.COUNT > 0 THEN
164
165 FOR l_ind in p_pty_dtl_tbl.FIRST..p_pty_dtl_tbl.LAST
166 LOOP
167
168 l_pty_ind := l_pty_ind + 1;
169 px_pty_dtl_tbl(l_pty_ind) := p_pty_dtl_tbl(l_ind);
170
171 END LOOP;
172
173 END IF;
174
175 l_pa_ind := px_pty_acct_tbl.COUNT;
176 IF p_pty_acct_tbl.COUNT > 0 THEN
177
178 FOR l_ind in p_pty_acct_tbl.FIRST .. p_pty_acct_tbl.LAST
179 LOOP
180
181 l_pa_ind := l_pa_ind + 1;
182 px_pty_acct_tbl(l_pa_ind) := p_pty_acct_tbl(l_ind);
183
184 END LOOP;
185 END IF;
186
187 l_ii_ind := px_ii_rltns_tbl.COUNT;
188 IF p_ii_rltns_tbl.COUNT > 0 THEN
189
190 FOR l_ind in p_ii_rltns_tbl.FIRST .. p_ii_rltns_tbl.LAST
191 LOOP
192
193 l_ii_ind := l_ii_ind + 1;
194 px_ii_rltns_tbl(l_ii_ind) := p_ii_rltns_tbl(l_ind);
195
196 END LOOP;
197 END IF;
198
199 l_oa_ind := px_org_assgn_tbl.COUNT;
200 IF p_org_assgn_tbl.COUNT > 0 THEN
201
202 FOR l_ind in p_org_assgn_tbl.FIRST..p_org_assgn_tbl.LAST
203 LOOP
204
205 l_oa_ind := l_oa_ind + 1;
206 px_org_assgn_tbl(l_oa_ind) := p_org_assgn_tbl(l_ind);
207
208 END LOOP;
209 END IF;
210
211 l_ea_ind := px_ext_attrib_tbl.COUNT;
212 IF p_ext_attrib_tbl.COUNT > 0 THEN
213
214 FOR l_ind in p_ext_attrib_tbl.FIRST .. p_ext_attrib_tbl.LAST
215 LOOP
216
217 l_ea_ind := l_ea_ind + 1;
218 px_ext_attrib_tbl(l_ea_ind) := p_ext_attrib_tbl(l_ind);
219
220 END LOOP;
221 END IF;
222
223 l_sys_ind := px_txn_systems_tbl.count;
224 IF p_txn_systems_tbl.COUNT > 0 THEN
225 FOR l_ind IN p_txn_systems_tbl.FIRST .. p_txn_systems_tbl.LAST
226 LOOP
227
228 l_sys_ind := l_sys_ind + 1;
229 px_txn_systems_tbl(l_sys_ind) := p_txn_systems_tbl(l_ind);
230
231 END LOOP;
232 END IF;
233
234 END merge_tables;
235
236
237
238 /* This routine converts the ids into indexes, This is to prepare the
239 pl/sql tables to be passed to the create API. Used in the Copy Txn Details
240 API
241 */
242 PROCEDURE convert_ids_to_index(
243 px_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
244 px_pty_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
245 px_pty_acct_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
246 px_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
247 px_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
248 px_ext_attrib_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
249 px_txn_systems_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl)
250 IS
251
252 l_debug_level NUMBER;
253 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
254 l_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
255 ll_pa_ind binary_integer;
256
257 l_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
258
259 l_subject_index binary_integer;
260 l_object_index binary_integer;
261 l_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
262
263 BEGIN
264
265 --debug messages
266 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
267
268 csi_t_gen_utility_pvt.dump_api_info(
269 p_api_name => 'convert_ids_to_index',
270 p_pkg_name => 'csi_t_utilities_pvt');
271
272
273 l_pty_dtl_tbl := px_pty_dtl_tbl;
274 l_line_dtl_tbl := px_line_dtl_tbl;
275 -- preserving the assc txn line detail id while copying . added this for loop for bug 3600950
276 FOR l_tld_ind in px_line_dtl_tbl.FIRST..px_line_dtl_tbl.LAST
277 LOOP
278 IF px_line_dtl_tbl(l_tld_ind).source_transaction_flag = 'N' THEN
279 /* translate the assc_txn_line_detail_id to an index */
280 IF nvl(px_line_dtl_tbl(l_tld_ind).assc_txn_line_detail_id, fnd_api.g_miss_num)
281 <> fnd_api.g_miss_num
282 THEN
283 FOR l_s_ind in l_line_dtl_tbl.FIRST .. l_line_dtl_tbl.LAST
284 LOOP
285
286 IF px_line_dtl_tbl(l_s_ind).txn_line_detail_id = px_line_dtl_tbl(l_tld_ind).assc_txn_line_detail_id
287 THEN
288 px_line_dtl_tbl(l_tld_ind).assc_txn_line_detail_id := l_s_ind;
289 exit;
290 END IF;
291 END LOOP;
292 END IF;
293 END IF;
294 END LOOP;
295
296 -- ii_relationsips
297
298 IF px_ii_rltns_tbl.COUNT > 0 THEN
299 FOR l_ii_ind in px_ii_rltns_tbl.FIRST..px_ii_rltns_tbl.LAST
300 LOOP
301
302 px_ii_rltns_tbl(l_ii_ind).transaction_line_id := fnd_api.g_miss_num;
303 px_ii_rltns_tbl(l_ii_ind).txn_relationship_id := fnd_api.g_miss_num;
304
305 /* translate the subject_id to subject index */
306 IF px_ii_rltns_tbl(l_ii_ind).subject_id IS NOT NULL
307 THEN
308 IF px_line_dtl_tbl.COUNT > 0
309 THEN
310 FOR l_s_ind in px_line_dtl_tbl.FIRST .. px_line_dtl_tbl.LAST
311 LOOP
312
313 IF px_line_dtl_tbl(l_s_ind).txn_line_detail_id = px_ii_rltns_tbl(l_ii_ind).subject_id
314 THEN
315 l_subject_index := l_s_ind;
316 exit;
317 END IF;
318
319 END LOOP;
320 END IF;
321 END IF;
322
323 /* translate the object_id to object index */
324 IF px_ii_rltns_tbl(l_ii_ind).object_id IS NOT NULL THEN
325 IF px_line_dtl_tbl.COUNT > 0 THEN
326 FOR l_o_ind in px_line_dtl_tbl.FIRST .. px_line_dtl_tbl.LAST
327 LOOP
328
329 IF px_line_dtl_tbl(l_o_ind).txn_line_detail_id = px_ii_rltns_tbl(l_ii_ind).object_id
330 THEN
331 l_object_index := l_o_ind;
332 exit;
333 END IF;
334
335 END LOOP;
336 END IF;
337 END IF;
338
339 ---Added (Start) for m-to-m enhancements
340 ---As we are also supporting rltns across source SO Lines
341 ---in that case the subject/object id will not find any match
342 ---in px_line_dtl_tbl. In such case we will keep the subject/object
343 ---as the same and flag it is as NOT an index.
344 IF l_subject_index IS NOT NULL
345 THEN
346 px_ii_rltns_tbl(l_ii_ind).subject_id := l_subject_index;
347 px_ii_rltns_tbl(l_ii_ind).subject_index_flag := 'Y' ;
348 ELSE
349 px_ii_rltns_tbl(l_ii_ind).subject_index_flag := 'N' ;
350 END IF ;
351
352 IF l_object_index IS NOT NULL
353 THEN
354 px_ii_rltns_tbl(l_ii_ind).object_id := l_object_index;
355 px_ii_rltns_tbl(l_ii_ind).object_index_flag := 'Y' ;
356 ELSE
357 px_ii_rltns_tbl(l_ii_ind).object_index_flag := 'N' ;
358 END IF ;
359 ---Added (End) for m-to-m enhancements
360
361 END LOOP;
362 END IF;
363
364 ll_pa_ind := 0;
365 IF px_line_dtl_tbl.COUNT > 0 THEN
366 FOR l_ln_ind IN px_line_dtl_tbl.first .. px_line_dtl_tbl.LAST
367 LOOP
368
369 IF px_pty_dtl_tbl.COUNT > 0 THEN
370 FOR l_pt_ind in px_pty_dtl_tbl.FIRST..px_pty_dtl_tbl.LAST
371 LOOP
372
373 IF px_pty_dtl_tbl(l_pt_ind).txn_line_detail_id =
374 px_line_dtl_tbl(l_ln_ind).txn_line_detail_id
375 THEN
376 IF px_pty_acct_tbl.COUNT > 0 THEN
377 FOR l_pa_ind in px_pty_acct_tbl.FIRST..px_pty_acct_tbl.LAST
378 LOOP
379
380 IF px_pty_acct_tbl(l_pa_ind).txn_party_detail_id =
381 px_pty_dtl_tbl(l_pt_ind).txn_party_detail_id
382 THEN
383 ll_pa_ind := ll_pa_ind + 1;
384 px_pty_acct_tbl(l_pa_ind).txn_party_details_index := l_pt_ind;
385 px_pty_acct_tbl(l_pa_ind).txn_party_detail_id := fnd_api.g_miss_num;
386 px_pty_acct_tbl(l_pa_ind).txn_account_detail_id := fnd_api.g_miss_num;
387 l_pty_acct_tbl(ll_pa_ind) := px_pty_acct_tbl(l_pa_ind);
388 END IF;
389 END LOOP;
390 END IF;
391
392 px_pty_dtl_tbl(l_pt_ind).txn_line_details_index := l_ln_ind;
393 px_pty_dtl_tbl(l_pt_ind).txn_line_detail_id := fnd_api.g_miss_num;
394 END IF;
395 --px_pty_dtl_tbl(l_pt_ind).txn_party_detail_id := fnd_api.g_miss_num;
396 END LOOP;
397 END IF;
398
399 IF px_org_assgn_tbl.COUNT > 0 THEN
400 FOR l_oa_ind in px_org_assgn_tbl.FIRST..px_org_assgn_tbl.LAST
401 LOOP
402 IF px_org_assgn_tbl(l_oa_ind).txn_line_detail_id =
403 px_line_dtl_tbl(l_ln_ind).txn_line_detail_id
404 THEN
405 px_org_assgn_tbl(l_oa_ind).txn_line_details_index := l_ln_ind;
406 px_org_assgn_tbl(l_oa_ind).txn_line_detail_id := fnd_api.g_miss_num;
407 END IF;
408 px_org_assgn_tbl(l_oa_ind).txn_operating_unit_id := fnd_api.g_miss_num;
409 END LOOP;
410 END IF;
411
412 IF px_ext_attrib_tbl.COUNT > 0 THEN
413 FOR l_ea_ind in px_ext_attrib_tbl.FIRST..px_ext_attrib_tbl.LAST
414 LOOP
415 IF px_ext_attrib_tbl(l_ea_ind).txn_line_detail_id =
416 px_line_dtl_tbl(l_ln_ind).txn_line_detail_id
417 THEN
418 px_ext_attrib_tbl(l_ea_ind).txn_line_details_index := l_ln_ind;
419 px_ext_attrib_tbl(l_ea_ind).txn_line_detail_id := fnd_api.g_miss_num;
420 END IF;
421 px_ext_attrib_tbl(l_ea_ind).txn_attrib_detail_id := fnd_api.g_miss_num;
422 END LOOP;
423 END IF;
424
425 px_line_dtl_tbl(l_ln_ind).txn_line_detail_id := fnd_api.g_miss_num;
426 px_line_dtl_tbl(l_ln_ind).transaction_line_id := fnd_api.g_miss_num;
427
428 IF px_line_dtl_tbl(l_ln_ind).transaction_system_id <> fnd_api.g_miss_num THEN
429
430 csi_t_vldn_routines_pvt.get_txn_systems_index(
431 p_txn_system_id => px_line_dtl_tbl(l_ln_ind).transaction_system_id,
432 p_txn_systems_tbl => px_txn_systems_tbl,
433 x_txn_systems_index => px_line_dtl_tbl(l_ln_ind).txn_systems_index,
434 x_return_status => l_return_status);
435
436 px_line_dtl_tbl(l_ln_ind).transaction_system_id := fnd_api.g_miss_num;
437
438 END IF;
439
440 END LOOP;
441 END IF;
442
443 IF px_txn_systems_tbl.COUNT > 0 THEN
444 FOR l_sys_ind in px_txn_systems_tbl.FIRST..px_txn_systems_tbl.LAST
445 LOOP
446 px_txn_systems_tbl(l_sys_ind).transaction_line_id := fnd_api.g_miss_num;
447 px_txn_systems_tbl(l_sys_ind).transaction_system_id := fnd_api.g_miss_num;
448 END LOOP;
449 END IF;
450
451 px_pty_acct_tbl := l_pty_acct_tbl;
452
453 IF px_pty_dtl_tbl.COUNT > 0 THEN
454 FOR l_ind IN px_pty_dtl_tbl.FIRST .. px_pty_dtl_tbl.LAST
455 LOOP
456 px_pty_dtl_tbl(l_ind).txn_party_detail_id := fnd_api.g_miss_num;
457
458 IF nvl(px_pty_dtl_tbl(l_ind).contact_party_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
459 THEN
460 FOR ll_ind IN l_pty_dtl_tbl.FIRST .. l_pty_dtl_tbl.LAST
461 LOOP
462 IF l_pty_dtl_tbl(ll_ind).txn_party_detail_id = px_pty_dtl_tbl(l_ind).contact_party_id
463 THEN
464 px_pty_dtl_tbl(l_ind).contact_party_id := ll_ind;
465 exit;
466 END IF;
467 END LOOP;
468 END IF;
469 END LOOP;
470 END IF;
471
472 END convert_ids_to_index;
473
474
475 /* This routine builds a txn system ids list given txn line detail pl/sql
476 table. The output is a comma seperated list of txn system id list within
477 braces which can be used in a IN operator in a where clause
478 */
479
480 PROCEDURE build_txn_system_id_list(
481 p_txn_line_detial_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
482 x_txn_system_id_list OUT NOCOPY varchar2,
483 x_return_status OUT NOCOPY varchar2)
484 IS
485 l_txn_system_id_list varchar2(1000);
486 l_hit_count number := 0;
487
488 BEGIN
489
490 l_txn_system_id_list := ' (' ;
491
492 FOR l_ind in p_txn_line_detial_tbl.FIRST .. p_txn_line_detial_tbl.LAST
493 LOOP
494
495 IF p_txn_line_detial_tbl(l_ind).transaction_system_id <> fnd_api.g_miss_num THEN
496
497 l_hit_count := l_hit_count + 1;
498
499 l_txn_system_id_list := l_txn_system_id_list||
500 to_char(p_txn_line_detial_tbl(l_ind).transaction_system_id)||',';
501
502 END IF;
503
504 END LOOP;
505
506 l_txn_system_id_list := rtrim(l_txn_system_id_list, ',')||')';
507
508 IF l_hit_count > 0 THEN
509 x_txn_system_id_list := l_txn_system_id_list;
510 ELSE
511 x_txn_system_id_list := null;
512 END IF;
513
514 END build_txn_system_id_list;
515
516 PROCEDURE source_for_standalone(
517 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
518 x_txn_source_rec OUT NOCOPY csi_t_ui_pvt.txn_source_rec,
519 x_txn_line_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
520 x_txn_party_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
521 x_txn_pty_acct_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
522 x_txn_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
523 x_return_status OUT NOCOPY varchar2)
524 IS
525
526 l_txn_source_rec csi_t_ui_pvt.txn_source_rec;
527 l_txn_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
528 l_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
529 l_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
530 l_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
531
532 l_mo_org_id oe_order_lines_all.org_id%TYPE;
533 l_serial_control_code mtl_system_items_b.serial_number_control_code%TYPE;
534 l_lot_control_code mtl_system_items_b.lot_control_code%TYPE;
535 l_nl_trackable_flag mtl_system_items_b.comms_nl_trackable_flag%TYPE;
536 l_loop_count number;
537 l_td_quantity number;
538 l_uom varchar2(30);
539 l_item_type_code oe_order_lines_all.item_type_code%TYPE;
540 l_location_type_code varchar2(30) := fnd_api.g_miss_char;
541 l_location_id number := fnd_api.g_miss_num;
542 l_operating_unit_id number;
543 l_oa_relationship_code varchar2(30);
544 l_sub_type_id number := fnd_api.g_miss_num;
545 l_shippable_flag varchar2(1) := 'N';
546 l_sold_from_org_id number;
547
548 -- For partner prdering
549 l_partner_rec oe_install_base_util.partner_order_rec;
550 l_ib_owner VARCHAR2(60);
551 l_end_customer_id NUMBER;
552 l_partner_ib_owner VARCHAR2(60);
553 l_line_id NUMBER;
554
555
556 BEGIN
557
558 x_return_status := fnd_api.g_ret_sts_success;
559
560 IF px_txn_line_rec.source_transaction_table = 'OE_ORDER_LINES_ALL' THEN
561
562 BEGIN
563
564 SELECT ol.inventory_item_id,
565 ol.item_revision,
566 ol.ordered_quantity,
567 ol.order_quantity_uom,
568 ol.shipped_quantity,
569 ol.fulfilled_quantity,
570 ol.org_id,
571 ol.item_type_code,
572 nvl(ol.sold_to_org_id, oh.sold_to_org_id),
573 nvl(ol.invoice_to_org_id, oh.invoice_to_org_id),
574 nvl(ol.ship_to_org_id, oh.ship_to_org_id),
575 nvl(ol.sold_from_org_id, oh.sold_from_org_id),
576 ol.line_id,
577 ol.header_id
578 INTO l_txn_source_rec.inventory_item_id,
579 l_txn_source_rec.item_revision,
580 l_txn_source_rec.source_quantity,
581 l_txn_source_rec.source_uom,
582 l_txn_source_rec.shipped_quantity,
583 l_txn_source_rec.fulfilled_quantity,
584 l_mo_org_id,
585 l_item_type_code,
586 l_txn_source_rec.party_account_id,
587 l_txn_source_rec.bill_to_address_id,
588 l_txn_source_rec.ship_to_address_id,
589 l_sold_from_org_id,
590 l_line_id,
591 px_txn_line_rec.source_txn_header_id
592 FROM oe_order_lines_all ol,
593 oe_order_headers_all oh
594 WHERE line_id = px_txn_line_rec.source_transaction_id
595 AND oh.header_id = ol.header_id;
596
597 -- for partner ordering
598 OE_INSTALL_BASE_UTIL.get_partner_ord_rec(p_order_line_id => l_line_id,
599 x_partner_order_rec => l_partner_rec);
600
601 IF l_partner_rec.IB_OWNER = 'END_CUSTOMER'
602 THEN
603 IF l_partner_rec.END_CUSTOMER_ID is null Then
604 fnd_message.set_name('CSI','CSI_PARTNER_VAL_MISSING');
605 fnd_msg_pub.add;
606 raise fnd_api.g_exc_error;
607 ELSE
608 l_ib_owner := l_partner_rec.ib_owner;
609 l_txn_source_rec.party_account_id := l_partner_rec.end_customer_id;
610 END IF;
611 ELSIF l_partner_rec.IB_OWNER = 'INSTALL_BASE'
612 THEN
613 l_ib_owner := l_partner_rec.ib_owner;
614 l_txn_source_rec.party_account_id := fnd_api.g_miss_num;
615 ELSE
616 l_txn_source_rec.party_account_id := l_txn_source_rec.party_account_id;
617 END IF;
618
619
620 SELECT party_id
621 INTO l_txn_source_rec.party_id
622 FROM hz_cust_accounts
623 where cust_account_id = l_txn_source_rec.party_account_id;
624
625 l_txn_source_rec.organization_id := oe_sys_parameters.value(
626 param_name => 'MASTER_ORGANIZATION_ID',
627 p_org_id => l_mo_org_id);
628
629 l_location_type_code := 'HZ_PARTY_SITES';
630 l_location_id := l_txn_source_rec.ship_to_address_id;
631
632 EXCEPTION
633 WHEN no_data_found THEN
634
635 FND_MESSAGE.set_name('CSI','CSI_TXN_SOURCE_ID_INVALID');
636 FND_MESSAGE.set_token('SRC_NAME',px_txn_line_rec.source_transaction_table);
637 FND_MESSAGE.set_token('SRC_LINE_ID',px_txn_line_rec.source_transaction_id);
638 FND_MSG_PUB.add;
639 RAISE fnd_api.g_exc_error;
640
641 END;
642
643 ELSIF px_txn_line_rec.source_transaction_table = 'WSH_DELIVERY_DETAILS' THEN
644
645 SELECT inventory_item_id,
646 revision,
647 shipped_quantity,
648 requested_quantity_uom,
649 organization_id,
650 customer_id
651 INTO l_txn_source_rec.inventory_item_id,
652 l_txn_source_rec.item_revision,
653 l_txn_source_rec.source_quantity,
654 l_txn_source_rec.source_uom,
655 l_txn_source_rec.organization_id,
656 l_txn_source_rec.party_id
657 -- FROM wsh_delivery_details
658 FROM wsh_delivery_details_ob_grp_v
659 WHERE delivery_detail_id = px_txn_line_rec.source_transaction_id;
660
661 ELSIF px_txn_line_rec.source_transaction_table = 'PO_LINES_ALL' THEN
662
663 SELECT item_id,
664 item_revision,
665 quantity,
666 unit_meas_lookup_code
667 INTO l_txn_source_rec.inventory_item_id,
668 l_txn_source_rec.item_revision,
669 l_txn_source_rec.source_quantity,
670 l_uom
671 FROM po_lines_all
672 WHERE po_line_id = px_txn_line_rec.source_transaction_id;
673
674 ELSIF px_txn_line_rec.source_transaction_table = 'RCV_SHIPMENT_LINES' THEN
675 null;
676 ELSIF px_txn_line_rec.source_transaction_table = 'RCV_TRANSACTIONS' THEN
677 null;
678 ELSIF px_txn_line_rec.source_transaction_table = 'MATERIAL_TRANSACTION' THEN
679 null;
680 ELSIF px_txn_line_rec.source_transaction_table = 'MATERIAL_TRANSACTION' THEN
681 null;
682 ELSIF px_txn_line_rec.source_transaction_table = 'ASO_QUOTE_LINES_ALL' THEN
683 null;
684 ELSE
685
686 FND_MESSAGE.set_name('CSI','CSI_TXN_SRC_TABLE_INVALID');
687 FND_MESSAGE.set_token('SRC_TABLE',px_txn_line_rec.source_transaction_table);
688 FND_MSG_PUB.add;
689 RAISE FND_API.g_exc_error;
690 END IF;
691
692 BEGIN
693
694 SELECT concatenated_segments ,
695 serial_number_control_code,
696 lot_control_code,
697 nvl(comms_nl_trackable_flag,'N'),
698 primary_uom_code,
699 nvl(shippable_item_flag,'N')
700 INTO l_txn_source_rec.inventory_item_name,
701 l_serial_control_code,
702 l_lot_control_code,
703 l_nl_trackable_flag,
704 l_txn_source_rec.primary_uom,
705 l_shippable_flag
706 FROM mtl_system_items_kfv
707 WHERE inventory_item_id = l_txn_source_rec.inventory_item_id
708 AND organization_id = l_txn_source_rec.organization_id;
709
710 EXCEPTION
711 WHEN no_data_found THEN
712 RAISE fnd_api.g_exc_error;
713 END;
714
715 /* logic to derive sub_type_id */
716
717 BEGIN
718
719 SELECT sub_type_id
720 INTO l_sub_type_id
721 FROM csi_txn_sub_types
722 WHERE transaction_type_id = px_txn_line_rec.source_transaction_type_id
723 AND default_flag = 'Y';
724
725 EXCEPTION
726 WHEN no_data_found THEN
727
728 FND_MESSAGE.set_name('CSI','CSI_TXN_TYPE_ID_INVALID');
729 FND_MESSAGE.set_token('TXN_TYPE_ID',px_txn_line_rec.source_transaction_type_id);
730 FND_MSG_PUB.add;
731 RAISE fnd_api.g_exc_error;
732
733 WHEN too_many_rows THEN
734
735 FND_MESSAGE.set_name('CSI','CSI_TXN_TYPE_ID_INVALID');
736 FND_MESSAGE.set_token('TXN_TYPE_ID',px_txn_line_rec.source_transaction_type_id);
737 FND_MSG_PUB.add;
738 RAISE fnd_api.g_exc_error;
739
740 END;
741
742 -- serial control chk (1 - NO Control ELSE serialized)
743 IF l_serial_control_code = 1 then
744 l_loop_count := 1;
745 l_td_quantity := l_txn_source_rec.source_quantity;
746 l_txn_source_rec.serial_control_flag := 'N';
747 ELSE
748 -- Commented out for UI
749 -- l_loop_count := l_txn_source_rec.source_quantity;
750 l_loop_count := 1;
751 l_td_quantity := l_txn_source_rec.source_quantity;
752 l_txn_source_rec.serial_control_flag := 'Y';
753 END IF;
754
755 IF nvl(l_lot_control_code, -9999) = 2 THEN
756 l_txn_source_rec.lot_control_flag := 'Y';
757 ELSE
758 l_txn_source_rec.lot_control_flag := 'N';
759 END IF;
760
761 l_txn_source_rec.nl_trackable_flag := l_nl_trackable_flag;
762
763 -- Commented out for UI
764 /* overriding rhe serial control here */
765 /* IF l_item_type_code in ('MODEL', 'KIT') THEN
766 l_loop_count := l_txn_source_rec.source_quantity;
767 l_td_quantity := 1;
768 END IF;
769 */
770
771 --populate txn_line_detail table
772 FOR l_ind in 1..l_loop_count
773 LOOP
774
775 l_txn_line_dtl_tbl(l_ind).sub_type_id := l_sub_type_id;
776 l_txn_line_dtl_tbl(l_ind).instance_exists_flag := 'N';
777 l_txn_line_dtl_tbl(l_ind).source_transaction_flag := 'Y';
778 l_txn_line_dtl_tbl(l_ind).inventory_item_id := l_txn_source_rec.inventory_item_id;
779 l_txn_line_dtl_tbl(l_ind).inventory_revision := l_txn_source_rec.item_revision;
780 l_txn_line_dtl_tbl(l_ind).inv_organization_id := l_txn_source_rec.organization_id;
781 l_txn_line_dtl_tbl(l_ind).quantity := l_td_quantity;
782 l_txn_line_dtl_tbl(l_ind).unit_of_measure := l_txn_source_rec.source_uom;
783
784 IF l_txn_source_rec.serial_control_flag = 'Y' THEN
785 l_txn_line_dtl_tbl(l_ind).mfg_serial_number_flag := 'Y';
786 ELSE
787 l_txn_line_dtl_tbl(l_ind).mfg_serial_number_flag := 'N';
788 END IF;
789
790 --l_txn_line_dtl_tbl(l_ind).location_type_code := l_location_type_code;
791 --l_txn_line_dtl_tbl(l_ind).location_id := l_location_id;
792 --l_txn_line_dtl_tbl(l_ind).installation_date := sysdate;
793 l_txn_line_dtl_tbl(l_ind).active_start_date := sysdate;
794 l_txn_line_dtl_tbl(l_ind).processing_status := 'SUBMIT';
795 l_txn_line_dtl_tbl(l_ind).object_version_number := 1.0;
796
797 -- party details
798 l_pty_dtl_tbl(l_ind).party_source_table := 'HZ_PARTIES';
799 l_pty_dtl_tbl(l_ind).party_source_id := l_txn_source_rec.party_id;
800 l_pty_dtl_tbl(l_ind).relationship_type_code := 'OWNER';
801 l_pty_dtl_tbl(l_ind).active_start_date := sysdate;
802 l_pty_dtl_tbl(l_ind).preserve_detail_flag := 'Y';
803 l_pty_dtl_tbl(l_ind).txn_line_details_index := l_ind;
804 l_pty_dtl_tbl(l_ind).contact_flag := 'N';
805
806 -- party account details
807 l_pty_acct_tbl(l_ind).account_id := l_txn_source_rec.party_account_id;
808 l_pty_acct_tbl(l_ind).bill_to_address_id := l_txn_source_rec.bill_to_address_id;
809 l_pty_acct_tbl(l_ind).ship_to_address_id := l_txn_source_rec.ship_to_address_id;
810 l_pty_acct_tbl(l_ind).relationship_type_code := 'OWNER';
811 l_pty_acct_tbl(l_ind).active_start_date := sysdate;
812 l_pty_acct_tbl(l_ind).preserve_detail_flag := 'Y';
813 l_pty_acct_tbl(l_ind).txn_party_details_index := l_ind;
814
815 l_org_assgn_tbl(l_ind).operating_unit_id := l_sold_from_org_id;
816 l_org_assgn_tbl(l_ind).relationship_type_code := 'SOLD_FROM';
817 l_org_assgn_tbl(l_ind).active_start_date := sysdate;
818 l_org_assgn_tbl(l_ind).preserve_detail_flag := 'Y';
819 l_org_assgn_tbl(l_ind).txn_line_details_index := l_ind;
820
821 END LOOP;
822
823 x_txn_source_rec := l_txn_source_rec;
824 x_txn_line_detail_tbl := l_txn_line_dtl_tbl;
825 x_txn_party_detail_tbl := l_pty_dtl_tbl;
826 x_txn_pty_acct_detail_tbl := l_pty_acct_tbl;
827 x_txn_org_assgn_tbl := l_org_assgn_tbl;
828
829 EXCEPTION
830 WHEN fnd_api.g_exc_error THEN
831 x_return_status := fnd_api.g_ret_sts_error;
832 END source_for_standalone;
833
834 PROCEDURE source_for_params(
835 p_txn_source_param_rec IN csi_t_ui_pvt.txn_source_param_rec,
836 x_txn_source_rec OUT NOCOPY csi_t_ui_pvt.txn_source_rec,
837 x_txn_line_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
838 x_txn_party_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
839 x_txn_pty_acct_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
840 x_txn_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
841 x_return_status OUT NOCOPY varchar2)
842 IS
843
844 l_txn_source_rec csi_t_ui_pvt.txn_source_rec;
845 l_txn_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
846
847 l_mo_org_id oe_order_lines_all.org_id%TYPE;
848 l_serial_control_code mtl_system_items_b.serial_number_control_code%TYPE;
849 l_lot_control_code mtl_system_items_b.lot_control_code%TYPE;
850 l_nl_trackable_flag mtl_system_items_b.comms_nl_trackable_flag%TYPE;
851 l_loop_count number;
852 l_td_quantity number;
853 l_uom varchar2(30);
854 l_item_type mtl_system_items.bom_item_type%TYPE;
855 l_location_type_code varchar2(30) := fnd_api.g_miss_char;
856 l_location_id number := fnd_api.g_miss_num;
857 l_operating_unit_id number;
858 l_oa_relationship_code varchar2(30);
859 l_sub_type_id number := fnd_api.g_miss_num;
860 l_shippable_flag varchar2(1) := 'N';
861
862 BEGIN
863
864 x_return_status := fnd_api.g_ret_sts_success;
865
866 l_txn_source_rec.organization_id := p_txn_source_param_rec.inv_orgn_id;
867 l_txn_source_rec.inventory_item_id := p_txn_source_param_rec.inventory_item_id;
868 l_txn_source_rec.item_revision := p_txn_source_param_rec.item_revision;
869 l_txn_source_rec.source_quantity := p_txn_source_param_rec.transacted_quantity;
870 l_txn_source_rec.source_uom := p_txn_source_param_rec.transacted_uom;
871 l_txn_source_rec.party_id := p_txn_source_param_rec.party_id;
872 l_txn_source_rec.party_account_id := p_txn_source_param_rec.account_id;
873 l_txn_source_rec.bill_to_address_id := p_txn_source_param_rec.invoice_to_org_id;
874 l_txn_source_rec.ship_to_address_id := p_txn_source_param_rec.ship_to_org_id;
875
876 BEGIN
877
878 SELECT concatenated_segments ,
879 serial_number_control_code,
880 lot_control_code,
881 nvl(comms_nl_trackable_flag,'N'),
882 primary_uom_code,
883 nvl(shippable_item_flag,'N')
884 INTO l_txn_source_rec.inventory_item_name,
885 l_serial_control_code,
886 l_lot_control_code,
887 l_nl_trackable_flag,
888 l_txn_source_rec.primary_uom,
889 l_shippable_flag
890 FROM mtl_system_items_kfv
891 WHERE inventory_item_id = l_txn_source_rec.inventory_item_id
892 AND organization_id = l_txn_source_rec.organization_id;
893
894 EXCEPTION
895 WHEN no_data_found THEN
896 RAISE fnd_api.g_exc_error;
897 END;
898
899 /* logic to derive sub_type_id */
900
901 BEGIN
902
903 SELECT sub_type_id
904 INTO l_sub_type_id
905 FROM csi_txn_sub_types
906 WHERE transaction_type_id = p_txn_source_param_rec.source_transaction_type_id
907 AND default_flag = 'Y';
908
909 EXCEPTION
910 WHEN no_data_found THEN
911
912 FND_MESSAGE.set_name('CSI','CSI_TXN_TYPE_ID_INVALID');
913 FND_MESSAGE.set_token('TXN_TYPE_ID',p_txn_source_param_rec.source_transaction_type_id);
914 FND_MSG_PUB.add;
915 RAISE fnd_api.g_exc_error;
916
917 WHEN too_many_rows THEN
918
919 FND_MESSAGE.set_name('CSI','CSI_TXN_TYPE_ID_INVALID');
920 FND_MESSAGE.set_token('TXN_TYPE_ID',p_txn_source_param_rec.source_transaction_type_id);
921 FND_MSG_PUB.add;
922 RAISE fnd_api.g_exc_error;
923
924 END;
925
926 -- serial control chk (1 - NO Control ELSE serialized)
927 IF l_serial_control_code = 1 then
928 l_loop_count := 1;
929 l_td_quantity := l_txn_source_rec.source_quantity;
930 l_txn_source_rec.serial_control_flag := 'N';
931 ELSE
932 -- Commented out for UI
933 --l_loop_count := l_txn_source_rec.source_quantity;
934 l_loop_count := 1;
935 l_td_quantity := l_txn_source_rec.source_quantity;
936 l_txn_source_rec.serial_control_flag := 'Y';
937 END IF;
938
939 IF nvl(l_lot_control_code, -9999) = 2 THEN
940 l_txn_source_rec.lot_control_flag := 'Y';
941 ELSE
942 l_txn_source_rec.lot_control_flag := 'N';
943 END IF;
944
945 l_txn_source_rec.nl_trackable_flag := l_nl_trackable_flag;
946
947 /* if MODEL then split the transaction detail into one each */
948
949 -- commented out for UI
950 /* IF l_item_type = 1 THEN
951 l_loop_count := l_txn_source_rec.source_quantity;
952 l_td_quantity := 1;
953 END IF;
954 */
955 --populate txn_line_detail table
956 FOR l_ind in 1..l_loop_count
957 LOOP
958
959 l_txn_line_dtl_tbl(l_ind).sub_type_id := l_sub_type_id;
960 l_txn_line_dtl_tbl(l_ind).instance_exists_flag := 'N';
961 l_txn_line_dtl_tbl(l_ind).source_transaction_flag := 'Y';
962 l_txn_line_dtl_tbl(l_ind).inventory_item_id := l_txn_source_rec.inventory_item_id;
963 l_txn_line_dtl_tbl(l_ind).inventory_revision := l_txn_source_rec.item_revision;
964 l_txn_line_dtl_tbl(l_ind).inv_organization_id := l_txn_source_rec.organization_id;
965 l_txn_line_dtl_tbl(l_ind).quantity := l_td_quantity;
966 l_txn_line_dtl_tbl(l_ind).unit_of_measure := l_txn_source_rec.source_uom;
967
968 IF l_txn_source_rec.serial_control_flag = 'Y' THEN
969 l_txn_line_dtl_tbl(l_ind).mfg_serial_number_flag := 'Y';
970 ELSE
971 l_txn_line_dtl_tbl(l_ind).mfg_serial_number_flag := 'N';
972 END IF;
973
974 l_txn_line_dtl_tbl(l_ind).installation_date := sysdate;
975 l_txn_line_dtl_tbl(l_ind).active_start_date := sysdate;
976 l_txn_line_dtl_tbl(l_ind).processing_status := 'SUBMIT';
977 l_txn_line_dtl_tbl(l_ind).object_version_number := 1.0;
978
979 END LOOP;
980
981 x_txn_line_detail_tbl := l_txn_line_dtl_tbl;
982 x_txn_source_rec := l_txn_source_rec;
983
984 EXCEPTION
985 WHEN fnd_api.g_exc_error THEN
986 x_return_status := fnd_api.g_ret_sts_error;
987 END source_for_params;
988
989 PROCEDURE get_source_dtls(
990 p_txn_source_param_rec IN csi_t_ui_pvt.txn_source_param_rec,
991 x_txn_source_rec OUT NOCOPY csi_t_ui_pvt.txn_source_rec,
992 x_txn_line_rec OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
993 x_txn_line_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
994 x_txn_party_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
995 x_txn_pty_acct_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
996 x_txn_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
997 x_return_status OUT NOCOPY varchar2)
998 IS
999
1000 l_txn_source_rec csi_t_ui_pvt.txn_source_rec;
1001 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
1002 l_txn_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
1003 l_txn_party_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
1004 l_txn_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
1005 l_txn_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
1006
1007 l_debug_level number;
1008 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1009
1010 BEGIN
1011
1012 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
1013
1014 x_return_status := l_return_status;
1015
1016 csi_t_gen_utility_pvt.dump_api_info(
1017 p_api_name => 'get_source_dtls',
1018 p_pkg_name => 'csi_t_utilities_pvt');
1019
1020 IF l_debug_level > 1 then
1021
1022 csi_t_gen_utility_pvt.dump_txn_source_param_rec(
1023 p_txn_source_param_rec => p_txn_source_param_rec);
1024
1025 END IF;
1026
1027 IF p_txn_source_param_rec.standalone_mode = 'Y' THEN
1028
1029 l_txn_line_rec.source_transaction_type_id :=
1030 p_txn_source_param_rec.source_transaction_type_id;
1031 l_txn_line_rec.source_transaction_table :=
1032 p_txn_source_param_rec.source_transaction_table;
1033 l_txn_line_rec.source_transaction_id :=
1034 p_txn_source_param_rec.source_transaction_id;
1035
1036 source_for_standalone(
1037 px_txn_line_rec => l_txn_line_rec,
1038 x_txn_source_rec => l_txn_source_rec,
1039 x_txn_line_detail_tbl => l_txn_line_dtl_tbl,
1040 x_txn_party_detail_tbl => l_txn_party_tbl,
1041 x_txn_pty_acct_detail_tbl => l_txn_pty_acct_tbl,
1042 x_txn_org_assgn_tbl => l_txn_org_assgn_tbl,
1043 x_return_status => l_return_status);
1044
1045 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1046 RAISE fnd_api.g_exc_error;
1047 END IF;
1048
1049 ELSE
1050
1051 source_for_params(
1052 p_txn_source_param_rec => p_txn_source_param_rec,
1053 x_txn_source_rec => l_txn_source_rec,
1054 x_txn_line_detail_tbl => l_txn_line_dtl_tbl,
1055 x_txn_party_detail_tbl => l_txn_party_tbl,
1056 x_txn_pty_acct_detail_tbl => l_txn_pty_acct_tbl,
1057 x_txn_org_assgn_tbl => l_txn_org_assgn_tbl,
1058 x_return_status => l_return_status);
1059
1060 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1061 RAISE fnd_api.g_exc_error;
1062 END IF;
1063
1064 END IF;
1065
1066 x_txn_source_rec := l_txn_source_rec;
1067 x_txn_line_rec := l_txn_line_rec;
1068 x_txn_line_detail_tbl := l_txn_line_dtl_tbl;
1069 x_txn_party_detail_tbl := l_txn_party_tbl;
1070 x_txn_pty_acct_detail_tbl := l_txn_pty_acct_tbl;
1071 x_txn_org_assgn_tbl := l_txn_org_assgn_tbl;
1072
1073 csi_t_gen_utility_pvt.dump_txn_source_rec(
1074 p_txn_source_rec => l_txn_source_rec);
1075
1076 EXCEPTION
1077 WHEN fnd_api.g_exc_error THEN
1078 x_return_status := fnd_api.g_ret_sts_error;
1079 WHEN others THEN
1080 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
1081 fnd_message.set_token('MESSAGE',sqlerrm);
1082 fnd_msg_pub.add;
1083 x_return_status := fnd_api.g_ret_sts_error;
1084 END get_source_dtls;
1085
1086
1087 FUNCTION delimiter_count (p_data_string IN varchar2 ) RETURN number
1088 IS
1089 l_delimiter char := ':';
1090 l_loop_count binary_integer;
1091 l_delimiter_position binary_integer;
1092 BEGIN
1093
1094 l_loop_count := 0;
1095 LOOP
1096 l_loop_count := l_loop_count + 1;
1097 l_delimiter_position := instr(p_data_string, l_delimiter, 1, l_loop_count);
1098
1099 IF l_delimiter_position = 0 THEN
1100 EXIT;
1101 END IF;
1102 END LOOP;
1103
1104 IF l_loop_count > 0 THEN
1105 RETURN(l_loop_count-1);
1106 ELSE
1107 RETURN(l_loop_count);
1108 END IF;
1109
1110 END delimiter_count;
1111
1112
1113 FUNCTION om_vld_org_id(p_order_line_id IN number) RETURN number IS
1114 l_org_id number;
1115 l_om_vld_org_id number;
1116 BEGIN
1117
1118 SELECT org_id INTO l_org_id
1119 FROM oe_order_lines_all
1120 WHERE line_id = p_order_line_id;
1121
1122 l_om_vld_org_id := oe_sys_parameters.value(
1123 param_name => 'MASTER_ORGANIZATION_ID',
1124 p_org_id =>l_org_id );
1125
1126 RETURN l_om_vld_org_id;
1127
1128
1129 END om_vld_org_id;
1130
1131 PROCEDURE cascade_child(
1132 p_data_string IN varchar2,
1133 x_return_status OUT NOCOPY varchar2)
1134 IS
1135
1136 l_api_name varchar2(30) := 'create_child';
1137
1138 l_delimiter char := ':';
1139
1140 l_transaction_line_id number;
1141 l_child_source_id number;
1142 l_inventory_item_id number;
1143 l_item_revision varchar2(30);
1144 l_quantity_ratio number;
1145 l_order_qty number; --added for bug 5096435
1146 l_item_uom varchar2(3);
1147
1148 l_parent_source_table varchar2(30);
1149 l_parent_source_id number;
1150
1151 l_txn_cascade_tbl csi_t_utilities_pvt.txn_cascade_tbl;
1152
1153 /* translate string parameters */
1154
1155 l_loop_count binary_integer;
1156 l_exit boolean;
1157 l_delimiter_position binary_integer;
1158 l_new_string varchar2(2000);
1159 l_value varchar2(100);
1160 l_delimiter_count number;
1161
1162 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1163 l_msg_data varchar2(512);
1164 l_msg_count number;
1165 --declared for bug5096435
1166 l_model_qty number;
1167 l_model_remnant_flag varchar2(1) := 'N';
1168 l_sum_qty number;
1169 l_link_to_line_id number;
1170
1171 BEGIN
1172
1173 fnd_msg_pub.initialize;
1174
1175 csi_t_gen_utility_pvt.dump_api_info(
1176 p_api_name => l_api_name,
1177 p_pkg_name => g_pkg_name);
1178
1179 x_return_status := fnd_api.g_ret_sts_success;
1180
1181 /* translate data string in to values */
1182
1183 l_loop_count := 0;
1184 l_exit := FALSE;
1185 l_delimiter_position := 0;
1186 l_new_string := p_data_string;
1187
1188 l_delimiter_count := delimiter_count(p_data_string => p_data_string);
1189
1190 debug(' hierarchy_string : '||p_data_string);
1191 debug(' delimiter_count : '||l_delimiter_count);
1192
1193 LOOP
1194
1195 l_loop_count := l_loop_count + 1;
1196 l_delimiter_position := instr(l_new_string, l_delimiter);
1197
1198 IF l_delimiter_position = 0 THEN
1199 l_exit := TRUE;
1200 l_delimiter_position := length(l_new_string) + 1;
1201 END IF;
1202
1203 l_value := substr(l_new_string, 1, (l_delimiter_position-1));
1204
1205 IF l_loop_count = 1 THEN
1206 l_parent_source_id := l_value;
1207
1208 END IF;
1209
1210 IF l_loop_count = 2 THEN
1211 l_child_source_id := l_value;
1212 END IF;
1213
1214 IF l_loop_count = 3 THEN
1215 l_inventory_item_id := l_value;
1216 END IF;
1217
1218 --fix for bug5096435
1219 IF l_loop_count = 4 THEN
1220
1221 IF l_delimiter_count = 6 THEN
1222 l_item_revision := l_value;
1223 ELSE
1224 l_item_revision := null;
1225 l_quantity_ratio := l_value;
1226 END IF;
1227
1228 END IF;
1229
1230 IF l_loop_count = 5 THEN
1231 IF l_delimiter_count = 6 THEN
1232 l_quantity_ratio := l_value;
1233 ELSE
1234 l_item_uom := l_value;
1235 END IF;
1236
1237 END IF;
1238 IF l_loop_count = 6 THEN
1239 IF l_delimiter_count = 6 THEN
1240 l_item_uom := l_value;
1241 ELSE
1242 l_order_qty := l_value;
1243 END IF;
1244 END IF;
1245 IF l_loop_count = 7 THEN
1246 IF l_delimiter_count = 6 THEN
1247 l_order_qty := l_value;
1248 END IF;
1249 END IF; --end 0f fix of bug 5096435
1250
1251 EXIT when l_exit = TRUE;
1252
1253 l_new_string := substr(l_new_string, (l_delimiter_position + 1));
1254
1255 END LOOP;
1256
1257 --fix for bug5096435
1258 BEGIN
1259 SELECT nvl(model_remnant_flag,'N'),ordered_quantity
1260 INTO l_model_remnant_flag,l_model_qty
1261 FROM oe_order_lines_all
1262 WHERE line_id=l_parent_source_id;
1263 EXCEPTION
1264 WHEN no_data_found THEN
1265 fnd_message.set_name('CSI','CSI_INT_OE_LINE_ID_INVALID');
1266 fnd_message.set_token('OE_LINE_ID', l_parent_source_id);
1267 fnd_msg_pub.add;
1268 RAISE fnd_api.g_exc_error;
1269 END;
1270 IF l_model_remnant_flag = 'Y' THEN
1271 BEGIN
1272 SELECT nvl(link_to_line_id,-99)
1273 INTO l_link_to_line_id
1274 FROM oe_order_lines_all
1275 WHERE line_id=l_child_source_id;
1276 EXCEPTION
1277 WHEN no_data_found THEN
1278 fnd_message.set_name('CSI','CSI_INT_OE_LINE_ID_INVALID');
1279 fnd_message.set_token('OE_LINE_ID', l_child_source_id);
1280 fnd_msg_pub.add;
1281 RAISE fnd_api.g_exc_error;
1282 END;
1283 IF l_link_to_line_id <> -99 THEN
1284
1285 SELECT SUM(ordered_quantity) INTO l_sum_qty
1286 FROM oe_order_lines_all
1287 WHERE link_to_line_id = l_link_to_line_id
1288 AND inventory_item_id = l_inventory_item_id;
1289
1290 l_quantity_ratio := l_sum_qty/l_model_qty;
1291 ELSE
1292 fnd_message.set_name('CSI','CSI_OE_LINK_TO_LINE_ID_INVALID');
1293 fnd_message.set_token('OE_LINK_TO_LINE_ID', l_child_source_id);
1294 fnd_msg_pub.add;
1295 RAISE fnd_api.g_exc_error;
1296 END IF;
1297 END IF;
1298 --end of fix for bug 5096435
1299
1300
1301 debug(' top_model_line_id : '|| l_parent_source_id);
1302 debug(' line_id : '|| l_child_source_id);
1303 debug(' inventory_item_id : '|| l_inventory_item_id);
1304 debug(' revision : '|| l_item_revision);
1305 debug(' Ordered Quantity : '|| l_order_qty); --fix for bug5096435
1306 debug(' quantity_ratio : '|| l_quantity_ratio);
1307 debug(' item_uom : '|| l_item_uom);
1308
1309 l_parent_source_table := 'OE_ORDER_LINES_ALL';
1310
1311 l_txn_cascade_tbl(1).parent_source_table := l_parent_source_table;
1312 l_txn_cascade_tbl(1).parent_source_id := l_parent_source_id;
1313 l_txn_cascade_tbl(1).child_source_id := l_child_source_id;
1314 l_txn_cascade_tbl(1).inventory_item_id := l_inventory_item_id;
1315 l_txn_cascade_tbl(1).item_revision := l_item_revision;
1316 l_txn_cascade_tbl(1).ordered_quantity := l_order_qty; --fix for bug5096435
1317 l_txn_cascade_tbl(1).quantity_ratio := l_quantity_ratio;
1318 l_txn_cascade_tbl(1).item_uom := l_item_uom;
1319
1320 csi_t_utilities_pvt.cascade(
1321 p_txn_cascade_tbl => l_txn_cascade_tbl,
1322 x_return_status => l_return_status);
1323
1324 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1325 RAISE fnd_api.g_exc_error;
1326 END IF;
1327
1328 EXCEPTION
1329 WHEN fnd_api.g_exc_error THEN
1330 x_return_status := fnd_api.g_ret_sts_error;
1331 WHEN others THEN
1332 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1333 fnd_message.set_token('MESSAGE', 'Error in cascade_child: '||substr(sqlerrm, 1, 240));
1334 fnd_msg_pub.add;
1335 x_return_status := fnd_api.g_ret_sts_unexp_error;
1336 END cascade_child;
1337
1338 PROCEDURE cascade_model(
1339 p_model_line_id IN number,
1340 x_return_status OUT NOCOPY varchar2)
1341 IS
1342
1343 l_om_vld_org_id number;
1344 l_model_line_rec oe_order_lines_all%rowtype;
1345 l_line_tbl oe_order_pub.line_tbl_type;
1346 l_qty_ratio number;
1347 l_tc_ind number;
1348 l_txn_cascade_tbl csi_t_utilities_pvt.txn_cascade_tbl;
1349
1350 l_api_name varchar2(30) := 'cascade_model';
1351 l_debug_level number;
1352 txn_dtls_not_found exception;
1353 l_error_message varchar2(255);
1354 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1355 l_msg_data varchar2(512);
1356 l_msg_count number;
1357 l_order_line_qty number; --added for bug 5096435
1358
1359 BEGIN
1360
1361 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
1362
1363 csi_t_gen_utility_pvt.dump_api_info(
1364 p_pkg_name => g_pkg_name,
1365 p_api_name => l_api_name);
1366
1367 BEGIN
1368
1369 SELECT * INTO l_model_line_rec
1370 FROM oe_order_lines_all
1371 WHERE line_id = p_model_line_id;
1372
1373 EXCEPTION
1374 WHEN no_data_found THEN
1375
1376 fnd_message.set_name('CSI','CSI_INT_OE_LINE_ID_INVALID');
1377 fnd_message.set_token('OE_LINE_ID', p_model_line_id);
1378 fnd_msg_pub.add;
1379 RAISE fnd_api.g_exc_error;
1380
1381 END;
1382
1383 dbms_application_info.set_client_info(l_model_line_rec.org_id);
1384
1385 l_om_vld_org_id := oe_sys_parameters.value(
1386 param_name => 'MASTER_ORGANIZATION_ID',
1387 p_org_id => l_model_line_rec.org_id);
1388
1389 csi_order_fulfill_pub.get_all_ib_trackable_children(
1390 p_model_line_id => p_model_line_id,
1391 p_om_vld_org_id => l_om_vld_org_id,
1392 x_trackable_line_tbl => l_line_tbl,
1393 x_return_status => l_return_status);
1394
1395 l_tc_ind := 0;
1396
1397 IF l_line_tbl.COUNT > 0 THEN
1398
1399 FOR l_ind IN l_line_tbl.FIRST .. l_line_tbl.LAST
1400 LOOP
1401 --fix for bug 5096435
1402 --Here we ensure that for remnant lines we calculate ratio
1403 --by summing all order quantity of relevent order lines.
1404 IF l_line_tbl(l_ind).model_remnant_flag = 'Y' THEN
1405 BEGIN
1406 select sum(ordered_quantity)
1407 into l_order_line_qty
1408 from oe_order_lines_all
1409 where link_to_line_id = l_line_tbl(l_ind).link_to_line_id
1410 and inventory_item_id = l_line_tbl(l_ind).inventory_item_id
1411 and model_remnant_flag = 'Y';
1412 EXCEPTION
1413 WHEN others THEN
1414 NULL;
1415 END;
1416 l_qty_ratio := l_order_line_qty/l_model_line_rec.ordered_quantity;
1417 ELSE
1418 l_qty_ratio := l_line_tbl(l_ind).ordered_quantity/l_model_line_rec.ordered_quantity;
1419 END IF;
1420
1421 l_tc_ind := l_tc_ind + 1;
1422
1423 l_txn_cascade_tbl(l_tc_ind).parent_source_table := 'OE_ORDER_LINES_ALL';
1424 l_txn_cascade_tbl(l_tc_ind).parent_source_id := p_model_line_id;
1425 l_txn_cascade_tbl(l_tc_ind).child_source_id := l_line_tbl(l_ind).line_id;
1426 --fix for bug5096435:ordered_quantity is included to have better control of qty in cascade
1427 --api while cascading txn details from model line
1428 l_txn_cascade_tbl(l_tc_ind).ordered_quantity := l_line_tbl(l_ind).ordered_quantity;
1429 l_txn_cascade_tbl(l_tc_ind).inventory_item_id := l_line_tbl(l_ind).inventory_item_id;
1430 l_txn_cascade_tbl(l_tc_ind).item_revision := l_line_tbl(l_ind).item_revision;
1431 l_txn_cascade_tbl(l_tc_ind).quantity_ratio := l_qty_ratio;
1432 l_txn_cascade_tbl(l_tc_ind).item_uom := l_line_tbl(l_ind).order_quantity_uom;
1433
1434 END LOOP;
1435
1436 END IF;
1437
1438 csi_t_utilities_pvt.cascade(
1439 p_txn_cascade_tbl => l_txn_cascade_tbl,
1440 x_return_status => l_return_status);
1441
1442 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1443 RAISE fnd_api.g_exc_error;
1444 END IF;
1445
1446 EXCEPTION
1447 WHEN fnd_api.g_exc_error THEN
1448 x_return_status := fnd_api.g_ret_sts_error;
1449 END cascade_model;
1450
1451
1452 /* CASCADE_TXN_DTLS */
1453 PROCEDURE cascade(
1454 p_txn_cascade_tbl IN csi_t_utilities_pvt.txn_cascade_tbl,
1455 x_return_status OUT NOCOPY varchar2)
1456 IS
1457
1458 l_om_vld_org_id number;
1459 l_src_txn_type_id number;
1460 l_src_txn_hdr_id number;
1461 l_chk_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
1462
1463 l_td_found boolean := FALSE;
1464
1465 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
1466 l_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec;
1467 l_txn_line_detail_query_rec csi_t_datastructures_grp.txn_line_detail_query_rec;
1468
1469 l_g_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
1470 l_g_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
1471 l_g_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
1472 l_g_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
1473 l_g_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
1474 l_g_ext_attrib_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
1475 l_g_csi_ea_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
1476 l_g_csi_eav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
1477 l_g_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
1478
1479 l_c_td_ind binary_integer;
1480 l_c_pt_ind binary_integer;
1481 l_c_pa_ind binary_integer;
1482 l_c_oa_ind binary_integer;
1483 l_c_ea_ind binary_integer;
1484 l_c_con_ind binary_integer; -- Added for Bug 3648418 (Ref Bug 3605645)
1485
1486 l_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
1487 l_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
1488 l_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
1489 l_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
1490 l_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
1491 l_ext_attrib_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
1492 l_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
1493
1494 -- For Bug 3555078
1495 l_parent_line_rec oe_order_pub.line_rec_type := oe_order_pub.g_miss_line_rec;
1496 l_no_trackable_parent varchar2(1) := 'N';
1497 l_loop_quantity number := 0;
1498
1499 l_api_name varchar2(30) := 'cascade';
1500 l_debug_level number;
1501 txn_dtls_not_found exception;
1502 l_error_message varchar2(255);
1503 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1504 l_msg_data varchar2(512);
1505 l_msg_count number;
1506
1507 --parent-child cascading for remnant 4344316
1508 l_non_ib_mdl_qty number;
1509 l_rem_qty number := 0; --added for bug5096435
1510 BEGIN
1511
1512 csi_t_gen_utility_pvt.dump_api_info(
1513 p_api_name => l_api_name,
1514 p_pkg_name => g_pkg_name);
1515
1516 IF p_txn_cascade_tbl.COUNT > 0 THEN
1517
1518 -- get_transaction_dtls
1519 l_txn_line_query_rec.source_transaction_table := p_txn_cascade_tbl(1).parent_source_table;
1520 l_txn_line_query_rec.source_transaction_id := p_txn_cascade_tbl(1).parent_source_id;
1521
1522 l_txn_line_detail_query_rec.source_transaction_flag := 'Y';
1523
1524 csi_t_txn_details_grp.get_transaction_details(
1525 p_api_version => 1,
1526 p_commit => fnd_api.g_false,
1527 p_init_msg_list => fnd_api.g_true,
1528 p_validation_level => fnd_api.g_valid_level_full,
1529 p_txn_line_query_rec => l_txn_line_query_rec,
1530 p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
1531 x_txn_line_detail_tbl => l_g_line_dtl_tbl,
1532 p_get_parties_flag => fnd_api.g_true,
1533 x_txn_party_detail_tbl => l_g_pty_dtl_tbl,
1534 p_get_pty_accts_flag => fnd_api.g_true,
1535 x_txn_pty_acct_detail_tbl => l_g_pty_acct_tbl,
1536 p_get_ii_rltns_flag => fnd_api.g_false,
1537 x_txn_ii_rltns_tbl => l_g_ii_rltns_tbl,
1538 p_get_org_assgns_flag => fnd_api.g_true,
1539 x_txn_org_assgn_tbl => l_g_org_assgn_tbl,
1540 p_get_ext_attrib_vals_flag => fnd_api.g_false,
1541 x_txn_ext_attrib_vals_tbl => l_g_ext_attrib_tbl,
1542 p_get_csi_attribs_flag => fnd_api.g_false,
1543 x_csi_ext_attribs_tbl => l_g_csi_ea_tbl,
1544 p_get_csi_iea_values_flag => fnd_api.g_false,
1545 x_csi_iea_values_tbl => l_g_csi_eav_tbl,
1546 p_get_txn_systems_flag => fnd_api.g_false,
1547 x_txn_systems_tbl => l_g_txn_systems_tbl,
1548 x_return_status => l_return_status,
1549 x_msg_count => l_msg_count,
1550 x_msg_data => l_msg_data);
1551
1552 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1553 RAISE fnd_api.g_exc_error;
1554 END IF;
1555
1556 IF l_g_line_dtl_tbl.count = 0 THEN
1557 RAISE txn_dtls_not_found;
1558 END IF;
1559
1560 SELECT source_transaction_type_id,
1561 source_txn_header_id
1562 INTO l_src_txn_type_id,
1563 l_src_txn_hdr_id
1564 FROM csi_t_transaction_lines
1565 WHERE source_transaction_table = p_txn_cascade_tbl(1).parent_source_table
1566 AND source_transaction_id = p_txn_cascade_tbl(1).parent_source_id;
1567
1568 /* for each of the order line children create transaction detail */
1569
1570 FOR l_ind IN p_txn_cascade_tbl.FIRST .. p_txn_cascade_tbl.LAST
1571 LOOP
1572
1573
1574 /* check if transaction details exist for this child */
1575
1576 l_chk_txn_line_rec.source_transaction_table := p_txn_cascade_tbl(l_ind).parent_source_table;
1577 l_chk_txn_line_rec.source_transaction_id := p_txn_cascade_tbl(l_ind).child_source_id;
1578
1579 l_td_found := csi_t_txn_details_pvt.check_txn_details_exist(
1580 p_txn_line_rec => l_chk_txn_line_rec);
1581 l_rem_qty := p_txn_cascade_tbl(l_ind).ordered_quantity; --added for bug 5096435
1582
1583 IF NOT (l_td_found) THEN
1584
1585 l_txn_line_rec.transaction_line_id := fnd_api.g_miss_num;
1586 l_txn_line_rec.source_transaction_table := p_txn_cascade_tbl(l_ind).parent_source_table;
1587 l_txn_line_rec.source_transaction_id := p_txn_cascade_tbl(l_ind).child_source_id;
1588 l_txn_line_rec.source_transaction_type_id := l_src_txn_type_id;
1589 l_txn_line_rec.source_txn_header_id := l_src_txn_hdr_id;
1590 l_txn_line_rec.processing_status := 'SUBMIT';
1591
1592 l_c_td_ind := 0;
1593 l_c_pt_ind := 0;
1594 l_c_pa_ind := 0;
1595 l_c_oa_ind := 0;
1596 l_c_ea_ind := 0;
1597 l_c_con_ind := 0; -- Added for Bug 3648418 (Ref Bug 3605645)
1598
1599 l_om_vld_org_id := om_vld_org_id(p_txn_cascade_tbl(1).child_source_id);
1600
1601 -- Begin fix for Bug 3555078
1602 csi_order_fulfill_pub.get_ib_trackable_parent(
1603 p_current_line_id => p_txn_cascade_tbl(1).child_source_id,
1604 p_om_vld_org_id => l_om_vld_org_id,
1605 x_parent_line_rec => l_parent_line_rec,
1606 x_return_status => l_return_status);
1607
1608 If nvl(l_parent_line_rec.line_id,fnd_api.g_miss_num) = fnd_api.g_miss_num
1609 Then
1610 l_no_trackable_parent := 'Y';
1611 End If;
1612 -- End fix for Bug 3555078
1613
1614 FOR l_td_ind IN l_g_line_dtl_tbl.FIRST .. l_g_line_dtl_tbl.LAST
1615 LOOP
1616 IF l_rem_qty <= 0 THEN --fix for bug 5096435
1617 EXIT;
1618 END IF;
1619 -- Begin Fix for Bug 3555078
1620 IF l_no_trackable_parent = 'Y'
1621 Then
1622 l_loop_quantity := 1;
1623 Else
1624 --Start 4344316
1625 IF l_g_line_dtl_tbl(l_td_ind).quantity > l_parent_line_rec.ordered_quantity Then
1626 l_loop_quantity := l_parent_line_rec.ordered_quantity;
1627 ELSE
1628 l_loop_quantity := l_g_line_dtl_tbl(l_td_ind).quantity;
1629 END IF;
1630 --End 4344316
1631 End If;
1632 -- End fix for Bug 3555078
1633
1634
1635
1636
1637 FOR i in 1..l_loop_quantity -- l_g_line_dtl_tbl(l_td_ind).quantity
1638 LOOP
1639 IF l_rem_qty <= 0 THEN --fix for bug 5096435
1640 EXIT;
1641 END IF;
1642
1643 l_c_td_ind := l_c_td_ind + 1;
1644
1645 l_line_dtl_tbl(l_c_td_ind) := l_g_line_dtl_tbl(l_td_ind);
1646
1647 -- Begin Fix for Bug 3555078
1648 -- l_line_dtl_tbl(l_c_td_ind).quantity := p_txn_cascade_tbl(l_ind).quantity_ratio;
1649 /* IF l_no_trackable_parent = 'Y'
1650 Then
1651 l_line_dtl_tbl(l_c_td_ind).quantity := p_txn_cascade_tbl(l_ind).quantity_ratio * l_g_line_dtl_tbl(l_td_ind).quantity;
1652 ELSE
1653 l_line_dtl_tbl(l_c_td_ind).quantity := p_txn_cascade_tbl(l_ind).quantity_ratio;
1654 END IF;*/
1655 -- End fix for Bug 3555078
1656
1657 --Start 4344316
1658 IF l_no_trackable_parent = 'N'
1659 THEN
1660 IF l_rem_qty <= p_txn_cascade_tbl(l_ind).quantity_ratio THEN --fix for bug 5096435
1661 l_line_dtl_tbl(l_c_td_ind).quantity := l_rem_qty;
1662 ELSE
1663 l_line_dtl_tbl(l_c_td_ind).quantity := p_txn_cascade_tbl(l_ind).quantity_ratio;
1664 END IF;
1665 ELSE
1666 BEGIN
1667 select nvl(ordered_quantity,0) INTO l_non_ib_mdl_qty
1668 FROM oe_order_lines_all
1669 WHERE line_id = p_txn_cascade_tbl(1).parent_source_id;
1670 IF l_rem_qty <= p_txn_cascade_tbl(l_ind).quantity_ratio THEN --fix for bug 5096435
1671 l_line_dtl_tbl(l_c_td_ind).quantity := l_rem_qty;
1672 ELSE
1673 If l_g_line_dtl_tbl(l_td_ind).quantity > l_non_ib_mdl_qty Then
1674 l_line_dtl_tbl(l_c_td_ind).quantity := p_txn_cascade_tbl(l_ind).quantity_ratio * l_non_ib_mdl_qty;
1675 Else
1676 l_line_dtl_tbl(l_c_td_ind).quantity := p_txn_cascade_tbl(l_ind).quantity_ratio * l_g_line_dtl_tbl(l_td_ind).quantity;
1677 End If;
1678 End If;
1679 EXCEPTION
1680 WHEN no_data_found THEN
1681 fnd_message.set_name('CSI','CSI_INT_OE_LINE_ID_INVALID');
1682 fnd_message.set_token('OE_LINE_ID', p_txn_cascade_tbl(1).parent_source_id);
1683 fnd_msg_pub.add;
1684 RAISE fnd_api.g_exc_error;
1685 END;
1686 END IF;
1687 --Start 4344316
1688 l_rem_qty := l_rem_qty - l_line_dtl_tbl(l_c_td_ind).quantity; --fix for bug5096435
1689 l_line_dtl_tbl(l_c_td_ind).transaction_line_id := fnd_api.g_miss_num;
1690 l_line_dtl_tbl(l_c_td_ind).txn_line_detail_id := fnd_api.g_miss_num;
1691 l_line_dtl_tbl(l_c_td_ind).inventory_item_id :=
1692 p_txn_cascade_tbl(l_ind).inventory_item_id;
1693 l_line_dtl_tbl(l_c_td_ind).unit_of_measure := p_txn_cascade_tbl(l_ind).item_uom;
1694 l_line_dtl_tbl(l_c_td_ind).inventory_revision := p_txn_cascade_tbl(l_ind).item_revision;
1695 l_line_dtl_tbl(l_c_td_ind).csi_transaction_id := fnd_api.g_miss_num;
1696 l_line_dtl_tbl(l_c_td_ind).processing_status := 'SUBMIT';
1697 l_line_dtl_tbl(l_c_td_ind).instance_exists_flag := 'N';
1698 l_line_dtl_tbl(l_c_td_ind).instance_id := fnd_api.g_miss_num;
1699 l_line_dtl_tbl(l_c_td_ind).source_txn_line_detail_id :=
1700 l_g_line_dtl_tbl(l_td_ind).txn_line_detail_id;
1701 l_line_dtl_tbl(l_c_td_ind).changed_instance_id := fnd_api.g_miss_num;
1702
1703 -- Fix for Bug 2962072 included to Null out Version_Label.
1704 l_line_dtl_tbl(l_c_td_ind).version_label := fnd_api.g_miss_char;
1705
1706 -- derive the item related attributes here
1707
1708 IF l_g_pty_dtl_tbl.COUNT > 0 THEN
1709 FOR l_pt_ind IN l_g_pty_dtl_tbl.FIRST .. l_g_pty_dtl_tbl.LAST
1710 LOOP
1711
1712 IF l_g_pty_dtl_tbl(l_pt_ind).txn_line_detail_id = l_g_line_dtl_tbl(l_td_ind).txn_line_detail_id
1713 AND
1714 l_g_pty_dtl_tbl(l_pt_ind).Contact_FLAG ='N' -- Added the condition for Bug 3648418 (Ref Bug 3605645)
1715 THEN
1716
1717 l_c_pt_ind := l_c_pt_ind + 1;
1718
1719 l_pty_dtl_tbl(l_c_pt_ind) := l_g_pty_dtl_tbl(l_pt_ind);
1720 l_pty_dtl_tbl(l_c_pt_ind).txn_line_detail_id := fnd_api.g_miss_num;
1721 l_pty_dtl_tbl(l_c_pt_ind).txn_party_detail_id := fnd_api.g_miss_num;
1722 l_pty_dtl_tbl(l_c_pt_ind).txn_line_details_index := l_c_td_ind;
1723 l_pty_dtl_tbl(l_c_pt_ind).instance_party_id := fnd_api.g_miss_num;
1724 l_c_con_ind := l_c_pt_ind; -- Added for Bug 3648418 (Ref Bug 3605645), Trapping index for Party in context
1725
1726 -- For Bug 3524837
1727 /*-- Creating index for contact parties --*/
1728 /* Begin fix for Bug 3648418 (Ref Bug 3605645)
1729 IF NVL(l_pty_dtl_tbl(l_c_pt_ind).contact_party_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1730 THEN
1731 FOR l_con_pt_ind in l_g_pty_dtl_tbl.FIRST .. l_g_pty_dtl_tbl.LAST
1732 LOOP
1733 IF l_g_pty_dtl_tbl(l_con_pt_ind).txn_party_detail_id = l_pty_dtl_tbl(l_c_pt_ind).contact_party_id
1734 THEN
1735 l_pty_dtl_tbl(l_c_pt_ind).contact_party_id := l_con_pt_ind;
1736 END IF;
1737 END LOOP;
1738 END IF;
1739 */
1740 FOR l_con_pt_ind in l_g_pty_dtl_tbl.FIRST .. l_g_pty_dtl_tbl.LAST
1741 LOOP
1742 IF l_g_pty_dtl_tbl(l_con_pt_ind).txn_line_detail_id = l_g_line_dtl_tbl(l_td_ind).txn_line_detail_id
1743 AND
1744 NVL(l_g_pty_dtl_tbl(l_con_pt_ind).contact_party_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1745 AND
1746 l_g_pty_dtl_tbl(l_pt_ind).txn_party_detail_id = l_g_pty_dtl_tbl(l_con_pt_ind).contact_party_id
1747 THEN
1748 l_c_pt_ind := l_c_pt_ind + 1;
1749 l_pty_dtl_tbl(l_c_pt_ind) := l_g_pty_dtl_tbl(l_con_pt_ind);
1750 l_pty_dtl_tbl(l_c_pt_ind).txn_line_detail_id := fnd_api.g_miss_num;
1751 l_pty_dtl_tbl(l_c_pt_ind).txn_party_detail_id := fnd_api.g_miss_num;
1752 l_pty_dtl_tbl(l_c_pt_ind).txn_line_details_index := l_c_td_ind;
1753 l_pty_dtl_tbl(l_c_pt_ind).instance_party_id := fnd_api.g_miss_num;
1754 l_pty_dtl_tbl(l_c_pt_ind).contact_party_id := l_c_con_ind;
1755 END IF;
1756 END LOOP;
1757 -- End fix for Bug 3648418 (Ref Bug 3605645)
1758
1759 IF l_g_pty_acct_tbl.COUNT > 0 THEN
1760
1761 FOR l_pa_ind IN l_g_pty_acct_tbl.FIRST .. l_g_pty_acct_tbl.LAST
1762 LOOP
1763 IF l_g_pty_acct_tbl(l_pa_ind).txn_party_detail_id =
1764 l_g_pty_dtl_tbl(l_pt_ind).txn_party_detail_id THEN
1765
1766 l_c_pa_ind := l_c_pa_ind + 1;
1767
1768 l_pty_acct_tbl(l_c_pa_ind) := l_g_pty_acct_tbl(l_pa_ind);
1769 l_pty_acct_tbl(l_c_pa_ind).txn_party_detail_id := fnd_api.g_miss_num;
1770 l_pty_acct_tbl(l_c_pa_ind).txn_account_detail_id := fnd_api.g_miss_num;
1771 l_pty_acct_tbl(l_c_pa_ind).txn_party_details_index := l_c_con_ind; -- l_c_pt_ind Changed for Bug 3648418 (Ref Bug 3605645)
1772 l_pty_acct_tbl(l_c_pa_ind).ip_account_id := fnd_api.g_miss_num;
1773
1774 END IF; -- pty acct detail id chk
1775
1776 END LOOP; -- party acct table loop
1777
1778 END IF; -- party acct count chk
1779
1780 END IF; -- txn_line_detail_id check
1781
1782 END LOOP; -- party table loop
1783
1784 END IF; -- party count check
1785
1786 IF l_g_org_assgn_tbl.COUNT > 0 THEN
1787 FOR l_oa_ind IN l_g_org_assgn_tbl.FIRST .. l_g_org_assgn_tbl.LAST
1788 LOOP
1789 IF l_g_org_assgn_tbl(l_oa_ind).txn_line_detail_id =
1790 l_g_line_dtl_tbl(l_td_ind).txn_line_detail_id THEN
1791
1792 l_c_oa_ind := l_c_oa_ind + 1;
1793 l_org_assgn_tbl(l_c_oa_ind) := l_g_org_assgn_tbl(l_oa_ind);
1794 l_org_assgn_tbl(l_c_oa_ind).txn_line_detail_id := fnd_api.g_miss_num;
1795 l_org_assgn_tbl(l_c_oa_ind).txn_operating_unit_id := fnd_api.g_miss_num;
1796 l_org_assgn_tbl(l_c_oa_ind).txn_line_details_index := l_c_td_ind;
1797 l_org_assgn_tbl(l_c_oa_ind).instance_ou_id := fnd_api.g_miss_num;
1798
1799 END IF;
1800 END LOOP;
1801 END IF;
1802
1803 IF l_g_ext_attrib_tbl.COUNT > 0 THEN
1804 FOR l_ea_ind IN l_g_ext_attrib_tbl.FIRST .. l_g_ext_attrib_tbl.LAST
1805 LOOP
1806 IF l_g_ext_attrib_tbl(l_ea_ind).txn_line_detail_id =
1807 l_g_line_dtl_tbl(l_td_ind).txn_line_detail_id THEN
1808
1809 l_c_ea_ind := l_c_ea_ind + 1;
1810 l_ext_attrib_tbl(l_c_ea_ind) := l_g_ext_attrib_tbl(l_ea_ind);
1811 l_ext_attrib_tbl(l_c_ea_ind).txn_line_detail_id := fnd_api.g_miss_num;
1812 l_ext_attrib_tbl(l_c_ea_ind).txn_attrib_detail_id := fnd_api.g_miss_num;
1813 l_ext_attrib_tbl(l_c_ea_ind).txn_line_details_index := l_c_td_ind;
1814
1815 END IF;
1816 END LOOP;
1817 END IF;
1818 END LOOP;
1819
1820 END LOOP; -- txn line details loop
1821
1822 -- create transaction dtls
1823 csi_t_txn_details_grp.create_transaction_dtls(
1824 p_api_version => 1.0,
1825 p_commit => fnd_api.g_false,
1826 p_init_msg_list => fnd_api.g_true,
1827 p_validation_level => fnd_api.g_valid_level_full,
1828 px_txn_line_rec => l_txn_line_rec,
1829 px_txn_line_detail_tbl => l_line_dtl_tbl,
1830 px_txn_party_detail_tbl => l_pty_dtl_tbl,
1831 px_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
1832 px_txn_ii_rltns_tbl => l_ii_rltns_tbl,
1833 px_txn_org_assgn_tbl => l_org_assgn_tbl,
1834 px_txn_ext_attrib_vals_tbl => l_ext_attrib_tbl,
1835 px_txn_systems_tbl => l_txn_systems_tbl,
1836 x_return_status => l_return_status,
1837 x_msg_count => l_msg_count,
1838 x_msg_data => l_msg_data);
1839
1840 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1841 raise fnd_api.g_exc_error;
1842 END IF;
1843
1844 /* clean up the pl/sql tables */
1845 l_line_dtl_tbl.DELETE;
1846 l_pty_dtl_tbl.DELETE;
1847 l_pty_acct_tbl.DELETE;
1848 l_ii_rltns_tbl.DELETE;
1849 l_org_assgn_tbl.DELETE;
1850 l_ext_attrib_tbl.DELETE;
1851 l_txn_systems_tbl.DELETE;
1852
1853 ELSE
1854 debug('txn dtls found for the child '||p_txn_cascade_tbl(l_ind).child_source_id);
1855 END IF; -- td found for the children chk
1856
1857 END LOOP;-- children line table loop
1858
1859 END IF; -- children line table count chk
1860
1861 EXCEPTION
1862
1863 WHEN txn_dtls_not_found THEN
1864 x_return_status := fnd_api.g_ret_sts_success;
1865 WHEN fnd_api.g_exc_error THEN
1866 x_return_status := fnd_api.g_ret_sts_error;
1867
1868 END cascade;
1869
1870 END csi_t_utilities_pvt;