[Home] [Help]
PACKAGE BODY: APPS.PO_SOURCING_PVT
Source
1 PACKAGE BODY PO_SOURCING_PVT AS
2 /* $Header: POXVCPAB.pls 120.4 2006/04/27 15:28:03 bao noship $*/
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 FROM po_headers_interface phi
83 WHERE phi.interface_header_id = p_interface_header_id;
84
85 -- Type declaration for System Parameters structure
86 TYPE system_parameters_rec_type IS RECORD
87 (currency_code GL_SETS_OF_BOOKS.currency_code%type,
88 coa_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE,
89 po_encumbrance_flag FINANCIALS_SYSTEM_PARAMETERS.purch_encumbrance_flag%TYPE,
90 req_encumbrance_flag FINANCIALS_SYSTEM_PARAMETERS.req_encumbrance_flag%TYPE,
91 sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE,
92 ship_to_location_id FINANCIALS_SYSTEM_PARAMETERS.ship_to_location_id%TYPE,
93 bill_to_location_id FINANCIALS_SYSTEM_PARAMETERS.bill_to_location_id%TYPE,
94 fob_lookup_code FINANCIALS_SYSTEM_PARAMETERS.fob_lookup_code%type,
95 freight_terms_lookup_code
96 FINANCIALS_SYSTEM_PARAMETERS.freight_terms_lookup_code%type,
97 terms_id PO_SYSTEM_PARAMETERS.term_id%TYPE,
98 default_rate_type PO_SYSTEM_PARAMETERS.default_rate_type%type,
99 taxable_flag PO_SYSTEM_PARAMETERS.taxable_flag%TYPE,
100 receiving_flag PO_SYSTEM_PARAMETERS.receiving_flag%TYPE,
101 enforce_buyer_name_flag PO_SYSTEM_PARAMETERS.enforce_buyer_name_flag%TYPE,
102 enforce_buyer_auth_flag PO_SYSTEM_PARAMETERS.enforce_buyer_authority_flag%TYPE,
103 line_type_id PO_SYSTEM_PARAMETERS.line_type_id%TYPE := null,
104 manual_po_num_type PO_SYSTEM_PARAMETERS.manual_po_num_type%TYPE,
105 po_num_code PO_SYSTEM_PARAMETERS.user_defined_po_num_code%TYPE,
106 price_type_lookup_code PO_SYSTEM_PARAMETERS.price_type_lookup_code%TYPE,
107 invoice_close_tolerance PO_SYSTEM_PARAMETERS.invoice_close_tolerance%TYPE,
108 receive_close_tolerance PO_SYSTEM_PARAMETERS.receive_close_tolerance%TYPE,
109 security_structure_id PO_SYSTEM_PARAMETERS.security_position_structure_id%TYPE,
110 expense_accrual_code PO_SYSTEM_PARAMETERS.price_type_lookup_code%TYPE,
111 inventory_organization_id FINANCIALS_SYSTEM_PARAMETERS.inventory_organization_id%TYPE,
112 rev_sort_ordering FINANCIALS_SYSTEM_PARAMETERS.revision_sort_ordering%TYPE,
113 min_rel_amount PO_SYSTEM_PARAMETERS.min_release_amount%TYPE,
114 notify_blanket_flag PO_SYSTEM_PARAMETERS.notify_if_blanket_flag%TYPE,
115 budgetary_control_flag GL_SETS_OF_BOOKS.enable_budgetary_control_flag%TYPE,
116 user_defined_req_num_code PO_SYSTEM_PARAMETERS.user_defined_req_num_code%type,
117 rfq_required_flag PO_SYSTEM_PARAMETERS.rfq_required_flag%TYPE,
118 manual_req_num_type PO_SYSTEM_PARAMETERS.manual_req_num_type%type,
119 enforce_full_lot_qty PO_SYSTEM_PARAMETERS.enforce_full_lot_quantities%type,
120 disposition_warning_flag PO_SYSTEM_PARAMETERS.disposition_warning_flag%TYPE,
121 reserve_at_completion_flag FINANCIALS_SYSTEM_PARAMETERS.reserve_at_completion_flag%TYPE,
122 user_defined_rcpt_num_code
123 PO_SYSTEM_PARAMETERS.user_defined_receipt_num_code%type,
124 manual_rcpt_num_type PO_SYSTEM_PARAMETERS.manual_receipt_num_type%type,
125 use_positions_flag FINANCIALS_SYSTEM_PARAMETERS.use_positions_flag%TYPE,
126 default_quote_warning_delay PO_SYSTEM_PARAMETERS.default_quote_warning_delay%TYPE,
127 inspection_required_flag PO_SYSTEM_PARAMETERS.inspection_required_flag%TYPE,
128 user_defined_quote_num_code
129 PO_SYSTEM_PARAMETERS.user_defined_quote_num_code%type,
130 manual_quote_num_type PO_SYSTEM_PARAMETERS.manual_quote_num_type%type,
131 user_defined_rfq_num_code
132 PO_SYSTEM_PARAMETERS.user_defined_rfq_num_code%type,
133 manual_rfq_num_type PO_SYSTEM_PARAMETERS.manual_rfq_num_type%type,
134 ship_via_lookup_code FINANCIALS_SYSTEM_PARAMETERS.ship_via_lookup_code%type,
135 qty_rcv_tolerance rcv_parameters.qty_rcv_tolerance%TYPE,
136 period_name GL_PERIOD_STATUSES.period_name%type);
137
138 -- Type declaration for Vendor defaults structure
139 TYPE vendor_defaults_rec_type IS RECORD
140 (vendor_id PO_VENDORS.vendor_id%TYPE := null,
141 -- Bug# 4546121:All columns that referred to the obsolete columns in po_vendors have
142 -- been modified to point to PO_HEADERS_ALL type.
143 ship_to_location_id PO_HEADERS_ALL.ship_to_location_id%TYPE := null,
144 bill_to_location_id PO_HEADERS_ALL.bill_to_location_id%TYPE := null,
145 ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE := null,
146 fob_lookup_code PO_HEADERS_ALL.fob_lookup_code%TYPE := null,
147 pay_on_code PO_VENDOR_SITES_ALL.pay_on_code%TYPE := null,
148 freight_terms_lookup_code PO_HEADERS_ALL.freight_terms_lookup_code%TYPE := null,
149 terms_id po_vendors.terms_id%TYPE := null,
150 type_1099 PO_VENDORS.type_1099%TYPE := null,
151 hold_flag PO_VENDORS.hold_flag%TYPE := null,
152 invoice_currency_code PO_VENDORS.invoice_currency_code%TYPE := null,
153 receipt_required_flag PO_VENDORS.receipt_required_flag%TYPE := null,
154 num_1099 PO_VENDORS.num_1099%TYPE := null,
155 vat_registration_num NUMBER := null,
156 inspection_required_flag PO_VENDORS.inspection_required_flag%TYPE := null,
157 invoice_match_option PO_VENDORS.match_option%TYPE := null,
158 shipping_control PO_VENDOR_SITES.shipping_control%TYPE := NULL
159 );
160
161 -- Type declaration for WHO information structure
162 TYPE who_rec_type IS RECORD
163 (user_id NUMBER := 0,
164 login_id NUMBER := 0,
165 resp_id NUMBER := 0);
166
167 g_cpa_csr g_interface_cursor%ROWTYPE;
168 g_who_rec who_rec_type;
169 g_params_rec system_parameters_rec_type;
170 g_vendor_rec vendor_defaults_rec_type;
171 g_vendor_default_rec vendor_defaults_rec_type;
172 g_progress VARCHAR2(2000) := '000';
173
174 -------------------------------------------------------------------------------
175 --Start of Comments
176 --Name: GET_SYSTEM_DEFAULTS
177 --Pre-reqs:
178 -- None
179 --Modifies:
180 -- None
181 --Locks:
182 -- None.
183 --Function:
184 -- This procedure is called for getting the default po paramters
185 --Parameters:
186 --IN:
187 --p_interface_header_id
188 -- Id that uniquely identifies a row in po_headers_interface table
189 --OUT:
190 -- None
191 --Notes:
192 -- None
193 --Testing:
194 -- None
195 --End of Comments
196 -------------------------------------------------------------------------------
197
198 PROCEDURE get_system_defaults(p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%TYPE) IS
199 x_date date;
200 l_api_name CONSTANT VARCHAR2(30) := 'get_system_defaults';
201 BEGIN
202 IF g_debug_stmt THEN
203 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
204 END IF;
205
206 -- Get WHO column values
207 g_who_rec.user_id := nvl(fnd_global.user_id,0);
208 g_who_rec.login_id := nvl(fnd_global.login_id,0);
209 g_who_rec.resp_id := nvl(fnd_global.resp_id,0);
210
211 g_progress:='010';
212
213 -- Get system defaults
214 PO_CORE_S.get_po_parameters(
215 x_currency_code => g_params_rec.currency_code,
216 x_coa_id => g_params_rec.coa_id,
217 x_po_encumberance_flag => g_params_rec.po_encumbrance_flag,
218 x_req_encumberance_flag => g_params_rec.req_encumbrance_flag,
219 x_sob_id => g_params_rec.sob_id,
220 x_ship_to_location_id => g_params_rec.ship_to_location_id,
221 x_bill_to_location_id => g_params_rec.bill_to_location_id,
222 x_fob_lookup_code => g_params_rec.fob_lookup_code,
223 x_freight_terms_lookup_code => g_params_rec.freight_terms_lookup_code,
224 x_terms_id => g_params_rec.terms_id,
225 x_default_rate_type => g_params_rec.default_rate_type,
226 x_taxable_flag => g_params_rec.taxable_flag,
227 x_receiving_flag => g_params_rec.receiving_flag,
228 x_enforce_buyer_name_flag => g_params_rec.enforce_buyer_name_flag,
229 x_enforce_buyer_auth_flag => g_params_rec.enforce_buyer_auth_flag,
230 x_line_type_id => g_params_rec.line_type_id,
231 x_manual_po_num_type => g_params_rec.manual_po_num_type,
232 x_po_num_code => g_params_rec.po_num_code,
233 x_price_lookup_code => g_params_rec.price_type_lookup_code,
234 x_invoice_close_tolerance => g_params_rec.invoice_close_tolerance,
235 x_receive_close_tolerance => g_params_rec.receive_close_tolerance,
236 x_security_structure_id => g_params_rec.security_structure_id,
237 x_expense_accrual_code => g_params_rec.expense_accrual_code,
238 x_inv_org_id => g_params_rec.inventory_organization_id,
239 x_rev_sort_ordering => g_params_rec.rev_sort_ordering,
240 x_min_rel_amount => g_params_rec.min_rel_amount,
241 x_notify_blanket_flag => g_params_rec.notify_blanket_flag,
242 x_budgetary_control_flag => g_params_rec.budgetary_control_flag,
243 x_user_defined_req_num_code => g_params_rec.user_defined_req_num_code,
244 x_rfq_required_flag => g_params_rec.rfq_required_flag,
245 x_manual_req_num_type => g_params_rec.manual_req_num_type,
246 x_enforce_full_lot_qty => g_params_rec.enforce_full_lot_qty,
247 x_disposition_warning_flag => g_params_rec.disposition_warning_flag,
248 x_reserve_at_completion_flag => g_params_rec.reserve_at_completion_flag,
249 x_user_defined_rcpt_num_code => g_params_rec.user_defined_rcpt_num_code,
250 x_manual_rcpt_num_type => g_params_rec.manual_rcpt_num_type,
251 x_use_positions_flag => g_params_rec.use_positions_flag,
252 x_default_quote_warning_delay => g_params_rec.default_quote_warning_delay,
253 x_inspection_required_flag => g_params_rec.inspection_required_flag,
254 x_user_defined_quote_num_code => g_params_rec.user_defined_quote_num_code,
255 x_manual_quote_num_type => g_params_rec.manual_quote_num_type,
256 x_user_defined_rfq_num_code => g_params_rec.user_defined_rfq_num_code,
257 x_manual_rfq_num_type => g_params_rec.manual_rfq_num_type,
258 x_ship_via_lookup_code => g_params_rec.ship_via_lookup_code,
259 x_qty_rcv_tolerance => g_params_rec.qty_rcv_tolerance);
260
261 g_progress:='020';
262
263 IF(g_params_rec.po_encumbrance_flag = 'Y') THEN
264 PO_CORE_S.get_period_name(
265 x_sob_id => g_params_rec.sob_id,
266 x_period => g_params_rec.period_name,
267 x_gl_date => x_date);
268 END IF;
269
270 IF g_debug_stmt THEN
271 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
272 END IF;
273
274 EXCEPTION
275 WHEN OTHERS THEN
276 g_progress:='030';
277 IF g_debug_unexp THEN
278 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
279 p_token => g_progress,
280 p_message => SQLERRM);
281 END IF;
282
283 FND_MSG_PUB.add_exc_msg(
284 p_pkg_name => 'PO_SOURCING_PVT',
285 p_procedure_name => l_api_name,
286 p_error_text => NULL);
287 END get_system_defaults;
288
289 -------------------------------------------------------------------------------
290 --Start of Comments
291 --Name: DEFAULT_CPA
292 --Pre-reqs:
293 -- None
294 --Modifies:
295 -- None
296 --Locks:
297 -- None.
298 --Function:
299 -- This procedure is called for defaulting all the values that are left null in
300 -- the headers_interface_table but are required for creating the CPA and can be
301 -- defaulted from one or more sources
302 --Parameters:
303 --IN:
304 -- None
305 --OUT:
306 --x_return_status
307 -- The standard OUT parameter giving return status of the API call.
308 -- FND_API.G_RET_STS_ERROR - for expected error
309 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
310 -- FND_API.G_RET_STS_SUCCESS - for success
311 --Notes:
312 -- None
313 --Testing:
314 -- None
315 --End of Comments
316 -------------------------------------------------------------------------------
317 PROCEDURE DEFAULT_CPA (
318 x_return_status OUT NOCOPY VARCHAR2
319 ) IS
320 l_api_name VARCHAR2(30) := 'DEFAULT_CPA';
321 l_terms_id PO_HEADERS.terms_id%TYPE;
322 l_fob_lookup_code PO_HEADERS.fob_lookup_code%TYPE;
323 l_freight_lookup_code PO_HEADERS.freight_terms_lookup_code%TYPE;
324 l_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE;
325 l_vs_terms_id PO_HEADERS.terms_id%TYPE;
326 l_vs_fob_lookup_code PO_HEADERS.fob_lookup_code%TYPE;
327 l_vs_freight_lookup_code PO_HEADERS.freight_terms_lookup_code%TYPE;
328 l_vs_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE;
329
330 BEGIN
331 -- Initialize API return status to success
332 x_return_status := FND_API.G_RET_STS_SUCCESS;
333
334 --Default all the required default po parameters from the financial system
335 --parameters,Po_system_parameters, receiving options and gl set of books.
336 --Using the procedure po_core_s.get_po_parameters
337 g_progress := '200';
338
339 IF g_debug_stmt THEN
340 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
341 p_token => g_progress,
342 p_message => 'Before calling get_system_defaults');
343 END IF;
344
345 get_system_defaults(p_interface_header_id => g_cpa_csr.interface_header_id);
346
347 g_progress := '201';
348 IF g_debug_stmt THEN
349 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
350 p_token => g_progress,
351 p_message => 'After calling get_system_defaults');
352 END IF;
353
354 IF (g_params_rec.po_num_code='AUTOMATIC') THEN
355 -- This is necessary to ensure that concurrency issues do not crop up.
356 -- We would actually pick the value from the db just before the commit action
357 g_cpa_csr.document_num := 'CPA 11.5.10+';
358 END IF;
359
360 -- Default the relevant vendor information
361 IF(g_cpa_csr.vendor_id is not null) THEN
362 g_progress := '202';
363 IF g_debug_stmt THEN
364 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
365 p_token => g_progress,
366 p_message => 'Before calling get_vendor_info');
367 END IF;
368
369 PO_VENDORS_SV.get_vendor_info (
370 X_vendor_id => g_cpa_csr.vendor_id,
371 X_ship_to_location_id => g_vendor_default_rec.ship_to_location_id,
372 X_bill_to_location_id => g_vendor_default_rec.bill_to_location_id,
373 X_ship_via_lookup_code => l_ship_via_lookup_code,
374 X_fob_lookup_code => l_fob_lookup_code,
375 X_freight_terms_lookup_code => l_freight_lookup_code,
376 X_terms_id => l_terms_id,
377 X_type_1099 => g_vendor_default_rec.type_1099,
378 X_hold_flag => g_vendor_default_rec.hold_flag,
379 X_invoice_currency_code => g_vendor_default_rec.invoice_currency_code,
380 X_receipt_required_flag => g_vendor_default_rec.receipt_required_flag,
381 X_num_1099 => g_vendor_default_rec.num_1099,
382 X_vat_registration_num => g_vendor_default_rec.vat_registration_num,
383 X_inspection_required_flag => g_vendor_default_rec.inspection_required_flag
384 );
385
386 g_progress := '203';
387 IF g_debug_stmt THEN
388 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
389 p_token => g_progress,
390 p_message => 'After calling get_vendor_info');
391 END IF;
392
393 END IF;
394 -- Default the relevant vendor site information. You would then require this for
395 -- defaulting the pay_on_code , shipping_control using the procedure
396 -- po_vendor_sites_sv.get_vendor_site_info
397 IF(g_cpa_csr.vendor_site_id is not null) THEN
398 g_progress := '204';
399 IF g_debug_stmt THEN
400 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
401 p_token => g_progress,
402 p_message => 'Before calling get_vendor_site_info');
403 END IF;
404
405 PO_VENDOR_SITES_SV.get_vendor_site_info(
406 X_vendor_site_id => g_cpa_csr.vendor_site_id,
407 X_vs_ship_to_location_id => g_vendor_rec.ship_to_location_id,
408 X_vs_bill_to_location_id => g_vendor_rec.bill_to_location_id,
409 X_vs_ship_via_lookup_code => l_vs_ship_via_lookup_code,
410 X_vs_fob_lookup_code => l_vs_fob_lookup_code,
411 X_vs_pay_on_code => g_vendor_rec.pay_on_code,
412 X_vs_freight_terms_lookup_code => l_vs_freight_lookup_code,
413 X_vs_terms_id => l_vs_terms_id,
414 X_vs_invoice_currency_code => g_vendor_rec.invoice_currency_code,
415 x_vs_shipping_control => g_vendor_rec.shipping_control
416 );
417
418 g_progress := '205';
419 IF g_debug_stmt THEN
420 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
421 p_token => g_progress,
422 p_message => 'After calling get_vendor_site_info');
423 END IF;
424
425 -- Default the pay_on_code for a CPA based
426 -- on the vendor site value.
427 if (g_vendor_rec.pay_on_code = 'RECEIPT_AND_USE') then
428 g_vendor_rec.pay_on_code := 'RECEIPT';
429 elsif (g_vendor_rec.pay_on_code = 'USE') then
430 g_vendor_rec.pay_on_code := null;
431 end if;
432 END IF; -- End of vendor_site_id is not null
433
434 -- IF the value of shipping_control in the interface table is null
435 -- then Copy the value from terms value defaulted from vendor site Information
436 IF g_cpa_csr.shipping_control IS NULL THEN
437 g_cpa_csr.shipping_control := g_vendor_rec.shipping_control;
438 END IF;
439
440 -- If global_agreement_flag is 'N' set it to null
441 IF g_cpa_csr.global_agreement_flag = 'N' THEN
442 g_cpa_csr.global_agreement_flag := NULL;
443 END IF;
444
445 -- Defaulting the terms id
446 IF g_cpa_csr.terms_id IS NULL THEN
447 if l_vs_terms_id is not null then
448 po_terms_sv.val_ap_terms(
449 X_temp_terms_id => l_vs_terms_id,
450 X_res_terms_id => g_cpa_csr.terms_id
451 );
452 end if;
453 IF g_cpa_csr.terms_id IS NULL THEN
454 if l_terms_id is not null then
455 po_terms_sv.val_ap_terms(
456 X_temp_terms_id => l_terms_id,
457 X_res_terms_id => g_cpa_csr.terms_id
458 );
459 end if;
460 IF g_cpa_csr.terms_id IS NULL THEN
461 g_cpa_csr.terms_id := g_params_rec.terms_id;
462 END IF;
463 END IF;
464 END IF;
465
466 -- Defaulting the ship_via_lookup_code
467 IF g_cpa_csr.ship_via_lookup_code IS NULL THEN
468 if l_vs_ship_via_lookup_code is not null then
469 po_vendors_sv.val_freight_carrier(
470 X_temp_ship_via => l_vs_ship_via_lookup_code,
471 X_org_id => g_params_rec.inventory_organization_id,
472 X_res_ship_via => g_cpa_csr.ship_via_lookup_code
473 );
474 end if;
475 IF g_cpa_csr.ship_via_lookup_code IS NULL THEN
476 if l_ship_via_lookup_code is not null then
477 po_vendors_sv.val_freight_carrier(
478 X_temp_ship_via => l_ship_via_lookup_code,
479 X_org_id => g_params_rec.inventory_organization_id,
480 X_res_ship_via => g_cpa_csr.ship_via_lookup_code
481 );
482 end if;
483 IF g_cpa_csr.ship_via_lookup_code IS NULL THEN
484 g_cpa_csr.ship_via_lookup_code := g_params_rec.ship_via_lookup_code;
485 END IF;
486 END IF;
487 END IF;
488
489 -- Defaulting the fob_lookup_code
490 IF g_cpa_csr.fob_lookup_code IS NULL THEN
491 if l_vs_fob_lookup_code is not null then
492 po_vendors_sv.val_fob(
493 X_temp_fob_lookup_code => l_vs_fob_lookup_code,
494 X_res_fob => g_cpa_csr.fob_lookup_code
495 );
496 end if;
497 IF g_cpa_csr.fob_lookup_code IS NULL THEN
498 if l_fob_lookup_code is not null then
499 po_vendors_sv.val_fob(
500 X_temp_fob_lookup_code => l_fob_lookup_code,
501 X_res_fob => g_cpa_csr.fob_lookup_code
502 );
503 end if;
504 IF g_cpa_csr.fob_lookup_code IS NULL THEN
505 g_cpa_csr.fob_lookup_code := g_params_rec.fob_lookup_code;
506 END IF;
507 END IF;
508 END IF;
509
510 -- Defaulting the pay_on_code
511 IF g_cpa_csr.pay_on_code IS NULL THEN
512 g_cpa_csr.pay_on_code := g_vendor_rec.pay_on_code;
513 END IF;
514
515 -- Defaulting the freight_terms_lookup_code
516 IF g_cpa_csr.freight_terms_lookup_code IS NULL THEN
517 if l_vs_freight_lookup_code is not null then
518 po_vendors_sv.val_freight_terms(
519 X_temp_freight_terms => l_vs_freight_lookup_code,
520 X_res_freight_terms => g_cpa_csr.freight_terms_lookup_code
521 );
522 end if;
523 IF g_cpa_csr.freight_terms_lookup_code IS NULL THEN
524 if l_freight_lookup_code is not null then
525 po_vendors_sv.val_freight_terms(
526 X_temp_freight_terms => l_freight_lookup_code,
527 X_res_freight_terms => g_cpa_csr.freight_terms_lookup_code
528 );
529 end if;
530 IF g_cpa_csr.freight_terms_lookup_code IS NULL THEN
531 g_cpa_csr.freight_terms_lookup_code := g_params_rec.freight_terms_lookup_code;
532 END IF;
533 END IF;
534 END IF;
535
536 g_cpa_csr.revision_num := 0;
537 g_cpa_csr.h_closed_code := 'OPEN';
538 g_cpa_csr.print_count := 0;
539 g_cpa_csr.confirming_order_flag := 'N';
540 g_cpa_csr.frozen_flag := 'N';
541
542 EXCEPTION
543 WHEN FND_API.G_EXC_ERROR THEN
544 x_return_status := FND_API.G_RET_STS_ERROR;
545 g_progress := '210';
546 IF g_debug_stmt THEN
547 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
548 p_token => g_progress,
549 p_message => 'Expected Error');
550 END IF;
551 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
553 g_progress := '211';
554 IF g_debug_unexp THEN
555 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
556 p_token => g_progress,
557 p_message => 'Unexpected Error');
558 END IF;
559 WHEN OTHERS THEN
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 g_progress := '212';
562 IF g_debug_unexp THEN
563 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
564 p_token => g_progress,
565 p_message => SQLERRM);
566 END IF;
567 FND_MSG_PUB.add_exc_msg(
568 p_pkg_name => 'PO_SOURCING_PVT',
569 p_procedure_name => l_api_name,
570 p_error_text => NULL);
571
572 END DEFAULT_CPA;
573 -------------------------------------------------------------------------------
574 --Start of Comments
575 --Name: VALIDATE_CPA
576 --Pre-reqs:
577 -- None
578 --Modifies:
579 -- None
580 --Locks:
581 -- None.
582 --Function:
583 -- validates the data in the interface table to create CPA
584 --Parameters:
585 --IN:
586 -- None
587 --OUT:
588 --x_return_status
589 -- The standard OUT parameter giving return status of the API call.
590 -- FND_API.G_RET_STS_ERROR - for expected error
591 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
592 -- FND_API.G_RET_STS_SUCCESS - for success
593 --Notes:
594 -- None
595 --Testing:
596 -- None
597 --End of Comments
598 -------------------------------------------------------------------------------
599 PROCEDURE VALIDATE_CPA (
600 x_return_status OUT NOCOPY VARCHAR2
601 ) IS
602 is_valid BOOLEAN := FALSE;
603 l_error_code VARCHAR2(30);
604 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CPA';
605 l_fob_lookup_code PO_LOOKUP_CODES.lookup_code%TYPE;
606 l_freight_terms_lookup_code PO_LOOKUP_CODES.lookup_code%TYPE;
607 l_freight_carrier ORG_FREIGHT.freight_code%TYPE;
608 l_terms_id AP_TERMS.term_id%TYPE;
609 BEGIN
610 -- Initialize API return status to success
611 x_return_status := FND_API.G_RET_STS_SUCCESS;
612
613 -- validate document number
614 IF g_cpa_csr.document_num IS NOT NULL THEN
615 IF g_cpa_csr.document_num <> 'CPA 11.5.10+' THEN
616 g_progress := '300';
617 IF g_debug_stmt THEN
618 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
619 p_token => g_progress,
620 p_message => 'Before calling PO_HEADERS_SV6.val_doc_num');
621 END IF;
622 is_valid := PO_HEADERS_SV6.val_doc_num(
623 X_doc_type => g_cpa_csr.document_type_code,
624 X_doc_num => g_cpa_csr.document_num,
625 X_user_defined_num => g_params_rec.manual_po_num_type,
626 X_user_defined_po_num_code => g_params_rec.po_num_code,
627 X_error_code => l_error_code);
628 g_progress := '301';
629 IF g_debug_stmt THEN
630 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
631 p_token => g_progress,
632 p_message => 'After calling PO_HEADERS_SV6.val_doc_num. X_error_code = '||l_error_code);
633 END IF;
634
635 IF (is_valid = FALSE ) THEN
636 IF (l_error_code = 'PO_PDOI_DOC_NUM_UNIQUE') THEN
637 g_progress := '302';
638 IF g_debug_stmt THEN
639 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
640 p_token => g_progress,
641 p_message => 'PO_PDOI_DOC_NUM_UNIQUE');
642 END IF;
643 Fnd_message.set_name('PO','PO_PDOI_DOC_NUM_UNIQUE');
644 Fnd_message.set_token( token => 'VALUE'
645 , VALUE => g_cpa_csr.document_num);
646 FND_MSG_PUB.Add;
647
648 RAISE FND_API.G_EXC_ERROR;
649
650 ELSIF (l_error_code = 'PO_PDOI_VALUE_NUMERIC') THEN
651 g_progress := '303';
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_VALUE_NUMERIC');
656 END IF;
657 Fnd_message.set_name('PO','PO_PDOI_VALUE_NUMERIC');
658 Fnd_message.set_token( token => 'COLUMN_NAME'
659 , VALUE => 'Document Number');
660 Fnd_message.set_token( token => 'VALUE'
661 , VALUE => g_cpa_csr.document_num);
662 FND_MSG_PUB.Add;
663 RAISE FND_API.G_EXC_ERROR;
664 ELSIF (l_error_code = 'PO_PDOI_LT_ZERO') THEN
665 g_progress := '304';
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_LT_ZERO');
670 END IF;
671 Fnd_message.set_name('PO','PO_PDOI_LT_ZERO');
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 END IF;
679 END IF; -- If is_valid = FALSE
680 END IF; -- End of IF g_cpa_csr.document_num <> 'CPA 11.5.10+'
681 ELSE
682 -- This code executes when the document num creation is manual
683 -- and no data is passed from sourcing for document num
684 g_progress := '305';
685 IF g_debug_stmt THEN
686 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
687 p_token => g_progress,
688 p_message => 'PO_PDOI_COLUMN_NOT_NULL');
689 END IF;
690 Fnd_message.set_name('PO','PO_PDOI_COLUMN_NOT_NULL');
691 Fnd_message.set_token( token => 'COLUMN_NAME'
692 , VALUE => 'Document Number');
693 FND_MSG_PUB.Add;
694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
695 END IF; -- End of IF g_cpa_csr.document_num IS NOT NULL
696
697 -- Validate ship_to_location
698 IF g_cpa_csr.ship_to_location_id IS NOT NULL THEN
699 g_progress := '306';
700 IF g_debug_stmt THEN
701 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
702 p_token => g_progress,
703 p_message => 'Before calling PO_LINE_LOCATIONS_SV1.val_location_id for Ship to location');
704 END IF;
705
706 is_valid := PO_LINE_LOCATIONS_SV1.val_location_id(
707 X_location_id => g_cpa_csr.ship_to_location_id,
708 X_location_type => 'SHIP_TO');
709 g_progress := '307';
710 IF g_debug_stmt THEN
711 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
712 p_token => g_progress,
713 p_message => 'After calling PO_LINE_LOCATIONS_SV1.val_location_id for Ship to Location');
714 END IF;
715 IF (is_valid = FALSE) THEN
716 g_progress := '308';
717 IF g_debug_stmt THEN
718 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
719 p_token => g_progress,
720 p_message => 'PO_PDOI_INVALID_SHIP_LOC_ID');
721 END IF;
722 g_cpa_csr.ship_to_location_id := NULL;
723 END IF;
724 END IF; -- End of validate ship_to_location
725
726 -- Validate bill_to_location
727 IF g_cpa_csr.bill_to_location_id IS NOT NULL THEN
728 g_progress := '309';
729 IF g_debug_stmt THEN
730 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
731 p_token => g_progress,
732 p_message => 'Before calling PO_LINE_LOCATIONS_SV1.val_location_id for Bill to location');
733 END IF;
734
735 is_valid := PO_LINE_LOCATIONS_SV1.val_location_id(
736 X_location_id => g_cpa_csr.bill_to_location_id,
737 X_location_type => 'BILL_TO');
738 g_progress := '310';
739 IF g_debug_stmt THEN
740 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
741 p_token => g_progress,
742 p_message => 'After calling PO_LINE_LOCATIONS_SV1.val_location_id for Bill to location');
743 END IF;
744
745 IF (is_valid = FALSE) THEN
746 g_progress := '311';
747 IF g_debug_stmt THEN
748 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
749 p_token => g_progress,
750 p_message => 'PO_PDOI_INVALID_BILL_LOC_ID');
751 END IF;
752 g_cpa_csr.bill_to_location_id := NULL;
753 END IF;
754 END IF; -- End of validate bill_to_location
755 EXCEPTION
756 WHEN FND_API.G_EXC_ERROR THEN
757 x_return_status := FND_API.G_RET_STS_ERROR;
758 g_progress := '320';
759 IF g_debug_stmt THEN
760 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
761 p_token => g_progress,
762 p_message => 'Expected Error');
763 END IF;
764 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
766 g_progress := '321';
767 IF g_debug_unexp THEN
768 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
769 p_token => g_progress,
770 p_message => 'Unexpected Error');
771 END IF;
772 WHEN OTHERS THEN
773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
774 g_progress := '322';
775 IF g_debug_unexp THEN
776 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
777 p_token => g_progress,
778 p_message => SQLERRM);
779 END IF;
780 FND_MSG_PUB.add_exc_msg(
781 p_pkg_name => 'PO_SOURCING_PVT',
782 p_procedure_name => l_api_name,
783 p_error_text => NULL);
784 END VALIDATE_CPA;
785
786 -------------------------------------------------------------------------------
787 --Start of Comments
788 --Name: INSERT_CPA
789 --Pre-reqs:
790 -- None
791 --Modifies:
792 -- Transaction tables for the requested document
793 --Locks:
794 -- None.
795 --Function:
796 -- inserts the data into the PO_HEADERS table to create CPA
797 --Parameters:
798 --IN:
799 --p_auction_header_id
800 -- Id of the negotiation
801 --p_bid_number
802 -- Bid Number for which is negotiation is awarded
803 --p_sourcing_k_doc_type
804 -- Represents the OKC document type that would be created into a CPA
805 -- The document type that Sourcing has seeded in Contracts.
806 --p_conterms_exist_flag
807 -- Whether the sourcing document has contract template attached.
808 --p_document_creation_method
809 -- Column specific to DBI. Sourcing will pass a value of AWARD_SOURCING
810 --OUT:
811 --x_document_id
812 -- The unique identifier for the newly created document.
813 --x_document_number
814 -- The document number that would uniquely identify a document in a given organization.
815 --x_return_status
816 -- The standard OUT parameter giving return status of the API call.
817 -- FND_API.G_RET_STS_ERROR - for expected error
818 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
819 -- FND_API.G_RET_STS_SUCCESS - for success
820 --Notes:
821 -- None
822 --Testing:
823 -- None
824 --End of Comments
825 -------------------------------------------------------------------------------
826 PROCEDURE INSERT_CPA (
827 p_auction_header_id IN PON_AUCTION_HEADERS_ALL.auction_header_id%TYPE,
828 p_bid_number IN PON_BID_HEADERS.bid_number%TYPE,
829 p_sourcing_k_doc_type IN VARCHAR2,
830 p_conterms_exist_flag IN PO_HEADERS_ALL.conterms_exist_flag%TYPE,
831 p_document_creation_method IN VARCHAR2,
832 x_document_id OUT NOCOPY PO_HEADERS_ALL.po_header_id%TYPE,
833 x_document_number OUT NOCOPY PO_HEADERS_ALL.segment1%TYPE,
834 x_return_status OUT NOCOPY VARCHAR2
835 ) IS
836 l_rowid VARCHAR2(30);
837 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
838 l_document_num PO_HEADERS_INTERFACE.document_num%TYPE;
839 l_current_org PO_SYSTEM_PARAMETERS.org_id%TYPE;
840 l_org_assign_rec PO_GA_ORG_ASSIGNMENTS%ROWTYPE;
841 l_org_row_id ROWID;
842 l_return_status VARCHAR2(1);
843 l_contract_doc_type VARCHAR2(150);
844 l_contracts_call_exception EXCEPTION;
845 l_msg_data VARCHAR2(2000);
846 l_msg_count NUMBER;
847 l_manual BOOLEAN;
848 x_document_num PO_HEADERS.segment1%TYPE:=null;
849 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_CPA';
850 BEGIN
851
852 -- Initialize API return status to success
853 x_return_status := FND_API.G_RET_STS_SUCCESS;
854
855 x_document_num := g_cpa_csr.document_num;
856 x_document_number := g_cpa_csr.document_num;
857
858 IF (g_params_rec.po_num_code = 'AUTOMATIC') THEN
859 l_manual := FALSE;
860 ELSE
861 l_manual := TRUE;
862 END IF;
863
864 g_progress := '400';
865 IF g_debug_stmt THEN
866 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
867 p_token => g_progress,
868 p_message => 'Before calling PO_HEADERS_PKG_S0.Insert_Row');
869 END IF;
870 l_current_org := PO_GA_PVT.get_current_org; -- <R12 MOAC>
871 PO_HEADERS_PKG_S0.Insert_Row(
872 X_Rowid => l_rowid,
873 X_Po_Header_Id => l_po_header_id,
874 X_Agent_Id => g_cpa_csr.agent_id,
875 X_Type_Lookup_Code => g_cpa_csr.document_subtype,
876 X_Last_Update_Date => g_cpa_csr.last_update_date,
877 X_Last_Updated_By => g_cpa_csr.last_updated_by,
878 X_Segment1 => x_document_num,
879 X_Summary_Flag => 'N',
880 X_Enabled_Flag => 'Y',
881 X_Segment2 => NULL,
882 X_Segment3 => NULL,
883 X_Segment4 => NULL,
884 X_Segment5 => NULL,
885 X_Start_Date_Active => NULL,
886 X_End_Date_Active => NULL,
887 X_Last_Update_Login => nvl(g_cpa_csr.last_update_login,fnd_global.login_id),
888 X_Creation_Date => g_cpa_csr.creation_date,
889 X_Created_By => g_cpa_csr.created_by,
890 X_Vendor_Id => g_cpa_csr.vendor_id,
891 X_Vendor_Site_Id => g_cpa_csr.vendor_site_id,
892 X_Vendor_Contact_Id => g_cpa_csr.vendor_contact_id,
893 X_Ship_To_Location_Id => g_cpa_csr.ship_to_location_id,
894 X_Bill_To_Location_Id => g_cpa_csr.bill_to_location_id,
895 X_Terms_Id => g_cpa_csr.terms_id,
896 X_Ship_Via_Lookup_Code => g_cpa_csr.ship_via_lookup_code,
897 X_Fob_Lookup_Code => g_cpa_csr.fob_lookup_code,
898 X_Pay_On_Code => g_cpa_csr.pay_on_code,
899 X_Freight_Terms_Lookup_Code => g_cpa_csr.freight_terms_lookup_code,
900 X_Status_Lookup_Code => NULL,
901 X_Currency_Code => g_cpa_csr.h_currency_code,
902 X_Rate_Type => g_cpa_csr.h_rate_type,
903 X_Rate_Date => nvl(g_cpa_csr.h_rate_date,trunc(sysdate)),
904 X_Rate => g_cpa_csr.h_rate,
905 X_From_Header_Id => NULL,
906 X_From_Type_Lookup_Code => NULL,
907 X_Start_Date => g_cpa_csr.effective_date,
908 X_End_Date => g_cpa_csr.expiration_date,
909 X_Blanket_Total_Amount => g_cpa_csr.amount_agreed,
910 X_Authorization_Status => NULL,
911 X_Revision_Num => g_cpa_csr.revision_num,
912 X_Revised_Date => NULL,
913 X_Approved_Flag => NULL,
914 X_Approved_Date => NULL,
915 X_Amount_Limit => nvl(g_cpa_csr.amount_limit, g_cpa_csr.amount_agreed),
916 X_Min_Release_Amount => NULL,
917 X_Note_To_Authorizer => NULL,
918 X_Note_To_Vendor => NULL,
919 X_Note_To_Receiver => NULL,
920 X_Print_Count => g_cpa_csr.print_count,
921 X_Printed_Date => NULL,
922 X_Vendor_Order_Num => NULL,
923 X_Confirming_Order_Flag => g_cpa_csr.confirming_order_flag,
924 X_Comments => NULL,
925 X_Reply_Date => NULL,
926 X_Reply_Method_Lookup_Code => NULL,
927 X_Rfq_Close_Date => NULL,
928 X_Quote_Type_Lookup_Code => NULL,
929 X_Quotation_Class_Code => NULL,
930 X_Quote_Warning_Delay_Unit => NULL,
931 X_Quote_Warning_Delay => NULL,
932 X_Quote_Vendor_Quote_Number => NULL,
933 X_Acceptance_Required_Flag => g_cpa_csr.acceptance_required_flag,
934 X_Acceptance_Due_Date => NULL,
935 X_Closed_Date => NULL,
936 X_User_Hold_Flag => NULL,
937 X_Approval_Required_Flag => NULL,
938 X_Cancel_Flag => 'N',
939 X_Firm_Status_Lookup_Code => nvl(g_cpa_csr.h_firm_status_lookup_code,'N'),
940 X_Firm_Date => NULL,
941 X_Frozen_Flag => g_cpa_csr.frozen_flag,
942 X_Global_Agreement_Flag => g_cpa_csr.global_agreement_flag,
943 X_Attribute_Category => NULL,
944 X_Attribute1 => NULL,
945 X_Attribute2 => NULL,
946 X_Attribute3 => NULL,
947 X_Attribute4 => NULL,
948 X_Attribute5 => NULL,
949 X_Attribute6 => NULL,
950 X_Attribute7 => NULL,
951 X_Attribute8 => NULL,
952 X_Attribute9 => NULL,
953 X_Attribute10 => NULL,
954 X_Attribute11 => NULL,
955 X_Attribute12 => NULL,
956 X_Attribute13 => NULL,
957 X_Attribute14 => NULL,
958 X_Attribute15 => NULL,
959 X_Closed_Code => g_cpa_csr.h_closed_code,
960 X_Ussgl_Transaction_Code => NULL,
961 X_Government_Context => NULL,
962 X_Supply_Agreement_flag => 'N',
963 X_Manual => l_manual,
964 X_Price_Update_Tolerance => NULL,
965 X_Global_Attribute_Category => NULL,
966 X_Global_Attribute1 => NULL,
967 X_Global_Attribute2 => NULL,
968 X_Global_Attribute3 => NULL,
969 X_Global_Attribute4 => NULL,
970 X_Global_Attribute5 => NULL,
971 X_Global_Attribute6 => NULL,
972 X_Global_Attribute7 => NULL,
973 X_Global_Attribute8 => NULL,
974 X_Global_Attribute9 => NULL,
975 X_Global_Attribute10 => NULL,
976 X_Global_Attribute11 => NULL,
977 X_Global_Attribute12 => NULL,
978 X_Global_Attribute13 => NULL,
979 X_Global_Attribute14 => NULL,
980 X_Global_Attribute15 => NULL,
981 X_Global_Attribute16 => NULL,
982 X_Global_Attribute17 => NULL,
983 X_Global_Attribute18 => NULL,
984 X_Global_Attribute19 => NULL,
985 X_Global_Attribute20 => NULL,
986 p_shipping_control => g_cpa_csr.shipping_control,
987 p_encumbrance_required_flag => NULL,
988 p_org_id => l_current_org -- <R12 MOAC>
989 );
990
991 x_document_id := l_po_header_id;
992 g_progress := '401';
993 IF g_debug_stmt THEN
994 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
995 p_token => g_progress,
996 p_message => 'After calling PO_HEADERS_PKG_S0.Insert_Row. po_header_id = '||l_po_header_id||': Segment1 = '||l_document_num);
997
998 END IF;
999
1000 -- After insert into po_headers, insert a row into org_assignments for a global agreement
1001 IF nvl(g_cpa_csr.global_agreement_flag, 'N') = 'Y' then
1002
1003 l_current_org := PO_GA_PVT.get_current_org;
1004
1005 -- call the GA org assignments table handler to insert a row
1006 -- for the owning org into the org assignments table
1007 l_org_assign_rec.po_header_id := l_po_header_id;
1008 l_org_assign_rec.organization_id := l_current_org;
1009 l_org_assign_rec.purchasing_org_id := l_org_assign_rec.organization_id;
1010 l_org_assign_rec.enabled_flag := 'Y';
1011 l_org_assign_rec.vendor_site_id := g_cpa_csr.vendor_site_id;
1012 l_org_assign_rec.last_update_date := g_cpa_csr.last_update_date;
1013 l_org_assign_rec.last_updated_by := g_cpa_csr.last_updated_by;
1014 l_org_assign_rec.creation_date := g_cpa_csr.creation_date;
1015 l_org_assign_rec.created_by := g_cpa_csr.created_by;
1016 l_org_assign_rec.last_update_login := g_cpa_csr.last_update_login;
1017
1018 g_progress := '402';
1019 IF g_debug_stmt THEN
1020 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1021 p_token => g_progress,
1022 p_message => 'Before calling PO_GA_ORG_ASSIGN_PVT.Insert_Row');
1023 END IF;
1024
1025 PO_GA_ORG_ASSIGN_PVT.insert_row(
1026 p_init_msg_list => FND_API.g_true,
1027 x_return_status => l_return_status,
1028 p_org_assign_rec => l_org_assign_rec,
1029 x_row_id => l_org_row_id);
1030 g_progress := '403';
1031 IF g_debug_stmt THEN
1032 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1033 p_token => g_progress,
1034 p_message => 'After calling PO_GA_ORG_ASSIGN_PVT.Insert_Row');
1035 END IF;
1036
1037 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1038 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1039 RAISE FND_API.G_EXC_ERROR;
1040 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1041 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1042 END IF;
1043 END IF;
1044
1045 END IF;
1046
1047 g_progress := '404';
1048 IF g_debug_stmt THEN
1049 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1050 p_token => g_progress,
1051 p_message => 'Before calling PO_NEGOTIATIONS_SV2.copy_attachments fro PON_AUCTION_HEADERS_ALL');
1052 END IF;
1053
1054 -- Copy attachments from negotiation header to CPA using the procedure
1055 PO_NEGOTIATIONS_SV2.copy_attachments(
1056 X_from_entity_name => 'PON_AUCTION_HEADERS_ALL',
1057 X_from_pk1_value => p_auction_header_id,
1058 X_from_pk2_value => NULL,
1059 X_from_pk3_value => NULL,
1060 X_from_pk4_value => NULL,
1061 X_from_pk5_value => NULL,
1062 X_to_entity_name => 'PO_HEADERS',
1063 X_to_pk1_value => l_po_header_id,
1064 X_to_pk2_value => NULL,
1065 X_to_pk3_value => NULL,
1066 X_to_pk4_value => NULL,
1067 X_to_pk5_value => NULL,
1068 X_created_by => g_cpa_csr.created_by,
1069 X_last_update_login => g_cpa_csr.last_update_login,
1070 X_program_application_id => NULL,
1071 X_program_id => NULL,
1072 X_request_id => NULL,
1073 X_column1 => 'NEG');
1074
1075 g_progress := '405';
1076 IF g_debug_stmt THEN
1077 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1078 p_token => g_progress,
1079 p_message => 'After calling PO_NEGOTIATIONS_SV2.copy_attachments for PON_AUCTION_HEADERS_ALL');
1080 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1081 p_token => g_progress,
1082 p_message => 'Before calling PO_NEGOTIATIONS_SV2.copy_attachments for PON_BID_HEADERS');
1083 END IF;
1084
1085 --copy attachment from bid header to cpa header
1086 PO_NEGOTIATIONS_SV2.copy_attachments(
1087 X_from_entity_name => 'PON_BID_HEADERS',
1088 X_from_pk1_value => p_auction_header_id,
1089 X_from_pk2_value => p_bid_number,
1090 X_from_pk3_value => '',
1091 X_from_pk4_value => '',
1092 X_from_pk5_value => '',
1093 X_to_entity_name => 'PO_HEADERS',
1094 X_to_pk1_value => l_po_header_id,
1095 X_to_pk2_value => '',
1096 X_to_pk3_value => '',
1097 X_to_pk4_value => '',
1098 X_to_pk5_value => '',
1099 X_created_by => g_cpa_csr.created_by,
1100 X_last_update_login => g_cpa_csr.last_update_login,
1101 X_program_application_id => '',
1102 X_program_id => '',
1103 X_request_id => NULL,
1104 X_column1 => 'NEG');
1105
1106 g_progress := '406';
1107 IF g_debug_stmt THEN
1108 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1109 p_token => g_progress,
1110 p_message => 'After calling PO_NEGOTIATIONS_SV2.copy_attachments for PON_BID_HEADERS');
1111 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1112 p_token => g_progress,
1113 p_message => 'Before calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_BID_BUYER_NOTES');
1114 END IF;
1115
1116 -- build and attach bid notes as internal to PO attachments on cpa header.
1117 PO_NEGOTIATIONS_SV2.add_attch_dynamic(
1118 x_from_entity_name => 'PON_BID_BUYER_NOTES',
1119 x_auction_header_id => p_auction_header_id,
1120 x_auction_line_number => NULL,
1121 x_bid_number => p_bid_number,
1122 x_bid_line_number => NULL,
1123 x_to_entity_name => 'PO_HEADERS',
1124 x_to_pk1_value => l_po_header_id,
1125 x_created_by => g_cpa_csr.created_by,
1126 x_last_update_login => g_cpa_csr.last_update_login,
1127 x_program_application_id => NULL,
1128 x_program_id => NULL,
1129 x_request_id => NULL);
1130
1131 g_progress := '407';
1132 IF g_debug_stmt THEN
1133 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1134 p_token => g_progress,
1135 p_message => 'After calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_BID_BUYER_NOTES');
1136 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1137 p_token => g_progress,
1138 p_message => 'Before calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_AUC_SUPPLIER_HEADER_NOTES');
1139 END IF;
1140
1141 -- Build and attach negotiation header notes as 'To Supplier' attachments on CPA header
1142 PO_NEGOTIATIONS_SV2.add_attch_dynamic(
1143 x_from_entity_name => 'PON_AUC_SUPPLIER_HEADER_NOTES',
1144 x_auction_header_id => p_auction_header_id,
1145 x_auction_line_number => NULL,
1146 x_bid_number => NULL,
1147 x_bid_line_number => NULL,
1148 x_to_entity_name => 'PO_HEADERS',
1149 x_to_pk1_value => l_po_header_id,
1150 x_created_by => g_cpa_csr.created_by,
1151 x_last_update_login => g_cpa_csr.last_update_login,
1152 x_program_application_id => NULL,
1153 x_program_id => NULL,
1154 x_request_id => NULL);
1155
1156 g_progress := '408';
1157 IF g_debug_stmt THEN
1158 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1159 p_token => g_progress,
1160 p_message => 'After calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_AUC_SUPPLIER_HEADER_NOTES');
1161 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1162 p_token => g_progress,
1163 p_message => 'Before calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_BID_HEADER_ATTRIBUTES');
1164 END IF;
1165 -- Build and attach negotiation/bid header attributes as 'To Supplier'attachment on CPA Header
1166 PO_NEGOTIATIONS_SV2.add_attch_dynamic(
1167 x_from_entity_name => 'PON_BID_HEADER_ATTRIBUTES',
1168 x_auction_header_id => p_auction_header_id,
1169 x_auction_line_number => NULL,
1170 x_bid_number => p_bid_number,
1171 x_bid_line_number => NULL,
1172 x_to_entity_name => 'PO_HEADERS',
1173 x_to_pk1_value => l_po_header_id,
1174 x_created_by => g_cpa_csr.created_by,
1175 x_last_update_login => g_cpa_csr.last_update_login,
1176 x_program_application_id => NULL,
1177 x_program_id => NULL,
1178 x_request_id => NULL);
1179
1180 g_progress := '409';
1181 IF g_debug_stmt THEN
1182 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1183 p_token => g_progress,
1184 p_message => 'After calling PO_NEGOTIATIONS_SV2.add_attch_dynamic for PON_BID_HEADER_ATTRIBUTES');
1185 END IF;
1186 IF (g_params_rec.po_num_code='AUTOMATIC') AND
1187 (g_cpa_csr.document_num = 'CPA 11.5.10+') THEN
1188
1189 g_progress:= '410';
1190 IF g_debug_stmt THEN
1191 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1192 p_token => g_progress,
1193 p_message => 'Before Selecting document number from po_unique_identifier_cont_all');
1194 END IF;
1195
1196 -- bug5176308
1197 -- Consolidate PO # generation code into one API
1198
1199 x_document_num :=
1200 PO_CORE_SV1.default_po_unique_identifier
1201 ( p_table_name => 'PO_HEADERS',
1202 p_org_id => g_cpa_csr.org_id
1203 );
1204
1205 g_progress:= '411';
1206 IF g_debug_stmt THEN
1207 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1208 p_token => g_progress,
1209 p_message => 'After Selecting document number from po_unique_identifier_cont_all');
1210 END IF;
1211
1212
1213 x_document_number := x_document_num;
1214 END IF;
1215
1216 g_progress:= '412';
1217 IF g_debug_stmt THEN
1218 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1219 p_token => g_progress,
1220 p_message => 'Before updating po_headers_all for conterms_exist_flag and document number');
1221 END IF;
1222
1223 --SQL WHAT: Updates Conterms_exist_flag, segment1 and document_creation_method
1224 --SQL WHY: To handle creation of an automatic document_number when the po_num_code is AUTOMATIC
1225 -- Update pf conterms_exist_flag and document_creation_method should have been
1226 -- handled in PO_HEADERS_PKG_S0.Insert_Row table handler.
1227 -- As this file is not allowed to update for 11.5.10, added separate update statement.
1228 --SQl Join:None
1229
1230 UPDATE PO_HEADERS_ALL
1231 SET conterms_exist_flag = decode(p_conterms_exist_flag,'Y','Y','N'),
1232 document_creation_method = p_document_creation_method,
1233 segment1 = x_document_num
1234 WHERE po_header_id = l_po_header_id;
1235
1236 g_progress:= '413';
1237 IF g_debug_stmt THEN
1238 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1239 p_token => g_progress,
1240 p_message => 'Before updating po_headers_all for conterms_exist_flag and document number');
1241 END IF;
1242
1243 --Copy contract terms if sourcing doc had a template attached.
1244 IF (p_conterms_exist_flag = 'Y') THEN
1245
1246 l_contract_doc_type:= PO_CONTERMS_UTL_GRP.GET_PO_CONTRACT_DOCTYPE(
1247 p_sub_doc_type=>g_cpa_csr.document_subtype);
1248 g_progress:= '414';
1249 IF g_debug_stmt THEN
1250 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1251 p_token => g_progress,
1252 p_message => 'Contracts template attached'||'-'||'l_contract_doc_type:'||l_contract_doc_type);
1253 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1254 p_token => g_progress,
1255 p_message => 'before call okc_terms_copy_grp.copy_doc');
1256 END IF;
1257
1258 OKC_TERMS_COPY_GRP.copy_doc(
1259 p_api_version => 1.0,
1260 p_source_doc_type => p_sourcing_k_doc_type,
1261 p_source_doc_id => p_bid_number,
1262 p_target_doc_type => l_contract_doc_type,
1263 p_target_doc_id => l_po_header_id,
1264 p_keep_version => 'Y',
1265 p_article_effective_date => sysdate,
1266 p_initialize_status_yn => 'N',
1267 p_reset_Fixed_Date_yn => 'N',
1268 p_copy_del_attachments_yn => 'Y',
1269 p_copy_deliverables => 'Y',
1270 p_document_number => x_document_num,
1271 x_return_status => l_return_status,
1272 x_msg_data => l_msg_data,
1273 x_msg_count => l_msg_count
1274 );
1275
1276 g_progress:='415';
1277 IF g_debug_stmt THEN
1278 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1279 p_token => g_progress,
1280 p_message => 'after call okc_terms_copy_grp.copy_doc.Return status:'||l_return_status);
1281 END IF;
1282
1283 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1284 RAISE l_Contracts_call_exception;
1285 END IF; -- Return status from contracts
1286
1287 END IF; -- if p_conterms_exist_flag = Y
1288 EXCEPTION
1289 WHEN l_Contracts_call_exception then
1290 g_progress := '416';
1291 x_return_status := FND_API.G_RET_STS_ERROR;
1292
1293 -- put error messages in log
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 => 'Insert_CPA: Inside l_contracts_call_exception');
1298 END IF;
1299 Fnd_message.set_name('PO','PO_API_ERROR');
1300 Fnd_message.set_token( token => 'PROC_CALLER'
1301 , VALUE => 'PO_INTERFACE_S.INSERT_CPA');
1302 Fnd_message.set_token( token => 'PROC_CALLED'
1303 , VALUE => 'OKC_TERMS_CPOY_GRP.COPY_DOC');
1304 FND_MSG_PUB.Add;
1305
1306 IF g_debug_stmt THEN
1307 l_msg_count := FND_MSG_PUB.Count_Msg;
1308 FOR i IN 1..l_msg_count LOOP
1309 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1310 p_token => g_progress||'_EXCEPTION_'||i,
1311 p_message => FND_MSG_PUB.Get(p_msg_index=>i,p_encoded =>'F'));
1312 END LOOP;
1313 END IF;
1314 WHEN OTHERS THEN
1315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1316 g_progress := '417';
1317 IF g_debug_unexp THEN
1318 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1319 p_token => g_progress,
1320 p_message => SQLERRM);
1321 END IF;
1322 FND_MSG_PUB.add_exc_msg(
1323 p_pkg_name => 'PO_SOURCING_PVT',
1324 p_procedure_name => l_api_name,
1325 p_error_text => NULL);
1326 END INSERT_CPA;
1327
1328 -------------------------------------------------------------------------------
1329 --Start of Comments
1330 --Name: create_cpa
1331 --Pre-reqs:
1332 -- None
1333 --Modifies:
1334 -- None
1335 --Locks:
1336 -- None.
1337 --Function:
1338 -- Creates Contract Purchase Agreement from Sourcing document
1339 --Parameters:
1340 --IN:
1341 --p_interface_header_id
1342 -- The id that will be used to uniquely identify a row in the PO_HEADERS_INTERFACE table
1343 --p_auction_header_id
1344 -- Id of the negotiation
1345 --p_bid_number
1346 -- Bid Number for which is negotiation is awarded
1347 --p_sourcing_k_doc_type
1348 -- Represents the OKC document type that would be created into a CPA
1349 -- The document type that Sourcing has seeded in Contracts.
1350 --p_conterms_exist_flag
1351 -- Whether the sourcing document has contract template attached.
1352 --p_document_creation_method
1353 -- Column specific to DBI. Sourcing will pass a value of AWARD_SOURCING
1354 --OUT:
1355 --x_document_id
1356 -- The unique identifier for the newly created document.
1357 --x_document_number
1358 -- The document number that would uniquely identify a document in a given organization.
1359 --x_return_status
1360 -- The standard OUT parameter giving return status of the API call.
1361 -- FND_API.G_RET_STS_ERROR - for expected error
1362 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
1363 -- FND_API.G_RET_STS_SUCCESS - for success
1364 --Notes:
1365 -- None
1366 --Testing:
1367 -- None
1368 --End of Comments
1369 -------------------------------------------------------------------------------
1370 PROCEDURE create_cpa (
1371 x_return_status OUT NOCOPY VARCHAR2,
1372 x_msg_count OUT NOCOPY NUMBER,
1373 x_msg_data OUT NOCOPY VARCHAR2,
1374 p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%TYPE,
1375 p_auction_header_id IN PON_AUCTION_HEADERS_ALL.auction_header_id%TYPE,
1376 p_bid_number IN PON_BID_HEADERS.bid_number%TYPE,
1377 p_sourcing_k_doc_type IN VARCHAR2,
1378 p_conterms_exist_flag IN PO_HEADERS_ALL.conterms_exist_flag%TYPE,
1379 p_document_creation_method IN PO_HEADERS_ALL.document_creation_method%TYPE,
1380 x_document_id OUT NOCOPY PO_HEADERS_ALL.po_header_id%TYPE,
1381 x_document_number OUT NOCOPY PO_HEADERS_ALL.segment1%TYPE
1382 ) IS
1383 l_return_status VARCHAR2(1);
1384 l_api_name CONSTANT VARCHAR2(30) := 'create_cpa';
1385
1386 l_document_type PO_HEADERS_INTERFACE.document_type_code%TYPE;
1387 l_document_subtype PO_HEADERS_INTERFACE.document_subtype%TYPE;
1388 l_action PO_HEADERS_INTERFACE.action%TYPE;
1389 BEGIN
1390 -- Initialize API return status to success
1391 x_return_status := FND_API.G_RET_STS_SUCCESS;
1392
1393 g_progress:='500';
1394 IF g_interface_cursor%ISOPEN THEN
1395 CLOSE g_interface_cursor;
1396 END IF;
1397 OPEN g_interface_cursor(p_interface_header_id);
1398
1399 FETCH g_interface_cursor INTO g_cpa_csr;
1400
1401 IF g_interface_cursor%NOTFOUND THEN
1402 CLOSE g_interface_cursor;
1403 IF g_debug_stmt THEN
1404 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1405 p_token => g_progress,
1406 p_message => 'PO_ALL_NO_DRILLDOWN: '||SQLERRM);
1407 END IF;
1408 Fnd_message.set_name('PO','PO_ALL_NO_DRILLDOWN');
1409 FND_MSG_PUB.Add;
1410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1411 END IF;
1412
1413 l_document_type := g_cpa_csr.document_type_code;
1414 l_document_subtype := g_cpa_csr.document_subtype;
1415 l_action := g_cpa_csr.action;
1416
1417 IF l_document_subtype = 'CONTRACT' THEN
1418 IF l_action = 'NEW' THEN
1419 g_progress := '501';
1420 IF g_debug_stmt THEN
1421 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1422 p_token => g_progress,
1423 p_message => 'Before calling po_sourcing_pvt.default_cpa');
1424 END IF;
1425 -- Default the required fields in the record
1426 DEFAULT_CPA(
1427 x_return_status => l_return_status
1428 );
1429
1430 g_progress := '502';
1431 IF g_debug_stmt THEN
1432 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1433 p_token => g_progress,
1434 p_message => 'After calling po_sourcing_pvt.default_cpa');
1435 END IF;
1436
1437 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1438 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1439 RAISE FND_API.G_EXC_ERROR;
1440 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1442 END IF;
1443 END IF;
1444
1445 -- Validate the required fields in the record
1446 g_progress := '503';
1447 IF g_debug_stmt THEN
1448 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1449 p_token => g_progress,
1450 p_message => 'Before calling po_sourcing_pvt.validate_cpa');
1451 END IF;
1452
1453 VALIDATE_CPA(
1454 x_return_status => l_return_status
1455 );
1456
1457 g_progress := '504';
1458 IF g_debug_stmt THEN
1459 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1460 p_token => g_progress,
1461 p_message => 'After calling po_sourcing_pvt.validate_cpa');
1462 END IF;
1463
1464 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1465 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1466 RAISE FND_API.G_EXC_ERROR;
1467 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1469 END IF;
1470 END IF;
1471
1472 -- Insert record in the po_headers table and add attachments and contract terms
1473 g_progress := '505';
1474 IF g_debug_stmt THEN
1475 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1476 p_token => g_progress,
1477 p_message => 'Before calling po_sourcing_pvt.insert_cpa');
1478 END IF;
1479
1480 INSERT_CPA (
1481 p_auction_header_id => p_auction_header_id,
1482 p_bid_number => p_bid_number,
1483 p_sourcing_k_doc_type => p_sourcing_k_doc_type,
1484 p_conterms_exist_flag => p_conterms_exist_flag,
1485 p_document_creation_method => p_document_creation_method,
1486 x_document_id => x_document_id,
1487 x_document_number => x_document_number,
1488 x_return_status => l_return_status
1489 );
1490
1491 g_progress := '506';
1492 IF g_debug_stmt THEN
1493 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1494 p_token => g_progress,
1495 p_message => 'After calling po_sourcing_pvt.insert_cpa');
1496 END IF;
1497
1498 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1499 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1500 RAISE FND_API.G_EXC_ERROR;
1501 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1503 END IF;
1504 END IF;
1505
1506 ELSE
1507 g_progress := '507';
1508 IF g_debug_stmt THEN
1509 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1510 p_token => g_progress,
1511 p_message => 'Invalid Action in the interface table');
1512 END IF;
1513 Fnd_message.set_name('PO','PO_PDOI_INVALID_ACTION');
1514 Fnd_message.set_token( token => 'VALUE'
1515 , VALUE => g_cpa_csr.action);
1516 FND_MSG_PUB.Add;
1517 RAISE FND_API.G_EXC_ERROR;
1518 END IF; -- End of l_action = 'NEW'
1519 ELSE
1520 g_progress := '508';
1521 IF g_debug_stmt THEN
1522 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1523 p_token => g_progress,
1524 p_message => 'Invalid Document Subtype Code in the interface table');
1525 END IF;
1526 Fnd_message.set_name('PO','PO_PDOI_INVALID_VALUE');
1527 Fnd_message.set_token( token => 'COLUMN_NAME'
1528 , VALUE => 'Document Subtype');
1529 Fnd_message.set_token( token => 'VALUE'
1530 , VALUE => 'CONTRACT');
1531 FND_MSG_PUB.Add;
1532
1533 RAISE FND_API.G_EXC_ERROR;
1534 END IF; -- End of l_document_type = 'CONTRACT'
1535 EXCEPTION
1536 WHEN FND_API.G_EXC_ERROR THEN
1537 x_document_number := NULL;
1538 x_document_id := NULL;
1539 x_return_status := FND_API.G_RET_STS_ERROR;
1540 g_progress := '510';
1541 IF g_debug_stmt THEN
1542 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1543 p_token => g_progress,
1544 p_message => 'Expected Error');
1545 END IF;
1546
1547 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1548 x_document_number := NULL;
1549 x_document_id := NULL;
1550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1551 g_progress := '511';
1552 IF g_debug_unexp THEN
1553 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1554 p_token => g_progress,
1555 p_message => 'Unexpected Error');
1556 END IF;
1557 WHEN OTHERS THEN
1558 x_document_number := NULL;
1559 x_document_id := NULL;
1560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1561 g_progress := '512';
1562 IF g_debug_unexp THEN
1563 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1564 p_token => g_progress,
1565 p_message => SQLERRM);
1566 END IF;
1567
1568 FND_MSG_PUB.add_exc_msg(
1569 p_pkg_name => 'PO_SOURCING_PVT',
1570 p_procedure_name => l_api_name,
1571 p_error_text => NULL);
1572 END CREATE_CPA;
1573
1574
1575 -------------------------------------------------------------------------------
1576 --Start of Comments
1577 --Name: DELETE_INTERFACE_HEADER
1578 --Pre-reqs:
1579 -- None
1580 --Modifies:
1581 -- po_headers_interface
1582 --Locks:
1583 -- None.
1584 --Function:
1585 -- This deletes the interface header row from interface table
1586 --Parameters:
1587 --IN:
1588 --p_interface_header_id
1589 -- The id that will be used to uniquely identify a row in the PO_HEADERS_INTERFACE table
1590 --OUT:
1591 --x_return_status
1592 -- The standard OUT parameter giving return status of the API call.
1593 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
1594 -- FND_API.G_RET_STS_SUCCESS - for success
1595 --Notes:
1596 -- None
1597 --Testing:
1598 -- None
1599 --End of Comments
1600 -------------------------------------------------------------------------------
1601
1602 PROCEDURE DELETE_INTERFACE_HEADER (
1603 p_interface_header_id IN PO_HEADERS_INTERFACE.INTERFACE_HEADER_ID%TYPE,
1604 x_return_status OUT NOCOPY VARCHAR2
1605 ) IS
1606 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_INTERFACE_HEADER';
1607 BEGIN
1608 -- Initialize API return status to success
1609 x_return_status := FND_API.G_RET_STS_SUCCESS;
1610
1611 DELETE po_headers_interface
1612 WHERE interface_header_id = p_interface_header_id;
1613
1614 g_progress := '600';
1615 IF g_debug_stmt THEN
1616 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1617 p_token => g_progress,
1618 p_message => 'No of Records deleted from PO_HEADERS_INTERFACE'||SQL%rowcount);
1619 END IF;
1620 EXCEPTION
1621 WHEN OTHERS THEN
1622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1623 g_progress := '601';
1624 IF g_debug_unexp THEN
1625 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1626 p_token => g_progress,
1627 p_message => SQLERRM);
1628 END IF;
1629 FND_MSG_PUB.add_exc_msg(
1630 p_pkg_name => 'PO_SOURCING_PVT',
1631 p_procedure_name => l_api_name,
1632 p_error_text => NULL);
1633 END DELETE_INTERFACE_HEADER;
1634
1635
1636 END PO_SOURCING_PVT;