[Home] [Help]
PACKAGE BODY: APPS.PO_DISTRIBUTIONS_DRAFT_PKG
Source
1 PACKAGE BODY PO_DISTRIBUTIONS_DRAFT_PKG AS
2 /* $Header: PO_DISTRIBUTIONS_DRAFT_PKG.plb 120.16.12020000.4 2013/03/05 00:57:59 pla ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_DISTRIBUTIONS_DRAFT_PKG');
6
7 -----------------------------------------------------------------------
11 --Modifies:
8 --Start of Comments
9 --Name: delete_rows
10 --Pre-reqs: None
12 --Locks:
13 -- None
14 --Function:
15 -- Deletes drafts for distribution based on the information given
16 -- If only draft_id is provided, then all distributions for the draft will be
17 -- deleted
18 -- If po_distribution_id is also provided, then the one record that has such
19 -- primary key will be deleted
20 --Parameters:
21 --IN:
22 --p_draft_id
23 -- draft unique identifier
24 --p_po_distribution_id
25 -- po distribution unique identifier
26 --IN OUT:
27 --OUT:
28 --Returns:
29 --Notes:
30 --Testing:
31 --End of Comments
32 ------------------------------------------------------------------------
33 PROCEDURE delete_rows
34 ( p_draft_id IN NUMBER,
35 p_po_distribution_id IN NUMBER
36 ) IS
37 d_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
38 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
39 d_position NUMBER;
40
41 BEGIN
42 d_position := 0;
43 IF (PO_LOG.d_proc) THEN
44 PO_LOG.proc_begin(d_module);
45 END IF;
46
47 DELETE FROM po_distributions_draft_all
48 WHERE draft_id = p_draft_id
49 AND po_distribution_id = NVL(p_po_distribution_id, po_distribution_id);
50
51 EXCEPTION
52 WHEN OTHERS THEN
53 PO_MESSAGE_S.add_exc_msg
54 ( p_pkg_name => d_pkg_name,
55 p_procedure_name => d_api_name || '.' || d_position
56 );
57 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
58 END delete_rows;
59
60 -----------------------------------------------------------------------
61 --Start of Comments
62 --Name: sync_draft_from_txn
63 --Pre-reqs: None
64 --Modifies:
65 --Locks:
66 -- None
67 --Function:
68 -- Copy data from transaction table to draft table, if the corresponding
69 -- record in draft table does not exist. It also sets the delete flag of
70 -- the draft record according to the parameter.
71 --Parameters:
72 --IN:
73 --p_po_distribution_id_tbl
74 -- table of po distribution unique identifier
75 --p_draft_id_tbl
76 -- table of draft ids this sync up will be done for
77 --p_delete_flag_tbl
78 -- table fo flags to indicate whether the draft record should be maked as
79 -- "to be deleted"
80 --IN OUT:
81 --OUT:
82 --x_record_already_exist_tbl
83 -- Returns whether the record was already in draft table or not
84 --Returns:
85 --Notes:
86 --Testing:
87 --End of Comments
88 ------------------------------------------------------------------------
89 PROCEDURE sync_draft_from_txn
90 ( p_po_distribution_id_tbl IN PO_TBL_NUMBER,
91 p_draft_id_tbl IN PO_TBL_NUMBER,
92 p_delete_flag_tbl IN PO_TBL_VARCHAR1,
93 x_record_already_exist_tbl OUT NOCOPY PO_TBL_VARCHAR1
94 ) IS
95
96 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
97 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
98 d_position NUMBER;
99
100 l_distinct_id_list DBMS_SQL.NUMBER_TABLE;
101 l_duplicate_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
102
103 BEGIN
104 d_position := 0;
105 IF (PO_LOG.d_proc) THEN
106 PO_LOG.proc_begin(d_module);
107 END IF;
108
109 x_record_already_exist_tbl :=
110 PO_DISTRIBUTIONS_DRAFT_PVT.draft_changes_exist
111 ( p_draft_id_tbl => p_draft_id_tbl,
112 p_po_distribution_id_tbl => p_po_distribution_id_tbl
113 );
114
115 -- bug5471513 START
116 -- If there're duplicate entries in the id table,
117 -- we do not want to insert multiple entries
118 -- Created an associative array to store what id has appeared.
119 l_duplicate_flag_tbl.EXTEND(p_po_distribution_id_tbl.COUNT);
120
121 FOR i IN 1..p_po_distribution_id_tbl.COUNT LOOP
122 IF (x_record_already_exist_tbl(i) = FND_API.G_FALSE) THEN
123
124 IF (l_distinct_id_list.EXISTS(p_po_distribution_id_tbl(i))) THEN
125
126 l_duplicate_flag_tbl(i) := FND_API.G_TRUE;
127 ELSE
128 l_duplicate_flag_tbl(i) := FND_API.G_FALSE;
129
130 l_distinct_id_list(p_po_distribution_id_tbl(i)) := 1;
131 END IF;
132
133 ELSE
134
135 l_duplicate_flag_tbl(i) := NULL;
136
137 END IF;
138 END LOOP;
139 -- bug5471513 END
140
141 d_position := 10;
142 IF (PO_LOG.d_stmt) THEN
143 PO_LOG.stmt(d_module, d_position, 'transfer records from txn to dft');
144 END IF;
145
146 FORALL i IN 1..p_po_distribution_id_tbl.COUNT
147 INSERT INTO po_distributions_draft_all
148 (
149 draft_id,
150 delete_flag,
151 change_accepted_flag,
152 po_distribution_id,
153 last_update_date,
154 last_updated_by,
155 po_header_id,
156 po_line_id,
157 line_location_id,
158 set_of_books_id,
159 code_combination_id,
160 quantity_ordered,
161 last_update_login,
162 creation_date,
163 created_by,
164 po_release_id,
165 quantity_delivered,
166 quantity_billed,
167 quantity_cancelled,
168 req_header_reference_num,
169 req_line_reference_num,
170 req_distribution_id,
171 deliver_to_location_id,
172 deliver_to_person_id,
173 rate_date,
177 encumbered_flag,
174 rate,
175 amount_billed,
176 accrued_flag,
178 encumbered_amount,
179 unencumbered_quantity,
180 unencumbered_amount,
181 failed_funds_lookup_code,
182 gl_encumbered_date,
183 gl_encumbered_period_name,
184 gl_cancelled_date,
185 destination_type_code,
186 destination_organization_id,
187 destination_subinventory,
188 attribute_category,
189 attribute1,
190 attribute2,
191 attribute3,
192 attribute4,
193 attribute5,
194 attribute6,
195 attribute7,
196 attribute8,
197 attribute9,
198 attribute10,
199 attribute11,
200 attribute12,
201 attribute13,
202 attribute14,
203 attribute15,
204 wip_entity_id,
205 wip_operation_seq_num,
206 wip_resource_seq_num,
207 wip_repetitive_schedule_id,
208 wip_line_id,
209 bom_resource_id,
210 budget_account_id,
211 accrual_account_id,
212 variance_account_id,
213 prevent_encumbrance_flag,
214 ussgl_transaction_code,
215 government_context,
216 destination_context,
217 distribution_num,
218 source_distribution_id,
219 request_id,
220 program_application_id,
221 program_id,
222 program_update_date,
223 project_id,
224 task_id,
225 expenditure_type,
226 project_accounting_context,
227 expenditure_organization_id,
228 gl_closed_date,
229 accrue_on_receipt_flag,
230 expenditure_item_date,
231 org_id,
232 kanban_card_id,
233 award_id,
234 mrc_rate_date,
235 mrc_rate,
236 mrc_encumbered_amount,
237 mrc_unencumbered_amount,
238 end_item_unit_number,
239 tax_recovery_override_flag,
240 recoverable_tax,
241 nonrecoverable_tax,
242 recovery_rate,
243 oke_contract_line_id,
244 oke_contract_deliverable_id,
245 amount_ordered,
246 amount_delivered,
247 amount_cancelled,
248 distribution_type,
249 amount_to_encumber,
250 invoice_adjustment_flag,
251 dest_charge_account_id,
252 dest_variance_account_id,
253 -- <Complex Work R12 Start>
254 quantity_financed,
255 amount_financed,
256 quantity_recouped,
257 amount_recouped,
258 retainage_withheld_amount,
259 retainage_released_amount,
260 -- <Complex Work R12 End>
261 tax_attribute_update_code, -- <ETAX R12>
262 group_line_id,
263 uda_template_id,
264 clm_misc_loa,
265 clm_defence_funding,
266 clm_fms_case_number,
267 clm_agency_acct_identifier,
268 partial_funded_flag,
269 funded_value,
270 quantity_funded,
271 amount_funded,
272 change_in_funded_value,
273 old_quantity_ordered,
274 ACRN , -- ACRN proj
275 old_amount_ordered,
276 clm_payment_sequence_num, -- CLM Phase 4
277 revision_num, --<PAR Project>
278 amount_changed_flag --13503748 Encumbrance ER
279 )
280 SELECT
281 p_draft_id_tbl(i),
282 p_delete_flag_tbl(i),
283 NULL,
284 po_distribution_id,
285 last_update_date,
286 last_updated_by,
287 po_header_id,
288 po_line_id,
289 line_location_id,
290 set_of_books_id,
291 code_combination_id,
292 quantity_ordered,
293 last_update_login,
294 creation_date,
295 created_by,
296 po_release_id,
297 quantity_delivered,
298 quantity_billed,
299 quantity_cancelled,
300 req_header_reference_num,
301 req_line_reference_num,
302 req_distribution_id,
303 deliver_to_location_id,
304 deliver_to_person_id,
305 rate_date,
306 rate,
307 amount_billed,
308 accrued_flag,
309 encumbered_flag,
310 encumbered_amount,
311 unencumbered_quantity,
312 unencumbered_amount,
313 failed_funds_lookup_code,
314 gl_encumbered_date,
315 gl_encumbered_period_name,
316 gl_cancelled_date,
317 destination_type_code,
318 destination_organization_id,
319 destination_subinventory,
320 attribute_category,
321 attribute1,
322 attribute2,
323 attribute3,
324 attribute4,
325 attribute5,
326 attribute6,
327 attribute7,
328 attribute8,
329 attribute9,
330 attribute10,
331 attribute11,
332 attribute12,
333 attribute13,
334 attribute14,
335 attribute15,
336 wip_entity_id,
337 wip_operation_seq_num,
338 wip_resource_seq_num,
339 wip_repetitive_schedule_id,
340 wip_line_id,
341 bom_resource_id,
342 budget_account_id,
343 accrual_account_id,
344 variance_account_id,
345 prevent_encumbrance_flag,
346 ussgl_transaction_code,
347 government_context,
348 destination_context,
349 distribution_num,
350 source_distribution_id,
351 request_id,
352 program_application_id,
356 task_id,
353 program_id,
354 program_update_date,
355 project_id,
357 expenditure_type,
358 project_accounting_context,
359 expenditure_organization_id,
360 gl_closed_date,
361 accrue_on_receipt_flag,
362 expenditure_item_date,
363 org_id,
364 kanban_card_id,
365 award_id,
366 mrc_rate_date,
367 mrc_rate,
368 mrc_encumbered_amount,
369 mrc_unencumbered_amount,
370 end_item_unit_number,
371 tax_recovery_override_flag,
372 recoverable_tax,
373 nonrecoverable_tax,
374 recovery_rate,
375 oke_contract_line_id,
376 oke_contract_deliverable_id,
377 amount_ordered,
378 amount_delivered,
379 amount_cancelled,
380 distribution_type,
381 amount_to_encumber,
382 invoice_adjustment_flag,
383 dest_charge_account_id,
384 dest_variance_account_id,
385 -- <Complex Work R12 Start>
386 quantity_financed,
387 amount_financed,
388 quantity_recouped,
389 amount_recouped,
390 retainage_withheld_amount,
391 retainage_released_amount,
392 -- <Complex Work R12 End>
393 tax_attribute_update_code, -- <ETAX R12>
394 group_line_id,
395 uda_template_id,
396 clm_misc_loa,
397 clm_defence_funding,
398 clm_fms_case_number,
399 clm_agency_acct_identifier,
400 partial_funded_flag,
401 funded_value,
402 quantity_funded,
403 amount_funded,
404 0, -- CLM Partial Funding Changes
405 quantity_ordered,
406 ACRN, -- ACRN proj
407 amount_ordered,
408 clm_payment_sequence_num, --CLM Phase 4
409 Nvl(revision_num, 0), --<PAR Project>
410 amount_changed_flag --13503748 Encumbrance ER
411 FROM po_distributions_all
412 WHERE po_distribution_id = p_po_distribution_id_tbl(i)
413 AND x_record_already_exist_tbl(i) = FND_API.G_FALSE
414 AND l_duplicate_flag_tbl(i) = FND_API.G_FALSE;
415
416 d_position := 20;
417 IF (PO_LOG.d_stmt) THEN
418 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
419 END IF;
420
421 FORALL i IN 1..p_po_distribution_id_tbl.COUNT
422 UPDATE po_distributions_draft_all
423 SET delete_flag = p_delete_flag_tbl(i)
424 WHERE po_distribution_id = p_po_distribution_id_tbl(i)
425 AND draft_id = p_draft_id_tbl(i)
426 AND NVL(delete_flag, 'N') <> 'Y' -- bug5570989
427 AND x_record_already_exist_tbl(i) = FND_API.G_TRUE;
428
429 d_position := 30;
430
431 IF (PO_LOG.d_stmt) THEN
432 PO_LOG.stmt(d_module, d_position, 'update draft records that are already' ||
433 ' in draft table. Count = ' || SQL%ROWCOUNT);
434 END IF;
435
436 d_position := 40;
437
438 IF (PO_LOG.d_proc) THEN
439 PO_LOG.proc_end(d_module);
440 END IF;
441
442 EXCEPTION
443 WHEN OTHERS THEN
444 PO_MESSAGE_S.add_exc_msg
445 ( p_pkg_name => d_pkg_name,
446 p_procedure_name => d_api_name || '.' || d_position
447 );
448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
449 END sync_draft_from_txn;
450
451 -----------------------------------------------------------------------
452 --Start of Comments
453 --Name: sync_draft_from_txn
454 --Pre-reqs: None
455 --Modifies:
456 --Locks:
457 -- None
458 --Function:
459 -- Same functionality as the bulk version of this procedure
460 --Parameters:
461 --IN:
462 --p_distribution_id
463 -- distribution unique identifier
464 --p_draft_id
465 -- the draft this sync up will be done for
466 --p_delete_flag
467 -- flag to indicate whether the draft record should be maked as "to be
468 -- deleted"
469 --IN OUT:
470 --OUT:
471 --x_record_already_exist
472 -- Returns whether the record was already in draft table or not
473 --Returns:
474 --Notes:
475 --Testing:
476 --End of Comments
477 ------------------------------------------------------------------------
478 PROCEDURE sync_draft_from_txn
479 ( p_po_distribution_id IN NUMBER,
480 p_draft_id IN NUMBER,
481 p_delete_flag IN VARCHAR2,
482 x_record_already_exist OUT NOCOPY VARCHAR2
483 ) IS
484
485 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
486 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
487 d_position NUMBER;
488
489 l_record_already_exist_tbl PO_TBL_VARCHAR1;
490
491 BEGIN
492 d_position := 0;
493 IF (PO_LOG.d_proc) THEN
494 PO_LOG.proc_begin(d_module);
495 PO_LOG.proc_begin(d_module, 'p_po_distribution_id', p_po_distribution_id);
496 END IF;
497
498 sync_draft_from_txn
499 ( p_po_distribution_id_tbl => PO_TBL_NUMBER(p_po_distribution_id),
500 p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
501 p_delete_flag_tbl => PO_TBL_VARCHAR1(p_delete_flag),
502 x_record_already_exist_tbl => l_record_already_exist_tbl
503 );
504
505 x_record_already_exist := l_record_already_exist_tbl(1);
506
507 d_position := 10;
508 IF (PO_LOG.d_proc) THEN
509 PO_LOG.proc_end(d_module);
510 PO_LOG.proc_end(d_module, 'x_record_already_exist', x_record_already_exist);
511 END IF;
512
513 EXCEPTION
514 WHEN OTHERS THEN
515 PO_MESSAGE_S.add_exc_msg
516 ( p_pkg_name => d_pkg_name,
520 END sync_draft_from_txn;
517 p_procedure_name => d_api_name || '.' || d_position
518 );
519 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
521
522 -----------------------------------------------------------------------
523 --Start of Comments
524 --Name: merge_changes
525 --Pre-reqs: None
526 --Modifies:
527 --Locks:
528 -- None
529 --Function:
530 -- Merge the records in draft table to transaction table
531 -- Either insert, update or delete will be performed on top of transaction
532 -- table, depending on the delete_flag on the draft record and whether the
533 -- record already exists in transaction table
534 --
535 --Parameters:
536 --IN:
537 --p_draft_id
538 -- draft unique identifier
539 --IN OUT:
540 --OUT:
541 --Returns:
542 --Notes:
543 --Testing:
544 --End of Comments
545 ------------------------------------------------------------------------
546 PROCEDURE merge_changes
547 ( p_draft_id IN NUMBER
548 ) IS
549
550 d_api_name CONSTANT VARCHAR2(30) := 'merge_changes';
551 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
552 d_position NUMBER;
553
554 BEGIN
555 d_position := 0;
556 IF (PO_LOG.d_proc) THEN
557 PO_LOG.proc_begin(d_module);
558 END IF;
559
560 -- Since putting DELETE within MERGE statement is causing database
561 -- to thrown internal error, for now we just separate the DELETE statement.
562 -- Once this is fixed we'll move the delete statement back to the merge
563 -- statement
564
565 -- bug5187544
566 -- Delete only records that have not been rejected
567
568 DELETE FROM po_distributions_all PD
569 WHERE PD.po_distribution_id IN
570 ( SELECT PDD.po_distribution_id -- Bug 5292573
571 FROM po_distributions_draft_all PDD
572 WHERE PDD.draft_id = p_draft_id
573 AND PDD.delete_flag = 'Y'
574 AND NVL(PDD.change_accepted_flag, 'Y') = 'Y' );
575
576 -- Merge PO Distribution changes
577 -- For update case, the following columns will be skipped:
578 --PD.po_distribution_id
579 --PD.creation_date
580 --PD.created_by
581 --PD.quantity_delivered
582 --PD.quantity_billed
583 --PD.quantity_cancelled
584 --PD.request_id
585 --PD.program_application_id
586 --PD.program_id
587 --PD.program_update_date
588 --PD.mrc_rate_date
589 --PD.mrc_rate
590 --PD.mrc_encumbered_amount
591 --PD.mrc_unencumbered_amount
592 --PD.end_item_unit_number
593 --PD.recoverable_tax
594 --PD.nonrecoverable_tax
595 --PD.amount_delivered
596 --PD.amount_cancelled
597 --PD.invoice_adjustment_flag
598 -- <Complex Work R12 Start>
599 --PD.quantity_financed
600 --PD.amount_financed
601 --PD.quantity_recouped
602 --PD.amount_recouped
603 --PD.retainage_withheld_amount
604 --PD.retainage_released_amount
605 -- <Complex Work R12 End>
606 --PD.uda_template_id
607 MERGE INTO po_distributions_all PD
608 USING (
609 SELECT
610 PDD.draft_id,
611 PDD.delete_flag,
612 PDD.change_accepted_flag,
613 PDD.po_distribution_id,
614 PDD.last_update_date,
615 PDD.last_updated_by,
616 PDD.po_header_id,
617 PDD.po_line_id,
618 PDD.line_location_id,
619 PDD.set_of_books_id,
620 PDD.code_combination_id,
621 PDD.quantity_ordered,
622 PDD.last_update_login,
623 PDD.creation_date,
624 PDD.created_by,
625 PDD.po_release_id,
626 PDD.quantity_delivered,
627 PDD.quantity_billed,
628 PDD.quantity_cancelled,
629 PDD.req_header_reference_num,
630 PDD.req_line_reference_num,
631 PDD.req_distribution_id,
632 PDD.deliver_to_location_id,
633 PDD.deliver_to_person_id,
634 PDD.rate_date,
635 PDD.rate,
636 PDD.amount_billed,
637 PDD.accrued_flag,
638 PDD.encumbered_flag,
639 PDD.encumbered_amount,
640 PDD.unencumbered_quantity,
641 PDD.unencumbered_amount,
642 PDD.failed_funds_lookup_code,
643 PDD.gl_encumbered_date,
644 PDD.gl_encumbered_period_name,
645 PDD.gl_cancelled_date,
646 PDD.destination_type_code,
647 PDD.destination_organization_id,
648 PDD.destination_subinventory,
649 PDD.attribute_category,
650 PDD.attribute1,
651 PDD.attribute2,
652 PDD.attribute3,
653 PDD.attribute4,
654 PDD.attribute5,
655 PDD.attribute6,
656 PDD.attribute7,
657 PDD.attribute8,
658 PDD.attribute9,
659 PDD.attribute10,
660 PDD.attribute11,
661 PDD.attribute12,
662 PDD.attribute13,
663 PDD.attribute14,
664 PDD.attribute15,
665 PDD.wip_entity_id,
666 PDD.wip_operation_seq_num,
667 PDD.wip_resource_seq_num,
668 PDD.wip_repetitive_schedule_id,
669 PDD.wip_line_id,
670 PDD.bom_resource_id,
671 PDD.budget_account_id,
672 PDD.accrual_account_id,
673 PDD.variance_account_id,
674 PDD.prevent_encumbrance_flag,
675 PDD.ussgl_transaction_code,
676 PDD.government_context,
677 PDD.destination_context,
678 PDD.distribution_num,
679 PDD.source_distribution_id,
680 PDD.request_id,
681 PDD.program_application_id,
682 PDD.program_id,
686 PDD.expenditure_type,
683 PDD.program_update_date,
684 PDD.project_id,
685 PDD.task_id,
687 PDD.project_accounting_context,
688 PDD.expenditure_organization_id,
689 PDD.gl_closed_date,
690 PDD.accrue_on_receipt_flag,
691 PDD.expenditure_item_date,
692 PDD.org_id,
693 PDD.kanban_card_id,
694 PDD.award_id,
695 PDD.mrc_rate_date,
696 PDD.mrc_rate,
697 PDD.mrc_encumbered_amount,
698 PDD.mrc_unencumbered_amount,
699 PDD.end_item_unit_number,
700 PDD.tax_recovery_override_flag,
701 PDD.recoverable_tax,
702 PDD.nonrecoverable_tax,
703 PDD.recovery_rate,
704 PDD.oke_contract_line_id,
705 PDD.oke_contract_deliverable_id,
706 PDD.amount_ordered,
707 PDD.amount_delivered,
708 PDD.amount_cancelled,
709 PDD.distribution_type,
710 PDD.amount_to_encumber,
711 PDD.invoice_adjustment_flag,
712 PDD.dest_charge_account_id,
713 PDD.dest_variance_account_id,
714 -- <Complex Work R12 Start>
715 PDD.quantity_financed,
716 PDD.amount_financed,
717 PDD.quantity_recouped,
718 PDD.amount_recouped,
719 PDD.retainage_withheld_amount,
720 PDD.retainage_released_amount,
721 -- <Complex Work R12 End>
722 PDD.tax_attribute_update_code, -- <ETAX R12>
723 PDD.group_line_id,
724 PDD.uda_template_id,
725 PDD.clm_misc_loa,
726 PDD.clm_defence_funding,
727 PDD.clm_fms_case_number,
728 PDD.clm_agency_acct_identifier,
729 PDD.partial_funded_flag,
730 PDD.funded_value,
731 PDD.quantity_funded,
732 PDD.amount_funded,
733 PDD.change_in_funded_value,
734 PDD.ACRN, -- ACRN proj
735 PDD.clm_payment_sequence_num, -- CLM Phase 4
736 PDD.amount_changed_flag --Bug 13503748 Encumbrance ER
737 FROM po_distributions_draft_all PDD
738 WHERE PDD.draft_id = p_draft_id
739 AND NVL(PDD.change_accepted_flag, 'Y') = 'Y'
740 ) PDDV
741 ON (PD.po_distribution_id = PDDV.po_distribution_id)
742 WHEN MATCHED THEN
743 UPDATE
744 SET
745 PD.last_update_date = PDDV.last_update_date,
746 PD.last_updated_by = PDDV.last_updated_by,
747 PD.po_header_id = PDDV.po_header_id,
748 PD.po_line_id = PDDV.po_line_id,
749 PD.line_location_id = PDDV.line_location_id,
750 PD.set_of_books_id = PDDV.set_of_books_id,
751 PD.code_combination_id = PDDV.code_combination_id,
752 PD.quantity_ordered = PDDV.quantity_ordered,
753 PD.last_update_login = PDDV.last_update_login,
754 PD.po_release_id = PDDV.po_release_id,
755 PD.req_header_reference_num = PDDV.req_header_reference_num,
756 PD.req_line_reference_num = PDDV.req_line_reference_num,
757 PD.req_distribution_id = PDDV.req_distribution_id,
758 PD.deliver_to_location_id = PDDV.deliver_to_location_id,
759 PD.deliver_to_person_id = PDDV.deliver_to_person_id,
760 PD.rate_date = PDDV.rate_date,
761 PD.rate = PDDV.rate,
762 PD.amount_billed = PDDV.amount_billed,
763 PD.accrued_flag = PDDV.accrued_flag,
764 PD.encumbered_flag = PDDV.encumbered_flag,
765 PD.encumbered_amount = PDDV.encumbered_amount,
766 PD.unencumbered_quantity = PDDV.unencumbered_quantity,
767 PD.unencumbered_amount = PDDV.unencumbered_amount,
768 PD.failed_funds_lookup_code = PDDV.failed_funds_lookup_code,
769 PD.gl_encumbered_date = PDDV.gl_encumbered_date,
770 PD.gl_encumbered_period_name = PDDV.gl_encumbered_period_name,
771 PD.gl_cancelled_date = PDDV.gl_cancelled_date,
772 PD.destination_type_code = PDDV.destination_type_code,
773 PD.destination_organization_id = PDDV.destination_organization_id,
774 PD.destination_subinventory = PDDV.destination_subinventory,
775 PD.attribute_category = PDDV.attribute_category,
776 PD.attribute1 = PDDV.attribute1,
777 PD.attribute2 = PDDV.attribute2,
778 PD.attribute3 = PDDV.attribute3,
779 PD.attribute4 = PDDV.attribute4,
780 PD.attribute5 = PDDV.attribute5,
781 PD.attribute6 = PDDV.attribute6,
782 PD.attribute7 = PDDV.attribute7,
783 PD.attribute8 = PDDV.attribute8,
784 PD.attribute9 = PDDV.attribute9,
785 PD.attribute10 = PDDV.attribute10,
786 PD.attribute11 = PDDV.attribute11,
787 PD.attribute12 = PDDV.attribute12,
788 PD.attribute13 = PDDV.attribute13,
789 PD.attribute14 = PDDV.attribute14,
790 PD.attribute15 = PDDV.attribute15,
791 PD.wip_entity_id = PDDV.wip_entity_id,
792 PD.wip_operation_seq_num = PDDV.wip_operation_seq_num,
793 PD.wip_resource_seq_num = PDDV.wip_resource_seq_num,
794 PD.wip_repetitive_schedule_id = PDDV.wip_repetitive_schedule_id,
795 PD.wip_line_id = PDDV.wip_line_id,
796 PD.bom_resource_id = PDDV.bom_resource_id,
797 PD.budget_account_id = PDDV.budget_account_id,
798 PD.accrual_account_id = PDDV.accrual_account_id,
799 PD.variance_account_id = PDDV.variance_account_id,
800 PD.prevent_encumbrance_flag = PDDV.prevent_encumbrance_flag,
801 PD.ussgl_transaction_code = PDDV.ussgl_transaction_code,
802 PD.government_context = PDDV.government_context,
803 PD.destination_context = PDDV.destination_context,
804 PD.distribution_num = PDDV.distribution_num,
805 PD.source_distribution_id = PDDV.source_distribution_id,
806 PD.project_id = PDDV.project_id,
807 PD.task_id = PDDV.task_id,
811 PD.gl_closed_date = PDDV.gl_closed_date,
808 PD.expenditure_type = PDDV.expenditure_type,
809 PD.project_accounting_context = PDDV.project_accounting_context,
810 PD.expenditure_organization_id = PDDV.expenditure_organization_id,
812 PD.accrue_on_receipt_flag = PDDV.accrue_on_receipt_flag,
813 PD.expenditure_item_date = PDDV.expenditure_item_date,
814 PD.org_id = PDDV.org_id,
815 PD.kanban_card_id = PDDV.kanban_card_id,
816 PD.award_id = PDDV.award_id,
817 PD.tax_recovery_override_flag = PDDV.tax_recovery_override_flag,
818 PD.recovery_rate = PDDV.recovery_rate,
819 PD.oke_contract_line_id = PDDV.oke_contract_line_id,
820 PD.oke_contract_deliverable_id = PDDV.oke_contract_deliverable_id,
821 PD.amount_ordered = PDDV.amount_ordered,
822 PD.distribution_type = PDDV.distribution_type,
823 PD.amount_to_encumber = PDDV.amount_to_encumber,
824 PD.dest_charge_account_id = PDDV.dest_charge_account_id,
825 PD.dest_variance_account_id = PDDV.dest_variance_account_id,
826 PD.tax_attribute_update_code = PDDV.tax_attribute_update_code, -- <ETAX R12>
827 PD.group_line_id = PDDV.group_line_id,
828 PD.clm_misc_loa = PDDV.clm_misc_loa,
829 PD.clm_defence_funding = PDDV.clm_defence_funding,
830 PD.clm_fms_case_number = PDDV.clm_fms_case_number,
831 PD.clm_agency_acct_identifier = PDDV.clm_agency_acct_identifier,
832 PD.partial_funded_flag = PDDV.partial_funded_flag,
833 PD.funded_value = PDDV.funded_value,
834 PD.quantity_funded = PDDV.quantity_funded,
835 PD.amount_funded = PDDV.amount_funded,
836 PD.change_in_funded_value = PDDV.change_in_funded_value,
837 PD.ACRN = PDDV.ACRN, --ACRN proj
838 PD.clm_payment_sequence_num = PDDV.clm_payment_sequence_num, -- CLM phase 4
839 pd.amount_changed_flag =pddv.amount_changed_flag -- Bug 13503748
840 -- DELETE WHERE PDDV.delete_flag = 'Y'
841 WHEN NOT MATCHED THEN
842 INSERT
843 (
844 PD.po_distribution_id,
845 PD.last_update_date,
846 PD.last_updated_by,
847 PD.po_header_id,
848 PD.po_line_id,
849 PD.line_location_id,
850 PD.set_of_books_id,
851 PD.code_combination_id,
852 PD.quantity_ordered,
853 PD.last_update_login,
854 PD.creation_date,
855 PD.created_by,
856 PD.po_release_id,
857 PD.quantity_delivered,
858 PD.quantity_billed,
859 PD.quantity_cancelled,
860 PD.req_header_reference_num,
861 PD.req_line_reference_num,
862 PD.req_distribution_id,
863 PD.deliver_to_location_id,
864 PD.deliver_to_person_id,
865 PD.rate_date,
866 PD.rate,
867 PD.amount_billed,
868 PD.accrued_flag,
869 PD.encumbered_flag,
870 PD.encumbered_amount,
871 PD.unencumbered_quantity,
872 PD.unencumbered_amount,
873 PD.failed_funds_lookup_code,
874 PD.gl_encumbered_date,
875 PD.gl_encumbered_period_name,
876 PD.gl_cancelled_date,
877 PD.destination_type_code,
878 PD.destination_organization_id,
879 PD.destination_subinventory,
880 PD.attribute_category,
881 PD.attribute1,
882 PD.attribute2,
883 PD.attribute3,
884 PD.attribute4,
885 PD.attribute5,
886 PD.attribute6,
887 PD.attribute7,
888 PD.attribute8,
889 PD.attribute9,
890 PD.attribute10,
891 PD.attribute11,
892 PD.attribute12,
893 PD.attribute13,
894 PD.attribute14,
895 PD.attribute15,
896 PD.wip_entity_id,
897 PD.wip_operation_seq_num,
898 PD.wip_resource_seq_num,
899 PD.wip_repetitive_schedule_id,
900 PD.wip_line_id,
901 PD.bom_resource_id,
902 PD.budget_account_id,
903 PD.accrual_account_id,
904 PD.variance_account_id,
905 PD.prevent_encumbrance_flag,
906 PD.ussgl_transaction_code,
907 PD.government_context,
908 PD.destination_context,
909 PD.distribution_num,
910 PD.source_distribution_id,
911 PD.request_id,
912 PD.program_application_id,
913 PD.program_id,
914 PD.program_update_date,
915 PD.project_id,
916 PD.task_id,
917 PD.expenditure_type,
918 PD.project_accounting_context,
919 PD.expenditure_organization_id,
920 PD.gl_closed_date,
921 PD.accrue_on_receipt_flag,
922 PD.expenditure_item_date,
923 PD.org_id,
924 PD.kanban_card_id,
925 PD.award_id,
926 PD.mrc_rate_date,
927 PD.mrc_rate,
928 PD.mrc_encumbered_amount,
929 PD.mrc_unencumbered_amount,
930 PD.end_item_unit_number,
931 PD.tax_recovery_override_flag,
932 PD.recoverable_tax,
933 PD.nonrecoverable_tax,
934 PD.recovery_rate,
935 PD.oke_contract_line_id,
936 PD.oke_contract_deliverable_id,
937 PD.amount_ordered,
938 PD.amount_delivered,
939 PD.amount_cancelled,
940 PD.distribution_type,
941 PD.amount_to_encumber,
942 PD.invoice_adjustment_flag,
943 PD.dest_charge_account_id,
944 PD.dest_variance_account_id,
945 -- <Complex Work R12 Start>
946 PD.quantity_financed,
947 PD.amount_financed,
948 PD.quantity_recouped,
949 PD.amount_recouped,
950 PD.retainage_withheld_amount,
951 PD.retainage_released_amount,
952 -- <Complex Work R12 End>
956 PD.clm_misc_loa,
953 PD.tax_attribute_update_code, -- <ETAX R12>
954 PD.group_line_id,
955 PD.uda_template_id,
957 PD.clm_defence_funding,
958 PD.clm_fms_case_number,
959 PD.clm_agency_acct_identifier,
960 PD.partial_funded_flag,
961 PD.funded_value,
962 PD.quantity_funded,
963 PD.amount_funded,
964 PD.change_in_funded_value,
965 PD.ACRN, --ACRN proj
966 PD.clm_payment_sequence_num, -- CLM phase 4
967 PD.amount_changed_flag --Bug 13503748 Encumbrance ER
968 )
969 VALUES
970 (
971 PDDV.po_distribution_id,
972 PDDV.last_update_date,
973 PDDV.last_updated_by,
974 PDDV.po_header_id,
975 PDDV.po_line_id,
976 PDDV.line_location_id,
977 PDDV.set_of_books_id,
978 PDDV.code_combination_id,
979 PDDV.quantity_ordered,
980 PDDV.last_update_login,
981 PDDV.creation_date,
982 PDDV.created_by,
983 PDDV.po_release_id,
984 PDDV.quantity_delivered,
985 PDDV.quantity_billed,
986 PDDV.quantity_cancelled,
987 PDDV.req_header_reference_num,
988 PDDV.req_line_reference_num,
989 PDDV.req_distribution_id,
990 PDDV.deliver_to_location_id,
991 PDDV.deliver_to_person_id,
992 PDDV.rate_date,
993 PDDV.rate,
994 PDDV.amount_billed,
995 PDDV.accrued_flag,
996 PDDV.encumbered_flag,
997 PDDV.encumbered_amount,
998 PDDV.unencumbered_quantity,
999 PDDV.unencumbered_amount,
1000 PDDV.failed_funds_lookup_code,
1001 PDDV.gl_encumbered_date,
1002 PDDV.gl_encumbered_period_name,
1003 PDDV.gl_cancelled_date,
1004 PDDV.destination_type_code,
1005 PDDV.destination_organization_id,
1006 PDDV.destination_subinventory,
1007 PDDV.attribute_category,
1008 PDDV.attribute1,
1009 PDDV.attribute2,
1010 PDDV.attribute3,
1011 PDDV.attribute4,
1012 PDDV.attribute5,
1013 PDDV.attribute6,
1014 PDDV.attribute7,
1015 PDDV.attribute8,
1016 PDDV.attribute9,
1017 PDDV.attribute10,
1018 PDDV.attribute11,
1019 PDDV.attribute12,
1020 PDDV.attribute13,
1021 PDDV.attribute14,
1022 PDDV.attribute15,
1023 PDDV.wip_entity_id,
1024 PDDV.wip_operation_seq_num,
1025 PDDV.wip_resource_seq_num,
1026 PDDV.wip_repetitive_schedule_id,
1027 PDDV.wip_line_id,
1028 PDDV.bom_resource_id,
1029 PDDV.budget_account_id,
1030 PDDV.accrual_account_id,
1031 PDDV.variance_account_id,
1032 PDDV.prevent_encumbrance_flag,
1033 PDDV.ussgl_transaction_code,
1034 PDDV.government_context,
1035 PDDV.destination_context,
1036 PDDV.distribution_num,
1037 PDDV.source_distribution_id,
1038 PDDV.request_id,
1039 PDDV.program_application_id,
1040 PDDV.program_id,
1041 PDDV.program_update_date,
1042 PDDV.project_id,
1043 PDDV.task_id,
1044 PDDV.expenditure_type,
1045 PDDV.project_accounting_context,
1046 PDDV.expenditure_organization_id,
1047 PDDV.gl_closed_date,
1048 PDDV.accrue_on_receipt_flag,
1049 PDDV.expenditure_item_date,
1050 PDDV.org_id,
1051 PDDV.kanban_card_id,
1052 PDDV.award_id,
1053 PDDV.mrc_rate_date,
1054 PDDV.mrc_rate,
1055 PDDV.mrc_encumbered_amount,
1056 PDDV.mrc_unencumbered_amount,
1057 PDDV.end_item_unit_number,
1058 PDDV.tax_recovery_override_flag,
1059 PDDV.recoverable_tax,
1060 PDDV.nonrecoverable_tax,
1061 PDDV.recovery_rate,
1062 PDDV.oke_contract_line_id,
1063 PDDV.oke_contract_deliverable_id,
1064 PDDV.amount_ordered,
1065 PDDV.amount_delivered,
1066 PDDV.amount_cancelled,
1067 PDDV.distribution_type,
1068 PDDV.amount_to_encumber,
1069 PDDV.invoice_adjustment_flag,
1070 PDDV.dest_charge_account_id,
1071 PDDV.dest_variance_account_id,
1072 -- <Complex Work R12 Start>
1073 PDDV.quantity_financed,
1074 PDDV.amount_financed,
1075 PDDV.quantity_recouped,
1076 PDDV.amount_recouped,
1077 PDDV.retainage_withheld_amount,
1078 PDDV.retainage_released_amount,
1079 -- <Complex Work R12 End>
1080 PDDV.tax_attribute_update_code, -- <ETAX R12>
1081 PDDV.group_line_id,
1082 PDDV.uda_template_id,
1083 PDDV.clm_misc_loa,
1084 PDDV.clm_defence_funding,
1085 PDDV.clm_fms_case_number,
1086 PDDV.clm_agency_acct_identifier,
1087 PDDV.partial_funded_flag,
1088 PDDV.funded_value,
1089 PDDV.quantity_funded,
1090 PDDV.amount_funded,
1091 PDDV.funded_value, -- CLM Partial Funding Changes
1092 PDDV.ACRN, --ACRN proj
1093 PDDV.clm_payment_sequence_num,
1094 pddv.amount_changed_flag -- Bug 13503748
1095 ) WHERE NVL(PDDV.delete_flag, 'N') <> 'Y';
1096
1097 d_position := 10;
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 PO_MESSAGE_S.add_exc_msg
1101 ( p_pkg_name => d_pkg_name,
1102 p_procedure_name => d_api_name || '.' || d_position
1103 );
1104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1105 END merge_changes;
1106
1107 -----------------------------------------------------------------------
1108 --Start of Comments
1109 --Name: lock_draft_record
1110 --Function:
1111 -- Obtain database lock for the record in draft table
1112 --Parameters:
1113 --IN:
1114 --p_po_distribution_id
1115 -- id for po distribution record
1116 --p_draft_id
1117 -- draft unique identifier
1118 --RETURN:
1119 --End of Comments
1120 ------------------------------------------------------------------------
1121 PROCEDURE lock_draft_record
1122 ( p_po_distribution_id IN NUMBER,
1123 p_draft_id IN NUMBER
1124 ) IS
1125
1126 d_api_name CONSTANT VARCHAR2(30) := 'lock_draft_record';
1127 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1128 d_position NUMBER;
1129
1130 l_dummy NUMBER;
1131
1132 BEGIN
1133 d_position := 0;
1134 IF (PO_LOG.d_proc) THEN
1135 PO_LOG.proc_begin(d_module);
1136 END IF;
1137
1138 SELECT 1
1139 INTO l_dummy
1140 FROM po_distributions_draft_all
1141 WHERE po_distribution_id = p_po_distribution_id
1142 AND draft_id = p_draft_id
1143 FOR UPDATE NOWAIT;
1144
1145 IF (PO_LOG.d_proc) THEN
1146 PO_LOG.proc_end(d_module);
1147 END IF;
1148
1149 EXCEPTION
1150 WHEN NO_DATA_FOUND THEN
1151 NULL;
1152 END lock_draft_record;
1153
1154 -----------------------------------------------------------------------
1155 --Start of Comments
1156 --Name: lock_transaction_record
1157 --Function:
1158 -- Obtain database lock for the record in transaction table
1159 --Parameters:
1160 --IN:
1161 --p_po_distribution_id
1162 -- id for po distribution record
1163 --RETURN:
1164 --End of Comments
1165 ------------------------------------------------------------------------
1166 PROCEDURE lock_transaction_record
1167 ( p_po_distribution_id IN NUMBER
1168 ) IS
1169
1170 d_api_name CONSTANT VARCHAR2(30) := 'lock_transaction_record';
1171 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1172 d_position NUMBER;
1173
1174 l_dummy NUMBER;
1175
1176 BEGIN
1177 d_position := 0;
1178 IF (PO_LOG.d_proc) THEN
1179 PO_LOG.proc_begin(d_module);
1180 END IF;
1181
1182 SELECT 1
1183 INTO l_dummy
1184 FROM po_distributions_all
1185 WHERE po_distribution_id = p_po_distribution_id
1186 FOR UPDATE NOWAIT;
1187
1188 IF (PO_LOG.d_proc) THEN
1189 PO_LOG.proc_end(d_module);
1190 END IF;
1191
1192 EXCEPTION
1193 WHEN NO_DATA_FOUND THEN
1194 NULL;
1195 END lock_transaction_record;
1196
1197 -----------------------------------------------------------------------
1198 --Start of Comments
1199 --Name: update_req_line_link
1200 --Function:
1201 -- Update po_requisition_line which were used for adding funds,
1202 -- when the PO distribution is being deleted,
1203 --Parameters:
1204 --IN:
1205 --p_req_line_id
1206 -- id for po requisition lines record
1207 --RETURN:
1208 --End of Comments
1209 ------------------------------------------------------------------------
1210 PROCEDURE update_req_line_link(p_req_line_id IN number) IS
1211 d_api_name CONSTANT VARCHAR2(30) := 'update_req_line_link';
1212 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1213 d_position NUMBER;
1214 BEGIN
1215 d_position := 0;
1216 IF (PO_LOG.d_proc) THEN
1217 PO_LOG.proc_begin(d_module);
1218 END IF;
1219
1220 UPDATE po_requisition_lines_all
1221 SET line_location_id = NULL,
1222 po_line_id = NULL,
1223 po_draft_id = NULL,
1224 linked_to_fund = NULL,
1225 reqs_in_pool_flag = 'Y',
1226 last_update_date = SYSDATE,
1227 last_updated_by = FND_GLOBAL.USER_ID,
1228 last_update_login = FND_GLOBAL.LOGIN_ID
1229 WHERE requisition_line_id = p_req_line_id;
1230
1231 IF (PO_LOG.d_proc) THEN
1232 PO_LOG.proc_end(d_module);
1233 END IF;
1234 END update_req_line_link;
1235
1236 END PO_DISTRIBUTIONS_DRAFT_PKG;