[Home] [Help]
PACKAGE: APPS.AMS_LISTIMPORT_PVT
Source
1 PACKAGE AMS_ListImport_PVT AUTHID CURRENT_USER AS
2 /* $Header: amsvimls.pls 115.35 2003/12/23 18:22:23 usingh ship $ */
3
4 -----------------------------------------------------------
5 G_PARTY_MAPPED VARCHAR2(1);
6
7 -- PACKAGE
8 -- AMS_ListImport_PVT
9 --
10 -- PURPOSE
11 -- This purpose of this program is to create organization,person
12 -- ,party relationship, org contacts, locations , party sites,
13 -- email and phone records for B2B or B2C type customer's
14 --
15 -- Call TCA API's to create the records in HZ schema.
16 --
17 --
18 -- For B2B creates the following using TCA API's
19 --
20 -- 1. Create organization
21 -- 2. Create Person
22 -- 3. Create Party Relation
23 -- 4. Create Party for Party Relationship
24 -- 5. Create Org contact
25 -- 6. Create Location (if address is available)
26 -- 7. Create Party Site (if address is available)
27 -- 8. Create Contact Points (if contact points are available)
28 --
29 --
30 --
31 --
32 -- For B2C creates the following using TCA API's
33 --
34 -- 1. Create Person
35 -- 2. Create Location (if address is available)
36 -- 3. Create Party Site (if address is available)
37 -- 4. Create Contact Points (if contact points are available)
38 --
39 -- PROCEDURES
40 -- list_import_to_hz
41 --
42 -- PARAMETERS
43 -- INPUT
44 -- p_import_list_header_id NUMBER.
45 --
46 -- OUTPUT
47 -- Errbuf VARCHAR2 -- Conc Pgm Error mesgs.
48 -- RetCode VARCHAR2 -- Conc Pgm Error Code.
49 -- 0 - Success, 2 - Failure.
50 --
51 -- HISTORY
52 -- 19-Mar-2001 usingh Created.
53 -- ---------------------------------------------------------
54 TYPE colNmValue IS TABLE of AMS_IMP_XML_ELEMENTS%ROWTYPE INDEX BY BINARY_INTEGER;
55
56 TYPE data_in_rec_type is RECORD (
57 cust_data_id NUMBER := FND_API.G_MISS_NUM,
58 org_imp_xml_element_id NUMBER := FND_API.G_MISS_NUM,
59 PARTY_NAME VARCHAR2(2000):= FND_API.G_MISS_CHAR,
60 FISCAL_YEAREND_MONTH VARCHAR2(2000):= FND_API.G_MISS_CHAR,
61 DUNS_NUMBER VARCHAR2(2000):= FND_API.G_MISS_CHAR,
62 EMPLOYEES_TOTAL VARCHAR2(2000):= FND_API.G_MISS_CHAR,
63 LINE_OF_BUSINESS VARCHAR2(2000):= FND_API.G_MISS_CHAR,
64 YEAR_ESTABLISHED VARCHAR2(2000):= FND_API.G_MISS_CHAR,
65 TAX_REFERENCE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
66 ORIG_SYSTEM_REFERENCE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
67 CEO_NAME VARCHAR2(2000):= FND_API.G_MISS_CHAR,
68 SIC_CODE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
69 SIC_CODE_TYPE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
70 ANALYSIS_FY VARCHAR2(2000):= FND_API.G_MISS_CHAR,
71 CURR_FY_POTENTIAL_REVENUE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
72 NEXT_FY_POTENTIAL_REVENUE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
73 GSA_INDICATOR_FLAG VARCHAR2(2000):= FND_API.G_MISS_CHAR,
74 MISSION_STATEMENT VARCHAR2(2000):= FND_API.G_MISS_CHAR,
75 ORGANIZATION_NAME_PHONETIC VARCHAR2(2000):= FND_API.G_MISS_CHAR,
76 CATEGORY_CODE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
77 JGZZ_FISCAL_CODE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
78 PARTY_ID VARCHAR2(2000):= FND_API.G_MISS_CHAR,
79 BRANCH_FLAG VARCHAR2(2000):= FND_API.G_MISS_CHAR,
80 BUSINESS_LINE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
81 BUSINESS_SCOPE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
82 CHIEF_EXECUTIVE_TITLE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
83 CONGRESSIONAL_DISTRICT_CODE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
84 CONTROL_YEAR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
85 CORPORATION_CLASS VARCHAR2(2000):= FND_API.G_MISS_CHAR,
86 CREDIT_SCORE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
87 CREDIT_SCORE_COMMENTARY VARCHAR2(2000):= FND_API.G_MISS_CHAR,
88 CUSTOMER_CATEGORY VARCHAR2(2000):= FND_API.G_MISS_CHAR,
89 DB_RATING VARCHAR2(2000):= FND_API.G_MISS_CHAR,
90 DEBARMENTS_COUNT VARCHAR2(2000):= FND_API.G_MISS_CHAR,
91 DEBARTMENTS_DATE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
92 DEPARTMENT_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
93 DISADVANTAGED_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
94 ENQUIRY_DUNS VARCHAR2(2000):= FND_API.G_MISS_CHAR,
95 EXPORT_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
96 FAILURE_SCORE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
97 FAILURE_SCORE_COMMENTARY VARCHAR2(2000):= FND_API.G_MISS_CHAR,
98 FAILURE_SCORE_NATL_PERCENTILE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
99 FAILURE_SCORE_OVERRIDE_CODE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
100 GLOBAL_FAILURE_SCORE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
101 HEADQUARTER_BRANCH_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
102 IMPORT_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
103 ORGANIZATION_KNOWN_AS VARCHAR2(2000):= FND_API.G_MISS_CHAR,
104 ORGANIZATION_KNOWN_AS2 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
105 ORGANIZATION_KNOWN_AS3 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
106 ORGANIZATION_KNOWN_AS4 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
107 ORGANIZATION_KNOWN_AS5 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
108 LABOR_SURPLUS_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
109 LOCAL_ACTIVITY_CODE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
110 LOCAL_ACTIVITY_CODE_TYPE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
111 MINORITY_OWNED_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
112 MINORITY_OWNED_TYPE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
113 ORGANIZATION_TYPE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
114 ORGANIZATION_URL VARCHAR2(2000):= FND_API.G_MISS_CHAR,
115 OUT_OF_BUSINESS_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
116 PRINCIPAL_NAME VARCHAR2(2000):= FND_API.G_MISS_CHAR,
117 PRINCIPAL_TITLE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
118 PUBLIC_PRIVATE_OWNERSHIP_FLAG VARCHAR2(2000):= FND_API.G_MISS_CHAR,
119 RENT_OWNED_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
120 RENT_OWNER_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
121 SMALL_BUSINESS_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
122 WOMAN_OWNED_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
123 ORG_ATTRIBUTE_CATEGORY VARCHAR2(2000):= FND_API.G_MISS_CHAR,
124 ORGANIZATION_ATTRIBUTE1 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
125 ORGANIZATION_ATTRIBUTE2 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
126 ORGANIZATION_ATTRIBUTE3 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
127 ORGANIZATION_ATTRIBUTE4 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
128 ORGANIZATION_ATTRIBUTE5 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
129 ORGANIZATION_ATTRIBUTE6 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
130 ORGANIZATION_ATTRIBUTE7 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
131 ORGANIZATION_ATTRIBUTE8 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
132 ORGANIZATION_ATTRIBUTE9 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
133 ORGANIZATION_ATTRIBUTE10 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
134 ORGANIZATION_ATTRIBUTE11 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
135 ORGANIZATION_ATTRIBUTE12 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
136 ORGANIZATION_ATTRIBUTE13 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
137 ORGANIZATION_ATTRIBUTE14 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
138 ORGANIZATION_ATTRIBUTE15 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
139 VEHICLE_RESPONSE_CODE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
140 SALES_AGENT_EMAIL_ID VARCHAR2(2000):= FND_API.G_MISS_CHAR,
141 NOTES VARCHAR2(2000):= FND_API.G_MISS_CHAR,
142 add_imp_xml_element_id NUMBER := FND_API.G_MISS_NUM,
143 address1 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
144 address2 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
145 address3 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
146 address4 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
147 city VARCHAR2(2000):= FND_API.G_MISS_CHAR,
148 county VARCHAR2(2000):= FND_API.G_MISS_CHAR,
149 province VARCHAR2(2000):= FND_API.G_MISS_CHAR,
150 state VARCHAR2(2000):= FND_API.G_MISS_CHAR,
151 postal_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
152 country VARCHAR2(2000):= FND_API.G_MISS_CHAR,
153 address_lines_phonetic VARCHAR2(2000):= FND_API.G_MISS_CHAR,
154 po_box_number VARCHAR2(2000):= FND_API.G_MISS_CHAR,
155 house_number VARCHAR2(2000):= FND_API.G_MISS_CHAR,
156 street_suffix VARCHAR2(2000):= FND_API.G_MISS_CHAR,
157 street VARCHAR2(2000):= FND_API.G_MISS_CHAR,
158 street_number VARCHAR2(2000):= FND_API.G_MISS_CHAR,
159 floor VARCHAR2(2000):= FND_API.G_MISS_CHAR,
160 suite VARCHAR2(2000):= FND_API.G_MISS_CHAR,
161 postal_plus4_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
162 identifying_address_flag VARCHAR2(2000):= FND_API.G_MISS_CHAR,
163 ADDRESS_ATTRIBUTE1 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
164 ADDRESS_ATTRIBUTE2 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
165 ADDRESS_ATTRIBUTE3 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
166 ADDRESS_ATTRIBUTE4 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
167 ADDRESS_ATTRIBUTE5 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
168 ADDRESS_ATTRIBUTE6 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
169 ADDRESS_ATTRIBUTE7 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
170 ADDRESS_ATTRIBUTE8 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
171 ADDRESS_ATTRIBUTE9 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
172 ADDRESS_ATTRIBUTE10 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
173 ADDRESS_ATTRIBUTE11 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
174 ADDRESS_ATTRIBUTE12 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
175 ADDRESS_ATTRIBUTE13 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
176 ADDRESS_ATTRIBUTE14 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
177 ADDRESS_ATTRIBUTE15 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
178 DESCRIPTION VARCHAR2(2000):= FND_API.G_MISS_CHAR,
179 ADDRESS_ATTRIBUTE_CATEGORY VARCHAR2(2000):= FND_API.G_MISS_CHAR,
180 PARTY_SITE_USE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
181 LOCATION_DIRECTIONS VARCHAR2(2000):= FND_API.G_MISS_CHAR,
182 SHORT_DESCRIPTION VARCHAR2(2000):= FND_API.G_MISS_CHAR,
183 ADDRESS_EFFECTIVE_DATE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
184 ADDRESS_EXPIRATION_DATE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
185 ocont_imp_xml_element_id NUMBER := FND_API.G_MISS_NUM,
186 person_first_name VARCHAR2(2000):= FND_API.G_MISS_CHAR,
187 person_middle_name VARCHAR2(2000):= FND_API.G_MISS_CHAR,
188 person_name_suffix VARCHAR2(2000):= FND_API.G_MISS_CHAR,
189 person_last_name VARCHAR2(2000):= FND_API.G_MISS_CHAR,
190 person_name_prefix VARCHAR2(2000):= FND_API.G_MISS_CHAR,
191 department VARCHAR2(2000):= FND_API.G_MISS_CHAR,
192 job_title VARCHAR2(2000):= FND_API.G_MISS_CHAR,
193 decision_maker_flag VARCHAR2(2000):= FND_API.G_MISS_CHAR,
194 PERSONAL_INCOME VARCHAR2(2000):= FND_API.G_MISS_CHAR,
195 PERSON_ACADEMIC_TITLE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
196 PERSON_FIRST_NAME_PHONETIC VARCHAR2(2000):= FND_API.G_MISS_CHAR,
197 PERSON_LAST_NAME_PHONETIC VARCHAR2(2000):= FND_API.G_MISS_CHAR,
198 MIDDLE_NAME_PHONETIC VARCHAR2(2000):= FND_API.G_MISS_CHAR,
199 PERSON_NAME_PHONETIC VARCHAR2(2000):= FND_API.G_MISS_CHAR,
200 PERSON_PREVIOUS_TITLE_NAME VARCHAR2(2000):= FND_API.G_MISS_CHAR,
201 PLACE_OF_BIRTH VARCHAR2(2000):= FND_API.G_MISS_CHAR,
202 HEAD_OF_HOUSEHOLD_FLAG VARCHAR2(2000):= FND_API.G_MISS_CHAR,
203 HOUSEHOLD_SIZE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
204 TAX_ID VARCHAR2(2000):= FND_API.G_MISS_CHAR,
205 ORG_CONTACT_ATTRIBUTE_CATEGORY VARCHAR2(2000):= FND_API.G_MISS_CHAR,
206 PERSON_KNOWN_AS VARCHAR2(2000):= FND_API.G_MISS_CHAR,
207 PERSON_KNOWN_AS2 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
208 PERSON_KNOWN_AS3 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
209 PERSON_KNOWN_AS4 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
210 PERSON_KNOWN_AS5 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
211 DATE_OF_BIRTH VARCHAR2(2000):= FND_API.G_MISS_CHAR,
212 DATE_OF_DEATH VARCHAR2(2000):= FND_API.G_MISS_CHAR,
213 DECLARED_ETHNICITY VARCHAR2(2000):= FND_API.G_MISS_CHAR,
214 MARITAL_STATUS VARCHAR2(2000):= FND_API.G_MISS_CHAR,
215 MARITAL_STATUS_EFFECTIVE_DATE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
216 ORG_CONTACT_ATTRIBUTE1 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
217 ORG_CONTACT_ATTRIBUTE2 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
218 ORG_CONTACT_ATTRIBUTE3 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
219 ORG_CONTACT_ATTRIBUTE4 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
220 ORG_CONTACT_ATTRIBUTE5 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
221 ORG_CONTACT_ATTRIBUTE6 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
222 ORG_CONTACT_ATTRIBUTE7 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
223 ORG_CONTACT_ATTRIBUTE8 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
224 ORG_CONTACT_ATTRIBUTE9 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
225 ORG_CONTACT_ATTRIBUTE10 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
226 ORG_CONTACT_ATTRIBUTE11 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
227 ORG_CONTACT_ATTRIBUTE12 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
228 ORG_CONTACT_ATTRIBUTE13 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
229 ORG_CONTACT_ATTRIBUTE14 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
230 ORG_CONTACT_ATTRIBUTE15 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
231 cp_imp_xml_element_id NUMBER := FND_API.G_MISS_NUM,
232 phone_country_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
233 phone_area_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
234 phone_number VARCHAR2(2000):= FND_API.G_MISS_CHAR,
235 phone_extension VARCHAR2(2000):= FND_API.G_MISS_CHAR,
236 em_imp_xml_element_id NUMBER := FND_API.G_MISS_NUM,
237 email_address VARCHAR2(2000):= FND_API.G_MISS_CHAR,
238 fx_imp_xml_element_id NUMBER := FND_API.G_MISS_NUM,
239 fax_country_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
240 fax_area_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
241 fax_number VARCHAR2(2000):= FND_API.G_MISS_CHAR);
242
243
244
245 TYPE data_in_rec_b2c_type is RECORD (
246 cust_data_id NUMBER := FND_API.G_MISS_NUM,
247 per_imp_xml_element_id NUMBER := FND_API.G_MISS_NUM,
248 person_first_name VARCHAR2(2000):= FND_API.G_MISS_CHAR,
249 person_middle_name VARCHAR2(2000):= FND_API.G_MISS_CHAR,
250 person_name_suffix VARCHAR2(2000):= FND_API.G_MISS_CHAR,
251 person_last_name VARCHAR2(2000):= FND_API.G_MISS_CHAR,
252 person_name_prefix VARCHAR2(2000):= FND_API.G_MISS_CHAR,
253 SALUTATION VARCHAR2(2000):= FND_API.G_MISS_CHAR,
254 PARTY_ID VARCHAR2(2000):= FND_API.G_MISS_CHAR,
255 URL VARCHAR2(2000):= FND_API.G_MISS_CHAR,
256 SECOND_TITLE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
257 DATE_OF_BIRTH VARCHAR2(2000):= FND_API.G_MISS_CHAR,
258 PERSON_ACADEMIC_TITLE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
259 PERSON_PREVIOUS_TITLE_NAME VARCHAR2(2000):= FND_API.G_MISS_CHAR,
260 PERSON_KNOWN_AS VARCHAR2(2000):= FND_API.G_MISS_CHAR,
261 PERSON_KNOWN_AS2 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
262 PERSON_KNOWN_AS3 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
263 PERSON_KNOWN_AS4 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
264 PERSON_KNOWN_AS5 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
265 PERSON_NAME_PHONETIC VARCHAR2(2000):= FND_API.G_MISS_CHAR,
266 MIDDLE_NAME_PHONETIC VARCHAR2(2000):= FND_API.G_MISS_CHAR,
267 PERSON_FIRST_NAME_PHONETIC VARCHAR2(2000):= FND_API.G_MISS_CHAR,
268 PERSON_LAST_NAME_PHONETIC VARCHAR2(2000):= FND_API.G_MISS_CHAR,
269 FISCAL_CODE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
270 PLACE_OF_BIRTH VARCHAR2(2000):= FND_API.G_MISS_CHAR,
271 DATE_OF_DEATH VARCHAR2(2000):= FND_API.G_MISS_CHAR,
272 DECLARED_ETHNICITY VARCHAR2(2000):= FND_API.G_MISS_CHAR,
273 MARITAL_STATUS VARCHAR2(2000):= FND_API.G_MISS_CHAR,
274 PERSONAL_INCOME VARCHAR2(2000):= FND_API.G_MISS_CHAR,
275 MARITAL_STATUS_EFFECTIVE_DATE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
276 HEAD_OF_HOUSEHOLD_FLAG VARCHAR2(2000):= FND_API.G_MISS_CHAR,
277 HOUSEHOLD_SIZE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
278 TAX_REFERENCE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
279 ORIG_SYSTEM_REFERENCE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
280 RENT_OWNED_INDICATOR VARCHAR2(2000):= FND_API.G_MISS_CHAR,
281 PERSON_ATTRIBUTE1 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
282 PERSON_ATTRIBUTE2 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
283 PERSON_ATTRIBUTE3 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
284 PERSON_ATTRIBUTE4 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
285 PERSON_ATTRIBUTE5 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
286 PERSON_ATTRIBUTE6 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
287 PERSON_ATTRIBUTE7 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
288 PERSON_ATTRIBUTE8 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
289 PERSON_ATTRIBUTE9 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
290 PERSON_ATTRIBUTE10 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
291 PERSON_ATTRIBUTE11 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
292 PERSON_ATTRIBUTE12 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
293 PERSON_ATTRIBUTE13 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
294 PERSON_ATTRIBUTE14 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
295 PERSON_ATTRIBUTE15 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
296 PERSON_ATTRIBUTE_CATEGORY VARCHAR2(2000):= FND_API.G_MISS_CHAR,
297 VEHICLE_RESPONSE_CODE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
298 SALES_AGENT_EMAIL_ID VARCHAR2(2000):= FND_API.G_MISS_CHAR,
299 NOTES VARCHAR2(2000):= FND_API.G_MISS_CHAR,
300 add_imp_xml_element_id NUMBER := FND_API.G_MISS_NUM,
301 address1 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
302 address2 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
303 address3 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
304 address4 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
305 city VARCHAR2(2000):= FND_API.G_MISS_CHAR,
306 county VARCHAR2(2000):= FND_API.G_MISS_CHAR,
307 province VARCHAR2(2000):= FND_API.G_MISS_CHAR,
308 state VARCHAR2(2000):= FND_API.G_MISS_CHAR,
309 postal_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
313 house_number VARCHAR2(2000):= FND_API.G_MISS_CHAR,
310 country VARCHAR2(2000):= FND_API.G_MISS_CHAR,
311 address_lines_phonetic VARCHAR2(2000):= FND_API.G_MISS_CHAR,
312 po_box_number VARCHAR2(2000):= FND_API.G_MISS_CHAR,
314 street_suffix VARCHAR2(2000):= FND_API.G_MISS_CHAR,
315 street VARCHAR2(2000):= FND_API.G_MISS_CHAR,
316 street_number VARCHAR2(2000):= FND_API.G_MISS_CHAR,
317 floor VARCHAR2(2000):= FND_API.G_MISS_CHAR,
318 suite VARCHAR2(2000):= FND_API.G_MISS_CHAR,
319 postal_plus4_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
320 identifying_address_flag VARCHAR2(2000):= FND_API.G_MISS_CHAR,
321 ADDRESS_ATTRIBUTE1 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
322 ADDRESS_ATTRIBUTE2 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
323 ADDRESS_ATTRIBUTE3 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
324 ADDRESS_ATTRIBUTE4 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
325 ADDRESS_ATTRIBUTE5 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
326 ADDRESS_ATTRIBUTE6 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
327 ADDRESS_ATTRIBUTE7 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
328 ADDRESS_ATTRIBUTE8 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
329 ADDRESS_ATTRIBUTE9 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
330 ADDRESS_ATTRIBUTE10 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
331 ADDRESS_ATTRIBUTE11 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
332 ADDRESS_ATTRIBUTE12 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
333 ADDRESS_ATTRIBUTE13 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
334 ADDRESS_ATTRIBUTE14 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
335 ADDRESS_ATTRIBUTE15 VARCHAR2(2000):= FND_API.G_MISS_CHAR,
336 DESCRIPTION VARCHAR2(2000):= FND_API.G_MISS_CHAR,
337 ADDRESS_ATTRIBUTE_CATEGORY VARCHAR2(2000):= FND_API.G_MISS_CHAR,
338 PARTY_SITE_USE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
339 LOCATION_DIRECTIONS VARCHAR2(2000):= FND_API.G_MISS_CHAR,
340 SHORT_DESCRIPTION VARCHAR2(2000):= FND_API.G_MISS_CHAR,
341 ADDRESS_EFFECTIVE_DATE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
342 ADDRESS_EXPIRATION_DATE VARCHAR2(2000):= FND_API.G_MISS_CHAR,
343 cp_imp_xml_element_id NUMBER := FND_API.G_MISS_NUM,
344 phone_country_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
345 phone_area_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
346 phone_number VARCHAR2(2000):= FND_API.G_MISS_CHAR,
347 phone_extension VARCHAR2(2000):= FND_API.G_MISS_CHAR,
348 em_imp_xml_element_id NUMBER := FND_API.G_MISS_NUM,
349 email_address VARCHAR2(2000):= FND_API.G_MISS_CHAR,
350 fx_imp_xml_element_id NUMBER := FND_API.G_MISS_NUM,
351 fax_country_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
352 fax_area_code VARCHAR2(2000):= FND_API.G_MISS_CHAR,
353 fax_number VARCHAR2(2000):= FND_API.G_MISS_CHAR);
354
355
356
357 TYPE cust_data_in_tbl IS TABLE OF data_in_rec_type
358 INDEX BY BINARY_INTEGER;
359
360
361 TYPE cust_b2c_data_in_tbl IS TABLE OF data_in_rec_b2c_type
362 INDEX BY BINARY_INTEGER;
363
364
365 -- This program loads the customer data from OM to TCA.
366
367 PROCEDURE list_import_to_hz (
368 Errbuf OUT NOCOPY VARCHAR2,
369 Retcode OUT NOCOPY VARCHAR2,
370 p_import_list_header_id NUMBER,
371 p_number_of_processes NUMBER DEFAULT 1
372 );
373
374
375 -- This program loads the data to OM table from the flat file
376
377 PROCEDURE load_to_ams(
378 Errbuf OUT NOCOPY VARCHAR2,
379 Retcode OUT NOCOPY VARCHAR2,
380 p_import_list_header_id IN NUMBER, -- To be used as part of executable name.
381 p_control_file IN VARCHAR2, -- Name of file to be used by the SQL*Loader process.
382 p_staged_only IN VARCHAR2 default 'N', -- Used for staged table import.
383 p_owner_user_id IN NUMBER, -- Used for list generation (resource_id)
384 p_generate_list IN VARCHAR2 default 'N',
385 p_list_name IN VARCHAR2 -- For list generation name.
386 );
387
388
389 -- This progam invokes the required concurrent program based on the
390 -- import type.
391
392 PROCEDURE list_loader (
393 p_import_list_header_id NUMBER,
394 x_request_id OUT NOCOPY NUMBER
395 );
396
397
398 -- This is the main program which starts the import process
399
400 PROCEDURE Import_process (
401 p_import_list_header_id IN NUMBER,
402 p_start_time IN DATE,
403 p_control_file IN VARCHAR2, -- Name of file to be used by the SQL*Loader process.
404 p_staged_only IN VARCHAR2 default 'N', -- Used for staged table import.
405 p_owner_user_id IN NUMBER, -- Used for list generation (resource_id)
409 );
406 p_generate_list IN VARCHAR2 default 'N', -- Used for staged table import.
407 p_list_name IN VARCHAR2, -- For list generation name.
408 x_request_id OUT NOCOPY NUMBER -- Used for concurrent program monitoring.
410
411 -- This progam is for client side loading.
412
413 PROCEDURE client_load(
414 p_import_list_header_id IN NUMBER,
415 p_owner_user_id IN NUMBER,
416 p_generate_list IN VARCHAR2 default 'N', -- Used for staged table import.
417 p_list_name IN VARCHAR2 -- For list generation name.
418 );
419
420
421 -- This progam checkes the de-duplication rules
422 --
423 PROCEDURE dedup_check(
424 p_import_list_header_id NUMBER
425 );
426
427
428 -- This program executes the OSO leads concurrent program.
429
430 PROCEDURE execute_lead_import (
431 -- Errbuf OUT NOCOPY VARCHAR2,
432 -- Retcode OUT NOCOPY VARCHAR2,
433 p_import_list_header_id NUMBER
434 );
435
436 -- This progam updates the party for the rented list
437 --
438 PROCEDURE update_rented_list_party (
439 p_party_id IN NUMBER,
440 p_return_status OUT NOCOPY VARCHAR2,
441 p_msg_count OUT NOCOPY NUMBER,
442 p_msg_data OUT NOCOPY VARCHAR2
443 );
444
445
446 -- This program performs error checks in ams_import_interface table.
447
448 PROCEDURE execute_lead_data_validation (
449 p_import_list_header_id NUMBER,
450 p_return_status OUT NOCOPY VARCHAR2
451 );
452
453 -- This program performs error checks in customer data.
454
455 PROCEDURE execute_cust_data_validation (
456 p_import_list_header_id NUMBER,
457 p_return_status OUT NOCOPY VARCHAR2
458 );
459
460 -- This program performs error checks in xml customer data.
461
462 PROCEDURE exe_custxml_data_validation (
463 p_import_list_header_id NUMBER,
464 p_return_status OUT NOCOPY VARCHAR2
465 );
466
467
468 -- This program performs error checks error for product import.
469
470 PROCEDURE execute_event_data_validation (
471 p_import_list_header_id NUMBER,
472 p_return_status OUT NOCOPY VARCHAR2
473 );
474
475
476 --
477 -- This procedure is used for existence checking for address.
478 --
479 --
480 PROCEDURE address_echeck(
481 p_party_id IN NUMBER,
482 x_return_status OUT NOCOPY VARCHAR2,
483 x_msg_count OUT NOCOPY NUMBER,
484 x_msg_data OUT NOCOPY VARCHAR2,
485 p_location_id IN OUT NOCOPY NUMBER,
486 p_address1 IN VARCHAR2,
487 p_city IN VARCHAR2,
488 p_pcode IN VARCHAR2,
489 p_country IN VARCHAR2
490 );
491
492 --
493 -- This procedure is used to create Location.
494 --
495
496 procedure create_location (
497 p_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
498 x_return_status OUT NOCOPY VARCHAR2,
499 x_msg_count OUT NOCOPY NUMBER,
500 x_msg_data OUT NOCOPY VARCHAR2,
501 x_location_id OUT NOCOPY NUMBER
502 );
503
504
505 --
506 -- This procedure is used to create party site
507 --
508
509 procedure create_party_site (
510 p_psite_rec IN hz_party_site_v2pub.party_site_rec_type,
511 x_return_status OUT NOCOPY VARCHAR2,
512 x_msg_count OUT NOCOPY NUMBER,
513 x_msg_data OUT NOCOPY VARCHAR2,
514 x_party_site_id OUT NOCOPY NUMBER,
515 x_party_site_number OUT NOCOPY VARCHAR2
516 );
517
518
519 --
520 -- This procedure is used to create party site use
521 --
522
523 procedure create_party_site_use (
524 p_psiteuse_rec IN hz_party_site_v2pub.party_site_use_rec_type,
525 x_return_status OUT NOCOPY VARCHAR2,
526 x_msg_count OUT NOCOPY NUMBER,
527 x_msg_data OUT NOCOPY VARCHAR2,
528 x_party_site_use_id OUT NOCOPY NUMBER
529 );
530
531 --
532 -- This procedure is used to create contact points
533 --
534 procedure create_contact_point(
535 p_cpoint_rec IN hz_contact_point_v2pub.contact_point_rec_type,
536 p_edi_rec IN hz_contact_point_v2pub.edi_rec_type,
537 p_email_rec IN hz_contact_point_v2pub.email_rec_type,
538 p_phone_rec IN hz_contact_point_v2pub.phone_rec_type,
539 p_telex_rec IN hz_contact_point_v2pub.telex_rec_type,
540 p_web_rec IN hz_contact_point_v2pub.web_rec_type,
541 x_return_status OUT NOCOPY VARCHAR2,
542 x_msg_count OUT NOCOPY NUMBER,
543 x_msg_data OUT NOCOPY VARCHAR2,
544 x_contact_point_id OUT NOCOPY NUMBER
545 );
546
547
548 -- This progam is for client side loading.
549
550 PROCEDURE client_load_direct (
551 p_import_list_header_id IN NUMBER,
552 p_owner_user_id IN NUMBER,
553 p_generate_list IN VARCHAR2 default 'N', -- Used for staged table import.
554 p_list_name IN VARCHAR2 -- For list generation name.
555 );
556
557
558 -- This progam is for client side loading.
559
560 PROCEDURE client_load_cm (
561 Errbuf OUT NOCOPY VARCHAR2,
562 Retcode OUT NOCOPY VARCHAR2,
563 p_import_list_header_id IN NUMBER,
564 p_owner_user_id IN NUMBER,
565 p_generate_list IN VARCHAR2 default 'N', -- Used for staged table import.
566 p_list_name IN VARCHAR2 -- For list generation name.
567 );
568
569
570
571 -- This program is for customer import for XML data.
572
573 PROCEDURE Process_customers_xml (
574 p_api_version_number IN NUMBER,
575 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
576 p_commit IN VARCHAR2 := FND_API.G_FALSE,
577 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
578 x_return_status OUT NOCOPY VARCHAR2,
579 x_msg_count OUT NOCOPY NUMBER,
580 x_msg_data OUT NOCOPY VARCHAR2,
581 p_import_list_header_id IN NUMBER,
582 p_update_flag IN VARCHAR2 DEFAULT NULL
583 );
584
585
586 --
587 -- for XML updates the error in the element table
588 --
589 PROCEDURE update_element_error (
590 p_import_list_header_id IN NUMBER,
591 p_xml_element_id IN NUMBER,
592 p_colName IN varchar2,
593 p_error_text IN varchar2);
594
595
596 -- This Program performs validation of the RELATIONSHIP_TYPE and RELATIONSHIP_CODE
597 PROCEDURE execute_reltnship_validation (
598 p_import_list_header_id NUMBER,
599 p_return_status OUT NOCOPY VARCHAR2
600 );
601
602 -- This Program will raise pre business event from list import
603 PROCEDURE Raise_Business_event(
604 p_import_list_header_id NUMBER,
605 p_event VARCHAR2
606 );
607 FUNCTION TEST_Pre_sub
608 ( p_subscription_guid In RAW
609 , p_event IN OUT NOCOPY WF_EVENT_T
610 )
611 RETURN VARCHAR2;
612
613
614 FUNCTION TEST_Post_sub
615 ( p_subscription_guid In RAW
616 , p_event IN OUT NOCOPY WF_EVENT_T
617 )
618 RETURN VARCHAR2;
619
620 end AMS_ListImport_PVT;