[Home] [Help]
PACKAGE BODY: APPS.QPR_COLLECT_UOM_DATA
Source
1 PACKAGE BODY QPR_COLLECT_UOM_DATA AS
2 /* $Header: QPRUCUMB.pls 120.0 2007/10/11 13:09:38 agbennet noship $ */
3
4 type char40_type is table of varchar2(40) index by PLS_INTEGER;
5 type char1_type is table of varchar2(1) index by PLS_INTEGER;
6 type char240_type is table of varchar2(240) index by PLS_INTEGER;
7 type num_type is table of number index by PLS_INTEGER;
8 type uom_type is record(FROM_UOM_CLASS char40_type,
9 TO_UOM_CLASS char40_type,
10 FROM_UOM_CODE char40_type,
11 TO_UOM_CODE char40_type,
12 CONVERSION_RATE num_type,
13 BASE_UOM_FLAG char1_type,
14 SR_ITEM_PK char240_type,
15 FROM_UOM_DESC char240_type);
16 r_uom_data uom_type;
17 UOM_CONV_SRC_TBL constant varchar2(30) := 'QPR_SR_UOM_CONVERSIONS_V';
18 procedure insert_uom_data(p_instance_id in number) is
19 request_id number;
20 sys_date date:= sysdate;
21 user_id number:= fnd_global.user_id;
22 login_id number:= fnd_global.conc_login_id;
23 prg_appl_id number:= fnd_global.prog_appl_id;
24 prg_id number:= fnd_global.conc_program_id;
25 begin
26 fnd_profile.get('CONC_REQUEST_ID', request_id);
27 forall i in r_uom_data.FROM_UOM_CLASS.first..r_uom_data.FROM_UOM_CLASS.last
28 insert into QPR_UOM_CONVERSIONS(UOM_CONV_ID, FROM_UOM_CLASS, TO_UOM_CLASS,
29 FROM_UOM_CODE, TO_UOM_CODE, BASE_UOM_FLAG,
30 CONVERSION_RATE, ITEM_KEY, FROM_UOM_DESC,
31 INSTANCE_ID,
32 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
33 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
34 PROGRAM_APPLICATION_ID, PROGRAM_ID,
35 REQUEST_ID)
36 values(QPR_UOM_CONVERSIONS_S.nextval,
37 r_uom_data.FROM_UOM_CLASS(i), r_uom_data.TO_UOM_CLASS(i),
38 r_uom_data.FROM_UOM_CODE(i), r_uom_data.TO_UOM_CODE(i),
39 r_uom_data.BASE_UOM_FLAG(i), r_uom_data.CONVERSION_RATE(i),
40 r_uom_data.SR_ITEM_PK(i), r_uom_data.FROM_UOM_DESC(i),
41 p_instance_id,
42 sys_date, user_id, sys_date, user_id,
43 login_id, prg_appl_id, prg_id, request_id) ;
44 exception
45 when OTHERS then
46 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING UOM_DATA...');
47 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
48 raise;
49 end insert_uom_data;
50
51 procedure collect_uom_data(errbuf out nocopy varchar2,
52 retcode out nocopy number,
53 p_instance_id number) is
54 bfound boolean := false;
55 nrows number := 1000;
56 src_table varchar2(200);
57 s_sql varchar2(1000);
58 c_get_uom_data SYS_REFCURSOR;
59 begin
60 if nvl(qpr_sr_util.read_parameter('QPR_PULL_UOM_CONV_TO_ODS'), 'N')= 'Y' then
61 src_table := UOM_CONV_SRC_TBL || qpr_sr_util.get_dblink(p_instance_id);
62
63 delete QPR_UOM_CONVERSIONS where INSTANCE_ID = p_instance_id;
64
65 s_sql := 'select FROM_UOM_CLASS, TO_UOM_CLASS,FROM_UOM_CODE, TO_UOM_CODE, ';
66 s_sql := s_sql || 'CONVERSION_RATE,BASE_UOM_FLAG, SR_ITEM_PK,FROM_UOM_DESC';
67 s_sql := s_sql || ' FROM ' || src_table;
68
69 open c_get_uom_data for s_sql;
70 loop
71 fetch c_get_uom_data bulk collect into r_uom_data limit nrows;
72 exit when r_uom_data.FROM_UOM_CLASS.count = 0;
73 fnd_file.put_line(fnd_file.log,
74 'Record count: ' || r_uom_data.FROM_UOM_CLASS.count);
75 insert_uom_data(p_instance_id);
76 bfound := true;
77 end loop;
78 commit;
79 if bfound = false then
80 fnd_file.put_line(fnd_file.log, 'No data retrieved from source');
81 end if;
82 else
83 fnd_file.put_line(fnd_file.log,
84 'Rates not fetched to ODS as parameter QPR_PULL_UOM_CONV_TO_ODS is No/not set');
85 end if;
86 exception
87 when OTHERS then
88 retcode := -1;
89 errbuf := 'ERROR: ' || substr(sqlerrm, 1, 1000);
90 fnd_file.put_line(fnd_file.log, substr(sqlerrm, 1, 1000));
91 fnd_file.put_line(fnd_file.log, 'CANNOT POPULATE UOM CONVERSION DATA');
92 rollback;
93 end collect_uom_data;
94 END;
95
96