[Home] [Help]
PACKAGE: APPS.QP_PRICE_LIST_PVT
Source
1 PACKAGE QP_PRICE_LIST_PVT AS
2 /* $Header: OEXVPRLS.pls 120.1 2005/06/09 05:23:54 appldev $ */
3
4 -- Start of Comments
5 -- API name Process_Price_List
6 -- Type Private
7 -- Function
8 --
9 -- Pre-reqs
10 --
11 -- Parameters
12 --
13 -- Version Current version = 1.0
14 -- Initial version = 1.0
15 --
16 -- Notes
17 --
18 -- End of Comments
19
20 PROCEDURE Process_Price_List
21 ( p_api_version_number IN NUMBER
22 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
23 , p_commit IN VARCHAR2 := FND_API.G_FALSE
24 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
25 , p_control_rec IN OE_GLOBALS.Control_Rec_Type :=
26 OE_GLOBALS.G_MISS_CONTROL_REC
27 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
28 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
29 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
30 , p_PRICE_LIST_rec IN OE_Price_List_PUB.Price_List_Rec_Type :=
31 OE_Price_List_PUB.G_MISS_PRICE_LIST_REC
32 , p_old_PRICE_LIST_rec IN OE_Price_List_PUB.Price_List_Rec_Type :=
33 OE_Price_List_PUB.G_MISS_PRICE_LIST_REC
34 , p_PRICE_LIST_LINE_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type :=
35 OE_Price_List_PUB.G_MISS_PRICE_LIST_LINE_TBL
36 , p_old_PRICE_LIST_LINE_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type :=
37 OE_Price_List_PUB.G_MISS_PRICE_LIST_LINE_TBL
38 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
39 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
40 );
41
42 -- Start of Comments
43 -- API name Lock_Price_List
44 -- Type Private
45 -- Function
46 --
47 -- Pre-reqs
48 --
49 -- Parameters
50 --
51 -- Version Current version = 1.0
52 -- Initial version = 1.0
53 --
54 -- Notes
55 --
56 -- End of Comments
57
58 PROCEDURE Lock_Price_List
59 ( p_api_version_number IN NUMBER
60 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
61 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
62 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
63 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
64 , p_PRICE_LIST_rec IN OE_Price_List_PUB.Price_List_Rec_Type :=
65 OE_Price_List_PUB.G_MISS_PRICE_LIST_REC
66 , p_PRICE_LIST_LINE_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type :=
67 OE_Price_List_PUB.G_MISS_PRICE_LIST_LINE_TBL
68 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
69 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
70 );
71
72 -- Start of Comments
73 -- API name Get_Price_List
74 -- Type Private
75 -- Function
76 --
77 -- Pre-reqs
78 --
79 -- Parameters
80 --
81 -- Version Current version = 1.0
82 -- Initial version = 1.0
83 --
84 -- Notes
85 --
86 -- End of Comments
87
88 PROCEDURE Get_Price_List
89 ( p_api_version_number IN NUMBER
90 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
91 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
92 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
93 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
94 , p_name IN VARCHAR2
95 , p_price_list_id IN NUMBER
96 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
97 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
98 );
99
100 -- Start of Comments
101 -- API name : Fetch_List_Price
102 -- Type : PRIVATE
103 -- Function : Return the list price of an item based on a specified
104 -- price list, item, and unit code.
105 -- Pre-reqs : None
106 -- Parameters :
107 -- IN : p_api_version_number IN NUMBER required
108 -- p_init_msg_list IN VARCHAR2 optional
109 -- default = FND_API.G_FALSE
110 -- p_validation_level IN NUMBER optional
111 -- default = FND_API.G_VALID_LEVEL_FULL
112 -- p_price_list_id IN NUMBER required
113 -- p_inventory_item_id IN NUMBER required
114 -- p_unit_code IN VARCHAR2 required
115 -- p_service_duration IN NUMBER optional
116 -- p_item_type_code IN VARCHAR2 optional
117 -- p_prc_method_code IN VARCHAR2 optional
118 -- p_pricing_attribute1 IN VARCHAR2 optional
119 -- p_pricing_attribute2 IN VARCHAR2 optional
120 -- p_pricing_attribute3 IN VARCHAR2 optional
121 -- p_pricing_attribute4 IN VARCHAR2 optional
122 -- p_pricing_attribute5 IN VARCHAR2 optional
123 -- p_pricing_attribute6 IN VARCHAR2 optional
124 -- p_pricing_attribute7 IN VARCHAR2 optional
125 -- p_pricing_attribute8 IN VARCHAR2 optional
126 -- p_pricing_attribute9 IN VARCHAR2 optional
127 -- p_pricing_attribute10 IN VARCHAR2 optional
128 -- p_pricing_attribute11 IN VARCHAR2 optional
129 -- p_pricing_attribute12 IN VARCHAR2 optional
130 -- p_pricing_attribute13 IN VARCHAR2 optional
131 -- p_pricing_attribute14 IN VARCHAR2 optional
132 -- p_pricing_attribute15 IN VARCHAR2 optional
133 -- p_base_price IN NUMBER optional
134 -- p_fetch_attempts IN NUMBER optional
135 -- default = G_PRC_LST_DEF_ATTEMPTS
136 -- OUT NOCOPY /* file.sql.39 change */ : p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2(1)
137 -- p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
138 -- p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2(2000)
139 -- p_price_list_id_out OUT NOCOPY /* file.sql.39 change */ NUMBER
140 -- p_prc_method_code_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2(4)
141 -- p_list_price OUT NOCOPY /* file.sql.39 change */ NUMBER
142 -- p_list_percent OUT NOCOPY /* file.sql.39 change */ NUMBER
143 -- p_rounding_factor OUT NOCOPY /* file.sql.39 change */ NUMBER
144 -- Version : Current Version 1.0
145 -- Initial Version 1.0
146 -- Notes :
147 -- End Of Comments
148
149
150
151 -- Global constants holding the maximum number of fetch attempts allowed.
152
153 G_PRC_LST_MAX_ATTEMPTS CONSTANT NUMBER := 2 ;
154 G_PRC_LST_DEF_ATTEMPTS CONSTANT NUMBER := 2 ;
155
156 -- Global constants representing pricing method codes
157
158 --G_PRC_METHOD_AMOUNT CONSTANT VARCHAR2(10) := 'AMNT';
159 --G_PRC_METHOD_PERCENT CONSTANT VARCHAR2(10) := 'PERC';
160
161 -- QP BEGIN Edited for QP datamodel
162 G_PRC_METHOD_AMOUNT CONSTANT VARCHAR2(30) := 'AMT';
163 G_PRC_METHOD_PERCENT CONSTANT VARCHAR2(30) := '%';
164 G_PRC_PRICE_LIST_LINE CONSTANT VARCHAR2(30) :='PLL';
165 -- QP END
166 -- Global constant Item type codes
167
168 G_PRC_ITEM_SERVICE CONSTANT VARCHAR2(10) := 'SERVICE';
169
170
171
172 PROCEDURE Fetch_List_Price
173 ( p_api_version_number IN NUMBER ,
174 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
175 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
176 p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
177 p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER ,
178 p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
179 p_price_list_id IN NUMBER := NULL ,
180 p_inventory_item_id IN NUMBER := NULL ,
181 p_unit_code IN VARCHAR2 := NULL ,
182 p_service_duration IN NUMBER := NULL ,
183 p_item_type_code IN VARCHAR2 := NULL ,
184 p_prc_method_code IN VARCHAR2 := NULL ,
185 p_pricing_attribute1 IN VARCHAR2 := NULL ,
186 p_pricing_attribute2 IN VARCHAR2 := NULL ,
187 p_pricing_attribute3 IN VARCHAR2 := NULL ,
188 p_pricing_attribute4 IN VARCHAR2 := NULL ,
189 p_pricing_attribute5 IN VARCHAR2 := NULL ,
190 p_pricing_attribute6 IN VARCHAR2 := NULL ,
191 p_pricing_attribute7 IN VARCHAR2 := NULL ,
192 p_pricing_attribute8 IN VARCHAR2 := NULL ,
193 p_pricing_attribute9 IN VARCHAR2 := NULL ,
194 p_pricing_attribute10 IN VARCHAR2 := NULL ,
195 p_pricing_attribute11 IN VARCHAR2 := NULL ,
196 p_pricing_attribute12 IN VARCHAR2 := NULL ,
197 p_pricing_attribute13 IN VARCHAR2 := NULL ,
198 p_pricing_attribute14 IN VARCHAR2 := NULL ,
199 p_pricing_attribute15 IN VARCHAR2 := NULL ,
200 p_base_price IN NUMBER := NULL ,
201 p_pricing_date IN DATE := NULL ,
202 p_fetch_attempts IN NUMBER := G_PRC_LST_DEF_ATTEMPTS ,
203 p_price_list_id_out OUT NOCOPY /* file.sql.39 change */ NUMBER ,
204 p_prc_method_code_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
205 p_list_price OUT NOCOPY /* file.sql.39 change */ NUMBER ,
206 p_list_percent OUT NOCOPY /* file.sql.39 change */ NUMBER ,
207 p_rounding_factor OUT NOCOPY /* file.sql.39 change */ NUMBER
208 );
209
210 Type product_rec_type is RECORD
211 (
212 l_rowid varchar2(2000) := NULL,
213 l_last_update_date date := NULL,
214 l_product_context varchar2(30) := NULL,
215 l_product_attr varchar2(30) := NULL,
216 l_product_attr_value varchar2(240) := NULL);
217
218 Type priceattr_rec_type is RECORD
219 (
220 l_rowid varchar2(2000) := NULL,
221 l_last_update_date date := NULL,
222 l_pricing_context varchar2(30) := NULL,
223 l_pricing_attr varchar2(30) := NULL,
224 l_pricing_attr_value_from varchar2(240) := NULL,
225 l_pricing_attr_value_to varchar2(240) := NULL);
226
227 Type priceattr_tbl_type is table of priceattr_rec_type
228 INDEX BY BINARY_INTEGER;
229
230 G_MISS_PRICEATTR_TBL priceattr_tbl_type;
231 G_MISS_PRODUCT_REC product_rec_type;
232
233 Function Get_Secondary_Price_List( p_list_header_id in number) return number;
234 Function Get_Inventory_Item_Id( p_list_line_id in NUMBER) return number;
235 Function Get_Customer_Item_Id( p_list_line_id in NUMBER) return number;
236 Function Get_Pricing_Attr_Context( p_list_line_id in NUMBER) return varchar2;
237 Function Get_Pricing_Attribute( p_list_line_id in NUMBER,
238 p_pricing_attr in varchar2) return varchar2;
239
240
241 Function Get_Price_Break_High ( p_list_line_id in number ) return number;
242 Function Get_Price_Break_Low ( p_list_line_id in number ) return number;
243 Function Get_Product_UOM_Code ( p_list_line_id in number ) return varchar2;
244 Function Does_Pricing_Attribute_Exist
245 (P_Product_Attr_Context In Varchar2,
246 p_Product_Attr In Varchar2,
247 P_Product_Attr_Val In Varchar2,
248 P_PRODUCT_UOM_CODE In Varchar2,
249 P_PRICING_ATTRIBUTE_CONTEXT In Varchar2,
250 P_PRICING_ATTRIBUTE In Varchar2,
251 P_PRICING_ATTR_VALUE_FROM In Varchar2,
252 P_PRICING_ATTR_VALUE_TO In Varchar2,
253 P_LIST_LINE_ID In Number,
254 P_LIST_HEADER_ID In Number
255 ) Return Varchar2;
256 Pragma Restrict_References(Does_Pricing_Attribute_Exist,WNDS,WNPS);
257 END QP_PRICE_LIST_PVT;