[Home] [Help]
PACKAGE: APPS.QP_PS_ATTR_GRP_PVT
Source
1 package QP_PS_ATTR_GRP_PVT AUTHID CURRENT_USER as
2 /* $Header: QPXPATGS.pls 120.2 2011/01/06 11:29:56 dnema noship $ */
3
4 type number_tbl_type is table of number index by binary_integer;
5 type date_tbl_type is table of date index by binary_integer;
6 type varchar1_tbl_type is table of varchar2(1) index by binary_integer;
7 type varchar3_tbl_type is table of varchar2(3) index by binary_integer;
8 type varchar30_tbl_type is table of varchar2(30) index by binary_integer;
9 type varchar240_tbl_type is table of varchar2(240) index by binary_integer;
10 type varchar2000_tbl_type is table of varchar2(2000) index by binary_integer;
11 type varchar4000_tbl_type is table of varchar2(4000) index by binary_integer;
12
13 type pattern_upg_slab_rec is record
14 (worker number,
15 low_list_line_id number,
16 high_list_line_id number);
17 type pattern_upg_slab_table is table of pattern_upg_slab_rec index by binary_integer;
18
19 g_delimiter varchar2(1) := '|';
20 g_call_from_setup varchar2(1) := 'N';
21 g_list_type VARCHAR2(30) :='JP';
22 G_QP_DEBUG VARCHAR2(1):='Y';
23
24
25 -- cursor tables
26 g_list_header_id_c_tbl number_tbl_type;
27 g_list_line_id_c_tbl number_tbl_type;
28 g_segment_id_c_tbl number_tbl_type;
29 g_active_flag_c_tbl varchar1_tbl_type;
30 g_list_type_code_c_tbl varchar30_tbl_type;
31 g_start_date_active_q_c_tbl date_tbl_type;
32 g_end_date_active_q_c_tbl date_tbl_type;
33 g_currency_code_c_tbl varchar30_tbl_type;
34 g_ask_for_flag_c_tbl varchar1_tbl_type;
35 g_header_limit_exists_c_tbl varchar1_tbl_type;
36 g_line_limit_exists_c_tbl varchar1_tbl_type;
37 g_source_system_code_c_tbl varchar30_tbl_type;
38 g_effective_precedence_c_tbl number_tbl_type;
39 g_qual_grouping_no_c_tbl number_tbl_type;
40 g_comparison_opr_code_c_tbl varchar30_tbl_type;
41 g_pricing_phase_id_c_tbl number_tbl_type;
42 g_modifier_level_code_c_tbl varchar30_tbl_type;
43 g_qual_datatype_c_tbl varchar30_tbl_type;
44 g_qual_attr_val_c_tbl varchar240_tbl_type;
45 g_attribute_type_c_tbl varchar30_tbl_type;
46 g_product_uom_code_c_tbl varchar30_tbl_type; -- only used in pp
47 g_prc_brk_typ_code_c_tbl varchar30_tbl_type;
48 g_pte_code_c_tbl varchar30_tbl_type;
49 ------- jagan
50 g_header_quals_exist_c_tbl varchar1_tbl_type;
51 g_orig_org_id_c_tbl number_tbl_type;
52 g_global_flag_c_tbl varchar1_tbl_type;
53 g_product_uom_code_j_c_tbl varchar30_tbl_type;
54 g_creation_date_c_tbl date_tbl_type;
55
56 g_created_by_c_tbl number_tbl_type;
57 g_request_id_c_tbl number_tbl_type;
58 g_program_update_date_c_tbl date_tbl_type;
59 g_program_id_c_tbl number_tbl_type;
60 g_program_applic_id_c_tbl number_tbl_type;
61 g_start_date_act_firs_c_tbl date_tbl_type;
62 g_end_date_act_firs_c_tbl date_tbl_type;
63 g_start_date_act_sec_c_tbl date_tbl_type;
64 g_end_date_act_sec_c_tbl date_tbl_type;
65 g_start_date_act_h_c_tbl date_tbl_type;
66 g_end_date_act_h_c_tbl date_tbl_type;
67 g_start_date_act_l_c_tbl date_tbl_type;
68 g_end_date_act_l_c_tbl date_tbl_type;
69 g_eq_flag_c_tbl varchar1_tbl_type;
70 g_act_date_firs_type_c_tbl varchar30_tbl_type;
71 g_act_date_sec_type_c_tbl varchar30_tbl_type;
72 g_currency_header_id_c_tbl number_tbl_type;
73 g_desc_quals_exist_c_tbl varchar1_tbl_type;
74 g_list_line_typ_code_c_tbl varchar30_tbl_type;
75 g_automatic_flag_c_tbl varchar1_tbl_type;
76
77 --------jagan
78
79
80 -- temp tables
81 g_list_header_id_tmp_tbl number_tbl_type;
82 g_list_line_id_tmp_tbl number_tbl_type;
83 g_active_flag_tmp_tbl varchar1_tbl_type;
84 g_list_type_code_tmp_tbl varchar30_tbl_type;
85 g_start_date_active_q_tmp_tbl date_tbl_type;
86 g_end_date_active_q_tmp_tbl date_tbl_type;
87 g_currency_code_tmp_tbl varchar30_tbl_type;
88 g_ask_for_flag_tmp_tbl varchar1_tbl_type;
89 g_header_limit_exists_tmp_tbl varchar1_tbl_type;
90 g_line_limit_exists_tmp_tbl varchar1_tbl_type;
91 g_source_system_code_tmp_tbl varchar30_tbl_type;
92 g_effective_precedence_tmp_tbl number_tbl_type;
93 g_qual_grouping_no_tmp_tbl number_tbl_type;
94 g_pricing_phase_id_tmp_tbl number_tbl_type;
95 g_modifier_level_code_tmp_tbl varchar30_tbl_type;
96 g_hash_key_tmp_tbl varchar2000_tbl_type;
97 g_cache_key_tmp_tbl varchar240_tbl_type;
98 g_pat_string_tmp_tbl varchar2000_tbl_type;
99 g_product_uom_code_tmp_tbl varchar30_tbl_type; -- only used in pp
100 g_pricing_attr_count_tmp_tbl number_tbl_type; -- only used in pp
101 g_comparison_opr_code_tmp_tbl varchar30_tbl_type; -- jagan
102 g_pte_code_tmp_tbl varchar30_tbl_type;
103 ------- jagan
104 g_header_quals_exist_tmp_tbl varchar1_tbl_type;
105 g_orig_org_id_tmp_tbl number_tbl_type;
106 g_global_flag_tmp_tbl varchar1_tbl_type;
107 g_product_uom_code_j_tmp_tbl varchar30_tbl_type;
108 g_creation_date_tmp_tbl date_tbl_type;
109
110 g_created_by_tmp_tbl number_tbl_type;
111 g_request_id_tmp_tbl number_tbl_type;
112 g_program_update_date_tmp_tbl date_tbl_type;
113 g_program_id_tmp_tbl number_tbl_type;
114 g_program_applic_id_tmp_tbl number_tbl_type;
115 g_start_date_act_firs_tmp_tbl date_tbl_type;
116 g_end_date_act_firs_tmp_tbl date_tbl_type;
117 g_start_date_act_sec_tmp_tbl date_tbl_type;
118 g_end_date_act_sec_tmp_tbl date_tbl_type;
119 g_start_date_act_h_tmp_tbl date_tbl_type;
120 g_end_date_act_h_tmp_tbl date_tbl_type;
121 g_start_date_act_l_tmp_tbl date_tbl_type;
122 g_end_date_act_l_tmp_tbl date_tbl_type;
123 g_eq_flag_tmp_tbl varchar1_tbl_type;
124 g_attribute_type_tmp_tbl varchar30_tbl_type;
125 g_act_date_firs_type_tmp_tbl varchar30_tbl_type;
126 g_act_date_sec_type_tmp_tbl varchar30_tbl_type;
127 g_currency_header_id_tmp_tbl number_tbl_type;
128 g_other_oprt_count_tmp_tbl number_tbl_type;
129 g_null_other_oprt_cnt_tmp_tbl number_tbl_type;
130 g_desc_quals_exist_tmp_tbl varchar1_tbl_type;
131 g_prc_brk_typ_code_tmp_tbl varchar30_tbl_type;
132 g_list_line_typ_code_tmp_tbl varchar30_tbl_type;
133 g_automatic_flag_tmp_tbl varchar1_tbl_type;
134
135 --------jagan
136
137 -- final tables
138 g_list_header_id_final_tbl number_tbl_type;
139 g_list_line_id_final_tbl number_tbl_type;
140 g_active_flag_final_tbl varchar1_tbl_type;
141 g_list_type_code_final_tbl varchar30_tbl_type;
142 g_st_date_active_q_final_tbl date_tbl_type;
143 g_end_date_active_q_final_tbl date_tbl_type;
144 g_pattern_id_final_tbl number_tbl_type;
145 g_currency_code_final_tbl varchar30_tbl_type;
146 g_ask_for_flag_final_tbl varchar1_tbl_type;
147 g_header_limit_exists_fnl_tbl varchar1_tbl_type;
148 g_line_limit_exists_fnl_tbl varchar1_tbl_type;
149 g_source_system_code_final_tbl varchar30_tbl_type;
150 g_effec_precedence_final_tbl number_tbl_type;
151 g_qual_grouping_no_final_tbl number_tbl_type;
152 g_pricing_phase_id_final_tbl number_tbl_type;
153 g_modifier_lvl_code_final_tbl varchar30_tbl_type;
154 g_hash_key_final_tbl varchar2000_tbl_type;
155 g_cache_key_final_tbl varchar240_tbl_type;
156 g_product_uom_code_final_tbl varchar30_tbl_type; -- only used in pp
157 g_pricing_attr_count_final_tbl number_tbl_type; -- only used in pp
158 g_pattern_segment_ind_fnl_tbl number_tbl_type;
159 g_prc_brk_typ_code_fnl_tbl varchar30_tbl_type;
160 g_pte_code_fnl_tbl varchar30_tbl_type;
161 g_list_line_typ_code_fnl_tbl varchar30_tbl_type;
162 g_automatic_flag_fnl_tbl varchar1_tbl_type;
163 ------- jagan
164 g_header_quals_exist_fnl_tbl varchar1_tbl_type;
165 g_orig_org_id_fnl_tbl number_tbl_type;
166 g_global_flag_fnl_tbl varchar1_tbl_type;
167 g_product_uom_code_j_fnl_tbl varchar30_tbl_type;
168 g_request_id_fnl_tbl number_tbl_type;
169 g_program_update_date_fnl_tbl date_tbl_type;
170 g_program_id_fnl_tbl number_tbl_type;
171 g_program_applic_id_fnl_tbl number_tbl_type;
172 g_start_date_act_firs_fnl_tbl date_tbl_type;
173 g_end_date_act_firs_fnl_tbl date_tbl_type;
174 g_start_date_act_sec_fnl_tbl date_tbl_type;
175 g_end_date_act_sec_fnl_tbl date_tbl_type;
176 g_start_date_act_h_fnl_tbl date_tbl_type;
177 g_end_date_act_h_fnl_tbl date_tbl_type;
178 g_start_date_act_l_fnl_tbl date_tbl_type;
179 g_end_date_act_l_fnl_tbl date_tbl_type;
180 g_eq_flag_fnl_tbl varchar1_tbl_type;
181 g_act_date_first_type_fnl_tbl varchar30_tbl_type;
182 g_act_date_sec_type_fnl_tbl varchar30_tbl_type;
183 g_currency_header_id_fnl_tbl number_tbl_type;
184 g_other_oprt_count_fnl_tbl number_tbl_type;
185 g_null_other_oprt_cnt_fnl_tbl number_tbl_type;
186 g_desc_quals_exist_fnl_tbl varchar1_tbl_type;
187 --------jagan
188 -- the standard who columns for qp_attribute_grps and qp_list_lines
189 g_creation_date_final_tbl date_tbl_type;
190 g_created_by_final_tbl number_tbl_type;
191 g_last_update_date_final_tbl date_tbl_type;
192 g_last_updated_by_final_tbl number_tbl_type;
193 g_last_update_login_final_tbl number_tbl_type;
194 g_program_appl_id_final_tbl number_tbl_type;
195 g_program_id_final_tbl number_tbl_type;
196 g_program_upd_date_final_tbl date_tbl_type;
197 g_request_id_final_tbl number_tbl_type;
198
199 g_pattern_grouping_no_tmp_tbl number_tbl_type;
200 g_pattern_segment_id_tmp_tbl number_tbl_type;
201
202 g_pattern_pattern_id_final_tbl number_tbl_type;
203 g_pattern_segment_id_final_tbl number_tbl_type;
204 g_pattern_pat_type_final_tbl varchar30_tbl_type;
205 g_pattern_pat_string_final_tbl varchar2000_tbl_type;
206 -- the standard who columns for qp_patterns
207 g_pattern_cr_dt_final_tbl date_tbl_type;
208 g_pattern_cr_by_final_tbl number_tbl_type;
209 g_pattern_lst_up_dt_final_tbl date_tbl_type;
210 g_pattern_lt_up_by_final_tbl number_tbl_type;
211 g_pattern_lt_up_lg_final_tbl number_tbl_type;
212 g_pattern_pr_ap_id_final_tbl number_tbl_type;
213 g_pattern_pr_id_final_tbl number_tbl_type;
214 g_pattern_pr_up_dt_final_tbl date_tbl_type;
215 g_pattern_req_id_final_tbl number_tbl_type;
216
217 g_pattern_upg_slab_table pattern_upg_slab_table;
218 g_pattern_upg_chunk_table pattern_upg_slab_table;
219
220 g_init_val constant number := -99999;
221 g_qp_pattern_search VARCHAR2(1) default nvl(FND_PROFILE.VALUE('QP_PATTERN_SEARCH'),'N');
222 --g_eq_flag VARCHAR2(1) DEFAULT NULL;
223
224 --Bug 10634202
225
226 G_PAT_DEBUG_ON CONSTANT varchar2(1):='Y';
227 G_PAT_DEBUG_OFF CONSTANT varchar2(1):='N';
228
229 PROCEDURE Set_Debug(p_debug IN VARCHAR2 DEFAULT 'Y');
230
231 --Bug 10634202 End
232
233 PROCEDURE Pattern_Upgrade (
234 err_buff out NOCOPY VARCHAR2,
235 retcode out NOCOPY NUMBER,
236 p_list_header_id IN NUMBER default null,
237 p_low_list_line_id IN NUMBER default null,
238 p_high_list_line_id IN NUMBER default NULL,
239 p_no_of_threads IN NUMBER default 1,
240 p_spawned_request IN VARCHAR2 default 'N',
241 p_debug VARCHAR2 DEFAULT 'N');
242
243 procedure generate_hp_atgrps(p_list_header_id number
244 ,p_qualifier_group number);
245 procedure generate_lp_atgrps(p_list_header_id number
246 ,p_qualifier_group number
247 ,p_low_list_line_id number
248 ,p_high_list_line_id number);
249 procedure update_pp_lines(p_list_header_id number
250 ,p_low_list_line_id number
251 ,p_high_list_line_id number);
252 procedure process_c_tables(p_pattern_type VARCHAR2);
253 procedure process_c_tables_pp(p_pattern_type VARCHAR2);
254 procedure Move_data_from_tmp_to_final(p_pattern_type VARCHAR2);
255 procedure populate_patterns;
256 procedure populate_atgrps;
257 procedure update_list_lines;
258 procedure populate_pp_atgrps;
259 procedure Reset_c_tables;
260 procedure Reset_tmp_tables;
261 procedure Reset_final_tables;
262 function get_pattern_id(p_pattern_type varchar2, p_pat_string varchar2,
263 p_grp_no number)
264 return number;
265
266 PROCEDURE Populate_Pattern_Phases (
267 p_list_header_id IN NUMBER,
268 p_pricing_phase_id IN NUMBER,
269 p_pattern_id IN NUMBER);
270
271 -- main procedure to process product pattern
272 PROCEDURE Product_Pattern_Main (
273 p_list_header_id IN NUMBER ,
274 p_list_line_id IN NUMBER ,
275 p_setup_action IN VARCHAR2 );
276
277 -- main procedure to process header pattern
278 PROCEDURE Header_Pattern_Main (
279 p_list_header_id IN NUMBER
280 ,p_qualifier_group IN NUMBER
284 -- main procedure to process line pattern
281 ,p_setup_action IN VARCHAR2
282 );
283
285 PROCEDURE Line_Pattern_Main (
286 p_list_header_id IN NUMBER
287 ,p_list_line_id IN NUMBER
288 ,p_qualifier_group IN NUMBER
289 ,p_setup_action IN VARCHAR2 );
290
291 -- procedure to remove product pattern
292 PROCEDURE Remove_Prod_Pattern_for_Line(p_list_line_id IN NUMBER);
293
294 PROCEDURE Update_Qual_Segment_id(p_list_header_id IN NUMBER
295 ,p_qualifier_group IN NUMBER
296 ,p_low_list_line_id number
297 ,p_high_list_line_id number);
298
299 PROCEDURE Update_Prod_Pric_Segment_id(p_list_header_id IN NUMBER
300 ,p_low_list_line_id number
301 ,p_high_list_line_id number);
302
303 procedure update_list_lines_cache_key;
304
305 PROCEDURE write_log
306 (log_text VARCHAR2);
307 end QP_PS_ATTR_GRP_PVT; -- end package