1 PACKAGE csi_interface_pkg AUTHID CURRENT_USER AS
2 /* $Header: csipitxs.pls 120.3.12020000.7 2012/12/14 06:04:36 mvaradam ship $ */
3
4 g_om_source_table constant varchar2(30) := 'OE_ORDER_LINES_ALL';
5 g_cz_source_table constant varchar2(30) := 'CONFIGURATOR';
6 g_oke_source_table constant varchar2(30) := 'WSH_DELIVERY_DETAILS';
7 g_ship_source_table constant varchar2(30) := 'WSH_DELIVERY_DETAILS';
8
9 g_om_txn_type_id constant number := 51;
10 g_rma_txn_type_id constant number := 53;
11 g_oke_txn_type_id constant number := 326;
12 g_macd_txn_type_id constant number := 401; --Added for bug 5194812
13
14 TYPE source_header_rec IS RECORD(
15 source_header_id number,
16 source_header_ref varchar2(150), -- Changed for bug#15931790
17 org_id number,
18 sold_from_org_id number,
19 owner_party_account_id number,
20 agreement_id number,
21 ship_to_address_id number,
22 bill_to_address_id number,
23 ship_to_contact_id number,
24 bill_to_contact_id number,
25 cust_po_number varchar2(50),
26 -- For partner ordering
27 sold_to_org_id number,
28 deliver_to_org_id number);
29
30
31 TYPE source_line_rec IS RECORD(
32 source_table varchar2(80),
33 source_line_id number,
34 source_line_ref varchar2(150), -- Changed for bug#15931790
35 org_id number,
36 sold_from_org_id number,
37 inventory_item_id number,
38 organization_id number,
39 item_revision varchar2(3),
40 uom_code varchar2(3),
41 source_quantity number,
42 shipped_quantity number,
43 shipped_date date,
44 fulfilled_quantity number,
45 fulfilled_date date,
46 owner_party_id number,
47 owner_party_account_id number,
48 ship_to_address_id number,
49 bill_to_address_id number,
50 ship_to_party_site_id number,
51 agreement_id number,
52 ship_to_contact_id number,
53 ship_to_contact_party_id number,
54 bill_to_contact_id number,
55 bill_to_contact_party_id number,
56 link_to_line_id number,
57 top_model_line_id number,
58 ato_line_id number,
59 item_type_code varchar2(30),
60 cust_po_number varchar2(50),
61 config_header_id number,
62 config_rev_num number,
63 config_item_id number,
64 batch_validate_flag varchar2(1),
65 -- For partner ordering
66 sold_to_org_id number,
67 deliver_to_org_id number,
68 ib_current_loc varchar2(60),
69 ib_current_loc_id number,
70 ib_install_loc varchar2(60),
71 ib_install_loc_id number,
72 install_to_party_site_id number);
73
74 TYPE source_line_tbl IS TABLE OF source_line_rec INDEX BY BINARY_INTEGER;
75
76 PROCEDURE get_source_info(
77 p_source_table IN varchar2,
78 p_source_id IN number,
79 x_source_header_rec OUT NOCOPY source_header_rec,
80 x_source_line_rec OUT NOCOPY source_line_rec,
81 x_return_status OUT NOCOPY varchar);
82
83 TYPE item_attributes_rec IS RECORD(
84 serial_control_code number,
85 lot_control_code number,
86 locator_control_code number,
87 revision_control_code number,
88 ib_trackable_flag varchar2(1),
89 shippable_flag varchar2(1),
90 inv_item_flag varchar2(1),
91 stockable_flag varchar2(1),
92 bom_item_type number,
93 pick_components_flag varchar2(1),
94 primary_uom_code varchar2(8),
95 ato_item_flag varchar2(1),
96 model_item_id number,
97 ib_item_instance_class varchar2(30),
98 config_model_type varchar2(30),
99 negative_balances_code number);
100
101 PROCEDURE get_item_attributes(
102 p_transaction_id IN number,-- Added for bug#14835893
103 p_inventory_item_id IN number,
104 p_organization_id IN number,
105 x_item_attrib_rec OUT NOCOPY item_attributes_rec,
106 x_return_status OUT NOCOPY varchar2);
107
108
109 /* ------------------------------------------------------------------- */
110 /* use the source information and build a default transaction detail */
111 /* ------------------------------------------------------------------- */
112
113 -- this routine also splits the txn_line_detail based on the srl cntrl flag
114 -- and also based on the parent/child ratio so that the instance are
115 -- based on the number of txn line detail records
116
117 -- also cascades the transaction details from its parent having txn details
118
119 PROCEDURE build_default_txn_detail(
120 p_source_table IN varchar2,
121 p_source_id IN number,
122 p_source_header_rec IN source_header_rec,
123 p_source_line_rec IN source_line_rec,
124 p_csi_txn_rec IN csi_datastructures_pub.transaction_rec,
125 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
126 x_txn_line_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
127 x_txn_party_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
128 x_txn_party_acct_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
129 x_txn_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
130 x_pricing_attribs_tbl OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
131 x_return_status OUT NOCOPY varchar2);
132
133
134 /* ------------------------------------------------------------------- */
135 /* this routine is to rebuild the user entered transaction detail with */
136 /* the addition of all the defaults like contacts, org assignments etc.*/
137 /* ------------------------------------------------------------------- */
138
139 -- rebuild also splits the transaction detail bases on the serial control
140 -- flag or the based on the parent/child ratios.
141
142 -- for shipping we might want to pass the mtl_txn_table based on which we should
143 -- re-build the txn detail table
144
145 PROCEDURE rebuild_txn_detail(
146 p_source_table IN varchar2,
147 p_source_id IN number,
148 p_source_header_rec IN source_header_rec,
149 p_source_line_rec IN source_line_rec,
150 p_csi_txn_rec IN csi_datastructures_pub.transaction_rec,
151 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
152 px_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
153 px_txn_party_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
154 px_txn_party_acct_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
155 px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
156 x_pricing_attribs_tbl OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
157 x_return_status OUT NOCOPY varchar2);
158
159
160 PROCEDURE get_cz_txn_details(
161 p_config_session_key IN csi_utility_grp.config_session_key,
162 x_txn_line_rec OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
163 x_txn_line_dtl_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
164 x_txn_party_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
165 x_txn_party_acct_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
166 x_txn_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
167 x_txn_ii_rltns_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
168 x_txn_eav_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
169 x_return_status OUT NOCOPY varchar2);
170
171 PROCEDURE get_config_keys_for_order(
172 p_header_id IN number,
173 x_config_session_keys OUT NOCOPY csi_utility_grp.config_session_keys,
174 x_return_status OUT NOCOPY varchar2);
175
176 PROCEDURE get_cz_relations(
177 p_source_header_rec IN source_header_rec,
178 p_source_line_rec IN source_line_rec,
179 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
180 px_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
181 x_txn_ii_rltns_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
182 x_txn_eav_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
183 x_return_status OUT NOCOPY varchar2);
184
185 /* this routine takes the source line info and the current entered source txn
186 line detail info and builds the non source relation based on the source table
187 information
188
189 . if it is for the config line then it would read the config txn detail to read
190 the relationship and builds them to the corresponding source line detail
191 entered in the order line level
192
193 . if it is for the fulfillment/shipment then it reads the parent and child
194 information from the order line and builds the parent and the child relation
195 for the current order line being processed
196
197 */
198
199 PROCEDURE get_relations(
200 p_source_id IN number,
201 p_source_table IN varchar2,
202 p_source_header_rec IN source_header_rec,
203 p_source_line_rec IN source_line_rec,
204 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
205 px_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
206 x_txn_ii_rltns_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
207 x_txn_eav_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
208 x_return_status OUT NOCOPY varchar2);
209
210 PROCEDURE get_extended_attrib_values(
211 p_source_id IN number,
212 p_source_table IN varchar2,
213 p_source_header_rec IN source_header_rec,
214 p_source_line_rec IN source_line_rec,
215 px_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
216 x_txn_eav_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
217 x_return_status OUT NOCOPY varchar2);
218
219 PROCEDURE get_order_line_source_info(
220 p_order_line_id IN number,
221 x_source_header_rec OUT NOCOPY source_header_rec,
222 x_source_line_rec OUT NOCOPY source_line_rec,
223 x_return_status OUT NOCOPY varchar2);
224
225 PROCEDURE dump_instance_key(
226 p_instance_key IN csi_utility_grp.config_instance_key);
227
228 PROCEDURE dump_instance_keys(
229 p_instance_keys IN csi_utility_grp.config_instance_keys);
230
231 /*
232 PROCEDURE dump_instance_keys(
233 p_instance_keys IN csi_utility_grp.config_instance_keys)
234 IS
235 l_rec csi_utility_grp.config_instance_key;
236 BEGIN
237 IF p_instance_keys.COUNT > 0 THEN
238 FOR l_ind IN p_instance_keys.FIRST .. p_instance_keys.LAST
239 LOOP
240 l_rec := p_instance_keys(l_ind);
241 csi_t_gen_utility_pvt.add('Instance rec: #'||l_ind);
242 csi_t_gen_utility_pvt.add(' inst_hdr_id :'||l_rec.inst_hdr_id
243 csi_t_gen_utility_pvt.add(' inst_item_id :'||l_rec.inst_item_id
244 csi_t_gen_utility_pvt.add(' inst_rev_num :'||l_rec.inst_rev_num
245 csi_t_gen_utility_pvt.add(' inst_baseline_rev_num :'||l_rec.inst_baseline_rev_num
246 END LOOP;
247 END IF;
248 END dump_instance_keys;
249 */
250
251 PROCEDURE interface_ib(
252 p_source_header_rec IN csi_interface_pkg.source_header_rec,
253 p_source_line_rec IN csi_interface_pkg.source_line_rec,
254 px_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
255 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
256 px_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
257 px_txn_party_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
258 px_txn_party_acct_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
259 px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
260 px_txn_eav_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
261 px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
262 px_pricing_attribs_tbl IN OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
263 x_return_status OUT NOCOPY varchar2,
264 x_return_message OUT NOCOPY varchar2);
265
266 FUNCTION check_MACD_processing(
267 p_config_session_key IN csi_utility_grp.config_session_key,
268 x_return_status OUT NOCOPY varchar2)
269 RETURN boolean;
270
271 TYPE mtl_txn_rec IS RECORD(
272 trx_source_line_id number,
273 inventory_item_id number,
274 organization_id number,
275 revision varchar2(30),
276 subinventory_code varchar2(80),
277 locator_id number,
278 lot_number varchar2(80),
279 serial_number varchar2(80),
280 transaction_quantity number,
281 transaction_uom varchar2(3),
282 transaction_date date,
283 lot_control_code number,
284 serial_control_code number,
285 primary_uom varchar2(3),
286 primary_quantity number,
287 transaction_type_id number,
288 transaction_action_id number,
289 src_serial_flag varchar2(1),
290 dest_serial_flag varchar2(1),
291 create_update_flag varchar2(1),
292 match_flag varchar2(1) := 'N',
293 instance_id number,
294 negative_instance_flag varchar2(1) := 'N',
295 object_version_num number);
296
297 TYPE mtl_txn_tbl IS TABLE OF mtl_txn_rec INDEX BY BINARY_INTEGER;
298
299 PROCEDURE process_cz_txn_details(
300 p_config_session_keys IN csi_utility_grp.config_session_keys,
301 p_instance_id IN number,
302 x_instance_tbl OUT NOCOPY csi_datastructures_pub.instance_tbl,
303 x_return_status OUT NOCOPY varchar2);
304
305 -- following routines are for processing mtl transactions
306
307 PROCEDURE get_mtl_txn_tbl(
308 p_mtl_txn_id IN number,
309 x_mtl_txn_tbl OUT NOCOPY mtl_txn_tbl,
310 x_return_status OUT NOCOPY varchar2);
311
312 PROCEDURE pre_process_mtl_txn_tbl(
313 p_item_attrib_rec IN item_attributes_rec,
314 px_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
315 x_return_status OUT NOCOPY varchar2);
316
317 PROCEDURE get_inventory_instances(
318 p_item_attrib_rec IN item_attributes_rec,
319 px_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
320 x_return_status OUT NOCOPY varchar2);
321
322 PROCEDURE decrement_inventory_instances(
323 p_item_attrib_rec IN item_attributes_rec,
324 p_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
325 px_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
326 x_return_status OUT NOCOPY varchar2);
327
328 PROCEDURE sync_txn_dtl_and_mtl_txn(
329 p_mtl_txn_tbl IN mtl_txn_tbl,
330 p_item_attrib_rec IN item_attributes_rec,
331 px_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
332 px_txn_party_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
333 px_txn_party_acct_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
334 px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
335 px_txn_eav_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
336 px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
337 x_return_status OUT NOCOPY varchar2);
338
339 FUNCTION check_standard_bom_pc(
340 p_instance_id IN NUMBER,
341 p_std_item_rec IN csi_datastructures_pub.instance_rec,
342 p_bom_item_type IN NUMBER)
343 RETURN boolean;
344
345 PROCEDURE build_relationship_tbl(
346 p_txn_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
347 p_txn_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
348 x_c_ii_rltns_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
349 x_u_ii_rltns_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
350 x_return_status OUT NOCOPY varchar2);
351
352 END csi_interface_pkg;
[Home] [Help]