[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
last_update_date DATE,
last_updated_by NUMBER,
creation_date DATE,
created_by NUMBER,
last_update_login NUMBER) ;
last_update_date DATE,
last_updated_by NUMBER,
creation_date DATE,
created_by NUMBER,
last_update_login NUMBER,
unit_landed_cost NUMBER) ;
l_debug_info := 'Insert error details in inl_ship_holds.';
INSERT
INTO inl_ship_holds
(
ship_hold_id , /* 01 */
ship_header_id , /* 02 */
ship_line_id , /* 03 */
table_name , /* 04 */
column_name , /* 05 */
column_value , /* 06 */
processing_date , /* 07 */
error_message_name , /* 08 */
error_message , /* 09 */
token1_name , /* 10 */
token1_value , /* 11 */
token2_name , /* 12 */
token2_value , /* 13 */
token3_name , /* 14 */
token3_value , /* 15 */
token4_name , /* 16 */
token4_value , /* 17 */
token5_name , /* 18 */
token5_value , /* 19 */
token6_name , /* 20 */
token6_value , /* 21 */
created_by , /* 22 */
creation_date , /* 23 */
last_updated_by , /* 24 */
last_update_date , /* 25 */
last_update_login , /* 26 */
program_id , /* 27 */
program_application_id, /* 28 */
program_update_date , /* 29 */
request_id /* 30 */
)
VALUES
(
inl_ship_holds_s.NEXTVAL , /* 01 */
p_ship_header_id , /* 02 */
p_ship_line_id , /* 03 */
p_table_name , /* 04 */
p_column_name , /* 05 */
p_column_value , /* 06 */
SYSDATE , /* 07 */
p_error_message_name , /* 08 */
p_error_message , /* 09 */
p_token1_name , /* 10 */
p_token1_value , /* 11 */
p_token2_name , /* 12 */
p_token2_value , /* 13 */
p_token3_name , /* 14 */
p_token3_value , /* 15 */
p_token4_name , /* 16 */
p_token4_value , /* 17 */
p_token5_name , /* 18 */
p_token5_value , /* 19 */
p_token6_name , /* 20 */
p_token6_value , /* 21 */
L_FND_USER_ID , /* 22 */
SYSDATE , /* 23 */
L_FND_USER_ID , /* 24 */
SYSDATE , /* 25 */
L_FND_LOGIN_ID , /* 26 */ --SCM-051
L_FND_CONC_PROGRAM_ID , /* 27 */
L_FND_PROG_APPL_ID , /* 28 */
SYSDATE , /* 29 */
L_FND_CONC_REQUEST_ID /* 30 */
) ;
l_debug_info := 'Delete Errors from previous validation.';
DELETE FROM inl_ship_holds
WHERE ship_header_id = p_ship_header_id;
DELETE
FROM inl_ship_holds
WHERE ship_header_id = p_ship_header_id
AND ship_line_id = p_ship_line_id;
DELETE
FROM inl_ship_holds
WHERE ship_header_id = p_ship_header_id
AND ship_line_id = p_charge_line_id;
SELECT pre_receive
INTO l_pre_receive
FROM rcv_parameters
WHERE organization_id = p_organization_id;
l_debug_info := 'Update the current INL_SHIP_HEADERS_ALL.ship_status_code to COMPLETED';
UPDATE inl_ship_headers
SET ship_status_code = 'COMPLETED',
last_updated_by = L_FND_USER_ID,
last_update_date = SYSDATE
WHERE ship_header_id = p_ship_header_id;
PROCEDURE Delete_Allocations(
p_ship_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_program_name CONSTANT VARCHAR2 (100) := 'Delete_Allocations ';
DELETE
FROM inl_allocations cfa
WHERE cfa.ship_header_id = p_ship_header_id
AND NOT EXISTS
(
SELECT 'x'
FROM inl_allocations al1
WHERE al1.ship_header_id = cfa.ship_header_id
AND al1.adjustment_num > 0
AND ROWNUM < 2
) ;
l_debug_info := 'deleted '||sql%ROWCOUNT||' allocations rows ';
END Delete_Allocations;
SELECT sh.ship_status_code
INTO l_ship_status
FROM inl_ship_headers sh
WHERE sh.ship_header_id = p_ship_header_id;
Delete_Allocations (p_ship_header_id, l_return_status) ;
UPDATE inl_ship_headers
SET ship_status_code = 'VALIDATION REQ'
WHERE ship_header_id = p_ship_header_id;
SELECT slg.ship_line_group_id, slg.ship_line_group_num, COUNT(ol.ship_line_id) count_ship_line
FROM inl_ship_line_groups slg,
inl_adj_ship_lines_v ol
WHERE ol.ship_header_id (+) = slg.ship_header_id
AND ol.ship_line_group_id (+) = slg.ship_line_group_id
AND slg.ship_header_id = p_ship_header_id
GROUP BY slg.ship_line_group_id, slg.ship_line_group_num
ORDER BY slg.ship_line_group_id;
SELECT ship_num
INTO l_ship_header_num
FROM inl_ship_headers
WHERE ship_header_id = p_ship_header_id;
SELECT ship_line_num
INTO l_ship_line_num
FROM inl_adj_ship_lines_v
WHERE NVL (parent_ship_line_id, ship_line_id) = p_ship_line_id;
SELECT rsl.shipment_line_id,
rsl.item_id,
pll.unit_meas_lookup_code
FROM rcv_shipment_lines rsl,
po_line_locations_all pll
WHERE rsl.lcm_shipment_line_id IS NOT NULL
AND pll.line_location_id = rsl.po_line_location_id
AND rsl.po_line_location_id = p_parent_id
AND NOT EXISTS(SELECT 1
FROM rcv_transactions_interface rti,
rcv_transactions rt
WHERE rti.shipment_line_id (+) = rt.shipment_line_id -- Bug #8235573
AND rsl.shipment_line_id = rt.shipment_line_id);
SELECT pre_receive
INTO l_pre_receive
FROM rcv_parameters
WHERE organization_id = p_inv_org_id;
SELECT uom_code
INTO x_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = x_unit_of_measure;
SELECT mum.unit_of_measure_tl
INTO l_txn_uom_tl
FROM mtl_units_of_measure_vl mum
WHERE mum.uom_code = p_txn_uom_code;
SELECT unit_of_measure_tl
INTO l_primary_uom_tl
FROM mtl_units_of_measure_vl
WHERE uom_code = p_primary_uom_code;
SELECT DECODE (pll.qty_rcv_exception_code, 'NONE', 'Y', 'N')
INTO l_acpt_qty
FROM po_line_locations pll
WHERE pll.line_location_id = p_ship_line_src_id;
SELECT NVL (SUM (sl.primary_qty), 0)
INTO x_qty_in_others_ops
FROM inl_ship_headers sh,
inl_adj_ship_lines_v sl
WHERE sl.ship_header_id = sh.ship_header_id
AND NVL (sl.parent_ship_line_id, sl.ship_line_id) <> p_ship_line_id
AND
(
sh.ship_status_code = 'VALIDATED'
OR sh.ship_header_id = NVL (p_same_shiph_id, 9999999999)
)
AND sh.rcv_enabled_flag = 'Y' -- Bug #8981485
AND sl.ship_line_src_type_code = p_ship_line_src_type_code
AND sl.ship_line_source_id = p_ship_line_src_id;
SELECT DISTINCT sh.ship_num,
sl.ship_line_num
BULK COLLECT INTO l_ship_qty_validation_inf_tbl
FROM inl_ship_headers sh,
inl_adj_ship_lines_v sl
WHERE sl.ship_header_id = sh.ship_header_id
AND NVL (sl.parent_ship_line_id, sl.ship_line_id) <> p_ship_line_id
AND
(
sh.ship_status_code = 'VALIDATED'
OR sh.ship_header_id = NVL (p_same_shiph_id, 9999999999)
)
AND sh.rcv_enabled_flag = 'Y' -- Bug 9735125
AND sl.ship_line_src_type_code = p_ship_line_src_type_code
AND sl. ship_line_source_id = p_ship_line_src_id
ORDER BY sh.ship_num, sl.ship_line_num;
SELECT DISTINCT sh.ship_num
FROM inl_ship_headers sh,
inl_adj_ship_lines_v sl
WHERE sl.ship_header_id = sh.ship_header_id
AND NVL (sl.parent_ship_line_id, sl.ship_line_id) <> p_ship_line_id
AND
(
sh.ship_status_code = 'VALIDATED'
OR sh.ship_header_id = NVL (p_same_shiph_id, 9999999999)
)
AND sh.rcv_enabled_flag = 'Y' -- Bug 9735125
AND sl.ship_line_src_type_code = p_ship_line_src_type_code
AND sl. ship_line_source_id = p_ship_line_src_id
ORDER BY sh.ship_num
)
LOOP
l_msg := l_msg||c_ship_num.ship_num||', ';-- Bug 9735125
SELECT po_price_toler_perc
INTO l_po_price_toler_perc
FROM inl_parameters
WHERE organization_id = p_organization_id;
SELECT po.unit_price,
po.currency_code, --BUG#7670307
po.currency_conversion_type, --BUG#7670307
po.currency_conversion_date, --BUG#7670307
po.currency_conversion_rate, --BUG#7670307
muom.uom_code, --BUG#7670307
po.ordered_qty,
po.item_id
INTO l_po_unit_price,
l_po_currency_code,
l_po_currency_conversion_type,
l_po_currency_conversion_date,
l_po_currency_conversion_rate,
l_po_UOM_code,
l_po_qty,
l_inventory_item_id
FROM inl_enter_receipts_v po, -- BUG#8229331 (Pls see bug 9179775)
mtl_units_of_measure muom
WHERE po.po_line_location_id = p_ship_line_src_id
AND muom.unit_of_measure (+) = po.ordered_uom;
SELECT ish.ship_status_code
INTO l_ship_status_code
FROM inl_ship_headers_all ish
WHERE ish.ship_header_id = p_ship_header_id;
SELECT DECODE (cl.currency_code,
p_functional_currency_code,
cl.charge_amt,
cl.charge_amt * cl.currency_conversion_rate) AS ch_line_amt_func_curr,
cl.currency_code,
cl.charge_line_id,
cl.charge_line_num
FROM inl_adj_charge_lines_v cl,
inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND assoc.from_parent_table_id = NVL (cl.parent_charge_line_id, cl.charge_line_id)
AND assoc.ship_header_id = p_ship_header_id
AND assoc.to_parent_table_name = p_to_parent_table_name
AND assoc.to_parent_table_id = p_to_parent_table_id
ORDER BY cl.charge_line_num;
SELECT ish.org_id LCM_ORG_ID,
ph.org_id PO_ORG_ID
INTO l_lcm_org_id,
l_po_org_id
FROM inl_ship_headers_all ish,
inl_ship_lines_all isl,
po_headers_all ph,
po_line_locations_all pll
WHERE isl.ship_header_id = ish.ship_header_id
AND ph.po_header_id = pll.po_header_id
AND pll.line_location_id = isl.ship_line_source_id
AND isl.ship_line_src_type_code = 'PO'
AND ish.ship_header_id = p_ship_header_id
AND isl.ship_line_id = p_ship_line_id;
SELECT NAME
INTO l_lcm_org_name
FROM hr_operating_units
WHERE organization_id = l_lcm_org_id;
SELECT NAME
INTO l_po_org_name
FROM hr_operating_units
WHERE organization_id = l_po_org_id;
SELECT hps.party_site_id
INTO l_party_site_id
FROM hz_party_sites hps,
hz_locations hl
WHERE hps.location_id = hl.location_id
AND hps.party_site_id = p_party_site_id
AND ((p_third_parties_allowed = 1
AND hl.country = p_taxation_country )
OR (p_third_parties_allowed = 2
AND hl.country <> p_taxation_country )
OR ( p_third_parties_allowed = 3 ));
SELECT
ph.vendor_id,
ph.vendor_site_id,
pv.party_id,
pvs.party_site_id,
hps.party_site_name,
pv.vendor_name,
pvs.vendor_site_code,
COUNT(*) how_many,
MAX(sl.ship_line_num) max_ship_line_num
FROM
inl_ship_lines_all sl,
po_headers_all ph,
po_line_locations_all pll,
po_vendors pv,
po_vendor_sites_all pvs,
hz_party_sites hps
WHERE
ph.po_header_id = pll.po_header_id
AND hps.party_site_id = pvs.party_site_id
AND pll.line_location_id = sl.ship_line_source_id
AND pv.vendor_id = ph.vendor_id
AND pvs.vendor_site_id = ph.vendor_site_id
AND sl.ship_header_id = p_ship_header_id
AND sl.ship_line_group_id = p_ln_group_id
GROUP BY
ph.vendor_id,
ph.vendor_site_id,
pv.party_id,
pvs.party_site_id,
hps.party_site_name,
pv.vendor_name,
pvs.vendor_site_code
ORDER BY 1,2
;
SELECT
sl.ship_line_num
FROM
inl_ship_lines_all sl,
po_headers_all ph,
po_line_locations_all pll,
po_vendors pv,
po_vendor_sites_all pvs,
hz_party_sites hps
WHERE
ph.po_header_id = pll.po_header_id
AND hps.party_site_id = pvs.party_site_id
AND pll.line_location_id = sl.ship_line_source_id
AND pv.vendor_id = ph.vendor_id
AND pvs.vendor_site_id = ph.vendor_site_id
AND sl.ship_header_id = p_ship_header_id
AND sl.ship_line_group_id = p_ln_group_id
AND pvs.party_site_id = pc_party_site_id
ORDER BY 1
;
SELECT pre_receive
INTO l_pre_receive
FROM rcv_parameters
WHERE organization_id = p_inv_org_id;
SELECT NVL(COUNT(1),0)
INTO l_count_associations
FROM inl_associations ia
WHERE ship_header_id = p_ship_header_id
AND ia.from_parent_table_name = 'INL_SHIP_LINES'
AND ia.from_parent_table_id = p_ship_line_id;
SELECT ol.ship_line_id,
ol.ship_line_num,
ol.ship_line_src_type_code,
ol.ship_line_source_id,
ol.inventory_item_id,
(SELECT SUM (a.primary_qty)
FROM inl_adj_ship_lines_v a
WHERE a.ship_header_id = ol.ship_header_id
AND a.ship_line_source_id = ol.ship_line_source_id) AS sum_primary_qty,
ol.primary_qty,
ol.primary_unit_price,
ol.primary_uom_code,
ol.txn_uom_code,
ol.currency_code,
ol.currency_conversion_type,
ol.currency_conversion_date,
ol.currency_conversion_rate,
'INL_SHIP_LINES' to_parent_table_name
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_header_id = p_ship_header_id
AND ol.ship_line_group_id = p_ln_group_id
ORDER BY ol.ship_line_num;
SELECT pre_receive
INTO l_pre_receive
FROM rcv_parameters
WHERE organization_id = p_organization_id;
SELECT DECODE(FND_PROFILE.VALUE('RCV_CLOSED_PO_DEFAULT_OPTION'),'N',NVL(pll.closed_code,'OPEN'), 'OPEN')
INTO l_closed_code
FROM po_line_locations pll
WHERE pll.line_location_id= l_ship_lines_rec.ship_line_source_id;
SELECT isl.ship_line_num, islg.ship_line_group_num
INTO l_ship_line_num,l_ship_ln_grp_num
FROM inl_ship_lines isl,
inl_ship_line_groups islg
WHERE isl.ship_line_group_id=islg.ship_line_group_id
AND isl.ship_line_id = l_ship_lines_rec.ship_line_id
AND isl.ship_header_id = p_ship_header_id;
SELECT ship_line_group_num, ship_line_group_id, lg.party_id, lg.party_site_id,
lg.ship_line_group_reference, hps.party_site_name
FROM inl_ship_line_groups lg,
hz_party_sites hps
WHERE hps.party_site_id = lg.party_site_id
AND ship_header_id = p_ship_header_id
ORDER BY ship_line_group_num;
SELECT shipt.ship_type_id,
shipt.ship_type_code,
shipt.trd_pties_alwd_code,
NVL(sh.rcv_enabled_flag,'Y') rcv_enabled_flag, -- dependence
sh.ship_status_code,
sh.organization_id,
sh.location_id,
sh.ship_date,
sh.pending_matching_flag,
sh.taxation_country -- Bug #8271690
INTO l_ship_type_id,
l_ship_type_code,
l_shipt_third_parties_allowed,
l_rcv_enabled_flag,
l_ship_status,
l_inv_org_id,
l_location_id,
l_ship_date,
l_pending_matching_flag,
l_taxation_country
FROM inl_ship_headers_all sh,
inl_ship_types_vl shipt
WHERE sh.ship_type_id = shipt.ship_type_id
AND sh.ship_header_id = p_ship_header_id;
SELECT set_of_books_id
INTO l_sob_id
FROM org_organization_definitions ood
WHERE organization_id = l_inv_org_id;
SELECT gsb.currency_code
INTO l_func_curr_code
FROM org_organization_definitions ood,
gl_sets_of_books gsb
WHERE gsb.set_of_books_id = ood.set_of_books_id
AND organization_id = l_inv_org_id;
SELECT country
INTO l_country_code_location
FROM hr_locations
WHERE location_id = l_location_id;
SELECT ship_status_code
INTO l_ship_status
FROM inl_ship_headers
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Update INL_SHIP_HEADERS_ALL.ship_status_code to VALIDATED';
UPDATE inl_ship_headers
SET ship_status_code = 'VALIDATED',
last_updated_by = L_FND_USER_ID,
last_update_date = SYSDATE
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Update INL_SHIP_HEADERS_ALL.ship_status_code to ON HOLD';
UPDATE inl_ship_headers
SET ship_status_code = 'ON HOLD',
last_updated_by = L_FND_USER_ID,
last_update_date = SYSDATE
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Insert in inl_associations.';
INSERT
INTO inl_associations (
association_id, /* 01 */
ship_header_id, /* 02 */
from_parent_table_name, /* 03 */
from_parent_table_id, /* 04 */
to_parent_table_name, /* 05 */
to_parent_table_id, /* 06 */
allocation_basis, /* 07 */
allocation_uom_code, /* 08 */
created_by, /* 09 */
creation_date, /* 10 */
last_updated_by, /* 11 */
last_update_date, /* 12 */
last_update_login /* 13 */
)
VALUES (
inl_associations_s.nextval, /* 01 */
p_Assoc.ship_header_id, /* 02 */
p_from_parent_table_name, /* 03 */
p_new_line_id, /* 04 */
p_Assoc.to_parent_table_name, /* 05 */
p_Assoc.to_parent_table_id, /* 06 */
p_Assoc.allocation_basis, /* 07 */
p_Assoc.allocation_uom_code, /* 08 */
L_FND_USER_ID, /* 09 */
sysdate, /* 10 */
L_FND_USER_ID, /* 11 */
sysdate, /* 12 */
l_fnd_login_id /* 13 */ --SCM-051
);
UPDATE inl_associations
SET
ship_header_id = p_Assoc.ship_header_id ,
from_parent_table_name = p_from_parent_table_name ,
from_parent_table_id = p_new_line_id ,
to_parent_table_name = p_Assoc.to_parent_table_name,
to_parent_table_id = p_Assoc.to_parent_table_id ,
allocation_basis = p_Assoc.allocation_basis ,
allocation_uom_code = p_Assoc.allocation_uom_code ,
last_updated_by = L_FND_USER_ID ,
last_update_date = SYSDATE ,
last_update_login = l_fnd_login_id --SCM-051
WHERE association_id = p_Assoc.association_id
;
SELECT NVL (MAX (tl.tax_line_num), 0) + 1
INTO l_tax_line_num
FROM inl_tax_lines tl
WHERE NVL (tl.ship_header_id, 0) = NVL (p_TxLn_Assoc.inl_Assoc.ship_header_id, 0) ;
SELECT inl_tax_lines_s.nextval
INTO x_new_tax_line_id
FROM dual;
INSERT
INTO inl_tax_lines (
tax_line_id, /* 01 */
tax_line_num, /* 02 */
tax_code, /* 03 */
ship_header_id, /* 04 */
parent_tax_line_id, /* 05 */
adjustment_num, /* 06 */
match_id, /* 07 */
match_amount_id, /* 08 */
source_parent_table_name, /* 09 */
source_parent_table_id, /* 10 */
tax_amt, /* 11 */
nrec_tax_amt, /* 12 */
currency_code, /* 13 */
currency_conversion_type, /* 14 */
currency_conversion_date, /* 15 */
currency_conversion_rate, /* 16 */
tax_amt_included_flag, /* 17 */
created_by, /* 18 */
creation_date, /* 19 */
last_updated_by, /* 20 */
last_update_date, /* 21 */
last_update_login /* 22 */
)
VALUES
(
x_new_tax_line_id, /* 01 */
l_tax_line_num, /* 02 */
p_TxLn_Assoc.tax_code, /* 03 */
p_TxLn_Assoc.inl_Assoc.ship_header_id, /* 04 */
p_TxLn_Assoc.parent_tax_line_id, /* 05 */
l_adjustment_num, /* 06 */
p_TxLn_Assoc.match_id, /* 07 */
p_TxLn_Assoc.match_amount_id, /* 08 */
p_TxLn_Assoc.source_parent_table_name, /* 09 */
p_TxLn_Assoc.source_parent_table_id, /* 10 */
p_TxLn_Assoc.matched_amt, /* 11 */
p_TxLn_Assoc.nrec_tax_amt, /* 12 */
p_TxLn_Assoc.currency_code, /* 13 */
p_TxLn_Assoc.currency_conversion_type, /* 14 */
p_TxLn_Assoc.currency_conversion_date, /* 15 */
p_TxLn_Assoc.currency_conversion_rate, /* 16 */
p_TxLn_Assoc.tax_amt_included_flag, /* 17 */
L_FND_USER_ID, /* 18 */
sysdate, /* 19 */
L_FND_USER_ID, /* 20 */
sysdate, /* 21 */
l_fnd_login_id --SCM-051 /* 22 */
) ;
SELECT tl.tax_line_id ,
tl.tax_line_num ,
tl.tax_code ,
tl.ship_header_id ,
tl.parent_tax_line_id ,
tl.adjustment_num ,
tl.match_id ,
tl.source_parent_table_name,
tl.source_parent_table_id ,
tl.tax_amt ,
tl.nrec_tax_amt ,
tl.currency_code ,
tl.currency_conversion_type,
tl.currency_conversion_date,
tl.currency_conversion_rate,
tl.tax_amt_included_flag
FROM inl_adj_tax_lines_v tl
WHERE p_ship_header_id IS NOT NULL --Bug#14044298
AND tl.adjustment_num = p_prev_adjustment_num
AND tl.ship_header_id = p_ship_header_id
AND
(
p_tax_code IS NULL
OR tl.tax_code = p_tax_code
)
UNION ALL
SELECT tl.tax_line_id ,
tl.tax_line_num ,
tl.tax_code ,
tl.ship_header_id ,
tl.parent_tax_line_id ,
tl.adjustment_num ,
tl.match_id ,
tl.source_parent_table_name,
tl.source_parent_table_id ,
tl.tax_amt ,
tl.nrec_tax_amt ,
tl.currency_code ,
tl.currency_conversion_type,
tl.currency_conversion_date,
tl.currency_conversion_rate,
tl.tax_amt_included_flag
FROM
inl_adj_tax_lines_v tl,
inl_associations assoc
WHERE p_ship_header_id IS NULL
AND (tl.tax_amt > 0
OR tl.nrec_tax_amt > 0) --SCM-051
AND (tl.adjustment_num = p_prev_adjustment_num
OR tl.adjustment_num < 1)
AND
(
p_tax_code IS NULL
OR tl.tax_code = p_tax_code
)
-- SCM-051
AND assoc.from_parent_table_name = 'INL_TAX_LINES'
AND assoc.to_parent_table_name = p_comp_name
AND assoc.to_parent_table_id = p_comp_id
AND assoc.from_parent_table_id =
(
SELECT parntTxLn0.tax_line_id
FROM inl_tax_lines parntTxLn0
WHERE CONNECT_BY_ISLEAF = 1
START WITH parntTxLn0.tax_line_id = tl.tax_line_id
CONNECT BY PRIOR parntTxLn0.parent_tax_line_id = parntTxLn0.tax_line_id
)
-- SCM-051
;
SELECT tl.tax_line_id ,
tl.tax_line_num ,
tl.tax_code ,
tl.ship_header_id ,
tl.parent_tax_line_id ,
tl.adjustment_num ,
tl.match_id ,
tl.source_parent_table_name,
tl.source_parent_table_id ,
tl.tax_amt ,
tl.nrec_tax_amt ,
tl.currency_code ,
tl.currency_conversion_type,
tl.currency_conversion_date,
tl.currency_conversion_rate,
tl.tax_amt_included_flag
FROM inl_adj_tax_lines_v tl
WHERE match_Amount_id = p_match_Amount_id
AND NOT EXISTS (SELECT 1
FROM INL_TAX_LINES t
WHERE t.parent_tax_line_id = tl.tax_line_id)
;
SELECT mat.matched_qty,
mat.match_id ,
DECODE (pc_to_currency_code,
mat.matched_curr_code,
mat.matched_amt,
inl_landedcost_pvt.Converted_Amt (
mat.matched_amt,
mat.matched_curr_code,
pc_to_currency_code,
pc_to_curr_conversion_type,
pc_to_curr_conversion_date
)) matched_amt
FROM inl_corr_matches_v mat
WHERE mat.to_parent_table_name = 'INL_SHIP_LINES'
AND mat.match_type_code = 'ITEM'
AND mat.to_parent_table_id = pc_ship_line_id
AND NOT EXISTS
(
SELECT 1
FROM inl_corr_matches_v m2
WHERE m2.from_parent_table_name = mat.from_parent_table_name
AND m2.from_parent_table_id = mat.from_parent_table_id
AND m2.match_id > mat.match_id
)
AND mat.match_id <> pc_curr_match_id
ORDER BY match_id ;
SELECT rt.transaction_id,
um.uom_code
INTO l_rt_transaction_id,
l_rt_uom_code
FROM mtl_units_of_measure um,
rcv_transactions rt,
inl_ship_lines sl
WHERE um.unit_of_measure = rt.unit_of_measure
AND rt.destination_type_code = 'RECEIVING'
AND ((rt.transaction_type = 'RECEIVE'
AND rt.parent_transaction_id = -1)
OR (rt.transaction_type = 'MATCH' -- Bug#9275335
))
AND rt.lcm_shipment_line_id = sl.ship_line_id
AND rt.po_line_location_id = sl.ship_line_source_id
AND sl.ship_line_src_type_code = 'PO'
AND sl.ship_line_id = p_to_ship_line_id;
SELECT
mat.ship_header_id , /* 01 */
mat.to_parent_table_id, /* 02 */
mat.matched_qty, /* 03 */
mat.matched_uom_code, /* 04 */
mat.replace_estim_qty_flag, /* 05 */
mat.existing_match_info_flag, /* 06 */
mat.matched_amt, /* 07 */
-- sl.txn_qty, /* 08 */ -- Bug #7702294
sl.primary_uom_code, /* 09a*/
sl.secondary_uom_code, /* 09b*/
sl.txn_uom_code, /* 09c*/ --Bug #7674121
sh.organization_id, /* 10 */
sl.inventory_item_id, /* 11 */
sl.ship_line_num, /* 12 */
sl.ship_line_group_id, /* 13 */
sl.currency_code, /* 14 */
mat.matched_curr_code, /* 15 */
mat.matched_curr_conversion_type, /* 16 */ --BUG#8468830
mat.matched_curr_conversion_rate , /* 16a*/ --BUG#8468830
sl.currency_conversion_type , /* 16b*/ --BUG#8468830
NVL (mat.matched_curr_conversion_date, sl.currency_conversion_date), /* 17 */
sl.currency_conversion_rate, /* 17a*/ --BUG#8468830
sl.txn_unit_price, /* 18 */
mat.from_parent_table_name, /* 19 */
mat.from_parent_table_id, /* 20 */
mat.match_id /* 21 */
INTO
l_to_ship_header_id , /* 01 */
l_to_ship_line_id , /* 02 */
l_matched_qty , /* 03 */
l_matched_uom_code , /* 04 */
l_replace_estim_qty_flag , /* 05 */
l_existing_match_info_flag, /* 06 */
l_matched_amt , /* 07 */
-- l_net_rcv_txn_qty , /* 08 */ -- Bug #7702294
l_primary_uom_code , /* 09a*/
l_secondary_uom_code , /* 09b*/
l_txn_uom_code , /* 09c*/ --Bug #7674121
l_inv_org_id , /* 10 */
l_sl_inv_item_id , /* 11 */
l_ship_line_num , /* 12 */
l_ship_line_group_id , /* 13 */
l_ship_line_curr_code , /* 14 */
l_current_curr_code , /* 15 */
l_current_curr_conv_type , /* 16 */
l_current_curr_conv_rate , /* 16a*/ --BUG#8468830
l_ship_line_curr_conv_type, /* 16b*/ --BUG#8468830
l_current_curr_conv_date , /* 17 */
l_ship_line_curr_conv_rate, /* 17a*/ --BUG#8468830
l_ori_unit_price , /* 18 */
l_from_parent_table_name , /* 19 */
l_from_parent_table_id , /* 20 */
l_curr_match_id /* 21 */
FROM inl_corr_matches_v mat,
inl_ship_lines sl ,
inl_ship_lines sl0 ,
inl_ship_headers sh
WHERE mat.match_id = p_match_id
AND mat.to_parent_table_name = 'INL_SHIP_LINES'
AND mat.adj_already_generated_flag = 'N'
AND sl.ship_header_id = sh.ship_header_id
AND sl0.ship_line_id = mat.to_parent_table_id
AND sl0.ship_header_id = sl.ship_header_id
AND sl0.ship_line_group_id = sl.ship_line_group_id
AND sl0.ship_line_num = sl.ship_line_num
AND sl.adjustment_num =
(
SELECT MIN(sl2.adjustment_num)
FROM inl_ship_lines sl2
WHERE sl0.ship_header_id = sl2.ship_header_id
AND sl0.ship_line_group_id = sl2.ship_line_group_id
AND sl0.ship_line_num = sl2.ship_line_num
)
AND (mat.correction_match_id is null OR
mat.correction_match_id = (select min(mat2.correction_match_id)
FROM inl_corr_matches_v mat2
WHERE mat2.match_id = p_match_id
AND mat2.to_parent_table_name = 'INL_SHIP_LINES'
AND mat2.adj_already_generated_flag = 'N'
));
SELECT rt.transaction_id,
um.uom_code
INTO l_rt_transaction_id,
l_rt_uom_code
FROM mtl_units_of_measure um,
rcv_transactions rt,
inl_ship_lines sl
WHERE um.unit_of_measure = rt.unit_of_measure
AND rt.destination_type_code = 'RECEIVING'
AND ((rt.transaction_type = 'RECEIVE'
AND rt.parent_transaction_id = -1)
OR (rt.transaction_type = 'MATCH' -- Bug#9275335
))
AND rt.lcm_shipment_line_id = sl.ship_line_id
AND rt.po_line_location_id = sl.ship_line_source_id
AND sl.ship_line_src_type_code = 'PO'
AND sl.ship_line_id = l_to_ship_line_id;
SELECT txn_qty
INTO l_net_rcv_txn_qty
FROM inl_ship_lines
WHERE ship_header_id = l_to_ship_header_id
AND ship_line_num = l_ship_line_num
AND ship_line_group_id = l_ship_line_group_id
AND adjustment_num = 0
;
SELECT txn_unit_price,
txn_qty ,
primary_qty ,
primary_unit_price
INTO l_final_price,
l_final_qty ,
l_primary_qty ,
l_primary_unit_price
FROM inl_adj_ship_lines_v
WHERE ship_header_id = l_to_ship_header_id
AND ship_line_group_id = l_ship_line_group_id
AND ship_line_num = l_ship_line_num;
INSERT
INTO inl_ship_lines_all
(
ship_header_id, /* 01 */
ship_line_group_id, /* 02 */
ship_line_id, /* 03 */
ship_line_num, /* 04 */
ship_line_type_id, /* 05 */
ship_line_src_type_code, /* 06 */
ship_line_source_id, /* 07 */
parent_ship_line_id, /* 08 */
adjustment_num, /* 09 */
match_id, /* 10 */
currency_code, /* 12 */
currency_conversion_type, /* 13 */
currency_conversion_date, /* 14 */
currency_conversion_rate, /* 15 */
inventory_item_id, /* 16 */
txn_qty, /* 17 */
txn_uom_code, /* 18 */
txn_unit_price, /* 19 */
primary_qty, /* 20 */
primary_uom_code, /* 21 */
primary_unit_price, /* 22 */
secondary_qty, /* 23 */
secondary_uom_code, /* 24 */
secondary_unit_price, /* 25 */
landed_cost_flag, /* 30 */
allocation_enabled_flag, /* 31 */
trx_business_category, /* 32 */
intended_use, /* 33 */
product_fiscal_class, /* 34 */
product_category, /* 35 */
product_type, /* 36 */
user_def_fiscal_class, /* 37 */
tax_classification_code, /* 38 */
assessable_value, /* 39 */
tax_already_calculated_flag,/* 40 */
ship_from_party_id, /* 41 */
ship_from_party_site_id, /* 42 */
ship_to_organization_id, /* 43 */
ship_to_location_id, /* 44 */
bill_from_party_id, /* 45 */
bill_from_party_site_id, /* 46 */
bill_to_organization_id, /* 47 */
bill_to_location_id, /* 48 */
poa_party_id, /* 49 */
poa_party_site_id, /* 50 */
poo_organization_id, /* 51 */
poo_location_id, /* 52 */
org_id, /* 53 */
created_by, /* 54 */
creation_date, /* 55 */
last_updated_by, /* 56 */
last_update_date, /* 57 */
last_update_login, /* 58 */
program_id, /* 59 */
program_update_date, /* 60 */
program_application_id, /* 61 */
request_id, /* 62 */
attribute_category, /* 63 */
attribute1, /* 64 */
attribute2, /* 65 */
attribute3, /* 66 */
attribute4, /* 67 */
attribute5, /* 68 */
attribute6, /* 69 */
attribute7, /* 70 */
attribute8, /* 71 */
attribute9, /* 72 */
attribute10, /* 73 */
attribute11, /* 74 */
attribute12, /* 75 */
attribute13, /* 76 */
attribute14, /* 77 */
attribute15, /* 78 */
nrq_zero_exception_flag /* 79 */ --BUG#8334078
)
SELECT sl.ship_header_id, /* 01 */
sl.ship_line_group_id, /* 02 */
inl_ship_lines_all_s.NEXTVAL, /* 03 */
sl.ship_line_num, /* 04 */
sl.ship_line_type_id, /* 05 */
sl.ship_line_src_type_code, /* 06 */
sl.ship_line_source_id, /* 07 */
sl.ship_line_id, /* 08 */
l_next_adjust_num, /* 09 */
p_match_id, /* 10 */
l_current_curr_code, /* 12 */
l_current_curr_conv_type, /* 13 */
l_current_curr_conv_date, /* 14 */
l_current_curr_conv_rate, /* 15 */
sl.inventory_item_id, /* 16 */
l_final_qty, /* 17 */
l_txn_uom_code, /* 18 */ --BUG#8198265
l_final_price, /* 19 */
l_primary_qty, /* 20 */
l_primary_uom_code, /* 21 */ --BUG#8198265
l_primary_unit_price, /* 22 */
NVL(l_secondary_qty,sl.secondary_qty), /* 23 */
sl.secondary_uom_code, /* 24 */
NVL(l_secondary_unit_price, sl.secondary_unit_price), /* 25 */
sl.landed_cost_flag, /* 30 */
sl.allocation_enabled_flag, /* 31 */
sl.trx_business_category, /* 32 */
sl.intended_use, /* 33 */
sl.product_fiscal_class, /* 34 */
sl.product_category, /* 35 */
sl.product_type, /* 36 */
sl.user_def_fiscal_class, /* 37 */
sl.tax_classification_code, /* 38 */
sl.assessable_value, /* 39 */
'N' , -- tax_already_calculated_flag/* 40 */
sl.ship_from_party_id, /* 41 */
sl.ship_from_party_site_id, /* 42 */
sl.ship_to_organization_id, /* 43 */
sl.ship_to_location_id, /* 44 */
sl.bill_from_party_id, /* 45 */
sl.bill_from_party_site_id, /* 46 */
sl.bill_to_organization_id, /* 47 */
sl.bill_to_location_id, /* 48 */
sl.poa_party_id, /* 49 */
sl.poa_party_site_id, /* 50 */
sl.poo_organization_id, /* 51 */
sl.poo_location_id, /* 52 */
sl.org_id, /* 53 */
sl.created_by, /* 54 */
sl.creation_date, /* 55 */
sl.last_updated_by, /* 56 */
sl.last_update_date, /* 57 */
sl.last_update_login, /* 58 */
sl.program_id, /* 59 */
sl.program_update_date, /* 60 */
sl.program_application_id, /* 61 */
sl.request_id, /* 62 */
sl.attribute_category, /* 63 */
sl.attribute1, /* 64 */
sl.attribute2, /* 65 */
sl.attribute3, /* 66 */
sl.attribute4, /* 67 */
sl.attribute5, /* 68 */
sl.attribute6, /* 69 */
sl.attribute7, /* 70 */
sl.attribute8, /* 71 */
sl.attribute9, /* 72 */
sl.attribute10, /* 73 */
sl.attribute11, /* 74 */
sl.attribute12, /* 75 */
sl.attribute13, /* 76 */
sl.attribute14, /* 77 */
sl.attribute15, /* 78 */
l_nrq_zero_exception_flag /* 79 */ --BUG#8334078
FROM inl_ship_lines sl
WHERE ship_line_id = l_to_ship_line_id;
SELECT NVL(MAX(cl.charge_line_num),0) + 1
INTO l_charge_line_num
FROM inl_charge_lines cl
WHERE EXISTS
(SELECT 1
FROM inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND assoc.from_parent_table_id = cl.charge_line_id
AND assoc.ship_header_id
IN (select m1.ship_header_id
from inl_corr_matches_v m1
where m1.match_amount_id = p_ChLn_Assoc.match_amount_id
)
)
;
SELECT NVL (MAX (cl.charge_line_num), 0) + 1
INTO l_charge_line_num
FROM inl_charge_lines cl
WHERE NVL (cl.parent_charge_line_id, cl.charge_line_id) IN
(
SELECT assoc.from_parent_table_id
FROM inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND assoc.ship_header_id = NVL (p_ChLn_Assoc.inl_Assoc.ship_header_id, 0)
) ;
SELECT inl_charge_lines_s.nextval
INTO x_new_charge_line_id FROM dual;
INSERT
INTO inl_charge_lines
(
charge_line_id, /* 01 */
charge_line_num, /* 02 */
charge_line_type_id, /* 03 */
landed_cost_flag, /* 04 */
parent_charge_line_id, /* 06 */
adjustment_num, /* 07 */
match_id, /* 08 */
match_amount_id, /* 09 */
charge_amt, /* 11 */
currency_code, /* 12 */
currency_conversion_type, /* 13 */
currency_conversion_date, /* 14 */
currency_conversion_rate, /* 15 */
party_id, /* 16 */
party_site_id, /* 17 */
trx_business_category, /* 18 */
intended_use, /* 19 */
product_fiscal_class, /* 20 */
product_category, /* 21 */
product_type, /* 22 */
user_def_fiscal_class, /* 23 */
tax_classification_code, /* 24 */
assessable_value, /* 25 */
tax_already_calculated_flag, /* 26 */
ship_from_party_id, /* 27 */
ship_from_party_site_id, /* 28 */
ship_to_organization_id, /* 29 */
ship_to_location_id, /* 30 */
bill_from_party_id, /* 31 */
bill_from_party_site_id, /* 32 */
bill_to_organization_id, /* 34 */
bill_to_location_id, /* 34 */
poa_party_id, /* 35 */
poa_party_site_id, /* 36 */
poo_organization_id, /* 37 */
poo_location_id, /* 38 */
created_by, /* 39 */
creation_date, /* 40 */
last_updated_by, /* 41 */
last_update_date, /* 42 */
last_update_login /* 43 */
)
VALUES
(
x_new_charge_line_id, /* 01 */
l_charge_line_num, /* 02 */
p_ChLn_Assoc.charge_line_type_id, /* 03 */
p_ChLn_Assoc.landed_cost_flag, /* 04 */
p_ChLn_Assoc.parent_charge_line_id, /* 06 */
l_adjustment_num, /* 07 */
p_ChLn_Assoc.match_id, /* 08 */
p_ChLn_Assoc.match_amount_id, /* 09 */
p_ChLn_Assoc.charge_amt, /* 11 */
p_ChLn_Assoc.currency_code, /* 12 */
p_ChLn_Assoc.currency_conversion_type, /* 13 */
p_ChLn_Assoc.currency_conversion_date, /* 14 */
p_ChLn_Assoc.currency_conversion_rate, /* 15 */
p_ChLn_Assoc.party_id, /* 16 */
p_ChLn_Assoc.party_site_id, /* 17 */
p_ChLn_Assoc.trx_business_category, /* 18 */
p_ChLn_Assoc.intended_use, /* 19 */
p_ChLn_Assoc.product_fiscal_class, /* 20 */
p_ChLn_Assoc.product_category, /* 21 */
p_ChLn_Assoc.product_type, /* 22 */
p_ChLn_Assoc.user_def_fiscal_class, /* 23 */
p_ChLn_Assoc.tax_classification_code, /* 24 */
p_ChLn_Assoc.assessable_value, /* 25 */
p_ChLn_Assoc.tax_already_calculated_flag,/* 26 */
p_ChLn_Assoc.ship_from_party_id, /* 27 */
p_ChLn_Assoc.ship_from_party_site_id, /* 28 */
p_ChLn_Assoc.ship_to_organization_id, /* 29 */
p_ChLn_Assoc.ship_to_location_id, /* 30 */
p_ChLn_Assoc.bill_from_party_id, /* 31 */
p_ChLn_Assoc.bill_from_party_site_id, /* 32 */
p_ChLn_Assoc.bill_to_organization_id, /* 33 */
p_ChLn_Assoc.bill_to_location_id, /* 34 */
p_ChLn_Assoc.poa_party_id, /* 35 */
p_ChLn_Assoc.poa_party_site_id, /* 36 */
p_ChLn_Assoc.poo_organization_id, /* 37 */
p_ChLn_Assoc.poo_location_id, /* 38 */
L_FND_USER_ID, /* 39 */
sysdate, /* 40 */
L_FND_USER_ID, /* 41 */
sysdate, /* 42 */
l_fnd_login_id --SCM-051 /* 43 */
) ;
SELECT charge_line_num ,
charge_line_type_id ,
landed_cost_flag ,
charge_line_id ,
adjustment_num ,
match_id ,
currency_code ,
currency_conversion_type ,
currency_conversion_date ,
currency_conversion_rate ,
party_id ,
party_site_id ,
trx_business_category ,
intended_use ,
product_fiscal_class ,
product_category ,
product_type ,
user_def_fiscal_class ,
tax_classification_code ,
tax_already_calculated_flag,
ship_from_party_id ,
ship_from_party_site_id ,
ship_to_organization_id ,
ship_to_location_id ,
bill_from_party_id ,
bill_from_party_site_id ,
bill_to_organization_id ,
bill_to_location_id ,
poa_party_id ,
poa_party_site_id ,
poo_organization_id ,
poo_location_id
FROM inl_charge_lines cl
WHERE cl.match_Amount_id = p_prev_match_amount_id
AND cl.match_id IS NULL
UNION --BUG#9804065
SELECT charge_line_num ,
charge_line_type_id ,
landed_cost_flag ,
charge_line_id ,
adjustment_num ,
match_id ,
currency_code ,
currency_conversion_type ,
currency_conversion_date ,
currency_conversion_rate ,
party_id ,
party_site_id ,
trx_business_category ,
intended_use ,
product_fiscal_class ,
product_category ,
product_type ,
user_def_fiscal_class ,
tax_classification_code ,
tax_already_calculated_flag,
ship_from_party_id ,
ship_from_party_site_id ,
ship_to_organization_id ,
ship_to_location_id ,
bill_from_party_id ,
bill_from_party_site_id ,
bill_to_organization_id ,
bill_to_location_id ,
poa_party_id ,
poa_party_site_id ,
poo_organization_id ,
poo_location_id
FROM inl_charge_lines cl
WHERE p_prev_match_id IS NOT NULL --Bug#14044298
AND cl.match_id = p_prev_match_id
AND cl.match_amount_id IS NULL
;
SELECT distinct --Bug#9804065
cl.charge_line_num ,
cl.charge_line_type_id ,
cl.landed_cost_flag ,
cl.charge_line_id ,
cl.adjustment_num ,
cl.match_id ,
cl.currency_code ,
cl.currency_conversion_type ,
cl.currency_conversion_date ,
cl.currency_conversion_rate ,
cl.party_id ,
cl.party_site_id ,
cl.trx_business_category ,
cl.intended_use ,
cl.product_fiscal_class ,
cl.product_category ,
cl.product_type ,
cl.user_def_fiscal_class ,
cl.tax_classification_code ,
cl.tax_already_calculated_flag,
cl.ship_from_party_id ,
cl.ship_from_party_site_id ,
cl.ship_to_organization_id ,
cl.ship_to_location_id ,
cl.bill_from_party_id ,
cl.bill_from_party_site_id ,
cl.bill_to_organization_id ,
cl.bill_to_location_id ,
cl.poa_party_id ,
cl.poa_party_site_id ,
cl.poo_organization_id ,
cl.poo_location_id
FROM inl_adj_charge_lines_v cl,
inl_associations a,
inl_matches m
WHERE
((cl.match_id IS NULL
AND cl.match_amount_id IS NULL)
OR
cl.adjustment_num < 0) -- SCM-051
AND NVL(cl.charge_amt,0) <> 0
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.ship_header_id = m.ship_header_id
AND a.from_parent_table_id
= (SELECT
cl1.charge_line_id
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = cl.charge_line_id
CONNECT BY PRIOR cl1.parent_charge_line_id = cl1.charge_line_id)
AND cl.charge_line_type_id = p_charge_line_type_id
AND (
m.match_id = p_match_id
OR m.match_amount_id = p_match_amount_id
)
AND (
(m.to_parent_table_name = 'INL_SHIP_HEADERS'
AND a.to_parent_table_name = 'INL_SHIP_HEADERS'
AND m.ship_header_id = a.ship_header_id)
OR
(m.to_parent_table_name = 'INL_SHIP_LINE_GROUPS'
AND m.ship_header_id = a.ship_header_id
AND (a.to_parent_table_name = 'INL_SHIP_HEADERS'
OR (a.to_parent_table_name = 'INL_SHIP_LINE_GROUPS'
AND a.to_parent_table_id = m.to_parent_table_id)
)
)
OR
(m.to_parent_table_name = 'INL_SHIP_LINES'
AND m.ship_header_id = a.ship_header_id
AND (a.to_parent_table_name = 'INL_SHIP_HEADERS'
OR (a.to_parent_table_name = 'INL_SHIP_LINE_GROUPS'
AND EXISTS (SELECT 1
FROM inl_ship_lines sl
WHERE sl.ship_line_id = m.to_parent_table_id -- the match ship line
AND sl.ship_line_group_id = a.to_parent_table_id --belong to group of association
)
)
OR (a.to_parent_table_name = 'INL_SHIP_LINES'
AND m.to_parent_table_id = a.to_parent_table_id
)
)
)
)
;
SELECT
a.ship_header_id ,
a.to_parent_table_name,
a.to_parent_table_id
FROM
inl_associations a
WHERE
a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = pc_charge_line_id
;
SELECT
cl.adjustment_num ,
cl.charge_line_id ,
cl.parent_charge_line_id ,
cl.charge_line_num ,
cl.landed_cost_flag ,
cl.update_allowed ,
cl.source_code ,
cl.charge_amt ,
cl.currency_code ,
cl.currency_conversion_type ,
cl.currency_conversion_date ,
cl.currency_conversion_rate ,
cl.party_id ,
cl.party_site_id ,
cl.trx_business_category ,
cl.intended_use ,
cl.product_fiscal_class ,
cl.product_category ,
cl.product_type ,
cl.user_def_fiscal_class ,
cl.tax_classification_code ,
cl.assessable_value ,
cl.tax_already_calculated_flag,
cl.ship_from_party_id ,
cl.ship_from_party_site_id ,
cl.ship_to_organization_id ,
cl.ship_to_location_id ,
cl.bill_from_party_id ,
cl.bill_from_party_site_id ,
cl.bill_to_organization_id ,
cl.bill_to_location_id ,
cl.poa_party_id ,
cl.poa_party_site_id ,
cl.poo_organization_id ,
cl.poo_location_id ,
a.to_parent_table_name ,
a.to_parent_table_id
FROM
inl_charge_lines cl,
inl_associations a,
inl_ship_lines_all sl,
inl_ship_line_groups slg,
inl_ship_headers_all sh
WHERE
cl.adjustment_num = 0
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = cl.charge_line_id
AND cl.charge_line_type_id = p_charge_line_type_id
AND a.ship_header_id = sl.ship_header_id
AND ((a.to_parent_table_name = 'INL_SHIP_LINES'
AND a.to_parent_table_id = sl.ship_line_id)
OR
(a.to_parent_table_name = 'INL_SHIP_LINE_GROUPS'
AND a.to_parent_table_id = sl.ship_line_group_id)
OR
(a.to_parent_table_name = 'INL_SHIP_HEADERS'
AND a.to_parent_table_id = sl.ship_header_id))
AND sh.ship_header_id = sl.ship_header_id
AND slg.ship_line_group_id = sl.ship_line_group_id
AND sl.ship_line_id = pc_ship_line_id
;
SELECT
cl.charge_line_id,
cl.charge_amt
INTO
l_created_chLnId,
l_created_chLnAmt
FROM
inl_charge_lines cl
WHERE
cl.match_id = p_match_id
AND cl.charge_amt <> 0 --BUG#9804065 --discards lines to zeroes estimated
;
SELECT
cl.charge_line_id,
cl.charge_amt
INTO
l_created_chLnId,
l_created_chLnAmt
FROM
inl_charge_lines cl
WHERE
cl.match_Amount_id = p_match_Amount_id
AND cl.charge_amt <> 0 --BUG#9804065 -- discards lines to zeroes estimated
;
SELECT
cl.charge_line_id,
cl.charge_amt
INTO
l_created_chLnId,
l_created_chLnAmt
FROM
inl_charge_lines cl
WHERE
((p_match_id IS NOT NULL
AND cl.match_id = p_match_id)
OR
(p_match_Amount_id IS NOT NULL
AND cl.match_Amount_id = p_match_Amount_id))
AND (cl.parent_charge_line_id IS NULL
-- OR 0 not in
OR 0 < --SCM-051
(SELECT
cl1.adjustment_num
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = cl.charge_line_id
CONNECT BY PRIOR cl1.parent_charge_line_id = cl1.charge_line_id));
SELECT
SUM(cl.charge_amt)
INTO
l_sum_chLnAmt
FROM
inl_adj_charge_lines_v cl,
inl_adj_associations_v a, --BUG#9804065
inl_ship_lines_all sl,
inl_ship_line_groups slg,
inl_ship_headers_all sh
WHERE
a.ship_header_id = sl.ship_header_id
AND cl.charge_line_type_id = p_charge_line_type_id --same cost factor
AND ((a.to_parent_table_name = 'INL_SHIP_LINES'
AND a.to_parent_table_id = sl.ship_line_id)
OR
(a.to_parent_table_name = 'INL_SHIP_LINE_GROUPS'
AND a.to_parent_table_id = sl.ship_line_group_id)
OR
(a.to_parent_table_name = 'INL_SHIP_HEADERS'
AND a.to_parent_table_id = sl.ship_header_id))
AND sh.ship_header_id = sl.ship_header_id
AND slg.ship_line_group_id = sl.ship_line_group_id
AND sl.ship_line_id = affected_by_alc_ship_lst(i).to_parent_table_id
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = cl.charge_line_id
;
SELECT
COUNT(*)
INTO
l_alc_zero
FROM
inl_charge_lines cl,
inl_associations assoc
WHERE
assoc.to_parent_table_id = affected_by_alc_ship_lst(i).to_parent_table_id
AND assoc.to_parent_table_name = 'INL_SHIP_LINES'
AND assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND assoc.from_parent_table_id = cl.charge_line_id
AND cl.charge_line_type_id = p_charge_line_type_id --same cost factor
AND cl.charge_amt = 0
AND (cl.parent_charge_line_id IS NULL
OR 0 not in
(SELECT
cl1.adjustment_num
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = cl.charge_line_id
CONNECT BY PRIOR cl1.parent_charge_line_id = cl1.charge_line_id));
SELECT
SUM(cl.charge_amt)
INTO
l_sum_chLnAmt
FROM
inl_adj_charge_lines_v cl,
inl_associations a
WHERE
a.ship_header_id = affected_by_elc_lst(k).ship_header_id
AND cl.charge_line_type_id = p_charge_line_type_id --same cost factor
AND NVL(cl.match_id,cl.match_amount_id) IS NOT NULL --only ALC
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = cl.charge_line_id
;
SELECT
COUNT(*)
INTO
l_alc_zero
FROM
inl_charge_lines cl,
inl_associations assoc
WHERE
assoc.ship_header_id = affected_by_elc_lst(k).ship_header_id
AND assoc.to_parent_table_name = 'INL_SHIP_LINES'
AND assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND assoc.from_parent_table_id = cl.charge_line_id
AND cl.charge_amt = 0
AND cl.charge_line_type_id = p_charge_line_type_id --same cost factor
AND NVL(cl.match_id,cl.match_amount_id) IS NOT NULL --only ALC
AND (cl.parent_charge_line_id IS NULL
OR 0 not in
(SELECT
cl1.adjustment_num
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = cl.charge_line_id
CONNECT BY PRIOR cl1.parent_charge_line_id = cl1.charge_line_id));
SELECT
SUM(cl.charge_amt)
INTO
l_sum_chLnAmt
FROM
inl_adj_charge_lines_v cl,
inl_associations a
WHERE
a.ship_header_id = affected_by_elc_lst(k).ship_header_id
AND cl.charge_line_type_id = p_charge_line_type_id --same cost factor
AND NVL(cl.match_id,cl.match_amount_id) IS NOT NULL --only ALC
AND EXISTS (SELECT 1
FROM inl_ship_lines_all sl
WHERE sl.ship_line_id = a.to_parent_table_id
AND sl.ship_header_id = affected_by_elc_lst(k).ship_header_id
AND sl.ship_line_group_id = affected_by_elc_lst(k).to_parent_table_id
AND ROWNUM < 2)
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = cl.charge_line_id
;
SELECT
COUNT(*)
INTO
l_alc_zero
FROM
inl_charge_lines cl,
inl_associations assoc
WHERE
assoc.ship_header_id = affected_by_elc_lst(k).ship_header_id
AND assoc.to_parent_table_name = 'INL_SHIP_LINES'
AND assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND assoc.from_parent_table_id = cl.charge_line_id
AND cl.charge_amt = 0
AND cl.charge_line_type_id = p_charge_line_type_id --same cost factor
AND NVL(cl.match_id,cl.match_amount_id) IS NOT NULL --only ALC
AND EXISTS (SELECT 1
FROM inl_ship_lines_all sl
WHERE sl.ship_line_id = assoc.to_parent_table_id
AND sl.ship_header_id = affected_by_elc_lst(k).ship_header_id
AND sl.ship_line_group_id = affected_by_elc_lst(k).to_parent_table_id
AND ROWNUM < 2)
AND (cl.parent_charge_line_id IS NULL
OR 0 not in
(SELECT
cl1.adjustment_num
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = cl.charge_line_id
CONNECT BY PRIOR cl1.parent_charge_line_id = cl1.charge_line_id));
SELECT
SUM(cl.charge_amt)
INTO
l_sum_chLnAmt
FROM
inl_adj_charge_lines_v cl,
inl_associations a
WHERE
a.ship_header_id = affected_by_elc_lst(k).ship_header_id
AND cl.charge_line_type_id = p_charge_line_type_id --same cost factor
AND NVL(cl.match_id,cl.match_amount_id) IS NOT NULL --only ALC
AND a.to_parent_table_id = affected_by_elc_lst(k).to_parent_table_id
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = cl.charge_line_id
;
SELECT
COUNT(*)
INTO
l_alc_zero
FROM
inl_charge_lines cl,
inl_associations assoc
WHERE
assoc.ship_header_id = affected_by_elc_lst(k).ship_header_id
AND assoc.to_parent_table_name = 'INL_SHIP_LINES'
AND assoc.to_parent_table_id = affected_by_elc_lst(k).to_parent_table_id
AND assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND assoc.from_parent_table_id = cl.charge_line_id
AND cl.charge_amt = 0
AND cl.charge_line_type_id = p_charge_line_type_id --same cost factor
AND NVL(cl.match_id,cl.match_amount_id) IS NOT NULL --only ALC
AND (cl.parent_charge_line_id IS NULL
OR 0 not in
(SELECT
cl1.adjustment_num
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = cl.charge_line_id
CONNECT BY PRIOR cl1.parent_charge_line_id = cl1.charge_line_id));
SELECT
cl1.charge_line_id
INTO
l_last_parentChargeLineID
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = ELC_CLines_lst(j).charge_line_id
CONNECT BY PRIOR cl1.charge_line_id = cl1.parent_charge_line_id
;
l_debug_info := 'Inserting a new ELC ***';
INSERT INTO inl_charge_lines
(
charge_line_id , /* 01 */
charge_line_num , /* 02 */
charge_line_type_id , /* 03 */
landed_cost_flag , /* 04 */
update_allowed , /* 05 */
source_code , /* 06 */
parent_charge_line_id , /* 07 */
adjustment_num , /* 08 */
match_id , /* 09 */
match_amount_id , /* 10 */
charge_amt , /* 11 */
currency_code , /* 12 */
currency_conversion_type , /* 13 */
currency_conversion_date , /* 14 */
currency_conversion_rate , /* 15 */
party_id , /* 16 */
party_site_id , /* 17 */
trx_business_category , /* 18 */
intended_use , /* 19 */
product_fiscal_class , /* 20 */
product_category , /* 21 */
product_type , /* 22 */
user_def_fiscal_class , /* 23 */
tax_classification_code , /* 24 */
assessable_value , /* 25 */
tax_already_calculated_flag, /* 26 */
ship_from_party_id , /* 27 */
ship_from_party_site_id , /* 28 */
ship_to_organization_id , /* 29 */
ship_to_location_id , /* 30 */
bill_from_party_id , /* 31 */
bill_from_party_site_id , /* 32 */
bill_to_organization_id , /* 33 */
bill_to_location_id , /* 34 */
poa_party_id , /* 35 */
poa_party_site_id , /* 36 */
poo_organization_id , /* 37 */
poo_location_id , /* 38 */
created_by , /* 39 */
creation_date , /* 40 */
last_updated_by , /* 41 */
last_update_date , /* 42 */
last_update_login /* 43 */
)
VALUES
(
inl_charge_lines_s.nextval , /* 01 */
ELC_CLines_lst(j).charge_line_num , /* 02 */
p_charge_line_type_id , /* 03 */
ELC_CLines_lst(j).landed_cost_flag , /* 04 */
ELC_CLines_lst(j).update_allowed , /* 05 */
ELC_CLines_lst(j).source_code , /* 06 */
l_last_parentChargeLineID , /* 07 */
p_adjustment_num , /* 08 */
NULL , /* 09 */
NULL , /* 10 */
ELC_CLines_lst(j).charge_amt , /* 11 */
ELC_CLines_lst(j).currency_code , /* 12 */
ELC_CLines_lst(j).currency_conversion_type , /* 13 */
ELC_CLines_lst(j).currency_conversion_date , /* 14 */
ELC_CLines_lst(j).currency_conversion_rate , /* 15 */
ELC_CLines_lst(j).party_id , /* 16 */
ELC_CLines_lst(j).party_site_id , /* 17 */
ELC_CLines_lst(j).trx_business_category , /* 18 */
ELC_CLines_lst(j).intended_use , /* 19 */
ELC_CLines_lst(j).product_fiscal_class , /* 20 */
ELC_CLines_lst(j).product_category , /* 21 */
ELC_CLines_lst(j).product_type , /* 22 */
ELC_CLines_lst(j).user_def_fiscal_class , /* 23 */
ELC_CLines_lst(j).tax_classification_code , /* 24 */
ELC_CLines_lst(j).assessable_value , /* 25 */
'N' , /* 26 */
ELC_CLines_lst(j).ship_from_party_id , /* 27 */
ELC_CLines_lst(j).ship_from_party_site_id , /* 28 */
ELC_CLines_lst(j).ship_to_organization_id , /* 29 */
ELC_CLines_lst(j).ship_to_location_id , /* 30 */
ELC_CLines_lst(j).bill_from_party_id , /* 31 */
ELC_CLines_lst(j).bill_from_party_site_id , /* 32 */
ELC_CLines_lst(j).bill_to_organization_id , /* 33 */
ELC_CLines_lst(j).bill_to_location_id , /* 34 */
ELC_CLines_lst(j).poa_party_id , /* 35 */
ELC_CLines_lst(j).poa_party_site_id , /* 36 */
ELC_CLines_lst(j).poo_organization_id , /* 37 */
ELC_CLines_lst(j).poo_location_id , /* 38 */
L_FND_USER_ID , /* 39 */
sysdate , /* 40 */
L_FND_USER_ID , /* 41 */
sysdate , /* 42 */
l_fnd_login_id --SCM-051 /* 43 */
);
SELECT m.to_parent_table_id , /* 01 */
m.from_parent_table_name , /* 02 */
m.from_parent_table_id , /* 03 */
m.to_parent_table_name , /* 04 */
m.to_parent_table_id , /* 05 */
m.matched_uom_code , /* 06 */
m.matched_amt , /* 07 */
m.replace_estim_qty_flag , /* 08 */
m.existing_match_info_flag , /* 09 */
m.party_id , /* 10 */
m.party_site_id , /* 11 */
m.charge_line_type_id , /* 12 */
m.matched_curr_code , /* 13 */
m.matched_curr_conversion_type, /* 14 */
m.matched_curr_conversion_date, /* 15 */
m.matched_curr_conversion_rate /* 16 */
INTO
l_ship_line_id , /* 01 */
l_from_parent_table_name , /* 02 */
l_from_parent_table_id , /* 03 */
l_to_parent_table_name , /* 04 */
l_to_parent_table_id , /* 05 */
l_matched_uom_code , /* 06 */
l_matched_amt , /* 07 */
l_replace_estim_qty_flag , /* 08 */
l_existing_match_info_flag , /* 09 */
l_party_id , /* 10 */
l_party_site_id , /* 11 */
l_charge_line_type_id , /* 12 */
l_matched_curr_code , /* 13 */
l_matched_curr_conversion_type, /* 14 */
l_matched_curr_conversion_date, /* 15 */
l_matched_curr_conversion_rate /* 16 */
FROM inl_corr_matches_v m
WHERE match_id = p_match_id;
SELECT sl.ship_header_id
INTO l_ship_header_id
FROM inl_ship_lines sl
WHERE sl.ship_line_id = l_ship_line_id;
SELECT p_adjustment_num,
cl.charge_line_id ,
cl.charge_line_num
INTO l_prev_adjustment_num ,
l_ChLn_Assoc.parent_charge_line_id,
l_ChLn_Assoc.charge_line_num
FROM inl_matches m,
inl_adj_charge_lines_v cl
WHERE cl.match_id = m.match_id
AND m.match_id <> p_match_id
AND m.from_parent_table_name = l_from_parent_table_name
AND m.from_parent_table_id = l_from_parent_table_id
AND m.to_parent_table_name = l_to_parent_table_name
AND m.to_parent_table_id = l_to_parent_table_id
AND m.match_id =
(
SELECT MAX (m2.match_id)
FROM inl_matches m2
WHERE m2.from_parent_table_name = m.from_parent_table_name
AND m2.from_parent_table_id = m.from_parent_table_id
AND m2.to_parent_table_name = m.to_parent_table_name
AND m2.to_parent_table_id = m.to_parent_table_id
AND m2.match_id <> p_match_id
)
AND cl.charge_line_num =
(
SELECT MAX (cl1.charge_line_num)
FROM inl_adj_charge_lines_v cl1
WHERE cl1.match_id = m.match_id
) ;
SELECT cl.charge_line_id,
cl.charge_line_num ,
cl.adjustment_num
INTO l_corr_charge_line_id,
l_corr_charge_line_num ,
l_corr_adj_num
FROM inl_adj_charge_lines_v cl
WHERE cl.match_id = p_match_id
AND cl.charge_line_num =
(
SELECT MAX (cl1.charge_line_num)
FROM inl_adj_charge_lines_v cl1
WHERE cl1.match_id = p_match_id
) ;
SELECT
NVL(clt.allocation_basis,'VALUE'),
abv.base_uom_code
INTO
l_ChLn_Assoc.inl_Assoc.allocation_basis,
l_ChLn_Assoc.inl_Assoc.allocation_uom_code
FROM
inl_charge_line_types_vl clt ,
inl_allocation_basis_vl abv
WHERE abv.allocation_basis_code = clt.allocation_basis
AND clt.charge_line_type_id = l_ChLn_Assoc.charge_line_type_id;
SELECT
m.match_id ,
m.ship_header_id ,
m.from_parent_table_name,
m.from_parent_table_id ,
m.to_parent_table_name ,
m.to_parent_table_id
FROM inl_corr_matches_v m
WHERE m.match_amount_id = p_match_amount_id
ORDER BY m.match_id;
SELECT
-- m.ship_header_id , --BUG#8198498
m.from_parent_table_name ,
-- the min is one of the lines that alread exists in prior calculation
m.from_parent_table_id ,
m.to_parent_table_name ,
m.to_parent_table_id ,
(SELECT SUM(INL_LANDEDCOST_PVT.Converted_Amt( NVL(a.matched_amt,0),
a.matched_curr_code,
ma.matched_curr_code,
ma.matched_curr_conversion_type,
ma.matched_curr_conversion_date))
FROM inl_corr_matches_v a
WHERE a.match_amount_id = ma.match_amount_id ) matched_amt,
m.party_id ,
m.party_site_id ,
ma.charge_line_type_id ,
ma.tax_code ,
m.existing_match_info_flag ,
ma.matched_curr_code ,
ma.matched_curr_conversion_type,
ma.matched_curr_conversion_date,
ma.matched_curr_conversion_rate,
clt.allocation_basis ,
abv.base_uom_code
INTO
-- l_ship_header_id ,--BUG#8198498
l_from_parent_table_name ,
l_from_parent_table_id ,
l_to_parent_table_name ,
l_to_parent_table_id ,
l_matched_amt ,
l_party_id ,
l_party_site_id ,
l_charge_line_type_id ,
l_tax_code ,
l_existing_match_info_flag ,
l_matched_curr_code ,
l_matched_curr_conversion_type,
l_matched_curr_conversion_date,
l_matched_curr_conversion_rate,
l_allocation_basis ,
l_allocation_uom_code
FROM inl_match_amounts ma, -- Bug #9179775
inl_corr_matches_v m ,
inl_charge_line_types_vl clt ,
inl_allocation_basis_vl abv
WHERE abv.allocation_basis_code = clt.allocation_basis
AND clt.charge_line_type_id = m.charge_line_type_id
AND m.match_amount_id = ma.match_amount_id
AND ma.match_amount_id = p_match_amount_id
AND m.match_id in (select min(match_id) -- BUG#8411594
FROM inl_corr_matches_v m
WHERE m.match_amount_id = p_match_amount_id);
SELECT max(match_amount_id)
INTO l_prev_match_amount_id
FROM inl_matches m
WHERE m.charge_line_type_id = l_charge_line_type_id
AND nvl(m.tax_code,'-9') = nvl(l_tax_code,'-9')
AND m.match_amount_id <> p_match_amount_id
AND adj_already_generated_flag = 'Y'
AND (m.from_parent_table_name, m.from_parent_table_id)
IN (SELECT m2.from_parent_table_name, m2.from_parent_table_id
FROM inl_matches m2
WHERE m2.match_amount_id = p_match_amount_id)
;
SELECT nvl(count(*),0)
INTO l_count_new_matches
FROM inl_matches m
WHERE m.match_amount_id = p_match_amount_id
AND not exists (SELECT 1
FROM inl_matches m2
WHERE m2.match_amount_id = l_prev_match_amount_id
AND m2.from_parent_table_name = m.from_parent_table_name
AND m2.from_parent_table_id = m.from_parent_table_id
AND m2.to_parent_table_name = m.to_parent_table_name
AND m2.to_parent_table_id = m.to_parent_table_id
)
;
SELECT nvl(count(*),0)
INTO l_count_new_matches
FROM inl_matches m
WHERE m.match_amount_id = l_prev_match_amount_id
AND not exists (SELECT 1
FROM inl_matches m2
WHERE m2.match_amount_id = p_match_amount_id
AND m2.from_parent_table_name = m.from_parent_table_name
AND m2.from_parent_table_id = m.from_parent_table_id
AND m2.to_parent_table_name = m.to_parent_table_name
AND m2.to_parent_table_id = m.to_parent_table_id
)
;
SELECT m.parent_match_id
INTO l_prev_match_id
FROM inl_matches m
WHERE m.match_amount_id = p_match_amount_id
AND m.existing_match_info_flag = 'Y';
SELECT cl.charge_line_id ,
cl.charge_line_num
INTO l_ChLn_Assoc.parent_charge_line_id,
l_ChLn_Assoc.charge_line_num
FROM inl_matches m,
inl_adj_charge_lines_v cl
WHERE cl.match_amount_id = m.match_amount_id
AND m.from_parent_table_name = l_from_parent_table_name
AND m.from_parent_table_id = l_from_parent_table_id
AND m.to_parent_table_name = l_to_parent_table_name
AND m.to_parent_table_id = l_to_parent_table_id
AND m.adj_already_generated_flag = 'Y'
AND m.match_id =
(
SELECT MAX (m2.match_id)
FROM inl_matches m2
WHERE m2.from_parent_table_name = m.from_parent_table_name
AND m2.from_parent_table_id = m.from_parent_table_id
AND m2.to_parent_table_name = m.to_parent_table_name
AND m2.to_parent_table_id = m.to_parent_table_id
AND m2.adj_already_generated_flag = 'Y'
)
AND cl.charge_line_num =
(
SELECT MAX (cl1.charge_line_num)
FROM inl_adj_charge_lines_v cl1
WHERE cl1.match_amount_id = m.match_amount_id
) ;
SELECT
cl.charge_line_id ,
cl.charge_line_num,
cl.adjustment_num
INTO
l_corr_charge_line_id ,
l_corr_charge_line_num,
l_corr_adj_num
FROM inl_adj_charge_lines_v cl
WHERE cl.match_amount_id = p_match_amount_id
AND cl.charge_line_num =
(
SELECT MAX (cl1.charge_line_num)
FROM inl_adj_charge_lines_v cl1
WHERE cl1.match_amount_id = p_match_amount_id
) ;
SELECT MAX(association_id) --Bug#9907327
INTO l_AssocLn.association_id
FROM inl_associations
WHERE from_parent_table_name = 'INL_CHARGE_LINES'
AND from_parent_table_id = l_new_charge_line_id
AND to_parent_table_name = c_matches(i).to_parent_table_name
AND to_parent_table_id = c_matches(i).to_parent_table_id
;
SELECT
m.to_parent_table_name ,
m.to_parent_table_id ,
m.tax_code ,
m.matched_amt ,
m.nrec_tax_amt ,
m.tax_amt_included_flag ,
m.from_parent_table_name ,
m.from_parent_table_id ,
m.matched_curr_code ,
m.matched_curr_conversion_type,
m.matched_curr_conversion_date,
m.matched_curr_conversion_rate,
m.ship_header_id
INTO
l_table_name ,
l_table_id ,
l_tax_code ,
l_matched_amt ,
l_nrec_tax_amt ,
l_tax_amt_included_flag ,
l_from_parent_table_name ,
l_from_parent_table_id ,
l_matched_curr_code ,
l_matched_curr_conversion_type,
l_matched_curr_conversion_date,
l_matched_curr_conversion_rate,
l_ship_header_id
FROM inl_corr_matches_v m
WHERE match_id = p_match_id;
SELECT NVL (MAX (adjustment_num), 0) + 1
INTO l_TxLn_Assoc.adjustment_num
FROM inl_tax_lines tx
WHERE tx.source_parent_table_name = l_from_parent_table_name
AND tx.source_parent_table_id = l_from_parent_table_id
AND tx.ship_header_id = l_ship_header_id;
SELECT tax_line_num, tax_line_id
INTO l_TxLn_Assoc.tax_line_num, l_TxLn_Assoc.parent_tax_line_id
FROM inl_tax_lines tx
WHERE tx.source_parent_table_name = l_from_parent_table_name
AND tx.source_parent_table_id = l_from_parent_table_id
AND tx.ship_header_id = l_ship_header_id
AND adjustment_num =
(
SELECT MAX (tx1.adjustment_num)
FROM inl_tax_lines tx1
WHERE tx1.source_parent_table_name = tx.source_parent_table_name
AND tx1.source_parent_table_id = tx.source_parent_table_id
AND tx1.ship_header_id = l_ship_header_id
) ;
SELECT distinct
m.ship_header_id , --BUG#8198498
m.to_parent_table_name,
m.to_parent_table_id
FROM inl_corr_matches_v m
WHERE m.match_amount_id = p_match_amount_id;
SELECT
(SELECT SUM(INL_LANDEDCOST_PVT.Converted_Amt( NVL(a.matched_amt,0),
a.matched_curr_code,
ma.matched_curr_code,
ma.matched_curr_conversion_type,
ma.matched_curr_conversion_date))
FROM inl_corr_matches_v a
WHERE a.match_amount_id = ma.match_amount_id ) tax_amt,
ma.matched_curr_code currency_code,
ma.matched_curr_conversion_type currency_conversion_type,
ma.matched_curr_conversion_date currency_conversion_date,
ma.matched_curr_conversion_rate currency_conversion_rate,
--
ma.tax_code tax_code,
-- m.ship_header_id ship_header_id , --BUG#8198498
m.from_parent_table_name source_parent_table_name,
m.from_parent_table_id source_parent_table_id,
ma.nrec_tax_amt nrec_tax_amt,
m.tax_amt_included_flag tax_amt_included_flag,
ma.charge_line_type_id charge_line_type_id,
--
clt.allocation_basis allocation_basis,
abv.base_uom_code base_uom_code,
m.existing_match_info_flag existing_match_info_flag
INTO
l_TxLn_Assoc.matched_amt ,
l_TxLn_Assoc.currency_code ,
l_TxLn_Assoc.currency_conversion_type ,
l_TxLn_Assoc.currency_conversion_date ,
l_TxLn_Assoc.currency_conversion_rate ,
--
l_TxLn_Assoc.tax_code ,
-- l_TxLn_Assoc.inl_Assoc.ship_header_id ,--BUG#8198498
l_TxLn_Assoc.source_parent_table_name ,
l_TxLn_Assoc.source_parent_table_id ,
l_TxLn_Assoc.nrec_tax_amt ,
l_TxLn_Assoc.tax_amt_included_flag ,
l_charge_line_type_id ,
--
l_TxLn_Assoc.inl_Assoc.allocation_basis,
l_TxLn_Assoc.inl_Assoc.allocation_uom_code,
l_existing_match_info_flag
FROM
inl_match_amounts ma , -- Bug #9179775
inl_matches m ,
inl_charge_line_types_vl clt,
inl_allocation_basis_vl abv
WHERE
abv.allocation_basis_code = clt.allocation_basis
AND clt.charge_line_type_id = m.charge_line_type_id
AND m.match_amount_id = ma.match_amount_id
AND ma.match_amount_id = p_match_amount_id
AND m.match_id in (select min(match_id) -- BUG#8411594
FROM inl_corr_matches_v m
WHERE m.match_amount_id = p_match_amount_id);
SELECT max(match_amount_id)
INTO l_prev_match_amount_id
FROM inl_matches m
WHERE m.charge_line_type_id = l_charge_line_type_id
AND nvl(m.tax_code,'-9') = nvl(l_TxLn_Assoc.tax_code,'-9')
AND m.match_amount_id <> p_match_amount_id
AND adj_already_generated_flag = 'Y'
AND (m.from_parent_table_name, m.from_parent_table_id)
IN (SELECT m2.from_parent_table_name, m2.from_parent_table_id
FROM inl_matches m2
WHERE m2.match_amount_id = p_match_amount_id)
;
SELECT nvl(count(*),0)
INTO l_count_new_matches
FROM inl_matches m
WHERE m.match_amount_id = p_match_amount_id
AND not exists (SELECT 1
FROM inl_matches m2
WHERE m2.match_amount_id = l_prev_match_amount_id
AND m2.from_parent_table_name = m.from_parent_table_name
AND m2.from_parent_table_id = m.from_parent_table_id
AND m2.to_parent_table_name = m.to_parent_table_name
AND m2.to_parent_table_id = m.to_parent_table_id
)
;
SELECT nvl(count(*),0)
INTO l_count_new_matches
FROM inl_matches m
WHERE m.match_amount_id = l_prev_match_amount_id
AND not exists (SELECT 1
FROM inl_matches m2
WHERE m2.match_amount_id = p_match_amount_id
AND m2.from_parent_table_name = m.from_parent_table_name
AND m2.from_parent_table_id = m.from_parent_table_id
AND m2.to_parent_table_name = m.to_parent_table_name
AND m2.to_parent_table_id = m.to_parent_table_id
)
;
SELECT tl.tax_line_id, tl.tax_line_num
INTO l_TxLn_Assoc.parent_tax_line_id, l_TxLn_Assoc.tax_line_num
FROM inl_matches m,
inl_adj_tax_lines_v tl
WHERE m.from_parent_table_name = l_TxLn_Assoc.source_parent_table_name
AND m.from_parent_table_id = l_TxLn_Assoc.source_parent_table_id
AND m.match_id = (select max(m2.match_id)
FROM inl_matches m2
WHERE m2.from_parent_table_name = l_TxLn_Assoc.source_parent_table_name
AND m2.from_parent_table_id = l_TxLn_Assoc.source_parent_table_id
AND m2.adj_already_generated_flag = 'Y' -- BUG#8411723 => MURALI
)
AND tl.match_amount_id = m.match_amount_id;
SELECT
'B' Ttype, --BUG#8198498
m.match_id ,
m.correction_match_id ,
NULL match_amount_id , --BUG#8198498
NVL(m.adj_group_date,l_initial_sysdate) adj_group_date, --OPM Integration / dependence
m.match_type_code ,
m.charge_line_type_id ,
m.tax_code ,
m.to_parent_table_name,
m.to_parent_table_id ,
sh.organization_id --Bug#14044298
--Bug#14044298 gsb.currency_code func_currency_code --BUG#8468830
FROM
--Bug#14044298 inl_charge_lines cl ,
--Bug#14044298 inl_ship_lines sl ,
inl_ship_headers sh ,
inl_corr_matches_v m
--Bug#14044298 org_organization_definitions ood, --BUG#8468830
--Bug#14044298 gl_sets_of_books gsb --BUG#8468830
WHERE
sh.ship_header_id = p_ship_header_id
--Bug#14044298 cl.charge_line_id (+) = DECODE (m.to_parent_table_name, 'INL_CHARGE_LINES', m.to_parent_table_id, NULL)
--Bug#14044298 AND sl.ship_line_id (+) = DECODE (m.to_parent_table_name, 'INL_SHIP_LINES', m.to_parent_table_id, NULL)
AND sh.ship_header_id = m.ship_header_id
AND m.ship_header_id = p_ship_header_id
AND m.match_amount_id IS NULL
AND m.match_type_code <> 'CORRECTION'
AND NVL (m.adj_already_generated_flag, 'N') = 'N'
--Bug#14044298 AND gsb.set_of_books_id = ood.set_of_books_id
--Bug#14044298 AND ood.organization_id = sh.organization_id
UNION
SELECT DISTINCT
'A' Ttype, --BUG#8198498
NULL AS match_id ,
NULL AS correction_match_id ,
ma.match_amount_id AS match_amount_id ,
ma.adj_group_date AS adj_group_date , --OPM Integration
m.match_type_code AS match_type_code ,
NULL AS charge_line_type_id ,
NULL AS tax_code ,
NULL AS to_parent_table_name,
NULL AS to_parent_table_id ,
sh.organization_id --Bug#14044298
--Bug#14044298 gsb.currency_code AS func_currency_code --BUG#8468830
FROM
inl_ship_headers sh,
inl_match_amounts ma,
inl_corr_matches_v m
--Bug#14044298 org_organization_definitions ood, --BUG#8468830
--Bug#14044298 gl_sets_of_books gsb --BUG#8468830
WHERE
sh.ship_header_id = p_ship_header_id
AND ma.match_amount_id = m.match_amount_id
AND m.ship_header_id = p_ship_header_id
AND m.match_type_code <> 'CORRECTION'
AND NVL (m.adj_already_generated_flag, 'N') = 'N'
--Bug#14044298 AND gsb.set_of_books_id = ood.set_of_books_id
--Bug#14044298 AND ood.organization_id = sh.organization_id
ORDER BY Ttype,
adj_group_date, --OPM Integration
match_id,
match_amount_id; --BUG#8198498, -- BUG#8411723 => MURALI
SELECT -- dependence
DECODE(COUNT(DISTINCT(NVL(mFromMA.adj_group_date,l_initial_sysdate))),1,'Y','N')
INTO
l_match_amt_proc_same_adj_num
FROM
inl_match_amounts ma,
inl_corr_matches_v m,
inl_corr_matches_v mFromMA
WHERE m.ship_header_id = p_ship_header_id
AND m.match_amount_id = ma.match_amount_id
AND m.match_type_code <> 'CORRECTION'
AND NVL (m.adj_already_generated_flag, 'N') = 'N'
AND mFromMA.match_amount_id = ma.match_amount_id
AND NVL (mFromMA.adj_already_generated_flag, 'N') = 'N'
;
SELECT NVL(adjustment_num,0) + 1
INTO l_adjustment_num
FROM inl_ship_headers
WHERE ship_header_id = p_ship_header_id FOR UPDATE;
SELECT NVL(MAX(sh.adjustment_num),0)+1
INTO l_adjustment_num
FROM inl_ship_headers sh
WHERE sh.ship_header_id
IN (select m1.ship_header_id
from inl_corr_matches_v m1
where m1.match_amount_id
IN (select DISTINCT(ma.match_amount_id)
from inl_match_amounts ma, -- Bug #9179775
inl_corr_matches_v m2
WHERE m2.match_amount_id = ma.match_amount_id
AND m2.ship_header_id = p_SHIP_HEADER_ID
AND m2.match_type_code <> 'CORRECTION'
AND NVL(m2.adj_already_generated_flag, 'N') = 'N')
AND m1.match_type_code <> 'CORRECTION')
;
FOR C_CUR_HEAD IN (select m1.ship_header_id
from inl_corr_matches_v m1
where m1.match_amount_id
IN (select DISTINCT(ma.match_amount_id)
from inl_match_amounts ma, -- Bug #9188553
inl_corr_matches_v m2
WHERE m2.match_amount_id = ma.match_amount_id
AND m2.ship_header_id = p_SHIP_HEADER_ID
AND m2.match_type_code <> 'CORRECTION'
AND NVL(m2.adj_already_generated_flag, 'N') = 'N')
AND m1.match_type_code <> 'CORRECTION') LOOP
*/
FOR C_CUR_HEAD IN (select m1.ship_header_id
from inl_matches m1
where m1.match_amount_id
IN (select DISTINCT(ma.match_amount_id)
from inl_match_amounts ma, -- Bug #9188553
inl_corr_matches_v m2
WHERE m2.match_amount_id = ma.match_amount_id
AND m2.ship_header_id = p_SHIP_HEADER_ID
AND m2.match_type_code <> 'CORRECTION'
AND NVL(m2.adj_already_generated_flag, 'N') = 'N')) LOOP
--Bug#14044298 END
l_match_ship_header_id_ind:=l_match_ship_header_id_ind+1;
SELECT NVL(MAX(sh.adjustment_num),0)
INTO l_adjustment_num_tmp
FROM inl_ship_headers sh
WHERE sh.ship_header_id
IN (SELECT m1.ship_header_id
FROM inl_corr_matches_v m1
WHERE m1.match_type_code <> 'CORRECTION'
AND m1.match_amount_id = r_match(i).match_amount_id)
;
SELECT
DISTINCT(m.adj_group_date)
INTO
l_adj_group_date
FROM
inl_corr_matches_v m
WHERE m.match_amount_id = r_match(i).match_amount_id
AND m.match_type_code <> 'CORRECTION'
AND NVL (m.adj_already_generated_flag, 'N') = 'N';
FOR C_CUR_HEAD IN (select m1.ship_header_id
from inl_corr_matches_v m1
where m1.match_amount_id
IN (SELECT m2.match_amount_id
FROM inl_corr_matches_v m2
WHERE m2.match_type_code <> 'CORRECTION'
AND m2.match_amount_id = r_match(i).match_amount_id)) LOOP
l_match_ship_header_id_ind:=l_match_ship_header_id_ind+1;
SELECT gsb.currency_code
INTO l_func_currency_code
FROM
org_organization_definitions ood,
gl_sets_of_books gsb
WHERE
gsb.set_of_books_id = ood.set_of_books_id
AND ood.organization_id = r_match(i).organization_id;
UPDATE inl_matches m
SET m.adj_already_generated_flag = 'Y' ,
m.adjustment_num = l_adjustment_num, --lcm/opm integration
m.last_updated_by = L_FND_USER_ID,
m.last_update_date = SYSDATE
WHERE m.match_id = NVL (r_match(i).correction_match_id, r_match(i).match_id)
OR NVL(m.match_amount_id,-1) = NVL(r_match(i).match_amount_id,-2);
l_debug_info := 'Update Shipment Header Adjustment Number';
UPDATE inl_ship_headers
SET adjustment_num = match_ship_header_id_lst(i).adjustment_num
WHERE ship_header_id = match_ship_header_id_lst(i).ship_header_id;
UPDATE inl_ship_headers
SET adjustment_num = l_adjustment_num
WHERE ship_header_id = p_ship_header_id;
SELECT msi.primary_uom_code
INTO x_1ary_uom_code
FROM mtl_system_items_vl msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id;
SELECT msi.secondary_uom_code
INTO x_2ary_uom_code
FROM mtl_system_items_vl msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id;
SELECT msi.primary_uom_code,
msi.secondary_uom_code
INTO x_1ary_uom_code,
x_2ary_uom_code
FROM mtl_system_items_vl msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id;
SELECT sl.ship_line_id, sl.new_txn_unit_price,
sl.primary_qty, sl.secondary_qty, sl.txn_qty,
sl.primary_uom_code, sl.secondary_uom_code
FROM inl_ship_headers_all sh,
inl_adj_ship_lines_v sl
WHERE(sl.new_txn_unit_price IS NOT NULL
OR sl.new_currency_conversion_type IS NOT NULL
OR sl.new_currency_conversion_date IS NOT NULL
OR sl.new_currency_conversion_rate IS NOT NULL)
AND sl.ship_header_id = sh.ship_header_id
AND sh.pending_update_flag = 'Y'
AND sh.ship_header_id = p_ship_header_id;
SELECT DISTINCT charge_line_id
FROM inl_ship_headers_all sh,
inl_adj_charge_lines_v cl,
inl_adj_associations_v a
WHERE sh.ship_header_id = a.ship_header_id
AND a.from_parent_table_id = cl.charge_line_id
AND sh.pending_update_flag = 'Y'
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.ship_header_id = p_ship_header_id
AND (cl.adjustment_type_flag IS NULL
OR cl.adjustment_type_flag <> 'Z')
ORDER BY charge_line_id;
SELECT (ABS(adjustment_num) +1) *-1
INTO l_adjustment_num
FROM inl_ship_headers_all sh
WHERE sh.ship_header_id = p_ship_header_id FOR UPDATE NOWAIT;
INSERT INTO inl_ship_lines(
ship_header_id, -- 01
ship_line_group_id, -- 02
ship_line_id, -- 03
ship_line_num, -- 04
ship_line_type_id, -- 05
ship_line_src_type_code, -- 06
ship_line_source_id, -- 07
parent_ship_line_id, -- 08
adjustment_num, -- 09
match_id, -- 10
currency_code, -- 11
currency_conversion_type, -- 12
currency_conversion_date, -- 13
currency_conversion_rate, -- 14
inventory_item_id, -- 15
txn_qty, -- 16
txn_uom_code, -- 17
txn_unit_price, -- 18
primary_qty, -- 19
primary_uom_code, -- 20
primary_unit_price, -- 21
secondary_qty, -- 22
secondary_uom_code, -- 23
secondary_unit_price, -- 24
landed_cost_flag, -- 25
allocation_enabled_flag, -- 26
trx_business_category, -- 27
intended_use, -- 28
product_fiscal_class, -- 29
product_category, -- 30
product_type, -- 31
user_def_fiscal_class, -- 32
tax_classification_code, -- 33
assessable_value, -- 34
tax_already_calculated_flag, -- 35
ship_from_party_id, -- 36
ship_from_party_site_id, -- 37
ship_to_organization_id, -- 38
ship_to_location_id, -- 39
bill_from_party_id, -- 40
bill_from_party_site_id, -- 41
bill_to_organization_id, -- 42
bill_to_location_id, -- 43
poa_party_id, -- 44
poa_party_site_id, -- 45
poo_organization_id, -- 46
poo_location_id, -- 47
org_id,-- 48
ship_line_int_id, -- 49
interface_source_table, -- 50
interface_source_line_id, -- 51
created_by, -- 52
creation_date, -- 53
last_updated_by, -- 54
last_update_date, -- 55
last_update_login, -- 56
program_id, -- 57
program_update_date, -- 58
program_application_id, -- 59
request_id, -- 60
attribute_category, -- 61
attribute1, -- 62
attribute2, -- 63
attribute3, -- 64
attribute4, -- 65
attribute5, -- 66
attribute6, -- 67
attribute7, -- 68
attribute8, -- 69
attribute9, -- 70
attribute10, -- 71
attribute11, -- 72
attribute12, -- 73
attribute13, -- 74
attribute14, -- 75
attribute15, -- 76
nrq_zero_exception_flag, -- 77
new_txn_unit_price, -- 78
new_currency_conversion_type, -- 79
new_currency_conversion_date, -- 80
new_currency_conversion_rate -- 81
)
(SELECT p_ship_header_id, -- 01
sl.ship_line_group_id, -- 02
inl_ship_lines_all_s.nextval, -- 03
sl.ship_line_num, -- 04
sl.ship_line_type_id, -- 05
sl.ship_line_src_type_code, -- 06
sl.ship_line_source_id, -- 07
NVL(parent_ship_line_id, sl.ship_line_id), -- 08
l_adjustment_num, -- 09
sl.match_id, -- 10
sl.currency_code, -- 11
NVL(sl.new_currency_conversion_type, sl.currency_conversion_type), -- 12
NVL(sl.new_currency_conversion_date, sl.currency_conversion_date), -- 13
NVL(sl.new_currency_conversion_rate, sl.currency_conversion_rate), -- 14
sl.inventory_item_id, -- 15
sl.txn_qty, -- 16
sl.txn_uom_code, -- 17
NVL(sl.new_txn_unit_price, sl.txn_unit_price), -- 18
sl.primary_qty, -- 19
sl.primary_uom_code, -- 20
NVL(l_primary_unit_price,sl.primary_unit_price), -- 21
sl.secondary_qty, -- 22
sl.secondary_uom_code, -- 23
NVL(l_secondary_unit_price,sl.secondary_unit_price), -- 24
sl.landed_cost_flag, -- 25
sl.allocation_enabled_flag, -- 26
sl.trx_business_category, -- 27
sl.intended_use, -- 28
sl.product_fiscal_class, -- 29
sl.product_category, -- 30
sl.product_type, -- 31
sl.user_def_fiscal_class, -- 32
sl.tax_classification_code, -- 33
sl.assessable_value, -- 34
sl.tax_already_calculated_flag, -- 35
sl.ship_from_party_id, -- 36
sl.ship_from_party_site_id, -- 37
sl.ship_to_organization_id, -- 38
sl.ship_to_location_id, -- 39
sl.bill_from_party_id, -- 40
sl.bill_from_party_site_id, -- 41
sl.bill_to_organization_id, -- 42
sl.bill_to_location_id, -- 43
sl.poa_party_id, -- 44
sl.poa_party_site_id, -- 45
sl.poo_organization_id, -- 46
sl.poo_location_id, -- 47
sl.org_id, -- 48
sl.ship_line_int_id, -- 49
sl.interface_source_table, -- 50
sl.interface_source_line_id, -- 51
l_fnd_user_id, -- 52
SYSDATE, -- 53
l_fnd_user_id, -- 54
SYSDATE, -- 55
l_fnd_login_id, -- 56
sl.program_id, -- 57
sl.program_update_date, -- 58
sl.program_application_id, -- 59
sl.request_id, -- 60
sl.attribute_category, -- 61
sl.attribute1, -- 62
sl.attribute2, -- 63
sl.attribute3, -- 64
sl.attribute4, -- 65
sl.attribute5, -- 66
sl.attribute6, -- 67
sl.attribute7, -- 68
sl.attribute8, -- 69
sl.attribute9, -- 70
sl.attribute10, -- 71
sl.attribute11, -- 72
sl.attribute12, -- 73
sl.attribute13, -- 74
sl.attribute14, -- 75
sl.attribute15, -- 76
sl.nrq_zero_exception_flag, -- 77
NULL, -- 78
NULL, -- 80
NULL, -- 81
NULL
FROM inl_adj_ship_lines_v sl
WHERE sl.ship_line_id = l_elc_adj_shiplines(i).ship_line_id);
SELECT COUNT(1)
INTO l_count_assoc_changed
FROM inl_adj_associations_v a
WHERE a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = l_elc_adj_chlines(i).charge_line_id
AND a.adjustment_type_flag IS NOT NULL
AND a.ship_header_id = p_ship_header_id;
SELECT COUNT(1)
INTO l_exist_associations
FROM inl_adj_associations_v a
WHERE a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = l_elc_adj_chlines(i).charge_line_id
AND (a.adjustment_type_flag IS NULL
OR a.adjustment_type_flag = 'A')
AND a.ship_header_id = p_ship_header_id;
SELECT inl_charge_lines_s.nextval
INTO l_charge_line_id
FROM DUAL;
SELECT NVL (MAX (cl.charge_line_num), 0) + 1
INTO l_charge_line_num
FROM inl_charge_lines cl
WHERE NVL (cl.parent_charge_line_id, cl.charge_line_id) IN(
SELECT assoc.from_parent_table_id
FROM inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND assoc.ship_header_id = p_ship_header_id);
INSERT INTO inl_charge_lines(charge_line_id, -- 01
charge_line_num, -- 02
charge_line_type_id, -- 03
landed_cost_flag, -- 04
update_allowed, -- 05
source_code, -- 06
parent_charge_line_id, -- 07
adjustment_num, -- 08
match_id, -- 09
match_amount_id, -- 10
charge_amt, -- 11
currency_code, -- 12
currency_conversion_type, -- 13
currency_conversion_date, -- 14
currency_conversion_rate, -- 15
party_id, -- 16
party_site_id, -- 17
trx_business_category, -- 18
intended_use, -- 19
product_fiscal_class, -- 20
product_category, -- 21
product_type, -- 22
user_def_fiscal_class, -- 23
tax_classification_code, -- 24
assessable_value, -- 25
tax_already_calculated_flag, -- 26
ship_from_party_id, -- 27
ship_from_party_site_id, -- 278
ship_to_organization_id, -- 29
ship_to_location_id, -- 30
bill_from_party_id, -- 31
bill_from_party_site_id, -- 32
bill_to_organization_id, -- 33
bill_to_location_id, -- 34
poa_party_id, -- 35
poa_party_site_id, -- 36
poo_organization_id, -- 37
poo_location_id, -- 38
created_by, -- 39
creation_date, -- 40
last_updated_by, -- 41
last_update_date, -- 42
last_update_login, -- 43
new_charge_amt, -- 44
new_currency_conversion_type, -- 45
new_currency_conversion_date, -- 46
new_currency_conversion_rate -- 47
)
(SELECT l_charge_line_id, -- 01
l_charge_line_num, -- 02
cl.charge_line_type_id, -- 03
cl.landed_cost_flag, -- 04
cl.update_allowed, -- 05
cl.source_code, -- 06
NULL, -- 07
l_adjustment_num, -- 08
cl.match_id, -- 09
cl.match_amount_id, -- 10
NVL(cl.new_charge_amt, cl.charge_amt), -- 11
cl.currency_code, -- 12
NVL(cl.new_currency_conversion_type, cl.currency_conversion_type), -- 13
NVL(cl.new_currency_conversion_date, cl.currency_conversion_date), -- 14
NVL(cl.new_currency_conversion_rate, cl.currency_conversion_rate), -- 15
cl.party_id, -- 16
cl.party_site_id, -- 17
cl.trx_business_category, -- 18
cl.intended_use, -- 19
cl.product_fiscal_class, -- 20
cl.product_category,-- 21
cl.product_type,-- 22
cl.user_def_fiscal_class,-- 23
cl.tax_classification_code,-- 24
cl.assessable_value,-- 25
cl.tax_already_calculated_flag,-- 26
cl.ship_from_party_id,-- 27
cl.ship_from_party_site_id,-- 28
cl.ship_to_organization_id,-- 29
cl.ship_to_location_id,-- 30
cl.bill_from_party_id, -- 31
cl.bill_from_party_site_id, -- 32
cl.bill_to_organization_id, -- 33
cl.bill_to_location_id, -- 34
cl.poa_party_id, -- 35
cl.poa_party_site_id, -- 36
cl.poo_organization_id, -- 37
cl.poo_location_id, -- 38
l_fnd_user_id, -- 39
SYSDATE, -- 40
l_fnd_user_id, -- 41
SYSDATE, -- 42
l_fnd_login_id, -- 43
NULL, -- 44
NULL, -- 45
NULL, -- 46
NULL -- 47
FROM inl_adj_charge_lines_v cl
WHERE cl.charge_line_id = l_elc_adj_chlines(i).charge_line_id);
INSERT INTO inl_associations(association_id, -- 01
ship_header_id, -- 02
from_parent_table_name, -- 03
from_parent_table_id, -- 04
to_parent_table_name, -- 05
to_parent_table_id, -- 06
allocation_basis, -- 07
allocation_uom_code, -- 08
created_by, -- 09
creation_date, -- 10
last_updated_by, -- 11
last_update_date, -- 12
last_update_login, -- 13
adjustment_type_flag) -- 14
(SELECT INL_ASSOCIATIONS_S.NEXTVAL, -- 01
a.ship_header_id, -- 02
a.from_parent_table_name, -- 03
l_charge_line_id, -- 04
a.to_parent_table_name, -- 05
a.to_parent_table_id, -- 06
a.allocation_basis, -- 07
a.allocation_uom_code, -- 08
l_fnd_user_id, -- 09
SYSDATE, -- 10
l_fnd_user_id, -- 11
SYSDATE, -- 12
L_FND_LOGIN_ID, -- 13
NULL -- 14
FROM inl_adj_associations_v 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 = l_elc_adj_chlines(i).charge_line_id
AND (a.adjustment_type_flag IS NULL
OR a.adjustment_type_flag = 'A'));
INSERT INTO inl_charge_lines(charge_line_id, -- 01
charge_line_num, -- 02
charge_line_type_id, -- 03
landed_cost_flag, -- 04
update_allowed, -- 05
source_code, -- 06
parent_charge_line_id, -- 07
adjustment_num, -- 08
match_id, -- 09
match_amount_id, -- 10
charge_amt, -- 11
currency_code, -- 12
currency_conversion_type, -- 13
currency_conversion_date, -- 14
currency_conversion_rate, -- 15
party_id, -- 16
party_site_id, -- 17
trx_business_category, -- 18
intended_use, -- 19
product_fiscal_class, -- 20
product_category,-- 21
product_type,-- 22
user_def_fiscal_class, -- 23
tax_classification_code, -- 24
assessable_value,-- 25
tax_already_calculated_flag, -- 26
ship_from_party_id, -- 27
ship_from_party_site_id, -- 28
ship_to_organization_id, -- 29
ship_to_location_id, -- 30
bill_from_party_id, -- 31
bill_from_party_site_id, -- 32
bill_to_organization_id, -- 33
bill_to_location_id, -- 34
poa_party_id, -- 35
poa_party_site_id, -- 36
poo_organization_id, -- 37
poo_location_id, -- 38
created_by, -- 39
creation_date, -- 40
last_updated_by, -- 41
last_update_date, -- 42
last_update_login, -- 43
new_charge_amt, -- 44
new_currency_conversion_type, -- 45
new_currency_conversion_date, -- 46
new_currency_conversion_rate, -- 47
adjustment_type_flag) -- 48
(SELECT INL_CHARGE_LINES_S.NEXTVAL, -- 01
cl.charge_line_num, -- 02
cl.charge_line_type_id, -- 03
cl.landed_cost_flag, -- 04
cl.update_allowed, -- 05
cl.source_code, -- 06
l_elc_adj_chlines(i).charge_line_id, -- 07
l_adjustment_num, -- 08
cl.match_id, -- 09
cl.match_amount_id, -- 10
0, -- 11
cl.currency_code, -- 12
cl.currency_conversion_type, -- 13
cl.currency_conversion_date, -- 14
cl.currency_conversion_rate, -- 15
cl.party_id, -- 16
cl.party_site_id, -- 17
cl.trx_business_category, -- 18
cl.intended_use, -- 19
cl.product_fiscal_class, -- 20
cl.product_category, -- 21
cl.product_type, -- 22
cl.user_def_fiscal_class, -- 23
cl.tax_classification_code, -- 24
cl.assessable_value, -- 25
cl.tax_already_calculated_flag, -- 26
cl.ship_from_party_id, -- 27
cl.ship_from_party_site_id, -- 28
cl.ship_to_organization_id, -- 29
cl.ship_to_location_id, -- 30
cl.bill_from_party_id, -- 31
cl.bill_from_party_site_id, -- 32
cl.bill_to_organization_id, -- 33
cl.bill_to_location_id, -- 34
cl.poa_party_id, -- 35
cl.poa_party_site_id, -- 36
cl.poo_organization_id, -- 37
cl.poo_location_id, -- 38
l_fnd_user_id, -- 39
SYSDATE, -- 40
l_fnd_user_id, -- 41
SYSDATE, -- 42
l_fnd_login_id, -- 43
NULL, -- 44
NULL, -- 45
NULL, -- 46
NULL, -- 47
'Z' -- 48
FROM inl_adj_charge_lines_v cl
WHERE cl.charge_line_id = l_elc_adj_chlines(i).charge_line_id);
SELECT COUNT(1)
INTO l_count_charge_changed
FROM inl_adj_charge_lines_v c
WHERE (c.new_charge_amt IS NOT NULL
OR c.new_currency_conversion_type IS NOT NULL
OR c.new_currency_conversion_date IS NOT NULL
OR c.new_currency_conversion_rate IS NOT NULL)
AND charge_line_id = l_elc_adj_chlines(i).charge_line_id;
INSERT INTO inl_charge_lines(charge_line_id, -- 01
charge_line_num, -- 02
charge_line_type_id, -- 03
landed_cost_flag, -- 04
update_allowed, -- 05
source_code, -- 06
parent_charge_line_id, -- 07
adjustment_num, -- 08
match_id, -- 09
match_amount_id, -- 10
charge_amt, -- 11
currency_code, -- 12
currency_conversion_type, -- 13
currency_conversion_date, -- 14
currency_conversion_rate, -- 15
party_id, -- 16
party_site_id, -- 17
trx_business_category, -- 18
intended_use, -- 19
product_fiscal_class, -- 20
product_category, -- 21
product_type, -- 22
user_def_fiscal_class, -- 23
tax_classification_code, -- 24
assessable_value, -- 25
tax_already_calculated_flag, -- 26
ship_from_party_id, -- 27
ship_from_party_site_id, -- 28
ship_to_organization_id, -- 29
ship_to_location_id, -- 30
bill_from_party_id, -- 31
bill_from_party_site_id, -- 32
bill_to_organization_id, -- 33
bill_to_location_id, -- 34
poa_party_id, -- 35
poa_party_site_id, -- 36
poo_organization_id, -- 37
poo_location_id, -- 38
created_by, -- 39
creation_date, -- 40
last_updated_by, -- 41
last_update_date, -- 42
last_update_login, -- 43
new_charge_amt, -- 44
new_currency_conversion_type, -- 45
new_currency_conversion_date, -- 46
new_currency_conversion_rate -- 47
)
(SELECT INL_CHARGE_LINES_S.NEXTVAL, -- 01
cl.charge_line_num, -- 02
cl.charge_line_type_id, -- 03
cl.landed_cost_flag, -- 04
cl.update_allowed, -- 05
cl.source_code, -- 06
cl.charge_line_id, -- 07
l_adjustment_num, -- 08
cl.match_id, -- 09
cl.match_amount_id, -- 10
NVL(cl.new_charge_amt, cl.charge_amt), -- 11
cl.currency_code, -- 12
NVL(cl.new_currency_conversion_type,cl.currency_conversion_type), -- 13
NVL(cl.new_currency_conversion_date,cl.currency_conversion_date), -- 14
NVL(cl.new_currency_conversion_rate,cl.currency_conversion_rate), -- 15
cl.party_id, -- 16
cl.party_site_id, -- 17
cl.trx_business_category, -- 18
cl.intended_use, -- 19
cl.product_fiscal_class, -- 20
cl.product_category, -- 21
cl.product_type, -- 22
cl.user_def_fiscal_class, -- 23
cl.tax_classification_code, -- 24
cl.assessable_value, -- 25
cl.tax_already_calculated_flag, -- 26
cl.ship_from_party_id, -- 27
cl.ship_from_party_site_id, -- 28
cl.ship_to_organization_id, -- 29
cl.ship_to_location_id, -- 30
cl.bill_from_party_id, -- 31
cl.bill_from_party_site_id, -- 32
cl.bill_to_organization_id, -- 33
cl.bill_to_location_id, -- 34
cl.poa_party_id, -- 35
cl.poa_party_site_id, -- 36
cl.poo_organization_id, -- 37
cl.poo_location_id, -- 38
l_fnd_user_id, -- 39
SYSDATE, -- 40
l_fnd_user_id, -- 41
SYSDATE, -- 42
l_fnd_login_id, -- 43
NULL, -- 44
NULL, -- 45
NULL, -- 46
NULL -- 47
FROM inl_adj_charge_lines_v cl
WHERE cl.charge_line_id = l_elc_adj_chlines(i).charge_line_id);
l_debug_info := 'Set pending_update_flag to N for Shipment';
UPDATE inl_ship_headers_all sh
SET sh.adjustment_num = ABS(l_adjustment_num),
sh.pending_update_flag = 'N',
sh.last_update_login = l_fnd_login_id,
sh.last_update_date = SYSDATE,
sh.last_updated_by = l_fnd_user_id
WHERE sh.ship_header_id = p_ship_header_id;
l_pending_update_flag VARCHAR2(1) ; -- SCM-051
l_debug_info := 'Get the Pending Match Flag, Pending Update Flag and Organization Id';
SELECT pending_matching_flag,
pending_update_flag, -- SCM-051
organization_id,
NVL(rcv_enabled_flag,'Y') rcv_enabled_flag, -- dependence,
ship_num -- SCM-051
INTO l_pending_matching_flag,
l_pending_update_flag, -- SCM-051
l_organization_id,
l_rcv_enabled_flag,
l_ship_num
FROM inl_ship_headers
WHERE ship_header_id = p_ship_header_id;
p_var_name => 'l_pending_update_flag',
p_var_value => l_pending_update_flag) ;
IF NVL(l_pending_update_flag, 'N') = 'Y' AND p_caller = 'U' THEN
SELECT COUNT(1)
INTO l_rcv_running
FROM rcv_transactions_interface rti,
inl_ship_lines_all sl
WHERE rti.transaction_type = 'RECEIVE'
AND rti.processing_status_code = 'RUNNING'
AND rti.lcm_shipment_line_id = sl.ship_line_id
AND sl.ship_header_id = p_ship_header_id;
IF NVL(l_pending_update_flag, 'N') = 'Y' AND p_caller = 'C' THEN
l_debug_info := 'Shipment with ELC Pending';
FND_MESSAGE.SET_NAME('INL','INL_ERR_CHK_ELC_UPDATE');
l_debug_info := 'Update PendingMatchingFlag';
INL_SHIPMENT_PVT.Update_PendingMatchingFlag( --BUG#8198498
p_ship_header_id => p_ship_header_id,
p_pending_matching_flag => 'N',
x_return_status => l_return_status
);
NVL(l_pending_update_flag, 'N') = 'N' THEN -- SCM-051
l_debug_info := 'Run INL_TAX_PVT.Generate_Taxes';
NVL(l_pending_update_flag, 'N') = 'N' THEN -- SCM-051
l_debug_info := 'Run INL_SHIPMENT_PVT.Validate_Shipment';
SELECT MAX(allocation_id)
INTO l_max_allocation_id
FROM inl_allocations;
p_pending_elc_flag => l_pending_update_flag, -- SCM-051
p_organization_id => l_organization_id,
p_max_allocation_id => l_max_allocation_id, --Bug#10032820
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
SELECT ship_header_id, org_id --Bug#10381495
FROM inl_ship_headers_all --Bug#10381495
WHERE ship_status_code = 'COMPLETED'
AND pending_matching_flag = 'Y'
AND (p_organization_id IS NULL
OR organization_id = p_organization_id);
SELECT org_id
INTO l_org_id
FROM inl_ship_headers_all
WHERE ship_header_id = p_ship_header_id;
PROCEDURE Update_PendingMatchingFlag(
p_ship_header_id IN NUMBER,
p_pending_matching_flag IN VARCHAR,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_program_name CONSTANT VARCHAR2 (100) := 'Update_PendingMatchingFlag';
UPDATE inl_ship_headers_all sh --Bug#10381495
SET sH.pending_matching_flag = p_pending_matching_flag ,
-- created_by = L_FND_USER_ID ,
-- creation_date = SYSDATE ,
sH.last_updated_by = L_FND_USER_ID ,
sH.last_update_date = SYSDATE ,
sH.last_update_login = L_FND_LOGIN_ID ,
sH.request_id = L_FND_CONC_REQUEST_ID ,
sH.program_id = L_FND_CONC_PROGRAM_ID ,
sH.program_update_date = SYSDATE ,
sH.program_application_id = L_FND_PROG_APPL_ID
WHERE sH.ship_header_id = p_ship_header_id;
END Update_PendingMatchingFlag;
PROCEDURE Delete_ChargeAssoc(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := L_FND_FALSE,
p_commit IN VARCHAR2 := L_FND_FALSE,
p_ship_header_id IN NUMBER,
p_charge_line_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_ChargeAssoc';
SELECT a.ship_header_id ,
sh.ship_status_code ship_status,
sh.pending_matching_flag
FROM inl_associations a,
inl_ship_headers sh
WHERE sh.ship_header_id = a.ship_header_id
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = p_charge_line_id
AND a.ship_header_id <> p_ship_header_id;
SAVEPOINT Delete_ChargeAssoc_PVT;
Update_PendingMatchingFlag (r_affected_ship.ship_header_id, 'Y', l_return_status) ;
ROLLBACK TO Delete_ChargeAssoc_PVT;
ROLLBACK TO Delete_ChargeAssoc_PVT;
ROLLBACK TO Delete_ChargeAssoc_PVT;
END Delete_ChargeAssoc;
SELECT SUM(NVL(a.nrec_tax_amt,0) * NVL(a.matched_curr_conversion_rate,1)) -- Bug #10102991 --Bug#1405859
INTO l_nrec_tax_amt
FROM inl_matches a
WHERE a.ship_header_id = p_ship_header_id
AND a.to_parent_table_name = 'INL_SHIP_LINES'
AND a.to_parent_table_id = (SELECT NVL(MAX(b.parent_ship_line_id), p_ship_line_id)
FROM inl_ship_lines_all b
WHERE b.ship_line_id = p_ship_line_id)
AND a.match_type_code = 'TAX'
AND (p_charge_line_type_id IS NULL OR a.charge_line_type_id = p_charge_line_type_id)
AND (p_tax_code IS NULL OR a.tax_code = p_tax_code)
-- Bug 8879575
AND a.adjustment_num = (SELECT NVL(MAX(c.adjustment_num), a.adjustment_num)
FROM inl_matches c
WHERE c.ship_header_id = a.ship_header_id
AND c.from_parent_table_name = a.from_parent_table_name
AND c.from_parent_table_id = a.from_parent_table_id
AND c.to_parent_table_name = a.to_parent_table_name
AND c.to_parent_table_id = a.to_parent_table_id
AND c.match_type_code = a.match_type_code
AND c.tax_code = a.tax_code
AND c.existing_match_info_flag = 'Y')
AND NOT EXISTS (SELECT 1
FROM inl_matches d
WHERE d.ship_header_id = a.ship_header_id
AND d.from_parent_table_name = a.from_parent_table_name
AND d.from_parent_table_id = a.from_parent_table_id
AND d.to_parent_table_name = a.to_parent_table_name
AND d.to_parent_table_id = a.to_parent_table_id
AND d.existing_match_info_flag = 'Y'
AND d.parent_match_id = a.match_id)
-- Bug 8879575
--Bug#14058596 GROUP BY a.matched_curr_code, a.matched_curr_conversion_rate
;
SELECT SUM(NVL(a.matched_amt,0) * NVL(a.matched_curr_conversion_rate,1)) -- Bug #10102991--Bug#14058596
INTO l_matched_charge_amt
FROM inl_matches a
WHERE a.ship_header_id = p_ship_header_id
AND a.to_parent_table_name = 'INL_SHIP_LINES'
AND a.to_parent_table_id = (select NVL(MAX(b.parent_ship_line_id), p_ship_line_id)
from inl_ship_lines_all b
where b.ship_line_id = p_ship_line_id)
AND a.match_type_code = 'CHARGE'
AND a.charge_line_type_id = p_charge_line_type_id
-- Bug 8879575
AND a.adjustment_num = (select NVL(MAX(c.adjustment_num), a.adjustment_num)
from inl_matches c
where c.ship_header_id = a.ship_header_id
and c.from_parent_table_name = a.from_parent_table_name
and c.from_parent_table_id = a.from_parent_table_id
and c.to_parent_table_name = a.to_parent_table_name
and c.to_parent_table_id = a.to_parent_table_id
and c.match_type_code = a.match_type_code
and c.charge_line_type_id = a.charge_line_type_id
and c.existing_match_info_flag = 'Y')
AND NOT EXISTS (select 1
from inl_matches d
where d.ship_header_id = a.ship_header_id
and d.from_parent_table_name = a.from_parent_table_name
and d.from_parent_table_id = a.from_parent_table_id
and d.to_parent_table_name = a.to_parent_table_name
and d.to_parent_table_id = a.to_parent_table_id
and d.existing_match_info_flag = 'Y'
and d.parent_match_id = a.match_id)
-- Bug 8879575
-- GROUP BY a.matched_curr_code, a.matched_curr_conversion_rate SCM-051
;
SELECT SUM(NVL(a.matched_amt,0) * NVL(a.matched_curr_conversion_rate,1)) -- Bug #10102991--Bug#14058596
INTO l_matched_item_amt
FROM inl_matches a
WHERE a.ship_header_id = p_ship_header_id
AND a.to_parent_table_name = 'INL_SHIP_LINES'
AND a.to_parent_table_id = (SELECT NVL(MAX(b.parent_ship_line_id), p_ship_line_id)
FROM inl_ship_lines_all b
WHERE b.ship_line_id = p_ship_line_id)
AND ((p_summarized_matched_amt = 'Y' AND a.match_type_code <> 'TAX')
OR (p_summarized_matched_amt = 'N'
AND a.match_type_code = 'ITEM'))
-- Bug 8879575
AND a.adjustment_num = (SELECT NVL(MAX(c.adjustment_num), a.adjustment_num)
FROM inl_matches c
WHERE c.ship_header_id = a.ship_header_id
AND c.from_parent_table_name = a.from_parent_table_name
AND c.from_parent_table_id = a.from_parent_table_id
AND c.to_parent_table_name = a.to_parent_table_name
AND c.to_parent_table_id = a.to_parent_table_id
AND c.match_type_code = a.match_type_code
AND c.existing_match_info_flag = 'Y')
AND NOT EXISTS (SELECT 1
FROM inl_matches d
WHERE d.ship_header_id = a.ship_header_id
AND d.from_parent_table_name = a.from_parent_table_name
AND d.from_parent_table_id = a.from_parent_table_id
AND d.to_parent_table_name = a.to_parent_table_name
AND d.to_parent_table_id = a.to_parent_table_id
AND d.existing_match_info_flag = 'Y'
AND d.parent_match_id = a.match_id)
--GROUP BY matched_curr_code, a.matched_curr_conversion_rate
;
SELECT gsb.currency_code
INTO l_func_currency_code
FROM inl_ship_headers_all ish,
gl_sets_of_books gsb,
org_organization_definitions ood
WHERE ish.organization_id = ood.organization_id
AND gsb.set_of_books_id = ood.set_of_books_id
AND ish.ship_header_id = p_ship_header_id;
FUNCTION Get_LastUpdateDateForShip(p_ship_header_id IN NUMBER) RETURN DATE
IS
l_header_last_update_date DATE;
l_group_last_update_date DATE;
l_line_last_update_date DATE;
l_charge_last_update_date DATE;
l_tax_last_update_date DATE;
l_assoc_last_update_date DATE;
ship_last_update_date DATE;
l_program_name CONSTANT VARCHAR2(30) := 'Get_LastUpdateDateForShip';
SELECT NVL(MAX(last_update_date), min_date)
INTO l_header_last_update_date
FROM inl_ship_headers_all
WHERE ship_header_id = p_ship_header_id;
SELECT NVL(MAX(last_update_date), min_date)
INTO l_group_last_update_date
FROM inl_ship_line_groups
WHERE ship_header_id = p_ship_header_id;
SELECT NVL(MAX(last_update_date), min_date)
INTO l_line_last_update_date
FROM inl_ship_lines_all
WHERE ship_header_id = p_ship_header_id;
SELECT NVL(MAX(last_update_date), min_date)
INTO l_assoc_last_update_date
FROM inl_associations
WHERE ship_header_id = p_ship_header_id;
SELECT NVL(MAX(last_update_date), min_date)
INTO l_tax_last_update_date
FROM inl_tax_lines
WHERE ship_header_id = p_ship_header_id;
ship_last_update_date := greatest(l_header_last_update_date,
l_group_last_update_date,
l_line_last_update_date,
l_assoc_last_update_date,
l_tax_last_update_date);
p_var_name => 'ship_last_update_date',
p_var_value => ship_last_update_date);
RETURN(ship_last_update_date);
END Get_LastUpdateDateForShip;
SELECT start_auto_lot_number, segment1 -- Bug #15927464
INTO l_lot_number, l_item -- Bug #15927464
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT tracking_quantity_ind,
secondary_default_ind,
ont_pricing_qty_source,
start_auto_lot_number
INTO l_tracking_quantity_ind,
l_secondary_default_ind,
l_ont_pricing_qty_source,
l_lot_number
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
PROCEDURE Discard_Updates(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := L_FND_FALSE,
p_commit IN VARCHAR2 := L_FND_FALSE,
p_ship_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_program_name CONSTANT VARCHAR2(30) := 'Discard_Updates';
CURSOR c_delete_cl IS
SELECT cl.charge_line_id
FROM inl_charge_lines cl
WHERE cl.adjustment_type_flag = 'A'
AND cl.parent_charge_line_id IS NULL
AND EXISTS (SELECT 1
FROM inl_associations a
WHERE a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = cl.charge_line_id
AND a.adjustment_type_flag = 'A'
AND a.ship_header_id = p_ship_header_id
AND ROWNUM < 2
)
AND NOT EXISTS (SELECT 1
FROM inl_associations a
WHERE a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = cl.charge_line_id
AND a.adjustment_type_flag IS NOT NULL
AND a.adjustment_type_flag <> 'A'
AND a.ship_header_id = p_ship_header_id
AND ROWNUM < 2
)
AND NOT EXISTS (SELECT 1
FROM inl_charge_lines cl0
WHERE cl0.parent_charge_line_id = cl.charge_line_id
AND ROWNUM < 2
);
TYPE l_delete_cl_lst_tp IS
TABLE OF c_delete_cl%ROWTYPE INDEX BY BINARY_INTEGER;
l_delete_cl_lst l_delete_cl_lst_tp;
SAVEPOINT Discard_Updates_PVT;
UPDATE inl_ship_lines_all isl
SET isl.last_update_login = l_fnd_login_id,
isl.last_update_date = SYSDATE,
isl.last_updated_by = l_fnd_user_id,
isl.new_txn_unit_price = NULL,
isl.new_currency_conversion_type = NULL,
isl.new_currency_conversion_date = NULL,
isl.new_currency_conversion_rate = NULL
WHERE isl.ship_header_id = p_ship_header_id
AND isl.adjustment_num = (SELECT MIN(a.adjustment_num)
FROM inl_ship_lines_all a
WHERE a.ship_header_id = p_ship_header_id)
AND ( isl.new_txn_unit_price IS NOT NULL
OR isl.new_currency_conversion_type IS NOT NULL
OR isl.new_currency_conversion_date IS NOT NULL
OR isl.new_currency_conversion_rate IS NOT NULL)
;
UPDATE inl_charge_lines icl
SET icl.last_update_login = l_fnd_login_id,
icl.last_update_date = SYSDATE,
icl.last_updated_by = l_fnd_user_id,
icl.new_charge_amt = NULL,
icl.new_currency_conversion_type = NULL,
icl.new_currency_conversion_date = NULL,
icl.new_currency_conversion_rate = NULL,
icl.adjustment_type_flag = NULL -- Bug #13643479
WHERE icl.charge_line_id = (
SELECT
cl1.charge_line_id
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = icl.charge_line_id
CONNECT BY PRIOR cl1.charge_line_id = cl1.parent_charge_line_id
)
AND EXISTS (
SELECT 1
FROM inl_associations a
WHERE a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.ship_header_id = p_ship_header_id
AND a.from_parent_table_id = (
SELECT
cl1.charge_line_id
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = icl.charge_line_id
CONNECT BY PRIOR cl1.parent_charge_line_id = cl1.charge_line_id
)
)
AND ( icl.new_charge_amt IS NOT NULL
OR icl.new_currency_conversion_type IS NOT NULL
OR icl.new_currency_conversion_date IS NOT NULL
OR icl.new_currency_conversion_rate IS NOT NULL
OR icl.adjustment_type_flag IS NOT NULL)
AND NVL(icl.adjustment_type_flag, 'X') NOT IN ('Z', 'A')
;
OPEN c_delete_cl;
FETCH c_delete_cl BULK COLLECT INTO l_delete_cl_lst;
CLOSE c_delete_cl;
l_debug_info := 'Delete new associations ADDED as part of the adjustment';
DELETE FROM inl_associations a
WHERE a.adjustment_type_flag = 'A'
AND a.ship_header_id = p_ship_header_id
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = ( --Charge Line should be the top of charge lines
SELECT
cl1.charge_line_id
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = a.from_parent_table_id
CONNECT BY PRIOR cl1.charge_line_id = cl1.parent_charge_line_id
);
l_debug_info := 'Delete new charge lines ADDED as part of the adjustment';
IF NVL (l_delete_cl_lst.LAST, 0) > 0 THEN
FOR i IN NVL (l_delete_cl_lst.FIRST, 0) ..NVL (l_delete_cl_lst.LAST, 0)
LOOP
DELETE FROM inl_charge_lines
WHERE charge_line_id = l_delete_cl_lst(i).charge_line_id;
UPDATE inl_associations a
SET a.adjustment_type_flag = NULL,
a.last_update_login = l_fnd_login_id,
a.last_update_date = SYSDATE,
a.last_updated_by = l_fnd_user_id
WHERE a.adjustment_type_flag = 'R'
AND a.ship_header_id = p_ship_header_id
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = ( --Charge Line should be the top of charge lines
SELECT
cl1.charge_line_id
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = a.from_parent_table_id
CONNECT BY PRIOR cl1.parent_charge_line_id = cl1.charge_line_id
);
UPDATE inl_ship_headers_all a
SET a.pending_update_flag = 'N',
a.last_update_login = l_fnd_login_id,
a.last_update_date = SYSDATE,
a.last_updated_by = l_fnd_user_id
WHERE a.ship_header_id = p_ship_header_id
AND a.pending_update_flag IS NOT NULL
;
ROLLBACK TO Discard_Updates_PVT;
ROLLBACK TO Discard_Updates_PVT;
ROLLBACK TO Discard_Updates_PVT;
END Discard_Updates;