[Home] [Help]
PACKAGE BODY: APPS.QPR_LOAD_MEAS_DATA
Source
1 PACKAGE BODY QPR_LOAD_MEAS_DATA AS
2 /* $Header: QPRUFMDB.pls 120.17 2008/05/29 06:01:26 vinnaray ship $ */
3
4 type char_type is table of varchar2(30) index by varchar2(30);
5 type qpr_src_qtn_line_type is table of QPR_PN_INT_LINES%rowtype;
6 type qpr_src_qtn_adj_type is table of qpr_pn_int_pr_adjs%rowtype;
7
8 g_request_id number;
9 g_instance_id number;
10 g_meas_type varchar2(30);
11 g_sys_date date;
12 g_user_id number;
13 g_login_id number;
14 g_prg_appl_id number;
15 g_prg_id number;
16
17 r_srcrecs QPR_MEAS_DATA_TYPE;
18 r_insrecs QPR_MEAS_DATA_TYPE;
19 r_updrecs QPR_MEAS_DATA_TYPE1;
20 r_meas_data QPR_DIM_DATA_TYPE;
21
22 l_upd_ctr pls_integer := 1;
23 l_ins_ctr pls_integer := 1;
24 g_src_cols char240_type;
25 g_trg_cols char240_type;
26
27 g_t_src_lines QPR_SRC_QTN_LINE_TYPE;
28 g_r_ins_deal QPR_DEAL_LINE_TYPE;
29 g_r_upd_deal QPR_DEAL_LINE_TYPE;
30 g_quote_hdr_sd QPR_PN_INT_HEADERS.SOURCE_REF_HEADER_SHORT_DESC%TYPE;
31 g_source_id number;
32
33 num_meas_cname CONSTANT varchar2(30) := 'MEASURE#_NUMBER';
34 char_meas_cname CONSTANT varchar2(30) := 'MEASURE#_CHAR';
35 max_num_meas CONSTANT pls_integer := 30;
36 max_char_meas CONSTANT pls_integer := 10;
37 MEAS_TYPE_SALES CONSTANT varchar2(30) := 'SALESDATA';
38 MEAS_TYPE_ADJ CONSTANT varchar2(30) := 'ADJUSTMENT';
39 MEAS_TYPE_OFFADJ CONSTANT varchar2(30) := 'OFFADJDATA';
40 OM_MEAS_TYPE_DEALINT CONSTANT varchar2(30) := 'OM_DEALINT';
41 ASO_MEAS_TYPE_DEALINT CONSTANT varchar2(30) := 'ASO_DEALINT';
42 DEAL_HEADER_TBL CONSTANT varchar2(30) := 'QPR_PN_INT_HEADERS';
43 DEAL_LINE_TBL CONSTANT varchar2(30) := 'QPR_PN_INT_LINES';
44 SEEDED_INSTANCE_ID CONSTANT number := 1;
45
46 NO_TBL_DEF exception;
47
48 s_dim_select CONSTANT varchar2(1000) := 'select SR_ORDER_LINE_PK, SR_ADJ_ID_PK,
49 BOOKED_DATE, SR_CUSTOMER_PK, SR_SHIP_TO_LOC_PK, SR_ITEM_PK, SR_SALES_REP_PK,
50 SR_SALES_CHANNEL_PK, OU_ID, SR_USER_DEFINED1_PK, SR_USER_DEFINED2_PK,
51 SR_USER_DEFINED3_PK, SR_USER_DEFINED4_PK, SR_USER_DEFINED5_PK, ';
52
53 s_where_clause CONSTANT varchar2(1000) := ' where booked_date between :d1 and
54 :d2 order by SR_ORDER_LINE_PK, SR_ADJ_ID_PK, BOOKED_DATE, SR_CUSTOMER_PK,
55 SR_SHIP_TO_LOC_PK, SR_ITEM_PK, SR_SALES_REP_PK, SR_SALES_CHANNEL_PK,
56 OU_ID, SR_USER_DEFINED1_PK, SR_USER_DEFINED2_PK, SR_USER_DEFINED3_PK,
57 SR_USER_DEFINED4_PK, SR_USER_DEFINED5_PK' ;
58
59 s_where_clause1 CONSTANT varchar2(1000) := ' where trx_date between :d1 and
60 :d2 order by SR_ORDER_LINE_PK, SR_ADJ_ID_PK, BOOKED_DATE, SR_CUSTOMER_PK,
61 SR_SHIP_TO_LOC_PK, SR_ITEM_PK, SR_SALES_REP_PK, SR_SALES_CHANNEL_PK,
62 OU_ID, SR_USER_DEFINED1_PK, SR_USER_DEFINED2_PK, SR_USER_DEFINED3_PK,
63 SR_USER_DEFINED4_PK, SR_USER_DEFINED5_PK' ;
64
65 procedure log_debug(p_text in varchar2) is
66 begin
67 fnd_file.put_line(fnd_file.log, p_text);
68 --pp_debug(p_text);
69 if (g_origin = 660 or g_origin = 697) then
70 qpr_deal_pvt.debug_ext_log(p_text, g_origin);
71 end if;
72 end;
73
74 function get_select_meas_sql(p_src_tbl_name varchar2, p_meas_type varchar2)
75 return varchar2 is
76 b_first boolean := true;
77 s_sql varchar2(30000) := '';
78 s_meas_sql varchar2(10000) := '';
79 meas_name varchar2(30) := '';
80 t_src_trg_cols char_type;
81 begin
82 for i in g_src_cols.first..g_src_cols.last loop
83 t_src_trg_cols(g_trg_cols(i)) := g_src_cols(i);
84 end loop;
85 -- loop to include measures of number type in select
86 for i in 1..max_num_meas loop
87 meas_name := replace(num_meas_cname, '#', i);
88 if t_src_trg_cols.exists(meas_name) then
89 s_meas_sql := s_meas_sql || t_src_trg_cols(meas_name);
90 else
91 s_meas_sql := s_meas_sql || 'null ';
92 end if;
93 -- if i < max_num_meas then
94 s_meas_sql := s_meas_sql || ', ';
95 -- end if;
96 end loop;
97
98 -- loop to include measures of char type in select
99 for i in 1..max_char_meas loop
100 meas_name := replace(char_meas_cname, '#', i);
101 if t_src_trg_cols.exists(meas_name) then
102 s_meas_sql := s_meas_sql || t_src_trg_cols(meas_name);
103 else
104 s_meas_sql := s_meas_sql || 'null ';
105 end if;
106 -- if i < max_char_meas then
107 s_meas_sql := s_meas_sql || ', ';
108 -- end if;
109 end loop;
110
111 if t_src_trg_cols.exists('MEASURE_UOM') then
112 s_meas_sql := s_meas_sql || t_src_trg_cols('MEASURE_UOM');
113 else
114 s_meas_sql := s_meas_sql || 'null';
115 end if;
116
117 s_sql := s_dim_select || s_meas_sql || ' from ' || p_src_tbl_name;
118 if p_meas_type = MEAS_TYPE_OFFADJ then
119 s_sql := s_sql || s_where_clause1;
120 else
121 s_sql := s_sql || s_where_clause;
122 end if;
123 log_debug('Complete SQL:' || s_sql);
124 t_src_trg_cols.delete;
125 return(s_sql);
126 exception
127 when OTHERS then
128 log_debug('ERROR FORMING MEASURE SELECT...');
129 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
130 raise;
131 end get_select_meas_sql;
132
133 procedure assign_val_to_ins(src_ctr in PLS_INTEGER) is
134 begin
135 r_insrecs.ORD_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.ORD_LEVEL_VALUE(src_ctr);
136 r_insrecs.ADJ_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.ADJ_LEVEL_VALUE(src_ctr);
137 r_insrecs.TIME_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.TIME_LEVEL_VALUE(src_ctr);
138 r_insrecs.CUS_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.CUS_LEVEL_VALUE(src_ctr);
139 r_insrecs.GEO_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.GEO_LEVEL_VALUE(src_ctr);
140 r_insrecs.PRD_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.PRD_LEVEL_VALUE(src_ctr);
141 r_insrecs.REP_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.REP_LEVEL_VALUE(src_ctr);
142 r_insrecs.CHN_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.CHN_LEVEL_VALUE(src_ctr);
143 r_insrecs.ORG_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.ORG_LEVEL_VALUE(src_ctr);
144 r_insrecs.USR1_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.USR1_LEVEL_VALUE(src_ctr);
145 r_insrecs.USR2_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.USR2_LEVEL_VALUE(src_ctr);
146 r_insrecs.USR3_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.USR3_LEVEL_VALUE(src_ctr);
147 r_insrecs.USR4_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.USR4_LEVEL_VALUE(src_ctr);
148 r_insrecs.USR5_LEVEL_VALUE(l_ins_ctr) := r_srcrecs.USR5_LEVEL_VALUE(src_ctr);
149 r_insrecs.MEASURE1_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE1_NUMBER(src_ctr);
150 r_insrecs.MEASURE2_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE2_NUMBER(src_ctr);
151 r_insrecs.MEASURE3_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE3_NUMBER(src_ctr);
152 r_insrecs.MEASURE4_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE4_NUMBER(src_ctr);
153 r_insrecs.MEASURE5_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE5_NUMBER(src_ctr);
154 r_insrecs.MEASURE6_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE6_NUMBER(src_ctr);
155 r_insrecs.MEASURE7_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE7_NUMBER(src_ctr);
156 r_insrecs.MEASURE8_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE8_NUMBER(src_ctr);
157 r_insrecs.MEASURE9_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE9_NUMBER(src_ctr);
158 r_insrecs.MEASURE10_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE10_NUMBER(src_ctr);
159 r_insrecs.MEASURE11_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE11_NUMBER(src_ctr);
160 r_insrecs.MEASURE12_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE12_NUMBER(src_ctr);
161 r_insrecs.MEASURE13_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE13_NUMBER(src_ctr);
162 r_insrecs.MEASURE14_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE14_NUMBER(src_ctr);
163 r_insrecs.MEASURE15_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE15_NUMBER(src_ctr);
164 r_insrecs.MEASURE16_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE16_NUMBER(src_ctr);
165 r_insrecs.MEASURE17_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE17_NUMBER(src_ctr);
166 r_insrecs.MEASURE18_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE18_NUMBER(src_ctr);
167 r_insrecs.MEASURE19_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE19_NUMBER(src_ctr);
168 r_insrecs.MEASURE20_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE20_NUMBER(src_ctr);
169 r_insrecs.MEASURE21_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE21_NUMBER(src_ctr);
170 r_insrecs.MEASURE22_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE22_NUMBER(src_ctr);
171 r_insrecs.MEASURE23_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE23_NUMBER(src_ctr);
172 r_insrecs.MEASURE24_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE24_NUMBER(src_ctr);
173 r_insrecs.MEASURE25_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE25_NUMBER(src_ctr);
174 r_insrecs.MEASURE26_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE26_NUMBER(src_ctr);
175 r_insrecs.MEASURE27_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE27_NUMBER(src_ctr);
176 r_insrecs.MEASURE28_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE28_NUMBER(src_ctr);
177 r_insrecs.MEASURE29_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE29_NUMBER(src_ctr);
178 r_insrecs.MEASURE30_NUMBER(l_ins_ctr) := r_srcrecs.MEASURE30_NUMBER(src_ctr);
179 r_insrecs.MEASURE1_CHAR(l_ins_ctr) := r_srcrecs.MEASURE1_CHAR(src_ctr);
180 r_insrecs.MEASURE2_CHAR(l_ins_ctr) := r_srcrecs.MEASURE2_CHAR(src_ctr);
181 r_insrecs.MEASURE3_CHAR(l_ins_ctr) := r_srcrecs.MEASURE3_CHAR(src_ctr);
182 r_insrecs.MEASURE4_CHAR(l_ins_ctr) := r_srcrecs.MEASURE4_CHAR(src_ctr);
183 r_insrecs.MEASURE5_CHAR(l_ins_ctr) := r_srcrecs.MEASURE5_CHAR(src_ctr);
184 r_insrecs.MEASURE6_CHAR(l_ins_ctr) := r_srcrecs.MEASURE6_CHAR(src_ctr);
185 r_insrecs.MEASURE7_CHAR(l_ins_ctr) := r_srcrecs.MEASURE7_CHAR(src_ctr);
186 r_insrecs.MEASURE8_CHAR(l_ins_ctr) := r_srcrecs.MEASURE8_CHAR(src_ctr);
187 r_insrecs.MEASURE9_CHAR(l_ins_ctr) := r_srcrecs.MEASURE9_CHAR(src_ctr);
188 r_insrecs.MEASURE10_CHAR(l_ins_ctr) := r_srcrecs.MEASURE10_CHAR(src_ctr);
189 r_insrecs.MEASURE_UOM(l_ins_ctr) := r_srcrecs.MEASURE_UOM(src_ctr);
190 exception
191 when OTHERS then
192 log_debug('ERROR ASSIGNING VALUES TO INSERT...');
193 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
194 raise;
195 end assign_val_to_ins;
196
197 procedure assign_upd_measure_values(src_ctr in PLS_INTEGER) is
198 begin
199 r_updrecs.MEASURE1_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE1_NUMBER(src_ctr);
200 r_updrecs.MEASURE2_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE2_NUMBER(src_ctr);
201 r_updrecs.MEASURE3_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE3_NUMBER(src_ctr);
202 r_updrecs.MEASURE4_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE4_NUMBER(src_ctr);
203 r_updrecs.MEASURE5_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE5_NUMBER(src_ctr);
204 r_updrecs.MEASURE6_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE6_NUMBER(src_ctr);
205 r_updrecs.MEASURE7_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE7_NUMBER(src_ctr);
206 r_updrecs.MEASURE8_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE8_NUMBER(src_ctr);
207 r_updrecs.MEASURE9_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE9_NUMBER(src_ctr);
208 r_updrecs.MEASURE10_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE10_NUMBER(src_ctr);
209 r_updrecs.MEASURE11_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE11_NUMBER(src_ctr);
210 r_updrecs.MEASURE12_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE12_NUMBER(src_ctr);
211 r_updrecs.MEASURE13_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE13_NUMBER(src_ctr);
212 r_updrecs.MEASURE14_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE14_NUMBER(src_ctr);
213 r_updrecs.MEASURE15_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE15_NUMBER(src_ctr);
214 r_updrecs.MEASURE16_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE16_NUMBER(src_ctr);
215 r_updrecs.MEASURE17_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE17_NUMBER(src_ctr);
216 r_updrecs.MEASURE18_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE18_NUMBER(src_ctr);
217 r_updrecs.MEASURE19_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE19_NUMBER(src_ctr);
218 r_updrecs.MEASURE20_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE20_NUMBER(src_ctr);
219 r_updrecs.MEASURE21_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE21_NUMBER(src_ctr);
220 r_updrecs.MEASURE22_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE22_NUMBER(src_ctr);
221 r_updrecs.MEASURE23_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE23_NUMBER(src_ctr);
222 r_updrecs.MEASURE24_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE24_NUMBER(src_ctr);
223 r_updrecs.MEASURE25_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE25_NUMBER(src_ctr);
224 r_updrecs.MEASURE26_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE26_NUMBER(src_ctr);
225 r_updrecs.MEASURE27_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE27_NUMBER(src_ctr);
226 r_updrecs.MEASURE28_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE28_NUMBER(src_ctr);
227 r_updrecs.MEASURE29_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE29_NUMBER(src_ctr);
228 r_updrecs.MEASURE30_NUMBER(l_upd_ctr) := r_srcrecs.MEASURE30_NUMBER(src_ctr);
229 r_updrecs.MEASURE1_CHAR(l_upd_ctr) := r_srcrecs.MEASURE1_CHAR(src_ctr);
230 r_updrecs.MEASURE2_CHAR(l_upd_ctr) := r_srcrecs.MEASURE2_CHAR(src_ctr);
231 r_updrecs.MEASURE3_CHAR(l_upd_ctr) := r_srcrecs.MEASURE3_CHAR(src_ctr);
232 r_updrecs.MEASURE4_CHAR(l_upd_ctr) := r_srcrecs.MEASURE4_CHAR(src_ctr);
233 r_updrecs.MEASURE5_CHAR(l_upd_ctr) := r_srcrecs.MEASURE5_CHAR(src_ctr);
234 r_updrecs.MEASURE6_CHAR(l_upd_ctr) := r_srcrecs.MEASURE6_CHAR(src_ctr);
235 r_updrecs.MEASURE7_CHAR(l_upd_ctr) := r_srcrecs.MEASURE7_CHAR(src_ctr);
236 r_updrecs.MEASURE8_CHAR(l_upd_ctr) := r_srcrecs.MEASURE8_CHAR(src_ctr);
237 r_updrecs.MEASURE9_CHAR(l_upd_ctr) := r_srcrecs.MEASURE9_CHAR(src_ctr);
238 r_updrecs.MEASURE10_CHAR(l_upd_ctr) := r_srcrecs.MEASURE10_CHAR(src_ctr);
239 r_updrecs.MEASURE_UOM(l_upd_ctr) := r_srcrecs.MEASURE_UOM(src_ctr);
240 exception
241 when OTHERS then
242 log_debug('ERROR ASSIGNING VALUES TO UPDATE...');
243 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
244 raise;
245 end assign_upd_measure_values;
246
247 procedure delete_ins_rec_data is
248 begin
249 r_insrecs.ORD_LEVEL_VALUE.delete;
250 r_insrecs.ADJ_LEVEL_VALUE.delete;
251 r_insrecs.TIME_LEVEL_VALUE.delete;
252 r_insrecs.CUS_LEVEL_VALUE.delete;
253 r_insrecs.GEO_LEVEL_VALUE.delete;
254 r_insrecs.PRD_LEVEL_VALUE.delete;
255 r_insrecs.REP_LEVEL_VALUE.delete;
256 r_insrecs.CHN_LEVEL_VALUE.delete;
257 r_insrecs.ORG_LEVEL_VALUE.delete;
258 r_insrecs.USR1_LEVEL_VALUE.delete;
259 r_insrecs.USR2_LEVEL_VALUE.delete;
260 r_insrecs.USR3_LEVEL_VALUE.delete;
261 r_insrecs.USR4_LEVEL_VALUE.delete;
262 r_insrecs.USR5_LEVEL_VALUE.delete;
263 r_insrecs.MEASURE1_NUMBER.delete;
264 r_insrecs.MEASURE2_NUMBER.delete;
265 r_insrecs.MEASURE3_NUMBER.delete;
266 r_insrecs.MEASURE4_NUMBER.delete;
267 r_insrecs.MEASURE5_NUMBER.delete;
268 r_insrecs.MEASURE6_NUMBER.delete;
269 r_insrecs.MEASURE7_NUMBER.delete;
270 r_insrecs.MEASURE8_NUMBER.delete;
271 r_insrecs.MEASURE9_NUMBER.delete;
272 r_insrecs.MEASURE10_NUMBER.delete;
273 r_insrecs.MEASURE11_NUMBER.delete;
274 r_insrecs.MEASURE12_NUMBER.delete;
275 r_insrecs.MEASURE13_NUMBER.delete;
276 r_insrecs.MEASURE14_NUMBER.delete;
277 r_insrecs.MEASURE15_NUMBER.delete;
278 r_insrecs.MEASURE16_NUMBER.delete;
279 r_insrecs.MEASURE17_NUMBER.delete;
280 r_insrecs.MEASURE18_NUMBER.delete;
281 r_insrecs.MEASURE19_NUMBER.delete;
282 r_insrecs.MEASURE20_NUMBER.delete;
283 r_insrecs.MEASURE21_NUMBER.delete;
284 r_insrecs.MEASURE22_NUMBER.delete;
285 r_insrecs.MEASURE23_NUMBER.delete;
286 r_insrecs.MEASURE24_NUMBER.delete;
287 r_insrecs.MEASURE25_NUMBER.delete;
288 r_insrecs.MEASURE26_NUMBER.delete;
289 r_insrecs.MEASURE27_NUMBER.delete;
290 r_insrecs.MEASURE28_NUMBER.delete;
291 r_insrecs.MEASURE29_NUMBER.delete;
292 r_insrecs.MEASURE30_NUMBER.delete;
293 r_insrecs.MEASURE1_CHAR.delete;
294 r_insrecs.MEASURE2_CHAR.delete;
295 r_insrecs.MEASURE3_CHAR.delete;
296 r_insrecs.MEASURE4_CHAR.delete;
297 r_insrecs.MEASURE5_CHAR.delete;
298 r_insrecs.MEASURE6_CHAR.delete;
299 r_insrecs.MEASURE7_CHAR.delete;
300 r_insrecs.MEASURE8_CHAR.delete;
301 r_insrecs.MEASURE9_CHAR.delete;
302 r_insrecs.MEASURE10_CHAR.delete;
303 r_insrecs.MEASURE_UOM.delete;
304 exception
305 when OTHERS then
306 log_debug('ERROR CLEARING INSERT REC BUFFER...');
307 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
308 raise;
309 end delete_ins_rec_data;
310
311 procedure delete_upd_rec_data is
312 begin
313 r_updrecs.MEASURE_VALUE_ID.delete;
314 r_updrecs.MEASURE1_NUMBER.delete;
315 r_updrecs.MEASURE2_NUMBER.delete;
316 r_updrecs.MEASURE3_NUMBER.delete;
317 r_updrecs.MEASURE4_NUMBER.delete;
318 r_updrecs.MEASURE5_NUMBER.delete;
319 r_updrecs.MEASURE6_NUMBER.delete;
320 r_updrecs.MEASURE7_NUMBER.delete;
321 r_updrecs.MEASURE8_NUMBER.delete;
322 r_updrecs.MEASURE9_NUMBER.delete;
323 r_updrecs.MEASURE10_NUMBER.delete;
324 r_updrecs.MEASURE11_NUMBER.delete;
325 r_updrecs.MEASURE12_NUMBER.delete;
326 r_updrecs.MEASURE13_NUMBER.delete;
327 r_updrecs.MEASURE14_NUMBER.delete;
328 r_updrecs.MEASURE15_NUMBER.delete;
329 r_updrecs.MEASURE16_NUMBER.delete;
330 r_updrecs.MEASURE17_NUMBER.delete;
331 r_updrecs.MEASURE18_NUMBER.delete;
332 r_updrecs.MEASURE19_NUMBER.delete;
333 r_updrecs.MEASURE20_NUMBER.delete;
334 r_updrecs.MEASURE21_NUMBER.delete;
335 r_updrecs.MEASURE22_NUMBER.delete;
336 r_updrecs.MEASURE23_NUMBER.delete;
337 r_updrecs.MEASURE24_NUMBER.delete;
338 r_updrecs.MEASURE25_NUMBER.delete;
339 r_updrecs.MEASURE26_NUMBER.delete;
340 r_updrecs.MEASURE27_NUMBER.delete;
341 r_updrecs.MEASURE28_NUMBER.delete;
342 r_updrecs.MEASURE29_NUMBER.delete;
343 r_updrecs.MEASURE30_NUMBER.delete;
344 r_updrecs.MEASURE1_CHAR.delete;
345 r_updrecs.MEASURE2_CHAR.delete;
346 r_updrecs.MEASURE3_CHAR.delete;
347 r_updrecs.MEASURE4_CHAR.delete;
348 r_updrecs.MEASURE5_CHAR.delete;
349 r_updrecs.MEASURE6_CHAR.delete;
350 r_updrecs.MEASURE7_CHAR.delete;
351 r_updrecs.MEASURE8_CHAR.delete;
352 r_updrecs.MEASURE9_CHAR.delete;
353 r_updrecs.MEASURE10_CHAR.delete;
354 r_updrecs.MEASURE_UOM.delete;
355 exception
356 when OTHERS then
357 log_debug('ERROR CLEARING UPDATE REC BUFFER...');
358 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
359 raise;
360 end delete_upd_rec_data;
361
362 procedure insert_measdata is
363 begin
364 forall i in r_insrecs.ORD_LEVEL_VALUE.FIRST..r_insrecs.ORD_LEVEL_VALUE.LAST
365 insert into QPR_MEASURE_DATA(MEASURE_VALUE_ID,
366 INSTANCE_ID,
367 MEASURE_TYPE_CODE,
368 ORD_LEVEL_VALUE,
369 ADJ_LEVEL_VALUE,
370 TIME_LEVEL_VALUE,
371 CUS_LEVEL_VALUE,
372 GEO_LEVEL_VALUE,
373 PRD_LEVEL_VALUE,
374 REP_LEVEL_VALUE,
375 CHN_LEVEL_VALUE,
376 ORG_LEVEL_VALUE,
377 USR1_LEVEL_VALUE,
378 USR2_LEVEL_VALUE,
379 USR3_LEVEL_VALUE,
380 USR4_LEVEL_VALUE,
381 USR5_LEVEL_VALUE,
382 MEASURE1_NUMBER ,
383 MEASURE2_NUMBER ,
384 MEASURE3_NUMBER ,
385 MEASURE4_NUMBER ,
386 MEASURE5_NUMBER ,
387 MEASURE6_NUMBER ,
388 MEASURE7_NUMBER ,
389 MEASURE8_NUMBER ,
390 MEASURE9_NUMBER ,
391 MEASURE10_NUMBER ,
392 MEASURE11_NUMBER ,
393 MEASURE12_NUMBER ,
394 MEASURE13_NUMBER ,
395 MEASURE14_NUMBER ,
396 MEASURE15_NUMBER ,
397 MEASURE16_NUMBER ,
398 MEASURE17_NUMBER ,
399 MEASURE18_NUMBER ,
400 MEASURE19_NUMBER ,
401 MEASURE20_NUMBER ,
402 MEASURE21_NUMBER ,
403 MEASURE22_NUMBER ,
404 MEASURE23_NUMBER ,
405 MEASURE24_NUMBER ,
406 MEASURE25_NUMBER ,
407 MEASURE26_NUMBER ,
408 MEASURE27_NUMBER ,
409 MEASURE28_NUMBER ,
410 MEASURE29_NUMBER ,
411 MEASURE30_NUMBER ,
412 MEASURE1_CHAR ,
413 MEASURE2_CHAR ,
414 MEASURE3_CHAR ,
415 MEASURE4_CHAR ,
416 MEASURE5_CHAR ,
417 MEASURE6_CHAR ,
418 MEASURE7_CHAR ,
419 MEASURE8_CHAR ,
420 MEASURE9_CHAR ,
421 MEASURE10_CHAR ,
422 MEASURE_UOM,
423 CREATION_DATE ,
424 CREATED_BY ,
425 LAST_UPDATE_DATE ,
426 LAST_UPDATED_BY ,
427 LAST_UPDATE_LOGIN ,
428 PROGRAM_APPLICATION_ID,
429 PROGRAM_ID,
430 REQUEST_ID)
431 values(QPR_MEASURE_DATA_S.nextval,
432 g_instance_id,
433 g_meas_type,
434 r_insrecs.ORD_LEVEL_VALUE(i),
435 r_insrecs.ADJ_LEVEL_VALUE(i),
436 r_insrecs.TIME_LEVEL_VALUE(i),
437 r_insrecs.CUS_LEVEL_VALUE(i),
438 r_insrecs.GEO_LEVEL_VALUE(i),
439 r_insrecs.PRD_LEVEL_VALUE(i),
440 r_insrecs.REP_LEVEL_VALUE(i),
441 r_insrecs.CHN_LEVEL_VALUE(i),
442 r_insrecs.ORG_LEVEL_VALUE(i),
443 r_insrecs.USR1_LEVEL_VALUE(i),
444 r_insrecs.USR2_LEVEL_VALUE(i),
445 r_insrecs.USR3_LEVEL_VALUE(i),
446 r_insrecs.USR4_LEVEL_VALUE(i),
447 r_insrecs.USR5_LEVEL_VALUE(i),
448 r_insrecs.MEASURE1_NUMBER(i),
449 r_insrecs.MEASURE2_NUMBER(i),
450 r_insrecs.MEASURE3_NUMBER(i),
451 r_insrecs.MEASURE4_NUMBER(i),
452 r_insrecs.MEASURE5_NUMBER(i),
453 r_insrecs.MEASURE6_NUMBER(i),
454 r_insrecs.MEASURE7_NUMBER(i),
455 r_insrecs.MEASURE8_NUMBER(i),
456 r_insrecs.MEASURE9_NUMBER(i),
457 r_insrecs.MEASURE10_NUMBER(i),
458 r_insrecs.MEASURE11_NUMBER(i),
459 r_insrecs.MEASURE12_NUMBER(i),
460 r_insrecs.MEASURE13_NUMBER(i),
461 r_insrecs.MEASURE14_NUMBER(i),
462 r_insrecs.MEASURE15_NUMBER(i),
463 r_insrecs.MEASURE16_NUMBER(i),
464 r_insrecs.MEASURE17_NUMBER(i),
465 r_insrecs.MEASURE18_NUMBER(i),
466 r_insrecs.MEASURE19_NUMBER(i),
467 r_insrecs.MEASURE20_NUMBER(i),
468 r_insrecs.MEASURE21_NUMBER(i),
469 r_insrecs.MEASURE22_NUMBER(i),
470 r_insrecs.MEASURE23_NUMBER(i),
471 r_insrecs.MEASURE24_NUMBER(i),
472 r_insrecs.MEASURE25_NUMBER(i),
473 r_insrecs.MEASURE26_NUMBER(i),
474 r_insrecs.MEASURE27_NUMBER(i),
475 r_insrecs.MEASURE28_NUMBER(i),
476 r_insrecs.MEASURE29_NUMBER(i),
477 r_insrecs.MEASURE30_NUMBER(i),
478 r_insrecs.MEASURE1_CHAR(i),
479 r_insrecs.MEASURE2_CHAR(i),
480 r_insrecs.MEASURE3_CHAR(i),
481 r_insrecs.MEASURE4_CHAR(i),
482 r_insrecs.MEASURE5_CHAR(i),
483 r_insrecs.MEASURE6_CHAR(i),
484 r_insrecs.MEASURE7_CHAR(i),
485 r_insrecs.MEASURE8_CHAR(i),
486 r_insrecs.MEASURE9_CHAR(i),
487 r_insrecs.MEASURE10_CHAR(i),
488 r_insrecs.MEASURE_UOM(i),
489 g_sys_date,
490 g_user_id,
491 g_sys_date,
492 g_user_id,
493 g_login_id,
494 g_prg_appl_id,
495 g_prg_id,
496 g_request_id);
497 exception
498 when OTHERS then
499 log_debug('ERROR INSERTING FACT DATA...');
500 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
501 raise;
502 end insert_measdata;
503
504 procedure update_measdata is
505 begin
506 forall i in r_updrecs.MEASURE_VALUE_ID.FIRST..r_updrecs.MEASURE_VALUE_ID.LAST
507 update QPR_MEASURE_DATA set
508 MEASURE1_NUMBER = r_updrecs.MEASURE1_NUMBER(i),
509 MEASURE2_NUMBER = r_updrecs.MEASURE2_NUMBER(i),
510 MEASURE3_NUMBER = r_updrecs.MEASURE3_NUMBER(i),
511 MEASURE4_NUMBER = r_updrecs.MEASURE4_NUMBER(i),
512 MEASURE5_NUMBER = r_updrecs.MEASURE5_NUMBER(i),
513 MEASURE6_NUMBER = r_updrecs.MEASURE6_NUMBER(i),
514 MEASURE7_NUMBER = r_updrecs.MEASURE7_NUMBER(i),
515 MEASURE8_NUMBER = r_updrecs.MEASURE8_NUMBER(i),
516 MEASURE9_NUMBER = r_updrecs.MEASURE9_NUMBER(i),
517 MEASURE10_NUMBER = r_updrecs.MEASURE10_NUMBER(i),
518 MEASURE11_NUMBER = r_updrecs.MEASURE11_NUMBER(i),
519 MEASURE12_NUMBER = r_updrecs.MEASURE12_NUMBER(i),
520 MEASURE13_NUMBER = r_updrecs.MEASURE13_NUMBER(i),
521 MEASURE14_NUMBER = r_updrecs.MEASURE14_NUMBER(i),
522 MEASURE15_NUMBER = r_updrecs.MEASURE15_NUMBER(i),
523 MEASURE16_NUMBER = r_updrecs.MEASURE16_NUMBER(i),
524 MEASURE17_NUMBER = r_updrecs.MEASURE17_NUMBER(i),
525 MEASURE18_NUMBER = r_updrecs.MEASURE18_NUMBER(i),
526 MEASURE19_NUMBER = r_updrecs.MEASURE19_NUMBER(i),
527 MEASURE20_NUMBER = r_updrecs.MEASURE20_NUMBER(i),
528 MEASURE21_NUMBER = r_updrecs.MEASURE21_NUMBER(i),
529 MEASURE22_NUMBER = r_updrecs.MEASURE22_NUMBER(i),
530 MEASURE23_NUMBER = r_updrecs.MEASURE23_NUMBER(i),
531 MEASURE24_NUMBER = r_updrecs.MEASURE24_NUMBER(i),
532 MEASURE25_NUMBER = r_updrecs.MEASURE25_NUMBER(i),
533 MEASURE26_NUMBER = r_updrecs.MEASURE26_NUMBER(i),
534 MEASURE27_NUMBER = r_updrecs.MEASURE27_NUMBER(i),
535 MEASURE28_NUMBER = r_updrecs.MEASURE28_NUMBER(i),
536 MEASURE29_NUMBER = r_updrecs.MEASURE29_NUMBER(i),
537 MEASURE30_NUMBER = r_updrecs.MEASURE30_NUMBER(i),
538 MEASURE1_CHAR = r_updrecs.MEASURE1_CHAR(i),
539 MEASURE2_CHAR = r_updrecs.MEASURE2_CHAR(i),
540 MEASURE3_CHAR = r_updrecs.MEASURE3_CHAR(i),
541 MEASURE4_CHAR = r_updrecs.MEASURE4_CHAR(i),
542 MEASURE5_CHAR = r_updrecs.MEASURE5_CHAR(i),
543 MEASURE6_CHAR = r_updrecs.MEASURE6_CHAR(i),
544 MEASURE7_CHAR = r_updrecs.MEASURE7_CHAR(i),
545 MEASURE8_CHAR = r_updrecs.MEASURE8_CHAR(i),
546 MEASURE9_CHAR = r_updrecs.MEASURE9_CHAR(i),
547 MEASURE10_CHAR = r_updrecs.MEASURE10_CHAR(i),
548 MEASURE_UOM = r_updrecs.MEASURE_UOM(i),
549 LAST_UPDATE_DATE = g_sys_date,
550 LAST_UPDATED_BY = g_user_id,
551 LAST_UPDATE_LOGIN = g_login_id,
552 PROGRAM_APPLICATION_ID = g_prg_appl_id,
553 PROGRAM_ID = g_prg_id,
554 REQUEST_ID = g_request_id
555 where MEASURE_VALUE_ID = r_updrecs.MEASURE_VALUE_ID(i);
556 exception
557 when OTHERS then
558 log_debug( 'ERROR UPDATING FACT DATA... ');
559 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
560 raise;
561 end update_measdata;
562
563 procedure insert_update_meas_data(p_date_from in varchar2,
564 p_date_to in varchar2,
565 p_sql in varchar2) is
566
567 bfound boolean := false;
568 bupdate boolean := false;
569 nrows number := 1000;
570 c_srcdata SYS_REFCURSOR;
571 date_from date;
572 date_to date;
573
574 -- order of fields in the order by clause is important and must match
575 -- the order of fields that is being selected from the source. This is done
576 -- to reduce the number of iterations while cheking for the records to be
577 -- updated.
578 cursor c_meas_data(d1 date, d2 date, ins_id number, meas_tname varchar2) is
579 select ORD_LEVEL_VALUE,
580 TIME_LEVEL_VALUE, CUS_LEVEL_VALUE,
581 GEO_LEVEL_VALUE, ORG_LEVEL_VALUE,
582 REP_LEVEL_VALUE, CHN_LEVEL_VALUE,
583 PRD_LEVEL_VALUE, ADJ_LEVEL_VALUE,
584 USR1_LEVEL_VALUE, USR2_LEVEL_VALUE,
585 USR3_LEVEL_VALUE, USR4_LEVEL_VALUE,
586 USR5_LEVEL_VALUE, MEASURE_VALUE_ID
587 from QPR_MEASURE_DATA
588 where TIME_LEVEL_VALUE between d1 and d2
589 and INSTANCE_ID = ins_id
590 and MEASURE_TYPE_CODE = meas_tname
591 order by ORD_LEVEL_VALUE, ADJ_LEVEL_VALUE,
592 TIME_LEVEL_VALUE,CUS_LEVEL_VALUE,
593 GEO_LEVEL_VALUE,
594 PRD_LEVEL_VALUE, REP_LEVEL_VALUE,
595 CHN_LEVEL_VALUE, ORG_LEVEL_VALUE,
596 USR1_LEVEL_VALUE,USR2_LEVEL_VALUE,
597 USR3_LEVEL_VALUE, USR4_LEVEL_VALUE,
598 USR5_LEVEL_VALUE;
599 begin
600
601 date_from := fnd_date.canonical_to_date(p_date_from);
602 date_to := fnd_date.canonical_to_date(p_date_to);
603
604 open c_meas_data(date_from,date_to,g_instance_id, g_meas_type);
605 loop
606 fetch c_meas_data bulk collect into r_meas_data;
607 exit when c_meas_data%notfound;
608 end loop;
609 close c_meas_data;
610
611 open c_srcdata for p_sql using date_from, date_to;
612 loop
613 fetch c_srcdata bulk collect into r_srcrecs limit nrows;
614 exit when r_srcrecs.ORD_LEVEL_VALUE.count = 0;
615
616 delete_ins_rec_data();
617 delete_upd_rec_data();
618
619 l_ins_ctr := 1;
620 l_upd_ctr := 1;
621 for i in r_srcrecs.ORD_LEVEL_VALUE.first..r_srcrecs.ORD_LEVEL_VALUE.last
622 loop
623 bfound := false;
624 bupdate := false;
625 if r_meas_data.ORD_LEVEL_VALUE.count = 0 then
626 assign_val_to_ins(i);
627 l_ins_ctr := l_ins_ctr + 1;
628 else
629 for j in r_meas_data.ORD_LEVEL_VALUE.first..
630 r_meas_data.ORD_LEVEL_VALUE.last loop
631 if (r_meas_data.ORD_LEVEL_VALUE.exists(j)) then
632 case g_meas_type
633 when MEAS_TYPE_SALES then
634 if (to_char(r_srcrecs.ORD_LEVEL_VALUE(i)) =
635 r_meas_data.ORD_LEVEL_VALUE(j))
636 and (r_srcrecs.TIME_LEVEL_VALUE(i) =
637 r_meas_data.TIME_LEVEL_VALUE(j))
638 then
639 bupdate := true;
640 end if;
641 when MEAS_TYPE_ADJ then
642 if (to_char(r_srcrecs.ORD_LEVEL_VALUE(i)) =
643 r_meas_data.ORD_LEVEL_VALUE(j))
644 and (to_char(r_srcrecs.ADJ_LEVEL_VALUE(i)) =
645 r_meas_data.ADJ_LEVEL_VALUE(j))
646 and (r_srcrecs.TIME_LEVEL_VALUE(i) =
647 r_meas_data.TIME_LEVEL_VALUE(j))
648 then
649 bupdate := true;
650 end if;
651 when MEAS_TYPE_OFFADJ then
652 if (to_char(r_srcrecs.ORD_LEVEL_VALUE(i)) =
653 r_meas_data.ORD_LEVEL_VALUE(j))
654 and (to_char(r_srcrecs.ADJ_LEVEL_VALUE(i)) =
655 r_meas_data.ADJ_LEVEL_VALUE(j))
656 and (r_srcrecs.TIME_LEVEL_VALUE(i) =
657 r_meas_data.TIME_LEVEL_VALUE(j))
658 then
659 bupdate := true;
660 end if;
661 else
662 if (to_char(r_srcrecs.ORD_LEVEL_VALUE(i)) =
663 r_meas_data.ORD_LEVEL_VALUE(j))
664 and (to_char(r_srcrecs.ADJ_LEVEL_VALUE(i)) =
665 r_meas_data.ADJ_LEVEL_VALUE(j))
666 and (r_srcrecs.TIME_LEVEL_VALUE(i) =
667 r_meas_data.TIME_LEVEL_VALUE(j))
668 and (to_char(r_srcrecs.CUS_LEVEL_VALUE(i)) =
669 r_meas_data.CUS_LEVEL_VALUE(j))
670 and (to_char(r_srcrecs.GEO_LEVEL_VALUE(i)) =
671 r_meas_data.GEO_LEVEL_VALUE(j))
672 and ( to_char(r_srcrecs.PRD_LEVEL_VALUE(i)) =
673 r_meas_data.PRD_LEVEL_VALUE(j))
674 and ( to_char(r_srcrecs.REP_LEVEL_VALUE(i)) =
675 r_meas_data.REP_LEVEL_VALUE(j))
676 and ( to_char(r_srcrecs.CHN_LEVEL_VALUE(i)) =
677 r_meas_data.CHN_LEVEL_VALUE(j))
678 and ( to_char(r_srcrecs.ORG_LEVEL_VALUE(i)) =
679 r_meas_data.ORG_LEVEL_VALUE(j))
680 and (r_srcrecs.USR1_LEVEL_VALUE(i) =
681 r_meas_data.USR1_LEVEL_VALUE(j))
682 and (r_srcrecs.USR2_LEVEL_VALUE(i) =
683 r_meas_data.USR2_LEVEL_VALUE(j))
684 and (r_srcrecs.USR3_LEVEL_VALUE(i) =
685 r_meas_data.USR3_LEVEL_VALUE(j))
686 and (r_srcrecs.USR4_LEVEL_VALUE(i) =
687 r_meas_data.USR4_LEVEL_VALUE(j))
688 and (r_srcrecs.USR5_LEVEL_VALUE(i) =
689 r_meas_data.USR5_LEVEL_VALUE(j))
690 then
691 bupdate := true;
692 end if;
693 end case;
694
695 if bupdate=true then
696 r_updrecs.MEASURE_VALUE_ID(l_upd_ctr) :=
697 r_meas_data.MEASURE_VALUE_ID(j);
698 assign_upd_measure_values(i);
699 l_upd_ctr := l_upd_ctr + 1;
700 bfound := true;
701 -- Deleting the matched measure data so during the next iteration
702 -- those values can be skipped.
703 r_meas_data.ORD_LEVEL_VALUE.delete(j);
704 r_meas_data.ADJ_LEVEL_VALUE.delete(j);
705 r_meas_data.TIME_LEVEL_VALUE.delete(j);
706 r_meas_data.CUS_LEVEL_VALUE.delete(j);
707 r_meas_data.GEO_LEVEL_VALUE.delete(j);
708 r_meas_data.PRD_LEVEL_VALUE.delete(j);
709 r_meas_data.REP_LEVEL_VALUE.delete(j);
710 r_meas_data.CHN_LEVEL_VALUE.delete(j);
711 r_meas_data.ORG_LEVEL_VALUE.delete(j);
712 r_meas_data.USR1_LEVEL_VALUE.delete(j);
713 r_meas_data.USR2_LEVEL_VALUE.delete(j);
714 r_meas_data.USR3_LEVEL_VALUE.delete(j);
715 r_meas_data.USR4_LEVEL_VALUE.delete(j);
716 r_meas_data.USR5_LEVEL_VALUE.delete(j);
717 r_meas_data.MEASURE_VALUE_ID.delete(j);
718 exit;
719 end if;
720 end if;
721 end loop;
722 if bfound = false then
723 assign_val_to_ins(i);
724 l_ins_ctr := l_ins_ctr + 1;
725 end if;
726 end if;
727 end loop;
728
729 if r_insrecs.ORD_LEVEL_VALUE.count > 0 then
730 log_debug('Inserted record count: ' || r_insrecs.ORD_LEVEL_VALUE.count);
731 insert_measdata;
732 end if;
733
734 if r_updrecs.MEASURE_VALUE_ID.count >0 then
735 log_debug('Updated record count: ' || r_updrecs.MEASURE_VALUE_ID.count);
736 update_measdata;
737 end if;
738 end loop;
739 close c_srcdata;
740 exception
741 when OTHERS then
742 log_debug('ERROR INSERTING/UPDATING FACT DATA... ');
743 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
744 raise;
745 end insert_update_meas_data;
746
747 function get_deal_sql(p_src_tbl_name in varchar2, p_tgt_tbl_name in varchar2)
748 return varchar2 is
749 s_sql varchar2(20000):= '';
750 b_ret boolean;
751 s_status varchar2(100);
752 s_industry varchar2(100);
753 s_table_owner varchar2(100);
754 t_src_trg_cols char_type;
755 t_deal_tbl_def char240_type;
756 begin
757 b_ret := FND_INSTALLATION.GET_APP_INFO('QPR', s_status, s_industry,
758 s_table_owner);
759
760 select column_name bulk collect into t_deal_tbl_def
761 from all_tab_columns
762 where table_name = p_tgt_tbl_name
763 and owner = s_table_owner order by column_id;
764
765 if t_deal_tbl_def.count = 0 then
766 raise NO_TBL_DEF;
767 end if;
768
769 for i in g_src_cols.first..g_src_cols.last loop
770 t_src_trg_cols(g_trg_cols(i)) := g_src_cols(i);
771 end loop;
772 s_sql := 'select ';
773 for i in t_deal_tbl_def.first..t_deal_tbl_def.last loop
774 if t_src_trg_cols.exists(t_deal_tbl_def(i)) then
775 s_sql := s_sql || t_src_trg_cols(t_deal_tbl_def(i));
776 else
777 s_sql := s_sql || ' null ';
778 end if;
779 if i < t_deal_tbl_def.count then
780 s_sql := s_sql || ',';
781 end if;
782 end loop;
783 if s_sql is not null then
784 s_sql := s_sql || ' from ' || p_src_tbl_name;
785 end if;
786 s_sql := s_sql || ' where quote_header_id = :1';
787 log_debug(s_sql);
788 return s_sql;
789 exception
790 when NO_TBL_DEF then
791 log_debug(p_tgt_tbl_name || ' definition not found');
792 raise;
793 when OTHERS then
794 log_debug('ERROR IN FORMING DEAL SQL');
795 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
796 raise;
797 end get_deal_sql;
798
799 function insert_update_deal_hdr(
800 p_header_id in number,
801 p_sql in varchar2) return number is
802 b_insert boolean := false;
803 l_req_int_hdr_id number;
804 s_status varchar2(1);
805 c_get_hdr SYS_REFCURSOR;
806 r_hdr QPR_PN_INT_HEADERS%rowtype;
807 begin
808 log_debug('In deal header method...');
809 open c_get_hdr for p_sql using p_header_id;
810 fetch c_get_hdr into r_hdr;
811 close c_get_hdr;
812
813 if r_hdr.SOURCE_REF_HEADER_ID is null then
814 log_debug('Quote Header not found.');
815 return(0);
816 end if;
817
818 begin
819 select PN_INT_HEADER_ID, PN_REQ_HEADER_STATUS_FLAG
820 into l_req_int_hdr_id, s_status
821 from qpr_pn_int_headers
822 where source_ref_header_id = p_header_id
823 and instance_id = g_instance_id
824 and source_id = g_source_id
825 and source_ref_header_short_desc = g_quote_hdr_sd;
826 exception
827 when NO_DATA_FOUND then
828 b_insert := true;
829 end;
830
831 if b_insert= true then
832 log_debug('Inserting deal interface header...');
833 insert into qpr_pn_int_headers(
834 PN_INT_HEADER_ID,
835 INSTANCE_ID,
836 SOURCE_REF_HEADER_ID,
837 SOURCE_REF_HEADER_SHORT_DESC,
838 SOURCE_REF_HEADER_LONG_DESC,
839 SOURCE_ID, SOURCE_SHORT_DESC, SOURCE_LONG_DESC,
840 CUSTOMER_ID, CUSTOMER_SHORT_DESC,CUSTOMER_LONG_DESC,
841 INVOICE_TO_PARTY_SITE_ID,
842 INVOICE_TO_PARTY_SITE_ADDRESS,
843 SALES_REP_ID, SALES_REP_SHORT_DESC,SALES_REP_LONG_DESC,
844 SALES_REP_EMAIL_ADDRESS,
845 SALES_CHANNEL_CODE,SALES_CHANNEL_SHORT_DESC,
846 SALES_CHANNEL_LONG_DESC,
847 FREIGHT_TERMS_CODE,FREIGHT_TERMS_SHORT_DESC,
848 FREIGHT_TERMS_LONG_DESC,
849 CURRENCY_CODE, CURRENCY_SHORT_DESC,CURRENCY_LONG_DESC,
850 PN_REQ_EXPIRY_DATE, PN_REQ_HEADER_STATUS_FLAG,
851 COMMENTS, ADDITIONAL_INFORMATION, PN_REQ_HEADER_CREATION_DATE,
852 MEASURE1_NUMBER, MEASURE2_NUMBER,MEASURE3_NUMBER,
853 MEASURE4_NUMBER, MEASURE5_NUMBER,MEASURE6_NUMBER,
854 MEASURE7_NUMBER, MEASURE8_NUMBER,MEASURE9_NUMBER,
855 MEASURE10_NUMBER,
856 MEASURE1_CHAR, MEASURE2_CHAR,MEASURE3_CHAR,
857 MEASURE4_CHAR, MEASURE5_CHAR,MEASURE6_CHAR,
858 MEASURE7_CHAR, MEASURE8_CHAR,MEASURE9_CHAR,
859 MEASURE10_CHAR,
860 REQUEST_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
861 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
862 PROGRAM_APPLICATION_ID,PROGRAM_ID)
863 values(qpr_pn_int_headers_s.nextval,
864 g_instance_id,
865 r_hdr.SOURCE_REF_HEADER_ID,
866 r_hdr.SOURCE_REF_HEADER_SHORT_DESC,
867 r_hdr.SOURCE_REF_HEADER_LONG_DESC,
868 r_hdr.SOURCE_ID,
869 r_hdr.SOURCE_SHORT_DESC,
870 r_hdr.SOURCE_LONG_DESC,
871 r_hdr.CUSTOMER_ID, r_hdr.CUSTOMER_SHORT_DESC,
872 r_hdr.CUSTOMER_LONG_DESC,
873 r_hdr.INVOICE_TO_PARTY_SITE_ID,
874 r_hdr.INVOICE_TO_PARTY_SITE_ADDRESS,
875 r_hdr.SALES_REP_ID, r_hdr.SALES_REP_SHORT_DESC,
876 r_hdr.SALES_REP_LONG_DESC,
877 r_hdr.SALES_REP_EMAIL_ADDRESS,
878 r_hdr.SALES_CHANNEL_CODE,r_hdr.SALES_CHANNEL_SHORT_DESC,
879 r_hdr.SALES_CHANNEL_LONG_DESC,
880 r_hdr.FREIGHT_TERMS_CODE,r_hdr.FREIGHT_TERMS_SHORT_DESC,
881 r_hdr.FREIGHT_TERMS_LONG_DESC,
882 r_hdr.CURRENCY_CODE, r_hdr.CURRENCY_SHORT_DESC,
883 r_hdr.CURRENCY_LONG_DESC,
884 r_hdr.PN_REQ_EXPIRY_DATE, 'I', r_hdr.COMMENTS,
885 r_hdr.ADDITIONAL_INFORMATION,
886 /*g_sys_date*/ r_hdr.PN_REQ_HEADER_CREATION_DATE,
887 r_hdr.MEASURE1_NUMBER, r_hdr.MEASURE2_NUMBER,
888 r_hdr.MEASURE3_NUMBER,
889 r_hdr.MEASURE4_NUMBER, r_hdr.MEASURE5_NUMBER,
890 r_hdr.MEASURE6_NUMBER,
891 r_hdr.MEASURE7_NUMBER, r_hdr.MEASURE8_NUMBER,
892 r_hdr.MEASURE9_NUMBER,
893 r_hdr.MEASURE10_NUMBER,
894 r_hdr.MEASURE1_CHAR, r_hdr.MEASURE2_CHAR,r_hdr.MEASURE3_CHAR,
895 r_hdr.MEASURE4_CHAR, r_hdr.MEASURE5_CHAR,r_hdr.MEASURE6_CHAR,
896 r_hdr.MEASURE7_CHAR, r_hdr.MEASURE8_CHAR,r_hdr.MEASURE9_CHAR,
897 r_hdr.MEASURE10_CHAR,
898 g_request_id, g_sys_date, g_user_id, g_sys_date,
899 g_user_id, g_login_id, g_prg_appl_id, g_prg_id)
900 returning PN_INT_HEADER_ID into l_req_int_hdr_id;
901 log_debug('PN_INT_HEADER_ID:' || l_req_int_hdr_id);
902 else
903 if s_status <> 'P' then
904 log_debug('Updating deal interface header...');
905 update qpr_pn_int_headers set
906 INSTANCE_ID = g_instance_id,
907 SOURCE_REF_HEADER_SHORT_DESC = r_hdr.SOURCE_REF_HEADER_SHORT_DESC,
908 SOURCE_REF_HEADER_LONG_DESC = r_hdr.SOURCE_REF_HEADER_LONG_DESC,
909 CUSTOMER_ID = r_hdr.CUSTOMER_ID,
910 CUSTOMER_SHORT_DESC= r_hdr.CUSTOMER_SHORT_DESC,
911 CUSTOMER_LONG_DESC = r_hdr.CUSTOMER_LONG_DESC,
912 INVOICE_TO_PARTY_SITE_ID = r_hdr.INVOICE_TO_PARTY_SITE_ID,
913 INVOICE_TO_PARTY_SITE_ADDRESS = r_hdr.INVOICE_TO_PARTY_SITE_ADDRESS,
914 SALES_REP_ID = r_hdr.SALES_REP_ID,
915 SALES_REP_SHORT_DESC = r_hdr.SALES_REP_SHORT_DESC,
916 SALES_REP_LONG_DESC = r_hdr.SALES_REP_LONG_DESC,
917 SALES_REP_EMAIL_ADDRESS = r_hdr.SALES_REP_EMAIL_ADDRESS,
918 SALES_CHANNEL_CODE = r_hdr.SALES_CHANNEL_CODE,
919 SALES_CHANNEL_SHORT_DESC = r_hdr.SALES_CHANNEL_SHORT_DESC,
920 SALES_CHANNEL_LONG_DESC = r_hdr.SALES_CHANNEL_LONG_DESC,
921 FREIGHT_TERMS_CODE = r_hdr.FREIGHT_TERMS_CODE,
922 FREIGHT_TERMS_SHORT_DESC = r_hdr.FREIGHT_TERMS_SHORT_DESC,
923 FREIGHT_TERMS_LONG_DESC = r_hdr.FREIGHT_TERMS_LONG_DESC,
924 CURRENCY_CODE = r_hdr.CURRENCY_CODE,
925 CURRENCY_SHORT_DESC = r_hdr.CURRENCY_SHORT_DESC,
926 CURRENCY_LONG_DESC = r_hdr.CURRENCY_LONG_DESC,
927 PN_REQ_EXPIRY_DATE = r_hdr.PN_REQ_EXPIRY_DATE,
928 COMMENTS = r_hdr.COMMENTS,
929 ADDITIONAL_INFORMATION = r_hdr.ADDITIONAL_INFORMATION,
930 MEASURE1_NUMBER = r_hdr.MEASURE1_NUMBER,
931 MEASURE2_NUMBER = r_hdr.MEASURE2_NUMBER,
932 MEASURE3_NUMBER = r_hdr.MEASURE3_NUMBER,
933 MEASURE4_NUMBER = r_hdr.MEASURE4_NUMBER,
934 MEASURE5_NUMBER = r_hdr.MEASURE5_NUMBER,
935 MEASURE6_NUMBER = r_hdr.MEASURE6_NUMBER,
936 MEASURE7_NUMBER = r_hdr.MEASURE7_NUMBER,
937 MEASURE8_NUMBER = r_hdr.MEASURE8_NUMBER,
938 MEASURE9_NUMBER = r_hdr.MEASURE9_NUMBER,
939 MEASURE10_NUMBER = r_hdr.MEASURE10_NUMBER,
940 MEASURE1_CHAR = r_hdr.MEASURE1_CHAR,
941 MEASURE2_CHAR = r_hdr.MEASURE2_CHAR,
942 MEASURE3_CHAR = r_hdr.MEASURE3_CHAR,
943 MEASURE4_CHAR = r_hdr.MEASURE4_CHAR,
944 MEASURE5_CHAR = r_hdr.MEASURE5_CHAR,
945 MEASURE6_CHAR = r_hdr.MEASURE6_CHAR,
946 MEASURE7_CHAR = r_hdr.MEASURE7_CHAR,
947 MEASURE8_CHAR = r_hdr.MEASURE8_CHAR,
948 MEASURE9_CHAR = r_hdr.MEASURE9_CHAR,
949 MEASURE10_CHAR = r_hdr.MEASURE10_CHAR,
950 LAST_UPDATE_DATE = g_sys_date,
951 LAST_UPDATED_BY = g_user_id,
952 LAST_UPDATE_LOGIN = g_login_id,
953 PROGRAM_APPLICATION_ID = g_prg_appl_id,
954 PROGRAM_ID = g_prg_id,
955 REQUEST_ID = g_request_id
956 where PN_INT_HEADER_ID = l_req_int_hdr_id;
957 else
958 log_debug('Status of the header does not permit update');
959 end if;
960 end if;
961 return(l_req_int_hdr_id);
962 exception
963 when OTHERS then
964 log_debug('ERROR IN INSERTING/UPDATING DEAL INTERFACE HEADER');
965 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
966 raise;
967 end insert_update_deal_hdr;
968
969 procedure assign_ins_deal_lines(p_qtn_ictr in number, p_src_ctr in number) is
970 l_line_no varchar2(240);
971 l_sql varchar2(2000);
972 begin
973 g_r_ins_deal.SOURCE_REF_HDR_ID(p_qtn_ictr) :=
974 g_t_src_lines(p_src_ctr).SOURCE_REF_HDR_ID;
975 g_r_ins_deal.SOURCE_REF_LINE_ID(p_qtn_ictr) :=
976 g_t_src_lines(p_src_ctr).SOURCE_REF_LINE_ID;
977 if g_meas_type = ASO_MEAS_TYPE_DEALINT then
978 begin
979 l_sql := 'select QUOTE_LINE_NUMBER from qpr_sr_quote_line_num_v';
980 l_sql := l_sql || qpr_sr_util.get_dblink(g_instance_id);
981 l_sql := l_sql || ' where quote_header_id = :1 ';
982 l_sql := l_sql || ' and quote_line_id = :2 and source_id = :3';
983 execute immediate l_sql into l_line_no using
984 g_t_src_lines(p_src_ctr).SOURCE_REF_HDR_ID,
985 g_t_src_lines(p_src_ctr).SOURCE_REF_LINE_ID,
986 g_source_id;
987 exception
988 when no_data_found then
989 l_line_no := g_t_src_lines(p_src_ctr).SOURCE_REQUEST_LINE_NUMBER;
990 end;
991 g_r_ins_deal.SOURCE_REQ_LINE_NO(p_qtn_ictr) := l_line_no;
992 else
993 g_r_ins_deal.SOURCE_REQ_LINE_NO(p_qtn_ictr) :=
994 g_t_src_lines(p_src_ctr).SOURCE_REQUEST_LINE_NUMBER;
995 end if;
996 g_r_ins_deal.SOURCE_ID(p_qtn_ictr) := g_t_src_lines(p_src_ctr).SOURCE_ID;
997 g_r_ins_deal.ORG_ID(p_qtn_ictr) := g_t_src_lines(p_src_ctr).ORG_ID;
998 g_r_ins_deal.ORG_SHORT_DESC(p_qtn_ictr) :=
999 g_t_src_lines(p_src_ctr).ORG_SHORT_DESC;
1000 g_r_ins_deal.ORG_LONG_DESC(p_qtn_ictr) :=
1001 g_t_src_lines(p_src_ctr).ORG_LONG_DESC;
1002 g_r_ins_deal.INVENTORY_ITEM_ID(p_qtn_ictr) :=
1003 g_t_src_lines(p_src_ctr).INVENTORY_ITEM_ID;
1004 g_r_ins_deal.INVENTORY_ITEM_SHORT_DESC(p_qtn_ictr) :=
1005 g_t_src_lines(p_src_ctr).INVENTORY_ITEM_SHORT_DESC;
1006 g_r_ins_deal.INVENTORY_ITEM_LONG_DESC(p_qtn_ictr) :=
1007 g_t_src_lines(p_src_ctr).INVENTORY_ITEM_LONG_DESC;
1008 g_r_ins_deal.ITEM_TYPE_CODE(p_qtn_ictr) :=
1009 g_t_src_lines(p_src_ctr).ITEM_TYPE_CODE;
1010 g_r_ins_deal.TOP_MDL_SRC_LINE_ID(p_qtn_ictr) :=
1011 g_t_src_lines(p_src_ctr).TOP_MDL_SRC_LINE_ID;
1012 g_r_ins_deal.PAYMENT_TERM_ID(p_qtn_ictr) :=
1013 g_t_src_lines(p_src_ctr).PAYMENT_TERM_ID;
1014 g_r_ins_deal.PAYMENT_TERM_SHORT_DESC(p_qtn_ictr) :=
1015 g_t_src_lines(p_src_ctr).PAYMENT_TERM_SHORT_DESC;
1016 g_r_ins_deal.PAYMENT_TERM_LONG_DESC(p_qtn_ictr) :=
1017 g_t_src_lines(p_src_ctr).PAYMENT_TERM_LONG_DESC;
1018 g_r_ins_deal.UOM_CODE(p_qtn_ictr) := g_t_src_lines(p_src_ctr).UOM_CODE;
1019 g_r_ins_deal.UOM_SHORT_DESC(p_qtn_ictr) :=
1020 g_t_src_lines(p_src_ctr).UOM_SHORT_DESC;
1021 g_r_ins_deal.CURRENCY_CODE(p_qtn_ictr) :=
1022 g_t_src_lines(p_src_ctr).CURRENCY_CODE;
1023 g_r_ins_deal.CURRENCY_SHORT_DESC(p_qtn_ictr) :=
1024 g_t_src_lines(p_src_ctr).CURRENCY_SHORT_DESC;
1025 g_r_ins_deal.ORDERED_QTY(p_qtn_ictr) := g_t_src_lines(p_src_ctr).ORDERED_QTY;
1026 g_r_ins_deal.LIST_PRICE(p_qtn_ictr) := g_t_src_lines(p_src_ctr).LIST_PRICE;
1027 g_r_ins_deal.PROPOSED_PRICE(p_qtn_ictr) :=
1028 g_t_src_lines(p_src_ctr).PROPOSED_PRICE;
1029 g_r_ins_deal.REVISED_OQ(p_qtn_ictr) :=
1030 g_t_src_lines(p_src_ctr).REVISED_OQ;
1031 g_r_ins_deal.COMPETITOR_NAME(p_qtn_ictr) :=
1032 g_t_src_lines(p_src_ctr).COMPETITOR_NAME;
1033 g_r_ins_deal.COMPETITOR_PRICE(p_qtn_ictr) :=
1034 g_t_src_lines(p_src_ctr).COMPETITOR_PRICE;
1035 g_r_ins_deal.COMMENTS(p_qtn_ictr) := g_t_src_lines(p_src_ctr).COMMENTS;
1036 g_r_ins_deal.ADDITIONAL_INFO(p_qtn_ictr) :=
1037 g_t_src_lines(p_src_ctr).ADDITIONAL_INFORMATION;
1038 g_r_ins_deal.SHIP_METHOD_CODE(p_qtn_ictr) :=
1039 g_t_src_lines(p_src_ctr).SHIP_METHOD_CODE;
1040 g_r_ins_deal.SHIP_METHOD_SHORT_DESC(p_qtn_ictr) :=
1041 g_t_src_lines(p_src_ctr).SHIP_METHOD_SHORT_DESC;
1042 g_r_ins_deal.SHIP_METHOD_LONG_DESC(p_qtn_ictr) :=
1043 g_t_src_lines(p_src_ctr).SHIP_METHOD_LONG_DESC;
1044 g_r_ins_deal.FREIGHT_CHARGES(p_qtn_ictr) :=
1045 g_t_src_lines(p_src_ctr).FREIGHT_CHARGES;
1046 g_r_ins_deal.GEOGRAPHY_ID(p_qtn_ictr) :=
1047 g_t_src_lines(p_src_ctr).GEOGRAPHY_ID;
1048 g_r_ins_deal.GEOGRAPHY_SHORT_DESC(p_qtn_ictr) :=
1049 g_t_src_lines(p_src_ctr).GEOGRAPHY_SHORT_DESC;
1050 g_r_ins_deal.GEOGRAPHY_LONG_DESC(p_qtn_ictr) :=
1051 g_t_src_lines(p_src_ctr).GEOGRAPHY_LONG_DESC;
1052 g_r_ins_deal.MEASURE1_NUMBER(p_qtn_ictr) :=
1053 g_t_src_lines(p_src_ctr).MEASURE1_NUMBER;
1054 g_r_ins_deal.MEASURE2_NUMBER(p_qtn_ictr) :=
1055 g_t_src_lines(p_src_ctr).MEASURE2_NUMBER;
1056 g_r_ins_deal.MEASURE3_NUMBER(p_qtn_ictr) :=
1057 g_t_src_lines(p_src_ctr).MEASURE3_NUMBER;
1058 g_r_ins_deal.MEASURE4_NUMBER(p_qtn_ictr) :=
1059 g_t_src_lines(p_src_ctr).MEASURE4_NUMBER;
1060 g_r_ins_deal.MEASURE5_NUMBER(p_qtn_ictr) :=
1061 g_t_src_lines(p_src_ctr).MEASURE5_NUMBER;
1062 g_r_ins_deal.MEASURE6_NUMBER(p_qtn_ictr) :=
1063 g_t_src_lines(p_src_ctr).MEASURE6_NUMBER;
1064 g_r_ins_deal.MEASURE7_NUMBER(p_qtn_ictr) :=
1065 g_t_src_lines(p_src_ctr).MEASURE7_NUMBER;
1066 g_r_ins_deal.MEASURE8_NUMBER(p_qtn_ictr) :=
1067 g_t_src_lines(p_src_ctr).MEASURE8_NUMBER;
1068 g_r_ins_deal.MEASURE9_NUMBER(p_qtn_ictr) :=
1069 g_t_src_lines(p_src_ctr).MEASURE9_NUMBER;
1070 g_r_ins_deal.MEASURE10_NUMBER(p_qtn_ictr) :=
1071 g_t_src_lines(p_src_ctr).MEASURE10_NUMBER;
1072 g_r_ins_deal.MEASURE1_CHAR(p_qtn_ictr) :=
1073 g_t_src_lines(p_src_ctr).MEASURE1_CHAR;
1074 g_r_ins_deal.MEASURE2_CHAR(p_qtn_ictr) :=
1075 g_t_src_lines(p_src_ctr).MEASURE2_CHAR;
1076 g_r_ins_deal.MEASURE3_CHAR(p_qtn_ictr) :=
1077 g_t_src_lines(p_src_ctr).MEASURE3_CHAR;
1078 g_r_ins_deal.MEASURE4_CHAR(p_qtn_ictr) :=
1079 g_t_src_lines(p_src_ctr).MEASURE4_CHAR;
1080 g_r_ins_deal.MEASURE5_CHAR(p_qtn_ictr) :=
1081 g_t_src_lines(p_src_ctr).MEASURE5_CHAR;
1082 g_r_ins_deal.MEASURE6_CHAR(p_qtn_ictr) :=
1083 g_t_src_lines(p_src_ctr).MEASURE6_CHAR;
1084 g_r_ins_deal.MEASURE7_CHAR(p_qtn_ictr) :=
1085 g_t_src_lines(p_src_ctr).MEASURE7_CHAR;
1086 g_r_ins_deal.MEASURE8_CHAR(p_qtn_ictr) :=
1087 g_t_src_lines(p_src_ctr).MEASURE8_CHAR;
1088 g_r_ins_deal.MEASURE9_CHAR(p_qtn_ictr) :=
1089 g_t_src_lines(p_src_ctr).MEASURE9_CHAR;
1090 g_r_ins_deal.MEASURE10_CHAR(p_qtn_ictr) :=
1091 g_t_src_lines(p_src_ctr).MEASURE10_CHAR;
1092
1093 exception
1094 when OTHERS then
1095 log_debug('ERROR ASSIGNING VALUES TO INSERT IN INTERFACE LINE RECORD');
1096 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1097 raise;
1098 end assign_ins_deal_lines;
1099
1100 procedure assign_upd_deal_lines(p_qtn_uctr in number, p_src_ctr in number,
1101 p_int_line_id in number) is
1102 l_line_no varchar2(240);
1103 begin
1104 g_r_upd_deal.PN_REQ_INTERFACE_LINE_ID(p_qtn_uctr) := p_int_line_id;
1105 g_r_upd_deal.SOURCE_REF_HDR_ID(p_qtn_uctr) :=
1106 g_t_src_lines(p_src_ctr).SOURCE_REF_HDR_ID;
1107 g_r_upd_deal.SOURCE_REF_LINE_ID(p_qtn_uctr) :=
1108 g_t_src_lines(p_src_ctr).SOURCE_REF_LINE_ID;
1109 if g_meas_type = ASO_MEAS_TYPE_DEALINT then
1110 begin
1111 select QUOTE_LINE_NUMBER into l_line_no
1112 from qpr_sr_quote_line_num_v
1113 where quote_header_id = g_t_src_lines(p_src_ctr).SOURCE_REF_HDR_ID
1114 and quote_line_id = g_t_src_lines(p_src_ctr).SOURCE_REF_LINE_ID
1115 and source_id = g_source_id;
1116 exception
1117 when no_data_found then
1118 l_line_no := g_t_src_lines(p_src_ctr).SOURCE_REQUEST_LINE_NUMBER;
1119 end;
1120 g_r_upd_deal.SOURCE_REQ_LINE_NO(p_qtn_uctr) := l_line_no;
1121 else
1122 g_r_upd_deal.SOURCE_REQ_LINE_NO(p_qtn_uctr) :=
1123 g_t_src_lines(p_src_ctr).SOURCE_REQUEST_LINE_NUMBER;
1124 end if;
1125 g_r_upd_deal.SOURCE_ID(p_qtn_uctr) := g_t_src_lines(p_src_ctr).SOURCE_ID;
1126 g_r_upd_deal.ORG_ID(p_qtn_uctr) := g_t_src_lines(p_src_ctr).ORG_ID;
1127 g_r_upd_deal.ORG_SHORT_DESC(p_qtn_uctr) :=
1128 g_t_src_lines(p_src_ctr).ORG_SHORT_DESC;
1129 g_r_upd_deal.ORG_LONG_DESC(p_qtn_uctr) :=
1130 g_t_src_lines(p_src_ctr).ORG_LONG_DESC;
1131 g_r_upd_deal.INVENTORY_ITEM_ID(p_qtn_uctr) :=
1132 g_t_src_lines(p_src_ctr).INVENTORY_ITEM_ID;
1133 g_r_upd_deal.INVENTORY_ITEM_SHORT_DESC(p_qtn_uctr) :=
1134 g_t_src_lines(p_src_ctr).INVENTORY_ITEM_SHORT_DESC;
1135 g_r_upd_deal.INVENTORY_ITEM_LONG_DESC(p_qtn_uctr) :=
1136 g_t_src_lines(p_src_ctr).INVENTORY_ITEM_LONG_DESC;
1137 g_r_upd_deal.ITEM_TYPE_CODE(p_qtn_uctr) :=
1138 g_t_src_lines(p_src_ctr).ITEM_TYPE_CODE;
1139 g_r_upd_deal.TOP_MDL_SRC_LINE_ID(p_qtn_uctr) :=
1140 g_t_src_lines(p_src_ctr).TOP_MDL_SRC_LINE_ID;
1141 g_r_upd_deal.PAYMENT_TERM_ID(p_qtn_uctr) :=
1142 g_t_src_lines(p_src_ctr).PAYMENT_TERM_ID;
1143 g_r_upd_deal.PAYMENT_TERM_SHORT_DESC(p_qtn_uctr) :=
1144 g_t_src_lines(p_src_ctr).PAYMENT_TERM_SHORT_DESC;
1145 g_r_upd_deal.PAYMENT_TERM_LONG_DESC(p_qtn_uctr) :=
1146 g_t_src_lines(p_src_ctr).PAYMENT_TERM_LONG_DESC;
1147 g_r_upd_deal.UOM_CODE(p_qtn_uctr) := g_t_src_lines(p_src_ctr).UOM_CODE;
1148 g_r_upd_deal.UOM_SHORT_DESC(p_qtn_uctr) :=
1149 g_t_src_lines(p_src_ctr).UOM_SHORT_DESC;
1150 g_r_upd_deal.CURRENCY_CODE(p_qtn_uctr) :=
1151 g_t_src_lines(p_src_ctr).CURRENCY_CODE;
1152 g_r_upd_deal.CURRENCY_SHORT_DESC(p_qtn_uctr) :=
1153 g_t_src_lines(p_src_ctr).CURRENCY_SHORT_DESC;
1154 g_r_upd_deal.ORDERED_QTY(p_qtn_uctr) := g_t_src_lines(p_src_ctr).ORDERED_QTY;
1155 g_r_upd_deal.LIST_PRICE(p_qtn_uctr) := g_t_src_lines(p_src_ctr).LIST_PRICE;
1156 g_r_upd_deal.PROPOSED_PRICE(p_qtn_uctr) :=
1157 g_t_src_lines(p_src_ctr).PROPOSED_PRICE;
1158 g_r_upd_deal.REVISED_OQ(p_qtn_uctr) :=
1159 g_t_src_lines(p_src_ctr).REVISED_OQ;
1160 g_r_upd_deal.COMPETITOR_NAME(p_qtn_uctr) :=
1161 g_t_src_lines(p_src_ctr).COMPETITOR_NAME;
1162 g_r_upd_deal.COMPETITOR_PRICE(p_qtn_uctr) :=
1163 g_t_src_lines(p_src_ctr).COMPETITOR_PRICE;
1164 g_r_upd_deal.COMMENTS(p_qtn_uctr) := g_t_src_lines(p_src_ctr).COMMENTS;
1165 g_r_upd_deal.ADDITIONAL_INFO(p_qtn_uctr) :=
1166 g_t_src_lines(p_src_ctr).ADDITIONAL_INFORMATION;
1167 g_r_upd_deal.SHIP_METHOD_CODE(p_qtn_uctr) :=
1168 g_t_src_lines(p_src_ctr).SHIP_METHOD_CODE;
1169 g_r_upd_deal.SHIP_METHOD_SHORT_DESC(p_qtn_uctr) :=
1170 g_t_src_lines(p_src_ctr).SHIP_METHOD_SHORT_DESC;
1171 g_r_upd_deal.SHIP_METHOD_LONG_DESC(p_qtn_uctr) :=
1172 g_t_src_lines(p_src_ctr).SHIP_METHOD_LONG_DESC;
1173 g_r_upd_deal.FREIGHT_CHARGES(p_qtn_uctr) :=
1174 g_t_src_lines(p_src_ctr).FREIGHT_CHARGES;
1175 g_r_upd_deal.GEOGRAPHY_ID(p_qtn_uctr) :=
1176 g_t_src_lines(p_src_ctr).GEOGRAPHY_ID;
1177 g_r_upd_deal.GEOGRAPHY_SHORT_DESC(p_qtn_uctr) :=
1178 g_t_src_lines(p_src_ctr).GEOGRAPHY_SHORT_DESC;
1179 g_r_upd_deal.GEOGRAPHY_LONG_DESC(p_qtn_uctr) :=
1180 g_t_src_lines(p_src_ctr).GEOGRAPHY_LONG_DESC;
1181 g_r_upd_deal.MEASURE1_NUMBER(p_qtn_uctr) :=
1182 g_t_src_lines(p_src_ctr).MEASURE1_NUMBER;
1183 g_r_upd_deal.MEASURE2_NUMBER(p_qtn_uctr) :=
1184 g_t_src_lines(p_src_ctr).MEASURE2_NUMBER;
1185 g_r_upd_deal.MEASURE3_NUMBER(p_qtn_uctr) :=
1186 g_t_src_lines(p_src_ctr).MEASURE3_NUMBER;
1187 g_r_upd_deal.MEASURE4_NUMBER(p_qtn_uctr) :=
1188 g_t_src_lines(p_src_ctr).MEASURE4_NUMBER;
1189 g_r_upd_deal.MEASURE5_NUMBER(p_qtn_uctr) :=
1190 g_t_src_lines(p_src_ctr).MEASURE5_NUMBER;
1191 g_r_upd_deal.MEASURE6_NUMBER(p_qtn_uctr) :=
1192 g_t_src_lines(p_src_ctr).MEASURE6_NUMBER;
1193 g_r_upd_deal.MEASURE7_NUMBER(p_qtn_uctr) :=
1194 g_t_src_lines(p_src_ctr).MEASURE7_NUMBER;
1195 g_r_upd_deal.MEASURE8_NUMBER(p_qtn_uctr) :=
1196 g_t_src_lines(p_src_ctr).MEASURE8_NUMBER;
1197 g_r_upd_deal.MEASURE9_NUMBER(p_qtn_uctr) :=
1198 g_t_src_lines(p_src_ctr).MEASURE9_NUMBER;
1199 g_r_upd_deal.MEASURE10_NUMBER(p_qtn_uctr) :=
1200 g_t_src_lines(p_src_ctr).MEASURE10_NUMBER;
1201 g_r_upd_deal.MEASURE1_CHAR(p_qtn_uctr) :=
1202 g_t_src_lines(p_src_ctr).MEASURE1_CHAR;
1203 g_r_upd_deal.MEASURE2_CHAR(p_qtn_uctr) :=
1204 g_t_src_lines(p_src_ctr).MEASURE2_CHAR;
1205 g_r_upd_deal.MEASURE3_CHAR(p_qtn_uctr) :=
1206 g_t_src_lines(p_src_ctr).MEASURE3_CHAR;
1207 g_r_upd_deal.MEASURE4_CHAR(p_qtn_uctr) :=
1208 g_t_src_lines(p_src_ctr).MEASURE4_CHAR;
1209 g_r_upd_deal.MEASURE5_CHAR(p_qtn_uctr) :=
1210 g_t_src_lines(p_src_ctr).MEASURE5_CHAR;
1211 g_r_upd_deal.MEASURE6_CHAR(p_qtn_uctr) :=
1212 g_t_src_lines(p_src_ctr).MEASURE6_CHAR;
1213 g_r_upd_deal.MEASURE7_CHAR(p_qtn_uctr) :=
1214 g_t_src_lines(p_src_ctr).MEASURE7_CHAR;
1215 g_r_upd_deal.MEASURE8_CHAR(p_qtn_uctr) :=
1216 g_t_src_lines(p_src_ctr).MEASURE8_CHAR;
1217 g_r_upd_deal.MEASURE9_CHAR(p_qtn_uctr) :=
1218 g_t_src_lines(p_src_ctr).MEASURE9_CHAR;
1219 g_r_upd_deal.MEASURE10_CHAR(p_qtn_uctr) :=
1220 g_t_src_lines(p_src_ctr).MEASURE10_CHAR;
1221
1222 exception
1223 when OTHERS then
1224 log_debug('ERROR ASSIGNING VALUES TO UPDATE INTERFACE LINE RECORD');
1225 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1226 raise;
1227 end assign_upd_deal_lines;
1228
1229 procedure del_ins_deal_lines is
1230 begin
1231 g_r_ins_deal.SOURCE_REF_HDR_ID.delete;
1232 g_r_ins_deal.SOURCE_REF_LINE_ID.delete;
1233 g_r_ins_deal.SOURCE_REQ_LINE_NO.delete;
1234 g_r_ins_deal.SOURCE_ID.delete;
1235 g_r_ins_deal.ORG_ID.delete;
1236 g_r_ins_deal.ORG_SHORT_DESC.delete;
1237 g_r_ins_deal.ORG_LONG_DESC.delete;
1238 g_r_ins_deal.INVENTORY_ITEM_ID.delete;
1239 g_r_ins_deal.INVENTORY_ITEM_SHORT_DESC.delete;
1240 g_r_ins_deal.INVENTORY_ITEM_LONG_DESC.delete;
1241 g_r_ins_deal.ITEM_TYPE_CODE.delete;
1242 g_r_ins_deal.TOP_MDL_SRC_LINE_ID.delete;
1243 g_r_ins_deal.PAYMENT_TERM_ID.delete;
1244 g_r_ins_deal.PAYMENT_TERM_SHORT_DESC.delete;
1245 g_r_ins_deal.PAYMENT_TERM_LONG_DESC.delete;
1246 g_r_ins_deal.UOM_CODE.delete;
1247 g_r_ins_deal.UOM_SHORT_DESC.delete;
1248 g_r_ins_deal.CURRENCY_CODE.delete;
1249 g_r_ins_deal.CURRENCY_SHORT_DESC.delete;
1250 g_r_ins_deal.ORDERED_QTY.delete;
1251 g_r_ins_deal.LIST_PRICE.delete;
1252 g_r_ins_deal.PROPOSED_PRICE.delete;
1253 g_r_ins_deal.REVISED_OQ.delete;
1254 g_r_ins_deal.COMPETITOR_NAME.delete;
1255 g_r_ins_deal.COMPETITOR_PRICE.delete;
1256 g_r_ins_deal.COMMENTS.delete;
1257 g_r_ins_deal.ADDITIONAL_INFO.delete;
1258 g_r_ins_deal.SHIP_METHOD_CODE.delete;
1259 g_r_ins_deal.SHIP_METHOD_SHORT_DESC.delete;
1260 g_r_ins_deal.SHIP_METHOD_LONG_DESC.delete;
1261 g_r_ins_deal.FREIGHT_CHARGES.delete;
1262 g_r_ins_deal.GEOGRAPHY_ID.delete;
1263 g_r_ins_deal.GEOGRAPHY_SHORT_DESC.delete;
1264 g_r_ins_deal.GEOGRAPHY_LONG_DESC.delete;
1265 g_r_ins_deal.MEASURE1_NUMBER.delete;
1266 g_r_ins_deal.MEASURE2_NUMBER.delete;
1267 g_r_ins_deal.MEASURE3_NUMBER.delete;
1268 g_r_ins_deal.MEASURE4_NUMBER.delete;
1269 g_r_ins_deal.MEASURE5_NUMBER.delete;
1270 g_r_ins_deal.MEASURE6_NUMBER.delete;
1271 g_r_ins_deal.MEASURE7_NUMBER.delete;
1272 g_r_ins_deal.MEASURE8_NUMBER.delete;
1273 g_r_ins_deal.MEASURE9_NUMBER.delete;
1274 g_r_ins_deal.MEASURE10_NUMBER.delete;
1275 g_r_ins_deal.MEASURE1_CHAR.delete;
1276 g_r_ins_deal.MEASURE2_CHAR.delete;
1277 g_r_ins_deal.MEASURE3_CHAR.delete;
1278 g_r_ins_deal.MEASURE4_CHAR.delete;
1279 g_r_ins_deal.MEASURE5_CHAR.delete;
1280 g_r_ins_deal.MEASURE6_CHAR.delete;
1281 g_r_ins_deal.MEASURE7_CHAR.delete;
1282 g_r_ins_deal.MEASURE8_CHAR.delete;
1283 g_r_ins_deal.MEASURE9_CHAR.delete;
1284 g_r_ins_deal.MEASURE10_CHAR.delete;
1285
1286 exception
1287 when OTHERS then
1288 log_debug('ERROR IN CLEARING INTERFACE LINE RECORD');
1289 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1290 raise;
1291 end del_ins_deal_lines;
1292
1293 procedure del_upd_deal_lines is
1294 begin
1295 g_r_upd_deal.PN_REQ_INTERFACE_LINE_ID.delete;
1296 g_r_upd_deal.SOURCE_REF_HDR_ID.delete;
1297 g_r_upd_deal.SOURCE_REF_LINE_ID.delete;
1298 g_r_upd_deal.SOURCE_REQ_LINE_NO.delete;
1299 g_r_upd_deal.SOURCE_ID.delete;
1300 g_r_upd_deal.ORG_ID.delete;
1301 g_r_upd_deal.ORG_SHORT_DESC.delete;
1302 g_r_upd_deal.ORG_LONG_DESC.delete;
1303 g_r_upd_deal.INVENTORY_ITEM_ID.delete;
1304 g_r_upd_deal.INVENTORY_ITEM_SHORT_DESC.delete;
1305 g_r_upd_deal.INVENTORY_ITEM_LONG_DESC.delete;
1306 g_r_upd_deal.ITEM_TYPE_CODE.delete;
1307 g_r_upd_deal.TOP_MDL_SRC_LINE_ID.delete;
1308 g_r_upd_deal.PAYMENT_TERM_ID.delete;
1309 g_r_upd_deal.PAYMENT_TERM_SHORT_DESC.delete;
1310 g_r_upd_deal.PAYMENT_TERM_LONG_DESC.delete;
1311 g_r_upd_deal.UOM_CODE.delete;
1312 g_r_upd_deal.UOM_SHORT_DESC.delete;
1313 g_r_upd_deal.CURRENCY_CODE.delete;
1314 g_r_upd_deal.CURRENCY_SHORT_DESC.delete;
1315 g_r_upd_deal.ORDERED_QTY.delete;
1316 g_r_upd_deal.LIST_PRICE.delete;
1317 g_r_upd_deal.PROPOSED_PRICE.delete;
1318 g_r_upd_deal.REVISED_OQ.delete;
1319 g_r_upd_deal.COMPETITOR_NAME.delete;
1320 g_r_upd_deal.COMPETITOR_PRICE.delete;
1321 g_r_upd_deal.COMMENTS.delete;
1322 g_r_upd_deal.ADDITIONAL_INFO.delete;
1323 g_r_upd_deal.SHIP_METHOD_CODE.delete;
1324 g_r_upd_deal.SHIP_METHOD_SHORT_DESC.delete;
1325 g_r_upd_deal.SHIP_METHOD_LONG_DESC.delete;
1326 g_r_upd_deal.FREIGHT_CHARGES.delete;
1327 g_r_upd_deal.GEOGRAPHY_ID.delete;
1328 g_r_upd_deal.GEOGRAPHY_SHORT_DESC.delete;
1329 g_r_upd_deal.GEOGRAPHY_LONG_DESC.delete;
1330 g_r_upd_deal.MEASURE1_NUMBER.delete;
1331 g_r_upd_deal.MEASURE2_NUMBER.delete;
1332 g_r_upd_deal.MEASURE3_NUMBER.delete;
1333 g_r_upd_deal.MEASURE4_NUMBER.delete;
1334 g_r_upd_deal.MEASURE5_NUMBER.delete;
1335 g_r_upd_deal.MEASURE6_NUMBER.delete;
1336 g_r_upd_deal.MEASURE7_NUMBER.delete;
1337 g_r_upd_deal.MEASURE8_NUMBER.delete;
1338 g_r_upd_deal.MEASURE9_NUMBER.delete;
1339 g_r_upd_deal.MEASURE10_NUMBER.delete;
1340 g_r_upd_deal.MEASURE1_CHAR.delete;
1341 g_r_upd_deal.MEASURE2_CHAR.delete;
1342 g_r_upd_deal.MEASURE3_CHAR.delete;
1343 g_r_upd_deal.MEASURE4_CHAR.delete;
1344 g_r_upd_deal.MEASURE5_CHAR.delete;
1345 g_r_upd_deal.MEASURE6_CHAR.delete;
1346 g_r_upd_deal.MEASURE7_CHAR.delete;
1347 g_r_upd_deal.MEASURE8_CHAR.delete;
1348 g_r_upd_deal.MEASURE9_CHAR.delete;
1349 g_r_upd_deal.MEASURE10_CHAR.delete;
1350
1351 exception
1352 when OTHERS then
1353 log_debug('ERROR IN CLEARING INTERFACE LINE RECORD');
1354 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1355 raise;
1356 end del_upd_deal_lines;
1357
1358 procedure insert_deal_lines(
1359 p_header_id in number
1360 ) is
1361 begin
1362 log_debug('Inserting deal interface lines ....');
1363 forall i in g_r_ins_deal.SOURCE_REF_HDR_ID.first..
1364 g_r_ins_deal.SOURCE_REF_HDR_ID.last
1365 insert into qpr_pn_int_lines(PN_INT_LINE_ID,
1366 -- PN_INT_HEADER_ID,
1367 SOURCE_REF_HDR_ID,
1368 SOURCE_REF_LINE_ID,
1369 SOURCE_REQUEST_LINE_NUMBER,
1370 SOURCE_ID,
1371 ORG_ID,
1372 ORG_SHORT_DESC,
1373 ORG_LONG_DESC,
1374 INVENTORY_ITEM_ID,
1375 INVENTORY_ITEM_SHORT_DESC,
1376 INVENTORY_ITEM_LONG_DESC,
1377 ITEM_TYPE_CODE,
1378 TOP_MDL_SRC_LINE_ID,
1379 PAYMENT_TERM_ID,
1380 PAYMENT_TERM_SHORT_DESC,
1381 PAYMENT_TERM_LONG_DESC,
1382 UOM_CODE,
1383 UOM_SHORT_DESC,
1384 CURRENCY_CODE,
1385 CURRENCY_SHORT_DESC,
1386 ORDERED_QTY,
1387 LIST_PRICE,
1388 PROPOSED_PRICE,
1389 REVISED_OQ,
1390 PN_REQ_LINE_STATUS_FLAG,
1391 COMPETITOR_NAME,
1392 COMPETITOR_PRICE,
1393 COMMENTS,
1394 ADDITIONAL_INFORMATION,
1395 SHIP_METHOD_CODE,
1396 SHIP_METHOD_SHORT_DESC,
1397 SHIP_METHOD_LONG_DESC,
1398 FREIGHT_CHARGES,
1399 GEOGRAPHY_ID,
1400 GEOGRAPHY_SHORT_DESC,
1401 GEOGRAPHY_LONG_DESC,
1402 MEASURE1_NUMBER,
1403 MEASURE2_NUMBER,
1404 MEASURE3_NUMBER,
1405 MEASURE4_NUMBER,
1406 MEASURE5_NUMBER,
1407 MEASURE6_NUMBER,
1408 MEASURE7_NUMBER,
1409 MEASURE8_NUMBER,
1410 MEASURE9_NUMBER,
1411 MEASURE10_NUMBER,
1412 MEASURE1_CHAR,
1413 MEASURE2_CHAR,
1414 MEASURE3_CHAR,
1415 MEASURE4_CHAR,
1416 MEASURE5_CHAR,
1417 MEASURE6_CHAR,
1418 MEASURE7_CHAR,
1419 MEASURE8_CHAR,
1420 MEASURE9_CHAR,
1421 MEASURE10_CHAR,
1422 CREATION_DATE,
1423 CREATED_BY,
1424 LAST_UPDATE_DATE,
1425 LAST_UPDATED_BY,
1426 LAST_UPDATE_LOGIN,
1427 PROGRAM_APPLICATION_ID,
1428 PROGRAM_ID ,
1429 REQUEST_ID)
1430 values(qpr_pn_int_lines_s.nextval,
1431 -- p_int_hdr_id,
1432 g_r_ins_deal.SOURCE_REF_HDR_ID(i) ,
1433 g_r_ins_deal.SOURCE_REF_LINE_ID(i) ,
1434 g_r_ins_deal.SOURCE_REQ_LINE_NO(i),
1435 g_r_ins_deal.SOURCE_ID(i) ,
1436 g_r_ins_deal.ORG_ID(i) ,
1437 g_r_ins_deal.ORG_SHORT_DESC(i) ,
1438 g_r_ins_deal.ORG_LONG_DESC(i) ,
1439 g_r_ins_deal.INVENTORY_ITEM_ID(i) ,
1440 g_r_ins_deal.INVENTORY_ITEM_SHORT_DESC(i) ,
1441 g_r_ins_deal.INVENTORY_ITEM_LONG_DESC(i) ,
1442 g_r_ins_deal.ITEM_TYPE_CODE(i) ,
1443 g_r_ins_deal.TOP_MDL_SRC_LINE_ID(i) ,
1444 g_r_ins_deal.PAYMENT_TERM_ID(i) ,
1445 g_r_ins_deal.PAYMENT_TERM_SHORT_DESC(i) ,
1446 g_r_ins_deal.PAYMENT_TERM_LONG_DESC(i) ,
1447 g_r_ins_deal.UOM_CODE(i) ,
1448 g_r_ins_deal.UOM_SHORT_DESC(i) ,
1449 g_r_ins_deal.CURRENCY_CODE(i) ,
1450 g_r_ins_deal.CURRENCY_SHORT_DESC(i) ,
1451 g_r_ins_deal.ORDERED_QTY(i) ,
1452 g_r_ins_deal.LIST_PRICE(i) ,
1453 g_r_ins_deal.PROPOSED_PRICE(i) ,
1454 g_r_ins_deal.REVISED_OQ(i) , 'I',
1455 g_r_ins_deal.COMPETITOR_NAME(i) ,
1456 g_r_ins_deal.COMPETITOR_PRICE(i) ,
1457 g_r_ins_deal.COMMENTS(i),
1458 g_r_ins_deal.ADDITIONAL_INFO(i),
1459 g_r_ins_deal.SHIP_METHOD_CODE(i),
1460 g_r_ins_deal.SHIP_METHOD_SHORT_DESC(i),
1461 g_r_ins_deal.SHIP_METHOD_LONG_DESC(i),
1462 g_r_ins_deal.FREIGHT_CHARGES(i),
1463 g_r_ins_deal.GEOGRAPHY_ID(i),
1464 g_r_ins_deal.GEOGRAPHY_SHORT_DESC(i),
1465 g_r_ins_deal.GEOGRAPHY_LONG_DESC(i),
1466 g_r_ins_deal.MEASURE1_NUMBER(i),
1467 g_r_ins_deal.MEASURE2_NUMBER(i),
1468 g_r_ins_deal.MEASURE3_NUMBER(i),
1469 g_r_ins_deal.MEASURE4_NUMBER(i),
1470 g_r_ins_deal.MEASURE5_NUMBER(i),
1471 g_r_ins_deal.MEASURE6_NUMBER(i),
1472 g_r_ins_deal.MEASURE7_NUMBER(i),
1473 g_r_ins_deal.MEASURE8_NUMBER(i),
1474 g_r_ins_deal.MEASURE9_NUMBER(i),
1475 g_r_ins_deal.MEASURE10_NUMBER(i),
1476 g_r_ins_deal.MEASURE1_CHAR(i),
1477 g_r_ins_deal.MEASURE2_CHAR(i),
1478 g_r_ins_deal.MEASURE3_CHAR(i),
1479 g_r_ins_deal.MEASURE4_CHAR(i),
1480 g_r_ins_deal.MEASURE5_CHAR(i),
1481 g_r_ins_deal.MEASURE6_CHAR(i),
1482 g_r_ins_deal.MEASURE7_CHAR(i),
1483 g_r_ins_deal.MEASURE8_CHAR(i),
1484 g_r_ins_deal.MEASURE9_CHAR(i),
1485 g_r_ins_deal.MEASURE10_CHAR(i),
1486 g_sys_date,
1487 g_user_id,
1488 g_sys_date,
1489 g_user_id,
1490 g_login_id, g_prg_appl_id , g_prg_id, g_request_id);
1491 del_ins_deal_lines;
1492 exception
1493 when OTHERS then
1494 log_debug('ERROR INSERTING VALUES TO DEAL INTERFACE LINE');
1495 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1496 raise;
1497 end insert_deal_lines;
1498
1499 procedure update_deal_lines is
1500 begin
1501 log_debug('Updating deal interface lines ....');
1502 forall i in g_r_upd_deal.SOURCE_REF_HDR_ID.first..
1503 g_r_upd_deal.SOURCE_REF_HDR_ID.last
1504 update qpr_pn_int_lines set
1505 SOURCE_REQUEST_LINE_NUMBER = g_r_upd_deal.SOURCE_REQ_LINE_NO(i),
1506 SOURCE_ID = g_r_upd_deal.SOURCE_ID(i),
1507 ORG_ID = g_r_upd_deal.ORG_ID(i),
1508 ORG_SHORT_DESC = g_r_upd_deal.ORG_SHORT_DESC(i) ,
1509 ORG_LONG_DESC = g_r_upd_deal.ORG_LONG_DESC(i) ,
1510 INVENTORY_ITEM_ID = g_r_upd_deal.INVENTORY_ITEM_ID(i) ,
1511 INVENTORY_ITEM_SHORT_DESC= g_r_upd_deal.INVENTORY_ITEM_SHORT_DESC(i),
1512 INVENTORY_ITEM_LONG_DESC = g_r_upd_deal.INVENTORY_ITEM_LONG_DESC(i),
1513 ITEM_TYPE_CODE = g_r_upd_deal.ITEM_TYPE_CODE(i) ,
1514 TOP_MDL_SRC_LINE_ID = g_r_upd_deal.TOP_MDL_SRC_LINE_ID(i) ,
1515 PAYMENT_TERM_ID = g_r_upd_deal.PAYMENT_TERM_ID(i) ,
1516 PAYMENT_TERM_SHORT_DESC = g_r_upd_deal.PAYMENT_TERM_SHORT_DESC(i),
1517 PAYMENT_TERM_LONG_DESC = g_r_upd_deal.PAYMENT_TERM_LONG_DESC(i),
1518 UOM_CODE = g_r_upd_deal.UOM_CODE(i) ,
1519 UOM_SHORT_DESC = g_r_upd_deal.UOM_SHORT_DESC(i) ,
1520 CURRENCY_CODE = g_r_upd_deal.CURRENCY_CODE(i) ,
1521 CURRENCY_SHORT_DESC = g_r_upd_deal.CURRENCY_SHORT_DESC(i) ,
1522 ORDERED_QTY = g_r_upd_deal.ORDERED_QTY(i) ,
1523 LIST_PRICE = g_r_upd_deal.LIST_PRICE(i) ,
1524 PROPOSED_PRICE = g_r_upd_deal.PROPOSED_PRICE(i) ,
1525 REVISED_OQ = g_r_upd_deal.REVISED_OQ(i) ,
1526 COMPETITOR_NAME = g_r_upd_deal.COMPETITOR_NAME(i),
1527 COMPETITOR_PRICE = g_r_upd_deal.COMPETITOR_PRICE(i) ,
1528 COMMENTS = g_r_upd_deal.COMMENTS(i) ,
1529 ADDITIONAL_INFORMATION = g_r_upd_deal.ADDITIONAL_INFO(i),
1530 SHIP_METHOD_CODE = g_r_upd_deal.SHIP_METHOD_CODE(i),
1531 SHIP_METHOD_SHORT_DESC = g_r_upd_deal.SHIP_METHOD_SHORT_DESC(i),
1532 SHIP_METHOD_LONG_DESC = g_r_upd_deal.SHIP_METHOD_LONG_DESC(i),
1533 FREIGHT_CHARGES = g_r_upd_deal.FREIGHT_CHARGES(i),
1534 GEOGRAPHY_ID = g_r_upd_deal.GEOGRAPHY_ID(i),
1535 GEOGRAPHY_SHORT_DESC = g_r_upd_deal.GEOGRAPHY_SHORT_DESC(i),
1536 GEOGRAPHY_LONG_DESC = g_r_upd_deal.GEOGRAPHY_LONG_DESC(i),
1537 MEASURE1_NUMBER = g_r_upd_deal.MEASURE1_NUMBER(i),
1538 MEASURE2_NUMBER = g_r_upd_deal.MEASURE2_NUMBER(i),
1539 MEASURE3_NUMBER = g_r_upd_deal.MEASURE3_NUMBER(i),
1540 MEASURE4_NUMBER = g_r_upd_deal.MEASURE4_NUMBER(i),
1541 MEASURE5_NUMBER = g_r_upd_deal.MEASURE5_NUMBER(i),
1542 MEASURE6_NUMBER = g_r_upd_deal.MEASURE6_NUMBER(i),
1543 MEASURE7_NUMBER = g_r_upd_deal.MEASURE7_NUMBER(i),
1544 MEASURE8_NUMBER = g_r_upd_deal.MEASURE8_NUMBER(i),
1545 MEASURE9_NUMBER = g_r_upd_deal.MEASURE9_NUMBER(i),
1546 MEASURE10_NUMBER = g_r_upd_deal.MEASURE10_NUMBER(i),
1547 MEASURE1_CHAR = g_r_upd_deal.MEASURE1_CHAR(i),
1548 MEASURE2_CHAR = g_r_upd_deal.MEASURE2_CHAR(i),
1549 MEASURE3_CHAR = g_r_upd_deal.MEASURE3_CHAR(i),
1550 MEASURE4_CHAR = g_r_upd_deal.MEASURE4_CHAR(i),
1551 MEASURE5_CHAR = g_r_upd_deal.MEASURE5_CHAR(i),
1552 MEASURE6_CHAR = g_r_upd_deal.MEASURE6_CHAR(i),
1553 MEASURE7_CHAR = g_r_upd_deal.MEASURE7_CHAR(i),
1554 MEASURE8_CHAR = g_r_upd_deal.MEASURE8_CHAR(i),
1555 MEASURE9_CHAR = g_r_upd_deal.MEASURE9_CHAR(i),
1556 MEASURE10_CHAR = g_r_upd_deal.MEASURE10_CHAR(i),
1557 LAST_UPDATE_DATE = g_sys_date,
1558 LAST_UPDATED_BY = g_user_id,
1559 LAST_UPDATE_LOGIN = g_login_id,
1560 PROGRAM_APPLICATION_ID = g_prg_appl_id,
1561 PROGRAM_ID = g_prg_id,
1562 REQUEST_ID = g_request_id
1563 where PN_INT_LINE_ID = g_r_upd_deal.PN_REQ_INTERFACE_LINE_ID(i);
1564
1565 del_upd_deal_lines;
1566 exception
1567 when OTHERS then
1568 log_debug('ERROR IN UPDATING DEAL INTERFACE LINES');
1569 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1570 raise;
1571 end update_deal_lines;
1572
1573 procedure insert_update_deal_lines(
1574 p_header_id in number,
1575 p_sql in varchar2) is
1576 b_update boolean := false;
1577 l_req_int_hdr_id number;
1578 l_req_int_line_id number;
1579 l_rows number := 1000;
1580 l_qt_ictr number := 0;
1581 l_qt_uctr number := 0;
1582 s_status varchar2(1);
1583 s_sql varchar2(20000) := '';
1584 s_src_tbl varchar2(30);
1585 t_src_line_id num_type;
1586 t_int_line_id num_type;
1587 t_status char240_type;
1588 c_deal_line SYS_REFCURSOR;
1589 begin
1590 log_debug('In Deal Line method..');
1591
1592 select l.SOURCE_REF_LINE_ID, l.PN_INT_LINE_ID, l.PN_REQ_LINE_STATUS_FLAG
1593 bulk collect into t_src_line_id, t_int_line_id, t_status
1594 from qpr_pn_int_lines l, qpr_pn_int_headers h
1595 where h.source_ref_header_id = p_header_id
1596 and h.instance_id = g_instance_id
1597 and h.source_id = g_source_id
1598 and h.source_ref_header_short_desc = g_quote_hdr_sd
1599 and h.source_id = l.source_id
1600 and h.source_ref_header_id = l.source_ref_hdr_id
1601 order by l.source_ref_line_id;
1602
1603 open c_deal_line for p_sql using p_header_id;
1604 loop
1605 fetch c_deal_line bulk collect into g_t_src_lines limit l_rows;
1606 exit when g_t_src_lines.count = 0;
1607 for i in g_t_src_lines.first..g_t_src_lines.last loop
1608 b_update := false;
1609 if t_src_line_id.count= 0 then
1610 l_qt_ictr := l_qt_ictr + 1;
1611 assign_ins_deal_lines(l_qt_ictr, i);
1612 else
1613 for j in t_src_line_id.first..t_src_line_id.last loop
1614 if t_src_line_id.exists(j) then
1615 if t_src_line_id(j) = g_t_src_lines(i).source_ref_line_id then
1616 s_status := t_status(j);
1617 b_update := true;
1618 l_req_int_line_id := t_int_line_id(j);
1619 t_src_line_id.delete(j);
1620 t_int_line_id.delete(j);
1621 t_status.delete(j);
1622 exit;
1623 end if;
1624 end if;
1625 end loop;
1626 if b_update = true then
1627 if s_status <> 'P' then
1628 l_qt_uctr := l_qt_uctr + 1;
1629 assign_upd_deal_lines(l_qt_uctr, i, l_req_int_line_id);
1630 else
1631 log_debug('Status of line ' ||g_t_src_lines(i).source_ref_line_id || ' does not permit update.');
1632 end if;
1633 else
1634 l_qt_ictr := l_qt_ictr + 1;
1635 assign_ins_deal_lines(l_qt_ictr, i);
1636 end if;
1637 end if;
1638 end loop; -- all src lines loop
1639 if g_r_ins_deal.SOURCE_REF_LINE_ID.count > 0 then
1640 insert_deal_lines( p_header_id);
1641 end if;
1642 if g_r_upd_deal.SOURCE_REF_LINE_ID.count > 0 then
1643 update_deal_lines;
1644 end if;
1645 g_t_src_lines.delete;
1646 end loop;
1647 -- end if;
1648 exception
1649 when OTHERS then
1650 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1651 raise;
1652 end insert_update_deal_lines;
1653
1654 procedure insert_deal_adjs(p_header_id in number, p_sql in varchar2) is
1655
1656 l_ctr number:= 0;
1657 l_rows number := 1000;
1658 l_status varchar2(1);
1659 b_first boolean := true;
1660 t_pr_adj_val qpr_deal_adj_type;
1661 t_adj_rec qpr_src_qtn_adj_type;
1662 c_adj SYS_REFCURSOR;
1663
1664 begin
1665 log_debug('In price adjustments...');
1666
1667 begin
1668 select 1 into l_status
1669 from qpr_pn_int_headers h
1670 where h.source_ref_header_id = p_header_id
1671 and h.instance_id = g_instance_id
1672 and h.source_id = g_source_id
1673 and h.pn_req_header_status_flag <> 'P';
1674 exception
1675 when no_data_found then
1676 log_debug('Quote status does not permit modifications');
1677 return;
1678 end;
1679
1680
1681 open c_adj for p_sql using p_header_id;
1682 loop
1683 fetch c_adj bulk collect into t_adj_rec limit l_rows;
1684 exit when t_adj_rec.count = 0;
1685
1686 if b_first then
1687 b_first := false;
1688
1689 delete qpr_pn_int_pr_adjs where source_ref_hdr_id = p_header_id
1690 and source_id = g_source_id
1691 and erosion_type = t_adj_rec(1).EROSION_TYPE;
1692 end if;
1693
1694 for k in t_adj_rec.first..t_adj_rec.last loop
1695 t_pr_adj_val.SOURCE_ID(l_ctr) := t_adj_rec(k).SOURCE_ID;
1696 t_pr_adj_val.EROSION_TYPE(l_ctr) := t_adj_rec(k).EROSION_TYPE;
1697 t_pr_adj_val.EROSION_NAME(l_ctr) := t_adj_rec(k).EROSION_NAME;
1698 t_pr_adj_val.EROSION_DESC(l_ctr) := t_adj_rec(k).EROSION_DESC;
1699 t_pr_adj_val.EROSION_PER_UNIT(l_ctr) :=
1700 t_adj_rec(k).EROSION_PER_UNIT;
1701 t_pr_adj_val.EROSION_AMOUNT(l_ctr) := t_adj_rec(k).EROSION_AMOUNT;
1702 t_pr_adj_val.SRC_REF_HDR_ID(l_ctr) := t_adj_rec(k).SOURCE_REF_HDR_ID;
1703 t_pr_adj_val.SRC_REF_LINE_ID(l_ctr) := t_adj_rec(k).SOURCE_REF_LINE_ID;
1704 l_ctr := l_ctr + 1;
1705 end loop;
1706
1707
1708 forall i in t_pr_adj_val.src_ref_line_id.first..
1709 t_pr_adj_val.src_ref_line_id.last
1710 insert into qpr_pn_int_pr_adjs(pn_int_pr_adj_id,
1711 source_ref_hdr_id,
1712 source_ref_line_id,
1713 source_id,
1714 erosion_type,
1715 erosion_name,
1716 erosion_desc,
1717 erosion_per_unit,
1718 erosion_amount,
1719 creation_date,
1720 created_by,
1721 last_update_date,
1722 last_updated_by,
1723 last_update_login,
1724 program_application_id,
1725 program_id,
1726 request_id)
1727 values(qpr_pn_int_pr_adjs_s.nextval,
1728 t_pr_adj_val.SRC_REF_HDR_ID(i),
1729 t_pr_adj_val.src_ref_line_id(i),
1730 t_pr_adj_val.source_id(i),
1731 t_pr_adj_val.erosion_type(i),
1732 t_pr_adj_val.erosion_name(i),
1733 t_pr_adj_val.erosion_desc(i),
1734 t_pr_adj_val.erosion_per_unit(i),
1735 t_pr_adj_val.erosion_amount(i),
1736 g_sys_date,
1737 g_user_id,
1738 g_sys_date,
1739 g_user_id,
1740 g_login_id, g_prg_appl_id , g_prg_id, g_request_id);
1741
1742 t_pr_adj_val.source_id.delete;
1743 t_pr_adj_val.EROSION_TYPE.delete;
1744 t_pr_adj_val.EROSION_NAME.delete;
1745 t_pr_adj_val.EROSION_DESC.delete;
1746 t_pr_adj_val.EROSION_PER_UNIT.delete;
1747 t_pr_adj_val.EROSION_AMOUNT.delete;
1748 t_pr_adj_val.src_ref_hdr_id.delete;
1749 t_pr_adj_val.src_ref_line_id.delete;
1750
1751 t_adj_rec.delete;
1752 end loop;
1753 exception
1754 when OTHERS then
1755 log_debug(dbms_utility.format_error_backtrace);
1756 raise;
1757 end insert_deal_adjs;
1758
1759 procedure fill_measure_data(errbuf out nocopy varchar2,
1760 retcode out nocopy varchar2,
1761 p_instance_id in number,
1762 p_date_from in varchar2,
1763 p_date_to in varchar2,
1764 p_meas_type in varchar2,
1765 p_header_id in number default 0) is
1766
1767 db_link varchar2(150) := '';
1768 src_table varchar2(200);
1769
1770 s_sql varchar2(30000) := '';
1771
1772 l_rec_count number :=0;
1773 l_start_time number;
1774 l_end_time number;
1775 l_ret_code number;
1776 l_src_count number;
1777 l_inst_id number;
1778 l_req_int_hdr_id number;
1779 l_inst_type varchar2(30);
1780
1781 cursor c_src_cols(m_type varchar2,m_ins_id number,m_src_tname varchar2, l_inst_type varchar2) is
1782 select distinct nvl(USER_SRC_COL_NAME,SRC_COL_NAME) SRC_COL_NAME,
1783 nvl(USER_TGT_COL_NAME, TGT_COL_NAME) TGT_COL_NAME
1784 from QPR_MEASURE_SOURCES
1785 where MEASURE_TYPE_CODE = m_type
1786 and INSTANCE_ID = m_ins_id
1787 and INSTANCE_TYPE = l_inst_type
1788 and nvl(user_src_tbl_name, src_tbl_name ) = m_src_tname
1789 order by TGT_COL_NAME;
1790
1791 cursor c_srcs(m_type varchar2, m_ins_id number, l_inst_type varchar2)is
1792 select distinct nvl(USER_SRC_TBL_NAME, SRC_TBL_NAME) SRC_TBL_NAME,
1793 TGT_TBL_NAME
1794 from QPR_MEASURE_SOURCES
1795 where MEASURE_TYPE_CODE = m_type
1796 and INSTANCE_ID = m_ins_id
1797 and INSTANCE_TYPE = l_inst_type
1798 order by TGT_TBL_NAME;
1799 begin
1800 log_debug('Starting...');
1801 select hsecs into l_start_time from v$timer;
1802 log_debug('Start time :'||
1803 to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
1804 db_link := qpr_sr_util.get_dblink(p_instance_id);
1805
1806 g_instance_id := p_instance_id;
1807 g_meas_type := p_meas_type;
1808
1809 fnd_profile.get('CONC_REQUEST_ID', g_request_id);
1810 g_sys_date := sysdate;
1811 g_user_id := fnd_global.user_id;
1812 g_login_id := fnd_global.conc_login_id;
1813 g_prg_appl_id := fnd_global.prog_appl_id;
1814 g_prg_id := fnd_global.conc_program_id;
1815
1816 if not qpr_sr_util.dm_parameters_ok then
1817 retcode:= 2;
1818 FND_MESSAGE.Set_Name ('QPR','QPR_NULL_PARAMETERS');
1819 FND_MSG_PUB.Add;
1820 log_debug('One or more mandatory parameters are not filled');
1821 return;
1822 end if;
1823
1824 select instance_type into l_inst_type
1825 from qpr_instances
1826 where instance_id = p_instance_id;
1827
1828 select count(*) into l_src_count
1829 from QPR_MEASURE_SOURCES
1830 where INSTANCE_ID = p_instance_id
1831 and MEASURE_TYPE_CODE = p_meas_type;
1832
1833 if l_src_count > 0 then
1834 l_inst_id := p_instance_id;
1835 else
1836 l_inst_id := SEEDED_INSTANCE_ID;
1837 end if;
1838
1839 for r_usr_src in c_srcs(p_meas_type, l_inst_id, l_inst_type) loop
1840 log_debug('Fetching source columns ...');
1841 open c_src_cols(p_meas_type, l_inst_id,
1842 r_usr_src.SRC_TBL_NAME, l_inst_type);
1843 fetch c_src_cols bulk collect into g_src_cols, g_trg_cols;
1844 close c_src_cols;
1845
1846 src_table := r_usr_src.SRC_TBL_NAME || db_link ;
1847
1848 if p_meas_type = OM_MEAS_TYPE_DEALINT or
1849 p_meas_type = ASO_MEAS_TYPE_DEALINT then
1850 s_sql := get_deal_sql(src_table, r_usr_src.TGT_TBL_NAME);
1851 if r_usr_src.TGT_TBL_NAME = DEAL_HEADER_TBL then
1852 s_sql := s_sql || ' and rownum < 2' ;
1853 l_req_int_hdr_id := insert_update_deal_hdr(
1854 p_header_id,
1855 s_sql);
1856 elsif r_usr_src.TGT_TBL_NAME = DEAL_LINE_TBL then
1857 -- since the sources are sorted by target tbl name the lines will
1858 -- come after header only
1859 insert_update_deal_lines(p_header_id,s_sql);
1860 else
1861 insert_deal_adjs(p_header_id, s_sql);
1862 end if;
1863 else
1864 s_sql := get_select_meas_sql(src_table, p_meas_type);
1865 insert_update_meas_data(p_date_from, p_date_to, s_sql);
1866 end if;
1867 end loop;
1868 select hsecs into l_end_time from v$timer;
1869 log_debug('End time :'|| to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
1870 log_debug('Time taken for loading(sec):' ||(l_end_time - l_start_time)/100);
1871 exception
1872 when OTHERS then
1873 retcode := 2;
1874 errbuf := 'ERROR: ' || substr(sqlerrm, 1, 1000);
1875 log_debug(substr(sqlerrm, 1, 1000));
1876 log_debug('CANNOT POPULATE FACT DATA');
1877 end fill_measure_data;
1878
1879 procedure load_quote_data(errbuf out nocopy varchar2,
1880 retcode out nocopy varchar2,
1881 p_instance_id in number,
1882 p_src_choice in number default 1,
1883 p_quote_number in number default 0,
1884 p_quote_version in number default 0,
1885 p_order_type in varchar2 default null) is
1886 l_dummy number;
1887 begin
1888 begin
1889 select request_header_id into l_dummy
1890 from qpr_pn_request_hdrs_b
1891 where instance_id = p_instance_id
1892 and source_id = decode(p_src_choice,1, 660, 2, 697, p_src_choice)
1893 and source_ref_hdr_short_desc = (p_quote_number || ' - Ver '|| p_quote_version)
1894 and nvl(request_status, 'ACTIVE') = 'ACTIVE'
1895 and nvl(simulation_flag, 'Y') = 'N'
1896 and rownum < 2;
1897
1898 retcode := 1;
1899 errbuf := 'Active Request ' || l_dummy || ' exist for this quote.';
1900 return;
1901 exception
1902 -- when no active requests are present then load quote --
1903 when NO_DATA_FOUND then
1904 load_quote_data_api(errbuf ,
1905 retcode ,
1906 p_instance_id ,
1907 p_src_choice ,
1908 null,
1909 p_quote_number ,
1910 p_quote_version ,
1911 p_order_type );
1912
1913 end;
1914 exception
1915 when OTHERS then
1916 retcode := 2;
1917 errbuf := 'ERROR: ' || substr(sqlerrm, 1, 1000);
1918 log_debug('ERROR IN LOADING QUOTE DATA');
1919 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1920 raise;
1921 end;
1922
1923 procedure load_quote_data_api(errbuf out nocopy varchar2,
1924 retcode out nocopy varchar2,
1925 p_instance_id in number,
1926 p_src_choice in number default 1,
1927 p_quote_header_id in number default null,
1928 p_quote_number in number default 0,
1929 p_quote_version in number default 0,
1930 p_order_type in varchar2 default null) is
1931 p_header_id number;
1932 l_sql varchar2(1000);
1933 src_tbl_name varchar2(200);
1934 s_deal_type varchar2(30);
1935 l_inst_type varchar2(30);
1936 begin
1937
1938 g_origin := p_src_choice;
1939
1940 if p_instance_id is null then
1941 retcode := 2;
1942 FND_MESSAGE.Set_Name ('QPR','QPR_NULL_INSTANCE');
1943 FND_MSG_PUB.Add;
1944 errbuf := 'Instance Id cannot be null';
1945 return;
1946 end if;
1947
1948 if (p_src_choice = 1 or p_src_choice = 660 )then
1949 s_deal_type := OM_MEAS_TYPE_DEALINT;
1950 elsif (p_src_choice = 2 or p_src_choice = 697) then
1951 s_deal_type := ASO_MEAS_TYPE_DEALINT;
1952 else
1953 s_deal_type := p_src_choice;
1954 end if;
1955
1956 select distinct src_tbl_name into src_tbl_name
1957 from qpr_measure_sources
1958 where measure_type_code = s_deal_type
1959 and tgt_tbl_name = DEAL_HEADER_TBL
1960 and INSTANCE_TYPE = (select instance_type
1961 from qpr_instances
1962 where instance_id = p_instance_id);
1963 if p_quote_header_id is null then
1964 l_sql := 'select quote_header_id, quote_header_sd, source_id from '
1965 || src_tbl_name||qpr_sr_util.get_dblink(p_instance_id)
1966 || ' where quote_number = ' || p_quote_number
1967 || ' and quote_version = ' || p_quote_version ;
1968 if p_src_choice = 1 or p_src_choice = 660 then
1969 l_sql := l_sql || ' and order_type_name = ''' || p_order_type || '''';
1970 end if;
1971 l_sql := l_sql || ' and rownum < 2';
1972 else
1973 l_sql := 'select quote_header_id, quote_header_sd, source_id from '
1974 || src_tbl_name||qpr_sr_util.get_dblink(p_instance_id)
1975 || ' where quote_header_id = ' || p_quote_header_id;
1976 l_sql := l_sql || ' and rownum < 2';
1977 end if;
1978 log_debug('SQL: '||l_sql);
1979 execute immediate l_sql into p_header_id, g_quote_hdr_sd, g_source_id;
1980 if nvl(p_header_id, 0) = 0 then
1981 retcode := 2;
1982 FND_MESSAGE.Set_Name ('QPR','QPR_NO_QUOTE');
1983 FND_MSG_PUB.Add;
1984 log_debug('Quote does not exist. ');
1985 else
1986 log_debug('Header_id: ' || p_header_id);
1987 fill_measure_data(errbuf,retcode, p_instance_id , null, null,
1988 s_deal_type,p_header_id);
1989 end if;
1990
1991 exception
1992 WHEN NO_DATA_FOUND then
1993 retcode := 2;
1994 log_debug('Quote does not exist.');
1995 when OTHERS then
1996 retcode := 2;
1997 errbuf := 'ERROR: ' || substr(sqlerrm, 1, 1000);
1998 log_debug('ERROR IN LOADING QUOTE DATA');
1999 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2000 raise;
2001 end load_quote_data_api;
2002
2003 function is_source_quote_changed(errbuf out nocopy varchar2,
2004 retcode out nocopy varchar2,
2005 p_instance_id in number,
2006 p_source_id in number,
2007 p_src_quote_header_id in number)
2008 return varchar2 is
2009 l_resp_cust qpr_pn_request_hdrs_b.customer_id%type;
2010 l_resp_sales_rep qpr_pn_request_hdrs_b.sales_rep_id%type;
2011 l_resp_sc qpr_pn_request_hdrs_b.sales_channel_code%type;
2012 l_request_hdr_id qpr_pn_request_hdrs_b.request_header_id%type;
2013 r_hdr qpr_pn_int_headers%rowtype;
2014 r_lines qpr_pn_int_lines%rowtype;
2015 l_hdr_matching boolean := false;
2016 l_line_matching boolean := false;
2017 l_org_id qpr_pn_lines.ORG_ID%type;
2018 l_item_id qpr_pn_lines.inventory_item_id%type;
2019 l_pt_id qpr_pn_lines.payment_term_id%type;
2020 l_shm_code qpr_pn_lines.ship_method_code%type;
2021 l_geo_id qpr_pn_lines.geography_id%type;
2022 l_uom_code qpr_pn_lines.uom_code%type;
2023 l_ord_qty qpr_pn_lines.ordered_qty%type;
2024 l_price qpr_pn_lines.proposed_price%type;
2025 l_curr_code qpr_pn_lines.currency_code%type;
2026 l_quote_changed varchar2(1);
2027 l_sql varchar2(10000);
2028 l_line_cnt number;
2029
2030 c_ref SYS_REFCURSOR;
2031
2032 function fetch_sql(p_tgt_tbl_name varchar2) return varchar2 is
2033 l_src_tbl varchar2(250);
2034 l_sql varchar2(10000);
2035
2036 cursor c_src_cols(m_src_tname varchar2) is
2037 select distinct nvl(USER_SRC_COL_NAME,SRC_COL_NAME) SRC_COL_NAME,
2038 nvl(USER_TGT_COL_NAME,TGT_COL_NAME) TGT_COL_NAME
2039 from QPR_MEASURE_SOURCES src, qpr_instances inst
2040 where src.instance_type = inst.instance_type
2041 and inst.instance_id = p_instance_id
2042 and src.measure_type_code = decode(p_source_id, 660, 'OM_DEALINT',
2043 697, 'ASO_DEALINT')
2044 and nvl(src.user_src_tbl_name, src.src_tbl_name ) = m_src_tname
2045 order by TGT_COL_NAME;
2046
2047 begin
2048 select distinct src_tbl_name into l_src_tbl
2049 from qpr_measure_sources src, qpr_instances inst
2050 where src.instance_type = inst.instance_type
2051 and inst.instance_id = p_instance_id
2052 and src.measure_type_code = decode(p_source_id, 660, 'OM_DEALINT',
2053 697, 'ASO_DEALINT')
2054 and tgt_tbl_name = p_tgt_tbl_name;
2055
2056 open c_src_cols(l_src_tbl);
2057 fetch c_src_cols bulk collect into g_src_cols, g_trg_cols;
2058 close c_src_cols;
2059
2060 l_src_tbl := l_src_tbl || qpr_sr_util.get_dblink(p_instance_id);
2061
2062 l_sql := get_deal_sql(l_src_tbl, p_tgt_tbl_name);
2063
2064 return l_sql;
2065 end fetch_sql;
2066
2067
2068 begin
2069 l_quote_changed := 'N';
2070 -- read request_header details
2071 begin
2072 select req.CUSTOMER_ID, req.SALES_REP_ID, req.SALES_CHANNEL_CODE,
2073 req.request_header_id
2074 into l_resp_cust, l_resp_sales_rep, l_resp_sc, l_request_hdr_id
2075 from qpr_pn_request_hdrs_b req
2076 where instance_id = p_instance_id
2077 and source_id = p_source_id
2078 and source_ref_hdr_id = p_src_quote_header_id
2079 and request_status = 'ACTIVE'
2080 and rownum < 2;
2081
2082 --get details from source ---
2083 l_sql := fetch_sql(DEAL_HEADER_TBL) ;
2084 l_sql := l_sql || ' and rownum < 2 ';
2085
2086 open c_ref for l_sql using p_src_quote_header_id;
2087 fetch c_ref into r_hdr;
2088 close c_ref;
2089
2090 if nvl(l_resp_cust,0) = nvl(r_hdr.CUSTOMER_ID,0) and
2091 nvl(l_resp_sales_rep,0) = nvl(r_hdr.SALES_REP_ID,0) and
2092 nvl(l_resp_sc, '*') = nvl(r_hdr.SALES_CHANNEL_CODE, '*') then
2093 l_hdr_matching := true;
2094 end if;
2095 exception
2096 when no_data_found then
2097 l_hdr_matching := false;
2098 end;
2099
2100 if not l_hdr_matching then
2101 -- if hdr values are modified then quote is changed
2102 l_quote_changed := 'Y';
2103 else
2104 -- fetch line values from source -----
2105
2106 l_sql := fetch_sql(DEAL_LINE_TBL);
2107
2108 open c_ref for l_sql using p_src_quote_header_id;
2109 loop
2110 fetch c_ref into r_lines;
2111 exit when c_ref%notfound;
2112
2113 l_line_matching := false;
2114
2115 --read line details from pn_lines ----
2116 begin
2117 select ORG_ID,INVENTORY_ITEM_ID,ORIG_PAYMENT_TERM_ID,
2118 ORIG_SHIP_METHOD_CODE,
2119 GEOGRAPHY_ID,UOM_CODE,ORDERED_QTY,PROPOSED_PRICE,CURRENCY_CODE
2120 into l_org_id, l_item_id, l_pt_id, l_shm_code,
2121 l_geo_id, l_uom_code, l_ord_qty, l_price, l_curr_code
2122 from qpr_pn_lines
2123 where request_header_id = l_request_hdr_id
2124 and source_ref_line_id = r_lines.SOURCE_REF_LINE_ID
2125 and source_ref_hdr_id = r_lines.SOURCE_REF_HDR_ID
2126 and source_id = r_lines.SOURCE_ID
2127 and item_type_code <> 'DUMMY_PARENT'
2128 and rownum < 2;
2129
2130 if nvl(l_org_id, 0) = nvl(r_lines.org_id,0) and
2131 nvl(l_item_id,0) = nvl(r_lines.inventory_item_id,0) and
2132 nvl(l_pt_id,0) = nvl(r_lines.payment_term_id,0) and
2133 nvl(l_shm_code,'*') = nvl(r_lines.ship_method_code, '*') and
2134 nvl(l_geo_id,0) = nvl(r_lines.geography_id, 0) and
2135 nvl(l_uom_code, '*') = nvl(r_lines.uom_code, '*') and
2136 nvl(l_ord_qty,0) = nvl(r_lines.ordered_qty,0) and
2137 nvl(l_price,0) = nvl(r_lines.proposed_price,0) and
2138 nvl(l_curr_code, '*') = nvl(r_lines.Currency_code, '*') then
2139 l_line_matching := true;
2140 end if;
2141 exception
2142 when NO_DATA_FOUND then
2143 l_line_matching := false;
2144 end;
2145
2146 -- even if one line is not matching then quote does not match
2147 if not l_line_matching then
2148 l_quote_changed := 'Y';
2149 exit;
2150 end if;
2151
2152 end loop;
2153 close c_ref;
2154
2155 -- handle the case: header is matching- few lines have been deleted in
2156 -- source and the existing lines match.
2157 if l_quote_changed = 'N' then
2158 select count(*) into l_line_cnt
2159 from qpr_pn_lines
2160 where request_header_id = l_request_hdr_id
2161 and item_type_code <> 'DUMMY_PARENT';
2162
2163 open c_ref for l_sql using p_src_quote_header_id;
2164 loop
2165 fetch c_ref into r_lines;
2166 exit when c_ref%notfound;
2167 end loop;
2168
2169 if c_ref%rowcount <> l_line_cnt then
2170 l_quote_changed := 'Y';
2171 end if;
2172 close c_ref;
2173 end if;
2174 end if;
2175
2176 return(l_quote_changed);
2177 exception
2178 when others then
2179 retcode := 2;
2180 errbuf := sqlerrm;
2181 return(null);
2182 end is_source_quote_changed;
2183
2184 END;
2185