[Home] [Help]
PACKAGE: APPS.ASO_PRICING_CORE_PVT
Source
1 PACKAGE ASO_PRICING_CORE_PVT AUTHID CURRENT_USER as
2 /* $Header: asovpcos.pls 120.2 2006/01/04 10:45:01 hagrawal ship $ */
3 -- Start of Comments
4 -- Package name : ASO_PRICING_CORE_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_LINE_INDEX_tbl QP_PREQ_GRP.pls_integer_type;
11 G_LINE_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
12 G_PRICING_EFFECTIVE_DATE_TBL QP_PREQ_GRP.DATE_TYPE;
13 G_ACTIVE_DATE_FIRST_TBL QP_PREQ_GRP.DATE_TYPE;
14 G_ACTIVE_DATE_FIRST_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
15 G_ACTIVE_DATE_SECOND_TBL QP_PREQ_GRP.DATE_TYPE ;
16 G_ACTIVE_DATE_SECOND_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE ;
17 G_LINE_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE ;
18 G_LINE_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
19 G_REQUEST_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
20 G_PRICED_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
21 G_UOM_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
22 G_PRICED_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
23 G_CURRENCY_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
24 G_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
25 G_PERCENT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
26 G_ADJUSTED_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
27 G_UPD_ADJUSTED_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
28 G_PROCESSED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
29 G_PRICE_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
30 G_LINE_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
31 G_PROCESSING_ORDER_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
32 G_ROUNDING_FACTOR_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
33 G_ROUNDING_FLAG_TBL QP_PREQ_GRP.FLAG_TYPE;
34 G_QUALIFIERS_EXIST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
35 G_PRICING_ATTRS_EXIST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
36 G_PRICE_LIST_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
37 G_PL_VALIDATED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
38 G_PRICE_REQUEST_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
39 G_USAGE_PRICING_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
40 G_LINE_CATEGORY_TBL QP_PREQ_GRP.VARCHAR_TYPE;
41 G_PRICING_STATUS_CODE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
42 G_PRICING_STATUS_TEXT_tbl QP_PREQ_GRP.VARCHAR_TYPE;
43 G_CHRG_PERIODICITY_CODE_TBL QP_PREQ_GRP.VARCHAR_3_TYPE;
44 /* Changes Made for OKS uptake bug 4900084 */
45 G_CONTRACT_START_DATE_TBL QP_PREQ_GRP.DATE_TYPE;
46 G_CONTRACT_END_DATE_TBL QP_PREQ_GRP.DATE_TYPE;
47
48 G_ATTR_LINE_INDEX_tbl QP_PREQ_GRP.pls_integer_type;
49 G_ATTR_LINE_DETAIL_INDEX_tbl QP_PREQ_GRP.pls_integer_type;
50 G_ATTR_VALIDATED_FLAG_tbl QP_PREQ_GRP.VARCHAR_TYPE;
51 G_ATTR_PRICING_CONTEXT_tbl QP_PREQ_GRP.VARCHAR_TYPE;
52 G_ATTR_PRICING_ATTRIBUTE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
53 G_ATTR_ATTRIBUTE_LEVEL_tbl QP_PREQ_GRP.VARCHAR_TYPE;
54 G_ATTR_ATTRIBUTE_TYPE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
55 G_ATTR_APPLIED_FLAG_tbl QP_PREQ_GRP.VARCHAR_TYPE;
56 G_ATTR_PRICING_STATUS_CODE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
57 G_ATTR_PRICING_ATTR_FLAG_tbl QP_PREQ_GRP.VARCHAR_TYPE;
58 G_ATTR_LIST_HEADER_ID_tbl QP_PREQ_GRP.NUMBER_TYPE;
59 G_ATTR_LIST_LINE_ID_tbl QP_PREQ_GRP.NUMBER_TYPE;
60 G_ATTR_VALUE_FROM_tbl QP_PREQ_GRP.VARCHAR_TYPE;
61 G_ATTR_SETUP_VALUE_FROM_tbl QP_PREQ_GRP.VARCHAR_TYPE;
62 G_ATTR_VALUE_TO_tbl QP_PREQ_GRP.VARCHAR_TYPE;
63 G_ATTR_SETUP_VALUE_TO_tbl QP_PREQ_GRP.VARCHAR_TYPE;
64 G_ATTR_GROUPING_NUMBER_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
65 G_ATTR_NO_QUAL_IN_GRP_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
66 G_ATTR_COMP_OPERATOR_TYPE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
67 G_ATTR_PRICING_STATUS_TEXT_tbl QP_PREQ_GRP.VARCHAR_TYPE;
68 G_ATTR_QUAL_PRECEDENCE_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
69 G_ATTR_DATATYPE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
70 G_ATTR_QUALIFIER_TYPE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
71 G_ATTR_PRODUCT_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
72 G_ATTR_EXCLUDER_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
73 G_ATTR_PRICING_PHASE_ID_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
74 G_ATTR_INCOM_GRP_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
75 G_ATTR_LDET_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
76 G_ATTR_MODIFIER_LEVEL_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
77 G_ATTR_PRIMARY_UOM_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
78
79 G_LDET_LINE_DTL_INDEX_TBL QP_PREQ_GRP.pls_integer_type;
80 G_LDET_PRICE_ADJ_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
81 G_LDET_LINE_DTL_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
82 G_LDET_PRICE_BREAK_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
83 G_LDET_LIST_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
84 G_LDET_LINE_INDEX_TBL QP_PREQ_GRP.pls_integer_type;
85 G_LDET_LIST_HEADER_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
86 G_LDET_LIST_LINE_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
87 G_LDET_LIST_LINE_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
88 G_LDET_LIST_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
89 G_LDET_CREATED_FROM_SQL_TBL QP_PREQ_GRP.VARCHAR_TYPE;
90 G_LDET_PRICING_GRP_SEQ_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
91 G_LDET_PRICING_PHASE_ID_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
92 G_LDET_OPERAND_CALC_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
93 G_LDET_OPERAND_VALUE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
94 G_LDET_SUBSTN_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
95 G_LDET_SUBSTN_VALUE_FROM_TBL QP_PREQ_GRP.VARCHAR_TYPE;
96 G_LDET_SUBSTN_VALUE_TO_TBL QP_PREQ_GRP.VARCHAR_TYPE;
97 G_LDET_ASK_FOR_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
98 G_LDET_PRICE_FORMULA_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
99 G_LDET_PRICING_STATUS_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
100 G_LDET_PRICING_STATUS_TXT_TBL QP_PREQ_GRP.VARCHAR_TYPE;
101 G_LDET_PRODUCT_PRECEDENCE_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
102 G_LDET_INCOMPAT_GRP_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
103 G_LDET_PROCESSED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
104 G_LDET_APPLIED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
105 G_LDET_AUTOMATIC_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
106 G_LDET_OVERRIDE_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
107 G_LDET_PRIMARY_UOM_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
108 G_LDET_PRINT_ON_INV_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
109 G_LDET_MODIFIER_LEVEL_TBL QP_PREQ_GRP.VARCHAR_TYPE;
110 G_LDET_BENEFIT_QTY_TBL QP_PREQ_GRP.NUMBER_TYPE;
111 G_LDET_BENEFIT_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
112 G_LDET_LIST_LINE_NO_TBL QP_PREQ_GRP.VARCHAR_TYPE;
113 G_LDET_ACCRUAL_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
114 G_LDET_ACCR_CONV_RATE_TBL QP_PREQ_GRP.NUMBER_TYPE;
115 G_LDET_ESTIM_ACCR_RATE_TBL QP_PREQ_GRP.NUMBER_TYPE;
116 G_LDET_RECURRING_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
117 G_LDET_SELECTED_VOL_ATTR_TBL QP_PREQ_GRP.VARCHAR_TYPE;
118 G_LDET_ROUNDING_FACTOR_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
119 G_LDET_HDR_LIMIT_EXISTS_TBL QP_PREQ_GRP.VARCHAR_TYPE;
120 G_LDET_LINE_LIMIT_EXISTS_TBL QP_PREQ_GRP.VARCHAR_TYPE;
121 G_LDET_CHARGE_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
122 G_LDET_CHARGE_SUBTYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
123 G_LDET_CURRENCY_DTL_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
124 G_LDET_CURRENCY_HDR_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
125 G_LDET_SELLING_ROUND_TBL QP_PREQ_GRP.NUMBER_TYPE;
126 G_LDET_ORDER_CURRENCY_TBL QP_PREQ_GRP.VARCHAR_TYPE;
127 G_LDET_PRICING_EFF_DATE_TBL QP_PREQ_GRP.DATE_TYPE;
128 G_LDET_BASE_CURRENCY_TBL QP_PREQ_GRP.VARCHAR_TYPE;
129 G_LDET_LINE_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
130 G_LDET_UPDATED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
131 G_LDET_CALC_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
132 G_LDET_CHG_REASON_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
133 G_LDET_CHG_REASON_TEXT_TBL QP_PREQ_GRP.VARCHAR_TYPE;
134
135 G_RLTD_LINE_INDEX_TBL QP_PREQ_GRP.NUMBER_TYPE;
136 G_RLTD_LINE_DTL_INDEX_TBL QP_PREQ_GRP.NUMBER_TYPE;
137 G_RLTD_RELATION_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
138 G_RLTD_RELATED_LINE_IND_TBL QP_PREQ_GRP.NUMBER_TYPE;
139 G_RLTD_RLTD_LINE_DTL_IND_TBL QP_PREQ_GRP.NUMBER_TYPE;
140 G_RLTD_LST_LN_ID_DEF_TBL QP_PREQ_GRP.NUMBER_TYPE;
141 G_RLTD_RLTD_LST_LN_ID_DEF_TBL QP_PREQ_GRP.NUMBER_TYPE;
142
143 --Define Some Constants used in pricing integration
144 G_TERMS_SUBSTITUTION CONSTANT VARCHAR2(30) := 'TSN';
145 G_ORDER_LEVEL CONSTANT VARCHAR2(30) :='ORDER';
146 G_LINE_LEVEL CONSTANT VARCHAR2(30) :='LINE';
147 G_QUAL_ATTRIBUTE1 CONSTANT VARCHAR2(30) := 'QUALIFIER_ATTRIBUTE1';
148 G_QUAL_ATTRIBUTE10 CONSTANT VARCHAR2(30) := 'QUALIFIER_ATTRIBUTE10';
149 G_QUAL_ATTRIBUTE11 CONSTANT VARCHAR2(30) := 'QUALIFIER_ATTRIBUTE11';
150 G_FREIGHT_TERM_LK_TYPE CONSTANT VARCHAR2(30) := 'FREIGHT_TERMS';
151 G_YES_FLAG CONSTANT VARCHAR2(1) := 'Y';
152 G_NO_FLAG CONSTANT VARCHAR2(1) := 'N';
153 G_FREE_LINE_FLAG CONSTANT VARCHAR2(1) := 'F';
154
155
156 TYPE Index_Link_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
157 G_MISS_Link_Tbl Index_Link_Tbl_Type;
158
159 PROCEDURE Initialize_Global_Tables;
160
161 FUNCTION Set_Global_Rec (
162 p_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type,
163 p_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type)
164 RETURN ASO_PRICING_INT.PRICING_HEADER_REC_TYPE;
165
166 FUNCTION Set_Global_Rec (
167 p_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type,
168 p_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type,
169 p_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type)
170 RETURN ASO_PRICING_INT.PRICING_LINE_REC_TYPE;
171
172
173 PROCEDURE Print_G_Header_Rec;
174
175 PROCEDURE Print_G_Line_Rec;
176
177 /*New Append_ask_for to use direct insert*/
178 PROCEDURE Append_Asked_For(
179 p_pricing_event varchar2
180 ,p_price_line_index NUMBER
181 ,p_header_id number := null
182 ,p_Line_id number := null
183 ,px_index_counter IN OUT NOCOPY /* file.sql.39 change */ number);
184
185 /*New copy_Header_to_request to use direct insert*/
186 PROCEDURE Copy_Header_To_Request(
187 p_Request_Type VARCHAR2,
188 p_price_line_index NUMBER,
189 px_index_counter NUMBER);
190
191 /*New copy_Line_to_request to use direct insert*/
192 PROCEDURE Copy_Line_To_Request(
193 p_Request_Type VARCHAR2,
194 p_price_line_index NUMBER,
195 px_index_counter NUMBER);
196
197 --PROCEDURE Query_Price_Adj_Header_All (p_quote_header_id IN NUMBER);
198
199 PROCEDURE Query_Price_Adj_All
200 (p_quote_header_id IN NUMBER,
201 x_adj_id_tbl OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE);
202
203
204 PROCEDURE Query_Price_Adj_Header (p_quote_header_id IN NUMBER);
205
206 PROCEDURE Query_Price_Adjustments
207 (p_quote_header_id IN NUMBER,
208 p_qte_line_id_tbl IN JTF_NUMBER_TABLE,
209 x_adj_id_tbl OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE);
210
211 Procedure Query_Relationships(p_qte_adj_id_tbl IN JTF_NUMBER_TABLE,
212 p_service_qte_line_id_tbl IN JTF_NUMBER_TABLE);
213
214 PROCEDURE Print_Global_Data_Lines;
215
216 PROCEDURE Print_Global_Data_Adjustments;
217
218 PROCEDURE Print_Global_Data_Rltships;
219
220 PROCEDURE Populate_QP_Temp_Tables;
221
222 PROCEDURE Delete_Promotion (
223 P_Api_Version_Number IN NUMBER,
224 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
225 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
226 p_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type,
227 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
228 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
229 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
230 );
231
232 PROCEDURE Copy_Price_To_Quote(
233 P_Api_Version_Number IN NUMBER,
234 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
235 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
239 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
236 P_control_rec IN ASO_PRICING_INT.Pricing_Control_Rec_Type,
237 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
238 P_Insert_Type IN VARCHAR2 := 'HDR',
240 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
241 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
242
243 FUNCTION Modify_Global_PlsIndex_Table (
244 p_global_tbl IN QP_PREQ_GRP.pls_integer_type,
245 p_search_tbl IN Index_Link_Tbl_Type)
246 RETURN QP_PREQ_GRP.pls_integer_type;
247
248 FUNCTION Modify_Global_NumIndex_Table (
249 p_global_tbl IN QP_PREQ_GRP.NUMBER_TYPE,
250 p_search_tbl IN Index_Link_Tbl_Type)
251 RETURN QP_PREQ_GRP.NUMBER_TYPE;
252
253 PROCEDURE Process_Charges(
254 P_Api_Version_Number IN NUMBER,
255 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
256 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
257 P_control_rec IN ASO_PRICING_INT.Pricing_Control_Rec_Type,
258 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
259 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
260 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
261 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
262
263
264 PROCEDURE Process_PRG(
265 P_Api_Version_Number IN NUMBER,
266 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
267 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
268 P_control_rec IN ASO_PRICING_INT.Pricing_Control_Rec_Type,
269 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
270 x_qte_line_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
271 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
272 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
273 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2);
274
275
276 End ASO_PRICING_CORE_PVT;