1 Package OKS_IBINT_PUB AUTHID CURRENT_USER As
2 /* $Header: OKSPIBIS.pls 120.11 2010/12/22 10:05:42 skuchima ship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 ---------------------------------------------------------------------------
7 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
8 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
9 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
10 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
11 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
12 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
13 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
14 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
15 G_UPPERCASE_REQUIRED CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UPPERCASE_REQUIRED';
16
17 ------------------------------------------------------------------------------------
18 -- GLOBAL EXCEPTION
19 ---------------------------------------------------------------------------
20 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
21
22 -- GLOBAL VARIABLES
23 ---------------------------------------------------------------------------
24 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKSOMINT';
25 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKS';
26 G_FND_LOG_OPTION CONSTANT VARCHAR2(30) := NVL(Fnd_Profile.Value('OKS_DEBUG'),'N');
27 ---------------------------------------------------------------------------
28
29 Type Instance_rec_type Is Record
30 ( old_customer_product_id Number
31 ,old_quantity Number
32 ,Bom_explosion_flag Varchar2(1)
33 ,Old_Unit_of_measure Varchar2(3)
34 ,Old_Inventory_item_id Number
35 ,Old_Customer_acct_id Number
36 ,Organization_id Number
37 ,Bill_to_site_use_id Number
38 ,Ship_to_site_use_id Number
39 ,Org_id Number
40 ,Order_line_id Number
41 ,Shipped_date Date
42 ,Installation_date Date
43 ,transaction_date Date
44
45 );
46
47 Type l_instance_tbl is table of instance_Rec_type index by binary_integer;
48 instance_tbl l_instance_tbl;
49 Type txn_instance_rec is record
50 (
51
52 Old_Customer_product_id NUMBER,
53 Old_Quantity NUMBER,
54 Old_Unit_of_measure VARCHAR2 (3),
55 Old_Inventory_item_id NUMBER,
56 Old_Customer_acct_id NUMBER,
57 New_Customer_product_id NUMBER,
58 New_Quantity NUMBER,
59 New_Customer_acct_id NUMBER,
60 New_inventory_item_id NUMBER,
61 New_Unit_of_measure VARCHAR2(3),
62 Org_id NUMBER,
63 Order_line_id NUMBER,
64 Shipped_date DATE,
65 Installation_date DATE,
66 Bill_to_site_use_id NUMBER,
67 Ship_to_site_use_id NUMBER,
68 Organization_id NUMBER,
69 System_id NUMBER,
70 Bom_explosion_flag VARCHAR2 (1),
71 Return_reason_code VARCHAR2 (240),
72 Raise_credit VARCHAR2 (50),
73 Transaction_date Date,
74 Transfer_date Date,
75 Termination_date Date,
76 TRM VARCHAR2(1),
77 TRF VARCHAR2(1),
78 RET VARCHAR2(1),
79 RPL VARCHAR2(1),
80 IDC VARCHAR2(1),
81 UPD VARCHAR2(1),
82 SPL VARCHAR2(1),
83 NEW VARCHAR2(1),
84 RIN VARCHAR2(1)
85 );
86 Type txn_instance_tbl is table of txn_instance_rec index by binary_integer;
87 /*
88 This api is an interface api, called by IB when any instance operation is performed.
89 For Mass Instance Updates, batch_id and batch_type along with the list of instances
90 is passed to identify the Mass update operation.
91 For Instance transfers, instance terminations and instance installation date change, parent instance as well as the components affected by the transaction are passed
92 */
93 Procedure IB_interface(
94 P_Api_Version NUMBER,
95 P_init_msg_list VARCHAR2,
96 P_single_txn_date_flag VARCHAR2,
97 P_Batch_type VARCHAR2,
98 P_Batch_ID NUMBER,
99 P_OKS_Txn_Inst_tbl TXN_INSTANCE_tbl,
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER,
102 x_msg_data OUT NOCOPY VARCHAR2
103 );
104
105
106 l_Header_rec OKS_EXTWAR_UTIL_PVT.Header_Rec_Type;
107 l_line_rec OKS_EXTWAR_UTIL_PVT.Line_Rec_Type;
108 War_tbl OKS_EXTWAR_UTIL_PVT.War_tbl;
109 l_Service_tbl OKS_EXTWAR_UTIL_PVT.Service_tbl;
110 l_extwar_rec OKS_EXTWARPRGM_PVT .extwar_rec_Type;
111
112 p_contact_tbl OKS_EXTWARPRGM_PVT .contact_tbl;
113 l_codv_tbl_in OKS_COD_PVT.codv_tbl_type;
114 l_codv_tbl_out OKS_COD_PVT.codv_tbl_type;
115 l_SalesCredit_tbl OKS_EXTWARPRGM_PVT.SalesCredit_tbl;
116 l_SalesCredit_tbl_hdr OKS_EXTWARPRGM_PVT.SalesCredit_tbl; --mmadhavi bug 4174921
117 l_pricing_attributes_in OKS_EXTWARPRGM_PVT.Pricing_attributes_Type;
118
119 TYPE CP_REC_TYPE Is RECORD
120 (
121 org_id Number
122 ,customer_product_id Number
123 ,order_line_id Number
124 ,SHipped_date Date
125 ,Installation_date Date
126 ,Bill_to_site_use_id Number
127 ,Ship_to_site_use_id Number
128 ,Quantity Number
129 ,Unit_of_measure Varchar2(3)
130 ,Inventory_item_id Number
131 ,Customer_acct_id Number
132 ,organization_id Number
133 ,System_id Number
134 ,bom_explosion_flag Varchar2(1)
135 ,return_reason_code Varchar2(240)
136 ,raise_credit Varchar2(50)
137 );
138
139
140 Type Cp_tbl_type is TABLE of cp_rec_type INDEX BY BINARY_INTEGER;
141
142 Type Renewal_rec_type Is Record
143 (
144 Chr_id Number
145 ,Renewal_type Varchar2(10)
146 ,po_required_yn Varchar2(1)
147 ,Renewal_pricing_type Varchar2(3)
148 ,Markup_percent Number
149 ,Price_list_id1 Varchar2(40)
150 ,link_chr_id Number
151 ,contact_id Number
152 ,email_id Number
153 ,phone_id Number
154 ,fax_id Number
155 ,site_id Number
156 ,cod_type Varchar2(3)
157 ,billing_profile_id Number -- new parameter added -vigandhi
158 ,line_renewal_type Varchar2(3)
159
160 );
161 l_renewal_rec renewal_rec_type;
162 -- Procedure to delete a batch
163 -- This procedure is called by IB when aInstance Mass update batch is deleted.
164 -- This api deletes the batch rules stored in OKS_BATCH_RULES table for the batch_id.
165 procedure delete_batch
166 (
167 P_Api_Version IN NUMBER,
168 P_init_msg_list IN VARCHAR2,
169 P_Batch_ID IN NUMBER,
170 x_return_status OUT NOCOPY VARCHAR2,
171 x_msg_count OUT NOCOPY NUMBER,
172 x_msg_data OUT NOCOPY VARCHAR2);
173
174 -- Procedure to create batch rules. This api is invoked from Instance Mass Update(Form based UI)
175 -- The transfer options as defined in the profile options,
176 -- are stored in OKS_BATCH_RULES_TABLE and are applied to contracts when the batch is submitted for processing.
177 procedure create_batch_rules
178 (
179 P_Api_Version IN NUMBER,
180 P_init_msg_list IN VARCHAR2,
181 P_Batch_ID IN NUMBER,
182 p_batch_type IN VARCHAR2,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2);
186
187 TYPE txn_tbl_type is TABLE of VARCHAR2(3) INDEX BY BINARY_INTEGER;
188 TYPE NUM_TBL_TYPE is TABLE of NUMBER INDEX BY BINARY_INTEGER;
189 TYPE VAR120_TBL_TYPE is TABLE of VARCHAR2(120) INDEX BY BINARY_INTEGER;
190 TYPE VAR150_TBL_TYPE is TABLE of VARCHAR2(150) INDEX BY BINARY_INTEGER;
191 TYPE VAR300_TBL_TYPE is TABLE of VARCHAR2(300) INDEX BY BINARY_INTEGER;
192 TYPE VAR90_TBL_TYPE is TABLE of VARCHAR2(90) INDEX BY BINARY_INTEGER;
193 TYPE DATE_TBL_TYPE is TABLE of DATE INDEX BY BINARY_INTEGER;
194
195 TYPE instance_rec is RECORD
196 ( INSTANCE_ID NUMBER,
197 INSTANCE_NUMBER VARCHAR2(30),
198 EXTERNAL_REFERENCE VARCHAR2(30),
199 INVENTORY_ITEM_ID NUMBER,
200 VLD_ORGANIZATION_ID NUMBER,
201 INVENTORY_REVISION VARCHAR2(3),
202 SERIAL_NUMBER VARCHAR2(30),
203 LOT_NUMBER VARCHAR2(80), --skuchima bug #10318534
204 QUANTITY NUMBER,
205 UNIT_OF_MEASURE VARCHAR2(3),
206 ACTIVE_START_DATE DATE,
207 INSTALL_DATE DATE,
208 LOCATION_TYPE_CODE VARCHAR2(30),
209 LOCATION_ID NUMBER,
210 CONTEXT VARCHAR2(30),
211 ATTRIBUTE1 VARCHAR2(240),
212 ATTRIBUTE2 VARCHAR2(240),
213 ATTRIBUTE3 VARCHAR2(240),
214 ATTRIBUTE4 VARCHAR2(240),
215 ATTRIBUTE5 VARCHAR2(240),
216 ATTRIBUTE6 VARCHAR2(240),
217 ATTRIBUTE7 VARCHAR2(240),
218 ATTRIBUTE8 VARCHAR2(240),
219 ATTRIBUTE9 VARCHAR2(240),
220 ATTRIBUTE10 VARCHAR2(240),
221 ATTRIBUTE11 VARCHAR2(240),
222 ATTRIBUTE12 VARCHAR2(240),
223 ATTRIBUTE13 VARCHAR2(240),
224 ATTRIBUTE14 VARCHAR2(240),
225 ATTRIBUTE15 VARCHAR2(240),
226 INSTALL_LOCATION_TYPE_CODE VARCHAR2(30),
227 INSTALL_LOCATION_ID NUMBER,
228 CALL_CONTRACTS VARCHAR2(1),
229 Party_Id NUMBER,
230 ACCOUNT_ID NUMBER
231 );
232
233 /*cgopinee bug9786579 introduced record type and API for csi mass upload feature*/
234 Type k_cov_line_rec_type Is Record
235 ( customer_product_id Number
236 ,quantity Number
237 ,Unit_of_measure Varchar2(3)
238 ,Inventory_item_id Number
239 ,Customer_acct_id Number
240 ,Organization_id Number
241 ,Org_id Number
242 ,parent_tbl_idx NUMBER
243 );
244 TYPE k_cov_line_tbl IS TABLE OF k_cov_line_rec_type INDEX BY BINARY_INTEGER;
245
246 PROCEDURE create_k_covered_levels_webadi
247 (
248 p_chr_id IN NUMBER,
249 p_attach2_line_id IN NUMBER,
250 p_k_covd_rec IN k_cov_line_rec_type,
251 x_covlvl_id OUT NOCOPY NUMBER,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_count OUT NOCOPY NUMBER,
254 x_msg_data OUT NOCOPY VARCHAR2
255 );
256
257 FUNCTION CHECK_SUBSCR_INSTANCE( p_instance_id NUMBER)
258 RETURN VARCHAR2 ;
259
260 PROCEDURE POPULATE_CHILD_INSTANCES (p_api_version IN Number,
261 p_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
262 p_instance_id IN NUMBER,
263 p_transaction_type IN VARCHAR2,
264 x_msg_Count OUT NOCOPY Number,
265 x_msg_Data OUT NOCOPY Varchar2,
266 x_return_status OUT NOCOPY Varchar2);
267
268 PROCEDURE CHECK_CONTRACTS_IMPACTED(
269 P_Api_Version IN NUMBER,
270 P_init_msg_list IN VARCHAR2 Default OKC_API.G_FALSE,
271 P_instance_id IN NUMBER,
272 p_parent_instance_yn IN VARCHAR2,
273 p_transaction_date IN DATE,
274 p_new_install_date IN DATE,
275 P_txn_tbl IN txn_tbl_type,
276 x_contract_exists_yn OUT NOCOPY VARCHAR2,
277 X_msg_Count OUT NOCOPY Number,
278 X_msg_Data OUT NOCOPY Varchar2,
279 x_return_status OUT NOCOPY Varchar2);
280
281 PROCEDURE GET_CONTRACTS(p_api_version IN Number,
282 p_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
283 p_instance_id IN NUMBER,
284 p_validate_yn IN VARCHAR2,
285 x_msg_Count OUT NOCOPY Number,
286 x_msg_Data OUT NOCOPY Varchar2,
287 x_return_status OUT NOCOPY Varchar2);
288
289 PROCEDURE CREATE_ITEM_INSTANCE
290 (
291 p_api_version IN NUMBER,
292 p_commit IN VARCHAR2,
293 p_init_msg_list IN VARCHAR2,
294 p_validation_level IN NUMBER,
295 p_instance_rec IN OUT NOCOPY instance_rec,
296 x_return_status OUT NOCOPY VARCHAR2,
297 x_msg_count OUT NOCOPY NUMBER,
298 x_msg_data OUT NOCOPY VARCHAR2
299 );
300
301 -- Procedure to validate batch rules before submitting
302 -- the batch for processing.
303 -- In the case where the new owner account is changed
304 -- either in the Forms Mass Edit batch or OA mass edit
305 -- batch without visiting contracts page, the api throws
306 -- an error forcing the user to visit the contracts page
307 -- and change the options.
308
309 procedure Validate_new_owner
310 (
311 P_Api_Version IN NUMBER,
312 P_init_msg_list IN VARCHAR2,
313 P_Batch_ID IN NUMBER,
314 P_new_owner_id IN NUMBER,
315 x_return_status OUT NOCOPY VARCHAR2,
316 x_msg_count OUT NOCOPY NUMBER,
317 x_msg_data OUT NOCOPY VARCHAR2);
318
319
320 End OKS_IBINT_PUB;
[Home] [Help]