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