[Home] [Help]
PACKAGE BODY: APPS.PO_NEGOTIATIONS_SV2
Source
1 PACKAGE BODY PO_NEGOTIATIONS_SV2 AS
2 /* $Header: POXNEG3B.pls 120.11.12000000.4 2007/10/17 12:54:42 bisdas ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_pdoi_write_to_file VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_PDOI_WRITE_TO_FILE'),'N');
6
7 g_cat_TO_SUPPLIER CONSTANT NUMBER := 33; -- <Complex Work R12>
8
9
10 /********************************************************************
11 PROCEDURE NAME: default_po_dist_interface()
12
13 DESCRIPTION: This API defaults the distribution in
14 po_distributions_interface table. This uses account
15 generator to build the accounts.
16 Referenced by: This is called from po_interface_s.setup_interface_tables.
17 from the file POXBWP1B.pls
18
19 CHANGE History: Created 12-Feb-2002 Toju George
20 ********************************************************************/
21
22 PROCEDURE default_po_dist_interface(
23 x_interface_header_id IN NUMBER,
24 x_interface_line_id IN NUMBER,
25 x_item_id IN NUMBER,
26 x_category_id IN NUMBER,
27 x_ship_to_organization_id IN NUMBER,
28 x_ship_to_location_id IN NUMBER,
29 x_deliver_to_person_id IN NUMBER,
30 x_def_sob_id IN NUMBER,
31 x_chart_of_accounts_id IN NUMBER,
32 x_line_type_id IN NUMBER,
33 x_quantity IN number,
34 x_amount IN NUMBER, -- <SERVICES FPJ>
35 x_rate IN NUMBER,
36 x_rate_date IN DATE,
37 x_vendor_id IN NUMBER,
38 x_vendor_site_id IN NUMBER,
39 x_agent_id IN NUMBER,
40 x_po_encumbrance_flag IN VARCHAR2,
41 x_ussgl_transaction_code IN VARCHAR2,
42 x_type_lookup_code IN VARCHAR2,
43 x_expenditure_organization_id IN NUMBER,
44 x_project_id IN NUMBER,
45 x_task_id IN NUMBER,
46 x_bom_resource_id IN NUMBER,
47 x_wip_entity_id IN NUMBER,
48 x_wip_line_id IN NUMBER,
49 x_wip_repetitive_schedule_id IN NUMBER,
50 x_gl_encumbered_date IN DATE,
51 x_gl_encumbered_period IN VARCHAR2,
52 x_destination_subinventory IN VARCHAR2,
53 x_expenditure_type IN VARCHAR2,
54 x_expenditure_item_date IN DATE,
55 x_wip_operation_seq_num IN NUMBER,
56 x_wip_resource_seq_num IN NUMBER,
57 x_project_accounting_context IN VARCHAR2,
58 p_purchasing_ou_id IN NUMBER, --< Shared Proc FPJ >
59 p_unit_price IN NUMBER --<BUG 3407630>
60 ) IS
61
62 X_progress VARCHAR2(3) := NULL;
63 x_inventory_organization_id number;
64 x_receipt_required_flag varchar2(1);
65 x_item_status varchar2(2);
66 x_gl_date date;
67 l_ship_to_organization_id NUMBER;
68 x_expense_accrual_code po_system_parameters.expense_accrual_code%type;
69 x_destination_type_code varchar2(25);
70 l_gl_encumbered_date date;
71 x_destination_context varchar2(30);
72 x_accrue_on_receipt_flag varchar2(1);
73 x_prevent_encumbrance_flag varchar2(1);
74 l_gl_encumbered_period varchar2(15);
75 l_destination_subinventory varchar2(10);
76 x_inv_install_status varchar2(1);
77 --variables to default the accounts
78 x_success BOOLEAN;-- If this is TRUE, it means the
79 --call to build all accounts was successful
80 x_charge_success BOOLEAN := TRUE;
81 x_budget_success BOOLEAN := TRUE;
82 x_accrual_success BOOLEAN := TRUE;
83 x_variance_success BOOLEAN := TRUE;
84 x_charge_account_id number;
85 x_budget_account_id number;
86 x_accrual_account_id number;
87 x_variance_account_id number;
88 x_charge_account_flex VARCHAR2(2000);
89 x_budget_account_flex VARCHAR2(2000);
90 x_accrual_account_flex VARCHAR2(2000);
91 x_variance_account_flex VARCHAR2(2000);
92 x_charge_account_desc VARCHAR2(2000);
93 x_budget_account_desc VARCHAR2(2000);
94 x_accrual_account_desc VARCHAR2(2000);
95 x_variance_account_desc VARCHAR2(2000);
96 wf_itemkey VARCHAR2(80) := NULL;
97 x_new_ccid_generated BOOLEAN := FALSE;
98 FB_ERROR_MSG VARCHAR2(2000);
99
100 x_bom_cost_element_id NUMBER := NULL;
101 x_result_billable_flag varchar2(5) := NULL;
102 x_from_type_lookup_code varchar2(5) := NULL;
103 x_from_header_id NUMBER := NULL;
104 x_from_line_id NUMBER := NULL;
105 x_wip_entity_type varchar2(25) := NULL;
106 x_end_item_unit_number varchar2(30):=null;
107
108 header_att1 VARCHAR2(150) := NULL; header_att2 VARCHAR2(150) := NULL;
109 header_att3 VARCHAR2(150) := NULL; header_att4 VARCHAR2(150) := NULL;
110 header_att5 VARCHAR2(150) := NULL; header_att6 VARCHAR2(150) := NULL;
111 header_att7 VARCHAR2(150) := NULL; header_att8 VARCHAR2(150) := NULL;
112 header_att9 VARCHAR2(150) := NULL; header_att10 VARCHAR2(150) := NULL;
113 header_att11 VARCHAR2(150) := NULL; header_att12 VARCHAR2(150) := NULL;
114 header_att13 VARCHAR2(150) := NULL; header_att14 VARCHAR2(150) := NULL;
115 header_att15 VARCHAR2(150) := NULL;
116
117 line_att1 VARCHAR2(150) := NULL; line_att2 VARCHAR2(150) := NULL;
118 line_att3 VARCHAR2(150) := NULL; line_att4 VARCHAR2(150) := NULL;
119 line_att5 VARCHAR2(150) := NULL; line_att6 VARCHAR2(150) := NULL;
120 line_att7 VARCHAR2(150) := NULL; line_att8 VARCHAR2(150) := NULL;
121 line_att9 VARCHAR2(150) := NULL; line_att10 VARCHAR2(150) := NULL;
122 line_att11 VARCHAR2(150) := NULL; line_att12 VARCHAR2(150) := NULL;
123 line_att13 VARCHAR2(150) := NULL; line_att14 VARCHAR2(150) := NULL;
124 line_att15 VARCHAR2(150) := NULL;
125
126 shipment_att1 VARCHAR2(150) := NULL; shipment_att2 VARCHAR2(150) := NULL;
127 shipment_att3 VARCHAR2(150) := NULL; shipment_att4 VARCHAR2(150) := NULL;
128 shipment_att5 VARCHAR2(150) := NULL; shipment_att6 VARCHAR2(150) := NULL;
129 shipment_att7 VARCHAR2(150) := NULL; shipment_att8 VARCHAR2(150) := NULL;
130 shipment_att9 VARCHAR2(150) := NULL; shipment_att10 VARCHAR2(150) := NULL;
131 shipment_att11 VARCHAR2(150) := NULL; shipment_att12 VARCHAR2(150) := NULL;
132 shipment_att13 VARCHAR2(150) := NULL; shipment_att14 VARCHAR2(150) := NULL;
133 shipment_att15 VARCHAR2(150) := NULL;
134
135 distribution_att1 VARCHAR2(150) := NULL;
136 distribution_att2 VARCHAR2(150) := NULL;
137 distribution_att3 VARCHAR2(150) := NULL;
138 distribution_att4 VARCHAR2(150) := NULL;
139 distribution_att5 VARCHAR2(150) := NULL;
140 distribution_att6 VARCHAR2(150) := NULL;
141 distribution_att7 VARCHAR2(150) := NULL;
142 distribution_att8 VARCHAR2(150) := NULL;
143 distribution_att9 VARCHAR2(150) := NULL;
144 distribution_att10 VARCHAR2(150) := NULL;
145 distribution_att11 VARCHAR2(150) := NULL;
146 distribution_att12 VARCHAR2(150) := NULL;
147 distribution_att13 VARCHAR2(150) := NULL;
148 distribution_att14 VARCHAR2(150) := NULL;
149 distribution_att15 VARCHAR2(150) := NULL;
150
151 --< Shared Proc FPJ Start >
152 l_transaction_flow_header_id NUMBER;
153 l_dest_charge_success BOOLEAN;
154 l_dest_variance_success BOOLEAN;
155 l_dest_charge_account_id NUMBER;
156 l_dest_variance_account_id NUMBER;
157 l_dest_charge_account_desc VARCHAR2(2000);
158 l_dest_variance_account_desc VARCHAR2(2000);
159 l_dest_charge_account_flex VARCHAR2(2000);
160 l_dest_variance_account_flex VARCHAR2(2000);
161 --< Shared Proc FPJ End >
162
163 l_func_unit_price PO_LINES_ALL.unit_price%TYPE; -- Bug 3463242
164 BEGIN
165
166 l_ship_to_organization_id :=x_ship_to_organization_id;
167 l_gl_encumbered_date := x_gl_encumbered_date;
168 l_gl_encumbered_period :=x_gl_encumbered_period;
169 l_destination_subinventory := x_destination_subinventory;
170
171
172 x_progress := '010';
173 /*******************************************************************
174 Get master inventory org from fsp.
175
176 *******************************************************************/
177 begin
178 select fsp.inventory_organization_id
179 into x_inventory_organization_id
180 from financials_system_parameters fsp;
181 exception
182 when no_data_found then
183 raise;
184 end;
185 /******************************************************************/
186
187
188 x_progress := '020';
189 /*******************************************************************
190 --default the ship_to_organization_id
191 --if the interface line level ship_to_organization_id is null get it
192 --from the location's organization, if not successful then from fsp.
193 --And use this as the destination organization.
194 ********************************************************************/
195 if l_ship_to_organization_id is null then
196 begin
197 SELECT inventory_organization_id
198 INTO l_ship_to_organization_id
199 FROM hr_locations_all
200 WHERE location_id = x_ship_to_location_id
201 AND ship_to_site_flag = 'Y';
202 x_progress := '021';
203 -- Bug 3419480
204 IF (l_ship_to_organization_id IS NULL) THEN
205 --Bug# 2315130
206 l_ship_to_organization_id :=x_inventory_organization_id;
207 --
208 -- Bug 3419480
209 END IF; /*IF (l_ship_to_organization_id IS NULL)*/
210 exception
211 when no_data_found then
212 x_progress := '022';
213 l_ship_to_organization_id :=x_inventory_organization_id;
214 end;
215 end if;
216 /*******************************************************************/
217
218
219 x_progress := '030';
220 /*******************************************************************
221 Default expense accrual code from psp.
222 *******************************************************************/
223 SELECT expense_accrual_code
224 INTO x_expense_accrual_code
225 FROM po_system_parameters;
226
227 /******************************************************************/
228
229
230 x_progress := '040';
231 /*******************************************************************
232 Get the item_status.
233 item_status values:
234 'O' = osp item item_status
235 'E' = item stockable in the org
236 'D' = item defined but not stockable in org
237 null = item not defined in org
238 *******************************************************************/
239 if x_item_id is not null then
240 po_items_sv2.get_item_status(x_item_id,
241 l_ship_to_organization_id,
242 x_item_status );
243 end if;
244 /******************************************************************/
245
246
247 x_progress := '050';
248 /*******************************************************************
249 Determine receipt required flag
250 Receipt_required_flag is not accepted as a parameter as it is always
251 defaulted from item/destorg,item/invorg, line type, vendor, psp
252 in the respective order, overriding the interface value.
253 At this point receipt_required_flag we have in interface table is the
254 value defaulted from po_line_types in setup_interface_tables procedure.
255 *******************************************************************/
256 if x_item_id is not null then
257 --get from item level for destination org
258 begin
259 select msi.receipt_required_flag
260 into x_receipt_required_flag
261 from mtl_system_items msi
262 where msi.inventory_item_id = x_item_id
263 and msi.organization_id = l_ship_to_organization_id;
264 exception
265 when no_data_found then
266 null;
267 when others then
268 raise;
269 end;
270 --get from item level for master org
271 if x_receipt_required_flag is null then
272 begin
273 select msi.receipt_required_flag
274 into x_receipt_required_flag
275 from mtl_system_items msi
276 where msi.inventory_item_id = x_item_id
277 and msi.organization_id = x_inventory_organization_id;
278 exception
279 when no_data_found then
280 null;
281 when others then
282 po_message_s.sql_error('default_po_dist_interface',x_progress,
283 sqlcode);
284 raise;
285 end;
286 end if;
287 end if; --if item_id is not null
288 --get from line type level
289 if x_receipt_required_flag is null then
290 begin
291 select plt.receiving_flag
292 into x_receipt_required_flag
293 from po_line_types plt
294 where plt.line_type_id=x_line_type_id;
295 exception
296 when no_data_found then
297 null;
298 when others then
299 po_message_s.sql_error('default_po_dist_interface',x_progress,
300 sqlcode);
301 raise;
302 end;
303 end if;
304 --get from vendor level
305 if x_receipt_required_flag is null then
306 if x_vendor_id is not null then
307 begin
308 select pov.receipt_required_flag
309 into x_receipt_required_flag
310 from po_vendors pov
311 where pov.vendor_id=x_vendor_id;
312 exception
313 when no_data_found then
314 null;
315 when others then
316 po_message_s.sql_error('default_po_dist_interface',x_progress,
317 sqlcode);
318 raise;
319 end;
320 end if;
321 end if;
322 --get from psp
323 if x_receipt_required_flag is null then
324 begin
325 select psp.receiving_flag
326 into x_receipt_required_flag
327 from po_system_parameters psp;
328 exception
329 when no_data_found then
330 null;
331 when others then
332 po_message_s.sql_error('default_po_dist_interface',x_progress,
333 sqlcode);
334 raise;
335 end;
336 end if;
337
338 /******************************************************************/
339
340
341 x_progress := '060';
342 /*******************************************************************
343 Determine accrue on receipt flag
344
345 *******************************************************************/
346 IF x_item_status = 'O' THEN
347 X_accrue_on_receipt_flag := 'Y';
348 ELSE
349 IF X_item_status = 'E' THEN
350 x_inv_install_status := po_core_s.get_product_install_status('INV');
351 IF nvl(x_inv_install_status,'N')='I' then
352 --if inventory is installed then
353 X_accrue_on_receipt_flag := 'Y';
354 ELSE
355 IF x_expense_accrual_code = 'RECEIPT' THEN
356 X_accrue_on_receipt_flag := 'Y';
357 -- NAME_IN('po_lines.receipt_required_flag');
358 ELSIF x_expense_accrual_code = 'PERIOD END' THEN
359 X_accrue_on_receipt_flag := 'N';
360 END IF;
361 END IF;
362 ELSE -- Item status != 'E'(including null)
363 IF x_expense_accrual_code = 'RECEIPT' THEN
364 X_accrue_on_receipt_flag := x_receipt_required_flag;
365 ELSIF x_expense_accrual_code = 'PERIOD END' THEN
366 X_accrue_on_receipt_flag := 'N';
367 END IF;
368 END IF;
369 END IF;
370 /*******************************************************************/
371
372
373 x_progress := '070';
374 /*******************************************************************
375 Default destination_type_code from the item status and
376 accrue on receipt flag.
377 *******************************************************************/
378 if x_destination_type_code is null then
379 IF x_item_id is NULL THEN
380 x_destination_type_code := 'EXPENSE';
381 x_destination_context := 'EXPENSE';
382 ELSE
383
384 if x_item_status = 'O' THEN
385 x_destination_type_code := 'SHOP FLOOR';
386 x_destination_context := 'SHOP FLOOR';
387 elsif (x_item_status= 'E') AND (x_accrue_on_receipt_flag = 'Y') THEN
388 x_destination_type_code := 'INVENTORY';
389 x_destination_context := 'INVENTORY';
390 ELSE
391 x_destination_type_code := 'EXPENSE';
392 x_destination_context := 'EXPENSE';
393 l_destination_subinventory := NULL;
394 END IF;
395 END IF; /* Item is Null */
396 end if; --if dest type is null
397 /*******************************************************************/
398
399
400 x_progress := '080';
401 /*******************************************************************
402 Default gl_period and encumbrance related info.
403
404 *******************************************************************/
405 IF x_destination_type_code = 'SHOP FLOOR' THEN
406 x_Prevent_Encumbrance_Flag := 'Y';
407 ELSE
408 x_Prevent_Encumbrance_Flag := 'N';
409 END IF;
410 x_progress := '081';
411
412 IF x_po_encumbrance_flag = 'Y' THEN
413 IF l_gl_encumbered_date is NULL THEN
414 x_gl_date := sysdate;
415 l_gl_encumbered_date := sysdate;
416 ELSE
417 x_gl_date := l_gl_encumbered_date;
418 END IF;
419 po_periods_sv.get_period_name(x_def_sob_id,
420 x_gl_date,
421 l_gl_encumbered_period);
422 IF l_gl_encumbered_period is NULL THEN
423 --po_message_s.sql_error('default_po_dist_interface',x_progress,sqlcode);
424 null;--raise;
425 END IF;
426 ELSE
427 l_gl_encumbered_date := NULL;
428 l_gl_encumbered_period := NULL;
429 END IF;
430 /*******************************************************************/
431
432
433 x_progress := '090';
434 /*******************************************************************
435 Call account generator.
436 *******************************************************************/
437 -- Bug 3463242 START
438 -- Need to pass price to PO Account Generator in functional currency.
439 l_func_unit_price := p_unit_price * NVL(x_rate, 1);
440 -- Bug 3463242 END
441
442 IF (g_po_pdoi_write_to_file = 'Y') THEN
443 PO_DEBUG.put_line('before workflow');
444 -- Bug 3463242 START
445 PO_DEBUG.put_line('rate='||x_rate);
446 PO_DEBUG.put_line('unit_price passed to account generator workflow '||
447 'in functional currency='||l_func_unit_price);
448 -- Bug 3463242 END
449 END IF;
450 -- get the account ids
451 -- we dont need to call account gen. for a blanket as we dont
452 -- need a distribution record. We still insert a distribution
453 -- record into po_distributions_interface for a blanket to
454 -- help programming.
455 -- Bug 5050208: Removed the item id condition as account generator
456 -- also generates accounts for 1 time items
457 if x_type_lookup_code <>'BLANKET' and
458 (not(x_po_encumbrance_flag = 'Y'
459 and l_gl_encumbered_period is NULL))
460 then
461
462 --< Shared Proc FPJ Start >
463 -- Make the transaction flow header id null because SPS is not supported
464 -- with Sourcing in FPJ.
465 l_transaction_flow_header_id := NULL;
466 --< Shared Proc FPJ Start >
467
468 x_success := PO_WF_BUILD_ACCOUNT_INIT.Start_Workflow (
469
470 --< Shared Proc FPJ Start >
471 -- SPS is not being supported with Sourcing in FPJ. The extra
472 -- parameters being added to the Start_Workflow function are just
473 -- because of the signature change. These variables would never get
474 -- populated by the workflow and will never be used in the Sourcing
475 -- process.
476 p_purchasing_ou_id, -- IN
477 l_transaction_flow_header_id, -- IN
478 l_dest_charge_success, -- IN OUT
479 l_dest_variance_success, -- IN OUT
480 l_dest_charge_account_id, -- IN OUT
481 l_dest_variance_account_id, -- IN OUT
482 l_dest_charge_account_desc, -- IN OUT
483 l_dest_variance_account_desc, -- IN OUT
484 l_dest_charge_account_flex, -- IN OUT
485 l_dest_variance_account_flex, -- IN OUT
486 --< Shared Proc FPJ End >
487
488 x_charge_success, x_budget_success,
489 x_accrual_success, x_variance_success,
490 x_charge_account_id, x_budget_account_id,
491 x_accrual_account_id, x_variance_account_id,
492 x_charge_account_flex, x_budget_account_flex,
493 x_accrual_account_flex, x_variance_account_flex,
494 x_charge_account_desc, x_budget_account_desc,
495 x_accrual_account_desc, x_variance_account_desc,
496 x_chart_of_accounts_id, x_bom_resource_id,
497 x_bom_cost_element_id, x_category_id,
498 x_destination_type_code, x_ship_to_location_id,
499 l_ship_to_organization_id,l_destination_subinventory,
500 x_expenditure_type,
501 x_expenditure_organization_id,x_expenditure_item_date,
502 x_item_id , x_line_type_id,
503 x_result_billable_flag, x_agent_id,
504 x_project_id, x_from_type_lookup_code,
505 x_from_header_id, x_from_line_id,
506 x_task_id, x_deliver_to_person_id,
507 x_type_lookup_code, x_vendor_id,
508 x_wip_entity_id, x_wip_entity_type,
509 x_wip_line_id, x_wip_repetitive_schedule_id,
510 x_wip_operation_seq_num, x_wip_resource_seq_num,
511 x_po_encumbrance_flag, l_gl_encumbered_date,
512 wf_itemkey, x_new_ccid_generated,
513 header_att1, header_att2, header_att3, header_att4,
514 header_att5, header_att6, header_att7, header_att8,
515 header_att9, header_att10, header_att11,header_att12,
516 header_att13, header_att14, header_att15,
517 line_att1, line_att2, line_att3, line_att4, line_att5,
518 line_att6, line_att7, line_att8, line_att9, line_att10,
519 line_att11, line_att12, line_att13, line_att14, line_att15,
520 shipment_att1, shipment_att2, shipment_att3, shipment_att4,
521 shipment_att5, shipment_att6, shipment_att7, shipment_att8,
522 shipment_att9, shipment_att10,shipment_att11, shipment_att12,
523 shipment_att13, shipment_att14, shipment_att15,
524 distribution_att1, distribution_att2, distribution_att3,
525 distribution_att4, distribution_att5, distribution_att6,
526 distribution_att7, distribution_att8, distribution_att9,
527 distribution_att10,distribution_att11,distribution_att12,
528 distribution_att13, distribution_att14, distribution_att15,
529 FB_ERROR_MSG,
530 --<BUG 3407630 START>
531 NULL, --x_award_id
532 NULL, --x_vendor_site_id
533 l_func_unit_price -- Bug 3463242
534 --<BUG 3407630 END>
535 );
536
537 --<bug#4101202> We need to clear the cache after the call to
538 --account generator because it normally runs in SYNCHRONOUS mode.
539 --However the calling program is normally ASYNCHRONOUS as in case
540 --of the Sourcing Complete Auction Workflow(PONCOMPL).
541 --In such a case if the account generator returns an error it would
542 --get propagated to the sourcing workflow and would result in an error.
543 --To prevent this we would have to clear the cache.
544
545 WF_ENGINE_UTIL.CLEARCACHE;
546 WF_ACTIVITY.CLEARCACHE;
547 WF_ITEM_ACTIVITY_STATUS.CLEARCACHE;
548 WF_ITEM.CLEARCACHE;
549 WF_PROCESS_ACTIVITY.CLEARCACHE;
550
551 --<bug#4101202>
552
553 end if;
554 --Insert a distribution record into the interface table even if the
555 --accounts are not built. We would handle it when we insert the record
556 --into po_distributions table. in create_distributions procedure.
557 x_progress := '091';
558 --budget account is defaulted only if encumbrance is on
559 if x_po_encumbrance_flag = 'N' then
560 x_budget_account_id := null;
561 end if;
562 /*******************************************************************/
563
564 -- Let the tax defaulting and recovery rate calculation happen at
565 --the time we create the PO, not at the interface level.
566
567
568 x_progress := '100';
569 /*******************************************************************
570 Insert into po_distributions_interface table.
571 *******************************************************************/
572
573 INSERT INTO po_distributions_interface
574 (interface_header_id,
575 interface_line_id,
576 interface_distribution_id,
577 distribution_num,
578 charge_account_id,
579 set_of_books_id,
580 quantity_ordered,
581 amount_ordered, -- <SERVICES FPJ>
582 rate,
583 rate_date,
584 req_distribution_id,
585 deliver_to_location_id,
586 deliver_to_person_id,
587 encumbered_flag,
588 gl_encumbered_date,
589 gl_encumbered_period_name,
590 destination_type_code,
591 destination_organization_id,
592 destination_subinventory,
593 budget_account_id,
594 accrual_account_id,
595 variance_account_id,
596 wip_entity_id,
597 wip_line_id,
598 wip_repetitive_schedule_id,
599 wip_operation_seq_num,
600 wip_resource_seq_num,
601 bom_resource_id,
602 prevent_encumbrance_flag,
603 project_id,
604 task_id,
605 end_item_unit_number,
606 expenditure_type,
607 project_accounting_context,
608 destination_context,
609 expenditure_organization_id,
610 expenditure_item_date
611 )
612 values(x_interface_header_id,
613 x_interface_line_id,
614 po_distributions_interface_s.nextval,
615 1, --prd.distribution_num,
616 x_charge_account_id, --prd.code_combination_id,
617 x_def_sob_id, --prd.set_of_books_id,
618 x_quantity,
619 x_amount, -- <SERVICES FPJ>
620 x_rate,
621 x_rate_date,
622 null, --prd.distribution_id, no ref to a req.
623 x_ship_to_location_id,--x_destination_locatin_id
624 x_deliver_to_person_id,
625 x_po_encumbrance_flag, --prd.encumbered_flag,
626 l_gl_encumbered_date,
627 l_gl_encumbered_period,
628 x_destination_type_code,
629 l_ship_to_organization_id, --prl.destination_organization_id,
630 l_destination_subinventory,
631 x_budget_account_id,
632 x_accrual_account_id,
633 x_variance_account_id,
634 x_wip_entity_id,
635 x_wip_line_id,
636 x_wip_repetitive_schedule_id,
637 x_wip_operation_seq_num,
638 x_wip_resource_seq_num,
639 x_bom_resource_id,
640 x_prevent_encumbrance_flag,
641 x_project_id,
642 x_task_id,
643 x_end_item_unit_number,
644 x_expenditure_type,
645 x_project_accounting_context,
646 x_destination_context,
647 x_expenditure_organization_id,
648 x_expenditure_item_date
649 );
650 /*******************************************************************/
651 x_progress := '110';
652
653
654 EXCEPTION
655 WHEN others THEN
656 po_message_s.sql_error('default_po_dist_interface', X_progress, sqlcode);
657 raise;
658 END default_po_dist_interface;
659
660
661 PROCEDURE handle_sourcing_attachments(
662 x_auction_header_id IN NUMBER,
663 x_auction_line_number IN NUMBER,
664 x_bid_number IN NUMBER,
665 x_bid_line_number IN NUMBER,
666 x_requisition_header_id IN NUMBER,
667 x_requisition_line_id IN NUMBER,
668 x_po_line_id IN NUMBER,
669 x_column1 IN VARCHAR2,
670 x_attch_suppress_flag IN VARCHAR2,
671 X_created_by IN NUMBER DEFAULT NULL,
672 X_last_update_login IN NUMBER DEFAULT NULL)
673 IS
674
675 x_progress varchar2(4);
676 BEGIN
677
678
679 IF x_attch_suppress_flag <>'Y' then
680
681 --<RENEG BLANKET FPI START>
682 -- Following code is commented out as the header level attchments are now
683 -- copied to po_header.
684 /*
685 --copy attachment from negotiation header to the po line
686 x_progress :='001';
687 po_negotiations_sv2.
688 copy_attachments('PON_AUCTION_HEADERS_ALL',
689 x_auction_header_id,
690 null,
691 null,
692 null,
693 null,
694 'PO_LINES',
695 x_po_line_id,
696 null,
697 null,
698 null,
699 null,
700 x_created_by,
701 x_last_update_login,
702 null,
703 null,
704 null,
705 'NEG');
706 */
707 --<RENEG BLANKET FPI END>
708
709 --copy attachment from negotiation line to the po line
710 x_progress :='002';
711 po_negotiations_sv2.
712 copy_attachments('PON_AUCTION_ITEM_PRICES_ALL',
713 x_auction_header_id,
714 x_auction_line_number,
715 '',
716 '',
717 '',
718 'PO_LINES',
719 x_po_line_id,
720 '',
721 '',
722 '',
723 '',
724 x_created_by,
725 x_last_update_login,
726 '',
727 '',
728 null,
729 'NEG');
730
731
732 --copy attachment from bid header to the po line
733 x_progress :='003';
734 po_negotiations_sv2.
735 copy_attachments('PON_BID_HEADERS',
736 x_auction_header_id,
737 x_bid_number,
738 '',
739 '',
740 '',
741 'PO_LINES',
742 x_po_line_id,
743 '',
744 '',
745 '',
746 '',
747 x_created_by,
748 x_last_update_login,
749 '',
750 '',
751 null,
752 'NEG');
753 --copy attachment from bid line to the po line
754 x_progress :='004';
755 po_negotiations_sv2.
756 copy_attachments('PON_BID_ITEM_PRICES',
757 x_auction_header_id,
758 x_bid_number,
759 x_auction_line_number,
760 -- Bug 3400627, in Sourcing the bid line attachments
761 -- are stored with the first three primary keys
762 -- (auction header id, bid number, auction line number).
763 '',
764 -- x_bid_line_number,
765 '',
766 'PO_LINES',
767 x_po_line_id,
768 '',
769 '',
770 '',
771 '',
772 x_created_by,
773 x_last_update_login,
774 '',
775 '',
776 null,
777 'NEG');
778
779
780 -- build and attach bid attributes as supplier type attachments on
781 --po/blanket line.
782 x_progress :='005';
783 add_attch_dynamic('PON_BID_ATTRIBUTES' ,
784 x_auction_header_id,
785 x_auction_line_number,
786 x_bid_number,
787 x_bid_line_number,
788 'PO_LINES',
789 x_po_line_id,
790 X_created_by,
791 X_last_update_login ,
792 null,
793 null,
794 null);
795 -- build and attach bid notes as internal to PO attachments on po/blanket line .
796 x_progress :='006';
797 add_attch_dynamic('PON_BID_BUYER_NOTES' ,
798 x_auction_header_id,
799 x_auction_line_number,
800 x_bid_number,
801 x_bid_line_number,
802 'PO_LINES',
803 x_po_line_id,
804 X_created_by,
805 X_last_update_login ,
806 null,
807 null,
808 null);
809
810 -- build and attach negotiation notes as to supplier attachments on
811 --po/blanket line.
812 x_progress :='007';
813
814 --<RENEG BLANKET FPI START>
815 /* Earlier both header and line level supplier notes were copied onto
816 on po/blanket line. Changing this so that only LINE level supplier notes
817 are copied over as attachments on po/blanket LINES
818 */
819 add_attch_dynamic('PON_AUC_SUPPLIER_LINE_NOTES' ,
820 x_auction_header_id,
821 x_auction_line_number,
822 x_bid_number,
823 x_bid_line_number,
824 'PO_LINES',
825 x_po_line_id,
826 X_created_by,
827 X_last_update_login ,
828 null,
829 null,
830 null);
831 --<RENEG BLANKET FPI END>
832
833 -- build and attach bid price elements as to supplier attachments on
834 --po/blanket line.
835 x_progress :='008';
836 add_attch_dynamic('PON_BID_TOTAL_COST' ,
837 x_auction_header_id,
838 x_auction_line_number,
839 x_bid_number,
840 x_bid_line_number,
841 'PO_LINES',
842 x_po_line_id,
843 X_created_by,
844 X_last_update_login ,
845 null,
846 null,
847 null);
848
849 -- <SERVICES FPJ START> Call to convert Job Long Description from
850 -- Negotiations Table to PO Line Attachment.
851 --
852 add_attch_dynamic
853 ( x_from_entity_name => 'PON_JOB_DETAILS'
854 , x_auction_header_id => x_auction_header_id
855 , x_auction_line_number => x_auction_line_number
856 , x_bid_number => x_bid_number
857 , x_bid_line_number => x_bid_line_number
858 , x_to_entity_name => 'PO_LINES'
859 , x_to_pk1_value => x_po_line_id
860 , x_created_by => x_created_by
861 , x_last_update_login => x_last_update_login
862 , x_program_application_id => NULL
863 , x_program_id => NULL
864 , x_request_id => NULL
865 );
866 -- <SERVICES FPJ END>
867
868 end if;
869 --copy attachment from requisition header/line to the po line,
870 --when backed by a req.
871 If x_column1='NEGREQ' then
872 x_progress :='009';
873 po_negotiations_sv2.
874 copy_attachments('REQ_HEADERS',
875 x_requisition_header_id,
876 '',
877 '',
878 '',
879 '',
880 'PO_LINES',
881 x_po_line_id,
882 '',
883 '',
884 '',
885 '',
886 x_created_by,
887 x_last_update_login,
888 '',
889 '',
890 null,
891 x_column1);
892 x_progress :='010';
893 po_negotiations_sv2.
894 copy_attachments('REQ_LINES',
895 x_requisition_line_id,
896 '',
897 '',
898 '',
899 '',
900 'PO_LINES',
901 x_po_line_id,
902 '',
903 '',
904 '',
905 '',
906 x_created_by,
907 x_last_update_login,
908 '',
909 '',
910 null,
911 x_column1);
912 end if;
913 exception
914 when others then
915 po_message_s.sql_error('handle_sourcing_attachments',x_progress,sqlcode);
916 raise;
917 end handle_sourcing_attachments;
918
919
920 -- API to copy attachments from one record to another
921 PROCEDURE copy_attachments(X_from_entity_name IN VARCHAR2,
922 X_from_pk1_value IN VARCHAR2,
923 X_from_pk2_value IN VARCHAR2 DEFAULT NULL,
924 X_from_pk3_value IN VARCHAR2 DEFAULT NULL,
925 X_from_pk4_value IN VARCHAR2 DEFAULT NULL,
926 X_from_pk5_value IN VARCHAR2 DEFAULT NULL,
927 X_to_entity_name IN VARCHAR2,
928 X_to_pk1_value IN VARCHAR2,
929 X_to_pk2_value IN VARCHAR2 DEFAULT NULL,
930 X_to_pk3_value IN VARCHAR2 DEFAULT NULL,
931 X_to_pk4_value IN VARCHAR2 DEFAULT NULL,
932 X_to_pk5_value IN VARCHAR2 DEFAULT NULL,
933 X_created_by IN NUMBER DEFAULT NULL,
934 X_last_update_login IN NUMBER DEFAULT NULL,
935 X_program_application_id IN NUMBER DEFAULT NULL,
936 X_program_id IN NUMBER DEFAULT NULL,
937 X_request_id IN NUMBER DEFAULT NULL,
938 X_column1 IN VARCHAR2 DEFAULT NULL) IS
939
940 /*
941 Bug 5938614 : UNABLE TO CREATE STANDARD PO FROM SOURCING RFQ WHEN MULTIPLE REQUISITIONS USED ,
942 This is because when we create a sourcing RFQ that combines 2 req lines from 2 different requisitions
943 which are having the one time attachement , and if we publish a negotiation then it is inserting 2 lines
944 into fnd_attached_documents with pk1_value as negotiation number,and pk2_value
945 as requisition line number.
946 Before this fix , At the time of PO Creation ,the one time address is being copied from
947 entity type 'PON_AUCTION_ITEM_PRICES_ALL' which is causing the problem.
948 For a single req line it is inserting two rows into fnd_attached_documents because
949 the below cursor returning two rows while selecting from entity type 'PON_AUCTION_ITEM_PRICES_ALL'.
950
951 Modified the query so that the one time attachments will be copied from entity type 'REQ_LINES' .
952
953 */
954
955 CURSOR doclist IS
956 SELECT fad.seq_num, fad.document_id,
957 fad.attribute_category, fad.attribute1, fad.attribute2,
958 fad.attribute3, fad.attribute4, fad.attribute5,
959 fad.attribute6, fad.attribute7, fad.attribute8,
960 fad.attribute9, fad.attribute10, fad.attribute11,
961 fad.attribute12, fad.attribute13, fad.attribute14,
962 fad.attribute15, fad.column1, fad.automatically_added_flag,
963 fdvl.datatype_id, fdvl.category_id, fdvl.security_type, fdvl.security_id,
964 fdvl.publish_flag, fdvl.image_type, fdvl.storage_type,
965 fdvl.usage_type, fdvl.start_date_active, fdvl.end_date_active,
966 userenv('LANG') language, fdvl.description, fdvl.file_name,
967 fdvl.media_id, --bug 4620207: get media_id from fd table
968 fdvl.doc_attribute_category dattr_cat,
969 fdvl.doc_attribute1 dattr1, fdvl.doc_attribute2 dattr2,
970 fdvl.doc_attribute3 dattr3, fdvl.doc_attribute4 dattr4,
971 fdvl.doc_attribute5 dattr5, fdvl.doc_attribute6 dattr6,
972 fdvl.doc_attribute7 dattr7, fdvl.doc_attribute8 dattr8,
973 fdvl.doc_attribute9 dattr9, fdvl.doc_attribute10 dattr10,
974 fdvl.doc_attribute11 dattr11, fdvl.doc_attribute12 dattr12,
975 fdvl.doc_attribute13 dattr13, fdvl.doc_attribute14 dattr14,
976 fdvl.doc_attribute15 dattr15,
977 fdvl.title, fdvl.url -- Bug 5000065
978 FROM fnd_attached_documents fad,
979 fnd_documents_vl fdvl
980 WHERE fad.document_id = fdvl.document_id
981 AND fad.entity_name = X_from_entity_name
982 AND fad.pk1_value = X_from_pk1_value
983 AND (X_from_pk2_value IS NULL
984 OR fad.pk2_value = X_from_pk2_value)
985 AND (X_from_pk3_value IS NULL
986 OR fad.pk3_value = X_from_pk3_value)
987 AND (X_from_pk4_value IS NULL
988 OR fad.pk4_value = X_from_pk4_value)
989 AND (X_from_pk5_value IS NULL
990 OR fad.pk5_value = X_from_pk5_value)
991 --5938614
992 AND ((X_column1 = 'NEGREQ' and (nvl(fdvl.category_id,-99) <> g_cat_TO_SUPPLIER or fdvl.description LIKE 'POR:%'))
993 or (X_column1='NEG' AND fdvl.description NOT LIKE 'POR:%'))
994 --5938614
995 AND ((X_column1 = 'NEGREQ')
996 or
997 ((X_column1='NEG') and
998 nvl(fad.column1,'NOVAL') <> 'MTL_SYSTEM_ITEMS'
999 ));
1000
1001 CURSOR shorttext (mid NUMBER) IS
1002 SELECT short_text
1003 FROM fnd_documents_short_text
1004 WHERE media_id = mid;
1005
1006 CURSOR longtext (mid NUMBER) IS
1007 SELECT long_text
1008 FROM fnd_documents_long_text
1009 WHERE media_id = mid;
1010
1011 CURSOR fnd_lobs_cur (mid NUMBER) IS
1012 SELECT file_id,
1013 file_name,
1014 file_content_type,
1015 upload_date,
1016 expiration_date,
1017 program_name,
1018 program_tag,
1019 file_data,
1020 language,
1021 oracle_charset,
1022 file_format
1023 FROM fnd_lobs
1024 WHERE file_id = mid;
1025
1026 media_id_tmp NUMBER;
1027 document_id_tmp NUMBER;
1028 row_id_tmp VARCHAR2(30);
1029 short_text_tmp Fnd_Documents_Short_Text.short_text%type ; /* Bug 4522511 */
1030 long_text_tmp LONG;
1031 fnd_lobs_rec fnd_lobs_cur%ROWTYPE;
1032 x_category_id_tmp fnd_documents.category_id%TYPE;
1033 x_language_temp fnd_documents_tl.language%TYPE;
1034 x_progress varchar2(4);
1035
1036 --<RENEG BLANKET FPI>
1037 l_intern_sourcing_cat_id fnd_documents.category_id%TYPE;
1038
1039 BEGIN
1040 -- Use cursor loop to get all attachments associated with
1041 -- the from_entity
1042 x_progress :='001';
1043 FOR docrec IN doclist LOOP
1044
1045 --<RENEG BLANKET FPI START>
1046 --Get the category id of Internal attachments to Sourcing
1047 select category_id
1048 into l_intern_sourcing_cat_id
1049 from fnd_document_categories
1050 where name='InternaltoSourcing';
1051 --<RENEG BLANKET FPI END>
1052
1053 -- Added the IF clause: Need not copy attachments that
1054 -- are internal to Sourcing
1055 --<RENEG BLANKET FPI>
1056 if (docrec.category_id <> l_intern_sourcing_cat_id) then
1057
1058 -- One-Time docs that Short Text or Long Text will have
1059 -- to be copied into a new document (Long Text will be
1060 -- truncated to 32K). Create the new document records
1061 -- before creating the attachment record
1062 --
1063 IF (docrec.usage_type = 'O'
1064 AND docrec.datatype_id IN (1,2,5,6) ) THEN
1065 -- Create Documents records
1066 x_language_temp := docrec.language;
1067
1068 --<RENEG BLANKET FPI START>
1069 /* The category_id should be taken from document except for bid
1070 items which are copied as Internal to PO line */
1071 -- <Complex Work R12> : Copy bid payitem attachments as internal.
1072 if X_from_entity_name in ('PON_BID_HEADERS',
1073 'PON_BID_ITEM_PRICES',
1074 'PON_BID_PAYMENTS_SHIPMENTS') then
1075 x_category_id_tmp:=39;
1076 else
1077 x_category_id_tmp:=docrec.category_id;
1078 end if;
1079
1080 -- Code prior to FPI where Sourcing did not support category_id
1081 -- so commenting it out
1082 /*
1083 if X_from_entity_name in ('PON_AUCTION_HEADERS_ALL',
1084 'PON_AUCTION_ITEM_PRICES_ALL') then
1085 x_category_id_tmp:= g_cat_TO_SUPPLIER -- <Complex Work R12>
1086 elsif X_from_entity_name in ('PON_BID_HEADERS',
1087 'PON_BID_ITEM_PRICES') then
1088 x_category_id_tmp:=39;
1089 else
1090 x_category_id_tmp:=docrec.category_id;
1091 end if;
1092 */
1093 --<RENEG BLANKET FPI END>
1094
1095 x_progress :='002';
1096 FND_DOCUMENTS_PKG.Insert_Row(row_id_tmp,
1097 document_id_tmp,
1098 SYSDATE,
1099 NVL(X_created_by,0),
1100 SYSDATE,
1101 NVL(X_created_by,0),
1102 X_last_update_login,
1103 docrec.datatype_id,
1104 -- docrec.category_id,
1105 x_category_id_tmp,
1106 docrec.security_type,
1107 docrec.security_id,
1108 docrec.publish_flag,
1109 docrec.image_type,
1110 docrec.storage_type,
1111 docrec.usage_type,
1112 docrec.start_date_active,
1113 docrec.end_date_active,
1114 X_request_id,
1115 X_program_application_id,
1116 X_program_id,
1117 SYSDATE,
1118 x_language_temp, --docrec.language,
1119 docrec.description,--x_description_tmp
1120 docrec.file_name,
1121 media_id_tmp,
1122 docrec.dattr_cat, docrec.dattr1,
1123 docrec.dattr2, docrec.dattr3,
1124 docrec.dattr4, docrec.dattr5,
1125 docrec.dattr6, docrec.dattr7,
1126 docrec.dattr8, docrec.dattr9,
1127 docrec.dattr10, docrec.dattr11,
1128 docrec.dattr12, docrec.dattr13,
1129 docrec.dattr14, docrec.dattr15,
1130 -- Bug 5000065 START
1131 -- Copy the URL/title (for web page attachments)
1132 'N', -- x_create_doc
1133 docrec.url,
1134 docrec.title
1135 -- Bug 5000065 END
1136 );
1137
1138 -- overwrite document_id from original
1139 -- cursor for later insert into
1140 -- fnd_attached_documents
1141 docrec.document_id := document_id_tmp;
1142
1143 -- Duplicate short or long text
1144 IF (docrec.datatype_id = 1) THEN
1145 -- Handle short Text
1146 -- get original data
1147 x_progress :='003';
1148 OPEN shorttext(docrec.media_id);
1149 FETCH shorttext INTO short_text_tmp;
1150 CLOSE shorttext;
1151
1152 x_progress :='004';
1153 INSERT INTO fnd_documents_short_text (
1154 media_id,
1155 short_text)
1156 VALUES (
1157 media_id_tmp,
1158 short_text_tmp);
1159 ELSIF (docrec.datatype_id = 2) THEN
1160 -- Handle long text
1161 -- get original data
1162 x_progress :='005';
1163 OPEN longtext(docrec.media_id);
1164 FETCH longtext INTO long_text_tmp;
1165 CLOSE longtext;
1166
1167 x_progress :='006';
1168 INSERT INTO fnd_documents_long_text (
1169 media_id,
1170 long_text)
1171 VALUES (
1172 media_id_tmp,
1173 long_text_tmp);
1174
1175 ELSIF (docrec.datatype_id=6) THEN
1176
1177 x_progress :='007';
1178 OPEN fnd_lobs_cur(docrec.media_id);
1179 FETCH fnd_lobs_cur
1180 INTO fnd_lobs_rec.file_id,
1181 fnd_lobs_rec.file_name,
1182 fnd_lobs_rec.file_content_type,
1183 fnd_lobs_rec.upload_date,
1184 fnd_lobs_rec.expiration_date,
1185 fnd_lobs_rec.program_name,
1186 fnd_lobs_rec.program_tag,
1187 fnd_lobs_rec.file_data,
1188 fnd_lobs_rec.language,
1189 fnd_lobs_rec.oracle_charset,
1190 fnd_lobs_rec.file_format;
1191 CLOSE fnd_lobs_cur;
1192
1193 x_progress :='008';
1194 INSERT INTO fnd_lobs (
1195 file_id,
1196 file_name,
1197 file_content_type,
1198 upload_date,
1199 expiration_date,
1200 program_name,
1201 program_tag,
1202 file_data,
1203 language,
1204 oracle_charset,
1205 file_format)
1206 VALUES (
1207 media_id_tmp,
1208 fnd_lobs_rec.file_name,
1209 fnd_lobs_rec.file_content_type,
1210 fnd_lobs_rec.upload_date,
1211 fnd_lobs_rec.expiration_date,
1212 fnd_lobs_rec.program_name,
1213 fnd_lobs_rec.program_tag,
1214 fnd_lobs_rec.file_data,
1215 fnd_lobs_rec.language,
1216 fnd_lobs_rec.oracle_charset,
1217 fnd_lobs_rec.file_format);
1218
1219 media_id_tmp := '';
1220
1221 END IF; -- end of duplicating text
1222 END IF; -- end if usage_type = 'O' and datatype in (1,2,6)
1223
1224 -- Create attachment record
1225 x_progress :='009';
1226 INSERT INTO fnd_attached_documents
1227 (attached_document_id,
1228 document_id,
1229 creation_date,
1230 created_by,
1231 last_update_date,
1232 last_updated_by,
1233 last_update_login,
1234 seq_num,
1235 entity_name,
1236 pk1_value, pk2_value, pk3_value,
1237 pk4_value, pk5_value,
1238 automatically_added_flag,
1239 program_application_id, program_id,
1240 program_update_date, request_id,
1241 attribute_category, attribute1,
1242 attribute2, attribute3, attribute4,
1243 attribute5, attribute6, attribute7,
1244 attribute8, attribute9, attribute10,
1245 attribute11, attribute12, attribute13,
1246 attribute14, attribute15, column1) VALUES
1247 (fnd_attached_documents_s.nextval,
1248 docrec.document_id,
1249 sysdate,
1250 NVL(X_created_by,0),
1251 sysdate,
1252 NVL(X_created_by,0),
1253 X_last_update_login,
1254 docrec.seq_num,
1255 X_to_entity_name,
1256 X_to_pk1_value, X_to_pk2_value, X_to_pk3_value,
1257 X_to_pk4_value, X_to_pk5_value,
1258 docrec.automatically_added_flag,
1259 X_program_application_id, X_program_id,
1260 sysdate, X_request_id,
1261 docrec.attribute_category, docrec.attribute1,
1262 docrec.attribute2, docrec.attribute3,
1263 docrec.attribute4, docrec.attribute5,
1264 docrec.attribute6, docrec.attribute7,
1265 docrec.attribute8, docrec.attribute9,
1266 docrec.attribute10, docrec.attribute11,
1267 docrec.attribute12, docrec.attribute13,
1268 docrec.attribute14, docrec.attribute15,
1269 docrec.column1);
1270
1271 -- Update the document to be a std document if it
1272 -- was an ole or image that wasn't already a std doc
1273 -- (images should be created as Std, but just in case)
1274 IF (docrec.datatype_id IN (3,4)
1275 AND docrec.usage_type <> 'S') THEN
1276 UPDATE fnd_documents
1277 SET usage_type = 'S'
1278 WHERE document_id = docrec.document_id;
1279 END IF;
1280 --<RENEG BLANKET FPI>
1281 END IF; -- end of not including 'Internal to Sourcing' attachments
1282 END LOOP; -- end of working through all attachments
1283
1284 EXCEPTION WHEN OTHERS THEN
1285
1286 CLOSE shorttext;
1287 CLOSE longtext;
1288 CLOSE fnd_lobs_cur;
1289 po_message_s.sql_error('copy_attachments',x_progress, sqlcode);
1290 raise;
1291 END copy_attachments;
1292
1293 PROCEDURE add_attch_dynamic(
1294 x_from_entity_name IN VARCHAR2
1295 , x_auction_header_id IN NUMBER
1296 , x_auction_line_number IN NUMBER
1297 , x_bid_number IN NUMBER
1298 , x_bid_line_number IN NUMBER
1299 , x_to_entity_name IN VARCHAR2
1300 , x_to_pk1_value IN VARCHAR2
1301 , x_created_by IN NUMBER DEFAULT NULL
1302 , x_last_update_login IN NUMBER DEFAULT NULL
1303 , x_program_application_id IN NUMBER DEFAULT NULL
1304 , x_program_id IN NUMBER DEFAULT NULL
1305 , x_request_id IN NUMBER DEFAULT NULL
1306 , p_auction_payment_id IN NUMBER DEFAULT NULL -- <Complex Work R12>
1307 )
1308 IS
1309
1310 media_id_tmp NUMBER;
1311 document_id_tmp NUMBER;
1312 row_id_tmp VARCHAR2(30);
1313 x_category_id_tmp fnd_documents.category_id%TYPE;
1314 x_security_id NUMBER;
1315 x_seq_num NUMBER :=0;
1316 x_language_temp fnd_documents_tl.language%TYPE;
1317 x_datatype_id_tmp NUMBER;
1318 x_description_tmp fnd_documents_tl.description%TYPE;
1319 l_text long;
1320 l_who_rec PO_NEGOTIATIONS_SV2.who_rec_type;
1321
1322
1323 x_errorcode varchar2(10);
1324 x_errormessage varchar2(255);
1325 x_progress varchar2(4);
1326 pon_get_attachment_exception exception;
1327
1328 d_module VARCHAR2(70) := 'po.plsql.PO_NEGOTIATIONS_SV2.add_attch_dynamic';
1329
1330 BEGIN
1331
1332 -- <Complex Work R12 Start>: Added logging
1333 IF (PO_LOG.d_proc) THEN
1334 PO_LOG.proc_begin(d_module);
1335 PO_LOG.proc_begin(d_module, 'x_from_entity_name', x_from_entity_name);
1336 PO_LOG.proc_begin(d_module, 'x_auction_header_id', x_auction_header_id);
1337 PO_LOG.proc_begin(d_module, 'x_auction_line_number', x_auction_line_number);
1338 PO_LOG.proc_begin(d_module, 'x_bid_number', x_bid_number);
1339 PO_LOG.proc_begin(d_module, 'x_bid_line_number', x_bid_line_number);
1340 PO_LOG.proc_begin(d_module, 'x_to_entity_name', x_to_entity_name);
1341 PO_LOG.proc_begin(d_module, 'x_to_pk1_value', x_to_pk1_value);
1342 PO_LOG.proc_begin(d_module, 'p_auction_payment_id', p_auction_payment_id);
1343 END IF;
1344 -- <Complex Work R12 End>
1345
1346
1347 -- One-Time docs that Short Text or Long Text will have
1348 -- to be copied into a new document (Long Text will be
1349 -- truncated to 32K). Create the new document records
1350 -- before creating the attachment record
1351 --
1352
1353 -- Create Documents records
1354 x_progress :='000';
1355 x_language_temp := userenv('LANG');
1356 x_security_id := po_moac_utils_pvt.get_current_org_id; --<R12 MOAC>
1357 if X_from_entity_name in ('PON_BID_ATTRIBUTES') then
1358 x_progress :='001';
1359 x_category_id_tmp := g_cat_TO_SUPPLIER; -- <Complex Work R12>
1360 pon_auction_po_pkg.get_attachment( x_auction_header_id,
1361 x_bid_number,
1362 x_bid_line_number,
1363 X_from_entity_name,
1364 x_description_tmp,
1365 l_text,
1366 x_errorcode,
1367 x_errormessage);
1368 --<RENEG BLANKET FPI >
1369 elsif X_from_entity_name in ('PON_AUC_SUPPLIER_LINE_NOTES',
1370 'PON_AUC_SUPPLIER_HEADER_NOTES') then
1371 x_progress :='002';
1372 x_category_id_tmp := g_cat_TO_SUPPLIER; -- <Complex Work R12>
1373 pon_auction_po_pkg.get_attachment( x_auction_header_id,
1374 x_bid_number,
1375 x_bid_line_number,
1376 X_from_entity_name,
1377 x_description_tmp,
1378 l_text,
1379 x_errorcode,
1380 x_errormessage);
1381 -- <Complex Work R12 Start>
1382 ELSIF (X_from_entity_name = 'PON_AUC_PYMNT_SHIP_SUPP_NOTES') THEN
1383
1384 x_category_id_tmp := g_cat_TO_SUPPLIER; -- to supplier
1385
1386 pon_auction_po_pkg.get_attachment(
1387 pk1 => p_auction_payment_id
1388 , pk2 => NULL
1389 , pk3 => NULL
1390 , attachmenttype => X_from_entity_name
1391 , attachmentdesc => x_description_tmp
1392 , attachment => l_text
1393 , error_code => x_errorcode
1394 , error_msg => x_errormessage
1395 );
1396
1397 -- <Complex Work R12 End>
1398 elsif X_from_entity_name in ('PON_BID_BUYER_NOTES') then
1399 x_progress :='003';
1400 x_category_id_tmp :=39;--Internal to PO,to buyer from sourcing perspecti
1401 pon_auction_po_pkg.get_attachment( x_auction_header_id,
1402 x_bid_number,
1403 x_bid_line_number,
1404 X_from_entity_name,
1405 x_description_tmp,
1406 l_text,
1407 x_errorcode,
1408 x_errormessage);
1409 elsif X_from_entity_name in ('PON_BID_TOTAL_COST') then
1410 x_progress :='004';
1411 x_category_id_tmp := g_cat_TO_SUPPLIER; -- <Complex Work R12>
1412 pon_auction_po_pkg.get_attachment( x_auction_header_id,
1413 x_bid_number,
1414 x_bid_line_number,
1415 X_from_entity_name,
1416 x_description_tmp,
1417 l_text,
1418 x_errorcode,
1419 x_errormessage);
1420
1421 -- <SERVICES FPJ START> Extract the Job Long Description from the
1422 -- Negotiations Table into a PO Line Attachment.
1423 --
1424 ELSIF ( x_from_entity_name = 'PON_JOB_DETAILS' ) THEN
1425
1426 x_progress := '005';
1427 x_category_id_tmp := g_cat_TO_SUPPLIER; -- <Complex Work R12>
1428
1429 PON_AUCTION_PO_PKG.get_attachment
1430 ( pk1 => x_auction_header_id -- IN
1431 , pk2 => NULL -- IN
1432 , pk3 => x_auction_line_number -- IN
1433 , attachmentType => x_from_entity_name -- IN
1434 , attachmentDesc => x_description_tmp -- OUT
1435 , attachment => l_text -- OUT
1436 , error_code => x_errorcode -- OUT
1437 , error_msg => x_errormessage -- OUT
1438 );
1439 --
1440 -- <SERVICES FPJ END>
1441
1442 --Bug# 3207840. Needs to copy Bid header attributes to PO headers from FPJ.
1443 ELSIF ( x_from_entity_name = 'PON_BID_HEADER_ATTRIBUTES' ) THEN
1444
1445 x_progress := '006';
1446 x_category_id_tmp := g_cat_TO_SUPPLIER; -- <Complex Work R12>
1447
1448 PON_AUCTION_PO_PKG.get_attachment
1449 ( pk1 => x_auction_header_id -- IN
1450 , pk2 => x_bid_number -- IN
1451 , pk3 => NULL -- IN
1452 , attachmentType => x_from_entity_name -- IN
1453 , attachmentDesc => x_description_tmp -- OUT
1454 , attachment => l_text -- OUT
1455 , error_code => x_errorcode -- OUT
1456 , error_msg => x_errormessage -- OUT
1457 );
1458 end if;
1459
1460 -- <Complex Work R12 Start>: Added logging
1461 IF (PO_LOG.d_stmt) THEN
1462 PO_LOG.stmt(d_module, 10, 'x_errorcode', x_errorcode);
1463 PO_LOG.stmt(d_module, 10, 'x_errormessage', x_errormessage);
1464 PO_LOG.stmt(d_module, 10, 'l_text', l_text);
1465 END IF;
1466 -- <Complex Work R12 End>
1467
1468 --<Bug# 2288408> added checks to verify if the api returns failure raise
1469 --exception and dont process if l_text is null.
1470 if x_errorcode='FAILURE' then
1471 raise pon_get_attachment_exception;
1472 end if;
1473 if x_errorcode='SUCCESS' and l_text is not null then
1474 x_progress :='010';
1475
1476 -- <SERVICES FPJ START>
1477
1478 l_who_rec.creation_date := sysdate;
1479 l_who_rec.created_by := nvl(x_created_by, 0);
1480 l_who_rec.last_update_date := sysdate;
1481 l_who_rec.last_updated_by := nvl(x_created_by, 0);
1482 l_who_rec.last_update_login := x_last_update_login;
1483
1484 PO_NEGOTIATIONS_SV2.convert_text_to_attachment
1485 ( p_long_text => l_text
1486 , p_description => x_description_tmp
1487 , p_category_id => x_category_id_tmp
1488 , p_to_entity_name => x_to_entity_name
1489 , p_to_pk1_value => x_to_pk1_value
1490 , p_who_rec => l_who_rec
1491 );
1492 -- <SERVICES FPJ END>
1493
1494 end if;
1495
1496 EXCEPTION
1497 --Bug# 2288408
1498 WHEN pon_get_attachment_exception then
1499 po_message_s.sql_error('add_attch_dynamic',x_progress, sqlcode);
1500 raise;
1501 WHEN OTHERS THEN
1502 po_message_s.sql_error('add_attch_dynamic',x_progress, sqlcode);
1503 raise;
1504 END add_attch_dynamic;
1505
1506
1507 -----------------------------------------------------------------<SERVICES FPJ>
1508 -------------------------------------------------------------------------------
1509 --Start of Comments
1510 --Name: convert_text_to_attachment
1511 --Pre-reqs:
1512 -- None.
1513 --Modifies:
1514 -- FND_DOCUMENTS, FND_DOCUMENTS_LONG_TEXT, FND_ATTACHED_DOCUMENTS
1515 --Locks:
1516 -- None.
1517 --Function:
1518 -- Converts a LONG text to an Attachment.
1519 --Parameters:
1520 --IN:
1521 --p_long_text
1522 -- LONG text to convert.
1523 --p_description
1524 -- Attachment description.
1525 --p_category_id
1526 -- Attachment category (i.e. 33 = 'To Supplier')
1527 --p_to_entity_name
1528 -- Entity to which the Attachment is attached (i.e. 'PO_LINES')
1529 --p_to_pk1_value
1530 -- ID of the entity to which the Attachment is attached.
1531 --p_who_rec
1532 -- Record of Standard WHO columns.
1533 --Testing:
1534 -- None.
1535 --End of Comments
1536 -------------------------------------------------------------------------------
1537 -------------------------------------------------------------------------------
1538 PROCEDURE convert_text_to_attachment
1539 ( p_long_text IN LONG
1540 , p_description IN VARCHAR2
1541 , p_category_id IN NUMBER
1542 , p_to_entity_name IN VARCHAR2
1543 , p_to_pk1_value IN VARCHAR2
1544 , p_who_rec IN who_rec_type
1545 )
1546 IS
1547 l_rowid VARCHAR2(30);
1548 l_document_id NUMBER;
1549 l_security_id NUMBER;
1550 l_media_id NUMBER;
1551 l_seq_num NUMBER;
1552
1553 BEGIN
1554
1555 l_security_id := po_moac_utils_pvt.get_current_org_id; --<R12 MOAC>
1556 -- Insert into FND_DOCUMENTS ----------------------------------------------
1557
1558 FND_DOCUMENTS_PKG.insert_row
1559 ( x_rowid => l_rowid -- IN/OUT
1560 , x_document_id => l_document_id -- IN/OUT
1561 , x_creation_date => nvl(p_who_rec.creation_date, sysdate)
1562 , x_created_by => nvl(p_who_rec.created_by, 0)
1563 , x_last_update_date => nvl(p_who_rec.last_update_date, sysdate)
1564 , x_last_updated_by => nvl(p_who_rec.last_updated_by, 0)
1565 , x_last_update_login => nvl(p_who_rec.last_update_login, 0)
1566 , x_datatype_id => 2
1567 , x_category_id => p_category_id
1568 , x_security_type => 1
1569 , x_security_id => l_security_id
1570 , x_publish_flag => 'Y'
1571 , x_usage_type => 'O'
1572 , x_program_update_date => sysdate
1573 , x_language => userenv('LANG')
1574 , x_description => p_description
1575 , x_media_id => l_media_id -- IN/OUT
1576 );
1577
1578 -- Insert into FND_DOCUMENTS_LONG_TEXT ------------------------------------
1579
1580 INSERT INTO fnd_documents_long_text
1581 ( media_id
1582 , long_text
1583 )
1584 VALUES
1585 ( l_media_id
1586 , p_long_text
1587 );
1588
1589 -- Insert into FND_ATTACHED_DOCUMENTS -------------------------------------
1590
1591 SELECT max(seq_num)
1592 INTO l_seq_num
1593 FROM fnd_attached_documents
1594 WHERE pk1_value = p_to_pk1_value
1595 AND entity_name = p_to_entity_name;
1596
1597 l_seq_num := nvl(l_seq_num, 0) + 10;
1598
1599 INSERT INTO fnd_attached_documents
1600 ( attached_document_id
1601 , document_id
1602 , creation_date
1603 , created_by
1604 , last_update_date
1605 , last_updated_by
1606 , last_update_login
1607 , seq_num
1608 , entity_name
1609 , pk1_value
1610 , automatically_added_flag
1611 , program_update_date
1612 )
1613 VALUES
1614 ( FND_ATTACHED_DOCUMENTS_S.nextval
1615 , l_document_id
1616 , nvl(p_who_rec.creation_date, sysdate)
1617 , nvl(p_who_rec.created_by, 0)
1618 , nvl(p_who_rec.last_update_date, sysdate)
1619 , nvl(p_who_rec.last_updated_by, 0)
1620 , nvl(p_who_rec.last_update_login, 0)
1621 , l_seq_num
1622 , p_to_entity_name
1623 , p_to_pk1_value
1624 , 'N'
1625 , sysdate
1626 );
1627
1628 EXCEPTION
1629
1630 WHEN OTHERS THEN
1631 PO_MESSAGE_S.sql_error ( 'PO_NEGOTIATIONS_SV2.CONVERT_TEXT_TO_ATTACHMENT', '000', SQLCODE );
1632 RAISE;
1633
1634 END convert_text_to_attachment;
1635
1636 -- <Complex Work R12 Start>
1637 -- Bug 4620207: Take in more parameters
1638 PROCEDURE copy_sourcing_payitem_atts(
1639 p_line_location_id IN NUMBER
1640 , p_created_by IN NUMBER
1641 , p_last_update_login IN NUMBER
1642 , p_auction_header_id IN NUMBER
1643 , p_auction_line_number IN NUMBER
1644 , p_bid_number IN NUMBER
1645 , p_bid_line_number IN NUMBER
1646 )
1647 IS
1648 d_progress NUMBER;
1649 d_module VARCHAR2(70) := 'po.plsql.PO_NEGOTIATIONS_SV2.copy_sourcing_payitem_atts';
1650
1651 l_bid_payment_id PO_LINE_LOCATIONS_INTERFACE.bid_payment_id%TYPE;
1652 l_auction_payment_id PO_LINE_LOCATIONS_INTERFACE.auction_payment_id%TYPE;
1653
1654 BEGIN
1655
1656 d_progress := 0;
1657
1658 IF (PO_LOG.d_proc) THEN
1659 PO_LOG.proc_begin(d_module);
1660 PO_LOG.proc_begin(d_module, 'p_line_location_id', p_line_location_id);
1661 PO_LOG.proc_begin(d_module, 'p_created_by', p_created_by);
1662 PO_LOG.proc_begin(d_module, 'p_last_update_login', p_last_update_login);
1663 END IF;
1664
1665 d_progress := 10;
1666
1667 SELECT polli.bid_payment_id, polli.auction_payment_id
1668 INTO l_bid_payment_id, l_auction_payment_id
1669 FROM po_line_locations_interface polli
1670 WHERE polli.line_location_id = p_line_location_id;
1671
1672 d_progress := 20;
1673
1674 IF (l_auction_payment_id IS NOT NULL)
1675 THEN
1676
1677 d_progress := 30;
1678 IF (PO_LOG.d_stmt) THEN
1679 PO_LOG.stmt(d_module, d_progress, 'Calling copy_attachments for auction payment attachments');
1680 END IF;
1681
1682 -- Bug 4620207: pass new pk1/pk2/pk3 values to copy_attachments
1683 copy_attachments(
1684 X_from_entity_name => 'PON_AUC_PAYMENTS_SHIPMENTS'
1685 , X_from_pk1_value => p_auction_header_id
1686 , X_from_pk2_value => p_auction_line_number
1687 , X_from_pk3_value => l_auction_payment_id
1688 , X_to_entity_name => 'PO_SHIPMENTS'
1689 , X_to_pk1_value => p_line_location_id
1690 , X_created_by => p_created_by
1691 , X_last_update_login => p_last_update_login
1692 , X_column1 => 'NEG'
1693 );
1694
1695 d_progress := 40;
1696 IF (PO_LOG.d_stmt) THEN
1697 PO_LOG.stmt(d_module, d_progress, 'Calling add_attch_dynamic for auction payment attachments');
1698 END IF;
1699
1700 add_attch_dynamic(
1701 x_from_entity_name => 'PON_AUC_PYMNT_SHIP_SUPP_NOTES'
1702 , x_auction_header_id => NULL
1703 , p_auction_payment_id => l_auction_payment_id
1704 , x_auction_line_number => NULL
1705 , x_bid_number => NULL
1706 , x_bid_line_number => NULL
1707 , x_to_entity_name => 'PO_SHIPMENTS'
1708 , x_to_pk1_value => p_line_location_id
1709 , x_created_by => p_created_by
1710 , x_last_update_login => p_last_update_login
1711 , x_program_id => NULL
1712 , x_request_id => NULL
1713 );
1714
1715 END IF; -- if l_auction_payment_id IS NOT NULL
1716
1717 IF (l_bid_payment_id IS NOT NULL)
1718 THEN
1719
1720 d_progress := 50;
1721 IF (PO_LOG.d_stmt) THEN
1722 PO_LOG.stmt(d_module, d_progress, 'Calling copy_attachments for bid payment attachments');
1723 END IF;
1724
1725 -- Bug 4620207: pass new pk1/pk2/pk3 values to copy_attachments
1726 copy_attachments(
1727 X_from_entity_name => 'PON_BID_PAYMENTS_SHIPMENTS'
1728 , X_from_pk1_value => p_bid_number
1729 , X_from_pk2_value => p_bid_line_number
1730 , X_from_pk3_value => l_bid_payment_id
1731 , X_to_entity_name => 'PO_SHIPMENTS'
1732 , X_to_pk1_value => p_line_location_id
1733 , X_created_by => p_created_by
1734 , X_last_update_login => p_last_update_login
1735 , X_column1 => 'NEG'
1736 );
1737
1738 END IF; -- if l_bid_payment_id IS NOT NULL
1739
1740 IF (PO_LOG.d_proc) THEN
1741 PO_LOG.proc_end(d_module);
1742 END IF;
1743
1744 EXCEPTION
1745 WHEN OTHERS THEN
1746 IF (PO_LOG.d_exc) THEN
1747 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1748 END IF;
1749 RAISE;
1750 END copy_sourcing_payitem_atts;
1751 -- <Complex Work R12 End>
1752
1753 END PO_NEGOTIATIONS_SV2;