1 PACKAGE PO_DOCUMENT_CHECKS_PVT AS
2 /* $Header: POXVDCKS.pls 120.5.12010000.2 2008/11/12 21:44:56 rarajar 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
66 --<DropShip FPJ Start>
67 --The following constants are possible values
68 -- for p_mode input parameter of po_status_check Procedure
69 G_CHECK_UPDATEABLE CONSTANT VARCHAR2(30) := 'CHECK_UPDATEABLE';
70 G_GET_STATUS CONSTANT VARCHAR2(30) := 'GET_STATUS';
71 G_CHECK_RESERVABLE CONSTANT VARCHAR2(30) := 'CHECK_RESERVABLE';
72 G_CHECK_UNRESERVABLE CONSTANT VARCHAR2(30) := 'CHECK_UNRESERVABLE';
73 --<DropShip FPJ End>
74
75
76 -----------------------------------------------------------------------------
77 -- Public subprograms
78 -----------------------------------------------------------------------------
79
80
81
82
83 PROCEDURE po_submission_check(
84 p_api_version IN NUMBER
85 , p_action_requested IN VARCHAR2
86 , p_document_type IN VARCHAR2
87 , p_document_subtype IN VARCHAR2
88 , p_document_level IN VARCHAR2
89 , p_document_level_id IN NUMBER
90 , p_requested_changes IN PO_CHANGES_REC_TYPE
91 , p_check_asl IN BOOLEAN
92 , p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
93 , p_origin_doc_id IN NUMBER := NULL --Bug#5462677
94 , x_return_status OUT NOCOPY VARCHAR2
95 , x_sub_check_status OUT NOCOPY VARCHAR2
96 , x_has_warnings OUT NOCOPY VARCHAR2 -- bug3574165
97 , x_msg_data OUT NOCOPY VARCHAR2
98 , x_online_report_id OUT NOCOPY NUMBER
99 , x_doc_check_error_record OUT NOCOPY doc_check_Return_Type
100 );
101
102
103 PROCEDURE post_submission_check -- <2757450>
104 ( p_api_version IN NUMBER
105 , p_document_type IN VARCHAR2
106 , p_document_subtype IN VARCHAR2
107 , p_document_id IN NUMBER
108 , x_return_status OUT NOCOPY VARCHAR2
109 , x_sub_check_status OUT NOCOPY VARCHAR2
110 , x_online_report_id OUT NOCOPY NUMBER
111 );
112
113 --Bug 4943365 Removed the check_asl procedure because blankets
114 --should not do the asl checks.
115 --PROCEDURE check_asl
116
117 --For REQUISTIONS
118 PROCEDURE check_requisitions(p_document_id IN NUMBER,
119 p_online_report_id IN NUMBER,
120 p_user_id IN NUMBER,
121 p_login_id IN NUMBER,
122 p_sequence IN OUT NOCOPY NUMBER,
123 x_return_status OUT NOCOPY VARCHAR2);
124
125 --For RELEASES
126 PROCEDURE check_releases(p_document_id IN NUMBER,
127 p_online_report_id IN NUMBER,
128 p_user_id IN NUMBER,
129 p_login_id IN NUMBER,
130 p_sequence IN OUT NOCOPY NUMBER,
131 x_return_status OUT NOCOPY VARCHAR2);
132
133 --For RELEASES, PO
134 PROCEDURE check_po_rel_reqprice(p_document_type IN VARCHAR2,
135 p_document_id IN NUMBER,
136 p_online_report_id IN NUMBER,
137 p_user_id IN NUMBER,
138 p_login_id IN NUMBER,
139 p_sequence IN OUT NOCOPY NUMBER,
140 x_return_status OUT NOCOPY VARCHAR2);
141
142 --For PO,PA: Header Checks
143 PROCEDURE check_po_pa_header(p_document_id IN NUMBER,
144 p_online_report_id IN NUMBER,
145 p_user_id IN NUMBER,
146 p_login_id IN NUMBER,
147 p_sequence IN OUT NOCOPY NUMBER,
148 x_return_status OUT NOCOPY VARCHAR2);
149
150 --For PO
151 PROCEDURE check_po( p_document_id IN NUMBER,
152 p_online_report_id IN NUMBER,
153 p_user_id IN NUMBER,
154 p_login_id IN NUMBER,
155 p_sequence IN OUT NOCOPY NUMBER,
156 x_return_status OUT NOCOPY VARCHAR2);
157
158 --For Planned POs and Blanket PAs
159 PROCEDURE check_planned_po_blanket_pa(p_document_id IN NUMBER,
160 p_online_report_id IN NUMBER,
161 p_user_id IN NUMBER,
162 p_login_id IN NUMBER,
163 p_sequence IN OUT NOCOPY NUMBER,
164 x_return_status OUT NOCOPY VARCHAR2);
165
166 --For Standard PO
167 --Includes Global Agreement Reference checks, Consigned Inventory checks
168 PROCEDURE check_standard_po(p_document_id IN NUMBER,
169 p_online_report_id IN NUMBER,
170 p_user_id IN NUMBER,
171 p_login_id IN NUMBER,
172 p_sequence IN OUT NOCOPY NUMBER,
173 x_return_status OUT NOCOPY VARCHAR2);
174
175 --For Standard POs which reference Global Agreement called from
176 --check_standard_po
177 PROCEDURE check_std_global_ref(p_document_id IN NUMBER,
178 p_online_report_id IN NUMBER,
179 p_user_id IN NUMBER,
180 p_login_id IN NUMBER,
181 p_sequence IN OUT NOCOPY NUMBER,
182 x_return_status OUT NOCOPY VARCHAR2);
183
184 --For Standard POs which have consigned reference called from
185 --check_standard_po
186 PROCEDURE check_std_consigned_ref(p_document_id IN NUMBER,
187 p_online_report_id IN NUMBER,
188 p_user_id IN NUMBER,
189 p_login_id IN NUMBER,
190 p_sequence IN OUT NOCOPY NUMBER,
191 x_return_status OUT NOCOPY VARCHAR2);
192
193 -- <GC FPJ START>
194 -- For Standard POs which have Global contract reference. Called from
195 -- check_standard_po
196
197 PROCEDURE check_std_gc_ref
198 ( p_document_id IN NUMBER,
199 p_online_report_id IN NUMBER,
200 p_user_id IN NUMBER,
201 p_login_id IN NUMBER,
202 x_sequence IN OUT NOCOPY NUMBER,
203 x_return_status OUT NOCOPY VARCHAR2
204 );
205
206 -- <GC FPJ END>
207
208 --For Contract PA
209 PROCEDURE check_contract_agreement(p_document_id IN NUMBER,
210 p_online_report_id IN NUMBER,
211 p_user_id IN NUMBER,
212 p_login_id IN NUMBER,
213 p_sequence IN OUT NOCOPY NUMBER,
214 x_return_status OUT NOCOPY VARCHAR2);
215
216 --For POs, Reqs, Releases
217 PROCEDURE do_cbc_related_validations(p_document_type IN VARCHAR2,
218 p_document_subtype IN VARCHAR2,
219 p_document_id IN NUMBER,
220 p_online_report_id IN NUMBER,
221 p_user_id IN NUMBER,
222 p_login_id IN NUMBER,
223 p_sequence IN OUT NOCOPY NUMBER,
224 x_return_status OUT NOCOPY VARCHAR2);
225
226
227 --For populating global temp from po_headers
228 PROCEDURE populate_po_headers_gt(p_document_id IN number,
229 x_return_status OUT NOCOPY VARCHAR2);
230
231
232 --For populating global temp from po_requisition_headers
233 PROCEDURE populate_req_headers_gt(p_document_id IN number,
234 x_return_status OUT NOCOPY VARCHAR2);
235
236 --For populating global temp from po_requisition_lines
237 PROCEDURE populate_req_lines_gt(p_document_id IN number,
238 x_return_status OUT NOCOPY VARCHAR2);
239
240 --For populating global temp from po_releases
241 PROCEDURE populate_releases_gt(p_document_id IN number,
242 x_return_status OUT NOCOPY VARCHAR2);
243
244
245 --For updating the global temp tables with requested changes
246 PROCEDURE update_global_temp_tables(p_document_type IN VARCHAR2,
247 p_document_subtype IN VARCHAR2,
248 p_document_id IN NUMBER,
249 -- <PO_CHANGE_API FPJ> Renamed the type to PO_CHANGES_REC_TYPE:
250 p_requested_changes IN PO_CHANGES_REC_TYPE,
251 x_return_status OUT NOCOPY VARCHAR2);
252
253 -- <FPJ, Refactor Security API START>
254
255 /**
256 * Public Procedure: PO_Security_Check
257 * Requires:
258 * IN PARAMETERS:
259 * p_api_version: Version number of API that caller expects. It
260 * should match the l_api_version defined in the
261 * procedure
262 * p_query_table: Table you want to check
263 * p_owner_id_column: Owner id column of the table
264 * p_employee_id: User id to access the document
265 * p_minimum_access_level: Minimum access level to the document
266 * p_document_type: The type of the document to perform
267 * the security check on
268 * p_document_subtype: The subtype of the document.
269 * p_type_clause: The document type clause to be used in
270 * constructing where clause
271 *
272 * Modifies: None
273 * Effects: This procedure builds dynamic WHERE clause fragments based on
274 * document security parameters.
275 * Returns:
276 * x_return_status: FND_API.G_RET_STS_SUCCESS if API succeeds
277 * FND_API.G_RET_STS_ERROR if API fails
278 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
279 * x_msg_data: Contains error msg in case x_return_status returned
280 * FND_API.G_RET_STS_ERROR or
281 * FND_API.G_RET_STS_UNEXP_ERROR
282 * x_where_clause: The constructed where clause
283 */
284
285 PROCEDURE PO_Security_Check (p_api_version IN Number,
286 p_query_table IN Varchar2,
287 p_owner_id_column IN Varchar2,
288 p_employee_id IN Varchar2,
289 p_minimum_access_level IN Varchar2,
290 p_document_type IN Varchar2,
291 p_document_subtype IN Varchar2,
292 p_type_clause IN Varchar2,
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_msg_data OUT NOCOPY VARCHAR2,
295 x_where_clause OUT NOCOPY VARCHAR2);
296
297 -- <FPJ Refactor Security API END>
298
299 -- The new procedure po_status_check added in DropShip FPJ project
300
301 -- Detailed comments maintained in the Package Body PO_DOCUMENT_CHECKS_PVT.po_status_check
302 PROCEDURE po_status_check (
303 p_api_version IN NUMBER,
304 p_header_id IN PO_TBL_NUMBER,
305 p_release_id IN PO_TBL_NUMBER,
306 p_document_type IN PO_TBL_VARCHAR30,
307 p_document_subtype IN PO_TBL_VARCHAR30,
308 p_document_num IN PO_TBL_VARCHAR30,
309 p_vendor_order_num IN PO_TBL_VARCHAR30,
310 p_line_id IN PO_TBL_NUMBER,
311 p_line_location_id IN PO_TBL_NUMBER,
312 p_distribution_id IN PO_TBL_NUMBER,
313 p_mode IN VARCHAR2,
314 p_lock_flag IN VARCHAR2 := 'N',
315 p_calling_module IN VARCHAR2 := NULL, -- PDOI Rewrite R12
316 p_role IN VARCHAR2 := NULL, -- PDOI Rewrite R12
317 p_skip_cat_upload_chk IN VARCHAR2 := NULL, -- PDOI Rewrite R12
318 x_po_status_rec OUT NOCOPY PO_STATUS_REC_TYPE,
319 x_return_status OUT NOCOPY VARCHAR2
320 );
321
322 -- <CONTERMS FPJ>
323 -- new procedure to call contract terms validation during submission check
324 PROCEDURE check_terms(
325 p_document_id IN NUMBER,
326 p_document_type IN VARCHAR2,
327 p_document_subtype IN VARCHAR2,
328 p_online_report_id IN NUMBER,
329 p_user_id IN NUMBER,
330 p_login_id IN NUMBER,
331 p_sequence IN OUT NOCOPY NUMBER,
332 x_return_status OUT NOCOPY VARCHAR2);
333
334 -- <JFMIP Vendor Registration FPJ>
335 -- For Release, PO, PA: checks if vendor site registration is valid
336 PROCEDURE check_vendor_site_ccr_regis(
337 p_document_id IN NUMBER,
338 p_online_report_id IN NUMBER,
339 p_user_id IN NUMBER,
340 p_login_id IN NUMBER,
341 p_sequence IN OUT NOCOPY NUMBER,
342 x_return_status OUT NOCOPY VARCHAR2);
343
344 --<LCM project start>
345 -- Procedure to set the lcm flag in po shipments and distributions
346 PROCEDURE Set_LCM_Flag (p_line_location_id IN NUMBER,
347 p_doc_check_status IN VARCHAR2,
348 x_return_status OUT nocopy VARCHAR2);
349 --<LCM project end>
350
351 --<BUG 4624736 START>
352 -- Checks if the pay item's price can be updated.
353 -- NOTE: does not verify that line location is in fact
354 -- a pay item.
355 FUNCTION is_pay_item_price_updateable (
356 p_line_location_id IN NUMBER
357 , p_add_reasons_to_msg_list IN VARCHAR2)
358 RETURN BOOLEAN;
359
360 FUNCTION chk_unv_invoices(p_invoice_type IN VARCHAR2 DEFAULT 'BOTH',
361 p_po_header_id IN NUMBER,
362 p_po_release_id IN NUMBER DEFAULT NULL,
363 p_po_line_id IN NUMBER DEFAULT NULL,
364 p_line_location_id IN NUMBER DEFAULT NULL,
365 p_po_distribution_id IN NUMBER DEFAULT NULL,
366 p_invoice_id IN NUMBER DEFAULT NULL,
367 p_calling_sequence IN VARCHAR2) RETURN NUMBER;
368
369 --<BUG 4624736 END>
370
371 END PO_DOCUMENT_CHECKS_PVT;
[Home] [Help]