[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 ,
ship_header_id ,
ship_line_id ,
table_name ,
column_name ,
column_value ,
processing_date ,
error_message_name ,
error_message ,
token1_name ,
token1_value ,
token2_name ,
token2_value ,
token3_name ,
token3_value ,
token4_name ,
token4_value ,
token5_name ,
token5_value ,
token6_name ,
token6_value ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
program_id ,
program_application_id,
program_update_date ,
request_id
)
VALUES
(
inl_ship_holds_s.NEXTVAL ,
p_ship_header_id ,
p_ship_line_id ,
p_table_name ,
p_column_name ,
p_column_value ,
SYSDATE ,
p_error_message_name ,
p_error_message ,
p_token1_name ,
p_token1_value ,
p_token2_name ,
p_token2_value ,
p_token3_name ,
p_token3_value ,
p_token4_name ,
p_token4_value ,
p_token5_name ,
p_token5_value ,
p_token6_name ,
p_token6_value ,
fnd_global.user_id ,
SYSDATE ,
fnd_global.user_id ,
SYSDATE ,
fnd_global.user_id ,
fnd_global.conc_program_id,
fnd_global.prog_appl_id ,
SYSDATE ,
fnd_global.conc_request_id
) ;
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 = fnd_global.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_proc_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 COUNT ( *)
INTO ac_lin
FROM inl_adj_ship_lines_v ol
WHERE ol.ship_header_id = p_ship_header_id
AND ROWNUM < 2;
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
WHERE rti.shipment_line_id = rsl.shipment_line_id);
SELECT uom_code
INTO x_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = x_unit_of_measure;
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 unit_of_measure_tl
INTO l_primary_uom_tl
FROM mtl_units_of_measure_vl
WHERE uom_code = p_primary_uom_code;
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 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
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 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 := ', '||c_ship_num.ship_num;
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_po_v po, -- BUG: 8229331
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 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_charge_line_types_vl clt ,
inl_associations assoc
WHERE clt.charge_line_type_id = cl.charge_line_type_id
AND 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 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.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,
inl_ship_line_types_vl olt
WHERE ol.ship_header_id = p_ship_header_id
AND ol.ship_line_type_id = olt.ship_line_type_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 ship_line_group_id
FROM inl_ship_line_groups lg
WHERE 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,
sh.ship_status_code,
sh.organization_id,
sh.location_id,
sh.ship_date,
sh.pending_matching_flag
INTO l_ship_type_id,
l_ship_type_code,
l_shipt_third_parties_allowed,
l_ship_status,
l_inv_org_id,
l_location_id,
l_ship_date,
l_pending_matching_flag
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 = fnd_global.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 = fnd_global.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 */
fnd_global.user_id, /* 09 */
sysdate, /* 10 */
fnd_global.user_id, /* 11 */
sysdate, /* 12 */
fnd_global.user_id /* 13 */
);
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 */
fnd_global.user_id, /* 18 */
sysdate, /* 19 */
fnd_global.user_id, /* 20 */
sysdate, /* 21 */
fnd_global.user_id /* 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 tl.adjustment_num = 0
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
WHERE p_ship_header_id IS NULL
AND tl.adjustment_num = 0
AND
(
p_tax_code IS NULL
OR tl.tax_code = p_tax_code
)
AND EXISTS
(
SELECT 1
FROM inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_TAX_LINES'
AND assoc.from_parent_table_id = tl.tax_line_id
AND assoc.to_parent_table_name = p_comp_name
AND assoc.to_parent_table_id = p_comp_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
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.txn_uom_code, /* 09b */ --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 */
NVL (mat.matched_curr_conversion_type, sl.currency_conversion_type), /* 16 */
NVL (mat.matched_curr_conversion_date, sl.currency_conversion_date), /* 17 */
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_txn_uom_code , /* 09b */ --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_date , /* 17 */
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_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 sl.ship_line_id = mat.to_parent_table_id
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
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_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 */
)
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 */
sl.secondary_qty, /* 23 */
sl.secondary_uom_code, /* 24 */
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 */
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 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 */
fnd_global.user_id, /* 39 */
sysdate, /* 40 */
fnd_global.user_id, /* 41 */
sysdate, /* 42 */
fnd_global.user_id /* 43 */
) ;
SELECT
assoc.to_parent_table_name,
assoc.to_parent_table_id,
assoc.ship_header_id
FROM inl_associations assoc,
inl_charge_lines cl
WHERE 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
AND EXISTS
(SELECT 1
FROM inl_matches m
WHERE assoc.ship_header_id = m.ship_header_id
AND
((p_match_id IS NOT NULL
AND m.match_id = p_match_id )
OR (p_match_Amount_id IS NOT NULL
AND EXISTS (SELECT 1
FROM inl_matches m2
WHERE m2.match_id = m.match_id
AND m2.match_Amount_id = p_match_Amount_id
AND rownum <2)))
AND rownum <2
)
UNION ALL
SELECT
m.to_parent_table_name,
m.to_parent_table_id,
m.ship_header_id
FROM inl_matches m
WHERE ((p_match_id IS NOT NULL
AND m.match_id = p_match_id )
OR
(p_match_Amount_id IS NOT NULL
AND EXISTS (SELECT 1
FROM inl_matches m2
WHERE m2.match_id = m.match_id
AND m2.match_Amount_id = p_match_Amount_id
AND rownum <2)))
;
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_adj_charge_lines_v cl
WHERE cl.adjustment_num = 0
AND cl.charge_line_type_id = p_charge_line_type_id
AND EXISTS
(
SELECT 1
FROM 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 (
(
pc_level = 'ALL'
AND EXISTS (SELECT 1
FROM inl_matches m
WHERE m.ship_header_id = assoc.ship_header_id
AND
((p_match_id IS NOT NULL
AND m.match_id = p_match_id )
OR (p_match_Amount_id IS NOT NULL
AND EXISTS (SELECT 1
FROM inl_matches m2
WHERE m2.match_id = m.match_id
AND m2.match_Amount_id = p_match_Amount_id
AND rownum <2)))
AND rownum <2
)
)
OR (pc_level = 'LINE'
AND assoc.to_parent_table_name = 'INL_SHIP_LINES'
AND EXISTS
(SELECT 1
FROM inl_matches m
WHERE m.ship_header_id = assoc.ship_header_id
AND m.to_parent_table_name = 'INL_SHIP_LINES'
AND m.to_parent_table_id = assoc.to_parent_table_id
AND
((p_match_id IS NOT NULL
AND m.match_id = p_match_id )
OR (p_match_Amount_id IS NOT NULL
AND EXISTS (SELECT 1
FROM inl_matches m2
WHERE m2.match_id = m.match_id
AND m2.match_Amount_id = p_match_Amount_id
AND rownum <2)))
AND rownum <2
) )
OR (pc_level = 'GROUP'
AND assoc.to_parent_table_name IN ('INL_SHIP_LINES','INL_SHIP_LINE_GROUPS')
AND EXISTS
(SELECT 1
FROM inl_matches m
WHERE m.ship_header_id = assoc.ship_header_id
AND (
( m.to_parent_table_name = assoc.to_parent_table_name
AND m.to_parent_table_id = assoc.to_parent_table_id
)
OR
(assoc.to_parent_table_name = 'INL_SHIP_LINES'
AND EXISTS (
select 1
from inl_ship_lines sl
where m.to_parent_table_name = 'INL_SHIP_LINE_GROUPS'
AND sl.ship_line_group_id = m.to_parent_table_id
)
)
OR
(assoc.to_parent_table_name = 'INL_SHIP_LINE_GROUPS'
AND EXISTS (
select 1
from inl_ship_lines sl
where m.to_parent_table_name = 'INL_SHIP_LINES'
AND sl.ship_line_id = m.to_parent_table_id
AND sl.ship_line_group_id = assoc.to_parent_table_id
)
)
)
AND
((p_match_id IS NOT NULL
AND m.match_id = p_match_id )
OR (p_match_Amount_id IS NOT NULL
AND EXISTS (SELECT 1
FROM inl_matches m2
WHERE m2.match_id = m.match_id
AND m2.match_Amount_id = p_match_Amount_id
AND rownum <2)))
AND rownum <2
)
)
)
AND ROWNUM < 2
)
;
SELECT m.to_parent_table_id ,
m.from_parent_table_name ,
m.from_parent_table_id ,
m.to_parent_table_name ,
m.to_parent_table_id ,
m.matched_uom_code ,
m.matched_amt ,
m.replace_estim_qty_flag ,
m.existing_match_info_flag ,
m.party_id ,
m.party_site_id ,
m.charge_line_type_id ,
m.matched_curr_code ,
m.matched_curr_conversion_type,
m.matched_curr_conversion_date,
m.matched_curr_conversion_rate
INTO l_ship_line_id ,
l_from_parent_table_name ,
l_from_parent_table_id ,
l_to_parent_table_name ,
l_to_parent_table_id ,
l_matched_uom_code ,
l_matched_amt ,
l_replace_estim_qty_flag ,
l_existing_match_info_flag ,
l_party_id ,
l_party_site_id ,
l_charge_line_type_id ,
l_matched_curr_code ,
l_matched_curr_conversion_type,
l_matched_curr_conversion_date,
l_matched_curr_conversion_rate
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 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 ,
m.from_parent_table_name ,
m.from_parent_table_id ,
m.to_parent_table_name ,
m.to_parent_table_id ,
ma.matched_amt ,
m.party_id ,
m.party_site_id ,
m.charge_line_type_id ,
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 ,
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_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_corr_match_amounts_v ma,
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 ROWNUM = 1;
SELECT p_adjustment_num,
cl.charge_line_id ,
cl.charge_line_num
INTO l_ChLn_Assoc.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_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
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.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
ma.matched_amt 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,
--
m.tax_code tax_code ,
m.ship_header_id ship_header_id ,
min(m.from_parent_table_name) source_parent_table_name,
min(m.from_parent_table_id) source_parent_table_id ,
sum(m.nrec_tax_amt) nrec_tax_amt ,
m.tax_amt_included_flag tax_amt_included_flag ,
m.charge_line_type_id charge_line_type_id ,
--
clt.allocation_basis allocation_basis ,
abv.base_uom_code base_uom_code ,
MAX(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 ,
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_corr_match_amounts_v ma ,
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
GROUP BY
m.tax_code ,
m.ship_header_id ,
m.tax_amt_included_flag ,
m.charge_line_type_id ,
ma.matched_amt ,
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
;
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 tl.match_amount_id = m.match_amount_id;
SELECT
m.match_id ,
m.correction_match_id ,
m.match_amount_id ,
m.match_type_code ,
m.charge_line_type_id ,
m.tax_code ,
m.to_parent_table_name,
m.to_parent_table_id
FROM
inl_charge_lines cl,
inl_ship_lines sl ,
inl_ship_headers sh ,
inl_corr_matches_v m
WHERE
cl.charge_line_id (+) = DECODE (m.to_parent_table_name, 'INL_CHARGE_LINES', m.to_parent_table_id, NULL)
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'
UNION
SELECT DISTINCT
NULL AS match_id ,
NULL AS correction_match_id ,
m.match_amount_id AS match_amount_id ,
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
FROM
inl_ship_headers sh,
inl_corr_matches_v m
WHERE
sh.ship_header_id = m.ship_header_id
AND m.ship_header_id = p_ship_header_id
AND m.match_amount_id IS NOT NULL
AND m.match_type_code <> 'CORRECTION'
AND NVL (m.adj_already_generated_flag, 'N') = 'N'
ORDER BY 1;
SELECT adjustment_num + 1
INTO l_adjustment_num
FROM inl_ship_headers
WHERE ship_header_id = p_ship_header_id FOR UPDATE;
UPDATE inl_matches m
SET m.adj_already_generated_flag = 'Y' ,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE m.match_id = NVL (rec_match.correction_match_id, rec_match.match_id)
OR m.match_amount_id = rec_match.match_amount_id;
l_debug_info := 'Update Shipment Header Adjustment Number';
UPDATE inl_ship_headers
SET adjustment_num = l_adjustment_num
WHERE ship_header_id = p_ship_header_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 pending_matching_flag,
organization_id
INTO l_pending_matching_flag,
l_organization_id
FROM inl_ship_headers
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Update PendingMatchingFlag';
UPDATE inl_ship_headers
SET pending_matching_flag = 'N',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id
WHERE ship_header_id = p_ship_header_id;
SELECT ship_header_id
FROM inl_ship_headers
WHERE ship_status_code = 'COMPLETED'
AND pending_matching_flag = 'Y'
AND (p_organization_id IS NULL
OR organization_id = p_organization_id);
PROCEDURE Update_PendingMatchingFlag
(
p_ship_header_id IN NUMBER,
p_pending_matching_flag IN VARCHAR,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_proc_name CONSTANT VARCHAR2 (100) := 'Update_PendingMatchingFlag';
UPDATE inl_ship_headers
SET pending_matching_flag = p_pending_matching_flag,
created_by = fnd_global.user_id ,
creation_date = SYSDATE ,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id
WHERE ship_header_id = p_ship_header_id;
END Update_PendingMatchingFlag;
PROCEDURE Delete_ChargeAssoc
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_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;