[Home] [Help]
PACKAGE BODY: APPS.PON_VALIDATE_PAYMENTS_INT
Source
1 PACKAGE BODY pon_validate_payments_int as
2 -- $Header: PONVAPIB.pls 120.24.12020000.2 2013/05/31 07:20:24 sgulkota ship $
3
4 -- These will be used for debugging the code
5 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_VALIDATE_PAYMENTS_INT';
7 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || g_pkg_name || '.';
8
9
10 /** =============Start declaration of private functions and procedures =========*/
11 PROCEDURE print_debug_log(p_module IN VARCHAR2,
12 p_message IN VARCHAR2);
13
14 PROCEDURE print_error_log(p_module IN VARCHAR2,
15 p_message IN VARCHAR2);
16
17 PROCEDURE validate_response (p_spreadsheet_type VARCHAR2, p_batch_Id NUMBER, p_bid_number NUMBER, p_auction_header_id NUMBER, p_request_id NUMBER) IS
18 l_userid NUMBER;
19 l_loginid NUMBER;
20 l_exp_date DATE;
21 l_interface_type VARCHAR2(15);
22 l_module CONSTANT VARCHAR2(32) := 'VALIDATE_RESPONSE';
23 l_progress varchar2(200);
24 l_entity_name PON_INTERFACE_ERRORS.entity_message_code%TYPE;
25
26 BEGIN
27
28 l_userid := fnd_global.user_id;
29 l_loginid := fnd_global.login_id;
30 l_exp_date := SYSDATE + 7;
31 l_interface_type := 'BIDPYMTUPLOAD'; --Radhika-- do i need to change this for Xml upload?
32 l_entity_name := 'PON_AUC_PAYMENTS';
33
34 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
35 print_debug_log(l_module,'VALIDATE_RESPONSE START p_batch_id = '||p_batch_id
36 ||' p_bid_number = ' ||p_bid_number || ' p_auction_header_id = '|| p_auction_header_id);
37 END IF;
38
39 --Validate and remove duplicate records for payments for Xml Upload case
40 --For text based spreadsheet duplicate records are not inserted in the interface table
41
42 IF p_spreadsheet_type = PON_BID_VALIDATIONS_PKG.g_xml_upload_mode THEN
43 BEGIN
44
45 INSERT INTO PON_INTERFACE_ERRORS
46 (
47 column_name,
48 error_message_name,
49 error_value_datatype,
50 error_value_number,
51 token1_name,
52 token1_value,
53 token2_name,
54 token2_value,
55 interface_type,
56 table_name,
57 batch_id,
58 interface_line_id,
59 auction_header_id,
60 expiration_date,
61 REQUEST_ID,
62 created_by,
63 creation_date,
64 last_updated_by,
65 last_update_date,
66 last_update_login,
67 worksheet_name,
68 worksheet_sequence_number,
69 entity_message_code
70 )
71 SELECT fnd_message.get_string('PON','PON_PAY_ITEM'),
72 'PON_PYMT_NUM_NOT_UNQ',
73 'NUM',
74 pbp.payment_display_number,
75 'LINENUM',
76 pbp.document_disp_line_number,
77 'PAYITEMNUM',
78 pbp.payment_display_number,
79 l_interface_type,
80 'PON_BID_PAYMENTS_SHIPMENTS',
81 p_batch_id,
82 pbp.interface_line_id,
83 pbp.auction_header_id,
84 l_exp_date,
85 p_request_id,
86 l_userid,
87 SYSDATE,
88 l_userid,
89 SYSDATE,
90 l_loginid,
91 pbp.worksheet_name,
92 pbp.worksheet_sequence_number,
93 l_entity_name
94 FROM PON_BID_PAYMENTS_INTERFACE pbp
95 WHERE pbp.batch_id = p_batch_id
96 AND pbp.interface_line_id >
97 ( SELECT min(interface_line_id)
98 FROM PON_BID_PAYMENTS_INTERFACE pbpi2
99 WHERE pbp.document_disp_line_number = pbpi2.document_disp_line_number
100 AND pbp.payment_display_number = pbpi2.payment_display_number
101 AND pbp.batch_id = pbpi2.batch_id
102 AND pbp.interface_line_id <> pbpi2.interface_line_id);
103
104 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
105 print_debug_log(l_module,'After valiating for duplicate payitems for p_batch_id = '||p_batch_id);
106 END IF;
107
108 -- If we don't delete duplicate records Merge statement won't work
109 -- We could have validated the rest of the records and delete them before merge
110 -- but as discussed with PM Alan Ng, we will retain the behaviour of R12 for now
111
112 DELETE FROM PON_BID_PAYMENTS_INTERFACE pbpi
113 WHERE pbpi.batch_id = p_batch_id
114 AND pbpi.interface_line_id >
115 ( SELECT min(interface_line_id)
116 FROM PON_BID_PAYMENTS_INTERFACE pbpi2
117 WHERE pbpi.document_disp_line_number = pbpi2.document_disp_line_number
118 AND pbpi.payment_display_number = pbpi2.payment_display_number
119 AND pbpi.batch_id = pbpi2.batch_id
120 AND pbpi.interface_line_id <> pbpi2.interface_line_id);
121 EXCEPTION
122 WHEN OTHERS THEN
123 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
124 print_error_log(l_module, 'EXCEPTION - l_progress='||l_progress||' Error Code=' || SQLCODE || ' SQLERRM=' || SQLERRM);
125 END if;
126 END;
127
128 END IF; --End of if xml
129
130 INSERT ALL
131 WHEN supplier_can_modify_payments = 'Y' AND
132 unit_of_measure IS NOT NULL AND uom_code IS NULL THEN
133 INTO pon_interface_errors
134 (
135 column_name, entity_attr_name, error_message_name, -- 1
136 error_value_datatype, error_value, error_value_date, -- 2
137 token1_name, token1_value, token2_name, -- 3
138 token2_value, interface_type, table_name, -- 4
139 batch_id, interface_line_id, entity_type, -- 5
140 auction_header_id, line_number, bid_payment_id, -- 6
141 expiration_date, created_by, creation_date, -- 7
142 last_updated_by, last_update_date, last_update_login, -- 8
143 worksheet_name,
144 worksheet_sequence_number,
145 entity_message_code,
146 REQUEST_ID
147
148 )
149 VALUES
150 (
151 fnd_message.get_string('PON','PON_AUCTS_UOM'), NULL, 'PON_UOM_INVALID', -- 1
152 'TXT', unit_of_measure, NULL, -- 2
153 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
154 payment_display_number, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
155 p_batch_id, interface_line_id, NULL, -- 5
156 auction_header_id, auction_line_number, bid_payment_id, -- 6
157 l_exp_date, l_userid, SYSDATE, -- 7
158 l_userid, SYSDATE, l_loginid, -- 8
159 s_worksheet_name,
160 s_worksheet_sequence_number,
161 s_entity_name ,
162 p_request_id
163 )
164 WHEN p_spreadsheet_type = PON_BID_VALIDATIONS_PKG.g_txt_upload_mode AND bid_line_number IS NOT NULL and group_type IN ('GROUP','LOT_LINE') THEN
165 INTO pon_interface_errors
166 (
167 column_name, entity_attr_name, error_message_name, -- 1
168 error_value_datatype, error_value, error_value_date, -- 2
169 token1_name, token1_value, token2_name, -- 3
170 token2_value, interface_type, table_name, -- 4
171 batch_id, interface_line_id, entity_type, -- 5
172 auction_header_id, line_number, bid_payment_id, -- 6
173 expiration_date, created_by, creation_date, -- 7
174 last_updated_by, last_update_date, last_update_login, -- 8
175 REQUEST_ID
176
177 )
178 VALUES
179 (
180 fnd_message.get_string('PON','PON_AUCTS_LINENUMBER'), NULL, 'PON_PYMT_NOTALLOWED', -- 1
181 'TXT', document_disp_line_number, NULL, -- 2
182 'LINENUM', document_disp_line_number, NULL, -- 3
183 NULL, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
184 p_batch_id, interface_line_id, NULL, -- 5
185 auction_header_id, auction_line_number, bid_payment_id, -- 6
186 l_exp_date, l_userid, SYSDATE, -- 7
187 l_userid, SYSDATE, l_loginid,
188 p_request_id
189 )
190 WHEN p_spreadsheet_type = PON_BID_VALIDATIONS_PKG.g_txt_upload_mode AND bid_line_number IS NULL THEN
191 INTO pon_interface_errors
192 (
193 column_name, entity_attr_name, error_message_name, -- 1
194 error_value_datatype, error_value, error_value_date, -- 2
195 token1_name, token1_value, token2_name, -- 3
196 token2_value, interface_type, table_name, -- 4
197 batch_id, interface_line_id, entity_type, -- 5
198 auction_header_id, line_number, bid_payment_id, -- 6
199 expiration_date, created_by, creation_date, -- 7
200 last_updated_by, last_update_date, last_update_login,
201 REQUEST_ID
202 )
203 VALUES
204 (
205 fnd_message.get_string('PON','PON_AUCTS_LINENUMBER'), NULL, 'PON_INVALID_LINE_NUM', -- 1
206 'TXT', document_disp_line_number, NULL, -- 2
207 NULL, NULL, NULL, -- 3
208 NULL, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
209 p_batch_id, interface_line_id, NULL, -- 5
210 auction_header_id, auction_line_number, bid_payment_id, -- 6
211 l_exp_date, l_userid, SYSDATE, -- 7
212 l_userid, SYSDATE, l_loginid,
213 p_request_id
214 )
215 -- For GOODS based line only MILESTONE payments should be allowed. Otherwise throw an error
216 -- If the payment type entered in the spreadsheet is not from a fnd lookup then throw an error
217 -- If pay item entered is not supported by the purchasing style then throw an error.
218
219 WHEN supplier_can_modify_payments = 'Y' AND
220 ((line_type = 'GOODS' AND lookup_payment_type_code <> 'MILESTONE') OR
221 (payment_type IS NOT NULL AND lookup_payment_type_code IS NULL) OR
222 (lookup_payment_type_code IS NOT NULL AND lookup_payment_type_code NOT IN
223 (SELECT pay_item_type FROM PO_STYLE_ENABLED_PAY_ITEMS WHERE style_id = po_style_id))) THEN
224 INTO pon_interface_errors
225 (
226 column_name,
227 entity_attr_name, error_message_name, -- 1
228 error_value_datatype, error_value, error_value_date, -- 2
229 token1_name, token1_value, token2_name, -- 3
230 token2_value, interface_type, table_name, -- 4
231 batch_id, interface_line_id, entity_type, -- 5
232 auction_header_id, line_number, bid_payment_id, -- 6
233 expiration_date, created_by, creation_date, -- 7
234 last_updated_by, last_update_date, last_update_login, -- 8
235 worksheet_name,
236 worksheet_sequence_number,
237 entity_message_code,
238 REQUEST_ID
239 )
240 VALUES
241 (
242 decode(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_txt_upload_mode, fnd_message.get_string('PON','PON_AUCTS_PAYITEM_TYPE'), fnd_message.get_string('PON','PON_AUCTS_TYPE')),
243 NULL, 'PON_PYMT_TYPE_INVALID', -- 1
244 'TXT', payment_type, NULL, -- 2
245 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
246 payment_display_number, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
247 p_batch_id, interface_line_id, NULL, -- 5
248 auction_header_id, auction_line_number, bid_payment_id, -- 6
249 l_exp_date, l_userid, SYSDATE, -- 7
250 l_userid, SYSDATE, l_loginid, -- 8
251 s_worksheet_name,
252 s_worksheet_sequence_number,
253 s_entity_name,
254 p_request_id
255 )
256 WHEN payment_display_number < 1 OR payment_display_number<> ROUND(payment_display_number) THEN
257 INTO pon_interface_errors
258 (
259 column_name,
260 entity_attr_name, error_message_name, -- 1
261 error_value_datatype, error_value, error_value_date, -- 2
262 token1_name, token1_value, token2_name, -- 3
263 token2_value, interface_type, table_name, -- 4
264 batch_id, interface_line_id, entity_type, -- 5
265 auction_header_id, line_number, bid_payment_id, -- 6
266 expiration_date, created_by, creation_date, -- 7
267 last_updated_by, last_update_date, last_update_login, -- 8
268 worksheet_name,
269 worksheet_sequence_number,
270 entity_message_code,
271 REQUEST_ID
272 )
273 VALUES
274 (
275 decode(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_txt_upload_mode, fnd_message.get_string('PON','PON_AUCTS_PAYITEM_NUMBER'), fnd_message.get_string('PON','PON_PAY_ITEM')),
276 NULL, 'PON_PYMT_NUM_WRONG', -- 1
277 'TXT', payment_display_number, NULL, -- 2
278 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
279 payment_display_number, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
280 p_batch_id, interface_line_id, NULL, -- 5
281 auction_header_id, auction_line_number, bid_payment_id, -- 6
282 l_exp_date, l_userid, SYSDATE, -- 7
283 l_userid, SYSDATE, l_loginid, -- 8
284 s_worksheet_name,
285 s_worksheet_sequence_number,
286 s_entity_name,
287 p_request_id
288 )
289 WHEN supplier_can_modify_payments = 'Y' AND
290 lookup_payment_type_code = 'RATE' AND quantity < 0 THEN
291 INTO pon_interface_errors
292 (
293 column_name, entity_attr_name, error_message_name, -- 1
294 error_value_datatype, error_value_number, error_value_date, -- 2
295 token1_name, token1_value, token2_name, -- 3
296 token2_value, interface_type, table_name, -- 4
297 batch_id, interface_line_id, entity_type, -- 5
298 auction_header_id, line_number, bid_payment_id, -- 6
299 expiration_date, created_by, creation_date, -- 7
300 last_updated_by, last_update_date, last_update_login, -- 8
301 worksheet_name,
302 worksheet_sequence_number,
303 entity_message_code,
304 REQUEST_ID
305 )
306 VALUES
307 (
308 fnd_message.get_string('PON','PON_AUCTS_BID_QTY_R'), NULL, 'PON_PYMT_QTY_WRONG', -- 1
309 'NUM', quantity, NULL, -- 2
310 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
311 payment_display_number, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
312 p_batch_id, interface_line_id, NULL, -- 5
313 auction_header_id, auction_line_number, bid_payment_id, -- 6
314 l_exp_date, l_userid, SYSDATE, -- 7
315 l_userid, SYSDATE, l_loginid, -- 8
316 s_worksheet_name,
317 s_worksheet_sequence_number,
318 s_entity_name,
319 p_request_id
320 )
321 WHEN supplier_can_modify_payments = 'Y' AND
322 lookup_payment_type_code = 'RATE' AND unit_of_measure IS NULL THEN
323 INTO pon_interface_errors
324 (
325 column_name, entity_attr_name, error_message_name, -- 1
326 error_value_datatype, error_value, error_value_date, -- 2
327 token1_name, token1_value, token2_name, -- 3
328 token2_value, interface_type, table_name, -- 4
329 batch_id, interface_line_id, entity_type, -- 5
330 auction_header_id, line_number, bid_payment_id, -- 6
331 expiration_date, created_by, creation_date, -- 7
332 last_updated_by, last_update_date, last_update_login, -- 8
333 worksheet_name,
334 worksheet_sequence_number,
335 entity_message_code,
336 REQUEST_ID
337 )
338 VALUES
339 (
340 fnd_message.get_string('PON','PON_AUCTS_UOM'), NULL, 'PON_PYMT_UOM_NULL', -- 1
341 'TXT', NULL, NULL, -- 2
342 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
343 payment_display_number, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
344 p_batch_id, interface_line_id, NULL, -- 5
345 auction_header_id, auction_line_number, bid_payment_id, -- 6
346 l_exp_date, l_userid, SYSDATE, -- 7
347 l_userid, SYSDATE, l_loginid, -- 8
348 s_worksheet_name,
349 s_worksheet_sequence_number,
350 s_entity_name,
351 p_request_id
352 )
353 WHEN bid_currency_price IS NOT NULL AND bid_currency_price < 0 THEN
354 INTO pon_interface_errors
355 (
356 column_name, entity_attr_name, error_message_name, -- 1
357 error_value_datatype, error_value_number, error_value_date, -- 2
358 token1_name, token1_value, token2_name, -- 3
359 token2_value, interface_type, table_name, -- 4
360 batch_id, interface_line_id, entity_type, -- 5
361 auction_header_id, line_number, bid_payment_id, -- 6
362 expiration_date, created_by, creation_date, -- 7
363 last_updated_by, last_update_date, last_update_login, -- 8
364 worksheet_name,
365 worksheet_sequence_number,
366 entity_message_code,
367 REQUEST_ID
368 )
369 VALUES
370 (
371 fnd_message.get_string('PON','PON_AUCTS_BID_PRICE_R'), NULL, 'PON_PYMT_PRICE_WRONG', -- 1
372 'NUM', bid_currency_price, NULL, -- 2
373 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
374 payment_display_number, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
375 p_batch_id, interface_line_id, NULL, -- 5
376 auction_header_id, auction_line_number, bid_payment_id, -- 6
377 l_exp_date, l_userid, SYSDATE, -- 7
378 l_userid, SYSDATE, l_loginid, -- 8
379 s_worksheet_name,
380 s_worksheet_sequence_number,
381 s_entity_name,
382 p_request_id
383 )
384 WHEN supplier_can_modify_payments = 'Y' AND
385 lookup_payment_type_code = 'RATE' AND quantity IS NULL THEN
386 INTO pon_interface_errors
387 (
388 column_name, entity_attr_name, error_message_name, -- 1
389 error_value_datatype, error_value, error_value_date, -- 2
390 token1_name, token1_value, token2_name, -- 3
391 token2_value, interface_type, table_name, -- 4
392 batch_id, interface_line_id, entity_type, -- 5
393 auction_header_id, line_number, bid_payment_id, -- 6
394 expiration_date, created_by, creation_date, -- 7
395 last_updated_by, last_update_date, last_update_login, -- 8
396 worksheet_name,
397 worksheet_sequence_number,
398 entity_message_code,
399 REQUEST_ID
400 )
401 VALUES
402 (
403 fnd_message.get_string('PON','PON_AUCTS_BID_QTY_R'), NULL, 'PON_PYMT_QTY_NULL', -- 1
404 'TXT', NULL, NULL, -- 2
405 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
406 payment_display_number, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
407 p_batch_id, interface_line_id, NULL, -- 5
408 auction_header_id, auction_line_number, bid_payment_id, -- 6
409 l_exp_date, l_userid, SYSDATE, -- 7
410 l_userid, SYSDATE, l_loginid, -- 8
411 s_worksheet_name,
412 s_worksheet_sequence_number,
413 s_entity_name,
414 p_request_id
415 )
416 WHEN bid_currency_price IS NULL THEN
417 INTO pon_interface_errors
418 (
419 column_name, entity_attr_name, error_message_name, -- 1
420 error_value_datatype, error_value, error_value_date, -- 2
421 token1_name, token1_value, token2_name, -- 3
422 token2_value, interface_type, table_name, -- 4
423 batch_id, interface_line_id, entity_type, -- 5
424 auction_header_id, line_number, bid_payment_id, -- 6
425 expiration_date, created_by, creation_date, -- 7
426 last_updated_by, last_update_date, last_update_login, -- 8
427 worksheet_name,
428 worksheet_sequence_number,
429 entity_message_code,
430 REQUEST_ID
431 )
432 VALUES
433 (
434 fnd_message.get_string('PON','PON_AUCTS_BID_PRICE_R'), NULL, 'PON_PYMT_BID_PRICE_NULL', -- 1
435 'TXT', NULL, NULL, -- 2
436 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
437 payment_display_number, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
438 p_batch_id, interface_line_id, NULL, -- 5
439 auction_header_id, auction_line_number, bid_payment_id, -- 6
440 l_exp_date, l_userid, SYSDATE, -- 7
441 l_userid, SYSDATE, l_loginid, -- 8
442 s_worksheet_name,
443 s_worksheet_sequence_number,
444 s_entity_name,
445 p_request_id
446 )
447 WHEN bid_currency_price IS NOT NULL
448 AND PON_BID_VALIDATIONS_PKG.validate_price_precision(
449 bid_currency_price, pbh_price_precision) = 'F' THEN
450 INTO pon_interface_errors
451 (
452 column_name, entity_attr_name, error_message_name, -- 1
453 error_value_datatype, error_value_number, error_value_date, -- 2
454 token1_name, token1_value, token2_name, -- 3
455 token2_value, interface_type, table_name, -- 4
456 batch_id, interface_line_id, entity_type, -- 5
457 auction_header_id, line_number, bid_payment_id, -- 6
458 expiration_date, created_by, creation_date, -- 7
459 last_updated_by, last_update_date, last_update_login, -- 8
460 worksheet_name,
461 worksheet_sequence_number,
462 entity_message_code,
463 REQUEST_ID
464 )
465 VALUES
466 (
467 fnd_message.get_string('PON','PON_AUCTS_BID_PRICE_R'), NULL, 'PON_QUOTEPRICE_INVALID_PREC_L', -- 1
468 'NUM', bid_currency_price, NULL, -- 2
469 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
470 payment_display_number, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
471 p_batch_id, interface_line_id, NULL, -- 5
472 auction_header_id, auction_line_number, bid_payment_id, -- 6
473 l_exp_date, l_userid, SYSDATE, -- 7
474 l_userid, SYSDATE, l_loginid, -- 8
475 s_worksheet_name,
476 s_worksheet_sequence_number,
477 s_entity_name,
478 p_request_id
479 )
480 WHEN promised_date IS NOT NULL AND promised_date <= close_bidding_date THEN
481 INTO pon_interface_errors
482 (
483 column_name, entity_attr_name, error_message_name, -- 1
484 error_value_datatype, error_value_number, error_value_date, -- 2
485 token1_name, token1_value, token2_name, -- 3
486 token2_value, interface_type, table_name, -- 4
487 batch_id, interface_line_id, entity_type, -- 5
488 auction_header_id, line_number, bid_payment_id, -- 6
489 expiration_date, created_by, creation_date, -- 7
490 last_updated_by, last_update_date, last_update_login, -- 8
491 worksheet_name,
492 worksheet_sequence_number,
493 entity_message_code,
494 REQUEST_ID
495 )
496 VALUES
497 (
498 fnd_message.get_string('PON','PON_AUCTS_PROMISED_DATE'), NULL, 'PON_PYMT_PDATE_LESS_CDATE', -- 1
499 'TIM', NULL, promised_date, -- 2
500 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
501 payment_display_number, l_interface_type, 'PON_BID_PAYMENTS_INTERFACE', -- 4
502 p_batch_id, interface_line_id, NULL, -- 5
503 auction_header_id, auction_line_number, bid_payment_id, -- 6
504 l_exp_date, l_userid, SYSDATE, -- 7
505 l_userid, SYSDATE, l_loginid, -- 8
506 s_worksheet_name,
507 s_worksheet_sequence_number,
508 s_entity_name,
509 p_request_id
510 )
511 SELECT
512 pah.po_style_id,
513 nvl(pah.supplier_enterable_pymt_flag,'N') supplier_can_modify_payments,
514 pbp.payment_display_number,
515 pbp.payment_type,
516 pbp.unit_of_measure,
517 pbp.interface_line_id interface_line_id,
518 pbp.auction_header_id auction_header_id,
519 pbp.document_disp_line_number,
520 pbp.bid_currency_price,
521 pbp.quantity,
522 pbp.promised_date,
523 DECODE(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, pbp.worksheet_name, NULL) s_worksheet_name,
524 DECODE(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, pbp.worksheet_sequence_number, NULL) s_worksheet_sequence_number,
525 DECODE(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, l_entity_name, NULL) s_entity_name,
526 pai.line_number auction_line_number,
527 pai.group_type,
528 pai.purchase_basis line_type,
529 pai.close_bidding_date,
530 uom.uom_code,
531 fl.lookup_code lookup_payment_type_code,
532 null bid_payment_id,
533 pbi.line_number bid_line_number,
534 fc.precision fc_precision,
535 pbh.number_price_decimals pbh_price_precision
536 FROM PON_BID_PAYMENTS_INTERFACE pbp,
537 PON_AUCTION_ITEM_PRICES_ALL pai,
538 PON_BID_ITEM_PRICES pbi,
539 PON_AUCTION_HEADERS_ALL pah,
540 MTL_UNITS_OF_MEASURE uom,
541 PO_LOOKUP_CODES fl,
542 FND_CURRENCIES fc,
543 PON_BID_HEADERS pbh
544 WHERE pbp.auction_header_id = pai.auction_header_id (+)
545 AND pbp.document_disp_line_number = pai.document_disp_line_number (+)
546 AND pbp.batch_id = p_batch_id
547 AND pbp.auction_header_id = p_auction_header_id
548 AND pbp.bid_number = p_bid_number
549 AND pah.auction_header_id(+) = pbp.auction_header_id
550 AND pbp.unit_of_measure = uom.unit_of_measure_tl(+)
551 AND uom.language (+) = userenv('LANG')
552 AND pbp.payment_type = fl.displayed_field (+)
553 AND fl.lookup_type(+) = 'PAYMENT TYPE'
554 AND pbi.auction_header_id(+) = pai.auction_header_id
555 AND pbi.line_number(+) = pai.line_number
556 AND fc.currency_code = pah.currency_code
557 AND pbh.bid_number(+) = p_bid_number
558 AND pbi.bid_number(+) = p_bid_number
559 ;
560
561 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
562 print_debug_log(l_module,'After Insert all for validate_response p_batch_id = '||p_batch_id);
563 END IF;
564
565
566 -- This validation is done in a separate sql as this one needs join to
567 -- pon_auc_payments_shipments and all the other validations don't need them.
568 INSERT INTO PON_INTERFACE_ERRORS
569 (
570 column_name, entity_attr_name, error_message_name, -- 1
571 error_value_datatype, error_value_number, error_value_date, -- 2
572 token1_name, token1_value, token2_name, -- 3
573 token2_value, interface_type, table_name, -- 4
574 batch_id, interface_line_id, entity_type, -- 5
575 auction_header_id, line_number, bid_payment_id, -- 6
576 expiration_date, created_by, creation_date, -- 7
577 last_updated_by, last_update_date, last_update_login, -- 8
578 worksheet_name,
579 worksheet_sequence_number,
580 entity_message_code,
581 REQUEST_ID
582
583 )
584 SELECT decode(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_txt_upload_mode, fnd_message.get_string('PON','PON_AUCTS_PAYITEM_NUMBER'), fnd_message.get_string('PON','PON_PAY_ITEM')),
585 NULL, 'PON_LINE_PYMT_INVALID', -- 1
586 'NUM', pbp.payment_display_number, NULL, -- 2
587 'LINENUM', pbp.document_disp_line_number, 'PAYITEMNUM', -- 3
588 pbp.payment_display_number, l_interface_type, 'PON_BID_PAYMENTS_SHIPMENTS', -- 4
589 p_batch_id, pbp.interface_line_id, NULL, -- 5
590 pbp.auction_header_id, pai.line_number, NULL, -- 6
591 l_exp_date, l_userid, SYSDATE, -- 7
592 l_userid, SYSDATE, l_loginid, -- 8
593 DECODE(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, pbp.worksheet_name, NULL),
594 DECODE(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, pbp.worksheet_sequence_number, NULL),
595 DECODE(p_spreadsheet_type, PON_BID_VALIDATIONS_PKG.g_xml_upload_mode, l_entity_name, NULL) ,
596 p_request_id
597 FROM PON_BID_PAYMENTS_INTERFACE pbp,
598 PON_AUCTION_ITEM_PRICES_ALL pai,
599 PON_AUCTION_HEADERS_ALL pah
600 WHERE pbp.document_disp_line_number = pai.document_disp_line_number
601 AND pbp.auction_header_id = pai.auction_header_id
602 AND pbp.payment_display_number NOT IN(SELECT pap.payment_display_number
603 FROM PON_AUC_PAYMENTS_SHIPMENTS pap
604 WHERE pap.auction_header_id=pbp.auction_header_id
605 AND pap.line_number=pai.line_number)
606 AND pah.auction_header_id = pbp.auction_header_id
607 AND pah.supplier_enterable_pymt_flag = 'N'
608 AND pbp.batch_id = p_batch_id;
609
610 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
611 print_debug_log(l_module,'After valiating for supplier enterable flag for p_batch_id = '||p_batch_id);
612 END IF;
613
614 EXCEPTION
615 WHEN OTHERS THEN
616 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
617 print_error_log(l_module, 'EXCEPTION - l_progress='||l_progress||' Error Code=' || SQLCODE || ' SQLERRM=' || SQLERRM);
618 END if;
619 END validate_response;
620
621 --
622 PROCEDURE validate_creation (p_source VARCHAR2, p_batch_Id NUMBER) IS
623 l_userid NUMBER;
624 l_loginid NUMBER;
625 l_exp_date DATE;
626 l_interface_type VARCHAR2(15);
627 l_module CONSTANT VARCHAR2(32) := 'VALIDATE_CREATION';
628 l_progress VARCHAR2(200);
629 CURSOR l_proj_cursor IS
630 SELECT papi.interface_line_id, papi.document_disp_line_number,
631 pro.project_id, task.task_id, porg.organization_id,
632 papi.project_expenditure_type,papi.project_expenditure_item_date,
633 papi.auction_header_id, papi.payment_display_number
634 FROM PA_PROJECTS_ALL pro,
635 PA_TASKS_EXPEND_V task,
636 HR_ALL_ORGANIZATION_UNITS porg,
637 PON_AUC_PAYMENTS_INTERFACE papi
638 WHERE papi.project_number = pro.segment1
639 AND papi.project_task_number = task.task_number
640 AND pro.project_id = task.project_id
641 AND papi.project_exp_organization_name = porg.name
642 AND papi.batch_id = p_batch_id
643 AND papi.project_number IS NOT NULL
644 AND papi.project_task_number IS NOT NULL
645 AND papi.project_expenditure_type IS NOT NULL
646 AND papi.project_exp_organization_name IS NOT NULL
647 AND papi.project_expenditure_item_date IS NOT NULL;
648
649 BEGIN
650
651 l_userid := fnd_global.user_id;
652 l_loginid := fnd_global.login_id;
653 l_exp_date := SYSDATE + 7;
654 l_interface_type := 'NEGPYMTUPLOAD';
655
656 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
657 print_debug_log(l_module,'Before insert all valiations of validate_creation for p_batch_id = '||p_batch_id);
658 END IF;
659
660 INSERT ALL
661 WHEN line_origination_code <> 'REQUISITION' AND project_number IS NOT NULL AND pro_project_id IS NULL THEN
662 INTO pon_interface_errors
663 (
664 column_name, entity_attr_name, error_message_name, -- 1
665 error_value_datatype, error_value, error_value_date, -- 2
666 token1_name, token1_value, token2_name, -- 3
667 token2_value, interface_type, table_name, -- 4
668 batch_id, interface_line_id, entity_type, -- 5
669 auction_header_id, line_number, payment_id, -- 6
670 expiration_date, created_by, creation_date, -- 7
671 last_updated_by, last_update_date, last_update_login -- 8
672 )
673 VALUES
674 (
675 fnd_message.get_string('PON','PON_AUCTS_PROJECT'), NULL, 'PON_PROJ_NUM_INVALID', -- 1
676 'TXT', Project_number, NULL, -- 2
677 'LINENUM', document_disp_line_number, NULL, -- 3
678 NULL, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
679 p_batch_id, interface_line_id, NULL, -- 5
680 auction_header_id, auction_line_number, NULL, -- 6
681 l_exp_date, l_userid, SYSDATE, -- 7
682 l_userid, SYSDATE, l_loginid -- 8
683 )
684
685 WHEN line_origination_code <> 'REQUISITION'
686 AND pro_project_id IS NOT NULL
687 AND project_task_number IS NOT NULL
688 AND NOT EXISTS (SELECT 1
689 FROM PA_TASKS_EXPEND_V task
690 WHERE task.project_id = pro_project_id AND task.task_number = project_task_number) THEN
691 INTO pon_interface_errors
692 (
693 column_name, entity_attr_name, error_message_name, -- 1
694 error_value_datatype, error_value, error_value_date, -- 2
695 token1_name, token1_value, token2_name, -- 3
696 token2_value, interface_type, table_name, -- 4
697 batch_id, interface_line_id, entity_type, -- 5
698 auction_header_id, line_number, payment_id, -- 6
699 expiration_date, created_by, creation_date, -- 7
700 last_updated_by, last_update_date, last_update_login -- 8
701 )
702 VALUES
703 (
704 fnd_message.get_string('PON','PON_AUCTS_TASK'), NULL, 'PON_PROJ_TASK_INVALID', -- 1
705 'TXT', project_task_number, NULL, -- 2
706 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
707 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
708 p_batch_id, interface_line_id, NULL, -- 5
709 auction_header_id, auction_line_number, NULL, -- 6
710 l_exp_date, l_userid, SYSDATE, -- 7
711 l_userid, SYSDATE, l_loginid -- 8
712 )
713 WHEN line_origination_code <> 'REQUISITION'
714 AND pro_project_id IS NOT NULL
715 AND project_task_number IS NOT NULL
716 AND project_award_number IS NOT NULL
717 AND NOT EXISTS (SELECT 1
718 FROM GMS_AWARDS_BASIC_V award,
719 PA_TASKS_EXPEND_V task
720 WHERE award.project_id = pro_project_id
721 AND task.task_number = project_task_number
722 AND award.task_id = task.task_id
723 AND task.project_id = pro_project_id) THEN
724 INTO pon_interface_errors
725 (
726 column_name, entity_attr_name, error_message_name, -- 1
727 error_value_datatype, error_value, error_value_date, -- 2
728 token1_name, token1_value, token2_name, -- 3
729 token2_value, interface_type, table_name, -- 4
730 batch_id, interface_line_id, entity_type, -- 5
731 auction_header_id, line_number, payment_id, -- 6
732 expiration_date, created_by, creation_date, -- 7
733 last_updated_by, last_update_date, last_update_login -- 8
734 )
735 VALUES
736 (
737 fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), NULL, 'PON_PROJ_AWARD_INVALID', -- 1
738 'TXT', project_award_number, NULL, -- 2
739 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
740 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
741 p_batch_id, interface_line_id, NULL, -- 5
742 auction_header_id, auction_line_number, NULL, -- 6
743 l_exp_date, l_userid, SYSDATE, -- 7
744 l_userid, SYSDATE, l_loginid -- 8
745 )
746 WHEN pro_project_id IS NOT NULL
747 AND project_award_number IS NULL
748 AND PON_NEGOTIATION_PUBLISH_PVT.IS_PROJECT_SPONSORED(pro_project_id) = 'Y' THEN INTO pon_interface_errors
749 (
750 column_name, entity_attr_name, error_message_name, -- 1
751 error_value_datatype, error_value, error_value_date, -- 2
752 token1_name, token1_value, token2_name, -- 3
753 token2_value, interface_type, table_name, -- 4
754 batch_id, interface_line_id, entity_type, -- 5
755 auction_header_id, line_number, payment_id, -- 6
756 expiration_date, created_by, creation_date, -- 7
757 last_updated_by, last_update_date, last_update_login -- 8
758 )
759 VALUES
760 (
761 fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), NULL, 'PON_PROJ_AWARD_NULL', -- 1
762 'TXT', project_award_number, NULL, -- 2
763 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
764 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
765 p_batch_id, interface_line_id, NULL, -- 5
766 auction_header_id, auction_line_number, NULL, -- 6
767 l_exp_date, l_userid, SYSDATE, -- 7
768 l_userid, SYSDATE, l_loginid -- 8
769 )
770 WHEN line_origination_code <> 'REQUISITION' AND project_exp_organization_name IS NOT NULL
771 AND porg_proj_exp_organization_id IS NULL THEN
772 INTO pon_interface_errors
773 (
774 column_name, entity_attr_name, error_message_name, -- 1
775 error_value_datatype, error_value, error_value_date, -- 2
776 token1_name, token1_value, token2_name, -- 3
777 token2_value, interface_type, table_name, -- 4
778 batch_id, interface_line_id, entity_type, -- 5
779 auction_header_id, line_number, payment_id, -- 6
780 expiration_date, created_by, creation_date, -- 7
781 last_updated_by, last_update_date, last_update_login -- 8
782 )
783 VALUES
784 (
785 fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_ORG'), NULL, 'PON_PROJ_EXPORG_INVALID', -- 1
786 'TXT', project_exp_organization_name,NULL, -- 2
787 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
788 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
789 p_batch_id, interface_line_id, NULL, -- 5
790 auction_header_id, auction_line_number, NULL, -- 6
791 l_exp_date, l_userid, SYSDATE, -- 7
792 l_userid, SYSDATE, l_loginid -- 8
793 )
794 WHEN line_origination_code <> 'REQUISITION'
795 AND project_expenditure_type IS NOT NULL
796 AND NOT EXISTS (SELECT 1
797 FROM pa_expenditure_types_expend_v exptype
798 WHERE system_linkage_function = 'VI'
799 AND exptype.expenditure_type = project_expenditure_type
800 AND trunc(sysdate) BETWEEN nvl(exptype.expnd_typ_start_date_active, trunc(sysdate))
801 AND nvl(exptype.expnd_typ_end_date_Active, trunc(sysdate))
802 AND trunc(sysdate) BETWEEN nvl(exptype.sys_link_start_date_active, trunc(sysdate))
803 AND nvl(exptype.sys_link_end_date_Active, trunc(sysdate))) THEN
804 INTO pon_interface_errors
805 (
806 column_name, entity_attr_name, error_message_name, -- 1
807 error_value_datatype, error_value, error_value_date, -- 2
808 token1_name, token1_value, token2_name, -- 3
809 token2_value, interface_type, table_name, -- 4
810 batch_id, interface_line_id, entity_type, -- 5
811 auction_header_id, line_number, payment_id, -- 6
812 expiration_date, created_by, creation_date, -- 7
813 last_updated_by, last_update_date, last_update_login -- 8
814 )
815 VALUES
816 (
817 fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_TYPE'), NULL, 'PON_PROJ_EXPTYPE_INVALID', -- 1
818 'TXT', project_expenditure_type,NULL, -- 2
819 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
820 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
821 p_batch_id, interface_line_id, NULL, -- 5
822 auction_header_id, auction_line_number, NULL, -- 6
823 l_exp_date, l_userid, SYSDATE, -- 7
824 l_userid, SYSDATE, l_loginid -- 8
825 )
826 WHEN line_origination_code <> 'REQUISITION' AND
827 (project_number IS NOT NULL OR project_task_number IS NOT NULL OR project_exp_organization_name IS NOT NULL
828 OR project_expenditure_item_date IS NOT NULL OR project_expenditure_type IS NOT NULL)
829 AND (project_number IS NULL OR project_task_number IS NULL OR project_exp_organization_name IS NULL
830 OR project_expenditure_item_date IS NULL OR project_expenditure_type IS NULL) THEN
831 INTO pon_interface_errors
832 (
833 column_name, entity_attr_name, error_message_name, -- 1
834 error_value_datatype, error_value, error_value_date, -- 2
835 token1_name, token1_value, token2_name, -- 3
836 token2_value, interface_type, table_name, -- 4
837 batch_id, interface_line_id, entity_type, -- 5
838 auction_header_id, line_number, payment_id, -- 6
839 expiration_date, created_by, creation_date, -- 7
840 last_updated_by, last_update_date, last_update_login -- 8
841 )
842 VALUES
843 (
844 fnd_message.get_string('PON','PON_AUCTS_PROJECT'), NULL, 'PON_PROJ_INFO_INCOMPLETE_P', -- 1
845 'TXT', NULL, NULL, -- 2
846 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
847 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
848 p_batch_id, interface_line_id, NULL, -- 5
849 auction_header_id, auction_line_number, NULL, -- 6
850 l_exp_date, l_userid, SYSDATE, -- 7
851 l_userid, SYSDATE, l_loginid -- 8
852 )
853 WHEN ship_to_location_code IS NOT NULL AND ship_to_location_id IS NULL THEN
854 INTO pon_interface_errors
855 (
856 column_name, entity_attr_name, error_message_name, -- 1
857 error_value_datatype, error_value, error_value_date, -- 2
858 token1_name, token1_value, token2_name, -- 3
859 token2_value, interface_type, table_name, -- 4
860 batch_id, interface_line_id, entity_type, -- 5
861 auction_header_id, line_number, payment_id, -- 6
862 expiration_date, created_by, creation_date, -- 7
863 last_updated_by, last_update_date, last_update_login -- 8
864 )
865 VALUES
866 (
867 fnd_message.get_string('PON','PON_AUCTS_SHIPTO'), NULL, 'PONPYMT_SHIPTO_INVALID', -- 1
868 'TXT', ship_to_location_code, NULL, -- 2
869 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
870 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
871 p_batch_id, interface_line_id, NULL, -- 5
872 auction_header_id, auction_line_number, NULL, -- 6
873 l_exp_date, l_userid, SYSDATE, -- 7
874 l_userid, SYSDATE, l_loginid -- 8
875 )
876 WHEN unit_of_measure IS NOT NULL AND uom_uom_code IS NULL THEN
877 INTO pon_interface_errors
878 (
879 column_name, entity_attr_name, error_message_name, -- 1
880 error_value_datatype, error_value, error_value_date, -- 2
881 token1_name, token1_value, token2_name, -- 3
882 token2_value, interface_type, table_name, -- 4
883 batch_id, interface_line_id, entity_type, -- 5
884 auction_header_id, line_number, payment_id, -- 6
885 expiration_date, created_by, creation_date, -- 7
886 last_updated_by, last_update_date, last_update_login -- 8
887 )
888 VALUES
889 (
890 fnd_message.get_string('PON','PON_AUCTS_UOM'), NULL, 'PON_UOM_INVALID', -- 1
891 'TXT', unit_of_measure, NULL, -- 2
892 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
893 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
894 p_batch_id, interface_line_id, NULL, -- 5
895 auction_header_id, auction_line_number, NULL, -- 6
896 l_exp_date, l_userid, SYSDATE, -- 7
897 l_userid, SYSDATE, l_loginid -- 8
898 )
899 WHEN work_approver_user_name IS NOT NULL
900 AND NOT EXISTS (SELECT 1
901 FROM PER_WORKFORCE_CURRENT_X peo,
902 FND_USER fu
903 WHERE fu.user_name = work_approver_user_name
904 AND fu.employee_id = peo.person_id
905 AND SYSDATE >= nvl(fu.start_date, SYSDATE)
906 AND SYSDATE <= nvl(fu.end_date, SYSDATE) ) THEN
907 INTO pon_interface_errors
908 (
909 column_name, entity_attr_name, error_message_name, -- 1
910 error_value_datatype, error_value, error_value_date, -- 2
911 token1_name, token1_value, token2_name, -- 3
912 token2_value, interface_type, table_name, -- 4
913 batch_id, interface_line_id, entity_type, -- 5
914 auction_header_id, line_number, payment_id, -- 6
915 expiration_date, created_by, creation_date, -- 7
916 last_updated_by, last_update_date, last_update_login -- 8
917 )
918 VALUES
919 (
920 fnd_message.get_string('PON','PON_AUCTS_OWNER'), NULL, 'PON_PYMT_OWNER_INVALID', -- 1
921 'TXT', work_approver_user_name, NULL, -- 2
922 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
923 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
924 p_batch_id, interface_line_id, NULL, -- 5
925 auction_header_id, auction_line_number, NULL, -- 6
926 l_exp_date, l_userid, SYSDATE, -- 7
927 l_userid, SYSDATE, l_loginid -- 8
928 )
929 -- For GOODS based line only MILESTONE payments should be allowed. Otherwise throw an error
930 -- If the payment type entered in the spreadsheet is not from a fnd lookup then throw an error
931 -- If pay item entered is not supported by the purchasing style then throw an error.
932
933 WHEN ((line_type = 'GOODS' AND lookup_payment_type_code <> 'MILESTONE') OR
934 (payment_type IS NOT NULL AND lookup_payment_type_code IS NULL) OR
935 (lookup_payment_type_code IS NOT NULL AND lookup_payment_type_code NOT IN
936 (SELECT pay_item_type FROM PO_STYLE_ENABLED_PAY_ITEMS WHERE style_id = po_style_id))) THEN
937 INTO pon_interface_errors
938 (
939 column_name, entity_attr_name, error_message_name, -- 1
940 error_value_datatype, error_value, error_value_date, -- 2
941 token1_name, token1_value, token2_name, -- 3
942 token2_value, interface_type, table_name, -- 4
943 batch_id, interface_line_id, entity_type, -- 5
944 auction_header_id, line_number, payment_id, -- 6
945 expiration_date, created_by, creation_date, -- 7
946 last_updated_by, last_update_date, last_update_login -- 8
947 )
948 VALUES
949 (
950 fnd_message.get_string('PON','PON_AUCTS_PAYITEM_TYPE'), NULL, 'PON_PYMT_TYPE_INVALID', -- 1
951 'TXT', payment_type, NULL, -- 2
952 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
953 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
954 p_batch_id, interface_line_id, NULL, -- 5
955 auction_header_id, auction_line_number, NULL, -- 6
956 l_exp_date, l_userid, SYSDATE, -- 7
957 l_userid, SYSDATE, l_loginid -- 8
958 )
959 WHEN auction_line_number IS NULL THEN
960 INTO pon_interface_errors
961 (
962 column_name, entity_attr_name, error_message_name, -- 1
963 error_value_datatype, error_value, error_value_date, -- 2
964 token1_name, token1_value, token2_name, -- 3
965 token2_value, interface_type, table_name, -- 4
966 batch_id, interface_line_id, entity_type, -- 5
967 auction_header_id, line_number, payment_id, -- 6
968 expiration_date, created_by, creation_date, -- 7
969 last_updated_by, last_update_date, last_update_login -- 8
970 )
971 VALUES
972 (
973 fnd_message.get_string('PON','PON_AUCTS_LINENUMBER'), NULL, 'PON_INVALID_LINE_NUM', -- 1
974 'TXT', document_disp_line_number, NULL, -- 2
975 null, null, NULL, -- 3
976 NULL, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
977 p_batch_id, interface_line_id, NULL, -- 5
978 auction_header_id, auction_line_number, NULL, -- 6
979 l_exp_date, l_userid, SYSDATE, -- 7
980 l_userid, SYSDATE, l_loginid -- 8
981 )
982 WHEN auction_line_number IS NOT NULL and group_type IN ('GROUP','LOT_LINE') THEN
983 INTO pon_interface_errors
984 (
985 column_name, entity_attr_name, error_message_name, -- 1
986 error_value_datatype, error_value, error_value_date, -- 2
987 token1_name, token1_value, token2_name, -- 3
988 token2_value, interface_type, table_name, -- 4
989 batch_id, interface_line_id, entity_type, -- 5
990 auction_header_id, line_number, payment_id, -- 6
991 expiration_date, created_by, creation_date, -- 7
992 last_updated_by, last_update_date, last_update_login -- 8
993 )
994 VALUES
995 (
996 fnd_message.get_string('PON','PON_AUCTS_LINENUMBER'), NULL, 'PON_PYMT_NOTALLOWED', -- 1
997 'TXT', document_disp_line_number, NULL, -- 2
998 'LINENUM', document_disp_line_number, NULL, -- 3
999 NULL, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
1000 p_batch_id, interface_line_id, NULL, -- 5
1001 auction_header_id, auction_line_number, NULL, -- 6
1002 l_exp_date, l_userid, SYSDATE, -- 7
1003 l_userid, SYSDATE, l_loginid -- 8
1004 )
1005 WHEN lookup_payment_type_code = 'RATE' AND unit_of_measure IS NULL THEN
1006 INTO pon_interface_errors
1007 (
1008 column_name, entity_attr_name, error_message_name, -- 1
1009 error_value_datatype, error_value, error_value_date, -- 2
1010 token1_name, token1_value, token2_name, -- 3
1011 token2_value, interface_type, table_name, -- 4
1012 batch_id, interface_line_id, entity_type, -- 5
1013 auction_header_id, line_number, payment_id, -- 6
1014 expiration_date, created_by, creation_date, -- 7
1015 last_updated_by, last_update_date, last_update_login -- 8
1016 )
1017 VALUES
1018 (
1019 fnd_message.get_string('PON','PON_AUCTS_UOM'), NULL, 'PON_PYMT_UOM_NULL', -- 1
1020 'TXT', NULL, NULL, -- 2
1021 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
1022 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
1023 p_batch_id, interface_line_id, NULL, -- 5
1024 auction_header_id, auction_line_number, NULL, -- 6
1025 l_exp_date, l_userid, SYSDATE, -- 7
1026 l_userid, SYSDATE, l_loginid -- 8
1027 )
1028 WHEN payment_display_number < 1 OR payment_display_number<> ROUND(payment_display_number) THEN
1029 INTO pon_interface_errors
1030 (
1031 column_name, entity_attr_name, error_message_name, -- 1
1032 error_value_datatype, error_value, error_value_date, -- 2
1033 token1_name, token1_value, token2_name, -- 3
1034 token2_value, interface_type, table_name, -- 4
1035 batch_id, interface_line_id, entity_type, -- 5
1036 auction_header_id, line_number, payment_id, -- 6
1037 expiration_date, created_by, creation_date, -- 7
1038 last_updated_by, last_update_date, last_update_login -- 8
1039 )
1040 VALUES
1041 (
1042 fnd_message.get_string('PON','PON_AUCTS_PAYITEM_NUMBER'), NULL, 'PON_PYMT_NUM_WRONG', -- 1
1043 'TXT', payment_display_number, NULL, -- 2
1044 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
1045 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
1046 p_batch_id, interface_line_id, NULL, -- 5
1047 auction_header_id, auction_line_number, NULL, -- 6
1048 l_exp_date, l_userid, SYSDATE, -- 7
1049 l_userid, SYSDATE, l_loginid -- 8
1050 )
1051 WHEN lookup_payment_type_code = 'RATE' AND quantity < 0 THEN
1052 INTO pon_interface_errors
1053 (
1054 column_name, entity_attr_name, error_message_name, -- 1
1055 error_value_datatype, error_value_number, error_value_date, -- 2
1056 token1_name, token1_value, token2_name, -- 3
1057 token2_value, interface_type, table_name, -- 4
1058 batch_id, interface_line_id, entity_type, -- 5
1059 auction_header_id, line_number, payment_id, -- 6
1060 expiration_date, created_by, creation_date, -- 7
1061 last_updated_by, last_update_date, last_update_login -- 8
1062 )
1063 VALUES
1064 (
1065 fnd_message.get_string('PON','PON_AUCTS_QUANTITY'), NULL, 'PON_PYMT_QTY_WRONG', -- 1
1066 'NUM', quantity, NULL, -- 2
1067 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
1068 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
1069 p_batch_id, interface_line_id, NULL, -- 5
1070 auction_header_id, auction_line_number, NULL, -- 6
1071 l_exp_date, l_userid, SYSDATE, -- 7
1072 l_userid, SYSDATE, l_loginid -- 8
1073 )
1074 WHEN target_price IS NOT NULL AND target_price < 0 THEN
1075 INTO pon_interface_errors
1076 (
1077 column_name, entity_attr_name, error_message_name, -- 1
1078 error_value_datatype, error_value_number, error_value_date, -- 2
1079 token1_name, token1_value, token2_name, -- 3
1080 token2_value, interface_type, table_name, -- 4
1081 batch_id, interface_line_id, entity_type, -- 5
1082 auction_header_id, line_number, payment_id, -- 6
1083 expiration_date, created_by, creation_date, -- 7
1084 last_updated_by, last_update_date, last_update_login -- 8
1085 )
1086 VALUES
1087 (
1088 fnd_message.get_string('PON','PON_AUCTS_TARGET_PRICE'), NULL, 'PON_PYMT_TPRICE_WRONG', -- 1
1089 'NUM', target_price, NULL, -- 2
1090 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
1091 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
1092 p_batch_id, interface_line_id, NULL, -- 5
1093 auction_header_id, auction_line_number, NULL, -- 6
1094 l_exp_date, l_userid, SYSDATE, -- 7
1095 l_userid, SYSDATE, l_loginid -- 8
1096 )
1097 WHEN payment_display_number IS NULL THEN
1098 INTO pon_interface_errors
1099 (
1100 column_name, entity_attr_name, error_message_name, -- 1
1101 error_value_datatype, error_value, error_value_date, -- 2
1102 token1_name, token1_value, token2_name, -- 3
1103 token2_value, interface_type, table_name, -- 4
1104 batch_id, interface_line_id, entity_type, -- 5
1105 auction_header_id, line_number, payment_id, -- 6
1106 expiration_date, created_by, creation_date, -- 7
1107 last_updated_by, last_update_date, last_update_login -- 8
1108 )
1109 VALUES
1110 (
1111 fnd_message.get_string('PON','PON_AUCTS_PAYITEM_NUMBER'), NULL, 'PON_PYMT_NUM_MISSING', -- 1
1112 'TXT', payment_display_number, NULL, -- 2
1113 'LINENUM', document_disp_line_number, null, -- 3
1114 NULL, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
1115 p_batch_id, interface_line_id, NULL, -- 5
1116 auction_header_id, auction_line_number, NULL, -- 6
1117 l_exp_date, l_userid, SYSDATE, -- 7
1118 l_userid, SYSDATE, l_loginid -- 8
1119 )
1120 WHEN payment_type IS NULL THEN
1121 INTO pon_interface_errors
1122 (
1123 column_name, entity_attr_name, error_message_name, -- 1
1124 error_value_datatype, error_value, error_value_date, -- 2
1125 token1_name, token1_value, token2_name, -- 3
1126 token2_value, interface_type, table_name, -- 4
1127 batch_id, interface_line_id, entity_type, -- 5
1128 auction_header_id, line_number, payment_id, -- 6
1129 expiration_date, created_by, creation_date, -- 7
1130 last_updated_by, last_update_date, last_update_login -- 8
1131 )
1132 VALUES
1133 (
1134 fnd_message.get_string('PON','PON_AUCTS_PAYITEM_TYPE'), NULL, 'PON_PYMT_TYPE_NULL', -- 1
1135 'TXT', payment_type, NULL, -- 2
1136 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
1137 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
1138 p_batch_id, interface_line_id, NULL, -- 5
1139 auction_header_id, auction_line_number, NULL, -- 6
1140 l_exp_date, l_userid, SYSDATE, -- 7
1141 l_userid, SYSDATE, l_loginid -- 8
1142 )
1143 WHEN payment_description IS NULL THEN
1144 INTO pon_interface_errors
1145 (
1146 column_name, entity_attr_name, error_message_name, -- 1
1147 error_value_datatype, error_value, error_value_date, -- 2
1148 token1_name, token1_value, token2_name, -- 3
1149 token2_value, interface_type, table_name, -- 4
1150 batch_id, interface_line_id, entity_type, -- 5
1151 auction_header_id, line_number, payment_id, -- 6
1152 expiration_date, created_by, creation_date, -- 7
1153 last_updated_by, last_update_date, last_update_login -- 8
1154 )
1155 VALUES
1156 (
1157 fnd_message.get_string('PON','PON_AUCTS_PAYMENT_DESC'), NULL, 'PON_PYMT_DESC_NULL', -- 1
1158 'TXT', payment_description, NULL, -- 2
1159 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
1160 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
1161 p_batch_id, interface_line_id, NULL, -- 5
1162 auction_header_id, auction_line_number, NULL, -- 6
1163 l_exp_date, l_userid, SYSDATE, -- 7
1164 l_userid, SYSDATE, l_loginid -- 8
1165 )
1166 WHEN lookup_payment_type_code = 'RATE' AND quantity IS NULL THEN
1167 INTO pon_interface_errors
1168 (
1169 column_name, entity_attr_name, error_message_name, -- 1
1170 error_value_datatype, error_value, error_value_date, -- 2
1171 token1_name, token1_value, token2_name, -- 3
1172 token2_value, interface_type, table_name, -- 4
1173 batch_id, interface_line_id, entity_type, -- 5
1174 auction_header_id, line_number, payment_id, -- 6
1175 expiration_date, created_by, creation_date, -- 7
1176 last_updated_by, last_update_date, last_update_login -- 8
1177 )
1178 VALUES
1179 (
1180 fnd_message.get_string('PON','PON_AUCTS_QUANTITY'), NULL, 'PON_PYMT_QTY_NULL', -- 1
1181 'TXT', NULL, NULL, -- 2
1182 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
1183 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
1184 p_batch_id, interface_line_id, NULL, -- 5
1185 auction_header_id, auction_line_number, NULL, -- 6
1186 l_exp_date, l_userid, SYSDATE, -- 7
1187 l_userid, SYSDATE, l_loginid -- 8
1188 )
1189 WHEN need_by_date IS NOT NULL AND need_by_date < pah_close_bidding_date THEN
1190 INTO pon_interface_errors
1191 (
1192 column_name, entity_attr_name, error_message_name, -- 1
1193 error_value_datatype, error_value_number, error_value_date, -- 2
1194 token1_name, token1_value, token2_name, -- 3
1195 token2_value, interface_type, table_name, -- 4
1196 batch_id, interface_line_id, entity_type, -- 5
1197 auction_header_id, line_number, payment_id, -- 6
1198 expiration_date, created_by, creation_date, -- 7
1199 last_updated_by, last_update_date, last_update_login -- 8
1200 )
1201 VALUES
1202 (
1203 fnd_message.get_string('PON','PON_AUCTS_NEEDBY'), NULL, 'PON_PYMT_NDATE_LESS_CDATE', -- 1
1204 'TIM', NULL, need_by_date, -- 2
1205 'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
1206 payment_display_number, l_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
1207 p_batch_id, interface_line_id, NULL, -- 5
1208 auction_header_id, auction_line_number, NULL, -- 6
1209 l_exp_date, l_userid, SYSDATE, -- 7
1210 l_userid, SYSDATE, l_loginid -- 8
1211 )
1212 SELECT
1213 ppi.project_number project_number,
1214 ppi.project_task_number ,
1215 ppi.project_expenditure_type,
1216 ppi.project_exp_organization_name,
1217 ppi.project_expenditure_item_date,
1218 ppi.project_award_number ,
1219 ppi.payment_display_number,
1220 ppi.payment_type,
1221 ppi.unit_of_measure,
1222 ppi.interface_line_id interface_line_id,
1223 ppi.auction_header_id auction_header_id,
1224 ppi.document_disp_line_number,
1225 ppi.work_approver_user_name,
1226 ppi.ship_to_location_code,
1227 ppi.target_price,
1228 ppi.quantity,
1229 ppi.payment_description,
1230 ppi.need_by_date,
1231 pah.close_bidding_date pah_close_bidding_date,
1232 pah.po_style_id,
1233 pai.line_number auction_line_number,
1234 pai.group_type,
1235 pai.purchase_basis line_type,
1236 NVL(pai.line_origination_code,'-9997') line_origination_code,
1237 uom.uom_code uom_uom_code,
1238 pro.project_id pro_project_id,
1239 porg.organization_id porg_proj_exp_organization_id,
1240 ship.location_id ship_to_location_id,
1241 fl.lookup_code lookup_payment_type_code
1242 FROM PON_AUC_PAYMENTS_INTERFACE ppi,
1243 PON_AUCTION_ITEM_PRICES_ALL pai,
1244 PON_AUCTION_HEADERS_ALL pah,
1245 PO_SHIP_TO_LOC_ORG_V ship,
1246 FINANCIALS_SYSTEM_PARAMS_ALL fsp,
1247 MTL_UNITS_OF_MEASURE uom,
1248 PO_LOOKUP_CODES fl,
1249 PA_PROJECTS_EXPEND_V pro,
1250 PA_ORGANIZATIONS_EXPEND_V porg
1251 WHERE ppi.auction_header_id = pai.auction_header_id (+)
1252 AND ppi.document_disp_line_number = pai.document_disp_line_number (+)
1253 AND ppi.batch_id = p_batch_id
1254 AND pah.auction_header_id = ppi.auction_header_id
1255 AND ppi.project_number = pro.project_number(+)
1256 AND ppi.project_exp_organization_name = porg.name(+)
1257 AND ppi.unit_of_measure = uom.unit_of_measure_tl(+)
1258 AND uom.language (+) = userenv('LANG')
1259 AND ppi.payment_type = fl.displayed_field (+)
1260 AND fl.lookup_type(+) = 'PAYMENT TYPE'
1261 AND pah.org_id = fsp.org_id (+)
1262 AND (ship.set_of_books_id IS NULL OR ship.set_of_books_id = fsp.set_of_books_id)
1263 AND ppi.ship_to_location_code = ship.location_code(+)
1264 AND ((nvl(pai.line_origination_code,'N') <> 'REQUISITION')
1265 OR
1266 (nvl(pai.line_origination_code,'N') = 'REQUISITION' AND
1267 (ship.inventory_organization_id is null
1268 or nvl(ship.inventory_organization_id,-1) = ( SELECT nvl(pr.destination_organization_id,-1)
1269 FROM po_requisition_lines_all pr,PON_BACKING_REQUISITIONS pbr
1270 WHERE pbr.auction_header_id= pai.auction_header_id
1271 AND pbr.line_number = pai.line_number
1272 AND pbr.requisition_line_id = pr.requisition_line_id))));
1273
1274 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1275 print_debug_log(l_module,'After insert all valiations of validate_creation for p_batch_id = '||p_batch_id);
1276 END IF;
1277
1278 --Validate project fields with PATC
1279 FOR l_proj_record IN l_proj_cursor LOOP
1280 IF l_proj_record.project_id IS NOT NULL AND
1281 l_proj_record.task_id IS NOT NULL AND
1282 l_proj_record.organization_id IS NOT NULL THEN
1283
1284 PON_NEGOTIATION_PUBLISH_PVT.VALIDATE_PROJECTS_DETAILS (
1285 p_project_id => l_proj_record.project_id,
1286 p_task_id => l_proj_record.task_id,
1287 p_expenditure_date => l_proj_record.project_expenditure_item_date,
1288 p_expenditure_type => l_proj_record.project_expenditure_type,
1289 p_expenditure_org => l_proj_record.organization_id,
1290 p_person_id => null,
1291 p_auction_header_id => l_proj_record.auction_header_id,
1292 p_line_number => null,
1293 p_document_disp_line_number => l_proj_record.document_disp_line_number,
1294 p_payment_id => null,
1295 p_interface_line_id => l_proj_record.interface_line_id,
1296 p_payment_display_number => l_proj_record.payment_display_number,
1297 p_batch_id => p_batch_id,
1298 p_table_name => 'PON_AUC_PAYMENTS_INTERFACE',
1299 p_interface_type => l_interface_type,
1300 p_entity_type => null,
1301 p_called_from => 'PAYMENTS_SP');
1302 END IF;
1303 END LOOP;
1304
1305 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1306 print_debug_log(l_module,'After calling projects validations for p_batch_id = '||p_batch_id);
1307 END IF;
1308 EXCEPTION
1309
1310 WHEN OTHERS THEN
1311 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1312 print_error_log(l_module, 'EXCEPTION - l_progress='||l_progress||' Error Code=' || SQLCODE || ' SQLERRM=' || SQLERRM);
1313 END if;
1314 END validate_creation;
1315 --
1316
1317 PROCEDURE copy_payments_from_int_to_txn(
1318 p_batch_id IN pon_bid_item_prices_interface.batch_id%TYPE,
1319 p_spreadsheet_type IN VARCHAR2,
1320 p_bid_number IN NUMBER,
1321 p_auction_header_id IN NUMBER,
1322 x_result OUT NOCOPY VARCHAR2, -- S: Success, E: failure
1323 x_error_code OUT NOCOPY VARCHAR2,
1324 x_error_message OUT NOCOPY VARCHAR2)
1325 IS
1326 l_rate pon_bid_headers.rate%TYPE;
1327 l_sequence NUMBER :=0;
1328 l_previous_line_number NUMBER := -99;
1329 l_module CONSTANT VARCHAR2(32) := 'COPY_PAYMENTS_FROM_INT_TO_TXN';
1330 l_progress varchar2(200);
1331 l_supplier_enterable_pymt_flag pon_auction_headers_all.supplier_enterable_pymt_flag%TYPE;
1332
1333 CURSOR delete_pymt_attachments_cursor IS
1334 SELECT fnd.pk3_value bid_payment_id, -- bid payment id
1335 fnd.pk1_value bid_number, -- bid number
1336 fnd.pk2_value bid_line_number -- bid line number
1337 FROM FND_ATTACHED_DOCUMENTS fnd
1338 WHERE fnd.pk1_value = p_bid_number
1339 AND fnd.pk3_value NOT IN (SELECT bid_payment_id
1340 FROM PON_BID_PAYMENTS_SHIPMENTS pbps
1341 WHERE pbps.bid_number = p_bid_number)
1342 AND fnd.entity_name = 'PON_BID_PAYMENTS_SHIPMENTS';
1343
1344
1345 CURSOR l_attachment_cursor
1346 IS
1347 SELECT pbpi.attachment_desc,
1348 pbpi.attachment_url,
1349 pbps.bid_payment_id,
1350 pbps.bid_number,
1351 pbps.bid_line_number,
1352 pbpi.document_disp_line_number
1353 FROM pon_bid_payments_interface pbpi,
1354 pon_auction_item_prices_all pai,
1355 pon_bid_payments_shipments pbps
1356 WHERE pbpi.auction_header_id = pai.auction_header_id
1357 AND pbpi.document_disp_line_number = pai.document_disp_line_number
1358 AND pbps.auction_header_id = pai.auction_header_id
1359 AND pbps.bid_line_number = pai.line_number
1360 AND pbps.payment_display_number = pbpi.payment_display_number
1361 AND pbpi.batch_id = p_batch_id
1362 AND pbpi.attachment_desc IS NOT NULL;
1363 BEGIN
1364
1365 x_result := 'S';
1366
1367 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1368 print_debug_log(l_module,'COPY_PAYMENTS_FROM_INT_TO_TXN START p_batch_id = '||p_batch_id);
1369 END IF;
1370
1371 -- select some variables that we need for currency conversion
1372 SELECT distinct pbh.rate
1373 INTO l_rate
1374 FROM pon_bid_headers pbh,
1375 pon_bid_payments_interface pbpi
1376 WHERE pbpi.bid_number = pbh.bid_number
1377 AND pbpi.batch_id = p_batch_id;
1378
1379 -- Update lines table with values in the interface table
1380 MERGE INTO pon_bid_payments_shipments bl
1381 USING
1382 (SELECT
1383 pbpi.auction_header_id,
1384 pbpi.bid_number,
1385 pbpi.document_disp_line_number,
1386 pbpi.batch_id,
1387 pbpi.interface_line_id,
1388 DECODE(fl.lookup_code, 'RATE', pbpi.QUANTITY, NULL) QUANTITY,
1389 DECODE(fl.lookup_code, 'RATE', uom.uom_code, NULL) UOM_CODE,
1390 pbpi.bid_currency_price,
1391 pbpi.payment_display_number,
1392 pbpi.payment_description,
1393 fl.lookup_code payment_type_code,
1394 pbpi.promised_date,
1395 pbip.line_number line_number,
1396 nvl(pah.supplier_enterable_pymt_flag,'N') supplier_can_modify_pymts
1397 FROM pon_bid_payments_interface pbpi,
1398 pon_auction_item_prices_all paip,
1399 pon_auction_headers_all pah,
1400 pon_bid_item_prices pbip,
1401 MTL_UNITS_OF_MEASURE uom,
1402 PO_LOOKUP_CODES fl
1403 WHERE pbpi.batch_id = p_batch_id
1404 AND pbpi.auction_header_id = p_auction_header_id
1405 AND pbpi.bid_number = p_bid_number
1406 AND pbpi.auction_header_id = paip.auction_header_id
1407 AND pbpi.document_disp_line_number = paip.document_disp_line_number
1408 AND pah.auction_header_id = pbpi.auction_header_id
1409 AND pbip.bid_number = pbpi.bid_number
1410 AND pbip.auction_header_id = paip.auction_header_id
1411 AND pbip.line_number = paip.line_number
1412 AND pbpi.unit_of_measure = uom.unit_of_measure_tl(+)
1413 AND uom.language (+) = userenv('LANG')
1414 AND pbpi.payment_type = fl.displayed_field (+)
1415 AND fl.lookup_type(+) = 'PAYMENT TYPE'
1416 ) bli
1417 ON (bl.bid_number = bli.bid_number
1418 AND bl.bid_line_number = bli.line_number
1419 AND bl.auction_header_id = bli.auction_header_id
1420 AND bl.payment_display_number = bli.payment_display_number)
1421 WHEN MATCHED THEN
1422 UPDATE SET
1423 bl.payment_description = decode(bli.supplier_can_modify_pymts, 'Y',bli.payment_description,'N',bl.payment_description),
1424 bl.payment_type_code = decode(bli.supplier_can_modify_pymts, 'Y',bli.payment_type_code,'N',bl.payment_type_code),
1425 bl.quantity = decode(bli.supplier_can_modify_pymts, 'Y',bli.quantity,'N',bl.quantity),
1426 bl.uom_code = decode(bli.supplier_can_modify_pymts, 'Y',bli.uom_code,'N',bl.uom_code),
1427 bl.bid_currency_price = bli.bid_currency_price,
1428 bl.price = bli.bid_currency_price/nvl(l_rate,1), --auction currency price
1429 bl.promised_date = bli.promised_date,
1430 bl.last_update_date = sysdate,
1431 bl.last_updated_by = fnd_global.user_id,
1432 bl.last_update_login = fnd_global.login_id
1433 WHEN NOT MATCHED THEN
1434 INSERT (
1435 BID_PAYMENT_ID ,
1436 AUCTION_HEADER_ID ,
1437 BID_LINE_NUMBER ,
1438 AUCTION_LINE_NUMBER ,
1439 BID_NUMBER ,
1440 PAYMENT_DISPLAY_NUMBER ,
1441 PAYMENT_DESCRIPTION ,
1442 PAYMENT_TYPE_CODE ,
1443 QUANTITY ,
1444 UOM_CODE ,
1445 BID_CURRENCY_PRICE ,
1446 PRICE , -- Auction Currency price
1447 PROMISED_DATE ,
1448 CREATION_DATE ,
1449 CREATED_BY ,
1450 LAST_UPDATE_DATE ,
1451 LAST_UPDATED_BY ,
1452 LAST_UPDATE_LOGIN
1453 )
1454 VALUES (
1455 PON_BID_PAYMENTS_SHIPMENTS_S1.nextval ,
1456 bli.AUCTION_HEADER_ID ,
1457 bli.LINE_NUMBER ,
1458 bli.LINE_NUMBER ,
1459 bli.BID_NUMBER ,
1460 bli.PAYMENT_DISPLAY_NUMBER ,
1461 bli.PAYMENT_DESCRIPTION ,
1462 bli.PAYMENT_TYPE_CODE ,
1463 bli.QUANTITY ,
1464 bli.UOM_CODE ,
1465 bli.BID_CURRENCY_PRICE ,
1466 bli.BID_CURRENCY_PRICE/nvl(l_rate,1) , --Auction currency price
1467 bli.PROMISED_DATE ,
1468 SYSDATE ,
1469 fnd_global.user_id ,
1470 SYSDATE ,
1471 fnd_global.user_id ,
1472 fnd_global.login_id
1473 ) ;
1474
1475 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1476 l_progress := 'Merge into pon_bid_payments_shipments is successful for batch_id = '||p_batch_id;
1477 END if;
1478
1479 IF (p_spreadsheet_type = PON_BID_VALIDATIONS_PKG.g_txt_upload_mode) THEN
1480 --create URL Attachments
1481 FOR l_attachment_record IN l_attachment_cursor LOOP
1482
1483 IF l_attachment_record.document_disp_line_number <> l_previous_line_number THEN
1484 l_sequence := 1;
1485 l_previous_line_number := l_attachment_record.document_disp_line_number;
1486 ELSE
1487 l_sequence := l_sequence+1;
1488
1489 END IF;
1490
1491 PON_OA_UTIL_PKG.create_url_attachment(
1492 p_seq_num => l_sequence,
1493 p_category_name => 'FromSupplier',
1494 p_document_description => l_attachment_record.attachment_desc,
1495 p_datatype_id => 5,
1496 p_url => l_attachment_record.attachment_url,
1497 p_entity_name => 'PON_BID_PAYMENTS_SHIPMENTS',
1498 p_pk1_value => l_attachment_record.bid_number,
1499 p_pk2_value => l_attachment_record.bid_line_number,
1500 p_pk3_value => l_attachment_record.bid_payment_id,
1501 p_pk4_value => NULL,
1502 p_pk5_value => NULL);
1503 END LOOP;
1504 ELSE --i.e (p_spreadsheet_type = 'XML')
1505
1506 SELECT supplier_enterable_pymt_flag
1507 INTO l_supplier_enterable_pymt_flag
1508 FROM PON_AUCTION_HEADERS_ALL
1509 WHERE auction_header_id = p_auction_header_id;
1510
1511 IF l_supplier_enterable_pymt_flag = 'Y' THEN
1512 DELETE FROM PON_BID_PAYMENTS_SHIPMENTS pbp
1513 WHERE pbp.payment_display_number NOT IN (SELECT pbpi.payment_display_number
1514 FROM PON_BID_PAYMENTS_INTERFACE pbpi,
1515 PON_AUCTION_ITEM_PRICES_ALL pai
1516 WHERE pbpi.batch_id = p_batch_id
1517 AND pbpi.bid_number = p_bid_number
1518 AND pai.auction_header_id = pbpi.auction_header_id
1519 AND pai.document_disp_line_number = pbpi.document_disp_line_number
1520 AND pbp.bid_line_number = pai.line_number)
1521 AND pbp.bid_number = p_bid_number
1522 AND pbp.bid_line_number IN (SELECT pbi.line_number FROM PON_BID_ITEM_PRICES_INTERFACE pbi WHERE pbi.batch_id = p_batch_id);
1523
1524 -- To delete attachments of pon_bid_payments_shipments
1525 FOR delete_pymt_attachments_record IN delete_pymt_attachments_cursor
1526 LOOP
1527 FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS
1528 (x_entity_name => 'PON_BID_PAYMENTS_SHIPMENTS',
1529 x_pk1_value => delete_pymt_attachments_record.bid_number,
1530 x_pk2_value => delete_pymt_attachments_record.bid_line_number,
1531 x_pk3_value => delete_pymt_attachments_record.bid_payment_id);
1532 END LOOP;
1533 END IF; -- End of suppliers can modify flag
1534
1535 END IF;
1536
1537
1538 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1539 l_progress := 'creting URL Attachments is complete for p_batch_id = '||p_batch_id;
1540 END if;
1541
1542
1543 --before deleting, set the is_changed_line_flag column for the lines for which
1544 --payments are uploaded in this batch
1545 UPDATE pon_bid_item_prices pbip SET pbip.IS_CHANGED_LINE_FLAG = 'Y',
1546 LAST_UPDATE_DATE = sysdate,
1547 LAST_UPDATED_BY = FND_GLOBAL.user_id
1548 WHERE pbip.line_number IN (
1549 SELECT paip.line_number FROM pon_auction_item_prices_all paip, pon_bid_payments_interface pbpi, pon_bid_payments_shipments pbps WHERE
1550 pbpi.batch_id = p_batch_id AND
1551 paip.auction_header_id = pbpi.auction_header_id AND
1552 pbpi.document_disp_line_number = paip.document_disp_line_number AND
1553 pbps.AUCTION_LINE_NUMBER = paip.line_number AND
1554 pbps.AUCTION_HEADER_ID = paip.auction_header_id AND
1555 pbps.BID_NUMBER = pbpi.bid_number AND
1556 (
1557 pbps.Old_Payment_Display_Number<> pbps.Payment_Display_Number OR
1558 pbps.Old_Payment_Type_Code<> pbps.Payment_Type_Code OR
1559 pbps.Old_Payment_Description<> pbps.Payment_Description OR
1560 pbps.Old_Quantity<> pbps.Quantity OR
1561 pbps.Old_Uom_Code<> pbps.Uom_Code OR
1562 pbps.Old_Bid_Currency_Price<> pbps.Bid_Currency_Price OR
1563 pbps.Old_Promised_Date<> pbps.Promised_Date
1564 )
1565 );
1566
1567
1568 -- Clear the interface tables
1569 delete from pon_bid_payments_interface where batch_id = p_batch_id;
1570
1571 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1572 l_progress := 'delete from pon_bid_payments_interface completed for p_batch_id = '||p_batch_id;
1573 END if;
1574
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 x_result := 'E';
1578 x_error_code := SQLCODE;
1579 x_error_message := SUBSTR(SQLERRM, 1, 100);
1580 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1581 print_error_log(l_module, 'EXCEPTION - l_progress='||l_progress||' x_result=' || x_result || ' x_error_code=' || x_error_code || ' x_error_message=' || x_error_message || ' SQLERRM=' || SQLERRM);
1582 END if;
1583 END copy_payments_from_int_to_txn;
1584
1585
1586 /*======================================================================
1587 PROCEDURE: PRINT_ERROR_LOG PRIVATE
1588 PARAMETERS:
1589 COMMENT : This procedure is used to print unexpected exceptions or
1590 error messages into FND logs
1591 ======================================================================*/
1592
1593 PROCEDURE print_error_log(p_module IN VARCHAR2,
1594 p_message IN VARCHAR2)
1595 IS
1596 BEGIN
1597
1598 IF (g_fnd_debug = 'Y' and FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1599 FND_LOG.string(log_level => FND_LOG.level_procedure,
1600 module => g_module_prefix || p_module,
1601 message => p_message);
1602 END if;
1603
1604 END;
1605
1606 /*======================================================================
1607 PROCEDURE: PRINT_DEBUG_LOG PRIVATE
1608 PARAMETERS:
1609 COMMENT : This procedure is used to print debug messages into
1610 FND logs
1611 ======================================================================*/
1612 PROCEDURE print_debug_log(p_module IN VARCHAR2,
1613 p_message IN VARCHAR2)
1614 IS
1615
1616 BEGIN
1617
1618 IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1619 FND_LOG.string(log_level => FND_LOG.level_statement,
1620 module => g_module_prefix || p_module,
1621 message => p_message);
1622 END if;
1623
1624 END;
1625 END pon_validate_payments_int;