[Home] [Help]
PACKAGE: APPS.QP_ATTR_GRP_PVT
Source
1 package QP_ATTR_GRP_PVT AUTHID CURRENT_USER as
2 /* $Header: QPXVATGS.pls 120.0.12010000.2 2009/06/04 07:29:00 jputta ship $ */
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
22 -- cursor tables
23 g_list_header_id_c_tbl number_tbl_type;
24 g_list_line_id_c_tbl number_tbl_type;
25 g_segment_id_c_tbl number_tbl_type;
26 g_active_flag_c_tbl varchar1_tbl_type;
27 g_list_type_code_c_tbl varchar30_tbl_type;
28 g_start_date_active_q_c_tbl date_tbl_type;
29 g_end_date_active_q_c_tbl date_tbl_type;
30 g_currency_code_c_tbl varchar30_tbl_type;
31 g_ask_for_flag_c_tbl varchar1_tbl_type;
32 g_limit_exists_c_tbl varchar1_tbl_type;
33 g_source_system_code_c_tbl varchar30_tbl_type;
34 g_effective_precedence_c_tbl number_tbl_type;
35 g_qual_grouping_no_c_tbl number_tbl_type;
36 g_comparison_opr_code_c_tbl varchar30_tbl_type;
37 g_pricing_phase_id_c_tbl number_tbl_type;
38 g_modifier_level_code_c_tbl varchar30_tbl_type;
39 g_qual_datatype_c_tbl varchar30_tbl_type;
40 g_qual_attr_val_c_tbl varchar240_tbl_type;
41 g_attribute_type_c_tbl varchar30_tbl_type;
42 g_product_uom_code_c_tbl varchar30_tbl_type; -- only used in pp
43
44 -- temp tables
45 g_list_header_id_tmp_tbl number_tbl_type;
46 g_list_line_id_tmp_tbl number_tbl_type;
47 g_active_flag_tmp_tbl varchar1_tbl_type;
48 g_list_type_code_tmp_tbl varchar30_tbl_type;
49 g_start_date_active_q_tmp_tbl date_tbl_type;
50 g_end_date_active_q_tmp_tbl date_tbl_type;
51 g_currency_code_tmp_tbl varchar30_tbl_type;
52 g_ask_for_flag_tmp_tbl varchar1_tbl_type;
53 g_limit_exists_tmp_tbl varchar1_tbl_type;
54 g_source_system_code_tmp_tbl varchar30_tbl_type;
55 g_effective_precedence_tmp_tbl number_tbl_type;
56 g_qual_grouping_no_tmp_tbl number_tbl_type;
57 g_pricing_phase_id_tmp_tbl number_tbl_type;
58 g_modifier_level_code_tmp_tbl varchar30_tbl_type;
59 g_hash_key_tmp_tbl varchar2000_tbl_type;
60 g_cache_key_tmp_tbl varchar240_tbl_type;
61 g_pat_string_tmp_tbl varchar2000_tbl_type;
62 g_product_uom_code_tmp_tbl varchar30_tbl_type; -- only used in pp
63 g_pricing_attr_count_tmp_tbl number_tbl_type; -- only used in pp
64
65 -- final tables
66 g_list_header_id_final_tbl number_tbl_type;
67 g_list_line_id_final_tbl number_tbl_type;
68 g_active_flag_final_tbl varchar1_tbl_type;
69 g_list_type_code_final_tbl varchar30_tbl_type;
70 g_st_date_active_q_final_tbl date_tbl_type;
71 g_end_date_active_q_final_tbl date_tbl_type;
72 g_pattern_id_final_tbl number_tbl_type;
73 g_currency_code_final_tbl varchar30_tbl_type;
74 g_ask_for_flag_final_tbl varchar1_tbl_type;
75 g_limit_exists_final_tbl varchar1_tbl_type;
76 g_source_system_code_final_tbl varchar30_tbl_type;
77 g_effec_precedence_final_tbl number_tbl_type;
78 g_qual_grouping_no_final_tbl number_tbl_type;
79 g_pricing_phase_id_final_tbl number_tbl_type;
80 g_modifier_lvl_code_final_tbl varchar30_tbl_type;
81 g_hash_key_final_tbl varchar2000_tbl_type;
82 g_cache_key_final_tbl varchar240_tbl_type;
83 g_product_uom_code_final_tbl varchar30_tbl_type; -- only used in pp
84 g_pricing_attr_count_final_tbl number_tbl_type; -- only used in pp
85 -- the standard who columns for qp_attribute_grps and qp_list_lines
86 g_creation_date_final_tbl date_tbl_type;
87 g_created_by_final_tbl number_tbl_type;
88 g_last_update_date_final_tbl date_tbl_type;
89 g_last_updated_by_final_tbl number_tbl_type;
90 g_last_update_login_final_tbl number_tbl_type;
91 g_program_appl_id_final_tbl number_tbl_type;
92 g_program_id_final_tbl number_tbl_type;
93 g_program_upd_date_final_tbl date_tbl_type;
94 g_request_id_final_tbl number_tbl_type;
95
96 g_pattern_grouping_no_tmp_tbl number_tbl_type;
97 g_pattern_segment_id_tmp_tbl number_tbl_type;
98
99 g_pattern_pattern_id_final_tbl number_tbl_type;
100 g_pattern_segment_id_final_tbl number_tbl_type;
101 g_pattern_pat_type_final_tbl varchar30_tbl_type;
102 g_pattern_pat_string_final_tbl varchar2000_tbl_type;
103 -- the standard who columns for qp_patterns
104 g_pattern_cr_dt_final_tbl date_tbl_type;
105 g_pattern_cr_by_final_tbl number_tbl_type;
106 g_pattern_lst_up_dt_final_tbl date_tbl_type;
107 g_pattern_lt_up_by_final_tbl number_tbl_type;
108 g_pattern_lt_up_lg_final_tbl number_tbl_type;
109 g_pattern_pr_ap_id_final_tbl number_tbl_type;
110 g_pattern_pr_id_final_tbl number_tbl_type;
111 g_pattern_pr_up_dt_final_tbl date_tbl_type;
112 g_pattern_req_id_final_tbl number_tbl_type;
113
114 g_pattern_upg_slab_table pattern_upg_slab_table;
115
116 g_init_val constant number := -99999;
117
118 PROCEDURE Pattern_Upgrade (
119 err_buff out NOCOPY VARCHAR2,
120 retcode out NOCOPY NUMBER,
121 p_list_header_id IN NUMBER default null,
122 p_low_list_line_id IN NUMBER default null,
123 p_high_list_line_id IN NUMBER default NULL,
124 p_no_of_threads IN NUMBER default 1,
125 p_spawned_request IN VARCHAR2 default 'N',
126 p_debug IN VARCHAR2 DEFAULT 'N');
127
128 procedure generate_hp_atgrps(p_list_header_id number
129 ,p_qualifier_group number);
130 procedure generate_lp_atgrps(p_list_header_id number
131 ,p_qualifier_group number
132 ,p_low_list_line_id number
133 ,p_high_list_line_id number);
134 procedure update_pp_lines(p_list_header_id number
135 ,p_low_list_line_id number
136 ,p_high_list_line_id number);
137 procedure process_c_tables(p_pattern_type VARCHAR2);
138 procedure process_c_tables_pp(p_pattern_type VARCHAR2);
139 procedure Move_data_from_tmp_to_final(p_pattern_type VARCHAR2);
140 procedure populate_patterns;
141 procedure populate_atgrps;
142 procedure update_list_lines;
143 procedure Reset_c_tables;
144 procedure Reset_tmp_tables;
145 procedure Reset_final_tables;
146 function get_pattern_id(p_pattern_type varchar2, p_pat_string varchar2,
147 p_grp_no number)
148 return number;
149
150 PROCEDURE Populate_Pattern_Phases (
151 p_list_header_id IN NUMBER,
152 p_pricing_phase_id IN NUMBER,
153 p_pattern_id IN NUMBER);
154
155 -- main procedure to process product pattern
156 PROCEDURE Product_Pattern_Main (
157 p_list_header_id IN NUMBER ,
158 p_list_line_id IN NUMBER ,
159 p_setup_action IN VARCHAR2 );
160
161 -- main procedure to process header pattern
162 PROCEDURE Header_Pattern_Main (
163 p_list_header_id IN NUMBER
164 ,p_qualifier_group IN NUMBER
165 ,p_setup_action IN VARCHAR2
166 );
167
168 -- main procedure to process line pattern
169 PROCEDURE Line_Pattern_Main (
170 p_list_header_id IN NUMBER
171 ,p_list_line_id IN NUMBER
172 ,p_qualifier_group IN NUMBER
173 ,p_setup_action IN VARCHAR2 );
174
175 -- procedure to remove product pattern
176 PROCEDURE Remove_Prod_Pattern_for_Line(p_list_line_id IN NUMBER);
177
178 PROCEDURE Update_Qual_Segment_id(p_list_header_id IN NUMBER
179 ,p_qualifier_group IN NUMBER
180 ,p_low_list_line_id number
181 ,p_high_list_line_id number);
182
183 PROCEDURE Update_Prod_Pric_Segment_id(p_list_header_id IN NUMBER
184 ,p_low_list_line_id number
185 ,p_high_list_line_id number);
186
187 procedure update_list_lines_cache_key;
188
189 end QP_ATTR_GRP_PVT; -- end package