[Home] [Help]
PACKAGE BODY: APPS.OKS_KTO_INSREL_PUB
Source
4
1 PACKAGE BODY OKS_KTO_INSREL_PUB AS
2 /* $Header: OKSPOIRB.pls 120.0 2005/05/25 17:41:33 appldev noship $ */
3
5
6
7 -------------------------------------------------------------------------------
8 --
9 -- global package structures
10 --
11 -------------------------------------------------------------------------------
12 --
13 -- global constants
14 --
15 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
16 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
17 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
18 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
19 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_OC_INT_PUB';
20 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
21 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
22 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_FORM_UNABLE_TO_RESERVE_REC;
23 G_API_TYPE VARCHAR2(30) := '_PROCESS';
24
25 L_LOG_ENABLED VARCHAR2(200);
26
27
28 -------------------------------------------------------------------------------
29 --
30 -- APIs: K->O
31 --
32 -------------------------------------------------------------------------------
33
34 -- Procedure: create_instance_rel
35 -- Version: 1.0
36 -- Purpose: Create instance relationship between subscription item instance
37 --- and instance created from the order management
38
39
40
41 PROCEDURE create_instance_rel(ERRBUF OUT NOCOPY VARCHAR2
42 ,RETCODE OUT NOCOPY NUMBER
43 ,p_contract_id IN okc_k_headers_b.ID%TYPE
44 ) IS
45
46 l_api_version CONSTANT NUMBER := 1;
47 lx_order_id okx_order_headers_v.id1%TYPE := NULL;
48 lx_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
49 lx_msg_count NUMBER := 0;
50 lx_msg_data VARCHAR2(2000);
51 l_trace_mode VARCHAR2(1) := OKC_API.G_TRUE;
52 l_contract_id number;
53
54
55
56 BEGIN
57 --
58 -- call full version of create_instance_rel
59 --
60
61 --errorout('1');
62 OKS_KTO_INSREL_PUB.create_instance_rel(p_api_version => l_api_version
63 ,p_init_msg_list => OKC_API.G_TRUE
64 ,p_commit => OKC_API.G_TRUE
65 ,x_return_status => lx_return_status
66 ,x_msg_count => lx_msg_count
67 ,x_msg_data => lx_msg_data
68 ,p_contract_id => p_contract_id);
69 --errorout('10');
70
71 -- no need to check for errors, message stack should be set,
72 -- nothing to return to caller
73 IF lx_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
74 IF lx_order_id IS NULL THEN
75 RETCODE := 2;
76 ELSE
77 RETCODE := 1;
78 END IF;
79 ELSE
80 RETCODE:=0;
81 END IF;
82 ERRBUF:=lx_msg_data;
83 END create_instance_rel;
84
85 --
86 -- full version of the procedure to create an order from a contract
87 --
88
89 PROCEDURE create_instance_rel(p_api_version IN NUMBER DEFAULT NULL
90 ,p_init_msg_list IN VARCHAR2 DEFAULT NULL
91 ,p_commit IN VARCHAR2 DEFAULT NULL
92 ,x_return_status OUT NOCOPY VARCHAR2
93 ,x_msg_count OUT NOCOPY NUMBER
94 ,x_msg_data OUT NOCOPY VARCHAR2
95 ,p_contract_id IN okc_k_headers_b.ID%TYPE
96 ) IS
97
98 l_api_name CONSTANT VARCHAR2(30) := 'OKS_KTO_INSREL_PUB_';
99 l_api_version CONSTANT NUMBER := 1;
100 l_commit VARCHAR2(1) := 'F';
101 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
102
103
104 cursor SUBSCR_INS(p_contract_id IN NUMBER) is
105 select ose.order_header_id
106 ,ose.order_line_id
107 ,osh.instance_id
108 ,osh.dnz_chr_id
109 from oks_subscr_elements ose,
110 oks_subscr_header_b osh,
111 okc_k_headers_b hdr
112 where ose.osh_id=osh.id
113 and osh.dnz_chr_id=hdr.id
114 and hdr.id= p_contract_id
115 and ose.order_header_id is not null;
119 select oh.header_id
116
117
118 CURSOR OM_INS(p_header_id IN NUMBER) is
120 ,ol.line_id
121 ,cii.instance_id
122 ,cii.inventory_item_id
123 ,cii.last_oe_order_line_id
124 from
125 csi_item_instances cii,
126 oe_order_lines_all ol,
127 oe_order_headers_all oh
128 where oh.header_id=ol.header_id
129 and oh.header_id=p_header_id
130 and cii.last_oe_order_line_id = ol.line_id
131 and ol.flow_status_code='CLOSED'
132 and ol.shipped_quantity=ol.fulfilled_quantity
133 order by oh.header_id;
134
135 subscr_ins_rec subscr_ins%rowtype;
136 om_ins_rec om_ins%rowtype;
137 l_relationship_tbl CSI_DATASTRUCTURES_PUB.II_RELATIONSHIP_TBL;
138 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
139 l_return_status VARCHAR2(2000);
140 l_msg_count NUMBER;
141 l_msg_data VARCHAR2(2000);
142 i NUMBER:=1;
143
144
145 BEGIN
146
147 FOR subscr_ins_rec in subscr_ins(p_contract_id) LOOP
148 FOR om_ins_rec in om_ins(subscr_ins_rec.order_header_id) LOOP
149
150 ---l_relationship_tbl(i).relationship_id := NULL;
151 l_relationship_tbl(i).relationship_type_code := 'COMPONENT-OF';
152 l_relationship_tbl(i).object_id := subscr_ins_rec.instance_id;
153 l_relationship_tbl(i).subject_id := om_ins_rec.instance_id;
154 -- l_relationship_tbl(i).object_id := 58988;
155 -- l_relationship_tbl(i).subject_id := 2007;
156 l_relationship_tbl(i).subject_has_child := '';
157 l_relationship_tbl(i).position_reference := '';
158 l_relationship_tbl(i).active_start_date := NULL;
159 l_relationship_tbl(i).active_end_date := NULL;
160 l_relationship_tbl(i).display_order := NULL;
161 l_relationship_tbl(i).mandatory_flag := 'N';
162 l_relationship_tbl(i).object_version_number := NULL;
163
164 --l_txn_rec.transaction_id := NULL;
165 l_txn_rec.transaction_date := sysdate;
166 l_txn_rec.source_transaction_date := sysdate;
167 l_txn_rec.transaction_type_id := 51;
168 l_txn_rec.txn_sub_type_id := NULL;
169 l_txn_rec.source_group_ref_id := NULL;
170 l_txn_rec.source_group_ref := '';
171 l_txn_rec.source_header_ref_id := NULL;
172 l_txn_rec.source_header_ref := '';
173 l_txn_rec.source_line_ref_id := NULL;
174 l_txn_rec.source_line_ref := '';
175 l_txn_rec.source_dist_ref_id1 := NULL;
176 l_txn_rec.source_dist_ref_id2 := NULL;
177 l_txn_rec.inv_material_transaction_id := NULL;
178 l_txn_rec.transaction_quantity := NULL;
179 l_txn_rec.transaction_uom_code := '';
180 l_txn_rec.transacted_by := NULL;
181 l_txn_rec.transaction_status_code := '';
182 l_txn_rec.transaction_action_code := '';
183 l_txn_rec.message_id := NULL;
184 l_txn_rec.object_version_number := 1;
185 l_txn_rec.split_reason_code := '';
186
187 csi_ii_relationships_pub.create_relationship(
188 p_api_version => 1.0,
189 p_init_msg_list => OKC_API.G_FALSE,
190 p_commit => l_commit,
191 p_validation_level=> l_validation_level,
192 x_return_status => l_return_status,
193 x_msg_count => l_msg_count,
194 x_msg_data => l_msg_data,
195 p_relationship_tbl=>l_relationship_tbl,
196 p_txn_rec => l_txn_rec);
197
198 FND_FILE.PUT_LINE(FND_FILE.LOG,'Return_status='||l_return_status);
199 FND_FILE.PUT_LINE(FND_FILE.LOG,'Relation_ship_id='||l_relationship_tbl(i).relationship_id );
200
201 i:=i+1;
202
203 --errorout('return_status='||l_return_status);
204
205 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
206 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
207 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
208 RAISE OKC_API.G_EXCEPTION_ERROR;
209 END IF;
210
211
212 END LOOP;
213
214 END LOOP;
215 -- check return status
216
217
218 EXCEPTION
219 when OKC_API.G_EXCEPTION_ERROR then
220 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
221 p_api_name => l_api_name,
222 p_pkg_name => g_pkg_name,
223 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
224 x_msg_count => x_msg_count,
225 x_msg_data => x_msg_data,
226 p_api_type => g_api_type);
227
228 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
229 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
230 p_api_name => l_api_name,
231 p_pkg_name => g_pkg_name,
232 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
233 x_msg_count => x_msg_count,
234 x_msg_data => x_msg_data,
235 p_api_type => g_api_type);
236
237 when OTHERS then
238 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
239 p_api_name => l_api_name,
240 p_pkg_name => g_pkg_name,
241 p_exc_name => 'OTHERS',
242 x_msg_count => x_msg_count,
243 x_msg_data => x_msg_data,
244 p_api_type => g_api_type);
245 END create_instance_rel;
246
247
248
249 END OKS_KTO_INSREL_PUB;
250