1 PACKAGE FTE_DIST_DWNLD_PKG AUTHID CURRENT_USER AS
2 /* $Header: FTEDISDS.pls 115.9 2004/03/18 20:18:28 ablundel noship $ */
3
4 -- ------------------------------------------------------------------------------------------- --
5 -- --
6 -- Tables and records for input --
7 -- ---------------------------- --
8 -- --
9 -- ------------------------------------------------------------------------------------------- --
10 g_user_debug NUMBER := 0;
11
12 TYPE fte_distd_tmplt_col_rec IS RECORD(seq NUMBER,
13 type VARCHAR2(30),
14 start_pos NUMBER,
15 length NUMBER,
16 delim VARCHAR2(10));
17 TYPE fte_distd_tmplt_col_tab IS TABLE OF fte_distd_tmplt_col_rec INDEX BY BINARY_INTEGER;
18
19
20 TYPE fte_distd_col_rec IS RECORD(seq NUMBER,
21 code VARCHAR2(30),
22 length NUMBER,
23 delim VARCHAR2(10),
24 start_pos NUMBER,
25 id NUMBER);
26 TYPE fte_distd_col_tab IS TABLE OF fte_distd_col_rec INDEX BY BINARY_INTEGER;
27
28
29 TYPE fte_distd_attr_rec IS RECORD(seq NUMBER,
30 code VARCHAR2(30),
31 length NUMBER,
32 delim VARCHAR2(10));
33 TYPE fte_distd_attr_tab IS TABLE OF fte_distd_attr_rec INDEX BY BINARY_INTEGER;
34
35 TYPE fte_distd_region_rec IS RECORD(region_id NUMBER,
36 postal_code VARCHAR2(30),
37 city VARCHAR2(60),
38 state VARCHAR2(60),
39 county VARCHAR2(60),
40 country VARCHAR2(60));
41 TYPE fte_distd_region_tab IS TABLE OF fte_distd_region_rec INDEX BY BINARY_INTEGER;
42
43 TYPE fte_distd_od_pair_rec IS RECORD(origin_id NUMBER,
44 destination_id NUMBER,
45 origin_line VARCHAR2(2000),
46 destination_line VARCHAR2(2000),
47 file_line VARCHAR2(2000));
48 TYPE fte_distd_od_pair_tab IS TABLE OF fte_distd_od_pair_rec INDEX BY BINARY_INTEGER;
49
50 TYPE fte_distd_reg_code_rec IS RECORD(region_id NUMBER,
51 state_code VARCHAR2(10),
52 country_code VARCHAR2(10));
53 TYPE fte_distd_reg_code_tab IS TABLE OF fte_distd_reg_code_rec INDEX BY BINARY_INTEGER;
54
55 -- -----------------------------------------------------------------------------------
56 -- GLOBAL VARIABLES/CONSTANTS
57 -- --------------------------
58 --
59 -- -----------------------------------------------------------------------------------
60 g_max_table_size CONSTANT PLS_INTEGER := 150;
61
62
63 TYPE fte_distd_tmp_num_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
64 TYPE fte_distd_tmp_flag_table IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
65 TYPE fte_distd_tmp_uom_table IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
66 TYPE fte_distd_tmp_char4_table IS TABLE OF VARCHAR2(4) INDEX BY BINARY_INTEGER;
67 TYPE fte_distd_tmp_char10_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
68 TYPE fte_distd_tmp_code_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
69 TYPE fte_distd_tmp_char60_table IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
70 TYPE fte_distd_tmp_char80_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
71 TYPE fte_distd_tmp_desc_table IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
72 TYPE fte_distd_tmp_msg_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
76
73 TYPE fte_distd_tmp_date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;
74
75
77
78
79 -- ------------------------------------------------------------------------------------------- --
80 -- --
81 -- PROCEDURE DEFINITONS --
82 -- -------------------- --
83 -- --
84 -- ------------------------------------------------------------------------------------------- --
85 PROCEDURE BULK_DOWNLOAD_DTT(p_load_id IN NUMBER,
86 p_template_id IN NUMBER,
87 p_origin_facility_id IN VARCHAR2,
88 p_origin_region_id IN VARCHAR2,
89 p_origin_all_fac_flag IN VARCHAR2,
90 p_all_fac_no_data_flag IN VARCHAR2,
91 p_dest_facility_id IN VARCHAR2,
92 p_dest_region_id IN VARCHAR2,
93 p_dest_all_fac_flag IN VARCHAR2,
94 p_file_extension IN VARCHAR2,
95 p_src_filename IN VARCHAR2,
96 p_resp_id IN NUMBER,
97 p_resp_appl_id IN NUMBER,
98 p_user_id IN NUMBER,
99 p_user_debug IN NUMBER,
100 x_filename OUT NOCOPY VARCHAR2,
101 x_request_id OUT NOCOPY NUMBER,
102 x_error_msg_text OUT NOCOPY VARCHAR2);
103
104 PROCEDURE DOWNLOAD_DTT_FILE(p_errbuf OUT NOCOPY VARCHAR2,
105 p_retcode OUT NOCOPY VARCHAR2,
106 p_load_id IN NUMBER,
107 p_src_filename IN VARCHAR2,
108 p_src_filedir IN VARCHAR2,
109 p_user_debug IN NUMBER,
110 p_template_id IN NUMBER,
111 p_origin_facility_id IN NUMBER,
112 p_origin_region_id IN NUMBER,
113 p_origin_all_fac_flag IN VARCHAR2,
114 p_all_fac_no_data_flag IN VARCHAR2,
115 p_dest_facility_id IN NUMBER,
116 p_dest_region_id IN NUMBER,
117 p_dest_all_fac_flag IN VARCHAR2,
118 p_file_extension IN VARCHAR2);
119
120 PROCEDURE DOWNLOAD_OD_DATA(p_template_id IN NUMBER,
121 p_origin_facility_id IN NUMBER,
122 p_origin_region_id IN NUMBER,
123 p_origin_all_fac_flag IN VARCHAR2,
124 p_all_fac_no_data_flag IN VARCHAR2,
125 p_dest_facility_id IN NUMBER,
126 p_dest_region_id IN NUMBER,
127 p_dest_all_fac_flag IN VARCHAR2,
128 p_file_extension IN VARCHAR2,
129 p_user_debug_flag IN VARCHAR2,
130 x_filename IN OUT NOCOPY VARCHAR2,
131 x_return_message OUT NOCOPY VARCHAR2,
132 x_return_status OUT NOCOPY VARCHAR2);
133
134
135 PROCEDURE CREATE_DWNLD_FILENAME(p_user_debug_flag IN VARCHAR2,
136 x_file_extension IN OUT NOCOPY VARCHAR2,
137 x_file_name OUT NOCOPY VARCHAR2,
138 x_return_message OUT NOCOPY VARCHAR2,
139 x_return_status OUT NOCOPY VARCHAR2);
140
141
142 PROCEDURE CREATE_DWNLD_FILE(p_origin_route IN PLS_INTEGER,
143 p_destination_route IN PLS_INTEGER,
144 p_origin_id IN NUMBER,
145 p_destination_id IN NUMBER,
146 p_template_id IN NUMBER,
147 p_file_name IN VARCHAR2,
148 p_file_extension IN VARCHAR2,
149 p_region_type IN NUMBER,
150 p_distance_profile IN VARCHAR2,
151 p_user_debug_flag IN VARCHAR2,
152 x_return_message OUT NOCOPY VARCHAR2,
153 x_return_status OUT NOCOPY VARCHAR2);
154
155 FUNCTION FIRST_TIME RETURN BOOLEAN;
156
157
158
159 END FTE_DIST_DWNLD_PKG;