1 PACKAGE CSI_UTILITY_GRP AUTHID CURRENT_USER AS
2 /* $Header: csigutls.pls 120.6 2010/05/12 07:26:19 smuhamme 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 --begin bug no. 7135052
61 TYPE install_address_rec IS RECORD(
62 party_site_number VARCHAR2(30) ,
63 party_name VARCHAR2(360) ,
64 party_number VARCHAR2(60) ,
65 party_id NUMBER ,
66 location_id NUMBER ,
67 location_type_code VARCHAR2(60) ,
68 address1 VARCHAR2(240) ,
69 address2 VARCHAR2(240) ,
70 address3 VARCHAR2(240) ,
71 address4 VARCHAR2(240) ,
72 city VARCHAR2(60) ,
73 state VARCHAR2(60) ,
74 postal_code VARCHAR2(60) ,
75 country VARCHAR2(60) ,
76 address_style VARCHAR2(30) ,
77 address_lines_phonetic VARCHAR2(560) ,
78 county VARCHAR2(60) ,
79 province VARCHAR2(60)
80 );
81 --end bug no. 7135052
82
83 PROCEDURE get_config_key_for_om_line(
84 p_line_id IN number,
85 x_config_session_key OUT NOCOPY config_session_key,
86 x_return_status OUT NOCOPY varchar2,
87 x_return_message OUT NOCOPY varchar2);
88
89 TYPE config_instance_key IS RECORD(
90 inst_hdr_id number,
91 inst_rev_num number,
92 inst_item_id number,
93 inst_baseline_rev_num number);
94
95 TYPE config_instance_keys IS TABLE OF config_instance_key INDEX BY BINARY_INTEGER;
96
97 PROCEDURE get_config_inst_valid_status(
98 p_instance_key IN config_instance_key,
99 x_config_valid_status OUT NOCOPY varchar2,
100 x_return_status OUT NOCOPY varchar2,
101 x_return_message OUT NOCOPY varchar2);
102
103 FUNCTION is_network_component(
104 p_order_line_id IN number,
105 x_return_status OUT NOCOPY varchar2)
106 RETURN boolean;
107
108 PROCEDURE vld_item_ctrl_changes (
109 p_api_version IN NUMBER
110 ,p_commit IN VARCHAR2 := fnd_api.g_false
111 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
112 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
113 ,p_inventory_item_id IN NUMBER
114 ,p_organization_id IN NUMBER
115 ,p_item_attr_name IN VARCHAR2
116 ,p_new_item_attr_value IN VARCHAR2
117 ,p_old_item_attr_value IN VARCHAR2
118 ,x_return_status OUT NOCOPY VARCHAR2
119 ,x_msg_count OUT NOCOPY NUMBER
120 ,x_msg_data OUT NOCOPY VARCHAR2);
121
122 FUNCTION vld_exist_txn_errors (p_item_id IN NUMBER) RETURN BOOLEAN;
123
124 FUNCTION vld_exist_mtl_iface_recs (p_item_id IN NUMBER,
125 p_org_id IN NUMBER) RETURN BOOLEAN;
126
127 FUNCTION vld_exist_mtl_temp_recs (p_item_id IN NUMBER,
128 p_org_id IN NUMBER) RETURN BOOLEAN;
129
130 FUNCTION vld_exist_sfm_events(p_item_id IN NUMBER) RETURN BOOLEAN;
131
132 FUNCTION vld_active_ib_inst(p_item_id IN NUMBER) RETURN BOOLEAN;
133
134 /********** Start New Functions for Inventory MACD validations **********/
135
136 -- check_inv_serial_cz_keys will call the other 3 functions internally
137 -- and will return either Y or N
138 --
139 -- N = Serial Number is NOT in a MACD Configuration
140 --
141 -- Y = Serial number IS in a MACD Configuration
142 --
143 --
144 FUNCTION check_inv_serial_cz_keys (p_inventory_item_id IN NUMBER,
145 p_organization_id IN NUMBER,
146 p_serial_number IN VARCHAR2) RETURN VARCHAR2;
147
148
149 FUNCTION check_inv_inst_cz_keys (p_inventory_item_id IN NUMBER,
150 p_organization_id IN NUMBER,
151 p_serial_number IN VARCHAR2) RETURN BOOLEAN;
152
153 FUNCTION check_inv_error_cz_keys (p_inventory_item_id IN NUMBER,
154 p_organization_id IN NUMBER,
155 p_serial_number IN VARCHAR2) RETURN BOOLEAN;
156
157 FUNCTION check_inv_sfm_cz_keys (p_inventory_item_id IN NUMBER,
158 p_organization_id IN NUMBER,
159 p_serial_number IN VARCHAR2) RETURN BOOLEAN;
160
161
162 /********** End New Functions for Inventory MACD validations **********/
163
164
165 -- Name : txn_oks_rec
166 -- Description : Holds the Table of OKS transaction types alongwith the source entity ID
167 -- Package name : csi_item_instance_grp
168 -- Type : rec type definition, Group
169 -- Description : This holds the data that is passed on by OKS(Service Contracts)
170 -- for both Mass Update and IB-OKS Usability R12 functionalities
171
172 TYPE txn_oks_rec IS RECORD
173 (
174 transaction_type T_V3,
175 batch_id NUMBER := NULL,
176 instance_id NUMBER := NULL
177 );
178
179 -- Name : txn_inst_rec
180 -- Description : Holds the OKS transaction type with the table of item instances impacted for it
181 -- Package name : csi_item_instance_grp
182 -- Type : Table and rec type definition, Group
183 -- Description : This holds the list of item instances that is passed on to OKS(Service Contracts)
184 -- for a particular OKS transaction type/operation.It is used by both Mass Update
185 -- and IB-OKS Usability R12 functionalities
186
187 TYPE txn_inst_rec IS RECORD
188 (
189 transaction_type VARCHAR2(3) := NULL,
190 instance_tbl T_NUM
191 );
192
193
194 TYPE txn_inst_tbl IS TABLE OF txn_inst_rec INDEX BY BINARY_INTEGER;
195
196 -- Name : get_impacted_item_instances
197 -- Description : This API returns the set of item instances that are impacted for a given OKS
198 -- related operation (Transaction type) that is performed through that source transaction
199 -- and this transaction could be a Mass update Batch OR item instance UI update etc
200 -- and it reads the inheritance rules of IB for the parent-child relationship to
201 -- return back the result set.Current usage is by OKS (Service contracts) and in the
202 -- context of the Mass Update and IB-OKS Usability R12 functionalities
203
204 PROCEDURE get_impacted_item_instances
205 (
206 p_api_version IN NUMBER
207 ,p_commit IN VARCHAR2 := fnd_api.g_false
208 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
209 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
210 ,x_txn_inst_tbl OUT NOCOPY txn_inst_tbl
211 ,p_txn_oks_rec IN txn_oks_rec
212 ,x_return_status OUT NOCOPY VARCHAR2
213 ,x_msg_count OUT NOCOPY NUMBER
214 ,x_msg_data OUT NOCOPY VARCHAR2
215 );
216
217
218 PROCEDURE get_instances (p_txn_oks_rec IN TXN_OKS_REC,
219 x_txn_inst_tbl OUT NOCOPY TXN_INST_TBL,
220 x_return_status OUT NOCOPY VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 x_msg_data OUT NOCOPY VARCHAR2);
223
224
225 -- Procedures for INV/OM Transaction Data Purge
226 PROCEDURE inv_txn_data_purge(
227 p_inv_period_from_date IN DATE,
228 p_inv_period_to_date IN DATE,
229 p_organization_id IN NUMBER,
230 x_return_status OUT NOCOPY varchar2,
231 x_return_message OUT NOCOPY varchar2);
232
233 PROCEDURE om_txn_data_purge(
234 p_om_txn_info IN csi_utility_grp.om_txn_info_tbl,
235 x_return_status OUT NOCOPY varchar2,
236 x_return_message OUT NOCOPY varchar2);
237
238 PROCEDURE purge_txn_detail_tables (
239 errbuf OUT NOCOPY VARCHAR2
240 ,retcode OUT NOCOPY NUMBER);
241
242 --start bug no. 7135052, new api added to get install location
243 PROCEDURE get_instance_install_location (
244 p_api_version IN NUMBER
245 ,p_commit IN VARCHAR2 := fnd_api.g_false
246 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
247 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
248 ,p_instance_id IN NUMBER
249 ,x_install_address_rec OUT NOCOPY install_address_rec
250 ,x_return_status OUT NOCOPY VARCHAR2
251 ,x_msg_count OUT NOCOPY NUMBER
252 ,x_msg_data OUT NOCOPY VARCHAR2
253 );
254 --end bug n. 7135052
255
256 END CSI_UTILITY_GRP;
[Home] [Help]