[Home] [Help]
PACKAGE BODY: APPS.IMC_CSV_LOAD
Source
1 PACKAGE BODY IMC_CSV_LOAD AS
2 /* $Header: IMCLOADB.pls 120.13.12010000.2 2009/07/31 06:26:24 vsegu 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 cursor ph_line_type(ph_line_type_value VARCHAR2) is
247 select count(*) from ar_lookups where lookup_type='PHONE_LINE_TYPE' and lookup_code=ph_line_type_value
248 AND ENABLED_FLAG = 'Y'
249 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE)) ;
250
251 BEGIN
252 open get_load_details(loadId,batchId);
253 fetch get_load_details into date_format,ADDRESS_FLAG,CONTACT_POINTS_FLAG,CLASSIFICATION_FLAG;
254 close get_load_details ;
255
256 open get_details(loadId,batchId);
257 LOOP
258 fetch get_details into
259 LOAD_ID,BATCH_ID,PARTY_REC_ID,REC_STATUS,PARTY_TYPE,ORGANIZATION_NAME,CEO_NAME,
260 CEO_TITLE,PRINCIPAL_NAME,PRINCIPAL_TITLE,LEGAL_STATUS,CONTROL_YR,EMPLOYEES_TOTAL,
261 HQ_BRANCH_IND,BRANCH_FLAG,OOB_IND,TAX_REFERENCE,GSA_INDICATOR_FLAG,
262 JGZZ_FISCAL_CODE,ANALYSIS_FY,FISCAL_YEAREND_MONTH,CURR_FY_POTENTIAL_REVENUE,NEXT_FY_POTENTIAL_REVENUE,
263 YEAR_ESTABLISHED,MISSION_STATEMENT,ORGANIZATION_TYPE,BUSINESS_SCOPE,KNOWN_AS,KNOWN_AS2,KNOWN_AS3,KNOWN_AS4,
264 KNOWN_AS5,LOCAL_BUS_IDEN_TYPE,LOCAL_BUS_IDENTIFIER,PREF_FUNCTIONAL_CURRENCY,REGISTRATION_TYPE,PARENT_SUB_IND,
265 INCORP_YEAR,LINE_OF_BUSINESS,PUBLIC_PRIVATE_OWNERSHIP_FLAG,TOTAL_PAYMENTS,DUNS_NUMBER_C,PERSON_PRE_NAME_ADJUNCT,
266 PERSON_FIRST_NAME,PERSON_MIDDLE_NAME,PERSON_LAST_NAME,PERSON_NAME_SUFFIX,PERSON_TITLE,PERSON_ACADEMIC_TITLE,
267 PERSON_PREVIOUS_LAST_NAME,PERSON_INITIALS,PERSON_NAME_PHONETIC,PERSON_FIRST_NAME_PHONETIC,PERSON_MIDDLE_NAME_PHONETIC,
268 PERSON_LAST_NAME_PHONETIC,PERSON_IDEN_TYPE,PERSON_IDENTIFIER,DATE_OF_BIRTH,PLACE_OF_BIRTH,DATE_OF_DEATH,GENDER,
269 DECLARED_ETHNICITY,MARITAL_STATUS,MARITAL_STATUS_EFFECTIVE_DATE,PERSONAL_INCOME,HEAD_OF_HOUSEHOLD_FLAG,
270 HOUSEHOLD_INCOME,HOUSEHOLD_SIZE,RENT_OWN_IND,COUNTRY,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,CITY,POSTAL_CODE,STATE,
271 PROVINCE,COUNTY,ADDRESS_LINES_PHONETIC,DESCRIPTION,LANGUAGE,PARTY_SITE_NAME,CONTACT_POINT_TYPE,EMAIL_FORMAT,
272 EMAIL_ADDRESS,PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,PHONE_LINE_TYPE,WEB_TYPE,URL,
273 RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,CLASS_CATEGORY,CLASS_CODE,DEPARTMENT_CODE,DEPARTMENT,TITLE,JOB_TITLE,
274 TELEX_NUMBER,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN;
275 exit when get_details%NOTFOUND;
276
277 open get_next_ref_id;
278 fetch get_next_ref_id into PARTY_ORIG_SYSTEM_REFERENCE;
279 close get_next_ref_id;
280 REC_STATUS:=null;
281 --Validations for the Organization fields
282 if (PARTY_TYPE='ORGANIZATION') then
283 if (ORGANIZATION_NAME is null) then
284 insert into imc_csv_error_details(
285 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
286 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
287 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',null,null,'ORGANIZATION_NAME',null,null,'E',
288 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
289 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;
290 REC_STATUS := 'E';
291 end if;
292 PARTY_NAME:=ORGANIZATION_NAME;
293 -- dbms_output.put_line('registration type'||REGISTRATION_TYPE);
294 --Data Type validations for CONTROL_YR,EMPLOYEES_TOTAL,INCORP_YEAR,TOTAL_PAYMENTS,YEAR_ESTABLISHED
295
296 if(CONTROL_YR is not null) then
297 begin
298 control_year := to_number(CONTROL_YR);
299 EXCEPTION
300 WHEN OTHERS THEN
301 insert into imc_csv_error_details(
302 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
303 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
304 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',CONTROL_YR,null,'CONTROL_YR',null,ORGANIZATION_NAME,'E',
305 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
306
307 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
308 ' and party_rec_id = '||PARTY_REC_ID;
309 REC_STATUS := 'E';
310 end;
311 end if;
312
313 if(EMPLOYEES_TOTAL is not null) then
314 begin
315 emp_total := to_number(EMPLOYEES_TOTAL);
316 EXCEPTION
317 WHEN OTHERS THEN
318 insert into imc_csv_error_details(
319 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
320 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
321 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',EMPLOYEES_TOTAL,null,'EMPLOYEES_TOTAL',null,ORGANIZATION_NAME,'E',
322 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
323
324 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
325 ' and party_rec_id = '||PARTY_REC_ID;
326 REC_STATUS := 'E';
327 end;
328 end if;
329
330 if(INCORP_YEAR is not null) then
331 begin
332 incorporation_year := to_number(INCORP_YEAR);
333 EXCEPTION
334 WHEN OTHERS THEN
335 insert into imc_csv_error_details(
336 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
337 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
338 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',INCORP_YEAR,null,'INCORP_YEAR',null,ORGANIZATION_NAME,'E',
339 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
340
341 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
342 ' and party_rec_id = '||PARTY_REC_ID;
343 REC_STATUS := 'E';
344 end;
345 end if;
346
347
348 if(TOTAL_PAYMENTS is not null) then
349 begin
350 total_pays := to_number(TOTAL_PAYMENTS);
351 EXCEPTION
352 WHEN OTHERS THEN
353 insert into imc_csv_error_details(
354 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
355 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
356 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',TOTAL_PAYMENTS,null,'TOTAL_PAYMENTS',null,ORGANIZATION_NAME,'E',
357 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
358
359 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
360 ' and party_rec_id = '||PARTY_REC_ID;
361 REC_STATUS := 'E';
362 end;
363 end if;
364
365 if(YEAR_ESTABLISHED is not null) then
366 begin
367 yr_established := to_number(YEAR_ESTABLISHED);
368 EXCEPTION
369 WHEN OTHERS THEN
370 insert into imc_csv_error_details(
371 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
372 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
373 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',YEAR_ESTABLISHED,null,'YEAR_ESTABLISHED',null,ORGANIZATION_NAME,'E',
374 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
375
376 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
377 ' and party_rec_id = '||PARTY_REC_ID;
378 REC_STATUS := 'E';
379 end;
380 end if;
381
382 if (LEGAL_STATUS is not null) then
383 open legal_status_code(LEGAL_STATUS);
384 fetch legal_status_code into lookup_code_count;
385 if (lookup_code_count=0) then
386 insert into imc_csv_error_details(
387 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
388 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
389 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',LEGAL_STATUS,null,'LEGAL_STATUS','LEGAL_STATUS',ORGANIZATION_NAME,'E',
390 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
391
392 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
393 ' and party_rec_id = '||PARTY_REC_ID;
394 REC_STATUS := 'E';
395 end if;
396 close legal_status_code;
397 end if;
398
399 if (HQ_BRANCH_IND is not null) then
400 open hq_branch(HQ_BRANCH_IND);
401 fetch hq_branch into lookup_code_count;
402 if (lookup_code_count=0) then
403 insert into imc_csv_error_details(
404 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
405 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
406 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',HQ_BRANCH_IND,null,'HQ_BRANCH_IND','HQ_BRANCH_IND',ORGANIZATION_NAME,'E',
407 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
408 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
409 ' and party_rec_id = '||PARTY_REC_ID;
410 REC_STATUS := 'E';
411 end if;
412 close hq_branch;
413 end if;
414
415 if (FISCAL_YEAREND_MONTH is not null) then
416 open FISCAL_YREND_MONTH(FISCAL_YEAREND_MONTH);
417 fetch FISCAL_YREND_MONTH into lookup_code_count;
418 if (lookup_code_count=0) then
419 insert into imc_csv_error_details(
420 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
421 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
422 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',FISCAL_YEAREND_MONTH,null,'FISCAL_YEAREND_MONTH','MONTH',ORGANIZATION_NAME,'E',
423 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
424 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
425 ' and party_rec_id = '||PARTY_REC_ID;
426 REC_STATUS := 'E';
427 end if;
428 close FISCAL_YREND_MONTH;
429 end if;
430
431 if (REGISTRATION_TYPE is not null) then
432 open REGISTRY_TYPE(REGISTRATION_TYPE);
433 fetch REGISTRY_TYPE into lookup_code_count;
434 if (lookup_code_count=0) then
435 --dbms_output.put_line('lookup code count'||lookup_code_count);
436 --dbms_output.put_line('resistation type'||REGISTRATION_TYPE);
437 insert into imc_csv_error_details(
438 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
439 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
440 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',REGISTRATION_TYPE,null,'REGISTRATION_TYPE','REGISTRATION_TYPE',ORGANIZATION_NAME,'E',
441 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
442 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
443 ' and party_rec_id = '||PARTY_REC_ID;
444 REC_STATUS := 'E';
445 end if;
446 close REGISTRY_TYPE;
447 end if;
448
449 if (OOB_IND is not null) then
450 if (OOB_IND='Y' OR OOB_IND='N') then
451 null;
452 else
453 insert into imc_csv_error_details(
454 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
455 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
456 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',OOB_IND,null,'OOB_IND','IMC_CSV_Y_OR_N',ORGANIZATION_NAME,'E',
457 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
458 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
459 ' and party_rec_id = '||PARTY_REC_ID;
460 REC_STATUS := 'E';
461 end if;
462 end if;
463
464 if (BRANCH_FLAG is not null) then
465 if (BRANCH_FLAG='Y' OR BRANCH_FLAG='N') then
466 null;
467 else
468 insert into imc_csv_error_details(
469 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
470 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
471 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',BRANCH_FLAG,null,'BRANCH_FLAG','IMC_CSV_Y_OR_N',ORGANIZATION_NAME,'E',
472 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
473 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
474 ' and party_rec_id = '||PARTY_REC_ID;
475 REC_STATUS := 'E';
476 end if;
477 end if;
478
479 if (PARENT_SUB_IND is not null) then
480 if (PARENT_SUB_IND='Y' OR PARENT_SUB_IND='N') then
481 null;
482 else
483 insert into imc_csv_error_details(
484 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
485 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
486 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',PARENT_SUB_IND,null,'PARENT_SUB_IND','IMC_CSV_Y_OR_N',ORGANIZATION_NAME,'E',
487 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
488 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
489 ' and party_rec_id = '||PARTY_REC_ID;
490 REC_STATUS := 'E';
491 end if;
492 end if;
493
494 if (PUBLIC_PRIVATE_OWNERSHIP_FLAG is not null) then
495 if (PUBLIC_PRIVATE_OWNERSHIP_FLAG='Y' OR PUBLIC_PRIVATE_OWNERSHIP_FLAG='N') then
496 null;
497 else
498 insert into imc_csv_error_details(
499 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
500 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
501 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',PUBLIC_PRIVATE_OWNERSHIP_FLAG,null,'PUBLIC_PRIVATE_OWNERSHIP_FLAG','IMC_CSV_Y_OR_N',ORGANIZATION_NAME,'E',
502 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
503 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
504 ' and party_rec_id = '||PARTY_REC_ID;
505 REC_STATUS := 'E';
506 end if;
507 end if;
508 END IF;
509
510 ----Validations for the Person fields
511 if (PARTY_TYPE='PERSON') then
512 if (PERSON_FIRST_NAME is null and PERSON_LAST_NAME is null) then
513 insert into imc_csv_error_details(
514 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
515 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
516 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',null,null,'PERSON_FIRST_NAME',null,null,'E',
517 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
518
519 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
520 ' and party_rec_id = '||PARTY_REC_ID;
521 REC_STATUS := 'E';
522 else
523 if (ORGANIZATION_NAME is not null) then
524 CONTACT_FLAG:='Y';
525 end if;
526 end if;
527 PARTY_NAME:=PERSON_FIRST_NAME||' '||PERSON_LAST_NAME;
528 --Data Type validations for PERSONAL_INCOME,HOUSEHOLD_INCOME,HOUSEHOLD_SIZE
529
530 if(PERSONAL_INCOME is not null) then
531 begin
532 per_income := to_number(PERSONAL_INCOME);
533 EXCEPTION
534 WHEN OTHERS THEN
535 insert into imc_csv_error_details(
536 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
537 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
538 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',PERSONAL_INCOME,null,'PERSONAL_INCOME',null,PARTY_NAME,'E',
539 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
540
541 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
542 ' and party_rec_id = '||PARTY_REC_ID;
543 REC_STATUS := 'E';
544 end;
545 end if;
546
547 if(HOUSEHOLD_INCOME is not null) then
548 begin
549 house_income := to_number(HOUSEHOLD_INCOME);
550 EXCEPTION
551 WHEN OTHERS THEN
552 insert into imc_csv_error_details(
553 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
554 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
555 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',HOUSEHOLD_INCOME,null,'HOUSEHOLD_INCOME',null,PARTY_NAME,'E',
556 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
557
558 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
559 ' and party_rec_id = '||PARTY_REC_ID;
560 REC_STATUS := 'E';
561 end;
562 end if;
563
564 if(HOUSEHOLD_SIZE is not null) then
565 begin
566 house_size := to_number(HOUSEHOLD_SIZE);
567 EXCEPTION
568 WHEN OTHERS THEN
569 insert into imc_csv_error_details(
570 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
571 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
572 loadId,batchId,PARTY_REC_ID,'DATA_TYPE',HOUSEHOLD_SIZE,null,'HOUSEHOLD_SIZE',null,PARTY_NAME,'E',
573 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
574 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
575 ' and party_rec_id = '||PARTY_REC_ID;
576 REC_STATUS := 'E';
577 end;
578 end if;
579
580
581 IF (DATE_OF_BIRTH is not null ) then
582 begin
583 birthdate := to_date(DATE_OF_BIRTH,date_format);
584 EXCEPTION
585 WHEN OTHERS THEN
586 insert into imc_csv_error_details(
587 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
588 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
589 loadId,batchId,PARTY_REC_ID,'DATE_FORMAT',DATE_OF_BIRTH,null,'DATE_OF_BIRTH',null,PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
590 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
591
592 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
593 ' and party_rec_id = '||PARTY_REC_ID;
594 REC_STATUS := 'E';
595 end;
596 end if;
597
598 IF (DATE_OF_DEATH is not null ) then
599 begin
600 deathdate := to_date(DATE_OF_DEATH,date_format);
601 EXCEPTION
602 WHEN OTHERS THEN
603 insert into imc_csv_error_details(
604 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
605 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
606 loadId,batchId,PARTY_REC_ID,'DATE_FORMAT',DATE_OF_DEATH,null,'DATE_OF_DEATH',null,PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
607 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
608
609 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
610 ' and party_rec_id = '||PARTY_REC_ID;
611 REC_STATUS := 'E';
612 end;
613 end if;
614
615 IF (MARITAL_STATUS_EFFECTIVE_DATE is not null ) then
616 begin
617 maritaldate := to_date(MARITAL_STATUS_EFFECTIVE_DATE,date_format);
618 EXCEPTION
619 WHEN OTHERS THEN
620 insert into imc_csv_error_details(
621 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
622 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
623 loadId,batchId,PARTY_REC_ID,'DATE_FORMAT',MARITAL_STATUS_EFFECTIVE_DATE,null,'MARITAL_STATUS_EFFECTIVE_DATE',null,PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
624 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
625
626 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
627 ' and party_rec_id = '||PARTY_REC_ID;
628 REC_STATUS := 'E';
629 end;
630 end if;
631
632 if (PERSON_PRE_NAME_ADJUNCT is not null) then
633 open contact_title(PERSON_PRE_NAME_ADJUNCT);
634 fetch contact_title into lookup_code_count;
635 if (lookup_code_count=0) then
636 insert into imc_csv_error_details(
637 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
638 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
639 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',
640 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
641 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
642 ' and party_rec_id = '||PARTY_REC_ID;
643 REC_STATUS := 'E';
644 end if;
645 close contact_title;
646 end if;
647
648 if (MARITAL_STATUS is not null) then
649 open marry_status(MARITAL_STATUS);
650 fetch marry_status into lookup_code_count;
651 if (lookup_code_count=0) then
652 insert into imc_csv_error_details(
653 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
654 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
655 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',MARITAL_STATUS,null,'MARITAL_STATUS','MARITAL_STATUS',PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
656 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
657 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
658 ' and party_rec_id = '||PARTY_REC_ID;
659 REC_STATUS := 'E';
660 end if;
661 close marry_status;
662 end if;
663
664 if (RENT_OWN_IND is not null) then
665 open own_rent(RENT_OWN_IND);
666 fetch own_rent into lookup_code_count;
667 if (lookup_code_count=0) then
668 insert into imc_csv_error_details(
669 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
670 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
671 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',RENT_OWN_IND,null,'RENT_OWN_IND','OWN_RENT_IND',PERSON_FIRST_NAME||' '||PERSON_LAST_NAME,'E',
672 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
673 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
674 ' and party_rec_id = '||PARTY_REC_ID;
675 REC_STATUS := 'E';
676 end if;
677 close own_rent;
678 end if;
679
680 if (HEAD_OF_HOUSEHOLD_FLAG is not null) then
681 if (HEAD_OF_HOUSEHOLD_FLAG='Y' OR HEAD_OF_HOUSEHOLD_FLAG='N') then
682 null;
683 else
684 insert into imc_csv_error_details(
685 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
686 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
687 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',
688 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
689 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
690 ' and party_rec_id = '||PARTY_REC_ID;
691 REC_STATUS := 'E';
692 end if;
693 end if;
694 END IF;
695
696 ----Validations for the Address fields
697 if(ADDRESS_FLAG ='Y') then
698 if (ADDRESS1 is null) then
699 insert into imc_csv_error_details(
700 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
701 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
702 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',ADDRESS1,null,'ADDRESS1',null,PARTY_NAME,'E',
703 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
704 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
705 ' and party_rec_id = '||PARTY_REC_ID;
706 REC_STATUS := 'E';
707 end if;
708
709 if (COUNTRY is not null) then
710 open country_code(COUNTRY);
711 fetch country_code into lookup_code_count;
712 if (lookup_code_count=0) then
713 insert into imc_csv_error_details(
714 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
715 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
716 loadId,batchId,PARTY_REC_ID,'VALUE_ERROR',COUNTRY,null,'COUNTRY',null,PARTY_NAME,'E',
717 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
718 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
719 ' and party_rec_id = '||PARTY_REC_ID;
720 REC_STATUS := 'E';
721 end if;
722 close country_code;
723 else
724 insert into imc_csv_error_details(
725 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
726 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
727 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',COUNTRY,null,'COUNTRY',null,PARTY_NAME,'E',
728 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
729 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
730 ' and party_rec_id = '||PARTY_REC_ID;
731 REC_STATUS := 'E';
732 end if;
733
734 if (LANGUAGE is not null) then
735 open language_code(LANGUAGE);
736 fetch language_code into lookup_code_count;
737 if (lookup_code_count=0) then
738 insert into imc_csv_error_details(
739 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
740 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
741 loadId,batchId,PARTY_REC_ID,'VALUE_ERROR',LANGUAGE,null,'LANGUAGE',null,PARTY_NAME,'E',
742 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
743 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
744 ' and party_rec_id = '||PARTY_REC_ID;
745 REC_STATUS := 'E';
746 end if;
747 close language_code;
748 end if;
749 end if;
750
751 ----Validations for the contact points fields
752
753 if (PHONE_LINE_TYPE is not null) then
754 open ph_line_type(PHONE_LINE_TYPE);
755 fetch ph_line_type into lookup_code_count;
756 if (lookup_code_count=0) then
757 insert into imc_csv_error_details(
758 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
759 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
760 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',PHONE_LINE_TYPE,null,'PHONE_LINE_TYPE','PHONE_LINE_TYPE',PARTY_NAME,'E',
761 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
762 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
763 ' and party_rec_id = '||PARTY_REC_ID;
764 REC_STATUS := 'E';
765 end if;
766 close ph_line_type;
767 END IF;
768
769 if(CONTACT_POINTS_FLAG ='Y') then
770 if (CONTACT_POINT_TYPE is not null) then
771 open contact_pnt_type(CONTACT_POINT_TYPE);
772 fetch contact_pnt_type into lookup_code_count;
773 if (lookup_code_count=0) then
774 insert into imc_csv_error_details(
775 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
776 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
777 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',CONTACT_POINT_TYPE,null,'CONTACT_POINT_TYPE','COMMUNICATION_TYPE',PARTY_NAME,'E',
778 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
779 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
780 ' and party_rec_id = '||PARTY_REC_ID;
781 REC_STATUS := 'E';
782 end if;
783 close contact_pnt_type;
784
785 if (CONTACT_POINT_TYPE='PHONE' AND (PHONE_NUMBER is null AND RAW_PHONE_NUMBER is null)) then
786 insert into imc_csv_error_details(
787 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
788 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
789 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',PHONE_NUMBER,null,'PHONE_NUMBER',null,PARTY_NAME,'E',
790 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
791 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
792 ' and party_rec_id = '||PARTY_REC_ID;
793 REC_STATUS := 'E';
794 end if;
795
796 if (CONTACT_POINT_TYPE='FAX' AND (PHONE_NUMBER is null AND RAW_PHONE_NUMBER is null)) then
797 insert into imc_csv_error_details(
798 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
799 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
800 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',PHONE_NUMBER,null,'PHONE_NUMBER',null,PARTY_NAME,'E',
801 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
802 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
803 ' and party_rec_id = '||PARTY_REC_ID;
804 REC_STATUS := 'E';
805 end if;
806
807 if (CONTACT_POINT_TYPE='EMAIL' AND EMAIL_ADDRESS is null) then
808 insert into imc_csv_error_details(
809 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
810 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
811 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',EMAIL_ADDRESS,null,'EMAIL_ADDRESS',null,PARTY_NAME,'E',
812 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
813 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
814 ' and party_rec_id = '||PARTY_REC_ID;
815 REC_STATUS := 'E';
816 end if;
817
818 if (CONTACT_POINT_TYPE='WEB' AND URL is null) then
819 insert into imc_csv_error_details(
820 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
821 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
822 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',URL,null,'URL',null,PARTY_NAME,'E',
823 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
824 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
825 ' and party_rec_id = '||PARTY_REC_ID;
826 REC_STATUS := 'E';
827 end if;
828
829 if (CONTACT_POINT_TYPE='TLX' AND TELEX_NUMBER is null) then
830 insert into imc_csv_error_details(
831 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
832 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
833 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',TELEX_NUMBER,null,'TELEX_NUMBER',null,PARTY_NAME,'E',
834 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
835 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
836 ' and party_rec_id = '||PARTY_REC_ID;
837 REC_STATUS := 'E';
838 end if;
839
840 end if;
841 end if;
842
843 ----Validations for the classifications fields
844 if (CLASSIFICATION_FLAG = 'Y') then
845 if (CLASS_CATEGORY is null) then
846 insert into imc_csv_error_details(
847 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
848 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
849 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',CLASS_CATEGORY,null,'CLASS_CATEGORY','SIC_CODE_TYPE',PARTY_NAME,'E',
850 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
851 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
852 ' and party_rec_id = '||PARTY_REC_ID;
853 REC_STATUS := 'E';
854 else
855 open clss_category(CLASS_CATEGORY);
856 fetch clss_category into lookup_code_count;
857 close clss_category;
858 if (lookup_code_count=0) then
859 insert into imc_csv_error_details(
860 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
861 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
862 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',CLASS_CATEGORY,null,'CLASS_CATEGORY','SIC_CODE_TYPE',PARTY_NAME,'E',
863 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
864 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
865 ' and party_rec_id = '||PARTY_REC_ID;
866 REC_STATUS := 'E';
867 else
868 if (CLASS_CODE is null) then
869 insert into imc_csv_error_details(
870 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
871 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
872 loadId,batchId,PARTY_REC_ID,'MANDATORY_FIELDS',CLASS_CODE,null,'CLASS_CODE',CLASS_CATEGORY,PARTY_NAME,'E',
873 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
874 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
875 ' and party_rec_id = '||PARTY_REC_ID;
876 REC_STATUS := 'E';
877 else
878 open clss_code(CLASS_CATEGORY,CLASS_CODE);
879 fetch clss_code into lookup_code_count;
880 close clss_code;
881 if (lookup_code_count=0) then
882 insert into imc_csv_error_details(
883 LOAD_ID,BATCH_ID,PARTY_REC_ID,ERROR_TYPE,ERROR_VALUE,NEW_VALUE,ATTRIBUTE_ERRORED,LOOKUP_ERROR,PARTY_NAME,ERROR_STATUS,
884 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN) values(
885 loadId,batchId,PARTY_REC_ID,'LOOKUP_ERROR',CLASS_CODE,null,'CLASS_CODE',CLASS_CATEGORY,PARTY_NAME,'E',
886 SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
887 execute immediate 'update imc_csv_interface_fields set rec_status=''E'' where load_id ='||loadId||' and batch_id = '||batchId||
888 ' and party_rec_id = '||PARTY_REC_ID;
889 REC_STATUS := 'E';
890 end if;
891 end if;
892 end if;
893
894 end if;
895
896 end if;
897
898
899 --INSERT INTO INTERFACE TABLES
900 if (REC_STATUS is null) OR (REC_STATUS<>'E') then
901 if (PARTY_TYPE='ORGANIZATION') then
902 --dbms_output.put_line('inserting into HZ parties interface');
903 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,
904 PRINCIPAL_TITLE,LEGAL_STATUS,CONTROL_YR,EMPLOYEES_TOTAL,HQ_BRANCH_IND,BRANCH_FLAG,OOB_IND,TAX_REFERENCE,
905 GSA_INDICATOR_FLAG,JGZZ_FISCAL_CODE,ANALYSIS_FY,FISCAL_YEAREND_MONTH,CURR_FY_POTENTIAL_REVENUE,
906 NEXT_FY_POTENTIAL_REVENUE,YEAR_ESTABLISHED,MISSION_STATEMENT,ORGANIZATION_TYPE,BUSINESS_SCOPE,KNOWN_AS,
907 LOCAL_BUS_IDEN_TYPE,LOCAL_BUS_IDENTIFIER,PREF_FUNCTIONAL_CURRENCY,REGISTRATION_TYPE,PARENT_SUB_IND,INCORP_YEAR,
908 PUBLIC_PRIVATE_OWNERSHIP_FLAG,TOTAL_PAYMENTS,DUNS_NUMBER_C,CREATION_DATE,CREATED_BY,
909 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
910 values (batchId,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,'ORGANIZATION',ORGANIZATION_NAME,CEO_NAME,CEO_TITLE,PRINCIPAL_NAME,
911 PRINCIPAL_TITLE,LEGAL_STATUS,control_year,emp_total,HQ_BRANCH_IND,BRANCH_FLAG,OOB_IND,TAX_REFERENCE,
912 GSA_INDICATOR_FLAG,JGZZ_FISCAL_CODE,ANALYSIS_FY,FISCAL_YEAREND_MONTH,CURR_FY_POTENTIAL_REVENUE,
913 NEXT_FY_POTENTIAL_REVENUE,yr_established,MISSION_STATEMENT,ORGANIZATION_TYPE,BUSINESS_SCOPE,KNOWN_AS,
914 LOCAL_BUS_IDEN_TYPE,LOCAL_BUS_IDENTIFIER,PREF_FUNCTIONAL_CURRENCY,REGISTRATION_TYPE,PARENT_SUB_IND,
915 incorporation_year,PUBLIC_PRIVATE_OWNERSHIP_FLAG,total_pays,DUNS_NUMBER_C,SYSDATE,FND_GLOBAL.user_id,SYSDATE,
916 FND_GLOBAL.user_id,FND_GLOBAL.login_id);
917 elsif (PARTY_TYPE='PERSON') then
918 insert into HZ_IMP_PARTIES_INT(batch_id,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,PARTY_TYPE,PERSON_PRE_NAME_ADJUNCT,PERSON_FIRST_NAME,
919 PERSON_MIDDLE_NAME,PERSON_LAST_NAME,PERSON_NAME_SUFFIX,PERSON_TITLE,PERSON_ACADEMIC_TITLE,
920 PERSON_PREVIOUS_LAST_NAME,PERSON_INITIALS,KNOWN_AS,PERSON_NAME_PHONETIC,PERSON_FIRST_NAME_PHONETIC,
921 person_MIDDLE_NAME_PHONETIC,PERSON_LAST_NAME_PHONETIC,TAX_REFERENCE,JGZZ_FISCAL_CODE,PERSON_IDEN_TYPE,
922 PERSON_IDENTIFIER,DATE_OF_BIRTH,PLACE_OF_BIRTH,DATE_OF_DEATH,GENDER,DECLARED_ETHNICITY,MARITAL_STATUS,
923 MARITAL_STATUS_EFFECTIVE_DATE,PERSONAL_INCOME,HEAD_OF_HOUSEHOLD_FLAG,HOUSEHOLD_INCOME,
924 HOUSEHOLD_SIZE,RENT_OWN_IND,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
925 values(batchId,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,'PERSON',PERSON_PRE_NAME_ADJUNCT,PERSON_FIRST_NAME,
926 PERSON_MIDDLE_NAME,PERSON_LAST_NAME,PERSON_NAME_SUFFIX,PERSON_TITLE,PERSON_ACADEMIC_TITLE,
927 PERSON_PREVIOUS_LAST_NAME,PERSON_INITIALS,KNOWN_AS,PERSON_NAME_PHONETIC,PERSON_FIRST_NAME_PHONETIC,
928 PERSON_MIDDLE_NAME_PHONETIC,PERSON_LAST_NAME_PHONETIC,TAX_REFERENCE,JGZZ_FISCAL_CODE,PERSON_IDEN_TYPE,
929 PERSON_IDENTIFIER,birthdate,PLACE_OF_BIRTH,deathdate,GENDER,DECLARED_ETHNICITY,MARITAL_STATUS,
930 maritaldate,per_income,HEAD_OF_HOUSEHOLD_FLAG,house_income,house_size,
931 RENT_OWN_IND,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
932 if(CONTACT_FLAG='Y') then
933 old_contact_org := 'N';
934 i:=1;
935 while(i<=org_contact.COUNT) LOOP
936 org_contact_rec := org_contact(i);
937 old_org_name := org_contact_rec.org_name ;
938 orig_sys_ref := org_contact_rec.original_sys_ref;
939 if(old_org_name = ORGANIZATION_NAME) then
940 old_contact_org := 'Y';
941 CNTORG_ORIG_SYSTEM_REFERENCE := orig_sys_ref;
942 end if;
943 i:=i+1;
944 end loop;
945
946 if(old_contact_org ='N') then
947
948 open get_next_ref_id;
949 fetch get_next_ref_id into CNTORG_ORIG_SYSTEM_REFERENCE;
950 close get_next_ref_id;
951
952 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,
953 PRINCIPAL_TITLE,LEGAL_STATUS,CONTROL_YR,EMPLOYEES_TOTAL,HQ_BRANCH_IND,BRANCH_FLAG,OOB_IND,TAX_REFERENCE,
954 GSA_INDICATOR_FLAG,JGZZ_FISCAL_CODE,ANALYSIS_FY,FISCAL_YEAREND_MONTH,CURR_FY_POTENTIAL_REVENUE,
955 NEXT_FY_POTENTIAL_REVENUE,YEAR_ESTABLISHED,MISSION_STATEMENT,ORGANIZATION_TYPE,BUSINESS_SCOPE,KNOWN_AS,
956 LOCAL_BUS_IDEN_TYPE,LOCAL_BUS_IDENTIFIER,PREF_FUNCTIONAL_CURRENCY,REGISTRATION_TYPE,PARENT_SUB_IND,INCORP_YEAR,
957 PUBLIC_PRIVATE_OWNERSHIP_FLAG,TOTAL_PAYMENTS,DUNS_NUMBER_C,CREATION_DATE,CREATED_BY,
958 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
959 values (batchId,'CSV',CNTORG_ORIG_SYSTEM_REFERENCE,'ORGANIZATION',ORGANIZATION_NAME,CEO_NAME,CEO_TITLE,PRINCIPAL_NAME,
960 PRINCIPAL_TITLE,LEGAL_STATUS,CONTROL_YR,EMPLOYEES_TOTAL,HQ_BRANCH_IND,BRANCH_FLAG,OOB_IND,TAX_REFERENCE,
961 GSA_INDICATOR_FLAG,JGZZ_FISCAL_CODE,ANALYSIS_FY,FISCAL_YEAREND_MONTH,CURR_FY_POTENTIAL_REVENUE,
962 NEXT_FY_POTENTIAL_REVENUE,YEAR_ESTABLISHED,MISSION_STATEMENT,ORGANIZATION_TYPE,BUSINESS_SCOPE,KNOWN_AS,
963 LOCAL_BUS_IDEN_TYPE,LOCAL_BUS_IDENTIFIER,PREF_FUNCTIONAL_CURRENCY,REGISTRATION_TYPE,PARENT_SUB_IND,INCORP_YEAR,
964 PUBLIC_PRIVATE_OWNERSHIP_FLAG,TOTAL_PAYMENTS,DUNS_NUMBER_C,SYSDATE,FND_GLOBAL.user_id,SYSDATE,
965 FND_GLOBAL.user_id,FND_GLOBAL.login_id);
966
967 org_contact_rec.org_name := ORGANIZATION_NAME ;
968 org_contact_rec.original_sys_ref := CNTORG_ORIG_SYSTEM_REFERENCE;
969 org_contact(i) := org_contact_rec;
970
971 end if;
972
973 open get_next_ref_id;
974 fetch get_next_ref_id into CNT_ORIG_SYSTEM_REFERENCE;
975 close get_next_ref_id;
976
977 insert into HZ_IMP_CONTACTS_INT(
978 BATCH_ID,CONTACT_ORIG_SYSTEM,CONTACT_ORIG_SYSTEM_REFERENCE,SUB_ORIG_SYSTEM,SUB_ORIG_SYSTEM_REFERENCE,
979 OBJ_ORIG_SYSTEM,OBJ_ORIG_SYSTEM_REFERENCE ,DEPARTMENT_CODE,DEPARTMENT,TITLE,JOB_TITLE,RELATIONSHIP_TYPE,RELATIONSHIP_CODE,
980 START_DATE,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
981 values(batchId,'CSV',CNT_ORIG_SYSTEM_REFERENCE,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,
982 'CSV',CNTORG_ORIG_SYSTEM_REFERENCE,DEPARTMENT_CODE,DEPARTMENT,TITLE,JOB_TITLE,'CONTACT','CONTACT_OF',
983 SYSDATE,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id) ;
984 end if;
985 end if;
986
987 if(ADDRESS_FLAG ='Y') then
988
989 open get_next_ref_id;
990 fetch get_next_ref_id into SITE_ORIG_SYSTEM_REFERENCE;
991 close get_next_ref_id;
992
993 insert into HZ_IMP_ADDRESSES_INT(batch_id,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,SITE_ORIG_SYSTEM,
994 SITE_ORIG_SYSTEM_REFERENCE,COUNTRY,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,CITY,
995 POSTAL_CODE,STATE,PROVINCE,COUNTY,ADDRESS_LINES_PHONETIC,LOCATION_DIRECTIONS,DESCRIPTION,SALES_TAX_GEOCODE,
996 PRIMARY_FLAG,PARTY_SITE_NAME,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
997 values(batchId,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,'CSV',SITE_ORIG_SYSTEM_REFERENCE,COUNTRY,ADDRESS1,ADDRESS2,
998 ADDRESS3,ADDRESS4,CITY,POSTAL_CODE,STATE,PROVINCE,COUNTY,ADDRESS_LINES_PHONETIC,LOCATION_DIRECTIONS,
999 DESCRIPTION,SALES_TAX_GEOCODE,PRIMARY_FLAG,PARTY_SITE_NAME,SYSDATE,FND_GLOBAL.user_id,SYSDATE,
1000 FND_GLOBAL.user_id,FND_GLOBAL.login_id);
1001 end if;
1002
1003 if(CONTACT_POINTS_FLAG ='Y') then
1004 /* Bug 3854824: If contact_point_type is not passed, based on the
1005 contact point passed, set the contact point type */
1006 /* Bug 5371056 : insert one row to hz_imp_contactpts_int for
1007 | each contact point */
1008 if (PHONE_NUMBER is not null OR RAW_PHONE_NUMBER is not null) then
1009 CONTACT_POINT_TYPE := 'PHONE';
1010 if (PHONE_LINE_TYPE is null) then
1011 PHONE_LINE_TYPE := 'GEN';
1012 end if;
1013 open get_next_ref_id;
1014 fetch get_next_ref_id into CP_ORIG_SYSTEM_REFERENCE;
1015 close get_next_ref_id;
1016
1017 insert into HZ_IMP_CONTACTPTS_INT(
1018 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,
1019 PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,CREATION_DATE,CREATED_BY,
1020 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
1021 values(batchId,'CSV',CP_ORIG_SYSTEM_REFERENCE,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,
1022 PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,
1023 RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
1024 end if;
1025
1026 if (EMAIL_ADDRESS is not null) then
1027 CONTACT_POINT_TYPE := 'EMAIL';
1028 open get_next_ref_id;
1029 fetch get_next_ref_id into CP_ORIG_SYSTEM_REFERENCE;
1030 close get_next_ref_id;
1031
1032 insert into HZ_IMP_CONTACTPTS_INT(
1033 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,
1034 PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,CREATION_DATE,CREATED_BY,
1035 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
1036 values(batchId,'CSV',CP_ORIG_SYSTEM_REFERENCE,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,
1037 PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,
1038 RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
1039 end if;
1040
1041 if (URL is not null) then
1042 CONTACT_POINT_TYPE := 'WEB';
1043 open get_next_ref_id;
1044 fetch get_next_ref_id into CP_ORIG_SYSTEM_REFERENCE;
1045 close get_next_ref_id;
1046
1047 insert into HZ_IMP_CONTACTPTS_INT(
1048 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,
1049 PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,CREATION_DATE,CREATED_BY,
1050 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
1051 values(batchId,'CSV',CP_ORIG_SYSTEM_REFERENCE,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,
1052 PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,
1053 RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
1054 end if;
1055
1056 if (TELEX_NUMBER is not null) then
1057 CONTACT_POINT_TYPE := 'TLX';
1058 open get_next_ref_id;
1059 fetch get_next_ref_id into CP_ORIG_SYSTEM_REFERENCE;
1060 close get_next_ref_id;
1061
1062 insert into HZ_IMP_CONTACTPTS_INT(
1063 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,
1064 PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,CREATION_DATE,CREATED_BY,
1065 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
1066 values(batchId,'CSV',CP_ORIG_SYSTEM_REFERENCE,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,CONTACT_POINT_TYPE,EMAIL_FORMAT,EMAIL_ADDRESS,
1067 PHONE_AREA_CODE,PHONE_COUNTRY_CODE,PHONE_NUMBER,PHONE_EXTENSION,PHONE_LINE_TYPE,TELEX_NUMBER,WEB_TYPE,URL,
1068 RAW_PHONE_NUMBER,CONTACT_POINT_PURPOSE,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
1069 end if;
1070 end if;
1071
1072 if(CLASSIFICATION_FLAG ='Y') then
1073 insert into HZ_IMP_CLASSIFICS_INT(BATCH_ID,PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE,CLASS_CATEGORY,CLASS_CODE,
1074 START_DATE_ACTIVE, CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
1075 values(batchId,'CSV',PARTY_ORIG_SYSTEM_REFERENCE,CLASS_CATEGORY,CLASS_CODE,SYSDATE,SYSDATE,FND_GLOBAL.user_id,
1076 SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id);
1077 end if;
1078 --dbms_output.put_line('before the update to rec status to P');
1079 execute immediate 'delete from imc_csv_interface_fields where load_id ='||loadId||' and batch_id = '||batchId||
1080 ' and party_rec_id = '||PARTY_REC_ID;
1081 end if;
1082 end loop;
1083 close get_details;
1084 -- Activating the created batch
1085
1086 EXCEPTION
1087 WHEN FND_API.G_EXC_ERROR THEN
1088 ROLLBACK;
1089 x_return_status := FND_API.G_RET_STS_ERROR;
1090 FND_MSG_PUB.Count_And_Get(
1091 p_encoded => FND_API.G_FALSE,
1092 p_count => x_msg_count,
1093 p_data => x_msg_data);
1094 RAISE FND_API.G_EXC_ERROR;
1095 execute immediate 'update hz_imp_batch_summary set csv_status=''ERROR'' where load_type=''CSV'' and batch_id = '||batchId;
1096
1097
1098 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1099 ROLLBACK;
1100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101 FND_MSG_PUB.Count_And_Get(
1102 p_encoded => FND_API.G_FALSE,
1103 p_count => x_msg_count,
1104 p_data => x_msg_data);
1105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1106 execute immediate 'update hz_imp_batch_summary set csv_status=''ERROR'' where load_type=''CSV'' and batch_id = '||batchId;
1107
1108
1109 WHEN OTHERS THEN
1110 ROLLBACK;
1111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1112
1113 FND_MESSAGE.SET_NAME('AR', 'IMC_API_OTHERS_EXCEP');
1114 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
1115 FND_MSG_PUB.ADD;
1116
1117 FND_MSG_PUB.Count_And_Get(
1118 p_encoded => FND_API.G_FALSE,
1119 p_count => x_msg_count,
1120 p_data => x_msg_data );
1121 RAISE FND_API.G_EXC_ERROR;
1122 execute immediate 'update hz_imp_batch_summary set csv_status=''ERROR'' where load_type=''CSV'' and batch_id = '||batchId;
1123
1124 END;
1125 END IMC_CSV_LOAD;