[Home] [Help]
PACKAGE BODY: APPS.PON_SOURCING_OPENAPI_GRP
Source
1 PACKAGE BODY pon_sourcing_openapi_grp AS
2 /* $Header: PONRNBAB.pls 120.36.12020000.3 2013/05/02 08:24:35 hvutukur ship $ */
3
4 g_call_purge BOOLEAN := false;
5
6 PROCEDURE Add_Catalog_Descriptors (p_document_number IN NUMBER,
7 p_interface_id IN NUMBER,
8 p_from_line_number IN NUMBER,
9 p_to_line_number IN NUMBER);
10
11 PROCEDURE INITIALISE_GLOBALS(p_interface_id IN NUMBER);
12
13
14 FUNCTION GET_PKEY(p_pkey1 Number, p_pkey2 Number DEFAULT NULL)
15 RETURN PO_TBL_NUMBER
16 IS
17 l_pkey PO_TBL_NUMBER;
18 BEGIN
19 l_pkey := PO_TBL_NUMBER();
20 if ( p_pkey2 is null ) then
21 l_pkey.extend(1);
22 l_pkey(1) := p_pkey1;
23 else
24 l_pkey.extend(2);
25 l_pkey(1) := p_pkey1;
26 l_pkey(2) := p_pkey2;
27 end if;
28 return l_pkey;
29 END GET_PKEY;
30
31 PROCEDURE create_draft_neg_interface_pvt (
32 p_interface_id NUMBER,
33 p_is_concurrent_call IN VARCHAR2,
34 p_document_number IN NUMBER,
35 x_document_number OUT NOCOPY NUMBER,
36 x_document_url OUT NOCOPY VARCHAR2,
37 x_request_id OUT NOCOPY NUMBER,
38 x_result OUT NOCOPY VARCHAR2,
39 x_error_code OUT NOCOPY VARCHAR2,
40 x_error_message OUT NOCOPY VARCHAR2);
41
42 /*======================================================================
43 PROCEDURE : create_draft_neg_interface PUBLIC
44 PARAMETERS:
45 p_interface_id IN interface id for data to convert
46 x_document_number OUT NOCOPY newly created draft negotiation number
47 x_document_url OUT NOCOPY document_url to edit the draft negotiation
48 x_result OUT NOCOPY result returned to called indicating SUCCESS or FAILURE
49 x_error_code OUT NOCOPY error code if x_result is FAILURE, NULL otherwise
50 x_error_message OUT NOCOPY error message if x_result is FAILURE, NULL otherwise
51 size is 250.
52
53 COMMENT : This is a wrapper over create_draft_neg_interface_pvt. This
54 retains the signature compatibility with PO code
55 ======================================================================*/
56
57 PROCEDURE create_draft_neg_interface (p_interface_id NUMBER,
58 x_document_number OUT NOCOPY NUMBER,
59 x_document_url OUT NOCOPY VARCHAR2,
60 x_result OUT NOCOPY VARCHAR2,
61 x_error_code OUT NOCOPY VARCHAR2,
62 x_error_message OUT NOCOPY VARCHAR2)
63 IS
64 l_request_id NUMBER;
65 BEGIN
66
67 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
68 fnd_log.string(fnd_log.level_statement,
69 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface',
70 'Entered with input -- '||
71 'p_interface_id : '|| p_interface_id);
72 end if;
73
74
75 create_draft_neg_interface_pvt (
76 p_interface_id => p_interface_id,
77 p_is_concurrent_call => 'N',
78 p_document_number => null,
79 x_document_number => x_document_number,
80 x_document_url => x_document_url,
81 x_request_id => l_request_id,
82 x_result => x_result,
83 x_error_code => x_error_code,
84 x_error_message => x_error_message
85 );
86
87 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
88 fnd_log.string(fnd_log.level_statement,
89 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface',
90 'returning with output -- '||
91 'x_document_number : '|| x_document_number ||
92 'x_document_url : ' || x_document_url ||
93 'x_request_id : ' || l_request_id ||
94 'x_result : ' || x_result ||
95 'x_error_code : ' || x_error_code ||
96 'x_error_message : ' || x_error_message);
97 end if;
98
99
100 END;
101
102 /*======================================================================
103 PROCEDURE : create_draft_neg_interface PUBLIC
104 PARAMETERS:
105 p_interface_id IN interface id for data to convert
106 x_document_number OUT NOCOPY newly created draft negotiation number
107 x_document_url OUT NOCOPY document_url to edit the draft negotiation
108 x_concurrent_program_started OUT NOCOPY This will be Y if a concurrent program has stared.
109 x_request_id OUT NOCOPY request id of the concurrent request that is
110 submitted in the case of super large auctions
111 x_result OUT NOCOPY result returned to called indicating SUCCESS or FAILURE
112 x_error_code OUT NOCOPY error code if x_result is FAILURE, NULL otherwise
113 x_error_message OUT NOCOPY error message if x_result is FAILURE, NULL otherwise
114 size is 250.
115
116 COMMENT : This is a wrapper over create_draft_neg_interface_pvt. This
117 retains the signature compatibility with PO code
118 ======================================================================*/
119
120 PROCEDURE create_draft_neg_interface (p_interface_id NUMBER,
121 x_document_number OUT NOCOPY NUMBER,
122 x_document_url OUT NOCOPY VARCHAR2,
123 x_concurrent_program_started OUT NOCOPY VARCHAR2,
124 x_request_id OUT NOCOPY NUMBER,
125 x_result OUT NOCOPY VARCHAR2,
126 x_error_code OUT NOCOPY VARCHAR2,
127 x_error_message OUT NOCOPY VARCHAR2)
128 IS
129 BEGIN
130
131 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
132 fnd_log.string(fnd_log.level_statement,
133 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface',
134 'Entered with input -- '||
135 'p_interface_id : '|| p_interface_id);
136 end if;
137
138 x_request_id := -1;
139
140 x_concurrent_program_started := 'N';
141
142 create_draft_neg_interface_pvt (
143 p_interface_id => p_interface_id,
144 p_is_concurrent_call => 'N',
145 p_document_number => null,
146 x_document_number => x_document_number,
147 x_document_url => x_document_url,
148 x_request_id => x_request_id,
149 x_result => x_result,
150 x_error_code => x_error_code,
151 x_error_message => x_error_message
152 );
153
154 if(x_request_id > 0) then
155
156 x_concurrent_program_started := 'Y';
157
158 end if;
159
160 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
161 fnd_log.string(fnd_log.level_statement,
162 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface',
163 'returning with output -- '||
164 'x_document_number : '|| x_document_number ||
165 'x_document_url : ' || x_document_url ||
166 'x_request_id : ' || x_request_id ||
167 'x_concurrent_program_started : ' || x_concurrent_program_started ||
168 'x_result : ' || x_result ||
169 'x_error_code : ' || x_error_code ||
170 'x_error_message : ' || x_error_message);
171 end if;
172
173
174 END;
175
176 /*======================================================================
177 PROCEDURE : create_draft_neg_interface_pvt PRIVATE
178 PARAMETERS:
179 p_interface_id IN interface id for data to convert
180 p_is_concurrent_call IN Indicates if this procedure is called from a concurrent program or not.
181 Valid values are 'Y' and 'N' with usual meaning
182 x_document_number OUT NOCOPY newly created draft negotiation number
183 x_document_url OUT NOCOPY document_url to edit the draft negotiation
184 x_result OUT NOCOPY result returned to called indicating SUCCESS or FAILURE
185 x_error_code OUT NOCOPY error code if x_result is FAILURE, NULL otherwise
186 x_error_message OUT NOCOPY error message if x_result is FAILURE, NULL otherwise
187 size is 250.
188
189 COMMENT : Create draft negotiation by reading data from interface tables
190 ======================================================================*/
191
192 PROCEDURE create_draft_neg_interface_pvt (
193 p_interface_id NUMBER,
194 p_is_concurrent_call IN VARCHAR2,
195 p_document_number IN NUMBER,
196 x_document_number OUT NOCOPY NUMBER,
197 x_document_url OUT NOCOPY VARCHAR2,
198 x_request_id OUT NOCOPY NUMBER,
199 x_result OUT NOCOPY VARCHAR2,
200 x_error_code OUT NOCOPY VARCHAR2,
201 x_error_message OUT NOCOPY VARCHAR2)
202 IS
203
204 v_debug_status VARCHAR2(100);
205 v_error_code VARCHAR2(100);
206 v_error_message VARCHAR2(400);
207 v_functional_currency_code pon_auction_headers_all.currency_code%TYPE;
208 v_currency_precision pon_auction_headers_all.number_price_decimals%TYPE;
209 v_doctype_id pon_auc_doctypes.doctype_id%TYPE;
210 v_transaction_type pon_auc_doctypes.transaction_type%TYPE;
211 v_site_id pon_auction_headers_all.trading_partner_id%TYPE;
212 v_site_name pon_auction_headers_all.trading_partner_name%TYPE;
213 v_trading_partner_id pon_bidding_parties.trading_partner_id%TYPE;
214 v_trading_partner_name pon_bidding_parties.trading_partner_name%TYPE;
215 v_trading_partner_contact_id pon_bidding_parties.trading_partner_contact_id%TYPE;
216 v_trading_partner_contact_name pon_bidding_parties.trading_partner_contact_name%TYPE;
217 v_blanket_bidders_curr VARCHAR2(1) := 'N';
218 v_set_as_bidders_curr VARCHAR2(1) := 'N';
219 v_order_type_lookup_code po_line_types_b.order_type_lookup_code%TYPE;
220
221 -- The v_bidders_currency_rate multiplier is initially defaulted to 1.
222 -- If the auction is in functional currency multiplying with this will
223 -- have no effect.
224
225 v_bidders_currency_rate NUMBER := 1;
226 v_att_category_id fnd_document_categories.category_id%TYPE;
227 v_seq_num fnd_attached_documents.seq_num%TYPE;
228 v_uom_code pon_auction_item_prices_all.uom_code%TYPE;
229 v_amount_based_lines NUMBER;
230
231 v_contracts_doctype VARCHAR2(60);
232 v_return_status VARCHAR2(1);
233 v_msg_data VARCHAR2(400);
234 v_msg_count NUMBER;
235 v_auc_contact_id pon_auction_headers_all.trading_partner_contact_id%TYPE;
236 v_supplier_site_id pon_bidding_parties.vendor_site_id%TYPE;
237 v_supplier_site_code pon_bidding_parties.vendor_site_code%TYPE;
238
239 v_price_break_response pon_auction_headers_all.price_break_response%type;
240 v_price_break_type pon_auction_item_prices_all.price_break_type%type;
241 v_price_break_neg_flag pon_auction_item_prices_all.price_break_neg_flag%type;
242 v_price_tiers_indicator pon_auction_headers_all.price_tiers_indicator%type;
243
244 l_is_super_large_neg VARCHAR2(1) := 'N';
245 l_number_of_lines NUMBER := -1;
246 l_request_id NUMBER := -1;
247 l_max_line_number NUMBER;
248 l_batch_start NUMBER;
249 l_batch_end NUMBER;
250 l_batch_size NUMBER;
251 l_last_line_number NUMBER;
252
253 -- multi org changes
254 l_old_org_id NUMBER;
255 l_old_policy VARCHAR2(2);
256
257 l_style_name po_doc_style_headers.style_name%TYPE;
258 l_style_description po_doc_style_headers.style_description%TYPE;
259 l_style_type po_doc_style_headers.style_type%TYPE;
260 l_status po_doc_style_headers.status%TYPE;
261 l_advances_flag po_doc_style_headers.advances_flag%TYPE;
262 l_retainage_flag po_doc_style_headers.retainage_flag%TYPE;
263 l_price_breaks_flag po_doc_style_headers.price_breaks_flag%TYPE;
264 l_price_differentials_flag po_doc_style_headers.price_differentials_flag%TYPE;
265 l_progress_payment_flag po_doc_style_headers.progress_payment_flag%TYPE;
266 l_contract_financing_flag po_doc_style_headers.contract_financing_flag%TYPE;
267 l_line_type_allowed po_doc_style_headers.line_type_allowed%TYPE;
268
269 --<Sol Project>
270 l_uda_template_id NUMBER;
271 l_src_template_id NUMBER;
272 l_uda_template_date DATE;
273 l_sol_type VARCHAR2(3);
274 l_line_uda_temp_id NUMBER;
275 l_src_pkey PO_TBL_NUMBER;
276 l_tar_pkey PO_TBL_NUMBER;
277 l_lineno_list PO_TBL_NUMBER;
278 l_poline_id NUMBER;
279 l_shp_template_id NUMBER;
280 l_lineloc_list PO_TBL_NUMBER;
281 clm_doc_number VARCHAR2(100);
282 l_return_status VARCHAR2(1);
283 --<Sol Project End>
284
285 --Bug : 16690156
286 l_standard_form pon_auction_headers_all.standard_form%TYPE;
287 l_document_format pon_auction_headers_all.document_format%TYPE;
288
289
290 BEGIN
291
292 -- Set a savepoint, so that we can always rollback to it.
293 -- Whatever happens we donot wan't to put corrupt data in
294 -- the transaction tables. This is our way of assuring that
295 -- this does not happen.
296 SAVEPOINT pon_before_insert;
297
298 --
299 --If it is a concurrent call set the return values
300 --
301 IF (p_is_concurrent_call = 'Y') THEN
302
303 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
304 fnd_log.string(fnd_log.level_statement,
305 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
306 'This is a concurrent call');
307 end if;
308
309 x_document_number := p_document_number;
310 x_document_url := null;
311 x_request_id := -1;
312 END IF;
313
314
315 -- Read data into record for convenience
316 --Call INITIALISE _GLOBALS here
317 INITIALISE_GLOBALS(p_interface_id => p_interface_id);
318
319 -- Get site ID for the enterprise
320 v_debug_status := 'SITE_ID';
321 pos_enterprise_util_pkg.get_enterprise_partyId(v_site_id,
322 v_error_code,
323 v_error_message);
324 IF (v_error_code IS NOT NULL OR v_site_id IS NULL) THEN
325 X_RESULT := 'FAILURE';
326 x_error_code := 'CREATE_DRAFT:GET_ENTERPRISE_ID';
327 x_error_message := 'Could not get the Enterprise ID. Error returned by get_enterprise_partyId method is - ' || Substr(v_error_message,1,150) ;
328
329 -- Update the process_status column in header table
330 UPDATE pon_auc_headers_interface
331 SET process_status = 'REJECTED'
332 WHERE interface_auction_header_id = p_interface_id;
333
334 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
335 fnd_log.string(fnd_log.level_statement,
336 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
337 x_error_message);
338 end if;
339
340 RETURN;
341 END IF;
342
343 -- Get site name for the enterprise
344 v_debug_status := 'SITE_NAME';
345 pos_enterprise_util_pkg.get_enterprise_party_name(v_site_name,
346 v_error_code,
347 v_error_message);
348 IF (v_error_code IS NOT NULL) THEN
349 X_RESULT := 'FAILURE';
350 x_error_code := 'CREATE_DRAFT:GET_ENTERPRISE_NAME';
351 x_error_message := 'Could not get the Enterprise Name. Error returned by get_enterprise_partyId method is - ' || Substr(v_error_message,1,150) ;
352
353 -- Update the process_status column in header table
354 UPDATE pon_auc_headers_interface
355 SET process_status = 'REJECTED'
356 WHERE interface_auction_header_id = p_interface_id;
357
358 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
359 fnd_log.string(fnd_log.level_statement,
360 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
361 x_error_message);
362 end if;
363
364 RETURN;
365 END IF;
366
367 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
368 fnd_log.string(fnd_log.level_statement,
369 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
370 'Counting the number of lines for p_interface_id : ' || p_interface_id);
371 end if;
372
373 SELECT count(p_interface_id)
374 INTO l_number_of_lines
375 FROM pon_auc_items_interface
376 WHERE interface_auction_header_id = p_interface_id;
377
378 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
379 fnd_log.string(fnd_log.level_statement,
380 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
381 'l_number_of_lines (number of lines to copy into PON tables, which may not be the same as the value in number_of_lines field in PON_AUCTION_HEADERS_ALL for this auction) : '||
382 l_number_of_lines ||';Cheking if l_number_of_lines > Threshold for p_party_id: '|| v_site_id);
383 end if;
384
385 PON_PROFILE_UTIL_PKG.LINES_MORE_THAN_THRESHOLD(
386 p_number_of_lines => l_number_of_lines,
387 p_party_id => v_site_id,
388 x_is_super_large_neg => l_is_super_large_neg);
389
390 --This is for testing
391 --Assign l_is_super_large_neg the value 'Y'
392 --l_is_super_large_neg := 'Y';
393
394 -- Get functional currency for this organization
395 v_debug_status := 'CURRENCY_CODE';
396 SELECT sob.currency_code, fc.precision INTO v_functional_currency_code, v_currency_precision
397 FROM gl_sets_of_books sob, financials_system_params_all fsp,fnd_currencies fc
398 WHERE nvl(fsp.org_id,-9999) = nvl(g_header_rec.org_id,-9999)
399 AND sob.set_of_books_id = fsp.set_of_books_id
400 AND sob.currency_code = fc.currency_code;
401
402
403 IF (v_functional_currency_code <> g_header_rec.currency_code) THEN
404 --Since functional currency code is different than the
405 -- transactional code the blanket is in bidders currency
406 v_blanket_bidders_curr := 'Y';
407 v_bidders_currency_rate := g_header_rec.rate;
408 END IF;
409
410 -- Check to see if we are allowing other bid currencies. In which
411 -- case the bidders currency gets set as a allowable foreigh currency for bidding.
412 IF (g_header_rec.allow_other_bid_currency_flag = 'Y') THEN
413 -- if there are amount based lines, don't allow other currencies.
414 SELECT count(*) INTO v_amount_based_lines
415 FROM pon_auc_items_interface paii, po_line_types_b polt
416 WHERE paii.interface_auction_header_id = p_interface_id
417 AND paii.line_type_id = polt.line_type_id
418 AND polt.order_type_lookup_code = 'AMOUNT';
419
420 IF (v_amount_based_lines = 0) THEN
421 v_set_as_bidders_curr := 'Y';
422 END IF;
423 END IF ;
424
425
426 -- Get doctypeID
427 v_debug_status := 'DOCTYPE_ID';
428 SELECT doctype_id, transaction_type
429 INTO v_doctype_id, v_transaction_type
430 FROM pon_auc_doctypes
431 WHERE internal_name = g_header_rec.neg_type;
432
433 -- price break header setting
434 PON_AUCTION_PKG.get_default_hdr_pb_settings (
435 v_doctype_id,
436 v_site_id,
437 v_price_break_response);
438
439
440 --ONLINE FROM HERE-------------------------------------------------------------------------------------
441 --
442 -- Handle Header data if it is an online call
443 --
444 IF (p_is_concurrent_call = 'N') THEN
445
446 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
447 fnd_log.string(fnd_log.level_statement,
448 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
449 'This is not a concurrent call; Handling Header information now; Validating the header');
450 end if;
451
452 -- Validate data in the PON_AUC_HEADERS_INTERFACE table
453 v_debug_status := 'VALIDATE_HEADER';
454 val_auc_headers_interface(p_interface_id,v_error_code, v_error_message);
455
456 -- Error encountered while validating auction header interface table data
457 IF (v_error_code IS NOT NULL ) THEN
458 x_result := 'FAILURE';
459 x_error_code := v_error_code;
460 x_error_message := v_error_message;
461
462 -- Update the process_status column in header table
463 UPDATE pon_auc_headers_interface
464 SET process_status = 'REJECTED'
465 WHERE interface_auction_header_id = p_interface_id;
466
467 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
468 fnd_log.string(fnd_log.level_statement,
469 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
470 'Error in validating the header; error mesg : ' ||x_error_message);
471 end if;
472
473 RETURN;
474 END IF;
475
476
477 --Insert a row in the transaction table
478 v_debug_status := 'INSERT_PON_AUC_HEADERS';
479
480
481 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
482 fnd_log.string(fnd_log.level_statement,
483 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
484 'preparing to insert a row in the transaction table');
485 end if;
486
487 -- Get the document_number from sequence and store it so that it can
488 -- be returned
489
490 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
491 fnd_log.string(fnd_log.level_statement,
492 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
493 'Getting the document_number from sequence');
494 end if;
495
496 SELECT pon_auction_headers_all_s.NEXTVAL INTO x_document_number
497 FROM dual;
498
499 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
500 fnd_log.string(fnd_log.level_statement,
501 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
502 'New document_number is (x_document_number) : ' ||x_document_number);
503 end if;
504
505 --Also get the document url and store it
506 x_document_url := '&' || 'auctionID=' || x_document_number || '&' || 'from=RENEGOTIATE_BLANKET';
507
508 -- copy any contracts from Blanket onto the new Document
509 if (pon_conterms_utl_pvt.is_contracts_installed() = 'T') then
510 begin
511 select fnd_user.employee_id
512 into v_auc_contact_id
513 from
514 fnd_user,
515 hz_relationships
516 where
517 fnd_user.user_id = fnd_global.user_id()
518 and hz_relationships.object_id = v_site_id
519 and hz_relationships.subject_id = fnd_user.person_party_id
520 and hz_relationships.relationship_type = 'POS_EMPLOYMENT'
521 and hz_relationships.relationship_code = 'EMPLOYEE_OF'
522 and hz_relationships.start_date <= SYSDATE
523 and hz_relationships.end_date >= SYSDATE
524 and nvl(fnd_user.end_date,sysdate) >= sysdate;
525 exception
526 when others then
527 v_auc_contact_id := null;
528 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
529 fnd_log.string(fnd_log.level_statement,
530 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface',
531 'Could not determine contact_id for fnd_user_id ' || fnd_global.user_id());
532 end if;
533 end;
534
535 v_contracts_doctype := pon_conterms_utl_pvt.get_negotiation_doc_type(v_doctype_id);
536
537 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
538 fnd_log.string(fnd_log.level_statement,
539 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
540 'Calling okc_terms_copy_grp.copy_doc with parameters -- '||
541 'v_contracts_doctype : ' || v_contracts_doctype ||
542 'x_document_number : ' || x_document_number ||
543 'v_auc_contact_id : '|| v_auc_contact_id
544 );
545 end if;
546
547 --
548 -- Get the current policy
549 --
550 l_old_policy := mo_global.get_access_mode();
551 l_old_org_id := mo_global.get_current_org_id();
552 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
553 fnd_log.string(fnd_log.level_statement,
554 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
555 'Getting current policy -- '||
556 'old policy : ' || l_old_policy ||
557 'old org_id : ' || l_old_org_id
558 );
559 end if;
560
561 --
562 -- Set the connection policy context. Bug 5040821.
563 --
564 mo_global.set_policy_context('S', g_header_rec.org_id);
565
566
567 okc_terms_copy_grp.copy_doc(
568 P_API_VERSION => 1.0, -- p_api_version
569 P_INIT_MSG_LIST => fnd_api.g_false, -- p_init_msg_list
570 P_COMMIT => fnd_api.g_false, -- p_commit
571 P_SOURCE_DOC_TYPE => 'PA_'||g_header_rec.origination_code, -- (origination_code is CONTRACT or BLANKET)
572 P_SOURCE_DOC_ID => g_header_rec.source_doc_id, -- p_source_doc_id
573 P_TARGET_DOC_TYPE => v_contracts_doctype, -- p_target_doc_type
574 P_TARGET_DOC_ID => x_document_number, -- p_target_doc_id
575 P_KEEP_VERSION => 'N', -- p_keep_version (N = copy latest version)
576 P_ARTICLE_EFFECTIVE_DATE => sysdate, -- p_article_effective_date
577 P_INITIALIZE_STATUS_YN => 'Y', -- p_initialize_status_yn
578 P_RESET_FIXED_DATE_YN => 'Y', -- p_reset_fixed_date_yn
579 P_INTERNAL_PARTY_ID => g_header_rec.org_id, -- p_internal_party_id
580 P_INTERNAL_CONTACT_ID => v_auc_contact_id, -- p_internal_contact_id
581 P_TARGET_CONTRACTUAL_DOCTYPE => 'PA_'||g_header_rec.origination_code, -- (origination_code is CONTRACT or BLANKET)
582 P_COPY_DEL_ATTACHMENTS_YN =>'Y', -- p_copy_del_attachments_yn
583 P_EXTERNAL_PARTY_ID => null, -- p_external_party_id
584 P_EXTERNAL_CONTACT_ID => null, -- p_external_contact_id
585 P_COPY_DELIVERABLES => 'Y', -- p_copy_deliverables
586 P_DOCUMENT_NUMBER => x_document_number, -- p_document_number
587 P_COPY_FOR_AMENDMENT => 'N', -- p_copy_for_amendment
588 P_COPY_DOC_ATTACHMENTS => 'N', -- p_copy_doc_attachments
589 P_ALLOW_DUPLICATE_TERMS => 'Y', -- p_allow_duplicate_terms
590 P_COPY_ATTACHMENTS_BY_REF => 'N', -- p_copy_attachments_by_ref
591 X_RETURN_STATUS => v_return_status, -- x_return_status (S, E, U)
592 X_MSG_DATA => v_msg_data, -- x_msg_data
593 X_MSG_COUNT => v_msg_count, -- x_msg_count
594 P_EXTERNAL_PARTY_SITE_ID => null -- p_external_party_site_id
595 );
596
597 --
598 -- Set the org context back
599 --
600 mo_global.set_policy_context(l_old_policy, l_old_org_id);
601
602
603 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
604 fnd_log.string(fnd_log.level_statement,
605 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
606 'Executed copy_doc() ; returned with status : ' ||v_return_status);
607 end if;
608
609 if (v_return_status <> fnd_api.g_ret_sts_success) then
610 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
611 fnd_log.string(fnd_log.level_statement,
612 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
613 'Call to copy_doc() failed for source_id=' || g_header_rec.source_doc_id || ' target_id=' || x_document_number);
614 end if;
615
616 x_result := 'FAILURE';
617 x_error_code := v_msg_data;
618 x_error_message := v_msg_data;
619 return ;
620 end if;
621 end if;
622
623
624 --
625 -- R12.1 Price Tiers Project
626 -- We need to check if PO style allows price breaks or not
627 --
628 IF ( g_header_rec.po_style_id IS NOT NULL) THEN
629 PO_DOC_STYLE_GRP.GET_DOCUMENT_STYLE_SETTINGS(
630 p_api_version => 1.0
631 , p_style_id => g_header_rec.po_style_id
632 , x_style_name => l_style_name
633 , x_style_description => l_style_description
634 , x_style_type => l_style_type
635 , x_status => l_status
636 , x_advances_flag => l_advances_flag
637 , x_retainage_flag => l_retainage_flag
638 , x_price_breaks_flag => l_price_breaks_flag
639 , x_price_differentials_flag => l_price_differentials_flag
640 , x_progress_payment_flag => l_progress_payment_flag
641 , x_contract_financing_flag => l_contract_financing_flag
642 , x_line_type_allowed => l_line_type_allowed);
643 END IF;
644
645 -- R12.1 Price tiers Project
646 -- Get Default price tiers indicator
647 -- As Reneg blanket does not allow styles to be set while creation
648 -- user can cng the style once a draft neg has been created.
649 -- Default style allows quantity based price tiers passing Y as p_qty_price_tiers_enabled.
650
651 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN --{
652 fnd_log.string(fnd_log.level_statement,
653 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
654 'Calling the PON_AUCTION_PKG.GET_DEFAULT_TIERS_INDICATOR API to get the' ||
655 ' default price tiers indicator value.');
656 END IF;
657
658
659 PON_AUCTION_PKG.GET_DEFAULT_TIERS_INDICATOR(
660 p_contract_type => g_header_rec.contract_type,
661 p_price_breaks_enabled => l_price_breaks_flag,
662 p_qty_price_tiers_enabled => 'Y',
663 p_doctype_id => v_doctype_id,
664 x_price_tiers_indicator => v_price_tiers_indicator);
665
666 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
667 fnd_log.string(fnd_log.level_statement,
668 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
669 'Inserting row into PON_AUCTION_HEADERS_ALL with the following filed values -- ' ||
670 'auction_header_id (x_document_number) : '|| x_document_number ||
671 '; document_number (x_document_number) : ' || x_document_number ||
672 '; amendment_number : ' || 0 ||
673 '; auction_status : ' || 'DRAFT' ||
674 '; award_status : ' || 'NO' ||
675 '; auction_type (v_transaction_type) : '|| v_transaction_type ||
676 '; contract_type (g_header_rec.contract_type) : '|| g_header_rec.contract_type ||
677 '; trading_partner_name (v_site_name) : ' || v_site_name ||
678 '; trading_partner_name_upper (Upper(v_site_name)) : ' || Upper(v_site_name) ||
679 '; trading_partner_id (v_site_id) : '|| v_site_id ||
680 '; language_code (g_header_rec.language_code) : ' || g_header_rec.language_code ||
681 '; bid_visibility_code : ' || 'OPEN_BIDDING' ||
682 '; attachment_flag : ' || 'N' ||
683 '; ship_to_location_id (g_header_rec.ship_to_location_id) : ' || g_header_rec.ship_to_location_id ||
684 '; bill_to_location_id (g_header_rec.bill_to_location_id) : ' || g_header_rec.bill_to_location_id ||
685 '; payment_terms_id (g_header_rec.payment_terms_id) : ' || g_header_rec.payment_terms_id ||
686 '; freight_terms_code (g_header_rec.freight_terms_code) : ' || g_header_rec.freight_terms_code ||
687 '; fob_code (g_header_rec.fob_code) : '|| g_header_rec.fob_code ||
688 '; carrier_code (g_header_rec.carrier_code) : '|| g_header_rec.carrier_code ||
689 '; note_to_bidders (g_header_rec.note_to_bidders) : ' || g_header_rec.note_to_bidders ||
690 '; po_agreed_amount (round(g_header_rec.po_agreed_amount * v_bidders_currency_rate,v_currency_precision)) : ' || round(g_header_rec.po_agreed_amount * v_bidders_currency_rate,v_currency_precision) ||
691 '; currency precision : ' || 10000 ||
692 '; global_agreement_flag ( Nvl(g_header_rec.global_agreement_flag,''N'') ) : ' || Nvl(g_header_rec.global_agreement_flag,'N') ||
693 '; creation_date : ' || sysdate ||
694 '; created_by (g_header_rec.user_id) : ' || g_header_rec.user_id ||
695 '; last_update_date : ' || sysdate ||
696 '; last_updated_by : ' || g_header_rec.user_id ||
697 '; auction_origination_code (g_header_rec.origination_code) : '|| g_header_rec.origination_code ||
698 '; doctype_id (v_doctype_id) : ' || v_doctype_id ||
699 '; org_id (g_header_rec.org_id) : ' || g_header_rec.org_id ||
700 '; buyer_id (g_header_rec.user_id) : '|| g_header_rec.user_id ||
701 '; manual_edit_flag : ' || 'N' ||
702 '; Source document number (g_header_rec.source_doc_number) : ' || g_header_rec.source_doc_number ||
703 '; Source doc id (g_header_rec.source_doc_id) : ' || g_header_rec.source_doc_id ||
704 '; Source doc number message to be displayed (g_header_rec.source_doc_msg) : ' || g_header_rec.source_doc_msg ||
705 '; Source doc line level message to be displayed (g_header_rec.source_doc_line_msg) : '|| g_header_rec.source_doc_line_msg ||
706 '; 3 character message app name (g_header_rec.source_doc_msg_app) : ' || g_header_rec.source_doc_msg_app ||
707 '; Security level code : ' || 'PUBLIC' ||
708 '; Share Award Decision : '|| 'N' ||
709 '; Approval Status : ' || 'NOT_REQUIRED' ||
710 '; po style id (g_header_rec.po_style_id) : ' || g_header_rec.po_style_id ||
711 '; price_break_response (v_price_break_response) : '|| v_price_break_response ||
712 '; Attribute Line Number : ' || -1 ||
713 '; Flag to indicate if Header Attributes are present : ' || 'N' ||
714 '; complete_flag : ' || 'N' ||
715 '; v_price_tiers_indicator : ' || v_price_tiers_indicator
716 );
717 end if;
718
719 --<Sol Project>
720 --Get uda template id and update it
721 v_debug_status := 'UDA_TEMPLATE_ID';
722 l_uda_template_id := NULL;
723 l_uda_template_date := NULL;
724 l_sol_type := NULL;
725 l_line_uda_temp_id := NULL;
726
727 IF PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(p_doc_type_id => v_doctype_id) = 1 THEN
728 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
729 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
730 'Retrieve Uda Template Id for Header');
731 end if;
732
733 l_uda_template_id := po_uda_data_util.get_template_id (
734 'SOURCING',
735 'SOLICITATION',
736 null,
737 'HEADER',
738 sysdate,
739 v_return_status,
740 v_error_message);
741 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
742 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
743 'Return from po_uda_data_util.get_template_id '||v_return_status);
744 end if;
745
746 IF ( v_return_status = 'E' ) THEN
747 X_RESULT := 'FAILURE';
748 x_error_code := 'CREATE_DRAFT:GET_UDA_TEMPLATE_ID';
749 x_error_message := 'Could not get UDA_TEMPLATE_ID.' || Substr(v_error_message,1,200) ;
750
751 -- Update the process_status column in header table
752 UPDATE pon_auc_headers_interface
753 SET process_status = 'REJECTED'
754 WHERE interface_auction_header_id = p_interface_id;
755
756 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
757 fnd_log.string(fnd_log.level_statement,
758 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
759 x_error_message);
760 end if;
761
762 RETURN;
763 END IF;
764 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
765 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.Create_Draft_Neg_interface_pvt',
766 'Got uda_template_id for Header'||l_uda_template_id);
767 end if;
768
769 l_uda_template_date := sysdate;
770 l_sol_type := 'RFQ';
771
772 --get uda line template id
773 l_line_uda_temp_id := po_uda_data_util.get_template_id (
774 'SOURCING',
775 'SOLICITATION',
776 null,
777 'LINE',
778 sysdate,
779 v_return_status,
780 v_error_message);
781
782
783 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
784 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
785 'Return from Line po_uda_data_util.get_template_id '||v_return_status);
786 end if;
787
788 IF ( v_return_status = 'E' ) THEN
789 X_RESULT := 'FAILURE';
790 x_error_code := 'CREATE_DRAFT:GET_UDA_TEMPLATE_ID';
791 x_error_message := 'Could not get UDA_TEMPLATE_ID.' || Substr(v_error_message,1,200) ;
792
793 -- Update the process_status column in header table
794 UPDATE pon_auc_headers_interface
795 SET process_status = 'REJECTED'
796 WHERE interface_auction_header_id = p_interface_id;
797
798 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
799 fnd_log.string(fnd_log.level_statement,
800 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
801 x_error_message);
802 end if;
803
804 RETURN;
805 END IF;
806 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
807 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.Create_Draft_Neg_interface_pvt',
808 'Got uda_template_id for Line '||l_line_uda_temp_id);
809 end if;
810
811 /* Bug : 16690156 : Get STANDARD_FORM, DOCUMENT_FORMAT default values */
812 BEGIN
813 SELECT STANDARD_FORM, DOCUMENT_FORMAT
814 INTO l_standard_form, l_document_format
815 FROM po_print_form_formats
816 WHERE document_type = 'PO_SOL_STD_FORM' AND default_flag = 'Y' ;
817
818 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
819 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.Create_Draft_Neg_interface_pvt',
820 'Got default standard_form, document_format : l_standard_form '||l_standard_form||' l_document_format '||l_document_format);
821 end if;
822
823 EXCEPTION
824 WHEN No_Data_Found THEN
825 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
826 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.Create_Draft_Neg_interface_pvt',
827 'Default values donot exist for standard form and document format');
828 end if;
829 l_standard_form := NULL;
830 l_document_format := NULL;
831 END;
832 --End Bug : 16690156
833
834 END IF;
835 --<Sol Project End>
836
837
838 INSERT INTO pon_auction_headers_all
839 (auction_header_id,
840 document_number,
841 auction_header_id_orig_amend,
842 auction_header_id_orig_round,
843 amendment_number,
844 auction_status,
845 award_status,
846 auction_type,
847 contract_type,
848 trading_partner_name,
849 trading_partner_name_upper,
850 trading_partner_id,
851 language_code,
852 bid_visibility_code,
853 attachment_flag,
854 ship_to_location_id,
855 bill_to_location_id,
856 payment_terms_id,
857 freight_terms_code,
858 fob_code,
859 carrier_code,
860 note_to_bidders,
861 allow_other_bid_currency_flag,
862 rate_type,
863 po_agreed_amount,
864 po_min_rel_amount,
865 currency_code,
866 number_price_decimals,
867 global_agreement_flag,
868 creation_date,
869 created_by,
870 last_update_date,
871 last_updated_by,
872 auction_origination_code,
873 doctype_id,
874 org_id,
875 buyer_id,
876 manual_edit_flag,
877 source_doc_number,
878 source_doc_id,
879 source_doc_msg,
880 source_doc_line_msg,
881 source_doc_msg_app,
882 security_level_code,
883 share_award_decision,
884 approval_status,
885 po_style_id,
886 price_break_response,
887 attribute_line_number,
888 has_hdr_attr_flag,
889 has_items_flag,
890 complete_flag,
891 progress_payment_type,
892 price_tiers_indicator,
893 UDA_TEMPLATE_ID, --<Sol Project> uda_template_id
894 UDA_TEMPLATE_DATE,
895 SOLICITATION_TYPE,
896 REVISION,
897 standard_form, --Bug : 16690156
898 document_format --Bug : 16690156
899 )
900 VALUES
901 (x_document_number, -- auction_header_id
902 x_document_number, -- document_number
903 x_document_number, -- auction_header_id_orig_amend,
904 x_document_number, -- auction_header_id_orig_round,
905 0, -- amendment_number
906 'DRAFT', -- auction_status
907 'NO', -- award_status
908 v_transaction_type, -- auction_type
909 g_header_rec.contract_type, -- contract_type
910 v_site_name, -- trading_partner_name
911 Upper(v_site_name), -- trading_partner_name_upper
912 v_site_id, -- trading_partner_id
913 g_header_rec.language_code, -- language_code
914 'OPEN_BIDDING', -- bid_visibility_code
915 'N', -- attachment_flag
916 g_header_rec.ship_to_location_id, -- ship_to_location_id
917 g_header_rec.bill_to_location_id, -- bill_to_location_id
918 g_header_rec.payment_terms_id, -- payment_terms_id
919 g_header_rec.freight_terms_code, -- freight_terms_code
920 g_header_rec.fob_code, -- fob_code
921 g_header_rec.carrier_code, -- carrier_code
922 g_header_rec.note_to_bidders, -- note_to_bidders
923 Decode(v_set_as_bidders_curr, 'Y', Decode(v_blanket_bidders_curr,'Y','Y','N'), 'N'), -- allow_other_bid_currency_flag
924 Decode(v_blanket_bidders_curr,'Y',g_header_rec.rate_type, null), -- rate_type
925 round(g_header_rec.po_agreed_amount * v_bidders_currency_rate,v_currency_precision), -- po_agreed_amount
926 decode(g_header_rec.global_agreement_flag, 'Y', null, Round(g_header_rec.po_min_rel_amount * v_bidders_currency_rate,v_currency_precision)), -- po_min_release_amount
927 Decode (v_blanket_bidders_curr, 'Y',v_functional_currency_code,g_header_rec.currency_code), -- currency_code
928 10000, -- currency precision set to ANY
929 Nvl(g_header_rec.global_agreement_flag,'N'), -- global_agreement_flag
930 Sysdate, -- creation_date
931 g_header_rec.user_id, -- created_by
932 Sysdate, -- last_update_date
933 g_header_rec.user_id, -- last_updated_date
934 g_header_rec.origination_code, -- auction_origination_code
935 v_doctype_id, -- doctype_id
936 g_header_rec.org_id, -- org_id
937 g_header_rec.user_id, -- buyer_id
938 'N', -- manual_edit_flag
939 g_header_rec.source_doc_number, -- Source document number
940 g_header_rec.source_doc_id, -- Source doc id
941 g_header_rec.source_doc_msg, -- Source doc number message to be displayed
942 g_header_rec.source_doc_line_msg, -- Source doc line level message to be displayed
943 g_header_rec.source_doc_msg_app, -- 3 character message app name
944 'PUBLIC', -- Security level code
945 'N', -- Share Award Decision
946 'NOT_REQUIRED', -- Approval Status
947 g_header_rec.po_style_id, -- po style id
948 v_price_break_response, -- price_break_response,
949 -1, -- Attribute Line Number
950 'N', -- Flag to indicate if Header Attributes are present
951 decode(g_header_rec.contract_type,'CONTRACT','N','Y'), -- Has Items Flag
952 'N', --complete_flag
953 'NONE', --Progress_Payment_Type
954 v_price_tiers_indicator, --price_tiers_indicator
955 l_uda_template_id, --<Sol Project> uda_template_id
956 l_uda_template_date,
957 l_sol_type,
958 0,
959 l_standard_form, --Bug : 16690156
960 l_document_format --Bug : 16690156
961 );
962
963 --<Sol Project>
964 --Copy Header Udas
965
966 IF PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(p_doc_type_id => v_doctype_id) = 1 THEN
967 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
968 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
969 'Copy Header UDA from PO to Solicitation');
970 end if;
971 -- bug#9645160
972 PO_DOC_NUMBERING_PKG.DEFAULT_DOC_NUMBER_UDA
973 (p_doc_header_id => x_document_number,
974 p_draft_id => -1,
975 p_template_id => l_uda_template_id,
976 p_context_usage => 'Base Document',
977 p_source_org_owned => 'NA',
978 p_caller => 'SOL',
979 x_doc_number => clm_doc_number,
980 x_return_status => l_return_status);
981
982 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
983 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_auction_interface_pkg.Create_Draft_Negotiation',
984 'Defaulted Document Number '||clm_doc_number||' status '||l_return_status);
985 END IF;
986
987 IF ( l_return_status = 'E' ) THEN
988 X_ERROR_CODE := 'CREATE_DRAFT:DEFAULT_DOC_NUMBER_UDA';
989 X_RESULT := l_return_status;
990 X_ERROR_MESSAGE := 'Error in PO_DOC_NUMBERING_PKG.DEFAULT_DOC_NUMBER_UDA';
991 RETURN;
992 END IF;
993
994 /* Bug 9645160 - updated clm_document_number back in PON_AUCTION_HEADERS_ALL table. */
995
996 UPDATE pon_auction_headers_all
997 SET DOCUMENT_NUMBER = clm_doc_number
998 WHERE AUCTION_HEADER_ID = x_document_number;
999
1000 select uda_template_id into l_src_template_id from po_headers_all where po_header_id = g_header_rec.source_doc_id;
1001 l_src_pkey := GET_PKEY(g_header_rec.source_doc_id,-1);
1002 l_tar_pkey := GET_PKEY(x_document_number);
1003
1004 pon_copy_udas_grp.copy_uda_data(
1005 l_src_pkey,
1006 l_tar_pkey,
1007 l_src_template_id,
1008 l_uda_template_id,
1009 'HEADER',
1010 'HEADER',
1011 'PO',
1012 'SOL',
1013 v_return_status,
1014 v_msg_count,
1015 v_msg_data);
1016
1017 IF ( v_return_status = 'E' ) THEN
1018 X_RESULT := 'FAILURE';
1019 x_error_code := 'CREATE_DRAFT:COPY_HEADER_UDA';
1020 x_error_message := 'Could not copy Hdr Uda.' || Substr(v_msg_data,1,200) ;
1021
1022 -- Update the process_status column in header table
1023 UPDATE pon_auc_headers_interface
1024 SET process_status = 'REJECTED'
1025 WHERE interface_auction_header_id = p_interface_id;
1026
1027 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1028 fnd_log.string(fnd_log.level_statement,
1029 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1030 x_error_message);
1031 end if;
1032
1033 RETURN;
1034 END IF;
1035 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1036 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.Create_Draft_Neg_interface_pvt',
1037 'Copy Header Uda Status '||v_return_status);
1038 end if;
1039 end if;
1040 --<Sol Project>
1041
1042 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1043 fnd_log.string(fnd_log.level_statement,
1044 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1045 'Inserted recore into pon_auction_headers_all');
1046 end if;
1047
1048 END IF; --end of IF (p_is_concurrent_call = 'N')
1049 --ONLINE TILL HERE-----------------------------------------------------------------------------------------------------
1050
1051
1052 --
1053 --Handle children only if
1054 --(a) It is not a super large negotiation OR
1055 --(b) It is a super large negotiation and this procedure is called from a conc_program
1056 --
1057
1058 IF (l_is_super_large_neg = 'N') OR (l_is_super_large_neg = 'Y' AND p_is_concurrent_call = 'Y') THEN
1059 --{
1060 -- price break line setting, this should be called after header is inserted
1061 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1062 fnd_log.string(fnd_log.level_statement,
1063 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1064 'calling PON_AUCTION_PKG.get_default_pb_settings with x_document_number : ' || x_document_number);
1065 end if;
1066
1067 PON_AUCTION_PKG.get_default_pb_settings (x_document_number,
1068 v_price_break_type,
1069 v_price_break_neg_flag);
1070
1071 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1072 fnd_log.string(fnd_log.level_statement,
1073 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1074 'v_price_break_type : ' || v_price_break_type || '; v_price_break_neg_flag : ' || v_price_break_neg_flag);
1075 end if;
1076
1077
1078 IF (g_header_rec.origination_code <> 'CONTRACT') THEN
1079 -- Validate data in the PON_AUCTION_ITEM_PRICES_INTERFACE table
1080 v_debug_status := 'VALIDATE_ITEM' ;
1081
1082 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1083 fnd_log.string(fnd_log.level_statement,
1084 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1085 'validating PON_AUCTION_ITEM_PRICES_INTERFACE; calling val_auc_items_interface with p_interface_id : ' || p_interface_id);
1086 end if;
1087
1088 val_auc_items_interface(p_interface_id, v_error_code, v_error_message);
1089
1090 IF (v_error_code IS NOT NULL) THEN
1091 -- Error encountered while validating the line items
1092 x_result := 'FAILURE';
1093 x_error_code := v_error_code;
1094 x_error_message := v_error_message;
1095
1096
1097 -- Update the process_status column in header table
1098 UPDATE pon_auc_headers_interface
1099 SET process_status = 'REJECTED'
1100 WHERE interface_auction_header_id = p_interface_id;
1101
1102 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1103 fnd_log.string(fnd_log.level_statement,
1104 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1105 'error when validating PON_AUCTION_ITEM_PRICES_INTERFACE; x_error_message : ' || x_error_message);
1106 end if;
1107
1108 RETURN;
1109 END IF;-- error code not null
1110
1111 -- Validate data in the PON_AUCTION_SHIPMENTS_INTERFACE table
1112
1113 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1114 fnd_log.string(fnd_log.level_statement,
1115 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1116 'validating PON_AUCTION_SHIPMENTS_INTERFACE; calling val_auc_shipments_interface with p_interface_id : ' || p_interface_id);
1117 end if;
1118
1119 val_auc_shipments_interface(p_interface_id,v_error_code, v_error_message);
1120
1121 IF (v_error_code IS NOT NULL ) THEN
1122 -- Error encountered while validating the shipments
1123 x_result := 'FAILURE';
1124 x_error_code := v_error_code;
1125 x_error_message := v_error_message;
1126
1127 -- Update the process_status column in header table
1128 UPDATE pon_auc_headers_interface
1129 SET process_status = 'REJECTED'
1130 WHERE interface_auction_header_id = p_interface_id;
1131
1132 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1133 fnd_log.string(fnd_log.level_statement,
1134 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1135 'error when validating PON_AUCTION_SHIPMENTS_INTERFACE; x_error_message : ' || x_error_message);
1136 end if;
1137
1138 RETURN;
1139 END IF;-- error code not null
1140 END IF; -- end if (g_header_rec.origination_code <> 'CONTRACT')
1141
1142 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1143 fnd_log.string(fnd_log.level_statement,
1144 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1145 'validating PON_ATTACHMENTS_INTERFACE; calling val_attachments_interface with p_interface_id : ' || p_interface_id);
1146 end if;
1147
1148 -- Validate data in the PON_ATTACHMENTS_INTERFACE table
1149 val_attachments_interface(p_interface_id,v_error_code, v_error_message);
1150
1151 IF (v_error_code IS NOT NULL ) THEN
1152 -- Error encountered while validating the attachments
1153 x_result := 'FAILURE';
1154 x_error_code := v_error_code;
1155 x_error_message := v_error_message;
1156
1157 -- Update the process_status column in header table
1158 UPDATE pon_auc_headers_interface
1159 SET process_status = 'REJECTED'
1160 WHERE interface_auction_header_id = p_interface_id;
1161
1162 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1163 fnd_log.string(fnd_log.level_statement,
1164 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1165 'error when validating PON_ATTACHMENTS_INTERFACE; x_error_message : ' || x_error_message);
1166 end if;
1167
1168 RETURN;
1169 END IF;
1170
1171 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1172 fnd_log.string(fnd_log.level_statement,
1173 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1174 'getting category id for VENDOR attachments');
1175 end if;
1176
1177 -- get category id for VENDOR attachments
1178 v_debug_status := 'ATTACHMENT_CATEGORY_ID';
1179 BEGIN
1180 SELECT category_id
1181 INTO v_att_category_id
1182 FROM fnd_document_categories
1183 WHERE upper(name) = 'VENDOR';
1184 EXCEPTION
1185 WHEN no_data_found THEN
1186 X_RESULT := 'FAILURE';
1187 X_ERROR_CODE := 'CREATE_DRAFT:ATTACHMENT_CATEGORY_ID';
1188 X_ERROR_MESSAGE := 'The attachment category id for name=VENDOR could not be found';
1189 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1190 fnd_log.string(fnd_log.level_statement,
1191 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1192 'error when getting category id for VENDOR attachments; x_error_message : ' || X_ERROR_MESSAGE);
1193 end if;
1194
1195 RETURN;
1196 END;
1197
1198 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1199 fnd_log.string(fnd_log.level_statement,
1200 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1201 'getting UOM for amount based line types');
1202 end if;
1203
1204 -- Get UOM for amount based line types
1205 v_debug_status := 'UOM_SELECT';
1206 BEGIN
1207 SELECT preference_value
1208 INTO v_uom_code
1209 FROM pon_party_preferences
1210 WHERE preference_name = 'AMOUNT_BASED_UOM'
1211 AND app_short_name = 'PON'
1212 AND party_id = v_site_id;
1213 EXCEPTION
1214 WHEN others THEN
1215 -- Don't fail! Use 'Each' and let the user change it later
1216 v_debug_status := 'UOM_SELECT_EACH';
1217 SELECT uom_code
1218 INTO v_uom_code
1219 FROM mtl_units_of_measure
1220 WHERE unit_of_measure = 'Each';
1221 END;
1222
1223
1224 UPDATE pon_auction_headers_all
1225 SET price_tiers_indicator = 'PRICE_BREAKS'
1226 Where exists (SELECT 'Y'
1227 FROM pon_auc_shipments_interface
1228 WHERE interface_auction_header_id = p_interface_id
1229 AND rownum=1)
1230 AND auction_header_id = x_document_number;
1231
1232 ----------------------------------------------------------------------------------------------------
1233 --BATCHING STARTS HERE--
1234 ----------------------------------------------------------------------------------------------------
1235
1236 --get the number of rows to be copied
1237
1238 SELECT nvl(max(interface_line_number),0) INTO l_max_line_number FROM pon_auc_items_interface
1239 WHERE interface_auction_header_id = p_interface_id;
1240
1241 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1242 fnd_log.string(fnd_log.level_statement,
1243 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1244 'Max lines to copy (l_max_line_number) : ' || l_max_line_number);
1245 end if;
1246
1247
1248 IF (l_max_line_number) > 0 then
1249 -- Draft with no lines, or RFI,CPA with no lines we need to skip batching
1250 -- its build into the loop logic but just to be explicit about this condition
1251
1252 -- Get the batch size
1253 l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
1254
1255 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1256 fnd_log.string(fnd_log.level_statement,
1257 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1258 'Starting batching with batchsize (l_batch_size) : ' || l_batch_size);
1259 end if;
1260
1261 -- for testing purpose
1262 -- l_batch_size := 2;
1263
1264 -- Define the initial batch range (line numbers are indexed from 1)
1265 l_batch_start := 1;
1266
1267 IF (l_max_line_number <l_batch_size) THEN
1268 l_batch_end := l_max_line_number;
1269 ELSE
1270 l_batch_end := l_batch_size;
1271 END IF;
1272
1273 WHILE (l_batch_start <= l_max_line_number) LOOP
1274
1275 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1276 fnd_log.string(fnd_log.level_statement,
1277 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1278 'Batching the line_numbers in the range '|| l_batch_start ||' to '|| l_batch_end ||' (inclusive)');
1279 end if;
1280
1281
1282 IF (g_header_rec.origination_code <> 'CONTRACT') THEN
1283 -- Insert item level data from interface tables into transaction tables
1284 -- Before that prepare the interface table. This means updating some internal
1285 -- columns in the interface item level columns.
1286 v_debug_status := 'INSERT_PON_AUC_ITEMS';
1287
1288 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1289 fnd_log.string(fnd_log.level_statement,
1290 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1291 'Handling Item level data; updating the pon_auc_items_interface table with the correct order_type_lookup_code values');
1292 end if;
1293
1294
1295 -- First update the pon_auc_items_interface table with the correct order_type_lookup_code values.
1296
1297 UPDATE pon_auc_items_interface paii
1298 SET paii.order_type_lookup_code =
1299 (SELECT polt.order_type_lookup_code
1300 FROM po_line_types_b polt
1301 WHERE paii.line_type_id = polt.line_type_id)
1302 WHERE paii.interface_auction_header_id = p_interface_id
1303 AND interface_line_number >= l_batch_start
1304 AND interface_line_number <= l_batch_end;
1305
1306 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1307 fnd_log.string(fnd_log.level_statement,
1308 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1309 'inserting into pon_auction_item_prices_all for auction_header_id (x_document_number) : ' || x_document_number);
1310 end if;
1311
1312 -- Then insert into the items transaction table.
1313 -- Here we will initially set the has_shipments flag to 'N'
1314 -- and then do an update by checking if shipments are present
1315 INSERT INTO pon_auction_item_prices_all
1316 (auction_header_id,
1317 line_number,
1318 disp_line_number,
1319 last_amendment_update,
1320 modified_date,
1321 item_description,
1322 category_id,
1323 category_name,
1324 ip_category_id,
1325 uom_code,
1326 residual_quantity,
1327 number_of_bids,
1328 creation_date,
1329 created_by,
1330 last_update_date,
1331 last_updated_by,
1332 note_to_bidders,
1333 has_attributes_flag,
1334 org_id,
1335 line_type_id,
1336 order_type_lookup_code,
1337 item_id,
1338 item_number,
1339 item_revision,
1340 line_origination_code,
1341 source_doc_id,
1342 source_line_id,
1343 source_doc_number,
1344 source_line_number,
1345 current_price,
1346 quantity,
1347 po_min_rel_amount,
1348 price_break_type,
1349 price_break_neg_flag,
1350 has_shipments_flag,
1351 has_quantity_tiers,
1352 price_disabled_flag,
1353 quantity_disabled_flag,
1354 --ADDED FOR SERVICES PROCUREMENT PROJECT - additional 3 columns
1355 job_id,
1356 po_agreed_amount,
1357 purchase_basis,
1358 price_diff_shipment_number,
1359 group_type,
1360 document_disp_line_number,
1361 sub_line_sequence_number,
1362 -- Clin Slin Changes
1363 line_num_display,
1364 group_line_id,
1365 clm_info_flag,
1366 clm_option_indicator,
1367 clm_option_num,
1368 clm_option_from_date,
1369 clm_option_to_date,
1370 clm_funded_flag,
1371 clm_base_line_num,
1372 uda_template_id,
1373 CLM_CONTRACT_TYPE, -- bug 9914034
1374 CLM_COST_CONSTRAINT,
1375 CLM_IDC_TYPE
1376 )
1377 SELECT x_document_number, -- auction_header_id,
1378 interface_line_number, -- line_number
1379 interface_line_number, -- disp_line_number
1380 0, -- last_amendment_update
1381 sysdate, -- modified_date
1382 item_description, -- item_description
1383 pon_auc_items_interface.category_id, -- category_id
1384 FND_FLEX_EXT.get_segs('INV', 'MCAT', mtl_categories_kfv.STRUCTURE_ID, mtl_categories_kfv.CATEGORY_ID), -- category_name from mtl_categories_kfv table
1385 pon_auc_items_interface.ip_category_id, -- ip_category_id
1386 Decode (order_type_lookup_code,'AMOUNT',v_uom_code, uom_code), -- uom_code
1387 Decode (order_type_lookup_code,'AMOUNT',1, quantity), -- residual quantity
1388 0, -- number_of_bids
1389 Sysdate, -- creation_date
1390 g_header_rec.user_id, -- created_by
1391 Sysdate, -- last_update_date
1392 g_header_rec.user_id, -- last_updated_by
1393 note_to_bidders, -- note_to_bidders
1394 'N', -- has_attribute_flag
1395 org_id, -- org_id
1396 line_type_id, -- line_type_id
1397 order_type_lookup_code, -- order_type_lookup_code
1398 item_id, -- item_id
1399 item_number, -- item_number
1400 item_revision, -- item_revision
1401 origination_code, -- line_origination_code
1402 source_doc_id, -- source_doc_id
1403 source_line_id, -- source_line_id
1404 source_doc_number, -- source_doc_number
1405 source_line_number, -- source_line_number
1406 --Decode (order_type_lookup_code, 'AMOUNT',round(quantity * v_bidders_currency_rate,v_currency_precision),round(current_price * v_bidders_currency_rate, v_currency_precision)), -- current_price
1407 decode(current_price, 0, to_number(null), current_price * v_bidders_currency_rate), -- current_price
1408 Decode (order_type_lookup_code,'AMOUNT',1, quantity), -- quantity
1409 round(po_min_rel_amount * v_bidders_currency_rate,v_currency_precision), -- po_min_rel_amount
1410 Decode (order_type_lookup_code,'AMOUNT', 'NONE', 'FIXED PRICE', 'NONE', decode(price_break_type, null, 'NON-CUMULATIVE', 'NON CUMULATIVE', 'NON-CUMULATIVE', price_break_type)), -- price_break_type
1411 'Y', -- price_break_neg_flag. Those pbs are from po, so should always be optional
1412 'N', -- has_shipments_flag initially set to 'N'
1413 'N', -- has_quantity_tiers initially set to 'N'
1414 'N', -- price_disabled_flag initially set to 'N'
1415 'N', -- quantity_disabled_flag initially set to 'N'
1416 job_id, -- ADDED FOR SERVICES PROCUREMENT PROJECT - job id
1417 po_agreed_amount, -- ADDED FOR SERVICES PROCUREMENT PROJECT - PO Agreed Amount,
1418 purchase_basis, -- ADDED FOR SERVICES PROCUREMENT PROJECT - Purchase basis of line type
1419 -1, -- Always set the price_diff_shipment_number to -1 for blankets
1420 'LINE', -- Group Type
1421 interface_line_number, -- document_disp_line_number
1422 interface_line_number, -- sub_line_sequence_number
1423 -- Clin Slin Changes
1424 line_num_display,
1425 group_line_id,
1426 clm_info_flag,
1427 clm_option_indicator,
1428 clm_option_num,
1429 clm_option_from_date,
1430 clm_option_to_date,
1431 clm_funded_flag,
1432 clm_base_line_num,
1433 l_line_uda_temp_id,
1434 CLM_CONTRACT_TYPE, -- bug 9914034
1435 CLM_COST_CONSTRAINT,
1436 CLM_IDC_TYPE
1437 FROM pon_auc_items_interface,
1438 mtl_categories_kfv
1439 WHERE interface_auction_header_id = p_interface_id
1440 AND interface_line_number >= l_batch_start
1441 AND interface_line_number <= l_batch_end
1442 AND mtl_categories_kfv.category_id (+) = pon_auc_items_interface.category_id;
1443
1444
1445
1446
1447 -- Update the shipments flag based on if their are shipments
1448
1449 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1450 fnd_log.string(fnd_log.level_statement,
1451 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1452 ' Updating the shipments flag based on if there are shipments');
1453 end if;
1454
1455 UPDATE pon_auction_item_prices_all
1456 SET has_shipments_flag = 'Y'
1457 WHERE (line_number) IN
1458 (SELECT interface_line_number
1459 FROM pon_auc_shipments_interface
1460 WHERE interface_auction_header_id = p_interface_id
1461 AND interface_line_number >= l_batch_start
1462 AND interface_line_number <= l_batch_end)
1463 AND auction_header_id = x_document_number;
1464
1465 -- ADDED FOR SERVICES PROCUREMENT PROJECT
1466 -- Update the price differntials flag based on if their are price differentials at item level
1467 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1468 fnd_log.string(fnd_log.level_statement,
1469 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1470 ' Updating the price differntials flag based on if their are price differentials at item level');
1471 end if;
1472
1473 UPDATE pon_auction_item_prices_all
1474 SET has_price_differentials_flag = 'Y',
1475 differential_response_type = 'OPTIONAL'
1476 WHERE (line_number) IN
1477 (SELECT interface_line_number
1478 FROM pon_price_differ_interface
1479 WHERE interface_auction_header_id = p_interface_id
1480 AND interface_line_number >= l_batch_start
1481 AND interface_line_number <= l_batch_end
1482 AND interface_shipment_number = -1)
1483 AND auction_header_id = x_document_number;
1484
1485
1486 --Update the attachment_flag in pon_auction_item_prices_all table for all the
1487 --items that have an attachment.
1488 -- Since attachments can come from either po line or item itself
1489 -- update the flag after attachments have been inserted.
1490 -- UPDATE pon_auction_item_prices_all
1491 -- SET attachment_flag = 'Y'
1492 -- WHERE auction_header_id = x_document_number
1493 -- AND line_number IN (SELECT interface_line_number
1494 -- FROM pon_attachments_interface
1495 -- WHERE interface_auction_header_id = p_interface_id
1496 -- AND interface_line_number IS NOT NULL);
1497
1498 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1499 fnd_log.string(fnd_log.level_statement,
1500 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1501 ' CAlling Add_Catalog_Descriptors with x_document_number : ' || x_document_number || '; p_interface_id : ' || p_interface_id);
1502 end if;
1503
1504 Add_Catalog_Descriptors (x_document_number, p_interface_id,l_batch_start,l_batch_end);
1505
1506
1507
1508
1509
1510 --<Sol Project>
1511 --Copy Header Udas
1512
1513 IF PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(p_doc_type_id => v_doctype_id) = 1 THEN
1514 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1515 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1516 'Copy Line UDA from PO to Solicitation');
1517 end if;
1518
1519 -- Insert price breaks information into the transaction table
1520 v_debug_status := 'COPY_LINE_UDA';
1521
1522 select interface_line_number bulk collect into l_lineno_list
1523 FROM pon_auc_items_interface
1524 WHERE interface_auction_header_id = p_interface_id
1525 AND interface_line_number >= l_batch_start
1526 AND interface_line_number <= l_batch_end;
1527
1528 for i in 1..l_lineno_list.count
1529 loop
1530 if ( i = 1 ) Then
1531 select uda_template_id into l_src_template_id from po_lines_all where po_header_id = g_header_rec.source_doc_id and rownum<2;
1532 end if;
1533 select source_line_id into l_poline_id from pon_auc_items_interface where
1534 interface_auction_header_id = p_interface_id and interface_line_number = l_lineno_list(i);
1535
1536 l_src_pkey := GET_PKEY(l_poline_id,-1);
1537 l_tar_pkey := GET_PKEY(x_document_number,l_lineno_list(i));
1538
1539 pon_copy_udas_grp.copy_uda_data(
1540 l_src_pkey,
1541 l_tar_pkey,
1542 l_src_template_id,
1543 l_line_uda_temp_id,
1544 'LINE',
1545 'LINE',
1546 'PO',
1547 'SOL',
1548 v_return_status,
1549 v_msg_count,
1550 v_msg_data);
1551
1552 -- bug 9914034 update clm_unit_price and clm_amount columns
1553 IF ( v_return_status <> 'E' ) THEN
1554 SELECT order_type_lookup_code INTO v_order_type_lookup_code from pon_auc_items_interface where
1555 interface_auction_header_id = p_interface_id and interface_line_number = l_lineno_list(i);
1556 IF(v_order_type_lookup_code <> 'QUANTITY') THEN
1557 BEGIN
1558 UPDATE pon_auction_item_prices_all paip
1559 SET clm_amount =
1560 (SELECT N_EXT_ATTR20
1561 FROM pon_auction_item_prices_ext_b ext,po_uda_ag_template_usages usage
1562 WHERE ext.auction_header_id = paip.auction_header_id
1563 AND ext.line_number = paip.LINE_NUMBER
1564 AND usage.TEMPLATE_ID = paip.UDA_TEMPLATE_ID
1565 AND usage.ATTRIBUTE1 = paip.CLM_IDC_TYPE
1566 AND usage.ATTRIBUTE2 = paip.CLM_CONTRACT_TYPE
1567 AND ext.ATTR_GROUP_ID = usage.ATTRIBUTE_GROUP_ID
1568 AND ROWNUM < 2)
1569 WHERE auction_header_id = x_document_number
1570 AND line_number = l_lineno_list(i);
1571 EXCEPTION
1572 WHEN OTHERS THEN
1573 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1574 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1575 'Exception in updating complex pricing extended price ');
1576 END IF;
1577 END;
1578 ELSE
1579 UPDATE pon_auction_Item_prices_all
1580 SET clm_unit_price = current_price,
1581 clm_amount = (Nvl(current_price,0)*Nvl(quantity,0))
1582 WHERE auction_header_id = x_document_number
1583 AND line_number = l_lineno_list(i);
1584 END IF;
1585
1586
1587 END IF;
1588
1589
1590 --copy SHIP_INFO UDA from po_line_locations
1591 IF ( v_return_status <> 'E' ) THEN
1592 select line_location_id bulk collect into l_lineloc_list from po_line_locations_all where po_header_id = g_header_rec.source_doc_id and po_line_id = l_poline_id;
1593 for j in 1..l_lineloc_list.count
1594 loop
1595 v_debug_status := 'COPY_SHIP_UDA';
1596 if ( j = 1 ) THEN
1597 select uda_template_id into l_shp_template_id from po_line_locations_all where line_location_id = l_lineloc_list(j) and rownum<2;
1598 end if;
1599 l_src_pkey := GET_PKEY(l_lineloc_list(j),-1);
1600 l_tar_pkey := GET_PKEY(x_document_number,l_lineno_list(i));
1601
1602 pon_copy_udas_grp.copy_uda_data(
1603 l_src_pkey,
1604 l_tar_pkey,
1605 l_shp_template_id,
1606 l_line_uda_temp_id,
1607 'SHIP',
1608 'LINE',
1609 'PO',
1610 'SOL',
1611 v_return_status,
1612 v_msg_count,
1613 v_msg_data);
1614
1615 exit when ( v_return_status = 'E');
1616 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1617 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.Create_Draft_Neg_interface_pvt',
1618 'Copy Shipment Uda Status '||v_return_status||' '||l_lineloc_list(j));
1619 end if;
1620 end loop;
1621 END IF;
1622
1623 IF ( v_return_status = 'E' ) THEN
1624 X_RESULT := 'FAILURE';
1625 x_error_code := 'CREATE_DRAFT:COPY_LINE_UDA';
1626 x_error_message := 'Could not copy Line Uda.' || Substr(v_msg_data,1,200) ;
1627
1628 -- Update the process_status column in header table
1629 UPDATE pon_auc_headers_interface
1630 SET process_status = 'REJECTED'
1631 WHERE interface_auction_header_id = p_interface_id;
1632
1633 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1634 fnd_log.string(fnd_log.level_statement,
1635 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1636 x_error_message);
1637 end if;
1638
1639 RETURN;
1640 END IF;
1641 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1642 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_sourcing_openapi_grp.Create_Draft_Neg_interface_pvt',
1643 'Copy Line Uda Status '||v_return_status||' '||l_lineno_list(i));
1644 end if;
1645 end loop;
1646 end if;
1647
1648
1649
1650 -- Insert price breaks information into the transaction table
1651 v_debug_status := 'INSERT_SHIPMENTS';
1652
1653 -- When selecting price breaks we donot select the effective start date
1654 -- and effective end date values. Also we want to collapse (so to speak)
1655 -- all price breaks that differ only in quantity, ship_to_location_id
1656 -- and the ship_to_organization_id column values. Hence we apply a group by
1657 -- clause
1658
1659 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1660 fnd_log.string(fnd_log.level_statement,
1661 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1662 ' copying price breaks and price break level price differentials' );
1663 end if;
1664
1665 INSERT INTO pon_auction_shipments_all
1666 (auction_header_id,
1667 line_number,
1668 shipment_number,
1669 shipment_type,
1670 ship_to_organization_id,
1671 ship_to_location_id,
1672 quantity,
1673 price,
1674 effective_start_date,
1675 effective_end_date,
1676 org_id,
1677 creation_date,
1678 created_by,
1679 last_update_date,
1680 last_updated_by,
1681 has_price_differentials_flag
1682 )
1683 SELECT
1684 x_document_number, -- auction_header_id
1685 interface_line_number, -- line_number
1686 MIN(interface_ship_number), -- shipment_number
1687 'PRICE BREAK', -- shipment_type
1688 ship_to_organization_id, -- ship_to_organization_id
1689 ship_to_location_id, -- ship_to_location_id
1690 quantity, -- quantity
1691 MIN(price * v_bidders_currency_rate), -- price
1692 NULL, -- effective_start_date
1693 NULL, -- effective_end_date
1694 MIN(org_id), -- org_id
1695 Sysdate, -- creation_date
1696 g_header_rec.user_id, -- created_by
1697 Sysdate, -- last_update_date
1698 g_header_rec.user_id, -- last_updated_by
1699 'N'
1700 FROM pon_auc_shipments_interface
1701 WHERE interface_auction_header_id = p_interface_id
1702 AND interface_line_number >= l_batch_start
1703 AND interface_line_number <= l_batch_end
1704 GROUP BY interface_line_number,ship_to_organization_id, ship_to_location_id, quantity;
1705
1706 -- ADDED FOR SERVICES PROCUREMENT PROJECT
1707 -- Update the price differntials flag based on if their are price differentials at shipments level
1708 UPDATE pon_auction_shipments_all
1709 SET has_price_differentials_flag = 'Y',
1710 differential_response_type = 'OPTIONAL'
1711 WHERE (shipment_number) IN
1712 (SELECT interface_shipment_number
1713 FROM pon_price_differ_interface
1714 WHERE interface_auction_header_id = p_interface_id
1715 AND interface_shipment_number <> -1)
1716 AND auction_header_id = x_document_number;
1717
1718 -- ADDED FOR SERVICES PROCUREMENT PROJECT
1719 -- Insert price differentials information into the transaction table
1720 -- at item level
1721 v_debug_status := 'INSERT_PRICE_DIFFERENTIALS_ITEM';
1722
1723 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1724 fnd_log.string(fnd_log.level_statement,
1725 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1726 'Inserting price differentials information into the transaction table at item level' );
1727 end if;
1728
1729 INSERT INTO pon_price_differentials
1730 (auction_header_id,
1731 line_number,
1732 shipment_number,
1733 price_differential_number,
1734 price_type,
1735 multiplier,
1736 creation_date,
1737 created_by,
1738 last_update_date,
1739 last_updated_by
1740 )
1741 SELECT
1742 x_document_number, -- auction_header_id
1743 interface_line_number, -- line_number
1744 interface_shipment_number, -- shipment_number
1745 interface_price_differ_number, -- price differentials number
1746 price_type, -- price differential type
1747 multiplier, -- multiplier
1748 Sysdate, -- creation_date
1749 g_header_rec.user_id, -- created_by
1750 Sysdate, -- last_update_date
1751 g_header_rec.user_id -- last_updated_by
1752 FROM pon_price_differ_interface
1753 WHERE interface_auction_header_id = p_interface_id
1754 AND interface_line_number >= l_batch_start
1755 AND interface_line_number <= l_batch_end
1756 AND interface_shipment_number = -1;
1757
1758
1759 v_debug_status := 'INSERT_PRICE_DIFFERENTIALS_SHIP';
1760
1761 -- We need to store the price differentials into the transaction table.
1762 -- But only store those price differentials for which the shipments have
1763 -- been copied into the transaction table.
1764 -- Because of the grouping above its possible that you will have some
1765 -- price differentials for whom the parent shipments have been grouped
1766 -- into one.
1767
1768 -- Insert price differentials information into the transaction table
1769 -- at shipment level
1770
1771 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1772 fnd_log.string(fnd_log.level_statement,
1773 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1774 'Inserting price differentials information into the transaction table at shipment level' );
1775 end if;
1776
1777 INSERT INTO pon_price_differentials
1778 (auction_header_id,
1779 line_number,
1780 shipment_number,
1781 price_differential_number,
1782 price_type,
1783 multiplier,
1784 creation_date,
1785 created_by,
1786 last_update_date,
1787 last_updated_by
1788 )
1789 SELECT
1790 x_document_number, -- auction_header_id
1791 interface_line_number, -- line_number
1792 interface_shipment_number, -- shipment_number
1793 interface_price_differ_number, -- price differentials number
1794 price_type, -- price differential type
1795 multiplier, -- multiplier
1796 Sysdate, -- creation_date
1797 g_header_rec.user_id, -- created_by
1798 Sysdate, -- last_update_date
1799 g_header_rec.user_id -- last_updated_by
1800 FROM pon_price_differ_interface
1801 WHERE interface_auction_header_id = p_interface_id
1802 AND interface_line_number >= l_batch_start
1803 AND interface_line_number <= l_batch_end
1804 AND interface_shipment_number <> -1
1805 AND interface_shipment_number IN (SELECT min(interface_ship_number)
1806 FROM pon_auc_shipments_interface
1807 WHERE interface_auction_header_id = p_interface_id
1808 GROUP BY interface_line_number,ship_to_organization_id, ship_to_location_id, quantity);
1809
1810 -- Copy over the attachments from the PON_ATTACHMENTS_INTERFACE table
1811 v_debug_status := 'INSERT_LINE_ATTACHMENTS';
1812
1813 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1814 fnd_log.string(fnd_log.level_statement,
1815 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1816 'Copying over the (Line) attachments from the PON_ATTACHMENTS_INTERFACE table' );
1817 end if;
1818
1819 INSERT INTO fnd_attached_documents
1820 ( attached_document_id,
1821 document_id,
1822 creation_date,
1823 created_by,
1824 last_update_date,
1825 last_updated_by,
1826 last_update_login,
1827 seq_num,
1828 entity_name,
1829 pk1_value,
1830 pk2_value,
1831 pk3_value,
1832 pk4_value,
1833 pk5_value,
1834 automatically_added_flag,
1835 column1
1836 )
1837 SELECT fnd_attached_documents_s.nextval, -- attached_document_id
1838 document_id, -- document_id,
1839 Sysdate, -- creation_date
1840 g_header_rec.user_id, -- created_by
1841 Sysdate, -- last_update_date
1842 g_header_rec.user_id, -- last_updated_by
1843 NULL, -- last_update_login
1844 seq_num, -- seq_num
1845 'PON_AUCTION_ITEM_PRICES_ALL', -- entity_name
1846 x_document_number, -- pk1_value
1847 interface_line_number, -- pk2_value
1848 NULL, -- pk3_value
1849 NULL, -- pk4_value
1850 NULL, -- pk5_value
1851 'N', -- automatically_added_flag
1852 NULL -- column1
1853 FROM pon_attachments_interface
1854 WHERE interface_auction_header_id = p_interface_id
1855 AND interface_line_number IS NOT NULL
1856 AND interface_line_number >= l_batch_start
1857 AND interface_line_number <= l_batch_end;
1858
1859 --Update the attachment_flag in pon_auction_item_prices_all
1860 --table for all the items that have attachments.
1861
1862 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1863 fnd_log.string(fnd_log.level_statement,
1864 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1865 'Updating the attachment_flag in pon_auction_item_prices_all table for all the items that have attachments.' );
1866 end if;
1867
1868 UPDATE pon_auction_item_prices_all
1869 SET attachment_flag = 'Y'
1870 WHERE auction_header_id = x_document_number
1871 AND line_number IN (SELECT to_number(pk2_value)
1872 FROM fnd_attached_documents
1873 WHERE entity_name = 'PON_AUCTION_ITEM_PRICES_ALL'
1874 AND pk1_value = to_char(x_document_number))
1875 AND line_number >= l_batch_start
1876 AND line_number <= l_batch_end;
1877
1878
1879 END IF; --End if (g_header_rec.origination_code <> 'CONTRACT')
1880
1881
1882 --commit the DML transactions of this batch only if it is a concurrent call
1883
1884 IF (p_is_concurrent_call = 'Y') THEN
1885 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1886 fnd_log.string(fnd_log.level_statement,
1887 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1888 'This being a concurrent call, the batch is being committed( from line numbers ' || l_batch_start || ' to ' || l_batch_end);
1889 end if;
1890
1891 COMMIT;
1892
1893 END IF;
1894
1895 l_batch_start := l_batch_end + 1;
1896
1897 IF (l_batch_end + l_batch_size > l_max_line_number) THEN
1898 l_batch_end := l_max_line_number;
1899 ELSE
1900 l_batch_end := l_batch_end + l_batch_size;
1901 END IF;
1902
1903 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1904 fnd_log.string(fnd_log.level_statement,
1905 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1906 'Computed the window for next batch to be ' || l_batch_start || ' to ' || l_batch_end || ' (inclusive) ' );
1907 end if;
1908
1909 END LOOP;
1910
1911 END IF;
1912
1913 ----------------------------------------------------------------------------------------------------
1914 --BATCHING ENDS HERE--
1915 ----------------------------------------------------------------------------------------------------
1916 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1917 fnd_log.string(fnd_log.level_statement,
1918 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1919 ' Before updating exhibits, check if elins are enabled or not');
1920 end if;
1921 /* Bug : 16690958 : Update exhibit_number field for the lines */
1922 IF PON_EXHIBITS_PKG.IS_EXHIBITS_ENABLED(g_header_rec.org_id, v_doctype_id) = 'Y' THEN
1923
1924 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1925 fnd_log.string(fnd_log.level_statement,
1926 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1927 ' Before updating exhibit numbers');
1928 end if;
1929
1930 UPDATE pon_auction_item_prices_all ai
1931 SET exhibit_number =
1932 (SELECT clm_exhibit_name FROM po_lines pl
1933 WHERE pl.po_header_id = ai.source_doc_id
1934 AND pl.po_line_id = ai.source_line_id)
1935 WHERE ai.auction_header_id = x_document_number;
1936
1937 END IF;
1938
1939 IF PON_EXHIBITS_PKG.IS_EXHIBITS_ENABLED(g_header_rec.org_id, v_doctype_id) = 'Y'
1940 OR PON_EXHIBITS_PKG.IS_CDRLS_ENABLED(g_header_rec.org_id, v_doctype_id) = 'Y' THEN
1941
1942 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1943 fnd_log.string(fnd_log.level_statement,
1944 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1945 ' Insert exhibit details into pon_auction_exhibit_details');
1946 end if;
1947
1948 INSERT INTO pon_auction_exhibit_details
1949 (AUCTION_HEADER_ID,
1950 EXHIBIT_NUMBER,
1951 IS_CDRL,
1952 ASSOCIATED_TO_LINE,
1953 EXHIBIT_DESCRIPTION,
1954 CREATION_DATE,
1955 CREATED_BY,
1956 LAST_UPDATE_DATE,
1957 LAST_UPDATED_BY)
1958 (SELECT
1959 x_document_number,
1960 ped.exhibit_name,
1961 Nvl(ped.is_cdrl, 'N'),
1962 paip.line_number,
1963 ped.exhibit_description,
1964 SYSDATE,
1965 paip.created_by,
1966 SYSDATE,
1967 paip.created_by
1968 FROM pon_auction_item_prices_all paip,
1969 po_exhibit_details_merge_v ped
1970 WHERE paip.auction_header_id = x_document_number
1971 AND ped.po_header_id = paip.source_doc_id
1972 AND ped.reference_line_id = paip.source_line_id
1973 AND ped.draft_id = -1);
1974
1975 END IF;
1976 --End Bug : 16690958
1977
1978 --}
1979 END IF; --IF (l_is_super_large_neg = N) OR (l_is_super_large_neg = 'Y' AND p_is_concurrent_call = 'Y')
1980
1981 --
1982 --Handle the remaining header information
1983 --and miscellaneous information like supplier details etc
1984 --Do this task only if it is an online call
1985 --
1986 IF (p_is_concurrent_call = 'N') THEN
1987
1988 -- Copy over the attachments from the PON_ATTACHMENTS_INTERFACE table
1989 v_debug_status := 'INSERT_HEADER_ATTACHMENTS';
1990
1991 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1992 fnd_log.string(fnd_log.level_statement,
1993 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1994 'Copying over the (header)attachments from the PON_ATTACHMENTS_INTERFACE table' );
1995 end if;
1996
1997 INSERT INTO fnd_attached_documents
1998 ( attached_document_id,
1999 document_id,
2000 creation_date,
2001 created_by,
2002 last_update_date,
2003 last_updated_by,
2004 last_update_login,
2005 seq_num,
2006 entity_name,
2007 pk1_value,
2008 pk2_value,
2009 pk3_value,
2010 pk4_value,
2011 pk5_value,
2012 automatically_added_flag,
2013 column1
2014 )
2015 SELECT
2016 fnd_attached_documents_s.nextval, -- attached_document_id
2017 document_id, -- document_id,
2018 Sysdate, -- creation_date
2019 g_header_rec.user_id, -- created_by
2020 Sysdate, -- last_update_date
2021 g_header_rec.user_id, -- last_updated_by
2022 NULL, -- last_update_login
2023 seq_num, -- seq_num
2024 'PON_AUCTION_HEADERS_ALL', -- entity_name
2025 x_document_number, -- pk1_value
2026 NULL, -- pk2_value
2027 NULL, -- pk3_value
2028 NULL, -- pk4_value
2029 NULL, -- pk5_value
2030 'N', -- automatically_added_flag
2031 NULL -- column1
2032 FROM pon_attachments_interface
2033 WHERE interface_auction_header_id = p_interface_id
2034 AND interface_line_number IS NULL;
2035
2036
2037 -- If the Blanket was in bidders currency see if the user wanted us to
2038 -- copy over the currency into PON_AUC_CURR_INFO table
2039 v_debug_status := 'INSERT_BIDDERS_CURR';
2040
2041 IF (v_set_as_bidders_curr = 'Y' AND v_blanket_bidders_curr = 'Y') THEN
2042
2043 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2044 fnd_log.string(fnd_log.level_statement,
2045 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2046 'Copying over the currency into PON_AUC_CURR_INFO table' );
2047 end if;
2048
2049 INSERT INTO pon_auction_currency_rates
2050 ( auction_header_id,
2051 auction_currency_code,
2052 bid_currency_code,
2053 --rate,
2054 --rate_dsp,
2055 number_price_decimals,
2056 sequence_number,
2057 last_update_date,
2058 last_updated_by,
2059 creation_date,
2060 created_by
2061 ) VALUES
2062 ( x_document_number, -- auction_header_id
2063 v_functional_currency_code, -- auction_currency_code
2064 g_header_rec.currency_code, -- bid_currency_code
2065 --Decode(g_header_rec.rate_type,'User',g_header_rec.rate, NULL), -- rate
2066 --Decode(g_header_rec.rate_type,'User',1/g_header_rec.rate, NULL), -- rate_dsp
2067 10000, -- number_price_decimals set to ANY
2068 10, -- sequence_number
2069 Sysdate, -- last_update_date
2070 g_header_rec.user_id, -- last_updated_by
2071 Sysdate, -- creation_date
2072 g_header_rec.user_id -- created_by
2073 ) ;
2074 END IF;
2075
2076 -- Add the supplier information on the blanket to the PON_BIDDING_PARTIES
2077 -- transaction table.
2078 v_debug_status := 'INSERT_SUPPLIER_INFO';
2079
2080 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2081 fnd_log.string(fnd_log.level_statement,
2082 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2083 'Addding the supplier information on the blanket to the PON_BIDDING_PARTIES; selectiong supplier_site_id' );
2084 end if;
2085
2086 select supplier_site_id
2087 into v_supplier_site_id
2088 from pon_auc_headers_interface
2089 WHERE interface_auction_header_id = p_interface_id;
2090
2091 if(v_supplier_site_id is not null and v_supplier_site_id <> -1) then
2092 BEGIN
2093 select vendor_site_code
2094 into v_supplier_site_code
2095 from po_vendor_sites_all
2096 where vendor_site_id = v_supplier_site_id ;
2097 EXCEPTION
2098 WHEN no_data_found THEN
2099 v_supplier_site_code := '-1';
2100 END;
2101
2102 else
2103 v_supplier_site_id := -1;
2104 v_supplier_site_code := '-1';
2105 end if;
2106
2107 --lxchen
2108 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2109 fnd_log.string(fnd_log.level_statement,
2110 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2111 'Getting supplier information' );
2112 end if;
2113
2114 -- Get the supplier information
2115 get_trading_partner_info(g_header_rec.supplier_id,
2116 v_trading_partner_id,
2117 v_trading_partner_name,
2118 v_trading_partner_contact_id,
2119 v_trading_partner_contact_name,
2120 v_error_code,
2121 v_error_message);
2122
2123 IF (v_error_code IS NOT NULL) THEN
2124 X_RESULT := 'FAILURE';
2125 x_error_code := v_error_code;
2126 x_error_message := v_error_message;
2127
2128 -- Update the process_status column in header table
2129 UPDATE pon_auc_headers_interface
2130 SET process_status = 'REJECTED'
2131 WHERE interface_auction_header_id = p_interface_id;
2132
2133 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2134 fnd_log.string(fnd_log.level_statement,
2135 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2136 'Error in retrieving supplier information for supplier_id : ' || g_header_rec.supplier_id );
2137 end if;
2138
2139 RETURN;
2140 END IF;
2141
2142 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2143 fnd_log.string(fnd_log.level_statement,
2144 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2145 'Inserting record into PON_BIDDING_PARTIES' );
2146 end if;
2147
2148
2149 INSERT INTO pon_bidding_parties
2150 (auction_header_id,
2151 list_id,
2152 sequence,
2153 trading_partner_name,
2154 trading_partner_id,
2155 trading_partner_contact_id,
2156 trading_partner_contact_name,
2157 --bid_currency_code,
2158 --number_price_decimals,
2159 --rate,
2160 --rate_dsp,
2161 vendor_site_id,
2162 vendor_site_code,
2163 last_update_date,
2164 last_updated_by,
2165 creation_date,
2166 created_by,
2167 access_type
2168 )VALUES
2169 (x_document_number, -- auction_header_id
2170 -1, -- list id
2171 10, -- sequence
2172 v_trading_partner_name, -- trading_partner_name
2173 v_trading_partner_id, -- trading_partner_id
2174 v_trading_partner_contact_id, -- trading_partner_contact_id
2175 v_trading_partner_contact_name, -- trading_partner_contact_name
2176 --g_header_rec.currency_code, -- bid_currency_code
2177 --10000, -- number_price_decimals,set to ANY
2178 --Decode(g_header_rec.rate_type,'USER',g_header_rec.rate, NULL), -- rate
2179 --Decode(g_header_rec.rate_type,'USER',1/g_header_rec.rate, NULL), -- rate_dsp
2180 v_supplier_site_id, -- default vendor site id
2181 v_supplier_site_code, -- default vendor site code
2182 Sysdate, -- last_update_date
2183 g_header_rec.user_id, -- last_updated_by
2184 Sysdate, -- creation_date
2185 g_header_rec.user_id, -- created_by
2186 'FULL' -- access_type
2187 );
2188
2189 --
2190 --If it's a super large auction, then raise a concurrent request here
2191 --to handle the children
2192 --
2193 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2194 fnd_log.string(fnd_log.level_statement,
2195 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2196 'Is this auction super large? (l_is_super_large_neg) : ' || l_is_super_large_neg);
2197 end if;
2198
2199 IF (l_is_super_large_neg = 'Y') THEN
2200 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2201 fnd_log.string(fnd_log.level_statement,
2202 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2203 'Raising a concurrent request here');
2204 end if;
2205
2206 --RAISE CONCURRENT REQUEST HERE
2207 l_request_id := FND_REQUEST.submit_request(
2208 application => 'PON',
2209 program => 'PON_RENEGOTIATE_BLANKET',
2210 description => null,
2211 start_time => null,
2212 sub_request => FALSE,
2213 argument1 => to_char(p_interface_id),
2214 argument2 => to_char(x_document_number),
2215 argument3 => FND_GLOBAL.USER_NAME
2216 );
2217
2218 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2219 fnd_log.string(fnd_log.level_statement,
2220 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2221 'Concurrent request successfully raised; Request id : ' || l_request_id ||' ; setting the request information into pon_auction_headers_all');
2222 end if;
2223
2224 update pon_auction_headers_all set
2225 request_id = l_request_id,
2226 number_of_lines = 0,
2227 requested_by = g_header_rec.user_id ,
2228 request_date = sysdate,
2229 last_update_date = sysdate,
2230 last_updated_by = g_header_rec.user_id ,
2231 complete_flag = 'N'
2232 where auction_header_id = x_document_number;
2233
2234 x_request_id := l_request_id;
2235
2236 END IF;
2237
2238 END IF; --IF (p_is_concurrent_call = 'N')
2239
2240 -----------------------------------------------------------------------------
2241 -- Set the result to success and unset any variables required.
2242 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2243 fnd_log.string(fnd_log.level_statement,
2244 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2245 'Setting the result to success');
2246 end if;
2247
2248 x_result := 'SUCCESS';
2249
2250 --
2251 --Update the header record process_status to ACCEPTED and
2252 --COMPLETE_FLAG in pon_auction_headers_all to 'Y'.
2253 --Also set the NUMBER_OF_LINES and LAST_LINE_NUMBER fields
2254 --This should not be done if
2255 --(a) It is not a super large auction OR
2256 --(b) It is a super large auction and the call to this function is
2257 -- from a concurrent program
2258 --
2259 IF (l_is_super_large_neg = 'N') OR (l_is_super_large_neg = 'Y' AND p_is_concurrent_call = 'Y') THEN
2260
2261 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2262 fnd_log.string(fnd_log.level_statement,
2263 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2264 'updating the process_status in pon_auc_headers_interface to ACCEPTED');
2265 end if;
2266
2267 UPDATE pon_auc_headers_interface
2268 SET process_status = 'ACCEPTED'
2269 WHERE interface_auction_header_id = p_interface_id;
2270
2271 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2272 fnd_log.string(fnd_log.level_statement,
2273 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2274 'updating the complete_flag in pon_auc_headers_all to Y');
2275 end if;
2276
2277
2278 SELECT
2279 COUNT(LINE_NUMBER) number_of_lines, MAX (DECODE (GROUP_TYPE, 'LOT_LINE', 0, 'GROUP_LINE', 0, SUB_LINE_SEQUENCE_NUMBER)) last_line_number
2280 INTO l_number_of_lines, l_last_line_number
2281 FROM PON_AUCTION_ITEM_PRICES_ALL
2282 WHERE
2283 AUCTION_HEADER_ID = x_document_number;
2284
2285
2286
2287
2288 UPDATE pon_auction_headers_all
2289 SET complete_flag = 'Y', number_of_lines = l_number_of_lines, last_line_number = l_last_line_number
2290 WHERE auction_header_id = x_document_number;
2291
2292 END IF; --IF (l_is_super_large_neg = N) OR (l_is_super_large_neg = 'Y' AND p_is_concurrent_call = 'Y')
2293
2294 --call commit. This is for TEST purposes only
2295 --COMMIT;
2296
2297 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2298 fnd_log.string(fnd_log.level_statement,
2299 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2300 'returning with output -- '||
2301 'x_document_number : '|| x_document_number ||
2302 'x_document_url : ' || x_document_url ||
2303 'x_result : ' || x_result ||
2304 'x_error_code : ' || x_error_code ||
2305 'x_error_message : ' || x_error_message);
2306 end if;
2307
2308 EXCEPTION
2309 WHEN others THEN
2310 fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2311 fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2312 fnd_message.set_token('PROCEDURE', 'create_draft_neg_interface');
2313 fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
2314
2315 ROLLBACK TO SAVEPOINT pon_before_insert;
2316
2317 app_exception.raise_exception;
2318
2319 END create_draft_neg_interface_pvt;
2320
2321 /*======================================================================
2322 PROCEDURE : val_auc_headers_interface PUBLIC
2323 PARAMETERS:
2324 p_interface_id IN interface id for the auction that is being validated
2325 x_error_code OUT NOCOPY error code if any error generate
2326 x_error_message OUT NOCOPY error message if any error
2327
2328 COMMENT : validates the data in the pon_auc_headers_interface table
2329 ======================================================================*/
2330
2331 PROCEDURE val_auc_headers_interface(p_interface_id NUMBER,
2332 x_error_code OUT NOCOPY VARCHAR2,
2333 x_error_message OUT NOCOPY VARCHAR2)
2334 IS
2335 v_debug_status VARCHAR2(100);
2336 v_count_auc_headers_interface NUMBER := 0;
2337 v_multi_org fnd_product_groups.multi_org_flag%TYPE := 'Y';
2338 v_process_status VARCHAR2(25);
2339
2340 BEGIN
2341
2342 v_debug_status := 'VALIDATE_HEADER';
2343
2344 -- Validate that the actual parameter p_interfaceid is a valid value
2345 IF (p_interface_id IS NULL) THEN
2346 x_error_code := 'VALIDATE_HEADER:NULL_INTERFACE_ID';
2347 x_error_message := 'Interface ID cannot be null';
2348
2349 RETURN;
2350 END IF;
2351
2352
2353 -- Validate that there is a single record for p_interface_id
2354 -- in the PON_AUC_HEADERS_INTERFACE table.
2355 SELECT COUNT(*) INTO v_count_auc_headers_interface
2356 FROM pon_auc_headers_interface
2357 WHERE interface_auction_header_id = p_interface_id;
2358
2359 IF (v_count_auc_headers_interface = 0 )THEN
2360 x_error_code := 'VALIDATE_HDR:INVALID_INTERFACE_ID';
2361 x_error_message := 'Cannot find header interface data for interface id ' || p_interface_id ;
2362 RETURN;
2363 END IF;
2364
2365 -- Validate that the process_status for the record is null
2366 /**
2367 SELECT process_status INTO v_process_status
2368 FROM pon_auc_headers_interface
2369 WHERE interface_auction_header_id = p_interface_id;
2370
2371 IF (v_process_status IS NOT NULL) THEN
2372 x_error_code := 'VALIDATE_HDR:INVALIDATE_PROCESS_STATUS';
2373 x_error_message := 'Process status value cannot be set before creating draft negotiation.';
2374 RETURN;
2375 END IF ;
2376 **/
2377
2378
2379 -- Read data into record for convenience
2380 --Call INITIALISE _GLOBALS is no more needed
2381 --here as it is called in create_draft_neg_interface_pvt
2382
2383 --INITIALISE_GLOBALS(p_interface_id => p_interface_id);
2384
2385
2386 -- Validate org_id is not null
2387 IF (g_header_rec.org_id IS NULL) THEN
2388 x_error_code := 'VALIDATE_HDR:NULL_ORG_ID';
2389 x_error_message := 'Please specify an ORG_ID';
2390 RETURN;
2391 END IF;
2392
2393 -- Validate that the origination_code is 'BLANKET' or 'CONTRACT'
2394 IF (g_header_rec.origination_code <> 'BLANKET' AND g_header_rec.origination_code <> 'CONTRACT') THEN
2395 x_error_code := 'VALIDATE_HDR:INVALID_AUCTION_ORIGNATION_CODE';
2396 x_error_message := 'Invalid origination_code ' || g_header_rec.origination_code || ' in header interface table' ;
2397
2398 RETURN;
2399 END IF;
2400
2401
2402 -- Validate that the contract_type is 'BLANKET'
2403 IF g_header_rec.contract_type <> 'BLANKET' THEN
2404 x_error_code := 'VALIDATE_HDR:INVALID_CONTRACT_TYPE';
2405 x_error_message := 'Invalid contract_type ' || g_header_rec.contract_type || ' in header interface table' ;
2406
2407 RETURN;
2408 END IF;
2409
2410 -- Validate that the document_type is 'BUYER_AUCTION' or 'REQUEST_FOR_QUOTE' or 'SOLICITATION' only --<Sol Project>
2411 IF NOT (g_header_rec.neg_type = 'BUYER_AUCTION' OR g_header_rec.neg_type = 'REQUEST_FOR_QUOTE' OR g_header_rec.neg_type = 'SOLICITATION') THEN
2412 x_error_code := 'VALIDATE_HDR:INVALID_NEG_TYPE';
2413 x_error_message := 'Invalid neg_type. Valid values are BUYER_AUCTION or REQUEST_FOR_QUOTE or SOLICITATION only';
2414 RETURN;
2415 END IF;
2416
2417 -- Validate that the buyer_id is not null
2418 IF (g_header_rec.user_id IS NULL) THEN
2419 x_error_code := 'VALIDATE_HDR:INVALID_BUYER_ID';
2420 x_error_message := 'Invalid buyer_id. This field should not be empty.';
2421 RETURN;
2422 END IF;
2423
2424
2425 EXCEPTION
2426 WHEN others THEN
2427 fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2428 fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2429 fnd_message.set_token('PROCEDURE', 'val_auc_headers_interface');
2430 fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
2431 app_exception.raise_exception;
2432
2433 END val_auc_headers_interface;
2434
2435
2436 /*======================================================================
2437 PROCEDURE : val_auc_items_interface PUBLIC
2438 PARAMETERS:
2439 p_interface_id IN interfaceid for the auction that is being validated
2440 x_error_code OUT NOCOPY errcode if any error generate
2441 x_error_message OUT NOCOPY error message if any error
2442
2443 COMMENT : validates the data in the pon_auc_items_interface table
2444 In this procedure we do column wise validation
2445 ======================================================================*/
2446
2447 PROCEDURE val_auc_items_interface(p_interface_id NUMBER,
2448 x_error_code OUT NOCOPY VARCHAR2,
2449 x_error_message OUT NOCOPY VARCHAR2)
2450 IS
2451 v_debug_status VARCHAR2(100);
2452 v_invalid_item_recs NUMBER;
2453 v_item_org_id NUMBER := 0;
2454
2455 BEGIN
2456
2457 v_debug_status := 'VALIDATING_ITEMS';
2458
2459 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2460 fnd_log.string(fnd_log.level_statement,
2461 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2462 'Validating the lines in the range ; validating item org_id');
2463 end if;
2464
2465 -- validate item org_id is the same as that in the header
2466
2467 SELECT MIN(interface_line_number) INTO v_invalid_item_recs
2468 FROM pon_auc_items_interface
2469 WHERE interface_auction_header_id = p_interface_id
2470 AND org_id <> g_header_rec.org_id;
2471
2472 IF (v_invalid_item_recs IS NOT NULL ) THEN
2473
2474 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2475 fnd_log.string(fnd_log.level_statement,
2476 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2477 'Error in validating item org_id');
2478 end if;
2479
2480 x_error_code := 'VALIDATE_ITEMS:INCORRECT_ORG_ID';
2481 x_error_message := 'Item interface table org_id does not match the header org id - ' || g_header_rec.org_id || ' for line ' || v_invalid_item_recs;
2482
2483 RETURN;
2484 END IF;
2485
2486 -- Validate origination_code is BLANKET
2487
2488 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2489 fnd_log.string(fnd_log.level_statement,
2490 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2491 'Validating origination_code is BLANKET');
2492 end if;
2493
2494 v_debug_status := 'VALIDATE_LINE_ORG_CODE';
2495
2496 SELECT MIN(interface_line_number) INTO v_invalid_item_recs
2497 FROM pon_auc_items_interface
2498 WHERE interface_auction_header_id = p_interface_id
2499 AND origination_code <> 'BLANKET';
2500
2501 IF (v_invalid_item_recs IS NOT NULL ) THEN
2502
2503 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2504 fnd_log.string(fnd_log.level_statement,
2505 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2506 'Error in validating origination_code is BLANKET');
2507 end if;
2508
2509 x_error_code := 'VALIDATE_ITEMS:INVALID_LINE_ORIGINATION_CODE';
2510 x_error_message := 'Invalid origination_code in item interface table' ;
2511
2512 RETURN;
2513 END if;
2514
2515 -- Validate that price_break_type values are CUMULATIVE, NON-CUMMULATIVE or null
2516
2517 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2518 fnd_log.string(fnd_log.level_statement,
2519 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2520 'Validating that price_break_type values are CUMULATIVE, NON-CUMMULATIVE or null');
2521 end if;
2522
2523 v_debug_status := 'VALIDATE_PRICE_BREAK_TYPE';
2524
2525 SELECT MIN(interface_line_number) INTO v_invalid_item_recs
2526 FROM pon_auc_items_interface
2527 WHERE interface_auction_header_id = p_interface_id
2528 AND decode(price_break_type, null, 'NONE', 'NON CUMULATIVE', 'NON-CUMULATIVE', price_break_type) NOT IN ('CUMULATIVE', 'NON-CUMULATIVE','NONE');
2529
2530 IF (v_invalid_item_recs IS NOT NULL ) THEN
2531
2532 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2533 fnd_log.string(fnd_log.level_statement,
2534 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2535 'Error in validating that price_break_type values are CUMULATIVE, NON-CUMMULATIVE or null');
2536 end if;
2537
2538 x_error_code := 'VALIDATE_ITEMS:INVALID_PRICE_BREAK_TYPE';
2539 x_error_message := 'Invalid price break type in item interface table' ;
2540
2541 RETURN;
2542 END if;
2543
2544 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2545 fnd_log.string(fnd_log.level_statement,
2546 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2547 'Returning with x_error_code : ' || x_error_code || '; x_error_message : ' || x_error_message);
2548 end if;
2549
2550 EXCEPTION
2551 WHEN others THEN
2552 fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2553 fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2554 fnd_message.set_token('PROCEDURE', 'val_auc_items_interface');
2555 fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
2556 app_exception.raise_exception;
2557
2558 END val_auc_items_interface;
2559
2560
2561 /*======================================================================
2562 PROCEDURE : val_auc_shipments_interface PUBLIC
2563 PARAMETERS:
2564 p_interface_id IN interfaceid for the auction that is being validated
2565 x_error_code OUT NOCOPY errcode if any error generate
2566 x_error_message OUT NOCOPY error message if any error
2567
2568 COMMENT : validates the data in the pon_auc_shipments_interface table
2569 ======================================================================*/
2570
2571 PROCEDURE val_auc_shipments_interface(p_interface_id NUMBER,
2572 x_error_code OUT NOCOPY VARCHAR2,
2573 x_error_message OUT NOCOPY VARCHAR2)
2574 IS
2575 v_debug_status VARCHAR2(100);
2576 v_shipment_number NUMBER := 0;
2577 v_shipment_org_id NUMBER := 0;
2578 v_shipment_rec VARCHAR2(40);
2579
2580 BEGIN
2581
2582 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2583 fnd_log.string(fnd_log.level_statement,
2584 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2585 'Entered the procedure');
2586 end if;
2587
2588 -- validate item org_id is the same as that in the header
2589
2590 SELECT MIN (To_char(interface_line_number) || '-' || To_char(interface_ship_number))
2591 INTO v_shipment_rec
2592 FROM pon_auc_shipments_interface
2593 WHERE interface_auction_header_id = p_interface_id
2594 AND org_id <> g_header_rec.org_id;
2595
2596 IF (v_shipment_rec IS NOT null) THEN
2597
2598 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2599 fnd_log.string(fnd_log.level_statement,
2600 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2601 'Error in validating item org_id');
2602 end if;
2603
2604 x_error_code := 'VALIDATE_SHIPMENTS:INCORRECT_ORG_ID';
2605 x_error_message := 'Shipments interface table org_id does not match the header org id - ' || g_header_rec.org_id || ' for record - ' || v_shipment_rec;
2606
2607 RETURN;
2608 END IF;
2609
2610 -- Validate the shipment type columns have correct values
2611
2612 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2613 fnd_log.string(fnd_log.level_statement,
2614 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2615 'Validating the shipment type columns have correct values');
2616 end if;
2617
2618 v_debug_status := 'VALIDATE_SHIPMENT_TYPE';
2619 SELECT MIN(To_char(interface_line_number) || '-' || To_char(interface_ship_number))
2620 INTO v_shipment_rec
2621 FROM pon_auc_shipments_interface
2622 WHERE interface_auction_header_id = p_interface_id
2623 AND shipment_type <> 'PRICE BREAK';
2624
2625 IF (v_shipment_rec IS NOT NULL ) THEN
2626
2627 x_error_code := 'VALIDATE_SHIPMENTS:INVALID_SHIP_TYPE';
2628 x_error_message := 'Invalid shipment_type in val_auc_shipments_interface table at record ' || v_shipment_rec;
2629 RETURN;
2630 END IF;
2631
2632
2633 EXCEPTION
2634 WHEN others THEN
2635 fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2636 fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2637 fnd_message.set_token('PROCEDURE', 'val_auc_shipments_interface');
2638 fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
2639 app_exception.raise_exception;
2640
2641 END val_auc_shipments_interface;
2642
2643 /*======================================================================
2644 PROCEDURE : val_attachments_interface PUBLIC
2645 PARAMETERS:
2646 p_interface_id IN Interface Header Id
2647 x_error_code OUT NOCOPY errcode if any error generate
2648 x_error_message OUT NOCOPY error message if any error
2649
2650 COMMENT : validates the data in the pon_attachments_interface table.
2651 The basic validation that is performed is to make sure that every
2652 record in this table corresponds to a valid header or valid line item
2653 in the other interface tables.
2654 ======================================================================*/
2655
2656 PROCEDURE val_attachments_interface(p_interface_id NUMBER,
2657 x_error_code OUT NOCOPY VARCHAR2,
2658 x_error_message OUT NOCOPY VARCHAR2)
2659 IS
2660 v_debug_status VARCHAR2(100);
2661
2662 BEGIN
2663 v_debug_status := 'ATTACHMENTS';
2664
2665 RETURN;
2666
2667 EXCEPTION
2668 WHEN others THEN
2669 fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2670 fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2671 fnd_message.set_token('PROCEDURE', 'val_attachments_interface');
2672 fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
2673 app_exception.raise_exception;
2674
2675 END val_attachments_interface;
2676
2677
2678 /*======================================================================
2679 PROCEDURE : get_trading_partner_info PUBLIC
2680 PARAMETERS:
2681 p_vendor_id IN vendor id for whom we need info
2682 x_trading_partner_id OUT NOCOPY trading_partner_id
2683 x_trading_partner_name OUT NOCOPY name of the supplier
2684 x_trading_partner_contact_id OUT NOCOPY id of the first contact person
2685 for this trading_partner
2686 x_trading_partner_contact_name OUT NOCOPY trading_partner contact name
2687 x_error_code OUT NOCOPY errcode if any error generate
2688 x_error_message OUT NOCOPY error message if any error
2689
2690 COMMENT : gets the trading_partner_information given a vendor id
2691 ======================================================================*/
2692
2693 PROCEDURE get_trading_partner_info(p_vendor_id NUMBER,
2694 x_trading_partner_id OUT NOCOPY NUMBER,
2695 x_trading_partner_name OUT NOCOPY VARCHAR2,
2696 x_trading_partner_contact_id OUT NOCOPY VARCHAR2,
2697 x_trading_partner_contact_name OUT NOCOPY VARCHAR2,
2698 x_error_code OUT NOCOPY VARCHAR2,
2699 x_error_message OUT NOCOPY varchar2)
2700 IS
2701 v_debug_status VARCHAR2(100);
2702 v_relationship_id NUMBER;
2703 v_exception_message VARCHAR2(400);
2704 v_error_status VARCHAR2(100);
2705
2706 BEGIN
2707
2708 -- Get the trading_partner_name and trading_partner_id of the supplier on
2709 -- the existing blanket
2710 v_debug_status := 'TRADING_PARTNER';
2711 x_trading_partner_id := pos_vendor_util_pkg.get_party_id_for_vendor(p_vendor_id);
2712
2713 SELECT party_name INTO x_trading_partner_name
2714 FROM hz_parties
2715 WHERE party_id = x_trading_partner_id;
2716
2717 -- Get the first contact as default contact for this trading_partner
2718 v_debug_status := 'TRADING_PARTNER_CONTACT';
2719
2720 BEGIN
2721 SELECT object_id INTO x_trading_partner_contact_id
2722 FROM hz_relationships
2723 WHERE subject_id = x_trading_partner_id
2724 AND relationship_type = 'CONTACT'
2725 AND relationship_code = 'CONTACT_OF'
2726 AND start_date < Sysdate
2727 AND Nvl(end_date, Sysdate+1) > Sysdate
2728 AND status = 'A'
2729 AND ROWNUM = 1;
2730
2731 SELECT user_name INTO x_trading_partner_contact_name
2732 FROM fnd_user
2733 WHERE person_party_id = x_trading_partner_contact_id
2734 AND nvl(fnd_user.end_date,sysdate) >= sysdate;
2735
2736 EXCEPTION
2737
2738 WHEN too_many_rows THEN
2739 IF ( FND_LOG.level_error >= fnd_log.g_current_runtime_level) then
2740 FND_LOG.string(log_level => FND_LOG.level_error,
2741 module => ' get_trading_partner_info ',
2742 message => ' Error while fetching UserName from fnd_user '|| SQLERRM);
2743 END IF;
2744
2745 SELECT user_name
2746 INTO x_trading_partner_contact_name
2747 FROM fnd_user
2748 WHERE person_party_id = x_trading_partner_contact_id
2749 AND nvl(end_date,sysdate) >= sysdate
2750 AND ROWNUM = 1;
2751 RETURN;
2752
2753 WHEN no_data_found THEN
2754 --When no trading_partner_contact is found donot return error.
2755 --x_error_code := 'GET_TRADING_PARTNER_INFO:NO_TP_CONTACT_FOUND';
2756 --x_error_message := 'Could not find default contact for TP';
2757 x_trading_partner_contact_name := NULL;
2758 RETURN;
2759 END;
2760
2761 RETURN;
2762
2763 EXCEPTION
2764 WHEN others THEN
2765 fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2766 fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2767 fnd_message.set_token('PROCEDURE', 'get_trading_partner_info');
2768 fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
2769 app_exception.raise_exception;
2770
2771 END get_trading_partner_info;
2772
2773
2774
2775 /*======================================================================
2776 PROCEDURE : purge_interface_table PUBLIC
2777 PARAMETERS:
2778 p_interface_id IN interfaceid for the auction that is being validated
2779 x_result OUT NOCOPY result returned to called indicating SUCCESS or FAILURE
2780 x_error_code OUT NOCOPY errcode if any error generate
2781 x_error_message OUT NOCOPY error message if any error. size is 250.
2782
2783 COMMENT : gets the trading_partner_information given a vendor id
2784 ======================================================================*/
2785
2786 PROCEDURE purge_interface_table(p_interface_id IN NUMBER,
2787 x_result OUT NOCOPY VARCHAR2,
2788 x_error_code OUT NOCOPY VARCHAR2,
2789 x_error_message OUT NOCOPY VARCHAR2
2790 )
2791 IS
2792
2793 v_debug_status VARCHAR2(100);
2794
2795 BEGIN
2796
2797 IF (NOT g_call_purge) THEN
2798 x_result := 'SUCCESS';
2799 return;
2800 END IF;
2801
2802 x_result := 'FAILURE';
2803
2804 -- Delete records from header table
2805 v_debug_status := 'DELETE_HEADER';
2806
2807 DELETE FROM pon_auc_headers_interface
2808 WHERE interface_auction_header_id = p_interface_id;
2809
2810 -- Delete records from item table
2811 v_debug_status := 'DELETE_ITEM';
2812
2813 DELETE FROM pon_auc_items_interface
2814 WHERE interface_auction_header_id = p_interface_id;
2815
2816 -- Deletes records from attributes table
2817 v_debug_status := 'DELETE_ATTRIBUTES';
2818
2819 DELETE FROM pon_attributes_interface
2820 WHERE interface_auction_header_id = p_interface_id;
2821
2822 -- Delete records from shipments table
2823 v_debug_status := 'DELETE_SHIPMENTS';
2824
2825 DELETE FROM pon_auc_shipments_interface
2826 WHERE interface_auction_header_id = p_interface_id;
2827
2828 -- ADDED FOR SERVICES PROCUREMENT PROJECT
2829 -- Delete records from price differentials table
2830 v_debug_status := 'DELETE_PRICE_DIFFERENTIALS';
2831
2832 DELETE FROM pon_price_differ_interface
2833 WHERE interface_auction_header_id = p_interface_id;
2834
2835 -- Delete records from attachments table
2836 v_debug_status := ' DELETE_ATTACHMENTS';
2837
2838 DELETE FROM pon_attachments_interface
2839 WHERE interface_auction_header_id = p_interface_id;
2840
2841 x_result := 'SUCCESS';
2842
2843 RETURN;
2844
2845 EXCEPTION
2846 WHEN others THEN
2847 fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2848 fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2849 fnd_message.set_token('PROCEDURE', 'purge_interface_table');
2850 fnd_message.set_token('ERROR','[' || SQLERRM || ']');
2851 app_exception.raise_exception;
2852 END purge_interface_table;
2853
2854 -------------------------------------------------------------------------------
2855 --Start of Comments
2856 --Name: is_cpa_integration_enabled
2857 --Pre-reqs:
2858 -- None.
2859 --Modifies:
2860 -- None.
2861 --Locks:
2862 -- None.
2863 --Function:
2864 -- This procedure determines if CPA outcome from rfq feature is enabled or not.
2865 --Parameters:
2866 --IN:
2867 --p_init_msg_list
2868 -- True/False parameter to initialize message list
2869 -- Defaults to false if nothing specified
2870 --p_api_version
2871 -- API version
2872 --OUT:
2873 --x_msg_count
2874 -- Message count
2875 --x_msg_data
2876 -- message data
2877 --x_return_status
2878 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
2879 --x_cpa_enabled
2880 -- Y if creation of CPA from sourcing is enabled
2881 -- N if creation of CPA from sourcing is disabled.
2882 --Testing:
2883 --
2884 --End of Comments
2885 -------------------------------------------------------------------------------
2886
2887 PROCEDURE is_cpa_integration_enabled
2888 (p_api_version IN VARCHAR2
2889 ,p_init_msg_list IN VARCHAR2
2890 ,x_return_status OUT NOCOPY VARCHAR2
2891 ,x_msg_count OUT NOCOPY NUMBER
2892 ,x_msg_data OUT NOCOPY VARCHAR2
2893 ,x_cpa_enabled OUT NOCOPY VARCHAR2) IS
2894
2895 -- declare local variables
2896 l_api_name CONSTANT VARCHAR2(30) := 'IS_CPA_INTEGRATION_ENABLED';
2897 l_pkg_name CONSTANT VARCHAR2(30) := 'PON_SOURCING_OPENAPI_GRP';
2898 l_api_version CONSTANT VARCHAR2(5) := '1.0';
2899
2900 BEGIN
2901
2902 IF NOT (FND_API.compatible_api_call(l_api_version
2903 ,p_api_version
2904 ,l_api_name
2905 ,l_pkg_name)) THEN
2906 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2907 END IF;
2908
2909 -- initialize API return status to success
2910 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2911
2912 -- initialize meesage list
2913 IF (FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE))) THEN
2914 FND_MSG_PUB.initialize;
2915 END IF;
2916
2917 x_cpa_enabled := 'Y';
2918
2919 EXCEPTION
2920 WHEN OTHERS THEN
2921 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2922 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2923 FND_MSG_PUB.add_exc_msg(l_pkg_name, l_api_name,SQLERRM);
2924 IF ( FND_LOG.level_unexpected >= fnd_log.g_current_runtime_level) then
2925 FND_LOG.string(log_level => FND_LOG.level_unexpected
2926 ,module => l_pkg_name ||'.'||l_api_name
2927 ,message => SQLERRM);
2928 END IF;
2929 END IF;
2930 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
2931 ,p_data => x_msg_data);
2932 END is_cpa_integration_enabled;
2933
2934 /* ======================================================================
2935 PROCEDURE : get_display_line_number PUBLIC
2936 PARAMETERS:
2937 p_api_version IN API Version (currently 1.0)
2938 p_init_msg_list IN call FND_MSG_PUB.initialize() ? T/F
2939 p_auction_header_id IN Auction Header Id of Sourcing document
2940 p_auction_line_number IN Line Number (internal) within the Sourcing document
2941 x_display_line_number OUT Line Number for display to users (buffer size 25)
2942 x_result OUT One of G_RET_STS_SUCCESS, G_RET_STS_ERROR, G_RET_STS_UNEXP_ERROR
2943 x_error_code OUT Error code if x_result <> SUCCESS
2944 x_error_message OUT Error message if x_result is FAILURE (buffer size 250)
2945 x_return_status OUT One of G_RET_STS_SUCCESS, G_RET_STS_ERROR, G_RET_STS_UNEXP_ERROR
2946 x_msg_count OUT Error message count
2947 x_msg_data OUT Error message data
2948
2949 COMMENT:
2950 This procedure translates the auction_line_number to a string to
2951 display to the user. Since the display_line_number can change at any time
2952 during auction creation, this procedure should be called each time the line
2953 is displayed rather than cacheing the return value.
2954 ====================================================================== */
2955
2956 procedure get_display_line_number(
2957 p_api_version IN NUMBER,
2958 p_init_msg_list IN VARCHAR2,
2959 p_auction_header_id IN NUMBER,
2960 p_auction_line_number IN NUMBER,
2961 x_display_line_number OUT NOCOPY VARCHAR2,
2962 x_return_status OUT NOCOPY VARCHAR2,
2963 x_msg_count OUT NOCOPY NUMBER,
2964 x_msg_data OUT NOCOPY VARCHAR2) IS
2965
2966 l_pkg_name CONSTANT VARCHAR2(30) := 'PON_SOURCING_OPENAPI_GRP';
2967 l_api_name CONSTANT VARCHAR2(30) := 'get_display_line_number';
2968 l_api_version CONSTANT NUMBER := 1.0;
2969
2970 begin
2971 IF NOT (FND_API.compatible_api_call(l_api_version,
2972 p_api_version,
2973 l_api_name,
2974 l_pkg_name)) THEN
2975 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count,
2976 p_data => x_msg_data);
2977 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2978 END IF;
2979
2980 -- initialize meesage list
2981 IF (FND_API.to_Boolean(nvl(p_init_msg_list, FND_API.G_FALSE))) THEN
2982 FND_MSG_PUB.initialize();
2983 END IF;
2984
2985 begin
2986 select
2987 document_disp_line_number
2988 into
2989 x_display_line_number
2990 from
2991 pon_auction_item_prices_all
2992 where
2993 auction_header_id = p_auction_header_id and
2994 line_number = p_auction_line_number;
2995
2996 -- bug# 9910507
2997 IF ( PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id) = 1 ) THEN
2998 select
2999 line_num_display
3000 into
3001 x_display_line_number
3002 from
3003 pon_auction_item_prices_all
3004 where
3005 auction_header_id = p_auction_header_id and
3006 line_number = p_auction_line_number;
3007
3008 END IF;
3009 exception
3010 when others then
3011 x_return_status := FND_API.G_RET_STS_ERROR;
3012 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3013 FND_MSG_PUB.add_exc_msg(l_pkg_name, l_api_name, SQLERRM);
3014 IF ( FND_LOG.level_error >= fnd_log.g_current_runtime_level) then
3015 FND_LOG.string(log_level => FND_LOG.level_error,
3016 module => l_pkg_name || '.' || l_api_name,
3017 message => 'Negotiation ' || p_auction_header_id || ' and line ' || p_auction_line_number || ' not found. ' || SQLERRM);
3018 END IF;
3019 END IF;
3020 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count,
3021 p_data => x_msg_data);
3022 return;
3023 end;
3024
3025 x_return_status := fnd_api.g_ret_sts_success;
3026
3027 exception
3028 when others then
3029 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3030 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3031 FND_MSG_PUB.add_exc_msg(l_pkg_name, l_api_name, SQLERRM);
3032 IF (FND_LOG.level_unexpected >= fnd_log.g_current_runtime_level) then
3033 FND_LOG.string(log_level => FND_LOG.level_unexpected,
3034 module => l_pkg_name ||'.'|| l_api_name,
3035 message => SQLERRM);
3036 END IF;
3037 END IF;
3038 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count,
3039 p_data => x_msg_data);
3040 end get_display_line_number;
3041
3042
3043 /* ======================================================================
3044 PROCEDURE : get_display_line_number PUBLIC
3045 PARAMETERS:
3046
3047 p_document_number IN NUMBER Auction_header_id in PON_AUCTION_HEADERS_ALL
3048 p_interface_id IN NUMBER interface id
3049 p_from_line_number IN Line number from whcih the validation has to start
3050 p_to_line_number IN Line number till whcih the validation has to be done
3051
3052 COMMENT:
3053 Adds catalog to the lines that are in the range p_from_line_number to
3054 p_to_line_number (inclusive)
3055 ====================================================================== */
3056
3057 PROCEDURE Add_Catalog_Descriptors (p_document_number IN NUMBER,
3058 p_interface_id IN NUMBER,
3059 p_from_line_number IN NUMBER,
3060 p_to_line_number IN NUMBER) IS
3061 v_ip_attr_default_option VARCHAR2(10);
3062 v_default_attr_group pon_auction_attributes.attr_group%TYPE;
3063 v_max_seq_number NUMBER;
3064 v_attr_group_name fnd_lookup_values.meaning%TYPE;
3065
3066 CURSOR lines IS
3067 SELECT interface_line_number
3068 FROM pon_auc_items_interface
3069 WHERE interface_auction_header_id = p_interface_id
3070 AND interface_line_number >= p_from_line_number
3071 AND interface_line_number <= p_to_line_number;
3072
3073 BEGIN
3074
3075 v_ip_attr_default_option := fnd_profile.value('PON_IP_ATTR_DEFAULT_OPTION');
3076
3077 IF (v_ip_attr_default_option is null or v_ip_attr_default_option = 'NONE') THEN
3078 RETURN;
3079 END IF;
3080
3081 select nvl(ppp.preference_value,'GENERAL'),
3082 flv.meaning
3083 into v_default_attr_group,
3084 v_attr_group_name
3085 from pon_party_preferences ppp,
3086 fnd_lookup_values flv
3087 where ppp.app_short_name = 'PON' and
3088 ppp.preference_name = 'LINE_ATTR_DEFAULT_GROUP' and
3089 ppp.party_id = (select trading_partner_id from pon_auction_headers_all where auction_header_id = p_document_number) and
3090 flv.lookup_type = 'PON_LINE_ATTRIBUTE_GROUPS' and
3091 nvl(ppp.preference_value,'GENERAL') = flv.lookup_code and
3092 flv.view_application_id = 0 and
3093 flv.security_group_id = 0 and
3094 flv.language = nvl(g_header_rec.language_code, userenv('LANG'));
3095
3096 v_max_seq_number := 9999999999999;
3097
3098 FOR line in LINES
3099 LOOP
3100
3101 INSERT INTO PON_AUCTION_ATTRIBUTES (
3102 AUCTION_HEADER_ID,
3103 LINE_NUMBER,
3104 ATTRIBUTE_NAME,
3105 DESCRIPTION,
3106 DATATYPE,
3107 MANDATORY_FLAG,
3108 VALUE,
3109 DISPLAY_PROMPT,
3110 HELP_TEXT,
3111 DISPLAY_TARGET_FLAG,
3112 CREATION_DATE,
3113 CREATED_BY,
3114 LAST_UPDATE_DATE,
3115 LAST_UPDATED_BY,
3116 ATTRIBUTE_LIST_ID,
3117 DISPLAY_ONLY_FLAG,
3118 SEQUENCE_NUMBER,
3119 COPIED_FROM_CAT_FLAG,
3120 WEIGHT,
3121 SCORING_TYPE,
3122 ATTR_LEVEL,
3123 ATTR_GROUP,
3124 SECTION_NAME,
3125 ATTR_MAX_SCORE,
3126 INTERNAL_ATTR_FLAG,
3127 ATTR_GROUP_SEQ_NUMBER,
3128 ATTR_DISP_SEQ_NUMBER,
3129 MODIFIED_FLAG,
3130 MODIFIED_DATE,
3131 LAST_AMENDMENT_UPDATE,
3132 IP_CATEGORY_ID,
3133 IP_DESCRIPTOR_ID
3134 )
3135 SELECT
3136 P_DOCUMENT_NUMBER, -- AUCTION_HEADER_ID
3137 line.interface_line_number, -- LINE_NUMBER
3138 ATTRIBUTE_NAME, -- ATTRIBUTE_NAME
3139 null, -- DESCRIPTION
3140 DATATYPE, -- DATATYPE
3141 'N', -- MANDATORY_FLAG
3142 VALUE, -- VALUE
3143 null, -- DISPLAY_PROMPT
3144 null, -- HELP_TEXT
3145 'N', -- DISPLAY_TARGET_FLAG
3146 SYSDATE, -- CREATION_DATE
3147 g_header_rec.user_id, -- CREATED_BY
3148 SYSDATE, -- LAST_UPDATE_DATE
3149 g_header_rec.user_id, -- LAST_UPDATED_BY
3150 -1, -- ATTRIBUTE_LIST_ID
3151 'N', -- DISPLAY_ONLY_FLAG
3152 (ROWNUM*10), -- SEQUENCE_NUMBER
3153 null, -- COPIED_FROM_CAT_FLAG
3154 null, -- WEIGHT
3155 null, -- SCORING_TYPE
3156 'LINE', -- ATTR_LEVEL
3157 v_default_attr_group, -- ATTR_GROUP
3158 v_attr_group_name, -- SECTION_NAME
3159 null, -- ATTR_MAX_SCORE
3160 'N', -- INTERNAL_ATTR_FLAG
3161 10, -- ATTR_GROUP_SEQ_NUMBER
3162 (ROWNUM*10), -- ATTR_DISP_SEQ_NUMBER
3163 null, -- MODIFIED_FLAG
3164 null, -- MODIFIED_DATE
3165 null, -- LAST_AMENDMENT_UPDATE
3166 IP_CATEGORY_ID, -- IP_CATEGORY_ID
3167 IP_DESCRIPTOR_ID -- IP_DESCRIPTOR_ID
3168 FROM
3169 (SELECT attribute_name, datatype, value, ip_category_id, ip_descriptor_id
3170 FROM pon_attributes_interface
3171 WHERE interface_auction_header_id = p_interface_id AND
3172 interface_line_number = line.interface_line_number AND
3173 ((ip_category_id = 0 and v_ip_attr_default_option in ('ALL', 'BASE')) or
3174 (ip_category_id <> 0 and v_ip_attr_default_option in ('ALL', 'CATEGORY')))
3175 ORDER BY nvl(interface_sequence_number, v_max_seq_number) asc);
3176
3177 END LOOP;
3178
3179 END Add_Catalog_Descriptors;
3180
3181
3182 /* ======================================================================
3183 PROCEDURE : INITIALISE_GLOBALS PRIVATE
3184 PARAMETERS:
3185 p_interface_id IN interface id for data to convert
3186
3187 COMMENT:
3188 This procedure is used to initialise the global variables.
3189 ====================================================================== */
3190
3191 PROCEDURE INITIALISE_GLOBALS(p_interface_id IN NUMBER)
3192 is
3193 BEGIN
3194
3195 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3196 fnd_log.string(fnd_log.level_statement,
3197 'pon.plsql.pon_sourcing_openapi_grp.initialise_globals',
3198 'Entered the procedure; initialising g_header_rec');
3199 end if;
3200
3201 -- Read data into header record for convenience
3202 SELECT * INTO g_header_rec
3203 FROM pon_auc_headers_interface
3204 WHERE interface_auction_header_id = p_interface_id;
3205
3206 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3207 fnd_log.string(fnd_log.level_statement,
3208 'pon.plsql.pon_sourcing_openapi_grp.initialise_globals',
3209 'initialised g_header_rec; Entered the procedure');
3210 end if;
3211
3212 END INITIALISE_GLOBALS;
3213
3214
3215 --PROCEDURE FOR RENEGOTIATING SUPER LARGE NEGOTIATIONS
3216 --This procedure will be called by the concurrent
3217 --manager. This inturn calls the create_draft_neg_interface_pvt
3218 --procedure with p_is_conc_call = 'Y'
3219
3220
3221 PROCEDURE PON_RENEG_SUPER_LARGE_NEG (
3222 EFFBUF OUT NOCOPY VARCHAR2,
3223 RETCODE OUT NOCOPY VARCHAR2,
3224 p_interface_id IN NUMBER,
3225 p_auction_header_id IN NUMBER,
3226 p_user_name IN VARCHAR2
3227 )
3228 is
3229 l_document_number NUMBER := null;
3230 l_document_url VARCHAR2(240) := null;
3231 l_result VARCHAR2(240) := null;
3232 l_error_code VARCHAR2(240) := null;
3233 l_error_message VARCHAR2(240) := null;
3234 l_request_id NUMBER;
3235 dummy NUMBER;
3236 l_message_suffix varchar2(1);
3237
3238 BEGIN
3239 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3240 fnd_log.string(fnd_log.level_statement,
3241 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
3242 'This is a concurrent program; Entered the procedure');
3243 end if;
3244
3245 --set the message_suffix
3246 IF (g_header_rec.neg_type = PON_WF_UTL_PKG.SRC_AUCTION) THEN
3247 --if it is an auction then the suffix is _B
3248 l_message_suffix := 'B';
3249 ELSE
3250 --it is an RFQ
3251 l_message_suffix := 'R';
3252 END IF;
3253
3254 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3255
3256 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3257 fnd_log.string(fnd_log.level_statement,
3258 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
3259 'The request Id of this concurrent process is ' || l_request_id ||'; Now calling create_draft_neg_interface_pvt');
3260 end if;
3261
3262 create_draft_neg_interface_pvt (
3263 p_interface_id => p_interface_id,
3264 p_is_concurrent_call => 'Y',
3265 p_document_number => p_auction_header_id,
3266 x_document_number => l_document_number,
3267 x_document_url => l_document_url,
3268 x_request_id => dummy,
3269 x_result => l_result,
3270 x_error_code => l_error_code,
3271 x_error_message => l_error_message
3272 );
3273 IF (l_result <> 'SUCCESS') THEN
3274 RETCODE := '2' ;
3275
3276 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3277 fnd_log.string(fnd_log.level_statement,
3278 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
3279 l_error_message);
3280 end if;
3281
3282 ELSE
3283
3284 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3285 fnd_log.string(fnd_log.level_statement,
3286 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
3287 'purging the tables; calling PON_SOURCING_OPENAPI_GRP.PURGE_INTERFACE_TABLE()');
3288 end if;
3289
3290 g_call_purge := true;
3291 PURGE_INTERFACE_TABLE (
3292 p_interface_id => p_interface_id,
3293 x_result => l_result,
3294 x_error_code => l_error_code,
3295 x_error_message => l_error_message);
3296 g_call_purge := false;
3297 IF (l_result <> 'SUCCESS') THEN
3298 RETCODE := '2' ;
3299
3300 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3301 fnd_log.string(fnd_log.level_statement,
3302 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
3303 l_error_message);
3304 end if;
3305
3306 END IF;
3307
3308 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3309 fnd_log.string(fnd_log.level_statement,
3310 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
3311 'Notifying SUCCESS');
3312 end if;
3313
3314 PON_WF_UTL_PKG.ReportConcProgramStatus (
3315 p_request_id => l_request_id,
3316 p_messagetype => 'S',
3317 p_RecepientUsername => p_user_name,
3318 p_recepientType => 'BUYER',
3319 p_auction_header_id => p_auction_header_id,
3320 p_ProgramTypeCode => 'NEG_RENEGOTIATE',
3321 p_DestinationPageCode => 'PON_MANAGE_DRAFT_NEG',
3322 p_bid_number => NULL);
3323
3324 --
3325 --Don't clear the request_id as the status of the
3326 --concurrent program has to be shown to the user
3327 --
3328 --Ref: ECO - 4517992
3329 --
3330
3331 /* if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3332 fnd_log.string(fnd_log.level_statement,
3333 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
3334 'Clearing request_id in pon_auction_headers_all');
3335 end if;
3336
3337 update pon_auction_headers_all
3338 set request_id = null
3339 where auction_header_id = p_auction_header_id;
3340 */
3341 RETCODE := '0';
3342
3343 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3344 fnd_log.string(fnd_log.level_statement,
3345 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
3346 'Cleared request_id in pon_auction_headers_all for auction_header_id : ' || p_auction_header_id ||' ; returning');
3347 end if;
3348
3349 Commit;
3350
3351 END IF;
3352
3353 IF (RETCODE <> '0') THEN
3354 PON_WF_UTL_PKG.ReportConcProgramStatus (
3355 p_request_id => l_request_id,
3356 p_messagetype => 'E',
3357 p_RecepientUsername => p_user_name,
3358 p_recepientType => 'BUYER',
3359 p_auction_header_id => p_auction_header_id,
3360 p_ProgramTypeCode => 'NEG_RENEGOTIATE',
3361 p_DestinationPageCode => 'PON_MANAGE_DRAFT_NEG',
3362 p_bid_number => NULL);
3363 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3364 fnd_log.string(fnd_log.level_statement,
3365 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
3366 'Notifying FAILURE');
3367 end if;
3368
3369 END IF;
3370
3371
3372
3373 EXCEPTION
3374 WHEN OTHERS THEN
3375
3376 --when an unexpected exception arises in the COPY_NEGOTIATION
3377 --procedure, we need to do the following
3378
3379 --rollback the transactions
3380 rollback;
3381
3382 --report error to the user
3383
3384 PON_WF_UTL_PKG.ReportConcProgramStatus (
3385 p_request_id => l_request_id,
3386 p_messagetype => 'E',
3387 p_RecepientUsername => p_user_name,
3388 p_recepientType => 'BUYER',
3389 p_auction_header_id => p_auction_header_id,
3390 p_ProgramTypeCode => 'NEG_RENEGOTIATE',
3391 p_DestinationPageCode => 'PON_MANAGE_DRAFT_NEG',
3392 p_bid_number => NULL);
3393
3394 --insert into interface errors table
3395
3396 insert into pon_interface_errors (
3397 ERROR_MESSAGE_NAME,
3398 request_id,
3399 auction_header_id,
3400 application_short_name,
3401 token1_name,
3402 token1_value,
3403 token2_name,
3404 token2_value,
3405 created_by,
3406 creation_date,
3407 last_updated_by,
3408 last_update_date,
3409 last_update_login,
3410 expiration_date
3411 )
3412 values(
3413 'PON_RENEG_ERROR_MSG_'||l_message_suffix,
3414 l_request_id,
3415 p_auction_header_id,
3416 'PON',
3417 'DOC_NUM',
3418 p_auction_header_id,
3419 'REQUEST_ID',
3420 l_request_id,
3421 g_header_rec.user_id,
3422 SYSDATE,
3423 g_header_rec.user_id,
3424 SYSDATE,
3425 fnd_global.login_id,
3426 sysdate + 7);
3427
3428 if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
3429 fnd_log.string(fnd_log.level_statement,
3430 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
3431 'Notifying FAILURE');
3432 end if;
3433
3434 --set the return code
3435 RETCODE := '2';
3436
3437 --commit
3438 COMMIT;
3439
3440 END;
3441
3442 PROCEDURE maintain_clm_relations(
3443 p_interface_id IN NUMBER,
3444 x_result OUT NOCOPY VARCHAR2,
3445 x_error_code OUT NOCOPY VARCHAR2,
3446 x_error_message OUT NOCOPY VARCHAR2
3447 ) IS
3448
3449
3450 CURSOR interface_lines IS
3451 SELECT interface_line_number, source_line_id
3452 FROM pon_auc_items_interface
3453 WHERE interface_auction_header_id = p_interface_id;
3454
3455
3456 BEGIN
3457 -- logme('in maintain_clm_relations');
3458
3459 FOR oneline IN interface_lines
3460 LOOP
3461 UPDATE pon_auc_items_interface
3462 SET group_line_id = oneline.interface_line_number
3463 WHERE interface_auction_header_id = p_interface_id
3464 AND group_line_id = oneline.source_line_id;
3465
3466 UPDATE pon_auc_items_interface
3467 SET clm_base_line_num = oneline.interface_line_number
3468 WHERE interface_auction_header_id = p_interface_id
3469 AND clm_base_line_num = oneline.source_line_id;
3470
3471
3472 END LOOP;
3473
3474 EXCEPTION
3475 WHEN NO_DATA_FOUND THEN
3476 x_result := FND_API.G_RET_STS_SUCCESS;
3477 x_error_code := null;
3478 x_error_message := NULL;
3479 -- logme('in NO_DATA_FOUND');
3480
3481 RETURN;
3482
3483 WHEN OTHERS THEN
3484 x_result := FND_API.G_RET_STS_ERROR ;
3485 x_error_code := 'UNKNOWN_ERROR';
3486 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
3487 fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
3488 fnd_message.set_token('PROCEDURE','maintain_clm_relations');
3489 fnd_message.set_token('ERROR', ' [' || SQLERRM || ']');
3490 --APP_EXCEPTION.RAISE_EXCEPTION;
3491 fnd_message.retrieve(x_error_message);
3492 -- logme('in OTHERS' || x_error_message);
3493
3494 RETURN;
3495
3496 END maintain_clm_relations;
3497
3498
3499 END PON_SOURCING_OPENAPI_GRP;