[Home] [Help]
PACKAGE BODY: APPS.OE_BIS_CUST_SAT_SUMMARY
Source
1 PACKAGE BODY OE_BIS_CUST_SAT_SUMMARY AS
2 /*$Header: OEXBCSSB.pls 115.4 99/08/18 09:51:38 porting ship $*/
3
4 g_userid NUMBER;
5 g_applicationid NUMBER;
6 g_debug NUMBER := 0 ;
7 g_date_from DATE ;
8 g_date_to DATE;
9 g_uom_code VARCHAR2(10);
10
11
12 /* OE Contants */
13
14 Procedure Load_Summary_Info IS
15 x_errnum NUMBER;
16 x_errmesg VARCHAR2(240);
17 errbuf VARCHAR2(240);
18 retcode VARCHAR2(240);
19 begin
20 Populate_Summary_Table(x_errnum, x_errmesg);
21
22 errbuf := x_errmesg ;
23 retcode := to_char(x_errnum);
24
25 end Load_Summary_Info ;
26
27
28 Procedure Load_Summary_Info2 IS
29 x_errnum NUMBER;
30 x_errmesg VARCHAR2(240);
31 errbuf VARCHAR2(240);
32 retcode VARCHAR2(240);
33 begin
34 Populate_Summary_Table2(x_errnum, x_errmesg);
35
36 errbuf := x_errmesg ;
37 retcode := to_char(x_errnum);
38
39 end Load_Summary_Info2;
40
41
42 Procedure Populate_Summary_Table(x_errnum OUT NUMBER,
43 x_errmesg OUT VARCHAR2)
44 /* p_ORGANIZATION_ID IN NUMBER,
45 p_INVENTORY_ITEM_ID IN NUMBER,
46 p_CUSTOMER_ID IN DATE,
47 p_TRANSACTION_DATE IN DATE,
48 p_DELIVERY_PERCENT IN NUMBER,
49 p_RETURN_PERCENT IN NUMBER,
50 p_NET_SALES IN NUMBER,
51 p_LAST_UPDATE_DATE IN NUMBER,
52 p_LAST_UPDATED_BY IN NUMBER,
53 p_CREATION_DATE IN VARCHAR2
54 p_CREATED_BY IN NUMBER
55 p_LAST_UPDATE_LOGIN IN NUMBER,
56 p_REQUEST_ID IN NUMBER,
57 p_PROGRAM_APPLICATION_ID IN NUMBER,
58 p_PROGRAM_ID IN NUMBER,
59 p_PROGRAM_UPDATE_DATE IN NUMBER */
60
61 IS
62 /* Local variables */
63 x_temp varchar2(10);
64 x_user_id NUMBER := fnd_global.user_id;
65 x_login_id NUMBER := fnd_global.login_id;
66 x_req_id NUMBER := fnd_global.CONC_REQUEST_ID;
67 x_prog_appl_id NUMBER := fnd_global.PROG_APPL_ID;
68 x_prog_id NUMBER := fnd_global.CONC_PROGRAM_ID;
69 app_col_name varchar2(100);
70
71 BEGIN
72
73 /*
74 ORGANIZATION_ID Dimension 1 NUMBER
75 INVENTORY_ITEM_ID Dimension 2 NOT NULL NUMBER
76 CUSTOMER_ID Dimension 3 NUMBER
77 TRANSACTION_DATE Dimension 4 NOT NULL DATE
78 DELIVERY_PERCENT Measure 1 NUMBER
79 RETURN_PERCENT Measure 2 NUMBER
80 NET_SALES Measure 3 NUMBER
81 LAST_UPDATE_DATE Who Column NOT NULL DATE
82 LAST_UPDATED_BY Who Column NOT NULL NUMBER
83 CREATION_DATE Who Column NOT NULL DATE
84 CREATED_BY Who Column NOT NULL NUMBER
85 LAST_UPDATE_LOGIN Who Column NUMBER
86 REQUEST_ID Who Column NUMBER
87 PROGRAM_APPLICATION_ID Who Column NUMBER(15)
88 PROGRAM_ID Who Column NUMBER(15)
89 PROGRAM_UPDATE_DATE Who Column NUMBER(15)
90 */
91
92
93 insert into oe_bis_cust_sat_t (
94 ORGANIZATION_ID,
95 INVENTORY_ITEM_ID,
96 CUSTOMER_ID,
97 TRANSACTION_DATE,
98 DEL_flag,
99 RETURN_flag,
100 NET_SALES,
101 LAST_UPDATE_DATE,
102 LAST_UPDATED_BY,
103 CREATION_DATE,
104 CREATED_BY,
105 LAST_UPDATE_LOGIN,
106 REQUEST_ID,
107 PROGRAM_APPLICATION_ID,
108 PROGRAM_ID,
109 PROGRAM_UPDATE_DATE,
110 HEADER_ID,
111 LINE_ID )
112 select sla.org_id,
113 sla.inventory_item_id,
114 sha.customer_id,
115 nvl(sla.promise_date, sha.date_ordered),
116 decode(sla.line_type_code, 'RETURN', NULL,decode(trunc(sla.promise_date),
117 trunc(wd.date_closed), 'Y','N')) del_flag,
118 NULL return_flag,
119 decode(sla.line_type_code, 'RETURN',0,nvl(sla.selling_price,0) *
120 (nvl(sla.ordered_quantity,0) - nvl(sla.cancelled_quantity,0))) net_sales,
121 sysdate, -- Last Update Date
122 x_user_id,
123 sysdate, -- Creation Date
124 x_user_id,
125 x_login_id,
126 x_req_id,
127 x_prog_appl_id,
128 x_prog_id,
129 sysdate, -- Program Update Date
130 sha.header_id,
131 sla.line_id
132 from
133 so_headers_all sha,
134 so_lines_all sla,
135 wsh_departures wd,
136 so_picking_lines_all spla,
137 so_picking_line_details spld
138 where sha.header_id = sla.header_id
139 and sla.line_id = spla.order_line_id
140 and spla.picking_line_id = spld.picking_line_id
141 and spld.picking_line_detail_id in -- New
142 (select min(spld1.picking_line_detail_id)
143 from so_picking_line_details spld1
144 where spla.picking_line_id = spld1.picking_line_id)
145 and spld.departure_id = wd.departure_id
146 and ( sla.promise_date is not null
147 or sla.schedule_date is not null );
148
149
150 /* FOR RETURNS */
151
152 insert into oe_bis_cust_sat_t (
153 ORGANIZATION_ID,
154 INVENTORY_ITEM_ID,
155 CUSTOMER_ID,
156 TRANSACTION_DATE,
157 DEL_flag,
158 RETURN_flag,
159 NET_SALES,
160 LAST_UPDATE_DATE,
161 LAST_UPDATED_BY,
162 CREATION_DATE,
163 CREATED_BY,
164 LAST_UPDATE_LOGIN,
165 REQUEST_ID,
166 PROGRAM_APPLICATION_ID,
167 PROGRAM_ID,
168 PROGRAM_UPDATE_DATE,
169 HEADER_ID,
170 LINE_ID )
171 select sla.org_id,
172 sla.inventory_item_id,
173 sha.customer_id,
174 nvl(sla.promise_date, sha.date_ordered),
175 NULL del_flag,
176 decode(sla.line_type_code, 'RETURN','Y',NULL) return_flag,
177 decode(sla.line_type_code, 'RETURN', nvl(sla.selling_price,0) *
178 (nvl(sla.ordered_quantity,0) -
182 sysdate, -- Last Update Date
179 nvl(sla.cancelled_quantity,0)) * -1,nvl(sla.selling_price,0) *
180 (nvl(sla.ordered_quantity,0) -
181 nvl(sla.cancelled_quantity,0))) net_sales,
183 x_user_id,
184 sysdate, -- Creation Date
185 x_user_id,
186 x_login_id,
187 x_req_id,
188 x_prog_appl_id,
189 x_prog_id,
190 sysdate, -- Program Update Date
191 sha.header_id,
192 sla.line_id
193 from
194 so_headers_all sha,
195 so_lines_all sla
196 where
197 sha.header_id = sla.header_id and
198 sla.line_type_code = 'RETURN';
199
200 COMMIT;
201
202 Exception
203
204 when others then
205
206 if g_debug = 1 then
207 fnd_file.put_line(fnd_file.log, SQLCODE);
208 fnd_file.put_line(fnd_file.log,SQLERRM);
209 end if ;
210 --dbms_output.put_line(SQLCODE);
211 --dbms_output.put_line(SQLERRM);
212 x_errnum := -1 ;
213 x_errmesg := substr(SQLERRM,1,150);
214 commit ;
215 return ;
216
217
218 x_errnum := 0;
219 x_errmesg := 'NOERROR'; -- Replace this with the actual error later
220
221
222 END Populate_Summary_Table;
223
224
225
226
227
228
229 Procedure Populate_Summary_Table2(x_errnum OUT NUMBER,
230 x_errmesg OUT VARCHAR2) IS
231 /* Local variables */
232 x_temp varchar2(10);
233 x_user_id NUMBER := fnd_global.user_id;
234 x_login_id NUMBER := fnd_global.login_id;
235 x_req_id NUMBER := fnd_global.CONC_REQUEST_ID;
236 x_prog_appl_id NUMBER := fnd_global.PROG_APPL_ID;
237 x_prog_id NUMBER := fnd_global.CONC_PROGRAM_ID;
238 app_col_name varchar2(100);
239 sql_stmt varchar2(10000);
240
241 BEGIN
242
243 /* Populating the second table */
244
245 begin
246 select 'hl.' || application_column_name
247 into app_col_name
248 from bis_flex_mappings_v
249 where flex_field_type = 'D' and
250 flex_field_name = 'Additional Location Details' and
251 level_id = 127;
252 exception
253 when others then
254 app_col_name := ' ';
255 end;
256
257
258 if app_col_name = ' ' then
259
260 sql_stmt := 'insert into oe_bis_cust_sat_t2 ( SET_OF_BOOKS_ID,
261 SET_OF_BOOKS_NAME,
262 LEGAL_ENTITY_ID,
263 LEGAL_ENTITY_NAME,
264 OPERATING_UNIT_ID,
265 OPERATING_UNIT_NAME,
266 ORGANIZATION_ID,
267 ORGANIZATION_NAME,
268 INVENTORY_ITEM_ID,
269 CATEGORY_ID,
270 CATEGORY_DESC,
271 ITEM_DESCRIPTION,
272 INVENTORY_ITEM_NAME,
273 CUSTOMER_ID,
274 SALES_CHANNEL_CODE,
278 TRANSACTION_DATE,
275 CUSTOMER_NAME,
276 AREA,
277 COUNTRY,
279 DEL_SALES,
280 RET_SALES,
281 NET_SALES,
282 PERIOD_SET_NAME,
283 YEAR_PERIOD,
284 QUARTER_PERIOD,
285 MONTH_PERIOD,
286 HEADER_ID,
287 LINE_ID,
288 LOCATION_ID,
289 LOCATION_CODE)
290 select ood.set_of_books_id, -- set_of_books_id,
291 gsob.name, -- set_of_books_name,
292 hle.organization_id, -- legal_entity_id,
293 hle.name, -- legal_entity_name,
294 haou.organization_id, -- operating_unit_id,
295 haou.name, -- operating_unit_name,
296 obcs.organization_id,
297 ood.organization_name,
298 obcs.inventory_item_id,
299 mc.category_id,
300 mc.segment1 || ''.'' || mc.segment2, -- as category_desc,
301 msi.description, -- as item_description,
302 mif.item_number, -- as inventory_item_name,
303 obcs.customer_id,
304 nvl(rc.sales_channel_code,''Unspecified''), -- sales_channel_code,
305 rc.customer_name,
306 bth.parent_territory_code, -- area,
307 hl.country,
308 obcs.transaction_date,
309 decode(obcs.del_flag,''Y'',obcs.net_sales,0), -- del_sales,
310 decode(obcs.return_flag,''Y'',(obcs.net_sales) * -1,0), -- ret_sales,
311 decode(obcs.return_flag, ''Y'',0,obcs.net_sales), -- net_sales,
312 year.period_set_name,
313 year.period_name, -- year_period ,
314 quarter.period_name, -- quarter_period,
315 month.period_name, -- month_period,
316 obcs.header_id, -- header_id,
317 obcs.line_id, -- line_id,
318 hl.location_id, -- location_id,
319 hl.location_code -- location_code,
320 from
321 gl_periods year,
322 gl_periods quarter,
323 gl_periods month,
324 gl_sets_of_books gsob,
325 oe_bis_cust_sat_t obcs,
326 org_organization_definitions ood,
327 ra_customers rc,
328 hr_all_organization_units haou,
329 hr_locations_v hl,
330 hr_legal_entities hle,
331 mtl_item_categories mic,
332 mtl_categories mc,
333 mtl_default_category_sets mdcs,
334 mtl_system_items msi,
335 mtl_item_flexfields mif,
336 bis_territory_hierarchies bth
337 where
338 obcs.organization_id = ood.organization_id and
339 obcs.customer_id = rc.customer_id and
340 haou.organization_id = ood.organization_id and
341 haou.location_id = hl.location_id and
342 ood.legal_entity = hle.organization_id and
343 ood.set_of_books_id = hle.set_of_books_id and
344 obcs.organization_id = mic.organization_id and
345 obcs.inventory_item_id = msi.inventory_item_id and
346 obcs.organization_id = msi.organization_id and
347 obcs.inventory_item_id = mic.inventory_item_id and
348 mdcs.functional_area_id = 7 and
349 mdcs.category_set_id = mic.category_set_id and
350 mic.category_id = mc.category_id and
351 mif.organization_id = obcs.organization_id and
352 mif.inventory_item_id = obcs.inventory_item_id and
353 hl.country = bth.child_territory_code and
357 year.period_set_name = gsob.period_set_name and
354 bth.parent_territory_type = ''AREA'' and
355 gsob.set_of_books_id = ood.set_of_books_id and
356 obcs.net_sales <> 0 and
358 quarter.period_set_name = gsob.period_set_name and
359 month.period_set_name = gsob.period_set_name and
360 year.period_type = ''Year'' and
361 quarter.period_type = ''Quarter'' and
362 month.period_type = ''Month'' and
363 year.adjustment_period_flag = ''N'' and
364 quarter.adjustment_period_flag = ''N'' and
365 month.adjustment_period_flag = ''N'' and
366 year.period_year = to_char(obcs.transaction_date, ''YYYY'') and
367 trunc(obcs.transaction_date) between trunc(quarter.start_date) and trunc(quarter.end_date) and
368 trunc(obcs.transaction_date) between trunc(month.start_date) and trunc(month.end_date) ' ;
369 else
370
371 sql_stmt := 'insert into oe_bis_cust_sat_t2 ( SET_OF_BOOKS_ID,
372 SET_OF_BOOKS_NAME,
373 LEGAL_ENTITY_ID,
374 LEGAL_ENTITY_NAME,
375 OPERATING_UNIT_ID,
376 OPERATING_UNIT_NAME,
377 ORGANIZATION_ID,
378 ORGANIZATION_NAME,
379 INVENTORY_ITEM_ID,
380 CATEGORY_ID,
381 CATEGORY_DESC,
382 ITEM_DESCRIPTION,
383 INVENTORY_ITEM_NAME,
384 CUSTOMER_ID,
385 SALES_CHANNEL_CODE,
386 CUSTOMER_NAME,
387 AREA,
388 COUNTRY,
389 TRANSACTION_DATE,
390 DEL_SALES,
391 RET_SALES,
392 NET_SALES,
393 PERIOD_SET_NAME,
394 YEAR_PERIOD,
395 QUARTER_PERIOD,
396 MONTH_PERIOD,
397 HEADER_ID,
398 LINE_ID,
399 LOCATION_ID,
400 LOCATION_CODE,
401 REGION)
402 select ood.set_of_books_id, -- set_of_books_id,
403 gsob.name, -- set_of_books_name,
404 hle.organization_id, -- legal_entity_id,
405 hle.name, -- legal_entity_name,
406 haou.organization_id, -- operating_unit_id,
407 haou.name, -- operating_unit_name,
408 obcs.organization_id,
409 ood.organization_name,
410 obcs.inventory_item_id,
411 mc.category_id,
412 mc.segment1 || ''.'' || mc.segment2, -- as category_desc,
413 msi.description, -- as item_description,
414 mif.item_number, -- as inventory_item_name,
415 obcs.customer_id,
416 nvl(rc.sales_channel_code,''Unspecified''), -- sales_channel_code,
417 rc.customer_name,
418 bth.parent_territory_code, -- area,
419 hl.country,
420 obcs.transaction_date,
421 decode(obcs.del_flag,''Y'',obcs.net_sales,0), -- del_sales,
422 decode(obcs.return_flag,''Y'',(obcs.net_sales) * -1,0), -- ret_sales,
423 decode(obcs.return_flag, ''Y'',0,obcs.net_sales), -- net_sales,
424 year.period_set_name,
425 year.period_name, -- year_period ,
426 quarter.period_name, -- quarter_period,
427 month.period_name, -- month_period,
428 obcs.header_id, -- header_id,
432 '||app_col_name||' -- region
429 obcs.line_id, -- line_id,
430 hl.location_id, -- location_id,
431 hl.location_code, -- location_code,
433 from
434 gl_periods year,
435 gl_periods quarter,
436 gl_periods month,
437 gl_sets_of_books gsob,
438 oe_bis_cust_sat_t obcs,
439 org_organization_definitions ood,
440 ra_customers rc,
441 hr_all_organization_units haou,
442 hr_locations_v hl,
443 hr_legal_entities hle,
444 mtl_item_categories mic,
445 mtl_categories mc,
446 mtl_default_category_sets mdcs,
447 mtl_system_items msi,
448 mtl_item_flexfields mif,
449 bis_territory_hierarchies bth
450 where
451 obcs.organization_id = ood.organization_id and
452 obcs.customer_id = rc.customer_id and
453 haou.organization_id = ood.organization_id and
454 haou.location_id = hl.location_id and
455 ood.legal_entity = hle.organization_id and
456 ood.set_of_books_id = hle.set_of_books_id and
457 obcs.organization_id = mic.organization_id and
458 obcs.inventory_item_id = msi.inventory_item_id and
459 obcs.organization_id = msi.organization_id and
460 obcs.inventory_item_id = mic.inventory_item_id and
461 mdcs.functional_area_id = 7 and
462 mdcs.category_set_id = mic.category_set_id and
463 mic.category_id = mc.category_id and
464 mif.organization_id = obcs.organization_id and
465 mif.inventory_item_id = obcs.inventory_item_id and
466 hl.country = bth.child_territory_code and
467 bth.parent_territory_type = ''AREA'' and
468 gsob.set_of_books_id = ood.set_of_books_id and
469 obcs.net_sales <> 0 and
470 year.period_set_name = gsob.period_set_name and
471 quarter.period_set_name = gsob.period_set_name and
472 month.period_set_name = gsob.period_set_name and
473 year.period_type = ''Year'' and
474 quarter.period_type = ''Quarter'' and
475 month.period_type = ''Month'' and
476 year.adjustment_period_flag = ''N'' and
477 quarter.adjustment_period_flag = ''N'' and
478 month.adjustment_period_flag = ''N'' and
479 year.period_year = to_char(obcs.transaction_date, ''YYYY'') and
480 trunc(obcs.transaction_date) between trunc(quarter.start_date) and trunc(quarter.end_date) and
481 trunc(obcs.transaction_date) between trunc(month.start_date) and trunc(month.end_date) ' ;
482 end if;
483
484 execute immediate sql_stmt;
485
486 Exception
487
488 when others then
489
490 if g_debug = 1 then
491 fnd_file.put_line(fnd_file.log, SQLCODE);
492 fnd_file.put_line(fnd_file.log,SQLERRM);
493 end if ;
494 x_errnum := -1 ;
495 x_errmesg := substr(SQLERRM,1,150);
496 commit ;
497 return ;
498
499
500 x_errnum := 0;
501 x_errmesg := 'NOERROR'; -- Replace this with the actual error later
502
503
504 END Populate_Summary_Table2;
505
506
507 END OE_BIS_CUST_SAT_SUMMARY;