[Home] [Help]
PACKAGE BODY: APPS.OKE_PA_MDS_RELIEF_PKG
Source
1 PACKAGE BODY OKE_PA_MDS_RELIEF_PKG AS
2 /* $Header: OKEVMDSB.pls 120.0 2005/05/25 17:34:01 appldev 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_pa_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.reference2
29 , b.quantity
30 , b.source_header_id
31 , d.task_id
32 , b.unit_number
33 From mtl_system_items c
34 , mtl_material_transactions a
35 , oke_deliverables_b b
36 , oke_deliverable_actions d
37 , mrp_schedule_dates m
38 where a.source_code = 'OKE'
39 And a.organization_id = c.organization_id
40 And a.inventory_item_id = c.inventory_item_id
41 And a.primary_quantity < 0
42 And a.transaction_source_type_id = 16
43 -- And a.transaction_source_id = b.source_header_id bug 3863976
44 And a.transaction_source_id = -99 -- bug 3863976
45 And b.deliverable_id = d.deliverable_id
46 And b.source_code = 'PA'
47 And d.action_id = a.source_line_id
48 And m.mps_transaction_id = d.reference2
49 And m.schedule_level = 2
50 And m.supply_demand_type = 1
51 And m.schedule_quantity > 0
52 And a.transaction_id > nvl( m.old_transaction_id , 0 )
53 Order by a.transaction_id asc;
54
55 l_mds_tbl oke_pa_mds_relief_pkg.mds_tbl_type;
56 l_found Boolean := False;
57 i Number := 1;
58
59 Begin
60
61 For c_rec in oke_c loop
62
63 l_mds_tbl(i).mtl_transaction_id := c_rec.transaction_id;
64 l_mds_tbl(i).organization_id := c_rec.organization_id;
65 l_mds_tbl(i).inventory_item_id := c_rec.inventory_item_id;
66 l_mds_tbl(i).transaction_source_id := c_rec.transaction_source_id;
67 l_mds_tbl(i).transaction_source_type_id := c_rec.transaction_source_type_id;
68
69 l_mds_tbl(i).trx_source_delivery_id := c_rec.trx_source_delivery_id;
70 l_mds_tbl(i).trx_source_line_id := c_rec.trx_source_line_id;
71 l_mds_tbl(i).revision := c_rec.revision;
72 l_mds_tbl(i).subinventory_code := c_rec.subinventory_code;
73 l_mds_tbl(i).locator_id := c_rec.locator_id;
74
75 l_mds_tbl(i).primary_quantity := c_rec.primary_quantity;
76 l_mds_tbl(i).transaction_quantity := c_rec.transaction_quantity;
77 l_mds_tbl(i).transaction_source_name := c_rec.transaction_source_name;
78 l_mds_tbl(i).transaction_date := c_rec.transaction_date;
79 l_mds_tbl(i).mps_transaction_id := c_rec.reference2;
80
81 l_mds_tbl(i).order_quantity := c_rec.quantity;
82 l_mds_tbl(i).project_id := c_rec.source_header_id;
83 l_mds_tbl(i).task_id := c_rec.task_id;
84 l_mds_tbl(i).unit_number := c_rec.unit_number;
85
86 i := i + 1;
87
88 end loop;
89
90 x_mds_tbl := l_mds_tbl;
91
92 end Get_Record;
93
94
95 Procedure Mds_Relief
96 ( ERRBUF OUT NOCOPY VARCHAR2
97 , RETCODE OUT NOCOPY NUMBER
98 ) Is
99
100 l_mds_tbl oke_pa_mds_relief_pkg.mds_tbl_type;
101 l_transaction_id Number;
102 l_qty Number;
103 l_qty_old Number;
104 l_schedule_date Date;
105 i Number;
106 l_return_status Varchar2(1) := oke_api.g_ret_sts_success;
107 l_found Boolean := True;
108 l_api_name Varchar2(30) := 'MDS Relief';
109 l_api_version Number := 1;
110 L_Error_Buf VARCHAR2(4000);
111 l_old_transaction_id Number;
112 l_disposition_id Number;
113 l_value Number;
114
115 Cursor qty_c Is
116 Select schedule_quantity, schedule_date, old_transaction_id
117 From Mrp_Schedule_Dates
118 Where mps_transaction_id = l_transaction_id
119 And Schedule_Level = 2
120 And Schedule_Quantity > 0
121 For Update Of schedule_quantity;
122
123 Cursor c Is
124 Select count(*) from mrp_schedule_consumptions
125 Where disposition_id = l_disposition_id;
126
127 Begin
128
129 Get_Record(l_mds_tbl);
130
131 If l_mds_tbl.count > 0 Then
132
133 i := l_mds_tbl.FIRST;
134 loop
135
136 write_log( 'Processing transaction ' || l_mds_tbl(i).mtl_transaction_id );
137 write_log( 'MDS Txn ID ......... ' || l_mds_tbl(i).mps_transaction_id );
138
139 l_transaction_id := l_mds_tbl(i).mps_transaction_id;
140
141 for qty_rec in qty_c loop
142
143 l_qty := qty_rec.schedule_quantity;
144 l_schedule_date := qty_rec.schedule_date;
145 l_old_transaction_id := qty_rec.old_transaction_id;
146
147 l_qty_old := l_qty;
148
149 if l_qty > l_mds_tbl(i).primary_quantity * (-1) then
150 l_qty := l_qty + l_mds_tbl(i).primary_quantity;
151 else
152 l_qty := 0;
153 end if;
154
155 write_log( 'Original MDS qty ... ' || l_qty_old );
156 write_log( 'Shipment qty ....... ' || (-1) * l_mds_tbl(i).primary_quantity );
157 write_log( 'New MDS qty ........ ' || l_qty );
158
159 if l_mds_tbl(i).mtl_transaction_id > nvl(l_old_transaction_id, 0) then
160
161 update mrp_schedule_dates
162 set schedule_quantity = l_qty,
163 last_update_date = sysdate,
164 last_updated_by = fnd_global.user_id,
165 request_id = fnd_global.conc_request_id,
166 program_application_id = fnd_global.prog_appl_id,
167 program_id = fnd_global.conc_program_id,
168 program_update_date = sysdate,
169 old_transaction_id = l_mds_tbl(i).mtl_transaction_id
170 where mps_transaction_id = l_mds_tbl(i).mps_transaction_id
171 and schedule_level = 2;
172
173 write_log( 'MDS entry updated' );
174
175 INSERT INTO mrp_schedule_consumptions
176 (transaction_id,
177 relief_type,
178 disposition_type,
179 disposition_id,
180 line_num,
181 last_update_date,
182 last_updated_by,
183 creation_date,
184 created_by,
185 last_update_login,
186 request_id,
187 program_application_id,
188 program_id,
189 program_update_date,
190 order_date,
191 order_quantity,
192 relief_quantity,
193 schedule_date)
194 VALUES
195 (
196 l_mds_tbl(i).mps_transaction_id,
197 1, -- MDS_RELIEF 1, MPS_RELIEF 2
198 3, -- R_WORK_ORDER 1, R_PURCH_ORDER 2, R_SALES_ORDER 3
199 null, -- l_mds_tbl(i).mtl_transaction_id,
200 null, -- No order line_num
201 SYSDATE,
202 fnd_global.user_id,
203 SYSDATE,
204 fnd_global.user_id,
205 fnd_global.login_id,
206 fnd_global.conc_request_id,
207 fnd_global.prog_appl_id,
208 fnd_global.conc_program_id,
209 SYSDATE,
210 l_mds_tbl(i).transaction_date,
211 l_mds_tbl(i).order_quantity,
212 l_mds_tbl(i).primary_quantity * -1,
213 l_schedule_date);
214
215 write_log( 'Relief record created' );
216
217 end if;
218
219 write_log( '' );
220
221 END loop;
222
223 --
224 -- Invoke customer extension
225 --
226 -- OKE_MDS_RELIEF_EXT.Relief_Demand( P_mds_rec => l_mds_tbl(i) );
227
228 EXIT WHEN i = l_mds_tbl.LAST;
229 i := l_mds_tbl.NEXT(i);
230 END LOOP;
231 END IF;
232
233 write_log( l_mds_tbl.count || ' transactions processed.' );
234
235 RETCODE := 0;
236
237 EXCEPTION
238 WHEN OTHERS THEN
239 ERRBUF := L_Error_Buf;
240 RETCODE := 2;
241
242 END Mds_Relief;
243
244 END OKE_PA_MDS_RELIEF_PKG;