[Home] [Help]
PACKAGE BODY: APPS.PO_SOURCING_PVT
Source
1 PACKAGE BODY PO_SOURCING_PVT AS
2 /* $Header: POXVCPAB.pls 120.11.12020000.2 2013/02/10 23:54:07 vegajula ship $*/
3
4
5 ---
6 --- +=======================================================================+
7 --- | Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA |
8 --- | All rights reserved. |
9 --- +=======================================================================+
10 --- |
11 --- | FILENAME
12 --- | POXVCPAB.pls
13 --- |
14 --- |
15 --- | DESCRIPTION
16 --- |
17 --- | This package contains procedures called from the sourcing
18 --- | to create CPA in PO
19 --- |
20 --- | HISTORY
21 --- |
22 --- | 30-Sep-2004 rbairraj Initial version
23 --- |
24 --- +=======================================================================+
25 ---
26
27 --------------------------------------------------------------------------------
28
29 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_SOURCING_PVT';
30 g_log_head CONSTANT VARCHAR2(30) := 'po.plsql.PO_SOURCING_PVT.';
31 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
32 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
33
34
35 --SQL WHAT: Selects the records from the po_headers_interface table
36 --SQL WHY: These values are used in creating a Purchase Order
37 --SQl Join:None
38
39 CURSOR g_interface_cursor(p_interface_header_id NUMBER) IS
40 SELECT phi.interface_header_id interface_header_id,
41 phi.interface_source_code interface_source_code,
42 phi.document_type_code,
43 phi.batch_id batch_id,
44 phi.action action,
45 phi.document_subtype document_subtype,
46 phi.document_num document_num,
47 phi.po_header_id po_header_id,
48 phi.agent_id agent_id,
49 phi.vendor_id vendor_id,
50 phi.vendor_site_id vendor_site_id,
51 phi.vendor_contact_id vendor_contact_id,
52 phi.ship_to_location_id ship_to_location_id,
53 phi.bill_to_location_id bill_to_location_id,
54 phi.terms_id terms_id,
55 phi.freight_carrier ship_via_lookup_code,
56 phi.fob fob_lookup_code,
57 phi.pay_on_code pay_on_code,
58 phi.freight_terms freight_terms_lookup_code,
59 phi.creation_date creation_date,
60 phi.created_by created_by,
61 phi.last_update_date last_update_date,
62 phi.last_updated_by last_updated_by,
63 phi.last_update_login last_update_login,
64 phi.revision_num revision_num,
65 phi.print_count print_count,
66 phi.closed_code h_closed_code,
67 phi.frozen_flag frozen_flag,
68 phi.firm_flag h_firm_status_lookup_code,
69 phi.confirming_order_flag confirming_order_flag,
70 phi.acceptance_required_flag acceptance_required_flag,
71 phi.currency_code h_currency_code,
72 phi.rate_type_code h_rate_type,
73 phi.rate_date h_rate_date,
74 phi.rate h_rate,
75 phi.amount_agreed,
76 phi.effective_date,
77 phi.expiration_date,
78 phi.amount_limit,
79 phi.global_agreement_flag,
80 phi.shipping_control,
81 phi.org_id,
82 phi.clm_award_type,
83 phi.clm_standard_form,
84 phi.clm_document_format,
85 phi.umbrella_program_id --umbrella program
86 FROM po_headers_interface phi
87 WHERE phi.interface_header_id = p_interface_header_id;
88
89 -- Type declaration for System Parameters structure
90 TYPE system_parameters_rec_type IS RECORD
91 (currency_code GL_SETS_OF_BOOKS.currency_code%type,
92 coa_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE,
93 po_encumbrance_flag FINANCIALS_SYSTEM_PARAMETERS.purch_encumbrance_flag%TYPE,
94 req_encumbrance_flag FINANCIALS_SYSTEM_PARAMETERS.req_encumbrance_flag%TYPE,
95 sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE,
96 ship_to_location_id FINANCIALS_SYSTEM_PARAMETERS.ship_to_location_id%TYPE,
97 bill_to_location_id FINANCIALS_SYSTEM_PARAMETERS.bill_to_location_id%TYPE,
98 fob_lookup_code FINANCIALS_SYSTEM_PARAMETERS.fob_lookup_code%type,
99 freight_terms_lookup_code
100 FINANCIALS_SYSTEM_PARAMETERS.freight_terms_lookup_code%type,
101 terms_id PO_SYSTEM_PARAMETERS.term_id%TYPE,
102 default_rate_type PO_SYSTEM_PARAMETERS.default_rate_type%type,
103 taxable_flag PO_SYSTEM_PARAMETERS.taxable_flag%TYPE,
104 receiving_flag PO_SYSTEM_PARAMETERS.receiving_flag%TYPE,
105 enforce_buyer_name_flag PO_SYSTEM_PARAMETERS.enforce_buyer_name_flag%TYPE,
106 enforce_buyer_auth_flag PO_SYSTEM_PARAMETERS.enforce_buyer_authority_flag%TYPE,
107 line_type_id PO_SYSTEM_PARAMETERS.line_type_id%TYPE := null,
108 manual_po_num_type PO_SYSTEM_PARAMETERS.manual_po_num_type%TYPE,
109 po_num_code PO_SYSTEM_PARAMETERS.user_defined_po_num_code%TYPE,
110 price_type_lookup_code PO_SYSTEM_PARAMETERS.price_type_lookup_code%TYPE,
111 invoice_close_tolerance PO_SYSTEM_PARAMETERS.invoice_close_tolerance%TYPE,
112 receive_close_tolerance PO_SYSTEM_PARAMETERS.receive_close_tolerance%TYPE,
113 security_structure_id PO_SYSTEM_PARAMETERS.security_position_structure_id%TYPE,
114 expense_accrual_code PO_SYSTEM_PARAMETERS.price_type_lookup_code%TYPE,
115 inventory_organization_id FINANCIALS_SYSTEM_PARAMETERS.inventory_organization_id%TYPE,
116 rev_sort_ordering FINANCIALS_SYSTEM_PARAMETERS.revision_sort_ordering%TYPE,
117 min_rel_amount PO_SYSTEM_PARAMETERS.min_release_amount%TYPE,
118 notify_blanket_flag PO_SYSTEM_PARAMETERS.notify_if_blanket_flag%TYPE,
119 budgetary_control_flag GL_SETS_OF_BOOKS.enable_budgetary_control_flag%TYPE,
120 user_defined_req_num_code PO_SYSTEM_PARAMETERS.user_defined_req_num_code%type,
121 rfq_required_flag PO_SYSTEM_PARAMETERS.rfq_required_flag%TYPE,
122 manual_req_num_type PO_SYSTEM_PARAMETERS.manual_req_num_type%type,
123 enforce_full_lot_qty PO_SYSTEM_PARAMETERS.enforce_full_lot_quantities%type,
124 disposition_warning_flag PO_SYSTEM_PARAMETERS.disposition_warning_flag%TYPE,
125 reserve_at_completion_flag FINANCIALS_SYSTEM_PARAMETERS.reserve_at_completion_flag%TYPE,
126 user_defined_rcpt_num_code
127 PO_SYSTEM_PARAMETERS.user_defined_receipt_num_code%type,
128 manual_rcpt_num_type PO_SYSTEM_PARAMETERS.manual_receipt_num_type%type,
129 use_positions_flag FINANCIALS_SYSTEM_PARAMETERS.use_positions_flag%TYPE,
130 default_quote_warning_delay PO_SYSTEM_PARAMETERS.default_quote_warning_delay%TYPE,
131 inspection_required_flag PO_SYSTEM_PARAMETERS.inspection_required_flag%TYPE,
132 user_defined_quote_num_code
133 PO_SYSTEM_PARAMETERS.user_defined_quote_num_code%type,
134 manual_quote_num_type PO_SYSTEM_PARAMETERS.manual_quote_num_type%type,
135 user_defined_rfq_num_code
136 PO_SYSTEM_PARAMETERS.user_defined_rfq_num_code%type,
137 manual_rfq_num_type PO_SYSTEM_PARAMETERS.manual_rfq_num_type%type,
138 ship_via_lookup_code FINANCIALS_SYSTEM_PARAMETERS.ship_via_lookup_code%type,
139 qty_rcv_tolerance rcv_parameters.qty_rcv_tolerance%TYPE,
140 period_name GL_PERIOD_STATUSES.period_name%type);
141
142 -- Type declaration for Vendor defaults structure
143 TYPE vendor_defaults_rec_type IS RECORD
144 (vendor_id PO_VENDORS.vendor_id%TYPE := null,
145 -- Bug# 4546121:All columns that referred to the obsolete columns in po_vendors have
146 -- been modified to point to PO_HEADERS_ALL type.
147 ship_to_location_id PO_HEADERS_ALL.ship_to_location_id%TYPE := null,
148 bill_to_location_id PO_HEADERS_ALL.bill_to_location_id%TYPE := null,
149 ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE := null,
150 fob_lookup_code PO_HEADERS_ALL.fob_lookup_code%TYPE := null,
151 pay_on_code PO_VENDOR_SITES_ALL.pay_on_code%TYPE := null,
152 freight_terms_lookup_code PO_HEADERS_ALL.freight_terms_lookup_code%TYPE := null,
153 terms_id po_vendors.terms_id%TYPE := null,
154 type_1099 PO_VENDORS.type_1099%TYPE := null,
155 hold_flag PO_VENDORS.hold_flag%TYPE := null,
156 invoice_currency_code PO_VENDORS.invoice_currency_code%TYPE := null,
157 receipt_required_flag PO_VENDORS.receipt_required_flag%TYPE := null,
158 num_1099 PO_VENDORS.num_1099%TYPE := null,
159 vat_registration_num PO_VENDORS.vat_registration_num%TYPE := NULL,
160 /*Bug 10203569 the variable vat_registration_num was initially declared as number but
161 in the view po_vendors this variable is a varchar, hence ORA-06502: PL/SQL:
162 numeric or value error: character to number conversion error. was encountered.
163 Changed the data type to PO_VENDORS.vat_registration_num%TYPE := NULL*/
164 inspection_required_flag PO_VENDORS.inspection_required_flag%TYPE := null,
165 invoice_match_option PO_VENDORS.match_option%TYPE := null,
166 shipping_control PO_VENDOR_SITES.shipping_control%TYPE := NULL
167 );
168
169 -- Type declaration for WHO information structure
170 TYPE who_rec_type IS RECORD
171 (user_id NUMBER := 0,
172 login_id NUMBER := 0,
173 resp_id NUMBER := 0);
174
175 g_cpa_csr g_interface_cursor%ROWTYPE;
176 g_who_rec who_rec_type;
177 g_params_rec system_parameters_rec_type;
178 g_vendor_rec vendor_defaults_rec_type;
179 g_vendor_default_rec vendor_defaults_rec_type;
180 g_progress VARCHAR2(2000) := '000';
181
182
183 -- CLM CPA Creation Flow
184 g_is_clm_flow VARCHAR2(1):= 'N';
185 g_style_id PO_HEADERS_INTERFACE.style_id%TYPE;
186
187
188 -------------------------------------------------------------------------------
189 --Start of Comments
190 --Name: GET_SYSTEM_DEFAULTS
191 --Pre-reqs:
192 -- None
193 --Modifies:
194 -- None
195 --Locks:
196 -- None.
197 --Function:
198 -- This procedure is called for getting the default po paramters
199 --Parameters:
200 --IN:
201 --p_interface_header_id
202 -- Id that uniquely identifies a row in po_headers_interface table
203 --OUT:
204 -- None
205 --Notes:
206 -- None
207 --Testing:
208 -- None
209 --End of Comments
210 -------------------------------------------------------------------------------
211
212 PROCEDURE get_system_defaults(p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%TYPE) IS
213 x_date date;
214 l_api_name CONSTANT VARCHAR2(30) := 'get_system_defaults';
215 BEGIN
216 IF g_debug_stmt THEN
217 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
218 END IF;
219
220 -- Get WHO column values
221 g_who_rec.user_id := nvl(fnd_global.user_id,0);
222 g_who_rec.login_id := nvl(fnd_global.login_id,0);
223 g_who_rec.resp_id := nvl(fnd_global.resp_id,0);
224
225 g_progress:='010';
226
227 -- Get system defaults
228 PO_CORE_S.get_po_parameters(
229 x_currency_code => g_params_rec.currency_code,
230 x_coa_id => g_params_rec.coa_id,
231 x_po_encumberance_flag => g_params_rec.po_encumbrance_flag,
232 x_req_encumberance_flag => g_params_rec.req_encumbrance_flag,
233 x_sob_id => g_params_rec.sob_id,
234 x_ship_to_location_id => g_params_rec.ship_to_location_id,
235 x_bill_to_location_id => g_params_rec.bill_to_location_id,
236 x_fob_lookup_code => g_params_rec.fob_lookup_code,
237 x_freight_terms_lookup_code => g_params_rec.freight_terms_lookup_code,
238 x_terms_id => g_params_rec.terms_id,
239 x_default_rate_type => g_params_rec.default_rate_type,
240 x_taxable_flag => g_params_rec.taxable_flag,
241 x_receiving_flag => g_params_rec.receiving_flag,
242 x_enforce_buyer_name_flag => g_params_rec.enforce_buyer_name_flag,
243 x_enforce_buyer_auth_flag => g_params_rec.enforce_buyer_auth_flag,
244 x_line_type_id => g_params_rec.line_type_id,
245 x_manual_po_num_type => g_params_rec.manual_po_num_type,
246 x_po_num_code => g_params_rec.po_num_code,
247 x_price_lookup_code => g_params_rec.price_type_lookup_code,
248 x_invoice_close_tolerance => g_params_rec.invoice_close_tolerance,
249 x_receive_close_tolerance => g_params_rec.receive_close_tolerance,
250 x_security_structure_id => g_params_rec.security_structure_id,
251 x_expense_accrual_code => g_params_rec.expense_accrual_code,
252 x_inv_org_id => g_params_rec.inventory_organization_id,
253 x_rev_sort_ordering => g_params_rec.rev_sort_ordering,
254 x_min_rel_amount => g_params_rec.min_rel_amount,
255 x_notify_blanket_flag => g_params_rec.notify_blanket_flag,
256 x_budgetary_control_flag => g_params_rec.budgetary_control_flag,
257 x_user_defined_req_num_code => g_params_rec.user_defined_req_num_code,
258 x_rfq_required_flag => g_params_rec.rfq_required_flag,
259 x_manual_req_num_type => g_params_rec.manual_req_num_type,
260 x_enforce_full_lot_qty => g_params_rec.enforce_full_lot_qty,
261 x_disposition_warning_flag => g_params_rec.disposition_warning_flag,
262 x_reserve_at_completion_flag => g_params_rec.reserve_at_completion_flag,
263 x_user_defined_rcpt_num_code => g_params_rec.user_defined_rcpt_num_code,
264 x_manual_rcpt_num_type => g_params_rec.manual_rcpt_num_type,
265 x_use_positions_flag => g_params_rec.use_positions_flag,
266 x_default_quote_warning_delay => g_params_rec.default_quote_warning_delay,
267 x_inspection_required_flag => g_params_rec.inspection_required_flag,
268 x_user_defined_quote_num_code => g_params_rec.user_defined_quote_num_code,
269 x_manual_quote_num_type => g_params_rec.manual_quote_num_type,
270 x_user_defined_rfq_num_code => g_params_rec.user_defined_rfq_num_code,
271 x_manual_rfq_num_type => g_params_rec.manual_rfq_num_type,
272 x_ship_via_lookup_code => g_params_rec.ship_via_lookup_code,
273 x_qty_rcv_tolerance => g_params_rec.qty_rcv_tolerance);
274
275 g_progress:='020';
276
277 IF(g_params_rec.po_encumbrance_flag = 'Y') THEN
278 PO_CORE_S.get_period_name(
279 x_sob_id => g_params_rec.sob_id,
280 x_period => g_params_rec.period_name,
281 x_gl_date => x_date);
282 END IF;
283
284 IF g_debug_stmt THEN
285 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
286 END IF;
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 g_progress:='030';
291 IF g_debug_unexp THEN
292 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
293 p_token => g_progress,
294 p_message => SQLERRM);
295 END IF;
296
297 FND_MSG_PUB.add_exc_msg(
298 p_pkg_name => 'PO_SOURCING_PVT',
299 p_procedure_name => l_api_name,
300 p_error_text => NULL);
301 END get_system_defaults;
302
303 -------------------------------------------------------------------------------
304 --Start of Comments
305 --Name: DEFAULT_CPA
306 --Pre-reqs:
307 -- None
308 --Modifies:
309 -- None
310 --Locks:
311 -- None.
312 --Function:
313 -- This procedure is called for defaulting all the values that are left null in
314 -- the headers_interface_table but are required for creating the CPA and can be
315 -- defaulted from one or more sources
316 --Parameters:
317 --IN:
318 -- None
319 --OUT:
320 --x_return_status
321 -- The standard OUT parameter giving return status of the API call.
322 -- FND_API.G_RET_STS_ERROR - for expected error
323 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
324 -- FND_API.G_RET_STS_SUCCESS - for success
325 --Notes:
326 -- None
327 --Testing:
328 -- None
329 --End of Comments
330 -------------------------------------------------------------------------------
331 PROCEDURE DEFAULT_CPA (
332 x_return_status OUT NOCOPY VARCHAR2
333 ) IS
334 l_api_name VARCHAR2(30) := 'DEFAULT_CPA';
335 l_terms_id PO_HEADERS.terms_id%TYPE;
336 l_fob_lookup_code PO_HEADERS.fob_lookup_code%TYPE;
337 l_freight_lookup_code PO_HEADERS.freight_terms_lookup_code%TYPE;
338 l_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE;
339 l_vs_terms_id PO_HEADERS.terms_id%TYPE;
340 l_vs_fob_lookup_code PO_HEADERS.fob_lookup_code%TYPE;
341 l_vs_freight_lookup_code PO_HEADERS.freight_terms_lookup_code%TYPE;
342 l_vs_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE;
343
344 BEGIN
345 -- Initialize API return status to success
346 x_return_status := FND_API.G_RET_STS_SUCCESS;
347
348 --Default all the required default po parameters from the financial system
349 --parameters,Po_system_parameters, receiving options and gl set of books.
350 --Using the procedure po_core_s.get_po_parameters
351 g_progress := '200';
352
353 IF g_debug_stmt THEN
354 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
355 p_token => g_progress,
356 p_message => 'Before calling get_system_defaults');
357 END IF;
358
359 get_system_defaults(p_interface_header_id => g_cpa_csr.interface_header_id);
360
361 g_progress := '201';
362 IF g_debug_stmt THEN
363 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
364 p_token => g_progress,
365 p_message => 'After calling get_system_defaults');
366 END IF;
367
368 IF (g_params_rec.po_num_code='AUTOMATIC') THEN
369 -- This is necessary to ensure that concurrency issues do not crop up.
370 -- We would actually pick the value from the db just before the commit action
371 g_cpa_csr.document_num := 'CPA 11.5.10+';
372 END IF;
373
374 -- Default the relevant vendor information
375 IF(g_cpa_csr.vendor_id is not null) THEN
376 g_progress := '202';
377 IF g_debug_stmt THEN
378 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
379 p_token => g_progress,
380 p_message => 'Before calling get_vendor_info');
381 END IF;
382
383 PO_VENDORS_SV.get_vendor_info (
384 X_vendor_id => g_cpa_csr.vendor_id,
385 X_ship_to_location_id => g_vendor_default_rec.ship_to_location_id,
386 X_bill_to_location_id => g_vendor_default_rec.bill_to_location_id,
387 X_ship_via_lookup_code => l_ship_via_lookup_code,
388 X_fob_lookup_code => l_fob_lookup_code,
389 X_freight_terms_lookup_code => l_freight_lookup_code,
390 X_terms_id => l_terms_id,
391 X_type_1099 => g_vendor_default_rec.type_1099,
392 X_hold_flag => g_vendor_default_rec.hold_flag,
393 X_invoice_currency_code => g_vendor_default_rec.invoice_currency_code,
394 X_receipt_required_flag => g_vendor_default_rec.receipt_required_flag,
395 X_num_1099 => g_vendor_default_rec.num_1099,
396 X_vat_registration_num => g_vendor_default_rec.vat_registration_num,
397 X_inspection_required_flag => g_vendor_default_rec.inspection_required_flag
398 );
399
400 g_progress := '203';
401 IF g_debug_stmt THEN
402 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
403 p_token => g_progress,
404 p_message => 'After calling get_vendor_info');
405 END IF;
406
407 END IF;
408 -- Default the relevant vendor site information. You would then require this for
409 -- defaulting the pay_on_code , shipping_control using the procedure
410 -- po_vendor_sites_sv.get_vendor_site_info
411 IF(g_cpa_csr.vendor_site_id is not null) THEN
412 g_progress := '204';
413 IF g_debug_stmt THEN
414 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
415 p_token => g_progress,
416 p_message => 'Before calling get_vendor_site_info');
417 END IF;
418
419 PO_VENDOR_SITES_SV.get_vendor_site_info(
420 X_vendor_site_id => g_cpa_csr.vendor_site_id,
421 X_vs_ship_to_location_id => g_vendor_rec.ship_to_location_id,
422 X_vs_bill_to_location_id => g_vendor_rec.bill_to_location_id,
423 X_vs_ship_via_lookup_code => l_vs_ship_via_lookup_code,
424 X_vs_fob_lookup_code => l_vs_fob_lookup_code,
425 X_vs_pay_on_code => g_vendor_rec.pay_on_code,
426 X_vs_freight_terms_lookup_code => l_vs_freight_lookup_code,
427 X_vs_terms_id => l_vs_terms_id,
428 X_vs_invoice_currency_code => g_vendor_rec.invoice_currency_code,
429 x_vs_shipping_control => g_vendor_rec.shipping_control
430 );
431
432 g_progress := '205';
433 IF g_debug_stmt THEN
434 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
435 p_token => g_progress,
436 p_message => 'After calling get_vendor_site_info');
437 END IF;
438
439 -- Default the pay_on_code for a CPA based
440 -- on the vendor site value.
441 if (g_vendor_rec.pay_on_code = 'RECEIPT_AND_USE') then
442 g_vendor_rec.pay_on_code := 'RECEIPT';
443 elsif (g_vendor_rec.pay_on_code = 'USE') then
444 g_vendor_rec.pay_on_code := null;
445 end if;
446 END IF; -- End of vendor_site_id is not null
447
448 -- IF the value of shipping_control in the interface table is null
449 -- then Copy the value from terms value defaulted from vendor site Information
450 IF g_cpa_csr.shipping_control IS NULL THEN
451 g_cpa_csr.shipping_control := g_vendor_rec.shipping_control;
452 END IF;
453
454 -- If global_agreement_flag is 'N' set it to null
455 IF g_cpa_csr.global_agreement_flag = 'N' THEN
456 g_cpa_csr.global_agreement_flag := NULL;
457 END IF;
458
459 -- Defaulting the terms id
460 IF g_cpa_csr.terms_id IS NULL THEN
461 if l_vs_terms_id is not null then
462 po_terms_sv.val_ap_terms(
463 X_temp_terms_id => l_vs_terms_id,
464 X_res_terms_id => g_cpa_csr.terms_id
465 );
466 end if;
467 IF g_cpa_csr.terms_id IS NULL THEN
468 if l_terms_id is not null then
469 po_terms_sv.val_ap_terms(
470 X_temp_terms_id => l_terms_id,
471 X_res_terms_id => g_cpa_csr.terms_id
472 );
473 end if;
474 IF g_cpa_csr.terms_id IS NULL THEN
475 g_cpa_csr.terms_id := g_params_rec.terms_id;
476 END IF;
477 END IF;
478 END IF;
479
480 -- Defaulting the ship_via_lookup_code
481 IF g_cpa_csr.ship_via_lookup_code IS NULL THEN
482 if l_vs_ship_via_lookup_code is not null then
483 po_vendors_sv.val_freight_carrier(
484 X_temp_ship_via => l_vs_ship_via_lookup_code,
485 X_org_id => g_params_rec.inventory_organization_id,
486 X_res_ship_via => g_cpa_csr.ship_via_lookup_code
487 );
488 end if;
489 IF g_cpa_csr.ship_via_lookup_code IS NULL THEN
490 if l_ship_via_lookup_code is not null then
491 po_vendors_sv.val_freight_carrier(
492 X_temp_ship_via => l_ship_via_lookup_code,
493 X_org_id => g_params_rec.inventory_organization_id,
494 X_res_ship_via => g_cpa_csr.ship_via_lookup_code
495 );
496 end if;
497 IF g_cpa_csr.ship_via_lookup_code IS NULL THEN
498 g_cpa_csr.ship_via_lookup_code := g_params_rec.ship_via_lookup_code;
499 END IF;
500 END IF;
501 END IF;
502
503 -- Defaulting the fob_lookup_code
504 IF g_cpa_csr.fob_lookup_code IS NULL THEN
505 if l_vs_fob_lookup_code is not null then
506 po_vendors_sv.val_fob(
507 X_temp_fob_lookup_code => l_vs_fob_lookup_code,
508 X_res_fob => g_cpa_csr.fob_lookup_code
509 );
510 end if;
511 IF g_cpa_csr.fob_lookup_code IS NULL THEN
512 if l_fob_lookup_code is not null then
513 po_vendors_sv.val_fob(
514 X_temp_fob_lookup_code => l_fob_lookup_code,
515 X_res_fob => g_cpa_csr.fob_lookup_code
516 );
517 end if;
518 IF g_cpa_csr.fob_lookup_code IS NULL THEN
519 g_cpa_csr.fob_lookup_code := g_params_rec.fob_lookup_code;
520 END IF;
521 END IF;
522 END IF;
523
524 -- Defaulting the pay_on_code
525 IF g_cpa_csr.pay_on_code IS NULL THEN
526 g_cpa_csr.pay_on_code := g_vendor_rec.pay_on_code;
527 END IF;
528
529 -- Defaulting the freight_terms_lookup_code
530 IF g_cpa_csr.freight_terms_lookup_code IS NULL THEN
531 if l_vs_freight_lookup_code is not null then
532 po_vendors_sv.val_freight_terms(
533 X_temp_freight_terms => l_vs_freight_lookup_code,
534 X_res_freight_terms => g_cpa_csr.freight_terms_lookup_code
535 );
536 end if;
537 IF g_cpa_csr.freight_terms_lookup_code IS NULL THEN
538 if l_freight_lookup_code is not null then
539 po_vendors_sv.val_freight_terms(
540 X_temp_freight_terms => l_freight_lookup_code,
541 X_res_freight_terms => g_cpa_csr.freight_terms_lookup_code
542 );
543 end if;
544 IF g_cpa_csr.freight_terms_lookup_code IS NULL THEN
545 g_cpa_csr.freight_terms_lookup_code := g_params_rec.freight_terms_lookup_code;
546 END IF;
547 END IF;
548 END IF;
549
550 g_cpa_csr.revision_num := 0;
551 g_cpa_csr.h_closed_code := 'OPEN';
552 g_cpa_csr.print_count := 0;
553 g_cpa_csr.confirming_order_flag := 'N';
554 g_cpa_csr.frozen_flag := 'N';
555
556 EXCEPTION
557 WHEN FND_API.G_EXC_ERROR THEN
558 x_return_status := FND_API.G_RET_STS_ERROR;
559 g_progress := '210';
560 IF g_debug_stmt THEN
561 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
562 p_token => g_progress,
563 p_message => 'Expected Error');
564 END IF;
565 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
567 g_progress := '211';
568 IF g_debug_unexp THEN
569 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
570 p_token => g_progress,
571 p_message => 'Unexpected Error');
572 END IF;
573 WHEN OTHERS THEN
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 g_progress := '212';
576 IF g_debug_unexp THEN
577 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
578 p_token => g_progress,
579 p_message => SQLERRM);
580 END IF;
581 FND_MSG_PUB.add_exc_msg(
582 p_pkg_name => 'PO_SOURCING_PVT',
583 p_procedure_name => l_api_name,
584 p_error_text => NULL);
585
586 END DEFAULT_CPA;
587 -------------------------------------------------------------------------------
588 --Start of Comments
589 --Name: VALIDATE_CPA
590 --Pre-reqs:
591 -- None
592 --Modifies:
593 -- None
594 --Locks:
595 -- None.
596 --Function:
597 -- validates the data in the interface table to create CPA
598 --Parameters:
599 --IN:
600 -- None
601 --OUT:
602 --x_return_status
603 -- The standard OUT parameter giving return status of the API call.
604 -- FND_API.G_RET_STS_ERROR - for expected error
605 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
606 -- FND_API.G_RET_STS_SUCCESS - for success
607 --Notes:
608 -- None
609 --Testing:
610 -- None
611 --End of Comments
612 -------------------------------------------------------------------------------
613 PROCEDURE VALIDATE_CPA (
614 x_return_status OUT NOCOPY VARCHAR2
615 ) IS
616 is_valid BOOLEAN := FALSE;
617 l_error_code VARCHAR2(30);
618 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CPA';
619 l_fob_lookup_code PO_LOOKUP_CODES.lookup_code%TYPE;
620 l_freight_terms_lookup_code PO_LOOKUP_CODES.lookup_code%TYPE;
621 l_freight_carrier ORG_FREIGHT.freight_code%TYPE;
622 l_terms_id AP_TERMS.term_id%TYPE;
623 BEGIN
624 -- Initialize API return status to success
625 x_return_status := FND_API.G_RET_STS_SUCCESS;
626
627 -- validate document number
628 IF g_cpa_csr.document_num IS NOT NULL THEN
629 IF g_cpa_csr.document_num <> 'CPA 11.5.10+' THEN
630 g_progress := '300';
631 IF g_debug_stmt THEN
632 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
633 p_token => g_progress,
634 p_message => 'Before calling PO_HEADERS_SV6.val_doc_num');
635 END IF;
636 is_valid := PO_HEADERS_SV6.val_doc_num(
637 X_doc_type => g_cpa_csr.document_type_code,
638 X_doc_num => g_cpa_csr.document_num,
639 X_user_defined_num => g_params_rec.manual_po_num_type,
640 X_user_defined_po_num_code => g_params_rec.po_num_code,
641 X_error_code => l_error_code);
642 g_progress := '301';
643 IF g_debug_stmt THEN
644 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
645 p_token => g_progress,
646 p_message => 'After calling PO_HEADERS_SV6.val_doc_num. X_error_code = '||l_error_code);
647 END IF;
648
649 IF (is_valid = FALSE ) THEN
650 IF (l_error_code = 'PO_PDOI_DOC_NUM_UNIQUE') THEN
651 g_progress := '302';
652 IF g_debug_stmt THEN
653 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
654 p_token => g_progress,
655 p_message => 'PO_PDOI_DOC_NUM_UNIQUE');
656 END IF;
657 Fnd_message.set_name('PO','PO_PDOI_DOC_NUM_UNIQUE');
658 Fnd_message.set_token( token => 'VALUE'
659 , VALUE => g_cpa_csr.document_num);
660 FND_MSG_PUB.Add;
661
662 RAISE FND_API.G_EXC_ERROR;
663
664 ELSIF (l_error_code = 'PO_PDOI_VALUE_NUMERIC') THEN
665 g_progress := '303';
666 IF g_debug_stmt THEN
667 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
668 p_token => g_progress,
669 p_message => 'PO_PDOI_VALUE_NUMERIC');
670 END IF;
671 Fnd_message.set_name('PO','PO_PDOI_VALUE_NUMERIC');
672 Fnd_message.set_token( token => 'COLUMN_NAME'
673 , VALUE => 'Document Number');
674 Fnd_message.set_token( token => 'VALUE'
675 , VALUE => g_cpa_csr.document_num);
676 FND_MSG_PUB.Add;
677 RAISE FND_API.G_EXC_ERROR;
678 ELSIF (l_error_code = 'PO_PDOI_LT_ZERO') THEN
679 g_progress := '304';
680 IF g_debug_stmt THEN
681 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
682 p_token => g_progress,
683 p_message => 'PO_PDOI_LT_ZERO');
684 END IF;
685 Fnd_message.set_name('PO','PO_PDOI_LT_ZERO');
686 Fnd_message.set_token( token => 'COLUMN_NAME'
687 , VALUE => 'Document Number');
688 Fnd_message.set_token( token => 'VALUE'
689 , VALUE => g_cpa_csr.document_num);
690 FND_MSG_PUB.Add;
691 RAISE FND_API.G_EXC_ERROR;
692 END IF;
693 END IF; -- If is_valid = FALSE
694 END IF; -- End of IF g_cpa_csr.document_num <> 'CPA 11.5.10+'
695 ELSE
696 -- This code executes when the document num creation is manual
697 -- and no data is passed from sourcing for document num
698 g_progress := '305';
699 IF g_debug_stmt THEN
700 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
701 p_token => g_progress,
702 p_message => 'PO_PDOI_COLUMN_NOT_NULL');
703 END IF;
704 Fnd_message.set_name('PO','PO_PDOI_COLUMN_NOT_NULL');
705 Fnd_message.set_token( token => 'COLUMN_NAME'
706 , VALUE => 'Document Number');
707 FND_MSG_PUB.Add;
708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
709 END IF; -- End of IF g_cpa_csr.document_num IS NOT NULL
710
711 -- Validate ship_to_location
712 IF g_cpa_csr.ship_to_location_id IS NOT NULL THEN
713 g_progress := '306';
714 IF g_debug_stmt THEN
715 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
716 p_token => g_progress,
717 p_message => 'Before calling PO_LINE_LOCATIONS_SV1.val_location_id for Ship to location');
718 END IF;
719
720 is_valid := PO_LINE_LOCATIONS_SV1.val_location_id(
721 X_location_id => g_cpa_csr.ship_to_location_id,
722 X_location_type => 'SHIP_TO');
723 g_progress := '307';
724 IF g_debug_stmt THEN
725 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
726 p_token => g_progress,
727 p_message => 'After calling PO_LINE_LOCATIONS_SV1.val_location_id for Ship to Location');
728 END IF;
729 IF (is_valid = FALSE) THEN
730 g_progress := '308';
731 IF g_debug_stmt THEN
732 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
733 p_token => g_progress,
734 p_message => 'PO_PDOI_INVALID_SHIP_LOC_ID');
735 END IF;
736 g_cpa_csr.ship_to_location_id := NULL;
737 END IF;
738 END IF; -- End of validate ship_to_location
739
740 -- Validate bill_to_location
741 IF g_cpa_csr.bill_to_location_id IS NOT NULL THEN
742 g_progress := '309';
743 IF g_debug_stmt THEN
744 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
745 p_token => g_progress,
746 p_message => 'Before calling PO_LINE_LOCATIONS_SV1.val_location_id for Bill to location');
747 END IF;
748
749 is_valid := PO_LINE_LOCATIONS_SV1.val_location_id(
750 X_location_id => g_cpa_csr.bill_to_location_id,
751 X_location_type => 'BILL_TO');
752 g_progress := '310';
753 IF g_debug_stmt THEN
754 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
755 p_token => g_progress,
756 p_message => 'After calling PO_LINE_LOCATIONS_SV1.val_location_id for Bill to location');
757 END IF;
758
759 IF (is_valid = FALSE) THEN
760 g_progress := '311';
761 IF g_debug_stmt THEN
762 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
763 p_token => g_progress,
764 p_message => 'PO_PDOI_INVALID_BILL_LOC_ID');
765 END IF;
766 g_cpa_csr.bill_to_location_id := NULL;
767 END IF;
768 END IF; -- End of validate bill_to_location
769 EXCEPTION
770 WHEN FND_API.G_EXC_ERROR THEN
771 x_return_status := FND_API.G_RET_STS_ERROR;
772 g_progress := '320';
773 IF g_debug_stmt THEN
774 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
775 p_token => g_progress,
776 p_message => 'Expected Error');
777 END IF;
778 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
779 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
780 g_progress := '321';
781 IF g_debug_unexp THEN
782 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
783 p_token => g_progress,
784 p_message => 'Unexpected Error');
785 END IF;
786 WHEN OTHERS THEN
787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
788 g_progress := '322';
789 IF g_debug_unexp THEN
790 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
791 p_token => g_progress,
792 p_message => SQLERRM);
793 END IF;
794 FND_MSG_PUB.add_exc_msg(
795 p_pkg_name => 'PO_SOURCING_PVT',
796 p_procedure_name => l_api_name,
797 p_error_text => NULL);
798 END VALIDATE_CPA;
799
800 -------------------------------------------------------------------------------
801 --Start of Comments
802 --Name: INSERT_CPA
803 --Pre-reqs:
804 -- None
805 --Modifies:
806 -- Transaction tables for the requested document
807 --Locks:
808 -- None.
809 --Function:
810 -- inserts the data into the PO_HEADERS table to create CPA
811 --Parameters:
812 --IN:
813 --p_auction_header_id
814 -- Id of the negotiation
815 --p_bid_number
816 -- Bid Number for which is negotiation is awarded
817 --p_sourcing_k_doc_type
818 -- Represents the OKC document type that would be created into a CPA
819 -- The document type that Sourcing has seeded in Contracts.
820 --p_conterms_exist_flag
821 -- Whether the sourcing document has contract template attached.
822 --p_document_creation_method
823 -- Column specific to DBI. Sourcing will pass a value of AWARD_SOURCING
824 --OUT:
825 --x_document_id
826 -- The unique identifier for the newly created document.
827 --x_document_number
828 -- The document number that would uniquely identify a document in a given organization.
829 --x_return_status
830 -- The standard OUT parameter giving return status of the API call.
831 -- FND_API.G_RET_STS_ERROR - for expected error
832 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
833 -- FND_API.G_RET_STS_SUCCESS - for success
834 --Notes:
835 -- None
836 --Testing:
837 -- None
838 --End of Comments
839 -------------------------------------------------------------------------------
840 PROCEDURE INSERT_CPA (
841 p_auction_header_id IN PON_AUCTION_HEADERS_ALL.auction_header_id%TYPE,
842 p_bid_number IN PON_BID_HEADERS.bid_number%TYPE,
843 p_sourcing_k_doc_type IN VARCHAR2,
844 p_conterms_exist_flag IN PO_HEADERS_ALL.conterms_exist_flag%TYPE,
845 p_document_creation_method IN VARCHAR2,
846 x_document_id OUT NOCOPY PO_HEADERS_ALL.po_header_id%TYPE,
847 x_document_number OUT NOCOPY PO_HEADERS_ALL.segment1%TYPE,
848 x_return_status OUT NOCOPY VARCHAR2
849 ) IS
850 l_rowid VARCHAR2(30);
851 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
852 l_document_num PO_HEADERS_INTERFACE.document_num%TYPE;
853 l_current_org PO_SYSTEM_PARAMETERS.org_id%TYPE;
854 l_org_assign_rec PO_GA_ORG_ASSIGNMENTS%ROWTYPE;
855 l_org_row_id ROWID;
856 l_return_status VARCHAR2(1);
857 l_contract_doc_type VARCHAR2(150);
858 l_contracts_call_exception EXCEPTION;
859 l_msg_data VARCHAR2(2000);
860 l_msg_count NUMBER;
861 l_manual BOOLEAN;
862 x_document_num PO_HEADERS.segment1%TYPE:=null;
863 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_CPA';
864
865 -- CLM CPA related changes
866
867 -- UDA Copy
868 x_errorcode NUMBER;
869 x_msg_count NUMBER;
870 x_msg_data VARCHAR2(2000);
871
872 -- Document numbering
873 x_doc_number VARCHAR2(2000);
874 x_err_msg VARCHAR2(2000);
875 x_template_id NUMBER;
876 x_clm_source_document_id PO_HEADERS_DRAFT_ALL.clm_source_document_id%TYPE;
877
878 l_uda_template_exception EXCEPTION;
879
880 -- Document Format
881 l_standard_form VARCHAR2(200);
882 l_document_format VARCHAR2(200);
883
884 -- AME
885 l_ame_approval_id po_headers_all.ame_approval_id%TYPE;
886 l_ame_transaction_type po_headers_all.ame_transaction_type%TYPE;
887 l_new_ame_appr_id_req varchar2(1);
888 BEGIN
889
890 -- Initialize API return status to success
891 x_return_status := FND_API.G_RET_STS_SUCCESS;
892
893 x_document_num := g_cpa_csr.document_num;
894 x_document_number := g_cpa_csr.document_num;
895
896 IF (g_params_rec.po_num_code = 'AUTOMATIC') THEN
897 l_manual := FALSE;
898 ELSE
899 l_manual := TRUE;
900 END IF;
901
902 g_progress := '400';
903 IF g_debug_stmt THEN
904 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
905 p_token => g_progress,
906 p_message => 'Before calling PO_HEADERS_PKG_S0.Insert_Row');
907 END IF;
908 l_current_org := PO_GA_PVT.get_current_org; -- <R12 MOAC>
909 PO_HEADERS_PKG_S0.Insert_Row(
910 X_Rowid => l_rowid,
911 X_Po_Header_Id => l_po_header_id,
912 X_Agent_Id => g_cpa_csr.agent_id,
913 X_Type_Lookup_Code => g_cpa_csr.document_subtype,
914 X_Last_Update_Date => g_cpa_csr.last_update_date,
915 X_Last_Updated_By => g_cpa_csr.last_updated_by,
916 X_Segment1 => x_document_num,
917 X_Summary_Flag => 'N',
918 X_Enabled_Flag => 'Y',
919 X_Segment2 => NULL,
920 X_Segment3 => NULL,
921 X_Segment4 => NULL,
922 X_Segment5 => NULL,
923 X_Start_Date_Active => NULL,
924 X_End_Date_Active => NULL,
925 X_Last_Update_Login => nvl(g_cpa_csr.last_update_login,fnd_global.login_id),
926 X_Creation_Date => g_cpa_csr.creation_date,
927 X_Created_By => g_cpa_csr.created_by,
928 X_Vendor_Id => g_cpa_csr.vendor_id,
929 X_Vendor_Site_Id => g_cpa_csr.vendor_site_id,
930 X_Vendor_Contact_Id => g_cpa_csr.vendor_contact_id,
931 X_Ship_To_Location_Id => g_cpa_csr.ship_to_location_id,
932 X_Bill_To_Location_Id => g_cpa_csr.bill_to_location_id,
933 X_Terms_Id => g_cpa_csr.terms_id,
934 X_Ship_Via_Lookup_Code => g_cpa_csr.ship_via_lookup_code,
935 X_Fob_Lookup_Code => g_cpa_csr.fob_lookup_code,
936 X_Pay_On_Code => g_cpa_csr.pay_on_code,
937 X_Freight_Terms_Lookup_Code => g_cpa_csr.freight_terms_lookup_code,
938 X_Status_Lookup_Code => NULL,
939 X_Currency_Code => g_cpa_csr.h_currency_code,
940 X_Rate_Type => g_cpa_csr.h_rate_type,
941 X_Rate_Date => nvl(g_cpa_csr.h_rate_date,trunc(sysdate)),
942 X_Rate => g_cpa_csr.h_rate,
943 X_From_Header_Id => NULL,
944 X_From_Type_Lookup_Code => NULL,
945 X_Start_Date => g_cpa_csr.effective_date,
946 X_End_Date => g_cpa_csr.expiration_date,
947 X_Blanket_Total_Amount => g_cpa_csr.amount_agreed,
948 X_Authorization_Status => NULL,
949 X_Revision_Num => g_cpa_csr.revision_num,
950 X_Revised_Date => NULL,
951 X_Approved_Flag => NULL,
952 X_Approved_Date => NULL,
953 X_Amount_Limit => nvl(g_cpa_csr.amount_limit, g_cpa_csr.amount_agreed),
954 X_Min_Release_Amount => NULL,
955 X_Note_To_Authorizer => NULL,
956 X_Note_To_Vendor => NULL,
957 X_Note_To_Receiver => NULL,
958 X_Print_Count => g_cpa_csr.print_count,
959 X_Printed_Date => NULL,
960 X_Vendor_Order_Num => NULL,
961 X_Confirming_Order_Flag => g_cpa_csr.confirming_order_flag,
962 X_Comments => NULL,
963 X_Reply_Date => NULL,
964 X_Reply_Method_Lookup_Code => NULL,
965 X_Rfq_Close_Date => NULL,
966 X_Quote_Type_Lookup_Code => NULL,
967 X_Quotation_Class_Code => NULL,
968 X_Quote_Warning_Delay_Unit => NULL,
969 X_Quote_Warning_Delay => NULL,
970 X_Quote_Vendor_Quote_Number => NULL,
971 X_Acceptance_Required_Flag => g_cpa_csr.acceptance_required_flag,
972 X_Acceptance_Due_Date => NULL,
973 X_Closed_Date => NULL,
974 X_User_Hold_Flag => NULL,
975 X_Approval_Required_Flag => NULL,
976 X_Cancel_Flag => 'N',
977 X_Firm_Status_Lookup_Code => nvl(g_cpa_csr.h_firm_status_lookup_code,'N'),
978 X_Firm_Date => NULL,
979 X_Frozen_Flag => g_cpa_csr.frozen_flag,
980 X_Global_Agreement_Flag => g_cpa_csr.global_agreement_flag,
981 X_Attribute_Category => NULL,
982 X_Attribute1 => NULL,
983 X_Attribute2 => NULL,
984 X_Attribute3 => NULL,
985 X_Attribute4 => NULL,
986 X_Attribute5 => NULL,
987 X_Attribute6 => NULL,
988 X_Attribute7 => NULL,
989 X_Attribute8 => NULL,
990 X_Attribute9 => NULL,
991 X_Attribute10 => NULL,
992 X_Attribute11 => NULL,
993 X_Attribute12 => NULL,
994 X_Attribute13 => NULL,
995 X_Attribute14 => NULL,
996 X_Attribute15 => NULL,
997 X_Closed_Code => g_cpa_csr.h_closed_code,
998 X_Ussgl_Transaction_Code => NULL,
999 X_Government_Context => NULL,
1000 X_Supply_Agreement_flag => 'N',
1001 X_Manual => l_manual,
1002 X_Price_Update_Tolerance => NULL,
1003 X_Global_Attribute_Category => NULL,
1004 X_Global_Attribute1 => NULL,
1005 X_Global_Attribute2 => NULL,
1006 X_Global_Attribute3 => NULL,
1007 X_Global_Attribute4 => NULL,
1008 X_Global_Attribute5 => NULL,
1009 X_Global_Attribute6 => NULL,
1010 X_Global_Attribute7 => NULL,
1011 X_Global_Attribute8 => NULL,
1012 X_Global_Attribute9 => NULL,
1013 X_Global_Attribute10 => NULL,
1014 X_Global_Attribute11 => NULL,
1015 X_Global_Attribute12 => NULL,
1016 X_Global_Attribute13 => NULL,
1017 X_Global_Attribute14 => NULL,
1018 X_Global_Attribute15 => NULL,
1019 X_Global_Attribute16 => NULL,
1020 X_Global_Attribute17 => NULL,
1021 X_Global_Attribute18 => NULL,
1022 X_Global_Attribute19 => NULL,
1023 X_Global_Attribute20 => NULL,
1024 p_shipping_control => g_cpa_csr.shipping_control,
1025 p_encumbrance_required_flag => NULL,
1026 p_org_id => l_current_org, -- <R12 MOAC>
1027 p_style_id => g_style_id,-- bug 10017321: Adding g_style_id while call
1028 p_umbrella_program_id => g_cpa_csr.umbrella_program_id --umbrella program
1029 );
1030
1031 x_document_id := l_po_header_id;
1032 g_progress := '401';
1033 IF g_debug_stmt THEN
1034 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1035 p_token => g_progress,
1036 p_message => 'After calling PO_HEADERS_PKG_S0.Insert_Row. po_header_id = '||l_po_header_id||': Segment1 = '||l_document_num);
1037
1038 END IF;
1039
1040 -- After insert into po_headers, insert a row into org_assignments for a global agreement
1041 IF nvl(g_cpa_csr.global_agreement_flag, 'N') = 'Y' then
1042
1043 l_current_org := PO_GA_PVT.get_current_org;
1044
1045 -- call the GA org assignments table handler to insert a row
1046 -- for the owning org into the org assignments table
1047 l_org_assign_rec.po_header_id := l_po_header_id;
1048 l_org_assign_rec.organization_id := l_current_org;
1049 l_org_assign_rec.purchasing_org_id := l_org_assign_rec.organization_id;
1050 l_org_assign_rec.enabled_flag := 'Y';
1051 l_org_assign_rec.vendor_site_id := g_cpa_csr.vendor_site_id;
1052 l_org_assign_rec.last_update_date := g_cpa_csr.last_update_date;
1053 l_org_assign_rec.last_updated_by := g_cpa_csr.last_updated_by;
1054 l_org_assign_rec.creation_date := g_cpa_csr.creation_date;
1055 l_org_assign_rec.created_by := g_cpa_csr.created_by;
1056 l_org_assign_rec.last_update_login := g_cpa_csr.last_update_login;
1057
1058 g_progress := '402';
1059 IF g_debug_stmt THEN
1060 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1061 p_token => g_progress,
1062 p_message => 'Before calling PO_GA_ORG_ASSIGN_PVT.Insert_Row');
1063 END IF;
1064
1065 PO_GA_ORG_ASSIGN_PVT.insert_row(
1066 p_init_msg_list => FND_API.g_true,
1067 x_return_status => l_return_status,
1068 p_org_assign_rec => l_org_assign_rec,
1069 x_row_id => l_org_row_id);
1070 g_progress := '403';
1071 IF g_debug_stmt THEN
1072 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1073 p_token => g_progress,
1074 p_message => 'After calling PO_GA_ORG_ASSIGN_PVT.Insert_Row');
1075 END IF;
1076
1077 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1078 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1079 RAISE FND_API.G_EXC_ERROR;
1080 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1082 END IF;
1083 END IF;
1084
1085 END IF;
1086
1087 g_progress := '404';
1088 IF g_debug_stmt THEN
1089 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1090 p_token => g_progress,
1091 p_message => 'Before calling PO_NEGOTIATIONS_SV2.copy_attachments fro PON_AUCTION_HEADERS_ALL');
1092 END IF;
1093
1094 -- Defaulting CLM specific attributes
1095 IF g_is_clm_flow = 'Y' THEN
1096
1097 po_autocreate_params.g_po_header_id:= l_po_header_id;
1098
1099 -- Getting UDA template id
1100 x_template_id := PO_UDA_DATA_UTIL.GET_TEMPLATE_ID(p_functional_area => 'PURCHASING',
1101 p_document_type => 'CONTRACT',
1102 p_document_style_id => g_style_id,
1103 p_document_level => 'HEADER',
1104 p_input_date => sysdate,
1105 x_return_status => x_return_status,
1106 x_err_msg => x_err_msg);
1107
1108 IF g_debug_stmt THEN
1109 po_debug.debug_stmt
1110 (p_log_head => g_log_head || l_api_name,
1111 p_token => g_progress,
1112 p_message => 'After getting the UDA template id: ' ||x_template_id
1113 );
1114 END IF;
1115
1116 -- Getting Standard Form and Document Format of the output document
1117 BEGIN
1118 --Validating the standard_form and document_format in interface
1119 SELECT standard_form,
1120 document_format
1121 INTO l_standard_form,
1122 l_document_format
1123 FROM po_print_form_formats
1124 WHERE NVL(inactive_date, SYSDATE+1) > SYSDATE
1125 AND standard_form = g_cpa_csr.clm_standard_form
1126 AND document_format = g_cpa_csr.clm_document_format;
1127
1128 EXCEPTION
1129 WHEN No_Data_Found THEN
1130 BEGIN
1131 --Defaulting standard_form and document_format
1132 SELECT standard_form,
1133 document_format
1134 INTO l_standard_form,
1135 l_document_format
1136 FROM po_print_form_formats
1137 WHERE NVL(inactive_date, SYSDATE+1) > SYSDATE
1138 AND default_flag = 'Y'
1139 AND style_id = g_style_id
1140 AND document_type = 'PO_IDV_STD_FORM';
1141
1142 EXCEPTION
1143 WHEN No_Data_Found THEN
1144 l_standard_form := NULL;
1145 l_document_format := NULL;
1146 WHEN OTHERS THEN
1147 l_standard_form := NULL;
1148 l_document_format := NULL;
1149 END;
1150 WHEN OTHERS THEN
1151 l_standard_form := NULL;
1152 l_document_format := NULL;
1153 END;
1154
1155
1156 -- Update CLM related attributes
1157 UPDATE PO_HEADERS_ALL
1158 SET uda_template_id = x_template_id,
1159 uda_template_date = SYSDATE,
1160 style_id = g_style_id,
1161 clm_standard_form = l_standard_form,
1162 clm_document_format = l_document_format,
1163 clm_award_type = g_cpa_csr.clm_award_type
1164 WHERE po_header_id = l_po_header_id;
1165
1166
1167
1168 -- Autocreate UDA attributes
1169 PO_UDA_AUTOCREATE_PKG.autocreate_uda_data(
1170 p_source_document => NULL , -- PR,SOL
1171 p_target_document => NULL, -- SOL,PO,IDV
1172 p_src_pk1_value => NULL,
1173 p_src_pk2_value => NULL,
1174 p_tar_pk1_value => l_po_header_id, -- PO_HEADER_ID
1175 p_tar_pk2_value => NULL, -- DRAFT_ID
1176 x_return_status => x_return_status,
1177 x_errorcode => x_errorcode,
1178 x_msg_count => x_msg_count,
1179 x_msg_data => x_msg_data);
1180
1181 IF g_debug_stmt THEN
1182 po_debug.debug_stmt
1183 (p_log_head => g_log_head || l_api_name,
1184 p_token => g_progress,
1185 p_message => 'After copying the UDA attributes'
1186 );
1187 END IF;
1188
1189 END IF; -- CLM flow
1190
1191 -- Copy attachments from negotiation header to CPA using the procedure
1192 PO_NEGOTIATIONS_SV2.copy_attachments(
1193 X_from_entity_name => 'PON_AUCTION_HEADERS_ALL',
1194 X_from_pk1_value => p_auction_header_id,
1195 X_from_pk2_value => NULL,
1196 X_from_pk3_value => NULL,
1197 X_from_pk4_value => NULL,
1198 X_from_pk5_value => NULL,
1199 X_to_entity_name => 'PO_HEADERS',
1200 X_to_pk1_value => l_po_header_id,
1201 X_to_pk2_value => NULL,
1202 X_to_pk3_value => NULL,
1203 X_to_pk4_value => NULL,
1204 X_to_pk5_value => NULL,
1205 X_created_by => g_cpa_csr.created_by,
1206 X_last_update_login => g_cpa_csr.last_update_login,
1207 X_program_application_id => NULL,
1208 X_program_id => NULL,
1209 X_request_id => NULL,
1210 X_column1 => 'NEG');
1211
1212 g_progress := '405';
1213 IF g_debug_stmt THEN
1214 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1215 p_token => g_progress,
1216 p_message => 'After calling PO_NEGOTIATIONS_SV2.copy_attachments for PON_AUCTION_HEADERS_ALL');
1217 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1218 p_token => g_progress,
1219 p_message => 'Before calling PO_NEGOTIATIONS_SV2.copy_attachments for PON_BID_HEADERS');
1220 END IF;
1221
1222 --copy attachment from bid header to cpa header
1223 PO_NEGOTIATIONS_SV2.copy_attachments(
1224 X_from_entity_name => 'PON_BID_HEADERS',
1225 X_from_pk1_value => p_auction_header_id,
1226 X_from_pk2_value => p_bid_number,
1227 X_from_pk3_value => '',
1228 X_from_pk4_value => '',
1229 X_from_pk5_value => '',
1230 X_to_entity_name => 'PO_HEADERS',
1231 X_to_pk1_value => l_po_header_id,
1232 X_to_pk2_value => '',
1233 X_to_pk3_value => '',
1234 X_to_pk4_value => '',
1235 X_to_pk5_value => '',
1236 X_created_by => g_cpa_csr.created_by,
1237 X_last_update_login => g_cpa_csr.last_update_login,
1238 X_program_application_id => '',
1239 X_program_id => '',
1240 X_request_id => NULL,
1241 X_column1 => 'NEG');
1242
1243 g_progress := '406';
1244 IF g_debug_stmt THEN
1245 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1246 p_token => g_progress,
1247 p_message => 'After calling PO_NEGOTIATIONS_SV2.copy_attachments for PON_BID_HEADERS');
1248 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1249 p_token => g_progress,
1250 p_message => 'Before calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_BID_BUYER_NOTES');
1251 END IF;
1252
1253 -- build and attach bid notes as internal to PO attachments on cpa header.
1254 PO_NEGOTIATIONS_SV2.add_attch_dynamic(
1255 x_from_entity_name => 'PON_BID_BUYER_NOTES',
1256 x_auction_header_id => p_auction_header_id,
1257 x_auction_line_number => NULL,
1258 x_bid_number => p_bid_number,
1259 x_bid_line_number => NULL,
1260 x_to_entity_name => 'PO_HEADERS',
1261 x_to_pk1_value => l_po_header_id,
1262 x_created_by => g_cpa_csr.created_by,
1263 x_last_update_login => g_cpa_csr.last_update_login,
1264 x_program_application_id => NULL,
1265 x_program_id => NULL,
1266 x_request_id => NULL);
1267
1268 g_progress := '407';
1269 IF g_debug_stmt THEN
1270 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1271 p_token => g_progress,
1272 p_message => 'After calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_BID_BUYER_NOTES');
1273 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1274 p_token => g_progress,
1275 p_message => 'Before calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_AUC_SUPPLIER_HEADER_NOTES');
1276 END IF;
1277
1278 -- Build and attach negotiation header notes as 'To Supplier' attachments on CPA header
1279 PO_NEGOTIATIONS_SV2.add_attch_dynamic(
1280 x_from_entity_name => 'PON_AUC_SUPPLIER_HEADER_NOTES',
1281 x_auction_header_id => p_auction_header_id,
1282 x_auction_line_number => NULL,
1283 x_bid_number => NULL,
1284 x_bid_line_number => NULL,
1285 x_to_entity_name => 'PO_HEADERS',
1286 x_to_pk1_value => l_po_header_id,
1287 x_created_by => g_cpa_csr.created_by,
1288 x_last_update_login => g_cpa_csr.last_update_login,
1289 x_program_application_id => NULL,
1290 x_program_id => NULL,
1291 x_request_id => NULL);
1292
1293 g_progress := '408';
1294 IF g_debug_stmt THEN
1295 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1296 p_token => g_progress,
1297 p_message => 'After calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_AUC_SUPPLIER_HEADER_NOTES');
1298 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1299 p_token => g_progress,
1300 p_message => 'Before calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_BID_HEADER_ATTRIBUTES');
1301 END IF;
1302 -- Build and attach negotiation/bid header attributes as 'To Supplier'attachment on CPA Header
1303 PO_NEGOTIATIONS_SV2.add_attch_dynamic(
1304 x_from_entity_name => 'PON_BID_HEADER_ATTRIBUTES',
1305 x_auction_header_id => p_auction_header_id,
1306 x_auction_line_number => NULL,
1307 x_bid_number => p_bid_number,
1308 x_bid_line_number => NULL,
1309 x_to_entity_name => 'PO_HEADERS',
1310 x_to_pk1_value => l_po_header_id,
1311 x_created_by => g_cpa_csr.created_by,
1312 x_last_update_login => g_cpa_csr.last_update_login,
1313 x_program_application_id => NULL,
1314 x_program_id => NULL,
1315 x_request_id => NULL);
1316
1317 g_progress := '409';
1318 IF g_debug_stmt THEN
1319 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1320 p_token => g_progress,
1321 p_message => 'After calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_BID_HEADER_ATTRIBUTES');
1322 END IF;
1323
1324 -- Getting the CLM Source Document Number
1325 BEGIN
1326
1327 SELECT phda.clm_source_document_id
1328 INTO x_clm_source_document_id
1329 FROM po_headers_all phda
1330 WHERE phda.po_header_id = l_po_header_id;
1331
1332 EXCEPTION
1333 WHEN No_Data_Found THEN
1334 x_clm_source_document_id := NULL;
1335 WHEN OTHERS THEN
1336 x_clm_source_document_id := NULL;
1337 END;
1338
1339
1340 IF (g_params_rec.po_num_code='AUTOMATIC') AND
1341 (g_cpa_csr.document_num = 'CPA 11.5.10+') THEN
1342
1343 -- CLM flow
1344 IF g_is_clm_flow = 'Y' THEN
1345 g_progress:= '410';
1346 IF g_debug_stmt THEN
1347 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1348 p_token => g_progress,
1349 p_message => 'Before Selecting document number in CLM flow');
1350 END IF;
1351
1352 -- Call Document Numbering API to get the order number
1353 IF x_template_id IS NOT NULL THEN
1354
1355 g_progress:= '411';
1356 PO_DOC_NUMBERING_PKG.DEFAULT_DOC_NUMBER_UDA(p_doc_header_id => l_po_header_id,
1357 p_draft_id => -1,
1358 p_template_id => x_template_id,
1359 p_context_usage => 'Base Document',
1360 p_source_org_owned => PO_DOC_NUMBERING_PKG.get_owned_by_issuing_org(x_clm_source_document_id),
1361 p_caller => 'PO',
1362 x_doc_number => x_doc_number,
1363 x_return_status => x_return_status);
1364
1365
1366
1367 IF g_debug_stmt THEN
1368 po_debug.debug_stmt
1369 (p_log_head => g_log_head || l_api_name,
1370 p_token => g_progress,
1371 p_message => 'After getting the Document Number: ' ||x_document_num
1372 );
1373 END IF;
1374
1375
1376 IF x_doc_number IS NOT NULL THEN
1377
1378 x_document_num := REPLACE(x_doc_number,'-');
1379
1380 IF Length(x_document_num) > 20 THEN
1381
1382 x_document_num := po_core_sv1.default_po_unique_identifier
1383 (p_table_name => 'PO_HEADERS',
1384 p_org_id => g_cpa_csr.org_id
1385 );
1386
1387 END IF;
1388 ELSE
1389
1390 x_document_num := po_core_sv1.default_po_unique_identifier
1391 (p_table_name => 'PO_HEADERS',
1392 p_org_id => g_cpa_csr.org_id
1393 );
1394 END IF; -- x_doc_number IS NOT NULL
1395 ELSE
1396 RAISE l_uda_template_exception;
1397 END IF; -- x_template_id IS NOT NULL
1398
1399 g_progress:= '412';
1400 IF g_debug_stmt THEN
1401 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1402 p_token => g_progress,
1403 p_message => 'After Selecting document number in CLM flow');
1404 END IF;
1405 ELSE
1406 g_progress:= '410';
1407 IF g_debug_stmt THEN
1408 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1409 p_token => g_progress,
1410 p_message => 'Before Selecting document number from po_unique_identifier_cont_all');
1411 END IF;
1412
1413 -- bug5176308
1414 -- Consolidate PO # generation code into one API
1415
1416 x_document_num :=
1417 PO_CORE_SV1.default_po_unique_identifier
1418 ( p_table_name => 'PO_HEADERS',
1419 p_org_id => g_cpa_csr.org_id
1420 );
1421
1422 g_progress:= '411';
1423 IF g_debug_stmt THEN
1424 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1425 p_token => g_progress,
1426 p_message => 'After Selecting document number from po_unique_identifier_cont_all');
1427 END IF;
1428
1429 END IF; -- IF g_is_clm_flow = 'Y'
1430
1431
1432 x_document_number := x_document_num;
1433 END IF;
1434
1435 g_progress:= '412';
1436 IF g_debug_stmt THEN
1437 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1438 p_token => g_progress,
1439 p_message => 'Before updating po_headers_all for conterms_exist_flag and document number');
1440 END IF;
1441
1442 --SQL WHAT: Updates Conterms_exist_flag, segment1 and document_creation_method
1443 --SQL WHY: To handle creation of an automatic document_number when the po_num_code is AUTOMATIC
1444 -- Update pf conterms_exist_flag and document_creation_method should have been
1445 -- handled in PO_HEADERS_PKG_S0.Insert_Row table handler.
1446 -- As this file is not allowed to update for 11.5.10, added separate update statement.
1447 --SQl Join:None
1448
1449 UPDATE PO_HEADERS_ALL
1450 SET conterms_exist_flag = decode(p_conterms_exist_flag,'Y','Y','N'),
1451 document_creation_method = p_document_creation_method,
1452 segment1 = x_document_num,
1453 clm_document_number = Nvl(x_doc_number, x_document_num)
1454 WHERE po_header_id = l_po_header_id;
1455
1456 g_progress:= '413';
1457 IF g_debug_stmt THEN
1458 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1459 p_token => g_progress,
1460 p_message => 'Before updating po_headers_all for conterms_exist_flag and document number');
1461 END IF;
1462
1463 --Copy contract terms if sourcing doc had a template attached.
1464 IF (p_conterms_exist_flag = 'Y') THEN
1465
1466 l_contract_doc_type:= PO_CONTERMS_UTL_GRP.GET_PO_CONTRACT_DOCTYPE(
1467 p_sub_doc_type=>g_cpa_csr.document_subtype);
1468 g_progress:= '414';
1469 IF g_debug_stmt THEN
1470 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1471 p_token => g_progress,
1472 p_message => 'Contracts template attached'||'-'||'l_contract_doc_type:'||l_contract_doc_type);
1473 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1474 p_token => g_progress,
1475 p_message => 'before call okc_terms_copy_grp.copy_doc');
1476 END IF;
1477
1478 OKC_TERMS_COPY_GRP.copy_doc(
1479 p_api_version => 1.0,
1480 p_source_doc_type => p_sourcing_k_doc_type,
1481 p_source_doc_id => p_bid_number,
1482 p_target_doc_type => l_contract_doc_type,
1483 p_target_doc_id => l_po_header_id,
1484 p_keep_version => 'Y',
1485 p_article_effective_date => sysdate,
1486 p_initialize_status_yn => 'N',
1487 p_reset_Fixed_Date_yn => 'N',
1488 p_copy_del_attachments_yn => 'Y',
1489 p_copy_deliverables => 'Y',
1490 p_document_number => x_document_num,
1491 x_return_status => l_return_status,
1492 x_msg_data => l_msg_data,
1493 x_msg_count => l_msg_count
1494 );
1495
1496 g_progress:='415';
1497 IF g_debug_stmt THEN
1498 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1499 p_token => g_progress,
1500 p_message => 'after call okc_terms_copy_grp.copy_doc.Return status:'||l_return_status);
1501 END IF;
1502
1503
1504 g_progress:='416';
1505 IF g_debug_stmt THEN
1506 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1507 p_token => g_progress,
1508 p_message => 'after call okc_terms_copy_grp.copy_doc.Return status:'||l_return_status);
1509 END IF;
1510
1511
1512
1513 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1514 RAISE l_Contracts_call_exception;
1515 END IF; -- Return status from contracts
1516
1517 END IF; -- if p_conterms_exist_flag = Y
1518
1519 /*IF g_is_clm_flow = 'Y' THEN
1520
1521 g_progress := '417';
1522 SELECT po_ame_approvals_s.nextval
1523 INTO l_ame_approval_id
1524 FROM dual;
1525
1526
1527 SELECT pdsh.ame_transaction_type
1528 INTO l_ame_transaction_type
1529 FROM po_doc_style_headers pdsh, po_headers_all pha
1530 WHERE pha.po_header_id = l_po_header_id
1531 AND pha.style_id = pdsh.style_id;
1532
1533 UPDATE po_headers_all
1534 SET ame_approval_id = l_ame_approval_id,
1535 ame_transaction_type = l_ame_transaction_type
1536 WHERE po_header_id = l_po_header_id;
1537 END IF; -- g_is_clm_flow = 'Y'
1538 */
1539 /* PO AME Approval workflow change : Updating po_headers_all with ame_transaction_type and ame_approval_id
1540 in case AME transaction type is populated in Style Headers page*/
1541 -- Start : PO AME Approval workflow
1542
1543 BEGIN
1544 SELECT 'Y',
1545 podsh.ame_transaction_type
1546 INTO l_new_ame_appr_id_req,
1547 l_ame_transaction_type
1548 FROM po_headers_all poh,
1549 po_doc_style_headers podsh
1550 WHERE poh.style_id = podsh.style_id
1551 AND podsh.ame_transaction_type IS NOT NULL
1552 AND poh.po_header_id = l_po_header_id;
1553
1554 EXCEPTION
1555 WHEN NO_DATA_FOUND THEN
1556 l_new_ame_appr_id_req := 'N';
1557 END;
1558
1559 UPDATE po_headers_all
1560 SET ame_approval_id = DECODE(l_new_ame_appr_id_req,
1561 'Y', po_ame_approvals_s.NEXTVAL,
1562 ame_approval_id),
1563 ame_transaction_type = DECODE(l_new_ame_appr_id_req,
1564 'Y', l_ame_transaction_type,
1565 ame_transaction_type)
1566 WHERE po_header_id = l_po_header_id;
1567 -- End : PO AME Approval workflow
1568
1569 EXCEPTION
1570 WHEN l_Contracts_call_exception then
1571 g_progress := '418';
1572 x_return_status := FND_API.G_RET_STS_ERROR;
1573
1574 -- put error messages in log
1575 IF g_debug_stmt THEN
1576 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1577 p_token => g_progress,
1578 p_message => 'Insert_CPA: Inside l_contracts_call_exception');
1579 END IF;
1580 Fnd_message.set_name('PO','PO_API_ERROR');
1581 Fnd_message.set_token( token => 'PROC_CALLER'
1582 , VALUE => 'PO_INTERFACE_S.INSERT_CPA');
1583 Fnd_message.set_token( token => 'PROC_CALLED'
1584 , VALUE => 'OKC_TERMS_CPOY_GRP.COPY_DOC');
1585 FND_MSG_PUB.Add;
1586
1587 IF g_debug_stmt THEN
1588 l_msg_count := FND_MSG_PUB.Count_Msg;
1589 FOR i IN 1..l_msg_count LOOP
1590 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1591 p_token => g_progress||'_EXCEPTION_'||i,
1592 p_message => FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F'));
1593 END LOOP;
1594 END IF;
1595 WHEN l_uda_template_exception THEN
1596
1597 IF g_debug_stmt THEN
1598 po_debug.debug_stmt
1599 (p_log_head => g_log_head || l_api_name,
1600 p_token => g_progress,
1601 p_message => 'PO_SOURCING_PVT.INSERT_CPA: Inside l_uda_template_exception'
1602 );
1603 END IF;
1604
1605 fnd_message.set_name ('PO', 'PO_UDA_TEMPLATE_ID_NULL');
1606 fnd_msg_pub.ADD;
1607 x_return_status := FND_API.G_RET_STS_ERROR;
1608
1609 WHEN OTHERS THEN
1610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1611 g_progress := '419';
1612 IF g_debug_unexp THEN
1613 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1614 p_token => g_progress,
1615 p_message => SQLERRM);
1616 END IF;
1617 FND_MSG_PUB.add_exc_msg(
1618 p_pkg_name => 'PO_SOURCING_PVT',
1619 p_procedure_name => l_api_name,
1620 p_error_text => NULL);
1621 END INSERT_CPA;
1622
1623 -------------------------------------------------------------------------------
1624 --Start of Comments
1625 --Name: create_cpa
1626 --Pre-reqs:
1627 -- None
1628 --Modifies:
1629 -- None
1630 --Locks:
1631 -- None.
1632 --Function:
1633 -- Creates Contract Purchase Agreement from Sourcing document
1634 --Parameters:
1635 --IN:
1636 --p_interface_header_id
1637 -- The id that will be used to uniquely identify a row in the PO_HEADERS_INTERFACE table
1638 --p_auction_header_id
1639 -- Id of the negotiation
1640 --p_bid_number
1641 -- Bid Number for which is negotiation is awarded
1642 --p_sourcing_k_doc_type
1643 -- Represents the OKC document type that would be created into a CPA
1644 -- The document type that Sourcing has seeded in Contracts.
1645 --p_conterms_exist_flag
1646 -- Whether the sourcing document has contract template attached.
1647 --p_document_creation_method
1648 -- Column specific to DBI. Sourcing will pass a value of AWARD_SOURCING
1649 --OUT:
1650 --x_document_id
1651 -- The unique identifier for the newly created document.
1652 --x_document_number
1653 -- The document number that would uniquely identify a document in a given organization.
1654 --x_return_status
1655 -- The standard OUT parameter giving return status of the API call.
1656 -- FND_API.G_RET_STS_ERROR - for expected error
1657 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
1658 -- FND_API.G_RET_STS_SUCCESS - for success
1659 --Notes:
1660 -- None
1661 --Testing:
1662 -- None
1663 --End of Comments
1664 -------------------------------------------------------------------------------
1665 PROCEDURE create_cpa (
1666 x_return_status OUT NOCOPY VARCHAR2,
1667 x_msg_count OUT NOCOPY NUMBER,
1668 x_msg_data OUT NOCOPY VARCHAR2,
1669 p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%TYPE,
1670 p_auction_header_id IN PON_AUCTION_HEADERS_ALL.auction_header_id%TYPE,
1671 p_bid_number IN PON_BID_HEADERS.bid_number%TYPE,
1672 p_sourcing_k_doc_type IN VARCHAR2,
1673 p_conterms_exist_flag IN PO_HEADERS_ALL.conterms_exist_flag%TYPE,
1674 p_document_creation_method IN PO_HEADERS_ALL.document_creation_method%TYPE,
1675 x_document_id OUT NOCOPY PO_HEADERS_ALL.po_header_id%TYPE,
1676 x_document_number OUT NOCOPY PO_HEADERS_ALL.segment1%TYPE
1677 ) IS
1678 l_return_status VARCHAR2(1);
1679 l_api_name CONSTANT VARCHAR2(30) := 'create_cpa';
1680
1681 l_document_type PO_HEADERS_INTERFACE.document_type_code%TYPE;
1682 l_document_subtype PO_HEADERS_INTERFACE.document_subtype%TYPE;
1683 l_action PO_HEADERS_INTERFACE.action%TYPE;
1684
1685 -- CLM CPA
1686 l_clm_enabled_flag VARCHAR2(1):= 'N';
1687 l_change_process_type PO_DOC_STYLE_HEADERS.change_process_type%TYPE;
1688
1689 BEGIN
1690 -- Initialize API return status to success
1691 x_return_status := FND_API.G_RET_STS_SUCCESS;
1692
1693 g_progress:='400';
1694 IF g_interface_cursor%ISOPEN THEN
1695 CLOSE g_interface_cursor;
1696 END IF;
1697 OPEN g_interface_cursor(p_interface_header_id);
1698
1699 FETCH g_interface_cursor INTO g_cpa_csr;
1700
1701 IF g_interface_cursor%NOTFOUND THEN
1702 CLOSE g_interface_cursor;
1703 IF g_debug_stmt THEN
1704 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1705 p_token => g_progress,
1706 p_message => 'PO_ALL_NO_DRILLDOWN: '||SQLERRM);
1707 END IF;
1708 Fnd_message.set_name('PO','PO_ALL_NO_DRILLDOWN');
1709 FND_MSG_PUB.Add;
1710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1711 END IF;
1712
1713 l_document_type := g_cpa_csr.document_type_code;
1714 l_document_subtype := g_cpa_csr.document_subtype;
1715 l_action := g_cpa_csr.action;
1716
1717
1718 /* Getting the style_id of the target document */
1719 BEGIN
1720
1721 SELECT phi.style_id
1722 INTO g_style_id
1723 FROM po_headers_interface phi
1724 WHERE phi.interface_header_id=p_interface_header_id;
1725
1726 EXCEPTION
1727 WHEN OTHERS THEN
1728 g_style_id := NULL;
1729 END;
1730
1731 g_progress:='450';
1732 IF g_debug_stmt THEN
1733 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1734 p_token => g_progress,
1735 p_message => 'Before checking for CLM Flow : Doc Style Id : '||g_style_id);
1736 END IF;
1737
1738
1739 -- Determines which flow to use
1740 IF g_style_id IS NOT NULL THEN
1741
1742 /* Check if the doc style is clm enabled */
1743
1744 SELECT nvl(pdsh.clm_flag,'N'),
1745 Nvl(pdsh.change_process_type,'CHANGE_ORDER')
1746 INTO l_clm_enabled_flag,
1747 l_change_process_type
1748 FROM po_doc_style_headers pdsh
1749 WHERE pdsh.style_id=g_style_id;
1750
1751 g_progress:='460';
1752 IF g_debug_stmt THEN
1753 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1754 p_token => g_progress,
1755 p_message => 'Inside checking for CLM Flow : l_clm_enabled_flag :'||l_clm_enabled_flag);
1756
1757 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1758 p_token => g_progress,
1759 p_message => 'Inside checking for CLM Flow : l_change_process_type :'||l_change_process_type);
1760 END IF;
1761
1762
1763 -- CLM Flow/ Modification Flow
1764 IF l_clm_enabled_flag = 'Y' THEN
1765 g_is_clm_flow := 'Y';
1766
1767 g_progress:='470';
1768 IF g_debug_stmt THEN
1769 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1770 p_token => g_progress,
1771 p_message => 'Is CLM Flow :'||g_is_clm_flow);
1772 END IF;
1773 END IF;
1774 END IF;
1775
1776
1777 IF l_document_subtype = 'CONTRACT' THEN
1778 IF l_action = 'NEW' THEN
1779 g_progress := '501';
1780 IF g_debug_stmt THEN
1781 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1782 p_token => g_progress,
1783 p_message => 'Before calling po_sourcing_pvt.default_cpa');
1784 END IF;
1785 -- Default the required fields in the record
1786 DEFAULT_CPA(
1787 x_return_status => l_return_status
1788 );
1789
1790 g_progress := '502';
1791 IF g_debug_stmt THEN
1792 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1793 p_token => g_progress,
1794 p_message => 'After calling po_sourcing_pvt.default_cpa');
1795 END IF;
1796
1797 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1798 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1799 RAISE FND_API.G_EXC_ERROR;
1800 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1801 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1802 END IF;
1803 END IF;
1804
1805 -- Validate the required fields in the record
1806 g_progress := '503';
1807 IF g_debug_stmt THEN
1808 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1809 p_token => g_progress,
1810 p_message => 'Before calling po_sourcing_pvt.validate_cpa');
1811 END IF;
1812
1813 VALIDATE_CPA(
1814 x_return_status => l_return_status
1815 );
1816
1817 g_progress := '504';
1818 IF g_debug_stmt THEN
1819 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1820 p_token => g_progress,
1821 p_message => 'After calling po_sourcing_pvt.validate_cpa');
1822 END IF;
1823
1824 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1825 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1826 RAISE FND_API.G_EXC_ERROR;
1827 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1829 END IF;
1830 END IF;
1831
1832 -- Insert record in the po_headers table and add attachments and contract terms
1833 g_progress := '505';
1834 IF g_debug_stmt THEN
1835 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1836 p_token => g_progress,
1837 p_message => 'Before calling po_sourcing_pvt.insert_cpa');
1838 END IF;
1839
1840 INSERT_CPA (
1841 p_auction_header_id => p_auction_header_id,
1842 p_bid_number => p_bid_number,
1843 p_sourcing_k_doc_type => p_sourcing_k_doc_type,
1844 p_conterms_exist_flag => p_conterms_exist_flag,
1845 p_document_creation_method => p_document_creation_method,
1846 x_document_id => x_document_id,
1847 x_document_number => x_document_number,
1848 x_return_status => l_return_status
1849 );
1850
1851 g_progress := '506';
1852 IF g_debug_stmt THEN
1853 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1854 p_token => g_progress,
1855 p_message => 'After calling po_sourcing_pvt.insert_cpa');
1856 END IF;
1857
1858 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1859 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1860 RAISE FND_API.G_EXC_ERROR;
1861 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1863 END IF;
1864 END IF;
1865
1866 ELSE
1867 g_progress := '507';
1868 IF g_debug_stmt THEN
1869 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1870 p_token => g_progress,
1871 p_message => 'Invalid Action in the interface table');
1872 END IF;
1873 Fnd_message.set_name('PO','PO_PDOI_INVALID_ACTION');
1874 Fnd_message.set_token( token => 'VALUE'
1875 , VALUE => g_cpa_csr.action);
1876 FND_MSG_PUB.Add;
1877 RAISE FND_API.G_EXC_ERROR;
1878 END IF; -- End of l_action = 'NEW'
1879 ELSE
1880 g_progress := '508';
1881 IF g_debug_stmt THEN
1882 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1883 p_token => g_progress,
1884 p_message => 'Invalid Document Subtype Code in the interface table');
1885 END IF;
1886 Fnd_message.set_name('PO','PO_PDOI_INVALID_VALUE');
1887 Fnd_message.set_token( token => 'COLUMN_NAME'
1888 , VALUE => 'Document Subtype');
1889 Fnd_message.set_token( token => 'VALUE'
1890 , VALUE => 'CONTRACT');
1891 FND_MSG_PUB.Add;
1892
1893 RAISE FND_API.G_EXC_ERROR;
1894 END IF; -- End of l_document_type = 'CONTRACT'
1895 EXCEPTION
1896 WHEN FND_API.G_EXC_ERROR THEN
1897 x_document_number := NULL;
1898 x_document_id := NULL;
1899 x_return_status := FND_API.G_RET_STS_ERROR;
1900 g_progress := '510';
1901 IF g_debug_stmt THEN
1902 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1903 p_token => g_progress,
1904 p_message => 'Expected Error');
1905 END IF;
1906
1907 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1908 x_document_number := NULL;
1909 x_document_id := NULL;
1910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1911 g_progress := '511';
1912 IF g_debug_unexp THEN
1913 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1914 p_token => g_progress,
1915 p_message => 'Unexpected Error');
1916 END IF;
1917 WHEN OTHERS THEN
1918 x_document_number := NULL;
1919 x_document_id := NULL;
1920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1921 g_progress := '512';
1922 IF g_debug_unexp THEN
1923 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1924 p_token => g_progress,
1925 p_message => SQLERRM);
1926 END IF;
1927
1928 FND_MSG_PUB.add_exc_msg(
1929 p_pkg_name => 'PO_SOURCING_PVT',
1930 p_procedure_name => l_api_name,
1931 p_error_text => NULL);
1932 END CREATE_CPA;
1933
1934
1935 -------------------------------------------------------------------------------
1936 --Start of Comments
1937 --Name: DELETE_INTERFACE_HEADER
1938 --Pre-reqs:
1939 -- None
1940 --Modifies:
1941 -- po_headers_interface
1942 --Locks:
1943 -- None.
1944 --Function:
1945 -- This deletes the interface header row from interface table
1946 --Parameters:
1947 --IN:
1948 --p_interface_header_id
1949 -- The id that will be used to uniquely identify a row in the PO_HEADERS_INTERFACE table
1950 --OUT:
1951 --x_return_status
1952 -- The standard OUT parameter giving return status of the API call.
1953 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
1954 -- FND_API.G_RET_STS_SUCCESS - for success
1955 --Notes:
1956 -- None
1957 --Testing:
1958 -- None
1959 --End of Comments
1960 -------------------------------------------------------------------------------
1961
1962 PROCEDURE DELETE_INTERFACE_HEADER (
1963 p_interface_header_id IN PO_HEADERS_INTERFACE.INTERFACE_HEADER_ID%TYPE,
1964 x_return_status OUT NOCOPY VARCHAR2
1965 ) IS
1966 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_INTERFACE_HEADER';
1967 BEGIN
1968 -- Initialize API return status to success
1969 x_return_status := FND_API.G_RET_STS_SUCCESS;
1970
1971 DELETE po_headers_interface
1972 WHERE interface_header_id = p_interface_header_id;
1973
1974 g_progress := '600';
1975 IF g_debug_stmt THEN
1976 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1977 p_token => g_progress,
1978 p_message => 'No of Records deleted from PO_HEADERS_INTERFACE'||SQL%rowcount);
1979 END IF;
1980 EXCEPTION
1981 WHEN OTHERS THEN
1982 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1983 g_progress := '601';
1984 IF g_debug_unexp THEN
1985 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1986 p_token => g_progress,
1987 p_message => SQLERRM);
1988 END IF;
1989 FND_MSG_PUB.add_exc_msg(
1990 p_pkg_name => 'PO_SOURCING_PVT',
1991 p_procedure_name => l_api_name,
1992 p_error_text => NULL);
1993 END DELETE_INTERFACE_HEADER;
1994
1995
1996 END PO_SOURCING_PVT;