1 PACKAGE HZ_IMP_LOAD_WRAPPER AUTHID CURRENT_USER AS
2 /*$Header: ARHLWRPS.pls 120.14 2005/10/30 03:53:23 appldev noship $*/
3
4 TYPE T_ERROR_ID IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
5 TYPE T_ENTITY_ID IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
6 TYPE T_ROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
7 TYPE T_ACTION_FLAG IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
8 TYPE T_TABLE_NAME IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
9 TYPE T_ERROR IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
10
11 TYPE INDEXVARCHAR30List IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
12
13 TYPE DML_RECORD_TYPE IS RECORD (
14 BATCH_ID NUMBER(15),
15 OS VARCHAR2(30),
16 FROM_OSR VARCHAR2(240),
17 TO_OSR VARCHAR2(240),
18 ACTUAL_CONTENT_SRC VARCHAR2(30),
19 RERUN VARCHAR2(1),
20 ERROR_LIMIT NUMBER,
21 BATCH_MODE_FLAG VARCHAR2(1),
22 USER_ID NUMBER(15),
23 SYSDATE DATE,
24 LAST_UPDATE_LOGIN NUMBER(15),
25 PROGRAM_ID NUMBER(15),
26 PROGRAM_APPLICATION_ID NUMBER(15),
27 REQUEST_ID NUMBER(15),
28 APPLICATION_ID NUMBER,
29 GMISS_CHAR VARCHAR2(240),
30 GMISS_NUM NUMBER,
31 GMISS_DATE DATE,
32 FLEX_VALIDATION VARCHAR2(1),
33 DSS_SECURITY VARCHAR2(1),
34 ALLOW_DISABLED_LOOKUP VARCHAR2(1),
35 PROFILE_VERSION VARCHAR2(30)
36 );
37
38 -- Main wrapper for running data load.
39 PROCEDURE DATA_LOAD (
40 Errbuf OUT NOCOPY VARCHAR2,
41 Retcode OUT NOCOPY VARCHAR2,
42 P_BATCH_ID IN NUMBER,
43 P_ORIG_SYSTEM IN VARCHAR2,
44 P_WHAT_IF_ANALYSIS IN VARCHAR2,
45 P_REGISTRY_DEDUP IN VARCHAR2,
46 P_REGISTRY_DEDUP_MATCH_RULE_ID IN NUMBER,
47 P_SYSDATE IN VARCHAR2,
48 P_BATCH_MODE_FLAG IN VARCHAR2,
49 P_NUM_OF_WORKERS IN NUMBER,
50 P_ERROR_LIMIT IN NUMBER,
51 P_RERUN_FLAG IN VARCHAR2,
52 P_REQUEST_ID IN NUMBER,
53 P_PROGRAM_APPLICATION_ID IN NUMBER,
54 P_PROGRAM_ID IN NUMBER
55 );
56
57 -- Wrapper for running batch data load. Call DATA_LOAD.
58 PROCEDURE BATCH_DATA_LOAD (
59 Errbuf OUT NOCOPY VARCHAR2,
60 Retcode OUT NOCOPY VARCHAR2,
61 P_BATCH_ID IN NUMBER,
62 P_ORIG_SYSTEM IN VARCHAR2,
63 P_WHAT_IF_ANALYSIS IN VARCHAR2,
64 P_REGISTRY_DEDUP IN VARCHAR2,
65 P_REGISTRY_DEDUP_MATCH_RULE_ID IN NUMBER,
66 P_SYSDATE IN VARCHAR2,
67 P_NUM_OF_WORKERS IN NUMBER,
68 P_ERROR_LIMIT IN NUMBER,
69 P_RERUN_FLAG IN VARCHAR2,
70 P_REQUEST_ID IN NUMBER,
71 P_PROGRAM_APPLICATION_ID IN NUMBER,
72 P_PROGRAM_ID IN NUMBER
73 );
74
75 -- Wrapper for running online data load. Call DATA_LOAD.
76 PROCEDURE ONLINE_DATA_LOAD (
77 Errbuf OUT NOCOPY VARCHAR2,
78 Retcode OUT NOCOPY VARCHAR2,
79 P_BATCH_ID IN NUMBER,
80 P_ORIG_SYSTEM IN VARCHAR2,
81 P_WHAT_IF_ANALYSIS IN VARCHAR2,
82 P_REGISTRY_DEDUP IN VARCHAR2,
83 P_REGISTRY_DEDUP_MATCH_RULE_ID IN NUMBER,
84 P_SYSDATE IN VARCHAR2,
85 P_ERROR_LIMIT IN NUMBER,
86 P_RERUN_FLAG IN VARCHAR2,
87 P_REQUEST_ID IN NUMBER,
88 P_PROGRAM_APPLICATION_ID IN NUMBER,
89 P_PROGRAM_ID IN NUMBER
90 );
91
92 PROCEDURE RETRIEVE_WORK_UNIT(
93 P_BATCH_ID IN NUMBER,
94 P_STAGE IN NUMBER,
95 P_OS IN OUT NOCOPY VARCHAR2,
96 P_FROM_OSR IN OUT NOCOPY VARCHAR2,
97 P_TO_OSR IN OUT NOCOPY VARCHAR2,
98 P_HWM_STAGE OUT NOCOPY NUMBER,
99 P_PP_STATUS OUT NOCOPY VARCHAR2
100 );
101
102 PROCEDURE RETRIEVE_PP_WORK_UNIT(
103 P_BATCH_ID IN NUMBER,
104 P_PP_STATUS IN VARCHAR2,
105 P_OS IN OUT NOCOPY VARCHAR2,
106 P_FROM_OSR IN OUT NOCOPY VARCHAR2,
107 P_TO_OSR IN OUT NOCOPY VARCHAR2
108 );
109
110 PROCEDURE GENERATE_ENTITIES_WORK_UNITS(
111 P_BATCH_ID IN NUMBER,
112 P_ORIG_SYSTEM IN VARCHAR2
113 );
114
115 PROCEDURE DATA_LOAD_PREPROCESSING(
116 P_BATCH_ID IN NUMBER,
117 P_ORIG_SYSTEM IN VARCHAR2,
118 P_WHAT_IF_ANALYSIS IN VARCHAR2,
119 P_RERUN_FLAG OUT NOCOPY VARCHAR2
120 );
121
122
123 PROCEDURE CLEANUP_STAGING(
124 P_BATCH_ID IN NUMBER,
125 P_BATCH_MODE_FLAG IN VARCHAR2
126 );
127
128 FUNCTION STAGING_DATA_EXISTS(
129 P_BATCH_ID IN NUMBER,
130 P_BATCH_MODE_FLAG IN VARCHAR2,
131 P_STAGE IN NUMBER
132 ) RETURN VARCHAR2;
133
134 END HZ_IMP_LOAD_WRAPPER;