[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT category_id
INTO X_category_id_v
FROM mtl_categories
WHERE sysdate < nvl(disable_date, sysdate + 1)
AND enabled_flag = 'Y'
AND sysdate between nvl(start_date_active, sysdate -1)
AND nvl(end_date_active, sysdate + 1)
AND category_id = X_category_id
AND structure_id = X_structure_id;
** selected organization.
*/
IF (X_item_revision IS NULL) THEN
SELECT MAX('Y') /*'item valid in the defaulted ship org'*/
INTO X_item_valid
FROM mtl_system_items msi
WHERE msi.inventory_item_id = X_item_id
AND msi.organization_id = X_master_ship_org_id
AND msi.purchasing_enabled_flag = 'Y'
AND ( ( X_outside_operation_flag = 'Y'
AND nvl(msi.outside_operation_flag,'N') = 'Y')
OR X_outside_operation_flag = 'N'
);
** valid for the selected organization.
*/
SELECT MAX('Y')
INTO X_item_valid
FROM mtl_system_items msi,
mtl_item_revisions mir
WHERE mir.organization_id = X_master_ship_org_id
AND mir.revision = X_item_revision
AND mir.inventory_item_id = X_item_id
AND msi.inventory_item_id = X_item_id
AND msi.organization_id = X_master_ship_org_id
AND msi.purchasing_enabled_flag = 'Y'
AND ( ( X_outside_operation_flag = 'Y'
AND nvl(msi.outside_operation_flag,'N') = 'Y')
OR X_outside_operation_flag = 'N'
);
X_allow_item_desc_update_flag IN OUT NOCOPY VARCHAR2,
X_allowed_units_lookup_code IN OUT NOCOPY NUMBER,
X_primary_unit_class IN OUT NOCOPY VARCHAR2,
X_rfq_required_flag IN OUT NOCOPY VARCHAR2,
X_un_number_id IN OUT NOCOPY NUMBER,
X_hazard_class_id IN OUT NOCOPY NUMBER,
X_inv_planned_item_flag IN OUT NOCOPY VARCHAR2,
X_mrp_planned_item_flag IN OUT NOCOPY VARCHAR2,
X_planned_item_flag IN OUT NOCOPY VARCHAR2,
X_taxable_flag IN OUT NOCOPY VARCHAR2,
X_market_price IN OUT NOCOPY NUMBER,
X_invoice_close_tolerance IN OUT NOCOPY NUMBER,
X_receive_close_tolerance IN OUT NOCOPY NUMBER,
X_receipt_required_flag IN OUT NOCOPY VARCHAR2,
X_restrict_subinventories_code IN OUT NOCOPY NUMBER,
X_hazard_class IN OUT NOCOPY VARCHAR2,
X_un_number IN OUT NOCOPY VARCHAR2,
X_stock_enabled_flag IN OUT NOCOPY VARCHAR2,
X_outside_operation_flag IN OUT NOCOPY VARCHAR2,
--
X_secondary_default_ind IN OUT NOCOPY VARCHAR2,
X_grade_control_flag IN OUT NOCOPY VARCHAR2,
X_secondary_unit_of_measure IN OUT NOCOPY VARCHAR2
--
) IS
x_progress VARCHAR2(3) := '';
X_allow_item_desc_update_flag,
X_allowed_units_lookup_code,
X_primary_unit_class,
X_rfq_required_flag,
X_un_number_id,
X_hazard_class_id,
X_inv_planned_item_flag,
X_mrp_planned_item_flag,
X_planned_item_flag,
X_taxable_flag,
X_market_price,
X_invoice_close_tolerance,
X_receive_close_tolerance,
X_receipt_required_flag,
X_restrict_subinventories_code,
X_stock_enabled_flag,
X_outside_operation_flag,
--
X_secondary_default_ind,
X_grade_control_flag,
X_secondary_unit_of_measure
--
);
X_allow_item_desc_update_flag IN OUT NOCOPY VARCHAR2,
X_allowed_units_lookup_code IN OUT NOCOPY NUMBER,
X_primary_unit_class IN OUT NOCOPY VARCHAR2,
X_rfq_required_flag IN OUT NOCOPY VARCHAR2,
X_un_number_id IN OUT NOCOPY NUMBER,
X_hazard_class_id IN OUT NOCOPY NUMBER,
X_inv_planned_item_flag IN OUT NOCOPY VARCHAR2,
X_mrp_planned_item_flag IN OUT NOCOPY VARCHAR2,
X_planned_item_flag IN OUT NOCOPY VARCHAR2,
X_taxable_flag IN OUT NOCOPY VARCHAR2,
X_market_price IN OUT NOCOPY NUMBER,
X_invoice_close_tolerance IN OUT NOCOPY NUMBER,
X_receive_close_tolerance IN OUT NOCOPY NUMBER,
X_receipt_required_flag IN OUT NOCOPY VARCHAR2,
X_restrict_subinventories_code IN OUT NOCOPY NUMBER,
X_stock_enabled_flag IN OUT NOCOPY VARCHAR2,
X_outside_operation_flag IN OUT NOCOPY VARCHAR2,
--
X_secondary_default_ind IN OUT NOCOPY VARCHAR2,
X_grade_control_flag IN OUT NOCOPY VARCHAR2,
X_secondary_unit_of_measure IN OUT NOCOPY VARCHAR2
--
) IS
x_progress VARCHAR2(3) := NULL;
** a different SELECT statement is executed.
*/
/* BUG: 656428 - Added to_char conversion to the mrp_planning_code
** and the inventory_planning_code to avoid value errors.
*/
IF (X_type_lookup_code IN ('INTERNAL', 'PURCHASE')) THEN
x_progress := '020';
SELECT msi.description,
decode(X_source_type_lookup_code, 'INVENTORY',
nvl(msi.unit_of_issue, msi.primary_unit_of_measure),
msi.primary_unit_of_measure),
msi.list_price_per_unit,
mic.category_id,
msi.purchasing_enabled_flag,
msi.internal_order_enabled_flag,
msi.outside_operation_uom_type,
msi.inventory_asset_flag,
msi.allow_item_desc_update_flag,
msi.allowed_units_lookup_code,
mum.uom_class,
nvl(msi.rfq_required_flag, X_rfq_required_flag),
nvl(msi.un_number_id, X_un_number_id),
nvl(msi.hazard_class_id, X_hazard_class_id),
decode(to_char(msi.inventory_planning_code),
NULL,'N',
'6', 'N',
'Y'),
decode(to_char(msi.mrp_planning_code),
NULL,'N',
'6', 'N',
'Y'),
nvl(msi.stock_enabled_flag,'N'),
nvl(msi.outside_operation_flag,'N'),
--
decode(msi.tracking_quantity_ind,
g_chktype_TRACKING_QTY_IND_S,msi.secondary_default_ind,NULL),
msi.grade_control_flag,
decode(msi.tracking_quantity_ind,
g_chktype_TRACKING_QTY_IND_S,mum2.unit_of_measure,NULL)
--
INTO X_item_description,
X_unit_meas_lookup_code,
X_unit_price,
X_category_id,
X_purchasing_enabled_flag,
X_internal_order_enabled_flag,
X_outside_op_uom_type,
X_inventory_asset_flag,
X_allow_item_desc_update_flag,
X_allowed_units_lookup_code,
X_primary_unit_class,
X_rfq_required_flag,
X_un_number_id,
X_hazard_class_id,
X_inv_planned_item_flag,
X_mrp_planned_item_flag,
X_stock_enabled_flag,
X_outside_operation_flag,
--
X_secondary_default_ind,
X_grade_control_flag ,
X_secondary_unit_of_measure
--
FROM mtl_units_of_measure mum,
mtl_item_categories mic,
mtl_system_items msi,
mtl_parameters mpa,
mtl_units_of_measure mum2 --
WHERE mic.inventory_item_id = X_item_id
AND mic.category_set_id = X_category_set_id
AND mic.organization_id = X_inventory_organization_id
AND msi.organization_id = X_inventory_organization_id
AND msi.inventory_item_id = X_item_id
AND mum.unit_of_measure =
decode(X_source_type_lookup_code,'INVENTORY',
nvl(msi.unit_of_issue, msi.primary_unit_of_measure),
msi.primary_unit_of_measure)
AND mpa.organization_id = X_inventory_organization_id
AND msi.secondary_uom_code = mum2.uom_code(+) ; --
** If document is NOT a requisition, perform a different SELECT
** (this SELECT doesn't take into consideration the source type)
*/
ELSE
x_progress := '040';
SELECT mic.category_id,
decode(msi.mrp_planning_code, 3, 'Y', 4, 'Y', 7, 'Y', 8, 'Y', 9, 'Y',
decode(msi.inventory_planning_code,1,'Y',2,'Y', 'N')),
msi.description,
msi.list_price_per_unit,
msi.market_price,
msi.taxable_flag,
msi.allow_item_desc_update_flag,
msi.allowed_units_lookup_code,
msi.primary_unit_of_measure,
mum.uom_class,
msi.un_number_id,
msi.hazard_class_id,
msi.outside_operation_uom_type,
nvl(msi.invoice_close_tolerance, X_invoice_close_tolerance),
nvl(msi.receive_close_tolerance, X_receive_close_tolerance),
nvl(msi.receipt_required_flag, X_receipt_required_flag),
msi.restrict_subinventories_code,
--
decode(msi.tracking_quantity_ind,
g_chktype_TRACKING_QTY_IND_S,msi.secondary_default_ind,NULL),
msi.grade_control_flag,
decode(msi.tracking_quantity_ind,
g_chktype_TRACKING_QTY_IND_S,mum2.unit_of_measure,NULL)
--
INTO X_category_id,
X_planned_item_flag,
X_item_description,
X_unit_price,
X_market_price,
X_taxable_flag,
X_allow_item_desc_update_flag,
X_allowed_units_lookup_code,
X_unit_meas_lookup_code,
X_primary_unit_class,
X_un_number_id,
X_hazard_class_id,
X_outside_op_uom_type,
X_invoice_close_tolerance,
X_receive_close_tolerance,
X_receipt_required_flag,
X_restrict_subinventories_code,
--
X_secondary_default_ind,
X_grade_control_flag ,
X_secondary_unit_of_measure
--
FROM mtl_units_of_measure mum,
mtl_item_categories mic,
mtl_system_items msi,
mtl_units_of_measure mum2 --
WHERE msi.inventory_item_id = X_item_id
AND mic.inventory_item_id = X_item_id
AND mic.category_set_id = X_category_set_id
AND mic.organization_id = X_inventory_organization_id
AND msi.organization_id = X_inventory_organization_id
AND msi.primary_unit_of_measure = mum.unit_of_measure
AND msi.secondary_uom_code = mum2.uom_code(+) ; --
** mtl_system_items. So the following SELECT statement
** is added to get the description from mtl_system_items_tl.
*/
SELECT description
INTO X_item_description
FROM mtl_system_items_tl
WHERE inventory_item_id = X_item_id
AND language = USERENV('LANG')
AND organization_id = X_inventory_organization_id;
SELECT hazard_class
INTO X_hazard_class
FROM po_hazard_classes
WHERE hazard_class_id = X_hazard_class_id;
SELECT poun.un_number
INTO X_un_number
FROM po_un_numbers poun
WHERE poun.un_number_id = X_un_number_id;
SELECT MAX('Y')
INTO X_revision_is_valid
FROM mtl_item_revisions mir
WHERE mir.organization_id = X_destination_org_id
AND mir.revision = X_item_revision
AND mir.inventory_item_id = X_item_id;
SELECT organization_name
INTO X_destination_org_name
FROM org_organization_definitions
WHERE organization_id = X_destination_org_id;
SELECT decode(msi.tracking_quantity_ind,
g_chktype_TRACKING_QTY_IND_S,msi.secondary_default_ind,NULL),
msi.grade_control_flag,
decode(msi.tracking_quantity_ind,
g_chktype_TRACKING_QTY_IND_S,mum.unit_of_measure,NULL)
INTO X_secondary_default_ind, X_grade_control_flag, X_secondary_unit_of_measure
FROM mtl_units_of_measure mum, mtl_system_items msi
WHERE msi.organization_id = X_inventory_organization_id
AND msi.inventory_item_id = X_item_id
AND mum.uom_code(+) = msi.secondary_uom_code ;
SELECT decode(msi.tracking_quantity_ind,
g_chktype_TRACKING_QTY_IND_S,msi.secondary_default_ind,NULL),
msi.grade_control_flag
INTO X_secondary_default_ind, X_grade_control_flag
FROM mtl_system_items msi
WHERE msi.organization_id = X_inventory_organization_id
AND msi.inventory_item_id = X_item_id ;
SELECT type_lookup_code INTO l_doc_type
FROM po_headers_all
WHERE po_header_id = (SELECT po_header_id
FROM po_lines_merge_v
WHERE po_line_id = p_po_line_id
AND draft_id = p_draft_id);
SELECT 1
INTO l_dummy
FROM DUAL
WHERE EXISTS
(SELECT MIR.revision
FROM mtl_item_revisions MIR,
org_organization_definitions OOD
WHERE OOD.set_of_books_id = p_sob_id
AND SYSDATE < NVL(OOD.disable_date, SYSDATE + 1)
AND MIR.organization_id = OOD.organization_id
AND MIR.inventory_item_id = p_item_id);
INSERT INTO po_session_gt
( key,
char1,
num1
)
SELECT l_key,
MIR.revision,
count(*)
FROM po_line_locations_all PLL,
mtl_item_revisions MIR,
org_organization_definitions OOD
WHERE PLL.po_line_id = p_po_line_id
AND NVL(PLL.cancel_flag, 'N') = 'N'
AND MIR.inventory_item_id = p_item_id
AND OOD.set_of_books_id = p_sob_id
AND SYSDATE < NVL (OOD.disable_date, SYSDATE + 1)
AND PLL.ship_to_organization_id = OOD.organization_id
AND PLL.ship_to_organization_id = MIR.organization_id
GROUP BY MIR.revision;
SELECT count(*)
INTO l_shipment_count
FROM po_line_locations_merge_v
WHERE po_line_id = p_po_line_id
AND draft_id = p_draft_id
AND NVL(cancel_flag, 'N') = 'N';
SELECT 1
INTO l_dummy
FROM DUAL
WHERE EXISTS
( SELECT 1
FROM po_session_gt
WHERE key = l_key
AND num1 = l_shipment_count );
DELETE FROM po_session_gt WHERE key = l_key;