1 PACKAGE WIP_OSP AS
2 /* $Header: wipospvs.pls 120.3.12010000.3 2008/09/18 06:57:02 tbhande ship $ */
3
4 /* RELEASE_VALIDATION
5
6 This routine checks if there are outside processing resources at
7 the first operation of the routing for the job or schedule identified
8 by the parameters. If so, it selects that operation_seq_num and
9 the outside processing account of the job or schedule, and calls
10 the CREATE_REQUISITION routine.
11 */
12
13 PROCEDURE RELEASE_VALIDATION
14 (P_Wip_Entity_Id NUMBER,
15 P_Organization_id NUMBER,
16 P_Repetitive_Schedule_Id NUMBER);
17
18 /* CREATE_REQUISITION
19
20 This routine does validation to make sure that outside processing
21 is allowed at the operation specified in P_Operation_Seq_Num.
22 Specifically, it raises an error if the User Id running the process
23 is not an employee or if the outside processing operation department
24 has no location specified.
25
26 If no error is raised, then a record is inserted into
27 PO_REQUISITIONS_INTERFACE.
28
29 Arguments:
30 P_Wip_Entity_Id, These identify the job or schedule
31 P_Organization_Id,
32 P_Repetitive_Schedule_Id
33
34 P_Operation_Seq_Num The operation that we are moving
35 to (or the first op for Release)
36
37 P_Outside_Proc_Acct Account of the job/schedule, needed
38 to create the interface record.
39
40 P_Line_Id Line attached to the Repetitive Schedule
41 This is null for Discrete Jobs
42 */
43
44 PROCEDURE CREATE_REQUISITION(
45 P_Wip_Entity_Id NUMBER,
46 P_Organization_Id NUMBER,
47 P_Repetitive_Schedule_Id NUMBER,
48 P_Operation_Seq_Num NUMBER,
49 P_Resource_Seq_Num IN NUMBER DEFAULT NULL,
50 P_Run_ReqImport IN NUMBER DEFAULT WIP_CONSTANTS.NO);
51
52 /* CREATE_ADDITIONAL_REQ
53 Create Additional PO requisitions
54
55 This Procedure is called only when the job quantity is increased.
56 This procedure sets the P_added_quantity to the global variable
57 additional_quantity. If the additional_quantity value is > 0 then
58 an addtional requisition is created for the increased job quantity */
59
60 PROCEDURE CREATE_ADDITIONAL_REQ
61 (P_Wip_Entity_Id NUMBER,
62 P_Organization_id NUMBER,
63 P_Repetitive_Schedule_Id NUMBER,
64 P_Added_Quantity NUMBER,
65 P_Op_Seq NUMBER default null);
66
67 FUNCTION PO_REQ_EXISTS ( p_wip_entity_id in NUMBER
68 ,p_rep_sched_id in NUMBER
69 ,p_organization_id in NUMBER
70 ,p_op_seq_num in NUMBER default NULL
71 ,p_entity_type in NUMBER
72 ) RETURN BOOLEAN;
73
74
75 /* ConvertToPrimaryMoveQty
76
77 Converts the quantity that is put on the purchase order to the
78 PRIMARY quantity to be moved for the assembly being built.
79
80 Arguments:
81 p_item_id : the OSP item that is put on the purchase order
82 p_organization_id :
83 p_quantity : the quantity that is on the purchase order
84 p_uom_code : the unit of measure that is on the purchase order
85 p_usage_rate_or_amount : the usage_rate_or_amount for the resource that
86 the OSP item is attached to; this is required when
87 the OSP item is of type RESOURCE
88 */
89
90
91 FUNCTION ConvertToPrimaryMoveQty (p_item_id NUMBER,
92 p_organization_id NUMBER,
93 p_quantity NUMBER,
94 p_uom_code VARCHAR2,
95 p_primary_uom_code VARCHAR2,
96 p_usage_rate_or_amount NUMBER) RETURN NUMBER;
97 PRAGMA RESTRICT_REFERENCES(ConvertToPrimaryMoveQty,WNDS,WNPS);
98
99
100 FUNCTION IS_ORDER_OPEN (
101 approved_flag in VARCHAR2 DEFAULT NULL,
102 closed_code in VARCHAR2 DEFAULT NULL,
103 line_closed_status in VARCHAR2 DEFAULT NULL,
104 cancel_flag in VARCHAR2 DEFAULT NULL,
105 frozen_flag in VARCHAR2 DEFAULT NULL,
106 user_hold_flag in VARCHAR2 DEFAULT NULL,
107 line_expiration_date in DATE DEFAULT NULL,
108 line_cancel_flag in VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
109 PRAGMA RESTRICT_REFERENCES(IS_ORDER_OPEN,WNDS,WNPS);
110
111
112 PROCEDURE ARE_QA_PLANS_AVAILABLE(
113 P_AssemblyItemNumber IN VARCHAR2 DEFAULT NULL,
114 P_VendorName IN VARCHAR2 DEFAULT NULL,
115 P_WipEntityName IN VARCHAR2 DEFAULT NULL,
116 P_BasePoNum IN VARCHAR2 DEFAULT NULL,
117 P_SupplierItemNumber IN VARCHAR2 DEFAULT NULL,
118 P_AssemblyPrimaryUom IN VARCHAR2 DEFAULT NULL,
119 P_Uom IN VARCHAR2 DEFAULT NULL,
120 P_WipLineCode IN VARCHAR2 DEFAULT NULL,
121 P_BomRevision IN VARCHAR2 DEFAULT NULL,
122 P_StartDate IN DATE DEFAULT NULL,
123 P_PoReleaseNumber IN NUMBER DEFAULT NULL,
124 P_OrganizationId IN NUMBER DEFAULT NULL,
125 P_WipEntityType IN NUMBER DEFAULT NULL,
126 P_WipEntityId IN NUMBER DEFAULT NULL,
127 P_WipRepetitiveScheduleId IN NUMBER DEFAULT NULL,
128 P_ResourceSeqNum IN NUMBER DEFAULT NULL,
129 P_ItemId IN NUMBER DEFAULT NULL,
130 P_AssemblyItemId IN NUMBER DEFAULT NULL,
131 P_WipOperationSeqNum IN NUMBER DEFAULT NULL,
132 R_QaAvailable OUT NOCOPY VARCHAR2);
133
134 /**
135 * This function validates the from op and to op for the user relating to
136 * OSP operation steps. The follow rules apply to OSP:
137 * - Users cannot move into a Queue of an OSP operation unless that
138 * department has a location setup.
139 * - Users cannot move forward into a queue of an operation that has
140 * PO resource unless the user is an employee
141 * The error message for the first case would be WIP_PO_MOVE_LOCATION and
142 * WIP_VALID_EMPLOYEE for the second case.
143 * Parameters:
144 * p_orgID The organization identifier.
145 * p_wipEntityID The wip entity identifier.
146 * p_lineID The line id used only for repetitive schedule. For
147 * discrete and lotbased, do not need to pass this value.
148 * p_entityType The wip entity type. (usually discrete)
149 * p_fmOpSeqNum The from operation sequence number that user is moving.
150 * p_toOpSeqNum The to operation sequence number that user is moving.
151 * p_toStep The to intraoperation step that user is moving to.
152 * p_userID The user identifier.
153 * x_msg The error message name before translation
154 * x_error The error message after translation for displaying
155 * to user.
156 * Return:
157 * boolean A flag indicating whether update successful or not.
158 */
159 FUNCTION checkOSP(p_orgID NUMBER,
160 p_wipEntityID NUMBER,
161 p_lineID NUMBER := NULL,
162 p_entityType NUMBER,
163 p_fmOpSeqNum NUMBER,
164 p_toOpSeqNum NUMBER,
165 p_toStep NUMBER,
166 p_userID NUMBER,
167 x_msg OUT NOCOPY VARCHAR2,
168 x_error OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
169
170 /**
171 * This procedure will pickup all PO/requisitions associated to the jobs
172 * based on the criteria used provided, then call updatePOReqNBD procedure
173 * to update PO/requisition need-by date. This procedure will be called from
174 * Update PO need-by date concurrent program.
175 *
176 * Parameters:
177 * errbuf Error messages that will be displayed in concurrent
178 * program log file
179 * retcode Return status of this procedure
180 * 0 - success
181 * 1 - warning
182 * 2 - error
183 * p_project_id Project that user want to update PO/requisition NBD
184 * p_task_id Task that user want to update PO/requisition NBD
185 * p_days_forward_fm Number of days forward that user want us to start
186 * updating PO/requisition NBD
187 * p_days_forward_to Number of days forward that user want us to stop
188 * updating PO/requisition NBD
189 * p_org_id Organization identifier.
190 * p_entity_type The wip entity type.
191 *
192 */
193 PROCEDURE updatePOReqNBDManager(errbuf OUT NOCOPY VARCHAR2,
194 retcode OUT NOCOPY NUMBER,
195 p_project_id IN NUMBER,
196 p_task_id IN NUMBER,
197 p_days_forward_fm IN NUMBER,
198 p_days_forward_to IN NUMBER,
199 p_org_id IN NUMBER,
200 p_entity_type IN NUMBER);
201
202 /**
203 * This procedure will update PO/requisition need-by date.
204 *
205 * Parameters:
206 * p_po_header_id PO header ID (Standard PO)
207 * p_po_release_id Release ID (Blanket)
208 * p_po_line_location_id Line location ID (shipment level)
209 * p_req_header_id Requisition header ID (requisition)
210 * p_req_line_id Requisition line ID (requisition)
211 * p_po_req_type Type of document('STANDARD','BLANKET',
212 * 'REQUISITION')
213 * p_approval_status Document approval status
214 * p_new_NBD new need-by date
215 * x_return_status There are 2 possible values
216 * *fnd_api.g_ret_sts_success*
217 * means all po/requisition get updated successfully
218 * *fnd_api.g_ret_sts_unexp_error*
219 * means an exception occurred
220 *
221 */
222 PROCEDURE updatePOReqNBD(p_po_header_id IN NUMBER,
223 p_po_release_id IN NUMBER,
224 p_po_line_location_id IN NUMBER,
225 p_req_header_id IN NUMBER,
226 p_req_line_id IN NUMBER,
227 p_po_req_type IN VARCHAR2,
228 p_approval_status IN VARCHAR2,
229 p_new_NBD IN DATE,
230 p_ou_id IN NUMBER,
231 x_return_status OUT NOCOPY VARCHAR2);
232
233
234 /**
235 * This procedure will update PO/requisition quantity..
236 *
237 * Parameters:
238 * p_job_id WIP_ENTITY_ID
239 * p_repetitive_id REPETITIVE_SCHEDULE_ID
240 * p_org_id ORGANIZATION_ID
241 * p_changed_qty Quantity of assembly that need to be changed, not PO
242 * quantity. Pass positive for increase, and negative
243 * for decrease case. For example, if user change job
244 * quantity from 10 to 12, pass 2 as p_changed_qty.
245 * p_fm_op We will update PO/requisition associated to the
246 * operation greater than p_fm_op.
247 * quantity. This parameter is useful for scrap, unscrap
248 * and overmove case. We should not update all PO/req
249 * associated to the job. Instead, we should update only
250 * the one affected by these transactions
251 * p_is_scrap_txn Added for bug 4734309: Pass WIP_CONSTANTS.YES when
252 * calling from scrap processor. For scrap transactions, only PO/REQs
253 * connected to future operations will be affected.
254 * x_return_status There are 2 possible values
255 * *fnd_api.g_ret_sts_success*
256 * means all po/requisition get updated successfully
257 * *fnd_api.g_ret_sts_unexp_error*
258 * means an exception occurred
259 *
260 */
261 PROCEDURE updatePOReqQuantity(p_job_id IN NUMBER,
262 p_repetitive_id IN NUMBER := NULL,
263 p_org_id IN NUMBER,
264 p_changed_qty IN NUMBER,
265 p_fm_op IN NUMBER,
266 p_is_scrap_txn IN NUMBER := NULL,
267 x_return_status OUT NOCOPY VARCHAR2);
268
269 /**
270 * This procedure will update PO/requisition quantity..
271 *
272 * Parameters:
273 * p_job_id WIP_ENTITY_ID
274 * p_repetitive_id REPETITIVE_SCHEDULE_ID
275 * p_org_id ORGANIZATION_ID
276 * p_op_seq_num Operation that we have to cancel PO/requisition.
277 * This parameter is useful if user want to cancel only
278 * PO/requisition for a specific operation.
279 * x_return_status There are 2 possible values
280 * *fnd_api.g_ret_sts_success*
281 * means all po/requisition get updated successfully
282 * *fnd_api.g_ret_sts_unexp_error*
283 * means an exception occurred
284 *
285 */
286 PROCEDURE cancelPOReq(p_job_id IN NUMBER,
287 p_repetitive_id IN NUMBER := NULL,
288 p_org_id IN NUMBER,
289 p_op_seq_num IN NUMBER := NULL,
290 x_return_status OUT NOCOPY VARCHAR2,
291 p_clr_fnd_mes_flag IN VARCHAR2 DEFAULT 'N'); -- added parameter p_clr_fnd_mes_flag for
292 -- bugfix 7415801
293
294
295 /* Fix for bug 4446607: This function returns TRUE if a PO/REQ is ever
296 * created for this particular job/operation, irrespective of whether the
297 * PO/REQ is cancelled or closed. This will be used to determine whether
298 * to call release_validation when rescheduling the job through mass-load.
299 * We had been using PO_REQ_EXISTS but that would return FALSE if either
300 * the PO/REQ is cancelled or if all the quantity is received for the PO.
301 * Because of this, requisition creation was erroneously triggered when
302 * rescheduling a job, whose associated PO has been received in total.
303 */
304 FUNCTION PO_REQ_created ( p_wip_entity_id in NUMBER
305 ,p_rep_sched_id in NUMBER
306 ,p_organization_id in NUMBER
307 ,p_op_seq_num in NUMBER default NULL
308 ,p_entity_type in NUMBER
309 ) RETURN BOOLEAN;
310
311 END WIP_OSP;
[Home] [Help]