1 PACKAGE OKC_PRICE_PVT AS
2 /* $Header: OKCRPRES.pls 120.2 2006/02/28 14:52:59 smallya noship $ */
3 -------------------------------------------------------------------------
4 -- GLOBAL_LSE_REC_TYPE holds the values that will be used in mapping line style values
5 -- to QP Qulaifiers and Pricing Attributes
6 -- CURRENT_SOURCE - The OKX object from which the value is coming
7 -- SOURCE_VALUE - The actual value
8 -------------------------------------------------------------------------
9 TYPE GLOBAL_LSE_REC_TYPE IS RECORD
10 (
11 current_source varchar2(30),
12 source_value varchar2(200)
13 );
14 -------------------------------------------------------------------------
15 -- GLOBAL_RPRLE_REC_TYPE holds the values that will be used in mapping rule values
16 -- to QP Qulaifiers and Pricing Attributes
17 -- CODE - The lookup code of the rule or party role being represented
18 -- CURRENT_SOURCE - The OKX object from which the value is coming
19 -- SOURCE_VALUE - The actual value
20 -------------------------------------------------------------------------
21 TYPE GLOBAL_RPRLE_REC_TYPE IS RECORD
22 (
23 code varchar2(90),
24 current_source varchar2(30),
25 source_value varchar2(200)
26 );
27
28 -------------------------------------------------------------------------
29 -- CLE_PRICE_REC_TYPE holds the price for the priced line
30 -- ID - Line Id
31 -- List_Price - Price of the line before adjustments
32 -- NEGOTIATED_AMT - Price of the line after adjustments
33 -------------------------------------------------------------------------
34 TYPE CLE_PRICE_REC_TYPE IS RECORD
35 (
36 ID NUMBER DEFAULT 0,
37 pi_bpi VARCHAR2(1) DEFAULT 'P', -- possible values 'P'/'B'
38 QTY NUMBER,
39 UOM_CODE varchar2(5),
40 CURRENCY VARCHAR2(15),
41 object_code varchar2(30),
42 id1 varchar2(40),
43 id2 varchar2(200),
44 LINE_NUM varchar2(2000),
45 LIST_PRICE NUMBER,
46 UNIT_PRICE NUMBER,
47 NEGOTIATED_AMT NUMBER,
48 PRICELIST_ID NUMBER,
49 PRICING_DATE DATE,
50 LIST_LINE_ID NUMBER,
51 RET_CODE varchar2(30),
52 RET_STS varchar2(1) DEFAULT 'S'
53 );
54
55
56 -------------------------------------------------------------------------
57 -- MANUAL_ADJ_REC_TYPE holds the specs of the qualifying modifier
58 -------------------------------------------------------------------------
59 Type Manual_Adj_REC_Type is Record
60 (modifier_number Varchar2(240),
61 list_line_type_code Varchar2(30),
62 operator Varchar2(30),
63 operand NUMBER,
64 list_line_id NUMBER,
65 list_header_id NUMBER,
66 pricing_phase_id NUMBER,
67 automatic_flag Varchar2(1),
68 modifier_level_code Varchar2(30),
69 override_flag Varchar2(1),
70 applied_flag Varchar2(1),
71 modifier_mechanism_type_code varchar2(30),
72 range_break_quantity NUMBER,
73 line_detail_index NUMBER
74 );
75
76 -------------------------------------------------------------------------
77 -- REQ_LINE_REC_TYPE holds the specs of the lines making a request line
78 -------------------------------------------------------------------------
79 Type LINE_REC_Type is Record
80 (id NUMBER,
81 QTY NUMBER,
82 CURRENCY VARCHAR2(15),
83 P_YN VARCHAR2(1) DEFAULT 'N',
84 PI_YN VARCHAR2(1) DEFAULT 'N',
85 BPI_YN VARCHAR2(1) DEFAULT 'N',
86 UOM_CODE VARCHAR2(3),
87 object_code varchar2(30),
88 id1 varchar2(40),
89 id2 varchar2(200),
90 pricelist_id number,
91 updated_price number,
92 unit_price number,
93 service_yn VARCHAR2(1) DEFAULT 'N',
94 pricing_date DATE
95 );
96
97 ----------------------------------------------------------------------------
98 ----OKC_CONTROL_REC_TYPE holds the control rec to be sent to price_request as well other parameters.
99 ----------------------------------------------------------------------------
100 TYPE OKC_CONTROL_REC_TYPE is Record
101 (
102 QP_CONTROL_REC QP_PREQ_GRP.CONTROL_RECORD_TYPE,
103 p_Request_Type_Code VARCHAR2(3),
104 p_negotiated_changed varchar2(1) DEFAULT 'N', --possible values 'Y','N'
105 p_level VARCHAR2(2) DEFAULT 'L',--possible values 'L' lines only,'QA' From QA, 'H' Header and lines
106 p_calc_flag varchar2(1) DEFAULT 'B', --possible values
107 --'B' (search and calculate -Apply the existing manual adj. and get new automatics and calculate price),
108 --'C' (calculate only- Recalculate the price based upon already selected adjustments)
109 -- 'S' (Search only- Do not recalculate the price. Just get the new adjustments available)
110 p_config_yn varchar2(1) DEFAULT 'N', --possible values
111 -- 'Y' Configurator call. donot save the price adjustments data
112 -- 'N' not configurator call
113 -- 'S' configurator call. save the price adjustments data
114 p_top_model_id number
115 );
116
117 ----------------------------------------------------------------------------
118 -- -- TABLE TYPES---------------------------------------------------------
119 ----------------------------------------------------------------------------
120 TYPE GLOBAL_LSE_TBL_TYPE is TABLE of GLOBAL_LSE_REC_TYPE INDEX BY BINARY_INTEGER;
121 TYPE GLOBAL_RPRLE_TBL_TYPE is TABLE of GLOBAL_RPRLE_REC_TYPE INDEX BY BINARY_INTEGER;
122 TYPE NUM_TBL_TYPE is TABLE of NUMBER INDEX BY BINARY_INTEGER;
123 TYPE CLE_PRICE_TBL_TYPE is TABLE of CLE_PRICE_REC_TYPE INDEX BY BINARY_INTEGER;
124 Type MANUAL_Adj_Tbl_Type Is Table of Manual_Adj_REC_Type INDEX by BINARY_INTEGER;
125 Type LINE_Tbl_Type Is Table of LINE_REC_Type INDEX by BINARY_INTEGER;
126
127 ----------------------------------------------------------------------------
128 -- Global Variables-----------------------------------------------------
129
130 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKC_QP_INT_PVT';
131 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
132 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
133 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
134 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
135 G_SOME_LINE_ERRORED varchar2(1) := 'P';
136
137 --G_REQUEST_TYPE_CODE CONSTANT VARCHAR2(3) := 'OKC';
138 --G_LSE_TBL GLOBAL_LSE_TBL_TYPE;
139 --G_RUL_TBL GLOBAL_RPRLE_TBL_TYPE;
140 --G_PRLE_TBL GLOBAL_RPRLE_TBL_TYPE;
141
142 --------------------------------------------------------------------
143 --FUNCTION - GET_LSE_SOURCE_VALUE
144 -- This function is used in mapping of attributes between QP and OKC
145 -- The calls to this function will be made by QP Engine to get values for
146 --various Qualifiers and Pricing Attributes
147 -- p_lse_tbl - Global Table holding various OKX_SOURCES and their values for lse
148 -- p_registered_source - The source for which value should be returned
149 -- Returns the value for the p_registered_source
150 ----------------------------------------------------------------------------
151 FUNCTION Get_LSE_SOURCE_VALUE (
152 p_lse_tbl IN global_lse_tbl_type,
153 p_registered_source IN VARCHAR2)
154 RETURN VARCHAR2;
155
156 -----------------------------------------------------------------------------
157 --FUNCTION - GET_RUL_SOURCE_VALUE
158 -- This function is used in mapping of attributes between QP and OKC
159 -- The calls to this function will be made by QP Engine to get values for
160 --various Qualifiers and Pricing Attributes
161 -- p_rul_tbl - Global Table holding various OKX_SOURCES and their values for rules
162 -- p_registered_code - The rule code for which value should be returned
163 -- p_registered_source - The source for which value should be returned
164 -- Returns the value for the p_registered_source+p_registered_code
165 ----------------------------------------------------------------------------
166 FUNCTION Get_RUL_SOURCE_VALUE (
167 p_rul_tbl IN global_rprle_tbl_type,
168 p_registered_code IN varchar2,
169 p_registered_source IN VARCHAR2)
170 RETURN VARCHAR2;
171
172 ------------------------------------------------------------------------------
173 --FUNCTION - GET_PRLE_SOURCE_VALUE
174 -- This function is used in mapping of attributes between QP and OKC
175 -- The calls to this function will be made by QP Engine to get values for
176 --various Qualifiers and Pricing Attributes
177 -- p_prle_tbl - Global Table holding various OKX_SOURCES and their values for rules
178 -- p_registered_role - The role code for which value should be returned
179 -- p_registered_source - The source for which value should be returned
180 -- Returns the value for the p_registered_source+p_registered_role
181 ----------------------------------------------------------------------------
182 FUNCTION Get_PRLE_SOURCE_VALUE (
183 p_prle_tbl IN global_rprle_tbl_type,
184 p_registered_code IN varchar2,
185 p_registered_source IN VARCHAR2)
186 RETURN VARCHAR2;
187
188
189 ----------------------------------------------------------------------------
190 -- PROCEDURE BUILD_CHR_CONTEXT
191 -- This procedure will populate the global table with the data sources
192 -- and values for them defined at header level
193 ----------------------------------------------------------------------------
194 PROCEDURE BUILD_CHR_CONTEXT(
195 p_api_version IN NUMBER DEFAULT 1,
196 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
197 p_request_type_code IN VARCHAR2 DEFAULT 'OKC',
198 p_chr_id IN NUMBER ,
199 p_pricing_type IN VARCHAR2 DEFAULT 'H',
200 p_line_index IN NUMBER DEFAULT 1,
201 x_pricing_contexts_Tbl OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
202 x_qualifier_contexts_Tbl OUT NOCOPY QP_PREQ_GRP.QUAL_TBL_TYPE,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2) ;
206 ----------------------------------------------------------------------------
207 -- PROCEDURE BUILD_CLE_CONTEXT
208 -- This procedure will populate the global table with the data sources
209 -- and values for them defined at line level
210 -- p_cle_id - The Priced Line Id.
211 ----------------------------------------------------------------------------
212 PROCEDURE BUILD_CLE_CONTEXT(
213 p_api_version IN NUMBER DEFAULT 1,
214 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
215 p_request_type_code IN VARCHAR2 DEFAULt 'OKC',
216 p_chr_id IN NUMBER,
217 P_line_tbl IN line_TBL_TYPE,
218 p_pricing_type IN VARCHAR2 DEFAULT 'L',
219 p_line_index IN NUMBER DEFAULT 1,
220 p_service_price IN VARCHAR2 DEFAULT 'N',
221 p_service_price_list IN VARCHAR2 DEFAULT NULL,
222 x_pricing_contexts_Tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
223 x_qualifier_contexts_Tbl IN OUT NOCOPY QP_PREQ_GRP.QUAL_TBL_TYPE,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER,
226 x_msg_data OUT NOCOPY VARCHAR2);
227 ---------------------------------------------------------------------------
228 --Procedure - get_line_ids
229 -- This Procedure will return the ids of the line that will make a request line
230 --p_cle_id - Id of the priced line
231 --x_line_tbl- This table will hold the line ids rec for all the lines that
232 -- make a request line.For related lines, it will hold both the PI as well BPI
233 ----------------------------------------------------------------------------
234 Procedure get_line_ids (p_chr_id NUMBER,
235 p_cle_id NUMBER DEFAULT NULL , --- priced line id
236 x_return_status IN OUT NOCOPY varchar2,
237 x_line_tbl OUT NOCOPY line_TBL_TYPE,
238 x_bpi_ind OUT NOCOPY NUMBER ,
239 x_pi_ind OUT NOCOPY NUMBER
240 );
241
242 ----------------------------------------------------------------------------
243 -- CALCULATE_PRICE
244 -- This procedure will calculate the price for the sent in line/header
245 -- px_cle_price_tbl returns the priced line ids and thier prices
246 -- p_level tells whether line level or header level or QA
247 -- possible value 'L' line only,'H' header and lines ,'Q' From QA DEFAULT 'L'
248 --p_calc_flag 'B'(Both -calculate and search),'C'(Calculate Only), 'S' (Search only)
249 ----------------------------------------------------------------------------
250 PROCEDURE CALCULATE_price(
251 p_api_version IN NUMBER DEFAULT 1,
252 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
253 p_CHR_ID IN NUMBER,
254 p_Control_Rec IN OKC_CONTROL_REC_TYPE,
255 px_req_line_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
256 px_Req_qual_tbl IN OUT NOCOPY QP_PREQ_GRP.QUAL_TBL_TYPE,
257 px_Req_line_attr_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
258 px_Req_LINE_DETAIL_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
259 px_Req_LINE_DETAIL_qual_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE,
260 px_Req_LINE_DETAIL_attr_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE,
261 px_Req_RELATED_LINE_TBL IN OUT NOCOPY QP_PREQ_GRP.RELATED_LINES_TBL_TYPE,
262 px_CLE_PRICE_TBL IN OUT NOCOPY CLE_PRICE_TBL_TYPE,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2);
266
267 --------------------------------------------------------------------------
268 -- Update_Contract_price
269 -- This procedure will calculate the price for all the Priced lines in a contract
270 -- while calculating whether header level adjustments are to be considrerd/data updated
271 -- or not will be taken care of by px_control_rec.p_level
272 -- (possible values 'L' line,'H' header and lines,'Q' from QA)
273 -- p_chr_id - id of the header
274 -- x_chr_net_price - estimated amount on header
275
276 ----------------------------------------------------------------------------
277 PROCEDURE Update_CONTRACT_price(
278 p_api_version IN NUMBER,
279 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
280 p_commit IN VARCHAR2 DEFAULT OKC_API.G_TRUE,
281 p_CHR_ID IN NUMBER,
282 px_Control_Rec IN OUT NOCOPY OKC_CONTROL_REC_TYPE,
283 x_CLE_PRICE_TBL OUT NOCOPY CLE_PRICE_TBL_TYPE,
284 x_chr_net_price OUT NOCOPY NUMBER,
285 x_return_status OUT NOCOPY VARCHAR2,
286 x_msg_count OUT NOCOPY NUMBER,
287 x_msg_data OUT NOCOPY VARCHAR2);
288
289 ----------------------------------------------------------------------------
290 -- Update_Line_price
291 -- This procedure will calculate the price for all the Priced lines below sent in line
292 -- Called when a line is updated in the form
293 -- p_cle_id - id of the line updated
294 -- p_chr_id - id of the header
295 -- p_lowest_level Possible values 0(p_cle_id not null and this line is subline),
296 -- 1(p_cle_id not null and this line is upper line),
297 -- -1(update all lines)
298 -- -2(update all lines and header)
299 -- DEFAULT -2
300 --
301 --px_chr_list_price IN OUT -holds the total line list price, for right value pass in the existing value,
302 --px_chr_net_price IN OUT -holds the total line net price, for right value pass in the existing value
303 -- px_cle_amt gets back the net price for the line that was updated. In case of
304 -- p_negotiated_changed, it brings in the old net price of the line updated
305
306 ----------------------------------------------------------------------------
307 PROCEDURE Update_LINE_price(
308 p_api_version IN NUMBER,
309 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
310 p_commit IN VARCHAR2 DEFAULT OKC_API.G_TRUE,
311 p_CHR_ID IN NUMBER,
312 p_cle_id IN NUMBER DEFAULT null,
313 p_lowest_level IN NUMBER DEFAULT -2,
314 px_Control_Rec IN OUT NOCOPY OKC_CONTROL_REC_TYPE,
315 px_chr_list_price IN OUT NOCOPY NUMBER,
316 px_chr_net_price IN OUT NOCOPY NUMBER,
317 px_cle_amt IN OUT NOCOPY NUMBER,
318 x_CLE_PRICE_TBL OUT NOCOPY CLE_PRICE_TBL_TYPE,
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2);
322
323 ----------------------------------------------------------------------------
324 -- GET_MANUAL_ADJUSTMENTS
325 -- This procedure will return all the manual adjustments that qualify for the
326 -- sent in lines and header
327 -- To get adjustments for a line pass p_cle_id and p_control_rec.p_level='L'
328 -- To get adjustments for a Header pass p_cle_id as null and p_control_rec.p_level='H'
329 ----------------------------------------------------------------------------
330 PROCEDURE get_manual_adjustments(
331 p_api_version IN NUMBER,
332 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
333 p_CHR_ID IN NUMBER,
334 p_cle_id IN number Default Null,
335 p_Control_Rec IN OKC_CONTROL_REC_TYPE,
336 x_ADJ_tbl OUT NOCOPY MANUAL_Adj_Tbl_Type,
337 x_return_status OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY NUMBER,
339 x_msg_data OUT NOCOPY VARCHAR2);
340
341
342 END OKC_PRICE_PVT;
[Home] [Help]