1 PACKAGE PO_DOCUMENT_CHECKS_GRP AUTHID CURRENT_USER AS
2 /* $Header: POXGDCKS.pls 120.7 2010/04/07 15:16:26 ssreekum ship $*/
3
4 -----------------------------------------------------------------------------
5 -- Public variables
6 -----------------------------------------------------------------------------
7
8 -- Actions:
9 g_action_DOC_SUBMISSION_CHECK CONSTANT
10 VARCHAR2(30)
11 := PO_DOCUMENT_CHECKS_PVT.g_action_DOC_SUBMISSION_CHECK
12 ;
13 g_action_UNRESERVE CONSTANT
14 VARCHAR2(30)
15 := PO_DOCUMENT_CHECKS_PVT.g_action_UNRESERVE
16 ;
17 -- <Doc Manager Rewrite 11.5.11 Start>
18 g_action_FINAL_CLOSE_CHECK CONSTANT
19 VARCHAR2(30)
20 := PO_DOCUMENT_CHECKS_PVT.g_action_FINAL_CLOSE_CHECK
21 ;
22 -- <Doc Manager Rewrite 11.5.11 End>
23
24 -- Document types:
25 g_document_type_REQUISITION CONSTANT
26 PO_DOCUMENT_TYPES.document_type_code%TYPE
27 := PO_CORE_S.g_doc_type_REQUISITION
28 ;
29 g_document_type_PO CONSTANT
30 PO_DOCUMENT_TYPES.document_type_code%TYPE
31 := PO_CORE_S.g_doc_type_PO
32 ;
33 g_document_type_PA CONSTANT
34 PO_DOCUMENT_TYPES.document_type_code%TYPE
35 := PO_CORE_S.g_doc_type_PA
36 ;
37 g_document_type_RELEASE CONSTANT
38 PO_DOCUMENT_TYPES.document_type_code%TYPE
39 := PO_CORE_S.g_doc_type_RELEASE
40 ;
41
42 -- Document levels:
43 g_document_level_HEADER CONSTANT
44 VARCHAR2(25)
45 := PO_CORE_S.g_doc_level_HEADER
46 ;
47 g_document_level_LINE CONSTANT
48 VARCHAR2(25)
49 := PO_CORE_S.g_doc_level_LINE
50 ;
51 g_document_level_SHIPMENT CONSTANT
52 VARCHAR2(25)
53 := PO_CORE_S.g_doc_level_SHIPMENT
54 ;
55 g_document_level_DISTRIBUTION CONSTANT
56 VARCHAR2(25)
57 := PO_CORE_S.g_doc_level_DISTRIBUTION
58 ;
59
60
61
62
63 -----------------------------------------------------------------------------
64 -- Public subprograms
65 -----------------------------------------------------------------------------
66
67
68
69
70 PROCEDURE po_submission_check(
71 p_api_version IN NUMBER
72 , p_action_requested IN VARCHAR2
73 , p_document_type IN VARCHAR2
74 , p_document_subtype IN VARCHAR2
75 , p_document_level IN VARCHAR2
76 , p_document_level_id IN NUMBER
77 , p_org_id IN NUMBER
78 , p_requested_changes IN PO_CHANGES_REC_TYPE
79 , p_check_asl IN BOOLEAN
80 , p_draft_id IN NUMBER:= -1 -- CLM Aprvl
81 , p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
82 , p_origin_doc_id IN NUMBER := NULL -- Bug#5462677
83 , x_return_status OUT NOCOPY VARCHAR2
84 , x_sub_check_status OUT NOCOPY VARCHAR2
85 , x_has_warnings OUT NOCOPY VARCHAR2 -- bug3574165
86 , x_msg_data OUT NOCOPY VARCHAR2
87 , x_online_report_id OUT NOCOPY NUMBER
88 , x_doc_check_error_record OUT NOCOPY doc_check_Return_Type
89 );
90
91
92 -- bug3574165
93 -- Overloaded procedure. This procedure does not have x_has_warnings as
94 -- parameter.
95 PROCEDURE po_submission_check(
96 p_api_version IN NUMBER
97 , p_action_requested IN VARCHAR2
98 , p_document_type IN VARCHAR2
99 , p_document_subtype IN VARCHAR2
100 , p_document_level IN VARCHAR2
101 , p_document_level_id IN NUMBER
102 , p_org_id IN NUMBER
103 , p_requested_changes IN PO_CHANGES_REC_TYPE
104 , p_check_asl IN BOOLEAN
105 , p_draft_id IN NUMBER:= -1 -- CLM Aprvl
106 , p_origin_doc_id IN NUMBER := NULL -- Bug#5462677
107 , p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
108 , x_return_status OUT NOCOPY VARCHAR2
109 , x_sub_check_status OUT NOCOPY VARCHAR2
110 , x_msg_data OUT NOCOPY VARCHAR2
111 , x_online_report_id OUT NOCOPY NUMBER
112 , x_doc_check_error_record OUT NOCOPY doc_check_Return_Type
113 );
114
115 PROCEDURE po_submission_check(
116 p_api_version IN NUMBER
117 , p_action_requested IN VARCHAR2
118 , p_document_type IN VARCHAR2
119 , p_document_subtype IN VARCHAR2
120 , p_document_id IN NUMBER
121 , p_org_id IN NUMBER
122 , p_requested_changes IN PO_CHANGES_REC_TYPE
123 , p_check_asl IN BOOLEAN := TRUE
124 , p_draft_id IN NUMBER:= -1 -- CLM Aprvl
125 , p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
126 , x_return_status OUT NOCOPY VARCHAR2
127 , x_sub_check_status OUT NOCOPY VARCHAR2
128 , x_msg_data OUT NOCOPY VARCHAR2
129 , x_online_report_id OUT NOCOPY NUMBER
130 , x_doc_check_error_record OUT NOCOPY doc_check_Return_Type
131 );
132
133
134 -- This procedure is called from POXAPAPC.pld, POXWPA4B.pls
135 --Overloaded procedure without following parameter:
136 -- IN: p_requested_changes
137 -- IN: p_org_id
138 -- OUT : x_doc_check_error_record
139 PROCEDURE po_submission_check(p_api_version IN NUMBER,
140 p_action_requested IN VARCHAR2,
141 p_document_type IN VARCHAR2,
142 p_document_subtype IN VARCHAR2,
143 p_document_id IN NUMBER,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_sub_check_status OUT NOCOPY VARCHAR2,
146 x_msg_data OUT NOCOPY VARCHAR2,
147 x_online_report_id OUT NOCOPY NUMBER);
148
149 -- bug3574165 START
150 -- Overloaded procedure to include x_has_warnings parameter
151 -- This parameter is used to indicate whether there are warnings coming
152 -- out from po submission check
153 PROCEDURE po_submission_check
154 (
155 p_api_version IN NUMBER,
156 p_action_requested IN VARCHAR2,
157 p_document_type IN VARCHAR2,
158 p_document_subtype IN VARCHAR2,
159 p_document_id IN NUMBER,
160 p_check_asl IN BOOLEAN,
161 p_draft_id IN NUMBER:= -1, -- CLM Aprvl
162 x_return_status OUT NOCOPY VARCHAR2,
163 x_sub_check_status OUT NOCOPY VARCHAR2,
164 x_has_warnings OUT NOCOPY VARCHAR2,
165 x_msg_data OUT NOCOPY VARCHAR2,
166 x_online_report_id OUT NOCOPY NUMBER
167 );
168 -- bug3574165 END
169
170 -- <2757450 START>: Overloaded procedure to include 'p_check_asl' parameter.
171 -- This parameter is used to indicate whether or not to perform the
172 -- PO_SUB_ITEM_NOT_APPROVED and PO_SUB_ITEM_ASL_DEBARRED checks.
173 --
174 PROCEDURE po_submission_check
175 (
176 p_api_version IN NUMBER,
177 p_action_requested IN VARCHAR2,
178 p_document_type IN VARCHAR2,
179 p_document_subtype IN VARCHAR2,
180 p_document_id IN NUMBER,
181 p_check_asl IN BOOLEAN,
182 p_draft_id IN NUMBER:= -1, -- CLM Aprvl
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_sub_check_status OUT NOCOPY VARCHAR2,
185 x_msg_data OUT NOCOPY VARCHAR2,
186 x_online_report_id OUT NOCOPY NUMBER
187 );
188 -- <2757450 END>
189
190 -- <FPJ Refactor Security API START>
191
192 /**
193 * Public Procedure: PO_Security_Check
194 * Requires:
195 * IN PARAMETERS:
196 * p_api_version: Version number of API that caller expects. It
197 * should match the l_api_version defined in the
198 * procedure
199 * p_query_table: Table you want to check
200 * p_owner_id_column: Owner id column of the table
201 * p_employee_id: User id to access the document
202 * p_minimum_access_level: Minimum access level to the document
203 * p_document_type: The type of the document to perform
204 * the security check on
205 * p_document_subtype: The subtype of the document.
206 * Valid Document types and Document subtypes
207 * Document Type Document Subtype
208 * RFQ ---> STANDARD
209 * QUOTATION ---> STANDARD
210 * REQUISITION ---> PURCHASE/INTERNAL
211 * RELEASE ---> SCHEDULED/BLANKET
212 * PO ---> PLANNED/STANDARD
213 * PA ---> CONTRACT/BLANKET
214 * p_type_clause: The document type clause to be used in
215 * constructing where clause
216 *
217 * Modifies: None
218 * Effects: This procedure builds dynamic WHERE clause fragments based on
219 * document security parameters.
220 * Returns:
221 * x_return_status: FND_API.G_RET_STS_SUCCESS if API succeeds
222 * FND_API.G_RET_STS_ERROR if API fails
223 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
224 * x_msg_data: Contains error msg in case x_return_status returned
225 * FND_API.G_RET_STS_ERROR or
226 * FND_API.G_RET_STS_UNEXP_ERROR
227 * x_where_clause: The constructed where clause
228 */
229 PROCEDURE PO_Security_Check (p_api_version IN Number,
230 p_query_table IN Varchar2,
231 p_owner_id_column IN Varchar2,
232 p_employee_id IN VARCHAR2,
233 p_org_id IN Number,
234 p_minimum_access_level IN Varchar2,
235 p_document_type IN Varchar2,
236 p_document_subtype IN Varchar2,
237 p_type_clause IN Varchar2,
238 x_return_status OUT NOCOPY VARCHAR2,
239 x_msg_data OUT NOCOPY VARCHAR2,
240 x_where_clause OUT NOCOPY VARCHAR2);
241
242 -- <FPJ Refactor Security API END>
243
244 -- The new overloaded procedures po_status_check added in DropShip FPJ project
245
246 -- Detailed comments are in PVT Package Body PO_DOCUMENT_CHECKS_PVT.po_status_check
247 PROCEDURE po_status_check (
248 p_api_version IN NUMBER,
249 p_header_id IN PO_TBL_NUMBER,
250 p_release_id IN PO_TBL_NUMBER,
251 p_document_type IN PO_TBL_VARCHAR30,
252 p_document_subtype IN PO_TBL_VARCHAR30,
253 p_document_num IN PO_TBL_VARCHAR30,
254 p_vendor_order_num IN PO_TBL_VARCHAR30,
255 p_line_id IN PO_TBL_NUMBER,
256 p_line_location_id IN PO_TBL_NUMBER,
257 p_distribution_id IN PO_TBL_NUMBER,
258 p_mode IN VARCHAR2,
259 p_lock_flag IN VARCHAR2 := 'N',
260 p_calling_module IN VARCHAR2 := NULL, -- PDOI Rewrite R12
261 p_role IN VARCHAR2 := NULL, -- PDOI Rewrite R12
262 p_skip_cat_upload_chk IN VARCHAR2 := NULL, -- PDOI Rewrite R12
263 x_po_status_rec OUT NOCOPY PO_STATUS_REC_TYPE,
264 x_return_status OUT NOCOPY VARCHAR2
265 );
266
267 -- Detailed comments are in PVT Package Body PO_DOCUMENT_CHECKS_PVT.po_status_check
268 PROCEDURE po_status_check (
269 p_api_version IN NUMBER,
270 p_header_id IN NUMBER := NULL,
271 p_release_id IN NUMBER := NULL,
272 p_document_type IN VARCHAR2 := NULL,
273 p_document_subtype IN VARCHAR2 := NULL,
274 p_document_num IN VARCHAR2 := NULL,
275 p_vendor_order_num IN VARCHAR2 := NULL,
276 p_line_id IN NUMBER := NULL,
277 p_line_location_id IN NUMBER := NULL,
278 p_distribution_id IN NUMBER := NULL,
279 p_mode IN VARCHAR2,
280 p_lock_flag IN VARCHAR2 := 'N',
281 p_calling_module IN VARCHAR2 := NULL, -- PDOI Rewrite R12
282 p_role IN VARCHAR2 := NULL, -- PDOI Rewrite R12
283 p_skip_cat_upload_chk IN VARCHAR2 := NULL, -- PDOI Rewrite R12
284 x_po_status_rec OUT NOCOPY PO_STATUS_REC_TYPE,
285 x_return_status OUT NOCOPY VARCHAR2
286 );
287
288 -- Bug 3312906 START
289 -------------------------------------------------------------------------------
290 --Start of Comments
291 --Name: check_std_po_price_updateable
292 --Function:
293 -- Checks whether price updates are allowed on this Standard PO line.
294 -- See the package body for detailed comments.
295 --End of Comments
296 -------------------------------------------------------------------------------
297 PROCEDURE check_std_po_price_updateable (
298 p_api_version IN NUMBER,
299 x_return_status OUT NOCOPY VARCHAR2,
300 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE,
301 p_from_price_break IN VARCHAR2,
302 p_add_reasons_to_msg_list IN VARCHAR2,
303 x_price_updateable OUT NOCOPY VARCHAR2,
304 x_retroactive_price_change OUT NOCOPY VARCHAR2
305 );
306
307 -------------------------------------------------------------------------------
308 --Start of Comments
309 --Name: check_rel_price_updateable
310 --Function:
311 -- Checks whether price updates are allowed on this release shipment.
312 -- See the package body for detailed comments.
313 --End of Comments
314 -------------------------------------------------------------------------------
315 PROCEDURE check_rel_price_updateable (
316 p_api_version IN NUMBER,
317 x_return_status OUT NOCOPY VARCHAR2,
318 p_line_location_id IN PO_LINE_LOCATIONS_ALL.line_location_id%TYPE,
319 p_from_price_break IN VARCHAR2,
320 p_add_reasons_to_msg_list IN VARCHAR2,
321 x_price_updateable OUT NOCOPY VARCHAR2,
322 x_retroactive_price_change OUT NOCOPY VARCHAR2
323 );
324 -- Bug 3312906 END
325
326 -- <Complex Work R12 START>
327 -------------------------------------------------------------------------------
328 --Start of Comments
329 --Name: check_payitem_price_updateable
330 --Function:
331 -- Checks whether price updates are allowed on this Complex Work PO Pay Item.
332 -- Note: NO validation is done to verify that p_line_location_id
333 -- corresponds to a Complex Work pay item!
334 --Parameters:
335 --IN:
336 --p_api_version
337 -- API version expected by the caller
338 --p_line_location_id
339 -- ID of a Complex Work pay item.
340 --p_add_reasons_to_msg_list
341 -- (Only applies if x_price_updateable = PO_CORE_S.G_PARAMETER_NO.)
342 -- If PO_CORE_S.G_PARAMETER_NO, the API will add the reasons why price updates
343 -- are not allowed to the standard API message list. Otherwise, the API
344 -- will not add the reasons to the message list.
345 --OUT:
346 --x_return_status
347 -- FND_API.G_RET_STS_SUCCESS if the API completed successfully.
348 -- FND_API.G_RET_STS_ERROR if there was an error.
349 -- FND_API.G_RET_STS_UNEXP_ERROR if there was an unexpected error.
350 --x_price_updateable
351 -- PO_CORE_S.G_PARAMETER_YES if price updates are allowed on this shipment,
352 -- PO_CORE_S.G_PARAMETER_NO otherwise
353 --End of Comments
354 -------------------------------------------------------------------------------
355 PROCEDURE check_payitem_price_updateable (
356 p_api_version IN NUMBER
357 , p_line_location_id IN NUMBER
358 , p_add_reasons_to_msg_list IN VARCHAR2
359 , x_return_status OUT NOCOPY VARCHAR2
360 , x_price_updateable OUT NOCOPY VARCHAR2
361 );
362 -- <Complex Work R12 END>
363
364 -- Bug 5560980 START
365 -------------------------------------------------------------------------------
366 --Start of Comments
367 --Name: po_combined_submission_check
368 --Function:
369 -- Call both Copy_Doc submission check and regular po submission check,
370 -- then combine the two online reports to one and return the single report id
371 -- See the package body for detailed comments.
372 --End of Comments
373 -------------------------------------------------------------------------------
374
375 PROCEDURE po_combined_submission_check(
376 p_api_version IN NUMBER
377 , p_action_requested IN VARCHAR2
378 , p_document_type IN VARCHAR2
379 , p_document_subtype IN VARCHAR2
380 , p_document_level IN VARCHAR2
381 , p_document_level_id IN NUMBER
382 , p_draft_id IN NUMBER -- CLM Approval
383 , p_org_id IN NUMBER
384 , p_requested_changes IN PO_CHANGES_REC_TYPE
385 , p_check_asl IN BOOLEAN
386 , p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
387 , p_origin_doc_id IN NUMBER := NULL -- Bug#5462677
388 -- parameters for combination
389 , p_from_header_id IN NUMBER
390 , p_from_type_lookup_code IN VARCHAR2
391 , p_po_header_id IN NUMBER
392 , p_online_report_id IN NUMBER
393 , p_sob_id IN NUMBER
394 , x_return_status OUT NOCOPY VARCHAR2
395 , x_sub_check_status OUT NOCOPY VARCHAR2
396 , x_has_warnings OUT NOCOPY VARCHAR2 -- bug3574165
397 , x_msg_data OUT NOCOPY VARCHAR2
398 , x_online_report_id OUT NOCOPY NUMBER
399 , x_doc_check_error_record OUT NOCOPY doc_check_Return_Type
400 );
401 -- Bug 5560980 END
402
403 END PO_DOCUMENT_CHECKS_GRP;