1 PACKAGE EGO_ITEM_PEOPLE_IMPORT_PKG AS
2 /* $Header: EGOCIPIS.pls 120.2 2005/10/06 03:07:29 srajapar noship $ */
3 ----------------------------------------------------------------------------
4 -- --
5 -- IMPORTANT: --
6 -- --
7 -- Please update the HLD whenever you update the specification --
8 -- HLD Location : --
9 -- http://development.oracle.com/pa/ProjectAttachmentsList.jsp?projectId=45
10 -- Document Name: Item 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_ITEM_PEOPLE_IMPORT_PKG';
27 --
28 -- Batch Sizes for the job
29 --
30 SMALL_BATCH_SIZE NUMBER := 1000;
31 RECOMMENDED_BATCH_SIZE NUMBER := 5000;
32 LARGE_BATCH_SIZE NUMBER := 10000;
33 --
34 -- Return values for RETCODE parameter (standard for concurrent programs)
35 --
36 RETCODE_SUCCESS VARCHAR2(1) := '0';
37 RETCODE_WARNING VARCHAR2(1) := '1';
38 RETCODE_ERROR VARCHAR2(1) := '2';
39 --
40 -- Input values for DEBUG_MODE parameter
41 --
42 DEBUG_MODE_FATAL NUMBER := 1;
43 DEBUG_MODE_ERROR NUMBER := 2;
44 DEBUG_MODE_INFO NUMBER := 3;
45 DEBUG_MODE_DEBUG NUMBER := 4;
46 --
47 -- Scope of logging errors LOG_MODE
48 --
49 LOG_INTO_TABLE_ONLY NUMBER := 1;
50 LOG_INTO_FILE_ONLY NUMBER := 2;
51 LOG_INTO_TABLE_AND_FILE NUMBER := 3;
52 --
53 -- Variables for ORDER BY clause
54 --
55 ORDER_BY_DELETE NUMBER := 10;
56 ORDER_BY_UPDATE NUMBER := 20;
57 ORDER_BY_SYNC NUMBER := 30;
58 ORDER_BY_CREATE NUMBER := 40;
59 ORDER_BY_OTHERS NUMBER := 99;
60 --
61 -- List of PROCESS_STATUS
62 --
63 G_PS_TO_BE_PROCESSED NUMBER := 1;
64 -- ProcessStatus : To Be Processed
65 -- the status when the record is loaded into ego_item_people_intf
66 G_PS_IN_PROCESS NUMBER := 2;
67 --ProcessStatus : In Process
68 -- the status when the record has valid data viz.,
69 -- the user name, item name, organization code, role name,
70 -- grantee_type and grantee_name combination, start_date <= end_date is found
71 G_PS_ERROR NUMBER := 3;
72 --ProcessStatus : Error
73 -- the given record is invalid
74 G_PS_SUCCESS NUMBER := 4;
75 --ProcessStatus : Sucess
76 -- after inserting the record into fnd_grants
77 G_PS_WARNING NUMBER := 5;
78 --ProcessStatus : Warning
79 -- to be implemented in Phase II
80 G_PS_VALID_FND_GRANTS NUMBER := 6;
81 -- ProcessStatus: Valid against FND_GRANTS
82 -- used as the flag to check against the records validated against fnd_grants
83 -- temporary status and will be immediately set to G_PS_TO_BE_PROCESSED or G_PS_ERROR
84 G_PS_TO_BE_REPROCESSED NUMBER := 7;
85 --ProcessStatus : To Be Re-Processed
86
87 -- =================================================================
88 -- Global variables to hold logging attributes
89 -- =================================================================
90
91 g_fd utl_file.file_type; -- Log file descriptor
92 G_TRACE_ON NUMBER := 0; -- Log ON state
93 G_DBG_LVL NUMBER := 0;
94 G_CONCREQ_VALID_FLAG BOOLEAN := FALSE;
95 G_FILE_INIT BOOLEAN := FALSE;
96 G_DBGPATH VARCHAR2(256) := '_';
97
98 -- =========================
99 -- PROCEDURES AND FUNCTIONS
100 -- =========================
101
102 FUNCTION get_curr_dataset_id RETURN NUMBER;
103 -- Start OF comments
104 -- API name : Load Interfance Lines
105 -- TYPE : Public (called by SQL Loader)
106 -- Pre-reqs : None
107 -- FUNCTION : Process and Load interfance lines into FND_GRANTS.
108 -- Errors are populated in MTL_INTERFACE_ERRORS
109
110
111 PROCEDURE load_interface_lines
112 (
113 x_errbuff OUT NOCOPY VARCHAR2,
114 x_retcode OUT NOCOPY VARCHAR2,
115 p_data_set_id IN NUMBER,
116 p_bulk_batch_size IN NUMBER DEFAULT EGO_ITEM_PEOPLE_IMPORT_PKG.RECOMMENDED_BATCH_SIZE,
117 p_delete_lines IN NUMBER DEFAULT EGO_ITEM_PUB.G_INTF_DELETE_NONE,
118 p_debug_mode IN NUMBER DEFAULT EGO_ITEM_PEOPLE_IMPORT_PKG.DEBUG_MODE_ERROR,
119 p_log_mode IN NUMBER DEFAULT EGO_ITEM_PEOPLE_IMPORT_PKG.LOG_INTO_TABLE_ONLY
120 );
121 -- Start OF comments
122 -- API name : Load Interfance Lines
123 -- TYPE : Public (called by Concurrent Program)
124 -- Pre-reqs : None
125 -- FUNCTION : Process and Load interfance lines into FND_GRANTS.
126 -- Errors are populated in MTL_INTERFACE_ERRORS
127 --
128 -- Parameters:
129 -- IN :
130 -- p_data_set_id IN NUMBER
131 -- The job number
132 --
133 -- p_bulk_batch_size IN NUMBER default 1000
134 -- Batch size used for Bulk Processing
135 --
136 -- p_delete_lines IN NUMBER
137 -- How the lines are to be processed in the interface table:
138 -- DELETE_NONE = 0 (Retain all lines)
139 -- DELETE_ALL = 1 (delete all lines)
140 -- DELETE_ERROR = 2 (delete all error lines)
141 -- DELETE_SUCCESS = 3 (delete all successful lines)
142 --
143 -- p_debug_mode IN NUMBER
144 -- To set the level of debugging to log to a file.
145 -- valid values :
146 -- DEBUG_MODE_FATAL = 1
147 -- DEBUG_MODE_ERROR = 2 (for the end users)
148 -- DEBUG_MODE_INFO = 3
149 -- DEBUG_MODE_DEBUG = 4 (for developers)
150 --
151 -- p_log_mode IN NUMBER
152 -- Where the LOG needs to be written
153 -- LOG_TABLE_ONLY = 1 (log data to table only)
154 -- LOG_ERROR_FILE_ONLY = 2 (log data to file only)
155 -- LOG_TABLE_AND_ERROR = 3 (log data to table and file)
156 --
157 -- OUT :
158 -- x_retcode OUT NOCOPY VARCHAR2,
159 -- x_errbuff OUT NOCOPY VARCHAR2
160
161 PROCEDURE purge_interface_lines
162 ( x_errbuff OUT NOCOPY VARCHAR2,
163 x_retcode OUT NOCOPY VARCHAR2,
164 p_data_set_id IN NUMBER,
165 p_closed_date IN VARCHAR2,
166 p_delete_line_type IN NUMBER
167 );
168 -- Start OF comments
169 -- API name : Clean Interface Lines
170 -- TYPE : Public (called by Concurrent Program)
171 -- Pre-reqs : None
172 -- FUNCTION : Removes all the interface lines
173 --
174 -- Parameters:
175 -- IN :
176 -- p_data_set_id IN NUMBER
177 -- The job number
178 --
179 -- p_closed_date IN VARCHAR2
180 -- concurrent program does not let you pass date, only CHAR
181 -- All records on or before the closed date will be deleted
182 --
183 -- Atleast one of the above two parameters should be given else
184 -- the program will fail with insufficient parameters
185 --
186 -- p_delete_line_type IN NUMBER -- mandatory parameter
187 -- How the lines are to be processed in the interface table:
188 -- DELETE_ALL = 1 (delete all lines)
189 -- DELETE_ERROR = 2 (delete all error lines)
190 -- DELETE_SUCCESS = 3 (delete all successful lines)
191 --
192 --
193 -- OUT :
194 -- x_retcode OUT NOCOPY VARCHAR2,
195 -- x_errbuff OUT NOCOPY VARCHAR2
196
197
198 END EGO_ITEM_PEOPLE_IMPORT_PKG;
[Home] [Help]