1 PACKAGE csi_utl_pkg as
2 /* $Header: csiutls.pls 120.4 2006/03/20 00:59:22 srsarava noship $ */
3
4 /*----------------------------------------------------------*/
5 /* Package Name : csi_utl_pkg */
6 /* Description : used by the order shipment interface */
7 /* to do the validations */
8 /*----------------------------------------------------------*/
9
10 TYPE txn_line_dtl_rec is RECORD
11 ( txn_line_detail_id NUMBER := FND_API.G_MISS_NUM );
12
13 TYPE txn_line_dtl_tbl is TABLE OF txn_line_dtl_rec INDEX BY BINARY_INTEGER;
14
15 TYPE txn_ps_rec is RECORD(
16 txn_line_detail_id number := fnd_api.g_miss_num,
17 transaction_line_id number := fnd_api.g_miss_num,
18 processed_flag varchar2(1) := fnd_api.g_miss_char,
19 quantity number := fnd_api.g_miss_num,
20 quantity_ratio number := fnd_api.g_miss_num,
21 quantity_remaining number := fnd_api.g_miss_num);
22
23 TYPE txn_ps_tbl IS TABLE OF txn_ps_rec INDEX BY binary_integer;
24
25
26 FUNCTION get_curr_party(
27 p_instance_id IN NUMBER,
28 p_rel_type_code IN VARCHAR2) RETURN NUMBER ;
29
30 PROCEDURE get_ext_attribs(
31 p_instance_id IN NUMBER ,
32 p_attribute_id IN NUMBER ,
33 x_attribute_value_id OUT NOCOPY NUMBER,
34 x_obj_version_number OUT NOCOPY NUMBER,
35 x_return_status OUT NOCOPY VARCHAR2);
36
37 PROCEDURE get_org_assign(
38 p_instance_id IN NUMBER ,
39 p_operating_unit_id IN NUMBER ,
40 p_rel_type_code IN VARCHAR2,
41 x_instance_ou_id OUT NOCOPY NUMBER,
42 x_obj_version_number OUT NOCOPY NUMBER,
43 x_return_status OUT NOCOPY VARCHAR2);
44
45 PROCEDURE get_party_account(
46 p_instance_pty_id IN NUMBER ,
47 p_rel_type_code IN VARCHAR2,
48 x_ip_account_id OUT NOCOPY NUMBER,
49 x_obj_version_number OUT NOCOPY NUMBER,
50 x_return_status OUT NOCOPY VARCHAR2);
51
52 PROCEDURE get_instance_party(
53 p_instance_id IN NUMBER ,
54 p_rel_type_code IN VARCHAR2,
55 x_inst_pty_qty OUT NOCOPY NUMBER,
56 x_obj_version_number OUT NOCOPY NUMBER,
57 x_return_status OUT NOCOPY VARCHAR2);
58
59 PROCEDURE get_instance(
60 p_instance_id IN NUMBER ,
61 x_obj_version_number OUT NOCOPY NUMBER,
62 x_inst_qty OUT NOCOPY NUMBER,
63 x_return_status OUT NOCOPY VARCHAR2 );
64
65 FUNCTION get_ii_obj_ver_num(
66 p_relationship_id IN NUMBER) RETURN NUMBER ;
67
68 FUNCTION get_org_obj_ver_num(
69 p_instance_ou_id IN NUMBER) RETURN NUMBER ;
70
71 FUNCTION get_ext_obj_ver_num(
72 p_attrib_value_id IN NUMBER) RETURN NUMBER;
73
74 FUNCTION get_pty_obj_ver_num(
75 p_inst_pty_id IN NUMBER) RETURN NUMBER;
76
77 FUNCTION get_acct_obj_ver_num(
78 p_ip_acct_id IN NUMBER) RETURN NUMBER;
79
80 FUNCTION get_primay_uom(
81 p_inv_item_id IN NUMBER,
82 p_inv_org_id IN NUMBER) RETURN VARCHAR2;
83
84 FUNCTION Check_relation_exists(
85 p_txn_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
86 p_txn_line_detail_id IN NUMBER) RETURN BOOLEAN;
87
88 PROCEDURE Get_Pricing_Attribs(
89 p_line_id IN NUMBER,
90 x_pricing_attb_tbl IN OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
91 x_return_status OUT NOCOPY VARCHAR2 );
92
93 PROCEDURE split_ship_rec(
94 x_upd_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl
95 ,x_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl
96 ,x_txn_line_detail_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec
97 ,p_txn_sub_type_rec IN csi_order_ship_pub.txn_sub_type_rec
98 ,p_order_shipment_rec IN csi_order_ship_pub.order_shipment_rec
99 ,p_order_line_rec IN csi_order_ship_pub.order_line_rec
100 ,p_proc_qty IN NUMBER
101 ,x_return_status OUT NOCOPY VARCHAR2);
102
103 PROCEDURE create_txn_details
104 ( x_txn_line_dtl_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec
105 ,p_txn_sub_type_rec IN csi_order_ship_pub.txn_sub_type_rec
106 ,p_order_shipment_rec IN csi_order_ship_pub.order_shipment_rec
107 ,p_order_line_rec IN csi_order_ship_pub.order_line_rec
108 ,x_return_status OUT NOCOPY VARCHAR2 );
109
110 PROCEDURE get_party_id(
111 p_cust_acct_id IN number,
112 x_party_id OUT NOCOPY number,
113 x_return_status OUT NOCOPY varchar2);
114
115 FUNCTION Check_config_exists
116 ( p_txn_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
117 p_txn_detail_id IN NUMBER ) RETURN BOOLEAN ;
118
119
120 FUNCTION validate_inst_party
121 (p_instance_id IN NUMBER,
122 p_inst_party_id IN NUMBER,
123 p_pty_rel_code IN VARCHAR2 ) RETURN BOOLEAN;
124
125 PROCEDURE get_dflt_inst_status_id(
126 x_instance_status_id OUT NOCOPY number,
127 x_return_status OUT NOCOPY varchar2);
128
129 PROCEDURE get_dflt_sub_type_id(
130 p_transaction_type_id IN number,
131 x_sub_type_id OUT NOCOPY number,
132 x_return_status OUT NOCOPY varchar2);
133
134 FUNCTION Get_trx_type_id
135 (p_trx_line_id IN NUMBER ) RETURN NUMBER ;
136
137 FUNCTION Get_trx_line_id
138 (p_src_trx_id IN NUMBER,
139 p_src_table_name IN VARCHAR2) RETURN NUMBER;
140
141
142 FUNCTION get_serial_contl_code
143 ( p_inv_item_id IN NUMBER,
144 p_inv_org_id IN NUMBER ) RETURN NUMBER ;
145
146
147 FUNCTION get_instance_party_id
148 ( p_instance_id IN NUMBER )RETURN NUMBER ;
149
150 FUNCTION get_ip_account_id
151 ( p_instance_party_id IN NUMBER )RETURN NUMBER ;
152
153 FUNCTION get_instance
154 ( p_order_line_id IN NUMBER )RETURN NUMBER ;
155
156 FUNCTION check_relation_exist
157 ( p_model_line_id IN NUMBER ,
158 p_line_id IN NUMBER )RETURN BOOLEAN ;
159
160 PROCEDURE get_order_line_dtls
161 ( p_mtl_transaction_id IN NUMBER,
162 x_order_line_rec OUT NOCOPY csi_order_ship_pub.order_line_rec,
163 x_return_status OUT NOCOPY VARCHAR2 );
164
165 PROCEDURE get_master_organization
166 (p_organization_id IN NUMBER,
167 p_master_organization_id OUT NOCOPY NUMBER,
168 x_return_status OUT NOCOPY VARCHAR2);
169
170 /* Added p_order_header_id as part of fix for Bug : 2897324 */
171
172 PROCEDURE get_split_order_line
173 ( p_order_line_id IN NUMBER,
174 p_order_header_id IN NUMBER,
175 x_split_ord_line_id OUT NOCOPY NUMBER,
176 x_return_status OUT NOCOPY VARCHAR2 );
177
178 PROCEDURE get_sub_type_rec
179 ( p_sub_type_id IN NUMBER,
180 p_trx_type_id IN NUMBER,
181 x_trx_sub_type_rec OUT NOCOPY csi_order_ship_pub.txn_sub_type_rec,
182 x_return_status OUT NOCOPY VARCHAR2 );
183
184 PROCEDURE get_int_party
185 ( x_int_party_id OUT NOCOPY NUMBER,
186 x_return_status OUT NOCOPY VARCHAR2 );
187
188 PROCEDURE get_inst_relation
189 ( p_ii_relationship_id IN NUMBER,
190 x_object_id OUT NOCOPY NUMBER ,
191 x_subject_id OUT NOCOPY NUMBER ,
192 x_return_status OUT NOCOPY VARCHAR2 );
193
194 PROCEDURE get_model_inst_lst(
195 p_parent_line_id IN number,
196 x_model_inst_tbl OUT NOCOPY csi_order_ship_pub.model_inst_tbl,
197 x_return_status OUT NOCOPY varchar2);
198
199 PROCEDURE get_qty_ratio --fix for 5096435
200 ( p_order_line_qty IN NUMBER,
201 p_order_item_id IN NUMBER,
202 p_model_remnant_flag IN VARCHAR2,
203 p_link_to_line_id IN NUMBER,
204 x_qty_ratio OUT NOCOPY NUMBER,
205 x_return_status OUT NOCOPY VARCHAR2 );
206
207 PROCEDURE get_link_to_line_id
208 ( x_link_to_line_id IN OUT NOCOPY NUMBER,
209 x_return_status OUT NOCOPY VARCHAR2 );
210
211 PROCEDURE build_inst_ii_tbl
212 ( p_orig_inst_id IN NUMBER,
213 p_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
214 p_new_instance_tbl IN csi_datastructures_pub.instance_tbl,
215 x_return_status OUT NOCOPY VARCHAR2);
216
217 PROCEDURE update_txn_line_dtl
218 ( p_source_trx_id IN NUMBER,
219 p_source_trx_table IN VARCHAR2,
220 p_api_name IN VARCHAR2,
221 p_error_message IN VARCHAR2 );
222
223 FUNCTION wip_config_exists(
224 p_instance_id IN NUMBER)
225 RETURN boolean;
226
227 FUNCTION check_standard_bom(
228 p_order_line_rec IN csi_order_ship_pub.order_line_rec)
229 RETURN boolean;
230
231 PROCEDURE create_txn_dtls(
232 p_source_trx_id IN NUMBER,
233 p_source_trx_table IN VARCHAR2,
234 x_return_status OUT NOCOPY VARCHAR2 );
235
236 PROCEDURE conv_to_prim_uom(
237 p_inv_organization_id IN number,
238 p_inventory_item_id IN number,
239 p_uom IN varchar2,
240 x_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
241 x_return_status OUT NOCOPY varchar2);
242
243 PROCEDURE get_source_trx_dtls(
244 p_mtl_transaction_id IN NUMBER,
245 x_mtl_txn_rec OUT NOCOPY csi_order_ship_pub.MTL_TXN_REC,
246 x_error_message OUT NOCOPY VARCHAR2,
247 x_return_status OUT NOCOPY VARCHAR2);
248
249 PROCEDURE split_txn_dtls(
250 split_txn_dtl_id IN NUMBER,
251 x_txn_line_dtls_lst OUT NOCOPY txn_line_dtl_tbl,
252 x_return_status OUT NOCOPY varchar2);
253
254 /* Included for Bug 4354267 */
255 PROCEDURE split_txn_dtls_with_qty(
256 split_txn_dtl_id IN NUMBER,
257 p_split_qty IN NUMBER,
258 x_return_status OUT NOCOPY varchar2);
259
260
261 PROCEDURE get_system_tbl(
262 p_txn_systems_rec IN csi_t_datastructures_grp.txn_system_rec,
263 x_cre_systems_rec OUT NOCOPY csi_datastructures_pub.system_rec );
264
265 PROCEDURE get_org_assignment_tbl(
266 p_txn_line_detail_rec IN csi_t_datastructures_grp.txn_line_detail_rec,
267 p_txn_org_assgn_tbl IN csi_t_datastructures_grp.txn_org_assgn_tbl,
268 x_cre_org_units_tbl OUT NOCOPY csi_datastructures_pub.organization_units_tbl,
269 x_upd_org_units_tbl OUT NOCOPY csi_datastructures_pub.organization_units_tbl,
270 x_return_status OUT NOCOPY VARCHAR2 );
271
272 PROCEDURE get_ext_attribs_tbl(
273 p_txn_line_detail_rec IN csi_t_datastructures_grp.txn_line_detail_rec,
274 p_txn_ext_attrib_vals_tbl IN csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
275 x_cre_ext_attrib_val_tbl OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl,
276 x_upd_ext_attrib_val_tbl OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl,
277 x_return_status OUT NOCOPY VARCHAR2 );
278
279 /* Added p_trx_rec for ER 2581101 */
280 PROCEDURE get_ii_relation_tbl(
281 p_txn_line_detail_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
282 p_txn_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
283 p_trx_rec IN csi_datastructures_pub.transaction_rec,
284 p_order_line_rec IN csi_order_ship_pub.order_line_rec,
285 x_cre_ii_rltns_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
286 x_upd_ii_rltns_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
287 x_return_status OUT NOCOPY VARCHAR2 );
288
289 PROCEDURE rebuild_tbls(
290 p_new_instance_id IN NUMBER,
291 x_upd_party_tbl IN OUT NOCOPY csi_datastructures_pub.party_tbl,
292 x_upd_party_acct_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl,
293 x_upd_org_units_tbl IN OUT NOCOPY csi_datastructures_pub.organization_units_tbl,
294 x_upd_ext_attrib_val_tbl IN OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl,
295 x_cre_org_units_tbl IN OUT NOCOPY csi_datastructures_pub.organization_units_tbl,
296 x_cre_ext_attrib_val_tbl IN OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl,
297 x_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
298 x_txn_line_detail_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
299 x_return_status OUT NOCOPY VARCHAR2 );
300
301 PROCEDURE cascade_txn_dtls(
302 p_source_trx_id IN NUMBER,
303 p_source_trx_table IN VARCHAR2,
304 p_ratio IN NUMBER,
305 x_return_status OUT NOCOPY VARCHAR2);
306
307 PROCEDURE derive_party_id(
308 p_cust_acct_role_id IN NUMBER,
309 x_party_id OUT NOCOPY NUMBER,
310 x_return_status OUT NOCOPY VARCHAR2);
311
312 PROCEDURE get_party_owner(
313 p_txn_line_detail_rec IN csi_t_datastructures_grp.txn_line_detail_rec,
314 p_txn_party_detail_tbl IN csi_t_datastructures_grp.txn_party_detail_tbl,
315 p_txn_pty_acct_dtl_tbl IN csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
316 x_trx_sub_type_rec IN csi_order_ship_pub.txn_sub_type_rec,
317 p_order_line_rec IN csi_order_ship_pub.order_line_rec,
318 x_upd_party_tbl OUT NOCOPY csi_datastructures_pub.party_tbl ,
319 x_upd_party_acct_tbl OUT NOCOPY csi_datastructures_pub.party_account_tbl,
320 x_cre_party_tbl OUT NOCOPY csi_datastructures_pub.party_tbl ,
321 x_cre_party_acct_tbl OUT NOCOPY csi_datastructures_pub.party_account_tbl,
322 x_return_status OUT NOCOPY VARCHAR2);
323
324 PROCEDURE make_non_header_tbl(
325 p_instance_header_tbl IN csi_datastructures_pub.instance_header_tbl,
326 x_instance_tbl OUT NOCOPY csi_datastructures_pub.instance_tbl,
327 x_return_status OUT NOCOPY varchar2);
328
329 PROCEDURE call_contracts_chk(
330 p_txn_line_detail_id in number,
331 p_txn_ii_rltns_tbl in csi_t_datastructures_grp.txn_ii_rltns_tbl,
332 x_call_contracts OUT NOCOPY varchar2,
333 x_return_status OUT NOCOPY varchar2);
334
335 PROCEDURE get_item_control_rec(
336 p_mtl_txn_id IN number,
337 x_item_control_rec OUT NOCOPY csi_order_ship_pub.item_control_rec,
338 x_return_status OUT NOCOPY varchar2);
339
340 ---Added (Start) for m-to-m enhancements
341 PROCEDURE rltns_xfaced_to_IB (
342 p_xtn_ii_rltns_rec IN csi_t_datastructures_grp.txn_ii_rltns_rec,
343 x_xface_to_IB_flag OUT NOCOPY VARCHAR2,
344 x_return_status OUT NOCOPY VARCHAR2);
345
346 PROCEDURE build_txn_relations (
347 p_txn_line_detail_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl ,
348 x_txn_ii_rltns_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
349 x_return_status OUT NOCOPY VARCHAR2) ;
350
351
352 PROCEDURE get_partner_rltns (
353 p_txn_line_detail_rec IN csi_t_datastructures_grp.txn_line_detail_rec ,
354 x_txn_ii_rltns_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
355 x_txn_line_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
356 x_return_status OUT NOCOPY VARCHAR2) ;
357 ---Added (End) for m-to-m enhancements
358
359 PROCEDURE build_parent_relation (
360 p_order_line_rec IN csi_order_ship_pub.order_line_rec,
361 x_model_inst_tbl IN OUT NOCOPY csi_order_ship_pub.model_inst_tbl,
362 x_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
363 x_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
364 x_return_status OUT NOCOPY VARCHAR2);
365
366 PROCEDURE build_child_relation(
367 p_order_line_rec IN csi_order_ship_pub.order_line_rec,
368 p_model_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
369 px_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
370 x_return_status OUT NOCOPY varchar2);
371
372 PROCEDURE get_ib_trackable_children(
373 p_order_line_rec IN csi_order_ship_pub.order_line_rec,
374 x_trackable_line_tbl OUT NOCOPY oe_order_pub.line_tbl_type,
375 x_return_status OUT NOCOPY varchar2);
376
377 PROCEDURE amend_contracts(
378 p_relationship_type_code in varchar2,
379 p_object_instance_id in number,
380 p_subject_instance_id in number,
381 p_trx_rec in csi_datastructures_pub.transaction_rec,
382 x_return_status OUT NOCOPY varchar2);
383
384 PROCEDURE get_parties_and_accounts(
385 p_instance_id IN number,
386 p_tld_rec IN csi_t_datastructures_grp.txn_line_detail_rec,
387 p_t_pty_tbl IN csi_t_datastructures_grp.txn_party_detail_tbl,
388 p_t_pty_acct_tbl IN csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
389 p_owner_pty_rec IN csi_datastructures_pub.party_rec,
390 p_owner_acct_rec IN csi_datastructures_pub.party_account_rec,
391 p_order_line_rec IN csi_order_ship_pub.order_line_rec,
392 x_i_pty_tbl OUT nocopy csi_datastructures_pub.party_tbl,
393 x_i_pty_acct_tbl OUT nocopy csi_datastructures_pub.party_account_tbl,
394 x_return_status OUT nocopy varchar2);
395
396 PROCEDURE get_unit_price_in_primary_uom(
397 p_unit_price IN number,
398 p_unit_price_uom IN varchar2,
399 px_item_control_rec IN OUT nocopy csi_order_ship_pub.item_control_rec,
400 x_unit_price_in_primary_uom OUT nocopy number,
401 x_return_status OUT nocopy varchar2);
402
403 END csi_utl_pkg ;
[Home] [Help]