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