[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Association(p_ship_header_id IN NUMBER,
p_from_parent_table_name IN VARCHAR2,
p_from_parent_table_id IN NUMBER,
p_to_parent_table_name IN VARCHAR2,
p_to_parent_table_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_proc_name CONSTANT VARCHAR2(30) := 'Insert_Association';
SELECT icv.allocation_basis,
ab.base_uom_code
INTO l_allocation_basis,
l_allocation_uom_code
FROM inl_allocation_basis_vl ab,
inl_charge_line_types_vl icv,
inl_charge_lines icl
WHERE ab.allocation_basis_code (+) = icv.allocation_basis
AND icv.charge_line_type_id (+) = icl.charge_line_type_id
AND icl.charge_line_id = p_from_parent_table_id;
l_debug_info := 'Insert into INL Associations table';
INSERT INTO inl_associations(association_id,
ship_header_id,
from_parent_table_name,
from_parent_table_id,
to_parent_table_name,
to_parent_table_id,
allocation_basis,
allocation_uom_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (inl_associations_s.NEXTVAL,
p_ship_header_id,
p_from_parent_table_name,
p_from_parent_table_id,
p_to_parent_table_name,
p_to_parent_table_id,
l_allocation_basis,
l_allocation_uom_code,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.login_id);
END Insert_Association;
PROCEDURE Insert_ChargeLines(p_ship_header_id IN NUMBER,
p_charge_line_type_id IN NUMBER,
p_landed_cost_flag IN VARCHAR2,
p_update_allowed IN VARCHAR2,
p_source_code IN VARCHAR2,
p_charge_amt IN NUMBER,
p_currency_code IN VARCHAR2,
p_currency_conversion_type IN VARCHAR2,
p_currency_conversion_date IN DATE,
p_currency_conversion_rate IN NUMBER,
p_party_id IN NUMBER,
p_party_site_id IN NUMBER,
p_trx_business_category IN VARCHAR2,
p_intended_use IN VARCHAR2,
p_product_fiscal_class IN VARCHAR2,
p_product_category IN VARCHAR2,
p_product_type IN VARCHAR2,
p_user_def_fiscal_class IN VARCHAR2,
p_tax_classification_code IN VARCHAR2,
p_assessable_value IN NUMBER,
p_ship_from_party_id IN NUMBER,
p_ship_from_party_site_id IN NUMBER,
p_ship_to_organization_id IN NUMBER,
p_ship_to_location_id IN NUMBER,
p_bill_from_party_id IN NUMBER,
p_bill_from_party_site_id IN NUMBER,
p_bill_to_organization_id IN NUMBER,
p_bill_to_location_id IN NUMBER,
p_poa_party_id IN NUMBER,
p_poa_party_site_id IN NUMBER,
p_poo_organization_id IN NUMBER,
p_poo_location_id IN NUMBER,
p_to_parent_table_name IN VARCHAR2,
p_to_parent_table_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_proc_name CONSTANT VARCHAR2(30) := 'Insert_ChargeLines';
SELECT inl_charge_lines_s.NEXTVAL
INTO l_charge_line_id
FROM dual;
SELECT NVL(MAX(icl.charge_line_num),0) + 1
INTO l_charge_line_num
FROM inl_charge_lines icl,
inl_associations ias
WHERE ias.from_parent_table_name = 'INL_CHARGE_LINES'
AND ias.from_parent_table_id = icl.charge_line_id
AND ias.ship_header_id = p_ship_header_id;
l_debug_info := 'Insert into INL Charge Line table.';
INSERT INTO inl_charge_lines(charge_line_id,
charge_line_num,
charge_line_type_id,
landed_cost_flag,
update_allowed,
source_code,
adjustment_num,
charge_amt,
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,
assessable_value,
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,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(l_charge_line_id,
l_charge_line_num,
p_charge_line_type_id,
p_landed_cost_flag,
p_update_allowed,
p_source_code,
0, -- adjustment_num
p_charge_amt,
p_currency_code,
p_currency_conversion_type,
p_currency_conversion_date,
p_currency_conversion_rate,
p_party_id,
p_party_site_id,
p_trx_business_category,
p_intended_use,
p_product_fiscal_class,
p_product_category,
p_product_type,
p_user_def_fiscal_class,
p_tax_classification_code,
p_assessable_value,
'N', -- tax_already_calculated_flag
p_ship_from_party_id,
p_ship_from_party_site_id,
p_ship_to_organization_id,
p_ship_to_location_id,
p_bill_from_party_id,
p_bill_from_party_site_id,
p_bill_to_organization_id,
p_bill_to_location_id,
p_poa_party_id,
p_poa_party_site_id,
p_poo_organization_id,
p_poo_location_id,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.login_id);
l_debug_info := 'Call Insert_Association(...)';
Insert_Association(p_ship_header_id => p_ship_header_id,
p_from_parent_table_name => 'INL_CHARGE_LINES', -- from_parent_table_name
p_from_parent_table_id => l_charge_line_id, -- from_parent_table_id
p_to_parent_table_name => p_to_parent_table_name,
p_to_parent_table_id => p_to_parent_table_id,
x_return_status => l_return_status);
END Insert_ChargeLines;
l_debug_info := 'Call QP_PREQ_GRP.INSERT_LINES2 to insert into qp_preq_lines_tmp table';
QP_PREQ_GRP.INSERT_LINES2(p_line_index => l_line_index_tbl,
p_line_type_code => l_line_type_code_tbl,
p_pricing_effective_date => l_pricinl_effective_date_tbl,
p_active_date_first => l_active_date_first_tbl,
p_active_date_first_type => l_active_date_first_type_tbl,
p_active_date_second => l_active_date_second_tbl,
p_active_date_second_type => l_active_date_second_type_tbl,
p_line_quantity => l_line_quantity_tbl,
p_line_uom_code => l_line_uom_code_tbl,
p_request_type_code => l_request_type_code_tbl,
p_priced_quantity => l_priced_quantity_tbl,
p_priced_uom_code => l_priced_uom_code_tbl,
p_currency_code => l_currency_code_tbl,
p_unit_price => l_unit_price_tbl,
p_percent_price => l_percent_price_tbl,
p_uom_quantity => l_uom_quantity_tbl,
p_adjusted_unit_price => l_adjusted_unit_price_tbl,
p_upd_adjusted_unit_price => l_upd_adjusted_unit_price_tbl,
p_processed_flag => l_processed_flag_tbl,
p_price_flag => l_price_flag_tbl,
p_line_id => l_line_id_tbl,
p_processing_order => l_processing_order_tbl,
p_pricing_status_code => l_pricing_status_code_tbl,
p_pricing_status_text => l_pricing_status_text_tbl,
p_rounding_flag => l_rounding_flag_tbl,
p_rounding_factor => l_rounding_factor_tbl,
p_qualifiers_exist_flag => l_qualifiers_exist_flag_tbl,
p_pricing_attrs_exist_flag => l_pricing_attrs_exist_flag_tbl,
p_price_list_id => l_price_list_id_tbl,
p_validated_flag => l_pl_validated_flag_tbl,
p_price_request_code => l_price_request_code_tbl,
p_usage_pricing_type => l_usage_pricing_type_tbl,
p_line_category => l_line_category_tbl,
p_line_unit_price => l_line_unit_price_tbl,
p_list_price_override_flag => l_list_price_overide_flag_tbl,
x_status_code => x_return_status,
x_status_text => l_return_status_text);
l_control_rec.temp_table_insert_flag := 'N';
SELECT charge_type_code,
order_qty_adj_amt freight_charge,
pricing_status_code,
pricing_status_text,
modifier_level_code,
override_flag
BULK COLLECT INTO l_freight_charge_rec_tbl
FROM qp_ldets_v
WHERE line_index = k
AND list_line_type_code = 'FREIGHT_CHARGE'
AND applied_flag = 'Y';
SELECT pricing_status_code,
pricing_status_text
INTO l_qp_cost_table(k).pricing_status_code,
l_qp_cost_table(k).pricing_status_text
FROM qp_preq_lines_tmp
WHERE line_index = k;
x_charge_ln_tbl(l_charge_ln_index).update_allowed := l_freight_charge_tbl(n).override_flag;
SELECT ship_line_group_id
BULK COLLECT INTO l_ship_ln_group_id_tbl
FROM inl_ship_line_groups
WHERE ship_header_id = p_ship_header_id
ORDER BY ship_line_group_num;
l_debug_info := 'Delete QP Charges and Associations previously to the current Shipment';
SELECT ias.association_id,
icl.charge_line_id
BULK COLLECT INTO l_association_tbl, l_charge_line_tbl
FROM inl_associations ias,
inl_charge_lines icl
WHERE ias.from_parent_table_id = icl.charge_line_id
AND ias.from_parent_table_name = 'INL_CHARGE_LINES'
AND icl.source_code = 'QP'
AND ias.ship_header_id = p_ship_header_id;
DELETE FROM inl_charge_lines
WHERE charge_line_id = l_charge_line_tbl(s)
AND NOT EXISTS (SELECT 1
FROM inl_associations
WHERE from_parent_table_name = 'INL_CHARGE_LINES'
AND from_parent_table_id = l_charge_line_tbl(s)
AND ship_header_id <> p_ship_header_id);
DELETE FROM inl_associations
WHERE association_id = l_association_tbl(t);
l_debug_info := 'Iterate through all selected Shipment Line Groups';
SELECT PO_MOAC_UTILS_PVT.get_current_org_id,
NULL, -- order_header_id
rsh.vendor_id,
rsh.vendor_site_id,
ilg.creation_date,
NULL, -- order_type
isl.ship_to_location_id,
ish.organization_id,
ilg.ship_line_group_id,
rsh.hazard_class,
rsh.hazard_code,
rsh.shipped_date,
ilg.ship_line_group_num,
rsh.carrier_method,
rsh.packaging_code,
rsh.freight_carrier_code,
rsh.freight_terms,
NVL(FND_PROFILE.VALUE('INL_QP_CURRENCY_CODE'), -- if profile currency code is null then get functional currency
(SELECT gl.currency_code
FROM gl_sets_of_books gl,
financials_system_parameters fsp
WHERE gl.set_of_books_id = fsp.set_of_books_id
AND fsp.org_id = ish.org_id)) currency_code,
NULL, -- conversion_rate
NULL, -- conversion_rate_type
ish.org_id,
rsh.expected_receipt_date
INTO l_ship_ln_group_rec
FROM inl_ship_line_groups ilg,
inl_ship_headers_all ish,
inl_ship_lines_all isl,
rcv_transactions rtr,
rcv_shipment_headers rsh
WHERE ilg.ship_header_id = ish.ship_header_id
AND isl.ship_line_group_id = ilg.ship_line_group_id
AND rsh.shipment_header_id (+) = rtr.shipment_header_id
AND rtr.po_line_location_id (+) = isl.ship_line_source_id
AND rtr.lcm_shipment_line_id (+) = isl.ship_line_id
AND ish.ship_header_id = p_ship_header_id
AND ilg.ship_line_group_id = l_ship_ln_group_id_tbl(i)
AND ROWNUM < 2;
SELECT sh.ship_date
INTO l_qp_curr_conv_date
FROM inl_ship_headers sh
WHERE sh.ship_header_id = p_ship_header_id;
SELECT NULL, -- order_line_id
NULL, -- agreement_type
NULL, -- agreement_id
NULL, -- agreement_line_id
ph.vendor_id,
ph.vendor_site_id,
isl.ship_to_location_id,
NULL, -- ship_to_org_id
rsl.vendor_item_num,
pl.item_revision,
pl.item_id,
NULL, --category_id
DECODE(isl.currency_code, l_ship_ln_group_rec.currency_code, isl.currency_conversion_rate,
inl_landedcost_pvt.Converted_Amt (isl.txn_unit_price,
isl.currency_code,
l_ship_ln_group_rec.currency_code,
l_qp_curr_conv_type,
l_qp_curr_conv_date
)/ txn_unit_price),
isl.currency_conversion_type,
l_ship_ln_group_rec.currency_code, --isl.currency_code,
pll.need_by_date,
isl.ship_line_id,
isl.primary_uom_code,
ish.organization_id,
isl.txn_uom_code,
isg.src_type_code, -- source_document_code (PO, RMA, INVENTORY, REQ)
-- isl.txn_unit_price,
DECODE(isl.currency_code, l_ship_ln_group_rec.currency_code, isl.txn_unit_price,
inl_landedcost_pvt.Converted_Amt (isl.txn_unit_price,
isl.currency_code,
l_ship_ln_group_rec.currency_code,
l_qp_curr_conv_type,
l_qp_curr_conv_date
)) txn_conv_unit_price,
isl.txn_qty -- quantity_received
BULK COLLECT INTO l_ship_ln_tbl
FROM inl_ship_headers_all ish,
inl_ship_lines_all isl,
inl_ship_line_groups isg,
po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll,
rcv_transactions rtr,
rcv_shipment_lines rsl
WHERE ish.ship_header_id = isl.ship_header_id
AND isl.ship_line_group_id = isg.ship_line_group_id
AND rsl.shipment_line_id (+) = rtr.shipment_line_id
AND rtr.po_line_location_id (+) = isl.ship_line_source_id
AND rtr.lcm_shipment_line_id (+) = isl.ship_line_id
AND isl.ship_line_source_id = pll.line_location_id
AND ph.po_header_id = pll.po_header_id
AND pl.po_line_id = pll.po_line_id
AND pl.po_header_id = ph.po_header_id
AND isl.adjustment_num = 0
AND isl.ship_header_id = p_ship_header_id
AND isl.ship_line_group_id = l_ship_ln_group_id_tbl(i)
ORDER BY isl.ship_line_num;
l_debug_info := 'Call Insert_ChargeLines(...)';
Insert_ChargeLines(p_ship_header_id => p_ship_header_id,
p_charge_line_type_id => l_charge_ln_tbl(j).charge_line_type_id,
p_landed_cost_flag => l_charge_ln_tbl(j).landed_cost_flag,
p_update_allowed => l_charge_ln_tbl(j).update_allowed,
p_source_code => l_charge_ln_tbl(j).source_code,
p_charge_amt => l_charge_ln_tbl(j).charge_amt,
p_currency_code => l_charge_ln_tbl(j).currency_code,
p_currency_conversion_type => l_charge_ln_tbl(j).currency_conversion_type,
p_currency_conversion_date => l_charge_ln_tbl(j).currency_conversion_date,
p_currency_conversion_rate => l_charge_ln_tbl(j).currency_conversion_rate,
p_party_id => l_charge_ln_tbl(j).party_id,
p_party_site_id => l_charge_ln_tbl(j).party_site_id,
p_trx_business_category => l_charge_ln_tbl(j).trx_business_category,
p_intended_use => l_charge_ln_tbl(j).intended_use,
p_product_fiscal_class => l_charge_ln_tbl(j).product_fiscal_class,
p_product_category => l_charge_ln_tbl(j).product_category,
p_product_type => l_charge_ln_tbl(j).product_type,
p_user_def_fiscal_class => l_charge_ln_tbl(j).user_def_fiscal_class,
p_tax_classification_code => l_charge_ln_tbl(j).tax_classification_code,
p_assessable_value => l_charge_ln_tbl(j).assessable_value,
p_ship_from_party_id => l_charge_ln_tbl(j).ship_from_party_id,
p_ship_from_party_site_id => l_charge_ln_tbl(j).ship_from_party_site_id,
p_ship_to_organization_id => l_charge_ln_tbl(j).ship_to_organization_id,
p_ship_to_location_id => l_charge_ln_tbl(j).ship_to_location_id,
p_bill_from_party_id => l_charge_ln_tbl(j).bill_from_party_id,
p_bill_from_party_site_id => l_charge_ln_tbl(j).bill_from_party_site_id,
p_bill_to_organization_id => l_charge_ln_tbl(j).bill_to_organization_id,
p_bill_to_location_id => l_charge_ln_tbl(j).bill_to_location_id,
p_poa_party_id => l_charge_ln_tbl(j).poa_party_id,
p_poa_party_site_id => l_charge_ln_tbl(j).poa_party_site_id,
p_poo_organization_id => l_charge_ln_tbl(j).poo_organization_id,
p_poo_location_id => l_charge_ln_tbl(j).poo_location_id,
p_to_parent_table_name => l_charge_ln_tbl(j).to_parent_table_name,
p_to_parent_table_id => l_charge_ln_tbl(j).to_parent_table_id,
x_return_status => l_return_status);
l_charge_ln_tbl.DELETE;
l_ship_ln_tbl.DELETE;