1 PACKAGE QP_UTIL_PUB AS
2 /* $Header: QPXRTCNS.pls 120.1.12010000.2 2008/11/25 23:35:48 rbadadar 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 PROCEDURE Get_Manual_All_Lines_Status(p_event_code IN VARCHAR2,
47 x_manual_all_lines_status OUT NOCOPY VARCHAR2);
48
49 TYPE currency_rec IS RECORD
50 (
51 currency_code VARCHAR2(15)
52 ,currency_name VARCHAR2(80)
53 ,currency_precision NUMBER
54 );
55
56 TYPE currency_code_tbl IS TABLE OF currency_rec INDEX BY BINARY_INTEGER;
57
58 TYPE price_list_rec IS RECORD
59 (
60 price_list_id NUMBER
61 ,name VARCHAR2(240)
62 ,description VARCHAR2(2000)
63 ,start_date_active DATE
64 ,end_date_active DATE
65 );
66
67 TYPE price_list_tbl IS TABLE OF price_list_rec INDEX BY BINARY_INTEGER;
68
69 TYPE price_lists_rec IS RECORD
70 (
71 price_list_id NUMBER
72 ,name VARCHAR2(240)
73 ,description VARCHAR2(2000)
74 ,rounding_factor NUMBER
75 ,start_date_active DATE
76 ,end_date_active DATE
77 );
78
79 TYPE price_lists_tbl IS TABLE OF price_lists_rec INDEX BY BINARY_INTEGER;
80
81 TYPE agreement_rec IS RECORD
82 (
83 agreement_name VARCHAR2(300)
84 ,agreement_id NUMBER
85 ,agreement_type VARCHAR2(30)
86 ,price_list_name VARCHAR2(240)
87 ,customer_name VARCHAR2(360)
88 ,payment_term_name VARCHAR2(15)
89 ,start_date_active DATE
90 ,end_date_active DATE
91 );
92
93 TYPE agreement_tbl IS TABLE OF agreement_rec INDEX BY BINARY_INTEGER;
94
95 PROCEDURE Validate_Price_list_Curr_code
96 (
97 l_price_list_id IN NUMBER
98 ,l_currency_code IN VARCHAR2
99 ,l_pricing_effective_date IN DATE
100 ,l_validate_result OUT NOCOPY VARCHAR2
101 );
102
103 PROCEDURE Get_Price_List
104 (
105 l_currency_code IN VARCHAR2
106 ,l_pricing_effective_date IN DATE
107 ,l_agreement_id IN NUMBER
108 ,l_blanket_reference_id IN VARCHAR2 DEFAULT NULL --Blanket Pricing
109 ,l_price_list_tbl OUT NOCOPY price_list_tbl
110 ,l_sold_to_org_id IN NUMBER DEFAULT NULL
111 );
112
113 PROCEDURE Get_Price_Lists
114 (
115 p_currency_code IN VARCHAR2 DEFAULT NULL
116 ,p_price_lists_tbl OUT NOCOPY price_lists_tbl
117 );
118
119 PROCEDURE Get_Agreement
120 (
121 p_sold_to_org_id IN NUMBER DEFAULT NULL
122 ,p_transaction_type_id IN NUMBER DEFAULT NULL
123 ,p_pricing_effective_date IN DATE
124 ,p_agreement_tbl OUT NOCOPY agreement_tbl
125 );
126
127 PROCEDURE Get_Currency
128 (
129 l_price_list_id IN NUMBER
130 ,l_pricing_effective_date IN DATE
131 ,l_currency_code_tbl OUT NOCOPY CURRENCY_CODE_TBL
132 );
133
134 -- round_price.p_operand_type could be 'A' for adjustment amount or 'S' for item price
135 PROCEDURE round_price
136 (
137 p_operand IN NUMBER
138 ,p_rounding_factor IN NUMBER
139 ,p_use_multi_currency IN VARCHAR2
140 ,p_price_list_id IN NUMBER
141 ,p_currency_code IN VARCHAR2
142 ,p_pricing_effective_date IN DATE
143 ,x_rounded_operand IN OUT NOCOPY NUMBER
144 ,x_status_code IN OUT NOCOPY VARCHAR2
145 ,p_operand_type IN VARCHAR2 default 'S'
146 );
147
148 -- called by pricing engine
149 FUNCTION get_rounding_factor
150 (
151 p_use_multi_currency IN VARCHAR2
152 ,p_price_list_id IN NUMBER
153 ,p_currency_code IN VARCHAR2
154 ,p_pricing_effective_date IN DATE
155 ) return NUMBER;
156
157 FUNCTION Basic_Pricing_Setup RETURN VARCHAR2;
158
159 PROCEDURE Reprice_Debug_Engine_Request
160 (
161 p_request_id IN NUMBER,
162 x_request_id OUT NOCOPY NUMBER,
163 x_return_status OUT NOCOPY VARCHAR2,
164 x_return_status_text OUT NOCOPY VARCHAR2
165 );
166
167 PROCEDURE Get_Price_List_Currency
168 (
169 p_price_list_id IN NUMBER
170 ,x_sql_string OUT NOCOPY VARCHAR2
171 );
172
173 --moved to QPXJUTLS.pls QP_JAVA_ENGINE_UTIL_PUB package
174 /*--'Y', JavaEngine is installed, 'N' is not installed.
175 FUNCTION Java_Engine_Installed RETURN VARCHAR2;
176 */
177 FUNCTION HVOP_Pricing_Setup RETURN VARCHAR2;
178
179 FUNCTION HVOP_Pricing_On RETURN VARCHAR2;
180
181 PROCEDURE RESET_HVOP_PRICING_ON;
182
183 TYPE attribute_rec IS RECORD
184 (
185 Attribute_Type VARCHAR2(30),
186 Context_Code VARCHAR2(30),
187 Attribute_Code VARCHAR2(30),
188 Operator VARCHAR2(30),
189 Attribute_Value_From VARCHAR2(240),
190 Attribute_Value_To VARCHAR2(240),
191 Context_Text VARCHAR2(240),
192 Attribute_Text VARCHAR2(80),
193 Attribute_Value_From_Text VARCHAR2(240)
194 );
195
196 TYPE attribute_tbl IS TABLE OF attribute_rec INDEX BY BINARY_INTEGER;
197
198 -- New procedure for bug 3118385
199 procedure Get_Attribute_Text(p_attributes_tbl IN OUT NOCOPY attribute_tbl);
200
201 -- This procedure fetchs price lists and modifier lists specific to a blanket.
202 -- i.e. pricing data with list_source_code of Blanket and orig_system_header_ref of this blanket header
203 procedure Get_Blanket_Pricelist_Modifier(
204 p_blanket_header_id IN NUMBER
205 ,x_price_list_tbl OUT NOCOPY QP_Price_List_PUB.Price_List_Tbl_Type
206 ,x_modifier_list_tbl OUT NOCOPY QP_Modifiers_PUB.Modifier_List_Tbl_Type
207 ,x_return_status OUT NOCOPY VARCHAR2
208 ,x_msg_count OUT NOCOPY NUMBER
209 ,x_msg_data OUT NOCOPY VARCHAR2
210 );
211
212 PROCEDURE Check_Pricing_Attributes (
213 P_Api_Version_Number IN NUMBER := 1,
214 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
215 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
216 P_Inventory_Id IN NUMBER := FND_API.G_MISS_NUM,
217 P_Price_List_Id IN NUMBER := FND_API.G_MISS_NUM,
218 X_Check_Return_Status_qp OUT NOCOPY VARCHAR2,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER,
221 x_msg_data OUT NOCOPY VARCHAR2);
222
223 PROCEDURE Check_Pricing_Attributes (
224 P_Api_Version_Number IN NUMBER := 1,
225 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
226 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
227 P_Inventory_Id IN NUMBER := FND_API.G_MISS_NUM,
228 P_Price_List_Id IN NUMBER := FND_API.G_MISS_NUM,
229 X_Check_Return_Status_qp OUT NOCOPY VARCHAR2,
230 x_msg_count OUT NOCOPY NUMBER,
231 x_msg_data OUT NOCOPY VARCHAR2);
232
233 /*--bug 3228829
234 OM needs API to update the lines_tmp table
235 this API will take care of updating i/f tables java engine is installed
236 and update temp tables when plsql engine is installed*/
237 PROCEDURE Update_Lines(p_update_type IN VARCHAR2, p_line_id IN NUMBER,
238 p_line_index IN NUMBER, p_priced_quantity IN NUMBER);
239
240 PROCEDURE Flex_Enabled_Status (p_flexfields_name IN VARCHAR2, x_status OUT NOCOPY VARCHAR2);
241
242
243 END QP_UTIL_PUB;