1 PACKAGE csi_order_ship_pub AUTHID CURRENT_USER AS
2 /* $Header: csipioss.pls 120.7.12020000.2 2012/12/05 10:29:40 mvaradam ship $ */
3
4
5 g_pkg_name constant varchar2(30) := 'csi_order_ship_pub';
6 g_api_name varchar2(80) := 'order_shipment';
7
8 g_txn_type_id number;
9 g_dflt_sub_type_id number;
10
11 /*----------------------------------------------------------*/
12 /* Record Name : model_inst_rec */
13 /* Description : used for keeping the txn_line_detail */
14 /* of the top model instance */
15 /*----------------------------------------------------------*/
16
17 TYPE model_inst_rec is RECORD(
18 parent_line_id NUMBER := fnd_api.g_miss_num,
19 instance_id NUMBER := fnd_api.g_miss_num,
20 rem_qty NUMBER := fnd_api.g_miss_num,
21 txn_line_detail_id NUMBER := fnd_api.g_miss_num,
22 process_flag VARCHAR2(1) := fnd_api.g_miss_char);
23
24 TYPE model_inst_tbl is TABLE OF model_inst_rec INDEX BY BINARY_INTEGER;
25
26 /*----------------------------------------------------------*/
27 /* Record name: MTL_TXN_REC */
28 /* Description: Record used for keeping the source details */
29 /* of material tansaction */
30 /*----------------------------------------------------------*/
31
32 TYPE MTL_TXN_REC is RECORD(
33 MTL_TRANSACTION_ID NUMBER := FND_API.G_MISS_NUM,
34 SOURCE_LINE_ID NUMBER := FND_API.G_MISS_NUM,
35 SOURCE_HEADER_REF_ID NUMBER := FND_API.G_MISS_NUM,
36 SOURCE_HEADER_REF VARCHAR2(150) := FND_API.G_MISS_CHAR, -- Changed for bug#15931790
37 SOURCE_LINE_REF_ID NUMBER := FND_API.G_MISS_NUM,
38 SOURCE_LINE_REF VARCHAR2(150) := FND_API.G_MISS_CHAR, -- Changed for bug#15931790
39 SOURCE_TRANSACTION_DATE DATE := FND_API.G_MISS_DATE,
40 INV_MATERIAL_TRANSACTION_ID NUMBER := FND_API.G_MISS_NUM );
41
42 TYPE item_control_rec IS RECORD (
43 inventory_item_id number := fnd_api.g_miss_num,
44 organization_id number := fnd_api.g_miss_num,
45 ib_trackable_flag varchar2(1) := fnd_api.g_miss_char,
46 serial_control_code number := fnd_api.g_miss_num,
47 lot_control_code number := fnd_api.g_miss_num,
48 revision_control_code number := fnd_api.g_miss_num,
49 locator_control_code number := fnd_api.g_miss_num,
50 primary_uom_code varchar2(3) := fnd_api.g_miss_char,
51 bom_item_type number := fnd_api.g_miss_num,
52 model_item_id number := fnd_api.g_miss_num,
53 pick_components_flag varchar2(1) := fnd_api.g_miss_char,
54 reservable_type number := fnd_api.g_miss_num,
55 negative_balances_code number := fnd_api.g_miss_num,
56 shippable_flag varchar2(1) := fnd_api.g_miss_char,
57 transactable_flag varchar2(1) := fnd_api.g_miss_char);
58
59 /*-----------------------------------------------------------*/
60 /* Record name: order_shipment_rec */
61 /* Description: Record used for keeping the shipment details*/
62 /*-----------------------------------------------------------*/
63
64 TYPE order_shipment_rec IS RECORD(
65 line_id NUMBER := FND_API.G_MISS_NUM,
66 header_id NUMBER := FND_API.G_MISS_NUM,
67 txn_line_detail_id NUMBER := FND_API.G_MISS_NUM,
68 orig_inst_id NUMBER := FND_API.G_MISS_NUM,
69 instance_id NUMBER := FND_API.G_MISS_NUM,
70 system_id NUMBER := FND_API.G_MISS_NUM,
71 instance_qty NUMBER := FND_API.G_MISS_NUM,
72 party_id NUMBER := FND_API.G_MISS_NUM,
73 party_source_table VARCHAR2(30) := FND_API.G_MISS_CHAR,
74 party_account_id NUMBER := FND_API.G_MISS_NUM,
75 inst_obj_version_number NUMBER := FND_API.G_MISS_NUM,
76 txn_dtls_qty NUMBER := FND_API.G_MISS_NUM,
77 ord_line_shipped_qty NUMBER := FND_API.G_MISS_NUM,
78 instance_match VARCHAR2(1) := FND_API.G_MISS_CHAR,
79 quantity_match VARCHAR2(1) := FND_API.G_MISS_CHAR,
80 -- Added this for Bug 3384668
81 lot_match VARCHAR2(1) := FND_API.G_MISS_CHAR,
82 customer_id NUMBER := FND_API.G_MISS_NUM,
83 inventory_item_id NUMBER := FND_API.G_MISS_NUM,
84 organization_id NUMBER := FND_API.G_MISS_NUM,
85 revision VARCHAR2(30) := FND_API.G_MISS_CHAR,
86 subinventory VARCHAR2(30) := FND_API.G_MISS_CHAR,
87 locator_id NUMBER := FND_API.G_MISS_NUM,
88 lot_number VARCHAR2(80) := FND_API.G_MISS_CHAR,
89 serial_number VARCHAR2(30) := FND_API.G_MISS_CHAR,
90 transaction_uom VARCHAR2(30) := FND_API.G_MISS_CHAR,
91 order_quantity_uom VARCHAR2(30) := FND_API.G_MISS_CHAR,
92 invoice_to_contact_id NUMBER := FND_API.G_MISS_NUM,
93 invoice_to_org_id NUMBER := FND_API.G_MISS_NUM,
94 line_type_id NUMBER := FND_API.G_MISS_NUM,
95 ordered_quantity NUMBER := FND_API.G_MISS_NUM,
96 ship_to_contact_id NUMBER := FND_API.G_MISS_NUM,
97 ship_to_org_id NUMBER := FND_API.G_MISS_NUM,
98 ship_from_org_id NUMBER := FND_API.G_MISS_NUM,
99 sold_to_org_id NUMBER := FND_API.G_MISS_NUM,
100 sold_from_org_id NUMBER := FND_API.G_MISS_NUM,
101 source_line_id NUMBER := FND_API.G_MISS_NUM,
102 shipped_quantity NUMBER := FND_API.G_MISS_NUM,
103 ship_to_site_use_id NUMBER := FND_API.G_MISS_NUM,
104 transaction_type_id NUMBER := FND_API.G_MISS_NUM,
105 transaction_date DATE := FND_API.G_MISS_DATE,
106 item_type_code VARCHAR2(30) := FND_API.G_MISS_CHAR,
107 cust_po_number VARCHAR2(50) := FND_API.G_MISS_CHAR,
108 ato_line_id NUMBER := FND_API.G_MISS_NUM,
109 top_model_line_id NUMBER := FND_API.G_MISS_NUM,
110 link_to_line_id NUMBER := FND_API.G_MISS_NUM,
111 order_number number := fnd_api.g_miss_num,
112 line_number varchar2(30) := fnd_api.g_miss_char,
113 ib_owner varchar2(30) := fnd_api.g_miss_char,
114 end_customer_id NUMBER := fnd_api.g_miss_num,
115 deliver_to_org_id NUMBER := FND_API.G_MISS_NUM,
116 ib_install_loc VARCHAR2(60) := fnd_api.g_miss_char,
117 ib_install_loc_id NUMBER := fnd_api.g_miss_num,
118 ib_current_loc VARCHAR2(60) := fnd_api.g_miss_char,
119 ib_current_loc_id NUMBER := fnd_api.g_miss_num,
120 source_code VARCHAR2(30) := fnd_api.g_miss_char); -- Added for Siebel Genesis Project
121
122 TYPE order_shipment_tbl IS TABLE OF order_shipment_rec INDEX BY BINARY_INTEGER;
123
124 /*----------------------------------------------------------*/
125 /* Record name: order_line_rec */
126 /* Description : Record used to keep the order line details */
127 /*----------------------------------------------------------*/
128
129 TYPE order_line_rec IS RECORD(
130 header_id NUMBER := FND_API.G_MISS_NUM,
131 order_line_id NUMBER := FND_API.G_MISS_NUM,
132 om_vld_org_id NUMBER := fnd_api.g_miss_num,
133 unit_price NUMBER := fnd_api.g_miss_num,
134 currency_code varchar2(15) := fnd_api.g_miss_char,
135 inv_item_id NUMBER := FND_API.G_MISS_NUM,
136 inv_org_id NUMBER := FND_API.G_MISS_NUM,
137 ordered_item varchar2(80) := fnd_api.g_miss_char,
138 ordered_quantity NUMBER := FND_API.G_MISS_NUM,
139 shipped_quantity NUMBER := FND_API.G_MISS_NUM,
140 fulfilled_quantity NUMBER := FND_API.G_MISS_NUM,
141 trx_sub_type_id NUMBER := FND_API.G_MISS_NUM,
142 split_ord_line_id NUMBER := FND_API.G_MISS_NUM,
143 serial_code NUMBER := FND_API.G_MISS_NUM,
144 internal_party_id NUMBER := FND_API.G_MISS_NUM,
145 trx_line_id NUMBER := FND_API.G_MISS_NUM,
146 ato_line_id NUMBER := FND_API.G_MISS_NUM,
147 top_model_line_id NUMBER := FND_API.G_MISS_NUM,
148 link_to_line_id NUMBER := FND_API.G_MISS_NUM,
149 party_id NUMBER := FND_API.G_MISS_NUM,
150 customer_id NUMBER := FND_API.G_MISS_NUM,
151 invoice_to_org_id NUMBER := FND_API.G_MISS_NUM,
152 ship_to_org_id NUMBER := FND_API.G_MISS_NUM,
153 sold_from_org_id NUMBER := FND_API.G_MISS_NUM,
154 sold_to_org_id NUMBER := FND_API.G_MISS_NUM,
155 ship_to_party_site_id NUMBER := fnd_api.g_miss_num,
156 item_type_code VARCHAR2(30) := FND_API.G_MISS_CHAR,
157 transaction_date DATE := FND_API.G_MISS_DATE,
158 order_quantity_uom VARCHAR2(30) := FND_API.G_MISS_CHAR,
159 primary_uom VARCHAR2(30) := fnd_api.g_miss_char,
160 inv_mtl_transaction_id NUMBER := FND_API.G_MISS_NUM ,
161 ship_to_contact_id NUMBER := FND_API.G_MISS_NUM,
162 invoice_to_contact_id NUMBER := FND_API.G_MISS_NUM,
163 agreement_id NUMBER := FND_API.g_MISS_NUM,
164 order_number number := fnd_api.g_miss_num,
165 line_number varchar2(30):= fnd_api.g_miss_char,
166 actual_shipment_date DATE := FND_API.G_MISS_DATE,
167 fulfillment_date DATE := FND_API.G_MISS_DATE,
168 org_id NUMBER := fnd_api.g_miss_num,
169 ib_owner varchar2(60):= fnd_api.g_miss_char,
170 end_customer_id NUMBER := fnd_api.g_miss_num,
171 deliver_to_org_id NUMBER := FND_API.G_MISS_NUM,
172 ib_install_loc VARCHAR2(60):= fnd_api.g_miss_char,
173 ib_install_loc_id NUMBER := fnd_api.g_miss_num,
174 ib_current_loc VARCHAR2(60):= fnd_api.g_miss_char,
175 ib_current_loc_id NUMBER := fnd_api.g_miss_num,
176 bom_item_type number := fnd_api.g_miss_num,
177 reservable_type number := fnd_api.g_miss_num,
178 negative_balances_code number := fnd_api.g_miss_num,
179 mtl_action_id number := fnd_api.g_miss_num,
180 mtl_src_type_id number := fnd_api.g_miss_num,
181 config_header_id number := fnd_api.g_miss_num,
182 config_rev_nbr number := fnd_api.g_miss_num,
183 configuration_id number := fnd_api.g_miss_num,
184 macd_order_line varchar2(1):= fnd_api.g_false,
185 --4344316
186 model_remnant_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
187 source_code VARCHAR2(30) := FND_API.G_MISS_CHAR -- Added for Siebel Genesis Project
188 );
189
190 /*----------------------------------------------------------*/
191 /* Record name: txn_sub_type_rec */
192 /* Description : Record used to keep the sub type definition*/
193 /*----------------------------------------------------------*/
194
195 TYPE txn_sub_type_rec IS RECORD(
196 src_chg_owner_code VARCHAR2(1) := FND_API.G_MISS_CHAR,
197 nsrc_chg_owner_code VARCHAR2(1) := FND_API.G_MISS_CHAR,
198 src_status_id NUMBER := FND_API.G_MISS_NUM ,
199 nsrc_status_id NUMBER := FND_API.G_MISS_NUM ,
200 src_change_owner VARCHAR2(1) := FND_API.G_MISS_CHAR,
201 nsrc_change_owner VARCHAR2(1) := FND_API.G_MISS_CHAR,
202 trx_type_id NUMBER := FND_API.G_MISS_NUM ,
203 sub_type_id NUMBER := FND_API.G_MISS_NUM ,
204 src_reference_reqd VARCHAR2(1) := FND_API.G_MISS_CHAR,
205 nsrc_reference_reqd VARCHAR2(1) := FND_API.G_MISS_CHAR,
206 src_return_reqd VARCHAR2(1) := FND_API.G_MISS_CHAR,
207 nsrc_return_reqd VARCHAR2(1) := FND_API.G_MISS_CHAR );
208
209 /*----------------------------------------------------------*/
210 /* Procedure name: Order_shipment */
211 /* Description : Main Procedure that process the order */
212 /* shipment */
213 /*----------------------------------------------------------*/
214
215 PROCEDURE order_shipment(
216 p_mtl_transaction_id IN number,
217 p_message_id IN number,
218 x_return_status OUT NOCOPY varchar2,
219 px_trx_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec);
220
221 /*----------------------------------------------------------*/
222 /* Procedure name: get_order_shipment_rec */
223 /* Description : Procedure that gets the Shipment record */
224 /*----------------------------------------------------------*/
225
226 PROCEDURE get_order_shipment_rec(
227 p_mtl_transaction_id IN NUMBER,
228 p_order_line_rec IN order_line_rec,
229 p_txn_sub_type_rec IN txn_sub_type_rec,
230 p_transaction_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
231 x_order_shipment_tbl OUT NOCOPY order_shipment_tbl,
232 px_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec,
233 x_return_status OUT NOCOPY VARCHAR2);
234
235
236 /*----------------------------------------------------------*/
237 /* Procedure name: Build_SHTD_table */
238 /* Description : Procedure used to match the shipment with */
239 /* txn line details */
240 /*----------------------------------------------------------*/
241
242 PROCEDURE Build_SHTD_table(
243 p_mtl_transaction_id IN NUMBER,
244 p_order_line_rec IN OUT NOCOPY order_line_rec,
245 p_txn_sub_type_rec IN txn_sub_type_rec,
246 p_trx_detail_exist IN boolean,
247 p_trackable_parent IN boolean,
248 p_transaction_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
249 x_order_shipment_tbl OUT NOCOPY order_shipment_tbl,
250 px_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec,
251 x_return_status OUT NOCOPY VARCHAR2);
252
253 /*----------------------------------------------------------*/
254 /* Procedure name: Construct_for_txn_exists */
255 /* Description : Procedure that process the txn line detail */
256 /* if exists */
257 /*----------------------------------------------------------*/
258 PROCEDURE Construct_for_txn_exists(
259 p_txn_sub_type_rec IN txn_sub_type_rec,
260 p_order_line_rec IN order_line_rec,
261 x_order_shipment_tbl IN OUT NOCOPY order_shipment_tbl,
262 x_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
263 x_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
264 x_return_status OUT NOCOPY VARCHAR2 );
265
266
267 /*----------------------------------------------------------*/
268 /* Procedure name: update_install_base */
269 /* Description : Procedure that updates the IB from the */
270 /* matched txn line details */
271 /*----------------------------------------------------------*/
272 PROCEDURE update_install_base(
273 p_api_version IN NUMBER,
274 p_commit IN VARCHAR2 := fnd_api.g_false,
275 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
276 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
277 p_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
278 p_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
279 p_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
280 p_txn_pty_acct_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
281 p_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
282 p_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
283 p_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
284 p_txn_systems_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_systems_tbl,
285 p_pricing_attribs_tbl IN OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
289 p_validate_only IN VARCHAR2,
286 p_order_line_rec IN order_line_rec,
287 p_trx_rec IN csi_datastructures_pub.transaction_rec,
288 p_source IN VARCHAR2,
290 px_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec,
291 x_return_status OUT NOCOPY VARCHAR2,
292 x_msg_count OUT NOCOPY NUMBER,
293 x_msg_data OUT NOCOPY VARCHAR2 );
294
295 /*----------------------------------------------------------*/
296 /* Procedure name: match_txn_with_ship */
297 /* Description : Procedure used to do the matching of */
298 /* (instance and qty) txn line details and shipment */
299 /*----------------------------------------------------------*/
300 PROCEDURE match_txn_with_ship(
301 p_serial_code IN NUMBER,
302 x_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
303 x_order_shipment_tbl IN OUT NOCOPY order_shipment_tbl,
304 x_return_status OUT NOCOPY VARCHAR2 );
305
306 /*----------------------------------------------------------*/
307 /* Procedure name: process_txn */
308 /* Description : Procedure used to match the unresolved */
309 /* txn line details and update staus to */
310 /* 'IN_PROCESS' so that the txn line dtls */
311 /* are eligible for processing */
312 /*----------------------------------------------------------*/
313 PROCEDURE process_txn_dtl(
314 p_serial_code IN NUMBER,
315 p_txn_sub_type_rec IN txn_sub_type_rec,
316 p_order_line_rec IN order_line_rec,
317 x_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
318 x_txn_ii_rltns_tbl IN csi_t_datastructures_grp.txn_ii_rltns_tbl,
319 x_order_shipment_tbl IN OUT NOCOPY order_shipment_tbl,
320 x_return_status OUT NOCOPY VARCHAR2 );
321
322 /*----------------------------------------------------------*/
323 /* Procedure name: process_option_item */
324 /* Description : Procedure used to create the txn line */
325 /* details if it have to be splitted in the qty ratio */
326 /* and txn details does not exist */
327 /*----------------------------------------------------------*/
328 PROCEDURE process_option_item(
329 p_serial_code IN NUMBER,
330 p_order_line_rec IN order_line_rec,
331 p_txn_sub_type_rec IN txn_sub_type_rec,
332 p_trackable_parent IN boolean, --Added for 4548453
333 x_order_shipment_tbl IN OUT NOCOPY order_shipment_tbl,
334 x_model_inst_tbl IN OUT NOCOPY model_inst_tbl,
335 x_trx_line_id OUT NOCOPY NUMBER,
336 x_return_status OUT NOCOPY VARCHAR2 );
337
338 /*----------------------------------------------------------*/
339 /* Procedure name: rebuild_shipping_tbl */
340 /* Description : Procedure used to rebuild the shipment */
341 /* table if the item type code is config */
342 /*----------------------------------------------------------*/
343 PROCEDURE rebuild_shipping_tbl(
344 p_qty_ratio IN NUMBER,
345 x_order_shipment_tbl IN OUT NOCOPY order_shipment_tbl,
346 x_return_status OUT NOCOPY VARCHAR2 );
347
348 /*----------------------------------------------------------*/
349 /* Procedure name: validate_txn_tbl */
350 /* Description : Procedure used to for validationg the */
351 /* txn line details and the child tables */
352 /*----------------------------------------------------------*/
353 PROCEDURE validate_txn_tbl(
354 p_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec,
355 p_txn_line_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
356 p_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
357 p_txn_pty_acct_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
358 p_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
359 p_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
360 p_order_line_rec IN OUT NOCOPY order_line_rec,
361 p_source IN VARCHAR2,
362 x_return_status OUT NOCOPY VARCHAR2 );
363
364 /*----------------------------------------------------------*/
365 /* Procedure name: DECODE_MESSAGE */
366 /* Description : Procedure used to decode the messages */
367 /*----------------------------------------------------------*/
368 PROCEDURE decode_message(
369 p_msg_header IN XNP_MESSAGE.Msg_Header_Rec_Type,
370 p_msg_text IN VARCHAR2,
371 x_return_status OUT NOCOPY VARCHAR2,
372 x_error_message OUT NOCOPY VARCHAR2,
373 x_mtl_trx_rec OUT NOCOPY MTL_TXN_REC);
374
375 PROCEDURE oke_shipment(
376 p_mtl_txn_id IN number,
377 x_return_status OUT NOCOPY varchar2,
378 px_trx_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec);
379
380 TYPE customer_product_rec IS RECORD(
381 instance_id number,
382 quantity number,
383 line_id number,
384 txn_line_detail_id number,
385 transaction_id number,
386 serial_number varchar2(80),
387 lot_number varchar2(80));
388
389 TYPE customer_products_tbl IS TABLE of customer_product_rec INDEX BY binary_integer;
390
391 PROCEDURE get_comp_instances_from_wip(
392 p_wip_entity_id IN number,
393 p_organization_id IN number,
394 p_cps_tbl IN customer_products_tbl,
395 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
396 x_iir_tbl OUT nocopy csi_datastructures_pub.ii_relationship_tbl,
397 x_return_status OUT nocopy varchar2);
398
399 END csi_order_ship_pub ;
[Home] [Help]