[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*SELECT OE_ORDER_LINES_S.NEXTVAL
INTO l_line_id
FROM DUAL;
in_atp_rec.Insert_Flag(I) := 1;
in_atp_rec.Insert_Flag(I) := 0;
SELECT mrp_atp_schedule_temp_s.nextval
INTO l_session_id
FROM dual;
SELECT BOM_ITEM_TYPE,PICK_COMPONENTS_FLAG
INTO l_bom_item_type,l_pick_components_flag
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID= in_item_id
AND ORGANIZATION_ID=in_org_id;
select meaning
into l_explanation
from mfg_lookups
where lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
and lookup_code = 80;
select meaning
into l_explanation
from mfg_lookups
where lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
and lookup_code = p_atp_rec.error_code(J) ;
select meaning
into l_explanation
from mfg_lookups
where lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
and lookup_code = p_atp_rec.error_code(J) ;
p_x_atp_rec.Insert_Flag.extend(l_count);
p_x_atp_rec.ato_delete_flag.extend(l_count);
SELECT msi.lot_control_code
INTO l_lot_control_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_item_id
AND msi.organization_id = nvl(p_org_id,l_org_id);
SELECT organization_code,
name
INTO out_code,
out_name
FROM oe_ship_from_orgs_v
WHERE organization_id = in_org_id;
G_LINE_INDEX_TBL.delete;
l_control_rec.temp_table_insert_flag := 'N';
SELECT concatenated_segments
INTO oe_order_pub.g_line.ordered_item
FROM mtl_system_items_kfv
WHERE inventory_item_id = g_panda_rec_table(p_line_index).p_inventory_item_id
AND organization_id = l_master_org_id;
SELECT concatenated_segments
INTO oe_order_pub.g_line.ordered_item
FROM mtl_system_items_kfv
WHERE inventory_item_id = g_inventory_item_id
AND organization_id = l_org_id;
G_LINE_INDEX_tbl.delete;
G_LINE_TYPE_CODE_TBL.delete ;
G_PRICING_EFFECTIVE_DATE_TBL.delete ;
G_ACTIVE_DATE_FIRST_TBL.delete ;
G_ACTIVE_DATE_FIRST_TYPE_TBL.delete ;
G_ACTIVE_DATE_SECOND_TBL.delete ;
G_ACTIVE_DATE_SECOND_TYPE_TBL.delete ;
G_LINE_QUANTITY_TBL.delete ;
G_LINE_UOM_CODE_TBL.delete ;
G_REQUEST_TYPE_CODE_TBL.delete ;
G_PRICED_QUANTITY_TBL.delete ;
G_UOM_QUANTITY_TBL.delete ;
G_PRICED_UOM_CODE_TBL.delete ;
G_CURRENCY_CODE_TBL.delete ;
G_UNIT_PRICE_TBL.delete ;
G_PERCENT_PRICE_TBL.delete ;
G_ADJUSTED_UNIT_PRICE_TBL.delete ;
G_PROCESSED_FLAG_TBL.delete ;
G_PRICE_FLAG_TBL.delete ;
G_LINE_ID_TBL.delete ;
G_PROCESSING_ORDER_TBL.delete ;
G_ROUNDING_FLAG_TBL.delete;
G_ROUNDING_FACTOR_TBL.delete ;
G_PRICING_STATUS_CODE_TBL.delete ;
G_PRICING_STATUS_TEXT_TBL.delete ;
G_ATTR_LINE_INDEX_tbl.delete;
G_ATTR_ATTRIBUTE_LEVEL_tbl.delete;
G_ATTR_VALIDATED_FLAG_tbl.delete;
G_ATTR_ATTRIBUTE_TYPE_tbl.delete;
G_ATTR_PRICING_CONTEXT_tbl.delete;
G_ATTR_PRICING_ATTRIBUTE_tbl.delete;
G_ATTR_APPLIED_FLAG_tbl.delete;
G_ATTR_PRICING_STATUS_CODE_tbl.delete;
G_ATTR_PRICING_ATTR_FLAG_tbl.delete;
G_ATTR_LIST_HEADER_ID_tbl.delete;
G_ATTR_LIST_LINE_ID_tbl.delete;
G_ATTR_VALUE_FROM_tbl.delete;
G_ATTR_SETUP_VALUE_FROM_tbl.delete;
G_ATTR_VALUE_TO_tbl.delete;
G_ATTR_SETUP_VALUE_TO_tbl.delete;
G_ATTR_GROUPING_NUMBER_tbl.delete;
G_ATTR_NO_QUAL_IN_GRP_tbl.delete;
G_ATTR_COMP_OPERATOR_TYPE_tbl.delete;
G_ATTR_VALIDATED_FLAG_tbl.delete;
G_ATTR_APPLIED_FLAG_tbl.delete;
G_ATTR_PRICING_STATUS_CODE_tbl.delete;
G_ATTR_PRICING_STATUS_TEXT_tbl.delete;
G_ATTR_QUAL_PRECEDENCE_tbl.delete;
G_ATTR_DATATYPE_tbl.delete;
G_ATTR_PRICING_ATTR_FLAG_tbl.delete ;
G_ATTR_QUALIFIER_TYPE_tbl.delete;
G_ATTR_PRODUCT_UOM_CODE_TBL.delete;
G_ATTR_EXCLUDER_FLAG_TBL.delete;
G_ATTR_PRICING_PHASE_ID_TBL.delete;
G_ATTR_INCOM_GRP_CODE_TBL.delete;
G_ATTR_LDET_TYPE_CODE_TBL.delete;
G_ATTR_MODIFIER_LEVEL_CODE_TBL.delete;
G_ATTR_PRIMARY_UOM_FLAG_TBL.delete;
oe_debug_pub.add( 'BEFORE DIRECT INSERT INTO TEMP TABLE: BULK INSERT'||G_LINE_INDEX_TBL.COUNT , 1 ) ;
QP_PREQ_GRP.INSERT_LINES2
(p_LINE_INDEX => G_LINE_INDEX_TBL,
p_LINE_TYPE_CODE => G_LINE_TYPE_CODE_TBL,
p_PRICING_EFFECTIVE_DATE =>G_PRICING_EFFECTIVE_DATE_TBL,
p_ACTIVE_DATE_FIRST =>G_ACTIVE_DATE_FIRST_TBL,
p_ACTIVE_DATE_FIRST_TYPE =>G_ACTIVE_DATE_FIRST_TYPE_TBL,
p_ACTIVE_DATE_SECOND =>G_ACTIVE_DATE_SECOND_TBL,
p_ACTIVE_DATE_SECOND_TYPE =>G_ACTIVE_DATE_SECOND_TYPE_TBL,
p_LINE_QUANTITY => G_LINE_QUANTITY_TBL,
p_LINE_UOM_CODE => G_LINE_UOM_CODE_TBL,
p_REQUEST_TYPE_CODE => G_REQUEST_TYPE_CODE_TBL,
p_PRICED_QUANTITY => G_PRICED_QUANTITY_TBL,
p_PRICED_UOM_CODE => G_PRICED_UOM_CODE_TBL,
p_CURRENCY_CODE => G_CURRENCY_CODE_TBL,
p_UNIT_PRICE => G_UNIT_PRICE_TBL,
p_PERCENT_PRICE => G_PERCENT_PRICE_TBL,
p_UOM_QUANTITY => G_UOM_QUANTITY_TBL,
p_ADJUSTED_UNIT_PRICE =>G_ADJUSTED_UNIT_PRICE_TBL,
p_UPD_ADJUSTED_UNIT_PRICE =>G_UPD_ADJUSTED_UNIT_PRICE_TBL,
p_PROCESSED_FLAG =>G_PROCESSED_FLAG_TBL,
p_PRICE_FLAG =>G_PRICE_FLAG_TBL,
p_LINE_ID =>G_LINE_ID_TBL,
p_PROCESSING_ORDER =>G_PROCESSING_ORDER_TBL,
p_PRICING_STATUS_CODE =>G_PRICING_STATUS_CODE_tbl,
p_PRICING_STATUS_TEXT =>G_PRICING_STATUS_TEXT_tbl,
p_ROUNDING_FLAG =>G_ROUNDING_FLAG_TBL,
p_ROUNDING_FACTOR =>G_ROUNDING_FACTOR_TBL,
p_QUALIFIERS_EXIST_FLAG => G_QUALIFIERS_EXIST_FLAG_TBL,
p_PRICING_ATTRS_EXIST_FLAG =>G_PRICING_ATTRS_EXIST_FLAG_TBL,
p_PRICE_LIST_ID => G_PRICE_LIST_ID_TBL,
p_VALIDATED_FLAG => G_PL_VALIDATED_FLAG_TBL,
p_PRICE_REQUEST_CODE => G_PRICE_REQUEST_CODE_TBL,
p_USAGE_PRICING_TYPE => G_USAGE_PRICING_TYPE_tbl,
p_line_category => G_LINE_CATEGORY_tbl,
--p_catchweight_qty => G_CATCHWEIGHT_QTY_tbl,
--p_actual_order_qty => G_ACTUAL_ORDER_QTY_TBL,
x_status_code =>l_return_status,
x_status_text =>l_return_status_text);
oe_debug_pub.add( 'WRONG IN INSERT_LINES2'||L_RETURN_STATUS_TEXT , 1 ) ;
QP_PREQ_GRP.INSERT_LINE_ATTRS2
( G_ATTR_LINE_INDEX_tbl,
G_ATTR_LINE_DETAIL_INDEX_tbl ,
G_ATTR_ATTRIBUTE_LEVEL_tbl ,
G_ATTR_ATTRIBUTE_TYPE_tbl ,
G_ATTR_LIST_HEADER_ID_tbl ,
G_ATTR_LIST_LINE_ID_tbl ,
G_ATTR_PRICING_CONTEXT_tbl ,
G_ATTR_PRICING_ATTRIBUTE_tbl ,
G_ATTR_VALUE_FROM_tbl ,
G_ATTR_SETUP_VALUE_FROM_tbl ,
G_ATTR_VALUE_TO_tbl ,
G_ATTR_SETUP_VALUE_TO_tbl ,
G_ATTR_GROUPING_NUMBER_tbl ,
G_ATTR_NO_QUAL_IN_GRP_tbl ,
G_ATTR_COMP_OPERATOR_TYPE_tbl ,
G_ATTR_VALIDATED_FLAG_tbl ,
G_ATTR_APPLIED_FLAG_tbl ,
G_ATTR_PRICING_STATUS_CODE_tbl ,
G_ATTR_PRICING_STATUS_TEXT_tbl ,
G_ATTR_QUAL_PRECEDENCE_tbl ,
G_ATTR_DATATYPE_tbl ,
G_ATTR_PRICING_ATTR_FLAG_tbl ,
G_ATTR_QUALIFIER_TYPE_tbl ,
G_ATTR_PRODUCT_UOM_CODE_TBL ,
G_ATTR_EXCLUDER_FLAG_TBL ,
G_ATTR_PRICING_PHASE_ID_TBL ,
G_ATTR_INCOM_GRP_CODE_TBL,
G_ATTR_LDET_TYPE_CODE_TBL,
G_ATTR_MODIFIER_LEVEL_CODE_TBL,
G_ATTR_PRIMARY_UOM_FLAG_TBL,
l_return_status ,
l_return_status_text );
oe_debug_pub.add( 'ERROR INSERTING INTO LINE ATTRS'||SQLERRM ) ;
oe_debug_pub.add( 'AFTER DIRECT INSERT INTO TEMP TABLE: BULK INSERT' , 1 ) ;
SELECT LINE_INDEX,
LINE_ID,
PRICE_LIST_HEADER_ID, -- shu, print out this
LINE_TYPE_CODE,
LINE_QUANTITY,
LINE_UOM_CODE,
LINE_UNIT_PRICE, -- shu
ROUNDING_FACTOR, -- shu
PRICED_QUANTITY,
UOM_QUANTITY,
PRICED_UOM_CODE,
CURRENCY_CODE,
UNIT_PRICE,
PERCENT_PRICE,
PARENT_PRICE,
PARENT_QUANTITY,
PARENT_UOM_CODE,
PRICE_FLAG,
ADJUSTED_UNIT_PRICE,
UPDATED_ADJUSTED_UNIT_PRICE,
PROCESSING_ORDER,
PROCESSED_CODE,
PRICING_STATUS_CODE,
PRICING_STATUS_TEXT,
HOLD_CODE,
HOLD_TEXT,
PRICE_REQUEST_CODE,
PRICING_EFFECTIVE_DATE,
EXTENDED_PRICE, /* block pricing */
ORDER_UOM_SELLING_PRICE
FROM QP_PREQ_LINES_TMP;
SELECT QPLAT.LINE_INDEX,
QPLAT.LINE_DETAIL_INDEX,
QPLAT.CONTEXT,
QPLAT.ATTRIBUTE,
QPLAT.SETUP_VALUE_FROM,
QPLAT.SETUP_VALUE_TO,
QPLAT.COMPARISON_OPERATOR_TYPE_CODE,
QPLAT.VALIDATED_FLAG,
QPLAT.PRICING_STATUS_CODE,
QPLAT.PRICING_STATUS_TEXT
FROM QP_PREQ_LDETS_TMP QPLD ,
QP_PREQ_LINE_ATTRS_TMP QPLAT
WHERE QPLD.LINE_DETAIL_INDEX = QPLAT.LINE_DETAIL_INDEX
AND QPLD.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND QPLAT.ATTRIBUTE_TYPE = 'QUALIFIER';
SELECT QPLAT_PRICING.CONTEXT PRICING_CONTEXT,
QPLAT_PRICING.ATTRIBUTE PRICING_ATTRIBUTE,
nvl(QPLAT_PRICING.SETUP_VALUE_FROM,QPLAT_PRICING.VALUE_FROM) PRICING_ATTR_VALUE_FROM,
QPLAT_PRICING.SETUP_VALUE_TO PRICING_ATTR_VALUE_TO,
QPLAT_PRICING.COMPARISON_OPERATOR_TYPE_CODE,
QPLAT_PRICING.LINE_DETAIL_INDEX,
QPLAT_PRICING.LINE_INDEX,
QPLAT_PRICING.VALIDATED_FLAG
FROM QP_PREQ_LDETS_TMP QPLD ,
QP_PREQ_LINE_ATTRS_TMP QPLAT_PRICING
WHERE QPLD.LINE_DETAIL_INDEX = QPLAT_PRICING.LINE_DETAIL_INDEX
AND QPLD.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND QPLAT_PRICING.ATTRIBUTE_TYPE IN ('PRICING','PRODUCT');
SELECT /*+ ORDERED USE_NL(A B C) l_ldets_cur */
a.LINE_DETAIL_INDEX,
a.LINE_DETAIL_TYPE_CODE,
a.LINE_INDEX,
a.CREATED_FROM_LIST_HEADER_ID LIST_HEADER_ID,
a.CREATED_FROM_LIST_LINE_ID LIST_LINE_ID,
a.CREATED_FROM_LIST_LINE_TYPE LIST_LINE_TYPE_CODE,
a.PRICE_BREAK_TYPE_CODE,
a.LINE_QUANTITY,
a.ADJUSTMENT_AMOUNT,
a.AUTOMATIC_FLAG,
a.PRICING_PHASE_ID,
a.OPERAND_CALCULATION_CODE,
a.OPERAND_VALUE,
a.PRICING_GROUP_SEQUENCE,
a.CREATED_FROM_LIST_TYPE_CODE,
a.APPLIED_FLAG,
a.PRICING_STATUS_CODE,
a.PRICING_STATUS_TEXT,
a.LIMIT_CODE,
a.LIMIT_TEXT,
a.LIST_LINE_NO,
a.GROUP_QUANTITY,
a.GROUP_AMOUNT, -- 2388011_new
a.UPDATED_FLAG,
a.PROCESS_CODE,
a.CALCULATION_CODE,
a.CHANGE_REASON_CODE,
a.CHANGE_REASON_TEXT,
a.ORDER_QTY_ADJ_AMT,
b.SUBSTITUTION_VALUE SUBSTITUTION_VALUE_TO,
b.SUBSTITUTION_ATTRIBUTE,
b.ACCRUAL_FLAG,
b.modifier_level_code,
b.ESTIM_GL_VALUE,
b.ACCRUAL_CONVERSION_RATE,
--Pass throuh components
b.OVERRIDE_FLAG,
b.PRINT_ON_INVOICE_FLAG,
b.INVENTORY_ITEM_ID,
b.ORGANIZATION_ID,
b.RELATED_ITEM_ID,
b.RELATIONSHIP_TYPE_ID,
b.ESTIM_ACCRUAL_RATE,
b.EXPIRATION_DATE,
b.BENEFIT_PRICE_LIST_LINE_ID,
b.RECURRING_FLAG,
b.RECURRING_VALUE,
b.BENEFIT_LIMIT,
b.CHARGE_TYPE_CODE,
b.CHARGE_SUBTYPE_CODE,
b.BENEFIT_QTY,
b.BENEFIT_UOM_CODE,
b.PRORATION_TYPE_CODE,
b.INCLUDE_ON_RETURNS_FLAG,
b.REBATE_TRANSACTION_TYPE_CODE,
b.NUMBER_EXPIRATION_PERIODS,
b.EXPIRATION_PERIOD_UOM,
b.COMMENTS
FROM QP_PREQ_LDETS_TMP a,
QP_LIST_LINES b
WHERE a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
SELECT LINE_INDEX,
LINE_DETAIL_INDEX,
RELATIONSHIP_TYPE_CODE,
RELATED_LINE_INDEX,
RELATED_LINE_DETAIL_INDEX,
PRICING_STATUS_CODE,
PRICING_STATUS_TEXT
FROM QP_PREQ_RLTD_LINES_TMP
WHERE PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
ORDER BY SETUP_VALUE_FROM;
x_line_tbl(I).UPDATED_ADJUSTED_UNIT_PRICE := l_line.UPDATED_ADJUSTED_UNIT_PRICE;
x_line_detail_tbl(I).UPDATED_FLAG := l_dets.UPDATED_FLAG;
oe_debug_pub.add( 'UPDATED_ADJUSTED_UNIT_PRICE ='|| OUT_REQ_LINE_TBL ( I ) .UPDATED_ADJUSTED_UNIT_PRICE ) ;
g_panda_rec_table.delete;
Select/*+ ordered use_nl(qpq qppa qpll qplh) */ min(qpll.operand)
From
qp_qualifiers qpq
, qp_pricing_attributes qppa
, qp_list_lines qpll
, qp_list_headers_b qplh
, qp_price_req_sources qpprs
where
qpq.qualifier_context='CUSTOMER'
and qpq.qualifier_attribute='QUALIFIER_ATTRIBUTE15'
and qpq.qualifier_attr_value='Y'
and qppa.list_header_id=qplh.list_header_id
and qplh.Active_flag='Y'
and qpprs.request_type_code = 'ONT'
and qpprs.source_system_code=qplh.source_system_code
and qppa.pricing_phase_id = 2
and qppa.qualification_ind = 6
and qppa.product_attribute_context ='ITEM'
and qppa.product_attribute='PRICING_ATTRIBUTE1'
and qppa.product_attr_value= g_panda_rec_table(1).p_inventory_item_id
and qppa.excluder_flag = 'N'
and qppa.list_header_id=qpq.list_header_id
and qppa.list_line_id=qpll.list_line_id
and g_panda_rec_table(1).p_pricing_date between nvl(trunc(qplh.start_date_active),g_panda_rec_table(1).p_pricing_date)
and nvl(trunc(qplh.End_date_active),g_panda_rec_table(1).p_pricing_date);
Select name into l_price_list
from qp_list_headers_vl
where list_header_id = in_price_list_id;
Select name into l_price_list
from qp_list_headers_vl a,qp_list_lines b where
b.list_line_id = to_number(substr(in_status_text,1,
instr(in_status_text,',')-1))
and a.list_header_id=b.list_header_id ;
Select name into l_price_list
from qp_list_headers_vl a,qp_list_lines b where
b.list_line_id = to_number(substr(in_status_text,
instr(in_status_text,',')+1))
and a.list_header_id=b.list_header_id ;
oe_debug_pub.add('After select OE_PRICING_ERROR 2');
SELECT concatenated_segments
INTO l_msg_text
FROM mtl_system_items_kfv
WHERE inventory_item_id = g_inventory_item_id
AND organization_id = l_org_id;
(in_status_code in ( QP_PREQ_GRP.G_STATUS_UPDATED ,
QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
--In this case even engine doesn't update the order (status = UNCHANGED)
--because of one of the lined is frozen,
--there can be some order level adjustments in database which
--need to be pulled out by append_adjustment_lines routine
or (in_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED))
Then
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'OE_PRICING_ERROR 3' ) ;
ELSIF in_line_Type_code ='LINE' and in_status_code = 'UPDATED' then
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'STATUS = UPDATED' ) ;
select rounding_factor
into l_rounding_factor
from qp_list_headers_b
where list_header_id = p_list_header_id;
SELECT name,
description,
list_type_code,
end_date_active,
start_date_active,
currency_code,
ask_for_flag
INTO out_name,
out_description,
l_list_type_code,
out_end_date,
out_start_date,
out_currency,
out_ask_for_flag
FROM qp_list_headers_vl
WHERE list_header_id = in_list_header_id;
SELECT
list_line_type_code,
end_date_active,
start_date_active,
modifier_level_code
INTO
out_list_line_type_code,
out_end_date,
out_start_date,
out_modifier_level_code
FROM qp_list_lines
WHERE list_line_id = in_list_line_id;
SELECT meaning
INTO l_meaning
FROM qp_lookups
WHERE lookup_type = in_lookup_type
AND lookup_code = in_lookup_code;
SELECT NAME,
end_date_active,
start_date_active,
automatic_flag,
rounding_factor,
terms_id,
gsa_indicator,
currency_code,
freight_terms_code
INTO out_name,
out_end_date,
out_start_date,
out_automatic_flag,
out_rounding_factor,
out_terms_id,
out_gsa_indicator,
out_currency,
out_freight_terms_code
FROM qp_list_headers_vl
WHERE list_header_id = p_price_list_id
and list_type_code in ('PRL', 'AGR');
SELECT shippable_item_flag,
customer_order_enabled_flag,
internal_order_enabled_flag,
stock_enabled_flag,
default_shipping_org,
returnable_flag,
source_organization_id,
unit_weight,
weight_uom_code,
unit_volume,
volume_uom_code,
cum_manufacturing_lead_time,
cumulative_total_lead_time,
primary_unit_of_measure,
inventory_item_status_code,
full_lead_time,
order_cost,
minimum_order_quantity,
maximum_order_quantity,
fixed_order_quantity,
reservable_type,
item_type,
orderable_on_web_flag,
planning_make_buy_code,
taxable_flag ,
serviceable_product_flag,
atp_flag
FROM mtl_system_items
WHERE inventory_item_id = in_inventory_item_id
AND organization_id = in_org_id;
select meaning
into out_user_item_type
from fnd_common_lookups
where lookup_type = 'ITEM_TYPE'
and lookup_code = l_user_item_type;
select hsecs
from v$timer;
select hsecs
from v$timer;
SELECT instance_id,
instance_code
FROM mrp_ap_apps_instances;
SELECT source_organization_id,
sr_instance_id,
ship_method,
delivery_lead_time,
freight_carrier
FROM mrp_atp_schedule_temp
WHERE session_id = in_session_id
--AND status_flag = -99;
SELECT mrp_atp_schedule_temp_s.nextval
INTO l_session_id
FROM dual;
' SELECT '||
' mast.plan_id '||
' FROM msc_atp_plan_sn'||l_dynstring||' mast '||
' WHERE mast.sr_instance_id = :in_instance_id '||
' AND mast.sr_inventory_item_id = :in_inventory_item_id'||
' AND mast.organization_id = :in_org_id';
oe_debug_pub.add(' Before inserting into temp table '||
' instance_id='||l_instance_id||
' session_id='||l_session_id||
' inv_item_id='||in_inventory_item_id||
' customer_id='||l_customer_id||
' customer_site_id='||l_customer_site_id||
' org_id='||l_org_id||
' ship_method='||l_ship_method
);
INSERT INTO MRP_ATP_SCHEDULE_TEMP(
sr_instance_id,
session_id,
inventory_item_id,
organization_id,
scenario_id,
customer_id,
customer_site_id,
ship_method,
status_flag)
VALUES
(l_instance_id,
l_session_id,
in_inventory_item_id,
l_org_id,
-1,
l_customer_id,
l_customer_site_id,
l_ship_method,
4
);
DELETE mrp_atp_schedule_temp
WHERE session_id = l_session_id;
SELECT primary_uom_code
FROM mtl_system_items_b
WHERE organization_id = in_org_id
AND inventory_item_id = g_panda_rec_table(1).p_Inventory_item_id;
SELECT uom_Class
FROM mtl_units_of_measure_tl
WHERE uom_code = in_ordered_uom;
SELECT uom_code
FROM mtl_units_of_measure_tl
WHERE uom_class = in_class
AND base_uom_flag = 'Y';
SELECT round(1/conversion_rate,6)
FROM mtl_uom_conversions
WHERE uom_code = in_uom_code
AND inventory_item_id = g_panda_rec_table(1).p_inventory_item_id;
SELECT round(1/conversion_rate,6)
FROM mtl_uom_conversions
WHERE uom_Code = in_uom_code
AND inventory_item_id = 0;
SELECT round(1/conversion_rate,6)
FROM mtl_uom_class_conversions
WHERE to_uom_code = in_uom_code
AND from_uom_code = in_base_uom
AND inventory_item_id = g_panda_rec_table(1).p_inventory_item_id;
SELECT mrp_atp_schedule_temp_s.nextval
INTO l_session_id
FROM dual;
SELECT ltrim(rtrim(substr(value, instr(value,',',-1,1)+1)))
INTO l_mrp_dir
FROM v$parameter
WHERE name='utl_file_dir';
g_panda_rec_table.delete;