[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO l_count
FROM po_rcv_charges
WHERE shipment_line_id = p_shipment_line_id
AND cost_factor_id = -20;
SELECT shipment_header_id
BULK COLLECT INTO l_rsh_id_table
FROM (-- po receipts
SELECT rt.shipment_header_id
FROM rcv_transactions rt,
rcv_parameters rp,
po_line_locations_all pll -- lcm changes
WHERE rt.group_id = DECODE (p_group_id, 0, rt.group_id, p_group_id)
AND rt.request_id = p_request_id
AND rt.organization_id = rp.organization_id
AND rt.transaction_type = 'RECEIVE'
AND rt.source_document_code = 'PO'
AND rp.advanced_pricing = 'Y'
-- to exclude receipts agasint ASN
AND (NOT EXISTS (SELECT 1 FROM po_rcv_charges prc
WHERE rt.shipment_header_id = prc.shipment_header_id))
AND rt.po_line_location_id = pll.line_location_id
AND nvl(pll.lcm_flag, 'N') = 'N'
UNION
-- import ASN
SELECT rsh.shipment_header_id
FROM rcv_shipment_headers rsh,
rcv_headers_interface rhi,
rcv_parameters rp
WHERE rhi.group_id = DECODE(p_group_id, 0, rhi.group_id, p_group_id)
AND rsh.shipment_num = rhi.shipment_num
AND rsh.request_id = p_request_id
AND rsh.receipt_source_code = 'VENDOR'
AND rsh.ship_to_org_id = rp.organization_id
AND rp.advanced_pricing = 'Y');
SELECT PO_MOAC_UTILS_PVT.get_current_org_id,
NULL, --p_order_header_id
vendor_id,
vendor_site_id,
creation_date,
NULL, --order_type
ship_to_location_id,
ship_to_org_id,
shipment_header_id,
hazard_class,
hazard_code,
shipped_date,
shipment_num,
carrier_method,
packaging_code,
freight_carrier_code,
freight_terms,
currency_code,
conversion_rate,
conversion_rate_type,
organization_id,
expected_receipt_date
INTO l_header_rec
FROM rcv_shipment_headers
WHERE shipment_header_id = l_rsh_id;
SELECT NULL, --order_line_id
NULL, --agreement_type
NULL, --agreement_id
NULL, --agreement_line_id
pha.vendor_id, -- Bug 7186657
pha.vendor_site_id, --Bug 7186657
rsl.ship_to_location_id,
NULL, --ship_to_org_id
rsl.vendor_item_num,
rsl.item_revision,
rsl.item_id,
NULL, --category_id
pha.rate,
pha.rate_type,
pha.currency_code,
plla.need_by_date, --need_by_date
rsl.shipment_line_id,
rsl.primary_unit_of_measure,
rsl.to_organization_id,
rsl.unit_of_measure,
rsl.source_document_code,
pla.unit_price,
decode(rsl.quantity_received, 0, rsl.quantity_shipped, rsl.quantity_received)
BULK COLLECT INTO l_line_rec_table
FROM rcv_shipment_lines rsl,
po_lines_all pla,
po_headers_all pha,
po_line_locations_all plla
WHERE rsl.po_line_id = pla.po_line_id
AND rsl.po_header_id = pha.po_header_id
ANd rsl.po_line_location_id = plla.line_location_id
AND rsl.shipment_header_id = l_rsh_id
AND nvl(plla.lcm_flag,'N') = 'N'; -- lcm changes
SELECT po_rcv_charges_s.NEXTVAL
INTO l_charge_table(k).charge_id
FROM dual;
l_charge_table(k).last_update_date := SYSDATE;
l_charge_table(k).last_updated_by := FND_GLOBAL.user_id;
INSERT INTO po_rcv_charges
VALUES l_charge_table(i);
asn_debug.put_line(sql%rowcount || ' rows inserted into po_rcv_charges');
INSERT INTO po_rcv_charge_allocations
VALUES l_charge_alloc_table(i);
asn_debug.put_line(sql%rowcount || ' rows inserted into po_rcv_charge_allocations');
SELECT DISTINCT shipment_header_id
BULK COLLECT INTO l_rsh_id_table
FROM rcv_transactions rt,
rcv_parameters rp,
po_line_locations_all pll -- lcm changes
WHERE rt.group_id = decode(p_group_id, 0, rt.group_id, p_group_id)
AND rt.request_id = p_request_id -- 0 for online mode
AND rt.transaction_type = 'RECEIVE'
AND rt.source_document_code = 'PO'
AND rt.organization_id = rp.organization_id
AND rp.transportation_execution = 'Y'
AND rt.po_line_location_id = pll.line_location_id
AND nvl(pll.lcm_flag, 'N') = 'N';
UPDATE po_rcv_charges
SET estimated_amount = ROUND(l_fte_cost_table(j).total_cost, l_precision)
WHERE shipment_line_id = j
AND estimated_amount <> ROUND(l_fte_cost_table(j).total_cost, l_precision);
asn_debug.put_line('updated ' || sql%rowcount || ' row in po_rcv_charges');
UPDATE po_rcv_charge_allocations
SET estimated_amount = ROUND(l_fte_cost_table(j).total_cost, l_precision)
WHERE shipment_line_id = j
AND estimated_amount <> ROUND(l_fte_cost_table(j).total_cost, l_precision);
asn_debug.put_line('updated ' || sql%rowcount || ' row in po_rcv_charge_allocations');
SELECT po_rcv_charges_s.NEXTVAL
INTO l_charge_table(k).charge_id
FROM dual;
l_charge_table(k).last_update_date := SYSDATE;
l_charge_table(k).last_updated_by := FND_GLOBAL.user_id;
INSERT INTO po_rcv_charges
VALUES l_charge_table(i);
asn_debug.put_line('Done bulk insert into po_rcv_charges');
INSERT INTO po_rcv_charge_allocations
VALUES l_charge_alloc_table(i);
asn_debug.put_line('Done bulk insert into po_rcv_charge_allocations');
SELECT decode(count(*), 0, 'Y', 'N')
INTO l_new_fte_charge
FROM po_rcv_charges
WHERE shipment_line_id = p_fte_actual_charge.shipment_line_id
AND cost_factor_id = g_fte_cost_factor_details.price_element_type_id
-- if vendor_id/vendor_site_id is null, consider as an existing
-- charge if cost type and rsl is matched.
AND NVL(vendor_id, p_fte_actual_charge.vendor_id)
= p_fte_actual_charge.vendor_id
AND NVL(vendor_site_id, p_fte_actual_charge.vendor_site_id)
= p_fte_actual_charge.vendor_site_id;
UPDATE po_rcv_charges
SET actual_amount = p_fte_actual_charge.actual_amount
, vendor_id = NVL(vendor_id, p_fte_actual_charge.vendor_id)
, vendor_site_id = NVL(vendor_site_id, p_fte_actual_charge.vendor_site_id)
WHERE shipment_line_id = p_fte_actual_charge.shipment_line_id;
UPDATE po_rcv_charge_allocations
SET actual_amount = p_fte_actual_charge.actual_amount
WHERE shipment_line_id = p_fte_actual_charge.shipment_line_id;
SELECT po_rcv_charges_s.nextval
INTO l_fte_actual_charges(1).charge_id
FROM dual;
SELECT shipment_header_id
INTO l_fte_actual_charges(1).shipment_header_id
FROM rcv_shipment_lines
WHERE shipment_line_id = l_fte_actual_charges(1).shipment_line_id;
l_fte_actual_charges(1).last_update_date := SYSDATE;
l_fte_actual_charges(1).last_updated_by := FND_GLOBAL.user_id;
INSERT INTO po_rcv_charges
VALUES l_fte_actual_charges(i);
asn_debug.put_line('Done bulk insert into po_rcv_charges');
INSERT INTO po_rcv_charge_allocations
VALUES l_fte_actual_charge_allocs(i);
asn_debug.put_line('Done bulk insert into po_rcv_charge_allocations');
INSERT INTO po_rcv_charges
VALUES l_charge_table(i);
asn_debug.put_line('Done bulk insert into po_rcv_charges');
INSERT INTO po_rcv_charge_allocations
VALUES l_charge_alloc_table(i);
asn_debug.put_line('Done bulk insert into po_rcv_charge_allocations');
select sum(amount)
into l_ap_charge_distribution.rec_tax
from ap_invoice_distributions_all where
line_type_lookup_code = 'TAX' and
charge_applicable_to_dist_id = l_ap_charge_distribution.invoice_distribution_id;
select sum(amount)
into l_ap_charge_distribution.nonrec_tax
from ap_invoice_distributions_all where
line_type_lookup_code = 'NONREC_TAX' and
charge_applicable_to_dist_id = l_ap_charge_distribution.invoice_distribution_id;
SELECT decode(count(*), 0, 'Y', 'N')
INTO l_new_ap_charge
FROM po_rcv_charges
WHERE cost_factor_id = l_cost_factor_details.price_element_type_id
AND shipment_header_id = l_ap_charge_distribution.shipment_header_id
AND NVL(vendor_id, l_ap_charge_distribution.vendor_id)
= l_ap_charge_distribution.vendor_id
AND NVL(vendor_site_id, l_ap_charge_distribution.vendor_site_id)
= l_ap_charge_distribution.vendor_site_id;
UPDATE po_rcv_charges
SET actual_amount = nvl(actual_amount, 0) + l_ap_charge_distribution.amount
, actual_tax = nvl(actual_tax, 0) +
l_ap_charge_distribution.rec_tax + l_ap_charge_distribution.nonrec_tax
, vendor_id = NVL(vendor_id, l_ap_charge_distribution.vendor_id)
, vendor_site_id = NVL(vendor_site_id, l_ap_charge_distribution.vendor_site_id)
WHERE cost_factor_id = l_cost_factor_details.price_element_type_id
AND shipment_line_id = l_ap_charge_distribution.shipment_line_id
RETURNING charge_id INTO l_charge_id;
asn_debug.put_line('Updated PRC (charge_id=' || l_charge_id || ') with amount'||l_ap_charge_distribution.amount);
UPDATE po_rcv_charge_allocations
SET actual_amount = nvl(actual_amount,0) + l_ap_charge_distribution.amount
, act_recoverable_tax = l_ap_charge_distribution.rec_tax
, act_non_recoverable_tax = l_ap_charge_distribution.nonrec_tax
WHERE shipment_line_id = l_ap_charge_distribution.shipment_line_id
AND charge_id = l_charge_id;
asn_debug.put_line('Updated corresponding PRCA with amount '||l_ap_charge_distribution.amount);
SELECT po_rcv_charges_s.NEXTVAL
INTO l_charge_table(k).charge_id
FROM dual;
l_charge_table(k).last_update_date := SYSDATE;
l_charge_table(k).last_updated_by := FND_GLOBAL.user_id;
SELECT po_rcv_charge_allocations_s.NEXTVAL
INTO l_charge_alloc_table(k).charge_allocation_id
FROM dual;
l_charge_alloc_table(k).last_update_date := SYSDATE;
l_charge_alloc_table(k).last_updated_by := FND_GLOBAL.user_id;
UPDATE ap_invoice_distributions_all
SET rcv_charge_addition_flag = 'Y'
WHERE invoice_distribution_id = l_ap_charge_distribution.invoice_distribution_id;