[Home] [Help]
PACKAGE BODY: APPS.MTL_MOVEMENT_STATISTICS1_PKG
Source
1 PACKAGE BODY MTL_MOVEMENT_STATISTICS1_PKG as
2 /* $Header: INVTTM1B.pls 120.1 2005/06/11 12:53:42 appldev $ */
3
4 procedure insert_row ( v_rowid IN OUT NOCOPY /* file.sql.39 change */ varchar2,
5 v_movement_id IN OUT NOCOPY /* file.sql.39 change */ number,
6 v_parent_movement_id IN OUT NOCOPY /* file.sql.39 change */ number,
7 v_Organization_Id number,
8 v_Last_Update_Date date,
9 v_Last_Updated_By number,
10 v_Creation_Date date,
11 v_Created_By number,
12 v_Last_Update_Login number,
13 v_movement_type varchar2,
14 v_document_source_type varchar2,
15 v_entity_org_id number,
16 v_Transaction_date date,
17 v_movement_status varchar2,
18 v_go_detail varchar2,
19 v_from_org_id number,
20 v_transacting_from_org varchar2,
21 v_to_org_id number,
22 v_transacting_to_org varchar2,
23 v_customer_name varchar2,
24 v_customer_number varchar2,
25 v_customer_location varchar2,
26 v_ship_to_customer_id number,
27 v_ship_to_site_use_id number,
28 v_vendor_name varchar2,
29 v_vendor_number varchar2,
30 v_vendor_site varchar2,
31 v_vendor_id number,
32 v_vendor_site_id number,
33 v_po_header_id number,
34 v_order_header_id number,
35 v_requisition_header_id number,
36 v_doc_reference varchar2,
37 v_po_line_id number,
38 v_order_line_id number,
39 v_requisition_line_id number,
40 v_doc_line_reference varchar2,
41 v_shipment_header_id number,
42 v_shipment_reference varchar2,
43 v_shipment_line_id number,
44 v_shipment_line_reference varchar2,
45 v_po_line_location_id number,
46 v_picking_line_id number,
47 v_picking_line_detail_id number,
48 v_pick_slip_reference varchar2,
49 v_bill_to_name varchar2,
50 v_bill_to_number varchar2,
51 v_bill_to_site varchar2,
52 v_bill_to_customer_id number,
53 v_bill_to_site_use_id number,
54 v_invoice_batch_id number,
55 v_invoice_batch_reference varchar2,
56 v_invoice_id number,
57 v_invoice_reference varchar2,
58 v_customer_trx_line_id number,
59 v_invoice_line_reference varchar2,
60 v_invoice_qty number,
61 v_invoice_unit_price number,
62 v_invoice_line_ext_val number,
63 v_invoice_date_reference date,
64 v_inventory_item_id number,
65 v_item_cost number,
66 v_item_desc varchar2,
67 v_commodity_code varchar2,
68 v_commodity_code_description varchar2,
69 v_category_id number,
70 v_transaction_uom_code varchar2,
71 v_transaction_qty number,
72 v_document_unit_price number,
73 v_document_line_ext_value number,
74 v_primary_qty number,
75 v_dispatch_terr_code varchar2,
76 v_destination_terr_code varchar2,
77 v_origin_terr_code varchar2,
78 v_txn_nature varchar2,
79 v_delivery_terms varchar2,
80 v_transport_mode varchar2,
81 v_area varchar2,
82 v_port varchar2,
83 v_stat_type varchar2,
84 v_weight_method varchar2,
85 v_unit_weight number ,
86 v_total_weight number ,
87 v_stat_adj_pct number ,
88 v_stat_adj_amt number ,
89 v_stat_ext_val number ,
90 v_stat_method varchar2,
91 v_comments varchar2,
92 v_alt_qty number,
93 v_alt_uom_code varchar2,
94 v_outside_code varchar2,
95 v_outside_unit_price number,
96 v_outside_ext_val number,
97 v_currency_code varchar2,
98 v_attribute_category varchar2,
99 v_attribute1 varchar2 ,
100 v_attribute2 varchar2 ,
101 v_attribute3 varchar2 ,
102 v_attribute4 varchar2 ,
103 v_attribute5 varchar2 ,
104 v_attribute6 varchar2 ,
105 v_attribute7 varchar2 ,
106 v_attribute8 varchar2 ,
107 v_attribute9 varchar2 ,
108 v_attribute10 varchar2 ,
109 v_attribute11 varchar2 ,
110 v_attribute12 varchar2 ,
111 v_attribute13 varchar2 ,
112 v_attribute14 varchar2 ,
113 v_attribute15 varchar2) is
114
115
116 CURSOR C IS SELECT rowid FROM mtl_movement_statistics
117 WHERE movement_id = v_movement_id;
118
119 CURSOR C2 IS SELECT mtl_movement_statistics_s.nextval FROM dual;
120
121 BEGIN
122 if (v_movement_Id is NULL) then
123 OPEN C2;
124 FETCH C2 INTO v_movement_Id;
125 CLOSE C2;
126 end if;
127
128 INSERT INTO mtl_movement_statistics(
129 MOVEMENT_ID ,
130 ORGANIZATION_ID ,
131 ENTITY_ORG_ID ,
132 MOVEMENT_TYPE ,
133 MOVEMENT_STATUS ,
134 TRANSACTION_DATE ,
135 LAST_UPDATE_DATE ,
136 LAST_UPDATED_BY ,
137 CREATION_DATE ,
138 CREATED_BY ,
139 LAST_UPDATE_LOGIN ,
140 DOCUMENT_SOURCE_TYPE ,
141 CREATION_METHOD ,
142 DOCUMENT_REFERENCE ,
143 DOCUMENT_LINE_REFERENCE ,
144 DOCUMENT_UNIT_PRICE ,
145 DOCUMENT_LINE_EXT_VALUE ,
146 RECEIPT_REFERENCE ,
147 SHIPMENT_REFERENCE ,
148 SHIPMENT_LINE_REFERENCE ,
149 PICK_SLIP_REFERENCE ,
150 CUSTOMER_NAME ,
151 CUSTOMER_NUMBER ,
152 CUSTOMER_LOCATION ,
153 TRANSACTING_FROM_ORG ,
154 TRANSACTING_TO_ORG ,
155 VENDOR_NAME ,
156 VENDOR_NUMBER ,
157 VENDOR_SITE ,
158 BILL_TO_NAME ,
159 BILL_TO_NUMBER ,
160 BILL_TO_SITE ,
161 PO_HEADER_ID ,
162 PO_LINE_ID ,
163 PO_LINE_LOCATION_ID ,
164 ORDER_HEADER_ID ,
165 ORDER_LINE_ID ,
166 REQUISITION_HEADER_ID ,
167 REQUISITION_LINE_ID ,
168 PICKING_LINE_ID ,
169 PICKING_LINE_DETAIL_ID ,
170 SHIPMENT_HEADER_ID ,
171 SHIPMENT_LINE_ID ,
172 SHIP_TO_CUSTOMER_ID ,
173 SHIP_TO_SITE_USE_ID ,
174 BILL_TO_CUSTOMER_ID ,
175 BILL_TO_SITE_USE_ID ,
176 VENDOR_ID ,
177 VENDOR_SITE_ID ,
178 FROM_ORGANIZATION_ID ,
179 TO_ORGANIZATION_ID ,
180 PARENT_MOVEMENT_ID ,
181 INVENTORY_ITEM_ID ,
182 ITEM_DESCRIPTION ,
183 ITEM_COST ,
184 TRANSACTION_QUANTITY ,
185 TRANSACTION_UOM_CODE ,
186 PRIMARY_QUANTITY ,
187 INVOICE_BATCH_ID ,
188 INVOICE_ID ,
189 CUSTOMER_TRX_LINE_ID ,
190 INVOICE_BATCH_REFERENCE ,
191 INVOICE_REFERENCE ,
192 INVOICE_LINE_REFERENCE ,
193 INVOICE_DATE_REFERENCE ,
194 INVOICE_QUANTITY ,
195 INVOICE_UNIT_PRICE ,
196 INVOICE_LINE_EXT_VALUE ,
197 OUTSIDE_CODE ,
198 OUTSIDE_EXT_VALUE ,
199 OUTSIDE_UNIT_PRICE ,
200 CURRENCY_CODE ,
201 CATEGORY_ID ,
202 COMMODITY_CODE ,
203 COMMODITY_DESCRIPTION ,
204 WEIGHT_METHOD ,
205 UNIT_WEIGHT ,
206 TOTAL_WEIGHT ,
207 TRANSACTION_NATURE ,
208 DELIVERY_TERMS ,
209 TRANSPORT_MODE ,
210 ALTERNATE_QUANTITY ,
211 ALTERNATE_UOM_CODE ,
212 DISPATCH_TERRITORY_CODE ,
213 DESTINATION_TERRITORY_CODE ,
214 ORIGIN_TERRITORY_CODE ,
215 STAT_METHOD ,
216 STAT_ADJ_PERCENT ,
217 STAT_ADJ_AMOUNT ,
218 STAT_EXT_VALUE ,
219 AREA ,
220 PORT ,
221 STAT_TYPE ,
222 COMMENTS ,
223 ATTRIBUTE_CATEGORY ,
224 ATTRIBUTE1 ,
225 ATTRIBUTE2 ,
226 ATTRIBUTE3 ,
227 ATTRIBUTE4 ,
228 ATTRIBUTE5 ,
229 ATTRIBUTE6 ,
230 ATTRIBUTE7 ,
231 ATTRIBUTE8 ,
232 ATTRIBUTE9 ,
233 ATTRIBUTE10 ,
234 ATTRIBUTE11 ,
235 ATTRIBUTE12 ,
236 ATTRIBUTE13 ,
237 ATTRIBUTE14 ,
238 ATTRIBUTE15)
239 VALUES (
240 v_movement_id,
241 v_organization_id,
242 v_entity_org_id,
243 v_movement_type,
244 v_movement_status,
245 v_transaction_date,
246 v_last_update_date,
247 v_last_updated_by,
248 v_creation_date,
249 v_created_by,
250 v_last_update_login,
251 v_document_source_type,
252 decode(v_go_detail, null, 'M', 'Z'),
253 v_doc_reference,
254 v_doc_line_reference,
255 v_document_unit_price,
256 v_document_line_ext_value,
257 null,
258 v_shipment_reference,
259 v_shipment_line_reference,
260 v_pick_slip_reference,
261 v_customer_name,
262 v_customer_number,
263 v_customer_location,
264 v_transacting_from_org,
265 v_transacting_to_org,
266 v_vendor_name,
267 v_vendor_number,
268 v_vendor_site,
269 v_bill_to_name,
270 v_bill_to_number,
271 v_bill_to_site,
272 v_po_header_id,
273 v_po_line_id,
274 v_po_line_location_id,
275 v_order_header_id,
276 v_order_line_id,
277 v_requisition_header_id,
278 v_requisition_line_id,
279 v_picking_line_id,
280 v_picking_line_detail_id,
281 v_shipment_header_id,
282 v_shipment_line_id,
283 v_ship_to_customer_id,
284 v_ship_to_site_use_id,
285 v_bill_to_customer_id,
286 v_bill_to_site_use_id,
287 v_vendor_id,
288 v_vendor_site_id,
289 v_from_org_id,
290 v_to_org_id,
291 decode(v_parent_movement_id, null, v_movement_id, v_parent_movement_id),
292 v_inventory_item_id,
293 v_item_desc,
294 v_item_cost,
295 v_transaction_qty,
296 v_transaction_uom_code,
297 v_primary_qty,
298 v_invoice_batch_id,
299 v_invoice_id,
300 v_customer_trx_line_id,
301 v_invoice_batch_reference,
302 v_invoice_reference,
303 v_invoice_line_reference,
304 v_invoice_date_reference,
305 v_invoice_qty,
306 v_invoice_unit_price,
307 v_invoice_line_ext_val,
308 v_outside_code,
309 v_outside_ext_val,
310 v_outside_unit_price,
311 v_currency_code,
312 v_category_id,
313 v_commodity_code,
314 v_commodity_code_description,
315 v_weight_method,
316 v_unit_weight,
317 v_total_weight,
318 v_txn_nature,
319 v_delivery_terms,
320 v_transport_mode,
321 v_alt_qty,
322 v_alt_uom_code,
323 v_dispatch_terr_code,
324 v_destination_terr_code,
325 v_origin_terr_code,
326 v_stat_method,
327 v_stat_adj_pct,
328 v_stat_adj_amt,
329 v_stat_ext_val,
330 v_area,
331 v_port,
332 v_stat_type,
333 v_comments,
334 v_attribute_category,
335 v_attribute1,
336 v_attribute2,
337 v_attribute3,
338 v_attribute4,
339 v_attribute5,
340 v_attribute6,
341 v_attribute7,
342 v_attribute8,
343 v_attribute9,
344 v_attribute10,
345 v_attribute11,
346 v_attribute12,
347 v_attribute13,
348 v_attribute14,
349 v_attribute15);
350
351 OPEN C;
352 FETCH C INTO v_RowId;
353 if (C%NOTFOUND) then
354 CLOSE C;
355 Raise NO_DATA_FOUND;
356 end if;
357 CLOSE C;
358
359 END insert_row;
360
361 procedure delete_row(v_rowid varchar2) is
362 begin
363
364 DELETE FROM mtl_movement_statistics
365 WHERE rowid = v_RowId;
366
367 if (SQL%NOTFOUND) then
368 Raise NO_DATA_FOUND;
369 end if;
370 END Delete_Row;
371
372 END MTL_MOVEMENT_STATISTICS1_PKG;