1 PACKAGE EGO_CHANGE_PEOPLE_IMPORT_PKG AUTHID CURRENT_USER AS
2 /* $Header: EGOCPIS.pls 115.2 2003/01/03 18:15:32 msarkhel noship $ */
3 ------------------------------------------------------------------------------------
4 -- --
5 -- IMPORTANT: --
6 -- --
7 -- Please update the DLD whenever you update the specification --
8 -- DLD Location : --
9 -- http://development.oracle.com/pa/ProjectCentral.jsp?projectId=1001 --
10 -- Document Name: Change People Import HLD --
11 -- --
12 -- Thank You --
13 ------------------------------------------------------------------------------------
14
15
16 -- ===============================================
17 -- CONSTANTS for concurrent program return values
18 -- ===============================================
19 --
20 -- Value for getting the current dataset id for sql loader.
21 --
22 G_CURR_DATASET_ID NUMBER := -1;
23 --
24 -- Package Name
25 --
26 G_PACKAGE_NAME VARCHAR2(30) := 'EGO_CHANGE_PEOPLE_IMPORT_PKG';
27 --
28 -- Return values for RETCODE parameter (standard for concurrent programs)
29 --
30 RETCODE_SUCCESS NUMBER := 0;
31 RETCODE_WARNING NUMBER := 1;
32 RETCODE_ERROR NUMBER := 2;
33 --
34 -- Input values for DEBUG_MODE parameter
35 --
36 DEBUG_MODE_FATAL NUMBER := 1;
37 DEBUG_MODE_ERROR NUMBER := 2;
38 DEBUG_MODE_INFO NUMBER := 3;
39 DEBUG_MODE_DEBUG NUMBER := 4;
40 --
41 -- Input values for DELETE_LINES
42 --
43 DELETE_NONE NUMBER := 0;
44 DELETE_ALL NUMBER := 1;
45 DELETE_ERROR NUMBER := 2;
46 DELETE_SUCCESS NUMBER := 3;
47 --
48 -- Scope of logging errors LOG_MODE
49 --
50 LOG_INTO_FILE_ONLY NUMBER := 1;
51 LOG_INTO_FILE_AND_TABLE NUMBER := 2;
52 LOG_INTO_TABLE_ONLY NUMBER := 3;
53 --
54 -- Variables for ORDER BY clause
55 --
56 ORDER_BY_DELETE NUMBER := 10;
57 ORDER_BY_UPDATE NUMBER := 20;
58 ORDER_BY_SYNC NUMBER := 30;
59 ORDER_BY_CREATE NUMBER := 40;
60 ORDER_BY_OTHERS NUMBER := 99;
61 --
62 -- List of PROCESS_STATUS
63 --
64 G_PS_TO_BE_PROCESSED NUMBER := 1;
65 -- ProcessStatus : To Be Processed
66 -- the status when the record is loaded into eng_change_people_intf
67 G_PS_IN_PROCESS NUMBER := 2;
68 --ProcessStatus : In Process
69 -- the status when the record has valid data viz.,
70 -- the user name, item name, organization code, role name,
71 -- grantee_type and grantee_name combination, start_date <= end_date is found
72 G_PS_ERROR NUMBER := 3;
73 --ProcessStatus : Error
74 -- the given record is invalid
75 G_PS_SUCCESS NUMBER := 4;
76 --ProcessStatus : Sucess
77 -- after inserting the record into fnd_grants
78
79 -- =================================================================
80 -- Global variables to hold logging attributes
81 -- =================================================================
82
83 g_fd utl_file.file_type; -- Log file descriptor
84 G_TRACE_ON NUMBER := 0; -- Log ON state
85 G_DBG_LVL NUMBER := 0;
86 G_CONCREQ_VALID_FLAG BOOLEAN := FALSE;
87 G_FILE_INIT BOOLEAN := FALSE;
88 G_DBGPATH VARCHAR2(256) := '_';
89
90 -- =========================
91 -- PROCEDURES AND FUNCTIONS
92 -- =========================
93
94 FUNCTION get_curr_dataset_id RETURN NUMBER;
95 -- Start OF comments
96 -- API name : Load Interfance Lines
97 -- TYPE : Public (called by SQL Loader)
98 -- Pre-reqs : None
99 -- FUNCTION : Process and Load interfance lines into FND_GRANTS.
100 -- Errors are populated in MTL_INTERFACE_ERRORS
101
102
103 PROCEDURE load_interface_lines
104 (
105 x_retcode IN OUT NOCOPY VARCHAR2,
106 x_errbuff IN OUT NOCOPY VARCHAR2,
107 p_data_set_id IN NUMBER,
108 p_bulk_batch_size IN NUMBER ,
109 p_delete_lines IN NUMBER ,
110 p_debug_mode IN NUMBER ,
111 p_log_mode IN NUMBER
112 );
113 -- Start OF comments
114 -- API name : Load Interfance Lines
115 -- TYPE : Public (called by Concurrent Program)
116 -- Pre-reqs : None
117 -- FUNCTION : Process and Load interfance lines into FND_GRANTS.
118 -- Errors are populated in MTL_INTERFACE_ERRORS
119 --
120 -- Parameters:
121 -- IN :
122 -- p_data_set_id IN NUMBER
123 -- The job number
124 --
125 -- p_bulk_batch_size IN NUMBER default 1000
126 -- Batch size used for Bulk Processing
127 --
128 -- p_delete_lines IN NUMBER
129 -- How the lines are to be processed in the interface table:
130 -- DELETE_NONE = 0 (Retain all lines)
131 -- DELETE_ALL = 1 (delete all lines)
132 -- DELETE_ERROR = 2 (delete all error lines)
133 -- DELETE_SUCCESS = 3 (delete all successful lines)
134 --
135 -- p_debug_mode IN NUMBER
136 -- To set the level of debugging to log to a file.
137 -- valid values :
138 -- DEBUG_MODE_FATAL = 1
139 -- DEBUG_MODE_ERROR = 2 (for the end users)
140 -- DEBUG_MODE_INFO = 3
141 -- DEBUG_MODE_DEBUG = 4 (for developers)
142 --
143 -- p_log_mode IN NUMBER
144 -- Where the LOG needs to be written
145 -- LOG_TABLE_ONLY = 1 (log data to table only)
146 -- LOG_ERROR_FILE_ONLY = 2 (log data to file only)
147 -- LOG_TABLE_AND_ERROR = 3 (log data to table and file)
148 --
149 -- OUT :
150 -- x_retcode OUT VARCHAR2
151 -- x_errbuff OUT VARCHAR2
152
153 PROCEDURE purge_interface_lines
154 ( p_data_set_id IN NUMBER,
155 p_closed_date IN DATE,
156 p_delete_line_type IN NUMBER,
157 x_retcode OUT NOCOPY VARCHAR2,
158 x_errbuff OUT NOCOPY VARCHAR2
159 );
160 -- Start OF comments
161 -- API name : Clean Interface Lines
162 -- TYPE : Public (called by Concurrent Program)
163 -- Pre-reqs : None
164 -- FUNCTION : Removes all the interface lines
165 --
166 -- Parameters:
167 -- IN :
168 -- p_data_set_id IN NUMBER
169 -- The job number
170 --
171 -- p_closed_date IN DATE
172 -- All records on or before the closed date will be deleted
173 --
174 -- Atleast one of the above two parameters should be given else
175 -- the program will fail with insufficient parameters
176 --
177 -- p_delete_line_type IN NUMBER -- mandatory parameter
178 -- How the lines are to be processed in the interface table:
179 -- DELETE_ALL = 1 (delete all lines)
180 -- DELETE_ERROR = 2 (delete all error lines)
181 -- DELETE_SUCCESS = 3 (delete all successful lines)
182 --
183 --
184 -- OUT :
185 -- x_retcode OUT VARCHAR2
186 -- x_errbuff OUT VARCHAR2
187
188
189 END EGO_CHANGE_PEOPLE_IMPORT_PKG;