eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.QP_JPE_CTRL_PVT

Source


1 package body qp_jpe_ctrl_pvt as
2 /* $Header: QPXJPECB.pls 120.1 2006/03/09 19:18:30 hwong noship $ */
3 
4 procedure switch(
5   err_buff out nocopy varchar2,
6   retcode out nocopy number,
7   p_control in  varchar2
8 )
9 is
10   l_internal varchar2(30);
11   l_engine_type varchar2(30);
12   l_stmt varchar2(20000);
13   l_save boolean;
14   l_pattern_count number;
15   l_attr_grp_count number;
16   l_cache_stat_count number;
17 begin
18 
19   l_internal := nvl(FND_PROFILE.VALUE(g_internal), g_null);
20   l_engine_type := nvl(FND_PROFILE.VALUE(g_engine_type), g_plsql);
21 
22   if (l_internal <> g_null) then
23 
24       if (p_control = g_plsql and l_engine_type = g_java) then
25 
26         begin
27 
28           l_save := fnd_profile.save(x_name => g_engine_type,
29                                      x_value => g_plsql,
30                                      x_level_name => g_site);
31 
32           l_stmt :=
33             'create or replace view QP_PREQ_LINES_TMP as
34                 SELECT QPT.REQUEST_TYPE_CODE,QPT.LINE_ID,QPT.LINE_INDEX,QPT.LINE_TYPE_CODE,
35                 QPT.PRICING_EFFECTIVE_DATE,QPT.LINE_QUANTITY,QPT.LINE_UOM_CODE,
36                 QPT.PRICED_QUANTITY,QPT.PRICED_UOM_CODE,QPT.UOM_QUANTITY,QPT.CURRENCY_CODE,
37                 QPT.UNIT_PRICE,QPT.PERCENT_PRICE,QPT.ADJUSTED_UNIT_PRICE,QPT.PARENT_PRICE,
38                 QPT.PARENT_QUANTITY,QPT.PARENT_UOM_CODE,QPT.PROCESSING_ORDER,
39                 QPT.PROCESSED_FLAG,QPT.PROCESSED_CODE,QPT.PRICE_FLAG,QPT.PRICING_STATUS_CODE,
40                 QPT.PRICING_STATUS_TEXT,QPT.START_DATE_ACTIVE_FIRST,QPT.ACTIVE_DATE_FIRST_TYPE,
41                 QPT.START_DATE_ACTIVE_SECOND,QPT.ACTIVE_DATE_SECOND_TYPE,QPT.GROUP_QUANTITY,
42                 QPT.GROUP_AMOUNT,QPT.LINE_AMOUNT,QPT.ROUNDING_FLAG,QPT.ROUNDING_FACTOR,
43                 QPT.UPDATED_ADJUSTED_UNIT_PRICE,QPT.PRICE_REQUEST_CODE,QPT.HOLD_CODE,
44                 QPT.HOLD_TEXT,QPT.PRICE_LIST_HEADER_ID,QPT.VALIDATED_FLAG,
45                 QPT.QUALIFIERS_EXIST_FLAG,QPT.PRICING_ATTRS_EXIST_FLAG,
46                 QPT.PRIMARY_QUALIFIERS_MATCH_FLAG,QPT.USAGE_PRICING_TYPE,
47                 QPT.LINE_CATEGORY,QPT.CONTRACT_START_DATE,QPT.CONTRACT_END_DATE,
48                 QPT.LINE_UNIT_PRICE,QPT.REQUEST_ID,QPT.PROCESS_STATUS,QPT.EXTENDED_PRICE,
49                 QPT.ORDER_UOM_SELLING_PRICE,QPT.CATCHWEIGHT_QTY,QPT.ACTUAL_ORDER_QUANTITY,
50                 QPT.HEADER_ID,QPT.PRICING_EVENT,QPT.CALCULATE_FLAG,QPT.SIMULATION_FLAG,
51                 QPT.GSA_CHECK_FLAG,QPT.GSA_DUP_CHECK_FLAG,QPT.MANUAL_DISCOUNT_FLAG,
52                 QPT.SOURCE_ORDER_AMOUNT_FLAG,
53                 QPT.MANUAL_ADJUSTMENTS_CALL_FLAG MANUAL_ADJUSTMENTS_CALL_FL,
54                 QPT.GET_FREIGHT_FLAG,QPT.VIEW_CODE,QPT.CHECK_CUST_VIEW_FLAG,
55                 QPT.FULL_PRICING_CALL,QPT.USE_MULTI_CURRENCY,QPT.USER_CONVERSION_RATE,
56                 QPT.USER_CONVERSION_TYPE,QPT.FUNCTION_CURRENCY,QPT.LIST_PRICE_OVERRIDE_FLAG,
57                 QPT.CHARGE_PERIODICITY_CODE
58                 FROM QP_PREQ_LINES_TMP_T QPT
59                 WHERE REQUEST_ID = nvl(sys_context(''qp_context'',''request_id''),1)';
60 
61           execute immediate l_stmt;
62 
63           l_stmt :=
64             'create or replace view QP_PREQ_LDETS_TMP as
65                 SELECT QPT.LINE_DETAIL_INDEX,QPT.LINE_DETAIL_TYPE_CODE,
66                 QPT.LINE_DETAIL_PBH_TYPE,QPT.PRICE_BREAK_TYPE_CODE,
67                 QPT.LINE_INDEX,QPT.CREATED_FROM_LIST_HEADER_ID,QPT.CREATED_FROM_LIST_LINE_ID,
68                 QPT.CREATED_FROM_LIST_LINE_TYPE,QPT.CREATED_FROM_LIST_TYPE_CODE,
69                 QPT.MODIFIER_LEVEL_CODE,QPT.CREATED_FROM_SQL,QPT.PRICING_GROUP_SEQUENCE,
70                 QPT.OPERAND_CALCULATION_CODE,QPT.OPERAND_VALUE,QPT.ADJUSTMENT_AMOUNT,
71                 QPT.LINE_QUANTITY,QPT.SUBSTITUTION_TYPE_CODE,QPT.SUBSTITUTION_VALUE_FROM,
72                 QPT.SUBSTITUTION_VALUE_TO,QPT.ASK_FOR_FLAG,QPT.PRICE_FORMULA_ID,
73                 QPT.PROCESSED_FLAG,QPT.PRICING_STATUS_CODE,QPT.PRICING_STATUS_TEXT,
74                 QPT.PRODUCT_PRECEDENCE,QPT.INCOMPATABILITY_GRP_CODE,QPT.BEST_PERCENT,
75                 QPT.PRICING_PHASE_ID,QPT.APPLIED_FLAG,QPT.AUTOMATIC_FLAG,
76                 QPT.OVERRIDE_FLAG,QPT.PRINT_ON_INVOICE_FLAG,QPT.PRIMARY_UOM_FLAG,
77                 QPT.BENEFIT_QTY,QPT.BENEFIT_UOM_CODE,QPT.LIST_LINE_NO,QPT.ACCRUAL_FLAG,
78                 QPT.ACCRUAL_CONVERSION_RATE,QPT.ESTIM_ACCRUAL_RATE,QPT.RECURRING_FLAG,
79                 QPT.SELECTED_VOLUME_ATTR,QPT.ROUNDING_FACTOR,QPT.SECONDARY_PRICELIST_IND,
80                 QPT.GROUP_QUANTITY,QPT.GROUP_AMOUNT,QPT.PROCESS_CODE,QPT.UPDATED_FLAG,
81                 QPT.CHARGE_TYPE_CODE,QPT.CHARGE_SUBTYPE_CODE,QPT.LIMIT_CODE,QPT.LIMIT_TEXT,
82                 QPT.HEADER_LIMIT_EXISTS,QPT.LINE_LIMIT_EXISTS,QPT.CALCULATION_CODE,
83                 QPT.CURRENCY_HEADER_ID,QPT.PRICING_EFFECTIVE_DATE,QPT.BASE_CURRENCY_CODE,
84                 QPT.ORDER_CURRENCY,QPT.CURRENCY_DETAIL_ID,QPT.SELLING_ROUNDING_FACTOR,
85                 QPT.CHANGE_REASON_CODE,QPT.CHANGE_REASON_TEXT,QPT.REQUEST_ID,
86                 QPT.PRICE_ADJUSTMENT_ID,QPT.RECURRING_VALUE,QPT.NET_AMOUNT_FLAG,
87                 QPT.ORDER_QTY_OPERAND,QPT.ORDER_QTY_ADJ_AMT,QPT.ACCUM_CONTEXT,
88                 QPT.ACCUM_ATTRIBUTE,QPT.ACCUM_ATTR_RUN_SRC_FLAG,QPT.BREAK_UOM_CODE,
89                 QPT.BREAK_UOM_CONTEXT,QPT.BREAK_UOM_ATTRIBUTE
90                 FROM QP_PREQ_LDETS_TMP_T QPT
91                 WHERE REQUEST_ID = nvl(SYS_CONTEXT(''QP_CONTEXT'',''REQUEST_ID''),1)';
92 
93           execute immediate l_stmt;
94 
95           l_stmt :=
96             'create or replace view QP_PREQ_LINE_ATTRS_TMP as
97                 SELECT QPT.LINE_INDEX,QPT.LINE_DETAIL_INDEX,QPT.ATTRIBUTE_LEVEL,
98                 QPT.ATTRIBUTE_TYPE,QPT.LIST_HEADER_ID,QPT.LIST_LINE_ID,QPT.CONTEXT,
99                 QPT.ATTRIBUTE,QPT.VALUE_FROM,QPT.SETUP_VALUE_FROM,QPT.VALUE_TO,
100                 QPT.SETUP_VALUE_TO,QPT.GROUPING_NUMBER,QPT.NO_QUALIFIERS_IN_GRP,
101                 QPT.COMPARISON_OPERATOR_TYPE_CODE,QPT.VALIDATED_FLAG,QPT.APPLIED_FLAG,
102                 QPT.PRICING_STATUS_CODE,QPT.PRICING_STATUS_TEXT,QPT.QUALIFIER_PRECEDENCE,
103                 QPT.PRICING_ATTR_FLAG,QPT.QUALIFIER_TYPE,QPT.DATATYPE,QPT.PRODUCT_UOM_CODE,
104                 QPT.PROCESSED_CODE,QPT.EXCLUDER_FLAG,QPT.GROUP_QUANTITY,QPT.GROUP_AMOUNT,
105                 QPT.DISTINCT_QUALIFIER_FLAG,QPT.PRICING_PHASE_ID,QPT.INCOMPATABILITY_GRP_CODE,
106                 QPT.LINE_DETAIL_TYPE_CODE,QPT.MODIFIER_LEVEL_CODE,QPT.PRIMARY_UOM_FLAG,
107                 QPT.REQUEST_ID,QPT.DERIVED_QUALIFIER_FLAG
108                 FROM QP_PREQ_LINE_ATTRS_TMP_T QPT
109                 WHERE REQUEST_ID = nvl(SYS_CONTEXT(''QP_CONTEXT'',''REQUEST_ID''), 1)';
110 
111           execute immediate l_stmt;
112 
113           l_stmt :=
114             'create or replace view QP_PREQ_RLTD_LINES_TMP as
115                 SELECT QPT.REQUEST_TYPE_CODE,QPT.LINE_INDEX,QPT.LINE_DETAIL_INDEX,
116                 QPT.RELATIONSHIP_TYPE_CODE,QPT.RELATED_LINE_INDEX,
117                 QPT.RELATED_LINE_DETAIL_INDEX,QPT.PRICING_STATUS_CODE,
118                 QPT.PRICING_STATUS_TEXT,QPT.LIST_LINE_ID,QPT.RELATED_LIST_LINE_ID,
119                 QPT.RELATED_LIST_LINE_TYPE,QPT.OPERAND_CALCULATION_CODE,QPT.OPERAND,
120                 QPT.PRICING_GROUP_SEQUENCE,QPT.RELATIONSHIP_TYPE_DETAIL,QPT.SETUP_VALUE_FROM,
121                 QPT.SETUP_VALUE_TO,QPT.QUALIFIER_VALUE,QPT.ADJUSTMENT_AMOUNT,
122                 QPT.SATISFIED_RANGE_VALUE,QPT.REQUEST_ID
123                 FROM QP_PREQ_RLTD_LINES_TMP_T QPT
124                 WHERE REQUEST_ID = nvl(sys_context(''qp_context'',''request_id''),1)';
125 
126           execute immediate l_stmt;
127 
128           l_stmt :=
129             'create or replace view QP_FORMULA_STEP_VALUES_TMP as
130                 SELECT QFT.PRICE_FORMULA_ID,QFT.STEP_NUMBER,QFT.COMPONENT_VALUE,
131                 QFT.PRICE_FORMULA_LINE_TYPE_CODE,QFT.LINE_INDEX,QFT.LIST_LINE_TYPE_CODE,
132                 QFT.LIST_HEADER_ID,QFT.LIST_LINE_ID,QFT.REQUEST_ID
133                 FROM QP_FORMULA_STEP_VALUES_TMP_T QFT
134                 WHERE REQUEST_ID = nvl(SYS_CONTEXT(''QP_CONTEXT'',''REQUEST_ID''),1)';
135 
136           execute immediate l_stmt;
137 
138           l_stmt :=
139             'create or replace view QP_LDETS_V as
140                 SELECT A.LINE_DETAIL_INDEX,A.LINE_DETAIL_TYPE_CODE,A.LINE_INDEX,
141                 A.CREATED_FROM_LIST_HEADER_ID LIST_HEADER_ID,
142                 A.CREATED_FROM_LIST_LINE_ID LIST_LINE_ID,
143                 A.CREATED_FROM_LIST_LINE_TYPE LIST_LINE_TYPE_CODE,
144                 A.PRICE_BREAK_TYPE_CODE,
145                 A.LINE_QUANTITY,A.ADJUSTMENT_AMOUNT,A.AUTOMATIC_FLAG,
146                 A.PRICING_PHASE_ID,A.OPERAND_CALCULATION_CODE,A.OPERAND_VALUE,
147                 A.PRICING_GROUP_SEQUENCE,A.CREATED_FROM_LIST_TYPE_CODE,
148                 A.APPLIED_FLAG,A.PRICING_STATUS_CODE,A.PRICING_STATUS_TEXT,
149                 A.LIMIT_CODE,A.LIMIT_TEXT,A.LIST_LINE_NO,A.GROUP_QUANTITY,
150                 A.UPDATED_FLAG,A.PROCESS_CODE,B.SUBSTITUTION_VALUE SUBSTITUTION_VALUE_TO,
151                 B.SUBSTITUTION_ATTRIBUTE,B.ACCRUAL_FLAG,A.MODIFIER_LEVEL_CODE,
152                 B.ESTIM_GL_VALUE,B.ACCRUAL_CONVERSION_RATE,B.OVERRIDE_FLAG,
153                 B.PRINT_ON_INVOICE_FLAG,B.INVENTORY_ITEM_ID,B.ORGANIZATION_ID,
154                 B.RELATED_ITEM_ID,B.RELATIONSHIP_TYPE_ID,B.ESTIM_ACCRUAL_RATE,
155                 B.EXPIRATION_DATE,B.BENEFIT_PRICE_LIST_LINE_ID,B.RECURRING_FLAG,
156                 B.BENEFIT_LIMIT,A.CHARGE_TYPE_CODE,A.CHARGE_SUBTYPE_CODE,A.BENEFIT_QTY,
157                 B.BENEFIT_UOM_CODE,B.PRORATION_TYPE_CODE,B.INCLUDE_ON_RETURNS_FLAG,
158                 B.REBATE_TRANSACTION_TYPE_CODE,B.NUMBER_EXPIRATION_PERIODS,
159                 B.EXPIRATION_PERIOD_UOM,B.COMMENTS,A.CALCULATION_CODE,
160                 A.CHANGE_REASON_CODE,A.CHANGE_REASON_TEXT,A.PRICE_ADJUSTMENT_ID,
161                 A.NET_AMOUNT_FLAG,A.ORDER_QTY_OPERAND,A.ORDER_QTY_ADJ_AMT,
162                 A.ACCUM_CONTEXT,A.ACCUM_ATTRIBUTE,A.ACCUM_ATTR_RUN_SRC_FLAG,
163                 A.BREAK_UOM_CODE,A.BREAK_UOM_CONTEXT,A.BREAK_UOM_ATTRIBUTE
164                 FROM QP_PREQ_LDETS_TMP_T A, QP_LIST_LINES B
165                 WHERE a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
166                 AND a.PRICING_STATUS_CODE = ''N''
167                 and a.REQUEST_ID = nvl(SYS_CONTEXT(''QP_CONTEXT'',''REQUEST_ID''),1)';
168 
169           execute immediate l_stmt;
170 
171           commit;
172 
173         exception
174           when others then
175             err_buff := sqlerrm;
176             retcode := 2;
177           end;
178 
179       elsif (p_control = g_java and l_engine_type = g_plsql) then
180 
181         begin
182 
183           l_save := fnd_profile.save(x_name => g_engine_type,
184                                      x_value => g_java,
185                                      x_level_name => g_site);
186 
187           l_stmt :=
188             'create or replace view QP_PREQ_LINES_TMP as
189                 SELECT QPT.REQUEST_TYPE_CODE,QPT.LINE_ID,QPT.LINE_INDEX,QPT.LINE_TYPE_CODE,
190                 QPT.PRICING_EFFECTIVE_DATE,QPT.LINE_QUANTITY,QPT.LINE_UOM_CODE,
191                 QPT.PRICED_QUANTITY,QPT.PRICED_UOM_CODE,QPT.UOM_QUANTITY,QPT.CURRENCY_CODE,
192                 QPT.UNIT_PRICE,QPT.PERCENT_PRICE,QPT.ADJUSTED_UNIT_PRICE,QPT.PARENT_PRICE,
193                 QPT.PARENT_QUANTITY,QPT.PARENT_UOM_CODE,QPT.PROCESSING_ORDER,
194                 QPT.PROCESSED_FLAG,QPT.PROCESSED_CODE,QPT.PRICE_FLAG,QPT.PRICING_STATUS_CODE,
195                 QPT.PRICING_STATUS_TEXT,QPT.START_DATE_ACTIVE_FIRST,QPT.ACTIVE_DATE_FIRST_TYPE,
196                 QPT.START_DATE_ACTIVE_SECOND,QPT.ACTIVE_DATE_SECOND_TYPE,QPT.GROUP_QUANTITY,
197                 QPT.GROUP_AMOUNT,QPT.LINE_AMOUNT,QPT.ROUNDING_FLAG,QPT.ROUNDING_FACTOR,
198                 QPT.UPDATED_ADJUSTED_UNIT_PRICE,QPT.PRICE_REQUEST_CODE,QPT.HOLD_CODE,
199                 QPT.HOLD_TEXT,QPT.PRICE_LIST_HEADER_ID,QPT.VALIDATED_FLAG,
200                 QPT.QUALIFIERS_EXIST_FLAG,QPT.PRICING_ATTRS_EXIST_FLAG,
201                 QPT.PRIMARY_QUALIFIERS_MATCH_FLAG,QPT.USAGE_PRICING_TYPE,
202                 QPT.LINE_CATEGORY,QPT.CONTRACT_START_DATE,QPT.CONTRACT_END_DATE,
203                 QPT.LINE_UNIT_PRICE,QPT.REQUEST_ID,QPT.PROCESS_STATUS,QPT.EXTENDED_PRICE,
204                 QPT.ORDER_UOM_SELLING_PRICE,QPT.CATCHWEIGHT_QTY,QPT.ACTUAL_ORDER_QUANTITY,
205                 QPT.HEADER_ID,QPT.PRICING_EVENT,QPT.CALCULATE_FLAG,QPT.SIMULATION_FLAG,
206                 QPT.GSA_CHECK_FLAG,QPT.GSA_DUP_CHECK_FLAG,QPT.MANUAL_DISCOUNT_FLAG,
207                 QPT.SOURCE_ORDER_AMOUNT_FLAG,
208                 QPT.MANUAL_ADJUSTMENTS_CALL_FLAG MANUAL_ADJUSTMENTS_CALL_FL,
209                 QPT.GET_FREIGHT_FLAG,QPT.VIEW_CODE,QPT.CHECK_CUST_VIEW_FLAG,
210                 QPT.FULL_PRICING_CALL,QPT.USE_MULTI_CURRENCY,QPT.USER_CONVERSION_RATE,
211                 QPT.USER_CONVERSION_TYPE,QPT.FUNCTION_CURRENCY,QPT.LIST_PRICE_OVERRIDE_FLAG,
212                 QPT.CHARGE_PERIODICITY_CODE
213                 FROM QP_INT_LINES_T QPT
214                 WHERE REQUEST_ID = nvl(sys_context(''qp_context'',''request_id''), -9999)';
215 
216           execute immediate l_stmt;
217 
218           l_stmt :=
219             'create or replace view QP_PREQ_LDETS_TMP as
220                 SELECT QPT.LINE_DETAIL_INDEX,QPT.LINE_DETAIL_TYPE_CODE,
221                 QPT.LINE_DETAIL_PBH_TYPE,QPT.PRICE_BREAK_TYPE_CODE,
222                 QPT.LINE_INDEX,QPT.CREATED_FROM_LIST_HEADER_ID,QPT.CREATED_FROM_LIST_LINE_ID,
223                 QPT.CREATED_FROM_LIST_LINE_TYPE,QPT.CREATED_FROM_LIST_TYPE_CODE,
224                 QPT.MODIFIER_LEVEL_CODE,QPT.CREATED_FROM_SQL,QPT.PRICING_GROUP_SEQUENCE,
225                 QPT.OPERAND_CALCULATION_CODE,QPT.OPERAND_VALUE,QPT.ADJUSTMENT_AMOUNT,
226                 QPT.LINE_QUANTITY,QPT.SUBSTITUTION_TYPE_CODE,QPT.SUBSTITUTION_VALUE_FROM,
227                 QPT.SUBSTITUTION_VALUE_TO,QPT.ASK_FOR_FLAG,QPT.PRICE_FORMULA_ID,
228                 QPT.PROCESSED_FLAG,QPT.PRICING_STATUS_CODE,QPT.PRICING_STATUS_TEXT,
229                 QPT.PRODUCT_PRECEDENCE,QPT.INCOMPATABILITY_GRP_CODE,QPT.BEST_PERCENT,
230                 QPT.PRICING_PHASE_ID,QPT.APPLIED_FLAG,QPT.AUTOMATIC_FLAG,
231                 QPT.OVERRIDE_FLAG,QPT.PRINT_ON_INVOICE_FLAG,QPT.PRIMARY_UOM_FLAG,
232                 QPT.BENEFIT_QTY,QPT.BENEFIT_UOM_CODE,QPT.LIST_LINE_NO,QPT.ACCRUAL_FLAG,
233                 QPT.ACCRUAL_CONVERSION_RATE,QPT.ESTIM_ACCRUAL_RATE,QPT.RECURRING_FLAG,
234                 QPT.SELECTED_VOLUME_ATTR,QPT.ROUNDING_FACTOR,QPT.SECONDARY_PRICELIST_IND,
235                 QPT.GROUP_QUANTITY,QPT.GROUP_AMOUNT,QPT.PROCESS_CODE,QPT.UPDATED_FLAG,
236                 QPT.CHARGE_TYPE_CODE,QPT.CHARGE_SUBTYPE_CODE,QPT.LIMIT_CODE,QPT.LIMIT_TEXT,
237                 QPT.HEADER_LIMIT_EXISTS,QPT.LINE_LIMIT_EXISTS,QPT.CALCULATION_CODE,
238                 QPT.CURRENCY_HEADER_ID,QPT.PRICING_EFFECTIVE_DATE,QPT.BASE_CURRENCY_CODE,
239                 QPT.ORDER_CURRENCY,QPT.CURRENCY_DETAIL_ID,QPT.SELLING_ROUNDING_FACTOR,
240                 QPT.CHANGE_REASON_CODE,QPT.CHANGE_REASON_TEXT,QPT.REQUEST_ID,
241                 QPT.PRICE_ADJUSTMENT_ID,QPT.RECURRING_VALUE,QPT.NET_AMOUNT_FLAG,
242                 QPT.ORDER_QTY_OPERAND,QPT.ORDER_QTY_ADJ_AMT,QPT.ACCUM_CONTEXT,
243                 QPT.ACCUM_ATTRIBUTE,QPT.ACCUM_ATTR_RUN_SRC_FLAG,QPT.BREAK_UOM_CODE,
244                 QPT.BREAK_UOM_CONTEXT,QPT.BREAK_UOM_ATTRIBUTE
245                 FROM QP_INT_LDETS_T QPT
246                 WHERE REQUEST_ID = nvl(SYS_CONTEXT(''QP_CONTEXT'',''REQUEST_ID''), -9999)';
247 
248           execute immediate l_stmt;
249 
250           l_stmt :=
251             'create or replace view QP_PREQ_LINE_ATTRS_TMP as
252                 SELECT QPT.LINE_INDEX,QPT.LINE_DETAIL_INDEX,QPT.ATTRIBUTE_LEVEL,
253                 QPT.ATTRIBUTE_TYPE,QPT.LIST_HEADER_ID,QPT.LIST_LINE_ID,QPT.CONTEXT,
254                 QPT.ATTRIBUTE,QPT.VALUE_FROM,QPT.SETUP_VALUE_FROM,QPT.VALUE_TO,
255                 QPT.SETUP_VALUE_TO,QPT.GROUPING_NUMBER,QPT.NO_QUALIFIERS_IN_GRP,
256                 QPT.COMPARISON_OPERATOR_TYPE_CODE,QPT.VALIDATED_FLAG,QPT.APPLIED_FLAG,
257                 QPT.PRICING_STATUS_CODE,QPT.PRICING_STATUS_TEXT,QPT.QUALIFIER_PRECEDENCE,
258                 QPT.PRICING_ATTR_FLAG,QPT.QUALIFIER_TYPE,QPT.DATATYPE,QPT.PRODUCT_UOM_CODE,
259                 QPT.PROCESSED_CODE,QPT.EXCLUDER_FLAG,QPT.GROUP_QUANTITY,QPT.GROUP_AMOUNT,
260                 QPT.DISTINCT_QUALIFIER_FLAG,QPT.PRICING_PHASE_ID,QPT.INCOMPATABILITY_GRP_CODE,
261                 QPT.LINE_DETAIL_TYPE_CODE,QPT.MODIFIER_LEVEL_CODE,QPT.PRIMARY_UOM_FLAG,
262                 QPT.REQUEST_ID,QPT.DERIVED_QUALIFIER_FLAG
263                 FROM QP_INT_LINE_ATTRS_T QPT
264                 WHERE REQUEST_ID = nvl(SYS_CONTEXT(''QP_CONTEXT'',''REQUEST_ID''), -9999)';
265 
266           execute immediate l_stmt;
267 
268           l_stmt :=
269             'create or replace view QP_PREQ_RLTD_LINES_TMP as
270                 SELECT QPT.REQUEST_TYPE_CODE,QPT.LINE_INDEX,QPT.LINE_DETAIL_INDEX,
271                 QPT.RELATIONSHIP_TYPE_CODE,QPT.RELATED_LINE_INDEX,
272                 QPT.RELATED_LINE_DETAIL_INDEX,QPT.PRICING_STATUS_CODE,
273                 QPT.PRICING_STATUS_TEXT,QPT.LIST_LINE_ID,QPT.RELATED_LIST_LINE_ID,
274                 QPT.RELATED_LIST_LINE_TYPE,QPT.OPERAND_CALCULATION_CODE,QPT.OPERAND,
275                 QPT.PRICING_GROUP_SEQUENCE,QPT.RELATIONSHIP_TYPE_DETAIL,QPT.SETUP_VALUE_FROM,
276                 QPT.SETUP_VALUE_TO,QPT.QUALIFIER_VALUE,QPT.ADJUSTMENT_AMOUNT,
277                 QPT.SATISFIED_RANGE_VALUE,QPT.REQUEST_ID
278                 FROM QP_INT_RLTD_LINES_T QPT
279                 WHERE REQUEST_ID = nvl(sys_context(''qp_context'',''request_id''),-9999)';
280 
281           execute immediate l_stmt;
282 
283           l_stmt :=
284             'create or replace view QP_FORMULA_STEP_VALUES_TMP as
285                 SELECT QFT.PRICE_FORMULA_ID,QFT.STEP_NUMBER,QFT.COMPONENT_VALUE,
286                 QFT.PRICE_FORMULA_LINE_TYPE_CODE,QFT.LINE_INDEX,QFT.LIST_LINE_TYPE_CODE,
287                 QFT.LIST_HEADER_ID,QFT.LIST_LINE_ID,QFT.REQUEST_ID
288                 FROM QP_INT_FML_STEP_VALS_T QFT
289                 WHERE REQUEST_ID = nvl(SYS_CONTEXT(''QP_CONTEXT'',''REQUEST_ID''), -9999)';
290 
291           execute immediate l_stmt;
292 
293           l_stmt :=
294             'create or replace view QP_LDETS_V as
295                 SELECT A.LINE_DETAIL_INDEX,A.LINE_DETAIL_TYPE_CODE,A.LINE_INDEX,
296                 A.CREATED_FROM_LIST_HEADER_ID LIST_HEADER_ID,
297                 A.CREATED_FROM_LIST_LINE_ID LIST_LINE_ID,
298                 A.CREATED_FROM_LIST_LINE_TYPE LIST_LINE_TYPE_CODE,
299                 A.PRICE_BREAK_TYPE_CODE,
300                 A.LINE_QUANTITY,A.ADJUSTMENT_AMOUNT,A.AUTOMATIC_FLAG,
301                 A.PRICING_PHASE_ID,A.OPERAND_CALCULATION_CODE,A.OPERAND_VALUE,
302                 A.PRICING_GROUP_SEQUENCE,A.CREATED_FROM_LIST_TYPE_CODE,
303                 A.APPLIED_FLAG,A.PRICING_STATUS_CODE,A.PRICING_STATUS_TEXT,
304                 A.LIMIT_CODE,A.LIMIT_TEXT,A.LIST_LINE_NO,A.GROUP_QUANTITY,
305                 A.UPDATED_FLAG,A.PROCESS_CODE,B.SUBSTITUTION_VALUE SUBSTITUTION_VALUE_TO,
306                 B.SUBSTITUTION_ATTRIBUTE,B.ACCRUAL_FLAG,A.MODIFIER_LEVEL_CODE,
307                 B.ESTIM_GL_VALUE,B.ACCRUAL_CONVERSION_RATE,B.OVERRIDE_FLAG,
308                 B.PRINT_ON_INVOICE_FLAG,B.INVENTORY_ITEM_ID,B.ORGANIZATION_ID,
309                 B.RELATED_ITEM_ID,B.RELATIONSHIP_TYPE_ID,B.ESTIM_ACCRUAL_RATE,
310                 B.EXPIRATION_DATE,B.BENEFIT_PRICE_LIST_LINE_ID,B.RECURRING_FLAG,
311                 B.BENEFIT_LIMIT,A.CHARGE_TYPE_CODE,A.CHARGE_SUBTYPE_CODE,A.BENEFIT_QTY,
312                 B.BENEFIT_UOM_CODE,B.PRORATION_TYPE_CODE,B.INCLUDE_ON_RETURNS_FLAG,
313                 B.REBATE_TRANSACTION_TYPE_CODE,B.NUMBER_EXPIRATION_PERIODS,
314                 B.EXPIRATION_PERIOD_UOM,B.COMMENTS,A.CALCULATION_CODE,
315                 A.CHANGE_REASON_CODE,A.CHANGE_REASON_TEXT,A.PRICE_ADJUSTMENT_ID,
316                 A.NET_AMOUNT_FLAG,A.ORDER_QTY_OPERAND,A.ORDER_QTY_ADJ_AMT,
317                 A.ACCUM_CONTEXT,A.ACCUM_ATTRIBUTE,A.ACCUM_ATTR_RUN_SRC_FLAG,
318                 A.BREAK_UOM_CODE,A.BREAK_UOM_CONTEXT,A.BREAK_UOM_ATTRIBUTE
319                 FROM QP_INT_LDETS_T a, QP_LIST_LINES b
320                 WHERE a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
321                 AND a.PRICING_STATUS_CODE = ''N''
322                 and a.REQUEST_ID = nvl(SYS_CONTEXT(''QP_CONTEXT'',''REQUEST_ID''),-9999)';
323 
324           execute immediate l_stmt;
325 
326           commit;
327 
328           select count(*) into l_pattern_count from qp_patterns;
329           select count(*) into l_attr_grp_count from qp_attribute_groups;
330 
331           if (l_pattern_count <= 0 or l_attr_grp_count <= 0) then
332 
333             err_buff := '';
334             retcode := 0;
335 
336             qp_attr_grp_pvt.pattern_upgrade(
337               err_buff => err_buff,
338               retcode => retcode,
339               p_list_header_id => null,
340               p_low_list_line_id => null,
341               p_high_list_line_id => null,
342               p_no_of_threads => 1,
343               p_spawned_request => 'N');
344 
345             if (err_buff <> '' or retcode <> 0) then
346               return;
347             end if;
348           end if;
349 
350           select count(*) into l_cache_stat_count from qp_cache_stats;
351 
352           if (l_cache_stat_count <= 0) then
353 
354             err_buff := '';
355             retcode := 0;
356 
357             qp_java_engine_cache_pvt.update_cache_stats(
358               err_buff => err_buff,
359               retcode => retcode
360             );
361 
362             if (err_buff <> '' or retcode <> 0) then
363               return;
364             end if;
365 
366           end if;
367 
368         exception
369           when others then
370             err_buff := sqlerrm;
371             retcode := 2;
372         end;
373 
374       end if;
375 
376   else
377 
378     err_buff := '';
379     retcode := 0;
380 
381   end if;
382 
383 end switch;
384 
385 end qp_jpe_ctrl_pvt;