[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name
INTO l_org_name
FROM hr_all_organization_units_vl
WHERE organization_id = p_organization_id;
/* Variable required for the validation for UPDATES */
/******************************************************/
loc_ctrl_code NUMBER;
select
ROWID,
TRANSACTION_ID,
ORGANIZATION_ID,
TRANSACTION_TYPE,
PROCESS_FLAG,
INVENTORY_ITEM_ID,
SUMMARY_FLAG,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
DESCRIPTION,
BUYER_ID,
ACCOUNTING_RULE_ID,
INVOICING_RULE_ID,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15,
SEGMENT16,
SEGMENT17,
SEGMENT18,
SEGMENT19,
SEGMENT20,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PURCHASING_ITEM_FLAG,
SHIPPABLE_ITEM_FLAG,
CUSTOMER_ORDER_FLAG,
INTERNAL_ORDER_FLAG,
SERVICE_ITEM_FLAG,
INVENTORY_ITEM_FLAG,
ENG_ITEM_FLAG,
INVENTORY_ASSET_FLAG,
PURCHASING_ENABLED_FLAG,
CUSTOMER_ORDER_ENABLED_FLAG,
INTERNAL_ORDER_ENABLED_FLAG,
SO_TRANSACTIONS_FLAG,
MTL_TRANSACTIONS_ENABLED_FLAG,
STOCK_ENABLED_FLAG,
BOM_ENABLED_FLAG,
BUILD_IN_WIP_FLAG,
REVISION_QTY_CONTROL_CODE,
ITEM_CATALOG_GROUP_ID,
CATALOG_STATUS_FLAG,
RETURNABLE_FLAG,
DEFAULT_SHIPPING_ORG,
COLLATERAL_FLAG,
TAXABLE_FLAG,
QTY_RCV_EXCEPTION_CODE,
ALLOW_ITEM_DESC_UPDATE_FLAG,
INSPECTION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
MARKET_PRICE,
HAZARD_CLASS_ID,
RFQ_REQUIRED_FLAG,
QTY_RCV_TOLERANCE,
LIST_PRICE_PER_UNIT,
UN_NUMBER_ID,
PRICE_TOLERANCE_PERCENT,
ASSET_CATEGORY_ID,
ROUNDING_FACTOR,
UNIT_OF_ISSUE,
ENFORCE_SHIP_TO_LOCATION_CODE,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
ALLOW_UNORDERED_RECEIPTS_FLAG,
ALLOW_EXPRESS_DELIVERY_FLAG,
DAYS_EARLY_RECEIPT_ALLOWED,
DAYS_LATE_RECEIPT_ALLOWED,
RECEIPT_DAYS_EXCEPTION_CODE,
RECEIVING_ROUTING_ID,
INVOICE_CLOSE_TOLERANCE,
RECEIVE_CLOSE_TOLERANCE,
AUTO_LOT_ALPHA_PREFIX,
START_AUTO_LOT_NUMBER,
LOT_CONTROL_CODE,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
SERIAL_NUMBER_CONTROL_CODE,
START_AUTO_SERIAL_NUMBER,
AUTO_SERIAL_ALPHA_PREFIX,
SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY,
EXPENSE_ACCOUNT,
ENCUMBRANCE_ACCOUNT,
RESTRICT_SUBINVENTORIES_CODE,
UNIT_WEIGHT,
WEIGHT_UOM_CODE,
VOLUME_UOM_CODE,
UNIT_VOLUME,
DIMENSION_UOM_CODE,
UNIT_LENGTH,
UNIT_WIDTH,
UNIT_HEIGHT,
RESTRICT_LOCATORS_CODE,
LOCATION_CONTROL_CODE,
SHRINKAGE_RATE,
ACCEPTABLE_EARLY_DAYS,
PLANNING_TIME_FENCE_CODE,
DEMAND_TIME_FENCE_CODE,
LEAD_TIME_LOT_SIZE,
STD_LOT_SIZE,
CUM_MANUFACTURING_LEAD_TIME,
OVERRUN_PERCENTAGE,
MRP_CALCULATE_ATP_FLAG,
ACCEPTABLE_RATE_INCREASE,
ACCEPTABLE_RATE_DECREASE,
CUMULATIVE_TOTAL_LEAD_TIME,
PLANNING_TIME_FENCE_DAYS,
DEMAND_TIME_FENCE_DAYS,
END_ASSEMBLY_PEGGING_FLAG,
REPETITIVE_PLANNING_FLAG,
PLANNING_EXCEPTION_SET,
BOM_ITEM_TYPE,
PICK_COMPONENTS_FLAG,
REPLENISH_TO_ORDER_FLAG,
BASE_ITEM_ID,
ATP_COMPONENTS_FLAG,
ATP_FLAG,
FIXED_LEAD_TIME,
VARIABLE_LEAD_TIME,
WIP_SUPPLY_LOCATOR_ID,
WIP_SUPPLY_TYPE,
WIP_SUPPLY_SUBINVENTORY,
PRIMARY_UOM_CODE,
PRIMARY_UNIT_OF_MEASURE,
ALLOWED_UNITS_LOOKUP_CODE,
COST_OF_SALES_ACCOUNT,
SALES_ACCOUNT,
DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
INVENTORY_ITEM_STATUS_CODE,
INVENTORY_PLANNING_CODE,
PLANNER_CODE,
PLANNING_MAKE_BUY_CODE,
FIXED_LOT_MULTIPLIER,
ROUNDING_CONTROL_TYPE,
CARRYING_COST,
POSTPROCESSING_LEAD_TIME,
PREPROCESSING_LEAD_TIME,
FULL_LEAD_TIME,
ORDER_COST,
MRP_SAFETY_STOCK_PERCENT,
MRP_SAFETY_STOCK_CODE,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
MINIMUM_ORDER_QUANTITY,
FIXED_ORDER_QUANTITY,
FIXED_DAYS_SUPPLY,
MAXIMUM_ORDER_QUANTITY,
ATP_RULE_ID,
PICKING_RULE_ID,
RESERVABLE_TYPE,
POSITIVE_MEASUREMENT_ERROR,
NEGATIVE_MEASUREMENT_ERROR,
ENGINEERING_ECN_CODE,
ENGINEERING_ITEM_ID,
ENGINEERING_DATE,
SERVICE_STARTING_DELAY,
VENDOR_WARRANTY_FLAG,
SERVICEABLE_COMPONENT_FLAG,
SERVICEABLE_PRODUCT_FLAG,
BASE_WARRANTY_SERVICE_ID,
PAYMENT_TERMS_ID,
PREVENTIVE_MAINTENANCE_FLAG,
PRIMARY_SPECIALIST_ID,
SECONDARY_SPECIALIST_ID,
SERVICEABLE_ITEM_CLASS_ID,
TIME_BILLABLE_FLAG,
MATERIAL_BILLABLE_FLAG,
EXPENSE_BILLABLE_FLAG,
PRORATE_SERVICE_FLAG,
COVERAGE_SCHEDULE_ID,
SERVICE_DURATION_PERIOD_CODE,
SERVICE_DURATION,
WARRANTY_VENDOR_ID,
MAX_WARRANTY_AMOUNT,
RESPONSE_TIME_PERIOD_CODE,
RESPONSE_TIME_VALUE,
NEW_REVISION_CODE,
INVOICEABLE_ITEM_FLAG,
TAX_CODE,
INVOICE_ENABLED_FLAG,
MUST_USE_APPROVED_VENDOR_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OUTSIDE_OPERATION_FLAG,
OUTSIDE_OPERATION_UOM_TYPE,
SAFETY_STOCK_BUCKET_DAYS,
AUTO_REDUCE_MPS,
COSTING_ENABLED_FLAG,
CYCLE_COUNT_ENABLED_FLAG,
AUTO_CREATED_CONFIG_FLAG,
ITEM_TYPE,
MODEL_CONFIG_CLAUSE_NAME,
SHIP_MODEL_COMPLETE_FLAG,
MRP_PLANNING_CODE,
RETURN_INSPECTION_REQUIREMENT,
ATO_FORECAST_CONTROL,
RELEASE_TIME_FENCE_CODE, /*NP 19AUG96 Eight cols added for 10.7 */
RELEASE_TIME_FENCE_DAYS,
CONTAINER_ITEM_FLAG,
CONTAINER_TYPE_CODE,
INTERNAL_VOLUME,
MAXIMUM_LOAD_WEIGHT,
MINIMUM_FILL_PERCENT,
VEHICLE_ITEM_FLAG,
SERV_REQ_ENABLED_CODE,
SERV_BILLING_ENABLED_FLAG,
-- SERV_IMPORTANCE_LEVEL,
PLANNED_INV_POINT_FLAG
-- Added for 11.5.10
, TRACKING_QUANTITY_IND
, ONT_PRICING_QTY_SOURCE
, SECONDARY_DEFAULT_IND
, SECONDARY_UOM_CODE
, DUAL_UOM_DEVIATION_HIGH
, DUAL_UOM_DEVIATION_LOW
, CONTRACT_ITEM_TYPE_CODE
, RECOVERED_PART_DISP_CODE
from MTL_SYSTEM_ITEMS_INTERFACE
-- Replacing organization_id + 0 with organization_id - Anmurali - Bug 4175124
where ((organization_id = org_id) or
(all_Org = 1))
and set_process_id = xset_id
and process_flag in (31, 32, 33, 34, 44);
SELECT stock_locator_control_code,negative_inv_receipt_code
FROM mtl_parameters
where organization_id = cp_org_id;
SELECT locator_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = cp_subinv_name
AND organization_id = cp_org_id
AND SYSDATE < nvl(disable_date, SYSDATE+1);
SELECT enabled_flag
FROM FND_COMMON_LOOKUPS
WHERE lookup_type = cp_lookup_type
AND lookup_code = cp_lookup_code;
SELECT 'x'
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = cp_lookup_type
AND LOOKUP_CODE = cp_lookup_code
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
AND ENABLED_FLAG = 'Y';
select inventory_item_id
into l_item_id
from mtl_system_items_interface
where transaction_id = cr.transaction_id
and set_process_id = xset_id;
SELECT lgr.CHART_OF_ACCOUNTS_ID into chart_of_acc_id
FROM gl_ledgers lgr,
hr_organization_information hoi
where hoi.organization_id = cr.organization_id
and (HOI.ORG_INFORMATION_CONTEXT|| '') ='Accounting Information'
and TO_NUMBER(DECODE(RTRIM(TRANSLATE(HOI.ORG_INFORMATION1,'0123456789',' ')), NULL, HOI.ORG_INFORMATION1,-99999)) = LGR.LEDGER_ID
and lgr.object_type_code = 'L'
and rownum = 1;
select 'x' into temp
from GL_CODE_COMBINATIONS
where CODE_COMBINATION_ID = cr.COST_OF_SALES_ACCOUNT
and CHART_OF_ACCOUNTS_ID = chart_of_acc_id
and nvl(START_DATE_ACTIVE,sysdate) <= sysdate
and nvl(END_DATE_ACTIVE,sysdate) >= sysdate
and DETAIL_POSTING_ALLOWED_FLAG = 'Y' ; --* Added for bug #4229090 - Anmurali
select 'x' into temp
from GL_CODE_COMBINATIONS
where CODE_COMBINATION_ID = cr.SALES_ACCOUNT
and CHART_OF_ACCOUNTS_ID = chart_of_acc_id
and nvl(START_DATE_ACTIVE,sysdate) <= sysdate
and nvl(END_DATE_ACTIVE,sysdate) >= sysdate
and DETAIL_POSTING_ALLOWED_FLAG = 'Y' ; --* Added for bug #4229090 - Anmurali
select 'x' into temp
from GL_CODE_COMBINATIONS
where CODE_COMBINATION_ID = cr.EXPENSE_ACCOUNT
and CHART_OF_ACCOUNTS_ID = chart_of_acc_id
and nvl(START_DATE_ACTIVE,sysdate) <= sysdate
and nvl(END_DATE_ACTIVE,sysdate) >= sysdate
and DETAIL_POSTING_ALLOWED_FLAG = 'Y' ; --* Added for bug #4229090 -Anmurali
select 'x' into temp
from GL_CODE_COMBINATIONS
where CODE_COMBINATION_ID = cr.ENCUMBRANCE_ACCOUNT
and CHART_OF_ACCOUNTS_ID = chart_of_acc_id
and nvl(START_DATE_ACTIVE,sysdate) <= sysdate
and nvl(END_DATE_ACTIVE,sysdate) >= sysdate
and DETAIL_POSTING_ALLOWED_FLAG = 'Y' ; --* Added for bug #4229090 -Anmurali
select 'x' into temp
from MTL_PICKING_RULES
where PICKING_RULE_ID = cr.PICKING_RULE_ID;
select 'x' into temp
from MTL_ITEM_STATUS
where INVENTORY_ITEM_STATUS_CODE = cr.INVENTORY_ITEM_STATUS_CODE
and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
select 'x' into temp
from MTL_SYSTEM_ITEMS
where INVENTORY_ITEM_ID = cr.ENGINEERING_ITEM_ID
and ORGANIZATION_ID = cr.ORGANIZATION_ID;
/*Fix for bug 4564472 - Since primary_unit_of_measure and primary_uom_code cannot be updated it is sufficient
to make the below uom related validations for create txn only.
Added below If condition to check for TRANSACTION_TYPE=CREATE. */
IF cr.TRANSACTION_TYPE='CREATE' THEN
IF cr.PRIMARY_UNIT_OF_MEASURE IS NOT NULL THEN
BEGIN
select UOM_CODE into temp_uom_code
from MTL_UNITS_OF_MEASURE
where UNIT_OF_MEASURE = cr.PRIMARY_UNIT_OF_MEASURE /*Bug 5192495*/
and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
select UNIT_OF_MEASURE into temp_u_o_m /*Bug 5192495*/
from MTL_UNITS_OF_MEASURE
where UOM_CODE = cr.PRIMARY_UOM_CODE
and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
select UOM_CLASS into temp_uom_class
from MTL_UNITS_OF_MEASURE
where UOM_CODE = cr.PRIMARY_UOM_CODE; --* Modified for Bug 4366615
select 'x' into temp
from MTL_SYSTEM_ITEMS msi
where INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
and ORGANIZATION_ID in
( select MASTER_ORGANIZATION_ID
from MTL_PARAMETERS
where ORGANIZATION_ID = MASTER_ORGANIZATION_ID )
and rownum = 1
and not exists
(select UNIT_OF_MEASURE
from MTL_UNITS_OF_MEASURE MUOM
where UOM_CLASS = temp_uom_class
and msi.PRIMARY_UOM_CODE
= MUOM.UOM_CODE);
select BASE_UOM_FLAG
into temp_base_uom_flag
from MTL_UNITS_OF_MEASURE
where UOM_CODE = cr.PRIMARY_UOM_CODE; --* Modified for Bug 4366615
select 'x'
into temp
from mtl_uom_conversions
where INVENTORY_ITEM_ID = 0
and UOM_CODE = cr.PRIMARY_UOM_CODE;
select 'x' into temp
from MTL_UNITS_OF_MEASURE
where UOM_CODE = cr.VOLUME_UOM_CODE;
/* R12 C Unit Weight can now be updated for Pending items. Moving the below set of validations to INVPVHDR
if cr.WEIGHT_UOM_CODE is not null then
begin
select 'x' into temp
from MTL_UNITS_OF_MEASURE
where UOM_CODE = cr.WEIGHT_UOM_CODE;
select 'x' into temp
from mtl_item_catalog_groups
where item_catalog_group_id = cr.ITEM_CATALOG_GROUP_ID
and item_creation_allowed_flag = 'Y'
and NVL(inactive_date,sysdate+1) > sysdate;
ELSIF cr.transaction_type ='UPDATE' THEN
SELECT item_catalog_group_id
INTO l_old_catalog_id
FROM mtl_system_items_b
WHERE inventory_item_id = cr.inventory_item_id
AND organization_id = cr.organization_id;
SELECT 'x' into temp
FROM mtl_item_catalog_groups_b
WHERE item_catalog_group_id = cr.item_catalog_group_id
AND item_creation_allowed_flag = 'Y'
AND item_catalog_group_id IN
(SELECT ICG.item_catalog_group_id
FROM mtl_item_catalog_groups_b ICG
WHERE ICG.item_creation_allowed_flag = 'Y'
AND ((ICG.inactive_date IS NULL) OR (TRUNC(ICG.inactive_date) > TRUNC(SYSDATE))));
SELECT 'x' into temp
FROM mtl_item_catalog_groups_b
WHERE item_catalog_group_id = cr.item_catalog_group_id
AND item_creation_allowed_flag = 'Y'
AND item_catalog_group_id IN
(SELECT ICG.item_catalog_group_id
FROM mtl_item_catalog_groups_b ICG
WHERE ICG.item_creation_allowed_flag = 'Y'
--Bug: 2805253 Removed NVL
AND ((ICG.inactive_date IS NULL) OR (TRUNC(ICG.inactive_date) > TRUNC(SYSDATE)))
CONNECT BY PRIOR ICG.item_catalog_group_id = ICG.parent_catalog_group_id
START WITH ICG.item_catalog_group_id = l_old_catalog_id);
SELECT 'x' into temp
FROM mtl_item_catalog_groups_b
WHERE item_catalog_group_id = cr.item_catalog_group_id
AND item_creation_allowed_flag = 'Y'
AND (inactive_date IS NULL OR TRUNC(inactive_date) > TRUNC(SYSDATE));
select restrict_subinventories_code
into l_source_restrict_sub
from mtl_system_items_b
where organization_id = cr.SOURCE_ORGANIZATION_ID
and inventory_item_id = cr.INVENTORY_ITEM_ID;
select 'x' into temp
from MTL_SECONDARY_INVENTORIES s, MTL_ITEM_SUB_INVENTORIES i
where s.SECONDARY_INVENTORY_NAME = cr.SOURCE_SUBINVENTORY
and s.ORGANIZATION_ID = cr.SOURCE_ORGANIZATION_ID
and nvl(s.DISABLE_DATE, sysdate + 1 ) > sysdate
and i.INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
and i.ORGANIZATION_ID = cr.SOURCE_ORGANIZATION_ID
and i.SECONDARY_INVENTORY = s.SECONDARY_INVENTORY_NAME
and 2 = decode(cr.source_organization_id, cr.organization_id,
decode(cr.mrp_planning_code, 3, availability_type, 7, availability_type, 9, availability_type, 2), 2);
select 'x' into temp
from MTL_SECONDARY_INVENTORIES
where SECONDARY_INVENTORY_NAME = cr.SOURCE_SUBINVENTORY
and ORGANIZATION_ID = cr.SOURCE_ORGANIZATION_ID
and nvl(DISABLE_DATE, sysdate + 1 ) > sysdate
and 2 = decode(cr.source_organization_id, cr.organization_id,
decode(cr.mrp_planning_code, 3, availability_type, 7, availability_type, 9, availability_type, 2), 2);
select 'x' into temp
from PO_HAZARD_CLASSES
where HAZARD_CLASS_ID = cr.HAZARD_CLASS_ID
and (INACTIVE_DATE is null or INACTIVE_DATE > sysdate);
select 'x' into temp
from PO_UN_NUMBERS
where UN_NUMBER_ID = cr.UN_NUMBER_ID;
select 'x' into temp
from FA_CATEGORIES
where CATEGORY_ID = cr.ASSET_CATEGORY_ID;
select 'x' into temp
from MTL_SYSTEM_ITEMS
where INVENTORY_ITEM_ID = cr.BASE_ITEM_ID
and ORGANIZATION_ID = cr.ORGANIZATION_ID;
ELSIF cr.RESTRICT_SUBINVENTORIES_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' THEN
BEGIN
SELECT 'x' INTO temp
FROM MTL_ITEM_SUB_INVENTORIES i
WHERE i.inventory_item_id = cr.inventory_item_id
AND i.ORGANIZATION_ID = cr.ORGANIZATION_ID
AND i.SECONDARY_INVENTORY = cr.WIP_SUPPLY_SUBINVENTORY;
SELECT 'x' INTO temp
FROM MTL_SECONDARY_INVENTORIES
WHERE SECONDARY_INVENTORY_NAME = cr.WIP_SUPPLY_SUBINVENTORY
AND ORGANIZATION_ID = cr.ORGANIZATION_ID
AND SYSDATE < nvl(DISABLE_DATE, SYSDATE+1);
ELSIF cr.RESTRICT_LOCATORS_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' THEN
BEGIN
SELECT 'x' INTO temp
FROM MTL_SECONDARY_LOCATORS
WHERE INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = cr.ORGANIZATION_ID
AND SECONDARY_LOCATOR = cr.WIP_SUPPLY_LOCATOR_ID
AND SUBINVENTORY_CODE = cr.WIP_SUPPLY_SUBINVENTORY;
SELECT 'x' INTO temp
FROM MTL_ITEM_LOCATIONS
WHERE INVENTORY_LOCATION_ID = cr.WIP_SUPPLY_LOCATOR_ID
AND SUBINVENTORY_CODE = cr.WIP_SUPPLY_SUBINVENTORY
AND ORGANIZATION_ID = cr.ORGANIZATION_ID
AND SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
SELECT 'x' into temp
FROM PER_PEOPLE_F PPF,
PO_AGENTS POA, PER_BUSINESS_GROUPS_PERF PB
WHERE PPF.PERSON_ID = POA.AGENT_ID
AND PPF.BUSINESS_GROUP_ID= PB.BUSINESS_GROUP_ID
AND SYSDATE BETWEEN NVL(POA.START_DATE_ACTIVE, SYSDATE-1)
AND NVL(POA.END_DATE_ACTIVE,SYSDATE+1)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
AND PPF.PERSON_ID = cr.BUYER_ID;
SELECT 'x' into temp
FROM PER_PEOPLE_F PPF,
PO_AGENTS POA , HR_ORGANIZATION_UNITS ORG
WHERE PPF.PERSON_ID = POA.AGENT_ID
AND PPF.BUSINESS_GROUP_ID= ORG.BUSINESS_GROUP_ID
AND ORG.ORGANIZATION_ID = cr.ORGANIZATION_ID
AND SYSDATE BETWEEN NVL(POA.START_DATE_ACTIVE, SYSDATE-1)
AND NVL(POA.END_DATE_ACTIVE,SYSDATE+1)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
AND PPF.PERSON_ID = cr.BUYER_ID;
select 'x' into temp
from RCV_ROUTING_HEADERS
where ROUTING_HEADER_ID = cr.RECEIVING_ROUTING_ID;
select UNIT_OF_MEASURE into temp_u_o_m
from MTL_UNITS_OF_MEASURE
where UOM_CODE = cr.SECONDARY_UOM_CODE
and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
select UOM_CLASS
into temp_uom_class
from MTL_UNITS_OF_MEASURE
where UOM_CODE = cr.SECONDARY_UOM_CODE;
select 'x' into temp
from MTL_SYSTEM_ITEMS msi
where INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
and secondary_uom_code IS NOT NULL
and not exists (select UNIT_OF_MEASURE
from MTL_UNITS_OF_MEASURE MUOM
where UOM_CLASS = temp_uom_class
and MUOM.UOM_CODE = msi.SECONDARY_UOM_CODE)
and rownum = 1;
select 'x' into temp
from MTL_UNITS_OF_MEASURE
where UOM_CODE = cr.DIMENSION_UOM_CODE;
/* NP26DEC94 : New code to update process_flag.
** This code necessiated due to the breaking up INVPVHDR into
** 6 smaller packages to overcome PL/SQL limitations with code size.
** Let's update the process flag for the record
** Give it value 42 if all okay and 32 if some validation failed in this procedure
** Need to do this ONLY if all previous validation okay.
** The process flag values that are possible at this time are
** 31, 41 :set by INVPVHDR
** 32, 42 :set by INVPVDR2
** 33, 43 :set by INVPVDR3
** 34, 44 :set by INVPVDR4
*/
/* Bug 4705184
select process_flag into temp_proc_flag
from MTL_SYSTEM_ITEMS_INTERFACE
where inventory_item_id = l_item_id
and set_process_id + 0 = xset_id
and process_flag in (31, 32, 33, 34, 44)
and organization_id = cr.organization_id
and rownum < 2; */
update MTL_SYSTEM_ITEMS_INTERFACE
set process_flag = DECODE(status,0,45,35),
PRIMARY_UOM_CODE = cr.primary_uom_code,
primary_unit_of_measure = cr.primary_unit_of_measure
where inventory_item_id = l_item_id
and set_process_id + 0 = xset_id
and process_flag = 44
and organization_id = cr.organization_id;