1 Package OKS_IBINT_PUB As
2 /* $Header: OKSPIBIS.pls 120.8.12000000.1 2007/01/16 22:06:40 appldev 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(30),
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 FUNCTION CHECK_SUBSCR_INSTANCE( p_instance_id NUMBER)
234 RETURN VARCHAR2 ;
235
236 PROCEDURE POPULATE_CHILD_INSTANCES (p_api_version IN Number,
237 p_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
238 p_instance_id IN NUMBER,
239 p_transaction_type IN VARCHAR2,
240 x_msg_Count OUT NOCOPY Number,
241 x_msg_Data OUT NOCOPY Varchar2,
242 x_return_status OUT NOCOPY Varchar2);
243
244 PROCEDURE CHECK_CONTRACTS_IMPACTED(
245 P_Api_Version IN NUMBER,
246 P_init_msg_list IN VARCHAR2 Default OKC_API.G_FALSE,
247 P_instance_id IN NUMBER,
248 p_parent_instance_yn IN VARCHAR2,
249 p_transaction_date IN DATE,
250 p_new_install_date IN DATE,
251 P_txn_tbl IN txn_tbl_type,
252 x_contract_exists_yn OUT NOCOPY VARCHAR2,
253 X_msg_Count OUT NOCOPY Number,
254 X_msg_Data OUT NOCOPY Varchar2,
255 x_return_status OUT NOCOPY Varchar2);
256
257 PROCEDURE GET_CONTRACTS(p_api_version IN Number,
258 p_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
259 p_instance_id IN NUMBER,
260 p_validate_yn IN VARCHAR2,
261 x_msg_Count OUT NOCOPY Number,
262 x_msg_Data OUT NOCOPY Varchar2,
263 x_return_status OUT NOCOPY Varchar2);
264
265 PROCEDURE CREATE_ITEM_INSTANCE
266 (
267 p_api_version IN NUMBER,
268 p_commit IN VARCHAR2,
269 p_init_msg_list IN VARCHAR2,
270 p_validation_level IN NUMBER,
271 p_instance_rec IN OUT NOCOPY instance_rec,
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_msg_count OUT NOCOPY NUMBER,
274 x_msg_data OUT NOCOPY VARCHAR2
275 );
276
277 -- Procedure to validate batch rules before submitting
278 -- the batch for processing.
279 -- In the case where the new owner account is changed
280 -- either in the Forms Mass Edit batch or OA mass edit
281 -- batch without visiting contracts page, the api throws
282 -- an error forcing the user to visit the contracts page
283 -- and change the options.
284
285 procedure Validate_new_owner
286 (
287 P_Api_Version IN NUMBER,
288 P_init_msg_list IN VARCHAR2,
289 P_Batch_ID IN NUMBER,
290 P_new_owner_id IN NUMBER,
291 x_return_status OUT NOCOPY VARCHAR2,
292 x_msg_count OUT NOCOPY NUMBER,
293 x_msg_data OUT NOCOPY VARCHAR2);
294
295
296 End OKS_IBINT_PUB;
[Home] [Help]