[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** also updated to the same status.
** If the Contract has to be Cancelled then the source for the
** cancel action needs to be passed (i.e, MANUAL or IBTRANSFER). -- made change from 'TRANSFER' to 'IBTRANSFER'
** In cancellation case the amount for the Header and Lines is
** updated to reflect the cancel action.
*/
procedure Update_header_status(x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_init_msg_list in varchar2,
p_id in number,
p_new_sts_code in varchar2,
p_canc_reason_code in varchar2,
p_old_sts_code in varchar2,
p_comments in varchar2,
p_term_cancel_source in varchar2,
p_date_cancelled in date,
p_validate_status in varchar2) is
l_chr_id number;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_HEADER_STATUS';
'100: Entered UPDATE_HEADER_STATUS');
'130: Calling Update_Header_status with chrv_tbl populated');
Update_header_status( x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_init_msg_list => FND_API.G_FALSE,
p_chrv_tbl => l_chrv_tbl,
p_canc_reason_code => p_canc_reason_code,
p_comments => p_comments,
p_term_cancel_source => p_term_cancel_source,
p_date_cancelled => p_date_cancelled);
'170: Completed Update_header_status succesfully');
procedure Update_header_status(x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_init_msg_list in varchar2,
p_chrv_tbl in OUT NOCOPY chrv_tbl_type,
p_canc_reason_code in varchar2,
p_comments in varchar2,
p_term_cancel_source in varchar2,
p_date_cancelled in date,
p_validate_status in varchar2) is
p_control_rec okc_util.okc_control_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_HEADER_STATUS';
l_line_update varchar2(1);
SELECT wf_item_key
FROM oks_k_headers_b
WHERE chr_id = p_contract_id;
'400: Entere Update_Header_Status');
'430: calling okc_contract_pub.update_contract_header');
OKC_CONTRACT_PUB.update_contract_header(
p_api_version => g_api_version,
P_INIT_MSG_LIST => 'F',
x_return_status => x_return_status,
x_msg_count => g_msg_count,
x_msg_data => g_msg_data,
p_restricted_update => 'T',
p_chrv_rec => p_chrv_tbl(i),
p_control_rec => p_control_rec,
x_chrv_rec => l_chrv_tbl(i));
'440: Calling oks_change_status_pvt.update_line_status ');
OKS_CHANGE_STATUS_PVT.update_line_status(
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_init_msg_list => l_init_msg_list,
p_id => p_chrv_tbl(i).id,
p_cle_id => l_cle_id,
p_new_sts_code => p_chrv_tbl(i).new_sts_code,
p_canc_reason_code => p_canc_reason_code,
p_old_sts_code => p_chrv_tbl(i).old_sts_code,
p_old_ste_code => p_chrv_tbl(i).old_ste_code,
p_new_ste_code => p_chrv_tbl(i).new_ste_code,
p_term_cancel_source => p_term_cancel_source,
p_date_cancelled => p_date_cancelled,
p_comments => p_comments,
p_validate_status => 'N');
'485: exiting Update_header_status ');
SELECT object_version_number, decode(
NVL(sign(months_between(START_DATE,sysdate+1)),1),-1,decode(
NVL(sign(months_between(END_DATE,sysdate-1)),1),1,'ACTIVE','EXPIRED'),'SIGNED' )
FROM okc_k_headers_b
WHERE id = p_id;
select 'Y'
into l_renewed
from okc_operation_instances OIE,
okc_class_operations COP
where OIE.cop_id=COP.id
and COP.opn_code in ('RENEWAL', 'REN_CON')
and target_chr_id = l_chr_id
and rownum = 1;
select distinct contract_number, contract_number_modifier,
contract_number||decode(contract_number_modifier, NULL,'','-'||contract_number_modifier) contracts
from okc_k_headers_b CHR,
okc_operation_lines OLI,
okc_operation_instances OIE, --**
okc_class_operations COP --**
where OLI.subject_chr_id = l_chr_id
and OLI.object_chr_id = chr.id
and OLI.oie_id = OIE.id --**
and OIE.cop_id = COP.id --**
and COP.opn_code in ('RENEWAL', 'REN_CON') --**
and OLI.subject_cle_id > 0;
select contract_number, contract_number_modifier,
contract_number||decode(contract_number_modifier, NULL,'','-'||contract_number_modifier) contracts
from okc_k_headers_b CHR,
okc_operation_lines OLI,
okc_operation_instances OIE, --**
okc_class_operations COP --**
where OLI.subject_chr_id = l_chr_id
and OLI.subject_cle_id = p_cle_id
and OLI.object_chr_id = chr.id
and OLI.oie_id = OIE.id --**
and OIE.cop_id = COP.id --**
and COP.opn_code in ('RENEWAL', 'REN_CON') --**
and OLI.subject_cle_id > 0;
SELECT distinct contract_number, contract_number_modifier,
contract_number||decode(contract_number_modifier, NULL,'','-'||contract_number_modifier) contracts
FROM okc_k_headers_b CHR,
okc_operation_lines OLI1,
okc_operation_lines OLI2,
okc_operation_instances OIE1,
okc_class_operations COP1,
okc_operation_instances OIE2,
okc_class_operations COP2
WHERE CHR.id = OLI1.subject_chr_id
and OLI1.object_chr_id = OLI2.object_chr_id
and OLI1.oie_id = OIE1.id
and OIE1.cop_id = COP1.id
and COP1.opn_code in ('RENEWAL', 'REN_CON')
and OLI2.oie_id = OIE2.id
and OIE2.cop_id = COP2.id
and COP2.opn_code in ('RENEWAL', 'REN_CON')
and OLI2.subject_chr_id = l_chr_id
and OLI1.subject_chr_id <> l_chr_id
and OLI2.subject_cle_id > 0
and OLI1.subject_cle_id > 0;
SELECT contract_number, contract_number_modifier,
contract_number||decode(contract_number_modifier, NULL,'','-'||contract_number_modifier) contracts
FROM okc_k_headers_b CHR,
okc_k_lines_b CLE,
okc_statuses_b STE,
okc_operation_lines OLI1,
okc_operation_lines OLI2,
okc_operation_instances OIE1,
okc_class_operations COP1,
okc_operation_instances OIE2,
okc_class_operations COP2
WHERE CHR.id = OLI1.subject_chr_id
and OLI1.object_chr_id = OLI2.object_chr_id
and OLI1.oie_id = OIE1.id
and OIE1.cop_id = COP1.id
and COP1.opn_code in ('RENEWAL', 'REN_CON')
and OLI2.oie_id = OIE2.id
and OIE2.cop_id = COP2.id
and COP2.opn_code in ('RENEWAL', 'REN_CON')
and CHR.id = CLE.dnz_chr_id
and CLE.sts_code = STE.Code
and STE.STE_CODE = 'ENTERED' -- this is a retrictive condn.
and CLE.id = OLI1.subject_cle_id
and OLI2.subject_chr_id = l_chr_id
and OLI1.subject_chr_id <> l_chr_id
and OLI2.subject_cle_id = p_cle_id
and OLI1.object_cle_id = OLI2.object_cle_id
and OLI1.subject_cle_id <> p_cle_id;
select distinct 'Y'
from OKC_K_HEADERS_B CHR,
okc_operation_lines OLI,
okc_operation_instances OIE,
okc_class_operations COP
where chr.id = oli.object_chr_id
and OLI.oie_id = OIE.id
and OIE.cop_id = COP.id
and COP.opn_code in ('RENEWAL', 'REN_CON')
and oli.subject_chr_id = l_chr_id
and CHR.date_renewed is NOT NULL;
select distinct 'Y'
from OKC_K_LINES_B CLE,
okc_operation_lines OLI,
okc_operation_instances OIE,
okc_class_operations COP
where cle.id = oli.object_cle_id
and OLI.oie_id = OIE.id
and OIE.cop_id = COP.id
and COP.opn_code in ('RENEWAL', 'REN_CON')
and oli.subject_chr_id = l_chr_id
and Cle.date_renewed is NOT NULL;
SELECT distinct 'Y'
FROM okc_k_headers_b CHR,
okc_statuses_b STS,
okc_operation_lines OLI1,
okc_operation_lines OLI2,
okc_operation_instances OIE1, --**
okc_class_operations COP1, --**
okc_operation_instances OIE2, --**
okc_class_operations COP2 --**
WHERE CHR.id = OLI1.subject_chr_id
and OLI1.oie_id = OIE1.id --**
and OIE1.cop_id = COP1.id --**
and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
and OLI2.oie_id = OIE2.id --**
and OIE2.cop_id = COP2.id --**
and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
AND OLI1.object_chr_id = OLI2.object_chr_id
AND OLI2.subject_chr_id = l_chr_id
AND OLI1.subject_cle_id IS NULL
AND OLI2.subject_cle_id IS NULL
AND CHR.sts_code = STS.code
AND STS.ste_code = 'ENTERED'
AND OLI1.active_yn = 'Y'
AND OLI1.process_flag = 'P'
AND OLI2.process_flag = 'P';
SELECT distinct 'Y'
FROM okc_k_headers_b CHR,
okc_k_lines_b CLE,
okc_statuses_b STS,
okc_operation_lines OLI1,
okc_operation_lines OLI2,
okc_operation_instances OIE1, --**
okc_class_operations COP1, --**
okc_operation_instances OIE2, --**
okc_class_operations COP2 --**
WHERE CHR.id = OLI1.subject_chr_id
and OLI1.oie_id = OIE1.id --**
and OIE1.cop_id = COP1.id --**
and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
and OLI2.oie_id = OIE2.id --**
and OIE2.cop_id = COP2.id --**
and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
AND OLI1.object_chr_id = OLI2.object_chr_id
AND OLI2.subject_chr_id = l_chr_id
AND OLI1.subject_chr_id <> l_chr_id
AND OLI1.subject_cle_id <> p_cle_id
AND OLI2.subject_cle_id = p_cle_id
AND CLE.sts_code = STS.code
AND STS.ste_code <> 'ENTERED'
AND CHR.id = CLE.DNZ_CHR_ID
AND CLE.ID = OLI1.subject_cle_id
AND OLI1.object_cle_id = OLI2.object_cle_id
AND OLI1.active_yn = 'Y'
AND OLI1.process_flag = 'P'
AND OLI2.process_flag = 'P';
SELECT distinct 'Y'
FROM okc_k_headers_b CHR,
okc_statuses_b STS,
okc_operation_lines OLI1,
okc_operation_lines OLI2,
okc_operation_instances OIE1, --**
okc_class_operations COP1, --**
okc_operation_instances OIE2, --**
okc_class_operations COP2 --**
WHERE CHR.id = OLI1.subject_chr_id
and OLI1.oie_id = OIE1.id --**
and OIE1.cop_id = COP1.id --**
and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
and OLI2.oie_id = OIE2.id --**
and OIE2.cop_id = COP2.id --**
and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
AND OLI1.object_chr_id = OLI2.object_chr_id
AND OLI2.subject_chr_id = l_chr_id
AND OLI1.subject_cle_id IS NULL
AND OLI2.subject_cle_id IS NULL
AND CHR.STS_CODE = STS.CODE
AND STS.ste_code not in ('ENTERED', 'CANCELLED')
AND OLI1.active_yn = 'Y'
AND OLI1.process_flag = 'P'
AND OLI2.process_flag = 'P';
SELECT distinct 'Y'
FROM okc_k_headers_b CHR,
okc_k_lines_b CLE,
okc_statuses_b STS,
okc_operation_lines OLI1,
okc_operation_lines OLI2,
okc_operation_instances OIE1, --**
okc_class_operations COP1, --**
okc_operation_instances OIE2, --**
okc_class_operations COP2 --**
WHERE CHR.id = OLI1.subject_chr_id
and CHR.id = CLE.dnz_chr_id
and OLI1.oie_id = OIE1.id --**
and OIE1.cop_id = COP1.id --**
and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
and OLI2.oie_id = OIE2.id --**
and OIE2.cop_id = COP2.id --**
and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
AND OLI1.object_chr_id = OLI2.object_chr_id
AND OLI2.subject_chr_id = l_chr_id
AND OLI1.subject_cle_id IS NULL
AND OLI2.subject_cle_id IS NULL
AND CLE.STS_CODE = STS.CODE
AND STS.ste_code not in ('ENTERED', 'CANCELLED')
AND OLI1.object_cle_id = OLI2.object_cle_id
AND OLI2.subject_cle_id = p_cle_id;
select distinct 'Y'
from okc_operation_lines OLI1,
okc_operation_lines OLI2,
okc_operation_instances OIE1, --**
okc_class_operations COP1, --**
okc_operation_instances OIE2, --**
okc_class_operations COP2 --**
where OLI1.object_chr_id = OLI2.object_chr_id
and OLI1.oie_id = OIE1.id --**
and OIE1.cop_id = COP1.id --**
and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
and OLI2.oie_id = OIE2.id --**
and OIE2.cop_id = COP2.id --**
and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
and OLI1.subject_chr_id <> OLI2.subject_chr_id
and OLI1.subject_chr_id = l_chr_id
and OLI2.subject_cle_id > 0
and OLI1.subject_cle_id > 0;
select distinct 'Y'
from okc_operation_lines OLI1,
okc_operation_lines OLI2,
okc_operation_instances OIE1, --**
okc_class_operations COP1, --**
okc_operation_instances OIE2, --**
okc_class_operations COP2 --**
where OLI1.object_chr_id = OLI2.object_chr_id
and OLI1.oie_id = OIE1.id --**
and OIE1.cop_id = COP1.id --**
and COP1.opn_code in ('RENEWAL', 'REN_CON') --**
and OLI2.oie_id = OIE2.id --**
and OIE2.cop_id = COP2.id --**
and COP2.opn_code in ('RENEWAL', 'REN_CON') --**
and OLI1.subject_chr_id <> OLI2.subject_chr_id
and OLI1.subject_chr_id = l_chr_id
and OLI2.subject_cle_id <> OLI1.subject_cle_id
and OLI1.subject_cle_id = p_cle_id;
procedure Update_line_status (x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_init_msg_list in varchar2,
p_id in number,
p_cle_id in number,
p_new_sts_code in varchar2,
p_canc_reason_code in varchar2,
p_old_sts_code in varchar2,
p_old_ste_code in varchar2,
p_new_ste_code in varchar2,
p_term_cancel_source in varchar2,
p_date_cancelled in Date,
p_comments in varchar2,
p_validate_status in varchar2) is
l_api_name Varchar2(100) := 'UPDATE_LINE_STATUS';
l_line_update Varchar2(1) := 'Y';
select ste_code
from okc_statuses_v
where code=p_new_sts_code;
select ste_code
from okc_statuses_v
where code=p_old_sts_code;
select id
from okc_k_lines_b
where id=p_cle_id
and cle_id is null;
select code
from okc_statuses_v
where ste_code='SIGNED'
and default_yn='Y';
select code
from okc_statuses_v
where ste_code='EXPIRED'
and default_yn='Y';
SELECT to_char (major_version)||'.'||to_char(minor_version)
FROM okc_k_vers_numbers
WHERE chr_id=p_chr_id;
SELECT scs_code
FROM okc_k_headers_b
WHERE id= p_id;
'600: Entered UPDATE_LINE_STATUS');
open c_lines for select L.id, decode(l_type,'ACTIVE',
decode(sign(months_between(sysdate-1,NVL(L.end_date,sysdate))),-1,
decode(sign(months_between(L.start_date-1,sysdate)),-1,p_new_sts_code,l_signed)
,l_expired)
,p_new_sts_code) code, L.lse_id,
L.object_version_number
from okc_k_lines_v L, okc_statuses_v ls
where L.dnz_chr_id = p_id
and ls.code = L.sts_code
and ls.ste_code in (l_old_type,'SIGNED')
and NVL(L.term_cancel_source,'MANUAL') NOT IN ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE'); --To ignore lines/sublines due to IBTRANSFER, IBRETURN, IBTERMINATE, IBREPLACE
open c_lines for select L.id, decode(l_type,'ACTIVE',
decode(sign(months_between(sysdate-1,NVL(L.end_date,sysdate))),-1,
decode(sign(months_between(L.start_date-1,sysdate)),-1,p_new_sts_code,l_signed)
,l_expired)
,p_new_sts_code) code, L.lse_id,
L.object_version_number
from okc_k_lines_v L, okc_statuses_v ls
where L.dnz_chr_id = p_id
and ls.code = L.sts_code
and ls.ste_code in (l_type,l_old_type)
and NVL(L.term_cancel_source,'MANUAL') NOT IN ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE'); --To ignore lines/sublines due to IBTRANSFER, IBRETURN, IBTERMINATE, IBREPLACE
open c_lines for select L.id,p_new_sts_code code, L.lse_id,L.object_version_number
from okc_k_lines_b L
where L.dnz_chr_id = p_id
and (L.id = p_cle_id or
L.cle_id = p_cle_id)
and EXISTS (select 'x'
from okc_statuses_b
where code = l.sts_code
and ste_code = 'CANCELLED')
and NVL(L.term_cancel_source,'MANUAL') NOT IN ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE'); --To ignore lines/sublines due to IBTRANSFER, IBRETURN, IBTERMINATE, IBREPLACE
open c_lines for select L.id, p_new_sts_code code, L.lse_id,L.object_version_number
from okc_k_lines_b L
where L.dnz_chr_id = p_id
and (L.id = p_cle_id or
L.cle_id = p_cle_id)
and EXISTS( select 'x'
from OKC_STATUSES_B
where code = l.sts_code
and ste_code = l_old_type)
and NVL(L.term_cancel_source,'MANUAL') NOT IN ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE'); --To ignore lines/sublines due to IBTRANSFER, IBRETURN, IBTERMINATE, IBREPLACE
open c_lines for select L.id, p_new_sts_code code, L.lse_id,L.object_version_number
from okc_k_lines_b L
where L.id = p_cle_id
and NVL(L.term_cancel_source,'MANUAL') NOT IN ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE'); --To ignore lines/sublines due to IBTRANSFER, IBRETURN, IBTERMINATE, IBREPLACE
'650: Calling OKC_CONTRACT_PUB.update_contract_line ');
OKC_CONTRACT_PUB.update_contract_line(
p_api_version => 1,
P_INIT_MSG_LIST => 'T',
p_restricted_update => 'T',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_tbl => l_clev_tbl,
x_clev_tbl => l1_clev_tbl);
'660: Succesfully completed OKC_CONTRACT_PUB.update_contract_line ');
'690: calling okc_contract_pvt.update_contract_amount, p_cle_id is not null '||p_cle_id);
OKC_CONTRACT_PVT.Update_contract_amount(
p_api_version => 1,
p_init_msg_list => 'F',
p_id => p_id,
p_from_ste_code => p_old_ste_code,
p_to_ste_code => p_new_ste_code,
p_cle_id => p_cle_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'695: calling update_contract_tax_amount, p_cle_id is not null '||p_cle_id);
UPDATE_CONTRACT_TAX_AMOUNT(
p_api_version => 1,
p_init_msg_list => 'F',
p_id => p_id,
p_from_ste_code => p_old_ste_code,
p_to_ste_code => p_new_ste_code,
p_cle_id => p_cle_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'700: calling okc_contract_pvt.update_contract_amount, p_cle_id is null ');
OKC_CONTRACT_PVT.Update_contract_amount(
p_api_version => 1,
p_init_msg_list => 'F',
p_id => p_id,
p_from_ste_code => p_old_ste_code,
p_to_ste_code => p_new_ste_code,
p_cle_id => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'705: calling update_contract_tax_amount, p_cle_id is null');
UPDATE_CONTRACT_TAX_AMOUNT (
p_api_version => 1,
p_init_msg_list => 'F',
p_id => p_id,
p_from_ste_code => p_old_ste_code,
p_to_ste_code => p_new_ste_code,
p_cle_id => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'730: succesfully complete update_line_status ');
Select ste_code from okc_statuses_b where code = p_code;
Select sts_code, start_date, end_date from okc_k_headers_b where id = p_id;
Select sts_code, start_date, end_date from okc_k_lines_b where id = p_id;
select 'Y' INTO l_allowed_status from dual where (l_new_sts_code,l_new_ste_code) in
(select
S.CODE STATUS_CODE
,S.STE_CODE STE_CODE
from
okc_statuses_v S
,fnd_lookups ST
where
S.STE_CODE in
(
NVL(l_old_ste_code,'ENTERED')
,decode(l_old_ste_code,
NULL, 'CANCELLED',
'ENTERED','CANCELLED',
'ACTIVE','HOLD',
'SIGNED','HOLD',
'HOLD',decode(
NVL(sign(months_between
(l_start_date,sysdate+1)),1),
-1,decode(
NVL(sign(months_between(l_end_date,sysdate-1)),
1),1,'ACTIVE'
,'EXPIRED'),'SIGNED')))
and sysdate between s.start_date and nvl(s.end_date,sysdate)
and st.lookup_type='OKC_STATUS_TYPE'
and st.lookup_code=s.ste_code
and sysdate between st.start_date_active and
nvl(st.end_date_active,sysdate)
and ST.enabled_flag='Y'
and S.code<>NVL(l_old_sts_code,'ENTERED')
and l_old_sts_code not like 'QA%HOLD'
and S.code not like 'QA%HOLD'
AND l_old_ste_code <> 'CANCELLED'
UNION ALL
SELECT S.CODE STATUS_CODE
,S.STE_CODE STE_CODE1
FROM OKC_STATUSES_V S
,FND_LOOKUPS ST
WHERE S.STE_CODE in ('ENTERED', 'CANCELLED')
AND SYSDATE BETWEEN S.START_DATE AND NVL(S.END_DATE, SYSDATE)
AND ST.LOOKUP_TYPE = 'OKC_STATUS_TYPE'
AND ST.LOOKUP_CODE=S.STE_CODE
AND SYSDATE BETWEEN ST.START_DATE_ACTIVE AND NVL(ST.END_DATE_ACTIVE, SYSDATE)
AND ST.ENABLED_FLAG = 'Y'
AND S.code <> l_old_sts_code
AND l_old_ste_code='CANCELLED');
The Header and Line Tax Amounts should be updated when Change Status action is taken
at the header/line/subline level. This is to ensure that the new calcualated Tax Amount
ignores cancelled top lines/sublines.
A new procedure Update_Contract_Tax_Amount is created which will be called when Change Status
action is taken on the header/line/subline level and after Update_Contract_Amount has
been called for the same.
*/
PROCEDURE UPDATE_CONTRACT_TAX_AMOUNT (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
p_id IN NUMBER,
p_from_ste_code IN VARCHAR2,
p_to_ste_code IN VARCHAR2,
p_cle_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
IS
l_cle_id Number := NULL;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_TAX_AMOUNT';
Select cle_id
from okc_k_lines_b
where id = p_cle_id
and dnz_chr_id = p_id;
Select sle.tax_amount
from okc_k_lines_b cle, oks_k_lines_b sle
where cle.id = p_cle_id
and cle.dnz_chr_id = p_id
and cle.id = sle.cle_id
and cle.dnz_chr_id = sle.dnz_chr_id;
select nvl(sum(nvl(tax_amount,0)),0)
from okc_k_lines_b cle, oks_k_lines_b sle
where cle.dnz_chr_id = p_id
and cle.lse_id in (1, 12, 14, 19, 46)
and cle.cle_id is null
and cle.id = sle.cle_id
and cle.dnz_chr_id = sle.dnz_chr_id;
select lse_id
from okc_k_lines_b
where id=p_cle_id;
'800: Entered UPDATE_CONTRACT_TAX_AMOUNT ');
Update oks_k_lines_b
set tax_amount= nvl(tax_amount, 0) - l_sub_line_tax_amt
where dnz_chr_id = p_id
and cle_id = l_cle_id;
Update oks_k_lines_b
set tax_amount= Nvl(tax_amount, 0) - l_sub_line_tax_amt
Where cle_id = p_cle_id
and dnz_chr_id = p_id;
Update oks_k_lines_b
set tax_amount= nvl(tax_amount, 0) + l_sub_line_tax_amt
where dnz_chr_id = p_id
and cle_id = l_cle_id;
Update oks_k_lines_b
set tax_amount= 0
where dnz_chr_id = p_id
and cle_id = p_cle_id;
Update oks_k_lines_b
set tax_amount= l_Tax_Value
where dnz_chr_id = p_id
and cle_id = p_cle_id;
Update oks_k_lines_b
set tax_amount=
(Select nvl(sum(nvl(tax_amount,0)),0)
from okc_k_lines_b cle, oks_k_lines_b sle
where cle.cle_id = p_cle_id
and cle.lse_id in (7,8,9,10,11,18,25,35)
and cle.dnz_chr_id = p_id
and cle.id = sle.cle_id
and cle.dnz_chr_id = sle.dnz_chr_id
and cle.date_cancelled is NULL -- Bug 5474479
)
where dnz_chr_id = p_id
and cle_id = p_cle_id;
update oks_k_lines_b
set tax_amount = 0
where dnz_chr_id = p_id
and cle_id IN
(select id
from okc_k_lines_b
where cle_id is null
and dnz_chr_id = p_id
and lse_id in (1, 12, 14, 19, 46)
);
update oks_k_lines_b oks1
set oks1.tax_amount =
(
select nvl(sum(nvl(tax_amount,0)),0)
from oks_k_lines_b oks2, okc_k_lines_b okc2
where oks2.cle_id = okc2.id
and oks2.dnz_chr_id = okc2.dnz_chr_id
and okc2.dnz_chr_id = p_id
and okc2.cle_id = oks1.cle_id
and okc2.date_cancelled IS NULL -- Bug 5474479.
)
where oks1.dnz_chr_id = p_id
and oks1.cle_id IN
(select id
from okc_k_lines_b okc1
where okc1.cle_id is null
and okc1.lse_id in (1, 12, 14, 19) --removed 46 (subscription type)
and okc1.dnz_chr_id = p_id
and okc1.date_cancelled IS NULL -- Bug 5474479.
);
'1130: Calling UPDATE_SUBSCRIPTION_TAX_AMOUNT ' || p_id);
UPDATE_SUBSCRIPTION_TAX_AMOUNT(
p_api_version => 1,
p_init_msg_list => 'F',
p_id => p_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'1140: Succesfully completed UPDATE_SUBSCRIPTION_TAX_AMOUNT ');
Update OKS_K_headers_b
set tax_amount = l_hdr_tax_amt
Where chr_id = p_id;
END UPDATE_CONTRACT_TAX_AMOUNT;
PROCEDURE UPDATE_SUBSCRIPTION_TAX_AMOUNT(
p_api_version IN NUMBER,
p_init_msg_list IN varchar2 default FND_API.G_FALSE,
p_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
Cursor get_K_subscription_lines IS
select id
from okc_k_lines_b
where cle_id is null
and lse_id = 46
and dnz_chr_id = p_id;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SUBSCRIPTION_TAX_AMOUNT';
'1150: Entered UPDATE_SUBSCRIPTION_TAX_AMOUNT ');
Update oks_k_lines_b
set tax_amount= l_subs_tax_Tbl(I)
where dnz_chr_id = p_id
and cle_id = l_id_Tbl(I);
'1165: Successfully bulk updated tax_amount of toplines of type subscription of contract '|| p_id);
END UPDATE_SUBSCRIPTION_TAX_AMOUNT;