[Home] [Help]
PACKAGE BODY: APPS.OE_CNCL_ORDER_IMPORT_PVT
Source
1 PACKAGE BODY OE_CNCL_ORDER_IMPORT_PVT AS
2 /* $Header: OEXVCIMB.pls 120.4.12020000.2 2012/07/03 10:20:28 amallik ship $ */
3
4 /* ---------------------------------------------------------------
5 -- Start of Comments
6 -- API name OE_CNCL_ORDER_IMPORT_PVT
7 -- Type Private
8 -- Function
9 -- Pre-reqs
10 -- Parameters
11 -- Version Current version = 1.0
12 -- Initial version = 1.0
13 -- Notes
14 --
15 -- End of Comments
16 ------------------------------------------------------------------
17 */
18
19
20 --G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_CNCL_Order_Import';
21
22
23
24 /* -----------------------------------------------------------
25 Procedure: Import_Order
26 -----------------------------------------------------------
27 */
28 PROCEDURE IMPORT_ORDER(
29 p_request_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
30 ,p_order_source_id IN NUMBER
31 ,p_orig_sys_document_ref IN VARCHAR2
32 ,p_sold_to_org_id IN NUMBER
33 ,p_sold_to_org IN VARCHAR2
34 ,p_change_sequence IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
35 ,p_validate_only IN VARCHAR2 DEFAULT FND_API.G_FALSE
36 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE
37 ,p_org_id IN NUMBER
38 ,p_msg_count OUT NOCOPY NUMBER
39
40 ,p_msg_data OUT NOCOPY VARCHAR2
41
42 ,p_return_status OUT NOCOPY VARCHAR2
43
44
45 ) IS
46
47 l_control_rec OE_Globals.Control_Rec_Type;
48
49 l_header_adj_rec OE_Order_Pub.Header_Adj_Rec_Type;
50 l_header_scredit_rec OE_Order_Pub.Header_Scredit_Rec_Type;
51 l_line_rec OE_Order_Pub.Line_Rec_Type;
52 l_line_adj_rec OE_Order_Pub.Line_Adj_Rec_Type;
53 l_line_scredit_rec OE_Order_Pub.Line_Scredit_Rec_Type;
54 l_lot_serial_rec OE_Order_Pub.Lot_Serial_Rec_Type;
55 l_reservation_rec OE_Order_Pub.Reservation_Rec_Type;
56 l_action_request_rec OE_Order_Pub.Request_Rec_Type;
57
58 l_header_rec OE_Order_Pub.Header_Rec_Type;
59 l_header_adj_tbl OE_Order_Pub.Header_Adj_Tbl_Type;
60 l_header_price_att_tbl OE_Order_Pub.Header_Price_Att_Tbl_Type;
61 l_header_adj_att_tbl OE_Order_Pub.Header_Adj_Att_Tbl_Type;
62 l_header_adj_assoc_tbl OE_Order_Pub.Header_Adj_Assoc_Tbl_Type;
63 l_header_scredit_tbl OE_Order_Pub.Header_Scredit_Tbl_Type;
64 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
65 l_line_adj_tbl OE_Order_Pub.Line_Adj_Tbl_Type;
66 l_line_price_att_tbl OE_Order_Pub.Line_Price_Att_Tbl_Type;
67 l_line_adj_att_tbl OE_Order_Pub.Line_Adj_Att_Tbl_Type;
68 l_line_adj_assoc_tbl OE_Order_Pub.Line_Adj_Assoc_Tbl_Type;
69 l_line_scredit_tbl OE_Order_Pub.Line_Scredit_Tbl_Type;
70 l_lot_serial_tbl OE_Order_Pub.Lot_Serial_Tbl_Type;
71 l_reservation_tbl OE_Order_Pub.Reservation_Tbl_Type;
72 l_action_request_tbl OE_Order_Pub.Request_Tbl_Type;
73
74 l_header_rec_old OE_Order_Pub.Header_Rec_Type;
75 l_header_adj_tbl_old OE_Order_Pub.Header_Adj_Tbl_Type;
76 l_header_price_att_tbl_old OE_Order_Pub.Header_Price_Att_Tbl_Type;
77 l_header_adj_att_tbl_old OE_Order_Pub.Header_Adj_Att_Tbl_Type;
78 l_header_adj_assoc_tbl_old OE_Order_Pub.Header_Adj_Assoc_Tbl_Type;
79 l_header_scredit_tbl_old OE_Order_Pub.Header_Scredit_Tbl_Type;
80 l_line_tbl_old OE_Order_Pub.Line_Tbl_Type;
81 l_line_adj_tbl_old OE_Order_Pub.Line_Adj_Tbl_Type;
82 l_line_price_att_tbl_old OE_Order_Pub.Line_Price_Att_Tbl_Type;
83 l_line_adj_att_tbl_old OE_Order_Pub.Line_Adj_Att_Tbl_Type;
84 l_line_adj_assoc_tbl_old OE_Order_Pub.Line_Adj_Assoc_Tbl_Type;
85 l_line_scredit_tbl_old OE_Order_Pub.Line_Scredit_Tbl_Type;
86 l_lot_serial_tbl_old OE_Order_Pub.Lot_Serial_Tbl_Type;
87 l_action_request_tbl_old OE_Order_Pub.Request_Tbl_Type;
88
89 l_header_rec_new OE_Order_Pub.Header_Rec_Type;
90 l_header_adj_tbl_new OE_Order_Pub.Header_Adj_Tbl_Type;
91 l_header_price_att_tbl_new OE_Order_Pub.Header_Price_Att_Tbl_Type;
92 l_header_adj_att_tbl_new OE_Order_Pub.Header_Adj_Att_Tbl_Type;
93 l_header_adj_assoc_tbl_new OE_Order_Pub.Header_Adj_Assoc_Tbl_Type;
94 l_header_scredit_tbl_new OE_Order_Pub.Header_Scredit_Tbl_Type;
95 l_line_tbl_new OE_Order_Pub.Line_Tbl_Type;
96 l_line_adj_tbl_new OE_Order_Pub.Line_Adj_Tbl_Type;
97 l_line_price_att_tbl_new OE_Order_Pub.Line_Price_Att_Tbl_Type;
98 l_line_adj_att_tbl_new OE_Order_Pub.Line_Adj_Att_Tbl_Type;
99 l_line_adj_assoc_tbl_new OE_Order_Pub.Line_Adj_Assoc_Tbl_Type;
100 l_line_scredit_tbl_new OE_Order_Pub.Line_Scredit_Tbl_Type;
101 l_lot_serial_tbl_new OE_Order_Pub.Lot_Serial_Tbl_Type;
102 l_action_request_tbl_new OE_Order_Pub.Request_Tbl_Type;
103
104 l_header_adj_val_rec OE_Order_Pub.Header_Adj_Val_Rec_Type;
105 l_header_scredit_val_rec OE_Order_Pub.Header_Scredit_Val_Rec_Type;
106 l_line_val_rec OE_Order_Pub.Line_Val_Rec_Type;
107 l_line_adj_val_rec OE_Order_Pub.Line_Adj_Val_Rec_Type;
108 l_line_scredit_val_rec OE_Order_Pub.Line_Scredit_Val_Rec_Type;
109 l_lot_serial_val_rec OE_Order_Pub.Lot_Serial_Val_Rec_Type;
110 l_reservation_val_rec OE_Order_Pub.Reservation_Val_Rec_Type;
111
112 l_header_val_rec OE_Order_Pub.Header_Val_Rec_Type;
113 l_header_adj_val_tbl OE_Order_Pub.Header_Adj_Val_Tbl_Type;
114 l_header_scredit_val_tbl OE_Order_Pub.Header_Scredit_Val_Tbl_Type;
115 l_line_val_tbl OE_Order_Pub.Line_Val_Tbl_Type;
116 l_line_adj_val_tbl OE_Order_Pub.Line_Adj_Val_Tbl_Type;
117 l_line_scredit_val_tbl OE_Order_Pub.Line_Scredit_Val_Tbl_Type;
118 l_lot_serial_val_tbl OE_Order_Pub.Lot_Serial_Val_Tbl_Type;
119 l_reservation_val_tbl OE_Order_Pub.Reservation_Val_Tbl_Type;
120
121 l_header_val_rec_old OE_Order_Pub.Header_Val_Rec_Type;
122 l_header_adj_val_tbl_old OE_Order_Pub.Header_Adj_Val_Tbl_Type;
123 l_header_scredit_val_tbl_old OE_Order_Pub.Header_Scredit_Val_Tbl_Type;
124 l_line_val_tbl_old OE_Order_Pub.Line_Val_Tbl_Type;
125 l_line_adj_val_tbl_old OE_Order_Pub.Line_Adj_Val_Tbl_Type;
126 l_line_scredit_val_tbl_old OE_Order_Pub.Line_Scredit_Val_Tbl_Type;
127 l_lot_serial_val_tbl_old OE_Order_Pub.Lot_Serial_Val_Tbl_Type;
128
129 l_header_val_rec_new OE_Order_Pub.Header_Val_Rec_Type;
130 l_header_adj_val_tbl_new OE_Order_Pub.Header_Adj_Val_Tbl_Type;
131 l_header_scredit_val_tbl_new OE_Order_Pub.Header_Scredit_Val_Tbl_Type;
132 l_line_val_tbl_new OE_Order_Pub.Line_Val_Tbl_Type;
133 l_line_adj_val_tbl_new OE_Order_Pub.Line_Adj_Val_Tbl_Type;
134 l_line_scredit_val_tbl_new OE_Order_Pub.Line_Scredit_Val_Tbl_Type;
135 l_lot_serial_val_tbl_new OE_Order_Pub.Lot_Serial_Val_Tbl_Type;
136
137 l_action_rec OE_Order_Import_Pvt.Action_Rec_Type;
138
139 l_request_id NUMBER := p_request_id;
140 l_order_source_id NUMBER := p_order_source_id;
141 l_orig_sys_document_ref VARCHAR2(50) := p_orig_sys_document_ref;
142 l_sold_to_org_id NUMBER := p_sold_to_org_id;
143 l_sold_to_org VARCHAR2(360) := p_sold_to_org;
144 l_change_sequence VARCHAR2(50) := p_change_sequence;
145 l_org_id NUMBER := p_org_id;
146 l_orig_sys_line_ref VARCHAR2(50);
147 l_orig_sys_shipment_ref VARCHAR2(50);
148
149 l_order_type_id NUMBER;
150 l_order_number NUMBER;
151 l_line_number NUMBER;
152 l_shipment_number NUMBER;
153 l_header_id NUMBER;
154 l_line_id NUMBER;
155
156 l_validate_only VARCHAR2(1);
157 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
158 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
159 l_return_values VARCHAR2(1) := FND_API.G_FALSE;
160 l_commit VARCHAR2(1) := FND_API.G_FALSE;
161 l_api_service_level VARCHAR2(30) := OE_GLOBALS.G_ALL_SERVICE;
162
163 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
164 l_return_status_oi_pre VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
165 l_return_status_oi_pst VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
166 l_return_status_po VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
167 l_return_status_del_ord VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
168 l_return_status_del_msg VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
169 l_return_status_sav_msg VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
170 l_return_status_upd_err VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
171 l_return_status_book VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
172 l_error_index_flag VARCHAR2(1) := 'N';
173 l_validation_org NUMBER := OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
174
175 l_header_count NUMBER := 0;
176 l_header_adj_count NUMBER := 0;
177 l_header_scredit_count NUMBER := 0;
178 l_line_count NUMBER := 0;
179 l_line_adj_count NUMBER := 0;
180 l_line_scredit_count NUMBER := 0;
181 l_lot_serial_count NUMBER := 0;
182 l_reservation_count NUMBER := 0;
183 l_action_request_count NUMBER := 0;
184
185 l_msg_index NUMBER := 0;
186 l_msg_context VARCHAR2(2000);
187 l_msg_data VARCHAR2(2000);
188
189 l_msg_entity_code VARCHAR2(30);
190 l_msg_entity_ref VARCHAR2(50);
191 l_msg_entity_id NUMBER;
192 l_msg_header_id NUMBER;
193 l_msg_line_id NUMBER;
194 l_msg_order_source_id NUMBER;
195 l_msg_orig_sys_document_ref VARCHAR2(50);
196 l_msg_sold_to_org_id NUMBER;
197 l_msg_sold_to_org VARCHAR2(360);
198 l_msg_change_sequence VARCHAR2(50);
199 l_msg_orig_sys_line_ref VARCHAR2(50);
200 l_msg_orig_sys_shipment_ref VARCHAR2(50);
201 l_msg_source_document_type_id NUMBER;
202 l_msg_source_document_id NUMBER;
203 l_msg_source_document_line_id NUMBER;
204 l_msg_attribute_code VARCHAR2(50);
205 l_msg_constraint_id NUMBER;
206 l_msg_process_activity NUMBER;
207 l_msg_notification_flag VARCHAR2(1);
208 l_msg_type VARCHAR2(30);
209
210 l_commit_flag VARCHAR2(1) := 'Y';
211 l_delete_flag VARCHAR2(1) := 'Y';
212
213 l_api_name CONSTANT VARCHAR2(30) := 'Import_Order';
214
215 l_structure fnd_flex_key_api.structure_type;
216 l_flexfield fnd_flex_key_api.flexfield_type;
217 l_segment_array fnd_flex_ext.segmentarray;
218 l_n_segments NUMBER;
219 l_segments FND_FLEX_KEY_API.SEGMENT_LIST;
220 l_id NUMBER;
221 failure_message varchar2(2000);
222
223 TYPE t_adj_line_ref_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
224 TYPE t_scredit_line_ref_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
225 l_adj_line_ref_tbl t_adj_line_ref_type;
226 l_scredit_line_ref_tbl t_scredit_line_ref_type;
227 l_adj_line_ref VARCHAR2(50);
228 l_scredit_line_ref VARCHAR2(50);
229
230 /* -----------------------------------------------------------
231 Headers cursor
232 -----------------------------------------------------------
233 */
234 CURSOR l_header_cursor IS
235 SELECT order_source_id
236 , orig_sys_document_ref
237 , change_sequence
238 , nvl(change_request_code, FND_API.G_MISS_CHAR)
239 , nvl(order_source, FND_API.G_MISS_CHAR)
240 , nvl(org_id, FND_API.G_MISS_NUM)
241 , nvl(header_id, FND_API.G_MISS_NUM)
242 , nvl(order_number, FND_API.G_MISS_NUM)
243 , nvl(version_number, FND_API.G_MISS_NUM)
244 , nvl(ordered_date, FND_API.G_MISS_DATE)
245 , nvl(order_type_id, FND_API.G_MISS_NUM)
246 , nvl(order_type, FND_API.G_MISS_CHAR)
247 , nvl(price_list_id, FND_API.G_MISS_NUM)
248 , nvl(price_list, FND_API.G_MISS_CHAR)
249 , nvl(conversion_rate, FND_API.G_MISS_NUM)
250 , nvl(conversion_rate_date, FND_API.G_MISS_DATE)
251 , nvl(conversion_type_code, FND_API.G_MISS_CHAR)
252 , nvl(conversion_type, FND_API.G_MISS_CHAR)
253 , nvl(transactional_curr_code, FND_API.G_MISS_CHAR)
254 , nvl(return_reason_code, FND_API.G_MISS_CHAR)
255 , nvl(salesrep_id, FND_API.G_MISS_NUM)
256 , nvl(salesrep, FND_API.G_MISS_CHAR)
257 , nvl(sales_channel_code, FND_API.G_MISS_CHAR)
258 , nvl(sales_channel, FND_API.G_MISS_CHAR)
259 , nvl(tax_point_code, FND_API.G_MISS_CHAR)
260 , nvl(tax_point, FND_API.G_MISS_CHAR)
261 , nvl(tax_exempt_flag, FND_API.G_MISS_CHAR)
262 , nvl(tax_exempt_number, FND_API.G_MISS_CHAR)
263 , nvl(tax_exempt_reason_code, FND_API.G_MISS_CHAR)
264 , nvl(tax_exempt_reason, FND_API.G_MISS_CHAR)
265 , nvl(agreement_id, FND_API.G_MISS_NUM)
266 , nvl(agreement, FND_API.G_MISS_CHAR)
267 , nvl(invoicing_rule_id, FND_API.G_MISS_NUM)
268 , nvl(invoicing_rule, FND_API.G_MISS_CHAR)
269 , nvl(accounting_rule_id, FND_API.G_MISS_NUM)
270 , nvl(accounting_rule, FND_API.G_MISS_CHAR)
271 , nvl(payment_term_id, FND_API.G_MISS_NUM)
272 , nvl(payment_term, FND_API.G_MISS_CHAR)
273 , nvl(demand_class_code, FND_API.G_MISS_CHAR)
274 , nvl(shipment_priority_code, FND_API.G_MISS_CHAR)
275 , nvl(shipment_priority, FND_API.G_MISS_CHAR)
276 , nvl(shipping_method_code, FND_API.G_MISS_CHAR)
277 , nvl(freight_carrier_code, FND_API.G_MISS_CHAR)
278 , nvl(freight_terms_code, FND_API.G_MISS_CHAR)
279 , nvl(freight_terms, FND_API.G_MISS_CHAR)
280 , nvl(fob_point_code, FND_API.G_MISS_CHAR)
281 , nvl(fob_point, FND_API.G_MISS_CHAR)
282 , nvl(partial_shipments_allowed, FND_API.G_MISS_CHAR)
283 , nvl(ship_tolerance_above, FND_API.G_MISS_NUM)
284 , nvl(ship_tolerance_below, FND_API.G_MISS_NUM)
285 , nvl(shipping_instructions, FND_API.G_MISS_CHAR)
286 , nvl(packing_instructions, FND_API.G_MISS_CHAR)
287 , nvl(order_date_type_code, FND_API.G_MISS_CHAR)
288 , nvl(earliest_schedule_limit, FND_API.G_MISS_NUM)
289 , nvl(latest_schedule_limit, FND_API.G_MISS_NUM)
290 , nvl(customer_po_number, FND_API.G_MISS_CHAR)
291 , nvl(customer_payment_term_id, FND_API.G_MISS_NUM)
292 , nvl(customer_payment_term, FND_API.G_MISS_CHAR)
293 , nvl(payment_type_code, FND_API.G_MISS_CHAR)
294 , nvl(payment_amount, FND_API.G_MISS_NUM)
295 , nvl(check_number, FND_API.G_MISS_CHAR)
296 , nvl(credit_card_code, FND_API.G_MISS_CHAR)
297 , nvl(credit_card_holder_name, FND_API.G_MISS_CHAR)
298 , nvl(credit_card_number, FND_API.G_MISS_CHAR)
299 , nvl(credit_card_expiration_date, FND_API.G_MISS_DATE)
300 , nvl(credit_card_approval_code, FND_API.G_MISS_CHAR)
301 , nvl(credit_card_approval_date, FND_API.G_MISS_DATE)
302 , nvl(sold_from_org_id, FND_API.G_MISS_NUM)
303 , nvl(sold_from_org, FND_API.G_MISS_CHAR)
304 , nvl(sold_to_org_id, FND_API.G_MISS_NUM)
305 , nvl(sold_to_org, FND_API.G_MISS_CHAR)
306 , nvl(customer_number, FND_API.G_MISS_CHAR)
307 , nvl(ship_from_org_id, FND_API.G_MISS_NUM)
308 , nvl(ship_from_org, FND_API.G_MISS_CHAR)
309 , nvl(ship_to_org_id, FND_API.G_MISS_NUM)
310 , nvl(ship_to_org, FND_API.G_MISS_CHAR)
311 , nvl(invoice_to_org_id, FND_API.G_MISS_NUM)
312 , nvl(invoice_to_org, FND_API.G_MISS_CHAR)
313 , nvl(deliver_to_org_id, FND_API.G_MISS_NUM)
314 , nvl(deliver_to_org, FND_API.G_MISS_CHAR)
315 , nvl(sold_to_contact_id, FND_API.G_MISS_NUM)
316 , nvl(sold_to_contact, FND_API.G_MISS_CHAR)
317 , nvl(ship_to_contact_id, FND_API.G_MISS_NUM)
318 , nvl(ship_to_contact, FND_API.G_MISS_CHAR)
319 , nvl(invoice_to_contact_id, FND_API.G_MISS_NUM)
320 , nvl(invoice_to_contact, FND_API.G_MISS_CHAR)
321 , nvl(deliver_to_contact_id, FND_API.G_MISS_NUM)
322 , nvl(deliver_to_contact, FND_API.G_MISS_CHAR)
323 , nvl(ship_to_address1, FND_API.G_MISS_CHAR)
324 , nvl(ship_to_address2, FND_API.G_MISS_CHAR)
325 , nvl(ship_to_address3, FND_API.G_MISS_CHAR)
326 , nvl(ship_to_address4, FND_API.G_MISS_CHAR)
327 , nvl(ship_to_city, FND_API.G_MISS_CHAR)
328 , nvl(ship_to_state, FND_API.G_MISS_CHAR)
329 , nvl(ship_to_postal_code, FND_API.G_MISS_CHAR)
330 , nvl(ship_to_country, FND_API.G_MISS_CHAR)
331 , nvl(invoice_address1, FND_API.G_MISS_CHAR)
332 , nvl(invoice_address2, FND_API.G_MISS_CHAR)
333 , nvl(invoice_address3, FND_API.G_MISS_CHAR)
334 , nvl(invoice_address4, FND_API.G_MISS_CHAR)
335 , nvl(invoice_city, FND_API.G_MISS_CHAR)
336 , nvl(invoice_state, FND_API.G_MISS_CHAR)
337 , nvl(invoice_postal_code, FND_API.G_MISS_CHAR)
338 , nvl(invoice_country, FND_API.G_MISS_CHAR)
339 , nvl(drop_ship_flag, FND_API.G_MISS_CHAR)
340 , nvl(booked_flag, 'Y')
341 -- , nvl(closed_flag, FND_API.G_MISS_CHAR)
342 , nvl(cancelled_flag, 'N')
343 , nvl(context, FND_API.G_MISS_CHAR)
344 , nvl(attribute1, FND_API.G_MISS_CHAR)
345 , nvl(attribute2, FND_API.G_MISS_CHAR)
346 , nvl(attribute3, FND_API.G_MISS_CHAR)
347 , nvl(attribute4, FND_API.G_MISS_CHAR)
348 , nvl(attribute5, FND_API.G_MISS_CHAR)
349 , nvl(attribute6, FND_API.G_MISS_CHAR)
350 , nvl(attribute7, FND_API.G_MISS_CHAR)
351 , nvl(attribute8, FND_API.G_MISS_CHAR)
352 , nvl(attribute9, FND_API.G_MISS_CHAR)
353 , nvl(attribute10, FND_API.G_MISS_CHAR)
354 , nvl(attribute11, FND_API.G_MISS_CHAR)
355 , nvl(attribute12, FND_API.G_MISS_CHAR)
356 , nvl(attribute13, FND_API.G_MISS_CHAR)
357 , nvl(attribute14, FND_API.G_MISS_CHAR)
358 , nvl(attribute15, FND_API.G_MISS_CHAR)
359 /* Added Attribute 16 to 20 for bug 3471009 */
360 , nvl(attribute16, FND_API.G_MISS_CHAR)
361 , nvl(attribute17, FND_API.G_MISS_CHAR)
362 , nvl(attribute18, FND_API.G_MISS_CHAR)
363 , nvl(attribute19, FND_API.G_MISS_CHAR)
364 , nvl(attribute20, FND_API.G_MISS_CHAR)
365 , nvl(tp_context, FND_API.G_MISS_CHAR)
366 , nvl(tp_attribute1, FND_API.G_MISS_CHAR)
367 , nvl(tp_attribute2, FND_API.G_MISS_CHAR)
368 , nvl(tp_attribute3, FND_API.G_MISS_CHAR)
369 , nvl(tp_attribute4, FND_API.G_MISS_CHAR)
370 , nvl(tp_attribute5, FND_API.G_MISS_CHAR)
371 , nvl(tp_attribute6, FND_API.G_MISS_CHAR)
372 , nvl(tp_attribute7, FND_API.G_MISS_CHAR)
373 , nvl(tp_attribute8, FND_API.G_MISS_CHAR)
374 , nvl(tp_attribute9, FND_API.G_MISS_CHAR)
375 , nvl(tp_attribute10, FND_API.G_MISS_CHAR)
376 , nvl(tp_attribute11, FND_API.G_MISS_CHAR)
377 , nvl(tp_attribute12, FND_API.G_MISS_CHAR)
378 , nvl(tp_attribute13, FND_API.G_MISS_CHAR)
379 , nvl(tp_attribute14, FND_API.G_MISS_CHAR)
380 , nvl(tp_attribute15, FND_API.G_MISS_CHAR)
381 , nvl(global_attribute_category, FND_API.G_MISS_CHAR)
382 , nvl(global_attribute1, FND_API.G_MISS_CHAR)
383 , nvl(global_attribute2, FND_API.G_MISS_CHAR)
384 , nvl(global_attribute3, FND_API.G_MISS_CHAR)
385 , nvl(global_attribute4, FND_API.G_MISS_CHAR)
386 , nvl(global_attribute5, FND_API.G_MISS_CHAR)
387 , nvl(global_attribute6, FND_API.G_MISS_CHAR)
388 , nvl(global_attribute7, FND_API.G_MISS_CHAR)
389 , nvl(global_attribute8, FND_API.G_MISS_CHAR)
390 , nvl(global_attribute9, FND_API.G_MISS_CHAR)
391 , nvl(global_attribute10, FND_API.G_MISS_CHAR)
392 , nvl(global_attribute11, FND_API.G_MISS_CHAR)
393 , nvl(global_attribute12, FND_API.G_MISS_CHAR)
394 , nvl(global_attribute13, FND_API.G_MISS_CHAR)
395 , nvl(global_attribute14, FND_API.G_MISS_CHAR)
396 , nvl(global_attribute15, FND_API.G_MISS_CHAR)
397 , nvl(global_attribute16, FND_API.G_MISS_CHAR)
398 , nvl(global_attribute17, FND_API.G_MISS_CHAR)
399 , nvl(global_attribute18, FND_API.G_MISS_CHAR)
400 , nvl(global_attribute19, FND_API.G_MISS_CHAR)
401 , nvl(global_attribute20, FND_API.G_MISS_CHAR)
402 , request_id
403 , NVL(request_date, FND_API.G_MISS_DATE)
404 , nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
405 , nvl(ready_flag, 'Y')
406 , nvl(status_flag, '0')
407 , nvl(force_apply_flag, 'N')
408 , nvl(change_reason, FND_API.G_MISS_CHAR)
409 , nvl(change_comments, FND_API.G_MISS_CHAR)
410 , 'N'
411 , nvl(customer_preference_set_code, FND_API.G_MISS_CHAR)
412 , nvl(sold_to_site_use_id, FND_API.G_MISS_NUM)
413 , nvl(sold_to_location_address1, FND_API.G_MISS_CHAR)
414 , nvl(sold_to_location_address2, FND_API.G_MISS_CHAR)
415 , nvl(sold_to_location_address3, FND_API.G_MISS_CHAR)
416 , nvl(sold_to_location_address4, FND_API.G_MISS_CHAR)
417 , nvl(sold_to_location_city, FND_API.G_MISS_CHAR)
418 , nvl(sold_to_location_postal_code, FND_API.G_MISS_CHAR)
419 , nvl(sold_to_location_country, FND_API.G_MISS_CHAR)
420 , nvl(sold_to_location_state, FND_API.G_MISS_CHAR)
421 , nvl(sold_to_location_county, FND_API.G_MISS_CHAR)
422 , nvl(sold_to_location_province, FND_API.G_MISS_CHAR)
423 -- start if additional quoting columns
424 , nvl(transaction_phase_code, FND_API.G_MISS_CHAR)
425 , nvl(expiration_date, FND_API.G_MISS_DATE)
426 , nvl(quote_number, FND_API.G_MISS_NUM)
427 , nvl(quote_date, FND_API.G_MISS_DATE)
428 , nvl(sales_document_name, FND_API.G_MISS_CHAR)
429 , nvl(user_status_code, FND_API.G_MISS_CHAR)
430 -- end of additional quoting columns
431 -- { Distributer Order related change
432 , nvl(end_customer_id, FND_API.G_MISS_NUM)
433 , nvl(end_customer_contact_id, FND_API.G_MISS_NUM)
434 , nvl(end_customer_site_use_id, FND_API.G_MISS_NUM)
435 --{added for bug 4240715
436 , nvl(end_customer_name, FND_API.G_MISS_CHAR)
437 , nvl(end_customer_address1, FND_API.G_MISS_CHAR)
438 , nvl(end_customer_address2, FND_API.G_MISS_CHAR)
439 , nvl(end_customer_address3, FND_API.G_MISS_CHAR)
440 , nvl(end_customer_address4, FND_API.G_MISS_CHAR)
441 -- , nvl(end_customer_location, FND_API.G_MISS_CHAR)
442 , nvl(end_customer_city, FND_API.G_MISS_CHAR)
443 , nvl(end_customer_state, FND_API.G_MISS_CHAR)
444 , nvl(end_customer_postal_code, FND_API.G_MISS_CHAR)
445 , nvl(end_customer_country, FND_API.G_MISS_CHAR)
446 , nvl(end_customer_contact, FND_API.G_MISS_CHAR)
447 , nvl(end_customer_number, FND_API.G_MISS_CHAR)
448 -- bug 4240715}
449 , nvl(ib_owner_code, FND_API.G_MISS_CHAR)
450 , nvl(ib_current_location_code, FND_API.G_MISS_CHAR)
451 , nvl(ib_installed_at_location_code, FND_API.G_MISS_CHAR)
452 , nvl(ib_owner, FND_API.G_MISS_CHAR)
453 , nvl(ib_current_location, FND_API.G_MISS_CHAR)
454 , nvl(ib_installed_at_location, FND_API.G_MISS_CHAR)
455 -- Distributer Order related change }
456 FROM oe_headers_iface_all
457 WHERE order_source_id = l_order_source_id
458 AND orig_sys_document_ref = l_orig_sys_document_ref
459 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
460 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
461 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
462 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
463 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
464 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
465 AND nvl(org_id, FND_API.G_MISS_NUM)
466 = nvl(l_org_id, FND_API.G_MISS_NUM)
467 AND nvl( request_id, FND_API.G_MISS_NUM)
468 = nvl(l_request_id, FND_API.G_MISS_NUM)
469 AND nvl(error_flag,'N') = 'N'
470 AND nvl(ready_flag,'Y') = 'Y'
471 AND nvl(rejected_flag,'N') = 'N'
472 AND nvl(force_apply_flag,'Y') = 'Y'
473 AND closed_flag = 'Y'
474 FOR UPDATE NOWAIT
475 ORDER BY org_id,order_source_id, orig_sys_document_ref, change_sequence
476 ;
477
478
479 /* -----------------------------------------------------------
480 Header Discounts/Price adjustments cursor
481 -----------------------------------------------------------
482 */
483 CURSOR l_header_adj_cursor IS
484 SELECT nvl(orig_sys_discount_ref, FND_API.G_MISS_CHAR)
485 , nvl(change_request_code, FND_API.G_MISS_CHAR)
486 , nvl(list_header_id, FND_API.G_MISS_NUM)
487 , nvl(list_line_id, FND_API.G_MISS_NUM)
488 , nvl(discount_name, FND_API.G_MISS_CHAR)
489 , nvl(percent, FND_API.G_MISS_NUM)
490 , nvl(automatic_flag, FND_API.G_MISS_CHAR)
491 , nvl(applied_flag, FND_API.G_MISS_CHAR)
492 , nvl(operand, FND_API.G_MISS_NUM)
493 , nvl(arithmetic_operator, FND_API.G_MISS_CHAR)
494 , nvl(context, FND_API.G_MISS_CHAR)
495 , nvl(attribute1, FND_API.G_MISS_CHAR)
496 , nvl(attribute2, FND_API.G_MISS_CHAR)
497 , nvl(attribute3, FND_API.G_MISS_CHAR)
498 , nvl(attribute4, FND_API.G_MISS_CHAR)
499 , nvl(attribute5, FND_API.G_MISS_CHAR)
500 , nvl(attribute6, FND_API.G_MISS_CHAR)
501 , nvl(attribute7, FND_API.G_MISS_CHAR)
502 , nvl(attribute8, FND_API.G_MISS_CHAR)
503 , nvl(attribute9, FND_API.G_MISS_CHAR)
504 , nvl(attribute10, FND_API.G_MISS_CHAR)
505 , nvl(attribute11, FND_API.G_MISS_CHAR)
506 , nvl(attribute12, FND_API.G_MISS_CHAR)
507 , nvl(attribute13, FND_API.G_MISS_CHAR)
508 , nvl(attribute14, FND_API.G_MISS_CHAR)
509 , nvl(attribute15, FND_API.G_MISS_CHAR)
510 , request_id
511 , nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
512 , nvl(status_flag, FND_API.G_MISS_CHAR)
513 -- Price Adjustment related changes bug# 1220921 (Start)
514 , nvl( AC_CONTEXT, FND_API.G_MISS_CHAR)
515 , nvl( AC_ATTRIBUTE1, FND_API.G_MISS_CHAR)
516 , nvl( AC_ATTRIBUTE2, FND_API.G_MISS_CHAR)
517 , nvl( AC_ATTRIBUTE3, FND_API.G_MISS_CHAR)
518 , nvl( AC_ATTRIBUTE4, FND_API.G_MISS_CHAR)
519 , nvl( AC_ATTRIBUTE5, FND_API.G_MISS_CHAR)
520 , nvl( AC_ATTRIBUTE6, FND_API.G_MISS_CHAR)
521 , nvl( AC_ATTRIBUTE7, FND_API.G_MISS_CHAR)
522 , nvl( AC_ATTRIBUTE8, FND_API.G_MISS_CHAR)
523 , nvl( AC_ATTRIBUTE9, FND_API.G_MISS_CHAR)
524 , nvl( AC_ATTRIBUTE10, FND_API.G_MISS_CHAR)
525 , nvl( AC_ATTRIBUTE11, FND_API.G_MISS_CHAR)
526 , nvl( AC_ATTRIBUTE12, FND_API.G_MISS_CHAR)
527 , nvl( AC_ATTRIBUTE13, FND_API.G_MISS_CHAR)
528 , nvl( AC_ATTRIBUTE14, FND_API.G_MISS_CHAR)
529 , nvl( AC_ATTRIBUTE15, FND_API.G_MISS_CHAR)
530 , nvl( LIST_NAME, FND_API.G_MISS_CHAR)
531 , nvl( LIST_LINE_TYPE_CODE, FND_API.G_MISS_CHAR)
532 , nvl( LIST_LINE_NUMBER, FND_API.G_MISS_CHAR)
533 , nvl( VERSION_NUMBER, FND_API.G_MISS_CHAR)
534 , nvl( INVOICED_FLAG, FND_API.G_MISS_CHAR)
535 , nvl( ESTIMATED_FLAG, FND_API.G_MISS_CHAR)
536 , nvl( INC_IN_SALES_PERFORMANCE, FND_API.G_MISS_CHAR)
537 , nvl( CHARGE_TYPE_CODE, FND_API.G_MISS_CHAR)
538 , nvl( CHARGE_SUBTYPE_CODE, FND_API.G_MISS_CHAR)
539 , nvl( CREDIT_OR_CHARGE_FLAG, FND_API.G_MISS_CHAR)
540 , nvl( INCLUDE_ON_RETURNS_FLAG, FND_API.G_MISS_CHAR)
541 , nvl( COST_ID, FND_API.G_MISS_NUM)
542 , nvl( TAX_CODE, FND_API.G_MISS_CHAR)
543 , nvl( PARENT_ADJUSTMENT_ID, FND_API.G_MISS_NUM)
544 , nvl(MODIFIER_MECHANISM_TYPE_CODE,FND_API.G_MISS_CHAR)
545 , nvl( MODIFIED_FROM, FND_API.G_MISS_CHAR)
546 , nvl( MODIFIED_TO, FND_API.G_MISS_CHAR)
547 , nvl( UPDATED_FLAG, FND_API.G_MISS_CHAR)
548 , nvl( UPDATE_ALLOWED, FND_API.G_MISS_CHAR)
549 , nvl( CHANGE_REASON_CODE, FND_API.G_MISS_CHAR)
550 , nvl( CHANGE_REASON_TEXT, FND_API.G_MISS_CHAR)
551 , nvl( PRICING_PHASE_ID, FND_API.G_MISS_NUM)
552 , nvl( ADJUSTED_AMOUNT, FND_API.G_MISS_NUM)
553 -- Price Adjustment related changes bug# 1220921 (End)
554 FROM oe_price_adjs_iface_all
555 WHERE order_source_id = l_order_source_id
556 AND orig_sys_document_ref = l_orig_sys_document_ref
557 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
558 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
559 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
560 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
561 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
562 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
563 AND nvl(org_id, FND_API.G_MISS_NUM)
564 = nvl(l_org_id, FND_API.G_MISS_NUM)
565 AND nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
566 = FND_API.G_MISS_CHAR
567 AND nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
568 = FND_API.G_MISS_CHAR
569 AND nvl( request_id, FND_API.G_MISS_NUM)
570 = nvl(l_request_id, FND_API.G_MISS_NUM)
571 FOR UPDATE NOWAIT
572 ORDER BY orig_sys_discount_ref
573 ;
574
575
576 /* -----------------------------------------------------------
577 Header Sales Credits cursor
578 -----------------------------------------------------------
579 */
580 CURSOR l_header_scredit_cursor IS
581 SELECT nvl(orig_sys_credit_ref, FND_API.G_MISS_CHAR)
582 , nvl(change_request_code, FND_API.G_MISS_CHAR)
583 , nvl(salesrep_id, FND_API.G_MISS_NUM)
584 , nvl(salesrep , FND_API.G_MISS_CHAR)
585 , nvl(sales_credit_type_id, FND_API.G_MISS_NUM)
586 , nvl(sales_credit_type, FND_API.G_MISS_CHAR)
587 , nvl(percent, FND_API.G_MISS_NUM)
588 , nvl(context, FND_API.G_MISS_CHAR)
589 , nvl(attribute1, FND_API.G_MISS_CHAR)
590 , nvl(attribute2, FND_API.G_MISS_CHAR)
591 , nvl(attribute3, FND_API.G_MISS_CHAR)
592 , nvl(attribute4, FND_API.G_MISS_CHAR)
593 , nvl(attribute5, FND_API.G_MISS_CHAR)
594 , nvl(attribute6, FND_API.G_MISS_CHAR)
595 , nvl(attribute7, FND_API.G_MISS_CHAR)
596 , nvl(attribute8, FND_API.G_MISS_CHAR)
597 , nvl(attribute9, FND_API.G_MISS_CHAR)
598 , nvl(attribute10, FND_API.G_MISS_CHAR)
599 , nvl(attribute11, FND_API.G_MISS_CHAR)
600 , nvl(attribute12, FND_API.G_MISS_CHAR)
601 , nvl(attribute13, FND_API.G_MISS_CHAR)
602 , nvl(attribute14, FND_API.G_MISS_CHAR)
603 , nvl(attribute15, FND_API.G_MISS_CHAR)
604 , nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
605 , nvl(status_flag, FND_API.G_MISS_CHAR)
606 FROM oe_credits_iface_all
607 WHERE order_source_id = l_order_source_id
608 AND orig_sys_document_ref = l_orig_sys_document_ref
609 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
610 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
611 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
612 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
613 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
614 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
615 AND nvl(org_id, FND_API.G_MISS_NUM)
616 = nvl(l_org_id, FND_API.G_MISS_NUM)
617 AND nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
618 = FND_API.G_MISS_CHAR
619 AND nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
620 = FND_API.G_MISS_CHAR
621 AND nvl( request_id, FND_API.G_MISS_NUM)
622 = nvl(l_request_id, FND_API.G_MISS_NUM)
623 FOR UPDATE NOWAIT
624 ORDER BY orig_sys_credit_ref
625 ;
626
627
628 /* -----------------------------------------------------------
629 Lines cursor
630 -----------------------------------------------------------
631 */
632 CURSOR l_line_cursor IS
633 SELECT nvl(order_source_id, FND_API.G_MISS_NUM)
634 , nvl(orig_sys_document_ref, FND_API.G_MISS_CHAR)
635 , nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
636 , nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
637 , nvl(change_request_code, FND_API.G_MISS_CHAR)
638 , nvl(org_id, FND_API.G_MISS_NUM)
639 , nvl(line_number, FND_API.G_MISS_NUM)
640 , nvl(shipment_number, FND_API.G_MISS_NUM)
641 , nvl(line_id, FND_API.G_MISS_NUM)
642 , nvl(line_type_id, FND_API.G_MISS_NUM)
643 , nvl(line_type, FND_API.G_MISS_CHAR)
644 , nvl(item_type_code, FND_API.G_MISS_CHAR)
645 , nvl(inventory_item_id, FND_API.G_MISS_NUM)
646 , nvl(inventory_item, FND_API.G_MISS_CHAR)
647 , nvl(top_model_line_ref, FND_API.G_MISS_CHAR)
648 , nvl(link_to_line_ref, FND_API.G_MISS_CHAR)
649 , nvl(explosion_date, FND_API.G_MISS_DATE)
650 , nvl(ato_line_id, FND_API.G_MISS_NUM)
651 , nvl(component_sequence_id, FND_API.G_MISS_NUM)
652 , nvl(component_code, FND_API.G_MISS_CHAR)
653 , nvl(sort_order, FND_API.G_MISS_CHAR)
654 , nvl(model_group_number, FND_API.G_MISS_NUM)
655 , nvl(option_number, FND_API.G_MISS_NUM)
656 , nvl(option_flag, 'N')
657 , nvl(ship_model_complete_flag, FND_API.G_MISS_CHAR)
658 , nvl(source_type_code, FND_API.G_MISS_CHAR)
659 , nvl(schedule_status_code, FND_API.G_MISS_CHAR)
660 , nvl(schedule_ship_date, FND_API.G_MISS_DATE)
661 , nvl(schedule_arrival_date, FND_API.G_MISS_DATE)
662 , nvl(actual_arrival_date, FND_API.G_MISS_DATE)
663 -- bug 3220711 - start
664 , nvl(actual_shipment_date, FND_API.G_MISS_DATE)
665 -- bug 3220711 - end
666 , nvl(request_date, FND_API.G_MISS_DATE)
667 , nvl(promise_date, FND_API.G_MISS_DATE)
668 , nvl(delivery_lead_time, FND_API.G_MISS_NUM)
669 , nvl(ordered_quantity, FND_API.G_MISS_NUM)
670 , nvl(order_quantity_uom , FND_API.G_MISS_CHAR)
671 , nvl(shipping_quantity, FND_API.G_MISS_NUM)
672 , nvl(shipping_quantity_uom, FND_API.G_MISS_CHAR)
673 , nvl(shipped_quantity, FND_API.G_MISS_NUM)
674 , nvl(cancelled_quantity, FND_API.G_MISS_NUM)
675 , nvl(fulfilled_quantity, FND_API.G_MISS_NUM)
676 /* OPM variables */ -- INVCONV
677 , nvl(ordered_quantity2, FND_API.G_MISS_NUM)
678 , nvl(ordered_quantity_uom2 , FND_API.G_MISS_CHAR)
679 , nvl(shipping_quantity2, FND_API.G_MISS_NUM)
680 , nvl(shipping_quantity_uom2, FND_API.G_MISS_CHAR)
681 , nvl(shipped_quantity2, FND_API.G_MISS_NUM)
682 , nvl(cancelled_quantity2, FND_API.G_MISS_NUM)
683 , nvl(fulfilled_quantity2, FND_API.G_MISS_NUM)
684 , nvl(preferred_grade, FND_API.G_MISS_CHAR)
685 , nvl(pricing_quantity, FND_API.G_MISS_NUM)
686 , nvl(pricing_quantity_uom, FND_API.G_MISS_CHAR)
687 , nvl(sold_from_org_id, FND_API.G_MISS_NUM)
688 , nvl(sold_from_org, FND_API.G_MISS_CHAR)
689 , nvl(sold_to_org_id , FND_API.G_MISS_NUM)
690 , nvl(sold_to_org, FND_API.G_MISS_CHAR)
691 , nvl(ship_from_org_id, FND_API.G_MISS_NUM)
692 , nvl(ship_from_org, FND_API.G_MISS_CHAR)
693 , nvl(ship_to_org_id , FND_API.G_MISS_NUM)
694 , nvl(ship_to_org, FND_API.G_MISS_CHAR)
695 , nvl(deliver_to_org_id, FND_API.G_MISS_NUM)
696 , nvl(deliver_to_org, FND_API.G_MISS_CHAR)
697 , nvl(invoice_to_org_id, FND_API.G_MISS_NUM)
698 , nvl(invoice_to_org, FND_API.G_MISS_CHAR)
699 , nvl(ship_to_address1, FND_API.G_MISS_CHAR)
700 , nvl(ship_to_address2, FND_API.G_MISS_CHAR)
701 , nvl(ship_to_address3, FND_API.G_MISS_CHAR)
702 , nvl(ship_to_address4, FND_API.G_MISS_CHAR)
703 , nvl(ship_to_city, FND_API.G_MISS_CHAR)
704 , nvl(ship_to_state, FND_API.G_MISS_CHAR)
705 , nvl(ship_to_postal_code, FND_API.G_MISS_CHAR)
706 , nvl(ship_to_country, FND_API.G_MISS_CHAR)
707 , nvl(ship_to_contact_id, FND_API.G_MISS_NUM)
708 , nvl(ship_to_contact, FND_API.G_MISS_CHAR)
709 , nvl(deliver_to_contact_id, FND_API.G_MISS_NUM)
710 , nvl(deliver_to_contact, FND_API.G_MISS_CHAR)
711 , nvl(invoice_to_contact_id, FND_API.G_MISS_NUM)
712 , nvl(invoice_to_contact, FND_API.G_MISS_CHAR)
713 , nvl(invoice_to_contact, FND_API.G_MISS_CHAR)
714 , nvl(ship_tolerance_above, FND_API.G_MISS_NUM)
715 , nvl(drop_ship_flag, FND_API.G_MISS_NUM)
716 , nvl(price_list_id, FND_API.G_MISS_NUM)
717 , nvl(price_list, FND_API.G_MISS_CHAR)
718 , nvl(pricing_date, FND_API.G_MISS_DATE)
719 , nvl(unit_list_price, FND_API.G_MISS_NUM)
720 , nvl(unit_selling_price, FND_API.G_MISS_NUM)
721 , nvl(calculate_price_flag, 'Y')
722 , nvl(ship_set_id, FND_API.G_MISS_NUM)
723 , nvl(ship_set_name, FND_API.G_MISS_CHAR)
724 , nvl(arrival_set_id, FND_API.G_MISS_NUM)
725 , nvl(arrival_set_name, FND_API.G_MISS_CHAR)
726 , nvl(fulfillment_set_id, FND_API.G_MISS_NUM)
727 , nvl(fulfillment_set_name, FND_API.G_MISS_CHAR)
728 , nvl(tax_code, FND_API.G_MISS_CHAR)
729 , nvl(tax_value, FND_API.G_MISS_NUM)
730 , nvl(tax_date, FND_API.G_MISS_DATE)
731 , nvl(tax_point_code, FND_API.G_MISS_CHAR)
732 , nvl(tax_point, FND_API.G_MISS_CHAR)
733 , nvl(tax_exempt_flag, FND_API.G_MISS_CHAR)
734 , nvl(tax_exempt_number, FND_API.G_MISS_CHAR)
735 , nvl(tax_exempt_reason_code, FND_API.G_MISS_CHAR)
736 , nvl(tax_exempt_reason, FND_API.G_MISS_CHAR)
737 , nvl(agreement_id, FND_API.G_MISS_NUM)
738 , nvl(agreement, FND_API.G_MISS_CHAR)
739 , nvl(invoicing_rule_id, FND_API.G_MISS_NUM)
740 , nvl(invoicing_rule, FND_API.G_MISS_CHAR)
741 , nvl(accounting_rule_id, FND_API.G_MISS_NUM)
742 , nvl(accounting_rule, FND_API.G_MISS_CHAR)
743 , nvl(payment_term_id, FND_API.G_MISS_NUM)
744 , nvl(payment_term, FND_API.G_MISS_CHAR)
745 , nvl(demand_class_code, FND_API.G_MISS_CHAR)
746 , nvl(shipment_priority_code, FND_API.G_MISS_CHAR)
747 , nvl(shipment_priority, FND_API.G_MISS_CHAR)
748 , nvl(shipping_method_code, FND_API.G_MISS_CHAR)
749 , nvl(shipping_instructions, FND_API.G_MISS_CHAR)
750 , nvl(packing_instructions, FND_API.G_MISS_CHAR)
751 , nvl(freight_carrier_code, FND_API.G_MISS_CHAR)
752 , nvl(freight_terms_code, FND_API.G_MISS_CHAR)
753 , nvl(freight_terms, FND_API.G_MISS_CHAR)
754 , nvl(fob_point_code, FND_API.G_MISS_CHAR)
755 , nvl(fob_point, FND_API.G_MISS_CHAR)
756 , nvl(return_reason_code, FND_API.G_MISS_CHAR)
757 , nvl(reference_type, FND_API.G_MISS_CHAR)
758 , nvl(reference_header_id, FND_API.G_MISS_NUM)
759 , nvl(reference_line_id, FND_API.G_MISS_NUM)
760 , nvl(credit_invoice_line_id, FND_API.G_MISS_NUM)
761 , nvl(customer_po_number, FND_API.G_MISS_CHAR)
762 , nvl(customer_line_number, FND_API.G_MISS_CHAR)
763 , nvl(customer_shipment_number, FND_API.G_MISS_CHAR)
764 , nvl(customer_item_id, FND_API.G_MISS_NUM)
765 , nvl(customer_item_id_type, FND_API.G_MISS_CHAR)
766 , nvl(customer_item_name, FND_API.G_MISS_CHAR)
767 -- , nvl(customer_item_revision, FND_API.G_MISS_CHAR)
768 , nvl(customer_item_net_price, FND_API.G_MISS_NUM)
769 , nvl(customer_payment_term_id, FND_API.G_MISS_NUM)
770 , nvl(customer_payment_term, FND_API.G_MISS_NUM)
771 , nvl(demand_bucket_type_code, FND_API.G_MISS_CHAR)
772 , nvl(demand_bucket_type, FND_API.G_MISS_CHAR)
773 , nvl(customer_dock_code, FND_API.G_MISS_CHAR)
774 , nvl(customer_job, FND_API.G_MISS_CHAR)
775 , nvl(customer_production_line, FND_API.G_MISS_CHAR)
776 , nvl(cust_model_serial_number, FND_API.G_MISS_CHAR)
777 , nvl(project_id, FND_API.G_MISS_NUM)
778 , nvl(project, FND_API.G_MISS_CHAR)
779 , nvl(task_id, FND_API.G_MISS_NUM)
780 , nvl(task, FND_API.G_MISS_CHAR)
781 , nvl(end_item_unit_number, FND_API.G_MISS_CHAR)
782 , nvl(item_revision, FND_API.G_MISS_CHAR)
783 , nvl(service_duration, FND_API.G_MISS_NUM)
784 , nvl(service_period, FND_API.G_MISS_CHAR)
785 , nvl(service_start_date, FND_API.G_MISS_DATE)
786 , nvl(service_end_date, FND_API.G_MISS_DATE)
787 , nvl(service_coterminate_flag, FND_API.G_MISS_CHAR)
788 , nvl(unit_selling_percent, FND_API.G_MISS_NUM)
789 , nvl(unit_list_percent, FND_API.G_MISS_NUM)
790 , nvl(unit_percent_base_price, FND_API.G_MISS_NUM)
791 , nvl(service_number, FND_API.G_MISS_NUM)
792 -- , nvl(fulfilled_flag, FND_API.G_MISS_CHAR)--ToBeAdded
793 -- , nvl(closed_flag, FND_API.G_MISS_CHAR)
794 , nvl(cancelled_flag, 'N')
795 , nvl(context, FND_API.G_MISS_CHAR)
796 , nvl(attribute1, FND_API.G_MISS_CHAR)
797 , nvl(attribute2, FND_API.G_MISS_CHAR)
798 , nvl(attribute3, FND_API.G_MISS_CHAR)
799 , nvl(attribute4, FND_API.G_MISS_CHAR)
800 , nvl(attribute5, FND_API.G_MISS_CHAR)
801 , nvl(attribute6, FND_API.G_MISS_CHAR)
802 , nvl(attribute7, FND_API.G_MISS_CHAR)
803 , nvl(attribute8, FND_API.G_MISS_CHAR)
804 , nvl(attribute9, FND_API.G_MISS_CHAR)
805 , nvl(attribute10, FND_API.G_MISS_CHAR)
806 , nvl(attribute11, FND_API.G_MISS_CHAR)
807 , nvl(attribute12, FND_API.G_MISS_CHAR)
808 , nvl(attribute13, FND_API.G_MISS_CHAR)
809 , nvl(attribute14, FND_API.G_MISS_CHAR)
810 , nvl(attribute15, FND_API.G_MISS_CHAR)
811 /* Added Attribute 16 to 20 for the bug 3513248 */
812 , nvl(attribute16, FND_API.G_MISS_CHAR)
813 , nvl(attribute17, FND_API.G_MISS_CHAR)
814 , nvl(attribute18, FND_API.G_MISS_CHAR)
815 , nvl(attribute19, FND_API.G_MISS_CHAR)
816 , nvl(attribute20, FND_API.G_MISS_CHAR)
817 , nvl(tp_context, FND_API.G_MISS_CHAR)
818 , nvl(tp_attribute1, FND_API.G_MISS_CHAR)
819 , nvl(tp_attribute2, FND_API.G_MISS_CHAR)
820 , nvl(tp_attribute3, FND_API.G_MISS_CHAR)
821 , nvl(tp_attribute4, FND_API.G_MISS_CHAR)
822 , nvl(tp_attribute5, FND_API.G_MISS_CHAR)
823 , nvl(tp_attribute6, FND_API.G_MISS_CHAR)
824 , nvl(tp_attribute7, FND_API.G_MISS_CHAR)
825 , nvl(tp_attribute8, FND_API.G_MISS_CHAR)
826 , nvl(tp_attribute9, FND_API.G_MISS_CHAR)
827 , nvl(tp_attribute10, FND_API.G_MISS_CHAR)
828 , nvl(tp_attribute11, FND_API.G_MISS_CHAR)
829 , nvl(tp_attribute12, FND_API.G_MISS_CHAR)
830 , nvl(tp_attribute13, FND_API.G_MISS_CHAR)
831 , nvl(tp_attribute14, FND_API.G_MISS_CHAR)
832 , nvl(tp_attribute15, FND_API.G_MISS_CHAR)
833 , nvl(industry_context, FND_API.G_MISS_CHAR)
834 , nvl(industry_attribute1, FND_API.G_MISS_CHAR)
835 , nvl(industry_attribute2, FND_API.G_MISS_CHAR)
836 , nvl(industry_attribute3, FND_API.G_MISS_CHAR)
837 , nvl(industry_attribute4, FND_API.G_MISS_CHAR)
838 , nvl(industry_attribute5, FND_API.G_MISS_CHAR)
839 , nvl(industry_attribute6, FND_API.G_MISS_CHAR)
840 , nvl(industry_attribute7, FND_API.G_MISS_CHAR)
841 , nvl(industry_attribute8, FND_API.G_MISS_CHAR)
842 , nvl(industry_attribute9, FND_API.G_MISS_CHAR)
843 , nvl(industry_attribute10, FND_API.G_MISS_CHAR)
844 , nvl(industry_attribute11, FND_API.G_MISS_CHAR)
845 , nvl(industry_attribute12, FND_API.G_MISS_CHAR)
846 , nvl(industry_attribute13, FND_API.G_MISS_CHAR)
847 , nvl(industry_attribute14, FND_API.G_MISS_CHAR)
848 , nvl(industry_attribute15, FND_API.G_MISS_CHAR)
849 , nvl(industry_attribute16, FND_API.G_MISS_CHAR)
850 , nvl(industry_attribute17, FND_API.G_MISS_CHAR)
851 , nvl(industry_attribute18, FND_API.G_MISS_CHAR)
852 , nvl(industry_attribute19, FND_API.G_MISS_CHAR)
853 , nvl(industry_attribute20, FND_API.G_MISS_CHAR)
854 , nvl(industry_attribute21, FND_API.G_MISS_CHAR)
855 , nvl(industry_attribute22, FND_API.G_MISS_CHAR)
856 , nvl(industry_attribute23, FND_API.G_MISS_CHAR)
857 , nvl(industry_attribute24, FND_API.G_MISS_CHAR)
858 , nvl(industry_attribute25, FND_API.G_MISS_CHAR)
859 , nvl(industry_attribute26, FND_API.G_MISS_CHAR)
860 , nvl(industry_attribute27, FND_API.G_MISS_CHAR)
861 , nvl(industry_attribute28, FND_API.G_MISS_CHAR)
862 , nvl(industry_attribute29, FND_API.G_MISS_CHAR)
863 , nvl(industry_attribute30, FND_API.G_MISS_CHAR)
864 , nvl(pricing_context, FND_API.G_MISS_CHAR)
865 , nvl(pricing_attribute1, FND_API.G_MISS_CHAR)
866 , nvl(pricing_attribute2, FND_API.G_MISS_CHAR)
867 , nvl(pricing_attribute3, FND_API.G_MISS_CHAR)
868 , nvl(pricing_attribute4, FND_API.G_MISS_CHAR)
869 , nvl(pricing_attribute5, FND_API.G_MISS_CHAR)
870 , nvl(pricing_attribute6, FND_API.G_MISS_CHAR)
871 , nvl(pricing_attribute7, FND_API.G_MISS_CHAR)
872 , nvl(pricing_attribute8, FND_API.G_MISS_CHAR)
873 , nvl(pricing_attribute9, FND_API.G_MISS_CHAR)
874 , nvl(pricing_attribute10, FND_API.G_MISS_CHAR)
875 , nvl(global_attribute_category, FND_API.G_MISS_CHAR)
876 , nvl(global_attribute1, FND_API.G_MISS_CHAR)
877 , nvl(global_attribute2, FND_API.G_MISS_CHAR)
878 , nvl(global_attribute3, FND_API.G_MISS_CHAR)
879 , nvl(global_attribute4, FND_API.G_MISS_CHAR)
880 , nvl(global_attribute5, FND_API.G_MISS_CHAR)
881 , nvl(global_attribute6, FND_API.G_MISS_CHAR)
882 , nvl(global_attribute7, FND_API.G_MISS_CHAR)
883 , nvl(global_attribute8, FND_API.G_MISS_CHAR)
884 , nvl(global_attribute9, FND_API.G_MISS_CHAR)
885 , nvl(global_attribute10, FND_API.G_MISS_CHAR)
886 , nvl(global_attribute11, FND_API.G_MISS_CHAR)
887 , nvl(global_attribute12, FND_API.G_MISS_CHAR)
888 , nvl(global_attribute13, FND_API.G_MISS_CHAR)
889 , nvl(global_attribute14, FND_API.G_MISS_CHAR)
890 , nvl(global_attribute15, FND_API.G_MISS_CHAR)
891 , nvl(global_attribute16, FND_API.G_MISS_CHAR)
892 , nvl(global_attribute17, FND_API.G_MISS_CHAR)
893 , nvl(global_attribute18, FND_API.G_MISS_CHAR)
894 , nvl(global_attribute19, FND_API.G_MISS_CHAR)
895 , nvl(global_attribute20, FND_API.G_MISS_CHAR)
896 , nvl(return_context, FND_API.G_MISS_CHAR)
897 , nvl(return_attribute1, FND_API.G_MISS_CHAR)
898 , nvl(return_attribute2, FND_API.G_MISS_CHAR)
899 , nvl(return_attribute3, FND_API.G_MISS_CHAR)
900 , nvl(return_attribute4, FND_API.G_MISS_CHAR)
901 , nvl(return_attribute5, FND_API.G_MISS_CHAR)
902 , nvl(return_attribute6, FND_API.G_MISS_CHAR)
903 , nvl(return_attribute7, FND_API.G_MISS_CHAR)
904 , nvl(return_attribute8, FND_API.G_MISS_CHAR)
905 , nvl(return_attribute9, FND_API.G_MISS_CHAR)
906 , nvl(return_attribute10, FND_API.G_MISS_CHAR)
907 , nvl(return_attribute11, FND_API.G_MISS_CHAR)
908 , nvl(return_attribute12, FND_API.G_MISS_CHAR)
909 , nvl(return_attribute13, FND_API.G_MISS_CHAR)
910 , nvl(return_attribute14, FND_API.G_MISS_CHAR)
911 , nvl(return_attribute15, FND_API.G_MISS_CHAR)
912 , request_id
913 , nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
914 , nvl(status_flag, FND_API.G_MISS_CHAR)
915 , nvl(change_reason, FND_API.G_MISS_CHAR)
916 , nvl(change_comments, FND_API.G_MISS_CHAR)
917 , nvl(service_txn_reason_code, FND_API.G_MISS_CHAR)
918 , nvl(service_txn_comments, FND_API.G_MISS_CHAR)
919 , nvl(service_reference_type_code,FND_API.G_MISS_CHAR)
920 , nvl(service_reference_order, FND_API.G_MISS_CHAR)
921 , nvl(service_reference_line, FND_API.G_MISS_CHAR)
922 , nvl(service_reference_system, FND_API.G_MISS_CHAR)
923 , INVENTORY_ITEM_SEGMENT_1
924 , INVENTORY_ITEM_SEGMENT_2
925 , INVENTORY_ITEM_SEGMENT_3
926 , INVENTORY_ITEM_SEGMENT_4
927 , INVENTORY_ITEM_SEGMENT_5
928 , INVENTORY_ITEM_SEGMENT_6
929 , INVENTORY_ITEM_SEGMENT_7
930 , INVENTORY_ITEM_SEGMENT_8
931 , INVENTORY_ITEM_SEGMENT_9
932 , INVENTORY_ITEM_SEGMENT_10
933 , INVENTORY_ITEM_SEGMENT_11
934 , INVENTORY_ITEM_SEGMENT_12
935 , INVENTORY_ITEM_SEGMENT_13
936 , INVENTORY_ITEM_SEGMENT_14
937 , INVENTORY_ITEM_SEGMENT_15
938 , INVENTORY_ITEM_SEGMENT_16
939 , INVENTORY_ITEM_SEGMENT_17
940 , INVENTORY_ITEM_SEGMENT_18
941 , INVENTORY_ITEM_SEGMENT_19
942 , INVENTORY_ITEM_SEGMENT_20
943 , nvl(commitment, FND_API.G_MISS_CHAR)
944 , nvl(commitment_id, FND_API.G_MISS_NUM)
945 -- aksingh subinventory
946 , nvl(subinventory, FND_API.G_MISS_CHAR)
947 ,nvl(salesrep, FND_API.G_MISS_CHAR)
948 ,nvl(salesrep_id, FND_API.G_MISS_NUM)
949 , nvl(earliest_acceptable_date, FND_API.G_MISS_DATE)
950 , nvl(latest_acceptable_date,FND_API.G_MISS_DATE)
951 , nvl(invoice_to_address1, FND_API.G_MISS_CHAR)
952 , nvl(invoice_to_address2, FND_API.G_MISS_CHAR)
953 , nvl(invoice_to_address3, FND_API.G_MISS_CHAR)
954 , nvl(invoice_to_address4, FND_API.G_MISS_CHAR)
955 , nvl(invoice_to_city, FND_API.G_MISS_CHAR)
956 , nvl(invoice_to_state, FND_API.G_MISS_CHAR)
957 , nvl(invoice_to_postal_code, FND_API.G_MISS_CHAR)
958 , nvl(invoice_to_country, FND_API.G_MISS_CHAR)
959 , nvl(user_item_description, FND_API.G_MISS_CHAR)
960 , nvl(change_sequence, FND_API.G_MISS_CHAR)
961 -- { Distributer Order related change
962 , nvl(end_customer_id, FND_API.G_MISS_NUM)
963 , nvl(end_customer_contact_id, FND_API.G_MISS_NUM)
964 , nvl(end_customer_site_use_id, FND_API.G_MISS_NUM)
965 --{added for bug 4240715
966 , nvl(end_customer_name, FND_API.G_MISS_CHAR)
967 , nvl(end_customer_address1, FND_API.G_MISS_CHAR)
968 , nvl(end_customer_address2, FND_API.G_MISS_CHAR)
969 , nvl(end_customer_address3, FND_API.G_MISS_CHAR)
970 , nvl(end_customer_address4, FND_API.G_MISS_CHAR)
971 -- , nvl(end_customer_location, FND_API.G_MISS_CHAR)
972 , nvl(end_customer_city, FND_API.G_MISS_CHAR)
973 , nvl(end_customer_state, FND_API.G_MISS_CHAR)
974 , nvl(end_customer_postal_code, FND_API.G_MISS_CHAR)
975 , nvl(end_customer_country, FND_API.G_MISS_CHAR)
976 , nvl(end_customer_contact, FND_API.G_MISS_CHAR)
977 , nvl(end_customer_number, FND_API.G_MISS_CHAR)
978 --bug 4240715}
979 , nvl(ib_owner_code, FND_API.G_MISS_CHAR)
980 , nvl(ib_current_location_code, FND_API.G_MISS_CHAR)
981 , nvl(ib_installed_at_location_code, FND_API.G_MISS_CHAR)
982 , nvl(ib_owner, FND_API.G_MISS_CHAR)
983 , nvl(ib_current_location, FND_API.G_MISS_CHAR)
984 , nvl(ib_installed_at_location, FND_API.G_MISS_CHAR)
985 -- Distributer Order related change }
986 FROM oe_lines_iface_all
987 WHERE order_source_id = l_order_source_id
988 AND orig_sys_document_ref = l_orig_sys_document_ref
989 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
990 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
991 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
992 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
993 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
994 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
995 AND nvl(org_id, FND_API.G_MISS_NUM)
996 = nvl(l_org_id, FND_API.G_MISS_NUM)
997 AND nvl( request_id, FND_API.G_MISS_NUM)
998 = nvl(l_request_id, FND_API.G_MISS_NUM)
999 AND nvl(rejected_flag,'N') = 'N'
1000 FOR UPDATE NOWAIT
1001 ORDER BY orig_sys_line_ref, orig_sys_shipment_ref
1002 ;
1003
1004
1005 /* -----------------------------------------------------------
1006 Line Discounts/Price adjustments cursor
1007 -----------------------------------------------------------
1008 */
1009 CURSOR l_line_adj_cursor IS
1010 SELECT nvl(orig_sys_discount_ref, FND_API.G_MISS_CHAR)
1011 , nvl(change_request_code, FND_API.G_MISS_CHAR)
1012 , nvl(list_header_id, FND_API.G_MISS_NUM)
1013 , nvl(list_line_id, FND_API.G_MISS_NUM)
1014 , nvl(discount_name, FND_API.G_MISS_CHAR)
1015 , nvl(percent, FND_API.G_MISS_NUM)
1016 , nvl(automatic_flag, FND_API.G_MISS_CHAR)
1017 , nvl(applied_flag, FND_API.G_MISS_CHAR)
1018 , nvl(operand, FND_API.G_MISS_NUM)
1019 , nvl(arithmetic_operator, FND_API.G_MISS_CHAR)
1020 , nvl(context, FND_API.G_MISS_CHAR)
1021 , nvl(attribute1, FND_API.G_MISS_CHAR)
1022 , nvl(attribute2, FND_API.G_MISS_CHAR)
1023 , nvl(attribute3, FND_API.G_MISS_CHAR)
1024 , nvl(attribute4, FND_API.G_MISS_CHAR)
1025 , nvl(attribute5, FND_API.G_MISS_CHAR)
1026 , nvl(attribute6, FND_API.G_MISS_CHAR)
1027 , nvl(attribute7, FND_API.G_MISS_CHAR)
1028 , nvl(attribute8, FND_API.G_MISS_CHAR)
1029 , nvl(attribute9, FND_API.G_MISS_CHAR)
1030 , nvl(attribute10, FND_API.G_MISS_CHAR)
1031 , nvl(attribute11, FND_API.G_MISS_CHAR)
1032 , nvl(attribute12, FND_API.G_MISS_CHAR)
1033 , nvl(attribute13, FND_API.G_MISS_CHAR)
1034 , nvl(attribute14, FND_API.G_MISS_CHAR)
1035 , nvl(attribute15, FND_API.G_MISS_CHAR)
1036 , request_id
1037 , nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
1038 , nvl(status_flag, FND_API.G_MISS_CHAR)
1039 -- Price Adjustment related changes bug# 1220921 (Start)
1040 , nvl( AC_CONTEXT, FND_API.G_MISS_CHAR)
1041 , nvl( AC_ATTRIBUTE1, FND_API.G_MISS_CHAR)
1042 , nvl( AC_ATTRIBUTE2, FND_API.G_MISS_CHAR)
1043 , nvl( AC_ATTRIBUTE3, FND_API.G_MISS_CHAR)
1044 , nvl( AC_ATTRIBUTE4, FND_API.G_MISS_CHAR)
1045 , nvl( AC_ATTRIBUTE5, FND_API.G_MISS_CHAR)
1046 , nvl( AC_ATTRIBUTE6, FND_API.G_MISS_CHAR)
1047 , nvl( AC_ATTRIBUTE7, FND_API.G_MISS_CHAR)
1048 , nvl( AC_ATTRIBUTE8, FND_API.G_MISS_CHAR)
1049 , nvl( AC_ATTRIBUTE9, FND_API.G_MISS_CHAR)
1050 , nvl( AC_ATTRIBUTE10, FND_API.G_MISS_CHAR)
1051 , nvl( AC_ATTRIBUTE11, FND_API.G_MISS_CHAR)
1052 , nvl( AC_ATTRIBUTE12, FND_API.G_MISS_CHAR)
1053 , nvl( AC_ATTRIBUTE13, FND_API.G_MISS_CHAR)
1054 , nvl( AC_ATTRIBUTE14, FND_API.G_MISS_CHAR)
1055 , nvl( AC_ATTRIBUTE15, FND_API.G_MISS_CHAR)
1056 , nvl( LIST_NAME, FND_API.G_MISS_CHAR)
1057 , nvl( LIST_LINE_TYPE_CODE, FND_API.G_MISS_CHAR)
1058 , nvl( LIST_LINE_NUMBER, FND_API.G_MISS_CHAR)
1059 , nvl( VERSION_NUMBER, FND_API.G_MISS_CHAR)
1060 , nvl( INVOICED_FLAG, FND_API.G_MISS_CHAR)
1061 , nvl( ESTIMATED_FLAG, FND_API.G_MISS_CHAR)
1062 , nvl( INC_IN_SALES_PERFORMANCE, FND_API.G_MISS_CHAR)
1063 , nvl( CHARGE_TYPE_CODE, FND_API.G_MISS_CHAR)
1064 , nvl( CHARGE_SUBTYPE_CODE, FND_API.G_MISS_CHAR)
1065 , nvl( CREDIT_OR_CHARGE_FLAG, FND_API.G_MISS_CHAR)
1066 , nvl( INCLUDE_ON_RETURNS_FLAG, FND_API.G_MISS_CHAR)
1067 , nvl( COST_ID, FND_API.G_MISS_NUM)
1068 , nvl( TAX_CODE, FND_API.G_MISS_CHAR)
1069 , nvl( PARENT_ADJUSTMENT_ID, FND_API.G_MISS_NUM)
1070 , nvl(MODIFIER_MECHANISM_TYPE_CODE,FND_API.G_MISS_CHAR)
1071 , nvl( MODIFIED_FROM, FND_API.G_MISS_CHAR)
1072 , nvl( MODIFIED_TO, FND_API.G_MISS_CHAR)
1073 , nvl( UPDATED_FLAG, FND_API.G_MISS_CHAR)
1074 , nvl( UPDATE_ALLOWED, FND_API.G_MISS_CHAR)
1075 , nvl( CHANGE_REASON_CODE, FND_API.G_MISS_CHAR)
1076 , nvl( CHANGE_REASON_TEXT, FND_API.G_MISS_CHAR)
1077 , nvl( PRICING_PHASE_ID, FND_API.G_MISS_NUM)
1078 , nvl( ADJUSTED_AMOUNT, FND_API.G_MISS_NUM)
1079 , nvl( ORIG_SYS_LINE_REF, FND_API.G_MISS_CHAR)
1080 -- Price Adjustment related changes bug# 1220921 (End)
1081 FROM oe_price_adjs_iface_all
1082 WHERE order_source_id = l_order_source_id
1083 AND orig_sys_document_ref = l_orig_sys_document_ref
1084 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
1085 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
1086 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
1087 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
1088 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
1089 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
1090 AND nvl(org_id, FND_API.G_MISS_NUM)
1091 = nvl(l_org_id, FND_API.G_MISS_NUM)
1092 AND orig_sys_line_ref = l_orig_sys_line_ref
1093 AND nvl( orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
1094 = nvl(l_orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
1095 AND nvl( request_id, FND_API.G_MISS_NUM)
1096 = nvl(l_request_id, FND_API.G_MISS_NUM)
1097 FOR UPDATE NOWAIT
1098 ORDER BY orig_sys_discount_ref
1099 ;
1100
1101
1102 /* -----------------------------------------------------------
1103 Line Sales Credits cursor
1104 -----------------------------------------------------------
1105 */
1106 CURSOR l_line_scredit_cursor IS
1107 SELECT nvl(orig_sys_credit_ref, FND_API.G_MISS_CHAR)
1108 , nvl(change_request_code, FND_API.G_MISS_CHAR)
1109 , nvl(salesrep_id, FND_API.G_MISS_NUM)
1110 , nvl(salesrep , FND_API.G_MISS_CHAR)
1111 , nvl(sales_credit_type_id, FND_API.G_MISS_NUM)
1112 , nvl(sales_credit_type, FND_API.G_MISS_CHAR)
1113 , nvl(percent, FND_API.G_MISS_NUM)
1114 , nvl(context, FND_API.G_MISS_CHAR)
1115 , nvl(attribute1, FND_API.G_MISS_CHAR)
1116 , nvl(attribute2, FND_API.G_MISS_CHAR)
1117 , nvl(attribute3, FND_API.G_MISS_CHAR)
1118 , nvl(attribute4, FND_API.G_MISS_CHAR)
1119 , nvl(attribute5, FND_API.G_MISS_CHAR)
1120 , nvl(attribute6, FND_API.G_MISS_CHAR)
1121 , nvl(attribute7, FND_API.G_MISS_CHAR)
1122 , nvl(attribute8, FND_API.G_MISS_CHAR)
1123 , nvl(attribute9, FND_API.G_MISS_CHAR)
1124 , nvl(attribute10, FND_API.G_MISS_CHAR)
1125 , nvl(attribute11, FND_API.G_MISS_CHAR)
1126 , nvl(attribute12, FND_API.G_MISS_CHAR)
1127 , nvl(attribute13, FND_API.G_MISS_CHAR)
1128 , nvl(attribute14, FND_API.G_MISS_CHAR)
1129 , nvl(attribute15, FND_API.G_MISS_CHAR)
1130 , nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
1131 , nvl(status_flag, FND_API.G_MISS_CHAR)
1132 , nvl(ORIG_SYS_LINE_REF, FND_API.G_MISS_CHAR)
1133 FROM oe_credits_iface_all
1134 WHERE order_source_id = l_order_source_id
1135 AND orig_sys_document_ref = l_orig_sys_document_ref
1136 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
1137 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
1138 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
1139 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
1140 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
1141 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
1142 AND nvl(org_id, FND_API.G_MISS_NUM)
1143 = nvl(l_org_id, FND_API.G_MISS_NUM)
1144 AND orig_sys_line_ref = l_orig_sys_line_ref
1145 AND nvl( orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
1146 = nvl(l_orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
1147 AND nvl( request_id, FND_API.G_MISS_NUM)
1148 = nvl(l_request_id, FND_API.G_MISS_NUM)
1149 FOR UPDATE NOWAIT
1150 ORDER BY orig_sys_credit_ref
1151 ;
1152
1153
1154
1155
1156 /* -----------------------------------------------------------
1157 Line Reservations cursor
1158 -----------------------------------------------------------
1159 */
1160 CURSOR l_reservation_cursor IS
1161 SELECT orig_sys_reservation_ref
1162 , revision
1163 , lot_number_id
1164 , lot_number
1165 , subinventory_id
1166 , subinventory_code
1167 , locator_id
1168 , quantity
1169 , attribute_category
1170 , attribute1
1171 , attribute2
1172 , attribute3
1173 , attribute4
1174 , attribute5
1175 , attribute6
1176 , attribute7
1177 , attribute8
1178 , attribute9
1179 , attribute10
1180 , attribute11
1181 , attribute12
1182 , attribute13
1183 , attribute14
1184 , attribute15
1185 , nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
1186 FROM oe_reservtns_iface_all
1187 WHERE order_source_id = l_order_source_id
1188 AND orig_sys_document_ref = l_orig_sys_document_ref
1189 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
1190 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
1191 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
1192 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
1193 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
1194 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
1195 AND nvl(org_id, FND_API.G_MISS_NUM)
1196 = nvl(l_org_id, FND_API.G_MISS_NUM)
1197 AND orig_sys_line_ref = l_orig_sys_line_ref
1198 AND nvl( orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
1199 = nvl(l_orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
1200 AND nvl( request_id, FND_API.G_MISS_NUM)
1201 = nvl(l_request_id, FND_API.G_MISS_NUM)
1202 FOR UPDATE NOWAIT
1203 ORDER BY orig_sys_reservation_ref
1204 ;
1205
1206
1207
1208 --
1209 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1210 --
1211 --13906241 start
1212 l_serv_num_cnt NUMBER := 0;
1213 l_servref_lineid NUMBER := 0;
1214 -- 13906241 End
1215 BEGIN
1216
1217 /* -----------------------------------------------------------
1218 Initialize messages
1219 -----------------------------------------------------------
1220 */
1221 IF p_init_msg_list = FND_API.G_TRUE THEN
1222 IF l_debug_level > 0 THEN
1223 oe_debug_pub.add( 'BEFORE INITIALIZING MESSAGES LIST' ) ;
1224 END IF;
1225 OE_MSG_PUB.Initialize;
1226 END IF;
1227
1228 /* -----------------------------------------------------------
1229 Set message context
1230 -----------------------------------------------------------
1231 */
1232 IF l_debug_level > 0 THEN
1233 oe_debug_pub.add( 'BEFORE SETTING MESSAGE CONTEXT' ) ;
1234 END IF;
1235
1236 OE_MSG_PUB.set_msg_context(
1237 p_entity_code => 'HEADER'
1238 ,p_entity_ref => null
1239 ,p_entity_id => null
1240 ,p_header_id => null
1241 ,p_line_id => null
1242 ,p_order_source_id => l_order_source_id
1243 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1244 ,p_change_sequence => l_change_sequence
1245 ,p_orig_sys_document_line_ref => null
1246 ,p_orig_sys_shipment_ref => null
1247 ,p_source_document_type_id => null
1248 ,p_source_document_id => null
1249 ,p_source_document_line_id => null
1250 ,p_attribute_code => null
1251 ,p_constraint_id => null
1252 );
1253
1254 /* -----------------------------------------------------------
1255 Initialization
1256 -----------------------------------------------------------
1257 */
1258 IF l_debug_level > 0 THEN
1259 oe_debug_pub.add( 'BEFORE INITIALIZATION' ) ;
1260 END IF;
1261 begin
1262 select DECODE(p_validate_only,'Y','T','N','F',p_validate_only)
1263 into l_validate_only
1264 from dual;
1265 exception
1266 when others then
1267 l_validate_only := 'T';
1268 end;
1269 l_header_rec := OE_Order_Pub.G_MISS_HEADER_REC;
1270 l_header_rec_old := OE_Order_Pub.G_MISS_HEADER_REC;
1271 l_header_rec_new := OE_Order_Pub.G_MISS_HEADER_REC;
1272 l_header_adj_rec := OE_Order_Pub.G_MISS_HEADER_ADJ_REC;
1273 l_header_adj_tbl := OE_Order_Pub.G_MISS_HEADER_ADJ_TBL;
1274 l_header_adj_tbl_old := OE_Order_Pub.G_MISS_HEADER_ADJ_TBL;
1275 l_header_adj_tbl_new := OE_Order_Pub.G_MISS_HEADER_ADJ_TBL;
1276 l_header_scredit_rec := OE_Order_Pub.G_MISS_HEADER_SCREDIT_REC;
1277
1278 l_header_scredit_tbl := OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL;
1279 l_header_scredit_tbl_old := OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL;
1280 l_header_scredit_tbl_new := OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL;
1281
1282 l_header_val_rec := OE_Order_Pub.G_MISS_HEADER_VAL_REC;
1283 l_header_val_rec_old := OE_Order_Pub.G_MISS_HEADER_VAL_REC;
1284 l_header_val_rec_new := OE_Order_Pub.G_MISS_HEADER_VAL_REC;
1285 l_header_adj_val_rec := OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_REC;
1286 l_header_adj_val_tbl := OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL;
1287 l_header_adj_val_tbl_old := OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL;
1288 l_header_adj_val_tbl_new := OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL;
1289 l_header_scredit_val_rec := OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_REC;
1290 l_header_scredit_val_tbl := OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL;
1291 l_header_scredit_val_tbl_old := OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL;
1292 l_header_scredit_val_tbl_new := OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL;
1293
1294 l_line_rec := OE_Order_Pub.G_MISS_LINE_REC;
1295 l_line_tbl := OE_Order_Pub.G_MISS_LINE_TBL;
1296 l_line_tbl_old := OE_Order_Pub.G_MISS_LINE_TBL;
1297 l_line_tbl_new := OE_Order_Pub.G_MISS_LINE_TBL;
1298 l_line_adj_rec := OE_Order_Pub.G_MISS_LINE_ADJ_REC;
1299 l_line_adj_tbl := OE_Order_Pub.G_MISS_LINE_ADJ_TBL;
1300 l_line_adj_tbl_old := OE_Order_Pub.G_MISS_LINE_ADJ_TBL;
1301 l_line_adj_tbl_new := OE_Order_Pub.G_MISS_LINE_ADJ_TBL;
1302 l_line_scredit_rec := OE_Order_Pub.G_MISS_LINE_SCREDIT_REC;
1303 l_line_scredit_tbl := OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL;
1304 l_line_scredit_tbl_old := OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL;
1305 l_line_scredit_tbl_new := OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL;
1306 l_reservation_rec := OE_Order_Pub.G_MISS_RESERVATION_REC;
1307 l_reservation_tbl := OE_Order_Pub.G_MISS_RESERVATION_TBL;
1308
1309 l_line_val_rec := OE_Order_Pub.G_MISS_LINE_VAL_REC;
1310 l_line_val_tbl := OE_Order_Pub.G_MISS_LINE_VAL_TBL;
1311 l_line_val_tbl_old := OE_Order_Pub.G_MISS_LINE_VAL_TBL;
1312 l_line_val_tbl_new := OE_Order_Pub.G_MISS_LINE_VAL_TBL;
1313 l_line_adj_val_rec := OE_Order_Pub.G_MISS_LINE_ADJ_VAL_REC;
1314 l_line_adj_val_tbl := OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL;
1315 l_line_adj_val_tbl_old := OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL;
1316 l_line_adj_val_tbl_new := OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL;
1317 l_line_scredit_val_rec := OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_REC;
1318 l_line_scredit_val_tbl := OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL;
1319 l_line_scredit_val_tbl_old := OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL;
1320 l_line_scredit_val_tbl_new := OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL;
1321 l_reservation_val_rec := OE_Order_Pub.G_MISS_RESERVATION_VAL_REC;
1322 l_reservation_val_tbl := OE_Order_Pub.G_MISS_RESERVATION_VAL_TBL;
1323
1324 l_lot_serial_rec := OE_Order_Pub.G_MISS_LOT_SERIAL_REC;
1325 l_lot_serial_tbl := OE_Order_Pub.G_MISS_LOT_SERIAL_TBL;
1326 l_lot_serial_tbl_old := OE_Order_Pub.G_MISS_LOT_SERIAL_TBL;
1327 l_lot_serial_tbl_new := OE_Order_Pub.G_MISS_LOT_SERIAL_TBL;
1328
1329 l_action_request_rec := OE_Order_Pub.G_MISS_REQUEST_REC;
1330 l_action_request_tbl := OE_Order_Pub.G_MISS_REQUEST_TBL;
1331 l_action_request_tbl_old := OE_Order_Pub.G_MISS_REQUEST_TBL;
1332 l_action_request_tbl_new := OE_Order_Pub.G_MISS_REQUEST_TBL;
1333
1334 p_return_status := FND_API.G_RET_STS_SUCCESS; -- Success
1335
1336
1337
1338
1339 /* -----------------------------------------------------------
1340 Headers
1341 -----------------------------------------------------------
1342 */
1343 IF l_debug_level > 0 THEN
1344 oe_debug_pub.add( 'BEFORE HEADERS LOOP' ) ;
1345 END IF;
1346
1347 l_header_count := 0;
1348
1349 OPEN l_header_cursor;
1350 --LOOP
1351 BEGIN
1352 FETCH l_header_cursor
1353 INTO l_header_rec.order_source_id
1354 , l_header_rec.orig_sys_document_ref
1355 , l_header_rec.change_sequence
1356 , l_header_rec.change_request_code
1357 , l_header_val_rec.order_source
1358 , l_header_rec.org_id
1359 , l_header_rec.header_id
1360 , l_header_rec.order_number
1361 , l_header_rec.version_number
1362 , l_header_rec.ordered_date
1363 , l_header_rec.order_type_id
1364 , l_header_val_rec.order_type
1365 , l_header_rec.price_list_id
1366 , l_header_val_rec.price_list
1367 , l_header_rec.conversion_rate
1368 , l_header_rec.conversion_rate_date
1369 , l_header_rec.conversion_type_code
1370 , l_header_val_rec.conversion_type
1371 , l_header_rec.transactional_curr_code
1372 , l_header_rec.return_reason_code
1373 , l_header_rec.salesrep_id
1374 , l_header_val_rec.salesrep
1375 , l_header_rec.sales_channel_code
1376 , l_header_val_rec.sales_channel
1377 , l_header_rec.tax_point_code
1378 , l_header_val_rec.tax_point
1379 , l_header_rec.tax_exempt_flag
1380 , l_header_rec.tax_exempt_number
1381 , l_header_rec.tax_exempt_reason_code
1382 , l_header_val_rec.tax_exempt_reason
1383 , l_header_rec.agreement_id
1384 , l_header_val_rec.agreement
1385 , l_header_rec.invoicing_rule_id
1386 , l_header_val_rec.invoicing_rule
1387 , l_header_rec.accounting_rule_id
1388 , l_header_val_rec.accounting_rule
1389 , l_header_rec.payment_term_id
1390 , l_header_val_rec.payment_term
1391 , l_header_rec.demand_class_code
1392 , l_header_rec.shipment_priority_code
1393 , l_header_val_rec.shipment_priority
1394 , l_header_rec.shipping_method_code
1395 , l_header_rec.freight_carrier_code
1396 , l_header_rec.freight_terms_code
1397 , l_header_val_rec.freight_terms
1398 , l_header_rec.fob_point_code
1399 , l_header_val_rec.fob_point
1400 , l_header_rec.partial_shipments_allowed
1401 , l_header_rec.ship_tolerance_above
1402 , l_header_rec.ship_tolerance_below
1403 , l_header_rec.shipping_instructions
1404 , l_header_rec.packing_instructions
1405 , l_header_rec.order_date_type_code
1406 , l_header_rec.earliest_schedule_limit
1407 , l_header_rec.latest_schedule_limit
1408 , l_header_rec.cust_po_number
1409 , l_header_rec.customer_payment_term_id
1410 , l_header_val_rec.customer_payment_term
1411 , l_header_rec.payment_type_code
1412 , l_header_rec.payment_amount
1413 , l_header_rec.check_number
1414 , l_header_rec.credit_card_code
1415 , l_header_rec.credit_card_holder_name
1416 , l_header_rec.credit_card_number
1417 , l_header_rec.credit_card_expiration_date
1418 , l_header_rec.credit_card_approval_code
1419 , l_header_rec.credit_card_approval_date
1420 , l_header_rec.sold_from_org_id
1421 , l_header_val_rec.sold_from_org
1422 , l_header_rec.sold_to_org_id
1423 , l_header_val_rec.sold_to_org
1424 , l_header_val_rec.customer_number
1425 , l_header_rec.ship_from_org_id
1426 , l_header_val_rec.ship_from_org
1427 , l_header_rec.ship_to_org_id
1428 , l_header_val_rec.ship_to_org
1429 , l_header_rec.invoice_to_org_id
1430 , l_header_val_rec.invoice_to_org
1431 , l_header_rec.deliver_to_org_id
1432 , l_header_val_rec.deliver_to_org
1433 , l_header_rec.sold_to_contact_id
1434 , l_header_val_rec.sold_to_contact
1435 , l_header_rec.ship_to_contact_id
1436 , l_header_val_rec.ship_to_contact
1437 , l_header_rec.invoice_to_contact_id
1438 , l_header_val_rec.invoice_to_contact
1439 , l_header_rec.deliver_to_contact_id
1440 , l_header_val_rec.deliver_to_contact
1441 , l_header_val_rec.ship_to_address1
1442 , l_header_val_rec.ship_to_address2
1443 , l_header_val_rec.ship_to_address3
1444 , l_header_val_rec.ship_to_address4
1445 , l_header_val_rec.ship_to_city
1446 , l_header_val_rec.ship_to_state
1447 , l_header_val_rec.ship_to_zip
1448 , l_header_val_rec.ship_to_country
1449 , l_header_val_rec.invoice_to_address1
1450 , l_header_val_rec.invoice_to_address2
1451 , l_header_val_rec.invoice_to_address3
1452 , l_header_val_rec.invoice_to_address4
1453 , l_header_val_rec.invoice_to_city
1454 , l_header_val_rec.invoice_to_state
1455 , l_header_val_rec.invoice_to_zip
1456 , l_header_val_rec.invoice_to_country
1457 , l_header_rec.drop_ship_flag
1458 , l_header_rec.booked_flag
1459 -- , l_header_rec.closed_flag
1460 , l_header_rec.cancelled_flag
1461 , l_header_rec.context
1462 , l_header_rec.attribute1
1463 , l_header_rec.attribute2
1464 , l_header_rec.attribute3
1465 , l_header_rec.attribute4
1466 , l_header_rec.attribute5
1467 , l_header_rec.attribute6
1468 , l_header_rec.attribute7
1469 , l_header_rec.attribute8
1470 , l_header_rec.attribute9
1471 , l_header_rec.attribute10
1472 , l_header_rec.attribute11
1473 , l_header_rec.attribute12
1474 , l_header_rec.attribute13
1475 , l_header_rec.attribute14
1476 , l_header_rec.attribute15
1477 /* Added attribute 16 to 20 for the bug 3471009 */
1478 , l_header_rec.attribute16
1479 , l_header_rec.attribute17
1480 , l_header_rec.attribute18
1481 , l_header_rec.attribute19
1482 , l_header_rec.attribute20
1483 , l_header_rec.tp_context
1484 , l_header_rec.tp_attribute1
1485 , l_header_rec.tp_attribute2
1486 , l_header_rec.tp_attribute3
1487 , l_header_rec.tp_attribute4
1488 , l_header_rec.tp_attribute5
1489 , l_header_rec.tp_attribute6
1490 , l_header_rec.tp_attribute7
1491 , l_header_rec.tp_attribute8
1492 , l_header_rec.tp_attribute9
1493 , l_header_rec.tp_attribute10
1494 , l_header_rec.tp_attribute11
1495 , l_header_rec.tp_attribute12
1496 , l_header_rec.tp_attribute13
1497 , l_header_rec.tp_attribute14
1498 , l_header_rec.tp_attribute15
1499 , l_header_rec.global_attribute_category
1500 , l_header_rec.global_attribute1
1501 , l_header_rec.global_attribute2
1502 , l_header_rec.global_attribute3
1503 , l_header_rec.global_attribute4
1504 , l_header_rec.global_attribute5
1505 , l_header_rec.global_attribute6
1506 , l_header_rec.global_attribute7
1507 , l_header_rec.global_attribute8
1508 , l_header_rec.global_attribute9
1509 , l_header_rec.global_attribute10
1510 , l_header_rec.global_attribute11
1511 , l_header_rec.global_attribute12
1512 , l_header_rec.global_attribute13
1513 , l_header_rec.global_attribute14
1514 , l_header_rec.global_attribute15
1515 , l_header_rec.global_attribute16
1516 , l_header_rec.global_attribute17
1517 , l_header_rec.global_attribute18
1518 , l_header_rec.global_attribute19
1519 , l_header_rec.global_attribute20
1520 , l_header_rec.request_id
1521 , l_header_rec.request_date
1522 , l_header_rec.operation
1523 , l_header_rec.ready_flag
1524 , l_header_rec.status_flag
1525 , l_header_rec.force_apply_flag
1526 , l_header_rec.change_reason
1527 , l_header_rec.change_comments
1528 , l_header_rec.open_flag
1529 , l_header_rec.customer_preference_set_code
1530 , l_header_rec.sold_to_site_use_id
1531 , l_header_val_rec.sold_to_location_address1
1532 , l_header_val_rec.sold_to_location_address2
1533 , l_header_val_rec.sold_to_location_address3
1534 , l_header_val_rec.sold_to_location_address4
1535 , l_header_val_rec.sold_to_location_city
1536 , l_header_val_rec.sold_to_location_postal
1537 , l_header_val_rec.sold_to_location_country
1538 , l_header_val_rec.sold_to_location_state
1539 , l_header_val_rec.sold_to_location_county
1540 , l_header_val_rec.sold_to_location_province
1541 -- start of additional quoting columns
1542 , l_header_rec.transaction_phase_code
1543 , l_header_rec.expiration_date
1544 , l_header_rec.quote_number
1545 , l_header_rec.quote_date
1546 , l_header_rec.sales_document_name
1547 , l_header_rec.user_status_code
1548 -- end of additional quoting columns
1549 -- { Distributer Order related change
1550 , l_header_rec.end_customer_id
1551 , l_header_rec.end_customer_contact_id
1552 , l_header_rec.end_customer_site_use_id
1553 --{added for bug 4240715
1554 ,l_header_val_rec.end_customer_name
1555 , l_header_val_rec.end_customer_site_address1
1556 , l_header_val_rec.end_customer_site_address2
1557 , l_header_val_rec.end_customer_site_address3
1558 , l_header_val_rec.end_customer_site_address4
1559 -- , l_header_val_rec.end_customer_site_location
1560 , l_header_val_rec.end_customer_site_city
1561 , l_header_val_rec.end_customer_site_state
1562 , l_header_val_rec.end_customer_site_postal_code
1563 , l_header_val_rec.end_customer_site_country
1564 , l_header_val_rec.end_customer_contact
1565 , l_header_val_rec.end_customer_number
1566 --bug 4240715}
1567 , l_header_rec.ib_owner
1568 , l_header_rec.ib_current_location
1569 , l_header_rec.ib_installed_at_location
1570 , l_header_val_rec.ib_owner_dsp
1571 , l_header_val_rec.ib_current_location_dsp
1572 , l_header_val_rec.ib_installed_at_location_dsp
1573 -- Distributer Order related change }
1574 ;
1575 --EXIT WHEN l_header_cursor%NOTFOUND;
1576
1577 IF l_debug_level > 0 THEN
1578 oe_debug_pub.add( 'AFTER FETCH HEADER CURSOR' ) ;
1579 END IF;
1580
1581 l_header_count := l_header_count + 1;
1582
1583 l_order_source_id := l_header_rec.order_source_id;
1584 l_orig_sys_document_ref := l_header_rec.orig_sys_document_ref;
1585 l_sold_to_org_id := l_header_rec.sold_to_org_id;
1586 l_sold_to_org := l_header_val_rec.sold_to_org;
1587 l_change_sequence := l_header_rec.change_sequence;
1588
1589 /*
1590 IF l_header_rec.operation = 'INSERT' THEN
1591 l_header_rec.operation := 'CREATE';
1592 END IF;
1593 */
1594
1595
1596 oe_debug_pub.add('Order Source Id: ' || l_order_source_id);
1597 oe_debug_pub.add('Orig Sys Reference: '|| l_orig_sys_document_ref);
1598 oe_debug_pub.add('Sold to Org Id: ' || l_sold_to_org_id);
1599 oe_debug_pub.add('Sold to Org: ' || l_sold_to_org);
1600 oe_debug_pub.add('Change Sequence: ' || l_change_sequence);
1601
1602
1603 --Default unpopulated transaction_phase_code to 'F' for bug 3576009
1604 IF l_header_rec.transaction_phase_code = FND_API.G_MISS_CHAR THEN
1605 l_header_rec.transaction_phase_code := 'F';
1606 END IF;
1607
1608
1609 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' THEN
1610
1611
1612 /***** If l_sold_to_org_id is null attempt to populate it based on
1613 l_sold_to_org.
1614
1615 ******/
1616 if l_sold_to_org_id = FND_API.G_MISS_NUM then
1617 IF l_debug_level > 0 THEN
1618 oe_debug_pub.add( 'header level sold to org id is g_miss_num, so it was not populated' ) ;
1619 END IF;
1620
1621 if l_sold_to_org is not null then
1622
1623 l_header_rec.sold_to_org_id := OE_VALUE_TO_ID.sold_to_org(
1624 p_sold_to_org => l_sold_to_org,
1625 p_customer_number => null);
1626
1627
1628 /* if oe_value_to_id returned g_miss_num, reassign sold_to_org_id back to null */
1629
1630 if l_header_rec.sold_to_org_id = FND_API.G_MISS_NUM then
1631 IF l_debug_level > 0 THEN
1632 oe_debug_pub.add( 'returned value for sold_to_org_id was g_miss_num') ;
1633 END IF;
1634 l_header_rec.sold_to_org_id := null;
1635 end if;
1636
1637 else
1638 IF l_debug_level > 0 THEN
1639 oe_debug_pub.add( 'sold to org is NULL') ;
1640 END IF;
1641 end if;
1642
1643 end if;
1644
1645 END IF; -- code control check
1646
1647 BEGIN
1648 --call value to id
1649 --
1650 OE_CNCL_UTIL.get_header_ids(l_header_rec,l_header_val_rec);
1651 --
1652 IF l_header_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1653 --
1654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1655 --
1656 ELSIF l_header_rec.return_status = FND_API.G_RET_STS_ERROR THEN
1657 --
1658 RAISE FND_API.G_EXC_ERROR;
1659 --
1660 END IF;
1661 --
1662 --
1663 OE_CNCL_Util.Convert_Miss_To_Null(l_header_rec);
1664 --
1665
1666 OE_CNCL_validate_header.attributes(x_return_status =>l_return_status
1667 , p_x_header_rec =>l_header_rec);
1668 --, p_validation_level =>FND_API.G_VALID_LEVEL_NONE
1669 --
1670 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1671 --
1672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1673 --
1674 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1675 --
1676 RAISE FND_API.G_EXC_ERROR;
1677 --
1678 END IF;
1679 --
1680 --
1681 OE_CNCL_validate_header.entity(x_return_status =>l_return_status
1682 , p_header_rec =>l_header_rec);
1683 --
1684 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1685 --
1686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1687 --
1688 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1689 --
1690 RAISE FND_API.G_EXC_ERROR;
1691 --
1692 END IF;
1693 --
1694 EXCEPTION
1695 --
1696 WHEN FND_API.G_EXC_ERROR THEN
1697 --
1698 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1699 OE_MSG_PUB.reset_msg_context('HEADER');
1700 --
1701 RAISE;
1702 --
1703 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1704 --
1705 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1706 OE_MSG_PUB.reset_msg_context('HEADER');
1707 --
1708 RAISE;
1709 --
1710 WHEN OTHERS THEN
1711 --
1712 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1713 --
1714 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Header');
1715 --
1716 END IF;
1717 --
1718 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1719 OE_MSG_PUB.reset_msg_context('HEADER');
1720 --
1721 RAISE;
1722 --
1723 END;
1724
1725
1726 /* -----------------------------------------------------------
1727 Header Discounts/Price adjustments
1728 -----------------------------------------------------------
1729 */
1730 IF l_debug_level > 0 THEN
1731 oe_debug_pub.add( 'BEFORE HEADER ADJUSTMENTS LOOP' ) ;
1732 END IF;
1733
1734 l_header_adj_count := 0;
1735
1736 OPEN l_header_adj_cursor;
1737 LOOP
1738 FETCH l_header_adj_cursor
1739 INTO l_header_adj_rec.orig_sys_discount_ref
1740 , l_header_adj_rec.change_request_code
1741 , l_header_adj_rec.list_header_id -- changed from discount_id
1742 , l_header_adj_rec.list_line_id -- changed from discount_line_id
1743 , l_header_adj_val_rec.discount
1744 , l_header_adj_rec.percent
1745 , l_header_adj_rec.automatic_flag
1746 , l_header_adj_rec.applied_flag
1747 , l_header_adj_rec.operand
1748 , l_header_adj_rec.arithmetic_operator
1749 , l_header_adj_rec.context
1750 , l_header_adj_rec.attribute1
1751 , l_header_adj_rec.attribute2
1752 , l_header_adj_rec.attribute3
1753 , l_header_adj_rec.attribute4
1754 , l_header_adj_rec.attribute5
1755 , l_header_adj_rec.attribute6
1756 , l_header_adj_rec.attribute7
1757 , l_header_adj_rec.attribute8
1758 , l_header_adj_rec.attribute9
1759 , l_header_adj_rec.attribute10
1760 , l_header_adj_rec.attribute11
1761 , l_header_adj_rec.attribute12
1762 , l_header_adj_rec.attribute13
1763 , l_header_adj_rec.attribute14
1764 , l_header_adj_rec.attribute15
1765 , l_header_adj_rec.request_id
1766 , l_header_adj_rec.operation
1767 , l_header_adj_rec.status_flag
1768 -- Price Adjustment related changes bug# 1220921 (Start)
1769 , l_header_adj_rec.AC_CONTEXT
1770 , l_header_adj_rec.AC_ATTRIBUTE1
1771 , l_header_adj_rec.AC_ATTRIBUTE2
1772 , l_header_adj_rec.AC_ATTRIBUTE3
1773 , l_header_adj_rec.AC_ATTRIBUTE4
1774 , l_header_adj_rec.AC_ATTRIBUTE5
1775 , l_header_adj_rec.AC_ATTRIBUTE6
1776 , l_header_adj_rec.AC_ATTRIBUTE7
1777 , l_header_adj_rec.AC_ATTRIBUTE8
1778 , l_header_adj_rec.AC_ATTRIBUTE9
1779 , l_header_adj_rec.AC_ATTRIBUTE10
1780 , l_header_adj_rec.AC_ATTRIBUTE11
1781 , l_header_adj_rec.AC_ATTRIBUTE12
1782 , l_header_adj_rec.AC_ATTRIBUTE13
1783 , l_header_adj_rec.AC_ATTRIBUTE14
1784 , l_header_adj_rec.AC_ATTRIBUTE15
1785 , l_header_adj_val_rec.LIST_NAME
1786 , l_header_adj_rec.LIST_LINE_TYPE_CODE
1787 , l_header_adj_rec.LIST_LINE_NO
1788 , l_header_adj_val_rec.VERSION_NO
1789 , l_header_adj_rec.INVOICED_FLAG
1790 , l_header_adj_rec.ESTIMATED_FLAG
1791 , l_header_adj_rec.INC_IN_SALES_PERFORMANCE
1792 , l_header_adj_rec.CHARGE_TYPE_CODE
1793 , l_header_adj_rec.CHARGE_SUBTYPE_CODE
1794 , l_header_adj_rec.CREDIT_OR_CHARGE_FLAG
1795 , l_header_adj_rec.INCLUDE_ON_RETURNS_FLAG
1796 , l_header_adj_rec.COST_ID
1797 , l_header_adj_rec.TAX_CODE
1798 , l_header_adj_rec.PARENT_ADJUSTMENT_ID
1799 , l_header_adj_rec.MODIFIER_MECHANISM_TYPE_CODE
1800 , l_header_adj_rec.MODIFIED_FROM
1801 , l_header_adj_rec.MODIFIED_TO
1802 , l_header_adj_rec.UPDATED_FLAG
1803 , l_header_adj_rec.UPDATE_ALLOWED
1804 , l_header_adj_rec.CHANGE_REASON_CODE
1805 , l_header_adj_rec.CHANGE_REASON_TEXT
1806 , l_header_adj_rec.PRICING_PHASE_ID
1807 , l_header_adj_rec.ADJUSTED_AMOUNT
1808 -- Price Adjustment related changes bug# 1220921 (End)
1809 ;
1810 EXIT WHEN l_header_adj_cursor%NOTFOUND;
1811
1812 /*
1813 IF l_header_adj_rec.operation = 'INSERT' THEN
1814 l_header_adj_rec.operation := 'CREATE';
1815 END IF;
1816 */
1817
1818 l_header_adj_count := l_header_adj_count + 1;
1819 l_header_adj_tbl (l_header_adj_count) := l_header_adj_rec;
1820 l_header_adj_val_tbl (l_header_adj_count) := l_header_adj_val_rec;
1821
1822 IF l_debug_level > 0 THEN
1823 oe_debug_pub.add( 'HEADER ADJ REF ( '||L_HEADER_ADJ_COUNT||' ) : '|| L_HEADER_ADJ_TBL ( L_HEADER_ADJ_COUNT ) .ORIG_SYS_DISCOUNT_REF ) ;
1824 END IF;
1825
1826
1827 BEGIN
1828 --
1829 OE_CNCL_UTIL.get_header_adj_ids(l_header_adj_rec,l_header_adj_val_rec);
1830 --
1831 IF l_header_adj_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1832 --
1833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1834 --
1835 ELSIF l_header_adj_rec.return_status = FND_API.G_RET_STS_ERROR THEN
1836 --
1837 RAISE FND_API.G_EXC_ERROR;
1838 --
1839 END IF;
1840 --
1841 --
1842 OE_CNCL_Util.Convert_Miss_To_Null(l_header_adj_rec);
1843 --
1844
1845 OE_CNCL_Validate_Header_Adj.Attributes( x_return_status =>l_return_status
1846 , p_Header_Adj_rec =>l_header_adj_rec);
1847 --
1848 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1849 --
1850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1851 --
1852 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1853 --
1854 RAISE FND_API.G_EXC_ERROR;
1855 --
1856 END IF;
1857 --
1858 OE_CNCL_Validate_Header_Adj.Entity( x_return_status =>l_return_status
1859 , p_Header_Adj_rec =>l_header_adj_rec);
1860 --
1861 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1862 --
1863 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1864 --
1865 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1866 --
1867 RAISE FND_API.G_EXC_ERROR;
1868 --
1869 END IF;
1870 --
1871
1872 EXCEPTION
1873 --
1874 WHEN FND_API.G_EXC_ERROR THEN
1875 --
1876 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1877 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
1878 --
1879 RAISE;
1880 --
1881 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1882 --
1883 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1884 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
1885 --
1886 RAISE;
1887 --
1888 WHEN OTHERS THEN
1889 --
1890 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1891 --
1892 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Header_Adjs');
1893 --
1894 END IF;
1895 --
1896 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1897 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
1898 --
1899 RAISE;
1900 --
1901 END;
1902
1903 END LOOP;
1904 CLOSE l_header_adj_cursor;
1905
1906
1907
1908
1909
1910 /* -----------------------------------------------------------
1911 Header Sales Credits
1912 -----------------------------------------------------------
1913 */
1914 IF l_debug_level > 0 THEN
1915 oe_debug_pub.add( 'BEFORE HEADER SALES CREDITS LOOP' ) ;
1916 END IF;
1917
1918 l_header_scredit_count := 0;
1919
1920 OPEN l_header_scredit_cursor;
1921 LOOP
1922 FETCH l_header_scredit_cursor
1923 INTO l_header_scredit_rec.orig_sys_credit_ref
1924 , l_header_scredit_rec.change_request_code
1925 , l_header_scredit_rec.salesrep_id
1926 , l_header_scredit_val_rec.salesrep
1927 , l_header_scredit_rec.sales_credit_type_id
1928 , l_header_scredit_val_rec.sales_credit_type
1929 , l_header_scredit_rec.percent
1930 , l_header_scredit_rec.context
1931 , l_header_scredit_rec.attribute1
1932 , l_header_scredit_rec.attribute2
1933 , l_header_scredit_rec.attribute3
1934 , l_header_scredit_rec.attribute4
1935 , l_header_scredit_rec.attribute5
1936 , l_header_scredit_rec.attribute6
1937 , l_header_scredit_rec.attribute7
1938 , l_header_scredit_rec.attribute8
1939 , l_header_scredit_rec.attribute9
1940 , l_header_scredit_rec.attribute10
1941 , l_header_scredit_rec.attribute11
1942 , l_header_scredit_rec.attribute12
1943 , l_header_scredit_rec.attribute13
1944 , l_header_scredit_rec.attribute14
1945 , l_header_scredit_rec.attribute15
1946 , l_header_scredit_rec.operation
1947 , l_header_scredit_rec.status_flag
1948 ;
1949 EXIT WHEN l_header_scredit_cursor%NOTFOUND;
1950
1951 /*
1952 IF l_header_scredit_rec.operation = 'INSERT' THEN
1953 l_header_scredit_rec.operation := 'CREATE';
1954 END IF;
1955 */
1956
1957 l_header_scredit_count := l_header_scredit_count + 1;
1958 l_header_scredit_tbl (l_header_scredit_count) := l_header_scredit_rec;
1959 l_header_scredit_val_tbl (l_header_scredit_count) := l_header_scredit_val_rec;
1960
1961 IF l_debug_level > 0 THEN
1962 oe_debug_pub.add( 'HEADER SALESCREDIT ( '|| L_HEADER_SCREDIT_COUNT||' ) : '|| L_HEADER_SCREDIT_TBL ( L_HEADER_SCREDIT_COUNT ) .ORIG_SYS_CREDIT_REF ) ;
1963 END IF;
1964
1965 BEGIN
1966 --
1967 OE_CNCL_UTIL.get_header_scredit_ids(l_header_scredit_rec,l_header_scredit_val_rec);
1968 --
1969 IF l_header_scredit_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1970 --
1971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1972 --
1973 ELSIF l_header_scredit_rec.return_status = FND_API.G_RET_STS_ERROR THEN
1974 --
1975 RAISE FND_API.G_EXC_ERROR;
1976 --
1977 END IF;
1978 --
1979
1980 --
1981 OE_CNCL_Util.Convert_Miss_To_Null(l_header_scredit_rec);
1982 --
1983
1984 OE_CNCL_Val_Header_Scredit.Attributes (x_return_status => l_return_status
1985 , p_Header_Scredit_rec => l_header_scredit_rec);
1986 --
1987 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1988 --
1989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1990 --
1991 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1992 --
1993 RAISE FND_API.G_EXC_ERROR;
1994 --
1995 END IF;
1996 --
1997
1998 OE_CNCL_Val_Header_Scredit.Entity (x_return_status => l_return_status
1999 , p_Header_Scredit_rec => l_header_scredit_rec);
2000 --
2001 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2002 --
2003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2004 --
2005 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2006 --
2007 RAISE FND_API.G_EXC_ERROR;
2008 --
2009 END IF;
2010 --
2011 EXCEPTION
2012 --
2013 WHEN FND_API.G_EXC_ERROR THEN
2014 --
2015 OE_Header_Security.g_check_all_cols_constraint := 'Y';
2016 OE_MSG_PUB.reset_msg_context('HEADER_SCREDIT');
2017 --
2018 RAISE;
2019 --
2020 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2021 --
2022 OE_Header_Security.g_check_all_cols_constraint := 'Y';
2023 OE_MSG_PUB.reset_msg_context('HEADER_SCREDIT');
2024 --
2025 RAISE;
2026 --
2027 WHEN OTHERS THEN
2028 --
2029 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2030 --
2031 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Header_Scredits');
2032 --
2033 END IF;
2034 --
2035 OE_Header_Security.g_check_all_cols_constraint := 'Y';
2036 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
2037 --
2038 RAISE;
2039 --
2040 END;
2041 --
2042 END LOOP;
2043 CLOSE l_header_scredit_cursor;
2044
2045
2046 /* -----------------------------------------------------------
2047 Lines
2048 -----------------------------------------------------------
2049 */
2050 IF l_debug_level > 0 THEN
2051 oe_debug_pub.add( 'BEFORE LINES LOOP' ) ;
2052 END IF;
2053
2054 l_line_count := 0;
2055
2056 OPEN l_line_cursor;
2057 LOOP
2058 FETCH l_line_cursor
2059 INTO l_line_rec.order_source_id
2060 , l_line_rec.orig_sys_document_ref
2061 , l_line_rec.orig_sys_line_ref
2062 , l_line_rec.orig_sys_shipment_ref
2063 , l_line_rec.change_request_code
2064 , l_line_rec.org_id
2065 , l_line_rec.line_number
2066 , l_line_rec.shipment_number
2067 , l_line_rec.line_id
2068 , l_line_rec.line_type_id
2069 , l_line_val_rec.line_type
2070 , l_line_rec.item_type_code
2071 , l_line_rec.inventory_item_id
2072 , l_line_val_rec.inventory_item
2073 , l_line_rec.top_model_line_ref
2074 , l_line_rec.link_to_line_ref
2075 , l_line_rec.explosion_date
2076 , l_line_rec.ato_line_id
2077 , l_line_rec.component_sequence_id
2078 , l_line_rec.component_code
2079 , l_line_rec.sort_order
2080 , l_line_rec.model_group_number
2081 , l_line_rec.option_number
2082 , l_line_rec.option_flag
2083 , l_line_rec.ship_model_complete_flag
2084 , l_line_rec.source_type_code
2085 , l_line_rec.schedule_status_code
2086 , l_line_rec.schedule_ship_date
2087 , l_line_rec.schedule_arrival_date
2088 , l_line_rec.actual_arrival_date
2089 -- bug 3220711 - start
2090 , l_line_rec.actual_shipment_date
2091 -- bug 3220711 - end
2092 , l_line_rec.request_date
2093 , l_line_rec.promise_date
2094 , l_line_rec.delivery_lead_time
2095 , l_line_rec.ordered_quantity
2096 , l_line_rec.order_quantity_uom
2097 , l_line_rec.shipping_quantity
2098 , l_line_rec.shipping_quantity_uom
2099 , l_line_rec.shipped_quantity
2100 , l_line_rec.cancelled_quantity
2101 , l_line_rec.fulfilled_quantity
2102 /* OPM variables */ -- INVCONV
2103 , l_line_rec.ordered_quantity2
2104 , l_line_rec.ordered_quantity_uom2
2105 , l_line_rec.shipping_quantity2
2106 , l_line_rec.shipping_quantity_uom2
2107 , l_line_rec.shipped_quantity2
2108 , l_line_rec.cancelled_quantity2
2109 , l_line_rec.fulfilled_quantity2
2110 , l_line_rec.preferred_grade
2111 , l_line_rec.pricing_quantity
2112 , l_line_rec.pricing_quantity_uom
2113 , l_line_rec.sold_from_org_id
2114 , l_line_val_rec.sold_from_org
2115 , l_line_rec.sold_to_org_id
2116 , l_line_val_rec.sold_to_org
2117 , l_line_rec.ship_from_org_id
2118 , l_line_val_rec.ship_from_org
2119 , l_line_rec.ship_to_org_id
2120 , l_line_val_rec.ship_to_org
2121 , l_line_rec.deliver_to_org_id
2122 , l_line_val_rec.deliver_to_org
2123 , l_line_rec.invoice_to_org_id
2124 , l_line_val_rec.invoice_to_org
2125 , l_line_val_rec.ship_to_address1
2126 , l_line_val_rec.ship_to_address2
2127 , l_line_val_rec.ship_to_address3
2128 , l_line_val_rec.ship_to_address4
2129 , l_line_val_rec.ship_to_city
2130 , l_line_val_rec.ship_to_state
2131 , l_line_val_rec.ship_to_zip
2132 , l_line_val_rec.ship_to_country
2133 , l_line_rec.ship_to_contact_id
2134 , l_line_val_rec.ship_to_contact
2135 , l_line_rec.deliver_to_contact_id
2136 , l_line_val_rec.deliver_to_contact
2137 , l_line_rec.invoice_to_contact_id
2138 , l_line_val_rec.invoice_to_contact
2139 , l_line_rec.drop_ship_flag
2140 , l_line_rec.ship_tolerance_above
2141 , l_line_rec.ship_tolerance_below
2142 , l_line_rec.price_list_id
2143 , l_line_val_rec.price_list
2144 , l_line_rec.pricing_date
2145 , l_line_rec.unit_list_price
2146 , l_line_rec.unit_selling_price
2147 , l_line_rec.calculate_price_flag
2148 , l_line_rec.ship_set_id
2149 , l_line_rec.ship_set
2150 , l_line_rec.arrival_set_id
2151 , l_line_rec.arrival_set
2152 , l_line_rec.fulfillment_set_id
2153 , l_line_rec.fulfillment_set
2154 , l_line_rec.tax_code
2155 , l_line_rec.tax_value
2156 , l_line_rec.tax_date
2157 , l_line_rec.tax_point_code
2158 , l_line_val_rec.tax_point
2159 , l_line_rec.tax_exempt_flag
2160 , l_line_rec.tax_exempt_number
2161 , l_line_rec.tax_exempt_reason_code
2162 , l_line_val_rec.tax_exempt_reason
2163 , l_line_rec.agreement_id
2164 , l_line_val_rec.agreement
2165 , l_line_rec.invoicing_rule_id
2166 , l_line_val_rec.invoicing_rule
2167 , l_line_rec.accounting_rule_id
2168 , l_line_val_rec.accounting_rule
2169 , l_line_rec.payment_term_id
2170 , l_line_val_rec.payment_term
2171 , l_line_rec.demand_class_code
2172 , l_line_rec.shipment_priority_code
2173 , l_line_val_rec.shipment_priority
2174 , l_line_rec.shipping_method_code
2175 , l_line_rec.shipping_instructions
2176 , l_line_rec.packing_instructions
2177 , l_line_rec.freight_carrier_code
2178 , l_line_rec.freight_terms_code
2179 , l_line_val_rec.freight_terms
2180 , l_line_rec.fob_point_code
2181 , l_line_val_rec.fob_point
2182 , l_line_rec.return_reason_code
2183 , l_line_rec.reference_type
2184 , l_line_rec.reference_header_id
2185 , l_line_rec.reference_line_id
2186 , l_line_rec.credit_invoice_line_id
2187 , l_line_rec.cust_po_number
2188 , l_line_rec.customer_line_number
2189 , l_line_rec.customer_shipment_number
2190 , l_line_rec.ordered_item_id
2191 , l_line_rec.item_identifier_type
2192 , l_line_rec.ordered_item
2193 -- , l_line_rec.customer_item_revision
2194 , l_line_rec.customer_item_net_price
2195 , l_line_rec.customer_payment_term_id
2196 , l_line_val_rec.customer_payment_term
2197 , l_line_rec.demand_bucket_type_code
2198 , l_line_val_rec.demand_bucket_type
2199 , l_line_rec.customer_dock_code
2200 , l_line_rec.customer_job
2201 , l_line_rec.customer_production_line
2202 , l_line_rec.cust_model_serial_number
2203 , l_line_rec.project_id
2204 , l_line_val_rec.project
2205 , l_line_rec.task_id
2206 , l_line_val_rec.task
2207 , l_line_rec.end_item_unit_number
2208 , l_line_rec.item_revision
2209 , l_line_rec.service_duration
2210 , l_line_rec.service_period
2211 , l_line_rec.service_start_date
2212 , l_line_rec.service_end_date
2213 , l_line_rec.service_coterminate_flag
2214 , l_line_rec.unit_selling_percent
2215 , l_line_rec.unit_list_percent
2216 , l_line_rec.unit_percent_base_price
2217 , l_line_rec.service_number
2218 -- , l_line_rec.fulfilled_flag -- To be added in Process Order
2219 -- , l_line_rec.closed_flag
2220 , l_line_rec.cancelled_flag
2221 , l_line_rec.context
2222 , l_line_rec.attribute1
2223 , l_line_rec.attribute2
2224 , l_line_rec.attribute3
2225 , l_line_rec.attribute4
2226 , l_line_rec.attribute5
2227 , l_line_rec.attribute6
2228 , l_line_rec.attribute7
2229 , l_line_rec.attribute8
2230 , l_line_rec.attribute9
2231 , l_line_rec.attribute10
2232 , l_line_rec.attribute11
2233 , l_line_rec.attribute12
2234 , l_line_rec.attribute13
2235 , l_line_rec.attribute14
2236 , l_line_rec.attribute15
2237 /* Added attribute 16 to 20 for bug 3513248 */
2238 , l_line_rec.attribute16
2239 , l_line_rec.attribute17
2240 , l_line_rec.attribute18
2241 , l_line_rec.attribute19
2242 , l_line_rec.attribute20
2243 , l_line_rec.tp_context
2244 , l_line_rec.tp_attribute1
2245 , l_line_rec.tp_attribute2
2246 , l_line_rec.tp_attribute3
2247 , l_line_rec.tp_attribute4
2248 , l_line_rec.tp_attribute5
2249 , l_line_rec.tp_attribute6
2250 , l_line_rec.tp_attribute7
2251 , l_line_rec.tp_attribute8
2252 , l_line_rec.tp_attribute9
2253 , l_line_rec.tp_attribute10
2254 , l_line_rec.tp_attribute11
2255 , l_line_rec.tp_attribute12
2256 , l_line_rec.tp_attribute13
2257 , l_line_rec.tp_attribute14
2258 , l_line_rec.tp_attribute15
2259 , l_line_rec.industry_context
2260 , l_line_rec.industry_attribute1
2261 , l_line_rec.industry_attribute2
2262 , l_line_rec.industry_attribute3
2263 , l_line_rec.industry_attribute4
2264 , l_line_rec.industry_attribute5
2265 , l_line_rec.industry_attribute6
2266 , l_line_rec.industry_attribute7
2267 , l_line_rec.industry_attribute8
2268 , l_line_rec.industry_attribute9
2269 , l_line_rec.industry_attribute10
2270 , l_line_rec.industry_attribute11
2271 , l_line_rec.industry_attribute12
2272 , l_line_rec.industry_attribute13
2273 , l_line_rec.industry_attribute14
2274 , l_line_rec.industry_attribute15
2275 , l_line_rec.industry_attribute16
2276 , l_line_rec.industry_attribute17
2277 , l_line_rec.industry_attribute18
2278 , l_line_rec.industry_attribute19
2279 , l_line_rec.industry_attribute20
2280 , l_line_rec.industry_attribute21
2281 , l_line_rec.industry_attribute22
2282 , l_line_rec.industry_attribute23
2283 , l_line_rec.industry_attribute24
2284 , l_line_rec.industry_attribute25
2285 , l_line_rec.industry_attribute26
2286 , l_line_rec.industry_attribute27
2287 , l_line_rec.industry_attribute28
2288 , l_line_rec.industry_attribute29
2289 , l_line_rec.industry_attribute30
2290 , l_line_rec.pricing_context
2291 , l_line_rec.pricing_attribute1
2292 , l_line_rec.pricing_attribute2
2293 , l_line_rec.pricing_attribute3
2294 , l_line_rec.pricing_attribute4
2295 , l_line_rec.pricing_attribute5
2296 , l_line_rec.pricing_attribute6
2297 , l_line_rec.pricing_attribute7
2298 , l_line_rec.pricing_attribute8
2299 , l_line_rec.pricing_attribute9
2300 , l_line_rec.pricing_attribute10
2301 , l_line_rec.global_attribute_category
2302 , l_line_rec.global_attribute1
2303 , l_line_rec.global_attribute2
2304 , l_line_rec.global_attribute3
2305 , l_line_rec.global_attribute4
2306 , l_line_rec.global_attribute5
2307 , l_line_rec.global_attribute6
2308 , l_line_rec.global_attribute7
2309 , l_line_rec.global_attribute8
2310 , l_line_rec.global_attribute9
2311 , l_line_rec.global_attribute10
2312 , l_line_rec.global_attribute11
2313 , l_line_rec.global_attribute12
2314 , l_line_rec.global_attribute13
2315 , l_line_rec.global_attribute14
2316 , l_line_rec.global_attribute15
2317 , l_line_rec.global_attribute16
2318 , l_line_rec.global_attribute17
2319 , l_line_rec.global_attribute18
2320 , l_line_rec.global_attribute19
2321 , l_line_rec.global_attribute20
2322 , l_line_rec.return_context
2323 , l_line_rec.return_attribute1
2324 , l_line_rec.return_attribute2
2325 , l_line_rec.return_attribute3
2326 , l_line_rec.return_attribute4
2327 , l_line_rec.return_attribute5
2328 , l_line_rec.return_attribute6
2329 , l_line_rec.return_attribute7
2330 , l_line_rec.return_attribute8
2331 , l_line_rec.return_attribute9
2332 , l_line_rec.return_attribute10
2333 , l_line_rec.return_attribute11
2334 , l_line_rec.return_attribute12
2335 , l_line_rec.return_attribute13
2336 , l_line_rec.return_attribute14
2337 , l_line_rec.return_attribute15
2338 , l_line_rec.request_id
2339 , l_line_rec.operation
2340 , l_line_rec.status_flag
2341 , l_line_rec.change_reason
2342 , l_line_rec.change_comments
2343 , l_line_rec.service_txn_reason_code
2344 , l_line_rec.service_txn_comments
2345 , l_line_rec.service_reference_type_code
2346 , l_line_rec.service_reference_order
2347 , l_line_rec.service_reference_line
2348 , l_line_rec.service_reference_system
2349 , l_segment_array(1)
2350 , l_segment_array(2)
2351 , l_segment_array(3)
2352 , l_segment_array(4)
2353 , l_segment_array(5)
2354 , l_segment_array(6)
2355 , l_segment_array(7)
2356 , l_segment_array(8)
2357 , l_segment_array(9)
2358 , l_segment_array(10)
2359 , l_segment_array(11)
2360 , l_segment_array(12)
2361 , l_segment_array(13)
2362 , l_segment_array(14)
2363 , l_segment_array(15)
2364 , l_segment_array(16)
2365 , l_segment_array(17)
2366 , l_segment_array(18)
2367 , l_segment_array(19)
2368 , l_segment_array(20)
2369 , l_line_val_rec.commitment
2370 , l_line_rec.commitment_id
2371 -- aksingh subinventory
2372 , l_line_rec.subinventory
2373 ,l_line_val_rec.salesrep
2374 ,l_line_rec.salesrep_id
2375 ,l_line_rec.earliest_acceptable_date
2376 ,l_line_rec.latest_acceptable_date
2377 ,l_line_val_rec.invoice_to_address1
2378 ,l_line_val_rec.invoice_to_address2
2379 ,l_line_val_rec.invoice_to_address3
2380 ,l_line_val_rec.invoice_to_address4
2381 ,l_line_val_rec.invoice_to_city
2382 ,l_line_val_rec.invoice_to_state
2383 ,l_line_val_rec.invoice_to_zip
2384 ,l_line_val_rec.invoice_to_country
2385 ,l_line_rec.user_item_description
2386 ,l_line_rec.change_sequence
2387 -- { Distributer Order related change
2388 ,l_line_rec.end_customer_id
2389 ,l_line_rec.end_customer_contact_id
2390 ,l_line_rec.end_customer_site_use_id
2391 --{added for bug 4240715
2392 ,l_line_val_rec.end_customer_name
2393 , l_line_val_rec.end_customer_site_address1
2394 , l_line_val_rec.end_customer_site_address2
2395 , l_line_val_rec.end_customer_site_address3
2396 , l_line_val_rec.end_customer_site_address4
2397 -- , l_line_val_rec.end_customer_site_location
2398 , l_line_val_rec.end_customer_site_city
2399 , l_line_val_rec.end_customer_site_state
2400 , l_line_val_rec.end_customer_site_postal_code
2401 , l_line_val_rec.end_customer_site_country
2402 , l_line_val_rec.end_customer_contact
2403 , l_line_val_rec.end_customer_number
2404 -- bug 4240715}
2405 ,l_line_rec.ib_owner
2406 ,l_line_rec.ib_current_location
2407 ,l_line_rec.ib_installed_at_location
2408 ,l_line_val_rec.ib_owner_dsp
2409 ,l_line_val_rec.ib_current_location_dsp
2410 ,l_line_val_rec.ib_installed_at_location_dsp
2411 -- Distributer Order related change }
2412 ;
2413 IF l_debug_level > 0 THEN
2414 oe_debug_pub.add('value inserted'||l_line_rec.end_customer_id); -- added for bug 4240715
2415 END IF;
2416 EXIT WHEN l_line_cursor%NOTFOUND;
2417
2418 IF l_debug_level > 0 THEN
2419 oe_debug_pub.add( 'AFTER LINE FETCH ' ) ;
2420 END IF;
2421 /*
2422 IF l_line_rec.operation = 'INSERT' THEN
2423 l_line_rec.operation := 'CREATE';
2424 END IF;
2425 */
2426
2427 --Assigning line level transaction phase value to header value for bug 3576009
2428 l_line_rec.transaction_phase_code := l_header_rec.transaction_phase_code;
2429
2430
2431 /* if missing, get line level sold to org id from header
2432 this is necessary for the case where the sold_to_org
2433 was populated and the header-level sold_to_org_id was derived
2434 */
2435
2436
2437 if l_line_rec.sold_to_org_id = FND_API.G_MISS_NUM then
2438 IF l_debug_level > 0 THEN
2439 oe_debug_pub.add( 'line level sold to org id is g_miss_num, so it was not populated. defaulting to header level sold to org id' ) ;
2440 END IF;
2441 l_line_rec.sold_to_org_id := l_header_rec.sold_to_org_id;
2442 end if;
2443
2444
2445
2446 l_line_count := l_line_count + 1;
2447 if l_line_rec.service_reference_order = FND_API.G_MISS_CHAR then
2448 IF l_debug_level > 0 THEN
2449 oe_debug_pub.add( 'L_LINE_REC.SERVICE_REFERENCE_ORDER ' || ASCII ( L_LINE_REC.SERVICE_REFERENCE_ORDER ) ) ;
2450 END IF;
2451 end if;
2452 if l_line_val_rec.sold_to_org = FND_API.G_MISS_CHAR then
2453 IF l_debug_level > 0 THEN
2454 oe_debug_pub.add( 'L_LINE_VAL_REC.SOLD_TO_ORG ' || ASCII ( L_LINE_VAL_REC.SOLD_TO_ORG ) ) ;
2455 END IF;
2456 end if;
2457
2458 --populate l_line_rec.inventory_item_id with ccid if any of the
2459 --segments are passed instead of inventory_item_id
2460 IF l_line_rec.inventory_item_id = FND_API.G_MISS_NUM AND
2461 ((l_segment_array(1) IS NOT NULL) OR
2462 (l_segment_array(2) IS NOT NULL) OR
2463 (l_segment_array(3) IS NOT NULL) OR
2464 (l_segment_array(4) IS NOT NULL) OR
2465 (l_segment_array(5) IS NOT NULL) OR
2466 (l_segment_array(6) IS NOT NULL) OR
2467 (l_segment_array(7) IS NOT NULL) OR
2468 (l_segment_array(8) IS NOT NULL) OR
2469 (l_segment_array(9) IS NOT NULL) OR
2470 (l_segment_array(10) IS NOT NULL) OR
2471 (l_segment_array(11) IS NOT NULL) OR
2472 (l_segment_array(12) IS NOT NULL) OR
2473 (l_segment_array(13) IS NOT NULL) OR
2474 (l_segment_array(14) IS NOT NULL) OR
2475 (l_segment_array(15) IS NOT NULL) OR
2476 (l_segment_array(16) IS NOT NULL) OR
2477 (l_segment_array(17) IS NOT NULL) OR
2478 (l_segment_array(18) IS NOT NULL) OR
2479 (l_segment_array(19) IS NOT NULL) OR
2480 (l_segment_array(20) IS NOT NULL)) THEN
2481 IF l_debug_level > 0 THEN
2482 oe_debug_pub.add( 'INSIDE GET CCID ROUTINE' ) ;
2483 END IF;
2484 FND_FLEX_KEY_API.SET_SESSION_MODE('customer_data');
2485 IF l_debug_level > 0 THEN
2486 oe_debug_pub.add( 'AFTER CALL TO SET SESSION' ) ;
2487 END IF;
2488 l_flexfield := FND_FLEX_KEY_API.FIND_FLEXFIELD('INV', 'MSTK');
2489 IF l_debug_level > 0 THEN
2490 oe_debug_pub.add( 'AFTER FIND FLEXFIELD' ) ;
2491 END IF;
2492 l_structure.structure_number := 101;
2493 FND_FLEX_KEY_API.GET_SEGMENTS(l_flexfield, l_structure, TRUE, l_n_segments, l_segments);
2494 IF l_debug_level > 0 THEN
2495 oe_debug_pub.add( 'SEGMENTS ENABLED = '||L_N_SEGMENTS ) ;
2496 END IF;
2497 IF l_debug_level > 0 THEN
2498 oe_debug_pub.add( 'ORG_ID = '||L_LINE_REC.ORG_ID ) ;
2499 END IF;
2500 IF l_debug_level > 0 THEN
2501 oe_debug_pub.add( 'VALIDATION_ORG_ID = '||L_VALIDATION_ORG ) ;
2502 END IF;
2503 IF l_debug_level > 0 THEN
2504 oe_debug_pub.add( 'ARRAY1 = '||L_SEGMENT_ARRAY ( 1 ) ) ;
2505 END IF;
2506 IF FND_FLEX_EXT.GET_COMBINATION_ID('INV', 'MSTK', 101, SYSDATE, l_n_segments, l_segment_array, l_id, l_validation_org) THEN
2507 l_line_rec.inventory_item_id := l_id;
2508 IF l_debug_level > 0 THEN
2509 oe_debug_pub.add( 'GET CCID = '||L_LINE_REC.INVENTORY_ITEM_ID ) ;
2510 END IF;
2511 ELSE
2512 IF l_debug_level > 0 THEN
2513 oe_debug_pub.add( 'ERROR IN GETTING CCID' ) ;
2514 END IF;
2515 failure_message := fnd_flex_ext.get_message;
2516 OE_MSG_PUB.Add_TEXT(failure_message);
2517 p_return_status := FND_API.G_RET_STS_ERROR;
2518 l_validate_only := FND_API.G_TRUE;
2519 IF l_debug_level > 0 THEN
2520 oe_debug_pub.add( 'FAILURE MESSAGE = ' || SUBSTR ( FAILURE_MESSAGE , 1 , 50 ) ) ;
2521 END IF;
2522 IF l_debug_level > 0 THEN
2523 oe_debug_pub.add( 'FAILURE MESSAGE = ' || SUBSTR ( FAILURE_MESSAGE , 51 , 50 ) ) ;
2524 END IF;
2525 IF l_debug_level > 0 THEN
2526 oe_debug_pub.add( 'FAILURE MESSAGE = ' || SUBSTR ( FAILURE_MESSAGE , 101 , 50 ) ) ;
2527 END IF;
2528 END IF;
2529
2530 ELSIF l_line_rec.inventory_item_id <> FND_API.G_MISS_NUM AND
2531 ((l_segment_array(1) IS NOT NULL) OR
2532 (l_segment_array(2) IS NOT NULL) OR
2533 (l_segment_array(3) IS NOT NULL) OR
2534 (l_segment_array(4) IS NOT NULL) OR
2535 (l_segment_array(5) IS NOT NULL) OR
2536 (l_segment_array(6) IS NOT NULL) OR
2537 (l_segment_array(7) IS NOT NULL) OR
2538 (l_segment_array(8) IS NOT NULL) OR
2539 (l_segment_array(9) IS NOT NULL) OR
2540 (l_segment_array(10) IS NOT NULL) OR
2541 (l_segment_array(11) IS NOT NULL) OR
2542 (l_segment_array(12) IS NOT NULL) OR
2543 (l_segment_array(13) IS NOT NULL) OR
2544 (l_segment_array(14) IS NOT NULL) OR
2545 (l_segment_array(15) IS NOT NULL) OR
2546 (l_segment_array(16) IS NOT NULL) OR
2547 (l_segment_array(17) IS NOT NULL) OR
2548 (l_segment_array(18) IS NOT NULL) OR
2549 (l_segment_array(19) IS NOT NULL) OR
2550 (l_segment_array(20) IS NOT NULL)) THEN
2551 IF l_debug_level > 0 THEN
2552 oe_debug_pub.add( 'INSIDE ROUTINE WHERE BOTH ID AND SEG ARE POPULATED' ) ;
2553 END IF;
2554 l_line_rec.inventory_item_id := FND_API.G_MISS_NUM;
2555 FND_MESSAGE.SET_NAME('ONT','OE_OIM_INVALID_ITEM_ID');
2556 FND_MESSAGE.SET_TOKEN('ORDER_NO', l_orig_sys_document_ref);
2557 FND_MESSAGE.SET_TOKEN('ORDER_SOURCE', l_order_source_id);
2558 OE_MSG_PUB.Add;
2559 p_return_status := FND_API.G_RET_STS_ERROR;
2560 l_validate_only := FND_API.G_TRUE;
2561 IF l_debug_level > 0 THEN
2562 oe_debug_pub.add( 'CANNOT IMPORT ORDER AS BOTH INVENTORY_ITEM_ID AND INVENTORY_ITEM_SEGMENTS ARE POPULATED' ) ;
2563 END IF;
2564 IF l_debug_level > 0 THEN
2565 oe_debug_pub.add( 'ORDER NO: '||L_ORIG_SYS_DOCUMENT_REF ) ;
2566 END IF;
2567 IF l_debug_level > 0 THEN
2568 oe_debug_pub.add( 'ORDER SOURCE: '||L_ORDER_SOURCE_ID ) ;
2569 END IF;
2570
2571 END IF;
2572
2573 l_line_rec.service_reference_order :=
2574 nvl(l_line_rec.service_reference_order, FND_API.G_MISS_CHAR);
2575 l_line_rec.service_reference_line :=
2576 nvl(l_line_rec.service_reference_line, FND_API.G_MISS_CHAR);
2577 l_line_tbl (l_line_count) := l_line_rec;
2578 l_line_val_tbl(l_line_count) := l_line_val_rec;
2579 if l_line_val_tbl(l_line_count).sold_to_org = FND_API.G_MISS_CHAR then
2580 IF l_debug_level > 0 THEN
2581 oe_debug_pub.add( 'L_LINE_TBL.SERVICE_REFERENCE_ORDER ' || ASCII ( L_LINE_TBL ( L_LINE_COUNT ) .SERVICE_REFERENCE_ORDER ) ) ;
2582 END IF;
2583 IF l_debug_level > 0 THEN
2584 oe_debug_pub.add( 'L_LINE_VAL_TBL.SOLD_TO_ORG ' || ASCII ( L_LINE_VAL_TBL ( L_LINE_COUNT ) .SOLD_TO_ORG ) ) ;
2585 END IF;
2586 end if;
2587
2588 l_orig_sys_line_ref := l_line_rec.orig_sys_line_ref;
2589 l_orig_sys_shipment_ref := l_line_rec.orig_sys_shipment_ref;
2590
2591 IF l_debug_level > 0 THEN
2592 oe_debug_pub.add( 'ORIG SYS LINE REF ( '||L_LINE_COUNT||' ) : '|| L_LINE_TBL ( L_LINE_COUNT ) .ORIG_SYS_LINE_REF ) ;
2593 END IF;
2594
2595 IF l_debug_level > 0 THEN
2596 oe_debug_pub.add( 'ORIG SYS SHIPMENT REF ( '||L_LINE_COUNT||' ) : '|| L_LINE_TBL ( L_LINE_COUNT ) .ORIG_SYS_SHIPMENT_REF ) ;
2597 END IF;
2598
2599 BEGIN
2600 oe_Debug_pub.add('calling get line ids'); --bug 4240715
2601 OE_CNCL_UTIL.get_line_ids(l_line_rec,l_line_val_rec);
2602 oe_debug_pub.add('after calling get line ids'||l_line_rec.end_customer_id); --bug 4240715
2603 --
2604 IF l_line_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2605 --
2606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2607 --
2608 ELSIF l_line_rec.return_status = FND_API.G_RET_STS_ERROR THEN
2609 --
2610 RAISE FND_API.G_EXC_ERROR;
2611 --
2612 END IF;
2613 --
2614 --
2615 OE_CNCL_Util.Convert_Miss_To_Null(l_line_rec);
2616 oe_debug_pub.add('after calling convert miss to null'||l_line_rec.end_customer_id); --bug 4240715
2617 --
2618
2619 OE_CNCL_Validate_Line.Attributes( x_return_status => l_return_status
2620 , p_x_line_rec => l_line_rec);
2621 oe_debug_pub.add('after calling validate line'||l_line_rec.end_customer_id); --bug 4240715
2622 --
2623 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2624 --
2625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2626 --
2627 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2628 --
2629 RAISE FND_API.G_EXC_ERROR;
2630 --
2631 END IF;
2632 --
2633
2634 OE_CNCL_Validate_Line.Entity( x_return_status => l_return_status
2635 , p_line_rec => l_line_rec);
2636 oe_debug_pub.add('after calling entity'||l_line_rec.end_customer_id); --bug 4240715
2637 --
2638 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2639 --
2640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2641 --
2642 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2643 --
2644 RAISE FND_API.G_EXC_ERROR;
2645 --
2646 END IF;
2647 --
2648 --{ added for bug 4240715
2649 l_line_tbl (l_line_count) := l_line_rec;
2650 l_line_val_tbl(l_line_count) := l_line_val_rec;
2651 -- bug 4240715}
2652
2653 EXCEPTION
2654 --
2655 WHEN FND_API.G_EXC_ERROR THEN
2656 --
2657 OE_MSG_PUB.reset_msg_context('LINE');
2658 --
2659 RAISE;
2660 --
2661 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2662 --
2663 OE_MSG_PUB.reset_msg_context('LINE');
2664 --
2665 RAISE;
2666 --
2667 WHEN OTHERS THEN
2668 --
2669 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2670 --
2671 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Lines');
2672 --
2673 END IF;
2674 --
2675 OE_MSG_PUB.reset_msg_context('LINE');
2676 --
2677 RAISE;
2678 --
2679 END;
2680
2681 /* -----------------------------------------------------------
2682 Line Discounts/Price adjustments
2683 -----------------------------------------------------------
2684 */
2685 IF l_debug_level > 0 THEN
2686 oe_debug_pub.add( 'BEFORE LINE ADJUSTMENTS LOOP' ) ;
2687 END IF;
2688
2689 OPEN l_line_adj_cursor;
2690 LOOP
2691 FETCH l_line_adj_cursor
2692 INTO l_line_adj_rec.orig_sys_discount_ref
2693 , l_line_adj_rec.change_request_code
2694 , l_line_adj_rec.list_header_id -- changed from discount_id
2695 , l_line_adj_rec.list_line_id -- changed from discount_line_id
2696 , l_line_adj_val_rec.discount
2697 , l_line_adj_rec.percent
2698 , l_line_adj_rec.automatic_flag
2699 , l_line_adj_rec.applied_flag
2700 , l_line_adj_rec.operand
2701 , l_line_adj_rec.arithmetic_operator
2702 , l_line_adj_rec.context
2703 , l_line_adj_rec.attribute1
2704 , l_line_adj_rec.attribute2
2705 , l_line_adj_rec.attribute3
2706 , l_line_adj_rec.attribute4
2707 , l_line_adj_rec.attribute5
2708 , l_line_adj_rec.attribute6
2709 , l_line_adj_rec.attribute7
2710 , l_line_adj_rec.attribute8
2711 , l_line_adj_rec.attribute9
2712 , l_line_adj_rec.attribute10
2713 , l_line_adj_rec.attribute11
2714 , l_line_adj_rec.attribute12
2715 , l_line_adj_rec.attribute13
2716 , l_line_adj_rec.attribute14
2717 , l_line_adj_rec.attribute15
2718 , l_line_adj_rec.request_id
2719 , l_line_adj_rec.operation
2720 , l_line_adj_rec.status_flag
2721 -- Price Adjustment related changes bug# 1220921 (Start)
2722 , l_line_adj_rec.AC_CONTEXT
2723 , l_line_adj_rec.AC_ATTRIBUTE1
2724 , l_line_adj_rec.AC_ATTRIBUTE2
2725 , l_line_adj_rec.AC_ATTRIBUTE3
2726 , l_line_adj_rec.AC_ATTRIBUTE4
2727 , l_line_adj_rec.AC_ATTRIBUTE5
2728 , l_line_adj_rec.AC_ATTRIBUTE6
2729 , l_line_adj_rec.AC_ATTRIBUTE7
2730 , l_line_adj_rec.AC_ATTRIBUTE8
2731 , l_line_adj_rec.AC_ATTRIBUTE9
2732 , l_line_adj_rec.AC_ATTRIBUTE10
2733 , l_line_adj_rec.AC_ATTRIBUTE11
2734 , l_line_adj_rec.AC_ATTRIBUTE12
2735 , l_line_adj_rec.AC_ATTRIBUTE13
2736 , l_line_adj_rec.AC_ATTRIBUTE14
2737 , l_line_adj_rec.AC_ATTRIBUTE15
2738 , l_line_adj_val_rec.LIST_NAME
2739 , l_line_adj_rec.LIST_LINE_TYPE_CODE
2740 , l_line_adj_rec.LIST_LINE_NO
2741 , l_line_adj_val_rec.VERSION_NO
2742 , l_line_adj_rec.INVOICED_FLAG
2743 , l_line_adj_rec.ESTIMATED_FLAG
2744 , l_line_adj_rec.INC_IN_SALES_PERFORMANCE
2745 , l_line_adj_rec.CHARGE_TYPE_CODE
2746 , l_line_adj_rec.CHARGE_SUBTYPE_CODE
2747 , l_line_adj_rec.CREDIT_OR_CHARGE_FLAG
2748 , l_line_adj_rec.INCLUDE_ON_RETURNS_FLAG
2749 , l_line_adj_rec.COST_ID
2750 , l_line_adj_rec.TAX_CODE
2751 , l_line_adj_rec.PARENT_ADJUSTMENT_ID
2752 , l_line_adj_rec.MODIFIER_MECHANISM_TYPE_CODE
2753 , l_line_adj_rec.MODIFIED_FROM
2754 , l_line_adj_rec.MODIFIED_TO
2755 , l_line_adj_rec.UPDATED_FLAG
2756 , l_line_adj_rec.UPDATE_ALLOWED
2757 , l_line_adj_rec.CHANGE_REASON_CODE
2758 , l_line_adj_rec.CHANGE_REASON_TEXT
2759 , l_line_adj_rec.PRICING_PHASE_ID
2760 , l_line_adj_rec.ADJUSTED_AMOUNT
2761 , l_adj_line_ref
2762 -- Price Adjustment related changes bug# 1220921 (End)
2763 ;
2764 EXIT WHEN l_line_adj_cursor%NOTFOUND;
2765
2766 l_line_adj_rec.line_index := l_line_count;
2767
2768 /*
2769 IF l_line_adj_rec.operation = 'INSERT' THEN
2770 l_line_adj_rec.operation := 'CREATE';
2771 END IF;
2772 */
2773
2774 l_line_adj_count := l_line_adj_count + 1;
2775 l_line_adj_tbl (l_line_adj_count) := l_line_adj_rec;
2776 l_line_adj_val_tbl (l_line_adj_count) := l_line_adj_val_rec;
2777
2778 l_adj_line_ref_tbl(l_line_adj_count) := l_adj_line_ref;
2779
2780 IF l_debug_level > 0 THEN
2781 oe_debug_pub.add( 'LINE ADJUSTMENT REF ( '||L_LINE_ADJ_COUNT||' ) : '|| L_LINE_ADJ_TBL ( L_LINE_ADJ_COUNT ) .ORIG_SYS_DISCOUNT_REF ) ;
2782 END IF;
2783
2784
2785 BEGIN
2786 --
2787 OE_CNCL_UTIL.get_line_adj_ids(l_line_adj_rec,l_line_adj_val_rec);
2788 --
2789 IF l_line_adj_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2790 --
2791 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2792 --
2793 ELSIF l_line_adj_rec.return_status = FND_API.G_RET_STS_ERROR THEN
2794 --
2795 RAISE FND_API.G_EXC_ERROR;
2796 --
2797 END IF;
2798 --
2799
2800 --
2801 OE_CNCL_Util.Convert_Miss_To_Null(l_line_adj_rec);
2802 --
2803
2804 OE_CNCL_Validate_Line_Adj.Attributes( x_return_status => l_return_status
2805 , p_Line_Adj_rec => l_line_adj_rec);
2806
2807 --
2808 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2809 --
2810 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2811 --
2812 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2813 --
2814 RAISE FND_API.G_EXC_ERROR;
2815 --
2816 END IF;
2817 --
2818
2819 OE_CNCL_Validate_Line_Adj.Entity( x_return_status => l_return_status
2820 , p_Line_Adj_rec => l_line_adj_rec);
2821 --
2822 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2823 --
2824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2825 --
2826 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2827 --
2828 RAISE FND_API.G_EXC_ERROR;
2829 --
2830 END IF;
2831 --
2832
2833 EXCEPTION
2834 --
2835 WHEN FND_API.G_EXC_ERROR THEN
2836 --
2837 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
2838 --
2839 RAISE;
2840 --
2841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2842 --
2843 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
2844 --
2845 RAISE;
2846 --
2847 WHEN OTHERS THEN
2848 --
2849 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2850 --
2851 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Line_Adjs');
2852 --
2853 END IF;
2854 --
2855 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
2856 --
2857 RAISE;
2858 --
2859 END;
2860 --
2861 END LOOP;
2862
2863 CLOSE l_line_adj_cursor;
2864
2865
2866 /* -----------------------------------------------------------
2867 Line Sales Credits
2868 -----------------------------------------------------------
2869 */
2870 IF l_debug_level > 0 THEN
2871 oe_debug_pub.add( 'BEFORE LINE SALES CREDITS LOOP' ) ;
2872 END IF;
2873
2874 OPEN l_line_scredit_cursor;
2875 LOOP
2876 FETCH l_line_scredit_cursor
2877 INTO l_line_scredit_rec.orig_sys_credit_ref
2878 , l_line_scredit_rec.change_request_code
2879 , l_line_scredit_rec.salesrep_id
2880 , l_line_scredit_val_rec.salesrep
2881 , l_line_scredit_rec.sales_credit_type_id
2882 , l_line_scredit_val_rec.sales_credit_type
2883 , l_line_scredit_rec.percent
2884 , l_line_scredit_rec.context
2885 , l_line_scredit_rec.attribute1
2886 , l_line_scredit_rec.attribute2
2887 , l_line_scredit_rec.attribute3
2888 , l_line_scredit_rec.attribute4
2889 , l_line_scredit_rec.attribute5
2890 , l_line_scredit_rec.attribute6
2891 , l_line_scredit_rec.attribute7
2892 , l_line_scredit_rec.attribute8
2893 , l_line_scredit_rec.attribute9
2894 , l_line_scredit_rec.attribute10
2895 , l_line_scredit_rec.attribute11
2896 , l_line_scredit_rec.attribute12
2897 , l_line_scredit_rec.attribute13
2898 , l_line_scredit_rec.attribute14
2899 , l_line_scredit_rec.attribute15
2900 , l_line_scredit_rec.operation
2901 , l_line_scredit_rec.status_flag
2902 , l_scredit_line_ref
2903 ;
2904 EXIT WHEN l_line_scredit_cursor%NOTFOUND;
2905
2906 l_line_scredit_rec.line_index := l_line_count;
2907
2908 /*
2909 IF l_line_scredit_rec.operation = 'INSERT' THEN
2910 l_line_scredit_rec.operation := 'CREATE';
2911 END IF;
2912 */
2913
2914 l_line_scredit_count := l_line_scredit_count + 1;
2915 l_line_scredit_tbl (l_line_scredit_count) := l_line_scredit_rec;
2916 l_line_scredit_val_tbl (l_line_scredit_count) := l_line_scredit_val_rec;
2917
2918 l_scredit_line_ref_tbl(l_line_scredit_count) := l_scredit_line_ref;
2919
2920 IF l_debug_level > 0 THEN
2921 oe_debug_pub.add( 'LINE SALESCREDITS REF ( '||L_LINE_SCREDIT_COUNT||' ) : '|| L_LINE_SCREDIT_TBL ( L_LINE_SCREDIT_COUNT ) .ORIG_SYS_CREDIT_REF ) ;
2922 END IF;
2923
2924
2925 BEGIN
2926 --
2927 OE_CNCL_UTIL.get_line_scredit_ids(l_line_scredit_rec,l_line_scredit_val_rec);
2928 --
2929 IF l_line_scredit_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2930 --
2931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2932 --
2933 ELSIF l_line_scredit_rec.return_status = FND_API.G_RET_STS_ERROR THEN
2934 --
2935 RAISE FND_API.G_EXC_ERROR;
2936 --
2937 END IF;
2938 --
2939
2940 --
2941 OE_CNCL_Util.Convert_Miss_To_Null(l_line_scredit_rec);
2942 --
2943
2944
2945 OE_CNCL_Validate_Line_Scredit.Attributes( x_return_status => l_return_status
2946 , p_Line_Scredit_rec => l_line_scredit_rec);
2947 --
2948 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2949 --
2950 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2951 --
2952 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2953 --
2954 RAISE FND_API.G_EXC_ERROR;
2955 --
2956 END IF;
2957 --
2958
2959 OE_CNCL_Validate_Line_Scredit.Entity( x_return_status => l_return_status
2960 , p_Line_Scredit_rec => l_line_scredit_rec);
2961
2962 --
2963 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2964 --
2965 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2966 --
2967 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2968 --
2969 RAISE FND_API.G_EXC_ERROR;
2970 --
2971 END IF;
2972 --
2973
2974 EXCEPTION
2975 --
2976 WHEN FND_API.G_EXC_ERROR THEN
2977 --
2978 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
2979 --
2980 RAISE;
2981 --
2982 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2983 --
2984 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
2985 --
2986 RAISE;
2987 --
2988 WHEN OTHERS THEN
2989 --
2990 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2991 --
2992 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Line_Scredits');
2993 --
2994 END IF;
2995 --
2996 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
2997 --
2998 RAISE;
2999 --
3000 END;
3001
3002
3003 END LOOP;
3004 CLOSE l_line_scredit_cursor;
3005
3006 END LOOP; /* Lines cursor */
3007 CLOSE l_line_cursor;
3008
3009
3010
3011
3012 /*------------------------+
3013 | |
3014 | INSERTION BEGINS HERE |
3015 | |
3016 +------------------------*/
3017
3018 --
3019 --Header Insert
3020 --
3021 BEGIN
3022 --
3023 OE_Header_Util.get_order_number(l_header_rec,l_header_rec_old);
3024
3025
3026 IF l_debug_level > 0 THEN
3027 oe_debug_pub.add( 'ORDER NUMBER'|| L_HEADER_REC.ORDER_NUMBER , 2 ) ;
3028 END IF;
3029 --
3030 SELECT OE_ORDER_HEADERS_S.NEXTVAL
3031 INTO l_header_rec.header_id
3032 FROM DUAL;
3033 --
3034 OE_CNCL_Util.Convert_Miss_To_Null(l_header_rec);
3035 --
3036 l_header_rec.creation_date := SYSDATE;
3037 l_header_rec.created_by := FND_GLOBAL.USER_ID;
3038 -- bug 4002850, removed quotes around 1.0 below
3039 l_header_rec.version_number := nvl(l_header_rec.version_number,1.0);
3040 l_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
3041 l_header_rec.last_update_date := sysdate;
3042 l_header_rec.booked_flag := nvl(l_header_rec.booked_flag, 'Y');
3043 l_header_rec.order_category_code :=
3044 nvl(l_header_rec.order_category_code, 'MIXED');
3045 l_header_rec.open_flag := 'N';
3046 l_header_rec.flow_status_code := 'CLOSED';
3047 --
3048 IF l_debug_level > 0 THEN
3049 oe_debug_pub.add( 'USER ID IS '|| FND_GLOBAL.USER_ID ) ;
3050 END IF;
3051 IF l_debug_level > 0 THEN
3052 oe_debug_pub.add( 'RESP ID IS '|| FND_GLOBAL.RESP_ID ) ;
3053 END IF;
3054 IF l_debug_level > 0 THEN
3055 oe_debug_pub.add( 'APPL ID IS '|| FND_GLOBAL.RESP_APPL_ID ) ;
3056 END IF;
3057
3058 --
3059 OE_Header_Util.Insert_Row(l_header_rec);
3060 --
3061 IF l_debug_level > 0 THEN
3062 oe_debug_pub.add( 'HEADER ID : ' || L_HEADER_REC.HEADER_ID , 2 ) ;
3063 END IF;
3064
3065
3066 EXCEPTION
3067 --
3068 WHEN FND_API.G_EXC_ERROR THEN
3069 --
3070 l_header_rec.return_status := FND_API.G_RET_STS_ERROR;
3071 --
3072 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3073 OE_MSG_PUB.reset_msg_context('HEADER');
3074 --
3075 RAISE;
3076 --
3077 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3078 --
3079 l_header_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3080 --
3081 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3082 OE_MSG_PUB.reset_msg_context('HEADER');
3083 --
3084 RAISE;
3085 --
3086 WHEN OTHERS THEN
3087 --
3088 l_header_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3089 --
3090 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3091 --
3092 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3093 'Header');
3094 --
3095 END IF;
3096 --
3097 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3098 OE_MSG_PUB.reset_msg_context('HEADER');
3099 --
3100 RAISE;
3101 --
3102 END;
3103 --
3104 --
3105 -- Loop for Sales Credits
3106 --
3107 --
3108 FOR I in 1..l_header_scredit_tbl.count
3109 --
3110 LOOP
3111 --
3112 BEGIN
3113 --
3114 SELECT OE_SALES_CREDITS_S.NEXTVAL
3115 INTO l_header_scredit_tbl(I).sales_credit_id
3116 FROM DUAL;
3117 --
3118 l_header_scredit_tbl(I).header_id := l_header_rec.header_id;
3119 --
3120 OE_CNCL_Util.Convert_Miss_To_Null(l_header_scredit_tbl(I));
3121 --
3122 l_header_scredit_tbl(I).creation_date := SYSDATE;
3123 l_header_scredit_tbl(I).created_by := FND_GLOBAL.USER_ID;
3124 l_header_scredit_tbl(I).last_updated_by := FND_GLOBAL.USER_ID;
3125 l_header_scredit_tbl(I).last_update_date := sysdate;
3126 --
3127 OE_Header_Scredit_Util.Insert_Row(l_header_scredit_tbl(I));
3128 --
3129 EXCEPTION
3130 --
3131 WHEN FND_API.G_EXC_ERROR THEN
3132 --
3133 l_header_scredit_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
3134 --
3135 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3136 OE_MSG_PUB.reset_msg_context('HEADER_SCREDIT');
3137 --
3138 RAISE;
3139 --
3140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3141 --
3142 l_header_scredit_tbl(I).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3143 --
3144 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3145 OE_MSG_PUB.reset_msg_context('HEADER_SCREDIT');
3146 --
3147 RAISE;
3148 --
3149 WHEN OTHERS THEN
3150 --
3151 l_header_scredit_tbl(I).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3152 --
3153 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3154 --
3155 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3156 'Header_Scredits');
3157 --
3158 END IF;
3159 --
3160 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3161 OE_MSG_PUB.reset_msg_context('HEADER_SCREDIT');
3162 --
3163 RAISE;
3164 --
3165 END;
3166 --
3167 END LOOP;
3168 --
3169 --
3170 -- Loop for Header Price Adjustment
3171 --
3172 --
3173 --
3174 FOR I in 1..l_header_adj_tbl.COUNT
3175 --
3176 LOOP
3177 --
3178 BEGIN
3179 --
3180 SELECT OE_PRICE_ADJUSTMENTS_S.NEXTVAL
3181 INTO l_header_adj_tbl(I).price_adjustment_id
3182 FROM DUAL;
3183 --
3184 l_header_adj_tbl(I).header_id := l_header_rec.header_id;
3185 --
3186 OE_CNCL_Util.Convert_Miss_To_Null(l_header_adj_tbl(I));
3187 --
3188 l_header_adj_tbl(I).creation_date := SYSDATE;
3189 l_header_adj_tbl(I).created_by := FND_GLOBAL.USER_ID;
3190 l_header_adj_tbl(I).last_updated_by := FND_GLOBAL.USER_ID;
3191 l_header_adj_tbl(I).last_update_date := sysdate;
3192 --
3193 OE_Header_Adj_Util.Insert_Row(l_header_adj_tbl(I));
3194 --
3195 EXCEPTION
3196 --
3197 WHEN FND_API.G_EXC_ERROR THEN
3198 --
3199 l_header_adj_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
3200 --
3201 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3202 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
3203 --
3204 RAISE;
3205 --
3206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3207 --
3208 l_header_adj_tbl(I).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3209 --
3210 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3211 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
3212 --
3213 RAISE;
3214 --
3215 WHEN OTHERS THEN
3216 --
3217 l_header_adj_tbl(I).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3218 --
3219 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3220 --
3221 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3222 'Header_Adjs');
3223 --
3224 END IF;
3225 --
3226 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3227 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
3228 --
3229 RAISE;
3230 --
3231 END;
3232 --
3233 END LOOP;
3234 --
3235 --
3236 --
3237 -- Line insert
3238 --
3239 --
3240 --
3241 FOR I in 1..l_line_tbl.COUNT
3242 --
3243 LOOP
3244 --
3245 --
3246 SELECT OE_ORDER_LINES_S.NEXTVAL
3247 INTO l_line_tbl(I).line_id
3248 FROM DUAL;
3249 --
3250 -- { Start Before insert check is the item_type_code is
3251 -- present. Call get_item_type_code function
3252 -- bug 1949855
3253
3254
3255 l_line_rec := l_line_tbl(I);
3256 l_line_tbl(I).item_type_code := OE_CNCL_Validate_Line.get_item_type
3257 (l_line_rec);
3258
3259 IF l_debug_level > 0 THEN
3260 oe_debug_pub.add( 'ITEM TYPE CODE => ' || L_LINE_TBL ( I ) .ITEM_TYPE_CODE ) ;
3261 END IF;
3262 -- End Before insert check is the item_type_code}
3263
3264 --
3265 IF ((l_line_tbl(I).item_type_code = 'MODEL') AND
3266 (l_line_tbl(I).orig_sys_line_ref = l_line_tbl(I).top_model_line_ref)) THEN
3267
3268 l_line_tbl(I).top_model_line_id := l_line_tbl(I).line_id;
3269
3270 ELSIF l_line_tbl(I).item_type_code = 'SERVICE' THEN
3271 --
3272 IF l_line_tbl(I).service_reference_type_code <> 'ORDER' THEN
3273 --
3274 l_line_tbl(I).service_reference_system_id := TO_NUMBER(l_line_tbl(I).service_reference_system);
3275 --
3276 END IF;
3277 -- Bug 13906241 start
3278 -- The logic for reference type ORDER never got executed earlier
3279 IF l_line_tbl(I).service_reference_type_code = 'ORDER' THEN
3280
3281 IF l_debug_level > 0 THEN
3282 oe_debug_pub.add( 'OEXVCIMB 1 Entered for ref type ORDER');
3283 END IF;
3284 --
3285 FOR N IN 1..l_line_tbl.COUNT
3286 --
3287 LOOP
3288 -- LOGIC: While importing, service number is NULL and if orig sys line and doc ref
3289 -- match then ref line id is to be populated
3290 -- To derive Service number, take a counter l_serv_num_cnt, if for the same ref line ids
3291 -- multiple service lines are attached (like 1.1...1, 1.1...2) then increment counter
3292 -- If a different ref line_id (for 2.1...1)is encountered, reset counter to 0.
3293 -- The logic works as cursor is ordered by orig sys line ref
3294 -- Commented Due to 14016683
3295 /*IF (l_line_tbl(I).orig_sys_document_ref = l_line_tbl(N).service_reference_order
3296 AND l_line_tbl(I).orig_sys_line_ref = l_line_tbl(N).service_reference_line
3297 AND l_line_tbl(I).service_number is NULL) THEN*/
3298 -- 14016683 Start, Loop with index I corresponds to service Ref Line and Loop with index N
3299 -- corresponds to All Lines; In previous case (13906241 fix) Service Ref Line ID for the Service Line
3300 -- had it's own line id instead of the parent line. Corrected the IF condition check
3301 IF (l_line_tbl(N).orig_sys_document_ref = l_line_tbl(I).service_reference_order
3302 AND l_line_tbl(N).orig_sys_line_ref = l_line_tbl(I).service_reference_line
3303 AND l_line_tbl(I).service_number is NULL) THEN
3304 -- 14016683 Change End
3305
3306 -- Reset counter for a different line id
3307 IF(l_serv_num_cnt <>0 and l_servref_lineid <> l_line_tbl(N).line_id) then
3308 l_serv_num_cnt := 0;
3309 End If;
3310
3311 l_serv_num_cnt := l_serv_num_cnt+1;
3312
3313 IF l_debug_level > 0 THEN
3314 oe_debug_pub.add( 'OEXVCIMB 2 service num= '||l_serv_num_cnt);
3315 END IF;
3316 --
3317 l_line_tbl(I).service_reference_line_id := l_line_tbl(N).line_id;
3318 l_line_tbl(I).service_number := l_serv_num_cnt;
3319 l_servref_lineid := l_line_tbl(N).line_id;
3320 --
3321 END IF;
3322 --
3323 END LOOP;
3324 --
3325 END IF;
3326 --- Bug 13906241 End
3327 --
3328 END IF;
3329 --
3330
3331 l_line_tbl(I).header_id := l_header_rec.header_id;
3332 --
3333 OE_CNCL_Util.Convert_Miss_To_Null(l_line_tbl(I));
3334 --
3335
3336 l_line_tbl(I).flow_status_code := 'CLOSED';
3337
3338 l_line_tbl(I).creation_date := SYSDATE;
3339 l_line_tbl(I).created_by := FND_GLOBAL.USER_ID;
3340 l_line_tbl(I).last_updated_by := FND_GLOBAL.USER_ID;
3341 l_line_tbl(I).last_update_date := SYSDATE;
3342
3343 --
3344
3345 --l_line_tbl(I).line_category_code := 'ORDER';
3346 --bug 1857305
3347
3348 IF(nvl(l_line_val_tbl(I).line_type,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR) THEN
3349
3350 BEGIN
3351
3352 SELECT order_category_code
3353 INTO l_line_val_tbl(I).line_type
3354 FROM OE_LINE_TYPES_V
3355 WHERE line_type_id = l_line_tbl(I).line_type_id ;
3356
3357 EXCEPTION
3358
3359 WHEN NO_DATA_FOUND THEN
3360
3361 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3362 THEN
3363
3364 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3365 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','line_type_id');
3366 OE_MSG_PUB.Add;
3367
3368 END IF;
3369
3370 WHEN OTHERS THEN
3371
3372 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3373 THEN
3374 OE_MSG_PUB.Add_Exc_Msg
3375 ( G_PKG_NAME
3376 , 'Order_Import'
3377 );
3378 END IF;
3379
3380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3381
3382 END;
3383
3384 END IF;
3385
3386 IF (nvl(l_line_val_tbl(I).line_type, 'ORDER') = 'ORDER' OR l_line_val_tbl(I).line_type = FND_API.G_MISS_CHAR) THEN
3387
3388 l_line_tbl(I).line_category_code := 'ORDER';
3389
3390 ELSIF (nvl(l_line_val_tbl(I).line_type, 'ORDER') = 'RETURN') THEN
3391
3392 l_line_tbl(I).line_category_code := 'RETURN';
3393
3394 ELSE
3395
3396 l_line_tbl(I).line_category_code := 'ORDER';
3397
3398 END IF;
3399
3400 l_line_tbl(I).open_flag := 'N';
3401 l_line_tbl(I).shipment_number := nvl(l_line_tbl(I).shipment_number,1);
3402 l_line_tbl(I).booked_flag := 'Y';
3403 If l_line_tbl(I).cancelled_flag = 'N' Then
3404 l_line_tbl(I).cancelled_quantity := 0;
3405 l_line_tbl(I).cancelled_quantity2 := 0; -- INVCONV
3406 End If;
3407
3408 --
3409 --
3410 -- Loop for Line Sales Credit
3411 --
3412 --
3413 /* FOR J in 1..l_line_scredit_tbl.COUNT
3414 --
3415 LOOP
3416 --
3417 IF l_scredit_line_ref_tbl(J) = l_line_tbl(I).orig_sys_line_ref THEN
3418 --
3419 SELECT OE_SALES_CREDITS_S.NEXTVAL
3420 INTO l_line_scredit_tbl(J).sales_credit_id
3421 FROM DUAL;
3422 --
3423 l_line_scredit_tbl(J).header_id := l_header_rec.header_id;
3424 --
3425 l_line_scredit_tbl(J).line_id := l_line_tbl(I).line_id;
3426 --
3427 OE_CNCL_Util.Convert_Miss_To_Null(l_line_scredit_tbl(J));
3428 --
3429 l_line_scredit_tbl(J).creation_date := SYSDATE;
3430 l_line_scredit_tbl(J).created_by := FND_GLOBAL.USER_ID;
3431 l_line_scredit_tbl(J).last_updated_by := FND_GLOBAL.USER_ID;
3432 l_line_scredit_tbl(J).last_update_date := sysdate;
3433 --
3434 END IF;
3435 --
3436 END LOOP;*/
3437 --
3438 --
3439 -- Loop for Line Price Adjustment
3440 --
3441 --
3442 /* FOR K in 1..l_line_adj_tbl.COUNT
3443 --
3444 LOOP
3445 --
3446 IF l_adj_line_ref_tbl(K) = l_line_tbl(I).orig_sys_line_ref THEN
3447 --
3448 SELECT OE_PRICE_ADJUSTMENTS_S.NEXTVAL
3449 INTO l_line_adj_tbl(K).price_adjustment_id
3450 FROM DUAL;
3451 --
3452 l_line_adj_tbl(K).header_id := l_header_rec.header_id;
3453 l_line_adj_tbl(K).line_id := l_line_tbl(I).line_id;
3454 --
3455 OE_CNCL_Util.Convert_Miss_To_Null(l_line_adj_tbl(K));
3456 --
3457 l_line_adj_tbl(K).creation_date := SYSDATE;
3458 l_line_adj_tbl(K).created_by := FND_GLOBAL.USER_ID;
3459 l_line_adj_tbl(K).last_updated_by := FND_GLOBAL.USER_ID;
3460 l_line_adj_tbl(K).last_update_date := sysdate;
3461 --
3462 END IF;
3463 --
3464 END LOOP;--line adj*/
3465 --
3466 END LOOP; --end line
3467 --
3468
3469
3470 /*-------------------------+
3471 | |
3472 | NON-MODEL HANDLING CODE |
3473 | |
3474 +--------------------------*/
3475
3476
3477
3478 FOR L in 1..l_line_tbl.COUNT
3479 --
3480 LOOP
3481 --
3482
3483 BEGIN
3484
3485 --check for non models
3486
3487
3488 IF ((l_line_tbl(L).item_type_code <> 'MODEL' AND l_line_tbl(L).item_type_code <> 'STANDARD' ) AND
3489 (l_line_tbl(L).orig_sys_line_ref <> l_line_tbl(L).top_model_line_ref)) THEN
3490
3491 FOR M in 1..l_line_tbl.COUNT
3492 LOOP
3493
3494 IF(l_line_tbl(L).top_model_line_ref = l_line_tbl(M).orig_sys_line_ref) THEN
3495 l_line_tbl(L).top_model_line_id := l_line_tbl(M).line_id;
3496 EXIT;
3497 END IF;
3498
3499 END LOOP;
3500 ---- bug# 8613185 : Start
3501 FOR N in 1..l_line_tbl.COUNT
3502 LOOP
3503
3504 IF(l_line_tbl(L).link_to_line_ref = l_line_tbl(N).orig_sys_line_ref) THEN
3505
3506 l_line_tbl(L).link_to_line_id := l_line_tbl(N).line_id;
3507 EXIT;
3508
3509 END IF;
3510 END LOOP;
3511 ---- bug# 8613185 : End
3512
3513 ELSIF l_line_tbl(L).item_type_code = 'SERVICE' THEN
3514 --
3515 IF l_line_tbl(L).service_reference_type_code = 'ORDER' THEN
3516 --
3517 FOR N IN 1..l_line_tbl.COUNT
3518 --
3519 LOOP
3520 --
3521 IF (l_line_tbl(L).orig_sys_document_ref = l_line_tbl(N).service_reference_order AND l_line_tbl(L).orig_sys_line_ref = l_line_tbl(N).service_reference_line) THEN
3522 --
3523 l_line_tbl(L).service_reference_line_id := l_line_tbl(N).line_id;
3524 --
3525 END IF;
3526 --
3527 END LOOP;
3528 --
3529 END IF;
3530 --
3531 END IF;
3532 --
3533
3534 oe_debug_pub.add( ' ORIG SYS LINE REF ( '|| L ||' ) : '|| L_LINE_TBL ( L ) .ORIG_SYS_LINE_REF ) ;
3535 oe_debug_pub.add( ' top_model_line_ref ( '|| L ||' ) : '|| L_LINE_TBL ( L ) .top_model_line_ref ) ;
3536 oe_debug_pub.add( ' top_model_line_id ( '|| L ||' ) : '|| L_LINE_TBL ( L ) .top_model_line_id ) ;
3537 oe_debug_pub.add( ' link_to_line_ref ( '|| L ||' ) : '|| L_LINE_TBL ( L ) .link_to_line_ref ) ;
3538 oe_debug_pub.add( ' link_to_line_id ( '|| L ||' ) : '|| L_LINE_TBL ( L ) .link_to_line_id ) ;
3539
3540 OE_Line_Util.Insert_Row(l_line_tbl(L));
3541
3542 FOR J in 1..l_line_scredit_tbl.COUNT
3543 --
3544 LOOP
3545 --
3546 BEGIN
3547 --
3548 --
3549 IF l_debug_level > 0 THEN
3550 oe_debug_pub.add( TO_CHAR ( J ) , 2 ) ;
3551 END IF;
3552 IF l_debug_level > 0 THEN
3553 oe_debug_pub.add( 'L_SCREDIT_LINE_REF_TBL ' || L_SCREDIT_LINE_REF_TBL ( J ) , 2 ) ;
3554 END IF;
3555 IF l_scredit_line_ref_tbl(J) = l_line_tbl(L).orig_sys_line_ref THEN
3556 --
3557 IF l_debug_level > 0 THEN
3558 oe_debug_pub.add( 'INSIDE IF STATEMENT**********************' , 2 ) ;
3559 END IF;
3560 SELECT OE_SALES_CREDITS_S.NEXTVAL
3561 INTO l_line_scredit_tbl(J).sales_credit_id
3562 FROM DUAL;
3563 --
3564 l_line_scredit_tbl(J).header_id := l_header_rec.header_id;
3565 --
3566 l_line_scredit_tbl(J).line_id := l_line_tbl(L).line_id;
3567 --
3568 OE_CNCL_Util.Convert_Miss_To_Null(l_line_scredit_tbl(J));
3569 --
3570 l_line_scredit_tbl(J).creation_date := SYSDATE;
3571 l_line_scredit_tbl(J).created_by := FND_GLOBAL.USER_ID;
3572 l_line_scredit_tbl(J).last_updated_by := FND_GLOBAL.USER_ID;
3573 l_line_scredit_tbl(J).last_update_date := sysdate;
3574
3575 OE_Line_Scredit_Util.Insert_Row(l_line_scredit_tbl(J));
3576 --
3577 END IF;
3578 --
3579
3580 --
3581 EXCEPTION
3582 --
3583 WHEN FND_API.G_EXC_ERROR THEN
3584 --
3585 l_line_scredit_tbl(J).return_status := FND_API.G_RET_STS_ERROR;
3586 --
3587 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3588 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
3589 --
3590 RAISE;
3591 --
3592 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3593 --
3594 l_line_scredit_tbl(J).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3595 --
3596 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3597 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
3598 --
3599 RAISE;
3600 --
3601 WHEN OTHERS THEN
3602 --
3603 l_line_scredit_tbl(J).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3604 --
3605 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3606 --
3607 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3608 'Line_Scredits');
3609 --
3610 END IF;
3611 --
3612 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3613 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
3614 --
3615 RAISE;
3616 --
3617 END;
3618 --
3619 END LOOP; --scredit line
3620
3621
3622 FOR K in 1..l_line_adj_tbl.COUNT
3623 --
3624 LOOP
3625 --
3626 BEGIN
3627 --
3628 IF l_adj_line_ref_tbl(K) = l_line_tbl(L).orig_sys_line_ref THEN
3629 --
3630 SELECT OE_PRICE_ADJUSTMENTS_S.NEXTVAL
3631 INTO l_line_adj_tbl(K).price_adjustment_id
3632 FROM DUAL;
3633 --
3634 l_line_adj_tbl(K).header_id := l_header_rec.header_id;
3635 l_line_adj_tbl(K).line_id := l_line_tbl(L).line_id;
3636 --
3637 OE_CNCL_Util.Convert_Miss_To_Null(l_line_adj_tbl(K));
3638 --
3639 l_line_adj_tbl(K).creation_date := SYSDATE;
3640 l_line_adj_tbl(K).created_by := FND_GLOBAL.USER_ID;
3641 l_line_adj_tbl(K).last_updated_by := FND_GLOBAL.USER_ID;
3642 l_line_adj_tbl(K).last_update_date := SYSDATE;
3643 --
3644 -- JAUTOMO: TO DO
3645 -- l_line_adj_tbl(K).update_flag := 'N';
3646
3647 OE_Line_Adj_Util.Insert_Row(l_line_adj_tbl(K));
3648 --
3649 END IF;
3650 --
3651
3652 --
3653 EXCEPTION
3654 --
3655 WHEN FND_API.G_EXC_ERROR THEN
3656 --
3657 l_line_adj_tbl(K).return_status := FND_API.G_RET_STS_ERROR;
3658 --
3659 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3660 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
3661 --
3662 RAISE;
3663 --
3664 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3665 --
3666 l_line_adj_tbl(K).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3667 --
3668 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3669 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
3670 --
3671 RAISE;
3672 --
3673 WHEN OTHERS THEN
3674 --
3675 l_line_adj_tbl(K).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3676 --
3677 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3678 --
3679 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3680 'Line_Adjs');
3681 --
3682 END IF;
3683 --
3684 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3685 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
3686 --
3687 RAISE;
3688 --
3689 END;
3690 --
3691 END LOOP;--line adj
3692
3693
3694 EXCEPTION
3695 --
3696 WHEN FND_API.G_EXC_ERROR THEN
3697 --
3698 l_line_tbl(L).return_status := FND_API.G_RET_STS_ERROR;
3699 --
3700 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3701 OE_MSG_PUB.reset_msg_context('LINE');
3702 --
3703 RAISE;
3704 --
3705 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3706 --
3707 l_line_tbl(L).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3708 --
3709 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3710 OE_MSG_PUB.reset_msg_context('LINE');
3711 --
3712 RAISE;
3713 --
3714 WHEN OTHERS THEN
3715 --
3716 l_line_tbl(L).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3717 --
3718 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3719 --
3720 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3721 'Lines');
3722 --
3723 END IF;
3724 --
3725 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3726 OE_MSG_PUB.reset_msg_context('LINE');
3727 --
3728 RAISE;
3729 --
3730 END;
3731 --
3732
3733
3734 END LOOP; --non-model and service
3735 --
3736
3737
3738
3739
3740
3741
3742 EXCEPTION
3743 --
3744 WHEN FND_API.G_EXC_ERROR THEN
3745 --
3746 p_return_status := FND_API.G_RET_STS_ERROR;
3747 --
3748 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3749 --
3750 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3751 --
3752 WHEN OTHERS THEN
3753 --
3754 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3755 --
3756 END;
3757
3758 --END LOOP;
3759 CLOSE l_header_cursor;
3760 ----------------------------------------
3761
3762 /* -----------------------------------------------------------
3763 Delete order from interface tables
3764 -----------------------------------------------------------
3765 */
3766 -- aksingh
3767 IF l_debug_level > 0 THEN
3768 oe_debug_pub.add( 'L_VALIDATE_ONLY '||L_VALIDATE_ONLY ) ;
3769 END IF;
3770 IF l_debug_level > 0 THEN
3771 oe_debug_pub.add( 'P_RETURN_STATUS '||P_RETURN_STATUS ) ;
3772 END IF;
3773
3774 -- aksingh IF p_validate_only = FND_API.G_FALSE AND
3775 IF l_validate_only = FND_API.G_FALSE AND
3776 p_return_status = FND_API.G_RET_STS_SUCCESS /* S=Success */
3777 THEN
3778 l_delete_flag := 'Y';
3779 ELSE
3780 l_delete_flag := 'N';
3781 END IF;
3782
3783 IF l_debug_level > 0 THEN
3784 oe_debug_pub.add( 'DELETE FLAG '||L_DELETE_FLAG ) ;
3785 END IF;
3786
3787 IF l_delete_flag = 'Y' THEN
3788
3789 IF l_debug_level > 0 THEN
3790 oe_debug_pub.add( 'BEFORE DELETING ORDER FROM INTERFACE TABLES' ) ;
3791 END IF;
3792
3793 OE_ORDER_IMPORT_UTIL_PVT.Delete_Order (
3794 p_request_id => l_request_id,
3795 p_order_source_id => l_order_source_id,
3796 p_orig_sys_document_ref => l_orig_sys_document_ref,
3797 p_sold_to_org_id => l_sold_to_org_id,
3798 p_sold_to_org => l_sold_to_org,
3799 p_change_sequence => l_change_sequence,
3800 p_return_status => l_return_status_del_ord
3801 );
3802
3803 /* -----------------------------------------------------------
3804 Set Return Status
3805 -----------------------------------------------------------
3806 */
3807 IF l_debug_level > 0 THEN
3808 oe_debug_pub.add( 'DELETE ORDER RETURN STATUS: '||L_RETURN_STATUS_DEL_ORD ) ;
3809 END IF;
3810
3811 IF l_return_status_del_ord IN (FND_API.G_RET_STS_ERROR)
3812 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR)
3813 THEN p_return_status := FND_API.G_RET_STS_ERROR;
3814 ELSIF l_return_status_del_ord IN (FND_API.G_RET_STS_UNEXP_ERROR)
3815 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR,
3816 FND_API.G_RET_STS_UNEXP_ERROR)
3817 THEN p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3818 END IF;
3819
3820 END IF; /* l_delete_flag = 'Y' */
3821
3822
3823 /* -----------------------------------------------------------
3824 Commit or rollback the transaction
3825 -----------------------------------------------------------
3826 */
3827 IF l_debug_level > 0 THEN
3828 oe_debug_pub.add( 'BEFORE COMMIT OR ROLLBACK' ) ;
3829 END IF;
3830
3831 IF l_validate_only = FND_API.G_TRUE
3832 OR p_return_status in (FND_API.G_RET_STS_ERROR, -- E:Expected error
3833 FND_API.G_RET_STS_UNEXP_ERROR) -- U:Unexpected error
3834 THEN
3835 l_commit_flag := 'N';
3836 ELSE
3837 l_commit_flag := 'Y';
3838 END IF;
3839
3840 IF l_debug_level > 0 THEN
3841 oe_debug_pub.add( 'COMMIT FLAG '||L_COMMIT_FLAG ) ;
3842 END IF;
3843
3844 IF l_commit_flag = 'Y' THEN
3845 COMMIT;
3846 ELSE
3847 ROLLBACK;
3848 END IF;
3849
3850 /* -----------------------------------------------------------
3851 Update error_flag in interface tables
3852 -----------------------------------------------------------
3853 */
3854 IF p_return_status IN (FND_API.G_RET_STS_ERROR,
3855 FND_API.G_RET_STS_UNEXP_ERROR)
3856 THEN
3857 BEGIN
3858 BEGIN
3859 IF l_debug_level > 0 THEN
3860 oe_debug_pub.add( 'BEFORE UPDATING ERROR FLAG FOR HEADER' ) ;
3861 END IF;
3862
3863 UPDATE oe_headers_interface
3864 SET error_flag = 'Y'
3865 WHERE order_source_id = l_order_source_id
3866 AND orig_sys_document_ref = l_orig_sys_document_ref
3867 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
3868 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
3869 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
3870 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
3871 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
3872 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
3873 AND nvl( request_id, FND_API.G_MISS_NUM)
3874 = nvl(l_request_id, FND_API.G_MISS_NUM);
3875 EXCEPTION
3876 WHEN OTHERS THEN
3877 IF l_debug_level > 0 THEN
3878 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3879 END IF;
3880 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3881 l_return_status_upd_err := FND_API.G_RET_STS_UNEXP_ERROR;
3882 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
3883 END IF;
3884 END;
3885
3886 BEGIN
3887 FOR I in 1..l_header_adj_tbl.count
3888 LOOP
3889 IF l_debug_level > 0 THEN
3890 oe_debug_pub.add( 'BEFORE UPDATING ERROR FLAG FOR HEADER PRICE ADJUSTMENTS' ) ;
3891 END IF;
3892 IF l_header_adj_tbl(I).return_status IN (
3893 FND_API.G_RET_STS_ERROR,
3894 FND_API.G_RET_STS_UNEXP_ERROR)
3895 THEN
3896 BEGIN
3897 UPDATE oe_price_adjs_interface
3898 SET error_flag = 'Y'
3899 WHERE order_source_id = l_order_source_id
3900 AND orig_sys_document_ref = l_orig_sys_document_ref
3901 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
3902 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
3903 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
3904 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
3905 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
3906 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
3907 AND nvl( request_id, FND_API.G_MISS_NUM)
3908 = nvl(l_request_id, FND_API.G_MISS_NUM)
3909 AND nvl(orig_sys_discount_ref, FND_API.G_MISS_CHAR)
3910 = nvl(l_header_adj_tbl(I).orig_sys_discount_ref,
3911 FND_API.G_MISS_CHAR);
3912 EXCEPTION
3913 WHEN OTHERS THEN
3914 IF l_debug_level > 0 THEN
3915 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3916 END IF;
3917 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3918 THEN
3919 l_return_status_upd_err := FND_API.G_RET_STS_UNEXP_ERROR;
3920 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
3921 END IF;
3922 END;
3923 END IF;
3924 END LOOP;
3925 END;
3926
3927 BEGIN
3928 FOR I in 1..l_header_scredit_tbl.count
3929 LOOP
3930 IF l_debug_level > 0 THEN
3931 oe_debug_pub.add( 'BEFORE UPDATING ERROR FLAG FOR HEADER SALES CREDITS' ) ;
3932 END IF;
3933 IF l_header_scredit_tbl(I).return_status IN (
3934 FND_API.G_RET_STS_ERROR,
3935 FND_API.G_RET_STS_UNEXP_ERROR)
3936 THEN
3937 BEGIN
3938 UPDATE oe_credits_interface
3939 SET error_flag = 'Y'
3940 WHERE order_source_id = l_order_source_id
3941 AND orig_sys_document_ref = l_orig_sys_document_ref
3942 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
3943 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
3944 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
3945 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
3946 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
3947 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
3948 AND nvl( request_id, FND_API.G_MISS_NUM)
3949 = nvl(l_request_id, FND_API.G_MISS_NUM)
3950 AND nvl(orig_sys_credit_ref, FND_API.G_MISS_CHAR)
3951 = nvl(l_header_scredit_tbl(I).orig_sys_credit_ref,
3952 FND_API.G_MISS_CHAR);
3953 EXCEPTION
3954 WHEN OTHERS THEN
3955 IF l_debug_level > 0 THEN
3956 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3957 END IF;
3958 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3959 THEN
3960 l_return_status_upd_err := FND_API.G_RET_STS_UNEXP_ERROR;
3961 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
3962 END IF;
3963 END;
3964 END IF;
3965 END LOOP;
3966 END;
3967
3968 BEGIN
3969 FOR I in 1..l_line_tbl.count
3970 LOOP
3971 IF l_debug_level > 0 THEN
3972 oe_debug_pub.add( 'BEFORE UPDATING ERROR FLAG FOR LINES' ) ;
3973 END IF;
3974 IF l_line_tbl(I).return_status IN (
3975 FND_API.G_RET_STS_ERROR,
3976 FND_API.G_RET_STS_UNEXP_ERROR)
3977 THEN
3978 BEGIN
3979 UPDATE oe_lines_interface
3980 SET error_flag = 'Y'
3981 WHERE order_source_id = l_order_source_id
3982 AND orig_sys_document_ref = l_orig_sys_document_ref
3983 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
3984 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
3985 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
3986 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
3987 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
3988 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
3989 AND nvl( request_id, FND_API.G_MISS_NUM)
3990 = nvl(l_request_id, FND_API.G_MISS_NUM)
3991 AND nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
3992 = nvl(l_line_tbl(I).orig_sys_line_ref, FND_API.G_MISS_CHAR)
3993 AND nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
3994 = nvl(l_line_tbl(I).orig_sys_shipment_ref,FND_API.G_MISS_CHAR);
3995 EXCEPTION
3996 WHEN OTHERS THEN
3997 IF l_debug_level > 0 THEN
3998 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3999 END IF;
4000 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4001 THEN
4002 l_return_status_upd_err := FND_API.G_RET_STS_UNEXP_ERROR;
4003 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
4004 END IF;
4005 END;
4006 END IF;
4007 END LOOP;
4008 END;
4009
4010 /* -----------------------------------------------------------
4011 Set Return Status
4012 -----------------------------------------------------------
4013 */
4014 IF l_debug_level > 0 THEN
4015 oe_debug_pub.add( 'UPDATE ERROR_FLAG RETURN STATUS: '||L_RETURN_STATUS_UPD_ERR ) ;
4016 END IF;
4017
4018 IF l_return_status_upd_err IN (FND_API.G_RET_STS_ERROR)
4019 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR)
4020 THEN p_return_status := FND_API.G_RET_STS_ERROR;
4021 ELSIF l_return_status_upd_err IN (FND_API.G_RET_STS_UNEXP_ERROR)
4022 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR,
4023 FND_API.G_RET_STS_UNEXP_ERROR)
4024 THEN p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4025 END IF;
4026
4027 /* -----------------------------------------------------------
4028 Commit or rollback the error_flag
4029 -----------------------------------------------------------
4030 */
4031 IF l_return_status_upd_err NOT IN (FND_API.G_RET_STS_ERROR,
4032 FND_API.G_RET_STS_UNEXP_ERROR)
4033 THEN
4034 COMMIT; /* commit the error_flag updated */
4035 ELSE
4036 ROLLBACK; /* rollback the error_flag updated */
4037 END IF;
4038
4039 END;
4040 END IF; /* IF p_return_status IN ... */
4041
4042
4043 /* -----------------------------------------------------------
4044 Update the processing messages table
4045 -----------------------------------------------------------
4046 */
4047 OE_MSG_PUB.Count_And_Get (p_count => p_msg_count
4048 ,p_data => p_msg_data);
4049
4050 IF l_debug_level > 0 THEN
4051 oe_debug_pub.add( 'MESSAGES RETURNED: '|| TO_CHAR ( P_MSG_COUNT ) ) ;
4052 END IF;
4053
4054 IF p_msg_count > 0 THEN
4055
4056 IF l_debug_level > 0 THEN
4057 oe_debug_pub.add( 'BEFORE UPDATING THE PROCESSING MESSAGES TABLE' ) ;
4058 END IF;
4059
4060 FOR k IN 1 .. p_msg_count
4061 LOOP
4062 IF l_debug_level > 0 THEN
4063 oe_debug_pub.add( 'BEFORE CALLING GET' ) ;
4064 END IF;
4065 oe_msg_pub.get (
4066 p_msg_index => -2
4067 ,p_encoded => 'F'
4068 ,p_data => p_msg_data
4069 ,p_msg_index_out => l_msg_index);
4070
4071 l_msg_order_source_id := '';
4072 l_msg_orig_sys_document_ref := '';
4073 l_msg_orig_sys_line_ref := '';
4074 l_msg_orig_sys_shipment_ref := '';
4075 l_msg_sold_to_org_id := '';
4076 l_msg_sold_to_org := '';
4077 l_msg_change_sequence := '';
4078 l_msg_entity_code := '';
4079 l_msg_entity_ref := '';
4080
4081 begin
4082 IF l_debug_level > 0 THEN
4083 oe_debug_pub.add( 'BEFORE CALLING GET_MSG_CONTEXT' ) ;
4084 END IF;
4085 oe_msg_pub.get_msg_context (
4086 p_msg_index => l_msg_index
4087 ,x_entity_code => l_msg_entity_code
4088 ,x_entity_ref => l_msg_entity_ref
4089 ,x_entity_id => l_msg_entity_id
4090 ,x_header_id => l_msg_header_id
4091 ,x_line_id => l_msg_line_id
4092 ,x_order_source_id => l_msg_order_source_id
4093 ,x_orig_sys_document_ref => l_msg_orig_sys_document_ref
4094 ,x_orig_sys_line_ref => l_msg_orig_sys_line_ref
4095 ,x_orig_sys_shipment_ref => l_msg_orig_sys_shipment_ref
4096 ,x_change_sequence => l_msg_change_sequence
4097 ,x_source_document_type_id => l_msg_source_document_type_id
4098 ,x_source_document_id => l_msg_source_document_id
4099 ,x_source_document_line_id => l_msg_source_document_line_id
4100 ,x_attribute_code => l_msg_attribute_code
4101 ,x_constraint_id => l_msg_constraint_id
4102 ,x_process_activity => l_msg_process_activity
4103 ,x_notification_flag => l_msg_notification_flag
4104 ,x_type => l_msg_type
4105 );
4106
4107 exception
4108 when others then
4109 IF l_debug_level > 0 THEN
4110 oe_debug_pub.add( 'UNEXPECTED ERROR IN GET MSG : '||SQLERRM ) ;
4111 END IF;
4112 IF l_debug_level > 0 THEN
4113 oe_debug_pub.add( 'IGNORING ABOVE MESSAGE' ) ;
4114 END IF;
4115 l_error_index_flag := 'Y';
4116 end;
4117 -- bug 4195533 - changed the condition below from <> to =
4118 if l_error_index_flag = 'Y' then
4119 goto out_error;
4120 end if;
4121 IF l_debug_level > 0 THEN
4122 oe_debug_pub.add( 'AFTER CALLING GET_MSG_CONTEXT' ) ;
4123 END IF;
4124 IF oe_msg_pub.g_msg_tbl(l_msg_index).message_text IS NULL THEN
4125 IF l_debug_level > 0 THEN
4126 oe_debug_pub.add( 'IN INDEX.MESSAGE_TEXT IS NULL' ) ;
4127 END IF;
4128 p_msg_data := oe_msg_pub.get(l_msg_index, 'F');
4129 END IF;
4130
4131 l_msg_context := '';
4132 IF l_msg_order_source_id IS NOT NULL THEN
4133 l_msg_context := 'Src: ' || l_msg_order_source_id;
4134 END IF;
4135 IF l_msg_orig_sys_document_ref IS NOT NULL THEN
4136 l_msg_context := l_msg_context ||
4137 ', ' || 'Hdr: '||rtrim(l_msg_orig_sys_document_ref);
4138 END IF;
4139 IF l_msg_orig_sys_line_ref IS NOT NULL THEN
4140 l_msg_context := l_msg_context ||
4141 ', ' || 'Line: '||rtrim(l_msg_orig_sys_line_ref);
4142 END IF;
4143 IF l_msg_orig_sys_shipment_ref IS NOT NULL THEN
4144 l_msg_context := l_msg_context ||
4145 ', ' || 'Ship: '||rtrim(l_msg_orig_sys_shipment_ref);
4146 END IF;
4147 IF l_msg_sold_to_org_id IS NOT NULL THEN
4148 l_msg_context := l_msg_context ||
4149 ', ' || 'Customer ID: '|| l_msg_sold_to_org_id;
4150 END IF;
4151 IF l_msg_sold_to_org IS NOT NULL THEN
4152 l_msg_context := l_msg_context ||
4153 ', ' || 'Customer Name: '||rtrim(l_msg_sold_to_org);
4154 END IF;
4155 IF l_msg_change_sequence IS NOT NULL THEN
4156 l_msg_context := l_msg_context ||
4157 ', ' || 'Chg: '||rtrim(l_msg_change_sequence);
4158 END IF;
4159
4160 IF l_msg_entity_code IS NOT NULL AND
4161 l_msg_entity_ref IS NOT NULL
4162 THEN
4163 IF l_msg_entity_code IN ('HEADER_ADJ', 'LINE_ADJ') THEN
4164 l_msg_context := l_msg_context || ', ' || 'Adj: ';
4165 ELSIF l_msg_entity_code IN ('HEADER_SCREDIT', 'LINE_SCREDIT') THEN
4166 l_msg_context := l_msg_context || ', ' || 'SCredit: ';
4167 ELSIF l_msg_entity_code IN ('LOT_SERIAL') THEN
4168 l_msg_context := l_msg_context || ', ' || 'Lot: ';
4169 ELSIF l_msg_entity_code IN ('RESERVATION') THEN
4170 l_msg_context := l_msg_context || ', ' || 'Rsrvtn: ';
4171 END IF;
4172 l_msg_context := l_msg_context || rtrim(l_msg_entity_ref);
4173 END IF;
4174 l_msg_data := 'Msg-'||k||' for '||l_msg_context||': '||p_msg_data;
4175
4176 IF l_debug_level > 0 THEN
4177 oe_debug_pub.add( L_MSG_DATA ) ;
4178 END IF;
4179 -- start bug 4195533
4180 IF p_return_status = FND_API.G_RET_STS_SUCCESS
4181 AND l_header_rec.header_id <> FND_API.G_MISS_NUM THEN
4182 IF l_debug_level > 0 THEN
4183 oe_debug_pub.add( 'Header id updated in msg stack:' || l_header_rec.header_id ) ;
4184 END IF;
4185 oe_msg_pub.g_msg_tbl(l_msg_index).header_id := l_header_rec.header_id;
4186 END IF;
4187 -- end bug 4195533
4188 END LOOP;
4189 <<out_error>>
4190 null;
4191 END IF;
4192
4193 /* -----------------------------------------------------------
4194 Delete messages from the database table
4195 -----------------------------------------------------------
4196 */
4197 IF l_debug_level > 0 THEN
4198 oe_debug_pub.add( 'BEFORE DELETING OLD MESSAGES FROM THE DATABASE TABLE' ) ;
4199 END IF;
4200
4201 /*
4202 OE_ORDER_IMPORT_UTIL_PVT.Delete_Messages (
4203 p_request_id => l_request_id,
4204 p_order_source_id => l_order_source_id,
4205 p_orig_sys_document_ref => l_orig_sys_document_ref,
4206 p_change_sequence => l_change_sequence,
4207 p_return_status => l_return_status_del_msg
4208 );
4209 */
4210
4211
4212 /* -----------------------------------------------------------
4213 Set Return Status
4214 -----------------------------------------------------------
4215 */
4216 IF l_debug_level > 0 THEN
4217 oe_debug_pub.add( 'DELETE MESSAGES RETURN STATUS: '||L_RETURN_STATUS_DEL_MSG ) ;
4218 END IF;
4219
4220 IF l_return_status_del_msg IN (FND_API.G_RET_STS_ERROR)
4221 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR)
4222 THEN p_return_status := FND_API.G_RET_STS_ERROR;
4223 ELSIF l_return_status_del_msg IN (FND_API.G_RET_STS_UNEXP_ERROR)
4224 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR,
4225 FND_API.G_RET_STS_UNEXP_ERROR)
4226 THEN p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4227 END IF;
4228
4229
4230 /* -----------------------------------------------------------
4231 Save messages in the database table
4232 -----------------------------------------------------------
4233 */
4234 IF l_debug_level > 0 THEN
4235 oe_debug_pub.add( 'BEFORE SAVING MESSAGES IN THE DATABASE TABLE' ) ;
4236 END IF;
4237
4238 IF p_msg_count > 0 THEN
4239 OE_MSG_PUB.save_messages (l_request_id);
4240 END IF;
4241 COMMIT; /* commit again to commit the error messages */
4242
4243
4244 /* -----------------------------------------------------------
4245 Commit or rollback the messages
4246 -----------------------------------------------------------
4247 */
4248 IF l_return_status_del_msg = FND_API.G_RET_STS_SUCCESS
4249 -- AND l_return_status_sav_msg = FND_API.G_RET_STS_SUCCESS -- Currently not set
4250 THEN
4251 COMMIT; /* commit again to commit the error messages */
4252 ELSE
4253 ROLLBACK; /* rollback the error messages deleted */
4254 END IF;
4255
4256
4257 /* -----------------------------------------------------------
4258 Report final order processing results
4259 -----------------------------------------------------------
4260 */
4261 IF l_debug_level > 0 THEN
4262 oe_debug_pub.add( 'RETURN_STATUS: '||P_RETURN_STATUS ) ;
4263 END IF;
4264
4265 IF p_return_status = FND_API.G_RET_STS_ERROR THEN
4266 IF l_debug_level > 0 THEN
4267 oe_debug_pub.add( 'ORDER FAILED WITH ERROR ( S ) ' ) ;
4268 END IF;
4269 ELSIF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4270 IF l_debug_level > 0 THEN
4271 oe_debug_pub.add( 'ORDER FAILED WITH UNEXPECTED ERROR ( S ) ' ) ;
4272 END IF;
4273 ELSE
4274 IF l_debug_level > 0 THEN
4275 oe_debug_pub.add( 'ORDER PROCESSED SUCCESSFULLY' ) ;
4276 END IF;
4277 END IF;
4278
4279 --END LOOP; /* Headers cursor */
4280 --CLOSE l_header_cursor;
4281
4282
4283 /*-----------------------------------------------------------
4284 End of Order Import
4285 -----------------------------------------------------------
4286 */
4287 --oe_debug_pub.add('End of Order Import');
4288
4289
4290 EXCEPTION
4291 --
4292 WHEN OTHERS THEN
4293 IF l_debug_level > 0 THEN
4294 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
4295 END IF;
4296 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4297 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
4298 END IF;
4299 p_msg_count := p_msg_count + 1;
4300 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4301
4302 END IMPORT_ORDER;
4303
4304 END OE_CNCL_ORDER_IMPORT_PVT;