1 PACKAGE CN_IMPORT_PVT AUTHID CURRENT_USER AS
2 -- $Header: cnvimps.pls 120.2 2005/08/07 23:03:56 vensrini noship $
3
4 -- * ------------------------------------------------------------------+
5 -- Record Type Definition
6 -- * ------------------------------------------------------------------+
7 -- Corresponding type def for the PL/SQL data type "JTF_VARCHAR2_TABLE_1000"
8 -- since Rosetta does not support nested tables.
9 TYPE CHAR_DATA_SET_TYPE IS
10 TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
11
12 --
13 -- Corresponding type def for the PL/SQL data type "JTF_NUMBER_TABLE"
14 -- since Rosetta does not support nested tables.
15 --
16 TYPE NUM_DATA_SET_TYPE IS
17 TABLE OF NUMBER INDEX BY BINARY_INTEGER;
18
19 -- Start of comments
20 -- API name : Import_Data
21 -- Type : Private.
22 -- Function : Main program to call all the concurrent programs
23 -- to transfer data from datafile to stage table then to
24 -- destination table
25 -- Pre-reqs : None.
26 -- Version : Current version 1.0
27 -- End of comments
28
29 PROCEDURE Import_Data
30 ( p_api_version IN NUMBER ,
31 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
32 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
33 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
34 x_return_status OUT NOCOPY VARCHAR2 ,
35 x_msg_count OUT NOCOPY NUMBER ,
36 x_msg_data OUT NOCOPY VARCHAR2 ,
37 p_imp_header_id IN NUMBER,
38 p_user_id IN NUMBER, -- setting the session context.
39 p_resp_id IN NUMBER, -- setting the session context.
40 p_app_id IN NUMBER, -- setting the session context.
41 p_control_file IN VARCHAR2,
42 x_request_id OUT NOCOPY NUMBER,
43 p_org_id IN NUMBER
44 );
45
46 -- Start of comments
47 -- API name : Export_Data
48 -- Type : Private.
49 -- Function : Main program to call all the concurrent programs
50 -- to transfer data from destination file to stage table
51 -- Pre-reqs : None.
52 -- Version : Current version 1.0
53 -- End of comments
54 PROCEDURE Export_Data
55 ( p_api_version IN NUMBER ,
56 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
57 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
58 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
59 x_return_status OUT NOCOPY VARCHAR2 ,
60 x_msg_count OUT NOCOPY NUMBER ,
61 x_msg_data OUT NOCOPY VARCHAR2 ,
62 p_imp_header_id IN NUMBER,
63 p_user_id IN NUMBER, -- setting the session context.
64 p_resp_id IN NUMBER, -- setting the session context.
65 p_app_id IN NUMBER, -- setting the session context.
66 x_request_id OUT NOCOPY NUMBER,
67 p_org_id IN NUMBER
68 );
69
70 -- Start of comments
71 -- API name : Client_Stage_data
72 -- Type : Private.
73 -- Function : Main program to call CN_IMPORT_CLIENT_PVT
74 -- to transfer data from datafile to stage table
75 -- Pre-reqs : None.
76 -- Version : Current version 1.0
77 -- End of comments
78
79 PROCEDURE Client_Stage_Data
80 ( p_api_version IN NUMBER ,
81 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
82 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
83 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
84 x_return_status OUT NOCOPY VARCHAR2 ,
85 x_msg_count OUT NOCOPY NUMBER ,
86 x_msg_data OUT NOCOPY VARCHAR2 ,
87 p_imp_header_id IN NUMBER,
88 p_data IN CHAR_DATA_SET_TYPE,
89 p_row_count IN NUMBER,
90 p_map_obj_ver IN NUMBER,
91 p_org_id IN NUMBER
92 );
93
94 -- --------------------------------------------------------+
95 -- This program invokes sql*loader from concurrent program
96 -- to populate the data from the data file to the OIC application.
97 -- --------------------------------------------------------+
98 PROCEDURE Server_Stage_Data
99 (errbuf OUT NOCOPY VARCHAR2,
100 retcode OUT NOCOPY NUMBER,
101 p_imp_header_id IN NUMBER,
102 p_control_file IN VARCHAR2,
103 p_org_id IN NUMBER
104 );
105
106 -- --------------------------------------------------------+
107 -- This program invokes concurrent program base on import type
108 -- to populate the data from the staging table into destination table
109 -- --------------------------------------------------------+
110 PROCEDURE Load_Data
111 (errbuf OUT NOCOPY VARCHAR2,
112 retcode OUT NOCOPY NUMBER,
113 p_imp_header_id IN NUMBER,
114 p_org_id IN NUMBER
115 );
116
117 -- --------------------------------------------------------+
118 -- update_imp_headers
119 --
120 -- This procedure will update cn_imp_headers status,processed_row
121 -- and failed_row
122 -- --------------------------------------------------------+
123 PROCEDURE update_imp_headers
124 (p_imp_header_id IN NUMBER,
125 p_status_code IN VARCHAR2,
126 p_staged_row IN NUMBER := NULL ,
127 p_processed_row IN NUMBER := NULL ,
128 p_failed_row IN NUMBER := NULL );
129
130 -- --------------------------------------------------------+
131 -- update_imp_lines
132 --
133 -- This procedure will update cn_imp_lines status and error code
134 -- --------------------------------------------------------+
135 PROCEDURE update_imp_lines
136 (p_imp_line_id IN NUMBER,
137 p_status_code IN VARCHAR2,
138 p_error_code IN VARCHAR2,
139 p_error_msg IN VARCHAR2 := NULL);
140
141 -- --------------------------------------------------------+
142 -- build_error_rec
143 --
144 -- This procedure will generate the list of source column headers for error
145 -- reporting. It will also generate a SQL statement which will be used to
146 -- retrieve target column values
147 -- --------------------------------------------------------+
148 PROCEDURE build_error_rec
149 (p_imp_header_id IN NUMBER,
150 x_header_list OUT NOCOPY VARCHAR2,
151 x_sql_stmt OUT NOCOPY VARCHAR2 );
152
153 -- --------------------------------------------------------+
154 -- write_error_rec
155 --
156 -- This procedure will write the list of source column headers to process log
157 -- also retrieve the value of corresponding target columns and write to log
158 -- --------------------------------------------------------+
159 PROCEDURE write_error_rec
160 (p_imp_header_id IN NUMBER,
161 p_imp_line_id IN NUMBER,
162 p_header_list IN VARCHAR2,
163 p_sql_stmt IN VARCHAR2 );
164
165 END CN_IMPORT_PVT;