[Home] [Help]
PACKAGE BODY: APPS.CSP_USAGE_HISTORIES_PVT
Source
1 PACKAGE BODY CSP_USAGE_HISTORIES_PVT AS
2 /* $Header: cspvpuhb.pls 115.26 2004/04/07 22:49:19 hhaugeru ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):='CSP_USAGE_HISTORIES_PVT';
4
5
6 PROCEDURE create_usage_history
7 ( errbuf OUT NOCOPY VARCHAR2,
8 retcode OUT NOCOPY NUMBER,
9 p_api_version IN NUMBER,
10 p_organization_id IN NUMBER
11 ) IS
12
13 l_api_name CONSTANT VARCHAR2(30) := 'create_usage_history';
14 l_api_version CONSTANT NUMBER := 1.0;
15 l_usage_id NUMBER;
16 l_organization_id NUMBER;
17 l_quantity NUMBER;
18 l_cursor NUMBER;
19 l_ddl_string VARCHAR2(100);
20 l_msg_data VARCHAR2(2000);
21 l_msg_count NUMBER;
22 l_return_status VARCHAR2(2000);
23 l_string VARCHAR2(2000);
24 l_start_date DATE;
25 l_parts_loop_id NUMBER;
26 l_period_size NUMBER;
27
28 cursor c_parts_loop is
29 select cplb.parts_loop_id,
30 cfrb.period_size,
31 cfrb.history_periods
32 from csp_parts_loops_b cplb,
33 csp_forecast_rules_b cfrb
34 where cplb.forecast_rule_id = cfrb.forecast_rule_id;
35
36 cursor c_transactions is
37 select decode(mmt.transaction_type_id,52,33,mmt.transaction_type_id) transaction_type_id,
38 mmt.inventory_item_id,
39 mmt.organization_id,
40 trunc(sysdate) - round((to_number(trunc(sysdate) - mmt.transaction_date)/l_period_size+0.5)) * l_period_size period_start_date,
41 mmt.subinventory_code,
42 sum(mmt.primary_quantity) * -1 primary_quantity
43 from mtl_material_transactions mmt,
44 csp_sec_inventories csi
45 where csi.parts_loop_id = l_parts_loop_id
46 and mmt.subinventory_code = csi.secondary_inventory_name
47 and mmt.organization_id = csi.organization_id
48 and mmt.transaction_date >= l_start_date
49 and mmt.transaction_type_id in (52,93)
50 and mmt.primary_quantity < 0
51 group by mmt.transaction_type_id,
52 mmt.inventory_item_id,
53 mmt.organization_id,
54 trunc(sysdate) - round((to_number(trunc(sysdate) - mmt.transaction_date)/l_period_size+0.5)) * l_period_size,
55 mmt.subinventory_code;
56
57 cursor c_sum_parts_loop is
58 select cuh.inventory_item_id,
59 cuh.period_start_date,
60 cuh.transaction_type_id,
61 cuh.parts_loop_id,
62 cuh.organization_id,
63 sum(cuh.quantity) quantity
64 from csp_usage_histories cuh
65 where nvl(cuh.history_data_type,0) = 0
66 group by cuh.inventory_item_id,
67 cuh.period_start_date,
68 cuh.transaction_type_id,
69 cuh.parts_loop_id,
70 cuh.organization_id;
71
72 BEGIN
73 -- Delete from Csp_Usage_Histories
74 EXECUTE IMMEDIATE 'DELETE FROM CSP_USAGE_HISTORIES WHERE HISTORY_DATA_TYPE = 0' ;
75
76 -- Standard Start of API savepoint
77 SAVEPOINT create_usage_history_pvt;
78 -- Standard call to check for call compatibility.
79 IF NOT FND_API.Compatible_API_Call (l_api_version ,
80 p_api_version ,
81 l_api_name ,
82 G_PKG_NAME ) THEN
83 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
84 END IF;
85 -- Initialize API return status to success
86 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 for curs in c_parts_loop loop
89 l_parts_loop_id := curs.parts_loop_id;
90 l_start_date := sysdate - curs.history_periods * curs.period_size;
91 l_period_size := curs.period_size;
92
93 for ct in c_transactions loop
94 -- insert using table handler
95
96 l_usage_id := null;
97 csp_usage_histories_pkg.insert_row(
98 px_usage_id => l_usage_id,
99 p_created_by => fnd_global.user_id,
100 p_creation_date => sysdate,
101 p_last_updated_by => fnd_global.user_id,
102 p_last_update_date => sysdate,
103 p_last_update_login => null,
104 p_inventory_item_id => ct.inventory_item_id,
105 p_organization_id => ct.organization_id,
106 p_period_type => 2,
107 p_period_start_date => ct.period_start_date,
108 p_quantity => ct.primary_quantity,
109 p_request_id => null,
110 p_program_application_id => null,
111 p_program_id => null,
112 p_program_update_date => null,
113 p_subinventory_code => ct.subinventory_code,
114 p_transaction_type_id => ct.transaction_type_id,
115 p_hierarchy_node_id => null,
116 p_parts_loop_id => curs.parts_loop_id,
117 p_history_data_type => 0,
118 p_attribute_category => null,
119 p_attribute1 => null,
120 p_attribute2 => null,
121 p_attribute3 => null,
122 p_attribute4 => null,
123 p_attribute5 => null,
124 p_attribute6 => null,
125 p_attribute7 => null,
126 p_attribute8 => null,
127 p_attribute9 => null,
128 p_attribute10 => null,
129 p_attribute11 => null,
130 p_attribute12 => null,
131 p_attribute13 => null,
132 p_attribute14 => null,
133 p_attribute15 => null);
134 commit;
135 end loop;
136 end loop;
137
138 -- Insert records for Part Loop level
139 for cr in c_sum_parts_loop loop
140 -- insert using table handler
141 l_usage_id := null;
142 csp_usage_histories_pkg.insert_row(
143 px_usage_id => l_usage_id,
144 p_created_by => fnd_global.user_id,
145 p_creation_date => sysdate,
146 p_last_updated_by => fnd_global.user_id,
147 p_last_update_date => sysdate,
148 p_last_update_login => null,
149 p_inventory_item_id => cr.inventory_item_id,
150 p_organization_id => cr.organization_id,
151 p_period_type => 2,
152 p_period_start_date => cr.period_start_date,
153 p_quantity => cr.quantity,
154 p_request_id => null,
155 p_program_application_id => null,
156 p_program_id => null,
157 p_program_update_date => null,
158 p_subinventory_code => '-',
159 p_transaction_type_id => cr.transaction_type_id,
160 p_hierarchy_node_id => null,
161 p_parts_loop_id => cr.parts_loop_id,
162 p_history_data_type => 0,
163 p_attribute_category => null,
164 p_attribute1 => null,
165 p_attribute2 => null,
166 p_attribute3 => null,
167 p_attribute4 => null,
168 p_attribute5 => null,
169 p_attribute6 => null,
170 p_attribute7 => null,
171 p_attribute8 => null,
172 p_attribute9 => null,
173 p_attribute10 => null,
174 p_attribute11 => null,
175 p_attribute12 => null,
176 p_attribute13 => null,
177 p_attribute14 => null,
178 p_attribute15 => null);
179 commit;
180 end loop;
181
182 FND_MSG_PUB.Count_And_Get
183 (p_count => l_msg_count ,
184 p_data => errbuf
185 );
186 EXCEPTION
187 WHEN FND_API.G_EXC_ERROR THEN
188 ROLLBACK TO create_usage_history_pvt;
189 retcode := 2;
190 FND_MSG_PUB.Count_And_Get
191 (p_count => l_msg_count ,
192 p_data => errbuf
193 );
194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
195 ROLLBACK TO create_usage_history_pvt;
196 retcode := 2;
197 FND_MSG_PUB.Count_And_Get
198 (p_count => l_msg_count ,
199 p_data => errbuf
200 );
201 WHEN OTHERS THEN
202 ROLLBACK TO create_usage_history_pvt;
203 retcode := 2;
204 IF FND_MSG_PUB.Check_Msg_Level
205 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
206 FND_MSG_PUB.Add_Exc_Msg
207 ( G_PKG_NAME ,
208 l_api_name
209 );
210 END IF;
211 FND_MSG_PUB.Count_And_Get
212 (p_count => l_msg_count ,
213 p_data => errbuf
214 );
215 END create_usage_history;
216
217 END csp_usage_histories_pvt;