[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 unit_of_measure_tl
INTO l_debug_info
FROM mtl_units_of_measure
WHERE uom_code = p_from_uom_code;
SELECT unit_of_measure_tl
INTO l_debug_info
FROM mtl_units_of_measure
WHERE uom_code = p_to_uom_code;
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 unit_of_measure_tl
INTO l_debug_info
FROM mtl_units_of_measure
WHERE uom_code = p_from_uom_code;
SELECT unit_of_measure_tl
INTO l_debug_info
FROM mtl_units_of_measure
WHERE uom_code = p_to_uom_code;
SELECT unit_of_measure_tl
INTO l_debug_info
FROM mtl_units_of_measure
WHERE uom_code = p_from_uom_code;
SELECT unit_of_measure_tl
INTO l_debug_info
FROM mtl_units_of_measure
WHERE uom_code = p_to_uom_code;
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 a.association_id,
a.to_parent_table_name,
DECODE(a.to_parent_table_name,
'INL_SHIP_LINES',
(SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl,
inl_ship_lines_all sl0
WHERE sl0.ship_line_id = a.to_parent_table_id
AND sl.ship_header_id = sl0.ship_header_id
AND sl.ship_line_group_id = sl0.ship_line_group_id
AND sl.ship_line_num = sl0.ship_line_num
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
),
'INL_CHARGE_LINES',
(SELECT MAX(cl.charge_line_id)
FROM inl_charge_lines cl
--- SCM-051
--- WHERE cl.adjustment_num <= p_adjustment_num
WHERE ABS(cl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
START WITH cl.charge_line_id = a.to_parent_table_id
CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
),
a.to_parent_table_id) to_parent_table_id
FROM inl_associations a
WHERE a.from_parent_table_name = p_from_component_name
AND a.from_parent_table_id
= DECODE(a.from_parent_table_name,
'INL_CHARGE_LINES',
(SELECT MIN(cl.charge_line_id)
FROM inl_charge_lines cl
--- SCM-051
--- WHERE cl.adjustment_num <= p_adjustment_num
WHERE ABS(cl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
START WITH cl.charge_line_id = p_from_component_id
CONNECT BY PRIOR cl.parent_charge_line_id = cl.charge_line_id),
'INL_TAX_LINES',
(SELECT MIN(tl.tax_line_id)
FROM inl_tax_lines tl
--- SCM-051
--- WHERE tl.adjustment_num <= p_adjustment_num
WHERE ABS(tl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
START WITH tl.tax_line_id = p_from_component_id
CONNECT BY PRIOR tl.parent_tax_line_id = tl.tax_line_id),
'INL_SHIP_LINES',
(SELECT MIN(sl.ship_line_id)
FROM inl_ship_lines sl
--- SCM-051
--- WHERE (sl.adjustment_num <= p_adjustment_num
WHERE (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
START WITH sl.ship_line_id = p_from_component_id
CONNECT BY PRIOR sl.parent_ship_line_id = sl.ship_line_id))
ORDER BY a.association_id;
SELECT
COUNT(*)
INTO l_count_aux
FROM inl_ship_lines_all ol
WHERE ol.ship_header_id = assoc_List(1).to_parent_table_id
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
);
SELECT
COUNT(*)
INTO l_count_aux
FROM inl_ship_lines_all ol
WHERE ol.ship_line_group_id = assoc_List(1).to_parent_table_id
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
);
SELECT
COUNT(*)
INTO l_count_aux
FROM inl_ship_lines_all ol
WHERE ol.ship_header_id = DECODE(p_from_component_name,'INL_SHIP_HEADERS',p_from_component_id,ol.ship_header_id)
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
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
);
SELECT organization_id
INTO l_inv_org_id
FROM inl_ship_headers_all
WHERE ship_header_id = p_ship_header_id;
SELECT SUM(DECODE(p_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),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,
COUNT(*) + l_count
INTO l_total_amt,
l_count
FROM inl_ship_lines_all ol
WHERE ol.ship_header_id = assoc_List(iAssoc).to_parent_table_id
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
);
SELECT SUM(DECODE(p_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),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,
COUNT(*) + l_count
INTO l_total_amt,
l_count
FROM inl_ship_lines_all ol
WHERE ol.ship_line_group_id = assoc_List(iAssoc).to_parent_table_id
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
);
SELECT SUM(DECODE(p_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),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,
COUNT(*) + l_count
INTO l_total_amt,
l_count
FROM inl_ship_lines_all ol
WHERE ol.ship_line_id = assoc_List(iAssoc).to_parent_table_id;
SELECT SUM(NVL(charge_amt,0) * NVL(currency_conversion_rate,1)) + l_total_amt,
COUNT(*) + l_count
INTO l_total_amt,
l_count
FROM inl_charge_lines cl
WHERE cl.charge_line_id = assoc_List(iAssoc).to_parent_table_id;
SELECT nvl(SUM(nrec_tax_amt),0) + l_total_amt,
COUNT(*) + l_count
INTO l_total_amt,
l_count
FROM inl_tax_lines tl --BUG#8330505
WHERE tl.tax_line_id = assoc_List(iAssoc).to_parent_table_id;
SELECT SUM(DECODE(p_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),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,
COUNT(*)
INTO l_total_amt,
l_count
FROM inl_ship_lines_all ol
WHERE ol.ship_header_id = DECODE(p_from_component_name,'INL_SHIP_HEADERS',p_from_component_id,ol.ship_header_id)
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
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
);
SELECT organization_id
INTO l_inv_org_id
FROM inl_ship_headers_all
WHERE ship_header_id = p_ship_header_id;
SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),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_ship_lines_all ol
WHERE ol.ship_header_id = p_to_component_id
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
);
SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),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_ship_lines_all ol
WHERE ol.ship_line_group_id = p_to_component_id
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
);
SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),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_ship_lines_all 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.primary_unit_price * NVL(ol.currency_conversion_rate,1),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_ship_lines_all ol
WHERE ol.ship_line_id = p_to_component_id;
SELECT SUM(NVL(charge_amt,0) * NVL(currency_conversion_rate,1))
INTO l_to_component_amt
FROM inl_charge_lines
WHERE charge_line_id = p_to_component_id;
SELECT NVL(SUM(nrec_tax_amt),0)
INTO l_to_component_amt
FROM inl_tax_lines --BUG#8330505
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_program_name CONSTANT VARCHAR2(30) := 'Insert_Allocation';
SELECT a.association_id,
a.from_parent_table_name,
(SELECT max(sl.ship_line_id)
FROM inl_ship_lines_all sl,
inl_ship_lines_all sl0
WHERE sl0.ship_line_id = a.from_parent_table_id
AND sl.ship_header_id = sl0.ship_header_id
AND sl.ship_line_group_id = sl0.ship_line_group_id
AND sl.ship_line_num = sl0.ship_line_num
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
) from_parent_table_id,
a.to_parent_table_name,
(SELECT max(sl.ship_line_id)
FROM inl_ship_lines_all sl,
inl_ship_lines_all sl0
WHERE sl0.ship_line_id = a.to_parent_table_id
AND sl.ship_header_id = sl0.ship_header_id
AND sl.ship_line_group_id = sl0.ship_line_group_id
AND sl.ship_line_num = sl0.ship_line_num
--- SCM-051
--- AND ( sl.adjustment_num <= p_adjustment_num
AND ( ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
) to_parent_table_id,
a.allocation_basis,
a.allocation_uom_code,
a.to_parent_table_id ship_line_id
/* SCM-LCM-010
FROM inl_adj_associations_v a
*/
FROM inl_associations a
WHERE a.from_parent_table_name = 'INL_SHIP_LINES'
AND a.from_parent_table_id =
(SELECT MIN(sl.ship_line_id)
FROM inl_ship_lines sl
--- SCM-051
--- WHERE (sl.adjustment_num <= p_adjustment_num
WHERE (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
START WITH sl.ship_line_id = p_ship_line_id
CONNECT BY PRIOR sl.parent_ship_line_id = sl.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;
SELECT COUNT(*)
INTO l_count
FROM inl_associations a
WHERE a.from_parent_table_name = 'INL_SHIP_LINES'
AND a.to_parent_table_name = 'INL_SHIP_LINES'
AND a.ship_header_id = p_ship_header_id;
l_debug_info := 'Inserting into inl_allocations';
Insert_Allocation (
p_ship_header_id => p_ship_header_id,
p_le_currency_code => p_le_currency_code,
p_association_id => p_association_id,
p_ship_line_id => assoc_List(iAssoc).ship_line_id,
p_amount => p_amount * l_factor,
p_from_component_name => l_from_component_name,
p_from_component_id => p_from_component_id,
p_to_component_name => assoc_List(iAssoc).to_parent_table_name,
p_to_component_id => assoc_List(iAssoc).to_parent_table_id,
p_lc_flag => p_lc_flag,
p_adjustment_num => p_adjustment_num,
x_return_status => l_return_status);
l_debug_info := 'Inserting into inl_allocations';
INSERT INTO inl_allocations
(allocation_id, /* 01 */
ship_header_id, /* 02 */
association_id, /* 03 */
ship_line_id, /* 04 */
from_parent_table_name, /* 05 */
from_parent_table_id, /* 06 */
to_parent_table_name, /* 07 */
to_parent_table_id, /* 08 */
adjustment_num, /* 09 */
allocation_amt, /* 10 */
landed_cost_flag, /* 11 */
created_by, /* 12 */
creation_date, /* 13 */
last_updated_by, /* 14 */
last_update_date, /* 15 */
last_update_login) /* 16 */
VALUES
(inl_allocations_s.NEXTVAL, /* 01 */
p_ship_header_id, /* 02 */
p_association_id, /* 03 */
p_ship_line_id, /* 04 */
l_from_component_name, /* 05 */
p_from_component_id, /* 06 */
l_to_component_name, /* 07 */
p_to_component_id, /* 08 */
--- SCM-051
ABS(p_adjustment_num), /* 09 */
--- SCM-051
p_amount, /* 10 */
DECODE(p_lc_flag,'N','N','Y',l_ship_line_lc_flag), /* 11 */
L_FND_USER_ID, /* 12 */
SYSDATE, /* 13 */
L_FND_USER_ID, /* 14 */
SYSDATE, /* 15 */
L_FND_LOGIN_ID); /* 16 */
END Insert_Allocation;
SELECT ship_line_id
FROM inl_ship_lines_all 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, ol.ship_line_id)
AND ol.ship_line_group_id = DECODE(p_to_component_name, 'INL_SHIP_LINE_GROUPS', p_to_component_id, ol.ship_line_group_id)
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
AND (sl.adjustment_num <= p_adjustment_num
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
)
ORDER BY ol.ship_line_id;
SELECT ship_line_id
FROM inl_ship_lines_all ol
WHERE ol.ship_header_id = p_ship_header_id
AND ol.ship_line_group_id = DECODE(p_to_component_name, 'INL_SHIP_LINE_GROUPS', p_to_component_id, ol.ship_line_group_id)
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
))
ORDER BY ol.ship_line_id;
Insert_Allocation (
p_ship_header_id => p_ship_header_id,
p_le_currency_code => p_le_currency_code,
p_association_id => p_association_id,
p_ship_line_id => p_to_component_id,
p_amount => p_amount,
p_from_component_name => p_from_component_name,
p_from_component_id => p_from_component_id,
p_to_component_name => p_to_component_name,
p_to_component_id => p_to_component_id,
p_lc_flag => p_lc_flag,
p_adjustment_num => p_adjustment_num,
x_return_status => l_return_status);
Insert_Allocation (
p_ship_header_id => p_ship_header_id,
p_le_currency_code => p_le_currency_code,
p_association_id => p_association_id,
p_ship_line_id => dist_List(idist).ship_line_id,
p_amount => p_amount * l_factor,
p_from_component_name => p_from_component_name,
p_from_component_id => p_from_component_id,
p_to_component_name => p_to_component_name,
p_to_component_id => p_to_component_id,
p_lc_flag => p_lc_flag,
p_adjustment_num => p_adjustment_num,
x_return_status => l_return_status);
SELECT 1 SEQ_NUM,
'INL_SHIP_LINES' COMPONENT_NAME,
ol.ship_line_id COMPONENT_ID
FROM inl_ship_lines_all 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)
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
)
UNION
SELECT 2 SEQ_NUM,
'INL_SHIP_HEADERS' COMPONENT_NAME,
ol.ship_header_id COMPONENT_ID
FROM inl_ship_lines_all 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)
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
)
UNION
SELECT 1 SEQ_NUM,
p_to_component_name COMPONENT_NAME,
p_to_component_id COMPONENT_ID
FROM dual
ORDER BY seq_num;
SELECT a.association_id,
a.from_parent_table_name,
DECODE(a.from_parent_table_name,
'INL_SHIP_LINES',
(SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl,
inl_ship_lines_all sl0
WHERE sl0.ship_line_id = a.from_parent_table_id
AND sl.ship_header_id = sl0.ship_header_id
AND sl.ship_line_group_id = sl0.ship_line_group_id
AND sl.ship_line_num = sl0.ship_line_num
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
),
'INL_CHARGE_LINES',
(SELECT MAX(cl.charge_line_id)
FROM inl_charge_lines cl
--- SCM-051
--- WHERE cl.adjustment_num <= p_adjustment_num
WHERE ABS(cl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
START WITH cl.charge_line_id = a.from_parent_table_id
CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
),
'INL_TAX_LINES',
(SELECT MAX(tl.tax_line_id)
FROM inl_tax_lines tl
--- SCM-051
--- WHERE tl.adjustment_num <= p_adjustment_num
WHERE ABS(tl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
START WITH tl.tax_line_id = a.from_parent_table_id
CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id
),
a.from_parent_table_id) from_parent_table_id,
a.to_parent_table_name,
DECODE(a.to_parent_table_name,
'INL_SHIP_LINES',
(SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl,
inl_ship_lines_all sl0
WHERE sl0.ship_line_id = a.to_parent_table_id
AND sl.ship_header_id = sl0.ship_header_id
AND sl.ship_line_group_id = sl0.ship_line_group_id
AND sl.ship_line_num = sl0.ship_line_num
--- SCM-051
--- AND (sl.adjustment_num <= p_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
),
'INL_CHARGE_LINES',
(SELECT MAX(cl.charge_line_id)
FROM inl_charge_lines cl
--- SCM-051
--- WHERE cl.adjustment_num <= p_adjustment_num
WHERE ABS(cl.adjustment_num) <= ABS(p_adjustment_num)
--- SCM-051
START WITH cl.charge_line_id = a.to_parent_table_id
CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
),
a.to_parent_table_id) to_parent_table_id
FROM inl_associations a
WHERE a.ship_header_id = p_ship_header_id
AND a.from_parent_table_name = pc_component_name
AND a.from_parent_table_id = pc_component_id
ORDER BY a.association_id;
Insert_Allocation (
p_ship_header_id => p_ship_header_id,
p_le_currency_code => p_le_currency_code,
p_association_id => p_association_id,
p_ship_line_id => NULL,
p_amount => p_amount,
p_from_component_name => p_from_component_name,
p_from_component_id => p_from_component_id,
p_to_component_name => p_to_component_name,
p_to_component_id => p_to_component_id,
p_lc_flag => 'N',
p_adjustment_num => p_adjustment_num,
x_return_status => l_return_status);
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
--- SCM-051
---
WHERE ABS(adjustment_num) = ABS(p_adjustment_num)
--- SCM-051
AND ship_header_id = p_ship_header_id
ORDER BY allocation_id;
l_program_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 = updalloc_List(iUpdAlloc).ship_header_id
AND NVL(a2.association_id,0) = NVL(updalloc_List(iUpdAlloc).association_id,0)
AND (a2.ship_line_id = updalloc_List(iUpdAlloc).ship_line_id
OR a2.ship_line_id = (SELECT a.parent_ship_line_id
FROM inl_ship_lines_all a
WHERE a.ship_line_id = updalloc_List(iUpdAlloc).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 = updalloc_List(iUpdAlloc).adjustment_num
AND a1.landed_cost_flag = 'Y' ))
WHERE a1.allocation_id = updalloc_List(iUpdAlloc).allocation_id
AND a1.adjustment_num = updalloc_List(iUpdAlloc).adjustment_num;
END Update_Allocation;
SELECT a.ship_header_id,
a.from_parent_table_name,
DECODE(a.from_parent_table_name,
'INL_SHIP_LINES',
(SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl,
inl_ship_lines_all sl0
WHERE sl0.ship_line_id = a.from_parent_table_id
AND sl.ship_header_id = sl0.ship_header_id
AND sl.ship_line_group_id = sl0.ship_line_group_id
AND sl.ship_line_num = sl0.ship_line_num
--- SCM-051
--- AND (sl.adjustment_num <= pc_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(pc_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
),
'INL_CHARGE_LINES',
(SELECT MAX(cl.charge_line_id)
FROM inl_charge_lines cl
--- SCM-051
--- WHERE cl.adjustment_num <= pc_adjustment_num
WHERE ABS(cl.adjustment_num) <= ABS(pc_adjustment_num)
--- SCM-051
START WITH cl.charge_line_id = a.from_parent_table_id
CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
),
'INL_TAX_LINES',
(SELECT MAX(tl.tax_line_id)
FROM inl_tax_lines tl
--- SCM-051
--- WHERE tl.adjustment_num <= pc_adjustment_num
WHERE ABS(tl.adjustment_num) <= ABS(pc_adjustment_num)
--- SCM-051
START WITH tl.tax_line_id = a.from_parent_table_id
CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id
),
a.from_parent_table_id) from_parent_table_id,
a.to_parent_table_name,
DECODE(a.to_parent_table_name,
'INL_SHIP_LINES',
(SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl,
inl_ship_lines_all sl0
WHERE sl0.ship_line_id = a.to_parent_table_id
AND sl.ship_header_id = sl0.ship_header_id
AND sl.ship_line_group_id = sl0.ship_line_group_id
AND sl.ship_line_num = sl0.ship_line_num
--- SCM-051
--- AND (sl.adjustment_num <= pc_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(pc_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
),
'INL_CHARGE_LINES',
(SELECT MAX(cl.charge_line_id)
FROM inl_charge_lines cl
--- SCM-051
--- WHERE cl.adjustment_num <= pc_adjustment_num
WHERE ABS(cl.adjustment_num) <= ABS(pc_adjustment_num)
--- SCM-051
START WITH cl.charge_line_id = a.to_parent_table_id
CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
),
a.to_parent_table_id) to_parent_table_id,
a.allocation_basis,
a.allocation_uom_code,
a.association_id
FROM inl_associations a
WHERE DECODE(a.from_parent_table_name,
'INL_SHIP_LINES',
(SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl,
inl_ship_lines_all sl0
WHERE sl0.ship_line_id = a.from_parent_table_id
AND sl.ship_header_id = sl0.ship_header_id
AND sl.ship_line_group_id = sl0.ship_line_group_id
AND sl.ship_line_num = sl0.ship_line_num
--- SCM-051
--- AND (sl.adjustment_num <= pc_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(pc_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
),
'INL_CHARGE_LINES',
(SELECT MAX(cl.charge_line_id)
FROM inl_charge_lines cl
--- SCM-051
--- WHERE cl.adjustment_num <= pc_adjustment_num
WHERE ABS(cl.adjustment_num) <= ABS(pc_adjustment_num)
--- SCM-051
START WITH cl.charge_line_id = a.from_parent_table_id
CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
),
'INL_TAX_LINES',
(SELECT MAX(tl.tax_line_id)
FROM inl_tax_lines tl
--- SCM-051
--- WHERE tl.adjustment_num <= pc_adjustment_num
WHERE ABS(tl.adjustment_num) <= ABS(pc_adjustment_num)
--- SCM-051
START WITH tl.tax_line_id = a.from_parent_table_id
CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id
)) IS NOT NULL
AND ship_header_id = p_ship_header_id
ORDER BY from_parent_table_name,
from_parent_table_id,
to_parent_table_name,
to_parent_table_id;
SELECT ship_header_id,
ship_line_id,
primary_qty,
-- bug 7660824
DECODE(landed_cost_flag,'Y',ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0) fc_primary_unit_price
FROM inl_ship_lines_all ol
WHERE ol.ship_header_id = p_ship_header_id
AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
--- SCM-051
--- AND (sl.adjustment_num <= pc_adjustment_num
AND (ABS(sl.adjustment_num) <= ABS(pc_adjustment_num)
--- SCM-051
OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
)
ORDER BY ship_line_id;
SELECT -- c.charge_amt, --BUG#9719618
DECODE(c.landed_cost_flag,'Y',c.charge_amt,0) charge_amt, --BUG#9719618
c.currency_code,
c.currency_conversion_type,
c.currency_conversion_rate,
c.currency_conversion_date,
c.charge_line_id
FROM inl_charge_lines c,
--Bug#13988746 BEG
(
SELECT DISTINCT a.from_parent_table_id
FROM inl_associations a
WHERE a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.ship_header_id = p_ship_header_id
) X
--Bug#13988746 END
--- SCM-051
--- WHERE c.adjustment_num <= pc_adjustment_num --Bug#9660084
WHERE ABS(c.adjustment_num) <= ABS(pc_adjustment_num) --Bug#9660084
--Bug#13988746 BEG
AND c.charge_line_id
= (
SELECT MAX(cl.charge_line_id)
FROM inl_charge_lines cl
WHERE cl.adjustment_num <= ABS(pc_adjustment_num) -- SCM-051
START WITH cl.charge_line_id = x.from_parent_table_id
CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
)
--- SCM-051
/*
AND EXISTS (SELECT 'x'
FROM inl_associations x
WHERE x.from_parent_table_name = 'INL_CHARGE_LINES'
AND (SELECT MAX(cl.charge_line_id)
FROM inl_charge_lines cl
--- SCM-051
--- WHERE cl.adjustment_num <= pc_adjustment_num
WHERE ABS(cl.adjustment_num) <= ABS(pc_adjustment_num)
--- SCM-051
START WITH cl.charge_line_id = x.from_parent_table_id
CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id) = c.charge_line_id
AND x.ship_header_id = p_ship_header_id
AND ROWNUM < 2)*/
--Bug#13988746 END
ORDER BY charge_line_id;
SELECT tax_amt,
tax_line_id
FROM inl_tax_lines t --BUG#8330505
--- SCM-051
--- WHERE t.adjustment_num <= pc_adjustment_num --Bug#9660084
WHERE ABS(t.adjustment_num) <= ABS(pc_adjustment_num) --Bug#9660084
--- SCM-051
AND EXISTS (SELECT 'x'
FROM inl_associations x
WHERE x.from_parent_table_name = 'INL_TAX_LINES'
AND (SELECT MAX(tl.tax_line_id)
FROM inl_tax_lines tl
--- SCM-051
--- WHERE tl.adjustment_num <= pc_adjustment_num
WHERE ABS(tl.adjustment_num) <= ABS(pc_adjustment_num)
--- SCM-051
START WITH tl.tax_line_id = x.from_parent_table_id
CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id) = t.tax_line_id
AND x.ship_header_id = p_ship_header_id
AND ROWNUM < 2)
ORDER BY tax_line_id;
SELECT DISTINCT
gl.currency_code,
oh.adjustment_num,
oh.ship_status_code
INTO
l_le_currency_code,
l_last_adjustment_num,
l_ship_status_code
FROM gl_ledgers gl,
xle_fp_ou_ledger_v l,
inl_ship_headers_all 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 MAX(ABS(adjustment_num))
--- SCM-051
INTO l_first_adjustment_num
FROM inl_allocations
WHERE ship_header_id = p_ship_header_id;
UPDATE inl_ship_headers_all
SET
adjustment_num = l_last_adjustment_num
WHERE ship_header_id = p_ship_header_id;
SELECT NVL(MIN(x.adjustment_num), l_last_adjustment_num) --Bug#10221931
INTO l_current_adjustment_num
FROM (SELECT sl.adjustment_num adjustment_num
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = p_ship_header_id
UNION ALL
SELECT sl.adjustment_num adjustment_num
FROM inl_ship_lines_all sl
WHERE EXISTS (SELECT 'X'
FROM inl_associations a
WHERE a.ship_header_id = p_ship_header_id
AND a.from_parent_table_name = 'INL_SHIP_LINES'
AND a.from_parent_table_id = sl.ship_line_id)
UNION ALL
SELECT cl.adjustment_num adjustment_num
FROM inl_charge_lines cl
WHERE EXISTS (SELECT 'X'
FROM inl_associations a
WHERE a.ship_header_id = p_ship_header_id
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = cl.charge_line_id)
UNION ALL
SELECT tl.adjustment_num adjustment_num
FROM inl_tax_lines tl
WHERE EXISTS (SELECT 'X'
FROM inl_associations a
WHERE a.ship_header_id = p_ship_header_id
AND a.from_parent_table_name = 'INL_TAX_LINES'
AND a.from_parent_table_id = tl.tax_line_id)) x
--- SCM-051
WHERE ABS(x.adjustment_num) >= ABS(l_i_adj)
AND ABS(x.adjustment_num) <= ABS(l_last_adjustment_num);
SELECT
MIN
(LEAST
(( SELECT NVL(MIN(ABS(sl.adjustment_num)),l_last_adjustment_num)
FROM inl_ship_lines_all sl
WHERE sl.ship_header_id = p_ship_header_id
AND ABS(sl.adjustment_num) >= ABS(l_i_adj)
AND ABS(sl.adjustment_num) <= ABS(l_last_adjustment_num)
),
( SELECT NVL(MIN(ABS(cl.adjustment_num)),l_last_adjustment_num)
FROM inl_charge_lines cl
WHERE a.from_parent_table_name = 'INL_CHARGE_LINES'
AND ABS(cl.adjustment_num) >= ABS(l_i_adj)
AND ABS(cl.adjustment_num) <= ABS(l_last_adjustment_num)
START WITH cl.charge_line_id = a.from_parent_table_id
CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
),
( SELECT NVL(MIN(ABS(tl.adjustment_num)),l_last_adjustment_num)
FROM inl_tax_lines tl
WHERE a.from_parent_table_name = 'INL_TAX_LINES'
AND ABS(tl.adjustment_num) >= ABS(l_i_adj)
AND ABS(tl.adjustment_num) <= ABS(l_last_adjustment_num)
START WITH tl.tax_line_id = a.from_parent_table_id
CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id
)
)) adj_num
INTO l_current_adjustment_num
FROM
inl_associations a,
inl_ship_headers_all sh
WHERE a.ship_header_id(+) = sh.ship_header_id
AND sh.ship_header_id = p_ship_header_id
;
DELETE FROM INL_allocations
WHERE ship_header_id = p_ship_header_id
AND ABS(adjustment_num) = ABS(l_current_adjustment_num);
SELECT DECODE(cl.landed_cost_flag,'Y',NVL(cl.charge_amt,0) * NVL(cl.currency_conversion_rate,1),0) --BUG#9719618
INTO l_from_amount
FROM inl_charge_lines cl
WHERE cl.charge_line_id = assoc_List(iAssoc).from_parent_table_id;
SELECT NVL(SUM(nrec_tax_amt),0)
INTO l_from_amount
FROM inl_tax_lines tl --BUG#8330505
WHERE tl.tax_line_id = assoc_List(iAssoc).from_parent_table_id;
SELECT SUM(NVL(ol.primary_qty,0)*NVL(DECODE(ol.landed_cost_flag,'Y',ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0)) --BUG#9719618
INTO l_from_amount
FROM inl_ship_lines_all ol
WHERE ol.ship_header_id = assoc_List(iAssoc).from_parent_table_id;
SELECT SUM(NVL(ol.primary_qty,0)*NVL(DECODE(ol.landed_cost_flag,'Y',ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0))
INTO l_from_amount
FROM inl_ship_lines_all ol
WHERE ol.ship_line_id = assoc_List(iAssoc).from_parent_table_id;
SELECT COUNT(*) + l_count1
INTO l_count1
FROM inl_associations
WHERE from_parent_table_name = 'INL_SHIP_LINES'
AND from_parent_table_id = dist_List(iDist).ship_line_id;
SELECT COUNT(*) + l_count1
INTO l_count1
FROM inl_associations
WHERE from_parent_table_name = 'INL_SHIP_HEADERS'
AND from_parent_table_id = dist_List(iDist).ship_header_id;
SELECT SUM(NVL(al.allocation_amt,0))
INTO l_inclusive_tax_amt
FROM
inl_tax_lines t, --BUG#8330505
inl_associations assoc,
inl_allocations al
WHERE t.tax_amt_included_flag = 'Y'
AND t.tax_line_id = (SELECT MAX(tl.tax_line_id)
FROM inl_tax_lines tl
--- SCM-051
--- WHERE tl.adjustment_num <= l_current_adjustment_num
WHERE ABS(tl.adjustment_num) <= ABS(l_current_adjustment_num)
--- SCM-051
START WITH tl.tax_line_id = assoc.from_parent_table_id
CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id)
AND assoc.from_parent_table_name = 'INL_TAX_LINES'
AND assoc.association_id = al.association_id
AND al.ship_line_id = dist_List(iDist).ship_line_id
AND ABS(al.adjustment_num) = ABS(l_current_adjustment_num);
Insert_Allocation (
p_ship_header_id => p_ship_header_id,
p_le_currency_code => l_le_currency_code,
p_association_id => NULL,
p_ship_line_id => dist_List(iDist).ship_line_id,
p_amount => (NVL(dist_List(iDist).primary_qty,0)*NVL(dist_List(iDist).fc_primary_unit_price,0))-NVL(l_inclusive_tax_amt,0),
p_from_component_name => 'INL_SHIP_DISTS',
p_from_component_id => dist_List(iDist).ship_line_id,
p_to_component_name => 'INL_SHIP_DISTS',
p_to_component_id => dist_List(iDist).ship_line_id,
p_lc_flag => l_lc_flag,
p_adjustment_num => l_current_adjustment_num,
x_return_status => l_return_status
);
Insert_Allocation (
p_ship_header_id => p_ship_header_id,
p_le_currency_code => l_le_currency_code,
p_association_id => NULL,
p_ship_line_id => NULL,
p_amount => charge_List(iCharge).charge_amt * NVL(charge_List(iCharge).currency_conversion_rate,1),
p_from_component_name => 'INL_CHARGE_LINES',
p_from_component_id => charge_List(iCharge).charge_line_id,
p_to_component_name => 'INL_CHARGE_LINES',
p_to_component_id => charge_List(iCharge).charge_line_id,
p_lc_flag => 'N',
p_adjustment_num => l_current_adjustment_num,
x_return_status => l_return_status);
Insert_Allocation (
p_ship_header_id => p_ship_header_id,
p_le_currency_code => l_le_currency_code,
p_association_id => NULL,
p_ship_line_id => NULL,
p_amount => NVL(tax_List(iTax).tax_amt,0),
p_from_component_name => 'INL_TAX_LINES',
p_from_component_id => tax_List(iTax).tax_line_id,
p_to_component_name => 'INL_TAX_LINES',
p_to_component_id => tax_List(iTax).tax_line_id,
p_lc_flag => 'N',
p_adjustment_num => l_current_adjustment_num,
x_return_status => l_return_status);
Update_Allocation (
p_ship_header_id => p_ship_header_id,
p_adjustment_num => l_current_adjustment_num,
x_return_status => x_return_status);