1 PACKAGE FTE_BULKLOAD_PKG AUTHID CURRENT_USER AS
2 /* $Header: FTEBKLDS.pls 120.3 2005/08/24 06:33:35 pkaliyam ship $ */
3
4 --
5 -- Package FTE_BULKLOAD_PKG
6 -- (Documentation in package body)
7 --
8
9 g_debug_on BOOLEAN := false;
10 g_upload_dirname VARCHAR2(300) ;
11 g_upload_dir_set BOOLEAN := false;
12
13 TYPE var_arr4000 IS TABLE OF VARCHAR2(4000);
14 TYPE var_arr100 IS TABLE OF VARCHAR2(100);
15
16 TYPE array_tbl IS TABLE OF STRINGARRAY INDEX BY BINARY_INTEGER;
17 TYPE data_values_tbl IS TABLE OF VARCHAR2(2000) INDEX BY VARCHAR2(50);
18 TYPE block_data_tbl IS TABLE OF data_values_tbl INDEX BY BINARY_INTEGER;
19 TYPE block_header_index_tbl IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
20 TYPE block_header_tbl IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
21
22 g_block_header_index block_header_index_tbl;
23 g_load_id NUMBER;
24
25 G_DATE_FORMAT CONSTANT VARCHAR2(25) := 'MM-DD-YYYY hh24:mi:ss';
26 G_TIME_FORMAT CONSTANT VARCHAR2(15) := 'hh24:mi';
27 G_DATE_FORMAT2 CONSTANT VARCHAR2(25) := 'YYYY-MM-DD';
28 G_DATE_FORMAT3 CONSTANT VARCHAR2(30) := 'DD-MON-RRRR';
29
30 ------------------------------------------------------------------
31 -- Procedure: GET_PROCESS_ID
32 -- Purpose: get the process id for qp interface tables
33 -- Return: process_id for qp_interface tables, also known as load_id
34 -------------------------------------------------------------------
35 FUNCTION GET_PROCESS_ID RETURN NUMBER;
36
37 ----------------------------------------------------------------------------------------------
38 -- Procedure: SUBMIT_DATA
39 -- Purpose: Create a new row in the database table 'FTE_BULKLOAD_DATA' with the
40 -- specified parameters when user uses the local file option. This represents a new
41 -- bulkloading request to the concurrent manager.
42 --
43 -- IN parameters:
44 -- 1. p_FileName: name of the file to submit
45 -- 2. p_LoadId: id of the load
46 -- 3. p_FileType: type of the file
47 -- 4. p_LoadType: type of load
48 -- 5. p_RequestId: id to return to user.
49 --
50 -- RETURN: 0 ==> Operation succeeded.
51 -- 1 ==> File Not Found
52 -- 2 ==> Invalid File - No Template (For DTT Upload- Pack J [ABLUNDEL][2003/06/13])
53 -- 3 ==> Invalid File Length (For DTT Upload- Pack J [ABLUNDEL][2003/06/13])
54 -- Propagate all other errors to the caller.
55 -----------------------------------------------------------------------------------------------
56 FUNCTION SUBMIT_DATA (
57 p_FileName IN VARCHAR2,
58 p_LoadId IN NUMBER,
59 p_FileType IN VARCHAR2,
60 p_LoadType IN VARCHAR2,
61 p_RequestId IN NUMBER ) RETURN NUMBER;
62
63 --------------------------------------------------------------------------------
64 -- Procedure: UPDATE_RID
65 --
66 -- Purpose: Update the row specified by the parameters with the request ID of the.
67 -- concurrent request. The row should already exist in the database.
68 --
69 -- IN parameters:
70 -- 1. p_FileName: file name
71 -- 2. p_LoadId: loading id
72 -- 3. p_FileType: type of file
73 -- 4. p_LoadType: loading type
74 -- 5. p_RequestId: the Id that need to be updated.
75 --------------------------------------------------------------------------------
76 PROCEDURE UPDATE_RID (
77 p_FileName IN VARCHAR2,
78 p_LoadId IN NUMBER,
79 p_FileType IN VARCHAR2,
80 p_LoadType IN VARCHAR2,
81 p_RequestId IN NUMBER );
82
83 -------------------------------------------------------------------------------------------
84 -- Procedure: UPLOAD_FILE
85 --
86 -- Purpose: Retrieve information on a load from the database with load id as the parameter
87 --
88 -- IN parameters:
89 -- 1. p_LoadId: loading id
90 --
91 -- OUT parameters:
92 -- 1. p_LoadType: type of load
93 -- 2. p_FileContents: file
94 -- 3. p_FileName: name of file
95 -- 4. p_DirName: name of the file directory
96 -- 5. p_ExitStatus: status
97 --------------------------------------------------------------------------------------------
98 PROCEDURE UPLOAD_FILE (
99 p_LoadId IN NUMBER,
100 p_LoadType OUT NOCOPY VARCHAR2,
101 p_FileContents OUT NOCOPY BLOB,
102 p_FileName OUT NOCOPY VARCHAR2,
103 p_DirName OUT NOCOPY VARCHAR2,
104 p_ExitStatus OUT NOCOPY NUMBER);
105
106 -----------------------------------------------------------------------------------
107 -- Procedure: PROCESS_DATA
108 --
109 -- Purpose: Read the input file into blocks and call approriate loader packages
110 --
111 -- IN parameters:
112 -- 1. p_load_id: loading id
113 -- 2. p_src_filename: filename
114 -- 3. p_currency: currency for LTL loader
115 -- 4. p_uom_code: uom for LTL load
116 -- 5. p_orig_country: origin country for LTL load
117 -- 6. p_dest_country: destination country for LTL load
118 -- 7. p_service_code: service level code for LTL load
119 -- 8. p_action_code: LTL load action
120 -- 9. p_tariff_name: LTL load tariff name
121 -- 10. p_user_debug: debug option
122 -- OUT parameters:
123 -- 1. x_status: -1 for no error
124 -- 2. x_error_msg: error message if status <> -1
125 -----------------------------------------------------------------------------------
126 PROCEDURE PROCESS_DATA (
127 ERRBUF OUT NOCOPY VARCHAR2,
128 RETCODE OUT NOCOPY VARCHAR2,
129 p_load_id IN NUMBER,
130 p_src_filename IN VARCHAR2,
131 p_currency IN VARCHAR2,
132 p_uom_code IN VARCHAR2,
133 p_origin_country IN VARCHAR2,
134 p_dest_country IN VARCHAR2,
135 p_service_code IN VARCHAR2,
136 p_action_code IN VARCHAR2,
137 p_tariff_name IN VARCHAR2,
138 p_user_debug IN NUMBER);
139
140 -----------------------------------------------------------------------------
141 -- PROCEDURE LOAD_FILE
142 --
143 -- Purpose: This is the starting point of the bulkloading process. Submits a
144 -- request to a concurrent program, that starts the rate chart loading
145 -- process.
146 --
147 -- IN Parameters
148 -- 1. p_load_id: The load id of the job
149 -- 2. p_src_filename: file name to be loaded
150 -- 3. p_currency: currency for LTL load
151 -- 4. p_uom_code: uom for LTL load
152 -- 5. p_orig_country: origin country for LTL load
153 -- 6. p_dest_country: destination country for LTL load
154 -- 7. p_service_code: service level code for LTL load
155 -- 8. p_action_code: LTL load action
156 -- 9. p_tariff_name: LTL load tariff name
157 -- 10. p_resp_id:
158 -- 11. p_resp_appl_id:
159 -- 12. p_user_id:
160 -- 13. p_user_debug: debug option
161
162 -- Out Parameters
163 -- 1. x_request_id: The request id of the bulkload process
164 -- 2. x_error_msg:
165 -----------------------------------------------------------------------------
166 PROCEDURE LOAD_FILE (
167 p_load_id IN NUMBER,
168 p_src_filename IN VARCHAR2,
169 p_currency IN VARCHAR2,
170 p_uom_code IN VARCHAR2,
171 p_origin_country IN VARCHAR2,
172 p_dest_country IN VARCHAR2,
173 p_service_code IN VARCHAR2,
174 p_action_code IN VARCHAR2,
175 p_tariff_name IN VARCHAR2,
176 p_resp_id IN NUMBER,
177 p_resp_appl_id IN NUMBER,
178 p_user_id IN NUMBER,
179 p_user_debug IN NUMBER,
180 x_request_id OUT NOCOPY NUMBER,
181 x_error_msg OUT NOCOPY VARCHAR2);
182
183 -----------------------------------------------------------------------------
184 -- FUNCTION GET_UPLOAD_DIR
185 -- Purpose: get the upload dir from the global variable
186 -- return the directory
187 -----------------------------------------------------------------------------
188 FUNCTION GET_UPLOAD_DIR RETURN VARCHAR2;
189
190 END FTE_BULKLOAD_PKG;
[Home] [Help]