1 PACKAGE csi_interface_pkg AS
2 /* $Header: csipitxs.pls 120.3.12010000.2 2008/09/19 18:32:27 rsinn 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(30),
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(30),
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_inventory_item_id IN number,
103 p_organization_id IN number,
104 x_item_attrib_rec OUT NOCOPY item_attributes_rec,
105 x_return_status OUT NOCOPY varchar2);
106
107
108 /* ------------------------------------------------------------------- */
109 /* use the source information and build a default transaction detail */
110 /* ------------------------------------------------------------------- */
111
112 -- this routine also splits the txn_line_detail based on the srl cntrl flag
113 -- and also based on the parent/child ratio so that the instance are
114 -- based on the number of txn line detail records
115
116 -- also cascades the transaction details from its parent having txn details
117
118 PROCEDURE build_default_txn_detail(
119 p_source_table IN varchar2,
120 p_source_id IN number,
121 p_source_header_rec IN source_header_rec,
122 p_source_line_rec IN source_line_rec,
123 p_csi_txn_rec IN csi_datastructures_pub.transaction_rec,
124 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
125 x_txn_line_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
126 x_txn_party_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
127 x_txn_party_acct_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
128 x_txn_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
129 x_pricing_attribs_tbl OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
130 x_return_status OUT NOCOPY varchar2);
131
132
133 /* ------------------------------------------------------------------- */
134 /* this routine is to rebuild the user entered transaction detail with */
135 /* the addition of all the defaults like contacts, org assignments etc.*/
136 /* ------------------------------------------------------------------- */
137
138 -- rebuild also splits the transaction detail bases on the serial control
139 -- flag or the based on the parent/child ratios.
140
141 -- for shipping we might want to pass the mtl_txn_table based on which we should
142 -- re-build the txn detail table
143
144 PROCEDURE rebuild_txn_detail(
145 p_source_table IN varchar2,
146 p_source_id IN number,
147 p_source_header_rec IN source_header_rec,
148 p_source_line_rec IN source_line_rec,
149 p_csi_txn_rec IN csi_datastructures_pub.transaction_rec,
150 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
151 px_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
152 px_txn_party_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
153 px_txn_party_acct_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
154 px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
155 x_pricing_attribs_tbl OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
156 x_return_status OUT NOCOPY varchar2);
157
158
159 PROCEDURE get_cz_txn_details(
160 p_config_session_key IN csi_utility_grp.config_session_key,
161 x_txn_line_rec OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
162 x_txn_line_dtl_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
163 x_txn_party_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
164 x_txn_party_acct_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
165 x_txn_org_assgn_tbl OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
166 x_txn_ii_rltns_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
167 x_txn_eav_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
168 x_return_status OUT NOCOPY varchar2);
169
170 PROCEDURE get_config_keys_for_order(
171 p_header_id IN number,
172 x_config_session_keys OUT NOCOPY csi_utility_grp.config_session_keys,
173 x_return_status OUT NOCOPY varchar2);
174
175 PROCEDURE get_cz_relations(
176 p_source_header_rec IN source_header_rec,
177 p_source_line_rec IN source_line_rec,
178 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
179 px_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
180 x_txn_ii_rltns_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
181 x_txn_eav_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
182 x_return_status OUT NOCOPY varchar2);
183
184 /* this routine takes the source line info and the current entered source txn
185 line detail info and builds the non source relation based on the source table
186 information
187
188 . if it is for the config line then it would read the config txn detail to read
189 the relationship and builds them to the corresponding source line detail
190 entered in the order line level
191
192 . if it is for the fulfillment/shipment then it reads the parent and child
193 information from the order line and builds the parent and the child relation
194 for the current order line being processed
195
196 */
197
198 PROCEDURE get_relations(
199 p_source_id IN number,
200 p_source_table IN varchar2,
201 p_source_header_rec IN source_header_rec,
202 p_source_line_rec IN source_line_rec,
203 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
204 px_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
205 x_txn_ii_rltns_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
206 x_txn_eav_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
207 x_return_status OUT NOCOPY varchar2);
208
209 PROCEDURE get_extended_attrib_values(
210 p_source_id IN number,
211 p_source_table IN varchar2,
212 p_source_header_rec IN source_header_rec,
213 p_source_line_rec IN source_line_rec,
214 px_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
215 x_txn_eav_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
216 x_return_status OUT NOCOPY varchar2);
217
218 PROCEDURE get_order_line_source_info(
219 p_order_line_id IN number,
220 x_source_header_rec OUT NOCOPY source_header_rec,
221 x_source_line_rec OUT NOCOPY source_line_rec,
222 x_return_status OUT NOCOPY varchar2);
223
224 PROCEDURE dump_instance_key(
225 p_instance_key IN csi_utility_grp.config_instance_key);
226
227 PROCEDURE dump_instance_keys(
228 p_instance_keys IN csi_utility_grp.config_instance_keys);
229
230 /*
231 PROCEDURE dump_instance_keys(
232 p_instance_keys IN csi_utility_grp.config_instance_keys)
233 IS
234 l_rec csi_utility_grp.config_instance_key;
235 BEGIN
236 IF p_instance_keys.COUNT > 0 THEN
237 FOR l_ind IN p_instance_keys.FIRST .. p_instance_keys.LAST
238 LOOP
239 l_rec := p_instance_keys(l_ind);
240 csi_t_gen_utility_pvt.add('Instance rec: #'||l_ind);
241 csi_t_gen_utility_pvt.add(' inst_hdr_id :'||l_rec.inst_hdr_id
242 csi_t_gen_utility_pvt.add(' inst_item_id :'||l_rec.inst_item_id
243 csi_t_gen_utility_pvt.add(' inst_rev_num :'||l_rec.inst_rev_num
244 csi_t_gen_utility_pvt.add(' inst_baseline_rev_num :'||l_rec.inst_baseline_rev_num
245 END LOOP;
246 END IF;
247 END dump_instance_keys;
248 */
249
250 PROCEDURE interface_ib(
251 p_source_header_rec IN csi_interface_pkg.source_header_rec,
252 p_source_line_rec IN csi_interface_pkg.source_line_rec,
253 px_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
254 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
255 px_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
256 px_txn_party_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
257 px_txn_party_acct_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
258 px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
259 px_txn_eav_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
260 px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
261 px_pricing_attribs_tbl IN OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
262 x_return_status OUT NOCOPY varchar2,
263 x_return_message OUT NOCOPY varchar2);
264
265 FUNCTION check_MACD_processing(
266 p_config_session_key IN csi_utility_grp.config_session_key,
267 x_return_status OUT NOCOPY varchar2)
268 RETURN boolean;
269
270 TYPE mtl_txn_rec IS RECORD(
271 trx_source_line_id number,
272 inventory_item_id number,
273 organization_id number,
274 revision varchar2(30),
275 subinventory_code varchar2(80),
276 locator_id number,
277 lot_number varchar2(80),
278 serial_number varchar2(80),
279 transaction_quantity number,
280 transaction_uom varchar2(3),
281 transaction_date date,
282 lot_control_code number,
283 serial_control_code number,
284 primary_uom varchar2(3),
285 primary_quantity number,
286 transaction_type_id number,
287 transaction_action_id number,
288 src_serial_flag varchar2(1),
289 dest_serial_flag varchar2(1),
290 create_update_flag varchar2(1),
291 match_flag varchar2(1) := 'N',
292 instance_id number,
293 negative_instance_flag varchar2(1) := 'N',
294 object_version_num number);
295
296 TYPE mtl_txn_tbl IS TABLE OF mtl_txn_rec INDEX BY BINARY_INTEGER;
297
298 PROCEDURE process_cz_txn_details(
299 p_config_session_keys IN csi_utility_grp.config_session_keys,
300 p_instance_id IN number,
301 x_instance_tbl OUT NOCOPY csi_datastructures_pub.instance_tbl,
302 x_return_status OUT NOCOPY varchar2);
303
304 -- following routines are for processing mtl transactions
305
306 PROCEDURE get_mtl_txn_tbl(
307 p_mtl_txn_id IN number,
308 x_mtl_txn_tbl OUT NOCOPY mtl_txn_tbl,
309 x_return_status OUT NOCOPY varchar2);
310
311 PROCEDURE pre_process_mtl_txn_tbl(
312 p_item_attrib_rec IN item_attributes_rec,
313 px_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
314 x_return_status OUT NOCOPY varchar2);
315
316 PROCEDURE get_inventory_instances(
317 p_item_attrib_rec IN item_attributes_rec,
318 px_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
319 x_return_status OUT NOCOPY varchar2);
320
321 PROCEDURE decrement_inventory_instances(
322 p_item_attrib_rec IN item_attributes_rec,
323 p_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
324 px_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
325 x_return_status OUT NOCOPY varchar2);
326
327 PROCEDURE sync_txn_dtl_and_mtl_txn(
328 p_mtl_txn_tbl IN mtl_txn_tbl,
329 p_item_attrib_rec IN item_attributes_rec,
330 px_txn_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
331 px_txn_party_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
332 px_txn_party_acct_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
333 px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
334 px_txn_eav_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
335 px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
336 x_return_status OUT NOCOPY varchar2);
337
338 FUNCTION check_standard_bom_pc(
339 p_instance_id IN NUMBER,
340 p_std_item_rec IN csi_datastructures_pub.instance_rec,
341 p_bom_item_type IN NUMBER)
342 RETURN boolean;
343
344 PROCEDURE build_relationship_tbl(
345 p_txn_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
346 p_txn_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
347 x_c_ii_rltns_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
348 x_u_ii_rltns_tbl OUT NOCOPY csi_datastructures_pub.ii_relationship_tbl,
349 x_return_status OUT NOCOPY varchar2);
350
351 END csi_interface_pkg;
[Home] [Help]