eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OKC_XPRT_QUOTE_INT_PVT

Source


1 PACKAGE BODY OKC_XPRT_QUOTE_INT_PVT AS
2 /* $Header: OKCVXQUOTEINTB.pls 120.11 2006/02/15 01:42:28 arsundar noship $ */
3 
4   ------------------------------------------------------------------------------
5   -- GLOBAL CONSTANTS
6   ------------------------------------------------------------------------------
7 
8 
9   G_FALSE                       CONSTANT VARCHAR2(1)    := FND_API.G_FALSE;
10   G_TRUE                        CONSTANT VARCHAR2(1)    := FND_API.G_TRUE;
11 
12   G_RET_STS_SUCCESS             CONSTANT VARCHAR2(1)    := FND_API.G_RET_STS_SUCCESS;
13   G_RET_STS_ERROR               CONSTANT VARCHAR2(1)    := FND_API.G_RET_STS_ERROR;
14   G_RET_STS_UNEXP_ERROR         CONSTANT VARCHAR2(1)    := FND_API.G_RET_STS_UNEXP_ERROR;
15 
16   G_PKG_NAME                    CONSTANT VARCHAR2(30)   := 'OKC_XPRT_QUOTE_INT_PVT';
17   G_APP_NAME                    CONSTANT VARCHAR2(30)   := OKC_API.G_APP_NAME;
18   G_STMT_LEVEL                  CONSTANT NUMBER         := FND_LOG.LEVEL_STATEMENT;
19   G_MODULE_NAME                 CONSTANT VARCHAR2(250)  := 'OKC.PLSQL.'||G_PKG_NAME||'.';
20 
21   G_ITEM_CODE                   CONSTANT VARCHAR2(30)   := 'OKC$S_ITEM';
22   G_ITEM_CATEGORY_CODE          CONSTANT VARCHAR2(30)   := 'OKC$S_ITEM_CATEGORY';
23   G_PA_NAME_CODE                CONSTANT VARCHAR2(30)   := 'OKC$S_PA_NAME';
24   G_PA_NAME_EXIST_CODE          CONSTANT VARCHAR2(30)   := 'OKC$S_PRC_AGR_EXIST';
25   G_CUSTOMER_NAME_CODE          CONSTANT VARCHAR2(30)   := 'OKC$S_CUSTOMER_NAME';
26   G_CURRENCY_CODE               CONSTANT VARCHAR2(30)   := 'OKC$S_CURRENCY_CODE';
27   G_FREIGHT_TERMS_CODE          CONSTANT VARCHAR2(30)   := 'OKC$S_FREIGHT_TERMS';
28   G_SHIPPING_METHOD_CODE        CONSTANT VARCHAR2(30)   := 'OKC$S_SHIPPING_METHOD';
29   G_PAYMENT_TERM_CODE           CONSTANT VARCHAR2(30)   := 'OKC$S_PAYMENT_TERM';
30   G_BILLTO_COUNTRY_CODE         CONSTANT VARCHAR2(30)   := 'OKC$S_BILLTO_COUNTRY';
31   G_SHIPTO_COUNTRY_CODE         CONSTANT VARCHAR2(30)   := 'OKC$S_SHIPTO_COUNTRY';
32   G_SOLDTO_COUNTRY_CODE         CONSTANT VARCHAR2(30)   := 'OKC$S_SOLDTO_COUNTRY';
33   G_CUST_PO_EXIST_CODE          CONSTANT VARCHAR2(30)   := 'OKC$S_CUST_PO_EXIST';
34   G_PRICE_LIST_CODE             CONSTANT VARCHAR2(30)   := 'OKC$S_PRICE_LIST';
35   G_TAX_HANDL_CODE              CONSTANT VARCHAR2(30)   := 'OKC$S_TAX_HANDL';
36   G_ORDER_TYPE_CODE             CONSTANT VARCHAR2(30)   := 'OKC$S_ORDER_TYPE';
37   G_FOB_CODE                    CONSTANT VARCHAR2(30)   := 'OKC$S_FOB';
38   G_PAYMENT_TYPE_CODE           CONSTANT VARCHAR2(30)   := 'OKC$S_PAYMENT_TYPE';
39   G_SALES_CHANNEL_CODE          CONSTANT VARCHAR2(30)   := 'OKC$S_SALES_CHANNEL';
40   G_TOTAL_CODE                  CONSTANT VARCHAR2(30)   := 'OKC$S_TOTAL';
41   G_TOTAL_ADJUST_AMT_CODE       CONSTANT VARCHAR2(30)   := 'OKC$S_ADJUST_AMOUNT';
42   G_TOTAL_ADJUST_PCT_CODE       CONSTANT VARCHAR2(30)   := 'OKC$S_ADJUST_PERCENT';
43   G_BILLTO_CUST_NAME_CODE       CONSTANT VARCHAR2(30)   := 'OKC$S_BILLTO_CUST_NAME';
44   G_SHIPTO_CUST_NAME_CODE       CONSTANT VARCHAR2(30)   := 'OKC$S_SHIPTO_CUST_NAME';
45   G_SALES_DOC_TYPE_CODE         CONSTANT VARCHAR2(30)   := 'OKC$S_SALES_DOC_TYPE';
46   G_CUST_CATEGORY_CODE          CONSTANT VARCHAR2(30)   := 'OKC$S_CUST_CAT';
47   G_CUST_CLASS_CODE             CONSTANT VARCHAR2(30)   := 'OKC$S_CUST_CLASS';
48   G_CUST_PROF_CLASS_CODE        CONSTANT VARCHAR2(30)   := 'OKC$S_CUST_PROF_CLASS';
49   G_CUST_CRDT_RATE_CODE         CONSTANT VARCHAR2(30)   := 'OKC$S_CUST_CRDT_RATE';
50   G_CUST_CRDT_CLASS_CODE        CONSTANT VARCHAR2(30)   := 'OKC$S_CUST_CRDT_CLASS';
51   G_CUST_RISK_CODE              CONSTANT VARCHAR2(30)   := 'OKC$S_CUST_RISK_CODE';
52 
53   G_PA_NUMBER_CODE                CONSTANT VARCHAR2(30)   := 'OKC$S_PA_NUMBER';
54   G_QUOTE_NUMBER_CODE             CONSTANT VARCHAR2(30)   := 'OKC$S_QUOTE_NUMBER';
55   G_CUSTOMER_NUMBER_CODE          CONSTANT VARCHAR2(30)   := 'OKC$S_CUSTOMER_NUMBER';
56   G_CUST_PO_NUMBER_CODE           CONSTANT VARCHAR2(30)   := 'OKC$S_CUST_PO_NUMBER';
57   G_VERSION_NUMBER_CODE           CONSTANT VARCHAR2(30)   := 'OKC$S_VERSION_NUMBER';
58   G_CUST_CONTACT_NAME_CODE        CONSTANT VARCHAR2(30)   := 'OKC$S_CUST_CONTACT_NAME';
59   G_SALESREP_NAME_CODE            CONSTANT VARCHAR2(30)   := 'OKC$S_SALESREP_NAME';
60   G_CURRENCY_NAME_CODE            CONSTANT VARCHAR2(30)   := 'OKC$S_CURRENCY_NAME';
61   G_CURRENCY_SYMBOL_CODE          CONSTANT VARCHAR2(30)   := 'OKC$S_CURRENCY_SYMBOL';
62   G_SUPPLIER_NAME_CODE            CONSTANT VARCHAR2(30)   := 'OKC$S_SUPPLIER_NAME';
63 
64   G_SHIPMENT_PRIORITY_CODE        CONSTANT VARCHAR2(30)   := 'OKC$S_SHIPMENT_PRIORITY';
65   G_SITE_ID_CODE                  CONSTANT VARCHAR2(30)   := 'OKC$S_SITE_ID';
66 
67   -- Added for Agreement type support
68   G_PA_TYPE_CODE                  CONSTANT VARCHAR2(30)   := 'OKC$S_PA_TYPE';
69 
70   -- Added for line level variables
71   G_LINE_PAYMENT_TERM_CODE        CONSTANT VARCHAR2(30)   := 'OKC$S_LINE_PAYMENT_TERM';
72   G_LINE_FOB_CODE                 CONSTANT VARCHAR2(30)   := 'OKC$S_LINE_FOB';
73 
74 
75 PROCEDURE Get_clause_Variable_Values (
76     p_api_version               IN            NUMBER,
77     p_init_msg_list             IN            VARCHAR2,
78     x_return_status             OUT NOCOPY    VARCHAR2,
79     x_msg_count                 OUT NOCOPY    NUMBER,
80     x_msg_data                  OUT NOCOPY    VARCHAR2,
81     p_doc_id                    IN            NUMBER,
82     p_sys_var_value_tbl         IN OUT NOCOPY OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type )
83 IS
84 
85 l_api_name          VARCHAR2(30) := 'get_clause_variable_values';
86 l_package_procedure VARCHAR2(60);
87 l_api_version 		CONSTANT NUMBER := 1;
88 l_debug             Boolean;
89 l_party_id		HZ_PARTIES.party_id%type;
90 l_module            VARCHAR2(250)   := G_MODULE_NAME||l_api_name;
91 l_expert_yes		FND_FLEX_VALUES.FLEX_VALUE_ID%type;
92 l_expert_no		FND_FLEX_VALUES.FLEX_VALUE_ID%type;
93 
94 --
95 -- Cursor to fetch the flex value id for Yes and No from the OKC_XPRT_YES_NO value set
96 --
97 CURSOR c_get_expert_yes_no(p_yes_no VARCHAR2) IS
98 select a.FLEX_VALUE_ID
99 from fnd_flex_values a, fnd_flex_value_sets b
100 where b.flex_value_set_name = 'OKC_XPRT_YES_NO'
101 and a.FLEX_VALUE_SET_ID = b.FLEX_VALUE_SET_ID
102 and a.FLEX_VALUE = p_yes_no;
103 
104 
105 --
106 -- Cursor to fetch the header attributes for the quote
107 --
108 CURSOR c_get_quote_header_variables IS
109 SELECT
110       quote.cust_account_id,
111       quote.currency_code,
112       quote.contract_id price_agreement_id,
113 --      decode(nvl(to_char(quote.contract_id),'X'),'X','N','Y') price_agreement_exist,
114       decode(nvl(to_char(quote.contract_id),'X'),'X',l_expert_no,l_expert_yes) price_agreement_exist,
115       quote.total_adjusted_percent,
116       quote.total_adjusted_amount,
117       quote.total_quote_price,
118       quote.order_type_id,
119       quote.party_id,
120       quote.invoice_to_cust_account_id,
121       quote.invoice_to_party_site_id,
122       quote.sold_to_party_site_id,
123       quote.price_list_id,
124       quote.sales_channel_code,
125 --      decode(nvl(payments.cust_po_number,'X'),'X','N','Y') cust_po_num_exist,
126       decode(nvl(payments.cust_po_number,'X'),'X',l_expert_no,l_expert_yes) cust_po_num_exist,
127       payments.payment_term_id,
128       payments.payment_type_code,
129       nvl(tax.tax_exempt_flag,'S') tax_exempt_flag,
130       shipments.freight_terms_code,
131       shipments.ship_method_code,
132       shipments.fob_code,
133       shipments.ship_to_cust_account_id,
134       shipments.ship_to_party_site_id,
135 -- XY
136 	 quote.quote_number,
137 --	 quote.cust_party_id,
138 	 quote.quote_version,
139 	 quote.resource_id,
140 	 quote.org_id,
141 	 payments.cust_po_number,
142 	 shipments.shipment_priority_code,
143 	 quote.minisite_id
144 -- XY
145 FROM
146       aso_payments payments,
147       aso_tax_details tax,
148       aso_shipments shipments,
149       aso_quote_headers_all quote
150 WHERE
151       quote.quote_header_id = payments.quote_header_id(+)
152  AND  payments.quote_line_id(+) IS NULL
153  AND  quote.quote_header_id = tax.quote_header_id(+)
154  AND  tax.quote_line_id(+) IS NULL
155  AND  quote.quote_header_id = shipments.quote_header_id(+)
156  AND  shipments.quote_line_id(+) IS NULL
157  AND  quote.quote_header_id = p_doc_id;
158 
159 
160 --
161 -- Cursor to fetch the customer information
162 --
163 
164 CURSOR c_get_cust_info_var(p_sold_to_org_id NUMBER) IS
165 SELECT
166      hzp.category_code,
167 	hzp.party_id,
168 	hzc.customer_class_code,
169 	hzcp.profile_class_id,
170 	hzcp.credit_rating,
171 	hzcp.credit_classification,
172 	hzcp.risk_code
173 FROM
174      hz_customer_profiles hzcp,
175      hz_cust_accounts hzc,
176      hz_parties hzp
177 WHERE
178      hzc.cust_account_id = hzcp.cust_account_id
179 AND  hzcp.site_use_id is null
180 AND  hzc.party_id = hzcp.party_id
181 AND  hzc.party_id = hzp.party_id
182 AND  hzc.cust_account_id = p_sold_to_org_id;
183 
184 
185 --
186 -- Cursor to get the bill to, ship to and sold to countries
187 -- for a Quote
188 --
189 
190 CURSOR c_get_country_quote(p_party_site_id NUMBER) IS
191 SELECT
192      loc.country
193 FROM
194      hz_locations loc,
195      hz_party_sites ps
196 WHERE
197      ps.location_id = loc.location_id
198 AND  ps.party_site_id = p_party_site_id;
199 
200  --
201  -- AK: Cursor to get Agreement type for
202  -- Quoted Order and Sales Order
203  --
204 
205  CURSOR c_get_pa_type(p_agreement_id NUMBER) IS
206  SELECT
207      pc.agreement_type_code
208  FROM
209      --oe_pricing_contracts_v pc -- Commented for Perf Bug 5027295
210      oe_agreements_b pc          -- Added for Perf Bug 5027295
211  WHERE
212      pc.agreement_id = p_agreement_id;
213 
214 l_quote_header_variables  c_get_quote_header_variables%ROWTYPE;
215 l_cust_info_variables	 c_get_cust_info_var%ROWTYPE;
216 l_customer_category		 hz_parties.category_code%TYPE;
217 
218 
219 BEGIN
220 
221    x_return_status := FND_API.G_RET_STS_SUCCESS;
222 
223    l_package_procedure := G_PKG_NAME || '.' || l_api_name;
224 
225    IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
226      l_debug := true;
227    END IF;
228 
229    IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
230        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'10: Entered ' || l_package_procedure);
231        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'20: p_doc_id: ' || p_doc_id);
232    END IF;
233 
234    --
235    -- Standard call to check for call compatibility.
236    --
237    IF NOT FND_API.Compatible_API_Call (l_api_version,
238        	       	    	    	 	p_api_version,
239         	    	    	    			l_api_name,
240     		    	    	    			G_PKG_NAME) THEN
241    	RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242    END IF;
243 
244    --
245    -- Initialize message list if p_init_msg_list is set to TRUE.
246    --
247    IF FND_API.to_Boolean( p_init_msg_list ) THEN
248   	FND_MSG_PUB.initialize;
249    END IF;
250 
251 
252    OPEN c_get_expert_yes_no('Yes');
253    FETCH c_get_expert_yes_no INTO l_expert_yes;
254    CLOSE c_get_expert_yes_no;
255 
256    OPEN c_get_expert_yes_no('No');
257    FETCH c_get_expert_yes_no INTO l_expert_no;
258    CLOSE c_get_expert_yes_no;
259 
260   IF p_sys_var_value_tbl.FIRST IS NOT NULL THEN
261 
262      OPEN c_get_quote_header_variables;
263      FETCH c_get_quote_header_variables INTO l_quote_header_variables;
264      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
265         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'30:c_get_quote_header_variables%ROWCOUNT:  ' || c_get_quote_header_variables%ROWCOUNT);
266         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'40:Values from l_quote_header_variables are:');
267 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,' ');
268 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'************************ ');
269 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'50: cust_account_id =        '||l_quote_header_variables.cust_account_id );
270 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'60: currency_code =          '||l_quote_header_variables.currency_code );
271 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'70: price_agreement_id =     '||l_quote_header_variables.price_agreement_id );
272 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'80: price_agreement_exist =  '||l_quote_header_variables.price_agreement_exist );
273 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'90: total_adjusted_percent = '||l_quote_header_variables.total_adjusted_percent );
274 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'100: total_adjusted_amount = '||l_quote_header_variables.total_adjusted_amount );
275 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'110: total_quote_price =     '||l_quote_header_variables.total_quote_price );
276 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'120: order_type_id =         '||l_quote_header_variables.order_type_id );
277 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'130: party_id =              '||l_quote_header_variables.party_id );
278 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'140: invoice_to_cust_account_id = '||l_quote_header_variables.invoice_to_cust_account_id );
279 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'150: invoice_to_party_site_id = '||l_quote_header_variables.invoice_to_party_site_id );
280 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'160: sold_to_party_site_id = '||l_quote_header_variables.sold_to_party_site_id );
281 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'170: price_list_id =         '||l_quote_header_variables.price_list_id );
282 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'180: sales_channel_code =    '||l_quote_header_variables.sales_channel_code );
283 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'190: cust_po_num_exist =     '||l_quote_header_variables.cust_po_num_exist );
284 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'200: payment_term_id =       '||l_quote_header_variables.payment_term_id );
285 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'210: payment_type_code =     '||l_quote_header_variables.payment_type_code );
286 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'220: tax_exempt_flag =       '||l_quote_header_variables.tax_exempt_flag );
287 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'230: freight_terms_code =    '||l_quote_header_variables.freight_terms_code );
288 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'240: ship_method_code =      '||l_quote_header_variables.ship_method_code );
289 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'250: fob_code =              '||l_quote_header_variables.fob_code );
290 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'260: ship_to_cust_account_id = '||l_quote_header_variables.ship_to_cust_account_id );
291 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'270: ship_to_party_site_id = '||l_quote_header_variables.ship_to_party_site_id );
292 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'272: quote_number =          '||l_quote_header_variables.quote_number );
293 --	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'273: cust_party_id =         '||l_quote_header_variables.cust_party_id );
294 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'274: quote_version =         '||l_quote_header_variables.quote_version );
295 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'275: resource_id, =          '||l_quote_header_variables.resource_id );
296 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'276: org_id =                '||l_quote_header_variables.org_id );
297 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'277: cust_po_number =        '||l_quote_header_variables.cust_po_number );
298 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'278: shipment_priority_code =        '||l_quote_header_variables.shipment_priority_code );
299 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'279: minisite_id =        '||l_quote_header_variables.minisite_id );
300 
301 
302 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'************************ ');
303 	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,' ');
304 
305      END IF;
306      CLOSE c_get_quote_header_variables;
307 
308   --  Get the customer info
309 
310      IF l_quote_header_variables.cust_account_id IS NOT NULL THEN
311     	   OPEN c_get_cust_info_var(l_quote_header_variables.cust_account_id);
312     	   FETCH c_get_cust_info_var INTO l_cust_info_variables;
313     	   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
314        	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'280: c_get_cust_info_var%ROWCOUNT:  ' || c_get_cust_info_var%ROWCOUNT);
315              fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'290: Values from l_cust_info_variables are:');
316 	        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,' ');
317 	        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'************************ ');
318 	        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'300: category_code =         '||l_cust_info_variables.category_code );
319 	        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'310: customer_class_code =   '||l_cust_info_variables.customer_class_code );
320 	        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'320: profile_class_id =      '||l_cust_info_variables.profile_class_id );
321 	        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'330: credit_rating =         '||l_cust_info_variables.credit_rating );
322 	        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'340: credit_classification = '||l_cust_info_variables.credit_classification );
323 	        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'350: risk_code =             '||l_cust_info_variables.risk_code );
324 	        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'362: party_id =              '||l_cust_info_variables.party_id );
325 	        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,' ');
326 
327     	   END IF;
328 	      l_party_id := l_cust_info_variables.party_id;
329     	   CLOSE c_get_cust_info_var;
330      ELSE   -- Customer Category is the only variable considered for prospects
331         BEGIN
332 	      SELECT category_code
333 		 INTO l_customer_category
334 		 FROM hz_parties
335 		 WHERE party_id = l_quote_header_variables.party_id;
336     	         IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
337 	             fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'360: category_code =      '||l_customer_category );
338 	        	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,' ');
339 	        	   fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'************************ ');
340     	         END IF;
341 	   EXCEPTION
342 	      WHEN NO_DATA_FOUND THEN
343 		   NULL;
344     	        IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
345 	           fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'370: Customer Category does not exist' );
346 	        	 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,' ');
347 	        	 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'************************ ');
348     	        END IF;
349 	   END;
350      END IF;
351 
352      FOR i IN p_sys_var_value_tbl.FIRST..p_sys_var_value_tbl.LAST LOOP
353 
354         IF p_sys_var_value_tbl(i).variable_code = G_CUST_CATEGORY_CODE     THEN
355            IF l_quote_header_variables.cust_account_id IS NOT NULL THEN
356                p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.category_code;
357 		 ELSE
358 		    p_sys_var_value_tbl(i).variable_value_id := l_customer_category;
359 		 END IF;
360 	   END IF;
361 
362         IF p_sys_var_value_tbl(i).variable_code = G_CUST_CLASS_CODE
363 		 AND l_quote_header_variables.cust_account_id IS NOT NULL THEN
364            p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.customer_class_code;
365 
366 	   ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_PROF_CLASS_CODE
367 		 AND l_quote_header_variables.cust_account_id IS NOT NULL THEN
368            p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.profile_class_id;
369 
370 	   ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_CRDT_RATE_CODE
371 		 AND l_quote_header_variables.cust_account_id IS NOT NULL THEN
372            p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.credit_rating;
373 
374 	   ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_CRDT_CLASS_CODE
375 		 AND l_quote_header_variables.cust_account_id IS NOT NULL THEN
376            p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.credit_classification;
377 
378 	   ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_RISK_CODE
379 		 AND l_quote_header_variables.cust_account_id IS NOT NULL THEN
380            p_sys_var_value_tbl(i).variable_value_id := l_cust_info_variables.risk_code;
381 	   END IF;
382 
383 
384         IF p_sys_var_value_tbl(i).variable_code = G_SOLDTO_COUNTRY_CODE THEN
385            IF l_quote_header_variables.sold_to_party_site_id IS NOT NULL THEN
386 		    OPEN c_get_country_quote(l_quote_header_variables.sold_to_party_site_id);
387 		    FETCH c_get_country_quote INTO p_sys_var_value_tbl(i).variable_value_id;
388 		    CLOSE c_get_country_quote;
389     	         IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
390 	            fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'380: Sold to country = '||p_sys_var_value_tbl(i).variable_value_id);
391     	         END IF;
392 	      END IF;
393 
394         ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIPTO_COUNTRY_CODE THEN
395            IF l_quote_header_variables.ship_to_party_site_id IS NOT NULL THEN
396               OPEN c_get_country_quote(l_quote_header_variables.ship_to_party_site_id);
397               FETCH c_get_country_quote INTO p_sys_var_value_tbl(i).variable_value_id;
398               CLOSE c_get_country_quote;
399     	         IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
400 	            fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'390: Ship to country = '||p_sys_var_value_tbl(i).variable_value_id);
401     	         END IF;
402 	      END IF;
403 
404         ELSIF p_sys_var_value_tbl(i).variable_code = G_BILLTO_COUNTRY_CODE THEN
405            IF l_quote_header_variables.invoice_to_party_site_id IS NOT NULL THEN
406               OPEN c_get_country_quote(l_quote_header_variables.invoice_to_party_site_id);
407               FETCH c_get_country_quote INTO p_sys_var_value_tbl(i).variable_value_id;
408               CLOSE c_get_country_quote;
409     	         IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
410 	            fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'400: Invoice to country = '||p_sys_var_value_tbl(i).variable_value_id);
411     	         END IF;
412 	      END IF;
413 
414         ELSIF p_sys_var_value_tbl(i).variable_code = G_CUSTOMER_NAME_CODE     THEN
415            p_sys_var_value_tbl(i).variable_value_id := nvl(l_quote_header_variables.cust_account_id, l_party_id);
416 
417         ELSIF p_sys_var_value_tbl(i).variable_code = G_BILLTO_CUST_NAME_CODE    THEN
418            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.invoice_to_cust_account_id;
419 
420         ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIPTO_CUST_NAME_CODE      THEN
421            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.ship_to_cust_account_id;
422 
423    	   ELSIF p_sys_var_value_tbl(i).variable_code = G_SALES_DOC_TYPE_CODE     THEN
424            p_sys_var_value_tbl(i).variable_value_id := G_QUOTE_DOC_TYPE;
425 
426     	   ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_PO_EXIST_CODE   THEN
427            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.cust_po_num_exist;
428 
429     	   ELSIF p_sys_var_value_tbl(i).variable_code = G_PRICE_LIST_CODE   THEN
430            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.price_list_id;
431 
432         ELSIF p_sys_var_value_tbl(i).variable_code = G_CURRENCY_CODE    THEN
433            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.currency_code;
434 
435         ELSIF p_sys_var_value_tbl(i).variable_code = G_FREIGHT_TERMS_CODE   THEN
436            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.freight_terms_code;
437 
438         ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIPPING_METHOD_CODE   THEN
439            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.ship_method_code;
440 
441         ELSIF p_sys_var_value_tbl(i).variable_code = G_PAYMENT_TERM_CODE   THEN
442            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.payment_term_id;
443 
444         ELSIF p_sys_var_value_tbl(i).variable_code = G_PA_NAME_CODE   THEN
445            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.price_agreement_id;
446 
447 --AK
448         ELSIF p_sys_var_value_tbl(i).variable_code = G_PA_TYPE_CODE     THEN
449            BEGIN
450 	      IF l_quote_header_variables.price_agreement_id IS NOT NULL THEN
451 	         OPEN c_get_pa_type(l_quote_header_variables.price_agreement_id);
452 	         FETCH c_get_pa_type INTO p_sys_var_value_tbl(i).variable_value_id;
453 	         CLOSE c_get_pa_type;
454 	      END IF;
455 	   END;
456 --AK
457 
458         ELSIF p_sys_var_value_tbl(i).variable_code = G_PA_NAME_EXIST_CODE   THEN
459            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.price_agreement_exist;
460 
461         ELSIF p_sys_var_value_tbl(i).variable_code = G_PAYMENT_TYPE_CODE   THEN
462            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.payment_type_code;
463 
464         ELSIF p_sys_var_value_tbl(i).variable_code = G_FOB_CODE   THEN
465            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.fob_code;
466 
467         ELSIF p_sys_var_value_tbl(i).variable_code = G_SALES_CHANNEL_CODE   THEN
468            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.sales_channel_code;
469 
470         ELSIF p_sys_var_value_tbl(i).variable_code = G_TAX_HANDL_CODE   THEN
471            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.tax_exempt_flag;
472 
473         ELSIF p_sys_var_value_tbl(i).variable_code = G_ORDER_TYPE_CODE   THEN
474            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.order_type_id;
475 
476         ELSIF p_sys_var_value_tbl(i).variable_code = G_TOTAL_CODE   THEN
477            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.total_quote_price;
478 
479         ELSIF p_sys_var_value_tbl(i).variable_code = G_TOTAL_ADJUST_AMT_CODE   THEN
480            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.total_adjusted_amount;
481 
482         ELSIF p_sys_var_value_tbl(i).variable_code = G_TOTAL_ADJUST_PCT_CODE   THEN
483            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.total_adjusted_percent;
484 -- XY
485 
486         ELSIF p_sys_var_value_tbl(i).variable_code = G_QUOTE_NUMBER_CODE   THEN
487            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.quote_number;
488 
489         ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_PO_NUMBER_CODE   THEN
490            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.cust_po_number;
491         -- Added for iStore
492         ELSIF p_sys_var_value_tbl(i).variable_code = G_SHIPMENT_PRIORITY_CODE   THEN
493            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.shipment_priority_code;
494         ELSIF p_sys_var_value_tbl(i).variable_code = G_SITE_ID_CODE   THEN
495            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.minisite_id;
496         --
497         ELSIF p_sys_var_value_tbl(i).variable_code = G_VERSION_NUMBER_CODE   THEN
498            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.quote_version;
499 
500         ELSIF p_sys_var_value_tbl(i).variable_code = G_CUST_CONTACT_NAME_CODE   THEN
501            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.party_id;
502 
503         ELSIF p_sys_var_value_tbl(i).variable_code = G_SALESREP_NAME_CODE   THEN
504            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.resource_id;
505 
506         ELSIF p_sys_var_value_tbl(i).variable_code = G_SUPPLIER_NAME_CODE   THEN
507            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.org_id;
508 
509         ELSIF p_sys_var_value_tbl(i).variable_code = G_CURRENCY_NAME_CODE   THEN
510            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.currency_code;
511 
512         ELSIF p_sys_var_value_tbl(i).variable_code = G_CURRENCY_SYMBOL_CODE   THEN
513            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.currency_code;
514 
515         ELSIF p_sys_var_value_tbl(i).variable_code = G_PA_NUMBER_CODE   THEN
516            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.price_agreement_id;
517 
518         ELSIF p_sys_var_value_tbl(i).variable_code = G_CUSTOMER_NUMBER_CODE   THEN
519            p_sys_var_value_tbl(i).variable_value_id := l_quote_header_variables.cust_account_id;
520 
521 -- XY
522 
523         END IF;
524 
525         IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
526 	      fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,' ');
527            fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'410: p_sys_var_value_tbl('||i||').variable_code     : '||p_sys_var_value_tbl(i).variable_code);
528            fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'415: p_sys_var_value_tbl('||i||').variable_value_id : '||p_sys_var_value_tbl(i).variable_value_id);
529         END IF;
530 
531      END LOOP;
532 
533   END IF;
534 
535   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
536 	 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,' ');
537 	 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'************************ ');
538       fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'420: End of '||l_package_procedure||' for header level variables, x_return_status ' || x_return_status);
539   END IF;
540 EXCEPTION
541 
542    WHEN FND_API.G_EXC_ERROR THEN
543 
544 	IF c_get_quote_header_variables%ISOPEN THEN
545 	   CLOSE c_get_quote_header_variables;
546 	END IF;
547 
548 	IF c_get_cust_info_var%ISOPEN THEN
549 	   CLOSE c_get_cust_info_var;
550 	END IF;
551 
552      IF c_get_country_quote%ISOPEN THEN
553         CLOSE c_get_country_quote;
554      END IF;
555 
556      IF c_get_expert_yes_no%ISOPEN THEN
557         CLOSE c_get_expert_yes_no;
558      END IF;
559 
560 	x_return_status := FND_API.G_RET_STS_ERROR ;
561 
562   	FND_MSG_PUB.Count_And_Get(
563   		        p_count => x_msg_count,
564           	    p_data => x_msg_data  );
565 
566      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
567         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'430: '||l_package_procedure||' In the FND_API.G_RET_STS_ERROR section');
568         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'440: x_return_status = '||x_return_status);
569      END IF;
570 
571    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
572 
573         IF c_get_quote_header_variables%ISOPEN THEN
574            CLOSE c_get_quote_header_variables;
575         END IF;
576 
577         IF c_get_cust_info_var%ISOPEN THEN
578            CLOSE c_get_cust_info_var;
579         END IF;
580 
581         IF c_get_country_quote%ISOPEN THEN
582            CLOSE c_get_country_quote;
583         END IF;
584 
585         IF c_get_expert_yes_no%ISOPEN THEN
586            CLOSE c_get_expert_yes_no;
587         END IF;
588 
589   	x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
590      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
591         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'450: '||l_package_procedure||' In the FND_API.G_EXC_UNEXPECTED_ERROR section');
592         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'460: x_return_status = '||x_return_status);
593      END IF;
594 
595   	FND_MSG_PUB.Count_And_Get(
596   		        p_count => x_msg_count,
597           	    p_data => x_msg_data  );
598 
599    WHEN OTHERS THEN
600 
601      IF c_get_quote_header_variables%ISOPEN THEN
602         CLOSE c_get_quote_header_variables;
603      END IF;
604 
605      IF c_get_cust_info_var%ISOPEN THEN
606         CLOSE c_get_cust_info_var;
607      END IF;
608 
609      IF c_get_country_quote%ISOPEN THEN
610         CLOSE c_get_country_quote;
611      END IF;
612 
613      IF c_get_expert_yes_no%ISOPEN THEN
614         CLOSE c_get_expert_yes_no;
615      END IF;
616 
617   	x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
618      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
619         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'470: '||l_package_procedure||' In the FND_API.G_RET_STS_UNEXP_ERROR section');
620         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'480: x_return_status = '||x_return_status);
621      END IF;
622 
623     	IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
624          	   FND_MSG_PUB.Add_Exc_Msg(
625           	        G_PKG_NAME ,
626           	        l_api_name );
627   	END IF;
628 
629   	FND_MSG_PUB.Count_And_Get(
630   	     p_count => x_msg_count,
631        	 p_data => x_msg_data );
632 
633 END get_clause_variable_values;
634 
635 
636 --this overloaded signature is called from the contract expert
637   PROCEDURE Get_clause_Variable_Values (
638     p_api_version               IN         NUMBER,
639     p_init_msg_list             IN         VARCHAR2,
640     x_return_status             OUT NOCOPY VARCHAR2,
641     x_msg_count                 OUT NOCOPY NUMBER,
642     x_msg_data                  OUT NOCOPY VARCHAR2,
643     p_doc_id                    IN         NUMBER,
644     p_variables_tbl             IN         OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type,
645     x_line_var_value_tbl        OUT NOCOPY OKC_TERMS_UTIL_GRP.item_dtl_tbl
646   ) IS
647 
648 l_api_name          VARCHAR2(30) := 'get_clause_variable_values - 2';
649 l_package_procedure VARCHAR2(60);
650 l_api_version       CONSTANT NUMBER := 1;
651 l_index             BINARY_INTEGER;
652 l_debug             Boolean;
653 l_module            VARCHAR2(250) := G_MODULE_NAME||l_api_name;
654 
655 BEGIN
656 
657    l_package_procedure := G_PKG_NAME || '.' || l_api_name;
658 
659    x_return_status := FND_API.G_RET_STS_SUCCESS;
660 
661    IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
662       l_debug := true;
663    END IF;
664 
665    IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
666        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,' ');
667        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'10: Start '||l_package_procedure);
668        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'20: Entered ' || l_package_procedure);
669        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'30: p_doc_id: ' || p_doc_id);
670        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'40: p_variables_tbl.COUNT: ' || p_variables_tbl.COUNT);
671    END IF;
672 
673    --
674    -- Standard call to check for call compatibility.
675    --
676    IF NOT FND_API.Compatible_API_Call (l_api_version,
677                                         p_api_version,
678                                         l_api_name,
679                                         G_PKG_NAME)
680    THEN
681         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
682    END IF;
683 
684    --
685    -- Initialize message list if p_init_msg_list is set to TRUE.
686    --
687    IF FND_API.to_Boolean( p_init_msg_list ) THEN
688         FND_MSG_PUB.initialize;
689    END IF;
690 
691 	l_index := p_variables_tbl.FIRST;
692      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
693         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'50: l_index = '||l_index);
694      END IF;
695 
696 	WHILE l_index IS NOT NULL
697 	LOOP
698 	   IF p_variables_tbl(l_index).Variable_code = G_ITEM_CODE THEN
699            IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
700               fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'60: variable code = '||p_variables_tbl(l_index).Variable_code);
701            END IF;
702 	      SELECT
703 		    items.concatenated_segments
704            BULK COLLECT INTO x_line_var_value_tbl.item
705            FROM
706 		    Mtl_System_Items_vl items,
707 		    Aso_Quote_Lines_all lines
708            WHERE
709 	         lines.inventory_item_id = items.INVENTORY_ITEM_ID
710              AND lines.organization_id = items.organization_id
711              AND lines.LINE_CATEGORY_CODE = 'ORDER'
712              AND lines.quote_header_id = p_doc_id;
713 
714 	   ELSIF p_variables_tbl(l_index).Variable_code = G_ITEM_CATEGORY_CODE THEN
715            IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
716               fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'70: variable code = '||p_variables_tbl(l_index).Variable_code);
717            END IF;
718 
719 	      SELECT
720 		      cats.category_concat_segs
721            BULK COLLECT INTO x_line_var_value_tbl.category
722         	 FROM
723 		    Mtl_Item_Categories mic,
724 		    Aso_Quote_Lines_all lines,
725 		    Mtl_Categories_V cats
726         	 WHERE
727 		    lines.inventory_item_id = mic.INVENTORY_ITEM_ID
728              AND mic.category_id = cats.category_id
729              AND mic.organization_id = lines.organization_id
730              AND mic.category_set_id = (
731 					SELECT nvl(FND_PROFILE.VALUE('ASO_CATEGORY_SET'), sets.category_set_id )
732               			FROM Mtl_Default_Category_Sets sets
733               			WHERE functional_area_id = 7
734 				     )
735              AND lines.LINE_CATEGORY_CODE = 'ORDER'
736          	   AND lines.quote_header_id = p_doc_id;
737 	   END IF;
738 
739 	      l_index := p_variables_tbl.next(l_index);
740            IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
741               fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'80: l_index = '||l_index);
742            END IF;
743 
744 	END LOOP;
745 
746   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
747       IF x_line_var_value_tbl.item.COUNT > 0 THEN
748 	    fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'90: Item Count = '||x_line_var_value_tbl.item.COUNT);
749          FOR i IN x_line_var_value_tbl.item.FIRST..x_line_var_value_tbl.item.LAST LOOP
750 	       fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'100: Item : '||x_line_var_value_tbl.item(i));
751 	    END LOOP;
752 	 END IF;
753       IF x_line_var_value_tbl.category.COUNT > 0 THEN
754 	    fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'110: Item category Count = '||x_line_var_value_tbl.category.COUNT);
755          FOR i IN x_line_var_value_tbl.category.FIRST..x_line_var_value_tbl.category.LAST LOOP
756 	       fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'120: Item category: '||x_line_var_value_tbl.category(i));
757 	    END LOOP;
758 	 END IF;
759 	 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'130:End of '||l_package_procedure||' for header level variables, x_return_status ' || x_return_status);
760   END IF;
761 EXCEPTION
762 
763    WHEN FND_API.G_EXC_ERROR THEN
764 
765 	x_return_status := FND_API.G_RET_STS_ERROR ;
766      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
767         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'130: '||l_package_procedure||' In the FND_API.G_EXC_ERROR section');
768         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'140: x_return_status = '||x_return_status);
769      END IF;
770 
771   	FND_MSG_PUB.Count_And_Get(
772   		        p_count => x_msg_count,
773           	    p_data => x_msg_data  );
774 
775    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
776 
777   	x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
778      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
779         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'150: '||l_package_procedure||' In the FND_API.G_EXC_UNEXPECTED_ERROR section');
780         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'160: x_return_status = '||x_return_status);
781      END IF;
782 
783   	FND_MSG_PUB.Count_And_Get(
784   		        p_count => x_msg_count,
785           	    p_data => x_msg_data  );
786 
787    WHEN OTHERS THEN
788 
789   	x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
790      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
791         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'170: '||l_package_procedure||' In the OTHERS section');
792         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'180: x_return_status = '||x_return_status);
793      END IF;
794 
795     	IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
796          	   FND_MSG_PUB.Add_Exc_Msg(
797           	        G_PKG_NAME ,
798           	        l_api_name );
799   	END IF;
800 
801   	FND_MSG_PUB.Count_And_Get(
802   	     p_count => x_msg_count,
803        	 p_data => x_msg_data );
804 
805 END Get_clause_Variable_Values;
806 
807 -- This procedure will be called from contract expert to get
808 -- line level system variables
809 PROCEDURE Get_Line_Variable_Values (
810     p_api_version               IN            NUMBER,
811     p_init_msg_list             IN            VARCHAR2 DEFAULT FND_API.G_FALSE,
812     p_doc_id                    IN            NUMBER,
813     x_return_status             OUT  NOCOPY   VARCHAR2,
814     x_msg_count                 OUT  NOCOPY   NUMBER,
815     x_msg_data                  OUT  NOCOPY   VARCHAR2,
816     x_line_sys_var_value_tbl    OUT  NOCOPY   OKC_XPRT_XRULE_VALUES_PVT.line_sys_var_value_tbl_type,
817     x_line_count                OUT  NOCOPY   NUMBER,
818     x_line_variables_count      OUT  NOCOPY   NUMBER
819   ) IS
820 
821 l_api_name          VARCHAR2(30) := 'get_line_variable_values';
822 l_package_procedure VARCHAR2(60);
823 l_api_version       CONSTANT NUMBER := 1;
824 --l_index             BINARY_INTEGER;
825 l_debug             Boolean;
826 l_module            VARCHAR2(250) := G_MODULE_NAME||l_api_name;
827 
828 --
829 -- Cursor to fetch the line attributes for the quote
830 --
831 CURSOR c_get_quote_line_variables IS
832 SELECT
833       quote.quote_line_id,
834       ASO_LINE_NUM_INT.Get_UI_Line_Number(quote.quote_line_id) line_number, -- Changed for Bug 4768964
835       nvl(payments.payment_term_id,'-99999') payment_term_id,
836       nvl(shipments.fob_code,'NO_VALUE') fob_code,
837       quote.inventory_item_id,
838       quote.org_id
839 FROM
840       aso_payments payments,
841       aso_shipments shipments,
842       aso_quote_lines_all quote
843 WHERE
844       quote.quote_line_id = payments.quote_line_id(+)
845  AND  quote.quote_line_id = shipments.quote_line_id(+)
846  AND  quote.quote_header_id = p_doc_id;
847 
848 l_quote_line_variables  c_get_quote_line_variables%ROWTYPE;
849 l_line_count	NUMBER := 0;
850 l_line_number   VARCHAR2(250); --NUMBER; For Bug 4768964
851 l_index	  NUMBER := 0;
852 
853 --AK
854 CURSOR c_get_quote_item (p_quote_line_id NUMBER) IS
855 SELECT
856     items.concatenated_segments item
857 FROM
858     Mtl_System_Items_vl items,
859     Aso_Quote_Lines_all lines
860 WHERE
861  lines.inventory_item_id = items.INVENTORY_ITEM_ID
862 AND lines.organization_id = items.organization_id
863 --AND lines.LINE_CATEGORY_CODE = 'ORDER'
864 AND lines.quote_header_id = p_doc_id
865 AND lines.quote_line_id = p_quote_line_id;
866 
867 CURSOR c_get_quote_item_category (p_quote_line_id NUMBER) IS
868 SELECT
869       cats.category_concat_segs item_category
870  FROM
871     Mtl_Item_Categories mic,
872     Aso_Quote_Lines_all lines,
873     Mtl_Categories_V cats
874  WHERE
875     lines.inventory_item_id = mic.INVENTORY_ITEM_ID
876 AND mic.category_id = cats.category_id
877 AND mic.organization_id = lines.organization_id
878 AND mic.category_set_id = (
879 			SELECT nvl(FND_PROFILE.VALUE('ASO_CATEGORY_SET'), sets.category_set_id )
880 		FROM Mtl_Default_Category_Sets sets
881 		WHERE functional_area_id = 7
882 		     )
883 AND lines.LINE_CATEGORY_CODE = 'ORDER'
884 AND lines.quote_header_id = p_doc_id
885 AND lines.quote_line_id = p_quote_line_id;
886 
887 --AK
888 
889 -- New Line Number
890 l_in_qte_line_number_tbl ASO_LINE_NUM_INT.In_Line_Number_Tbl_Type;
891 l_out_qte_line_number_tbl ASO_LINE_NUM_INT.Out_Line_Number_Tbl_Type;
892 i NUMBER := 1;
893 l_quote_org_id NUMBER;
894 CURSOR c_get_quote_org_id IS
895 SELECT
896       quote.org_id
897 FROM
898       aso_quote_headers_all quote
899 WHERE
900       quote.quote_header_id = p_doc_id;
901 -- New Line Number
902 
903 BEGIN
904 
905    l_package_procedure := G_PKG_NAME || '.' || l_api_name;
906 
907    x_return_status := FND_API.G_RET_STS_SUCCESS;
908 
909    IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
910       l_debug := true;
911    END IF;
912 
913    IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
914        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,' ');
915        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'10: Start '||l_package_procedure);
916        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'20: Entered ' || l_package_procedure);
917        fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'30: p_doc_id: ' || p_doc_id);
918    END IF;
919 
920    --
921    -- Standard call to check for call compatibility.
922    --
923    IF NOT FND_API.Compatible_API_Call (l_api_version,
924                                         p_api_version,
925                                         l_api_name,
926                                         G_PKG_NAME)
927    THEN
928         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
929    END IF;
930 
931    --
932    -- Initialize message list if p_init_msg_list is set to TRUE.
933    --
934    IF FND_API.to_Boolean( p_init_msg_list ) THEN
935         FND_MSG_PUB.initialize;
936    END IF;
937 
938    OPEN c_get_quote_org_id;
939    FETCH c_get_quote_org_id INTO l_quote_org_id;
940    CLOSE c_get_quote_org_id;
941 
942    MO_GLOBAL.INIT('ASO');
943    MO_GLOBAL.SET_POLICY_CONTEXT('S',l_quote_org_id);
944 
945    OPEN c_get_quote_line_variables;
946    LOOP
947      FETCH c_get_quote_line_variables INTO l_quote_line_variables;
948      EXIT WHEN c_get_quote_line_variables%NOTFOUND;
949 
950      --l_line_number := l_quote_line_variables.line_number;
951 
952      -- Begin
953      aso_line_num_int.reset_line_num;
954      l_in_qte_line_number_tbl(i).quote_line_id := l_quote_line_variables.quote_line_id;
955      aso_line_num_int.aso_ui_line_number( p_in_Line_number_tbl   => l_in_qte_line_number_tbl,
956                                           x_out_line_number_tbl  => l_out_qte_line_number_tbl);
957      l_line_number := l_out_qte_line_number_tbl(l_in_qte_line_number_tbl(i).quote_line_id);
958      i := i + 1;
959      -- End
960 
961      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
962           fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'40: line_number =        '||l_quote_line_variables.line_number );
963           fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'50: payment_term_id =        '||l_quote_line_variables.payment_term_id );
964           fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'60: item_id =        '||l_quote_line_variables.inventory_item_id );
965           fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'70: org_id =        '||l_quote_line_variables.org_id );
966      END IF;
967 
968 
969 	l_line_count := l_line_count+1;
970 
971      IF l_quote_line_variables.payment_term_id IS NOT NULL THEN
972        l_index      := l_index+1;
973        x_line_sys_var_value_tbl(l_index).line_number :=  l_line_number;
974        x_line_sys_var_value_tbl(l_index).variable_code := G_LINE_PAYMENT_TERM_CODE;
975        x_line_sys_var_value_tbl(l_index).variable_value := l_quote_line_variables.payment_term_id;
976        x_line_sys_var_value_tbl(l_index).item_id := l_quote_line_variables.inventory_item_id;
977        x_line_sys_var_value_tbl(l_index).org_id := l_quote_line_variables.org_id;
978      END IF;
979 
980      IF l_quote_line_variables.fob_code IS NOT NULL THEN
981        l_index      := l_index+1;
982        x_line_sys_var_value_tbl(l_index).line_number :=  l_line_number;
983        x_line_sys_var_value_tbl(l_index).variable_code := G_LINE_FOB_CODE;
984        x_line_sys_var_value_tbl(l_index).variable_value := l_quote_line_variables.fob_code;
985        x_line_sys_var_value_tbl(l_index).item_id := l_quote_line_variables.inventory_item_id;
986        x_line_sys_var_value_tbl(l_index).org_id := l_quote_line_variables.org_id;
987      END IF;
988 
989     IF l_quote_line_variables.inventory_item_id IS NOT NULL THEN
990       FOR c_get_quote_item_rec IN c_get_quote_item(l_quote_line_variables.quote_line_id) LOOP
991        l_index      := l_index+1;
992        x_line_sys_var_value_tbl(l_index).line_number :=  l_line_number;
993        x_line_sys_var_value_tbl(l_index).variable_code := G_ITEM_CODE;
994        x_line_sys_var_value_tbl(l_index).variable_value := c_get_quote_item_rec.item;
995        x_line_sys_var_value_tbl(l_index).item_id := l_quote_line_variables.inventory_item_id;
996        x_line_sys_var_value_tbl(l_index).org_id := l_quote_line_variables.org_id;
997       END LOOP;
998     END IF;
999 
1000     IF l_quote_line_variables.inventory_item_id IS NOT NULL THEN
1001       FOR c_get_quote_item_category_rec IN c_get_quote_item_category(l_quote_line_variables.quote_line_id) LOOP
1002        l_index      := l_index+1;
1003        x_line_sys_var_value_tbl(l_index).line_number :=  l_line_number;
1004        x_line_sys_var_value_tbl(l_index).variable_code := G_ITEM_CATEGORY_CODE;
1005        x_line_sys_var_value_tbl(l_index).variable_value := c_get_quote_item_category_rec.item_category;
1006        x_line_sys_var_value_tbl(l_index).item_id := l_quote_line_variables.inventory_item_id;
1007        x_line_sys_var_value_tbl(l_index).org_id := l_quote_line_variables.org_id;
1008       END LOOP;
1009     END IF;
1010 
1011    END LOOP;
1012 
1013    CLOSE c_get_quote_line_variables;
1014 
1015    x_line_count := l_line_count;
1016    -- Fix for 4768964 to show line number
1017    x_line_variables_count := 4; --Item, Item Category, Payment term, FOB
1018 
1019   IF l_line_count = 0 THEN
1020       x_line_count := 1; -- Since no Lines, need to set line count to 1 for the CX Java code
1021       x_line_variables_count := 0; -- Since no Lines, need to set line variables count to 0 for the CX Java code
1022   END IF;
1023 
1024   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1025      fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'130:End of '||l_package_procedure||' for line level variables, x_return_status ' || x_return_status);
1026   END IF;
1027 EXCEPTION
1028 
1029    WHEN FND_API.G_EXC_ERROR THEN
1030 
1031      IF c_get_quote_line_variables%ISOPEN THEN
1032      	CLOSE c_get_quote_line_variables;
1033      END IF;
1034 
1035 
1036 	x_return_status := FND_API.G_RET_STS_ERROR ;
1037      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1038         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'130: '||l_package_procedure||' In the FND_API.G_EXC_ERROR section');
1039         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'140: x_return_status = '||x_return_status);
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    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1047      IF c_get_quote_line_variables%ISOPEN THEN
1048      	CLOSE c_get_quote_line_variables;
1049      END IF;
1050 
1051 
1052   	x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1053      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1054         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'150: '||l_package_procedure||' In the FND_API.G_EXC_UNEXPECTED_ERROR section');
1055         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'160: x_return_status = '||x_return_status);
1056      END IF;
1057 
1058   	FND_MSG_PUB.Count_And_Get(
1059   		        p_count => x_msg_count,
1060           	    p_data => x_msg_data  );
1061 
1062    WHEN OTHERS THEN
1063      IF c_get_quote_line_variables%ISOPEN THEN
1064      	CLOSE c_get_quote_line_variables;
1065      END IF;
1066 
1067   	x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1068      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1069         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'170: '||l_package_procedure||' In the OTHERS section');
1070         fnd_log.string(FND_LOG.LEVEL_PROCEDURE,l_module,'180: x_return_status = '||x_return_status);
1071      END IF;
1072 
1073     	IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1074          	   FND_MSG_PUB.Add_Exc_Msg(
1075           	        G_PKG_NAME ,
1076           	        l_api_name );
1077   	END IF;
1078 
1079   	FND_MSG_PUB.Count_And_Get(
1080   	     p_count => x_msg_count,
1081        	 p_data => x_msg_data );
1082 
1083 END Get_Line_Variable_Values;
1084 
1085 
1086 
1087 
1088 END OKC_XPRT_QUOTE_INT_PVT;