1 PACKAGE HZ_IMP_LOAD_CPT_PKG AS
2 /* $Header: ARHLCPTS.pls 120.7 2005/10/30 04:20:19 appldev noship $ */
3
4 TYPE BATCH_ID IS TABLE OF HZ_IMP_CONTACTPTS_INT.BATCH_ID%TYPE;
5 TYPE ORIG_SYSTEM IS TABLE OF HZ_IMP_CONTACTPTS_INT.CP_ORIG_SYSTEM%TYPE;
6 TYPE ORIG_SYSTEM_REFERENCE IS TABLE OF HZ_IMP_CONTACTPTS_INT.CP_ORIG_SYSTEM_REFERENCE%TYPE;
7 TYPE INSERT_UPDATE_FLAG IS TABLE OF HZ_IMP_CONTACTPTS_INT.INSERT_UPDATE_FLAG%TYPE;
8 TYPE CONTACT_POINT_TYPE IS TABLE OF HZ_IMP_CONTACTPTS_INT.CONTACT_POINT_TYPE%TYPE;
9 TYPE CONTACT_POINT_PURPOSE IS TABLE OF HZ_IMP_CONTACTPTS_INT.CONTACT_POINT_PURPOSE%TYPE;
10 TYPE EDI_ECE_TP_LOCATION_CODE IS TABLE OF HZ_IMP_CONTACTPTS_INT.EDI_ECE_TP_LOCATION_CODE%TYPE;
11 TYPE EDI_ID_NUMBER IS TABLE OF HZ_IMP_CONTACTPTS_INT.EDI_ID_NUMBER%TYPE;
12 TYPE EDI_PAYMENT_FORMAT IS TABLE OF HZ_IMP_CONTACTPTS_INT.EDI_PAYMENT_FORMAT%TYPE;
13 TYPE EDI_PAYMENT_METHOD IS TABLE OF HZ_IMP_CONTACTPTS_INT.EDI_PAYMENT_METHOD%TYPE;
14 TYPE EDI_REMITTANCE_INSTRUCTION IS TABLE OF HZ_IMP_CONTACTPTS_INT.EDI_REMITTANCE_INSTRUCTION%TYPE;
15 TYPE EDI_REMITTANCE_METHOD IS TABLE OF HZ_IMP_CONTACTPTS_INT.EDI_REMITTANCE_METHOD%TYPE;
16 TYPE EDI_TP_HEADER_ID IS TABLE OF HZ_IMP_CONTACTPTS_INT.EDI_TP_HEADER_ID%TYPE;
17 TYPE EDI_TRANSACTION_HANDLING IS TABLE OF HZ_IMP_CONTACTPTS_INT.EDI_TRANSACTION_HANDLING%TYPE;
18 TYPE EFT_PRINTING_PROGRAM_ID IS TABLE OF HZ_IMP_CONTACTPTS_INT.EFT_PRINTING_PROGRAM_ID%TYPE;
19 TYPE EFT_SWIFT_CODE IS TABLE OF HZ_IMP_CONTACTPTS_INT.EFT_SWIFT_CODE%TYPE;
20 TYPE EFT_TRANSMISSION_PROGRAM_ID IS TABLE OF HZ_IMP_CONTACTPTS_INT.EFT_TRANSMISSION_PROGRAM_ID%TYPE;
21 TYPE EFT_USER_NUMBER IS TABLE OF HZ_IMP_CONTACTPTS_INT.EFT_USER_NUMBER%TYPE;
22 TYPE EMAIL_ADDRESS IS TABLE OF HZ_IMP_CONTACTPTS_INT.EMAIL_ADDRESS%TYPE;
23 TYPE EMAIL_FORMAT IS TABLE OF HZ_IMP_CONTACTPTS_INT.EMAIL_FORMAT%TYPE;
24 TYPE PHONE_AREA_CODE IS TABLE OF HZ_IMP_CONTACTPTS_INT.PHONE_AREA_CODE%TYPE;
25 TYPE PHONE_COUNTRY_CODE IS TABLE OF HZ_IMP_CONTACTPTS_INT.PHONE_COUNTRY_CODE%TYPE;
26 TYPE PHONE_EXTENSION IS TABLE OF HZ_IMP_CONTACTPTS_INT.PHONE_EXTENSION%TYPE;
27 TYPE PHONE_LINE_TYPE IS TABLE OF HZ_IMP_CONTACTPTS_INT.PHONE_LINE_TYPE%TYPE;
28 TYPE PHONE_NUMBER IS TABLE OF HZ_IMP_CONTACTPTS_INT.PHONE_NUMBER%TYPE;
29 TYPE RAW_PHONE_NUMBER IS TABLE OF HZ_IMP_CONTACTPTS_INT.RAW_PHONE_NUMBER%TYPE;
30 TYPE PHONE_CALLING_CALENDAR IS TABLE OF HZ_IMP_CONTACTPTS_INT.PHONE_CALLING_CALENDAR%TYPE;
31 TYPE TELEX_NUMBER IS TABLE OF HZ_IMP_CONTACTPTS_INT.TELEX_NUMBER%TYPE;
32 TYPE TIMEZONE_ID IS TABLE OF fnd_timezones_b.UPGRADE_TZ_ID%TYPE;
33 TYPE TIMEZONE_CODE IS TABLE OF HZ_IMP_CONTACTPTS_INT.TIMEZONE_CODE%TYPE;
34 TYPE URL IS TABLE OF HZ_IMP_CONTACTPTS_INT.URL%TYPE;
35 TYPE WEB_TYPE IS TABLE OF HZ_IMP_CONTACTPTS_INT.WEB_TYPE%TYPE;
36 TYPE ATTRIBUTE_CATEGORY IS TABLE OF HZ_IMP_CONTACTPTS_INT.ATTRIBUTE_CATEGORY%TYPE;
37 TYPE ATTRIBUTE IS TABLE OF HZ_IMP_CONTACTPTS_INT.ATTRIBUTE1%TYPE;
38 TYPE INTERFACE_STATUS IS TABLE OF HZ_IMP_CONTACTPTS_INT.INTERFACE_STATUS%TYPE;
39 TYPE ACTION_FLAG IS TABLE OF HZ_IMP_CONTACTPTS_SG.ACTION_FLAG%TYPE;
40 TYPE ERROR_ID IS TABLE OF HZ_IMP_CONTACTPTS_INT.ERROR_ID%TYPE;
41 TYPE DQM_ACTION_FLAG IS TABLE OF HZ_IMP_CONTACTPTS_INT.DQM_ACTION_FLAG%TYPE;
42 TYPE DUP_WITHIN_INT_FLAG IS TABLE OF HZ_IMP_CONTACTPTS_INT.DUP_WITHIN_INT_FLAG%TYPE;
43 TYPE PARTY_ID IS TABLE OF HZ_IMP_CONTACTPTS_SG.PARTY_ID%TYPE;
44 TYPE PARTY_SITE_ID IS TABLE OF HZ_IMP_CONTACTPTS_SG.PARTY_SITE_ID%TYPE;
45 -- TYPE STAGE_CP_ID IS TABLE OF HZ_IMP_CONTACTPTS_INT.STAGE_CP_ID%TYPE;
46 TYPE CREATED_BY_MODULE IS TABLE OF HZ_IMP_CONTACTPTS_INT.CREATED_BY_MODULE%TYPE;
47 TYPE OWNER_TABLE_NAME IS TABLE OF HZ_CONTACT_POINTS.OWNER_TABLE_NAME%TYPE;
48 TYPE OWNER_TABLE_ID IS TABLE OF HZ_CONTACT_POINTS.OWNER_TABLE_ID%TYPE;
49 TYPE CONTACT_POINT_ID IS TABLE OF HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE;
50
51 TYPE ERROR_MESSAGE_NAME IS TABLE OF HZ_IMP_ERRORS.MESSAGE_NAME%TYPE;
52 TYPE ERROR_MESSAGE_TOKEN IS TABLE OF HZ_IMP_ERRORS.TOKEN1_NAME%TYPE;
53 TYPE ERROR_MESSAGE_VALUE IS TABLE OF HZ_IMP_ERRORS.TOKEN1_VALUE%TYPE;
54 TYPE ROWID IS TABLE OF VARCHAR2(50); --UROWID;
55 TYPE TYPE_COLUMN IS TABLE OF VARCHAR2(30);
56 TYPE IND_COLUMN IS TABLE OF VARCHAR2(30);
57 TYPE YEAR_COLUMN IS TABLE OF NUMBER(4);
58 TYPE NUMBER_COLUMN IS TABLE OF NUMBER;
59 TYPE FLAG_COLUMN IS TABLE OF VARCHAR2(1);
60 TYPE DATE_COLUMN IS TABLE OF DATE;
61
62 TYPE RefCurType IS REF CURSOR;
63
64
65 /* Validation error columns */
66 TYPE error_ids IS TABLE OF hz_imp_errors.error_id%TYPE;
67 TYPE LOOKUP_ERROR IS TABLE OF ar_lookups.lookup_code%TYPE;
68 TYPE FLAG_ERROR IS TABLE OF VARCHAR2(1);
69
70 /* Data load G_MISS values */
71 G_MISS_NUM CONSTANT NUMBER := -9999;
72 G_MISS_CHAR CONSTANT VARCHAR2(1) := '!';
73 G_MISS_DATE CONSTANT DATE := to_date('01/01/4000', 'DD/MM/YYYY');
74
75
76 PROCEDURE load_contactpoints
77 (
78 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
79 ,x_return_status OUT NOCOPY VARCHAR2
80 ,x_msg_count OUT NOCOPY NUMBER
81 ,x_msg_data OUT NOCOPY VARCHAR2 )
82 ;
83
84 FUNCTION validate_desc_flexfield_f(
85 p_attr_category IN VARCHAR2,
86 p_attr1 IN VARCHAR2,
87 p_attr2 IN VARCHAR2,
88 p_attr3 IN VARCHAR2,
89 p_attr4 IN VARCHAR2,
90 p_attr5 IN VARCHAR2,
91 p_attr6 IN VARCHAR2,
92 p_attr7 IN VARCHAR2,
93 p_attr8 IN VARCHAR2,
94 p_attr9 IN VARCHAR2,
95 p_attr10 IN VARCHAR2,
96 p_attr11 IN VARCHAR2,
97 p_attr12 IN VARCHAR2,
98 p_attr13 IN VARCHAR2,
99 p_attr14 IN VARCHAR2,
100 p_attr15 IN VARCHAR2,
101 p_attr16 IN VARCHAR2,
102 p_attr17 IN VARCHAR2,
103 p_attr18 IN VARCHAR2,
104 p_attr19 IN VARCHAR2,
105 p_attr20 IN VARCHAR2,
106 p_validation_date IN DATE
107 ) RETURN VARCHAR2;
108
109 END HZ_IMP_LOAD_CPT_PKG;