[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.3 2006/02/20 21:50:53 mbhoumik noship $ */
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)
288 , nvl(earliest_schedule_limit, 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)
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)
391 , nvl(global_attribute10, 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)
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)
493 , nvl(arithmetic_operator, FND_API.G_MISS_CHAR)
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)
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)
595 , nvl(attribute7, 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)
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)
702 , nvl(ship_to_address4, 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)
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)
807 , nvl(attribute12, 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)
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)
909 , nvl(return_attribute13, 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)
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)
1015 , nvl(percent, FND_API.G_MISS_NUM)
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)
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)
1117 , nvl(attribute3, FND_API.G_MISS_CHAR)
1114 , nvl(context, FND_API.G_MISS_CHAR)
1115 , nvl(attribute1, FND_API.G_MISS_CHAR)
1116 , nvl(attribute2, 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 BEGIN
1212
1213 /* -----------------------------------------------------------
1214 Initialize messages
1215 -----------------------------------------------------------
1216 */
1217 IF p_init_msg_list = FND_API.G_TRUE THEN
1218 IF l_debug_level > 0 THEN
1219 oe_debug_pub.add( 'BEFORE INITIALIZING MESSAGES LIST' ) ;
1220 END IF;
1221 OE_MSG_PUB.Initialize;
1222 END IF;
1223
1224 /* -----------------------------------------------------------
1225 Set message context
1226 -----------------------------------------------------------
1227 */
1228 IF l_debug_level > 0 THEN
1229 oe_debug_pub.add( 'BEFORE SETTING MESSAGE CONTEXT' ) ;
1230 END IF;
1231
1232 OE_MSG_PUB.set_msg_context(
1233 p_entity_code => 'HEADER'
1234 ,p_entity_ref => null
1235 ,p_entity_id => null
1236 ,p_header_id => null
1237 ,p_line_id => null
1238 ,p_order_source_id => l_order_source_id
1239 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1240 ,p_change_sequence => l_change_sequence
1241 ,p_orig_sys_document_line_ref => null
1242 ,p_orig_sys_shipment_ref => null
1243 ,p_source_document_type_id => null
1244 ,p_source_document_id => null
1245 ,p_source_document_line_id => null
1249
1246 ,p_attribute_code => null
1247 ,p_constraint_id => null
1248 );
1250 /* -----------------------------------------------------------
1251 Initialization
1252 -----------------------------------------------------------
1253 */
1254 IF l_debug_level > 0 THEN
1255 oe_debug_pub.add( 'BEFORE INITIALIZATION' ) ;
1256 END IF;
1257 begin
1258 select DECODE(p_validate_only,'Y','T','N','F',p_validate_only)
1259 into l_validate_only
1260 from dual;
1261 exception
1262 when others then
1263 l_validate_only := 'T';
1264 end;
1265 l_header_rec := OE_Order_Pub.G_MISS_HEADER_REC;
1266 l_header_rec_old := OE_Order_Pub.G_MISS_HEADER_REC;
1267 l_header_rec_new := OE_Order_Pub.G_MISS_HEADER_REC;
1268 l_header_adj_rec := OE_Order_Pub.G_MISS_HEADER_ADJ_REC;
1269 l_header_adj_tbl := OE_Order_Pub.G_MISS_HEADER_ADJ_TBL;
1270 l_header_adj_tbl_old := OE_Order_Pub.G_MISS_HEADER_ADJ_TBL;
1271 l_header_adj_tbl_new := OE_Order_Pub.G_MISS_HEADER_ADJ_TBL;
1272 l_header_scredit_rec := OE_Order_Pub.G_MISS_HEADER_SCREDIT_REC;
1273
1274 l_header_scredit_tbl := OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL;
1275 l_header_scredit_tbl_old := OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL;
1276 l_header_scredit_tbl_new := OE_Order_Pub.G_MISS_HEADER_SCREDIT_TBL;
1277
1278 l_header_val_rec := OE_Order_Pub.G_MISS_HEADER_VAL_REC;
1279 l_header_val_rec_old := OE_Order_Pub.G_MISS_HEADER_VAL_REC;
1280 l_header_val_rec_new := OE_Order_Pub.G_MISS_HEADER_VAL_REC;
1281 l_header_adj_val_rec := OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_REC;
1282 l_header_adj_val_tbl := OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL;
1283 l_header_adj_val_tbl_old := OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL;
1284 l_header_adj_val_tbl_new := OE_Order_Pub.G_MISS_HEADER_ADJ_VAL_TBL;
1285 l_header_scredit_val_rec := OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_REC;
1286 l_header_scredit_val_tbl := OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL;
1287 l_header_scredit_val_tbl_old := OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL;
1288 l_header_scredit_val_tbl_new := OE_Order_Pub.G_MISS_HEADER_SCREDIT_VAL_TBL;
1289
1290 l_line_rec := OE_Order_Pub.G_MISS_LINE_REC;
1291 l_line_tbl := OE_Order_Pub.G_MISS_LINE_TBL;
1292 l_line_tbl_old := OE_Order_Pub.G_MISS_LINE_TBL;
1293 l_line_tbl_new := OE_Order_Pub.G_MISS_LINE_TBL;
1294 l_line_adj_rec := OE_Order_Pub.G_MISS_LINE_ADJ_REC;
1295 l_line_adj_tbl := OE_Order_Pub.G_MISS_LINE_ADJ_TBL;
1296 l_line_adj_tbl_old := OE_Order_Pub.G_MISS_LINE_ADJ_TBL;
1297 l_line_adj_tbl_new := OE_Order_Pub.G_MISS_LINE_ADJ_TBL;
1298 l_line_scredit_rec := OE_Order_Pub.G_MISS_LINE_SCREDIT_REC;
1299 l_line_scredit_tbl := OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL;
1300 l_line_scredit_tbl_old := OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL;
1301 l_line_scredit_tbl_new := OE_Order_Pub.G_MISS_LINE_SCREDIT_TBL;
1302 l_reservation_rec := OE_Order_Pub.G_MISS_RESERVATION_REC;
1303 l_reservation_tbl := OE_Order_Pub.G_MISS_RESERVATION_TBL;
1304
1305 l_line_val_rec := OE_Order_Pub.G_MISS_LINE_VAL_REC;
1306 l_line_val_tbl := OE_Order_Pub.G_MISS_LINE_VAL_TBL;
1307 l_line_val_tbl_old := OE_Order_Pub.G_MISS_LINE_VAL_TBL;
1308 l_line_val_tbl_new := OE_Order_Pub.G_MISS_LINE_VAL_TBL;
1309 l_line_adj_val_rec := OE_Order_Pub.G_MISS_LINE_ADJ_VAL_REC;
1310 l_line_adj_val_tbl := OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL;
1311 l_line_adj_val_tbl_old := OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL;
1312 l_line_adj_val_tbl_new := OE_Order_Pub.G_MISS_LINE_ADJ_VAL_TBL;
1313 l_line_scredit_val_rec := OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_REC;
1314 l_line_scredit_val_tbl := OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL;
1315 l_line_scredit_val_tbl_old := OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL;
1316 l_line_scredit_val_tbl_new := OE_Order_Pub.G_MISS_LINE_SCREDIT_VAL_TBL;
1317 l_reservation_val_rec := OE_Order_Pub.G_MISS_RESERVATION_VAL_REC;
1318 l_reservation_val_tbl := OE_Order_Pub.G_MISS_RESERVATION_VAL_TBL;
1319
1320 l_lot_serial_rec := OE_Order_Pub.G_MISS_LOT_SERIAL_REC;
1321 l_lot_serial_tbl := OE_Order_Pub.G_MISS_LOT_SERIAL_TBL;
1322 l_lot_serial_tbl_old := OE_Order_Pub.G_MISS_LOT_SERIAL_TBL;
1323 l_lot_serial_tbl_new := OE_Order_Pub.G_MISS_LOT_SERIAL_TBL;
1324
1325 l_action_request_rec := OE_Order_Pub.G_MISS_REQUEST_REC;
1326 l_action_request_tbl := OE_Order_Pub.G_MISS_REQUEST_TBL;
1327 l_action_request_tbl_old := OE_Order_Pub.G_MISS_REQUEST_TBL;
1328 l_action_request_tbl_new := OE_Order_Pub.G_MISS_REQUEST_TBL;
1329
1330 p_return_status := FND_API.G_RET_STS_SUCCESS; -- Success
1331
1332
1333
1334
1335 /* -----------------------------------------------------------
1336 Headers
1337 -----------------------------------------------------------
1338 */
1339 IF l_debug_level > 0 THEN
1340 oe_debug_pub.add( 'BEFORE HEADERS LOOP' ) ;
1341 END IF;
1342
1343 l_header_count := 0;
1344
1345 OPEN l_header_cursor;
1346 --LOOP
1347 BEGIN
1348 FETCH l_header_cursor
1349 INTO l_header_rec.order_source_id
1350 , l_header_rec.orig_sys_document_ref
1351 , l_header_rec.change_sequence
1352 , l_header_rec.change_request_code
1353 , l_header_val_rec.order_source
1354 , l_header_rec.org_id
1355 , l_header_rec.header_id
1356 , l_header_rec.order_number
1357 , l_header_rec.version_number
1358 , l_header_rec.ordered_date
1359 , l_header_rec.order_type_id
1363 , l_header_rec.conversion_rate
1360 , l_header_val_rec.order_type
1361 , l_header_rec.price_list_id
1362 , l_header_val_rec.price_list
1364 , l_header_rec.conversion_rate_date
1365 , l_header_rec.conversion_type_code
1366 , l_header_val_rec.conversion_type
1367 , l_header_rec.transactional_curr_code
1368 , l_header_rec.return_reason_code
1369 , l_header_rec.salesrep_id
1370 , l_header_val_rec.salesrep
1371 , l_header_rec.sales_channel_code
1372 , l_header_val_rec.sales_channel
1373 , l_header_rec.tax_point_code
1374 , l_header_val_rec.tax_point
1375 , l_header_rec.tax_exempt_flag
1376 , l_header_rec.tax_exempt_number
1377 , l_header_rec.tax_exempt_reason_code
1378 , l_header_val_rec.tax_exempt_reason
1379 , l_header_rec.agreement_id
1380 , l_header_val_rec.agreement
1381 , l_header_rec.invoicing_rule_id
1382 , l_header_val_rec.invoicing_rule
1383 , l_header_rec.accounting_rule_id
1384 , l_header_val_rec.accounting_rule
1385 , l_header_rec.payment_term_id
1386 , l_header_val_rec.payment_term
1387 , l_header_rec.demand_class_code
1388 , l_header_rec.shipment_priority_code
1389 , l_header_val_rec.shipment_priority
1390 , l_header_rec.shipping_method_code
1391 , l_header_rec.freight_carrier_code
1392 , l_header_rec.freight_terms_code
1393 , l_header_val_rec.freight_terms
1394 , l_header_rec.fob_point_code
1395 , l_header_val_rec.fob_point
1396 , l_header_rec.partial_shipments_allowed
1397 , l_header_rec.ship_tolerance_above
1398 , l_header_rec.ship_tolerance_below
1399 , l_header_rec.shipping_instructions
1400 , l_header_rec.packing_instructions
1401 , l_header_rec.order_date_type_code
1402 , l_header_rec.earliest_schedule_limit
1403 , l_header_rec.latest_schedule_limit
1404 , l_header_rec.cust_po_number
1405 , l_header_rec.customer_payment_term_id
1406 , l_header_val_rec.customer_payment_term
1407 , l_header_rec.payment_type_code
1408 , l_header_rec.payment_amount
1409 , l_header_rec.check_number
1410 , l_header_rec.credit_card_code
1411 , l_header_rec.credit_card_holder_name
1412 , l_header_rec.credit_card_number
1413 , l_header_rec.credit_card_expiration_date
1414 , l_header_rec.credit_card_approval_code
1415 , l_header_rec.credit_card_approval_date
1416 , l_header_rec.sold_from_org_id
1417 , l_header_val_rec.sold_from_org
1418 , l_header_rec.sold_to_org_id
1419 , l_header_val_rec.sold_to_org
1420 , l_header_val_rec.customer_number
1421 , l_header_rec.ship_from_org_id
1422 , l_header_val_rec.ship_from_org
1423 , l_header_rec.ship_to_org_id
1424 , l_header_val_rec.ship_to_org
1425 , l_header_rec.invoice_to_org_id
1426 , l_header_val_rec.invoice_to_org
1427 , l_header_rec.deliver_to_org_id
1428 , l_header_val_rec.deliver_to_org
1429 , l_header_rec.sold_to_contact_id
1430 , l_header_val_rec.sold_to_contact
1431 , l_header_rec.ship_to_contact_id
1432 , l_header_val_rec.ship_to_contact
1433 , l_header_rec.invoice_to_contact_id
1434 , l_header_val_rec.invoice_to_contact
1435 , l_header_rec.deliver_to_contact_id
1436 , l_header_val_rec.deliver_to_contact
1437 , l_header_val_rec.ship_to_address1
1438 , l_header_val_rec.ship_to_address2
1439 , l_header_val_rec.ship_to_address3
1440 , l_header_val_rec.ship_to_address4
1441 , l_header_val_rec.ship_to_city
1442 , l_header_val_rec.ship_to_state
1443 , l_header_val_rec.ship_to_zip
1444 , l_header_val_rec.ship_to_country
1445 , l_header_val_rec.invoice_to_address1
1446 , l_header_val_rec.invoice_to_address2
1447 , l_header_val_rec.invoice_to_address3
1448 , l_header_val_rec.invoice_to_address4
1449 , l_header_val_rec.invoice_to_city
1450 , l_header_val_rec.invoice_to_state
1451 , l_header_val_rec.invoice_to_zip
1452 , l_header_val_rec.invoice_to_country
1453 , l_header_rec.drop_ship_flag
1454 , l_header_rec.booked_flag
1455 -- , l_header_rec.closed_flag
1456 , l_header_rec.cancelled_flag
1457 , l_header_rec.context
1458 , l_header_rec.attribute1
1459 , l_header_rec.attribute2
1460 , l_header_rec.attribute3
1461 , l_header_rec.attribute4
1462 , l_header_rec.attribute5
1463 , l_header_rec.attribute6
1464 , l_header_rec.attribute7
1465 , l_header_rec.attribute8
1466 , l_header_rec.attribute9
1467 , l_header_rec.attribute10
1468 , l_header_rec.attribute11
1469 , l_header_rec.attribute12
1470 , l_header_rec.attribute13
1471 , l_header_rec.attribute14
1472 , l_header_rec.attribute15
1473 /* Added attribute 16 to 20 for the bug 3471009 */
1474 , l_header_rec.attribute16
1475 , l_header_rec.attribute17
1476 , l_header_rec.attribute18
1477 , l_header_rec.attribute19
1478 , l_header_rec.attribute20
1479 , l_header_rec.tp_context
1480 , l_header_rec.tp_attribute1
1481 , l_header_rec.tp_attribute2
1482 , l_header_rec.tp_attribute3
1483 , l_header_rec.tp_attribute4
1484 , l_header_rec.tp_attribute5
1485 , l_header_rec.tp_attribute6
1486 , l_header_rec.tp_attribute7
1487 , l_header_rec.tp_attribute8
1488 , l_header_rec.tp_attribute9
1489 , l_header_rec.tp_attribute10
1490 , l_header_rec.tp_attribute11
1491 , l_header_rec.tp_attribute12
1492 , l_header_rec.tp_attribute13
1493 , l_header_rec.tp_attribute14
1497 , l_header_rec.global_attribute2
1494 , l_header_rec.tp_attribute15
1495 , l_header_rec.global_attribute_category
1496 , l_header_rec.global_attribute1
1498 , l_header_rec.global_attribute3
1499 , l_header_rec.global_attribute4
1500 , l_header_rec.global_attribute5
1501 , l_header_rec.global_attribute6
1502 , l_header_rec.global_attribute7
1503 , l_header_rec.global_attribute8
1504 , l_header_rec.global_attribute9
1505 , l_header_rec.global_attribute10
1506 , l_header_rec.global_attribute11
1507 , l_header_rec.global_attribute12
1508 , l_header_rec.global_attribute13
1509 , l_header_rec.global_attribute14
1510 , l_header_rec.global_attribute15
1511 , l_header_rec.global_attribute16
1512 , l_header_rec.global_attribute17
1513 , l_header_rec.global_attribute18
1514 , l_header_rec.global_attribute19
1515 , l_header_rec.global_attribute20
1516 , l_header_rec.request_id
1517 , l_header_rec.request_date
1518 , l_header_rec.operation
1519 , l_header_rec.ready_flag
1520 , l_header_rec.status_flag
1521 , l_header_rec.force_apply_flag
1522 , l_header_rec.change_reason
1523 , l_header_rec.change_comments
1524 , l_header_rec.open_flag
1525 , l_header_rec.customer_preference_set_code
1526 , l_header_rec.sold_to_site_use_id
1527 , l_header_val_rec.sold_to_location_address1
1528 , l_header_val_rec.sold_to_location_address2
1529 , l_header_val_rec.sold_to_location_address3
1530 , l_header_val_rec.sold_to_location_address4
1531 , l_header_val_rec.sold_to_location_city
1532 , l_header_val_rec.sold_to_location_postal
1533 , l_header_val_rec.sold_to_location_country
1534 , l_header_val_rec.sold_to_location_state
1535 , l_header_val_rec.sold_to_location_county
1536 , l_header_val_rec.sold_to_location_province
1537 -- start of additional quoting columns
1538 , l_header_rec.transaction_phase_code
1539 , l_header_rec.expiration_date
1540 , l_header_rec.quote_number
1541 , l_header_rec.quote_date
1542 , l_header_rec.sales_document_name
1543 , l_header_rec.user_status_code
1544 -- end of additional quoting columns
1545 -- { Distributer Order related change
1546 , l_header_rec.end_customer_id
1547 , l_header_rec.end_customer_contact_id
1548 , l_header_rec.end_customer_site_use_id
1549 --{added for bug 4240715
1550 ,l_header_val_rec.end_customer_name
1551 , l_header_val_rec.end_customer_site_address1
1552 , l_header_val_rec.end_customer_site_address2
1553 , l_header_val_rec.end_customer_site_address3
1554 , l_header_val_rec.end_customer_site_address4
1555 -- , l_header_val_rec.end_customer_site_location
1556 , l_header_val_rec.end_customer_site_city
1557 , l_header_val_rec.end_customer_site_state
1558 , l_header_val_rec.end_customer_site_postal_code
1559 , l_header_val_rec.end_customer_site_country
1560 , l_header_val_rec.end_customer_contact
1561 , l_header_val_rec.end_customer_number
1562 --bug 4240715}
1563 , l_header_rec.ib_owner
1564 , l_header_rec.ib_current_location
1565 , l_header_rec.ib_installed_at_location
1566 , l_header_val_rec.ib_owner_dsp
1567 , l_header_val_rec.ib_current_location_dsp
1568 , l_header_val_rec.ib_installed_at_location_dsp
1569 -- Distributer Order related change }
1570 ;
1571 --EXIT WHEN l_header_cursor%NOTFOUND;
1572
1573 IF l_debug_level > 0 THEN
1574 oe_debug_pub.add( 'AFTER FETCH HEADER CURSOR' ) ;
1575 END IF;
1576
1577 l_header_count := l_header_count + 1;
1578
1579 l_order_source_id := l_header_rec.order_source_id;
1580 l_orig_sys_document_ref := l_header_rec.orig_sys_document_ref;
1581 l_sold_to_org_id := l_header_rec.sold_to_org_id;
1582 l_sold_to_org := l_header_val_rec.sold_to_org;
1583 l_change_sequence := l_header_rec.change_sequence;
1584
1585 /*
1586 IF l_header_rec.operation = 'INSERT' THEN
1587 l_header_rec.operation := 'CREATE';
1588 END IF;
1589 */
1590
1591
1592 oe_debug_pub.add('Order Source Id: ' || l_order_source_id);
1593 oe_debug_pub.add('Orig Sys Reference: '|| l_orig_sys_document_ref);
1594 oe_debug_pub.add('Sold to Org Id: ' || l_sold_to_org_id);
1595 oe_debug_pub.add('Sold to Org: ' || l_sold_to_org);
1596 oe_debug_pub.add('Change Sequence: ' || l_change_sequence);
1597
1598
1599 --Default unpopulated transaction_phase_code to 'F' for bug 3576009
1600 IF l_header_rec.transaction_phase_code = FND_API.G_MISS_CHAR THEN
1601 l_header_rec.transaction_phase_code := 'F';
1602 END IF;
1603
1604
1605 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' THEN
1606
1607
1608 /***** If l_sold_to_org_id is null attempt to populate it based on
1609 l_sold_to_org.
1610
1611 ******/
1612 if l_sold_to_org_id = FND_API.G_MISS_NUM then
1613 IF l_debug_level > 0 THEN
1614 oe_debug_pub.add( 'header level sold to org id is g_miss_num, so it was not populated' ) ;
1615 END IF;
1616
1617 if l_sold_to_org is not null then
1618
1619 l_header_rec.sold_to_org_id := OE_VALUE_TO_ID.sold_to_org(
1620 p_sold_to_org => l_sold_to_org,
1621 p_customer_number => null);
1625
1622
1623
1624 /* if oe_value_to_id returned g_miss_num, reassign sold_to_org_id back to null */
1626 if l_header_rec.sold_to_org_id = FND_API.G_MISS_NUM then
1627 IF l_debug_level > 0 THEN
1628 oe_debug_pub.add( 'returned value for sold_to_org_id was g_miss_num') ;
1629 END IF;
1630 l_header_rec.sold_to_org_id := null;
1631 end if;
1632
1633 else
1634 IF l_debug_level > 0 THEN
1635 oe_debug_pub.add( 'sold to org is NULL') ;
1636 END IF;
1637 end if;
1638
1639 end if;
1640
1641 END IF; -- code control check
1642
1643 BEGIN
1644 --call value to id
1645 --
1646 OE_CNCL_UTIL.get_header_ids(l_header_rec,l_header_val_rec);
1647 --
1648 IF l_header_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1649 --
1650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1651 --
1652 ELSIF l_header_rec.return_status = FND_API.G_RET_STS_ERROR THEN
1653 --
1654 RAISE FND_API.G_EXC_ERROR;
1655 --
1656 END IF;
1657 --
1658 --
1659 OE_CNCL_Util.Convert_Miss_To_Null(l_header_rec);
1660 --
1661
1662 OE_CNCL_validate_header.attributes(x_return_status =>l_return_status
1663 , p_x_header_rec =>l_header_rec);
1664 --, p_validation_level =>FND_API.G_VALID_LEVEL_NONE
1665 --
1666 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1667 --
1668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1669 --
1670 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1671 --
1672 RAISE FND_API.G_EXC_ERROR;
1673 --
1674 END IF;
1675 --
1676 --
1677 OE_CNCL_validate_header.entity(x_return_status =>l_return_status
1678 , p_header_rec =>l_header_rec);
1679 --
1680 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1681 --
1682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1683 --
1684 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1685 --
1686 RAISE FND_API.G_EXC_ERROR;
1687 --
1688 END IF;
1689 --
1690 EXCEPTION
1691 --
1692 WHEN FND_API.G_EXC_ERROR THEN
1693 --
1694 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1695 OE_MSG_PUB.reset_msg_context('HEADER');
1696 --
1697 RAISE;
1698 --
1699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1700 --
1701 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1702 OE_MSG_PUB.reset_msg_context('HEADER');
1703 --
1704 RAISE;
1705 --
1706 WHEN OTHERS THEN
1707 --
1708 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1709 --
1710 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Header');
1711 --
1712 END IF;
1713 --
1714 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1715 OE_MSG_PUB.reset_msg_context('HEADER');
1716 --
1717 RAISE;
1718 --
1719 END;
1720
1721
1722 /* -----------------------------------------------------------
1723 Header Discounts/Price adjustments
1724 -----------------------------------------------------------
1725 */
1726 IF l_debug_level > 0 THEN
1727 oe_debug_pub.add( 'BEFORE HEADER ADJUSTMENTS LOOP' ) ;
1728 END IF;
1729
1730 l_header_adj_count := 0;
1731
1732 OPEN l_header_adj_cursor;
1733 LOOP
1734 FETCH l_header_adj_cursor
1735 INTO l_header_adj_rec.orig_sys_discount_ref
1736 , l_header_adj_rec.change_request_code
1737 , l_header_adj_rec.list_header_id -- changed from discount_id
1738 , l_header_adj_rec.list_line_id -- changed from discount_line_id
1739 , l_header_adj_val_rec.discount
1740 , l_header_adj_rec.percent
1741 , l_header_adj_rec.automatic_flag
1742 , l_header_adj_rec.applied_flag
1743 , l_header_adj_rec.operand
1744 , l_header_adj_rec.arithmetic_operator
1745 , l_header_adj_rec.context
1746 , l_header_adj_rec.attribute1
1747 , l_header_adj_rec.attribute2
1748 , l_header_adj_rec.attribute3
1749 , l_header_adj_rec.attribute4
1750 , l_header_adj_rec.attribute5
1751 , l_header_adj_rec.attribute6
1752 , l_header_adj_rec.attribute7
1753 , l_header_adj_rec.attribute8
1754 , l_header_adj_rec.attribute9
1755 , l_header_adj_rec.attribute10
1756 , l_header_adj_rec.attribute11
1757 , l_header_adj_rec.attribute12
1758 , l_header_adj_rec.attribute13
1759 , l_header_adj_rec.attribute14
1760 , l_header_adj_rec.attribute15
1761 , l_header_adj_rec.request_id
1762 , l_header_adj_rec.operation
1763 , l_header_adj_rec.status_flag
1764 -- Price Adjustment related changes bug# 1220921 (Start)
1765 , l_header_adj_rec.AC_CONTEXT
1766 , l_header_adj_rec.AC_ATTRIBUTE1
1770 , l_header_adj_rec.AC_ATTRIBUTE5
1767 , l_header_adj_rec.AC_ATTRIBUTE2
1768 , l_header_adj_rec.AC_ATTRIBUTE3
1769 , l_header_adj_rec.AC_ATTRIBUTE4
1771 , l_header_adj_rec.AC_ATTRIBUTE6
1772 , l_header_adj_rec.AC_ATTRIBUTE7
1773 , l_header_adj_rec.AC_ATTRIBUTE8
1774 , l_header_adj_rec.AC_ATTRIBUTE9
1775 , l_header_adj_rec.AC_ATTRIBUTE10
1776 , l_header_adj_rec.AC_ATTRIBUTE11
1777 , l_header_adj_rec.AC_ATTRIBUTE12
1778 , l_header_adj_rec.AC_ATTRIBUTE13
1779 , l_header_adj_rec.AC_ATTRIBUTE14
1780 , l_header_adj_rec.AC_ATTRIBUTE15
1781 , l_header_adj_val_rec.LIST_NAME
1782 , l_header_adj_rec.LIST_LINE_TYPE_CODE
1783 , l_header_adj_rec.LIST_LINE_NO
1784 , l_header_adj_val_rec.VERSION_NO
1785 , l_header_adj_rec.INVOICED_FLAG
1786 , l_header_adj_rec.ESTIMATED_FLAG
1787 , l_header_adj_rec.INC_IN_SALES_PERFORMANCE
1788 , l_header_adj_rec.CHARGE_TYPE_CODE
1789 , l_header_adj_rec.CHARGE_SUBTYPE_CODE
1790 , l_header_adj_rec.CREDIT_OR_CHARGE_FLAG
1791 , l_header_adj_rec.INCLUDE_ON_RETURNS_FLAG
1792 , l_header_adj_rec.COST_ID
1793 , l_header_adj_rec.TAX_CODE
1794 , l_header_adj_rec.PARENT_ADJUSTMENT_ID
1795 , l_header_adj_rec.MODIFIER_MECHANISM_TYPE_CODE
1796 , l_header_adj_rec.MODIFIED_FROM
1797 , l_header_adj_rec.MODIFIED_TO
1798 , l_header_adj_rec.UPDATED_FLAG
1799 , l_header_adj_rec.UPDATE_ALLOWED
1800 , l_header_adj_rec.CHANGE_REASON_CODE
1801 , l_header_adj_rec.CHANGE_REASON_TEXT
1802 , l_header_adj_rec.PRICING_PHASE_ID
1803 , l_header_adj_rec.ADJUSTED_AMOUNT
1804 -- Price Adjustment related changes bug# 1220921 (End)
1805 ;
1806 EXIT WHEN l_header_adj_cursor%NOTFOUND;
1807
1808 /*
1809 IF l_header_adj_rec.operation = 'INSERT' THEN
1810 l_header_adj_rec.operation := 'CREATE';
1811 END IF;
1812 */
1813
1814 l_header_adj_count := l_header_adj_count + 1;
1815 l_header_adj_tbl (l_header_adj_count) := l_header_adj_rec;
1816 l_header_adj_val_tbl (l_header_adj_count) := l_header_adj_val_rec;
1817
1818 IF l_debug_level > 0 THEN
1819 oe_debug_pub.add( 'HEADER ADJ REF ( '||L_HEADER_ADJ_COUNT||' ) : '|| L_HEADER_ADJ_TBL ( L_HEADER_ADJ_COUNT ) .ORIG_SYS_DISCOUNT_REF ) ;
1820 END IF;
1821
1822
1823 BEGIN
1824 --
1825 OE_CNCL_UTIL.get_header_adj_ids(l_header_adj_rec,l_header_adj_val_rec);
1826 --
1827 IF l_header_adj_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1828 --
1829 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1830 --
1831 ELSIF l_header_adj_rec.return_status = FND_API.G_RET_STS_ERROR THEN
1832 --
1833 RAISE FND_API.G_EXC_ERROR;
1834 --
1835 END IF;
1836 --
1837 --
1838 OE_CNCL_Util.Convert_Miss_To_Null(l_header_adj_rec);
1839 --
1840
1841 OE_CNCL_Validate_Header_Adj.Attributes( x_return_status =>l_return_status
1842 , p_Header_Adj_rec =>l_header_adj_rec);
1843 --
1844 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1845 --
1846 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1847 --
1848 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1849 --
1850 RAISE FND_API.G_EXC_ERROR;
1851 --
1852 END IF;
1853 --
1854 OE_CNCL_Validate_Header_Adj.Entity( x_return_status =>l_return_status
1855 , p_Header_Adj_rec =>l_header_adj_rec);
1856 --
1857 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1858 --
1859 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1860 --
1861 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1862 --
1863 RAISE FND_API.G_EXC_ERROR;
1864 --
1865 END IF;
1866 --
1867
1868 EXCEPTION
1869 --
1870 WHEN FND_API.G_EXC_ERROR THEN
1871 --
1872 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1873 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
1874 --
1875 RAISE;
1876 --
1877 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1878 --
1879 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1880 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
1881 --
1882 RAISE;
1883 --
1884 WHEN OTHERS THEN
1885 --
1886 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1887 --
1888 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Header_Adjs');
1889 --
1890 END IF;
1891 --
1892 OE_Header_Security.g_check_all_cols_constraint := 'Y';
1893 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
1894 --
1895 RAISE;
1896 --
1897 END;
1898
1899 END LOOP;
1900 CLOSE l_header_adj_cursor;
1901
1902
1903
1904
1905
1906 /* -----------------------------------------------------------
1910 IF l_debug_level > 0 THEN
1907 Header Sales Credits
1908 -----------------------------------------------------------
1909 */
1911 oe_debug_pub.add( 'BEFORE HEADER SALES CREDITS LOOP' ) ;
1912 END IF;
1913
1914 l_header_scredit_count := 0;
1915
1916 OPEN l_header_scredit_cursor;
1917 LOOP
1918 FETCH l_header_scredit_cursor
1919 INTO l_header_scredit_rec.orig_sys_credit_ref
1920 , l_header_scredit_rec.change_request_code
1921 , l_header_scredit_rec.salesrep_id
1922 , l_header_scredit_val_rec.salesrep
1923 , l_header_scredit_rec.sales_credit_type_id
1924 , l_header_scredit_val_rec.sales_credit_type
1925 , l_header_scredit_rec.percent
1926 , l_header_scredit_rec.context
1927 , l_header_scredit_rec.attribute1
1928 , l_header_scredit_rec.attribute2
1929 , l_header_scredit_rec.attribute3
1930 , l_header_scredit_rec.attribute4
1931 , l_header_scredit_rec.attribute5
1932 , l_header_scredit_rec.attribute6
1933 , l_header_scredit_rec.attribute7
1934 , l_header_scredit_rec.attribute8
1935 , l_header_scredit_rec.attribute9
1936 , l_header_scredit_rec.attribute10
1937 , l_header_scredit_rec.attribute11
1938 , l_header_scredit_rec.attribute12
1939 , l_header_scredit_rec.attribute13
1940 , l_header_scredit_rec.attribute14
1941 , l_header_scredit_rec.attribute15
1942 , l_header_scredit_rec.operation
1943 , l_header_scredit_rec.status_flag
1944 ;
1945 EXIT WHEN l_header_scredit_cursor%NOTFOUND;
1946
1947 /*
1948 IF l_header_scredit_rec.operation = 'INSERT' THEN
1949 l_header_scredit_rec.operation := 'CREATE';
1950 END IF;
1951 */
1952
1953 l_header_scredit_count := l_header_scredit_count + 1;
1954 l_header_scredit_tbl (l_header_scredit_count) := l_header_scredit_rec;
1955 l_header_scredit_val_tbl (l_header_scredit_count) := l_header_scredit_val_rec;
1956
1957 IF l_debug_level > 0 THEN
1958 oe_debug_pub.add( 'HEADER SALESCREDIT ( '|| L_HEADER_SCREDIT_COUNT||' ) : '|| L_HEADER_SCREDIT_TBL ( L_HEADER_SCREDIT_COUNT ) .ORIG_SYS_CREDIT_REF ) ;
1959 END IF;
1960
1961 BEGIN
1962 --
1963 OE_CNCL_UTIL.get_header_scredit_ids(l_header_scredit_rec,l_header_scredit_val_rec);
1964 --
1965 IF l_header_scredit_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1966 --
1967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1968 --
1969 ELSIF l_header_scredit_rec.return_status = FND_API.G_RET_STS_ERROR THEN
1970 --
1971 RAISE FND_API.G_EXC_ERROR;
1972 --
1973 END IF;
1974 --
1975
1976 --
1977 OE_CNCL_Util.Convert_Miss_To_Null(l_header_scredit_rec);
1978 --
1979
1980 OE_CNCL_Val_Header_Scredit.Attributes (x_return_status => l_return_status
1981 , p_Header_Scredit_rec => l_header_scredit_rec);
1982 --
1983 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1984 --
1985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1986 --
1987 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1988 --
1989 RAISE FND_API.G_EXC_ERROR;
1990 --
1991 END IF;
1992 --
1993
1994 OE_CNCL_Val_Header_Scredit.Entity (x_return_status => l_return_status
1995 , p_Header_Scredit_rec => l_header_scredit_rec);
1996 --
1997 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1998 --
1999 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2000 --
2001 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2002 --
2003 RAISE FND_API.G_EXC_ERROR;
2004 --
2005 END IF;
2006 --
2007 EXCEPTION
2008 --
2009 WHEN FND_API.G_EXC_ERROR THEN
2010 --
2011 OE_Header_Security.g_check_all_cols_constraint := 'Y';
2012 OE_MSG_PUB.reset_msg_context('HEADER_SCREDIT');
2013 --
2014 RAISE;
2015 --
2016 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2017 --
2018 OE_Header_Security.g_check_all_cols_constraint := 'Y';
2019 OE_MSG_PUB.reset_msg_context('HEADER_SCREDIT');
2020 --
2021 RAISE;
2022 --
2023 WHEN OTHERS THEN
2024 --
2025 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2026 --
2027 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Header_Scredits');
2028 --
2029 END IF;
2030 --
2031 OE_Header_Security.g_check_all_cols_constraint := 'Y';
2032 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
2033 --
2034 RAISE;
2035 --
2036 END;
2037 --
2038 END LOOP;
2039 CLOSE l_header_scredit_cursor;
2040
2041
2042 /* -----------------------------------------------------------
2043 Lines
2044 -----------------------------------------------------------
2045 */
2046 IF l_debug_level > 0 THEN
2047 oe_debug_pub.add( 'BEFORE LINES LOOP' ) ;
2048 END IF;
2049
2050 l_line_count := 0;
2051
2055 INTO l_line_rec.order_source_id
2052 OPEN l_line_cursor;
2053 LOOP
2054 FETCH l_line_cursor
2056 , l_line_rec.orig_sys_document_ref
2057 , l_line_rec.orig_sys_line_ref
2058 , l_line_rec.orig_sys_shipment_ref
2059 , l_line_rec.change_request_code
2060 , l_line_rec.org_id
2061 , l_line_rec.line_number
2062 , l_line_rec.shipment_number
2063 , l_line_rec.line_id
2064 , l_line_rec.line_type_id
2065 , l_line_val_rec.line_type
2066 , l_line_rec.item_type_code
2067 , l_line_rec.inventory_item_id
2068 , l_line_val_rec.inventory_item
2069 , l_line_rec.top_model_line_ref
2070 , l_line_rec.link_to_line_ref
2071 , l_line_rec.explosion_date
2072 , l_line_rec.ato_line_id
2073 , l_line_rec.component_sequence_id
2074 , l_line_rec.component_code
2075 , l_line_rec.sort_order
2076 , l_line_rec.model_group_number
2077 , l_line_rec.option_number
2078 , l_line_rec.option_flag
2079 , l_line_rec.ship_model_complete_flag
2080 , l_line_rec.source_type_code
2081 , l_line_rec.schedule_status_code
2082 , l_line_rec.schedule_ship_date
2083 , l_line_rec.schedule_arrival_date
2084 , l_line_rec.actual_arrival_date
2085 -- bug 3220711 - start
2086 , l_line_rec.actual_shipment_date
2087 -- bug 3220711 - end
2088 , l_line_rec.request_date
2089 , l_line_rec.promise_date
2090 , l_line_rec.delivery_lead_time
2091 , l_line_rec.ordered_quantity
2092 , l_line_rec.order_quantity_uom
2093 , l_line_rec.shipping_quantity
2094 , l_line_rec.shipping_quantity_uom
2095 , l_line_rec.shipped_quantity
2096 , l_line_rec.cancelled_quantity
2097 , l_line_rec.fulfilled_quantity
2098 /* OPM variables */ -- INVCONV
2099 , l_line_rec.ordered_quantity2
2100 , l_line_rec.ordered_quantity_uom2
2101 , l_line_rec.shipping_quantity2
2102 , l_line_rec.shipping_quantity_uom2
2103 , l_line_rec.shipped_quantity2
2104 , l_line_rec.cancelled_quantity2
2105 , l_line_rec.fulfilled_quantity2
2106 , l_line_rec.preferred_grade
2107 , l_line_rec.pricing_quantity
2108 , l_line_rec.pricing_quantity_uom
2109 , l_line_rec.sold_from_org_id
2110 , l_line_val_rec.sold_from_org
2111 , l_line_rec.sold_to_org_id
2112 , l_line_val_rec.sold_to_org
2113 , l_line_rec.ship_from_org_id
2114 , l_line_val_rec.ship_from_org
2115 , l_line_rec.ship_to_org_id
2116 , l_line_val_rec.ship_to_org
2117 , l_line_rec.deliver_to_org_id
2118 , l_line_val_rec.deliver_to_org
2119 , l_line_rec.invoice_to_org_id
2120 , l_line_val_rec.invoice_to_org
2121 , l_line_val_rec.ship_to_address1
2122 , l_line_val_rec.ship_to_address2
2123 , l_line_val_rec.ship_to_address3
2124 , l_line_val_rec.ship_to_address4
2125 , l_line_val_rec.ship_to_city
2126 , l_line_val_rec.ship_to_state
2127 , l_line_val_rec.ship_to_zip
2128 , l_line_val_rec.ship_to_country
2129 , l_line_rec.ship_to_contact_id
2130 , l_line_val_rec.ship_to_contact
2131 , l_line_rec.deliver_to_contact_id
2132 , l_line_val_rec.deliver_to_contact
2133 , l_line_rec.invoice_to_contact_id
2134 , l_line_val_rec.invoice_to_contact
2135 , l_line_rec.drop_ship_flag
2136 , l_line_rec.ship_tolerance_above
2137 , l_line_rec.ship_tolerance_below
2138 , l_line_rec.price_list_id
2139 , l_line_val_rec.price_list
2140 , l_line_rec.pricing_date
2141 , l_line_rec.unit_list_price
2142 , l_line_rec.unit_selling_price
2143 , l_line_rec.calculate_price_flag
2144 , l_line_rec.ship_set_id
2145 , l_line_rec.ship_set
2146 , l_line_rec.arrival_set_id
2147 , l_line_rec.arrival_set
2148 , l_line_rec.fulfillment_set_id
2149 , l_line_rec.fulfillment_set
2150 , l_line_rec.tax_code
2151 , l_line_rec.tax_value
2152 , l_line_rec.tax_date
2153 , l_line_rec.tax_point_code
2154 , l_line_val_rec.tax_point
2155 , l_line_rec.tax_exempt_flag
2156 , l_line_rec.tax_exempt_number
2157 , l_line_rec.tax_exempt_reason_code
2158 , l_line_val_rec.tax_exempt_reason
2159 , l_line_rec.agreement_id
2160 , l_line_val_rec.agreement
2161 , l_line_rec.invoicing_rule_id
2162 , l_line_val_rec.invoicing_rule
2163 , l_line_rec.accounting_rule_id
2164 , l_line_val_rec.accounting_rule
2165 , l_line_rec.payment_term_id
2166 , l_line_val_rec.payment_term
2167 , l_line_rec.demand_class_code
2168 , l_line_rec.shipment_priority_code
2169 , l_line_val_rec.shipment_priority
2170 , l_line_rec.shipping_method_code
2171 , l_line_rec.shipping_instructions
2172 , l_line_rec.packing_instructions
2173 , l_line_rec.freight_carrier_code
2174 , l_line_rec.freight_terms_code
2175 , l_line_val_rec.freight_terms
2176 , l_line_rec.fob_point_code
2177 , l_line_val_rec.fob_point
2178 , l_line_rec.return_reason_code
2179 , l_line_rec.reference_type
2180 , l_line_rec.reference_header_id
2181 , l_line_rec.reference_line_id
2182 , l_line_rec.credit_invoice_line_id
2183 , l_line_rec.cust_po_number
2184 , l_line_rec.customer_line_number
2185 , l_line_rec.customer_shipment_number
2186 , l_line_rec.ordered_item_id
2187 , l_line_rec.item_identifier_type
2188 , l_line_rec.ordered_item
2189 -- , l_line_rec.customer_item_revision
2190 , l_line_rec.customer_item_net_price
2191 , l_line_rec.customer_payment_term_id
2195 , l_line_rec.customer_dock_code
2192 , l_line_val_rec.customer_payment_term
2193 , l_line_rec.demand_bucket_type_code
2194 , l_line_val_rec.demand_bucket_type
2196 , l_line_rec.customer_job
2197 , l_line_rec.customer_production_line
2198 , l_line_rec.cust_model_serial_number
2199 , l_line_rec.project_id
2200 , l_line_val_rec.project
2201 , l_line_rec.task_id
2202 , l_line_val_rec.task
2203 , l_line_rec.end_item_unit_number
2204 , l_line_rec.item_revision
2205 , l_line_rec.service_duration
2206 , l_line_rec.service_period
2207 , l_line_rec.service_start_date
2208 , l_line_rec.service_end_date
2209 , l_line_rec.service_coterminate_flag
2210 , l_line_rec.unit_selling_percent
2211 , l_line_rec.unit_list_percent
2212 , l_line_rec.unit_percent_base_price
2213 , l_line_rec.service_number
2214 -- , l_line_rec.fulfilled_flag -- To be added in Process Order
2215 -- , l_line_rec.closed_flag
2216 , l_line_rec.cancelled_flag
2217 , l_line_rec.context
2218 , l_line_rec.attribute1
2219 , l_line_rec.attribute2
2220 , l_line_rec.attribute3
2221 , l_line_rec.attribute4
2222 , l_line_rec.attribute5
2223 , l_line_rec.attribute6
2224 , l_line_rec.attribute7
2225 , l_line_rec.attribute8
2226 , l_line_rec.attribute9
2227 , l_line_rec.attribute10
2228 , l_line_rec.attribute11
2229 , l_line_rec.attribute12
2230 , l_line_rec.attribute13
2231 , l_line_rec.attribute14
2232 , l_line_rec.attribute15
2233 /* Added attribute 16 to 20 for bug 3513248 */
2234 , l_line_rec.attribute16
2235 , l_line_rec.attribute17
2236 , l_line_rec.attribute18
2237 , l_line_rec.attribute19
2238 , l_line_rec.attribute20
2239 , l_line_rec.tp_context
2240 , l_line_rec.tp_attribute1
2241 , l_line_rec.tp_attribute2
2242 , l_line_rec.tp_attribute3
2243 , l_line_rec.tp_attribute4
2244 , l_line_rec.tp_attribute5
2245 , l_line_rec.tp_attribute6
2246 , l_line_rec.tp_attribute7
2247 , l_line_rec.tp_attribute8
2248 , l_line_rec.tp_attribute9
2249 , l_line_rec.tp_attribute10
2250 , l_line_rec.tp_attribute11
2251 , l_line_rec.tp_attribute12
2252 , l_line_rec.tp_attribute13
2253 , l_line_rec.tp_attribute14
2254 , l_line_rec.tp_attribute15
2255 , l_line_rec.industry_context
2256 , l_line_rec.industry_attribute1
2257 , l_line_rec.industry_attribute2
2258 , l_line_rec.industry_attribute3
2259 , l_line_rec.industry_attribute4
2260 , l_line_rec.industry_attribute5
2261 , l_line_rec.industry_attribute6
2262 , l_line_rec.industry_attribute7
2263 , l_line_rec.industry_attribute8
2264 , l_line_rec.industry_attribute9
2265 , l_line_rec.industry_attribute10
2266 , l_line_rec.industry_attribute11
2267 , l_line_rec.industry_attribute12
2268 , l_line_rec.industry_attribute13
2269 , l_line_rec.industry_attribute14
2270 , l_line_rec.industry_attribute15
2271 , l_line_rec.industry_attribute16
2272 , l_line_rec.industry_attribute17
2273 , l_line_rec.industry_attribute18
2274 , l_line_rec.industry_attribute19
2275 , l_line_rec.industry_attribute20
2276 , l_line_rec.industry_attribute21
2277 , l_line_rec.industry_attribute22
2278 , l_line_rec.industry_attribute23
2279 , l_line_rec.industry_attribute24
2280 , l_line_rec.industry_attribute25
2281 , l_line_rec.industry_attribute26
2282 , l_line_rec.industry_attribute27
2283 , l_line_rec.industry_attribute28
2284 , l_line_rec.industry_attribute29
2285 , l_line_rec.industry_attribute30
2286 , l_line_rec.pricing_context
2287 , l_line_rec.pricing_attribute1
2288 , l_line_rec.pricing_attribute2
2289 , l_line_rec.pricing_attribute3
2290 , l_line_rec.pricing_attribute4
2291 , l_line_rec.pricing_attribute5
2292 , l_line_rec.pricing_attribute6
2293 , l_line_rec.pricing_attribute7
2294 , l_line_rec.pricing_attribute8
2295 , l_line_rec.pricing_attribute9
2296 , l_line_rec.pricing_attribute10
2297 , l_line_rec.global_attribute_category
2298 , l_line_rec.global_attribute1
2299 , l_line_rec.global_attribute2
2300 , l_line_rec.global_attribute3
2301 , l_line_rec.global_attribute4
2302 , l_line_rec.global_attribute5
2303 , l_line_rec.global_attribute6
2304 , l_line_rec.global_attribute7
2305 , l_line_rec.global_attribute8
2306 , l_line_rec.global_attribute9
2307 , l_line_rec.global_attribute10
2308 , l_line_rec.global_attribute11
2309 , l_line_rec.global_attribute12
2310 , l_line_rec.global_attribute13
2311 , l_line_rec.global_attribute14
2312 , l_line_rec.global_attribute15
2313 , l_line_rec.global_attribute16
2314 , l_line_rec.global_attribute17
2315 , l_line_rec.global_attribute18
2316 , l_line_rec.global_attribute19
2317 , l_line_rec.global_attribute20
2318 , l_line_rec.return_context
2319 , l_line_rec.return_attribute1
2320 , l_line_rec.return_attribute2
2321 , l_line_rec.return_attribute3
2322 , l_line_rec.return_attribute4
2323 , l_line_rec.return_attribute5
2324 , l_line_rec.return_attribute6
2325 , l_line_rec.return_attribute7
2326 , l_line_rec.return_attribute8
2327 , l_line_rec.return_attribute9
2328 , l_line_rec.return_attribute10
2329 , l_line_rec.return_attribute11
2330 , l_line_rec.return_attribute12
2331 , l_line_rec.return_attribute13
2332 , l_line_rec.return_attribute14
2333 , l_line_rec.return_attribute15
2337 , l_line_rec.change_reason
2334 , l_line_rec.request_id
2335 , l_line_rec.operation
2336 , l_line_rec.status_flag
2338 , l_line_rec.change_comments
2339 , l_line_rec.service_txn_reason_code
2340 , l_line_rec.service_txn_comments
2341 , l_line_rec.service_reference_type_code
2342 , l_line_rec.service_reference_order
2343 , l_line_rec.service_reference_line
2344 , l_line_rec.service_reference_system
2345 , l_segment_array(1)
2346 , l_segment_array(2)
2347 , l_segment_array(3)
2348 , l_segment_array(4)
2349 , l_segment_array(5)
2350 , l_segment_array(6)
2351 , l_segment_array(7)
2352 , l_segment_array(8)
2353 , l_segment_array(9)
2354 , l_segment_array(10)
2355 , l_segment_array(11)
2356 , l_segment_array(12)
2357 , l_segment_array(13)
2358 , l_segment_array(14)
2359 , l_segment_array(15)
2360 , l_segment_array(16)
2361 , l_segment_array(17)
2362 , l_segment_array(18)
2363 , l_segment_array(19)
2364 , l_segment_array(20)
2365 , l_line_val_rec.commitment
2366 , l_line_rec.commitment_id
2367 -- aksingh subinventory
2368 , l_line_rec.subinventory
2369 ,l_line_val_rec.salesrep
2370 ,l_line_rec.salesrep_id
2371 ,l_line_rec.earliest_acceptable_date
2372 ,l_line_rec.latest_acceptable_date
2373 ,l_line_val_rec.invoice_to_address1
2374 ,l_line_val_rec.invoice_to_address2
2375 ,l_line_val_rec.invoice_to_address3
2376 ,l_line_val_rec.invoice_to_address4
2377 ,l_line_val_rec.invoice_to_city
2378 ,l_line_val_rec.invoice_to_state
2379 ,l_line_val_rec.invoice_to_zip
2380 ,l_line_val_rec.invoice_to_country
2381 ,l_line_rec.user_item_description
2382 ,l_line_rec.change_sequence
2383 -- { Distributer Order related change
2384 ,l_line_rec.end_customer_id
2385 ,l_line_rec.end_customer_contact_id
2386 ,l_line_rec.end_customer_site_use_id
2387 --{added for bug 4240715
2388 ,l_line_val_rec.end_customer_name
2389 , l_line_val_rec.end_customer_site_address1
2390 , l_line_val_rec.end_customer_site_address2
2391 , l_line_val_rec.end_customer_site_address3
2392 , l_line_val_rec.end_customer_site_address4
2393 -- , l_line_val_rec.end_customer_site_location
2394 , l_line_val_rec.end_customer_site_city
2395 , l_line_val_rec.end_customer_site_state
2396 , l_line_val_rec.end_customer_site_postal_code
2397 , l_line_val_rec.end_customer_site_country
2398 , l_line_val_rec.end_customer_contact
2399 , l_line_val_rec.end_customer_number
2400 -- bug 4240715}
2401 ,l_line_rec.ib_owner
2402 ,l_line_rec.ib_current_location
2403 ,l_line_rec.ib_installed_at_location
2404 ,l_line_val_rec.ib_owner_dsp
2405 ,l_line_val_rec.ib_current_location_dsp
2406 ,l_line_val_rec.ib_installed_at_location_dsp
2407 -- Distributer Order related change }
2408 ;
2409 IF l_debug_level > 0 THEN
2410 oe_debug_pub.add('value inserted'||l_line_rec.end_customer_id); -- added for bug 4240715
2411 END IF;
2412 EXIT WHEN l_line_cursor%NOTFOUND;
2413
2414 IF l_debug_level > 0 THEN
2415 oe_debug_pub.add( 'AFTER LINE FETCH ' ) ;
2416 END IF;
2417 /*
2418 IF l_line_rec.operation = 'INSERT' THEN
2419 l_line_rec.operation := 'CREATE';
2420 END IF;
2421 */
2422
2423 --Assigning line level transaction phase value to header value for bug 3576009
2424 l_line_rec.transaction_phase_code := l_header_rec.transaction_phase_code;
2425
2426
2427 /* if missing, get line level sold to org id from header
2428 this is necessary for the case where the sold_to_org
2429 was populated and the header-level sold_to_org_id was derived
2430 */
2431
2432
2433 if l_line_rec.sold_to_org_id = FND_API.G_MISS_NUM then
2434 IF l_debug_level > 0 THEN
2435 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' ) ;
2436 END IF;
2437 l_line_rec.sold_to_org_id := l_header_rec.sold_to_org_id;
2438 end if;
2439
2440
2441
2442 l_line_count := l_line_count + 1;
2443 if l_line_rec.service_reference_order = FND_API.G_MISS_CHAR then
2444 IF l_debug_level > 0 THEN
2445 oe_debug_pub.add( 'L_LINE_REC.SERVICE_REFERENCE_ORDER ' || ASCII ( L_LINE_REC.SERVICE_REFERENCE_ORDER ) ) ;
2446 END IF;
2447 end if;
2448 if l_line_val_rec.sold_to_org = FND_API.G_MISS_CHAR then
2449 IF l_debug_level > 0 THEN
2450 oe_debug_pub.add( 'L_LINE_VAL_REC.SOLD_TO_ORG ' || ASCII ( L_LINE_VAL_REC.SOLD_TO_ORG ) ) ;
2451 END IF;
2452 end if;
2453
2454 --populate l_line_rec.inventory_item_id with ccid if any of the
2455 --segments are passed instead of inventory_item_id
2456 IF l_line_rec.inventory_item_id = FND_API.G_MISS_NUM AND
2457 ((l_segment_array(1) IS NOT NULL) OR
2458 (l_segment_array(2) IS NOT NULL) OR
2459 (l_segment_array(3) IS NOT NULL) OR
2460 (l_segment_array(4) IS NOT NULL) OR
2461 (l_segment_array(5) IS NOT NULL) OR
2462 (l_segment_array(6) IS NOT NULL) OR
2463 (l_segment_array(7) IS NOT NULL) OR
2464 (l_segment_array(8) IS NOT NULL) OR
2465 (l_segment_array(9) IS NOT NULL) OR
2466 (l_segment_array(10) IS NOT NULL) OR
2470 (l_segment_array(14) IS NOT NULL) OR
2467 (l_segment_array(11) IS NOT NULL) OR
2468 (l_segment_array(12) IS NOT NULL) OR
2469 (l_segment_array(13) IS NOT NULL) OR
2471 (l_segment_array(15) IS NOT NULL) OR
2472 (l_segment_array(16) IS NOT NULL) OR
2473 (l_segment_array(17) IS NOT NULL) OR
2474 (l_segment_array(18) IS NOT NULL) OR
2475 (l_segment_array(19) IS NOT NULL) OR
2476 (l_segment_array(20) IS NOT NULL)) THEN
2477 IF l_debug_level > 0 THEN
2478 oe_debug_pub.add( 'INSIDE GET CCID ROUTINE' ) ;
2479 END IF;
2480 FND_FLEX_KEY_API.SET_SESSION_MODE('customer_data');
2481 IF l_debug_level > 0 THEN
2482 oe_debug_pub.add( 'AFTER CALL TO SET SESSION' ) ;
2483 END IF;
2484 l_flexfield := FND_FLEX_KEY_API.FIND_FLEXFIELD('INV', 'MSTK');
2485 IF l_debug_level > 0 THEN
2486 oe_debug_pub.add( 'AFTER FIND FLEXFIELD' ) ;
2487 END IF;
2488 l_structure.structure_number := 101;
2489 FND_FLEX_KEY_API.GET_SEGMENTS(l_flexfield, l_structure, TRUE, l_n_segments, l_segments);
2490 IF l_debug_level > 0 THEN
2491 oe_debug_pub.add( 'SEGMENTS ENABLED = '||L_N_SEGMENTS ) ;
2492 END IF;
2493 IF l_debug_level > 0 THEN
2494 oe_debug_pub.add( 'ORG_ID = '||L_LINE_REC.ORG_ID ) ;
2495 END IF;
2496 IF l_debug_level > 0 THEN
2497 oe_debug_pub.add( 'VALIDATION_ORG_ID = '||L_VALIDATION_ORG ) ;
2498 END IF;
2499 IF l_debug_level > 0 THEN
2500 oe_debug_pub.add( 'ARRAY1 = '||L_SEGMENT_ARRAY ( 1 ) ) ;
2501 END IF;
2502 IF FND_FLEX_EXT.GET_COMBINATION_ID('INV', 'MSTK', 101, SYSDATE, l_n_segments, l_segment_array, l_id, l_validation_org) THEN
2503 l_line_rec.inventory_item_id := l_id;
2504 IF l_debug_level > 0 THEN
2505 oe_debug_pub.add( 'GET CCID = '||L_LINE_REC.INVENTORY_ITEM_ID ) ;
2506 END IF;
2507 ELSE
2508 IF l_debug_level > 0 THEN
2509 oe_debug_pub.add( 'ERROR IN GETTING CCID' ) ;
2510 END IF;
2511 failure_message := fnd_flex_ext.get_message;
2512 OE_MSG_PUB.Add_TEXT(failure_message);
2513 p_return_status := FND_API.G_RET_STS_ERROR;
2514 l_validate_only := FND_API.G_TRUE;
2515 IF l_debug_level > 0 THEN
2516 oe_debug_pub.add( 'FAILURE MESSAGE = ' || SUBSTR ( FAILURE_MESSAGE , 1 , 50 ) ) ;
2517 END IF;
2518 IF l_debug_level > 0 THEN
2519 oe_debug_pub.add( 'FAILURE MESSAGE = ' || SUBSTR ( FAILURE_MESSAGE , 51 , 50 ) ) ;
2520 END IF;
2521 IF l_debug_level > 0 THEN
2522 oe_debug_pub.add( 'FAILURE MESSAGE = ' || SUBSTR ( FAILURE_MESSAGE , 101 , 50 ) ) ;
2523 END IF;
2524 END IF;
2525
2526 ELSIF l_line_rec.inventory_item_id <> FND_API.G_MISS_NUM AND
2527 ((l_segment_array(1) IS NOT NULL) OR
2528 (l_segment_array(2) IS NOT NULL) OR
2529 (l_segment_array(3) IS NOT NULL) OR
2530 (l_segment_array(4) IS NOT NULL) OR
2531 (l_segment_array(5) IS NOT NULL) OR
2532 (l_segment_array(6) IS NOT NULL) OR
2533 (l_segment_array(7) IS NOT NULL) OR
2534 (l_segment_array(8) IS NOT NULL) OR
2535 (l_segment_array(9) IS NOT NULL) OR
2536 (l_segment_array(10) IS NOT NULL) OR
2537 (l_segment_array(11) IS NOT NULL) OR
2538 (l_segment_array(12) IS NOT NULL) OR
2539 (l_segment_array(13) IS NOT NULL) OR
2540 (l_segment_array(14) IS NOT NULL) OR
2541 (l_segment_array(15) IS NOT NULL) OR
2542 (l_segment_array(16) IS NOT NULL) OR
2543 (l_segment_array(17) IS NOT NULL) OR
2544 (l_segment_array(18) IS NOT NULL) OR
2545 (l_segment_array(19) IS NOT NULL) OR
2546 (l_segment_array(20) IS NOT NULL)) THEN
2547 IF l_debug_level > 0 THEN
2548 oe_debug_pub.add( 'INSIDE ROUTINE WHERE BOTH ID AND SEG ARE POPULATED' ) ;
2549 END IF;
2550 l_line_rec.inventory_item_id := FND_API.G_MISS_NUM;
2551 FND_MESSAGE.SET_NAME('ONT','OE_OIM_INVALID_ITEM_ID');
2552 FND_MESSAGE.SET_TOKEN('ORDER_NO', l_orig_sys_document_ref);
2553 FND_MESSAGE.SET_TOKEN('ORDER_SOURCE', l_order_source_id);
2554 OE_MSG_PUB.Add;
2555 p_return_status := FND_API.G_RET_STS_ERROR;
2556 l_validate_only := FND_API.G_TRUE;
2557 IF l_debug_level > 0 THEN
2558 oe_debug_pub.add( 'CANNOT IMPORT ORDER AS BOTH INVENTORY_ITEM_ID AND INVENTORY_ITEM_SEGMENTS ARE POPULATED' ) ;
2559 END IF;
2560 IF l_debug_level > 0 THEN
2561 oe_debug_pub.add( 'ORDER NO: '||L_ORIG_SYS_DOCUMENT_REF ) ;
2562 END IF;
2563 IF l_debug_level > 0 THEN
2564 oe_debug_pub.add( 'ORDER SOURCE: '||L_ORDER_SOURCE_ID ) ;
2565 END IF;
2566
2567 END IF;
2568
2569 l_line_rec.service_reference_order :=
2570 nvl(l_line_rec.service_reference_order, FND_API.G_MISS_CHAR);
2571 l_line_rec.service_reference_line :=
2572 nvl(l_line_rec.service_reference_line, FND_API.G_MISS_CHAR);
2573 l_line_tbl (l_line_count) := l_line_rec;
2574 l_line_val_tbl(l_line_count) := l_line_val_rec;
2575 if l_line_val_tbl(l_line_count).sold_to_org = FND_API.G_MISS_CHAR then
2576 IF l_debug_level > 0 THEN
2577 oe_debug_pub.add( 'L_LINE_TBL.SERVICE_REFERENCE_ORDER ' || ASCII ( L_LINE_TBL ( L_LINE_COUNT ) .SERVICE_REFERENCE_ORDER ) ) ;
2578 END IF;
2579 IF l_debug_level > 0 THEN
2580 oe_debug_pub.add( 'L_LINE_VAL_TBL.SOLD_TO_ORG ' || ASCII ( L_LINE_VAL_TBL ( L_LINE_COUNT ) .SOLD_TO_ORG ) ) ;
2581 END IF;
2582 end if;
2583
2584 l_orig_sys_line_ref := l_line_rec.orig_sys_line_ref;
2585 l_orig_sys_shipment_ref := l_line_rec.orig_sys_shipment_ref;
2586
2590
2587 IF l_debug_level > 0 THEN
2588 oe_debug_pub.add( 'ORIG SYS LINE REF ( '||L_LINE_COUNT||' ) : '|| L_LINE_TBL ( L_LINE_COUNT ) .ORIG_SYS_LINE_REF ) ;
2589 END IF;
2591 IF l_debug_level > 0 THEN
2592 oe_debug_pub.add( 'ORIG SYS SHIPMENT REF ( '||L_LINE_COUNT||' ) : '|| L_LINE_TBL ( L_LINE_COUNT ) .ORIG_SYS_SHIPMENT_REF ) ;
2593 END IF;
2594
2595 BEGIN
2596 oe_Debug_pub.add('calling get line ids'); --bug 4240715
2597 OE_CNCL_UTIL.get_line_ids(l_line_rec,l_line_val_rec);
2598 oe_debug_pub.add('after calling get line ids'||l_line_rec.end_customer_id); --bug 4240715
2599 --
2600 IF l_line_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2601 --
2602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2603 --
2604 ELSIF l_line_rec.return_status = FND_API.G_RET_STS_ERROR THEN
2605 --
2606 RAISE FND_API.G_EXC_ERROR;
2607 --
2608 END IF;
2609 --
2610 --
2611 OE_CNCL_Util.Convert_Miss_To_Null(l_line_rec);
2612 oe_debug_pub.add('after calling convert miss to null'||l_line_rec.end_customer_id); --bug 4240715
2613 --
2614
2615 OE_CNCL_Validate_Line.Attributes( x_return_status => l_return_status
2616 , p_x_line_rec => l_line_rec);
2617 oe_debug_pub.add('after calling validate line'||l_line_rec.end_customer_id); --bug 4240715
2618 --
2619 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2620 --
2621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2622 --
2623 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2624 --
2625 RAISE FND_API.G_EXC_ERROR;
2626 --
2627 END IF;
2628 --
2629
2630 OE_CNCL_Validate_Line.Entity( x_return_status => l_return_status
2631 , p_line_rec => l_line_rec);
2632 oe_debug_pub.add('after calling entity'||l_line_rec.end_customer_id); --bug 4240715
2633 --
2634 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2635 --
2636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2637 --
2638 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2639 --
2640 RAISE FND_API.G_EXC_ERROR;
2641 --
2642 END IF;
2643 --
2644 --{ added for bug 4240715
2645 l_line_tbl (l_line_count) := l_line_rec;
2646 l_line_val_tbl(l_line_count) := l_line_val_rec;
2647 -- bug 4240715}
2648
2649 EXCEPTION
2650 --
2651 WHEN FND_API.G_EXC_ERROR THEN
2652 --
2653 OE_MSG_PUB.reset_msg_context('LINE');
2654 --
2655 RAISE;
2656 --
2657 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2658 --
2659 OE_MSG_PUB.reset_msg_context('LINE');
2660 --
2661 RAISE;
2662 --
2663 WHEN OTHERS THEN
2664 --
2665 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2666 --
2667 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Lines');
2668 --
2669 END IF;
2670 --
2671 OE_MSG_PUB.reset_msg_context('LINE');
2672 --
2673 RAISE;
2674 --
2675 END;
2676
2677 /* -----------------------------------------------------------
2678 Line Discounts/Price adjustments
2679 -----------------------------------------------------------
2680 */
2681 IF l_debug_level > 0 THEN
2682 oe_debug_pub.add( 'BEFORE LINE ADJUSTMENTS LOOP' ) ;
2683 END IF;
2684
2685 OPEN l_line_adj_cursor;
2686 LOOP
2687 FETCH l_line_adj_cursor
2688 INTO l_line_adj_rec.orig_sys_discount_ref
2689 , l_line_adj_rec.change_request_code
2690 , l_line_adj_rec.list_header_id -- changed from discount_id
2691 , l_line_adj_rec.list_line_id -- changed from discount_line_id
2692 , l_line_adj_val_rec.discount
2693 , l_line_adj_rec.percent
2694 , l_line_adj_rec.automatic_flag
2695 , l_line_adj_rec.applied_flag
2696 , l_line_adj_rec.operand
2697 , l_line_adj_rec.arithmetic_operator
2698 , l_line_adj_rec.context
2699 , l_line_adj_rec.attribute1
2700 , l_line_adj_rec.attribute2
2701 , l_line_adj_rec.attribute3
2702 , l_line_adj_rec.attribute4
2703 , l_line_adj_rec.attribute5
2704 , l_line_adj_rec.attribute6
2705 , l_line_adj_rec.attribute7
2706 , l_line_adj_rec.attribute8
2707 , l_line_adj_rec.attribute9
2708 , l_line_adj_rec.attribute10
2709 , l_line_adj_rec.attribute11
2710 , l_line_adj_rec.attribute12
2711 , l_line_adj_rec.attribute13
2712 , l_line_adj_rec.attribute14
2713 , l_line_adj_rec.attribute15
2714 , l_line_adj_rec.request_id
2715 , l_line_adj_rec.operation
2716 , l_line_adj_rec.status_flag
2717 -- Price Adjustment related changes bug# 1220921 (Start)
2718 , l_line_adj_rec.AC_CONTEXT
2719 , l_line_adj_rec.AC_ATTRIBUTE1
2720 , l_line_adj_rec.AC_ATTRIBUTE2
2721 , l_line_adj_rec.AC_ATTRIBUTE3
2722 , l_line_adj_rec.AC_ATTRIBUTE4
2723 , l_line_adj_rec.AC_ATTRIBUTE5
2724 , l_line_adj_rec.AC_ATTRIBUTE6
2725 , l_line_adj_rec.AC_ATTRIBUTE7
2726 , l_line_adj_rec.AC_ATTRIBUTE8
2727 , l_line_adj_rec.AC_ATTRIBUTE9
2728 , l_line_adj_rec.AC_ATTRIBUTE10
2729 , l_line_adj_rec.AC_ATTRIBUTE11
2733 , l_line_adj_rec.AC_ATTRIBUTE15
2730 , l_line_adj_rec.AC_ATTRIBUTE12
2731 , l_line_adj_rec.AC_ATTRIBUTE13
2732 , l_line_adj_rec.AC_ATTRIBUTE14
2734 , l_line_adj_val_rec.LIST_NAME
2735 , l_line_adj_rec.LIST_LINE_TYPE_CODE
2736 , l_line_adj_rec.LIST_LINE_NO
2737 , l_line_adj_val_rec.VERSION_NO
2738 , l_line_adj_rec.INVOICED_FLAG
2739 , l_line_adj_rec.ESTIMATED_FLAG
2740 , l_line_adj_rec.INC_IN_SALES_PERFORMANCE
2741 , l_line_adj_rec.CHARGE_TYPE_CODE
2742 , l_line_adj_rec.CHARGE_SUBTYPE_CODE
2743 , l_line_adj_rec.CREDIT_OR_CHARGE_FLAG
2744 , l_line_adj_rec.INCLUDE_ON_RETURNS_FLAG
2745 , l_line_adj_rec.COST_ID
2746 , l_line_adj_rec.TAX_CODE
2747 , l_line_adj_rec.PARENT_ADJUSTMENT_ID
2748 , l_line_adj_rec.MODIFIER_MECHANISM_TYPE_CODE
2749 , l_line_adj_rec.MODIFIED_FROM
2750 , l_line_adj_rec.MODIFIED_TO
2751 , l_line_adj_rec.UPDATED_FLAG
2752 , l_line_adj_rec.UPDATE_ALLOWED
2753 , l_line_adj_rec.CHANGE_REASON_CODE
2754 , l_line_adj_rec.CHANGE_REASON_TEXT
2755 , l_line_adj_rec.PRICING_PHASE_ID
2756 , l_line_adj_rec.ADJUSTED_AMOUNT
2757 , l_adj_line_ref
2758 -- Price Adjustment related changes bug# 1220921 (End)
2759 ;
2760 EXIT WHEN l_line_adj_cursor%NOTFOUND;
2761
2762 l_line_adj_rec.line_index := l_line_count;
2763
2764 /*
2765 IF l_line_adj_rec.operation = 'INSERT' THEN
2766 l_line_adj_rec.operation := 'CREATE';
2767 END IF;
2768 */
2769
2770 l_line_adj_count := l_line_adj_count + 1;
2771 l_line_adj_tbl (l_line_adj_count) := l_line_adj_rec;
2772 l_line_adj_val_tbl (l_line_adj_count) := l_line_adj_val_rec;
2773
2774 l_adj_line_ref_tbl(l_line_adj_count) := l_adj_line_ref;
2775
2776 IF l_debug_level > 0 THEN
2777 oe_debug_pub.add( 'LINE ADJUSTMENT REF ( '||L_LINE_ADJ_COUNT||' ) : '|| L_LINE_ADJ_TBL ( L_LINE_ADJ_COUNT ) .ORIG_SYS_DISCOUNT_REF ) ;
2778 END IF;
2779
2780
2781 BEGIN
2782 --
2783 OE_CNCL_UTIL.get_line_adj_ids(l_line_adj_rec,l_line_adj_val_rec);
2784 --
2785 IF l_line_adj_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2786 --
2787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2788 --
2789 ELSIF l_line_adj_rec.return_status = FND_API.G_RET_STS_ERROR THEN
2790 --
2791 RAISE FND_API.G_EXC_ERROR;
2792 --
2793 END IF;
2794 --
2795
2796 --
2797 OE_CNCL_Util.Convert_Miss_To_Null(l_line_adj_rec);
2798 --
2799
2800 OE_CNCL_Validate_Line_Adj.Attributes( x_return_status => l_return_status
2801 , p_Line_Adj_rec => l_line_adj_rec);
2802
2803 --
2804 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2805 --
2806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2807 --
2808 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2809 --
2810 RAISE FND_API.G_EXC_ERROR;
2811 --
2812 END IF;
2813 --
2814
2815 OE_CNCL_Validate_Line_Adj.Entity( x_return_status => l_return_status
2816 , p_Line_Adj_rec => l_line_adj_rec);
2817 --
2818 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2819 --
2820 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2821 --
2822 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2823 --
2824 RAISE FND_API.G_EXC_ERROR;
2825 --
2826 END IF;
2827 --
2828
2829 EXCEPTION
2830 --
2831 WHEN FND_API.G_EXC_ERROR THEN
2832 --
2833 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
2834 --
2835 RAISE;
2836 --
2837 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2838 --
2839 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
2840 --
2841 RAISE;
2842 --
2843 WHEN OTHERS THEN
2844 --
2845 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2846 --
2847 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Line_Adjs');
2848 --
2849 END IF;
2850 --
2851 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
2852 --
2853 RAISE;
2854 --
2855 END;
2856 --
2857 END LOOP;
2858
2859 CLOSE l_line_adj_cursor;
2860
2861
2862 /* -----------------------------------------------------------
2863 Line Sales Credits
2864 -----------------------------------------------------------
2865 */
2866 IF l_debug_level > 0 THEN
2867 oe_debug_pub.add( 'BEFORE LINE SALES CREDITS LOOP' ) ;
2868 END IF;
2869
2870 OPEN l_line_scredit_cursor;
2871 LOOP
2872 FETCH l_line_scredit_cursor
2873 INTO l_line_scredit_rec.orig_sys_credit_ref
2874 , l_line_scredit_rec.change_request_code
2875 , l_line_scredit_rec.salesrep_id
2876 , l_line_scredit_val_rec.salesrep
2877 , l_line_scredit_rec.sales_credit_type_id
2878 , l_line_scredit_val_rec.sales_credit_type
2879 , l_line_scredit_rec.percent
2883 , l_line_scredit_rec.attribute3
2880 , l_line_scredit_rec.context
2881 , l_line_scredit_rec.attribute1
2882 , l_line_scredit_rec.attribute2
2884 , l_line_scredit_rec.attribute4
2885 , l_line_scredit_rec.attribute5
2886 , l_line_scredit_rec.attribute6
2887 , l_line_scredit_rec.attribute7
2888 , l_line_scredit_rec.attribute8
2889 , l_line_scredit_rec.attribute9
2890 , l_line_scredit_rec.attribute10
2891 , l_line_scredit_rec.attribute11
2892 , l_line_scredit_rec.attribute12
2893 , l_line_scredit_rec.attribute13
2894 , l_line_scredit_rec.attribute14
2895 , l_line_scredit_rec.attribute15
2896 , l_line_scredit_rec.operation
2897 , l_line_scredit_rec.status_flag
2898 , l_scredit_line_ref
2899 ;
2900 EXIT WHEN l_line_scredit_cursor%NOTFOUND;
2901
2902 l_line_scredit_rec.line_index := l_line_count;
2903
2904 /*
2905 IF l_line_scredit_rec.operation = 'INSERT' THEN
2906 l_line_scredit_rec.operation := 'CREATE';
2907 END IF;
2908 */
2909
2910 l_line_scredit_count := l_line_scredit_count + 1;
2911 l_line_scredit_tbl (l_line_scredit_count) := l_line_scredit_rec;
2912 l_line_scredit_val_tbl (l_line_scredit_count) := l_line_scredit_val_rec;
2913
2914 l_scredit_line_ref_tbl(l_line_scredit_count) := l_scredit_line_ref;
2915
2916 IF l_debug_level > 0 THEN
2917 oe_debug_pub.add( 'LINE SALESCREDITS REF ( '||L_LINE_SCREDIT_COUNT||' ) : '|| L_LINE_SCREDIT_TBL ( L_LINE_SCREDIT_COUNT ) .ORIG_SYS_CREDIT_REF ) ;
2918 END IF;
2919
2920
2921 BEGIN
2922 --
2923 OE_CNCL_UTIL.get_line_scredit_ids(l_line_scredit_rec,l_line_scredit_val_rec);
2924 --
2925 IF l_line_scredit_rec.return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2926 --
2927 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2928 --
2929 ELSIF l_line_scredit_rec.return_status = FND_API.G_RET_STS_ERROR THEN
2930 --
2931 RAISE FND_API.G_EXC_ERROR;
2932 --
2933 END IF;
2934 --
2935
2936 --
2937 OE_CNCL_Util.Convert_Miss_To_Null(l_line_scredit_rec);
2938 --
2939
2940
2941 OE_CNCL_Validate_Line_Scredit.Attributes( x_return_status => l_return_status
2942 , p_Line_Scredit_rec => l_line_scredit_rec);
2943 --
2944 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2945 --
2946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2947 --
2948 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2949 --
2950 RAISE FND_API.G_EXC_ERROR;
2951 --
2952 END IF;
2953 --
2954
2955 OE_CNCL_Validate_Line_Scredit.Entity( x_return_status => l_return_status
2956 , p_Line_Scredit_rec => l_line_scredit_rec);
2957
2958 --
2959 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2960 --
2961 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2962 --
2963 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2964 --
2965 RAISE FND_API.G_EXC_ERROR;
2966 --
2967 END IF;
2968 --
2969
2970 EXCEPTION
2971 --
2972 WHEN FND_API.G_EXC_ERROR THEN
2973 --
2974 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
2975 --
2976 RAISE;
2977 --
2978 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2979 --
2980 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
2981 --
2982 RAISE;
2983 --
2984 WHEN OTHERS THEN
2985 --
2986 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2987 --
2988 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Line_Scredits');
2989 --
2990 END IF;
2991 --
2992 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
2993 --
2994 RAISE;
2995 --
2996 END;
2997
2998
2999 END LOOP;
3000 CLOSE l_line_scredit_cursor;
3001
3002 END LOOP; /* Lines cursor */
3003 CLOSE l_line_cursor;
3004
3005
3006
3007
3008 /*------------------------+
3009 | |
3010 | INSERTION BEGINS HERE |
3011 | |
3012 +------------------------*/
3013
3014 --
3015 --Header Insert
3016 --
3017 BEGIN
3018 --
3019 OE_Header_Util.get_order_number(l_header_rec,l_header_rec_old);
3020
3021
3022 IF l_debug_level > 0 THEN
3023 oe_debug_pub.add( 'ORDER NUMBER'|| L_HEADER_REC.ORDER_NUMBER , 2 ) ;
3024 END IF;
3025 --
3026 SELECT OE_ORDER_HEADERS_S.NEXTVAL
3027 INTO l_header_rec.header_id
3028 FROM DUAL;
3029 --
3030 OE_CNCL_Util.Convert_Miss_To_Null(l_header_rec);
3031 --
3032 l_header_rec.creation_date := SYSDATE;
3033 l_header_rec.created_by := FND_GLOBAL.USER_ID;
3034 -- bug 4002850, removed quotes around 1.0 below
3035 l_header_rec.version_number := nvl(l_header_rec.version_number,1.0);
3036 l_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
3040 nvl(l_header_rec.order_category_code, 'MIXED');
3037 l_header_rec.last_update_date := sysdate;
3038 l_header_rec.booked_flag := nvl(l_header_rec.booked_flag, 'Y');
3039 l_header_rec.order_category_code :=
3041 l_header_rec.open_flag := 'N';
3042 l_header_rec.flow_status_code := 'CLOSED';
3043 --
3044 IF l_debug_level > 0 THEN
3045 oe_debug_pub.add( 'USER ID IS '|| FND_GLOBAL.USER_ID ) ;
3046 END IF;
3047 IF l_debug_level > 0 THEN
3048 oe_debug_pub.add( 'RESP ID IS '|| FND_GLOBAL.RESP_ID ) ;
3049 END IF;
3050 IF l_debug_level > 0 THEN
3051 oe_debug_pub.add( 'APPL ID IS '|| FND_GLOBAL.RESP_APPL_ID ) ;
3052 END IF;
3053
3054 --
3055 OE_Header_Util.Insert_Row(l_header_rec);
3056 --
3057 IF l_debug_level > 0 THEN
3058 oe_debug_pub.add( 'HEADER ID : ' || L_HEADER_REC.HEADER_ID , 2 ) ;
3059 END IF;
3060
3061
3062 EXCEPTION
3063 --
3064 WHEN FND_API.G_EXC_ERROR THEN
3065 --
3066 l_header_rec.return_status := FND_API.G_RET_STS_ERROR;
3067 --
3068 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3069 OE_MSG_PUB.reset_msg_context('HEADER');
3070 --
3071 RAISE;
3072 --
3073 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3074 --
3075 l_header_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3076 --
3077 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3078 OE_MSG_PUB.reset_msg_context('HEADER');
3079 --
3080 RAISE;
3081 --
3082 WHEN OTHERS THEN
3083 --
3084 l_header_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3085 --
3086 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3087 --
3088 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3089 'Header');
3090 --
3091 END IF;
3092 --
3093 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3094 OE_MSG_PUB.reset_msg_context('HEADER');
3095 --
3096 RAISE;
3097 --
3098 END;
3099 --
3100 --
3101 -- Loop for Sales Credits
3102 --
3103 --
3104 FOR I in 1..l_header_scredit_tbl.count
3105 --
3106 LOOP
3107 --
3108 BEGIN
3109 --
3110 SELECT OE_SALES_CREDITS_S.NEXTVAL
3111 INTO l_header_scredit_tbl(I).sales_credit_id
3112 FROM DUAL;
3113 --
3114 l_header_scredit_tbl(I).header_id := l_header_rec.header_id;
3115 --
3116 OE_CNCL_Util.Convert_Miss_To_Null(l_header_scredit_tbl(I));
3117 --
3118 l_header_scredit_tbl(I).creation_date := SYSDATE;
3119 l_header_scredit_tbl(I).created_by := FND_GLOBAL.USER_ID;
3120 l_header_scredit_tbl(I).last_updated_by := FND_GLOBAL.USER_ID;
3121 l_header_scredit_tbl(I).last_update_date := sysdate;
3122 --
3123 OE_Header_Scredit_Util.Insert_Row(l_header_scredit_tbl(I));
3124 --
3125 EXCEPTION
3126 --
3127 WHEN FND_API.G_EXC_ERROR THEN
3128 --
3129 l_header_scredit_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
3130 --
3131 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3132 OE_MSG_PUB.reset_msg_context('HEADER_SCREDIT');
3133 --
3134 RAISE;
3135 --
3136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3137 --
3138 l_header_scredit_tbl(I).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3139 --
3140 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3141 OE_MSG_PUB.reset_msg_context('HEADER_SCREDIT');
3142 --
3143 RAISE;
3144 --
3145 WHEN OTHERS THEN
3146 --
3147 l_header_scredit_tbl(I).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3148 --
3149 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3150 --
3151 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3152 'Header_Scredits');
3153 --
3154 END IF;
3155 --
3156 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3157 OE_MSG_PUB.reset_msg_context('HEADER_SCREDIT');
3158 --
3159 RAISE;
3160 --
3161 END;
3162 --
3163 END LOOP;
3164 --
3165 --
3166 -- Loop for Header Price Adjustment
3167 --
3168 --
3169 --
3170 FOR I in 1..l_header_adj_tbl.COUNT
3171 --
3172 LOOP
3173 --
3174 BEGIN
3175 --
3176 SELECT OE_PRICE_ADJUSTMENTS_S.NEXTVAL
3177 INTO l_header_adj_tbl(I).price_adjustment_id
3178 FROM DUAL;
3179 --
3180 l_header_adj_tbl(I).header_id := l_header_rec.header_id;
3181 --
3182 OE_CNCL_Util.Convert_Miss_To_Null(l_header_adj_tbl(I));
3183 --
3184 l_header_adj_tbl(I).creation_date := SYSDATE;
3185 l_header_adj_tbl(I).created_by := FND_GLOBAL.USER_ID;
3186 l_header_adj_tbl(I).last_updated_by := FND_GLOBAL.USER_ID;
3187 l_header_adj_tbl(I).last_update_date := sysdate;
3188 --
3189 OE_Header_Adj_Util.Insert_Row(l_header_adj_tbl(I));
3193 WHEN FND_API.G_EXC_ERROR THEN
3190 --
3191 EXCEPTION
3192 --
3194 --
3195 l_header_adj_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
3196 --
3197 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3198 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
3199 --
3200 RAISE;
3201 --
3202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3203 --
3204 l_header_adj_tbl(I).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3205 --
3206 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3207 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
3208 --
3209 RAISE;
3210 --
3211 WHEN OTHERS THEN
3212 --
3213 l_header_adj_tbl(I).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3214 --
3215 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3216 --
3217 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3218 'Header_Adjs');
3219 --
3220 END IF;
3221 --
3222 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3223 OE_MSG_PUB.reset_msg_context('HEADER_ADJ');
3224 --
3225 RAISE;
3226 --
3227 END;
3228 --
3229 END LOOP;
3230 --
3231 --
3232 --
3233 -- Line insert
3234 --
3235 --
3236 --
3237 FOR I in 1..l_line_tbl.COUNT
3238 --
3239 LOOP
3240 --
3241 --
3242 SELECT OE_ORDER_LINES_S.NEXTVAL
3243 INTO l_line_tbl(I).line_id
3244 FROM DUAL;
3245 --
3246 -- { Start Before insert check is the item_type_code is
3247 -- present. Call get_item_type_code function
3248 -- bug 1949855
3249
3250
3251 l_line_rec := l_line_tbl(I);
3252 l_line_tbl(I).item_type_code := OE_CNCL_Validate_Line.get_item_type
3253 (l_line_rec);
3254
3255 IF l_debug_level > 0 THEN
3256 oe_debug_pub.add( 'ITEM TYPE CODE => ' || L_LINE_TBL ( I ) .ITEM_TYPE_CODE ) ;
3257 END IF;
3258 -- End Before insert check is the item_type_code}
3259
3260 --
3261 IF ((l_line_tbl(I).item_type_code = 'MODEL') AND
3262 (l_line_tbl(I).orig_sys_line_ref = l_line_tbl(I).top_model_line_ref)) THEN
3263
3264 l_line_tbl(I).top_model_line_id := l_line_tbl(I).line_id;
3265
3266 ELSIF l_line_tbl(I).item_type_code = 'SERVICE' THEN
3267 --
3268 IF l_line_tbl(I).service_reference_type_code <> 'ORDER' THEN
3269 --
3270 l_line_tbl(I).service_reference_system_id := TO_NUMBER(l_line_tbl(I).service_reference_system);
3271 --
3272 END IF;
3273 --
3274 END IF;
3275 --
3276
3277 l_line_tbl(I).header_id := l_header_rec.header_id;
3278 --
3279 OE_CNCL_Util.Convert_Miss_To_Null(l_line_tbl(I));
3280 --
3281
3282 l_line_tbl(I).flow_status_code := 'CLOSED';
3283
3284 l_line_tbl(I).creation_date := SYSDATE;
3285 l_line_tbl(I).created_by := FND_GLOBAL.USER_ID;
3286 l_line_tbl(I).last_updated_by := FND_GLOBAL.USER_ID;
3287 l_line_tbl(I).last_update_date := SYSDATE;
3288
3289 --
3290
3291 --l_line_tbl(I).line_category_code := 'ORDER';
3292 --bug 1857305
3293
3294 IF(nvl(l_line_val_tbl(I).line_type,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR) THEN
3295
3296 BEGIN
3297
3298 SELECT order_category_code
3299 INTO l_line_val_tbl(I).line_type
3300 FROM OE_LINE_TYPES_V
3301 WHERE line_type_id = l_line_tbl(I).line_type_id ;
3302
3303 EXCEPTION
3304
3305 WHEN NO_DATA_FOUND THEN
3306
3307 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3308 THEN
3309
3310 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3311 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','line_type_id');
3312 OE_MSG_PUB.Add;
3313
3314 END IF;
3315
3316 WHEN OTHERS THEN
3317
3318 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3319 THEN
3320 OE_MSG_PUB.Add_Exc_Msg
3321 ( G_PKG_NAME
3322 , 'Order_Import'
3323 );
3324 END IF;
3325
3326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3327
3328 END;
3329
3330 END IF;
3331
3332 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
3333
3334 l_line_tbl(I).line_category_code := 'ORDER';
3335
3336 ELSIF (nvl(l_line_val_tbl(I).line_type, 'ORDER') = 'RETURN') THEN
3337
3338 l_line_tbl(I).line_category_code := 'RETURN';
3339
3340 ELSE
3341
3342 l_line_tbl(I).line_category_code := 'ORDER';
3343
3344 END IF;
3345
3346 l_line_tbl(I).open_flag := 'N';
3347 l_line_tbl(I).shipment_number := nvl(l_line_tbl(I).shipment_number,1);
3348 l_line_tbl(I).booked_flag := 'Y';
3352 End If;
3349 If l_line_tbl(I).cancelled_flag = 'N' Then
3350 l_line_tbl(I).cancelled_quantity := 0;
3351 l_line_tbl(I).cancelled_quantity2 := 0; -- INVCONV
3353
3354 --
3355 --
3356 -- Loop for Line Sales Credit
3357 --
3358 --
3359 /* FOR J in 1..l_line_scredit_tbl.COUNT
3360 --
3361 LOOP
3362 --
3363 IF l_scredit_line_ref_tbl(J) = l_line_tbl(I).orig_sys_line_ref THEN
3364 --
3365 SELECT OE_SALES_CREDITS_S.NEXTVAL
3366 INTO l_line_scredit_tbl(J).sales_credit_id
3367 FROM DUAL;
3368 --
3369 l_line_scredit_tbl(J).header_id := l_header_rec.header_id;
3370 --
3371 l_line_scredit_tbl(J).line_id := l_line_tbl(I).line_id;
3372 --
3373 OE_CNCL_Util.Convert_Miss_To_Null(l_line_scredit_tbl(J));
3374 --
3375 l_line_scredit_tbl(J).creation_date := SYSDATE;
3376 l_line_scredit_tbl(J).created_by := FND_GLOBAL.USER_ID;
3377 l_line_scredit_tbl(J).last_updated_by := FND_GLOBAL.USER_ID;
3378 l_line_scredit_tbl(J).last_update_date := sysdate;
3379 --
3380 END IF;
3381 --
3382 END LOOP;*/
3383 --
3384 --
3385 -- Loop for Line Price Adjustment
3386 --
3387 --
3388 /* FOR K in 1..l_line_adj_tbl.COUNT
3389 --
3390 LOOP
3391 --
3392 IF l_adj_line_ref_tbl(K) = l_line_tbl(I).orig_sys_line_ref THEN
3393 --
3394 SELECT OE_PRICE_ADJUSTMENTS_S.NEXTVAL
3395 INTO l_line_adj_tbl(K).price_adjustment_id
3396 FROM DUAL;
3397 --
3398 l_line_adj_tbl(K).header_id := l_header_rec.header_id;
3399 l_line_adj_tbl(K).line_id := l_line_tbl(I).line_id;
3400 --
3401 OE_CNCL_Util.Convert_Miss_To_Null(l_line_adj_tbl(K));
3402 --
3403 l_line_adj_tbl(K).creation_date := SYSDATE;
3404 l_line_adj_tbl(K).created_by := FND_GLOBAL.USER_ID;
3405 l_line_adj_tbl(K).last_updated_by := FND_GLOBAL.USER_ID;
3406 l_line_adj_tbl(K).last_update_date := sysdate;
3407 --
3408 END IF;
3409 --
3410 END LOOP;--line adj*/
3411 --
3412 END LOOP; --end line
3413 --
3414
3415
3416 /*-------------------------+
3417 | |
3418 | NON-MODEL HANDLING CODE |
3419 | |
3420 +--------------------------*/
3421
3422
3423
3424 FOR L in 1..l_line_tbl.COUNT
3425 --
3426 LOOP
3427 --
3428
3429 BEGIN
3430
3431 --check for non models
3432
3433
3434 IF ((l_line_tbl(L).item_type_code <> 'MODEL' AND l_line_tbl(L).item_type_code <> 'STANDARD' ) AND
3435 (l_line_tbl(L).orig_sys_line_ref <> l_line_tbl(L).top_model_line_ref)) THEN
3436
3437 FOR M in 1..l_line_tbl.COUNT
3438 LOOP
3439
3440 IF(l_line_tbl(L).top_model_line_ref = l_line_tbl(M).orig_sys_line_ref) THEN
3441
3442 l_line_tbl(L).top_model_line_id := l_line_tbl(M).line_id;
3443
3444 EXIT;
3445
3446 END IF;
3447
3448 END LOOP;
3449
3450 ELSIF l_line_tbl(L).item_type_code = 'SERVICE' THEN
3451 --
3452 IF l_line_tbl(L).service_reference_type_code = 'ORDER' THEN
3453 --
3454 FOR N IN 1..l_line_tbl.COUNT
3455 --
3456 LOOP
3457 --
3458 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
3459 --
3460 l_line_tbl(L).service_reference_line_id := l_line_tbl(N).line_id;
3461 --
3462 END IF;
3463 --
3464 END LOOP;
3465 --
3466 END IF;
3467 --
3468 END IF;
3469 --
3470
3471 OE_Line_Util.Insert_Row(l_line_tbl(L));
3472
3473 FOR J in 1..l_line_scredit_tbl.COUNT
3474 --
3475 LOOP
3476 --
3477 BEGIN
3478 --
3479 --
3480 IF l_debug_level > 0 THEN
3481 oe_debug_pub.add( TO_CHAR ( J ) , 2 ) ;
3482 END IF;
3483 IF l_debug_level > 0 THEN
3484 oe_debug_pub.add( 'L_SCREDIT_LINE_REF_TBL ' || L_SCREDIT_LINE_REF_TBL ( J ) , 2 ) ;
3485 END IF;
3486 IF l_scredit_line_ref_tbl(J) = l_line_tbl(L).orig_sys_line_ref THEN
3487 --
3488 IF l_debug_level > 0 THEN
3489 oe_debug_pub.add( 'INSIDE IF STATEMENT**********************' , 2 ) ;
3490 END IF;
3491 SELECT OE_SALES_CREDITS_S.NEXTVAL
3492 INTO l_line_scredit_tbl(J).sales_credit_id
3493 FROM DUAL;
3494 --
3495 l_line_scredit_tbl(J).header_id := l_header_rec.header_id;
3496 --
3497 l_line_scredit_tbl(J).line_id := l_line_tbl(L).line_id;
3498 --
3499 OE_CNCL_Util.Convert_Miss_To_Null(l_line_scredit_tbl(J));
3500 --
3501 l_line_scredit_tbl(J).creation_date := SYSDATE;
3502 l_line_scredit_tbl(J).created_by := FND_GLOBAL.USER_ID;
3506 OE_Line_Scredit_Util.Insert_Row(l_line_scredit_tbl(J));
3503 l_line_scredit_tbl(J).last_updated_by := FND_GLOBAL.USER_ID;
3504 l_line_scredit_tbl(J).last_update_date := sysdate;
3505
3507 --
3508 END IF;
3509 --
3510
3511 --
3512 EXCEPTION
3513 --
3514 WHEN FND_API.G_EXC_ERROR THEN
3515 --
3516 l_line_scredit_tbl(J).return_status := FND_API.G_RET_STS_ERROR;
3517 --
3518 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3519 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
3520 --
3521 RAISE;
3522 --
3523 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3524 --
3525 l_line_scredit_tbl(J).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3526 --
3527 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3528 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
3529 --
3530 RAISE;
3531 --
3532 WHEN OTHERS THEN
3533 --
3534 l_line_scredit_tbl(J).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3535 --
3536 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3537 --
3538 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3539 'Line_Scredits');
3540 --
3541 END IF;
3542 --
3543 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3544 OE_MSG_PUB.reset_msg_context('LINE_SCREDIT');
3545 --
3546 RAISE;
3547 --
3548 END;
3549 --
3550 END LOOP; --scredit line
3551
3552
3553 FOR K in 1..l_line_adj_tbl.COUNT
3554 --
3555 LOOP
3556 --
3557 BEGIN
3558 --
3559 IF l_adj_line_ref_tbl(K) = l_line_tbl(L).orig_sys_line_ref THEN
3560 --
3561 SELECT OE_PRICE_ADJUSTMENTS_S.NEXTVAL
3562 INTO l_line_adj_tbl(K).price_adjustment_id
3563 FROM DUAL;
3564 --
3565 l_line_adj_tbl(K).header_id := l_header_rec.header_id;
3566 l_line_adj_tbl(K).line_id := l_line_tbl(L).line_id;
3567 --
3568 OE_CNCL_Util.Convert_Miss_To_Null(l_line_adj_tbl(K));
3569 --
3570 l_line_adj_tbl(K).creation_date := SYSDATE;
3571 l_line_adj_tbl(K).created_by := FND_GLOBAL.USER_ID;
3572 l_line_adj_tbl(K).last_updated_by := FND_GLOBAL.USER_ID;
3573 l_line_adj_tbl(K).last_update_date := SYSDATE;
3574 --
3575 -- JAUTOMO: TO DO
3576 -- l_line_adj_tbl(K).update_flag := 'N';
3577
3578 OE_Line_Adj_Util.Insert_Row(l_line_adj_tbl(K));
3579 --
3580 END IF;
3581 --
3582
3583 --
3584 EXCEPTION
3585 --
3586 WHEN FND_API.G_EXC_ERROR THEN
3587 --
3588 l_line_adj_tbl(K).return_status := FND_API.G_RET_STS_ERROR;
3589 --
3590 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3591 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
3592 --
3593 RAISE;
3594 --
3595 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3596 --
3597 l_line_adj_tbl(K).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3598 --
3599 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3600 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
3601 --
3602 RAISE;
3603 --
3604 WHEN OTHERS THEN
3605 --
3606 l_line_adj_tbl(K).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3607 --
3608 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3609 --
3610 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3611 'Line_Adjs');
3612 --
3613 END IF;
3614 --
3615 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3616 OE_MSG_PUB.reset_msg_context('LINE_ADJ');
3617 --
3618 RAISE;
3619 --
3620 END;
3621 --
3622 END LOOP;--line adj
3623
3624
3625 EXCEPTION
3626 --
3627 WHEN FND_API.G_EXC_ERROR THEN
3628 --
3629 l_line_tbl(L).return_status := FND_API.G_RET_STS_ERROR;
3630 --
3631 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3632 OE_MSG_PUB.reset_msg_context('LINE');
3633 --
3634 RAISE;
3635 --
3636 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3637 --
3638 l_line_tbl(L).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3639 --
3640 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3641 OE_MSG_PUB.reset_msg_context('LINE');
3642 --
3643 RAISE;
3644 --
3645 WHEN OTHERS THEN
3646 --
3647 l_line_tbl(L).return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3648 --
3649 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3650 --
3651 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3655 --
3652 'Lines');
3653 --
3654 END IF;
3656 OE_Header_Security.g_check_all_cols_constraint := 'Y';
3657 OE_MSG_PUB.reset_msg_context('LINE');
3658 --
3659 RAISE;
3660 --
3661 END;
3662 --
3663
3664
3665 END LOOP; --non-model and service
3666 --
3667
3668
3669
3670
3671
3672
3673 EXCEPTION
3674 --
3675 WHEN FND_API.G_EXC_ERROR THEN
3676 --
3677 p_return_status := FND_API.G_RET_STS_ERROR;
3678 --
3679 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3680 --
3681 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3682 --
3683 WHEN OTHERS THEN
3684 --
3685 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3686 --
3687 END;
3688
3689 --END LOOP;
3690 CLOSE l_header_cursor;
3691 ----------------------------------------
3692
3693 /* -----------------------------------------------------------
3694 Delete order from interface tables
3695 -----------------------------------------------------------
3696 */
3697 -- aksingh
3698 IF l_debug_level > 0 THEN
3699 oe_debug_pub.add( 'L_VALIDATE_ONLY '||L_VALIDATE_ONLY ) ;
3700 END IF;
3701 IF l_debug_level > 0 THEN
3702 oe_debug_pub.add( 'P_RETURN_STATUS '||P_RETURN_STATUS ) ;
3703 END IF;
3704
3705 -- aksingh IF p_validate_only = FND_API.G_FALSE AND
3706 IF l_validate_only = FND_API.G_FALSE AND
3707 p_return_status = FND_API.G_RET_STS_SUCCESS /* S=Success */
3708 THEN
3709 l_delete_flag := 'Y';
3710 ELSE
3711 l_delete_flag := 'N';
3712 END IF;
3713
3714 IF l_debug_level > 0 THEN
3715 oe_debug_pub.add( 'DELETE FLAG '||L_DELETE_FLAG ) ;
3716 END IF;
3717
3718 IF l_delete_flag = 'Y' THEN
3719
3720 IF l_debug_level > 0 THEN
3721 oe_debug_pub.add( 'BEFORE DELETING ORDER FROM INTERFACE TABLES' ) ;
3722 END IF;
3723
3724 OE_ORDER_IMPORT_UTIL_PVT.Delete_Order (
3725 p_request_id => l_request_id,
3726 p_order_source_id => l_order_source_id,
3727 p_orig_sys_document_ref => l_orig_sys_document_ref,
3728 p_sold_to_org_id => l_sold_to_org_id,
3729 p_sold_to_org => l_sold_to_org,
3730 p_change_sequence => l_change_sequence,
3731 p_return_status => l_return_status_del_ord
3732 );
3733
3734 /* -----------------------------------------------------------
3735 Set Return Status
3736 -----------------------------------------------------------
3737 */
3738 IF l_debug_level > 0 THEN
3739 oe_debug_pub.add( 'DELETE ORDER RETURN STATUS: '||L_RETURN_STATUS_DEL_ORD ) ;
3740 END IF;
3741
3742 IF l_return_status_del_ord IN (FND_API.G_RET_STS_ERROR)
3743 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR)
3744 THEN p_return_status := FND_API.G_RET_STS_ERROR;
3745 ELSIF l_return_status_del_ord IN (FND_API.G_RET_STS_UNEXP_ERROR)
3746 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR,
3747 FND_API.G_RET_STS_UNEXP_ERROR)
3748 THEN p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3749 END IF;
3750
3751 END IF; /* l_delete_flag = 'Y' */
3752
3753
3754 /* -----------------------------------------------------------
3755 Commit or rollback the transaction
3756 -----------------------------------------------------------
3757 */
3758 IF l_debug_level > 0 THEN
3759 oe_debug_pub.add( 'BEFORE COMMIT OR ROLLBACK' ) ;
3760 END IF;
3761
3762 IF l_validate_only = FND_API.G_TRUE
3763 OR p_return_status in (FND_API.G_RET_STS_ERROR, -- E:Expected error
3764 FND_API.G_RET_STS_UNEXP_ERROR) -- U:Unexpected error
3765 THEN
3766 l_commit_flag := 'N';
3767 ELSE
3768 l_commit_flag := 'Y';
3769 END IF;
3770
3771 IF l_debug_level > 0 THEN
3772 oe_debug_pub.add( 'COMMIT FLAG '||L_COMMIT_FLAG ) ;
3773 END IF;
3774
3775 IF l_commit_flag = 'Y' THEN
3776 COMMIT;
3777 ELSE
3778 ROLLBACK;
3779 END IF;
3780
3781 /* -----------------------------------------------------------
3782 Update error_flag in interface tables
3783 -----------------------------------------------------------
3784 */
3785 IF p_return_status IN (FND_API.G_RET_STS_ERROR,
3786 FND_API.G_RET_STS_UNEXP_ERROR)
3787 THEN
3788 BEGIN
3789 BEGIN
3790 IF l_debug_level > 0 THEN
3791 oe_debug_pub.add( 'BEFORE UPDATING ERROR FLAG FOR HEADER' ) ;
3792 END IF;
3793
3794 UPDATE oe_headers_interface
3795 SET error_flag = 'Y'
3796 WHERE order_source_id = l_order_source_id
3797 AND orig_sys_document_ref = l_orig_sys_document_ref
3798 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
3799 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
3800 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
3801 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
3802 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
3803 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
3807 WHEN OTHERS THEN
3804 AND nvl( request_id, FND_API.G_MISS_NUM)
3805 = nvl(l_request_id, FND_API.G_MISS_NUM);
3806 EXCEPTION
3808 IF l_debug_level > 0 THEN
3809 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3810 END IF;
3811 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3812 l_return_status_upd_err := FND_API.G_RET_STS_UNEXP_ERROR;
3813 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
3814 END IF;
3815 END;
3816
3817 BEGIN
3818 FOR I in 1..l_header_adj_tbl.count
3819 LOOP
3820 IF l_debug_level > 0 THEN
3821 oe_debug_pub.add( 'BEFORE UPDATING ERROR FLAG FOR HEADER PRICE ADJUSTMENTS' ) ;
3822 END IF;
3823 IF l_header_adj_tbl(I).return_status IN (
3824 FND_API.G_RET_STS_ERROR,
3825 FND_API.G_RET_STS_UNEXP_ERROR)
3826 THEN
3827 BEGIN
3828 UPDATE oe_price_adjs_interface
3829 SET error_flag = 'Y'
3830 WHERE order_source_id = l_order_source_id
3831 AND orig_sys_document_ref = l_orig_sys_document_ref
3832 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
3833 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
3834 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
3835 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
3836 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
3837 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
3838 AND nvl( request_id, FND_API.G_MISS_NUM)
3839 = nvl(l_request_id, FND_API.G_MISS_NUM)
3840 AND nvl(orig_sys_discount_ref, FND_API.G_MISS_CHAR)
3841 = nvl(l_header_adj_tbl(I).orig_sys_discount_ref,
3842 FND_API.G_MISS_CHAR);
3843 EXCEPTION
3844 WHEN OTHERS THEN
3845 IF l_debug_level > 0 THEN
3846 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3847 END IF;
3848 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3849 THEN
3850 l_return_status_upd_err := FND_API.G_RET_STS_UNEXP_ERROR;
3851 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
3852 END IF;
3853 END;
3854 END IF;
3855 END LOOP;
3856 END;
3857
3858 BEGIN
3859 FOR I in 1..l_header_scredit_tbl.count
3860 LOOP
3861 IF l_debug_level > 0 THEN
3862 oe_debug_pub.add( 'BEFORE UPDATING ERROR FLAG FOR HEADER SALES CREDITS' ) ;
3863 END IF;
3864 IF l_header_scredit_tbl(I).return_status IN (
3865 FND_API.G_RET_STS_ERROR,
3866 FND_API.G_RET_STS_UNEXP_ERROR)
3867 THEN
3868 BEGIN
3869 UPDATE oe_credits_interface
3870 SET error_flag = 'Y'
3871 WHERE order_source_id = l_order_source_id
3872 AND orig_sys_document_ref = l_orig_sys_document_ref
3873 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
3874 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
3875 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
3876 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
3877 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
3878 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
3879 AND nvl( request_id, FND_API.G_MISS_NUM)
3880 = nvl(l_request_id, FND_API.G_MISS_NUM)
3881 AND nvl(orig_sys_credit_ref, FND_API.G_MISS_CHAR)
3882 = nvl(l_header_scredit_tbl(I).orig_sys_credit_ref,
3883 FND_API.G_MISS_CHAR);
3884 EXCEPTION
3885 WHEN OTHERS THEN
3886 IF l_debug_level > 0 THEN
3887 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3888 END IF;
3889 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3890 THEN
3891 l_return_status_upd_err := FND_API.G_RET_STS_UNEXP_ERROR;
3892 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
3893 END IF;
3894 END;
3895 END IF;
3896 END LOOP;
3897 END;
3898
3899 BEGIN
3900 FOR I in 1..l_line_tbl.count
3901 LOOP
3902 IF l_debug_level > 0 THEN
3903 oe_debug_pub.add( 'BEFORE UPDATING ERROR FLAG FOR LINES' ) ;
3904 END IF;
3905 IF l_line_tbl(I).return_status IN (
3906 FND_API.G_RET_STS_ERROR,
3907 FND_API.G_RET_STS_UNEXP_ERROR)
3908 THEN
3909 BEGIN
3910 UPDATE oe_lines_interface
3911 SET error_flag = 'Y'
3912 WHERE order_source_id = l_order_source_id
3913 AND orig_sys_document_ref = l_orig_sys_document_ref
3914 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
3915 = nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
3916 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
3917 = nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
3918 AND nvl( change_sequence, FND_API.G_MISS_CHAR)
3919 = nvl(l_change_sequence, FND_API.G_MISS_CHAR)
3920 AND nvl( request_id, FND_API.G_MISS_NUM)
3921 = nvl(l_request_id, FND_API.G_MISS_NUM)
3925 = nvl(l_line_tbl(I).orig_sys_shipment_ref,FND_API.G_MISS_CHAR);
3922 AND nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
3923 = nvl(l_line_tbl(I).orig_sys_line_ref, FND_API.G_MISS_CHAR)
3924 AND nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
3926 EXCEPTION
3927 WHEN OTHERS THEN
3928 IF l_debug_level > 0 THEN
3929 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3930 END IF;
3931 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3932 THEN
3933 l_return_status_upd_err := FND_API.G_RET_STS_UNEXP_ERROR;
3934 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
3935 END IF;
3936 END;
3937 END IF;
3938 END LOOP;
3939 END;
3940
3941 /* -----------------------------------------------------------
3942 Set Return Status
3943 -----------------------------------------------------------
3944 */
3945 IF l_debug_level > 0 THEN
3946 oe_debug_pub.add( 'UPDATE ERROR_FLAG RETURN STATUS: '||L_RETURN_STATUS_UPD_ERR ) ;
3947 END IF;
3948
3949 IF l_return_status_upd_err IN (FND_API.G_RET_STS_ERROR)
3950 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR)
3951 THEN p_return_status := FND_API.G_RET_STS_ERROR;
3952 ELSIF l_return_status_upd_err IN (FND_API.G_RET_STS_UNEXP_ERROR)
3953 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR,
3954 FND_API.G_RET_STS_UNEXP_ERROR)
3955 THEN p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3956 END IF;
3957
3958 /* -----------------------------------------------------------
3959 Commit or rollback the error_flag
3960 -----------------------------------------------------------
3961 */
3962 IF l_return_status_upd_err NOT IN (FND_API.G_RET_STS_ERROR,
3963 FND_API.G_RET_STS_UNEXP_ERROR)
3964 THEN
3965 COMMIT; /* commit the error_flag updated */
3966 ELSE
3967 ROLLBACK; /* rollback the error_flag updated */
3968 END IF;
3969
3970 END;
3971 END IF; /* IF p_return_status IN ... */
3972
3973
3974 /* -----------------------------------------------------------
3975 Update the processing messages table
3976 -----------------------------------------------------------
3977 */
3978 OE_MSG_PUB.Count_And_Get (p_count => p_msg_count
3979 ,p_data => p_msg_data);
3980
3981 IF l_debug_level > 0 THEN
3982 oe_debug_pub.add( 'MESSAGES RETURNED: '|| TO_CHAR ( P_MSG_COUNT ) ) ;
3983 END IF;
3984
3985 IF p_msg_count > 0 THEN
3986
3987 IF l_debug_level > 0 THEN
3988 oe_debug_pub.add( 'BEFORE UPDATING THE PROCESSING MESSAGES TABLE' ) ;
3989 END IF;
3990
3991 FOR k IN 1 .. p_msg_count
3992 LOOP
3993 IF l_debug_level > 0 THEN
3994 oe_debug_pub.add( 'BEFORE CALLING GET' ) ;
3995 END IF;
3996 oe_msg_pub.get (
3997 p_msg_index => -2
3998 ,p_encoded => 'F'
3999 ,p_data => p_msg_data
4000 ,p_msg_index_out => l_msg_index);
4001
4002 l_msg_order_source_id := '';
4003 l_msg_orig_sys_document_ref := '';
4004 l_msg_orig_sys_line_ref := '';
4005 l_msg_orig_sys_shipment_ref := '';
4006 l_msg_sold_to_org_id := '';
4007 l_msg_sold_to_org := '';
4008 l_msg_change_sequence := '';
4009 l_msg_entity_code := '';
4010 l_msg_entity_ref := '';
4011
4012 begin
4013 IF l_debug_level > 0 THEN
4014 oe_debug_pub.add( 'BEFORE CALLING GET_MSG_CONTEXT' ) ;
4015 END IF;
4016 oe_msg_pub.get_msg_context (
4017 p_msg_index => l_msg_index
4018 ,x_entity_code => l_msg_entity_code
4019 ,x_entity_ref => l_msg_entity_ref
4020 ,x_entity_id => l_msg_entity_id
4021 ,x_header_id => l_msg_header_id
4022 ,x_line_id => l_msg_line_id
4023 ,x_order_source_id => l_msg_order_source_id
4024 ,x_orig_sys_document_ref => l_msg_orig_sys_document_ref
4025 ,x_orig_sys_line_ref => l_msg_orig_sys_line_ref
4026 ,x_orig_sys_shipment_ref => l_msg_orig_sys_shipment_ref
4027 ,x_change_sequence => l_msg_change_sequence
4028 ,x_source_document_type_id => l_msg_source_document_type_id
4029 ,x_source_document_id => l_msg_source_document_id
4030 ,x_source_document_line_id => l_msg_source_document_line_id
4031 ,x_attribute_code => l_msg_attribute_code
4032 ,x_constraint_id => l_msg_constraint_id
4033 ,x_process_activity => l_msg_process_activity
4034 ,x_notification_flag => l_msg_notification_flag
4035 ,x_type => l_msg_type
4036 );
4037
4038 exception
4039 when others then
4040 IF l_debug_level > 0 THEN
4041 oe_debug_pub.add( 'UNEXPECTED ERROR IN GET MSG : '||SQLERRM ) ;
4042 END IF;
4043 IF l_debug_level > 0 THEN
4044 oe_debug_pub.add( 'IGNORING ABOVE MESSAGE' ) ;
4048 -- bug 4195533 - changed the condition below from <> to =
4045 END IF;
4046 l_error_index_flag := 'Y';
4047 end;
4049 if l_error_index_flag = 'Y' then
4050 goto out_error;
4051 end if;
4052 IF l_debug_level > 0 THEN
4053 oe_debug_pub.add( 'AFTER CALLING GET_MSG_CONTEXT' ) ;
4054 END IF;
4055 IF oe_msg_pub.g_msg_tbl(l_msg_index).message_text IS NULL THEN
4056 IF l_debug_level > 0 THEN
4057 oe_debug_pub.add( 'IN INDEX.MESSAGE_TEXT IS NULL' ) ;
4058 END IF;
4059 p_msg_data := oe_msg_pub.get(l_msg_index, 'F');
4060 END IF;
4061
4062 l_msg_context := '';
4063 IF l_msg_order_source_id IS NOT NULL THEN
4064 l_msg_context := 'Src: ' || l_msg_order_source_id;
4065 END IF;
4066 IF l_msg_orig_sys_document_ref IS NOT NULL THEN
4067 l_msg_context := l_msg_context ||
4068 ', ' || 'Hdr: '||rtrim(l_msg_orig_sys_document_ref);
4069 END IF;
4070 IF l_msg_orig_sys_line_ref IS NOT NULL THEN
4071 l_msg_context := l_msg_context ||
4072 ', ' || 'Line: '||rtrim(l_msg_orig_sys_line_ref);
4073 END IF;
4074 IF l_msg_orig_sys_shipment_ref IS NOT NULL THEN
4075 l_msg_context := l_msg_context ||
4076 ', ' || 'Ship: '||rtrim(l_msg_orig_sys_shipment_ref);
4077 END IF;
4078 IF l_msg_sold_to_org_id IS NOT NULL THEN
4079 l_msg_context := l_msg_context ||
4080 ', ' || 'Customer ID: '|| l_msg_sold_to_org_id;
4081 END IF;
4082 IF l_msg_sold_to_org IS NOT NULL THEN
4083 l_msg_context := l_msg_context ||
4084 ', ' || 'Customer Name: '||rtrim(l_msg_sold_to_org);
4085 END IF;
4086 IF l_msg_change_sequence IS NOT NULL THEN
4087 l_msg_context := l_msg_context ||
4088 ', ' || 'Chg: '||rtrim(l_msg_change_sequence);
4089 END IF;
4090
4091 IF l_msg_entity_code IS NOT NULL AND
4092 l_msg_entity_ref IS NOT NULL
4093 THEN
4094 IF l_msg_entity_code IN ('HEADER_ADJ', 'LINE_ADJ') THEN
4095 l_msg_context := l_msg_context || ', ' || 'Adj: ';
4096 ELSIF l_msg_entity_code IN ('HEADER_SCREDIT', 'LINE_SCREDIT') THEN
4097 l_msg_context := l_msg_context || ', ' || 'SCredit: ';
4098 ELSIF l_msg_entity_code IN ('LOT_SERIAL') THEN
4099 l_msg_context := l_msg_context || ', ' || 'Lot: ';
4100 ELSIF l_msg_entity_code IN ('RESERVATION') THEN
4101 l_msg_context := l_msg_context || ', ' || 'Rsrvtn: ';
4102 END IF;
4103 l_msg_context := l_msg_context || rtrim(l_msg_entity_ref);
4104 END IF;
4105 l_msg_data := 'Msg-'||k||' for '||l_msg_context||': '||p_msg_data;
4106
4107 IF l_debug_level > 0 THEN
4108 oe_debug_pub.add( L_MSG_DATA ) ;
4109 END IF;
4110 -- start bug 4195533
4111 IF p_return_status = FND_API.G_RET_STS_SUCCESS
4112 AND l_header_rec.header_id <> FND_API.G_MISS_NUM THEN
4113 IF l_debug_level > 0 THEN
4114 oe_debug_pub.add( 'Header id updated in msg stack:' || l_header_rec.header_id ) ;
4115 END IF;
4116 oe_msg_pub.g_msg_tbl(l_msg_index).header_id := l_header_rec.header_id;
4117 END IF;
4118 -- end bug 4195533
4119 END LOOP;
4120 <<out_error>>
4121 null;
4122 END IF;
4123
4124 /* -----------------------------------------------------------
4125 Delete messages from the database table
4126 -----------------------------------------------------------
4127 */
4128 IF l_debug_level > 0 THEN
4129 oe_debug_pub.add( 'BEFORE DELETING OLD MESSAGES FROM THE DATABASE TABLE' ) ;
4130 END IF;
4131
4132 /*
4133 OE_ORDER_IMPORT_UTIL_PVT.Delete_Messages (
4134 p_request_id => l_request_id,
4135 p_order_source_id => l_order_source_id,
4136 p_orig_sys_document_ref => l_orig_sys_document_ref,
4137 p_change_sequence => l_change_sequence,
4138 p_return_status => l_return_status_del_msg
4139 );
4140 */
4141
4142
4143 /* -----------------------------------------------------------
4144 Set Return Status
4145 -----------------------------------------------------------
4146 */
4147 IF l_debug_level > 0 THEN
4148 oe_debug_pub.add( 'DELETE MESSAGES RETURN STATUS: '||L_RETURN_STATUS_DEL_MSG ) ;
4149 END IF;
4150
4151 IF l_return_status_del_msg IN (FND_API.G_RET_STS_ERROR)
4152 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR)
4153 THEN p_return_status := FND_API.G_RET_STS_ERROR;
4154 ELSIF l_return_status_del_msg IN (FND_API.G_RET_STS_UNEXP_ERROR)
4155 AND p_return_status NOT IN (FND_API.G_RET_STS_ERROR,
4156 FND_API.G_RET_STS_UNEXP_ERROR)
4157 THEN p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4158 END IF;
4159
4160
4161 /* -----------------------------------------------------------
4162 Save messages in the database table
4163 -----------------------------------------------------------
4164 */
4165 IF l_debug_level > 0 THEN
4166 oe_debug_pub.add( 'BEFORE SAVING MESSAGES IN THE DATABASE TABLE' ) ;
4167 END IF;
4168
4169 IF p_msg_count > 0 THEN
4170 OE_MSG_PUB.save_messages (l_request_id);
4171 END IF;
4172 COMMIT; /* commit again to commit the error messages */
4173
4174
4175 /* -----------------------------------------------------------
4176 Commit or rollback the messages
4177 -----------------------------------------------------------
4178 */
4179 IF l_return_status_del_msg = FND_API.G_RET_STS_SUCCESS
4180 -- AND l_return_status_sav_msg = FND_API.G_RET_STS_SUCCESS -- Currently not set
4181 THEN
4182 COMMIT; /* commit again to commit the error messages */
4183 ELSE
4184 ROLLBACK; /* rollback the error messages deleted */
4185 END IF;
4186
4187
4188 /* -----------------------------------------------------------
4189 Report final order processing results
4190 -----------------------------------------------------------
4191 */
4192 IF l_debug_level > 0 THEN
4193 oe_debug_pub.add( 'RETURN_STATUS: '||P_RETURN_STATUS ) ;
4194 END IF;
4195
4196 IF p_return_status = FND_API.G_RET_STS_ERROR THEN
4197 IF l_debug_level > 0 THEN
4198 oe_debug_pub.add( 'ORDER FAILED WITH ERROR ( S ) ' ) ;
4199 END IF;
4200 ELSIF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4201 IF l_debug_level > 0 THEN
4202 oe_debug_pub.add( 'ORDER FAILED WITH UNEXPECTED ERROR ( S ) ' ) ;
4203 END IF;
4204 ELSE
4205 IF l_debug_level > 0 THEN
4206 oe_debug_pub.add( 'ORDER PROCESSED SUCCESSFULLY' ) ;
4207 END IF;
4208 END IF;
4209
4210 --END LOOP; /* Headers cursor */
4211 --CLOSE l_header_cursor;
4212
4213
4214 /*-----------------------------------------------------------
4215 End of Order Import
4216 -----------------------------------------------------------
4217 */
4218 --oe_debug_pub.add('End of Order Import');
4219
4220
4221 EXCEPTION
4222 --
4223 WHEN OTHERS THEN
4224 IF l_debug_level > 0 THEN
4225 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
4226 END IF;
4227 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4228 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
4229 END IF;
4230 p_msg_count := p_msg_count + 1;
4231 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4232
4233 END IMPORT_ORDER;
4234
4235 END OE_CNCL_ORDER_IMPORT_PVT;