[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT qualifier_id
FROM qp_qualifiers
WHERE list_header_id = p_price_list_id
AND NOT (qualifier_context = 'MODLIST' AND
qualifier_attribute = 'QUALIFIER_ATTRIBUTE4');
IS SELECT a.user_precedence
FROM qp_segments_v a,
qp_prc_contexts_b b,
qp_pte_segments c
WHERE
b.prc_context_type = 'PRODUCT' and
b.prc_context_code = 'ITEM' and
b.prc_context_id = a.prc_context_id and
a.segment_mapping_column = 'PRICING_ATTRIBUTE1' and
a.segment_id = c.segment_id and
c.pte_code = a_pte_code;
select qp_pricing_attr_group_no_s.nextval
into l_attr_grp_s
from dual;
select (-1*PL.ROUNDING_FACTOR),
NVL(FC.MINIMUM_ACCOUNTABLE_UNIT,-1)
into l_rounding_factor, l_min_acct_unit
from QP_LIST_HEADERS_B PL, FND_CURRENCIES FC
where PL.LIST_HEADER_ID = p_price_list_id
and PL.CURRENCY_CODE = FC.CURRENCY_CODE;
select count(*)
into l_already_exists
from qp_pricing_attributes qppa
where qppa.pricing_phase_id = 1
and qppa.qualification_ind in (4,6,20,22)
and qppa.product_attribute_context = 'ITEM'
and qppa.product_attr_value = l_item_tbl(l_index)
and qppa.excluder_flag = 'N'
and qppa.list_header_id = p_price_list_id;
Select 1
Into l_already_exists
From Dual
Where Exists
(Select Null
from qp_pricing_attributes qppa
where qppa.list_header_id = p_price_list_id
and qppa.pricing_phase_id = 1
and qppa.product_attribute_context = 'ITEM'
and qppa.product_attribute = 'PRICING_ATTRIBUTE1'
and qppa.product_attr_value = l_item_tbl(l_index)
);
select decode(l_min_acct_unit,
-1,
round(nvl(cst.item_cost,0), l_rounding_factor),
round(nvl(cst.item_cost,0)/l_min_acct_unit)
* l_min_acct_unit)
into l_price
from mtl_system_items mtl, cst_item_costs_for_gl_view cst
where mtl.inventory_item_id = l_item_tbl(l_index)
and cst.inventory_item_id (+)=mtl.inventory_item_id
and cst.organization_id (+)= nvl(p_costorg_id,mtl.organization_id)
and mtl.organization_id = p_organization_id;
select decode(l_min_acct_unit,
-1,
round(nvl(cst.item_cost,0), l_rounding_factor),
round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
Into l_price
from mtl_system_items mtl, cst_item_costs_for_gl_view cst
where mtl.inventory_item_id = l_item_tbl(l_index)
and cst.inventory_item_id =mtl.inventory_item_id
and cst.organization_id = mtl.organization_id
and mtl.organization_id = p_organization_id;
SELECT decode(l_min_acct_unit,-1,round(nvl(v_item_cost,0), l_rounding_factor),
round(nvl(v_item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
INTO l_price
FROM dual;
select decode(l_min_acct_unit,
-1,
round(nvl(cst.item_cost,0), l_rounding_factor),
round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
Into l_price
from mtl_system_items mtl, cst_item_costs_for_gl_view cst
where mtl.inventory_item_id = l_item_tbl(l_index)
and cst.inventory_item_id =mtl.inventory_item_id
and cst.organization_id = p_costorg_id
and mtl.organization_id = p_organization_id;
select decode(l_min_acct_unit,
-1,
nvl(cst.item_cost,0),
round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
Into l_price
from mtl_system_items mtl, cst_item_costs_for_gl_view cst
where mtl.inventory_item_id = l_item_tbl(l_index)
and cst.inventory_item_id =mtl.inventory_item_id
and cst.organization_id = mtl.organization_id
and mtl.organization_id = p_organization_id;
SELECT decode(l_min_acct_unit,-1,round(nvl(v_item_cost,0), l_rounding_factor),
round(nvl(v_item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
INTO l_price
FROM dual;
select decode(l_min_acct_unit,
-1,
nvl(cst.item_cost,0),
round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
Into l_price
from mtl_system_items mtl, cst_item_costs_for_gl_view cst
where mtl.inventory_item_id = l_item_tbl(l_index)
and cst.inventory_item_id =mtl.inventory_item_id
and cst.organization_id = p_costorg_id
and mtl.organization_id = p_organization_id;
select mtl.primary_uom_code
into l_uom
from mtl_system_items mtl
where mtl.inventory_item_id = l_item_tbl(l_index)
and mtl.organization_id = p_organization_id;
select qp_list_lines_s.nextval
into l_list_line_id
from dual;
SELECT orig_system_header_ref INTO
l_orig_sys_header_ref
FROM qp_list_headers_all_b
WHERE list_header_id = p_price_list_id;
insert
into qp_list_lines
(LIST_LINE_ID,
LIST_LINE_NO,
LAST_UPDATE_DATE,
CREATION_DATE,
LIST_PRICE_UOM_CODE,
LIST_PRICE,
LAST_UPDATED_BY,
CREATED_BY,
LAST_UPDATE_LOGIN,
LIST_HEADER_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LIST_LINE_TYPE_CODE,
START_DATE_ACTIVE,
AUTOMATIC_FLAG,
PRICING_PHASE_ID,
OPERAND,
ARITHMETIC_OPERATOR,
INCOMPATIBILITY_GRP_CODE,
PRODUCT_PRECEDENCE,
MODIFIER_LEVEL_CODE,
QUALIFICATION_IND
-- Bug 5202021 RAVI
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF) -- 10185331
values (
l_list_line_id,
l_list_line_id,
-- Begin Bug No: 7281484
sysdate,
sysdate,
-- End Bug No: 7281484
l_uom,
l_price,
l_user_id,
l_user_id,
l_conc_login_id,
p_price_list_id,
l_conc_request_id,
l_conc_program_application_id,
l_conc_program_id,
sysdate, --Bug No: 7281484
'PLL',
trunc(sysdate),
'Y',
1,
l_price,
'UNIT_PRICE',
'EXCL',
l_sequence_num, --modified by dhgupta for bug 2113793
-- 220,
'LINE',
l_qualification_ind
-- Bug 5202021 RAVI
,l_list_line_id
,l_orig_sys_header_ref); -- 10185331
insert
into qp_pricing_attributes
(pricing_attribute_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
list_line_id,
list_header_id,
pricing_phase_id,
qualification_ind,
product_attribute_context,
product_attribute,
product_attribute_datatype, --3099578
product_attr_value,
product_uom_code,
excluder_flag,
accumulate_flag,
comparison_operator_code, --2814272
attribute_grouping_no
-- Bug 5202021 RAVI
,ORIG_SYS_PRICING_ATTR_REF
,ORIG_SYS_HEADER_REF) -- 10185331
values (qp_pricing_attributes_s.nextval,
sysdate, -- Bug No: 7281484
l_user_id,
sysdate, -- Bug No: 7281484
l_user_id,
l_conc_login_id,
l_conc_program_application_id,
l_conc_program_id,
sysdate, -- Bug No: 7281484
l_conc_request_id,
l_list_line_id,
p_price_list_id,
1,
l_qualification_ind,
'ITEM',
'PRICING_ATTRIBUTE1',
'C', --3099578
l_item_tbl(l_index),
l_uom,
'N',
'N',
'BETWEEN', --2814272
l_attr_grp_s
-- Bug 5202021 RAVI
,qp_pricing_attributes_s.CURRVAL
,l_orig_sys_header_ref); -- 10185331
select count(*)
into l_already_exists
from qp_pricing_attributes qppa
where qppa.list_header_id = p_price_list_id
and qppa.pricing_phase_id = 1
and qppa.product_attribute_context = 'ITEM'
and qppa.product_attr_value = l_item_tbl(l_index);
Select 1
Into l_already_exists
From Dual
Where Exists
(Select Null
from qp_pricing_attributes qppa
where qppa.list_header_id = p_price_list_id
and qppa.pricing_phase_id = 1
and qppa.product_attribute_context = 'ITEM'
and qppa.product_attribute = 'PRICING_ATTRIBUTE1'
and qppa.product_attr_value = l_item_tbl(l_index)
);
select mtl.primary_uom_code
into l_uom
from mtl_system_items mtl
where mtl.inventory_item_id = l_item_tbl(l_index)
and mtl.organization_id = p_organization_id;
SELECT orig_system_header_ref INTO
l_orig_sys_header_ref
FROM qp_list_headers_all_b
WHERE list_header_id = p_price_list_id;
insert
into qp_list_lines
(LIST_LINE_ID,
LIST_LINE_NO,
LAST_UPDATE_DATE,
CREATION_DATE,
LIST_PRICE_UOM_CODE,
LIST_PRICE,
LAST_UPDATED_BY,
CREATED_BY,
LAST_UPDATE_LOGIN,
LIST_HEADER_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LIST_LINE_TYPE_CODE,
START_DATE_ACTIVE,
AUTOMATIC_FLAG,
PRICING_PHASE_ID,
OPERAND,
ARITHMETIC_OPERATOR,
INCOMPATIBILITY_GRP_CODE,
PRODUCT_PRECEDENCE,
MODIFIER_LEVEL_CODE,
QUALIFICATION_IND
-- Bug 5202021 RAVI
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF) -- 10185331
values (
qp_list_lines_s.nextval,
qp_list_lines_s.currval,
-- Begin Bug No: 7281484
sysdate,
sysdate,
-- End -- Bug No: 7281484
l_uom,
0,
l_user_id,
l_user_id,
l_conc_login_id,
p_price_list_id,
l_conc_request_id,
l_conc_program_application_id,
l_conc_program_id,
sysdate, -- Bug No: 7281484
'PLL',
trunc(sysdate),
'Y',
1,
0,
'UNIT_PRICE',
'EXCL',
l_sequence_num,
--220, --modified by dhgupta for bug 2113793
'LINE',
l_qualification_ind
-- Bug 5202021 RAVI
,qp_list_lines_s.CURRVAL
,l_orig_sys_header_ref); -- 10185331
| Insert pricing attributes |
+-----------------------------------------------------------------------*/
insert
into qp_pricing_attributes
(PRICING_ATTRIBUTE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
LIST_LINE_ID,
LIST_HEADER_ID,
PRICING_PHASE_ID,
QUALIFICATION_IND,
PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTRIBUTE,
product_attribute_datatype, --3099578
PRODUCT_ATTR_VALUE,
PRODUCT_UOM_CODE,
EXCLUDER_FLAG,
ACCUMULATE_FLAG,
comparison_operator_code, --2814272
ATTRIBUTE_GROUPING_NO
-- Bug 5202021 RAVI
,ORIG_SYS_PRICING_ATTR_REF
,ORIG_SYS_HEADER_REF) -- 10185331
values (qp_pricing_attributes_s.nextval,
sysdate, -- Bug No: 7281484
l_user_id,
sysdate, -- Bug No: 7281484
l_user_id,
l_conc_login_id,
l_conc_program_application_id,
l_conc_program_id,
sysdate, -- Bug No: 7281484
l_conc_request_id,
qp_list_lines_s.currval,
p_price_list_id,
1,
l_qualification_ind,
'ITEM',
'PRICING_ATTRIBUTE1',
'C', --3099578
l_item_tbl(l_index),
l_uom,
'N',
'N',
'BETWEEN', --2814272
l_attr_grp_s
-- Bug 5202021 RAVI
,qp_pricing_attributes_s.CURRVAL
,l_orig_sys_header_ref); -- 10185331
select min(list_line_id), max(list_line_id)
into l_min_list_line_id, l_max_list_line_id
from qp_list_lines
where list_header_id = p_price_list_id;
QP_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id(p_price_list_id, l_min_list_line_id, l_max_list_line_id);
QP_ATTR_GRP_PVT.update_pp_lines(p_price_list_id, l_min_list_line_id, l_max_list_line_id);
select min(list_line_id), max(list_line_id)
into l_min_list_line_id, l_max_list_line_id
from qp_list_lines
where list_header_id = p_price_list_id;
QP_PS_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id(p_price_list_id, l_min_list_line_id, l_max_list_line_id);
QP_PS_ATTR_GRP_PVT.update_pp_lines(p_price_list_id, l_min_list_line_id, l_max_list_line_id);