[Home] [Help]
PACKAGE: APPS.OE_ORDER_ADJ_PVT
Source
1 PACKAGE oe_order_adj_pvt AS
2 /* $Header: OEXVADJS.pls 120.3.12010000.1 2008/07/25 07:58:09 appldev ship $ */
3
4 G_STMT_NO Varchar2(2000);
5
6 -- Global constant holding the package name
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'oe_order_adj_pvt';
9 G_SEEDED_GSA_HOLD_ID CONSTANT NUMBER := 2;
10 G_SEEDED_PROM_ORDER_HOLD_ID CONSTANT NUMBER := 30; -- PROMOTIONS SEP/01
11 G_SEEDED_PROM_LINE_HOLD_ID CONSTANT NUMBER := 31; -- PROMOTIONS SEP/01
12
13 -- Header_Adjs
14
15 --btea begin
16 --profile option value for rounding flag
17 G_ROUNDING_FLAG VARCHAR2(1) :=nvl(Fnd_Profile.value('OE_UNIT_PRICE_ROUNDING'),'N');
18 --btea end
19
20 G_PASS_ALL_LINES VARCHAR2(30) := NULL;
21
22 Type rounding_factor_rec is Record
23 (List_Header_id number
24 ,rounding_factor number
25 );
26
27 g_rounding_factor_rec rounding_factor_rec;
28
29 Type Index_Tbl_Type is table of number
30 Index by Binary_Integer;
31
32 Type Char_Tbl_Type is table of Varchar2(5) Index by Binary_Integer;
33
34 G_MISS_INDEX_TBL Index_Tbl_Type;
35
36 Type Sorted_Adjustment_rec_Type is Record
37 (Adj_index Number
38 ,Pricing_group_sequence number
39 );
40
41 Type Sorted_Adjustment_Tbl_Type is table of Sorted_Adjustment_rec_Type
42 Index by Binary_Integer;
43
44 PROCEDURE Header_Adjs
45 ( p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE
46 , p_validation_level IN NUMBER
47 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
48 , p_x_Header_Adj_tbl IN OUT NOCOPY OE_Order_PUB.Header_Adj_Tbl_Type
49 , p_x_old_Header_Adj_tbl IN OUT NOCOPY OE_Order_PUB.Header_Adj_Tbl_Type
50 );
51
52 PROCEDURE HEader_Price_Atts
53 ( p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE
54 , p_validation_level IN NUMBER
55 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
56 , p_x_HEader_Price_Att_tbl IN OUT NOCOPY OE_Order_PUB.HEader_Price_Att_Tbl_Type
57 , p_x_old_HEader_Price_Att_tbl IN OUT NOCOPY OE_Order_PUB.HEader_Price_Att_Tbl_Type
58 );
59
60 PROCEDURE Header_Adj_Atts
61 ( p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE
62 , p_validation_level IN NUMBER
63 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
64 , p_x_Header_Adj_Att_tbl IN OUT NOCOPY OE_Order_PUB.Header_Adj_Att_Tbl_Type
65 , p_x_old_Header_Adj_Att_tbl IN OUT NOCOPY OE_Order_PUB.Header_Adj_Att_Tbl_Type
66 );
67
68 PROCEDURE Header_Adj_Assocs
69 ( p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE
70 , p_validation_level IN NUMBER
71 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
72 , p_x_Header_Adj_Assoc_tbl IN OUT NOCOPY OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
73 , p_x_old_Header_Adj_Assoc_tbl IN OUT NOCOPY OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
74 );
75
76 PROCEDURE Line_Adjs
77 ( p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE
78 , p_validation_level IN NUMBER
79 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
80 , p_x_Line_Adj_tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Tbl_Type
81 , p_x_old_Line_Adj_tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Tbl_Type
82 );
83
84 PROCEDURE Line_Price_Atts
85 ( p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE
86 , p_validation_level IN NUMBER
87 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
88 , p_x_Line_Price_Att_tbl IN OUT NOCOPY OE_Order_PUB.Line_Price_Att_Tbl_Type
89 , p_x_old_Line_Price_Att_tbl IN OUT NOCOPY OE_Order_PUB.Line_Price_Att_Tbl_Type
90 );
91
92 PROCEDURE Line_Adj_Atts
93 ( p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE
94 , p_validation_level IN NUMBER
95 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
96 , p_x_Line_Adj_Att_tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Tbl_Type
97 , p_x_old_Line_Adj_Att_tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Tbl_Type
98 );
99
100 PROCEDURE Line_Adj_Assocs
101 ( p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE
102 , p_validation_level IN NUMBER
103 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
104 , p_x_Line_Adj_Assoc_tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
105 , p_x_old_Line_Adj_Assoc_tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
106 );
107
108 procedure calculate_adjustments(
109 x_return_status out nocopy varchar2,
110 p_line_id number default null,
111 p_header_id number Default null,
112 p_Request_Type_Code varchar2 ,
113 p_Control_Rec QP_PREQ_GRP.CONTROL_RECORD_TYPE,
114 x_req_line_tbl out nocopy QP_PREQ_GRP.LINE_TBL_TYPE,
115 x_Req_qual_tbl out nocopy QP_PREQ_GRP.QUAL_TBL_TYPE,
116 x_Req_line_attr_tbl out nocopy QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
117 x_Req_LINE_DETAIL_tbl out nocopy QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
118 x_Req_LINE_DETAIL_qual_tbl out nocopy QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE,
119 x_Req_LINE_DETAIL_attr_tbl out nocopy QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE,
120 x_Req_related_lines_tbl out nocopy QP_PREQ_GRP.RELATED_LINES_TBL_TYPE
121 ,p_use_current_header in Boolean Default FALSE
122 ,p_write_to_db Boolean Default TRUE
123 ,x_any_frozen_line out nocopy Boolean
124 ,x_Header_Rec out nocopy oe_Order_Pub.Header_REc_Type
125 ,x_line_Tbl in out nocopy oe_Order_Pub.Line_Tbl_Type
126 ,p_honor_price_flag VARCHAR2 Default 'Y'
127 ,p_multiple_events in VARCHAR2 Default 'N'
128 ,p_action_code in VARCHAR2 Default 'NONE'
129 );
130
131 procedure process_adjustments
132 (
133 p_request_type_code varchar2,
134 x_return_status out nocopy Varchar2,
135 p_Req_Control_Rec QP_PREQ_GRP.Control_record_type,
136 p_req_line_tbl QP_PREQ_GRP.line_tbl_type,
137 p_Req_qual_tbl QP_PREQ_GRP.qual_tbl_type,
138 p_Req_line_attr_tbl QP_PREQ_GRP.line_attr_tbl_type,
139 p_Req_Line_Detail_tbl QP_PREQ_GRP.line_detail_tbl_type,
140 p_Req_Line_Detail_Qual_tbl QP_PREQ_GRP.line_detail_qual_tbl_type,
141 p_Req_Line_Detail_Attr_tbl QP_PREQ_GRP.line_detail_attr_tbl_type,
142 p_Req_related_lines_tbl QP_PREQ_GRP.related_lines_tbl_type
143 ,p_write_to_db Boolean
144 ,p_any_frozen_line in Boolean
145 ,x_line_Tbl in out nocopy oe_Order_Pub.Line_Tbl_Type
146 ,p_header_rec oe_Order_Pub.header_rec_type
147 ,p_multiple_events in Varchar2 Default 'N'
148 ,p_honor_price_flag in Varchar2 Default 'Y' --bug 2503186
149 );
150
151 Procedure Price_line(
152 X_Return_Status out nocopy Varchar2
153 ,p_Line_id Number Default Null
154 ,p_Header_id Number DEfault Null
155 ,p_Request_Type_code Varchar2
156 ,p_Control_Rec QP_PREQ_GRP.control_record_type
157 ,p_write_to_db Boolean DEFAULT TRUE
158 ,p_request_rec OE_Order_PUB.request_rec_type default oe_order_pub.G_MISS_REQUEST_REC
159 ,x_line_Tbl in out nocopy oe_Order_Pub.Line_Tbl_Type
160 ,p_honor_price_flag VARCHAR2 Default 'Y'
161 ,p_multiple_events Varchar2 default 'N'
162 ,p_action_code VARCHAR2 Default Null
163 );
164
165 Procedure Price_Adjustments(
166 X_Return_Status out nocopy Varchar2
167 ,p_Header_id Number DEfault null
168 ,p_Line_id Number DEfault null
169 ,p_request_type_code varchar2
170 ,p_request_rec OE_Order_PUB.request_rec_type default oe_order_pub.G_MISS_REQUEST_REC
171
172 );
173
174 procedure price_action
175 (
176 p_selected_records Oe_Globals.Selected_Record_Tbl
177 ,P_price_level varchar2
178 ,p_header_id Number Default Null
179 ,x_Return_Status out nocopy varchar2
180 ,x_msg_count out nocopy number
181 ,x_msg_data out nocopy varchar2
182 );
183
184
185 /* For Backward Compatibility */
186 procedure price_action
187 (p_HEader_count Number
188 ,p_HEader_list varchar2
189 ,p_line_count number
190 ,p_line_List Varchar2
191 ,P_price_level varchar2
192 ,x_Return_Status out nocopy varchar2
193 ,x_msg_count out nocopy number
194 ,x_msg_data out nocopy varchar2
195 );
196
197
198 Type quote_header_Rec_Type Is Record
199 ( accounting_rule_id NUMBER
200 , agreement_id NUMBER
201 , booked_flag VARCHAR2(1)
202 , cancelled_flag VARCHAR2(1)
203 , context VARCHAR2(30)
204 , conversion_rate NUMBER
205 , conversion_rate_date DATE
206 , conversion_type_code VARCHAR2(30)
207 , customer_preference_set_code VARCHAR2(30)
208 , cust_po_number VARCHAR2(50)
209 , deliver_to_contact_id NUMBER
210 , deliver_to_org_id NUMBER
211 , demand_class_code VARCHAR2(30)
212 , expiration_date DATE
213 , fob_point_code VARCHAR2(30)
214 , freight_carrier_code VARCHAR2(30)
215 , freight_terms_code VARCHAR2(30)
216 , invoice_to_contact_id NUMBER
217 , invoice_to_org_id NUMBER
218 , invoicing_rule_id NUMBER
219 , order_category_code VARCHAR2(30)
220 , ordered_date DATE
221 , order_date_type_code VARCHAR2(30)
222 , order_number NUMBER
223 , order_source_id NUMBER
224 , order_type_id NUMBER
225 , org_id NUMBER
226 , payment_term_id NUMBER
227 , price_list_id NUMBER
228 , pricing_date DATE
229 , request_date DATE
230 , request_id NUMBER
231 , salesrep_id NUMBER
232 , sales_channel_code VARCHAR2(30)
233 , shipment_priority_code VARCHAR2(30)
234 , shipping_method_code VARCHAR2(30)
235 , ship_from_org_id NUMBER
236 , ship_to_contact_id NUMBER
237 , ship_to_org_id NUMBER
238 , sold_from_org_id NUMBER
239 , sold_to_contact_id NUMBER
240 , sold_to_org_id NUMBER
241 , source_document_id NUMBER
242 , source_document_type_id NUMBER
243 , transactional_curr_code VARCHAR2(15)
244 , drop_ship_flag VARCHAR2(1)
245 , customer_payment_term_id NUMBER
246 , payment_type_code VARCHAR2(30)
247 , payment_amount NUMBER
248 , credit_card_code VARCHAR2(80)
249 , credit_card_holder_name VARCHAR2(80)
250 , credit_card_number VARCHAR2(80)
251 , marketing_source_code_id NUMBER
252 );
253
254
255 TYPE quote_line_Rec IS RECORD
256 ( actual_arrival_date DATE
257 , actual_shipment_date DATE
258 , agreement_id NUMBER
259 , cancelled_quantity NUMBER
260 , cust_po_number VARCHAR2(50)
261 , deliver_to_contact_id NUMBER
262 , deliver_to_org_id NUMBER
263 , freight_carrier_code VARCHAR2(30)
264 , freight_terms_code VARCHAR2(30)
265 , intermed_ship_to_org_id NUMBER
266 , intermed_ship_to_contact_id NUMBER
267 , inventory_item_id NUMBER
268 , invoice_interface_status_code VARCHAR2(30)
269 , invoice_to_contact_id NUMBER
270 , invoice_to_org_id NUMBER
271 , ordered_item VARCHAR2(2000)
272 , item_type_code VARCHAR2(30)
273 , line_id NUMBER
274 , line_type_id NUMBER
275 , ordered_quantity NUMBER
276 , ordered_quantity2 NUMBER
277 , order_quantity_uom VARCHAR2(3)
278 , ordered_quantity_uom2 VARCHAR2(3)
279 , org_id NUMBER
280 , payment_term_id NUMBER
281 , price_list_id NUMBER
282 , pricing_context VARCHAR2(240)
283 , pricing_date DATE
284 , pricing_quantity NUMBER
285 , pricing_quantity_uom VARCHAR2(3)
286 , project_id NUMBER
287 , promise_date DATE
288 , salesrep_id NUMBER
289 , schedule_arrival_date DATE
290 , schedule_ship_date DATE
291 , ship_from_org_id NUMBER
292 , ship_to_org_id NUMBER
293 , sold_to_org_id NUMBER
294 , sold_from_org_id NUMBER
295 , source_document_type_id NUMBER
296 , task_id NUMBER
297 , tax_code VARCHAR2(50)
298 , unit_list_price NUMBER
299 , unit_selling_price NUMBER
300 , order_source_id NUMBER
301 , customer_payment_term_id NUMBER
302 , ordered_item_id NUMBER
303 , item_identifier_type VARCHAR2(25)
304 , unit_list_percent NUMBER
305 , unit_selling_percent NUMBER
306 , unit_percent_base_price NUMBER
307 , service_number NUMBER
308 , revenue_amount NUMBER
309 , status_code VARCHAR2(30)
310 , status_text VARCHAR2(240)
311 );
312
313 Type quote_line_tbl_type is Table of quote_line_rec index by binary_integer;
314
315
316 Type key_rec_type is record
317 (db_start NUMBER DEFAULT NULL,
318 db_end NUMBER DEFAULT NULL
319 );
320
321 Type key_tbl_type is table of key_rec_type index by binary_integer;
322
323 G_MISS_KEY_TBL key_tbl_type;
324
325 PROCEDURE Get_Quote(p_quote_header in quote_header_rec_type,
326 p_quote_line_tbl in quote_line_tbl_type,
327 p_request_type_code in Varchar2,
328 p_event in Varchar2 default 'BATCH',
329 x_quote_line_tbl out nocopy quote_line_tbl_type,
330 x_return_status out nocopy Varchar2,
331 x_return_status_text out nocopy Varchar2);
332 -- sgowtham
333 Type Manual_Adj_Type is Record
334 (modifier_number Varchar2(240)
335 ,list_line_type_code Varchar2(30)
336 ,operator Varchar2(30)
337 ,operand Number
338 ,list_line_id Number
339 ,list_header_id Number
340 ,pricing_phase_id Number
341 ,automatic_flag Varchar2(1)
342 ,modifier_level_code Varchar2(30)
343 ,override_flag Varchar2(1)
344 ,adjusted_amount Number
345 ,charge_type_code Varchar2(30)
346 ,charge_subtype_code Varchar2(30)
347 ,PRICE_BREAK_TYPE_CODE VARCHAR2(30) --bucket man
348 ,PRICING_GROUP_SEQUENCE NUMBER
349 );
350
351 Type Manual_Adj_Tbl_Type Is Table of Manual_Adj_Type index by Binary_Integer;
352
353 Procedure Create_Manual_Adjustments(p_line_id In Number);
354
355 -- Bug 1713035
356 -- Introduced a new parameter p_line_level to indicate that
357 -- this procedure is being called from sales order lines.
358 -- if p_line_level='Y' show only line/linegroup level manual adjustments
359 -- else show all other adjustments too
360
361 Procedure Get_Manual_Adjustments (
362 p_header_id in number Default Null,
363 p_line_id in number Default Null,
364 p_line_rec in oe_Order_Pub.Line_Rec_Type Default oe_order_pub.g_miss_line_rec,
365 p_level in Varchar2 default 'LINE',
366 p_pbh_mode in Varchar2 default 'CHILD',
367 p_cross_order in Varchar2 Default 'N',
368 p_line_level in Varchar2 Default 'N',
369 x_manual_adj_tbl out Nocopy Oe_Order_Adj_Pvt.Manual_Adj_Tbl_Type,
370 x_return_status out nocopy Varchar2,
371 x_header_id out nocopy Number,
372 p_freight_flag in boolean default false,
373 p_called_from in varchar2 default null
374 );
375
376 Procedure Promotion_Put_Hold( -- PROMOTIONS SEP/01
377 p_header_id in Number default null,
378 p_line_id in Number default null
379 );
380
381 PROCEDURE Insert_Adj_Assocs
382 (p_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
383 );
384
385 PROCEDURE Insert_Adj_Atts
386 (p_Line_Adj_attribs_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type
387 );
388
389
390 Procedure copy_Header_to_request(
391 p_header_rec OE_Order_PUB.Header_Rec_Type
392 ,px_req_line_tbl in out nocopy QP_PREQ_GRP.LINE_TBL_TYPE
393 ,p_Request_Type_Code varchar2
394 ,p_calculate_price_flag varchar2
395 );
396
397
398 Procedure copy_Line_to_request(
399 p_Line_rec OE_Order_PUB.Line_Rec_Type
400 ,px_req_line_tbl in out nocopy QP_PREQ_GRP.LINE_TBL_TYPE
401 ,p_pricing_event varchar2
402 ,p_Request_Type_Code varchar2
403 ,p_honor_price_flag VARCHAR2 Default 'Y'
404 );
405
406 Procedure copy_attribs_to_Req(
407 p_line_index number
408 ,p_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
409 ,p_qualifier_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
410 ,px_Req_line_attr_tbl in out nocopy QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
411 ,px_Req_qual_tbl in out nocopy QP_PREQ_GRP.QUAL_TBL_TYPE
412 );
413
414
415 Procedure Reset_Fields(p_line_rec in Oe_Order_Pub.Line_Rec_Type);
416 -- bug 6718566
417 Procedure GET_MANUAL_ADV_STATUS(p_event_code IN VARCHAR2);
418
419 end oe_order_adj_pvt;