1 PACKAGE CSI_UTILITY_GRP AS
2 /* $Header: csigutls.pls 120.5 2007/02/08 23:12:12 jpwilson ship $ */
3 --
4 --
5 g_pkg_name VARCHAR2(30) := 'CSI_UTILITY_GRP';
6 --
7
8 TYPE T_NUM is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
9 TYPE T_V3 is TABLE OF VARCHAR(03) INDEX BY BINARY_INTEGER;
10
11 TYPE OM_TXN_INFO_REC IS RECORD
12 ( LINE_ID NUMBER := FND_API.G_MISS_NUM,
13 SHIP_FROM_ORG_ID NUMBER := FND_API.G_MISS_NUM,
14 INV_VLD_ORGANIZATION_ID NUMBER := FND_API.G_MISS_NUM,
15 HEADER_ID NUMBER := FND_API.G_MISS_NUM,
16 REQUEST_DATE DATE := FND_API.G_MISS_DATE,
17 SCHEDULE_SHIP_DATE DATE := FND_API.G_MISS_DATE,
18 INVENTORY_ITEM_ID NUMBER := FND_API.G_MISS_NUM);
19
20 TYPE OM_TXN_INFO_TBL is TABLE OF OM_TXN_INFO_REC INDEX BY BINARY_INTEGER;
21
22 -- This Function can be used to check if Oracle Installed Base
23 -- Product is Installed and Active at an Implementation. This
24 -- would check for a freeze_flag in Install Parameters.
25 --
26 FUNCTION IB_ACTIVE RETURN BOOLEAN;
27 PRAGMA RESTRICT_REFERENCES( ib_active, WNDS, WNPS);
28 --
29 -- This Function can be used to check if Oracle Installed Base
30 -- Product is Installed and Active at an Implementation. This
31 -- would check for a freeze_flag in Install Parameters.
32 -- This function returns a VARCHAR2 in the form 'Y' or 'N'
33 -- and can be used in a SQL statement in the predicate.
34 --
35 FUNCTION IB_ACTIVE_FLAG RETURN VARCHAR2;
36 PRAGMA RESTRICT_REFERENCES( ib_active_flag, WNDS, WNPS);
37
38
39 --
40 -- This function returns the version of the Installed Base
41 -- This would be 1150 when it is on pre 1156
42 --
43 FUNCTION IB_VERSION RETURN NUMBER;
44 PRAGMA RESTRICT_REFERENCES( ib_version, WNDS, WNPS);
45
46 --
47 -- This procedure checks if the installation parameters are in
48 -- a frozen state and populates an error message in the message queue
49 -- if not frozen. It also raises the fnd_api.g_exc_error exception
50 --
51 PROCEDURE check_ib_active;
52
53 TYPE config_session_key IS RECORD(
54 session_hdr_id number,
55 session_rev_num number,
56 session_item_id number);
57
58 TYPE config_session_keys IS TABLE OF config_session_key INDEX BY BINARY_INTEGER;
59
60 PROCEDURE get_config_key_for_om_line(
61 p_line_id IN number,
62 x_config_session_key OUT NOCOPY config_session_key,
63 x_return_status OUT NOCOPY varchar2,
64 x_return_message OUT NOCOPY varchar2);
65
66 TYPE config_instance_key IS RECORD(
67 inst_hdr_id number,
68 inst_rev_num number,
69 inst_item_id number,
70 inst_baseline_rev_num number);
71
72 TYPE config_instance_keys IS TABLE OF config_instance_key INDEX BY BINARY_INTEGER;
73
74 PROCEDURE get_config_inst_valid_status(
75 p_instance_key IN config_instance_key,
76 x_config_valid_status OUT NOCOPY varchar2,
77 x_return_status OUT NOCOPY varchar2,
78 x_return_message OUT NOCOPY varchar2);
79
80 FUNCTION is_network_component(
81 p_order_line_id IN number,
82 x_return_status OUT NOCOPY varchar2)
83 RETURN boolean;
84
85 PROCEDURE vld_item_ctrl_changes (
86 p_api_version IN NUMBER
87 ,p_commit IN VARCHAR2 := fnd_api.g_false
88 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
89 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
90 ,p_inventory_item_id IN NUMBER
91 ,p_organization_id IN NUMBER
92 ,p_item_attr_name IN VARCHAR2
93 ,p_new_item_attr_value IN VARCHAR2
94 ,p_old_item_attr_value IN VARCHAR2
95 ,x_return_status OUT NOCOPY VARCHAR2
96 ,x_msg_count OUT NOCOPY NUMBER
97 ,x_msg_data OUT NOCOPY VARCHAR2);
98
99 FUNCTION vld_exist_txn_errors (p_item_id IN NUMBER) RETURN BOOLEAN;
100
101 FUNCTION vld_exist_mtl_iface_recs (p_item_id IN NUMBER,
102 p_org_id IN NUMBER) RETURN BOOLEAN;
103
104 FUNCTION vld_exist_mtl_temp_recs (p_item_id IN NUMBER,
105 p_org_id IN NUMBER) RETURN BOOLEAN;
106
107 FUNCTION vld_exist_sfm_events(p_item_id IN NUMBER) RETURN BOOLEAN;
108
109 FUNCTION vld_active_ib_inst(p_item_id IN NUMBER) RETURN BOOLEAN;
110
111 /********** Start New Functions for Inventory MACD validations **********/
112
113 -- check_inv_serial_cz_keys will call the other 3 functions internally
114 -- and will return either Y or N
115 --
116 -- N = Serial Number is NOT in a MACD Configuration
117 --
118 -- Y = Serial number IS in a MACD Configuration
119 --
120 --
121 FUNCTION check_inv_serial_cz_keys (p_inventory_item_id IN NUMBER,
122 p_organization_id IN NUMBER,
123 p_serial_number IN VARCHAR2) RETURN VARCHAR2;
124
125
126 FUNCTION check_inv_inst_cz_keys (p_inventory_item_id IN NUMBER,
127 p_organization_id IN NUMBER,
128 p_serial_number IN VARCHAR2) RETURN BOOLEAN;
129
130 FUNCTION check_inv_error_cz_keys (p_inventory_item_id IN NUMBER,
131 p_organization_id IN NUMBER,
132 p_serial_number IN VARCHAR2) RETURN BOOLEAN;
133
134 FUNCTION check_inv_sfm_cz_keys (p_inventory_item_id IN NUMBER,
135 p_organization_id IN NUMBER,
136 p_serial_number IN VARCHAR2) RETURN BOOLEAN;
137
138
139 /********** End New Functions for Inventory MACD validations **********/
140
141
142 -- Name : txn_oks_rec
143 -- Description : Holds the Table of OKS transaction types alongwith the source entity ID
144 -- Package name : csi_item_instance_grp
145 -- Type : rec type definition, Group
146 -- Description : This holds the data that is passed on by OKS(Service Contracts)
147 -- for both Mass Update and IB-OKS Usability R12 functionalities
148
149 TYPE txn_oks_rec IS RECORD
150 (
151 transaction_type T_V3,
152 batch_id NUMBER := NULL,
153 instance_id NUMBER := NULL
154 );
155
156 -- Name : txn_inst_rec
157 -- Description : Holds the OKS transaction type with the table of item instances impacted for it
158 -- Package name : csi_item_instance_grp
159 -- Type : Table and rec type definition, Group
160 -- Description : This holds the list of item instances that is passed on to OKS(Service Contracts)
161 -- for a particular OKS transaction type/operation.It is used by both Mass Update
162 -- and IB-OKS Usability R12 functionalities
163
164 TYPE txn_inst_rec IS RECORD
165 (
166 transaction_type VARCHAR2(3) := NULL,
167 instance_tbl T_NUM
168 );
169
170
171 TYPE txn_inst_tbl IS TABLE OF txn_inst_rec INDEX BY BINARY_INTEGER;
172
173 -- Name : get_impacted_item_instances
174 -- Description : This API returns the set of item instances that are impacted for a given OKS
175 -- related operation (Transaction type) that is performed through that source transaction
176 -- and this transaction could be a Mass update Batch OR item instance UI update etc
177 -- and it reads the inheritance rules of IB for the parent-child relationship to
178 -- return back the result set.Current usage is by OKS (Service contracts) and in the
179 -- context of the Mass Update and IB-OKS Usability R12 functionalities
180
181 PROCEDURE get_impacted_item_instances
182 (
183 p_api_version IN NUMBER
184 ,p_commit IN VARCHAR2 := fnd_api.g_false
185 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
186 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
187 ,x_txn_inst_tbl OUT NOCOPY txn_inst_tbl
188 ,p_txn_oks_rec IN txn_oks_rec
189 ,x_return_status OUT NOCOPY VARCHAR2
190 ,x_msg_count OUT NOCOPY NUMBER
191 ,x_msg_data OUT NOCOPY VARCHAR2
192 );
193
194
195 PROCEDURE get_instances (p_txn_oks_rec IN TXN_OKS_REC,
196 x_txn_inst_tbl OUT NOCOPY TXN_INST_TBL,
197 x_return_status OUT NOCOPY VARCHAR2,
198 x_msg_count OUT NOCOPY NUMBER,
199 x_msg_data OUT NOCOPY VARCHAR2);
200
201
202 -- Procedures for INV/OM Transaction Data Purge
203 PROCEDURE inv_txn_data_purge(
204 p_inv_period_from_date IN DATE,
205 p_inv_period_to_date IN DATE,
206 p_organization_id IN NUMBER,
207 x_return_status OUT NOCOPY varchar2,
208 x_return_message OUT NOCOPY varchar2);
209
210 PROCEDURE om_txn_data_purge(
211 p_om_txn_info IN csi_utility_grp.om_txn_info_tbl,
212 x_return_status OUT NOCOPY varchar2,
213 x_return_message OUT NOCOPY varchar2);
214
215 PROCEDURE purge_txn_detail_tables (
216 errbuf OUT NOCOPY VARCHAR2
217 ,retcode OUT NOCOPY NUMBER);
218
219 END CSI_UTILITY_GRP;
[Home] [Help]