1 PACKAGE CZ_IB_TRANSACTIONS AS
2 /* $Header: czibtxs.pls 120.0.12010000.2 2008/11/10 07:14:15 kksriram ship $ */
3
4 -----------------------------------------------------------------------
5 ------------------ stubs for CSI structures ---------------------------
6 -----------------------------------------------------------------------
7
8 -- Name : txn_system_rec
9 -- Description : record to hold the values of systems.
10
11 TYPE txn_system_rec IS RECORD(
12 TRANSACTION_SYSTEM_ID NUMBER ,
13 TRANSACTION_LINE_ID NUMBER ,
14 SYSTEM_NAME VARCHAR2(50) ,
15 DESCRIPTION VARCHAR2(240) ,
16 SYSTEM_TYPE_CODE VARCHAR2(30) ,
17 SYSTEM_NUMBER VARCHAR2(30) ,
18 CUSTOMER_ID NUMBER ,
19 BILL_TO_CONTACT_ID NUMBER ,
20 SHIP_TO_CONTACT_ID NUMBER ,
21 TECHNICAL_CONTACT_ID NUMBER ,
22 SERVICE_ADMIN_CONTACT_ID NUMBER ,
23 SHIP_TO_SITE_USE_ID NUMBER ,
24 BILL_TO_SITE_USE_ID NUMBER ,
25 INSTALL_SITE_USE_ID NUMBER ,
26 COTERMINATE_DAY_MONTH VARCHAR2(6) ,
27 CONFIG_SYSTEM_TYPE VARCHAR2(30) ,
28 START_DATE_ACTIVE DATE ,
29 END_DATE_ACTIVE DATE ,
30 CONTEXT VARCHAR2(30) ,
31 ATTRIBUTE1 VARCHAR2(150) ,
32 ATTRIBUTE2 VARCHAR2(150) ,
33 ATTRIBUTE3 VARCHAR2(150) ,
34 ATTRIBUTE4 VARCHAR2(150) ,
35 ATTRIBUTE5 VARCHAR2(150) ,
36 ATTRIBUTE6 VARCHAR2(150) ,
37 ATTRIBUTE7 VARCHAR2(150) ,
38 ATTRIBUTE8 VARCHAR2(150) ,
39 ATTRIBUTE9 VARCHAR2(150) ,
40 ATTRIBUTE10 VARCHAR2(150) ,
41 ATTRIBUTE11 VARCHAR2(150) ,
42 ATTRIBUTE12 VARCHAR2(150) ,
43 ATTRIBUTE13 VARCHAR2(150) ,
44 ATTRIBUTE14 VARCHAR2(150) ,
45 ATTRIBUTE15 VARCHAR2(150) ,
46 OBJECT_VERSION_NUMBER NUMBER );
47
48 TYPE txn_systems_tbl IS TABLE OF txn_system_rec INDEX BY BINARY_INTEGER;
49
50 -- Name : txn_line_rec
51 -- Type : type definition, group
52 -- Description : record to hold the attributes of the source
53 -- transaction identifiers.
54
55 TYPE txn_line_rec IS RECORD (
56 TRANSACTION_LINE_ID NUMBER ,
57 SOURCE_TRANSACTION_TYPE_ID NUMBER ,
58 SOURCE_TRANSACTION_ID NUMBER ,
59 ---Added (Start) for m-to-m enhancements
60 SOURCE_TXN_HEADER_ID NUMBER ,
61 ---Added (End) for m-to-m enhancements
62 SOURCE_TRANSACTION_TABLE VARCHAR2(30) ,
63 -- Added for CZ Integration (Begin)
64 CONFIG_SESSION_HDR_ID NUMBER ,
65 CONFIG_SESSION_REV_NUM NUMBER ,
66 CONFIG_SESSION_ITEM_ID NUMBER ,
67 CONFIG_VALID_STATUS VARCHAR2(30) ,
68 SOURCE_TRANSACTION_STATUS VARCHAR2(30) ,
69 API_CALLER_IDENTITY VARCHAR2(15) ,
70 -- Added for CZ Integration (End)
71 INV_MATERIAL_TXN_FLAG VARCHAR2(1) ,
72 ERROR_CODE VARCHAR2(240) ,
73 ERROR_EXPLANATION VARCHAR2(240) ,
74 PROCESSING_STATUS VARCHAR2(30) ,
75 CONTEXT VARCHAR2(30) ,
76 ATTRIBUTE1 VARCHAR2(150) ,
77 ATTRIBUTE2 VARCHAR2(150) ,
78 ATTRIBUTE3 VARCHAR2(150) ,
79 ATTRIBUTE4 VARCHAR2(150) ,
80 ATTRIBUTE5 VARCHAR2(150) ,
81 ATTRIBUTE6 VARCHAR2(150) ,
82 ATTRIBUTE7 VARCHAR2(150) ,
83 ATTRIBUTE8 VARCHAR2(150) ,
84 ATTRIBUTE9 VARCHAR2(150) ,
85 ATTRIBUTE10 VARCHAR2(150) ,
86 ATTRIBUTE11 VARCHAR2(150) ,
87 ATTRIBUTE12 VARCHAR2(150) ,
88 ATTRIBUTE13 VARCHAR2(150) ,
89 ATTRIBUTE14 VARCHAR2(150) ,
90 ATTRIBUTE15 VARCHAR2(150) ,
91 OBJECT_VERSION_NUMBER NUMBER );
92
93 -- Name : txn_line_detail_rec
94 -- Type : type definition, group
95 -- Description : record to hold the attributes of the txn line detail.
96
97 TYPE txn_line_detail_rec IS RECORD (
98 TXN_LINE_DETAIL_ID NUMBER ,
99 TRANSACTION_LINE_ID NUMBER ,
100 SUB_TYPE_ID NUMBER ,
101 INSTANCE_EXISTS_FLAG VARCHAR2(1) ,
102 SOURCE_TRANSACTION_FLAG VARCHAR2(1) ,
103 INSTANCE_ID NUMBER ,
104 CHANGED_INSTANCE_ID NUMBER ,
105 CSI_SYSTEM_ID NUMBER ,
106 INVENTORY_ITEM_ID NUMBER ,
107 INVENTORY_REVISION VARCHAR2(3) ,
108 INV_ORGANIZATION_ID NUMBER ,
109 ITEM_CONDITION_ID NUMBER ,
110 INSTANCE_TYPE_CODE VARCHAR2(30) ,
111 QUANTITY NUMBER ,
112 UNIT_OF_MEASURE VARCHAR2(3) ,
113 QTY_REMAINING NUMBER ,
114 SERIAL_NUMBER VARCHAR2(30) ,
115 MFG_SERIAL_NUMBER_FLAG VARCHAR2(1) ,
116 LOT_NUMBER VARCHAR2(30) ,
117 LOCATION_TYPE_CODE VARCHAR2(30) ,
118 LOCATION_ID NUMBER ,
119 INSTALLATION_DATE DATE ,
120 IN_SERVICE_DATE DATE ,
121 EXTERNAL_REFERENCE VARCHAR2(30) ,
122 TRANSACTION_SYSTEM_ID NUMBER ,
123 SELLABLE_FLAG VARCHAR2(1) ,
124 VERSION_LABEL VARCHAR2(240) ,
125 RETURN_BY_DATE DATE ,
126 ACTIVE_START_DATE DATE ,
127 ACTIVE_END_DATE DATE ,
128 PRESERVE_DETAIL_FLAG VARCHAR2(1) ,
129 REFERENCE_SOURCE_ID NUMBER ,
130 REFERENCE_SOURCE_DATE DATE ,
131 CSI_TRANSACTION_ID NUMBER ,
132 SOURCE_TXN_LINE_DETAIL_ID NUMBER ,
133 INV_MTL_TRANSACTION_ID NUMBER ,
134 PROCESSING_STATUS VARCHAR2(30) ,
135 ERROR_CODE VARCHAR2(240) ,
136 ERROR_EXPLANATION VARCHAR2(240) ,
137 TXN_SYSTEMS_INDEX NUMBER ,
138 -- Added for CZ Integration (Begin)
139 CONFIG_INST_HDR_ID NUMBER ,
140 CONFIG_INST_REV_NUM NUMBER ,
141 CONFIG_INST_ITEM_ID NUMBER ,
142 CONFIG_INST_BASELINE_REV_NUM NUMBER ,
143 TARGET_COMMITMENT_DATE DATE ,
144 INSTANCE_DESCRIPTION VARCHAR2(240) ,
145 API_CALLER_IDENTITY VARCHAR2(15) ,
146 -- Added for CZ Integration (End)
147 CONTEXT VARCHAR2(30) ,
148 ATTRIBUTE1 VARCHAR2(150) ,
149 ATTRIBUTE2 VARCHAR2(150) ,
150 ATTRIBUTE3 VARCHAR2(150) ,
151 ATTRIBUTE4 VARCHAR2(150) ,
152 ATTRIBUTE5 VARCHAR2(150) ,
153 ATTRIBUTE6 VARCHAR2(150) ,
154 ATTRIBUTE7 VARCHAR2(150) ,
155 ATTRIBUTE8 VARCHAR2(150) ,
156 ATTRIBUTE9 VARCHAR2(150) ,
157 ATTRIBUTE10 VARCHAR2(150) ,
158 ATTRIBUTE11 VARCHAR2(150) ,
159 ATTRIBUTE12 VARCHAR2(150) ,
160 ATTRIBUTE13 VARCHAR2(150) ,
161 ATTRIBUTE14 VARCHAR2(150) ,
162 ATTRIBUTE15 VARCHAR2(150) ,
163 OBJECT_VERSION_NUMBER NUMBER );
164
165 TYPE txn_line_detail_tbl IS TABLE OF txn_line_detail_rec INDEX BY BINARY_INTEGER;
166
167
168 -- Name : txn_party_detail_rec
169 -- Type : type definition, group
170 -- Description : record to hold information about an transaction
171 -- detail-party relationship.
172
173 TYPE txn_party_detail_rec IS RECORD (
174 TXN_PARTY_DETAIL_ID NUMBER ,
175 TXN_LINE_DETAIL_ID NUMBER ,
176 INSTANCE_PARTY_ID NUMBER ,
177 PARTY_SOURCE_TABLE VARCHAR2(30) ,
178 PARTY_SOURCE_ID NUMBER ,
179 RELATIONSHIP_TYPE_CODE VARCHAR2(30) ,
180 CONTACT_FLAG VARCHAR2(1) ,
181 CONTACT_PARTY_ID NUMBER ,
182 ACTIVE_START_DATE DATE ,
183 ACTIVE_END_DATE DATE ,
184 PRESERVE_DETAIL_FLAG VARCHAR2(1) ,
185 CONTEXT VARCHAR2(30) ,
186 ATTRIBUTE1 VARCHAR2(150) ,
187 ATTRIBUTE2 VARCHAR2(150) ,
188 ATTRIBUTE3 VARCHAR2(150) ,
189 ATTRIBUTE4 VARCHAR2(150) ,
190 ATTRIBUTE5 VARCHAR2(150) ,
191 ATTRIBUTE6 VARCHAR2(150) ,
192 ATTRIBUTE7 VARCHAR2(150) ,
193 ATTRIBUTE8 VARCHAR2(150) ,
194 ATTRIBUTE9 VARCHAR2(150) ,
195 ATTRIBUTE10 VARCHAR2(150) ,
196 ATTRIBUTE11 VARCHAR2(150) ,
197 ATTRIBUTE12 VARCHAR2(150) ,
198 ATTRIBUTE13 VARCHAR2(150) ,
199 ATTRIBUTE14 VARCHAR2(150) ,
200 ATTRIBUTE15 VARCHAR2(150) ,
201 TXN_LINE_DETAILS_INDEX NUMBER ,
202 OBJECT_VERSION_NUMBER NUMBER );
203
204 TYPE txn_party_detail_tbl IS TABLE OF txn_party_detail_rec INDEX BY BINARY_INTEGER;
205
206
207 -- Name : txn_pty_acct_detail_rec
208 -- Type : type definition, group
209 -- Description : record to hold information about an transaction
210 -- party detail-account relationship.
211
212 TYPE txn_pty_acct_detail_rec IS RECORD (
213 TXN_ACCOUNT_DETAIL_ID NUMBER ,
214 TXN_PARTY_DETAIL_ID NUMBER ,
215 IP_ACCOUNT_ID NUMBER ,
216 ACCOUNT_ID NUMBER ,
217 RELATIONSHIP_TYPE_CODE VARCHAR2(30) ,
218 BILL_TO_ADDRESS_ID NUMBER ,
219 SHIP_TO_ADDRESS_ID NUMBER ,
220 ACTIVE_START_DATE DATE ,
221 ACTIVE_END_DATE DATE ,
222 PRESERVE_DETAIL_FLAG VARCHAR2(1) ,
223 CONTEXT VARCHAR2(30) ,
224 ATTRIBUTE1 VARCHAR2(150) ,
225 ATTRIBUTE2 VARCHAR2(150) ,
226 ATTRIBUTE3 VARCHAR2(150) ,
227 ATTRIBUTE4 VARCHAR2(150) ,
228 ATTRIBUTE5 VARCHAR2(150) ,
229 ATTRIBUTE6 VARCHAR2(150) ,
230 ATTRIBUTE7 VARCHAR2(150) ,
231 ATTRIBUTE8 VARCHAR2(150) ,
232 ATTRIBUTE9 VARCHAR2(150) ,
233 ATTRIBUTE10 VARCHAR2(150) ,
234 ATTRIBUTE11 VARCHAR2(150) ,
235 ATTRIBUTE12 VARCHAR2(150) ,
236 ATTRIBUTE13 VARCHAR2(150) ,
237 ATTRIBUTE14 VARCHAR2(150) ,
238 ATTRIBUTE15 VARCHAR2(150) ,
239 TXN_PARTY_DETAILS_INDEX NUMBER ,
240 OBJECT_VERSION_NUMBER NUMBER );
241
242 TYPE txn_pty_acct_detail_tbl IS TABLE OF txn_pty_acct_detail_rec INDEX BY BINARY_INTEGER;
243
244
245 -- Name : txn_ii_rltns_rec
246 -- Type : type definition, group
247 -- Description : record to hold information about an transaction
248 -- detail-configuration details.
249
250 TYPE txn_ii_rltns_rec IS RECORD (
251 TXN_RELATIONSHIP_ID NUMBER ,
252 TRANSACTION_LINE_ID NUMBER ,
253 CSI_INST_RELATIONSHIP_ID NUMBER ,
254 SUBJECT_ID NUMBER ,
255 ---Added (Start) for m-to-m enhancements
256 SUBJECT_INDEX_FLAG VARCHAR2(1) ,
257 SUBJECT_TYPE VARCHAR2(30) := 'T' ,
258 ---Added (End) for m-to-m enhancements
259 OBJECT_ID NUMBER ,
260 ---Added (Start) for m-to-m enhancements
261 OBJECT_INDEX_FLAG VARCHAR2(1) ,
262 OBJECT_TYPE VARCHAR2(30) := 'T' ,
263 ---Added (End) for m-to-m enhancements
264 -- Added for CZ Integration (Begin)
265 SUB_CONFIG_INST_HDR_ID NUMBER ,
266 SUB_CONFIG_INST_REV_NUM NUMBER ,
267 SUB_CONFIG_INST_ITEM_ID NUMBER ,
268 OBJ_CONFIG_INST_HDR_ID NUMBER ,
269 OBJ_CONFIG_INST_REV_NUM NUMBER ,
270 OBJ_CONFIG_INST_ITEM_ID NUMBER ,
271 TARGET_COMMITMENT_DATE DATE ,
272 API_CALLER_IDENTITY VARCHAR2(15) ,
273 -- Added for CZ Integration (End)
274 RELATIONSHIP_TYPE_CODE VARCHAR2(30) ,
275 DISPLAY_ORDER NUMBER ,
276 POSITION_REFERENCE VARCHAR2(30) ,
277 MANDATORY_FLAG VARCHAR2(1) ,
278 ACTIVE_START_DATE DATE ,
279 ACTIVE_END_DATE DATE ,
280 CONTEXT VARCHAR2(30) ,
281 ATTRIBUTE1 VARCHAR2(150) ,
282 ATTRIBUTE2 VARCHAR2(150) ,
283 ATTRIBUTE3 VARCHAR2(150) ,
284 ATTRIBUTE4 VARCHAR2(150) ,
285 ATTRIBUTE5 VARCHAR2(150) ,
289 ATTRIBUTE9 VARCHAR2(150) ,
286 ATTRIBUTE6 VARCHAR2(150) ,
287 ATTRIBUTE7 VARCHAR2(150) ,
288 ATTRIBUTE8 VARCHAR2(150) ,
290 ATTRIBUTE10 VARCHAR2(150) ,
291 ATTRIBUTE11 VARCHAR2(150) ,
292 ATTRIBUTE12 VARCHAR2(150) ,
293 ATTRIBUTE13 VARCHAR2(150) ,
294 ATTRIBUTE14 VARCHAR2(150) ,
295 ATTRIBUTE15 VARCHAR2(150) ,
296 OBJECT_VERSION_NUMBER NUMBER );
297
298 TYPE txn_ii_rltns_tbl IS TABLE OF txn_ii_rltns_rec INDEX BY BINARY_INTEGER;
299
300
301 -- Name : txn_org_assgn_rec
302 -- Type : type definition, group
303 -- Description : record to hold information about an transaction
304 -- details-org association.
305
306 TYPE txn_org_assgn_rec IS RECORD (
307 TXN_OPERATING_UNIT_ID NUMBER ,
308 TXN_LINE_DETAIL_ID NUMBER ,
309 INSTANCE_OU_ID NUMBER ,
310 OPERATING_UNIT_ID NUMBER ,
311 RELATIONSHIP_TYPE_CODE VARCHAR2(30) ,
312 ACTIVE_START_DATE DATE ,
313 ACTIVE_END_DATE DATE ,
314 PRESERVE_DETAIL_FLAG VARCHAR2(1) ,
315 CONTEXT VARCHAR2(30) ,
316 ATTRIBUTE1 VARCHAR2(150) ,
317 ATTRIBUTE2 VARCHAR2(150) ,
318 ATTRIBUTE3 VARCHAR2(150) ,
319 ATTRIBUTE4 VARCHAR2(150) ,
320 ATTRIBUTE5 VARCHAR2(150) ,
321 ATTRIBUTE6 VARCHAR2(150) ,
322 ATTRIBUTE7 VARCHAR2(150) ,
323 ATTRIBUTE8 VARCHAR2(150) ,
324 ATTRIBUTE9 VARCHAR2(150) ,
325 ATTRIBUTE10 VARCHAR2(150) ,
326 ATTRIBUTE11 VARCHAR2(150) ,
327 ATTRIBUTE12 VARCHAR2(150) ,
328 ATTRIBUTE13 VARCHAR2(150) ,
329 ATTRIBUTE14 VARCHAR2(150) ,
330 ATTRIBUTE15 VARCHAR2(150) ,
331 TXN_LINE_DETAILS_INDEX NUMBER ,
332 OBJECT_VERSION_NUMBER NUMBER );
333
334 TYPE txn_org_assgn_tbl IS TABLE OF txn_org_assgn_rec INDEX BY BINARY_INTEGER;
335
336
337 -- Name : txn_ext_attrib_vals_rec
338 -- Type : type definition, group
339 -- Description : record to hold the values of a transaction detail's
340 -- extended attributes.
341
342 TYPE txn_ext_attrib_vals_rec IS RECORD(
343 TXN_ATTRIB_DETAIL_ID NUMBER ,
344 TXN_LINE_DETAIL_ID NUMBER ,
345 ATTRIB_SOURCE_TABLE VARCHAR2(30) ,
346 ATTRIBUTE_SOURCE_ID NUMBER ,
347 ATTRIBUTE_VALUE VARCHAR2(240) ,
348 -- Added for CZ Integration (Begin)
349 ATTRIBUTE_CODE VARCHAR2(30) ,
350 ATTRIBUTE_LEVEL VARCHAR2(15) ,
351 API_CALLER_IDENTITY VARCHAR2(15) ,
352 -- Added for CZ Integration (End)
353 PROCESS_FLAG VARCHAR2(30) ,
354 ACTIVE_START_DATE DATE ,
355 ACTIVE_END_DATE DATE ,
356 PRESERVE_DETAIL_FLAG VARCHAR2(1) ,
357 CONTEXT VARCHAR2(30) ,
358 ATTRIBUTE1 VARCHAR2(150) ,
359 ATTRIBUTE2 VARCHAR2(150) ,
360 ATTRIBUTE3 VARCHAR2(150) ,
361 ATTRIBUTE4 VARCHAR2(150) ,
362 ATTRIBUTE5 VARCHAR2(150) ,
363 ATTRIBUTE6 VARCHAR2(150) ,
364 ATTRIBUTE7 VARCHAR2(150) ,
365 ATTRIBUTE8 VARCHAR2(150) ,
366 ATTRIBUTE9 VARCHAR2(150) ,
367 ATTRIBUTE10 VARCHAR2(150) ,
368 ATTRIBUTE11 VARCHAR2(150) ,
369 ATTRIBUTE12 VARCHAR2(150) ,
370 ATTRIBUTE13 VARCHAR2(150) ,
371 ATTRIBUTE14 VARCHAR2(150) ,
372 ATTRIBUTE15 VARCHAR2(150) ,
373 TXN_LINE_DETAILS_INDEX NUMBER ,
374 OBJECT_VERSION_NUMBER NUMBER );
375
376
377 TYPE txn_ext_attrib_vals_tbl IS TABLE OF txn_ext_attrib_vals_rec INDEX BY BINARY_INTEGER;
378
379 -- Name : csi_ext_attribs_rec
380 -- Type : type definition, group
381 -- Description : record to hold the item's extended attributes as defined
382 -- in the core installed base (csi_i_extend_attribs)
383
384 TYPE csi_ext_attribs_rec IS RECORD(
385 ATTRIBUTE_ID NUMBER ,
386 ATTRIBUTE_LEVEL VARCHAR2(15) ,
387 MASTER_ORGANIZATION_ID NUMBER ,
388 INVENTORY_ITEM_ID NUMBER ,
389 ITEM_CATEGORY_ID NUMBER ,
390 INSTANCE_ID NUMBER ,
391 ATTRIBUTE_CODE VARCHAR2(30) ,
392 ATTRIBUTE_NAME VARCHAR2(50) ,
393 ATTRIBUTE_CATEGORY VARCHAR2(30) ,
394 DESCRIPTION VARCHAR2(240) ,
395 ACTIVE_START_DATE DATE ,
396 ACTIVE_END_DATE DATE ,
397 CONTEXT VARCHAR2(30) ,
401 ATTRIBUTE4 VARCHAR2(150) ,
398 ATTRIBUTE1 VARCHAR2(150) ,
399 ATTRIBUTE2 VARCHAR2(150) ,
400 ATTRIBUTE3 VARCHAR2(150) ,
402 ATTRIBUTE5 VARCHAR2(150) ,
403 ATTRIBUTE6 VARCHAR2(150) ,
404 ATTRIBUTE7 VARCHAR2(150) ,
405 ATTRIBUTE8 VARCHAR2(150) ,
406 ATTRIBUTE9 VARCHAR2(150) ,
407 ATTRIBUTE10 VARCHAR2(150) ,
408 ATTRIBUTE11 VARCHAR2(150) ,
409 ATTRIBUTE12 VARCHAR2(150) ,
410 ATTRIBUTE13 VARCHAR2(150) ,
411 ATTRIBUTE14 VARCHAR2(150) ,
412 ATTRIBUTE15 VARCHAR2(150) ,
413 OBJECT_VERSION_NUMBER NUMBER );
414
415 TYPE csi_ext_attribs_tbl IS TABLE OF csi_ext_attribs_rec INDEX BY BINARY_INTEGER;
416
417
418 -- Name : csi_ext_attrib_vals_rec
419 -- Description : record to hold the values of an item instances
420 -- extended attribute values.
421
422 TYPE csi_ext_attrib_vals_rec IS RECORD(
423 ATTRIBUTE_VALUE_ID NUMBER ,
424 INSTANCE_ID NUMBER ,
425 ATTRIBUTE_ID NUMBER ,
426 ATTRIBUTE_VALUE VARCHAR2(240) ,
427 ACTIVE_START_DATE DATE ,
428 ACTIVE_END_DATE DATE ,
429 CONTEXT VARCHAR2(30) ,
430 ATTRIBUTE1 VARCHAR2(150) ,
431 ATTRIBUTE2 VARCHAR2(150) ,
432 ATTRIBUTE3 VARCHAR2(150) ,
433 ATTRIBUTE4 VARCHAR2(150) ,
434 ATTRIBUTE5 VARCHAR2(150) ,
435 ATTRIBUTE6 VARCHAR2(150) ,
436 ATTRIBUTE7 VARCHAR2(150) ,
437 ATTRIBUTE8 VARCHAR2(150) ,
438 ATTRIBUTE9 VARCHAR2(150) ,
439 ATTRIBUTE10 VARCHAR2(150) ,
440 ATTRIBUTE11 VARCHAR2(150) ,
441 ATTRIBUTE12 VARCHAR2(150) ,
442 ATTRIBUTE13 VARCHAR2(150) ,
443 ATTRIBUTE14 VARCHAR2(150) ,
444 ATTRIBUTE15 VARCHAR2(150) ,
445 OBJECT_VERSION_NUMBER NUMBER );
446
447 TYPE csi_ext_attrib_vals_tbl IS TABLE OF csi_ext_attrib_vals_rec INDEX BY BINARY_INTEGER;
448
449
450 -- Name : txn_line_detail_ids_rec
451 -- Type : type definition, group
452 -- Description : record to hold internal id values of the txn line details
453
454 TYPE txn_line_detail_ids_rec IS RECORD
455 (
456 TRANSACTION_LINE_ID NUMBER ,
457 TXN_LINE_DETAIL_ID NUMBER
458 );
459
460 TYPE txn_line_detail_ids_tbl IS TABLE OF txn_line_detail_ids_rec INDEX BY BINARY_INTEGER;
461
462 -- Name : txn_party_ids_rec
463 -- Type : type definition, group
464 -- Description : record to hold internal id values of the instance
465 -- party relationship
466
467 TYPE txn_party_ids_rec IS RECORD
468 (
469 TXN_PARTY_DETAIL_ID NUMBER ,
470 TXN_LINE_DETAIL_ID NUMBER
471 );
472
473 TYPE txn_party_ids_tbl IS TABLE OF txn_party_ids_rec INDEX BY BINARY_INTEGER;
474
475
476 -- Name : txn_pty_acct_ids_rec
477 -- Type : type definition, group
478 -- Description : record to hold internal id values of the instance
479 -- accounts
480
481 TYPE txn_pty_acct_ids_rec IS RECORD
482 (
483 TXN_ACCOUNT_DETAIL_ID NUMBER ,
484 TXN_PARTY_DETAIL_ID NUMBER ,
485 TXN_LINE_DETAIL_ID NUMBER
486 );
487
488 TYPE txn_pty_acct_ids_tbl IS TABLE OF txn_pty_acct_ids_rec INDEX BY BINARY_INTEGER;
489
490
491 -- Name : txn_ii_rltns_ids_rec
492 -- Type : type definition, group
493 -- Description : record to hold internal id values of the
494 -- instance-instance relationships
495
496 TYPE txn_ii_rltns_ids_rec IS RECORD
497 (
498 TXN_RELATIONSHIP_ID NUMBER ,
499 TRANSACTION_LINE_ID NUMBER
500 );
501
502 TYPE txn_ii_rltns_ids_tbl IS TABLE OF txn_ii_rltns_ids_rec INDEX BY BINARY_INTEGER;
503
504
505 -- Name : txn_org_assgn_ids_rec
506 -- Type : type definition, group
507 -- Description : record to hold internal id values of the instance
508 -- org assignments
509
510 TYPE txn_org_assgn_ids_rec IS RECORD
511 (
512 TXN_OPERATING_UNIT_ID NUMBER ,
513 TXN_LINE_DETAIL_ID NUMBER
514 );
515
516 TYPE txn_org_assgn_ids_tbl IS TABLE OF txn_org_assgn_ids_rec INDEX BY BINARY_INTEGER;
517
518
519 -- Name : txn_ext_attrib_ids_rec
520 -- Type : type definition, group
521 -- Description : record to hold internal id values of the extended
522 -- attributes
523
524 TYPE txn_ext_attrib_ids_rec IS RECORD
525 (
526 TXN_ATTRIB_DETAIL_ID NUMBER ,
527 TXN_LINE_DETAIL_ID NUMBER
528 );
529
530 TYPE txn_ext_attrib_ids_tbl IS TABLE OF txn_ext_attrib_ids_rec INDEX BY BINARY_INTEGER;
531
532 -- Name : txn_line_query_rec
533 -- Description : This record structure holds the possible query criteria
537 TRANSACTION_LINE_ID NUMBER ,
534 -- for transaction line
535
536 TYPE txn_line_query_rec IS RECORD(
538 ---Added (Start) for m-to-m enhancements
539 SOURCE_TRANSACTION_TYPE_ID NUMBER ,
540 SOURCE_TXN_HEADER_ID NUMBER ,
541 ---Added (End) for m-to-m enhancements
542 SOURCE_TRANSACTION_ID NUMBER ,
543 SOURCE_TRANSACTION_TABLE VARCHAR2(30) ,
544 -- Added for CZ Integration (Begin)
545 CONFIG_SESSION_HDR_ID NUMBER ,
546 CONFIG_SESSION_REV_NUM NUMBER ,
547 CONFIG_SESSION_ITEM_ID NUMBER ,
548 API_CALLER_IDENTITY VARCHAR2(15) ,
549 -- Added for CZ Integration (End)
550 PROCESSING_STATUS VARCHAR2(30) ,
551 ERROR_CODE VARCHAR2(240) ,
552 ERROR_EXPLANATION VARCHAR2(240) );
553
554
555 -- Name : txn_line_detail_query_rec
556 -- Description : This record structure holds the possible query criteria
557 -- for transaction line detail
558
559 TYPE txn_line_detail_query_rec IS RECORD(
560 TRANSACTION_LINE_ID NUMBER ,
561 TXN_LINE_DETAIL_ID NUMBER ,
562 SUB_TYPE_ID NUMBER ,
563 CSI_TRANSACTION_ID NUMBER ,
564 SOURCE_TRANSACTION_FLAG VARCHAR2(1) ,
565 INSTANCE_ID NUMBER ,
566 INSTANCE_EXISTS_FLAG VARCHAR2(1) ,
567 CSI_SYSTEM_ID NUMBER ,
568 TRANSACTION_SYSTEM_ID NUMBER ,
569 INV_ORGANIZATION_ID NUMBER ,
570 INVENTORY_ITEM_ID NUMBER ,
571 INVENTORY_REVISION VARCHAR2(3) ,
572 SERIAL_NUMBER VARCHAR2(30) ,
573 MFG_SERIAL_NUMBER_FLAG VARCHAR2(1) ,
574 LOT_NUMBER VARCHAR2(30) ,
575 LOCATION_TYPE_CODE VARCHAR2(30) ,
576 EXTERNAL_REFERENCE VARCHAR2(30) ,
577 RETURN_BY_DATE DATE ,
578 REFERENCE_SOURCE_ID NUMBER ,
579 PROCESSING_STATUS VARCHAR2(30) ,
580 ERROR_CODE VARCHAR2(240) ,
581 ERROR_EXPLANATION VARCHAR2(240) );
582
583 -----------------------------------------------------------------------
584 -----------------------------------------------------------------------
585
586 TYPE config_query_record IS RECORD(
587 config_header_id NUMBER ,
588 config_revision_number NUMBER );
589
590 TYPE config_query_table IS TABLE OF config_query_record INDEX BY BINARY_INTEGER;
591
592 TYPE config_rec IS RECORD
593 (
594 source_application_id NUMBER,
595 source_txn_header_ref VARCHAR2(30),
596 source_txn_line_ref1 VARCHAR2(30),
597 source_txn_line_ref2 VARCHAR2(30),
598 source_txn_line_ref3 VARCHAR2(30),
599 instance_id NUMBER,
600 lock_id NUMBER,
601 lock_status NUMBER,
602 config_inst_hdr_id NUMBER,
603 config_inst_item_id NUMBER,
604 config_inst_rev_num NUMBER
605 );
606
607 m_config_rec config_rec;
608
609 TYPE config_pair_record IS RECORD(
610 subject_header_id number ,
611 subject_revision_number number ,
612 subject_item_id number ,
613 object_header_id number ,
614 object_revision_number number ,
615 object_item_id number ,
616 root_header_id number ,
617 root_revision_number number ,
618 root_item_id number ,
619 source_application_id number ,
620 source_txn_header_ref varchar2(30),
621 source_txn_line_ref1 varchar2(30),
622 source_txn_line_ref2 varchar2(30),
623 source_txn_line_ref3 varchar2(30),
624 lock_id number ,
625 lock_status number);
626
627 TYPE config_pair_table IS TABLE OF config_pair_record INDEX BY BINARY_INTEGER;
628
629 -- used for outputing in generate_config_trees and add_to_config_tree procedures
630 TYPE config_model_rec_type IS RECORD
631 (
632 inventory_item_id NUMBER,
633 organization_id NUMBER,
634 config_hdr_id NUMBER,
635 config_rev_nbr NUMBER,
636 config_item_id NUMBER
637 );
638 TYPE config_model_tbl_type IS TABLE OF config_model_rec_type INDEX BY BINARY_INTEGER;
639
640 -----------------------------------------------------------------------
641 -----------------------------------------------------------------------
642
643 m_txn_line_rec txn_line_rec;
644 m_txn_line_detail_tbl txn_line_detail_tbl;
645 m_txn_ext_attrib_vals_tbl txn_ext_attrib_vals_tbl;
646 m_txn_ii_rltns_tbl txn_ii_rltns_tbl;
647
648 m_config_query_table config_query_table;
649 m_config_pair_table config_pair_table;
650 m_config_rev_number NUMBER;
651
652 m_msg_count NUMBER;
653 m_msg_data VARCHAR2(2000);
654 m_return_status VARCHAR2(255);
655 m_return_message VARCHAR2(2000);
656
657 DEBUG_OUTPUT CONSTANT VARCHAR2(255):='OUTPUT';
658 DEBUG_DB CONSTANT VARCHAR2(255):='DATABASE';
659
660 DEBUG_MODE VARCHAR2(255):=DEBUG_OUTPUT;
664 TYPE int_array_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
661 ERROR_CODE VARCHAR2(50):='0000';
662
663
665 TYPE int_array_tbl_type_idx_vc2 IS TABLE OF NUMBER INDEX BY VARCHAR2(15);-- Bug 6892148;
666 TYPE char_array_tbl_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
667 TYPE char_array_tbl_type_idx_vc2 IS TABLE OF VARCHAR2(2000) INDEX BY VARCHAR2(15);-- Bug 6892148;
668 TYPE long_char_array_tbl_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
669 TYPE date_array_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
670
671 m_config_hdr_tbl int_array_tbl_type;
672 m_config_rev_nbr_tbl int_array_tbl_type;
673 m_config_item_tbl int_array_tbl_type;
674 m_attribute_category_tbl long_char_array_tbl_type;
675 m_attribute_name_tbl long_char_array_tbl_type;
676 m_attribute_value_tbl long_char_array_tbl_type;
677 m_location_id_tbl int_array_tbl_type;
678 m_instance_description_tbl long_char_array_tbl_type;
679 m_csi_rev_nbr_tbl int_array_tbl_type_idx_vc2;-- Bug 6892148;
680
681 m_COUNTER NUMBER;
682
683 --
684 -- define transaction type for CZ/IB transactions
685 -- value has been suggested by IB team
686 --
687 CZ_TRANSACTION_TYPE_ID CONSTANT INTEGER := 401;
688
689 INCOMPLETE_CONFIG_STATUS CONSTANT VARCHAR2(50) := '1';
690 COMPLETE_CONFIG_STATUS CONSTANT VARCHAR2(50) := '2';
691
692 --
693 -- define types of relationships for using in IB
694 --
695 CONNECTED_TO_RELATIONSHIP CONSTANT VARCHAR2(50) := 'CONNECTED-TO';
696 COMPONENT_OF_RELATIONSHIP CONSTANT VARCHAR2(50) := 'COMPONENT-OF';
697 --
698 -- define source transaction table or subschema
699 -- for CZ/IB transactions
700 --
701 CZ_IB_TRANSACTION_TABLE CONSTANT VARCHAR2(50) := 'CONFIGURATOR';
702 DISCONTINUE_ACTION_TYPE CONSTANT NUMBER:=4;
703
704 YES_FLAG CONSTANT VARCHAR2(1) := '1';
705 NO_FLAG CONSTANT VARCHAR2(1) := '0';
706
707 --
708 -- global variable ( session level ) that stores value of p_Effective_Date parameter
709 --
710 m_EFFECTIVE_DATE DATE;
711
712 --
713 -- global variable to store a value from APPS profile "CZ_IB_AUTO_EXPIRATION"
714 --
715 m_CZ_IB_AUTO_EXPIRATION VARCHAR2(255):='Y';
716
717 --
718 -- CZ_DB_LOGS.run_id
719 --
720 m_RUN_ID NUMBER;
721
722
723 --
724 -- delete IB data
725 --
726 PROCEDURE remove_IB_Config
727 (
728 p_session_config_hdr_id IN NUMBER DEFAULT NULL,
729 p_session_config_rev_nbr IN NUMBER DEFAULT NULL,
730 p_instance_hdr_id IN NUMBER DEFAULT NULL,
731 p_instance_rev_nbr IN NUMBER DEFAULT NULL,
732 p_instance_item_id IN NUMBER DEFAULT NULL,
733 x_run_id OUT NOCOPY NUMBER
734 );
735
736
737 --
738 -- INSERT/UPDATE data IN IB Repository based ON the changes OF configuration items IN CZ --
739 --
740 PROCEDURE Update_Instances
741 (
742 p_config_instance_tbl IN SYSTEM.cz_config_instance_tbl_type,
743 p_effective_date IN DATE,
744 p_txn_type_id IN NUMBER,
745 x_run_id OUT NOCOPY NUMBER
746 );
747
748 /**
749 * The method will UPDATE the status OF the IB instance
750 * <=> CSI_T_TRANSACTION_LINES. CONFIG_VALID_STATUS / CSI_ITEM_INSTANCES. CONFIG_VALID_STATUS TO be INVALID
751 * IF either the CZ_CONFIG_HDRS.config_status field IS SET TO INCOMPLETE OR
752 * the CZ_CONFIG_HDRS.has_failures field IS SET TO TRUE, otherwise, it will be SET TO VALID
753 */
754
755 PROCEDURE Update_Instances_Status
756 (
757 p_config_instance_tbl IN SYSTEM.cz_config_instance_tbl_type,
758 x_run_id OUT NOCOPY NUMBER
759 );
760
761 /**
762 * RETURN ARRAY OF attributes OF config items FROM subtree that starts WITH
763 * config item (p_config_hdr_id,p_config_rev_nbr,p_config_item_id)
764 */
765 PROCEDURE Synchronize_Attributes
766 (
767 p_config_hdr_id IN NUMBER,
768 p_config_rev_nbr IN NUMBER,
769 p_install_rev_nbr IN NUMBER,
770 p_config_item_id IN NUMBER,
771 x_config_attribute_tbl OUT NOCOPY SYSTEM.cz_config_attribute_tbl_type,
772 x_txn_params_tbl OUT NOCOPY SYSTEM.cz_txn_params_tbl_type,
773 x_run_id OUT NOCOPY INTEGER
774 );
775
776 /**
777 * check_CZIB_Item PROCEDURE sets x_in_txn TO '1' IF config item
778 * (p_config_hdr_id, p_config_rev_nbr, p_config_item_id)
779 * EXISTS IN IB Transactions subschema
780 * AND sets x_in_inst TO '1' IF config item
781 * (p_config_hdr_id, p_config_rev_nbr, p_config_item_id)
782 * EXISTS IN IB Instances subschema,
783 * otherwise x_in_txn='0' AND x_in_inst='0'
784 */
785
786 PROCEDURE check_CZIB_Item
787 (
788 p_config_hdr_id IN NUMBER,
789 p_config_rev_nbr IN NUMBER,
790 p_config_item_id IN NUMBER,
791 x_in_txn OUT NOCOPY VARCHAR2,
792 x_in_inst OUT NOCOPY VARCHAR2
793 );
794
795 /**
796 * wrapper FOR CZ_IB_WRAPPERS.Get_Configuration_Revision() PROCEDURE
797 *
798 * 1. IF LEVEL IS "Installed", retrieve the "Revision Number" FROM CSI_Item_Instances
799 * FOR the Config_Header_Id passed.
800 * 2. IF LEVEL IS NULL OR "PENDING", THEN
804 * c. IF a revision IS FOUND THEN RETURN Revision AND the LEVEL AS PENDING,
801 * a. Retrive the "Revision Number" FROM Csi_Item_Instances FOR the given Config_Header_Id.
802 * b. CHECK IN Transcation Details, IF there IS a revision ON TRANSACTION details
803 * FOR the config_Header_ID, which IS NOT a base revision ON ANY other line IN TRANSACTION details.
805 * otherwise RETURN the revision FROM the Csi_Item_Instances AND LEVEL AS INSTALLED.
806 */
807 PROCEDURE Get_Configuration_Revision
808 (
809 p_Config_Header_Id IN NUMBER,
810 p_target_commitment_date IN DATE,
811 px_Instance_Level IN OUT NOCOPY VARCHAR2,
812 x_config_rec OUT NOCOPY SYSTEM.CZ_CONFIG_REC,
813 x_run_id OUT NOCOPY NUMBER
814 );
815
816 /**
817 * wrapper FOR CSI_CZ_INT.Get_Connected_Configurations() PROCEDURE
818 */
819 PROCEDURE Get_Connected_Configurations
820 (
821 p_Config_Query_Table IN SYSTEM.cz_config_query_table,
822 p_Instance_Level IN VARCHAR2,
823 x_Config_Pair_Table OUT NOCOPY SYSTEM.cz_config_pair_table,
824 x_run_id OUT NOCOPY NUMBER
825 );
826
827 /**
828 * this PROCEDURE IS used IN order TO CREATE IB data FOR
829 * the copied model
830 */
831 PROCEDURE clone_IB_Data
832 (
833 p_config_hdr_id IN NUMBER,
834 p_config_rev_nbr IN NUMBER,
835 x_run_id OUT NOCOPY NUMBER
836 );
837
838 /**
839 * test Update_Instances() PROCEDURE
840 */
841 PROCEDURE Test_Update_Instances
842 (
843 p_instance_hdr_id NUMBER,
844 p_config_item_id NUMBER,
845 p_old_rev_nbr NUMBER,
846 p_new_rev_nbr NUMBER
847 );
848
849 PROCEDURE Test_Connected_Configurations;
850
851 PROCEDURE Test_Configuration_Revision
852 (
853 p_config_hdr_id IN NUMBER
854 );
855
856 PROCEDURE LOG_REPORT
857 (p_run_id IN VARCHAR2,
858 p_error_message IN VARCHAR2,
859 p_count IN NUMBER);
860
861 END CZ_IB_TRANSACTIONS;
[Home] [Help]