1 PACKAGE HZ_IMP_LOAD_PARTIES_PKG AUTHID CURRENT_USER AS
2 /*$Header: ARHLPTYS.pls 120.6 2005/10/30 03:53:10 appldev noship $*/
3
4 /* HZ_IMP_PARTIES_INT columns */
5 TYPE BATCH_ID IS TABLE OF HZ_IMP_PARTIES_INT.BATCH_ID%TYPE;
6 TYPE PARTY_ORIG_SYSTEM IS TABLE OF HZ_IMP_PARTIES_INT.PARTY_ORIG_SYSTEM%TYPE;
7 TYPE PARTY_ORIG_SYSTEM_REFERENCE IS TABLE OF HZ_IMP_PARTIES_INT.PARTY_ORIG_SYSTEM_REFERENCE%TYPE;
8 TYPE INSERT_UPDATE_FLAG IS TABLE OF HZ_IMP_PARTIES_INT.INSERT_UPDATE_FLAG%TYPE;
9 TYPE PARTY_ID IS TABLE OF HZ_IMP_PARTIES_INT.PARTY_ID%TYPE;
10 TYPE PARTY_NAME IS TABLE OF HZ_PARTIES.PARTY_NAME%TYPE;
11 TYPE PERSON_NAME IS TABLE OF HZ_PERSON_PROFILES.PERSON_NAME%TYPE;
12 TYPE PARTY_NUMBER IS TABLE OF HZ_IMP_PARTIES_INT.PARTY_NUMBER%TYPE;
13 TYPE SALUTATION IS TABLE OF HZ_IMP_PARTIES_INT.SALUTATION%TYPE;
14 TYPE ATTRIBUTE_CATEGORY IS TABLE OF HZ_IMP_PARTIES_INT.ATTRIBUTE_CATEGORY%TYPE;
15 TYPE ATTRIBUTE IS TABLE OF HZ_IMP_PARTIES_INT.ATTRIBUTE1%TYPE;
16 TYPE ORGANIZATION_NAME IS TABLE OF HZ_IMP_PARTIES_INT.ORGANIZATION_NAME%TYPE;
17 TYPE ORGANIZATION_NAME_PHONETIC IS TABLE OF HZ_IMP_PARTIES_INT.ORGANIZATION_NAME_PHONETIC%TYPE;
18 TYPE ANALYSIS_FY IS TABLE OF HZ_IMP_PARTIES_INT.ANALYSIS_FY%TYPE;
19 TYPE BRANCH_FLAG IS TABLE OF HZ_IMP_PARTIES_INT.BRANCH_FLAG%TYPE;
20 TYPE BUSINESS_SCOPE IS TABLE OF HZ_IMP_PARTIES_INT.BUSINESS_SCOPE%TYPE;
21 TYPE CEO IS TABLE OF HZ_IMP_PARTIES_INT.CEO_NAME%TYPE;
22 TYPE CONG_DIST_CODE IS TABLE OF HZ_IMP_PARTIES_INT.CONG_DIST_CODE%TYPE;
23 TYPE CORPORATION_CLASS IS TABLE OF HZ_IMP_PARTIES_INT.CORPORATION_CLASS%TYPE;
24 TYPE PREF_FUNCTIONAL_CURRENCY IS TABLE OF HZ_IMP_PARTIES_INT.PREF_FUNCTIONAL_CURRENCY%TYPE;
25 TYPE DO_NOT_CONFUSE_WITH IS TABLE OF HZ_IMP_PARTIES_INT.DO_NOT_CONFUSE_WITH%TYPE;
26 TYPE DUNS_NUMBER_C IS TABLE OF HZ_IMP_PARTIES_INT.DUNS_NUMBER_C%TYPE;
27 TYPE EMP_AT_PRIMARY_ADR IS TABLE OF HZ_IMP_PARTIES_INT.EMP_AT_PRIMARY_ADR%TYPE;
28 TYPE EMP_AT_PRIMARY_ADR_TEXT IS TABLE OF HZ_IMP_PARTIES_INT.EMP_AT_PRIMARY_ADR_TEXT%TYPE;
29 TYPE DISPLAYED_DUNS IS TABLE OF HZ_IMP_PARTIES_INT.DISPLAYED_DUNS%TYPE;
30 TYPE FISCAL_YEAREND_MONTH IS TABLE OF HZ_IMP_PARTIES_INT.FISCAL_YEAREND_MONTH%TYPE;
31 TYPE JGZZ_FISCAL_CODE IS TABLE OF HZ_IMP_PARTIES_INT.JGZZ_FISCAL_CODE%TYPE;
32 TYPE TAX_REFERENCE IS TABLE OF HZ_IMP_PARTIES_INT.TAX_REFERENCE%TYPE;
33 TYPE KNOWN_AS IS TABLE OF HZ_IMP_PARTIES_INT.KNOWN_AS%TYPE;
34 TYPE LEGAL_STATUS IS TABLE OF HZ_IMP_PARTIES_INT.LEGAL_STATUS%TYPE;
35 TYPE LINE_OF_BUSINESS IS TABLE OF HZ_IMP_PARTIES_INT.LINE_OF_BUSINESS%TYPE;
36 TYPE LOCAL_BUS_IDENTIFIER IS TABLE OF HZ_IMP_PARTIES_INT.LOCAL_BUS_IDENTIFIER%TYPE;
37 TYPE MISSION_STATEMENT IS TABLE OF HZ_IMP_PARTIES_INT.MISSION_STATEMENT%TYPE;
38 TYPE PRINCIPAL_NAME IS TABLE OF HZ_IMP_PARTIES_INT.PRINCIPAL_NAME%TYPE;
39 TYPE PRINCIPAL_TITLE IS TABLE OF HZ_IMP_PARTIES_INT.PRINCIPAL_TITLE%TYPE;
40 TYPE TOTAL_EMPLOYEES_TEXT IS TABLE OF HZ_IMP_PARTIES_INT.TOTAL_EMPLOYEES_TEXT%TYPE;
41 TYPE PERSON_FIRST_NAME IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_FIRST_NAME%TYPE;
42 TYPE PERSON_LAST_NAME IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_LAST_NAME%TYPE;
43 TYPE PERSON_MIDDLE_NAME IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_MIDDLE_NAME%TYPE;
44 TYPE PERSON_INITIALS IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_INITIALS%TYPE;
45 TYPE PERSON_NAME_SUFFIX IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_NAME_SUFFIX%TYPE;
46 TYPE PERSON_PRE_NAME_ADJUNCT IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_PRE_NAME_ADJUNCT%TYPE;
47 TYPE PERSON_PREVIOUS_LAST_NAME IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_PREVIOUS_LAST_NAME%TYPE;
48 TYPE PERSON_TITLE IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_TITLE%TYPE;
49 TYPE PERSON_FIRST_NAME_PHONETIC IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_FIRST_NAME_PHONETIC%TYPE;
50 TYPE PERSON_NAME_PHONETIC IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_NAME_PHONETIC%TYPE;
51 TYPE PERSON_ACADEMIC_TITLE IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_ACADEMIC_TITLE%TYPE;
52 TYPE PLACE_OF_BIRTH IS TABLE OF HZ_IMP_PARTIES_INT.PLACE_OF_BIRTH%TYPE;
53 TYPE DECLARED_ETHNICITY IS TABLE OF HZ_IMP_PARTIES_INT.DECLARED_ETHNICITY%TYPE;
54 TYPE GENDER IS TABLE OF HZ_IMP_PARTIES_INT.GENDER%TYPE;
55 TYPE MARITAL_STATUS IS TABLE OF HZ_IMP_PARTIES_INT.MARITAL_STATUS%TYPE;
56 TYPE PERSON_IDENTIFIER IS TABLE OF HZ_IMP_PARTIES_INT.PERSON_IDENTIFIER%TYPE;
57 TYPE INTERFACE_STATUS IS TABLE OF HZ_IMP_PARTIES_INT.INTERFACE_STATUS%TYPE;
58 TYPE ERROR_ID IS TABLE OF HZ_IMP_PARTIES_INT.ERROR_ID%TYPE;
59 TYPE OLD_OSR IS TABLE OF HZ_IMP_PARTIES_SG.OLD_ORIG_SYSTEM_REFERENCE%TYPE;
60 TYPE CREATED_BY_MODULE IS TABLE OF HZ_IMP_PARTIES_INT.CREATED_BY_MODULE%TYPE;
61 TYPE ERROR_MESSAGE_NAME IS TABLE OF HZ_IMP_ERRORS.MESSAGE_NAME%TYPE;
62 TYPE ERROR_MESSAGE_TOKEN IS TABLE OF HZ_IMP_ERRORS.TOKEN1_VALUE%TYPE;
63 TYPE ERROR_MESSAGE_TOKEN_NAME IS TABLE OF HZ_IMP_ERRORS.TOKEN1_NAME%TYPE;
64 TYPE ROWID IS TABLE OF VARCHAR2(50); --UROWID;
65 TYPE TYPE_COLUMN IS TABLE OF VARCHAR2(30);
66 TYPE IND_COLUMN IS TABLE OF VARCHAR2(30);
67 TYPE YEAR_COLUMN IS TABLE OF NUMBER(4);
68 TYPE NUMBER_COLUMN IS TABLE OF NUMBER;
69 TYPE FLAG_COLUMN IS TABLE OF VARCHAR2(1);
70 TYPE DATE_COLUMN IS TABLE OF DATE;
71 TYPE ORG_PROFILE_ID IS TABLE OF HZ_ORGANIZATION_PROFILES.ORGANIZATION_PROFILE_ID%TYPE;
72 TYPE PER_PROFILE_ID IS TABLE OF HZ_PERSON_PROFILES.PERSON_PROFILE_ID%TYPE;
73
74 TYPE RefCurType IS REF CURSOR;
75
76 /* Validation error columns */
77 TYPE error_ids IS TABLE OF hz_imp_errors.error_id%TYPE;
78 TYPE LOOKUP_ERROR IS TABLE OF ar_lookups.lookup_code%TYPE;
79 TYPE FLAG_ERROR IS TABLE OF VARCHAR2(1);
80
81 PROCEDURE load_parties (
82 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
83 ,x_return_status OUT NOCOPY VARCHAR2
84 ,x_msg_count OUT NOCOPY NUMBER
85 ,x_msg_data OUT NOCOPY VARCHAR2 );
86
87
88 FUNCTION validate_desc_flexfield_f(
89 p_attr_category IN VARCHAR2,
90 p_attr1 IN VARCHAR2,
91 p_attr2 IN VARCHAR2,
92 p_attr3 IN VARCHAR2,
93 p_attr4 IN VARCHAR2,
94 p_attr5 IN VARCHAR2,
95 p_attr6 IN VARCHAR2,
96 p_attr7 IN VARCHAR2,
97 p_attr8 IN VARCHAR2,
98 p_attr9 IN VARCHAR2,
99 p_attr10 IN VARCHAR2,
100 p_attr11 IN VARCHAR2,
101 p_attr12 IN VARCHAR2,
102 p_attr13 IN VARCHAR2,
103 p_attr14 IN VARCHAR2,
104 p_attr15 IN VARCHAR2,
105 p_attr16 IN VARCHAR2,
106 p_attr17 IN VARCHAR2,
107 p_attr18 IN VARCHAR2,
108 p_attr19 IN VARCHAR2,
109 p_attr20 IN VARCHAR2,
110 p_attr21 IN VARCHAR2,
111 p_attr22 IN VARCHAR2,
112 p_attr23 IN VARCHAR2,
113 p_attr24 IN VARCHAR2,
114 p_validation_date IN DATE
115 ) RETURN VARCHAR2;
116
117
118 END HZ_IMP_LOAD_PARTIES_PKG;