[Home] [Help]
PACKAGE BODY: APPS.OE_RETROBILL_PVT
Source
1 PACKAGE BODY OE_RETROBILL_PVT AS
2 /* $Header: OEXVRTOB.pls 120.32 2011/11/17 06:48:45 nilegupt ship $ */
3
4
5 /*
6 If p_retrobill_request_rec.mode = PREVIEW or user has directly EXECUTE without previous preview Then
7 1. Insert original lines_id and retrobill qty, original_ulp, original_usp to temp table.
8 2a. Use SQL and Cursor, Group lines by sold_to_org, currency and conversion
9 b. Select original essential line_attributes like price_list_id, nvl (line.bill_to,header.bill_to) into a cursor.
10
11 3. For each group (group can be detected when changes by sold_to_org, currency and conversion), process header attributes.
12 4. For each line in the group, process lines attributes.
13 a.Set pricing date to be sysdate.
14 b.Set the line_id to missing.
15 c.Set return_reason_code.
16 d.Let the original ulp and usp remain in the line tbl.
17 e.Copy price list id from old line and set validation_level = G_VALID_PARTIAL_WITH_DEF (which means to redefault is invalid)
18
19 5. For each group, call process_order. Process_order api will be called in batch mode. (which eventually call pricing integration price_line).
20 a. Log copy adjustment in OEXULINB if is a retrobill line
21 b. In OEXULADB.pls copy adjustment lines to set the retrobill_info_flag
22 c. Log retrobill event in OEXULINB if it is a retrobill line (retrobill_request_id is not null)
23 d. Price_line recognizes it is a retrobilling call because of the event and retrobill_request_id
24 e. Process_adjustment (within the price_line call) will perform post processing for lines and adjustments.
25 f. If the line has retrobilled before, get the difference by sum ULP, USP, ULPPQTY, USPPQTY.
26 g. Updated applied_flag = N on any adjustment has
27 h. If price negative then change line category RETURN, pricing recursion Y oe_order_pvt.lines to redefault the lines.
28
29 2. Log this retrobill request into OE_RETROBILL_REQUESTS table.
30 Elsif EXECUTE previous preview Then
31 For each retrobill_header_id in p_rectobill_line_tbl call OE_ORDER_BOOK_UTIL.Book_Order.
32 Elsif PREVIEW AGAIN Then
33
34 If;
35 */
36 Cursor Group_Lines IS
37 Select /*+ ORDERED USE_NL(t,l,h) */
38 LINE_ID
39 , nvl(l.ORG_ID,h.ORG_ID) org_id
40 , l.HEADER_ID
41 , LINE_TYPE_ID
42 , LINE_NUMBER
43 , ORDERED_ITEM
44 , nvl(l.REQUEST_DATE,h.request_date) request_date
45 , PROMISE_DATE
46 , SCHEDULE_SHIP_DATE
47 , ORDER_QUANTITY_UOM
48 , PRICING_QUANTITY
49 , PRICING_QUANTITY_UOM
50 , CANCELLED_QUANTITY
51 --, SHIPPED_QUANTITY
52 , ORDERED_QUANTITY
53 , FULFILLED_QUANTITY
54 --, SHIPPING_QUANTITY
55 --, SHIPPING_QUANTITY_UOM
56 , DELIVERY_LEAD_TIME
57 , nvl(l.TAX_EXEMPT_FLAG,h.TAX_EXEMPT_FLAG) TAX_EXEMPT_FLAG
58 , nvl(l.TAX_EXEMPT_NUMBER,h.TAX_EXEMPT_NUMBER) TAX_EXEMPT_NUMBER
59 , nvl(l.TAX_EXEMPT_REASON_CODE,h.TAX_EXEMPT_REASON_CODE) TAX_EXEMPT_REASON_CODE
60 , nvl(l.SHIP_FROM_ORG_ID,h.SHIP_FROM_ORG_ID) SHIP_FROM_ORG_ID
61 , nvl(l.SHIP_TO_ORG_ID,h.SHIP_TO_ORG_ID) SHIP_TO_ORG_ID
62 , nvl(l.INVOICE_TO_ORG_ID,h.INVOICE_TO_ORG_ID)INVOICE_TO_ORG_ID
63 , nvl(l.DELIVER_TO_ORG_ID,h.DELIVER_TO_ORG_ID) DELIVER_TO_ORG_ID
64 , nvl(l.SHIP_TO_CONTACT_ID,h.SHIP_TO_CONTACT_ID) SHIP_TO_CONTACT_ID
65 , nvl(l.DELIVER_TO_CONTACT_ID,h.DELIVER_TO_CONTACT_ID) DELIVER_TO_CONTACT_ID
66 , nvl(l.INVOICE_TO_CONTACT_ID,h.INVOICE_TO_CONTACT_ID) INVOICE_TO_CONTACT_ID
67 , INTMED_SHIP_TO_ORG_ID
68 , INTMED_SHIP_TO_CONTACT_ID
69 , nvl(l.SOLD_FROM_ORG_ID,h.SOLD_FROM_ORG_ID) SOLD_FROM_ORG_ID
70 , nvl(l.SOLD_TO_ORG_ID,h.SOLD_TO_ORG_ID) sold_to_org_id1
71 , nvl(l.CUST_PO_NUMBER,h.CUST_PO_NUMBER) CUST_PO_NUMBER
72 , nvl(l.SHIP_TOLERANCE_ABOVE,h.SHIP_TOLERANCE_ABOVE) SHIP_TOLERANCE_ABOVE
73 , nvl(l.SHIP_TOLERANCE_BELOW,h.SHIP_TOLERANCE_BELOW) SHIP_TOLERANCE_BELOW
74 --, nvl(l.DEMAND_BUCKET_TYPE_CODE,h.DEMAND_BUCKET_TYPE_CODE)
75 , VEH_CUS_ITEM_CUM_KEY_ID
76 , RLA_SCHEDULE_TYPE_CODE
77 , CUSTOMER_DOCK_CODE
78 , CUSTOMER_JOB
79 , CUSTOMER_PRODUCTION_LINE
80 , CUST_MODEL_SERIAL_NUMBER
81 , PROJECT_ID
82 , TASK_ID
83 , INVENTORY_ITEM_ID
84 , TAX_DATE
85 , TAX_CODE
86 , TAX_RATE
87 , nvl(l.DEMAND_CLASS_CODE,h.DEMAND_CLASS_CODE) DEMAND_CLASS_CODE
88 , l.PRICE_LIST_ID
89 , nvl(l.PRICING_DATE,h.pricing_date) pricing_date
90 , SHIPMENT_NUMBER
91 , nvl(l.AGREEMENT_ID,h.agreement_id) agreement_id
92 , nvl(l.SHIPMENT_PRIORITY_CODE,h.SHIPMENT_PRIORITY_CODE) SHIPMENT_PRIORITY_CODE
93 , nvl(l.SHIPPING_METHOD_CODE,h.SHIPPING_METHOD_CODE) SHIPPING_METHOD_CODE
94 , nvl(l.FREIGHT_CARRIER_CODE,h.FREIGHT_CARRIER_CODE) FREIGHT_CARRIER_CODE
95 , nvl(l.FREIGHT_TERMS_CODE,h.FREIGHT_TERMS_CODE) FREIGHT_TERMS_CODE
96 , nvl(l.FOB_POINT_CODE,h.FOB_POINT_CODE) FOB_POINT_CODE , nvl(l.TAX_POINT_CODE,h.TAX_POINT_CODE) TAX_POINT_CODE
97 , nvl(l.PAYMENT_TERM_ID,h.PAYMENT_TERM_ID) PAYMENT_TERM_ID
98 , nvl(l.INVOICING_RULE_ID,h.INVOICING_RULE_ID) INVOICING_RULE_ID
99 , nvl(l.ACCOUNTING_RULE_ID,h.ACCOUNTING_RULE_ID) ACCOUNTING_RULE_ID
100 , nvl(l.SOURCE_DOCUMENT_TYPE_ID,h.SOURCE_DOCUMENT_TYPE_ID) SOURCE_DOCUMENT_TYPE_ID
101 , l.ORIG_SYS_DOCUMENT_REF
102 , nvl(l.SOURCE_DOCUMENT_ID,h.SOURCE_DOCUMENT_ID) SOURCE_DOCUMENT_ID
103 , l.ORIG_SYS_LINE_REF
104 , l.SOURCE_DOCUMENT_LINE_ID
105 , ITEM_REVISION
106 , UNIT_SELLING_PRICE
107 , UNIT_LIST_PRICE
108 , TAX_VALUE
109 , TOP_MODEL_LINE_ID
110 , LINK_TO_LINE_ID
111 , COMPONENT_SEQUENCE_ID
112 , COMPONENT_CODE
113 , CONFIG_DISPLAY_SEQUENCE
114 , SORT_ORDER
115 , ITEM_TYPE_CODE
116 --, OPTION_NUMBER
117 , OPTION_FLAG
118 , DEP_PLAN_REQUIRED_FLAG
119 , VISIBLE_DEMAND_FLAG
120 , LINE_CATEGORY_CODE
121 --, ACTUAL_SHIPMENT_DATE
122 , CUSTOMER_TRX_LINE_ID
123 , ACTUAL_ARRIVAL_DATE
124 , ATO_LINE_ID
125 , AUTO_SELECTED_QUANTITY
126 , COMPONENT_NUMBER
127 , EARLIEST_ACCEPTABLE_DATE
128 , EXPLOSION_DATE
129 , LATEST_ACCEPTABLE_DATE
130 , MODEL_GROUP_NUMBER
131 , SCHEDULE_ARRIVAL_DATE
132 , SHIP_MODEL_COMPLETE_FLAG
133 , SCHEDULE_STATUS_CODE
134 , SOURCE_TYPE_CODE
135 , l.CANCELLED_FLAG
136 , l.OPEN_FLAG
137 , l.BOOKED_FLAG
138 , nvl(l.SALESREP_ID,h.SALESREP_ID)SALESREP_ID
139 , ARRIVAL_SET_ID
140 , SHIP_SET_ID
141 , SPLIT_FROM_LINE_ID
142 , CUST_PRODUCTION_SEQ_NUM
143 , AUTHORIZED_TO_SHIP_FLAG
144 --, OVER_SHIP_REASON_CODE
145 --, OVER_SHIP_RESOLVED_FLAG
146 , ORDERED_ITEM_ID
147 , ITEM_IDENTIFIER_TYPE
148 , CONFIGURATION_ID
149 , COMMITMENT_ID
150 --, SHIPPING_INTERFACED_FLAG
151 , CREDIT_INVOICE_LINE_ID
152 , l.FIRST_ACK_CODE
153 , l.FIRST_ACK_DATE
154 , l.LAST_ACK_CODE
155 , l.LAST_ACK_DATE
156 --, PLANNING_PRIORITY
157 , l.ORDER_SOURCE_ID --Order source id at the line ??
158 , ORIG_SYS_SHIPMENT_REF
159 , nvl(l.CHANGE_SEQUENCE,h.CHANGE_SEQUENCE)CHANGE_SEQUENCE
160 , nvl(l.DROP_SHIP_FLAG,h.DROP_SHIP_FLAG) DROP_SHIP_FLAG
161 , CUSTOMER_LINE_NUMBER
162 , CUSTOMER_SHIPMENT_NUMBER
163 , CUSTOMER_ITEM_NET_PRICE
164 , nvl(l.CUSTOMER_PAYMENT_TERM_ID,h.CUSTOMER_PAYMENT_TERM_ID) CUSTOMER_PAYMENT_TERM_ID
165 -- , nvl(l.BLANKET_NUMBER,h.BLANKET_NUMBER) BLANKET_NUMBER --bug8341909 --- bug# 8682469 : Reverted the fix done earlier
166 , FULFILLED_FLAG
167 , END_ITEM_UNIT_NUMBER
168 , CONFIG_HEADER_ID
169 , CONFIG_REV_NBR
170 , MFG_COMPONENT_SEQUENCE_ID
171 --, nvl(l.SHIPPING_INSTRUCTIONS,h.SHIPPING_INSTRUCTIONS) SHIPPING_INSTRUCTIONS
172 --, nvl(l.PACKING_INSTRUCTIONS,h.PACKING_INSTRUCTIONS) PACKING_INSTRUCTIONS
173 , INVOICED_QUANTITY
174 , REFERENCE_CUSTOMER_TRX_LINE_ID
175 , SPLIT_BY
176 , LINE_SET_ID
177 , SERVICE_TXN_REASON_CODE
178 , SERVICE_TXN_COMMENTS
179 , SERVICE_DURATION
180 , SERVICE_START_DATE
181 , SERVICE_END_DATE
182 , SERVICE_COTERMINATE_FLAG
183 , UNIT_LIST_PERCENT
184 , UNIT_SELLING_PERCENT
185 , UNIT_PERCENT_BASE_PRICE
186 , SERVICE_NUMBER
187 , SERVICE_PERIOD
188 , SHIPPABLE_FLAG
189 , MODEL_REMNANT_FLAG
190 , RE_SOURCE_FLAG
191 --, FLOW_STATUS_CODE
192 , FULFILLMENT_METHOD_CODE
193 , nvl(l.MARKETING_SOURCE_CODE_ID,h.MARKETING_SOURCE_CODE_ID)MARKETING_SOURCE_CODE_ID
194 , SERVICE_REFERENCE_TYPE_CODE
195 , SERVICE_REFERENCE_LINE_ID
196 , SERVICE_REFERENCE_SYSTEM_ID
197 , CALCULATE_PRICE_FLAG
198 , l.UPGRADED_FLAG
199 , REVENUE_AMOUNT
200 , FULFILLMENT_DATE
201 , PREFERRED_GRADE
202 , ORDERED_QUANTITY2
203 , ORDERED_QUANTITY_UOM2
204 --,SHIPPING_QUANTITY2
205 , CANCELLED_QUANTITY2
206 , SHIPPED_QUANTITY2
207 --, SHIPPING_QUANTITY_UOM2
208 , FULFILLED_QUANTITY2
209 , MFG_LEAD_TIME
210 --,LOCK_CONTROL
211 , SUBINVENTORY
212 , UNIT_LIST_PRICE_PER_PQTY
213 , UNIT_SELLING_PRICE_PER_PQTY
214 , nvl(l.PRICE_REQUEST_CODE,h.PRICE_REQUEST_CODE) PRICE_REQUEST_CODE
215 , ORIGINAL_INVENTORY_ITEM_ID
216 , ORIGINAL_ORDERED_ITEM_ID
217 , ORIGINAL_ORDERED_ITEM
218 , ORIGINAL_ITEM_IDENTIFIER_TYPE
219 , ITEM_SUBSTITUTION_TYPE_CODE
220 , OVERRIDE_ATP_DATE_CODE
221 , LATE_DEMAND_PENALTY_FACTOR
222 , nvl(l.ACCOUNTING_RULE_DURATION,h.ACCOUNTING_RULE_DURATION)ACCOUNTING_RULE_DURATION
223 , USER_ITEM_DESCRIPTION
224 , UNIT_COST
225 , RETROBILL_REQUEST_ID
226 --,h.SOLD_TO_ORG_ID
227 , h.TRANSACTIONAL_CURR_CODE
228 , h.CONVERSION_TYPE_CODE
229 , h.Order_Number
230 , h.Order_Type_Id
231 , t.value PLSQL_TBL_INDEX
232 FROM OM_ID_LIST_TMP t,
233 OE_ORDER_LINES_ALL l,
234 OE_ORDER_HEADERS_ALL h
235 WHERE l.line_id = t.key_id
236 AND l.header_id = h.header_id
237 ORDER BY sold_to_org_id1,h.transactional_curr_code,h.conversion_type_code;
238
239
240 G_CURRENT_RETROBILL_REQUEST_ID NUMBER;
241 G_LINES_NOT_RETRO_DISPLAYED VARCHAR2(1);
242 G_PKG_NAME VARCHAR2(30):='OE_RETROBILL_PVT';
243 --bug3738043
244 G_RETRO_PRICING_PHASE_COUNT NUMBER := 0;
245
246 -- 3661895
247 -- This for caching the retrobill bill only lines returned by interface_retrobilled_rma
248 TYPE Retro_Bill_Only_Line_Type IS RECORD
249 ( header_id NUMBER := NULL
250 , line_id NUMBER := NULL
251 );
252
253 TYPE Retro_Bill_Only_Line_Tbl_Type IS TABLE OF Retro_Bill_Only_Line_Type index by binary_integer;
254 G_Retro_Bill_Only_Line_Tbl Retro_Bill_Only_Line_Tbl_Type;
255 /*******************************************************************
256 This procedure display message in message stack for debuggin purpose
257 *******************************************************************/
258 Procedure display_message(p_msg_count IN NUMBER,
259 p_msg_data IN VARCHAR2) AS
260 l_msg_data VARCHAR2(5000);
261 l_msg_count NUMBER;
262 Begin
263 oe_debug_pub.add('no. of OE messages :'||p_msg_count);
264 for k in 1 .. p_msg_count loop
265 l_msg_data := oe_msg_pub.get( p_msg_index => k,
266 p_encoded => 'F'
267 );
268 oe_debug_pub.add(substr(l_msg_data,1,255));
269 oe_debug_pub.add('Error msg: '||substr(l_msg_data,1,200));
270 end loop;
271
272 fnd_msg_pub.count_and_get( p_encoded => 'F'
273 ,p_count => l_msg_count
274 ,p_data => l_msg_data);
275
276 --oe_debug_pub.add('no. of FND messages :'||l_msg_count,1);
277 oe_debug_pub.add('no. of FND messages :'||l_msg_count);
278
279 for k in 1 .. l_msg_count loop
280 l_msg_data := fnd_msg_pub.get( p_msg_index => k,
281 p_encoded => 'F'
282 );
283 oe_debug_pub.add('Error msg: '||substr(l_msg_data,1,200));
284 oe_debug_pub.add(substr(l_msg_data,1,255));
285 end loop;
286
287 End;
288
289 PROCEDURE Get_Last_Retro_HdrID(p_header_id IN NUMBER,
290 x_header_id OUT NOCOPY NUMBER) AS
291 Cursor last_retrobill_line IS
292 Select max(header_id)
293 From OE_ORDER_LINES_ALL l
294 Where l.order_source_id = 27
295 And l.orig_sys_document_ref = to_char(p_header_id) -- p_header_id --commented for bug#7665009
296 And retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID;
297
298 l_retro_line_id Number;
299 l_retro_header_id Number;
300 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
301 l_query_line_id Number;
302 l_query_header_id Number;
303
304 Begin
305
306 If l_debug_level > 0 Then
307 oe_debug_pub.add('Entering oe_retrobill_pvt.get_last_retro...input header_id:'||p_header_id);
308 End If;
309
310 --!!!Need to check system param, if no retrobill should return the same header_id immediately
311 --to save processing time!!!
312 OPEN last_retrobill_line;
313 Fetch last_retrobill_line Into l_retro_header_id;
314 Close last_retrobill_line;
315
316 IF l_retro_header_id IS NULL THEN
317 --null header id, has not been retrobilled
318 x_header_id:=p_header_id;
319 ELSE
320 x_header_id:=l_retro_header_id;
321 END IF;
322
323 If l_debug_level > 0 Then
324 oe_debug_pub.add('Exiting oe_retrobill_pvt.get_last_retro...output header_id:'||x_header_id);
325 End If;
326
327 Exception
328 When Others then
329 oe_debug_pub.add('Retro:get_last_retro_HdrID:'||SQLERRM);
330 x_header_id:=p_header_id;
331
332 End;
333
334
335 PROCEDURE Get_Last_Retro_LinID(p_line_id IN NUMBER,
336 x_line_id OUT NOCOPY NUMBER) AS
337 Cursor last_retrobill_line IS
338 Select max(l.line_id)
339 From OE_ORDER_LINES_ALL l,
340 OE_ORDER_LINES_ALL b
341 Where l.order_source_id = 27
342 And l.orig_sys_document_ref = b.header_id
343 And l.orig_sys_line_ref = to_char(p_line_id) -- p_line_id --commented for bug#7665009
344 And l.retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID
345 AND b.line_id = p_line_id;
346 l_retro_line_id Number;
347 l_retro_header_id Number;
348 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
349 l_query_line_id Number;
350 l_query_header_id Number;
351 Begin
352
353 If l_debug_level > 0 Then
354 oe_debug_pub.add('Entering oe_retrobill_pvt.get_last_retro...input Line_id:'||p_line_id);
355 End If;
356
357 --!!!Need to check system param, if no retrobill should return the same header_id immediately
358 --to save processing time!!!
359
360 OPEN last_retrobill_line;
361 Fetch last_retrobill_line Into l_retro_line_id;
362 Close last_retrobill_line;
363
364 IF l_retro_line_id IS NULL THEN
365 --null line id, has not been retrobilled
366 x_line_id:=p_line_id;
367 ELSE
368 x_line_id:=l_retro_line_id;
369 END IF;
370
371 If l_debug_level > 0 Then
372 oe_debug_pub.add('Exiting oe_retrobill_pvt.get_last_retro...output line_id:'||x_line_id);
373 End If;
374
375 Exception
376 When Others then
377 oe_debug_pub.add('Retro:get_last_retro_linID:'||SQLERRM);
378 x_line_id:=p_line_id;
379 End;
380
381
382 FUNCTION Get_First_Line_Price_List_Id RETURN NUMBER AS
383 Begin
384 G_FIRST_LINE_PL_ASSIGNED:='Y';
385 Return G_FIRST_LINE_PRICE_LIST_ID;
386 End;
387
388 --bug3738043 This function returns the number of pricing phases other than List Line Base Price Phase where the RETROBILL event is attached.
389 --The global variable G_RETRO_PRICING_PHASE_COUNT is initialized by calling this function in the procedure Perform_Operations if it is preview again
390 FUNCTION Get_Retro_Pricing_Phase_Count RETURN NUMBER AS
391 l_retro_pricing_phase_count NUMBER := 0;
392 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
393 BEGIN
394
395 SELECT count(*) INTO l_retro_pricing_phase_count
396 FROM qp_pricing_phases p,
397 qp_event_phases e
398 WHERE p.pricing_phase_id=e.pricing_phase_id
399 AND p.pricing_phase_id <> 1
400 AND e.pricing_event_code='RETROBILL'
401 AND trunc(sysdate) BETWEEN trunc(nvl(e.end_date_active,sysdate))
402 AND trunc(nvl(e.end_date_active,sysdate));
403 IF l_debug_level > 0 THEN
404 oe_debug_pub.add('IN Get_Retro_Pricing_Phase_count: The number of phases for RETROBILL event other than 1 is ' || l_retro_pricing_phase_count);
405 END IF;
406
407 RETURN l_retro_pricing_phase_count;
408 EXCEPTION
409 WHEN OTHERS THEN
410 RETURN 0;
411 END;
412
413 /******************************************************************
414 This function returns total sum amount a line has been retrobilled.
415 For example if someone returns a $10 item and the line has been
416 retrobilled twice, one time $4 and another time $3. The retrobilled
417 sum amount will be $7. We should return a credit of 10 - 7 to the
418 buyer instead of $10
419 ******************************************************************/
420 Procedure Get_Retrobilled_Sum(p_header_id IN NUMBER,
421 p_line_id IN NUMBER,
422 p_curr_retro_id IN NUMBER DEFAULT -999,
423 x_usp_sum OUT NOCOPY NUMBER,
424 x_ulp_sum OUT NOCOPY NUMBER) AS
425 l_usp_sum NUMBER:=0;
426 l_ulp_sum NUMBER:=0;
427
428 cursor retro_sum IS
429 select sum(oeol1.unit_selling_price * decode(oeol1.line_category_code,'RETURN',-1,1)),
430 sum(oeol1.unit_list_price * decode(oeol1.line_category_code,'RETURN',-1,1))
431 From oe_order_lines_all oeol1
432 Where oeol1.order_source_id = G_RETROBILL_ORDER_SOURCE_ID
433 and oeol1.orig_sys_document_ref = to_char(p_header_id) --p_header_id --commented for bug#7665009
434 and oeol1.orig_sys_line_ref = to_char(p_line_id) --p_line_id --commented for bug#7665009
435 and nvl(oeol1.retrobill_request_id,-1) <> p_curr_retro_id; --exclude current retrobill line
436
437 Begin
438 OPEN retro_sum;
439 FETCH retro_sum INTO x_usp_sum,x_ulp_sum;
440 CLOSE retro_sum;
441
442 x_usp_sum:=nvl(x_usp_sum,0);
443 x_ulp_sum:=nvl(x_ulp_sum,0);
444
445 Exception
446 When Others Then
447 oe_debug_pub.add('Retro:Get_Retrobilled_Sum:'||SQLERRM);
448 Raise;
449 End;
450
451 Procedure Get_Return_Price(p_header_id IN NUMBER,
452 p_line_id IN NUMBER,
453 p_ordered_qty IN NUMBER,--bug3540728
454 p_pricing_qty IN NUMBER,--bug3540728
455 p_usp IN NUMBER,
456 p_ulp IN NUMBER,
457 x_usp OUT NOCOPY NUMBER,
458 x_ulp OUT NOCOPY NUMBER,
459 x_ulp_ppqty OUT NOCOPY NUMBER,--bug3540728
460 x_usp_ppqty OUT NOCOPY NUMBER) AS --bug3540728
461 l_usp_sum NUMBER;
462 l_ulp_sum NUMBER;
463 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
464 Begin
465 Get_Retrobilled_Sum(p_header_id=>p_header_id,
466 p_line_id =>p_line_id,
467 x_usp_sum =>l_usp_sum,
468 x_ulp_sum =>l_ulp_sum);
469
470 IF l_debug_level > 0 THEN
471 oe_debug_pub.add('Retro:Input header_id:'||p_header_id);
472 oe_debug_pub.add('Retro:Input line_id:'||p_line_id);
473 oe_debug_pub.add('Retro:Input USP:'||p_usp);
474 oe_debug_pub.add('Retro:Input ULP:'||p_ulp);
475 END IF;
476
477 x_usp:=p_usp + l_usp_sum;
478 x_ulp:=p_ulp + l_ulp_sum;
479 --bug3540728 added the IF condition so that the _per_pqty fields will be populated with l_ref_line_id's values in OEXDLINB.pls if there are no retrobill lines.
480 IF (l_usp_sum <> 0 OR l_ulp_sum <>0) THEN
481 x_ulp_ppqty := (p_ulp + l_ulp_sum) * (nvl(p_ordered_qty,nvl(p_pricing_qty,1))/nvl(p_pricing_qty,1));
482 x_usp_ppqty := (p_usp + l_usp_sum) * (nvl(p_ordered_qty,nvl(p_pricing_qty,1))/nvl(p_pricing_qty,1));
483 END IF;
484 --bug3540728 end
485
486 IF l_debug_level > 0 THEN
487 oe_debug_pub.add('Retro:Return usp_sum:'||l_usp_sum);
488 oe_debug_pub.add('Retro:Return ulp_sum:'||l_ulp_sum);
489 oe_debug_pub.add('Retro:Return USP:'||x_usp);
490 oe_debug_pub.add('Retro:Return ULP:'||x_ulp);
491 oe_debug_pub.add('Retro:Return ULP_PER_PQTY:'||x_ulp_ppqty);
492 oe_debug_pub.add('Retro:Return USP_PER_PQTY:'||x_usp_ppqty);
493 END IF;
494
495 End;
496
497 /*****************************************************************
498 Validate Tax_Code and Tax_Date
499 ******************************************************************/
500 Function Is_Tax_Code_Valid(p_header_id IN NUMBER,
501 p_line_id IN NUMBER,
502 p_tax_code IN VARCHAR2,
503 p_tax_date IN DATE,
504 p_org_id IN NUMBER) RETURN BOOLEAN AS
505 l_dummy Varchar2(5);
506 l_order_number NUMBER;
507 l_line_no VARCHAR2(30);
508
509 BEGIN
510
511 -- Added below code for bug 12922209
512 -- From R12 onwards, Tax Code is not mandatory on the Order Line. If Tax Code is NULL, there is no point in trying to validate it.
513 -- If we validate NULL Tax Code, an un-necessary validation message gets logged for each Order Line against which Retrobill is being generated.
514 if p_tax_code is null then
515 return TRUE;
516 end if;
517 -- End of changes for bug 12922209
518
519
520 -- EBTax Changes
521 SELECT 'VALID'
522 INTO l_dummy
523 FROM ZX_OUTPUT_CLASSIFICATIONS_V
524 WHERE LOOKUP_CODE = p_tax_code
525 AND ORG_ID IN (p_org_id, -99)
526 AND TRUNC(p_tax_date)
527 BETWEEN TRUNC(START_DATE_ACTIVE) AND
528 TRUNC(NVL(END_DATE_ACTIVE, p_tax_date))
529 AND ROWNUM = 1;
530
531 RETURN TRUE;
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 BEGIN
535 SELECT order_number INTO l_order_number
536 FROM oe_order_headers_all
537 WHERE header_id=p_header_id;
538
539 l_line_no := OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER(p_line_id);
540 EXCEPTION
541 WHEN no_data_found THEN
542 null;
543 WHEN too_many_rows THEN
544 null;
545 END;
546
547
548 oe_debug_pub.add('Retro:Tax code invalid');
549 FND_MESSAGE.SET_NAME('ONT','ONT_RETROBILL_TAX_INVALID');
550 FND_MESSAGE.SET_TOKEN('TAX_CODE',p_tax_code);
551 FND_MESSAGE.SET_TOKEN('ORDER',l_order_number);
552 FND_MESSAGE.SET_TOKEN('LINE',l_line_no);
553 OE_MSG_PUB.Add;
554 RETURN FALSE;
555 WHEN OTHERS THEN
556 oe_debug_pub.add('Retro:IS_TAX_CODE_VALID:'||SQLERRM);
557 RETURN FALSE;
558 End;
559
560 --key header and line is to facilate an index search to oe_order_lines_all
561 --p_adjustment_level value is either 'HEADER' or 'LINE'. 'HEADER' means
562 --query order level adjustment and 'LINE' means query line level adjustment
563 PROCEDURE Get_Most_Recent_Retro_Adj
564 (p_key_header_id IN NUMBER,
565 p_key_line_id IN NUMBER,
566 p_adjustment_level IN VARCHAR2,
567 x_retro_exists OUT NOCOPY BOOLEAN, --bug3738043
568 x_line_adj_tbl OUT NOCOPY OE_ORDER_PUB.LINE_ADJ_TBL_TYPE) AS
569
570 Cursor last_retrobill_line IS
571 Select max(line_id),
572 max(header_id)
573 From OE_ORDER_LINES_ALL l
574 Where l.order_source_id = 27
575 And l.orig_sys_document_ref = to_char(p_key_header_id) --p_key_header_id --commented for bug#7665009
576 And l.orig_sys_line_ref = to_char(p_key_line_id) --p_key_line_id --commented for bug#7665009
577 And retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID;
578 l_retro_line_id Number;
579 l_retro_header_id Number;
580 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
581 l_query_line_id Number;
582 l_query_header_id Number;
583
584 Begin
585 OPEN last_retrobill_line;
586 Fetch last_retrobill_line Into l_retro_line_id,l_retro_header_id;
587 Close last_retrobill_line;
588
589 IF l_retro_line_id IS NULL THEN
590 --No previous retrobill line perform a reqular query_
591 --IF l_debug_level > 0 THEN
592 oe_debug_pub.add('Retro: no previous retrobill line');
593 --END IF;
594 l_query_line_id := p_key_line_id;
595 l_query_header_id := p_key_header_id;
596 x_retro_exists := FALSE; --bug3738043
597 ELSIF l_retro_line_id IS NOT NULL THEN
598 l_query_line_id := l_retro_line_id;
599 l_query_header_id := l_retro_header_id;
600 x_retro_exists := TRUE; --bug3738043
601 --IF l_debug_level > 0 THEN
602 oe_debug_pub.add('Retro:Previoulsy Retrobilled, Get Most Recent Adj:retro line id:'||l_retro_line_id);
603 --END IF;
604 End IF;
605
606
607 IF p_adjustment_level = 'HEADER' THEN
608 oe_debug_pub.add('RETRO:QUERY Order level adjustment');
609 OE_Line_Adj_Util.Query_Rows(p_header_Id => l_query_header_id
610 ,x_Line_Adj_Tbl => x_Line_Adj_Tbl);
611 ELSE
612 oe_debug_pub.add('RETRO:QUERY line level adjustment');
613 OE_Line_Adj_Util.Query_Rows(p_line_Id => l_query_line_id
614 ,x_Line_Adj_Tbl => x_Line_Adj_Tbl);
615 END IF;
616
617 Exception
618 When Others Then
619 oe_debug_pub.add('Retro:'||SQLERRM);
620 END;
621
622 /******************************************************************
623 To get a invoice id of the original line
624 *******************************************************************/
625 Function Get_Credit_Invoice_Line_Id(p_order_number IN NUMBER,
626 p_order_type_id IN NUMBER,
627 p_line_id IN NUMBER)
628 Return Number As
629
630 Cursor Get_CI_Line_Id Is
631 Select customer_trx_line_id --customer_trx_line_id is the invoice_id
632 From ra_customer_trx_lines_all a,
633 oe_transaction_types_tl b
634 Where INTERFACE_LINE_CONTEXT= 'ORDER ENTRY'
635 and INTERFACE_LINE_ATTRIBUTE1=to_char(p_order_number) ----bug5138249
636 and INTERFACE_LINE_ATTRIBUTE2=b.name
637 and INTERFACE_LINE_ATTRIBUTE6=to_char(p_line_id) --bug5138249
638 and INTERFACE_LINE_ATTRIBUTE11 = '0'
639 and a.org_id = (select org_id from oe_order_lines_all where line_id = p_line_id) --bug# 8448816
640 and b.transaction_type_id = p_order_type_id
641 and b.language = (select language_code from fnd_languages where installed_flag = 'B');
642
643 --CONTEXT,ATTRIBUTE1,ATTRIBUTE2 and ATTRIBUTE6 will hit a concatenate index in AR table
644 l_ci_id NUMBER;
645 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
646 Begin
647 --should not be multiple, just in case
648 For i IN Get_CI_Line_Id Loop
649 l_ci_id:=i.customer_trx_line_id;
650 End Loop;
651
652 If l_debug_level > 0 Then
653 oe_debug_pub.add('Retro:Leaving get credit invoice id:'||l_ci_id);
654 End If;
655
656 Return l_ci_id;
657
658 End;
659
660 /******************************************************************
661 --This is mainly for preview again or reprice of retrobill lines scenario.
662 --Before calling pricing engine this procedure will be called from
663 --OEXVOPRB.pls oe_order_price_pvt.
664 --First delete the offset adjustments and then update the current
665 --adjustment with the most recent retrobill adjustment if the
666 --line has been retrobilled (either previewed or executed) before.
667 --After calling pricing engine, a new offset adj will be created
668 --this offset will be most_recent_retrobill_adj - adj_returned_by_pricing engine.
669 --And the we will again update the original adjustment
670 ******************************************************************/
671
672 Procedure Preprocess_Adjustments(p_orig_sys_document_ref IN NUMBER
673 ,p_orig_sys_line_ref IN NUMBER
674 ,p_header_id IN NUMBER --bug3738043
675 ,p_line_id IN NUMBER) As
676 --bug3738043 start
677 CURSOR retro_list_line_ids IS
678 SELECT price_adjustment_id, list_line_id
679 FROM oe_price_adjustments
680 WHERE line_id = p_line_id
681 AND retrobill_request_id IS NOT NULL
682 AND list_line_type_code IN ('DIS', 'SUR', 'PBH');
683
684 cursor retro_inv_list_line_id(p_list_line_id in number, p_line_id in number) is
685 SELECT list_line_id
686 FROM oe_price_adjustments
687 WHERE line_id = p_line_id
688 AND retrobill_request_id IS NOT NULL
689 AND list_line_type_code IN ('DIS', 'SUR', 'PBH')
690 AND list_line_id = p_list_line_id
691 AND line_id = p_line_id;
692 --bug3738043 end
693
694 l_line_adj_tbl OE_ORDER_PUB.LINE_ADJ_TBL_TYPE;
695 i PLS_INTEGER;
696 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
697 --bug3738043 start
698 l_new_price_adj_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
699 k PLS_INTEGER := 1 ;
700 l_found BOOLEAN := FALSE;
701 l_dummy NUMBER;
702 l_retro_exists BOOLEAN;
703 --bug3738043 end
704 Begin
705
706 if l_debug_level > 0 then
707 oe_debug_pub.add('Entering oe_retro_pvt.preprocess_adjustments');
708 end if;
709
710 --bug3738043 start
711 IF l_debug_level > 0 THEN
712 oe_debug_pub.add('PVIPRANA: retrobill header_id: ' || p_header_id);
713 oe_debug_pub.add('PVIPRANA: retrobill line_id: ' || p_line_id);
714 END IF;
715 --bug3738043 end
716
717 DELETE From OE_PRICE_ADJUSTMENTS
718 WHERE line_id = p_line_id
719 AND retrobill_request_id IS NULL
720 AND list_line_type_code <> 'TAX';
721
722 Get_Most_Recent_Retro_Adj
723 (p_key_header_id =>p_orig_sys_document_ref,
724 p_key_line_id =>p_orig_sys_line_ref,
725 p_adjustment_level =>'LINE',
726 x_retro_exists => l_retro_exists, --bug3738043
727 x_line_adj_tbl=>l_line_adj_tbl);
728
729 i:=l_line_adj_tbl.first;
730 WHILE I IS NOT NULL LOOP
731
732 if l_debug_level > 0 then
733 oe_debug_pub.add(' list_line_no: ' || l_line_adj_tbl(i).list_line_no || ' list_line_id: ' || l_line_adj_tbl(i).list_line_id);
734 oe_debug_pub.add(' operand queried:'||l_line_adj_tbl(i).operand||' operand_perpqty:'||l_line_adj_tbl(i).operand_per_pqty);
735 oe_debug_pub.add(' list_line_type_code:'||l_line_adj_tbl(i).list_line_type_code||'applied_flag:'||l_line_adj_tbl(i).applied_flag); oe_debug_pub.add(' adjusted amount:'||l_line_adj_tbl(i).adjusted_amount);
736 oe_debug_pub.add(' adj_amt_pqty:'||l_line_adj_tbl(i).adjusted_amount_per_pqty);
737 oe_debug_pub.add(' retrobill_request_id: '||l_line_adj_tbl(i).retrobill_request_id);
738 end if;
739 --bug3738043 adding the following condition
740 IF (l_retro_exists AND l_line_adj_tbl(i).retrobill_request_id IS NOT NULL) OR
741 NOT l_retro_exists THEN
742 UPDATE OE_PRICE_ADJUSTMENTS
743 SET operand = l_line_adj_tbl(i).operand,
744 operand_per_pqty = l_line_adj_tbl(i).operand_per_pqty,
745 adjusted_amount = l_line_adj_tbl(i).adjusted_amount,
746 adjusted_amount_per_pqty = l_line_adj_tbl(i).adjusted_amount_per_pqty,
747 applied_flag = 'Y'
748 WHERE line_id = p_line_id
749 AND retrobill_request_id IS NOT NULL
750 AND list_line_id = l_line_adj_tbl(i).list_line_id;
751
752 --bug3738043 begin
753 --narrowing down the scope to avoid regression, only phase_count > 0 will activate this code
754 -- This is to insert to adjustments which are present in original line or previous retrobill line but currently invalid in the pricing set up
755 IF nvl(G_RETRO_PRICING_PHASE_COUNT,0) > 0 THEN
756 OPEN retro_inv_list_line_id(l_line_adj_tbl(i).list_line_id,p_line_id);
757 FETCH retro_inv_list_line_id into l_dummy;
758 IF retro_inv_list_line_id%NOTFOUND THEN
759 IF l_line_adj_tbl(i).LIST_LINE_TYPE_CODE IN ('DIS', 'SUR', 'PBH') THEN
760 IF l_debug_level > 0 THEN
761 oe_debug_pub.add('PVIPRANA: Inserting Invalid list_line_id ' || l_line_adj_tbl(i).list_line_id);
762 END IF;
763
764 l_line_adj_tbl(i).line_id := p_line_id;
765 l_line_adj_tbl(i).header_id := p_header_id;
766 l_line_adj_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
767
768 SELECT Oe_Price_Adjustments_S.Nextval
769 INTO l_line_adj_tbl(i).price_adjustment_id
770 FROM dual;
771
772 OE_LINE_ADJ_UTIL.Insert_Row(l_line_adj_tbl(i));
773 END IF; --end check for 'DIS', 'PBH' and 'SUR'
774 END IF;
775
776 close retro_inv_list_line_id;
777 End If;
778 END IF; --check for retrobill_request_id NOT NULL
779 --bug3738043 end
780 i:= l_line_adj_tbl.next(i);
781 END LOOP;
782
783 --bug3738043 start
784 IF l_debug_level > 0 THEN
785 oe_debug_pub.add('PVIPRANA: G_RETRO_PRICING_PHASE_COUNT is ' || G_RETRO_PRICING_PHASE_COUNT);
786 END IF;
787
788 IF nvl(G_RETRO_PRICING_PHASE_COUNT,0) > 0 THEN
789 IF l_debug_level > 0 THEN
790 oe_debug_pub.add('PVIPRANA: deleting the new adjustments with retrobill_request_id NOT null');
791 END IF;
792
793
794 FOR retro_list_line_id IN retro_list_line_ids LOOP
795 I:=l_line_adj_tbl.first;
796 l_found := FALSE;
797 WHILE I IS NOT NULL LOOP
798 IF retro_list_line_id.list_line_id = l_line_adj_tbl(i).list_line_id AND
799 --bug3749627 added the following condition
800 ((l_retro_exists AND l_line_adj_tbl(i).retrobill_request_id IS NOT NULL) OR
801 NOT l_retro_exists )THEN
802 l_found := TRUE;
803 EXIT;
804 ELSE
805 i:= l_line_adj_tbl.next(i);
806 END IF;
807 END LOOP;
808
809 IF NOT l_found THEN
810 l_new_price_adj_id_tbl(k) := retro_list_line_id.price_adjustment_id;
811 k := k+1;
812 IF l_debug_level > 0 THEN
813 oe_debug_pub.add('PVIPRANA: new list_line_id ' || retro_list_line_id.list_line_id);
814 END IF;
815 END IF;
816
817 END LOOP;
818
819
820 IF l_debug_level > 0 THEN
821 oe_debug_pub.add('PVIPRANA: Deleting ' || (k-1) || ' new adjustments');
822 END IF;
823
824 IF l_new_price_adj_id_tbl.FIRST IS NOT NULL THEN
825
826 FORALL i IN l_new_price_adj_id_tbl.FIRST..l_new_price_adj_id_tbl.LAST
827 DELETE FROM oe_price_adjustments
828 WHERE price_adjustment_id=l_new_price_adj_id_tbl(i);
829
830 END IF;
831
832 END IF;
833 --bug3738043 end
834
835 if l_debug_level > 0 then
836 oe_debug_pub.add('Leaving oe_retro_pvt.preprocess_adjustments');
837 end if;
838 --bug3738043
839 Exception
840 when others then
841 oe_debug_pub.add('Exception in Preprocess Adjustments ' || SQLERRM);
842
843 End;
844
845 /***************************************************************
846 This function returns quantity which is still eligible for the
847 purpose of retrobilling.
848 Input: Line Id to be retrobilled.
849 Curr Ordered Qty, current ordered quantity for the line to be retrobilled.
850 Output: Quantity that are eligible for retrobilling.
851 ****************************************************************/
852 Function Get_Retrobillable_Qty(p_line_id Number,
853 p_curr_ordered_qty Number) Return Number As
854 Cursor Return_Lines Is
855 Select sum(ordered_quantity)
856 From Oe_Order_Lines_All
857 Where reference_line_id = p_line_id
858 And line_category_code = 'RETURN';
859
860
861 l_qty Number:=0;
862 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
863
864 Begin
865
866 Open Return_Lines;
867 Fetch Return_Lines Into l_qty;
868 Close Return_Lines;
869
870 If nvl(l_qty,0) = 0 Then
871 --all curr qty are eligible for returns
872 Return p_curr_ordered_qty;
873 Else
874 If l_debug_level > 0 Then
875 oe_debug_pub.add('Retro:Returned Qty:'||l_qty);
876 oe_debug_pub.add('Retro:Retrobillable Qty:'|| p_curr_ordered_qty - l_qty);
877 End If;
878 Return p_curr_ordered_qty - l_qty;
879 End If;
880
881 Exception
882 When NO_DATA_FOUND Then
883 oe_debug_pub.add('Retro:No returns against line_id:'||p_line_id);
884 Return p_curr_ordered_qty;
885 End;
886
887 /*************************************************************
888 This procdures update oe_retrobill_requests table
889 *************************************************************/
890 Procedure Update_Row(p_retrobill_request_rec IN OE_RETROBILL_REQUESTS%ROWTYPE) AS
891 Begin
892 oe_debug_pub.add('Retro:Entering Update retrobill request,request_id:'||p_retrobill_request_rec.retrobill_request_id);
893 UPDATE OE_RETROBILL_REQUESTS
894 SET
895 NAME = p_retrobill_request_rec.name
896 , DESCRIPTION = p_retrobill_request_rec.description
897 , EXECUTION_MODE= p_retrobill_request_rec.execution_mode
898 , ORDER_TYPE_ID = p_retrobill_request_rec.order_type_id
899 , RETROBILL_REASON_CODE= p_retrobill_request_rec.retrobill_reason_code
900 , EXECUTION_DATE= nvl(p_retrobill_request_rec.execution_date,SYSDATE)
901 , INVENTORY_ITEM_ID= p_retrobill_request_rec.inventory_item_id
902 , SOLD_TO_ORG_ID = p_retrobill_request_rec.sold_to_org_id
903 , CREATION_DATE = nvl(p_retrobill_request_rec.creation_date,SYSDATE)
904 , CREATED_BY = nvl(p_retrobill_request_rec.created_by,fnd_global.user_id)
905 , LAST_UPDATE_DATE = nvl(p_retrobill_request_rec.last_update_date,SYSDATE)
906 , LAST_UPDATED_BY = nvl(p_retrobill_request_rec.last_updated_by,fnd_global.user_id)
907 , LAST_UPDATE_LOGIN= nvl(p_retrobill_request_rec.last_update_login,fnd_global.login_id)
908 , REQUEST_ID = p_retrobill_request_rec.request_id
909 , PROGRAM_APPLICATION_ID= nvl(p_retrobill_request_rec.program_application_id,fnd_global.prog_appl_id)
910 , PROGRAM_ID = p_retrobill_request_rec.program_id
911 , PROGRAM_UPDATED_DATE= p_retrobill_request_rec.program_updated_date
912 Where retrobill_request_id = p_retrobill_request_rec.retrobill_request_id;
913
914 oe_debug_pub.add('Retro:Leaving Update retrobill request:'||SQL%ROWCOUNT||' updated');
915 Exception
916 When Others Then
917 oe_debug_pub.add('Retro:Update_Row:'||SQLERRM);
918 End;
919
920 /**************************************************************
921 This procedure calls process order api to create headers and lines
922 ***************************************************************/
923 Procedure Call_Process_Order(p_header_rec In Oe_Order_Pub.Header_Rec_Type,
924 p_line_tbl In Oe_Order_Pub.Line_Tbl_Type,
925 p_Line_price_Att_tbl IN Oe_Order_Pub.Line_Price_Att_Tbl_Type, -- 8736629
926 x_created_header_id Out NOCOPY Number,
927 x_return_status Out NOCOPY Varchar2) As
928
929 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
930 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
931 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
932 --l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type; -- 8736629
933 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
934 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
935 l_x_header_rec OE_Order_PUB.Header_Rec_Type;
936 l_line_rec OE_Order_PUB.Line_Rec_Type;
937 l_line_adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
938 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
939 l_line_adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
940 l_x_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
941 l_x_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
942 l_x_line_tbl OE_Order_PUB.Line_Tbl_Type;
943 l_x_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
944 l_Line_Scredit_out_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
945 l_action_request_out_tbl OE_Order_PUB.request_tbl_type;
946 l_lot_serial_tbl OE_Order_PUB.lot_serial_tbl_type;
947 l_x_Line_price_Att_tbl Oe_Order_Pub.Line_Price_Att_Tbl_Type; -- 8736629
948 l_return_status Varchar2(30);
949 l_file_val Varchar2(30);
950 x_msg_count number;
951 x_msg_data Varchar2(2000);
952 x_msg_index number;
953 v Varchar2(30);
954 Begin
955
956 l_x_line_tbl := p_line_tbl;
957 l_x_header_rec:=p_header_rec;
958 l_x_Line_price_Att_tbl := p_Line_price_Att_tbl; --8736629
959 OE_Order_PVT.Process_order
960 ( p_api_version_number => 1.0
961 , p_init_msg_list => FND_API.G_FALSE
962 , x_return_status => x_return_status
963 , x_msg_count => x_msg_count
964 , x_msg_data => x_msg_data
965 , p_validation_level => OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
966 -- , p_validation_level => FND_API.G_VALID_LEVEL_NONE
967 , p_x_header_rec => l_x_header_rec
968 , p_x_Header_Adj_tbl => l_x_Header_Adj_tbl
969 , p_x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
970 , p_x_line_tbl => l_x_line_tbl
971 , p_x_Line_Adj_tbl => l_Line_Adj_tbl
972 , p_x_Line_Scredit_tbl => l_Line_Scredit_out_tbl
973 , p_x_Action_Request_tbl => l_Action_Request_out_Tbl
974 , p_x_lot_serial_tbl => l_lot_serial_tbl
975 ,p_x_Header_price_Att_tbl => l_Header_price_Att_tbl
976 ,p_x_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl
977 ,p_x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl
978 --,p_x_Line_price_Att_tbl => l_Line_price_Att_tbl -- 8736629
979 ,p_x_Line_price_Att_tbl => l_x_Line_price_Att_tbl -- 8736629
980 ,p_x_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
981 ,p_x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
982 );
983
984 x_created_header_id:=l_x_header_rec.header_id;
985
986 oe_debug_pub.add('no. of OE messages :'||x_msg_count);
987 for k in 1 .. x_msg_count loop
988 x_msg_data := oe_msg_pub.get( p_msg_index => k,
989 p_encoded => 'F'
990 );
991 oe_debug_pub.add(substr(x_msg_data,1,255));
992 oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
993 end loop;
994
995 fnd_msg_pub.count_and_get( p_encoded => 'F'
996 , p_count => x_msg_count
997 , p_data => x_msg_data);
998 --oe_debug_pub.add('no. of FND messages :'||x_msg_count,1);
999 oe_debug_pub.add('no. of FND messages :'||x_msg_count);
1000 for k in 1 .. x_msg_count loop
1001 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
1002 p_encoded => 'F'
1003 );
1004 oe_debug_pub.add('Error msg: '||substr(x_msg_data,1,200));
1005 oe_debug_pub.add(substr(x_msg_data,1,255));
1006 end loop;
1007
1008 oe_debug_pub.add('header id created:'||l_x_header_rec.header_id);
1009
1010 End;
1011
1012
1013 /**************************************************************
1014 This procedure preprocess a retrobill line before passing it to
1015 process order.
1016 ***************************************************************/
1017 Procedure Prepare_Line(p_oline_rec In GROUP_LINES%ROWTYPE,
1018 p_retrobill_tbl In RETROBILL_TBL_TYPE,
1019 p_retrobill_request_rec In OE_RETROBILL_REQUESTS%ROWTYPE,
1020 x_line_rec Out NOCOPY OE_ORDER_PUB.LINE_REC_TYPE) AS
1021 l_retrobillable_qty NUMBER;
1022 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1023 stmt NUMBER:=7.1;
1024 Begin
1025 --Copy Original Attributes to retrobill line
1026 x_line_rec.org_id :=p_oline_rec.org_id ;
1027 x_line_rec.ORDERED_ITEM :=p_oline_rec.ORDERED_ITEM ;
1028 --x_line_rec.request_date :=p_oline_rec.request_date ;
1029 x_line_rec.ORDER_QUANTITY_UOM :=p_oline_rec.ORDER_QUANTITY_UOM ;
1030 --x_line_rec.DELIVERY_LEAD_TIME :=p_oline_rec.DELIVERY_LEAD_TIME ;
1031 stmt:=7.2;
1032 x_line_rec.TAX_EXEMPT_FLAG :=p_oline_rec.TAX_EXEMPT_FLAG ;
1033 x_line_rec.TAX_EXEMPT_NUMBER :=p_oline_rec.TAX_EXEMPT_NUMBER ;
1034 x_line_rec.TAX_EXEMPT_REASON_CODE :=p_oline_rec.TAX_EXEMPT_REASON_CODE ;
1035 x_line_rec.TAX_DATE :=p_oline_rec.TAX_DATE ;
1036 x_line_rec.TAX_CODE :=p_oline_rec.TAX_CODE ;
1037 x_line_rec.TAX_RATE :=p_oline_rec.TAX_RATE ;
1038 stmt:=7.3;
1039 x_line_rec.SHIP_FROM_ORG_ID :=p_oline_rec.SHIP_FROM_ORG_ID ;
1040 x_line_rec.SHIP_TO_ORG_ID :=p_oline_rec.SHIP_TO_ORG_ID ;
1041 x_line_rec.INVOICE_TO_ORG_ID :=p_oline_rec.INVOICE_TO_ORG_ID ;
1042 x_line_rec.DELIVER_TO_ORG_ID :=p_oline_rec.DELIVER_TO_ORG_ID ;
1043 x_line_rec.SHIP_TO_CONTACT_ID :=p_oline_rec.SHIP_TO_CONTACT_ID ;
1044 x_line_rec.DELIVER_TO_CONTACT_ID :=p_oline_rec.DELIVER_TO_CONTACT_ID ;
1045 x_line_rec.INVOICE_TO_CONTACT_ID :=p_oline_rec.INVOICE_TO_CONTACT_ID ;
1046 --x_line_rec.INTMED_SHIP_TO_ORG_ID :=p_oline_rec.INTMED_SHIP_TO_ORG_ID ;
1047 --x_line_rec.INTMED_SHIP_TO_CONTACT_ID :=p_oline_rec.INTMED_SHIP_TO_CONTACT_ID ;
1048 x_line_rec.SOLD_FROM_ORG_ID :=p_oline_rec.SOLD_FROM_ORG_ID ;
1049 x_line_rec.sold_to_org_id :=p_oline_rec.sold_to_org_id1 ;
1050 x_line_rec.CUST_PO_NUMBER :=p_oline_rec.CUST_PO_NUMBER ;
1051 --x_line_rec.VEH_CUS_ITEM_CUM_KEY_ID :=p_oline_rec.VEH_CUS_ITEM_CUM_KEY_ID ;
1052 --x_line_rec.RLA_SCHEDULE_TYPE_CODE :=p_oline_rec.RLA_SCHEDULE_TYPE_CODE ;
1053 --x_line_rec.CUSTOMER_DOCK_CODE :=p_oline_rec.CUSTOMER_DOCK_CODE ;
1054 --x_line_rec.CUSTOMER_JOB :=p_oline_rec.CUSTOMER_JOB ;
1055 --x_line_rec.CUSTOMER_PRODUCTION_LINE :=p_oline_rec.CUSTOMER_PRODUCTION_LINE ;
1056 --x_line_rec.CUST_MODEL_SERIAL_NUMBER :=p_oline_rec.CUST_MODEL_SERIAL_NUMBER ;
1057 x_line_rec.PROJECT_ID :=p_oline_rec.PROJECT_ID ;
1058 x_line_rec.TASK_ID :=p_oline_rec.TASK_ID ;
1059 x_line_rec.INVENTORY_ITEM_ID :=p_oline_rec.INVENTORY_ITEM_ID ;
1060 --x_line_rec.DEMAND_CLASS_CODE :=p_oline_rec.DEMAND_CLASS_CODE ;
1061 x_line_rec.PRICE_LIST_ID :=p_oline_rec.PRICE_LIST_ID ;
1062 x_line_rec.agreement_id :=p_oline_rec.agreement_id ;
1063 x_line_rec.PAYMENT_TERM_ID :=p_oline_rec.PAYMENT_TERM_ID ;
1064 x_line_rec.INVOICING_RULE_ID :=p_oline_rec.INVOICING_RULE_ID ;
1065 x_line_rec.ACCOUNTING_RULE_ID :=p_oline_rec.ACCOUNTING_RULE_ID ;
1066 --x_line_rec.SOURCE_DOCUMENT_TYPE_ID :=p_oline_rec.SOURCE_DOCUMENT_TYPE_ID ;
1067 x_line_rec.ORIG_SYS_DOCUMENT_REF :=p_oline_rec.ORIG_SYS_DOCUMENT_REF ;
1068 x_line_rec.SOURCE_DOCUMENT_ID :=p_oline_rec.SOURCE_DOCUMENT_ID ;
1069 x_line_rec.ORIG_SYS_LINE_REF :=p_oline_rec.ORIG_SYS_LINE_REF ;
1070 x_line_rec.SOURCE_DOCUMENT_LINE_ID :=p_oline_rec.SOURCE_DOCUMENT_LINE_ID ;
1071 x_line_rec.ITEM_REVISION :=p_oline_rec.ITEM_REVISION ;
1072 x_line_rec.TAX_VALUE :=p_oline_rec.TAX_VALUE ;
1073 --x_line_rec.TOP_MODEL_LINE_ID :=p_oline_rec.TOP_MODEL_LINE_ID ;
1074 --x_line_rec.LINK_TO_LINE_ID :=p_oline_rec.LINK_TO_LINE_ID ;
1075 --x_line_rec.COMPONENT_SEQUENCE_ID :=p_oline_rec.COMPONENT_SEQUENCE_ID ;
1076 --x_line_rec.COMPONENT_CODE :=p_oline_rec.COMPONENT_CODE ;
1077 --x_line_rec.CONFIG_DISPLAY_SEQUENCE :=p_oline_rec.CONFIG_DISPLAY_SEQUENCE ;
1078 --x_line_rec.SORT_ORDER :=p_oline_rec.SORT_ORDER ;
1079 x_line_rec.ITEM_TYPE_CODE :=p_oline_rec.ITEM_TYPE_CODE ;
1080 --x_line_rec.OPTION_FLAG :=p_oline_rec.OPTION_FLAG ;
1081 --x_line_rec.ACTUAL_ARRIVAL_DATE :=p_oline_rec.ACTUAL_ARRIVAL_DATE ;
1082 --x_line_rec.ATO_LINE_ID :=p_oline_rec.ATO_LINE_ID ;
1083 --x_line_rec.AUTO_SELECTED_QUANTITY :=p_oline_rec.AUTO_SELECTED_QUANTITY ;
1084 --x_line_rec.COMPONENT_NUMBER :=p_oline_rec.COMPONENT_NUMBER ;
1085 --x_line_rec.EARLIEST_ACCEPTABLE_DATE :=p_oline_rec.EARLIEST_ACCEPTABLE_DATE ;
1086 x_line_rec.EXPLOSION_DATE :=p_oline_rec.EXPLOSION_DATE ;
1087 x_line_rec.LATEST_ACCEPTABLE_DATE :=p_oline_rec.LATEST_ACCEPTABLE_DATE ;
1088 x_line_rec.MODEL_GROUP_NUMBER :=p_oline_rec.MODEL_GROUP_NUMBER ;
1089 x_line_rec.SHIP_MODEL_COMPLETE_FLAG :=p_oline_rec.SHIP_MODEL_COMPLETE_FLAG ;
1090 --bug5114210 commenting the following
1091 --x_line_rec.SCHEDULE_STATUS_CODE :=p_oline_rec.SCHEDULE_STATUS_CODE ;
1092 x_line_rec.SOURCE_TYPE_CODE :=p_oline_rec.SOURCE_TYPE_CODE ;
1093 x_line_rec.SALESREP_ID :=p_oline_rec.SALESREP_ID ;
1094 --x_line_rec.ARRIVAL_SET_ID :=p_oline_rec.ARRIVAL_SET_ID ;
1095 --x_line_rec.SHIP_SET_ID :=p_oline_rec.SHIP_SET_ID ;
1096 --x_line_rec.SPLIT_FROM_LINE_ID :=p_oline_rec.SPLIT_FROM_LINE_ID ;
1097 x_line_rec.CUST_PRODUCTION_SEQ_NUM :=p_oline_rec.CUST_PRODUCTION_SEQ_NUM ;
1098 x_line_rec.ORDERED_ITEM_ID :=p_oline_rec.ORDERED_ITEM_ID ;
1099 x_line_rec.ITEM_IDENTIFIER_TYPE :=p_oline_rec.ITEM_IDENTIFIER_TYPE ;
1100 x_line_rec.CONFIGURATION_ID :=p_oline_rec.CONFIGURATION_ID ;
1101 --x_line_rec.COMMITMENT_ID :=p_oline_rec.COMMITMENT_ID ;
1102 x_line_rec.CREDIT_INVOICE_LINE_ID :=p_oline_rec.CREDIT_INVOICE_LINE_ID ;
1103 --x_line_rec.FIRST_ACK_CODE :=p_oline_rec.FIRST_ACK_CODE ;
1104 --x_line_rec.FIRST_ACK_DATE :=p_oline_rec.FIRST_ACK_DATE ;
1105 --x_line_rec.LAST_ACK_CODE :=p_oline_rec.LAST_ACK_CODE ;
1106 --x_line_rec.LAST_ACK_DATE :=p_oline_rec.LAST_ACK_DATE ;
1107 x_line_rec.ORDER_SOURCE_ID :=p_oline_rec.ORDER_SOURCE_ID ;
1108 x_line_rec.ORIG_SYS_SHIPMENT_REF :=p_oline_rec.ORIG_SYS_SHIPMENT_REF ;
1109 x_line_rec.CHANGE_SEQUENCE :=p_oline_rec.CHANGE_SEQUENCE ;
1110 x_line_rec.DROP_SHIP_FLAG :=p_oline_rec.DROP_SHIP_FLAG ;
1111 x_line_rec.CUSTOMER_LINE_NUMBER :=p_oline_rec.CUSTOMER_LINE_NUMBER ;
1112 x_line_rec.CUSTOMER_SHIPMENT_NUMBER :=p_oline_rec.CUSTOMER_SHIPMENT_NUMBER ;
1113 x_line_rec.CUSTOMER_ITEM_NET_PRICE :=p_oline_rec.CUSTOMER_ITEM_NET_PRICE ;
1114 x_line_rec.CUSTOMER_PAYMENT_TERM_ID :=p_oline_rec.CUSTOMER_PAYMENT_TERM_ID ;
1115 x_line_rec.FULFILLED_FLAG :=p_oline_rec.FULFILLED_FLAG ;
1116 x_line_rec.END_ITEM_UNIT_NUMBER :=p_oline_rec.END_ITEM_UNIT_NUMBER ;
1117 x_line_rec.CONFIG_HEADER_ID :=p_oline_rec.CONFIG_HEADER_ID ;
1118 x_line_rec.CONFIG_REV_NBR :=p_oline_rec.CONFIG_REV_NBR ;
1119 x_line_rec.MFG_COMPONENT_SEQUENCE_ID :=p_oline_rec.MFG_COMPONENT_SEQUENCE_ID ;
1120 x_line_rec.REFERENCE_CUSTOMER_TRX_LINE_ID :=p_oline_rec.REFERENCE_CUSTOMER_TRX_LINE_ID ;
1121 --x_line_rec.SPLIT_BY :=p_oline_rec.SPLIT_BY ;
1122 --x_line_rec.LINE_SET_ID :=p_oline_rec.LINE_SET_ID ;
1123 x_line_rec.SERVICE_TXN_REASON_CODE :=p_oline_rec.SERVICE_TXN_REASON_CODE ;
1124 x_line_rec.SERVICE_TXN_COMMENTS :=p_oline_rec.SERVICE_TXN_COMMENTS ;
1125 x_line_rec.SERVICE_DURATION :=p_oline_rec.SERVICE_DURATION ;
1126 x_line_rec.SERVICE_START_DATE :=p_oline_rec.SERVICE_START_DATE ;
1127 x_line_rec.SERVICE_END_DATE :=p_oline_rec.SERVICE_END_DATE ;
1128 x_line_rec.SERVICE_COTERMINATE_FLAG :=p_oline_rec.SERVICE_COTERMINATE_FLAG ;
1129 x_line_rec.UNIT_LIST_PERCENT :=p_oline_rec.UNIT_LIST_PERCENT ;
1130 x_line_rec.UNIT_SELLING_PERCENT :=p_oline_rec.UNIT_SELLING_PERCENT ;
1131 x_line_rec.REVENUE_AMOUNT :=p_oline_rec.REVENUE_AMOUNT ;
1132 x_line_rec.UNIT_PERCENT_BASE_PRICE :=p_oline_rec.UNIT_PERCENT_BASE_PRICE ;
1133 x_line_rec.SERVICE_NUMBER :=p_oline_rec.SERVICE_NUMBER ;
1134 x_line_rec.SERVICE_PERIOD :=p_oline_rec.SERVICE_PERIOD ; stmt:=7.4;
1135 x_line_rec.SHIPPABLE_FLAG :=p_oline_rec.SHIPPABLE_FLAG ;
1136 x_line_rec.MODEL_REMNANT_FLAG :=p_oline_rec.MODEL_REMNANT_FLAG ;
1137 x_line_rec.RE_SOURCE_FLAG :=p_oline_rec.RE_SOURCE_FLAG ;
1138 x_line_rec.FULFILLMENT_METHOD_CODE :=p_oline_rec.FULFILLMENT_METHOD_CODE ;
1139 x_line_rec.MARKETING_SOURCE_CODE_ID :=p_oline_rec.MARKETING_SOURCE_CODE_ID ;
1140 x_line_rec.SERVICE_REFERENCE_TYPE_CODE :=p_oline_rec.SERVICE_REFERENCE_TYPE_CODE ;
1141 x_line_rec.SERVICE_REFERENCE_LINE_ID :=p_oline_rec.SERVICE_REFERENCE_LINE_ID ;
1142 x_line_rec.SERVICE_REFERENCE_SYSTEM_ID :=p_oline_rec.SERVICE_REFERENCE_SYSTEM_ID ;
1143 --x_line_rec.FULFILLMENT_DATE :=p_oline_rec.FULFILLMENT_DATE ;
1144 x_line_rec.PREFERRED_GRADE :=p_oline_rec.PREFERRED_GRADE ;
1145 x_line_rec.ORDERED_QUANTITY2 :=p_oline_rec.ORDERED_QUANTITY2 ; --Bug#12425758
1146 --not copying the ORDERED_QUANTITY2 from reference line so that corrcet ORDERED_QUANTITY2 is calculated and populated
1147 x_line_rec.ORDERED_QUANTITY_UOM2 :=p_oline_rec.ORDERED_QUANTITY_UOM2 ;
1148 --x_line_rec.CANCELLED_QUANTITY2 :=p_oline_rec.CANCELLED_QUANTITY2 ;
1149 --x_line_rec.FULFILLED_QUANTITY2 :=p_oline_rec.FULFILLED_QUANTITY2 ;
1150 x_line_rec.MFG_LEAD_TIME :=p_oline_rec.MFG_LEAD_TIME ;
1151 x_line_rec.SUBINVENTORY :=p_oline_rec.SUBINVENTORY ;
1152 x_line_rec.UNIT_LIST_PRICE_PER_PQTY :=p_oline_rec.UNIT_LIST_PRICE_PER_PQTY ;
1153 x_line_rec.UNIT_SELLING_PRICE_PER_PQTY :=p_oline_rec.UNIT_SELLING_PRICE_PER_PQTY ;
1154 x_line_rec.UNIT_SELLING_PRICE :=p_oline_rec.UNIT_SELLING_PRICE;
1155 x_line_rec.UNIT_LIST_PRICE :=p_oline_rec.UNIT_LIST_PRICE;
1156 x_line_rec.PRICE_REQUEST_CODE :=p_oline_rec.PRICE_REQUEST_CODE ;
1157 x_line_rec.ORIGINAL_INVENTORY_ITEM_ID :=p_oline_rec.ORIGINAL_INVENTORY_ITEM_ID ;
1158 x_line_rec.ORIGINAL_ORDERED_ITEM_ID :=p_oline_rec.ORIGINAL_ORDERED_ITEM_ID ;
1159 x_line_rec.ORIGINAL_ORDERED_ITEM :=p_oline_rec.ORIGINAL_ORDERED_ITEM ;
1160 x_line_rec.ORIGINAL_ITEM_IDENTIFIER_TYPE :=p_oline_rec.ORIGINAL_ITEM_IDENTIFIER_TYPE ;
1161 x_line_rec.ITEM_SUBSTITUTION_TYPE_CODE :=p_oline_rec.ITEM_SUBSTITUTION_TYPE_CODE ;
1162 --Shipment number need to be redefault?
1163 x_line_rec.SHIPMENT_NUMBER :=p_oline_rec.SHIPMENT_NUMBER;
1164 --x_line_rec.OVERRIDE_ATP_DATE_CODE :=p_oline_rec.OVERRIDE_ATP_DATE_CODE ;
1165 --x_line_rec.LATE_DEMAND_PENALTY_FACTOR :=p_oline_rec.LATE_DEMAND_PENALTY_FACTOR ;
1166 x_line_rec.ACCOUNTING_RULE_DURATION :=p_oline_rec.ACCOUNTING_RULE_DURATION ;
1167 x_line_rec.USER_ITEM_DESCRIPTION :=p_oline_rec.USER_ITEM_DESCRIPTION ;
1168 --x_line_rec.UNIT_COST :=p_oline_rec.UNIT_COST ;
1169 stmt:=7.5;
1170 /*******************************
1171 --Explicitly Set Null Attributes
1172 ********************************/
1173 x_line_rec.shipped_quantity:=NULL;
1174 --x_line_rec.reserve this col is not in line tablex
1175 x_line_rec.shipping_quantity:=NULL;
1176 x_line_rec.shipping_quantity_uom:=NULL;
1177 x_line_rec.actual_shipment_date:=NULL;
1178 x_line_rec.over_ship_reason_code:=NULL;
1179 x_line_rec.over_ship_resolved_flag:=NULL;
1180 x_line_rec.shipping_interfaced_flag:=NULL;
1181 x_line_rec.option_number:=NULL;
1182
1183 -- x_line_rec.Blanket_Number := p_oline_rec.Blanket_Number;--bug8341909 --- bug# 8682469 : Reverted the fix done earlier
1184
1185 /******************************************************
1186 --change values to retrobilling related/specific fields
1187 *******************************************************/
1188 x_line_rec.cancelled_flag:='N';
1189 x_line_rec.item_type_code:='STANDARD';
1190 x_line_rec.pricing_date:=SYSDATE;
1191 x_line_rec.request_date:=SYSDATE;
1192 x_line_rec.pricing_quantity:=NULL;
1193 x_line_rec.orig_sys_document_ref:= to_char(p_oline_rec.header_id); -- p_oline_rec.header_id; --commented for bug#7665009
1194 x_line_rec.orig_sys_line_ref:= to_char(p_oline_rec.line_id); -- p_oline_rec.line_id; --commented for bug#7665009
1195 x_line_rec.calculate_price_flag:='Y';
1196 x_line_rec.operation:= OE_Globals.G_OPR_CREATE;
1197 x_line_rec.flow_status_code:='ENTERED';
1198 --SET line category code as 'RETURN' first because this is the most common case
1199 --line category code will be reset to 'ORDER' by OEXVOPRB.pls if it ends up to be
1200 --not a 'RETURN' line.
1201 stmt:=7.51;
1202 x_line_rec.LINE_CATEGORY_CODE:='RETURN';
1203
1204 --Get quantity that are eligible for retrobill.
1205 --l_retrobillable_qty:=Get_Retrobillable_Qty(p_oline_rec.line_id,p_oline_rec.ordered_quantity);
1206
1207 --If user trying to retrobill more than he is eligible to, set the quantity to eligible qty
1208 /* If p_retrobill_tbl(p_oline_rec.plsql_tbl_index).retrobill_qty > l_retrobillable_qty Then
1209 x_line_rec.ordered_quantity:=l_retrobillable_qty;
1210 If l_debug_level > 0 Then
1211 oe_debug_pub.add('Retro:User tries to retrobill more than eligible. Retrobill Qty:'|| p_retrobill_tbl(p_oline_rec.plsql_tbl_index).retrobill_qty);
1212 End If;
1213 Else*/
1214 oe_debug_pub.add('Retro:plsql index'||p_oline_rec.plsql_tbl_index);
1215 x_line_rec.ordered_quantity:=p_retrobill_tbl(p_oline_rec.plsql_tbl_index).retrobill_qty;
1216 --End If;
1217
1218 x_line_rec.retrobill_request_id:=p_retrobill_request_rec.retrobill_request_id;
1219 --need to do, if reason code is null get it from sys parameter!!!
1220 x_line_rec.Return_Reason_Code:=p_retrobill_request_rec.retrobill_reason_code;
1221
1222 x_line_rec.order_source_id:=G_RETROBILL_ORDER_SOURCE_ID;
1223 --x_line_rec.Source_Document_shipment_ref:=p_retrobill_request_rec.retrobill_request_id;
1224 x_line_rec. ORIG_SYS_SHIPMENT_REF:=p_retrobill_request_rec.retrobill_request_id;
1225 stmt:=7.52;
1226 If l_debug_level > 0 Then
1227 oe_debug_pub.add('Retro:invoiced_quantity:'|| p_oline_rec.invoiced_quantity);
1228 End If;
1229
1230 If p_oline_rec.invoiced_quantity > 0 Then
1231 x_line_rec.credit_invoice_line_id:=Get_Credit_Invoice_Line_Id(p_oline_rec.order_number
1232 ,p_oline_rec.order_type_id
1233 ,p_oline_rec.line_id);
1234 End If;
1235
1236 x_line_rec.Shippable_Flag:='N';
1237 stmt:=7.6;
1238 /******************************************************
1239 Line info need to be validated
1240 *******************************************************/
1241 --We try to copy over the tax code and date, but if it fails
1242 --in validation, we will need to ask the system to default the tax code and date
1243 --if we default, we will to tell users about this.
1244 IF NOT IS_TAX_CODE_VALID(p_header_id=>x_line_rec.header_id,p_line_id=>x_line_rec.line_id,p_tax_code=>x_line_rec.tax_code,p_tax_date=>x_line_rec.tax_date, p_org_id=>x_line_rec.org_id) THEN
1245 oe_debug_pub.add('Retro:Old tax code is no longer valid, redaulting a new one');
1246 x_line_rec.tax_code:=FND_API.G_MISS_CHAR;
1247 stmt:=7.61;
1248 x_line_rec.tax_rate:=FND_API.G_MISS_NUM;
1249 stmt:=7.62;
1250 x_line_rec.tax_date:=FND_API.G_MISS_DATE;
1251 END IF;
1252
1253 /******************************************************
1254 --Line info need to be defaulted
1255 *******************************************************/
1256 x_line_rec.LINE_TYPE_ID:=FND_API.G_MISS_NUM;
1257 stmt:=7.63;
1258 x_line_rec.line_id:=FND_API.G_MISS_NUM;
1259 stmt:=7.64;
1260 x_line_rec.line_number:=FND_API.G_MISS_NUM;
1261 stmt:=7.65;
1262 x_line_rec.open_flag:=FND_API.G_MISS_CHAR;
1263 stmt:=7.66;
1264 x_line_rec.booked_flag:=FND_API.G_MISS_CHAR;
1265 stmt:=7.67;
1266
1267 -- x_line_rec.ship_to_org_id:=FND_API.G_MISS_NUM; -- Commented for bug 5612169
1268
1269 If l_debug_level > 0 Then
1270 oe_debug_pub.add('Retro: leaving prepare line');
1271 End If;
1272
1273 Exception
1274 When Others then
1275 oe_debug_pub.add('Exception occured at statement:'||stmt||':'||SQLERRM);
1276 raise;
1277 End;
1278
1279 PROCEDURE Prepare_Header(p_cust_po_number IN VARCHAR2, -- Bug# 6603714
1280 p_sold_to_org_id IN NUMBER,
1281 p_transaction_curr_code IN VARCHAR2,
1282 p_conversion_type_code IN VARCHAR2,
1283 p_retrobill_request_rec IN OUT NOCOPY OE_RETROBILL_REQUESTS%ROWTYPE,
1284 x_header_rec OUT NOCOPY OE_ORDER_PUB.HEADER_REC_TYPE) AS
1285 l_order_type_id Number;
1286 l_pl_tbl QP_UTIL_PUB.price_list_tbl;
1287 l_valid_price_list_id Number;
1288 --internal private profile for debugging only.
1289 G_INT_USE_ANY_VALID_PL Varchar2(3);
1290 Begin
1291 --GSCC (not initializing during declaration)
1292 G_INT_USE_ANY_VALID_PL := nvl(FND_PROFILE.VALUE('ONT_USE_ANY_VALID_PL'),'N');
1293 x_header_rec.sold_to_org_id := p_sold_to_org_id;
1294 x_header_rec.transactional_curr_code:=p_transaction_curr_code;
1295 x_header_rec.conversion_type_code:=p_conversion_type_code;
1296 x_header_rec.operation :=OE_Globals.G_OPR_CREATE;
1297 x_header_rec.ordered_date :=SYSDATE;
1298 --Should pric list get defaulted? Or just plain copy?
1299 x_header_rec.price_list_id :=FND_API.G_MISS_NUM;
1300 x_header_rec.header_id :=FND_API.G_MISS_NUM;
1301 x_header_rec.version_number :=1;
1302 x_header_rec.invoice_to_org_id :=FND_API.G_MISS_NUM;
1303 x_header_rec.open_flag :='Y';
1304 x_header_rec.booked_flag :='N';
1305 x_header_rec.ship_to_org_id :=FND_API.G_MISS_NUM;
1306 x_header_rec.tax_exempt_flag :=FND_API.G_MISS_CHAR;
1307 x_header_rec.tax_exempt_number :=FND_API.G_MISS_CHAR;
1308 x_header_rec.tax_exempt_reason_code:=FND_API.G_MISS_CHAR;
1309 x_header_rec.payment_term_id :=FND_API.G_MISS_NUM;
1310 x_header_rec.salesrep_id :=FND_API.G_MISS_NUM;
1311 x_header_rec.flow_status_code :='ENTERED';
1312 x_header_rec.order_source_id :=G_RETROBILL_ORDER_SOURCE_ID;
1313 x_header_rec.orig_sys_document_ref :=p_retrobill_request_rec.retrobill_request_id;
1314 x_header_rec.cust_po_number :=p_cust_po_number; -- Bug# 6603714
1315 oe_debug_pub.add('x_header_rec.cust_po_number'||x_header_rec.cust_po_number);
1316
1317 If p_retrobill_request_rec.order_type_id IS NULL Then
1318 null;
1319 --get order type id from sys parameter
1320 --....need to code !!! to populare l_order_type_id based on sys param.
1321 --p_retrobill_request_rec.order_type_id :=l_order_type_id;
1322 Else
1323 l_order_type_id:= p_retrobill_request_rec.order_type_id;
1324 End If;
1325 x_header_rec.order_type_id :=l_order_type_id;
1326
1327 IF G_INT_USE_ANY_VALID_PL = 'Y' THEN
1328 --although price at header doesn't matter for retrobilling because each line
1329 --might have their own price list, however, it will fail in header validation if
1330 --the price list does not valid. We need to assign a valid price list for the header
1331 QP_UTIL_PUB.Get_Price_List(l_currency_code=>p_transaction_curr_code,
1332 l_pricing_effective_date=>SYSDATE,
1333 l_agreement_id=>NULL,
1334 l_price_list_tbl=>l_pl_tbl,
1335 l_sold_to_org_id=>p_sold_to_org_id
1336 );
1337
1338 IF l_pl_tbl.First IS NOT NULL THEN
1339 l_valid_price_list_id := l_pl_tbl(l_pl_tbl.First).price_list_id;
1340 oe_debug_pub.add('RETRO:Valid Price List to be defaulted is:'||l_pl_tbl(l_pl_tbl.First).price_list_id);
1341 ELSE
1342 oe_debug_pub.add('RETRO:ERROR:UNABLE TO DEFAULT A VALID HEADER PRICE LIST');
1343 END IF;
1344 x_header_rec.price_list_id:=l_valid_price_list_id;
1345 END IF; --end if for g_int_use_any_valid_pl
1346
1347 oe_debug_pub.add('Retro:Order Type id:'||x_header_rec.order_type_id);
1348 oe_debug_pub.add('Retro:sold to org id:'||x_header_rec.sold_to_org_id);
1349 oe_debug_pub.add('Retro:currency:'||x_header_rec.transactional_curr_code);
1350 End;
1351
1352 PROCEDURE Insert_Id(p_retrobill_tbl IN RETROBILL_TBL_TYPE) As
1353 l_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
1354 l_value_tbl OE_GLOBALS.NUMBER_TBL_TYPE; --store the index of p_retrobill_tbl for line_id
1355 i NUMBER;
1356 j NUMBER:=1;
1357 Begin
1358
1359 i:=p_retrobill_tbl.first;
1360
1361 WHILE i IS NOT NULL LOOP
1362 l_id_tbl(j):=p_retrobill_tbl(i).original_line_id;
1363 l_value_tbl(j):=i;
1364 i:=p_retrobill_tbl.next(i);
1365 j:=j+1;
1366 END LOOP;
1367
1368 DELETE FROM OM_ID_LIST_TMP;
1369
1370 --USE BULK INSERT TO MAXIMIZE PERFORMANCE
1371 IF l_id_tbl.FIRST IS NOT NULL THEN
1372 FORALL j IN l_id_tbl.FIRST..l_id_tbl.LAST
1373 INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
1374 VALUES (l_id_tbl(j),l_value_tbl(j));
1375 END IF;
1376
1377 Exception
1378 When Others Then
1379 oe_debug_pub.add('Execption occured in Oe_Retrobill_Pvt.Insert_Id:'||SQLERRM);
1380 Raise;
1381 End;
1382
1383 /**************************************************************
1384 This procedure was added for bug 8736629. This procedure prepares
1385 the pricing attributes table for the line level
1386 ***************************************************************/
1387 Procedure Prepare_Line_Pricing_Attribs(p_line_id In NUMBER,
1388 x_Line_price_Att_rec Out NOCOPY Oe_Order_Pub.Line_Price_Att_Rec_Type,
1389 x_row_count OUT NOCOPY NUMBER) AS
1390
1391 l_row_count NUMBER := 0;
1392 Begin
1393
1394 -- query the attributes table OE_ORDER_PRICE_ATTRIBS and assign it to the record structure
1395 oe_debug_pub.add('Enter procedure Oe_Retrobill_Pvt.Prepare_Line_Pricing_Attribs:');
1396
1397 SELECT
1398 pricing_context,
1399 pricing_attribute1,
1400 pricing_attribute2,
1401 pricing_attribute3,
1402 pricing_attribute4,
1403 pricing_attribute5,
1404 pricing_attribute6,
1405 pricing_attribute7,
1406 pricing_attribute8,
1407 pricing_attribute9,
1408 pricing_attribute10,
1409 pricing_attribute11,
1410 pricing_attribute12,
1411 pricing_attribute13,
1412 pricing_attribute14,
1413 pricing_attribute15,
1414 pricing_attribute16,
1415 pricing_attribute17,
1416 pricing_attribute18,
1417 pricing_attribute19,
1418 pricing_attribute20,
1419 pricing_attribute21,
1420 pricing_attribute22,
1421 pricing_attribute23,
1422 pricing_attribute24,
1423 pricing_attribute25,
1424 pricing_attribute26,
1425 pricing_attribute27,
1426 pricing_attribute28,
1427 pricing_attribute29,
1428 pricing_attribute30,
1429 pricing_attribute31,
1430 pricing_attribute32,
1431 pricing_attribute33,
1432 pricing_attribute34,
1433 pricing_attribute35,
1434 pricing_attribute36,
1435 pricing_attribute37,
1436 pricing_attribute38,
1437 pricing_attribute39,
1438 pricing_attribute40,
1439 pricing_attribute41,
1440 pricing_attribute42,
1441 pricing_attribute43,
1442 pricing_attribute44,
1443 pricing_attribute45,
1444 pricing_attribute46,
1445 pricing_attribute47,
1446 pricing_attribute48,
1447 pricing_attribute49,
1448 pricing_attribute50,
1449 pricing_attribute51,
1450 pricing_attribute52,
1451 pricing_attribute53,
1452 pricing_attribute54,
1453 pricing_attribute55,
1454 pricing_attribute56,
1455 pricing_attribute57,
1456 pricing_attribute58,
1457 pricing_attribute59,
1458 pricing_attribute60,
1459 pricing_attribute61,
1460 pricing_attribute62,
1461 pricing_attribute63,
1462 pricing_attribute64,
1463 pricing_attribute65,
1464 pricing_attribute66,
1465 pricing_attribute67,
1466 pricing_attribute68,
1467 pricing_attribute69,
1468 pricing_attribute70,
1469 pricing_attribute71,
1470 pricing_attribute72,
1471 pricing_attribute73,
1472 pricing_attribute74,
1473 pricing_attribute75,
1474 pricing_attribute76,
1475 pricing_attribute77,
1476 pricing_attribute78,
1477 pricing_attribute79,
1478 pricing_attribute80,
1479 pricing_attribute81,
1480 pricing_attribute82,
1481 pricing_attribute83,
1482 pricing_attribute84,
1483 pricing_attribute85,
1484 pricing_attribute86,
1485 pricing_attribute87,
1486 pricing_attribute88,
1487 pricing_attribute89,
1488 pricing_attribute90,
1489 pricing_attribute91,
1490 pricing_attribute92,
1491 pricing_attribute93,
1492 pricing_attribute94,
1493 pricing_attribute95,
1494 pricing_attribute96,
1495 pricing_attribute97,
1496 pricing_attribute98,
1497 pricing_attribute99,
1498 pricing_attribute100,
1499 context,
1500 attribute1,
1501 attribute2,
1502 attribute3,
1503 attribute4,
1504 attribute5,
1505 attribute6,
1506 attribute7,
1507 attribute8,
1508 attribute9,
1509 attribute10,
1510 attribute11,
1511 attribute12,
1512 attribute13,
1513 attribute14,
1514 attribute15,
1515 flex_title ,
1516 override_flag,
1517 lock_control,
1518 orig_sys_atts_ref
1519 INTO
1520 x_Line_price_Att_rec.pricing_context,
1521 x_Line_price_Att_rec.pricing_attribute1,
1522 x_Line_price_Att_rec.pricing_attribute2,
1523 x_Line_price_Att_rec.pricing_attribute3,
1524 x_Line_price_Att_rec.pricing_attribute4,
1525 x_Line_price_Att_rec.pricing_attribute5,
1526 x_Line_price_Att_rec.pricing_attribute6,
1527 x_Line_price_Att_rec.pricing_attribute7,
1528 x_Line_price_Att_rec.pricing_attribute8,
1529 x_Line_price_Att_rec.pricing_attribute9,
1530 x_Line_price_Att_rec.pricing_attribute10,
1531 x_Line_price_Att_rec.pricing_attribute11,
1532 x_Line_price_Att_rec.pricing_attribute12,
1533 x_Line_price_Att_rec.pricing_attribute13,
1534 x_Line_price_Att_rec.pricing_attribute14,
1535 x_Line_price_Att_rec.pricing_attribute15,
1536 x_Line_price_Att_rec.pricing_attribute16,
1537 x_Line_price_Att_rec.pricing_attribute17,
1538 x_Line_price_Att_rec.pricing_attribute18,
1539 x_Line_price_Att_rec.pricing_attribute19,
1540 x_Line_price_Att_rec.pricing_attribute20,
1541 x_Line_price_Att_rec.pricing_attribute21,
1542 x_Line_price_Att_rec.pricing_attribute22,
1543 x_Line_price_Att_rec.pricing_attribute23,
1544 x_Line_price_Att_rec.pricing_attribute24,
1545 x_Line_price_Att_rec.pricing_attribute25,
1546 x_Line_price_Att_rec.pricing_attribute26,
1547 x_Line_price_Att_rec.pricing_attribute27,
1548 x_Line_price_Att_rec.pricing_attribute28,
1549 x_Line_price_Att_rec.pricing_attribute29,
1550 x_Line_price_Att_rec.pricing_attribute30,
1551 x_Line_price_Att_rec.pricing_attribute31,
1552 x_Line_price_Att_rec.pricing_attribute32,
1553 x_Line_price_Att_rec.pricing_attribute33,
1554 x_Line_price_Att_rec.pricing_attribute34,
1555 x_Line_price_Att_rec.pricing_attribute35,
1556 x_Line_price_Att_rec.pricing_attribute36,
1557 x_Line_price_Att_rec.pricing_attribute37,
1558 x_Line_price_Att_rec.pricing_attribute38,
1559 x_Line_price_Att_rec.pricing_attribute39,
1560 x_Line_price_Att_rec.pricing_attribute40,
1561 x_Line_price_Att_rec.pricing_attribute41,
1562 x_Line_price_Att_rec.pricing_attribute42,
1563 x_Line_price_Att_rec.pricing_attribute43,
1564 x_Line_price_Att_rec.pricing_attribute44,
1565 x_Line_price_Att_rec.pricing_attribute45,
1566 x_Line_price_Att_rec.pricing_attribute46,
1567 x_Line_price_Att_rec.pricing_attribute47,
1568 x_Line_price_Att_rec.pricing_attribute48,
1569 x_Line_price_Att_rec.pricing_attribute49,
1570 x_Line_price_Att_rec.pricing_attribute50,
1571 x_Line_price_Att_rec.pricing_attribute51,
1572 x_Line_price_Att_rec.pricing_attribute52,
1573 x_Line_price_Att_rec.pricing_attribute53,
1574 x_Line_price_Att_rec.pricing_attribute54,
1575 x_Line_price_Att_rec.pricing_attribute55,
1576 x_Line_price_Att_rec.pricing_attribute56,
1577 x_Line_price_Att_rec.pricing_attribute57,
1578 x_Line_price_Att_rec.pricing_attribute58,
1579 x_Line_price_Att_rec.pricing_attribute59,
1580 x_Line_price_Att_rec.pricing_attribute60,
1581 x_Line_price_Att_rec.pricing_attribute61,
1582 x_Line_price_Att_rec.pricing_attribute62,
1583 x_Line_price_Att_rec.pricing_attribute63,
1584 x_Line_price_Att_rec.pricing_attribute64,
1585 x_Line_price_Att_rec.pricing_attribute65,
1586 x_Line_price_Att_rec.pricing_attribute66,
1587 x_Line_price_Att_rec.pricing_attribute67,
1588 x_Line_price_Att_rec.pricing_attribute68,
1589 x_Line_price_Att_rec.pricing_attribute69,
1590 x_Line_price_Att_rec.pricing_attribute70,
1591 x_Line_price_Att_rec.pricing_attribute71,
1592 x_Line_price_Att_rec.pricing_attribute72,
1593 x_Line_price_Att_rec.pricing_attribute73,
1594 x_Line_price_Att_rec.pricing_attribute74,
1595 x_Line_price_Att_rec.pricing_attribute75,
1596 x_Line_price_Att_rec.pricing_attribute76,
1597 x_Line_price_Att_rec.pricing_attribute77,
1598 x_Line_price_Att_rec.pricing_attribute78,
1599 x_Line_price_Att_rec.pricing_attribute79,
1600 x_Line_price_Att_rec.pricing_attribute80,
1601 x_Line_price_Att_rec.pricing_attribute81,
1602 x_Line_price_Att_rec.pricing_attribute82,
1603 x_Line_price_Att_rec.pricing_attribute83,
1604 x_Line_price_Att_rec.pricing_attribute84,
1605 x_Line_price_Att_rec.pricing_attribute85,
1606 x_Line_price_Att_rec.pricing_attribute86,
1607 x_Line_price_Att_rec.pricing_attribute87,
1608 x_Line_price_Att_rec.pricing_attribute88,
1609 x_Line_price_Att_rec.pricing_attribute89,
1610 x_Line_price_Att_rec.pricing_attribute90,
1611 x_Line_price_Att_rec.pricing_attribute91,
1612 x_Line_price_Att_rec.pricing_attribute92,
1613 x_Line_price_Att_rec.pricing_attribute93,
1614 x_Line_price_Att_rec.pricing_attribute94,
1615 x_Line_price_Att_rec.pricing_attribute95,
1616 x_Line_price_Att_rec.pricing_attribute96,
1617 x_Line_price_Att_rec.pricing_attribute97,
1618 x_Line_price_Att_rec.pricing_attribute98,
1619 x_Line_price_Att_rec.pricing_attribute99,
1620 x_Line_price_Att_rec.pricing_attribute100,
1621 x_Line_price_Att_rec.context,
1622 x_Line_price_Att_rec.attribute1,
1623 x_Line_price_Att_rec.attribute2,
1624 x_Line_price_Att_rec.attribute3,
1625 x_Line_price_Att_rec.attribute4,
1626 x_Line_price_Att_rec.attribute5,
1627 x_Line_price_Att_rec.attribute6,
1628 x_Line_price_Att_rec.attribute7,
1629 x_Line_price_Att_rec.attribute8,
1630 x_Line_price_Att_rec.attribute9,
1631 x_Line_price_Att_rec.attribute10,
1632 x_Line_price_Att_rec.attribute11,
1633 x_Line_price_Att_rec.attribute12,
1634 x_Line_price_Att_rec.attribute13,
1635 x_Line_price_Att_rec.attribute14,
1636 x_Line_price_Att_rec.attribute15,
1637 x_Line_price_Att_rec.flex_title ,
1638 x_Line_price_Att_rec.override_flag,
1639 x_Line_price_Att_rec.lock_control,
1640 x_Line_price_Att_rec.orig_sys_atts_ref
1641 FROM oe_order_price_attribs
1642 WHERE line_id =p_line_id;
1643
1644 l_row_count := SQL%ROWCOUNT;
1645 x_row_count := l_row_count;
1646
1647 oe_debug_pub.add('Number of records: ' || l_row_count);
1648 oe_debug_pub.add('Exiting procedure Oe_Retrobill_Pvt.Prepare_Line_Pricing_Attribs:');
1649
1650 Exception
1651
1652 WHEN NO_DATA_FOUND THEN
1653 oe_debug_pub.add('Line pricing attributes not populated for the original Order');
1654 x_row_count := 0;
1655
1656 When Others Then
1657 oe_debug_pub.add('Execption occured in Oe_Retrobill_Pvt.Prepare_Line_Pricing_Attribs:'||SQLERRM);
1658 Raise;
1659 End;
1660
1661
1662
1663 PROCEDURE INSERT_RETROBILL_REQUEST(p_retrobill_request_rec IN OE_RETROBILL_REQUESTS%ROWTYPE) AS
1664 Begin
1665 INSERT INTO OE_RETROBILL_REQUESTS
1666 ( RETROBILL_REQUEST_ID
1667 ,NAME
1668 , DESCRIPTION
1669 , EXECUTION_MODE
1670 , ORDER_TYPE_ID
1671 , RETROBILL_REASON_CODE
1672 , EXECUTION_DATE
1673 , INVENTORY_ITEM_ID
1674 , SOLD_TO_ORG_ID
1675 , CREATION_DATE
1676 , CREATED_BY
1677 , LAST_UPDATE_DATE
1678 , LAST_UPDATED_BY
1679 , LAST_UPDATE_LOGIN
1680 , REQUEST_ID
1681 , PROGRAM_APPLICATION_ID
1682 , PROGRAM_ID
1683 , PROGRAM_UPDATED_DATE)
1684 VALUES
1685 (p_retrobill_request_rec.retrobill_request_id,
1686 nvl(p_retrobill_request_rec.name,'RETRO TEST '||p_retrobill_request_rec.retrobill_request_id),
1687 p_retrobill_request_rec.description,
1688 p_retrobill_request_rec.execution_mode,
1689 p_retrobill_request_rec.order_type_id,
1690 p_retrobill_request_rec.retrobill_reason_code,
1691 nvl(p_retrobill_request_rec.execution_date,SYSDATE),
1692 p_retrobill_request_rec.inventory_item_id,
1693 p_retrobill_request_rec.sold_to_org_id,
1694 nvl(p_retrobill_request_rec.creation_date,SYSDATE),
1695 nvl(p_retrobill_request_rec.created_by,fnd_global.user_id),
1696 nvl(p_retrobill_request_rec.last_update_date,SYSDATE),
1697 nvl(p_retrobill_request_rec.last_updated_by,fnd_global.user_id),
1698 nvl(p_retrobill_request_rec.last_update_login,fnd_global.login_id),
1699 p_retrobill_request_rec.request_id,
1700 nvl(p_retrobill_request_rec.program_application_id,fnd_global.prog_appl_id),
1701 p_retrobill_request_rec.program_id,
1702 p_retrobill_request_rec.program_updated_date);
1703
1704 Exception
1705 When Others Then
1706 oe_debug_pub.add('RETRO error:'||SQLERRM);
1707 oe_debug_pub.add('RETRO:INSERT_RETROBILL_REQUEST:'||SQLERRM);
1708 End;
1709
1710
1711
1712
1713 /**************************************************************
1714 Called by OE_ORDER_PRICE_PVT, Update retrobill lines
1715 based on the end results. New prie > old --> ORDER then redefault line type
1716 If New price < old --> RETURN existing line type is fine (we always assume return) is
1717 the most commom case
1718 ***************************************************************/
1719 Procedure Update_Retrobill_Lines(p_operation IN VARCHAR2)
1720 As
1721
1722 Cursor priced_lines IS
1723 select lines.ADJUSTED_UNIT_PRICE*nvl(lines.priced_quantity,l.ordered_quantity)/l.
1724 ordered_quantity NEW_UNIT_SELLING_PRICE
1725 , lines.UNIT_PRICE*nvl(lines.priced_quantity,l.ordered_quantity)/l.ordered_quantity NEW_UNIT_LIST_PRICE
1726 , lines.ADJUSTED_UNIT_PRICE UNIT_SELLING_PRICE_PER_PQTY
1727 , lines.UNIT_PRICE UNIT_LIST_PRICE_PER_PQTY
1728 , decode(lines.priced_quantity,-99999,l.ordered_quantity,lines.priced_quantity) PRICING_QUANTITY
1729 , decode(lines.priced_quantity,-99999,l.order_quantity_uom,lines.priced_uom_code)PRICING_QUANTITY_UOM
1730 , lines.price_list_header_id PRICE_LIST_ID
1731 , lines.price_request_code PRICE_REQUEST_CODE
1732 , nvl(lines.percent_price, NULL) UNIT_LIST_PERCENT
1733 , nvl(lines.parent_price, NULL) UNIT_PERCENT_BASE_PRICE
1734 , decode(lines.parent_price, NULL, 0, 0, 0,
1735 lines.adjusted_unit_price/lines.parent_price) UNIT_SELLING_PERCENT
1736 , l.unit_selling_price OLD_UNIT_SELLING_PRICE
1737 , l.unit_list_price OLD_UNIT_LIST_PRICE
1738 , l.line_id
1739 , l.header_id
1740 , l.retrobill_request_id
1741 , l.order_source_id --source_id,orig_sys_document_ref and sys_line_ref forms an index
1742 , l.orig_sys_document_ref
1743 , l.orig_sys_line_ref
1744 , l.inventory_item_id --For identifying unique item
1745 , l.sold_to_org_id --For identifying unique customer
1746 , l.line_number
1747 , l.lock_control + 1 LOCK_CONTROL
1748 from qp_preq_lines_tmp lines,
1749 oe_order_lines_all l
1750 where lines.line_id=l.line_id
1751 and lines.line_type_code='LINE'
1752 and lines.pricing_status_code in(QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION);
1753
1754 cursor get_retrobilled_sum(p_order_source_id IN NUMBER,
1755 p_orig_sys_document_ref IN NUMBER,
1756 p_orig_sys_line_ref IN NUMBER,
1757 p_curr_retro_id IN NUMBER) IS
1758 select sum(oeol1.unit_selling_price * decode(oeol1.line_category_code,'RETURN',-1,1)),
1759 sum(oeol1.unit_list_price * decode(oeol1.line_category_code,'RETURN',-1,1))
1760 From oe_order_lines_all oeol1
1761 Where oeol1.order_source_id = p_order_source_id
1762 and oeol1.orig_sys_document_ref = to_char(p_orig_sys_document_ref) --p_orig_sys_document_ref --commented for bug#7665009
1763 and oeol1.orig_sys_line_ref = to_char(p_orig_sys_line_ref) --p_orig_sys_line_ref --commented for bug#7665009
1764 and oeol1.retrobill_request_id <> p_curr_retro_id; --exclude current retrobill line
1765
1766 cursor get_original_price(p_orig_sys_line_ref IN NUMBER) IS
1767 Select unit_selling_price,unit_list_price
1768 From oe_order_lines_all
1769 Where line_id = p_orig_sys_line_ref;
1770
1771 cursor line_number(p_header_id IN NUMBER) IS
1772 Select line_id from oe_order_lines_all
1773 Where header_id = p_header_id
1774 Order by line_id;
1775
1776 l_deleted_line NUMBER :=NULL;
1777 l_deleted_line_hdr NUMBER :=NULL;
1778 k PLS_INTEGER:=1;
1779 l_line_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
1780 l_line_num_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
1781 l_usp_sum Number:=0;
1782 l_ulp_sum Number:=0;
1783 l_orig_usp Number:=0;
1784 l_orig_ulp Number:=0;
1785 l_retrobill_selling_price Number;
1786 l_retrobill_list_price Number;
1787 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
1788 l_line_tbl OE_ORDER_PUB.LINE_TBL_TYPE;
1789 l_old_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
1790 l_old_line_tbl OE_ORDER_PUB.LINE_TBL_TYPE;
1791 l_debit Varchar2(1);
1792 j Number:=0;
1793 l_control_rec OE_GLOBALS.Control_Rec_Type;
1794 l_return_status Varchar2(15);
1795 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1796 --skubendr{
1797 l_inventory_item_id_previous NUMBER;
1798 l_unique_item BOOLEAN := TRUE;
1799 l_sold_to_org_id_previous NUMBER;
1800 l_unique_customer BOOLEAN := TRUE;
1801 l_current_line_deleted VARCHAR2(1);
1802 --skubendr}
1803
1804 l_line_category_code VARCHAR2(100); --Bug# 8609475
1805
1806 BEGIN
1807
1808 oe_debug_pub.add('Entering update retrobill lines : p_operation = '||p_operation);
1809 --GSCC Not initializing during declaration
1810 l_debit := 'N';
1811 l_inventory_item_id_previous := g_retrobill_request_rec.inventory_item_id;
1812 l_sold_to_org_id_previous := g_retrobill_request_rec.sold_to_org_id;
1813 l_current_line_deleted := 'N';
1814
1815 FOR I IN PRICED_LINES LOOP
1816 open get_retrobilled_sum(I.order_source_id,I.orig_sys_document_ref,I.orig_sys_line_ref,I.retrobill_request_id);
1817 fetch get_retrobilled_sum into l_usp_sum,l_ulp_sum;
1818 close get_retrobilled_sum;
1819 IF l_usp_sum IS NULL THEN l_usp_sum:=0; END IF;
1820 IF l_ulp_sum IS NULL THEN l_ulp_sum:=0; END IF;
1821
1822 IF nvl(p_operation,'xxx') = 'CREATE' THEN
1823 --now get the difference between the original price and new price minus the sum (if the line has previous retrobilled before)
1824 l_retrobill_selling_price := I.OLD_UNIT_SELLING_PRICE + l_usp_sum - I.NEW_UNIT_SELLING_PRICE;
1825 l_retrobill_list_price := I.OLD_UNIT_LIST_PRICE + l_ulp_sum - I.NEW_UNIT_LIST_PRICE;
1826 --I.OLD_UNIT price can not be used because it is the difference of price if it users PREVIEW/reprice
1827 --the retrobill lines again, therefore we need to query the original price to get the price
1828 ELSE
1829 open get_original_price(I.orig_sys_line_ref);
1830 fetch get_original_price into l_orig_usp,l_orig_ulp;
1831 close get_original_price;
1832 IF l_debug_level > 0 THEN
1833 Oe_Debug_Pub.Add('Retro:orig_usp:'||l_orig_usp||',orig_ulp:'||l_orig_ulp);
1834 END IF;
1835 l_retrobill_selling_price := l_orig_usp + l_usp_sum - I.NEW_UNIT_SELLING_PRICE;
1836 l_retrobill_list_price := l_orig_ulp + l_ulp_sum - I.NEW_UNIT_LIST_PRICE;
1837 END IF;
1838
1839 IF l_debug_level > 0 THEN
1840 oe_debug_pub.add('retro-old usp:'||I.OLD_UNIT_SELLING_PRICE||' new usp:'||I.NEW_UNIT_SELLING_PRICE);
1841 oe_debug_pub.add('retro:usp sum'||l_usp_sum);
1842 oe_debug_pub.add('retro:retrobill selling_price:'|| l_retrobill_selling_price);
1843
1844 oe_debug_pub.add('retro-old ulp:'||I.OLD_UNIT_LIST_PRICE||' new ULP:'||I.NEW_UNIT_LIST_PRICE);
1845 oe_debug_pub.add('retro:ulp sum'||l_ulp_sum);
1846 oe_debug_pub.add('retro:retrobill list_price:'|| l_retrobill_list_price);
1847 END IF;
1848
1849 -------- Bug# 8609475 : Start -------
1850 select line_category_code into l_line_category_code
1851 from oe_order_lines_all
1852 where line_id = to_number(I.orig_sys_line_ref);
1853
1854 oe_debug_pub.add(' line_category_code of original order: l_line_category_code = '|| l_line_category_code);
1855
1856 if l_line_category_code = 'RETURN' then
1857 l_retrobill_list_price := l_retrobill_list_price * -1;
1858 l_retrobill_selling_price := l_retrobill_selling_price * -1;
1859 end if;
1860 oe_debug_pub.add(' l_retrobill_selling_price = '|| l_retrobill_selling_price);
1861 oe_debug_pub.add(' l_retrobill_list_price = ' || l_retrobill_list_price);
1862 -------- Bug# 8609475 : end -------
1863
1864 --Handle the line as a 'return' because new price > old price + previously retrobilled amount
1865 --bug3654144
1866 IF l_retrobill_list_price > 0 OR
1867 (l_retrobill_list_price = 0 AND l_retrobill_selling_price > 0)
1868 THEN
1869 oe_debug_pub.add('Retro: New price is lower giving credit');
1870 UPDATE OE_ORDER_LINES_all l
1871 SET UNIT_SELLING_PRICE =l_retrobill_selling_price
1872 ,UNIT_LIST_PRICE =l_retrobill_list_price
1873 ,UNIT_SELLING_PRICE_PER_PQTY=I.UNIT_SELLING_PRICE_PER_PQTY
1874 ,UNIT_LIST_PRICE_PER_PQTY =I.UNIT_LIST_PRICE_PER_PQTY
1875 ,PRICING_QUANTITY =I.PRICING_QUANTITY
1876 ,PRICING_QUANTITY_UOM =I.PRICING_QUANTITY_UOM
1877 ,PRICE_LIST_ID =I.PRICE_LIST_ID
1878 ,PRICE_REQUEST_CODE =I.PRICE_REQUEST_CODE
1879 ,UNIT_LIST_PERCENT =I.UNIT_LIST_PERCENT
1880 ,UNIT_PERCENT_BASE_PRICE =I.UNIT_PERCENT_BASE_PRICE
1881 ,UNIT_SELLING_PERCENT =I.UNIT_SELLING_PERCENT
1882 ,CALCULATE_PRICE_FLAG ='N'
1883 ,LOCK_CONTROL =I.LOCK_CONTROL
1884 WHERE l.line_id = I.line_id;
1885 --What about update global??? Need to think about it...
1886 oe_debug_pub.add('retro:updated row number:'|| SQL%ROWCOUNT||'line_id:'|| I.line_id);
1887
1888 --Handle the line as a 'buy' (invoice customer) if new price is higher
1889 --This case will be a little complex because we are changing line_category_code
1890 --by doing so, many line attributes will need to be redefaulted.
1891 --we need to call process_order oe_order_pvt.lines to update so that redefaulting could take place.
1892 --bug3654144
1893 ELSIF l_retrobill_list_price < 0 OR
1894 (l_retrobill_list_price = 0 AND l_retrobill_selling_price < 0)
1895 THEN
1896 oe_debug_pub.add('Retro: New price is higher charging more');
1897
1898 l_line_rec:=Oe_Line_Util.Query_Row(I.line_id);
1899
1900 oe_debug_pub.add(' Retro-0: l_line_rec.payment_term_id = '||l_line_rec.payment_term_id
1901 || ' l_line_rec.price_list_id = ' || l_line_rec.price_list_id
1902 || ' l_line_rec.line_type_id = ' || l_line_rec.line_type_id
1903 || ' g_retrobill_request_rec.order_type_id = ' || g_retrobill_request_rec.order_type_id
1904 ) ;
1905 SELECT DEFAULT_OUTBOUND_LINE_TYPE_ID INTO l_line_rec.line_type_id
1906 FROM OE_TRANSACTION_TYPES_all
1907 WHERE TRANSACTION_TYPE_ID = g_retrobill_request_rec.order_type_id ; -- bug# 8751523 : Added the SELECT sql
1908
1909 l_old_line_rec:=l_line_rec;
1910
1911 l_line_rec.unit_selling_price :=-1 * l_retrobill_selling_price; --bug3654144
1912 l_line_rec.unit_list_price :=abs(l_retrobill_list_price);
1913 l_line_rec.unit_selling_price_per_pqty:=-1 *I.UNIT_SELLING_PRICE_PER_PQTY;--bug3654144
1914 l_line_rec.unit_list_price_per_pqty:=abs(I.UNIT_LIST_PRICE_PER_PQTY);
1915 l_line_rec.pricing_quantity :=I.PRICING_QUANTITY;
1916 l_line_rec.pricing_quantity_uom :=I.PRICING_QUANTITY_UOM;
1917 l_line_rec.price_list_id :=I.PRICE_LIST_ID;
1918 l_line_rec.price_request_code :=I.PRICE_REQUEST_CODE;
1919 l_line_rec.unit_list_percent :=I.UNIT_LIST_PERCENT;
1920 l_line_rec.unit_percent_base_price:=I.UNIT_PERCENT_BASE_PRICE;
1921 l_line_rec.unit_selling_percent :=I.UNIT_SELLING_PERCENT;
1922 l_line_rec.line_category_code :='ORDER';
1923 l_line_rec.calculate_price_flag :='N';
1924 --set following to miss char such that redefault can take place.
1925 -- l_line_rec.line_type_id :=FND_API.G_MISS_NUM; -- bug# 8751523 : Commented this assignment
1926 l_line_rec.operation :=OE_GLOBALS.G_OPR_UPDATE;
1927 l_debit := 'Y';
1928 j:=j+1;
1929
1930 -- oe_default_line.attributes(p_x_line_rec => l_line_rec, p_old_line_rec => l_old_line_rec); --- -- bug# 8751523: Commented this call
1931
1932 l_line_rec.price_list_id :=I.PRICE_LIST_ID;
1933 l_old_line_rec.price_list_id :=I.PRICE_LIST_ID;
1934
1935 oe_debug_pub.add('Price list id after setting it back:' || l_line_rec.price_list_id);
1936 oe_debug_pub.add('Old price list id:'||l_old_line_rec.price_list_id);
1937 oe_debug_pub.add(' Retro-1: l_line_rec.payment_term_id = '||l_line_rec.payment_term_id
1938 || ' l_line_rec.price_list_id = ' || l_line_rec.price_list_id
1939 || ' l_line_rec.line_type_id = ' || l_line_rec.line_type_id
1940 ) ;
1941
1942 l_old_line_tbl(j):=l_old_line_rec;
1943 l_line_tbl(j):=l_line_rec;
1944 ELSE
1945 --If the selling price is the same then we remove the created line
1946 --skubendr{
1947 l_current_line_deleted := 'Y';
1948 --skubendr}
1949 oe_debug_pub.add('Retro:No price difference,delete line_id:'||I.line_id);
1950
1951 l_deleted_line_hdr := I.header_id;
1952
1953 IF(I.line_number =1) THEN
1954 G_FIRST_LINE_DELETED := 'Y';
1955 END IF;
1956
1957 Oe_Line_Util.Delete_Row(I.line_id);
1958
1959 IF G_LINES_NOT_RETRO_DISPLAYED='N' THEN
1960 FND_MESSAGE.SET_NAME('ONT','ONT_RETROBILL_LINES_NO_CHANGE');
1961 OE_MSG_PUB.ADD;
1962 G_LINES_NOT_RETRO_DISPLAYED:='Y';
1963 END IF;
1964 END IF;
1965 --skubendr{
1966 IF( l_current_line_deleted = 'N') THEN
1967 g_retrobill_request_rec.inventory_item_id := I.inventory_item_id;
1968 g_retrobill_request_rec.sold_to_org_id := I.sold_to_org_id;
1969 IF(l_unique_item = TRUE and l_inventory_item_id_previous <> I.inventory_item_id) THEN
1970 l_unique_item := FALSE;
1971 END If;
1972 IF(l_unique_customer = TRUE and l_sold_to_org_id_previous <> I.sold_to_org_id) THEN
1973 l_unique_customer := FALSE;
1974 END If;
1975 l_inventory_item_id_previous := I.inventory_item_id;
1976 l_sold_to_org_id_previous := I.sold_to_org_id;
1977 END If;
1978 l_current_line_deleted := 'N';
1979 --skubendr}
1980 END LOOP;
1981
1982
1983 IF l_debit = 'Y' THEN
1984 l_control_rec.controlled_operation := TRUE;
1985 l_control_rec.change_attributes := TRUE;
1986 l_control_rec.default_attributes := TRUE;
1987 l_control_rec.validate_entity := FALSE;
1988 l_control_rec.check_security := FALSE;
1989 l_control_rec.write_to_DB := TRUE;
1990 l_control_rec.process := FALSE;
1991 -- Instruct API to retain its caches
1992 l_control_rec.clear_api_cache := FALSE;
1993 l_control_rec.clear_api_requests := FALSE;
1994
1995 -- set the pricing recursion, so that pricing will not get triggered
1996 -- again due to the Oe_Order_Pub.Lines call.
1997 oe_globals.g_pricing_recursion := 'Y';
1998
1999 Oe_Order_Pvt.Lines(p_validation_level=> FND_API.G_VALID_LEVEL_NONE,
2000 p_control_rec => l_control_rec,
2001 p_x_line_tbl => l_line_tbl,
2002 p_x_old_line_tbl => l_old_line_tbl,
2003 x_return_status => l_return_status);
2004
2005 -- Reset the pricing recursion, so that pricing will get triggered after Oe_Order_Pvt.Lines call
2006 oe_globals.g_pricing_recursion := 'N';
2007 --deletion occured, need to resequence line number.
2008 END IF;
2009
2010 IF l_deleted_line_hdr IS NOT NULL THEN
2011 oe_debug_pub.add('Retro:before line_num');
2012 FOR N IN line_number(l_deleted_line_hdr) LOOP
2013 l_line_num_tbl(k):= k;
2014 l_line_id_tbl(k):= N.LINE_ID;
2015 IF(G_FIRST_LINE_DELETED='Y' and k=1) THEN
2016 SELECT price_list_id INTO G_FIRST_LINE_PRICE_LIST_ID
2017 FROM oe_order_lines_all
2018 WHERE line_id=N.LINE_ID;
2019 END IF;
2020 k:=k+1;
2021 END LOOP;
2022 oe_debug_pub.add('Retro:before update line_num');
2023 IF l_line_id_tbl.FIRST IS NOT NULL THEN
2024 FORALL K IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
2025 UPDATE OE_ORDER_LINES_ALL
2026 SET LINE_NUMBER = l_line_num_tbl(K)
2027 WHERE LINE_ID = l_line_id_tbl(K);
2028 END IF;
2029 oe_debug_pub.add('Retro:after update line_num'||SQL%ROWCOUNT);
2030 END IF;
2031 --skubendr{
2032 IF(l_unique_item = FALSE) THEN
2033 g_retrobill_request_rec.inventory_item_id := NULL;
2034 END IF;
2035 IF(l_unique_customer = FALSE) THEN
2036 g_retrobill_request_rec.sold_to_org_id := NULL;
2037 END IF;
2038 oe_debug_pub.add('Customer id before inserting'||g_retrobill_request_rec.sold_to_org_id);
2039 oe_debug_pub.add('Inventory item id before inserting'||g_retrobill_request_rec.inventory_item_id);
2040 --skubendr}
2041 End;
2042
2043
2044
2045 PROCEDURE Insert_diff_Adj As
2046 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2047
2048 cursor l_debug_cur IS
2049 select operand,operand_per_pqty,list_line_id,applied_flag,arithmetic_operator,updated_flag,adjusted_amount,adjusted_amount_per_pqty
2050 from oe_price_adjustments
2051 where retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID;
2052
2053 Begin
2054
2055 IF l_debug_level > 2 THEN
2056 --will be very slow if debug on fts will happen
2057 for k in l_debug_cur loop
2058 oe_debug_pub.add('operand:'||k.operand||'operand_per_pqty:'||k.operand_per_pqty||'list_line_id:'||k.list_line_id||
2059 'applied flag:'||k.applied_flag);
2060 oe_debug_pub.add('arithmetic_operator:'||k.arithmetic_operator||'updated_flag:'||k.updated_flag||'adjusted_amount:'||k.adjusted_amount||'adjusted_amount_per_pqty:'||k.adjusted_amount_per_pqty||' retrobill id:'||G_CURRENT_RETROBILL_REQUEST_ID);
2061
2062 end loop;
2063 END IF;
2064
2065 INSERT INTO OE_PRICE_ADJUSTMENTS
2066 ( PRICE_ADJUSTMENT_ID
2067 , CREATION_DATE
2068 , CREATED_BY
2069 , LAST_UPDATE_DATE
2070 , LAST_UPDATED_BY
2071 , LAST_UPDATE_LOGIN
2072 , PROGRAM_APPLICATION_ID
2073 , PROGRAM_ID
2074 , PROGRAM_UPDATE_DATE
2075 , REQUEST_ID
2076 , HEADER_ID
2077 , DISCOUNT_ID
2078 , DISCOUNT_LINE_ID
2079 , AUTOMATIC_FLAG
2080 , PERCENT
2081 , LINE_ID
2082 , CONTEXT
2083 , ATTRIBUTE1
2084 , ATTRIBUTE2
2085 , ATTRIBUTE3
2086 , ATTRIBUTE4
2087 , ATTRIBUTE5
2088 , ATTRIBUTE6
2089 , ATTRIBUTE7
2090 , ATTRIBUTE8
2091 , ATTRIBUTE9
2092 , ATTRIBUTE10
2093 , ATTRIBUTE11
2094 , ATTRIBUTE12
2095 , ATTRIBUTE13
2096 , ATTRIBUTE14
2097 , ATTRIBUTE15
2098 , ORIG_SYS_DISCOUNT_REF
2099 , LIST_HEADER_ID
2100 , LIST_LINE_ID
2101 , LIST_LINE_TYPE_CODE
2102 , MODIFIER_MECHANISM_TYPE_CODE
2103 , MODIFIED_FROM
2104 , MODIFIED_TO
2105 , UPDATED_FLAG
2106 , UPDATE_ALLOWED
2107 , APPLIED_FLAG
2108 , CHANGE_REASON_CODE
2109 , CHANGE_REASON_TEXT
2110 , operand
2111 , Arithmetic_operator
2112 , COST_ID
2113 , TAX_CODE
2114 , TAX_EXEMPT_FLAG
2115 , TAX_EXEMPT_NUMBER
2116 , TAX_EXEMPT_REASON_CODE
2117 , PARENT_ADJUSTMENT_ID
2118 , INVOICED_FLAG
2119 , ESTIMATED_FLAG
2120 , INC_IN_SALES_PERFORMANCE
2121 , SPLIT_ACTION_CODE
2122 , ADJUSTED_AMOUNT
2123 , PRICING_PHASE_ID
2124 , CHARGE_TYPE_CODE
2125 , CHARGE_SUBTYPE_CODE
2126 , list_line_no
2127 , source_system_code
2128 , benefit_qty
2129 , benefit_uom_code
2130 , print_on_invoice_flag
2131 , expiration_date
2132 , rebate_transaction_type_code
2133 , rebate_transaction_reference
2134 , rebate_payment_system_code
2135 , redeemed_date
2136 , redeemed_flag
2137 , accrual_flag
2138 , range_break_quantity
2139 , accrual_conversion_rate
2140 , pricing_group_sequence
2141 , modifier_level_code
2142 , price_break_type_code
2143 , substitution_attribute
2144 , proration_type_code
2145 , CREDIT_OR_CHARGE_FLAG
2146 , INCLUDE_ON_RETURNS_FLAG
2147 , AC_CONTEXT
2148 , AC_ATTRIBUTE1
2149 , AC_ATTRIBUTE2
2150 , AC_ATTRIBUTE3
2151 , AC_ATTRIBUTE4
2152 , AC_ATTRIBUTE5
2153 , AC_ATTRIBUTE6
2154 , AC_ATTRIBUTE7
2155 , AC_ATTRIBUTE8
2156 , AC_ATTRIBUTE9
2157 , AC_ATTRIBUTE10
2158 , AC_ATTRIBUTE11
2159 , AC_ATTRIBUTE12
2160 , AC_ATTRIBUTE13
2161 , AC_ATTRIBUTE14
2162 , AC_ATTRIBUTE15
2163 , OPERAND_PER_PQTY
2164 , ADJUSTED_AMOUNT_PER_PQTY
2165 , LOCK_CONTROL
2166 , retrobill_request_id
2167 )
2168 ( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
2169 oe_price_adjustments_s.nextval -- p_Line_Adj_rec.price_adjustment_id
2170 , sysdate --p_Line_Adj_rec.creation_date
2171 , fnd_global.user_id --p_Line_Adj_rec.created_by
2172 , sysdate --p_Line_Adj_rec.last_update_date
2173 , fnd_global.user_id --p_Line_Adj_rec.last_updated_by
2174 , fnd_global.login_id --p_Line_Adj_rec.last_update_login
2175 , NULL --p_Line_Adj_rec.program_application_id
2176 , NULL --p_Line_Adj_rec.program_id
2177 , NULL --p_Line_Adj_rec.program_update_date
2178 , NULL --p_Line_Adj_rec.request_id
2179 , oe_order_pub.g_hdr.header_id --p_Line_Adj_rec.header_id
2180 , NULL --p_Line_Adj_rec.discount_id
2181 , NULL --p_Line_Adj_rec.discount_line_id
2182 , ldets.automatic_flag
2183 , NULL --p_Line_Adj_rec.percent
2184 , oepj.line_id
2185 , NULL --p_Line_Adj_rec.context
2186 , NULL --p_Line_Adj_rec.attribute1
2187 , NULL --p_Line_Adj_rec.attribute2
2188 , NULL --p_Line_Adj_rec.attribute3
2189 , NULL --p_Line_Adj_rec.attribute4
2190 , NULL --p_Line_Adj_rec.attribute5
2191 , NULL --p_Line_Adj_rec.attribute6
2192 , NULL --p_Line_Adj_rec.attribute7
2193 , NULL --p_Line_Adj_rec.attribute8
2194 , NULL --p_Line_Adj_rec.attribute9
2195 , NULL --p_Line_Adj_rec.attribute10
2196 , NULL --p_Line_Adj_rec.attribute11
2197 , NULL --p_Line_Adj_rec.attribute12
2198 , NULL --p_Line_Adj_rec.attribute13
2199 , NULL --p_Line_Adj_rec.attribute14
2200 , NULL --p_Line_Adj_rec.attribute15
2201 , NULL --p_Line_Adj_rec.orig_sys_discount_ref
2202 , ldets.LIST_HEADER_ID
2203 , ldets.LIST_LINE_ID
2204 --bug3654144 Changing the list_line_type_code to 'DIS' if ldets.list_line_type_code is 'PBH'
2205 , decode(ldets.LIST_LINE_TYPE_CODE, 'PBH', 'DIS', ldets.list_line_type_code)
2206 , NULL --p_Line_Adj_rec.MODIFIER_MECHANISM_TYPE_CODE
2207 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_attribute, 'IUE', to_char(ldets.inventory_item_id), NULL)
2208 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_value_to, 'IUE', to_char(ldets.related_item_id), NULL)
2209 , 'N' --p_Line_Adj_rec.UPDATED_FLAG
2210 , 'N' --bug3896248 override_allowed
2211 --bug3590893 setting the applied_flag to 'N' if the adjusted_amount on the modifier that is inserted for the retrobill line is 0
2212 , decode((abs(abs(oepj.adjusted_amount) - abs(ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1))) * decode(ldets.list_line_type_code,'DIS',-1,1)),0,'N',ldets.APPLIED_FLAG)
2213 , NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
2214 , NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
2215 --below is operand
2216 --bug3654144 modifying the calculation so that the operand is opposite in sign to adjusted_amount if the list_line_type_code = 'DIS' and same as adjusted_amount otherwise ('PBH' is considered as 'DIS' in diff adjs)
2217 , (oepj.adjusted_amount - ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1)) * decode(oeol.line_category_code, 'RETURN', 1, -1) * decode(ldets.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
2218 , 'AMT' --arithmetic_operator hardcoded to AMT
2219 , NULl --p_line_Adj_rec.COST_ID
2220 , NULL --p_line_Adj_rec.TAX_CODE
2221 , NULL --p_line_Adj_rec.TAX_EXEMPT_FLAG
2222 , NULL --p_line_Adj_rec.TAX_EXEMPT_NUMBER
2223 , NULL --p_line_Adj_rec.TAX_EXEMPT_REASON_CODE
2224 , NULL --p_line_Adj_rec.PARENT_ADJUSTMENT_ID
2225 , NULL --p_line_Adj_rec.INVOICED_FLAG
2226 , NULL --p_line_Adj_rec.ESTIMATED_FLAG
2227 , NULL --p_line_Adj_rec.INC_IN_SALES_PERFORMANCE
2228 , NULL --p_line_Adj_rec.SPLIT_ACTION_CODE
2229 --below is adjusted amount
2230 --bug3654144 commenting the following and adding a new calculation for adjusted_amount
2231 -- , abs(abs(oepj.adjusted_amount) - abs(ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1))) * decode(ldets.list_line_type_code,'DIS',-1,1)
2232 , (oepj.adjusted_amount - ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1)) * decode(oeol.line_category_code, 'RETURN', 1, -1)
2233 , ldets.pricing_phase_id --p_line_Adj_rec.PRICING_PHASE_ID
2234 , ldets.CHARGE_TYPE_CODE
2235 , ldets.CHARGE_SUBTYPE_CODE
2236 , ldets.list_line_no
2237 , qh.source_system_code
2238 , ldets.benefit_qty
2239 , ldets.benefit_uom_code
2240 , NULL --p_Line_Adj_rec.print_on_invoice_flag
2241 , ldets.expiration_date
2242 , ldets.rebate_transaction_type_code
2243 , NULL --p_Line_Adj_rec.rebate_transaction_reference
2244 , NULL --p_Line_Adj_rec.rebate_payment_system_code
2245 , NULL --p_Line_Adj_rec.redeemed_date
2246 , NULL --p_Line_Adj_rec.redeemed_flag
2247 , ldets.accrual_flag
2248 , ldets.line_quantity --p_Line_Adj_rec.range_break_quantity
2249 , ldets.accrual_conversion_rate
2250 , ldets.pricing_group_sequence
2251 , ldets.modifier_level_code
2252 , ldets.price_break_type_code
2253 , ldets.substitution_attribute
2254 , ldets.proration_type_code
2255 , NULL --p_Line_Adj_rec.credit_or_charge_flag
2256 , ldets.include_on_returns_flag
2257 , NULL -- p_Line_Adj_rec.ac_context
2258 , NULL -- p_Line_Adj_rec.ac_attribute1
2259 , NULL -- p_Line_Adj_rec.ac_attribute2
2260 , NULL -- p_Line_Adj_rec.ac_attribute3
2261 , NULL -- p_Line_Adj_rec.ac_attribute4
2262 , NULL -- p_Line_Adj_rec.ac_attribute5
2263 , NULL -- p_Line_Adj_rec.ac_attribute6
2264 , NULL -- p_Line_Adj_rec.ac_attribute7
2265 , NULL -- p_Line_Adj_rec.ac_attribute8
2266 , NULL -- p_Line_Adj_rec.ac_attribute9
2267 , NULL -- p_Line_Adj_rec.ac_attribute10
2268 , NULL -- p_Line_Adj_rec.ac_attribute11
2269 , NULL -- p_Line_Adj_rec.ac_attribute12
2270 , NULL -- p_Line_Adj_rec.ac_attribute13
2271 , NULL -- p_Line_Adj_rec.ac_attribute14
2272 , NULL -- p_Line_Adj_rec.ac_attribute15
2273 --bug3654144 commenting the following and adding a new calculation of operand_per_pqty
2274 -- , decode(sign(ldets.OPERAND_value),-1,oepj.operand_per_pqty-ldets.OPERAND_value, abs(oepj.operand_per_pqty - ldets.OPERAND_value))
2275 , (oepj.adjusted_amount_per_pqty - ldets.adjustment_amount) * decode(oeol.line_category_code, 'RETURN', 1, -1) * decode(ldets.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
2276 --bug3654144 Multiplying by -1 if the line_category_code = 'ORDER'
2277 , (oepj.adjusted_amount_per_pqty - ldets.adjustment_amount) * decode(oeol.line_category_code, 'RETURN', 1, -1)
2278 , 1
2279 ,null --this offset adjustment should not have retrobill request id
2280 FROM QP_LDETS_v ldets
2281 , QP_PREQ_LINES_TMP lines
2282 , QP_LIST_HEADERS_B QH
2283 , OE_PRICE_ADJUSTMENTS oepj
2284 , OE_ORDER_LINES_ALL oeol
2285 WHERE
2286 ldets.list_header_id=qh.list_header_id
2287 --AND ldets.process_code=QP_PREQ_GRP.G_STATUS_NEW
2288 AND lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
2289 --AND lines.process_status <> 'NOT_VALID'
2290 AND ldets.line_index=lines.line_index
2291 AND oepj.line_id=oeol.line_id
2292 --AND (nvl(ldets.automatic_flag,'N') = 'Y')
2293 AND nvl(ldets.created_from_list_type_code,'xxx') not in ('PRL','AGR')
2294 AND ldets.list_line_type_code<>'PLL'
2295 AND ldets.list_line_type_code<>'IUE'
2296 AND ldets.price_adjustment_id = oepj.price_adjustment_id
2297 AND ldets.list_line_id = oepj.list_line_id
2298 AND oepj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID
2299 );
2300
2301 oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' inserted, Retrobill id:'||G_CURRENT_RETROBILL_REQUEST_ID);
2302 Exception
2303 WHEN OTHERS THEN
2304 IF l_debug_level > 0 THEN
2305 oe_debug_pub.add('RETRO:ERROR in creating offset adjustments'||sqlerrm);
2306 END IF;
2307 Raise FND_API.G_EXC_ERROR;
2308 END Insert_diff_Adj;
2309
2310 Procedure Update_Diff_Adj AS
2311 Begin
2312 /*UPDATE OE_PRICE_ADJUSTMENTS oepj
2313 SET (operand,
2314 operand_per_pqty,
2315 adjusted_amount,
2316 adjusted_amount_per_pqty)
2317 =(SELECT oepj.adjusted_amount - ldets.adjustment_amount* nvl(lines.priced_quantity,1/nvl(lines.line_quantity,1))
2318 ,oepj.adjusted_amount_per_pqty - ldets.adjustment_amount
2319 adjusted_amount - ldets.adjustment_amount* nvl(lines.priced_quantity,1/nvl(lines.line_quantity,1))
2320 adjusted_amont_per_pqty - ldets.adjustment_amount
2321 From QP_LDETS_V ldets
2322 Where ldets.list_line_id = oepj.list_line_id
2323 --AND ldets.price_adjustment_id = oepj.price_adjustment_id
2324 AND oepj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID
2325 --AND ldets.process_code = QP_PREQ_GRP.G_STATUS_UPDATED
2326 )
2327 WHERE retrobill_request_id IS NULL --offset adj does not have retrobill_request_id
2328 AND */
2329 null;
2330 End;
2331
2332 Procedure Update_Existing_Retro_Adj AS
2333 Begin
2334 UPDATE OE_PRICE_ADJUSTMENTS adj
2335 SET ( operand
2336 , operand_per_pqty
2337 , adjusted_amount
2338 , adjusted_amount_per_pqty
2339 , arithmetic_operator
2340 , pricing_phase_id
2341 , pricing_group_sequence
2342 , automatic_flag
2343 , list_line_type_code
2344 , applied_flag
2345 , modified_from
2346 , modified_to
2347 , update_allowed
2348 , updated_flag
2349 , charge_type_code
2350 , charge_subtype_code
2351 , range_break_quantity
2352 , accrual_conversion_rate
2353 , accrual_flag
2354 , list_line_no
2355 , benefit_qty
2356 , benefit_uom_code
2357 , print_on_invoice_flag
2358 , expiration_date
2359 , rebate_transaction_type_code
2360 , modifier_level_code
2361 , price_break_type_code
2362 , substitution_attribute
2363 , proration_type_code
2364 , include_on_returns_flag
2365 , lock_control
2366 )
2367 =
2368 (select
2369 decode(ldets.operand_calculation_code,
2370 '%',ldets.operand_value,
2371 'LUMPSUM', ldets.operand_value,
2372 ldets.operand_value*nvl(lines.priced_quantity,lines.line_quantity)/lines.line_quantity)
2373 , ldets.operand_value
2374 --bug4583857
2375 , ldets.adjustment_amount* nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1)
2376 , ldets.adjustment_amount
2377 , ldets.operand_calculation_code
2378 , ldets.pricing_phase_id
2379 , ldets.pricing_group_sequence
2380 , ldets.automatic_flag
2381 , ldets.list_line_type_code
2382 , 'N' --ldets.applied_flag
2383 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_attribute, 'IUE', to_char(ldets.inventory_item_id), NULL)
2384 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_value_to, 'IUE',to_char(ldets.related_item_id), NULL)
2385 , ldets.override_flag
2386 , ldets.updated_flag
2387 , ldets.charge_type_code
2388 , ldets.charge_subtype_code
2389 , ldets.line_quantity --range_break_quantity (?)
2390 , ldets.accrual_conversion_rate
2391 , ldets.accrual_flag
2392 , ldets.list_line_no
2393 , ldets.benefit_qty
2394 , ldets.benefit_uom_code
2395 , ldets.print_on_invoice_flag
2396 , ldets.expiration_date
2397 , ldets.rebate_transaction_type_code
2398 , ldets.modifier_level_code
2399 , ldets.price_break_type_code
2400 , ldets.substitution_attribute
2401 , ldets.proration_type_code
2402 , ldets.include_on_returns_flag
2403 , adj.lock_control + 1
2404 from
2405 QP_LDETS_v ldets
2406 , QP_PREQ_LINES_TMP lines
2407 WHERE
2408 lines.line_index = ldets.line_index
2409 --and lines.process_status <> 'NOT_VALID'
2410 and ldets.list_line_id = adj.list_line_id
2411 --bug3417428
2412 --and ldets.line_index = adj.header_id+nvl(adj.line_id,0)
2413 and lines.line_id=adj.line_id
2414 --and ldets.process_code = QP_PREQ_GRP.G_STATUS_UPDATED
2415 and adj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID
2416 )
2417 WHERE header_id=oe_order_pub.g_hdr.header_id
2418 and list_line_id in
2419 (select list_line_id
2420 from qp_ldets_v ldets2, QP_PREQ_LINES_TMP lines2
2421 where --lines2.process_status <> 'NOT_VALID'
2422 --and ldets2.process_code=QP_PREQ_GRP.G_STATUS_UPDATED
2423 lines2.line_index = ldets2.line_index
2424 and ldets2.list_line_id = adj.list_line_id
2425 --bug3417428
2426 --and ldets2.line_index = adj.header_id+nvl(adj.line_id,0))
2427 and lines2.line_id=adj.line_id)
2428 and adj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID;
2429 oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' updated');
2430 Exception
2431 WHEN OTHERS THEN
2432
2433 oe_debug_pub.add('Retro:ERROR in updating adjustments'||sqlerrm);
2434
2435 --Raise FND_API.G_EXC_ERROR;
2436 End;
2437
2438 --bug3654144 Adding a new procedure to insert adjustment lines corresponding to new modifiers returns by pricing engine.
2439 PROCEDURE Insert_New_Adj As
2440 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2441 Begin
2442 INSERT INTO OE_PRICE_ADJUSTMENTS
2443 ( PRICE_ADJUSTMENT_ID
2444 , CREATION_DATE
2445 , CREATED_BY
2446 , LAST_UPDATE_DATE
2447 , LAST_UPDATED_BY
2448 , LAST_UPDATE_LOGIN
2449 , PROGRAM_APPLICATION_ID
2450 , PROGRAM_ID
2451 , PROGRAM_UPDATE_DATE
2452 , REQUEST_ID
2453 , HEADER_ID
2454 , DISCOUNT_ID
2455 , DISCOUNT_LINE_ID
2456 , AUTOMATIC_FLAG
2457 , PERCENT
2458 , LINE_ID
2459 , CONTEXT
2460 , ATTRIBUTE1
2461 , ATTRIBUTE2
2462 , ATTRIBUTE3
2463 , ATTRIBUTE4
2464 , ATTRIBUTE5
2465 , ATTRIBUTE6
2466 , ATTRIBUTE7
2467 , ATTRIBUTE8
2468 , ATTRIBUTE9
2469 , ATTRIBUTE10
2470 , ATTRIBUTE11
2471 , ATTRIBUTE12
2472 , ATTRIBUTE13
2473 , ATTRIBUTE14
2474 , ATTRIBUTE15
2475 , ORIG_SYS_DISCOUNT_REF
2476 , LIST_HEADER_ID
2477 , LIST_LINE_ID
2478 , LIST_LINE_TYPE_CODE
2479 , MODIFIER_MECHANISM_TYPE_CODE
2480 , MODIFIED_FROM
2481 , MODIFIED_TO
2482 , UPDATED_FLAG
2483 , UPDATE_ALLOWED
2484 , APPLIED_FLAG
2485 , CHANGE_REASON_CODE
2486 , CHANGE_REASON_TEXT
2487 , operand
2488 , Arithmetic_operator
2489 , COST_ID
2490 , TAX_CODE
2491 , TAX_EXEMPT_FLAG
2492 , TAX_EXEMPT_NUMBER
2493 , TAX_EXEMPT_REASON_CODE
2494 , PARENT_ADJUSTMENT_ID
2495 , INVOICED_FLAG
2496 , ESTIMATED_FLAG
2497 , INC_IN_SALES_PERFORMANCE
2498 , SPLIT_ACTION_CODE
2499 , ADJUSTED_AMOUNT
2500 , PRICING_PHASE_ID
2501 , CHARGE_TYPE_CODE
2502 , CHARGE_SUBTYPE_CODE
2503 , list_line_no
2504 , source_system_code
2505 , benefit_qty
2506 , benefit_uom_code
2507 , print_on_invoice_flag
2508 , expiration_date
2509 , rebate_transaction_type_code
2510 , rebate_transaction_reference
2511 , rebate_payment_system_code
2512 , redeemed_date
2513 , redeemed_flag
2514 , accrual_flag
2515 , range_break_quantity
2516 , accrual_conversion_rate
2517 , pricing_group_sequence
2518 , modifier_level_code
2519 , price_break_type_code
2520 , substitution_attribute
2521 , proration_type_code
2522 , CREDIT_OR_CHARGE_FLAG
2523 , INCLUDE_ON_RETURNS_FLAG
2524 , AC_CONTEXT
2525 , AC_ATTRIBUTE1
2526 , AC_ATTRIBUTE2
2527 , AC_ATTRIBUTE3
2528 , AC_ATTRIBUTE4
2529 , AC_ATTRIBUTE5
2530 , AC_ATTRIBUTE6
2531 , AC_ATTRIBUTE7
2532 , AC_ATTRIBUTE8
2533 , AC_ATTRIBUTE9
2534 , AC_ATTRIBUTE10
2535 , AC_ATTRIBUTE11
2536 , AC_ATTRIBUTE12
2537 , AC_ATTRIBUTE13
2538 , AC_ATTRIBUTE14
2539 , AC_ATTRIBUTE15
2540 , OPERAND_PER_PQTY
2541 , ADJUSTED_AMOUNT_PER_PQTY
2542 , LOCK_CONTROL
2543 , RETROBILL_REQUEST_ID
2544 )
2545 ( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
2546 -- oe_price_adjustments_s.nextval -- p_Line_Adj_rec.price_adjustment_id
2547 ldets.price_adjustment_id
2548 , sysdate --p_Line_Adj_rec.creation_date
2549 , fnd_global.user_id --p_Line_Adj_rec.created_by
2550 , sysdate --p_Line_Adj_rec.last_update_date
2551 , fnd_global.user_id --p_Line_Adj_rec.last_updated_by
2552 , fnd_global.login_id --p_Line_Adj_rec.last_update_login
2553 , NULL --p_Line_Adj_rec.program_application_id
2554 , NULL --p_Line_Adj_rec.program_id
2555 , NULL --p_Line_Adj_rec.program_update_date
2556 , NULL --p_Line_Adj_rec.request_id
2557 , oe_order_pub.g_hdr.header_id --p_Line_Adj_rec.header_id
2558 , NULL --p_Line_Adj_rec.discount_id
2559 , NULL --p_Line_Adj_rec.discount_line_id
2560 , ldets.automatic_flag
2561 , NULL --p_Line_Adj_rec.percent
2562 , decode(ldets.modifier_level_code,'ORDER',NULL,lines.line_id)
2563 , NULL --p_Line_Adj_rec.context
2564 , NULL --p_Line_Adj_rec.attribute1
2565 , NULL --p_Line_Adj_rec.attribute2
2566 , NULL --p_Line_Adj_rec.attribute3
2567 , NULL --p_Line_Adj_rec.attribute4
2568 , NULL --p_Line_Adj_rec.attribute5
2569 , NULL --p_Line_Adj_rec.attribute6
2570 , NULL --p_Line_Adj_rec.attribute7
2571 , NULL --p_Line_Adj_rec.attribute8
2572 , NULL --p_Line_Adj_rec.attribute9
2573 , NULL --p_Line_Adj_rec.attribute10
2574 , NULL --p_Line_Adj_rec.attribute11
2575 , NULL --p_Line_Adj_rec.attribute12
2576 , NULL --p_Line_Adj_rec.attribute13
2577 , NULL --p_Line_Adj_rec.attribute14
2578 , NULL --p_Line_Adj_rec.attribute15
2579 , NULL --p_Line_Adj_rec.orig_sys_discount_ref
2580 , ldets.LIST_HEADER_ID
2581 , ldets.LIST_LINE_ID
2582 , ldets.LIST_LINE_TYPE_CODE
2583 , NULL --p_Line_Adj_rec.MODIFIER_MECHANISM_TYPE_CODE
2584 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_attribute, 'IUE', to_char(ldets.inventory_item_id), NULL)
2585 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_value_to, 'IUE', to_char(ldets.related_item_id), NULL)
2586 , 'N' --p_Line_Adj_rec.UPDATED_FLAG
2587 , 'N' --bug3896248 --override_allowed
2588 , 'N' -- applied_flag
2589 , NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
2590 , NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
2591 , nvl(ldets.order_qty_operand, decode(ldets.operand_calculation_code,
2592 '%', ldets.operand_value,
2593 'LUMPSUM', ldets.operand_value,
2594 ldets.operand_value*lines.priced_quantity/nvl(lines.line_quantity,1)))
2595 , ldets.operand_calculation_code --p_Line_Adj_rec.arithmetic_operator
2596 , NULl --p_line_Adj_rec.COST_ID
2597 , NULL --p_line_Adj_rec.TAX_CODE
2598 , NULL --p_line_Adj_rec.TAX_EXEMPT_FLAG
2599 , NULL --p_line_Adj_rec.TAX_EXEMPT_NUMBER
2600 , NULL --p_line_Adj_rec.TAX_EXEMPT_REASON_CODE
2601 , NULL --p_line_Adj_rec.PARENT_ADJUSTMENT_ID
2602 , NULL --p_line_Adj_rec.INVOICED_FLAG
2603 , NULL --p_line_Adj_rec.ESTIMATED_FLAG
2604 , NULL --p_line_Adj_rec.INC_IN_SALES_PERFORMANCE
2605 , NULL --p_line_Adj_rec.SPLIT_ACTION_CODE
2606 , nvl(ldets.order_qty_adj_amt, ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1))
2607 , ldets.pricing_phase_id --p_line_Adj_rec.PRICING_PHASE_ID
2608 , ldets.CHARGE_TYPE_CODE
2609 , ldets.CHARGE_SUBTYPE_CODE
2610 , ldets.list_line_no
2611 , qh.source_system_code
2612 , ldets.benefit_qty
2613 , ldets.benefit_uom_code
2614 , NULL --p_Line_Adj_rec.print_on_invoice_flag
2615 , ldets.expiration_date
2616 , ldets.rebate_transaction_type_code
2617 , NULL --p_Line_Adj_rec.rebate_transaction_reference
2618 , NULL --p_Line_Adj_rec.rebate_payment_system_code
2619 , NULL --p_Line_Adj_rec.redeemed_date
2620 , NULL --p_Line_Adj_rec.redeemed_flag
2621 , ldets.accrual_flag
2622 , ldets.line_quantity --p_Line_Adj_rec.range_break_quantity
2623 , ldets.accrual_conversion_rate
2624 , ldets.pricing_group_sequence
2625 , ldets.modifier_level_code
2626 , ldets.price_break_type_code
2627 , ldets.substitution_attribute
2628 , ldets.proration_type_code
2629 , NULL --p_Line_Adj_rec.credit_or_charge_flag
2630 , ldets.include_on_returns_flag
2631 , NULL -- p_Line_Adj_rec.ac_context
2632 , NULL -- p_Line_Adj_rec.ac_attribute1
2633 , NULL -- p_Line_Adj_rec.ac_attribute2
2634 , NULL -- p_Line_Adj_rec.ac_attribute3
2635 , NULL -- p_Line_Adj_rec.ac_attribute4
2636 , NULL -- p_Line_Adj_rec.ac_attribute5
2637 , NULL -- p_Line_Adj_rec.ac_attribute6
2638 , NULL -- p_Line_Adj_rec.ac_attribute7
2639 , NULL -- p_Line_Adj_rec.ac_attribute8
2640 , NULL -- p_Line_Adj_rec.ac_attribute9
2641 , NULL -- p_Line_Adj_rec.ac_attribute10
2642 , NULL -- p_Line_Adj_rec.ac_attribute11
2643 , NULL -- p_Line_Adj_rec.ac_attribute12
2644 , NULL -- p_Line_Adj_rec.ac_attribute13
2645 , NULL -- p_Line_Adj_rec.ac_attribute14
2646 , NULL -- p_Line_Adj_rec.ac_attribute15
2647 , ldets.OPERAND_value
2648 , ldets.adjustment_amount
2649 , 1
2650 , G_CURRENT_RETROBILL_REQUEST_ID
2651 FROM
2652 QP_LDETS_v ldets
2653 , QP_PREQ_LINES_TMP lines
2654 , QP_LIST_HEADERS_B QH
2655 WHERE
2656 ldets.list_header_id=qh.list_header_id
2657 AND ldets.process_code=QP_PREQ_GRP.G_STATUS_NEW
2658 AND lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
2659 AND lines.process_status <> 'NOT_VALID'
2660 AND ldets.line_index=lines.line_index
2661 --AND ldets.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2662 AND (nvl(ldets.automatic_flag,'N') = 'Y')
2663 -- or
2664 -- (ldets.list_line_type_code = 'FREIGHT_CHARGE'))
2665 AND ldets.created_from_list_type_code not in ('PRL','AGR')
2666 AND ldets.list_line_type_code<>'PLL'
2667 AND ldets.list_line_type_code<>'IUE'
2668 AND ldets.list_line_type_code NOT IN ('TAX','FREIGHT_CHARGE')
2669 -- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
2670 );
2671
2672 IF l_debug_level > 0 THEN
2673 oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' NEW ADJUSTMENTS' ) ;
2674 END IF;
2675
2676 INSERT INTO OE_PRICE_ADJ_ASSOCS
2677 ( PRICE_ADJUSTMENT_ID
2678 ,CREATION_DATE
2679 ,CREATED_BY
2680 ,LAST_UPDATE_DATE
2681 ,LAST_UPDATED_BY
2682 ,LAST_UPDATE_LOGIN
2683 ,PROGRAM_APPLICATION_ID
2684 ,PROGRAM_ID
2685 ,PROGRAM_UPDATE_DATE
2686 ,REQUEST_ID
2687 ,PRICE_ADJ_ASSOC_ID
2688 ,LINE_ID
2689 ,RLTD_PRICE_ADJ_ID
2690 ,LOCK_CONTROL
2691 )
2692 (SELECT /*+ ORDERED USE_NL(QPL ADJ RADJ) */
2693 LDET.price_adjustment_id
2694 ,sysdate --p_Line_Adj_Assoc_Rec.creation_date
2695 ,fnd_global.user_id --p_Line_Adj_Assoc_Rec.CREATED_BY
2696 ,sysdate --p_Line_Adj_Assoc_Rec.LAST_UPDATE_DATE
2697 ,fnd_global.user_id --p_Line_Adj_Assoc_Rec.LAST_UPDATED_BY
2698 ,fnd_global.login_id --p_Line_Adj_Assoc_Rec.LAST_UPDATE_LOGIN
2699 ,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_APPLICATION_ID
2700 ,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_ID
2701 ,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_UPDATE_DATE
2702 ,NULL --p_Line_Adj_Assoc_Rec.REQUEST_ID
2703 ,OE_PRICE_ADJ_ASSOCS_S.nextval
2704 ,NULL
2705 ,RLDET.PRICE_ADJUSTMENT_ID
2706 ,1
2707 FROM
2708 QP_PREQ_RLTD_LINES_TMP RLTD,
2709 QP_PREQ_LDETS_TMP LDET,
2710 QP_PREQ_LDETS_TMP RLDET
2711 WHERE
2712 LDET.LINE_DETAIL_INDEX = RLTD.LINE_DETAIL_INDEX AND
2713 RLDET.LINE_DETAIL_INDEX = RLTD.RELATED_LINE_DETAIL_INDEX AND
2714 LDET.PRICING_STATUS_CODE = 'N' AND
2715 LDET.PROCESS_CODE IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) AND
2716 nvl(LDET.AUTOMATIC_FLAG, 'N') = 'Y' AND
2717 lDET.CREATED_FROM_LIST_TYPE_CODE NOT IN ('PRL','AGR') AND
2718 lDET.PRICE_ADJUSTMENT_ID IS NOT NULL AND
2719 RLDET.PRICE_ADJUSTMENT_ID IS NOT NULL AND
2720 RLDET.PRICING_STATUS_CODE = 'N' AND
2721 RLDET.PROCESS_CODE = 'N' AND
2722 nvl(RLDET.AUTOMATIC_FLAG, 'N') = 'Y' AND
2723 -- not in might not be needed
2724 RLDET.PRICE_ADJUSTMENT_ID
2725 NOT IN (SELECT RLTD_PRICE_ADJ_ID
2726 FROM OE_PRICE_ADJ_ASSOCS
2727 WHERE PRICE_ADJUSTMENT_ID = LDET.PRICE_ADJUSTMENT_ID ) AND
2728 RLTD.PRICING_STATUS_CODE = 'N');
2729
2730
2731
2732 IF l_debug_level > 0 THEN
2733 oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' NEW PRICE ADJ ASSOCS' , 3 ) ;
2734 END IF;
2735
2736
2737 INSERT INTO OE_PRICE_ADJUSTMENTS
2738 ( PRICE_ADJUSTMENT_ID
2739 , CREATION_DATE
2740 , CREATED_BY
2741 , LAST_UPDATE_DATE
2742 , LAST_UPDATED_BY
2743 , LAST_UPDATE_LOGIN
2744 , PROGRAM_APPLICATION_ID
2745 , PROGRAM_ID
2746 , PROGRAM_UPDATE_DATE
2747 , REQUEST_ID
2748 , HEADER_ID
2749 , DISCOUNT_ID
2750 , DISCOUNT_LINE_ID
2751 , AUTOMATIC_FLAG
2752 , PERCENT
2753 , LINE_ID
2754 , CONTEXT
2755 , ATTRIBUTE1
2756 , ATTRIBUTE2
2757 , ATTRIBUTE3
2758 , ATTRIBUTE4
2759 , ATTRIBUTE5
2760 , ATTRIBUTE6
2761 , ATTRIBUTE7
2762 , ATTRIBUTE8
2763 , ATTRIBUTE9
2764 , ATTRIBUTE10
2765 , ATTRIBUTE11
2766 , ATTRIBUTE12
2767 , ATTRIBUTE13
2768 , ATTRIBUTE14
2769 , ATTRIBUTE15
2770 , ORIG_SYS_DISCOUNT_REF
2771 , LIST_HEADER_ID
2772 , LIST_LINE_ID
2773 , LIST_LINE_TYPE_CODE
2774 , MODIFIER_MECHANISM_TYPE_CODE
2775 , MODIFIED_FROM
2776 , MODIFIED_TO
2777 , UPDATED_FLAG
2778 , UPDATE_ALLOWED
2779 , APPLIED_FLAG
2780 , CHANGE_REASON_CODE
2781 , CHANGE_REASON_TEXT
2782 , operand
2783 , Arithmetic_operator
2784 , COST_ID
2785 , TAX_CODE
2786 , TAX_EXEMPT_FLAG
2787 , TAX_EXEMPT_NUMBER
2788 , TAX_EXEMPT_REASON_CODE
2789 , PARENT_ADJUSTMENT_ID
2790 , INVOICED_FLAG
2791 , ESTIMATED_FLAG
2792 , INC_IN_SALES_PERFORMANCE
2793 , SPLIT_ACTION_CODE
2794 , ADJUSTED_AMOUNT
2795 , PRICING_PHASE_ID
2796 , CHARGE_TYPE_CODE
2797 , CHARGE_SUBTYPE_CODE
2798 , list_line_no
2799 , source_system_code
2800 , benefit_qty
2801 , benefit_uom_code
2802 , print_on_invoice_flag
2803 , expiration_date
2804 , rebate_transaction_type_code
2805 , rebate_transaction_reference
2806 , rebate_payment_system_code
2807 , redeemed_date
2808 , redeemed_flag
2809 , accrual_flag
2810 , range_break_quantity
2811 , accrual_conversion_rate
2812 , pricing_group_sequence
2813 , modifier_level_code
2814 , price_break_type_code
2815 , substitution_attribute
2816 , proration_type_code
2817 , CREDIT_OR_CHARGE_FLAG
2818 , INCLUDE_ON_RETURNS_FLAG
2819 , AC_CONTEXT
2820 , AC_ATTRIBUTE1
2821 , AC_ATTRIBUTE2
2822 , AC_ATTRIBUTE3
2823 , AC_ATTRIBUTE4
2824 , AC_ATTRIBUTE5
2825 , AC_ATTRIBUTE6
2826 , AC_ATTRIBUTE7
2827 , AC_ATTRIBUTE8
2828 , AC_ATTRIBUTE9
2829 , AC_ATTRIBUTE10
2830 , AC_ATTRIBUTE11
2831 , AC_ATTRIBUTE12
2832 , AC_ATTRIBUTE13
2833 , AC_ATTRIBUTE14
2834 , AC_ATTRIBUTE15
2835 , OPERAND_PER_PQTY
2836 , ADJUSTED_AMOUNT_PER_PQTY
2837 , LOCK_CONTROL
2838 , RETROBILL_REQUEST_ID
2839 )
2840 ( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
2841 oe_price_adjustments_s.nextval -- p_Line_Adj_rec.price_adjustment_id
2842 , sysdate --p_Line_Adj_rec.creation_date
2843 , fnd_global.user_id --p_Line_Adj_rec.created_by
2844 , sysdate --p_Line_Adj_rec.last_update_date
2845 , fnd_global.user_id --p_Line_Adj_rec.last_updated_by
2846 , fnd_global.login_id --p_Line_Adj_rec.last_update_login
2847 , NULL --p_Line_Adj_rec.program_application_id
2848 , NULL --p_Line_Adj_rec.program_id
2849 , NULL --p_Line_Adj_rec.program_update_date
2850 , NULL --p_Line_Adj_rec.request_id
2851 , oe_order_pub.g_hdr.header_id --p_Line_Adj_rec.header_id
2852 , NULL --p_Line_Adj_rec.discount_id
2853 , NULL --p_Line_Adj_rec.discount_line_id
2854 , ldets.automatic_flag
2855 , NULL --p_Line_Adj_rec.percent
2856 , decode(ldets.modifier_level_code,'ORDER',NULL,lines.line_id)
2857 , NULL --p_Line_Adj_rec.context
2858 , NULL --p_Line_Adj_rec.attribute1
2859 , NULL --p_Line_Adj_rec.attribute2
2860 , NULL --p_Line_Adj_rec.attribute3
2861 , NULL --p_Line_Adj_rec.attribute4
2862 , NULL --p_Line_Adj_rec.attribute5
2863 , NULL --p_Line_Adj_rec.attribute6
2864 , NULL --p_Line_Adj_rec.attribute7
2865 , NULL --p_Line_Adj_rec.attribute8
2866 , NULL --p_Line_Adj_rec.attribute9
2867 , NULL --p_Line_Adj_rec.attribute10
2868 , NULL --p_Line_Adj_rec.attribute11
2869 , NULL --p_Line_Adj_rec.attribute12
2870 , NULL --p_Line_Adj_rec.attribute13
2871 , NULL --p_Line_Adj_rec.attribute14
2872 , NULL --p_Line_Adj_rec.attribute15
2873 , NULL --p_Line_Adj_rec.orig_sys_discount_ref
2874 , ldets.LIST_HEADER_ID
2875 , ldets.LIST_LINE_ID
2876 , decode(ldets.list_line_type_code,'PBH','DIS',ldets.list_line_type_code)
2877 , NULL --p_Line_Adj_rec.MODIFIER_MECHANISM_TYPE_CODE
2878 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_attribute, 'IUE', to_char(ldets.inventory_item_id), NULL)
2879 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_value_to, 'IUE', to_char(ldets.related_item_id), NULL)
2880 , 'N' --p_Line_Adj_rec.UPDATED_FLAG
2881 , ldets.override_flag
2882 , ldets.applied_flag
2883 , NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
2884 , NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
2885 --below is operand
2886 , decode(oeol.line_category_code, 'RETURN', -1, 1) * ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1) * decode(ldets.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
2887 , 'AMT' --hardcoded to 'AMT'
2888 , NULl --p_line_Adj_rec.COST_ID
2889 , NULL --p_line_Adj_rec.TAX_CODE
2890 , NULL --p_line_Adj_rec.TAX_EXEMPT_FLAG
2891 , NULL --p_line_Adj_rec.TAX_EXEMPT_NUMBER
2892 , NULL --p_line_Adj_rec.TAX_EXEMPT_REASON_CODE
2893 , NULL --p_line_Adj_rec.PARENT_ADJUSTMENT_ID
2894 , NULL --p_line_Adj_rec.INVOICED_FLAG
2895 , NULL --p_line_Adj_rec.ESTIMATED_FLAG
2896 , NULL --p_line_Adj_rec.INC_IN_SALES_PERFORMANCE
2897 , NULL --p_line_Adj_rec.SPLIT_ACTION_CODE
2898 , decode(oeol.line_category_code, 'RETURN', -1, 1) * nvl(ldets.order_qty_adj_amt, ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1))
2899 , ldets.pricing_phase_id --p_line_Adj_rec.PRICING_PHASE_ID
2900 , ldets.CHARGE_TYPE_CODE
2901 , ldets.CHARGE_SUBTYPE_CODE
2902 , ldets.list_line_no
2903 , qh.source_system_code
2904 , ldets.benefit_qty
2905 , ldets.benefit_uom_code
2906 , NULL --p_Line_Adj_rec.print_on_invoice_flag
2907 , ldets.expiration_date
2908 , ldets.rebate_transaction_type_code
2909 , NULL --p_Line_Adj_rec.rebate_transaction_reference
2910 , NULL --p_Line_Adj_rec.rebate_payment_system_code
2911 , NULL --p_Line_Adj_rec.redeemed_date
2912 , NULL --p_Line_Adj_rec.redeemed_flag
2913 , ldets.accrual_flag
2914 , ldets.line_quantity --p_Line_Adj_rec.range_break_quantity
2915 , ldets.accrual_conversion_rate
2916 , ldets.pricing_group_sequence
2917 , ldets.modifier_level_code
2918 , ldets.price_break_type_code
2919 , ldets.substitution_attribute
2920 , ldets.proration_type_code
2921 , NULL --p_Line_Adj_rec.credit_or_charge_flag
2922 , ldets.include_on_returns_flag
2923 , NULL -- p_Line_Adj_rec.ac_context
2924 , NULL -- p_Line_Adj_rec.ac_attribute1
2925 , NULL -- p_Line_Adj_rec.ac_attribute2
2926 , NULL -- p_Line_Adj_rec.ac_attribute3
2927 , NULL -- p_Line_Adj_rec.ac_attribute4
2928 , NULL -- p_Line_Adj_rec.ac_attribute5
2929 , NULL -- p_Line_Adj_rec.ac_attribute6
2930 , NULL -- p_Line_Adj_rec.ac_attribute7
2931 , NULL -- p_Line_Adj_rec.ac_attribute8
2932 , NULL -- p_Line_Adj_rec.ac_attribute9
2933 , NULL -- p_Line_Adj_rec.ac_attribute10
2934 , NULL -- p_Line_Adj_rec.ac_attribute11
2935 , NULL -- p_Line_Adj_rec.ac_attribute12
2936 , NULL -- p_Line_Adj_rec.ac_attribute13
2937 , NULL -- p_Line_Adj_rec.ac_attribute14
2938 , NULL -- p_Line_Adj_rec.ac_attribute15
2939 , decode(oeol.line_category_code, 'RETURN', -1, 1) * ldets.adjustment_amount * decode(ldets.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
2940 , decode(oeol.line_category_code, 'RETURN', -1, 1) * ldets.adjustment_amount
2941 , 1
2942 , null --retrobill_request_id
2943 FROM
2944 QP_LDETS_v ldets
2945 , QP_PREQ_LINES_TMP lines
2946 , QP_LIST_HEADERS_B QH
2947 , OE_ORDER_LINES_ALL oeol
2948 WHERE
2949 ldets.list_header_id=qh.list_header_id
2950 AND ldets.process_code=QP_PREQ_GRP.G_STATUS_NEW
2951 AND lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
2952 AND lines.process_status <> 'NOT_VALID'
2953 AND ldets.line_index=lines.line_index
2954 AND lines.line_id=oeol.line_id
2955 --AND ldets.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2956 AND (nvl(ldets.automatic_flag,'N') = 'Y')
2957 -- or
2958 -- (ldets.list_line_type_code = 'FREIGHT_CHARGE'))
2959 AND ldets.created_from_list_type_code not in ('PRL','AGR')
2960 AND ldets.list_line_type_code<>'PLL'
2961 AND ldets.list_line_type_code<>'IUE'
2962 AND ldets.list_line_type_code NOT IN ('TAX','FREIGHT_CHARGE')
2963 -- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
2964 );
2965
2966 IF l_debug_level > 0 THEN
2967 oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' new DIFF ADJUSTMENTS' ) ;
2968 END IF;
2969
2970
2971 Exception
2972 WHEN NO_DATA_FOUND THEN
2973 null;
2974 WHEN OTHERS THEN
2975 IF l_debug_level > 0 THEN
2976 oe_debug_pub.add('RETRO:ERROR in creating new offset adjustments'||sqlerrm);
2977 END IF;
2978 Raise FND_API.G_EXC_ERROR;
2979 END Insert_New_Adj;
2980
2981 --bug3654144 new procedure added
2982 -- This procedure selects the adjustments which were copied over to oe_price_adjustments
2983 --before the pricing engine call but are invalid in the setup,
2984 --and negates the adjusted_amount, operand etc so that the adjusted_amounts would tally
2985 --in the view adjustments form for the retrobill line.
2986 PROCEDURE Update_Invalid_Diff_Adj AS
2987 CURSOR invalid_price_adjs IS
2988 SELECT price_adjustment_id, list_line_id, list_line_type_code, line_id
2989 FROM oe_price_adjustments adj
2990 WHERE adj.list_line_id NOT IN
2991 (SELECT list_line_id
2992 FROM
2993 QP_LDETS_v ldets
2994 , QP_PREQ_LINES_TMP lines
2995 WHERE
2996 lines.line_index = ldets.line_index
2997 and lines.line_id= adj.line_id
2998 and adj.list_line_id=ldets.list_line_id)
2999 AND header_id=oe_order_pub.g_hdr.header_id
3000 AND nvl(applied_flag,'N') = 'Y'
3001 AND nvl(automatic_flag,'N') = 'Y'
3002 AND nvl(list_line_type_code,'TAX') NOT IN ('TAX', 'FREIGHT_CHARGE')
3003 ORDER BY line_id;
3004
3005 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3006 l_price_adjustment_id NUMBER;
3007 l_updated_inv_adj_count PLS_INTEGER := 0;
3008 l_last_line_id NUMBER;
3009 l_line_category_code VARCHAR2(30);
3010
3011 BEGIN
3012 --GSCC not initializing during declaration
3013 l_last_line_id := -1;
3014 FOR invalid_price_adj IN invalid_price_adjs LOOP
3015 IF l_debug_level > 0 THEN
3016 oe_debug_pub.add('pviprana: Invalid adj list line id : '||invalid_price_adj.list_line_id);
3017 oe_debug_pub.add('pviprana: Invalid adj price_adjustment_id : '||invalid_price_adj.price_adjustment_id);
3018 oe_debug_pub.add('pviprana: Invalid adj list_line_type_code : '||invalid_price_adj.list_line_type_code);
3019 END IF;
3020
3021 IF l_last_line_id <> invalid_price_adj.line_id THEN
3022 BEGIN
3023 SELECT line_category_code INTO l_line_category_code
3024 FROM oe_order_lines_all
3025 WHERE line_id = invalid_price_adj.line_id;
3026 EXCEPTION
3027 WHEN OTHERS THEN
3028 IF l_debug_level > 0 THEN
3029 oe_debug_pub.add('RETRO:ERROR in creating offset adjustments'||sqlerrm);
3030 END IF;
3031 Raise FND_API.G_EXC_ERROR;
3032 END;
3033 l_last_line_id := invalid_price_adj.line_id;
3034 END IF;
3035
3036 l_price_adjustment_id := invalid_price_adj.price_adjustment_id;
3037
3038 UPDATE OE_PRICE_ADJUSTMENTS
3039 SET ( LAST_UPDATE_DATE
3040 , LIST_LINE_TYPE_CODE
3041 , UPDATED_FLAG
3042 , UPDATE_ALLOWED --bug3896248
3043 , APPLIED_FLAG
3044 , operand
3045 , Arithmetic_operator
3046 , ADJUSTED_AMOUNT
3047 , OPERAND_PER_PQTY
3048 , ADJUSTED_AMOUNT_PER_PQTY
3049 , LOCK_CONTROL
3050 , retrobill_request_id
3051 )
3052 = (SELECT SYSDATE
3053 , decode(adj.list_line_type_code,'PBH','DIS',adj.list_line_type_code)
3054 , 'N' --p_Line_Adj_rec.UPDATED_FLAG
3055 , 'N' --bug3896248 update_allowed
3056 , adj.applied_flag
3057 --below is operand
3058 , nvl(adj.adjusted_amount,0) * decode(l_line_category_code, 'ORDER', -1, 1) * decode(adj.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
3059 , 'AMT'
3060 --below is adjusted amount
3061 , nvl(adj.adjusted_amount,0) * decode(l_line_category_code, 'ORDER', -1, 1)
3062 , nvl(adj.adjusted_amount_per_pqty,0) * decode(l_line_category_code, 'ORDER', -1, 1) * decode(adj.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
3063 , nvl(adj.adjusted_amount_per_pqty,0) * decode(l_line_category_code, 'ORDER', -1, 1)
3064 , 1
3065 , null --this offset adjustment should not have retrobill request id
3066 FROM oe_price_adjustments adj
3067 , oe_order_lines_all oeol
3068 WHERE adj.price_adjustment_id = l_price_adjustment_id
3069 AND oeol.line_id=adj.line_id
3070 )
3071 WHERE price_adjustment_id=l_price_adjustment_id;
3072 l_updated_inv_adj_count := l_updated_inv_adj_count + 1;
3073
3074 END LOOP;
3075
3076 IF l_debug_level > 0 THEN
3077 oe_debug_pub.add( 'pviprana UPDATED '|| l_updated_inv_adj_count ||' INVALID DIFF ADJUSTMENTS' ) ;
3078 END IF;
3079
3080 EXCEPTION
3081 WHEN NO_DATA_FOUND THEN
3082 oe_debug_pub.add('pviprana: Exception: NO_DATA_FOUND');
3083 WHEN OTHERS THEN
3084 oe_debug_pub.add('pviprana: Exception: '||SQLERRM);
3085
3086 END;
3087
3088 --RT{
3089 --Need to code this after final merging since there is significant changes in
3090 --pack I code about qp structure.
3091 Procedure Process_Retrobill_Adjustments(p_operation IN VARCHAR2) As
3092
3093 Begin
3094 oe_debug_pub.add('Retro:Entering Process_Retrobill_Adjustments');
3095 --case 1, Retrobill does not have adjustment phases, only list price phase.
3096 --ideally in this case only adjustment amount(% discount) could have changed,operand should be the same
3097
3098 --insert the offset adjustments, this is always needed in both cases
3099
3100 Insert_Diff_Adj;
3101
3102 --Update the copied over adjustments, these adjustments were copied over
3103 --from original lines to the new retrobill lines. This procedure handle both cases
3104 --which is 1. No change in pricing setup (RETROBILL event only contains list price phase, and
3105 -- 2 which is changes in pricing adjustment setup (RETROBILL event contains pricing phase
3106 -- more than list pirce phase)
3107 Update_Existing_Retro_Adj;
3108
3109 --bug3654144
3110 Insert_New_Adj;
3111
3112 oe_debug_pub.add('Retro:Leaving Process_Retrobill_Adjustments');
3113
3114 End Process_Retrobill_Adjustments;
3115 --RT}
3116
3117
3118 PROCEDURE Perform_Operations(p_retrobill_tbl IN RETROBILL_TBL_TYPE,
3119 p_execution_mode IN VARCHAR2,
3120 --bug5003256
3121 x_error_count OUT NOCOPY NUMBER) AS
3122 i PLS_INTEGER;
3123 j PLS_INTEGER;
3124 k NUMBER;
3125 N NUMBER;
3126
3127 l_delete_tbl RETROBILL_TBL_TYPE;
3128 l_update_tbl RETROBILL_TBL_TYPE;
3129 l_null_tbl RETROBILL_TBL_TYPE;
3130
3131 del_index PLS_INTEGER:=1;
3132 upd_index PLS_INTEGER:=1;
3133 n_index PLS_INTEGER:=1;
3134
3135
3136 l_header_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
3137 l_line_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
3138 l_arrange_line_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
3139 l_arrange_line_num_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
3140 l_retrobill_qty_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
3141 l_line_tbl OE_ORDER_PUB.LINE_TBL_TYPE;
3142 l_return_status VARCHAR2(15);
3143 l_msg_count NUMBER;
3144 l_msg_data VARCHAR2(2000);
3145 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3146 Cursor unique_header_id IS
3147 SELECT DISTINCT KEY_ID Header_Id
3148 FROM OM_ID_LIST_TMP;
3149
3150 Cursor line_numbers(p_header_id IN NUMBER) IS
3151 SELECT line_id
3152 FROM oe_order_lines_all
3153 WHERE header_id=p_header_id;
3154
3155 l_price_control_rec QP_PREQ_GRP.control_record_type;
3156 --bug5003256
3157 l_book_line_count NUMBER;
3158 Begin
3159
3160 x_error_count := 0; --bug5003256
3161 i:=p_retrobill_tbl.first;
3162
3163 WHILE i IS NOT NULL LOOP
3164 IF nvl(p_retrobill_tbl(i).operation,'NULL') = 'DELETE' THEN
3165 l_delete_tbl(del_index):=p_retrobill_tbl(i);
3166 del_index:=del_index + 1;
3167 ELSIF nvl(p_retrobill_tbl(i).operation,'NULL') IN ('UPDATE','NULL') THEN
3168
3169 l_header_id_tbl(n_index):=p_retrobill_tbl(i).retrobill_header_id;
3170
3171 /*IF l_debug_level > 0 THEN
3172 oe_debug_pub.add('Retro:retrobill_header_id:'||nvl(p_retrobill_tbl(i).retrobill_header_id,'NULL'));
3173 END IF;*/
3174
3175 IF nvl(p_retrobill_tbl(i).operation,'NULL') = 'UPDATE' THEN
3176 l_line_id_tbl(upd_index):=p_retrobill_tbl(i).retrobill_line_id;
3177 l_retrobill_qty_tbl(upd_index):=p_retrobill_tbl(i).retrobill_qty;
3178 upd_index:=upd_index+1;
3179 END IF;
3180
3181 n_index:=n_index+1;
3182 END IF;
3183 i:=p_retrobill_tbl.next(i);
3184 END LOOP;
3185
3186 --We need to delete first, otherwise, we will be unable to delete booked records.
3187 i:=l_delete_tbl.first;
3188 WHILE i IS NOT NULL LOOP
3189 oe_debug_pub.add('Retro:Operation DELETE:deleting line id:'||l_delete_tbl(i).retrobill_line_id);
3190 Oe_Line_Util.Delete_Row(p_line_id=>l_delete_tbl(i).retrobill_line_id);
3191 i:=l_delete_tbl.next(i);
3192 END LOOP;
3193
3194 -- rearranging the line numbers for the headers corresponding to the lines which were deleted.
3195 DELETE FROM OM_ID_LIST_TMP;
3196
3197 j := l_delete_tbl.FIRST;
3198 WHILE j IS NOT NULL LOOP
3199 INSERT INTO OM_ID_LIST_TMP(KEY_ID)
3200 VALUES (l_delete_tbl(j).retrobill_header_id);
3201 j:=l_delete_tbl.next(j);
3202 END LOOP;
3203
3204 FOR k in unique_header_id LOOP
3205 j:=1;
3206 FOR N IN line_numbers(k.header_id) LOOP
3207 l_arrange_line_num_tbl(j):= j;
3208 l_arrange_line_id_tbl(j):= N.LINE_ID;
3209 j:=j+1;
3210 END LOOP;
3211 oe_debug_pub.add('Retro:before update line_num');
3212 IF l_arrange_line_id_tbl.FIRST IS NOT NULL THEN
3213 FORALL i IN l_arrange_line_id_tbl.FIRST..l_arrange_line_id_tbl.LAST
3214 UPDATE OE_ORDER_LINES_ALL
3215 SET LINE_NUMBER = l_arrange_line_num_tbl(i)
3216 WHERE LINE_ID = l_arrange_line_id_tbl(i);
3217 END IF;
3218 oe_debug_pub.add('Retro:after update line_num');
3219 END LOOP;
3220
3221 --handle mass update by joing to OM_ID_LIST_TMP
3222 DELETE FROM OM_ID_LIST_TMP;
3223
3224 IF l_line_id_tbl.FIRST IS NOT NULL THEN
3225 FORALL j IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
3226 INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
3227 VALUES (l_line_id_tbl(j),l_retrobill_qty_tbl(j));
3228 oe_debug_pub.add('Retro:execution mode='||p_execution_mode);
3229
3230 --pricing quantity derivation might be wrong need to get back to this!!!
3231 UPDATE OE_ORDER_LINES_ALL oeol
3232 SET (ordered_quantity,
3233 pricing_quantity
3234 )
3235 = (Select value,
3236 (value * oeol.unit_selling_price)/decode(nvl(oeol.unit_selling_price_per_pqty,0),0,1,oeol.unit_selling_price_per_pqty)
3237 From OM_ID_LIST_TMP idt
3238 Where idt.key_id = oeol.line_id)
3239 WHERE oeol.retrobill_request_id IS NOT NULL
3240 AND line_id in (SELECT key_id
3241 FROM OM_ID_LIST_TMP);
3242 END IF;
3243
3244
3245 DELETE FROM OM_ID_LIST_TMP;
3246
3247 IF l_header_id_tbl.FIRST IS NOT NULL THEN
3248 FORALL j IN l_header_id_tbl.FIRST..l_header_id_tbl.LAST
3249 INSERT INTO OM_ID_LIST_TMP(KEY_ID)
3250 VALUES (l_header_id_tbl(j));
3251 END IF;
3252
3253 --execute means book the orders
3254 IF p_execution_mode = 'EXECUTE' THEN
3255 FOR k in unique_header_id LOOP
3256 oe_debug_pub.add('Booking header id:'||k.header_id);
3257 -- Progress the workflow so that booking process is kicked off.
3258 -- This call should come back with a message OE_ORDER_BOOKED
3259 -- if booking completed successfully and if booking was deferred,
3260 -- message OE_ORDER_BOOK_DEFERRED is added to the stack.
3261 -- If booking was not successful, it should come back with a
3262 -- return status of FND_API.G_RET_STS_ERROR or
3263 -- FND_API.G_RET_STS_UNEXP_ERROR
3264 OE_Order_Book_Util.Complete_Book_Eligible
3265 ( p_api_version_number => 1.0
3266 , p_init_msg_list => FND_API.G_FALSE
3267 , p_header_id => k.header_id
3268 , x_return_status => l_return_status
3269 , x_msg_count => l_msg_count
3270 , x_msg_data => l_msg_data);
3271
3272 /*Oe_Order_Book_Util.Book_Order(p_api_version_number=>1.0
3273 ,p_header_id=>k.header_id
3274 ,x_return_status=>l_return_status
3275 ,x_msg_count=>l_msg_count
3276 ,x_msg_data=>l_msg_data);*/
3277 IF l_debug_level > 0 THEN
3278 --skubendr{
3279 IF (l_return_status<>FND_API.G_RET_STS_SUCCESS) THEN
3280 Display_Message(l_msg_count,l_msg_data);
3281 END IF;
3282 --skubendr}
3283 END IF;
3284
3285 --bug5003256 start
3286 IF (l_return_status<>FND_API.G_RET_STS_SUCCESS) THEN
3287 SELECT count(*) INTO l_book_line_count
3288 FROM oe_order_lines_all
3289 WHERE header_id = k.header_id;
3290 x_error_count := x_error_count + l_book_line_count;
3291 END IF;
3292 --bug5003256 end
3293
3294 END LOOP;
3295 ELSIF p_execution_mode = 'PREVIEW' THEN --preview again, reprice the order
3296 l_Price_Control_Rec.pricing_event := 'RETROBILL';
3297 l_Price_Control_Rec.calculate_flag := QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
3298 l_Price_Control_Rec.Simulation_Flag := 'N';
3299
3300 --bug3738043 start
3301 --reinitializing G_RETRO_PRICING_PHASE_COUNT when previewed again
3302 G_RETRO_PRICING_PHASE_COUNT := Get_Retro_Pricing_Phase_count;
3303
3304 --bug3738043 end
3305
3306 FOR k in unique_header_id LOOP
3307 oe_debug_pub.add('Retro:Repricing Order: header id:'||k.header_id);
3308 --cal price flag from retrobilling lines are 'N', set them to 'Y' before
3309 --reprice and then set them back to 'N'
3310 UPDATE OE_ORDER_LINES_ALL
3311 SET calculate_price_flag='Y'
3312 WHERE header_id=k.header_id;
3313
3314 oe_order_adj_pvt.Price_line(X_Return_Status => l_Return_Status
3315 ,p_Header_id => k.header_id
3316 ,p_Request_Type_code=> 'ONT'
3317 ,p_Control_rec => l_Price_Control_Rec
3318 ,p_write_to_db => TRUE
3319 ,x_line_Tbl => l_Line_Tbl);
3320 oe_debug_pub.add('Retro:return status from price_line:'||l_Return_Status);
3321
3322 --bug5003256 start
3323 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3324 x_error_count := x_error_count + l_line_tbl.count;
3325 END IF;
3326 --bug5003256 end
3327
3328 UPDATE OE_ORDER_LINES_ALL
3329 SET calculate_price_flag='N'
3330 WHERE header_id=k.header_id;
3331
3332 END LOOP;
3333 END IF;
3334
3335 End;
3336
3337 --Procedure for deleting the order header if no retrobill lines are created
3338 PROCEDURE Delete_Order
3339 ( p_header_id in NUMBER,
3340 --bug5003256
3341 x_header_deleted out nocopy BOOLEAN)
3342 AS
3343 l_line_count NUMBER := 0;
3344 l_return_status VARCHAR2(1);
3345 l_msg_count NUMBER;
3346 l_msg_data VARCHAR2(5000);
3347 BEGIN
3348 oe_debug_pub.add('Entering procedure Delete_Order');
3349 --bug5003256
3350 x_header_deleted := FALSE;
3351 select count(*) into l_line_count from oe_order_lines_all where header_id=p_header_id;
3352 IF(l_line_count =0) THEN
3353 OE_Header_Util.Delete_Row(p_header_id);
3354 --bug5003256
3355 x_header_deleted := TRUE;
3356 END IF;
3357 EXCEPTION
3358 WHEN OTHERS THEN
3359 oe_debug_pub.add('Exception occured in Delete_Order:'||SQLERRM);
3360 END;
3361
3362 PROCEDURE Process_Retrobill_Request
3363 (p_retrobill_request_rec IN OE_RETROBILL_REQUESTS%ROWTYPE
3364 ,p_retrobill_tbl IN RETROBILL_TBL_TYPE
3365 ,x_created_retrobill_request_id OUT NOCOPY NUMBER
3366 ,x_msg_count OUT NOCOPY NUMBER
3367 ,x_msg_data OUT NOCOPY VARCHAR2
3368 ,x_return_status OUT NOCOPY VARCHAR2
3369 ,x_retrun_status_text OUT NOCOPY VARCHAR2
3370 --bug5003256
3371 ,x_error_count OUT NOCOPY NUMBER
3372 ,x_ret_code IN OUT NOCOPY NUMBER--bug 12922209
3373 ) AS
3374 l_sold_to_org NUMBER;
3375 l_currency_code VARCHAR2(15);
3376 l_conversion_type VARCHAR2(30);
3377 l_header_rec OE_ORDER_PUB.HEADER_REC_TYPE;
3378 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
3379 l_line_tbl OE_ORDER_PUB.LINE_TBL_TYPE;
3380 l_retrobill_request_id NUMBER;
3381 l_header_price_list_id NUMBER;
3382 l_update_price_list Boolean:=FALSE;
3383 l_retrobill_request_rec OE_RETROBILL_REQUESTS%ROWTYPE;
3384 j PLS_INTEGER:=0;
3385 i PLS_INTEGER:=1;
3386 l_visited Boolean:=FALSE;
3387 stmt NUMBER;
3388 lx_header_id NUMBER;
3389 l_to_be_exe_hdr_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
3390 l_msg_count NUMBER;
3391 l_msg_data VARCHAR2(5000);
3392 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3393 lx_return_status VARCHAR2(15);
3394 l_msg VARCHAR2(2000);
3395 --bug5003256 start
3396 l_book_line_count NUMBER;
3397 l_header_deleted BOOLEAN;
3398 --bug5003256 end
3399 l_cust_po_number VARCHAR2(50); -- Bug# 6603714
3400 l_Line_price_Att_rec Oe_Order_Pub.Line_Price_Att_Rec_Type; -- 8736629
3401 l_Line_price_Att_tbl Oe_Order_Pub.Line_Price_Att_Tbl_Type; -- 8736629
3402 l_row_count NUMBER; -- 8736629
3403 Begin
3404 stmt:=1;
3405 x_return_status:=FND_API.G_RET_STS_SUCCESS;
3406 --GSCC (instead of initializing in the spec)
3407 G_FIRST_LINE_DELETED := 'N';
3408 G_FIRST_LINE_PL_ASSIGNED := 'N';
3409 --GSCC not initializing during declaration
3410 l_sold_to_org :=-9876571.5;
3411 l_currency_code :='*&)1a--,=2@~';
3412 l_conversion_type := '#2g?,-871))z';
3413 l_cust_po_number := '#2g?,-871))z'; -- Bug# 6603714
3414
3415
3416 l_retrobill_request_rec:=p_retrobill_request_rec;
3417 --skubendr{
3418 G_RETROBILL_REQUEST_REC:=p_retrobill_request_rec;
3419 --skubendr}
3420 G_LINES_NOT_RETRO_DISPLAYED := 'N';
3421 --bug5003256
3422 x_error_count := 0;
3423 --First time preview or execute, retrobill_line_id is null because retrobill lines not yet created
3424 If (p_retrobill_request_rec.execution_mode = 'PREVIEW'
3425 OR p_retrobill_request_rec.execution_mode = 'EXECUTE')
3426 AND (p_retrobill_tbl(p_retrobill_tbl.first).retrobill_line_id IS NULL)
3427 AND p_retrobill_request_rec.retrobill_request_id IS NULL
3428 Then
3429 stmt:=2;
3430 Select OE_RETROBILL_REQUEST_S.NEXTVAL
3431 Into l_retrobill_request_rec.retrobill_request_id
3432 From dual;
3433
3434 G_CURRENT_RETROBILL_REQUEST_ID:=l_retrobill_request_rec.retrobill_request_id;
3435 x_created_retrobill_request_id:=l_retrobill_request_rec.retrobill_request_id;
3436 g_retrobill_request_rec.retrobill_request_id:=l_retrobill_request_rec.retrobill_request_id;
3437 stmt:=3;
3438 Insert_Id(p_retrobill_tbl);
3439 stmt:=4;
3440 For l In Group_Lines Loop
3441
3442 If l_sold_to_org <> l.sold_to_org_id1 OR
3443 l_currency_code <> l.transactional_curr_code OR
3444 l_conversion_type <> l.conversion_type_code
3445 Then --New group create new header
3446
3447
3448 If l_visited Then --Process Previous Headers and Lines before creating new group
3449 --call process order
3450 Call_Process_Order(p_header_rec =>l_header_rec,
3451 p_line_tbl =>l_line_tbl,
3452 p_Line_price_Att_tbl => l_Line_price_Att_tbl, -- 8736629
3453 x_created_header_id=>lx_header_id,
3454 x_return_status =>lx_return_status);
3455 IF(lx_return_status=FND_API.G_RET_STS_SUCCESS and lx_header_id is not NULL) THEN
3456 Oe_retrobill_Pvt.Delete_Order(lx_header_id, l_header_deleted);
3457 --bug 12922209
3458 IF l_header_deleted THEN
3459 oe_debug_pub.ADD('Retro Order Header created has been deleted');
3460 --assiging 1 to ret_code to return warning
3461 x_ret_code :=1;
3462 END IF;
3463 --bug12922209
3464 --bug5003256
3465 ELSE
3466 x_ret_code :=1; --bug12922209
3467 oe_debug_pub.ADD('Error occured in Process order');
3468
3469 x_error_count := x_error_count + l_line_tbl.count;
3470 END IF;
3471
3472 oe_debug_pub.add('Praveen: Price List Id' || G_FIRST_LINE_PRICE_LIST_ID);
3473 oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
3474
3475 BEGIN
3476
3477 SELECT price_list_id INTO l_header_price_list_id
3478 FROM oe_order_headers_all
3479 WHERE header_id=lx_header_id;
3480
3481 IF ((G_FIRST_LINE_DELETED='Y' AND
3482 G_FIRST_LINE_PL_ASSIGNED='Y' AND
3483 G_FIRST_LINE_PRICE_LIST_ID IS NOT NULL) OR
3484 (l_header_price_list_id IS NULL)) THEN
3485 BEGIN
3486 UPDATE oe_order_headers_all
3487 SET price_list_id=G_FIRST_LINE_PRICE_LIST_ID
3488 WHERE header_id=lx_header_id;
3489 EXCEPTION
3490 WHEN NO_DATA_FOUND THEN
3491 null;
3492 END;
3493 END IF;
3494 EXCEPTION
3495 WHEN NO_DATA_FOUND THEN
3496 null;
3497 END;
3498
3499
3500
3501 If lx_return_status = FND_API.G_RET_STS_SUCCESS AND
3502 NOT l_header_deleted Then --bug5003256
3503 l_to_be_exe_hdr_id_tbl(i):=lx_header_id;
3504 i:=i+1;
3505 End If;
3506
3507 oe_debug_pub.add('Retro:Return status after calling process order:'||lx_return_status);
3508
3509 --clear header, reset line tbl records
3510 l_line_tbl.delete;
3511 l_header_rec:=null;
3512 L_Line_price_Att_tbl.delete; --8736629
3513 j:=0;
3514 G_FIRST_LINE_DELETED:='N';
3515 G_FIRST_LINE_PRICE_LIST_ID:=null;
3516 G_FIRST_LINE_PL_ASSIGNED:='N';
3517 End If;
3518
3519 l_cust_po_number:= l.cust_po_number; -- Bug# 6603714
3520 l_sold_to_org := l.sold_to_org_id1;
3521 l_currency_code := l.transactional_curr_code;
3522 l_conversion_type := l.conversion_type_code;
3523 stmt:=5;
3524 Prepare_Header(p_cust_po_number =>l_cust_po_number, -- Bug# 6603714
3525 p_sold_to_org_id =>l_sold_to_org,
3526 p_transaction_curr_code=>l_currency_code,
3527 p_conversion_type_code =>l_conversion_type,
3528 p_retrobill_request_rec=>l_retrobill_request_rec,
3529 x_header_rec =>l_header_rec);
3530 stmt:=6;
3531 l_visited := TRUE;
3532
3533 End If;
3534 stmt:=7;
3535 Prepare_Line(p_oline_rec=>l,
3536 p_retrobill_tbl=>p_retrobill_tbl,
3537 p_retrobill_request_rec=>l_retrobill_request_rec,
3538 x_line_rec=>l_line_rec);
3539
3540 --8736629
3541 oe_debug_pub.add('Prining the line_id before call to Prepare_Line_Pricing_Attribs: '|| l.line_id);
3542 Prepare_Line_Pricing_Attribs(p_line_id => l.line_id,
3543 x_Line_price_Att_rec => l_Line_price_Att_rec,
3544 x_row_count => l_row_count);
3545 IF l_row_count > 0 THEN
3546 l_Line_price_Att_rec.operation := OE_GLOBALS.G_OPR_CREATE;
3547 END IF;
3548 --8736629
3549
3550
3551 IF(j=0) THEN
3552 G_FIRST_LINE_PRICE_LIST_ID := l_line_rec.price_list_id;
3553 END IF;
3554 j:=j+1;
3555 oe_debug_pub.add('Adding line rec to the table to index : '|| j);
3556 l_line_tbl(j):=l_line_rec;
3557 -- Added for bug 8736629 Start
3558 IF l_row_count > 0 THEN
3559 oe_debug_pub.add('Adding attribute rec to the table to index : '|| j);
3560 l_Line_price_Att_rec.line_index:= j;
3561 l_Line_price_Att_tbl(j):=l_Line_price_Att_rec;
3562 oe_debug_pub.add('Clearing the attribute rec after adding attribute rec to the attribute table');
3563 l_Line_price_Att_rec:= NULL;
3564 END IF;
3565 -- Added for bug 8736629 End
3566 stmt:=8;
3567 End Loop;
3568
3569 --Call Pro Ord If there is only one unique header or for the last header and lines group
3570 Call_Process_Order(p_header_rec=>l_header_rec,
3571 p_line_tbl=>l_line_tbl,
3572 p_Line_price_Att_tbl => l_Line_price_Att_tbl, -- 8736629
3573 x_created_header_id=>lx_header_id,
3574 x_return_status=>lx_return_status);
3575 IF(lx_return_status=FND_API.G_RET_STS_SUCCESS and lx_header_id is not NULL) THEN
3576 oe_retrobill_pvt.delete_order(lx_header_id, l_header_deleted); --bug5003256
3577 --bug 12922209 start
3578 IF l_header_deleted THEN
3579 oe_debug_pub.ADD('Retro Order Header created has been deleted');
3580 x_ret_code :=1;
3581 END IF;
3582 --bug12922209 end
3583 --bug5003256
3584 ELSE
3585 oe_debug_pub.ADD('Error occured in Process order');
3586 x_ret_code :=1; --bug12922209
3587 x_error_count := x_error_count + l_line_tbl.count;
3588
3589 END IF;
3590
3591 oe_debug_pub.add('Praveen: Price List Id' || G_FIRST_LINE_PRICE_LIST_ID);
3592 oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
3593
3594 If lx_return_status = FND_API.G_RET_STS_SUCCESS AND
3595 NOT l_header_deleted Then --bug5003256
3596 l_to_be_exe_hdr_id_tbl(i):=lx_header_id;
3597 End If;
3598 BEGIN
3599
3600 SELECT price_list_id INTO l_header_price_list_id
3601 FROM oe_order_headers_all
3602 WHERE header_id=lx_header_id;
3603
3604 IF ((G_FIRST_LINE_DELETED='Y' AND
3605 G_FIRST_LINE_PL_ASSIGNED='Y' AND
3606 G_FIRST_LINE_PRICE_LIST_ID IS NOT NULL) OR
3607 (l_header_price_list_id IS NULL)) THEN
3608 BEGIN
3609 UPDATE oe_order_headers_all
3610 SET price_list_id=G_FIRST_LINE_PRICE_LIST_ID
3611 WHERE header_id=lx_header_id;
3612 EXCEPTION
3613 WHEN NO_DATA_FOUND THEN
3614 null;
3615 END;
3616 END IF;
3617 EXCEPTION
3618 WHEN NO_DATA_FOUND THEN
3619 null;
3620 END;
3621
3622 IF p_retrobill_request_rec.execution_mode = 'EXECUTE' THEN
3623 i:=l_to_be_exe_hdr_id_tbl.first;
3624 --bug5003256
3625 IF i IS NOT NULL THEN
3626 x_error_count := 0; --not including the error count obtained while previewing
3627 END IF;
3628
3629 WHILE i IS NOT NULL LOOP
3630 OE_Order_Book_Util.Complete_Book_Eligible
3631 ( p_api_version_number => 1.0
3632 , p_init_msg_list => FND_API.G_FALSE
3633 , p_header_id => l_to_be_exe_hdr_id_tbl(i)
3634 , x_return_status => lx_return_status
3635 , x_msg_count => l_msg_count
3636 , x_msg_data => l_msg_data);
3637 IF l_debug_level > 0 THEN
3638 oe_debug_pub.add('Retro:Direct Execution without preview');
3639 --skubendr{
3640 IF (lx_return_status<>FND_API.G_RET_STS_SUCCESS) THEN
3641 Display_Message(l_msg_count,l_msg_data);
3642 END IF;
3643 --skubendr}
3644 END IF;
3645
3646 --bug5003256 start
3647 IF (lx_return_status<>FND_API.G_RET_STS_SUCCESS) THEN
3648 SELECT count(*) INTO l_book_line_count
3649 FROM oe_order_lines_all
3650 WHERE header_id = l_to_be_exe_hdr_id_tbl(i);
3651
3652 x_error_count := x_error_count + l_book_line_count;
3653 END IF;
3654 --bug5003256 end
3655
3656 i:=l_to_be_exe_hdr_id_tbl.next(i);
3657 END LOOP;
3658 END IF;
3659 IF(g_retrobill_request_rec.retrobill_request_id = l_retrobill_request_rec.retrobill_request_id) THEN
3660 l_retrobill_request_rec.sold_to_org_id:=g_retrobill_request_rec.sold_to_org_id;
3661 l_retrobill_request_rec.inventory_item_id:=g_retrobill_request_rec.inventory_item_id;
3662 END IF;
3663 oe_debug_pub.add('sold_to_org_id'||l_retrobill_request_rec.sold_to_org_id);
3664 oe_debug_pub.add('inventory_item_id'||l_retrobill_request_rec.inventory_item_id);
3665 Insert_Retrobill_Request(l_retrobill_request_rec);
3666
3667
3668 Else --previously previewed request
3669
3670 --EXECUTE will not reprice the orders/lines, just booked the order and update
3671 --PREVIEW again will reprice the orders/lines
3672 oe_debug_pub.add('execution mode:'||p_retrobill_request_rec.execution_mode);
3673
3674 G_CURRENT_RETROBILL_REQUEST_ID:=p_retrobill_request_rec.retrobill_request_id;
3675
3676 IF G_CURRENT_RETROBILL_REQUEST_ID IS NULL THEN
3677 oe_debug_pub.add('Retro:previouly previewed request, request id can not be NULL!');
3678 l_msg:='Retro:previouly previewed request, request id can not be NULL!';
3679 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3680 END IF;
3681
3682 --retrobilled request parameters
3683 Update_Row(p_retrobill_request_rec=>l_retrobill_request_rec);
3684
3685 --Handle Delete,Update and null operations on the p_retrobill_tbl
3686 Perform_Operations(p_retrobill_tbl,p_retrobill_request_rec.execution_mode, x_error_count);--bug5003256
3687
3688 End If; --end if for execution mode
3689
3690
3691 oe_msg_pub.count_and_get(p_count => x_msg_count,p_data=> x_msg_data);
3692 Exception
3693
3694 When FND_API.G_EXC_UNEXPECTED_ERROR Then
3695 OE_MSG_PUB.Add_Exc_Msg
3696 ( G_PKG_NAME ,
3697 l_msg
3698 );
3699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3700 oe_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
3701 x_created_retrobill_request_id:=NULL;
3702 --bug5003256
3703 x_error_count := -1;
3704 When Others Then
3705 oe_debug_pub.add('Exception occured in process_retrobill_requests:'||SQLERRM);
3706 oe_debug_pub.add('Statement number:'||stmt);
3707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3708 x_retrun_status_text:='OE_RETROBILL_PVT.Process_Retrobill_Request'||SQLERRM;
3709 oe_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
3710 x_created_retrobill_request_id:=NULL;
3711 --bug5003256
3712 x_error_count := -1;
3713 End Process_Retrobill_Request;
3714
3715 -- This procedure gets the request_session_id from the UI and builds the pl/sql table from oe_conc_request_iface table and passes it to
3716 -- Process_Retrobill_Request
3717
3718 PROCEDURE Oe_Build_Retrobill_Tbl
3719 (
3720 p_request_session_id IN NUMBER,
3721 p_retrobill_event IN VARCHAR2,
3722 p_description IN VARCHAR2,
3723 p_order_type_id IN NUMBER,
3724 p_retrobill_request_id IN NUMBER,
3725 p_reason_code IN VARCHAR2,
3726 p_retrobill_mode IN VARCHAR2,
3727 p_sold_to_org_id IN NUMBER,
3728 p_inventory_item_id IN NUMBER,
3729 x_return_status OUT NOCOPY VARCHAR2,
3730 x_msg_count OUT NOCOPY NUMBER,
3731 x_msg_data OUT NOCOPY VARCHAR2,
3732 x_return_status_text OUT NOCOPY VARCHAR2,
3733 x_retrobill_request_id OUT NOCOPY NUMBER,
3734 x_error_count OUT NOCOPY NUMBER, --bug5003256
3735 x_ret_code OUT NOCOPY NUMBER) --bug12922209
3736 is
3737 cursor c_lines is
3738 select line_id,
3739 header_id,
3740 numeric_attribute1,
3741 numeric_attribute2,
3742 numeric_attribute3,
3743 char_attribute1
3744 from oe_conc_request_iface
3745 where request_id = p_request_session_id
3746 FOR UPDATE NOWAIT;
3747
3748 cursor c_open_retrobill_requests(c_header_id NUMBER,c_line_id NUMBER) is
3749 select name
3750 from oe_retrobill_requests
3751 where
3752 retrobill_request_id
3753 in
3754 (select retrobill_request_id from oe_order_lines_all
3755 where
3756 order_source_id=27 and
3757 orig_sys_document_ref=to_char(c_header_id) and
3758 orig_sys_line_ref=to_char(c_line_id) and
3759 invoiced_quantity is NULL
3760 )
3761 group by name;
3762
3763 c_lines_record c_lines%rowtype;
3764
3765 i NUMBER :=1;
3766 j NUMBER;
3767 l_retrobill_request_rec OE_RETROBILL_REQUESTS%ROWTYPE;
3768 l_retrobill_tbl OE_RETROBILL_PVT.RETROBILL_TBL_TYPE;
3769
3770 l_return_status varchar2(2000);
3771 l_msg_count NUMBER;
3772 l_msg_data varchar2(2000);
3773 l_return_status_text varchar2(2000);
3774 l_retrobill_request_id NUMBER;
3775
3776 l_request_name varchar2(2000) := NULL;
3777 l_lines_not_retrobilled_count NUMBER:=0;
3778
3779 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3780 l_ret_code NUMBER; --bug12922209
3781 BEGIN
3782 open c_lines;
3783 If l_debug_level > 0 Then
3784 Oe_Debug_Pub.add('skubendr:Entering procedure Oe_Retrobill_Pvt.Oe_Build_Retrobill_Table');
3785 End If;
3786
3787 -- Initialize Message Stack
3788 -- We initialize the message stack here and for the subsequent process order calls pass the init_msg_list as FALSE
3789 OE_MSG_PUB.initialize;
3790 LOOP
3791 fetch c_lines into c_lines_record;
3792 exit when c_lines%notfound;
3793
3794 IF(p_retrobill_request_id is NULL) THEN
3795 FOR c_open_retrobill_requests_rec in c_open_retrobill_requests(c_lines_record.header_id,c_lines_record.line_id) LOOP
3796 l_request_name := c_open_retrobill_requests_rec.name;
3797 END LOOP;
3798 oe_debug_pub.add('l_request_name:'||l_request_name);
3799 END IF;
3800
3801 IF(l_request_name is NULL OR p_retrobill_request_id is NOT NULL) THEN
3802 l_retrobill_tbl(i).original_line_id :=c_lines_record.line_id;
3803 l_retrobill_tbl(i).original_header_id :=c_lines_record.header_id;
3804 l_retrobill_tbl(i).retrobill_qty :=
3805 c_lines_record.numeric_attribute1;
3806 l_retrobill_tbl(i).retrobill_line_id :=
3807 c_lines_record.numeric_attribute2;
3808 l_retrobill_tbl(i).retrobill_header_id :=
3809 c_lines_record.numeric_attribute3;
3810 l_retrobill_tbl(i).operation :=
3811 c_lines_record.char_attribute1;
3812 ELSE
3813 -- Setting the Message Context
3814 OE_MSG_PUB.set_msg_context(
3815 p_entity_code => 'LINE'
3816 ,p_entity_ref => null
3817 ,p_entity_id => null
3818 ,p_header_id => c_lines_record.header_id
3819 ,p_line_id => c_lines_record.line_id
3820 -- ,p_batch_request_id => p_x_header_rec.request_id
3821 ,p_order_source_id => null
3822 ,p_orig_sys_document_ref => null
3823 ,p_change_sequence => null
3824 ,p_orig_sys_document_line_ref => null
3825 ,p_orig_sys_shipment_ref => null
3826 ,p_source_document_type_id => null
3827 ,p_source_document_id => null
3828 ,p_source_document_line_id => null
3829 ,p_attribute_code => null
3830 ,p_constraint_id => null
3831 );
3832
3833 IF l_debug_level > 0 Then
3834 oe_debug_pub.add('Line has open retrobill line:'||c_lines_record.line_id);
3835 oe_debug_pub.add('Open Request:'||l_request_name);
3836 END IF;
3837
3838 -- The line has previewed retrobill line and hence cannot be previewed
3839 FND_MESSAGE.SET_NAME('ONT','ONT_UNINVOICED_RETLINE_EXIST');
3840 FND_MESSAGE.SET_TOKEN('REQUEST',l_request_name);
3841 OE_MSG_PUB.ADD;
3842 l_ret_code := 1;--bug12922209
3843 END IF;
3844 l_request_name := NULL;
3845 i := i + 1;
3846 END LOOP;
3847
3848 IF (c_lines%ISOPEN) then
3849 close c_lines;
3850 END IF;
3851
3852 l_retrobill_request_rec.name := p_retrobill_event;
3853 l_retrobill_request_rec.description := p_description;
3854 l_retrobill_request_rec.execution_mode := p_retrobill_mode;
3855 l_retrobill_request_rec.order_type_id := p_order_type_id;
3856 l_retrobill_request_rec.retrobill_reason_code := p_reason_code;
3857 l_retrobill_request_rec.retrobill_request_id := p_retrobill_request_id;
3858 l_retrobill_request_rec.sold_to_org_id := p_sold_to_org_id;
3859 l_retrobill_request_rec.inventory_item_id := p_inventory_item_id;
3860
3861 If l_debug_level > 0 Then
3862 Oe_Debug_Pub.add('skubendr:Before calling Oe_Retrobill_Pvt.Process_Retrobill_Request');
3863 End If;
3864
3865 IF(l_retrobill_tbl.count <> 0) THEN
3866 Oe_Retrobill_Pvt.Process_Retrobill_Request
3867 (p_retrobill_request_rec => l_retrobill_request_rec
3868 ,p_retrobill_tbl => l_retrobill_tbl
3869 ,x_created_retrobill_request_id => l_retrobill_request_id
3870 ,x_msg_count => l_msg_count
3871 ,x_msg_data => l_msg_data
3872 ,x_return_status => l_return_status
3873 ,x_retrun_status_text => l_return_status_text
3874 ,x_error_count => x_error_count --bug5003256
3875 ,x_ret_code => l_ret_code); --bug12922209
3876 END IF;
3877
3878 x_ret_code := l_ret_code; --bug 12922209
3879
3880 IF l_debug_level > 0 Then
3881 Oe_Debug_Pub.add('skubendr:After calling Oe_Retrobill_Pvt.Process_Retrobill_Request :'||l_return_status);
3882 END IF;
3883
3884 -- delete the data from temp table after retrobilling process
3885 IF(p_request_session_id is not NULL) THEN
3886 Delete from oe_conc_request_iface where request_id =p_request_session_id;
3887 END IF;
3888
3889 x_retrobill_request_id := l_retrobill_request_id;
3890 x_msg_count := OE_MSG_PUB.Count_Msg;
3891 x_msg_data := l_msg_data;
3892 x_return_status := l_return_status;
3893 x_return_status_text := l_return_status_text;
3894
3895 IF l_debug_level > 0 Then
3896 Oe_Debug_Pub.add('skubendr:Exiting procedure Oe_Retrobill_Pvt.Oe_Build_Retrobill_Table:'||l_return_status);
3897 END IF;
3898 EXCEPTION
3899 WHEN NO_DATA_FOUND THEN
3900 RAISE NO_DATA_FOUND;
3901 WHEN OTHERS THEN
3902 oe_debug_pub.add('Error'||SQLERRM);
3903 END Oe_Build_Retrobill_Tbl;
3904
3905
3906 -- This procedure will be called from the UI when the user submits an concurrent request
3907
3908 PROCEDURE Oe_Retrobill_Conc_Pgm
3909 (
3910 errbuf OUT NOCOPY VARCHAR2,
3911 retcode OUT NOCOPY NUMBER,
3912 p_request_session_id IN VARCHAR2,
3913 p_retrobill_event IN VARCHAR2,
3914 p_description IN VARCHAR2,
3915 p_order_type_id IN VARCHAR2,
3916 p_retrobill_request_id IN VARCHAR2,
3917 p_reason_code IN VARCHAR2,
3918 p_retrobill_mode IN VARCHAR2,
3919 p_sold_to_org_id IN NUMBER,
3920 p_inventory_item_id IN NUMBER
3921 )
3922 is
3923 l_return_status VARCHAR2(2000);
3924 l_msg_count NUMBER;
3925 l_msg_data VARCHAR2(2000);
3926 l_return_status_text VARCHAR2(2000);
3927 l_retrobill_request_id NUMBER;
3928
3929 l_concurrent_request_id NUMBER;
3930 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3931 --rt moac
3932 l_org_id NUMBER;
3933
3934 --bug5003256
3935 l_error_count NUMBER;
3936
3937 l_ret_code NUMBER;--bug 12922209
3938 BEGIN
3939
3940 FND_PROFILE.Get('CONC_REQUEST_ID', l_concurrent_request_id);
3941
3942 IF l_debug_level > 0 THEN
3943 oe_debug_pub.add( 'REQUEST ID: '|| TO_CHAR ( L_CONCURRENT_REQUEST_ID ) ) ;
3944 END IF;
3945 --rt moac start
3946 BEGIN
3947 SELECT org_id INTO l_org_id
3948 FROM oe_transaction_types_all
3949 WHERE transaction_type_id = p_order_type_id;
3950 EXCEPTION
3951 WHEN NO_DATA_FOUND THEN
3952 l_return_status := FND_API.G_RET_STS_ERROR;
3953 fnd_file.put_line(FND_FILE.OUTPUT,'Could not set org context');
3954 retcode := -1;
3955 RETURN;
3956 END;
3957 IF l_org_id IS NOT NULL THEN
3958 MO_GLOBAL.Set_Policy_Context('S',l_org_id);
3959 IF l_debug_level > 0 THEN
3960 oe_debug_pub.add('Context is set for org_id : '|| mo_global.get_current_org_id);
3961 END IF;
3962 ELSE
3963 l_return_status := FND_API.G_RET_STS_ERROR;
3964 fnd_file.put_line(FND_FILE.OUTPUT,'Could not set org context');
3965 retcode := -1;
3966 RETURN;
3967 END IF;
3968 --rt moac end
3969
3970 Oe_Build_Retrobill_Tbl(
3971 p_request_session_id =>to_number(p_request_session_id),
3972 p_retrobill_event =>p_retrobill_event,
3973 p_description =>p_description,
3974 p_order_type_id =>to_number(p_order_type_id),
3975 p_retrobill_request_id=>to_number(p_retrobill_request_id),
3976 p_reason_code =>p_reason_code,
3977 p_retrobill_mode =>p_retrobill_mode,
3978 p_sold_to_org_id =>p_sold_to_org_id,
3979 p_inventory_item_id =>p_inventory_item_id,
3980 x_return_status =>l_return_status,
3981 x_msg_count =>l_msg_count,
3982 x_msg_data =>l_msg_data,
3983 x_return_status_text =>l_return_status_text,
3984 x_retrobill_request_id=>l_retrobill_request_id,
3985 x_error_count => l_error_count, --bug5003256
3986 x_ret_code =>l_ret_code); --bug12922209
3987
3988 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3989 OE_MSG_PUB.save_messages (l_concurrent_request_id);
3990 commit;
3991 --fnd_file.put_line(FND_FILE.OUTPUT,'Sucessful completion');
3992 errbuf := FND_MESSAGE.GET;
3993 --retcode := 0;
3994 --bug 12922209 Start
3995 IF NVL(l_ret_code,0) = 1 THEN
3996 retcode := 1;
3997 fnd_file.put_line(FND_FILE.OUTPUT,'Warning');
3998 ELSE
3999 retcode := 0;
4000 fnd_file.put_line(FND_FILE.OUTPUT,'Sucessful completion');
4001 END IF;
4002 --bug 12922209 End
4003
4004 ELSE
4005 rollback;
4006 OE_MSG_PUB.save_messages (l_concurrent_request_id);
4007 commit;
4008 fnd_file.put_line(FND_FILE.OUTPUT,'Failed');
4009 errbuf := FND_MESSAGE.GET;
4010 retcode := -1;
4011 END IF;
4012 EXCEPTION
4013 WHEN OTHERS THEN
4014 rollback;
4015 oe_debug_pub.add('Error'||SQLERRM);
4016 retcode := -1;
4017 END Oe_Retrobill_Conc_Pgm;
4018
4019
4020 FUNCTION Retrobill_Enabled RETURN BOOLEAN
4021 IS
4022 l_enable_retrobilling varchar2(1);
4023 BEGIN
4024 l_enable_retrobilling := nvl(OE_Sys_Parameters.VALUE('ENABLE_RETROBILLING'),'N');
4025 IF(l_enable_retrobilling = 'Y') THEN
4026 return(TRUE);
4027 ELSE
4028 return(FALSE);
4029 END IF;
4030 END Retrobill_Enabled;
4031
4032 PROCEDURE Interface_Retrobilled_RMA
4033 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
4034 , p_header_rec IN OE_Order_PUB.Header_Rec_Type
4035 , x_return_status OUT NOCOPY VARCHAR2
4036 , x_result_out OUT NOCOPY VARCHAR2
4037 ) IS
4038
4039 l_result_code VARCHAR2(240);
4040 l_return_status VARCHAR2(30);
4041 l_interface_line_rec OE_Invoice_PUB.RA_Interface_Lines_Rec_Type;
4042 --
4043 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4044 --
4045
4046 --Find all retrobill bill only lines created before the Return Line
4047 cursor previous_retrobill_lines IS
4048 select /* MOAC_SQL_CHANGE */ retro_line.line_id
4049 , retro_line.header_id
4050 , retro_line.order_source_id
4051 , retro_line.unit_list_price
4052 , retro_line.unit_selling_price
4053 , retro_line.invoiced_quantity
4054 , retro_line.line_category_code
4055 , retro_line.retrobill_request_id
4056 , retro_header.order_number
4057 , retro_header.order_type_id
4058 from
4059 oe_order_lines_all retro_line
4060 , oe_order_lines orig_line
4061 , oe_order_headers_all retro_header
4062 where orig_line.line_id = p_line_rec.reference_line_id
4063 and retro_line.order_source_id=27
4064 and retro_line.orig_sys_document_ref = to_char(orig_line.header_id) --bug5553346
4065 and retro_line.orig_sys_line_ref = to_char(p_line_rec.reference_line_id) --bug5553346
4066 and retro_line.header_id = retro_header.header_id
4067 and retro_line.creation_date < p_line_rec.creation_date;
4068 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
4069 l_line_tbl OE_ORDER_PUB.LINE_TBL_TYPE;
4070 i pls_integer;
4071 l_retrobilled_list_price number :=0;
4072 l_retrobilled_selling_price number:=0;
4073 l_retro_adjusted_list_price number :=0;
4074 l_retro_adjusted_selling_price number := 0;
4075 l_orig_header_id NUMBER;
4076 l_orig_list_price NUMBER;
4077 l_orig_selling_price NUMBER;
4078 BEGIN
4079 null;
4080 IF l_debug_level > 0 THEN
4081 oe_debug_pub.add( 'ENTERING INTERFACE_Retrobilled_RMA ( ) PROCEDURE' , 5 ) ;
4082 oe_debug_pub.add( 'INTERFACING Retro RMA LINE ID '||TO_CHAR ( P_LINE_REC.LINE_ID ), 3);
4083 END IF;
4084
4085 -- First credit to original order line, then positive retrobill lines
4086 i := 1;
4087 -- the first line is original order line
4088 l_line_tbl(1) := p_line_rec;
4089
4090 -- loop through retrobill lines
4091 For retrobill_line in previous_retrobill_lines
4092 LOOP
4093 IF l_debug_level > 0 THEN
4094 oe_debug_pub.add('find retrobill line'||retrobill_line.line_id, 3);
4095 oe_debug_pub.add('line category code'||retrobill_line.line_category_code, 3);
4096 oe_debug_pub.add('retrobill request'||retrobill_line.retrobill_request_id, 3);
4097 oe_debug_pub.add('order_source_id'||retrobill_line.order_source_id, 3);
4098 oe_debug_pub.add('list price'||retrobill_line.unit_list_price, 3);
4099 oe_debug_pub.add('selling price'||retrobill_line.unit_selling_price, 3);
4100 END IF;
4101 -- Only credit to positive lines, but want to get the
4102 -- total retrobilled price even for negative lines,
4103 -- so that we know whether RMA line price is the same as invoiced price
4104 IF (retrobill_line.line_category_code='ORDER') THEN
4105 --3661895 This is for caching retrobill bill only lines header_id and line_id
4106 G_Retro_Bill_Only_Line_Tbl(i).header_id := retrobill_line.header_id;
4107 G_Retro_Bill_Only_Line_Tbl(i).line_id := retrobill_line.line_id;
4108 l_line_rec := p_line_rec;
4109 l_line_rec.credit_invoice_line_id := Get_Credit_Invoice_Line_Id(retrobill_line.order_number
4110 ,retrobill_line.order_type_id
4111 ,retrobill_line.line_id);
4112 l_line_rec.retrobill_request_id := retrobill_line.retrobill_request_id;
4113 l_line_rec.order_source_id := retrobill_line.order_source_id;
4114 l_line_rec.orig_sys_document_ref := retrobill_line.header_id;
4115 l_line_rec.orig_sys_line_ref := retrobill_line.line_id;
4116 l_line_rec.unit_list_price := retrobill_line.unit_list_price;
4117 l_line_rec.unit_selling_price := retrobill_line.unit_selling_price;
4118 -- The following is for the SQL for discount reference_line_id
4119 l_line_rec.reference_line_id := retrobill_line.line_id;
4120 l_retrobilled_list_price := l_retrobilled_list_price + retrobill_line.unit_list_price;
4121 l_retrobilled_selling_price := l_retrobilled_selling_price+ retrobill_line.unit_selling_price;
4122 l_retro_adjusted_list_price := l_retro_adjusted_list_price + retrobill_line.unit_list_price;
4123 l_retro_adjusted_selling_price := l_retro_adjusted_selling_price + retrobill_line.unit_selling_price;
4124
4125 i := i + 1;
4126 l_line_tbl(i) := l_line_rec;
4127 ELSE
4128 l_retrobilled_list_price := l_retrobilled_list_price - retrobill_line.unit_list_price;
4129 l_retrobilled_selling_price := l_retrobilled_selling_price- retrobill_line.unit_selling_price;
4130 END IF;
4131 END LOOP;
4132
4133 -- Get the original price
4134 BEGIN
4135 SELECT header_id
4136 ,unit_list_price
4137 ,unit_selling_price INTO
4138 l_orig_header_id
4139 ,l_orig_list_price
4140 ,l_orig_selling_price
4141 FROM oe_order_lines_all where line_id=p_line_rec.reference_line_id;
4142 EXCEPTION
4143 WHEN OTHERS THEN
4144 -- THis is impossible
4145 RAISE FND_API.G_EXC_ERROR;
4146 END;
4147
4148 IF l_debug_level > 0 THEN
4149 oe_debug_pub.add('original Price:'||l_orig_selling_price,3);
4150 oe_debug_pub.add('rma price:'||p_line_rec.unit_selling_price,3);
4151 oe_debug_pub.add('retrobilled price'||l_retrobilled_selling_price,3);
4152 --added below debug for bug12703043
4153 oe_debug_pub.add('p_line_rec.unit_selling_price'||p_line_rec.unit_selling_price,3);
4154 oe_debug_pub.add('l_orig_selling_price '||l_orig_selling_price,3);
4155 oe_debug_pub.add('l_retro_adjusted_selling_price '||l_retro_adjusted_selling_price,3);
4156 END IF;
4157 -- No retrobill line, interface the current line as it is
4158 -- If RMA price doesn't match the invoiced price, we don't know how to handle this, credit to the original line: normally should nothappen
4159 IF (l_line_tbl.count = 1) THEN
4160
4161 --bug12703043 start
4162 l_line_rec := p_line_rec;
4163 IF (l_orig_selling_price - l_retro_adjusted_selling_price + l_retrobilled_selling_price <> p_line_rec.unit_selling_price)
4164 OR (l_orig_list_price - l_retro_adjusted_list_price + l_retrobilled_list_price <> p_line_rec.unit_list_price) then
4165
4166 l_line_rec.unit_list_price := l_orig_list_price - l_retro_adjusted_list_price + l_retrobilled_list_price;
4167 l_line_rec.unit_selling_price := l_orig_selling_price - l_retro_adjusted_selling_price + l_retrobilled_selling_price;
4168 END IF;
4169 oe_debug_pub.add('l_line_rec.unit_list_price '||l_line_rec.unit_list_price,3);
4170 oe_debug_pub.add('l_line_rec.unit_selling_price '||l_line_rec.unit_selling_price,3);
4171 --bug12703043 end
4172
4173 OE_Invoice_PUB.Interface_Single_line(p_line_rec => l_line_rec --bug12703043
4174 ,p_header_rec => p_header_rec
4175 ,p_x_interface_line_rec => l_interface_line_rec
4176 ,x_return_status => x_return_status
4177 ,x_result_out => x_result_out);
4178 RETURN;
4179 END IF;
4180
4181 -- some retrobill bill only line exists
4182 i := l_line_tbl.first;
4183 while i is not null loop
4184 l_line_rec := l_line_tbl(i);
4185 IF (i = 1) THEN
4186 -- credit to original line
4187
4188 l_line_rec.retrobill_request_id := 0;
4189 /*if (l_orig_list_price + l_retro_adjusted_list_price <> p_line_rec.unit_list_price) then
4190 -- there are other retrobill credit lines that already credit to original invoice
4191 l_line_rec.unit_list_price := p_line_rec.unit_list_price - l_retro_adjusted_list_price;
4192 l_line_rec.unit_selling_price := p_line_rec.unit_selling_price - l_retro_adjusted_selling_price;*/
4193 --bug 12703043 starts
4194 IF (l_orig_selling_price - l_retro_adjusted_selling_price + l_retrobilled_selling_price <> p_line_rec.unit_selling_price)
4195 OR (l_orig_list_price - l_retro_adjusted_list_price + l_retrobilled_list_price <> p_line_rec.unit_list_price) then
4196
4197 l_line_rec.unit_list_price := l_orig_list_price - l_retro_adjusted_list_price + l_retrobilled_list_price;
4198 l_line_rec.unit_selling_price := l_orig_selling_price - l_retro_adjusted_selling_price + l_retrobilled_selling_price;
4199 --bug 12703043 ends
4200 else
4201 l_line_rec.unit_list_price := l_orig_list_price;
4202 l_line_rec.unit_selling_price := l_orig_selling_price;
4203 end if;
4204
4205 l_line_rec.orig_sys_document_ref := l_orig_header_id;
4206 l_line_rec.orig_sys_line_ref := l_line_rec.reference_line_id;
4207 ELSE
4208 l_line_rec.invoiced_quantity := NULL;
4209 END IF;
4210
4211 IF l_debug_level > 0 THEN
4212 oe_debug_pub.add('retrobill:'||l_line_rec.line_id,3);
4213 oe_debug_pub.add('retrobill line category'
4214 ||l_line_rec.line_category_code,3);
4215 oe_debug_pub.add('retrobill line type'
4216 ||l_line_rec.line_type_id,3);
4217 oe_debug_pub.add('retrobill item'||l_line_rec.ordered_item,3);
4218 oe_debug_pub.add('invoiced_quantity'||l_line_rec.invoiced_quantity,3);
4219 END IF;
4220
4221 OE_Invoice_PUB. Interface_Single_line(p_line_rec => l_line_rec
4222 ,p_header_rec => p_header_rec
4223 ,p_x_interface_line_rec => l_interface_line_rec
4224 ,x_return_status => x_return_status
4225 ,x_result_out => x_result_out);
4226
4227 IF l_debug_level > 0 THEN
4228 oe_debug_pub.add( 'INTERFACED W/REQUEST_ID : '||
4229 L_INTERFACE_LINE_REC.REQUEST_ID || ' X_RETURN_STATUS: '|| X_RETURN_STATUS , 5 ) ;
4230 END IF;
4231 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4232 RAISE FND_API.G_EXC_ERROR;
4233 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4235 END IF;
4236 i := l_line_tbl.next(i);
4237 END LOOP;
4238
4239 -- 3661895: we need to clear this global table
4240 G_Retro_Bill_Only_Line_Tbl.delete;
4241 IF l_debug_level > 0 THEN
4242 oe_debug_pub.add('Clearing the cache G_Retro_Bill_Only_Line_Tbl');
4243 END IF;
4244 END Interface_Retrobilled_RMA;
4245
4246 Function Invoice_Number(p_order_number IN NUMBER,
4247 p_line_id IN NUMBER,
4248 p_order_type_id NUMBER) RETURN VARCHAR2 AS
4249
4250 l_order_number NUMBER;
4251 l_line_id NUMBER;
4252 l_order_type_id NUMBER;
4253 l_trx_number ra_customer_trx_all.trx_number%TYPE;
4254
4255 CURSOR invoice_number is
4256 select rct.trx_number
4257 from ra_customer_trx_lines_all rctl,
4258 ra_customer_trx_all rct,
4259 oe_transaction_types_tl ott
4260 where rctl.INTERFACE_LINE_CONTEXT='ORDER ENTRY' and
4261 rctl.INTERFACE_LINE_ATTRIBUTE1= to_char(l_order_number) and -- Added to_char for bug 9323027
4262 rctl.INTERFACE_LINE_ATTRIBUTE2=ott.name and
4263 rctl. INTERFACE_LINE_ATTRIBUTE6= to_char(l_line_id) and -- Added to_char for bug 9323027
4264 rctl.customer_trx_id = rct.customer_trx_id and
4265 ott.transaction_type_id=l_order_type_id
4266 and ott.language = (select language_code from fnd_languages
4267 where installed_flag = 'B') ;
4268
4269 BEGIN
4270 --GSCC Not initializing during declaration
4271 l_order_number := p_order_number;
4272 l_line_id := p_line_id;
4273 l_order_type_id := p_order_type_id;
4274
4275 OPEN invoice_number;
4276 LOOP
4277 FETCH invoice_number into l_trx_number;
4278 EXIT WHEN invoice_number%NOTFOUND;
4279 END LOOP;
4280 IF (invoice_number%ROWCOUNT=0) THEN
4281 l_trx_number:=null;
4282 END IF;
4283 return l_trx_number;
4284 END Invoice_Number;
4285
4286 --skubendr{
4287 --Procedure for the api based validation template Return Retrobilled Line
4288 PROCEDURE Return_Retrobilled_Line_Check
4289 ( p_application_id IN NUMBER,
4290 p_entity_short_name IN VARCHAR2,
4291 p_validation_entity_short_name IN VARCHAR2,
4292 p_validation_tmplt_short_name IN VARCHAR2,
4293 p_record_set_short_name IN VARCHAR2,
4294 p_scope IN VARCHAR2,
4295 x_result OUT NOCOPY NUMBER
4296 )
4297 AS
4298 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4299 l_count NUMBER := 0;
4300 l_line_id NUMBER;
4301 l_header_id NUMBER;
4302 l_creation_date DATE;
4303 l_line_category VARCHAR2(30);
4304 BEGIN
4305 l_line_id := oe_line_security.g_record.reference_line_id;
4306 l_header_id := oe_line_security.g_record.reference_header_id;
4307 l_line_category := oe_line_security.g_record.line_category_code;
4308 l_creation_date := oe_line_security.g_record.creation_date;
4309 If(l_line_category <> 'RETURN') THEN
4310 x_result :=0;
4311 RETURN;
4312 END IF;
4313
4314 BEGIN
4315 select count(*) into l_count from
4316 oe_order_lines_all where
4317 order_source_id=27 and
4318 orig_sys_document_ref=to_char(l_header_id) and
4319 orig_sys_line_ref = to_char(l_line_id)
4320 and l_creation_date < creation_date;
4321 EXCEPTION
4322 WHEN NO_DATA_FOUND THEN
4323 x_result := 0;
4324 RETURN;
4325 END;
4326
4327 If(l_line_category = 'RETURN' and l_count>0) THEN
4328 x_result := 1;
4329 else
4330 x_result := 0;
4331 END IF;
4332 END Return_Retrobilled_Line_Check;
4333 --Procedure for Purging Retrobill Request and the related Headers/Lines
4334
4335 PROCEDURE Delete_Retrobill_Orders
4336 ( p_purge_preview_orders IN VARCHAR2,
4337 p_retrobill_request_id IN VARCHAR2,
4338 p_request_name IN VARCHAR2,
4339 x_requests_processed OUT NOCOPY NUMBER,
4340 x_headers_processed OUT NOCOPY NUMBER
4341 )
4342 AS
4343 cursor retrobill_header(p_retrobill_request_id in VARCHAR2) is
4344 select header_id,booked_flag from oe_order_headers_all
4345 where orig_sys_document_ref=to_char(p_retrobill_request_id) --p_retrobill_request_id --commented for bug#7665009
4346 and order_source_id=27;
4347
4348 l_header_count NUMBER;
4349 l_msg_count NUMBER;
4350 l_return_status VARCHAR2(1);
4351 l_msg_data VARCHAR2(2000);
4352 l_headers_processed NUMBER:= 0;
4353 l_requests_processed NUMBER:= 0;
4354
4355 --rt moac start
4356 l_header_rec OE_ORDER_PUB.Header_Rec_Type;
4357 l_old_header_rec OE_ORDER_PUB.Header_Rec_Type;
4358 l_control_rec OE_GLOBALS.Control_Rec_Type;
4359 l_header_out_rec OE_ORDER_PUB.Header_Rec_Type;
4360 l_line_out_tbl OE_ORDER_PUB.Line_Tbl_Type;
4361 l_line_adj_out_tbl oe_order_pub.line_Adj_Tbl_Type;
4362 l_header_adj_out_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
4363 l_Header_Scredit_out_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
4364 l_Line_Scredit_out_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
4365 l_Header_Payment_out_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
4366 l_Line_Payment_out_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
4367 l_action_request_out_tbl OE_Order_PUB.request_tbl_type;
4368 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
4369 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
4370 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
4371 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
4372 l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
4373 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
4374 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
4375 --rt moac end
4376
4377 BEGIN
4378 FOR l_retrobill_header_rec in retrobill_header(p_retrobill_request_id) LOOP
4379
4380 IF(p_purge_preview_orders='Y') THEN
4381 IF(l_retrobill_header_rec.booked_flag = 'N') THEN
4382
4383 --rt moac directly calling oe_order_pvt.process_order with delete operation since the context has already been set in procedure oe_retrobill_purge
4384 /*
4385 Oe_Order_Pub.Delete_Order
4386 (
4387 p_header_id =>l_retrobill_header_rec.header_id,
4388 x_return_status =>l_return_status,
4389 x_msg_count =>l_msg_count,
4390 x_msg_data =>l_msg_data
4391 );
4392 */
4393 l_header_rec := OE_Order_PUB.G_MISS_HEADER_REC;
4394 l_header_rec.header_id := l_retrobill_header_rec.header_id;
4395 l_header_rec.operation := OE_GLOBALS.G_OPR_DELETE;
4396
4397 OE_ORDER_PVT.Process_order
4398 ( p_api_version_number => 1.0
4399 , p_init_msg_list => FND_API.G_TRUE
4400 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
4401 , x_return_status => l_return_status
4402 , x_msg_count => l_msg_count
4403 , x_msg_data => l_msg_data
4404 , p_control_rec => l_control_rec
4405 , p_x_header_rec => l_header_rec
4406 , p_x_Header_Adj_tbl => l_Header_Adj_out_tbl
4407 , p_x_Header_Scredit_tbl => l_Header_Scredit_out_tbl
4408 , p_x_Header_Payment_tbl => l_Header_Payment_out_tbl
4409 , p_x_line_tbl => l_line_out_tbl
4410 , p_x_Line_Adj_tbl => l_Line_Adj_out_tbl
4411 , p_x_Line_Scredit_tbl => l_Line_Scredit_out_tbl
4412 , p_x_Line_Payment_tbl => l_Line_Payment_out_tbl
4413 , p_x_Action_Request_tbl => l_Action_Request_out_Tbl
4414 , p_x_lot_serial_tbl => l_lot_serial_tbl
4415 , p_x_Header_price_Att_tbl => l_Header_price_Att_tbl
4416 , p_x_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl
4417 , p_x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl
4418 , p_x_Line_price_Att_tbl => l_Line_price_Att_tbl
4419 , p_x_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl
4420 , p_x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
4421 );
4422
4423 IF(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4424 oe_debug_pub.add('Deleted Header:'||l_retrobill_header_rec.header_id);
4425 l_headers_processed := l_headers_processed+1;
4426 END IF;
4427 ELSE
4428 oe_debug_pub.add('Cannot purge request as retrobill booked order exists'||p_request_name);
4429 FND_MESSAGE.SET_NAME('ONT','ONT_RETRO_PURGE_NOT_ALLOWED');
4430 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_request_name);
4431 OE_MSG_PUB.ADD;
4432
4433 EXIT;
4434 END IF;
4435 ELSE
4436 oe_debug_pub.add('Cannot purge request as purge preview order is N and attached orders exist');
4437 FND_MESSAGE.SET_NAME('ONT','ONT_RETRO_PURGE_NOT_ALLOWED');
4438 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_request_name);
4439 OE_MSG_PUB.ADD;
4440
4441 EXIT;
4442 END IF;--End of check purge_preview_orders
4443 END LOOP;
4444
4445 select count(*) into l_header_count from oe_order_headers_all
4446 where orig_sys_document_ref=to_char(p_retrobill_request_id) -- p_retrobill_request_id --commented for bug#7665009
4447 and order_source_id=27;
4448
4449 IF(l_header_count = 0) THEN
4450 IF(p_retrobill_request_id is NOT NULL) THEN
4451 Delete from oe_retrobill_requests where
4452 retrobill_request_id=to_number(p_retrobill_request_id);
4453 oe_debug_pub.add('Deleted Request'||p_retrobill_request_id);
4454 l_requests_processed := l_requests_processed + 1;
4455 END IF;
4456 END IF;
4457 x_requests_processed := l_requests_processed;
4458 x_headers_processed := l_headers_processed;
4459 EXCEPTION
4460 WHEN OTHERS THEN
4461 oe_debug_pub.add('Exception occured:'||SQLERRM);
4462 RAISE;
4463 END;
4464
4465 PROCEDURE Oe_Retrobill_Purge
4466 ( errbuf OUT NOCOPY VARCHAR2,
4467 retcode OUT NOCOPY NUMBER,
4468 p_org_id IN VARCHAR2, --rt moac
4469 p_retrobill_request_id IN VARCHAR2,
4470 p_creation_date_from IN VARCHAR2,
4471 p_creation_date_to IN VARCHAR2,
4472 p_execution_date_from IN VARCHAR2,
4473 p_execution_date_to IN VARCHAR2,
4474 p_purge_preview_orders IN VARCHAR2
4475 )
4476 AS
4477 l_header_count NUMBER;
4478 l_msg_count NUMBER;
4479 l_return_status VARCHAR2(1);
4480 l_msg_data VARCHAR2(2000);
4481 l_headers_processed_per_call NUMBER:= 0;
4482 l_requests_processed_per_call NUMBER:= 0;
4483 l_headers_processed NUMBER:= 0;
4484 l_requests_processed NUMBER:= 0;
4485 l_creation_date_from DATE;
4486 l_creation_date_to DATE;
4487 l_execution_date_from DATE;
4488 l_execution_date_to DATE;
4489 l_request_name VARCHAR2(240) := NULL;
4490 l_concurrent_id NUMBER;
4491 l_booked_flag VARCHAR2(1) := NULL;
4492 l_order_source_id NUMBER;
4493 l_orig_sys_document_ref VARCHAR2(50);
4494 l_change_sequence VARCHAR2(50);
4495 l_orig_sys_line_ref VARCHAR2(50);
4496 l_message_text VARCHAR2(2000);
4497
4498 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4499 --rt moac
4500 l_org_id NUMBER;
4501 l_old_org_id NUMBER;
4502
4503 cursor retrobill_request is
4504 --bug4752386 Selecting executed requests as well and adding execution_mode to the select clause
4505 select retrobill_request_id,retro.name,ord_typ.org_id,retro.execution_mode from oe_retrobill_requests retro,oe_order_types_v ord_typ --rt moac selecting the org_id
4506 where trunc(retro.creation_date) >= nvl(trunc(l_creation_date_from),trunc(retro.creation_date))
4507 and trunc(retro.creation_date) <= nvl(trunc(l_creation_date_to),trunc(retro.creation_date))
4508 and trunc(execution_date) >= nvl(trunc(l_execution_date_from),trunc(execution_date))
4509 and trunc(execution_date) <= nvl(trunc(l_execution_date_to),trunc(execution_date))
4510 -- and execution_mode='PREVIEW'
4511 and retro.order_type_id = ord_typ.order_type_id
4512 order by org_id; --rt moac
4513
4514
4515
4516 cursor retrobill_request_id is
4517 --bug4752386 Selecting executed requests as well and adding execution_mode to the select clause
4518 select retrobill_request_id,retro.name,ord_typ.org_id,retro.execution_mode from oe_retrobill_requests retro,oe_order_types_v ord_typ --rt moac selecting the org_id
4519 where retrobill_request_id = to_number(p_retrobill_request_id)
4520 and trunc(retro.creation_date) >= nvl(trunc(l_creation_date_from),trunc(retro.creation_date))
4521 and trunc(retro.creation_date) <= nvl(trunc(l_creation_date_to),trunc(retro.creation_date))
4522 and trunc(execution_date) >= nvl(trunc(l_execution_date_from),trunc(execution_date))
4523 and trunc(execution_date) <= nvl(trunc(l_execution_date_to),trunc(execution_date))
4524 -- and execution_mode='PREVIEW'
4525 and retro.order_type_id = ord_typ.order_type_id;
4526
4527
4528
4529 /* -----------------------------------------------------------
4530 Messages cursor
4531 -----------------------------------------------------------
4532 */
4533 CURSOR l_msg_cursor IS
4534 SELECT /*+ INDEX (a,OE_PROCESSING_MSGS_N2)
4535 USE_NL (a b) */
4536 a.order_source_id
4537 , a.original_sys_document_ref
4538 , a.change_sequence
4539 , a.original_sys_document_line_ref
4540 , b.message_text
4541 FROM oe_processing_msgs a, oe_processing_msgs_tl b
4542 WHERE a.request_id = l_concurrent_id
4543 AND a.transaction_id = b.transaction_id
4544 AND b.language = oe_globals.g_lang
4545 ORDER BY a.order_source_id, a.original_sys_document_ref, a.change_sequence;
4546
4547
4548 BEGIN
4549
4550 --GSCC Not initializing while declaration
4551 l_creation_date_from := fnd_date.canonical_to_date(p_creation_date_from);
4552 l_creation_date_to := fnd_date.canonical_to_date(p_creation_date_to);
4553 l_execution_date_from := fnd_date.canonical_to_date(p_execution_date_from);
4554 l_execution_date_to := fnd_date.canonical_to_date(p_execution_date_to);
4555 l_message_text := '';
4556
4557 fnd_file.put_line(FND_FILE.OUTPUT,'Parameters :');
4558 fnd_file.put_line(FND_FILE.OUTPUT,'Request id :'||p_retrobill_request_id);
4559 fnd_file.put_line(FND_FILE.OUTPUT,'Creation Date From:'||p_creation_date_from);
4560 fnd_file.put_line(FND_FILE.OUTPUT,'Creation Date To :'||p_creation_date_to);
4561 fnd_file.put_line(FND_FILE.OUTPUT,'Execution Date From:'||p_execution_date_from);
4562 fnd_file.put_line(FND_FILE.OUTPUT,'Execution Date To :'||p_execution_date_to);
4563 fnd_file.put_line(FND_FILE.OUTPUT,'Purge Preview Orders:'||p_purge_preview_orders);
4564 fnd_file.put_line(FND_FILE.OUTPUT,'Operating Unit Id:'||p_org_id);
4565 fnd_file.put_line(FND_FILE.OUTPUT,'');
4566
4567 FND_PROFILE.Get('CONC_REQUEST_ID', l_concurrent_id);
4568
4569 IF l_debug_level > 0 THEN
4570 oe_debug_pub.add('Entering Oe_Retrobill_Pvt.Oe_Retrobill_Purge',1);
4571 oe_debug_pub.add( 'CONCURRENT REQUEST ID: '|| TO_CHAR ( L_CONCURRENT_ID ) ) ;
4572 END IF;
4573
4574 --rt moac start
4575 mo_global.init('ONT');
4576 IF p_org_id IS NOT NULL THEN
4577 l_org_id := to_number(p_org_id);
4578 MO_GLOBAL.Set_Policy_Context('S',l_org_id);
4579 IF l_debug_level > 0 THEN
4580 oe_debug_pub.add('Setting the context for org_id : '|| l_org_id);
4581 END IF;
4582 ELSE
4583 MO_GLOBAL.Set_Policy_Context('M',null);
4584 END IF;
4585 --rt moac end
4586
4587 IF(p_retrobill_request_id is NULL) THEN
4588 l_old_org_id := -99; --rt moac
4589 FOR l_retrobill_request_rec IN retrobill_request LOOP
4590 oe_debug_pub.add('Retrobill Request Id:'||l_retrobill_request_rec.retrobill_request_id);
4591 --rt moac start
4592 IF p_org_id IS NULL THEN
4593 IF l_retrobill_request_rec.org_id <> l_old_org_id THEN
4594 MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4595 IF l_debug_level > 0 THEN
4596 oe_debug_pub.add('Setting the context for org_id : '|| l_retrobill_request_rec.org_id);
4597 oe_debug_pub.add('mo_global.get_current_org_id : '|| mo_global.get_current_org_id);
4598 END IF;
4599 l_old_org_id := l_retrobill_request_rec.org_id;
4600 END IF;
4601 END IF;
4602
4603 --setting a dummy message context so that the messages not attached to any particular order get the org_id corresponding to the policy context
4604 OE_MSG_PUB.Set_Msg_Context;
4605 --rt moac end
4606
4607 --bug4752386 If the execution mode is 'EXECUTE', need to log a message and continue with the next request
4608 IF l_retrobill_request_rec.execution_mode='EXECUTE' THEN
4609 oe_debug_pub.add('Cannot purge request as it has been executed'||l_retrobill_request_rec.name);
4610 FND_MESSAGE.SET_NAME('ONT','ONT_RT_EXC_PURGE_NOT_ALLOWED');
4611 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_retrobill_request_rec.name);
4612 OE_MSG_PUB.ADD;
4613
4614 ELSE
4615
4616
4617 delete_retrobill_orders(p_purge_preview_orders,to_char(l_retrobill_request_rec.retrobill_request_id),l_retrobill_request_rec.name,l_requests_processed_per_call,l_headers_processed_per_call);
4618 l_requests_processed := l_requests_processed + l_requests_processed_per_call;
4619 l_headers_processed := l_headers_processed + l_headers_processed_per_call;
4620 END IF; --execution mode = 'EXECUTE'
4621 END LOOP;
4622 ELSE
4623 FOR l_retrobill_request_rec IN retrobill_request_id LOOP
4624 oe_debug_pub.add('Retrobill Request Id:'||l_retrobill_request_rec.retrobill_request_id);
4625
4626 --rt moac start
4627 IF p_org_id IS NULL THEN
4628 MO_GLOBAL.Set_Policy_Context('S',l_retrobill_request_rec.org_id);
4629 IF l_debug_level > 0 THEN
4630 oe_debug_pub.add('Setting the context for org_id : '|| l_retrobill_request_rec.org_id);
4631 END IF;
4632 END IF;
4633
4634 --setting a dummy message context so that the messages not attached to any particular order get the org_id corresponding to the policy context
4635 OE_MSG_PUB.Set_Msg_Context;
4636 --rt moac end
4637
4638 --bug4752386 If the execution mode is 'EXECUTE', need to log a message and continue with the next request
4639 IF l_retrobill_request_rec.execution_mode='EXECUTE' THEN
4640 oe_debug_pub.add('Cannot purge request as it has been executed'||l_retrobill_request_rec.name);
4641 FND_MESSAGE.SET_NAME('ONT','ONT_RT_EXC_PURGE_NOT_ALLOWED');
4642 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_retrobill_request_rec.name);
4643 OE_MSG_PUB.ADD;
4644
4645 ELSE
4646
4647 delete_retrobill_orders(p_purge_preview_orders,to_char(l_retrobill_request_rec.retrobill_request_id),l_retrobill_request_rec.name,l_requests_processed_per_call,l_headers_processed_per_call);
4648 l_requests_processed := l_requests_processed + l_requests_processed_per_call;
4649 l_headers_processed := l_headers_processed + l_headers_processed_per_call;
4650 END IF; --execution mode = 'EXECUTE'
4651 END LOOP;
4652 END IF;
4653 /* Save messages to Process Messages Table */
4654 OE_MSG_PUB.save_messages(l_concurrent_id);
4655
4656 /*Displaying messages from Processing Message table*/
4657 fnd_file.put_line(FND_FILE.OUTPUT,'Source/Order/Seq/Line Message');
4658 OPEN l_msg_cursor;
4659 LOOP
4660 FETCH l_msg_cursor
4661 INTO l_order_source_id
4662 , l_orig_sys_document_ref
4663 , l_change_sequence
4664 , l_orig_sys_line_ref
4665 , l_message_text;
4666 EXIT WHEN l_msg_cursor%NOTFOUND;
4667
4668 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_order_source_id)
4669 ||'/'||l_orig_sys_document_ref
4670 ||'/'||l_change_sequence
4671 ||'/'||l_orig_sys_line_ref
4672 ||' '||l_message_text);
4673 fnd_file.put_line(FND_FILE.OUTPUT,'');
4674 END LOOP;
4675
4676 IF l_debug_level > 0 THEN
4677 oe_debug_pub.add('Number of Requests Purged:' ||l_requests_processed);
4678 oe_debug_pub.add('Number of Orders Purged:' ||l_headers_processed);
4679 END IF;
4680
4681 fnd_file.put_line(FND_FILE.OUTPUT,'Number of Requests Purged:' ||l_requests_processed);
4682 fnd_file.put_line(FND_FILE.OUTPUT,'Number of Orders Purged:' ||l_headers_processed);
4683 oe_debug_pub.add('Exiting Oe_Retrobill_pvt.Oe_retrobill_Purge');
4684 commit;
4685
4686 EXCEPTION
4687 WHEN OTHERS THEN
4688 ROLLBACK;
4689 retcode := -1;
4690 oe_debug_pub.add('Error in Procedure Oe_Retrobill_Purge'||sqlerrm);
4691 Raise;
4692 END Oe_Retrobill_Purge;
4693 --retro}
4694 -- 3661895 Added the following procedure
4695 /*******************************************************************************************
4696 This function returns the sum of adjusted amount of bill only retrobill lines created before
4697 return retrobilled line for the corresponding list line id
4698 *******************************************************************************************/
4699 FUNCTION Get_Retrobill_Credited_Adj
4700 (
4701 p_price_adjustment_id IN NUMBER
4702 ) RETURN NUMBER
4703 AS
4704 --
4705 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4706 --
4707
4708 l_list_line_id NUMBER;
4709 l_sum_credied_amount NUMBER := 0;
4710 l_adjusted_amount NUMBER := 0;
4711
4712 BEGIN
4713
4714 IF l_debug_level > 0 THEN
4715 oe_debug_pub.add('ENTERING FUNCTION Get_Retrobill_Credited_Adj',1);
4716 oe_debug_pub.add('G_Retro_Bill_Only_Line_Tbl count:'||G_Retro_Bill_Only_Line_Tbl.count);
4717 END IF;
4718
4719 IF(p_price_adjustment_id is NOT NULL) THEN
4720 select list_line_id into l_list_line_id
4721 from oe_price_adjustments
4722 where price_adjustment_id = p_price_adjustment_id;
4723 END IF;
4724
4725 IF (l_list_line_id is NOT NULL) THEN
4726 -- Looping through the retrobill only lines and totalling the adjustments for the given list line id of Return retrobilled line
4727
4728 FOR I IN G_Retro_Bill_Only_Line_Tbl.first.. G_Retro_Bill_Only_Line_Tbl.last LOOP
4729 BEGIN
4730 SELECT adjusted_amount into l_adjusted_amount from oe_price_adjustments where
4731 header_id = G_Retro_Bill_Only_Line_Tbl(i).header_id and
4732 line_id = G_Retro_Bill_Only_Line_Tbl(i).line_id and
4733 list_line_id = l_list_line_id and
4734 applied_flag = 'Y' and
4735 retrobill_request_id is NULL;
4736
4737 l_sum_credied_amount := l_sum_credied_amount + nvl(l_adjusted_amount,0);
4738
4739 EXCEPTION
4740 WHEN NO_DATA_FOUND THEN
4741 NULL;
4742 WHEN OTHERS THEN
4743 oe_debug_pub.add('Exception occured:'||SQLERRM);
4744 RAISE;
4745 END;
4746 END LOOP;
4747 END IF;
4748
4749 IF l_debug_level > 0 THEN
4750 oe_debug_pub.add('l_sum_credied_amount:'||l_sum_credied_amount||'for list line id:'||l_list_line_id);
4751 END IF;
4752 RETURN nvl(l_sum_credied_amount,0);
4753
4754 EXCEPTION
4755 WHEN NO_DATA_FOUND THEN
4756 NULL;
4757 WHEN OTHERS THEN
4758 oe_debug_pub.add('Exception occured:'||SQLERRM);
4759 RAISE;
4760 END Get_Retrobill_Credited_Adj;
4761
4762 -- 3661895
4763 /********************************************************************************************************************************
4764 This procedure is called from OEXPINVB.pls while invoice interfacing the Return Retrobilled RMA.For interfacing the RMA against the
4765 original line we query return lines adjustments and deduct the credited amount of the Retrobill Only lines. For interfacing the RMA
4766 against Retrobill Bill only lines we query adjustments of Retrobill bill only Lines
4767 *********************************************************************************************************************************/
4768 PROCEDURE Get_Line_Adjustments
4769 (
4770 p_line_rec IN OE_Order_Pub.Line_Rec_Type
4771 ,x_line_adjustments OUT NOCOPY OE_Header_Adj_Util.Line_Adjustments_Tab_Type
4772 )
4773 IS
4774 --
4775 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4776 --
4777 l_adjustments_tbl OE_Header_Adj_Util.Line_Adjustments_Tab_Type;
4778 l_adj_rec OE_Header_Adj_Util.Line_Adjustments_Rec_Type;
4779 l_header_id NUMBER;
4780 l_line_id NUMBER;
4781
4782 BEGIN
4783
4784 IF l_debug_level > 0 THEN
4785 oe_debug_pub.add( 'ENTERING OE_HEADER_ADJ_UTIL.GET_LINE_ADJUSTMENTS' , 1 ) ;
4786 END IF;
4787
4788 IF(p_line_rec.retrobill_request_id = 0) THEN
4789 l_header_id := p_line_rec.header_id;
4790 l_line_id := p_line_rec.line_id;
4791 ELSE
4792 l_header_id := p_line_rec.orig_sys_document_ref;
4793 l_line_id := p_line_rec.orig_sys_line_ref;
4794 END IF;
4795
4796 IF l_debug_level > 0 THEN
4797 oe_debug_pub.add('HEADER ID:'||l_header_id);
4798 oe_debug_pub.add('LINE ID:'||l_line_id);
4799 END IF;
4800
4801 OE_Header_Adj_Util.Get_Line_Adjustments
4802 (p_header_id => l_header_id
4803 ,p_line_id => l_line_id
4804 ,x_line_adjustments => l_adjustments_tbl);
4805
4806 oe_debug_pub.add( ' p_line_rec.retrobill_request_id = '||p_line_rec.retrobill_request_id , 1 ) ;
4807 oe_debug_pub.add( ' l_adjustments_tbl.count = '|| l_adjustments_tbl.count , 1 ) ;
4808
4809 IF (p_line_rec.retrobill_request_id = 0) THEN
4810
4811 if l_adjustments_tbl.count > 0 then -- bug# 8435020 : add if condition
4812 FOR I IN l_adjustments_tbl.first..l_adjustments_tbl.last LOOP
4813 l_adj_rec := l_adjustments_tbl(i);
4814 l_adj_rec.unit_discount_amount := l_adj_rec.unit_discount_amount + nvl(Get_Retrobill_Credited_Adj(l_adj_rec.price_adjustment_id),0);
4815 x_line_adjustments(i).price_adjustment_id := l_adj_rec.price_adjustment_id;
4816 x_line_adjustments(i).adjustment_name := l_adj_rec.adjustment_name;
4817 x_line_adjustments(i).list_line_no := l_adj_rec.list_line_no;
4818 x_line_adjustments(i).adjustment_type_code := l_adj_rec.adjustment_type_code;
4819 x_line_adjustments(i).operand := l_adj_rec.operand;
4820 x_line_adjustments(i).arithmetic_operator := l_adj_rec.arithmetic_operator;
4821 x_line_adjustments(i).unit_discount_amount := l_adj_rec.unit_discount_amount;
4822 END LOOP;
4823 end if;
4824
4825 ELSE
4826
4827 if l_adjustments_tbl.count > 0 then -- bug# 8435020 : add if condition
4828 FOR I IN l_adjustments_tbl.first..l_adjustments_tbl.last LOOP
4829 l_adj_rec := l_adjustments_tbl(i);
4830 x_line_adjustments(i).price_adjustment_id := l_adj_rec.price_adjustment_id;
4831 x_line_adjustments(i).adjustment_name := l_adj_rec.adjustment_name;
4832 x_line_adjustments(i).list_line_no := l_adj_rec.list_line_no;
4833 x_line_adjustments(i).adjustment_type_code := l_adj_rec.adjustment_type_code;
4834 x_line_adjustments(i).operand := l_adj_rec.operand;
4835 x_line_adjustments(i).arithmetic_operator := l_adj_rec.arithmetic_operator;
4836 x_line_adjustments(i).unit_discount_amount := l_adj_rec.unit_discount_amount;
4837 END LOOP;
4838 end if;
4839
4840 END IF;
4841
4842 IF l_debug_level > 0 THEN
4843 oe_debug_pub.add( 'EXITING OE_HEADER_ADJ_UTIL.GET_LINE_ADJUSTMENTS' , 1 ) ;
4844 END IF;
4845
4846 END Get_Line_Adjustments;
4847
4848 End OE_RETROBILL_PVT;