The following lines contain the word 'select', 'insert', 'update' or 'delete':
,'Insert record into temp table'
);
INSERT INTO inv_mvt_excep_rep_temp
( error_type
, error_name
, inventory_item_id
, item_code
, movement_id
, from_currency_code
, to_currency_code
, exchange_type
, from_uom
, to_uom
, tp_name
, tp_type
, column_name
, number_of_records
)
VALUES
( l_error_type
, l_error_name
, NULL
, NULL
, p_mtl_movement_transaction.movement_id
, NULL ----from_currency_code
, NULL ----to_currency_code
, NULL ----p_mtl_movement_transaction.exchange_type
, NULL ----from_uom
, NULL ----to_uom
, NULL ----tp_name
, NULL ----tp_type
, p_excp_list(l_count_i).excp_col_name
, NULL
);
SELECT segment1
INTO l_item_code
FROM mtl_item_flexfields
WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
AND organization_id=p_mtl_movement_transaction.organization_id;
SELECT item_number
INTO l_item_code
FROM mtl_item_flexfields
WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
AND organization_id=p_mtl_movement_transaction.organization_id;
, 'Insert record into temp table'
);
INSERT INTO inv_mvt_excep_rep_temp
( error_type
, error_name
, inventory_item_id
, item_code
, movement_id
, from_currency_code
, to_currency_code
, exchange_type
, from_uom
, to_uom
, tp_name
, tp_type
, column_name
, number_of_records
)
VALUES
( 'Error'
, 'INV_MGD_MVT_MIS_ITEM'
, NULL
, NULL
, p_mtl_movement_transaction.movement_id
, NULL ----p_mtl_movement_transaction.currency_code
, NULL
, NULL ----p_mtl_movement_transaction.currency_conversion_rate
, NULL ----from_uom
, NULL ----to_uom
, NULL ----tp_name
, NULL ----tp_type
, NULL
, NULL
);
SELECT COUNT(*)
INTO l_count
FROM inv_mvt_excep_rep_temp
WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
AND error_name=l_error_name
AND column_name='COMMODITY_CODE';
, 'Update temp table'
);
UPDATE inv_mvt_excep_rep_temp
SET number_of_records=number_of_records+1
WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
AND error_name=l_error_name
AND column_name='COMMODITY_CODE';
, 'Insert record into temp table:'
);
INSERT INTO inv_mvt_excep_rep_temp
( error_type
, error_name
, inventory_item_id
, item_code
, movement_id
, from_currency_code
, to_currency_code
, exchange_type
, from_uom
, to_uom
, tp_name
, tp_type
, column_name
, number_of_records
)
VALUES
( l_error_type
, l_error_name
, p_mtl_movement_transaction.inventory_item_id
, l_item_code
, NULL
, NULL
, NULL
, NULL
, NULL ----from_uom
, NULL ----to_uom
, NULL ----tp_name
, NULL ----tp_type
, p_excp_list(l_count_i).excp_col_name
, 1
);
SELECT segment1
INTO l_item_code
FROM mtl_item_flexfields
WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
AND organization_id=p_mtl_movement_transaction.organization_id;
SELECT item_number
INTO l_item_code
FROM mtl_item_flexfields
WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
AND organization_id=p_mtl_movement_transaction.organization_id;
, 'Insert record into temp table:'
);
INSERT INTO inv_mvt_excep_rep_temp
( error_type
, error_name
, inventory_item_id
, item_code
, movement_id
, from_currency_code
, to_currency_code
, exchange_type
, from_uom
, to_uom
, tp_name
, tp_type
, column_name
, number_of_records
)
VALUES
( 'Error'
, 'INV_MGD_MVT_MIS_ITEM'
, NULL
, NULL
, p_mtl_movement_transaction.movement_id
, NULL ----p_mtl_movement_transaction.currency_code
, NULL
, NULL ----p_mtl_movement_transaction.currency_conversion_rate
, NULL ----from_uom
, NULL ----to_uom
, NULL ----tp_name
, NULL ----tp_type
, NULL
, NULL
);
SELECT COUNT(*)
INTO l_count
FROM inv_mvt_excep_rep_temp
WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
AND error_name=l_error_name;
, 'Update temp table:'
);
UPDATE inv_mvt_excep_rep_temp
SET number_of_records=number_of_records+1
WHERE inventory_item_id=p_mtl_movement_transaction.inventory_item_id
AND error_name=l_error_name;
, 'Insert record into temp table:'
);
INSERT INTO inv_mvt_excep_rep_temp
( error_type
, error_name
, inventory_item_id
, item_code
, movement_id
, from_currency_code
, to_currency_code
, exchange_type
, from_uom
, to_uom
, tp_name
, tp_type
, column_name
, number_of_records
)
VALUES
( l_error_type
, l_error_name
, p_mtl_movement_transaction.inventory_item_id
, l_item_code
, NULL
, NULL
, NULL
, NULL
, NULL ----from_uom
, NULL ----to_uom
, NULL ----tp_name
, NULL ----tp_type
, p_excp_list(l_count_i).excp_col_name
, 1
);
SELECT COUNT(*)
INTO l_count
FROM inv_mvt_excep_rep_temp
WHERE from_currency_code=p_mtl_movement_transaction.currency_code
AND error_name=l_error_name;
, 'Update temp table'
);
UPDATE inv_mvt_excep_rep_temp
SET number_of_records=number_of_records+1
WHERE from_currency_code=p_mtl_movement_transaction.currency_code
AND error_name=l_error_name;
, 'Insert record into temp table:'
);
INSERT INTO inv_mvt_excep_rep_temp
( error_type
, error_name
, inventory_item_id
, item_code
, movement_id
, from_currency_code
, to_currency_code
, exchange_type
, from_uom
, to_uom
, tp_name
, tp_type
, column_name
, number_of_records
)
VALUES
( l_error_type
, l_error_name
, NULL
, NULL
, NULL
, p_mtl_movement_transaction.currency_code
, l_f_currency_code
, p_mtl_movement_transaction.currency_conversion_rate
, NULL ----from_uom
, NULL ----to_uom
, NULL ----tp_name
, NULL ----tp_type
, p_excp_list(l_count_i).excp_col_name
, 1
);
SELECT name
INTO l_tp_name
FROM hr_all_organization_units
WHERE organization_id=p_mtl_movement_transaction.entity_org_id;
SELECT COUNT(*)
INTO l_count
FROM inv_mvt_excep_rep_temp
WHERE tp_name=l_tp_name
AND tp_type=l_tp_type
AND column_name='PARTNER_VAT_NUMBER'
AND error_name=l_error_name;
, 'Update temp table:'
);
UPDATE inv_mvt_excep_rep_temp
SET number_of_records=number_of_records+1
WHERE tp_name=l_tp_name
AND tp_type=l_tp_type
AND column_name='PARTNER_VAT_NUMBER'
AND error_name=l_error_name;
, 'Insert record into temp table, error_name='||l_error_name
);
INSERT INTO inv_mvt_excep_rep_temp
( error_type
, error_name
, inventory_item_id
, item_code
, movement_id
, from_currency_code
, to_currency_code
, exchange_type
, from_uom
, to_uom
, tp_name
, tp_type
, column_name
, number_of_records
)
VALUES
( l_error_type
, l_error_name
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL ----from_uom
, NULL ----to_uom
, l_tp_name
, l_tp_type
, p_excp_list(l_count_i).excp_col_name
, 1
);
, 'Insert record into temp table:'
);
INSERT INTO inv_mvt_excep_rep_temp
( error_type
, error_name
, inventory_item_id
, item_code
, movement_id
, from_currency_code
, to_currency_code
, exchange_type
, from_uom
, to_uom
, tp_name
, tp_type
, column_name
, number_of_records
)
VALUES
( l_error_type
, l_error_name
, NULL
, NULL
, p_mtl_movement_transaction.movement_id
, NULL ----p_mtl_movement_transaction.currency_code
, NULL
, NULL ----p_mtl_movement_transaction.currency_conversion_rate
, NULL ----from_uom
, NULL ----to_uom
, NULL ----tp_name
, NULL ----tp_type
, l_excp_col_name
, NULL
);
x_updated_flag VARCHAR2(1);
l_excp_list.DELETE;
, l_mtl_movement_statistics.last_update_date
, l_mtl_movement_statistics.last_updated_by
, l_mtl_movement_statistics.creation_date
, l_mtl_movement_statistics.created_by
, l_mtl_movement_statistics.last_update_login
, l_mtl_movement_statistics.document_source_type
, l_mtl_movement_statistics.creation_method
, l_mtl_movement_statistics.document_reference
, l_mtl_movement_statistics.document_line_reference
, l_mtl_movement_statistics.document_unit_price
, l_mtl_movement_statistics.document_line_ext_value
, l_mtl_movement_statistics.receipt_reference
, l_mtl_movement_statistics.shipment_reference
, l_mtl_movement_statistics.shipment_line_reference
, l_mtl_movement_statistics.pick_slip_reference
, l_mtl_movement_statistics.customer_name
, l_mtl_movement_statistics.customer_number
, l_mtl_movement_statistics.customer_location
, l_mtl_movement_statistics.transacting_from_org
, l_mtl_movement_statistics.transacting_to_org
, l_mtl_movement_statistics.vendor_name
, l_mtl_movement_statistics.vendor_number
, l_mtl_movement_statistics.vendor_site
, l_mtl_movement_statistics.bill_to_name
, l_mtl_movement_statistics.bill_to_number
, l_mtl_movement_statistics.bill_to_site
, l_mtl_movement_statistics.po_header_id
, l_mtl_movement_statistics.po_line_id
, l_mtl_movement_statistics.po_line_location_id
, l_mtl_movement_statistics.order_header_id
, l_mtl_movement_statistics.order_line_id
, l_mtl_movement_statistics.picking_line_id
, l_mtl_movement_statistics.shipment_header_id
, l_mtl_movement_statistics.shipment_line_id
, l_mtl_movement_statistics.ship_to_customer_id
, l_mtl_movement_statistics.ship_to_site_use_id
, l_mtl_movement_statistics.bill_to_customer_id
, l_mtl_movement_statistics.bill_to_site_use_id
, l_mtl_movement_statistics.vendor_id
, l_mtl_movement_statistics.vendor_site_id
, l_mtl_movement_statistics.from_organization_id
, l_mtl_movement_statistics.to_organization_id
, l_mtl_movement_statistics.parent_movement_id
, l_mtl_movement_statistics.inventory_item_id
, l_mtl_movement_statistics.item_description
, l_mtl_movement_statistics.item_cost
, l_mtl_movement_statistics.transaction_quantity
, l_mtl_movement_statistics.transaction_uom_code
, l_mtl_movement_statistics.primary_quantity
, l_mtl_movement_statistics.invoice_batch_id
, l_mtl_movement_statistics.invoice_id
, l_mtl_movement_statistics.customer_trx_line_id
, l_mtl_movement_statistics.invoice_batch_reference
, l_mtl_movement_statistics.invoice_reference
, l_mtl_movement_statistics.invoice_line_reference
, l_mtl_movement_statistics.invoice_date_reference
, l_mtl_movement_statistics.invoice_quantity
, l_mtl_movement_statistics.invoice_unit_price
, l_mtl_movement_statistics.invoice_line_ext_value
, l_mtl_movement_statistics.outside_code
, l_mtl_movement_statistics.outside_ext_value
, l_mtl_movement_statistics.outside_unit_price
, l_mtl_movement_statistics.currency_code
, l_mtl_movement_statistics.currency_conversion_rate
, l_mtl_movement_statistics.currency_conversion_type
, l_mtl_movement_statistics.currency_conversion_date
, l_mtl_movement_statistics.period_name
, l_mtl_movement_statistics.report_reference
, l_mtl_movement_statistics.report_date
, l_mtl_movement_statistics.category_id
, l_mtl_movement_statistics.weight_method
, l_mtl_movement_statistics.unit_weight
, l_mtl_movement_statistics.total_weight
, l_mtl_movement_statistics.transaction_nature
, l_mtl_movement_statistics.delivery_terms
, l_mtl_movement_statistics.transport_mode
, l_mtl_movement_statistics.alternate_quantity
, l_mtl_movement_statistics.alternate_uom_code
, l_mtl_movement_statistics.dispatch_territory_code
, l_mtl_movement_statistics.destination_territory_code
, l_mtl_movement_statistics.origin_territory_code
, l_mtl_movement_statistics.stat_method
, l_mtl_movement_statistics.stat_adj_percent
, l_mtl_movement_statistics.stat_adj_amount
, l_mtl_movement_statistics.stat_ext_value
, l_mtl_movement_statistics.area
, l_mtl_movement_statistics.port
, l_mtl_movement_statistics.stat_type
, l_mtl_movement_statistics.comments
, l_mtl_movement_statistics.attribute_category
, l_mtl_movement_statistics.commodity_code
, l_mtl_movement_statistics.commodity_description
, l_mtl_movement_statistics.requisition_header_id
, l_mtl_movement_statistics.requisition_line_id
, l_mtl_movement_statistics.picking_line_detail_id
, l_mtl_movement_statistics.usage_type
, l_mtl_movement_statistics.zone_code
, l_mtl_movement_statistics.edi_sent_flag
, l_mtl_movement_statistics.statistical_procedure_code
, l_mtl_movement_statistics.movement_amount
, l_mtl_movement_statistics.triangulation_country_code
, l_mtl_movement_statistics.csa_code
, l_mtl_movement_statistics.oil_reference_code
, l_mtl_movement_statistics.container_type_code
, l_mtl_movement_statistics.flow_indicator_code
, l_mtl_movement_statistics.affiliation_reference_code
, l_mtl_movement_statistics.origin_territory_eu_code
, l_mtl_movement_statistics.destination_territory_eu_code
, l_mtl_movement_statistics.dispatch_territory_eu_code
, l_mtl_movement_statistics.set_of_books_period
, l_mtl_movement_statistics.taric_code
, l_mtl_movement_statistics.preference_code
, l_mtl_movement_statistics.rcv_transaction_id
, l_mtl_movement_statistics.mtl_transaction_id
, l_mtl_movement_statistics.total_weight_uom_code
, l_mtl_movement_statistics.financial_document_flag
, l_mtl_movement_statistics.customer_vat_number
, l_mtl_movement_statistics.attribute1
, l_mtl_movement_statistics.attribute2
, l_mtl_movement_statistics.attribute3
, l_mtl_movement_statistics.attribute4
, l_mtl_movement_statistics.attribute5
, l_mtl_movement_statistics.attribute6
, l_mtl_movement_statistics.attribute7
, l_mtl_movement_statistics.attribute8
, l_mtl_movement_statistics.attribute9
, l_mtl_movement_statistics.attribute10
, l_mtl_movement_statistics.attribute11
, l_mtl_movement_statistics.attribute12
, l_mtl_movement_statistics.attribute13
, l_mtl_movement_statistics.attribute14
, l_mtl_movement_statistics.attribute15
, l_mtl_movement_statistics.triangulation_country_eu_code
, l_mtl_movement_statistics.distribution_line_number
, l_mtl_movement_statistics.ship_to_name
, l_mtl_movement_statistics.ship_to_number
, l_mtl_movement_statistics.ship_to_site
, l_mtl_movement_statistics.edi_transaction_date
, l_mtl_movement_statistics.edi_transaction_reference
, l_mtl_movement_statistics.esl_drop_shipment_code;
l_excp_list.DELETE;
, x_updated_flag => x_updated_flag
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_excp_list => l_excp_list
, x_movement_statistics => l_ret_movement_statistics
);
l_excp_list.DELETE;
IF x_updated_flag='Y' OR l_ret_movement_statistics.movement_status='V'
THEN
INV_MGD_MVT_STATS_PVT.Update_Movement_Statistics
( p_movement_statistics => l_ret_movement_statistics
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);