[Home] [Help]
PACKAGE: APPS.INV_MGD_MVT_DATA_STR
Source
1 PACKAGE INV_MGD_MVT_DATA_STR AUTHID CURRENT_USER AS
2 -- $Header: INVDATSS.pls 120.2.12010000.2 2008/10/01 11:52:40 ajmittal ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVDATSS.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Spec of INV_MGD_MVT_DATA_STR |
13 --| Data Structure definitions:Includes record types, REF cursors |
14 --| |
15 --| HISTORY |
16 --| 03/20/00 pseshadr Created |
17 --| 06/07/00 ksaini Added spec for Val_crsr cursor |
18 --| 02/01/01 odaboval Added spec for TRNI/TRNR, XFER cursors |
19 --| for OPSO and RECV cursors |
20 --| 04/01/01 odaboval Added 2 new rec-types. |
21 --| 11/22/02 vma Removed defaulting parameters to |
22 --| FND_API.G_MISS_NUM in Trans_Rec |
23 --| 16/04/2007 Neelam Soni Bug 5920143. Added support for Include |
24 --| Establishments. |
25 --| 02-Aug-08 ajmittal Bug 7165989 - Movement Statistics RMA |
26 --| Triangulation uptake. Modified the |
27 --| Movement_Transaction_Rec_Type record |
28 --| to include an additional attribue |
29
30 --+======================================================================*/
31
32 --===================
33 -- TYPES
34 --===================
35 -- This record type contains the attributes that are not in movement
36 -- statistics table but these are used to derive some information
37 -- to populate mtl_movement_statistics for transaction type INVENTORY
38
39 TYPE Material_Transaction_Rec_Type IS RECORD
40 (
41 transaction_type_id mtl_material_transactions.transaction_type_id%TYPE
42 , transaction_action_id mtl_material_transactions.transaction_action_id%TYPE
43 , transfer_organization_id
44 mtl_material_transactions.transfer_organization_id%TYPE
45 , outside_processing_account
46 mtl_material_transactions.outside_processing_account%TYPE
47 , vendor_lot_number mtl_material_transactions.vendor_lot_number%TYPE
48 , acct_period_id mtl_material_transactions.acct_period_id%TYPE
49 , transaction_date mtl_material_transactions.transaction_id%TYPE
50 , last_update_date DATE := NULL
51 , last_updated_by NUMBER := NULL
52 , last_update_login NUMBER := NULL
53 );
54
55 -- This record type contains the attributes that are not in movement
56 -- statistics table but these are used to derive some information
57 -- to populate mtl_movement_statistics for transaction type RECEIPT/PO
58
59 TYPE Receipt_Transaction_Rec_Type IS RECORD
60 (
61 parent_transaction_id rcv_transactions.parent_transaction_id%TYPE
62 , po_release_id rcv_transactions.po_release_id%TYPE
63 , to_organization_id rcv_shipment_lines.to_organization_id%TYPE
64 , mrc_currency_conversion_date
65 rcv_transactions.mrc_currency_conversion_date%TYPE
66 , mrc_currency_conversion_rate
67 rcv_transactions.mrc_currency_conversion_rate%TYPE
68 , mrc_currency_conversion_type
69 rcv_transactions.mrc_currency_conversion_type%TYPE
70 , mrc_po_unit_price NUMBER
71 , primary_unit_of_measure
72 rcv_transactions.primary_unit_of_measure%TYPE
73 , uom_code rcv_transactions.uom_code%TYPE
74 , transaction_type rcv_transactions.transaction_type%TYPE
75 , source_document_code rcv_transactions.source_document_code%TYPE
76 , last_update_date DATE := NULL
77 , last_updated_by NUMBER := NULL
78 , last_update_login NUMBER := NULL
79 );
80
81
82 -- This record type contains the attributes that are not in movement
83 -- statistics table but these are used to derive some information
84 -- to populate mtl_movement_statistics for transaction type SALES ORDER
85
86 TYPE Shipment_Transaction_Rec_Type IS RECORD
87 (
88 org_id wsh_delivery_details.org_id%TYPE
89 , so_org_id oe_order_headers_all.org_id%TYPE
90 , shipped_quantity oe_order_lines_all.shipped_quantity%TYPE
91 , last_update_date DATE := NULL
92 , last_updated_by NUMBER := NULL
93 , last_update_login NUMBER := NULL
94 , drop_ship_source_id oe_drop_ship_sources.drop_ship_source_id%TYPE
95 , destination_org_id
96 oe_drop_ship_sources.destination_organization_id%TYPE
97 , req_line_num oe_order_lines_all.orig_sys_line_ref%TYPE
98 , req_num oe_order_headers_all.orig_sys_document_ref%TYPE
99 , order_source_id oe_order_headers_all.order_source_id%TYPE
100 , mvt_stat_status wsh_delivery_details.mvt_stat_status%TYPE
101 );
102
103
104 TYPE Movement_Transaction_Rec_Type IS RECORD
105 ( movement_id mtl_movement_statistics.movement_id%TYPE
106 , organization_id mtl_movement_statistics.organization_id%TYPE
107 , entity_org_id mtl_movement_statistics.entity_org_id%TYPE
108 , movement_type mtl_movement_statistics.movement_type%TYPE
109 , movement_status mtl_movement_statistics.movement_status%TYPE
110 , transaction_date mtl_movement_statistics.transaction_date%TYPE
111 , last_update_date mtl_movement_statistics.last_update_date%TYPE
112 , last_updated_by mtl_movement_statistics.last_updated_by%TYPE
113 , creation_date mtl_movement_statistics.creation_date%TYPE
114 , created_by mtl_movement_statistics.created_by%TYPE
115 , last_update_login mtl_movement_statistics.last_update_login%TYPE
116 , document_source_type mtl_movement_statistics.document_source_type%TYPE
117 , creation_method mtl_movement_statistics.creation_method%TYPE
118 , document_reference mtl_movement_statistics.document_reference%TYPE
119 , document_line_reference
120 mtl_movement_statistics.document_line_reference%TYPE
121 , document_unit_price mtl_movement_statistics.document_unit_price%TYPE
122 , document_line_ext_value
123 mtl_movement_statistics.document_line_ext_value%TYPE
124 , extended_amount NUMBER
125 , receipt_reference mtl_movement_statistics.receipt_reference%TYPE
126 , receipt_num rcv_shipment_headers.receipt_num%TYPE
127 , shipment_reference mtl_movement_statistics.shipment_reference%TYPE
128 , shipment_line_reference
129 mtl_movement_statistics.shipment_line_reference%TYPE
130 , pick_slip_reference mtl_movement_statistics.pick_slip_reference%TYPE
131 , customer_name mtl_movement_statistics.customer_name%TYPE
132 , customer_number mtl_movement_statistics.customer_number%TYPE
133 , customer_location mtl_movement_statistics.customer_location%TYPE
134 , transacting_from_org mtl_movement_statistics.transacting_from_org%TYPE
135 , transacting_to_org mtl_movement_statistics.transacting_to_org%TYPE
136 , transfer_organization_id
137 mtl_material_transactions.transfer_organization_id%TYPE
138 , vendor_name mtl_movement_statistics.vendor_name%TYPE
139 , vendor_number mtl_movement_statistics.vendor_number%TYPE
140 , vendor_site mtl_movement_statistics.vendor_site%TYPE
141 , bill_to_name mtl_movement_statistics.bill_to_name%TYPE
142 , bill_to_number mtl_movement_statistics.bill_to_number%TYPE
143 , bill_to_site mtl_movement_statistics.bill_to_site%TYPE
144 , po_header_id mtl_movement_statistics.po_header_id%TYPE
145 , po_line_id mtl_movement_statistics.po_line_id%TYPE
146 , po_line_location_id mtl_movement_statistics.po_line_location_id%TYPE
147 , order_header_id mtl_movement_statistics.order_header_id%TYPE
148 , order_line_id mtl_movement_statistics.order_line_id%TYPE
149 , picking_line_id mtl_movement_statistics.picking_line_id%TYPE
150 , shipment_header_id mtl_movement_statistics.shipment_header_id%TYPE
151 , shipment_number rcv_shipment_headers.shipment_num%TYPE
152 , shipment_line_id mtl_movement_statistics.shipment_line_id%TYPE
153 , ship_to_customer_id mtl_movement_statistics.ship_to_customer_id%TYPE
154 , ship_to_site_use_id mtl_movement_statistics.ship_to_site_use_id%TYPE
155 , bill_to_customer_id mtl_movement_statistics.bill_to_customer_id%TYPE
156 , bill_to_site_use_id mtl_movement_statistics.bill_to_site_use_id%TYPE
157 , vendor_id mtl_movement_statistics.vendor_id%TYPE
158 , vendor_site_id mtl_movement_statistics.vendor_site_id%TYPE
159 , from_organization_id mtl_movement_statistics.from_organization_id%TYPE
160 , to_organization_id mtl_movement_statistics.to_organization_id%TYPE
161 , parent_movement_id mtl_movement_statistics.parent_movement_id%TYPE
162 , inventory_item_id mtl_movement_statistics.inventory_item_id%TYPE
163 , item_description mtl_movement_statistics.item_description%TYPE
164 , item_cost mtl_movement_statistics.item_cost%TYPE
165 , transaction_quantity mtl_movement_statistics.transaction_quantity%TYPE
166 , transaction_uom_code mtl_movement_statistics.transaction_uom_code%TYPE
167 , primary_quantity mtl_movement_statistics.primary_quantity%TYPE
168 , primary_uom_code mtl_movement_statistics.transaction_uom_code%TYPE
169 , shipped_quantity wsh_delivery_details.shipped_quantity%TYPE
170 , invoice_batch_id mtl_movement_statistics.invoice_batch_id%TYPE
171 , invoice_id mtl_movement_statistics.invoice_id%TYPE
172 , customer_trx_line_id mtl_movement_statistics.customer_trx_line_id%TYPE
173 , invoice_batch_reference
174 mtl_movement_statistics.invoice_batch_reference%TYPE
175 , invoice_reference mtl_movement_statistics.invoice_reference%TYPE
176 , invoice_line_reference
177 mtl_movement_statistics.invoice_line_reference%TYPE
178 , invoice_date_reference
179 mtl_movement_statistics.invoice_date_reference%TYPE
180 , invoice_quantity mtl_movement_statistics.invoice_quantity%TYPE
181 , invoice_unit_price mtl_movement_statistics.invoice_unit_price%TYPE
182 , invoice_line_ext_value
183 mtl_movement_statistics.invoice_line_ext_value%TYPE
184 , outside_code mtl_movement_statistics.outside_code%TYPE
185 , outside_ext_value mtl_movement_statistics.outside_ext_value%TYPE
186 , outside_unit_price mtl_movement_statistics.outside_unit_price%TYPE
187 , currency_code mtl_movement_statistics.currency_code%TYPE
188 , gl_currency_code VARCHAR2(15)
189 , currency_conversion_rate
190 mtl_movement_statistics.currency_conversion_rate%TYPE
191 , currency_conversion_type
192 mtl_movement_statistics.currency_conversion_type%TYPE
193 , currency_conversion_date
194 mtl_movement_statistics.currency_conversion_date%TYPE
195 , period_name mtl_movement_statistics.period_name%TYPE
196 , report_reference mtl_movement_statistics.report_reference%TYPE
197 , report_date mtl_movement_statistics.report_date%TYPE
198 , category_id mtl_movement_statistics.category_id%TYPE
199 , weight_method mtl_movement_statistics.weight_method%TYPE
200 , unit_weight mtl_movement_statistics.unit_weight%TYPE
201 , total_weight mtl_movement_statistics.total_weight%TYPE
202 , transaction_nature mtl_movement_statistics.transaction_nature%TYPE
203 , delivery_terms mtl_movement_statistics.delivery_terms%TYPE
204 , transport_mode mtl_movement_statistics.transport_mode%TYPE
205 , alternate_quantity mtl_movement_statistics.alternate_quantity%TYPE
206 , alternate_uom_code mtl_movement_statistics.alternate_uom_code%TYPE
207 , dispatch_territory_code
208 mtl_movement_statistics.dispatch_territory_code%TYPE
209 , destination_territory_code
210 mtl_movement_statistics.destination_territory_code%TYPE
211 , origin_territory_code
212 mtl_movement_statistics.origin_territory_code%TYPE
213 , origin_territory_eu_code
214 mtl_movement_statistics.origin_territory_eu_code%TYPE
215 , dispatch_territory_eu_code
216 mtl_movement_statistics.dispatch_territory_eu_code%TYPE
217 , destination_territory_eu_code
218 mtl_movement_statistics.destination_territory_eu_code%TYPE
219 , triangulation_country_eu_code
220 mtl_movement_statistics.triangulation_country_eu_code%TYPE
221 , stat_method mtl_movement_statistics.stat_method%TYPE
222 , stat_adj_percent mtl_movement_statistics.stat_adj_percent%TYPE
223 , stat_adj_amount mtl_movement_statistics.stat_adj_amount%TYPE
224 , stat_ext_value mtl_movement_statistics.stat_ext_value%TYPE
225 , area mtl_movement_statistics.area%TYPE
226 , port mtl_movement_statistics.port%TYPE
227 , stat_type mtl_movement_statistics.stat_type%TYPE
228 , statistical_procedure_code
229 mtl_movement_statistics.statistical_procedure_code%TYPE
230 , comments mtl_movement_statistics.comments%TYPE
231 , attribute_category mtl_movement_statistics.attribute_category%TYPE
232 , attribute1 mtl_movement_statistics.attribute1%TYPE
233 , attribute2 mtl_movement_statistics.attribute2%TYPE
234 , attribute3 mtl_movement_statistics.attribute3%TYPE
235 , attribute4 mtl_movement_statistics.attribute4%TYPE
236 , attribute5 mtl_movement_statistics.attribute5%TYPE
237 , attribute6 mtl_movement_statistics.attribute6%TYPE
238 , attribute7 mtl_movement_statistics.attribute7%TYPE
239 , attribute8 mtl_movement_statistics.attribute8%TYPE
240 , attribute9 mtl_movement_statistics.attribute9%TYPE
241 , attribute10 mtl_movement_statistics.attribute10%TYPE
242 , attribute11 mtl_movement_statistics.attribute11%TYPE
243 , attribute12 mtl_movement_statistics.attribute12%TYPE
244 , attribute13 mtl_movement_statistics.attribute13%TYPE
245 , attribute14 mtl_movement_statistics.attribute14%TYPE
246 , attribute15 mtl_movement_statistics.attribute15%TYPE
247 , commodity_code mtl_movement_statistics.commodity_code%TYPE
248 , commodity_description
249 mtl_movement_statistics.commodity_description%TYPE
250 , requisition_header_id
251 mtl_movement_statistics.requisition_header_id%TYPE
252 , requisition_line_id mtl_movement_statistics.requisition_line_id%TYPE
253 , picking_line_detail_id
254 mtl_movement_statistics.picking_line_detail_id%TYPE
255 , usage_type mtl_movement_statistics.usage_type%TYPE
256 , zone_code mtl_movement_statistics.zone_code%TYPE
257 , edi_sent_flag mtl_movement_statistics.edi_sent_flag%TYPE
258 , movement_amount mtl_movement_statistics.movement_amount%TYPE
259 , order_number oe_order_headers_all.order_number%TYPE
260 , line_number oe_order_lines_all.line_number%TYPE
261 , triangulation_country_code
262 mtl_movement_statistics.triangulation_country_code%TYPE
263 , csa_code mtl_movement_statistics.csa_code%TYPE
264 , taric_code mtl_movement_statistics.taric_code%TYPE
265 , preference_code mtl_movement_statistics.preference_code%TYPE
266 , oil_reference_code mtl_movement_statistics.oil_reference_code%TYPE
267 , container_type_code mtl_movement_statistics.container_type_code%TYPE
268 , flow_indicator_code mtl_movement_statistics.flow_indicator_code%TYPE
269 , affiliation_reference_code
270 mtl_movement_statistics.affiliation_reference_code%TYPE
271 , set_of_books_period mtl_movement_statistics.set_of_books_period%TYPE
272 , distribution_line_number
273 mtl_movement_statistics.distribution_line_number%TYPE
274 , rcv_transaction_id mtl_movement_statistics.rcv_transaction_id%TYPE
275 , mtl_transaction_id mtl_movement_statistics.mtl_transaction_id%TYPE
276 , total_weight_uom_code mtl_movement_statistics.total_weight_uom_code%TYPE
277 , ship_to_name mtl_movement_statistics.ship_to_name%TYPE
278 , ship_to_number mtl_movement_statistics.ship_to_number%TYPE
279 , ship_to_site mtl_movement_statistics.ship_to_site%TYPE
280 , financial_document_flag
281 mtl_movement_statistics.financial_document_flag%TYPE
282 , edi_transaction_reference
283 mtl_movement_statistics.edi_transaction_reference%TYPE
284 , edi_transaction_date
285 mtl_movement_statistics.edi_transaction_date%TYPE
286 , esl_drop_shipment_code mtl_movement_statistics.esl_drop_shipment_code%TYPE
287 , customer_vat_number mtl_movement_statistics.customer_vat_number%TYPE
288 , transaction_type_id mtl_material_transactions.transaction_type_id%TYPE
289 , transaction_action_id mtl_material_transactions.transaction_action_id%TYPE
290 --, opm_trans_id NUMBER
291 , org_id oe_order_headers_all.org_id%TYPE
292 , release_id rcv_transactions.po_release_id%TYPE
293 , type_lookup_code po_headers_all.type_lookup_code%TYPE
294 , reference_date DATE
295 , consigned_flag rcv_transactions.consigned_flag%TYPE
296 , sold_from_org_id oe_order_headers_all.sold_from_org_id%TYPE -- 7165989
297 );
298
299 --Bug: 5920143. New column include_establishments has beed added in
300 -- record definition.
301 TYPE Movement_Stat_Usages_Rec_Type IS RECORD
302 ( legal_entity_id mtl_stat_type_usages.legal_entity_id%TYPE
303 , zone_code mtl_stat_type_usages.zone_code%TYPE
304 , usage_type mtl_stat_type_usages.usage_type%TYPE
305 , stat_type mtl_stat_type_usages.stat_type%TYPE
306 , start_date DATE
307 , end_date DATE
308 , conversion_option mtl_stat_type_usages.conversion_option%TYPE
309 , conversion_type mtl_stat_type_usages.conversion_type%TYPE
310 , category_set_id mtl_stat_type_usages.category_set_id%TYPE
311 , weight_uom_code mtl_stat_type_usages.weight_uom_code%TYPE
312 , gl_currency_code VARCHAR2(15)
313 , gl_set_of_books_id NUMBER
314 , gl_end_date DATE
315 , gl_period_name VARCHAR2(15)
316 , start_period_name mtl_stat_type_usages.start_period_name%TYPE
317 , end_period_name mtl_stat_type_usages.end_period_name%TYPE
318 , period_set_name mtl_stat_type_usages.period_set_name%TYPE
319 , period_type mtl_stat_type_usages.period_type%TYPE
320 , attribute_rule_set_code
321 mtl_stat_type_usages.attribute_rule_set_code%TYPE
322 , alt_uom_rule_set_code
323 mtl_stat_type_usages.alt_uom_rule_set_code%TYPE
324 , triangulation_mode mtl_stat_type_usages.triangulation_mode%TYPE
325 , reference_period_rule mtl_stat_type_usages.reference_period_rule%TYPE
326 , pending_invoice_days mtl_stat_type_usages.pending_invoice_days%TYPE
327 , prior_invoice_days mtl_stat_type_usages.prior_invoice_days%TYPE
328 , returns_processing mtl_stat_type_usages.returns_processing%TYPE
329 , kit_method mtl_stat_type_usages.kit_method%TYPE
330 , include_establishments mtl_stat_type_usages.include_establishments%TYPE
331 );
332
333
334 -- Declare the REF Cursor
335
336 TYPE invCurTyp IS REF CURSOR;
337 TYPE invidCurTyp IS REF CURSOR;
338 TYPE soCurTyp IS REF CURSOR;
339 TYPE poCurTyp IS REF CURSOR;
340 TYPE rtvCurTyp IS REF CURSOR;
341 TYPE valCurTyp IS REF CURSOR;
342 stat_type_usages_rec Movement_stat_usages_rec_type;
343 TYPE setupCurTyp IS REF CURSOR;
344
345 TYPE Trans_Rec IS RECORD
346 ( movement_id NUMBER
347 , picking_line_detail_id NUMBER
348 , rcv_transaction_id NUMBER
349 , mtl_transaction_id NUMBER
350 );
351
352 TYPE Trans_List IS TABLE OF Trans_Rec
353 INDEX BY BINARY_INTEGER ;
354
355
356
357
358 --========================================================================
359 --
360 -- COMMENT : PL/SQL Table type definition. This table reference
361 -- will be used to
362 --- populate and print the exception messages
363 -- Defined by rajkrish
364 --===========================================================================
365
366 TYPE EXCP_REC IS RECORD
367 ( excp_col_name varchar2(40)
368 , excp_message_cd number) ;
369
370 TYPE EXCP_LIST IS TABLE OF EXCP_REC
371 INDEX BY BINARY_INTEGER ;
372
373
374
375 END INV_MGD_MVT_DATA_STR;