1 Package OKI_REFRESH_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKIRRFHS.pls 115.18 2003/11/25 10:18:39 kbajaj ship $ */
3
4 -- GLOBAL VARIABLES
5
6 g_request_id oki.oki_refreshs.request_id%TYPE;
7 g_program_application_id oki.oki_refreshs.PROGRAM_APPLICATION_ID%TYPE;
8 g_program_id oki.oki_refreshs.PROGRAM_ID%TYPE;
9 g_program_update_date oki.oki_refreshs.PROGRAM_UPDATE_DATE%TYPE;
10
11
12 FUNCTION get_conversion_rate( p_curr_date DATE
13 ,p_from_currency IN VARCHAR2
14 ,p_to_currency IN VARCHAR2
15 ) RETURN NUMBER;
16
17 -- PRAGMA RESTRICT_REFERENCES (get_conversion_rate, WNDS);
18
19
20 --
21 -- update the table that holds the last refresh date
22 --
23 PROCEDURE update_oki_refresh( p_object_name IN VARCHAR2
24 , x_retcode OUT NOCOPY VARCHAR2
25 , p_job_run_id IN NUMBER DEFAULT NULL ) ;
26 --
27 -- Procedure to refresh the latest conversion rates from gl table to oki schema
28 --
29 PROCEDURE refresh_daily_rates(errbuf OUT NOCOPY VARCHAR2
30 ,retcode OUT NOCOPY VARCHAR2);
31
32 --
33 -- Procedure to refresh the sales header denormalized table
34 --
35 PROCEDURE refresh_sales_k_hdrs(errbuf OUT NOCOPY VARCHAR2
36 ,retcode OUT NOCOPY VARCHAR2);
37
38 --
39 -- Procedure to refresh the address denormalized table
40 --
41 PROCEDURE refresh_addrs(errbuf OUT NOCOPY VARCHAR2
42 ,retcode OUT NOCOPY VARCHAR2);
43
44 --
45 -- Procedure to refresh the sold item lines denormalized table
46 --
47 PROCEDURE refresh_sold_itm_lines(errbuf OUT NOCOPY VARCHAR2
48 ,retcode OUT NOCOPY VARCHAR2);
49
50 --
51 -- Procedure to refresh the covered product lines denormalized table
52 --
53 PROCEDURE refresh_cov_prd_lines(errbuf OUT NOCOPY VARCHAR2
54 ,retcode OUT NOCOPY VARCHAR2);
55
56 --
57 -- Procedure to refresh the expired lines denormalized table
58 --
59 PROCEDURE refresh_expired_lines(errbuf OUT NOCOPY VARCHAR2
60 ,retcode OUT NOCOPY VARCHAR2);
61
62 --
63 -- Procedure to refresh the salesrep denormalized table
64 --
65 PROCEDURE refresh_k_salesreps(errbuf OUT NOCOPY VARCHAR2
66 ,retcode OUT NOCOPY VARCHAR2);
67
68 PROCEDURE refresh_k_conv_rates(errbuf OUT NOCOPY VARCHAR2
69 ,retcode OUT NOCOPY VARCHAR2);
70
71 --
72 -- Procedure to create a oki_job_runs record
73 --
74 PROCEDURE job_start( p_job_start_date IN DATE
75 , x_errbuf OUT NOCOPY VARCHAR2
76 , x_retcode OUT NOCOPY VARCHAR2 ) ;
77 --
78 -- Procedure to update a oki_job_runs record
79 --
80 PROCEDURE job_end( x_errbuf OUT NOCOPY VARCHAR2
81 , x_retcode OUT NOCOPY VARCHAR2 ) ;
82
83 --
84 -- Procedure to fast refresh the sales header denormalized table
85 --
86 PROCEDURE fast_sales_k_hdrs( x_errbuf OUT NOCOPY VARCHAR2
87 , x_retcode OUT NOCOPY VARCHAR2 ) ;
88
89 --
90 -- Procedure to fast refresh the sold item lines denormalized table
91 --
92 PROCEDURE fast_sold_itm_lines( x_errbuf OUT NOCOPY VARCHAR2
93 , x_retcode OUT NOCOPY VARCHAR2 ) ;
94
95 --
96 -- Procedure to fast refresh the covered product lines denormalized table
97 --
98 PROCEDURE fast_cov_prd_lines( x_errbuf OUT NOCOPY VARCHAR2
99 , x_retcode OUT NOCOPY VARCHAR2 ) ;
100
101 --
102 -- Procedure to fast refresh the expired lines denormalized table
103 --
104 PROCEDURE fast_expired_lines( x_errbuf OUT NOCOPY VARCHAR2
105 , x_retcode OUT NOCOPY VARCHAR2 ) ;
106
107 --
108 -- Procedure to fast refresh the contract salesrep denormalized table
109 --
110 PROCEDURE fast_k_salesreps( x_errbuf OUT NOCOPY VARCHAR2
111 , x_retcode OUT NOCOPY VARCHAR2 ) ;
112
113 --
114 -- Procedure to fast refresh the contract addresses denormalized table
115 --
116 PROCEDURE fast_addrs( x_errbuf OUT NOCOPY VARCHAR2
117 , x_retcode OUT NOCOPY VARCHAR2 ) ;
118
119 --
120 -- Procedure to handle the first time fast refresh load
121 --
122 PROCEDURE initial_job_check( x_errbuf OUT NOCOPY VARCHAR2
123 , x_retcode OUT NOCOPY VARCHAR2 ) ;
124
125 --
126 -- Procedure to bring contract pricing rules rand quote to contact information
127 -- into OKI schema
128 --
129 PROCEDURE refresh_k_pricing_rules( x_errbuf OUT NOCOPY VARCHAR2
130 , x_retcode OUT NOCOPY VARCHAR2 ) ;
131
132 --
133 -- Procedure to bring contract pricing rules rand quote to contact information into OKI schema
134 -- for only those contracts whose data has changed between last full or fast refresh and rundate
135 --
136 PROCEDURE fast_k_pricing_rules( x_errbuf OUT NOCOPY VARCHAR2
137 , x_retcode OUT NOCOPY VARCHAR2 ) ;
138
139 --
140 -- Procedure to update Top line price_negotiated amount with
141 -- sum of covered product line amounts for service contracts.
142 --
143 PROCEDURE update_service_line( x_errbuf OUT NOCOPY VARCHAR2
144 , x_retcode OUT NOCOPY VARCHAR2 ) ;
145
146 --
147 -- Procedure to update Top line price_negotiated amount with
148 -- sum of covered product line amounts for service contracts.
149 -- for only those contracts whose data has changed between last full or fast refresh and rundate
150 --
151 PROCEDURE fast_update_service_line( x_errbuf OUT NOCOPY VARCHAR2
152 , x_retcode OUT NOCOPY VARCHAR2 ) ;
153
154 --Procedure to load the job_run_dtl with contracts with currency conversion rate
155 --information during inital load
156
157 PROCEDURE initial_load_job_run_dtl( p_job_run_id IN NUMBER
158 , p_job_curr_start_date IN DATE
159 , p_job_curr_end_date IN DATE
160 , x_retcode OUT NOCOPY VARCHAR2);
161 --Procedure to kick the job start during inital load
162
163 PROCEDURE initial_load_job_start(x_errbuf OUT NOCOPY VARCHAR2
164 ,x_retcode OUT NOCOPY VARCHAR2);
165
166 END OKI_REFRESH_PVT ;