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