1 package bic_summary_extract_pkg AUTHID CURRENT_USER as
2 /* $Header: bicsumms.pls 115.13 2004/05/14 08:15:24 vsegu ship $ */
3
4 -- Procedure Name: extract_main
5 -- Parameters:
6 -- P_start_date : Start date of the start period for for extraction
7 -- p_end_date : Start date of the end period for extraction e.g. if
8 -- the extraction is from jan 99 to july 99,
9 -- p_start_date=1-jan-1999 and p_end_date 1-july-1999
10 -- extracted
11 -- p_delete_flag : default value - 'N'
12 -- if 'Y', first delete existing records for the date
13 -- range, measure and org
14 -- p_measure_code : default null
15 -- if specified, extract data only for that measure.
16 -- Valid measure codes are 'SATISFACTION', 'LOYALTY',
17 -- 'RETENTION', 'LIFECYCLE', 'ACTIVATION' and
18 -- 'ACQUISITION'.
19 -- p_org_id : default null
20 -- if specified, extract data only for that org
21
22 g_proc_name varchar2(50);
23 procedure extract_main ( errbuf out NOCOPY varchar2,
24 retcode out NOCOPY number,
25 p_start_date varchar2 default null,
26 p_end_date varchar2 default null,
27 p_delete_flag varchar2 default null,
28 p_measure_code varchar2 default null,
29 p_org_id number default null);
30 --
31 -- This variable g_log_output can be set to not null value to print messages
32 -- using dbms_output instead of fnd_file.put_line. This is used only for
33 -- testing and developement by developer. Developer can set this variable
34 -- in SQL script before calling extract_main procedure. In this w_log
35 -- procedure will use DBMS_OUTPUT to print message instead of
36 -- fnd_file.put_line
37 g_log_output varchar2( 1) := null;
38 g_measure_code bic_measures_all.measure_code % type;
39 g_period_start_date date;
40 g_to_currency_code varchar2(30) ;
41 g_conversion_type varchar2(30) ;
42
43 -- This procedure gets records from gl_period and inserts them in bic_period
44 procedure extract_calendar( errbuf out NOCOPY varchar2,
45 retcode out NOCOPY number);
46 procedure extract_sales( p_period_start_date date,
47 p_period_end_date date,
48 p_org_id number,
49 p_lf_flag varchar2 default null);
50 procedure write_log( p_msg varchar2);
51 procedure get_sql_sttmnt( p_measure_code varchar2,
52 p_sttmnt in out nocopy varchar2,
53 p_operation_type in out nocopy varchar2,
54 p_mult_factor in out nocopy number);
55 procedure run_sql( p_sttmnt varchar2);
56 procedure extract_periods( p_start_date date,
57 p_end_date date,
58 p_measure_code varchar2,
59 p_org_flag varchar2,
60 p_delete_flag varchar2,
61 p_org_id number );
62 procedure extract_all_periods(p_start_date date,
63 p_end_date date ) ;
64 procedure generate_error( p_measure_code varchar2 default null,
65 msg varchar2);
66 function convert_amt( p_from_currency_code varchar2,
67 p_date date,
68 p_amt number) return number ;
69 procedure debug( debug_str VARCHAR2);
70 end bic_summary_extract_pkg;
71
72
73