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