1 PACKAGE QP_UTIL_PUB AUTHID CURRENT_USER AS
2 /* $Header: QPXRTCNS.pls 120.3 2010/09/17 11:01:43 jputta ship $ */
3
4 --GLOBAL Constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_LIMITS_UTIL_PUB';
7 G_MULTI_CURRENCY VARCHAR2(30);
8 G_MULTI_CURRENCY_USAGE VARCHAR2(30);
9 G_ROUNDING_OPTIONS VARCHAR2(30);
10 G_OE_UNIT_PRICE_ROUNDING VARCHAR2(30);
11 G_PRICE_LIST_ID NUMBER := 0;
12 G_CURRENCY_CODE VARCHAR2(15) := 'x';
13 G_PRICING_EFF_DATE DATE := trunc(sysdate);
14 G_ROUNDING_FACTOR NUMBER := '';
15
16 /***********************************************************************
17 Procedure to Reverse the Limit Balances and Transactions for a return
18 or cancellation.
19 ***********************************************************************/
20
21 TYPE qp_preq_lines_tbl_type IS TABLE OF qp_npreq_lines_tmp%ROWTYPE;
22 TYPE qp_preq_ldets_tbl_type IS TABLE OF qp_npreq_ldets_tmp%ROWTYPE;
23
24 Procedure Reverse_Limits (p_action_code IN VARCHAR2,
25 p_cons_price_request_code IN VARCHAR2,
26 p_orig_ordered_qty IN NUMBER DEFAULT NULL,
27 p_amended_qty IN NUMBER DEFAULT NULL,
28 p_ret_price_request_code IN VARCHAR2 DEFAULT NULL,
29 p_returned_qty IN NUMBER DEFAULT NULL,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_return_message OUT NOCOPY VARCHAR2);
32
33 TYPE ORDER_LINES_STATUS_REC_TYPE IS RECORD
34 (ALL_LINES_FLAG VARCHAR2(30),
35 SUMMARY_LINE_FLAG VARCHAR2(30),
36 CHANGED_LINES_FLAG VARCHAR2(1));
37
38 -- added third parameter for bug 3006670
39 -- [julin/4261562] added p_request_type_code for PTE/SS filter
40 Procedure Get_Order_Lines_Status(p_event_code IN VARCHAR2,
41 x_order_status_rec OUT NOCOPY ORDER_LINES_STATUS_REC_TYPE,
42 p_freight_call_flag IN VARCHAR2 := 'N',
43 p_request_type_code IN VARCHAR2 DEFAULT NULL);
44
45 -- Bug 7241731/7596981
46 -- introduced as part of custom hook for adv modifiers 9251187
47 Procedure Get_Order_Lines_Status(p_event_code IN VARCHAR2,
48 p_header_id in NUMBER,
49 p_line_id in NUMBER,
50 x_order_status_rec OUT NOCOPY ORDER_LINES_STATUS_REC_TYPE,
51 p_freight_call_flag IN VARCHAR2 := 'N',
52 p_request_type_code IN VARCHAR2 DEFAULT NULL);
53
54 PROCEDURE Get_Manual_All_Lines_Status(p_event_code IN VARCHAR2,
55 x_manual_all_lines_status OUT NOCOPY VARCHAR2);
56
57 TYPE currency_rec IS RECORD
58 (
59 currency_code VARCHAR2(15)
60 ,currency_name VARCHAR2(80)
61 ,currency_precision NUMBER
62 );
63
64 TYPE currency_code_tbl IS TABLE OF currency_rec INDEX BY BINARY_INTEGER;
65
66 TYPE price_list_rec IS RECORD
67 (
68 price_list_id NUMBER
69 ,name VARCHAR2(240)
70 ,description VARCHAR2(2000)
71 ,start_date_active DATE
72 ,end_date_active DATE
73 );
74
78 (
75 TYPE price_list_tbl IS TABLE OF price_list_rec INDEX BY BINARY_INTEGER;
76
77 TYPE price_lists_rec IS RECORD
79 price_list_id NUMBER
80 ,name VARCHAR2(240)
81 ,description VARCHAR2(2000)
82 ,rounding_factor NUMBER
83 ,start_date_active DATE
84 ,end_date_active DATE
85 );
86
87 TYPE price_lists_tbl IS TABLE OF price_lists_rec INDEX BY BINARY_INTEGER;
88
89 TYPE agreement_rec IS RECORD
90 (
91 agreement_name VARCHAR2(300)
92 ,agreement_id NUMBER
93 ,agreement_type VARCHAR2(30)
94 ,price_list_name VARCHAR2(240)
95 ,customer_name VARCHAR2(360)
96 ,payment_term_name VARCHAR2(15)
97 ,start_date_active DATE
98 ,end_date_active DATE
99 );
100
101 TYPE agreement_tbl IS TABLE OF agreement_rec INDEX BY BINARY_INTEGER;
102
103 PROCEDURE Validate_Price_list_Curr_code
104 (
105 l_price_list_id IN NUMBER
106 ,l_currency_code IN VARCHAR2
107 ,l_pricing_effective_date IN DATE
108 ,l_validate_result OUT NOCOPY VARCHAR2
109 );
110
111 PROCEDURE Get_Price_List
112 (
113 l_currency_code IN VARCHAR2
114 ,l_pricing_effective_date IN DATE
115 ,l_agreement_id IN NUMBER
116 ,l_blanket_reference_id IN VARCHAR2 DEFAULT NULL --Blanket Pricing
117 ,l_price_list_tbl OUT NOCOPY price_list_tbl
118 ,l_sold_to_org_id IN NUMBER DEFAULT NULL
119 );
120
121 PROCEDURE Get_Price_Lists
122 (
123 p_currency_code IN VARCHAR2 DEFAULT NULL
124 ,p_price_lists_tbl OUT NOCOPY price_lists_tbl
125 );
126
127 PROCEDURE Get_Agreement
128 (
129 p_sold_to_org_id IN NUMBER DEFAULT NULL
130 ,p_transaction_type_id IN NUMBER DEFAULT NULL
131 ,p_pricing_effective_date IN DATE
132 ,p_agreement_tbl OUT NOCOPY agreement_tbl
133 );
134
135 PROCEDURE Get_Currency
136 (
137 l_price_list_id IN NUMBER
138 ,l_pricing_effective_date IN DATE
139 ,l_currency_code_tbl OUT NOCOPY CURRENCY_CODE_TBL
140 );
141
142 -- round_price.p_operand_type could be 'A' for adjustment amount or 'S' for item price
143 PROCEDURE round_price
144 (
145 p_operand IN NUMBER
146 ,p_rounding_factor IN NUMBER
147 ,p_use_multi_currency IN VARCHAR2
148 ,p_price_list_id IN NUMBER
149 ,p_currency_code IN VARCHAR2
150 ,p_pricing_effective_date IN DATE
151 ,x_rounded_operand IN OUT NOCOPY NUMBER
152 ,x_status_code IN OUT NOCOPY VARCHAR2
153 ,p_operand_type IN VARCHAR2 default 'S'
154 );
155
156 -- called by pricing engine
157 FUNCTION get_rounding_factor
158 (
159 p_use_multi_currency IN VARCHAR2
160 ,p_price_list_id IN NUMBER
161 ,p_currency_code IN VARCHAR2
162 ,p_pricing_effective_date IN DATE
163 ) return NUMBER;
164
165 FUNCTION Basic_Pricing_Setup RETURN VARCHAR2;
166
167 PROCEDURE Reprice_Debug_Engine_Request
168 (
169 p_request_id IN NUMBER,
170 x_request_id OUT NOCOPY NUMBER,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_return_status_text OUT NOCOPY VARCHAR2
173 );
174
175 PROCEDURE Get_Price_List_Currency
176 (
177 p_price_list_id IN NUMBER
178 ,x_sql_string OUT NOCOPY VARCHAR2
179 );
180
181 --moved to QPXJUTLS.pls QP_JAVA_ENGINE_UTIL_PUB package
182 /*--'Y', JavaEngine is installed, 'N' is not installed.
183 FUNCTION Java_Engine_Installed RETURN VARCHAR2;
184 */
185 FUNCTION HVOP_Pricing_Setup RETURN VARCHAR2;
186
187 FUNCTION HVOP_Pricing_On RETURN VARCHAR2;
188
189 PROCEDURE RESET_HVOP_PRICING_ON;
190
191 TYPE attribute_rec IS RECORD
192 (
193 Attribute_Type VARCHAR2(30),
194 Context_Code VARCHAR2(30),
195 Attribute_Code VARCHAR2(30),
196 Operator VARCHAR2(30),
197 Attribute_Value_From VARCHAR2(240),
198 Attribute_Value_To VARCHAR2(240),
199 Context_Text VARCHAR2(240),
200 Attribute_Text VARCHAR2(80),
201 Attribute_Value_From_Text VARCHAR2(240)
202 );
203
204 TYPE attribute_tbl IS TABLE OF attribute_rec INDEX BY BINARY_INTEGER;
205
206 -- New procedure for bug 3118385
207 procedure Get_Attribute_Text(p_attributes_tbl IN OUT NOCOPY attribute_tbl);
208
209 -- This procedure fetchs price lists and modifier lists specific to a blanket.
210 -- i.e. pricing data with list_source_code of Blanket and orig_system_header_ref of this blanket header
211 procedure Get_Blanket_Pricelist_Modifier(
212 p_blanket_header_id IN NUMBER
213 ,x_price_list_tbl OUT NOCOPY QP_Price_List_PUB.Price_List_Tbl_Type
214 ,x_modifier_list_tbl OUT NOCOPY QP_Modifiers_PUB.Modifier_List_Tbl_Type
215 ,x_return_status OUT NOCOPY VARCHAR2
216 ,x_msg_count OUT NOCOPY NUMBER
217 ,x_msg_data OUT NOCOPY VARCHAR2
218 );
219
220 PROCEDURE Check_Pricing_Attributes (
221 P_Api_Version_Number IN NUMBER := 1,
222 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
223 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
224 P_Inventory_Id IN NUMBER := FND_API.G_MISS_NUM,
225 P_Price_List_Id IN NUMBER := FND_API.G_MISS_NUM,
226 X_Check_Return_Status_qp OUT NOCOPY VARCHAR2,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2);
230
231 PROCEDURE Check_Pricing_Attributes (
232 P_Api_Version_Number IN NUMBER := 1,
233 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
234 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
235 P_Inventory_Id IN NUMBER := FND_API.G_MISS_NUM,
236 P_Price_List_Id IN NUMBER := FND_API.G_MISS_NUM,
237 X_Check_Return_Status_qp OUT NOCOPY VARCHAR2,
238 x_msg_count OUT NOCOPY NUMBER,
239 x_msg_data OUT NOCOPY VARCHAR2);
240
241 /*--bug 3228829
242 OM needs API to update the lines_tmp table
243 this API will take care of updating i/f tables java engine is installed
244 and update temp tables when plsql engine is installed*/
245 PROCEDURE Update_Lines(p_update_type IN VARCHAR2, p_line_id IN NUMBER,
246 p_line_index IN NUMBER, p_priced_quantity IN NUMBER);
247
248 PROCEDURE Flex_Enabled_Status (p_flexfields_name IN VARCHAR2, x_status OUT NOCOPY VARCHAR2);
249
250
251 END QP_UTIL_PUB;
[Home] [Help]