[Home] [Help]
PACKAGE: APPS.INV_MO_LINE_DETAIL_UTIL
Source
1 PACKAGE inv_mo_line_detail_util AS
2 /* $Header: INVUTLDS.pls 120.1 2005/10/02 14:15:04 dherring noship $ */
3 --
4 TYPE g_mmtt_rec IS RECORD(
5 transaction_header_id NUMBER
6 , transaction_temp_id NUMBER
7 , source_code VARCHAR2(30) := fnd_api.g_miss_char
8 , source_line_id NUMBER
9 , transaction_mode NUMBER
10 , lock_flag VARCHAR2(1) := fnd_api.g_miss_char
11 , last_update_date DATE := fnd_api.g_miss_date
12 , last_updated_by NUMBER
13 , creation_date DATE := fnd_api.g_miss_date
14 , created_by NUMBER
15 , last_update_login NUMBER
16 , request_id NUMBER
17 , program_application_id NUMBER
18 , program_id NUMBER
19 , program_update_date DATE := fnd_api.g_miss_date
20 , inventory_item_id NUMBER
21 , revision VARCHAR2(3) := fnd_api.g_miss_char
22 , organization_id NUMBER
23 , subinventory_code VARCHAR2(10) := fnd_api.g_miss_char
24 , locator_id NUMBER
25 , transaction_quantity NUMBER
26 , primary_quantity NUMBER
27 , transaction_uom VARCHAR2(3) := fnd_api.g_miss_char
28 , transaction_cost NUMBER
29 , transaction_type_id NUMBER
30 , transaction_action_id NUMBER
31 , transaction_source_type_id NUMBER
32 , transaction_source_id NUMBER
33 , transaction_source_name VARCHAR2(80) := fnd_api.g_miss_char
34 -- updated size of varchar from 30 to 80 for bug 4614163
35 , transaction_date DATE := fnd_api.g_miss_date
36 , acct_period_id NUMBER
37 , distribution_account_id NUMBER
38 , transaction_reference VARCHAR2(240) := fnd_api.g_miss_char
39 , requisition_line_id NUMBER
40 , requisition_distribution_id NUMBER
41 , reason_id NUMBER
42 , lot_number VARCHAR2(80) := fnd_api.g_miss_char
43 , lot_expiration_date DATE := fnd_api.g_miss_date
44 , serial_number VARCHAR2(30) := fnd_api.g_miss_char
45 , receiving_document VARCHAR2(10) := fnd_api.g_miss_char
46 , demand_id NUMBER
47 , rcv_transaction_id NUMBER
48 , move_transaction_id NUMBER
49 , completion_transaction_id NUMBER
50 , wip_entity_type NUMBER
51 , schedule_id NUMBER
52 , repetitive_line_id NUMBER
53 , employee_code VARCHAR2(10) := fnd_api.g_miss_char
54 , primary_switch NUMBER
55 , schedule_update_code NUMBER
56 , setup_teardown_code NUMBER
57 , item_ordering NUMBER
58 , negative_req_flag NUMBER
59 , operation_seq_num NUMBER
60 , picking_line_id NUMBER
61 , trx_source_line_id NUMBER
62 , trx_source_delivery_id NUMBER
63 , physical_adjustment_id NUMBER
64 , cycle_count_id NUMBER
65 , rma_line_id NUMBER
66 , customer_ship_id NUMBER
67 , currency_code VARCHAR2(10) := fnd_api.g_miss_char
68 , currency_conversion_rate NUMBER
69 , currency_conversion_type VARCHAR2(30) := fnd_api.g_miss_char
70 , currency_conversion_date DATE := fnd_api.g_miss_date
71 , ussgl_transaction_code VARCHAR2(30) := fnd_api.g_miss_char
72 , vendor_lot_number VARCHAR2(80) := fnd_api.g_miss_char
73 , encumbrance_account NUMBER
74 , encumbrance_amount NUMBER
75 , ship_to_location NUMBER
76 , shipment_number VARCHAR2(30) := fnd_api.g_miss_char
77 , transfer_cost NUMBER
78 , transportation_cost NUMBER
79 , transportation_account NUMBER
80 , freight_code VARCHAR2(25) := fnd_api.g_miss_char
81 , containers NUMBER
82 , waybill_airbill VARCHAR2(20) := fnd_api.g_miss_char
83 , expected_arrival_date DATE := fnd_api.g_miss_date
84 , transfer_subinventory VARCHAR2(10) := fnd_api.g_miss_char
85 , transfer_organization NUMBER
86 , transfer_to_location NUMBER
87 , new_average_cost NUMBER
88 , value_change NUMBER
89 , percentage_change NUMBER
90 , material_allocation_temp_id NUMBER
91 , demand_source_header_id NUMBER
92 , demand_source_line VARCHAR2(30) := fnd_api.g_miss_char
93 , demand_source_delivery VARCHAR2(30) := fnd_api.g_miss_char
94 , item_segments VARCHAR2(240) := fnd_api.g_miss_char
95 , item_description VARCHAR2(240) := fnd_api.g_miss_char
96 , item_trx_enabled_flag VARCHAR2(1) := fnd_api.g_miss_char
97 , item_location_control_code NUMBER
98 , item_restrict_subinv_code NUMBER
99 , item_restrict_locators_code NUMBER
100 , item_revision_qty_control_code NUMBER
101 , item_primary_uom_code VARCHAR2(3) := fnd_api.g_miss_char
102 , item_uom_class VARCHAR2(10) := fnd_api.g_miss_char
103 , item_shelf_life_code NUMBER
104 , item_shelf_life_days NUMBER
105 , item_lot_control_code NUMBER
106 , item_serial_control_code NUMBER
107 , item_inventory_asset_flag VARCHAR2(1) := fnd_api.g_miss_char
108 , allowed_units_lookup_code NUMBER
109 , department_id NUMBER
110 , department_code VARCHAR2(10) := fnd_api.g_miss_char
111 , wip_supply_type NUMBER
112 , supply_subinventory VARCHAR2(10) := fnd_api.g_miss_char
113 , supply_locator_id NUMBER
114 , valid_subinventory_flag VARCHAR2(1) := fnd_api.g_miss_char
115 , valid_locator_flag VARCHAR2(1) := fnd_api.g_miss_char
116 , locator_segments VARCHAR2(240) := fnd_api.g_miss_char
117 , current_locator_control_code NUMBER
118 , number_of_lots_entered NUMBER
119 , wip_commit_flag VARCHAR2(1) := fnd_api.g_miss_char
120 , next_lot_number VARCHAR2(80) := fnd_api.g_miss_char
121 , lot_alpha_prefix VARCHAR2(30) := fnd_api.g_miss_char
122 , next_serial_number VARCHAR2(30) := fnd_api.g_miss_char
123 , serial_alpha_prefix VARCHAR2(30) := fnd_api.g_miss_char
124 , shippable_flag VARCHAR2(1) := fnd_api.g_miss_char
125 , posting_flag VARCHAR2(1) := fnd_api.g_miss_char
126 , required_flag VARCHAR2(1) := fnd_api.g_miss_char
127 , process_flag VARCHAR2(1) := fnd_api.g_miss_char
128 , ERROR_CODE VARCHAR2(240) := fnd_api.g_miss_char
129 , error_explanation VARCHAR2(240) := fnd_api.g_miss_char
130 , attribute_category VARCHAR2(30) := fnd_api.g_miss_char
131 , attribute1 VARCHAR2(150) := fnd_api.g_miss_char
132 , attribute2 VARCHAR2(150) := fnd_api.g_miss_char
133 , attribute3 VARCHAR2(150) := fnd_api.g_miss_char
134 , attribute4 VARCHAR2(150) := fnd_api.g_miss_char
135 , attribute5 VARCHAR2(150) := fnd_api.g_miss_char
136 , attribute6 VARCHAR2(150) := fnd_api.g_miss_char
137 , attribute7 VARCHAR2(150) := fnd_api.g_miss_char
138 , attribute8 VARCHAR2(150) := fnd_api.g_miss_char
139 , attribute9 VARCHAR2(150) := fnd_api.g_miss_char
140 , attribute10 VARCHAR2(150) := fnd_api.g_miss_char
141 , attribute11 VARCHAR2(150) := fnd_api.g_miss_char
142 , attribute12 VARCHAR2(150) := fnd_api.g_miss_char
143 , attribute13 VARCHAR2(150) := fnd_api.g_miss_char
144 , attribute14 VARCHAR2(150) := fnd_api.g_miss_char
145 , attribute15 VARCHAR2(150) := fnd_api.g_miss_char
146 , movement_id NUMBER
147 , reservation_quantity NUMBER
148 , shipped_quantity NUMBER
149 , transaction_line_number NUMBER
150 , task_id NUMBER
151 , to_task_id NUMBER
152 , source_task_id NUMBER
153 , project_id NUMBER
154 , source_project_id NUMBER
155 , pa_expenditure_org_id NUMBER
156 , to_project_id NUMBER
157 , expenditure_type VARCHAR2(30) := fnd_api.g_miss_char
158 , final_completion_flag VARCHAR2(1) := fnd_api.g_miss_char
159 , transfer_percentage NUMBER
160 , transaction_sequence_id NUMBER
161 , material_account NUMBER
162 , material_overhead_account NUMBER
163 , resource_account NUMBER
164 , outside_processing_account NUMBER
165 , overhead_account NUMBER
166 , flow_schedule VARCHAR2(1) := fnd_api.g_miss_char
167 , cost_group_id NUMBER := fnd_api.g_miss_num
168 , demand_class VARCHAR2(30) := fnd_api.g_miss_char
169 , qa_collection_id NUMBER
170 , kanban_card_id NUMBER
171 , overcompletion_transaction_id NUMBER
172 , overcompletion_primary_qty NUMBER
173 , overcompletion_transaction_qty NUMBER
174 , end_item_unit_number VARCHAR2(60) := fnd_api.g_miss_char
175 , scheduled_payback_date DATE := fnd_api.g_miss_date
176 , line_type_code NUMBER
177 , parent_transaction_temp_id NUMBER
178 , put_away_strategy_id NUMBER
179 , put_away_rule_id NUMBER
180 , pick_strategy_id NUMBER
181 , pick_rule_id NUMBER
182 , common_bom_seq_id NUMBER
183 , common_routing_seq_id NUMBER
184 , cost_type_id NUMBER
185 , org_cost_group_id NUMBER
186 , move_order_line_id NUMBER
187 , task_group_id NUMBER
188 , pick_slip_number NUMBER
189 , reservation_id NUMBER
190 , transaction_status NUMBER
191 , transfer_cost_group_id NUMBER := fnd_api.g_miss_num
192 , lpn_id NUMBER
193 , transfer_lpn_id NUMBER
194 , pick_slip_date DATE
195 , content_lpn_id NUMBER
196 , secondary_transaction_quantity NUMBER --INVCONV
197 , secondary_uom_code VARCHAR2(3) --INVCONV
198 );
199
200 --TYPE g_mmtt_rec IS mtl_material_transactions_temp%ROWTYPE;
201
202 TYPE g_mmtt_tbl_type IS TABLE OF g_mmtt_rec
203 INDEX BY BINARY_INTEGER;
204
205 TYPE g_update_qty_rec IS RECORD(
206 inventory_item_id NUMBER := fnd_api.g_miss_num
207 , revision VARCHAR2(3) := fnd_api.g_miss_char
208 , organization_id NUMBER := fnd_api.g_miss_num
209 , subinventory_code VARCHAR2(10) := fnd_api.g_miss_char
210 , locator_id NUMBER := fnd_api.g_miss_num
211 , transaction_quantity NUMBER := fnd_api.g_miss_num
212 , transaction_uom VARCHAR2(3) := fnd_api.g_miss_char
213 , secondary_transaction_quantity NUMBER := fnd_api.g_miss_num --INVCONV
214 , secondary_uom_code VARCHAR2(3) := fnd_api.g_miss_char --INVCONV
215 , lot_number VARCHAR2(80) := fnd_api.g_miss_char
216 , serial_number VARCHAR2(30) := fnd_api.g_miss_char
217 );
218
219 TYPE g_update_qty_tbl_type IS TABLE OF g_update_qty_rec
220 INDEX BY BINARY_INTEGER;
221
222 -- Procedure Update_Row
223 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_mo_line_detail_rec IN g_mmtt_rec);
224
225 -- Procedure Insert_Row
226 PROCEDURE insert_row(x_return_status OUT NOCOPY VARCHAR2, p_mo_line_detail_rec IN g_mmtt_rec);
227
228 -- Procedure Delete_Row
229 PROCEDURE delete_row(x_return_status OUT NOCOPY VARCHAR2, p_line_id IN NUMBER, p_line_detail_id IN NUMBER);
230
231 -- Procedure lock_Row
232 PROCEDURE lock_row(
233 x_return_status OUT NOCOPY VARCHAR2
234 , p_mo_line_detail_rec IN g_mmtt_rec
235 , x_mo_line_detail_rec OUT NOCOPY g_mmtt_rec
236 );
237
238 -- Function Query_Row
239 FUNCTION query_row(p_line_detail_id IN NUMBER)
240 RETURN g_mmtt_rec;
241
242 -- Function Query_Rows
243 FUNCTION query_rows(p_line_id IN NUMBER := fnd_api.g_miss_num, p_line_detail_id IN NUMBER := fnd_api.g_miss_num)
244 RETURN g_mmtt_tbl_type;
245
246
247 PROCEDURE update_quantity_allocations(
248 p_move_order_line_id IN NUMBER
249 , p_mold_table IN inv_mo_line_detail_util.g_update_qty_tbl_type
250 , x_mold_table OUT NOCOPY inv_mo_line_detail_util.g_mmtt_tbl_type
251 , x_return_status OUT NOCOPY VARCHAR2
252 , x_msg_count OUT NOCOPY NUMBER
253 , x_msg_data OUT NOCOPY VARCHAR2
254 );
255
256 PROCEDURE reduce_allocation_quantity(
257 x_return_status OUT NOCOPY VARCHAR2
258 , p_transaction_temp_id IN NUMBER
259 , p_quantity IN NUMBER
260 , p_secondary_quantity IN NUMBER --INVCONV
261 );
262
263 /**
264 * Deletes the Allocations existing for a Move Order Line.
265 * <p>
266 * Deletes the Allocations existing for a Move Order Line. If Move Order Line ID
267 * is passed, then all the allocations for that line are deleted. If Transaction
268 * Temp ID is passed, then that allocation alone is deleted. <br>
269 * Either Move Order Line ID or Transaction Temp ID has to be passed for the API
270 * to proceed further.
271 * <p>
272 * @param x_return_status Return Status
273 * @param x_msg_count Count of the Messages in the Message Stack
274 * @param x_msg_data Message if the Count is 1
275 * @param p_mo_line_id Move Order Line ID
276 * @param p_transaction_temp_id Transaction Temp ID
277 * <p>
278 * @author Venkatesh (venjayar)
279 */
280 PROCEDURE delete_allocations(
281 x_return_status OUT NOCOPY VARCHAR2
282 , x_msg_data OUT NOCOPY VARCHAR2
283 , x_msg_count OUT NOCOPY NUMBER
284 , p_mo_line_id IN NUMBER DEFAULT NULL
285 , p_transaction_temp_id IN NUMBER DEFAULT NULL
286 );
287
288 /**
289 * Checks the correctness of the allocations for the Move Order Line.
290 * <p>
291 * Checks whether the Move Order Line is detailed and if detailed whether the
292 * allocations are in proper shape. For a Lot Controlled Item, the sum of Lot Qty
293 * allocated assigned to the allocation should match the Transaction Qty of the
294 * allocation. Similarly for Serial Controlled and Lot and Serial Controlled Item.
295 * <p>
296 * @param x_return_status Return Status
297 * @param x_msg_count Count of the Messages in the Message Stack
298 * @param x_msg_data Message if the Count is 1
299 * @param p_move_order_line_id Move Order Line ID to be checked
300 * <p>
301 * @author Venkatesh (venjayar)
302 */
303 PROCEDURE is_line_detailed(
304 x_return_status OUT NOCOPY VARCHAR2
305 , x_msg_count OUT NOCOPY NUMBER
306 , x_msg_data OUT NOCOPY VARCHAR2
307 , p_move_order_line_id IN NUMBER
308 );
309 END inv_mo_line_detail_util;