1 package wip_picking_pvt as
2 /* $Header: wippckvs.pls 120.2.12010000.1 2008/07/24 05:25:07 appldev ship $ */
3
4
5 /* This procedure explodes the BOM of a flow schedule. It also purges any components that were incorrectly exploded if
6 the supply type for their assembly was sub-assy, not phantom
7 Parameters:
8 p_organization_id: The org of the bill.
9 p_bill_sequence_id: The bill sequence of the assembly
10 p_revision_date: The revision date of the assembly
11 p_primary_item_id: The item id of the assembly
12 p_alternate_bom_designator: the abm of the assembly, if it is being used
13 p_user_id: To set the standard WHO column in bom_explosions
14 x_return_status: FND_API return codes are used
15 x_msg_data: The error message. This message is not put on the stack!
16 */
17 procedure explode(p_organization_id NUMBER,
18 p_bill_sequence_id NUMBER,
19 p_revision_date DATE,
20 p_primary_item_id NUMBER,
21 p_alternate_bom_designator VARCHAR2,
22 p_user_id NUMBER,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_data OUT NOCOPY VARCHAR2);
25
26
27 /* This procedure cancels open allocations. It should be called when a job/schedule is cancelled to prevent addional move orders
28 or tasks from being transacted.
29 Parameters:
30 p_wip_entity_id: The wip entity id of the job/schedule
31 p_wip_entity_type: The wip entity type of the job/schedule
32 p_repetitive_schedule_id: The rep sched id of the schedule if cancelling a rep. sched.
33 x_return_status: FND_API return codes are used
34 x_msg_data: The error message. This message is not put on the stack!
35 */
36 procedure cancel_allocations(p_wip_entity_id NUMBER,
37 p_wip_entity_type NUMBER,
38 p_repetitive_schedule_id NUMBER DEFAULT NULL,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_data OUT NOCOPY VARCHAR2);
41
42
43 /* This procedure called for cancellation of move orders for specific components. This procedure queries for the move order
44 lines related to the specified component of the entity and calls cancel_MO_line for each of this component and zeros the
45 backorder quantity in WRO.*/
46
47 Procedure cancel_comp_allocations(p_wip_entity_id NUMBER,
48 p_operation_seq_num NUMBER,
49 p_inventory_item_id NUMBER,
50 p_wip_entity_type NUMBER,
51 p_repetitive_schedule_id NUMBER DEFAULT NULL,
52 x_return_status OUT NOCOPY VARCHAR2,
53 x_msg_data OUT NOCOPY VARCHAR2);
54
55
56 /* This procedure is called from cancel_allocations and cancel_comp_allocations.
57 It updates the allocated quantity appropriately for the received row id and calls inventory's API
58 to cancel the move order line. */
59
60 Procedure cancel_MO_line(p_lineId IN NUMBER,
61 p_rowId ROWID,
62 p_wip_entity_type NUMBER,
63 p_openQty NUMBER,
64 x_return_status OUT NOCOPY VARCHAR2,
65 x_msg_data OUT NOCOPY VARCHAR2);
66
67
68 /* This procedure updates the operation sequence number of open allocations. It should be called when a routing is added
69 to the job schedule after a pick release has occurred. In this case, all move orders w/op_seq = 1 will get updated to the
70 lowest added operation of the new routing.
71
72 Parameters:
73 p_wip_entity_id: The wip entity id of the job/schedule
74 p_operation_seq_num: The new op seq of the move orders
75 p_repetitive_schedule_id: The rep sched id of the schedule if cancelling a rep. sched.
76 x_return_status: FND_API return codes are used
77 x_msg_data: The error message. This message is not put on the stack!
78 */
79 procedure update_allocation_op_seqs(p_wip_entity_id IN NUMBER,
80 p_repetitive_schedule_id IN NUMBER := null,
81 p_operation_seq_num IN NUMBER,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_data OUT NOCOPY VARCHAR2);
84
85 /* This procedure reduces the allocated quantity for a component
86 by the requested quantity */
87 procedure reduce_comp_allocations(p_comp_tbl IN wip_picking_pub.allocate_comp_tbl_t,
88 p_wip_entity_type NUMBER,
89 p_organization_id NUMBER,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_data OUT NOCOPY VARCHAR2);
92
93
94 /* This procedure is used in the picking concurrent request.
95 Parameters:
96 errbuf: Error message returned to conc. manager.
97 retcode: return status to conc. manager. See app developer guide for meanings
98 p_wip_entity_type: The wip entity type of the job/schedule
99 p_days_forward: Number of days to allocate for (from sysdate).
100 p_organization_id: The org to allocate material for.
101 p_use_pickset_indicator: Whether to use picksets or not
102 p_days_to_alloc: For rep scheds, the maximum number of days to allocate. Less may
103 be allocated if the open quantity is less.
104 */
105 procedure allocate(errbuf OUT NOCOPY VARCHAR2,
106 retcode OUT NOCOPY NUMBER,
107 p_wip_entity_type NUMBER,
108 p_job_type NUMBER DEFAULT 4, /*Bug 5932126 (FP of 5880558): Added one new parameter for job type*/
109 p_days_forward NUMBER,
110 p_organization_id NUMBER,
111 p_use_pickset_indicator NUMBER,
112 p_pick_grouping_rule_id NUMBER := NULL,
113 p_print_pickslips NUMBER DEFAULT NULL, -- lookup code is 1 for default value YES
114 p_plan_tasks NUMBER DEFAULT NULL, -- lookup code is 2 for default value NO
115 p_days_to_alloc NUMBER DEFAULT NULL); --only used for rep scheds
116
117 /* This procedure is used in the WIPCMPPK form to allocate material.
118 Parameters:
119 p_alloc_tbl: A table of records containing the entities for which to allocate.
120 p_days_to_alloc: For rep scheds, the maximum number of days to allocate. Less may
121 be allocated if the open quantity is less.
122 p_cutoff_date: The last date to allocate for.
123 p_wip_entity_type: The wip entity type of the jobs/schedules.
124 p_organization_id: The organization id of the jobs/schedules.
125 x_return_status: FND_API return codes are used
126 x_msg_data: The error message. This message is not put on the stack!
127 */
128 procedure allocate(p_alloc_tbl IN OUT NOCOPY wip_picking_pub.allocate_tbl_t,
129 p_days_to_alloc NUMBER := NULL, --only used for rep scheds
130 p_auto_detail_flag VARCHAR2 DEFAULT NULL,
131 p_start_date DATE DEFAULT NULL, /* Enh#2824753 */
132 p_cutoff_date DATE,
133 p_wip_entity_type NUMBER,
134 p_organization_id NUMBER,
135 p_operation_seq_num_low NUMBER := NULL, /* Enh#2824753 */
136 p_operation_seq_num_high NUMBER := NULL,
137 p_pick_grouping_rule_id NUMBER := NULL, /* Added as part of Enhancement#2578514*/
138 p_print_pick_slip VARCHAR2 DEFAULT NULL, /* Added as part of Enhancement#2578514*/
139 p_plan_tasks BOOLEAN DEFAULT NULL, /* Added as part of Enhancement#2578514*/
140 x_conc_req_id OUT NOCOPY NUMBER,
141 x_mo_req_number OUT NOCOPY VARCHAR2,
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_msg_data OUT NOCOPY VARCHAR2);
144
145 /* This procedure is called by allocate and allocate_comp to get the MO header and lines records appropriately filled in. */
146
147 procedure get_HdrLinesRec( p_wip_entity_id NUMBER,
148 p_project_id NUMBER,
149 p_task_id NUMBER,
150 p_wip_entity_type NUMBER,
151 p_repetitive_schedule_id NUMBER,
152 p_operation_seq_num NUMBER,
153 p_inventory_item_id NUMBER,
154 p_use_pickset_flag VARCHAR2,
155 p_pickset_id NUMBER,
156 p_open_qty NUMBER,
157 p_to_subinv VARCHAR2,
158 p_to_locator NUMBER,
159 p_default_subinv VARCHAR2,
160 p_default_locator NUMBER,
161 p_uom VARCHAR2 ,
162 p_supply_type NUMBER ,
163 p_req_date DATE,
164 p_rev_control_code VARCHAR2 ,
165 p_organization_id NUMBER,
166 p_pick_grouping_rule_id NUMBER := NULL, /* Added as part of Enhancement#2578514*/
167 p_carton_grouping_id NUMBER := NULL, /* Added as part of Enhancement#2578514*/
168 p_hdrRec IN OUT NOCOPY INV_MOVE_ORDER_PUB.Trohdr_Rec_Type,
169 p_linesRec IN OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Rec_Type,
170 x_return_status OUT NOCOPY VARCHAR2,
171 x_msg_data OUT NOCOPY VARCHAR2);
172
173 /* This procedure is called for allocating specific components .
174 Parameters:
175 p_alloc_comp_tbl: A table of records containing the (entity,operation sequence,component) combination for which to allocate.
176 p_days_to_alloc: For rep scheds, the maximum number of days to allocate. Less may
177 be allocated if the open quantity is less.
178 p_auto_detail_flag: Indicates automatic detailing of Move Order lines to Inventory.
179 p_cutoff_date: The last date to allocate for.
180 p_wip_entity_type: The wip entity type of the jobs/schedules.
181 p_organization_id: The organization id of the jobs/schedules.
182 x_return_status: FND_API return codes are used
183 x_msg_data: The error message. This message is not put on the stack!
184 */
185
186
187 procedure allocate_comp(p_alloc_comp_tbl IN OUT NOCOPY wip_picking_pub.allocate_comp_tbl_t,
188 p_days_to_alloc NUMBER DEFAULT NULL, --only used for rep scheds
189 p_auto_detail_flag VARCHAR2 DEFAULT NULL,
190 p_cutoff_date DATE,
191 p_wip_entity_type NUMBER,
192 p_organization_id NUMBER,
193 p_pick_grouping_rule_id NUMBER := NULL,
194 p_print_pick_slip VARCHAR2 DEFAULT NULL,
195 p_plan_tasks BOOLEAN DEFAULT NULL,
196 x_conc_req_id OUT NOCOPY NUMBER,
197 x_mo_req_number OUT NOCOPY VARCHAR2,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_data OUT NOCOPY VARCHAR2);
200
201 /**
202 * Explodes an item's bom and returns the components in a pl/sql table
203 * p_organization_id The organization.
204 * p_assembly_item_id The assembly.
205 * p_alt_option 2 if an exact match to the alternate bom designator is necessary
206 * 1 if the alternate is not found, the main bom will be used.
207 * p_assembly_qty Qty to explode. Pass a negative value for returns.
208 * p_alt_bom_desig The alternate bom designator if one was provided. Null otherwise.
209 * p_rev_date The date of the transaction. This is used to retrieve the correct bom.
210 */
211 procedure explodeMultiLevel(p_organization_id NUMBER,
212 p_assembly_item_id NUMBER,
213 p_alt_option NUMBER,
214 p_assembly_qty NUMBER,
215 p_alt_bom_desig VARCHAR2,
216 p_rev_date DATE,
217 p_project_id NUMBER,
218 p_task_id NUMBER,
219 p_to_op_seq_num NUMBER,
220 p_alt_rout_desig VARCHAR2,
221 x_comp_sql_tbl OUT NOCOPY wip_picking_pub.allocate_comp_tbl_t,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_data OUT NOCOPY VARCHAR2 );
224
225 Procedure Post_Explosion_CleanUp(p_wip_entity_id number,
226 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
227 p_org_id in NUMBER,
228 x_return_status OUT NOCOPY VARCHAR2,
229 x_msg_data OUT NOCOPY VARCHAR2 );
230
231 RECORDS_LOCKED EXCEPTION;
232 PRAGMA EXCEPTION_INIT (RECORDS_LOCKED, -0054);
233 g_PickRelease_Failed BOOLEAN := FALSE; /* used to set the request to warning */
234 end wip_picking_pvt;
[Home] [Help]