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