1 package wip_picking_pub as
2 /* $Header: wippckps.pls 120.1.12000000.1 2007/01/18 22:19:35 appldev ship $ */
3
4 type allocate_rec_t IS RECORD(wip_entity_id NUMBER,
5 repetitive_schedule_id NUMBER, --only populated for rep schedules
6 use_pickset_flag VARCHAR2(1),
7 project_id NUMBER,
8 task_id NUMBER,
9 bill_seq_id NUMBER, --these 2 are only populated for flow schedules if
10 bill_org_id NUMBER, --the schedule has been exploded in the bom_explosions table
11 required_date DATE,
12 alt_rtg_dsg VARCHAR2(10));
13
14 type allocate_tbl_t IS TABLE OF allocate_rec_t INDEX BY BINARY_INTEGER;
15
16 /* ER 4378835: Increased length of lot_number from 30 to 80 to support OPM Lot-model changes */
17 type allocate_comp_rec_t IS RECORD(wip_entity_id NUMBER,
18 repetitive_schedule_id NUMBER,
19 use_pickset_flag VARCHAR2(1),
20 project_id NUMBER,
21 task_id NUMBER,
22 operation_seq_num NUMBER,
23 inventory_item_id NUMBER,
24 item_name VARCHAR2(2000),
25 primary_uom_code VARCHAR2(3),
26 revision VARCHAR2(3),
27 requested_quantity NUMBER,
28 source_subinventory_code VARCHAR2(10),
29 source_locator_id NUMBER,
30 lot_number VARCHAR2(80),
31 start_serial VARCHAR2(30),
32 end_serial VARCHAR2(30));
33
34 type allocate_comp_tbl_t IS TABLE OF allocate_comp_rec_t INDEX BY BINARY_INTEGER;
35
36 /* This procedure is callback for INV to set backorder qty in WRO */
37 procedure pre_allocate_material(p_wip_entity_id in NUMBER,
38 p_operation_seq_num in NUMBER,
39 p_inventory_item_id in NUMBER,
40 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
41 p_use_pickset_flag in VARCHAR2, -- null is no,
42 p_allocate_quantity in NUMBER,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_data OUT NOCOPY VARCHAR2);
45
46 /* This procedure issues material to a job/repetitive_schedule.
47 However, it fails if the requirement does not exist!
48 Parameters:
49 p_wip_entity_id: the wip entity_id
50 p_inventory_item_id: id of the item being issued
51 p_repetitive_line_id: line id of the repetitive schedule if
52 issuing to a rep sched
53 p_transaction_id: the transaction id from mmt. Only used for
54 repetitive schedules
55 p_primary_quantity: quantity being issued, in item's primary UOM and negative
56 since it is a pull from inventory. e.g., set this to -5
57 to issue 5 to the job
58 x_return_status: FND_API return codes are used, also
59 'L' if rows are locked
60 x_msg_data: error message if error occurred. This is not put on the message
61 stack!
62 */
63 procedure issue_material(p_wip_entity_id in NUMBER,
64 p_operation_seq_num in NUMBER,
65 p_inventory_item_id in NUMBER,
66 p_repetitive_line_id in NUMBER DEFAULT NULL,
67 p_transaction_id in NUMBER DEFAULT NULL,
68 p_primary_quantity in NUMBER,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_data OUT NOCOPY VARCHAR2);
71
72
73 /* This procedure deallocates material from a job/repetitive_schedule, i.e. it
74 lowers the quantity_allocated column in wro.
75 Parameters:
76 p_wip_entity_id: the wip entity_id
77 p_operation_seq_num: op seq of item being deallocated
78 p_inventory_item_id: id of the item being issued
79 p_repetitive_schedule_id: repetitive schedule id if entity is rep sched
80 p_primary_quantity: quantity being issued, in item's primary UOM
81 x_return_status: FND_API return codes are used, also
82 'L' if rows are locked
83 x_msg_data: error message if error occurred. This is not put on the message
84 stack!
85 */
86 procedure unallocate_material(p_wip_entity_id in NUMBER,
87 p_operation_seq_num in NUMBER,
88 p_inventory_item_id in NUMBER,
89 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
90 p_primary_quantity in NUMBER,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_data OUT NOCOPY VARCHAR2);
93
94
95 /* This procedure allocates material to a job/repetitive_schedule, i.e. it
96 ups the quantity_allocated column in wro.
97 Parameters:
98 p_wip_entity_id: the wip entity_id
99 p_operation_seq_num: op seq of item being deallocated
100 p_inventory_item_id: id of the item being issued
101 p_repetitive_schedule_id: repetitive schedule id if entity is rep sched
102 p_primary_quantity: quantity being issued, in item's primary UOM
103 x_return_status: FND_API return codes are used, also
104 'L' if rows are locked
105 x_msg_data: error message if error occurred. This is not put on the message
106 stack!
107 */
108 procedure allocate_material(p_wip_entity_id in NUMBER,
109 p_operation_seq_num in NUMBER,
110 p_inventory_item_id in NUMBER,
111 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
112 p_primary_quantity in NUMBER,
113 x_quantity_allocated OUT NOCOPY NUMBER,
114 x_return_status OUT NOCOPY VARCHAR2,
115 x_msg_data OUT NOCOPY VARCHAR2);
116
117 /* This procedure cancels open allocations. It should be called when a job/schedule is cancelled to prevent addional move orders
118 or tasks from being transacted.
119 Parameters:
120 p_wip_entity_id: The wip entity id of the job/schedule
121 p_wip_entity_type: The wip entity type of the job/schedule
122 p_repetitive_schedule_id: The rep sched id of the schedule if cancelling a rep. sched.
123 x_return_status: FND_API return codes are used
124 x_msg_data: The error message. This message is not put on the stack!
125 */
126 procedure cancel_allocations(p_wip_entity_id NUMBER,
127 p_wip_entity_type NUMBER,
128 p_repetitive_schedule_id NUMBER DEFAULT NULL,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_msg_data OUT NOCOPY VARCHAR2);
131
132
133 /* This procedure called for cancellation of move orders for specific components. This procedure queries for the move order
134 lines related to the specified component of the entity and calls cancel_MO_line for each of this component and zeros the
135 backorder quantity in WRO.*/
136
137 Procedure cancel_comp_allocations(p_wip_entity_id NUMBER,
138 p_operation_seq_num NUMBER,
139 p_inventory_item_id NUMBER,
140 p_wip_entity_type NUMBER,
141 p_repetitive_schedule_id NUMBER DEFAULT NULL,
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_msg_data OUT NOCOPY VARCHAR2);
144
145 /* This procedure reduces the allocated quantity for a component
146 by the requested quantity */
147 procedure reduce_comp_allocations(p_comp_tbl IN allocate_comp_tbl_t,
148 p_wip_entity_type NUMBER,
149 p_organization_id NUMBER,
150 x_return_status OUT NOCOPY VARCHAR2,
151 x_msg_data OUT NOCOPY VARCHAR2);
152
153 /* This procedure is used in the WIPCMPPK form to allocate material.
154 Parameters:
155 p_alloc_tbl: A table of records containing the entities for which to allocate.
156 p_days_to_alloc: For rep scheds, the maximum number of days to allocate. Less may
157 be allocated if the open quantity is less.
158 p_start_date : The start date to allocate for.
159 p_cutoff_date: The last date to allocate for.
160 p_operation_seq_num_low : Starting operation seq to allocate for. Only for Discrete jobs and EAM work orders
161 p_operation_seq_num_high: Ending operation seq to allocate for. Only for Discrete jobs and EAM work orders
162 p_wip_entity_type: The wip entity type of the jobs/schedules.
163 p_organization_id: The organization id of the jobs/schedules.
164 x_return_status: FND_API return codes are used
165 x_msg_data: The error message. This message is not put on the stack!
166 */
167 procedure allocate(p_alloc_tbl IN OUT NOCOPY allocate_tbl_t,
168 p_days_to_alloc NUMBER := NULL, --only used for rep scheds
169 p_auto_detail_flag VARCHAR2 DEFAULT NULL,
170 p_start_date DATE DEFAULT NULL,
171 p_cutoff_date DATE,
172 p_operation_seq_num_low NUMBER DEFAULT NULL,
173 p_operation_seq_num_high NUMBER DEFAULT NULL,
174 p_wip_entity_type NUMBER,
175 p_organization_id NUMBER,
176 p_pick_grouping_rule_id NUMBER := NULL, /* Added as part of Enhancement#2578514*/
177 p_print_pick_slip VARCHAR2 DEFAULT NULL, /* Added as part of Enhancement#2578514*/
178 p_plan_tasks BOOLEAN DEFAULT NULL, /* Added as part of Enhancement#2578514*/
179 x_conc_req_id OUT NOCOPY NUMBER,
180 x_mo_req_number OUT NOCOPY VARCHAR2,
181 x_return_status OUT NOCOPY VARCHAR2,
182 x_msg_data OUT NOCOPY VARCHAR2);
183
184 /* This procedure is called for allocating specific components .
185 Parameters:
186 p_alloc_comp_tbl: A table of records containing the (entity,operation sequence,component) combination for which to allocate.
187 p_days_to_alloc: For rep scheds, the maximum number of days to allocate. Less may
188 be allocated if the open quantity is less.
189 p_auto_detail_flag: Indicates automatic detailing of Move Order lines to Inventory.
190 p_cutoff_date: The last date to allocate for.
191 p_wip_entity_type: The wip entity type of the jobs/schedules.
192 p_organization_id: The organization id of the jobs/schedules.
193 x_return_status: FND_API return codes are used
194 x_msg_data: The error message. This message is not put on the stack!
195 */
196
197
198 procedure allocate_comp(p_alloc_comp_tbl IN OUT NOCOPY allocate_comp_tbl_t,
199 p_days_to_alloc NUMBER DEFAULT NULL, --only used for rep scheds
200 p_auto_detail_flag VARCHAR2 DEFAULT NULL,
201 p_cutoff_date DATE,
202 p_wip_entity_type NUMBER,
203 p_organization_id NUMBER,
204 p_pick_grouping_rule_id NUMBER := NULL,
205 p_print_pick_slip VARCHAR2 DEFAULT NULL,
206 p_plan_tasks BOOLEAN DEFAULT NULL,
207 x_conc_req_id OUT NOCOPY NUMBER,
208 x_mo_req_number OUT NOCOPY VARCHAR2,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_data OUT NOCOPY VARCHAR2);
211
212 Function Quantity_Allocated(p_wip_entity_id IN NUMBER,
213 p_operation_seq_num IN NUMBER,
214 p_organization_id IN NUMBER,
215 p_inventory_item_id IN NUMBER,
216 p_repetitive_schedule_id IN NUMBER DEFAULT NULL,
217 p_quantity_issued IN NUMBER DEFAULT NULL) RETURN NUMBER;
218
219 Function Is_Component_Pick_Released(p_wip_entity_id in number,
220 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
221 p_org_id in NUMBER,
222 p_operation_seq_num in NUMBER,
223 p_inventory_item_id in NUMBER) RETURN BOOLEAN;
224
225 Function Is_Job_Pick_Released(p_wip_entity_id in number,
226 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
227 p_org_id in NUMBER) RETURN BOOLEAN;
228
229 Procedure Update_Requirement_SubinvLoc(p_wip_entity_id number,
230 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
231 p_operation_seq_num NUMBER,
232 p_supply_subinventory in VARCHAR2,
233 p_supply_locator_id in NUMBER,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_data OUT NOCOPY VARCHAR2);
236
237 Procedure Update_Component_BackOrdQty(p_wip_entity_id number,
238 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
239 p_operation_seq_num NUMBER,
240 p_new_component_qty in NUMBER,
241 p_inventory_item_id in NUMBER DEFAULT NULL,
242 x_return_status OUT NOCOPY VARCHAR2,
243 x_msg_data OUT NOCOPY VARCHAR2);
244
245 Procedure Update_Job_BackOrdQty(p_wip_entity_id number,
246 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
247 p_new_job_qty in NUMBER,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_data OUT NOCOPY VARCHAR2);
250
251 RECORDS_LOCKED EXCEPTION;
252 PRAGMA EXCEPTION_INIT (RECORDS_LOCKED, -0054);
253 end wip_picking_pub;
[Home] [Help]