[Home] [Help]
PACKAGE BODY: APPS.IMC_CSV_LOAD
Source
1 PACKAGE BODY IMC_CSV_LOAD AS
2 /* $Header: IMCLOADB.pls 120.12.12000000.2 2007/10/12 14:18:27 idali ship $ */
3 PROCEDURE LOAD_DETAILS(loadId NUMBER, batchId NUMBER) IS
4 p_init_msg_list VARCHAR2(25) := FND_API.G_FALSE;
5 x_return_status VARCHAR2(50);
6 x_msg_count NUMBER;
7 x_msg_data VARCHAR2(100);
8 load_id imc_csv_interface_fields.LOAD_ID%TYPE;
9 BATCH_ID imc_csv_interface_fields.BATCH_ID%TYPE;
10 PARTY_REC_ID imc_csv_interface_fields.PARTY_REC_ID%TYPE;
11 REC_STATUS imc_csv_interface_fields.REC_STATUS%TYPE;
12 PARTY_ORIG_SYSTEM HZ_IMP_PARTIES_INT.PARTY_ORIG_SYSTEM%TYPE;
13 PARTY_ORIG_SYSTEM_REFERENCE HZ_IMP_PARTIES_INT.PARTY_ORIG_SYSTEM_REFERENCE%TYPE;
14 PARTY_TYPE imc_csv_interface_fields.PARTY_TYPE%TYPE;
15 ORGANIZATION_NAME imc_csv_interface_fields.ORGANIZATION_NAME%TYPE;
16 CEO_NAME imc_csv_interface_fields.CEO_NAME%TYPE;
17 CEO_TITLE imc_csv_interface_fields.CEO_TITLE%TYPE;
18 PRINCIPAL_NAME imc_csv_interface_fields.PRINCIPAL_NAME%TYPE;
19 PRINCIPAL_TITLE imc_csv_interface_fields.PRINCIPAL_TITLE%TYPE;
20 LEGAL_STATUS imc_csv_interface_fields.LEGAL_STATUS%TYPE;
21 CONTROL_YR imc_csv_interface_fields.CONTROL_YR%TYPE;
22 EMPLOYEES_TOTAL imc_csv_interface_fields.EMPLOYEES_TOTAL%TYPE;
23 HQ_BRANCH_IND imc_csv_interface_fields.HQ_BRANCH_IND%TYPE;
24 BRANCH_FLAG imc_csv_interface_fields.BRANCH_FLAG%TYPE;
25 OOB_IND imc_csv_interface_fields.OOB_IND%TYPE;
26 TAX_REFERENCE imc_csv_interface_fields.TAX_REFERENCE%TYPE;
27 GSA_INDICATOR_FLAG imc_csv_interface_fields.GSA_INDICATOR_FLAG%TYPE;
28 JGZZ_FISCAL_CODE imc_csv_interface_fields.JGZZ_FISCAL_CODE%TYPE;
29 ANALYSIS_FY imc_csv_interface_fields.ANALYSIS_FY%TYPE;
30 FISCAL_YEAREND_MONTH imc_csv_interface_fields.FISCAL_YEAREND_MONTH%TYPE;
31 CURR_FY_POTENTIAL_REVENUE imc_csv_interface_fields.CURR_FY_POTENTIAL_REVENUE%TYPE;
32 NEXT_FY_POTENTIAL_REVENUE imc_csv_interface_fields.NEXT_FY_POTENTIAL_REVENUE%TYPE;
33 YEAR_ESTABLISHED imc_csv_interface_fields.YEAR_ESTABLISHED%TYPE;
34 MISSION_STATEMENT imc_csv_interface_fields.MISSION_STATEMENT%TYPE;
35 ORGANIZATION_TYPE imc_csv_interface_fields.ORGANIZATION_TYPE%TYPE;
36 BUSINESS_SCOPE imc_csv_interface_fields.BUSINESS_SCOPE%TYPE;
37 KNOWN_AS imc_csv_interface_fields.KNOWN_AS%TYPE;
38 KNOWN_AS2 imc_csv_interface_fields.KNOWN_AS2%TYPE;
39 KNOWN_AS3 imc_csv_interface_fields.KNOWN_AS3%TYPE;
40 KNOWN_AS4 imc_csv_interface_fields.KNOWN_AS4%TYPE;
41 KNOWN_AS5 imc_csv_interface_fields.KNOWN_AS5%TYPE;
42 LOCAL_BUS_IDEN_TYPE imc_csv_interface_fields.LOCAL_BUS_IDEN_TYPE%TYPE;
43 LOCAL_BUS_IDENTIFIER imc_csv_interface_fields.LOCAL_BUS_IDENTIFIER%TYPE;
44 PREF_FUNCTIONAL_CURRENCY imc_csv_interface_fields.PREF_FUNCTIONAL_CURRENCY%TYPE;
45 REGISTRATION_TYPE imc_csv_interface_fields.REGISTRATION_TYPE%TYPE;
46 PARENT_SUB_IND imc_csv_interface_fields.PARENT_SUB_IND%TYPE;
47 INCORP_YEAR imc_csv_interface_fields.INCORP_YEAR%TYPE;
48 PUBLIC_PRIVATE_OWNERSHIP_FLAG imc_csv_interface_fields.PUBLIC_PRIVATE_OWNERSHIP_FLAG%TYPE;
49 TOTAL_PAYMENTS imc_csv_interface_fields.TOTAL_PAYMENTS%TYPE;
50 DUNS_NUMBER_C imc_csv_interface_fields.DUNS_NUMBER_C%TYPE;
51 CLASS_CODE imc_csv_interface_fields.CLASS_CODE%TYPE;
52 CLASS_CATEGORY imc_csv_interface_fields.CLASS_CATEGORY%TYPE;
53 PERSON_PRE_NAME_ADJUNCT imc_csv_interface_fields.PERSON_PRE_NAME_ADJUNCT%TYPE;
54 PERSON_FIRST_NAME imc_csv_interface_fields.PERSON_FIRST_NAME%TYPE;
55 PERSON_MIDDLE_NAME imc_csv_interface_fields.PERSON_MIDDLE_NAME%TYPE;
56 PERSON_LAST_NAME imc_csv_interface_fields.PERSON_LAST_NAME%TYPE;
57 PERSON_NAME_SUFFIX imc_csv_interface_fields.PERSON_NAME_SUFFIX%TYPE;
58 PERSON_TITLE imc_csv_interface_fields.PERSON_TITLE%TYPE;
59 PERSON_ACADEMIC_TITLE imc_csv_interface_fields.PERSON_ACADEMIC_TITLE%TYPE;
60 PERSON_PREVIOUS_LAST_NAME imc_csv_interface_fields.PERSON_PREVIOUS_LAST_NAME%TYPE;
61 PERSON_INITIALS imc_csv_interface_fields.PERSON_INITIALS%TYPE;
62 PERSON_NAME_PHONETIC imc_csv_interface_fields.PERSON_NAME_PHONETIC%TYPE;
63 PERSON_FIRST_NAME_PHONETIC imc_csv_interface_fields.PERSON_FIRST_NAME_PHONETIC%TYPE;
64 PERSON_MIDDLE_NAME_PHONETIC imc_csv_interface_fields.PERSON_MIDDLE_NAME_PHONETIC%TYPE;
65 PERSON_LAST_NAME_PHONETIC imc_csv_interface_fields.PERSON_LAST_NAME_PHONETIC%TYPE;
66 PERSON_IDEN_TYPE imc_csv_interface_fields.PERSON_IDEN_TYPE%TYPE;
67 PERSON_IDENTIFIER imc_csv_interface_fields.PERSON_IDENTIFIER%TYPE;
68 DATE_OF_BIRTH imc_csv_interface_fields.DATE_OF_BIRTH%TYPE;
69 PLACE_OF_BIRTH imc_csv_interface_fields.PLACE_OF_BIRTH%TYPE;
70 DATE_OF_DEATH imc_csv_interface_fields.DATE_OF_DEATH%TYPE;
71 GENDER imc_csv_interface_fields.GENDER%TYPE;
72 DECLARED_ETHNICITY imc_csv_interface_fields.DECLARED_ETHNICITY%TYPE;
73 MARITAL_STATUS imc_csv_interface_fields.MARITAL_STATUS%TYPE;
74 MARITAL_STATUS_EFFECTIVE_DATE imc_csv_interface_fields.MARITAL_STATUS_EFFECTIVE_DATE%TYPE;
75 PERSONAL_INCOME imc_csv_interface_fields.PERSONAL_INCOME%TYPE;
76 HEAD_OF_HOUSEHOLD_FLAG imc_csv_interface_fields.HEAD_OF_HOUSEHOLD_FLAG%TYPE;
77 HOUSEHOLD_INCOME imc_csv_interface_fields.HOUSEHOLD_INCOME%TYPE;
78 HOUSEHOLD_SIZE imc_csv_interface_fields.HOUSEHOLD_SIZE%TYPE;
79 RENT_OWN_IND imc_csv_interface_fields.RENT_OWN_IND%TYPE;
80 COUNTRY imc_csv_interface_fields.COUNTRY%TYPE;
81 ADDRESS1 imc_csv_interface_fields.ADDRESS1%TYPE;
82 ADDRESS2 imc_csv_interface_fields.ADDRESS2%TYPE;
83 ADDRESS3 imc_csv_interface_fields.ADDRESS3%TYPE;
84 ADDRESS4 imc_csv_interface_fields.ADDRESS4%TYPE;
85 CITY imc_csv_interface_fields.CITY%TYPE;
86 POSTAL_CODE imc_csv_interface_fields.POSTAL_CODE%TYPE;
87 STATE imc_csv_interface_fields.STATE%TYPE;
88 PROVINCE imc_csv_interface_fields.PROVINCE%TYPE;
89 COUNTY imc_csv_interface_fields.COUNTY%TYPE;
90 ADDRESS_LINES_PHONETIC imc_csv_interface_fields.ADDRESS_LINES_PHONETIC%TYPE;
91 LOCATION_DIRECTIONS HZ_IMP_ADDRESSES_INT.LOCATION_DIRECTIONS%TYPE;
92 DESCRIPTION imc_csv_interface_fields.DESCRIPTION%TYPE;
93 SALES_TAX_GEOCODE HZ_IMP_ADDRESSES_INT.SALES_TAX_GEOCODE%TYPE;
94 PRIMARY_FLAG HZ_IMP_ADDRESSES_INT.PRIMARY_FLAG%TYPE;
95 PARTY_SITE_NAME imc_csv_interface_fields.PARTY_SITE_NAME%TYPE;
96 CONTACT_POINT_TYPE imc_csv_interface_fields.CONTACT_POINT_TYPE%TYPE;
97 EMAIL_FORMAT imc_csv_interface_fields.EMAIL_FORMAT%TYPE;
98 EMAIL_ADDRESS imc_csv_interface_fields.EMAIL_ADDRESS%TYPE;
99 PHONE_AREA_CODE imc_csv_interface_fields.PHONE_AREA_CODE%TYPE;
100 PHONE_COUNTRY_CODE imc_csv_interface_fields.PHONE_COUNTRY_CODE%TYPE;
101 PHONE_NUMBER imc_csv_interface_fields.PHONE_NUMBER%TYPE;
102 PHONE_EXTENSION imc_csv_interface_fields.PHONE_EXTENSION%TYPE;
103 PHONE_LINE_TYPE imc_csv_interface_fields.PHONE_LINE_TYPE%TYPE;
104 TELEX_NUMBER imc_csv_interface_fields.TELEX_NUMBER%TYPE;
105 WEB_TYPE imc_csv_interface_fields.WEB_TYPE%TYPE;
106 URL imc_csv_interface_fields.URL%TYPE;
107 RAW_PHONE_NUMBER imc_csv_interface_fields.RAW_PHONE_NUMBER%TYPE;
108 CONTACT_POINT_PURPOSE imc_csv_interface_fields.CONTACT_POINT_PURPOSE%TYPE;
109 IDENTIFYING_ADDRESS_FLAG VARCHAR2(100);
110 LANGUAGE imc_csv_interface_fields.LANGUAGE%TYPE;
111 DEPARTMENT_CODE imc_csv_interface_fields.DEPARTMENT_CODE%TYPE;
112 DEPARTMENT imc_csv_interface_fields.DEPARTMENT%TYPE;
113 TITLE imc_csv_interface_fields.TITLE%TYPE;
114 JOB_TITLE imc_csv_interface_fields.JOB_TITLE%TYPE;
115 LINE_OF_BUSINESS imc_csv_interface_fields.LINE_OF_BUSINESS%TYPE;
116 CREATION_DATE imc_csv_interface_fields.CREATION_DATE%TYPE;
117 CREATED_BY imc_csv_interface_fields.CREATED_BY%TYPE;
118 LAST_UPDATE_DATE imc_csv_interface_fields.LAST_UPDATE_DATE%TYPE;
119 LAST_UPDATED_BY imc_csv_interface_fields.LAST_UPDATED_BY%TYPE;
120 LAST_UPDATE_LOGIN imc_csv_interface_fields.LAST_UPDATE_LOGIN%TYPE;
121
122 date_format imc_csv_load_details.DATE_FORMAT%TYPE;
123 ADDRESS_FLAG imc_csv_load_details.ADDRESS_FLAG%TYPE;
124 CONTACT_POINTS_FLAG imc_csv_load_details.CONTACT_POINTS_FLAG%TYPE;
125 CLASSIFICATION_FLAG imc_csv_load_details.CLASSIFICATION_FLAG%TYPE;
126 CONTACT_FLAG VARCHAR2(10);
127
128 birthdate HZ_IMP_PARTIES_INT.DATE_OF_BIRTH%TYPE;
129 deathdate HZ_IMP_PARTIES_INT.DATE_OF_DEATH%TYPE;
130 maritaldate HZ_IMP_PARTIES_INT.MARITAL_STATUS_EFFECTIVE_DATE%TYPE;
131 --Data Type validations for CONTROL_YR,EMPLOYEES_TOTAL,INCORP_YEAR,TOTAL_PAYMENTS,YEAR_ESTABLISHED
132 control_year HZ_IMP_PARTIES_INT.CONTROL_YR%TYPE;
133 emp_total HZ_IMP_PARTIES_INT.EMPLOYEES_TOTAL%TYPE;
134 incorporation_year HZ_IMP_PARTIES_INT.INCORP_YEAR%TYPE;
135 total_pays HZ_IMP_PARTIES_INT.TOTAL_PAYMENTS%TYPE;
136 yr_established HZ_IMP_PARTIES_INT.YEAR_ESTABLISHED%TYPE;
137 per_income HZ_IMP_PARTIES_INT.PERSONAL_INCOME%TYPE;
138 house_income HZ_IMP_PARTIES_INT.HOUSEHOLD_INCOME%TYPE;
139 house_size HZ_IMP_PARTIES_INT.HOUSEHOLD_SIZE%TYPE;
140
141 lookup_code_count VARCHAR2(50);
142 CNT_ORIG_SYSTEM_REFERENCE number;
143 CNTORG_ORIG_SYSTEM_REFERENCE number;
144 SITE_ORIG_SYSTEM_REFERENCE number;
145 CP_ORIG_SYSTEM_REFERENCE number;
146 PARTY_NAME imc_csv_interface_fields.ORGANIZATION_NAME%TYPE;
147
148 TYPE org_cnt_rec_type IS RECORD (
149 org_name imc_csv_interface_fields.ORGANIZATION_NAME%TYPE,
150 original_sys_ref NUMBER);
151
152 TYPE org_contact_type IS TABLE OF org_cnt_rec_type INDEX BY BINARY_INTEGER;
153
154 org_contact org_contact_type;
155 org_contact_rec org_cnt_rec_type;
156 old_org_name imc_csv_interface_fields.ORGANIZATION_NAME%TYPE;
157 orig_sys_ref NUMBER;
158 old_contact_org VARCHAR2(100);
159 i NUMBER;
160
161 cursor get_details(loadId number, batchId number) is
162 select LOAD_ID,BATCH_ID,PARTY_REC_ID,REC_STATUS,PARTY_TYPE,ORGANIZATION_NAME,CEO_NAME,
163 CEO_TITLE,PRINCIPAL_NAME,PRINCIPAL_TITLE,LEGAL_STATUS,CONTROL_YR,EMPLOYEES_TOTAL,
164 HQ_BRANCH_IND,BRANCH_FLAG,OOB_IND,TAX_REFERENCE,GSA_INDICATOR_FLAG,
165 JGZZ_FISCAL_CODE,ANALYSIS_FY,FISCAL_YEAREND_MONTH,CURR_FY_POTENTIAL_REVENUE,NEXT_FY_POTENTIAL_REVENUE,
166 YEAR_ESTABLISHED,MISSION_STATEMENT,ORGANIZATION_TYPE,BUSINESS_SCOPE,KNOWN_AS,KNOWN_AS2,KNOWN_AS3,KNOWN_AS4,
167 KNOWN_AS5,LOCAL_BUS_IDEN_TYPE,LOCAL_BUS_IDENTIFIER,PREF_FUNCTIONAL_CURRENCY,REGISTRATION_TYPE,PARENT_SUB_IND,
168 INCORP_YEAR,LINE_OF_BUSINESS,PUBLIC_PRIVATE_OWNERSHIP_FLAG,TOTAL_PAYMENTS,DUNS_NUMBER_C,PERSON_PRE_NAME_ADJUNCT,
169 PERSON_FIRST_NAME,PERSON_MIDDLE_NAME,PERSON_LAST_NAME,PERSON_NAME_SUFFIX,PERSON_TITLE,PERSON_ACADEMIC_TITLE,
170 PERSON_PREVIOUS_LAST_NAME,PERSON_INITIALS,PERSON_NAME_PHONETIC,PERSON_FIRST_NAME_PHONETIC,PERSON_MIDDLE_NAME_PHONETIC,
171 PERSON_LAST_NAME_PHONETIC,PERSON_IDEN_TYPE,PERSON_IDENTIFIER,DATE_OF_BIRTH,PLACE_OF_BIRTH,DATE_OF_DEATH,GENDER,
172 DECLARED_ETHNICITY,MARITAL_STATUS,MARITAL_STATUS_EFFECTIVE_DATE,PERSONAL_INCOME,HEAD_OF_HOUSEHOLD_FLAG,
173 HOUSEHOLD_INCOME,HOUSEHOLD_SIZE,RENT_OWN_IND,COUNTRY,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,CITY,POSTAL_CODE,STATE,
174 PROVINCE,COUNTY,ADDRESS_LINES_PHONETIC,DESCRIPTION,LANGUAGE,PARTY_SITE_NAME,CONTACT_POINT_TYPE,EMAIL_FORMAT,
175 EMAIL_ADDRESS,PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,PHONE_LINE_TYPE,WEB_TYPE,URL,
176 RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,CLASS_CATEGORY,CLASS_CODE,DEPARTMENT_CODE,DEPARTMENT,TITLE,JOB_TITLE,
177 TELEX_NUMBER,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN
178 from imc_csv_interface_fields where load_id = loadId and batch_id=batchId
179 and (rec_status='E' OR nvl(rec_status,'NULL')='NULL');
180
181 cursor get_load_details(loadId number, batchId number) is
182 select date_format,ADDRESS_FLAG,CONTACT_POINTS_FLAG,CLASSIFICATION_FLAG
183 from imc_csv_load_details where load_id = loadId and batch_id=batchId;
184
185 cursor get_next_ref_id is
186 select imc_csv_orig_ref_s.NEXTVAL from dual;
187
188 -- Bug 4310048/4315682 : do not show invalid lookups
189 -- Add check for ENABLED_FLAG and END_DATE_ACTIVE
190
191 cursor legal_status_code(legal_status_cd VARCHAR2) is
192 select count(*) from ar_lookups where lookup_type='LEGAL_STATUS' and lookup_code=legal_status_cd
193 AND ENABLED_FLAG = 'Y'
194 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)) ;
195
196 cursor hq_branch(hq_branch_ind_cd VARCHAR2) is
197 select count(*) from ar_lookups where lookup_type='HQ_BRANCH_IND' and lookup_code=hq_branch_ind_cd
198 AND ENABLED_FLAG = 'Y'
199 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)) ;
200
201 cursor FISCAL_YREND_MONTH(fiscal_yearend_month_cd VARCHAR2) is
202 select count(*) from ar_lookups where lookup_type='MONTH' and lookup_code = fiscal_yearend_month_cd
203 AND ENABLED_FLAG = 'Y'
204 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)) ;
205
206 cursor REGISTRY_TYPE(registration_type_cd VARCHAR2) is
207 select count(*) from ar_lookups where lookup_type='REGISTRATION_TYPE' and lookup_code=registration_type_cd
208 AND ENABLED_FLAG = 'Y'
209 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)) ;
210
211 cursor contact_title(pre_name_adjunct VARCHAR2) is
212 select count(*) from ar_lookups where lookup_type='CONTACT_TITLE' and lookup_code=pre_name_adjunct
213 AND ENABLED_FLAG = 'Y'
214 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)) ;
215
216 cursor marry_status(marital_status_cd VARCHAR2) is
217 select count(*) from ar_lookups where lookup_type='MARITAL_STATUS' and lookup_code=marital_status_cd
218 AND ENABLED_FLAG = 'Y'
219 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)) ;
220
221 cursor own_rent(own_rent_cd VARCHAR2) is
222 select count(*) from ar_lookups where lookup_type='OWN_RENT_IND' and lookup_code=own_rent_cd
223 AND ENABLED_FLAG = 'Y'
224 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)) ;
225
226 cursor contact_pnt_type(contact_pnt_type_cd VARCHAR2) is
227 select count(*) from ar_lookups where lookup_type='COMMUNICATION_TYPE' and lookup_code=contact_pnt_type_cd
228 AND ENABLED_FLAG = 'Y'
229 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)) ;
230
231 -- Bug 4310158: Should only allow valid country code
232 cursor country_code(country_cd VARCHAR2) is
233 select count(*) from FND_TERRITORIES where TERRITORY_CODE=country_cd AND OBSOLETE_FLAG = 'N';
234
235 cursor language_code(language_cd VARCHAR2) is
236 select count(*) from FND_LANGUAGES where LANGUAGE_CODE IN ('B', 'I') and LANGUAGE_CODE=language_cd;
237
238 cursor clss_category(p_class_category VARCHAR2) is
239 select count(*) from hz_class_categories where class_category = p_class_category;
240
241 cursor clss_code(class_category VARCHAR2,class_code VARCHAR2) is
242 select count(*) from ar_lookups where lookup_type = class_category and lookup_code = class_code
243 AND ENABLED_FLAG = 'Y'
244 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)) ;
245
246 BEGIN
247 open get_load_details(loadId,batchId);
248 fetch get_load_details into date_format,ADDRESS_FLAG,CONTACT_POINTS_FLAG,CLASSIFICATION_FLAG;
249 close get_load_details ;
250
251 open get_details(loadId,batchId);
252 LOOP
253 fetch get_details into
254 LOAD_ID,BATCH_ID,PARTY_REC_ID,REC_STATUS,PARTY_TYPE,ORGANIZATION_NAME,CEO_NAME,
255 CEO_TITLE,PRINCIPAL_NAME,PRINCIPAL_TITLE,LEGAL_STATUS,CONTROL_YR,EMPLOYEES_TOTAL,
256 HQ_BRANCH_IND,BRANCH_FLAG,OOB_IND,TAX_REFERENCE,GSA_INDICATOR_FLAG,
257 JGZZ_FISCAL_CODE,ANALYSIS_FY,FISCAL_YEAREND_MONTH,CURR_FY_POTENTIAL_REVENUE,NEXT_FY_POTENTIAL_REVENUE,
258 YEAR_ESTABLISHED,MISSION_STATEMENT,ORGANIZATION_TYPE,BUSINESS_SCOPE,KNOWN_AS,KNOWN_AS2,KNOWN_AS3,KNOWN_AS4,
259 KNOWN_AS5,LOCAL_BUS_IDEN_TYPE,LOCAL_BUS_IDENTIFIER,PREF_FUNCTIONAL_CURRENCY,REGISTRATION_TYPE,PARENT_SUB_IND,
260 INCORP_YEAR,LINE_OF_BUSINESS,PUBLIC_PRIVATE_OWNERSHIP_FLAG,TOTAL_PAYMENTS,DUNS_NUMBER_C,PERSON_PRE_NAME_ADJUNCT,
261 PERSON_FIRST_NAME,PERSON_MIDDLE_NAME,PERSON_LAST_NAME,PERSON_NAME_SUFFIX,PERSON_TITLE,PERSON_ACADEMIC_TITLE,
262 PERSON_PREVIOUS_LAST_NAME,PERSON_INITIALS,PERSON_NAME_PHONETIC,PERSON_FIRST_NAME_PHONETIC,PERSON_MIDDLE_NAME_PHONETIC,
263 PERSON_LAST_NAME_PHONETIC,PERSON_IDEN_TYPE,PERSON_IDENTIFIER,DATE_OF_BIRTH,PLACE_OF_BIRTH,DATE_OF_DEATH,GENDER,
264 DECLARED_ETHNICITY,MARITAL_STATUS,MARITAL_STATUS_EFFECTIVE_DATE,PERSONAL_INCOME,HEAD_OF_HOUSEHOLD_FLAG,
265 HOUSEHOLD_INCOME,HOUSEHOLD_SIZE,RENT_OWN_IND,COUNTRY,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,CITY,POSTAL_CODE,STATE,
266 PROVINCE,COUNTY,ADDRESS_LINES_PHONETIC,DESCRIPTION,LANGUAGE,PARTY_SITE_NAME,CONTACT_POINT_TYPE,EMAIL_FORMAT,
267 EMAIL_ADDRESS,PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,PHONE_LINE_TYPE,WEB_TYPE,URL,
268 RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,CLASS_CATEGORY,CLASS_CODE,DEPARTMENT_CODE,DEPARTMENT,TITLE,JOB_TITLE,
269 TELEX_NUMBER,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN;
270 exit when get_details%NOTFOUND;
271
272 open get_next_ref_id;
273 fetch get_next_ref_id into PARTY_ORIG_SYSTEM_REFERENCE;
274 close get_next_ref_id;
275 REC_STATUS:=null;
276 --Validations for the Organization fields
277 if (PARTY_TYPE='ORGANIZATION') then
278 if (ORGANIZATION_NAME is null) then
279 insert into imc_csv_error_details(
280 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
281 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
282 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',null,null,'ORGANIZATION_NAME',null,null,'E',
283 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
284 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||' and party_rec_id = '||PARTY_REC_ID;
285 REC_STATUS := 'E';
286 end if;
287 PARTY_NAME:=ORGANIZATION_NAME;
288 -- dbms_output.put_line('registration type'||REGISTRATION_TYPE);
289 --Data Type validations for CONTROL_YR,EMPLOYEES_TOTAL,INCORP_YEAR,TOTAL_PAYMENTS,YEAR_ESTABLISHED
290
291 if(CONTROL_YR is not null) then
292 begin
293 control_year := to_number(CONTROL_YR);
294 EXCEPTION
295 WHEN OTHERS THEN
296 insert into imc_csv_error_details(
297 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
298 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
299 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',CONTROL_YR,null,'CONTROL_YR',null,ORGANIZATION_NAME,'E',
300 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
301
302 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
303 ' and party_rec_id = '||PARTY_REC_ID;
304 REC_STATUS := 'E';
305 end;
306 end if;
307
308 if(EMPLOYEES_TOTAL is not null) then
309 begin
310 emp_total := to_number(EMPLOYEES_TOTAL);
311 EXCEPTION
312 WHEN OTHERS THEN
313 insert into imc_csv_error_details(
314 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
315 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
316 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',EMPLOYEES_TOTAL,null,'EMPLOYEES_TOTAL',null,ORGANIZATION_NAME,'E',
317 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
318
319 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
320 ' and party_rec_id = '||PARTY_REC_ID;
321 REC_STATUS := 'E';
322 end;
323 end if;
324
325 if(INCORP_YEAR is not null) then
326 begin
327 incorporation_year := to_number(INCORP_YEAR);
328 EXCEPTION
329 WHEN OTHERS THEN
330 insert into imc_csv_error_details(
331 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
332 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
333 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',INCORP_YEAR,null,'INCORP_YEAR',null,ORGANIZATION_NAME,'E',
334 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
335
336 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
337 ' and party_rec_id = '||PARTY_REC_ID;
338 REC_STATUS := 'E';
339 end;
340 end if;
341
342
343 if(TOTAL_PAYMENTS is not null) then
344 begin
345 total_pays := to_number(TOTAL_PAYMENTS);
346 EXCEPTION
347 WHEN OTHERS THEN
348 insert into imc_csv_error_details(
349 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
350 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
351 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',TOTAL_PAYMENTS,null,'TOTAL_PAYMENTS',null,ORGANIZATION_NAME,'E',
352 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
353
354 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
355 ' and party_rec_id = '||PARTY_REC_ID;
356 REC_STATUS := 'E';
357 end;
358 end if;
359
360 if(YEAR_ESTABLISHED is not null) then
361 begin
362 yr_established := to_number(YEAR_ESTABLISHED);
363 EXCEPTION
364 WHEN OTHERS THEN
365 insert into imc_csv_error_details(
366 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
367 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
368 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',YEAR_ESTABLISHED,null,'YEAR_ESTABLISHED',null,ORGANIZATION_NAME,'E',
369 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
370
371 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
372 ' and party_rec_id = '||PARTY_REC_ID;
373 REC_STATUS := 'E';
374 end;
375 end if;
376
377 if (LEGAL_STATUS is not null) then
378 open legal_status_code(LEGAL_STATUS);
379 fetch legal_status_code into lookup_code_count;
380 if (lookup_code_count=0) then
381 insert into imc_csv_error_details(
382 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
383 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
384 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',LEGAL_STATUS,null,'LEGAL_STATUS','LEGAL_STATUS',ORGANIZATION_NAME,'E',
385 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
386
387 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
388 ' and party_rec_id = '||PARTY_REC_ID;
389 REC_STATUS := 'E';
390 end if;
391 close legal_status_code;
392 end if;
393
394 if (HQ_BRANCH_IND is not null) then
395 open hq_branch(HQ_BRANCH_IND);
396 fetch hq_branch into lookup_code_count;
397 if (lookup_code_count=0) then
398 insert into imc_csv_error_details(
399 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
400 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
401 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',HQ_BRANCH_IND,null,'HQ_BRANCH_IND','HQ_BRANCH_IND',ORGANIZATION_NAME,'E',
402 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
403 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
404 ' and party_rec_id = '||PARTY_REC_ID;
405 REC_STATUS := 'E';
406 end if;
407 close hq_branch;
408 end if;
409
410 if (FISCAL_YEAREND_MONTH is not null) then
411 open FISCAL_YREND_MONTH(FISCAL_YEAREND_MONTH);
412 fetch FISCAL_YREND_MONTH into lookup_code_count;
413 if (lookup_code_count=0) then
414 insert into imc_csv_error_details(
415 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
416 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
417 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',FISCAL_YEAREND_MONTH,null,'FISCAL_YEAREND_MONTH','MONTH',ORGANIZATION_NAME,'E',
418 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
419 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
420 ' and party_rec_id = '||PARTY_REC_ID;
421 REC_STATUS := 'E';
422 end if;
423 close FISCAL_YREND_MONTH;
424 end if;
425
426 if (REGISTRATION_TYPE is not null) then
427 open REGISTRY_TYPE(REGISTRATION_TYPE);
428 fetch REGISTRY_TYPE into lookup_code_count;
429 if (lookup_code_count=0) then
430 --dbms_output.put_line('lookup code count'||lookup_code_count);
431 --dbms_output.put_line('resistation type'||REGISTRATION_TYPE);
432 insert into imc_csv_error_details(
433 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
434 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
435 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',REGISTRATION_TYPE,null,'REGISTRATION_TYPE','REGISTRATION_TYPE',ORGANIZATION_NAME,'E',
436 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
437 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
438 ' and party_rec_id = '||PARTY_REC_ID;
439 REC_STATUS := 'E';
440 end if;
441 close REGISTRY_TYPE;
442 end if;
443
444 if (OOB_IND is not null) then
445 if (OOB_IND='Y' OR OOB_IND='N') then
446 null;
447 else
448 insert into imc_csv_error_details(
449 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
450 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
451 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',OOB_IND,null,'OOB_IND','IMC_CSV_Y_OR_N',ORGANIZATION_NAME,'E',
452 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
453 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
454 ' and party_rec_id = '||PARTY_REC_ID;
455 REC_STATUS := 'E';
456 end if;
457 end if;
458
459 if (BRANCH_FLAG is not null) then
460 if (BRANCH_FLAG='Y' OR BRANCH_FLAG='N') then
461 null;
462 else
463 insert into imc_csv_error_details(
464 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
465 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
466 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',BRANCH_FLAG,null,'BRANCH_FLAG','IMC_CSV_Y_OR_N',ORGANIZATION_NAME,'E',
467 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
468 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
469 ' and party_rec_id = '||PARTY_REC_ID;
470 REC_STATUS := 'E';
471 end if;
472 end if;
473
474 if (PARENT_SUB_IND is not null) then
475 if (PARENT_SUB_IND='Y' OR PARENT_SUB_IND='N') then
476 null;
477 else
478 insert into imc_csv_error_details(
479 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
480 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
481 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',PARENT_SUB_IND,null,'PARENT_SUB_IND','IMC_CSV_Y_OR_N',ORGANIZATION_NAME,'E',
482 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
483 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
484 ' and party_rec_id = '||PARTY_REC_ID;
485 REC_STATUS := 'E';
486 end if;
487 end if;
488
489 if (PUBLIC_PRIVATE_OWNERSHIP_FLAG is not null) then
490 if (PUBLIC_PRIVATE_OWNERSHIP_FLAG='Y' OR PUBLIC_PRIVATE_OWNERSHIP_FLAG='N') then
491 null;
492 else
493 insert into imc_csv_error_details(
494 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
495 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
496 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',PUBLIC_PRIVATE_OWNERSHIP_FLAG,null,'PUBLIC_PRIVATE_OWNERSHIP_FLAG','IMC_CSV_Y_OR_N',ORGANIZATION_NAME,'E',
497 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
498 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
499 ' and party_rec_id = '||PARTY_REC_ID;
500 REC_STATUS := 'E';
501 end if;
502 end if;
503 END IF;
504
505 ----Validations for the Person fields
506 if (PARTY_TYPE='PERSON') then
507 if (PERSON_FIRST_NAME is null and PERSON_LAST_NAME is null) then
508 insert into imc_csv_error_details(
509 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
510 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
511 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',null,null,'PERSON_FIRST_NAME',null,null,'E',
512 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
513
514 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
515 ' and party_rec_id = '||PARTY_REC_ID;
516 REC_STATUS := 'E';
517 else
518 if (ORGANIZATION_NAME is not null) then
519 CONTACT_FLAG:='Y';
520 end if;
521 end if;
522 PARTY_NAME:=PERSON_FIRST_NAME||' '||PERSON_LAST_NAME;
523 --Data Type validations for PERSONAL_INCOME,HOUSEHOLD_INCOME,HOUSEHOLD_SIZE
524
525 if(PERSONAL_INCOME is not null) then
526 begin
527 per_income := to_number(PERSONAL_INCOME);
528 EXCEPTION
529 WHEN OTHERS THEN
530 insert into imc_csv_error_details(
531 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
532 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
533 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',PERSONAL_INCOME,null,'PERSONAL_INCOME',null,PARTY_NAME,'E',
534 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
535
536 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
537 ' and party_rec_id = '||PARTY_REC_ID;
538 REC_STATUS := 'E';
539 end;
540 end if;
541
542 if(HOUSEHOLD_INCOME is not null) then
543 begin
544 house_income := to_number(HOUSEHOLD_INCOME);
545 EXCEPTION
546 WHEN OTHERS THEN
547 insert into imc_csv_error_details(
548 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
549 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
550 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',HOUSEHOLD_INCOME,null,'HOUSEHOLD_INCOME',null,PARTY_NAME,'E',
551 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
552
553 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
554 ' and party_rec_id = '||PARTY_REC_ID;
555 REC_STATUS := 'E';
556 end;
557 end if;
558
559 if(HOUSEHOLD_SIZE is not null) then
560 begin
561 house_size := to_number(HOUSEHOLD_SIZE);
562 EXCEPTION
563 WHEN OTHERS THEN
564 insert into imc_csv_error_details(
565 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
566 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
567 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',HOUSEHOLD_SIZE,null,'HOUSEHOLD_SIZE',null,PARTY_NAME,'E',
568 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
569 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
570 ' and party_rec_id = '||PARTY_REC_ID;
571 REC_STATUS := 'E';
572 end;
573 end if;
574
575
576 IF (DATE_OF_BIRTH is not null ) then
577 begin
578 birthdate := to_date(DATE_OF_BIRTH,date_format);
579 EXCEPTION
580 WHEN OTHERS THEN
581 insert into imc_csv_error_details(
582 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
583 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
584 loadId,batchId,PARTY_REC_ID,'DATE_FORMAT',DATE_OF_BIRTH,null,'DATE_OF_BIRTH',null,PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
585 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
586
587 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
588 ' and party_rec_id = '||PARTY_REC_ID;
589 REC_STATUS := 'E';
590 end;
591 end if;
592
593 IF (DATE_OF_DEATH is not null ) then
594 begin
595 deathdate := to_date(DATE_OF_DEATH,date_format);
596 EXCEPTION
597 WHEN OTHERS THEN
598 insert into imc_csv_error_details(
599 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
600 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
601 loadId,batchId,PARTY_REC_ID,'DATE_FORMAT',DATE_OF_DEATH,null,'DATE_OF_DEATH',null,PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
602 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
603
604 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
605 ' and party_rec_id = '||PARTY_REC_ID;
606 REC_STATUS := 'E';
607 end;
608 end if;
609
610 IF (MARITAL_STATUS_EFFECTIVE_DATE is not null ) then
611 begin
612 maritaldate := to_date(MARITAL_STATUS_EFFECTIVE_DATE,date_format);
613 EXCEPTION
614 WHEN OTHERS THEN
615 insert into imc_csv_error_details(
616 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
617 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
618 loadId,batchId,PARTY_REC_ID,'DATE_FORMAT',MARITAL_STATUS_EFFECTIVE_DATE,null,'MARITAL_STATUS_EFFECTIVE_DATE',null,PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
619 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
620
621 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
622 ' and party_rec_id = '||PARTY_REC_ID;
623 REC_STATUS := 'E';
624 end;
625 end if;
626
627 if (PERSON_PRE_NAME_ADJUNCT is not null) then
628 open contact_title(PERSON_PRE_NAME_ADJUNCT);
629 fetch contact_title into lookup_code_count;
630 if (lookup_code_count=0) then
631 insert into imc_csv_error_details(
632 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
633 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
634 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',PERSON_PRE_NAME_ADJUNCT,null,'PERSON_PRE_NAME_ADJUNCT','CONTACT_TITLE',PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
635 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
636 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
637 ' and party_rec_id = '||PARTY_REC_ID;
638 REC_STATUS := 'E';
639 end if;
640 close contact_title;
641 end if;
642
643 if (MARITAL_STATUS is not null) then
644 open marry_status(MARITAL_STATUS);
645 fetch marry_status into lookup_code_count;
646 if (lookup_code_count=0) then
647 insert into imc_csv_error_details(
648 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
649 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
650 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',MARITAL_STATUS,null,'MARITAL_STATUS','MARITAL_STATUS',PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
651 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
652 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
653 ' and party_rec_id = '||PARTY_REC_ID;
654 REC_STATUS := 'E';
655 end if;
656 close marry_status;
657 end if;
658
659 if (RENT_OWN_IND is not null) then
660 open own_rent(RENT_OWN_IND);
661 fetch own_rent into lookup_code_count;
662 if (lookup_code_count=0) then
663 insert into imc_csv_error_details(
664 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
665 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
666 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',RENT_OWN_IND,null,'RENT_OWN_IND','OWN_RENT_IND',PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
667 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
668 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
669 ' and party_rec_id = '||PARTY_REC_ID;
670 REC_STATUS := 'E';
671 end if;
672 close own_rent;
673 end if;
674
675 if (HEAD_OF_HOUSEHOLD_FLAG is not null) then
676 if (HEAD_OF_HOUSEHOLD_FLAG='Y' OR HEAD_OF_HOUSEHOLD_FLAG='N') then
677 null;
678 else
679 insert into imc_csv_error_details(
680 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
681 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
682 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',HEAD_OF_HOUSEHOLD_FLAG,null,'HEAD_OF_HOUSEHOLD_FLAG','IMC_CSV_Y_OR_N',PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
683 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
684 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
685 ' and party_rec_id = '||PARTY_REC_ID;
686 REC_STATUS := 'E';
687 end if;
688 end if;
689 END IF;
690
691 ----Validations for the Address fields
692 if(ADDRESS_FLAG ='Y') then
693 if (ADDRESS1 is null) then
694 insert into imc_csv_error_details(
695 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
696 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
697 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',ADDRESS1,null,'ADDRESS1',null,PARTY_NAME,'E',
698 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
699 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
700 ' and party_rec_id = '||PARTY_REC_ID;
701 REC_STATUS := 'E';
702 end if;
703
704 if (COUNTRY is not null) then
705 open country_code(COUNTRY);
706 fetch country_code into lookup_code_count;
707 if (lookup_code_count=0) then
708 insert into imc_csv_error_details(
709 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
710 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
711 loadId,batchId,PARTY_REC_ID,'VALUE_ERROR',COUNTRY,null,'COUNTRY',null,PARTY_NAME,'E',
712 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
713 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
714 ' and party_rec_id = '||PARTY_REC_ID;
715 REC_STATUS := 'E';
716 end if;
717 close country_code;
718 else
719 insert into imc_csv_error_details(
720 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
721 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
722 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',COUNTRY,null,'COUNTRY',null,PARTY_NAME,'E',
723 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
724 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
725 ' and party_rec_id = '||PARTY_REC_ID;
726 REC_STATUS := 'E';
727 end if;
728
729 if (LANGUAGE is not null) then
730 open language_code(LANGUAGE);
731 fetch language_code into lookup_code_count;
732 if (lookup_code_count=0) then
733 insert into imc_csv_error_details(
734 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
735 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
736 loadId,batchId,PARTY_REC_ID,'VALUE_ERROR',LANGUAGE,null,'LANGUAGE',null,PARTY_NAME,'E',
737 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
738 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
739 ' and party_rec_id = '||PARTY_REC_ID;
740 REC_STATUS := 'E';
741 end if;
742 close language_code;
743 end if;
744 end if;
745
746 ----Validations for the contact points fields
747
748 if(CONTACT_POINTS_FLAG ='Y') then
749 if (CONTACT_POINT_TYPE is not null) then
750 open contact_pnt_type(CONTACT_POINT_TYPE);
751 fetch contact_pnt_type into lookup_code_count;
752 if (lookup_code_count=0) then
753 insert into imc_csv_error_details(
754 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
755 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
756 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',CONTACT_POINT_TYPE,null,'CONTACT_POINT_TYPE','COMMUNICATION_TYPE',PARTY_NAME,'E',
757 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
758 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
759 ' and party_rec_id = '||PARTY_REC_ID;
760 REC_STATUS := 'E';
761 end if;
762 close contact_pnt_type;
763
764 if (CONTACT_POINT_TYPE='PHONE' AND (PHONE_NUMBER is null AND RAW_PHONE_NUMBER is null)) then
765 insert into imc_csv_error_details(
766 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
767 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
768 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',PHONE_NUMBER,null,'PHONE_NUMBER',null,PARTY_NAME,'E',
769 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
770 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
771 ' and party_rec_id = '||PARTY_REC_ID;
772 REC_STATUS := 'E';
773 end if;
774
775 if (CONTACT_POINT_TYPE='FAX' AND (PHONE_NUMBER is null AND RAW_PHONE_NUMBER is null)) then
776 insert into imc_csv_error_details(
777 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
778 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
779 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',PHONE_NUMBER,null,'PHONE_NUMBER',null,PARTY_NAME,'E',
780 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
781 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
782 ' and party_rec_id = '||PARTY_REC_ID;
783 REC_STATUS := 'E';
784 end if;
785
786 if (CONTACT_POINT_TYPE='EMAIL' AND EMAIL_ADDRESS is null) then
787 insert into imc_csv_error_details(
788 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
789 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
790 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',EMAIL_ADDRESS,null,'EMAIL_ADDRESS',null,PARTY_NAME,'E',
791 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
792 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
793 ' and party_rec_id = '||PARTY_REC_ID;
794 REC_STATUS := 'E';
795 end if;
796
797 if (CONTACT_POINT_TYPE='WEB' AND URL is null) then
798 insert into imc_csv_error_details(
799 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
800 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
801 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',URL,null,'URL',null,PARTY_NAME,'E',
802 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
803 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
804 ' and party_rec_id = '||PARTY_REC_ID;
805 REC_STATUS := 'E';
806 end if;
807
808 if (CONTACT_POINT_TYPE='TLX' AND TELEX_NUMBER is null) then
809 insert into imc_csv_error_details(
810 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
811 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
812 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',TELEX_NUMBER,null,'TELEX_NUMBER',null,PARTY_NAME,'E',
813 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
814 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
815 ' and party_rec_id = '||PARTY_REC_ID;
816 REC_STATUS := 'E';
817 end if;
818
819 end if;
820 end if;
821
822 ----Validations for the classifications fields
823 if (CLASSIFICATION_FLAG = 'Y') then
824 if (CLASS_CATEGORY is null) then
825 insert into imc_csv_error_details(
826 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
827 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
828 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',CLASS_CATEGORY,null,'CLASS_CATEGORY','SIC_CODE_TYPE',PARTY_NAME,'E',
829 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
830 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
831 ' and party_rec_id = '||PARTY_REC_ID;
832 REC_STATUS := 'E';
833 else
834 open clss_category(CLASS_CATEGORY);
835 fetch clss_category into lookup_code_count;
836 close clss_category;
837 if (lookup_code_count=0) then
838 insert into imc_csv_error_details(
839 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
840 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
841 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',CLASS_CATEGORY,null,'CLASS_CATEGORY','SIC_CODE_TYPE',PARTY_NAME,'E',
842 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
843 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
844 ' and party_rec_id = '||PARTY_REC_ID;
845 REC_STATUS := 'E';
846 else
847 if (CLASS_CODE is null) then
848 insert into imc_csv_error_details(
849 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
850 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
851 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',CLASS_CODE,null,'CLASS_CODE',CLASS_CATEGORY,PARTY_NAME,'E',
852 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
853 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
854 ' and party_rec_id = '||PARTY_REC_ID;
855 REC_STATUS := 'E';
856 else
857 open clss_code(CLASS_CATEGORY,CLASS_CODE);
858 fetch clss_code into lookup_code_count;
859 close clss_code;
860 if (lookup_code_count=0) then
861 insert into imc_csv_error_details(
862 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
863 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
864 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',CLASS_CODE,null,'CLASS_CODE',CLASS_CATEGORY,PARTY_NAME,'E',
865 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
866 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
867 ' and party_rec_id = '||PARTY_REC_ID;
868 REC_STATUS := 'E';
869 end if;
870 end if;
871 end if;
872
873 end if;
874
875 end if;
876
877 --INSERT INTO INTERFACE TABLES
878 if (REC_STATUS is null) OR (REC_STATUS<>'E') then
879 if (PARTY_TYPE='ORGANIZATION') then
880 --dbms_output.put_line('inserting into HZ parties interface');
881 insert into HZ_IMP_PARTIES_INT(batch_id,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,PARTY_TYPE,ORGANIZATION_NAME,CEO_NAME,CEO_TITLE,PRINCIPAL_NAME,
882 PRINCIPAL_TITLE,LEGAL_STATUS,CONTROL_YR,EMPLOYEES_TOTAL,HQ_BRANCH_IND,BRANCH_FLAG,OOB_IND,TAX_REFERENCE,
883 GSA_INDICATOR_FLAG,JGZZ_FISCAL_CODE,ANALYSIS_FY,FISCAL_YEAREND_MONTH,CURR_FY_POTENTIAL_REVENUE,
884 NEXT_FY_POTENTIAL_REVENUE,YEAR_ESTABLISHED,MISSION_STATEMENT,ORGANIZATION_TYPE,BUSINESS_SCOPE,KNOWN_AS,
885 LOCAL_BUS_IDEN_TYPE,LOCAL_BUS_IDENTIFIER,PREF_FUNCTIONAL_CURRENCY,REGISTRATION_TYPE,PARENT_SUB_IND,INCORP_YEAR,
886 PUBLIC_PRIVATE_OWNERSHIP_FLAG,TOTAL_PAYMENTS,DUNS_NUMBER_C,CREATION_DATE,CREATED_BY,
887 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
888 values (batchId,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,'ORGANIZATION',ORGANIZATION_NAME,CEO_NAME,CEO_TITLE,PRINCIPAL_NAME,
889 PRINCIPAL_TITLE,LEGAL_STATUS,control_year,emp_total,HQ_BRANCH_IND,BRANCH_FLAG,OOB_IND,TAX_REFERENCE,
890 GSA_INDICATOR_FLAG,JGZZ_FISCAL_CODE,ANALYSIS_FY,FISCAL_YEAREND_MONTH,CURR_FY_POTENTIAL_REVENUE,
891 NEXT_FY_POTENTIAL_REVENUE,yr_established,MISSION_STATEMENT,ORGANIZATION_TYPE,BUSINESS_SCOPE,KNOWN_AS,
892 LOCAL_BUS_IDEN_TYPE,LOCAL_BUS_IDENTIFIER,PREF_FUNCTIONAL_CURRENCY,REGISTRATION_TYPE,PARENT_SUB_IND,
893 incorporation_year,PUBLIC_PRIVATE_OWNERSHIP_FLAG,total_pays,DUNS_NUMBER_C,SYSDATE,FND_GLOBAL.user_id,SYSDATE,
894 FND_GLOBAL.user_id,FND_GLOBAL.login_id);
895 elsif (PARTY_TYPE='PERSON') then
896 insert into HZ_IMP_PARTIES_INT(batch_id,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,PARTY_TYPE,PERSON_PRE_NAME_ADJUNCT,PERSON_FIRST_NAME,
897 PERSON_MIDDLE_NAME,PERSON_LAST_NAME,PERSON_NAME_SUFFIX,PERSON_TITLE,PERSON_ACADEMIC_TITLE,
898 PERSON_PREVIOUS_LAST_NAME,PERSON_INITIALS,KNOWN_AS,PERSON_NAME_PHONETIC,PERSON_FIRST_NAME_PHONETIC,
899 person_MIDDLE_NAME_PHONETIC,PERSON_LAST_NAME_PHONETIC,TAX_REFERENCE,JGZZ_FISCAL_CODE,PERSON_IDEN_TYPE,
900 PERSON_IDENTIFIER,DATE_OF_BIRTH,PLACE_OF_BIRTH,DATE_OF_DEATH,GENDER,DECLARED_ETHNICITY,MARITAL_STATUS,
901 MARITAL_STATUS_EFFECTIVE_DATE,PERSONAL_INCOME,HEAD_OF_HOUSEHOLD_FLAG,HOUSEHOLD_INCOME,
902 HOUSEHOLD_SIZE,RENT_OWN_IND,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
903 values(batchId,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,'PERSON',PERSON_PRE_NAME_ADJUNCT,PERSON_FIRST_NAME,
904 PERSON_MIDDLE_NAME,PERSON_LAST_NAME,PERSON_NAME_SUFFIX,PERSON_TITLE,PERSON_ACADEMIC_TITLE,
905 PERSON_PREVIOUS_LAST_NAME,PERSON_INITIALS,KNOWN_AS,PERSON_NAME_PHONETIC,PERSON_FIRST_NAME_PHONETIC,
906 PERSON_MIDDLE_NAME_PHONETIC,PERSON_LAST_NAME_PHONETIC,TAX_REFERENCE,JGZZ_FISCAL_CODE,PERSON_IDEN_TYPE,
907 PERSON_IDENTIFIER,birthdate,PLACE_OF_BIRTH,deathdate,GENDER,DECLARED_ETHNICITY,MARITAL_STATUS,
908 maritaldate,per_income,HEAD_OF_HOUSEHOLD_FLAG,house_income,house_size,
909 RENT_OWN_IND,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
910 if(CONTACT_FLAG='Y') then
911 old_contact_org := 'N';
912 i:=1;
913 while(i<=org_contact.COUNT) LOOP
914 org_contact_rec := org_contact(i);
915 old_org_name := org_contact_rec.org_name ;
916 orig_sys_ref := org_contact_rec.original_sys_ref;
917 if(old_org_name = ORGANIZATION_NAME) then
918 old_contact_org := 'Y';
919 CNTORG_ORIG_SYSTEM_REFERENCE := orig_sys_ref;
920 end if;
921 i:=i+1;
922 end loop;
923
924 if(old_contact_org ='N') then
925
926 open get_next_ref_id;
927 fetch get_next_ref_id into CNTORG_ORIG_SYSTEM_REFERENCE;
928 close get_next_ref_id;
929
930 insert into HZ_IMP_PARTIES_INT(batch_id,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,PARTY_TYPE,ORGANIZATION_NAME,CEO_NAME,CEO_TITLE,PRINCIPAL_NAME,
931 PRINCIPAL_TITLE,LEGAL_STATUS,CONTROL_YR,EMPLOYEES_TOTAL,HQ_BRANCH_IND,BRANCH_FLAG,OOB_IND,TAX_REFERENCE,
932 GSA_INDICATOR_FLAG,JGZZ_FISCAL_CODE,ANALYSIS_FY,FISCAL_YEAREND_MONTH,CURR_FY_POTENTIAL_REVENUE,
933 NEXT_FY_POTENTIAL_REVENUE,YEAR_ESTABLISHED,MISSION_STATEMENT,ORGANIZATION_TYPE,BUSINESS_SCOPE,KNOWN_AS,
934 LOCAL_BUS_IDEN_TYPE,LOCAL_BUS_IDENTIFIER,PREF_FUNCTIONAL_CURRENCY,REGISTRATION_TYPE,PARENT_SUB_IND,INCORP_YEAR,
935 PUBLIC_PRIVATE_OWNERSHIP_FLAG,TOTAL_PAYMENTS,DUNS_NUMBER_C,CREATION_DATE,CREATED_BY,
936 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
937 values (batchId,'CSV',CNTORG_ORIG_SYSTEM_REFERENCE,'ORGANIZATION',ORGANIZATION_NAME,CEO_NAME,CEO_TITLE,PRINCIPAL_NAME,
938 PRINCIPAL_TITLE,LEGAL_STATUS,CONTROL_YR,EMPLOYEES_TOTAL,HQ_BRANCH_IND,BRANCH_FLAG,OOB_IND,TAX_REFERENCE,
939 GSA_INDICATOR_FLAG,JGZZ_FISCAL_CODE,ANALYSIS_FY,FISCAL_YEAREND_MONTH,CURR_FY_POTENTIAL_REVENUE,
940 NEXT_FY_POTENTIAL_REVENUE,YEAR_ESTABLISHED,MISSION_STATEMENT,ORGANIZATION_TYPE,BUSINESS_SCOPE,KNOWN_AS,
941 LOCAL_BUS_IDEN_TYPE,LOCAL_BUS_IDENTIFIER,PREF_FUNCTIONAL_CURRENCY,REGISTRATION_TYPE,PARENT_SUB_IND,INCORP_YEAR,
942 PUBLIC_PRIVATE_OWNERSHIP_FLAG,TOTAL_PAYMENTS,DUNS_NUMBER_C,SYSDATE,FND_GLOBAL.user_id,SYSDATE,
943 FND_GLOBAL.user_id,FND_GLOBAL.login_id);
944
945 org_contact_rec.org_name := ORGANIZATION_NAME ;
946 org_contact_rec.original_sys_ref := CNTORG_ORIG_SYSTEM_REFERENCE;
947 org_contact(i) := org_contact_rec;
948
949 end if;
950
951 open get_next_ref_id;
952 fetch get_next_ref_id into CNT_ORIG_SYSTEM_REFERENCE;
953 close get_next_ref_id;
954
955 insert into HZ_IMP_CONTACTS_INT(
956 BATCH_ID,CONTACT_ORIG_SYSTEM,CONTACT_ORIG_SYSTEM_REFERENCE,SUB_ORIG_SYSTEM,SUB_ORIG_SYSTEM_REFERENCE,
957 OBJ_ORIG_SYSTEM,OBJ_ORIG_SYSTEM_REFERENCE ,DEPARTMENT_CODE,DEPARTMENT,TITLE,JOB_TITLE,RELATIONSHIP_TYPE,RELATIONSHIP_CODE,
958 START_DATE,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
959 values(batchId,'CSV',CNT_ORIG_SYSTEM_REFERENCE,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,
960 'CSV',CNTORG_ORIG_SYSTEM_REFERENCE,DEPARTMENT_CODE,DEPARTMENT,TITLE,JOB_TITLE,'CONTACT','CONTACT_OF',
961 SYSDATE,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id) ;
962 end if;
963 end if;
964
965 if(ADDRESS_FLAG ='Y') then
966
967 open get_next_ref_id;
968 fetch get_next_ref_id into SITE_ORIG_SYSTEM_REFERENCE;
969 close get_next_ref_id;
970
971 insert into HZ_IMP_ADDRESSES_INT(batch_id,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,SITE_ORIG_SYSTEM,
972 SITE_ORIG_SYSTEM_REFERENCE,COUNTRY,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,CITY,
973 POSTAL_CODE,STATE,PROVINCE,COUNTY,ADDRESS_LINES_PHONETIC,LOCATION_DIRECTIONS,DESCRIPTION,SALES_TAX_GEOCODE,
974 PRIMARY_FLAG,PARTY_SITE_NAME,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
975 values(batchId,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,'CSV',SITE_ORIG_SYSTEM_REFERENCE,COUNTRY,ADDRESS1,ADDRESS2,
976 ADDRESS3,ADDRESS4,CITY,POSTAL_CODE,STATE,PROVINCE,COUNTY,ADDRESS_LINES_PHONETIC,LOCATION_DIRECTIONS,
977 DESCRIPTION,SALES_TAX_GEOCODE,PRIMARY_FLAG,PARTY_SITE_NAME,SYSDATE,FND_GLOBAL.user_id,SYSDATE,
978 FND_GLOBAL.user_id,FND_GLOBAL.login_id);
979 end if;
980
981 if(CONTACT_POINTS_FLAG ='Y') then
982 /* Bug 3854824: If contact_point_type is not passed, based on the
983 contact point passed, set the contact point type */
984 /* Bug 5371056 : insert one row to hz_imp_contactpts_int for
985 | each contact point */
986 if (PHONE_NUMBER is not null OR RAW_PHONE_NUMBER is not null) then
987 CONTACT_POINT_TYPE := 'PHONE';
988 if (PHONE_LINE_TYPE is null) then
989 PHONE_LINE_TYPE := 'GEN';
990 end if;
991 open get_next_ref_id;
992 fetch get_next_ref_id into CP_ORIG_SYSTEM_REFERENCE;
993 close get_next_ref_id;
994
995 insert into HZ_IMP_CONTACTPTS_INT(
996 batch_id,CP_ORIG_SYSTEM,CP_ORIG_SYSTEM_REFERENCE,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,
997 PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,CREATION_DATE,CREATED_BY,
998 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
999 values(batchId,'CSV',CP_ORIG_SYSTEM_REFERENCE,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,
1000 PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,
1001 RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
1002 end if;
1003
1004 if (EMAIL_ADDRESS is not null) then
1005 CONTACT_POINT_TYPE := 'EMAIL';
1006 open get_next_ref_id;
1007 fetch get_next_ref_id into CP_ORIG_SYSTEM_REFERENCE;
1008 close get_next_ref_id;
1009
1010 insert into HZ_IMP_CONTACTPTS_INT(
1011 batch_id,CP_ORIG_SYSTEM,CP_ORIG_SYSTEM_REFERENCE,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,
1012 PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,CREATION_DATE,CREATED_BY,
1013 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
1014 values(batchId,'CSV',CP_ORIG_SYSTEM_REFERENCE,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,
1015 PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,
1016 RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
1017 end if;
1018
1019 if (URL is not null) then
1020 CONTACT_POINT_TYPE := 'WEB';
1021 open get_next_ref_id;
1022 fetch get_next_ref_id into CP_ORIG_SYSTEM_REFERENCE;
1023 close get_next_ref_id;
1024
1025 insert into HZ_IMP_CONTACTPTS_INT(
1026 batch_id,CP_ORIG_SYSTEM,CP_ORIG_SYSTEM_REFERENCE,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,
1027 PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,CREATION_DATE,CREATED_BY,
1028 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
1029 values(batchId,'CSV',CP_ORIG_SYSTEM_REFERENCE,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,
1030 PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,
1031 RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
1032 end if;
1033
1034 if (TELEX_NUMBER is not null) then
1035 CONTACT_POINT_TYPE := 'TLX';
1036 open get_next_ref_id;
1037 fetch get_next_ref_id into CP_ORIG_SYSTEM_REFERENCE;
1038 close get_next_ref_id;
1039
1040 insert into HZ_IMP_CONTACTPTS_INT(
1041 batch_id,CP_ORIG_SYSTEM,CP_ORIG_SYSTEM_REFERENCE,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,
1042 PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,CREATION_DATE,CREATED_BY,
1043 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
1044 values(batchId,'CSV',CP_ORIG_SYSTEM_REFERENCE,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,
1045 PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,
1046 RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
1047 end if;
1048 end if;
1049
1050 if(CLASSIFICATION_FLAG ='Y') then
1051 insert into HZ_IMP_CLASSIFICS_INT(BATCH_ID,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,CLASS_CATEGORY,CLASS_CODE,
1052 START_DATE_ACTIVE, CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
1053 values(batchId,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,CLASS_CATEGORY,CLASS_CODE,SYSDATE,SYSDATE,FND_GLOBAL.user_id,
1054 SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
1055 end if;
1056 --dbms_output.put_line('before the update to rec status to P');
1057 execute immediate 'delete from imc_csv_interface_fields where load_id ='||loadId||' and batch_id = '||batchId||
1058 ' and party_rec_id = '||PARTY_REC_ID;
1059 end if;
1060 end loop;
1061 close get_details;
1062 -- Activating the created batch
1063
1064 EXCEPTION
1065 WHEN FND_API.G_EXC_ERROR THEN
1066 ROLLBACK;
1067 x_return_status := FND_API.G_RET_STS_ERROR;
1068 FND_MSG_PUB.Count_And_Get(
1069 p_encoded => FND_API.G_FALSE,
1070 p_count => x_msg_count,
1071 p_data => x_msg_data);
1072 RAISE FND_API.G_EXC_ERROR;
1073 execute immediate 'update hz_imp_batch_summary set csv_status=''ERROR'' where load_type=''CSV'' and batch_id = '||batchId;
1074
1075
1076 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1077 ROLLBACK;
1078 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1079 FND_MSG_PUB.Count_And_Get(
1080 p_encoded => FND_API.G_FALSE,
1081 p_count => x_msg_count,
1082 p_data => x_msg_data);
1083 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084 execute immediate 'update hz_imp_batch_summary set csv_status=''ERROR'' where load_type=''CSV'' and batch_id = '||batchId;
1085
1086
1087 WHEN OTHERS THEN
1088 ROLLBACK;
1089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1090
1091 FND_MESSAGE.SET_NAME('AR', 'IMC_API_OTHERS_EXCEP');
1092 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
1093 FND_MSG_PUB.ADD;
1094
1095 FND_MSG_PUB.Count_And_Get(
1096 p_encoded => FND_API.G_FALSE,
1097 p_count => x_msg_count,
1098 p_data => x_msg_data );
1099 RAISE FND_API.G_EXC_ERROR;
1100 execute immediate 'update hz_imp_batch_summary set csv_status=''ERROR'' where load_type=''CSV'' and batch_id = '||batchId;
1101
1102 END;
1103 END IMC_CSV_LOAD;