[Home] [Help]
PACKAGE BODY: APPS.QPR_LOAD_MEAS_DATA
Source
1 PACKAGE BODY QPR_LOAD_MEAS_DATA AS
2 /* $Header: QPRUFMDB.pls 120.25 2012/01/03 06:30:37 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 l_sql varchar2(1000);
1104 begin
1105 g_r_upd_deal.PN_REQ_INTERFACE_LINE_ID(p_qtn_uctr) := p_int_line_id;
1106 g_r_upd_deal.SOURCE_REF_HDR_ID(p_qtn_uctr) :=
1107 g_t_src_lines(p_src_ctr).SOURCE_REF_HDR_ID;
1108 g_r_upd_deal.SOURCE_REF_LINE_ID(p_qtn_uctr) :=
1109 g_t_src_lines(p_src_ctr).SOURCE_REF_LINE_ID;
1110 if g_meas_type = ASO_MEAS_TYPE_DEALINT then
1111 begin
1112 l_sql := 'select QUOTE_LINE_NUMBER from qpr_sr_quote_line_num_v';
1113 l_sql := l_sql || qpr_sr_util.get_dblink(g_instance_id);
1114 l_sql := l_sql || ' where quote_header_id = :1 ';
1115 l_sql := l_sql || ' and quote_line_id = :2 and source_id = :3';
1116 execute immediate l_sql into l_line_no using
1117 g_t_src_lines(p_src_ctr).SOURCE_REF_HDR_ID,
1118 g_t_src_lines(p_src_ctr).SOURCE_REF_LINE_ID,
1119 g_source_id;
1120 exception
1121 when no_data_found then
1122 l_line_no := g_t_src_lines(p_src_ctr).SOURCE_REQUEST_LINE_NUMBER;
1123 end;
1124 g_r_upd_deal.SOURCE_REQ_LINE_NO(p_qtn_uctr) := l_line_no;
1125 else
1126 g_r_upd_deal.SOURCE_REQ_LINE_NO(p_qtn_uctr) :=
1127 g_t_src_lines(p_src_ctr).SOURCE_REQUEST_LINE_NUMBER;
1128 end if;
1129 g_r_upd_deal.SOURCE_ID(p_qtn_uctr) := g_t_src_lines(p_src_ctr).SOURCE_ID;
1130 g_r_upd_deal.ORG_ID(p_qtn_uctr) := g_t_src_lines(p_src_ctr).ORG_ID;
1131 g_r_upd_deal.ORG_SHORT_DESC(p_qtn_uctr) :=
1132 g_t_src_lines(p_src_ctr).ORG_SHORT_DESC;
1133 g_r_upd_deal.ORG_LONG_DESC(p_qtn_uctr) :=
1134 g_t_src_lines(p_src_ctr).ORG_LONG_DESC;
1135 g_r_upd_deal.INVENTORY_ITEM_ID(p_qtn_uctr) :=
1136 g_t_src_lines(p_src_ctr).INVENTORY_ITEM_ID;
1137 g_r_upd_deal.INVENTORY_ITEM_SHORT_DESC(p_qtn_uctr) :=
1138 g_t_src_lines(p_src_ctr).INVENTORY_ITEM_SHORT_DESC;
1139 g_r_upd_deal.INVENTORY_ITEM_LONG_DESC(p_qtn_uctr) :=
1140 g_t_src_lines(p_src_ctr).INVENTORY_ITEM_LONG_DESC;
1141 g_r_upd_deal.ITEM_TYPE_CODE(p_qtn_uctr) :=
1142 g_t_src_lines(p_src_ctr).ITEM_TYPE_CODE;
1143 g_r_upd_deal.TOP_MDL_SRC_LINE_ID(p_qtn_uctr) :=
1144 g_t_src_lines(p_src_ctr).TOP_MDL_SRC_LINE_ID;
1145 g_r_upd_deal.PAYMENT_TERM_ID(p_qtn_uctr) :=
1146 g_t_src_lines(p_src_ctr).PAYMENT_TERM_ID;
1147 g_r_upd_deal.PAYMENT_TERM_SHORT_DESC(p_qtn_uctr) :=
1148 g_t_src_lines(p_src_ctr).PAYMENT_TERM_SHORT_DESC;
1149 g_r_upd_deal.PAYMENT_TERM_LONG_DESC(p_qtn_uctr) :=
1150 g_t_src_lines(p_src_ctr).PAYMENT_TERM_LONG_DESC;
1151 g_r_upd_deal.UOM_CODE(p_qtn_uctr) := g_t_src_lines(p_src_ctr).UOM_CODE;
1152 g_r_upd_deal.UOM_SHORT_DESC(p_qtn_uctr) :=
1153 g_t_src_lines(p_src_ctr).UOM_SHORT_DESC;
1154 g_r_upd_deal.CURRENCY_CODE(p_qtn_uctr) :=
1155 g_t_src_lines(p_src_ctr).CURRENCY_CODE;
1156 g_r_upd_deal.CURRENCY_SHORT_DESC(p_qtn_uctr) :=
1157 g_t_src_lines(p_src_ctr).CURRENCY_SHORT_DESC;
1158 g_r_upd_deal.ORDERED_QTY(p_qtn_uctr) := g_t_src_lines(p_src_ctr).ORDERED_QTY;
1159 g_r_upd_deal.LIST_PRICE(p_qtn_uctr) := g_t_src_lines(p_src_ctr).LIST_PRICE;
1160 g_r_upd_deal.PROPOSED_PRICE(p_qtn_uctr) :=
1161 g_t_src_lines(p_src_ctr).PROPOSED_PRICE;
1162 g_r_upd_deal.REVISED_OQ(p_qtn_uctr) :=
1163 g_t_src_lines(p_src_ctr).REVISED_OQ;
1164 g_r_upd_deal.COMPETITOR_NAME(p_qtn_uctr) :=
1165 g_t_src_lines(p_src_ctr).COMPETITOR_NAME;
1166 g_r_upd_deal.COMPETITOR_PRICE(p_qtn_uctr) :=
1167 g_t_src_lines(p_src_ctr).COMPETITOR_PRICE;
1168 g_r_upd_deal.COMMENTS(p_qtn_uctr) := g_t_src_lines(p_src_ctr).COMMENTS;
1169 g_r_upd_deal.ADDITIONAL_INFO(p_qtn_uctr) :=
1170 g_t_src_lines(p_src_ctr).ADDITIONAL_INFORMATION;
1171 g_r_upd_deal.SHIP_METHOD_CODE(p_qtn_uctr) :=
1172 g_t_src_lines(p_src_ctr).SHIP_METHOD_CODE;
1173 g_r_upd_deal.SHIP_METHOD_SHORT_DESC(p_qtn_uctr) :=
1174 g_t_src_lines(p_src_ctr).SHIP_METHOD_SHORT_DESC;
1175 g_r_upd_deal.SHIP_METHOD_LONG_DESC(p_qtn_uctr) :=
1176 g_t_src_lines(p_src_ctr).SHIP_METHOD_LONG_DESC;
1177 g_r_upd_deal.FREIGHT_CHARGES(p_qtn_uctr) :=
1178 g_t_src_lines(p_src_ctr).FREIGHT_CHARGES;
1179 g_r_upd_deal.GEOGRAPHY_ID(p_qtn_uctr) :=
1180 g_t_src_lines(p_src_ctr).GEOGRAPHY_ID;
1181 g_r_upd_deal.GEOGRAPHY_SHORT_DESC(p_qtn_uctr) :=
1182 g_t_src_lines(p_src_ctr).GEOGRAPHY_SHORT_DESC;
1183 g_r_upd_deal.GEOGRAPHY_LONG_DESC(p_qtn_uctr) :=
1184 g_t_src_lines(p_src_ctr).GEOGRAPHY_LONG_DESC;
1185 g_r_upd_deal.MEASURE1_NUMBER(p_qtn_uctr) :=
1186 g_t_src_lines(p_src_ctr).MEASURE1_NUMBER;
1187 g_r_upd_deal.MEASURE2_NUMBER(p_qtn_uctr) :=
1188 g_t_src_lines(p_src_ctr).MEASURE2_NUMBER;
1189 g_r_upd_deal.MEASURE3_NUMBER(p_qtn_uctr) :=
1190 g_t_src_lines(p_src_ctr).MEASURE3_NUMBER;
1191 g_r_upd_deal.MEASURE4_NUMBER(p_qtn_uctr) :=
1192 g_t_src_lines(p_src_ctr).MEASURE4_NUMBER;
1193 g_r_upd_deal.MEASURE5_NUMBER(p_qtn_uctr) :=
1194 g_t_src_lines(p_src_ctr).MEASURE5_NUMBER;
1195 g_r_upd_deal.MEASURE6_NUMBER(p_qtn_uctr) :=
1196 g_t_src_lines(p_src_ctr).MEASURE6_NUMBER;
1197 g_r_upd_deal.MEASURE7_NUMBER(p_qtn_uctr) :=
1198 g_t_src_lines(p_src_ctr).MEASURE7_NUMBER;
1199 g_r_upd_deal.MEASURE8_NUMBER(p_qtn_uctr) :=
1200 g_t_src_lines(p_src_ctr).MEASURE8_NUMBER;
1201 g_r_upd_deal.MEASURE9_NUMBER(p_qtn_uctr) :=
1202 g_t_src_lines(p_src_ctr).MEASURE9_NUMBER;
1203 g_r_upd_deal.MEASURE10_NUMBER(p_qtn_uctr) :=
1204 g_t_src_lines(p_src_ctr).MEASURE10_NUMBER;
1205 g_r_upd_deal.MEASURE1_CHAR(p_qtn_uctr) :=
1206 g_t_src_lines(p_src_ctr).MEASURE1_CHAR;
1207 g_r_upd_deal.MEASURE2_CHAR(p_qtn_uctr) :=
1208 g_t_src_lines(p_src_ctr).MEASURE2_CHAR;
1209 g_r_upd_deal.MEASURE3_CHAR(p_qtn_uctr) :=
1210 g_t_src_lines(p_src_ctr).MEASURE3_CHAR;
1211 g_r_upd_deal.MEASURE4_CHAR(p_qtn_uctr) :=
1212 g_t_src_lines(p_src_ctr).MEASURE4_CHAR;
1213 g_r_upd_deal.MEASURE5_CHAR(p_qtn_uctr) :=
1214 g_t_src_lines(p_src_ctr).MEASURE5_CHAR;
1215 g_r_upd_deal.MEASURE6_CHAR(p_qtn_uctr) :=
1216 g_t_src_lines(p_src_ctr).MEASURE6_CHAR;
1217 g_r_upd_deal.MEASURE7_CHAR(p_qtn_uctr) :=
1218 g_t_src_lines(p_src_ctr).MEASURE7_CHAR;
1219 g_r_upd_deal.MEASURE8_CHAR(p_qtn_uctr) :=
1220 g_t_src_lines(p_src_ctr).MEASURE8_CHAR;
1221 g_r_upd_deal.MEASURE9_CHAR(p_qtn_uctr) :=
1222 g_t_src_lines(p_src_ctr).MEASURE9_CHAR;
1223 g_r_upd_deal.MEASURE10_CHAR(p_qtn_uctr) :=
1224 g_t_src_lines(p_src_ctr).MEASURE10_CHAR;
1225
1226 exception
1227 when OTHERS then
1228 log_debug('ERROR ASSIGNING VALUES TO UPDATE INTERFACE LINE RECORD');
1229 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1230 raise;
1231 end assign_upd_deal_lines;
1232
1233 procedure del_ins_deal_lines is
1234 begin
1235 g_r_ins_deal.SOURCE_REF_HDR_ID.delete;
1236 g_r_ins_deal.SOURCE_REF_LINE_ID.delete;
1237 g_r_ins_deal.SOURCE_REQ_LINE_NO.delete;
1238 g_r_ins_deal.SOURCE_ID.delete;
1239 g_r_ins_deal.ORG_ID.delete;
1240 g_r_ins_deal.ORG_SHORT_DESC.delete;
1241 g_r_ins_deal.ORG_LONG_DESC.delete;
1242 g_r_ins_deal.INVENTORY_ITEM_ID.delete;
1243 g_r_ins_deal.INVENTORY_ITEM_SHORT_DESC.delete;
1244 g_r_ins_deal.INVENTORY_ITEM_LONG_DESC.delete;
1245 g_r_ins_deal.ITEM_TYPE_CODE.delete;
1246 g_r_ins_deal.TOP_MDL_SRC_LINE_ID.delete;
1247 g_r_ins_deal.PAYMENT_TERM_ID.delete;
1248 g_r_ins_deal.PAYMENT_TERM_SHORT_DESC.delete;
1249 g_r_ins_deal.PAYMENT_TERM_LONG_DESC.delete;
1250 g_r_ins_deal.UOM_CODE.delete;
1251 g_r_ins_deal.UOM_SHORT_DESC.delete;
1252 g_r_ins_deal.CURRENCY_CODE.delete;
1253 g_r_ins_deal.CURRENCY_SHORT_DESC.delete;
1254 g_r_ins_deal.ORDERED_QTY.delete;
1255 g_r_ins_deal.LIST_PRICE.delete;
1256 g_r_ins_deal.PROPOSED_PRICE.delete;
1257 g_r_ins_deal.REVISED_OQ.delete;
1258 g_r_ins_deal.COMPETITOR_NAME.delete;
1259 g_r_ins_deal.COMPETITOR_PRICE.delete;
1260 g_r_ins_deal.COMMENTS.delete;
1261 g_r_ins_deal.ADDITIONAL_INFO.delete;
1262 g_r_ins_deal.SHIP_METHOD_CODE.delete;
1263 g_r_ins_deal.SHIP_METHOD_SHORT_DESC.delete;
1264 g_r_ins_deal.SHIP_METHOD_LONG_DESC.delete;
1265 g_r_ins_deal.FREIGHT_CHARGES.delete;
1266 g_r_ins_deal.GEOGRAPHY_ID.delete;
1267 g_r_ins_deal.GEOGRAPHY_SHORT_DESC.delete;
1268 g_r_ins_deal.GEOGRAPHY_LONG_DESC.delete;
1269 g_r_ins_deal.MEASURE1_NUMBER.delete;
1270 g_r_ins_deal.MEASURE2_NUMBER.delete;
1271 g_r_ins_deal.MEASURE3_NUMBER.delete;
1272 g_r_ins_deal.MEASURE4_NUMBER.delete;
1273 g_r_ins_deal.MEASURE5_NUMBER.delete;
1274 g_r_ins_deal.MEASURE6_NUMBER.delete;
1275 g_r_ins_deal.MEASURE7_NUMBER.delete;
1276 g_r_ins_deal.MEASURE8_NUMBER.delete;
1277 g_r_ins_deal.MEASURE9_NUMBER.delete;
1278 g_r_ins_deal.MEASURE10_NUMBER.delete;
1279 g_r_ins_deal.MEASURE1_CHAR.delete;
1280 g_r_ins_deal.MEASURE2_CHAR.delete;
1281 g_r_ins_deal.MEASURE3_CHAR.delete;
1282 g_r_ins_deal.MEASURE4_CHAR.delete;
1283 g_r_ins_deal.MEASURE5_CHAR.delete;
1284 g_r_ins_deal.MEASURE6_CHAR.delete;
1285 g_r_ins_deal.MEASURE7_CHAR.delete;
1286 g_r_ins_deal.MEASURE8_CHAR.delete;
1287 g_r_ins_deal.MEASURE9_CHAR.delete;
1288 g_r_ins_deal.MEASURE10_CHAR.delete;
1289
1290 exception
1291 when OTHERS then
1292 log_debug('ERROR IN CLEARING INTERFACE LINE RECORD');
1293 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1294 raise;
1295 end del_ins_deal_lines;
1296
1297 procedure del_upd_deal_lines is
1298 begin
1299 g_r_upd_deal.PN_REQ_INTERFACE_LINE_ID.delete;
1300 g_r_upd_deal.SOURCE_REF_HDR_ID.delete;
1301 g_r_upd_deal.SOURCE_REF_LINE_ID.delete;
1302 g_r_upd_deal.SOURCE_REQ_LINE_NO.delete;
1303 g_r_upd_deal.SOURCE_ID.delete;
1304 g_r_upd_deal.ORG_ID.delete;
1305 g_r_upd_deal.ORG_SHORT_DESC.delete;
1306 g_r_upd_deal.ORG_LONG_DESC.delete;
1307 g_r_upd_deal.INVENTORY_ITEM_ID.delete;
1308 g_r_upd_deal.INVENTORY_ITEM_SHORT_DESC.delete;
1309 g_r_upd_deal.INVENTORY_ITEM_LONG_DESC.delete;
1310 g_r_upd_deal.ITEM_TYPE_CODE.delete;
1311 g_r_upd_deal.TOP_MDL_SRC_LINE_ID.delete;
1312 g_r_upd_deal.PAYMENT_TERM_ID.delete;
1313 g_r_upd_deal.PAYMENT_TERM_SHORT_DESC.delete;
1314 g_r_upd_deal.PAYMENT_TERM_LONG_DESC.delete;
1315 g_r_upd_deal.UOM_CODE.delete;
1316 g_r_upd_deal.UOM_SHORT_DESC.delete;
1317 g_r_upd_deal.CURRENCY_CODE.delete;
1318 g_r_upd_deal.CURRENCY_SHORT_DESC.delete;
1319 g_r_upd_deal.ORDERED_QTY.delete;
1320 g_r_upd_deal.LIST_PRICE.delete;
1321 g_r_upd_deal.PROPOSED_PRICE.delete;
1322 g_r_upd_deal.REVISED_OQ.delete;
1323 g_r_upd_deal.COMPETITOR_NAME.delete;
1324 g_r_upd_deal.COMPETITOR_PRICE.delete;
1325 g_r_upd_deal.COMMENTS.delete;
1326 g_r_upd_deal.ADDITIONAL_INFO.delete;
1327 g_r_upd_deal.SHIP_METHOD_CODE.delete;
1328 g_r_upd_deal.SHIP_METHOD_SHORT_DESC.delete;
1329 g_r_upd_deal.SHIP_METHOD_LONG_DESC.delete;
1330 g_r_upd_deal.FREIGHT_CHARGES.delete;
1331 g_r_upd_deal.GEOGRAPHY_ID.delete;
1332 g_r_upd_deal.GEOGRAPHY_SHORT_DESC.delete;
1333 g_r_upd_deal.GEOGRAPHY_LONG_DESC.delete;
1334 g_r_upd_deal.MEASURE1_NUMBER.delete;
1335 g_r_upd_deal.MEASURE2_NUMBER.delete;
1336 g_r_upd_deal.MEASURE3_NUMBER.delete;
1337 g_r_upd_deal.MEASURE4_NUMBER.delete;
1338 g_r_upd_deal.MEASURE5_NUMBER.delete;
1339 g_r_upd_deal.MEASURE6_NUMBER.delete;
1340 g_r_upd_deal.MEASURE7_NUMBER.delete;
1341 g_r_upd_deal.MEASURE8_NUMBER.delete;
1342 g_r_upd_deal.MEASURE9_NUMBER.delete;
1343 g_r_upd_deal.MEASURE10_NUMBER.delete;
1344 g_r_upd_deal.MEASURE1_CHAR.delete;
1345 g_r_upd_deal.MEASURE2_CHAR.delete;
1346 g_r_upd_deal.MEASURE3_CHAR.delete;
1347 g_r_upd_deal.MEASURE4_CHAR.delete;
1348 g_r_upd_deal.MEASURE5_CHAR.delete;
1349 g_r_upd_deal.MEASURE6_CHAR.delete;
1350 g_r_upd_deal.MEASURE7_CHAR.delete;
1351 g_r_upd_deal.MEASURE8_CHAR.delete;
1352 g_r_upd_deal.MEASURE9_CHAR.delete;
1353 g_r_upd_deal.MEASURE10_CHAR.delete;
1354
1355 exception
1356 when OTHERS then
1357 log_debug('ERROR IN CLEARING INTERFACE LINE RECORD');
1358 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1359 raise;
1360 end del_upd_deal_lines;
1361
1362 procedure insert_deal_lines(
1363 p_header_id in number
1364 ) is
1365 begin
1366 log_debug('Inserting deal interface lines ....');
1367 forall i in g_r_ins_deal.SOURCE_REF_HDR_ID.first..
1368 g_r_ins_deal.SOURCE_REF_HDR_ID.last
1369 insert into qpr_pn_int_lines(PN_INT_LINE_ID,
1370 -- PN_INT_HEADER_ID,
1371 SOURCE_REF_HDR_ID,
1372 SOURCE_REF_LINE_ID,
1373 SOURCE_REQUEST_LINE_NUMBER,
1374 SOURCE_ID,
1375 ORG_ID,
1376 ORG_SHORT_DESC,
1377 ORG_LONG_DESC,
1378 INVENTORY_ITEM_ID,
1379 INVENTORY_ITEM_SHORT_DESC,
1380 INVENTORY_ITEM_LONG_DESC,
1381 ITEM_TYPE_CODE,
1382 TOP_MDL_SRC_LINE_ID,
1383 PAYMENT_TERM_ID,
1384 PAYMENT_TERM_SHORT_DESC,
1385 PAYMENT_TERM_LONG_DESC,
1386 UOM_CODE,
1387 UOM_SHORT_DESC,
1388 CURRENCY_CODE,
1389 CURRENCY_SHORT_DESC,
1390 ORDERED_QTY,
1391 LIST_PRICE,
1392 PROPOSED_PRICE,
1393 REVISED_OQ,
1394 PN_REQ_LINE_STATUS_FLAG,
1395 COMPETITOR_NAME,
1396 COMPETITOR_PRICE,
1397 COMMENTS,
1398 ADDITIONAL_INFORMATION,
1399 SHIP_METHOD_CODE,
1400 SHIP_METHOD_SHORT_DESC,
1401 SHIP_METHOD_LONG_DESC,
1402 FREIGHT_CHARGES,
1403 GEOGRAPHY_ID,
1404 GEOGRAPHY_SHORT_DESC,
1405 GEOGRAPHY_LONG_DESC,
1406 MEASURE1_NUMBER,
1407 MEASURE2_NUMBER,
1408 MEASURE3_NUMBER,
1409 MEASURE4_NUMBER,
1410 MEASURE5_NUMBER,
1411 MEASURE6_NUMBER,
1412 MEASURE7_NUMBER,
1413 MEASURE8_NUMBER,
1414 MEASURE9_NUMBER,
1415 MEASURE10_NUMBER,
1416 MEASURE1_CHAR,
1417 MEASURE2_CHAR,
1418 MEASURE3_CHAR,
1419 MEASURE4_CHAR,
1420 MEASURE5_CHAR,
1421 MEASURE6_CHAR,
1422 MEASURE7_CHAR,
1423 MEASURE8_CHAR,
1424 MEASURE9_CHAR,
1425 MEASURE10_CHAR,
1426 CREATION_DATE,
1427 CREATED_BY,
1428 LAST_UPDATE_DATE,
1429 LAST_UPDATED_BY,
1430 LAST_UPDATE_LOGIN,
1431 PROGRAM_APPLICATION_ID,
1432 PROGRAM_ID ,
1433 REQUEST_ID)
1434 values(qpr_pn_int_lines_s.nextval,
1435 -- p_int_hdr_id,
1436 g_r_ins_deal.SOURCE_REF_HDR_ID(i) ,
1437 g_r_ins_deal.SOURCE_REF_LINE_ID(i) ,
1438 g_r_ins_deal.SOURCE_REQ_LINE_NO(i),
1439 g_r_ins_deal.SOURCE_ID(i) ,
1440 g_r_ins_deal.ORG_ID(i) ,
1441 g_r_ins_deal.ORG_SHORT_DESC(i) ,
1442 g_r_ins_deal.ORG_LONG_DESC(i) ,
1443 g_r_ins_deal.INVENTORY_ITEM_ID(i) ,
1444 g_r_ins_deal.INVENTORY_ITEM_SHORT_DESC(i) ,
1445 g_r_ins_deal.INVENTORY_ITEM_LONG_DESC(i) ,
1446 g_r_ins_deal.ITEM_TYPE_CODE(i) ,
1447 g_r_ins_deal.TOP_MDL_SRC_LINE_ID(i) ,
1448 g_r_ins_deal.PAYMENT_TERM_ID(i) ,
1449 g_r_ins_deal.PAYMENT_TERM_SHORT_DESC(i) ,
1450 g_r_ins_deal.PAYMENT_TERM_LONG_DESC(i) ,
1451 g_r_ins_deal.UOM_CODE(i) ,
1452 g_r_ins_deal.UOM_SHORT_DESC(i) ,
1453 g_r_ins_deal.CURRENCY_CODE(i) ,
1454 g_r_ins_deal.CURRENCY_SHORT_DESC(i) ,
1455 g_r_ins_deal.ORDERED_QTY(i) ,
1456 g_r_ins_deal.LIST_PRICE(i) ,
1457 g_r_ins_deal.PROPOSED_PRICE(i) ,
1458 g_r_ins_deal.REVISED_OQ(i) , 'I',
1459 g_r_ins_deal.COMPETITOR_NAME(i) ,
1460 g_r_ins_deal.COMPETITOR_PRICE(i) ,
1461 g_r_ins_deal.COMMENTS(i),
1462 g_r_ins_deal.ADDITIONAL_INFO(i),
1463 g_r_ins_deal.SHIP_METHOD_CODE(i),
1464 g_r_ins_deal.SHIP_METHOD_SHORT_DESC(i),
1465 g_r_ins_deal.SHIP_METHOD_LONG_DESC(i),
1466 g_r_ins_deal.FREIGHT_CHARGES(i),
1467 g_r_ins_deal.GEOGRAPHY_ID(i),
1468 g_r_ins_deal.GEOGRAPHY_SHORT_DESC(i),
1469 g_r_ins_deal.GEOGRAPHY_LONG_DESC(i),
1470 g_r_ins_deal.MEASURE1_NUMBER(i),
1471 g_r_ins_deal.MEASURE2_NUMBER(i),
1472 g_r_ins_deal.MEASURE3_NUMBER(i),
1473 g_r_ins_deal.MEASURE4_NUMBER(i),
1474 g_r_ins_deal.MEASURE5_NUMBER(i),
1475 g_r_ins_deal.MEASURE6_NUMBER(i),
1476 g_r_ins_deal.MEASURE7_NUMBER(i),
1477 g_r_ins_deal.MEASURE8_NUMBER(i),
1478 g_r_ins_deal.MEASURE9_NUMBER(i),
1479 g_r_ins_deal.MEASURE10_NUMBER(i),
1480 g_r_ins_deal.MEASURE1_CHAR(i),
1481 g_r_ins_deal.MEASURE2_CHAR(i),
1482 g_r_ins_deal.MEASURE3_CHAR(i),
1483 g_r_ins_deal.MEASURE4_CHAR(i),
1484 g_r_ins_deal.MEASURE5_CHAR(i),
1485 g_r_ins_deal.MEASURE6_CHAR(i),
1486 g_r_ins_deal.MEASURE7_CHAR(i),
1487 g_r_ins_deal.MEASURE8_CHAR(i),
1488 g_r_ins_deal.MEASURE9_CHAR(i),
1489 g_r_ins_deal.MEASURE10_CHAR(i),
1490 g_sys_date,
1491 g_user_id,
1492 g_sys_date,
1493 g_user_id,
1494 g_login_id, g_prg_appl_id , g_prg_id, g_request_id);
1495 del_ins_deal_lines;
1496 exception
1497 when OTHERS then
1498 log_debug('ERROR INSERTING VALUES TO DEAL INTERFACE LINE');
1499 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1500 raise;
1501 end insert_deal_lines;
1502
1503 procedure update_deal_lines is
1504 begin
1505 log_debug('Updating deal interface lines ....');
1506 forall i in g_r_upd_deal.SOURCE_REF_HDR_ID.first..
1507 g_r_upd_deal.SOURCE_REF_HDR_ID.last
1508 update qpr_pn_int_lines set
1509 SOURCE_REQUEST_LINE_NUMBER = g_r_upd_deal.SOURCE_REQ_LINE_NO(i),
1510 SOURCE_ID = g_r_upd_deal.SOURCE_ID(i),
1511 ORG_ID = g_r_upd_deal.ORG_ID(i),
1512 ORG_SHORT_DESC = g_r_upd_deal.ORG_SHORT_DESC(i) ,
1513 ORG_LONG_DESC = g_r_upd_deal.ORG_LONG_DESC(i) ,
1514 INVENTORY_ITEM_ID = g_r_upd_deal.INVENTORY_ITEM_ID(i) ,
1515 INVENTORY_ITEM_SHORT_DESC= g_r_upd_deal.INVENTORY_ITEM_SHORT_DESC(i),
1516 INVENTORY_ITEM_LONG_DESC = g_r_upd_deal.INVENTORY_ITEM_LONG_DESC(i),
1517 ITEM_TYPE_CODE = g_r_upd_deal.ITEM_TYPE_CODE(i) ,
1518 TOP_MDL_SRC_LINE_ID = g_r_upd_deal.TOP_MDL_SRC_LINE_ID(i) ,
1519 PAYMENT_TERM_ID = g_r_upd_deal.PAYMENT_TERM_ID(i) ,
1520 PAYMENT_TERM_SHORT_DESC = g_r_upd_deal.PAYMENT_TERM_SHORT_DESC(i),
1521 PAYMENT_TERM_LONG_DESC = g_r_upd_deal.PAYMENT_TERM_LONG_DESC(i),
1522 UOM_CODE = g_r_upd_deal.UOM_CODE(i) ,
1523 UOM_SHORT_DESC = g_r_upd_deal.UOM_SHORT_DESC(i) ,
1524 CURRENCY_CODE = g_r_upd_deal.CURRENCY_CODE(i) ,
1525 CURRENCY_SHORT_DESC = g_r_upd_deal.CURRENCY_SHORT_DESC(i) ,
1526 ORDERED_QTY = g_r_upd_deal.ORDERED_QTY(i) ,
1527 LIST_PRICE = g_r_upd_deal.LIST_PRICE(i) ,
1528 PROPOSED_PRICE = g_r_upd_deal.PROPOSED_PRICE(i) ,
1529 REVISED_OQ = g_r_upd_deal.REVISED_OQ(i) ,
1530 COMPETITOR_NAME = g_r_upd_deal.COMPETITOR_NAME(i),
1531 COMPETITOR_PRICE = g_r_upd_deal.COMPETITOR_PRICE(i) ,
1532 COMMENTS = g_r_upd_deal.COMMENTS(i) ,
1533 ADDITIONAL_INFORMATION = g_r_upd_deal.ADDITIONAL_INFO(i),
1534 SHIP_METHOD_CODE = g_r_upd_deal.SHIP_METHOD_CODE(i),
1535 SHIP_METHOD_SHORT_DESC = g_r_upd_deal.SHIP_METHOD_SHORT_DESC(i),
1536 SHIP_METHOD_LONG_DESC = g_r_upd_deal.SHIP_METHOD_LONG_DESC(i),
1537 FREIGHT_CHARGES = g_r_upd_deal.FREIGHT_CHARGES(i),
1538 GEOGRAPHY_ID = g_r_upd_deal.GEOGRAPHY_ID(i),
1539 GEOGRAPHY_SHORT_DESC = g_r_upd_deal.GEOGRAPHY_SHORT_DESC(i),
1540 GEOGRAPHY_LONG_DESC = g_r_upd_deal.GEOGRAPHY_LONG_DESC(i),
1541 MEASURE1_NUMBER = g_r_upd_deal.MEASURE1_NUMBER(i),
1542 MEASURE2_NUMBER = g_r_upd_deal.MEASURE2_NUMBER(i),
1543 MEASURE3_NUMBER = g_r_upd_deal.MEASURE3_NUMBER(i),
1544 MEASURE4_NUMBER = g_r_upd_deal.MEASURE4_NUMBER(i),
1545 MEASURE5_NUMBER = g_r_upd_deal.MEASURE5_NUMBER(i),
1546 MEASURE6_NUMBER = g_r_upd_deal.MEASURE6_NUMBER(i),
1547 MEASURE7_NUMBER = g_r_upd_deal.MEASURE7_NUMBER(i),
1548 MEASURE8_NUMBER = g_r_upd_deal.MEASURE8_NUMBER(i),
1549 MEASURE9_NUMBER = g_r_upd_deal.MEASURE9_NUMBER(i),
1550 MEASURE10_NUMBER = g_r_upd_deal.MEASURE10_NUMBER(i),
1551 MEASURE1_CHAR = g_r_upd_deal.MEASURE1_CHAR(i),
1552 MEASURE2_CHAR = g_r_upd_deal.MEASURE2_CHAR(i),
1553 MEASURE3_CHAR = g_r_upd_deal.MEASURE3_CHAR(i),
1554 MEASURE4_CHAR = g_r_upd_deal.MEASURE4_CHAR(i),
1555 MEASURE5_CHAR = g_r_upd_deal.MEASURE5_CHAR(i),
1556 MEASURE6_CHAR = g_r_upd_deal.MEASURE6_CHAR(i),
1557 MEASURE7_CHAR = g_r_upd_deal.MEASURE7_CHAR(i),
1558 MEASURE8_CHAR = g_r_upd_deal.MEASURE8_CHAR(i),
1559 MEASURE9_CHAR = g_r_upd_deal.MEASURE9_CHAR(i),
1560 MEASURE10_CHAR = g_r_upd_deal.MEASURE10_CHAR(i),
1561 LAST_UPDATE_DATE = g_sys_date,
1562 LAST_UPDATED_BY = g_user_id,
1563 LAST_UPDATE_LOGIN = g_login_id,
1564 PROGRAM_APPLICATION_ID = g_prg_appl_id,
1565 PROGRAM_ID = g_prg_id,
1566 REQUEST_ID = g_request_id
1567 where PN_INT_LINE_ID = g_r_upd_deal.PN_REQ_INTERFACE_LINE_ID(i);
1568
1569 del_upd_deal_lines;
1570 exception
1571 when OTHERS then
1572 log_debug('ERROR IN UPDATING DEAL INTERFACE LINES');
1573 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1574 raise;
1575 end update_deal_lines;
1576
1577 procedure insert_update_deal_lines(
1578 p_header_id in number,
1579 p_sql in varchar2) is
1580 b_update boolean := false;
1581 l_req_int_hdr_id number;
1582 l_req_int_line_id number;
1583 l_rows number := 1000;
1584 l_qt_ictr number := 0;
1585 l_qt_uctr number := 0;
1586 s_status varchar2(1);
1587 s_sql varchar2(20000) := '';
1588 s_src_tbl varchar2(30);
1589 t_src_line_id num_type;
1590 t_int_line_id num_type;
1591 t_status char240_type;
1592 c_deal_line SYS_REFCURSOR;
1593 begin
1594 log_debug('In Deal Line method..');
1595
1596 select l.SOURCE_REF_LINE_ID, l.PN_INT_LINE_ID, l.PN_REQ_LINE_STATUS_FLAG
1597 bulk collect into t_src_line_id, t_int_line_id, t_status
1598 from qpr_pn_int_lines l, qpr_pn_int_headers h
1599 where h.source_ref_header_id = p_header_id
1600 and h.instance_id = g_instance_id
1601 and h.source_id = g_source_id
1602 and h.source_ref_header_short_desc = g_quote_hdr_sd
1603 and h.source_id = l.source_id
1604 and h.source_ref_header_id = l.source_ref_hdr_id
1605 order by l.source_ref_line_id;
1606
1607 -- this is needed for aso quotes only. To clear the package variable
1608 -- set during call. Only after this will the ui line number will generate
1609 -- proper line no.
1610 if g_meas_type = ASO_MEAS_TYPE_DEALINT then
1611 s_sql:= 'begin aso_line_num_int.RESET_LINE_NUM'||
1612 qpr_sr_util.get_dblink(g_instance_id) || '; end;';
1613 execute immediate s_sql;
1614 end if;
1615
1616 open c_deal_line for p_sql using p_header_id;
1617 loop
1618 fetch c_deal_line bulk collect into g_t_src_lines limit l_rows;
1619 exit when g_t_src_lines.count = 0;
1620 for i in g_t_src_lines.first..g_t_src_lines.last loop
1621 b_update := false;
1622 if t_src_line_id.count= 0 then
1623 l_qt_ictr := l_qt_ictr + 1;
1624 assign_ins_deal_lines(l_qt_ictr, i);
1625 else
1626 for j in t_src_line_id.first..t_src_line_id.last loop
1627 if t_src_line_id.exists(j) then
1628 if t_src_line_id(j) = g_t_src_lines(i).source_ref_line_id then
1629 s_status := t_status(j);
1630 b_update := true;
1631 l_req_int_line_id := t_int_line_id(j);
1632 t_src_line_id.delete(j);
1633 t_int_line_id.delete(j);
1634 t_status.delete(j);
1635 exit;
1636 end if;
1637 end if;
1638 end loop;
1639 if b_update = true then
1640 if s_status <> 'P' then
1641 l_qt_uctr := l_qt_uctr + 1;
1642 assign_upd_deal_lines(l_qt_uctr, i, l_req_int_line_id);
1643 else
1644 log_debug('Status of line ' ||g_t_src_lines(i).source_ref_line_id || ' does not permit update.');
1645 end if;
1646 else
1647 l_qt_ictr := l_qt_ictr + 1;
1648 assign_ins_deal_lines(l_qt_ictr, i);
1649 end if;
1650 end if;
1651 end loop; -- all src lines loop
1652 if g_r_ins_deal.SOURCE_REF_LINE_ID.count > 0 then
1653 insert_deal_lines( p_header_id);
1654 end if;
1655 if g_r_upd_deal.SOURCE_REF_LINE_ID.count > 0 then
1656 update_deal_lines;
1657 end if;
1658 g_t_src_lines.delete;
1659 end loop;
1660 -- end if;
1661 exception
1662 when OTHERS then
1663 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1664 raise;
1665 end insert_update_deal_lines;
1666
1667 procedure insert_deal_adjs(p_header_id in number, p_sql in varchar2) is
1668
1669 l_ctr number:= 0;
1670 l_rows number := 1000;
1671 l_status varchar2(1);
1672 b_first boolean := true;
1673 t_pr_adj_val qpr_deal_adj_type;
1674 t_adj_rec qpr_src_qtn_adj_type;
1675 c_adj SYS_REFCURSOR;
1676
1677 begin
1678 log_debug('In price adjustments...');
1679
1680 begin
1681 select 1 into l_status
1682 from qpr_pn_int_headers h
1683 where h.source_ref_header_id = p_header_id
1684 and h.instance_id = g_instance_id
1685 and h.source_id = g_source_id
1686 and h.pn_req_header_status_flag <> 'P';
1687 exception
1688 when no_data_found then
1689 log_debug('Quote status does not permit modifications');
1690 return;
1691 end;
1692
1693
1694 open c_adj for p_sql using p_header_id;
1695 loop
1696 fetch c_adj bulk collect into t_adj_rec limit l_rows;
1697 exit when t_adj_rec.count = 0;
1698
1699 if b_first then
1700 b_first := false;
1701
1702 delete qpr_pn_int_pr_adjs where source_ref_hdr_id = p_header_id
1703 and source_id = g_source_id
1704 and erosion_type = t_adj_rec(1).EROSION_TYPE;
1705 end if;
1706
1707 for k in t_adj_rec.first..t_adj_rec.last loop
1708 t_pr_adj_val.SOURCE_ID(l_ctr) := t_adj_rec(k).SOURCE_ID;
1709 t_pr_adj_val.EROSION_TYPE(l_ctr) := t_adj_rec(k).EROSION_TYPE;
1710 t_pr_adj_val.EROSION_NAME(l_ctr) := t_adj_rec(k).EROSION_NAME;
1711 t_pr_adj_val.EROSION_DESC(l_ctr) := t_adj_rec(k).EROSION_DESC;
1712 t_pr_adj_val.EROSION_PER_UNIT(l_ctr) :=
1713 t_adj_rec(k).EROSION_PER_UNIT;
1714 t_pr_adj_val.EROSION_AMOUNT(l_ctr) := t_adj_rec(k).EROSION_AMOUNT;
1715 t_pr_adj_val.SRC_REF_HDR_ID(l_ctr) := t_adj_rec(k).SOURCE_REF_HDR_ID;
1716 t_pr_adj_val.SRC_REF_LINE_ID(l_ctr) := t_adj_rec(k).SOURCE_REF_LINE_ID;
1717 l_ctr := l_ctr + 1;
1718 end loop;
1719
1720
1721 forall i in t_pr_adj_val.src_ref_line_id.first..
1722 t_pr_adj_val.src_ref_line_id.last
1723 insert into qpr_pn_int_pr_adjs(pn_int_pr_adj_id,
1724 source_ref_hdr_id,
1725 source_ref_line_id,
1726 source_id,
1727 erosion_type,
1728 erosion_name,
1729 erosion_desc,
1730 erosion_per_unit,
1731 erosion_amount,
1732 creation_date,
1733 created_by,
1734 last_update_date,
1735 last_updated_by,
1736 last_update_login,
1737 program_application_id,
1738 program_id,
1739 request_id)
1740 values(qpr_pn_int_pr_adjs_s.nextval,
1741 t_pr_adj_val.SRC_REF_HDR_ID(i),
1742 t_pr_adj_val.src_ref_line_id(i),
1743 t_pr_adj_val.source_id(i),
1744 t_pr_adj_val.erosion_type(i),
1745 t_pr_adj_val.erosion_name(i),
1746 t_pr_adj_val.erosion_desc(i),
1747 t_pr_adj_val.erosion_per_unit(i),
1748 t_pr_adj_val.erosion_amount(i),
1749 g_sys_date,
1750 g_user_id,
1751 g_sys_date,
1752 g_user_id,
1753 g_login_id, g_prg_appl_id , g_prg_id, g_request_id);
1754
1755 t_pr_adj_val.source_id.delete;
1756 t_pr_adj_val.EROSION_TYPE.delete;
1757 t_pr_adj_val.EROSION_NAME.delete;
1758 t_pr_adj_val.EROSION_DESC.delete;
1759 t_pr_adj_val.EROSION_PER_UNIT.delete;
1760 t_pr_adj_val.EROSION_AMOUNT.delete;
1761 t_pr_adj_val.src_ref_hdr_id.delete;
1762 t_pr_adj_val.src_ref_line_id.delete;
1763
1764 t_adj_rec.delete;
1765 end loop;
1766 exception
1767 when OTHERS then
1768 log_debug(dbms_utility.format_error_backtrace);
1769 raise;
1770 end insert_deal_adjs;
1771
1772 procedure fill_measure_data(errbuf out nocopy varchar2,
1773 retcode out nocopy varchar2,
1774 p_instance_id in number,
1775 p_date_from in varchar2,
1776 p_date_to in varchar2,
1777 p_meas_type in varchar2,
1778 p_header_id in number default 0) is
1779
1780 db_link varchar2(150) := '';
1781 src_table varchar2(200);
1782
1783 s_sql varchar2(30000) := '';
1784
1785 l_rec_count number :=0;
1786 l_start_time number;
1787 l_end_time number;
1788 l_ret_code number;
1789 l_src_count number;
1790 l_inst_id number;
1791 l_req_int_hdr_id number;
1792 l_inst_type varchar2(30);
1793
1794 cursor c_src_cols(m_type varchar2,m_ins_id number,m_src_tname varchar2, l_inst_type varchar2) is
1795 select distinct nvl(USER_SRC_COL_NAME,SRC_COL_NAME) SRC_COL_NAME,
1796 nvl(USER_TGT_COL_NAME, TGT_COL_NAME) TGT_COL_NAME
1797 from QPR_MEASURE_SOURCES
1798 where MEASURE_TYPE_CODE = m_type
1799 and INSTANCE_ID = m_ins_id
1800 and INSTANCE_TYPE = l_inst_type
1801 and nvl(user_src_tbl_name, src_tbl_name ) = m_src_tname
1802 order by TGT_COL_NAME;
1803
1804 cursor c_srcs(m_type varchar2, m_ins_id number, l_inst_type varchar2)is
1805 select distinct nvl(USER_SRC_TBL_NAME, SRC_TBL_NAME) SRC_TBL_NAME,
1806 TGT_TBL_NAME
1807 from QPR_MEASURE_SOURCES
1808 where MEASURE_TYPE_CODE = m_type
1809 and INSTANCE_ID = m_ins_id
1810 and INSTANCE_TYPE = l_inst_type
1811 order by TGT_TBL_NAME;
1812 begin
1813 log_debug('Starting...');
1814 select hsecs into l_start_time from v$timer;
1815 log_debug('Start time :'||
1816 to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
1817 db_link := qpr_sr_util.get_dblink(p_instance_id);
1818
1819 g_instance_id := p_instance_id;
1820 g_meas_type := p_meas_type;
1821
1822 fnd_profile.get('CONC_REQUEST_ID', g_request_id);
1823 g_sys_date := sysdate;
1824 g_user_id := fnd_global.user_id;
1825 g_login_id := fnd_global.conc_login_id;
1826 g_prg_appl_id := fnd_global.prog_appl_id;
1827 g_prg_id := fnd_global.conc_program_id;
1828
1829 if not qpr_sr_util.dm_parameters_ok then
1830 retcode:= 2;
1831 FND_MESSAGE.Set_Name ('QPR','QPR_NULL_PARAMETERS');
1832 FND_MSG_PUB.Add;
1833 log_debug('One or more mandatory parameters are not filled');
1834 return;
1835 end if;
1836
1837 select instance_type into l_inst_type
1838 from qpr_instances
1839 where instance_id = p_instance_id;
1840
1841 select count(*) into l_src_count
1842 from QPR_MEASURE_SOURCES
1843 where INSTANCE_ID = p_instance_id
1844 and MEASURE_TYPE_CODE = p_meas_type;
1845
1846 if l_src_count > 0 then
1847 l_inst_id := p_instance_id;
1848 else
1849 l_inst_id := SEEDED_INSTANCE_ID;
1850 end if;
1851
1852 for r_usr_src in c_srcs(p_meas_type, l_inst_id, l_inst_type) loop
1853 log_debug('Fetching source columns ...');
1854 open c_src_cols(p_meas_type, l_inst_id,
1855 r_usr_src.SRC_TBL_NAME, l_inst_type);
1856 fetch c_src_cols bulk collect into g_src_cols, g_trg_cols;
1857 close c_src_cols;
1858
1859 src_table := r_usr_src.SRC_TBL_NAME || db_link ;
1860
1861 if p_meas_type = OM_MEAS_TYPE_DEALINT or
1862 p_meas_type = ASO_MEAS_TYPE_DEALINT then
1863 s_sql := get_deal_sql(src_table, r_usr_src.TGT_TBL_NAME);
1864 if r_usr_src.TGT_TBL_NAME = DEAL_HEADER_TBL then
1865 s_sql := s_sql || ' and rownum < 2' ;
1866 l_req_int_hdr_id := insert_update_deal_hdr(
1867 p_header_id,
1868 s_sql);
1869 elsif r_usr_src.TGT_TBL_NAME = DEAL_LINE_TBL then
1870 -- since the sources are sorted by target tbl name the lines will
1871 -- come after header only
1872 insert_update_deal_lines(p_header_id,s_sql);
1873 else
1874 insert_deal_adjs(p_header_id, s_sql);
1875 end if;
1876 else
1877 s_sql := get_select_meas_sql(src_table, p_meas_type);
1878 insert_update_meas_data(p_date_from, p_date_to, s_sql);
1879 end if;
1880 end loop;
1881 select hsecs into l_end_time from v$timer;
1882 log_debug('End time :'|| to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
1883 log_debug('Time taken for loading(sec):' ||(l_end_time - l_start_time)/100);
1884 exception
1885 when OTHERS then
1886 retcode := 2;
1887 errbuf := 'ERROR: ' || substr(sqlerrm, 1, 1000);
1888 log_debug(substr(sqlerrm, 1, 1000));
1889 log_debug('CANNOT POPULATE FACT DATA');
1890 end fill_measure_data;
1891
1892 procedure load_quote_data(errbuf out nocopy varchar2,
1893 retcode out nocopy varchar2,
1894 p_instance_id in number,
1895 p_src_choice in number default 1,
1896 p_quote_number in number default 0,
1897 p_quote_version in number default 0,
1898 p_order_type in varchar2 default null) is
1899 l_dummy number;
1900 begin
1901 begin
1902 select request_header_id into l_dummy
1903 from qpr_pn_request_hdrs_b
1904 where instance_id = p_instance_id
1905 and source_id = decode(p_src_choice,1, 660, 2, 697, p_src_choice)
1906 and source_ref_hdr_short_desc = (p_quote_number || ' - Ver '|| p_quote_version)
1907 and nvl(request_status, 'ACTIVE') = 'ACTIVE'
1908 and nvl(simulation_flag, 'Y') = 'N'
1909 and rownum < 2;
1910
1911 retcode := 1;
1912 errbuf := 'Active Request ' || l_dummy || ' exist for this quote.';
1913 return;
1914 exception
1915 -- when no active requests are present then load quote --
1916 when NO_DATA_FOUND then
1917 load_quote_data_api(errbuf ,
1918 retcode ,
1919 p_instance_id ,
1920 p_src_choice ,
1921 null,
1922 p_quote_number ,
1923 p_quote_version ,
1924 p_order_type );
1925
1926 end;
1927 exception
1928 when OTHERS then
1929 retcode := 2;
1930 errbuf := 'ERROR: ' || substr(sqlerrm, 1, 1000);
1931 log_debug('ERROR IN LOADING QUOTE DATA');
1932 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1933 raise;
1934 end;
1935
1936 procedure load_quote_data_api(errbuf out nocopy varchar2,
1937 retcode out nocopy varchar2,
1938 p_instance_id in number,
1939 p_src_choice in number default 1,
1940 p_quote_header_id in number default null,
1941 p_quote_number in number default 0,
1942 p_quote_version in number default 0,
1943 p_order_type in varchar2 default null) is
1944 p_header_id number;
1945 l_sql varchar2(1000);
1946 src_tbl_name varchar2(200);
1947 s_deal_type varchar2(30);
1948 l_inst_type varchar2(30);
1949 begin
1950
1951 g_origin := p_src_choice;
1952
1953 if p_instance_id is null then
1954 retcode := 2;
1955 FND_MESSAGE.Set_Name ('QPR','QPR_NULL_INSTANCE');
1956 FND_MSG_PUB.Add;
1957 errbuf := 'Instance Id cannot be null';
1958 return;
1959 end if;
1960
1961 if (p_src_choice = 1 or p_src_choice = 660 )then
1962 s_deal_type := OM_MEAS_TYPE_DEALINT;
1963 elsif (p_src_choice = 2 or p_src_choice = 697) then
1964 s_deal_type := ASO_MEAS_TYPE_DEALINT;
1965 else
1966 s_deal_type := p_src_choice;
1967 end if;
1968
1969 select distinct src_tbl_name into src_tbl_name
1970 from qpr_measure_sources
1971 where measure_type_code = s_deal_type
1972 and tgt_tbl_name = DEAL_HEADER_TBL
1973 and INSTANCE_TYPE = (select instance_type
1974 from qpr_instances
1975 where instance_id = p_instance_id);
1976 if p_quote_header_id is null then
1977 l_sql := 'select quote_header_id, quote_header_sd, source_id from '
1978 || src_tbl_name||qpr_sr_util.get_dblink(p_instance_id)
1979 || ' where quote_number = ' || p_quote_number
1980 || ' and quote_version = ' || p_quote_version ;
1981 if p_src_choice = 1 or p_src_choice = 660 then
1982 l_sql := l_sql || ' and order_type_name = ''' || p_order_type || '''';
1983 end if;
1984 l_sql := l_sql || ' and rownum < 2';
1985 else
1986 l_sql := 'select quote_header_id, quote_header_sd, source_id from '
1987 || src_tbl_name||qpr_sr_util.get_dblink(p_instance_id)
1988 || ' where quote_header_id = ' || p_quote_header_id;
1989 l_sql := l_sql || ' and rownum < 2';
1990 end if;
1991 log_debug('SQL: '||l_sql);
1992 execute immediate l_sql into p_header_id, g_quote_hdr_sd, g_source_id;
1993 if nvl(p_header_id, 0) = 0 then
1994 retcode := 2;
1995 FND_MESSAGE.Set_Name ('QPR','QPR_NO_QUOTE');
1996 FND_MSG_PUB.Add;
1997 log_debug('Quote does not exist. ');
1998 else
1999 log_debug('Header_id: ' || p_header_id);
2000 fill_measure_data(errbuf,retcode, p_instance_id , null, null,
2001 s_deal_type,p_header_id);
2002 end if;
2003
2004 exception
2005 WHEN NO_DATA_FOUND then
2006 retcode := 2;
2007 log_debug('Quote does not exist.');
2008 when OTHERS then
2009 retcode := 2;
2010 errbuf := 'ERROR: ' || substr(sqlerrm, 1, 1000);
2011 log_debug('ERROR IN LOADING QUOTE DATA');
2012 log_debug(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2013 raise;
2014 end load_quote_data_api;
2015
2016 function is_source_quote_changed(errbuf out nocopy varchar2,
2017 retcode out nocopy varchar2,
2018 p_instance_id in number,
2019 p_source_id in number,
2020 p_src_quote_header_id in number)
2021 return varchar2 is
2022 l_resp_cust qpr_pn_request_hdrs_b.customer_id%type;
2023 l_resp_sales_rep qpr_pn_request_hdrs_b.sales_rep_id%type;
2024 l_resp_sc qpr_pn_request_hdrs_b.sales_channel_code%type;
2025 l_request_hdr_id qpr_pn_request_hdrs_b.request_header_id%type;
2026 r_hdr qpr_pn_int_headers%rowtype;
2027 r_lines qpr_pn_int_lines%rowtype;
2028 l_hdr_matching boolean := false;
2029 l_line_matching boolean := false;
2030 l_org_id qpr_pn_lines.ORG_ID%type;
2031 l_item_id qpr_pn_lines.inventory_item_id%type;
2032 l_pt_id qpr_pn_lines.payment_term_id%type;
2033 l_shm_code qpr_pn_lines.ship_method_code%type;
2034 l_geo_id qpr_pn_lines.geography_id%type;
2035 l_uom_code qpr_pn_lines.uom_code%type;
2036 l_ord_qty qpr_pn_lines.ordered_qty%type;
2037 l_price qpr_pn_lines.proposed_price%type;
2038 l_curr_code qpr_pn_lines.currency_code%type;
2039 l_quote_changed varchar2(1);
2040 l_sql varchar2(10000);
2041 l_line_cnt number;
2042
2043 c_ref SYS_REFCURSOR;
2044
2045 function fetch_sql(p_tgt_tbl_name varchar2) return varchar2 is
2046 l_src_tbl varchar2(250);
2047 l_sql varchar2(10000);
2048
2049 cursor c_src_cols(m_src_tname varchar2) is
2050 select distinct nvl(USER_SRC_COL_NAME,SRC_COL_NAME) SRC_COL_NAME,
2051 nvl(USER_TGT_COL_NAME,TGT_COL_NAME) TGT_COL_NAME
2052 from QPR_MEASURE_SOURCES src, qpr_instances inst
2053 where src.instance_type = inst.instance_type
2054 and inst.instance_id = p_instance_id
2055 and src.measure_type_code = decode(p_source_id, 660, 'OM_DEALINT',
2056 697, 'ASO_DEALINT')
2057 and nvl(src.user_src_tbl_name, src.src_tbl_name ) = m_src_tname
2058 order by TGT_COL_NAME;
2059
2060 begin
2061 select distinct src_tbl_name into l_src_tbl
2062 from qpr_measure_sources src, qpr_instances inst
2063 where src.instance_type = inst.instance_type
2064 and inst.instance_id = p_instance_id
2065 and src.measure_type_code = decode(p_source_id, 660, 'OM_DEALINT',
2066 697, 'ASO_DEALINT')
2067 and tgt_tbl_name = p_tgt_tbl_name;
2068
2069 open c_src_cols(l_src_tbl);
2070 fetch c_src_cols bulk collect into g_src_cols, g_trg_cols;
2071 close c_src_cols;
2072
2073 l_src_tbl := l_src_tbl || qpr_sr_util.get_dblink(p_instance_id);
2074
2075 l_sql := get_deal_sql(l_src_tbl, p_tgt_tbl_name);
2076
2077 return l_sql;
2078 end fetch_sql;
2079
2080
2081 begin
2082 l_quote_changed := 'N';
2083 -- read request_header details
2084 begin
2085 select req.CUSTOMER_ID, req.SALES_REP_ID, req.SALES_CHANNEL_CODE,
2086 req.request_header_id
2087 into l_resp_cust, l_resp_sales_rep, l_resp_sc, l_request_hdr_id
2088 from qpr_pn_request_hdrs_b req
2089 where instance_id = p_instance_id
2090 and source_id = p_source_id
2091 and source_ref_hdr_id = p_src_quote_header_id
2092 and request_status = 'ACTIVE'
2093 and rownum < 2;
2094
2095 --get details from source ---
2096 l_sql := fetch_sql(DEAL_HEADER_TBL) ;
2097 l_sql := l_sql || ' and rownum < 2 ';
2098
2099 open c_ref for l_sql using p_src_quote_header_id;
2100 fetch c_ref into r_hdr;
2101 close c_ref;
2102
2103 if nvl(l_resp_cust,0) = nvl(r_hdr.CUSTOMER_ID,0) and
2104 nvl(l_resp_sales_rep,0) = nvl(r_hdr.SALES_REP_ID,0) and
2105 nvl(l_resp_sc, '*') = nvl(r_hdr.SALES_CHANNEL_CODE, '*') then
2106 l_hdr_matching := true;
2107 end if;
2108 exception
2109 when no_data_found then
2110 l_hdr_matching := false;
2111 end;
2112
2113 if not l_hdr_matching then
2114 -- if hdr values are modified then quote is changed
2115 l_quote_changed := 'Y';
2116 else
2117 -- fetch line values from source -----
2118
2119 l_sql := fetch_sql(DEAL_LINE_TBL);
2120
2121 open c_ref for l_sql using p_src_quote_header_id;
2122 loop
2123 fetch c_ref into r_lines;
2124 exit when c_ref%notfound;
2125
2126 l_line_matching := false;
2127
2128 --read line details from pn_lines ----
2129 begin
2130 select ORG_ID,INVENTORY_ITEM_ID,ORIG_PAYMENT_TERM_ID,
2131 ORIG_SHIP_METHOD_CODE,
2132 GEOGRAPHY_ID,UOM_CODE,ORDERED_QTY,PROPOSED_PRICE,CURRENCY_CODE
2133 into l_org_id, l_item_id, l_pt_id, l_shm_code,
2134 l_geo_id, l_uom_code, l_ord_qty, l_price, l_curr_code
2135 from qpr_pn_lines
2136 where request_header_id = l_request_hdr_id
2137 and source_ref_line_id = r_lines.SOURCE_REF_LINE_ID
2138 and source_ref_hdr_id = r_lines.SOURCE_REF_HDR_ID
2139 and source_id = r_lines.SOURCE_ID
2140 and item_type_code <> 'DUMMY_PARENT'
2141 and rownum < 2;
2142
2143 if nvl(l_org_id, 0) = nvl(r_lines.org_id,0) and
2144 nvl(l_item_id,0) = nvl(r_lines.inventory_item_id,0) and
2145 nvl(l_pt_id,0) = nvl(r_lines.payment_term_id,0) and
2146 nvl(l_shm_code,'*') = nvl(r_lines.ship_method_code, '*') and
2147 nvl(l_geo_id,0) = nvl(r_lines.geography_id, 0) and
2148 nvl(l_uom_code, '*') = nvl(r_lines.uom_code, '*') and
2149 nvl(l_ord_qty,0) = nvl(r_lines.ordered_qty,0) and
2150 nvl(l_price,0) = nvl(r_lines.proposed_price,0) and
2151 nvl(l_curr_code, '*') = nvl(r_lines.Currency_code, '*') then
2152 l_line_matching := true;
2153 end if;
2154 exception
2155 when NO_DATA_FOUND then
2156 l_line_matching := false;
2157 end;
2158
2159 -- even if one line is not matching then quote does not match
2160 if not l_line_matching then
2161 l_quote_changed := 'Y';
2162 exit;
2163 end if;
2164
2165 end loop;
2166 close c_ref;
2167
2168 -- handle the case: header is matching- few lines have been deleted in
2169 -- source and the existing lines match.
2170 if l_quote_changed = 'N' then
2171 select count(*) into l_line_cnt
2172 from qpr_pn_lines
2173 where request_header_id = l_request_hdr_id
2174 and item_type_code <> 'DUMMY_PARENT';
2175
2176 open c_ref for l_sql using p_src_quote_header_id;
2177 loop
2178 fetch c_ref into r_lines;
2179 exit when c_ref%notfound;
2180 end loop;
2181
2182 if c_ref%rowcount <> l_line_cnt then
2183 l_quote_changed := 'Y';
2184 end if;
2185 close c_ref;
2186 end if;
2187 end if;
2188
2189 return(l_quote_changed);
2190 exception
2191 when others then
2192 retcode := 2;
2193 errbuf := sqlerrm;
2194 return(null);
2195 end is_source_quote_changed;
2196
2197 END;
2198