1 PACKAGE BODY okl_trans_pricing_params_pvt AS
2 /* $Header: OKLRSPMB.pls 120.5 2006/07/11 10:02:09 dkagrawa noship $*/
3
4 PROCEDURE create_trans_pricing_params(
5 p_api_version IN NUMBER
6 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
7 ,x_return_status OUT NOCOPY VARCHAR2
8 ,x_msg_count OUT NOCOPY NUMBER
9 ,x_msg_data OUT NOCOPY VARCHAR2
10 ,p_tpp_rec IN tpp_rec_type
11 ,p_chr_id IN NUMBER DEFAULT Okl_Api.G_MISS_NUM
12 ,p_gts_id IN NUMBER DEFAULT Okl_Api.G_MISS_NUM
13 ,p_sif_id IN NUMBER )
14 IS
15 BEGIN
16 NULL;
17 END create_trans_pricing_params;
18
19
20 PROCEDURE create_trans_pricing_params(
21 p_api_version IN NUMBER
22 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
23 ,x_return_status OUT NOCOPY VARCHAR2
24 ,x_msg_count OUT NOCOPY NUMBER
25 ,x_msg_data OUT NOCOPY VARCHAR2
26 ,p_tpp_tbl IN tpp_tbl_type
27 ,p_chr_id IN NUMBER DEFAULT Okl_Api.G_MISS_NUM
28 ,p_gts_id IN NUMBER DEFAULT Okl_Api.G_MISS_NUM
29 ,p_sif_id IN NUMBER )
30 IS
31 l_tpp_tbl tpp_tbl_type;
32 l_spmv_tbl spmv_tbl_type;
33 l_sif_id okl_sif_pricing_params.sif_id%TYPE := p_sif_id;
34 l_gts_id okl_st_gen_tmpt_sets.id%TYPE := p_gts_id;
35 l_chr_id okc_k_headers_b.id%TYPE := p_chr_id;
36 i NUMBER := 0;
37 j NUMBER := 0;
38 l_found VARCHAR2(1) := 'N';
39 l_api_version CONSTANT NUMBER := 1;
40 l_row_notfound BOOLEAN :=TRUE;
41
42 l_id okl_st_gen_prc_params.id%TYPE;
43 l_name okl_st_gen_prc_params.name%TYPE;
44 l_display_yn okl_st_gen_prc_params.display_yn%TYPE;
45 l_update_yn okl_st_gen_prc_params.update_yn%TYPE;
46 l_default_value okl_st_gen_prc_params.default_value%TYPE;
47 l_prc_eng_ident okl_st_gen_prc_params.prc_eng_ident%TYPE;
48 l_description okl_st_gen_prc_params.description%TYPE;
49
50 CURSOR st_gen_prc_params(p_gts_id NUMBER) IS
51 SELECT pp.id, pp.name, pp.display_yn,pp.update_yn,
52 pp.default_value, pp.prc_eng_ident, pp.description
53 FROM okl_st_gen_tmpt_sets temp_set,
54 okl_st_gen_templates template,
55 okl_st_gen_prc_params pp
56 WHERE temp_set.id = template.gts_id
57 AND template.id = pp.gtt_id
58 AND temp_set.id = p_gts_id;
59
60 CURSOR get_gts_id(p_chr_id NUMBER) IS
61 SELECT TST.ID GTS_ID
62 FROM OKL_ST_GEN_TMPT_SETS TST,
63 OKL_AE_TMPT_SETS_V AES,
64 OKL_PRODUCTS_V PDT,
65 OKL_K_HEADERS KHR
66 WHERE TST.ID = AES.GTS_ID AND
67 AES.ID = PDT.AES_ID AND
68 PDT.ID = KHR.PDT_ID AND
69 KHR.ID =p_chr_id;
70
71 BEGIN
72 x_return_status :=OKC_API.G_RET_STS_SUCCESS;
73 l_tpp_tbl := p_tpp_tbl;
74
75 delete_pricing_params(l_chr_id,x_return_status);
76
77 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
78 RAISE G_EXCEPTION_HALT_VALIDATION;
79 END IF;
80
81 IF ( (l_gts_id IS NULL OR l_gts_id = OKC_API.G_MISS_NUM) AND
82 (l_chr_id IS NULL OR l_chr_id = OKC_API.G_MISS_NUM) )
83 THEN
84 x_return_status := Okc_Api.G_RET_STS_ERROR;
85 OKL_API.Set_Message(p_app_name => G_APP_NAME,
86 p_msg_name => G_REQUIRED_VALUE,
87 p_token1 => G_COL_NAME_TOKEN,
88 p_token1_value => 'Contract Number');
89 RAISE G_EXCEPTION_HALT_VALIDATION;
90 ELSIF ( (l_gts_id IS NULL OR l_gts_id = OKC_API.G_MISS_NUM) AND
91 (l_chr_id IS NOT NULL OR l_chr_id <> OKC_API.G_MISS_NUM) )
92 THEN
93 OPEN get_gts_id(l_chr_id);
94 FETCH get_gts_id INTO l_gts_id;
95 l_row_notfound := get_gts_id%NOTFOUND;
96 CLOSE get_gts_id;
97
98 IF l_row_notfound THEN
99 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
100 p_msg_name => 'OKL_INVALID_CONTRACT_ID',
101 p_token1 => 'CONT_ID',
102 p_token1_value => to_char(l_chr_id));
103 RAISE G_EXCEPTION_HALT_VALIDATION;
104 END IF;
105 END IF;
106
107 IF (l_gts_id IS NOT NULL OR l_gts_id <> OKC_API.G_MISS_NUM) THEN
108 OPEN st_gen_prc_params(l_gts_id);
109 LOOP
110 FETCH st_gen_prc_params INTO l_id, l_name,l_display_yn,l_update_yn,
111 l_default_value,l_prc_eng_ident,l_description;
112 EXIT WHEN st_gen_prc_params%NOTFOUND;
113 l_spmv_tbl(j).parameter_value := l_default_value;
114 IF (l_display_yn = 'Y' AND l_update_yn = 'Y')
115 THEN
116 l_found := 'N';
117 IF (l_tpp_tbl.COUNT > 0)
118
119 THEN
120 i := l_tpp_tbl.FIRST;
121 LOOP
122 IF l_tpp_tbl(i).gtp_id = l_id
123 THEN
124 --Modified by kthiruva for on 06-Jan-2005.
125 -- A String was being compared against G_MISS_NUM instead of G_MISS_CHAR
126 --Bug 4062792 - Start of Changes
127 IF l_tpp_tbl(i).parameter_value IS NOT NULL AND
128 l_tpp_tbl(i).parameter_value <> OKC_API.G_MISS_CHAR
129 --Bug 4062792 - End of Changes
130 THEN
131 l_spmv_tbl(j).parameter_value := l_tpp_tbl(i).parameter_value;
132 END IF;
133 l_found := 'Y';
134 END IF;
135 EXIT WHEN (i = l_tpp_tbl.LAST OR l_found = 'Y');
136 i := l_tpp_tbl.NEXT(i);
137 END LOOP;
138 END IF;
139 END IF;
140 l_spmv_tbl(j).object_version_number := 1;
141 /*commented by suresh gorantla 04/03/05*/
142 --l_spmv_tbl(j).sif_id := l_sif_id;
143 l_spmv_tbl(j).khr_id := l_chr_id;
144 l_spmv_tbl(j).name := l_name;
145 l_spmv_tbl(j).display_yn := l_display_yn;
146 l_spmv_tbl(j).update_yn := l_update_yn;
147 l_spmv_tbl(j).default_value := l_default_value;
148 l_spmv_tbl(j).prc_eng_ident := l_prc_eng_ident;
149 l_spmv_tbl(j).description := l_description;
150 j := j + 1;
151 END LOOP;
152
153 CLOSE st_gen_prc_params;
154
155 OKL_SPM_PVT.insert_row(p_api_version => l_api_version,
156 p_init_msg_list => p_init_msg_list,
157 x_return_status => x_return_status,
158 x_msg_count => x_msg_count,
159 x_msg_data => x_msg_data,
160 p_spmv_tbl =>l_spmv_tbl);
161 END IF;
162
163 EXCEPTION
164 WHEN OTHERS THEN
165 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
166 RAISE G_EXCEPTION_HALT_VALIDATION;
167 END create_trans_pricing_params;
168
169 --User navigates to the same screen second time before Generate the Streams we need to remove the records
170 --whatever inserted in the first time
171
172 PROCEDURE delete_pricing_params(
173 p_chr_id IN NUMBER
174 ,x_return_status OUT NOCOPY VARCHAR2)
175 IS
176 BEGIN
177 DELETE FROM OKL_SIF_PRICING_PARAMS
178 WHERE khr_ID = p_chr_id
179 AND sif_id IS NULL;
180
181 x_return_status := OKC_API.G_RET_STS_SUCCESS;
182 EXCEPTION
183 WHEN NO_DATA_FOUND THEN
184 x_return_status := OKC_API.G_RET_STS_SUCCESS;
185 WHEN OTHERS THEN
186 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
187 END delete_pricing_params;
188
189 END; --OKL_TRANS_PRICING_PARAMS_PVT
[Home] [Help]