[Home] [Help]
PACKAGE BODY: APPS.HRDPP_CREATE_USER_TABLE
Source
1 package body hrdpp_CREATE_USER_TABLE as
2 /*
3 * Generated by hr_pump_meta_mapper at: 2007/01/03 23:01:50
4 * Generated for API: PAY_USER_TABLE_API.CREATE_USER_TABLE
5 */
6 --
7 dh constant date := hr_api.g_date;
8 nh constant number := hr_api.g_number;
9 vh constant varchar2(64) := hr_api.g_varchar2;
10 c_sot constant date := to_date('01010001','DDMMYYYY');
11 cn constant varchar2(32) := '<NULL>';
12 dn constant date := null;
13 nn constant number := null;
14 vn constant varchar2(1) := null;
15 --
16 function dc(p in date) return varchar2 is
17 begin
18 if p<c_sot then
19 if p<>trunc(p) then
20 return to_char(p,'SYYYY/MM/DD HH24:MI:SS');
21 end if;
22 return to_char(p,'SYYYY/MM/DD');
23 elsif p<>trunc(p) then
24 return to_char(p,'YYYY/MM/DD HH24:MI:SS');
25 end if;
26 return to_char(p,'YYYY/MM/DD');
27 end dc;
28 function d(p in varchar2) return date is
29 begin
30 if length(p)=10 then
31 return to_date(p,'YYYY/MM/DD');
32 elsif length(p)=19 then
33 return to_date(p,'YYYY/MM/DD HH24:MI:SS');
34 elsif length(p)=11 then
35 return to_date(p,'SYYYY/MM/DD');
36 elsif length(p)=20 then
37 return to_date(p,'SYYYY/MM/DD HH24:MI:SS');
38 end if;
39 -- Try default format as last resort.
40 return to_date(p,'YYYY/MM/DD');
41 end d;
42 function n(p in varchar2) return number is
43 begin
44 return to_number(p);
45 end n;
46 function dd(p in date,i in varchar2)
47 return varchar2 is
48 begin
49 if upper(i) = 'N' then return dc(p);
50 else return cn; end if;
51 end dd;
52 function nd(p in number,i in varchar2)
53 return varchar2 is
54 begin
55 if upper(i) = 'N' then return to_char(p);
56 else return cn; end if;
57 end nd;
58 --
59 procedure iuk
60 (p_batch_line_id in number,
61 p_user_key_value in varchar2,
62 p_unique_key_id in number)
63 is
64 begin
65 hr_data_pump.entry('ins_user_key');
66 insert into hr_pump_batch_line_user_keys
67 (user_key_id, batch_line_id,user_key_value,unique_key_id)
68 values
69 (hr_pump_batch_line_user_keys_s.nextval,
70 p_batch_line_id,
71 p_user_key_value,
72 p_unique_key_id);
73 hr_data_pump.exit('ins_user_key');
74 end iuk;
75 --
76 procedure insert_batch_lines
77 (p_batch_id in number
78 ,p_data_pump_batch_line_id in number default null
79 ,p_data_pump_business_grp_name in varchar2 default null
80 ,p_user_sequence in number default null
81 ,p_link_value in number default null
82 ,P_EFFECTIVE_DATE in date
83 ,P_LEGISLATION_CODE in varchar2 default null
84 ,P_RANGE_OR_MATCH in varchar2 default null
85 ,P_USER_KEY_UNITS in varchar2 default null
86 ,P_USER_TABLE_NAME in varchar2
87 ,P_USER_ROW_TITLE in varchar2 default null
88 ,P_USER_TABLE_USER_KEY in varchar2) is
89 blid number := p_data_pump_batch_line_id;
90 begin
91 if blid is not null then
92 delete from hr_pump_batch_lines where batch_line_id = blid;
93 delete from hr_pump_batch_exceptions
94 where source_type = 'BATCH_LINE' and source_id = blid;
95 end if;
96 insert into hr_pump_batch_lines
97 (batch_id
98 ,batch_line_id
99 ,business_group_name
100 ,api_module_id
101 ,line_status
102 ,user_sequence
103 ,link_value
104 ,pval001
105 ,pval002
106 ,pval003
107 ,pval004
108 ,pval005
109 ,pval006
110 ,pval007)
111 values
112 (p_batch_id
113 ,nvl(blid,hr_pump_batch_lines_s.nextval)
114 ,p_data_pump_business_grp_name
115 ,3042
116 ,'U'
117 ,p_user_sequence
118 ,p_link_value
119 ,dc(P_EFFECTIVE_DATE)
120 ,P_LEGISLATION_CODE
121 ,P_RANGE_OR_MATCH
122 ,P_USER_KEY_UNITS
123 ,P_USER_TABLE_NAME
124 ,P_USER_ROW_TITLE
125 ,P_USER_TABLE_USER_KEY);
126 end insert_batch_lines;
127 --
128 procedure call
129 (p_business_group_id in number,
130 p_batch_line_id in number) is
131 cursor cr is
132 select l.rowid myrowid,
133 decode(l.pval001,cn,dn,d(l.pval001)) p1,
134 decode(l.pval002,cn,vn,vn,vn,l.pval002) p2,
135 l.pval002 d2,
136 decode(l.pval003,cn,vn,vn,'M',
137 hr_pump_get.gl(l.pval003,'RANGE_MATCH',d(l.pval001),vn)) p3,
138 l.pval003 d3,
139 decode(l.pval004,cn,vn,vn,'N',
140 hr_pump_get.gl(l.pval004,'DATA_TYPE',d(l.pval001),vn)) p4,
141 l.pval004 d4,
142 decode(l.pval005,cn,vn,l.pval005) p5,
143 decode(l.pval006,cn,vn,vn,vn,l.pval006) p6,
144 l.pval006 d6,
145 l.pval007 p7,
146 l.pval008 p8
147 from hr_pump_batch_lines l
148 where l.batch_line_id = p_batch_line_id;
149 --
150 c cr%rowtype;
151 l_validate boolean := false;
152 L_USER_TABLE_ID number;
153 --
154 begin
155 hr_data_pump.entry('call');
156 open cr;
157 fetch cr into c;
158 if cr%notfound then
159 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
160 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
161 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
162 hr_utility.set_message_token('VALUE',p_batch_line_id);
163 hr_utility.raise_error;
164 end if;
165 --
166 hr_data_pump.api_trc_on;
167 PAY_USER_TABLE_API.CREATE_USER_TABLE
168 (p_validate => l_validate
169 ,P_EFFECTIVE_DATE => c.p1
170 ,p_business_group_id => p_business_group_id
171 ,P_LEGISLATION_CODE => c.p2
172 ,P_RANGE_OR_MATCH => c.p3
173 ,P_USER_KEY_UNITS => c.p4
174 ,P_USER_TABLE_NAME => c.p5
175 ,P_USER_ROW_TITLE => c.p6
176 ,P_USER_TABLE_ID => L_USER_TABLE_ID
177 ,P_OBJECT_VERSION_NUMBER => c.p8);
178 hr_data_pump.api_trc_off;
179 --
180 iuk(p_batch_line_id,c.p7,L_USER_TABLE_ID);
181 --
182 update hr_pump_batch_lines l set
183 l.pval007 = decode(c.p7,null,cn,c.p7),
184 l.pval008 = decode(c.p8,null,cn,c.p8)
185 where l.rowid = c.myrowid;
186 --
187 close cr;
188 --
189 hr_data_pump.exit('call');
190 exception
191 when hr_multi_message.error_message_exist then
192 if cr%isopen then
193 close cr;
194 end if;
195 hr_pump_utils.set_multi_msg_error_flag(true);
196 when others then
197 if cr%isopen then
198 close cr;
199 end if;
200 raise;
201 end call;
202 end hrdpp_CREATE_USER_TABLE;