[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT unit_of_measure
INTO g_base_weight_uom
FROM mtl_units_of_measure_tl
WHERE base_uom_flag = 'Y'
AND uom_class = 'Weight'
AND ROWNUM < 2;
SELECT unit_of_measure
INTO g_base_volume_uom
FROM mtl_units_of_measure_tl
WHERE base_uom_flag = 'Y'
AND uom_class = 'Volume'
AND ROWNUM < 2;
Procedure Update_Interface_Charges
( p_charge_interface_table IN charge_interface_table_type
) IS
l_interface_charge_id_tbl dbms_utility.number_array;
UPDATE rcv_charges_interface
SET ROW = p_charge_interface_table(i)
WHERE interface_charge_id = l_interface_charge_id_tbl(i);
END Update_Interface_Charges;
SELECT po_rcv_charges_s.NEXTVAL
INTO l_charge.charge_id
FROM dual;
l_charge.last_update_date := SYSDATE;
l_charge.last_updated_by := FND_GLOBAL.user_id;
SELECT po_rcv_charge_allocations_s.NEXTVAL
INTO l_charge_allocation.charge_allocation_id
FROM dual;
l_charge_allocation.last_update_date := SYSDATE;
l_charge_allocation.last_updated_by := FND_GLOBAL.user_id;
SELECT rsl.shipment_line_id
, rsl.to_organization_id
, rsl.item_id
, decode(rsl.quantity_received, 0, rsl.quantity_shipped, rsl.quantity_received)
, rsl.unit_of_measure
, muom_rsl.uom_class
, msi.primary_unit_of_measure
, msi.unit_volume
, muom_unit.unit_of_measure
BULK COLLECT INTO l_shipment_line_ids
, l_organization_ids
, l_item_ids
, l_quantities
, l_units_of_measure
, l_uom_classes
, l_primary_units_of_measure
, l_unit_volumes
, l_volume_uoms
FROM rcv_shipment_lines rsl
, mtl_system_items msi
, mtl_units_of_measure muom_rsl
, mtl_units_of_measure muom_unit
WHERE rsl.shipment_header_id = p_charge.shipment_header_id
AND msi.inventory_item_id (+) = rsl.item_id
AND msi.organization_id (+) = rsl.to_organization_id
AND muom_unit.uom_code (+) = msi.volume_uom_code
AND muom_rsl.unit_of_measure = rsl.unit_of_measure;
SELECT rsl.shipment_line_id
, rsl.to_organization_id
, rsl.item_id
, decode(rsl.quantity_received, 0, rsl.quantity_shipped, rsl.quantity_received)
, rsl.unit_of_measure
, muom_rsl.uom_class
, msi.primary_unit_of_measure
, msi.unit_weight
, muom_unit.unit_of_measure
BULK COLLECT INTO l_shipment_line_ids
, l_organization_ids
, l_item_ids
, l_quantities
, l_units_of_measure
, l_uom_classes
, l_primary_units_of_measure
, l_unit_weights
, l_weight_uoms
FROM rcv_shipment_lines rsl
, mtl_system_items msi
, mtl_units_of_measure muom_rsl
, mtl_units_of_measure muom_unit
WHERE rsl.shipment_header_id = p_charge.shipment_header_id
AND msi.inventory_item_id (+) = rsl.item_id
AND msi.organization_id (+) = rsl.to_organization_id
AND muom_unit.uom_code (+) = msi.weight_uom_code
AND muom_rsl.unit_of_measure = rsl.unit_of_measure;
SELECT shipment_line_id
, decode(quantity_received, 0, quantity_shipped, quantity_received)
BULK COLLECT INTO l_shipment_line_ids
, l_shipment_line_quantities
FROM rcv_shipment_lines
WHERE shipment_header_id = p_charge.shipment_header_id;
SELECT rsl.shipment_line_id
, decode(rsl.quantity_received, 0, rsl.quantity_shipped, rsl.quantity_received) * pol.unit_price
BULK COLLECT INTO l_shipment_line_ids
, l_shipment_line_amounts
FROM rcv_shipment_lines rsl
, po_lines_all pol
WHERE rsl.shipment_header_id = p_charge.shipment_header_id
AND pol.po_line_id = rsl.po_line_id;
p_charge_allocation_table.DELETE(j);
SELECT *
BULK COLLECT INTO l_charge_interface_table
FROM rcv_charges_interface
WHERE ( parent_header_interface_id = p_transaction_record.header_interface_id OR
parent_interface_id = p_transaction_record.interface_transaction_id
)
AND processing_status_code = 'P'
ORDER BY interface_charge_id
FOR UPDATE;
Update_Interface_Charges(l_charge_interface_table);
SELECT *
BULK COLLECT INTO l_charge_interface_table
FROM rcv_charges_interface
WHERE ( parent_interface_id = p_rti_id
OR ( parent_header_interface_id = p_rhi_id
AND parent_interface_id IS NULL
AND NOT EXISTS (
SELECT NULL
FROM rcv_transactions_interface
WHERE header_interface_id = p_rhi_id
AND interface_transaction_id <> p_rti_id)
)
)
AND processing_status_code IN ('S', 'W')
AND transaction_status_code = 'P'
ORDER BY interface_charge_id;
Update_Interface_Charges(l_charge_interface_table);
INSERT INTO po_rcv_charges
VALUES l_charge_table(i);
asn_debug.put_line('Inserted ' || SQL%ROWCOUNT || ' rows into PO_RCV_CHARGES');
INSERT INTO po_rcv_charge_allocations
VALUES l_charge_allocation_table(i);
asn_debug.put_line('Inserted ' || SQL%ROWCOUNT || ' rows into PO_RCV_CHARGE_ALLOCATIONS');
Update_Interface_Charges(l_charge_interface_table);
DELETE FROM rcv_charges_interface
WHERE transaction_status_code IN ('S','W');
asn_debug.put_line('Deleted ' || SQL%ROWCOUNT || ' successful rows from rcv_charges_interface');