[Home] [Help]
29: l_count NUMBER;
30: BEGIN
31: SELECT count(*)
32: INTO l_count
33: FROM po_rcv_charges
34: WHERE shipment_line_id = p_shipment_line_id
35: AND cost_factor_id = -20;
36:
37: IF l_count < 1 THEN
81: AND rt.transaction_type = 'RECEIVE'
82: AND rt.source_document_code = 'PO'
83: AND rp.advanced_pricing = 'Y'
84: -- to exclude receipts agasint ASN
85: AND (NOT EXISTS (SELECT 1 FROM po_rcv_charges prc
86: WHERE rt.shipment_header_id = prc.shipment_header_id))
87: AND rt.po_line_location_id = pll.line_location_id
88: AND nvl(pll.lcm_flag, 'N') = 'N'
89: UNION
322: END IF;
323: l_cost_factor_details :=
324: pon_cf_type_grp.get_cost_factor_details(TO_NUMBER(l_freight_charge_tbl(n).charge_type_code));
325:
326: SELECT po_rcv_charges_s.NEXTVAL
327: INTO l_charge_table(k).charge_id
328: FROM dual;
329:
330: IF (g_asn_debug = 'Y') THEN
348: END IF;
349:
350: /* Bug#6821589:
351: include_in_acquisition_cost is used by Costing team to determine whether they have to
352: include that cost mentioned in po_rcv_charges as acquisition cost. As that value is not
353: passed, 'Acquisition cost report' is not working properly.
354: We need to stamp the cost_acquisition_code got from Cost factor setup table in
355: po_rcv_charges.include_in_acquisition_cost
356: * */
351: include_in_acquisition_cost is used by Costing team to determine whether they have to
352: include that cost mentioned in po_rcv_charges as acquisition cost. As that value is not
353: passed, 'Acquisition cost report' is not working properly.
354: We need to stamp the cost_acquisition_code got from Cost factor setup table in
355: po_rcv_charges.include_in_acquisition_cost
356: * */
357:
358: l_charge_table(k).cost_factor_id := l_cost_factor_details.price_element_type_id;
359: l_charge_table(k).allocation_method := l_cost_factor_details.allocation_basis;
409: -- Allocate all the QP charges
410: -- QP charges can be header level or line level
411: RCV_CHARGES_GRP.Allocate_charges(l_charge_table, l_charge_alloc_table, g_dummy_rci_tbl);
412:
413: -- bulk insert po_rcv_charges from the charge table
414: FORALL i IN INDICES OF l_charge_table
415: INSERT INTO po_rcv_charges
416: VALUES l_charge_table(i);
417:
411: RCV_CHARGES_GRP.Allocate_charges(l_charge_table, l_charge_alloc_table, g_dummy_rci_tbl);
412:
413: -- bulk insert po_rcv_charges from the charge table
414: FORALL i IN INDICES OF l_charge_table
415: INSERT INTO po_rcv_charges
416: VALUES l_charge_table(i);
417:
418: IF (g_asn_debug = 'Y') THEN
419: asn_debug.put_line(sql%rowcount || ' rows inserted into po_rcv_charges');
415: INSERT INTO po_rcv_charges
416: VALUES l_charge_table(i);
417:
418: IF (g_asn_debug = 'Y') THEN
419: asn_debug.put_line(sql%rowcount || ' rows inserted into po_rcv_charges');
420: END IF;
421:
422: -- bulk insert po_rcv_charge_allocations from the charge table
423: FORALL i IN INDICES OF l_charge_alloc_table
556: IF (g_asn_debug = 'Y') THEN
557: asn_debug.put_line('FTE charge exists for shipment_line: ' || j);
558: END IF;
559:
560: UPDATE po_rcv_charges
561: SET estimated_amount = ROUND(l_fte_cost_table(j).total_cost, l_precision)
562: WHERE shipment_line_id = j
563: AND estimated_amount <> ROUND(l_fte_cost_table(j).total_cost, l_precision);
564:
562: WHERE shipment_line_id = j
563: AND estimated_amount <> ROUND(l_fte_cost_table(j).total_cost, l_precision);
564:
565: IF (g_asn_debug = 'Y') THEN
566: asn_debug.put_line('updated ' || sql%rowcount || ' row in po_rcv_charges');
567: END IF;
568:
569: UPDATE po_rcv_charge_allocations
570: SET estimated_amount = ROUND(l_fte_cost_table(j).total_cost, l_precision)
578: IF (g_asn_debug = 'Y') THEN
579: asn_debug.put_line('Populating charge_table (' || k ||')');
580: END IF;
581:
582: SELECT po_rcv_charges_s.NEXTVAL
583: INTO l_charge_table(k).charge_id
584: FROM dual;
585:
586: IF (g_asn_debug = 'Y') THEN
654: -- Allocate all the FTE charges
655: -- FTE charges are always line level charges
656: RCV_CHARGES_GRP.Allocate_Charges(l_charge_table, l_charge_alloc_table, g_dummy_rci_tbl);
657:
658: -- bulk insert po_rcv_charges from the charge table
659: FORALL i IN INDICES OF l_charge_table
660: INSERT INTO po_rcv_charges
661: VALUES l_charge_table(i);
662:
656: RCV_CHARGES_GRP.Allocate_Charges(l_charge_table, l_charge_alloc_table, g_dummy_rci_tbl);
657:
658: -- bulk insert po_rcv_charges from the charge table
659: FORALL i IN INDICES OF l_charge_table
660: INSERT INTO po_rcv_charges
661: VALUES l_charge_table(i);
662:
663: IF (g_asn_debug = 'Y') THEN
664: asn_debug.put_line('Done bulk insert into po_rcv_charges');
660: INSERT INTO po_rcv_charges
661: VALUES l_charge_table(i);
662:
663: IF (g_asn_debug = 'Y') THEN
664: asn_debug.put_line('Done bulk insert into po_rcv_charges');
665: END IF;
666:
667: -- bulk insert po_rcv_charge_allocations from the charge table
668: FORALL i IN INDICES OF l_charge_alloc_table
691: , p_init_msg_list IN VARCHAR2
692: , x_return_status OUT NOCOPY VARCHAR2
693: , x_msg_count OUT NOCOPY NUMBER
694: , x_msg_data OUT NOCOPY VARCHAR2
695: , p_fte_actual_charge IN PO_RCV_CHARGES%ROWTYPE
696: ) IS
697:
698: l_new_fte_charge VARCHAR2(1) := 'Y';
699: l_shipment_line_id NUMBER;
736: -- shipment_line_id matches an existing FTE charge
737:
738: SELECT decode(count(*), 0, 'Y', 'N')
739: INTO l_new_fte_charge
740: FROM po_rcv_charges
741: WHERE shipment_line_id = p_fte_actual_charge.shipment_line_id
742: AND cost_factor_id = g_fte_cost_factor_details.price_element_type_id
743: -- if vendor_id/vendor_site_id is null, consider as an existing
744: -- charge if cost type and rsl is matched.
755: -- For existing FTE charges, update the charge with the actual cost.
756: IF l_new_fte_charge = 'N' THEN
757:
758: IF (g_asn_debug = 'Y') THEN
759: asn_debug.put_line('Updating PO_RCV_CHARGES with actual amount: '
760: || p_fte_actual_charge.actual_amount);
761: END IF;
762:
763: UPDATE po_rcv_charges
759: asn_debug.put_line('Updating PO_RCV_CHARGES with actual amount: '
760: || p_fte_actual_charge.actual_amount);
761: END IF;
762:
763: UPDATE po_rcv_charges
764: SET actual_amount = p_fte_actual_charge.actual_amount
765: , vendor_id = NVL(vendor_id, p_fte_actual_charge.vendor_id)
766: , vendor_site_id = NVL(vendor_site_id, p_fte_actual_charge.vendor_site_id)
767: WHERE shipment_line_id = p_fte_actual_charge.shipment_line_id;
777:
778: IF (g_asn_debug = 'Y') THEN
779: asn_debug.put_line('Done updating actual amounts for existing charge');
780: END IF;
781: -- For new charges, populate new rows in po_rcv_charges and po_rcv_charge_allocations.
782: ELSE
783: IF (g_asn_debug = 'Y') THEN
784: asn_debug.put_line('This is a new charge');
785: END IF;
785: END IF;
786:
787: l_fte_actual_charges(1) := p_fte_actual_charge;
788:
789: SELECT po_rcv_charges_s.nextval
790: INTO l_fte_actual_charges(1).charge_id
791: FROM dual;
792:
793: SELECT shipment_header_id
828:
829: -- Using charge table insetead of po_rcv_charge rowtype because GSCC doesn't
830: -- like insert without column list unless for bulk insert
831: FORALL i in 1..l_fte_actual_charges.COUNT
832: INSERT INTO po_rcv_charges
833: VALUES l_fte_actual_charges(i);
834:
835: IF (g_asn_debug = 'Y') THEN
836: asn_debug.put_line('Done bulk insert into po_rcv_charges');
832: INSERT INTO po_rcv_charges
833: VALUES l_fte_actual_charges(i);
834:
835: IF (g_asn_debug = 'Y') THEN
836: asn_debug.put_line('Done bulk insert into po_rcv_charges');
837: END IF;
838:
839: -- bulk insert po_rcv_charge_allocations from the charge table
840: FORALL i IN 1..l_fte_actual_charge_allocs.COUNT
934: ||l_charge_table.COUNT||' of them are new charges');
935: END IF;
936:
937:
938: -- bulk insert po_rcv_charges from the charge table
939: FORALL i IN 1..l_charge_table.COUNT
940: INSERT INTO po_rcv_charges
941: VALUES l_charge_table(i);
942:
936:
937:
938: -- bulk insert po_rcv_charges from the charge table
939: FORALL i IN 1..l_charge_table.COUNT
940: INSERT INTO po_rcv_charges
941: VALUES l_charge_table(i);
942:
943: IF (g_asn_debug = 'Y') THEN
944: asn_debug.put_line('Done bulk insert into po_rcv_charges');
940: INSERT INTO po_rcv_charges
941: VALUES l_charge_table(i);
942:
943: IF (g_asn_debug = 'Y') THEN
944: asn_debug.put_line('Done bulk insert into po_rcv_charges');
945: END IF;
946:
947: -- bulk insert po_rcv_charge_allocations from the charge table
948: FORALL i IN 1..l_charge_alloc_table.COUNT
989: )
990:
991: IS
992:
993: l_charge_id po_rcv_charges.charge_id%TYPE;
994: l_charge_allocation_id po_rcv_charge_allocations.charge_allocation_id%TYPE;
995: l_cost_factor_details pon_price_element_types_vl%ROWTYPE;
996: l_new_ap_charge VARCHAR2(1) := 'Y';
997:
1029: END IF;
1030:
1031: SELECT decode(count(*), 0, 'Y', 'N')
1032: INTO l_new_ap_charge
1033: FROM po_rcv_charges
1034: WHERE cost_factor_id = l_cost_factor_details.price_element_type_id
1035: AND shipment_header_id = l_ap_charge_distribution.shipment_header_id
1036: AND NVL(vendor_id, l_ap_charge_distribution.vendor_id)
1037: = l_ap_charge_distribution.vendor_id
1047: IF (g_asn_debug = 'Y') THEN
1048: asn_debug.put_line('Updating charge amount for existing charge');
1049: END IF;
1050:
1051: UPDATE po_rcv_charges
1052: SET actual_amount = nvl(actual_amount, 0) + l_ap_charge_distribution.amount
1053: , actual_tax = nvl(actual_tax, 0) +
1054: l_ap_charge_distribution.rec_tax + l_ap_charge_distribution.nonrec_tax
1055: , vendor_id = NVL(vendor_id, l_ap_charge_distribution.vendor_id)
1076: IF (g_asn_debug = 'Y') THEN
1077: asn_debug.put_line('Populating charge_table (' || k ||') for a new charge');
1078: END IF;
1079:
1080: SELECT po_rcv_charges_s.NEXTVAL
1081: INTO l_charge_table(k).charge_id
1082: FROM dual;
1083:
1084: IF (g_asn_debug = 'Y') THEN