[Home] [Help]
PACKAGE: APPS.PO_REQS_SV
Source
1 PACKAGE PO_REQS_SV as
2 /* $Header: POXRQR1S.pls 120.0 2005/06/01 17:43:01 appldev noship $ */
3 /*===========================================================================
4 PACKAGE NAME: po_reqs_sv
5
6 DESCRIPTION: Contains all server side procedures that access the
7 requisitions entity
8
9 CLIENT/SERVER: Server
10
11 LIBRARY NAME: None
12
13 OWNER: RMULPURY
14
15 PROCEDURE/FUNCTIONS: lock_row_for_status_update
16 get_reqs_auth_status()
17 update_reqs_header_status()
18 delete_children()
19 delete_req()
20 insert_req()
21 update_oe_flag()
22 ===========================================================================*/
23
24 /*===========================================================================
25 FUNCTION NAME: lock_row_for_status_update
26
27 DESCRIPTION: Locks a row in the po_requisition_headers table
28 for update by the approvals and control forms.
29
30 PARAMETERS: x_requisition_header_id IN NUMBER
31
32 DESIGN REFERENCES: POXDOAPP.dd, POXDOCON.dd
33
34 ALGORITHM:
35
36 NOTES:
37
38 OPEN ISSUES:
39
40 CLOSED ISSUES:
41
42 CHANGE HISTORY: cmok 7/26 created it
43 wlau 7/31 change api name
44 ===========================================================================*/
45
46 PROCEDURE lock_row_for_status_update (x_requisition_header_id IN NUMBER);
47
48 /*===========================================================================
49 PROCEDURE NAME: update_reqs_header_status
50
51 DESCRIPTION: Updates requisition header status fields.
52
53 PARAMETERS: X_req_header_id IN NUMBER,
54 X_req_line_id IN NUMBER,
55 X_req_control_action IN VARCHAR2,
56 X_req_control_reason IN VARCHAR2,
57 X_req_action_history_code IN OUT VARCHAR2,
58 X_req_control_error_rc IN OUT VARCHAR2
59
60 DESIGN REFERENCES: ../POXDOCON.dd
61
62 ALGORITHM: 1. If control action is 'CANCEL' and all lines
63 are closed, update requisition header's
64 authorization_status to 'CANCELLED'.
65
66 2. If requisition still has lines that are on
67 open po shipment, update requisition
68 header's authorization_status to 'APPROVED'.
69
70 3. If control action is 'FINALLY CLOSE', update
71 closed_code to 'FINALLY CLOSED'.
72
73 NOTES:
74
75 OPEN ISSUES:
76
77 CLOSED ISSUES:
78
79 CHANGE HISTORY: WLAU 5/12 Created
80 WLAU 7/13 Added X_req_action_history_code
81 parameter.
82 ===========================================================================*/
83 PROCEDURE update_reqs_header_status
84 (X_req_header_id IN NUMBER,
85 X_req_line_id IN NUMBER,
86 X_req_control_action IN VARCHAR2,
87 X_req_control_reason IN VARCHAR2,
88 X_req_action_history_code IN OUT NOCOPY VARCHAR2,
89 X_req_control_error_rc IN OUT NOCOPY VARCHAR2);
90
91
92 /*===========================================================================
93 PROCEDURE NAME: get_req_encumbered
94
95 DESCRIPTION: Check if any distributions for a REQ is encumbered.
96
97 PARAMETERS: X_req_hdr_id IN NUMBER
98
99 DESIGN REFERENCES:
100
101 ALGORITHM: 1. Check if any distributions for a given
102 req_header_id is encumbered.
103 2. If there encumbered distributions, return TRUE
104 else return FALSE.
105
106 NOTES:
107
108 OPEN ISSUES:
109
110 CLOSED ISSUES:
111
112 CHANGE HISTORY: SMURUGES 11/22/99 Created(930894)
113 SMURUGES 12/01/99 Modified(930894)
114 Closed the cursor
115 when it fetches some
116 rows.
117 ============================================================================*/
118
119 FUNCTION get_req_encumbered(X_req_hdr_id IN NUMBER) RETURN BOOLEAN;
120
121
122 /*===========================================================================
123 PROCEDURE NAME: val_req_delete
124
125 DESCRIPTION: Checks if the REQ is not encumbered.
126 If it is encumbered, cannot delete the Requisition.
127
128 PARAMETERS: X_req_hdr_id IN NUMBER
129
130 DESIGN REFERENCES:
131
132 ALGORITHM: 1. Calls get_req_encumbered and checks if the REQ
133 is encumbered.
134 2. If the REQ is encumbered, displays the message
135 "PO_RQ_USE_LINE_DEL" and returns FALSE.
136 3. If the REQ is not encumbered returns TRUE.
137
138 NOTES:
139
140 OPEN ISSUES:
141
142 CLOSED ISSUES:
143
144 CHANGE HISTORY: SMURUGES 11/22/99 Created(930894)
145 ============================================================================*/
146
147 FUNCTION val_req_delete(X_req_hdr_id IN NUMBER) RETURN BOOLEAN;
148
149 /*===========================================================================
150 PROCEDURE NAME: delete_children
151
152 DESCRIPTION: Deletes all the children associated with
153 a requisition header. The following entities
154 are deleted for a specific header:
155
156 - distributions
157 - lines
158
159 PARAMETERS: X_req_hdr_id IN NUMBER
160
161 DESIGN REFERENCES: POXRQERQ.doc
162
163 ALGORITHM:
164
165 NOTES:
166
167 OPEN ISSUES: DEBUG. Need to verify if notifications should
168 also be deleted as a part of this package.
169
170 CLOSED ISSUES:
171
172 CHANGE HISTORY: RMULPURY 05/10 Created
173 ===========================================================================*/
174
175 PROCEDURE delete_children(X_req_hdr_id IN NUMBER);
176
177 /*===========================================================================
178 PROCEDURE NAME: delete_req
179
180 DESCRIPTION: Cover routine for deleting the requisition
181 header and the following entities associated
182 with the requisition header.
183
184 - header
185 - lines
186 - distributions
187 - notifications
188
189 PARAMETERS: X_req_hdr_id IN NUMBER
190
191
192 DESIGN REFERENCES: POXRQERQ.doc
193
194 ALGORITHM:
195
196 NOTES:
197
198 OPEN ISSUES:
199
200 CLOSED ISSUES:
201
202 CHANGE HISTORY: RMULPURY 05/10 Created
203 ===========================================================================*/
204
205 PROCEDURE delete_req(X_req_hdr_id IN NUMBER);
206
207
208 /*===========================================================================
209 PROCEDURE NAME: insert_req
210
211 DESCRIPTION: Cover routine to insert a requisition header
212 and notification. This procedure also
213 handles automatic numbering.
214
215 PARAMETERS: See below.
216
217 DESIGN REFERENCES: POXRQERQ.doc
218
219 ALGORITHM:
220
221 NOTES: X_manual should be FALSE if numbering is 'AUTOMATIC'
222 and it should be TRUE if 'MANUAL' numbering
223 is being used for requisitions.
224
225 OPEN ISSUES:
226
227 CLOSED ISSUES:
228
229 CHANGE HISTORY: RMULPURY 05/10 Created
230 ===========================================================================*/
231
232 PROCEDURE insert_req(X_Rowid IN OUT NOCOPY VARCHAR2,
233 X_Requisition_Header_Id IN OUT NOCOPY NUMBER,
234 X_Preparer_Id NUMBER,
235 X_Last_Update_Date DATE,
236 X_Last_Updated_By NUMBER,
237 X_Segment1 IN OUT NOCOPY VARCHAR2,
238 X_Summary_Flag VARCHAR2,
239 X_Enabled_Flag VARCHAR2,
240 X_Segment2 VARCHAR2,
241 X_Segment3 VARCHAR2,
242 X_Segment4 VARCHAR2,
243 X_Segment5 VARCHAR2,
244 X_Start_Date_Active DATE,
245 X_End_Date_Active DATE,
246 X_Last_Update_Login NUMBER,
247 X_Creation_Date DATE,
248 X_Created_By NUMBER,
249 X_Description VARCHAR2,
250 X_Authorization_Status VARCHAR2,
251 X_Note_To_Authorizer VARCHAR2,
252 X_Type_Lookup_Code VARCHAR2,
253 X_Transferred_To_Oe_Flag VARCHAR2,
254 X_Attribute_Category VARCHAR2,
255 X_Attribute1 VARCHAR2,
256 X_Attribute2 VARCHAR2,
257 X_Attribute3 VARCHAR2,
258 X_Attribute4 VARCHAR2,
259 X_Attribute5 VARCHAR2,
260 X_On_Line_Flag VARCHAR2,
261 X_Preliminary_Research_Flag VARCHAR2,
262 X_Research_Complete_Flag VARCHAR2,
263 X_Preparer_Finished_Flag VARCHAR2,
264 X_Preparer_Finished_Date DATE,
265 X_Agent_Return_Flag VARCHAR2,
266 X_Agent_Return_Note VARCHAR2,
267 X_Cancel_Flag VARCHAR2,
268 X_Attribute6 VARCHAR2,
269 X_Attribute7 VARCHAR2,
270 X_Attribute8 VARCHAR2,
271 X_Attribute9 VARCHAR2,
272 X_Attribute10 VARCHAR2,
273 X_Attribute11 VARCHAR2,
274 X_Attribute12 VARCHAR2,
275 X_Attribute13 VARCHAR2,
276 X_Attribute14 VARCHAR2,
277 X_Attribute15 VARCHAR2,
278 X_Ussgl_Transaction_Code VARCHAR2,
279 X_Government_Context VARCHAR2,
280 X_Interface_Source_Code VARCHAR2,
281 X_Interface_Source_Line_Id NUMBER,
282 X_Closed_Code VARCHAR2,
283 X_Manual BOOLEAN,
284 X_amount NUMBER,
285 X_currency_code VARCHAR2,
286 p_org_id IN NUMBER default null -- <R12 MOAC>
287 );
288
289 /*===========================================================================
290 PROCEDURE NAME: update_oe_flag
291
292 DESCRIPTION: Update the column transferred_to_oe_flag
293 for the specific requisition.
294
295
296 PARAMETERS: X_req_hdr_id IN NUMBER
297 X_flag IN VARCHAR2
298
299 DESIGN REFERENCES: POXRQERQ.doc
300
301 ALGORITHM:
302
303 NOTES:
304
305 OPEN ISSUES:
306
307 CLOSED ISSUES:
308
309 CHANGE HISTORY: RMULPURY Created 05/10
310 ===========================================================================*/
311
312 PROCEDURE update_oe_flag(X_req_hdr_id IN NUMBER,
313 X_flag IN VARCHAR2);
314
315
316
317 /*===========================================================================
318 PROCEDURE NAME: get_req_startup_values
319
320 DESCRIPTION: Gets startup values required for requisitions.
321
322
323 PARAMETERS: X_source_inventory IN OUT VARCHAR2,
324 X_source_vendor IN OUT VARCHAR2
325
326 DESIGN REFERENCES: ../POXRQERQ.doc
327
328 ALGORITHM:
329
330 NOTES:
331
332 OPEN ISSUES:
333
334 CLOSED ISSUES:
335
336 CHANGE HISTORY: RMULPURY 08/10 Created
337 ===========================================================================*/
338 PROCEDURE get_req_startup_values
339 (X_source_inventory IN OUT NOCOPY VARCHAR2,
340 X_source_vendor IN OUT NOCOPY VARCHAR2);
341
342
343
344 END PO_REQS_SV;