[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO l_count
FROM inl_associations
WHERE ship_header_id = p_ship_header_id
START WITH FROM_PARENT_TABLE_NAME = P_from_parent_table_name AND FROM_PARENT_TABLE_ID = P_from_parent_table_id
CONNECT BY PRIOR TO_PARENT_TABLE_NAME = FROM_PARENT_TABLE_NAME AND PRIOR TO_PARENT_TABLE_ID = FROM_PARENT_TABLE_ID;
SELECT msi.primary_uom_code,
uom.uom_class,
msi.concatenated_segments
INTO l_primary_uom_code,
l_primary_uom_class,
l_concatenated_segments
FROM mtl_units_of_measure uom,
mtl_system_items_vl msi
WHERE uom.uom_code = msi.primary_uom_code
AND msi.organization_id = p_organization_id
AND msi.inventory_item_id = p_inventory_item_id;
SELECT uom_class
INTO l_from_uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_from_uom_code;
SELECT uom_class
INTO l_to_uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_to_uom_code;
SELECT 1/NVL(conversion_rate,0) * NVL(p_unit_price,0)
INTO l_converted_price
FROM mtl_uom_conversions_view
WHERE primary_uom_class = l_primary_uom_class
AND primary_uom_code = l_primary_uom_code
AND uom_class = l_from_uom_class
AND uom_code = p_from_uom_code
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT NVL(conversion_rate,0) * NVL(l_converted_price,0)
INTO l_converted_price
FROM mtl_uom_conversions_view
WHERE primary_uom_class = l_primary_uom_class
AND primary_uom_code = l_primary_uom_code
AND uom_class = l_to_uom_class
AND uom_code = p_to_uom_code
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT (SELECT (conversion_rate * p_unit_price) as conversion_rate
FROM mtl_uom_conversions_view
WHERE primary_uom_class = l_from_uom_class
AND primary_uom_code = p_from_uom_code
AND uom_class = l_to_uom_class
AND uom_code = p_to_uom_code
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
UNION
SELECT (1/conversion_rate * p_unit_price) as conversion_rate
FROM mtl_uom_conversions_view
WHERE primary_uom_class = l_to_uom_class
AND primary_uom_code = p_to_uom_code
AND uom_class = l_from_uom_class
AND uom_code = p_from_uom_code
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id)
INTO l_converted_price
FROM dual;
SELECT msi.primary_uom_code,
uom.uom_class,
msi.concatenated_segments
INTO l_primary_uom_code,
l_primary_uom_class,
l_concatenated_segments
FROM mtl_units_of_measure uom,
mtl_system_items_vl msi
WHERE uom.uom_code = msi.primary_uom_code
AND msi.organization_id = p_organization_id
AND msi.inventory_item_id = p_inventory_item_id;
SELECT uom_class
INTO l_allocation_uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_from_uom_code;
SELECT NVL(conversion_rate,0) * NVL(p_qty,0)
INTO l_primary_qty
FROM mtl_uom_conversions_view
WHERE primary_uom_class = l_primary_uom_class
AND primary_uom_code = l_primary_uom_code
AND uom_class = l_allocation_uom_class
AND uom_code = p_from_uom_code
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT uom_class
INTO l_to_allocation_uom_class
FROM mtl_units_of_measure
WHERE uom_code = P_to_uom_code;
SELECT 1/NVL(conversion_rate,0) * NVL(l_primary_qty,0)
INTO l_converted_qty
FROM mtl_uom_conversions_view
WHERE primary_uom_class = l_primary_uom_class
AND primary_uom_code = l_primary_uom_code
AND uom_class = l_to_allocation_uom_class
AND uom_code = p_to_uom_code
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT NVL(p_amt,0) * NVL(conversion_rate,0),
conversion_rate
INTO l_converted_amt,
x_currency_conversion_rate
FROM gl_daily_rates
WHERE from_currency = p_from_currency_code
AND to_currency = p_to_currency_code
AND conversion_type = p_currency_conversion_type
AND TRUNC(conversion_date) = TRUNC(p_currency_conversion_date);
SELECT association_id,
to_parent_table_name,
to_parent_table_id
FROM inl_adj_associations_v
WHERE from_parent_table_name = p_from_component_name
AND from_parent_table_id = p_from_component_id
ORDER BY association_id;
SELECT organization_id
INTO l_inv_org_id
FROM inl_ship_headers
WHERE ship_header_id = p_ship_header_id;
SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
Converted_Qty (l_inv_org_id,
ol.inventory_item_id,
NVL(ol.primary_qty,0),
ol.primary_uom_code,
p_allocation_uom_code))) + l_total_amt
INTO l_total_amt
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_header_id = rec_assoc.to_parent_table_id;
SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
Converted_Qty (l_inv_org_id,
ol.inventory_item_id,
NVL(ol.primary_qty,0),
-- Bug #7674125
ol.primary_uom_code,
p_allocation_uom_code))) + l_total_amt
INTO l_total_amt
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_line_group_id = rec_assoc.to_parent_table_id;
SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
Converted_Qty (l_inv_org_id,
ol.inventory_item_id,
NVL(ol.primary_qty,0),
-- Bug #7674125
ol.primary_uom_code,
p_allocation_uom_code))) + l_total_amt
INTO l_total_amt
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_line_id = rec_assoc.to_parent_table_id;
SELECT SUM(Converted_Amt (NVL(charge_amt,0),
currency_code,
p_le_currency_code,
currency_conversion_type,
currency_conversion_date)) + l_total_amt
INTO l_total_amt
FROM INL_adj_charge_lines_v
WHERE charge_line_id = rec_assoc.to_parent_table_id;
SELECT nvl(SUM(nrec_tax_amt),0) + l_total_amt
INTO l_total_amt
FROM INL_tax_lines
WHERE tax_line_id = rec_assoc.to_parent_table_id;
SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
Converted_Qty (l_inv_org_id,
ol.inventory_item_id,
NVL(ol.primary_qty,0),
ol.primary_uom_code,
p_allocation_uom_code))) + l_total_amt
INTO l_total_amt
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_header_id = DECODE(p_from_component_name,'INL_SHIP_HEADERS',p_from_component_id,ol.ship_header_id)
--- bug 7654293
AND ol.ship_line_group_id = DECODE(p_from_component_name,'INL_SHIP_LINE_GROUPS',p_from_component_id,ol.ship_line_group_id)
AND ol.ship_line_id = DECODE(p_from_component_name,'INL_SHIP_LINES',p_from_component_id,ol.ship_line_id)
AND ol.ship_header_id = p_ship_header_id;
SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
Converted_Qty (l_inv_org_id,
ol.inventory_item_id,
NVL(ol.primary_qty,0),
ol.primary_uom_code,
p_allocation_uom_code)))
INTO l_to_component_amt
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_header_id = p_to_component_id;
SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
Converted_Qty (l_inv_org_id,
ol.inventory_item_id,
NVL(ol.primary_qty,0),
ol.primary_uom_code,
p_allocation_uom_code)))
INTO l_to_component_amt
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_line_group_id = p_to_component_id;
SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
Converted_Qty (l_inv_org_id,
ol.inventory_item_id,
NVL(ol.primary_qty,0),
ol.primary_uom_code,
p_allocation_uom_code)))
INTO l_to_component_amt
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_line_id = p_to_component_id;
SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
Converted_Qty (l_inv_org_id,
ol.inventory_item_id,
NVL(ol.primary_qty,0),
ol.primary_uom_code,
p_allocation_uom_code)))
INTO l_to_component_amt
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_line_id = p_to_component_id;
SELECT SUM(Converted_Amt (NVL(charge_amt,0),
currency_code,
P_le_currency_code,
currency_conversion_type,
currency_conversion_date))
INTO l_to_component_amt
FROM inl_adj_charge_lines_v
WHERE charge_line_id = p_to_component_id;
SELECT NVL(SUM(nrec_tax_amt),0)
INTO l_to_component_amt
FROM INL_tax_lines
WHERE tax_line_id = p_to_component_id;
PROCEDURE Insert_Allocation (p_ship_header_id IN NUMBER,
p_le_currency_code IN VARCHAR2,
p_association_id IN NUMBER,
p_ship_line_id IN NUMBER,
p_amount IN NUMBER,
p_from_component_name IN VARCHAR2,
p_from_component_id IN NUMBER,
p_to_component_name IN VARCHAR2,
p_to_component_id IN NUMBER,
p_lc_flag IN VARCHAR2,
p_adjustment_num IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_ship_line_lc_flag VARCHAR2(1);
l_procedure_name CONSTANT VARCHAR2(30) := 'Insert_Allocation';
SELECT a.association_id,
a.from_parent_table_name,
a.from_parent_table_id,
a.to_parent_table_name,
a.to_parent_table_id,
a.allocation_basis,
a.allocation_uom_code,
a.to_parent_table_id ship_line_id
FROM inl_adj_associations_v a
WHERE a.from_parent_table_name = 'INL_SHIP_LINES'
AND a.from_parent_table_id = p_ship_line_id
AND a.to_parent_table_name = 'INL_SHIP_LINES'
AND a.ship_header_id = p_ship_header_id
ORDER BY a.association_id;
SELECT ol.landed_cost_flag
INTO l_ship_line_lc_flag
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_line_id = p_ship_line_id;
l_debug_info := 'Inserting into inl_allocations';
Insert_Allocation (p_ship_header_id,
p_le_currency_code,
p_association_id,
rec_assoc.ship_line_id,
p_amount * l_factor,
l_from_component_name,
p_from_component_id,
rec_assoc.to_parent_table_name,
rec_assoc.to_parent_table_id,
p_lc_flag,
p_adjustment_num,
l_return_status);
l_debug_info := 'Inserting into inl_allocations';
INSERT INTO inl_allocations
(allocation_id,
ship_header_id,
association_id,
ship_line_id,
from_parent_table_name,
from_parent_table_id,
to_parent_table_name,
to_parent_table_id,
adjustment_num,
allocation_amt,
landed_cost_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(inl_allocations_s.NEXTVAL,
p_ship_header_id,
p_association_id,
p_ship_line_id,
l_from_component_name,
p_from_component_id,
l_to_component_name,
p_to_component_id,
p_adjustment_num,
p_amount,
DECODE(p_lc_flag,'N','N','Y',l_ship_line_lc_flag),
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id);
END Insert_Allocation;
SELECT ship_line_id
FROM inl_adj_ship_lines_v
WHERE ship_header_id = p_ship_header_id
AND ship_line_id = DECODE(p_to_component_name, 'INL_SHIP_LINES', p_to_component_id, ship_line_id)
--- bug 7654293
AND ship_line_group_id = DECODE(p_to_component_name, 'INL_SHIP_LINE_GROUPS', p_to_component_id, ship_line_group_id);
Insert_Allocation (p_ship_header_id,
p_le_currency_code,
p_association_id,
rec_dist.ship_line_id,
p_amount * l_factor,
p_from_component_name,
p_from_component_id,
p_to_component_name,
p_to_component_id,
p_lc_flag,
p_adjustment_num,
l_return_status);
SELECT 1 SEQ_NUM,
'INL_SHIP_LINES' COMPONENT_NAME,
ol.ship_line_id COMPONENT_ID
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_header_id = p_ship_header_id
AND ol.ship_line_id = DECODE(p_to_component_name, 'INL_SHIP_LINES', p_to_component_id, -1)
UNION
SELECT 2 SEQ_NUM,
'INL_SHIP_HEADERS' COMPONENT_NAME,
ol.ship_header_id COMPONENT_ID
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_header_id = p_ship_header_id
AND ol.ship_line_id = DECODE(p_to_component_name, 'INL_SHIP_LINES', p_to_component_id, -1)
UNION
SELECT 1 SEQ_NUM,
p_to_component_name COMPONENT_NAME,
p_to_component_id COMPONENT_ID
FROM dual
ORDER BY seq_num;
SELECT association_id,
from_parent_table_name,
from_parent_table_id,
to_parent_table_name,
to_parent_table_id
FROM INL_adj_associations_v
WHERE ship_header_id = p_ship_header_id
AND from_parent_table_name = rec_component.component_name
AND from_parent_table_id = rec_component.component_id
ORDER BY association_id;
Insert_Allocation (p_ship_header_id,
p_le_currency_code,
p_association_id,
NULL,
p_amount,
p_from_component_name,
p_from_component_id,
p_to_component_name,
p_to_component_id,
'N',
p_adjustment_num,
l_return_status);
SELECT DECODE(l_next_level_allocation,'N','Y','N')
INTO l_lc_flag
FROM DUAL;
PROCEDURE Update_Allocation (p_ship_header_id IN NUMBER,
p_adjustment_num IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
--
--
--
CURSOR updalloc IS
SELECT allocation_id,
parent_allocation_id,
ship_header_id,
association_id,
ship_line_id,
adjustment_num
FROM inl_allocations
WHERE adjustment_num = p_adjustment_num
AND ship_header_id = p_ship_header_id
ORDER BY allocation_id;
l_procedure_name CONSTANT VARCHAR2(30) := 'Update_Allocation';
UPDATE inl_allocations a1
SET a1.parent_allocation_id = (SELECT MIN(a2.allocation_id)
FROM inl_allocations a2
WHERE a2.ship_header_id = rec_updalloc.ship_header_id
AND NVL(a2.association_id,0) = NVL(rec_updalloc.association_id,0)
AND (a2.ship_line_id = rec_updalloc.ship_line_id
OR a2.ship_line_id = (SELECT a.parent_ship_line_id
FROM inl_ship_lines a
WHERE a.ship_line_id = rec_updalloc.ship_line_id))
AND a2.adjustment_num = 0
AND NOT EXISTS (SELECT 'X' FROM inl_allocations a1
-- bug #7674125
WHERE NVL(a1.parent_allocation_id,0) = NVL(a2.allocation_id,0)
AND a1.ship_header_id = a2.ship_header_id
AND NVL(a1.association_id,0) = NVL(a2.association_id,0)
AND a1.adjustment_num = rec_updalloc.adjustment_num
AND a1.landed_cost_flag = 'Y' ))
WHERE a1.allocation_id = rec_updalloc.allocation_id
AND a1.adjustment_num = rec_updalloc.adjustment_num;
END Update_Allocation;
SELECT ship_header_id,
from_parent_table_name,
from_parent_table_id,
to_parent_table_name,
to_parent_table_id,
allocation_basis,
allocation_uom_code,
association_id
FROM inl_adj_associations_v
WHERE ship_header_id = p_ship_header_id
AND ((p_calc_scope_code = 3 AND from_parent_table_name = 'INL_TAX_LINES') OR
(p_calc_scope_code = 2 AND from_parent_table_name = 'INL_CHARGE_LINES') OR
(p_calc_scope_code = 1 AND from_parent_table_name IN ('INL_SHIP_HEADERS','INL_SHIP_LINES')) OR
p_calc_scope_code = 0)
ORDER BY association_id;
SELECT ship_header_id,
ship_line_id,
primary_qty,
-- bug 7660824
DECODE(landed_cost_flag,'Y',fc_primary_unit_price,0) fc_primary_unit_price
FROM inl_adj_ship_lines_v
WHERE ship_header_id = p_ship_header_id
AND p_calc_scope_code in (0,1)
ORDER BY ship_line_id;
SELECT charge_amt,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
charge_line_id
FROM INL_adj_charge_lines_v c
WHERE EXISTS (SELECT 'x'
FROM inl_adj_associations_v x
WHERE x.from_parent_table_name = 'INL_CHARGE_LINES'
AND x.from_parent_table_id = c.charge_line_id
AND x.ship_header_id = p_ship_header_id
AND p_calc_scope_code in (0,2)
AND ROWNUM < 2)
ORDER BY charge_line_id;
SELECT tax_amt,
tax_line_id
FROM INL_tax_lines t
WHERE EXISTS (SELECT 'x'
FROM INL_adj_associations_v x
WHERE x.from_parent_table_name = 'INL_TAX_LINES'
AND x.from_parent_table_id = t.tax_line_id
AND x.ship_header_id = p_ship_header_id
AND p_calc_scope_code in (0,3)
AND ROWNUM < 2)
ORDER BY tax_line_id;
SELECT adjustment_num
INTO l_adjustment_num
FROM inl_ship_headers
WHERE ship_header_id = p_ship_header_id;
DELETE FROM INL_allocations
WHERE ship_header_id = p_ship_header_id
AND adjustment_num = l_adjustment_num
AND ((p_calc_scope_code = 3 AND from_parent_table_name = 'INL_TAX_LINES') OR
(p_calc_scope_code = 2 AND from_parent_table_name = 'INL_CHARGE_LINES') OR
(p_calc_scope_code = 1 AND from_parent_table_name IN ('INL_SHIP_HEADERS',
'INL_SHIP_LINES',
'INL_SHIP_DISTS')) OR
p_calc_scope_code = 0);
SELECT DISTINCT gl.currency_code
INTO l_le_currency_code
FROM gl_ledgers gl,
xle_fp_ou_ledger_v l,
inl_ship_headers oh
WHERE gl.ledger_id = l.ledger_id
AND l.legal_entity_id = oh.legal_entity_id
AND oh.ship_header_id = p_ship_header_id;
SELECT Converted_Amt (charge_amt,
currency_code,
l_le_currency_code,
currency_conversion_type,
currency_conversion_date)
INTO l_from_amount
FROM INL_adj_charge_lines_v
WHERE charge_line_id = rec_assoc.from_parent_table_id;
SELECT NVL(SUM(nrec_tax_amt),0)
INTO l_from_amount
FROM INL_tax_lines
WHERE tax_line_id = rec_assoc.from_parent_table_id;
SELECT SUM(NVL(primary_qty,0)*NVL(fc_primary_unit_price,0))
INTO l_from_amount
FROM inl_adj_ship_lines_v
WHERE ship_header_id = rec_assoc.from_parent_table_id;
SELECT SUM(NVL(primary_qty,0)*NVL(DECODE(landed_cost_flag,'Y',fc_primary_unit_price,0),0))
INTO l_from_amount
FROM inl_adj_ship_lines_v
WHERE ship_line_id = rec_assoc.from_parent_table_id;
SELECT COUNT(*) + l_count
INTO l_count
FROM INL_adj_associations_v
WHERE from_parent_table_name = 'INL_SHIP_LINES'
AND from_parent_table_id = rec_dist.ship_line_id;
SELECT COUNT(*) + l_count
INTO l_count
FROM INL_adj_associations_v
WHERE from_parent_table_name = 'INL_SHIP_HEADERS'
AND from_parent_table_id = rec_dist.ship_header_id;
SELECT DECODE(l_count,0,'Y','N') INTO l_lc_flag FROM DUAL;
SELECT SUM(NVL(al.allocation_amt,0))
INTO l_inclusive_tax_amt
FROM inl_tax_lines t,
inl_adj_associations_v assoc,
inl_allocations al
WHERE t.tax_amt_included_flag = 'Y'
AND t.tax_line_id = assoc.from_parent_table_id
AND assoc.from_parent_table_name = 'INL_TAX_LINES'
AND assoc.association_id = al.association_id
AND al.ship_line_id = rec_dist.ship_line_id
AND al.adjustment_num = l_adjustment_num;
Insert_Allocation (p_ship_header_id,
l_le_currency_code,
NULL,
rec_dist.ship_line_id,
(NVL(rec_dist.primary_qty,0)*NVL(rec_dist.fc_primary_unit_price,0))-NVL(l_inclusive_tax_amt,0),
'INL_SHIP_DISTS',
rec_dist.ship_line_id,
'INL_SHIP_DISTS',
rec_dist.ship_line_id,
l_lc_flag,
l_adjustment_num,
l_return_status);
Insert_Allocation (p_ship_header_id,
l_le_currency_code,
NULL,
NULL,
Converted_Amt (rec_charge.charge_amt,
rec_charge.currency_code,
l_le_currency_code,
rec_charge.currency_conversion_type,
rec_charge.currency_conversion_date),
'INL_CHARGE_LINES',
rec_charge.charge_line_id,
'INL_CHARGE_LINES',
rec_charge.charge_line_id,
'N',
l_adjustment_num,
l_return_status);
Insert_Allocation (p_ship_header_id,
l_le_currency_code,
NULL,
NULL,
NVL(rec_tax.tax_amt,0),
'INL_TAX_LINES',
rec_tax.tax_line_id,
'INL_TAX_LINES',
rec_tax.tax_line_id,
'N',
l_adjustment_num,
l_return_status);
Update_Allocation (p_ship_header_id,
l_adjustment_num,
x_return_status);