1 PACKAGE HZ_IMP_LOAD_ORG_CONTACT_PKG AUTHID CURRENT_USER AS
2 /*$Header: ARHLORCS.pls 120.9 2008/10/23 11:17:02 idali ship $*/
3
4 /* HZ_IMP_CONTACTS_INT columns */
5
6 TYPE batch_id IS TABLE OF HZ_IMP_CONTACTS_INT.BATCH_ID%TYPE;
7 TYPE contact_orig_system IS TABLE OF HZ_IMP_CONTACTS_INT.CONTACT_ORIG_SYSTEM%TYPE;
8 TYPE contact_orig_system_reference IS TABLE OF HZ_IMP_CONTACTS_INT.CONTACT_ORIG_SYSTEM_REFERENCE%TYPE;
9 TYPE sub_orig_system IS TABLE OF HZ_IMP_CONTACTS_INT.SUB_ORIG_SYSTEM%TYPE;
10 TYPE sub_orig_system_reference IS TABLE OF HZ_IMP_CONTACTS_INT.SUB_ORIG_SYSTEM_REFERENCE%TYPE;
11 TYPE obj_orig_system IS TABLE OF HZ_IMP_CONTACTS_INT.OBJ_ORIG_SYSTEM%TYPE;
12 TYPE obj_orig_system_reference IS TABLE OF HZ_IMP_CONTACTS_INT.OBJ_ORIG_SYSTEM_REFERENCE%TYPE;
13 TYPE insert_update_flag IS TABLE OF HZ_IMP_CONTACTS_INT.INSERT_UPDATE_FLAG%TYPE;
14 TYPE contact_number IS TABLE OF HZ_IMP_CONTACTS_INT.CONTACT_NUMBER%TYPE;
15 TYPE department_code IS TABLE OF HZ_IMP_CONTACTS_INT.DEPARTMENT_CODE%TYPE;
16 TYPE department IS TABLE OF HZ_IMP_CONTACTS_INT.DEPARTMENT%TYPE;
17 TYPE title IS TABLE OF HZ_IMP_CONTACTS_INT.TITLE%TYPE;
18 TYPE job_title IS TABLE OF HZ_IMP_CONTACTS_INT.JOB_TITLE%TYPE;
19 TYPE job_title_code IS TABLE OF HZ_IMP_CONTACTS_INT.JOB_TITLE_CODE%TYPE;
20 TYPE decision_maker_flag IS TABLE OF HZ_IMP_CONTACTS_INT.DECISION_MAKER_FLAG%TYPE;
21 TYPE reference_use_flag IS TABLE OF HZ_IMP_CONTACTS_INT.REFERENCE_USE_FLAG%TYPE;
22 TYPE comments IS TABLE OF HZ_IMP_CONTACTS_INT.COMMENTS%TYPE;
23 TYPE relationship_type IS TABLE OF HZ_IMP_CONTACTS_INT.RELATIONSHIP_TYPE%TYPE;
24 TYPE relationship_code IS TABLE OF HZ_IMP_CONTACTS_INT.RELATIONSHIP_CODE%TYPE;
25 TYPE start_date IS TABLE OF HZ_IMP_CONTACTS_INT.START_DATE%TYPE;
26 TYPE end_date IS TABLE OF HZ_IMP_CONTACTS_INT.END_DATE%TYPE;
27 TYPE rel_comments IS TABLE OF HZ_IMP_CONTACTS_INT.REL_COMMENTS%TYPE;
28 TYPE attribute_category IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE_CATEGORY%TYPE;
29 TYPE attribute IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE1%TYPE;
30 TYPE attribute2 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE2%TYPE;
31 TYPE attribute3 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE3%TYPE;
32 TYPE attribute4 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE4%TYPE;
33 TYPE attribute5 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE5%TYPE;
34 TYPE attribute6 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE6%TYPE;
35 TYPE attribute7 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE7%TYPE;
36 TYPE attribute8 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE8%TYPE;
37 TYPE attribute9 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE9%TYPE;
38 TYPE attribute10 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE10%TYPE;
39 TYPE attribute11 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE11%TYPE;
40 TYPE attribute12 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE12%TYPE;
41 TYPE attribute13 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE13%TYPE;
42 TYPE attribute14 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE14%TYPE;
43 TYPE attribute15 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE15%TYPE;
44 TYPE attribute16 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE16%TYPE;
45 TYPE attribute17 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE17%TYPE;
46 TYPE attribute18 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE18%TYPE;
47 TYPE attribute19 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE19%TYPE;
48 TYPE attribute20 IS TABLE OF HZ_IMP_CONTACTS_INT.ATTRIBUTE20%TYPE;
49 TYPE interface_status IS TABLE OF HZ_IMP_CONTACTS_INT.INTERFACE_STATUS%TYPE;
50 TYPE error_id IS TABLE OF HZ_IMP_CONTACTS_INT.ERROR_ID%TYPE;
51 TYPE created_by_module IS TABLE OF HZ_IMP_CONTACTS_INT.CREATED_BY_MODULE%TYPE;
52
53 TYPE party_id IS TABLE OF HZ_PARTIES.PARTY_ID%TYPE;
54 TYPE party_name IS TABLE OF HZ_PARTIES.PARTY_NAME%TYPE;
55 TYPE party_type IS TABLE OF HZ_PARTIES.PARTY_TYPE%TYPE;
56 TYPE party_number IS TABLE OF HZ_PARTIES.PARTY_NUMBER%TYPE;
57
58 TYPE ORG_CONTACT_ID IS TABLE OF HZ_ORG_CONTACTS.ORG_CONTACT_ID%TYPE;
59 TYPE DIRECTION_CODE IS TABLE OF HZ_RELATIONSHIPS.DIRECTION_CODE%TYPE;
60
61 TYPE ROWID IS TABLE OF VARCHAR2(50); --UROWID;
62 TYPE TYPE_COLUMN IS TABLE OF VARCHAR2(30);
63 TYPE IND_COLUMN IS TABLE OF VARCHAR2(30);
64 TYPE YEAR_COLUMN IS TABLE OF NUMBER(4);
65 TYPE NUMBER_COLUMN IS TABLE OF NUMBER;
66 TYPE FLAG_COLUMN IS TABLE OF VARCHAR2(1);
67 TYPE DATE_COLUMN IS TABLE OF DATE;
68
69
70 TYPE ERROR_MESSAGE_NAME IS TABLE OF HZ_IMP_ERRORS.MESSAGE_NAME%TYPE;
71 TYPE ERROR_MESSAGE_TOKEN IS TABLE OF HZ_IMP_ERRORS.TOKEN1_VALUE%TYPE;
72 TYPE ERROR_MESSAGE_TOKEN_NAME IS TABLE OF HZ_IMP_ERRORS.TOKEN1_NAME%TYPE;
73
74 TYPE RefCurType IS REF CURSOR;
75
76
77 /* Validation error columns */
78 TYPE ERROR_IDS IS TABLE OF hz_imp_errors.error_id%TYPE;
79 TYPE LOOKUP_ERROR IS TABLE OF ar_lookups.lookup_code%TYPE;
80 TYPE FLAG_ERROR IS TABLE OF VARCHAR2(1);
81
82 /* Data load G_MISS values */
83 --G_MISS_NUM CONSTANT NUMBER := -9999;
84 --G_MISS_CHAR CONSTANT VARCHAR2(1) := '!';
85 --G_MISS_DATE CONSTANT DATE := to_date('01/01/4000', 'DD/MM/YYYY');
86
87
88 PROCEDURE load_org_contacts (
89 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
90 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
91 ,X_MSG_COUNT OUT NOCOPY NUMBER
92 ,X_MSG_DATA OUT NOCOPY VARCHAR2 );
93
94 FUNCTION validate_desc_flexfield_f(
95 p_attr_category IN VARCHAR2,
96 p_attr1 IN VARCHAR2,
97 p_attr2 IN VARCHAR2,
98 p_attr3 IN VARCHAR2,
99 p_attr4 IN VARCHAR2,
100 p_attr5 IN VARCHAR2,
101 p_attr6 IN VARCHAR2,
102 p_attr7 IN VARCHAR2,
103 p_attr8 IN VARCHAR2,
104 p_attr9 IN VARCHAR2,
105 p_attr10 IN VARCHAR2,
106 p_attr11 IN VARCHAR2,
107 p_attr12 IN VARCHAR2,
108 p_attr13 IN VARCHAR2,
109 p_attr14 IN VARCHAR2,
110 p_attr15 IN VARCHAR2,
111 p_attr16 IN VARCHAR2,
112 p_attr17 IN VARCHAR2,
113 p_attr18 IN VARCHAR2,
114 p_attr19 IN VARCHAR2,
115 p_attr20 IN VARCHAR2,
116 p_validation_date IN DATE
117 ) RETURN VARCHAR2 ;
118
119
120 END HZ_IMP_LOAD_ORG_CONTACT_PKG;