[Home] [Help]
PACKAGE BODY: APPS.OKC_XPRT_OM_INT_PVT
Source
1 PACKAGE BODY OKC_XPRT_OM_INT_PVT AS
2 /* $Header: OKCVXOMINTB.pls 120.8.12010000.3 2009/08/10 10:51:06 nvvaidya ship $ */
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ------------------------------------------------------------------------------
7
8 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
9 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
10
11 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
12 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
13 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
14
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKC_XPRT_OM_INT_PVT';
16 G_MODULE_NAME CONSTANT VARCHAR2(250) := 'OKC.PLSQL.'||G_PKG_NAME||'.';
17 G_STMT_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
18 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
19
20 G_ITEM_CODE CONSTANT VARCHAR2(30) := 'OKC$S_ITEM';
21 G_ITEM_CATEGORY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_ITEM_CATEGORY';
22 G_PA_NAME_CODE CONSTANT VARCHAR2(30) := 'OKC$S_PA_NAME';
23 G_PA_NAME_EXIST_CODE CONSTANT VARCHAR2(30) := 'OKC$S_PRC_AGR_EXIST';
24 G_CUSTOMER_NAME_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CUSTOMER_NAME';
25 G_CURRENCY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CURRENCY_CODE';
26 G_FREIGHT_TERMS_CODE CONSTANT VARCHAR2(30) := 'OKC$S_FREIGHT_TERMS';
27 G_SHIPPING_METHOD_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIPPING_METHOD';
28 G_PAYMENT_TERM_CODE CONSTANT VARCHAR2(30) := 'OKC$S_PAYMENT_TERM';
29 G_BILLTO_COUNTRY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILLTO_COUNTRY';
30 G_SHIPTO_COUNTRY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIPTO_COUNTRY';
31 G_SOLDTO_COUNTRY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SOLDTO_COUNTRY';
32 G_BLKT_AGR_TYPE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BLANKET_AGREEMENT_TYPE';
33 G_CUST_PO_EXIST_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CUST_PO_EXIST';
34 G_INVOICING_RULE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_INVOICING_RULE';
35 G_PRICE_LIST_CODE CONSTANT VARCHAR2(30) := 'OKC$S_PRICE_LIST';
36 G_MIN_AMT_AGREED_CODE CONSTANT VARCHAR2(30) := 'OKC$S_MIN_AMOUNT_AGREED';
37 G_MAX_AMT_AGREED_CODE CONSTANT VARCHAR2(30) := 'OKC$S_MAX_AMOUNT_AGREED';
38 G_TAX_HANDL_CODE CONSTANT VARCHAR2(30) := 'OKC$S_TAX_HANDL';
39 G_ORDER_TYPE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_ORDER_TYPE';
40 G_FOB_CODE CONSTANT VARCHAR2(30) := 'OKC$S_FOB';
41 G_PAYMENT_TYPE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_PAYMENT_TYPE';
42 G_SALES_CHANNEL_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SALES_CHANNEL';
43 G_END_CUST_CODE CONSTANT VARCHAR2(30) := 'OKC$S_END_CUST';
44 G_END_CUST_EXIST_CODE CONSTANT VARCHAR2(30) := 'OKC$S_END_CUST_EXIST';
45 G_TOTAL_CODE CONSTANT VARCHAR2(30) := 'OKC$S_TOTAL';
46 G_TOTAL_ADJUST_AMT_CODE CONSTANT VARCHAR2(30) := 'OKC$S_ADJUST_AMOUNT';
47 G_TOTAL_ADJUST_PCT_CODE CONSTANT VARCHAR2(30) := 'OKC$S_ADJUST_PERCENT';
48 G_BLKT_NUM_EXIST_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BLKT_NUM_EXIST';
49 G_BILLTO_CUST_NAME_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILLTO_CUST_NAME';
50 G_SHIPTO_CUST_NAME_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIPTO_CUST_NAME';
51 G_SALES_DOC_TYPE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SALES_DOC_TYPE';
52 G_CUST_CATEGORY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CUST_CAT';
53 G_CUST_CLASS_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CUST_CLASS';
54 G_CUST_PROF_CLASS_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CUST_PROF_CLASS';
55 G_CUST_CRDT_RATE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CUST_CRDT_RATE';
56 G_CUST_CRDT_CLASS_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CUST_CRDT_CLASS';
57 G_CUST_RISK_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CUST_RISK_CODE';
58
59 ---6899074: New bill to ship to and deliver to system variables
60 G_BILL_TO_CITY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILL_TO_CITY';
61 G_DELIVER_TO_CITY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_DELIVER_TO_CITY';
62 G_SHIP_TO_LOCATION_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIP_TO_LOCATION';
63 G_SHIP_TO_ADDRESS1_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIP_TO_ADDRESS1';
64 G_SHIP_TO_ADDRESS2_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIP_TO_ADDRESS2';
65 G_SHIP_TO_ADDRESS3_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIP_TO_ADDRESS3';
66 G_SHIP_TO_ADDRESS4_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIP_TO_ADDRESS4';
67 G_SHIP_TO_CITY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIP_TO_CITY';
68 G_SHIP_TO_COUNTY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIP_TO_COUNTY';
69 G_SHIP_TO_STATE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIP_TO_STATE';
70 G_SHIP_TO_PROVINCE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIP_TO_PROVINCE';
71 G_SHIP_TO_POSTAL_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIP_TO_POSTAL_CODE';
72 G_BILL_TO_LOCATION_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILL_TO_LOCATION';
73 G_BILL_TO_ADDRESS1_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILL_TO_ADDRESS1';
74 G_BILL_TO_ADDRESS2_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILL_TO_ADDRESS2';
75 G_BILL_TO_ADDRESS3_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILL_TO_ADDRESS3';
76 G_BILL_TO_ADDRESS4_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILL_TO_ADDRESS4';
77 G_BILL_TO_COUNTY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILL_TO_COUNTY';
78 G_BILL_TO_STATE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILL_TO_STATE';
79 G_BILL_TO_PROVINCE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILL_TO_PROVINCE';
80 G_BILL_TO_POSTAL_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BILL_TO_POSTAL_CODE';
81 G_DELIVER_TO_LOCATION_CODE CONSTANT VARCHAR2(30) := 'OKC$S_DELIVER_TO_LOCATION';
82 G_DELIVER_TO_ADDRESS1_CODE CONSTANT VARCHAR2(30) := 'OKC$S_DELIVER_TO_ADDRESS1';
83 G_DELIVER_TO_ADDRESS2_CODE CONSTANT VARCHAR2(30) := 'OKC$S_DELIVER_TO_ADDRESS2';
84 G_DELIVER_TO_ADDRESS3_CODE CONSTANT VARCHAR2(30) := 'OKC$S_DELIVER_TO_ADDRESS3';
85 G_DELIVER_TO_ADDRESS4_CODE CONSTANT VARCHAR2(30) := 'OKC$S_DELIVER_TO_ADDRESS4';
86 G_DELIVER_TO_COUNTY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_DELIVER_TO_COUNTY';
87 G_DELIVER_TO_STATE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_DELIVER_TO_STATE';
88 G_DELIVER_TO_PROVINCE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_DELIVER_TO_PROVINCE';
89 G_DELIVER_TO_POSTAL_CODE CONSTANT VARCHAR2(30) := 'OKC$S_DELIVER_TO_POSTAL_CODE';
90 G_DELIVER_TO_COUNTRY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_DELIVER_TO_COUNTRY';
91 G_PRICE_LIST_NAME_CODE CONSTANT VARCHAR2(30) := 'OKC$S_PRICE_LIST_NAME';
92
93 ---end- 6899074: New bill to ship to and deliver to system variables
94
95
96
97 -- XY
98 G_ORDER_NUMBER_CODE CONSTANT VARCHAR2(30) := 'OKC$S_ORDER_NUMBER';
99 G_BLANKET_NUMBER_CODE CONSTANT VARCHAR2(30) := 'OKC$S_BLANKET_NUMBER';
100 G_PA_NUMBER_CODE CONSTANT VARCHAR2(30) := 'OKC$S_PA_NUMBER';
101 G_QUOTE_NUMBER_CODE CONSTANT VARCHAR2(30) := 'OKC$S_QUOTE_NUMBER';
102 G_CUSTOMER_NUMBER_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CUSTOMER_NUMBER';
103 G_CUST_PO_NUMBER_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CUST_PO_NUMBER';
104 G_VERSION_NUMBER_CODE CONSTANT VARCHAR2(30) := 'OKC$S_VERSION_NUMBER';
105 G_CUST_CONTACT_NAME_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CUST_CONTACT_NAME';
106 G_SALESREP_NAME_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SALESREP_NAME';
107 G_CURRENCY_NAME_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CURRENCY_NAME';
108 G_CURRENCY_SYMBOL_CODE CONSTANT VARCHAR2(30) := 'OKC$S_CURRENCY_SYMBOL';
109 G_SUPPLIER_NAME_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SUPPLIER_NAME';
110 G_ACTIVATION_DATE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_ACTIVATION_DATE';
111 G_EXPIRATION_DATE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_EXPIRATION_DATE';
112 -- XY
113 G_PA_TYPE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_PA_TYPE';
114
115 -- Added for line level variables
116 G_LINE_PAYMENT_TERM_CODE CONSTANT VARCHAR2(30) := 'OKC$S_LINE_PAYMENT_TERM';
117 G_LINE_PA_NAME_CODE CONSTANT VARCHAR2(30) := 'OKC$S_LINE_PA_NAME';
118 G_LINE_INVOICING_RULE_CODE CONSTANT VARCHAR2(30) := 'OKC$S_LINE_INVOICING_RULE';
119
120 -- Added ro resolve bugs 5300044 and 5299978
121 G_SHIPMENT_PRIORITY_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SHIPMENT_PRIORITY';
122 G_SITE_ID_CODE CONSTANT VARCHAR2(30) := 'OKC$S_SITE_ID';
123
124 PROCEDURE get_clause_variable_values
125 (
126 p_api_version IN NUMBER,
127 p_init_msg_list IN VARCHAR2,
128
129 p_doc_type IN VARCHAR2,
130 p_doc_id IN NUMBER,
131 p_sys_var_value_tbl IN OUT NOCOPY OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type,
132
133 x_return_status OUT NOCOPY VARCHAR2,
134 x_msg_count OUT NOCOPY NUMBER,
135 x_msg_data OUT NOCOPY VARCHAR2
136 )
137
138 IS
139
140 l_api_name VARCHAR2(30) := 'get_clause_variable_values';
141 l_package_procedure VARCHAR2(60);
142 l_api_version CONSTANT NUMBER := 1;
143 l_debug Boolean;
144 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
145
146 l_sold_to_site_use_id NUMBER := NULL;
147 l_sold_to_org_id NUMBER := NULL;
148 l_invoice_to_site_use_id NUMBER := NULL;
149 l_ship_to_site_use_id NUMBER := NULL;
150 l_deliver_to_site_use_id NUMBER := NULL; --- bug 6899074
151 l_expert_yes FND_FLEX_VALUES.FLEX_VALUE_ID%type;
152 l_expert_no FND_FLEX_VALUES.FLEX_VALUE_ID%type;
153
154
155 --
156 -- Cursor to fetch the flex value id for Yes and No from the OKC_XPRT_YES_NO value set
157 --
158 CURSOR c_get_expert_yes_no(p_yes_no VARCHAR2) IS
159 select a.FLEX_VALUE_ID
160 from fnd_flex_values a, fnd_flex_value_sets b
161 where b.flex_value_set_name = 'OKC_XPRT_YES_NO'
162 and a.FLEX_VALUE_SET_ID = b.FLEX_VALUE_SET_ID
163 and a.FLEX_VALUE = p_yes_no;
164
165
166 --
167 --cursor to fetch value of header level variables such as OKC$S_BLANKET_NUMBER etc. for blankets
168 --
169 CURSOR c_get_bsa_header_variables IS
170 SELECT
171 bh.agreement_id,
172 -- decode(nvl(to_char(bh.agreement_id),'X'),'X','N','Y') price_agr_exist,
173 decode(nvl(to_char(bh.agreement_id),'X'),'X',l_expert_no,l_expert_yes) price_agr_exist,
174 bh.sold_to_org_id,
175 bh.invoice_to_org_id,
176 bh.ship_to_org_id,
177 bh.deliver_to_org_id,
178 bh.sold_to_site_use_id,
179 bh.order_type_id,
180 -- decode(nvl(bh.cust_po_number,'X'),'X','N','Y') cust_po_num_exist,
181 decode(nvl(bh.cust_po_number,'X'),'X',l_expert_no,l_expert_yes) cust_po_num_exist,
182 bh.transactional_curr_code,
183 bh.freight_terms_code,
184 bh.shipping_method_code,
185 bh.payment_term_id,
186 bh.invoicing_rule_id,
187 bh.tax_exempt_flag,
188 bh.price_list_id,
189 bh.org_id,
190 bhe.blanket_min_amount,
191 bhe.blanket_max_amount,
192 -- XY
193 bh.order_number,
194 bh.cust_po_number,
195 bh.version_number,
196 bh.sold_to_contact_id,
197 bh.salesrep_id,
198 bhe.start_date_active,
199 bhe.end_date_active
200 -- XY
201 FROM
202 oe_blanket_headers_ext bhe,
203 oe_blanket_headers_all bh
204 WHERE
205 bh.order_number = bhe.order_number
206 AND bh.header_id = p_doc_id;
207
208 --
209 --cursor to fetch value of header level variables such as OKC$S_ORDER_NUMBER etc. for sales orders
210 --
211 CURSOR c_get_so_header_variables IS
212 SELECT
213 oh.blanket_number,
214 -- decode(nvl(to_char(oh.blanket_number),'X'),'X','N','Y') blanket_number_exist,
215 decode(nvl(to_char(oh.blanket_number),'X'),'X',l_expert_no,l_expert_yes) blanket_number_exist,
216 oh.agreement_id,
217 -- decode(nvl(to_char(oh.agreement_id),'X'),'X','N','Y') price_agr_exist,
218 decode(nvl(to_char(oh.agreement_id),'X'),'X',l_expert_no,l_expert_yes) price_agr_exist,
219 oh.sold_to_org_id,
220 oh.invoice_to_org_id,
221 oh.ship_to_org_id,
222 oh.deliver_to_org_id,
223 oh.sold_to_site_use_id,
224 -- decode(nvl(oh.cust_po_number,'X'),'X','N','Y') cust_po_num_exist,
225 decode(nvl(oh.cust_po_number,'X'),'X',l_expert_no,l_expert_yes) cust_po_num_exist,
226 oh.transactional_curr_code,
227 oh.freight_terms_code,
228 oh.shipping_method_code,
229 oh.payment_term_id,
230 oh.invoicing_rule_id,
231 oh.org_id,
232 oh.order_type_id,
233 oh.fob_point_code,
234 oh.payment_type_code,
235 oh.end_customer_id,
236 -- decode(nvl(to_char(oh.end_customer_id),'X'),'X','N','Y') end_cust_exist,
237 decode(nvl(to_char(oh.end_customer_id),'X'),'X',l_expert_no,l_expert_yes) end_cust_exist,
238 oh.price_list_id,
239 oh.tax_exempt_flag,
240 oh.sales_channel_code,
241 oe_oe_totals_summary.prt_order_total(oh.header_id) total,
242 oe_oe_totals_summary.price_adjustments(oh.header_id) total_adjusted_amount,
243 oe_oe_totals_summary.get_order_amount(oh.header_id) total_list_price,
244 bh.header_id blanket_header_id,
245 -- XY
246 oh.order_number,
247 oh.quote_number,
248 oh.cust_po_number,
249 oh.version_number,
250 oh.sold_to_contact_id,
251 oh.salesrep_id,
252 -- XY
253
254 -- Begin: Added for resolving bug 5300044 and 5299978
255 oh.shipment_priority_code,
256 oh.minisite_id ,
257 -- End: Added for resolving bug 5300044 and 5299978
258 pl.name price_list_name --6899074
259 FROM
260 oe_blanket_headers_all bh,
261 oe_order_headers_all oh,
262 qp_list_headers_tl pl
263 WHERE
264 oh.blanket_number = bh.order_number(+)
265 AND bh.sales_document_type_code(+) = 'B'
266 AND oh.header_id = p_doc_id
267 AND oh.price_list_id = pl.list_header_id(+)
268 AND pl.language(+) = USERENV('LANG');
269
270 --
271 -- Cursor to fetch the customer information
272 --
273
274 CURSOR c_get_cust_info_var(p_sold_to_org_id NUMBER) IS
275 SELECT
276 hzp.category_code,
277 hzc.customer_class_code,
278 hzcp.profile_class_id,
279 hzcp.credit_rating,
280 hzcp.credit_classification,
281 hzcp.risk_code
282 FROM
283 hz_customer_profiles hzcp,
284 hz_cust_accounts hzc,
285 hz_parties hzp
286 WHERE
287 hzc.cust_account_id = hzcp.cust_account_id
288 AND hzcp.site_use_id is null
289 AND hzc.party_id = hzcp.party_id
290 AND hzc.party_id = hzp.party_id
291 AND hzc.cust_account_id = p_sold_to_org_id;
292
293
294 --
295 -- Cursor to get the bill to, ship to and sold to countries
296 -- Blanket sales agreement, Quoted Order and Sales Order
297 --
298 CURSOR c_get_country(p_site_use_id NUMBER) IS
299 SELECT
300 loc.country
301 FROM
302 hz_locations loc,
303 hz_party_sites ps,
304 hz_cust_acct_sites cas,
305 hz_cust_site_uses su
306 WHERE
307 ps.location_id = loc.location_id
308 and cas.party_site_id = ps.party_site_id
309 and su.cust_acct_site_id = cas.cust_acct_site_id
310 and su.site_use_id = p_site_use_id;
311
312 --
313 --bug 6899074
314 -- Cursor to get the bill to, ship to and sold to address
315 -- Blanket sales agreement and Sales Order. Not modifed the existing usage for country
316
317 CURSOR c_get_address_info(p_site_use_id NUMBER) IS
318
319 SELECT
320 su.location,
321 loc.province,
322 loc.address1,
323 loc.address2,
324 loc.address3,
325 loc.address4,
326 loc.city,
327 loc.postal_code,
328 loc.state,
329 loc.county
330 FROM
331 hz_locations loc,
332 hz_party_sites ps,
333 hz_cust_acct_sites cas,
334 hz_cust_site_uses su
335 WHERE
336 ps.location_id = loc.location_id
337 and cas.party_site_id = ps.party_site_id
338 and su.cust_acct_site_id = cas.cust_acct_site_id
339 and su.site_use_id = p_site_use_id;
340 --
341 -- Cursor to get the bill to and ship to customer account for
342 -- Blanket sales agreement, Quoted Order and Sales Order
343 --
344 CURSOR c_get_cust_account(p_site_use_id NUMBER) IS
345 SELECT
346 cas.cust_account_id
347 FROM
348 hz_cust_acct_sites cas,
349 hz_cust_site_uses su
350 WHERE
351 su.cust_acct_site_id = cas.cust_acct_site_id
352 and su.site_use_id = p_site_use_id;
353
354 --
355 -- AK: Cursor to get Agreement type for
356 -- Quoted Order and Sales Order
357 --
358 CURSOR c_get_pa_type(p_agreement_id NUMBER) IS
359 SELECT
360 pc.agreement_type_code
361 FROM
362 oe_pricing_contracts_v pc
363 WHERE
364 pc.agreement_id = p_agreement_id;
365
366
367 l_bsa_header_variables c_get_bsa_header_variables%ROWTYPE;
368 l_so_header_variables c_get_so_header_variables%ROWTYPE;
369 l_cust_info_variables c_get_cust_info_var%ROWTYPE;
370 -- bug 6899074
371 l_ship_to_address_var c_get_address_info%ROWTYPE;
372 l_deliver_to_address_var c_get_address_info%ROWTYPE;
373 l_bill_to_address_var c_get_address_info%ROWTYPE;
374
375 BEGIN
376
377 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
378 l_debug := true;
379 END IF;
380
381 x_return_status := FND_API.G_RET_STS_SUCCESS;
382
383 l_package_procedure := G_PKG_NAME || '.' || l_api_name;
384
385 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
386 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'10: Entered ' || l_package_procedure);
387 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'20: p_doc_type: ' || p_doc_type);
388 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'30: p_doc_id: ' || p_doc_id);
389 END IF;
390
391 --
392 -- Standard call to check for call compatibility.
393 --
394 IF NOT FND_API.Compatible_API_Call (l_api_version,
395 p_api_version,
396 l_api_name,
397 G_PKG_NAME)
398 THEN
399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 END IF;
401
402 --
403 -- Initialize message list if p_init_msg_list is set to TRUE.
404 --
405 IF FND_API.to_Boolean( p_init_msg_list ) THEN
406 FND_MSG_PUB.initialize;
407 END IF;
408
409
410 OPEN c_get_expert_yes_no('Yes');
411 FETCH c_get_expert_yes_no INTO l_expert_yes;
412 CLOSE c_get_expert_yes_no;
413
414 OPEN c_get_expert_yes_no('No');
415 FETCH c_get_expert_yes_no INTO l_expert_no;
416 CLOSE c_get_expert_yes_no;
417
418 -- Query OM tables to retrieve values against variable codes sent in by calling contract expert API.
419
420 IF p_sys_var_value_tbl.FIRST IS NOT NULL THEN
421
422 IF p_doc_type = G_BSA_DOC_TYPE THEN
423
424 OPEN c_get_bsa_header_variables;
425 FETCH c_get_bsa_header_variables INTO l_bsa_header_variables;
426 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
427 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'40: c_get_bsa_header_variables%ROWCOUNT: ' || c_get_bsa_header_variables%ROWCOUNT);
428 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'50: Values from l_bsa_header_variables: ');
429 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'55: ************************************');
430 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'60: Agreement_id = '||l_bsa_header_variables.agreement_id);
431 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'70: price_agr_exist = '||l_bsa_header_variables.price_agr_exist );
432 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'80: sold_to_org_id = '||l_bsa_header_variables.sold_to_org_id );
433 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'90: invoice_to_org_id = '||l_bsa_header_variables.invoice_to_org_id );
434 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'100: ship_to_org_id = '||l_bsa_header_variables.ship_to_org_id );
435 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'105: deliver_to_org_id = '||l_bsa_header_variables.deliver_to_org_id );
436 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'110: sold_to_site_use_id = '||l_bsa_header_variables.sold_to_site_use_id );
437 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'120: order_type_id = '||l_bsa_header_variables.order_type_id );
438 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'130: cust_po_num_exist = '||l_bsa_header_variables.cust_po_num_exist );
439 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'140: transactional_curr_code = '||l_bsa_header_variables.transactional_curr_code );
440 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'150: freight_terms_code = '||l_bsa_header_variables.freight_terms_code );
441 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'160: shipping_method_code = '||l_bsa_header_variables.shipping_method_code );
442 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'170: payment_term_id = '||l_bsa_header_variables.payment_term_id );
443 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'180: invoicing_rule_id = '||l_bsa_header_variables.invoicing_rule_id );
444 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'190: tax_exempt_flag = '||l_bsa_header_variables.tax_exempt_flag );
445 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'200: price_list_id = '||l_bsa_header_variables.price_list_id );
446 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'210: org_id = '||l_bsa_header_variables.org_id );
447 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'220: blanket_min_amount = '||l_bsa_header_variables.blanket_min_amount );
448 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'230: blanket_max_amount = '||l_bsa_header_variables.blanket_max_amount );
449 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'240: order_number = '||l_bsa_header_variables.order_number );
450 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'250: cust_po_number = '||l_bsa_header_variables.cust_po_number );
451 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'260: version_number = '||l_bsa_header_variables.version_number );
452 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'270: sold_to_contact_id = '||l_bsa_header_variables.sold_to_contact_id );
453 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'280: salesrep_id = '||l_bsa_header_variables.salesrep_id );
454 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'290: start_date_active = '||l_bsa_header_variables.start_date_active );
455 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'300: end_date_active = '||l_bsa_header_variables.end_date_active );
456 END IF;
457 CLOSE c_get_bsa_header_variables;
458
459 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
460
461 OPEN c_get_so_header_variables;
462 FETCH c_get_so_header_variables INTO l_so_header_variables;
463 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
464 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'40: c_get_so_header_variables%ROWCOUNT: ' || c_get_so_header_variables%ROWCOUNT);
465 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'50: Values from l_so_header_variables: ');
466 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'55: ************************************');
467 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'60: blanket_number = '||l_so_header_variables.blanket_number );
468 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'70: blanket_number_exist = '||l_so_header_variables.blanket_number_exist );
469 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'80: agreement_id = '||l_so_header_variables.agreement_id );
470 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'90: price_agr_exist = '||l_so_header_variables.price_agr_exist );
471 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'100: sold_to_org_id = '||l_so_header_variables.sold_to_org_id );
472 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'110: invoice_to_org_id = '||l_so_header_variables.invoice_to_org_id );
473 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'120: ship_to_org_id = '||l_so_header_variables.ship_to_org_id );
474 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'125: deliver_to_org_id = '||l_so_header_variables.deliver_to_org_id );
475 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'130: sold_to_site_use_id = '||l_so_header_variables.sold_to_site_use_id );
476 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'140: cust_po_num_exist = '||l_so_header_variables.cust_po_num_exist );
477 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'150: transactional_curr_code = '||l_so_header_variables.transactional_curr_code );
478 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'160: freight_terms_code = '||l_so_header_variables.freight_terms_code );
479 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'170: shipping_method_code = '||l_so_header_variables.shipping_method_code );
480 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'180: payment_term_id = '||l_so_header_variables.payment_term_id );
481 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'190: invoicing_rule_id = '||l_so_header_variables.invoicing_rule_id );
482 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'200: org_id = '||l_so_header_variables.org_id );
483 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'210: order_type_id = '||l_so_header_variables.order_type_id );
484 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'220: fob_point_code = '||l_so_header_variables.fob_point_code );
485 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'230: payment_type_code = '||l_so_header_variables.payment_type_code );
486 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'240: end_customer_id = '||l_so_header_variables.end_customer_id );
487 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'250: end_cust_exist = '||l_so_header_variables.end_cust_exist );
488 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'260: price_list_id = '||l_so_header_variables.price_list_id );
489 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'270: tax_exempt_flag = '||l_so_header_variables.tax_exempt_flag );
490 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'280: sales_channel_code = '||l_so_header_variables.sales_channel_code );
491 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'290: total = '||l_so_header_variables.total );
492 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'300: total_adjusted_amount = '||l_so_header_variables.total_adjusted_amount );
493 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'310: total_list_price = '||l_so_header_variables.total_list_price );
494 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'320: blanket_header_id = '||l_so_header_variables.blanket_header_id );
495 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'330: order_number = '||l_so_header_variables.order_number );
496 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'340: quote_number = '||l_so_header_variables.quote_number );
497 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'350: cust_po_number = '||l_so_header_variables.cust_po_number );
498 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'360: version_number = '||l_so_header_variables.version_number );
499 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'370: sold_to_contact_id = '||l_so_header_variables.sold_to_contact_id );
500 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'380: salesrep_id = '||l_so_header_variables.salesrep_id );
501 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'381: shipment_priority_code = '||l_so_header_variables.shipment_priority_code );
502 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'382: minisite_id = '||l_so_header_variables.minisite_id );
503 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'390: Price_list_name = '||l_so_header_variables.price_list_name );
504 END IF;
505 CLOSE c_get_so_header_variables;
506
507 END IF;
508
509
510 -- Get the customer info
511
512 IF G_BSA_DOC_TYPE = p_doc_type THEN
513 l_sold_to_org_id := l_bsa_header_variables.sold_to_org_id;
514 ELSIF G_SO_DOC_TYPE = p_doc_type THEN
515 l_sold_to_org_id := l_so_header_variables.sold_to_org_id;
516 END IF;
517
518 IF l_sold_to_org_id IS NOT NULL THEN
519 OPEN c_get_cust_info_var(l_sold_to_org_id);
520 FETCH c_get_cust_info_var INTO l_cust_info_variables;
521 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
522 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'330: c_get_cust_info_var%ROWCOUNT: ' || c_get_cust_info_var%ROWCOUNT);
523 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'340: Values from l_cust_info_variables: ');
524 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'350: sold_to_org_id used = '||l_sold_to_org_id );
525 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'360: category_code = '||l_cust_info_variables.category_code );
526 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'370: customer_class_code = '||l_cust_info_variables.customer_class_code );
527 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'380: profile_class_id = '||l_cust_info_variables.profile_class_id );
528 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'390: credit_rating = '||l_cust_info_variables.credit_rating );
529 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'400: credit_classification = '||l_cust_info_variables.credit_classification );
530 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'410: risk_code = '||l_cust_info_variables.risk_code );
531 END IF;
532 CLOSE c_get_cust_info_var;
533 END IF;
534
535 -- Depending on the doc type, assign the sold to, bill to and ship to
536 -- to be used for retrieving the sold to, bill to and ship to country for
537 -- Blanket sales agreement, Quoted Order and Sales Order
538
539 IF G_BSA_DOC_TYPE = p_doc_type THEN
540 l_sold_to_site_use_id := l_bsa_header_variables.sold_to_site_use_id;
541 ELSIF G_SO_DOC_TYPE = p_doc_type THEN
542 l_sold_to_site_use_id := l_so_header_variables.sold_to_site_use_id;
543 END IF;
544
545
546 IF G_BSA_DOC_TYPE = p_doc_type THEN
547 l_invoice_to_site_use_id := l_bsa_header_variables.invoice_to_org_id;
548 ELSIF G_SO_DOC_TYPE = p_doc_type THEN
549 l_invoice_to_site_use_id := l_so_header_variables.invoice_to_org_id;
550 END IF;
551
552
553 IF G_BSA_DOC_TYPE = p_doc_type THEN
554 l_ship_to_site_use_id := l_bsa_header_variables.ship_to_org_id;
555 ELSIF G_SO_DOC_TYPE = p_doc_type THEN
556 l_ship_to_site_use_id := l_so_header_variables.ship_to_org_id;
557 END IF;
558 -----bug 6899074: for DELIVER_TO_ORG_ID
559 IF G_BSA_DOC_TYPE = p_doc_type THEN
560 l_deliver_to_site_use_id := l_bsa_header_variables.deliver_to_org_id;
561 ELSIF G_SO_DOC_TYPE = p_doc_type THEN
562 l_deliver_to_site_use_id := l_so_header_variables.deliver_to_org_id;
563 END IF;
564
565 --bug 6899074 get Address Info
566 IF l_invoice_to_site_use_id IS NOT NULL THEN
567 OPEN c_get_address_info(l_invoice_to_site_use_id);
568 FETCH c_get_address_info INTO l_bill_to_address_var;
569 CLOSE c_get_address_info;
570 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
571 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'411: Bill to Location = '|| l_bill_to_address_var.location );
572 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'412: Bill to Address1 = '|| l_bill_to_address_var.address1);
573 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'413: Bill to Address2 = '|| l_bill_to_address_var.address2);
574 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'414: Bill to Address3= '|| l_bill_to_address_var.address3);
575 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'415: Bill to Address4 ='|| l_bill_to_address_var.address4);
576 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'416: Bill to City = '|| l_bill_to_address_var.city );
577 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'417: Bill to = County'|| l_bill_to_address_var.county);
578 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'418: Bill to = State'|| l_bill_to_address_var.state);
579 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'419: Bill to = Province'|| l_bill_to_address_var.province);
580 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'420: Bill to = Postal Code'|| l_bill_to_address_var.postal_code );
581 END IF;
582 END IF;
583
584 IF l_deliver_to_site_use_id IS NOT NULL THEN
585 OPEN c_get_address_info(l_invoice_to_site_use_id);
586 FETCH c_get_address_info INTO l_deliver_to_address_var;
587 CLOSE c_get_address_info;
588 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
589 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'421: Deliver to Location = '|| l_deliver_to_address_var.location );
590 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'422: Deliver to Address1 = '|| l_deliver_to_address_var.address1);
591 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'423: Deliver to Address2 = '|| l_deliver_to_address_var.address2);
592 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'424: Deliver to Address3= '|| l_deliver_to_address_var.address3);
593 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'425: Deliver to Address4 ='|| l_deliver_to_address_var.address4);
594 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'426: Deliver to City = '|| l_deliver_to_address_var.city );
595 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'427: Deliver to = County'|| l_deliver_to_address_var.county);
596 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'428: Deliver to = State'|| l_deliver_to_address_var.state);
597 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'429: Deliver to = Province'|| l_deliver_to_address_var.province);
598 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'431: Deliver to = Postal Code'|| l_deliver_to_address_var.postal_code );
599 END IF;
600 END IF;
601
602 IF l_ship_to_site_use_id IS NOT NULL THEN
603 OPEN c_get_address_info(l_ship_to_site_use_id);
604 FETCH c_get_address_info INTO l_ship_to_address_var;
605 CLOSE c_get_address_info;
606 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
607 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'432: ship to Location = '|| l_ship_to_address_var.location );
608 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'433: ship to Address1 = '|| l_ship_to_address_var.address1);
609 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'434: ship to Address2 = '|| l_ship_to_address_var.address2);
610 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'435: ship to Address3= '|| l_ship_to_address_var.address3);
611 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'436: ship to Address4 ='|| l_ship_to_address_var.address4);
612 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'437: ship to City = '|| l_ship_to_address_var.city );
613 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'438: ship to = County'|| l_ship_to_address_var.county);
614 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'439: ship to = State'|| l_ship_to_address_var.state);
615 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'441: ship to = Province'|| l_ship_to_address_var.province);
616 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'442: ship to = Postal Code'|| l_ship_to_address_var.postal_code );
617 END IF;
618 END IF;
619
620 FOR i IN p_sys_var_value_tbl.FIRST..p_sys_var_value_tbl.LAST LOOP
621
622 IF p_sys_var_value_tbl(i).variable_code = G_CUST_CATEGORY_CODE THEN
623 p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.category_code;
624
625 ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_CLASS_CODE THEN
626 p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.customer_class_code;
627
628 ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_PROF_CLASS_CODE THEN
629 p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.profile_class_id;
630
631 ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_CRDT_RATE_CODE THEN
632 p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.credit_rating;
633
634 ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_CRDT_CLASS_CODE THEN
635 p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.credit_classification;
636
637 ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_RISK_CODE THEN
638 p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.risk_code;
639
640 ELSIF p_sys_var_value_tbl(i).variable_code = G_SOLDTO_COUNTRY_CODE THEN
641 BEGIN
642 IF l_sold_to_site_use_id IS NOT NULL THEN
643 OPEN c_get_country(l_sold_to_site_use_id);
644 FETCH c_get_country INTO p_sys_var_value_tbl(i).variable_value_id;
645 CLOSE c_get_country;
646 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
647 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'430: Sold to country = '||p_sys_var_value_tbl(i).variable_value_id);
648 END IF;
649 END IF;
650 END;
651
652 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIPTO_COUNTRY_CODE THEN
653 BEGIN
654 IF l_ship_to_site_use_id IS NOT NULL THEN
655 OPEN c_get_country(l_ship_to_site_use_id);
656 FETCH c_get_country INTO p_sys_var_value_tbl(i).variable_value_id;
657 CLOSE c_get_country;
658 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
659 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'440: Ship to country = '||p_sys_var_value_tbl(i).variable_value_id);
660 END IF;
661 END IF;
662 END;
663
664 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILLTO_COUNTRY_CODE THEN
665 BEGIN
666 IF l_invoice_to_site_use_id IS NOT NULL THEN
667 OPEN c_get_country(l_invoice_to_site_use_id);
668 FETCH c_get_country INTO p_sys_var_value_tbl(i).variable_value_id;
669 CLOSE c_get_country;
670 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
671 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'450: Invoice to country = '||p_sys_var_value_tbl(i).variable_value_id);
672 END IF;
673 END IF;
674 END;
675
676 ELSIF p_sys_var_value_tbl(i).variable_code = G_CUSTOMER_NAME_CODE THEN
677 BEGIN
678 IF p_doc_type = G_BSA_DOC_TYPE THEN
679 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.sold_to_org_id;
680 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
681 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.sold_to_org_id;
682 END IF;
683 END;
684
685 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILLTO_CUST_NAME_CODE THEN
686 BEGIN
687 IF l_invoice_to_site_use_id IS NOT NULL THEN
688 OPEN c_get_cust_account(l_invoice_to_site_use_id);
689 FETCH c_get_cust_account INTO p_sys_var_value_tbl(i).variable_value_id;
690 CLOSE c_get_cust_account;
691 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
692 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'460: Invoice to Customer = '||p_sys_var_value_tbl(i).variable_value_id);
693 END IF;
694 END IF;
695 END;
696
697 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIPTO_CUST_NAME_CODE THEN
698 BEGIN
699 IF l_ship_to_site_use_id IS NOT NULL THEN
700 OPEN c_get_cust_account(l_ship_to_site_use_id);
701 FETCH c_get_cust_account INTO p_sys_var_value_tbl(i).variable_value_id;
702 CLOSE c_get_cust_account;
703 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
704 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'470: Ship to Customer = '||p_sys_var_value_tbl(i).variable_value_id);
705 END IF;
706 END IF;
707 END;
708
709 ELSIF p_sys_var_value_tbl(i).variable_code = G_SALES_DOC_TYPE_CODE THEN
710 BEGIN
711 IF p_doc_type = G_BSA_DOC_TYPE THEN
712 p_sys_var_value_tbl(i).variable_value_id := p_doc_type;
713 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
714 p_sys_var_value_tbl(i).variable_value_id := p_doc_type;
715 END IF;
716 END;
717
718 ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_PO_EXIST_CODE THEN
719 BEGIN
720 IF p_doc_type = G_BSA_DOC_TYPE THEN
721 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.cust_po_num_exist;
722 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
723 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.cust_po_num_exist;
724 END IF;
725 END;
726
727 ELSIF p_sys_var_value_tbl(i).variable_code = G_PRICE_LIST_CODE THEN
728 BEGIN
729 IF p_doc_type = G_BSA_DOC_TYPE THEN
730 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.price_list_id;
731 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
732 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.price_list_id;
733 END IF;
734 END;
735
736 ELSIF p_sys_var_value_tbl(i).variable_code = G_CURRENCY_CODE THEN
737 BEGIN
738 IF p_doc_type = G_BSA_DOC_TYPE THEN
739 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.transactional_curr_code;
740 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
741 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.transactional_curr_code;
742 END IF;
743 END;
744
745 ELSIF p_sys_var_value_tbl(i).variable_code = G_FREIGHT_TERMS_CODE THEN
746 BEGIN
747 IF p_doc_type = G_BSA_DOC_TYPE THEN
748 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.freight_terms_code;
749 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
750 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.freight_terms_code;
751 END IF;
752 END;
753
754 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIPPING_METHOD_CODE THEN
755 BEGIN
756 IF p_doc_type = G_BSA_DOC_TYPE THEN
757 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.shipping_method_code;
758 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
759 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.shipping_method_code;
760 END IF;
761 END;
762
763 ELSIF p_sys_var_value_tbl(i).variable_code = G_PAYMENT_TERM_CODE THEN
764 BEGIN
765 IF p_doc_type = G_BSA_DOC_TYPE THEN
766 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.payment_term_id;
767 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
768 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.payment_term_id;
769 END IF;
770 END;
771
772 ELSIF p_sys_var_value_tbl(i).variable_code = G_PA_NAME_CODE THEN
773 BEGIN
774 -- XY
775 IF p_doc_type = G_BSA_DOC_TYPE THEN
776 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.agreement_id;
777 -- XY
778 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
779 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.agreement_id;
780 END IF;
781 END;
782
783 ELSIF p_sys_var_value_tbl(i).variable_code = G_PA_NAME_EXIST_CODE THEN
784 BEGIN
785 IF p_doc_type = G_SO_DOC_TYPE THEN
786 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.price_agr_exist;
787 END IF;
788 END;
789
790 ELSIF p_sys_var_value_tbl(i).variable_code = G_PAYMENT_TYPE_CODE THEN
791 BEGIN
792 IF p_doc_type = G_SO_DOC_TYPE THEN
793 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.payment_type_code;
794 END IF;
795 END;
796
797 ELSIF p_sys_var_value_tbl(i).variable_code = G_FOB_CODE THEN
798 BEGIN
799 IF p_doc_type = G_SO_DOC_TYPE THEN
800 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.fob_point_code;
801 END IF;
802 END;
803
804 ELSIF p_sys_var_value_tbl(i).variable_code = G_SALES_CHANNEL_CODE THEN
805 BEGIN
806 IF p_doc_type = G_SO_DOC_TYPE THEN
807 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.sales_channel_code;
808 END IF;
809 END;
810
811 ELSIF p_sys_var_value_tbl(i).variable_code = G_TAX_HANDL_CODE THEN
812 BEGIN
813 IF p_doc_type = G_SO_DOC_TYPE THEN
814 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.tax_exempt_flag;
815 END IF;
816 END;
817
818 ELSIF p_sys_var_value_tbl(i).variable_code = G_ORDER_TYPE_CODE THEN
819 BEGIN
820 IF p_doc_type = G_SO_DOC_TYPE THEN
821 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.order_type_id;
822 END IF;
823 END;
824
825 ELSIF p_sys_var_value_tbl(i).variable_code = G_TOTAL_CODE THEN
826 BEGIN
827 IF p_doc_type = G_SO_DOC_TYPE THEN
828 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.total;
829 END IF;
830 END;
831
832 ELSIF p_sys_var_value_tbl(i).variable_code = G_TOTAL_ADJUST_AMT_CODE THEN
833 BEGIN
834 IF p_doc_type = G_SO_DOC_TYPE THEN
835 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.total_adjusted_amount;
836 END IF;
837 END;
838
839 ELSIF p_sys_var_value_tbl(i).variable_code = G_TOTAL_ADJUST_PCT_CODE THEN
840 BEGIN
841 IF p_doc_type = G_SO_DOC_TYPE AND l_so_header_variables.total_list_price IS NOT NULL
842 AND l_so_header_variables.total_list_price <> 0 THEN
843 p_sys_var_value_tbl(i).variable_value_id :=
844 (l_so_header_variables.total_adjusted_amount / l_so_header_variables.total_list_price) * 100;
845 END IF;
846 END;
847
848 ELSIF p_sys_var_value_tbl(i).variable_code = G_INVOICING_RULE_CODE THEN
849 BEGIN
850 IF p_doc_type = G_SO_DOC_TYPE THEN
851 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.invoicing_rule_id;
852 ELSIF p_doc_type = G_BSA_DOC_TYPE THEN
853 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.invoicing_rule_id;
854 END IF;
855 END;
856
857 ELSIF p_sys_var_value_tbl(i).variable_code = G_BLKT_AGR_TYPE_CODE THEN
858 BEGIN
859 IF p_doc_type = G_BSA_DOC_TYPE THEN
860 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.order_type_id;
861 END IF;
862 END;
863
864 ELSIF p_sys_var_value_tbl(i).variable_code = G_MIN_AMT_AGREED_CODE THEN
865 BEGIN
866 IF p_doc_type = G_BSA_DOC_TYPE THEN
867 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.blanket_min_amount;
868 END IF;
869 END;
870
871 ELSIF p_sys_var_value_tbl(i).variable_code = G_MAX_AMT_AGREED_CODE THEN
872 BEGIN
873 IF p_doc_type = G_BSA_DOC_TYPE THEN
874 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.blanket_max_amount;
875 END IF;
876 END;
877
878 ELSIF p_sys_var_value_tbl(i).variable_code = G_END_CUST_CODE THEN
879 BEGIN
880 IF p_doc_type = G_SO_DOC_TYPE THEN
881 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.end_customer_id;
882 END IF;
883 END;
884
885 ELSIF p_sys_var_value_tbl(i).variable_code = G_END_CUST_EXIST_CODE THEN
886 BEGIN
887 IF p_doc_type = G_SO_DOC_TYPE THEN
888 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.end_cust_exist;
889 END IF;
890 END;
891
892 ELSIF p_sys_var_value_tbl(i).variable_code = G_BLKT_NUM_EXIST_CODE THEN
893 BEGIN
894 IF p_doc_type = G_SO_DOC_TYPE THEN
895 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.blanket_number_exist;
896 END IF;
897 END;
898 -- XY
899 ELSIF p_sys_var_value_tbl(i).variable_code = G_CUSTOMER_NUMBER_CODE THEN
900 BEGIN
901 IF p_doc_type = G_BSA_DOC_TYPE THEN
902 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.sold_to_org_id;
903 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
904 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.sold_to_org_id;
905 END IF;
906 END;
907
908 ELSIF p_sys_var_value_tbl(i).variable_code = G_PA_NUMBER_CODE THEN
909 BEGIN
910 IF p_doc_type = G_BSA_DOC_TYPE THEN
911 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.agreement_id;
912 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
913 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.agreement_id;
914 END IF;
915 END;
916
917 --AK
918 ELSIF p_sys_var_value_tbl(i).variable_code = G_PA_TYPE_CODE THEN
919 BEGIN
920 IF p_doc_type = G_BSA_DOC_TYPE THEN
921 p_sys_var_value_tbl(i).variable_value_id := NULL;
922 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
923 IF l_so_header_variables.agreement_id IS NOT NULL THEN
924 OPEN c_get_pa_type(l_so_header_variables.agreement_id);
925 FETCH c_get_pa_type INTO p_sys_var_value_tbl(i).variable_value_id;
926 CLOSE c_get_pa_type;
927 END IF;
928 END IF;
929 END;
930 --AK
931 ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_PO_NUMBER_CODE THEN
932 BEGIN
933 IF p_doc_type = G_BSA_DOC_TYPE THEN
934 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.cust_po_number;
935 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
936 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.cust_po_number;
937 END IF;
938 END;
939
940 ELSIF p_sys_var_value_tbl(i).variable_code = G_VERSION_NUMBER_CODE THEN
941 BEGIN
942 IF p_doc_type = G_BSA_DOC_TYPE THEN
943 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.version_number;
944 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
945 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.version_number;
946 END IF;
947 END;
948
949 ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_CONTACT_NAME_CODE THEN
950 BEGIN
951 IF p_doc_type = G_BSA_DOC_TYPE THEN
952 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.sold_to_contact_id;
953 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
954 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.sold_to_contact_id;
955 END IF;
956 END;
957
958 ELSIF p_sys_var_value_tbl(i).variable_code = G_SALESREP_NAME_CODE THEN
959 BEGIN
960 IF p_doc_type = G_BSA_DOC_TYPE THEN
961 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.salesrep_id;
962 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
963 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.salesrep_id;
964 END IF;
965 END;
966
967 ELSIF p_sys_var_value_tbl(i).variable_code = G_CURRENCY_NAME_CODE THEN
968 BEGIN
969 IF p_doc_type = G_BSA_DOC_TYPE THEN
970 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.transactional_curr_code;
971 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
972 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.transactional_curr_code;
973 END IF;
974 END;
975
976 ELSIF p_sys_var_value_tbl(i).variable_code = G_CURRENCY_SYMBOL_CODE THEN
977 BEGIN
978 IF p_doc_type = G_BSA_DOC_TYPE THEN
979 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.transactional_curr_code;
980 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
981 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.transactional_curr_code;
982 END IF;
983 END;
984
985 ELSIF p_sys_var_value_tbl(i).variable_code = G_SUPPLIER_NAME_CODE THEN
986 BEGIN
987 IF p_doc_type = G_BSA_DOC_TYPE THEN
988 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.org_id;
989 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
990 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.org_id;
991 END IF;
992 END;
993
994 ELSIF p_sys_var_value_tbl(i).variable_code = G_BLANKET_NUMBER_CODE THEN
995 BEGIN
996 IF p_doc_type = G_BSA_DOC_TYPE THEN
997 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.order_number;
998 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
999 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.blanket_number;
1000 END IF;
1001 END;
1002
1003 ELSIF p_sys_var_value_tbl(i).variable_code = G_ORDER_NUMBER_CODE THEN
1004 BEGIN
1005 IF p_doc_type = G_SO_DOC_TYPE THEN
1006 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.order_number;
1007 END IF;
1008 END;
1009
1010 ELSIF p_sys_var_value_tbl(i).variable_code = G_QUOTE_NUMBER_CODE THEN
1011 BEGIN
1012 IF p_doc_type = G_SO_DOC_TYPE THEN
1013 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.quote_number;
1014 END IF;
1015 END;
1016
1017 ELSIF p_sys_var_value_tbl(i).variable_code = G_ACTIVATION_DATE_CODE THEN
1018 BEGIN
1019 IF p_doc_type = G_BSA_DOC_TYPE THEN
1020 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.start_date_active;
1021 END IF;
1022 END;
1023
1024 ELSIF p_sys_var_value_tbl(i).variable_code = G_EXPIRATION_DATE_CODE THEN
1025 BEGIN
1026 IF p_doc_type = G_BSA_DOC_TYPE THEN
1027 p_sys_var_value_tbl(i).variable_value_id := l_bsa_header_variables.end_date_active;
1028 END IF;
1029 END;
1030
1031 -- XY
1032
1033 -- Begin: Added for resolving bug 5300044 and 5299978
1034
1035 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIPMENT_PRIORITY_CODE THEN
1036 BEGIN
1037 IF p_doc_type = G_SO_DOC_TYPE THEN
1038 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.shipment_priority_code;
1039 END IF;
1040 END;
1041
1042 ELSIF p_sys_var_value_tbl(i).variable_code = G_SITE_ID_CODE THEN
1043 BEGIN
1044 IF p_doc_type = G_SO_DOC_TYPE THEN
1045 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.minisite_id;
1046 END IF;
1047 END;
1048
1049 -- End: Added for resolving bug 5300044 and 5299978
1050 -- XY
1051 --Begin: Bug 66899074: New Variables
1052
1053 ELSIF p_sys_var_value_tbl(i).variable_code = G_DELIVER_TO_CITY_CODE THEN
1054 p_sys_var_value_tbl(i).variable_value_id := l_deliver_to_address_var.city;
1055
1056 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIP_TO_LOCATION_CODE THEN
1057 p_sys_var_value_tbl(i).variable_value_id := l_ship_to_address_var.location;
1058
1059 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIP_TO_ADDRESS1_CODE THEN
1060 p_sys_var_value_tbl(i).variable_value_id := l_ship_to_address_var.address1;
1061
1062 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIP_TO_ADDRESS2_CODE THEN
1063 p_sys_var_value_tbl(i).variable_value_id := l_ship_to_address_var.address2;
1064
1065 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIP_TO_ADDRESS3_CODE THEN
1066 p_sys_var_value_tbl(i).variable_value_id := l_ship_to_address_var.address3;
1067
1068 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIP_TO_ADDRESS4_CODE THEN
1069 p_sys_var_value_tbl(i).variable_value_id := l_ship_to_address_var.address4;
1070
1071 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIP_TO_CITY_CODE THEN
1072 p_sys_var_value_tbl(i).variable_value_id := l_ship_to_address_var.city;
1073
1074 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIP_TO_COUNTY_CODE THEN
1075 p_sys_var_value_tbl(i).variable_value_id := l_ship_to_address_var.county;
1076
1077 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIP_TO_STATE_CODE THEN
1078 p_sys_var_value_tbl(i).variable_value_id := l_ship_to_address_var.state;
1079
1080 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIP_TO_PROVINCE_CODE THEN
1081 p_sys_var_value_tbl(i).variable_value_id := l_ship_to_address_var.province;
1082
1083 ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIP_TO_POSTAL_CODE THEN
1084 p_sys_var_value_tbl(i).variable_value_id := l_ship_to_address_var.postal_code;
1085
1086 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILL_TO_CITY_CODE THEN
1087 p_sys_var_value_tbl(i).variable_value_id := l_bill_to_address_var.city;
1088
1089 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILL_TO_LOCATION_CODE THEN
1090 p_sys_var_value_tbl(i).variable_value_id := l_bill_to_address_var.location;
1091
1092 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILL_TO_ADDRESS1_CODE THEN
1093 p_sys_var_value_tbl(i).variable_value_id := l_bill_to_address_var.address1;
1094
1095 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILL_TO_ADDRESS2_CODE THEN
1096 p_sys_var_value_tbl(i).variable_value_id := l_bill_to_address_var.address2;
1097
1098 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILL_TO_ADDRESS3_CODE THEN
1099 p_sys_var_value_tbl(i).variable_value_id := l_bill_to_address_var.address3;
1100
1101 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILL_TO_ADDRESS4_CODE THEN
1102 p_sys_var_value_tbl(i).variable_value_id := l_bill_to_address_var.address4;
1103
1104 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILL_TO_COUNTY_CODE THEN
1105 p_sys_var_value_tbl(i).variable_value_id := l_bill_to_address_var.county;
1106
1107 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILL_TO_STATE_CODE THEN
1108 p_sys_var_value_tbl(i).variable_value_id := l_bill_to_address_var.state;
1109
1110 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILL_TO_PROVINCE_CODE THEN
1111 p_sys_var_value_tbl(i).variable_value_id := l_bill_to_address_var.province;
1112
1113 ELSIF p_sys_var_value_tbl(i).variable_code = G_BILL_TO_POSTAL_CODE THEN
1114 p_sys_var_value_tbl(i).variable_value_id := l_bill_to_address_var.postal_code;
1115
1116 ELSIF p_sys_var_value_tbl(i).variable_code = G_DELIVER_TO_LOCATION_CODE THEN
1117 p_sys_var_value_tbl(i).variable_value_id := l_deliver_to_address_var.location;
1118
1119 ELSIF p_sys_var_value_tbl(i).variable_code = G_DELIVER_TO_ADDRESS1_CODE THEN
1120 p_sys_var_value_tbl(i).variable_value_id := l_deliver_to_address_var.address1;
1121
1122 ELSIF p_sys_var_value_tbl(i).variable_code = G_DELIVER_TO_ADDRESS2_CODE THEN
1123 p_sys_var_value_tbl(i).variable_value_id := l_deliver_to_address_var.address2;
1124
1125 ELSIF p_sys_var_value_tbl(i).variable_code = G_DELIVER_TO_ADDRESS3_CODE THEN
1126 p_sys_var_value_tbl(i).variable_value_id := l_deliver_to_address_var.address3;
1127
1128 ELSIF p_sys_var_value_tbl(i).variable_code = G_DELIVER_TO_ADDRESS4_CODE THEN
1129 p_sys_var_value_tbl(i).variable_value_id := l_deliver_to_address_var.address4;
1130
1131 ELSIF p_sys_var_value_tbl(i).variable_code = G_DELIVER_TO_COUNTY_CODE THEN
1132 p_sys_var_value_tbl(i).variable_value_id := l_deliver_to_address_var.county;
1133
1134 ELSIF p_sys_var_value_tbl(i).variable_code = G_DELIVER_TO_STATE_CODE THEN
1135 p_sys_var_value_tbl(i).variable_value_id := l_deliver_to_address_var.state;
1136
1137 ELSIF p_sys_var_value_tbl(i).variable_code = G_DELIVER_TO_PROVINCE_CODE THEN
1138 p_sys_var_value_tbl(i).variable_value_id := l_deliver_to_address_var.province;
1139
1140 ELSIF p_sys_var_value_tbl(i).variable_code = G_DELIVER_TO_POSTAL_CODE THEN
1141 p_sys_var_value_tbl(i).variable_value_id := l_deliver_to_address_var.postal_code;
1142
1143 ELSIF p_sys_var_value_tbl(i).variable_code = G_DELIVER_TO_COUNTRY_CODE THEN
1144 BEGIN
1145 IF l_ship_to_site_use_id IS NOT NULL THEN
1146 OPEN c_get_country(l_deliver_to_site_use_id);
1147 FETCH c_get_country INTO p_sys_var_value_tbl(i).variable_value_id;
1148 CLOSE c_get_country;
1149 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1150 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'440: deliver to country = '||p_sys_var_value_tbl(i).variable_value_id);
1151 END IF;
1152 END IF;
1153 END;
1154
1155 ELSIF p_sys_var_value_tbl(i).variable_code = G_PRICE_LIST_NAME_CODE THEN
1156 BEGIN
1157 IF p_doc_type = G_SO_DOC_TYPE THEN
1158 p_sys_var_value_tbl(i).variable_value_id := l_so_header_variables.price_list_name;
1159 END IF;
1160 END;
1161 --END- Bug 6899074
1162
1163 END IF;
1164
1165 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1166 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'480: p_sys_var_value_tbl('||i||').variable_code : '||p_sys_var_value_tbl(i).variable_code);
1167 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'485: p_sys_var_value_tbl('||i||').variable_value_id : '||p_sys_var_value_tbl(i).variable_value_id);
1168 END IF;
1169
1170
1171 END LOOP;
1172
1173 END IF;
1174
1175 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1176 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'490: End of '||l_package_procedure||' for header level variables, x_return_status ' || x_return_status);
1177 END IF;
1178
1179
1180 EXCEPTION
1181
1182 WHEN FND_API.G_EXC_ERROR THEN
1183
1184 IF c_get_bsa_header_variables%ISOPEN THEN
1185 CLOSE c_get_bsa_header_variables;
1186 END IF;
1187
1188 IF c_get_so_header_variables%ISOPEN THEN
1189 CLOSE c_get_so_header_variables;
1190 END IF;
1191
1192 IF c_get_cust_info_var%ISOPEN THEN
1193 CLOSE c_get_cust_info_var;
1194 END IF;
1195
1196 IF c_get_country%ISOPEN THEN
1197 CLOSE c_get_country;
1198 END IF;
1199
1200 IF c_get_cust_account%ISOPEN THEN
1201 CLOSE c_get_cust_account;
1202 END IF;
1203
1204 IF c_get_expert_yes_no%ISOPEN THEN
1205 CLOSE c_get_expert_yes_no;
1206 END IF;
1207 --Bug 6899074
1208 IF c_get_address_info%ISOPEN THEN
1209 CLOSE c_get_address_info;
1210 END IF;
1211
1212 x_return_status := FND_API.G_RET_STS_ERROR ;
1213 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1214 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'500: '||l_package_procedure||' In the FND_API.G_EXC_ERROR section');
1215 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'510: x_return_status = '||x_return_status);
1216 END IF;
1217
1218 FND_MSG_PUB.Count_And_Get(
1219 p_count => x_msg_count,
1220 p_data => x_msg_data );
1221
1222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1223
1224 IF c_get_bsa_header_variables%ISOPEN THEN
1225 CLOSE c_get_bsa_header_variables;
1226 END IF;
1227
1228 IF c_get_so_header_variables%ISOPEN THEN
1229 CLOSE c_get_so_header_variables;
1230 END IF;
1231
1232 IF c_get_cust_info_var%ISOPEN THEN
1233 CLOSE c_get_cust_info_var;
1234 END IF;
1235
1236 IF c_get_country%ISOPEN THEN
1237 CLOSE c_get_country;
1238 END IF;
1239
1240 IF c_get_cust_account%ISOPEN THEN
1241 CLOSE c_get_cust_account;
1242 END IF;
1243
1244 IF c_get_expert_yes_no%ISOPEN THEN
1245 CLOSE c_get_expert_yes_no;
1246 END IF;
1247 --Bug 6899074
1248 IF c_get_address_info%ISOPEN THEN
1249 CLOSE c_get_address_info;
1250 END IF;
1251
1252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1253
1254 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1255 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'520: '||l_package_procedure||' In the FND_API.G_RET_STS_UNEXP_ERROR section');
1256 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'530: x_return_status = '||x_return_status);
1257 END IF;
1258
1259 FND_MSG_PUB.Count_And_Get(
1260 p_count => x_msg_count,
1261 p_data => x_msg_data );
1262
1263 WHEN OTHERS THEN
1264
1265 IF c_get_bsa_header_variables%ISOPEN THEN
1266 CLOSE c_get_bsa_header_variables;
1267 END IF;
1268
1269 IF c_get_so_header_variables%ISOPEN THEN
1270 CLOSE c_get_so_header_variables;
1271 END IF;
1272
1273 IF c_get_cust_info_var%ISOPEN THEN
1274 CLOSE c_get_cust_info_var;
1275 END IF;
1276
1277 IF c_get_country%ISOPEN THEN
1278 CLOSE c_get_country;
1279 END IF;
1280
1281 IF c_get_cust_account%ISOPEN THEN
1282 CLOSE c_get_cust_account;
1283 END IF;
1284
1285 IF c_get_expert_yes_no%ISOPEN THEN
1286 CLOSE c_get_expert_yes_no;
1287 END IF;
1288 --Bug 6711319
1289 IF c_get_address_info%ISOPEN THEN
1290 CLOSE c_get_address_info;
1291 END IF;
1292
1293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1294 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1295 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'540: '||l_package_procedure||' In the OTHERS section');
1296 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'550: x_return_status = '||x_return_status);
1297 END IF;
1298
1299 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1300 FND_MSG_PUB.Add_Exc_Msg(
1301 G_PKG_NAME ,
1302 l_api_name );
1303 END IF;
1304
1305 FND_MSG_PUB.Count_And_Get(
1306 p_count => x_msg_count,
1307 p_data => x_msg_data );
1308
1309 END get_clause_variable_values;
1310
1311
1312
1313
1314 --this overloaded signature is called from the contract expert
1315 PROCEDURE get_clause_variable_values
1316 (
1317 p_api_version IN NUMBER,
1318 p_init_msg_list IN VARCHAR2,
1319
1320 p_doc_type IN VARCHAR2,
1321 p_doc_id IN NUMBER,
1322 p_line_var_tbl IN line_var_tbl_type,
1323
1324 x_line_var_value_tbl OUT NOCOPY OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type,
1325 x_return_status OUT NOCOPY VARCHAR2,
1326 x_msg_count OUT NOCOPY NUMBER,
1327 x_msg_data OUT NOCOPY VARCHAR2
1328 ) IS
1329
1330 l_api_name VARCHAR2(30) := 'get_clause_variable_values';
1331 l_package_procedure VARCHAR2(60);
1332 l_api_version CONSTANT NUMBER := 1;
1333 l_debug Boolean;
1334 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
1335
1336
1337
1338 -- Cursor to get all the items of the BSA i.e. internal (INT) customer (CUST) etc.
1339 -- Returns non-translatable code eg. AS54888
1340 --
1341 CURSOR c_get_items IS
1342 SELECT item_identifier_type, --eg. INT
1343 ordered_item, --eg. AS54888
1344 ordered_item_id,
1345 org_id,
1346 inventory_item_id,
1347 sold_to_org_id
1348 FROM oe_blanket_lines_all
1349 WHERE header_id = p_doc_id
1350 AND p_doc_type = G_BSA_DOC_TYPE
1351 AND item_identifier_type <> 'CAT'
1352 AND line_category_code = 'ORDER'
1353
1354 UNION ALL
1355
1356 -- Get all the items of the Sales Order i.e. internal (INT) customer (CUST) etc.
1357 -- Returns non-translatable code eg. AS54888
1358 --
1359 SELECT item_identifier_type, --eg. INT
1360 ordered_item, --eg. AS54888
1361 ordered_item_id,
1362 org_id,
1363 inventory_item_id,
1364 sold_to_org_id
1365 FROM oe_order_lines_all
1366 WHERE header_id = p_doc_id
1367 AND p_doc_type = G_SO_DOC_TYPE
1368 AND item_identifier_type <> 'CAT'
1369 AND line_category_code = 'ORDER'
1370 ORDER BY ordered_item;
1371
1372
1373 -- Cursor to retrieve the item categories (CATs) in the BSA
1374 -- Returns non-translatable code eg. 208.05
1375 --
1376 CURSOR c_get_item_categories IS
1377 SELECT ordered_item
1378 FROM oe_blanket_lines_all
1379 WHERE header_id = p_doc_id
1380 AND p_doc_type = G_BSA_DOC_TYPE
1381 AND item_identifier_type = 'CAT'
1382 AND line_category_code = 'ORDER'
1383 ORDER BY ordered_item;
1384
1385
1386 -- Cursor to retrieve categories to which the INT (internal) and non-INT
1387 -- items in the BSA or Sales Order belong
1388 -- Note: the inventory_item_id stored in oe_blanket_lines_all and oe_order_lines_all
1389 -- against the non-INT item is that of the mapped INT item so we can use it
1390 -- directly to get the item category
1391 --
1392 -- Returns non-translatable code eg. HOSPITAL.MISC
1393 --
1394 CURSOR c_get_derived_item_category (cp_org_id NUMBER,
1395 cp_inventory_item_id NUMBER) IS
1396 SELECT category_concat_segs
1397 FROM mtl_item_categories_v
1398 WHERE inventory_item_id = cp_inventory_item_id
1399 AND organization_id = cp_org_id -- should be inventory master org
1400 AND structure_id = 101; -- hardcoded to 101 i.e. Item Categories(Inv.Items) for OM
1401
1402
1403 l_bsa_derived_item_category c_get_derived_item_category%ROWTYPE;
1404
1405 j BINARY_INTEGER := 1;
1406 l_master_org_id NUMBER;
1407 lx_ordered_item VARCHAR2(2000);
1408 lx_inventory_item VARCHAR2(2000);
1409
1410 l_current_org_id NUMBER;
1411
1412 CURSOR c_get_doc_org_id IS
1413 SELECT org_id
1414 FROM oe_blanket_headers_all
1415 WHERE header_id = p_doc_id
1416 AND p_doc_type = G_BSA_DOC_TYPE
1417
1418 UNION ALL
1419
1420 SELECT org_id
1421 FROM oe_order_headers_all
1422 WHERE header_id = p_doc_id
1423 AND p_doc_type = G_SO_DOC_TYPE;
1424
1425 BEGIN
1426
1427 l_package_procedure := G_PKG_NAME || '.' || l_api_name || ' - 2';
1428
1429 x_return_status := FND_API.G_RET_STS_SUCCESS;
1430
1431 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1432 l_debug := true;
1433 END IF;
1434
1435 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1436 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'10: Entered ' || l_package_procedure);
1437 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'20: p_doc_type: ' || p_doc_type);
1438 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'30: p_doc_id: ' || p_doc_id);
1439 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'40: p_line_var_tbl.COUNT: ' || p_line_var_tbl.COUNT);
1440 END IF;
1441
1442 --
1443 -- Standard call to check for call compatibility.
1444 --
1445 IF NOT FND_API.Compatible_API_Call (l_api_version,
1446 p_api_version,
1447 l_api_name,
1448 G_PKG_NAME)
1449 THEN
1450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1451 END IF;
1452
1453 --
1454 -- Initialize message list if p_init_msg_list is set to TRUE.
1455 --
1456 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1457 FND_MSG_PUB.initialize;
1458 END IF;
1459
1460
1461 --Get Current Org Id from context
1462 OPEN c_get_doc_org_id;
1463 FETCH c_get_doc_org_id INTO l_current_org_id;
1464 CLOSE c_get_doc_org_id;
1465
1466 --Get inventory master org
1467
1468 l_master_org_id := TO_NUMBER(oe_sys_parameters.value (
1469 param_name => 'MASTER_ORGANIZATION_ID',p_org_id => l_current_org_id ));
1470
1471 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1472 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'45: l_master_org_id: ' || l_master_org_id);
1473 END IF;
1474
1475 -- Query OM tables oe_blanket_headers_all and oe_blanket_lines_all to retrieve values
1476 -- against variable codes sent in by calling contrtact expert API
1477
1478 IF p_line_var_tbl.FIRST IS NOT NULL THEN
1479 FOR i IN p_line_var_tbl.FIRST..p_line_var_tbl.LAST LOOP
1480
1481 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1482 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'50: Processing for ' || p_line_var_tbl(i));
1483 END IF;
1484
1485 IF p_line_var_tbl(i) = G_ITEM_CODE THEN
1486
1487 FOR c_get_items_rec IN c_get_items LOOP
1488 --loop thru all the items for internal INT items
1489 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1490 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'60: c_get_items_rec.item_identifier_type: '||c_get_items_rec.item_identifier_type);
1491 END IF;
1492
1493 IF c_get_items_rec.item_identifier_type = 'INT' THEN
1494 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_ITEM
1495 x_line_var_value_tbl(j).variable_value_id := c_get_items_rec.ordered_item; --eg. AS54888
1496
1497 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1498 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'70: x_line_var_value_tbl('||j||').variable_code: ' || p_line_var_tbl(i));
1499 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'80: x_line_var_value_tbl('||j||').variable_value_id: ' || c_get_items_rec.ordered_item);
1500 END IF;
1501
1502 ELSIF c_get_items_rec.item_identifier_type <> 'INT' THEN
1503 --map the non-INT items to INT items
1504
1505 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1506 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'90: Mapping non-INT item to INT item, Calling OE_Id_To_Value.Ordered_Item ');
1507 END IF;
1508
1509 -- Map non-INT item to INT item
1510 OE_Id_To_Value.Ordered_Item (
1511 p_item_identifier_type => c_get_items_rec.item_identifier_type,
1512 p_inventory_item_id => c_get_items_rec.inventory_item_id,
1513 p_organization_id => l_master_org_id,
1514 p_ordered_item_id => c_get_items_rec.ordered_item_id,
1515 p_sold_to_org_id => c_get_items_rec.sold_to_org_id,
1516 p_ordered_item => c_get_items_rec.ordered_item,
1517 x_ordered_item => lx_ordered_item,
1518 x_inventory_item => lx_inventory_item
1519 );
1520
1521 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_ITEM
1522 x_line_var_value_tbl(j).variable_value_id := lx_inventory_item;
1523
1524 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1525 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'100: x_line_var_value_tbl('||j||').variable_code: ' || p_line_var_tbl(i));
1526 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'110: x_line_var_value_tbl('||j||').variable_value_id: ' || lx_inventory_item);
1527 END IF;
1528 END IF;
1529
1530 j := j + 1;
1531
1532 END LOOP;
1533
1534
1535 ELSIF p_line_var_tbl(i) = G_ITEM_CATEGORY_CODE THEN
1536
1537
1538 --get all the item categories in the BSA
1539 FOR c_get_item_categories_rec IN c_get_item_categories LOOP
1540
1541 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_ITEM_CATEGORY
1542 x_line_var_value_tbl(j).variable_value_id := c_get_item_categories_rec.ordered_item;
1543
1544 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1545 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'120: x_line_var_value_tbl('||j||').variable_code: '||p_line_var_tbl(i));
1546 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'130: x_line_var_value_tbl('||j||').variable_value_id: '||c_get_item_categories_rec.ordered_item);
1547 END IF;
1548
1549 j := j + 1;
1550
1551 END LOOP;
1552
1553 -- Get the item categories to which the INT and non-INT items in the BSA belong to
1554 -- NOTE: the inventory_item_id stored in oe_blanket_lines_all against the non-INT
1555 -- items is actually that of the mapped INT item so we can use it directly to get the
1556 -- item category
1557
1558 FOR c_get_items_rec IN c_get_items LOOP
1559
1560 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1561 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'140: get the item categories to which the INT and non-INT items in the BSA belong to');
1562 END IF;
1563
1564 l_bsa_derived_item_category := null; --initialize
1565
1566 OPEN c_get_derived_item_category(l_master_org_id, c_get_items_rec.inventory_item_id);
1567 FETCH c_get_derived_item_category INTO l_bsa_derived_item_category;
1568 CLOSE c_get_derived_item_category;
1569
1570 x_line_var_value_tbl(j).variable_code := p_line_var_tbl(i); --i.e. OKC$S_ITEM_CATEGORY
1571 x_line_var_value_tbl(j).variable_value_id := l_bsa_derived_item_category.category_concat_segs;
1572
1573 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1574 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'150: x_line_var_value_tbl('||j||').variable_code: '||p_line_var_tbl(i));
1575 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'160: x_line_var_value_tbl('||j||').variable_value_id: '||l_bsa_derived_item_category.category_concat_segs);
1576 END IF;
1577
1578 j := j + 1;
1579
1580 END LOOP;
1581
1582 END IF;
1583
1584 END LOOP;
1585 END IF; ----IF p_line_var_tbl.FIRST IS NOT NULL THEN
1586
1587 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1588 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'170: End of '||l_package_procedure||'for line level variables, x_return_status: '|| x_return_status);
1589 END IF;
1590
1591 EXCEPTION
1592
1593 WHEN FND_API.G_EXC_ERROR THEN
1594
1595 IF c_get_items%ISOPEN THEN
1596 CLOSE c_get_items;
1597 END IF;
1598
1599 IF c_get_item_categories%ISOPEN THEN
1600 CLOSE c_get_item_categories;
1601 END IF;
1602
1603 IF c_get_derived_item_category%ISOPEN THEN
1604 CLOSE c_get_derived_item_category;
1605 END IF;
1606
1607 x_return_status := FND_API.G_RET_STS_ERROR ;
1608
1609 FND_MSG_PUB.Count_And_Get(
1610 p_count => x_msg_count,
1611 p_data => x_msg_data );
1612
1613 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1614 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'180: '||l_package_procedure||' In the FND_API.G_EXC_ERROR section');
1615 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'190: x_return_status = '||x_return_status);
1616 END IF;
1617
1618 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1619
1620 IF c_get_items%ISOPEN THEN
1621 CLOSE c_get_items;
1622 END IF;
1623
1624 IF c_get_item_categories%ISOPEN THEN
1625 CLOSE c_get_item_categories;
1626 END IF;
1627
1628 IF c_get_derived_item_category%ISOPEN THEN
1629 CLOSE c_get_derived_item_category;
1630 END IF;
1631
1632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1633
1634 FND_MSG_PUB.Count_And_Get(
1635 p_count => x_msg_count,
1636 p_data => x_msg_data );
1637
1638 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1639 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'200: '||l_package_procedure||' In the FND_API.G_EXC_UNEXPECTED_ERROR section');
1640 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'210: x_return_status = '||x_return_status);
1641 END IF;
1642
1643 WHEN OTHERS THEN
1644
1645 IF c_get_items%ISOPEN THEN
1646 CLOSE c_get_items;
1647 END IF;
1648
1649 IF c_get_item_categories%ISOPEN THEN
1650 CLOSE c_get_item_categories;
1651 END IF;
1652
1653 IF c_get_derived_item_category%ISOPEN THEN
1654 CLOSE c_get_derived_item_category;
1655 END IF;
1656
1657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1658
1659 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1660 FND_MSG_PUB.Add_Exc_Msg(
1661 G_PKG_NAME ,
1662 l_api_name );
1663 END IF;
1664
1665 FND_MSG_PUB.Count_And_Get(
1666 p_count => x_msg_count,
1667 p_data => x_msg_data );
1668
1669 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1670 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'220: '||l_package_procedure||' In the OTHERS section');
1671 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'230: x_return_status = '||x_return_status);
1672 END IF;
1673
1674 END get_clause_variable_values;
1675
1676 --
1677 -- This procedure will be called from contract expert to get
1678 -- line level system variables
1679 --
1680 PROCEDURE Get_Line_Variable_Values (
1681 p_api_version IN NUMBER,
1682 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1683 p_doc_type IN VARCHAR2,
1684 p_doc_id IN NUMBER,
1685 x_return_status OUT NOCOPY VARCHAR2,
1686 x_msg_count OUT NOCOPY NUMBER,
1687 x_msg_data OUT NOCOPY VARCHAR2,
1688 x_line_sys_var_value_tbl OUT NOCOPY OKC_XPRT_XRULE_VALUES_PVT.line_sys_var_value_tbl_type,
1689 x_line_count OUT NOCOPY NUMBER,
1690 x_line_variables_count OUT NOCOPY NUMBER
1691 ) IS
1692
1693 l_api_name VARCHAR2(30) := 'get_line_variable_values';
1694 l_package_procedure VARCHAR2(60);
1695 l_api_version CONSTANT NUMBER := 1;
1696 l_debug Boolean;
1697 l_module VARCHAR2(250) := G_MODULE_NAME||l_api_name;
1698
1699 --
1700 --cursor to fetch value of line level variables for blankets
1701 --
1702 CURSOR c_get_bsa_line_variables IS
1703 SELECT
1704 bl.line_number,
1705 NVL(bl.payment_term_id,'-99999') payment_term_id,
1706 NVL(bl.invoicing_rule_id,'-99999') invoicing_rule_id,
1707 bl.inventory_item_id,
1708 bl.org_id,
1709 --AK
1710 bl.item_identifier_type, --eg. INT
1711 bl.ordered_item, --eg. AS54888
1712 bl.ordered_item_id,
1713 bl.sold_to_org_id
1714 --AK
1715 FROM
1716 oe_blanket_lines_all bl
1717 WHERE
1718 bl.header_id = p_doc_id;
1719
1720 --
1721 --cursor to fetch value of line level variables for sales orders
1722 --
1723 CURSOR c_get_so_line_variables IS
1724 SELECT
1725 ol.line_number,
1726 NVL(ol.payment_term_id,'-99999') payment_term_id,
1727 NVL(ol.invoicing_rule_id,'-99999') invoicing_rule_id,
1728 NVL(ol.agreement_id,'-99999') agreement_id,
1729 ol.inventory_item_id,
1730 ol.org_id,
1731 --AK
1732 ol.item_identifier_type, --eg. INT
1733 ol.ordered_item, --eg. AS54888
1734 ol.ordered_item_id,
1735 ol.sold_to_org_id,
1736 --AK
1737 --Bug 4768964
1738 ol.service_number,
1739 ol.option_number,
1740 ol.component_number,
1741 ol.shipment_number
1742 --Bug 4768964
1743 FROM
1744 oe_order_lines_all ol
1745 WHERE ol.header_id = p_doc_id;
1746
1747 l_bsa_line_variables c_get_bsa_line_variables%ROWTYPE;
1748 l_so_line_variables c_get_so_line_variables%ROWTYPE;
1749 l_line_count NUMBER := 0;
1750 l_line_number VARCHAR2(250); --NUMBER; Changed for bug 4768964
1751 l_index NUMBER := 0;
1752
1753
1754 --AK
1755 -- Cursor to retrieve the item categories (CATs) in the BSA
1756 -- Returns non-translatable code eg. 208.05
1757 --
1758 CURSOR c_get_item_categories IS
1759 SELECT ordered_item
1760 FROM oe_blanket_lines_all
1761 WHERE header_id = p_doc_id
1762 AND p_doc_type = G_BSA_DOC_TYPE
1763 AND item_identifier_type = 'CAT'
1764 AND line_category_code = 'ORDER'
1765 ORDER BY ordered_item;
1766
1767
1768 -- Cursor to retrieve categories to which the INT (internal) and non-INT
1769 -- items in the BSA or Sales Order belong
1770 -- Note: the inventory_item_id stored in oe_blanket_lines_all and oe_order_lines_all
1771 -- against the non-INT item is that of the mapped INT item so we can use it
1772 -- directly to get the item category
1773 --
1774 -- Returns non-translatable code eg. HOSPITAL.MISC
1775 --
1776 CURSOR c_get_derived_item_category (cp_org_id NUMBER,
1777 cp_inventory_item_id NUMBER) IS
1778 SELECT category_concat_segs
1779 FROM mtl_item_categories_v
1780 WHERE inventory_item_id = cp_inventory_item_id
1781 AND organization_id = cp_org_id -- should be inventory master org
1782 AND structure_id = 101; -- hardcoded to 101 i.e. Item Categories(Inv.Items) for OM
1783
1784
1785 l_bsa_derived_item_category VARCHAR2(2500); --c_get_derived_item_category%ROWTYPE;
1786 l_so_derived_item_category VARCHAR2(2500); --c_get_derived_item_category%ROWTYPE;
1787
1788 j BINARY_INTEGER := 1;
1789 l_master_org_id NUMBER;
1790 lx_ordered_item VARCHAR2(2000);
1791 lx_inventory_item VARCHAR2(2000);
1792 l_current_org_id NUMBER;
1793
1794 CURSOR c_get_doc_org_id IS
1795 SELECT org_id
1796 FROM oe_blanket_headers_all
1797 WHERE header_id = p_doc_id
1798 AND p_doc_type = G_BSA_DOC_TYPE
1799
1800 UNION ALL
1801
1802 SELECT org_id
1803 FROM oe_order_headers_all
1804 WHERE header_id = p_doc_id
1805 AND p_doc_type = G_SO_DOC_TYPE;
1806
1807 --AK
1808
1809 BEGIN
1810
1811 l_package_procedure := G_PKG_NAME || '.' || l_api_name;
1812
1813 x_return_status := FND_API.G_RET_STS_SUCCESS;
1814
1815 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1816 l_debug := true;
1817 END IF;
1818
1819 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1820 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'10: Entered ' || l_package_procedure);
1821 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'20: p_doc_id: ' || p_doc_id);
1822 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'30: p_doc_type: ' || p_doc_type);
1823 END IF;
1824
1825 --
1826 -- Standard call to check for call compatibility.
1827 --
1828 IF NOT FND_API.Compatible_API_Call (l_api_version,
1829 p_api_version,
1830 l_api_name,
1831 G_PKG_NAME)
1832 THEN
1833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1834 END IF;
1835
1836 --
1837 -- Initialize message list if p_init_msg_list is set to TRUE.
1838 --
1839 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1840 FND_MSG_PUB.initialize;
1841 END IF;
1842
1843 --AK
1844 --Get Current Org Id from context
1845 OPEN c_get_doc_org_id;
1846 FETCH c_get_doc_org_id INTO l_current_org_id;
1847 CLOSE c_get_doc_org_id;
1848
1849 --Get inventory master org
1850
1851 l_master_org_id := TO_NUMBER(oe_sys_parameters.value (
1852 param_name => 'MASTER_ORGANIZATION_ID',p_org_id => l_current_org_id ));
1853
1854 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1855 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'35: l_master_org_id: ' || l_master_org_id);
1856 END IF;
1857 --AK
1858
1859 IF p_doc_type = G_BSA_DOC_TYPE THEN
1860 OPEN c_get_bsa_line_variables;
1861 LOOP
1862 FETCH c_get_bsa_line_variables INTO l_bsa_line_variables;
1863 EXIT WHEN c_get_bsa_line_variables%NOTFOUND;
1864
1865 l_line_number := to_char(l_bsa_line_variables.line_number);
1866
1867 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1868 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'40: line_number = '||l_bsa_line_variables.line_number );
1869 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'50: payment_term_id = '||l_bsa_line_variables.payment_term_id );
1870 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'60: item_id = '||l_bsa_line_variables.inventory_item_id );
1871 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'70: org_id = '||l_bsa_line_variables.org_id );
1872 END IF;
1873
1874
1875 l_line_count := l_line_count+1;
1876
1877 IF l_bsa_line_variables.payment_term_id IS NOT NULL THEN
1878 l_index := l_index+1;
1879 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
1880 x_line_sys_var_value_tbl(l_index).variable_code := G_LINE_PAYMENT_TERM_CODE;
1881 x_line_sys_var_value_tbl(l_index).variable_value := l_bsa_line_variables.payment_term_id;
1882 x_line_sys_var_value_tbl(l_index).item_id := l_bsa_line_variables.inventory_item_id;
1883 x_line_sys_var_value_tbl(l_index).org_id := l_bsa_line_variables.org_id;
1884 END IF;
1885
1886 IF l_bsa_line_variables.invoicing_rule_id IS NOT NULL THEN
1887 l_index := l_index+1;
1888 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
1889 x_line_sys_var_value_tbl(l_index).variable_code := G_LINE_INVOICING_RULE_CODE;
1890 x_line_sys_var_value_tbl(l_index).variable_value := l_bsa_line_variables.invoicing_rule_id;
1891 x_line_sys_var_value_tbl(l_index).item_id := l_bsa_line_variables.inventory_item_id;
1892 x_line_sys_var_value_tbl(l_index).org_id := l_bsa_line_variables.org_id;
1893 END IF;
1894
1895 --AK
1896 IF l_bsa_line_variables.item_identifier_type = 'INT' THEN
1897 l_index := l_index+1;
1898 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
1899 x_line_sys_var_value_tbl(l_index).variable_code := G_ITEM_CODE;
1900 x_line_sys_var_value_tbl(l_index).variable_value := l_bsa_line_variables.ordered_item;
1901 x_line_sys_var_value_tbl(l_index).item_id := l_bsa_line_variables.inventory_item_id;
1902 x_line_sys_var_value_tbl(l_index).org_id := l_bsa_line_variables.org_id;
1903 ELSIF l_bsa_line_variables.item_identifier_type <> 'INT' THEN
1904 --map the non-INT items to INT items
1905
1906 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1907 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'71: Mapping non-INT item to INT item, Calling OE_Id_To_Value.Ordered_Item ');
1908 END IF;
1909
1910 -- Map non-INT item to INT item
1911 OE_Id_To_Value.Ordered_Item (
1912 p_item_identifier_type => l_bsa_line_variables.item_identifier_type,
1913 p_inventory_item_id => l_bsa_line_variables.inventory_item_id,
1914 p_organization_id => l_master_org_id,
1915 p_ordered_item_id => l_bsa_line_variables.ordered_item_id,
1916 p_sold_to_org_id => l_bsa_line_variables.sold_to_org_id,
1917 p_ordered_item => l_bsa_line_variables.ordered_item,
1918 x_ordered_item => lx_ordered_item,
1919 x_inventory_item => lx_inventory_item
1920 );
1921 l_index := l_index+1;
1922 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
1923 x_line_sys_var_value_tbl(l_index).variable_code := G_ITEM_CODE;
1924 x_line_sys_var_value_tbl(l_index).variable_value := lx_inventory_item;
1925 x_line_sys_var_value_tbl(l_index).item_id := l_bsa_line_variables.inventory_item_id;
1926 x_line_sys_var_value_tbl(l_index).org_id := l_bsa_line_variables.org_id;
1927
1928 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1929 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'72: x_line_var_value_tbl('||j||').variable_code: ' || G_ITEM_CODE);
1930 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'73: x_line_var_value_tbl('||j||').variable_value_id: ' || lx_inventory_item);
1931 END IF;
1932 END IF;
1933
1934 IF l_bsa_line_variables.inventory_item_id IS NOT NULL THEN
1935
1936 FOR c_get_item_categories_rec IN c_get_item_categories LOOP
1937 l_index := l_index+1;
1938 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
1939 x_line_sys_var_value_tbl(l_index).variable_code := G_ITEM_CATEGORY_CODE;
1940 x_line_sys_var_value_tbl(l_index).variable_value := c_get_item_categories_rec.ordered_item;
1941 x_line_sys_var_value_tbl(l_index).item_id := l_bsa_line_variables.inventory_item_id;
1942 x_line_sys_var_value_tbl(l_index).org_id := l_bsa_line_variables.org_id;
1943 END LOOP;
1944
1945 OPEN c_get_derived_item_category(l_master_org_id, l_bsa_line_variables.inventory_item_id);
1946 FETCH c_get_derived_item_category INTO l_bsa_derived_item_category;
1947 CLOSE c_get_derived_item_category;
1948
1949 l_index := l_index+1;
1950 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
1951 x_line_sys_var_value_tbl(l_index).variable_code := G_ITEM_CATEGORY_CODE;
1952 x_line_sys_var_value_tbl(l_index).variable_value := l_bsa_derived_item_category;
1953 x_line_sys_var_value_tbl(l_index).item_id := l_bsa_line_variables.inventory_item_id;
1954 x_line_sys_var_value_tbl(l_index).org_id := l_bsa_line_variables.org_id;
1955
1956 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1957 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'74: x_line_var_value_tbl('||j||').variable_code: '||G_ITEM_CATEGORY_CODE);
1958 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'75: x_line_var_value_tbl('||j||').variable_value_id: '||l_bsa_derived_item_category);
1959 END IF;
1960 END IF;
1961
1962 --AK
1963 END LOOP;
1964
1965 CLOSE c_get_bsa_line_variables;
1966
1967 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
1968 OPEN c_get_so_line_variables;
1969 LOOP
1970
1971 FETCH c_get_so_line_variables INTO l_so_line_variables;
1972 EXIT WHEN c_get_so_line_variables%NOTFOUND;
1973
1974 --l_line_number := l_so_line_variables.line_number;
1975
1976 -- Bug 4768964 Logic provided by OM
1977 IF l_so_line_variables.service_number is not null then
1978 IF l_so_line_variables.option_number is not null then
1979 IF l_so_line_variables.component_number is not null then
1980 l_line_number := l_so_line_variables.line_number||'.'||l_so_line_variables.shipment_number||'.'||
1981 l_so_line_variables.option_number||'.'||l_so_line_variables.component_number||'.'||
1982 l_so_line_variables.service_number;
1983 ELSE
1984 l_line_number := l_so_line_variables.line_number||'.'||l_so_line_variables.shipment_number||'.'||
1985 l_so_line_variables.option_number||'..'||l_so_line_variables.service_number;
1986 END IF;
1987
1988 --- if a option is not attached
1989 ELSE
1990 IF l_so_line_variables.component_number is not null then
1991 l_line_number := l_so_line_variables.line_number||'.'||l_so_line_variables.shipment_number||'..'||
1992 l_so_line_variables.component_number||'.'||l_so_line_variables.service_number;
1993 ELSE
1994 l_line_number := l_so_line_variables.line_number||'.'||l_so_line_variables.shipment_number||'...'||
1995 l_so_line_variables.service_number;
1996 END IF;
1997
1998 END IF; /* if option number is not null */
1999
2000 -- if the service number is null
2001 ELSE
2002 IF l_so_line_variables.option_number is not null then
2003 IF l_so_line_variables.component_number is not null then
2004 l_line_number := l_so_line_variables.line_number||'.'||l_so_line_variables.shipment_number||'.'||
2005 l_so_line_variables.option_number||'.'||l_so_line_variables.component_number;
2006 ELSE
2007 l_line_number := l_so_line_variables.line_number||'.'||l_so_line_variables.shipment_number||'.'||
2008 l_so_line_variables.option_number;
2009 END IF;
2010 --- if a option is not attached
2011 ELSE
2012 IF l_so_line_variables.component_number is not null then
2013 l_line_number := l_so_line_variables.line_number||'.'||l_so_line_variables.shipment_number||'..'||
2014 l_so_line_variables.component_number;
2015 ELSE
2016 IF (l_so_line_variables.line_number is NULL and l_so_line_variables.shipment_number is NULL ) THEN
2017 l_line_number := NULL;
2018 ELSE
2019 l_line_number := l_so_line_variables.line_number||'.'||l_so_line_variables.shipment_number;
2020 END IF;
2021 END IF;
2022
2023 END IF; /* if option number is not null */
2024
2025 END IF; /* if service number is not null */
2026 -- Bug 4768964 Logic provided by OM
2027
2028 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2029 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'80: line_number = '||l_so_line_variables.line_number );
2030 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'90: payment_term_id = '||l_so_line_variables.payment_term_id );
2031 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'100: invoicing_rule_id = '||l_so_line_variables.invoicing_rule_id );
2032 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'110: agreement_id = '||l_so_line_variables.agreement_id );
2033 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'120: item_id = '||l_so_line_variables.inventory_item_id );
2034 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'130: org_id = '||l_so_line_variables.org_id );
2035 END IF;
2036
2037
2038 l_line_count := l_line_count+1;
2039
2040 IF l_so_line_variables.payment_term_id IS NOT NULL THEN
2041 l_index := l_index+1;
2042 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
2043 x_line_sys_var_value_tbl(l_index).variable_code := G_LINE_PAYMENT_TERM_CODE;
2044 x_line_sys_var_value_tbl(l_index).variable_value := l_so_line_variables.payment_term_id;
2045 x_line_sys_var_value_tbl(l_index).item_id := l_so_line_variables.inventory_item_id;
2046 x_line_sys_var_value_tbl(l_index).org_id := l_so_line_variables.org_id;
2047 END IF;
2048
2049 IF l_so_line_variables.invoicing_rule_id IS NOT NULL THEN
2050 l_index := l_index+1;
2051 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
2052 x_line_sys_var_value_tbl(l_index).variable_code := G_LINE_INVOICING_RULE_CODE;
2053 x_line_sys_var_value_tbl(l_index).variable_value := l_so_line_variables.invoicing_rule_id;
2054 x_line_sys_var_value_tbl(l_index).item_id := l_so_line_variables.inventory_item_id;
2055 x_line_sys_var_value_tbl(l_index).org_id := l_so_line_variables.org_id;
2056 END IF;
2057
2058 IF l_so_line_variables.agreement_id IS NOT NULL THEN
2059 l_index := l_index+1;
2060 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
2061 x_line_sys_var_value_tbl(l_index).variable_code := G_LINE_PA_NAME_CODE;
2062 x_line_sys_var_value_tbl(l_index).variable_value := l_so_line_variables.agreement_id;
2063 x_line_sys_var_value_tbl(l_index).item_id := l_so_line_variables.inventory_item_id;
2064 x_line_sys_var_value_tbl(l_index).org_id := l_so_line_variables.org_id;
2065 END IF;
2066
2067 --AK
2068 IF l_so_line_variables.item_identifier_type = 'INT' THEN
2069 l_index := l_index+1;
2070 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
2071 x_line_sys_var_value_tbl(l_index).variable_code := G_ITEM_CODE;
2072 x_line_sys_var_value_tbl(l_index).variable_value := l_so_line_variables.ordered_item;
2073 x_line_sys_var_value_tbl(l_index).item_id := l_so_line_variables.inventory_item_id;
2074 x_line_sys_var_value_tbl(l_index).org_id := l_so_line_variables.org_id;
2075 ELSIF l_so_line_variables.item_identifier_type <> 'INT' THEN
2076 --map the non-INT items to INT items
2077
2078 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2079 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'90: Mapping non-INT item to INT item, Calling OE_Id_To_Value.Ordered_Item ');
2080 END IF;
2081
2082 -- Map non-INT item to INT item
2083 OE_Id_To_Value.Ordered_Item (
2084 p_item_identifier_type => l_so_line_variables.item_identifier_type,
2085 p_inventory_item_id => l_so_line_variables.inventory_item_id,
2086 p_organization_id => l_master_org_id,
2087 p_ordered_item_id => l_so_line_variables.ordered_item_id,
2088 p_sold_to_org_id => l_so_line_variables.sold_to_org_id,
2089 p_ordered_item => l_so_line_variables.ordered_item,
2090 x_ordered_item => lx_ordered_item,
2091 x_inventory_item => lx_inventory_item
2092 );
2093 l_index := l_index+1;
2094 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
2095 x_line_sys_var_value_tbl(l_index).variable_code := G_ITEM_CODE;
2096 x_line_sys_var_value_tbl(l_index).variable_value := lx_inventory_item;
2097 x_line_sys_var_value_tbl(l_index).item_id := l_so_line_variables.inventory_item_id;
2098 x_line_sys_var_value_tbl(l_index).org_id := l_so_line_variables.org_id;
2099
2100 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2101 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'100: x_line_var_value_tbl('||j||').variable_code: ' || G_ITEM_CODE);
2102 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'110: x_line_var_value_tbl('||j||').variable_value_id: ' || lx_inventory_item);
2103 END IF;
2104 END IF;
2105
2106 IF l_so_line_variables.inventory_item_id IS NOT NULL THEN
2107
2108 FOR c_get_item_categories_rec IN c_get_item_categories LOOP
2109 l_index := l_index+1;
2110 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
2111 x_line_sys_var_value_tbl(l_index).variable_code := G_ITEM_CATEGORY_CODE;
2112 x_line_sys_var_value_tbl(l_index).variable_value := c_get_item_categories_rec.ordered_item;
2113 x_line_sys_var_value_tbl(l_index).item_id := l_so_line_variables.inventory_item_id;
2114 x_line_sys_var_value_tbl(l_index).org_id := l_so_line_variables.org_id;
2115 END LOOP;
2116
2117
2118 OPEN c_get_derived_item_category(l_master_org_id, l_so_line_variables.inventory_item_id);
2119 FETCH c_get_derived_item_category INTO l_so_derived_item_category;
2120 CLOSE c_get_derived_item_category;
2121
2122 l_index := l_index+1;
2123 x_line_sys_var_value_tbl(l_index).line_number := l_line_number;
2124 x_line_sys_var_value_tbl(l_index).variable_code := G_ITEM_CATEGORY_CODE;
2125 x_line_sys_var_value_tbl(l_index).variable_value := l_so_derived_item_category;
2126 x_line_sys_var_value_tbl(l_index).item_id := l_so_line_variables.inventory_item_id;
2127 x_line_sys_var_value_tbl(l_index).org_id := l_so_line_variables.org_id;
2128
2129
2130 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2131 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'74: x_line_var_value_tbl('||j||').variable_code: '||G_ITEM_CATEGORY_CODE);
2132 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'75: x_line_var_value_tbl('||j||').variable_value_id: '||l_so_derived_item_category);
2133 END IF;
2134 END IF;
2135
2136 --AK
2137 END LOOP;
2138
2139 CLOSE c_get_so_line_variables;
2140
2141 END IF;
2142
2143 x_line_count := l_line_count;
2144
2145 -- Fix for 4768964 to show line number
2146 IF p_doc_type = G_BSA_DOC_TYPE THEN
2147 x_line_variables_count := 4; --Item, Item Category, Payment term, Invoicing rule
2148 ELSIF p_doc_type = G_SO_DOC_TYPE THEN
2149 x_line_variables_count := 5; --Item, Item Category, Payment term, Invoicing rule, Price Agreement
2150 END IF;
2151
2152 IF l_line_count = 0 THEN
2153 x_line_count := 1; -- Since no Lines, need to set line count to 1 for the CX Java code
2154 x_line_variables_count := 0; -- Since no Lines, need to set line variables count to 0 for the CX Java code
2155 END IF;
2156
2157 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2158 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'170: End of '||l_package_procedure||'for line level variables, x_return_status: '|| x_return_status);
2159 END IF;
2160
2161 EXCEPTION
2162
2163 WHEN FND_API.G_EXC_ERROR THEN
2164 IF c_get_bsa_line_variables%ISOPEN THEN
2165 CLOSE c_get_bsa_line_variables;
2166 END IF;
2167
2168 IF c_get_so_line_variables%ISOPEN THEN
2169 CLOSE c_get_so_line_variables;
2170 END IF;
2171
2172
2173 x_return_status := FND_API.G_RET_STS_ERROR ;
2174
2175 FND_MSG_PUB.Count_And_Get(
2176 p_count => x_msg_count,
2177 p_data => x_msg_data );
2178
2179 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2180 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'180: '||l_package_procedure||' In the FND_API.G_EXC_ERROR section');
2181 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'190: x_return_status = '||x_return_status);
2182 END IF;
2183
2184 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2185 IF c_get_bsa_line_variables%ISOPEN THEN
2186 CLOSE c_get_bsa_line_variables;
2187 END IF;
2188
2189 IF c_get_so_line_variables%ISOPEN THEN
2190 CLOSE c_get_so_line_variables;
2191 END IF;
2192
2193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2194
2195 FND_MSG_PUB.Count_And_Get(
2196 p_count => x_msg_count,
2197 p_data => x_msg_data );
2198
2199 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2200 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'200: '||l_package_procedure||' In the FND_API.G_EXC_UNEXPECTED_ERROR section');
2201 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'210: x_return_status = '||x_return_status);
2202 END IF;
2203
2204 WHEN OTHERS THEN
2205 IF c_get_bsa_line_variables%ISOPEN THEN
2206 CLOSE c_get_bsa_line_variables;
2207 END IF;
2208
2209 IF c_get_so_line_variables%ISOPEN THEN
2210 CLOSE c_get_so_line_variables;
2211 END IF;
2212
2213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2214
2215 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2216 FND_MSG_PUB.Add_Exc_Msg(
2217 G_PKG_NAME ,
2218 l_api_name );
2219 END IF;
2220
2221 FND_MSG_PUB.Count_And_Get(
2222 p_count => x_msg_count,
2223 p_data => x_msg_data );
2224
2225 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2226 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'220: '||l_package_procedure||' In the OTHERS section');
2227 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'230: x_return_status = '||x_return_status);
2228 END IF;
2229
2230 END get_line_variable_values;
2231
2232
2233 END OKC_XPRT_OM_INT_PVT;