[Home] [Help]
PACKAGE BODY: APPS.HXC_APP_PERIOD_SUMMARY_PKG
Source
1 package body hxc_app_period_summary_pkg as
2 /* $Header: hxcapsum.pkb 120.1 2005/06/30 12:11:17 jdupont noship $ */
3
4 procedure insert_summary_row
5 (p_application_period_id in hxc_app_period_summary.application_period_id%type
6 ,p_application_period_ovn in hxc_app_period_summary.application_period_ovn%type
7 ,p_approval_status in hxc_app_period_summary.approval_status%type
8 ,p_time_recipient_id in hxc_app_period_summary.time_recipient_id%type
9 ,p_time_category_id in hxc_app_period_summary.time_category_id%type
10 ,p_start_time in hxc_app_period_summary.start_time%type
11 ,p_stop_time in hxc_app_period_summary.stop_time%type
12 ,p_resource_id in hxc_app_period_summary.resource_id%type
13 ,p_recipient_sequence in hxc_app_period_summary.recipient_sequence%type
14 ,p_category_sequence in hxc_app_period_summary.category_sequence%type
15 ,p_creation_date in hxc_app_period_summary.creation_date%type
16 ,p_notification_status in hxc_app_period_summary.notification_status%type
17 ,p_approver_id in hxc_app_period_summary.approver_id%type
18 ,p_approval_comp_id in hxc_app_period_summary.approval_comp_id%type
19 ,p_approval_item_type in hxc_app_period_summary.approval_item_type%type
20 ,p_approval_process_name in hxc_app_period_summary.approval_process_name%type
21 ,p_approval_item_key in hxc_app_period_summary.approval_item_key%type
22 ,p_data_set_id in hxc_app_period_summary.data_set_id%type
23 ) is
24
25 Begin
26
27 insert into hxc_app_period_summary
28 (application_period_id
29 ,application_period_ovn
30 ,approval_status
31 ,time_recipient_id
32 ,time_category_id
33 ,start_time
34 ,stop_time
35 ,resource_id
36 ,recipient_sequence
37 ,category_sequence
38 ,creation_date
39 ,notification_status
40 ,approver_id
41 ,approval_comp_id
42 ,approval_item_type
43 ,approval_process_name
44 ,approval_item_key
45 ,data_set_id
46 )
47 values
48 (p_application_period_id
49 ,p_application_period_ovn
50 ,p_approval_status
51 ,p_time_recipient_id
52 ,p_time_category_id
53 ,p_start_time
54 ,p_stop_time
55 ,p_resource_id
56 ,p_recipient_sequence
57 ,p_category_sequence
58 ,p_creation_date
59 ,p_notification_status
60 ,p_approver_id
61 ,p_approval_comp_id
62 ,p_approval_item_type
63 ,p_approval_process_name
64 ,p_approval_item_key
65 ,NULL--p_data_set_id
66 );
67
68 End;
69
70 procedure insert_summary_row
71 (p_app_period_id in hxc_time_building_blocks.time_building_block_id%type
72 ,p_approval_item_type in hxc_app_period_summary.approval_item_type%type
73 ,p_approval_process_name in hxc_app_period_summary.approval_process_name%type
74 ,p_approval_item_key in hxc_app_period_summary.approval_item_key%type
75 ) is
76
77 cursor c_app_period_info(p_id in hxc_time_building_blocks.time_building_block_id%type) is
78 select tbb.time_building_block_id application_period_id
79 ,tbb.resource_id
80 ,tbb.start_time
81 ,tbb.stop_time
82 ,tbb.approval_status
83 ,ta1.attribute1 time_recipient_id
84 ,ta1.attribute11 time_category_id
85 ,decode(ta1.attribute9,'NA',0,to_number(ta1.attribute9)) recipient_sequence
86 ,ta1.attribute12 category_sequence
87 ,ta1.attribute13 approval_comp_id
88 ,tbb.creation_date
89 ,ta1.attribute4 notification_status
90 ,decode(ta1.attribute3,'NA',0,to_number(ta1.attribute3)) approver_id
91 ,tbb.object_version_number application_period_ovn
92 ,tbb.data_set_id
93 from hxc_time_building_blocks tbb, hxc_time_attribute_usages tau, hxc_time_attributes ta1
94 where tbb.time_building_block_id = p_id
95 and tbb.date_to = hr_general.end_of_time
96 and tbb.scope = 'APPLICATION_PERIOD'
97 and tau.time_building_block_id = tbb.time_building_block_id
98 and tau.time_building_block_ovn = tbb.object_version_number
99 and tau.time_attribute_id = ta1.time_attribute_id
100 and ta1.attribute_category = 'APPROVAL'
101 and tau.time_attribute_usage_id =
102 (select max(time_attribute_usage_id) from hxc_time_attribute_usages tau2
103 where tau2.time_building_block_id = tau.time_building_block_id
104 and tau2.time_building_block_ovn = tau.time_building_block_ovn
105 );
106
107 l_app_summary_row c_app_period_info%rowtype;
108
109 Begin
110
111 open c_app_period_info(p_app_period_id);
112 fetch c_app_period_info into l_app_summary_row;
113
114 if(c_app_period_info%found) then
115 insert into hxc_app_period_summary
116 (application_period_id
117 ,application_period_ovn
118 ,approval_status
119 ,time_recipient_id
120 ,time_category_id
121 ,start_time
122 ,stop_time
123 ,resource_id
124 ,recipient_sequence
125 ,category_sequence
126 ,creation_date
127 ,notification_status
128 ,approver_id
129 ,approval_comp_id
130 ,approval_item_type
131 ,approval_process_name
132 ,approval_item_key
133 ,data_set_id
134 )
135 values
136 (l_app_summary_row.application_period_id
137 ,l_app_summary_row.application_period_ovn
138 ,l_app_summary_row.approval_status
139 ,l_app_summary_row.time_recipient_id
140 ,l_app_summary_row.time_category_id
141 ,l_app_summary_row.start_time
142 ,l_app_summary_row.stop_time
143 ,l_app_summary_row.resource_id
144 ,l_app_summary_row.recipient_sequence
145 ,l_app_summary_row.category_sequence
146 ,l_app_summary_row.creation_date
147 ,l_app_summary_row.notification_status
148 ,l_app_summary_row.approver_id
149 ,l_app_summary_row.approval_comp_id
150 ,p_approval_item_type
151 ,p_approval_process_name
152 ,p_approval_item_key
153 ,NULL--l_app_summary_row.data_set_id
154 );
155 end if;
156
157 close c_app_period_info;
158
159 End insert_summary_row;
160
161 procedure update_summary_row(p_app_period_id in hxc_time_building_blocks.time_building_block_id%type
162 ,p_approval_item_type in hxc_app_period_summary.approval_item_type%type
163 ,p_approval_process_name in hxc_app_period_summary.approval_process_name%type
164 ,p_approval_item_key in hxc_app_period_summary.approval_item_key%type
165 ) is
166
167 Begin
168
169 null;
170
171 End update_summary_row;
172
173 procedure delete_summary_row(p_app_period_id in hxc_time_building_blocks.time_building_block_id%type) is
174
175 Begin
176
177 delete from hxc_app_period_summary where application_period_id = p_app_period_id;
178
179 Exception
180 When others then
181 FND_MESSAGE.set_name('HXC','HXC_NO_APP_PERIOD_ID');
182 FND_MESSAGE.raise_error;
183
184 End delete_summary_row;
185
186 end hxc_app_period_summary_pkg;