1 PACKAGE PO_DOCUMENT_CHECKS_PVT AUTHID CURRENT_USER AS
2 /* $Header: POXVDCKS.pls 120.16.12020000.5 2013/04/08 17:46:54 akyanama ship $*/
3
4
5
6
7 -----------------------------------------------------------------------------
8 -- Public variables
9 -----------------------------------------------------------------------------
10
11 --<FPJ ENCUMBRANCE>
12
13 -- <Document Manger Rewrite 11.5.11>: Added FINAL_CLOSE_CHECK
14
15 -- Actions:
16 g_action_DOC_SUBMISSION_CHECK CONSTANT
17 VARCHAR2(30)
18 := 'DOC_SUBMISSION_CHECK'
19 ;
20 g_action_UNRESERVE CONSTANT
21 VARCHAR2(30)
22 := 'UNRESERVE'
23 ;
24 g_action_FINAL_CLOSE_CHECK CONSTANT
25 VARCHAR2(30)
26 := 'FINAL CLOSE'
27 ;
28
29 -- Document types:
30 g_document_type_REQUISITION CONSTANT
31 PO_DOCUMENT_TYPES.document_type_code%TYPE
32 := PO_CORE_S.g_doc_type_REQUISITION
33 ;
34 g_document_type_PO CONSTANT
35 PO_DOCUMENT_TYPES.document_type_code%TYPE
36 := PO_CORE_S.g_doc_type_PO
37 ;
38 g_document_type_PA CONSTANT
39 PO_DOCUMENT_TYPES.document_type_code%TYPE
40 := PO_CORE_S.g_doc_type_PA
41 ;
42 g_document_type_RELEASE CONSTANT
43 PO_DOCUMENT_TYPES.document_type_code%TYPE
44 := PO_CORE_S.g_doc_type_RELEASE
45 ;
46
47 -- Document levels:
48 g_document_level_HEADER CONSTANT
49 VARCHAR2(25)
50 := PO_CORE_S.g_doc_level_HEADER
51 ;
52 g_document_level_LINE CONSTANT
53 VARCHAR2(25)
54 := PO_CORE_S.g_doc_level_LINE
55 ;
56 g_document_level_SHIPMENT CONSTANT
57 VARCHAR2(25)
58 := PO_CORE_S.g_doc_level_SHIPMENT
59 ;
60 g_document_level_DISTRIBUTION CONSTANT
61 VARCHAR2(25)
62 := PO_CORE_S.g_doc_level_DISTRIBUTION
63 ;
64
65 -- 13655939, 13019003
66 --Changed the 2 arguments to 3
67 FUNCTION validate_account_wrapper( p_structure_number IN NUMBER,
68 p_combination_id IN NUMBER,
69 p_val_date IN DATE) RETURN VARCHAR2;
70 -- 13655939, 13019003
71 --<DropShip FPJ Start>
72 --The following constants are possible values
76 G_CHECK_RESERVABLE CONSTANT VARCHAR2(30) := 'CHECK_RESERVABLE';
73 -- for p_mode input parameter of po_status_check Procedure
74 G_CHECK_UPDATEABLE CONSTANT VARCHAR2(30) := 'CHECK_UPDATEABLE';
75 G_GET_STATUS CONSTANT VARCHAR2(30) := 'GET_STATUS';
77 G_CHECK_UNRESERVABLE CONSTANT VARCHAR2(30) := 'CHECK_UNRESERVABLE';
78 --<DropShip FPJ End>
79
80
81 -----------------------------------------------------------------------------
82 -- Public subprograms
83 -----------------------------------------------------------------------------
84
85
86
87
88 PROCEDURE po_submission_check(
89 p_api_version IN NUMBER
90 , p_action_requested IN VARCHAR2
91 , p_document_type IN VARCHAR2
92 , p_document_subtype IN VARCHAR2
93 , p_document_level IN VARCHAR2
94 , p_document_level_id IN NUMBER
95 , p_requested_changes IN PO_CHANGES_REC_TYPE
96 , p_check_asl IN BOOLEAN
97 , p_draft_id IN NUMBER := -1
98 , p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
99 , p_origin_doc_id IN NUMBER := NULL --Bug#5462677
100 , x_return_status OUT NOCOPY VARCHAR2
101 , x_sub_check_status OUT NOCOPY VARCHAR2
102 , x_has_warnings OUT NOCOPY VARCHAR2 -- bug3574165
103 , x_msg_data OUT NOCOPY VARCHAR2
104 , x_online_report_id OUT NOCOPY NUMBER
105 , x_doc_check_error_record OUT NOCOPY doc_check_Return_Type
106 );
107
108
109 PROCEDURE post_submission_check -- <2757450>
110 ( p_api_version IN NUMBER
111 , p_document_type IN VARCHAR2
112 , p_document_subtype IN VARCHAR2
113 , p_document_id IN NUMBER
114 , x_return_status OUT NOCOPY VARCHAR2
115 , x_sub_check_status OUT NOCOPY VARCHAR2
116 , x_online_report_id OUT NOCOPY NUMBER
117 );
118
119 --Bug 4943365 Removed the check_asl procedure because blankets
120 --should not do the asl checks.
121 --PROCEDURE check_asl
122
123 --For REQUISTIONS
124 PROCEDURE check_requisitions(p_document_id IN NUMBER,
125 p_online_report_id IN NUMBER,
126 p_user_id IN NUMBER,
127 p_login_id IN NUMBER,
128 p_sequence IN OUT NOCOPY NUMBER,
129 x_return_status OUT NOCOPY VARCHAR2);
130
131 --For RELEASES
132 PROCEDURE check_releases(p_document_id IN NUMBER,
133 p_online_report_id IN NUMBER,
134 p_user_id IN NUMBER,
135 p_login_id IN NUMBER,
136 p_sequence IN OUT NOCOPY NUMBER,
137 x_return_status OUT NOCOPY VARCHAR2);
138
139 --For RELEASES, PO
140 PROCEDURE check_po_rel_reqprice(p_document_type IN VARCHAR2,
141 p_document_id IN NUMBER,
142 p_online_report_id IN NUMBER,
143 p_user_id IN NUMBER,
144 p_login_id IN NUMBER,
145 p_sequence IN OUT NOCOPY NUMBER,
146 x_return_status OUT NOCOPY VARCHAR2);
147
148 --For PO,PA: Header Checks
149 PROCEDURE check_po_pa_header(p_document_id IN NUMBER,
150 p_online_report_id IN NUMBER,
151 p_user_id IN NUMBER,
152 p_login_id IN NUMBER,
153 p_sequence IN OUT NOCOPY NUMBER,
154 x_return_status OUT NOCOPY VARCHAR2);
155
156 --For PO
157 PROCEDURE check_po( p_document_id IN NUMBER,
158 p_online_report_id IN NUMBER,
159 p_user_id IN NUMBER,
160 p_login_id IN NUMBER,
161 p_sequence IN OUT NOCOPY NUMBER,
162 x_return_status OUT NOCOPY VARCHAR2);
163
164 --For Planned POs and Blanket PAs
165 PROCEDURE check_planned_po_blanket_pa(p_document_id IN NUMBER,
166 p_online_report_id IN NUMBER,
167 p_user_id IN NUMBER,
168 p_login_id IN NUMBER,
169 p_sequence IN OUT NOCOPY NUMBER,
170 x_return_status OUT NOCOPY VARCHAR2);
171
172 --For Standard PO
173 --Includes Global Agreement Reference checks, Consigned Inventory checks
174 PROCEDURE check_standard_po(p_document_id IN NUMBER,
175 p_online_report_id IN NUMBER,
176 p_user_id IN NUMBER,
177 p_login_id IN NUMBER,
178 p_sequence IN OUT NOCOPY NUMBER,
179 x_return_status OUT NOCOPY VARCHAR2);
180
181 --For Standard POs which reference Global Agreement called from
182 --check_standard_po
183 PROCEDURE check_std_global_ref(p_document_id IN NUMBER,
184 p_online_report_id IN NUMBER,
185 p_user_id IN NUMBER,
186 p_login_id IN NUMBER,
187 p_sequence IN OUT NOCOPY NUMBER,
188 x_return_status OUT NOCOPY VARCHAR2);
189
190 --For Standard POs which have consigned reference called from
191 --check_standard_po
192 PROCEDURE check_std_consigned_ref(p_document_id IN NUMBER,
193 p_online_report_id IN NUMBER,
194 p_user_id IN NUMBER,
195 p_login_id IN NUMBER,
196 p_sequence IN OUT NOCOPY NUMBER,
200 -- For Standard POs which have Global contract reference. Called from
197 x_return_status OUT NOCOPY VARCHAR2);
198
199 -- <GC FPJ START>
201 -- check_standard_po
202
203 PROCEDURE check_std_gc_ref
204 ( p_document_id IN NUMBER,
205 p_online_report_id IN NUMBER,
206 p_user_id IN NUMBER,
207 p_login_id IN NUMBER,
208 x_sequence IN OUT NOCOPY NUMBER,
209 x_return_status OUT NOCOPY VARCHAR2
210 );
211
212 -- <GC FPJ END>
213
214 --For Contract PA
215 PROCEDURE check_contract_agreement(p_document_id IN NUMBER,
216 p_online_report_id IN NUMBER,
217 p_user_id IN NUMBER,
218 p_login_id IN NUMBER,
219 p_sequence IN OUT NOCOPY NUMBER,
220 x_return_status OUT NOCOPY VARCHAR2);
221
222 --For POs, Reqs, Releases
223 PROCEDURE do_cbc_related_validations(p_document_type IN VARCHAR2,
224 p_document_subtype IN VARCHAR2,
225 p_document_id IN NUMBER,
226 p_online_report_id IN NUMBER,
227 p_user_id IN NUMBER,
228 p_login_id IN NUMBER,
229 p_sequence IN OUT NOCOPY NUMBER,
230 x_return_status OUT NOCOPY VARCHAR2);
231
232
233 --For populating global temp from po_headers
234 -- CLM Aprvl Added Draft Id parameter
235 PROCEDURE populate_po_headers_gt(p_document_id IN number, p_draft_id IN number := -1,
236 x_return_status OUT NOCOPY VARCHAR2);
237
238
239 --For populating global temp from po_requisition_headers
240 PROCEDURE populate_req_headers_gt(p_document_id IN number,
241 x_return_status OUT NOCOPY VARCHAR2);
242
243 --For populating global temp from po_requisition_lines
244 PROCEDURE populate_req_lines_gt(p_document_id IN number,
245 x_return_status OUT NOCOPY VARCHAR2);
246
247 --For populating global temp from po_releases
248 PROCEDURE populate_releases_gt(p_document_id IN number,
249 x_return_status OUT NOCOPY VARCHAR2);
250
251
252 --For updating the global temp tables with requested changes
253 PROCEDURE update_global_temp_tables(p_document_type IN VARCHAR2,
254 p_document_subtype IN VARCHAR2,
255 p_document_id IN NUMBER,
256 -- <PO_CHANGE_API FPJ> Renamed the type to PO_CHANGES_REC_TYPE:
257 p_requested_changes IN PO_CHANGES_REC_TYPE,
258 x_return_status OUT NOCOPY VARCHAR2);
259
260 -- <FPJ, Refactor Security API START>
261
262 /**
263 * Public Procedure: PO_Security_Check
264 * Requires:
265 * IN PARAMETERS:
266 * p_api_version: Version number of API that caller expects. It
267 * should match the l_api_version defined in the
268 * procedure
269 * p_query_table: Table you want to check
270 * p_owner_id_column: Owner id column of the table
271 * p_employee_id: User id to access the document
272 * p_minimum_access_level: Minimum access level to the document
273 * p_document_type: The type of the document to perform
274 * the security check on
275 * p_document_subtype: The subtype of the document.
276 * p_type_clause: The document type clause to be used in
277 * constructing where clause
278 *
279 * Modifies: None
280 * Effects: This procedure builds dynamic WHERE clause fragments based on
281 * document security parameters.
282 * Returns:
283 * x_return_status: FND_API.G_RET_STS_SUCCESS if API succeeds
284 * FND_API.G_RET_STS_ERROR if API fails
285 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
286 * x_msg_data: Contains error msg in case x_return_status returned
287 * FND_API.G_RET_STS_ERROR or
288 * FND_API.G_RET_STS_UNEXP_ERROR
289 * x_where_clause: The constructed where clause
290 */
291
292 PROCEDURE PO_Security_Check (p_api_version IN Number,
293 p_query_table IN Varchar2,
294 p_owner_id_column IN Varchar2,
295 p_employee_id IN Varchar2,
296 p_minimum_access_level IN Varchar2,
297 p_document_type IN Varchar2,
298 p_document_subtype IN Varchar2,
299 p_type_clause IN Varchar2,
300 x_return_status OUT NOCOPY VARCHAR2,
301 x_msg_data OUT NOCOPY VARCHAR2,
302 x_where_clause OUT NOCOPY VARCHAR2);
303
304 -- <FPJ Refactor Security API END>
305
306 -- The new procedure po_status_check added in DropShip FPJ project
307
308 -- Detailed comments maintained in the Package Body PO_DOCUMENT_CHECKS_PVT.po_status_check
309 PROCEDURE po_status_check (
310 p_api_version IN NUMBER,
311 p_header_id IN PO_TBL_NUMBER,
312 p_release_id IN PO_TBL_NUMBER,
313 p_document_type IN PO_TBL_VARCHAR30,
314 p_document_subtype IN PO_TBL_VARCHAR30,
315 p_document_num IN PO_TBL_VARCHAR30,
316 p_vendor_order_num IN PO_TBL_VARCHAR30,
317 p_line_id IN PO_TBL_NUMBER,
318 p_line_location_id IN PO_TBL_NUMBER,
319 p_distribution_id IN PO_TBL_NUMBER,
320 p_mode IN VARCHAR2,
321 p_lock_flag IN VARCHAR2 := 'N',
322 p_calling_module IN VARCHAR2 := NULL, -- PDOI Rewrite R12
326 x_return_status OUT NOCOPY VARCHAR2
323 p_role IN VARCHAR2 := NULL, -- PDOI Rewrite R12
324 p_skip_cat_upload_chk IN VARCHAR2 := NULL, -- PDOI Rewrite R12
325 x_po_status_rec OUT NOCOPY PO_STATUS_REC_TYPE,
327 );
328
329 -- <CONTERMS FPJ>
330 -- new procedure to call contract terms validation during submission check
331 PROCEDURE check_terms(
332 p_document_id IN NUMBER,
333 p_document_type IN VARCHAR2,
334 p_document_subtype IN VARCHAR2,
335 p_online_report_id IN NUMBER,
336 p_user_id IN NUMBER,
337 p_login_id IN NUMBER,
338 p_sequence IN OUT NOCOPY NUMBER,
339 x_return_status OUT NOCOPY VARCHAR2);
340
341 -- <JFMIP Vendor Registration FPJ>
342 -- For Release, PO, PA: checks if vendor site registration is valid
343 PROCEDURE check_vendor_site_ccr_regis(
344 p_document_id IN NUMBER,
345 p_online_report_id IN NUMBER,
346 p_user_id IN NUMBER,
347 p_login_id IN NUMBER,
348 p_sequence IN OUT NOCOPY NUMBER,
349 x_return_status OUT NOCOPY VARCHAR2);
350
351 --<LCM project start>
352 -- Procedure to set the lcm flag in po shipments and distributions
353 PROCEDURE Set_LCM_Flag (p_line_location_id IN NUMBER,
354 p_doc_check_status IN VARCHAR2,
355 x_return_status OUT nocopy VARCHAR2);
356 --<LCM project end>
357
358 --<BUG 4624736 START>
359 -- Checks if the pay item's price can be updated.
360 -- NOTE: does not verify that line location is in fact
361 -- a pay item.
362 FUNCTION is_pay_item_price_updateable (
363 p_line_location_id IN NUMBER
364 , p_add_reasons_to_msg_list IN VARCHAR2)
365 RETURN BOOLEAN;
366
367 FUNCTION chk_unv_invoices(p_invoice_type IN VARCHAR2 DEFAULT 'BOTH',
368 p_po_header_id IN NUMBER,
369 p_po_release_id IN NUMBER DEFAULT NULL,
370 p_po_line_id IN NUMBER DEFAULT NULL,
371 p_line_location_id IN NUMBER DEFAULT NULL,
372 p_po_distribution_id IN NUMBER DEFAULT NULL,
373 p_invoice_id IN NUMBER DEFAULT NULL,
374 p_calling_sequence IN VARCHAR2) RETURN NUMBER;
375
376 --<BUG 4624736 END>
377
378 -- For System Generated Description project
379 PROCEDURE validate_genchangedesc( p_draft_id IN NUMBER
380 , p_header_id IN NUMBER
381 , p_online_report_id IN NUMBER
382 , p_user_id IN NUMBER
383 , p_login_id IN NUMBER
384 , p_sequence IN OUT NOCOPY NUMBER
385 , x_return_status OUT NOCOPY VARCHAR2);
386
387
388
389
390 -- Added for Bug 10218898
391 PROCEDURE PO_UOM_CHECK(P_DOCUMENT_ID IN NUMBER,
392 P_DOCUMENT_TYPE IN VARCHAR2,
393 P_ONLINE_REPORT_ID IN NUMBER,
394 P_USER_ID IN NUMBER,
395 P_LOGIN_ID IN NUMBER,
396 P_SEQUENCE IN OUT NOCOPY NUMBER,
397 X_RETURN_STATUS OUT NOCOPY VARCHAR2
398 ,x_msg_data OUT NOCOPY VARCHAR2);
399
400 --Added for Bug 11714043
401
402 PROCEDURE validate_supplier_debarred( p_draft_id IN NUMBER
403 , p_header_id IN NUMBER
404 , p_online_report_id IN NUMBER
405 , p_user_id IN NUMBER
406 , p_login_id IN NUMBER
407 , p_sequence IN OUT NOCOPY NUMBER
408 , x_return_status OUT NOCOPY VARCHAR2
409 );
410
411 PROCEDURE validate_co_warrant( p_draft_id IN NUMBER
412 , p_header_id IN NUMBER
413 , p_contracting_officer IN NUMBER
414 , p_user_id IN NUMBER
415 , p_login_id IN NUMBER
416 , itemtype IN VARCHAR2 default null
417 , itemkey IN VARCHAR2 default null
418 , p_online_report_id IN OUT NOCOPY NUMBER
419 , p_sequence IN OUT NOCOPY NUMBER
420 , x_return_status OUT NOCOPY VARCHAR2
421 );
422
423
424
425 --bug13059027
426 FUNCTION is_approved_clm_document(p_document_id IN NUMBER) RETURN VARCHAR2;
427
428 PROCEDURE PO_ACRN_CHECK(P_DOCUMENT_ID IN NUMBER,
429 P_DRAFT_ID IN NUMBER,
430 P_DOCUMENT_TYPE IN VARCHAR2,
431 P_ONLINE_REPORT_ID IN NUMBER,
432 P_USER_ID IN NUMBER,
433 P_LOGIN_ID IN NUMBER,
434 P_SEQUENCE IN OUT NOCOPY NUMBER,
435 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
436 x_msg_data OUT NOCOPY VARCHAR2);
437
438 ----for bug 13481176
439 --<Bug 13019003> Merged all four account validation procedures into one.
440 PROCEDURE PO_VALIDATE_ACCOUNTS(P_DOCUMENT_ID IN NUMBER,
441 P_DOCUMENT_TYPE IN VARCHAR2,
442 P_ONLINE_REPORT_ID IN NUMBER,
443 P_USER_ID IN NUMBER,
444 P_LOGIN_ID IN NUMBER,
445 P_SEQUENCE IN OUT NOCOPY NUMBER,
446 X_RETURN_STATUS OUT NOCOPY VARCHAR2
447 ,x_msg_data OUT NOCOPY VARCHAR2);
448
449 --Added for bug 12951645
450 PROCEDURE CHECK_CLOSE_WIP_JOB(p_document_id IN NUMBER,
451 p_document_type IN VARCHAR2,
452 p_online_report_id IN NUMBER,
453 p_user_id IN NUMBER,
454 p_login_id IN NUMBER,
455 p_sequence IN OUT NOCOPY NUMBER,
456 x_return_status OUT NOCOPY VARCHAR2
457 );
458
459 --Bug 15843328
460 PROCEDURE check_accrue_on_receipt(
461 P_DOCUMENT_ID IN NUMBER,
462 P_DOCUMENT_TYPE IN VARCHAR2,
463 P_ONLINE_REPORT_ID IN NUMBER,
464 P_USER_ID IN NUMBER,
465 P_LOGIN_ID IN NUMBER,
466 P_SEQUENCE IN OUT NOCOPY NUMBER,
467 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
468 x_msg_data OUT NOCOPY VARCHAR2 );
469
470 --SBCR Integration Changes Begin.
471
472 PROCEDURE Check_sbcr_exists(document_id IN NUMBER,
473 document_type IN VARCHAR2,
474 document_version IN NUMBER,
475 document_number IN VARCHAR2,
476 x_msg_data OUT nocopy VARCHAR2,
477 x_msg_count OUT nocopy NUMBER,
478 x_contract_exists OUT nocopy VARCHAR2,
479 x_contract_status OUT nocopy VARCHAR2,
480 x_is_draft_status OUT nocopy VARCHAR2);
481 PROCEDURE check_po_sbcr_reference(p_document_id IN NUMBER,
482 p_draft_id IN NUMBER,
483 p_online_report_id IN NUMBER,
484 p_user_id IN NUMBER,
485 p_login_id IN NUMBER,
486 p_org_id IN NUMBER,
487 p_sequence IN OUT NOCOPY NUMBER,
488 x_return_status OUT NOCOPY VARCHAR2);
489 --SBCR Integration Changes End.
490 END PO_DOCUMENT_CHECKS_PVT;
[Home] [Help]