[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select meaning
into l_explanation
from mfg_lookups
where lookup_type = l_lookup_type
and lookup_code = l_lookup_code; -- added for bug 3776769
select meaning
into l_explanation
from mfg_lookups
where lookup_type = l_lookup_type
and lookup_code =l_lookup_code; -- added for bug 3776769
select meaning
into l_explanation
from mfg_lookups
where lookup_type =l_lookup_type
and lookup_code =l_lookup_code; -- added for bug 3776769
/*SELECT OE_ORDER_LINES_S.NEXTVAL
INTO l_line_id
FROM DUAL;
in_atp_rec.Insert_Flag(I) := 1; -- it can be 0 or 1
SELECT mrp_atp_schedule_temp_s.nextval
INTO l_session_id
FROM dual;
select nvl(enforce_line_prices_flag,'N') into out_enforce_price_flag
from oe_Order_types_v where Order_type_id=in_order_type_id;
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 BOM_ITEM_TYPE,PICK_COMPONENTS_FLAG
INTO l_bom_item_type,l_pick_components_flag
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID= in_out_default_rec.inventory_item_id
AND ORGANIZATION_ID=in_out_default_rec.org_id;
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;
SELECT p.party_name,p.party_id
INTO out_name,out_cust_id
FROM hz_parties p ,hz_cust_accounts c
WHERE p.party_id=c.party_id and c.cust_account_id = in_org_id;
SELECT p.party_name,c.ACCOUNT_NUMBER
INTO out_name,out_cust_id
FROM hz_parties p ,hz_cust_accounts c
WHERE p.party_id=c.party_id and c.cust_account_id = in_org_id;
SELECT name
INTO out_name
FROM oe_ship_to_orgs_v
WHERE organization_id = in_org_id;
SELECT name
INTO out_name
FROM oe_order_types_v
WHERE order_type_id = in_order_type_id;
SELECT name
INTO out_name
FROM fnd_currencies_vl
WHERE currency_code like l_temp;
SELECT demand_class
INTO out_name
FROM oe_demand_classes_v
WHERE demand_class_code like l_temp;
SELECT name
INTO out_name
FROM oe_invoice_to_orgs_v
WHERE organization_id = in_bill_to_org_id;
SELECT agreement_name
INTO out_name
FROM oe_agreements_lov_v
WHERE agreement_id = in_agreement_id;
G_LINE_INDEX_TBL.delete;
l_control_rec.temp_table_insert_flag := 'N';
x_control_rec.temp_table_insert_flag := in_control_rec.temp_table_insert_flag;
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; -- bug 5645866/5847803
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 qpt.line_index, qpt.line_id, qpt.price_list_header_id,
qpt.line_type_code, qpt.line_quantity, qpt.line_uom_code,
qpt.line_unit_price, qpt.rounding_factor, qpt.priced_quantity,
qpt.uom_quantity, qpt.priced_uom_code, qpt.currency_code,
qpt.unit_price, qpt.percent_price, qpt.parent_price,
qpt.parent_quantity, qpt.parent_uom_code, qpt.price_flag,
qpt.adjusted_unit_price, qpt.updated_adjusted_unit_price,
qpt.processing_order, qpt.processed_code, qpt.pricing_status_code,
qpt.pricing_status_text, qpt.hold_code, qpt.hold_text,
qpt.price_request_code, qpt.pricing_effective_date, qpt.extended_price,
qpt.order_uom_selling_price
FROM qp_preq_lines_tmp_t qpt
WHERE qp_java_engine_util_pub.java_engine_running = 'N'
AND request_id = NVL (SYS_CONTEXT ('qp_context', 'request_id'), 1)
UNION ALL
SELECT qpt.line_index, qpt.line_id, qpt.price_list_header_id,
qpt.line_type_code, qpt.line_quantity, qpt.line_uom_code,
qpt.line_unit_price, qpt.rounding_factor, qpt.priced_quantity,
qpt.uom_quantity, qpt.priced_uom_code, qpt.currency_code,
qpt.unit_price, qpt.percent_price, qpt.parent_price,
qpt.parent_quantity, qpt.parent_uom_code, qpt.price_flag,
qpt.adjusted_unit_price, qpt.updated_adjusted_unit_price,
qpt.processing_order, qpt.processed_code, qpt.pricing_status_code,
qpt.pricing_status_text, qpt.hold_code, qpt.hold_text,
qpt.price_request_code, qpt.pricing_effective_date, qpt.extended_price,
qpt.order_uom_selling_price
FROM qp_int_lines_t qpt
WHERE qp_java_engine_util_pub.java_engine_running = 'Y'
AND request_id = NVL (SYS_CONTEXT ('qp_context', 'request_id'), -9999);
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 qpt.line_detail_index, qpt.line_detail_type_code, qpt.line_index,
qpt.created_from_list_header_id list_header_id,
qpt.created_from_list_line_id list_line_id,
qpt.created_from_list_line_type list_line_type_code,
qpt.price_break_type_code, qpt.line_quantity, qpt.adjustment_amount,qpt.automatic_flag,
qpt.pricing_phase_id, qpt.operand_calculation_code, qpt.operand_value,
qpt.pricing_group_sequence, qpt.created_from_list_type_code,
qpt.applied_flag, qpt.pricing_status_code, qpt.pricing_status_text,
qpt.limit_code, qpt.limit_text, qpt.list_line_no, qpt.group_quantity,
qpt.group_amount, qpt.updated_flag, qpt.process_code,
qpt.calculation_code, qpt.change_reason_code, qpt.change_reason_text,
qpt.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, 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_t qpt, qp_list_lines b
WHERE qp_java_engine_util_pub.java_engine_running = 'N'
AND qpt.request_id = NVL (SYS_CONTEXT ('QP_CONTEXT', 'REQUEST_ID'), 1)
AND qpt.created_from_list_line_id = b.list_line_id
AND qpt.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
UNION ALL
SELECT qpt.line_detail_index, qpt.line_detail_type_code, qpt.line_index,
qpt.created_from_list_header_id list_header_id, qpt.created_from_list_line_id list_line_id,
qpt.created_from_list_line_type list_line_type_code, qpt.price_break_type_code,
qpt.line_quantity, qpt.adjustment_amount, qpt.automatic_flag, qpt.pricing_phase_id,
qpt.operand_calculation_code, qpt.operand_value,
qpt.pricing_group_sequence, qpt.created_from_list_type_code,
qpt.applied_flag, qpt.pricing_status_code, qpt.pricing_status_text,
qpt.limit_code, qpt.limit_text, qpt.list_line_no, qpt.group_quantity,
qpt.group_amount, qpt.updated_flag, qpt.process_code,
qpt.calculation_code, qpt.change_reason_code, qpt.change_reason_text,
qpt.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, 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_int_ldets_t qpt, qp_list_lines b
WHERE qp_java_engine_util_pub.java_engine_running = 'Y'
AND qpt.request_id = NVL (SYS_CONTEXT ('QP_CONTEXT', 'REQUEST_ID'), -9999)
AND qpt.created_from_list_line_id = b.list_line_id
AND qpt.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;
g_applied_manual_tbl.delete;
oe_debug_pub.add('before inserting manual adjustment'||g_manual_modifier_tbl.count);
insert_manual_adjustment(in_line_id=>g_panda_rec_table(l_line_index).p_line_id,
in_line_index=>global_line_index) ;
oe_debug_pub.add('after inserting manual adjustment');
oe_debug_pub.add('UPDATED_ADJUSTED_UNIT_PRICE ='|| OUT_REQ_LINE_TBL (I) .UPDATED_ADJUSTED_UNIT_PRICE ) ;
g_panda_rec_table.delete;
Procedure Delete_manual_modifiers Is
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add('entered delete manual modifiers');
g_manual_modifier_tbl.delete;
g_modf_rel_tbl.delete;
g_modf_attributes_tbl.delete;
oe_debug_pub.add('exiting delete manual modifiers');
End Delete_manual_modifiers;
PROCEDURE Delete_Applied_Manual_Adj(in_line_id in number,
in_list_line_id in number,
in_list_header_id in number)
IS
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add('Entered Delete Applied Manual Adj');
g_modf_attributes_tbl.DELETE(ms);
g_manual_modifier_tbl.DELETE(ix);
g_modf_rel_tbl.delete(k);
-- delete the corresponding attributes
/* if g_modf_attributes_tbl.count >0 then
for ms in g_modf_attributes_tbl.first..g_modf_attributes_tbl.last
loop
if (g_modf_attributes_tbl.exists(ms) and
g_modf_attributes_tbl(ms).p_line_id = in_line_id and
g_modf_attributes_tbl(ms).p_list_line_id = g_manual_modifier_tbl(i).list_line_id)
then
g_modf_attributes_tbl.DELETE(ms);
g_manual_modifier_tbl.DELETE(i);
oe_debug_pub.add('Deleted the modifier');
END Delete_Applied_Manual_Adj;
PROCEDURE Insert_Manual_Adjustment(in_line_id in number,
in_line_index in number)
IS
x_status_code varchar2(100);
l_insert_flag varchar2(2);
oe_debug_pub.add('entered insert adjustment'||g_manual_modifier_tbl.count);
select count(*) into in_line_detail_index from qp_preq_ldets_tmp;
l_insert_flag:='Y';
l_insert_flag:='N';
if l_insert_flag = 'Y' then
rs:= g_applied_manual_tbl.count;
INSERT INTO QP_NPREQ_LDETS_TMP
(LINE_DETAIL_INDEX,
LINE_DETAIL_TYPE_CODE,
LINE_INDEX,
PROCESS_CODE,
PRICING_PHASE_ID,
OPERAND_CALCULATION_CODE,
OPERAND_VALUE,
OVERRIDE_FLAG,
CREATED_FROM_LIST_TYPE_CODE,
CREATED_FROM_LIST_HEADER_ID,
CREATED_FROM_LIST_LINE_ID,
CREATED_FROM_LIST_LINE_TYPE,
PRICING_STATUS_CODE,
APPLIED_FLAG,
MODIFIER_LEVEL_CODE,
UPDATED_FLAG,
PRICE_BREAK_TYPE_CODE)
--ORDER_QTY_OPERAND)
VALUES ( in_LINE_DETAIL_INDEX,
' ',
--in_LINE_INDEX,
l_LINE_INDEX,
'N',
g_manual_modifier_tbl(i).pricing_phase_id,
g_manual_modifier_tbl(i).operator,
g_manual_modifier_tbl(i).operand,
g_manual_modifier_tbl(i).override_flag,
' ',
g_manual_modifier_tbl(i).list_header_id,
g_manual_modifier_tbl(i).list_line_id,
g_manual_modifier_tbl(i).list_line_type_code,
'X',
--'N',
l_applied_flag,
g_manual_modifier_tbl(i).modifier_level_code,
'Y',
l_price_break_type_code);
Insert into qp_npreq_line_attrs_tmp
(Line_index,
Line_detail_index,
attribute_level,
attribute_type,
list_header_id,
list_line_id,
context,
attribute,
value_from,
value_to,
pricing_status_code,
pricing_phase_id,
modifier_level_code,
validated_flag)
values
(in_line_index,
in_line_detail_index,
'LINE',
'PRICING',
g_manual_modifier_tbl(i).list_header_id,
g_manual_modifier_tbl(i).list_line_id,
'VOLUME',
'PRICING_ATTRIBUTE10',
0,
0 ,
'X',
g_manual_modifier_tbl(i).pricing_phase_id,
g_manual_modifier_tbl(i).modifier_level_code,
'N');
oe_debug_pub.add('inserted pbh line into qp_preq_line_attrs_tmp');
Insert into qp_npreq_line_attrs_tmp
(Line_index,
Line_detail_index,
attribute_level,
attribute_type,
list_header_id,
list_line_id,
context,
attribute,
value_from,
value_to,
pricing_status_code,
pricing_phase_id,
modifier_level_code,
validated_flag)
values
(in_line_index,
in_line_detail_index,
'LINE',
'PRICING',
g_manual_modifier_tbl(i).list_header_id,
g_manual_modifier_tbl(i).list_line_id,
g_modf_attributes_tbl(k).p_context,
g_modf_attributes_tbl(k).p_attribute,
g_modf_attributes_tbl(k).p_attr_value_from,
g_modf_attributes_tbl(k).p_attr_value_to,
'X',
g_manual_modifier_tbl(i).pricing_phase_id,
g_manual_modifier_tbl(i).modifier_level_code,
'N');
oe_debug_pub.add('after inserting attributes');
select line_index,line_detail_index into l_line_index,l_line_detail_index
from qp_preq_ldets_Tmp where created_From_list_line_id=g_modf_rel_tbl(s).line_detail_index;
select line_index,line_detail_index into rltd_line_index,rltd_line_detail_index from
qp_preq_ldets_tmp where created_from_list_line_id=g_modf_rel_tbl(s).rltd_line_detail_index;
Insert into qp_npreq_rltd_lines_tmp
(line_index,
line_detail_index,
related_line_index,
related_line_detail_index,
pricing_status_code,
relationship_type_code)
Values
(l_line_index,
l_line_detail_index,
rltd_line_index,
rltd_line_detail_index,
'N',
'PBH_LINE');
oe_Debug_pub.add('after inserting relationship types');
oe_debug_pub.add('end of inserting');
oe_debug_pub.add('insert into ldets'||x_status_code||'error'||x_status_text);
END Insert_Manual_Adjustment;
g_promotions_tbl.DELETE(m);
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 concatenated_segments
INTO out_inv_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_inv_item_id
AND organization_id = l_org_id;
Select meaning into out_meaning from oe_lookups
where lookup_type = l_lookup_type and -- added for bug 3776769
lookup_code=in_item_type_code;
SELECT concatenated_segments,description,item_type
INTO out_inv_item_name,out_inv_desc,out_inv_item_type
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_inv_item_id
AND organization_id = l_org_id;
select product_attr_value into l_item_id from qp_pricing_attributes
where list_line_id=l_list_line_no;
select to_rltd_modifier_id into l_list_line_no
from qp_rltd_modifiers where
from_rltd_modifier_id=in_list_line_id;
select list_line_type_code,
arithmetic_operator,
operand into
l_code,l_operator,l_operand
from qp_list_lines where list_line_id=l_list_line_no;
select product_attr_value into l_item_id from qp_pricing_attributes
where list_line_id=l_list_line_no;
select segment_code into l_benefit_method from qp_segments_b qsb,qp_prc_contexts_b qpcb
where qsb.prc_context_id=qpcb.prc_context_id and
qsb.segment_mapping_column=in_substitution_attribute and qpcb.prc_context_code='TERMS';
select language_code into l_lang_code from fnd_languages where installed_flag='B';
select user_segment_name into out_benefit_method from qp_segments_tl qst,qp_segments_b qsb
where qst.segment_id= qsb.segment_id and qsb.segment_code=l_benefit_method and qst.language=l_lang_code;
select name into out_benefit_value from ra_terms where term_id=in_substitution_to;
select freight_terms into out_benefit_value from oe_frght_terms_Active_v
where freight_terms_code=in_substitution_to;
select meaning into out_benefit_value from oe_ship_methods_v where
lookup_type = l_lookup_type and lookup_code=in_substitution_to;
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
l.list_line_type_code,
nvl(l.end_date_active,h.end_date_active),
l.start_date_active,
l.modifier_level_code
INTO
out_list_line_type_code,
out_end_date,
out_start_date,
out_modifier_level_code
FROM qp_list_lines l,qp_list_headers h
WHERE l.list_line_id = in_list_line_id
AND h.list_header_id = l.list_header_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,
bom_item_type,
replenish_to_order_flag,
build_in_wip_flag,
decode(default_so_source_type,'EXTERNAL','External','INTERNAL','Internal')
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 = l_lookup_type -- added for bug 3776769
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 a2m_dblink
into l_mrp_atp_database_link
from mrp_ap_apps_instances;
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
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,
order_line_id)
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,
-1
);
-- Insert into l_sources_orgs_table org_id and instance_id
-- delete from mrp_atp_schedule_temp
FOR l_temp_rec in c_temp_table(l_session_id)
LOOP
IF l_debug_level > 0 THEN
oe_debug_pub.add( ' Reading data from temp table');
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';
if p_header_adj_tbl(i).updated_flag is not null then
x_header_adj_tbl(j).updated_flag:= p_header_adj_tbl(i).updated_flag;
if p_line_Adj_tbl(i).updated_flag is not null then
x_line_Adj_tbl(j).updated_flag :=p_line_Adj_tbl(i).updated_flag;
||'applied flag'||x_line_Adj_tbl(j).applied_flag||'updated_flag'||x_line_Adj_tbl(j).updated_flag
);
'Updated Flag=' ||l_line_adj_tbl(i).updated_flag ||
'Applied Flag=' ||l_line_adj_tbl(i).applied_flag ||
'Arithmetic Operator ='||l_line_adj_tbl(i).arithmetic_operator ||
'Price Break Type code =' ||l_line_adj_tbl(i).price_break_type_code
);
'Updated Flag=' ||l_header_adj_tbl(i).updated_flag ||
'Applied Flag=' ||l_header_adj_tbl(i).applied_flag ||
'Arithmetic Operator ='||l_header_adj_tbl(i).arithmetic_operator ||
'Price Break Type code =' ||l_header_adj_tbl(i).price_break_type_code
);
select booked_flag, shipping_interfaced_flag
into l_booked_flag, l_shipping_interfaced_flag
from oe_order_lines_all
where header_id = l_header_rec.header_id
and rownum = 1;
SELECT atp_flag,
decode(default_so_source_type,'EXTERNAL','External','INTERNAL','Internal')
FROM mtl_system_items
WHERE inventory_item_id = in_inventory_item_id
AND organization_id = in_org_id;
SELECT id_flex_num
FROM fnd_id_flex_structures
WHERE id_flex_code = X_Id_Flex_Code;