[Home] [Help]
PACKAGE BODY: APPS.OKE_MDS_RELIEF_PKG
Source
1 PACKAGE BODY OKE_MDS_RELIEF_PKG AS
2 /* $Header: OKEMDSFB.pls 120.2 2006/03/16 14:56:45 ausmani noship $ */
3
4 PROCEDURE write_log ( mesg IN VARCHAR2 ) IS
5 BEGIN
6 fnd_file.put_line( fnd_file.log , mesg );
7 END write_log;
8
9
10 PROCEDURE Get_Record (x_mds_tbl IN OUT NOCOPY oke_mds_relief_pkg.mds_tbl_type) IS
11
12 CURSOR Oke_C IS
13
14 SELECT a.transaction_id
15 , a.organization_id
16 , a.inventory_item_id
17 , nvl(a.transaction_source_id , 0) transaction_source_id
18 , a.transaction_source_type_id
19 , a.trx_source_delivery_id
20 , a.trx_source_line_id
21 , a.revision
22 , a.subinventory_code
23 , a.locator_id
24 , a.primary_quantity
25 , a.transaction_quantity
26 , a.transaction_source_name
27 , a.transaction_date
28 , d.mps_transaction_id
29 , d.quantity
30 , d.project_id
31 , d.task_id
32 , d.unit_number
33 FROM mtl_system_items c
34 , mtl_material_transactions a
35 , oke_k_deliverables_b d
36 , mrp_schedule_dates m
37 WHERE a.source_code = 'OKE'
38 AND a.organization_id = c.organization_id
39 AND a.inventory_item_id = c.inventory_item_id
40 AND a.primary_quantity < 0
41 AND a.transaction_source_type_id = 16
42 AND a.transaction_source_id = d.k_header_id
43 AND d.deliverable_id = a.source_line_id
44 And m.mps_transaction_id = d.mps_transaction_id
45 And m.schedule_level = 2
46 And m.supply_demand_type = 1
47 And m.schedule_quantity > 0
48 AND a.transaction_id > nvl( d.po_ref_3 , 0 )
49 ORDER BY a.transaction_id ASC;
50
51 i NUMBER := 1;
52
53 BEGIN
54
55 x_mds_tbl.DELETE;
56
57 FOR c_rec IN oke_c LOOP
58
59 x_mds_tbl(i).mtl_transaction_id := c_rec.transaction_id;
60 x_mds_tbl(i).organization_id := c_rec.organization_id;
61 x_mds_tbl(i).inventory_item_id := c_rec.inventory_item_id;
62 x_mds_tbl(i).transaction_source_id := c_rec.transaction_source_id;
63 x_mds_tbl(i).transaction_source_type_id := c_rec.transaction_source_type_id;
64
65 x_mds_tbl(i).trx_source_delivery_id := c_rec.trx_source_delivery_id;
66 x_mds_tbl(i).trx_source_line_id := c_rec.trx_source_line_id;
67 x_mds_tbl(i).revision := c_rec.revision;
68 x_mds_tbl(i).subinventory_code := c_rec.subinventory_code;
69 x_mds_tbl(i).locator_id := c_rec.locator_id;
70
71 x_mds_tbl(i).primary_quantity := c_rec.primary_quantity;
72 x_mds_tbl(i).transaction_quantity := c_rec.transaction_quantity;
73 x_mds_tbl(i).transaction_source_name := c_rec.transaction_source_name;
74 x_mds_tbl(i).transaction_date := c_rec.transaction_date;
75 x_mds_tbl(i).mps_transaction_id := c_rec.mps_transaction_id;
76
77 x_mds_tbl(i).order_quantity := c_rec.quantity;
78 x_mds_tbl(i).project_id := c_rec.project_id;
79 x_mds_tbl(i).task_id := c_rec.task_id;
80 x_mds_tbl(i).unit_number := c_rec.unit_number;
81
82 i := i + 1;
83
84 END LOOP;
85
86 END Get_Record;
87
88
89 PROCEDURE Mds_Relief
90 ( ERRBUF OUT NOCOPY VARCHAR2
91 , RETCODE OUT NOCOPY NUMBER
92 ) IS
93
94 l_mds_tbl oke_mds_relief_pkg.mds_tbl_type;
95 l_transaction_id NUMBER;
96 l_count NUMBER := 0;
97 l_qty NUMBER;
98 l_schedule_date DATE;
99 i NUMBER;
100 l_now DATE := SYSDATE;
101 l_user_id NUMBER := fnd_global.user_id;
102 l_login_id NUMBER := fnd_global.login_id;
103 l_conc_request_id NUMBER := fnd_global.conc_request_id;
104 l_prog_appl_id NUMBER := fnd_global.prog_appl_id;
105 l_conc_program_id NUMBER := fnd_global.conc_program_id;
106
107 CURSOR qty_c (p_transaction_id NUMBER) IS
108 SELECT schedule_quantity, original_schedule_quantity, schedule_date
109 FROM Mrp_Schedule_Dates
110 WHERE mps_transaction_id = p_transaction_id
111 AND Schedule_Level = 2
112 AND supply_demand_type = 1
113 AND Schedule_Quantity > 0
114 FOR UPDATE OF schedule_quantity;
115
116 CURSOR get_cancelled_line IS
117 SELECT d.mps_transaction_id
118 , d.quantity
119 , d.project_id
120 , d.task_id
121 , d.unit_number
122 , m.schedule_quantity
123 , m.schedule_date
124 FROM okc_k_lines_b a
125 , oke_k_deliverables_b d
126 , mrp_schedule_dates m
127 , okc_statuses_b sts
128 WHERE d.k_line_id = a.id
129 And m.mps_transaction_id = d.mps_transaction_id
130 And m.schedule_level = 2
131 And m.supply_demand_type = 1
132 And m.schedule_quantity > 0
133 And sts.code =a.sts_code
134 And sts.ste_code in ( 'CANCELLED','TERMINATED');
135 BEGIN
136
137 Get_Record(l_mds_tbl);
138
139 IF l_mds_tbl.count > 0 THEN
140
141 FOR i IN l_mds_tbl.FIRST..l_mds_tbl.LAST LOOP
142
143 write_log( 'Processing MTL transaction ' || l_mds_tbl(i).mtl_transaction_id );
144 write_log( 'MDS Txn ID ......... ' || l_mds_tbl(i).mps_transaction_id );
145
146 FOR qty_rec IN qty_c(l_mds_tbl(i).mps_transaction_id) LOOP
147
148 l_qty := qty_rec.schedule_quantity;
149 l_schedule_date := qty_rec.schedule_date;
150
151 write_log( 'Original MDS qty ... ' || qty_rec.original_schedule_quantity );
152 write_log( 'Deliverable qty ... ' || l_mds_tbl(i).order_quantity );
153 write_log( 'Old MDS qty ... ' || qty_rec.schedule_quantity );
154 write_log( 'Shipment qty ....... ' || -l_mds_tbl(i).primary_quantity );
155
156 INSERT INTO mrp_schedule_consumptions(
157 transaction_id,
158 relief_type,
159 disposition_type,
160 disposition_id,
161 line_num,
162 last_update_date,
163 last_updated_by,
164 creation_date,
165 created_by,
166 last_update_login,
167 request_id,
168 program_application_id,
169 program_id,
170 program_update_date,
171 order_date,
172 order_quantity,
173 relief_quantity,
174 schedule_date
175 ) VALUES (
176 l_mds_tbl(i).mps_transaction_id,
177 1, -- MDS_RELIEF 1, MPS_RELIEF 2
178 3, -- R_WORK_ORDER 1, R_PURCH_ORDER 2, R_SALES_ORDER 3
179 NULL, -- l_mds_tbl(i).mtl_transaction_id,
180 NULL, -- No order line_num
181 l_now,
182 l_user_id,
183 l_now,
184 l_user_id,
185 l_login_id,
186 l_conc_request_id,
187 l_prog_appl_id,
188 l_conc_program_id,
189 l_now,
190 l_mds_tbl(i).transaction_date,
191 l_mds_tbl(i).order_quantity,
192 -l_mds_tbl(i).primary_quantity,
193 l_schedule_date
194 );
195
196 write_log( 'Relief record created' );
197
198 SELECT Greatest(Nvl(l_mds_tbl(i).order_quantity,0)-Nvl(SUM(RELIEF_QUANTITY),0),0)
199 INTO l_qty
200 FROM mrp_schedule_consumptions mc
201 where mc.transaction_id = l_mds_tbl(i).mps_transaction_id
202 AND mc.relief_type=1 AND mc.disposition_type=3
203 ;
204
205 write_log( 'New MDS qty ........ ' || l_qty );
206
207 UPDATE mrp_schedule_dates d
208 SET schedule_quantity = l_qty
209 , original_schedule_quantity = l_mds_tbl(i).order_quantity
210 , last_update_date = l_now
211 , last_updated_by = l_user_id
212 , request_id = l_conc_request_id
213 , program_application_id = l_prog_appl_id
214 , program_id = l_conc_program_id
215 , program_update_date = l_now
216 WHERE CURRENT OF qty_c;
217
218 write_log( 'MDS entry updated' );
219
220 UPDATE oke_k_deliverables_b d
221 SET PO_REF_3 = l_mds_tbl(i).mtl_transaction_id
222 WHERE mps_transaction_id = l_mds_tbl(i).mps_transaction_id;
223
224 write_log( 'DLV entry updated' );
225 write_log( '' );
226
227 END LOOP;
228
229 --
230 -- Invoke customer extension
231 --
232 OKE_MDS_RELIEF_EXT.Relief_Demand( P_mds_rec => l_mds_tbl(i) );
233
234 END LOOP;
235 END IF;
236
237 write_log( l_mds_tbl.count || ' Material transactions processed.' );
238
239 FOR cancel_rec IN get_cancelled_line LOOP
240 l_count := l_count +1;
241 write_log( 'MDS Txn ID ......... ' || cancel_rec.mps_transaction_id );
242 write_log( 'cancel_rec.quantity ......... ' || cancel_rec.quantity );
243 write_log( 'cancel_rec.schedule_date ......... ' || cancel_rec.schedule_date );
244 write_log( 'cancel_rec.schedule_quantity ......... ' || cancel_rec.schedule_quantity );
245
246 INSERT INTO mrp_schedule_consumptions(
247 transaction_id,
248 relief_type,
249 disposition_type,
250 disposition_id,
251 line_num,
252 last_update_date,
253 last_updated_by,
254 creation_date,
255 created_by,
256 last_update_login,
257 request_id,
258 program_application_id,
259 program_id,
260 program_update_date,
261 order_date,
262 order_quantity,
263 relief_quantity,
264 schedule_date
265 ) VALUES (
266 cancel_rec.mps_transaction_id,
267 1, -- MDS_RELIEF 1, MPS_RELIEF 2
268 3, -- R_WORK_ORDER 1, R_PURCH_ORDER 2, R_SALES_ORDER 3
269 NULL, -- l_mds_tbl(i).mtl_transaction_id,
270 NULL, -- No order line_num
271 l_now,
272 l_user_id,
273 l_now,
274 l_user_id,
275 l_login_id,
276 l_conc_request_id,
277 l_prog_appl_id,
278 l_conc_program_id,
279 l_now,
280 sysdate,
281 cancel_rec.quantity,
282 cancel_rec.schedule_quantity,
283 cancel_rec.schedule_date
284 );
285 write_log( 'Relief record created' );
286
287 SELECT Greatest(Nvl(cancel_rec.quantity,0)-Nvl(SUM(RELIEF_QUANTITY),0),0)
288 INTO l_qty
289 FROM mrp_schedule_consumptions mc
290 where mc.transaction_id = cancel_rec.mps_transaction_id
291 AND mc.relief_type=1 AND mc.disposition_type=3
292 ;
293
294 write_log( 'New MDS qty ........ ' || l_qty );
295
296 UPDATE mrp_schedule_dates d
297 SET schedule_quantity = l_qty
298 , original_schedule_quantity = cancel_rec.quantity
299 , last_update_date = l_now
300 , last_updated_by = l_user_id
301 , request_id = l_conc_request_id
302 , program_application_id = l_prog_appl_id
303 , program_id = l_conc_program_id
304 , program_update_date = l_now
305 WHERE mps_transaction_id =cancel_rec.mps_transaction_id;
306
307 end loop;
308
309
310 write_log( l_count || ' Contract Line Cancellation/Termination processed.' );
311 ERRBUF := NULL;
312 RETCODE := 0;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 ERRBUF := sqlerrm;
317 RETCODE := 2;
318
319 END Mds_Relief;
320
321 END OKE_MDS_RELIEF_PKG;