[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_insert_flag VARCHAR2(1);
l_insert_flag := 'Y';
SELECT MTL_MOVEMENT_STATISTICS_S.NEXTVAL
INTO l_parent_movement_id
FROM SYS.DUAL;
INSERT INTO
MTL_MOVEMENT_STATISTICS(
movement_id
, organization_id
, entity_org_id
, movement_type
, movement_status
, transaction_date
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, document_source_type
, creation_method
, document_reference
, document_line_reference
, document_unit_price
, document_line_ext_value
, receipt_reference
, shipment_reference
, shipment_line_reference
, pick_slip_reference
, customer_name
, customer_number
, customer_location
, transacting_from_org
, transacting_to_org
, vendor_name
, vendor_number
, vendor_site
, bill_to_name
, bill_to_number
, bill_to_site
, ship_to_name
, ship_to_number
, ship_to_site
, po_header_id
, po_line_id
, po_line_location_id
, order_header_id
, order_line_id
, picking_line_id
, shipment_header_id
, shipment_line_id
, ship_to_customer_id
, ship_to_site_use_id
, bill_to_customer_id
, bill_to_site_use_id
, vendor_id
, vendor_site_id
, from_organization_id
, to_organization_id
, parent_movement_id
, inventory_item_id
, item_description
, item_cost
, transaction_quantity
, transaction_uom_code
, primary_quantity
, invoice_batch_id
, invoice_id
, customer_trx_line_id
, invoice_batch_reference
, invoice_reference
, invoice_line_reference
, invoice_date_reference
, invoice_quantity
, invoice_unit_price
, invoice_line_ext_value
, outside_code
, outside_ext_value
, outside_unit_price
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, period_name
, report_reference
, report_date
, category_id
, weight_method
, unit_weight
, total_weight
, transaction_nature
, delivery_terms
, transport_mode
, alternate_quantity
, alternate_uom_code
, dispatch_territory_code
, destination_territory_code
, origin_territory_code
, dispatch_territory_eu_code
, destination_territory_eu_code
, origin_territory_eu_code
, stat_method
, stat_adj_percent
, stat_adj_amount
, stat_ext_value
, area
, port
, stat_type
, comments
, commodity_code
, commodity_description
, requisition_header_id
, requisition_line_id
, picking_line_detail_id
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, edi_sent_flag
, usage_type
, zone_code
, statistical_procedure_code
, movement_amount
, taric_code
, preference_code
, triangulation_country_code
, triangulation_country_eu_code
, csa_code
, oil_reference_code
, container_type_code
, flow_indicator_code
, affiliation_reference_code
, set_of_books_period
, rcv_transaction_id
, mtl_transaction_id
, total_weight_uom_code
, distribution_line_number
, financial_document_flag
, edi_transaction_reference
, edi_transaction_date
, esl_drop_shipment_code
, customer_vat_number
)
VALUES(
l_parent_movement_id
, l_movement_transaction.organization_id
, l_movement_transaction.entity_org_id
, l_movement_transaction.movement_type
, l_movement_transaction.movement_status
, TRUNC(l_movement_transaction.transaction_date)
, l_movement_transaction.last_update_date
, l_movement_transaction.last_updated_by
, l_movement_transaction.creation_date
, l_movement_transaction.created_by
, l_movement_transaction.last_update_login
, l_movement_transaction.document_source_type
, NVL(l_movement_transaction.creation_method,'A')
, l_movement_transaction.document_reference
, l_movement_transaction.document_line_reference
, l_movement_transaction.document_unit_price
, l_movement_transaction.document_line_ext_value
, l_movement_transaction.receipt_reference
, l_movement_transaction.shipment_reference
, l_movement_transaction.shipment_line_reference
, l_movement_transaction.pick_slip_reference
, l_movement_transaction.customer_name
, l_movement_transaction.customer_number
, l_movement_transaction.customer_location
, l_movement_transaction.transacting_from_org
, l_movement_transaction.transacting_to_org
, l_movement_transaction.vendor_name
, l_movement_transaction.vendor_number
, l_movement_transaction.vendor_site
, l_movement_transaction.bill_to_name
, l_movement_transaction.bill_to_number
, l_movement_transaction.bill_to_site
, l_movement_transaction.ship_to_name
, l_movement_transaction.ship_to_number
, l_movement_transaction.ship_to_site
, l_movement_transaction.po_header_id
, l_movement_transaction.po_line_id
, l_movement_transaction.po_line_location_id
, l_movement_transaction.order_header_id
, l_movement_transaction.order_line_id
, l_movement_transaction.picking_line_id
, l_movement_transaction.shipment_header_id
, l_movement_transaction.shipment_line_id
, l_movement_transaction.ship_to_customer_id
, l_movement_transaction.ship_to_site_use_id
, l_movement_transaction.bill_to_customer_id
, l_movement_transaction.bill_to_site_use_id
, l_movement_transaction.vendor_id
, l_movement_transaction.vendor_site_id
, l_movement_transaction.from_organization_id
, l_movement_transaction.to_organization_id
, nvl(l_movement_id,l_parent_movement_id)
, l_movement_transaction.inventory_item_id
, l_movement_transaction.item_description
, l_movement_transaction.item_cost
, l_movement_transaction.transaction_quantity
, l_movement_transaction.transaction_uom_code
, l_movement_transaction.primary_quantity
, l_movement_transaction.invoice_batch_id
, l_movement_transaction.invoice_id
, l_movement_transaction.customer_trx_line_id
, l_movement_transaction.invoice_batch_reference
, l_movement_transaction.invoice_reference
, l_movement_transaction.invoice_line_reference
, l_movement_transaction.invoice_date_reference
, l_movement_transaction.invoice_quantity
, l_movement_transaction.invoice_unit_price
, l_movement_transaction.invoice_line_ext_value
, l_movement_transaction.outside_code
, l_movement_transaction.outside_ext_value
, l_movement_transaction.outside_unit_price
, l_movement_transaction.currency_code
, l_movement_transaction.currency_conversion_rate
, l_movement_transaction.currency_conversion_type
, l_movement_transaction.currency_conversion_date
, l_movement_transaction.period_name
, l_movement_transaction.report_reference
, l_movement_transaction.report_date
, l_movement_transaction.category_id
, l_movement_transaction.weight_method
, l_movement_transaction.unit_weight
, l_movement_transaction.total_weight
, l_movement_transaction.transaction_nature
, l_movement_transaction.delivery_terms
, l_movement_transaction.transport_mode
, l_movement_transaction.alternate_quantity
, l_movement_transaction.alternate_uom_code
, l_movement_transaction.dispatch_territory_code
, l_movement_transaction.destination_territory_code
, l_movement_transaction.origin_territory_code
, l_movement_transaction.dispatch_territory_eu_code
, l_movement_transaction.destination_territory_eu_code
, l_movement_transaction.origin_territory_eu_code
, l_movement_transaction.stat_method
, l_movement_transaction.stat_adj_percent
, l_movement_transaction.stat_adj_amount
, nvl(l_movement_transaction.stat_ext_value,
l_movement_transaction.movement_amount)
, l_movement_transaction.area
, l_movement_transaction.port
, l_movement_transaction.stat_type
, l_movement_transaction.comments
, l_movement_transaction.commodity_code
, l_movement_transaction.commodity_description
, l_movement_transaction.requisition_header_id
, l_movement_transaction.requisition_line_id
, l_movement_transaction.picking_line_detail_id
, l_movement_transaction.attribute1
, l_movement_transaction.attribute2
, l_movement_Transaction.attribute3
, l_movement_Transaction.attribute4
, l_movement_Transaction.attribute5
, l_movement_Transaction.attribute6
, l_movement_Transaction.attribute7
, l_movement_Transaction.attribute8
, l_movement_Transaction.attribute9
, l_movement_Transaction.attribute10
, l_movement_Transaction.attribute11
, l_movement_Transaction.attribute12
, l_movement_Transaction.attribute13
, l_movement_Transaction.attribute14
, l_movement_Transaction.attribute15
, l_movement_transaction.edi_sent_flag
, l_movement_transaction.usage_type
, l_movement_transaction.zone_code
, l_movement_transaction.statistical_procedure_code
, l_movement_transaction.movement_amount
, l_movement_transaction.taric_code
, l_movement_transaction.preference_code
, l_movement_transaction.triangulation_country_code
, l_movement_transaction.triangulation_country_eu_code
, l_movement_transaction.csa_code
, l_movement_transaction.oil_reference_code
, l_movement_transaction.container_type_code
, l_movement_transaction.flow_indicator_code
, l_movement_transaction.affiliation_reference_code
, l_movement_transaction.set_of_books_period
, l_movement_transaction.rcv_transaction_id
, l_movement_transaction.mtl_transaction_id
, l_movement_transaction.total_weight_uom_code
, l_movement_transaction.distribution_line_number
, NVL(l_movement_transaction.financial_document_flag,'NOT_REQUIRED')
, l_movement_transaction.edi_transaction_reference
, l_movement_transaction.edi_transaction_date
, l_movement_transaction.esl_drop_shipment_code
, l_movement_transaction.customer_vat_number
);
, 'Insert movement record successfully'
);
FND_MESSAGE.Set_Name('INV', 'INV_MGD_UPDATE_EXC');
x_movement_transaction.last_updated_by :=
NVL(TO_NUMBER(fnd_profile.value('USER_ID')),0);
x_movement_transaction.last_update_date := SYSDATE;
x_movement_transaction.last_update_login :=
NVL(TO_NUMBER(fnd_profile.value('LOGIN_ID')),0);
SELECT
mtl_stats.movement_id
, mtl_stats.organization_id
, mtl_stats.entity_org_id
, mtl_stats.movement_type
, mtl_stats.movement_status
, mtl_stats.transaction_date
, mtl_stats.last_update_date
, mtl_stats.last_updated_by
, mtl_stats.creation_date
, mtl_stats.created_by
, mtl_stats.last_update_login
, mtl_stats.document_source_type
, mtl_stats.creation_method
, mtl_stats.document_reference
, mtl_stats.document_line_reference
, mtl_stats.document_unit_price
, mtl_stats.document_line_ext_value
, mtl_stats.receipt_reference
, mtl_stats.shipment_reference
, mtl_stats.shipment_line_reference
, mtl_stats.pick_slip_reference
, mtl_stats.customer_name
, mtl_stats.customer_number
, mtl_stats.customer_location
, mtl_stats.transacting_from_org
, mtl_stats.transacting_to_org
, mtl_stats.vendor_name
, mtl_stats.vendor_number
, mtl_stats.vendor_site
, mtl_stats.bill_to_name
, mtl_stats.bill_to_number
, mtl_stats.bill_to_site
, mtl_stats.po_header_id
, mtl_stats.po_line_id
, mtl_stats.po_line_location_id
, mtl_stats.order_header_id
, mtl_stats.order_line_id
, mtl_stats.picking_line_id
, mtl_stats.shipment_header_id
, mtl_stats.shipment_line_id
, mtl_stats.ship_to_customer_id
, mtl_stats.ship_to_site_use_id
, mtl_stats.bill_to_customer_id
, mtl_stats.bill_to_site_use_id
, mtl_stats.vendor_id
, mtl_stats.vendor_site_id
, mtl_stats.from_organization_id
, mtl_stats.to_organization_id
, mtl_stats.parent_movement_id
, mtl_stats.inventory_item_id
, mtl_stats.item_description
, mtl_stats.item_cost
, mtl_stats.transaction_quantity
, mtl_stats.transaction_uom_code
, mtl_stats.primary_quantity
, mtl_stats.invoice_batch_id
, mtl_stats.invoice_id
, mtl_stats.customer_trx_line_id
, mtl_stats.invoice_batch_reference
, mtl_stats.invoice_reference
, mtl_stats.invoice_line_reference
, mtl_stats.invoice_date_reference
, mtl_stats.invoice_quantity
, mtl_stats.invoice_unit_price
, mtl_stats.invoice_line_ext_value
, mtl_stats.outside_code
, mtl_stats.outside_ext_value
, mtl_stats.outside_unit_price
, mtl_stats.currency_code
, mtl_stats.currency_conversion_rate
, mtl_stats.currency_conversion_type
, mtl_stats.currency_conversion_date
, mtl_stats.period_name
, mtl_stats.report_reference
, mtl_stats.report_date
, mtl_stats.category_id
, mtl_stats.weight_method
, mtl_stats.unit_weight
, mtl_stats.total_weight
, mtl_stats.transaction_nature
, mtl_stats.delivery_terms
, mtl_stats.transport_mode
, mtl_stats.alternate_quantity
, mtl_stats.alternate_uom_code
, mtl_stats.dispatch_territory_code
, mtl_stats.destination_territory_code
, mtl_stats.origin_territory_code
, mtl_stats.stat_method
, mtl_stats.stat_adj_percent
, mtl_stats.stat_adj_amount
, mtl_stats.stat_ext_value
, mtl_stats.area
, mtl_stats.port
, mtl_stats.stat_type
, mtl_stats.comments
, mtl_stats.attribute_category
, mtl_stats.commodity_code
, mtl_stats.commodity_description
, mtl_stats.requisition_header_id
, mtl_stats.requisition_line_id
, mtl_stats.picking_line_detail_id
, mtl_stats.usage_type
, mtl_stats.zone_code
, mtl_stats.edi_sent_flag
, mtl_stats.statistical_procedure_code
, mtl_stats.movement_amount
, mtl_stats.triangulation_country_code
, mtl_stats.csa_code
, mtl_stats.oil_reference_code
, mtl_stats.container_type_code
, mtl_stats.flow_indicator_code
, mtl_stats.affiliation_reference_code
, mtl_stats.origin_territory_eu_code
, mtl_stats.destination_territory_eu_code
, mtl_stats.dispatch_territory_eu_code
, mtl_stats.set_of_books_period
, mtl_stats.taric_code
, mtl_stats.preference_code
, mtl_stats.rcv_transaction_id
, mtl_stats.mtl_transaction_id
, mtl_stats.total_weight_uom_code
, mtl_stats.financial_document_flag
, mtl_stats.customer_vat_number
, mtl_stats.attribute1
, mtl_stats.attribute2
, mtl_stats.attribute3
, mtl_stats.attribute4
, mtl_stats.attribute5
, mtl_stats.attribute6
, mtl_stats.attribute7
, mtl_stats.attribute8
, mtl_stats.attribute9
, mtl_stats.attribute10
, mtl_stats.attribute11
, mtl_stats.attribute12
, mtl_stats.attribute13
, mtl_stats.attribute14
, mtl_stats.attribute15
, mtl_stats.triangulation_country_eu_code
, mtl_stats.distribution_line_number
, mtl_stats.ship_to_name
, mtl_stats.ship_to_number
, mtl_stats.ship_to_site
, mtl_stats.edi_transaction_date
, mtl_stats.edi_transaction_reference
, mtl_stats.esl_drop_shipment_code
FROM
MTL_MOVEMENT_STATISTICS mtl_stats
WHERE entity_org_id = p_legal_entity_id
AND period_name = p_period_name
AND document_source_type = nvl(p_document_source_type, document_source_type)
AND zone_code = p_economic_zone_code
AND usage_type = p_usage_type
AND stat_type = p_stat_type
AND (movement_status = 'O' OR (financial_document_flag = 'MISSING'))
ORDER BY
mtl_stats.movement_id;
PROCEDURE Update_Movement_Statistics (
p_movement_statistics IN
INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
, 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) := 'Update_Movement_Statistics';
/*bug #7499719 Call Out Program was not called for Update Movement Statistic*/
IF l_movement_transaction.movement_status IN ('O','V','P')
THEN
INV_MGD_MVT_DEF_ATTR.Default_Attr
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_movement_transaction => l_movement_transaction
, x_transaction_nature => l_movement_transaction.transaction_nature
, x_delivery_terms => l_movement_transaction.delivery_terms
, x_area => l_movement_transaction.area
, x_port => l_movement_transaction.port
, x_csa_code => l_movement_transaction.csa_code
, x_oil_reference_code => l_movement_transaction.oil_reference_code
, x_container_type_code => l_movement_transaction.container_type_code
, x_flow_indicator_code => l_movement_transaction.flow_indicator_code
, x_affiliation_reference_code =>
l_movement_transaction.affiliation_reference_code
, x_taric_code => l_movement_transaction.taric_code
, x_preference_code => l_movement_transaction.preference_code
, x_statistical_procedure_code =>
l_movement_transaction.statistical_procedure_code
, x_transport_mode => l_movement_transaction.transport_mode
--
-- bug 13686996
-- Added the DFF attributes from attribute1 to attribute15
-- to allow the customer to defalut the DFF values.
--
, x_attribute1 => l_movement_transaction.attribute1
, x_attribute2 => l_movement_transaction.attribute2
, x_attribute3 => l_movement_transaction.attribute3
, x_attribute4 => l_movement_transaction.attribute4
, x_attribute5 => l_movement_transaction.attribute5
, x_attribute6 => l_movement_transaction.attribute6
, x_attribute7 => l_movement_transaction.attribute7
, x_attribute8 => l_movement_transaction.attribute8
, x_attribute9 => l_movement_transaction.attribute9
, x_attribute10 => l_movement_transaction.attribute10
, x_attribute11 => l_movement_transaction.attribute11
, x_attribute12 => l_movement_transaction.attribute12
, x_attribute13 => l_movement_transaction.attribute13
, x_attribute14 => l_movement_transaction.attribute14
, x_attribute15 => l_movement_transaction.attribute15
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_return_status => l_return_status1
);
UPDATE MTL_MOVEMENT_STATISTICS
SET movement_id = P_MOVEMENT_STATISTICS.movement_id
, organization_id = P_MOVEMENT_STATISTICS.organization_id
, entity_org_id = P_MOVEMENT_STATISTICS.entity_org_id
, movement_type = P_MOVEMENT_STATISTICS.movement_type
, movement_status = P_MOVEMENT_STATISTICS.movement_status
, transaction_date = P_MOVEMENT_STATISTICS.transaction_date
, last_update_date = SYSDATE
, last_updated_by = NVL
( TO_NUMBER(FND_PROFILE.Value('USER_ID'))
, last_updated_by
)
, last_update_login = NVL
( TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'))
, last_update_login
)
, document_source_type = P_MOVEMENT_STATISTICS.document_source_type
, creation_method = P_MOVEMENT_STATISTICS.creation_method
, document_reference = P_MOVEMENT_STATISTICS.document_reference
, document_line_reference =
P_MOVEMENT_STATISTICS.document_line_reference
, document_unit_price = l_movement_transaction.document_unit_price
, document_line_ext_value =
P_MOVEMENT_STATISTICS.document_line_ext_value
, receipt_reference = P_MOVEMENT_STATISTICS.receipt_reference
, shipment_reference = P_MOVEMENT_STATISTICS.shipment_reference
, shipment_line_reference =
P_MOVEMENT_STATISTICS.shipment_line_reference
, pick_slip_reference = P_MOVEMENT_STATISTICS.pick_slip_reference
, customer_name = P_MOVEMENT_STATISTICS.customer_name
, customer_number = P_MOVEMENT_STATISTICS.customer_number
, customer_location = P_MOVEMENT_STATISTICS.customer_location
, transacting_from_org = P_MOVEMENT_STATISTICS.transacting_from_org
, transacting_to_org = P_MOVEMENT_STATISTICS.transacting_to_org
, vendor_name = P_MOVEMENT_STATISTICS.vendor_name
, vendor_number = P_MOVEMENT_STATISTICS.vendor_number
, vendor_site = P_MOVEMENT_STATISTICS.vendor_site
, bill_to_name = P_MOVEMENT_STATISTICS.bill_to_name
, bill_to_number = P_MOVEMENT_STATISTICS.bill_to_number
, bill_to_site = P_MOVEMENT_STATISTICS.bill_to_site
, ship_to_name = P_MOVEMENT_STATISTICS.ship_to_name
, ship_to_number = P_MOVEMENT_STATISTICS.ship_to_number
, ship_to_site = P_MOVEMENT_STATISTICS.ship_to_site
, po_header_id = P_MOVEMENT_STATISTICS.po_header_id
, po_line_id = P_MOVEMENT_STATISTICS.po_line_id
, po_line_location_id = P_MOVEMENT_STATISTICS.po_line_location_id
, order_header_id = P_MOVEMENT_STATISTICS.order_header_id
, order_line_id = P_MOVEMENT_STATISTICS.order_line_id
, picking_line_id = P_MOVEMENT_STATISTICS.picking_line_id
, shipment_header_id = P_MOVEMENT_STATISTICS.shipment_header_id
, shipment_line_id = P_MOVEMENT_STATISTICS.shipment_line_id
, ship_to_customer_id = P_MOVEMENT_STATISTICS.ship_to_customer_id
, ship_to_site_use_id = P_MOVEMENT_STATISTICS.ship_to_site_use_id
, bill_to_customer_id = P_MOVEMENT_STATISTICS.bill_to_customer_id
, bill_to_site_use_id = P_MOVEMENT_STATISTICS.bill_to_site_use_id
, vendor_id = P_MOVEMENT_STATISTICS.vendor_id
, vendor_site_id = P_MOVEMENT_STATISTICS.vendor_site_id
, from_organization_id = P_MOVEMENT_STATISTICS.from_organization_id
, to_organization_id = P_MOVEMENT_STATISTICS.to_organization_id
, parent_movement_id = P_MOVEMENT_STATISTICS.parent_movement_id
, inventory_item_id = P_MOVEMENT_STATISTICS.inventory_item_id
, item_description = P_MOVEMENT_STATISTICS.item_description
, item_cost = P_MOVEMENT_STATISTICS.item_cost
, transaction_quantity = P_MOVEMENT_STATISTICS.transaction_quantity
, transaction_uom_code = P_MOVEMENT_STATISTICS.transaction_uom_code
, primary_quantity = P_MOVEMENT_STATISTICS.primary_quantity
, invoice_batch_id = P_MOVEMENT_STATISTICS.invoice_batch_id
, invoice_id = P_MOVEMENT_STATISTICS.invoice_id
, customer_trx_line_id = P_MOVEMENT_STATISTICS.customer_trx_line_id
, invoice_batch_reference = P_MOVEMENT_STATISTICS.invoice_batch_reference
, invoice_reference = P_MOVEMENT_STATISTICS.invoice_reference
, invoice_line_reference = P_MOVEMENT_STATISTICS.invoice_line_reference
, invoice_date_reference = P_MOVEMENT_STATISTICS.invoice_date_reference
, invoice_quantity = P_MOVEMENT_STATISTICS.invoice_quantity
, invoice_unit_price = P_MOVEMENT_STATISTICS.invoice_unit_price
, invoice_line_ext_value = P_MOVEMENT_STATISTICS.invoice_line_ext_value
, outside_code = P_MOVEMENT_STATISTICS.outside_code
, outside_ext_value = P_MOVEMENT_STATISTICS.outside_ext_value
, outside_unit_price = P_MOVEMENT_STATISTICS.outside_unit_price
, currency_code = P_MOVEMENT_STATISTICS.currency_code
, currency_conversion_rate = P_MOVEMENT_STATISTICS.currency_conversion_rate
, currency_conversion_type = P_MOVEMENT_STATISTICS.currency_conversion_type
, currency_conversion_date = P_MOVEMENT_STATISTICS.currency_conversion_date
, period_name = P_MOVEMENT_STATISTICS.period_name
, report_reference = P_MOVEMENT_STATISTICS.report_reference
, report_date = P_MOVEMENT_STATISTICS.report_date
, category_id = P_MOVEMENT_STATISTICS.category_id
, weight_method = P_MOVEMENT_STATISTICS.weight_method
, unit_weight = P_MOVEMENT_STATISTICS.unit_weight
, total_weight = P_MOVEMENT_STATISTICS.total_weight
, transaction_nature = l_movement_transaction.transaction_nature
, delivery_terms = l_movement_transaction.delivery_terms
, transport_mode = l_movement_transaction.transport_mode
, alternate_quantity = P_MOVEMENT_STATISTICS.alternate_quantity
, alternate_uom_code = P_MOVEMENT_STATISTICS.alternate_uom_code
, dispatch_territory_code = P_MOVEMENT_STATISTICS.dispatch_territory_code
, destination_territory_code =
P_MOVEMENT_STATISTICS.destination_territory_code
, origin_territory_code = P_MOVEMENT_STATISTICS.origin_territory_code
, dispatch_territory_eu_code =
P_MOVEMENT_STATISTICS.dispatch_territory_eu_code
, destination_territory_eu_code =
P_MOVEMENT_STATISTICS.destination_territory_eu_code
, origin_territory_eu_code =
P_MOVEMENT_STATISTICS.origin_territory_eu_code
, stat_method = P_MOVEMENT_STATISTICS.stat_method
, stat_adj_percent = P_MOVEMENT_STATISTICS.stat_adj_percent
, stat_adj_amount = P_MOVEMENT_STATISTICS.stat_adj_amount
, stat_ext_value = l_movement_transaction.stat_ext_value
, area = l_movement_transaction.area
, port = l_movement_transaction.port
, stat_type = P_MOVEMENT_STATISTICS.stat_type
, commodity_code = P_MOVEMENT_STATISTICS.commodity_code
, commodity_description = P_MOVEMENT_STATISTICS.commodity_description
, requisition_header_id = P_MOVEMENT_STATISTICS.requisition_header_id
, requisition_line_id = P_MOVEMENT_STATISTICS.requisition_line_id
, picking_line_detail_id = P_MOVEMENT_STATISTICS.picking_line_detail_id
, statistical_procedure_code = l_movement_transaction.statistical_procedure_code
, comments = P_MOVEMENT_STATISTICS.comments
, attribute_category = P_MOVEMENT_STATISTICS.attribute_category
, attribute1 = P_MOVEMENT_STATISTICS.attribute1
, attribute2 = P_MOVEMENT_STATISTICS.attribute2
, attribute3 = P_MOVEMENT_STATISTICS.attribute3
, attribute4 = P_MOVEMENT_STATISTICS.attribute4
, attribute5 = P_MOVEMENT_STATISTICS.attribute5
, attribute6 = P_MOVEMENT_STATISTICS.attribute6
, attribute7 = P_MOVEMENT_STATISTICS.attribute7
, attribute8 = P_MOVEMENT_STATISTICS.attribute8
, attribute9 = P_MOVEMENT_STATISTICS.attribute9
, attribute10 = P_MOVEMENT_STATISTICS.attribute10
, attribute11 = P_MOVEMENT_STATISTICS.attribute11
, attribute12 = P_MOVEMENT_STATISTICS.attribute12
, attribute13 = P_MOVEMENT_STATISTICS.attribute13
, attribute14 = P_MOVEMENT_STATISTICS.attribute14
, attribute15 = P_MOVEMENT_STATISTICS.attribute15
, usage_type = P_MOVEMENT_STATISTICS.usage_type
, zone_code = P_MOVEMENT_STATISTICS.zone_code
, edi_sent_flag = P_MOVEMENT_STATISTICS.edi_sent_flag
, movement_amount = l_movement_transaction.movement_amount
, triangulation_country_code =
P_MOVEMENT_STATISTICS.triangulation_country_code
, triangulation_country_eu_code =
P_MOVEMENT_STATISTICS.triangulation_country_eu_code
, distribution_line_number =
P_MOVEMENT_STATISTICS.distribution_line_number
, csa_code = l_movement_transaction.csa_code
, oil_reference_code = l_movement_transaction.oil_reference_code
, container_type_code = l_movement_transaction.container_type_code
, flow_indicator_code = l_movement_transaction.flow_indicator_code
, affiliation_reference_code = l_movement_transaction.affiliation_reference_code
, financial_document_flag = P_MOVEMENT_STATISTICS.financial_document_flag
, set_of_books_period = P_MOVEMENT_STATISTICS.set_of_books_period
, edi_transaction_date = P_MOVEMENT_STATISTICS.edi_transaction_date
, edi_transaction_reference =
P_MOVEMENT_STATISTICS.edi_transaction_reference
, taric_code = l_movement_transaction.taric_code
, preference_code = l_movement_transaction.preference_code
, rcv_transaction_id = P_MOVEMENT_STATISTICS.rcv_transaction_id
, mtl_transaction_id = P_MOVEMENT_STATISTICS.mtl_transaction_id
, total_weight_uom_code = P_MOVEMENT_STATISTICS.total_weight_uom_code
, esl_drop_shipment_code = P_MOVEMENT_STATISTICS.esl_drop_shipment_code
, customer_vat_number = P_MOVEMENT_STATISTICS.customer_vat_number
WHERE movement_id = P_MOVEMENT_STATISTICS.movement_id;
END Update_Movement_Statistics ;
, x_updated_flag OUT NOCOPY VARCHAR2
);
, x_updated_flag OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_movement_statistics OUT NOCOPY
INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_MOVEMENT_STATISTICS';
l_updated_flag VARCHAR2(1);
x_updated_flag :='N';
g_final_excp_list.delete;
SELECT
order_number
INTO
l_orig_mvmt_transaction.order_number
FROM
oe_order_headers_all
WHERE header_id = l_orig_mvmt_transaction.order_header_id;
-- record updated
x_updated_flag :='Y';
-- record updated
x_updated_flag :='Y';
-- record updated
x_updated_flag :='Y';
x_updated_flag := 'Y';
, x_updated_flag => l_updated_flag
);
x_updated_flag := l_updated_flag;
-- record updated
x_updated_flag :='Y';
-- record updated
x_updated_flag :='Y';
g_final_excp_list.DELETE;
, x_updated_flag OUT NOCOPY VARCHAR2
)
IS
l_orig_mvmt_transaction
INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
x_updated_flag := 'N';
SELECT
1
INTO
l_chk
FROM
FND_LOOKUPS
WHERE lookup_type = 'MVT_DELIVERY_TERMS'
AND lookup_code = l_orig_mvmt_transaction.delivery_terms;
x_updated_flag :='Y';
x_updated_flag := 'Y';
PROCEDURE Delete_Movement_Statistics
( p_movement_transaction IN
INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
, x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
DELETE FROM MTL_MOVEMENT_STATISTICS
WHERE movement_id = p_movement_transaction.movement_id
AND movement_status in ('O','V')
AND edi_sent_flag = 'N';
FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Delete_Movement_Statistics');
END Delete_Movement_Statistics;
SELECT COUNT(*)
INTO l_count
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_rule_field_value;
SELECT COUNT(*)
INTO l_count1
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = l_form_vat_value;
UPDATE mtl_movement_statistics
SET customer_vat_number=l_form_vat_value
WHERE movement_id=p_mvt_transaction_rec.movement_id;
Select ATTRIBUTE_CODE
, ATTRIBUTE_PROPERTY_CODE
, ATTRIBUTE_LOOKUP_TYPE
FROM MTL_MVT_STATS_RULES
WHERE RULE_SET_CODE = p_movement_stat_usages_rec.Attribute_rule_set_code
AND SOURCE_TYPE = x_movement_transaction.document_source_type;
IF Rules_rec.Attribute_property_Code = 'REQUIRED_UPDATEABLE'
OR Rules_rec.Attribute_property_Code = 'REQUIRED_NON_UPDATEABLE'
THEN
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
THEN
FND_LOG.string(FND_LOG.LEVEL_STATEMENT
, G_MODULE_NAME || l_api_name
, 'Exception for '||Rules_rec.attribute_code
);
Select MEANING into lookup_value
from fnd_lookups
where lookup_type = rules_rec.attribute_lookup_type
and lookup_code = rule_field_value;
Select Attribute_Code
Into l_orig_mvmt_transaction.alternate_uom_code
From MTL_MVT_STATS_RULES
Where RULE_SET_CODE = p_movement_stat_usages_rec.Alt_Uom_Rule_Set_Code
And commodity_code = l_orig_mvmt_transaction.commodity_code;
SELECT
mtl_stats.movement_id
, mtl_stats.organization_id
, mtl_stats.entity_org_id
, mtl_stats.movement_type
, mtl_stats.movement_status
, mtl_stats.transaction_date
, mtl_stats.last_update_date
, mtl_stats.last_updated_by
, mtl_stats.creation_date
, mtl_stats.created_by
, mtl_stats.last_update_login
, mtl_stats.document_source_type
, mtl_stats.creation_method
, mtl_stats.document_reference
, mtl_stats.document_line_reference
, mtl_stats.document_unit_price
, mtl_stats.document_line_ext_value
, mtl_stats.receipt_reference
, mtl_stats.shipment_reference
, mtl_stats.shipment_line_reference
, mtl_stats.pick_slip_reference
, mtl_stats.customer_name
, mtl_stats.customer_number
, mtl_stats.customer_location
, mtl_stats.transacting_from_org
, mtl_stats.transacting_to_org
, mtl_stats.vendor_name
, mtl_stats.vendor_number
, mtl_stats.vendor_site
, mtl_stats.bill_to_name
, mtl_stats.bill_to_number
, mtl_stats.bill_to_site
, mtl_stats.po_header_id
, mtl_stats.po_line_id
, mtl_stats.po_line_location_id
, mtl_stats.order_header_id
, mtl_stats.order_line_id
, mtl_stats.picking_line_id
, mtl_stats.shipment_header_id
, mtl_stats.shipment_line_id
, mtl_stats.ship_to_customer_id
, mtl_stats.ship_to_site_use_id
, mtl_stats.bill_to_customer_id
, mtl_stats.bill_to_site_use_id
, mtl_stats.vendor_id
, mtl_stats.vendor_site_id
, mtl_stats.from_organization_id
, mtl_stats.to_organization_id
, mtl_stats.parent_movement_id
, mtl_stats.inventory_item_id
, mtl_stats.item_description
, mtl_stats.item_cost
, mtl_stats.transaction_quantity
, mtl_stats.transaction_uom_code
, mtl_stats.primary_quantity
, mtl_stats.invoice_batch_id
, mtl_stats.invoice_id
, mtl_stats.customer_trx_line_id
, mtl_stats.invoice_batch_reference
, mtl_stats.invoice_reference
, mtl_stats.invoice_line_reference
, mtl_stats.invoice_date_reference
, mtl_stats.invoice_quantity
, mtl_stats.invoice_unit_price
, mtl_stats.invoice_line_ext_value
, mtl_stats.outside_code
, mtl_stats.outside_ext_value
, mtl_stats.outside_unit_price
, mtl_stats.currency_code
, mtl_stats.currency_conversion_rate
, mtl_stats.currency_conversion_type
, mtl_stats.currency_conversion_date
, mtl_stats.period_name
, mtl_stats.report_reference
, mtl_stats.report_date
, mtl_stats.category_id
, mtl_stats.weight_method
, mtl_stats.unit_weight
, mtl_stats.total_weight
, mtl_stats.transaction_nature
, mtl_stats.delivery_terms
, mtl_stats.transport_mode
, mtl_stats.alternate_quantity
, mtl_stats.alternate_uom_code
, mtl_stats.dispatch_territory_code
, mtl_stats.destination_territory_code
, mtl_stats.origin_territory_code
, mtl_stats.stat_method
, mtl_stats.stat_adj_percent
, mtl_stats.stat_adj_amount
, mtl_stats.stat_ext_value
, mtl_stats.area
, mtl_stats.port
, mtl_stats.stat_type
, mtl_stats.comments
, mtl_stats.attribute_category
, mtl_stats.commodity_code
, mtl_stats.commodity_description
, mtl_stats.requisition_header_id
, mtl_stats.requisition_line_id
, mtl_stats.picking_line_detail_id
, mtl_stats.usage_type
, mtl_stats.zone_code
, mtl_stats.edi_sent_flag
, mtl_stats.statistical_procedure_code
, mtl_stats.movement_amount
, mtl_stats.triangulation_country_code
, mtl_stats.csa_code
, mtl_stats.oil_reference_code
, mtl_stats.container_type_code
, mtl_stats.flow_indicator_code
, mtl_stats.affiliation_reference_code
, mtl_stats.origin_territory_eu_code
, mtl_stats.destination_territory_eu_code
, mtl_stats.dispatch_territory_eu_code
, mtl_stats.set_of_books_period
, mtl_stats.taric_code
, mtl_stats.preference_code
, mtl_stats.rcv_transaction_id
, mtl_stats.mtl_transaction_id
, mtl_stats.total_weight_uom_code
, mtl_stats.financial_document_flag
, mtl_stats.customer_vat_number
, mtl_stats.attribute1
, mtl_stats.attribute2
, mtl_stats.attribute3
, mtl_stats.attribute4
, mtl_stats.attribute5
, mtl_stats.attribute6
, mtl_stats.attribute7
, mtl_stats.attribute8
, mtl_stats.attribute9
, mtl_stats.attribute10
, mtl_stats.attribute11
, mtl_stats.attribute12
, mtl_stats.attribute13
, mtl_stats.attribute14
, mtl_stats.attribute15
, mtl_stats.triangulation_country_eu_code
, mtl_stats.distribution_line_number
, mtl_stats.ship_to_name
, mtl_stats.ship_to_number
, mtl_stats.ship_to_site
, mtl_stats.edi_transaction_date
, mtl_stats.edi_transaction_reference
, mtl_stats.esl_drop_shipment_code
FROM
MTL_MOVEMENT_STATISTICS mtl_stats
WHERE entity_org_id = p_movement_transaction.entity_org_id
AND document_source_type = DECODE(p_transaction_type,null,
document_source_type,'ALL',document_source_type,p_transaction_type )
AND transaction_date BETWEEN trunc(p_start_date -1) and trunc(p_end_date +1)
AND movement_status in ('O','V')
AND financial_document_flag <> 'NOT_REQUIRED'
ORDER BY mtl_stats.movement_id;
SELECT
mtl_stats.movement_id
, mtl_stats.organization_id
, mtl_stats.entity_org_id
, mtl_stats.movement_type
, mtl_stats.movement_status
, mtl_stats.transaction_date
, mtl_stats.last_update_date
, mtl_stats.last_updated_by
, mtl_stats.creation_date
, mtl_stats.created_by
, mtl_stats.last_update_login
, mtl_stats.document_source_type
, mtl_stats.creation_method
, mtl_stats.document_reference
, mtl_stats.document_line_reference
, mtl_stats.document_unit_price
, mtl_stats.document_line_ext_value
, mtl_stats.receipt_reference
, mtl_stats.shipment_reference
, mtl_stats.shipment_line_reference
, mtl_stats.pick_slip_reference
, mtl_stats.customer_name
, mtl_stats.customer_number
, mtl_stats.customer_location
, mtl_stats.transacting_from_org
, mtl_stats.transacting_to_org
, mtl_stats.vendor_name
, mtl_stats.vendor_number
, mtl_stats.vendor_site
, mtl_stats.bill_to_name
, mtl_stats.bill_to_number
, mtl_stats.bill_to_site
, mtl_stats.po_header_id
, mtl_stats.po_line_id
, mtl_stats.po_line_location_id
, mtl_stats.order_header_id
, mtl_stats.order_line_id
, mtl_stats.picking_line_id
, mtl_stats.shipment_header_id
, mtl_stats.shipment_line_id
, mtl_stats.ship_to_customer_id
, mtl_stats.ship_to_site_use_id
, mtl_stats.bill_to_customer_id
, mtl_stats.bill_to_site_use_id
, mtl_stats.vendor_id
, mtl_stats.vendor_site_id
, mtl_stats.from_organization_id
, mtl_stats.to_organization_id
, mtl_stats.parent_movement_id
, mtl_stats.inventory_item_id
, mtl_stats.item_description
, mtl_stats.item_cost
, mtl_stats.transaction_quantity
, mtl_stats.transaction_uom_code
, mtl_stats.primary_quantity
, mtl_stats.invoice_batch_id
, mtl_stats.invoice_id
, mtl_stats.customer_trx_line_id
, mtl_stats.invoice_batch_reference
, mtl_stats.invoice_reference
, mtl_stats.invoice_line_reference
, mtl_stats.invoice_date_reference
, mtl_stats.invoice_quantity
, mtl_stats.invoice_unit_price
, mtl_stats.invoice_line_ext_value
, mtl_stats.outside_code
, mtl_stats.outside_ext_value
, mtl_stats.outside_unit_price
, mtl_stats.currency_code
, mtl_stats.currency_conversion_rate
, mtl_stats.currency_conversion_type
, mtl_stats.currency_conversion_date
, mtl_stats.period_name
, mtl_stats.report_reference
, mtl_stats.report_date
, mtl_stats.category_id
, mtl_stats.weight_method
, mtl_stats.unit_weight
, mtl_stats.total_weight
, mtl_stats.transaction_nature
, mtl_stats.delivery_terms
, mtl_stats.transport_mode
, mtl_stats.alternate_quantity
, mtl_stats.alternate_uom_code
, mtl_stats.dispatch_territory_code
, mtl_stats.destination_territory_code
, mtl_stats.origin_territory_code
, mtl_stats.stat_method
, mtl_stats.stat_adj_percent
, mtl_stats.stat_adj_amount
, mtl_stats.stat_ext_value
, mtl_stats.area
, mtl_stats.port
, mtl_stats.stat_type
, mtl_stats.comments
, mtl_stats.attribute_category
, mtl_stats.commodity_code
, mtl_stats.commodity_description
, mtl_stats.requisition_header_id
, mtl_stats.requisition_line_id
, mtl_stats.picking_line_detail_id
, mtl_stats.usage_type
, mtl_stats.zone_code
, mtl_stats.edi_sent_flag
, mtl_stats.statistical_procedure_code
, mtl_stats.movement_amount
, mtl_stats.triangulation_country_code
, mtl_stats.csa_code
, mtl_stats.oil_reference_code
, mtl_stats.container_type_code
, mtl_stats.flow_indicator_code
, mtl_stats.affiliation_reference_code
, mtl_stats.origin_territory_eu_code
, mtl_stats.destination_territory_eu_code
, mtl_stats.dispatch_territory_eu_code
, mtl_stats.set_of_books_period
, mtl_stats.taric_code
, mtl_stats.preference_code
, mtl_stats.rcv_transaction_id
, mtl_stats.mtl_transaction_id
, mtl_stats.total_weight_uom_code
, mtl_stats.financial_document_flag
, mtl_stats.customer_vat_number
, mtl_stats.attribute1
, mtl_stats.attribute2
, mtl_stats.attribute3
, mtl_stats.attribute4
, mtl_stats.attribute5
, mtl_stats.attribute6
, mtl_stats.attribute7
, mtl_stats.attribute8
, mtl_stats.attribute9
, mtl_stats.attribute10
, mtl_stats.attribute11
, mtl_stats.attribute12
, mtl_stats.attribute13
, mtl_stats.attribute14
, mtl_stats.attribute15
, mtl_stats.triangulation_country_eu_code
, mtl_stats.distribution_line_number
, mtl_stats.ship_to_name
, mtl_stats.ship_to_number
, mtl_stats.ship_to_site
, mtl_stats.edi_transaction_date
, mtl_stats.edi_transaction_reference
, mtl_stats.esl_drop_shipment_code
FROM
MTL_MOVEMENT_STATISTICS mtl_stats
WHERE entity_org_id = p_legal_entity_id
AND document_source_type IN ('PO', 'RTV')
AND movement_status IN ('O','V','P')
AND transaction_date BETWEEN TRUNC(p_start_date -1)
AND TRUNC(p_end_date +1)
AND rcv_transaction_id
IN (SELECT parent_transaction_id
FROM rcv_transactions
WHERE mvt_stat_status = 'NEW'
AND transaction_type = 'CORRECT')
ORDER BY mtl_stats.movement_id;
SELECT
mtl_stats.movement_id
, mtl_stats.organization_id
, mtl_stats.entity_org_id
, mtl_stats.movement_type
, mtl_stats.movement_status
, mtl_stats.transaction_date
, mtl_stats.last_update_date
, mtl_stats.last_updated_by
, mtl_stats.creation_date
, mtl_stats.created_by
, mtl_stats.last_update_login
, mtl_stats.document_source_type
, mtl_stats.creation_method
, mtl_stats.document_reference
, mtl_stats.document_line_reference
, mtl_stats.document_unit_price
, mtl_stats.document_line_ext_value
, mtl_stats.receipt_reference
, mtl_stats.shipment_reference
, mtl_stats.shipment_line_reference
, mtl_stats.pick_slip_reference
, mtl_stats.customer_name
, mtl_stats.customer_number
, mtl_stats.customer_location
, mtl_stats.transacting_from_org
, mtl_stats.transacting_to_org
, mtl_stats.vendor_name
, mtl_stats.vendor_number
, mtl_stats.vendor_site
, mtl_stats.bill_to_name
, mtl_stats.bill_to_number
, mtl_stats.bill_to_site
, mtl_stats.po_header_id
, mtl_stats.po_line_id
, mtl_stats.po_line_location_id
, mtl_stats.order_header_id
, mtl_stats.order_line_id
, mtl_stats.picking_line_id
, mtl_stats.shipment_header_id
, mtl_stats.shipment_line_id
, mtl_stats.ship_to_customer_id
, mtl_stats.ship_to_site_use_id
, mtl_stats.bill_to_customer_id
, mtl_stats.bill_to_site_use_id
, mtl_stats.vendor_id
, mtl_stats.vendor_site_id
, mtl_stats.from_organization_id
, mtl_stats.to_organization_id
, mtl_stats.parent_movement_id
, mtl_stats.inventory_item_id
, mtl_stats.item_description
, mtl_stats.item_cost
, mtl_stats.transaction_quantity
, mtl_stats.transaction_uom_code
, mtl_stats.primary_quantity
, mtl_stats.invoice_batch_id
, mtl_stats.invoice_id
, mtl_stats.customer_trx_line_id
, mtl_stats.invoice_batch_reference
, mtl_stats.invoice_reference
, mtl_stats.invoice_line_reference
, mtl_stats.invoice_date_reference
, mtl_stats.invoice_quantity
, mtl_stats.invoice_unit_price
, mtl_stats.invoice_line_ext_value
, mtl_stats.outside_code
, mtl_stats.outside_ext_value
, mtl_stats.outside_unit_price
, mtl_stats.currency_code
, mtl_stats.currency_conversion_rate
, mtl_stats.currency_conversion_type
, mtl_stats.currency_conversion_date
, mtl_stats.period_name
, mtl_stats.report_reference
, mtl_stats.report_date
, mtl_stats.category_id
, mtl_stats.weight_method
, mtl_stats.unit_weight
, mtl_stats.total_weight
, mtl_stats.transaction_nature
, mtl_stats.delivery_terms
, mtl_stats.transport_mode
, mtl_stats.alternate_quantity
, mtl_stats.alternate_uom_code
, mtl_stats.dispatch_territory_code
, mtl_stats.destination_territory_code
, mtl_stats.origin_territory_code
, mtl_stats.stat_method
, mtl_stats.stat_adj_percent
, mtl_stats.stat_adj_amount
, mtl_stats.stat_ext_value
, mtl_stats.area
, mtl_stats.port
, mtl_stats.stat_type
, mtl_stats.comments
, mtl_stats.attribute_category
, mtl_stats.commodity_code
, mtl_stats.commodity_description
, mtl_stats.requisition_header_id
, mtl_stats.requisition_line_id
, mtl_stats.picking_line_detail_id
, mtl_stats.usage_type
, mtl_stats.zone_code
, mtl_stats.edi_sent_flag
, mtl_stats.statistical_procedure_code
, mtl_stats.movement_amount
, mtl_stats.triangulation_country_code
, mtl_stats.csa_code
, mtl_stats.oil_reference_code
, mtl_stats.container_type_code
, mtl_stats.flow_indicator_code
, mtl_stats.affiliation_reference_code
, mtl_stats.origin_territory_eu_code
, mtl_stats.destination_territory_eu_code
, mtl_stats.dispatch_territory_eu_code
, mtl_stats.set_of_books_period
, mtl_stats.taric_code
, mtl_stats.preference_code
, mtl_stats.rcv_transaction_id
, mtl_stats.mtl_transaction_id
, mtl_stats.total_weight_uom_code
, mtl_stats.financial_document_flag
, mtl_stats.customer_vat_number
, mtl_stats.attribute1
, mtl_stats.attribute2
, mtl_stats.attribute3
, mtl_stats.attribute4
, mtl_stats.attribute5
, mtl_stats.attribute6
, mtl_stats.attribute7
, mtl_stats.attribute8
, mtl_stats.attribute9
, mtl_stats.attribute10
, mtl_stats.attribute11
, mtl_stats.attribute12
, mtl_stats.attribute13
, mtl_stats.attribute14
, mtl_stats.attribute15
, mtl_stats.triangulation_country_eu_code
, mtl_stats.distribution_line_number
, mtl_stats.ship_to_name
, mtl_stats.ship_to_number
, mtl_stats.ship_to_site
, mtl_stats.edi_transaction_date
, mtl_stats.edi_transaction_reference
, mtl_stats.esl_drop_shipment_code
FROM
MTL_MOVEMENT_STATISTICS mtl_stats
WHERE entity_org_id = p_movement_transaction.entity_org_id
AND document_source_type = DECODE(p_document_source_type,null,
document_source_type,'ALL',document_source_type,p_document_source_type )
AND movement_status = 'P'
ORDER BY
mtl_stats.movement_id;