[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT chr.id ID,
chr.contract_number CONTRACT_NUMBER,
chr.short_description short_description,
chr.Currency_code currency_code,
chr.Contract_number_modifier contract_number_modifier,
chr.start_date start_date,
chr.end_date end_date,
chr.authoring_org_id authoring_org_id,
chr.inv_organization_id inv_organization_id
FROM okc_k_headers_V chr
WHERE chr.id=b_chr_id
and chr.application_id in (510,871)
AND chr.buy_or_sell='S'
AND chr.issue_or_receive='I'
AND chr.template_yn='N'
AND chr.deleted_yn='N';
SELECT id ,
object_version_number,
ph_min_qty, -- Minimum Order Quantity
ph_min_amt, -- Minimum Order Amount
ph_qp_reference_id,
ph_enforce_price_list_yn,
decode(p_unconditional_call,'Y','N',ph_integrated_with_qp) ph_integrated_with_qp,
start_date,
end_date,
price_list_id,
date_terminated
FROM okc_k_lines_v cle
WHERE cle.dnz_chr_id=b_chr_id
AND cle.cle_id is null
AND cle.lse_id=61 -- Price Hold Line style.
AND nvl(cle.end_date,sysdate+1) > Sysdate
AND rownum=1;
SELECT cle.id id,
cle.object_version_number object_version_number,
cle.line_number line_number,
cle.lse_id lse_id,
ph_pricing_type,
ph_price_break_basis,
ph_min_qty, -- Minimum Line Quantity
ph_min_amt, -- Minimum Line Amount
ph_value,
ph_qp_reference_id,
ph_integrated_with_qp,
start_date,
end_date,
jtot_object1_code,
object1_id1,
object1_id2,
uom_code
FROM okc_k_lines_v cle,
okc_k_items_v cim
WHERE cle.cle_id=b_cle_id
AND cle.id=cim.cle_id
ORDER BY line_number;
SELECT id ,
object_version_number,
pricing_type,
value_from,
value_to,
value,
qp_reference_id,
integrated_with_qp
FROM okc_ph_line_breaks_v
WHERE cle_id=b_ph_line_id
AND nvl(integrated_with_qp,'N')='N'
order by id;
SELECT qualifier_id
FROM qp_qualifiers_v
WHERE list_header_id=b_list_header_id
AND list_line_id=-1;
SELECT pricing_attribute_id
FROM qp_pricing_attributes
WHERE LIST_LINE_ID=b_list_line_id
AND LIST_HEADER_ID=b_list_header_id;
SELECT
rgp.chr_id
,rgp.cle_id
,rul.object1_id1
,rul.object1_id2
,rul.jtot_object1_code
,rul.object2_id1
,rul.object2_id2
,rul.jtot_object2_code
,rul.object3_id1
,rul.object3_id2
,rul.jtot_object3_code
,rul.rule_information_category
,rul.rule_information1
FROM okc_rule_groups_b rgp
,okc_rules_b rul
WHERE rgp.dnz_chr_id = b_chr_id
AND rgp.cle_id IS NULL
AND rul.rgp_id = rgp.id
AND rul.rule_information_category IN ( 'SMD', 'CAN', 'FRT','PTR');
cle_cnt NUMBER := 0; -- count of lines being updated in okc_k_lines_b with new qp references;
phl_cnt NUMBER := 0; -- count of lines being updated in okc_ph_line_breaks with new qp references;
l_qp_ph_relship_tbl.delete;
l_clev_tbl.delete;
lx_clev_tbl.delete;
l_ph_line_breaks_tbl.delete;
lx_ph_line_breaks_tbl.delete;
AND p_operation_code='UPDATE' then
-- This is the case of creation and updation of Modfiers .
fnd_profile.put('QP_SOURCE_SYSTEM_CODE','OKC');
/* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
l_qp_rlship_cnt := l_qp_rlship_cnt +1;
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
/* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
l_qp_rlship_cnt := l_qp_rlship_cnt +1;
l_QUALIFIERS_tbl(l_hdr_qual_cnt).operation := QP_GLOBALS.G_OPR_DELETE;
/* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
l_qp_rlship_cnt := l_qp_rlship_cnt +1;
l_MODIFIERS_tbl(l_line_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
/* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
l_qp_rlship_cnt := l_qp_rlship_cnt +1;
l_PRICING_ATTR_tbl(l_line_pattr_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
/* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
l_qp_rlship_cnt := l_qp_rlship_cnt +1;
l_MODIFIERS_tbl(l_line_breaks_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
/* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
l_qp_rlship_cnt := l_qp_rlship_cnt +1;
l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
/* Build PL/SQL table to update Lines and line breaks with qp_refrence_id */
IF l_qp_ph_relship_tbl.first IS NOT NULL and l_call_qp_api THEN
FOR i IN l_qp_ph_relship_tbl.first..l_qp_ph_relship_tbl.last LOOP
IF l_qp_ph_relship_tbl(i).line_type ='PRICE_HOLD' then
cle_cnt := cle_cnt + 1;
okc_util.print_trace(2,'Start OKC_CONTRACT_PUB.update_contract_line');
OKC_CONTRACT_PUB.update_contract_line( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => OKC_API.G_TRUE,
p_clev_tbl => l_clev_tbl,
x_clev_tbl => lx_clev_tbl);
okc_util.print_trace(2,'End OKC_CONTRACT_PUB.update_contract_line:Status'||l_return_status);
okc_util.print_trace(2,'Start OKC_PHL_PVT.update_row');
OKC_PHL_PVT.update_row( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_okc_ph_line_breaks_v_tbl =>l_ph_line_breaks_tbl,
x_okc_ph_line_breaks_v_tbl =>lx_ph_line_breaks_tbl);
okc_util.print_trace(2,'End OKC_PHL_PVT.update_row:Status'||l_return_status);
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
SELECT chr.id ID
FROM okc_k_headers_V chr,
okc_k_lines_V cle
WHERE cle.id=b_cle_id
AND chr.id=cle.dnz_chr_id
AND chr.application_id in (510,871)
AND chr.buy_or_sell='S'
AND chr.issue_or_receive='I'
AND chr.template_yn='N'
AND chr.deleted_yn='N'
AND chr.date_terminated IS NULL
AND chr.datetime_cancelled IS NULL;
SELECT id ,
dnz_chr_id,
ph_qp_reference_id,
ph_integrated_with_qp,
start_date,
end_date
FROM okc_k_lines_v cle
WHERE cle.id=b_cle_id
AND cle.cle_id is null
AND cle.lse_id=61 -- Price Hold Line style.
AND cle.date_terminated IS NULL
AND cle.ph_qp_reference_id is not null
AND rownum=1;
SELECT cle.id id,
cle.line_number line_number,
cle.lse_id lse_id,
ph_qp_reference_id,
ph_integrated_with_qp,
start_date,
end_date
FROM okc_k_lines_v cle
WHERE cle.cle_id=b_cle_id
AND cle.ph_qp_reference_id is not null
ORDER BY line_number;
SELECT id ,
qp_reference_id,
integrated_with_qp
FROM okc_ph_line_breaks_v
WHERE cle_id=b_ph_line_id
AND qp_reference_id is not null
order by id;
l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
l_MODIFIERS_tbl(l_line_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
l_MODIFIERS_tbl(l_line_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
p_restricted_update in VARCHAR2,
p_delete_before_yn in VARCHAR2 , -- delete current lines before copying
p_commit_changes_yn in VARCHAR2 , -- commit changes after copying
x_recs_copied OUT NOCOPY NUMBER) IS
l_cnt NUMBER := 1;
select id
from okc_k_lines_v
where cle_id = p_par_id;
select id
from okc_k_lines_v
where chr_id=p_chr_id and item_to_price_yn='Y'
order by DISPLAY_SEQUENCE;
select
PRICE_LIST_ID,
PH_PRICING_TYPE,
PH_ADJUSTMENT
from okc_k_lines_v
where id=p_cle_id;
SELECT '' ID,
SFWT_FLAG,
'' CHR_ID,
DNZ_CHR_ID,
CLE_ID,
'' LINE_NUMBER,
62 LSE_ID,
STS_CODE,
'' DISPLAY_SEQUENCE,
TRN_CODE,
COMMENTS,
ITEM_DESCRIPTION,
OKE_BOE_DESCRIPTION,
COGNOMEN,
HIDDEN_IND,
PRICE_UNIT,
PRICE_UNIT_PERCENT,
'' PRICE_NEGOTIATED,
PRICE_LEVEL_IND,
INVOICE_LINE_LEVEL_IND,
DPAS_RATING,
BLOCK23TEXT,
EXCEPTION_YN,
TEMPLATE_USED,
DATE_TERMINATED,
NAME,
START_DATE,
END_DATE,
DATE_RENEWED,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PRICE_LIST_ID,
PRICING_DATE,
PRICE_LIST_LINE_ID,
LINE_LIST_PRICE,
ITEM_TO_PRICE_YN,
PRICE_BASIS_YN,
CONFIG_HEADER_ID,
CONFIG_REVISION_NUMBER,
CONFIG_COMPLETE_YN,
CONFIG_VALID_YN,
CONFIG_TOP_MODEL_LINE_ID,
CONFIG_ITEM_TYPE,
CONFIG_ITEM_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PRICE_TYPE,
CURRENCY_CODE,
SERVICE_ITEM_YN,
-- new columns for price hold
PH_PRICING_TYPE,
PH_ADJUSTMENT,
PH_PRICE_BREAK_BASIS,
PH_MIN_QTY,
PH_MIN_AMT,
PH_QP_REFERENCE_ID,
PH_VALUE,
PH_ENFORCE_PRICE_LIST_YN,
PH_INTEGRATED_WITH_QP
FROM OKC_K_LINES_V
WHERE id = p_cle_id;
SELECT ID,
CLE_ID,
CHR_ID,
CLE_ID_FOR,
DNZ_CHR_ID,
OBJECT1_ID1,
OBJECT1_ID2,
'OKX_SYSITEM' JTOT_OBJECT1_CODE,
UOM_CODE,
EXCEPTION_YN,
1 NUMBER_OF_ITEMS,
'N' PRICED_ITEM_YN
FROM OKC_K_ITEMS_V
WHERE CLE_ID = p_cle_id;
IF Nvl(p_delete_before_yn,'N') = 'Y' THEN
FOR crec IN c_cur_ph_lines( top_line_id ) LOOP
okc_contract_pub.delete_contract_line (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_id => crec.id
);
select Greatest(Nvl(Max(DISPLAY_SEQUENCE),0),Nvl(Max(LINE_NUMBER),0))
INTO m_cnt
from okc_k_lines_v
where cle_id = top_line_id;
x_clev_rec.PROGRAM_UPDATE_DATE,
x_clev_rec.PRICE_LIST_ID,
x_clev_rec.PRICING_DATE,
x_clev_rec.PRICE_LIST_LINE_ID,
x_clev_rec.LINE_LIST_PRICE,
x_clev_rec.ITEM_TO_PRICE_YN,
x_clev_rec.PRICE_BASIS_YN,
x_clev_rec.CONFIG_HEADER_ID,
x_clev_rec.CONFIG_REVISION_NUMBER,
x_clev_rec.CONFIG_COMPLETE_YN,
x_clev_rec.CONFIG_VALID_YN,
x_clev_rec.CONFIG_TOP_MODEL_LINE_ID,
x_clev_rec.CONFIG_ITEM_TYPE,
x_clev_rec.CONFIG_ITEM_ID,
x_clev_rec.ATTRIBUTE_CATEGORY,
x_clev_rec.ATTRIBUTE1,
x_clev_rec.ATTRIBUTE2,
x_clev_rec.ATTRIBUTE3,
x_clev_rec.ATTRIBUTE4,
x_clev_rec.ATTRIBUTE5,
x_clev_rec.ATTRIBUTE6,
x_clev_rec.ATTRIBUTE7,
x_clev_rec.ATTRIBUTE8,
x_clev_rec.ATTRIBUTE9,
x_clev_rec.ATTRIBUTE10,
x_clev_rec.ATTRIBUTE11,
x_clev_rec.ATTRIBUTE12,
x_clev_rec.ATTRIBUTE13,
x_clev_rec.ATTRIBUTE14,
x_clev_rec.ATTRIBUTE15,
x_clev_rec.PRICE_TYPE,
x_clev_rec.CURRENCY_CODE,
x_clev_rec.SERVICE_ITEM_YN,
x_clev_rec.PH_PRICING_TYPE,
x_clev_rec.PH_ADJUSTMENT,
x_clev_rec.PH_PRICE_BREAK_BASIS,
x_clev_rec.PH_MIN_QTY,
x_clev_rec.PH_MIN_AMT,
x_clev_rec.PH_QP_REFERENCE_ID,
x_clev_rec.PH_VALUE,
x_clev_rec.PH_ENFORCE_PRICE_LIST_YN,
x_clev_rec.PH_INTEGRATED_WITH_QP;
p_restricted_update => p_restricted_update,
p_clev_rec => x_clev_rec,
x_clev_rec => xn_clev_rec
);