1 Package OKC_OC_INT_PRICING_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKCRPRIS.pls 120.0 2005/05/25 22:36:17 appldev noship $ */
3
4 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
5 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_OC_INT_PRICING_PVT';
6 ---G_APP_NAME CONSTANT VARCHAR2(3) := OKO_DATATYPES.G_APP_NAME;
7 -- to be restored when this package is moved back into OKO
8
9 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKO';
10
11 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
12 G_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_ERROR';
13 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
14 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
15
16 --G_MISS_REL_LINE_TAB OKC_K_REL_OBJS_PUB.crj_rel_line_tbl_type;
17 G_MISS_LINE_INF_TAB OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type;
18
19 ---------------------------------------------------------------------------
20 --Procedure to contract pricing information at the header and line levels
21 ----------------------------------------------------------------------------
22 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
23 PROCEDURE create_k_pricing (
24 x_return_status OUT NOCOPY VARCHAR2,
25
26 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
27 p_o_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
28 p_ohr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
29 p_q_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
30 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
31 DEFAULT OKC_API.G_MISS_NUM,
32 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
33 DEFAULT OKC_OC_INT_PRICING_PVT.G_MISS_LINE_INF_TAB
34 -- this PL/SQL table has quote (or order) lines against contract lines
35
36
37 );
38
39
40
41 ----------------------------------------------------------------------------
42 -- Procedure creates price attribute information in OKC from
43 -- ASO or ONT pricing
44 ----------------------------------------------------------------------------
45 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
46 PROCEDURE get_price_attr(
47 p_chr_id IN NUMBER,
48 p_q_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
49 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
50 DEFAULT OKC_API.G_MISS_NUM,
51 p_o_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
52 p_ohr_id IN NUMBER,
53 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
54 DEFAULT OKC_OC_INT_PRICING_PVT.G_MISS_LINE_INF_TAB
55 );
56
57
58
59 --------------------------------------------------------------------------
60 --------get price adjustments from ASO or ONT and put in OKC pricing tables
61 --------------------------------------------------------------------------
62 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
63 PROCEDURE get_price_adj(
64 p_chr_id IN NUMBER,
65 p_q_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
66 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
67 DEFAULT OKC_API.G_MISS_NUM,
68 p_o_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
69 p_ohr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
70 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
71 DEFAULT OKC_OC_INT_PRICING_PVT.G_MISS_LINE_INF_TAB
72 );
73
74
75
76 --put PRICE ADJUSTMENT RELATIONSHIPS FROM ASO (or ONT) into OKC
77 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
78 PROCEDURE get_price_adj_rltship(
79 p_o_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
80 p_ohr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
81 p_q_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
82 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
83 DEFAULT OKC_API.G_MISS_NUM,
84 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
85 DEFAULT OKC_OC_INT_PRICING_PVT.G_MISS_LINE_INF_TAB
86 );
87
88
89
90 ----------------------------------------------------------------
91 --put PRICE ADJUSTMENT ATTRIBUTES FROM ASO or ONT into OKC
92 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
93 PROCEDURE get_price_adj_attr(
94 p_o_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
95 p_ohr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
96 p_q_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
97 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
98 DEFAULT OKC_API.G_MISS_NUM
99 );
100
101 ----------------------------------------------------------------------------
102 -- PROCEDURE to get PRICE ADJUSTMENTS information from ASO or ONT tables
103 -- get_patv_tab stores it's output in global PL/SQL table g_patv_tbl
104 ----------------------------------------------------------------------------
105 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
106 -- ASO_QUOTE_LINES_ALL.LINE_HEADER_ID to OKX_QUOTE_LINES_V.ID1
107 PROCEDURE get_patv_tab(
108 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
109 p_cle_id IN OKC_K_LINES_B.ID%TYPE DEFAULT OKC_API.G_MISS_NUM,
110 p_o_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
111 p_ohr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
112 p_ole_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
113 p_q_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
114 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
115 DEFAULT OKC_API.G_MISS_NUM,
116 p_qle_id IN OKX_QUOTE_LINES_V.ID1%TYPE
117 DEFAULT OKC_API.G_MISS_NUM);
118
119
120 ---------------------------------------------------------------
121 -- PROCEDURE to get PRICE ADJUSTMENT RELATIONSHIP information
122 -- from ASO or ONT tables
123 -- get_pacv_tab stores all the price adjustment relationships in
124 -- global PL/SQL table g_pacv_tbl
125 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
126 PROCEDURE get_pacv_tab(
127 p_old_pat_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
128 p_new_pat_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
129 p_o_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
130 p_ohr_id IN NUMBER
131 DEFAULT OKC_API.G_MISS_NUM,
132 p_q_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
133 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
134 DEFAULT OKC_API.G_MISS_NUM,
135 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
136 DEFAULT OKC_OC_INT_PRICING_PVT.G_MISS_LINE_INF_TAB);
137
138
139 -----------------------------------------------------------------------------
140 -- to get PRICE ADJUSTMENT ATTRIBUTES information from ASO or ONT tables
141 -- get_paav_tab stores all the price adjustment attributes in global
142 -- PL/SQL table g_paav_tbl
143 -----------------------------------------------------------------------------
144 PROCEDURE get_paav_tab(p_old_pat_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
145 p_new_pat_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
146 p_q_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
147 p_qhr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
148 p_o_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
149 p_ohr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM);
150 -- x_paav_tbl OUT NOCOPY paav_tbl_type)
151
152
153 ----------------------------------------------------------------------
154 -- PROCEDURE to get PRICE ATTRIBUTES information from ASO or ONT tables
155 -- get_pavv_tab stores it's output in global PL/SQL table g_pavv_tbl
156 ----------------------------------------------------------------------
157 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
158 -- ASO_QUOTE_LINES_ALL.LINE_HEADER_ID to OKX_QUOTE_LINES_V.ID1
159 PROCEDURE get_pavv_tab(
160 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
161 p_cle_id IN OKC_K_LINES_B.ID%TYPE DEFAULT OKC_API.G_MISS_NUM,
162
163 p_o_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE, --order flag
164 p_ohr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
165 p_ole_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
166
167 p_q_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE, --quote flag
168 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
169 DEFAULT OKC_API.G_MISS_NUM,
170 p_qle_id IN OKX_QUOTE_LINES_V.ID1%TYPE
171 DEFAULT OKC_API.G_MISS_NUM);
172
173
174
175 -- =========================================================================
176 -- =========================================================================
177 -- START OF KTQ or KTO PRICING INFORMATION CREATION
178 -- or UPDATE
179 -- =========================================================================
180 -- =========================================================================
181
182
183 TYPE k_price_adj_rec_type IS RECORD(id okc_price_adjustments.id%TYPE,level VARCHAR2(1));
184
185 TYPE k_price_adj_tab_type IS TABLE OF k_price_adj_rec_type index by binary_integer;
186
187
188 g_aso_op_code_create VARCHAR2(30) := 'CREATE';
189 g_aso_op_code_update VARCHAR2(30) := 'UPDATE';
190 g_aso_op_code_delete VARCHAR2(30) := 'DELETE';
191
192 x_return_status VARCHAR2(1);
193 p_o_flag VARCHAR2(1);
194 p_q_flag VARCHAR2(1);
195
196 p_qle_tab ASO_QUOTE_PUB.qte_line_tbl_type DEFAULT ASO_QUOTE_PUB.g_miss_qte_line_tbl;
197 p_qle_shipment_tab ASO_QUOTE_PUB.shipment_tbl_type DEFAULT ASO_QUOTE_PUB.g_miss_shipment_tbl;
198
199 p_ole_tab ASO_QUOTE_PUB.qte_line_tbl_type DEFAULT ASO_QUOTE_PUB.g_miss_qte_line_tbl;
200 p_ole_shipment_tab ASO_QUOTE_PUB.shipment_tbl_type DEFAULT ASO_QUOTE_PUB.g_miss_shipment_tbl;
201
202
203
204 e_exit EXCEPTION;
205
206 l_line_tab ASO_QUOTE_PUB.qte_line_tbl_type;
207 l_line_shipment_tab ASO_QUOTE_PUB.shipment_tbl_type;
208 l_k_price_adj_tab k_price_adj_tab_type;
209
210
211 -----------------------------------------------------------------------------------------
212 -- procedure build_pricing_from_k
213 -----------------------------------------------------------------------------------------
214
215 -- Notes for the impact of configuration items on Pricing information
216 -- for k->Q update and K->O creation.
217 --
218 -- Quote Contract
219 -- ----- --------
220 -- QL1 <---------------- KL1 Top Model line (Contains sales credit, rule info)
221 -- |______________ |__KSL1 Top Base line (Contains Price adjustment info)
222 -- |
223 -- QL2 <---------------- |- KSL1.1 Config (Contains Price adjustment info)
224 -- QL3 <---------------- |_ KSL1.2 Config (Contains Price adjustment info)
225 --
226 -- The Top model line and the Top base line information is stored into the same
227 -- quote line as shown above and when the relationship PL/SQL table is constructed
228 -- the first relationship between KL1 and QL1 is overwritten by the second
229 -- relationship. Therefore the line id that is against the QL1 is the id
230 -- of the top base line.So there is no impact of configuration items on
231 -- Pricing information because all the information is available
232 --
233
234 PROCEDURE build_pricing_from_k(
235 p_chr_id IN OKC_K_HEADERS_B.id%TYPE,
236 p_kl_rel_tab IN okc_oc_int_config_pvt.line_rel_tab_type DEFAULT okc_oc_int_config_pvt.g_miss_kl_rel_tab,
237 --
238 p_q_flag IN VARCHAR2 DEFAULT OKC_API.g_miss_char,
239 p_qhr_id IN OKX_QUOTE_HEADERS_V.id1%TYPE DEFAULT OKC_API.g_miss_num,
240 p_qle_tab IN ASO_QUOTE_PUB.qte_line_tbl_type DEFAULT ASO_QUOTE_PUB.g_miss_qte_line_tbl,
241 p_qle_shipment_tab IN ASO_QUOTE_PUB.shipment_tbl_type DEFAULT ASO_QUOTE_PUB.g_miss_shipment_tbl,
242 --
243 p_o_flag IN VARCHAR2 DEFAULT OKC_API.g_miss_char,
244 p_ohr_id IN OKX_ORDER_HEADERS_V.id1%TYPE DEFAULT OKC_API.g_miss_num,
245 p_ole_tab IN ASO_QUOTE_PUB.qte_line_tbl_type DEFAULT ASO_QUOTE_PUB.g_miss_qte_line_tbl,
246 p_ole_shipment_tab IN ASO_QUOTE_PUB.shipment_tbl_type DEFAULT ASO_QUOTE_PUB.g_miss_shipment_tbl,
247 --
248 x_hd_price_adj_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_tbl_type,
249 x_ln_price_adj_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_tbl_type,
250 x_hd_price_adj_attr_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_attr_tbl_type,
251 x_ln_price_adj_attr_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_attr_tbl_type,
252 x_hd_price_attr_tab OUT NOCOPY ASO_QUOTE_PUB.price_attributes_tbl_type,
253 x_ln_price_attr_tab OUT NOCOPY ASO_QUOTE_PUB.price_attributes_tbl_type,
254 x_hd_price_adj_rltship_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_rltship_tbl_type,
255 x_ln_price_adj_rltship_tab OUT NOCOPY ASO_QUOTE_PUB.price_adj_rltship_tbl_type,
256 --
257 x_return_status OUT NOCOPY VARCHAR2);
258
259 -- =========================================================================
260 -- =========================================================================
261 -- END OF KTQ or KTO PRICING INFORMATION CREATION
262 -- or UPDATE
263 -- =========================================================================
264 -- =========================================================================
265
266 END OKC_OC_INT_PRICING_PVT;
[Home] [Help]